<?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 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();
		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();
     }
	 /*
     else
     {
       return false;
     }
	 */
  }
  
   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);
			
		}
	}
							
}