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

class Mpayin extends CI_Model {

	public function __construct() {        
		parent::__construct();
	}
	public function totalDockets($table,$condition){
        $result         = array();
        $total_docket   = 0.00;
        $this->db->select('sum(value) as total_docket');
        $this->db->from($table);
        $this->db->where($condition);
        $query  = $this->db->get();
        $result = $query->row_array();
        if(!empty($result) && !empty($result['total_docket'])){
            $total_docket   = $result['total_docket'];
        } 

        return $total_docket;
    }
	public function totalExpense($table,$condition){
        $result         = array();
        $total_expense  = 0.00;
        $this->db->select('sum(value) as total_expense');
        $this->db->from($table);
        $this->db->where($condition);
        $query  = $this->db->get();
        $result = $query->row_array();
        if(!empty($result) && !empty($result['total_expense'])){
            $total_expense   = $result['total_expense'];
        }        
        return $total_expense;
    }
    public function AccidentInstallment($cond_accident_installment){
        $result                       = array();
        $total_accident_installment   = 0.00;
        $this->db->select('sum(shift_amt) as total_accident_installment');
        $this->db->from('master_accident');
        $this->db->join('accident_charge', 'master_accident.accident_id = accident_charge.accident_id', 'left');
        $this->db->where($cond_accident_installment); 
        $query  = $this->db->get();
       //echo $this->db->last_query(); die();
        $result = $query->row_array();
        if(!empty($result) && !empty($result['total_accident_installment'])){
            $total_accident_installment   = $result['total_accident_installment'];
        }
        
        return $total_accident_installment;
    }
    public function GetAccidentData($cond_accident_installment){
        $result                       = array();
        $total_accident_installment   = 0.00;
        $this->db->select('master_accident.*,accident_charge.*');
        $this->db->from('master_accident');
        $this->db->join('accident_charge', 'master_accident.accident_id = accident_charge.accident_id', 'left');
        $this->db->where($cond_accident_installment); 
        $query  = $this->db->get();
       //echo $this->db->last_query(); die();
        $result = $query->result_array();
        return $result ;
    }
    public function getCommission($commission_cond){
        $result         = array();
        $driver_share   = 0.00;
        $this->db->select('master_commission.owner_share as driver_share');// owner share for logic change from client end
        $this->db->from('user_profile');
        $this->db->join('master_commission', 'user_profile.dr_commission_id = master_commission.commission_id', 'left');
        $this->db->where($commission_cond); 
        $query  = $this->db->get();
        //echo $this->db->last_query(); die();
        $result = $query->row_array();
        //pr($result);
        if(!empty($result) && !empty($result['driver_share'])){
            $driver_share   = $result['driver_share'];
        }        

        return $driver_share;
    }

    public function get_ongoing_shift_details($driver_id,$shift_from_date,$shift_to_date){
        
        $query="SELECT r.roster_id,rdm.roster_driver_id,dsd.*,r.car_id,mc.registration_no,mc.levy_applicable,CASE WHEN rsm.shift_name = 'D' THEN 'DAY (4am to 4pm)' WHEN rsm.shift_name = 'N' THEN 'NIGHT (4pm to 4am)' ELSE 'HUNGRY (4am to 4am)' END as shift_name,date_format(r.day_date,'%d/%m/%Y') as day_date,r.dayname FROM driver_shift_details dsd
                INNER JOIN roster_driver_maping rdm ON rdm.roster_driver_id=dsd.roster_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
                WHERE rdm.driver_id='".$driver_id."' AND r.day_date BETWEEN STR_TO_DATE('".$shift_from_date."','%d/%m/%Y') AND STR_TO_DATE('".$shift_to_date."','%d/%m/%Y') AND dsd.shift_status = 1";
        $run_query=$this->db->query($query);
        $result=$run_query->row_array();
        return $result;
    }

    public function get_driver_shift_details($driver_id,$shift_from_date,$shift_to_date){ 
        
        $query='SELECT r.roster_id,r.car_id,rdm.roster_driver_id,CASE WHEN rsm.shift_name = "D" THEN "DAY (4am to 4pm)" WHEN rsm.shift_name = "N" THEN "NIGHT (4pm to 4am)" ELSE "HUNGRY (4am to 4am)" END as shift_name,date_format(r.day_date,"%d/%m/%Y") as day_date,r.dayname,mc.registration_no
		from roster_driver_maping rdm
		left join roster_shift_maping rsm on rsm.roster_shift_id=rdm.roster_shift_id
		left join roster r on r.roster_id=rsm.roster_id
		inner join master_car mc on mc.car_id=r.car_id
		where rdm.driver_id ='.$driver_id.' 
        AND NOT EXISTS (SELECT 1 FROM driver_shift_payin dsp WHERE dsp.roster_driver_id=rdm.roster_driver_id) AND r.day_date BETWEEN STR_TO_DATE("'.$shift_from_date.'","%d/%m/%Y") AND STR_TO_DATE("'.$shift_to_date.'","%d/%m/%Y")';
        //echo $query;die;
        $run_query=$this->db->query($query);
        //print_r($this->db->last_query());die();
		return $run_query->row_array(); 

    }

    public function get_shift_number($roster_driver_id){
        $this->db->select('CONCAT(CASE WHEN rsm.shift_name = "D" THEN "DAY" WHEN rsm.shift_name = "N" THEN "NIGHT" ELSE "HUNGRY" END,"/",date_format(r.day_date,"%d%m%Y"),"/",mc.registration_no,"/",mu.dc_no) as shift_no');
		$this->db->from('roster_driver_maping rdm');
		$this->db->join('roster_shift_maping rsm','rsm.roster_shift_id=rdm.roster_shift_id','left');
		$this->db->join('roster r','r.roster_id=rsm.roster_id','left');
        $this->db->join('master_car mc','mc.car_id=r.car_id','inner');
        $this->db->join('master_user mu','mu.user_id=rdm.driver_id','inner');
        $this->db->join('user_profile up','up.user_id=mu.user_id','inner');
        $this->db->where('rdm.roster_driver_id',$roster_driver_id);
        $query=$this->db->get();
        //print_r($this->db->last_query());die();
		return $query->row_array();

    
    }
    

    public function insert_driver_shift_payin($start_time,$end_time,$driver_id,$shift_id){
        $check_end_time = $end_time;
        $query='INSERT INTO driver_shift_payin(shift_id,roster_driver_id,created_by,created_ts)
        SELECT "'.$shift_id.'",rdm.roster_driver_id, "'.$driver_id.'","'.date("Y-m-d H:i:s").'"
		from roster_driver_maping rdm
		left join roster_shift_maping rsm on rsm.roster_shift_id=rdm.roster_shift_id
		left join roster r on r.roster_id=rsm.roster_id
		inner join master_car mc on mc.car_id=r.car_id
		where rdm.driver_id ="'.$driver_id.'" AND r.breakdown <> 1
        AND r.day_date BETWEEN str_to_date("'.$start_time.'","%d/%m/%Y") AND  
        str_to_date("'.$end_time.'","%d/%m/%Y")
        AND NOT EXISTS (SELECT 1 FROM driver_shift_details dsd WHERE dsd.roster_driver_id=rdm.roster_driver_id AND dsd.shift_status=2)';
        //echo $query;die;
        $run_query=$this->db->query($query);
        return true;


    }

    public function check_service_notification($shift_id,$payin_id){
        $query="SELECT check_service_notification('".$shift_id."','".$payin_id."') as notification_flag";
        $run_query=$this->db->query($query);
        $result=$run_query->row_array();
        return $result;
    }

    public function get_previous_payin_data($car_id){

        $query="SELECT p.payin_id,p.total_extra_end,TRUNCATE(p.paid_km_end,0) as paid_km_end,TRUNCATE(p.total_km_end,0) as total_km_end,TRUNCATE(p.no_of_hiring_end,0) as no_of_hiring_end,TRUNCATE(p.extra_end,0) as extra_end,TRUNCATE(p.speedo_reading_end,0) as speedo_reading_end
        FROM driver_settlement 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 roster r ON r.roster_id=rdm.roster_id
        WHERE r.car_id='".$car_id."' ORDER BY p.payin_id DESC LIMIT 1";
        
        //echo $query;die;
        $run_query=$this->db->query($query);
        $result=$run_query->row_array();
        return $result;
    }

}