File: /var/www/html/pmw24/pmw_live_testing/app/application/models/customer/Mcar.php
<?php
class Mcar extends CI_Model {
var $table = 'car';
var $column_order = array('car_id',null,'car_no','company_name','make','model_no','body','compliance_date','vin','engine','fuel','network','colour','camera','registration_expiry_date','registration_due_month','plate_type','car_status','terminal_no'); //set column field database for datatable orderable
var $column_search = array('car_no','company_name','make','model_no','body','compliance_date','vin','engine','fuel','network','colour','camera','registration_expiry_date','registration_due_month','plate_type','car_status','terminal_no'); //set column field database for datatable searchable just firstname , lastname , address are searchable
var $order = array('car_id' => 'desc'); // default order
var $table_history = 'job';
var $column_order_history = array('job.job_id','car.car_no','job.service',null); //set column field database for datatable orderable
var $column_search_history = array('job.job_id','car.car_no','job.service'); //set column field database for datatable searchable just firstname , lastname , address are searchable
var $order_history = array('job.job_id' => 'desc'); // default order
var $table_invoice_history = 'invoices';
var $column_order_invoice_history = array('invoices.id','invoices.invoice_no','invoice_details.item',null); //set column field database for datatable orderable
var $column_search_invoice_history = array('invoices.id','invoices.invoice_no','invoice_details.item'); //set column field database for datatable searchable just firstname , lastname , address are searchable
var $order_invoice_history = array('invoices.id' => 'desc'); // default order
public function __construct() {
parent::__construct();
$this->customer=$this->session->userdata('customer_data');
}
private function _get_datatables_query(){
// $this->db->select('car.*,IF(silver_service=1,"Yes","NO") silver_service,DATE_FORMAT(car.compliance_date, "%d/%m/%Y") compliance_date,DATE_FORMAT(car.registration_expiry_date, "%d/%m/%Y") registration_expiry_date,DATE_FORMAT(car.registration_due_month, "%d/%m/%Y") registration_due_month');
$this->db->select('car.*,compliance_date,registration_expiry_date,registration_due_month');
$this->db->from('car');
$this->db->where('car.customer_id',$this->customer['customer_id']);
$i = 0;
foreach ($this->column_search as $item){ // loop column
//echo $_POST['search']['value'];exit;
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(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(){
$this->_get_datatables_query();
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(){
$this->_get_datatables_query();
$query = $this->db->get();
return $query->num_rows();
}
public function count_all(){
$this->db->from($this->table);
$this->db->where('car.customer_id',$this->customer['customer_id']);
return $this->db->count_all_results();
}
private function _get_datatables_query_history(){
$this->db->select('job.job_id,car.car_no,job.service,job.job_status');
$this->db->from($this->table_history);
$this->db->join('car','car.car_id=job.car_id','left');
$this->db->where('car.customer_id',$this->customer['customer_id']);
$i = 0;
foreach ($this->column_search_history 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_history) - 1 == $i) //last loop
$this->db->group_end(); //close bracket
}
$i++;
}
if(isset($_POST['order'])){ // here order processing
$this->db->order_by($this->column_order_history[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
}else if(isset($this->order_history)){
$order = $this->order_history;
$this->db->order_by(key($order), $order[key($order)]);
}
}
public function get_datatables_history(){
$this->_get_datatables_query_history();
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_history(){
$this->_get_datatables_query_history();
$query = $this->db->get();
return $query->num_rows();
}
public function count_all_history(){
$this->db->from($this->table_history);
$this->db->join('car','car.car_id=job.car_id','left');
$this->db->where('car.customer_id',$this->customer['customer_id']);
return $this->db->count_all_results();
}
private function _get_datatables_query_invoice_history(){
$this->db->select('invoices.*,GROUP_CONCAT(invoice_details.item) as items,car.car_no,admins.name as mechanic');
$this->db->from($this->table_invoice_history);
$this->db->join('invoice_details','invoice_details.invoice_id=invoices.id','left');
$this->db->join('car','car.car_id=invoices.car_id','left');
$this->db->join('admins','admins.admin_id=invoices.mechanic_id','left');
$this->db->where('car.customer_id',$this->customer['customer_id']);
$this->db->where('ifnull(invoices.job_id,"")',"");
$this->db->group_by('invoices.id');
$this->db->order_by('invoices.id','DESC');
$i = 0;
foreach ($this->column_search_invoice_history 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_invoice_history) - 1 == $i) //last loop
$this->db->group_end(); //close bracket
}
$i++;
}
if(isset($_POST['order'])){ // here order processing
$this->db->order_by($this->column_order_invoice_history[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
}else if(isset($this->order_invoice_history)){
$order = $this->order_invoice_history;
$this->db->order_by(key($order), $order[key($order)]);
}
}
public function get_datatables_invoice_history(){
$this->_get_datatables_query_invoice_history();
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_invoice_history(){
$this->_get_datatables_query_invoice_history();
$query = $this->db->get();
return $query->num_rows();
}
public function count_all_invoice_history(){
$this->db->from($this->table_invoice_history);
$this->db->join('car','car.car_id=invoices.car_id','left');
$this->db->where('car.customer_id',$this->customer['customer_id']);
return $this->db->count_all_results();
}
public function get_car_invoice_history_details($invoice_id){
$this->db->select('invoices.*,ifnull(invoices.remarks,"") as remarks,GROUP_CONCAT(invoice_details.item) as items,car.car_no,admins.name as mechanic');
$this->db->from('invoices');
$this->db->join('invoice_details','invoice_details.invoice_id=invoices.id','left');
$this->db->join('car','car.car_id=invoices.car_id','left');
$this->db->join('admins','admins.admin_id=invoices.mechanic_id','left');
$this->db->where('invoices.id',$invoice_id);
$query=$this->db->get();
return $query->row_array();
}
public function get_details($car_id){
$this->db->select('car.*,car.silver_service as silver_service_flag,IF(silver_service=1,"Yes","NO") silver_service,DATE_FORMAT(car.compliance_date, "%d/%m/%Y") compliance_date,DATE_FORMAT(car.registration_expiry_date, "%d/%m/%Y") registration_expiry_date,DATE_FORMAT(car.registration_due_month, "%d/%m/%Y") registration_due_month,DATE_FORMAT(car.dob, "%d/%m/%Y") dob');
$this->db->from($this->table);
$this->db->where('car_id',$car_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 get_parent_category(){
$this->db->select('*');
$this->db->from($this->table);
$this->db->where('parent_id=0');
$query=$this->db->get();
return $query->result_array();
}
public function get_sub_category($category_id){
$this->db->select('*');
$this->db->from($this->table);
$this->db->where('parent_id',$category_id);
$query=$this->db->get();
return $query->result_array();
}
public function add($data){
$this->db->insert($this->table,$data);
return $this->db->insert_id();
}
public function get_category($category_id){
$this->db->select('*');
$this->db->from($this->table);
$this->db->where('category_id',$category_id);
$query=$this->db->get();
return $query->row_array();
}
public function check_category_exsits($category_name,$parent_id){
$this->db->select('*');
$this->db->from($this->table);
$this->db->where('parent_id',$parent_id);
$this->db->where('category_name',$category_name);
$query=$this->db->get();
if($query->result())
{
return 1;
}else{
return 0;
}
}
public function delete($condition){
$this->db->delete($this->table,$condition);
return 1;
}
public function active($condition,$data){
$this->db->where($condition);
$this->db->update($this->table,$data);
return 1;
}
public function getRow($table,$condition){
$this->db->where($condition);
$query=$this->db->get($table);
return $query->row_array();
}
public function getDetails($table,$condition){
$this->db->where($condition);
$query=$this->db->get($table);
return $query->result_array();
}
public function set_active_inactive_car($car_id){
$query="UPDATE car SET status =(CASE WHEN status = 1 THEN 0 ELSE 1 END),updated_by = 0,date_of_update='".date('Y-m-d H:i:s')."' WHERE car_id='".$car_id."'";
$run_query= $this->db->query($query);
return ($run_query)?true:false;
}
public function getServices($job_id){
$this->db->from("job_services");
$this->db->join('service','service.service_id=job_services.service_id');
$this->db->where('job_id',$job_id);
$query=$this->db->get();
return $query->result_array();
}
public function getRowA($table,$condition){
$this->db->where($condition);
$this->db->order_by('assign_job_id', 'DESC');
$this->db->limit(1);
$query=$this->db->get($table);
return $query->row_array();
}
}
?>