<?php

class Vehicle {

    protected $info = array();
    protected $AllAccounts = array();
    private $db;
    private $year_id;
    private $commonACC;

    public function __construct() {
        $this->db = DBConnectorFactory::loadDriver();
        $this->year_id = Session::getCurrentYearID();
        $this->commonACC = new CommonACC();
    }

    public function setData($data) {
        $this->info = $data;

        return $this;
    }

    public function addVehicle() {
        $this->db->query("START TRANSACTION");
        $date_time = date('Y-m-d h:i:s');
        $data = [&$this->info['Vehicle_Code'],
            &$this->info['Vehicle_Name'],
            &$this->info['rent_type_id'],
            &$this->info['insurance_date'],
            &$this->info['Vehicle_Make_Name_ID'],
            &$this->info['Vehicle_Model_ID'],
            &$this->info['Vehicle_Color_ID'],
            &$this->info['Vehicle_Registration_Number'],
            &$this->info['Vehicle_Fuel_Tank_Volume'],
            &$this->info['Vehicle_Fuel_Type_ID'],
            &$this->info['Vehicle_Usage_Type_ID'],
            &$this->info['Vehicle_Maintenance_Period'],
            &$this->info['Vehicle_Start_Usage_Date'],
            &$this->info['Vehicle_Tracking_Number'],
            &$this->info['Vehicle_Fuel_Consumption'],
            &$this->info['Vehicle_Oil_Change'],
            &$this->info['Vehicle_Mecanique'],
            &$this->info['Vehicle_Zone_ID'],
            &$this->info['driver_name'],
            &$this->info['driver_phone'],
            &$this->info['owner_name'],
            &$this->info['note'],
            &$this->info['insurance_company'],
            &$this->info['vehicle_year'],
            &$this->info['driver_emp_id'],
            &$date_time,
            &$this->info[0]
        ];

        $this->db->prepare_Stmt('INSERT INTO tbl_garage_vehicle
                                            (
                                               Vehicle_Code,
                                               Vehicle_Name,
                                               rent_type_id,
                                               insurance_date,
                                               Vehicle_Make_Name_ID,
                                               Vehicle_Model_ID,
                                               Vehicle_Color_ID,
                                               Vehicle_Registration_Number,
                                               Vehicle_Fuel_Tank_Volume,
                                               Vehicle_Fuel_Type_ID,
                                               Vehicle_Usage_Type_ID,
                                               Vehicle_Maintenance_Period,
                                               Vehicle_Start_Usage_Date,
                                               Vehicle_Tracking_Number,
                                               Vehicle_Fuel_Consumption,
                                               Vehicle_Oil_Change,
                                               mecanique_month,
                                               zone_id,
                                               driver_name,
                                               driver_phone,
                                               owner_name,
                                               note,
                                               insurance_company,
                                               vehicle_year,
                                               driver_emp_id,
                                               date_time,
                                               logged_id

                                            )
                                                VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', $data);

        if (smc_session()['default_setting']['activate_vehicle_job'] == 1) {
            $vehicle_id = $this->db->id();
            $job_code = $this->commonACC->get_next_job_code(0);
            $this->db->prepare_Stmt('INSERT INTO tbl_accounting_jobs
                                            (job_name,job_code,parent_id,job_main_type,job_main_id)
                                                VALUES(?,?,0,"veh",?)', [&$this->info['Vehicle_Name'], &$job_code,
                &$vehicle_id]);
        }


        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function updateVehicle() {
        $this->db->query("START TRANSACTION");
        $data = [&$this->info['Vehicle_Code'],
            &$this->info['Vehicle_Name'],
            &$this->info['rent_type_id'],
            &$this->info['insurance_date'],
            &$this->info['Vehicle_Make_Name_ID'],
            &$this->info['Vehicle_Model_ID'],
            &$this->info['Vehicle_Color_ID'],
            &$this->info['Vehicle_Registration_Number'],
            &$this->info['Vehicle_Fuel_Tank_Volume'],
            &$this->info['Vehicle_Fuel_Type_ID'],
            &$this->info['Vehicle_Usage_Type_ID'],
            &$this->info['Vehicle_Maintenance_Period'],
            &$this->info['Vehicle_Start_Usage_Date'],
            &$this->info['Vehicle_Tracking_Number'],
            &$this->info['Vehicle_Fuel_Consumption'],
            &$this->info['Vehicle_Oil_Change'],
            &$this->info['driver_emp_id'],
            &$this->info['driver_name'],
            &$this->info['driver_phone'],
            &$this->info['owner_name'],
            &$this->info['note'],
            &$this->info['insurance_company'],
            &$this->info['vehicle_year'],
            &$this->info['Vehicle_Mecanique'],
            &$this->info['Vehicle_ID']
        ];
        $this->db->prepare_Stmt('UPDATE
                                       tbl_garage_vehicle
                                    SET
                                       Vehicle_Code                  =  ?,
                                       Vehicle_Name                  =  ?,
                                       rent_type_id                  =  ?,
                                       insurance_date                =  ?,
                                       Vehicle_Make_Name_ID          =  ?,
                                       Vehicle_Model_ID              =  ?,
                                       Vehicle_Color_ID              =  ?,
                                       Vehicle_Registration_Number   =  ?,
                                       Vehicle_Fuel_Tank_Volume      =  ?,
                                       Vehicle_Fuel_Type_ID          =  ?,
                                       Vehicle_Usage_Type_ID         =  ?,
                                       Vehicle_Maintenance_Period    =  ?,
                                       Vehicle_Start_Usage_Date      =  ?,
                                       Vehicle_Tracking_Number       =  ?,
                                       Vehicle_Fuel_Consumption      =  ?,
                                       Vehicle_Oil_Change            =  ?,
                                       driver_emp_id                 =  ?,
                                       driver_name                   =  ?,
                                       driver_phone                  =  ?,
                                       owner_name                    =  ?,
                                       note                          =  ?,
                                       insurance_company             =  ?,
                                       vehicle_year                  =  ?,
									   mecanique_month               =  ?
                                    WHERE
                                        Vehicle_ID                   = ? ', $data);
        if (smc_session()['default_setting']['activate_vehicle_job'] == 1) {
            $this->db->prepare_Stmt('UPDATE tbl_accounting_jobs  set job_name = ? where job_main_type ="veh" and job_main_id= ? ', [&$this->info['Vehicle_Name'], &$this->info['Vehicle_ID']]);
        }


        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function getVehicleInfo($VID) {
        $data = array();
        $query = $this->db->query('SELECT
                                        v.*,
                                        c.Color_Name,
                                        f.Fuel_Type_Name,
                                        u.Usage_Type_Name,
                                        m.Model_Name,
                                        t.Vehicle_Type_Name,
                                        z.Zone_Name,
                                        concat_ws(" ",e.Employee_FirstName,e.Employee_MiddleName,e.Employee_LastName) as Employee_Name,
                                        rent_type.rent_type_name,
                                        rent_type.rent_type_price,
                                        rent_type.currency_id
                                    FROM
                                        tbl_garage_vehicle v
                                    LEFT JOIN
                                        tbl_color c
                                    ON
                                        c.Color_ID = v.Vehicle_Color_ID
                                    LEFT JOIN
                                        tbl_fuel_type f
                                    ON
                                        f.Fuel_Type_ID = v.Vehicle_Fuel_Type_ID
                                   LEFT JOIN
                                        tbl_vehicle_usage_type u
                                    ON
                                        u.Usage_Type_ID = v.Vehicle_Usage_Type_ID
                                    LEFT JOIN
                                        tbl_vehicle_type t
                                    ON
                                        t.Vehicle_Type_ID = v.Vehicle_Make_Name_ID
                                    LEFT JOIN
                                        tbl_vehicle_model m
                                    ON
                                        m.Model_ID = v.Vehicle_Model_ID
                                    LEFT JOIN
                                        tbl_garage_vehicle_zone z
                                    ON
                                        z.Zone_ID = v.zone_id
                                    LEFT JOIN
                                        tbl_hrm_employee e
                                    ON
                                        e.Employee_ID = v.driver_emp_id
                                    LEFT JOIN
                                        tbl_garage_rent_type rent_type
                                    ON
                                        rent_type.rent_type_id = v.rent_type_id
                                    WHERE
                                        v.Vehicle_ID = "' . $VID . '"
                    ');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function addTruckExpenses() {
        $flag = $this->info['is_actual'];
        $check = CommonFunctions::check_lock_data(['type' => 'vehicle_expense', 'start_date' => $this->info['Expense_Date'], 'is_actual' => $flag]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $jv = JV::returnJV($flag);
        $this->db->query("START TRANSACTION");
        $supp_acc = Supplier::get_all_aupp_accs($this->info['Supplier_ID']);
        $total_exp = 0;
        $total_exp_vat = 0;
        //        $total_fa = 0;
        //        $total_fa_vat = 0;
        //        $total_fa_paid = 0;
        $total_exp_paid = 0;
        $expense_nb = $this->get_max_exp_nb($flag);
        $logged_id = smc_session()['user_logged_id'];
        $date_time = date('Y-m-d H:i:s');
        $payment_acc_id = !empty($this->info['pay_method_acc_id']) ? $this->info['pay_method_acc_id'] : smc_session()['user_accounts']['cash_account_id'];
        $year_id = $flag ? $this->year_id_actual : $this->year_id;
        //        $paid_amount = $this->info['Cash_Amount'] + $this->info['Check_Amount'] + $this->info['Card_Amount'];
        $rate = !empty($this->info['Currency_Rate']) ? $this->info['Currency_Rate'] : CurrencyFactory::getLastExRate($this->info['Currency_ID']);
        $second_rate = CurrencyFactory::getSecondCurrencyLastExRate($this->info['Currency_ID']);
        $exp_data = [&$expense_nb, &$this->info['Expense_Date'], &$this->info['Expense_DueDate'], &$this->info['expense_no_vat'],
            &$this->info['Currency_ID'], &$rate, &$this->info['Expense_Note'], &$logged_id, &$this->info['Supplier_ID'],
            &$this->info['Expense_VAT'], &$flag, &$year_id, &$this->info['Supplier_Invoice'],
            &$this->info['branch_id'], &$this->info['job_id'], &$payment_acc_id, &$second_rate, &$this->info['project_id'],
            &$logged_id, &$date_time, &$date_time];
        $this->db->prepare_Stmt('insert into tbl_garage_truck_expenses (expense_nb,`Truck_Expense_Date`,Expense_DueDate,`Truck_Expense_Amount`,
            Currency_ID,current_rate,`Truck_Expense_Description`,Logged_ID,Supplier_ID,
            Expense_VAT,Is_Hidden,current_year_id,supplier_inv_id,branch_id,job_id,pay_method_acc_id,
            second_currency_rate,project_id,created_logged_id,date_created,date_time
           ) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', $exp_data);
        $exp_id = $this->db->id();


        foreach ($this->info['expense'] as $exp) {
            $exp_det = [&$exp_id, &$exp['exp_acc'], &$exp['exp_qty'], &$exp['unit_cost'], &$exp['vat_percent'],
                &$exp['vat_amount'], &$exp['total'], &$exp['exp_desc'], &$exp['paid_amount'], &$exp['truck_id']];

            $this->db->prepare_Stmt('insert into tbl_garage_truck_expenses_details 
                (`Expense_ID`, `Account_ID`, `quantity`, `unit_price`, `vat_percent`, `vat_amount`,
                `net_total`, `description`,`paid_amount`,truck_id) values(?,?,?,?,?,?,?,?,?,?)', $exp_det);

            $total_exp +=($exp['total'] + $exp['vat_amount']);
            $total_exp_vat +=$exp['vat_amount'];
            $total_exp_paid +=$exp['paid_amount'];

            //exp row transaction
            $data_1 = array(
                "JV_ID" => $jv,
                "Transaction_Reference" => $exp_id,
                "Transaction_Account" => $exp['exp_acc'],
                "Transaction_Type" => "Debit",
                "Transaction_Amount" => $exp['total'],
                "Transaction_Date" => $this->info['Expense_Date'],
                "Currency_ID" => $this->info['Currency_ID'],
                "Logged_ID" => $logged_id,
                "Transaction_Description" => $exp['exp_desc'],
                "Current_Ex_Rate" => $rate,
                "second_currency_rate" => $second_rate,
                "main_id" => $exp_id,
                "main_type" => 'vex',
                "branch_id" => $this->info['branch_id'],
                "main_nb" => $expense_nb,
                "due_date" => $this->info['Expense_Date'],
                "job_id" => $this->info['job_id'],
                "project_id" => $this->info['project_id']
            );
            JV::addTransaction($data_1, $flag);
        }
        //          $error = $this->db->error();
        ////        echo $exp_id.'==';
        ////        echo"<pre>";
        //        die(print_r($error));
        //        echo $total_exp_paid.'=='.$total_fa_paid;
        //        die();
        //        
        $data_2 = array(
            "JV_ID" => $jv,
            "Transaction_Reference" => $exp_id,
            "Transaction_Account" => $supp_acc['exp'],
            "Transaction_Type" => "Credit",
            "Transaction_Amount" => $total_exp,
            "Transaction_Date" => $this->info['Expense_Date'],
            "Currency_ID" => $this->info['Currency_ID'],
            "Logged_ID" => $logged_id,
            "Transaction_Description" => $this->info['Expense_Note'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $exp_id,
            "main_type" => 'vex',
            "branch_id" => $this->info['branch_id'],
            "job_id" => $this->info['job_id'],
            "main_nb" => $expense_nb,
            "due_date" => $this->info['Expense_Date'],
            "project_id" => $this->info['project_id']
        );
        JV::addTransaction($data_2, $flag);
        $data_3 = array(
            "JV_ID" => $jv,
            "Transaction_Reference" => $exp_id,
            "Transaction_Account" => $supp_acc['exp_vat'],
            "Transaction_Type" => "Debit",
            "Transaction_Amount" => $total_exp_vat,
            "Transaction_Date" => $this->info['Expense_Date'],
            "Currency_ID" => $this->info['Currency_ID'],
            "Logged_ID" => $logged_id,
            "Transaction_Description" => $this->info['Expense_Note'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $exp_id,
            "main_type" => 'vex',
            "branch_id" => $this->info['branch_id'],
            "main_nb" => $expense_nb,
            "due_date" => $this->info['Expense_Date'],
            "job_id" => $this->info['job_id'],
            "project_id" => $this->info['project_id']
        );
        JV::addTransaction($data_3, $flag);

        $data_6 = array(
            "JV_ID" => $jv,
            "Transaction_Reference" => $exp_id,
            "Transaction_Account" => $payment_acc_id,
            "Transaction_Type" => "Credit",
            "Transaction_Amount" => $this->info['total_paid'],
            "Transaction_Date" => $this->info['Expense_Date'],
            "Currency_ID" => $this->info['Currency_ID'],
            "Logged_ID" => $logged_id,
            "Transaction_Description" => $this->info['Expense_Note'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $exp_id,
            "main_type" => 'vex',
            "branch_id" => $this->info['branch_id'],
            "job_id" => $this->info['job_id'],
            "main_nb" => $expense_nb,
            "due_date" => $this->info['Expense_Date'],
            "project_id" => $this->info['project_id']
        );
        JV::addTransaction($data_6, $flag);
        $data_7 = array(
            "JV_ID" => $jv,
            "Transaction_Reference" => $exp_id,
            "Transaction_Account" => $supp_acc['exp'],
            "Transaction_Type" => "Debit",
            "Transaction_Amount" => $total_exp_paid,
            "Transaction_Date" => $this->info['Expense_Date'],
            "Currency_ID" => $this->info['Currency_ID'],
            "Logged_ID" => $logged_id,
            "Transaction_Description" => $this->info['Expense_Note'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $exp_id,
            "main_type" => 'vex',
            "branch_id" => $this->info['branch_id'],
            "job_id" => $this->info['job_id'],
            "main_nb" => $expense_nb,
            "due_date" => $this->info['Expense_Date'],
            "project_id" => $this->info['project_id']
        );
        JV::addTransaction($data_7, $flag);

        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    //    public function addTruckExpenses() {
    //
    //        $this->db->query('START TRANSACTION');
    //        foreach ($this->info['Expenses'] as $item) {
    //            $check = JV::checkCurrentYear($item['Truck_Expense_Date'], $item['Truck_Expense_Date'], 0);
    //            if ($check !== '') {
    //                $error = array();
    //                $error[0] = $check;
    //                $this->db->query('ROLLBACK');
    //                return $error;
    //            }
    //            $currency = array_key_exists('ExpenseCurrency_ID', $item) ? $item['ExpenseCurrency_ID'] : $this->info['Currency_ID'];
    //            $data = [&$item['Truck_Code'],
    //                     &$item['Truck_Expense_Description'],
    //                     &$item['Truck_Expense_Amount'],
    //                     &$currency,
    //                     &$item['Truck_Expense_Date'],
    //                     &$item['Truck_Expense_Account'],
    //                     &$this->info[0],
    //                     &$this->year_id];
    //            $this->db->prepare_Stmt('INSERT INTO
    //                                            tbl_garage_truck_expenses (
    //                                                Truck_ID,
    //                                                Truck_Expense_Description,
    //                                                Truck_Expense_Amount,
    //                                                Currency_ID,
    //                                                Truck_Expense_Date,
    //                                                Truck_Expense_Account,
    //                                                Logged_ID,
    //                                                current_year_id
    //                                            )
    //                                            VALUES (?,?,?,?,?,?,?,?)', $data);
    //
    //            $exp_id = $this->db->id();
    //            $jv = JV::returnJV();
    //            $data_1 = array(
    //                "JV_ID" => $jv,
    //                "Transaction_Reference" => $exp_id,
    //                "Transaction_Account" => $item['Truck_Expense_Account'],
    //                "Transaction_Type" => "Debit",
    //                "Transaction_Amount" => $item['Truck_Expense_Amount'],
    //                "Transaction_Date" => $item['Truck_Expense_Date'],
    //                "Currency_ID" => (array_key_exists('ExpenseCurrency_ID', $item) ? $item['ExpenseCurrency_ID'] : $this->info['Currency_ID']),
    //                "Logged_ID" => $this->info[0],
    //                "Transaction_Description" => $item['Truck_Expense_Description'],
    //                "Current_Ex_Rate" => "0",
    //                "main_id" => $exp_id,
    //                "main_type" => "vex"
    //            );
    //            JV::addTransaction($data_1);
    //            $data_2 = array(
    //                "JV_ID" => $jv,
    //                "Transaction_Reference" => $exp_id,
    //                "Transaction_Account" => "53",
    //                "Transaction_Type" => "Credit",
    //                "Transaction_Amount" => $item['Truck_Expense_Amount'],
    //                "Transaction_Date" => $item['Truck_Expense_Date'],
    //                "Currency_ID" => (array_key_exists('ExpenseCurrency_ID', $item) ? $item['ExpenseCurrency_ID'] : $this->info['Currency_ID']),
    //                "Logged_ID" => $this->info[0],
    //                "Transaction_Description" => $item['Truck_Expense_Description'],
    //                "Current_Ex_Rate" => "0",
    //                "main_id" => $exp_id,
    //                "main_type" => "vex"
    //            );
    //            JV::addTransaction($data_2);
    //        }
    //        $error = $this->db->error();
    //        if (empty($error)) {
    //            $this->db->query('COMMIT');
    //        } else {
    //            $this->db->query('ROLLBACK');
    //        }
    //        return array_unique($error);
    //    }

    public function addVehicleCheckup() {
        $date_time = date('Y-m-d h:i:s');
        $vehicle_id = $this->info['vehicle_id'];
        $vehicle_action = $this->info['vehicle_action'];
        $vehicle_milage = $this->info['vehicle_milage'];
        $data = [
            &$vehicle_id,
            &$this->info['checkup_date'],
            &$this->info['vehicle_milage'],
            &$vehicle_action,
            &$this->info['checkup_note'],
            &$date_time,
            &$this->info[0]
        ];
        if (isset($this->info['vehicle_checkup_id'])) {
            $data[] = &$this->info['vehicle_checkup_id'];
            $this->db->prepare_Stmt('update tbl_vehicle_checkup set vehicle_id = ?,checkup_date = ?,vehicle_milage = ?,vehicle_action = ?,checkup_note = ?,date_time = ?,logged_id = ? where vehicle_checkup_id = ?', $data);
        } else {
            $this->db->prepare_Stmt('Insert into tbl_vehicle_checkup (vehicle_id,checkup_date,vehicle_milage,vehicle_action,checkup_note,date_time,logged_id) values (?,?,?,?,?,?,?)', $data);
        }
        $this->updateVehicleMaintenance($vehicle_id);
        $error = $this->db->error();
        return $error;
    }

    public function ViewVehicleCheckupByID($vehicle_checkup_id) {
        $query = $this->db->prepare_Stmt('select tbl_vehicle_checkup.*,concat(tbl_garage_vehicle.Vehicle_Code,"-",tbl_garage_vehicle.Vehicle_Registration_Number) as vehicle_name from tbl_vehicle_checkup join tbl_garage_vehicle on (tbl_vehicle_checkup.vehicle_id = tbl_garage_vehicle.Vehicle_ID) where tbl_vehicle_checkup.vehicle_checkup_id = ? ', [&$vehicle_checkup_id]);
        $row = $this->db->assoc($query);
        return $row;
    }

    public function deleteVehicleCheckup($vehicle_checkup_id) {
        $query = $this->db->prepare_Stmt('select vehicle_id from tbl_vehicle_checkup where vehicle_checkup_id = ? ', [&$vehicle_checkup_id]);
        $row = $this->db->assoc($query);
        $this->db->prepare_Stmt('delete from  tbl_vehicle_checkup where vehicle_checkup_id = ? ', [&$vehicle_checkup_id]);
        $this->updateVehicleMaintenance($row['vehicle_id']);
        $error = $this->db->error();
        return $error;
    }

    public function addVehicleMecanique() {
        $date_time = date('Y-m-d h:i:s');
        $this->db->query('START TRANSACTION');
        $vehicle_id = $this->info['vehicle_id'];
        $data = [
            &$vehicle_id,
            &$this->info['mecanique_date'],
            &$this->info['mecanique_year'],
            &$this->info['mecanique_note'],
            &$date_time,
            &$this->info[0],
            &$this->info['is_syndicate']
        ];
        if ($this->info['is_syndicate'] == 1) {
            $last_syndicate_date = date('Y-m-d', strtotime($this->info['mecanique_year'] . ' -1 year'));
            $query = $this->db->query('select * from tbl_vehicle_mecanique
                                      where is_syndicate = 1 and vehicle_id = "' . $vehicle_id . '" and mecanique_year > "' . $last_syndicate_date . '" limit 1');
            $count = $this->db->rowsno($query);
            if ($count > 0)
                return [CommonLanguage::getMessage('duplicate_insurance')];
        }
        if (isset($this->info['vehicle_mecanique_id'])) {
            $data[] = &$this->info['vehicle_mecanique_id'];
            $this->db->prepare_Stmt('update tbl_vehicle_mecanique set vehicle_id = ?,mecanique_date = ?,mecanique_year = ?,mecanique_note = ?,date_time = ?,logged_id = ? where vehicle_mecanique_id = ?', $data);
        } else {
            $this->db->prepare_Stmt('Insert into tbl_vehicle_mecanique (vehicle_id,mecanique_date,mecanique_year,mecanique_note,date_time,logged_id,is_syndicate) values (?,?,?,?,?,?,?)', $data);
        }

        $this->updateVehicleMecanique($vehicle_id, $this->info['is_syndicate']);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function ViewVehicleMecaniqueByID($vehicle_mecanique_id) {
        $query = $this->db->prepare_Stmt('select tbl_vehicle_mecanique.*,concat(tbl_garage_vehicle.Vehicle_Code,"-",tbl_garage_vehicle.Vehicle_Registration_Number) as vehicle_name from tbl_vehicle_mecanique join tbl_garage_vehicle on (tbl_vehicle_mecanique.vehicle_id = tbl_garage_vehicle.Vehicle_ID) where tbl_vehicle_mecanique.vehicle_mecanique_id = ? ', [&$vehicle_mecanique_id]);
        $row = $this->db->assoc($query);
        return $row;
    }

    public function deleteVehicleMecanique($vehicle_mecanique_id) {
        $query = $this->db->prepare_Stmt('select vehicle_id,is_syndicate from tbl_vehicle_mecanique where vehicle_mecanique_id = ? ', [&$vehicle_mecanique_id]);
        $row = $this->db->assoc($query);
        $this->db->prepare_Stmt('delete from  tbl_vehicle_mecanique where vehicle_mecanique_id = ? ', [&$vehicle_mecanique_id]);
        $this->updateVehicleMecanique($row['vehicle_id'], $row['is_syndicate']);
        $error = $this->db->error();
        return $error;
    }

    public function updateVehicleMaintenance($vehicle_id) {
        $veh_data = array(0, 0, 0);

        $query = $this->db->prepare_Stmt('SELECT vehicle_action, max(vehicle_milage) as max_milage from tbl_vehicle_checkup where vehicle_id = ? group by vehicle_action', [&$vehicle_id]);
        while ($row = $this->db->assoc($query))
            $veh_data[$row['vehicle_action']] = $row['max_milage'];
        //
        $veh_data[0] = max($veh_data);  //last check is the max. milage
        $this->db->prepare_Stmt('update tbl_garage_vehicle set last_check = ?, last_oil_change = ?, last_maintainence = ? where Vehicle_ID = ?', [&$veh_data[0], &$veh_data[1], &$veh_data[2], &$vehicle_id]);
    }

    public function updateVehicleMecanique($vehicle_id, $type) {
        $last_mec_yr = null;
        $query = $this->db->prepare_Stmt('SELECT mecanique_year from tbl_vehicle_mecanique where vehicle_id = ? and is_syndicate = ? order by mecanique_year desc limit 1', [&$vehicle_id, &$type]);
        $row = $this->db->assoc($query);
        $vehicle_query = $this->db->query('select * from tbl_garage_vehicle where Vehicle_ID = "' . $vehicle_id . '"');
        $vehicle_row = $this->db->assoc($vehicle_query);
        if (!empty($row)) {
            if ($type == 1)
                $last_mec_yr = date('Y-m-d', strtotime('+1 year', strtotime($row['mecanique_year'])));
            else
                $last_mec_yr = $row['mecanique_year'];
        }

        $this->db->prepare_Stmt('update tbl_garage_vehicle set  ' . ($type == 0 ? 'last_mecanique_year' : 'insurance_date') . ' = ? where Vehicle_ID = ?', [&$last_mec_yr, &$vehicle_id]);
    }

    public function truckSearch($term = null, $page, $limit) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' and (Vehicle_Code  LIKE ? OR Vehicle_Name  LIKE ? OR Vehicle_Registration_Number LIKE ? OR Vehicle_Type_Name  LIKE ?) ';
            $data_filter[] = "%" . $term . "%";
            $data_filter[] = "%" . $term . "%";
            $data_filter[] = "%" . $term . "%";
            $data_filter[] = "%" . $term . "%";
        }

        $query = $this->db->prepare_Stmt('SELECT Vehicle_ID as id, CONCAT (Vehicle_Name," - ",Vehicle_Code ) as text  
                from tbl_garage_vehicle
                left join `tbl_vehicle_type` on (tbl_vehicle_type.Vehicle_Type_ID = tbl_garage_vehicle.Vehicle_ID)
                where is_disabled=0 ' . $cond . ' limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function availableCheckoutDatesSearch($term = null, $page, $limit) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' and check_out_date LIKE ? ';
            $data_filter[] = "%" . $term . "%";
        }
        $data_filter[] = $this->info['vehicle_id'];
        $query = $this->db->prepare_Stmt('select 
                                        Entry_ID as id,
                                        check_out_date as text
                                   from tbl_garage_vehicle_checkup
                                   where Vehicle_ID = ?
                                   and check_in = 0
                ' . $cond . ' limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function submitCheckupSetting() {
        $this->db->prepare_Stmt('UPDATE
                                     tbl_garage_vehicle_checkup_setting
                                SET
                                    checkup_setting_title = ?,
                                    checkup_setting_type = ?
                                WHERE
                                    checkup_setting_id = ?
                    ', [&$this->info['checkup_setting_title'],
            &$this->info['checkup_setting_type'],
            &$this->info['checkup_setting_id']]);
        $error = $this->db->error();
        return $error;
    }

    public function getVehicleDetails() {
        $row = array();
        $query = $this->db->query('select check_in_out.*,model.Model_Name,vehicle_type.Vehicle_Type_Name
                               from 
                                     tbl_garage_vehicle_checkup check_in_out 
                                inner join tbl_garage_vehicle vehicle using(Vehicle_ID)
                                inner join tbl_vehicle_type vehicle_type  on vehicle_type.Vehicle_Type_ID = vehicle.Vehicle_Make_Name_ID
                                inner join tbl_vehicle_model model on model.Model_ID = vehicle.Vehicle_Model_ID
                                ');
        $row = $this->db->assoc($query);
        return $row;
    }

    public function getLastOfType($table, $ColName) {
        $query = $this->db->query("SELECT * FROM $table ORDER BY $ColName DESC limit 1");
        $row = $this->db->assoc($query);
        $retrievedData[] = $row;

        return $retrievedData;
    }

    public function addVehicleMakeName() {
        $this->db->prepare_Stmt('INSERT INTO tbl_vehicle_type( Vehicle_Type_Name )VALUES( ? )', [&$this->info['MakeName']]);
        $error = $this->db->error();
        return $error;
    }

    public function addVehicleColor() {
        $this->db->prepare_Stmt('INSERT INTO tbl_color(Color_Name )VALUES( ? )', [&$this->info['ColorID']]);
        $error = $this->db->error();
        return $error;
    }

    public function addVehicleZone() {
        $this->db->prepare_Stmt('INSERT INTO tbl_garage_vehicle_zone ( Zone_Name ) VALUES ( ? )', [&$this->info['ZoneID']]);
        $error = $this->db->error();
        return $error;
    }

    public function addVehicleModel() {
        $this->db->prepare_Stmt('INSERT INTO tbl_vehicle_model(Model_Name )VALUES( ? )', [&$this->info['ModelID']]);
        $error = $this->db->error();
        return $error;
    }

    public function addVehicleFuelType() {
        $this->db->prepare_Stmt('INSERT INTO tbl_fuel_type(Fuel_Type_Name)VALUES( ? )', [&$this->info['FuelTypeID']]);
        $error = $this->db->error();
        return $error;
    }

    public function addVehicleUsageType() {
        $this->db->prepare_Stmt('INSERT INTO tbl_vehicle_usage_type(Usage_Type_Name )VALUES ( ? )', [&$this->info['UsageTypeID']]);
        $error = $this->db->error();
        return $error;
    }

    public function getAllVehicles($AllVehicle = null) {
        $data = array();
        $query = $this->db->query('SELECT
                                        CONCAT(v.Vehicle_Code,t.Vehicle_Type_Name) AS UNLQ,
                                        v.*,
                                        t.*,
                                        m.*

                                    FROM
                                        tbl_garage_vehicle v
                                    LEFT JOIN
                                        tbl_vehicle_type t
                                    ON
                                        t.Vehicle_Type_ID = v.Vehicle_Make_Name_ID
                                    LEFT JOIN
                                        tbl_vehicle_model m
                                    ON
                                        m.Model_ID = v.Vehicle_Model_ID
                                        ');
        $i = 0;
        while ($row = $this->db->assoc($query)) {
            if (!is_null($AllVehicle)) {
                $data[$i][0] = $row['Vehicle_ID'];
                $data[$i][1] = $row['Vehicle_Type_Name'];
                $data[$i][2] = $row['Vehicle_Registration_Number'];
                $data[$i][3] = $row['Model_Name'];
                $data[$i][4] = $row['Vehicle_Oil_Change'] . ' Km';
                $i++;
            } else {
                $data[] = $row;
            }
        }

        return $data;
    }

    //function to get all the vehicles with passing an attribute for the id of the vehicle
    public function getAllVehicles_ID($id = null) {
        $data = array();

        $query = $this->db->query('SELECT
                                        CONCAT(v.Vehicle_Code,t.Vehicle_Type_Name) AS UNLQ,
                                        v.*,
                                        t.*,
                                        m.*

                                    FROM
                                        tbl_garage_vehicle v
                                    LEFT JOIN
                                        tbl_vehicle_type t
                                    ON
                                        t.Vehicle_Type_ID = v.Vehicle_Make_Name_ID
                                    LEFT JOIN
                                        tbl_vehicle_model m
                                    ON
                                        m.Model_ID = v.Vehicle_Model_ID
                                        ' . (is_null($id) ? '' : 'WHERE Vehicle_ID = "' . $id . '"'));
        $result = $this->db->rowsno($query);
        if ($result == 0)
            return false;
        else {
            while ($row = $this->db->assoc($query)) {
                $data[] = $row;
            }
        }
        return $data;
    }

    public function getVehicles() {
        $data = array();

        $query = $this->db->query('SELECT
                                        CONCAT(v.Vehicle_Code,t.Vehicle_Type_Name) AS UNLQ,
                                        v.*,
                                        c.*,
                                        f.*,
                                        u.*,
                                        t.*,
                                        m.*,
                                        z.*,
                                        vd.*
                                    FROM
                                        tbl_garage_vehicle v
                                    LEFT JOIN
                                        tbl_color c
                                    ON
                                        c.Color_ID = v.Vehicle_Color_ID
                                    LEFT JOIN
                                        tbl_fuel_type f
                                    ON
                                        f.Fuel_Type_ID = v.Vehicle_Fuel_Type_ID
                                   LEFT JOIN
                                        tbl_vehicle_usage_type u
                                    ON
                                        u.Usage_Type_ID = v.Vehicle_Usage_Type_ID
                                    LEFT JOIN
                                        tbl_vehicle_type t
                                    ON
                                        t.Vehicle_Type_ID = v.Vehicle_Make_Name_ID
                                    LEFT JOIN
                                        tbl_vehicle_model m
                                    ON
                                        m.Model_ID = v.Vehicle_Model_ID
                                    LEFT JOIN
                                        tbl_garage_vehicle_driver vd
                                    ON
                                        vd.Vehicle_ID = v.Vehicle_ID
                                    LEFT JOIN
                                        tbl_garage_vehicle_zone z
                                    ON
                                        z.Zone_ID = vd.Vehicle_Zone_ID

                                    GROUP BY
                                        UNLQ

                ');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function getAvailableVehicles($id = null) {
        $data = array();
        $conditions = '';
        if (!is_null($id))
            $conditions .= ' AND v.Vehicle_ID = "' . $id . '"';

        $query = $this->db->query('SELECT
                                        CONCAT(v.Vehicle_Code,t.Vehicle_Type_Name) AS UNLQ,
                                        v.*,
                                        d.*,

                                        m.*,
                                        z.*,
                                        t.*,
                                        CONCAT(dr.Employee_FirstName, " ", dr.Employee_MiddleName, " ", dr.Employee_LastName) AS Driver_Name,
                                        CONCAT(s.Employee_FirstName, " ", s.Employee_MiddleName, " ", s.Employee_LastName) AS Sales_Man_Name


                                    FROM
                                        tbl_garage_vehicle_driver d
                                    LEFT JOIN
                                        tbl_garage_vehicle v
                                    ON
                                        d.Vehicle_ID = v.Vehicle_ID
                                    LEFT JOIN
                                        tbl_garage_vehicle_zone z
                                    ON
                                        z.Zone_ID = d.Vehicle_Zone_ID


                                    LEFT JOIN
                                        tbl_vehicle_model m
                                    ON
                                        m.Model_ID = v.Vehicle_Model_ID
                                    LEFT JOIN
                                        tbl_vehicle_type t
                                    ON
                                        t.Vehicle_Type_ID = v.Vehicle_Make_Name_ID
                                    LEFT JOIN
                                        tbl_hrm_employee dr
                                    ON
                                        dr.Employee_ID = d.Driver_ID
                                    LEFT JOIN
                                        tbl_hrm_employee s
                                    ON
                                        s.Employee_ID = d.Sales_Man_ID
                                    WHERE
                                        1=1 ' . $conditions . '


                                    GROUP BY
                                        UNLQ
                                    ORDER BY
                                        z.Zone_ID



                ');

        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }

        return $data;
    }

    public function getAvailableVehiclesForSelect() {
        $data = array();
        $query = $this->db->query('SELECT

                                        CONCAT(v.Vehicle_Code,t.Vehicle_Type_Name) AS UNLQ,
                                        CONCAT(dr.Employee_FirstName, " ", dr.Employee_MiddleName, " ", dr.Employee_LastName) AS Driver_Name,
                                        CONCAT(s.Employee_FirstName, " ", s.Employee_MiddleName, " ", s.Employee_LastName) AS Sales_Man_Name,
                                        v.*,
                                        d.*,
                                        m.*,
                                        z.*,
                                        t.*

                                    FROM
                                        tbl_garage_vehicle_driver d
                                    LEFT JOIN
                                        tbl_garage_vehicle v
                                    ON
                                        d.Vehicle_ID = v.Vehicle_ID
                                    LEFT JOIN
                                        tbl_garage_vehicle_zone z
                                    ON
                                        z.Zone_ID = d.Vehicle_Zone_ID

                                    LEFT JOIN
                                        tbl_vehicle_model m
                                    ON
                                        m.Model_ID = v.Vehicle_Model_ID
                                    LEFT JOIN
                                        tbl_vehicle_type t
                                    ON
                                        t.Vehicle_Type_ID = v.Vehicle_Make_Name_ID
                                    LEFT JOIN
                                        tbl_hrm_employee dr
                                    ON
                                        dr.Employee_ID = d.Driver_ID
                                    LEFT JOIN
                                        tbl_hrm_employee s
                                    ON
                                        s.Employee_ID = d.Sales_Man_ID


                                    GROUP BY
                                        UNLQ
                                    ORDER BY
                                        z.Zone_ID 



                ');

        while ($row = $this->db->assoc($query)) {
            $check = $this->db->query('SELECT
                                            Is_Load,
                                            Loading_Order_ID AS LoadID
                                        FROM
                                            tbl_garage_loading_order 
                                        WHERE
                                            Vehicle_ID = "' . $row['Vehicle_ID'] . '"
                                        ORDER BY
                                            Loading_Order_ID
                                        DESC 
                                        LIMIT 1  

                    ');
            $check_row = $this->db->assoc($check);
            if (count($check_row) > 0) {

                $row['Is_Load'] = $check_row['Is_Load'];
                $row['LoadID'] = $check_row['LoadID'];
            } else
                $row['Is_Load'] = 0;
            $data[] = $row;
        }
        return $data;
    }

    private function VehicleExists() {
        $query = $this->db->query('SELECT * FROM tbl_garage_vehicle WHERE Vehicle_Code = "' . $this->info['Vehicle_Code'] . '" AND Vehicle_Make_Name_ID = "' . $this->info['Vehicle_Make_Name_ID'] . '"');
        $result = $this->db->rowsno($query);
        if ($result != 0)
            return true;
        return false;
    }

    public function getVehicleCode($UsageType) {
        $data = array();
        $query = $this->db->query('SELECT * FROM tbl_garage_vehicle WHERE Vehicle_Usage_Type_ID = "' . $UsageType . '"');
        $result = $this->db->rowsno($query);
        if ($result == 0)
            return array();
        else {


            while ($row = $this->db->assoc($query)) {
                $data[] = $row;
            }
            return $data;
        }
    }

    private function AssignDriverExists() {
        $query = $this->db->query('SELECT * FROM tbl_garage_vehicle_driver WHERE Vehicle_ID = "' . $this->info['Assign_Vehicle_Code'] . '" AND Driver_ID = "' . $this->info['Vehicle_Driver'] . '"AND Sales_Man_ID = "' . $this->info['Vehicle_Sales_Man'] . '"');
        $result = $this->db->rowsno($query);
        if ($result != 0)
            return true;
        return false;
    }

    //    public function getVehicleDriver($vehicleDriver = null) {
    //
    //        $data = array();
    //
    //        $query = $this->db->query('SELECT
    //                                        CONCAT(v.Vehicle_Code,t.Vehicle_Type_Name) AS UNLQ,
    //                                        v.Vehicle_ID AS ID,
    //                                        v.Vehicle_Milage AS Milage,
    //                                        v.*,
    //                                        m.*,
    //                                        t.*
    //                                    FROM
    //                                        tbl_garage_vehicle v
    //
    //                                    LEFT JOIN
    //                                        tbl_vehicle_model m
    //                                    ON
    //                                        m.Model_ID = v.Vehicle_Model_ID
    //                                    LEFT JOIN
    //                                        tbl_vehicle_type t
    //                                    ON
    //                                        t.Vehicle_Type_ID = v.Vehicle_Make_Name_ID
    //                                    GROUP BY
    //                                        UNLQ
    //                ');
    //        $i = 0;
    //        while ($row = $this->db->assoc($query)) {
    //            if (!is_null($vehicleDriver)) {
    //                $data[$i][0] = $row['Vehicle_Registration_Number'];
    //                $data[$i][1] = $row['Vehicle_Oil_Change'];
    //                $i++;
    //            } else {
    //                $check = $this->db->query('SELECT
    //                                            CheckIn,
    //                                            Entry_ID
    //                                        FROM
    //                                            tbl_garage_vehicle_checkup
    //                                        WHERE 
    //                                            Vehicle_ID = "' . $row['ID'] . '"
    //                                        ORDER BY
    //                                            Entry_ID
    //                                        DESC
    //                                        LIMIT 1
    //
    //                                    ');
    //                $ifCheck = $this->db->assoc($check);
    //                $checkLoad = $this->db->query('SELECT
    //                                            Is_Load,
    //                                            Loading_Order_ID AS LoadID
    //                                        FROM
    //                                            tbl_garage_loading_order 
    //                                        WHERE
    //                                            Vehicle_ID = "' . $row['ID'] . '"
    //                                        ORDER BY
    //                                            Loading_Order_ID
    //                                        DESC 
    //                                        LIMIT 1  
    //
    //                    ');
    //                $check_row = $this->db->assoc($checkLoad);
    //                if (count($check_row) > 0) { // if loaded before
    //                    if (count($ifCheck) > 0) { // if it is checked up before
    //                        if ($ifCheck['CheckIn'] == 1 && $check_row['Is_Load'] == 2) //if the last checkup was check in and it is loaded
    //                            $row['Checked'] = $ifCheck['CheckIn']; // can check out
    //                        else
    //                            if ($ifCheck['CheckIn'] == 1 && $check_row['Is_Load'] != 2)// if the last checkup was check in and it is not loaded
    //                                $row['Checked'] = 2; // can not check out
    //                        else
    //                            if ($ifCheck['CheckIn'] == 0)
    //                                $row['Checked'] = $ifCheck['CheckIn']; //can check in
    //                    }
    //                    else {
    //                        if ($check_row['Is_Load'] == 2)
    //                            $row['Checked'] = 1; // if it is not checked up before then check out
    //                        else
    //                            $row['Checked'] = 2;
    //                    }
    //                } else
    //                    $row['Checked'] = 2; // if it is not loaded before can not check out until it validate a load
    //
    //                $data[] = $row;
    //            }
    //        }
    //
    //        return $data;
    //    } 
    public function getVehicleData() {
        $data = array();
        $vehicle_query = $this->db->query('SELECT 
                                            CONCAT_ws(" - ",v.Vehicle_Code,v.Vehicle_Registration_Number,t.Vehicle_Type_Name) AS UNLQ
                                    FROM
                                            tbl_garage_vehicle v
                                    inner JOIN
                                            tbl_vehicle_model m
                                    ON
                                            m.Model_ID = v.Vehicle_Model_ID
                                    inner JOIN
                                            tbl_vehicle_type t
                                    ON
                                            t.Vehicle_Type_ID = v.Vehicle_Make_Name_ID
                                    where v.Vehicle_ID = "' . $this->info['vehicle_id'] . '"');
        $data = $this->db->assoc($vehicle_query);
        return $data;
    }

    public function getCheckupStatusData() {
        $data = array();
        $query = $this->db->query('select
                                        * 
                                   from
                                        tbl_garage_vehicle_checkup_setting
                                   where 
                                        checkup_setting_type IN (' . $this->info['checkup_status'] . ',2)');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    //function that return list of vehicle drivers with the id of the driver, for logging
    public function getVehicleDriver_ID($id = null) {
        $data = array();

        $query = $this->db->query('SELECT
                                        CONCAT(v.Vehicle_Code,t.Vehicle_Type_Name) AS UNLQ,
                                        v.*,
                                        d.*,
                                        m.*,
                                        t.*,
                                        z.*,
                                        CONCAT(dr.Employee_FirstName, " ", dr.Employee_MiddleName, " ", dr.Employee_LastName) AS Driver_Name,
                                        CONCAT(s.Employee_FirstName, " ", s.Employee_MiddleName, " ", s.Employee_LastName) AS Sales_Man_Name


                                    FROM
                                        tbl_garage_vehicle_driver d
                                    LEFT JOIN
                                        tbl_garage_vehicle v
                                    ON
                                        d.Vehicle_ID = v.Vehicle_ID
                                    LEFT JOIN
                                        tbl_vehicle_model m
                                    ON
                                        m.Model_ID = v.Vehicle_Model_ID
                                    LEFT JOIN
                                        tbl_vehicle_type t
                                    ON
                                        t.Vehicle_Type_ID = v.Vehicle_Make_Name_ID
                                    LEFT JOIN
                                        tbl_hrm_employee dr
                                    ON
                                        dr.Employee_ID = d.Driver_ID
                                    LEFT JOIN
                                        tbl_hrm_employee s
                                    ON
                                        s.Employee_ID = d.Sales_Man_ID
                                    LEFT JOIN
                                        tbl_garage_vehicle_zone z
                                    ON
                                        z.Zone_ID = d.Vehicle_Zone_ID                                    
                                    WHERE
                                        d.Vehicle_Driver_ID = "' . $id . '"
                ');
        $result = $this->db->rowsno($query);
        if ($result == 0)
            return false;
        else {
            while ($row = $this->db->assoc($query)) {
                $data[] = $row;
            }
        }
        return $data;
    }

    /* End of getVehicleDriver_ID function! */

    public function addVehicleDriver() {
        $data = [&$this->info['Assign_Vehicle_Code'],
            &$this->info['Vehicle_Driver'],
            &$this->info['Vehicle_Sales_Man'],
            &$this->info['Vehicle_Zone_ID']];
        $this->db->prepare_Stmt('INSERT INTO tbl_garage_vehicle_driver
                                            (

                                                Vehicle_ID,
                                                Driver_ID,
                                                Sales_Man_ID,
                                                Vehicle_Zone_ID
                                            )
                                                VALUES(?,?,?,?)', $data);
        $error = $this->db->error();
        return $error;
    }

    public function addVehicleWeighbridge() {
        $check = CommonFunctions::check_lock_data(['type' => 'weighbridge', 'start_date' => $this->info['Weighbridge_Date'], 'is_actual' => 0]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $this->db->query('START TRANSACTION');
        //1 paid, 2 unpaid
        if ($this->info['Is_Paid'] == 0)
            $this->info['Is_Paid'] = 2;
        $data = [&$this->info['Weighbridge_Vehicle_ID'],
            &$this->info['Weighbridge_Date'],
            &$this->info['Weighbridge_Amount'],
            &$this->info['Currency_ID'],
            &$this->info['Is_Paid'],
            &$this->year_id];
        $this->db->prepare_Stmt('INSERT INTO tbl_garage_vehicle_weighbridge
                                            (

                                                Weighbridge_Vehicle_ID,
                                                WeighBridge_Date,
                                                Weighbridge_Amount,
                                                Currency_ID,
                                                Is_Paid,
                                                current_year_id


                                            )
                                                VALUES(?,?,?,?,?,?)', $data);
        $exp_id = $this->db->id();
        if ($this->info['Is_Paid'] == 1) {
            $desc = $this->info['description'];
            $desc2 = "Vehicle Weighbridge Expense " . $desc;
            $jv = JV::returnJV();

            $data_1 = array(
                "JV_ID" => $jv,
                "Transaction_Reference" => $exp_id,
                "Transaction_Account" => "61185",
                "Transaction_Type" => "Debit",
                "Transaction_Amount" => $this->info['Weighbridge_Amount'],
                "Transaction_Date" => $this->info['Weighbridge_Date'],
                "Currency_ID" => $this->info['Currency_ID'],
                "Logged_ID" => $this->info[0],
                "Transaction_Description" => $desc2,
                "Current_Ex_Rate" => "0",
                "main_id" => $exp_id,
                "main_type" => "wbd"
            );
            JV::addTransaction($data_1);
            $data_2 = array(
                "JV_ID" => $jv,
                "Transaction_Reference" => $exp_id,
                "Transaction_Account" => "53",
                "Transaction_Type" => "Credit",
                "Transaction_Amount" => $this->info['Weighbridge_Amount'],
                "Transaction_Date" => $this->info['Weighbridge_Date'],
                "Currency_ID" => $this->info['Currency_ID'],
                "Logged_ID" => $this->info[0],
                "Transaction_Description" => $desc2,
                "Current_Ex_Rate" => "0",
                "main_id" => $exp_id,
                "main_type" => "wbd"
            );
            JV::addTransaction($data_2);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function getLastExpenseID() {
        $exp = '';
        $query = $this->db->query('SELECT * FROM tbl_accounting_expenses ORDER BY Expense_ID DESC limit 1');
        $row = $this->db->assoc($query);
        $exp = $row['Expense_ID'];
        return $exp;
    }

    public function getVehicleMakeName() {
        $data = array();
        $query = $this->db->query('SELECT * FROM tbl_vehicle_type');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function getVehicleModel() {
        $data = array();
        $query = $this->db->query('SELECT * FROM tbl_vehicle_model');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function getColor() {
        $data = array();
        $query = $this->db->query('SELECT * FROM tbl_color');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function getFuelType() {
        $data = array();
        $query = $this->db->query('SELECT * FROM tbl_fuel_type');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function getUsageType() {
        $data = array();
        $query = $this->db->query('SELECT * FROM tbl_vehicle_usage_type');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function getVehicleZones() {
        $data = array();
        $query = $this->db->query('SELECT * FROM tbl_garage_vehicle_zone');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function getDriver() {
        $data = array();
        $query = $this->db->query('SELECT
                                        e.*,
                                        CONCAT(e.Employee_FirstName, " ", e.Employee_MiddleName, " ", e.Employee_LastName) AS Employee_Name,

                                        p.*,
                                        j.*
                                    FROM
                                        tbl_hrm_employee e

                                    LEFT JOIN
                                        tbl_hrm_paygrade p
                                    ON
                                        p.Paygrade_ID = e.Paygrade_ID

                                    LEFT JOIN
                                        tbl_hrm_jobs j
                                    ON
                                        j.Job_ID = p.Job_ID

                                    WHERE 
                                        j.Job_Name LIKE "driver"
                                    AND 
                                        e.Status_ID = 1
                                    AND 
                                        p.Status_ID = 1 
                                    AND 
                                        j.Status_ID = 1

                ');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function getSalesMan() {
        $data = array();
        $query = $this->db->query('SELECT
                                        e.*,
                                        CONCAT(e.Employee_FirstName, " ", e.Employee_MiddleName, " ", e.Employee_LastName) AS Employee_Name,

                                        p.*,
                                        j.*
                                    FROM
                                        tbl_hrm_employee e

                                    LEFT JOIN
                                        tbl_hrm_paygrade p
                                    ON
                                        p.Paygrade_ID = e.Paygrade_ID

                                    LEFT JOIN
                                        tbl_hrm_jobs j
                                    ON
                                        j.Job_ID = p.Job_ID

                                    WHERE 
                                        j.Job_Name LIKE "sales man" OR "salesman"
                                    AND 
                                        e.Status_ID = 1
                                    AND 
                                        p.Status_ID = 1 
                                    AND 
                                        j.Status_ID = 1
                ');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function getVehicleDriverInfo($code) {
        $data = array();
        $query = $this->db->query('SELECT
                                        v.*,
                                        d.*,
                                        z.*,
                                        e.*,
                                        CONCAT(e.Employee_FirstName, " ", e.Employee_MiddleName, " ", e.Employee_LastName) AS Employee_Name,
                                        p.*,
                                        s.*,
                                        j.*
                                    FROM
                                        tbl_garage_vehicle v
                                    LEFT JOIN
                                        tbl_garage_vehicle_driver d
                                    ON
                                        d.Vehicle_ID = v.Vehicle_ID
                                     LEFT JOIN
                                        tbl_garage_vehicle_zone z
                                    ON
                                        z.Zone_ID = d.Vehicle_Zone_ID
                                    LEFT JOIN
                                        tbl_hrm_employee e
                                    ON
                                        e.Employee_ID = d.Driver_ID
                                    LEFT JOIN
                                        tbl_hrm_shifts s
                                    ON
                                        s.Shift_ID = e.Shift_ID
                                    LEFT JOIN
                                        tbl_hrm_paygrade p
                                    ON
                                        p.Paygrade_ID = e.Paygrade_ID
                                    LEFT JOIN
                                        tbl_hrm_jobs j
                                    ON
                                        j.Job_ID = p.Job_ID

                                    WHERE
                                        j.Job_Name LIKE "driver"

                                        AND v.Vehicle_Code = "' . $code . '"
                    ');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function getVehicleSalesManInfo($code) {
        $data = array();
        $query = $this->db->query('SELECT
                                        v.*,
                                        d.*,
                                        z.*,
                                        e.*,
                                        CONCAT(e.Employee_FirstName, " ", e.Employee_MiddleName, " ", e.Employee_LastName) AS Employee_Name,
                                        p.*,
                                        s.*,
                                        j.*


                                    FROM
                                        tbl_garage_vehicle v
                                    LEFT JOIN
                                        tbl_garage_vehicle_driver d
                                    ON
                                        d.Vehicle_ID = v.Vehicle_ID
                                    LEFT JOIN
                                        tbl_garage_vehicle_zone z
                                    ON
                                        z.Zone_ID = d.Vehicle_Zone_ID
                                    LEFT JOIN
                                        tbl_hrm_employee e
                                    ON
                                        e.Employee_ID = d.Sales_Man_ID
                                    LEFT JOIN
                                        tbl_hrm_shifts s
                                    ON
                                        s.Shift_ID = e.Shift_ID
                                    LEFT JOIN
                                        tbl_hrm_paygrade p
                                    ON
                                        p.Paygrade_ID = e.Paygrade_ID
                                    LEFT JOIN
                                        tbl_hrm_jobs j
                                    ON
                                        j.Job_ID = p.Job_ID

                                    WHERE
                                        v.Vehicle_Code = "' . $code . '"

                                    AND
                                        j.Job_Name LIKE "sales man" OR "salesman"

                    ');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function insertVehicleCheckup() {
        //        $check = self::checkVehicleStatus(); // 0:checkout ,1:checkin
        //        if(empty($check) && $this->info['CheckIn'] == 1){
        //            return ['you_have_check_out_before'];
        //        }else{
        //            if($check['CheckIn'] == $this->info['CheckIn']){
        //                if($this->info['CheckIn'] == 0)
        //                    return ['you_have_check_in'];
        //                if($this->info['CheckIn'] == 1)
        //                    return ['you_have_check_out'];
        //
        //            }
        //            if($this->info['CheckIn']== 1 && $this->info['Chekup_Date'] < $check['Chekup_Date']){
        //                return ['checkin_greater_checkout'];
        //            }
        //        }
        //        die('here');
        $zeros = 0;
        $ones = 0;
        foreach ($this->info as $key => $val) {
            if (substr($key, 0, 2) == 'Is') {
                if ($val == 1)
                    $ones++;
                else
                    $zeros++;
            }
        }
        if ($ones > 0 && $zeros == 0)
            $check = 1;
        elseif ($zeros > 0 && $ones == 0) {
            $check = 3;
            $this->info['New_Maintenance_Status'] = 3;
        } else {
            $check = 2;
            $this->info['New_Maintenance_Status'] = 3;
        }
        $this->db->query('START TRANSACTION');
        $data_arr = [&$this->info['Vehicle_ID'],
            &$this->info['Chekup_Date'],
            &$this->info['Vehicle_Milage'],
            &$this->info['CheckIn'],
            &$this->info['IsJack'],
            &$this->info['IsWheelSpanner'],
            &$this->info['IsSpareWheel'],
            &$this->info['IsFire'],
            &$this->info['IsTriangles'],
            &$this->info['Is_oil_checked'],
            &$this->info['Is_water_checked'],
            &$this->info['IsDriverSide'],
            &$this->info['IsPassengerSide'],
            &$this->info['IsFrontSide'],
            &$this->info['IsBackSide'],
            &$this->info['Checkup_Comment'],
            &$check];
        $this->db->prepare_Stmt('INSERT INTO
                                        tbl_garage_vehicle_checkup
                                        (
                                        Vehicle_ID,
                                        Chekup_Date,
                                        Vehicle_Milage,
                                        CheckIn,
                                        IsJack,
                                        IsWheelSpanner,
                                        IsSpareWheel,
                                        IsFire,
                                        IsTriangles,
                                        is_oil_checked,
                                        is_water_checked,
                                        IsDriverSide,
                                        IsPassengerSide,
                                        IsFrontSide,
                                        IsBackSide,
                                        Checkup_Comment,
                                        Vehicle_Status_ID
                                        )
                                    VALUES
                                        (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', $data_arr);

        $this->db->prepare_Stmt('UPDATE
                                             tbl_garage_vehicle
                                        SET
                                            Vehicle_Status = ?,
                                            Vehicle_Maintenance_Counter = ?,
                                            Vehicle_Milage = ?,
                                            Vehicle_Maintenance_Status = ?

                                        WHERE
                                            Vehicle_ID = ?
                    ', [&$check, &$this->info['New_Counter'], &$this->info['Vehicle_Milage'], &$this->info['New_Maintenance_Status'], &$this->info['Vehicle_ID']]);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function vehicleCheckup() {
        $this->db->query('START TRANSACTION');
        if ($this->info['CheckIn'] == 0) {
            $data_arr = [&$this->info['Vehicle_ID'],
                &$this->info['Checkup_Date'],
                &$this->info['Vehicle_Milage'],
                &$this->info['IsDriverSide'],
                &$this->info['IsPassengerSide'],
                &$this->info['IsFrontSide'],
                &$this->info['IsBackSide'],
                &$this->info['Checkup_Comment']];
            $this->db->prepare_Stmt('INSERT INTO
                                        tbl_garage_vehicle_checkup
                                        (
                                        Vehicle_ID,
                                        check_out_date,
                                        vehicle_mileage_out,
                                        check_out,
                                        is_driver_side_out,
                                        is_passenger_side_out,
                                        is_front_side_out,
                                        is_back_side_out,
                                        check_out_comment
                                        )
                                    VALUES
                                        (?,?,?,1,?,?,?,?,?)', $data_arr);
        } else {
            $data_arr = [&$this->info['Checkup_Date'],
                &$this->info['Vehicle_Milage'],
                &$this->info['IsDriverSide'],
                &$this->info['IsPassengerSide'],
                &$this->info['IsFrontSide'],
                &$this->info['IsBackSide'],
                &$this->info['Checkup_Comment'],
                &$this->info['check_out_entry_id']];
            $this->db->prepare_Stmt('Update 
                                        tbl_garage_vehicle_checkup
                                 set 
                                        check_in_date= ?,
                                        vehicle_mileage_in= ?,
                                        check_in= 1,
                                        is_driver_side_in= ?,
                                        is_passenger_side_in= ?,
                                        is_front_side_in= ?,
                                        is_back_side_in= ?,
                                        check_in_comment = ?
                                   where  Entry_ID = ?', $data_arr);
        }
        foreach ($this->info['checkup_setting'] as $setting) {
            $data_arr = [&$setting['checkup_setting_id'],
                &$setting['checkup_status']];
            $this->db->prepare_Stmt('INSERT INTO
                                        tbl_garage_vehicle_checkup_details
                                        (
                                        checkup_setting_id,
                                        checkup_setting_status
                                        )
                                    VALUES
                                        (?,?)', $data_arr);
        }


        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    //    public function checkVehicleStatus(){
    //        $row = array();
    //        $query = $this->db->query('select * from tbl_garage_vehicle_checkup  where Vehicle_ID= "'.$this->info['Vehicle_ID'].'"');
    //        $row = $this->db->assoc($query);
    //        return $row;
    //    }
    //    public function checkVehicle($sign,$type){
    //        $row = array();
    //        $query = $this->db->query('select * from tbl_garage_vehicle_checkup  where Vehicle_ID= "'.$this->info['Vehicle_ID'].'"  and Chekup_Date '.$sign.' "'.$this->info['Chekup_Date'].'" ORDER BY  '.$type.' limit 1');
    //        $row = $this->db->assoc($query);
    //        return $row;
    //    }


    public function checkMaintenance($vehicle_id) {
        $counter = $this->db->query('SELECT
                                            Vehicle_Maintenance_Counter,Vehicle_Milage,Vehicle_Maintenance_Period,Vehicle_Maintenance_Status
                                        FROM
                                            tbl_garage_vehicle

                                        WHERE
                                            Vehicle_ID = "' . $vehicle_id . '"
            ');
        $row = $this->db->assoc($counter);
        return $row;
    }

    public function viewDailyCheckup($date = null, $year = null, $month = null, $vehicle_id = null, $in = false) {
        $condition = '';
        $data = array();
        if (!is_null($date))
            $condition .= ' AND i.Chekup_Date = "' . $date . '" AND o.Chekup_Date = "' . $date . '"';
        if (!is_null($year))
            $condition .= ' AND YEAR(i.Chekup_Date) = "' . $year . '" AND YEAR(o.Chekup_Date) = "' . $year . '"';
        if (!is_null($month))
            $condition .= ' AND MONTH(i.Chekup_Date) = "' . $month . '" AND MONTH(o.Chekup_Date) = "' . $month . '"';
        if (!is_null($vehicle_id))
            $condition .= ' AND v.Vehicle_ID = "' . $vehicle_id . '"';
        if ($in)
            $condition .= ' AND (v.Vehicle_Fuel_Consumption/100)*(i.Vehicle_Milage-o.Vehicle_Milage)>0 GROUP BY CONCAT(i.Chekup_Date,v.Vehicle_ID)';
        $query = $this->db->query('SELECT
                                    i.Chekup_Date,
                                    v.Vehicle_Code,
                                    v.Vehicle_Registration_Number,
                                    m.Model_Name,
                                    t.Vehicle_Type_Name,
                                    i.Vehicle_Milage AS In_Milage,
                                    o.Vehicle_Milage AS Out_Milage,
                                    (i.Vehicle_Milage-o.Vehicle_Milage) AS Distance,
                                    (v.Vehicle_Fuel_Consumption/100)*(i.Vehicle_Milage-o.Vehicle_Milage) AS Fuel
                                FROM
                                    tbl_garage_vehicle v
                                INNER JOIN
                                    tbl_vehicle_type t
                                ON
                                    v.Vehicle_Make_Name_ID = t.Vehicle_Type_ID
                                INNER JOIN
                                    tbl_vehicle_model m
                                ON
                                    v.Vehicle_Model_ID = m.Model_ID
                                INNER JOIN
                                    tbl_garage_vehicle_checkup i
                                ON
                                    v.Vehicle_ID = i.Vehicle_ID
                                INNER JOIN
                                    tbl_garage_vehicle_checkup o
                                ON
                                    v.Vehicle_ID = o.Vehicle_ID
                                WHERE
                                    i.CheckIn = 1
                                AND
                                    o.CheckIn = 0




        ' . $condition);
        $total = 0;
        while ($row = $this->db->assoc($query)) {
            $total += $row['Fuel'];
            $row['Total_Fuel'] = $total;
            $data [] = $row;
        }

        return $data;
    }

    public function checkInOut($VID, $month = null, $year = null) {
        $data = array();
        $condition = '';
        if (!is_null($month))
            $condition .= ' AND MONTH(c.Chekup_Date) = "' . $month . '"';
        if (!is_null($year))
            $condition .= ' AND YEAR(c.Chekup_Date) = "' . $year . '"';
        $query = $this->db->query('SELECT 
                                        v.Vehicle_Registration_Number,
                                        v.Vehicle_Fuel_Consumption,
                                        c.CheckIn,
                                        c.Chekup_Date,
                                        c.Vehicle_Milage,
                                        c.Entry_ID,
                                        m.Model_Name,
                                        t.Vehicle_Type_Name
                                    FROM
                                        tbl_garage_vehicle_checkup c
                                    LEFT JOIN
                                        tbl_garage_vehicle v
                                    ON 
                                        c.Vehicle_ID = v.Vehicle_ID
                                    LEFT JOIN
                                        tbl_vehicle_type t
                                    ON
                                        v.Vehicle_Make_Name_ID = t.Vehicle_Type_ID
                                    LEFT JOIN
                                        tbl_vehicle_model m
                                    ON
                                        v.Vehicle_Model_ID = m.Model_ID
                                    WHERE
                                        c.Vehicle_ID = "' . $VID . '" 
                                    ' . $condition . '
                                    ORDER BY  
                                        c.Entry_ID ASC
                                ');

        $k = 0; // $k used to check first array to make sure it contains checkout and checkin
        $j = 0; //to ensure checkin and checkout in same array
        if ($this->db->rowsno($query) > 1) {
            while ($row = $this->db->assoc($query)) {

                if ($k == 0) {
                    //first row : if check out then will enter first array check in and checkout else will skip first row
                    if ($row['CheckIn'] == 0) {
                        $data[$j]['checkout'] = $row;
                        $k = 1; //this means there is checkout and the next row will be check in
                    } else
                        $k = 2; // to skip first row since it is not checkout
                }
                else if ($k == 1) {
                    //this means first row is checkout and so the same array will include checkin for this checkout
                    $data[$j]['checkin'] = $row;
                    $data[$j]['distance'] = $row['Vehicle_Milage'] - $data[$j]['checkout']['Vehicle_Milage'];
                    $data[$j]['fuel'] = ($data[$j]['distance'] / 100) * $row['Vehicle_Fuel_Consumption'];
                    $j++;
                    $k = 2; // to proceed with the next rows
                } else {
                    //after finishing first array
                    if ($row['CheckIn'] == 0) {
                        $data[$j]['checkout'] = $row;
                    } else {
                        $data[$j]['checkin'] = $row;
                        $data[$j]['distance'] = $row['Vehicle_Milage'] - $data[$j]['checkout']['Vehicle_Milage'];
                        $data[$j]['fuel'] = ($data[$j]['distance'] / 100) * $row['Vehicle_Fuel_Consumption'];
                        $j++;
                    }
                }
            }


            if (count($data) > 0) {
                if (!array_key_exists('checkin', $data[$j - 1])) {
                    //to make sure the last array contains both 
                    //else it will be removed
                    array_pop($data);
                    $j--;
                }
                $first_checkout = $data[0]['checkout'];
                $last_checkin = $data[$j - 1]['checkin'];
                $total_distance = $last_checkin['Vehicle_Milage'] - $first_checkout['Vehicle_Milage'];
                $total_fuel = $total_distance * $first_checkout['Vehicle_Fuel_Consumption'] / 100;
            }
        } else {
            $total_distance = 0;
            $total_fuel = 0;
        }

        $data['Total_Distance'] = $total_distance;
        $data['Total_Fuel'] = $total_fuel;
        return $data;
    }

    public function viewFuelConsumbtionPerMonth($month, $year) {
        $data = array();
        $total_fuel = 0.0;
        $query = $this->db->query('SELECT DISTINCT Vehicle_ID FROM tbl_garage_vehicle_checkup WHERE MONTH(Chekup_Date) = "' . $month . '" AND YEAR(chekup_Date) = "' . $year . '"');
        while ($row = $this->db->assoc($query)) {
            $temp = array();
            $temp = self::checkInOut($row['Vehicle_ID'], $month, $year);
            //            die(print_r($temp));
            if (count($temp) > 2)
                $data[] = array("Registration_Number" => $temp[0]['checkin']['Vehicle_Registration_Number'],
                    "Model_Name" => $temp[0]['checkin']['Model_Name'],
                    "Vehicle_Type_Name" => $temp[0]['checkin']['Vehicle_Type_Name'],
                    "Fuel_Consumbtion" => $temp['Total_Fuel'],
                    "Distance" => $temp['Total_Distance']
                );
            $total_fuel += $temp['Total_Fuel'];
        }
        $data['Total_Fuel'] = $total_fuel;
        //        die(print_r($data));
        return $data;
    }

    public function getStatusName($code) {
        return StatusCodes::getStatus($code);
    }

    /**
     * Generic function to get an uploaded $_FILE[name_of_file] and save it to a specific path
     * @param [[Type]] $FileName The name of the input[file] tag that is assigned in the client-side
     * @param [[Type]] $SavePath Destination path
     */
    //    public function viewExpenseAccounts() {
    //        $data = array();
    //        $query = $this->db->query('SELECT
    //                                        *
    //                                    FROM
    //                                        tbl_chart_of_accounts
    //                                    WHERE
    //                                        LEFT(Account_Code,4) = 6118
    //                                    AND
    //                                        Account_ID <> 467
    //                ');
    //        while ($row = $this->db->assoc($query))
    //            $data[] = $row;
    //        return $data;
    //    }

    public function updateStatus($status, $id) {
        $this->db->query('START TRANSACTION');
        $this->db->prepare_Stmt('UPDATE
                                    tbl_garage_vehicle
                                SET
                                    Vehicle_Maintenance_Status = ?
                                WHERE
                                    Vehicle_ID = ? ', [&$status, &$id]);
        if ($status == 2 || $status == 1) {
            $this->db->prepare_Stmt('UPDATE
                                        tbl_garage_vehicle
                                    SET
                                        Vehicle_Maintenance_Counter = 0
                                    WHERE
                                        Vehicle_ID = ?', [&$id]);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function getVehicleID() {
        $query = $this->db->query('SELECT
                                      Vehicle_ID
                                   FROM
                                      tbl_garage_vehicle
                                   ORDER BY
                                      Vehicle_ID DESC
                                   LIMIT 1
            ');
        while ($row = $this->db->assoc($query)) {
            $rows = $row['Vehicle_ID'];
        }
        return $rows;
    }

    public function VehicleExpenseList($date) {
        $data = array();
        $query = $this->db->query('SELECT
                                    v.Vehicle_Registration_Number,
                                    c.Currency_Symbol,
                                    t.*,
                                    h.Account_Name
                                FROM
                                    tbl_garage_truck_expenses t
                                LEFT JOIN 
                                    tbl_garage_vehicle v
                                ON 
                                    v.Vehicle_ID = t.Truck_ID
                                LEFT JOIN 
                                    tbl_common_currency c
                                ON 
                                    c.Currency_ID = t.Currency_ID
                                LEFT JOIN 
                                    tbl_chart_of_accounts h
                                ON 
                                    t.Truck_Expense_Account = h.Account_Code
                                WHERE
                                    t.Truck_Expense_Date = "' . $date . '"
        ');
        while ($row = $this->db->assoc($query))
            $data[] = $row;
        return $data;
    }

    public function deleteAssignVehicle($id) {
        $this->db->prepare_Stmt('Delete FROM tbl_garage_vehicle_driver WHERE Vehicle_Driver_ID = ?', [&$id]);
        $error = $this->db->error();
        return $error;
    }

    public function getVehicleDriverToEdit($id = null) {
        $condition = '';
        $data = array();
        if (!is_null($id))
            $condition .= ' WHERE d.Vehicle_Driver_ID = "' . $id . '"';

        $query = $this->db->query('SELECT
                                        v.Vehicle_Code ,
                                        v.Vehicle_ID ,
                                        v.Vehicle_ID,
                                        v.Vehicle_Registration_Number,
                                        d.*,
                                        z.*,
                                        t.Usage_Type_ID,
                                        CONCAT(dr.Employee_FirstName, " ", dr.Employee_MiddleName, " ", dr.Employee_LastName) AS Driver_Name,
                                        CONCAT(s.Employee_FirstName, " ", s.Employee_MiddleName, " ", s.Employee_LastName) AS Sales_Man_Name


                                    FROM
                                        tbl_garage_vehicle_driver d
                                    LEFT JOIN
                                        tbl_garage_vehicle v
                                    ON
                                        d.Vehicle_ID = v.Vehicle_ID
                                    LEFT JOIN
                                        tbl_vehicle_usage_type t 
                                    ON 
                                        t.Usage_Type_ID = v.Vehicle_Usage_Type_ID
                                    LEFT JOIN
                                        tbl_hrm_employee dr
                                    ON
                                        dr.Employee_ID = d.Driver_ID
                                    LEFT JOIN
                                        tbl_hrm_employee s
                                    ON
                                        s.Employee_ID = d.Sales_Man_ID
                                    LEFT JOIN
                                        tbl_garage_vehicle_zone z
                                    ON
                                        z.Zone_ID = d.Vehicle_Zone_ID


                ' . $condition);
        $result = $this->db->rowsno($query);
        if ($result == 0)
            return false;
        else {
            while ($row = $this->db->assoc($query)) {
                $data[] = $row;
            }
        }
        return $data;
    }

    public function updateVehicleDriver($id) {
        $data = [&$this->info['Vehicle_Driver'],
            &$this->info['Vehicle_Sales_Man'],
            &$this->info['Vehicle_Zone_ID'],
            &$this->info['Vehicle_Driver_ID']
        ];
        $this->db->prepare_Stmt('UPDATE tbl_garage_vehicle_driver SET 
                                    Driver_ID = ?,
                                    Sales_Man_ID = ?,
                                    Vehicle_Zone_ID = ?
                                WHERE 
                                    Vehicle_Driver_ID = ?', $data);

        $error = $this->db->error();
        return $error;
    }

    public function getCountAvailableTrucks() {
        $data = array();
        $query = $this->db->query('SELECT COUNT(Vehicle_ID) AS count
                                    FROM
                                        tbl_garage_vehicle
                                    WHERE
                                        Vehicle_Status = 2
                                        ');
        $row = $this->db->assoc($query);
        $data = $row['count'];
        return $data;
    }

    public function getCountTruckToMaintain() {
        $data = array();
        $query = $this->db->query('SELECT COUNT(Vehicle_ID) AS count
                                    FROM
                                        tbl_garage_vehicle
                                    WHERE
                                        Vehicle_Maintenance_Status = 3
                                ');
        $row = $this->db->assoc($query);
        $data = $row['count'];
        return $data;
    }

    public function GetCountLoadedTrucks() {
        $data = array();
        $today = Date::getToday();
        $query = $this->db->query('SELECT
                                        COUNT(Vehicle_ID) AS count
                                    FROM
                                        tbl_garage_loading_order
                                    WHERE
                                        Is_Load = 2
                                    AND 
                                        Loading_Order_Date = "' . $today . '"

                                    ');
        $row = $this->db->assoc($query);
        $data = $row['count'];
        return $data;
    }

    public function getDailyTruckExpense() {
        $data = array();
        $today = Date::getToday();
        $total = 0;
        $query = $this->db->query('SELECT
                                        SUM(Truck_Expense_Amount) AS Total,
                                        Currency_ID
                                    FROM
                                        tbl_garage_truck_expenses
                                    WHERE
                                        Truck_Expense_Date = "' . $today . '"

            ');
        while ($row = $this->db->assoc($query)) {
            $total += CurrencyFactory::returnPriceInDefaultCurrencyValue($row['Currency_ID'], $row['Total']);
        }
        $data['Total_Expense'] = $total;
        return $data;
    }

    public function getTopSalesTruck($year, $month) {
        $data = array();
        $dataValue = array();
        $data_names = array();
        $data_values = array();
        $loads = $this->db->query('SELECT

                                        DISTINCT l.Vehicle_ID,
                                        v.Vehicle_Registration_Number
                                    FROM
                                        tbl_garage_loading_order l
                                    LEFT JOIN
                                            tbl_garage_vehicle v
                                        ON
                                            l.Vehicle_ID = v.Vehicle_ID
                                    WHERE
                                        MONTH(l.Loading_Order_Date) = "' . $month . '"
                                    AND 
                                        YEAR(l.Loading_Order_Date) = "' . $year . '"

                                ');
        while ($row = $this->db->assoc($loads)) {
            $total = 0.0;
            $totalRem = 0.0;
            $vehicle_id = $row['Vehicle_Registration_Number'];
            $query = $this->db->query('SELECT
                                            (r.Product_Qty * p.Product_Price) AS Total,
                                            (r.Total_Remaining_Qty * p.Product_Price) AS Remaining_Total,
                                            p.Currency_ID,
                                            r.Product_Qty,
                                            r.Total_Remaining_Qty,
                                            r.Loading_Order_ID,
                                            p.Product_Price

                                        FROM
                                            tbl_garage_load_returns r
                                        LEFT JOIN
                                            tbl_garage_loading_order l
                                        ON
                                            l.Loading_Order_ID = r.Loading_Order_ID

                                        LEFT JOIN
                                            tbl_stock_products p
                                        ON
                                            p.Product_ID = r.Product_ID
                                        WHERE
                                            l.Vehicle_ID = "' . $row['Vehicle_ID'] . '"
                                        AND 
                                            p.Status_ID = 1
                                        AND 
                                            r.Status_ID =1

                                        ');
            while ($row1 = $this->db->assoc($query)) {
                if (!is_null($row1)) {
                    $total += CurrencyFactory::returnPriceInDefaultCurrencyValue($row1['Currency_ID'], $row1['Total']);
                    $totalRem += CurrencyFactory::returnPriceInDefaultCurrencyValue($row1['Currency_ID'], $row1['Remaining_Total']);
                }
            }
            if ($total > 0) {

                //            $data1['TotalPrice'] = $total;
                //            $data1['RemainingPrice'] = $totalRem;
                $dataVehicle[] = $vehicle_id;
                $dataValue[] = $totalRem / $total * 100;
            }
        }
        arsort($dataValue);
        $sorted_array = array_slice($dataValue, 0, 5, true);
        $sum = array_sum($sorted_array);
        foreach ($sorted_array as $key => $val) {
            $data_values[] = round($val / $sum * 100);
            $data_names[] = $dataVehicle[$key];
        }
        $data = array_merge($data_names, $data_values);

        return $data;
    }

    public function FuelGraph($year = null, $month = null, $day = null, $in = false) {
        $condition = '';
        $data = array();

        if (!is_null($year))
            $condition .= ' AND YEAR(i.Chekup_Date) = "' . $year . '" AND YEAR(o.Chekup_Date) = "' . $year . '"';
        if (!is_null($month))
            $condition .= ' AND MONTH(i.Chekup_Date) = "' . $month . '" AND MONTH(o.Chekup_Date) = "' . $month . '"';
        if (!is_null($day))
            $condition .= ' AND DAY(i.Chekup_Date) = "' . $day . '" AND DAY(o.Chekup_Date) = "' . $day . '"';

        if ($in)
            $condition .= ' AND (v.Vehicle_Fuel_Consumption/100)*(i.Vehicle_Milage-o.Vehicle_Milage)>0 GROUP BY CONCAT(i.Chekup_Date,v.Vehicle_ID)';
        $query = $this->db->query('SELECT


                                    (v.Vehicle_Fuel_Consumption/100)*(i.Vehicle_Milage-o.Vehicle_Milage) AS Fuel
                                FROM
                                    tbl_garage_vehicle_checkup i
                                INNER JOIN
                                    tbl_garage_vehicle v
                                ON
                                    v.Vehicle_ID = i.Vehicle_ID
                                INNER JOIN
                                    tbl_garage_vehicle_checkup o
                                ON
                                    v.Vehicle_ID = o.Vehicle_ID
                                WHERE
                                    i.CheckIn = 1
                                AND
                                    o.CheckIn = 0






        ' . $condition);
        $total = 0;
        while ($row = $this->db->assoc($query)) {
            $total += $row['Fuel'];
        }

        return round($total);
    }

    //    public function truckSearch($term = null, $page, $limit) {
    //        $data = array();
    //        $data['is_more'] = false;
    //        $data['data'] = array();
    //        $cond = '';
    //        if (!is_null($term)) {
    //            $cond .= ' where Vehicle_Registration_Number LIKE "%' . $term . '%" OR Zone_Name  LIKE "%' . $term . '%"';
    //        }
    //
    //        $query = $this->db->query('SELECT Vehicle_ID as id, CONCAT (Vehicle_Registration_Number," - ",Zone_Name ) as text FROM vehicle_driver_list' . $cond . ' limit ' . ($page * $limit) . ',' . ($limit + 1));
    //        while ($row = $this->db->assoc($query)) {
    //            $data['data'][] = $row;
    //        }
    //        if (count($data['data']) > $limit) {
    //            array_pop($data['data']);
    //            $data['is_more'] = true;
    //        }
    //        return $data;
    //    }



    public function get_max_exp_nb($actual) {
        $year = $actual == 1 ? $this->year_id_actual : $this->year_id;
        $query = $this->db->query('SELECT MAX(expense_nb) AS expense_nb FROM  tbl_garage_truck_expenses 
                where current_year_id = "' . $year . '" 
                and Is_Hidden = "' . $actual . '" ');
        $row = $this->db->assoc($query);
        return $row['expense_nb'] + 1;
    }

    public function getVehicleExpenseByID($id) {
        $data = array();
        $exp_query = $this->db->query('select e.*,b.*,c.*,j.*,coa.Account_Name as pay_method,
            pr.project_name
            from tbl_garage_truck_expenses e
            join tbl_organization_branches b on b.branch_id = e.branch_id
            join tbl_common_currency c on c.Currency_ID = e.Currency_ID 
            left join tbl_accounting_jobs j on j.job_id = e.job_id
            left join tbl_projects pr on pr.project_id = e.project_id
            left join tbl_chart_of_accounts coa on coa.Account_ID = e.pay_method_acc_id
            where Truck_Expense_ID = "' . $id . '"');
        $data['Main'] = $this->db->assoc($exp_query);
        //
        $data['Person'] = Supplier::getSupplierByID($data['Main']['Supplier_ID']);
        //
        $det_query = $this->db->query('select d.*,c.Account_Code,c.Account_Name,
            CONCAT (Vehicle_Code," - ",Vehicle_Registration_Number," - ",Vehicle_Type_Name ) as truck_name
            from tbl_garage_truck_expenses_details d
            join tbl_chart_of_accounts c on c.Account_ID = d.Account_ID
            left join tbl_garage_vehicle v on v.Vehicle_ID = d.truck_id
             left join tbl_vehicle_type t on t.Vehicle_Type_ID = v.`Vehicle_Model_ID`
            where d.Expense_ID="' . $id . '" order by d.exp_detail_id');
        while ($row = $this->db->assoc($det_query)) {
            $data['details'][] = $row;
        }

        return $data;
    }

    public function edit_vehicle_expense() {
        //        die('here');
        $flag = $this->info['is_actual'];
        $year_id = $flag ? $this->year_id_actual : $this->year_id;
        $check = CommonFunctions::check_lock_data(['type' => 'vehicle_expense', 'start_date' => $this->info['Expense_Date'], 'is_actual' => $flag]);

//        $check = JV::checkCurrentYear($this->info['Expense_Date'], $this->info['Expense_Date'], $flag);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $jv_query = $this->db->query("SELECT DISTINCT JV_ID FROM tbl_accounting_transactions  
            WHERE main_id='" . $this->info['exp_id'] . "' AND main_type= 'vex' 
                AND current_year_id='" . $year_id . "' and is_actual = '" . $flag . "'");
        $jv_assoc = $this->db->assoc($jv_query);
        if (count($jv_assoc) > 0)
            $jv = $jv_assoc['JV_ID'];
        else
            $jv = JV::returnJV($flag);


        $jv_arr = array();

        $exp_nb_query = $this->db->query('select expense_nb from tbl_garage_truck_expenses where Truck_Expense_ID = "' . $this->info['exp_id'] . '"');
        $exp_nb = $this->db->assoc($exp_nb_query)['expense_nb'];
        //        $error = $this->db->error();
        //        die(print_r($error));
        $this->db->query("START TRANSACTION");
        //delete transactions of deprecetiation and expense
        $this->db->prepare_Stmt("DELETE FROM tbl_accounting_transactions  
            WHERE main_id= ? AND main_type= 'vex' 
                AND current_year_id='" . $year_id . "' and is_actual = ?", [&$this->info['exp_id'], &$flag]);
        //               $error = $this->db->error();
        //        die(print_r($error));
        $supp_acc = Supplier::get_all_aupp_accs($this->info['Supplier_ID']);
        $total_exp = 0;
        $total_exp_vat = 0;

        $total_exp_paid = 0;
        $logged_id = smc_session()['user_logged_id'];
        $date_time = date('Y-m-d');
        //        $paid_amount = $this->info['Cash_Amount'] + $this->info['Check_Amount'] + $this->info['Card_Amount'];
        $rate = !empty($this->info['Currency_Rate']) ? $this->info['Currency_Rate'] : CurrencyFactory::getLastExRate($this->info['Currency_ID']);
        $second_rate = CurrencyFactory::getSecondCurrencyLastExRate($this->info['Currency_ID']);
        $exp_data = [ &$this->info['Expense_Date'], &$this->info['Expense_DueDate'], &$this->info['expense_no_vat'],
            &$this->info['Currency_ID'], &$rate, &$this->info['Expense_Note'], &$logged_id, &$this->info['Supplier_ID'],
            &$this->info['Expense_VAT'], &$this->info['Supplier_Invoice'], &$this->info['branch_id'],
            &$this->info['job_id'], &$this->info['project_id'], &$this->info['pay_method_acc_id'], &$second_rate, $date_time, &$this->info['exp_id']];
        $this->db->prepare_Stmt('update tbl_garage_truck_expenses set `Truck_Expense_Date`=?,Expense_DueDate=?,`Truck_Expense_Amount`=?,
            Currency_ID=?,current_rate=?,`Truck_Expense_Description`=?,Logged_ID=?,Supplier_ID=?,
            Expense_VAT=?,supplier_inv_id=?,branch_id=?,job_id = ?,project_id=?,pay_method_acc_id = ?,second_currency_rate=?
            ,date_time=? where `Truck_Expense_ID` = ?', $exp_data);
        $exp_id = $this->info['exp_id'];

        $this->db->prepare_Stmt('delete from tbl_garage_truck_expenses_details where Expense_ID = ?', [&$exp_id]);


        //        $error = $this->db->error();
        //        echo $exp_id.'==';
        //        echo"<pre>";
        //        die(print_r($supp_acc));
        foreach ($this->info['expense'] as $exp) {
            $exp_det = [&$exp_id, &$exp['exp_acc'], &$exp['exp_qty'], &$exp['unit_cost'], &$exp['vat_percent'],
                &$exp['vat_amount'], &$exp['total'], &$exp['exp_desc'], &$exp['paid_amount'], &$exp['truck_id']];

            $this->db->prepare_Stmt('insert into tbl_garage_truck_expenses_details 
                (`Expense_ID`, `Account_ID`, `quantity`, `unit_price`, `vat_percent`, `vat_amount`,
                `net_total`, `description`,`paid_amount`,truck_id) values(?,?,?,?,?,?,?,?,?,?)', $exp_det);

            $total_exp +=($exp['total'] + $exp['vat_amount']);
            $total_exp_vat +=$exp['vat_amount'];
            $total_exp_paid +=$exp['paid_amount'];



            //exp row transaction
            $data_1 = array(
                "JV_ID" => $jv,
                "Transaction_Reference" => $exp_id,
                "Transaction_Account" => $exp['exp_acc'],
                "Transaction_Type" => "Debit",
                "Transaction_Amount" => $exp['total'],
                "Transaction_Date" => $this->info['Expense_Date'],
                "Currency_ID" => $this->info['Currency_ID'],
                "Logged_ID" => $logged_id,
                "Transaction_Description" => $exp['exp_desc'],
                "Current_Ex_Rate" => $rate,
                "second_currency_rate" => $second_rate,
                "main_id" => $exp_id,
                "main_type" => 'vex',
                "branch_id" => $this->info['branch_id'],
                "due_date" => $this->info['Expense_DueDate'],
                "main_nb" => $exp_nb,
                "job_id" => $this->info['job_id'],
                "project_id" => $this->info['project_id']
            );
            JV::addTransaction($data_1, $flag);
        }
        $data_2 = array(
            "JV_ID" => $jv,
            "Transaction_Reference" => $exp_id,
            "Transaction_Account" => $supp_acc['exp'],
            "Transaction_Type" => "Credit",
            "Transaction_Amount" => $total_exp,
            "Transaction_Date" => $this->info['Expense_Date'],
            "Currency_ID" => $this->info['Currency_ID'],
            "Logged_ID" => $logged_id,
            "Transaction_Description" => $this->info['Expense_Note'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $exp_id,
            "main_type" => 'vex',
            "branch_id" => $this->info['branch_id'],
            "due_date" => $this->info['Expense_DueDate'],
            "main_nb" => $exp_nb,
            "job_id" => $this->info['job_id'],
            "project_id" => $this->info['project_id']
        );
        JV::addTransaction($data_2, $flag);
        $data_3 = array(
            "JV_ID" => $jv,
            "Transaction_Reference" => $exp_id,
            "Transaction_Account" => $supp_acc['exp_vat'],
            "Transaction_Type" => "Debit",
            "Transaction_Amount" => $total_exp_vat,
            "Transaction_Date" => $this->info['Expense_Date'],
            "Currency_ID" => $this->info['Currency_ID'],
            "Logged_ID" => $logged_id,
            "Transaction_Description" => $this->info['Expense_Note'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $exp_id,
            "main_type" => 'vex',
            "branch_id" => $this->info['branch_id'],
            "due_date" => $this->info['Expense_DueDate'],
            "main_nb" => $exp_nb,
            "job_id" => $this->info['job_id'],
            "project_id" => $this->info['project_id']
        );
        JV::addTransaction($data_3, $flag);

        $data_6 = array(
            "JV_ID" => $jv,
            "Transaction_Reference" => $exp_id,
            "Transaction_Account" => $this->info['pay_method_acc_id'],
            "Transaction_Type" => "Credit",
            "Transaction_Amount" => $this->info['total_paid'],
            "Transaction_Date" => $this->info['Expense_Date'],
            "Currency_ID" => $this->info['Currency_ID'],
            "Logged_ID" => $logged_id,
            "Transaction_Description" => $this->info['Expense_Note'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $exp_id,
            "main_type" => 'vex',
            "branch_id" => $this->info['branch_id'],
            "job_id" => $this->info['job_id'],
            "main_nb" => $exp_nb,
            "due_date" => $this->info['Expense_Date'],
            "project_id" => $this->info['project_id']
        );
        JV::addTransaction($data_6, $flag);
        $data_7 = array(
            "JV_ID" => $jv,
            "Transaction_Reference" => $exp_id,
            "Transaction_Account" => $supp_acc['exp'],
            "Transaction_Type" => "Debit",
            "Transaction_Amount" => $total_exp_paid,
            "Transaction_Date" => $this->info['Expense_Date'],
            "Currency_ID" => $this->info['Currency_ID'],
            "Logged_ID" => $logged_id,
            "Transaction_Description" => $this->info['Expense_Note'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $exp_id,
            "main_type" => 'vex',
            "branch_id" => $this->info['branch_id'],
            "job_id" => $this->info['job_id'],
            "main_nb" => $exp_nb,
            "due_date" => $this->info['Expense_Date'],
            "project_id" => $this->info['project_id']
        );
        JV::addTransaction($data_7, $flag);


        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function submitRentType() {
        $rent_type_id = $this->info['rent_type_id'];
        if ($rent_type_id == 0) {
            $this->db->prepare_Stmt('Insert into tbl_garage_rent_type (rent_type_name,rent_type_price,currency_id) values (?,?,?)', [&$this->info['rent_type_name'], &$this->info['rent_type_price'], &$this->info['currency_id']]);
        } else {
            $this->db->prepare_Stmt('Update  tbl_garage_rent_type set rent_type_name =?,rent_type_price=?,currency_id =? where rent_type_id = ?', [&$this->info['rent_type_name'], &$this->info['rent_type_price'], &$this->info['currency_id'], &$rent_type_id]);
        }
        $error = $this->db->error();
        return $error;
    }

    public function deleteRentType() {
        $this->db->prepare_Stmt('delete from  tbl_garage_rent_type where rent_type_id = ?', [&$this->info['rent_type_id']]);
        $error = $this->db->error();
        return $error;
    }

    public function get_vehicle_rent_amount($vehicle_id) {
        $query = $this->db->query('SELECT if(isnull(r.rent_type_price),0,r.rent_type_price) as rent_type_price,
            v.driver_emp_id,r.currency_id,c.Currency_Name ,r.rent_type_name,v.rent_type_id,
            concat(`e`.`Employee_FirstName`," ",`e`.`Employee_MiddleName`," ",`e`.`Employee_LastName`) AS `Employee_Name`
            FROM `tbl_garage_vehicle` v 
            join tbl_hrm_employee e on e.Employee_ID = v.driver_emp_id
            left join  tbl_garage_rent_type r on r.rent_type_id = v.`rent_type_id` 
            left join tbl_common_currency c on c.Currency_ID = r.currency_id where v.Vehicle_ID = "' . $vehicle_id . '"');
        $row = $this->db->assoc($query);
        return $row;
    }

    public function vehicle_rent_cron() {
        $this->db->query('START TRANSACTION');
        $vehicle_query = $this->db->query('select vehicle.*,rent_type.rent_type_price from tbl_garage_vehicle vehicle
                                           inner join tbl_garage_rent_type rent_type using(rent_type_id)
                                          where vehicle.rent_type_id IS NOT NULL and vehicle.driver_emp_id IS NOT NULL and vehicle.rent_type_id <> 0 and vehicle.driver_emp_id <> 0');
        $rent_date = date('Y-m-d');
        $account_id = smc_session()['account_setting']['vehicle_rent_account_id'];
        while ($row = $this->db->assoc($vehicle_query)) {
            $data = ['vehicle_id' => $row['Vehicle_ID'], 'employee_id' => $row['driver_emp_id'],
                'currency_id' => smc_session()['local_currency'], 'rent_date' => $rent_date,
                'account_id' => $account_id, 'rent_type_id' => $row['rent_type_id'],
                'rent_amount' => $row['rent_type_price']];
            $this->setData($data)->add_vehicle_rent(true);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function add_vehicle_rent($other = false) {
        if (!$other)
            $this->db->query('START TRANSACTION');
        $date_time = date('Y-m-d H:i:s');
        $logged_id = smc_session()['user_logged_id'];
        $rate = CurrencyFactory::getLastExRate($this->info['currency_id']);
        $second_rate = CurrencyFactory::getSecondCurrencyLastExRate($this->info['currency_id']);
        $data = [&$this->info['vehicle_id'], &$this->info['employee_id'], &$this->info['rent_date'],
            &$this->info['rent_amount'], &$this->info['currency_id'], &$rate, &$this->info['account_id'],
            &$logged_id, &$logged_id, &$date_time, &$date_time, &$this->info['rent_type_id'], &$second_rate];
        $this->db->prepare_Stmt('INSERT INTO `tbl_garage_vehicle_rents`( `vehicle_id`, `employee_id`,
            `rent_date`, `rent_amount`, `currency_id`, `current_rate`, `account_id`, `created_logged_id`,
            `Logged_ID`, `date_created`, `date_time`,rent_type_id,second_currency_rate) 
            values(?,?,?,?,?,?,?,?,?,?,?,?,?)', $data);
        $rent_id = $this->db->id();
        $jv = JV::returnJV(0);
        $employee_name = Employee::get_emp_name_by_id($this->info['employee_id']);
        $desc = 'Vehicle Rent for ' . $employee_name;
        $branch_id = empty($this->info['branch_id']) ? smc_session()['default_setting']['default_branch_id'] : $this->info['branch_id'];
        $job_id = Vehicle::get_vehicle_job_id($this->info['vehicle_id']);
        $emp_acc = Employee::viewEmployeeAccount($this->info['employee_id'])['inc'];
        $dat_1 = ["JV_ID" => $jv,
            "Transaction_Reference" => $rent_id,
            "Transaction_Account" => $this->info['account_id'],
            "Transaction_Type" => "Credit",
            "Transaction_Amount" => $this->info['rent_amount'],
            "Transaction_Date" => $this->info['rent_date'],
            "Currency_ID" => $this->info['currency_id'],
            "Logged_ID" => $logged_id,
            "created_logged_id" => $logged_id,
            "Transaction_Description" => $desc,
            "date_created" => $date_time,
            "date_time" => $date_time,
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $rent_id,
            "main_type" => "vre",
            "branch_id" => $branch_id,
            "due_date" => $this->info['rent_date'],
            "main_nb" => $rent_id
        ];
        JV::addTransaction($dat_1);
        $dat_2 = ["JV_ID" => $jv,
            "Transaction_Reference" => $rent_id,
            "Transaction_Account" => $emp_acc,
            "Transaction_Type" => "Debit",
            "Transaction_Amount" => $this->info['rent_amount'],
            "Transaction_Date" => $this->info['rent_date'],
            "Currency_ID" => $this->info['currency_id'],
            "Logged_ID" => $logged_id,
            "created_logged_id" => $logged_id,
            "Transaction_Description" => $desc,
            "date_created" => $date_time,
            "date_time" => $date_time,
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $rent_id,
            "main_type" => "vre",
            "branch_id" => $branch_id,
            "due_date" => $this->info['rent_date'],
            "main_nb" => $rent_id,
            "job_id" => $job_id
        ];
        JV::addTransaction($dat_2);
        if (!$other) {
            $error = $this->db->error();
            if (empty($error)) {
                $this->db->query('COMMIT');
            } else {
                $this->db->query('ROLLBACK');
            }
            return $error;
        }
    }

    public function edit_vehicle_rent() {
        $this->db->query('START TRANSACTION');
        $date_time = date('Y-m-d H:i:s');
        $logged_id = smc_session()['user_logged_id'];
        $rate = CurrencyFactory::getLastExRate($this->info['currency_id']);
        $second_rate = CurrencyFactory::getSecondCurrencyLastExRate($this->info['currency_id']);

        $data = [&$this->info['vehicle_id'], &$this->info['employee_id'], &$this->info['rent_date'],
            &$this->info['rent_amount'], &$this->info['currency_id'], &$rate, &$this->info['account_id'],
            &$logged_id, &$date_time, &$this->info['rent_type_id'], &$second_rate, &$this->info['rent_id']];
        $this->db->prepare_Stmt('update `tbl_garage_vehicle_rents` set  `vehicle_id` = ?, `employee_id` = ?,
            `rent_date` = ?, `rent_amount` = ?, `currency_id` = ?, `current_rate` = ?, `account_id` = ?,
            `Logged_ID` = ?,  `date_time` = ?,rent_type_id = ?,second_currency_rate=? where rent_id = ?', $data);
        $rent_id = $this->info['rent_id'];
        $jv_query = $this->db->query("SELECT DISTINCT JV_ID,Current_Ex_Rate FROM tbl_accounting_transactions  WHERE main_id='" . $this->info['rent_id'] . "' AND main_type= 'vre' ");
        $jv_assoc = $this->db->assoc($jv_query);
        if (count($jv_assoc) > 0)
            $jv = $jv_assoc['JV_ID'];
        else
            $jv = JV::returnJV(0);
        $this->db->prepare_Stmt("DELETE FROM tbl_accounting_transactions  WHERE main_id= ? AND main_type= 'vre' ", [&$this->info['rent_id']]);

        $employee_name = Employee::get_emp_name_by_id($this->info['employee_id']);
        $desc = 'Vehicle Rent for ' . $employee_name;
        $branch_id = empty($this->info['branch_id']) ? smc_session()['default_setting']['default_branch_id'] : $this->info['branch_id'];
        $job_id = Vehicle::get_vehicle_job_id($this->info['vehicle_id']);

        $emp_acc = Employee::viewEmployeeAccount($this->info['employee_id'])['inc'];
        $dat_1 = ["JV_ID" => $jv,
            "Transaction_Reference" => $rent_id,
            "Transaction_Account" => $this->info['account_id'],
            "Transaction_Type" => "Credit",
            "Transaction_Amount" => $this->info['rent_amount'],
            "Transaction_Date" => $this->info['rent_date'],
            "Currency_ID" => $this->info['currency_id'],
            "Logged_ID" => $logged_id,
            "created_logged_id" => $logged_id,
            "Transaction_Description" => $desc,
            "date_created" => $date_time,
            "date_time" => $date_time,
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $rent_id,
            "main_type" => "vre",
            "branch_id" => $branch_id,
            "due_date" => $this->info['rent_date'],
            "main_nb" => $rent_id
        ];
        JV::addTransaction($dat_1);
        $dat_2 = ["JV_ID" => $jv,
            "Transaction_Reference" => $rent_id,
            "Transaction_Account" => $emp_acc,
            "Transaction_Type" => "Debit",
            "Transaction_Amount" => $this->info['rent_amount'],
            "Transaction_Date" => $this->info['rent_date'],
            "Currency_ID" => $this->info['currency_id'],
            "Logged_ID" => $logged_id,
            "created_logged_id" => $logged_id,
            "Transaction_Description" => $desc,
            "date_created" => $date_time,
            "date_time" => $date_time,
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $rent_id,
            "main_type" => "vre",
            "branch_id" => $branch_id,
            "due_date" => $this->info['rent_date'],
            "main_nb" => $rent_id,
            "job_id" => $job_id
        ];
        JV::addTransaction($dat_2);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function get_vehicle_rent_by_id($id) {
        $data = array();
        $query = $this->db->query('select * from vehicle_rents where rent_id = "' . $id . '"');
        $data = $this->db->assoc($query);
        return $data;
    }

    public function get_multi_rent_payment($date, $rent_type = null) {
        $data = array();
        $report = new Report;
        $cond = ' ';
        if (!empty($rent_type)) {
            $cond.=' and  rent_type_id = "' . $rent_type . '" ';
        }
        $query = $this->db->query('select * from  vehicle_rents 
            where rent_date = "' . $date . '" ' . $cond);
        while ($row = $this->db->assoc($query)) {
            $data_row['Vehicle_Name'] = $row['Vehicle_Name'] . ' - ' . $row['Vehicle_Code'];
            $data_row['Employee_Name'] = $row['Employee_Name'];
            $data_row['rent_amount'] = CommonFunctions::formatNumber($row['rent_amount']) . ' ' . $row['Currency_Symbol'];
            $data_row['balance'] = CommonFunctions::formatNumber($report->returnEmployeeExactBalance($row['employee_id'], $row['currency_id'], smc_session()['start_date'], smc_session()['end_date'], 0, 0)['Balance']) . ' ' . $row['Currency_Symbol'];
            $data_row['amount'] = '<input type="number" class="form-control pay_amount" data-id="' . $row['rent_id'] . '" data-currency="' . $row['currency_id'] . '" data-vehicle="' . $row['vehicle_id'] . '" data-emp="' . $row['employee_id'] . '" />';
            $data[] = $data_row;
        }
        return $data;
    }

    public function add_multi_rent_pay() {
        $this->db->query('START TRANSACTION');
        $today = Date::getToday();
        $logged_id = smc_session()['user_logged_id'];
        $date_time = Date("Y-m-d H:i:s");
        $branch_id = empty($this->info['branch_id']) ? smc_session()['default_setting']['default_branch_id'] : $this->info['branch_id'];
        foreach ($this->info['pay_arr'] as $pay) {
            $jv = JV::returnJV(0);
            $emp_acc = Employee::viewEmployeeAccount($pay['employee_id'])['inc'];
            $rate = CurrencyFactory::getLastExRate($pay['currency_id']);
            $desc = 'Rent Payment for ' . $pay['Employee_Name'];
            $job_id = Vehicle::get_vehicle_job_id($pay['vehicle_id']);
            $dat_1 = ["JV_ID" => $jv,
                "Transaction_Reference" => $pay['rent_id'],
                "Transaction_Account" => $emp_acc,
                "Transaction_Type" => "Credit",
                "Transaction_Amount" => $pay['amount'],
                "Transaction_Date" => $today,
                "Currency_ID" => $pay['currency_id'],
                "Logged_ID" => $logged_id,
                "created_logged_id" => $logged_id,
                "Transaction_Description" => $desc,
                "date_created" => $date_time,
                "date_time" => $date_time,
                "Current_Ex_Rate" => $rate,
                "main_id" => $pay['rent_id'],
                "main_type" => "vre",
                "branch_id" => $branch_id,
                "due_date" => $today,
                "main_nb" => $pay['rent_id'],
                "job_id" => $job_id
            ];
            JV::addTransaction($dat_1);
            $dat_2 = ["JV_ID" => $jv,
                "Transaction_Reference" => $pay['rent_id'],
                "Transaction_Account" => $pay['payment_type'],
                "Transaction_Type" => "Debit",
                "Transaction_Amount" => $pay['amount'],
                "Transaction_Date" => $today,
                "Currency_ID" => $pay['currency_id'],
                "Logged_ID" => $logged_id,
                "created_logged_id" => $logged_id,
                "Transaction_Description" => $desc,
                "date_created" => $date_time,
                "date_time" => $date_time,
                "Current_Ex_Rate" => $rate,
                "main_id" => $pay['rent_id'],
                "main_type" => "vre",
                "branch_id" => $branch_id,
                "due_date" => $today,
                "main_nb" => $pay['rent_id']
            ];
            JV::addTransaction($dat_2);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public static function get_vehicle_job_id($id) {
        $db = DBConnectorFactory::loadDriver();
        $job_id = 0;
        $query = $db->query('select job_id from tbl_accounting_jobs 
            where job_main_id = "' . $id . '" and job_main_type="veh"');
        $row = $db->assoc($query);
        if (!empty($row))
            $job_id = $row['job_id'];
        return $job_id;
    }

    public function searchInsuranceCompany($term) {
        $data = array();
        $query = $this->db->prepare_Stmt('select insurance_company as text 
            from tbl_garage_vehicle where insurance_company like ?  limit 30', ["%", $term . "%"]);
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function getMaxVehicleCode() {
        $query = $this->db->query('select max(cast(`Vehicle_Code` as signed)) as max_code from tbl_garage_vehicle');
        $code = $this->db->assoc($query)['max_code'];
        return $code + 1;
    }

    public static function getVehiclesInsuranceMecanique() {
        $db = DBConnectorFactory::loadDriver();
        $data = array('data' => array(), 'total' => 0);
        $default_days = smc_session()['default_setting']['days_before_mecq_insur_notification'];
        $vehicle_query = $db->query('select if(DATE_SUB(insurance_date, INTERVAL ' . $default_days . ' DAY) <= now(),1,0) as insurance,if(DATE_SUB(concat_ws("-",last_mecanique_year,mecanique_month,"01"), INTERVAL ' . $default_days . ' DAY) <= now(),1,0) as mecanique,
                                            vehicle.Vehicle_Name,
                                            vehicle.Vehicle_ID,
                                            concat_ws(" - ",vehicle.Vehicle_Name,vehicle.Vehicle_Code) as vehicle_name_code
                                          from  tbl_garage_vehicle vehicle
                                          where  is_disabled = 0 and (!isNull(insurance_date) && DATE_SUB(insurance_date, INTERVAL ' . $default_days . ' DAY) <= now())
                                          or (!isNull(mecanique_month) && (DATE_SUB(concat_ws("-",last_mecanique_year,mecanique_month,"01"), INTERVAL ' . $default_days . ' DAY) <= now())) ');
        $total = 0;
        while ($row = $db->assoc($vehicle_query)) {
            $data['data'][] = $row;
            $total++;
        }
        $data['total'] = $total;
        return $data;
    }

    public function deleteVehicleExpense() {
        $query = $this->db->query('select Truck_Expense_Date ,Is_Hidden
            from tbl_garage_truck_expenses where Truck_Expense_ID = "'.$this->info['expense_id'].'"');
        $row = $this->db->assoc($query);
        $check = CommonFunctions::check_lock_data(['type' => 'vehicle_expense', 'start_date' => $row['Truck_Expense_Date'], 'is_actual' => $row['Is_Hidden']]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $this->db->query('START TRANSACTION');
        $this->db->prepare_Stmt('delete from tbl_garage_truck_expenses_details where Expense_ID = ? ', [&$this->info['expense_id']]);
        $this->db->prepare_Stmt('delete from tbl_garage_truck_expenses where Truck_Expense_ID = ? ', [&$this->info['expense_id']]);
        $this->db->prepare_Stmt('delete from tbl_accounting_transactions where main_type = "vex" and main_id = ? ', [&$this->info['expense_id']]);

        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function getExpenseIdsLinkedVehicle($vehicle_id) {
        $data = array();
        $query = $this->db->query('
                                  select Expense_ID
                                  from tbl_garage_truck_expenses_details 
                                  where truck_id = "' . $vehicle_id . '"
        ');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row['Expense_ID'];
        }
        return $data;
    }

    public function enable_disable_vehicle() {
        $this->db->prepare_Stmt('update tbl_garage_vehicle set is_disabled = ? 
            where Vehicle_ID = ?', [&$this->info['status'], &$this->info['vehicle_id']]);
        $error = $this->db->error();
        return $error;
    }

}
