<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

class Graph extends Admin_Controller
{

	function __construct()
	{
		parent::__construct();
		$this->load->helper('form');
		$this->load->library('form_validation');
	}

	//this is to list all homepage
	public function index()
	{

		$sql = "SELECT year(order_date) as tahun, sum(total_amount) as jumlah
				FROM orders
				WHERE payment_status = 5
				group by year(order_date)
				order by tahun asc ";
		$query = $this->db->query($sql);
		$_SESSION['penjualantahun'] = $query->result_array();

		//data product month
		$sql = "SELECT *
				FROM (	select order_date,DATE_FORMAT(order_date, '%M') as bulan, sum(total_amount) as jumlah 
						from orders
						where payment_status = 5
						group by year(order_date), month(order_date)
						order by order_date desc
						limit 5) var1
				ORDER by order_date asc";
		$query = $this->db->query($sql);
		$_SESSION['penjualanbulan'] = $query->result_array();


		// data product last 7 day
		$sql = "SELECT SUM(total_amount) as jumlah , CAST(order_date AS DATE) as hari
				FROM orders 
				WHERE order_date >= (DATE(NOW()) - INTERVAL 7 DAY) and payment_status = 5
				GROUP BY hari 
				ORDER BY hari  ASC";
		$query = $this->db->query($sql);
		$_SESSION['penjualanhari'] = $query->result_array();

		// best product
		$sql = "SELECT orders_detail.item_name as produk, COUNT(*) as jumlah
				FROM orders, orders_detail
				WHERE orders.payment_status = 5 and orders.id_orders = orders_detail.orders_id
				GROUP BY orders_detail.item_name
				order by jumlah desc";
		$query = $this->db->query($sql);
		$_SESSION['top'] = $query->result_array();

		// not ordered
		$query = $this->db->select('products.title AS produk')
			->from('products')
			->join('orders_detail', 'products.id_products = orders_detail.product_id', 'left')
			->where('orders_detail.product_id IS NULL')
			->get();

		$result = $query->result_array();
		$_SESSION['poornotbuy'] = $result;

		// poor product
		$sql = "SELECT orders_detail.item_name as produk, COUNT(*) as jumlah 
				FROM orders, orders_detail 
				WHERE orders.id_orders = orders_detail.orders_id and orders.payment_status = 5
				GROUP by orders_detail.item_name 
				ORDER BY jumlah  ASC";
		$query = $this->db->query($sql);
		$_SESSION['poor'] = $query->result_array();

		$sql = "SELECT name, sex_type, phone, district, type, id_customers
		FROM customers
		ORDER BY 'name' ASC";
		$query = $this->db->query($sql);
		$_SESSION['customerreport'] = $query->result_array();

		$sql = "SELECT od.item_name
        FROM orders_detail od
        INNER JOIN orders o ON od.orders_id = o.id_orders
        INNER JOIN customers c ON o.customer_id = c.id_customers
        WHERE c.id_customers = o.customer_id AND o.id_orders = od.orders_id
        ORDER BY c.name ASC";
		$query = $this->db->query($sql);
		$_SESSION['customerreportitemname'] = $query->result_array();

		$sql = "SELECT  pd.sku, p.title AS product_name, p.brand_id, s.stock
		FROM stock s
		JOIN product_details pd ON s.id_product_detail = pd.id
		JOIN products p ON pd.product_id = p.id_products
		ORDER BY product_name ASC, pd.sku ASC, p.brand_id ASC";
		$query = $this->db->query($sql);
		$_SESSION['stockProduk'] = $query->result_array();

		$sql = "SELECT COUNT(*) AS jumlah_rusak, p.title AS product_name
		FROM claim_garansi cg
		JOIN orders_detail od ON cg.no_order_detail_id = od.id_orders_detail
		JOIN products p ON od.product_id = p.id_products
		GROUP BY p.title;";
		$query = $this->db->query($sql);
		$_SESSION['databarangrusak'] = $query->result_array();


		$sql = "SELECT DISTINCT c.name, c.email, c.phone, c.birthday 
		FROM customers c
		JOIN orders o ON c.id_customers = o.customer_id
		WHERE o.payment_status = 5 AND DAY(c.birthday) = DAY(CURDATE()) AND MONTH(c.birthday) = MONTH(CURDATE())
		ORDER BY YEAR(CURDATE()), MONTH(c.birthday), DAY(c.birthday)";
		$query = $this->db->query($sql);
		$_SESSION['birthdayday'] = $query->result_array();

		$sql = "SELECT DISTINCT c.name, c.email, c.phone, c.birthday 
		FROM customers c
		JOIN orders o ON c.id_customers = o.customer_id
		WHERE o.payment_status = 5 AND WEEKOFYEAR(c.birthday) = WEEKOFYEAR(CURDATE())
		ORDER BY DATE_FORMAT(c.birthday, '%m-%d') ASC
		";
		$query = $this->db->query($sql);
		$_SESSION['birthdayweek'] = $query->result_array();

		$sql = "SELECT DISTINCT c.name, c.email, c.phone, c.birthday 
		FROM customers c
		JOIN orders o ON c.id_customers = o.customer_id
		WHERE o.payment_status = 5 AND MONTH(c.birthday) = MONTH(CURDATE())
		ORDER BY DAY(c.birthday) ASC, MONTH(c.birthday) ASC";
		$query = $this->db->query($sql);
		$_SESSION['birthdaymonth'] = $query->result_array();

		// best cust
		$sql = "SELECT orders.customer_id, customers.name as name, COUNT(*) as jumlah 
				FROM orders, customers 
				WHERE payment_status = 5 AND customers.id_customers = orders.customer_id 
				GROUP BY orders.customer_id 
				ORDER BY `jumlah`  DESC 
				limit 5";
		$query = $this->db->query($sql);
		$_SESSION['bestcust'] = $query->result_array();
		$sql = "SELECT orders.customer_id, customers.name as name, COUNT(*) as jumlah 
				FROM orders, customers 
				WHERE payment_status = 5 AND customers.id_customers = orders.customer_id 
				GROUP BY orders.customer_id 
				ORDER BY `jumlah`  DESC";
		$query = $this->db->query($sql);
		$_SESSION['bestcustfull'] = $query->result_array();

		$sql = "SELECT sex_type, COUNT(*) as total FROM customers GROUP BY sex_type ORDER BY `total`  DESC";
		$query = $this->db->query($sql);
		$_SESSION['customerjk'] = $query->result_array();

		$sql = "SELECT province, COUNT(*) as customer_count 
        FROM customers 
        WHERE province IS NOT NULL 
        GROUP BY province";
		$query = $this->db->query($sql);
		$_SESSION['customerlokasi'] = $query->result_array();

		$sql = "SELECT 
		COUNT(CASE WHEN TIMESTAMPDIFF(YEAR, birthday, CURDATE()) <= 21 THEN 1 END) AS '21 kebawah',
		COUNT(CASE WHEN TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 22 AND 25 THEN 1 END) AS '21-25',
		COUNT(CASE WHEN TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 26 AND 30 THEN 1 END) AS '26-30',
		COUNT(CASE WHEN TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 31 AND 35 THEN 1 END) AS '31-35',
		COUNT(CASE WHEN TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 36 AND 40 THEN 1 END) AS '36-40',
		COUNT(CASE WHEN TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 41 AND 45 THEN 1 END) AS '41-45',
		COUNT(CASE WHEN TIMESTAMPDIFF(YEAR, birthday, CURDATE()) >= 46 THEN 1 END) AS '46 keatas'
		FROM 
		customers";
		$query = $this->db->query($sql);
		$_SESSION['customerumur'] = $query->result_array();

		$sql = "SELECT FLOOR(DATEDIFF(CURDATE(), birthday) / 365) AS age, COUNT(*) AS count_age
		FROM customers
		WHERE is_delete = 'no'
		GROUP BY age
		ORDER BY age ASC";
		$query = $this->db->query($sql);
		$_SESSION['customerumurtotal'] = $query->result_array();

		

		//load view
		$this->data['subview'] = 'admin/graph/index';
		$this->load->view('admin/templates/header', $this->data_header);
		$this->load->view('admin/_layout_main', $this->data);
		$this->load->view('admin/templates/footer');
	}

	// Controller function to fetch orders for a customer


	public function getPenjualan()
	{
		$startDate = $this->input->post('startPenjualan');
		$endDate = $this->input->post('endPenjualan');
		$_SESSION['startPenjualan'] = $startDate;
		$_SESSION['endPenjualan'] = $endDate;

		//data product year
		$sql = "SELECT year(order_date) as tahun, sum(total_amount) as jumlah
				FROM orders
				WHERE payment_status = 5 and order_date >= '" . $startDate . "' AND order_date <= '" . $endDate . "'
				group by year(order_date)
				order by tahun asc ";
		$query = $this->db->query($sql);
		$_SESSION['penjualantahun'] = $query->result_array();

		//data product month
		$sql = "SELECT *
				FROM (	select order_date,DATE_FORMAT(order_date, '%M') as bulan, sum(total_amount) as jumlah 
						from orders
						where payment_status = 5 and order_date >= '" . $startDate . "' AND order_date <= '" . $endDate . "'
						group by year(order_date), month(order_date)
						order by order_date desc) var1
				ORDER by order_date asc";
		$query = $this->db->query($sql);
		$_SESSION['penjualanbulan'] = $query->result_array();


		// data product day

		$sql = "SELECT SUM(total_amount) as jumlah , CAST(order_date AS DATE) as hari
				FROM orders 
				WHERE order_date BETWEEN '" . $startDate . "' AND '" . $endDate . "' and payment_status = 5
				GROUP BY hari 
				ORDER BY hari  ASC";
		$query = $this->db->query($sql);
		$_SESSION['penjualanhari'] = $query->result_array();

		$this->data['subview'] = 'admin/graph/index';
		$this->load->view('admin/templates/header', $this->data_header);
		$this->load->view('admin/_layout_main', $this->data);
		$this->load->view('admin/templates/footer');

		// echo "<script>console.log(".$startDate.$endDate.") </script>;";
		// echo "<script>console.log('bangke') </script>;";
	}

	public function getProduk()
	{
		$startDate = $this->input->post('startProduk');
		$endDate = $this->input->post('endProduk');
		$_SESSION['startProduk'] = $startDate;
		$_SESSION['endProduk'] = $endDate;

		// best product
		$sql = "SELECT orders_detail.item_name as produk, COUNT(*) as jumlah
				FROM orders, orders_detail
				WHERE orders.payment_status = 5 and orders.id_orders = orders_detail.orders_id and order_date >= '" . $startDate . "' AND order_date <= '" . $endDate . "'
				GROUP BY orders_detail.item_name
				order by jumlah desc";
		$query = $this->db->query($sql);
		$_SESSION['top'] = $query->result_array();

		$sql = "SELECT orders_detail.item_name as produk, COUNT(*) as jumlah
				FROM orders, orders_detail
				WHERE orders.payment_status = 5 and orders.id_orders = orders_detail.orders_id and order_date >= '" . $startDate . "' AND order_date <= '" . $endDate . "'
				GROUP BY orders_detail.item_name
				order by jumlah asc";
		$query = $this->db->query($sql);
		$_SESSION['poor'] = $query->result_array();

		$sql = "SELECT p.title AS nama_produk, pd.sku, s.stock, m.stock_change AS movement
		FROM products p
		JOIN product_details pd ON pd.product_id = p.id_products
		JOIN stock s ON s.id_product_detail = pd.id
		JOIN stock_movement m ON m.stock_id = s.id
		WHERE p.brand_id = 1 AND s.warehouse_id = 2 AND m.datetime BETWEEN STR_TO_DATE('2022-01-01','%Y-%m-%d') AND STR_TO_DATE('2022-03-31','%Y-%m-%d')
		ORDER BY m.datetime ASC";
		$query = $this->db->query($sql);
		$_SESSION['stockProduk'] = $query->result_array();


		$this->data['subview'] = 'admin/graph/index';
		$this->load->view('admin/templates/header', $this->data_header);
		$this->load->view('admin/_layout_main', $this->data);
		$this->load->view('admin/templates/footer');
	}

	public function getCustomer()
	{
		$startDate = $this->input->post('startCustomer');
		$endDate = $this->input->post('endCustomer');
		$_SESSION['startCustomer'] = $startDate;
		$_SESSION['endCustomer'] = $endDate;

		// best cust
		$sql = "SELECT orders.customer_id, customers.name as name, COUNT(*) as jumlah 
				FROM orders, customers 
				WHERE payment_status = 5 AND customers.id_customers = orders.customer_id and order_date >= '" . $startDate . "' AND order_date <= '" . $endDate . "'
				GROUP BY orders.customer_id 
				ORDER BY `jumlah`  DESC 
				limit 7";
		$query = $this->db->query($sql);
		$_SESSION['bestcust'] = $query->result_array();
		$sql = "SELECT orders.customer_id, customers.name as name, COUNT(*) as jumlah 
				FROM orders, customers 
				WHERE payment_status = 5 AND customers.id_customers = orders.customer_id and order_date >= '" . $startDate . "' AND order_date <= '" . $endDate . "'
				GROUP BY orders.customer_id 
				ORDER BY `jumlah`  DESC";
		$query = $this->db->query($sql);
		$_SESSION['bestcustfull'] = $query->result_array();


		$this->data['subview'] = 'admin/graph/index';
		$this->load->view('admin/templates/header', $this->data_header);
		$this->load->view('admin/_layout_main', $this->data);
		$this->load->view('admin/templates/footer');
	}
}