<?php
require_once("report.php");
class Summary_sales extends Report
{
	function __construct()
	{
		parent::__construct();
	}

	public function getDataColumns()
	{
		return array($this->lang->line('reports_date'), $this->lang->line('reports_subtotal'), $this->lang->line('reports_total'), $this->lang->line('reports_tax'), $this->lang->line('reports_profit'));
	}

	public function getData(array $inputs)
	{
		$this->db->select('sale_date, sum(subtotal) as subtotal, sum(total) as total, sum(tax) as tax,sum(profit) as profit');
		$this->db->from('sales_items_temp');
		if ($inputs['sale_type'] == 'sales')
		{
			$this->db->where('quantity_purchased > 0');
		}
		elseif ($inputs['sale_type'] == 'returns')
		{
			$this->db->where('quantity_purchased < 0');
		}

		$this->db->group_by('sale_date');
		$this->db->having('sale_date BETWEEN "'. $inputs['start_date']. '" and "'. $inputs['end_date'].'"');
		$this->db->order_by('sale_date');
		return $this->db->get()->result_array();
	}


	public function getData_shawpon_date_range(array $inputs)
	{
		$query = $this->db->query("SELECT
b.stock_id,
c.description,
d.description AS cat_description,
c.category_id,
c.gender,
SUM(b.quantity_purchased) AS sales_qty,
SUM(b.item_unit_price) AS sale_amount,
SUM(b.total_sale_amount) AS product_amount,
SUM(IF(b.discount_percent> 0,b.total_discount,0)) AS discount_amount,
SUM(IF(b.discount_percent= 0,b.total_discount,0)) AS adjustment_amount,
SUM(b.vat_enable_price) AS vat_enable_price,
SUM(b.vat_amount) AS vat_amount

FROM ospos_sales AS a
LEFT JOIN ospos_sales_items AS b ON a.sale_id=b.sale_id
LEFT JOIN stock_master AS c ON b.stock_id=c.stock_id
LEFT JOIN stock_category AS d ON c.category_id=d.category_id

WHERE DATE_FORMAT(a.sale_time,'%Y-%m-%d') BETWEEN '".$inputs['start_date']."' AND '".$inputs['end_date']."'
AND b.quantity_purchased>0 AND a.location='".LOCATION_CODE."' AND a.is_global='0'  AND a.acc_location='".LOCATION_CODE."'
GROUP BY c.category_id,c.description,c.gender ORDER BY c.description ASC ");




		foreach ($query->result() as $row) {
			$sales_data[] = $row;
		}

		return $sales_data;
	}

	public function getData_payment_shawpon_date_range(array $inputs)
	{
		$query = $this->db->query("SELECT
b.payment_type,SUM(b.payment_amount) AS payment_amoount
FROM ospos_sales AS a
LEFT JOIN ospos_sales_payments AS b ON a.sale_id=b.sale_id
WHERE DATE_FORMAT(a.sale_time,'%Y-%m-%d') BETWEEN '".$inputs['start_date']."' AND '".$inputs['end_date']."'
AND a.acc_location='".LOCATION_CODE."'
GROUP BY b.payment_type");


		foreach ($query->result() as $row) {
			$sales_data[] = $row;
		}

		return $sales_data;
	}

	public function getData_global_sale_shawpon_date_range(array $inputs)
	{
		$query = $this->db->query("SELECT
b.stock_id,
c.description,
d.description AS cat_description,
c.category_id,
c.gender,
SUM(b.quantity_purchased) AS sales_qty,
SUM(b.item_unit_price) AS sale_amount,
SUM(b.total_sale_amount) AS product_amount,
SUM(IF(b.discount_percent> 0,b.total_discount,0)) AS discount_amount,
SUM(IF(b.discount_percent= 0,b.total_discount,0)) AS adjustment_amount,
SUM(b.vat_enable_price) AS vat_enable_price,
SUM(b.vat_amount) AS vat_amount

FROM ospos_sales AS a
LEFT JOIN ospos_sales_items AS b ON a.sale_id=b.sale_id
LEFT JOIN stock_master AS c ON b.stock_id=c.stock_id
LEFT JOIN stock_category AS d ON c.category_id=d.category_id

WHERE DATE_FORMAT(a.sale_time,'%Y-%m-%d') BETWEEN '".$inputs['start_date']."' AND '".$inputs['end_date']."'
AND b.quantity_purchased>0 AND a.is_global='1'  AND a.acc_location='".LOCATION_CODE."' AND a.receive_location!='".LOCATION_CODE."'
GROUP BY c.category_id,c.description,c.gender ORDER BY c.description ASC ");


		foreach ($query->result() as $row) {
			$sales_data[] = $row;
		}

		return $sales_data;
	}


	public function getData_global_delivery_shawpon_date_range(array $inputs)
	{
		$query = $this->db->query("SELECT
b.stock_id,
c.description,
d.description AS cat_description,
c.category_id,
c.gender,
SUM(b.quantity_purchased) AS sales_qty,
SUM(b.item_unit_price) AS sale_amount,
SUM(b.total_sale_amount) AS product_amount,
SUM(IF(b.discount_percent> 0,b.total_discount,0)) AS discount_amount,
SUM(IF(b.discount_percent= 0,b.total_discount,0)) AS adjustment_amount,
SUM(b.vat_enable_price) AS vat_enable_price,
SUM(b.vat_amount) AS vat_amount

FROM ospos_sales AS a
LEFT JOIN ospos_sales_items AS b ON a.sale_id=b.sale_id
LEFT JOIN stock_master AS c ON b.stock_id=c.stock_id
LEFT JOIN stock_category AS d ON c.category_id=d.category_id

WHERE DATE_FORMAT(a.sale_time,'%Y-%m-%d') BETWEEN '".$inputs['start_date']."' AND '".$inputs['end_date']."'
AND b.quantity_purchased>0 AND a.is_global='1'  AND a.receive_location='".LOCATION_CODE."' AND a.acc_location!='".LOCATION_CODE."'
GROUP BY c.category_id,c.description,c.gender ORDER BY c.description ASC ");


		foreach ($query->result() as $row) {
			$sales_data[] = $row;
		}

		return $sales_data;
	}


	public function getData_shawpon_return_date_range(array $inputs)
	{
		$query = $this->db->query("SELECT
b.stock_id,
c.description,
d.description AS cat_description,
c.category_id,
c.gender,
SUM(b.quantity_purchased) AS sales_qty,
SUM(b.item_unit_price) AS sale_amount,
SUM(b.total_sale_amount) AS product_amount,
SUM(IF(b.discount_percent> 0,b.total_discount,0)) AS discount_amount,
SUM(IF(b.discount_percent= 0,b.total_discount,0)) AS adjustment_amount,
SUM(b.vat_enable_price) AS vat_enable_price,
SUM(b.vat_amount) AS vat_amount

FROM ospos_sales AS a
LEFT JOIN ospos_sales_items AS b ON a.sale_id=b.sale_id
LEFT JOIN stock_master AS c ON b.stock_id=c.stock_id
LEFT JOIN stock_category AS d ON c.category_id=d.category_id

WHERE DATE_FORMAT(a.sale_time,'%Y-%m-%d') BETWEEN '".$inputs['start_date']."' AND '".$inputs['end_date']."'
AND b.quantity_purchased<0 AND a.location='".LOCATION_CODE."' AND a.acc_location='".LOCATION_CODE."'
GROUP BY c.category_id,c.description,c.gender ORDER BY c.description ASC ");
		foreach ($query->result() as $row) {
			$sales_data[] = $row;
		}
		return $sales_data;
	}


	public function getData_shawpon_opening_range($start_date,$unique_items_cat,$unique_items_gender,$unique_items_description)
	{

		$unique_items_cat_str="'".implode("','",$unique_items_cat)."'";
		$unique_items_gender_str="'".implode("','",$unique_items_gender)."'";
		$unique_items_description_str="'".implode("','",$unique_items_description)."'";

		$query = $this->db->query("SELECT a.stock_id,a.description,a.category_id,a.gender,c.description AS cat_description,
SUM(b.qty) AS qty,b.price

FROM stock_master AS a
LEFT JOIN stock_moves AS b ON a.stock_id=b.stock_id
LEFT JOIN stock_category AS c ON a.category_id=c.category_id
WHERE a.category_id IN (".$unique_items_cat_str.") 
AND a.gender IN (".$unique_items_gender_str.") 
AND a.description IN (".$unique_items_description_str.")
AND b.loc_code='".LOCATION_CODE."' AND b.tran_date<'".$start_date."'
GROUP BY b.stock_id,b.lot_no,b.price");


		foreach ($query->result() as $row) {
			$sales_data[] = $row;
		}
		return $sales_data;
	}


	public function getData_shawpon_opening_range_all($start_date)
	{

		$unique_items_cat_str="'".implode("','",$unique_items_cat)."'";
		$unique_items_gender_str="'".implode("','",$unique_items_gender)."'";
		$unique_items_description_str="'".implode("','",$unique_items_description)."'";

		$query = $this->db->query("SELECT a.stock_id,a.description,a.category_id,a.gender,c.description AS cat_description,
SUM(b.qty) AS qty,b.price

FROM stock_master AS a
LEFT JOIN stock_moves AS b ON a.stock_id=b.stock_id
LEFT JOIN stock_category AS c ON a.category_id=c.category_id
WHERE b.loc_code='".LOCATION_CODE."' AND b.tran_date<'".$start_date."' AND a.no_sale=0
GROUP BY b.stock_id,b.lot_no,b.price");


		foreach ($query->result() as $row) {
			$sales_data[] = $row;
		}
		return $sales_data;
	}


	public function getData_receive_shawpon_date_range(array $inputs)
	{
		$query = $this->db->query("SELECT a.stock_id,a.description,a.category_id,a.gender,c.description AS cat_description,SUM(b.qty) AS qty,b.price
FROM stock_moves AS b
LEFT JOIN stock_master AS a ON b.stock_id=a.stock_id
LEFT JOIN stock_category AS c ON a.category_id=c.category_id
WHERE b.loc_code='".LOCATION_CODE."' AND b.tran_date>='".$inputs['start_date']."' AND b.tran_date<='".$inputs['end_date']."'  AND b.receive_qty>0
GROUP BY b.stock_id,b.lot_no,b.price");

		foreach ($query->result() as $row) {
			$sales_data[] = $row;
		}
		return $sales_data;
	}


	public function getSummaryData(array $inputs)
	{
		$this->db->select('sum(subtotal) as subtotal, sum(total) as total, sum(tax) as tax,sum(profit) as profit');
		$this->db->from('sales_items_temp');
		$this->db->where('sale_date BETWEEN "'. $inputs['start_date']. '" and "'. $inputs['end_date'].'"');
		if ($inputs['sale_type'] == 'sales')
		{
			$this->db->where('quantity_purchased > 0');
		}
		elseif ($inputs['sale_type'] == 'returns')
		{
			$this->db->where('quantity_purchased < 0');
		}
		return $this->db->get()->row_array();
	}

}
?>