<?php

class Expense {

    protected $info = array();
    private $db;
    private $dep_rate_id;
    private $year_id;
    private $year_id_actual;
    public $permissions;

    public function __construct() {
        $this->db = DBConnectorFactory::loadDriver();
        $this->year_id = Session::getCurrentYearID();
        $this->year_id_actual = Session::getCurrentYearIDActual();
        $this->start_date = Session::getCurrentYearRange()['start_date'];
        $this->end_date = Session::getCurrentYearRange()['end_date'];
        $this->permissions = new Permissions(smc_session()['user_logged_id']);

    }

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

    //    public function viewExpsAndAssetsAccounts() {
    //        $query = $this->db->query('SELECT * FROM tbl_chart_of_accounts WHERE (LEFT(Account_Code,1)=6 OR LEFT(Account_Code,1)=2) AND Account_Status = 1');
    //        while ($row = $this->db->assoc($query))
    //            $data[] = $row;
    //        return $data;
    //    }

    public function ExpenseAccSearch($term = null, $page, $limit) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        if (!is_null($term)) {
            $cond .=' AND ( Account_Name LIKE "%' . $term . '%" OR Account_Code LIKE "%' . $term . '%" )';
        }
        $query = $this->db->query('SELECT Account_Code as id,
                                          concat(Account_Name," - ",Account_Code) as text
                                    FROM 
                                        tbl_chart_of_accounts 
                                    WHERE (LEFT(Account_Code,1)=2
                                    OR    LEFT(Account_Code,1)=6 )
                                    and Status_ID = 1
                                    ' . $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 viewDepreciationPeriod() {
        $data = array();
        $query = $this->db->query('SELECT * FROM tbl_accounting_period_types');
        while ($row = $this->db->assoc($query))
            $data[] = $row;
        return $data;
    }

    //    public function create($is_hidden = false) {
    ////        echo"<pre>";
    ////        die(print_r($this->info));
    //        $check = JV::checkCurrentYear($this->info['PurchaseInvoiceDate'], $this->info['PurchaseInvoiceDate'], $is_hidden);
    //        if ($check !== '') {
    //            $error = array();
    //            $error[0] = $check;
    //            return $error;
    //        }
    //
    //        $this->db->query("START TRANSACTION");
    //        $fileType = '';
    //        $documentType = '';
    //        if ($is_hidden) {
    //            $is_hidden = 1;
    //            $flag = 1;
    //            $year_id = $this->year_id_actual;
    //        } else {
    //            $is_hidden = 0;
    //            $flag = 0;
    //            $year_id = $this->year_id;
    //        }
    //        $main_jv = JV::returnJV($flag);
    //
    //        for ($i = 0; $i < count($this->info['Expenses']); $i++) {
    //            $temp = $this->info['Expenses'][$i];
    //
    //            if (!empty($_FILES)) {
    //                $fileType = explode(".", basename($_FILES["Image" . $i]['name']));
    //                $fileType = strtolower($fileType[count($fileType) - 1]);
    //                $documentType = $fileType ? ('.' . $fileType) : "";
    //            }
    //            $vat_amount = $temp['Expense_VAT'];
    //            $info = [
    //                &$temp['Supplier_ID'],
    //                &$temp['Expense_Date'],
    //                &$temp['Expense_Description'],
    //                &$temp['Expense_Item_Quantity'],
    //                &$temp['Expense_Unit_Price'],
    //                &$temp['Account_Code'],
    //                &$temp['Expense_Total_Price'],
    //                &$temp['Currency_ID'],
    //                &$temp['Expense_Note'],
    //                &$vat_amount,
    //                &$this->info[0],
    //                &$is_hidden,
    //                &$documentType,
    //                &$year_id
    //            ];
    //            $this->db->prepare_Stmt("
    //                    INSERT INTO
    //                        tbl_accounting_expenses
    //                        (	Supplier_ID,
    //                            Expense_Date,
    //                            Expense_Description,
    //                            Expense_Item_Quantity,
    //                            Expense_Unit_Price,
    //                            Account_ID,
    //                            Expense_Total_Price,
    //                            Currency_ID,
    //                            Expense_Note,
    //                            Expense_VAT,
    //                            Logged_ID,
    //                            Is_Hidden,
    //                            Document_Type,
    //                            current_year_id
    //
    //                        )
    //                    VALUES
    //                        (
    //                            ?,?,?,?,?,?,?,?,?,?,?,?,?,?
    //                        )
    //            ", $info);
    //
    //            $exp_id = $this->db->id();
    //            $jv = $main_jv + $i;
    //            if ($temp['Depreciation_Rate_Percent'] > 0) {
    //                $dep_data = $temp;
    //                $dep_data['JV_ID'] = $jv;
    //                $dep_data['Is_Hidden'] = $is_hidden;
    //                self::addDepreciationRateData($dep_data);
    //            }
    //
    //            $acc_code = $temp['Account_Code'];
    //            $fixed = JV::getAccountCodeByID($acc_code);
    //            $left_fixed = substr($fixed, 0, 1);
    //            $sub_type = $left_fixed == 2 ? 'fa' : 'exp';
    //            $is_expense = $left_fixed == 2 ? false : true;
    //            $sup = $this->db->query('SELECT
    //                                            Account_Code,
    //                                            Account_ID
    //                                        FROM
    //                                            tbl_chart_of_accounts 
    //                                        WHERE
    //                                            main_id = "' . $temp['Supplier_ID'] . '"
    //                                        AND
    //                                            main_type="supp"
    //                                        AND
    //                                            sub_type="' . $sub_type . '"
    //                    ');
    //            $row = $this->db->assoc($sup);
    ////            $supp_account = $row['Account_Code'];
    //            $supp_id = $row['Account_ID'];
    //            $supp_vat_account = self::returnSuppVATAccount($temp['Supplier_ID'], $is_expense);
    //
    //            $amount_novat = $temp['Expense_Total_Price'] - $vat_amount;
    //            $total_paid_all = $temp['Card_Amount'] + $temp['Cash_Amount'] + $temp['Check_Amount'];
    //            $remaining = $temp['Expense_Total_Price'] - $total_paid_all;
    //            $data_1 = array(
    //                "JV_ID" => $jv,
    //                "Transaction_Reference" => $exp_id,
    //                "Transaction_Account" => $temp['Account_Code'],
    //                "Transaction_Type" => "Debit",
    //                "Transaction_Amount" => $amount_novat,
    //                "Transaction_Date" => $temp['Expense_Date'],
    //                "Currency_ID" => $temp['Currency_ID'],
    //                "Logged_ID" => $this->info[0],
    //                "Transaction_Description" => $temp['Expense_Description'],
    //                "Current_Ex_Rate" => "0",
    //                "main_id" => $exp_id,
    //                "main_type" => 'exp'
    //            );
    //            JV::addTransaction($data_1, $flag);
    //
    //            $data_2 = array(
    //                "JV_ID" => $jv,
    //                "Transaction_Reference" => $exp_id,
    //                "Transaction_Account" => $supp_vat_account,
    //                "Transaction_Type" => "Debit",
    //                "Transaction_Amount" => $vat_amount,
    //                "Transaction_Date" => $temp['Expense_Date'],
    //                "Currency_ID" => $temp['Currency_ID'],
    //                "Logged_ID" => $this->info[0],
    //                "Transaction_Description" => $temp['Expense_Description'],
    //                "Current_Ex_Rate" => "0",
    //                "main_id" => $exp_id,
    //                "main_type" => 'exp'
    //            );
    //            JV::addTransaction($data_2, $flag);
    //
    //            $data_3 = array(
    //                "JV_ID" => $jv,
    //                "Transaction_Reference" => $exp_id,
    //                "Transaction_Account" => $supp_id,
    //                "Transaction_Type" => "Credit",
    //                "Transaction_Amount" => $temp['Expense_Total_Price'],
    //                "Transaction_Date" => $temp['Expense_Date'],
    //                "Currency_ID" => $temp['Currency_ID'],
    //                "Logged_ID" => $this->info[0],
    //                "Transaction_Description" => $temp['Expense_Description'],
    //                "Current_Ex_Rate" => "0",
    //                "main_id" => $exp_id,
    //                "main_type" => 'exp'
    //            );
    //            JV::addTransaction($data_3, $flag);
    ////             $error = $this->db->error();
    ////            die(print_r($error));
    //            $data_31 = array(
    //                "JV_ID" => $jv,
    //                "Transaction_Reference" => $exp_id,
    //                "Transaction_Account" => $supp_id,
    //                "Transaction_Type" => "Debit",
    //                "Transaction_Amount" => $total_paid_all,
    //                "Transaction_Date" => $temp['Expense_Date'],
    //                "Currency_ID" => $temp['Currency_ID'],
    //                "Logged_ID" => $this->info[0],
    //                "Transaction_Description" => $temp['Expense_Description'],
    //                "Current_Ex_Rate" => "0",
    //                "main_id" => $exp_id,
    //                "main_type" => 'exp'
    //            );
    //            JV::addTransaction($data_31, $flag);
    //            $data_4 = array(
    //                "JV_ID" => $jv,
    //                "Transaction_Reference" => $exp_id,
    //                "Transaction_Account" => $temp['Card_Account'],
    //                "Transaction_Type" => "Credit",
    //                "Transaction_Amount" => $temp['Card_Amount'],
    //                "Transaction_Date" => $temp['Expense_Date'],
    //                "Currency_ID" => $temp['Currency_ID'],
    //                "Logged_ID" => $this->info[0],
    //                "Transaction_Description" => $temp['Expense_Description'],
    //                "Current_Ex_Rate" => "0",
    //                "main_id" => $exp_id,
    //                "main_type" => 'exp'
    //            );
    //            JV::addTransaction($data_4, $flag);
    //            $data_5 = array(
    //                "JV_ID" => $jv,
    //                "Transaction_Reference" => $exp_id,
    //                "Transaction_Account" => $temp['Cash_Account'],
    //                "Transaction_Type" => "Credit",
    //                "Transaction_Amount" => $temp['Cash_Amount'],
    //                "Transaction_Date" => $temp['Expense_Date'],
    //                "Currency_ID" => $temp['Currency_ID'],
    //                "Logged_ID" => $this->info[0],
    //                "Transaction_Description" => $temp['Expense_Description'],
    //                "Current_Ex_Rate" => "0",
    //                "main_id" => $exp_id,
    //                "main_type" => 'exp'
    //            );
    //            JV::addTransaction($data_5, $flag);
    //            $data_6 = array(
    //                "JV_ID" => $jv,
    //                "Transaction_Reference" => $exp_id,
    //                "Transaction_Account" => $temp['Check_Account'],
    //                "Transaction_Type" => "Credit",
    //                "Transaction_Amount" => $temp['Check_Amount'],
    //                "Transaction_Date" => $temp['Expense_Date'],
    //                "Currency_ID" => $temp['Currency_ID'],
    //                "Logged_ID" => $this->info[0],
    //                "Transaction_Description" => $temp['Expense_Description'],
    //                "Current_Ex_Rate" => "0",
    //                "main_id" => $exp_id,
    //                "main_type" => 'exp'
    //            );
    //            JV::addTransaction($data_6, $flag);
    //
    //            if (!empty($_FILES) && empty($error)) {
    //                $uploaddir = dirname(__FILE__) . '/../../uploads/expenses/';
    //                $fileType = explode(".", basename($_FILES["Image" . $i]['name']));
    //                $fileType = strtolower($fileType[count($fileType) - 1]);
    //
    //                $file = move_uploaded_file($_FILES["Image" . $i]['tmp_name'], $uploaddir . $exp_id . "." . $fileType);
    //            }
    //        }
    //        $error = $this->db->error();
    //        if (empty($error)) {
    //            $this->db->query('COMMIT');
    //        } else {
    //            $this->db->query('ROLLBACK');
    //        }
    //        return array_unique($error);
    //    }
    //    public function view($status = null, $expense_id = null, $date = null, $month = null, $year = null, $from = null, $to = null, $is_hidden = false) {
    //        $data = array();
    //        $condition = '';
    //        if (!$is_hidden)
    //            $condition .= ' AND e.Is_Hidden = 0';
    //        if (!is_null($status))
    //            $condition .= ' AND e.Expense_Status = ' . $status;
    //        if (!is_null($expense_id))
    //            $condition .= ' AND e.Expense_ID = "' . $expense_id . '"';
    //        if (!is_null($date))
    //            $condition .= ' AND e.Expense_Date = "' . $date . '"';
    //        if (!is_null($month))
    //            $condition .= ' AND MONTH(e.Expense_Date) = "' . $month . '"';
    //        if (!is_null($year))
    //            $condition .= ' AND YEAR(e.Expense_Date) = "' . $year . '"';
    //        if (!is_null($from) and ! is_null($to))
    //            $condition .= ' AND e.Expense_Date >= "' . $from . '" AND e.Expense_Date <= "' . $to . '"';
    //        $query = $this->db->query('SELECT
    //                                            e.*,
    //                                            e.Currency_ID AS Expense_Currency,
    //                                            cu.Currency_Code,
    //                                            cu.Currency_Symbol,
    //                                            Concat(ep.Employee_FirstName," ",ep.Employee_MiddleName," ",ep.Employee_LastName) AS Employee_Name,
    //                                            c.*,
    //											s.Supplier_Name
    //                                        FROM
    //                                            tbl_accounting_expenses e
    //                                        LEFT JOIN
    //                                            tbl_hrm_employee ep
    //                                        ON
    //                                            e.Logged_ID = ep.Employee_ID
    //										LEFT JOIN
    //                                            tbl_stock_suppliers s
    //                                        ON
    //                                            e.Supplier_ID = s.Supplier_ID
    //                                        LEFT JOIN
    //                                            tbl_chart_of_accounts c
    //                                        ON
    //                                            e.Account_ID = c.Account_ID
    //                                           LEFT JOIN
    //                                           tbl_common_currency cu
    //                                           ON
    //                                           e.Currency_ID = cu.Currency_ID
    //                                        WHERE
    //                                            1 = 1' . $condition . ' GROUP BY e.Expense_ID');
    //        while ($row = $this->db->assoc($query)) {
    //            if (is_null($row['Supplier_Name']))
    //                $row['Supplier_Name'] = "-";
    //            $data[] = $row;
    //        }
    //
    //        return $data;
    //    }
    //    public function updateExpense($id, $Extension) {
    //        $this->db->query("START TRANSACTION");
    //        $desc = $this->info['old_desc'];
    ////        if ($this->info['is_hidden'] == 0)
    //        $flag = $this->info['is_hidden'];
    ////        else
    ////            $flag = 1;
    //        $ex = '.' . $Extension;
    //        $update = [
    //            &$this->info['Expense_Description'],
    //            &$this->info['Expense_Item_Quantity'],
    //            &$this->info['Expense_Date'],
    //            &$this->info['Expense_Unit_Price'],
    //            &$this->info['Expense_Total_Price'],
    //            &$this->info['Expense_Note'],
    //            &$this->info['Account_Code'],
    //            &$ex,
    //            &$id
    //        ];
    //        $this->db->prepare_Stmt("
    //                      UPDATE
    //                          tbl_accounting_expenses
    //                    SET
    //                        Expense_Description   = ?,
    //                        Expense_Item_Quantity = ?,
    //                        Expense_Date          = ?,
    //                        Expense_Unit_Price    = ?,
    //                        Expense_Total_Price   = ?,
    //                        Expense_Note          = ?,
    //                        Account_ID          = ?,
    //                        Document_Type         = ?
    //                    WHERE
    //                        Expense_ID            = ? ", $update);
    //
    //        $data = self::returnNeededTransactions($id, $desc, $flag); //return transaction happend upon this expense
    //        $j = 0;
    //        $jv = JV::returnJV($flag);
    //        $description = $this->info['Expense_Description'];
    //        if ($this->info['Expense_Total_Price'] > $this->info['old_total']) {
    //            //if we update expense increasing it's total price
    //            //same transactions will occur with the difference of the new update
    //            $new_amount = $this->info['Expense_Total_Price'] - $this->info['old_total'];
    //            for ($i = 0; $i < count($data); $i++) {
    //                $data_1 = array(
    //                    "JV_ID" => $jv,
    //                    "Transaction_Reference" => $id,
    //                    "Transaction_Account" => $data[$i]['Account_ID'],
    //                    "Transaction_Type" => $data[$i]['Transaction_Type'],
    //                    "Transaction_Amount" => $new_amount,
    //                    "Transaction_Date" => $this->info['Expense_Date'],
    //                    "Currency_ID" => $this->info['Currency_ID'],
    //                    "Logged_ID" => $this->info[0],
    //                    "Transaction_Description" => $description,
    //                    "Current_Ex_Rate" => "0",
    //                    "main_id" => $id,
    //                    "main_type" => 'exp'
    //                );
    //                JV::addTransaction($data_1, $flag);
    //            }
    //        } elseif ($this->info['Expense_Total_Price'] < $this->info['old_total']) {
    //            //if we update expense deccreasing it's total price
    //            //opposite transactions will occur with the difference of the new update
    //            $new_amount = $this->info['old_total'] - $this->info['Expense_Total_Price'];
    //            for ($i = 0; $i < count($data); $i++) {
    //                if ($data[$i]['Transaction_Type'] == "Credit")
    //                    $type = "Debit";
    //                else
    //                    $type = "Credit";
    //                $data_2 = array(
    //                    "JV_ID" => $jv,
    //                    "Transaction_Reference" => $id,
    //                    "Transaction_Account" => $this->info['Account_Code'],
    //                    "Transaction_Type" => $type,
    //                    "Transaction_Amount" => $new_amount,
    //                    "Transaction_Date" => $this->info['Expense_Date'],
    //                    "Currency_ID" => $row['Currency_ID'],
    //                    "Logged_ID" => $this->info[0],
    //                    "Transaction_Description" => $description,
    //                    "Current_Ex_Rate" => "0",
    //                    "main_id" => $id,
    //                    "main_type" => 'exp'
    //                );
    //                JV::addTransaction($data_2, $flag);
    //            }
    //        }
    //        $error = $this->db->error();
    //        if (empty($error)) {
    //            $this->db->query('COMMIT');
    //        } else {
    //            $this->db->query('ROLLBACK');
    //        }
    //        return array_unique($error);
    //    }
    //    public function returnNeededTransactions($id, $desc, $flag) {
    //        $data = array();
    ////        if (is_null($flag))
    //        $transaction = $this->db->query(' SELECT
    //                                                *
    //                                            FROM
    //                                                tbl_accounting_transactions
    //                                            WHERE
    //                                                Transaction_Reference = "' . $id . '"
    //                                            AND
    //                                                Transaction_Description = "' . $desc . '"
    //                                            and is_actual = "' . $flag . '"
    //                        ');
    //        else
    //            $transaction = $this->db->query(' SELECT
    //                                                *
    //                                            FROM
    //                                                tbl_mgt_logs
    //                                            WHERE
    //                                                Transaction_Reference = "' . $id . '"
    //                                            AND
    //                                                Transaction_Description = "' . $desc . '"
    //                        ');
    //        while ($row = $this->db->assoc($transaction))
    //            $data[] = $row;
    //        return $data;
    //    }

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

    //    public function viewWeighBridge($date = null, $year = null, $month = null, $from = null, $to = null) {
    //        $data = array();
    //        $condition = '';
    //        if (!is_null($date))
    //            $condition .= ' AND w.WeighBridge_Date = "' . $date . '"';
    //        if (!is_null($month))
    //            $condition .= ' AND MONTH(w.WeighBridge_Date) = "' . $month . '"';
    //        if (!is_null($year))
    //            $condition .= ' AND YEAR(w.WeighBridge_Date) = "' . $year . '"';
    //        if (!is_null($from) and ! is_null($to))
    //            $condition .= ' AND w.WeighBridge_Date >= "' . $from . '" AND w.WeighBridge_Date <= "' . $to . '"';
    //        $query = $this->db->query('SELECT
    //                                        v.Vehicle_Code,
    //                                        v.Vehicle_Registration_Number,
    //                                        w.Weighbridge_Amount,
    //                                        w.WeighBridge_Date,
    //                                        w.Is_Paid,
    //                                        w.Weighbridge_ID,
    //                                        w.Currency_ID,
    //                                        w.Weighbridge_Vehicle_ID,
    //                                        c.Currency_Symbol
    //                                    FROM
    //                                        tbl_garage_vehicle_weighbridge w
    //                                    LEFT JOIN
    //                                        tbl_garage_vehicle v
    //                                    ON
    //                                        v.Vehicle_ID = w.Weighbridge_Vehicle_ID
    //                                    LEFT JOIN
    //                                        tbl_common_currency c
    //                                    ON
    //                                        c.Currency_ID = w.Currency_ID
    //                                    WHERE
    //                                        1=1
    //
    //                ' . $condition);
    //        while ($row = $this->db->assoc($query))
    //            $data[] = $row;
    //        return $data;
    //    }

    public function submitPaidWeighBridge() {
        $is_actual = 0;
        $this->db->query("START TRANSACTION");
        $update = [&$this->info['Weighbridge_ID']];
        $this->db->prepare_Stmt("UPDATE
                                    tbl_garage_vehicle_weighbridge
                                SET
                                    Is_Paid = 1
                                WHERE
                                    Weighbridge_ID = ?
            ", $update);
        $desc2 = "Vehicle Weighbridge Expense For Vehicle Number " . $this->info['Weighbridge_Vehicle_Number'];
        $jv = JV::returnJV($is_actual);
        $ids_arr = JV::getAccountIDsByCodes(["61185", "53"]);
        $data_1 = array(
            "JV_ID" => $jv,
            "Transaction_Reference" => $this->info['Weighbridge_ID'],
            "Transaction_Account" => $ids_arr['61185'],
            "Transaction_Type" => "Debit",
            "Transaction_Amount" => $this->info['Weighbridge_Amount'],
            "Transaction_Date" => $this->info['Weighbridge_Date'],
            "Currency_ID" => $this->info['Weighbridge_Currency_ID'],
            "Logged_ID" => $this->info[0],
            "Transaction_Description" => $desc2,
            "Current_Ex_Rate" => "0",
            "main_id" => $this->info['Weighbridge_ID'],
            "main_type" => 'wbd'
        );
        JV::addTransaction($data_1, $is_actual);
        $data_2 = array(
            "JV_ID" => $jv,
            "Transaction_Reference" => $this->info['Weighbridge_ID'],
            "Transaction_Account" => $ids_arr["53"],
            "Transaction_Type" => "Credit",
            "Transaction_Amount" => $this->info['Weighbridge_Amount'],
            "Transaction_Date" => $this->info['Weighbridge_Date'],
            "Currency_ID" => $this->info['Weighbridge_Currency_ID'],
            "Logged_ID" => $this->info[0],
            "Transaction_Description" => $desc2,
            "Current_Ex_Rate" => "0",
            "main_id" => $this->info['Weighbridge_ID'],
            "main_type" => 'wbd'
        );
        JV::addTransaction($data_2, $is_actual);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    /**
     * 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 SaveUploadedPhoto($FileName, $SavePath) {
    //        if (!isset($_FILES[$FileName]))
    //            return false;
    //        $SaveToPath = substr($SavePath, 0, strrpos($SavePath, "/") + 1);
    //        $SaveAsFile = substr($SavePath, strrpos($SavePath, "/") + 1);
    //        $SaveAsExtention = explode(".", $SaveAsFile);
    //        $SaveAsExtention = strtolower($SaveAsExtention[count($SaveAsExtention) - 1]);
    //        $SaveAsFile = substr($SaveAsFile, 0, strrpos($SaveAsFile, "."));
    //        $fileType = explode(".", basename($_FILES[$FileName]['name']));
    //        $fileType = strtolower($fileType[count($fileType) - 1]);
    //        $DestinationFile = $SaveToPath . $SaveAsFile . ".";
    //        unlink($DestinationFile . $fileType);
    //        move_uploaded_file($_FILES[$FileName]['tmp_name'], $DestinationFile . $fileType);
    //        if ($fileType == "png" && $SaveAsExtention == "jpg") {
    //            unlink($DestinationFile . "jpg");
    //            $image = imagecreatefrompng($DestinationFile . $fileType);
    //            imagejpeg($image, $DestinationFile . $SaveAsExtention, 100);
    //            imagedestroy($image);
    //            unlink($DestinationFile . $fileType);
    //        }
    //        return file_exists($DestinationFile . $SaveAsExtention);
    //    }
    //    private function returnSuppVATAccount($supp_id, $is_expense) {
    //        if (!$is_expense)
    //            $sub_type = "fa_vat";
    //        else
    //            $sub_type = "exp_vat";
    //        $query = $this->db->query('SELECT Account_ID,Account_Code FROM tbl_chart_of_accounts
    //                            WHERE main_id = "' . $supp_id . '" and main_type="supp"
    //                            AND sub_type="' . $sub_type . '"');
    //        $row = $this->db->assoc($query);
    //        return $row['Account_ID'];
    //    }
    //    public function addDepreciationRateData($data) {
    //        if ($data['Depreciation_Period_ID'] == 1)
    //            $data['Depreciation_Rate_Percent'] = $data['Depreciation_Rate_Percent'] / 1200; //div by months and percent
    //        elseif ($data['Depreciation_Period_ID'] == 2)
    //            $data['Depreciation_Rate_Percent'] = $data['Depreciation_Rate_Percent'] / 400; //div by quartely and percent
    //        else
    //            $data['Depreciation_Rate_Percent'] = $data['Depreciation_Rate_Percent'] / 100; //div by percent only
    //        $data['Expense_Purchased_Date'] = $data['Expense_Date'];
    //        $stm = [
    //            &$data['JV_ID'],
    //            &$data['Supplier_ID'],
    //            &$data['Account_Code'],
    //            &$data['Expense_Total_Price'],
    //            &$data['Currency_ID'],
    //            &$data['Depreciation_Rate_Percent'],
    //            &$data['Depreciation_Period_ID'],
    //            &$data['Expense_Purchased_Date'],
    //            &$data['Depreciation_Rate_Description'],
    //            &$data['Supplier_Invoice_Number'],
    //            &$data['Is_Hidden']
    //        ];
    //        $this->db->prepare_Stmt("INSERT INTO 
    //                        tbl_accounting_depreciation_rate_data(
    //                            JV_ID,
    //                            Supplier_ID,
    //                            Account_ID,
    //                            Expense_Purchased_Amount,
    //                            Currency_ID,
    //                            Depreciation_Rate_Percent,
    //                            Depreciation_Period_ID,
    //                            Expense_Purchased_Date,
    //                            Depreciation_Rate_Description,
    //                            Supplier_Invoice_Number,
    //                            Is_Hidden
    //                        )
    //                        VALUES(
    //                            ?,?,?,?,?,?,?,?,?,?,?
    //                        )
    //                ", $stm);
    //        $this->dep_rate_id = $this->db->id();
    //        if ($data['Depreciation_Period_ID'] == 1)
    //            self::insertMonthlyDepreciationDates($data);
    //        if ($data['Depreciation_Period_ID'] == 2)
    //            self::insertQuartelyDepreciationDates($data);
    //        if ($data['Depreciation_Period_ID'] == 3)
    //            self::insertAnnualDepreciationDates($data);
    //    }
    //    public function insertAnnualDepreciationDates($dep) {
    //        $data = array();
    //        $year = substr($dep['Expense_Purchased_Date'], 0, 4);
    //        $month = substr($dep['Expense_Purchased_Date'], 5, 2);
    //        $day = substr($dep['Expense_Purchased_Date'], 8, 2);
    //        $amount_per_year = $dep['Expense_Total_Price'] * $dep['Depreciation_Rate_Percent']; //depreciation amount per year
    //        $years_num = $dep['Expense_Total_Price'] / $amount_per_year; //number of depreciation years
    //        //to check if purchased date is the last date of the year then the depreciation will start with the end of next year 
    //        if (($month == '12' and $day == '31')) {
    //            //loop on number of years of depreciation
    //            for ($i = 0; $i < $years_num; $i++) {
    //                $year++;
    //                $data[$i]['Depreciation_Date'] = $year . '-12-31';
    //                $data[$i]['Depreciation_Amount'] = $amount_per_year;
    //            }
    //        }
    //        //else first depreciation will occur in the current year then with next years
    //        else {
    //            // to calculate number of days from current date to the end of year so the depreciation will be calculated for these days only
    //            $diff = date_diff(date_create($dep['Expense_Purchased_Date']), date_create($year . '-12-31'));
    //            $first_days_num = $diff->format("%a");
    //            $rem = $year / 4;
    //            //if this year is a leap year then for feb there is 29 days else 28 days
    //            if (is_integer($rem))
    //                $total_year_days = "366";
    //            else
    //                $total_year_days = "365";
    //            //depreciation first amount depending on calculated days
    //            $data[0]['Depreciation_Date'] = $year . '-12-31';
    //            $data[0]['Depreciation_Amount'] = ($first_days_num / $total_year_days) * $amount_per_year;
    //            //sum to calculate later the remeining amount for the final year
    //            $sum_depreciation_amount = $data[0]['Depreciation_Amount'];
    //            //loop on number of years of depreciation excluding first and last year  because amounts and dates are different
    //            for ($i = 1; $i < $years_num - 1; $i++) {
    //                $year++;
    //                $data[$i]['Depreciation_Date'] = $year . '-12-31';
    //                $data[$i]['Depreciation_Amount'] = $amount_per_year;
    //                $sum_depreciation_amount += $amount_per_year;
    //            }
    //            if ($sum_depreciation_amount < $dep['Expense_Total_Price']) {
    //                $year++;
    //                $count = count($data);
    //                $data[$count]['Depreciation_Date'] = $year . '-' . $month . '-' . ($day - 1);
    //                $data[$count]['Depreciation_Amount'] = $dep['Expense_Total_Price'] - $sum_depreciation_amount;
    //            }
    //        }
    //        self::addDepreciationDates($data);
    //    }
    //    public function insertMonthlyDepreciationDates($dep) {
    //        $data = array();
    //        $year = substr($dep['Expense_Purchased_Date'], 0, 4);
    //        $month = substr($dep['Expense_Purchased_Date'], 5, 2);
    //        $day = substr($dep['Expense_Purchased_Date'], 8, 2);
    //        $amount_per_month = $dep['Expense_Total_Price'] * $dep['Depreciation_Rate_Percent']; //depreciation amount per month
    //        $months_num = $dep['Expense_Total_Price'] / $amount_per_month;
    //        $last_day_of_month = date("Y-m-t", strtotime($dep['Expense_Purchased_Date']));
    //        $k = 0;
    //        $sum = 0.0;
    //        //
    //        if ($month == 12) {
    //            $year++;
    //            $j = 0;
    //        } else
    //            $j = $month;
    //        //
    //        if ($dep['Expense_Purchased_Date'] != $last_day_of_month) {
    //            $diff = date_diff(date_create($dep['Expense_Purchased_Date']), date_create($last_day_of_month));
    //            $first_days_num = $diff->format("%a");
    ////            $num_of_days_of_month = substr($dep['Expense_Purchased_Date'],8,2);
    //            $months_num = $months_num - 1;
    //            $num_days_in_month = substr($last_day_of_month, 8, 2);
    //            $data[0]['Depreciation_Date'] = $last_day_of_month;
    //            $data[0]['Depreciation_Amount'] = ($first_days_num / $num_days_in_month) * $amount_per_month;
    //            $sum = $data[0]['Depreciation_Amount'];
    //            $k = 1;
    //        }
    //        $months_num = intval($months_num);
    //        for ($i = 0; $i < $months_num; $i++) {
    //            $j++;
    //            $current_month = $year . '-' . $j . '-' . '1';
    //            $last_day_of_month_current = date("Y-m-t", strtotime($current_month));
    //            $data[$k]['Depreciation_Date'] = $last_day_of_month_current;
    //            $data[$k]['Depreciation_Amount'] = $amount_per_month;
    //            $sum += $amount_per_month;
    //            $k++;
    //            if ($j == 12) {
    //                $j = 0;
    //                $year++;
    //            }
    //        }
    //        $j++;
    //        if ($sum < $dep['Expense_Total_Price']) {
    //            $date_final = $year . '-' . $j . '-' . $day;
    //            $data[$k]['Depreciation_Date'] = $date_final;
    //            $data[$k]['Depreciation_Amount'] = $dep['Expense_Total_Price'] - $sum;
    //        }
    //        self::addDepreciationDates($data);
    //    }
    //    public function insertQuartelyDepreciationDates($dep) {
    //        $data = array();
    //        $year = substr($dep['Expense_Purchased_Date'], 0, 4);
    //        $month = substr($dep['Expense_Purchased_Date'], 5, 2);
    //        $day = substr($dep['Expense_Purchased_Date'], 8, 2);
    //        $amount_per_quarte = $dep['Expense_Total_Price'] * $dep['Depreciation_Rate_Percent']; //depreciation amount per month
    //        $quart_num = $dep['Expense_Total_Price'] / $amount_per_quarte;
    //        $k = 0;
    //        $sum = 0.0;
    //
    //        if ($month <= 3) {
    //            if ($month == 3 && $day == 31) {
    //                $j = 3;
    //            } else {
    //                $j = 3;
    //                $diff = date_diff(date_create($dep['Expense_Purchased_Date']), date_create($year . '-03-31'));
    //                $first_days_num = $diff->format("%a");
    //                $quart_num -= 2;
    //                $data[0]['Depreciation_Date'] = $year . '-03-31';
    //                $data[0]['Depreciation_Amount'] = ($first_days_num / 90) * $amount_per_quarte;
    //                $sum = $data[0]['Depreciation_Amount'];
    //                $k = 1;
    //            }
    //        } else if ($month > 3 && $month <= 6) {
    //            if ($month == 6 && $day == 30) {
    //                $j = 6;
    //            } else {
    //                $j = 6;
    //                $diff = date_diff(date_create($dep['Expense_Purchased_Date']), date_create($year . '-06-30'));
    //                $first_days_num = $diff->format("%a");
    //                $quart_num -= 2;
    //                $data[0]['Depreciation_Date'] = $year . '-06-30';
    //                $data[0]['Depreciation_Amount'] = ($first_days_num / 91) * $amount_per_quarte;
    //                $sum = $data[0]['Depreciation_Amount'];
    //                $k = 1;
    //            }
    //        } else if ($month > 6 && $month <= 9) {
    //            if ($month == 9 && $day == 30) {
    //                $j = 9;
    //            } else {
    //                $j = 9;
    //                $diff = date_diff(date_create($dep['Expense_Purchased_Date']), date_create($year . '-09-30'));
    //                $first_days_num = $diff->format("%a");
    //                $quart_num -= 2;
    //                $data[0]['Depreciation_Date'] = $year . '-09-30';
    //                $data[0]['Depreciation_Amount'] = ($first_days_num / 92) * $amount_per_quarte;
    //                $sum = $data[0]['Depreciation_Amount'];
    //                $k = 1;
    //            }
    //        } else if ($month > 9) {
    //            if ($month == 12 && $day == 31) {
    //                $j = 6;
    //            } else {
    //                $j = 12;
    //                $diff = date_diff(date_create($dep['Expense_Purchased_Date']), date_create($year . '-12-31'));
    //                $first_days_num = $diff->format("%a");
    //                $quart_num -= 2;
    //                $data[0]['Depreciation_Date'] = $year . '-12-31';
    //                $data[0]['Depreciation_Amount'] = ($first_days_num / 92) * $amount_per_quarte;
    //                $sum = $data[0]['Depreciation_Amount'];
    //                $k = 1;
    //            }
    //        }
    //
    //        for ($i = 0; $i < $quart_num; $i++) {
    //            $j += 3;
    //            if ($j > 12) {
    //                $j = 3;
    //                $year++;
    //            }
    //            $current_month = $year . '-' . $j . '-' . '1';
    //            $last_day_of_month_current = date("Y-m-t", strtotime($current_month));
    //            $data[$k]['Depreciation_Date'] = $last_day_of_month_current;
    //            $data[$k]['Depreciation_Amount'] = $amount_per_quarte;
    //            $sum += $amount_per_quarte;
    //            $k++;
    //        }
    //        if ($sum < $dep['Expense_Total_Price']) {
    //            $date_final = $year . '-' . $month . '-' . $day;
    //            $data[$k]['Depreciation_Date'] = $date_final;
    //            $data[$k]['Depreciation_Amount'] = $dep['Expense_Total_Price'] - $sum;
    //        }
    //        self::addDepreciationDates($data);
    //    }

    private function addDepreciationDates($data) {
        foreach ($data as $item) {
            $det = [
                &$this->dep_rate_id,
                &$item['Depreciation_Date'],
                &$item['Depreciation_Amount']
            ];
            $this->db->prepare_Stmt("INSERT INTO 
                                            tbl_accounting_depreciation_dates(
                                                Depreciation_Rate_ID,
                                                Depreciation_Date,
                                                Depreciation_Amount
                                            )
                                            VALUES(
                                                ?,?,?
                                            )
            ", $det);
        }
    }

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

    public function add_expense() {
        $error = token_unique::is_valid_token();
        if (!empty($error))
            return $error;
//        if (!empty($this->info['unique_id']) && !empty(smc_session()['default_setting']['is_production'])) {
//            if (!isset(smc_session()['duplicate_check'][$this->info['unique_id']])) {
//                $error = array();
//                $error[0] = CommonLanguage::getMessage('duplicate_record');
//                return $error;
//            } else {
//                unset(smc_session()['duplicate_check'][$this->info['unique_id']]);
//            }
//        }
        $flag = $this->info['is_actual'];
        $check = CommonFunctions::check_lock_data(['type' => '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 = 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;
        $is_pending = !empty($this->info['is_pending']) ? $this->info['is_pending'] : 0;
        $is_cash = !empty($this->info['is_cash']) ? $this->info['is_cash'] : 0;
        $this->info['main_acc_id'] = !empty($this->info['main_acc_id']) ? $this->info['main_acc_id'] : null;
        $this->info['vat_acc_id'] = !empty($this->info['vat_acc_id']) ? $this->info['vat_acc_id'] : null;
        $expense_nb = $this->get_max_exp_nb($flag, $is_pending);
        $logged_id = smc_session()['user_logged_id'];
        $mydate = 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 = !empty($this->info['second_currency_rate']) ? $this->info['second_currency_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,
            &$mydate, &$mydate, &$logged_id, &$second_rate, &$this->info['project_id'], $is_pending, $is_cash,
            $this->info['main_acc_id'], $this->info['vat_acc_id']];
        $this->db->prepare_Stmt('insert into tbl_accounting_expenses (expense_nb,Expense_Date,Expense_DueDate,Expense_Total_Price,
            Currency_ID,current_rate,Expense_Note,Logged_ID,Supplier_ID,
            Expense_VAT,Is_Hidden,current_year_id,supplier_inv_id,branch_id,job_id,pay_method_acc_id,date_created,date_time,created_logged_id,
            second_currency_rate,project_id,is_pending,is_cash,main_acc_id,vat_acc_id
           ) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', $exp_data);
        $exp_id = $this->db->id();
        $arr_log = ['type' => 'exp', 'main_id' => $exp_id,
            'action' => 0, 'date_time' => $mydate,
            'user_id' => $logged_id];
        CommonFunctions::add_user_log($arr_log);

        //        $error = $this->db->error();
        //        echo $exp_id.'==';
        //        echo"<pre>";
        //        die(print_r($supp_acc));
        $job_trans_type = smc_session()['default_setting']['job_transaction_type'];
        $person_job = ($job_trans_type == 0 || $job_trans_type == 1 ? $this->info['job_id'] : 0);
        $opposite_job = ($job_trans_type == 0 || $job_trans_type == 2 ? $this->info['job_id'] : 0);
        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']];

            $this->db->prepare_Stmt('insert into tbl_accounting_expenses_details 
                (`Expense_ID`, `Account_ID`, `quantity`, `unit_price`, `vat_percent`, `vat_amount`,
                `net_total`, `description`,`paid_amount`) values(?,?,?,?,?,?,?,?,?)', $exp_det);
            $det_id = $this->db->id();
            if (!empty($exp['dep_rate']) && !empty($exp['Dep_Period'])) {
                $dep_data = [&$exp['dep_rate'], &$exp['Dep_Period'], &$exp['dep_desc'], &$exp_id, &$det_id, &$this->info['Expense_Date']];
                $this->db->prepare_Stmt('INSERT INTO `tbl_accounting_depreciation_rate_data`
                ( `Depreciation_Rate_Percent`, `Depreciation_Period_ID`,`Depreciation_Rate_Description`, 
                `Expense_ID`, `exp_detail_id`, `dep_start_date`) values(?,?,?,?,?,?)', $dep_data);
                $this->dep_rate_id = $this->db->id();
                //
                $dep_arr = ["Expense_Purchased_Date" => $this->info['Expense_Date'],
                    "Expense_Total_Price" => $exp['total'], "Depreciation_Rate_Percent" => $exp['dep_rate']];
                $this->insertPeriodDepreciationDates($dep_arr, $exp['Dep_Period']);
                $total_fa +=($exp['total'] + $exp['vat_amount']);
                $total_fa_vat +=$exp['vat_amount'];
                $total_fa_paid +=$exp['paid_amount'];
            } else {
                $total_exp +=($exp['total'] + $exp['vat_amount']);
                $total_exp_vat +=$exp['vat_amount'];
                $total_exp_paid +=$exp['paid_amount'];
            }
            if ($is_pending == 0) {
                //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,
                    "created_logged_id" => $logged_id,
                    "Transaction_Description" => $exp['exp_desc'],
                    "Current_Ex_Rate" => $rate,
                    "second_currency_rate" => $second_rate,
                    "main_id" => $exp_id,
                    "main_type" => 'exp',
                    "branch_id" => $this->info['branch_id'],
                    "main_nb" => $expense_nb,
                    "due_date" => $this->info['Expense_Date'],
                    "job_id" => $opposite_job,
                    "project_id" => $this->info['project_id']
                );
                JV::addTransaction($data_1, $flag);
            }
        }
        //        echo $total_exp_paid.'=='.$total_fa_paid;
        //        die();
        //        
        if ($is_pending == 0) {
            $exp_acc = !empty($this->info['main_acc_id']) ? $this->info['main_acc_id'] : $supp_acc['exp'];
            $exp_vat_acc = !empty($this->info['vat_acc_id']) ? $this->info['vat_acc_id'] : $supp_acc['exp_vat'];
            $fa_acc = !empty($this->info['main_acc_id']) ? $this->info['main_acc_id'] : $supp_acc['fa'];
            $fa_vat_acc = !empty($this->info['vat_acc_id']) ? $this->info['vat_acc_id'] : $supp_acc['fa_vat'];
            if (!empty(smc_session()['default_setting']['supplier_required_expense'])) {
                $data_2 = array(
                    "JV_ID" => $jv,
                    "Transaction_Reference" => $exp_id,
                    "Transaction_Account" => $exp_acc,
                    "Transaction_Type" => "Credit",
                    "Transaction_Amount" => $total_exp,
                    "Transaction_Date" => $this->info['Expense_Date'],
                    "Currency_ID" => $this->info['Currency_ID'],
                    "Logged_ID" => $logged_id,
                    "created_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" => 'exp',
                    "branch_id" => $this->info['branch_id'],
                    "job_id" => $person_job,
                    "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" => $exp_vat_acc,
                    "Transaction_Type" => "Debit",
                    "Transaction_Amount" => $total_exp_vat,
                    "Transaction_Date" => $this->info['Expense_Date'],
                    "Currency_ID" => $this->info['Currency_ID'],
                    "Logged_ID" => $logged_id,
                    "created_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" => 'exp',
                    "branch_id" => $this->info['branch_id'],
                    "main_nb" => $expense_nb,
                    "due_date" => $this->info['Expense_Date'],
                    "job_id" => $opposite_job,
                    "project_id" => $this->info['project_id']
                );
                JV::addTransaction($data_3, $flag);
                $data_4 = array(
                    "JV_ID" => $jv,
                    "Transaction_Reference" => $exp_id,
                    "Transaction_Account" => $fa_acc,
                    "Transaction_Type" => "Credit",
                    "Transaction_Amount" => $total_fa,
                    "Transaction_Date" => $this->info['Expense_Date'],
                    "Currency_ID" => $this->info['Currency_ID'],
                    "Logged_ID" => $logged_id,
                    "created_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" => 'exp',
                    "branch_id" => $this->info['branch_id'],
                    "main_nb" => $expense_nb,
                    "due_date" => $this->info['Expense_Date'],
                    "job_id" => $person_job,
                    "project_id" => $this->info['project_id']
                );
                JV::addTransaction($data_4, $flag);
                $data_5 = array(
                    "JV_ID" => $jv,
                    "Transaction_Reference" => $exp_id,
                    "Transaction_Account" => $fa_vat_acc,
                    "Transaction_Type" => "Debit",
                    "Transaction_Amount" => $total_fa_vat,
                    "Transaction_Date" => $this->info['Expense_Date'],
                    "Currency_ID" => $this->info['Currency_ID'],
                    "Logged_ID" => $logged_id,
                    "created_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" => 'exp',
                    "branch_id" => $this->info['branch_id'],
                    "main_nb" => $expense_nb,
                    "due_date" => $this->info['Expense_Date'],
                    "job_id" => $opposite_job,
                    "project_id" => $this->info['project_id']
                );
                JV::addTransaction($data_5, $flag);

                $data_7 = array(
                    "JV_ID" => $jv,
                    "Transaction_Reference" => $exp_id,
                    "Transaction_Account" => $exp_acc,
                    "Transaction_Type" => "Debit",
                    "Transaction_Amount" => $total_exp_paid,
                    "Transaction_Date" => $this->info['Expense_Date'],
                    "Currency_ID" => $this->info['Currency_ID'],
                    "Logged_ID" => $logged_id,
                    "created_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" => 'exp',
                    "branch_id" => $this->info['branch_id'],
                    "job_id" => $person_job,
                    "main_nb" => $expense_nb,
                    "due_date" => $this->info['Expense_Date'],
                    "project_id" => $this->info['project_id']
                );
                JV::addTransaction($data_7, $flag);
                $data_8 = array(
                    "JV_ID" => $jv,
                    "Transaction_Reference" => $exp_id,
                    "Transaction_Account" => $fa_acc,
                    "Transaction_Type" => "Debit",
                    "Transaction_Amount" => $total_fa_paid,
                    "Transaction_Date" => $this->info['Expense_Date'],
                    "Currency_ID" => $this->info['Currency_ID'],
                    "Logged_ID" => $logged_id,
                    "created_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" => 'exp',
                    "branch_id" => $this->info['branch_id'],
                    "job_id" => $person_job,
                    "main_nb" => $expense_nb,
                    "due_date" => $this->info['Expense_Date'],
                    "project_id" => $this->info['project_id']
                );
                JV::addTransaction($data_8, $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,
                "created_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" => 'exp',
                "branch_id" => $this->info['branch_id'],
                "job_id" => $opposite_job,
                "main_nb" => $expense_nb,
                "due_date" => $this->info['Expense_Date'],
                "project_id" => $this->info['project_id']
            );
            JV::addTransaction($data_6, $flag);
        }
        //        $data_6 = array(
        //            "JV_ID" => $jv,
        //            "Transaction_Reference" => $exp_id,
        //            "Transaction_Account" => $supp_acc['exp'],
        //            "Transaction_Type" => "Debit",
        //            "Transaction_Amount" => $paid_amount,
        //            "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,
        //            "main_id" => $exp_id,
        //            "main_type" => 'exp',
        //            "branch_id" => $this->info['branch_id']
        //        );
        //        JV::addTransaction($data_6, $flag);
        //        $data_7 = array(
        //            "JV_ID" => $jv,
        //            "Transaction_Reference" => 'credit',
        //            "Transaction_Account" =>$this->info['Card_Account'] ,
        //            "Transaction_Type" => "Credit",
        //            "Transaction_Amount" => $this->info['Card_Amount'],
        //            "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,
        //            "main_id" => $exp_id,
        //            "main_type" => 'exp',
        //            "branch_id" => $this->info['branch_id']
        //        );
        //        JV::addTransaction($data_7, $flag);
        //        $data_8 = array(
        //            "JV_ID" => $jv,
        //            "Transaction_Reference" => 'cash',
        //            "Transaction_Account" => $this->info['Cash_Account'],
        //            "Transaction_Type" => "Credit",
        //            "Transaction_Amount" => $this->info['Cash_Amount'],
        //            "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" => "0",
        //            "main_id" => $exp_id,
        //            "main_type" => 'exp',
        //            "branch_id" => $this->info['branch_id']
        //        );
        //        JV::addTransaction($data_8, $flag);
        //        $data_9 = array(
        //            "JV_ID" => $jv,
        //            "Transaction_Reference" => 'check',
        //            "Transaction_Account" => $this->info['Check_Account'],
        //            "Transaction_Type" => "Credit",
        //            "Transaction_Amount" => $this->info['Check_Amount'],
        //            "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" => "0",
        //            "main_id" => $exp_id,
        //            "main_type" => 'exp',
        //            "branch_id" => $this->info['branch_id']
        //        );
        //        JV::addTransaction($data_9, $flag);

        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
            token_unique::remove_token();
            return [$exp_id, $expense_nb];
        } else {
            $this->db->query('ROLLBACK');
            return $error;
        }
    }

    public function getExpenseByID($id) {
        $data = array();
        $exp_query = $this->db->query('select e.*,b.*,c.*,j.*,pr.*,coa.Account_Name as pay_method,
            ifnull(concat(m.Account_Code," - ",m.Account_Name),"") as main_acc_name,
            ifnull(concat(v.Account_Code," - ",v.Account_Name),"") as vat_acc_name
            from tbl_accounting_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
            left join tbl_chart_of_accounts m on m.Account_ID = e.main_acc_id
            left join tbl_chart_of_accounts v on v.Account_ID = e.vat_acc_id
            where 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.*,r.Depreciation_Rate_ID,r.Depreciation_Rate_Percent,
            r.Depreciation_Period_ID,r.Depreciation_Rate_Description,c.Account_Code,c.Account_Name
            from tbl_accounting_expenses_details d
            join tbl_chart_of_accounts c on c.Account_ID = d.Account_ID
            left join tbl_accounting_depreciation_rate_data r on r.exp_detail_id = d.exp_detail_id
            where d.Expense_ID="' . $id . '" order by d.exp_detail_id');
        while ($row = $this->db->assoc($det_query)) {
            $data['details'][] = $row;
        }
        //        $query = $this->db->query("SELECT 
        //                                            t.Transaction_Amount,
        //                                            t.Transaction_Reference,
        //                                            t.Account_ID,
        //                                            p.Account_Name AS P_Name
        //                                        FROM 
        //                                            tbl_accounting_transactions t
        //                                        LEFT JOIN 
        //                                            tbl_chart_of_accounts p
        //                                        ON 
        //                                            t.Account_ID = p.Account_ID
        //                                       WHERE 
        //                                             t.main_id = '" . $id . "'
        //                                        AND 
        //                                            t.main_type = 'exp'
        //                                        AND 
        //                                            t.Transaction_Reference in ('cash','check','credit')
        //                                        ");
        //        $data['Payment'] = array();
        //        while ($row = $this->db->assoc($query)) {
        //            if (stripos($row['Transaction_Reference'], 'cash') !== false)
        //                $data['Payment']['Cash'] = $row;
        //            if (stripos($row['Transaction_Reference'], 'check') !== false)
        //                $data['Payment']['Cheque'] = $row;
        //            if (stripos($row['Transaction_Reference'], 'credit') !== false)
        //                $data['Payment']['Credit_Card'] = $row;
        //        }
        return $data;
    }

    public function edit_expense() {
        $flag = $this->info['is_actual'];
        $year_id = $flag ? $this->year_id_actual : $this->year_id;
        $check = CommonFunctions::check_lock_data(['type' => '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= 'exp' 
                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);

        //select depreciation date ids where Is_Done=1 to keep on them when editing depreciation
        $dep_date_arr = array();
        $dep_date_query = $this->db->query('select Depreciation_Date_ID from tbl_accounting_depreciation_dates d
            join tbl_accounting_depreciation_rate_data r on r.Depreciation_Rate_ID = d.Depreciation_Rate_ID
            where r.Expense_ID = "' . $this->info['exp_id'] . '" and d.Is_Done=1');
        while ($dep_row = $this->db->assoc($dep_date_query)) {
            $dep_date_arr[] = $dep_row['Depreciation_Date_ID'];
        }
        $jv_arr = array();
        //        $dep_date_str = empty($dep_date_arr) ? '' : implode(',', $dep_date_arr);
        if (!empty($dep_date_arr)) {
            $jv_dep_query = $this->db->query("SELECT DISTINCT JV_ID FROM tbl_accounting_transactions  
            WHERE main_id in (" . implode(',', $dep_date_arr) . ") AND main_type= 'dep' 
                AND current_year_id='" . $year_id . "' and is_actual = '" . $flag . "' order by JV_ID desc");
            while ($row = $this->db->assoc($jv_dep_query)) {
                $jv_arr[] = $row['JV_ID'];
            }
        }
        $mydate = date("Y-m-d H:i:s");

        $old_created_logged_id = smc_session()['user_logged_id'];
        $old_date_created = $mydate;
        $exp_nb_query = $this->db->query('select expense_nb,date_created,created_logged_id
            from tbl_accounting_expenses where Expense_ID = "' . $this->info['exp_id'] . '"');
        $exp_query = $this->db->assoc($exp_nb_query);
        $exp_nb = !empty($this->info['expense_nb']) ? $this->info['expense_nb'] : $exp_query['expense_nb'];
        $old_created_logged_id = $exp_query['created_logged_id'];
        $old_date_created = $exp_query['date_created'];
        //        $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= 'exp' 
                AND current_year_id='" . $year_id . "' and is_actual = ?", [&$this->info['exp_id'], &$flag]);
        if (!empty($dep_date_arr)) {

            $this->db->prepare_Stmt("DELETE FROM tbl_accounting_transactions  
            WHERE main_id in (" . implode(',', $dep_date_arr) . ") and main_type='dep' 
                AND current_year_id='" . $year_id . "' and is_actual = ?", [ &$flag]);
        }
        $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;
        $logged_id = smc_session()['user_logged_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 = !empty($this->info['second_currency_rate']) ? $this->info['second_currency_rate'] : CurrencyFactory::getSecondCurrencyLastExRate($this->info['Currency_ID']);
        $is_pending = !empty($this->info['is_pending']) ? $this->info['is_pending'] : 0;
        $is_cash = !empty($this->info['is_cash']) ? $this->info['is_cash'] : 0;
        $this->info['main_acc_id'] = !empty($this->info['main_acc_id']) ? $this->info['main_acc_id'] : null;
        $this->info['vat_acc_id'] = !empty($this->info['vat_acc_id']) ? $this->info['vat_acc_id'] : null;
        $exp_data = [$exp_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'], &$this->info['Supplier_Invoice'], &$this->info['branch_id'], &$this->info['job_id'],
            &$this->info['project_id'], &$this->info['pay_method_acc_id'], &$mydate, &$second_rate, $is_pending, $is_cash,
            $this->info['main_acc_id'], $this->info['vat_acc_id'], &$this->info['exp_id']];
//        echo"<pre>";
//        die(print_r($exp_data));
        $this->db->prepare_Stmt('update tbl_accounting_expenses set expense_nb=?,Expense_Date=?,Expense_DueDate=?,Expense_Total_Price=?,
            Currency_ID=?,current_rate=?,Expense_Note=?,Logged_ID=?,Supplier_ID=?,
            Expense_VAT=?,supplier_inv_id=?,branch_id=?,job_id = ?,project_id=?,pay_method_acc_id = ?,
            date_time=?,second_currency_rate=?,is_pending=?,is_cash=?,main_acc_id=?,vat_acc_id=?
            where Expense_ID = ?', $exp_data);
        $exp_id = $this->info['exp_id'];
        $arr_log = ['type' => 'exp', 'main_id' => $exp_id,
            'action' => 1, 'date_time' => $mydate,
            'user_id' => $logged_id];
        CommonFunctions::add_user_log($arr_log);

        $this->db->prepare_Stmt('delete from tbl_accounting_expenses_details where Expense_ID = ?', [&$exp_id]);
        $this->db->prepare_Stmt('delete d.* from tbl_accounting_depreciation_dates d 
            join tbl_accounting_depreciation_rate_data r on r.Depreciation_Rate_ID = d.Depreciation_Rate_ID 
            where r.Expense_ID = ?', [&$exp_id]);
        $this->db->prepare_Stmt('delete from tbl_accounting_depreciation_rate_data where Expense_ID = ?', [&$exp_id]);

        //                $error = $this->db->error();
        //        //        echo $exp_id.'==';
        //        //        echo"<pre>";
        //                die(print_r($error));
        $job_trans_type = smc_session()['default_setting']['job_transaction_type'];
        $person_job = ($job_trans_type == 0 || $job_trans_type == 1 ? $this->info['job_id'] : 0);
        $opposite_job = ($job_trans_type == 0 || $job_trans_type == 2 ? $this->info['job_id'] : 0);
        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']];

            $this->db->prepare_Stmt('insert into tbl_accounting_expenses_details 
                (`Expense_ID`, `Account_ID`, `quantity`, `unit_price`, `vat_percent`, `vat_amount`,
                `net_total`, `description`,`paid_amount`) values(?,?,?,?,?,?,?,?,?)', $exp_det);
            $det_id = $this->db->id();
            if (!empty($exp['dep_rate']) && !empty($exp['Dep_Period'])) {
                $dep_data = [&$exp['dep_rate'], &$exp['Dep_Period'], &$exp['dep_desc'], &$exp_id, &$det_id, &$this->info['Expense_Date']];
                $this->db->prepare_Stmt('INSERT INTO `tbl_accounting_depreciation_rate_data`
                ( `Depreciation_Rate_Percent`, `Depreciation_Period_ID`,`Depreciation_Rate_Description`, 
                `Expense_ID`, `exp_detail_id`, `dep_start_date`) values(?,?,?,?,?,?)', $dep_data);
                $this->dep_rate_id = $this->db->id();

                $dep_arr = ["Expense_Purchased_Date" => $this->info['Expense_Date'],
                    "Expense_Total_Price" => $exp['total'], "Depreciation_Rate_Percent" => $exp['dep_rate']];
                //                $period_months = $exp['Dep_Period'] == 3 ? 12 : ($exp['Dep_Period'] == 2 ? 3 : 1);
                $this->insertPeriodDepreciationDates($dep_arr, $exp['Dep_Period']);
                $total_fa +=($exp['total'] + $exp['vat_amount']);
                $total_fa_vat +=$exp['vat_amount'];
                $total_fa_paid +=$exp['paid_amount'];
            } else {
                $total_exp +=($exp['total'] + $exp['vat_amount']);
                $total_exp_vat +=$exp['vat_amount'];
                $total_exp_paid +=$exp['paid_amount'];
            }

            if ($is_pending == 0) {
                //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,
                    "date_created" => $old_date_created,
                    "created_logged_id" => $old_created_logged_id,
                    "Transaction_Description" => $exp['exp_desc'],
                    "Current_Ex_Rate" => $rate,
                    "second_currency_rate" => $second_rate,
                    "main_id" => $exp_id,
                    "main_type" => 'exp',
                    "branch_id" => $this->info['branch_id'],
                    "due_date" => $this->info['Expense_DueDate'],
                    "main_nb" => $exp_nb,
                    "job_id" => $opposite_job,
                    "project_id" => $this->info['project_id']
                );
                JV::addTransaction($data_1, $flag);
            }
        }
        if ($is_pending == 0) {
            $exp_acc = !empty($this->info['main_acc_id']) ? $this->info['main_acc_id'] : $supp_acc['exp'];
            $exp_vat_acc = !empty($this->info['vat_acc_id']) ? $this->info['vat_acc_id'] : $supp_acc['exp_vat'];
            $fa_acc = !empty($this->info['main_acc_id']) ? $this->info['main_acc_id'] : $supp_acc['fa'];
            $fa_vat_acc = !empty($this->info['vat_acc_id']) ? $this->info['vat_acc_id'] : $supp_acc['fa_vat'];

            $data_2 = array(
                "JV_ID" => $jv,
                "Transaction_Reference" => $exp_id,
                "Transaction_Account" => $exp_acc,
                "Transaction_Type" => "Credit",
                "Transaction_Amount" => $total_exp,
                "Transaction_Date" => $this->info['Expense_Date'],
                "Currency_ID" => $this->info['Currency_ID'],
                "Logged_ID" => $logged_id,
                "date_created" => $old_date_created,
                "created_logged_id" => $old_created_logged_id,
                "Transaction_Description" => $this->info['Expense_Note'],
                "Current_Ex_Rate" => $rate,
                "second_currency_rate" => $second_rate,
                "main_id" => $exp_id,
                "main_type" => 'exp',
                "branch_id" => $this->info['branch_id'],
                "due_date" => $this->info['Expense_DueDate'],
                "main_nb" => $exp_nb,
                "job_id" => $person_job,
                "project_id" => $this->info['project_id']
            );
            JV::addTransaction($data_2, $flag);
            $data_3 = array(
                "JV_ID" => $jv,
                "Transaction_Reference" => $exp_id,
                "Transaction_Account" => $exp_vat_acc,
                "Transaction_Type" => "Debit",
                "Transaction_Amount" => $total_exp_vat,
                "Transaction_Date" => $this->info['Expense_Date'],
                "Currency_ID" => $this->info['Currency_ID'],
                "Logged_ID" => $logged_id,
                "date_created" => $old_date_created,
                "created_logged_id" => $old_created_logged_id,
                "Transaction_Description" => $this->info['Expense_Note'],
                "Current_Ex_Rate" => $rate,
                "second_currency_rate" => $second_rate,
                "main_id" => $exp_id,
                "main_type" => 'exp',
                "branch_id" => $this->info['branch_id'],
                "due_date" => $this->info['Expense_DueDate'],
                "main_nb" => $exp_nb,
                "job_id" => $opposite_job,
                "project_id" => $this->info['project_id']
            );
            JV::addTransaction($data_3, $flag);
            $data_4 = array(
                "JV_ID" => $jv,
                "Transaction_Reference" => $exp_id,
                "Transaction_Account" => $fa_acc,
                "Transaction_Type" => "Credit",
                "Transaction_Amount" => $total_fa,
                "Transaction_Date" => $this->info['Expense_Date'],
                "Currency_ID" => $this->info['Currency_ID'],
                "Logged_ID" => $logged_id,
                "date_created" => $old_date_created,
                "created_logged_id" => $old_created_logged_id,
                "Transaction_Description" => $this->info['Expense_Note'],
                "Current_Ex_Rate" => $rate,
                "second_currency_rate" => $second_rate,
                "main_id" => $exp_id,
                "main_type" => 'exp',
                "branch_id" => $this->info['branch_id'],
                "due_date" => $this->info['Expense_DueDate'],
                "main_nb" => $exp_nb,
                "job_id" => $person_job,
                "project_id" => $this->info['project_id']
            );
            JV::addTransaction($data_4, $flag);
            $data_5 = array(
                "JV_ID" => $jv,
                "Transaction_Reference" => $exp_id,
                "Transaction_Account" => $fa_vat_acc,
                "Transaction_Type" => "Debit",
                "Transaction_Amount" => $total_fa_vat,
                "Transaction_Date" => $this->info['Expense_Date'],
                "Currency_ID" => $this->info['Currency_ID'],
                "Logged_ID" => $logged_id,
                "date_created" => $old_date_created,
                "created_logged_id" => $old_created_logged_id,
                "Transaction_Description" => $this->info['Expense_Note'],
                "Current_Ex_Rate" => $rate,
                "second_currency_rate" => $second_rate,
                "main_id" => $exp_id,
                "main_type" => 'exp',
                "branch_id" => $this->info['branch_id'],
                "due_date" => $this->info['Expense_DueDate'],
                "main_nb" => $exp_nb,
                "job_id" => $opposite_job,
                "project_id" => $this->info['project_id']
            );
            JV::addTransaction($data_5, $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,
                "date_created" => $old_date_created,
                "created_logged_id" => $old_created_logged_id,
                "Transaction_Description" => $this->info['Expense_Note'],
                "Current_Ex_Rate" => $rate,
                "second_currency_rate" => $second_rate,
                "main_id" => $exp_id,
                "main_type" => 'exp',
                "branch_id" => $this->info['branch_id'],
                "job_id" => $opposite_job,
                "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" => $exp_acc,
                "Transaction_Type" => "Debit",
                "Transaction_Amount" => $total_exp_paid,
                "Transaction_Date" => $this->info['Expense_Date'],
                "Currency_ID" => $this->info['Currency_ID'],
                "Logged_ID" => $logged_id,
                "date_created" => $old_date_created,
                "created_logged_id" => $old_created_logged_id,
                "Transaction_Description" => $this->info['Expense_Note'],
                "Current_Ex_Rate" => $rate,
                "second_currency_rate" => $second_rate,
                "main_id" => $exp_id,
                "main_type" => 'exp',
                "branch_id" => $this->info['branch_id'],
                "job_id" => $person_job,
                "main_nb" => $exp_nb,
                "due_date" => $this->info['Expense_Date'],
                "project_id" => $this->info['project_id']
            );
            JV::addTransaction($data_7, $flag);
            $data_8 = array(
                "JV_ID" => $jv,
                "Transaction_Reference" => $exp_id,
                "Transaction_Account" => $fa_acc,
                "Transaction_Type" => "Debit",
                "Transaction_Amount" => $total_fa_paid,
                "Transaction_Date" => $this->info['Expense_Date'],
                "Currency_ID" => $this->info['Currency_ID'],
                "Logged_ID" => $logged_id,
                "date_created" => $old_date_created,
                "created_logged_id" => $old_created_logged_id,
                "Transaction_Description" => $this->info['Expense_Note'],
                "Current_Ex_Rate" => $rate,
                "second_currency_rate" => $second_rate,
                "main_id" => $exp_id,
                "main_type" => 'exp',
                "branch_id" => $this->info['branch_id'],
                "job_id" => $person_job,
                "main_nb" => $exp_nb,
                "due_date" => $this->info['Expense_Date'],
                "project_id" => $this->info['project_id']
            );

            JV::addTransaction($data_8, $flag);

            //re-insert previous dep
            $today = Date::getToday();
            $prev_data = Report::view_dep_trans($today);
            foreach ($prev_data as $prev) {
                if (!empty($jv_arr))
                    $cur_jv = array_pop($jv_arr);
                else
                    $cur_jv = JV::returnJV($prev['Is_Hidden']);
                $data_1 = array(
                    "JV_ID" => $cur_jv,
                    "Transaction_Reference" => $prev['Depreciation_Date_ID'],
                    "Transaction_Account" => $prev['dep_account'],
                    "Transaction_Type" => "Credit",
                    "Transaction_Amount" => $prev['Depreciation_Amount'],
                    "Transaction_Date" => $prev['Depreciation_Date'],
                    "Currency_ID" => $prev['Currency_ID'],
                    "Logged_ID" => $logged_id,
                    "Transaction_Description" => $prev['Depreciation_Rate_Description'],
                    "Current_Ex_Rate" => $rate,
                    "second_currency_rate" => $second_rate,
                    "main_id" => $prev['Depreciation_Date_ID'],
                    "main_type" => 'dep',
                    "branch_id" => $prev['branch_id'],
                    "job_id" => $prev['job_id'],
                    "project_id" => $prev['project_id'],
                );
                JV::addTransaction($data_1, $prev['Is_Hidden']);
                $data_2 = array(
                    "JV_ID" => $cur_jv,
                    "Transaction_Reference" => $prev['Depreciation_Date_ID'],
                    "Transaction_Account" => $prev['pro_account'],
                    "Transaction_Type" => "Debit",
                    "Transaction_Amount" => $prev['Depreciation_Amount'],
                    "Transaction_Date" => $prev['Depreciation_Date'],
                    "Currency_ID" => $prev['Currency_ID'],
                    "Logged_ID" => $logged_id,
                    "Transaction_Description" => $prev['Depreciation_Rate_Description'],
                    "Current_Ex_Rate" => $rate,
                    "second_currency_rate" => $second_rate,
                    "main_id" => $prev['Depreciation_Date_ID'],
                    "main_type" => 'dep',
                    "branch_id" => $prev['branch_id'],
                    "job_id" => $prev['job_id'],
                    "project_id" => $prev['project_id'],
                );
                JV::addTransaction($data_2, $prev['Is_Hidden']);
                $update = [&$prev['Depreciation_Date_ID']];
                $this->db->prepare_Stmt("Update 
                                                tbl_accounting_depreciation_dates
                                            SET 
                                                Is_Done = 1 
                                            WHERE 
                                                Depreciation_Date_ID = ?
                        ", $update);
            }
        }

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

        if (empty($error)) {
            $this->db->query('COMMIT');
            return[$exp_id, $exp_nb];
        } else {
            $this->db->query('ROLLBACK');
            return $error;
        }
    }

    public function insertPeriodDepreciationDates($dep, $dep_period_id) {
        //$period_months  = 3;    //each period how many month, ex: for monthly 1 [month], quarter it is 3 [months], year 12 [months]
        //depreciation percentage is for the whole year so for monthly we divide by 12 month, quarter by 4, ..
        if ($dep_period_id == 1)    //monthly
            $period_months = 1;
        elseif ($dep_period_id == 2)    //quarter
            $period_months = 3;
        else    //year
            $period_months = 12;
        //
        $data = array();
        $year = substr($dep['Expense_Purchased_Date'], 0, 4);
        $month = substr($dep['Expense_Purchased_Date'], 5, 2);
        $day = substr($dep['Expense_Purchased_Date'], 8, 2);
        //depreciation percentage is for the whole year [for 12 month] for that if monthly it must divide by 12, if quarter must divide by 4
        $dep_rate_per = $dep['Depreciation_Rate_Percent'] / (100 * (12 / $period_months));
        $period_amount = round(($dep['Expense_Total_Price'] * $dep_rate_per), 5);
        if ($period_amount == 0 || $dep['Expense_Total_Price'] == 0)
            return false;
        $period_count = intval($dep['Expense_Total_Price'] / $period_amount);
        //
        $frst_priod_mnth = floor($month / $period_months) * $period_months;
        $first_period_last_day = date("Y-m-t", strtotime(substr_replace($dep['Expense_Purchased_Date'], $frst_priod_mnth, 5, 2)));
        $first_period_frst_day = date("Y-m-01", strtotime(substr_replace($dep['Expense_Purchased_Date'], ($frst_priod_mnth - $period_months + 1), 5, 2)));
        $indx = 0;
        $remain = $dep['Expense_Total_Price'];
        //if the period already not from the start then take that period value and not full period amount
        if ($dep['Expense_Purchased_Date'] != $first_period_last_day) {
            $frst_priod_days_diff = date_diff(date_create($dep['Expense_Purchased_Date']), date_create($first_period_last_day));
            $first_period_days = $frst_priod_days_diff->format("%a");
            $period_count--;
            //note that this is differ depend on current month, period months,..
            $priod_days_diff = date_diff(date_create($first_period_frst_day), date_create($first_period_last_day));
            $num_days_in_period = $priod_days_diff->format("%a");
            $data[$indx]['Depreciation_Date'] = $first_period_last_day;
            $data[$indx]['Depreciation_Amount'] = (($period_amount / $num_days_in_period) * $first_period_days);
            $remain-= $data[$indx]['Depreciation_Amount'];
            $indx++;
        }
        //
        $crnt_priod_mnth = $frst_priod_mnth;
        //loop on the needed periods
        for ($i = 0; $i < $period_count; $i++) {
            $crnt_priod_mnth+=$period_months;
            if ($crnt_priod_mnth > 12) {
                $crnt_priod_mnth = $period_months;
                $year++;
            }
            //last day of the period
            $data[$indx]['Depreciation_Date'] = date("Y-m-t", strtotime("{$year}-{$crnt_priod_mnth}-01"));
            $data[$indx]['Depreciation_Amount'] = $period_amount;
            $remain-= $data[$indx]['Depreciation_Amount'];
            $indx++;
        }
        //maybe there is remain since in first period may not take the whole period amount
        if ($remain > 0.002) {
            $crnt_priod_mnth+=$period_months;
            if ($crnt_priod_mnth > 12) {
                $crnt_priod_mnth = $period_months;
                $year++;
            }
            //here till the $day not end of the month day since in first period we already remove the already passed days
            $data[$indx]['Depreciation_Date'] = "{$year}-{$crnt_priod_mnth}-{$day}";
            $data[$indx]['Depreciation_Amount'] = $remain;
        }
        //
        self::addDepreciationDates($data);
    }

    public function getExpenseToPrint($id) {
        $data = array();
        $exp_query = $this->db->query('select * from tbl_accounting_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
            where Expense_ID = "' . $id . '"');
        $row = $this->db->assoc($exp_query);
        $expense_total = $row['Expense_Total_Price'];
        $expense_vat = $row['Expense_VAT'];
        $prec = $row['Currency_ID'] == 84 ? 0 : 2;
        $row['Expense_VAT'] = CommonFunctions::public_round_number($row['Expense_VAT']);
        $row['Expense_Total_Price'] = CommonFunctions::public_round_number($row['Expense_Total_Price']);
        $row['expense_total_with_vat'] = CommonFunctions::public_round_number(($expense_total + $expense_vat));
        //
        $row['total_price_word_ar'] = Forms::num_to_word($row['Expense_Total_Price'], $row['Currency_ID'], 'ar');
        $row['total_price_word_en'] = Forms::num_to_word($row['Expense_Total_Price'], $row['Currency_ID'], 'en');


        $row['total_price_with_vat_word_ar'] = Forms::num_to_word($row['expense_total_with_vat'], $row['Currency_ID'], 'ar');
        $row['total_price_with_vat_word_en'] = Forms::num_to_word($row['expense_total_with_vat'], $row['Currency_ID'], 'en');

        $person = Supplier::getSupplierByID($row['Supplier_ID']);
        $data['main'] = array_merge($row, $person);
        //
        $det_query = $this->db->query('select d.*,c.Account_Code,c.Account_Name
            from tbl_accounting_expenses_details d
            join tbl_chart_of_accounts c on c.Account_ID = d.Account_ID
            where d.Expense_ID="' . $id . '" order by d.exp_detail_id');
        $i = 0;
        while ($row = $this->db->assoc($det_query)) {
            $unit_price = $row['unit_price'];
            $vat_amount = $row['vat_amount'];
            $net_total = $row['net_total'];
            $row['unit_price'] = CommonFunctions::public_round_number($unit_price);
            $row['vat_amount'] = CommonFunctions::public_round_number($vat_amount);
            $row['net_total'] = CommonFunctions::public_round_number($net_total);
            $row['total_with_vat'] = CommonFunctions::public_round_number(($net_total + $vat_amount));

    
            $row['count'] = ++$i;
            $data['details'][] = $row;
        }
        //        $query = $this->db->query("SELECT 
        //                                            t.Transaction_Amount,
        //                                            t.Transaction_Reference,
        //                                            t.Account_ID,
        //                                            p.Account_Name AS P_Name
        //                                        FROM 
        //                                            tbl_accounting_transactions t
        //                                        LEFT JOIN 
        //                                            tbl_chart_of_accounts p
        //                                        ON 
        //                                            t.Account_ID = p.Account_ID
        //                                       WHERE 
        //                                             t.main_id = '" . $id . "'
        //                                        AND 
        //                                            t.main_type = 'exp'
        //                                        AND 
        //                                            t.Transaction_Reference in ('cash','check','credit')
        //                                        ");
        //        $data['Payment'] = array();
        //        while ($row = $this->db->assoc($query)) {
        //            if (stripos($row['Transaction_Reference'], 'cash') !== false)
        //                $data['Payment']['Cash'] = $row;
        //            if (stripos($row['Transaction_Reference'], 'check') !== false)
        //                $data['Payment']['Cheque'] = $row;
        //            if (stripos($row['Transaction_Reference'], 'credit') !== false)
        //                $data['Payment']['Credit_Card'] = $row;
        //        }
        return $data;
    }

    public function delete_expense($ids) {

        if (smc_session()['year_closed'] == 1) {
            $error = array();
            $error[0] = CommonLanguage::getMessage('delete_year_alert');
            return $error;
        }

        $mydate = date("Y-m-d H:i:s");
        $this->db->query('START TRANSACTION');
        foreach ($ids as $id) {
            $query = $this->db->query('select Expense_Date,Is_Hidden from tbl_accounting_expenses
                where Expense_ID = "' . $id . '"');
            $row = $this->db->assoc($query);
            $check = CommonFunctions::check_lock_data(['type' => 'expense', 'start_date' => $row['Expense_Date'], 'is_actual' => $row['Is_Hidden']]);
            if ($check !== '') {
                continue;
            }
            $dep_ids = array();
            $query = $this->db->query('select Depreciation_Date_ID 
            from tbl_accounting_depreciation_dates join tbl_accounting_depreciation_rate_data 
            using(Depreciation_Rate_ID) where Expense_ID = "' . $id . '"');
            while ($row = $this->db->assoc($query)) {
                $dep_ids[] = $row['Depreciation_Date_ID'];
            }

            if (!empty($dep_ids)) {
                $this->db->prepare_Stmt('delete from tbl_accounting_transactions where main_type = "dep" 
            and main_id in (' . implode(',', $dep_ids) . ')');
            }
            $this->db->prepare_Stmt('delete from tbl_accounting_transactions where main_type = "exp" 
            and main_id = ? ', [&$id]);
            $this->db->prepare_Stmt('delete from tbl_accounting_expenses_details where Expense_ID = ?', [&$id]);
            $this->db->prepare_Stmt('delete d.* from tbl_accounting_depreciation_dates d 
            join tbl_accounting_depreciation_rate_data r on r.Depreciation_Rate_ID = d.Depreciation_Rate_ID 
            where r.Expense_ID = ?', [&$id]);
            $this->db->prepare_Stmt('delete from tbl_accounting_depreciation_rate_data where Expense_ID = ?', [&$id]);

            $this->db->prepare_Stmt('delete from tbl_accounting_expenses where Expense_ID = ?', [&$id]);
            $arr_log = ['type' => 'exp', 'main_id' => $id,
                'action' => 2, 'date_time' => $mydate,
                'user_id' => smc_session()['user_logged_id']];
            CommonFunctions::add_user_log($arr_log);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function fix_negative_expenses() {
        $this->db->query('START TRANSACTION');
        $query = $this->db->query('SELECT * FROM `tbl_accounting_expenses` WHERE `Expense_Total_Price` < 0');
        while ($row = $this->db->assoc($query)) {
            $jv = JV::returnJV();
            $data_1 = array(
                "JV_ID" => $jv,
                "Transaction_Reference" => $row['Expense_ID'],
                "Transaction_Account" => 3602,
                "Transaction_Type" => "Credit",
                "Transaction_Amount" => abs($row['Expense_Total_Price']),
                "Transaction_Date" => $row['Expense_Date'],
                "Currency_ID" => $row['Currency_ID'],
                "Logged_ID" => $row['Logged_ID'],
                "created_logged_id" => $row['Logged_ID'],
                "Transaction_Description" => $row['Expense_Note'],
                "Current_Ex_Rate" => $row['current_rate'],
                "second_currency_rate" => $row['second_currency_rate'],
                "main_id" => $row['Expense_ID'],
                "main_type" => 'mjv',
                "branch_id" => $row['branch_id'],
                "main_nb" => $row['expense_nb'],
                "due_date" => $row['Expense_DueDate'],
                "job_id" => $row['job_id'],
                "project_id" => $row['project_id']
            );
            JV::addTransaction($data_1);
            $data_1 = array(
                "JV_ID" => $jv,
                "Transaction_Reference" => $row['Expense_ID'],
                "Transaction_Account" => 3574,
                "Transaction_Type" => "Debit",
                "Transaction_Amount" => abs($row['Expense_Total_Price']),
                "Transaction_Date" => $row['Expense_Date'],
                "Currency_ID" => $row['Currency_ID'],
                "Logged_ID" => $row['Logged_ID'],
                "created_logged_id" => $row['Logged_ID'],
                "Transaction_Description" => $row['Expense_Note'],
                "Current_Ex_Rate" => $row['current_rate'],
                "second_currency_rate" => $row['second_currency_rate'],
                "main_id" => $row['Expense_ID'],
                "main_type" => 'mjv',
                "branch_id" => $row['branch_id'],
                "main_nb" => $row['expense_nb'],
                "due_date" => $row['Expense_DueDate'],
                "job_id" => $row['job_id'],
                "project_id" => $row['project_id']
            );
            JV::addTransaction($data_1);
            $data_1 = array(
                "JV_ID" => $jv,
                "Transaction_Reference" => $row['Expense_ID'],
                "Transaction_Account" => 2097,
                "Transaction_Type" => "Debit",
                "Transaction_Amount" => abs($row['Expense_Total_Price']),
                "Transaction_Date" => $row['Expense_Date'],
                "Currency_ID" => $row['Currency_ID'],
                "Logged_ID" => $row['Logged_ID'],
                "created_logged_id" => $row['Logged_ID'],
                "Transaction_Description" => $row['Expense_Note'],
                "Current_Ex_Rate" => $row['current_rate'],
                "second_currency_rate" => $row['second_currency_rate'],
                "main_id" => $row['Expense_ID'],
                "main_type" => 'mjv',
                "branch_id" => $row['branch_id'],
                "main_nb" => $row['expense_nb'],
                "due_date" => $row['Expense_DueDate'],
                "job_id" => $row['job_id'],
                "project_id" => $row['project_id']
            );
            JV::addTransaction($data_1);
            $data_1 = array(
                "JV_ID" => $jv,
                "Transaction_Reference" => $row['Expense_ID'],
                "Transaction_Account" => 3574,
                "Transaction_Type" => "Credit",
                "Transaction_Amount" => abs($row['Expense_Total_Price']),
                "Transaction_Date" => $row['Expense_Date'],
                "Currency_ID" => $row['Currency_ID'],
                "Logged_ID" => $row['Logged_ID'],
                "created_logged_id" => $row['Logged_ID'],
                "Transaction_Description" => $row['Expense_Note'],
                "Current_Ex_Rate" => $row['current_rate'],
                "second_currency_rate" => $row['second_currency_rate'],
                "main_id" => $row['Expense_ID'],
                "main_type" => 'mjv',
                "branch_id" => $row['branch_id'],
                "main_nb" => $row['expense_nb'],
                "due_date" => $row['Expense_DueDate'],
                "job_id" => $row['job_id'],
                "project_id" => $row['project_id']
            );
            JV::addTransaction($data_1);
        }
        $this->db->query('delete FROM `tbl_accounting_expenses` WHERE `Expense_Total_Price` < 0');

        $error = $this->db->error();
        if (empty($error))
            $this->db->query('COMMIT');
        else
            $this->db->query('ROLLBACK');
        echo"<pre>";
        die(print_r($error));
    }

    public function get_total_expenses($from_date, $to_date) {
        $condition = '';
        $total = 0;
        if (!empty($this->info['branch_id']))
            $condition = ' AND branch_id = "' . $this->info['branch_id'] . '"';
        $query = $this->db->query('select sum(Expense_Total_Price*current_rate) as total_expense
            from tbl_accounting_expenses where  Expense_Date between  "' . $from_date . '" and "' . $to_date . '"' . $condition);
        $total = $this->db->assoc($query)['total_expense'];
//        $total = CurrencyFactory::returnPriceInDefaultCurrencyValue(smc_session()['local_currency'], $total);


        $query = $this->db->query('select sum(Truck_Expense_Amount*current_rate) as total_expense
            from tbl_garage_truck_expenses where  Truck_Expense_Date between  "' . $from_date . '" and "' . $to_date . '"' . $condition);
        $total+= $this->db->assoc($query)['total_expense'];
        $total = CurrencyFactory::returnPriceInDefaultCurrencyValue(smc_session()['local_currency'], $total);


        return $total;
    }

    public function get_total_expense_list() {
        $condition = '';
        if (isset($this->info['Expense_List_Date_From']) && isset($this->info['Expense_List_Date_To'])) {
            $FromDate = $this->info['Expense_List_Date_From'];
            $ToDate = $this->info['Expense_List_Date_To'];
            $condition = ' and Expense_Date >= "' . $FromDate . '" AND Expense_Date <= "' . $ToDate . '"';
        }
        if (isset($this->info['is_pending']))
            $condition.=' and is_pending = "' . $this->info['is_pending'] . '" ';
        if (!empty($this->info['search_note']))
            $condition.=' and Expense_Note like "%' . $this->info['search_note'] . '%" ';
        if ($this->permissions->hasPermission('Branches')) {
            $condition .= !empty($_GET['search_branch_id']) ? ' and branch_id ="' . ($_GET['search_branch_id']) . '"' : '';
        } else {
            $condition .= ' and branch_id ="' . (smc_session()['user_accounts']['branch_id']) . '"';
        }
                        
        $condition .= ' AND Is_Hidden = "' . $this->info['Is_Actual'] . '" AND current_year_id = "' . $this->year_id . '"';
        $query = $this->db->query('select sum(Expense_Total_Price*current_rate) as total_first,
                                          sum(Expense_Total_Price*second_currency_rate) as total_second
            from tbl_accounting_expenses where 1=1 ' . $condition);
        $row = $this->db->assoc($query);
        $totals = CommonFunctions::formatNumber($row['total_first']) . ' ' . smc_session()['local_currency_symbol'] . '<br>';
        // HIDE HIDE yousifhide
        if (smc_session()['default_setting']['activate_second_currency'] == 1)
        $totals .= CommonFunctions::formatNumber($row['total_second']) . ' ' . smc_session()['second_currency']['symbol'];
        return $totals;
    }

    public function settle_expense($id) {
        $data = array();
        $query = $this->db->query('SELECT `Expense_ID` as exp_id,`Is_Hidden` as is_actual,`Expense_Date`,
            `Supplier_ID`,`current_rate` as Currency_Rate,`second_currency_rate`,0 as `is_pending`,
            `Expense_DueDate`,Expense_Total_Price as expense_no_vat,`Currency_ID`,`Expense_Description` as Expense_Note,
            `Expense_VAT`, `supplier_inv_id` as Supplier_Invoice,`branch_id`,`job_id`,
            `pay_method_acc_id`,0 as total_paid FROM `tbl_accounting_expenses` where Expense_ID = "' . $id . '"');
        $data = $this->db->assoc($query);
        //
        $query = $this->db->query('SELECT d.`Account_ID` as exp_acc,d.`quantity` as exp_qty,
            d.`unit_price` as unit_cost,d.`vat_percent`,d.`vat_amount`,d.`net_total` as total,
            d.`description` as exp_desc,d.`paid_amount`,r.Depreciation_Rate_Percent as dep_rate,
            r.Depreciation_Period_ID as Dep_Period,
            r.Depreciation_Rate_Description as dep_desc FROM `tbl_accounting_expenses_details` d 
           left join tbl_accounting_depreciation_rate_data r on r.exp_detail_id = d.`exp_detail_id`
            WHERE d.`Expense_ID`= "' . $id . '"');
        while ($row = $this->db->assoc($query)) {
            $data['total_paid']+=$row['paid_amount'];
            $data['expense'][] = $row;
        }
//        echo"<pre>";
//        die(print_r($data));
        $data['expense_nb'] = $this->get_max_exp_nb($data['is_actual'], 0);
        $error = $this->setData($data)->edit_expense();
        return $error;
    }

    public function change_expense_actual($id_arr, $is_actual, $is_pending) {
        $this->db->query('START TRANSACTION');
        $date_time = date('Y-m-d H:i:s');
        $logged_id = smc_session()['user_logged_id'];
        foreach ($id_arr as $id) {
            $exp_nb = $expense_nb = $this->get_max_exp_nb($is_actual, $is_pending);
            $this->db->prepare_Stmt('update tbl_accounting_expenses set expense_nb = ?,Is_Hidden=?
            where Expense_ID = ?', [&$exp_nb, &$is_actual, &$id]);
            $jv = JV::returnJV($is_actual);
            $this->db->prepare_Stmt('update tbl_accounting_transactions set main_nb= ?,Transaction_Reference=?, 
            is_actual = ?,JV_ID = ?,Logged_ID=?,date_time = ? where main_id = ? 
            and main_type="exp"', [&$exp_nb, $exp_nb, &$is_actual, &$jv, $logged_id, $date_time, &$id]);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

}
