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

class Mdashboard extends CI_Model {

	public function __construct() {
		parent::__construct();
	}	
	public function actv_dr_cnt($condition = array())
    {

        $this->db->select('*');
        $this->db->from('master_user');
        $this->db->where($condition);
        $query = $this->db->get();
        return $query->num_rows();
    }
	public function daily_inspcn_fault($start_dt,$end_dt)
	{
		
		$result = array();
		$this->db->select("dsd.shift_no,inspection.*,inspection_answer.*,master_user.dc_no,user_profile.mobile,CONCAT(user_profile.first_name,' ',ifnull(user_profile.middle_name,''),' ',user_profile.last_name) as driver_name,master_car.registration_no");
        $this->db->from('inspection');
        $this->db->join('driver_shift_details dsd','dsd.shift_id = inspection.shift_id','inner');
        $this->db->join('inspection_answer','inspection.inspection_id = inspection_answer.inspection_id','inner');
        $this->db->join('master_user','master_user.user_id = inspection.driver_id','inner');
        $this->db->join('user_profile','user_profile.user_id = inspection.driver_id','inner');
        $this->db->join('master_car','master_car.car_id = inspection.car_id','inner');
        $this->db->where('inspection_answer.answer','0');
        $this->db->where("inspection.inspection_date BETWEEN '".$start_dt."' AND '". $end_dt."'");
        $this->db->order_by("inspection.created_ts", "DESC");
        $query = $this->db->get();
        //echo $this->db->last_query();exit;
        $result = $query->result_array();
                         
        return $result;
	}

	public function accdnt_fault_notfault_cnt($start_dt,$end_dt,$fault_type)
	{
		
		$result = array();
		$this->db->select("*");
        $this->db->from('master_accident');
        $this->db->where('fault_type',$fault_type);
        $this->db->where("accident_datetime BETWEEN '".$start_dt."' AND '". $end_dt."'");
        $this->db->order_by("accident_datetime","DESC");
        $query = $this->db->get();
        //echo $this->db->last_query();exit;
        $result = $query->result_array();                         
        return $query->num_rows();
    }
    
    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_count(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->row_array();
        return $result;



    }

    public function get_roster_report(){
        $query="SELECT COUNT(r.roster_id) as total_roster_count,(ifnull(sum(case when r.breakdown = 1 then 1 else 0 end),0)) as tot_breakdown_cnt,COUNT(rdm.roster_driver_id) as roster_set_cnt FROM roster r 
        LEFT JOIN roster_shift_maping rsm ON rsm.roster_id=r.roster_id
        LEFT JOIN roster_driver_maping rdm ON rdm.roster_shift_id=rsm.roster_shift_id
        WHERE r.day_date BETWEEN (curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY) AND (curdate() - INTERVAL DAYOFWEEK(curdate()) DAY)";
        //echo $query;die;
        $run_query=$this->db->query($query);
        $result=$run_query->row_array();
        return $result;
    }

    public function get_job_report(){

        $query="SELECT sum(ifnull(p.levy,0)) as levy,
        SUM((p.extra_end - p.extra_start / 2) - (p.no_of_hiring_end - no_of_hiring_start)) as hail_job_cnt,
        SUM(p.no_of_hiring_end - no_of_hiring_start) as tot_job_cnt
        FROM payin p
        WHERE str_to_date(p.created_ts,'%Y-%m-%d') BETWEEN (curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY) AND (curdate() - INTERVAL DAYOFWEEK(curdate()) DAY)";
        //echo $query;die;
        $run_query=$this->db->query($query);
        $result=$run_query->row_array();
        return $result;
    }


    public function get_driver_due_data(){

        $query="SELECT ifnull(p.final_due_amt,'0.00') as due_amount,dsd.shift_no,up.mobile,up.first_name
        FROM payin p
        INNER JOIN user_profile up ON up.user_id=p.driver_id
        INNER JOIN driver_shift_details dsd ON dsd.shift_id = p.shift_id
        where p.driver_settlement_flag = 2 ORDER BY p.created_ts DESC";
        //AND str_to_date(p.created_ts,'%Y-%m-%d') BETWEEN (curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY) AND (curdate() - INTERVAL DAYOFWEEK(curdate()) DAY)
        $run_query=$this->db->query($query);
        $result=$run_query->result_array();
        return $result;


    }

    public function get_driver_due_data_not_paid(){

        $query="SELECT ifnull(p.final_due_amt,'0.00') as due_amount,dsd.shift_no,up.mobile,up.first_name
        FROM payin p
        INNER JOIN user_profile up ON up.user_id=p.driver_id
        INNER JOIN driver_shift_details dsd ON dsd.shift_id = p.shift_id
        where p.driver_settlement_flag = 0 ORDER BY p.created_ts DESC";
        //AND str_to_date(p.created_ts,'%Y-%m-%d') BETWEEN (curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY) AND (curdate() - INTERVAL DAYOFWEEK(curdate()) DAY)
        $run_query=$this->db->query($query);
        $result=$run_query->result_array();
        return $result;


    }

    

    function get_total_bond(){
        //SELECT ifnull((SUM(ifnull(bond_amt,0)) -((ifnull(initial_amt_paid,0) + ifnull(installment_amt_paid,0)))),0) as due_amt FROM  master_bond WHERE bond_settlement_flag =0
        $query="SELECT ifnull(SUM(ifnull(initial_amt_paid,0) + ifnull(installment_amt_paid,0)),0) as paid_amt FROM  master_bond";
        $run_query=$this->db->query($query);
        $result=$run_query->row_array();
        return $result;
    }

    function get_total_cash_in_hand(){
        $transaction_date = date('Y-m-d');
        //$transaction_previous_date=date('Y-m-d', strtotime('-1 day', strtotime($transaction_date)));

        $query="SELECT ((SELECT ifnull(SUM(dts.total_amount),0) FROM daily_transactions_settle dts WHERE dts.transaction_date = (select max(date(dts1.transaction_date))
        from daily_transactions_settle dts1  
        where date(dts1.transaction_date) < date('".$transaction_date."'))) + (ifnull(sum(case when dt.transaction_type = 1 then dt.total_amount else 0 end),0)) - ifnull(sum(case when dt.transaction_type = 2 then dt.total_amount else 0 end),0)) as total_cash_in_hand  FROM  daily_transactions dt WHERE dt.transaction_date ='".$transaction_date."'";
        //echo $query;die;
        $run_query=$this->db->query($query);
        $result=$run_query->row_array();
        return $result;
    }
    
    
}