<?php
if (!defined('BASEPATH'))
    exit('No direct script access allowed');
class Reports_Marketing_Model extends CI_Model
{
    private $tableName = 'sales';
    function __construct()
    {
        /* Call the Model constructor */
        parent::__construct();
    }
    function get_active_sales_rep()
    {
        $this->db->select('user_id,user_first_name,user_last_name');
        $this->db->from('user');
        $this->db->where('group_id', 4);
        $this->db->where('user_status', 1);
        $query = $this->db->get();
        return $query->result_array();
    }
    function get_active_cash_collector()
    {
        $this->db->select('user_id,user_first_name,user_last_name');
        $this->db->from('user');
        $this->db->where('group_id', 5);
        $this->db->where('user_status', 1);
        $query = $this->db->get();
        return $query->result_array();
    }
    function get_daily_cash_by_collector_id_and_date($collector_id, $date)
    {
        $date2          = date_create($date);
        $converted_date = date_format($date2, "Y-m-d");
        $this->db->select('sales.card_ref_number , sales.sale_reference_no');
        $this->db->select('sale_payments.sale_pymnt_amount');
        $this->db->from('sale_payments');
        $this->db->join('sales', 'sale_payments.sale_id=sales.sale_id', 'left');
        $this->db->where('sale_payments.user_id', $collector_id);
        $this->db->where('sale_payments.sale_pymnt_date_time LIKE', $converted_date . "%");
        $quary = $this->db->get();
        return $quary->result_array();
    }
    function get_daily_sale_by_rep_id_and_date($sale_rep_id, $date)
    {
        $date2          = date_create($date);
        $converted_date = date_format($date2, "Y-m-d");
        $this->db->select('s.card_ref_number , s.sale_reference_no');
        $this->db->select('si.quantity , si.gross_total');
        $this->db->select('p.product_name,p.product_code');
		$this->db->select('c.cus_name,c.cus_code');
        $this->db->from('sales s');
        $this->db->join('sale_items si', 's.sale_id=si.sale_id', 'left');
        $this->db->join('product p', 'si.product_id=p.product_id', 'left');
        $this->db->join('customer c', 's.customer_id=c.cus_id', 'left');
		if($sale_rep_id){
		$this->db->where('s.sales_rep_id ', $sale_rep_id);	
		}
        
        $this->db->where('s.sale_datetime LIKE', $converted_date . "%");
        $this->db->group_by('si.id');
        $quary = $this->db->get();
        return $quary->result_array();
    }
	
    function get_daily_sale_by_rep_id_and_date_sales_note_report($sale_rep_id, $date,$to_date='')
    {
		$to_date	=	date("Y-m-d", strtotime($to_date));
        $date2          = date_create($date);
        $converted_date = date_format($date2, "Y-m-d");
        $this->db->select('sales.card_ref_number , sales.sale_reference_no ,sales.totat_down_payment,sale_datetime');
        $this->db->select('sale_items.quantity , sale_items.gross_total');
        $this->db->select('product.product_name');
        $this->db->select('sale_payments.sale_pymnt_amount ,sale_payments.sale_pymnt_paying_by');
        $this->db->from('sales');
        $this->db->join('sale_items', 'sales.sale_id=sale_items.sale_id', 'left');
        $this->db->join('product', 'sale_items.product_id=product.product_id', 'left');
        $this->db->join('sale_payments', 'sales.sale_id=sale_payments.sale_id', 'left');
        $this->db->where('sales_rep_id ', $sale_rep_id);
        $this->db->where('DATE(sale_datetime ) >= "'.$converted_date.'"');
		$this->db->where('DATE(sale_datetime ) <= "'.$to_date.'"');
        $this->db->group_by('sale_items.id');
        $quary = $this->db->get();
        return $quary->result_array();
    }
    function get_sales_note_report_rep_id_and_date($sale_rep_id, $from_date, $to_date)
    {
        $date1               = date_create($from_date);
        $converted_from_date = date_format($date1, "Y-m-d");
        $converted_to_date   = date('Y-m-d', strtotime($to_date . ' +1 day'));
		
        $this->db->select('sales.card_ref_number , sales.sale_reference_no ,sales.totat_down_payment,sales.sale_datetime');
        $this->db->select('sale_items.quantity , sale_items.gross_total');
        $this->db->select('product.product_name');
        $this->db->select('SUM(sale_payments.sale_pymnt_amount) AS  sale_pymnt_amount,sale_payments.sale_pymnt_paying_by');
        $this->db->from('sales');
        $this->db->join('sale_items', 'sales.sale_id=sale_items.sale_id', 'left');
        $this->db->join('product', 'sale_items.product_id=product.product_id', 'left');
        $this->db->join('sale_payments', 'sales.sale_id=sale_payments.sale_id', 'left');
        $this->db->where('sales_rep_id ', $sale_rep_id);
        $this->db->where('sale_datetime >= ', $converted_from_date);
        $this->db->where('sale_datetime <= ', $converted_to_date);
        $this->db->group_by('sale_items.id');
        $quary = $this->db->get();
        return $quary->result_array();
    }
    public function get_daily_sale_by_rep_id_and_date_final($sale_rep_id, $date)
    {
        $date2          = date_create($date);
        $converted_date = date_format($date2, "Y-m-d");
        $this->db->select('sales.card_ref_number , sales.sale_reference_no ,sales.totat_down_payment,sales.sale_total');
        $this->db->select('SUM(sale_payments.sale_pymnt_amount) AS  sale_pymnt_amount ,sale_payments.sale_pymnt_paying_by');
        $this->db->select('customer.cus_name ');
        $this->db->from('sales');
        $this->db->join('sale_payments', 'sales.sale_id=sale_payments.sale_id', 'left');
        $this->db->join('customer', 'sales.customer_id=customer.cus_id', 'left');
		if($sale_rep_id){
		 $this->db->where('sales.sales_rep_id ', $sale_rep_id);	
		}
       
        $this->db->where('sales.sale_datetime LIKE', $converted_date . "%");
        $this->db->group_by('sales.sale_id');
        $quary = $this->db->get();
        return $quary->result_array();
    }
    public function get_sales_note_report_rep_id_and_date_invoice_wise($sale_rep_id, $from_date, $to_date)
    {
        $date1               = date_create($from_date);
        $converted_from_date = date_format($date1, "Y-m-d");
        $converted_to_date   = date('Y-m-d', strtotime($to_date . ' +1 day'));
        $this->db->select('sales.card_ref_number , sales.sale_reference_no ,sales.totat_down_payment,sales.sale_total,sales.sale_datetime');
        $this->db->select('SUM(sale_payments.sale_pymnt_amount) AS  sale_pymnt_amount ,sale_payments.sale_pymnt_paying_by');
        $this->db->select('customer.cus_name ');
        $this->db->from('sales');
        $this->db->join('sale_payments', 'sales.sale_id=sale_payments.sale_id', 'left');
        $this->db->join('customer', 'sales.customer_id=customer.cus_id', 'left');
        $this->db->where('sales.sales_rep_id ', $sale_rep_id);
        $this->db->where('sale_datetime >= ', $converted_from_date);
        $this->db->where('sale_datetime <= ', $converted_to_date);
        $this->db->group_by('sales.sale_id');
        $quary = $this->db->get();
        return $quary->result_array();
    }
    public function get_daily_cash_by_collector_and_date_summary($sale_rep_id, $date)
    {
        $date2          = date_create($date);
        $converted_date = date_format($date2, "Y-m-d");
        //sale_payments.sale_pymnt_paying_by = "cash"
        //$this->db->select('SUM(sale_payments.sale_pymnt_amount ) AS all_sale_pymnt_amount');
        //$this->db->select('user.user_first_name');
        //$this->db->from('sale_payments');
        $sel            = 'SUM(p.sale_pymnt_amount) AS all_sale_pymnt_amount ,c.route_name, 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('routes c', 'c.route_id = b.route_id', 'left');
        $this->db->join('user u', 'u.user_id = p.user_id', 'left');
        //$this->db->join('user','sale_payments.user_id =user.user_id','left');
        //$this->db->join('routes','sales.route_id = routes.route_id','inner');
        //$this->db->join('sales','sale_payments.sale_id =sales.sale_id AND routes.route_id=sales.route_id   ','lefts');
        //$this->db->where('sale_payments.sale_pymnt_paying_by !="Return Payment"' );
        $this->db->where('p.user_id', $sale_rep_id);
        $this->db->where('p.sale_pymnt_date_time  LIKE', $converted_date . "%");
        $this->db->where('p.sale_pymnt_paying_by !="Return Payment"');
        $quary = $this->db->get();
        return $quary->row_array();
    }
    public function get_daily_sale_by_rep_id_and_date_summary($sale_rep_id, $date)
    {
        $date2          = date_create($date);
        $converted_date = date_format($date2, "Y-m-d");
        $this->db->select('SUM(sales.totat_down_payment) AS all_downpayment');
        $this->db->select('SUM(sales.sale_total) AS all_sale_total ');
        $this->db->select('user.user_first_name');
        $this->db->from('sales');
        $this->db->join('user', 'sales.sales_rep_id =user.user_id', 'left');
		if($sale_rep_id){
        $this->db->where('sales.sales_rep_id ', $sale_rep_id);
		}
        $this->db->where('sales.sale_datetime LIKE', $converted_date . "%");
        $quary = $this->db->get();
        return $quary->row_array();
    }
    public function get_sale_by_rep_id_and_date_summary($sale_rep_id, $from_date, $to_date)
    {
        $date1               = date_create($from_date);
        $converted_from_date = date_format($date1, "Y-m-d");
        $converted_to_date   = date('Y-m-d', strtotime($to_date . ' +1 day'));
        $this->db->select('SUM(sales.totat_down_payment) AS all_downpayment');
        $this->db->select('SUM(sales.sale_total) AS all_sale_total ');
        $this->db->select('user.user_first_name');
        $this->db->from('sales');
        $this->db->join('user', 'sales.sales_rep_id =user.user_id', 'left');
        $this->db->where('sales.sales_rep_id ', $sale_rep_id);
        $this->db->where('sales.sale_datetime >=', $converted_from_date);
        $this->db->where('sales.sale_datetime <=', $converted_to_date);
        $quary = $this->db->get();
        return $quary->row_array();
    }
    public function get_daily_sale_by_rep_id_and_date_summary_cash($sale_rep_id, $date)
    {
        $date2          = date_create($date);
        $converted_date = date_format($date2, "Y-m-d");
        $this->db->select('SUM(sale_payments.sale_pymnt_amount ) AS all_cash_sale_pymnt_amount');
        $this->db->from('sale_payments');    
        $this->db->where('sale_payments.sale_pymnt_paying_by ="cash"');
		if($sale_rep_id){
        $this->db->where('sale_payments.user_id', $sale_rep_id);
		}
        $this->db->where('sale_payments.sale_pymnt_date_time LIKE', $converted_date ."%");
        $quary = $this->db->get();
        return $quary->row_array();
    }
    public function get_sale_by_rep_id_and_date_summary_cash($sale_rep_id, $from_date, $to_date)
    {
        $date1               = date_create($from_date);
        $converted_from_date = date_format($date1, "Y-m-d");
       	$converted_to_date   = date('Y-m-d', strtotime($to_date . ' +1 day'));
        $this->db->select('SUM(sale_payments.sale_pymnt_amount ) AS all_cash_sale_pymnt_amount');
        $this->db->from('sales');
        $this->db->join('sale_payments', 'sales.sale_id=sale_payments.sale_id ', 'left');
        $this->db->join('user', 'sales.sales_rep_id =user.user_id', 'left');
        $this->db->join('routes', 'sales.route_id =routes.route_id', 'left');
        $this->db->where('sale_payments.sale_pymnt_paying_by ="cash"');
        $this->db->where('sale_payments.sale_pymnt_note !="cash collector received"');
        $this->db->where('sales.sales_rep_id', $sale_rep_id);
        $this->db->where('sale_datetime >= ', $converted_from_date);
        $this->db->where('sale_datetime <= ', $converted_to_date);
        $quary = $this->db->get();
        return $quary->row_array();
    }
    public function get_sale_by_rep_id_and_date_summary_cheque($sale_rep_id, $from_date, $to_date)
    {
        $date1               = date_create($from_date);
        $converted_from_date = date_format($date1, "Y-m-d");
        $converted_to_date   = date('Y-m-d', strtotime($to_date . ' +1 day'));
        $this->db->select('SUM(sale_payments.sale_pymnt_amount ) AS all_cheque_sale_pymnt_amount');
        $this->db->from('sales');
        $this->db->join('sale_payments', 'sales.sale_id=sale_payments.sale_id ', 'left');
        $this->db->join('user', 'sales.sales_rep_id =user.user_id', 'left');
        $this->db->join('routes', 'sales.route_id =routes.route_id', 'left');
        $this->db->where('sale_payments.sale_pymnt_paying_by ="Cheque"');
        $this->db->where('sale_payments.sale_pymnt_note !="cash collector received"');
        $this->db->where('sales.sales_rep_id', $sale_rep_id);
        $this->db->where('sale_datetime >= ', $converted_from_date);
        $this->db->where('sale_datetime <= ', $converted_to_date);
        $quary = $this->db->get();
        return $quary->row_array();
    }
    public function get_sale_by_rep_id_and_date_summary_total($sale_rep_id, $from_date, $to_date)
    {
        $date1               = date_create($from_date);
        $converted_from_date = date_format($date1, "Y-m-d");
        $converted_to_date   = date('Y-m-d', strtotime($to_date . ' +1 day'));
        $this->db->select('SUM(sale_payments.sale_pymnt_amount ) AS all_sale_pymnt_amount');
        $this->db->from('sales');
        $this->db->join('sale_payments', 'sales.sale_id=sale_payments.sale_id ', 'left');
        $this->db->join('user', 'sales.sales_rep_id =user.user_id', 'left');
        $this->db->join('routes', 'sales.route_id =routes.route_id', 'left');
        //$this->db->where('sale_payments.sale_pymnt_paying_by ="Cheque"' );
        $this->db->where('sale_payments.sale_pymnt_note !="cash collector received"');
        $this->db->where('sales.sales_rep_id', $sale_rep_id);
        $this->db->where('sale_datetime >= ', $converted_from_date);
        $this->db->where('sale_datetime <= ', $converted_to_date);
        $quary = $this->db->get();
        return $quary->row_array();
    }
    public function get_daily_sale_by_rep_id_and_date_summary_cheque($sale_rep_id, $date)
    {
        $date2          = date_create($date);
        $converted_date = date_format($date2, "Y-m-d");
        $this->db->select('SUM(sale_payments.sale_pymnt_amount ) AS all_cheque_sale_pymnt_amount');
        $this->db->from('sale_payments');    
        $this->db->where('sale_payments.sale_pymnt_paying_by ="Cheque"');
		if($sale_rep_id){
        $this->db->where('sale_payments.user_id', $sale_rep_id);
		}
        $this->db->where('sale_payments.sale_pymnt_date_time LIKE', $converted_date ."%");
        $quary = $this->db->get();
        return $quary->row_array();
    }
	
	 function get_active_delivery()
    {
        $this->db->select('m.id,m.loading_ref_no,v.vehicle_code');
        $this->db->from('marketing_loading_unloading m');
		$this->db->join('vehicles v', 'v.vehicle_id =m.lorry_id', 'left');
        $this->db->where('m.status', 1);
        $this->db->where('m.approval_status', 1);
        $query = $this->db->get();
        return $query->result_array();
    }
	
	function get_invoice_list_product($deliver_id,$from_date)
	{	
		$date2          = date_create($from_date);
        $converted_date = date_format($date2, "Y-m-d");		
		$this->db->select('si.product_id,p.product_code,p.product_name');
        $this->db->from('sale_items si');
		$this->db->join('product p', 'p.product_id=si.product_id', 'inner');
		$this->db->join('sales s', 's.sale_id=si.sale_id', 'inner');
		if($from_date){
		$this->db->where('s.sale_datetime LIKE',$converted_date."%");	
		}
		if($deliver_id)
		{
		$this->db->where('s.delivery_id',$deliver_id);	
		}
		
        
		$this->db->group_by('si.product_id');
        $query = $this->db->get();
        return $query->result_array();	
		
	}
	
	function get_sold_qty_and_sub_total($product_id,$deliver_id,$from_date)
	{	
		$date2          = date_create($from_date);
        $converted_date = date_format($date2, "Y-m-d");		
		
		$this->db->select_sum('si.quantity');
		$this->db->select_sum('si.gross_total');
        $this->db->from('sale_items si');
		$this->db->join('sales s', 's.sale_id=si.sale_id', 'inner');
		if($from_date){
		$this->db->where('s.sale_datetime LIKE',$converted_date."%");	
		}
		if($deliver_id){
		$this->db->where('s.delivery_id',$deliver_id);	
		}
		$this->db->where('si.product_id',$product_id);
        $query = $this->db->get();
        return $query->row_array();	
		
	}
	
	function get_return_list_product($deliver_id,$from_date)
	{
		$date2          = date_create($from_date);
        $converted_date = date_format($date2, "Y-m-d");		
		$this->db->select('p.product_code,p.product_name,si.return_qty,si.return_date_time');
        $this->db->from('customer_order_return_items si');
		$this->db->join('product p', 'p.product_id=si.product_id', 'inner');
		if($from_date){
		$this->db->where('si.return_date_time LIKE',$converted_date."%");	
		}
		if($deliver_id){
		$this->db->where('si.delivery_id',$deliver_id);
		}
		$query = $this->db->get();
        return $query->result_array();	
	}
	
	function get_all_customers_by_route_id($route_id,$start,$length,$search) {
		$this->db->select('customer.*');		
		//$this->db->where("customer.cus_status",1);
		if($route_id){
			$this->db->where("customer.route_id",$route_id);	
		}
		if(isset($search['value']))
		if($search['value']){		
				$this->db->like('customer.cus_code', $search['value']);
				$this->db->or_like('customer.cus_name', $search['value']);
			}
		if($start!='' && $length!=''){
            $this->db->limit($length,$start);
			}			
		$this->db->order_by("customer.cus_name", "asc");
		$query = $this->db->get('customer');
		if($start!='' && $length!=''){
			return $query->result_array();            
		}else{
			return $query->num_rows();
		}
	}
	
	function get_all_sale_total_by_customer_id($customer_id)
	{
		$this->db->select_sum('s.sale_total');
        $this->db->from('sales s');
		//$this->db->join('customer c', 'c.cus_id=s.customer_id', 'left');
		$this->db->where("s.customer_id",$customer_id);
		$query = $this->db->get();
		$data = $query->row_array();  
		
		if($data['sale_total']){
			return $data['sale_total'];
		}
		else{
			return 0;
		}
	}
	
	function get_all_paid_total_by_customer_id($customer_id)
	{
		$this->db->select_sum('sp.sale_pymnt_amount');
        $this->db->from('sale_payments sp');
		$this->db->join('sales s', 's.sale_id=sp.sale_id', 'left');
		$this->db->where("s.customer_id",$customer_id);
		$query = $this->db->get();
		$data = $query->row_array();  
		
		if($data['sale_pymnt_amount']){
			return $data['sale_pymnt_amount'];
		}
		else{
			return 0;
		}
	}
	
	function get_all_paid_total_by_sale_id($sale_id)
	{
		$this->db->select_sum('sp.sale_pymnt_amount');
        $this->db->from('sale_payments sp');
		$this->db->where("sp.sale_id",$sale_id);
		$query = $this->db->get();
		$data = $query->row_array();  
		
		if($data['sale_pymnt_amount']){
			return $data['sale_pymnt_amount'];
		}
		else{
			return 0;
		}
	}
	
	function get_all_sale_list_by_customer_id($customer_id)
	{
		$this->db->select('s.sale_id,s.sale_reference_no,s.card_ref_number,s.due_date,s.sale_total,s.sale_datetime,s.due_date');
		$this->db->select('it.invoice_type_name,it.class_name_report');
        $this->db->from('sales s');
		$this->db->join('master_invoice_type it', 's.invoice_type=it.invoice_type_id', 'left');
		$this->db->where("s.customer_id",$customer_id);
		$query = $this->db->get();
		return $query->result_array(); 	
	}
	function get_all_sale_total_by_route_id($route_id)
	{
		$this->db->select_sum('s.sale_total');
        $this->db->from('sales s');
		$this->db->join('customer c', 'c.cus_id=s.customer_id', 'left');
		if($route_id){$this->db->where("c.route_id",$route_id);}
		
		$query = $this->db->get();
		$data = $query->row_array();		
		if($data['sale_total']){
			return $data['sale_total'];
		}
		else{
			return 0;
		}
	}
	function get_all_paid_total_by_route_id($route_id)
	{
		$this->db->select_sum('sp.sale_pymnt_amount');
        $this->db->from('sale_payments sp');
		$this->db->join('sales s', 's.sale_id=sp.sale_id', 'left');
		$this->db->join('customer c', 'c.cus_id=s.customer_id', 'left');
		if($route_id){$this->db->where("c.route_id",$route_id);}		
		$query = $this->db->get();
		$data = $query->row_array();  		
		if($data['sale_pymnt_amount']){
			return $data['sale_pymnt_amount'];
		}
		else{
			return 0;
		}
	}
	
	function get_invoice_list_product_item($from_date,$todate,$rep_id='')
	{		
		$this->db->select('si.product_id,p.product_code,p.product_name');
        $this->db->from('sale_items si');
		$this->db->join('product p', 'p.product_id=si.product_id', 'inner');
		$this->db->join('sales s', 's.sale_id=si.sale_id', 'inner');
		if($from_date){
		$this->db->where('s.sale_datetime >=',$from_date);	
		}
		if($todate)
		{
		$this->db->where('s.sale_datetime <=',$todate);	
		}	
		if($rep_id)
		{
		$this->db->where('s.sales_rep_id',$rep_id);	
		}
		$this->db->group_by('si.product_id');
        $query = $this->db->get();
        return $query->result_array();		
	}
	
	function get_sold_qty_and_sub_total_by_period($from_date,$to_date,$product_id,$rep_id='')
	{			
		$this->db->select_sum('si.quantity');
		$this->db->select_sum('si.net_total');
        $this->db->from('sale_items si');
		$this->db->join('sales s', 's.sale_id=si.sale_id', 'inner');
		if($from_date){
		$this->db->where('s.sale_datetime >=',$from_date);	
		}
		if($to_date)
		{
		$this->db->where('s.sale_datetime <=',$to_date);	
		}
		if($rep_id)
		{
		$this->db->where('s.sales_rep_id',$rep_id);	
		}
		$this->db->where('si.product_id',$product_id);
        $query = $this->db->get();
        return $query->row_array();	
	}
	
	function get_sold_qty_and_sub_total_by_period_summary($from_date,$to_date,$rep_id='')
	{			
		$this->db->select_sum('si.quantity');
		$this->db->select_sum('si.net_total');
        $this->db->from('sale_items si');
		$this->db->join('sales s', 's.sale_id=si.sale_id', 'inner');
		if($from_date){
		$this->db->where('s.sale_datetime >=',$from_date);	
		}
		if($to_date)
		{
		$this->db->where('s.sale_datetime <=',$to_date);	
		}
		if($rep_id)
		{
		$this->db->where('s.sales_rep_id',$rep_id);	
		}
		//$this->db->where('si.product_id',$product_id);
        $query = $this->db->get();
        return $query->row_array();	
	}
	function get_invoice_list($from_date,$todate,$rep_id='')
	{		
		$this->db->select('s.*');
		$this->db->select('c.cus_name,c.cus_code');
        $this->db->from('sales s');
		$this->db->join('customer c', 'c.cus_id=s.customer_id', 'left');
		if($from_date){
		$this->db->where('s.sale_datetime >=',$from_date);	
		}
		if($todate)
		{
		$this->db->where('s.sale_datetime <=',$todate);	
		}	
		if($rep_id)
		{
		$this->db->where('s.sales_rep_id',$rep_id);	
		}
        $query = $this->db->get();
        return $query->result_array();		
	}
	
	function get_invoice_summary($from_date,$todate,$rep_id='')
	{		
		$this->db->select_sum('s.sale_inv_gross_total');
		$this->db->select_sum('s.sale_total');
		$this->db->select_sum('s.cost_total');
		$this->db->select_sum('s.total_default_discount_value');
		//$this->db->select('c.cus_name,c.cus_code');
        $this->db->from('sales s');
		//$this->db->join('customer c', 'c.cus_id=s.customer_id', 'left');
		if($from_date){
		$this->db->where('s.sale_datetime >=',$from_date);	
		}
		if($todate)
		{
		$this->db->where('s.sale_datetime <=',$todate);	
		}	
		if($rep_id)
		{
		$this->db->where('s.sales_rep_id',$rep_id);	
		}
        $query = $this->db->get();
        return $query->row_array();		
	}
	function get_payment_list($from_date,$todate,$route_id='')
	{		
		$this->db->select('sp.*');
		$this->db->select('s.sale_reference_no,s.card_ref_number');
		$this->db->select('c.cus_name,c.cus_code');
        $this->db->from('sale_payments sp');
		$this->db->join('sales s', 'sp.sale_id=s.sale_id', 'left');
		$this->db->join('customer c', 'c.cus_id=s.customer_id', 'left');
		if($from_date){
		$this->db->where('sp.sale_pymnt_date_time >=',$from_date);	
		}
		if($todate)
		{
		$this->db->where('sp.sale_pymnt_date_time <=',$todate);	
		}	
		if($route_id)
		{
		$this->db->where('c.route_id',$route_id);	
		}
		$this->db->where('sp.sale_payment_type',"sale");
        $query = $this->db->get();
        return $query->result_array();		
	}
	function get_payment_cash_summary($from_date,$todate,$route_id='')
	{		
		$this->db->select_sum('sp.sale_pymnt_amount');
		$this->db->from('sale_payments sp');
		$this->db->join('sales s', 'sp.sale_id=s.sale_id', 'left');
		$this->db->join('customer c', 'c.cus_id=s.customer_id', 'left');
		if($from_date){
		$this->db->where('sp.sale_pymnt_date_time >=',$from_date);	
		}
		if($todate)
		{
		$this->db->where('sp.sale_pymnt_date_time <=',$todate);	
		}	
		if($route_id)
		{
		$this->db->where('c.route_id',$route_id);	
		}
		$this->db->where('sp.sale_payment_type',"sale");
		$this->db->where('sp.sale_pymnt_paying_by',"Cash");
        $query = $this->db->get();
        return $query->row_array();;		
	}
	function get_payment_cheque_summary($from_date,$todate,$route_id='')
	{		
		$this->db->select_sum('sp.sale_pymnt_amount');
		$this->db->from('sale_payments sp');
		$this->db->join('sales s', 'sp.sale_id=s.sale_id', 'left');
		$this->db->join('customer c', 'c.cus_id=s.customer_id', 'left');
		if($from_date){
		$this->db->where('sp.sale_pymnt_date_time >=',$from_date);	
		}
		if($todate)
		{
		$this->db->where('sp.sale_pymnt_date_time <=',$todate);	
		}	
		if($route_id)
		{
		$this->db->where('c.route_id',$route_id);	
		}
		$this->db->where('sp.sale_payment_type',"sale");
		$this->db->where('sp.sale_pymnt_paying_by',"Cheque");
        $query = $this->db->get();
        return $query->row_array();		
	}
	
	function get_cus_order_list($from_date,$todate,$route_id='',$cus_id='')
	{		
		$this->db->select('cm.*');
		$this->db->select('c.cus_name,c.cus_code');
		$this->db->select('mt.invoice_type_name');
        $this->db->from('customer_order_master cm');
		$this->db->join('customer c', 'c.cus_id=cm.customer_id', 'left');
		$this->db->join('master_invoice_type mt', 'mt.invoice_type_id=cm.invoice_type', 'left');
		if($from_date){
		$this->db->where('cm.customer_order_date >=',$from_date);	
		}
		if($todate)
		{
		$this->db->where('cm.customer_order_date <=',$todate);	
		}	
		if($route_id&&!$cus_id)
		{
		$this->db->where('c.route_id',$route_id);	
		}
		if($cus_id)
		{
		$this->db->where('cm.customer_id',$route_id);	
		}
		//$this->db->where('cm.sale_payment_type',"sale");
        $query = $this->db->get();
        return $query->result_array();		
	}
	
}