<?php
require_once("report.php");
class Summary_inventory 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_inventory_summary($location_code)
	{


		$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,
SUM(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<='".date("Y-m-d")."' AND c.stock_id!='' AND c.stock_id IN(".$str.") AND c.qty !=0  GROUP BY c.stock_id,c.lot_no,c.price,c.standard_cost ORDER BY c.stock_id ASC,c.lot_no ASC,c.price ASC,c.standard_cost 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=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[$stock_items_gender[$value->gender]][$value->cat_description][$value->description]['qty_total']+=$value->sum_qty;
			$unique_items[$stock_items_gender[$value->gender]][$value->cat_description][$value->description]['price_total']+=number_format($value->sum_qty*$input_price,2,".","");
		}

		return $unique_items;
	}

	public function getData_inventory_details($location_code)
	{

		$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<='".date("Y-m-d")."' 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[$stock_items_gender[$value->gender]][$value->cat_description][$value->description]['qty_total']+=$value->sum_qty;
			$unique_items[$stock_items_gender[$value->gender]][$value->cat_description][$value->description]['price_total']+=number_format($value->sum_qty*$input_price,2,".","");
		}
		return $unique_items;
	}

	public function getData_inventory_details_lot($location_code)
	{

		$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<='".date("Y-m-d")."' 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]->item_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[$stock_items_gender[$value->gender]][$value->cat_description][$value->description][$value->lot_no]['qty_total']+=$value->sum_qty;
			$unique_items[$stock_items_gender[$value->gender]][$value->cat_description][$value->description][$value->lot_no]['price_total']+=number_format($value->sum_qty*$input_price,2,".","");
		}

		return $unique_items;
	}




	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();
	}

}
?>