<?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(ds.shift_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(ds.extra_end),0) as extra_end,ifnull(SUM(ds.extra_start),0) as extra_start,ifnull(SUM(ds.no_of_hiring_end),0) as no_of_hiring_end,ifnull(SUM(ds.no_of_hiring_start),0) as no_of_hiring_start');
        $this->db->from('payin p');
        $this->db->join('driver_settlement ds','ds.payin_id=p.payin_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('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 r','r.roster_id=rdm.roster_id','inner');
        $this->db->join('dr_company_agreement_mapping dcam','dcam.driver_id=p.driver_id','inner');
        
        $this->db->join('master_car mc','mc.car_id=r.car_id AND mc.company_id = dcam.company_id ','inner');
       
        //$this->db->join('master_company mcm','mcm.company_id=mc.company_id','inner');
        
        $this->db->where('p.levy_settled_flag',$levy_settled_flag);
        $this->db->where('ds.shift_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_new($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,p.payin_id as payin_id,dsd.shift_no,ifnull(ds.shift_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(ds.extra_end,0) as extra_end,ifnull(ds.extra_start,0) as extra_start,ifnull(ds.no_of_hiring_end,0) as no_of_hiring_end,ifnull(ds.no_of_hiring_start,0) as no_of_hiring_start');
        $this->db->from('payin p');
        $this->db->join('driver_settlement ds','ds.payin_id=p.payin_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('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 r','r.roster_id=rdm.roster_id','inner');
        //$this->db->join('dr_company_agreement_mapping dcam','dcam.driver_id=p.driver_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->where('p.levy_settled_flag',$levy_settled_flag);
        $this->db->where('ds.shift_levy > ',0);
		if($driver_id){
            $this->db->where('p.driver_id',$driver_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(r.day_date,"%d/%m/%Y"),"%d/%m/%Y") >=',$start_date);
            $this->db->where('str_to_date(date_format(r.day_date,"%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_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,ds.shift_levy as levy,ifnull(ds.extra_end,0) as extra_end,ifnull(ds.extra_start,0) as extra_start,ifnull(ds.no_of_hiring_end,0) as no_of_hiring_end,ifnull(ds.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.flat_no,",",up.street_no,",",up.street_name,",",up.suburb,",",up.state,",",up.pin) as address');
        $this->db->from('payin p');
        $this->db->join('driver_settlement ds','ds.payin_id=p.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('dr_company_agreement_mapping dcam','dcam.driver_id=p.driver_id','inner');
        
        $this->db->join('master_car mc','mc.car_id=r.car_id AND mc.company_id = dcam.company_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->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','DESC');
        $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('ds.*,dsm.shift_name,dsd.shift_no,mc.registration_no,date_format(ds.created_on,"%d/%m/%Y %H:%i:%s") as settled_date,CASE WHEN ABS(ds.amount_due) <> ABS(ds.final_due_amt) THEN date_format(ds.updated_on,"%d/%m/%Y %H:%i:%s") ELSE "" END as latest_payment_ts,ds.speedo_reading_start,ds.speedo_reading_end');
        $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=ds.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(!empty($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(r.day_date,"%d/%m/%Y"),"%d/%m/%Y") >=',$start_date);
            $this->db->where('str_to_date(date_format(r.day_date,"%d/%m/%Y"),"%d/%m/%Y") <=',$end_date);
        }
        $this->db->group_by('p.payin_id');    
        $this->db->order_by('r.day_date','ASC');
        $this->db->order_by('dsm.shift_name','ASC');
        $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);                
        $this->db->where('mu.role_id',2);                  
        $this->db->order_by('up.first_name','ASC');                  
        $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('master_user mu'); 
        $this->db->join('user_profile up','up.user_id=mu.user_id','inner'); 
        $this->db->join('roster_driver_maping rdm','rdm.driver_id=mu.user_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->where('mc.company_id',$company_id); 
        $this->db->where('mu.is_active',1);   
        $this->db->group_by('mu.user_id','ASC');
        $this->db->order_by('up.first_name','ASC');               
        $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);                  
        $this->db->order_by('mc.registration_no','ASC');                  
        $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('ds.*,ds.shift_levy as levy,date_format(r.day_date,"%d/%m/%Y") as day_date,dsd.shift_no,ifnull((ds.total_extra_end - ds.total_extra_start),0) as metered_fares,dsm.shift_name,mc.registration_no,date_format(p.created_ts,"%d/%m/%Y") as payin_date,
        (SELECT ifnull(SUM(dse.expense_settlement_amt),"0")FROM dr_settlement_expense dse INNER JOIN payin_expenses pe ON dse.payin_expense_id = pe.payin_expense_id WHERE dse.dr_settlement_id=ds.dr_settlement_id AND pe.expense_id=1)  as expense_total_fuel,
        (SELECT ifnull(SUM(dse.expense_settlement_amt),"0")FROM dr_settlement_expense dse INNER JOIN payin_expenses pe ON dse.payin_expense_id = pe.payin_expense_id WHERE dse.dr_settlement_id=ds.dr_settlement_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,p.rental_commission_flag,ifnull(p.rental_amount,"N/A") as rental_amount,
        ds.no_of_whl  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_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(r.day_date,"%d/%m/%Y"),"%d/%m/%Y") >=',$start_date);
            $this->db->where('str_to_date(date_format(r.day_date,"%d/%m/%Y"),"%d/%m/%Y") <=',$end_date);
        }
        $this->db->order_by('r.day_date','ASC');
        //$this->db->order_by('dsm.shift_name','DESC');

        $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.*,ds.shift_levy as levy,date_format(r.day_date,"%d/%m/%Y") as day_date,dsd.shift_no,(ds.total_extra_end - ds.total_extra_start) as 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,
        ds.no_of_whl  as total_lifting_fees');
         //(SELECT gtn_share * p.no_of_whl  FROM master_lifting_fee ORDER BY lifting_fee_id DESC LIMIT 1)
        $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(r.day_date,"%d/%m/%Y"),"%d/%m/%Y") >=',$start_date);
            $this->db->where('str_to_date(date_format(r.day_date,"%d/%m/%Y"),"%d/%m/%Y") <=',$end_date);
        }
        $this->db->order_by('r.day_date','ASC');
        //$this->db->order_by('dsm.shift_name','ASC');
        
        $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');
        $this->db->order_by('sn.service_status','ASC');
        $this->db->order_by('p.created_ts','ASC');
        $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,' ',ifnull(up.middle_name,''),' ',up.last_name) as full_name,mu.dc_no,up.mobile,get_settlement_due_status(rdm.driver_id,r.car_id) as weekly_settlement_status
            FROM roster_driver_maping rdm
            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 driver_shift_details dsd ON dsd.roster_driver_id=rdm.roster_driver_id
            LEFT JOIN payin p ON p.shift_id=dsd.shift_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 master_car mc ON mc.car_id=r.car_id
            WHERE rdm.created_ts between DATE_SUB(NOW(), INTERVAL 30 DAY)  and now() 
            GROUP BY rdm.driver_id,r.car_id
            ORDER BY mc.registration_no ASC";
        
        //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 ;

    }

}