<?php

 

class Purchases_Model extends CI_Model {

  

  

  function __construct() 

  {

    parent::__construct();

  }
  
  
  	public function get_grn_info($id)
	 {
		$this->db->select('*');
		$this->db->from('purchases');
		$this->db->where("id", $id);
		$this->db->order_by("id", "desc");
		$query = $this->db->get();
		return $query->row_array(); 
	 }
	 
	 	 	public function get_last_grn_cost_price_by_prodcut_id($product_id)
	 {
		$this->db->select('pis.*');
		$this->db->from('purchase_items_serial pis');
		$this->db->where("pis.product_id", $product_id);
		$this->db->order_by("pis.pis_id", "desc");
		$query = $this->db->get();
		return $query->row_array(); 
	 }
	 
	 
	  function get_total_paid_by_grn_id($sale_id){
	$this->db->select_sum('sale_pymnt_amount');
	$this->db->from('sale_payments');
	$this->db->where("sale_id",$sale_id)->where("(sale_payment_type='grn')");
	$query=$this->db->get();
	//echo $this->db->last_query();
	if($query->row()->sale_pymnt_amount){
		return $query->row()->sale_pymnt_amount;
	}else {
		return 0;
	}
  }
  
  
	 public function get_purchase_item_list_by_purchase_id($purchase_id)

	 {

		$this->db->select('purchase_items.id,purchase_items.product_id, purchase_items.product_name, purchase_items.product_code, purchase_items.quantity,purchase_items.sub_total, purchase_items.discount,purchase_items.discount_cal,purchase_items.unit_price,product.product_part_no,product.product_oem_part_number,product.product_name as product_name_c');

		$this->db->from('purchase_items');

		$this->db->join('product', 'purchase_items.product_id = product.product_id', 'left');

		$this->db->order_by("purchase_items.id", "desc");

		$this->db->where("purchase_items.purchase_id", $purchase_id);//("id !=",$id);

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

		return $query->result_array();

		

	 }
	 
	 function get_purchase_info($purchase_id)

{

        $this->db->select('*');

		$this->db->from('purchases');

		$this->db->where("id",$purchase_id);

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

		return $query->row_array(); 

}
 
  public function get_next_warehouse_ref_no($warehouse_id){
	$this->db->select_max('warehouse_last_id');
	$this->db->where('warehouse_id', $warehouse_id);
	$query = $this->db->get('purchases');
     if($query->num_rows() >0)
     {
       $g = $query->result();
       $u = $g[0]->warehouse_last_id;
       return $u;
     }
     else
     {
       return false;
     }
}

	public function get_grn_item_list_by_grn_id($grn_id)
	 {
		$this->db->select('purchase_items.*, product.*, product.product_sn_exissts, product.product_code,product.product_part_no,product.product_oem_part_number');
		$this->db->from('purchase_items');
		$this->db->join('product', 'purchase_items.product_id = product.product_id', 'left');
		$this->db->order_by("purchase_items.id", "desc");
		$this->db->where("purchase_items.purchase_id", $grn_id);//("id !=",$id);
		$query = $this->db->get();
		return $query->result_array();
		
	 }
	 
	   
public function get_next_ref_no(){
	$this->db->select_max('warehouse_last_id');
	$query = $this->db->get('purchases');
     if($query->num_rows() >0)
     {
       $g = $query->result();
       $u = $g[0]->warehouse_last_id;
       return $u;
     }
     else
     {
       return false;
     }
}

public function change_manual_setlmnt_status($pis_id,$data)
	{    
		
		$this->db->where('pis_id',$pis_id);
		if($this->db->update('purchase_items_serial',$data))
		{
			//echo $this->db->last_query();
			return true;
		}else{return false;}
	}
	
public function change_product_sn_by_sn($pis_number,$data)
	{    
		
		$this->db->where('pis_number',$pis_number);
		if($this->db->update('purchase_items_serial',$data))
		{
			//echo $this->db->last_query();
			return true;
		}else{return false;}
	}
	
	
	
	
  public function get_product_by_code($product_code = '',$cat_srh='')

  {

     $this->db->select('p.*');

     $this->db->from('product p');
     if($cat_srh){
		$this->db->where('cat_id',$cat_srh);
     }
     
     
     $this->db->where('product_status',1);
     
     $pizza  = $product_code;
     $pieces = explode("+", $pizza);
     //print_r($pieces);
     for($i=0; $i<count($pieces); $i++){
        // echo ', key:'.$pieces[$i];
        $searh_key=$pieces[$i];
        $this->db->like('p.product_name',$searh_key); 
     }
    
    //old
   // $this->db->like('p.product_name',$product_code); 
    
    // $this->db->or_like('p.product_code',$product_code); 

     $this->db->limit('15');

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

//   echo $this->db->last_query();

     if($query->num_rows() >0)

     {

       return $query->result();

     }

     else

     {

       return false;

     }

  }

  

  //get Purchased Qty By WarehouseId
	public function getPurchasedQtyByWarehouseId($warehouse_id,$product_id,$srh_from_date='',$srh_to_date='',$search_key_val='',$cat_srh='')
	{
		$this->db->select_sum('pi.quantity');
		$this->db->from('purchase_items pi');
		$this->db->join('purchases p', 'p.id = pi.purchase_id', 'left');
		$this->db->join("product pr", "pr.product_id = pi.product_id", "left");
		$this->db->join("product_category pc", "pr.cat_id = pc.cat_id", "left");
	  if($cat_srh){
	  $this->db->where("pc.cat_name",$cat_srh);
	  }
		
		if($search_key_val){
			 $this->db->where("pi.product_code LIKE '%$search_key_val%'","left");
		}
		
		if($warehouse_id){
		$this->db->where('p.warehouse_id',$warehouse_id);
		}
		if($product_id)
		$this->db->where('pi.product_id',$product_id);
		
		
		if($srh_to_date){
			$srh_to_date=date('Y-m-d',strtotime($srh_to_date . ""));
		
		//echo $srh_to_date.'|';
			$this->db->where("date(p.date) <=",$srh_to_date);//("id !=",$id);
		}
		if($srh_from_date){
			$this->db->where("date(p.date) >=",$srh_from_date);//("id !=",$id);
		}
		$query=$this->db->get();
		//echo $this->db->last_query();
		//return $data['quantity']=$query->row()->quantity;
		
		if($query->num_rows() >0)
     {
       return $data['quantity']=$query->row()->quantity;
     }
     else
     {
       return 0;
     }
	}



  function get_tax_by_id($tax_id='')

  {

     $this->db->select('t.*');

     $this->db->from('tax_rates t');

     $this->db->where('t.id',$tax_id); 

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

   

     if($query->num_rows() >0)

     {

       return $query->result();

     }

     else

     {

       return false;

     }

  }



  public function get_warehouse()

  {

     $this->db->select('*');

     $this->db->from('warehouses');

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

     if($query->num_rows() >0)

     {

       return $query->result();

     }

     else

     {

       return false;

     }

  }



  public function get_supplier()

  {

     $this->db->select('*');

     $this->db->from('supplier');

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

     if($query->num_rows() >0)

     {

       return $query->result();

     }

     else

     {

       return false;

     }

  }



  public function add_grn_header($podate,$reference_no,$supplier,$discount,$powarehouse,$note,$grand_total,$total,$order_cal_des,$supp_invocie_no,$warehouse_last_id,$poaddition='')

  {

    $data = array(

       'reference_no' => $reference_no ,

       'warehouse_id' => $powarehouse,

       'supplier_id'  => $supplier,

       'date'         => date('Y-m-d H:i', strtotime($podate)),

       'note'         => $note,

       'total'        => $total,

       'grand_total'  => $grand_total,

       'discount'     => $discount,

       'discount_cal' => $order_cal_des,

	   'supp_invocie_no'=>$supp_invocie_no,
	   'warehouse_last_id'=>$warehouse_last_id,
	   'user'=>$this->session->userdata('ss_user_id'),
	   'poaddition'=>$poaddition,

    );

//print_r($data);

    if($this->db->insert('purchases', $data)){

      return $this->db->insert_id();

    }else{

      return false;

    }

  }

  

	//save grn serial numbers

	function save_grn_serial_numbers($data_item)

	{

		$this->db->insert('purchase_items_serial',$data_item);

	}

	function update_grn_serial_numbers($data_item,$pis_number)

	{

		$this->db->where('pis_number', $pis_number);

		return $this->db->update('purchase_items_serial',$data_item);

	}

		  

  	//Sales item save

	function save_grn_item($data_item)

	{

			$this->db->insert('purchase_items',$data_item);

	}

	

	function save_grn($data_item,$grn_id=false)

	{

			

		if (!$grn_id)

		{

			$this->db->insert('purchases',$data_item);

		}else {

			$this->db->where('id', $grn_id);

			return $this->db->update('purchases',$data_item);

		}

	}



  public function add_grn_list_item( $product_id_array, $grn_header_id,$product_array, $product_name_array, $unit_cost_array, $quantity_array, $product_discount_array,$gross_total,$sub_total,$discount_cal,$product_w_array)

  {

    $data = array(

       'purchase_id'  => $grn_header_id ,

       'product_id'   => $product_id_array,

       'product_code' => $product_array,

       'product_name' => $product_name_array,

	  

	   'product_w' => $product_w_array,

       'quantity'     => $quantity_array,

       'unit_price'   => $unit_cost_array,

       'sub_total'    => $sub_total,

       'discount'     => $product_discount_array,

       'discount_cal' => $discount_cal

    );



    if($this->db->insert('purchase_items', $data)){

      return true;

    }else{

      return false;

    }

  }

  public function getpurchases($start,$length,$search_key_val,$warehouse_id='',$search_key1='',$srh_from_date='',$srh_to_date='')

  {
	  //echo "srh_from_date:$srh_from_date";

     $this->db->select("s.*,w.name,spl.*");

     $this->db->from("purchases s");

     //$this->db->join("sale_payments sp", "s.id = sp.sale_id AND sp.sale_payment_type ='grn'", "left"); 

     $this->db->join("supplier spl", "spl.supp_id = s.supplier_id", "left");
	  $this->db->join("warehouses w", "w.id = s.warehouse_id", "left");
	  
	  /*
	  if($search_key_val){
		$this->db->where("s.warehouse_id = $warehouse_id AND ( s.reference_no LIKE '%$search_key_val%' OR spl.supp_company_name LIKE '%$search_key_val%' OR s.supp_invocie_no LIKE '%$search_key_val%')");
	}
	*/
	
	//echo "test:$search_key1";
	//die();
	
	 if($search_key1){
		$this->db->where("( s.reference_no LIKE '%$search_key1%' OR spl.supp_company_name LIKE '%$search_key1%' OR s.supp_invocie_no LIKE '%$search_key1%' OR s.grand_total LIKE '%$search_key1%')");
	}
	
	
	 if($warehouse_id){
		$this->db->where('s.warehouse_id',$warehouse_id);
		}
		
		if($srh_to_date){
			$srh_to_date=date('Y-m-d',strtotime($srh_to_date));
		
	
			$this->db->where("date(s.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(s.date) >=",$srh_from_date);//("id !=",$id);
		}
		
	if($start!='' && $length!=''){
		$this->db->limit($length,$start);
	}

     $this->db->group_by("s.id");

     $this->db->order_by("s.id", "desc");

     $query = $this->db->get();  
     //echo $this->db->last_query();

     if($query->num_rows() >0)

     {

       return $query->result();

     }

     else

     {



       return false;

     }

  }



  public function getpurchases_by_id($po_id='')

  {

     $this->db->select("p.* , sp.*,wh.id as warehouse_id,wh.*");

     $this->db->from("purchases p");

     $this->db->join("supplier sp", " sp.supp_id = p.supplier_id", "left"); 

     $this->db->join("warehouses wh", " wh.id = p.warehouse_id", "left"); 

     $this->db->where("p.id",$po_id);

     $this->db->order_by("p.reference_no", "desc");

     $this->db->group_by("p.id");

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

     if($query->num_rows() >0)

     {

       return $query->result();

     }

     else

     {

       return false;

     }

  }





  public function get_purchese_data_by_id($po_id='')

  {

     $this->db->select('*,product.product_name as product_name_c');

     $this->db->from('purchase_items');
     
     $this->db->join('product', 'purchase_items.product_id = product.product_id', 'left');

     $this->db->where('purchase_items.purchase_id',$po_id);
     $this->db->order_by("purchase_items.id", "desc");

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

     if($query->num_rows() >0)

     {

       return $query->result();

     }

     else

     {

       return false;

     }

  }





  public function get_serial_no_supp_id($sn='')

  {

     $this->db->select('p.*,sis.pis_number');

     $this->db->from('purchase_items_serial sis');

	 $this->db->join('purchases p', 'p.id = sis.purchase_id', 'left');

     $this->db->where('sis.pis_number',$sn);

	 // $this->db->where('pis_sold',0);

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

	// echo $this->db->last_query();

       return $query->result();

  }
  
  
    public function get_serial_no_cost_by_serial_no($sn='')

  {

     $this->db->select('sis.pis_cost_price');

     $this->db->from('purchase_items_serial sis');

	 //$this->db->join('purchases p', 'p.id = sis.purchase_id', 'left');

     $this->db->where('sis.pis_number',$sn);

	 // $this->db->where('pis_sold',0);

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

	// echo $this->db->last_query();
	
	$pis_des=$query->result();
    $pis_cost_price=0;//pis_cost_price;
    $pis_cost_price=$pis_des[0]->pis_cost_price;
       return $pis_cost_price;

  }
  
  
   public function get_serial_no_seling_price_no($sn='')

  {

     $this->db->select('sis.pis_cash_price');

     $this->db->from('purchase_items_serial sis');

	 //$this->db->join('purchases p', 'p.id = sis.purchase_id', 'left');

     $this->db->where('sis.pis_number',$sn);

	 // $this->db->where('pis_sold',0);

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

	 //echo $this->db->last_query();
	
	$pis_des=$query->result();
    $pis_cost_price=0;//pis_cost_price;
    $pis_cost_price=$pis_des[0]->pis_cash_price;
       return $pis_cost_price;

  }

  
  public function get_serial_no_by_product_id($product_id='',$warehouse_id='')
  {
     $this->db->select('sis.*');
     $this->db->from('purchase_items_serial sis');
	$this->db->join("purchases p", "p.id = sis.purchase_id", "left");
     $this->db->where('sis.product_id',$product_id);
	//  $this->db->where('sis.pis_sold',0);
	  $this->db->where('sis.pis_transferred',0);
	  $this->db->where('sis.pis_sold',0);
	 // $this->db->where('sis.pis_transferred',0);
	  
	 //  $where_sale = '(sis.pis_sold=0 AND sis.pis_sales_return = 0)';
    // $this->db->where($where_sale);
	 
	// $where_sale_rtn = '(sis.pis_sold=1 AND sis.pis_sales_return = 1)';
    // $this->db->or_where($where_sale_rtn);
	  
	  
	 // if($warehouse_id){
	  $this->db->where('p.warehouse_id',$warehouse_id);
	//  }
     $query = $this->db->get();
	// echo $this->db->last_query();
       return $query->result();
  }
  
  
   public function get_serial_no_by_product_id_v5($product_id='',$warehouse_id='')
  {
     $this->db->select('sis.*');
     $this->db->from('purchase_items_serial sis');
	$this->db->join("purchases p", "p.id = sis.purchase_id", "left");
     $this->db->where('sis.product_id',$product_id);
	//  $this->db->where('sis.pis_sold',0);
	 // $this->db->where('sis.pis_transferred',0);
	  $this->db->where('sis.product_avalable',1);
	 // $this->db->where('sis.pis_transferred',0);
	  
	 //  $where_sale = '(sis.pis_sold=0 AND sis.pis_sales_return = 0)';
    // $this->db->where($where_sale);
	 
	// $where_sale_rtn = '(sis.pis_sold=1 AND sis.pis_sales_return = 1)';
    // $this->db->or_where($where_sale_rtn);
	  
	  
	  if($warehouse_id){
	  $this->db->where('sis.avalable_warehouse_id',$warehouse_id);
	  }
     $query = $this->db->get();
//	 echo $this->db->last_query();
       return $query->result();
  }
 
 
   public function get_serial_no_by_avalable_warehosue_id($product_id='',$warehouse_id='',$supplier_id='',$srh_from_date='',$srh_to_date='')
  {
     $this->db->select('pis.*,w.code');
     $this->db->from('purchase_items_serial pis');
	$this->db->join("purchases p", "p.id = pis.purchase_id", "left");
	 $this->db->join('warehouses w', 'pis.avalable_warehouse_id = w.id', 'left');
     $this->db->where('pis.product_id',$product_id);
     if($supplier_id){
	  $this->db->where('p.supplier_id',$supplier_id);
	 }
	 if($warehouse_id){
	  $this->db->where('pis.avalable_warehouse_id',$warehouse_id);
	 }
	 if($srh_to_date){
				$this->db->where("date(p.date) <=",$srh_to_date);//("id !=",$id);
				}
				if($srh_from_date){
				$this->db->where("date(p.date) >=",$srh_from_date);//("id !=",$id);
				}
	   $this->db->where('pis.product_avalable',1);
     $query = $this->db->get();
	// echo $this->db->last_query();
       return $query->result();
  }
  
  
   public function get_serial_no_by_avalable_warehosue_id_sb($product_id='',$warehouse_id='')
  {
     $this->db->select('pis.*,w.code');
     $this->db->from('purchase_items_serial pis');
	$this->db->join("purchases p", "p.id = pis.purchase_id", "left");
	 $this->db->join('warehouses w', 'pis.avalable_warehouse_id = w.id', 'left');
     $this->db->where('pis.product_id',$product_id);
	 if($warehouse_id){
	  $this->db->where_in('pis.avalable_warehouse_id',$warehouse_id);
	 }
	   $this->db->where('pis.product_avalable',1);
     $query = $this->db->get();
	// echo $this->db->last_query();
       return $query->result();
  }
  
    public function get_tra_pending_serial_no_by_avalable_warehosue_id($product_id='',$warehouse_id='')
  {
     $this->db->select('pis.*,w.code');
     $this->db->from('purchase_items_serial pis');
	$this->db->join("purchases p", "p.id = pis.purchase_id", "left");
	 $this->db->join('warehouses w', 'pis.avalable_warehouse_id = w.id', 'left');
     $this->db->where('pis.product_id',$product_id);
	 if($warehouse_id){
	  $this->db->where('pis.avalable_warehouse_id',$warehouse_id);
	 }
	   $this->db->where('pis.product_avalable',2);
     $query = $this->db->get();
	// echo $this->db->last_query();
       return $query->result();
  }
  
  
    public function get_tra_pending_serial_no_by_avalable_warehosue_id_sb($product_id='',$warehouse_id='')
  {
     $this->db->select('pis.*,w.code');
     $this->db->from('purchase_items_serial pis');
	$this->db->join("purchases p", "p.id = pis.purchase_id", "left");
	 $this->db->join('warehouses w', 'pis.avalable_warehouse_id = w.id', 'left');
     $this->db->where('pis.product_id',$product_id);
	 if($warehouse_id){
	  $this->db->where_in('pis.avalable_warehouse_id',$warehouse_id);
	 }
	   $this->db->where('pis.product_avalable',2);
     $query = $this->db->get();
	// echo $this->db->last_query();
       return $query->result();
  }
   
    public function get_transfer_reserved_serial_no_by_product_id($product_id='',$warehouse_id='')
  {
     $this->db->select('sis.*');
     $this->db->from('purchase_items_serial sis');
	$this->db->join("transfer t", "t.trnsfr_id = sis.trnsfr_id", "left");
     $this->db->where('sis.product_id',$product_id);
	  $this->db->where('sis.pis_transferred',1);
	  $this->db->where('sis.pis_sold',0);
	 // if($warehouse_id){
	  $this->db->where('t.trnsfr_to_warehouse_id',$warehouse_id);
	 // }
	  $this->db->group_by('sis.pis_number'); 
     $query = $this->db->get();
	// echo $this->db->last_query();
       return $query->result();
  }
  
     public function get_grn_return_serial_no_by_product_id($product_id='',$warehouse_id='')
  {
     $this->db->select('pris.pris_number');
     $this->db->from('purchase_return_items_serial pris');
	$this->db->join("purchase_return pr", "pr.pur_rtn_id = pris.pur_rtn_id", "left");
     $this->db->where('pris.product_id',$product_id);
	  $this->db->where('pr.warehouse_id',$warehouse_id);
	 // $this->db->where('sis.pis_sold',0);
	 // if($warehouse_id){
	 // $this->db->where('t.trnsfr_to_warehouse_id',$warehouse_id);
	 // }
	  $this->db->group_by('pris.pris_number'); 
     $query = $this->db->get();
	// echo $this->db->last_query();
       return $query->result_array();
  } 
  
   public function get_serial_no_by_product_id_sale_id($product_id='',$sale_id='')
  {
     $this->db->select('sis.*');
     $this->db->from('sale_items_serial sis');
	//$this->db->join("purchases p", "p.id = sis.purchase_id", "left");
     $this->db->where('sis.product_id',$product_id);
	 // $this->db->where('sis.pis_sold',1);
	  $this->db->where('sis.sale_id',$sale_id);
	  
	 /* if($warehouse_id){
	  $this->db->where('p.warehouse_id',$warehouse_id);
	  }*/
     $query = $this->db->get();
	// echo $this->db->last_query();
       return $query->result();
  }
 
    public function get_serial_no_by_product_id_grn_id($product_id='',$grn_id='',$warehouse_id='')
  {
     $this->db->select('sis.*');
     $this->db->from('purchase_items_serial sis');
	//$this->db->join("purchases p", "p.id = sis.purchase_id", "left");
     $this->db->where('sis.product_id',$product_id);
	  $this->db->where('sis.pis_sold',0);
	  $this->db->where('sis.product_avalable',1);
	  $this->db->where('sis.avalable_warehouse_id',$warehouse_id);
	  $this->db->where('sis.purchase_id',$grn_id);
	  
	 /* if($warehouse_id){
	  $this->db->where('p.warehouse_id',$warehouse_id);
	  }*/
     $query = $this->db->get();
	// echo $this->db->last_query();
       return $query->result();
  } 
  
    public function get_purchese_serial_by_id($po_id='',$id='')

  {

   //  $this->db->select('*');
    $this->db->select('purchase_items_serial.*,product.product_name,product.product_code');

     $this->db->from('purchase_items_serial');
     // $this->db->join('product','product.product_id = purchase_items_serial.product_id','left');
      $this->db->join('product','purchase_items_serial.product_id = product.product_id','left');
if($po_id){
     $this->db->where('purchase_items_serial.purchase_id',$po_id);
}
if($id){
	  $this->db->where('purchase_items_serial.purchase_item_id',$id);
}

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

     if($query->num_rows() >0)

     {

       return $query->result();

     }

     else

     {

       return false;

     }

  }

  public function get_purchese_serial_by_id_v1($po_id='',$id='')

  {

     $this->db->select('*');

     $this->db->from('purchase_items_serial');

     $this->db->where('purchase_items_serial.purchase_id',$po_id);

	  $this->db->where('purchase_items_serial.purchase_item_id',$id);

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

     if($query->num_rows() >0)

     {

       return $query->result();

     }

     else

     {

       return false;

     }

  }



  public function get_payment_by_id($purchase_id='')

  {

     $this->db->select('sp.*');

     $this->db->from('sale_payments sp');

     $this->db->where('sp.sale_id',$purchase_id);

     $this->db->where('sp.sale_payment_type','grn');

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

     return $query->result();



  }



  function grn_pay_total($purchase_id='')

  {

     $this->db->select('SUM(sp.sale_pymnt_amount) AS grn_paid_total');

     $this->db->from('sale_payments sp');

     $this->db->where('sp.sale_id',$purchase_id);

     $this->db->where('sp.sale_payment_type','grn');

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

     return $query->result();

  }



/* sanath start*/

  public function get_all_grn_for_report($srh_warehouse_id,$srh_to_date,$srh_from_date,$from='',$to='',$srh_supplier_id)

  {

     $this->db->select("s.*,spl.*, sp.sale_pymnt_amount, SUM(sp.sale_pymnt_amount) AS grn_total_paid");

     $this->db->from("purchases s");

     $this->db->join("sale_payments sp", "s.id = sp.sale_id AND sp.sale_payment_type ='grn'", "left"); 

     $this->db->join("supplier spl", "spl.supp_id = s.supplier_id", "left");

	 $this->db->join("warehouses w", "w.id = s.warehouse_id", "left");

	if($srh_warehouse_id){

			$this->db->where("s.warehouse_id",$srh_warehouse_id);//("id !=",$id);

	}

	if($srh_supplier_id){

			$this->db->where("s.supplier_id",$srh_supplier_id);//("id !=",$id);

	}

	if($srh_to_date){

		$this->db->where("date(s.date) <=",$srh_to_date);//("id !=",$id);

	}

	if($srh_from_date){

		$this->db->where("date(s.date) >=",$srh_from_date);//("id !=",$id);

	}

	if($to){

		$this->db->limit($to,$from);

		}	

     $this->db->group_by("s.id");

     $this->db->order_by("s.reference_no", "desc");

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

    // echo $this->db->last_query();

     return $query->result_array();

  }


 public function get_all_grn_for_report_v2($srh_warehouse_id,$srh_to_date,$srh_from_date,$from='',$to='',$srh_supplier_id)

  {

     $this->db->select("s.*");

     $this->db->from("purchases s");

 //    $this->db->join("sale_payments sp", "s.id = sp.sale_id AND sp.sale_payment_type ='grn'", "left"); 

     $this->db->join("supplier spl", "spl.supp_id = s.supplier_id", "left");

	 $this->db->join("warehouses w", "w.id = s.warehouse_id", "left");

	if($srh_warehouse_id){

			$this->db->where("s.warehouse_id",$srh_warehouse_id);//("id !=",$id);

	}

	if($srh_supplier_id){

			$this->db->where("s.supplier_id",$srh_supplier_id);//("id !=",$id);

	}

	if($srh_to_date){

		$this->db->where("date(s.date) <=",$srh_to_date);//("id !=",$id);

	}

	if($srh_from_date){

		$this->db->where("date(s.date) >=",$srh_from_date);//("id !=",$id);

	}

	if($to){

		$this->db->limit($to,$from);

		}	

     $this->db->group_by("s.id");

     $this->db->order_by("s.reference_no", "desc");

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

    // echo $this->db->last_query();

     return $query->result_array();

  }



 public function get_all_sales_list_for_sup_rep($srh_warehouse_id,$srh_to_date,$srh_from_date,$from='',$to='',$customer_id)

  {

     $this->db->select("s.*");

     $this->db->from("sales s");

 //    $this->db->join("sale_payments sp", "s.id = sp.sale_id AND sp.sale_payment_type ='grn'", "left"); 

     $this->db->join("customer c", "s.customer_id = c.cus_id", "left");

	 $this->db->join("warehouses w", "s.warehouse_id=w.id", "left");

	if($srh_warehouse_id){

			$this->db->where("s.warehouse_id",$srh_warehouse_id);//("id !=",$id);

	}

	if($customer_id){

			$this->db->where("s.customer_id",$customer_id);//("id !=",$id);

	}

	if($srh_to_date){

		$this->db->where("date(s.sale_datetime) <=",$srh_to_date);//("id !=",$id);

	}

	if($srh_from_date){

		$this->db->where("date(s.sale_datetime) >=",$srh_from_date);//("id !=",$id);

	}

	if($to){

		$this->db->limit($to,$from);

		}	

    // $this->db->group_by("s.id");

    // $this->db->order_by("s.reference_no", "desc");

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

     ///echo $this->db->last_query();

     return $query->result_array();

  }


public function get_all_sales_payent_list_for_sup_rep($srh_warehouse_id,$srh_to_date,$srh_from_date,$from='',$to='',$customer_id)

  {

     $this->db->select("sp.*");

     //$this->db->from("sales s");
     $this->db->from("sale_payments sp");

    $this->db->join("sales s", "sp.sale_id = s.sale_id AND sp.sale_payment_type ='sale' AND sp.sale_pymnt_paying_by!='GRN Goods'", "left"); 

     $this->db->join("customer c", "s.customer_id = c.cus_id", "left");

	 $this->db->join("warehouses w", "s.warehouse_id=w.id", "left");

	if($srh_warehouse_id){

			$this->db->where("s.warehouse_id",$srh_warehouse_id);//("id !=",$id);

	}

	if($customer_id){

			$this->db->where("s.customer_id",$customer_id);//("id !=",$id);

	}

	if($srh_to_date){

		$this->db->where("date(sp.sale_pymnt_added_date_time) <=",$srh_to_date);//("id !=",$id);

	}

	if($srh_from_date){

		$this->db->where("date(sp.sale_pymnt_added_date_time) >=",$srh_from_date);//("id !=",$id);

	}

	if($to){

		$this->db->limit($to,$from);

		}	

    // $this->db->group_by("s.id");

    // $this->db->order_by("s.reference_no", "desc");

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

     ///echo $this->db->last_query();

     return $query->result_array();

  }


   public function check_avalable_sn_for_sale($product_id='',$sris_number)



  {

     $this->db->select('pis.*');

     $this->db->from('purchase_items_serial pis');
    if($product_id){
	 $this->db->where('pis.product_id',$product_id);
    }

	 $this->db->where('pis.pis_sold',0);

	// $this->db->where('pis.pis_issue',1);

	 $this->db->where('pis.pis_number',$sris_number);

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

	// echo $this->db->last_query();

	 return  $this->db->affected_rows();



  }


 public function check_avalable_sn_for_sale_v2($product_id='',$sris_number)



  {

     $this->db->select('pis.*');

     $this->db->from('purchase_items_serial pis');
    if($product_id){
	 $this->db->where('pis.product_id',$product_id);
    }

	 $this->db->where('pis.pis_sold',0);

	// $this->db->where('pis.pis_issue',1);

	 $this->db->where('pis.pis_number',$sris_number);

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

	// echo $this->db->last_query();

	 return  $query->result_array();



  }

/* end sanath*/


		public function getReturnQtyByWarehouseId($warehouse_id,$product_id,$srh_from_date='',$srh_to_date='',$search_key_val='',$cat_srh='')
	{
		$this->db->select_sum('pi.pur_rtn_itm_quantity');
		$this->db->from('purchase_return_items pi');
		$this->db->join('purchase_return p', 'p.pur_rtn_id = pi.pur_rtn_id', 'left');
		$this->db->join('product pr','pi.product_id = pr.product_id');
		$this->db->join("product_category pc", "pr.cat_id = pc.cat_id", "left");
	  if($cat_srh){
	  $this->db->where("pc.cat_name",$cat_srh);
	  }
		if($search_key_val){
			 $this->db->where("pi.product_code LIKE '%$search_key_val%'","left");
		}
		if($warehouse_id){
		$this->db->where('p.warehouse_id',$warehouse_id);
		}
		if($product_id)
		$this->db->where('pi.product_id',$product_id);
		if($srh_to_date){
//		$srh_to_date=date('Y-m-d',strtotime($srh_to_date . ""));	
			$this->db->where("p.pur_rtn_datetime <=",$srh_to_date);//("id !=",$id);
		}
		if($srh_from_date){
			$this->db->where("p.pur_rtn_datetime >=",$srh_from_date);//("id !=",$id);
		}
		$query=$this->db->get();
		//echo $this->db->last_query();
		//return $data['quantity']=$query->row()->quantity;
		if($query->num_rows() >0)
     {
       return $data['pur_rtn_itm_quantity']=$query->row()->pur_rtn_itm_quantity;
     }
     else
     {
       return 0;
     }
	}

  function getPaymentsForPrint_grn($srh_warehouse_id='',$srh_to_date='',$srh_from_date='',$srh_type='',$srh_payment_term='',$ss_user_id='')
   {
	   $warehouse_id='';
	    $sel='p.*,pur.*,s.supp_company_name';
	   if($ss_user_id) $sel.=',u.user_first_name';
	   $this->db->select($sel);
       $this->db->from('sale_payments p');
	   $this->db->join('purchases pur', 'pur.id = p.sale_id', 'left');
	   $this->db->join('warehouses w', 'w.id = pur.warehouse_id', 'left');
	   $this->db->join('supplier s', 's.supp_id = pur.supplier_id', 'left');
	   //$this->db->join('customer c', 'c.cus_id = pur.customer_id', 'left');
	    if($ss_user_id) $this->db->join('user u', 'u.user_id = p.user_id', 'left');
	
		
	   if($srh_type){
	   
	   $this->db->where("p.sale_payment_type",$srh_type);//
	   }
	   if($srh_payment_term){
	   
	   $this->db->where("p.sale_pymnt_paying_by",$srh_payment_term);//
	   }
	    if($ss_user_id){
	   
	   $this->db->where("p.user_id",$ss_user_id);//
	   }
	   if($srh_warehouse_id){
	   
	   $this->db->where("pur.warehouse_id",$srh_warehouse_id);//
	   
	   }


		$this->db->where("p.sale_payment_type",'grn');
		   
		   
	   
	   if($srh_to_date){
			$this->db->where("p.sale_pymnt_date_time <=",$srh_to_date);//("id !=",$id);
		}
		if($srh_from_date){
			$this->db->where("p.sale_pymnt_date_time >=",$srh_from_date);//("id !=",$id);
		}
	   $this->db->order_by("pur.id", "desc");
	  
	   $query = $this->db->get();
	   // echo $this->db->last_query();
     if($query->num_rows() >0)
     {
       return $query->result();
     }
     else
     {
       //return false;
     }

   }

   public function getpurchases_return($start,$length,$search_key_val,$srh_warehouse_id,$srh_to_date,$srh_supplier_id)
  {
     $this->db->select("s.*,spl.*, sp.sale_pymnt_amount, SUM(sp.sale_pymnt_amount) AS grn_total_paid");
     $this->db->from("purchases_return s");
     $this->db->join("sale_payments sp", "s.pr_id = sp.sale_id AND sp.sale_payment_type ='grn_r'", "left"); 
     $this->db->join("supplier spl", "spl.supp_id = s.supplier_id", "left");
	 
	if($srh_warehouse_id){
		$this->db->where("s.warehouse_id",$srh_warehouse_id);//("id !=",$id);
	}
	if($srh_supplier_id){
		$this->db->where("s.supplier_id",$srh_supplier_id);//("id !=",$id);
	}
	if($srh_to_date){
		$srh_to_date = date('Y-m-d',strtotime($srh_to_date));	
		$this->db->where("s.date <=",$srh_to_date);//("id !=",$id);
	}
	
	 if($search_key_val){
            $this->db->where("s.pr_id LIKE '%$search_key_val%' OR s.reference_no LIKE '%$search_key_val%' OR spl.supp_company_name LIKE '%$search_key_val%'");/*OR spl.supp_company_name LIKE '%$search_key_val%'*/
       	}
    /* $this->db->group_by("s.date");
     $this->db->order_by("s.date", "desc");
	 */
     $this->db->group_by("s.pr_id");
     $this->db->order_by("s.pr_id", "desc");
	 if($start!='' && $length!=''){
            $this->db->limit($length,$start);
        }
     $query = $this->db->get();  
	 
	 /*echo "{";
	 print_r($this->db->last_query());
	  echo "}";*/
	  
/*	  echo "{";
	 print_r($query->result());
	  echo "}";*/
	  
     if($query->num_rows() >0)
     {
       return $query->result();
     }
     else
     {

       //return false;
     }
  }
  
   public function getpurchases_return_by_id($po_id='')
  {
     $this->db->select("p.* , sp.*,wh.id as warehouse_id,wh.*");
     $this->db->from("purchases_return p");
     $this->db->join("supplier sp", " sp.supp_id = p.supplier_id", "left"); 
     $this->db->join("warehouses wh", " wh.id = p.warehouse_id", "left"); 
     $this->db->where("p.pr_id",$po_id);
     $this->db->order_by("p.reference_no", "desc");
     $this->db->group_by("p.pr_id");
     $query = $this->db->get();  
     if($query->num_rows() >0)
     {
       return $query->result();
     }
     else
     {
       return false;
     }
  }
  
   public function get_purchese_return_data_by_id($po_id='')
  {
     $this->db->select('*');
     $this->db->from('purchase_return_items');
     $this->db->where('purchase_return_items.pr_id',$po_id);
     $query = $this->db->get();
	// print_r('<br><br><br><br><br>'.$this->db->last_query());
	 
     if($query->num_rows() >0)
     {
       return $query->result();
     }
     else
     {
       return false;
     }
  }
  
   public function get_payment_by_id_r($purchase_id='')
  {
     $this->db->select('sp.*');
     $this->db->from('sale_payments sp');
     $this->db->where('sp.sale_id',$purchase_id);
     $this->db->where('sp.sale_payment_type','grn_r');
     $query = $this->db->get();
     return $query->result();

  }
  
   function grn_pay_total_r($purchase_id='')
  {
     $this->db->select('SUM(sp.sale_pymnt_amount) AS grn_paid_total');
     $this->db->from('sale_payments sp');
     $this->db->where('sp.sale_id',$purchase_id);
     $this->db->where('sp.sale_payment_type','grn_r');
     $query = $this->db->get();
     return $query->result();
  }
  
  	public function getPurchasedQtyByWarehouseIdAndDateRange($warehouse_id='',$product_id='',$srh_from_date='',$srh_to_date='')

	{

		$this->db->select_sum('pi.quantity');

		$this->db->from('purchase_items pi');

		$this->db->join('purchases p', 'p.id = pi.purchase_id', 'left');
		if($warehouse_id){
		$this->db->where('p.warehouse_id',$warehouse_id);
		}

		if($product_id){
		$this->db->where('pi.product_id',$product_id);
		}
		
		if($srh_from_date){
			$srh_from_date=date('Y-m-d',strtotime($srh_from_date));
			$this->db->where("date(p.date) >=",$srh_from_date);//("id !=",$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);
		}
		
		$query=$this->db->get();
		//echo $this->db->last_query();

		return $data['quantity']=$query->row()->quantity;

	}
	
	 	public function getPurchasedQtyByWarehouseIdAndDateRange_mw($warehouse_id='',$product_id='',$srh_from_date='',$srh_to_date='')

	{

		$this->db->select_sum('pi.quantity');

		$this->db->from('purchase_items pi');

		$this->db->join('purchases p', 'p.id = pi.purchase_id', 'left');
		if($warehouse_id){
		$this->db->where_in('p.warehouse_id',$warehouse_id);
		}

		if($product_id){
		$this->db->where('pi.product_id',$product_id);
		}
		
		if($srh_from_date){
			$srh_from_date=date('Y-m-d',strtotime($srh_from_date));
			$this->db->where("date(p.date) >=",$srh_from_date);//("id !=",$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);
		}
		
		$query=$this->db->get();
		//echo $this->db->last_query();

		return $data['quantity']=$query->row()->quantity;

	}

	public function getPurchaseRTNdQtyByWarehouseIdAndDateRange($warehouse_id='',$product_id='',$srh_from_date='',$srh_to_date='')

	{

		$this->db->select_sum('pri.pur_rtn_itm_quantity');

		$this->db->from('purchase_return_items pri');

		$this->db->join('purchase_return pr', 'pri.pur_rtn_id = pr.pur_rtn_id', 'left');
		if($warehouse_id){
		$this->db->where('pr.warehouse_id',$warehouse_id);
		}

		if($product_id){
		$this->db->where('pri.product_id',$product_id);
		}
		
		if($srh_from_date){
			$srh_from_date=date('Y-m-d',strtotime($srh_from_date));
			$this->db->where("date(pr.pur_rtn_datetime) >=",$srh_from_date);//("id !=",$id);
		}
		if($srh_to_date){
			$srh_to_date=date('Y-m-d',strtotime($srh_to_date));
			$this->db->where("date(pr.pur_rtn_datetime) <=",$srh_to_date);//("id !=",$id);
		}
		
		$query=$this->db->get();
		//echo $this->db->last_query();

		return $data['pur_rtn_itm_quantity']=$query->row()->pur_rtn_itm_quantity;

	}
	
		public function getPurchaseRTNdQtyByWarehouseIdAndDateRange_mw($warehouse_id='',$product_id='',$srh_from_date='',$srh_to_date='')

	{

		$this->db->select_sum('pri.pur_rtn_itm_quantity');

		$this->db->from('purchase_return_items pri');

		$this->db->join('purchase_return pr', 'pri.pur_rtn_id = pr.pur_rtn_id', 'left');
		if($warehouse_id){
		$this->db->where_in('pr.warehouse_id',$warehouse_id);
		}

		if($product_id){
		$this->db->where('pri.product_id',$product_id);
		}
		
		if($srh_from_date){
			$srh_from_date=date('Y-m-d',strtotime($srh_from_date));
			$this->db->where("date(pr.pur_rtn_datetime) >=",$srh_from_date);//("id !=",$id);
		}
		if($srh_to_date){
			$srh_to_date=date('Y-m-d',strtotime($srh_to_date));
			$this->db->where("date(pr.pur_rtn_datetime) <=",$srh_to_date);//("id !=",$id);
		}
		
		$query=$this->db->get();
		//echo $this->db->last_query();

		return $data['pur_rtn_itm_quantity']=$query->row()->pur_rtn_itm_quantity;

	}
public function getServiceIssueWarehouseIdAndDateRange($warehouse_id='',$product_id='',$srh_from_date='',$srh_to_date='')
	{
	$this->db->select('s.service_id');
	$this->db->from('service s');
	//$this->db->join('purchases p', 'p.id = pi.purchase_id', 'left');
	if($warehouse_id){
	$this->db->where('s.warehouse_id',$warehouse_id);
	}
	if($product_id){
	$this->db->where('s.product_id',$product_id);
	}
	if($srh_from_date){
	$srh_from_date=date('Y-m-d',strtotime($srh_from_date));
	$this->db->where("date(s.prepared_date_time) >=",$srh_from_date);//("id !=",$id);
	}
	if($srh_to_date){
	$srh_to_date=date('Y-m-d',strtotime($srh_to_date));
	$this->db->where("date(s.prepared_date_time) <=",$srh_to_date);//("id !=",$id);
	}
	$this->db->where('s.prepared_status','Prepared');
	$query=$this->db->get();
	//echo $this->db->last_query();
	return $query->num_rows();
}	

public function getServiceReturnWarehouseIdAndDateRange($warehouse_id='',$product_id='',$srh_from_date='',$srh_to_date='')
	{
	$this->db->select('s.service_id');
	$this->db->from('service s');
	//$this->db->join('purchases p', 'p.id = pi.purchase_id', 'left');
	if($warehouse_id){
	$this->db->where('s.warehouse_id',$warehouse_id);
	}
	if($product_id){
	$this->db->where('s.product_id',$product_id);
	}
	if($srh_from_date){
	$srh_from_date=date('Y-m-d',strtotime($srh_from_date));
	$this->db->where("date(s.ready_date_time) >=",$srh_from_date);//("id !=",$id);
	}
	if($srh_to_date){
	$srh_to_date=date('Y-m-d',strtotime($srh_to_date));
	$this->db->where("date(s.ready_date_time) <=",$srh_to_date);//("id !=",$id);
	}
	$this->db->where('s.ready_status','Ready');
	$query=$this->db->get();
	//echo $this->db->last_query();
	return $query->num_rows();
}	
}