<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Sales_Model extends CI_Model {
  private $tableName = 'sales';
	
  function __construct() 
  {
    /* Call the Model constructor */
    parent::__construct();
  }

  
function get_all_sales_return_for_balance_report($srh_warehouse_id = '', $srh_to_date = '', $srh_from_date = '', $srh_customer_id = '')
    {
        $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');
        if ($srh_customer_id) {
            $this->db->where("sr.customer_id", $srh_customer_id);
        }
		$this->db->where('sale_pymnt_paying_by !=', 'Cash');
        if ($srh_warehouse_id) {
            $this->db->where("sr.warehouse_id", $srh_warehouse_id);
        }
        if ($srh_from_date) {
            $this->db->where("sr.sl_rtn_datetime >=", $srh_from_date);
        }
        if ($srh_to_date) {
            $this->db->where("sr.sl_rtn_datetime <=", $srh_to_date);
        }
        $this->db->order_by("sr.sl_rtn_id", "desc");
        $this->db->group_by('sr.sl_rtn_id');
		$query = $this->db->get();
        return $query->result_array();
}
  

  public function get_products_suggestions_get_by_delivery_id($term,$delivery_id,$werehouse_id){
	  
	$this->db->select('p.*');
	$this->db->from('product p');
	$this->db->join('lot_order_items li','li.product_id=p.product_id','inner');	
	if ($term) {
          $this->db->where("li.delivery_id = '$delivery_id' AND li.delivery_status = '1' AND li.item_status = '1' AND li.is_delete_item = '0' AND p.product_status = '1' AND p.product_code LIKE '%$term%'" );
          $this->db->or_where("li.delivery_id = '$delivery_id' AND li.delivery_status = '1' AND li.item_status = '1' AND li.is_delete_item = '0' AND p.product_status = '1' AND p.product_name LIKE '%$term%'");
        }	
	$this->db->group_by('p.product_id');
    $this->db->limit(10, 0);
    $query = $this->db->get();  
	return $query->result_array();
  }

 
 	//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();
		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');		
		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($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();
		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->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);
		}
		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($to){
		$this->db->limit($to,$from);
		}
		$query = $this->db->get();
		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);
		$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('hw_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("date(p.sale_pymnt_date_time) <=",$srh_to_date);//
		}
		if($srh_from_date){
			$this->db->where("date(p.sale_pymnt_date_time) >=",$srh_from_date);//
		}
	   $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->select('mb.bank_name');
       $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->join('mstr_bank mb', 'mb.bank_id = p.bank_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();
	   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);
	$this->db->where("sale_pymnt_paying_by != 'Cheque_Return'");
	$this->db->where("sale_payment_type","sale");
	$query=$this->db->get();
	if($query->row()->sale_pymnt_amount){
		return $query->row()->sale_pymnt_amount;
	}else {
		return 0;
	}
  }
  function get_total_paid_by_sale_id_hw($sale_id){

	$this->db->select_sum('sale_pymnt_amount');
	$this->db->from('sale_payments');
	$this->db->where("sale_id",$sale_id);
	$this->db->where("sale_pymnt_paying_by != 'Cheque_Return'");
	$this->db->where("sale_payment_type","sale");
	$query=$this->db->get();
	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('s.*');
		$this->db->select('it.invoice_type_name');
		$this->db->select('v.vehicle_code,v.vehicle_name');
		$this->db->select('du.user_first_name as driver_first_name,du.user_last_name as driver_last_name   ,du.invoice_name as driver_tpno');		 
		$this->db->select('u.user_first_name,u.user_last_name,u.invoice_name');
		$this->db->from('sales s');
		$this->db->join('user u','s.sales_rep_id=u.user_id','left');
		$this->db->join('marketing_loading_unloading ml','s.delivery_id=ml.id','left');
		$this->db->join('vehicles v','ml.lorry_id=v.vehicle_id','left');
		$this->db->join('user du','ml.rep_id=du.user_id','left');
		$this->db->join('master_invoice_type it','s.invoice_type=it.invoice_type_id','left');
		$this->db->where("s.sale_id", $id);
		$query = $this->db->get();
		return $query->row_array(); 
	 } 	
	
	public function get_add_sale_info($id)
	 {
		$this->db->select('*');
		$this->db->from('sales_additional_details');
		$this->db->where("sale_id", $id);
		$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.product_name, product.product_code,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();
	 }
	
	 function get_count($CRN){
		 $this->db->select('COUNT(*) as counts');
		 $this->db->from('sales');
		 $this->db->where("card_ref_number", $CRN);
		 $query = $this->db->get();
		 return $query;
	 }
	//Sales save
	function save_sales(&$supplier_data,$sale_id=false)	{

		if (!$sale_id)
		{
			$this->db->insert($this->tableName,$supplier_data);
			return $this->db->_error_message();
		}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='') {
		$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();
		return $query->result_array();
	}
	function get_all_sales_for_report_hw($srh_warehouse_id='',$srh_to_date='',$srh_from_date='',$sale_id='',$from='',$to='',$srh_customer_id='') {
		$this->db->select('s.* , c.cus_name ,SUM(p.sale_pymnt_amount) AS total_paid_amount');
		$this->db->from('hw_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",'hw_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();
		return $query->result_array();
	}
	//Sales all get
	public function get_all_sales($start,$length,$search,$direct_sale="") {		
		$this->db->select('sales.*, customer.cus_name');
		$this->db->from('sales');
		$this->db->join('customer', 'sales.customer_id = customer.cus_id', 'left');		
		if(isset($search['value']))
		if($search['value']){		
				$this->db->like('sales.card_ref_number', $search['value']);
				$this->db->or_like('sales.sale_reference_no', $search['value']);
				$this->db->or_like('customer.cus_name', $search['value']);
			}			
		//echo $direct_sale;
		if($direct_sale == 1){
			$this->db->where("sales.direct_sale","1");
		}else $this->db->where("sales.direct_sale","0");
		if($start!='' && $length!=''){
            $this->db->limit($length,$start);
			}
		$this->db->order_by("sales.sale_datetime", "desc");		
		//echo $this->db->last_query();echo "|";
		if($start!='' && $length!=''){
			$query=$this->db->get();
			return $query->result_array();            
		}else{
			$query = $this->db->get();
			return $query->num_rows();
		}
	}
	//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);
		$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%' 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');
		return $query->result_array();
	}

	function get_all_products() {
		$this->db->select('product'.'.*');
		$this->db->order_by("product_name", "asc");
		$this->db->where("product_id IS NOT NULL");
		$query = $this->db->get('product');
		return $query->result_array();
	}

	function get_products_suggestions_get_by_sales_id_for_return($term,$sales_id){
	$this->db->select('sale_items.*');
	$this->db->select('product.product_name,product.product_code,product.product_oem_part_number,product.product_part_no');	
	$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->limit(10, 0);
	$query = $this->db->get();
	return $query->result_array();
	}
	function get_products_suggestions_get_by_sales_id_for_return_hw($term,$sales_id){
	$this->db->select('hw_sale_items.*');
	$this->db->select('product.product_name,product.product_code,product.product_oem_part_number,product.product_part_no');	
	$this->db->from('hw_sale_items');	
	$this->db->join('product','hw_sale_items.product_id = product.product_id','left');
	$this->db->where("hw_sale_items.sale_id",$sales_id);
	$this->db->where("product.product_name LIKE '%$term%'");
	$this->db->or_where("hw_sale_items.sale_id",$sales_id );
	$this->db->where("product.product_code LIKE '%$term%'");
	$this->db->or_where("hw_sale_items.sale_id",$sales_id );
	$this->db->where("product.product_oem_part_number LIKE '%$term%'");
	$this->db->or_where("hw_sale_items.sale_id",$sales_id );
	$this->db->where("product.product_part_no LIKE '%$term%'");
	$this->db->limit(10, 0);
	$query = $this->db->get();
	return $query->result_array();
	}

	function get_sale_paynment_total_info($sale_id){
		$sale_payment_type="sale";
		$this->db->select_sum('sale_pymnt_amount');
		$this->db->from('sale_payments');
		$this->db->where("sale_payment_type",$sale_payment_type );
		$this->db->where("sale_id",$sale_id );
		$query = $this->db->get();
		return $query->row_array();
	}
	
	function get_sum_of_return_received_item_qty($product_id,$sale_id){

		$this->db->select_sum('quantity');
		$this->db->from('sales_return_items');
		$this->db->join('sales_return','sales_return_items.sl_rtn_id = sales_return.sl_rtn_id','left');
		$this->db->where("sales_return.sale_id",$sale_id);
		$this->db->where("sales_return_items.product_id",$product_id );
		$query = $this->db->get();
		return $query->row_array();
	}

	function get_sum_of_invoiced_item_qty($product_id,$rep_issue_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("sale_items.sales_rep_issue_id",$rep_issue_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_old($product_id,$rep_issue_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_issue_id",$rep_issue_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_sales_item_by_sale_id($sales_id)
	{
	$this->db->select('sale_items.id, sale_items.product_id , product.product_code , product.product_name ,sale_items.unit_price ,product.product_oem_part_number ,product.product_part_no ,sale_items.item_cost ,sale_items.quantity');
	$this->db->from('sale_items');
	$this->db->join('product','sale_items.product_id = product.product_id ','left');
	$this->db->join('sales','sales.sale_id = sale_items.sale_id ','left');	
	$this->db->where('sales.sale_id',$sales_id);
	$query = $this->db->get();
	return $query->result_array();
	}	
	
	function get_lorry_details_by_delivery_id($delivery_id){
		$this->db->select('vehicle_name');
		$this->db->from('vehicles');
		$this->db->join('marketing_loading_unloading','vehicles.vehicle_id = marketing_loading_unloading.lorry_id ','left');
		$this->db->where('marketing_loading_unloading.id',$delivery_id);
		$query = $this->db->get();
		return $query->result_array();
	}
	
	function get_rep_name_by_rep_id($id)
	 {  
	    $this->db->select('user_first_name,user_last_name');
		$this->db->from("user");
		$this->db->where("user_id", $id);
		$query = $this->db->get();
		return $query->row_array(); 
	 }
	
	 function get_cus_tot_credit($cus_id)
	{
		$this->db->select_sum('s.sale_total');
		$this->db->from('sales s');
		//$this->db->join('sales s', 's.sale_id = si.sale_id', 'left');
		$this->db->where('s.customer_id',$cus_id);		
		$query=$this->db->get();
		return $query->row()->sale_total;
	}
	
	function save_sales_additional($data){
		return $this->db->insert('sales_additional_details',$data);		
	}
	
	function get_sale_master_details($sale_id){
		$this->db->select('s.*');
		$this->db->from('sales s');
		$this->db->where('s.sale_id',$sale_id);		
		$query=$this->db->get();
		return $query->row_array();		
	}
	function get_sale_item_details($sale_id){
		$this->db->select('si.*');
		$this->db->from('sale_items si');
		$this->db->where('si.sale_id',$sale_id);		
		$query=$this->db->get();
		return $query->result_array();		
	}
	function get_product_cash_price($product_id){
		$this->db->select('p.product_price');
		$this->db->from('product p');
		$this->db->where('p.product_id',$product_id);		
		$query = $this->db->get();
		$data = $query->row_array();	
		if($data['product_price']){
			return $data['product_price'];
		}
		else{
			return 1;
		}	
	}
	function get_product_credit_price($product_id){
		$this->db->select('p.credit_salling_price');
		$this->db->from('product p');
		$this->db->where('p.product_id',$product_id);		
		$query = $this->db->get();
		$data = $query->row_array();	
		if($data['credit_salling_price']){
			return $data['credit_salling_price'];
		}
		else{
			return 1;
		}	
	}
	
	function update_sale_item_by_sale_item_id($id='',$data)
	{
		if($id>0){
			$this->db->where('id', $id);
		return $this->db->update('sale_items',$data);
		}
		else{
		return 0;	
		}
		
	}
	
	function return_cheque($id='',$data)
	{
		if($id>0){
			$this->db->where('sale_pymnt_id', $id);
		return $this->db->update('sale_payments',$data);
		}
		else{
		return 0;	
		}
		
	}
	
		function get_all_sum_sales_for_summery_report($srh_warehouse_id='',$srh_to_date='',$srh_from_date='',$sale_id='',$from='',$to='',$srh_customer_id='') {
		
					$this->db->select('SUM(`sale_total`) AS sale_total');
					$this->db->from('sales');
//					$this->db->join('customer','customer.cus_id = sales.customer_id');
//					$this->db->join('sales_return','customer.cus_id = sales_return.customer_id');
					$this->db->where('sales.customer_id',$srh_customer_id);					
				if($srh_warehouse_id){	
					$this->db->where("sales.warehouse_id",$srh_warehouse_id);//("id !=",$id);
				}				
				if($srh_to_date){
					$srh_to_date = date('Y-m-d',strtotime($srh_to_date. ""));
					$this->db->where("sales.sale_datetime <=",$srh_to_date);			
				}
				if($srh_from_date){
					$srh_from_date = date('Y-m-d',strtotime($srh_from_date));
					$this->db->where("sales.sale_datetime >=",$srh_from_date);//("id !=",$id);
				}
					$query = $this->db->get();		
					//print_r($this->db->last_query());
					$result = $query->result_array();		
		return $result;
		}	
	
		function get_sum_sales_return($srh_warehouse_id='',$srh_to_date='',$srh_from_date='',$sale_id='',$from='',$to='',$srh_customer_id='') {
		
				$this->db->select('SUM(`sl_rtn_total`) AS sale_return_total');
				$this->db->from('sales_return');
				$this->db->where('customer_id',$srh_customer_id);					
				if($srh_warehouse_id){	
					$this->db->where("sales_return.warehouse_id",$srh_warehouse_id);//("id !=",$id);
				}				
				if($srh_to_date){
					$srh_to_date=date('Y-m-d',strtotime($srh_to_date . ""));
					$this->db->where("sales_return.sl_rtn_datetime <=",$srh_to_date);
				}
				if($srh_from_date){
					$srh_from_date = date('Y-m-d',strtotime($srh_from_date));
					$this->db->where("sales_return.sl_rtn_datetime >=",$srh_from_date);//("id !=",$id);
				}
					$query = $this->db->get();
					$result= $query->row_array();		
		return $result;
	
	}
	function getSumPaymentsForBalanceRep($srh_warehouse_id='',$srh_to_date='',$srh_from_date='',$srh_type='',$srh_payment_term='',$ss_user_id='',$srh_customer_id='',$return_name='')
   {
	   $warehouse_id='';
	    $sel='SUM(p.sale_pymnt_amount) AS '.$return_name.'';
	   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('sale_pymnt_paying_by !=','Cheque_Return');
	    if($ss_user_id) $this->db->join('user u', 'u.user_id = p.user_id', 'left');
		if($srh_from_date){			
			$this->db->where("p.sale_pymnt_added_date_time >=",$srh_from_date);//("id !=",$id);
		}		
		if($return_name == 'tot_sp'){
				   $this->db->where('sale_pymnt_paying_by !=','Cheque_Return');
			}		
		if($return_name == 'unrealized_chq'){
			$srh_to_date=date('Y-m-d',strtotime($srh_to_date. ""));
			$this->db->where("p.sale_pymnt_date_time >=",$srh_to_date);//("id !=",$id);			
			}else
		if($srh_to_date){			
			$srh_to_date=date('Y-m-d',strtotime($srh_to_date. ""));
			$this->db->where("p.sale_pymnt_added_date_time <=",$srh_to_date);//("id !=",$id);
		}		
	   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_customer_id)
		{
		$this->db->where("b.warehouse_id =".$srh_warehouse_id." AND b.customer_id=".$srh_customer_id."" );//	   
		}
		else		
		 if($srh_warehouse_id){	   
	   $this->db->where("b.warehouse_id",$srh_warehouse_id);//	   
	   }else{
	 	$this->session->userdata('ss_warehouse_id');
		$warehouse_id =	$this->session->userdata('ss_warehouse_id');		
//		echo $warehouse_id;
		$this->db->where("b.warehouse_id",$warehouse_id);
		//		echo "INN";
		}		   
	   /*
	   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;
     }
   }
	
	//-----------------------------------------------------------------------------
	
	function get_sale_payment_cus_id($py_id)
	{
		$this->db->select('s.customer_id');
        $this->db->from('sale_payments p');
	    $this->db->join('sales s', 's.sale_id = p.sale_id', 'left');
		$this->db->where("p.sale_pymnt_id",$py_id);
		$query = $this->db->get();
		$result= $query->row_array();		
		return $result['customer_id'];	   
	}

	

	function get_direct_sale_qty($warehouse_id='',$product_id,$srh_to_date='',$srh_from_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.direct_sale',1);
		
		if($warehouse_id)
		$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);
		}		
			$date=date_create("2020-05-29");
			$srh_from_date= date_format($date,"Y-m-d");
			$this->db->where("s.sale_datetime >=",$srh_from_date);//("id !=",$id);
		
		$query=$this->db->get();
		return $data['quantity']=$query->row()->quantity;
	
	}
	
	
	
	

}