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

class Mbondreport 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('group_concat(p.payin_id) as payin_id,ifnull(sum(p.levy),"0.00") 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');
        $this->db->from('payin p');
        $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);
		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(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_meter_reading_report($start_date=null,$end_date=null,$car_id =null){
           
        $this->db->select('p.*,dsd.shift_no,mc.registration_no,date_format(p.created_ts,"%d/%m/%Y") as payin_date');
        $this->db->from('payin p');
        $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('master_car mc','mc.car_id=r.car_id','inner');
        
        if($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(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);
        }
        $query=$this->db->get();
        //echo '<pre>';print_r($this->db->last_query());die();
		return $query->result_array();
    }
    

    /*
    author: soumya hazra
    purpose: Get Active roles
    date: 9-9-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);                
        $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('dr_company_agreement_mapping dcam');                
        $this->db->join('master_user mu','mu.user_id=dcam.driver_id','inner');                
        $this->db->join('user_profile up','up.user_id=dcam.driver_id','inner');           
        $this->db->where('dcam.company_id',$company_id); 
        $this->db->where('mu.is_active',1);                  
        $query = $this->db->get();
        $result = $query->result_array();
        return $result;
    }

    /*
    author: soumya hazra
    purpose: Get In-Active roles
    date: 9-9-2019
    */
    public function get_inactive_role()
    {
        $result = array();

        $this->db->select('*');
        $this->db->from('master_role');                
        $this->db->where('is_active',0);                
        $query = $this->db->get();
        $result = $query->result_array();
        return $result;
    }



    /*
    author: soumya hazra
    purpose: Set a role deactive
    date: 9-9-2019
    */
    public function set_deactive_role($rid)
    {

        $this->db->set('is_active',0);                    
        $this->db->where('role_id',$rid);                    
        $this->db->update('master_role'); 

        return true;     

    }
    /*
    author: soumya hazra
    purpose: Set a role active
    date: 9-9-2019
    */
    public function set_active_role($rid)
    {

        $this->db->set('is_active',1);                    
        $this->db->where('role_id',$rid);                    
        $this->db->update('master_role'); 

        return true;     

    }
     /*
    author: soma
    purpose: Get singel role
    date: 18-9-2019
    */
    public function get_singl_role($role_id)
    {
        $result =  array();
        $this->db->select('*');
        $this->db->from('master_role');       
        $this->db->where('role_id',$role_id);       
        $query = $this->db->get();
       // echo $this->db->last_query();die;
        $result = $query->row_array();
        return $result;
    }


    /*
    author: soma
    purpose: Update role
    date: 18-9-2019
    */
    public function updaterole($role_name,$updated_by,$role_id)
    {       
        $this->db->set('role_name',$role_name); 
        $this->db->set('updated_by',$updated_by);       
        $this->db->where('role_id',$role_id);
        $this->db->Update('master_role');

        return true;
    }


}