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

class Mrentout extends CI_Model {

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

    }

    public function getRentoutDetails($where=''){
        $this->db->select('rov.*,md.first_name,md.middle_name,md.last_name,md.flat_no,md.street_no,md.street_name,md.suburb,md.pin,md.dob,md.licence_no,md.licence_expiry,md.is_australian_licence,md.passport_no,md.mobile,md.created_ts driver_reg_date,mc.make,mc.model,mc.car_no,mc.total_odometer_reading,bw.bond_payment_method,bw.bond_reference_no,mcp.company_name,mc.car_no,mc.make,mc.model,ddc.advantage_pay_customer_response,md.country_code,riv.is_deleted_direct_debit,md.email');
        $this->db->from('rent_out_vehcile rov');
        $this->db->join('rent_in_vehcile riv','riv.rent_out_id=rov.rent_out_id','left');
        $this->db->join('master_driver md','md.driver_id=rov.driver_id','inner');
        $this->db->join('direct_debit_customers ddc','ddc.direct_debit_customer_id=rov.direct_debit_customer_id','left');
        $this->db->join('master_car mc','mc.car_id=rov.car_id','inner'); 
        $this->db->join('master_company mcp','mcp.company_id=mc.company_id','inner');
        $this->db->join('bond_wallet bw','bw.driver_id=rov.driver_id','inner'); 
        $this->db->group_by('rov.rent_out_id');
        
        // $this->db->join('master_company mcom','mcom.company_id=rov.company_id','inner');
        if(!empty($where)){
            $this->db->where('rov.rent_out_id',$where);
            $query=$this->db->get();
            return $query->row_array();
        }else{ 
            $this->db->where('rov.is_rent_in',0);
            $this->db->order_by('rov.rent_out_id','desc');
            $query=$this->db->get();
            return $query->result_array();
        }
    }

    public function get_driver(){
        $this->db->select('md.*');
        $this->db->from('master_driver md');
        $this->db->where(" NOT EXISTS (SELECT rov.driver_id FROM rent_out_vehcile rov WHERE rov.driver_id = md.driver_id AND rov.is_rent_in='0')", null, false);
        $this->db->where('md.status','1');
        $this->db->order_by('md.first_name','ASC');
        $query=$this->db->get();
        return $query->result_array();
    }

    public function get_driver_details($driver_id){
        $this->db->select('md.*');
        $this->db->from('master_driver md');
        $this->db->where('md.driver_id',$driver_id);
        $query=$this->db->get();
        return $query->row_array();
    }

    public function get_direct_debit_customer_details($driver_id,$company_id){
        $this->db->select('ddc.*,md.country_code,md.mobile,md.pin,md.email');
        $this->db->from('direct_debit_customers ddc');
        $this->db->join('master_driver md','md.driver_id = ddc.driver_id','inner');
        $this->db->where('ddc.driver_id',$driver_id);
        $this->db->where('ddc.company_id',$company_id);
        $query=$this->db->get();
        return $query->row_array();
    }

    

    public function get_car_details($car_id){
        $this->db->select('DATE_FORMAT(mc.insurance_expire_date,"%d/%m/%Y") AS insurance_expire_date');
        $this->db->from('master_car mc');
        $this->db->where('mc.car_id',$car_id);
        $query=$this->db->get();
        return $query->row_array();
    }

    public function get_car(){
        $this->db->select('mc.*');
        $this->db->from('master_car mc');
        $this->db->where(" NOT EXISTS (SELECT rov.car_id FROM rent_out_vehcile rov WHERE rov.car_id = mc.car_id AND rov.is_rent_in='0')", null, false);
        $this->db->where('mc.status','0');
        $this->db->order_by('mc.car_no','ASC');
		$this->db->group_by('mc.car_id');
        $query=$this->db->get();
        return $query->result_array();
    }

    public function getdriverbondamount($driver_id){
        $sql = "SELECT sum(bond_amount) total_credit_amount FROM `bond_wallet` WHERE driver_id='".$driver_id."' AND transaction_type='CREDIT'";
        $query = $this->db->query($sql);
        $result=$query->row_array();

        $sql1 = "SELECT sum(bond_amount) total_debit_amount FROM `bond_wallet` WHERE driver_id='".$driver_id."' AND transaction_type='DEBIT'";
        $query1 = $this->db->query($sql1);
        $result1=$query1->row_array();

        return $actual_bond_amount= $result['total_credit_amount'] - $result1['total_debit_amount'];
        
    }

    public function get_countries(){
        $this->db->select('c.*');
        $this->db->from('country c');       
        $this->db->order_by('c.name','ASC');
		$query=$this->db->get();
        return $query->result_array();
    }

    
    public function submit_rentout($data){
        $this->db->insert('rent_out_vehcile', $data);
        return $this->db->insert_id();
    }

    public function edit_car($car_id){
        $this->db->select('master_car.*,master_company.advantage_pay_credentials');
        $this->db->from('master_car');
        $this->db->join('master_company','master_company.company_id = master_car.company_id','inner');
		$this->db->where('car_id',$car_id);
        $query=$this->db->get();
        return $query->row_array();
    }

    public function update_rentout($condition,$data){
        $result=$this->db->update('rent_out_vehcile', $data, $condition);
        return $result;
    }

    public function update_car($condition,$data){
        $result=$this->db->update('master_car', $data, $condition);
        return $result;
    }

    public function get_not_authorized_payments(){
        
        $sql = "SELECT rov.*,mcm.advantage_pay_credentials
        FROM rent_out_vehcile rov
        INNER JOIN master_car mc ON mc.car_id=rov.car_id
        INNER JOIN master_company mcm ON mcm.company_id=mc.company_id
        WHERE ifnull(rov.direct_debit_id,'') <> '' AND rov.is_authorised in(0,2)";
        //echo $sql;die;
        $run_sql=$this->db->query($sql);
        if($run_sql){
            return $run_sql->result_array();
        } 

        return false;
        
    }

    public function getRentoutDetailsForPDF($where=''){
        $this->db->select('rov.*,md.first_name,md.middle_name,md.last_name,md.flat_no,md.street_no,md.street_name,md.suburb,md.pin,md.dob,md.licence_no,md.licence_image,md.licence_expiry,md.is_australian_licence,md.passport_no,md.mobile,md.created_ts driver_reg_date,mc.make,mc.model,mc.car_no,mc.total_odometer_reading,bw.bond_amount,bw.bond_payment_method,bw.bond_reference_no,mcp.company_name,riv.rent_in_id,riv.odometer_reading miles_in,riv.damage_amount,riv.created_ts as rent_in_date,mc.car_no,mc.make,mc.model');
        $this->db->from('rent_out_vehcile rov');
        $this->db->join('rent_in_vehcile riv','riv.rent_out_id=rov.rent_out_id','left');
        $this->db->join('master_driver md','md.driver_id=rov.driver_id','inner');
        $this->db->join('master_car mc','mc.car_id=rov.car_id','inner');
        $this->db->join('master_company mcp','mcp.company_id=mc.company_id','inner');
        $this->db->join('bond_wallet bw','bw.driver_id=rov.driver_id','inner'); 
        $this->db->group_by('rov.rent_out_id');
        
        // $this->db->join('master_company mcom','mcom.company_id=rov.company_id','inner');
        if(!empty($where)){
            $this->db->where('rov.rent_out_id',$where);
            $query=$this->db->get();
            return $query->row_array();
        }else{ 
            $this->db->order_by('rov.rent_out_id','desc');
            $query=$this->db->get();
            return $query->result_array();
        }
    }

    public function getZipCodeWise($zipcode){

        $this->db->select('state_name');
        $this->db->from('master_state');
        $this->db->where($zipcode.' BETWEEN zip_code_from and zip_code_to');
        $query=$this->db->get();
        return $query->row_array();
        

    }

    public function get_no_odometer_readings(){
        
        $sql = "SELECT MAX(o.odometer_id),rov.rent_out_no,rov.rent_out_id,md.first_name,md.middle_name,md.last_name,md.email,atd.token_key,
        DATEDIFF(CURDATE(),ifnull(MAX(DATE(o.odometer_update_date)),DATE(rov.created_ts))) as remaining_odometer_day,mc.car_id
        FROM rent_out_vehcile rov
        INNER JOIN master_driver md ON md.driver_id=rov.driver_id
        INNER JOIN master_car mc ON mc.car_id=rov.car_id
        LEFT JOIN odometers o ON o.rent_out_id=rov.rent_out_id
        INNER JOIN api_token_driver atd ON atd.user_id=rov.driver_id AND atd.device_type IN (1,2)
        WHERE rov.is_rent_in = 0 GROUP BY rov.rent_out_id HAVING remaining_odometer_day > 15"; 
        //echo $sql;die; 
        $run_sql=$this->db->query($sql);
        if($run_sql){
            return $run_sql->result_array();
        } 

        return false;
        
    }

}