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

class Mtyreservicereport extends CI_Model
{
    var $table = 'job';
    var $column_order = array('', 'car.car_no', 'job_service_date.service_date', 'admins.name', null); //set column field database for datatable orderable
    var $column_search = array('car.car_no', 'DATE_FORMAT(job_service_date.service_date, "%d/%m/%Y")', 'admins.name'); //set column field database for datatable searchable just firstname , lastname , address are searchable
    // var $order = array('job_service_date_id' => 'desc'); // default order 
    public function __construct()
    {
        parent::__construct();
    }
    private function _get_datatables_query($start_date, $end_date, $car_id = null, $task_id = null)
    {
        $this->db->select('*,DATE_FORMAT(job_service_date.service_date, "%d/%m/%Y") service_date');
        $this->db->from($this->table);
        $this->db->join('job_service_date', 'job_service_date.job_id = job.job_id', 'left');
        $this->db->join('assign_job', 'assign_job.job_id = job.job_id', 'left');
        $this->db->join('mechanic_job_task', 'mechanic_job_task.job_id = job.job_id', 'left');
        $this->db->join('car', 'car.car_id = job.car_id', 'left');
        $this->db->join('admins', 'admins.admin_id = assign_job.mechanic_id', 'left');
        if ($car_id != 0) {
            $this->db->where("job.car_id", $car_id);
        }
        $this->db->where("job.job_status", 2);
        if ($task_id != 0) {
            $this->db->where("mechanic_job_task.service_id", 11);
            $this->db->where("mechanic_job_task.task_id", $task_id);
        } else {
            $this->db->where("mechanic_job_task.service_id", 11);
        }
        $this->db->where("assign_job.assign_status", 1);
        $this->db->where("job_service_date.service_date>=", $start_date);
        $this->db->where("job_service_date.service_date<=", $end_date);

        $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($start_date, $end_date, $car_id, $task_id)
    {
        $this->_get_datatables_query($start_date, $end_date, $car_id, $task_id);
        if ($_POST['length'] != -1)
            $this->db->limit($_POST['length'], $_POST['start']);
        $query = $this->db->get();
        //echo $this->db->last_query(); exit() ; 
        return $query->result();
    }

    public function count_filtered($start_date, $end_date, $car_id, $task_id)
    {
        $this->_get_datatables_query($start_date, $end_date, $car_id, $task_id);
        $query = $this->db->get();
        return $query->num_rows();
    }

    public function count_all($start_date, $end_date, $car_id = null, $task_id = null)
    {
        $this->db->select('*,DATE_FORMAT(job_service_date.service_date, "%d/%m/%Y") service_date');
        $this->db->from($this->table);
        $this->db->join('job_service_date', 'job_service_date.job_id = job.job_id', 'left');
        $this->db->join('assign_job', 'assign_job.job_id = job.job_id', 'left');
        $this->db->join('mechanic_job_task', 'mechanic_job_task.job_id = job.job_id', 'left');
        $this->db->join('car', 'car.car_id = job.car_id', 'left');
        $this->db->join('admins', 'admins.admin_id = assign_job.mechanic_id', 'left');
        if ($car_id != 0) {
            $this->db->where("job.car_id", $car_id);
        }
        $this->db->where("job.job_status", 2);
        if ($task_id != 0) {
            $this->db->where("mechanic_job_task.service_id", 11);
            $this->db->where("mechanic_job_task.task_id", $task_id);
        } else {
            $this->db->where("mechanic_job_task.service_id", 11);
        }
        $this->db->where("assign_job.assign_status", 1);
        $this->db->where("job_service_date.service_date>=", $start_date);
        $this->db->where("job_service_date.service_date<=", $end_date);
        return $this->db->count_all_results();
    }

    public function get_details($job_id)
    {
        $this->db->select('*');
        $this->db->from('job_services');
        $this->db->join('service', 'service.service_id = job_services.service_id', 'left');
        $this->db->where('job_id', $job_id);
        $query = $this->db->get();
        return $query->result_array();
    }

    public function get_user_slots($user_id)
    {
        $this->db->from('user_time_slot');
        $this->db->where('user_id', $user_id);
        $query = $this->db->get();
        return $query->result_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);
        return $this->db->insert_id();
    }

    public function add_payment($table, $data)
    {
        $this->db->insert($table, $data);
        return $this->db->insert_id();
    }

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

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

    public function delete($condition)
    {
        $this->db->delete($this->table, $condition);
        return 1;
    }

    public function delete_cg_work_types($condition)
    {
        $this->db->delete('caregiver_work_expertise', $condition);
        return 1;
    }

    public function delete_paypal_device_details($condition)
    {
        $this->db->delete('paypal_device_details', $condition);
        return 1;
    }

    public function active($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('categories');
        $this->db->where('parent_id=0');
        $query = $this->db->get();
        return $query->result_array();
    }

    public function get_cities()
    {
        $this->db->select('*');
        $this->db->from('city');
        $query = $this->db->get();
        return $query->result_array();
    }

    public function get_sub_category($category_id)
    {
        $this->db->select('*');
        $this->db->from('categories');
        $this->db->where('parent_id', $category_id);
        $query = $this->db->get();
        return $query->result_array();
    }

    public function get_sub_category_by_category_id($category_id)
    {
        $this->db->select('*');
        $this->db->from('categories');
        $this->db->where('category_id', $category_id);
        $query = $this->db->get();
        return $query->row_array();
    }

    public function get_parent_category_by_parent_id($parent_id)
    {
        $this->db->select('*');
        $this->db->from('categories');
        $this->db->where('category_id', $parent_id);
        $query = $this->db->get();
        return $query->row_array();
    }

    public function get_sub_categories_by_parent_id($parent_id)
    {
        $this->db->select('*');
        $this->db->from('categories');
        $this->db->where('parent_id', $parent_id);
        $query = $this->db->get();
        return $query->result_array();
    }

    public function get_category_by_id($category_id)
    {
        $this->db->select('*');
        $this->db->from('categories');
        $this->db->where('category_id', $category_id);
        $query = $this->db->get();
        return $query->row_array();
    }

    public function get_city_by_id($city_id)
    {
        $this->db->select('*');
        $this->db->from('city');
        $this->db->where('city_id', $city_id);
        $query = $this->db->get();
        return $query->row_array();
    }

    public function getRows($table, $condition, $order_col = null, $order_type = null)
    {
        $this->db->where($condition);

        if (isset($limit)) {
            $this->db->limit($limit);
        }
        if (!empty($order_col) && !empty($order_type)) {
            $this->db->order_by($order_col, $order_type);
        }
        $query = $this->db->get($table);
        return $query->result_array();
    }

    public function getRow($table, $condition)
    {
        $this->db->where($condition);
        $query = $this->db->get($table);
        return $query->row_array();
    }

    public function get_company_detail($condition)
    {
        $this->db->select('*');
        $this->db->from('companys');
        $this->db->join('company_type', 'companys.company_type_id = company_type.company_type_id', 'left');
        $this->db->where($condition);
        $query = $this->db->get();
        return $query->row_array();
    }

    public function get_work_type($admin_id)
    {
        $this->db->select('*');
        $this->db->from('caregiver_work_expertise');
        $this->db->join('work_types', 'caregiver_work_expertise.work_type_id = work_types.work_type_id', 'left');
        $this->db->where("care_giver_id", $admin_id);
        $query = $this->db->get();
        return $query->result_array();
    }

    public function get_transaction_report($driver_id, $is_paid, $start_date, $end_date)
    {
        $this->db->select('driver_customer_ride.*,users.driver_dc');
        $this->db->from($this->table);
        $this->db->join('users', 'users.user_id = driver_customer_ride.driver_id', 'left');
        $this->db->where('is_paid', $is_paid);
        $this->db->where("DATE_FORMAT(date_time,'%Y-%m-%d')>=", $start_date);
        $this->db->where("DATE_FORMAT(date_time,'%Y-%m-%d')<=", $end_date);
        if ($driver_id != '') {
            $this->db->where('driver_id', $driver_id);
        }

        $query = $this->db->get();
        return $query->result_array();
    }
}