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

class Physical_Verifcation_Model extends CI_Model
{

	private $tableName = 'sales';

	function __construct()
	{
		/* Call the Model constructor */
		parent::__construct();
	}


	function list_physical_verifcation($start = '', $length = '', $search_key_val = '', $srh_from_date = '', $srh_to_date = '', $srh_warehouse_id = '')
	{
		$this->db->select('pvh.*, w.name,pc.cat_name');
		$this->db->from('physical_verifcation_header pvh');
		$this->db->join('warehouses w', 'pvh.warehouse_id = w.id', 'left');
		$this->db->join('product_category pc', 'pvh.cat_srh = pc.cat_id', 'left');
		$this->db->order_by("pvh.pvh_id", "desc");

		if ($srh_warehouse_id) {
			$this->db->where("pvh.warehouse_id", $srh_warehouse_id);
		}
		/*
		if($srh_from_date){
			$srh_from_date=date('Y-m-d',strtotime($srh_from_date));
			$this->db->where("date(t.trnsfr_datetime) >=",$srh_from_date);
		}
		if($srh_to_date){
			$srh_to_date=date('Y-m-d',strtotime($srh_to_date));
			$this->db->where("date(t.trnsfr_datetime) <=",$srh_to_date);
		}
		*/
		if ($start != '' && $length != '') {
			if ($length > 0) $this->db->limit($length, $start);
		}
		$query = $this->db->get();
		return $query->result_array();
	}



	function save_physical_verifcation_item($data_item)
	{
		$this->db->insert('physical_verifcation_item', $data_item);
	}

	function physical_verifcation_header_list($pvh_id = '')
	{
		$this->db->select("pvh.*,pc.cat_name");
		$this->db->from('physical_verifcation_header pvh');
		$this->db->join('warehouses w', 'pvh.warehouse_id = w.id', 'left');
		$this->db->join('product_category pc', 'pvh.cat_srh = pc.cat_id', 'left');
		// $this->db->join('physical_verifcation_item pvi', 'pvh.pvh_id = pvi.pvh_id', 'left');
		//physical_verifcation_header
		$this->db->order_by("pvh.pvh_id", "desc");
		//$this->db->where("id IS NOT NULL");//("id !=",$id);
		if ($pvh_id) {
			$this->db->where("pvh.pvh_id", $pvh_id);
		}

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

	function get_total_balance_by_product_id_supp_sales_v1($product_id, $warehouse_id, $srh_to_date = '')
	{
		$this->db->select('pis.pis_number as sis_number');
		$this->db->from('purchase_items_serial pis');
		//	$this->db->join('sales s', 'sis.sale_id = s.sale_id', 'left');
		//	$this->db->join('purchase_items_serial pis', 'pis.pis_number = pis.pis_number', 'left');
		$this->db->join('purchases p', 'p.id=pis.purchase_id', 'left');
		$this->db->where("pis.product_id", $product_id);
		$this->db->where("pis.product_avalable", 1);

		if ($warehouse_id) {
			$this->db->where("pis.avalable_warehouse_id", $warehouse_id);
		}

		if ($srh_to_date) {
			$srh_to_date = date('Y-m-d', strtotime($srh_to_date));
			$this->db->where("date(p.date) <=", $srh_to_date); //("id !=",$id);
		}

		$this->db->order_by('pis.pis_number', 'asc');
		$query = $this->db->get();
		//	if($product_id)
		//echo $this->db->last_query();
		if ($query->row()) {
			return $query->result();
		} else {
			return $query->result();
		}
	}

	function get_total_balance_by_product_id_supp_sales_v2($warehouse_id, $srh_to_date = '')
	{
		$this->db->select('pis.product_id,pis.pis_number as sis_number');
		$this->db->from('purchase_items_serial pis');
		$this->db->join('purchases p', 'p.id=pis.purchase_id', 'left');
		//$this->db->where("pis.product_id",$product_id);
		$this->db->where("pis.product_avalable", 1);
		if ($warehouse_id) {
			$this->db->where("pis.avalable_warehouse_id", $warehouse_id);
		}
		if ($srh_to_date) {
			$srh_to_date = date('Y-m-d', strtotime($srh_to_date));
			$this->db->where("date(p.date) <=", $srh_to_date); //("id !=",$id);
		}
		$this->db->order_by('pis.product_id', 'asc');
		//$this->db->order_by('pis.pis_number','asc');
		//->db->group_by('pis.product_id');
		$query = $this->db->get();
		//echo $this->db->last_query(); die();
		//	if($product_id)

		if ($query->num_rows() > 0) {
			$data = array();

			foreach ($query->result() as $row) {

				$data[$row->product_id][] = $row->sis_number;
			}
			//print_r($data); die();
			return $data;
		} else {
			return false;
		}
	}


	function physical_verifcation_header_list_for_stock_reconcilation_report($warehouse_id = '', $pvh_id = '', $get_count = '')
	{
		$this->db->select("pvh.*,pvi.*,p.product_name,p.product_code, SUM(pvi_tmp_id) AS physical_qty,md.department_name,mt.ptype_name,pc.cat_name,w.name");
		$this->db->from('physical_verifcation_item pvi');
		$this->db->join('physical_verifcation_header pvh', 'pvi.pvh_id = pvh.pvh_id', 'left');
		$this->db->join('product p', 'pvi.product_id = p.product_id', 'left');
		$this->db->join('mstr_department md', 'p.dep_id= md.department_id', 'left');
		$this->db->join('product_category pc', 'p.cat_id= pc.cat_id', 'left');
		$this->db->join('mstr_pro_type mt', 'p.ptype_id= mt.ptype_id', 'left');
		$this->db->join('warehouses w', 'pvh.warehouse_id= w.id', 'left');

		$this->db->where('pvi.pvh_id', $pvh_id);
		$this->db->group_by("pvi.product_id");

		//$this->db->where("id IS NOT NULL");//("id !=",$id);

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


	function physical_verifcation_header_list_for_stock_reconcilation_report_v2($warehouse_id = '', $pvh_id = '', $get_count = '', $cat_id = '')
	{
		//	$this->db->select("pvh.*,pvi.*,p.product_name,p.product_code, SUM(pvi_tmp_id) AS physical_qty,md.department_name,mt.ptype_name,pc.cat_name,w.name");

		$this->db->select("p.product_name,p.product_id,pc.cat_name,md.department_name,mt.ptype_name,p.product_code");

		//$this->db->from('physical_verifcation_item pvi');
		//	$this->db->from('product_category pc');
		$this->db->from('product p');

		$this->db->join('product_category pc', 'p.cat_id= pc.cat_id', 'left');
		//$this->db->from('physical_verifcation_item pvi');
		//	$this->db->join('physical_verifcation_header pvh', 'pvi.pvh_id = pvh.pvh_id', 'left');
		//$this->db->join('product p', 'pc.cat_id = p.cat_id', 'left');
		$this->db->join('mstr_department md', 'p.dep_id= md.department_id', 'left');
		//	$this->db->join('product_category pc', 'p.cat_id= pc.cat_id', 'left');

		$this->db->join('mstr_pro_type mt', 'p.ptype_id= mt.ptype_id', 'left');
		//	$this->db->join('warehouses w', 'pvh.warehouse_id= w.id', 'left');

		$this->db->where('pc.cat_id', $cat_id);
		//	$this->db->group_by("pvi.product_id");

		//$this->db->where("id IS NOT NULL");//("id !=",$id);
		//	$this->db->limit("5",5);

		$query = $this->db->get();

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

	public function get_physical_verifcation_item_by_pvh_id_v1($pvh_id)
	{
		$this->db->select('pvi.pvi_serial_no,pvi.product_id');
		$this->db->from('physical_verifcation_item pvi');
		//$this->db->join('product p', 'pvi.product_id = p.product_id', 'left');
		if ($pvh_id) {
			$this->db->where('pvi.pvh_id', $pvh_id);
		}
		$this->db->order_by('pvi.product_id', 'asc');
		$this->db->order_by('pvi.pvi_serial_no', 'asc');
		$query = $this->db->get();
		if ($query->num_rows() > 0) {
			$data = array();
			foreach ($query->result() as $row) {
				$data[$row->product_id][] = $row;
			}
			return $data;
			//return $query->result();
		}
	
	}


	public function get_physical_verifcation_item_by_pvh_id($pvh_id, $product_id = '', $pvi_serial_no = '')
	{
		$this->db->select('pvi.*,p.product_name');
		$this->db->from('physical_verifcation_item pvi');
		$this->db->join('product p', 'pvi.product_id = p.product_id', 'left');
		if ($pvh_id) {
			$this->db->where('pvi.pvh_id', $pvh_id);
		}
		if ($product_id) {
			$this->db->where('pvi.product_id', $product_id);
		}
		if ($pvi_serial_no) {
			$this->db->where('pvi.pvi_serial_no', $pvi_serial_no);
		}

		$this->db->order_by('pvi.pvi_serial_no', 'asc');
		$query = $this->db->get();
		if ($query->num_rows() > 0) {
			return $query->result();
		}
	
	}

	public function get_physical_verifcation_item_by_pvh_id_only_ary($pvh_id, $product_id = '')
	{
		$this->db->select('pvi.pvi_serial_no');
		$this->db->from('physical_verifcation_item pvi');
		if ($pvh_id) {
			$this->db->where('pvi.pvh_id', $pvh_id);
		}
		if ($product_id) {
			$this->db->where('pvi.product_id', $product_id);
		}

		$query = $this->db->get();
		//echo $this->db->last_query();;
		if ($query->num_rows() > 0) {
			return $query->result_array();
		}
		/*
     else
     {
       return false;
     }
	 */
	}



	public function opening_balance_get($srh_from_date, $srh_to_date, $warehouse_id)
	{
		$this->db->select_sum('t.acctrnss_amount');
		$this->db->from('acc_transactions t');
		//$this->db->join('sales s', 's.sale_id = si.sale_id', 'left');
		$this->db->where('t.fxd_ass_id', 1);
		if ($warehouse_id) {
			$this->db->where('t.warehouse_id', $warehouse_id);
		}
		if ($srh_to_date) {
			$srh_to_date = date('Y-m-d', strtotime($srh_to_date));
			$this->db->where("date(t.acctrnss_date) <=", $srh_to_date); //("id !=",$id);
		}
		if ($srh_from_date) {
			$srh_from_date = date('Y-m-d', strtotime($srh_from_date));
			$this->db->where("date(t.acctrnss_date) >=", $srh_from_date); //("id !=",$id);
		}

		$query = $this->db->get();
		//echo $this->db->last_query();
		return $query->row()->acctrnss_amount;
	}

	public function get_product_serial_info($pis_number)
	{
		$this->db->select('pis.product_id');
		$this->db->from('purchase_items_serial pis');
		//$this->db->join('sales s', 's.sale_id = si.sale_id', 'left');
		$this->db->where('pis.pis_number', $pis_number);


		$query = $this->db->get();
		//echo $this->db->last_query();
		return $query->row()->product_id;
	}

	function save_physical_verifcation(&$data, $acctrnss_id = false)
	{
		if (!$acctrnss_id) {
			$this->db->insert('physical_verifcation_header', $data);
		} else {
			//	$this->db->where('acctrnss_id', $acctrnss_id);
			//	return $this->db->update('acc_transactions',$data);

		}
	}
}
