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

class Report_Oparational_Model extends CI_Model {
  
  private $tableName = '';
  
  function __construct() 
  {
    /* Call the Model constructor */
    parent::__construct();
  }
  
  
  //2022-01-08 implement m.m.sachith eranga
  function get_sale_total($rep_id='',$from_date='',$to_date='',$route_id='',$customer_id='',$type='')
  {
        $this->db->select_sum('s.cost_total');
        $this->db->select_sum('s.sale_total');
        $this->db->from('sales s');
        if($route_id){
         $this->db->join('customer c','c.cus_id=s.customer_id','inner');  
         $this->db->join('routes r','r.route_id=c.route_id','inner');
         $this->db->where('r.area_id',$route_id); 
        }
        if($rep_id){
         $this->db->where("s.sales_rep_id",$rep_id);   
        }
        if($customer_id){
         $this->db->where("s.customer_id",$customer_id);   
        }
        if($type==1){
            $this->db->where("s.direct_sale",1); 
        }else{
             $this->db->where("s.direct_sale",0); 
        }
        if($from_date){
		   $this->db->where("date(s.sale_datetime)>=",$from_date);
        }
         if($to_date){
		   $this->db->where("date(s.sale_datetime)<=",$to_date);
        }
        $query = $this->db->get();
        $result= $query->row_array();
        if(isset($result['sale_total'])){
            $return_data['sale_total']=$result['sale_total'];
           
        }else{
            $return_data['sale_total']=0;
        }
        
        if(isset($result['cost_total'])){
            $return_data['cost_total']=$result['cost_total'];
           
        }else{
            $return_data['cost_total']=0;
        }
        return $return_data;
  }
  
  function getMarketingReturnTotal($rep_id='',$date='',$todate='',$route_id='',$customer_id='')
  {
        $this->db->select_sum('s.total_return_value');
        $this->db->from('delivery_return_master s');
         if($route_id){
         $this->db->join('customer c','c.cus_id=s.customer_id','inner');  
         $this->db->join('routes r','r.route_id=c.route_id','inner');
         $this->db->where('r.area_id',$route_id); 
        }
        if($rep_id){
         $this->db->where("s.sales_rep_id",$rep_id);   
        }
        if($customer_id){
         $this->db->where("s.customer_id",$customer_id);   
        }
        if($date){
		   $this->db->where("date(s.mobile_date_time)>=",$date);
        }
         if($todate){
		   $this->db->where("date(s.mobile_date_time)<=",$todate);
        }
        $query = $this->db->get();
        $result= $query->row_array();
        if(isset($result['total_return_value'])){
            return $result['total_return_value'];
           
        }else{
           return 0;
        }
  }
  
  function getInvoiceReturnTotal($rep_id='',$date='',$todate='',$route_id='',$customer_id='')
  {
        $this->db->select_sum('s.sl_rtn_total');
        $this->db->from('sales_return s');
         if($route_id){
         $this->db->join('customer c','c.cus_id=s.customer_id','inner');  
         $this->db->join('routes r','r.route_id=c.route_id','inner');
         $this->db->where('r.area_id',$route_id); 
        }
        if($rep_id){
         $this->db->where("s.user_id",$rep_id);   
        }
        if($customer_id){
         $this->db->where("s.customer_id",$customer_id);   
        }
        if($date){
		   $this->db->where("date(s.sl_rtn_datetime)>=",$date);
        }
         if($todate){
		   $this->db->where("date(s.sl_rtn_datetime)<=",$todate);
        }
        $query = $this->db->get();
        $result= $query->row_array();
        if(isset($result['sl_rtn_total'])){
            return $result['sl_rtn_total'];
           
        }else{
           return 0;
        }
  }
  function getDiscountTotal($rep_id='',$date='',$todate='',$route_id='',$customer_id='',$type='')
  {
        $this->db->select_sum('s.total_added_discount_value');
        $this->db->select_sum('s.sale_inv_discount_amount');
        $this->db->from('sales s');
         if($route_id){
         $this->db->join('customer c','c.cus_id=s.customer_id','inner');  
         $this->db->join('routes r','r.route_id=c.route_id','inner');
         $this->db->where('r.area_id',$route_id); 
        }
        if($rep_id){
         $this->db->where("s.sales_rep_id",$rep_id);   
        }
         if($customer_id){
         $this->db->where("s.customer_id",$customer_id);   
        }
        if($type==1){
            $this->db->where("s.direct_sale",1); 
        }else{
             $this->db->where("s.direct_sale",0); 
        }
        if($date){
		   $this->db->where("date(s.sale_datetime)>=",$date);
        }
         if($todate){
		   $this->db->where("date(s.sale_datetime)<=",$todate);
        }
        $query = $this->db->get();
        $result= $query->row_array();
        if(isset($result['total_added_discount_value'])){
            $return_data['total_added_discount_value']=$result['total_added_discount_value'];
        }else{
            $return_data['total_added_discount_value']=0;
        }
        if(isset($result['sale_inv_discount_amount'])){
            $return_data['sale_inv_discount_amount']=$result['sale_inv_discount_amount'];
        }else{
            $return_data['sale_inv_discount_amount']=0;
        }
        return $return_data;
  }
   function getInvoiceList($rep_id='',$date='',$todate='',$route_id='',$customer_id='',$type='')
  {
        $this->db->select('s.*');
        $this->db->select('c.cus_name,c.cus_code');
        $this->db->select('m.invoice_type_name');
        $this->db->from('sales s');
        $this->db->join('master_invoice_type m', 'm.invoice_type_id=s.invoice_type', 'inner');
        $this->db->join('customer c', 'c.cus_id=s.customer_id', 'inner');
        if($route_id){
        $this->db->join('routes r','r.route_id=c.route_id','inner');
         $this->db->where('r.area_id',$route_id); 
        }
        if($rep_id){
         $this->db->where("s.sales_rep_id",$rep_id);   
        }
        if($customer_id){
         $this->db->where("s.customer_id",$customer_id);   
        }
        if($type==1){
            $this->db->where("s.direct_sale",1); 
        }else{
             $this->db->where("s.direct_sale",0); 
        }
        if($date){
		   $this->db->where("date(s.sale_datetime)>=",$date);
        }
        if($todate){
		   $this->db->where("date(s.sale_datetime)<=",$todate);
        }
        $this->db->limit(2000);
        $query = $this->db->get();
        return  $query->result_array();
       
  }
   function getInvoiceAllItemList($rep_id='',$date='',$todate='',$route_id='',$customer_id='',$type='')
  {
        $this->db->select_sum('si.quantity');
        $this->db->select_sum('si.net_total');
        $this->db->select_sum('si.total_item_cost');
        $this->db->select_sum('si.discount_val');
        $this->db->select('si.product_id');
        $this->db->select('p.product_code,p.product_name');
        $this->db->from('sale_items si');
        $this->db->join('product p','si.product_id=p.product_id', 'inner');
        $this->db->join('sales s','si.sale_id=s.sale_id', 'inner');
         if($route_id){
         $this->db->join('customer c','c.cus_id=s.customer_id','inner');  
         $this->db->join('routes r','r.route_id=c.route_id','inner');
         $this->db->where('r.area_id',$route_id); 
        }
         if($customer_id){
         $this->db->where("s.customer_id",$customer_id);   
        }
        if($rep_id){
         $this->db->where("s.sales_rep_id",$rep_id);   
        }
        if($date){
		   $this->db->where("date(s.sale_datetime)>=",$date);
        }
         if($todate){
		   $this->db->where("date(s.sale_datetime)<=",$todate);
        }
       // $this->db->where("s.direct_sale",1);
        $this->db->group_by('si.product_id');
        $query = $this->db->get();
        return  $query->result_array();
  }

  
  function getMarketReturnItemList($product_id='',$rep_id='',$date='',$todate='',$route_id='',$customer_id='',$type='')
  {
        $this->db->select_sum('si.product_qty');
        $this->db->select_sum('si.product_total_sale_value');
        $this->db->from('delivery_return_items si');
        $this->db->join('delivery_return_master s','si.delivery_return_id=s.delivery_return_id', 'left');
         if($route_id){
         $this->db->join('customer c','c.cus_id=s.customer_id','inner');  
         $this->db->join('routes r','r.route_id=c.route_id','inner');
         $this->db->where('r.area_id',$route_id); 
        }
        if($rep_id){
         $this->db->where("s.sales_rep_id",$rep_id);   
        }
         if($customer_id){
         $this->db->where("s.customer_id",$customer_id);   
        }
         if($product_id){
         $this->db->where("si.product_id",$product_id);   
        }
        if($date){
		   $this->db->where("date(s.system_date_time)>=",$date);
        }
        if($todate){
		   $this->db->where("date(s.system_date_time)<=",$todate);
        }
        $query = $this->db->get();
        return  $query->row_array();
        
  }
  
   function getMarketReturnItemListll($product_id='',$rep_id='',$date='',$todate='',$type='')
  {
        $query= $this->db->select('t1.*')
            ->from('delivery_return_items AS t1')
           
            ->where('t1.product_id NOT IN (select product_id from sale_items)',NULL,FALSE)->get();
            return  $query->result_array();
  }
  

  
  

	
		
}