<?php

class JV {

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

    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(Session::getUserID());
    }

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

    public function createTransaction($flag = null) {
        $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']]);
//            }
//        }
        $actual = is_null($flag) ? 0 : 1;
        $check = CommonFunctions::check_lock_data(['type' => 'mjv', 'start_date' => $this->info['Transaction_Date'], 'is_actual' => $is_actual]);

//        $check = JV::checkCurrentYear($this->info['Transaction_Date'], $this->info['Transaction_Date'], $actual);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $mydate = date("Y-m-d H:i:s");
        $this->db->query("START TRANSACTION");
        $jv = JV::returnJV($actual);

        $query_branch = $this->db->query('select branch_prefix from tbl_organization_branches where branch_id = "' . $this->info['branch_id'] . '"');
        $branch_prefix = $this->db->assoc($query_branch)['branch_prefix'];
        $prefix = smc_session()['user_accounts']['serial_prefix'] ? smc_session()['user_accounts']['serial_prefix'] : $branch_prefix;
        $jv_nb = JV::return_max_jv_nb($actual, $prefix);
        for ($i = 0; $i < count($this->info['Accounts']); $i++) {
//            echo (($this->info['Accounts'][$i]['Current_Ex_Rate'] == '')?"Aa":"bbb").'==';
//            if ($this->info['Accounts'][$i]['Current_Ex_Rate'] == '') {
//                $rate = CurrencyFactory::getLastExRate($this->info['Accounts'][$i]['Currency_ID']);
//            } else {
//                $rate = $this->info['Accounts'][$i]['Current_Ex_Rate'];
//            }
//            if ($this->info['Accounts'][$i]['second_currency_rate'] == '') {
//                $second_rate = CurrencyFactory::getSecondCurrencyLastExRate($this->info['Accounts'][$i]['Currency_ID']);
//            } else {
//                $second_rate = $this->info['Accounts'][$i]['second_currency_rate'];
//            }
            $this->info['Accounts'][$i]['job_id'] = empty($this->info['Accounts'][$i]['job_id']) ? null : $this->info['Accounts'][$i]['job_id'];
            $user_ref = !empty($this->info['Accounts'][$i]['user_ref']) ? $this->info['Accounts'][$i]['user_ref'] : $this->info['jv_note'];
            $data = array(
                "JV_ID" => $jv,
                "Transaction_Reference" => $this->info['Accounts'][$i]['Transaction_Reference'],
                "user_ref" => $user_ref,
                "Transaction_Account" => $this->info['Accounts'][$i]['Account_ID'],
                "Transaction_Type" => $this->info['Accounts'][$i]['Transcation_Type'],
                "Transaction_Amount" => $this->info['Accounts'][$i]['Transcation_Amount'],
                "Transaction_Date" => $this->info['Transaction_Date'],
                "Currency_ID" => $this->info['Accounts'][$i]['Currency_ID'],
                "Logged_ID" => $this->info[0],
                "Transaction_Description" => $this->info['Accounts'][$i]['Transaction_Description'],
                "Current_Ex_Rate" => $this->info['Accounts'][$i]['Current_Ex_Rate'],
                "second_currency_rate" => $this->info['Accounts'][$i]['second_currency_rate'],
                "main_id" => "0",
                "main_type" => 'mjv',
                "branch_id" => $this->info['branch_id'],
                "job_id" => $this->info['Accounts'][$i]['job_id'],
                "project_id" => $this->info['Accounts'][$i]['project_id'],
                "due_date" => $this->info['Accounts'][$i]['due_date'],
                "subtype" => $this->info['Accounts'][$i]['subtype'],
                "prefix" => $prefix,
                "main_nb" => $jv_nb,
                //we need this in case if user want to insert 0 local or second rate
                "keep_rate" => true
            );
            JV::addTransaction($data, $actual);
            $trans_id = $this->db->id();
            $close_arr = !empty($this->info['Accounts'][$i]['close_arr']) ? json_decode($this->info['Accounts'][$i]['close_arr'], true) : array();
            if (isset($close_arr)) {
                $rec_type = $this->info['Accounts'][$i]['acc_type'] == 'cust' ? 'cmjv' : 'smjv';
                $row_data = ['rate' => $this->info['Accounts'][$i]['Current_Ex_Rate']];
                CommonBusiness::close_invoices($close_arr, $trans_id, $rec_type, $row_data);
            }
        }
        $arr_log = ['type' => 'mjv', 'main_id' => $jv,
            'action' => 0, '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');
            token_unique::remove_token();
            return $jv;
        } else {
            $this->db->query('ROLLBACK');
            return array_unique($error);
        }
    }

    public function addPrepaid() {
        //Account_Code account of prepaid for expense or income
        //amount permonth must be calculated
        //must make sure of accounts
        $this->db->query('START TRANSACTION');
        $check = CommonFunctions::check_lock_data(['type' => 'prepaid', 'start_date' => $this->info['Start_Date'], 'is_actual' => $this->info['is_actual']]);

//        $check = JV::checkCurrentYear($this->info['Start_Date'], $this->info['Start_Date'], $this->info['is_actual']);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $flag = $this->info['is_actual'];
        if ($this->info['is_actual'] == 1) {
            //            $flag = 0;
            $year_id = $this->year_id_actual;
        } else {
            //            $flag = null;
            $year_id = $this->year_id;
        }
        $rate = !empty($this->info['current_rate']) ? $this->info['current_rate'] : CurrencyFactory::getLastExRate($this->info['Currency_ID']);
        $second_rate = CurrencyFactory::getSecondCurrencyLastExRate($this->info['Currency_ID']);
        $data = [
            &$this->info['Account_Code'],
            &$this->info['Prepaid_Account'],
            &$this->info['Payment_ID'],
            &$this->info['Supplier_ID'],
            &$this->info['Total_Amount'],
            &$this->info['Currency_ID'],
            &$this->info['Amount_PerMonth'],
            &$this->info['Start_Date'],
            &$this->info['End_Date'],
            &$this->info['Description'],
            &$this->info[0],
            &$this->info['Paid_Amount'],
            &$this->info['is_actual'],
            &$this->info['is_expense'],
            &$year_id, &$rate, &$second_rate, &$this->info['branch_id'], &$this->info['job_id'], &$this->info['project_id']
        ];
        $this->db->prepare_Stmt("INSERT INTO
                                tbl_accounting_prepaid_expense_income (
                                    Account_ID,
                                    Prepaid_Account_ID,
                                    Payment_ID,
                                    Supplier_ID,
                                    Total_Amount,
                                    Currency_ID,
                                    Amount_PerMonth,
                                    Start_Date,
                                    End_Date,
                                    Description,
                                    Logged_ID,
                                    Paid_Amount,
                                    Is_Actual,
                                    is_expense,
                                    current_year_id,current_rate,second_currency_rate,branch_id,job_id,project_id
                                )
                                VALUES (
                                    ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?
                                )
        ", $data);
        $ref_id = $this->db->id();
        $jv = JV::returnJV($flag);
        $payment_code = $this->info['Payment_ID'];
        $account_code = self::getAccountCodeByID($this->info['Account_Code']);

        if (substr($account_code, 0, 1) == 6 or substr($account_code, 0, 1) == 2) {
            $supp_account = self::returnSupplierCode($account_code, $this->info['Supplier_ID']);
            $credit_account = $supp_account;
            $debit_account = $this->info['Prepaid_Account'];
            $credit_account_pay = $payment_code;
            $debit_account_pay = $supp_account;
        } else {
            $supp_account = Customer::getCustomerAccount($this->info['Supplier_ID'])['Account_ID'];
            $credit_account = $this->info['Prepaid_Account'];
            $debit_account = $supp_account;
            $credit_account_pay = $supp_account;
            $debit_account_pay = $payment_code;
        }
        $data_1 = array(
            "JV_ID" => $jv,
            "Transaction_Reference" => $ref_id,
            "Transaction_Account" => $credit_account,
            "Transaction_Type" => "Credit",
            "Transaction_Amount" => $this->info['Total_Amount'],
            "Transaction_Date" => $this->info['Start_Date'],
            "Currency_ID" => $this->info['Currency_ID'],
            "Logged_ID" => $this->info[0],
            "Transaction_Description" => $this->info['Description'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "branch_id" => $this->info['branch_id'],
            "main_id" => $ref_id,
            "main_type" => 'pre',
            "job_id" => $this->info['job_id'],
            "project_id" => $this->info['project_id']
        );
        JV::addTransaction($data_1, $flag);
        $data_2 = array(
            "JV_ID" => $jv,
            "Transaction_Reference" => $ref_id,
            "Transaction_Account" => $debit_account,
            "Transaction_Type" => "Debit",
            "Transaction_Amount" => $this->info['Total_Amount'],
            "Transaction_Date" => $this->info['Start_Date'],
            "Currency_ID" => $this->info['Currency_ID'],
            "Logged_ID" => $this->info[0],
            "Transaction_Description" => $this->info['Description'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "branch_id" => $this->info['branch_id'],
            "main_id" => $ref_id,
            "main_type" => 'pre',
            "job_id" => $this->info['job_id'],
            "project_id" => $this->info['project_id']
        );
        JV::addTransaction($data_2, $flag);
        $data_3 = array(
            "JV_ID" => $jv,
            "Transaction_Reference" => $ref_id,
            "Transaction_Account" => $credit_account_pay,
            "Transaction_Type" => "Credit",
            "Transaction_Amount" => $this->info['Paid_Amount'],
            "Transaction_Date" => $this->info['Start_Date'],
            "Currency_ID" => $this->info['Currency_ID'],
            "Logged_ID" => $this->info[0],
            "Transaction_Description" => $this->info['Description'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "branch_id" => $this->info['branch_id'],
            "main_id" => $ref_id,
            "main_type" => 'pre',
            "job_id" => $this->info['job_id'],
            "project_id" => $this->info['project_id']
        );
        self::addTransaction($data_3, $flag);
        $data_4 = array(
            "JV_ID" => $jv,
            "Transaction_Reference" => $ref_id,
            "Transaction_Account" => $debit_account_pay,
            "Transaction_Type" => "Debit",
            "Transaction_Amount" => $this->info['Paid_Amount'],
            "Transaction_Date" => $this->info['Start_Date'],
            "Currency_ID" => $this->info['Currency_ID'],
            "Logged_ID" => $this->info[0],
            "Transaction_Description" => $this->info['Description'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "branch_id" => $this->info['branch_id'],
            "main_id" => $ref_id,
            "main_type" => 'pre',
            "job_id" => $this->info['job_id'],
            "project_id" => $this->info['project_id']
        );
        self::addTransaction($data_4, $flag);
        $mydate = date('Y-m-d H:i:s');
        $arr_log = ['type' => 'pre', 'main_id' => $ref_id,
            'action' => 0, 'date_time' => $mydate,
            'user_id' => smc_session()['user_logged_id']];
        CommonFunctions::add_user_log($arr_log);
        self::addMonthlyPrepaidData($ref_id);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    private function addMonthlyPrepaidData($ref_id) {
        $exp_data = $this->info;
        $data = array();
        if ($exp_data['Prepaid_ID'] == 0)
            $prepaid_id = $ref_id;
        else
            $prepaid_id = $exp_data['Prepaid_ID'];
        $year = substr($exp_data['Start_Date'], 0, 4);
        $month = substr($exp_data['Start_Date'], 5, 2);
        $day = substr($exp_data['Start_Date'], 8, 2);
        $amount_per_month = $exp_data['Amount_PerMonth'];
        $months_num = $exp_data['Total_Amount'] / $amount_per_month;
        $last_day_of_month = date("Y-m-t", strtotime($exp_data['Start_Date']));
        $k = 0;
        $sum = 0.0;
        $j = $month;
        //        if ($last_day_of_month == $exp_data['Start_Date'] && $month == 12)
        //            $j = 0;
        //        else if ($last_day_of_month == $exp_data['Start_Date'] && $month < 12)
        //            $j = $month;
        //        else
        if ($last_day_of_month != $exp_data['Start_Date']) {
            //            $j = $month;
            $diff = date_diff(date_create($exp_data['Start_Date']), date_create($last_day_of_month));
            $first_days_num = $diff->format("%a");
            $num_of_days_of_month = substr($exp_data['Start_Date'], 8, 2);
            $months_num = $months_num - 1;
            $num_days_in_month = substr($last_day_of_month, 8, 2);
            $data[0]['Transaction_Date'] = $last_day_of_month;
            $data[0]['Transaction_Amount'] = ($first_days_num / $num_days_in_month) * $amount_per_month;
            $sum = $data[0]['Transaction_Amount'];
            $k = 1;
        }
        $months_num = intval($months_num);
        if ($j >= 12) {
            $j = 0;
            $year++;
        }
        for ($i = 0; $i < $months_num; $i++) {
            $j++;
            $current_month = "{$year}-{$j}-01";
            $last_day_of_month_current = date("Y-m-t", strtotime($current_month));
            $data[$k]['Transaction_Date'] = $last_day_of_month_current;
            $data[$k]['Transaction_Amount'] = $amount_per_month;
            $sum += $amount_per_month;
            $k++;
            if ($j >= 12) {
                $j = 0;
                $year++;
            }
        }
        $j++;
        if ($sum < $exp_data['Total_Amount']) {
            //            $date_final = $year . '-' . $j . '-' . $day;
            //we stop above since error if $day 31 and $j month is now 2 feb or ..
            $date_final = date("Y-m-t", strtotime("{$year}-{$j}-01"));
            $data[$k]['Transaction_Date'] = $date_final;
            $data[$k]['Transaction_Amount'] = $exp_data['Total_Amount'] - $sum;
        }
        $data['Prepaid_ID'] = $prepaid_id;
        self::addPrepaidData($data);
    }

    private function addPrepaidData($data) {

        $prepaid_id = $data['Prepaid_ID'];
        array_pop($data);

        foreach ($data as $item) {
            $prp = [
                &$prepaid_id,
                &$item['Transaction_Date'],
                &$item['Transaction_Amount']
            ];
            $this->db->prepare_Stmt("INSERT INTO 
                                    tbl_accounting_prepaid_data(
                                        Prepaid_ID,
                                        Transaction_Date,
                                        Transaction_Amount
                                    )
                                    VALUES(
                                    ?,?,?
                                    )
            ", $prp);
        }
    }

    public function updatePrepaid() {
        $check = CommonFunctions::check_lock_data(['type' => 'prepaid', 'start_date' => $this->info['Start_Date'], 'is_actual' => $this->info['is_actual']]);

//        $check = JV::checkCurrentYear($this->info['Start_Date'], $this->info['Start_Date'], $this->info['is_actual']);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $this->db->query("START TRANSACTION");
        $rate = !empty($this->info['current_rate']) ? $this->info['current_rate'] : CurrencyFactory::getLastExRate($this->info['Currency_ID']);
        $second_rate = CurrencyFactory::getSecondCurrencyLastExRate($this->info['Currency_ID']);
        $data = [
            &$this->info['Account_Code'],
            &$this->info['Prepaid_Account'],
            &$this->info['Payment_ID'],
            &$this->info['Supplier_ID'],
            &$this->info['Total_Amount'],
            &$this->info['Currency_ID'],
            &$this->info['Amount_PerMonth'],
            &$this->info['Start_Date'],
            &$this->info['End_Date'],
            &$this->info['Description'],
            &$this->info[0],
            &$this->info['Paid_Amount'], &$rate, &$second_rate, &$this->info['branch_id'],
            &$this->info['job_id'], &$this->info['project_id'],
            &$this->info['Prepaid_ID']
        ];
        $this->db->prepare_Stmt("UPDATE 
                                tbl_accounting_prepaid_expense_income 
                            SET
                                Account_ID    =  ?,
                                Prepaid_Account_ID =  ?,
                                Payment_ID      =  ?,
                                Supplier_ID     =  ?,
                                Total_Amount    =  ?,
                                Currency_ID     =  ?,
                                Amount_PerMonth =  ?,
                                Start_Date      =  ?,
                                End_Date        =  ?,
                                Description     =  ?,
                                Logged_ID       =  ?,
                                Paid_Amount     =  ?,current_rate=?,second_currency_rate=?,branch_id=?,
                                job_id=?,project_id=?
                            WHERE 
                                Prepaid_ID      =  ?
        ", $data);

        $this->db->prepare_Stmt("DELETE FROM tbl_accounting_prepaid_data WHERE Prepaid_ID = ?", [&$this->info['Prepaid_ID']]);
        self::addMonthlyPrepaidData($this->info);

        $ref_id = $this->info['Prepaid_ID'];
        $jv = JV::returnJVID($ref_id, 'pre', $this->info['is_actual']);
        $del = [&$ref_id];
        $this->db->prepare_Stmt("DELETE FROM tbl_accounting_transactions WHERE main_id = ? and main_type='pre'
            and is_actual='" . $this->info['is_actual'] . "'", $del);
        $payment_code = $this->info['Payment_ID'];
        $account_code = self::getAccountCodeByID($this->info['Account_Code']);
        $supp_account = self::returnSupplierCode($account_code, $this->info['Supplier_ID']);
        if (substr($account_code, 0, 1) == 6 or substr($account_code, 0, 1) == 2) {
            $credit_account = $supp_account;
            $debit_account = $this->info['Prepaid_Account'];
            $credit_account_pay = $payment_code;
            $debit_account_pay = $supp_account;
        } else {
            $credit_account = $this->info['Prepaid_Account'];
            $debit_account = $supp_account;
            $credit_account_pay = $supp_account;
            $debit_account_pay = $payment_code;
        }
        $data_1 = array(
            "JV_ID" => $jv,
            "Transaction_Reference" => $ref_id,
            "Transaction_Account" => $credit_account,
            "Transaction_Type" => "Credit",
            "Transaction_Amount" => $this->info['Total_Amount'],
            "Transaction_Date" => $this->info['Start_Date'],
            "Currency_ID" => $this->info['Currency_ID'],
            "Logged_ID" => $this->info[0],
            "Transaction_Description" => $this->info['Description'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "branch_id" => $this->info['branch_id'],
            "main_id" => $this->info['Prepaid_ID'],
            "main_type" => 'pre',
            "job_id" => $this->info['job_id'],
            "project_id" => $this->info['project_id']
        );

        JV::addTransaction($data_1, $this->info['is_actual']);

        $data_2 = array(
            "JV_ID" => $jv,
            "Transaction_Reference" => $ref_id,
            "Transaction_Account" => $debit_account,
            "Transaction_Type" => "Debit",
            "Transaction_Amount" => $this->info['Total_Amount'],
            "Transaction_Date" => $this->info['Start_Date'],
            "Currency_ID" => $this->info['Currency_ID'],
            "Logged_ID" => $this->info[0],
            "Transaction_Description" => $this->info['Description'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "branch_id" => $this->info['branch_id'],
            "main_id" => $this->info['Prepaid_ID'],
            "main_type" => 'pre',
            "job_id" => $this->info['job_id'],
            "project_id" => $this->info['project_id']
        );

        JV::addTransaction($data_2, $this->info['is_actual']);

        $data_3 = array(
            "JV_ID" => $jv,
            "Transaction_Reference" => $ref_id,
            "Transaction_Account" => $credit_account_pay,
            "Transaction_Type" => "Credit",
            "Transaction_Amount" => $this->info['Paid_Amount'],
            "Transaction_Date" => $this->info['Start_Date'],
            "Currency_ID" => $this->info['Currency_ID'],
            "Logged_ID" => $this->info[0],
            "Transaction_Description" => $this->info['Description'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "branch_id" => $this->info['branch_id'],
            "main_id" => $this->info['Prepaid_ID'],
            "main_type" => 'pre',
            "job_id" => $this->info['job_id'],
            "project_id" => $this->info['project_id']
        );
        JV::addTransaction($data_3, $this->info['is_actual']);
        $data_4 = array(
            "JV_ID" => $jv,
            "Transaction_Reference" => $ref_id,
            "Transaction_Account" => $debit_account_pay,
            "Transaction_Type" => "Debit",
            "Transaction_Amount" => $this->info['Paid_Amount'],
            "Transaction_Date" => $this->info['Start_Date'],
            "Currency_ID" => $this->info['Currency_ID'],
            "Logged_ID" => $this->info[0],
            "Transaction_Description" => $this->info['Description'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "branch_id" => $this->info['branch_id'],
            "main_id" => $this->info['Prepaid_ID'],
            "main_type" => 'pre',
            "job_id" => $this->info['job_id'],
            "project_id" => $this->info['project_id']
        );
        JV::addTransaction($data_4, $this->info['is_actual']);
        $mydate = date('Y-m-d H:i:s');
        $arr_log = ['type' => 'pre', 'main_id' => $ref_id,
            'action' => 1, '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 array_unique($error);
    }

    //    public function returnPrepaids($date = null) {
    //        //return prepaids with status 1 and on definite date
    //        $condition = '';
    //        $data = array();
    //        if (!is_null($date))
    //            $condition .= ' AND (Start_Date < "' . $date . '" AND End_Date >= "' . $date . '")';
    //        $query = $this->db->query('SELECT
    //                                        *
    //                                    FROM
    //                                        tbl_accounting_prepaid_expense_income
    //                                    WHERE
    //                                        Status_ID = 1
    //                                    AND
    //                                        Prepaid_Account NOT IN (
    //                                            SELECT Transaction_Account FROM tbl_accounting_transactions WHERE Transaction_Date = "' . $date . '"
    //                                        )
    //                ' . $condition);
    //        while ($row = $this->db->assoc($query))
    //            $data[] = $row;
    //        return $data;
    //    }

    private function returnPrepaidForTransactions($date) {
        $data = array();
        $query = $this->db->query('SELECT 
                                        p.*,
                                        d.*,
                                        a.Account_Code as Main_Account_Code
                                    FROM 
                                        tbl_accounting_prepaid_data d
                                    LEFT JOIN 
                                        tbl_accounting_prepaid_expense_income p
                                        join tbl_chart_of_accounts a on a.Account_ID = p.Account_ID
                                    ON 
                                        d.Prepaid_ID = p.Prepaid_ID
                                    WHERE  
                                        d.Is_Done = 0
                                    AND 
                                        Transaction_Date <= "' . $date . '"
                ');
        while ($row = $this->db->assoc($query))
            $data[] = $row;
        return $data;
    }

    public function addMonthlyPrepaidTransactions($date) {
        $this->db->query("START TRANSACTION");
        $data = self::returnPrepaidForTransactions($date);
        foreach ($data as $item) {
            if (substr($item['Main_Account_Code'], 0, 1) == 6 or substr($item['Main_Account_Code'], 0, 1) == 2) {
                $debit_account = $item['Account_ID'];
                $credit_account = $item['Prepaid_Account_ID'];
            } else {
                $debit_account = $item['Prepaid_Account_ID'];
                $credit_account = $item['Account_ID'];
            }
            //ref_id: return the jv of the main prepaid to save it as reference for the new monthly transaction
            //$ref_id = self::returnOldJV($item['Prepaid_ID'],$item['Start_Date'],$item['Description']);
            $jv = JV::returnJV($item['Is_Actual']);
            $data_1 = array(
                "JV_ID" => $jv,
                "Transaction_Reference" => $item['Prepaid_ID'],
                "Transaction_Account" => $debit_account,
                "Transaction_Type" => "Debit",
                "Transaction_Amount" => $item['Transaction_Amount'],
                "Transaction_Date" => $item['Transaction_Date'],
                "Currency_ID" => $item['Currency_ID'],
                "Logged_ID" => $item['Logged_ID'],
                "Transaction_Description" => $item['Description'],
                "Current_Ex_Rate" => "0",
                "main_id" => $item['Prepaid_ID'],
                "job_id" => $item['job_id'],
                "branch_id" => $item['branch_id'],
                "project_id" => $item['project_id'],
                "main_type" => 'pre'
            );
            JV::addTransaction($data_1, $item['Is_Actual']);

            $data_2 = array(
                "JV_ID" => $jv,
                "Transaction_Reference" => $item['Prepaid_ID'],
                "Transaction_Account" => $credit_account,
                "Transaction_Type" => "Credit",
                "Transaction_Amount" => $item['Transaction_Amount'],
                "Transaction_Date" => $item['Transaction_Date'],
                "Currency_ID" => $item['Currency_ID'],
                "Logged_ID" => $item['Logged_ID'],
                "Transaction_Description" => $item['Description'],
                "Current_Ex_Rate" => "0",
                "main_id" => $item['Prepaid_ID'],
                "job_id" => $item['job_id'],
                "branch_id" => $item['branch_id'],
                "project_id" => $item['project_id'],
                "main_type" => 'pre'
            );
            JV::addTransaction($data_2, $item['Is_Actual']);
            $update = [&$item['Prepaid_Data_ID']];
            $this->db->prepare_Stmt("Update 
                                    tbl_accounting_prepaid_data
                                SET 
                                    Is_Done = 1 
                                WHERE 
                                    Prepaid_Data_ID = ?
            ", $update);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function returnMaxPrepaidID() {
        $max = $this->db->query('SELECT MAX(Prepaid_ID) AS max_id FROM tbl_accounting_prepaid_expense_income');
        $row = $this->db->assoc($max);
        $id = $row['max_id'];
        return $id;
    }

    public function returnOldJV($id, $date, $desc) {
        $query = $this->db->query('SELECT
                                        JV_ID
                                    FROM
                                        tbl_accounting_transactions
                                    WHERE
                                        Transaction_Reference = "' . $id . '"
                                    AND
                                        Transaction_Date = "' . $date . '"
                                    AND
                                        Transaction_Description = "' . $desc . '"
                                    GROUP BY
                                        JV_ID

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

    //    public function paymentTypeCode($id) {
    //        $query = $this->db->query('SELECT
    //                                        Account_Code
    //                                    FROM
    //                                        tbl_payment_types
    //                                    WHERE
    //                                        Payment_Type_ID = "' . $id . '"
    //                                ');
    //        $row = $this->db->assoc($query);
    //        return $row['Account_Code'];
    //    }

    public function returnSupplierCode($account_code, $supp_id) {
        $condition = '';
        if (substr($account_code, 0, 1) == 2)
            $condition .= ' AND c.sub_type = "fa"';
        if (substr($account_code, 0, 1) == 6)
            $condition .= ' AND c.sub_type = "exp"';
        $supplier = $this->db->query('SELECT
                                            c.Account_ID
                                        FROM
                                            tbl_chart_of_accounts c 
                                        LEFT JOIN
                                            tbl_stock_suppliers s
                                        ON
                                            s.Supplier_ID = c.main_id
                                        WHERE
                                            c.main_id = "' . $supp_id . '"
                                        AND
                                            c.main_type="supp"
                                        ' . $condition);
        $row2 = $this->db->assoc($supplier);
        return $row2['Account_ID'];
    }

    public function addAccruedExpense() {
        $check = CommonFunctions::check_lock_data(['type' => 'accrued', 'start_date' => $this->info['Start_Date'], 'is_actual' => $this->info['is_actual']]);

//        $check = JV::checkCurrentYear($this->info['Start_Date'], $this->info['Start_Date'], $this->info['is_actual']);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $this->db->query("START TRANSACTION");
        $flag = $this->info['is_actual'];
        //        if ($this->info['is_actual'] == 1) {
        //            $flag = "1";
        $year = $flag ? $this->year_id_actual : $this->year_id;
        //        } else {
        //            $flag = null;
        //            $year = $this->year_id;
        //        }
        $rate = !empty($this->info['current_rate']) ? $this->info['current_rate'] : CurrencyFactory::getLastExRate($this->info['Currency_ID']);
        $second_rate = CurrencyFactory::getSecondCurrencyLastExRate($this->info['Currency_ID']);
        $data = [
            &$this->info['Expense_Code'],
            &$this->info['AccruedlExpense_Code'],
            &$this->info['Expected_Amount'],
            &$this->info['Currency_ID'],
            &$this->info['Description'],
            &$this->info['Start_Date'],
            &$this->info['End_Date'],
            &$this->info['Supplier_ID'],
            &$this->info['is_actual'],
            &$this->info[0],
            &$year, &$rate, &$second_rate, &$this->info['branch_id'], &$this->info['job_id'], &$this->info['project_id']
        ];
        $this->db->prepare_Stmt("INSERT INTO
                                        tbl_accounting_accrued_expenses (
                                            Account_ID,
                                            Accrued_Account_ID,
                                            Expected_Amount,
                                            Currency_ID,
                                            Description,
                                            Start_Date,
                                            End_Date,
                                            Supplier_ID,
                                            Is_Actual,
                                            Logged_ID,
                                            current_year_id,current_rate,second_currency_rate,branch_id,job_id,project_id
                                        )
                                        VALUES (
                                            ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?
                                        )
                ", $data);
        $ref_id = $this->db->id();
        $jv = JV::returnJV($flag);
        $data_1 = array(
            "JV_ID" => $jv,
            "Transaction_Reference" => $ref_id,
            "Transaction_Account" => $this->info['AccruedlExpense_Code'],
            "Transaction_Type" => "Credit",
            "Transaction_Amount" => $this->info['Expected_Amount'],
            "Transaction_Date" => $this->info['Start_Date'],
            "Currency_ID" => $this->info['Currency_ID'],
            "Logged_ID" => $this->info[0],
            "Transaction_Description" => $this->info['Description'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "branch_id" => $this->info['branch_id'],
            "main_id" => $ref_id,
            "main_type" => 'aex',
            "job_id" => $this->info['job_id'],
            "project_id" => $this->info['project_id']
        );
        JV::addTransaction($data_1, $flag);
        $data_2 = array(
            "JV_ID" => $jv,
            "Transaction_Reference" => $ref_id,
            "Transaction_Account" => $this->info['Expense_Code'],
            "Transaction_Type" => "Debit",
            "Transaction_Amount" => $this->info['Expected_Amount'],
            "Transaction_Date" => $this->info['Start_Date'],
            "Currency_ID" => $this->info['Currency_ID'],
            "Logged_ID" => $this->info[0],
            "Transaction_Description" => $this->info['Description'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "branch_id" => $this->info['branch_id'],
            "main_id" => $ref_id,
            "main_type" => 'aex',
            "job_id" => $this->info['job_id'],
            "project_id" => $this->info['project_id']
        );
        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 updateAccruedExpense() {
        $old_accrued = self::viewAccruedExpense($this->info['Accrued_Expense_ID']);
        $check = CommonFunctions::check_lock_data(['type'=>'accrued','start_date'=>$this->info['Start_Date'],'is_actual'=>$old_accrued['Is_Actual']]);

//        $check = JV::checkCurrentYear($this->info['Start_Date'], $this->info['Start_Date'], $old_accrued['Is_Actual']);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $rate = !empty($this->info['current_rate']) ? $this->info['current_rate'] : CurrencyFactory::getLastExRate($this->info['Currency_ID']);
        $second_rate = CurrencyFactory::getSecondCurrencyLastExRate($this->info['Currency_ID']);
        $this->db->query("START TRANSACTION");
        $data = [
            &$this->info['Expense_Code'],
            &$this->info['AccruedlExpense_Code'],
            &$this->info['Expected_Amount'],
            &$this->info['Currency_ID'],
            &$this->info['Description'],
            &$this->info['Start_Date'],
            &$this->info['End_Date'],
            &$this->info['Supplier_ID'],
            &$this->info[0], &$rate, &$second_rate, &$this->info['branch_id'], &$this->info['job_id'], &$this->info['project_id'],
            &$this->info['Accrued_Expense_ID']
        ];
        $this->db->prepare_Stmt("UPDATE 
                                        tbl_accounting_accrued_expenses 
                                    SET
                                        Account_ID    =  ?,
                                        Accrued_Account_ID    =  ?,
                                        Expected_Amount    =  ?,
                                        Currency_ID    =  ?,
                                        Description    =  ?,
                                        Start_Date    =  ?,
                                        End_Date    =  ?,
                                        Supplier_ID    =  ?,
                                        Logged_ID    =  ?,current_rate=?,second_currency_rate=?,branch_id = ?,job_id=?,project_id=?
                                    WHERE 
                                        Accrued_Expense_ID      =  ?
                ", $data);
        $ref_id = $this->info['Accrued_Expense_ID'];
        $jv = self::returnNeededTransactions($old_accrued['Accrued_Expense_ID'], $old_accrued['Description']); //return transaction happend upon this prepaid
        $del = [&$jv, &$old_accrued['Is_Actual']];
        $this->db->prepare_Stmt("Delete from tbl_accounting_transactions WHERE JV_ID = ? and is_actual = ?", $del);
        $data_1 = array(
            "JV_ID" => $jv,
            "Transaction_Reference" => $ref_id,
            "Transaction_Account" => $this->info['AccruedlExpense_Code'],
            "Transaction_Type" => "Credit",
            "Transaction_Amount" => $this->info['Expected_Amount'],
            "Transaction_Date" => $this->info['Start_Date'],
            "Currency_ID" => $this->info['Currency_ID'],
            "Logged_ID" => $this->info[0],
            "Transaction_Description" => $this->info['Description'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "branch_id" => $this->info['branch_id'],
            "main_id" => $this->info['Accrued_Expense_ID'],
            "main_type" => 'aex',
            "job_id" => $this->info['job_id'],
            "project_id" => $this->info['project_id']
        );
        JV::addTransaction($data_1, $old_accrued['Is_Actual']);
        $data_2 = array(
            "JV_ID" => $jv,
            "Transaction_Reference" => $ref_id,
            "Transaction_Account" => $this->info['Expense_Code'],
            "Transaction_Type" => "Debit",
            "Transaction_Amount" => $this->info['Expected_Amount'],
            "Transaction_Date" => $this->info['Start_Date'],
            "Currency_ID" => $this->info['Currency_ID'],
            "Logged_ID" => $this->info[0],
            "Transaction_Description" => $this->info['Description'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "branch_id" => $this->info['branch_id'],
            "main_id" => $this->info['Accrued_Expense_ID'],
            "main_type" => 'aex',
            "job_id" => $this->info['job_id'],
            "project_id" => $this->info['project_id']
        );
        JV::addTransaction($data_2, $old_accrued['Is_Actual']);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function returnMaxAccruedExpenseID() {
        $max = $this->db->query('SELECT MAX(Accrued_Expense_ID) AS max_id FROM tbl_accounting_accrued_expenses');
        $row = $this->db->assoc($max);
        $id = $row['max_id'];
        return $id;
    }

    public function payForAccruedExpense() {
        $this->db->query("START TRANSACTION");
        $old_accrued = self::viewAccruedExpense($this->info['ID']);
        $is_actual = $old_accrued['Is_Actual'];
        $data = [
            &$old_accrued['Account_ID'],
            &$old_accrued['Accrued_Account_ID'],
            &$old_accrued['Expected_Amount'],
            &$this->info['Real_Amount'],
            &$this->info['Real_Payment_Type'],
            &$this->info['Currency_ID'],
            &$old_accrued['Description'],
            &$old_accrued['Start_Date'],
            &$this->info['End_Date'],
            &$old_accrued['Supplier_ID'],
            &$this->info[0],
            &$this->info['ID']
        ];
        $this->db->prepare_Stmt("UPDATE 
                                        tbl_accounting_accrued_expenses 
                                    SET
                                        Account_ID            =  ?,
                                        Accrued_Account_ID    =  ?,
                                        Expected_Amount         =  ?,
                                        Real_Amount             =  ?,
                                        Payment_ID              =  ?,
                                        Currency_ID             =  ?,
                                        Description             =  ?,
                                        Start_Date              =  ?,
                                        End_Date                =  ?,
                                        Supplier_ID             =  ?,
                                        Logged_ID               =  ?
                                    WHERE 
                                        Accrued_Expense_ID      =  ?
                ", $data);
        $ref_id = $this->info['ID'];
        $jv = JV::returnJV($is_actual);
        $payment_code = $this->info['Real_Payment_Type'];
        if ($old_accrued['Expected_Amount'] == $this->info['Real_Amount']) {
            $data_1 = array(
                "JV_ID" => $jv,
                "Transaction_Reference" => $ref_id,
                "Transaction_Account" => $old_accrued['Accrued_Account_ID'],
                "Transaction_Type" => "Debit",
                "Transaction_Amount" => $this->info['Real_Amount'],
                "Transaction_Date" => $this->info['End_Date'],
                "Currency_ID" => $this->info['Currency_ID'],
                "Logged_ID" => $this->info[0],
                "Transaction_Description" => $old_accrued['Description'],
                "Current_Ex_Rate" => "0",
                "main_id" => $this->info['ID'],
                "main_type" => 'aex',
                "job_id" => $old_accrued['job_id'],
                "project_id" => $old_accrued['project_id'],
                "branch_id" => $old_accrued['branch_id']
            );
            JV::addTransaction($data_1, $is_actual);
            $data_2 = array(
                "JV_ID" => $jv,
                "Transaction_Reference" => $ref_id,
                "Transaction_Account" => $payment_code,
                "Transaction_Type" => "Credit",
                "Transaction_Amount" => $this->info['Real_Amount'],
                "Transaction_Date" => $this->info['End_Date'],
                "Currency_ID" => $this->info['Currency_ID'],
                "Logged_ID" => $this->info[0],
                "Transaction_Description" => $old_accrued['Description'],
                "Current_Ex_Rate" => "0",
                "main_id" => $this->info['ID'],
                "main_type" => 'aex',
                "job_id" => $old_accrued['job_id'],
                "project_id" => $old_accrued['project_id'],
                "branch_id" => $old_accrued['branch_id']
            );
            JV::addTransaction($data_2, $is_actual);
        } elseif ($old_accrued['Expected_Amount'] < $this->info['Real_Amount']) {
            $amount_diff = $this->info['Real_Amount'] - $old_accrued['Expected_Amount'];
            $data_1 = array(
                "JV_ID" => $jv,
                "Transaction_Reference" => $ref_id,
                "Transaction_Account" => $old_accrued['Accrued_Account_ID'],
                "Transaction_Type" => "Debit",
                "Transaction_Amount" => $old_accrued['Expected_Amount'],
                "Transaction_Date" => $this->info['End_Date'],
                "Currency_ID" => $this->info['Currency_ID'],
                "Logged_ID" => $this->info[0],
                "Transaction_Description" => $old_accrued['Description'],
                "Current_Ex_Rate" => "0",
                "main_id" => $this->info['ID'],
                "main_type" => 'aex',
                "job_id" => $old_accrued['job_id'],
                "project_id" => $old_accrued['project_id'],
                "branch_id" => $old_accrued['branch_id']
            );
            JV::addTransaction($data_1, $is_actual);
            $data_2 = array(
                "JV_ID" => $jv,
                "Transaction_Reference" => $ref_id,
                "Transaction_Account" => $old_accrued['Account_ID'],
                "Transaction_Type" => "Debit",
                "Transaction_Amount" => $amount_diff,
                "Transaction_Date" => $this->info['End_Date'],
                "Currency_ID" => $this->info['Currency_ID'],
                "Logged_ID" => $this->info[0],
                "Transaction_Description" => $old_accrued['Description'],
                "Current_Ex_Rate" => "0",
                "main_id" => $this->info['ID'],
                "main_type" => 'aex',
                "job_id" => $old_accrued['job_id'],
                "project_id" => $old_accrued['project_id'],
                "branch_id" => $old_accrued['branch_id']
            );
            JV::addTransaction($data_2, $is_actual);
            $data_3 = array(
                "JV_ID" => $jv,
                "Transaction_Reference" => $ref_id,
                "Transaction_Account" => $payment_code,
                "Transaction_Type" => "Credit",
                "Transaction_Amount" => $this->info['Real_Amount'],
                "Transaction_Date" => $this->info['End_Date'],
                "Currency_ID" => $this->info['Currency_ID'],
                "Logged_ID" => $this->info[0],
                "Transaction_Description" => $old_accrued['Description'],
                "Current_Ex_Rate" => "0",
                "main_id" => $this->info['ID'],
                "main_type" => 'aex',
                "job_id" => $old_accrued['job_id'],
                "project_id" => $old_accrued['project_id'],
                "branch_id" => $old_accrued['branch_id']
            );
            JV::addTransaction($data_3, $is_actual);
        } else {
            //must record later,need to be accurate: ask mr. ali
            $amount_diff = $old_accrued['Expected_Amount'] - $this->info['Real_Amount'];
            $data_1 = array(
                "JV_ID" => $jv,
                "Transaction_Reference" => $ref_id,
                "Transaction_Account" => $old_accrued['Accrued_Account_ID'],
                "Transaction_Type" => "Debit",
                "Transaction_Amount" => $old_accrued['Expected_Amount'],
                "Transaction_Date" => $this->info['End_Date'],
                "Currency_ID" => $this->info['Currency_ID'],
                "Logged_ID" => $this->info[0],
                "Transaction_Description" => $old_accrued['Description'],
                "Current_Ex_Rate" => "0",
                "main_id" => $this->info['ID'],
                "main_type" => 'aex',
                "job_id" => $old_accrued['job_id'],
                "project_id" => $old_accrued['project_id'],
                "branch_id" => $old_accrued['branch_id']
            );
            JV::addTransaction($data_1, $is_actual);
            $data_2 = array(
                "JV_ID" => $jv,
                "Transaction_Reference" => $ref_id,
                "Transaction_Account" => $old_accrued['Account_ID'],
                "Transaction_Type" => "Debit",
                "Transaction_Amount" => $amount_diff,
                "Transaction_Date" => $this->info['End_Date'],
                "Currency_ID" => $this->info['Currency_ID'],
                "Logged_ID" => $this->info[0],
                "Transaction_Description" => $old_accrued['Description'],
                "Current_Ex_Rate" => "0",
                "main_id" => $this->info['ID'],
                "main_type" => 'aex',
                "job_id" => $old_accrued['job_id'],
                "project_id" => $old_accrued['project_id'],
                "branch_id" => $old_accrued['branch_id']
            );
            JV::addTransaction($data_2, $is_actual);
            $data_3 = array(
                "JV_ID" => $jv,
                "Transaction_Reference" => $ref_id,
                "Transaction_Account" => $payment_code,
                "Transaction_Type" => "Credit",
                "Transaction_Amount" => $this->info['Real_Amount'],
                "Transaction_Date" => $this->info['End_Date'],
                "Currency_ID" => $this->info['Currency_ID'],
                "Logged_ID" => $this->info[0],
                "Transaction_Description" => $old_accrued['Description'],
                "Current_Ex_Rate" => "0",
                "main_id" => $this->info['ID'],
                "main_type" => 'aex',
                "job_id" => $old_accrued['job_id'],
                "project_id" => $old_accrued['project_id'],
                "branch_id" => $old_accrued['branch_id']
            );
            JV::addTransaction($data_3, $is_actual);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function viewAccruedExpense($accrued_id) {
        //to be updated later
        $query = $this->db->query('SELECT
                                        a.*,
                                        s.*,
                                        k.Account_Name AS Expense_Name,
                                        pa.Account_Name AS Accrued_Name,
                                        pa.Account_Code as Accrued_Code,
                                        k.Account_Code as Expense_Code,
                                        c.Currency_Symbol,b.branch_name,j.job_name,pr.project_name
                                    FROM
                                        tbl_accounting_accrued_expenses a
                                    LEFT JOIN
                                        tbl_chart_of_accounts k
                                    ON
                                        k.Account_ID = a.Account_ID
                                    LEFT JOIN
                                        tbl_chart_of_accounts pa
                                    ON
                                        pa.Account_ID = a.Accrued_Account_ID
                                    LEFT JOIN
                                        tbl_stock_suppliers s
                                    ON
                                        s.Supplier_ID = a.Supplier_ID
                                    LEFT JOIN
                                        tbl_common_currency c
                                    ON
                                        c.Currency_ID = a.Currency_ID
                                        left join tbl_organization_branches b on b.branch_id = a.branch_id
                                        left join tbl_accounting_jobs j on j.job_id = a.job_id
                                        left join tbl_projects pr on pr.project_id = a.project_id
                                    WHERE
                                        a.Accrued_Expense_ID = "' . $accrued_id . '"
                ');
        $row = $this->db->assoc($query);
        return $row;
    }

    //    public function viewAccountsByType($type) {
    //        $condition = '';
    //        $data = array();
    //        if ($type == "Income")
    //            $condition = 'and LEFT(Account_Code,1) = 5 OR LEFT(Account_Code,1) = 7';
    //        elseif ($type == "Expense")
    //            $condition = 'and LEFT(Account_Code,1) = 2 OR LEFT(Account_Code,1) = 6';
    //        elseif ($type == "PrepaidIncome")
    //            $condition = 'and LEFT(Account_Code,3) = 473';
    //        elseif ($type == "PrepaidExpense")
    //            $condition = 'and LEFT(Account_Code,3) = 471';
    //        elseif ($type == "AccruedExpense")
    //            $condition = 'and LEFT(Account_Code,3) = 472';
    //        elseif ($type == "Payment")
    //            $condition .= ''; //$condition = 'WHERE LEFT(Account_Code,2) = 53 OR LEFT(Account_Code,3) = 512 OR LEFT(Account_Code,3) = 101 OR LEFT(Account_Code,3) = 109';
    //        $query = $this->db->query('SELECT
    //                                        *
    //                                    FROM
    //                                        tbl_chart_of_accounts where Status_ID = 1
    //                ' . $condition);
    //        while ($row = $this->db->assoc($query))
    //            $data[] = $row;
    //        return $data;
    //    }
    // to be deleted later after finishing account code fix
    public function AccountsByTypeSearch($term = null, $page, $limit, $type) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' AND ( Account_Name LIKE ? OR Account_Code LIKE ? )';
            $data_filter[] = "%" . $term . "%";
            $data_filter[] = "%" . $term . "%";
        }
        if ($type == "Income")
            $cond .= 'AND (LEFT(Account_Code,1) = 5 OR LEFT(Account_Code,1) = 7)';
        elseif ($type == "Expense")
            $cond .= 'AND (LEFT(Account_Code,1) = 2 OR LEFT(Account_Code,1) = 6)';
        elseif ($type == "sales")
            $cond .= 'AND (LEFT(Account_Code,2) = 71 OR LEFT(Account_Code,2) = 70)';
        elseif ($type == "purchase")
            $cond .= 'AND LEFT(Account_Code,3) = 601';
        elseif ($type == "PrepaidIncome")
            $cond .= 'AND LEFT(Account_Code,3) = 473';
        elseif ($type == "PrepaidExpense")
            $cond .= 'AND LEFT(Account_Code,3) = 471';
        elseif ($type == "AccruedExpense")
            $cond .= 'AND LEFT(Account_Code,3) = 472';
        elseif ($type == "cash" || $type == "check" || $type == "credit_card") {
            $sub_type = ($type == "credit_card" ? "credit" : $type);
            $cond .= 'AND main_type="pay" and concat(",",sub_type,",") like "%,' . $sub_type . ',%" ';
        } elseif ($type == "contra")
            $cond .= 'AND (LEFT(Account_Code,3) = 512 OR    LEFT(Account_Code,2) = 53 )';
        elseif ($type == "Payment")
            $cond .= '';
        $query = $this->db->prepare_Stmt('SELECT Account_Code as id,
                                          concat(Account_Name," - ",Account_Code) as text
                                    FROM 
                                        tbl_chart_of_accounts
                                        WHERE Status_ID = 1
                                    ' . $cond . '
                                    limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);

        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function AccountIDByTypeSearch($term = null, $page, $limit, $type = '') {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' AND ( Account_Name LIKE ? OR Account_Code LIKE ? )';
            $data_filter[] = "%" . $term . "%";
            $data_filter[] = "%" . $term . "%";
        }
        if ($type == "Income")
            $cond .= 'AND (LEFT(Account_Code,1) = 5 OR LEFT(Account_Code,1) = 7)';
        elseif ($type == "Expense")
            $cond .= 'AND (LEFT(Account_Code,1) = 2 OR LEFT(Account_Code,1) = 6)';
        elseif ($type == "sales")
            $cond .= 'AND (LEFT(Account_Code,2) = 71 OR LEFT(Account_Code,2) = 70)';
        elseif ($type == "purchase")
            $cond .= 'AND ( LEFT(Account_Code,2) = 60 or LEFT(Account_Code,2) = 61)';
        elseif ($type == "PrepaidIncome")
            $cond .= 'AND LEFT(Account_Code,3) = 473';
        elseif ($type == "PrepaidExpense")
            $cond .= 'AND LEFT(Account_Code,3) = 471';
        elseif ($type == "AccruedExpense")
            $cond .= 'AND LEFT(Account_Code,3) = 472';
        elseif ($type == "supp") {
            $cond .= 'AND main_type="supp" and sub_type = "main" ';
        } elseif ($type == "cust") {
            $cond .= 'AND main_type="cust" and sub_type = "main" ';
        } elseif ($type == "pay") {
            $cond .= 'AND main_type="pay" ';
        } elseif ($type == "cash" || $type == "check" || $type == "credit_card") {
            $sub_type = ($type == "credit_card" ? "credit" : $type);
            $cond .= 'AND main_type="pay" and concat(",",sub_type,",") like "%,' . $sub_type . ',%" ';
        } elseif ($type == "contra")
            $cond .= 'AND (LEFT(Account_Code,3) = 512 OR    LEFT(Account_Code,2) = 53 )';

        $restriction_trans_code_length = smc_session()['default_setting']['restrict_trans_code_length'];
        if ($restriction_trans_code_length == 0) {
            $cond .= ' and Account_ID not in (select distinct parent_id from tbl_chart_of_accounts )';
        } else {
            $account_code_length = smc_session()['account_setting']['account_code_length'];
            $cond .= ' AND length(Account_Code) = "' . $account_code_length . '"';
        }
        if (!empty($this->info['Status_ID'])) {
            $cond .= ' AND  Status_ID = "1" ';
        }
        if (!empty($this->info['is_edited'])) {
            $cond .= ' AND  (Status_ID = "1"  or (Status_ID = 0 and Account_ID= ?))';
            $data_filter[] = $this->info['is_edited'];
        }
        if (!empty(smc_session()['default_setting']['hide_account_types'])) {
            $opts = str_replace(',', '","', smc_session()['default_setting']['hide_account_types']);
            $cond.= ' and concat(main_type,"#",sub_type) not in ("' . $opts . '") ';
        }

        $query = $this->db->prepare_Stmt('SELECT Account_ID as id,
                                          concat(Account_Code," - ",Account_Name) as text,
                                          main_type,sub_type,main_id,
                                          Account_Code,
                                          job_required
                                    FROM 
                                        tbl_chart_of_accounts
                                        WHERE Status_ID = 1
                                    ' . $cond . '
                                    limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);

        while ($row = $this->db->assoc($query)) {
            $desc = CommonBusiness::get_account_desc($row['main_type'], $row['sub_type']);
            $row['text'] = $row['text'] . (!empty($desc) ? ' - ' . $desc : '');
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function viewPaymentTypes() {
        $data = array();
        $query = $this->db->query('select Account_ID, Account_Code, Account_Name
                                    from tbl_chart_of_accounts where main_type = "pay" and Status_ID = 1');
        while ($row = $this->db->assoc($query))
            $data[] = $row;
        return $data;
    }

    public function addDepositWithDrawal($is_pos = 0, $other = false) {
        $error = token_unique::is_valid_token();
        if (!empty($error))
            return $error;
//        if (!empty($this->info['unique_id'])) {
//            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'=>'deposit','start_date'=>$this->info['Deposit_WithDrawal_Date'],'is_actual'=>$flag]);

//        $check = JV::checkCurrentYear($this->info['Deposit_WithDrawal_Date'], $this->info['Deposit_WithDrawal_Date'], $flag);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        if (!$other)
            $this->db->query("START TRANSACTION");
        $rate_from = !empty($this->info['rate_from']) ? $this->info['rate_from'] : CurrencyFactory::getLastExRate($this->info['Currency_From']);

        $second_rate_from = !empty($this->info['sec_rate_from']) ? $this->info['sec_rate_from'] : CurrencyFactory::getSecondCurrencyLastExRate($this->info['Currency_From']);
        $second_rate_to = !empty($this->info['sec_rate_to']) ? $this->info['sec_rate_to'] : CurrencyFactory::getSecondCurrencyLastExRate($this->info['Currency_To']);
        //        echo $rate_from.'==';
        //        die();
        $rate_to = !empty($this->info['rate_to']) ? $this->info['rate_to'] : CurrencyFactory::getLastExRate($this->info['Currency_To']);
        $year_id = $flag ? $this->year_id_actual : $this->year_id;
        $this->info['branch_id'] = !empty($this->info['branch_id']) ? $this->info['branch_id'] : ( empty(smc_session()['user_accounts']['branch_id']) ? smc_session()['default_setting']['default_branch_id'] : smc_session()['user_accounts']['branch_id']);
        if (isset($this->info['dw_prefix'])) {
            $dw_prefix = $this->info['dw_prefix'];
        } else {
            $query_branch = $this->db->query('select branch_prefix from tbl_organization_branches where branch_id = "' . $this->info['branch_id'] . '"');
            $branch_prefix = $this->db->assoc($query_branch)['branch_prefix'];
            $dw_prefix = smc_session()['user_accounts']['serial_prefix'] ? smc_session()['user_accounts']['serial_prefix'] : $branch_prefix;
        }
        $dw_nb = isset($this->info['dw_number']) ? $this->info['dw_number'] : $this->getmaxDWID($flag, $dw_prefix);

        $app_user_id = isset($this->info['app_user_id']) ? $this->info['app_user_id'] : 0;
        $app_dep_id = isset($this->info['app_dep_id']) ? $this->info['app_dep_id'] : 0;
        $app_branch_id = isset($this->info['app_branch_id']) ? $this->info['app_branch_id'] : 0;

        $mydate = date("Y-m-d H:i:s");
        $logged_id = isset($this->info['Logged_ID']) ? $this->info['Logged_ID'] : $this->info[0];
        $created_logged_id = isset($this->info['created_logged_id']) ? $this->info['created_logged_id'] : $this->info[0];
        $date_time = isset($this->info['date_time']) ? $this->info['date_time'] : $mydate;
        $date_created = isset($this->info['date_created']) ? $this->info['date_created'] : $mydate;
        $this->info['due_date'] = !empty($this->info['due_date']) ? $this->info['due_date'] : $this->info['Deposit_WithDrawal_Date'];

        $data = [
            &$dw_nb,
            &$dw_prefix,
            &$date_time,
            &$date_created,
            &$this->info['To_Account'],
            &$this->info['From_Account'],
            &$this->info['Deposit_WithDrawal_Amount'],
            &$this->info['Deposit_WithDrawal_Amount_To'],
            &$this->info['Currency_From'],
            &$this->info['Currency_To'],
            &$rate_from,
            &$rate_to,
            &$second_rate_from, &$second_rate_to,
            &$this->info['branch_id'],
            &$this->info['Deposit_WithDrawal_Date'],
            &$this->info['due_date'],
            &$this->info['Deposit_WithDrawal_Description'],
            &$logged_id,
            &$created_logged_id,
            &$flag,
            &$year_id,
            &$app_user_id,
            &$app_dep_id, &$app_branch_id, &$this->info['job_id'], &$this->info['project_id']
        ];
        //        echo"<pre>";
        //        die(print_r($data));
        $this->db->prepare_Stmt("INSERT INTO
                                        tbl_accounting_bank_deposit_withdrawal (
                                        " . (!empty($this->info['fill_auto_id']) ? 'Deposit_WithDrawal_ID,' : '') . "
                                        dw_number,
                                        dw_prefix,
                                        date_time,date_created,
                                            To_Account_ID,
                                            From_Account_ID,
                                            Deposit_WithDrawal_Amount,
                                            Deposit_WithDrawal_Amount_To,
                                            Currency_From,
                                            Currency_To,
                                            current_rate_from,
                                            current_rate_to,
                                            second_currency_rate_from,second_currency_rate_to,
                                            branch_id,
                                            Deposit_WithDrawal_Date,due_date,
                                            Deposit_WithDrawal_Description,
                                            Logged_ID,created_logged_id,
                                            Is_Hidden,
                                            current_year_id,app_user_id,app_dep_id,app_branch_id,job_id,project_id
                                        )
                                        VALUES (
                                            " . (!empty($this->info['fill_auto_id']) ? intval($this->info['fill_auto_id']) . ',' : '') . "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?
                                        )
                ", $data);
        //        $error = $this->db->error();die(print_r($error));
        $ref_id = $this->db->id();
        $jv = JV::returnJV($flag);
        $data_1 = array(
            "JV_ID" => $jv,
            "Transaction_Reference" => $dw_nb,
            "Transaction_Account" => $this->info['From_Account'],
            "Transaction_Type" => "Credit",
            "Transaction_Amount" => $this->info['Deposit_WithDrawal_Amount'],
            "Transaction_Date" => $this->info['Deposit_WithDrawal_Date'],
            "Currency_ID" => $this->info['Currency_From'],
            "Logged_ID" => $logged_id,
            "created_logged_id" => $created_logged_id,
            "date_time" => $date_time,
            "date_created" => $date_created,
            "Transaction_Description" => $this->info['Deposit_WithDrawal_Description'],
            "Current_Ex_Rate" => $rate_from,
            "second_currency_rate" => $second_rate_from,
            "branch_id" => $this->info['branch_id'],
            "main_id" => $ref_id,
            "prefix" => $dw_prefix,
            "main_nb" => $dw_nb,
            "main_type" => 'dwt',
            "job_id" => $this->info['job_id'],
            "project_id" => $this->info['project_id'],
            "due_date" => $this->info['due_date']
        );
        JV::addTransaction($data_1, $flag);
        $data_2 = array(
            "JV_ID" => $jv,
            "Transaction_Reference" => $dw_nb,
            "Transaction_Account" => $this->info['To_Account'],
            "Transaction_Type" => "Debit",
            "Transaction_Amount" => $this->info['Deposit_WithDrawal_Amount_To'],
            "Transaction_Date" => $this->info['Deposit_WithDrawal_Date'],
            "Currency_ID" => $this->info['Currency_To'],
            "Logged_ID" => $logged_id,
            "created_logged_id" => $created_logged_id,
            "date_time" => $date_time,
            "date_created" => $date_created,
            "Transaction_Description" => $this->info['Deposit_WithDrawal_Description'],
            "Current_Ex_Rate" => $rate_to,
            "second_currency_rate" => $second_rate_to,
            "branch_id" => $this->info['branch_id'],
            "main_id" => $ref_id,
            "prefix" => $dw_prefix,
            "main_nb" => $dw_nb,
            "main_type" => 'dwt',
            "job_id" => $this->info['job_id'],
            "project_id" => $this->info['project_id'],
            "due_date" => $this->info['due_date']
        );
        JV::addTransaction($data_2, $flag);
        $arr_log = ['type' => 'dwt', 'main_id' => $ref_id,
            'action' => 0, 'date_time' => $mydate,
            'user_id' => smc_session()['user_logged_id']];
        CommonFunctions::add_user_log($arr_log);
        if (!$other) {
            $error = $this->db->error();
            if (empty($error)) {
                $this->db->query('COMMIT');
                token_unique::remove_token();
                return $is_pos ? $ref_id : [$ref_id];
            } else {
                $this->db->query('ROLLBACK');
                return array_unique($error);
            }
        }
        //        return array_unique($error);
    }

    public function getmaxDWID($actual, $prefix = '') {
        $year = $actual == 1 ? $this->year_id_actual : $this->year_id;
        $cond = '';
        if (!empty($prefix)) {
            $cond.=' and dw_prefix = "' . $prefix . '" ';
        }
        $query = $this->db->query('SELECT MAX(dw_number) AS dw_number FROM tbl_accounting_bank_deposit_withdrawal 
                where current_year_id = "' . $year . '" 
                and Is_Hidden = "' . $actual . '" ' . $cond);
        $row = $this->db->assoc($query);
        return $row['dw_number'] + 1;
    }

    public function returnMaxDepositWithDrawalID() {
        $max = $this->db->query('SELECT MAX(Deposit_WithDrawal_ID) AS max_id FROM tbl_accounting_bank_deposit_withdrawal');
        $row = $this->db->assoc($max);
        $id = $row['max_id'];
        return $id;
    }

    public function viewPrepaidByType($type, $id = null, $date = null, $month = null, $year = null, $from = null, $to = null) {
        $data = array();
        $condition = '';
        if ($type == 1)
            $condition .= ' AND p.is_expense = 1';
        elseif ($type ==0)
            $condition .= ' AND p.is_expense = 0';
        if (!is_null($id))
            $condition .= ' AND p.Prepaid_ID = ' . $id . '';
        if (!is_null($date))
            $condition .= ' AND p.Start_Date = "' . $date . '"';
        if (!is_null($month))
            $condition .= ' AND MONTH(p.Start_Date) = "' . $month . '"';
        if (!is_null($year))
            $condition .= ' AND YEAR(p.Start_Date) = "' . $year . '"';
        if (!is_null($from) and ! is_null($to))
            $condition .= ' AND p.Start_Date >= "' . $from . '" AND p.Start_Date <= "' . $to . '"';
        $query = $this->db->query('SELECT
                                        p.*,
                                        p.Prepaid_Account AS Prepaid_Code,
                                        concat(a.Account_Code," - ",a.Account_Name) As Main_Account,
                                        concat(pa.Account_Code," - ",pa.Account_Name) As Prepaid_Account_Name,
                                        s.*,b.branch_name,
                                        c.Currency_Symbol,j.job_name,pr.project_name,c.Currency_Name
                                    FROM
                                        tbl_accounting_prepaid_expense_income p
                                    LEFT JOIN
                                        tbl_chart_of_accounts a
                                    ON
                                        a.Account_ID = p.Account_ID
                                    LEFT JOIN
                                        tbl_chart_of_accounts pa
                                    ON
                                        pa.Account_ID = p.Prepaid_Account_ID
                                    LEFT JOIN
                                        tbl_stock_suppliers s
                                    ON
                                        s.Supplier_ID = p.Supplier_ID
                                    LEFT JOIN
                                        tbl_common_currency c
                                    ON
                                        c.Currency_ID = p.Currency_ID
                                        left join tbl_organization_branches b on b.branch_id = p.branch_id
                                        left join tbl_accounting_jobs j on j.job_id = p.job_id
                                        left join tbl_projects pr on pr.project_id = p.project_id
                                    WHERE
                                        p.Status_ID = 1 
                ' . $condition . ' GROUP BY p.Prepaid_ID');
        while ($row = $this->db->assoc($query))
            $data[] = $row;
        return $data;
    }

    //    public function returnAccruedExpense($id = null, $date = null, $month = null, $year = null, $from = null, $to = null) {
    //        $data = array();
    //        $condition = '';
    //        if (!is_null($id))
    //            $condition .= ' AND Accrued_Expense_ID = "' . $id . '"';
    //        if (!is_null($date))
    //            $condition .= ' AND Start_Date = "' . $date . '"';
    //        if (!is_null($month))
    //            $condition .= ' AND MONTH(Start_Date) = "' . $month . '"';
    //        if (!is_null($year))
    //            $condition .= ' AND YEAR(Start_Date) = "' . $year . '"';
    //        if (!is_null($from) and ! is_null($to))
    //            $condition .= ' AND Start_Date >= "' . $from . '" AND Start_Date <= "' . $to . '"';
    //        $query = $this->db->query(' SELECT * FROM accrued_list WHERE 1=1
    //                ' . $condition);
    //        while ($row = $this->db->assoc($query))
    //            $data[] = $row;
    //        return $data;
    //    }
    //    public function viewDepositWithDrawal($date = null, $month = null, $year = null, $from = null, $to = null, $is_hidden = false) {
    //        $data = array();
    //        $condition = '';
    //        if (!$is_hidden)
    //            $condition .= ' AND dw.Is_Hidden = 0';
    //        if (!is_null($date))
    //            $condition .= ' AND dw.Deposit_WithDrawal_Date = "' . $date . '"';
    //        if (!is_null($month))
    //            $condition .= ' AND MONTH(dw.Deposit_WithDrawal_Date) = "' . $month . '"';
    //        if (!is_null($year))
    //            $condition .= ' AND YEAR(dw.Deposit_WithDrawal_Date) = "' . $year . '"';
    //        if (!is_null($from) and ! is_null($to))
    //            $condition .= ' AND dw.Deposit_WithDrawal_Date >= "' . $from . '" AND dw.Deposit_WithDrawal_Date <= "' . $to . '"';
    //        $query = $this->db->query('SELECT
    //                                        dw.*,
    //                                        f.Account_Name AS T_FROM,
    //                                        t.Account_Name AS T_TO,
    //                                        c.Currency_Symbol AS Symbol_From,
    //                                        ct.Currency_Symbol AS Symbol_To
    //                                    FROM
    //                                        tbl_accounting_bank_deposit_withdrawal dw
    //                                    LEFT JOIN
    //                                        tbl_chart_of_accounts f
    //                                    ON
    //                                        dw.From_Account = f.Account_Code
    //                                    LEFT JOIN
    //                                        tbl_chart_of_accounts t
    //                                    ON
    //                                        dw.To_Account = t.Account_Code
    //                                    LEFT JOIN
    //                                        tbl_common_currency c
    //                                    ON
    //                                        dw.Currency_From = c.Currency_ID
    //                                    LEFT JOIN
    //                                        tbl_common_currency ct
    //                                    ON
    //                                        dw.Currency_To = ct.Currency_ID
    //                                    WHERE
    //                                        1=1
    //                ' . $condition);
    //        while ($row = $this->db->assoc($query)) {
    //            $data[] = $row;
    //        }
    //        return $data;
    //    }

    public function viewAccountByCodeOrName($cmd = null, $term = null) {
        $condition = '';
        $data = array();
        $data_filter = array();
        if (!is_null($term) && $cmd == 1) {
            $condition .= ' AND a.Account_Code like ? ';
            $data_filter[] = "%" . $term . "%";
        }
        if (!is_null($term) && $cmd == 2) {
            $condition .= ' AND a.Account_Name like ?';
            $data_filter[] = "%" . $term . "%";
        }
        $restriction_trans_code_length = smc_session()['default_setting']['restrict_trans_code_length'];
        if ($restriction_trans_code_length == 0) {
            $condition .= ' and a.Account_ID not in (select distinct parent_id from tbl_chart_of_accounts )';
        } else {
            $account_code_length = smc_session()['account_setting']['account_code_length'];
            $condition .= ' AND length(a.Account_Code) = "' . $account_code_length . '"';
        }
        if (!empty($this->info['Status_ID'])) {
            $condition .= ' AND  a.Status_ID = "1" ';
        }
        if (!empty($this->info['is_edited'])) {
            $condition .= ' AND  (a.Status_ID = "1"  or (a.Status_ID = 0 and a.Account_ID= "' . $this->info['is_edited'] . '"))';
        }
        $query = $this->db->prepare_Stmt('SELECT
                                        a.Account_ID,
                                        a.Account_Name,
                                        a.Account_Code,
                                        c.Currency_Symbol,
                                        a.job_ids,
                                        a.job_required,
                                        a.Currency_ID,a.main_type,a.sub_type
                                    FROM
                                        tbl_chart_of_accounts a
                                    LEFT JOIN
                                        tbl_common_currency c
                                    ON
                                        a.Currency_ID = c.Currency_ID
                                    WHERE
                                        1=1
                ' . $condition . ' limit 60', $data_filter);
        while ($row = $this->db->assoc($query))
            $data[] = $row;
        return $data;
    }

    private function returnNeededTransactions($id, $desc) {
        $transaction = $this->db->query(' SELECT
                                            DISTINCT JV_ID
                                        FROM
                                            tbl_accounting_transactions
                                        WHERE
                                            Transaction_Reference = "' . $id . '"
                                        AND
                                            Transaction_Description = "' . $desc . '"
                    ');
        $row = $this->db->assoc($transaction);
        return $row['JV_ID'];
    }

    public function returnTransactionsByJV($jv_id, $is_actual = 0) {
        $data = array();
        $total_debits = 0.0;
        $total_credits = 0.0;
        $balance = 0.0;
        $transaction = $this->db->query(' SELECT
                                            *
                                            FROM 
                                                transactions_info
                                        WHERE
                                            JV_ID = "' . $jv_id . '"
                                                and current_year_id = "' . $this->year_id . '" and is_actual = "' . $is_actual . '"
                    ');
        while ($row = $this->db->assoc($transaction)) {
            if ($row['Transaction_Type'] == "Debit") {
                $total_debits += $row['Transaction_Amount'];
                $balance += $row['Transaction_Amount'];
            } else {
                $total_credits += $row['Transaction_Amount'];
                $balance -= $row['Transaction_Amount'];
            }
            $data[] = $row;
        }
        $data['Total_Debits'] = $total_debits;
        $data['Total_Credits'] = $total_credits;
        $data['Balance'] = $balance;
        return $data;
    }

    public function transferJVToActual() {
        $is_actual = 1;
        $old_jv = $this->info['JVID'];
        $old_is_act = 0;
        $transfer_data = self::returnTransactionsByJV($old_jv, $old_is_act);
//        $check = JV::checkCurrentYear($transfer_data[0]['Transaction_Date'], $transfer_data[0]['Transaction_Date'], $is_actual);
//        if ($check !== '') {
//            $error = array();
//            $error[0] = $check;
//            return $error;
//        }

        $this->db->query('START TRANSACTION');
        $j = 0;
        $new_jv = JV::returnJV($is_actual);
        $transfer_item = [&$old_jv,
            &$new_jv,
            &$this->info['TransferDate'],
            &$this->info['DescTransfer'],
            &$this->info[0],
            &$this->year_id_actual];
        $this->db->prepare_Stmt('INSERT INTO
                                tbl_accounting_transfer_jv (
                                    Old_JV_ID,
                                    New_JV_ID,
                                    Transfer_Date,
                                    Transfer_Description,
                                    Logged_ID,
                                    current_year_id
                                )
                                VALUES (?,?,?,?,?,?)', $transfer_item);
        $tr_id = $this->db->id();

        for ($i = 0; $i < count($transfer_data) - 3; $i++) {
            $data = array(
                "JV_ID" => $new_jv,
                "Transaction_Reference" => $transfer_data[$i]['Transaction_Reference'],
                "Transaction_Account" => $transfer_data[$i]['Account_ID'],
                "Transaction_Type" => $transfer_data[$i]['Transaction_Type'],
                "Transaction_Amount" => $transfer_data[$i]['Transaction_Amount'],
                "Transaction_Date" => $transfer_data[$i]['Transaction_Date'],
                "Currency_ID" => $transfer_data[$i]['Currency_ID'],
                "Logged_ID" => $transfer_data[$i]['Logged_ID'],
                "Transaction_Description" => $transfer_data[$i]['Transaction_Description'],
                "Current_Ex_Rate" => "0",
                "main_id" => $tr_id,
                "main_type" => 'tjv'
            );
            JV::addTransaction($data, $is_actual);
        }

        $this->db->prepare_Stmt('DELETE FROM tbl_accounting_transactions WHERE JV_ID = ? and is_actual = ?', [&$old_jv, &$old_is_act]);

        $this->db->prepare_Stmt('UPDATE 
                                    tbl_accounting_transactions 
                                SET 
                                    JV_ID = JV_ID - 1
                                WHERE 
                                    JV_ID > ? and is_actual = ? ', [&$old_jv, &$old_is_act]);


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

    public function mergeAccounts() {
        $this->db->query("START TRANSACTION");
        $accounts = implode(",", $this->info['Merge_Account_Array']);
        $up = [&$this->info['Merged_On'], &$this->info['is_actual']];
        $this->db->prepare_Stmt("UPDATE 
                                            tbl_accounting_transactions
                                        SET 
                                            Account_ID = ?
                                        WHERE 
                                            Account_ID IN (" . $accounts . ") 
                                                and is_actual = ?
                    ", $up);

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

    public function viewAllAccounts() {
        $data = array();
        $query = $this->db->query('SELECT * FROM tbl_chart_of_accounts where Status_ID = 1');
        while ($row = $this->db->assoc($query))
            $data[] = $row;
        return $data;
    }

    public static function returnJV($flag = 0, $year = null) {
        $year_id = is_null($year) ? ($flag ? Session::getCurrentYearIDActual() : Session::getCurrentYearID()) : $year;
        $db = DBConnectorFactory::loadDriver();
//        $start = microtime(true);
        $query = $db->query('SELECT 
                                                JV_ID AS MAX_JV
                                        FROM
                                                tbl_accounting_transactions
                                        WHERE
                                            current_year_id = "' . $year_id . '" and is_actual = "' . $flag . '"
                                                order by JV_ID desc limit 1

                 ');
//        echo (microtime(true)-$start).'end jv===';
        $row = $db->assoc($query);
        $jv = $row['MAX_JV'];
        if (is_null($jv))
            $jv = 1;
        else
            $jv += 1;
        return $jv;
    }

    public static function return_max_jv_nb($flag = 0, $prefix = '') {
        $year_id = $flag ? Session::getCurrentYearIDActual() : Session::getCurrentYearID();
        $db = DBConnectorFactory::loadDriver();
        $query = $db->query('SELECT 
                                                MAX(main_nb) AS main_nb
                                        FROM
                                                tbl_accounting_transactions
                                        WHERE
                                            current_year_id = "' . $year_id . '" 
                                                and is_actual = "' . $flag . '" and main_type="mjv" and prefix="' . $prefix . '"

                 ');
        $row = $db->assoc($query);
        $jv = $row['main_nb'];
        if (is_null($jv))
            $jv = 1;
        else
            $jv += 1;
        return $jv;
    }

    public static function addTransaction(Array $data, $flag = 0) {
        $db = DBConnectorFactory::loadDriver();
        if (array_key_exists("current_year_id", $data)) {
            $year_id = $data['current_year_id'];
        } else {
            $year_id = ($flag == 1 ? Session::getCurrentYearIDActual() : Session::getCurrentYearID());
        }
        $mydate = date("Y-m-d H:i:s");
        if ((!isset($data['Current_Ex_Rate']) || !is_numeric($data['Current_Ex_Rate'])) || ($data['Current_Ex_Rate'] == 0 && empty($data['keep_rate']))) {
            $data['Current_Ex_Rate'] = CurrencyFactory::getLastExRate($data['Currency_ID']);
        }
        //we put it isset not empty since we need to send it 0 from close year (case if debit amount and credit loca amount or vise versa)
        if ((!isset($data['second_currency_rate']) || !is_numeric($data['second_currency_rate'])) || ($data['second_currency_rate'] == 0 && empty($data['keep_rate']))) {
            $data['second_currency_rate'] = CurrencyFactory::getSecondCurrencyLastExRate($data['Currency_ID']);
        }
//          echo'heree';
        if (empty($data['branch_id']))
            $data['branch_id'] = empty(smc_session()['user_accounts']['branch_id']) ? smc_session()['default_branch_id'] : smc_session()['user_accounts']['branch_id'];

        if (empty($data['job_id']))
            $data['job_id'] = null;
        if (!isset($data['due_date']))
            $data['due_date'] = $data['Transaction_Date'];
        if (!isset($data['value_date']))
            $data['value_date'] = $data['Transaction_Date'];
        if (!isset($data['main_nb']))
            $data['main_nb'] = $data['main_id'];
        if (!isset($data['subtype']))
            $data['subtype'] = '';
        if (!isset($data['prefix']))
            $data['prefix'] = '';
        if (!isset($data['user_ref']))
            $data['user_ref'] = '';
        if (!isset($data['created_logged_id']))
            $data['created_logged_id'] = smc_session()['user_logged_id'];
        if (!isset($data['date_created']))
            $data['date_created'] = $mydate;
        if (!isset($data['date_time']))
            $data['date_time'] = $mydate;
        if (empty($data['check_nb']))
            $data['check_nb'] = '';
        if (empty($data['project_id']))
            $data['project_id'] = null;
        if (empty($data['sub_id']))
            $data['sub_id'] = null;
        if (empty($data['pos_customer_id']))
            $data['pos_customer_id'] = 0;
        if (empty($data['salesman_id']))
            $data['salesman_id'] = 0;
        if (empty($data['activity_id']))
            $data['activity_id'] = null;
        //        echo"<pre>";die(print_r($data));
        if (!isset($data['local_amount']) || !is_numeric($data['local_amount']))
            $data['local_amount'] = $data['Transaction_Amount'] * $data['Current_Ex_Rate'];
        //here some case maybe transaction amount zero and local not zero like in exchange, open year..
        if ($data['Transaction_Amount'] > 0 || $data['local_amount'] > 0) {
            if (empty($data['Transaction_Account'])) {
                $db->set_error(CommonLanguage::getMessage('account_empty_error') . ' ' . $data['Transaction_Amount'] . (empty($data['Transaction_Reference']) ? '' : ' ( ' . $data['Transaction_Reference'] . ' )'));
            }
            $data_arr = [
                &$data['JV_ID'], &$data['Transaction_Reference'], &$data['user_ref'], &$data['Transaction_Account'], &$data['Transaction_Type'],
                &$data['Transaction_Amount'], &$data['Transaction_Date'], &$data['Currency_ID'], &$data['Logged_ID'], &$data['created_logged_id'],
                &$year_id, &$data['Current_Ex_Rate'], &$data['second_currency_rate'], &$data['Transaction_Description'], &$data['date_time'], &$data['date_created'], &$data['branch_id'],
                &$data['main_id'], &$data['main_type'], &$data['job_id'], &$data['main_nb'], &$data['due_date'],
                &$data['subtype'], &$data['prefix'], &$flag, &$data['check_nb'], &$data['value_date'],
                &$data['local_amount'], &$data['project_id'], &$data['sub_id'], &$data['pos_customer_id'], &$data['salesman_id'], &$data['activity_id']];
//            if ($data['Transaction_Account'] == 3888)
//                print_r($data_arr);
            $db->prepare_Stmt('INSERT INTO tbl_accounting_transactions(
                ' . (!empty($data['Transaction_ID']) ? 'Transaction_ID,' : '') . '
                JV_ID,Transaction_Reference,user_ref,Account_ID,Transaction_Type,
                Transaction_Amount,Transaction_Date,Currency_ID,Logged_ID,created_logged_id,
                current_year_id,Current_Ex_Rate,second_currency_rate,Transaction_Description,date_time,date_created,branch_id,
                main_id,main_type,job_id,main_nb,due_date,subtype,prefix,is_actual,check_nb,value_date,local_amount,
                project_id,sub_id,pos_customer_id,salesman_id,activity_id) 
                VALUES(' . (!empty($data['Transaction_ID']) ? $data['Transaction_ID'] . ',' : '') . '?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', $data_arr);
        }
    }

    public function getAccountName($term, $only_cust = 0) {
        $return_arr = array();
        $condition = (!empty($this->info['is_customer_supplier']) && $this->info['is_customer_supplier'] == 1) ? ' and (a.main_type IN ("supp","cust"))  and a.sub_type = "main" ' : '';
        $term_new = preg_replace('/\s+/', '%', $term);

        if (!empty($only_cust)) {
            $condition .= ' and a.main_type="cust" and a.sub_type="main" ';
        }
        if (!empty(smc_session()['default_setting']['hide_account_types'])) {
            $opts = str_replace(',', '","', smc_session()['default_setting']['hide_account_types']);
            $condition.= ' and concat(main_type,"#",sub_type) not in ("' . $opts . '") ';
        }
        $query = $this->db->query('SELECT a.*,cu.Currency_Code,cu.Currency_Symbol FROM tbl_chart_of_accounts a
            left join tbl_common_currency cu on cu.Currency_ID = a.Currency_ID
            WHERE (concat(a.Account_Name," - ",a.Account_Code) LIKE "%' . $term_new . '%" or a.Account_Name LIKE "%' . $term . '%" OR a.Account_Code LIKE "%' . $term . '%" )' . $condition);
        while ($row = $this->db->fetch($query)) {
            $row_array['id'] = $row['Account_ID'];
//            if (substr($row['Account_Code'], 0, 5) == 44210 or substr($row['Account_Code'], 0, 5) == 44211)
//                $row_array['value'] = ucwords($row['Account_Name']) . " - " . $row['Account_Code'] . " - Purchase VAT Account";
//            elseif (substr($row['Account_Code'], 0, 4) == 4011)
//                $row_array['value'] = ucwords($row['Account_Name']) . " - " . $row['Account_Code'] . " - Purchase Account";
//            elseif (substr($row['Account_Code'], 0, 5) == 44213 or substr($row['Account_Code'], 0, 5) == 44214)
//                $row_array['value'] = ucwords($row['Account_Name']) . " - " . $row['Account_Code'] . " - Fixed Asset VAT Account";
//            elseif (substr($row['Account_Code'], 0, 4) == 4031)
//                $row_array['value'] = ucwords($row['Account_Name']) . " - " . $row['Account_Code'] . " - Fixed Asset Account";
//            elseif (substr($row['Account_Code'], 0, 5) == 44216 or substr($row['Account_Code'], 0, 5) == 44217)
//                $row_array['value'] = ucwords($row['Account_Name']) . " - " . $row['Account_Code'] . " - Expense VAT Account";
//            elseif (substr($row['Account_Code'], 0, 5) == 46190)
//                $row_array['value'] = ucwords($row['Account_Name']) . " - " . $row['Account_Code'] . " - Expense Account";
//            else
//                $row_array['value'] = ucwords($row['Account_Name']) . " - " . $row['Account_Code'] . " - " . $row['Currency_Code'];
            $desc = CommonBusiness::get_account_desc($row['main_type'], $row['sub_type']);
            $row_array['value'] = $row['Account_Code'] . " - " . ucwords($row['Account_Name']) . " - " . $row['Currency_Code'] . (!empty($desc) ? ' - ' . $desc : '');
            $row_array['parent'] = $row['Parent_ID'];
            $row_array['code'] = $row['Account_Code'];
            $row_array['account_name'] = $row['Account_Name'];
            array_push($return_arr, $row_array);
        }
        return $return_arr;
    }

    public function reorderOffJVNum($from_date, $is_actual) {
        $year_id = $is_actual ? $this->year_id_actual : $this->year_id;
        $this->db->query("START TRANSACTION");
        $jv_cond = " and Transaction_Date >='" . $from_date . "' and JV_ID > 1
                and current_year_id = '" . $year_id . "' and is_actual = '" . $is_actual . "' ";
        $old = 0;
        $query = $this->db->query("SELECT JV_ID 
            FROM tbl_accounting_transactions WHERE 1=1 {$jv_cond}
            ORDER BY JV_ID asc limit 1");
        $row = $this->db->assoc($query);
        $new_jv = empty($row['JV_ID']) ? 1 : ($row['JV_ID'] - 1);
        //
        $query = $this->db->query("SELECT Transaction_ID,JV_ID 
            FROM tbl_accounting_transactions WHERE 1=1 {$jv_cond}
            ORDER BY Transaction_Date, JV_ID ");
        while ($row = $this->db->assoc($query)) {
            if ($row['JV_ID'] != $old)
                $new_jv++;
            $up = [&$new_jv, &$row['Transaction_ID']];
            $this->db->prepare_Stmt("UPDATE tbl_accounting_transactions 
                SET JV_ID = ? WHERE Transaction_ID = ? ", $up);
            $old = $row['JV_ID'];
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function editJV() {
        $this->db->query("START TRANSACTION");
        $mydate = date("Y-m-d H:i:s");
        $flag = $this->info['is_actual'];
        $check = CommonFunctions::check_lock_data(['type'=>'mjv','start_date'=>$this->info['Transaction_Date'],'is_actual'=>$flag]);

//        $check = JV::checkCurrentYear($this->info['Transaction_Date'], $this->info['Transaction_Date'], $flag);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $year_id = $flag == 1 ? SESSION::getCurrentYearIDActual() : SESSION::getCurrentYearID();
        $old_jv_query = $this->db->query('select main_nb, date_created from tbl_accounting_transactions WHERE JV_ID = "' . $this->info['JV_ID'] . '" AND current_year_id = "' . $year_id . '" and is_actual = "' . $flag . '" limit 1');
        $old_jv_row = $this->db->assoc($old_jv_query);
        $jv_nb = $old_jv_row['main_nb'];
        $date_created = $old_jv_row['date_created'];
        //return close invoice amounts to invoices and purchases payment_paid_amount
        $query_old = $this->db->query('select p.type,t.Currency_ID,t.Current_Ex_Rate,p.record_id
            from tbl_accounting_transactions t
            join tbl_invoice_payments p on p.record_id = t.Transaction_ID and p.type in ("cmjv","smjv")
            where t.JV_ID = "' . $this->info['JV_ID'] . '" and t.current_year_id = "' . $year_id . '" 
                and t.is_actual="' . $flag . '" ');
        while ($row = $this->db->assoc($query_old)) {
            CommonBusiness::return_inv_pur_close_amount($row['type'], $row['Currency_ID'], $row['Current_Ex_Rate'], $row['record_id']);
        }
        //
//        echo $jv_nb.'==';
        $this->db->prepare_Stmt("DELETE FROM tbl_accounting_transactions  WHERE JV_ID = ? AND current_year_id = ? and is_actual = ?", [&$this->info['JV_ID'], &$year_id, &$flag]);
        for ($i = 0; $i < count($this->info['Accounts']); $i++) {
            $this->info['Accounts'][$i]['job_id'] = empty($this->info['Accounts'][$i]['job_id']) ? null : $this->info['Accounts'][$i]['job_id'];
            $this->info['Accounts'][$i]['transaction_id'] = !empty($this->info['Accounts'][$i]['transaction_id']) ? $this->info['Accounts'][$i]['transaction_id'] : 0;
            $user_ref = !empty($this->info['Accounts'][$i]['user_ref']) ? $this->info['Accounts'][$i]['user_ref'] : $this->info['jv_note'];

            $data = array(
                "JV_ID" => $this->info['JV_ID'],
                "Transaction_Reference" => $this->info['Accounts'][$i]['Transaction_Reference'],
                "user_ref" => $user_ref,
                "Transaction_Account" => $this->info['Accounts'][$i]['Account_ID'],
                "Transaction_Type" => $this->info['Accounts'][$i]['Transcation_Type'],
                "Transaction_Amount" => $this->info['Accounts'][$i]['Transcation_Amount'],
                "Transaction_Date" => $this->info['Transaction_Date'],
                "Currency_ID" => $this->info['Accounts'][$i]['Currency_ID'],
                "Logged_ID" => $this->info[0],
                "Transaction_Description" => $this->info['Accounts'][$i]['Transaction_Description'],
                "Current_Ex_Rate" => $this->info['Accounts'][$i]['Current_Ex_Rate'],
                "second_currency_rate" => $this->info['Accounts'][$i]['second_currency_rate'],
                "main_id" => '0',
                "main_type" => 'mjv',
                "branch_id" => $this->info['branch_id'],
                "job_id" => $this->info['Accounts'][$i]['job_id'],
                "project_id" => $this->info['Accounts'][$i]['project_id'],
                "due_date" => $this->info['Accounts'][$i]['due_date'],
                "subtype" => $this->info['Accounts'][$i]['subtype'],
                "main_nb" => $jv_nb,
                "date_created" => $date_created,
                //we need this in case if user want to insert 0 local or second rate
                "keep_rate" => true,
                "Transaction_ID" => $this->info['Accounts'][$i]['transaction_id']
            );
            JV::addTransaction($data, $flag);

            $trans_id = $this->db->id();
            $close_arr = !empty($this->info['Accounts'][$i]['close_arr']) ? json_decode($this->info['Accounts'][$i]['close_arr'], true) : array();
            if (isset($close_arr)) {
                $rec_type = $this->info['Accounts'][$i]['acc_type'] == 'cust' ? 'cmjv' : 'smjv';
                $row_data = ['rate' => $this->info['Accounts'][$i]['Current_Ex_Rate']];
                CommonBusiness::close_invoices($close_arr, $trans_id, $rec_type, $row_data);
            }
        }
        $arr_log = ['type' => 'mjv', 'main_id' => $this->info['JV_ID'],
            'action' => 1, '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');
            return $this->info['JV_ID'];
        } else {
            $this->db->query('ROLLBACK');
            return array_unique($error);
        }
    }

    public function viewJVInfo($id, $actual) {
        $data = array();
        $totals_arr = array('local_balance' => 0, 'second_balance' => 0
            , 'total_debit_local' => 0, 'total_credit_local' => 0
            , 'total_debit_second' => 0, 'total_credit_second' => 0);
        if (!empty($id)) {
            $year_id = Session::getCurrentYearID($actual);
            $query = $this->db->query("SELECT *, local_amount,
                (Transaction_Amount*second_currency_rate) as second_amount FROM transactions_info 
                WHERE JV_ID = '" . $id . "' and is_actual = '" . $actual . "' and current_year_id = '" . $year_id . "'");

            while ($row = $this->db->assoc($query)) {
                $row['local_rate'] = CurrencyFactory::getLastExRate($row['Currency_ID']);
                $row['second_rate'] = CurrencyFactory::getSecondCurrencyLastExRate($row['Currency_ID']);
                $row['Transaction_Amount_Formatted'] = CommonFunctions::formatNumber($row['Transaction_Amount']);


                $data[$row['Transaction_ID']] = $row;
                //
                if ($row['Transaction_Type'] == 'Debit') {
                    $totals_arr['total_debit_local'] += $row['local_amount'];
                    $totals_arr['total_debit_second'] += $row['second_amount'];
                } else {
                    $totals_arr['total_credit_local'] += $row['local_amount'];
                    $totals_arr['total_credit_second'] += $row['second_amount'];
                }
            }
//            $totals_arr['total_debit_local'] = CommonFunctions::formatNumber($totals_arr['total_debit_local']);
//            $totals_arr['total_debit_second'] = CommonFunctions::formatNumber($totals_arr['total_debit_second']);
//            $totals_arr['total_credit_local'] = CommonFunctions::formatNumber($totals_arr['total_credit_local']);
//            $totals_arr['total_credit_second'] = CommonFunctions::formatNumber($totals_arr['total_credit_second']);
            $totals_arr['local_balance'] = $totals_arr['total_debit_local'] - $totals_arr['total_credit_local'];
            $totals_arr['second_balance'] = $totals_arr['total_debit_second'] - $totals_arr['total_credit_second'];

            $totals_arr['total_debit_local_formatted'] = CommonFunctions::formatNumber($totals_arr['total_debit_local']);
            $totals_arr['total_debit_second_formatted'] = CommonFunctions::formatNumber($totals_arr['total_debit_second']);
            $totals_arr['total_credit_local_formatted'] = CommonFunctions::formatNumber($totals_arr['total_credit_local']);
            $totals_arr['total_credit_second_formatted'] = CommonFunctions::formatNumber($totals_arr['total_credit_second']);
            $totals_arr['local_balance_formatted'] = CommonFunctions::formatNumber($totals_arr['total_debit_local'] - $totals_arr['total_credit_local']);
            $totals_arr['second_balance_formatted'] = CommonFunctions::formatNumber($totals_arr['total_debit_second'] - $totals_arr['total_credit_second']);
        }
        //
        if (!empty($data)) {
            $query = $this->db->query('select p.*,if(p.type="cmjv",i.Invoice_Currency,pr.Currency_ID) as inv_currency,
                if(p.type="cmjv",((i.Invoice_Net_Total+if((i.is_invoice_total_on_shipping=0  and trans_on_change_status=0),i.Invoice_Shipping,0)) - i.Invoice_Paid_Amount - i.Payment_Paid_Amount+(p.Payment_Amount*t.Current_Ex_Rate/i.current_rate)),(pr.Purchase_Net_Total+pr.Purchase_Shipping-pr.Purchase_Paid_Amount-pr.Payment_Paid_Amount+(p.Payment_Amount*t.Current_Ex_Rate/pr.current_rate))) as Remaining_Amount,
                if(p.type="cmjv",i.current_rate,pr.current_rate) as current_rate
                from tbl_invoice_payments p
                join tbl_accounting_transactions t on t.Transaction_ID = p.record_id
                left join tbl_invoices i on i.Invoice_ID_Auto = p.inv_pur_id and p.type="cmjv"
                left join tbl_inventory_purchases pr on pr.Purchase_ID_Auto = p.inv_pur_id and p.type="smjv"
                where record_id in (' . implode(',', array_keys($data)) . ') and type in ("cmjv","smjv")');
            while ($row = $this->db->assoc($query)) {
                $rem_amount = $row['Remaining_Amount'] * $row['current_rate'];
                $data[$row['record_id']]['close_data'][] = ['inv_id' => $row['inv_pur_id'],
                    'inv_amount' => $row['Payment_Amount'],
                    'inv_currency' => $row['inv_currency'],
                    'local_rem' => $rem_amount];
                if (!isset($data[$row['record_id']]['invoices_total']))
                    $data[$row['record_id']]['invoices_total'] = 0;
                $data[$row['record_id']]['invoices_total']+=$row['Payment_Amount'];
            }
        }

        return ['data' => array_values($data), 'totals' => $totals_arr];
    }

    public static function checkCurrentYear($start_date, $end_date, $is_actual) {
        $official = Session::getCurrentYearRange();
        $actual = Session::getActCurrentYearRange();
        $start_year = $is_actual ? $actual['start_date_act'] : $official['start_date'];
        $end_year = $is_actual ? $actual['end_date_act'] : $official['end_date'];
        $is_closed = $is_actual ? $actual['year_closed_act'] : $official['year_closed'];
        $return = '';
        if ($is_closed) {
            $return = CommonLanguage::getMessage('edit_year_alert');
        } elseif ($start_date < $start_year || $end_date > $end_year) {
            $return = CommonLanguage::getMessage('current_year_modify');
        }
        return $return;
    }

    public static function getAccountNameByCode($code) {
        $db = DBConnectorFactory::loadDriver();
        $query = $db->query('SELECT Account_Name FROM tbl_chart_of_accounts WHERE Account_Code = "' . $code . '"');
        $row = $db->assoc($query);
        return $row['Account_Name'];
    }

    public static function getAccountNameByID($id) {
        $db = DBConnectorFactory::loadDriver();
        $query = $db->query('SELECT concat(Account_Name," - ",Account_Code) as Account_Name FROM tbl_chart_of_accounts WHERE Account_ID = "' . $id . '"');
        $row = $db->assoc($query);
        return $row['Account_Name'] ? $row['Account_Name'] : '';
    }

    public static function returnJVID($id, $type, $is_actual) {
        $db = DBConnectorFactory::loadDriver();
        $transaction = $db->query(' SELECT
                                            DISTINCT JV_ID
                                        FROM
                                            tbl_accounting_transactions
                                        WHERE
                                            main_id = "' . $id . '"
                                        AND
                                            main_type = "' . $type . '"
                                        and is_actual = "' . $is_actual . '"
                    ');
        $row = $db->assoc($transaction);
        return $row['JV_ID'];
    }

    //    public function addDailyDeposit($other = false) {
    //        $is_actual = 0;
    //        if (!$other)
    //            $this->db->query('START TRANSACTION');
    //        $user_id = Session::getUserID();
    //        $deposite_account = smc_session()['user_accounts']['deposit_account_id'];
    //        $cash_account = isset($this->info['cash_drawer_id_ref']) ? $this->info['cash_drawer_id_ref'] : smc_session()['user_accounts']['cash_account_id'];
    //        $today = Date::getToday();
    //        $time = date("h:i:s a");
    //        $sum = 0.0;
    //        $rate = CurrencyFactory::getLastExRate($this->info['Currency_ID']);
    //        foreach ($this->info['Deposit'] as $item) {
    //            if ($this->info['Org_Type'] == 1) {
    //                $item['Cash_Category_ID'] = null;
    //                $item['Cash_Category_Value'] = 1;
    //            }
    //            $data = [
    //                &$item['Cash_Category_ID'],
    //                &$item['Deposit_Quantity'],
    //                &$this->info['Currency_ID'],
    //                &$rate,
    //                &$cash_account,
    //                &$deposite_account,
    //                &$today,
    //                &$time,
    //                &$user_id,
    //                &$item['Deposit_Quantity'],
    //                &$time
    //            ];
    //            $this->db->prepare_Stmt("
    //                                    INSERT
    //                                    INTO
    //                                        tbl_daily_deposits
    //                                    (
    //                                        Cash_Category_ID,
    //                                        Deposit_Quantity,
    //                                        Currency_ID,
    //                                        current_rate,
    //                                        Cash_Account_ID,
    //                                        From_Account_ID,
    //                                        Deposit_Date,
    //                                        Deposit_Time,
    //                                        Logged_ID
    //                                    )
    //                                        VALUES
    //                                    (
    //                                        ?,?,?,?,?,?,?,?,?
    //                                    )
    //                                    ON DUPLICATE KEY UPDATE
    //                                        Deposit_Quantity = Deposit_Quantity " . $item['Operation'] . "?,
    //                                        Deposit_Time = ?
    //                                ", $data);
    //            $dip = $this->db->id();
    //
    //            $value = $item['Cash_Category_Value'] * $item['Deposit_Quantity'];
    //
    //            if ($item['Operation'] == "+") {
    //                $sum += $value;
    //            } else {
    //                $sum -= $value;
    //            }
    //        }
    //        $jv = JV::returnJV($is_actual);
    //        $ref = "Dep.";
    //        $desc = isset($this->info['description']) ? $this->info['description'] : "Deposit on " . $today;
    //        if ($sum > 0) {
    //            $data_1 = array(
    //                "JV_ID" => $jv,
    //                "Transaction_Reference" => $ref,
    //                "Transaction_Account" => $deposite_account,
    //                "Transaction_Type" => "Credit",
    //                "Transaction_Amount" => $sum,
    //                "Transaction_Date" => $today,
    //                "Currency_ID" => $this->info['Currency_ID'],
    //                "Logged_ID" => $user_id,
    //                "Transaction_Description" => $desc,
    //                "Current_Ex_Rate" => $rate,
    //                "main_id" => $dip,
    //                "main_type" => "ddp"
    //            );
    //            JV::addTransaction($data_1, $is_actual);
    //            $data_2 = array(
    //                "JV_ID" => $jv,
    //                "Transaction_Reference" => $ref,
    //                "Transaction_Account" => $cash_account,
    //                "Transaction_Type" => "Debit",
    //                "Transaction_Amount" => $sum,
    //                "Transaction_Date" => $today,
    //                "Currency_ID" => $this->info['Currency_ID'],
    //                "Logged_ID" => $user_id,
    //                "Transaction_Description" => $desc,
    //                "Current_Ex_Rate" => $rate,
    //                "main_id" => $dip,
    //                "main_type" => "ddp"
    //            );
    //            JV::addTransaction($data_2, $is_actual);
    //        } elseif ($sum < 0) {
    //            $sum = $sum * (-1);
    //            $data_1 = array(
    //                "JV_ID" => $jv,
    //                "Transaction_Reference" => $ref,
    //                "Transaction_Account" => $deposite_account,
    //                "Transaction_Type" => "Debit",
    //                "Transaction_Amount" => $sum,
    //                "Transaction_Date" => $today,
    //                "Currency_ID" => $this->info['Currency_ID'],
    //                "Logged_ID" => $user_id,
    //                "Transaction_Description" => $desc,
    //                "Current_Ex_Rate" => $rate,
    //                "main_id" => $dip,
    //                "main_type" => "ddp"
    //            );
    //            JV::addTransaction($data_1, $is_actual);
    //            $data_2 = array(
    //                "JV_ID" => $jv,
    //                "Transaction_Reference" => $ref,
    //                "Transaction_Account" => $cash_account,
    //                "Transaction_Type" => "Credit",
    //                "Transaction_Amount" => $sum,
    //                "Transaction_Date" => $today,
    //                "Currency_ID" => $this->info['Currency_ID'],
    //                "Logged_ID" => $user_id,
    //                "Transaction_Description" => $desc,
    //                "Current_Ex_Rate" => $rate,
    //                "main_id" => $dip,
    //                "main_type" => "ddp"
    //            );
    //            JV::addTransaction($data_2, $is_actual);
    //        }
    //        if (!$other) {
    //            $error = $this->db->error();
    //            if (empty($error)) {
    //                $this->db->query("COMMIT");
    //                if (smc_session()['sumatra_print'] == 1) {
    //                    $invoice = new Invoicepos;
    //                    $currency_symbol = CurrencyFactory::getCurrencySymbol($this->info['Currency_ID']);
    //                    $arr = ['deposite_amount' => $this->info['Deposit'][0]['Deposit_Quantity'],
    //                        'Currency_Symbol' => $currency_symbol];
    //                    $invoice->printPosDeposit($arr);
    //                }
    //                return $dip;
    //            } else {
    //                $this->db->query("ROLLBACK");
    //                return array_unique($error);
    //            }
    //        }
    //    }

    public function viewDailyDepositByUser($is_actual = 0) {
        $data = array();
        $user_id = Session::getUserID();
        $today = Date::getToday();
        $query = $this->db->query("SELECT sum(Deposit_WithDrawal_Amount_To*current_rate_to) AS Cash_Quantity 
            FROM tbl_accounting_bank_deposit_withdrawal 
            WHERE Logged_ID = '" . $user_id . "' AND Deposit_WithDrawal_Date = '" . $today . "'
                and To_Account_ID = '" . smc_session()['user_accounts']['cash_account_id'] . "' and Is_Hidden = '" . $is_actual . "'
                group by Logged_ID");
        $symbol = smc_session()['local_currency_symbol'];
        $row = '('.CommonLanguage::getMessage('total_deposit').': ' . CommonFunctions::public_round_number($this->db->assoc($query)['Cash_Quantity']) . ' ' . $symbol . ' )';
        return $row;
    }

    //    public function addDailyWithDrawal($other = false) {
    //        $is_actual = 0;
    //        if (!$other)
    //            $this->db->query('START TRANSACTION');
    //        $user_id = Session::getUserID();
    //        $today = Date::getToday();
    //        $cash_account = isset($this->info['cash_drawer_id_ref']) ? $this->info['cash_drawer_id_ref'] : smc_session()['user_accounts']['cash_account_id'];
    //        $deposite_account = smc_session()['user_accounts']['withdrawal_account_id'];
    //
    //
    //        $time = date("h:i:s a");
    //        $sum = 0.0;
    //        $rate = CurrencyFactory::getLastExRate($this->info['Currency_ID']);
    //        foreach ($this->info['Deposit'] as $item) {
    //            if ($this->info['Org_Type'] == 1) {
    //                $item['Cash_Category_ID'] = null;
    //                $item['Cash_Category_Value'] = 1;
    //            }
    //            $data = [
    //                &$item['Cash_Category_ID'],
    //                &$item['Deposit_Quantity'],
    //                &$this->info['Currency_ID'],
    //                &$rate,
    //                &$cash_account,
    //                &$deposite_account,
    //                &$today,
    //                &$time,
    //                &$user_id,
    //                &$item['Deposit_Quantity'],
    //                &$time
    //            ];
    //
    //            $this->db->prepare_Stmt("
    //                                    INSERT
    //                                    INTO
    //                                        tbl_daily_withdrawal
    //                                    (
    //                                        Cash_Category_ID,
    //                                        WD_Quantity,
    //                                        Currency_ID,
    //                                        current_rate,
    //                                        Cash_Account_ID,
    //                                        From_Account_ID,
    //                                        WD_Date,
    //                                        WD_Time,
    //                                        Logged_ID
    //                                    )
    //                                        VALUES
    //                                    (
    //                                        ?,?,?,?,?,?,?,?,?
    //                                    )
    //                                    ON DUPLICATE KEY UPDATE
    //                                        WD_Quantity = WD_Quantity " . $item['Operation'] . " ?,
    //                                        WD_Time = ?
    //                                ", $data);
    //            $dip = $this->db->id();
    //
    //            $value = $item['Cash_Category_Value'] * $item['Deposit_Quantity'];
    //            if ($item['Operation'] == "+") {
    //                $new_op = "-";
    //                $sum += $value;
    //            } else {
    //                $new_op = "+";
    //                $sum -= $value;
    //            }
    //        }
    //        $jv = JV::returnJV($is_actual);
    //        $ref = "WD.";
    //        $desc = isset($this->info['description']) ? $this->info['description'] : "WithDrawal on " . $today;
    //        if ($sum > 0) {
    //            $data_1 = array(
    //                "JV_ID" => $jv,
    //                "Transaction_Reference" => $ref,
    //                "Transaction_Account" => $deposite_account,
    //                "Transaction_Type" => "Debit",
    //                "Transaction_Amount" => $sum,
    //                "Transaction_Date" => $today,
    //                "Currency_ID" => $this->info['Currency_ID'],
    //                "Logged_ID" => $user_id,
    //                "Transaction_Description" => $desc,
    //                "Current_Ex_Rate" => $rate,
    //                "main_id" => $dip,
    //                "main_type" => "dwd"
    //            );
    //            JV::addTransaction($data_1, $is_actual);
    //            $data_2 = array(
    //                "JV_ID" => $jv,
    //                "Transaction_Reference" => $ref,
    //                "Transaction_Account" => $cash_account,
    //                "Transaction_Type" => "Credit",
    //                "Transaction_Amount" => $sum,
    //                "Transaction_Date" => $today,
    //                "Currency_ID" => $this->info['Currency_ID'],
    //                "Logged_ID" => $user_id,
    //                "Transaction_Description" => $desc,
    //                "Current_Ex_Rate" => $rate,
    //                "main_id" => $dip,
    //                "main_type" => "dwd"
    //            );
    //            JV::addTransaction($data_2, $is_actual);
    //        } elseif ($sum < 0) {
    //            $sum = $sum * (-1);
    //            $data_1 = array(
    //                "JV_ID" => $jv,
    //                "Transaction_Reference" => $ref,
    //                "Transaction_Account" => $deposite_account,
    //                "Transaction_Type" => "Credit",
    //                "Transaction_Amount" => $sum,
    //                "Transaction_Date" => $today,
    //                "Currency_ID" => $this->info['Currency_ID'],
    //                "Logged_ID" => $user_id,
    //                "Transaction_Description" => $desc,
    //                "Current_Ex_Rate" => $rate,
    //                "main_id" => $dip,
    //                "main_type" => "dwd"
    //            );
    //            JV::addTransaction($data_1, $is_actual);
    //            $data_2 = array(
    //                "JV_ID" => $jv,
    //                "Transaction_Reference" => $ref,
    //                "Transaction_Account" => $cash_account,
    //                "Transaction_Type" => "Debit",
    //                "Transaction_Amount" => $sum,
    //                "Transaction_Date" => $today,
    //                "Currency_ID" => $this->info['Currency_ID'],
    //                "Logged_ID" => $user_id,
    //                "Transaction_Description" => $desc,
    //                "Current_Ex_Rate" => $rate,
    //                "main_id" => $dip,
    //                "main_type" => "dwd"
    //            );
    //            JV::addTransaction($data_2, $is_actual);
    //        }
    //        if (!$other) {
    //            $error = $this->db->error();
    //            if (empty($error)) {
    //                $this->db->query("COMMIT");
    //
    //                if (smc_session()['sumatra_print'] == 1) {
    //                    $invoice = new Invoicepos;
    //                    $currency_symbol = CurrencyFactory::getCurrencySymbol($this->info['Currency_ID']);
    //                    $arr = ['withdrawal_amount' => $this->info['Deposit'][0]['Deposit_Quantity'],
    //                        'Currency_Symbol' => $currency_symbol];
    //                    $invoice->printPosWithDraw($arr);
    //                }
    //                return $dip;
    //            } else {
    //                $this->db->query("ROLLBACK");
    //                return array_unique($error);
    //            }
    //        }
    //    }

    public function viewCashCategories() {
        $data = array();
        $query = $this->db->query('SELECT c.*,cr.Currency_Symbol,cr.Currency_Code FROM tbl_cash_categories c LEFT JOIN tbl_common_currency cr ON c.Currency_ID = cr.Currency_ID');
        while ($row = $this->db->assoc($query)) {
            $data[$row['Currency_Code']][] = $row;
        }
        return $data;
    }

    public function getCashCategoriesByCurrency($id) {
        $data = array();
        $query = $this->db->query('SELECT * FROM tbl_cash_categories where Currency_ID = "' . $id . '"');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function getDailyCashPOS($is_actual = 0) {
        //deposit + //payment - // wd - //invoices -
        $user_id = null;
        $today = Date::getToday();
        $cash_account = smc_session()['user_accounts']['cash_account_id'];
        if (!$this->permissions->hasPermission('view_all_pos_invoices'))
            $user_id = Session::getUserID();

        $balance = self::getDailyCashPOSQueries($user_id, $cash_account, $is_actual);
        $symbol = smc_session()['local_currency_symbol'];
        $final_balance = '('.CommonLanguage::getMessage('total_cash').': ' . CommonFunctions::public_round_number($balance) . ' ' . $symbol . ' )';
        return $final_balance;
    }

    public function getDailyCashPOSQueries($user_id, $cash_account, $is_actual = 0) {
        $condition = '';
        if (!empty($this->info['branch_id']))
            $condition .= ' and branch_id = "' . $this->info['branch_id'] . '"';
        else{
            if($this->permissions->hasPermission('Branches')){
                $condition .=  '';
            }else{
                $condition .= ' and branch_id ="' . (smc_session()['user_accounts']['branch_id']) . '"'  ;
            }
    
        }


        if (!is_null($user_id))
            $condition .= ' and Logged_ID = "' . $user_id . '" ';
        $last_close = Invoicepos::get_open_shift_time($user_id);
        $query = $this->db->query('SELECT SUM(Transaction_Amount*Current_Ex_Rate*if(Transaction_Type = "Debit",1,-1)) AS Amount
                                    FROM tbl_accounting_transactions 
                                    WHERE Account_ID = "' . $cash_account . '"
                                        AND date_time >= "' . $last_close . '"
                                        and is_actual = "' . $is_actual . '"
                                        ' . $condition . ' ');
        $row = $this->db->assoc($query);
        $balance = $row['Amount'];
        return $balance;
    }

    public function getSecondCurrencyDailyCashPOS($is_actual = 0) {
        //deposit + //payment - // wd - //invoices -
        $user_id = null;
        $today = Date::getToday();
        $second_curr_cash_account  = smc_session()['account_setting']['second_curr_cash_account'];
        if (!$this->permissions->hasPermission('view_all_pos_invoices'))
            $user_id = Session::getUserID();

        $balance = self::getSecondCurrencyDailyCashPOSQueries($user_id, $second_curr_cash_account, $is_actual);
        $symbol = smc_session()['second_currency']['symbol'];
        $final_balance = '('.CommonLanguage::getMessage('total_second_cash').': ' . CommonFunctions::formatNumber(CommonFunctions::public_round_number($balance)) . ' ' . $symbol . ' )';
        return $final_balance;
    }

    public function getSecondCurrencyDailyCashPOSQueries($user_id, $cash_account, $is_actual = 0) {
        $condition = '';
        if (!empty($this->info['branch_id'])){
            $condition .= ' and branch_id = "' . $this->info['branch_id'] . '"';
        }else{
            if($this->permissions->hasPermission('Branches')){
                $condition .=  '';
            }else{
                $condition .= ' and branch_id ="' . (smc_session()['user_accounts']['branch_id']) . '"'  ;
            }
    
        }
    
        if (!is_null($user_id))
            $condition .= ' and Logged_ID = "' . $user_id . '" ';
        $last_close = Invoicepos::get_open_shift_time($user_id);
        $query = $this->db->query('SELECT SUM(Transaction_Amount *if(Transaction_Type = "Debit",1,-1)) AS Amount
                                    FROM tbl_accounting_transactions 
                                    WHERE Account_ID = "' . $cash_account . '"
                                        AND date_time >= "' . $last_close . '"
                                        and is_actual = "' . $is_actual . '"
                                        ' . $condition . ' ');
        $row = $this->db->assoc($query);
        $balance = $row['Amount'];
        return $balance;
    }


    public function getSetofAccounts($code) {
        //53 cash
        //71 sales
        //45 for deposite and withdrawal
        $data = array();
        $len = strlen($code);
        $query = $this->db->query("SELECT * FROM tbl_chart_of_accounts WHERE LEFT(Account_Code," . $len . ") = '" . $code . "' and Status_ID = 1");
        while ($row = $this->db->assoc($query))
            $data[] = $row;
        return $data;
    }

    public function allAccountsSearch($term = null, $page, $limit) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' AND ( Account_Name LIKE ? OR Account_Code LIKE ? )';
            $data_filter[] = "%" . $term . "%";
            $data_filter[] = "%" . $term . "%";
        }

        $query = $this->db->prepare_Stmt('SELECT Account_ID as id,
                                          concat(Account_Name," - ",Account_Code) as text
                                    FROM 
                                        tbl_chart_of_accounts
                                        WHERE Status_ID = 1
                                    ' . $cond . '
                                    limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);

        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public static function getAccountCodeByID($id) {
        $db = DBConnectorFactory::loadDriver();
        $query = $db->prepare_Stmt('select Account_Code from tbl_chart_of_accounts 
            where Account_ID = ?', [&$id]);
        $account = $db->assoc($query)['Account_Code'];
        return $account;
    }

    public static function getAccountIDsByCodes($code_arr) {
        //        echo"hereeee0";
        //        echo"<pre>";
        //        die(print_r($code_arr));
        $data = array();
        $db = DBConnectorFactory::loadDriver();
        $query = $db->prepare_Stmt('select Account_ID,Account_Code from tbl_chart_of_accounts where Account_Code in (' . implode($code_arr, ',') . ')');
        while ($row = $db->assoc($query)) {
            $data[$row['Account_Code']] = $row['Account_ID'];
        }
        return $data;
    }

    public function update_main_id_type() {
        $this->db->query('update tbl_accounting_transactions set main_type="", main_id=0 ');
        //        $off_years = array();
        $off_years_query = $this->db->query('select current_year_id,current_year from tbl_common_current_year');
        while ($row_off = $this->db->assoc($off_years_query)) {
            echo "============================off: " . $row_off['current_year'] . "===========================<br>";
            self::update_main_id_type_det($row_off['current_year_id'], 0);
        }
        echo "=======================================================<br>";
        $act_years_query = $this->db->query('select current_year_id,current_year from tbl_common_current_year_actual');
        while ($row_act = $this->db->assoc($act_years_query)) {
            echo "============================act: " . $row_act['current_year'] . "===========================<br>";
            self::update_main_id_type_det($row_act['current_year_id'], 1);
        }
    }

    public function update_main_id_type_det($year_id, $is_actual) {
        //        $data = array();
        $updated_jv_id = array();
        //
        /**
         * =====================Payments========================
         */
        $query_pay = $this->db->query('select JV_ID,p.Payment_Auto_ID,p.Payment_ID,p.IsReceipt,t.Transaction_Reference from tbl_payments_on_accounts p 
            join tbl_accounting_transactions t
                on p.Account_ID = t.Account_ID 
                and p.Payment_Amount = t.Transaction_Amount 
                and p.Payment_Date = t.Transaction_Date 
                and p.Currency_ID = t.Currency_ID
                and p.current_year_id = t.current_year_id
                and p.Status_ID = t.is_actual
                and t.Transaction_Type = if(p.IsReceipt = 1 ,"Credit","Debit")
            where t.current_year_id = "' . $year_id . '" and t.is_actual = "' . $is_actual . '" and t.main_type=""
            order by p.Payment_ID asc, JV_ID asc
            ');
        $i = 0;
        $pay_ids = array();
        while ($row = $this->db->assoc($query_pay)) {
            $pay_main_type = ($row['IsReceipt'] == 1 ? 'rec' : 'pay');
            $pay_data = [&$row['Payment_ID'], &$pay_main_type, &$row['JV_ID'], &$year_id, &$is_actual];
            if (!isset($updated_jv_id[$row['JV_ID']]) && !isset($pay_ids[$row['Payment_Auto_ID']])) {
                $i++;
                $updated_jv_id[$row['JV_ID']] = $row['JV_ID'];
                $pay_ids[$row['Payment_Auto_ID']] = $row['Payment_Auto_ID'];
                $this->db->prepare_Stmt('update tbl_accounting_transactions set main_id = ?,main_type = ? 
                    where JV_ID = ? and current_year_id = ? and is_actual = ?', $pay_data);
                if ($row['Transaction_Reference'] != $row['Payment_ID'])
                    echo "JV_ID : " . $row['JV_ID'] . " REF : " . $row['Transaction_Reference'] . " Payment_ID : " . $row['Payment_ID'] . "<br>";
            }
        }
        $skipped_arr = array();
        $query = $this->db->query('select Payment_Auto_ID
            from tbl_payments_on_accounts where Payment_Auto_ID not in (' . implode(',', $pay_ids) . ') 
                and current_year_id = "' . $year_id . '" and Status_ID = "' . $is_actual . '"');
        while ($row_imp = $this->db->assoc($query)) {
            $skipped_arr[] = $row_imp['Payment_Auto_ID'];
        }
        echo "=================================" . implode(',', $skipped_arr) . "===================================<br>";
        echo "Payment/Receipt total : " . $i . '<br>';

        /**
         * =====================Purchases========================
         */
        $query = $this->db->query('select JV_ID,p.Purchase_ID_Auto,t.Transaction_Reference 
            from tbl_inventory_purchases p 
            join tbl_chart_of_accounts c on c.main_id = p.Supplier_ID and c.main_type ="supp" and c.sub_type="main"
            join tbl_accounting_transactions t
                on c.Account_ID = t.Account_ID 
                and (p.Purchase_Net_Total + p.Purchase_VAT) = t.Transaction_Amount 
                and p.Purchase_Date = t.Transaction_Date 
                and p.Currency_ID = t.Currency_ID
                and p.current_year_id = t.current_year_id
                and p.is_actual = t.is_actual
                and t.Transaction_Type = "Credit"
            where t.current_year_id = "' . $year_id . '" and t.is_actual = "' . $is_actual . '" and t.main_type=""
            order by p.Purchase_ID_Auto asc, JV_ID asc
            ');
        $i = 0;
        $ids = array();
        while ($row = $this->db->assoc($query)) {
            $main_type = 'pur';
            $pay_data = [&$row['Purchase_ID_Auto'], &$main_type, &$row['JV_ID'], &$year_id, &$is_actual];
            if (!isset($updated_jv_id[$row['JV_ID']]) && !isset($ids[$row['Purchase_ID_Auto']])) {
                $i++;
                $updated_jv_id[$row['JV_ID']] = $row['JV_ID'];
                $ids[$row['Purchase_ID_Auto']] = $row['Purchase_ID_Auto'];
                $this->db->prepare_Stmt('update tbl_accounting_transactions set main_id = ?,main_type = ? 
                    where JV_ID = ? and current_year_id = ? and is_actual = ?', $pay_data);
                if ($row['Transaction_Reference'] != $row['Purchase_ID_Auto'])
                    echo "JV_ID : " . $row['JV_ID'] . " REF : " . $row['Transaction_Reference'] . " Purchase_ID_Auto : " . $row['Purchase_ID_Auto'] . "<br>";
            }
        }
        echo "Purchase total : " . $i . '<br>';

        /**
         * =====================Service Invoice========================
         */
        $query = $this->db->query('select dtl.Entery_ID,JV_ID,p.Invoice_ID_Auto,t.Transaction_Reference 
            from tbl_invoices p 
            join tbl_chart_of_accounts c on c.main_id = p.Customer_ID and c.main_type ="cust" and c.sub_type="main"
            join tbl_invoice_products_details dtl on p.Invoice_ID_Auto=dtl.Invoice_ID_Auto
            join tbl_accounting_transactions t
                on c.Account_ID = t.Account_ID 
                and (dtl.Product_Total_Price + dtl.vat_amount) = t.Transaction_Amount 
                and p.Invoice_Date = t.Transaction_Date 
                and p.Invoice_Currency = t.Currency_ID
                and p.current_year_id = t.current_year_id
                and p.is_actual = t.is_actual
                and t.Transaction_Type = "Debit"
            where t.current_year_id = "' . $year_id . '" and t.is_actual = "' . $is_actual . '" and t.main_type=""
            order by p.Invoice_ID_Auto asc, JV_ID asc
            ');
        $i = 0;
        $inv_ids = array();
        $ids = array();
        while ($row = $this->db->assoc($query)) {
            $main_type = 'inv';
            $pay_data = [&$row['Invoice_ID_Auto'], &$main_type, &$row['JV_ID'], &$year_id, &$is_actual];
            if (!isset($updated_jv_id[$row['JV_ID']]) && !isset($ids[$row['Entery_ID']])) {
                $i++;
                $updated_jv_id[$row['JV_ID']] = $row['JV_ID'];
                $inv_ids[$row['Invoice_ID_Auto']] = $row['Invoice_ID_Auto'];
                $ids[$row['Entery_ID']] = $row['Entery_ID'];
                $this->db->prepare_Stmt('update tbl_accounting_transactions set main_id = ?,main_type = ? 
                    where JV_ID = ? and current_year_id = ? and is_actual = ?', $pay_data);
                if ($row['Transaction_Reference'] != $row['Entery_ID'] && ($row['Transaction_Reference'] != ('SRV.' . $row['Entery_ID'])))
                    echo "JV_ID : " . $row['JV_ID'] . " REF : " . $row['Transaction_Reference'] . " Invoice_ID_Auto : " . $row['Invoice_ID_Auto'] . "<br>";
            }
        }
        echo "Services Invoices total : " . $i . '<br>';

        /**
         * =====================Sales Invoice========================
         */
        $query = $this->db->query('select JV_ID,p.Invoice_ID_Auto,t.Transaction_Reference 
            from tbl_invoices p 
            join tbl_chart_of_accounts c on c.main_id = p.Customer_ID and c.main_type ="cust" and c.sub_type="main"
            join tbl_accounting_transactions t
                on c.Account_ID = t.Account_ID 
                and (p.Invoice_Net_Total + p.Invoice_VAT) = t.Transaction_Amount 
                and p.Invoice_Date = t.Transaction_Date 
                and p.Invoice_Currency = t.Currency_ID
                and p.current_year_id = t.current_year_id
                and p.is_actual = t.is_actual
                and t.Transaction_Type = "Debit"
            where t.current_year_id = "' . $year_id . '" and t.is_actual = "' . $is_actual . '"  and t.main_type=""
                ' . (empty($inv_ids) ? "" : " and p.Invoice_ID_Auto not in (" . implode(',', $inv_ids) . ") ") . '
            order by p.Invoice_ID_Auto asc, JV_ID asc
            ');
        $i = 0;
        $ids = array();
        while ($row = $this->db->assoc($query)) {
            $main_type = 'inv';
            $pay_data = [&$row['Invoice_ID_Auto'], &$main_type, &$row['JV_ID'], &$year_id, &$is_actual];
            if (!isset($updated_jv_id[$row['JV_ID']]) && !isset($ids[$row['Invoice_ID_Auto']])) {
                $i++;
                $updated_jv_id[$row['JV_ID']] = $row['JV_ID'];
                $ids[$row['Invoice_ID_Auto']] = $row['Invoice_ID_Auto'];
                $this->db->prepare_Stmt('update tbl_accounting_transactions set main_id = ?,main_type = ? 
                    where JV_ID = ? and current_year_id = ? and is_actual = ?', $pay_data);
                if ($row['Transaction_Reference'] != $row['Invoice_ID_Auto'] && ($row['Transaction_Reference'] != ('SRV.' . $row['Invoice_ID_Auto'])))
                    echo "JV_ID : " . $row['JV_ID'] . " REF : " . $row['Transaction_Reference'] . " Invoice_ID_Auto : " . $row['Invoice_ID_Auto'] . "<br>";
            }
        }
        echo "Sales Invoices total : " . $i . '<br>';

        /**
         * =====================Returns========================
         */
        $query = $this->db->query('select JV_ID,p.Return_Invoice_ID,t.Transaction_Reference,p.Is_Customer_Invoice 
            from tbl_accounting_return_invoices p 
            join tbl_chart_of_accounts c on c.main_id = p.Person_ID 
            and c.main_type =if(p.`Is_Customer_Invoice`=0,"supp","cust") and c.sub_type="main"
            join tbl_accounting_transactions t
                on c.Account_ID = t.Account_ID 
                and (p.Return_Net_Total + p.Invoice_VAT) = t.Transaction_Amount 
                and p.Return_Invoice_Date = t.Transaction_Date 
                and p.Currency_ID = t.Currency_ID
                and p.current_year_id = t.current_year_id
                and p.is_actual = t.is_actual
                and t.Transaction_Type = "Credit"
            where t.current_year_id = "' . $year_id . '" and t.is_actual = "' . $is_actual . '" and t.main_type=""
            order by p.Return_Invoice_ID asc, JV_ID asc
            ');
        $i = 0;
        $ids = array();
        while ($row = $this->db->assoc($query)) {
            $main_type = $row['Is_Customer_Invoice'] == 1 ? 'rin' : 'rpr';
            $pay_data = [&$row['Return_Invoice_ID'], &$main_type, &$row['JV_ID'], &$year_id, &$is_actual];
            if (!isset($updated_jv_id[$row['JV_ID']]) && !isset($ids[$row['Return_Invoice_ID']])) {
                $i++;
                $updated_jv_id[$row['JV_ID']] = $row['JV_ID'];
                $ids[$row['Return_Invoice_ID']] = $row['Return_Invoice_ID'];
                $this->db->prepare_Stmt('update tbl_accounting_transactions set main_id = ?,main_type = ? 
                    where JV_ID = ? and current_year_id = ? and is_actual = ?', $pay_data);
                if ($row['Transaction_Reference'] != $row['Return_Invoice_ID'])
                    echo "JV_ID : " . $row['JV_ID'] . " REF : " . $row['Transaction_Reference'] . " Return_Invoice_ID : " . $row['Return_Invoice_ID'] . "<br>";
            }
        }
        echo "Returns total : " . $i . '<br>';
        /**
         * =====================Expenses========================
         */
        $query = $this->db->query('select JV_ID,p.Expense_ID,t.Transaction_Reference
            from  tbl_accounting_expenses p 
            join tbl_accounting_transactions t
                on p.Account_ID = t.Account_ID 
                and abs(p.Expense_Total_Price - t.Transaction_Amount) < (if(p.Currency_ID=144, 0.5, 500))
                and p.Expense_Date = t.Transaction_Date
                and p.Currency_ID = t.Currency_ID
                and p.current_year_id = t.current_year_id
                and p.Is_Hidden = t.is_actual
                and t.Transaction_Type = "Debit"
            where t.current_year_id = "' . $year_id . '" and t.is_actual = "' . $is_actual . '" and t.main_type=""
            order by p.Expense_ID asc, JV_ID asc
            ');
        $i = 0;
        $ids = array();
        while ($row = $this->db->assoc($query)) {
            $main_type = 'exp';
            $pay_data = [&$row['Expense_ID'], &$main_type, &$row['JV_ID'], &$year_id, &$is_actual];
            if (!isset($updated_jv_id[$row['JV_ID']]) && !isset($ids[$row['Expense_ID']])) {
                $i++;
                $updated_jv_id[$row['JV_ID']] = $row['JV_ID'];
                $ids[$row['Expense_ID']] = $row['Expense_ID'];
                $this->db->prepare_Stmt('update tbl_accounting_transactions set main_id = ?,main_type = ? 
                    where JV_ID = ? and current_year_id = ? and is_actual = ?', $pay_data);
                if ($row['Transaction_Reference'] != $row['Expense_ID'])
                    echo "JV_ID : " . $row['JV_ID'] . " REF : " . $row['Transaction_Reference'] . " Expense_ID : " . $row['Expense_ID'] . "<br>";
            }
        }
        //
        $query = $this->db->query('select JV_ID,p.Expense_ID,t.Transaction_Reference,(p.Expense_Total_Price) as vat, 
                sum(t.Transaction_Amount*if(t.Transaction_Type = "Debit",1,-1)) as trans_sum, p.Currency_ID as curr
                            from  tbl_accounting_expenses p 
                            join tbl_accounting_transactions t
                                on p.Account_ID = t.Account_ID 
                                /*and p.Expense_Date = t.Transaction_Date*/ 
                                and p.Currency_ID = t.Currency_ID
                                and p.current_year_id = t.current_year_id
                                and p.Is_Hidden = t.is_actual
                                and t.Transaction_Description = p.Expense_Description
                where t.current_year_id = "' . $year_id . '" and t.is_actual = "' . $is_actual . '" and t.main_type=""
                group by p.Expense_ID having abs(vat - trans_sum) < (if(curr=144, 0.5, 500))
                ORDER BY `p`.`Expense_ID` ASC');
        while ($row = $this->db->assoc($query)) {
            $main_type = 'exp';
            $pay_data = [&$row['Expense_ID'], &$main_type, &$row['JV_ID'], &$year_id, &$is_actual];
            if (!isset($updated_jv_id[$row['JV_ID']]) && !isset($ids[$row['Expense_ID']])) {
                $i++;
                $updated_jv_id[$row['JV_ID']] = $row['JV_ID'];
                $ids[$row['Expense_ID']] = $row['Expense_ID'];
                $this->db->prepare_Stmt('update tbl_accounting_transactions set main_id = ?,main_type = ? 
                    where JV_ID = ? and current_year_id = ? and is_actual = ?', $pay_data);
                if ($row['Transaction_Reference'] != $row['Expense_ID'])
                    echo "JV_ID : " . $row['JV_ID'] . " REF : " . $row['Transaction_Reference'] . " Expense_ID : " . $row['Expense_ID'] . "<br>";
            }
        }
        //
        $query = $this->db->query('select JV_ID,p.Expense_ID,t.Transaction_Reference
            from  tbl_accounting_expenses p 
            join tbl_accounting_transactions t
                on p.Account_ID = t.Account_ID 
                and abs(p.Expense_Total_Price - t.Transaction_Amount) < (if(p.Currency_ID=144, 0.5, 500))
                /*and p.Expense_Date = t.Transaction_Date*/
                and p.Currency_ID = t.Currency_ID
                and p.current_year_id = t.current_year_id
                and p.Is_Hidden = t.is_actual
                and t.Transaction_Type = "Debit"
            where t.current_year_id = "' . $year_id . '" and t.is_actual = "' . $is_actual . '" and t.main_type=""
            ' . (empty($ids) ? "" : " and p.Expense_ID not in (" . implode(',', $ids) . ") ") . '
            order by p.Expense_ID asc, JV_ID asc
            ');
        while ($row = $this->db->assoc($query)) {
            $main_type = 'exp';
            $pay_data = [&$row['Expense_ID'], &$main_type, &$row['JV_ID'], &$year_id, &$is_actual];
            if (!isset($updated_jv_id[$row['JV_ID']]) && !isset($ids[$row['Expense_ID']])) {
                $i++;
                $updated_jv_id[$row['JV_ID']] = $row['JV_ID'];
                $ids[$row['Expense_ID']] = $row['Expense_ID'];
                $this->db->prepare_Stmt('update tbl_accounting_transactions set main_id = ?,main_type = ? 
                    where JV_ID = ? and current_year_id = ? and is_actual = ?', $pay_data);
                if ($row['Transaction_Reference'] != $row['Expense_ID'])
                    echo "JV_ID : " . $row['JV_ID'] . " REF : " . $row['Transaction_Reference'] . " Expense_ID : " . $row['Expense_ID'] . "<br>";
            }
        }
        $skipped_arr = array();
        $query = $this->db->query('select Expense_ID
            from tbl_accounting_expenses where Expense_ID not in (' . implode(',', $ids) . ') 
                and current_year_id = "' . $year_id . '" and Is_Hidden = "' . $is_actual . '"');
        while ($row_imp = $this->db->assoc($query)) {
            $skipped_arr[] = $row_imp['Expense_ID'];
        }
        echo "=================================" . implode(',', $skipped_arr) . "===================================<br>";
        echo "Expenses total : " . $i . '<br>';

        /**
         * =====================Close of year========================
         */
        $this->db->prepare_Stmt('update tbl_accounting_transactions set main_type="cly" where 
            current_year_id = "' . $year_id . '" and is_actual = "' . $is_actual . '" 
                and Transaction_Description in("Closing of year","Opening of year") ');
        echo "Close Year <br>";

        /**
         * ==========================Payroll==========================
         */
        $query = $this->db->query('select JV_ID,p.Payroll_ID
            from tbl_hrm_payroll p 
            join tbl_chart_of_accounts c on c.main_id = p.Employee_ID 
            and c.main_type ="emp" and c.sub_type="exp"
            join tbl_accounting_transactions t
                on c.Account_ID = t.Account_ID  
                and month(p.Payroll_Paid_Date) = month(t.Transaction_Date) 
                and p.Currency_ID = t.Currency_ID
                and t.Transaction_Type = "Debit"
            where t.current_year_id = "' . $year_id . '" and t.is_actual = "' . $is_actual . '" and t.main_type=""
            order by p.Payroll_ID asc, JV_ID asc
            ');
        $i = 0;
        $ids = array();
        while ($row = $this->db->assoc($query)) {
            $main_type = 'prl';
            $pay_data = [&$row['Payroll_ID'], &$main_type, &$row['JV_ID'], &$year_id, &$is_actual];
            if (!isset($updated_jv_id[$row['JV_ID']]) && !isset($ids[$row['Payroll_ID']])) {
                $i++;
                $updated_jv_id[$row['JV_ID']] = $row['JV_ID'];
                $ids[$row['Payroll_ID']] = $row['Payroll_ID'];
                $this->db->prepare_Stmt('update tbl_accounting_transactions set main_id = ?,main_type = ? 
                    where JV_ID = ? and current_year_id = ? and is_actual = ?', $pay_data);
                //                if ($row['Transaction_Reference'] != $row['Return_Invoice_ID'])
                //                    echo "JV_ID : " . $row['JV_ID'] . " REF : " . $row['Transaction_Reference'] . " Return_Invoice_ID : " . $row['Return_Invoice_ID'] . "<br>";
            }
        }
        echo "Payroll total : " . $i . '<br>';
        /**
         * ==========================Deposit/Withdrawal==========================
         */
        $query = $this->db->query('select JV_ID,p.Deposit_WithDrawal_ID,Transaction_Reference
            from tbl_accounting_bank_deposit_withdrawal p 
            join tbl_accounting_transactions t
                on p.From_Account_ID = t.Account_ID  
                and p.Deposit_WithDrawal_Date = t.Transaction_Date
                and p.Currency_From = t.Currency_ID
                and p.Deposit_WithDrawal_Amount = t.Transaction_Amount
                and t.Transaction_Type = "Credit"
                and p.Is_Hidden = t.is_actual
            where t.current_year_id = "' . $year_id . '" and t.is_actual = "' . $is_actual . '" and t.main_type=""
            order by p.Deposit_WithDrawal_ID asc, JV_ID asc
            ');
        $i = 0;
        $ids = array();
        while ($row = $this->db->assoc($query)) {
            $main_type = 'dwt';
            $pay_data = [&$row['Deposit_WithDrawal_ID'], &$main_type, &$row['JV_ID'], &$year_id, &$is_actual];
            if (!isset($updated_jv_id[$row['JV_ID']]) && !isset($ids[$row['Deposit_WithDrawal_ID']])) {
                $i++;
                $updated_jv_id[$row['JV_ID']] = $row['JV_ID'];
                $ids[$row['Deposit_WithDrawal_ID']] = $row['Deposit_WithDrawal_ID'];
                $this->db->prepare_Stmt('update tbl_accounting_transactions set main_id = ?,main_type = ? 
                    where JV_ID = ? and current_year_id = ? and is_actual = ?', $pay_data);
                if ($row['Transaction_Reference'] != $row['Deposit_WithDrawal_ID'])
                    echo "JV_ID : " . $row['JV_ID'] . " REF : " . $row['Transaction_Reference'] . " Deposit_WithDrawal_ID : " . $row['Deposit_WithDrawal_ID'] . "<br>";
            }
        }


        //
        $query = $this->db->query('select JV_ID,p.Deposit_WithDrawal_ID,Transaction_Reference
            from tbl_accounting_bank_deposit_withdrawal p 
            join tbl_accounting_transactions t
                on p.To_Account_ID = t.Account_ID  
                and p.Deposit_WithDrawal_Date = t.Transaction_Date
                and p.Currency_to = t.Currency_ID
                and p.Deposit_WithDrawal_Amount_To = t.Transaction_Amount
                and t.Transaction_Type = "Debit"
                and p.Is_Hidden = t.is_actual
            where t.current_year_id = "' . $year_id . '" and t.is_actual = "' . $is_actual . '" and t.main_type=""
            order by p.Deposit_WithDrawal_ID asc, JV_ID asc
            ');
        //        $i = 0;
        //        $ids = array();
        while ($row = $this->db->assoc($query)) {
            $main_type = 'dwt';
            $pay_data = [&$row['Deposit_WithDrawal_ID'], &$main_type, &$row['JV_ID'], &$year_id, &$is_actual];
            if (!isset($updated_jv_id[$row['JV_ID']]) && !isset($ids[$row['Deposit_WithDrawal_ID']])) {
                $i++;
                $updated_jv_id[$row['JV_ID']] = $row['JV_ID'];
                $ids[$row['Deposit_WithDrawal_ID']] = $row['Deposit_WithDrawal_ID'];
                $this->db->prepare_Stmt('update tbl_accounting_transactions set main_id = ?,main_type = ? 
                    where JV_ID = ? and current_year_id = ? and is_actual = ?', $pay_data);
                if ($row['Transaction_Reference'] != $row['Deposit_WithDrawal_ID'])
                    echo "JV_ID : " . $row['JV_ID'] . " REF : " . $row['Transaction_Reference'] . " Deposit_WithDrawal_ID : " . $row['Deposit_WithDrawal_ID'] . "<br>";
            }
        }


        $skipped_arr = array();
        $query = $this->db->query('select Deposit_WithDrawal_ID
            from tbl_accounting_bank_deposit_withdrawal where Deposit_WithDrawal_ID not in (' . implode(',', $ids) . ') 
                and current_year_id = "' . $year_id . '" and Is_Hidden = "' . $is_actual . '"');
        while ($row_imp = $this->db->assoc($query)) {
            $skipped_arr[] = $row_imp['Deposit_WithDrawal_ID'];
        }
        echo "=================================" . implode(',', $skipped_arr) . "===================================<br>";
        echo "Deposit/Withdrawal total : " . $i . '<br>';

        /**
         * ==========================Prepaid Income/Expense==========================
         */
        $query = $this->db->query('select JV_ID,p.Prepaid_ID,p.Prepaid_Data_ID
            from tbl_accounting_prepaid_data p 
            join tbl_accounting_prepaid_expense_income c on c.Prepaid_ID = p.Prepaid_ID 
            join tbl_accounting_transactions t
                on c.Prepaid_Account_ID = t.Account_ID  
                and p.Transaction_Date = t.Transaction_Date
                and p.Transaction_Amount = t.Transaction_Amount
            where t.current_year_id = "' . $year_id . '" and t.is_actual = "' . $is_actual . '" and t.main_type="" and p.Is_Done=1
            order by p.Prepaid_Data_ID asc, JV_ID asc
            ');
        $i = 0;
        $ids = array();
        while ($row = $this->db->assoc($query)) {
            $main_type = 'pre';
            $pay_data = [&$row['Prepaid_ID'], &$main_type, &$row['JV_ID'], &$year_id, &$is_actual];
            if (!isset($updated_jv_id[$row['JV_ID']]) && !isset($ids[$row['Prepaid_Data_ID']])) {
                $i++;
                $updated_jv_id[$row['JV_ID']] = $row['JV_ID'];
                $ids[$row['Prepaid_Data_ID']] = $row['Prepaid_Data_ID'];
                $this->db->prepare_Stmt('update tbl_accounting_transactions set main_id = ?,main_type = ? 
                    where JV_ID = ? and current_year_id = ? and is_actual = ?', $pay_data);
                //                if ($row['Transaction_Reference'] != $row['Return_Invoice_ID'])
                //                    echo "JV_ID : " . $row['JV_ID'] . " REF : " . $row['Transaction_Reference'] . " Return_Invoice_ID : " . $row['Return_Invoice_ID'] . "<br>";
            }
        }
        echo "Prepaid detail total : " . $i . '<br>';
        $query = $this->db->query('select JV_ID,p.Prepaid_ID
            from tbl_accounting_prepaid_expense_income p 
            join tbl_accounting_transactions t
                on p.Prepaid_Account_ID = t.Account_ID  
                and p.Start_Date = t.Transaction_Date
                and p.Total_Amount = t.Transaction_Amount
            where t.current_year_id = "' . $year_id . '" and t.is_actual = "' . $is_actual . '" and t.main_type="" 
            order by p.Prepaid_ID asc, JV_ID asc
            ');
        $i = 0;
        $ids = array();
        while ($row = $this->db->assoc($query)) {
            $main_type = 'pre';
            $pay_data = [&$row['Prepaid_ID'], &$main_type, &$row['JV_ID'], &$year_id, &$is_actual];
            if (!isset($updated_jv_id[$row['JV_ID']]) && !isset($ids[$row['Prepaid_ID']])) {
                $i++;
                $updated_jv_id[$row['JV_ID']] = $row['JV_ID'];
                $ids[$row['Prepaid_ID']] = $row['Prepaid_ID'];
                $this->db->prepare_Stmt('update tbl_accounting_transactions set main_id = ?,main_type = ? 
                    where JV_ID = ? and current_year_id = ? and is_actual = ?', $pay_data);
                //                if ($row['Transaction_Reference'] != $row['Return_Invoice_ID'])
                //                    echo "JV_ID : " . $row['JV_ID'] . " REF : " . $row['Transaction_Reference'] . " Return_Invoice_ID : " . $row['Return_Invoice_ID'] . "<br>";
            }
        }
        echo "Prepaid detail total : " . $i . '<br>';



        /**
         * =====================Purchases========================
         */
        $query = $this->db->query('select t.JV_ID,p.Depreciation_Date_ID,t.Transaction_Reference 
            FROM `viewdepreciationfulldata` p
            inner join tbl_accounting_transactions t on p.`Debit_Account` = t.Transaction_Account 
                and t.Transaction_Date = `Depreciation_Date` and t.Transaction_Amount = Depreciation_Amount
                and t.Transaction_Type = "Debit" and `Is_Done` = 1
            where t.current_year_id = "' . $year_id . '" and t.is_actual = "' . $is_actual . '" and t.main_type=""
            order by p.Depreciation_Date_ID asc, JV_ID asc ');
        $i = 0;
        $ids = array();
        while ($row = $this->db->assoc($query)) {
            $main_type = 'dep';
            $pay_data = [&$row['Depreciation_Date_ID'], &$main_type, &$row['JV_ID'], &$year_id, &$is_actual];
            if (!isset($updated_jv_id[$row['JV_ID']]) && !isset($ids[$row['Depreciation_Date_ID']])) {
                $i++;
                $updated_jv_id[$row['JV_ID']] = $row['JV_ID'];
                $ids[$row['Depreciation_Date_ID']] = $row['Depreciation_Date_ID'];
                $this->db->prepare_Stmt('update tbl_accounting_transactions set main_id = ?,main_type = ? 
                    where JV_ID = ? and current_year_id = ? and is_actual = ?', $pay_data);
                if ($row['Transaction_Reference'] != $row['Depreciation_Date_ID'])
                    echo "JV_ID : " . $row['JV_ID'] . " REF : " . $row['Transaction_Reference'] . " Depreciation_Date_ID : " . $row['Depreciation_Date_ID'] . "<br>";
            }
        }
        $query = $this->db->query('select t.JV_ID,p.Depreciation_Date_ID,t.Transaction_Reference 
            FROM `viewdepreciationfulldata` p
            inner join tbl_accounting_transactions t on p.`Credit_Account` = t.Transaction_Account 
                and t.Transaction_Date = `Depreciation_Date` and t.Transaction_Amount = Depreciation_Amount
                and t.Transaction_Type = "Credit" and `Is_Done` = 1
            where t.current_year_id = "' . $year_id . '" and t.is_actual = "' . $is_actual . '" and t.main_type=""
            order by p.Depreciation_Date_ID asc, JV_ID asc ');
        $ids = array();
        while ($row = $this->db->assoc($query)) {
            $main_type = 'dep';
            $pay_data = [&$row['Depreciation_Date_ID'], &$main_type, &$row['JV_ID'], &$year_id, &$is_actual];
            if (!isset($updated_jv_id[$row['JV_ID']]) && !isset($ids[$row['Depreciation_Date_ID']])) {
                $i++;
                $updated_jv_id[$row['JV_ID']] = $row['JV_ID'];
                $ids[$row['Depreciation_Date_ID']] = $row['Depreciation_Date_ID'];
                $this->db->prepare_Stmt('update tbl_accounting_transactions set main_id = ?,main_type = ? 
                    where JV_ID = ? and current_year_id = ? and is_actual = ?', $pay_data);
                if ($row['Transaction_Reference'] != $row['Depreciation_Date_ID'])
                    echo "JV_ID : " . $row['JV_ID'] . " REF : " . $row['Transaction_Reference'] . " Depreciation_Date_ID : " . $row['Depreciation_Date_ID'] . "<br>";
            }
        }
        echo "Depreciation total : " . $i . '<br>';

        /**
         * =====================Debit/Credit Note========================
         */
        $query = $this->db->query('select JV_ID,p.Note_ID,t.Transaction_Reference,p.Is_Debit_Note 
            from tbl_accounting_debit_credit_note p 
            join tbl_chart_of_accounts c on c.main_id = p.Supplier_Customer_ID 
            and c.main_type =if(p.`Is_Debit_Note`=0,"cust","supp") and c.sub_type="main"
            join tbl_accounting_transactions t
                on c.Account_ID = t.Account_ID 
                and p.Note_Amount = t.Transaction_Amount 
                and p.Note_Date = t.Transaction_Date 
                and p.Currency_ID = t.Currency_ID
                and p.current_year_id = t.current_year_id
                and p.is_actual = t.is_actual
            where t.current_year_id = "' . $year_id . '" and t.is_actual = "' . $is_actual . '" and t.main_type=""
            order by p.Note_ID asc, JV_ID asc
            ');
        $i = 0;
        $ids = array();
        while ($row = $this->db->assoc($query)) {
            $main_type = $row['Is_Debit_Note'] == 1 ? 'deb' : 'cre';
            $pay_data = [&$row['Note_ID'], &$main_type, &$row['JV_ID'], &$year_id, &$is_actual];
            if (!isset($updated_jv_id[$row['JV_ID']]) && !isset($ids[$row['Note_ID']])) {
                $i++;
                $updated_jv_id[$row['JV_ID']] = $row['JV_ID'];
                $ids[$row['Note_ID']] = $row['Note_ID'];
                $this->db->prepare_Stmt('update tbl_accounting_transactions set main_id = ?,main_type = ? 
                    where JV_ID = ? and current_year_id = ? and is_actual = ?', $pay_data);
                if ($row['Transaction_Reference'] != $row['Note_ID'])
                    echo "JV_ID : " . $row['JV_ID'] . " REF : " . $row['Transaction_Reference'] . " Note_ID : " . $row['Note_ID'] . "<br>";
            }
        }
        echo "Debit/Credit Note total : " . $i . '<br>';
        /**
         * =====================Accrued Expenses========================
         */
        $query_pay = $this->db->query('select JV_ID,p.Accrued_Expense_ID,
            t.Transaction_Reference from tbl_accounting_accrued_expenses p 
            join tbl_accounting_transactions t
                on p.Accrued_Account_ID = t.Account_ID 
                and p.Expected_Amount = t.Transaction_Amount 
                and p.Start_Date = t.Transaction_Date 
                and p.Currency_ID = t.Currency_ID
                and p.current_year_id = t.current_year_id
                and p.Is_Actual = t.is_actual
            where t.current_year_id = "' . $year_id . '" and t.is_actual = "' . $is_actual . '" and t.main_type=""
            order by p.Accrued_Expense_ID asc, JV_ID asc
            ');
        $i = 0;
        $ids = array();
        while ($row = $this->db->assoc($query_pay)) {
            $pay_main_type = 'aex';
            $pay_data = [&$row['Accrued_Expense_ID'], &$pay_main_type, &$row['JV_ID'], &$year_id, &$is_actual];
            if (!isset($updated_jv_id[$row['JV_ID']]) && !isset($ids[$row['Accrued_Expense_ID']])) {
                $i++;
                $updated_jv_id[$row['JV_ID']] = $row['JV_ID'];
                $ids[$row['Accrued_Expense_ID']] = $row['Accrued_Expense_ID'];
                $this->db->prepare_Stmt('update tbl_accounting_transactions set main_id = ?,main_type = ? 
                    where JV_ID = ? and current_year_id = ? and is_actual = ?', $pay_data);
                if ($row['Transaction_Reference'] != $row['Accrued_Expense_ID'])
                    echo "JV_ID : " . $row['JV_ID'] . " REF : " . $row['Transaction_Reference'] . " Accrued_Expense_ID : " . $row['Accrued_Expense_ID'] . "<br>";
            }
        }
        echo "Accrued Expenses total : " . $i . '<br>';
    }

    public function getDWByID($id) {
        $data = array();
        $query = $this->db->query('select d.*,concat(c_from.Account_Name," - ",c_from.Account_Code) as from_account_name, 
            concat(c_to.Account_Name," - ",c_to.Account_Code) as to_account_name,b.branch_name,
            cu_from.Currency_Symbol as currency_from_sym,cu_to.Currency_Symbol as currency_to_sym,
            j.job_name,pr.project_name
            from tbl_accounting_bank_deposit_withdrawal d
            join tbl_chart_of_accounts c_from
            on c_from.Account_ID = d.From_Account_ID
            join tbl_chart_of_accounts c_to
            on c_to.Account_ID = d.To_Account_ID
            left join tbl_organization_branches b on b.branch_id = d.branch_id
            left join tbl_accounting_jobs j on j.job_id = d.job_id
            left join tbl_projects pr on pr.project_id = d.project_id
            join tbl_common_currency cu_from on cu_from.Currency_ID = d.Currency_From
            join tbl_common_currency cu_to on cu_to.Currency_ID = d.Currency_To
            where Deposit_WithDrawal_ID = "' . $id . '"');
        $data = $this->db->assoc($query);
        $data['to_WithDrawal_Amount_word_ar'] = Forms::num_to_word($data['Deposit_WithDrawal_Amount_To'], $data['Currency_To'], 'ar');
        $data['to_WithDrawal_Amount_word_en'] = Forms::num_to_word($data['Deposit_WithDrawal_Amount_To'], $data['Currency_To'], 'en');


        $data['from_WithDrawal_Amount_word_ar'] = Forms::num_to_word($data['Deposit_WithDrawal_Amount'], $data['Currency_From'], 'ar');
        $data['from_WithDrawal_Amount_word_en'] = Forms::num_to_word($data['Deposit_WithDrawal_Amount'], $data['Currency_From'], 'en');

        return $data;
    }

    public function editDepositWithDrawal() {

        $flag = $this->info['Is_Actual'];
        $year_id = $flag ? $this->year_id_actual : $this->year_id;
        $check = CommonFunctions::check_lock_data(['type'=>'deposit','start_date'=>$this->info['Deposit_WithDrawal_Date'],'is_actual'=>$flag]);

//        $check = JV::checkCurrentYear($this->info['Deposit_WithDrawal_Date'], $this->info['Deposit_WithDrawal_Date'], $flag);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $jv_query = $this->db->query("SELECT DISTINCT JV_ID,prefix,date_created,created_logged_id FROM tbl_accounting_transactions  
            WHERE main_id='" . $this->info['dw_id'] . "' AND main_type= 'dwt' 
                AND current_year_id='" . $year_id . "' and is_actual = '" . $flag . "'");
        $jv_assoc = $this->db->assoc($jv_query);
        $mydate = date("Y-m-d H:i:s");
        $date_created = $mydate;
        $created_logged_id = smc_session()['user_logged_id'];
        $logged_id = isset($this->info['Logged_ID']) ? $this->info['Logged_ID'] : smc_session()['user_logged_id'];
        $date_time = isset($this->info['date_time']) ? $this->info['date_time'] : $mydate;
        if (count($jv_assoc) > 0) {
            $jv = $jv_assoc['JV_ID'];
            $prefix = $jv_assoc['prefix'];
            $date_created = $jv_assoc['date_created'];
            $created_logged_id = $jv_assoc['created_logged_id'];
        } else {
            $jv = JV::returnJV($flag);
            $query_branch = $this->db->query('select branch_prefix from tbl_organization_branches where branch_id = "' . $this->info['branch_id'] . '"');
            $branch_prefix = $this->db->assoc($query_branch)['branch_prefix'];
            $prefix = smc_session()['user_accounts']['serial_prefix'] ? smc_session()['user_accounts']['serial_prefix'] : $branch_prefix;
        }
        $query_nb = $this->db->query('select dw_number from tbl_accounting_bank_deposit_withdrawal 
            where Deposit_WithDrawal_ID="' . $this->info['dw_id'] . '"');
        $dw_nb = $this->db->assoc($query_nb)['dw_number'];
        $this->db->query("START TRANSACTION");
        $rate_from = !empty($this->info['rate_from']) ? $this->info['rate_from'] : CurrencyFactory::getLastExRate($this->info['Currency_From']);
        $rate_to = !empty($this->info['rate_to']) ? $this->info['rate_to'] : CurrencyFactory::getLastExRate($this->info['Currency_To']);
        $second_rate_from = !empty($this->info['sec_rate_from']) ? $this->info['sec_rate_from'] : CurrencyFactory::getSecondCurrencyLastExRate($this->info['Currency_From']);
        $second_rate_to = !empty($this->info['sec_rate_to']) ? $this->info['sec_rate_to'] : CurrencyFactory::getSecondCurrencyLastExRate($this->info['Currency_To']);
        $type = 'dwt';
        $delete_jv = [&$this->info['dw_id'], &$type];
        $this->db->prepare_Stmt('delete from tbl_accounting_transactions where main_id = ? and main_type= ? ', $delete_jv);
        $this->info['due_date'] = !empty($this->info['due_date']) ? $this->info['due_date'] : $this->info['Deposit_WithDrawal_Date'];
        $data = [
            &$this->info['To_Account'],
            &$this->info['From_Account'],
            &$this->info['Deposit_WithDrawal_Amount'],
            &$this->info['Deposit_WithDrawal_Amount_To'],
            &$this->info['Currency_From'],
            &$this->info['Currency_To'],
            &$rate_from,
            &$rate_to, &$second_rate_from, &$second_rate_to,
            &$this->info['branch_id'],
            &$this->info['Deposit_WithDrawal_Date'],
            &$this->info['due_date'],
            &$this->info['Deposit_WithDrawal_Description'],
            &$logged_id,
            &$date_time, &$this->info['job_id'], &$this->info['project_id'],
            &$this->info['dw_id']
        ];
        $this->db->prepare_Stmt("update
                                        tbl_accounting_bank_deposit_withdrawal 
                                        set 
                                            To_Account_ID = ?,
                                            From_Account_ID = ?,
                                            Deposit_WithDrawal_Amount = ?,
                                            Deposit_WithDrawal_Amount_To = ?,
                                            Currency_From = ?,
                                            Currency_To = ?,
                                            current_rate_from = ?,
                                            current_rate_to = ?,
                                            second_currency_rate_from=?,second_currency_rate_to = ?,
                                            branch_id = ?,
                                            Deposit_WithDrawal_Date = ?,due_date=?,
                                            Deposit_WithDrawal_Description = ?,
                                            Logged_ID=?,
                                            date_time=?,job_id=?,project_id=?,is_main_transfer = 0
                                            where Deposit_WithDrawal_ID=?


                ", $data);

        $ref_id = $this->info['dw_id'];
        $data_1 = array(
            "JV_ID" => $jv,
            "Transaction_Reference" => $dw_nb,
            "Transaction_Account" => $this->info['From_Account'],
            "Transaction_Type" => "Credit",
            "Transaction_Amount" => $this->info['Deposit_WithDrawal_Amount'],
            "Transaction_Date" => $this->info['Deposit_WithDrawal_Date'],
            "Currency_ID" => $this->info['Currency_From'],
            "Logged_ID" => $logged_id,
            "date_time" => $date_time,
            "date_created" => $date_created,
            "created_logged_id" => $created_logged_id,
            "Transaction_Description" => $this->info['Deposit_WithDrawal_Description'],
            "Current_Ex_Rate" => $rate_from,
            "second_currency_rate" => $second_rate_from,
            "branch_id" => $this->info['branch_id'],
            "main_id" => $ref_id,
            "prefix" => $prefix,
            "main_type" => 'dwt',
            "job_id" => $this->info['job_id'],
            "project_id" => $this->info['project_id'],
            "due_date" => $this->info['due_date']
        );
        JV::addTransaction($data_1, $flag);

        $data_2 = array(
            "JV_ID" => $jv,
            "Transaction_Reference" => $dw_nb,
            "Transaction_Account" => $this->info['To_Account'],
            "Transaction_Type" => "Debit",
            "Transaction_Amount" => $this->info['Deposit_WithDrawal_Amount_To'],
            "Transaction_Date" => $this->info['Deposit_WithDrawal_Date'],
            "Currency_ID" => $this->info['Currency_To'],
            "Logged_ID" => $logged_id,
            "date_created" => $date_created,
            "date_time" => $date_time,
            "created_logged_id" => $created_logged_id,
            "Transaction_Description" => $this->info['Deposit_WithDrawal_Description'],
            "Current_Ex_Rate" => $rate_to,
            "second_currency_rate" => $second_rate_to,
            "branch_id" => $this->info['branch_id'],
            "main_id" => $ref_id,
            "prefix" => $prefix,
            "main_type" => 'dwt',
            "job_id" => $this->info['job_id'],
            "project_id" => $this->info['project_id'],
            "due_date" => $this->info['due_date']
        );
        JV::addTransaction($data_2, $flag);
        $arr_log = ['type' => 'dwt', 'main_id' => $ref_id,
            'action' => 1, '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');
            return [$ref_id];
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function fill_second_rate() {
        $this->db->query('START TRANSACTION;');
        $query = $this->db->query('select * from tbl_accounting_transactions where Currency_ID <> 144 and second_currency_rate=0');
        while ($row = $this->db->assoc($query)) {
            $rate = CurrencyFactory::getCurrencyTimeExchangeRate($row['Currency_ID'], 144, $row['Transaction_Date']);
            $this->db->prepare_Stmt('update tbl_accounting_transactions set second_currency_rate=? where Transaction_ID = ?', [&$rate, $row['Transaction_ID']]);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function checkAccountTransactions() {
        $query = $this->db->query('select * from tbl_accounting_transactions where Account_ID ="' . $this->info['Account_ID'] . '"');
        $rows_nb = $this->db->rowsno($query);
        if ($rows_nb > 0) {
            return 0;
        }
        return 1;
    }

    public function checkJobTransactions() {
        $query = $this->db->query('select * from tbl_accounting_transactions where job_id ="' . $this->info['job_id'] . '" and job_id <>0');
        $rows_nb = $this->db->rowsno($query);
        if ($rows_nb > 0) {
            return 0;
        }
        return 1;
    }

    public function checkProjectTransactions() {
        $query = $this->db->query('select * from tbl_accounting_transactions where project_id ="' . $this->info['project_id'] . '" and project_id <>0');
        $rows_nb = $this->db->rowsno($query);
        if ($rows_nb > 0) {
            return 0;
        }
        return 1;
    }

    public function delete_jv($jv_ids, $is_actual) {
        $error = array();
        $year_id = $is_actual ? $this->year_id_actual : $this->year_id;
        if (smc_session()['year_closed'] == 1) {
            $error[0] = CommonLanguage::getMessage('delete_year_alert');
            return $error;
        }
        $mydate = date("Y-m-d H:i:s");

        foreach ($jv_ids as $jv_id) {
            $query_old = $this->db->query('select p.type,t.Currency_ID,t.Current_Ex_Rate,p.record_id,t.Transaction_Date
            from tbl_accounting_transactions t
            join tbl_invoice_payments p on p.record_id = t.Transaction_ID and p.type in ("cmjv","smjv")
            where t.JV_ID = "' . $jv_id . '" and t.current_year_id = "' . $year_id . '" 
                and t.is_actual="' . $is_actual . '" ');
            while ($row = $this->db->assoc($query_old)) {
                CommonBusiness::return_inv_pur_close_amount($row['type'], $row['Currency_ID'], $row['Current_Ex_Rate'], $row['record_id']);
            }
            $query = $this->db->query('select Transaction_Date from tbl_accounting_transactions t
                where t.JV_ID = "' . $jv_id . '" and t.current_year_id = "' . $year_id . '" 
                and t.is_actual="' . $is_actual . '"');
             $row = $this->db->assoc($query);

            $check = CommonFunctions::check_lock_data(['type' => 'mjv', 'start_date' => $row['Transaction_Date'], 'is_actual' => $is_actual]);
            if ($check !== '') {
                continue;
            }
            $this->db->prepare_Stmt('delete from tbl_accounting_transactions 
            where JV_ID = ? and is_actual = ? and current_year_id = ?', [&$jv_id, &$is_actual, &$year_id]);
            $arr_log = ['type' => 'mjv', 'main_id' => $jv_id,
                'action' => 2, 'date_time' => $mydate,
                'user_id' => smc_session()['user_logged_id']];
            CommonFunctions::add_user_log($arr_log);
        }
        $error = $this->db->error();
        return $error;
    }

    public function delete_deposit_withdrawal($ids) {
        if (smc_session()['year_closed'] == 1) {
            $error = array();
            $error[0] = CommonLanguage::getMessage('delete_year_alert');
            return $error;
        }
        $this->db->query('START TRANSACTION');
        $mydate = date('Y-m-d H:i:s');
        foreach ($ids as $id) {
            $query = $this->db->prepare_Stmt('select Deposit_WithDrawal_Date,Is_Hidden
                from tbl_accounting_bank_deposit_withdrawal where Deposit_WithDrawal_ID = "'.$id.'"');
            $row = $this->db->assoc($query);

            $check = CommonFunctions::check_lock_data(['type' => 'deposit', 'start_date' => $row['Deposit_WithDrawal_Date'], 'is_actual' => $row['Is_Hidden']]);
            if ($check !== '') {
                continue;
            }
            $this->db->prepare_Stmt('delete from tbl_accounting_transactions 
            where main_type = "dwt" and main_id = ?', [&$id]);
            $this->db->prepare_Stmt('delete from tbl_accounting_bank_deposit_withdrawal where Deposit_WithDrawal_ID = ?', [&$id]);


            $arr_log = ['type' => 'dwt', '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 delete_prepaid($id) {
        $query = $this->db->query('select Start_Date,Is_Actual 
            from tbl_accounting_prepaid_expense_income where Prepaid_ID = "'.$id.'"');
        $row = $this->db->assoc($query);
         $check = CommonFunctions::check_lock_data(['type' => 'prepaid', 'start_date' => $row['Start_Date'], 'is_actual' => $row['Is_Actual']]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $this->db->query('START TRANSACTION');
        $this->db->prepare_Stmt('delete from tbl_accounting_prepaid_data where Prepaid_ID = ?', [$id]);
        $this->db->prepare_Stmt('delete from tbl_accounting_prepaid_expense_income where Prepaid_ID = ?', [$id]);
        $this->db->prepare_Stmt('delete from tbl_accounting_transactions where main_id = ? and main_type="pre"', [$id]);
        $error = $this->db->error();
        if (empty($error))
            $this->db->query('COMMIT');
        else
            $this->db->query('ROLLBACK');
        return $error;
    }

    public function change_deposit_actual($id_arr, $is_actual) {
        $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) {
            $query = $this->db->query('select dw_prefix from tbl_accounting_bank_deposit_withdrawal where Deposit_WithDrawal_ID = "' . $id . '"');
            $prefix = $this->db->assoc($query)['dw_prefix'];
            $nb = $this->getmaxDWID($is_actual, $prefix);
            $this->db->prepare_Stmt('update tbl_accounting_bank_deposit_withdrawal set dw_number = ?,
                Is_Hidden=?,is_main_transfer=0,date_time=?,Logged_ID=?
            where Deposit_WithDrawal_ID = ?', [&$nb, &$is_actual, $date_time, $logged_id, &$id]);
            $jv = JV::returnJV($is_actual);
            $this->db->prepare_Stmt('update tbl_accounting_transactions set main_nb= ?, 
            is_actual = ?,JV_ID = ?
            where main_id = ? and main_type="dwt"', [&$nb, &$is_actual, &$jv, &$id]);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function change_jv_actual($id_arr, $is_actual) {
        $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) {
            $query = $this->db->query('select prefix from tbl_accounting_transactions 
                where JV_ID = "' . $id . '" and is_actual = "' . $is_actual . '"');
            $prefix = $this->db->assoc($query)['prefix'];
            $nb = JV::return_max_jv_nb($is_actual, $prefix);
            ;
            $jv = JV::returnJV($is_actual);
            $this->db->prepare_Stmt('update tbl_accounting_transactions set main_nb= ?, 
            is_actual = ?,JV_ID = ?,Logged_ID = ?,date_time = ?
            where JV_ID = ? and main_type="mjv" and is_actual = ? ', [&$nb, &$is_actual, &$jv, $logged_id, $date_time, $id, ($is_actual ? 0 : 1)]);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

}
