<?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(b.vat_enable_price_after_discount) AS product_amount_for_vat,
SUM(b.vat_enable_price) AS product_vat_enable_price,
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_shawpon_date_range_reprint(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(b.vat_enable_price_after_discount) AS product_amount_for_vat,
SUM(b.vat_enable_price) AS product_vat_enable_price,
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_reprint 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) {
			if($row->payment_type!=""){
				$sales_data[] = $row;
			}
		}

		return $sales_data;
	}
	public function getData_payment_shawpon_date_range_reprint(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_reprint 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) {
			if($row->payment_type!=""){
				$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(b.vat_enable_price_after_discount) AS product_amount_for_vat,
SUM(b.vat_enable_price) AS product_vat_enable_price,
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(b.vat_enable_price_after_discount) AS product_amount_for_vat,
SUM(b.vat_enable_price) AS product_vat_enable_price,
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(b.vat_enable_price_after_discount) AS product_amount_for_vat,
SUM(b.vat_enable_price) AS product_vat_enable_price,
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_return_date_range_reprint(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(b.vat_enable_price_after_discount) AS product_amount_for_vat,
SUM(b.vat_enable_price) AS product_vat_enable_price,
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_reprint 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)
	{

		$sql_item="SELECT b.stock_id,b.description,
IF(b.item_size_id> 0, CONCAT(b.stock_id,' - ',b.description,' - ',p.size), CONCAT(b.stock_id,' - ',b.description)) AS item_description,
IF(b.item_size_id> 0,p.size, '') AS item_size,
a.description AS cat_description,
b.category_id,
b.gender

FROM stock_master AS b
LEFT JOIN stock_category AS a ON a.category_id=b.category_id
LEFT JOIN pos_item_sizes AS p ON b.item_size_id=p.id
WHERE b.no_sale=0 ";

		$item_inventory_array=array();
		$stock_ids=array();

		$query = $this->db->query($sql_item);
		foreach ($query->result() as $row) {
			$item_inventory_array[$row->stock_id]=$row;
			$stock_ids[]=$row->stock_id;
		}

		$str="'".implode("','",$stock_ids)."'";


		$sql="SELECT
c.stock_id,
c.lot_no,
c.qty AS sum_qty,
c.price,
c.standard_cost,
c.type

FROM stock_moves AS c 
WHERE c.loc_code='".LOCATION_CODE."' AND c.tran_date<'".$start_date."' AND c.receive_date<'".$start_date."' AND c.stock_id!='' AND c.stock_id IN(".$str.") AND c.qty !=0  ORDER BY c.stock_id ASC,c.lot_no ASC,c.price ASC";


		$report_data_current_inventory=array();///////////////////////////////////////
		$latest_price=array();

		$search_perice_array=array();

		$sql_search="SELECT stock_id,price,lot_no,standard_cost FROM stock_moves_of_edit_data
							 ORDER BY  edit_date ASC ";

		$result_search =$this->db->query($sql_search);


		foreach ($result_search->result() as $row_search) {
			$search_perice_array[$row_search->stock_id][$row_search->lot_no]=$row_search->price;
		}

		$stock_in_array=array();
		$stock_out_array=array();

		$query = $this->db->query($sql);
		foreach ($query->result() as $row) {

			if (array_key_exists($row->stock_id,$item_inventory_array)){
				if($row->type!='201'){//////////////MODYFY/////////////////
					$search_perice=0;
					if(isset($search_perice_array[$row->stock_id][$row->lot_no])){
						$search_perice=intval($search_perice_array[$row->stock_id][$row->lot_no]);
						if($search_perice!=0){
							$row->price=$search_perice;
						}
					}
					if(isset($stock_in_array[$row->stock_id.$row->lot_no])){
						if($stock_in_array[$row->stock_id.$row->lot_no]>=$row->price){
							$stock_in_array[$row->stock_id.$row->lot_no]=$row->price;
						}
					}else{
						$stock_in_array[$row->stock_id.$row->lot_no]=$row->price;
					}
				}else{
					$stock_out_array[$row->stock_id.$row->lot_no]=$row->price;
				}
				$row->description=trim($item_inventory_array[$row->stock_id]->description);
				$row->cat_description=trim($item_inventory_array[$row->stock_id]->cat_description);
				$row->category_id=trim($item_inventory_array[$row->stock_id]->category_id);
				$row->gender=trim($item_inventory_array[$row->stock_id]->gender);
				$report_data_current_inventory[]=$row;

			}
		}


		$stock_items_gender = array(
		'fabric' => _("Fabric"),
		'male' => _("Male"),
		'female' => _("Female"),
		'boy' => _("Boy"),
		'girl' => _("Girl"),
		'newlook' => _("New Look"),
		'others' => _("Others"),
		'' => _("Miscellaneous"),
		'school' => _("School Fabric"),
		);


		$unique_items_temp=array();
		$unique_items=array();



		foreach ($report_data_current_inventory as $key => $value) {
			$input_price=0;
			if(isset($stock_in_array[$value->stock_id.$value->lot_no])){
				$input_price=intval($stock_in_array[$value->stock_id.$value->lot_no]);

			}else{
				$input_price=intval($stock_out_array[$value->stock_id.$value->lot_no]);
			}
			$unique_items_temp[$stock_items_gender[$value->gender]][$value->cat_description][$value->description]['qty_total']+=$value->sum_qty;
			$unique_items_temp[$stock_items_gender[$value->gender]][$value->cat_description][$value->description]['price_total']+=number_format($value->sum_qty*$input_price,2,".","");
		}

		return $unique_items_temp;

	}


	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.receive_date>='".$inputs['start_date']."' AND b.receive_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();
	}

}
?>