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

class Mreport extends CI_Model {



    /*
    author: arindam biswas
    purpose: Get Levy Report
    date: 11/10/2019
    */
    public function get_levy_report($start_date=null,$end_date=null,$driver_id=null,$company_id=null,$levy_settled_flag=null){
           
        $this->db->select('p.driver_id,p.levy_settled_flag,group_concat(p.payin_id) as payin_id,sum(ifnull(p.levy,0)) as levy,up.first_name,up.middle_name,up.last_name,mu.dc_no,up.abn,(SELECT value FROM lookup_table WHERE title="LEVY_GST") as levy_gst,ifnull(SUM(p.extra_end),0) as extra_end,ifnull(SUM(p.extra_start),0) as extra_start,ifnull(SUM(p.no_of_hiring_end),0) as no_of_hiring_end,ifnull(SUM(p.no_of_hiring_start),0) as no_of_hiring_start');
        $this->db->from('payin p');
        $this->db->join('master_user mu','mu.user_id=p.driver_id','inner');
        $this->db->join('user_profile up','up.user_id=p.driver_id','inner');
        $this->db->join('dr_company_agreement_mapping dcam','dcam.driver_id=p.driver_id','left');
        
        $this->db->where('p.levy_settled_flag',$levy_settled_flag);
        $this->db->where('p.levy > ',0);
		if($driver_id){
            $this->db->where('p.driver_id',$driver_id);
        }
        if($company_id){
            $this->db->where('dcam.company_id',$company_id);
        }
        if(!empty($start_date) && !empty($end_date)){
            $this->db->where('str_to_date(date_format(p.created_ts,"%d/%m/%Y"),"%d/%m/%Y") >=',$start_date);
            $this->db->where('str_to_date(date_format(p.created_ts,"%d/%m/%Y"),"%d/%m/%Y") <=',$end_date);
        }
        $this->db->group_by('p.driver_id');
        $query=$this->db->get();
        //echo '<pre>';print_r($this->db->last_query());die();
		return $query->result_array();
    }

    public function get_levy_report_details($start_date=null,$end_date=null,$driver_id=null,$company_id=null,$levy_settled_flag=null){
           
        $this->db->select('p.payin_id,p.levy,ifnull(p.extra_end,0) as extra_end,ifnull(p.extra_start,0) as extra_start,ifnull(p.no_of_hiring_end,0) as no_of_hiring_end,ifnull(p.no_of_hiring_start,0) as no_of_hiring_start,CONCAT(up.first_name," ",up.middle_name," ",up.last_name) as full_name,dsm.shift_name,mu.dc_no,up.abn,up.mobile,(SELECT value FROM lookup_table WHERE title="LEVY_GST") as levy_gst,mc.registration_no,dsd.shift_no,date_format(p.created_ts,"%d/%m/%Y") as created_ts,CONCAT(up.street_no,",",up.street_name,",",up.suburb,",",up.state,",",up.pin) as address');
        $this->db->from('payin p');
        $this->db->join('driver_shift_details dsd','dsd.shift_id=p.shift_id','inner');
        $this->db->join('roster_driver_maping rdm','rdm.roster_driver_id=dsd.roster_driver_id','inner');
        $this->db->join('roster_shift_maping dsm','dsm.roster_shift_id=rdm.roster_shift_id','inner');
        $this->db->join('roster r','r.roster_id=rdm.roster_id','inner');
        $this->db->join('master_car mc','mc.car_id=r.car_id','inner');
        $this->db->join('master_user mu','mu.user_id=p.driver_id','inner');
        $this->db->join('user_profile up','up.user_id=p.driver_id','inner');
        $this->db->join('dr_company_agreement_mapping dcam','dcam.driver_id=p.driver_id','left');
        $this->db->where('p.levy_settled_flag',$levy_settled_flag);
		$this->db->where('p.driver_id',$driver_id);
        
        if($company_id){
            $this->db->where('dcam.company_id',$company_id);
        }
        if(!empty($start_date) && !empty($end_date)){
            $this->db->where('str_to_date(date_format(p.created_ts,"%d/%m/%Y"),"%d/%m/%Y") >=',$start_date);
            $this->db->where('str_to_date(date_format(p.created_ts,"%d/%m/%Y"),"%d/%m/%Y") <=',$end_date);
        }
        $this->db->order_by('p.created_ts');
        $query=$this->db->get();
        //echo '<pre>';print_r($this->db->last_query());die();
		return $query->result_array();
    }
    

    public function get_meter_reading_report($start_date=null,$end_date=null,$car_id =null){
           
        $this->db->select('p.*,dsm.shift_name,mc.registration_no,date_format(p.created_ts,"%d/%m/%Y") as payin_date');
        $this->db->from('payin p');
        $this->db->join('driver_shift_details dsd','dsd.shift_id=p.shift_id','inner');
        $this->db->join('roster_driver_maping rdm','rdm.roster_driver_id=dsd.roster_driver_id','inner');
        $this->db->join('roster_shift_maping dsm','dsm.roster_shift_id=rdm.roster_shift_id','inner');
        $this->db->join('roster r','r.roster_id=rdm.roster_id','inner');
        $this->db->join('master_car mc','mc.car_id=r.car_id','inner');
        
        if($car_id){
            $this->db->where('r.car_id',$car_id);
        }
        if(!empty($start_date) && !empty($end_date)){
            $this->db->where('str_to_date(date_format(p.created_ts,"%d/%m/%Y"),"%d/%m/%Y") >=',$start_date);
            $this->db->where('str_to_date(date_format(p.created_ts,"%d/%m/%Y"),"%d/%m/%Y") <=',$end_date);
        }
        $query=$this->db->get();
        //echo '<pre>';print_r($this->db->last_query());die();
		return $query->result_array();
    }
    

    /*
    author: Arindam Biswas
    purpose: Get Active roles
    date: 15/10/2019
    */
    public function get_driver_list()
    {
        $result = array();

        $this->db->select('mu.*,up.*');
        $this->db->from('master_user mu');                
        $this->db->join('user_profile up','up.user_id=mu.user_id','inner');           
        $this->db->where('mu.is_active',1);                
        $query = $this->db->get();
        $result = $query->result_array();
        return $result;
    }  
    
    public function get_driver_against_company($company_id){
        $result = array();

        $this->db->select('mu.*,up.*');
        $this->db->from('dr_company_agreement_mapping dcam');                
        $this->db->join('master_user mu','mu.user_id=dcam.driver_id','inner');                
        $this->db->join('user_profile up','up.user_id=dcam.driver_id','inner');           
        $this->db->where('dcam.company_id',$company_id); 
        $this->db->where('mu.is_active',1);                  
        $query = $this->db->get();
        $result = $query->result_array();
        return $result;
    }

    /*
    author: Arindam Biswas
    purpose: Get Car List
    date: 16/10/2019
    */

    
    public function get_car_against_company($company_id){
        $result = array();

        $this->db->select('mc.*');
        $this->db->from('master_car mc');                  
        $this->db->where('mc.company_id',$company_id); 
        $this->db->where('mc.status',1);                  
        $query = $this->db->get();
        $result = $query->result_array();
        return $result;
    }


    public function get_income_report($start_date=null,$end_date=null,$car_id =null,$company_id =null){
           
        $this->db->select('p.*,dsm.shift_name,mc.registration_no,date_format(p.created_ts,"%d/%m/%Y") as payin_date,
        (SELECT ifnull(SUM(pe.value),"0")FROM payin_expenses pe WHERE pe.payin_id=p.payin_id AND pe.expense_id=1)  as expense_total_fuel,
        (SELECT ifnull(SUM(pe.value),"0")FROM payin_expenses pe WHERE pe.payin_id=p.payin_id AND pe.expense_id=3)  as expense_total_other,
        mcn.commission_id, mcn.commission_name, ifnull(mcn.driver_share,0) as driver_commission_share,ifnull(mcn.owner_share,0) as owner_commission_share,
        (SELECT gtn_share * p.no_of_whl  FROM master_lifting_fee ORDER BY lifting_fee_id DESC LIMIT 1) as total_lifting_fees');
        $this->db->from('payin p');
        $this->db->join('driver_shift_details dsd','dsd.shift_id=p.shift_id','inner');        
        $this->db->join('roster_driver_maping rdm','rdm.roster_driver_id=dsd.roster_driver_id','inner');
        $this->db->join('roster_shift_maping dsm','dsm.roster_shift_id=rdm.roster_shift_id','inner');
        $this->db->join('roster r','r.roster_id=rdm.roster_id','inner');
        $this->db->join('master_car mc','mc.car_id=r.car_id','inner');
        $this->db->join('master_company mcm','mcm.company_id=mc.company_id','inner');
        $this->db->join('user_profile up','up.user_id=p.driver_id','inner');
        $this->db->join('master_commission mcn','mcn.commission_id=up.dr_commission_id','left');
        
        
        if($car_id){
            $this->db->where('r.car_id',$car_id);
        }
        if($company_id){
            $this->db->where('mc.company_id',$company_id);
        }
        if(!empty($start_date) && !empty($end_date)){
            $this->db->where('str_to_date(date_format(p.created_ts,"%d/%m/%Y"),"%d/%m/%Y") >=',$start_date);
            $this->db->where('str_to_date(date_format(p.created_ts,"%d/%m/%Y"),"%d/%m/%Y") <=',$end_date);
        }
        $query=$this->db->get();
        //echo '<pre>';print_r($this->db->last_query());die();
		return $query->result_array();
    }

    public function get_driver_income_report($start_date=null,$end_date=null,$driver_id){
        //echo $driver_id;die;
           
        $this->db->select('ds.*,p.levy,p.metered_fares,CONCAT(up.first_name," ",up.middle_name," ",up.last_name) as full_name,mu.dc_no,up.abn,up.mobile,dsm.shift_name,mc.registration_no,date_format(p.created_ts,"%d/%m/%Y") as payin_date,CONCAT(up.street_no,",",up.street_name,",",up.suburb,",",up.state,",",up.pin) as address,
        mcn.commission_id, mcn.commission_name, ifnull(mcn.driver_share,0) as driver_commission_share,ifnull(mcn.owner_share,0) as owner_commission_share,
        (SELECT gtn_share * p.no_of_whl  FROM master_lifting_fee ORDER BY lifting_fee_id DESC LIMIT 1) as total_lifting_fees');
        $this->db->from('driver_settlement ds');
        $this->db->join('payin p','p.payin_id=ds.payin_id','inner');
        $this->db->join('driver_shift_details dsd','dsd.shift_id=p.shift_id','inner');
        $this->db->join('roster_driver_maping rdm','rdm.roster_driver_id=dsd.roster_driver_id','inner');
        $this->db->join('roster_shift_maping dsm','dsm.roster_shift_id=rdm.roster_shift_id','inner');
        $this->db->join('roster r','r.roster_id=rdm.roster_id','inner');
        $this->db->join('master_car mc','mc.car_id=r.car_id','inner');
        $this->db->join('master_user mu','mu.user_id=p.driver_id','inner');
        $this->db->join('user_profile up','up.user_id=p.driver_id','inner');
        $this->db->join('master_commission mcn','mcn.commission_id=up.dr_commission_id','left');
        
        if($driver_id){
            $this->db->where('p.driver_id',$driver_id);
        }
       
        if(!empty($start_date) && !empty($end_date)){
            $this->db->where('str_to_date(date_format(p.created_ts,"%d/%m/%Y"),"%d/%m/%Y") >=',$start_date);
            $this->db->where('str_to_date(date_format(p.created_ts,"%d/%m/%Y"),"%d/%m/%Y") <=',$end_date);
        }
        $query=$this->db->get();
        //echo '<pre>';print_r($this->db->last_query());die();
		return $query->result_array();
    }

    public function get_service_notification_report(){
        $this->db->select('sn.*,mc.registration_no,date_format(p.created_ts,"%d/%m/%Y") as payin_date,p.total_km_end,up.first_name,up.middle_name,up.last_name,mu.dc_no,up.mobile');
        $this->db->from('service_notification sn');
        $this->db->join('payin p','sn.payin_id=p.payin_id','inner');
        $this->db->join('master_car mc','mc.car_id=sn.car_id','inner');
        $this->db->join('user_profile up','up.user_id=p.driver_id','inner');
        $this->db->join('master_user mu','mu.user_id=p.driver_id','inner');
        $query=$this->db->get();
        //echo '<pre>';print_r($this->db->last_query());die();
		return $query->result_array();

    }

    public function get_settlement_due_report(){
        $query="SELECT GROUP_CONCAT(p.payin_id) as payin_id,GROUP_CONCAT(p.shift_id) as shift_id,GROUP_CONCAT(dsd.shift_no) as shift_no,r.car_id,mc.registration_no,CONCAT(up.first_name,'',up.middle_name,'',up.last_name) as full_name,mu.dc_no,up.mobile,get_settlement_due_status(GROUP_CONCAT(p.payin_id)) as weekly_settlement_status
            FROM payin p
            INNER JOIN driver_shift_details dsd ON dsd.shift_id=p.shift_id
            INNER JOIN roster_driver_maping rdm ON rdm.roster_driver_id=dsd.roster_driver_id
            INNER JOIN master_user mu ON mu.user_id=rdm.driver_id
            INNER JOIN user_profile up ON up.user_id=rdm.driver_id
            INNER JOIN roster_shift_maping rsm ON rsm.roster_shift_id=rdm.roster_shift_id
            INNER JOIN roster r ON r.roster_id=rdm.roster_id
            INNER JOIN master_car mc ON mc.car_id=r.car_id
            GROUP BY rdm.driver_id,r.car_id";
        
        //echo $query;die;
        $run_query=$this->db->query($query);
        $result=$run_query->result_array();
        return $result;



    }
    
    public function get_customer_statement_report($start_date=null,$end_date=null,$customer_id=null){
        $query="SELECT dt.*,date_format(dt.transaction_date,'%d/%m/%Y') as transaction_date,
        CASE WHEN dt.transaction_type = 1 THEN 
            CASE WHEN dt.customer_id <> 0 THEN CONCAT_WS(' ','Cash from',mc.first_name,mc.middle_name,mc.last_name) ELSE CONCAT('Payment Received from driver(',mu.dc_no,')')  END 
        ELSE
            CASE WHEN dt.customer_id <> 0 THEN CONCAT_WS (' ','Cash for',mc.first_name,mc.middle_name,mc.last_name) ELSE CONCAT('Payment To Driver (',mu.dc_no,')') END
        END as transaction_desc
        FROM daily_transactions dt 
        LEFT JOIN master_customer mc ON mc.customer_id = dt.customer_id
        LEFT JOIN payin p ON p.payin_id=dt.payin_id
        LEFT JOIN master_user mu ON mu.user_id=p.driver_id
        WHERE dt.customer_id <> 0";

        if($customer_id){
            $query .=" AND dt.customer_id='".$customer_id."'";
        }
        if(!empty($start_date) && !empty($end_date)){
            $query .=" AND dt.transaction_date BETWEEN '".$start_date."' AND '".$end_date."'";
        }
        
        $query .=" ORDER BY dt.transaction_date DESC";

        $run_query  = $this->db->query($query);
        //echo $this->db->last_query(); die();
        $result = $run_query->result_array();
        return $result ;

    }

}