File: /var/www/html/taxicamera/old/application/models/admin/Mtracker.php
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class Mdriver extends CI_Model {
var $table = 'users';
var $column_order = array('user_id','status','date_of_creation',null); //set column field database for datatable orderable
var $column_search = array('first_name','last_name','email','phoneno','status','islands_name','date_of_creation'); //set column field database for datatable searchable just firstname , lastname , address are searchable
var $order = array('user_id' => 'desc'); // default order
public function __construct() {
parent::__construct();
}
private function _get_datatables_query($ads_status,$island_id){
$this->db->select('users.*,islands.islands_name');
$this->db->from($this->table);
$this->db->join('islands', 'users.country = islands.island_id', 'inner');
$i = 0;
foreach ($this->column_search as $item){ // loop column
if($_POST['search']['value']){ // if datatable send POST for search
if($i===0){ // first loop
$this->db->group_start(); // open bracket. query Where with OR clause better with bracket. because maybe can combine with other WHERE with AND.
$this->db->like($item, $_POST['search']['value']);
}
else{
$this->db->or_like($item, $_POST['search']['value']);
}
if(count($this->column_search) - 1 == $i) //last loop
$this->db->group_end(); //close bracket
}
$i++;
}
if ($ads_status!='') {
$this->db->where('users.status', $ads_status);
}
if ($island_id!='') {
$this->db->where('users.country', $island_id);
}
if(isset($_POST['order'])){ // here order processing
$this->db->order_by($this->column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
}else if(isset($this->order)){
$order = $this->order;
$this->db->order_by(key($order), $order[key($order)]);
}
}
public function get_datatables($ads_status,$island_id){
$this->_get_datatables_query($ads_status,$island_id);
if($_POST['length'] != -1)
$this->db->limit($_POST['length'], $_POST['start']);
$query = $this->db->get();
//echo $this->db->last_query();die;
return $query->result();
}
public function count_filtered($ads_status,$island_id){
$this->_get_datatables_query($ads_status,$island_id);
$query = $this->db->get();
return $query->num_rows();
}
public function count_all(){
$this->db->from($this->table);
return $this->db->count_all_results();
}
public function get_details($user_id){
$this->db->select('*');
$this->db->from($this->table);
$this->db->where('user_id',$user_id);
$query=$this->db->get();
return $query->row_array();
}
public function update($condition,$data){
$this->db->where($condition);
$this->db->update($this->table,$data);
return 1;
}
public function add($data){
$this->db->insert($this->table,$data);
//echo $this->db->last_query();die;
return true;
}
public function delete($condition){
$this->db->delete($this->table,$condition);
return 1;
}
public function change_status($user_id){
$sql = "update users Set status = (case when (status = 1) THEN 0 ELSE 1 END) where `user_id`='".$user_id."'";
$query = $this->db->query($sql);
if($query){
return true;
}else{
return false;
}
}
public function get_driver_list($status ='0'){
$result=array();
$query = "select mu.*,up.*,
CONCAT(up.first_name,' ',ifnull(up.middle_name,''),' ',up.last_name) as full_name,
CONCAT(up.flat_no,' Street: ',up.street_no,',',up.street_name,',Suburb: ',up.suburb,',Postcode: ',up.pin) as full_address,
date_format(up.dr_licence_expiry,'%d/%m/%Y') dr_licence_expiry,
date_format(up.dr_dc_expiry,'%d/%m/%Y') dr_dc_expiry from master_user mu
inner join user_profile up on mu.user_id=up.user_id
where mu.role_id=2 AND status = '".$status."'";
$query1 = $this->db->query($query);
$result=$query1->result_array();
return $result;
}
public function get_driver_list_bu_companyID($company_id){
$result=array();
$query = "select mu.*,up.*,
CONCAT(up.first_name,' ',ifnull(up.middle_name,''),' ',up.last_name) as full_name,
CONCAT('Unit: ',up.flat_no,',Street: ',up.street_no,',',up.street_name,',Suburb: ',up.suburb,',Postcode: ',up.pin) as full_address,
date_format(up.dr_licence_expiry,'%d/%m/%Y') dr_licence_expiry,
date_format(up.dr_dc_expiry,'%d/%m/%Y') dr_dc_expiry from dr_company_agreement_mapping DCAM INNER JOIN master_user mu ON DCAM.driver_id = mu.user_id
inner join user_profile up on mu.user_id=up.user_id where mu.role_id=2 AND status = '1' AND DCAM.company_id ='".$company_id."'";
//echo $query;exit;
$query1 = $this->db->query($query);
//echo $this->db->last_query();die;
$result=$query1->result_array();
return $result;
}
public function get_docket_list_bu_companyID($company_id){
$this->db->select('master_docket.*');
$this->db->from('dr_company_agreement_mapping as DCAM');
$this->db->join('driver_settlement','driver_settlement.driver_id = DCAM.driver_id','inner');
$this->db->join('dr_settlement_docket','dr_settlement_docket.dr_settlement_id = driver_settlement.dr_settlement_id');
$this->db->join('payin_dockets','payin_dockets.payin_dockets_id = dr_settlement_docket.payin_docket_id','inner');
$this->db->join('master_docket','master_docket.docket_id = payin_dockets.docket_id','inner');
$this->db->where('DCAM.company_id',$company_id);
$this->db->where('master_docket.is_active','1');
$query = $this->db->get();
//echo $this->db->last_query();exit;
$result = $query->result_array();
return $result;
}
public function get_driver_master_list(){
$result=array();
$query = "select mu.*,up.*,
CONCAT(up.first_name,' ',ifnull(up.middle_name,''),' ',up.last_name) as full_name,
CONCAT('Unit: ',up.flat_no,',Street: ',up.street_no,',',up.street_name,',Suburb: ',up.suburb,',Postcode: ',up.pin) as full_address,
date_format(up.dr_licence_expiry,'%d/%m/%Y') dr_licence_expiry,
date_format(up.dr_dc_expiry,'%d/%m/%Y') dr_dc_expiry from master_user mu
inner join user_profile up on mu.user_id=up.user_id
where mu.role_id=2 AND is_active = 1 AND status = 1 AND is_delete != 1 order by up.updated_ts DESC";
//echo $query;exit;
$query1 = $this->db->query($query);
$result=$query1->result_array();
return $result;
}
public function get_driver_data($user_id){
$result=array();
$query = "select mu.*,up.*,date_format(up.dr_licence_expiry,'%d/%m/%Y') dr_licence_expiry,date_format(up.dr_dc_expiry,'%d/%m/%Y') dr_dc_expiry from master_user mu
inner join user_profile up on mu.user_id=up.user_id
where mu.user_id='".$user_id."'";
$query1 = $this->db->query($query);
$result=$query1->row_array();
return $result;
}
public function get_driver_doc($user_id){
$result=array();
$query = "select * ,date_format(created_ts,'%d/%m/%Y') upload_date from user_docs where user_id='".$user_id."' order by driver_docs_id DESC";
$query1 = $this->db->query($query);
$result=$query1->result_array();
return $result;
}
public function check_email_exist($email,$user_id){
$this->db->select('user_id');
$this->db->from('master_user');
$this->db->where('email', $email);
$this->db->where('user_id !=', $user_id);
$query = $this->db->get();
$num = $query->num_rows();
if ($num > 0) {
return FALSE;
} else {
return TRUE;
}
}
public function check_dc_no_exist($dc_no,$user_id){
$this->db->select('user_id');
$this->db->from('master_user');
$this->db->where('dc_no', $dc_no);
$this->db->where('user_id !=', $user_id);
$query = $this->db->get();
$num = $query->num_rows();
if ($num > 0) {
return FALSE;
} else {
return TRUE;
}
}
/*
author: soma
purpose: Set a driver deactive
date: 12-9-2019
*/
public function set_deactive_driver($user_id)
{
$this->db->set('is_active',0);
$this->db->where('user_id',$user_id);
$this->db->update('master_user');
// echo $this->db->last_query();die;
return true;
}
/*
author: soma
purpose: Set a driver active
date: 12-9-2019
*/
public function set_active_driver($user_id)
{
$this->db->set('is_active',1);
$this->db->where('user_id',$user_id);
$this->db->update('master_user');
return true;
}
/*
author: soma
purpose: Generate Agreement
date: 13-9-2019
*/
public function generate_agreement($user_id)
{
$this->db->set('status',1);
$this->db->set('is_active',1);
$this->db->where('user_id',$user_id);
$this->db->update('master_user');
return true;
}
/*
author: soma
purpose: delete driver
date: 13-9-2019
*/
public function delete_driver($user_id)
{
$this->db->set('is_delete',1);
$this->db->set('is_active',0);
$this->db->where('user_id',$user_id);
$this->db->update('master_user');
return true;
}
/*
author: soma
purpose: restore driver
date: 13-9-2019
*/
public function restore_driver($user_id)
{
$this->db->set('is_delete',0);
$this->db->set('is_active',1);
$this->db->where('user_id',$user_id);
$this->db->update('master_user');
return true;
}
/*
author: soma
purpose: driver deactive list
date: 13-9-2019
*/
public function get_driver_master_list_inc(){
$result=array();
$query = "select mu.*,up.*,
CONCAT(up.first_name,' ',ifnull(up.middle_name,''),' ',up.last_name) as full_name,
CONCAT('Unit: ',up.flat_no,',Street: ',up.street_no,',',up.street_name,',Suburb: ',up.suburb,',Postcode: ',up.pin) as full_address,
date_format(up.dr_licence_expiry,'%d/%m/%Y') dr_licence_expiry,
date_format(up.dr_dc_expiry,'%d/%m/%Y') dr_dc_expiry from master_user mu
inner join user_profile up on mu.user_id=up.user_id
where mu.role_id=2 AND is_active = 0 AND is_delete != 1";
$query1 = $this->db->query($query);
//echo $this->db->last_query();die;
$result=$query1->result_array();
return $result;
}
/*
author: soma
purpose: driver trash list
date: 13-9-2019
*/
public function get_driver_master_list_trash(){
$result=array();
$query = "select mu.*,up.*,
CONCAT(up.first_name,' ',ifnull(up.middle_name,''),' ',up.last_name) as full_name,
CONCAT('Unit: ',up.flat_no,',Street: ',up.street_no,',',up.street_name,',Suburb: ',up.suburb,',Postcode: ',up.pin) as full_address,
date_format(up.dr_licence_expiry,'%d/%m/%Y') dr_licence_expiry,
date_format(up.dr_dc_expiry,'%d/%m/%Y') dr_dc_expiry from master_user mu
inner join user_profile up on mu.user_id=up.user_id
where mu.role_id=2 AND status = 1 AND is_delete = 1";
$query1 = $this->db->query($query);
// echo $this->db->last_query();die;
$result=$query1->result_array();
return $result;
}
public function get_car_list($driver_id)
{
$result = array();
$this->db->select('master_car.car_id,master_car.registration_no as car_no');
$this->db->from('master_accident');
$this->db->join('master_user','master_accident.driver_id = master_user.user_id');
$this->db->join('user_profile','user_profile.user_id = master_user.user_id');
$this->db->join('master_car','master_car.car_id = master_accident.car_id');
$this->db->where('master_accident.driver_id',$driver_id);
$this->db->group_by('master_car.car_id');
$query = $this->db->get();
$result = $query->result_array();
return $result;
}
}