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

class reports extends CI_Model {
	public function __construct() {
		parent::__construct();
		$this->load->database();
	}

	//Count stock report
	public function count_stock_report() {
		$this->db->select("a.product_name,a.product_id,a.price,a.product_model,sum(b.quantity) as 'totalSalesQnty',(select sum(product_purchase_details.quantity) from product_purchase_details where product_id= `a`.`product_id`) as 'totalBuyQnty'");
		$this->db->from('product_information a');
		$this->db->join('invoice_details b', 'b.product_id = a.product_id');
		$this->db->where(array('a.status' => 1, 'b.status' => 1));
		$this->db->group_by('a.product_id');
		$query = $this->db->get();
		return $query->num_rows();

	}

	//Stock report
	public function stock_report($limit, $page) {
		$this->db->select("a.product_name,a.product_id,a.price,a.product_model,sum(b.quantity) as 'totalSalesQnty',(select sum(product_purchase_details.quantity) from product_purchase_details where product_id= `a`.`product_id`) as 'totalBuyQnty'");
		$this->db->from('product_information a');
		$this->db->join('invoice_details b', 'b.product_id = a.product_id');
		$this->db->where(array('a.status' => 1, 'b.status' => 1));
		$this->db->group_by('a.product_id');
		$this->db->order_by('a.product_id', 'desc');
		$this->db->limit($limit, $page);
		$query = $this->db->get();
		return $query->result_array();
	}

	//Out of stock
	public function out_of_stock() {

		$this->db->select("
			a.product_name,
			a.unit,
			a.product_id,
			a.price,
			a.supplier_price,
			a.product_model,
			c.category_name,
			sum(d.quantity) as totalSalesQnty,
			sum(b.quantity) as totalPurchaseQnty,
			e.purchase_date as purchase_date,
			e.purchase_id,
			(sum(b.quantity) - sum(d.quantity)) as stock
			");

		$this->db->from('product_information a');
		$this->db->join('product_category c', 'c.category_id = a.category_id', 'left');
		$this->db->join('product_purchase_details b', 'b.product_id = a.product_id', 'left');
		$this->db->join('invoice_details d', 'd.product_id = a.product_id', 'left');
		$this->db->join('product_purchase e', 'e.purchase_id = b.purchase_id', 'left');
		$this->db->group_by('a.product_id');
		$this->db->having('stock < 10', null, false);
		$this->db->having('totalPurchaseQnty < 10', null, false);
		$this->db->order_by('a.product_name', 'asc');
		$query = $this->db->get();
		return $query->result_array();
	}

	//Out of stock count
	public function out_of_stock_count() {
		$this->db->query("SET SQL_BIG_SELECTS=1"); // for avoid max_join_size.
		$this->db->select("
			a.product_name,
			a.unit,
			a.product_id,
			a.price,
			a.supplier_price,
			a.product_model,
			c.category_name,
			sum(d.quantity) as totalSalesQnty,
			sum(b.quantity) as totalPurchaseQnty,
			e.purchase_date as purchase_date,
			e.purchase_id,
			(sum(b.quantity) - sum(d.quantity)) as stock
			");

		$this->db->from('product_information a');
		$this->db->join('product_category c', 'c.category_id = a.category_id', 'left');
		$this->db->join('product_purchase_details b', 'b.product_id = a.product_id', 'left');
		$this->db->join('invoice_details d', 'd.product_id = a.product_id', 'left');
		$this->db->join('product_purchase e', 'e.purchase_id = b.purchase_id', 'left');
		$this->db->group_by('a.product_id');
		$this->db->having('stock < 10', null, false);
		$this->db->having('totalPurchaseQnty < 10', null, false);
		$this->db->order_by('a.product_name', 'asc');

		$query = $this->db->get();
		return $query->num_rows();
	}

	//Stock report single item
	public function stock_report_single_item($product_id) {
		$this->db->select("a.product_name,a.price,a.product_model,sum(b.quantity) as 'totalSalesQnty',sum(c.quantity) as 'totalBuyQnty'");
		$this->db->from('product_information a');
		$this->db->join('invoice_details b', 'b.product_id = a.product_id');
		$this->db->join('product_purchase_details c', 'c.product_id = a.product_id');
		$this->db->where(array('a.product_id' => $product_id, 'a.status' => 1, 'b.status' => 1));
		$this->db->group_by('a.product_id');
		$this->db->order_by('a.product_id', 'desc');
		$query = $this->db->get();
		return $query->result_array();
	}

	//Stock Report by date
	public function stock_report_bydate($product_id, $date, $limit, $page) {

		$this->db->select("
			a.product_name,
			a.unit,
			a.product_id,
			a.price,
			a.supplier_price,
			a.product_model,
			c.category_name,
			sum(b.quantity) as totalPurchaseQnty,
			e.purchase_date as purchase_date,
			e.purchase_id,
			f.unit_name
			");

		$this->db->from('product_information a');
		$this->db->join('product_purchase_details b', 'b.product_id = a.product_id', 'left');
		$this->db->join('product_category c', 'c.category_id = a.category_id');
		$this->db->join('product_purchase e', 'e.purchase_id = b.purchase_id');
		$this->db->join('unit f', 'f.unit_id = a.unit', 'left');
		$this->db->join('variant g', 'g.variant_id = b.variant_id', 'left');
		$this->db->join('store_set h', 'h.store_id = b.store_id');
		$this->db->where('b.store_id !=', null);
		$this->db->group_by('a.product_id');
		$this->db->order_by('a.product_name', 'asc');
		$this->db->where(array('a.delete_time' => NULL)); //Added
		if (empty($product_id)) {
			$this->db->where(array('a.status' => 1));

		} else {
			//Single product information
			$this->db->where(array('a.status' => 1, 'e.purchase_date <= ' => $date, 'a.product_id' => $product_id));
		}

		$this->db->limit($limit, $page);
		$query = $this->db->get();

		return $query->result_array();
	}

	//Counter of unique product histor which has been affected
	public function product_counter($product_id, $date) {
		$this->db->select("
			a.product_name,
			a.unit,
			a.product_id,
			a.price,
			a.supplier_price,
			a.product_model,
			c.category_name,
			sum(b.quantity) as totalPurchaseQnty,
			e.purchase_date as purchase_date,
			e.purchase_id,
			f.unit_name
			");

		$this->db->from('product_information a');
		$this->db->join('product_purchase_details b', 'b.product_id = a.product_id', 'left');
		$this->db->join('product_category c', 'c.category_id = a.category_id');
		$this->db->join('product_purchase e', 'e.purchase_id = b.purchase_id');
		$this->db->join('unit f', 'f.unit_id = a.unit', 'left');
		$this->db->join('variant g', 'g.variant_id = b.variant_id', 'left');
		$this->db->join('store_set h', 'h.store_id = b.store_id');
		$this->db->where('b.store_id !=', null);
		$this->db->group_by('a.product_id');
		$this->db->order_by('a.product_name', 'asc');

		if (empty($product_id)) {
			$this->db->where(array('a.status' => 1));
		} else {
			//Single product information
			$this->db->where(array('a.status' => 1, 'e.purchase_date <= ' => $date, 'a.product_id' => $product_id));
		}
		$query = $this->db->get();
		return $query->num_rows();
	}

	//Stock report product by date
	public function stock_report_product_bydate($product_id, $supplier_id, $from_date, $to_date, $per_page, $page) {

		$this->db->select("
			a.product_name,
			a.unit,
			a.product_id,
			a.price,
			a.supplier_price,
			a.product_model,
			c.category_name,
			sum(b.quantity) as 'totalPurchaseQnty',
			e.purchase_date as date
			");

		$this->db->from('product_information a');
		$this->db->join('product_purchase_details b', 'b.product_id = a.product_id', 'left');
		$this->db->join('product_category c', 'c.category_id = a.category_id');
		$this->db->join('product_purchase e', 'e.purchase_id = b.purchase_id');
		$this->db->join('unit f', 'f.unit_id = a.unit', 'left');
		$this->db->join('variant g', 'g.variant_id = b.variant_id', 'left');
		$this->db->join('store_set h', 'h.store_id = b.store_id');
		$this->db->where('b.store_id !=', null);
		$this->db->where('a.delete_time', null); //added
		$this->db->group_by('a.product_id');
		$this->db->order_by('a.product_name', 'asc');
		$this->db->limit($per_page, $page);

		if (empty($supplier_id)) {
			$this->db->where(array('a.status' => 1));
		} else {
			$this->db->where(
				array(
					'a.status' => 1,
					'a.supplier_id' => $supplier_id,
					'a.product_id' => $product_id,
				)
			);
			$this->db->where("STR_TO_DATE(e.purchase_date, '%m-%d-%Y') BETWEEN DATE('" . $from_date . "') AND DATE('" . $to_date . "')");
		}
		$query = $this->db->get();
		return $query->result_array();
	}

	//Stock report product by date count
	public function stock_report_product_bydate_count($product_id, $supplier_id, $from_date, $to_date) {

		$this->db->select("
			a.product_name,
			a.unit,
			a.product_id,
			a.price,
			a.supplier_price,
			a.product_model,
			c.category_name,
			sum(b.quantity) as 'totalPurchaseQnty',
			e.purchase_date as date
			");

		$this->db->from('product_information a');
		$this->db->join('product_purchase_details b', 'b.product_id = a.product_id', 'left');
		$this->db->join('product_category c', 'c.category_id = a.category_id');
		$this->db->join('product_purchase e', 'e.purchase_id = b.purchase_id');
		$this->db->join('unit f', 'f.unit_id = a.unit', 'left');
		$this->db->join('variant g', 'g.variant_id = b.variant_id', 'left');
		$this->db->join('store_set h', 'h.store_id = b.store_id');
		$this->db->where('b.store_id !=', null);
		$this->db->where('a.delete_time', null); //added
		$this->db->group_by('a.product_id');
		$this->db->order_by('a.product_name', 'asc');

		if (empty($supplier_id)) {
			$this->db->where(array('a.status' => 1));
		} else {
			$this->db->where(
				array(
					'a.status' => 1,
					'a.supplier_id' => $supplier_id,
					'a.product_id' => $product_id,
				)
			);
			$this->db->where("STR_TO_DATE(e.purchase_date, '%m-%d-%Y') BETWEEN DATE('" . $from_date . "') AND DATE('" . $to_date . "')");
		}
		$query = $this->db->get();
		return $query->num_rows();
	}

	//Stock report supplier by date
	public function stock_report_supplier_bydate($product_id, $supplier_id, $date, $perpage, $page) {

		$this->db->select("
			a.product_name,
			a.unit,
			a.product_id,
			a.price,
			a.supplier_price,
			a.product_model,
			c.category_name,
			sum(b.quantity) as 'totalPrhcsCtn',
			e.purchase_date as purchase_date,
			");

		$this->db->from('product_information a');
		$this->db->join('product_purchase_details b', 'b.product_id = a.product_id', 'left');
		$this->db->join('product_category c', 'c.category_id = a.category_id');
		$this->db->join('product_purchase e', 'e.purchase_id = b.purchase_id');
		$this->db->join('unit f', 'f.unit_id = a.unit', 'left');
		$this->db->join('variant g', 'g.variant_id = b.variant_id', 'left');
		$this->db->join('store_set h', 'h.store_id = b.store_id');
		$this->db->where('b.store_id !=', null);
		$this->db->where('a.delete_time', null); //added
		$this->db->group_by('a.product_id');
		$this->db->order_by('a.product_name', 'asc');

		if (empty($supplier_id)) {
			$this->db->where(array('a.status' => 1));
		} else {
			$this->db->where('a.status', 1);
			$this->db->where('e.purchase_date <=', $date);
			$this->db->where('a.supplier_id', $supplier_id);
		}
		$this->db->limit($perpage, $page);
		$query = $this->db->get();
		return $query->result_array();
	}

	//Counter of unique product histor which has been affected
	public function product_counter_by_supplier($supplier_id, $date) {
		$this->db->select("
			a.product_name,
			a.unit,
			a.product_id,
			a.price,
			a.supplier_price,
			a.product_model,
			c.category_name,
			sum(b.quantity) as 'totalPrhcsCtn',
			e.purchase_date as purchase_date,
			");

		$this->db->from('product_information a');
		$this->db->join('product_purchase_details b', 'b.product_id = a.product_id', 'left');
		$this->db->join('product_category c', 'c.category_id = a.category_id');
		$this->db->join('product_purchase e', 'e.purchase_id = b.purchase_id');
		$this->db->join('unit f', 'f.unit_id = a.unit', 'left');
		$this->db->join('variant g', 'g.variant_id = b.variant_id', 'left');
		$this->db->join('store_set h', 'h.store_id = b.store_id');
		$this->db->where('b.store_id !=', null);
		$this->db->group_by('a.product_id');
		$this->db->order_by('a.product_name', 'asc');

		if (empty($supplier_id)) {
			$this->db->where(array('a.status' => 1));
		} else {
			$this->db->where('a.status', 1);
			$this->db->where('e.purchase_date <=', $date);
			$this->db->where('a.supplier_id', $supplier_id);
		}
		$query = $this->db->get();
		return $query->num_rows();
	}

//get stock report store wise
	public function stock_report_by_store($from_date, $to_date, $store_id, $perpage, $page, $product_id = false) {
		$this->db->select("
			a.product_name,
			a.unit,
			a.product_id,
			a.price,
			a.supplier_price,
			a.product_model,
			c.category_name,
			b.date_time as purchase_date,
            g.variant_name,
			g.variant_id,
            ga.variant_id as variant_color,
            ga.variant_name as variant_color_name,
			h.store_name,
			h.store_id,
			");
		$wh = "date_format( str_to_date( date_time, '%m-%d-%Y' ) , '%Y-%m-%d' ) Between '" . $from_date . "' AND'" . $to_date . "'";

		$this->db->from('product_information a');
		$this->db->join('transfer b', 'b.product_id = a.product_id', 'left');
		$this->db->join('product_category c', 'c.category_id = a.category_id');
		$this->db->join('unit f', 'f.unit_id = a.unit', 'left');
		$this->db->join('variant g', 'g.variant_id = b.variant_id', 'left');
		$this->db->join('variant ga', 'ga.variant_id = b.variant_color', 'left');
		$this->db->join('store_set h', 'h.store_id = b.store_id');
		$this->db->group_by('a.product_id');
		$this->db->group_by('g.variant_id');
		$this->db->group_by('h.store_id');
		$this->db->order_by('a.product_name', 'asc');

		$this->db->where('a.status', 1);
		$this->db->where('a.delete_time', NULL); //ADDED
		$this->db->where('b.store_id', $store_id);
		if (!empty($product_id)) {
			$this->db->where('a.product_id', $product_id);
		}
		$this->db->where($wh);

		$this->db->limit($perpage, $page);
		$query = $this->db->get();
		return $query->result_array();
	}

//Stock report variant by date
	public function stock_report_variant_bydate($from_date, $to_date, $store_id, $perpage, $page) {

		$this->db->select("
			a.product_name,
			a.unit,
			a.product_id,
			a.price,
			a.supplier_price,
			a.product_model,
			c.category_name,
			sum(b.quantity) as totalPrhcsCtn,
			b.date_time as purchase_date,
			g.variant_name,
			g.variant_id,
			h.store_name,
			h.store_id,
			");

		$this->db->from('product_information a');
		$this->db->join('transfer b', 'b.product_id = a.product_id', 'left');
		$this->db->join('product_category c', 'c.category_id = a.category_id');
		$this->db->join('unit f', 'f.unit_id = a.unit', 'left');
		$this->db->join('variant g', 'g.variant_id = b.variant_id', 'left');
		$this->db->join('store_set h', 'h.store_id = b.store_id');
		$this->db->where('b.store_id !=', null);
		$this->db->where('b.t_store_id =', null);
		$this->db->group_by('a.product_id');
		$this->db->group_by('g.variant_id');
		$this->db->group_by('h.store_id');
		$this->db->order_by('a.product_name', 'asc');

		if (empty($from_date)) {
			$this->db->where('a.status', 1);
		} else {
			$this->db->where('a.status', 1);
			$this->db->where('b.date_time >=', $from_date);
			$this->db->where('b.date_time <=', $to_date);
			$this->db->where('b.store_id', $store_id);
		}
		$this->db->limit($perpage, $page);
		$query = $this->db->get();
		return $query->result_array();
	}

	//Counter of unique product histor which has been affected
	public function stock_report_variant_bydate_count($from_date, $to_date, $store_id, $product_id = false) {

		$this->db->select("
			a.product_name,
			a.unit,
			a.product_id,
			a.price,
			a.supplier_price,
			a.product_model,
			c.category_name,
			b.date_time as purchase_date,
			g.variant_name,
			g.variant_id,
			h.store_name,
			h.store_id,
			");
		$wh = "date_format( str_to_date( date_time, '%m-%d-%Y' ) , '%Y-%m-%d' ) Between '" . $from_date . "' AND'" . $to_date . "'";
		$this->db->from('product_information a');
		$this->db->join('transfer b', 'b.product_id = a.product_id', 'left');
		$this->db->join('product_category c', 'c.category_id = a.category_id');
		$this->db->join('unit f', 'f.unit_id = a.unit', 'left');
		$this->db->join('variant g', 'g.variant_id = b.variant_id', 'left');
		$this->db->join('store_set h', 'h.store_id = b.store_id');
		$this->db->group_by('a.product_id');
		$this->db->group_by('g.variant_id');
		$this->db->group_by('h.store_id');
		$this->db->order_by('a.product_name', 'asc');

		$this->db->where('a.status', 1);
		$this->db->where('a.delete_time', NULL); //added

		$this->db->where('b.store_id', $store_id);
		if (!empty($product_id)) {
			$this->db->where('a.product_id', $product_id);
		}
		$this->db->where($wh);
		$query = $this->db->get();

		return $query->num_rows();
	}

// ====================STORE WISE STOCK REPORT =======================
	public function store_wise_product($per_page = NULL, $page = NULL) {

		$this->db->select('
			a.store_name,
			a.store_id,
			b.*,
			SUM(b.quantity) as quantity,
			c.product_name,
			c.product_id,
			c.product_model,
			d.variant_name,
			d.variant_id
			');
		$this->db->from('store_set a');
		$this->db->join('transfer b', 'a.store_id= b.store_id', 'left');
		$this->db->join('product_information c', 'c.product_id = b.product_id');
		$this->db->join('variant d', 'd.variant_id = b.variant_id');

		$this->db->group_by('b.product_id');
		$this->db->group_by('b.variant_id');
		$this->db->group_by('a.store_id');
		$this->db->order_by('a.store_name', 'asc');
		$this->db->limit($per_page, $page);
		$query = $this->db->get();

		$result = $query->result();

		$test = [];
		foreach ($result as $product) {

			$sql = 'select sum(a.quantity) as sell from order_details a  where store_id="' . $product->store_id . '" and product_id="' . $product->product_id . '" and variant_id="' . $product->variant_id . '"';
			$product_sell = $this->db->query($sql)->row();
			$test[] = array_merge((array) $product, (array) $product_sell);
		}

		if ($test != NULl && !empty($test)) {
			return $test;
		}
		return false;

	}

	// ====================INDIVIDUAL STORE WISE STOCK REPORT =======================
	public function individual_store_wise_product($per_page = NULL, $page = NULL) {

		$store_id = $this->session->userdata('store_id');

		$this->db->select('
			a.store_name,
			a.store_id,
			b.*,
			SUM(b.quantity) as quantity,
			c.product_name,
			c.product_id,
			c.product_model,
			d.variant_name,
			d.variant_id
			');
		$this->db->from('store_set a');
		$this->db->join('transfer b', 'a.store_id= b.store_id', 'left');
		$this->db->join('product_information c', 'c.product_id = b.product_id');
		$this->db->join('variant d', 'd.variant_id = b.variant_id');

		$this->db->where('b.store_id=', $store_id);
		$this->db->group_by('b.product_id');
		$this->db->group_by('b.variant_id');
		$this->db->group_by('a.store_id');
		$this->db->order_by('a.store_name', 'asc');
		$this->db->limit($per_page, $page);
		$query = $this->db->get();

		$result = $query->result();

		$test = [];
		foreach ($result as $product) {

			$sql = 'select sum(a.quantity) as sell from invoice_details a  where store_id="' . $store_id . '" and product_id="' . $product->product_id . '" and variant_id="' . $product->variant_id . '"';
			$product_sell = $this->db->query($sql)->row();
			$test[] = array_merge((array) $product, (array) $product_sell);
		}

		if ($test != NULl && !empty($test)) {
			return $test;
		}
		return false;

	}

//=============sales report store wise=================

	public function retrieve_sales_report_store_wise($store_id, $start_date = null, $end_date = null) {
		$dateRange = "STR_TO_DATE(a.date, '%m-%d-%Y') BETWEEN DATE('$start_date') AND DATE('$end_date')";
		$this->db->select("a.*,b.store_name");
		$this->db->from('invoice a');
		$this->db->join('store_set b', 'a.store_id = b.store_id');
		$this->db->where('a.store_id', $store_id);
		$this->db->group_start();
		$this->db->where('a.invoice_status !=', 2); //cancel
		$this->db->where('a.invoice_status !=', 6); //return
		$this->db->group_end();
		$this->db->where('a.delete_time', NULL); //added
		if ($dateRange) {
			$this->db->where($dateRange, NULL, FALSE);
		}
		$this->db->order_by('a.date', 'desc');
		$this->db->limit('500');
		$query = $this->db->get();
		return $query->result_array();
	}

	//Retrieve todays_sales_report
	public function todays_total_sales_report() {
		$today = date('m-d-Y');

		$this->db->select('date,
			invoice,
			SUM(total_amount) as total_sale');
		$this->db->from('invoice');
		$this->db->where('date', $today);
		$query = $this->db->get();
		return $query->result_array();
	}

	//Retrieve todays_total_purchase_report
	public function todays_total_purchase_report() {
		$today = date('m-d-Y');

		$this->db->select('purchase_date,
			invoice_no,
			SUM(grand_total_amount) as total_purchase');
		$this->db->from('product_purchase');
		$this->db->where('purchase_date', $today);
		$query = $this->db->get();
		return $query->result_array();
	}

	//Retrieve todays_total_discount_report
	public function todays_total_discount_report() {
		$today = date('m-d-Y');

		$this->db->select('date,
			invoice,
			SUM(invoice_discount) as total_discount');
		$this->db->from('invoice');
		$this->db->where('date', $today);
		$query = $this->db->get();
		return $query->result_array();
	}

	//Retrieve todays_sales_report
	public function todays_sales_report($per_page, $page) {
		$today = date('m-d-Y');
		$this->db->select("a.*,b.customer_id,b.customer_name");
		$this->db->from('invoice a');
		$this->db->join('customer_information b', 'b.customer_id = a.customer_id');
		$this->db->where('a.date', $today);
		$this->db->group_start();
		$this->db->where('a.invoice_status !=', 2); //cancel
		$this->db->where('a.invoice_status !=', 6); //return
		$this->db->group_end();
		$this->db->where('a.delete_time', NULL); //added
		$this->db->limit($per_page, $page);
		$this->db->order_by('a.invoice_id', 'desc');
		$query = $this->db->get();
		return $query->result_array();
	}

	//Retrieve todays_sales_report_count
	public function todays_sales_report_count() {
		$today = date('m-d-Y');
		$this->db->select("a.*,b.customer_id,b.customer_name");
		$this->db->from('invoice a');
		$this->db->join('customer_information b', 'b.customer_id = a.customer_id');
		$this->db->where('a.date', $today);
		$this->db->group_start();
		$this->db->where('a.invoice_status !=', 2); //cancel
		$this->db->where('a.invoice_status !=', 6); //return
		$this->db->group_end();
		$this->db->where('a.delete_time', NULL); //added
		$this->db->order_by('a.invoice_id', 'desc');
		$query = $this->db->get();
		// echo $this->db->last_query();
		return $query->num_rows();
	}

	//Retrieve store_to_store_transfer
	public function store_to_store_transfer($from_date = false, $to_date = false, $from_store = false, $to_store = false) {
		$today = date("m-d-Y");
		$this->db->select("
			a.*,
			b.store_name,
			c.product_name,
			c.product_model,
			d.variant_name,
			e.store_name as t_store_name
			");
		$this->db->from('transfer a');
		$this->db->join('store_set b', 'b.store_id = a.store_id');
		$this->db->join('product_information c', 'c.product_id = a.product_id');
		$this->db->join('variant d', 'd.variant_id = a.variant_id');
		$this->db->join('store_set e', 'e.store_id = a.t_store_id');

		if (!empty($from_store)) {
			$this->db->where('a.store_id', $from_store);
		}
		if (!empty($to_store)) {
			$this->db->where('a.t_store_id', $to_store);
		}

		if (!empty($from_date) && !empty($to_date)) {
			$dateRange = "STR_TO_DATE(a.date_time, '%m-%d-%Y') BETWEEN DATE('$from_date') AND DATE('$to_date')";
		}

		$this->db->where('a.status', 1);
		$this->db->order_by('a.transfer_id', 'desc');
		$query = $this->db->get();
		return $query->result_array();
	}

	//Retrieve store_to_warehouse_transfer
	public function store_to_warehouse_transfer($from_date, $to_date, $from_store, $t_wearhouse) {
		$today = date("m-d-Y");
		$this->db->select("
			a.*,
			b.store_name,
			c.product_name,
			c.product_model,
			d.variant_name,
			e.wearhouse_name as t_wearhouse_name
			");
		$this->db->from('transfer a');
		$this->db->join('store_set b', 'b.store_id = a.store_id');
		$this->db->join('product_information c', 'c.product_id = a.product_id');
		$this->db->join('variant d', 'd.variant_id = a.variant_id');
		$this->db->join('wearhouse_set e', 'e.wearhouse_id = a.t_warehouse_id');
		if (!empty($from_store)) {
			$this->db->where('a.date_time >=', $from_date);
			$this->db->where('a.date_time <=', $to_date);
			$this->db->where('a.store_id', $from_store);
			$this->db->where('a.t_warehouse_id', $t_wearhouse);
		}
		$this->db->where('a.status', 2);
		$this->db->order_by('a.transfer_id', 'desc');
		$query = $this->db->get();
		return $query->result_array();
	}

	//Retrieve store_to_warehouse_transfer
	public function warehouse_to_store_transfer($from_date, $to_date, $wearhouse, $t_store) {
		$today = date("m-d-Y");
		$this->db->select("
			a.*,
			b.store_name,
			c.product_name,
			c.product_model,
			d.variant_name,
			e.wearhouse_name
			");
		$this->db->from('transfer a');
		$this->db->join('store_set b', 'b.store_id = a.t_store_id');
		$this->db->join('product_information c', 'c.product_id = a.product_id');
		$this->db->join('variant d', 'd.variant_id = a.variant_id');
		$this->db->join('wearhouse_set e', 'e.wearhouse_id = a.warehouse_id');
		if (!empty($wearhouse)) {
			$this->db->where('a.date_time >=', $from_date);
			$this->db->where('a.date_time <=', $to_date);
			$this->db->where('a.warehouse_id', $wearhouse);
			$this->db->where('a.t_store_id', $t_store);
		}
		$this->db->where('a.status', 3);
		$this->db->order_by('a.transfer_id', 'desc');
		$query = $this->db->get();
		return $query->result_array();
	}

	//Retrieve store_to_warehouse_transfer
	public function warehouse_to_warehouse_transfer($from_date, $to_date, $wearhouse, $t_wearhouse) {
		$today = date("m-d-Y");
		$this->db->select("
			a.*,
			b.wearhouse_name,
			c.product_name,
			c.product_model,
			d.variant_name,
			e.wearhouse_name as t_wearhouse_name
			");
		$this->db->from('transfer a');
		$this->db->join('wearhouse_set b', 'b.wearhouse_id = a.warehouse_id');
		$this->db->join('product_information c', 'c.product_id = a.product_id');
		$this->db->join('variant d', 'd.variant_id = a.variant_id');
		$this->db->join('wearhouse_set e', 'e.wearhouse_id = a.t_warehouse_id');
		if (!empty($wearhouse)) {
			$this->db->where('a.date_time >=', $from_date);
			$this->db->where('a.date_time <=', $to_date);
			$this->db->where('a.warehouse_id', $wearhouse);
			$this->db->where('a.t_warehouse_id', $t_wearhouse);
		}
		$this->db->where('a.status', 4);
		$this->db->order_by('a.transfer_id', 'desc');
		$query = $this->db->get();
		return $query->result_array();
	}

	//Retrieve todays_purchase_report
	public function todays_purchase_report($per_page = null, $page = null) {
		$today = date('m-d-Y');
		$this->db->select("a.*,b.supplier_id,b.supplier_name");
		$this->db->from('product_purchase a');
		$this->db->join('supplier_information b', 'b.supplier_id = a.supplier_id');
		$this->db->where('a.purchase_date', $today);
		$this->db->order_by('a.purchase_id', 'desc');
		$this->db->limit($per_page, $page);
		$query = $this->db->get();
		return $query->result_array();
	}

	//Retrieve todays_purchase_report count
	public function todays_purchase_report_count() {
		$today = date('m-d-Y');
		$this->db->select("a.*,b.supplier_id,b.supplier_name");
		$this->db->from('product_purchase a');
		$this->db->join('supplier_information b', 'b.supplier_id = a.supplier_id');
		$this->db->where('a.purchase_date', $today);
		$this->db->order_by('a.purchase_id', 'desc');
		$this->db->limit('500');
		$query = $this->db->get();
		return $query->num_rows();
	}

	//Total profit report
	public function total_profit_report($perpage, $page) {

		$this->db->select("a.date,a.invoice,b.invoice_id,
			CAST(sum(total_price) AS DECIMAL(16,2)) as total_sale");
		$this->db->select('CAST(sum(`quantity`*`supplier_rate`) AS DECIMAL(16,2)) as total_supplier_rate', FALSE);
		$this->db->select("CAST(SUM(total_price) - SUM(`quantity`*`supplier_rate`) AS DECIMAL(16,2)) AS total_profit");
		$this->db->from('invoice a');
		$this->db->join('invoice_details b', 'b.invoice_id = a.invoice_id');
		$this->db->group_by('b.invoice_id');
		$this->db->order_by('a.invoice', 'desc');
		$this->db->limit($perpage, $page);
		$query = $this->db->get();
		return $query->result_array();
	}

	//Total profit report
	public function total_profit_report_count() {

		$this->db->select("a.date,a.invoice,b.invoice_id,sum(total_price) as total_sale");
		$this->db->select('sum(`quantity`*`supplier_rate`) as total_supplier_rate', FALSE);
		$this->db->select("(SUM(total_price) - SUM(`quantity`*`supplier_rate`)) AS total_profit");
		$this->db->from('invoice a');
		$this->db->join('invoice_details b', 'b.invoice_id = a.invoice_id');
		$this->db->group_by('b.invoice_id');
		$this->db->order_by('a.invoice', 'desc');
		$query = $this->db->get();
		return $query->num_rows();
	}

	//Tax report product wise
	public function tax_report_product_wise($from_date, $to_date) {

		$today = date("m-d-Y");
		$this->db->select("
			a.amount,
			a.date,
			a.invoice_id,
			b.product_name,
			c.tax_name
			");
		$this->db->from('tax_collection_details a');
		$this->db->join('product_information b', 'b.product_id = a.product_id', 'left');
		$this->db->join('tax c', 'c.tax_id = a.tax_id');

		if (empty($from_date)) {
			$this->db->where('a.date', $today);
		} else {
			$this->db->where("STR_TO_DATE(a.date, '%m-%d-%Y') BETWEEN DATE('$from_date') AND DATE('$to_date')");
		}
		$this->db->order_by('a.date', 'asc');
		$query = $this->db->get();
		return $query->result_array();
	}

	//Tax report product wise
	public function tax_report_invoice_wise($from_date = false, $to_date = false) {

		$today = date("m-d-Y");
		$this->db->select("
			a.tax_amount,
			a.date,
			a.invoice_id,
			c.tax_name
			");
		$this->db->from('tax_collection_summary a');
		$this->db->join('tax c', 'c.tax_id = a.tax_id');

		if (empty($from_date)) {
			$this->db->where('a.date', $today);
		} else {
			$this->db->where("STR_TO_DATE(a.date, '%m-%d-%Y') BETWEEN DATE('$from_date') AND DATE('$to_date')");
		}
		$this->db->order_by('a.date', 'asc');
		$query = $this->db->get();
		return $query->result_array();
	}

	public function monthly_sales_report() {
		$query1 = $this->db->query("
			SELECT
			date,
			EXTRACT(MONTH FROM STR_TO_DATE(date,'%m-%d-%Y')) as month,
			COUNT(invoice_id) as total
			FROM
			invoice
			WHERE
			EXTRACT(YEAR FROM STR_TO_DATE(date,'%m-%d-%Y'))  >= EXTRACT(YEAR FROM NOW())
			GROUP BY
			EXTRACT(YEAR_MONTH FROM STR_TO_DATE(date,'%m-%d-%Y'))
			ORDER BY
			month ASC
			")->result();

		$query2 = $this->db->query("
			SELECT
			purchase_date,
			EXTRACT(MONTH FROM STR_TO_DATE(purchase_date,'%m-%d-%Y')) as month,
			COUNT(purchase_id) as total_month
			FROM
			product_purchase
			WHERE
			EXTRACT(YEAR FROM STR_TO_DATE(purchase_date,'%m-%d-%Y'))  >= EXTRACT(YEAR FROM NOW())
			GROUP BY
			EXTRACT(YEAR_MONTH FROM STR_TO_DATE(purchase_date,'%m-%d-%Y'))
			ORDER BY
			month ASC
			")->result();

		return [$query1, $query2];
	}

	//Retrieve all Report
	public function retrieve_dateWise_SalesReports($start_date = false, $end_date = false) {
		$dateRange = "STR_TO_DATE(a.date, '%m-%d-%Y') BETWEEN DATE('$start_date') AND DATE('$end_date')";

		$this->db->select("a.*,b.customer_id,b.customer_name");
		$this->db->from('invoice a');
		$this->db->join('customer_information b', 'b.customer_id = a.customer_id');
		$this->db->where($dateRange, NULL, FALSE);
		$this->db->order_by('a.date', 'desc');
		$this->db->limit('500');
		$query = $this->db->get();
		return $query->result_array();
	}

	//Retrieve all Report
	public function retrieve_dateWise_PurchaseReports($start_date, $end_date) {
		$dateRange = "STR_TO_DATE(a.purchase_date, '%m-%d-%Y') BETWEEN DATE('$start_date') AND DATE('$end_date')";

		$this->db->select("a.*,b.supplier_id,b.supplier_name");
		$this->db->from('product_purchase a');
		$this->db->join('supplier_information b', 'b.supplier_id = a.supplier_id');
		$this->db->where($dateRange, NULL, FALSE);
		$this->db->order_by('a.purchase_date', 'desc');
		$this->db->limit('500');
		$query = $this->db->get();
		return $query->result_array();
	}

	//Retrieve date wise profit report
	public function retrieve_dateWise_profit_report($start_date, $end_date) {
		$this->db->select("a.date,a.invoice,b.invoice_id,
			CAST(sum(total_price) AS DECIMAL(16,2)) as total_sale");
		$this->db->select('CAST(sum(`quantity`*`supplier_rate`) AS DECIMAL(16,2)) as total_supplier_rate', FALSE);
		$this->db->select("CAST(SUM(total_price) - SUM(`quantity`*`supplier_rate`) AS DECIMAL(16,2)) AS total_profit");

		$this->db->from('invoice a');
		$this->db->join('invoice_details b', 'b.invoice_id = a.invoice_id');

		$this->db->where('a.date >=', $start_date);
		$this->db->where('a.date <=', $end_date);

		$this->db->group_by('b.invoice_id');
		$this->db->order_by('a.invoice', 'desc');
		$query = $this->db->get();
		return $query->result_array();
	}

	//Retrieve date wise profit report
	public function retrieve_dateWise_profit_report_count($start_date, $end_date) {
		$this->db->select("a.date,a.invoice,b.invoice_id,sum(total_price) as total_sale");
		$this->db->select('sum(`quantity`*`supplier_rate`) as total_supplier_rate', FALSE);
		$this->db->select("(SUM(total_price) - SUM(`quantity`*`supplier_rate`)) AS total_profit");

		$this->db->from('invoice a');
		$this->db->join('invoice_details b', 'b.invoice_id = a.invoice_id');

		$this->db->where('a.date >=', $start_date);
		$this->db->where('a.date <=', $end_date);

		$this->db->group_by('b.invoice_id');
		$this->db->order_by('a.invoice', 'desc');
		$query = $this->db->get();
		return $query->num_rows();
	}

	//Product wise sales report
	public function product_wise_report() {
		$today = date('m-d-Y');
		$this->db->select("a.*,b.customer_id,b.customer_name");
		$this->db->from('invoice a');
		$this->db->join('customer_information b', 'b.customer_id = a.customer_id');
		$this->db->where('a.date', $today);
		$this->db->order_by('a.invoice_id', 'desc');
		$this->db->limit('500');
		$query = $this->db->get();
		return $query->result_array();
	}

	//RETRIEVE DATE WISE SEARCH SINGLE PRODUCT REPORT
	public function retrieve_product_search_sales_report($start_date, $end_date) {
		$dateRange = "STR_TO_DATE(c.date, '%m-%d-%Y') BETWEEN DATE('$start_date') AND DATE('$end_date')";
		$this->db->select("a.*,b.product_name,b.product_model,c.date,d.customer_name");
		$this->db->from('invoice_details a');
		$this->db->join('product_information b', 'b.product_id = a.product_id');
		$this->db->join('invoice c', 'c.invoice_id = a.invoice_id');
		$this->db->join('customer_information d', 'd.customer_id = c.customer_id');
		$this->db->where($dateRange, NULL, FALSE);
		$this->db->order_by('c.date', 'desc');
		$query = $this->db->get();
		return $query->result_array();
	}

	//RETRIEVE DATE WISE SEARCH SINGLE PRODUCT REPORT
	public function retrieve_product_search_sales_report_count($start_date, $end_date) {
		$dateRange = "STR_TO_DATE(c.date, '%m-%d-%Y') BETWEEN DATE('$start_date') AND DATE('$end_date')";

		$this->db->select("a.*,b.product_name,b.product_model,c.date,d.customer_name");
		$this->db->from('invoice_details a');
		$this->db->join('product_information b', 'b.product_id = a.product_id');
		$this->db->join('invoice c', 'c.invoice_id = a.invoice_id');
		$this->db->join('customer_information d', 'd.customer_id = c.customer_id');
		$this->db->where($dateRange, NULL, FALSE);
		$this->db->order_by('c.date', 'desc');
		$query = $this->db->get();
		return $query->num_rows();
	}

	//Retrieve company Edit Data
	public function retrieve_company() {
		$this->db->select('*');
		$this->db->from('company_information');
		$this->db->limit('1');
		$query = $this->db->get();
		if ($query->num_rows() > 0) {
			return $query->result_array();
		}
		return false;
	}

	public function get_stock_items($id) {
		$this->db->select("
			a.product_name,
			a.product_id,
            a.variants
			");

		$this->db->from('product_information a');

		$this->db->where('a.product_id', $id);
		$query = $this->db->get();
		$product = $query->result_array();
		$variants = explode(',', $product[0]['variants']);
		$product_info = [
			'product_name' => $product[0]['product_name'],
			'variants' => $variants,
		];
		//====================================

		foreach ($product_info['variants'] as $variant):

			$this->db->select("a.variant_id, a.variant_name, sum(b.quantity) as totalPurchaseQnty,
																																																																																																																																																        (select sum(c.quantity) from invoice_details c where c.product_id=b.product_id and c.variant_id=b.variant_id) as totalSalesQty,
																																																																																																																																																         a.variant_name");

			$this->db->from('variant a', null, false);
			$this->db->join('product_purchase_details b', 'a.variant_id=b.variant_id');

			$this->db->where('a.variant_id', $variant);
			$this->db->where('b.product_id', $id);
			$query = $this->db->get();
			$tt[] = $query->result_array();

		endforeach;

		$data = [
			'product_name' => $product[0]['product_name'],
			'products' => $tt,
		];

		return $data;

	}

	public function product_analysis_report($per_page, $page) {
		$today = date('m-d-Y');
		$this->db->select("a.*,b.customer_id,b.customer_name");
		$this->db->from('invoice a');
		$this->db->join('customer_information b', 'b.customer_id = a.customer_id');
		$this->db->where('a.date', $today);
		$this->db->group_start();
		$this->db->where('a.invoice_status !=', 2); //cancel
		$this->db->where('a.invoice_status !=', 6); //return
		$this->db->group_end();
		$this->db->where('a.delete_time', NULL); //added
		$this->db->limit($per_page, $page);
		$this->db->order_by('a.invoice_id', 'desc');
		$query = $this->db->get();
		return $query->result_array();
	}
	public function retrieve_dateWise_product_SalesReports($start_date = false, $end_date = false, $product_category = '', $product_name_select = '') {
		$dateRange = "STR_TO_DATE(a.date, '%m-%d-%Y') BETWEEN DATE('$start_date') AND DATE('$end_date')";

		$this->db->select("a.*,b.customer_id,b.customer_name,c.*,d.product_name_ch,d.category_id,e.category_name_ch");
		$this->db->from('invoice a');
		$this->db->join('customer_information b', 'b.customer_id = a.customer_id');
		$this->db->join('order_details c', 'c.order_id = a.order_id');
		$this->db->join('product_information d', 'd.product_id = c.product_id');
		$this->db->join('product_category e', 'e.category_id = d.category_id');
		$this->db->where($dateRange, NULL, FALSE);
		if (!empty($product_category)) {
			$this->db->where('d.category_id', $product_category);
		}

		if (!empty($product_name_select) && $product_name_select != 'all') {
			$this->db->where('d.product_id', $product_name_select);
		}
		$this->db->order_by('a.date', 'desc');
		$this->db->order_by('a.order_id', 'desc');
		$this->db->limit('500');
		$query = $this->db->get();

		// echo $this->db->last_query();
		return $query->result_array();
	}
	public function retrieve_dateWise_customer_purchase_report($per_page, $page, $from_date = '', $to_date = '', $gender = '', $age_range = '') {

		// echo "from_date";
		// echo $from_date;
		// echo "<br>";
		// echo "<br>";
		$dateRange = "STR_TO_DATE(a.date, '%m-%d-%Y') BETWEEN DATE('$from_date') AND DATE('$to_date')";

		$this->db->select("a.*,b.customer_id,b.customer_name,c.*,d.product_name_ch,d.category_id,e.category_name_ch,b.gender,b.birthday");
		$this->db->from('invoice a');
		$this->db->join('customer_information b', 'b.customer_id = a.customer_id');
		$this->db->join('order_details c', 'c.order_id = a.order_id');
		$this->db->join('product_information d', 'd.product_id = c.product_id');
		$this->db->join('product_category e', 'e.category_id = d.category_id');
		$this->db->where($dateRange, NULL, FALSE);
		// if (!empty($product_category)) {
		// 	$this->db->where('d.category_id', $product_category);
		// }

		// if (!empty($product_name_select) && $product_name_select != 'all') {
		// 	$this->db->where('d.product_id', $product_name_select);
		// }
		if (!empty($gender)) {
			$this->db->where('b.gender', $gender);
		}
		if (!empty($age_range)) {

			if ($age_range == 1) {
				//60-100
				$start_age_date = '1800-10-11';
				$end_age_date = '1959-10-10';
			}
			if ($age_range == 2) {
				//40-59
				$start_age_date = '1959-10-11';
				$end_age_date = '1979-10-10';
			}
			if ($age_range == 3) {
				//20-39
				$start_age_date = '1979-10-11';
				$end_age_date = '1999-10-10';

			}
			if ($age_range == 4) {
				//0-19
				$start_age_date = '1999-10-11';
				$end_age_date = '2030-10-10';
			}
			$birthdayRange = "STR_TO_DATE(b.birthday, '%Y-%m-%d') BETWEEN DATE('$start_age_date') AND DATE('$end_age_date')";
			$this->db->where($birthdayRange, NULL, FALSE);
		}
		$this->db->order_by('a.date', 'desc');
		$this->db->order_by('a.order_id', 'desc');
		$this->db->limit('500');
		$query = $this->db->get();

		// echo $this->db->last_query();
		return $query->result_array();
	}
	public function non_active_customer($per_page, $page, $from_date = '', $to_date = '', $non_active_range = '') {

		//non active customer
		// SELECT `b`.* FROM `customer_information` `b` WHERE NOT EXISTS (SELECT customer_id FROM invoice a Where a.customer_id = b.customer_id ) LIMIT 500

		$this->db->select("b.*");
		$this->db->from('customer_information b');
		$today = date('m-d-Y');
		if ($non_active_range == 1) {
			$newdate = date('m-d-Y', strtotime('-3 months', strtotime($today)));
		}
		if ($non_active_range == 2) {
			$newdate = date('m-d-Y', strtotime('-6 months', strtotime($today)));
		}
		if ($non_active_range == 3) {
			$newdate = date('m-d-Y', strtotime('-12 months', strtotime($today)));
		}
		if ($non_active_range == 4) {
			$newdate = date('m-d-Y', strtotime('-24 months', strtotime($today)));
		}

		$newdate = date('m-d-Y', strtotime('-3 months', strtotime($today)));
		// $from_date = date('Y-m-d', strtotime("+3 months", now));
		$whereSql = "NOT EXISTS(SELECT customer_id FROM invoice a Where a.customer_id = b.customer_id  AND  " . "STR_TO_DATE(a.date, '%m-%d-%Y') BETWEEN DATE('$today') AND DATE('$newdate') )";
		$this->db->where($whereSql);

		//end non active customer

		// echo "from_date";
		// echo $from_date;
		// echo "<br>";
		// echo "<br>";
		// $dateRange = "STR_TO_DATE(a.date, '%m-%d-%Y') BETWEEN DATE('$from_date') AND DATE('$to_date')";

		// $this->db->select("a.*,b.customer_id,b.customer_name,c.*,d.product_name_ch,d.category_id,e.category_name_ch,b.gender,b.birthday");
		// // $this->db->from('invoice a');
		// $this->db->from('customer_information b');
		// // $this->db->join('customer_information b', 'b.customer_id = a.customer_id');
		// $this->db->join('invoice a', 'b.customer_id = a.customer_id', 'left');
		// $this->db->join('order_details c', 'c.order_id = a.order_id', 'left');
		// $this->db->join('product_information d', 'd.product_id = c.product_id');
		// $this->db->join('product_category e', 'e.category_id = d.category_id');
		// $this->db->where($dateRange, NULL, FALSE);
		// if (!empty($product_category)) {
		// 	$this->db->where('d.category_id', $product_category);
		// }

		// if (!empty($product_name_select) && $product_name_select != 'all') {
		// 	$this->db->where('d.product_id', $product_name_select);
		// }
		// if (!empty($gender)) {
		// 	$this->db->where('b.gender', $gender);
		// }
		// if (!empty($non_active_range)) {

		// 	if ($age_range == 1) {
		// 		//60-100
		// 		$start_age_date = '1800-10-11';
		// 		$end_age_date = '1959-10-10';
		// 	}
		// 	if ($age_range == 2) {
		// 		//40-59
		// 		$start_age_date = '1959-10-11';
		// 		$end_age_date = '1979-10-10';
		// 	}
		// 	if ($age_range == 3) {
		// 		//20-39
		// 		$start_age_date = '1979-10-11';
		// 		$end_age_date = '1999-10-10';

		// 	}
		// 	if ($age_range == 4) {
		// 		//0-19
		// 		$start_age_date = '1999-10-11';
		// 		$end_age_date = '2030-10-10';
		// 	}
		// 	$birthdayRange = "STR_TO_DATE(b.birthday, '%Y-%m-%d') BETWEEN DATE('$start_age_date') AND DATE('$end_age_date')";
		// 	$this->db->where($birthdayRange, NULL, FALSE);
		// }
		// $this->db->order_by('a.date', 'desc');
		// $this->db->order_by('a.order_id', 'desc');
		$this->db->limit('500');
		$query = $this->db->get();

		// echo $this->db->last_query();
		return $query->result_array();
	}

}