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

class Mdashboard extends CI_Model {

    public function __construct() {
        parent::__construct();

    }

    public function get_car_van(){

        $query = $this->db->query('SELECT * FROM master_car WHERE car_type="Car" AND status= "0"');
        $total_car_van['total_car']=$query->num_rows();

        $query = $this->db->query('SELECT * FROM master_car WHERE car_type="Van" AND status= "0"');
        $total_car_van['total_van']=$query->num_rows();

        return $total_car_van;
    }

    public function get_on_rentin_car_van(){

        $query = $this->db->query('SELECT rov.*,mc.car_pic FROM rent_out_vehcile rov INNER JOIN  master_car mc ON mc.car_id=rov.car_id WHERE rov.is_rent_in=0 AND mc.car_type="Car" AND mc.status= "0" GROUP BY rov.car_id');
        $total_on_rentin_car_van['total_car']=$query->num_rows();

        $query = $this->db->query('SELECT rov.*,mc.car_pic FROM rent_out_vehcile rov INNER JOIN  master_car mc ON mc.car_id=rov.car_id WHERE rov.is_rent_in=0 AND mc.car_type="Van" AND mc.status= "0" GROUP BY rov.car_id');
        $total_on_rentin_car_van['total_van']=$query->num_rows();

        return $total_on_rentin_car_van;
    }

    public function get_pending_driver_validate(){

        $query = $this->db->query('SELECT driver_id,concat(first_name," ",middle_name," ",last_name) as driver_name,mobile,licence_no,is_australian_licence FROM master_driver WHERE status= "0" AND created_ts > now() - INTERVAL 15 day ORDER BY driver_id DESC');

        $pending_driver_validate=$query->result_array();

        return $pending_driver_validate;
    }

    public function get_return_notice(){

        $query = $this->db->query('SELECT rn.*,ifnull(riv.rent_in_id,"") as rent_in_id FROM return_notice rn LEFT JOIN rent_in_vehcile riv ON riv.rent_out_id = rn.rent_out_id HAVING rent_in_id = ""');
        $total_return_notice=$query->num_rows();

        return $total_return_notice;
    }

    public function get_failed_direct_debit(){

        $query = $this->db->query('SELECT COUNT(direct_debit_payment_id) as cnt FROM direct_debit_payments WHERE payment_status = "Failed" AND DATE(failed_ts)=DATE_SUB(CURDATE(), INTERVAL 1 DAY)');
        $total_failed_direct_debit=$query->row_array()['cnt'];

        return $total_failed_direct_debit;
    }

    

    public function get_available_vehicle(){

        $query = $this->db->query('SELECT car_no,car_type,insurance_expire_pic,car_pic FROM master_car WHERE status= "0" AND car_id NOT IN (SELECT car_id FROM rent_out_vehcile WHERE is_rent_in = 0) ORDER BY car_id DESC');

        $available_vehicle=$query->result_array();

        return $available_vehicle;
    }

    public function get_accidental_vehicle(){

        $query = $this->db->query('SELECT mc.car_no,mc.car_type,mc.insurance_expire_pic,car_pic FROM master_car mc WHERE mc.status IN("2","3") ORDER BY mc.car_id DESC');

        $accidental_vehicle=$query->result_array();

        return $accidental_vehicle;
    }

    public function get_accidental_pending_payment(){

        $query = $this->db->query('SELECT mc.car_no,concat(md.first_name," ",md.middle_name," ",md.last_name) as driver_name,acc.total_due_amount,mc.car_pic FROM accidents acc INNER JOIN  master_car mc ON mc.car_id=acc.car_id INNER JOIN  master_driver md ON md.driver_id=acc.driver_id WHERE acc.total_due_amount >0 AND mc.status="0" ORDER BY md.driver_id DESC');

        $accidental_pending_payment=$query->result_array();

        return $accidental_pending_payment;
    }

    public function get_insurance_renewals(){

        $query = $this->db->query('SELECT car_no,car_type,insurance_expire_pic,car_pic FROM master_car WHERE status= "0" AND DATEDIFF(insurance_expire_date,CURDATE()) between 0 AND 15 ORDER BY car_id DESC');

        $insurance_renewals=$query->result_array();

        return $insurance_renewals;
    }

    public function get_service_due_list(){
        
        $query = "SELECT mc.car_id,mc.car_no,mc.insurance_expire_pic,mc.make,mc.model,mc.year,ifnull(mc.total_odometer_reading,0) as total_odometer_reading,ifnull(mc.service_kilometer,0) as service_kilometer,
        (ifnull(mc.service_kilometer,0) - (ifnull(mc.total_odometer_reading,0) - ifnull(mc.last_service_kilometer,0))) as service_due_kilometer,
        (ifnull(mc.transmission_service,0) - (ifnull(mc.total_odometer_reading,0) - ifnull(mc.last_transmission_service,0))) 
        as transmission_service_due_kilometer,
        (ifnull(mc.spark_plug_for_eg,0) - (ifnull(mc.total_odometer_reading,0) - ifnull(mc.last_spark_plug_for_eg,0))) as spark_plug_service_due_kilometer, 
        
        concat(md.first_name,' ',md.middle_name,' ',md.last_name) as driver_name,md.mobile,mc.car_pic 
        FROM master_car mc 
        LEFT JOIN rent_out_vehcile rov ON rov.car_id = mc.car_id AND is_rent_in = '0'
        LEFT JOIN master_driver md ON md.driver_id = rov.driver_id
        WHERE mc.status='0' GROUP BY mc.car_id ORDER BY service_due_kilometer ASC,transmission_service_due_kilometer ASC, spark_plug_service_due_kilometer ASC";
        // echo $query;die;
        $run_query=$this->db->query($query);
        return $run_query->result_array();
    }

    public function update_last_service_km($car_id){

        $sql = "UPDATE master_car SET last_service_kilometer=total_odometer_reading,last_service_kilometer_ts='".date('Y-m-d H"i"s')."',updated_by=".$this->admin_session_data['user_id'].",updated_ts='".date('Y-m-d H"i"s')."' WHERE car_id = ".$car_id."";

        $run_sql = $this->db->query($sql);
        if($run_sql){
            return true;
        }
        return false;
    }

}