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

class Report_Model extends CI_Model {
  
  private $tableName = 'sales';
  
  function __construct() 
  {
    /* Call the Model constructor */
    parent::__construct();
     $this->db->query("SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));");
  }
  
  
  
  
 	//get Sold Qty By WarehouseId
	public function getSoldQtyByWarehouseId($warehouse_id,$product_id,$srh_from_date='',$srh_to_date='')
	{
		$this->db->select_sum('si.quantity');
		$this->db->from('sale_items si');
		$this->db->join('sales s', 's.sale_id = si.sale_id', 'left');
		$this->db->where('s.warehouse_id',$warehouse_id);
		$this->db->where('si.product_id',$product_id);
		if($srh_to_date){
			$srh_to_date=date('Y-m-d',strtotime($srh_to_date . "+1 days"));
			$this->db->where("s.sale_datetime <=",$srh_to_date);//("id !=",$id);
		}
		if($srh_from_date){
			$this->db->where("s.sale_datetime >=",$srh_from_date);//("id !=",$id);
		}
		$query=$this->db->get();
		//echo $this->db->last_query();
		return $data['quantity']=$query->row()->quantity;
	}
	
	
	function get_all_sales_for_sales_report($srh_warehouse_id,$srh_to_date,$srh_from_date,$srh_customer_id,$srh_payment_status)
	{
		$this->db->select('s.* , c.cus_name ,SUM(p.sale_pymnt_amount) AS total_paid_amount');
		$this->db->from('sales s');
		$this->db->join('customer c', 's.customer_id = c.cus_id', 'left');
		$this->db->join('sale_payments p', 's.sale_id = p.sale_id', 'left');
		$this->db->order_by("s.sale_id", "desc");
		$this->db->group_by('s.sale_id');
		//$this->db->where("p.sale_payment_type",'sale');

		if($srh_warehouse_id){	
			$this->db->where("s.warehouse_id",$srh_warehouse_id);
		}
		if($srh_to_date){
			$srh_to_date=date('Y-m-d',strtotime($srh_to_date . "+1 days"));
			$this->db->where("s.sale_datetime <=",$srh_to_date);
			
		}
		if($srh_from_date){
			$this->db->where("s.sale_datetime >=",$srh_from_date);
		}
		/*if($sale_id){
			$this->db->where("s.sale_id =",$sale_id);
		}*/
		if($srh_customer_id){
			$this->db->where("s.customer_id",$srh_customer_id);
	}
		if($srh_from_date){
		//$this->db->limit($srh_from_date,$srh_to_date);
		}
		$query = $this->db->get();
		
		//echo $this->db->last_query();
		return $query->result_array();
		
		
	}
	
	function get_all_sales_return_for_sales_report($srh_warehouse_id,$srh_to_date,$srh_from_date,$srh_customer_id,$srh_payment_status)
	{
		$this->db->select('s.* , c.cus_name ,SUM(p.sale_pymnt_amount) AS total_paid_amount');
		$this->db->from('sales_return s');
		$this->db->join('customer c', 's.customer_id = c.cus_id', 'left');
		$this->db->join('sale_payments p', 's.sale_id = p.sale_id', 'left');
		$this->db->order_by("s.sale_id", "desc");
		$this->db->group_by('s.sale_id');
		//$this->db->where("p.sale_payment_type",'sale');

		if($srh_warehouse_id){	
			$this->db->where("s.warehouse_id",$srh_warehouse_id);
		}
		if($srh_to_date){
			$srh_to_date=date('Y-m-d',strtotime($srh_to_date . "+1 days"));
			$this->db->where("s.sl_rtn_datetime <=",$srh_to_date);
			
		}
		if($srh_from_date){
			$this->db->where("s.sl_rtn_datetime >=",$srh_from_date);
		}
		/*if($sale_id){
			$this->db->where("s.sale_id =",$sale_id);
		}*/
		if($srh_customer_id){
			$this->db->where("s.customer_id",$srh_customer_id);
	}
		if($srh_from_date){
		$this->db->limit($srh_from_date,$srh_to_date);
		}
		$query = $this->db->get();
		
		//echo $this->db->last_query();
		return $query->result_array();
		
		
	}
	
	
	
	
	
	
	
	
function get_all_sales_return_for_report($srh_warehouse_id='',$srh_to_date='',$srh_from_date='',$sale_id='',$from='',$to='') {
		$this->db->select('sr.* , c.cus_name ,SUM(p.sale_pymnt_amount) AS total_paid_amount');
		$this->db->from('sales_return sr');
		$this->db->join('customer c', 'sr.customer_id = c.cus_id', 'left');
		$this->db->join('sale_payments p', 'sr.sl_rtn_id = p.sale_id', 'left');
		$this->db->where("p.sale_payment_type",'sales_return');
		//$this->db->join('sales_return sr', 'sr.sale_id = p.sale_id', 'left');
		$this->db->order_by("sr.sl_rtn_id", "desc");
		$this->db->group_by('sr.sl_rtn_id');
		if($srh_warehouse_id){
			$this->db->where("sr.warehouse_id",$srh_warehouse_id);//("id !=",$id);
		}
		if($srh_to_date){
			$srh_to_date=date('Y-m-d',strtotime($srh_to_date . "+1 days"));
			$this->db->where("sr.sl_rtn_datetime <=",$srh_to_date);//("id !=",$id);
		}
		if($srh_from_date){
			$this->db->where("sr.sl_rtn_datetime >=",$srh_from_date);//("id !=",$id);
		}
		//if($sl_rtn_id){
			//$this->db->where("sr.sl_rtn_id =",$sl_rtn_id);//("id !=",$id);
	//	}
		if($to){
		$this->db->limit($to,$from);
		}
		$query = $this->db->get();
		//echo $this->db->last_query();
		return $query->result_array();
	}	
	
	
		     //Sales get information
	public function get_sale_info_by_customer_id($id)
	 {
		$this->db->select('*');
		$this->db->from('sales');
		$this->db->where("customer_id", $id);
		$this->db->order_by("sale_id", "desc");
		$query = $this->db->get();
		return $query->result(); 
	 }

	function get_all_sales_bu_cus_id($cus_id) {
		$this->db->select('sales.*, customer.cus_name');
		$this->db->from('customer');
		$this->db->join('sales', 'sales.customer_id = customer.cus_id', 'left');
		$this->db->order_by("sales.sale_id", "desc");
		$this->db->where("sales.customer_id",$cus_id);//("id !=",$id);
		$query = $this->db->get();
		return $query->result();
	}	 
	 
 function getPaymentsForPrint($srh_warehouse_id='',$srh_to_date='',$srh_from_date='',$srh_type='',$srh_payment_term='',$ss_user_id='')
   {
	    $sel='p.*,c.cus_name,b.*';
	   if($ss_user_id) $sel.=',u.user_first_name';
	   $this->db->select($sel);
       $this->db->from('sale_payments p');
	   $this->db->join('sales b', 'b.sale_id = p.sale_id', 'left');
	   $this->db->join('warehouses w', 'w.id = b.warehouse_id', 'left');
	   $this->db->join('customer c', 'c.cus_id = b.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("b.warehouse_id",$srh_warehouse_id);//
	   }
	   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("b.sale_id", "desc");
	  
	   $query = $this->db->get();
	   // echo $this->db->last_query();
     if($query->num_rows() >0)
     {
       return $query->result();
     }
     else
     {
       //return false;
     }

   }
   
   function getChequeForPrint($srh_warehouse_id='',$srh_to_date='',$srh_from_date='',$srh_type='',$srh_payment_term='',$ss_user_id='')
   {
	    $sel='p.*,c.cus_name,b.*';
	   if($ss_user_id) $sel.=',u.user_first_name';
	   $this->db->select($sel);
       $this->db->from('sale_payments p');
	   $this->db->join('sales b', 'b.sale_id = p.sale_id', 'left');
	   $this->db->join('warehouses w', 'w.id = b.warehouse_id', 'left');
	   $this->db->join('customer c', 'c.cus_id = b.customer_id', 'left');
	   $this->db->where("p.sale_pymnt_paying_by",'Cheque');
	   
	   
	    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("b.warehouse_id",$srh_warehouse_id);//
	   }
	   if($srh_to_date){
			$this->db->where("p.sale_pymnt_added_date_time <=",$srh_to_date);//("id !=",$id);
		}
		if($srh_from_date){
			$this->db->where("p.sale_pymnt_added_date_time >=",$srh_from_date);//("id !=",$id);
		}
	   $this->db->order_by("b.sale_id", "desc");
	  
	   $query = $this->db->get();
	  //  echo $this->db->last_query();
     if($query->num_rows() >0)
     {
       return $query->result();
     }
     else
     {
       //return false;
     }

   }
   
   	
	  
   //Sales best for dashboard
   function getBestSales($year=null,$month=0,$from=0,$to=0){
	$this->db->select('SUM(ft.fi_qty)AS fi_qty_tot,p.product_name,p.product_code,p.product_part_no,p.product_oem_part_number');
	$this->db->from('fi_table ft');
	$this->db->join('product p', 'ft.fi_item_id = p.product_id', 'left');
	$this->db->where('ft.fi_type_id', 'sale');
	if($month){
		$this->db->where('MONTH(ft.fi_date_time)', $month , FALSE);
	}
	if($year){
		$this->db->where('YEAR(ft.fi_date_time)', $year , FALSE);
	}
	if($to){
		$this->db->limit($to,$from);
	}
	$this->db->order_by("fi_qty_tot", "desc");
	$this->db->group_by('ft.fi_item_id');
	$query=$this->db->get();
	return $query->result();
  }  
  
  //Sales genarate referance number
  function get_next_ref_no(){
	  $this->db->select_max('sale_id');
	  return $this->db->get('sales');
  }
 
  //Sales get avalable product qty
  function get_avalable_product_qty($product_id,$warehouse_id){
		$this->db->select_sum('fi_qty');
		$query = $this->db->get('fi_table');
		return $query->row()->fi_qty;
  }
  
  //Sales get toatal paid 
  function get_total_paid_by_sale_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='sale' OR sale_payment_type='pos_sale')");
	$query=$this->db->get();
	//echo $this->db->last_query();
	if($query->row()->sale_pymnt_amount){
		return $query->row()->sale_pymnt_amount;
	}else {
		return 0;
	}
  }
  
    //Sales get information
	public function get_sale_info($id)
	 {
		$this->db->select('*');
		$this->db->from('sales');
		$this->db->where("sale_id", $id);
		$this->db->order_by("sale_id", "desc");
		$query = $this->db->get();
		return $query->row_array(); 
	 }
	 
	//Sales item list get by id 
	public function get_sale_item_list_by_sale_id($sale_id)
	 {
		$this->db->select('sale_items.product_id, product.product_name, product.product_code, sale_items.quantity, sale_items.discount, sale_items.discount_val, sale_items.unit_price, sale_items.gross_total,product.product_part_no,product.product_oem_part_number');
		$this->db->from('sale_items');
		$this->db->join('product', 'sale_items.product_id = product.product_id', 'left');
		$this->db->order_by("sale_items.id", "desc");
		$this->db->where("sale_items.sale_id", $sale_id);//("id !=",$id);
		$query = $this->db->get();
		return $query->result_array();
		
	 }

	//Sales save
	function save_sales(&$supplier_data,$sale_id=false)
	{
		if (!$sale_id)
		{
			$this->db->insert($this->tableName,$supplier_data);
		}else {
			$this->db->where('sale_id', $sale_id);
			return $this->db->update($this->tableName,$supplier_data);
		}
	}	
	
	//Sales item save
	function save_sales_item(&$data_item)
	{
			$this->db->insert('sale_items',$data_item);
	}	

	//Sales get for report
	function get_all_sales_for_report($srh_warehouse_id='',$srh_to_date='',$srh_from_date='',$sale_id='',$from='',$to='',$srh_customer_id='') {
		//echo "<br/>Test".$srh_customer_id;
		$this->db->select('s.* , c.cus_name ,SUM(p.sale_pymnt_amount) AS total_paid_amount');
		$this->db->from('sales s');
		$this->db->join('customer c', 's.customer_id = c.cus_id', 'left');
		$this->db->join('sale_payments p', 's.sale_id = p.sale_id', 'left');
		$this->db->order_by("s.sale_id", "desc");
		$this->db->group_by('s.sale_id');
		//$this->db->where("p.sale_payment_type",'sale');

		if($srh_warehouse_id){	
			$this->db->where("s.warehouse_id",$srh_warehouse_id);//("id !=",$id);
		}
		if($srh_to_date){
			$srh_to_date=date('Y-m-d',strtotime($srh_to_date . "+1 days"));
			$this->db->where("s.sale_datetime <=",$srh_to_date);
			
		}
		if($srh_from_date){
			$this->db->where("s.sale_datetime >=",$srh_from_date);//("id !=",$id);
		}
		if($sale_id){
			$this->db->where("s.sale_id =",$sale_id);//("id !=",$id);
		}
		if($srh_customer_id){
			$this->db->where("s.customer_id",$srh_customer_id);//("id !=",$id);
	}
		if($to){
		$this->db->limit($to,$from);
		}
		$query = $this->db->get();
		
		//echo $this->db->last_query();
		return $query->result_array();
	}	
	
	//Sales all get
	function get_all_sales() {
		$this->db->select('sales.*, customer.cus_name');
		$this->db->from('customer');
		$this->db->join('sales', 'sales.customer_id = customer.cus_id', 'left');
		$this->db->order_by("sales.sale_id", "desc");
		$this->db->where("sales.sale_id IS NOT NULL");//("id !=",$id);
		$query = $this->db->get();
		return $query->result_array();
	}
	
	//Sales get for print
	function get_all_sales_for_print_sales() {
		$this->db->select('s.* , c.cus_name ,SUM(p.sale_pymnt_amount) AS total_paid_amount');
		$this->db->from('sales s');
		$this->db->join('customer c', 's.customer_id = c.cus_id', 'left');
		$this->db->join('sale_payments p', 's.sale_id = p.sale_id', 'left');
		$this->db->order_by("s.sale_id", "desc");
		$this->db->group_by('s.sale_id');
		$this->db->where("s.sale_id IS NOT NULL");//("id !=",$id);
		$query = $this->db->get();
		return $query->result_array();
	}
	
	//Sales payment get 
	function get_sale_payments_by_sale_id($sale_id) {
		$this->db->select('sale_payments.*,user.user_first_name,user_group.user_group_name');
		$this->db->from('sale_payments');
		$this->db->join('user', 'sale_payments.user_id = user.user_id', 'left');
		$this->db->join('user_group', 'user.group_id = user_group.user_group_id', 'left');
		$this->db->order_by("sale_payments.sale_pymnt_id", "desc");
		$this->db->where("sale_payments.sale_id",$sale_id);//("id !=",$id);
		$this->db->where("sale_payments.sale_payment_type",'sale');
		$query = $this->db->get();
		return $query->result();
	}
	
	//Get product sujetions
	function get_products_suggestions($term) {
		$this->db->select('product'.'.*');
		$this->db->order_by("product_name", "asc");
		//$this->db->where("product_name LIKE '%$term%'");
		$this->db->where("product_name LIKE '%$term%' OR product_code LIKE '%$term%' OR product_oem_part_number LIKE '%$term%' OR product_part_no LIKE '%$term%'");
		 $this->db->limit(10, 0);
		$query = $this->db->get('product');
		//echo $this->db->last_query();
		return $query->result_array();
	}
	
	//Get all products
	function get_all_products() {
		$this->db->select('product'.'.*');
		$this->db->order_by("product_name", "asc");
		$this->db->where("product_id IS NOT NULL");//("id !=",$id);
		$query = $this->db->get('product');
		return $query->result_array();
	}
	
	
	function get_products_suggestions_get_by_sales_rep_id($term,$Sales_rep_id,$werehouse_id)
 {
	 $this->db->distinct();
	$this->db->select('product.*');
	$this->db->from('product');
	
	$this->db->join('sales_rep_issue_items','product.product_id = sales_rep_issue_items.product_id ','left');
	$this->db->join('sales_rep_issues','sales_rep_issue_items.sales_rep_issue_id = sales_rep_issues.sales_rep_issue_id ','left');
	
	
	$this->db->where("sales_rep_issues.wharehouse_id",$werehouse_id );
	$this->db->where("sales_rep_issues.sales_rep_id",$Sales_rep_id );
	$this->db->where("product.product_name LIKE '%$term%'");
	$this->db->or_where("sales_rep_issues.sales_rep_id",$Sales_rep_id );
	$this->db->where("sales_rep_issues.wharehouse_id",$werehouse_id );
	$this->db->where("product.product_code LIKE '%$term%'");
	$this->db->or_where("sales_rep_issues.sales_rep_id",$Sales_rep_id );
	$this->db->where("sales_rep_issues.wharehouse_id",$werehouse_id );
	$this->db->where("product.product_oem_part_number LIKE '%$term%'");
	$this->db->or_where("sales_rep_issues.sales_rep_id",$Sales_rep_id );
	$this->db->where("product.product_part_no LIKE '%$term%'");
	$this->db->where("sales_rep_issues.wharehouse_id",$werehouse_id );
	$this->db->distinct();
		
	$this->db->limit(10, 0);
		$query = $this->db->get();
	//echo $this->db->last_query();
	return $query->result_array();
	}
	
	function get_products_suggestions_get_by_sales_id_for_return($term,$sales_id)
 {
	 //$this->db->distinct();
	$this->db->select('product.product_name,product.product_code,product.product_oem_part_number 
,product.product_part_no,sale_items.*');
	$this->db->from('sale_items');
	
	$this->db->join('product','sale_items.product_id = product.product_id ','left');
	
	
	
	$this->db->where("sale_items.sale_id",$sales_id);
	$this->db->where("product.product_name LIKE '%$term%'");
	
	$this->db->or_where("sale_items.sale_id",$sales_id );
	$this->db->where("product.product_code LIKE '%$term%'");

	$this->db->or_where("sale_items.sale_id",$sales_id );
	$this->db->where("product.product_oem_part_number LIKE '%$term%'");
	
	$this->db->or_where("sale_items.sale_id",$sales_id );
	$this->db->where("product.product_part_no LIKE '%$term%'");
	
	//$this->db->distinct();
		
	$this->db->limit(10, 0);
		$query = $this->db->get();
	//echo $this->db->last_query();
	return $query->result_array();
	}
	
	
	function get_sum_of_received_item_qty($product_id,$Sales_rep_id,$werehouse_id)
	{
		$this->db->select_sum('salse_rep_issue_qty');
		$this->db->from('sales_rep_issue_items ');
		$this->db->join('sales_rep_issues','sales_rep_issue_items.sales_rep_issue_id = sales_rep_issues.sales_rep_issue_id ','left');
		$this->db->where("sales_rep_issues.wharehouse_id",$werehouse_id );
		$this->db->where("sales_rep_issues.sales_rep_id",$Sales_rep_id );
		$this->db->where("sales_rep_issue_items.product_id",$product_id );
		
		
		$query = $this->db->get();
		return $query->result_array();
	}
	
	
	function get_sum_of_invoiced_item_qty($product_id,$Sales_rep_id,$werehouse_id)
	
{
	
		$this->db->select_sum('quantity');
		$this->db->from('sale_items');
		$this->db->join('sales','sale_items.sale_id = sales.sale_id ','left');
		$this->db->where("sales.warehouse_id",$werehouse_id );
		$this->db->where("sales.sales_rep_id",$Sales_rep_id );
		$this->db->where("sale_items.product_id",$product_id );
	
		$query = $this->db->get();
		return $query->result_array();
			
}
	
	function get_sum_of_invoiced_item_qty_by_sale_id($product_id,$sale_id)
	
{
	
		$this->db->select_sum('quantity');
		$this->db->from('sale_items');
		$this->db->join('sales','sale_items.sale_id = sales.sale_id ','left');
		$this->db->where("sales.sales_rep_id",$sale_id );
		$this->db->where("sale_items.product_id",$product_id );
	
		$query = $this->db->get();
		return $query->result_array();
	
	
	
}
	
	
	//Sales payment save
	function save_sale_payments(&$data,$sale_pymnt_id=false)
	{
		if (!$sale_pymnt_id)
		{
			return $this->db->insert('sale_payments',$data);
		}else {
			$this->db->where('supp_id', $sale_pymnt_id);
			return $this->db->update('sale_payments',$data);
		}
	}
	
	
	public function get_all_item_list_by_sale_rep_id_and_warehouse($srh_warehouse_id,$srh_to_date,$srh_from_date,$sales_rep_id)
	{
		$this->db->select('p.product_id ,p.product_name ,p.product_code,SUM(srit.salse_rep_issue_qty) AS total_salse_rep_issue_qty');
		$this->db->from('sales_rep_issue_items srit');
		$this->db->join('product p', 'srit.product_id  = p.product_id', 'left');
		$this->db->join('sales_rep_issues sri', 'srit.sales_rep_issue_id = sri.sales_rep_issue_id', 'left');
		$this->db->join('sales s','sri.sales_rep_id = s.sales_rep_id', 'left');
		$this->db->order_by("p.product_name", "desc");
		$this->db->group_by('p.product_id');
		$this->db->where("sri.sales_rep_id",$sales_rep_id);
		$this->db->where("sri.wharehouse_id",$srh_warehouse_id);
		$query = $this->db->get();
		return $query->result_array();
	}
	
		function get_transactions_list($fxd_ass_id='',$warehouse_id='',$srh_from_date='',$srh_to_date='')
	{
		$this->db->select('acc_transactions.*,mstr_expences_type.*');
		$this->db->select('fixed_asset.*');
		$this->db->from('acc_transactions');
		$this->db->join('fixed_asset','acc_transactions.fxd_ass_id=fixed_asset.fxd_ass_id','left');
		$this->db->join('mstr_expences_type','acc_transactions.etp_id=mstr_expences_type.etp_id','left');
		if($fxd_ass_id){
			$this->db->where("acc_transactions.fxd_ass_id",$fxd_ass_id);
		}		
		if($srh_to_date){			
			$srh_to_date=date('Y-m-d',strtotime($srh_to_date));
			$this->db->where("acc_transactions.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("acc_transactions.acctrnss_date >=",$srh_from_date);//("id !=",$id);
		}		
		$query=$this->db->get();		
		return $query->result_array();
	}
	
	function get_sale_items_using_sale_id($sale_id)
	{
		
		$this->db->select('s.*');
		$this->db->select('p.product_code,p.product_name');
		$this->db->from('hw_sale_items s');
		$this->db->join('product p', 's.product_id = p.product_id', 'left');
		$this->db->where("s.sale_id",$sale_id);
		$query=$this->db->get();		
		return $query->result_array();
	}
	
	function get_all_sales_return_for_report_hw($srh_warehouse_id='', $srh_to_date='', $srh_from_date='',$float_id='') {
		$this->db->select('s.* , c.cus_name ,sm.sale_reference_no,sm.sale_datetime');
		$this->db->from('hw_sales_return s');
		$this->db->join('customer c', 's.customer_id = c.cus_id', 'left');
		$this->db->join('hw_sales sm', 's.sale_id = sm.sale_id', 'left');
		$this->db->order_by("s.sl_rtn_id", "asc");
		if($srh_warehouse_id){
			$this->db->where("s.warehouse_id",$srh_warehouse_id);
		}
		if($srh_to_date){
			$srh_to_date=date('Y-m-d',strtotime($srh_to_date . "+1 days"));
			$this->db->where("s.sl_rtn_datetime <=",$srh_to_date);
		}
		if($srh_from_date){
			$this->db->where("s.sl_rtn_datetime >=",$srh_from_date);
		}
		if($float_id){
			$this->db->where("s.float_id",$float_id);
		}
		$query = $this->db->get();
		return $query->result_array();
	}
	
	function get_sale_return_items_using_sale_id($sale_id)
	{
		$this->db->select('s.*');
		$this->db->select('p.product_code,p.product_name');
		$this->db->from('hw_sales_return_items s');
		$this->db->join('product p', 's.product_id = p.product_id', 'left');
		$this->db->where("s.sl_rtn_id",$sale_id);
		$query=$this->db->get();		
		return $query->result_array();
	}
	
	function get_sale_items_summary($srh_warehouse_id='', $srh_to_date='', $srh_from_date='',$float_id='')
	{
		
		$this->db->select_sum('s.total_item_cost');
		$this->db->select_sum('s.gross_total');
		$this->db->from('hw_sale_items s');
		$this->db->join('hw_sales sm', 's.sale_id = sm.sale_id', 'left');
		$this->db->join('product p', 's.product_id = p.product_id', 'left');
		if($srh_warehouse_id){
			$this->db->where("sm.warehouse_id",$srh_warehouse_id);
		}
		if($srh_to_date){
			$srh_to_date=date('Y-m-d',strtotime($srh_to_date . "+1 days"));
			$this->db->where("sm.sale_datetime <=",$srh_to_date);
		}
		if($srh_from_date){
			$this->db->where("sm.sale_datetime >=",$srh_from_date);
		}
			if($float_id){
			$this->db->where("sm.float_id",$float_id);
		}
		$query=$this->db->get();		
		$result = $query->row_array();
		if(isset($result['gross_total'])){
		    return $data=array(
		         'sale_total'=>$result['gross_total'],
		         'cost_total'=>$result['total_item_cost'],
		        );
		    
		}else{
		       return $data=array(
		         'sale_total'=>0,
		         'cost_total'=>0,
		        );
		}
	}
	
	function get_sale_return_items_summary($srh_warehouse_id='', $srh_to_date='', $srh_from_date='',$float_id='')
	{
		
		$this->db->select_sum('s.item_total_cost');
		$this->db->select_sum('s.gross_total');
		$this->db->from('hw_sales_return_items s');
		$this->db->join('hw_sales_return sm', 's.sl_rtn_id = sm.sl_rtn_id', 'left');
		$this->db->join('product p', 's.product_id = p.product_id', 'left');
		if($srh_warehouse_id){
			$this->db->where("sm.warehouse_id",$srh_warehouse_id);
		}
		if($srh_to_date){
			$srh_to_date=date('Y-m-d',strtotime($srh_to_date . "+1 days"));
			$this->db->where("sm.sl_rtn_datetime <=",$srh_to_date);
		}
		if($srh_from_date){
			$this->db->where("sm.sl_rtn_datetime >=",$srh_from_date);
		}
		if($float_id){
			$this->db->where("sm.float_id",$float_id);
		}
		$query=$this->db->get();		
		$result = $query->row_array();
		if(isset($result['gross_total'])){
		    return $data=array(
		         'sale_return_total'=>$result['gross_total'],
		         'cost_return_total'=>$result['item_total_cost'],
		        );
		    
		}else{
		       return $data=array(
		         'sale_return_total'=>0,
		         'cost_return_total'=>0,
		        );
		}
	}
	
	public function get_load_list()
	 {
		$this->db->select('f.*,u.user_first_name');
		$this->db->from('cashier_float_master f');
		$this->db->join('user u', 'u.user_id = f.user_id', 'left');
		$this->db->order_by("f.c_float_mstr_id", "desc");
		$query = $this->db->get();
		return $query->result_array();
		
	 }
	
	function get_all_sales_for_report_hw($srh_warehouse_id='', $srh_to_date='', $srh_from_date='',$float_id='') {
		$this->db->select('s.* , c.cus_name');
		$this->db->from('hw_sales s');
		$this->db->join('customer c', 's.customer_id = c.cus_id', 'left');
		$this->db->order_by("s.sale_id", "desc");
		if($srh_warehouse_id){
			$this->db->where("s.warehouse_id",$srh_warehouse_id);
		}
		if($srh_to_date){
			$srh_to_date=date('Y-m-d',strtotime($srh_to_date . "+1 days"));
			$this->db->where("s.sale_datetime <=",$srh_to_date);
		}
		if($srh_from_date){
			$this->db->where("s.sale_datetime >=",$srh_from_date);
		}
		if($float_id){
			$this->db->where("s.float_id",$float_id);
		}
		$query = $this->db->get();
		return $query->result_array();
	}
	
	function get_purches_items_total_summary($srh_warehouse_id='')
	{
		$this->db->select_sum('pi.sub_total');
		$this->db->from('purchase_items pi');
		$query=$this->db->get();		
		$result = $query->row_array();
		if(isset($result['sub_total'])){
		    return $result['sub_total'];
		}else{
		       return 0;
		}
	}
	
		function get_purches_return_items_total_summary($srh_warehouse_id='')
	{
		$this->db->select_sum('pi.gross_total');
		$this->db->from('purchase_return_items pi');
		if($srh_warehouse_id){
			$this->db->where("sm.warehouse_id",$srh_warehouse_id);
		}
		$query=$this->db->get();		
		$result = $query->row_array();
		if(isset($result['gross_total'])){
		    return $result['gross_total'];
		    
		}else{
		       return 0;
		}
	}
	
	function get_product_list_server_side($term, $srh_warehouse_id = '', $cat_srh = '', $subcategory = '')
	{
		$this->db->select('p.*');
		$this->db->from('product p');
		$this->db->order_by("p.product_name", "asc");
		$this->db->group_by('p.product_id');
		$cat_query = '';
		$cat_query = '';
		if ($cat_srh) {
			$cat_query = "AND cat_id='$cat_srh'";
		}
		if ($subcategory) {
			$cat_query = $cat_query . "AND sub_cat_id='$subcategory'";
		}
		$sql = "(p.product_name LIKE '%$term%' OR p.product_code LIKE '%$term%' OR p.product_oem_part_number LIKE '%$term%' OR p.product_part_no LIKE '%$term%') $cat_query";
		$this->db->where($sql);
		$this->db->limit(50, 0);
		$query = $this->db->get();
		return $query->result_array();
	}
	
	function get_product_purchesed_item($start, $length, $search_key_val,$product_id,$srh_from_date,$srh_to_date)
    {
        $this->db->select('b.batch_ref_number,b.batch_id,b.retail_cash');
        $this->db->select('pi.*');
        $this->db->select('pm.reference_no,pm.date,pm.id AS grn_master_id');
        $this->db->select('p.product_code,p.product_name');
        $this->db->from('purchase_items pi');
        $this->db->join('btatch_prroduct_grn bg', 'bg.grn_item_id = pi.id', 'inner');
        $this->db->join('purchases pm', 'pm.id = pi.purchase_id', 'inner');
        $this->db->join('batch_product b', 'b.batch_id = bg.batch_id', 'inner');
        $this->db->join('product p', 'p.product_id = pi.product_id', 'inner');
        if ($search_key_val) {
           
            $this->db->where("pi.product_id = '$product_id' AND b.batch_ref_number LIKE '%$search_key_val%' ");
             if($srh_to_date){
			$this->db->where("date(pm.date) <=",$srh_to_date);
		}
		if($srh_from_date){
			$this->db->where("date(pm.date) >=",$srh_from_date);
		}
            
        }else{
          $this->db->where("pi.product_id",$product_id); 
          if($srh_to_date){
		        $this->db->where("date(pm.date) <=",$srh_to_date);
		   }
		    if($srh_from_date){
			    $this->db->where("date(pm.date) >=",$srh_from_date);
		    }
        }
		$this->db->order_by("pi.id", "desc");
		$this->db->group_by("pi.id");
        if ($start != '' && $length != '') {
            $this->db->limit($length, $start);
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->result();
            } else {
                return false;
            }
        } else {
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->num_rows();
            } else {
                return false;
            }
        }
    }
    function get_product_sold_item($start, $length, $search_key_val,$product_id,$srh_from_date,$srh_to_date)
    {
        $this->db->select('b.batch_ref_number,b.batch_id,b.retail_cash');
        $this->db->select('si.*');
        $this->db->select('s.sale_reference_no,s.sale_datetime');
        $this->db->select('p.product_code,p.product_name');
        $this->db->from('hw_sale_items si');
        $this->db->join('hw_sales s', 's.sale_id = si.sale_id', 'inner');
        $this->db->join('batch_product b', 'b.batch_id = si.batch_id', 'inner');
        $this->db->join('product p', 'p.product_id = si.product_id', 'inner');
        if ($search_key_val) {
            $this->db->where("si.product_id = '$product_id' AND b.batch_ref_number LIKE '%$search_key_val%' ");
            if($srh_to_date){
			$this->db->where("date(s.sale_datetime) <=",$srh_to_date);
		    }
		    if($srh_from_date){
			$this->db->where("date(s.sale_datetime) >=",$srh_from_date);
		    }
        }else{
          $this->db->where("si.product_id",$product_id); 
          if($srh_to_date){
		        $this->db->where("date(s.sale_datetime) <=",$srh_to_date);
		   }
		    if($srh_from_date){
			    $this->db->where("date(s.sale_datetime) >=",$srh_from_date);
		    }
        }
		$this->db->order_by("si.id", "desc");
		$this->db->group_by("si.id");
        if ($start != '' && $length != '') {
            $this->db->limit($length, $start);
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->result();
            } else {
                return false;
            }
        } else {
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->num_rows();
            } else {
                return false;
            }
        }
    }
    
    function get_product_sale_return_item($start, $length, $search_key_val,$product_id,$srh_from_date,$srh_to_date)
    {
        $this->db->select('b.batch_ref_number,b.batch_id,b.retail_cash');
        $this->db->select('si.*');
        $this->db->select('s.sl_rtn_reference_no,s.sl_rtn_datetime');
        $this->db->select('p.product_code,p.product_name');
        $this->db->from('hw_sales_return_items si');
        $this->db->join('hw_sales_return s', 's.sl_rtn_id = si.sl_rtn_id', 'inner');
        $this->db->join('batch_product b', 'b.batch_id = si.batch_id', 'inner');
        $this->db->join('product p', 'p.product_id = si.product_id', 'inner');
        if ($search_key_val) {
            $this->db->where("si.product_id = '$product_id' AND b.batch_ref_number LIKE '%$search_key_val%' ");
            if($srh_to_date){
			$this->db->where("date(s.sl_rtn_datetime) <=",$srh_to_date);
		    }
		    if($srh_from_date){
			$this->db->where("date(s.sl_rtn_datetime) >=",$srh_from_date);
		    }
        }else{
          $this->db->where("si.product_id",$product_id); 
          if($srh_to_date){
		        $this->db->where("date(s.sl_rtn_datetime) <=",$srh_to_date);
		   }
		    if($srh_from_date){
			    $this->db->where("date(s.sl_rtn_datetime) >=",$srh_from_date);
		    }
        }
		$this->db->order_by("si.id", "desc");
		$this->db->group_by("si.id");
        if ($start != '' && $length != '') {
            $this->db->limit($length, $start);
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->result();
            } else {
                return false;
            }
        } else {
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->num_rows();
            } else {
                return false;
            }
        }
    }
    
    function get_product_grn_return_item($start, $length, $search_key_val,$product_id,$srh_from_date,$srh_to_date)
    {
        $this->db->select('b.batch_ref_number,b.batch_id,b.retail_cash');
        $this->db->select('si.*');
        $this->db->select('s.pur_r_ref_no,s.pur_return_date_time');
        $this->db->select('p.product_code,p.product_name');
        $this->db->from('purchase_return_items si');
        $this->db->join('purchase_return s', 's.pur_return_id = si.pur_r_item_id', 'inner');
        $this->db->join('batch_product b', 'b.batch_id = si.batch_id', 'inner');
        $this->db->join('product p', 'p.product_id = si.product_id', 'inner');
        if ($search_key_val) {
            $this->db->where("si.product_id = '$product_id' AND b.batch_ref_number LIKE '%$search_key_val%' ");
            if($srh_to_date){
			$this->db->where("date(s.pur_return_date_time) <=",$srh_to_date);
		    }
		    if($srh_from_date){
			$this->db->where("date(s.pur_return_date_time) >=",$srh_from_date);
		    }
        }else{
          $this->db->where("si.product_id",$product_id); 
          if($srh_to_date){
		        $this->db->where("date(s.pur_return_date_time) <=",$srh_to_date);
		   }
		    if($srh_from_date){
			    $this->db->where("date(s.pur_return_date_time) >=",$srh_from_date);
		    }
        }
		$this->db->order_by("si.pur_return_id", "desc");
		$this->db->group_by("si.pur_return_id");
        if ($start != '' && $length != '') {
            $this->db->limit($length, $start);
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->result();
            } else {
                return false;
            }
        } else {
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->num_rows();
            } else {
                return false;
            }
        }
    }
    
    function get_custom_sold_qty_total($product_id,$srh_from_date,$srh_to_date)
    {
        $this->db->select_sum('si.quantity');
        $this->db->from('hw_sale_items si');
        $this->db->join('hw_sales s', 's.sale_id = si.sale_id', 'inner');
        $this->db->where("si.product_id",$product_id); 
         if($srh_to_date){
		       $this->db->where("date(s.sale_datetime) <=",$srh_to_date);
		 }
		 if($srh_from_date){
		    $this->db->where("date(s.sale_datetime) >=",$srh_from_date);
		}
		$query = $this->db->get();
        $result=$query->row_array();
        if(isset($result['quantity']))
        {
            return $result['quantity'];
        }else{
       return 0;
        }
    }
    
    
    function get_custom_sale_return_qty_total($product_id,$srh_from_date,$srh_to_date)
    {
        $this->db->select_sum('si.quantity');
        $this->db->from('hw_sales_return_items si');
        $this->db->join('hw_sales_return s', 's.sl_rtn_id = si.sl_rtn_id', 'inner');
          $this->db->where("si.product_id",$product_id); 
          if($srh_to_date){
		        $this->db->where("date(s.sl_rtn_datetime) <=",$srh_to_date);
		   }
		    if($srh_from_date){
			    $this->db->where("date(s.sl_rtn_datetime) >=",$srh_from_date);
		    }
		    $query = $this->db->get();
        $result=$query->row_array();
        if(isset($result['quantity']))
        {
            return $result['quantity'];
        }else{
       return 0;
        }
    }
    
    function get_custom_grn_return_qty_total($product_id,$srh_from_date,$srh_to_date)
    {
       $this->db->select_sum('si.quantity');
        $this->db->from('purchase_return_items si');
        $this->db->join('purchase_return s', 's.pur_return_id = si.pur_r_item_id', 'inner');
       
          $this->db->where("si.product_id",$product_id); 
          if($srh_to_date){
		        $this->db->where("date(s.pur_return_date_time) <=",$srh_to_date);
		   }
		    if($srh_from_date){
			    $this->db->where("date(s.pur_return_date_time) >=",$srh_from_date);
		    }
		     $query = $this->db->get();
        $result=$query->row_array();
        if(isset($result['quantity']))
        {
            return $result['quantity'];
        }else{
       return 0;
        }
    }
    
    function get_custom_grn_qty_total($product_id,$srh_from_date,$srh_to_date)
    {
       $this->db->select_sum('si.quantity');
        $this->db->from('purchase_items si');
        $this->db->join('purchases s', 's.id = si.purchase_id', 'inner');
       
          $this->db->where("si.product_id",$product_id); 
          if($srh_to_date){
		        $this->db->where("date(s.date) <=",$srh_to_date);
		   }
		    if($srh_from_date){
			    $this->db->where("date(s.date) >=",$srh_from_date);
		    }
		     $query = $this->db->get();
        $result=$query->row_array();
        if(isset($result['quantity']))
        {
            return $result['quantity'];
        }else{
       return 0;
        }
    }
    	function get_all_cashier_summery_list($start,$length,$search) {
		
		$this->db->select('cs.*,u.user_first_name,u.user_last_name,w.name');
		$this->db->from('cashier_float_master cs');
		$this->db->join('user u', 'cs.user_id = u.user_id', 'inner');
		$this->db->join('warehouses w', 'cs.warehouse_id = w.id', 'inner');
		if(isset($search['value']))
		if($search['value']){		
				$this->db->like('u.user_first_name', $search['value']);
				$this->db->or_like('u.user_last_name', $search['value']);
			}
		if($start!='' && $length!=''){
            $this->db->limit($length,$start);
			}
		$this->db->order_by("cs.c_f_m_date_time", "desc");		
		if($start!='' && $length!=''){
			$query=$this->db->get();
			return $query->result_array();            
		}else{
			$query = $this->db->get();
			return $query->num_rows();
		}
	}
	//update product value report
	function get_custom_sold_value_total($product_id,$srh_from_date,$srh_to_date)
    {
        $this->db->select_sum('si.gross_total');
        $this->db->from('hw_sale_items si');
        $this->db->join('hw_sales s', 's.sale_id = si.sale_id', 'inner');
        $this->db->where("si.product_id",$product_id); 
         if($srh_to_date){
		       $this->db->where("date(s.sale_datetime) <=",$srh_to_date);
		 }
		 if($srh_from_date){
		    $this->db->where("date(s.sale_datetime) >=",$srh_from_date);
		}
		$query = $this->db->get();
        $result=$query->row_array();
        if(isset($result['gross_total']))
        {
            return $result['gross_total'];
        }else{
       return 0;
        }
    }
    
    function get_custom_sold_cost_value_total($product_id,$srh_from_date,$srh_to_date)
    {
        $this->db->select_sum('si.total_item_cost');
        $this->db->from('hw_sale_items si');
        $this->db->join('hw_sales s', 's.sale_id = si.sale_id', 'inner');
        $this->db->where("si.product_id",$product_id); 
         if($srh_to_date){
		       $this->db->where("date(s.sale_datetime) <=",$srh_to_date);
		 }
		 if($srh_from_date){
		    $this->db->where("date(s.sale_datetime) >=",$srh_from_date);
		}
		$query = $this->db->get();
        $result=$query->row_array();
        if(isset($result['total_item_cost']))
        {
            return $result['total_item_cost'];
        }else{
       return 0;
        }
    }
    
    
    function get_custom_sale_return_value_total($product_id,$srh_from_date,$srh_to_date)
    {
        $this->db->select_sum('si.gross_total');
        $this->db->from('hw_sales_return_items si');
        $this->db->join('hw_sales_return s', 's.sl_rtn_id = si.sl_rtn_id', 'inner');
          $this->db->where("si.product_id",$product_id); 
          if($srh_to_date){
		        $this->db->where("date(s.sl_rtn_datetime) <=",$srh_to_date);
		   }
		    if($srh_from_date){
			    $this->db->where("date(s.sl_rtn_datetime) >=",$srh_from_date);
		    }
		    $query = $this->db->get();
        $result=$query->row_array();
        if(isset($result['gross_total']))
        {
            return $result['gross_total'];
        }else{
       return 0;
        }
    }
    
    function get_custom_sale_return_cost_value_total($product_id,$srh_from_date,$srh_to_date)
    {
        $this->db->select_sum('si.item_total_cost');
        $this->db->from('hw_sales_return_items si');
        $this->db->join('hw_sales_return s', 's.sl_rtn_id = si.sl_rtn_id', 'inner');
          $this->db->where("si.product_id",$product_id); 
          if($srh_to_date){
		        $this->db->where("date(s.sl_rtn_datetime) <=",$srh_to_date);
		   }
		    if($srh_from_date){
			    $this->db->where("date(s.sl_rtn_datetime) >=",$srh_from_date);
		    }
		    $query = $this->db->get();
        $result=$query->row_array();
        if(isset($result['item_total_cost']))
        {
            return $result['item_total_cost'];
        }else{
       return 0;
        }
    }
    
    function getBasicProductMaterialMasterData(){
        $this->db->select('p.* , c.cat_name , s.sub_cat_name, u.unit_name,b.brand_name');
        $this->db->from('product_material p');
        $this->db->join('product_category c', 'c.cat_id = p.mat_cat_id', 'left');
        $this->db->join('product_sub_category s', 's.sub_cat_id = p.mat_sub_cat_id', 'left');
        $this->db->join('mstr_unit u', 'u.unit_id = p.product_mat_unit', 'left');
         $this->db->join('master_brand b', 'p.mat_brand_id = b.brand_id', 'left');
        $this->db->where('p.product_mat_status',1);
         $query = $this->db->get();
          return $query->result();
    }
    
    
    function get_stn_list($srh_from_date,$srh_to_date){
        $this->db->select('p.* ');
        $this->db->from('production_store_note_master p');
         if($srh_to_date){
		        $this->db->where("date(p.psnm_date_time) <=",$srh_to_date);
		   }
		    if($srh_from_date){
			    $this->db->where("date(p.psnm_date_time) >=",$srh_from_date);
		    }
		    $this->db->order_by('psnm_date_time','desc');
         $query = $this->db->get();
          return $query->result_array();
    }
    
    	function get_invoice_info_by_supplier_id($supp_id='', $srh_from_date='', $srh_to_date='')
	{
	
		$this->db->select('p.id,p.reference_no,p.date,p.grand_total,p.supp_invocie_no');
		$this->db->from('purchases p');
		$this->db->where("p.approval_status",1);
		if($supp_id){
			$this->db->where("p.supplier_id",$supp_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);
		}
		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);
		}	
		$this->db->order_by('p.date','asc');
		$query=$this->db->get();		
		return $query->result_array();
	}
	
	function get_total_paid_by_invoiceid($id){
	    $this->db->select_sum('p.allocation_amount ');
        $this->db->from('payment_given_items p');
       	$this->db->where("p.invoice_id",$id);
         $query = $this->db->get();
          $result= $query->row_array();
	    if(isset($result['allocation_amount'])){
	        return $result['allocation_amount'];
	    }else{
	        return 0;
	    }
	}
    
		
}