<?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_customers_by_area_id($route_id,$start,$length,$search) {
		$this->db->select('customer.*');	
		$this->db->join('routes r', 'r.route_id=customer.route_id', 'inner');	
		//$this->db->where("customer.cus_status",1);
		if($route_id){
			$this->db->where("r.area_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);
		$this->db->where("s.payment_status",0);
		$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);
		$this->db->where("s.payment_status",0);
		$this->db->where("sp.sale_pymnt_paying_by != 'Cheque_Return'");
		$this->db->where("sp.sale_payment_type","sale");
		$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);
		$this->db->where("sp.sale_pymnt_paying_by != 'Cheque_Return'");
		$this->db->where("sp.sale_payment_type","sale");
		$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);
		$this->db->where("s.payment_status",0);
		$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');
		$this->db->where("s.payment_status",0);
		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_sale_returns_total_by_route_id($route_id)
	{
		$this->db->select_sum('sr.sl_rtn_total');
		$this->db->from('sales_return sr');
		$this->db->join('sales s', 's.sale_id=sr.sale_id', 'inner');
		$this->db->join('customer c', 'c.cus_id=s.customer_id', 'left');
		$this->db->where("s.payment_status",0);
		if($route_id){$this->db->where("c.route_id",$route_id);}		
		$query = $this->db->get();
		$data = $query->row_array();		
		if($data['sl_rtn_total']){
			return $data['sl_rtn_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');
		$this->db->where("s.payment_status",0);
		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='',$route_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);	
		}
		if($route_id)
		{
		$this->db->where('c.route_id',$route_id);	
		}
        $query = $this->db->get();
        return $query->result_array();		
	}
	
	function get_invoice_summary($from_date,$todate,$rep_id='',$route_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);	
		}
		if($route_id)
		{
		$this->db->where('c.route_id',$route_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='',$status_id='',$type_id='')
	{		
		$this->db->select('cm.*');
		$this->db->select('ms1.ap_status as approval_status');
		$this->db->select('ms2.ap_status as credit_approval_status');
		$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');
		$this->db->join('master_approval_status ms1', 'ms1.app_value=cm.order_approval_status', 'left');
		$this->db->join('master_approval_status ms2', 'ms2.app_value=cm.credit_approval_status', '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',$cus_id);	
		}
		if($status_id=="pending")
		{
		$this->db->where('cm.status',0);	
		}
		if($status_id=="completed")
		{
		$this->db->where('cm.status',1);	
		}
		if($type_id)
		{
		$this->db->where('cm.invoice_type',$type_id);	
		}
		$this->db->order_by('cm.cus_order_ref_no','desc');
		//$this->db->where('cm.sale_payment_type',"sale");
        $query = $this->db->get();
        return $query->result_array();		
	}
	
	function get_cus_order_summary($from_date,$todate,$route_id='',$cus_id='',$status_id='',$type_id='')
	{	
		$this->db->select_sum('cm.net_amount');
        $this->db->from('customer_order_master cm');
		$this->db->join('customer c', 'c.cus_id=cm.customer_id', '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',$cus_id);	
		}
		if($status_id=="pending")
		{
		$this->db->where('cm.status',0);	
		}
		if($status_id=="completed")
		{
		$this->db->where('cm.status',1);	
		}
		if($type_id)
		{
		$this->db->where('cm.invoice_type',$type_id);	
		}
		$this->db->order_by('cm.cus_order_ref_no','desc');
		//$this->db->where('cm.sale_payment_type',"sale");
        $query = $this->db->get();
        return $query->row_array();		
	}
	
	function get_expensess_summary($from_date,$todate)
	{		
		$this->db->select_sum('at.acctrnss_amount');
		$this->db->from('acc_transactions at');
		if($from_date){
		$this->db->where('at.acctrnss_date >=',$from_date);	
		}
		if($todate)
		{
		$this->db->where('at.acctrnss_date <=',$todate);	
		}	
        $query = $this->db->get();
        return $query->row_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->select('fixed_assets_type.*');
		//$this->db->select('fixed_assets_master.*');
		$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');
	//	$this->db->join('fixed_assets_master','fixed_assets_master.fam_id=fixed_assets_type.fam_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_cus_due_list($from_date,$todate,$route_id='',$cus_id='',$status_id='',$type_id='')
	{
		$due_date=date('Y-m-d');
		$this->db->select('s.*');
		$this->db->select('c.cus_name,c.cus_code');
		$this->db->select('mt.invoice_type_name');
        $this->db->from('sales s');
		$this->db->join('customer c', 'c.cus_id=s.customer_id', 'left');
		$this->db->join('master_invoice_type mt', 'mt.invoice_type_id=s.invoice_type', 'left');		
		$this->db->where('s.payment_status',0);
		$this->db->where('s.due_date <',$due_date);	
		if($from_date){
		$this->db->where('s.sale_datetime >=',$from_date);	
		}
		if($todate)
		{
		$this->db->where('s.sale_datetime <=',$todate);	
		}	
		if($route_id&&!$cus_id)
		{
		$this->db->where('c.route_id',$route_id);	
		}
		if($cus_id)
		{
		$this->db->where('s.customer_id',$cus_id);	
		}
		if($status_id=="pending")
		{
		$this->db->where('cm.status',0);	
		}
		if($status_id=="completed")
		{
		$this->db->where('cm.status',1);	
		}
		if($type_id)
		{
		$this->db->where('s.invoice_type',$type_id);	
		}
		$this->db->order_by('s.due_date','desc');
		//$this->db->where('cm.sale_payment_type',"sale");
        $query = $this->db->get();
        return $query->result_array();		
	}



	function get_cheque_return_balance_by_customer_id($customer_id)
	{		
		$this->db->select_sum('rp.total_return_amount');
		$this->db->from('return_payments rp');
		$this->db->join('sale_multiple_payment sm', 'sm.smp_id=rp.payment_id', 'left');	
		$this->db->where('sm.customer_id',$customer_id);			
        $query = $this->db->get();
		$result= $query->row_array();	
		if($result['total_return_amount']){$value=$result['total_return_amount'];}else{$value=0;}
		return $value;			
	}

	public function get_uncomplte_multiple_payment($cus_id)
	{		
		$this->db->select('s.*');
		$this->db->from('sale_multiple_payment s');
		$this->db->where("s.payment_settlement_status", 0);
		$this->db->where("s.customer_id", $cus_id);
		$query = $this->db->get();
		return $query->result_array(); 	
	}

	function get_total_sale_paid_by_smp_id($id)
    {
        $this->db->select_sum('sp.sale_pymnt_amount');
        $this->db->from('sale_payments sp');
        $this->db->where('sp.smp_id', $id);
        $query = $this->db->get();
        $result= $query->row_array();

        if($result['sale_pymnt_amount']){
            return $result['sale_pymnt_amount'];
        }else{
            return 0;
        }
	}
	
	function get_total_cheque_return_paid_by_smp_id($id)
    {
        $this->db->select_sum('sp.paid_amount');
        $this->db->from('cheque_return_payment sp');
        $this->db->where('sp.smp_id', $id);
        $query = $this->db->get();
        $result= $query->row_array();
        if($result['paid_amount']){
            return $result['paid_amount'];
        }else{
            return 0;
        }
	}

	public function get_salse_rep_users()
	{		
		$this->db->select('s.user_id,s.user_first_name,s.user_last_name');
		$this->db->from('user s');
		$this->db->where("s.group_id", 4);
		$this->db->where("s.user_status", 1);
		$query = $this->db->get();
		return $query->result_array(); 	
	}

	function get_sale_total_by_sale_rep($id,$fromdate,$todate)
	{
		$this->db->select_sum('s.sale_total');
        $this->db->from('sales s');		
		$this->db->where("s.sales_rep_id",$id);
		//$this->db->where("s.sale_status",1);	
		$this->db->where('s.sale_datetime >=',$fromdate);
		$this->db->where('s.sale_datetime <=',$todate);	
		$query = $this->db->get();
		$data = $query->row_array();  
		//print_r($this->db->last_query());
		if($data['sale_total']){
			return $data['sale_total'];
		}
		else{
			return 0;
		}
	}

	function get_credit_sale_total_by_sale_rep($id,$fromdate,$todate)
	{
		$this->db->select_sum('s.sale_total');
        $this->db->from('sales s');		
		$this->db->where("s.sales_rep_id",$id);
		$this->db->where("s.sale_status",1);	
		$this->db->where("s.invoice_type",2);		
		$this->db->where('s.sale_datetime >=',$fromdate);
		$this->db->where('s.sale_datetime <=',$todate);	
		$query = $this->db->get();
		$data = $query->row_array();  
		if($data['sale_total']){
			return $data['sale_total'];
		}
		else{
			return 0;
		}
	}

	function get_cash_sale_return_total_by_sale_rep($id,$fromdate,$todate)
	{
		$this->db->select_sum('sr.sl_rtn_total');
		$this->db->from('sales_return sr');	
		$this->db->join('sales s', 's.sale_id=sr.sale_id', 'left');	
		$this->db->where("s.sales_rep_id",$id);
		$this->db->where("s.sale_status",1);
		$this->db->where("s.invoice_type",1);	
		$this->db->where('sr.sl_rtn_datetime >=',$fromdate);
		$this->db->where('sr.sl_rtn_datetime <=',$todate);	
		$query = $this->db->get();
		$data = $query->row_array();  
		//print_r($this->db->last_query());
		if($data['sl_rtn_total']){
			return $data['sl_rtn_total'];
		}
		else{
			return 0;
		}
	}

	function get_credit_sale_return_total_by_sale_rep($id,$fromdate,$todate)
	{
		$this->db->select_sum('sr.sl_rtn_total');
		$this->db->from('sales_return sr');	
		$this->db->join('sales s', 's.sale_id=sr.sale_id', 'left');	
		$this->db->where("s.sales_rep_id",$id);
		$this->db->where("s.sale_status",1);
		$this->db->where("s.invoice_type",2);	
		$this->db->where('sr.sl_rtn_datetime >=',$fromdate);
		$this->db->where('sr.sl_rtn_datetime <=',$todate);	
		$query = $this->db->get();
		$data = $query->row_array();  
		//print_r($this->db->last_query());
		if($data['sl_rtn_total']){
			return $data['sl_rtn_total'];
		}
		else{
			return 0;
		}
	}

	function get_sale_list_by_sale_rep($id,$fromdate,$todate)
	{
		$this->db->select('s.*');
		$this->db->from('sales s');		
		$this->db->where("s.sales_rep_id",$id);
		//$this->db->where("s.sale_status",1);	
		//$this->db->where("s.payment_status",1);
		$this->db->where('s.sale_datetime >=',$fromdate);
		$this->db->where('s.sale_datetime <=',$todate);	
		$this->db->order_by('s.sale_datetime','asc');	
		$query = $this->db->get();
		return $query->result_array();  			
	}
	
	
	function get_sale_return_list_by_sale_rep($id,$fromdate,$todate)
	{
		$this->db->select('s.*');
		$this->db->select('sr.*');
		$this->db->from('sales_return sr');	
		$this->db->join('sales s', 's.sale_id=sr.sale_id', 'left');	
		$this->db->where("s.sales_rep_id",$id);
		$this->db->where('sr.sl_rtn_datetime >=',$fromdate);
		$this->db->where('sr.sl_rtn_datetime <=',$todate);	
		$this->db->order_by('sr.sl_rtn_datetime','asc');	
		$query = $this->db->get();
		return $query->result_array();  			
	}

	function get_sales_last_payment($sale_id){
		$this->db->select('sp.sale_pymnt_date_time');
        $this->db->from('sale_payments sp');		
		$this->db->where("sp.sale_id",$sale_id);	
		$this->db->where("sp.sale_payment_type",'sale');
		$this->db->order_by('sp.sale_pymnt_date_time','desc');	
		$query = $this->db->get();
		$data = $query->row_array();  
		if(isset($data['sale_pymnt_date_time'])){
			return $data['sale_pymnt_date_time'];
		}
		else{
			return 0;
		}
	}
	

	function get_cheque_return_deduction_total_by_sale_rep($id,$fromdate,$todate)
	{
		$this->db->select_sum('s.crd_amount');
        $this->db->from(' cheque_return_deduction s');		
		$this->db->where("s.sale_rep_id",$id);		
		$this->db->where('s.crd_date >=',$fromdate);
		$this->db->where('s.crd_date <=',$todate);	
		$query = $this->db->get();
		$data = $query->row_array();  
		//print_r($this->db->last_query());
		if($data['crd_amount']){
			return $data['crd_amount'];
		}
		else{
			return 0;
		}
	}


	function get_all_cheque_return_list_by_customer_id($customer_id)
	{		
		$this->db->select('s.*');
		$this->db->select('sp.*');
		$this->db->from('return_payments s');		
		$this->db->join('sale_multiple_payment sp', 's.payment_id=sp.smp_id', 'left');
		$this->db->where("s.return_pay_status",1);	
		$this->db->where("sp.customer_id",$customer_id);
		$this->db->group_by('s.ret_pay_id');
		$query = $this->db->get();
		return $query->result_array();  
		//print_r($this->db->last_query());
		
	}

	function get_cheque_reurn_paid_amount($id)
    {
        $this->db->select_sum('sp.paid_amount');
        $this->db->from('cheque_return_payment sp');
        $this->db->where('sp.ch_return_id', $id);
        $query = $this->db->get();
        $result= $query->row_array();
        if($result['paid_amount']){
            return $result['paid_amount'];
        }else{
            return 0;
        }
	}
	
	function get_special_commission_produc_list($sale_id)
	{
		$this->db->select('si.net_total,si.product_id');
		$this->db->select('p.commission_cash_within,p.commission_cash_after,p.commission_credit_within,p.commission_credit_after');
        $this->db->from('sale_items si');
		$this->db->join('product p', 'p.product_id=si.product_id', 'inner');
		$this->db->where("si.sale_id",$sale_id);
		$this->db->where("p.is_special_rep_commisson",1);
		$query = $this->db->get();
		return $query->result_array();  
	}


	
	
	
}