<?php

class Payment {

    protected $info = array();
    private $db;
    private $year_id;
    private $year_id_actual;
    private $current_date_time;
    private $logged_id;
    private $perm;
    public $stop_transaction_start_end = false;
    public $mycount = 0;

    public function __construct() {
        $this->db = DBConnectorFactory::loadDriver();
        $this->logged_id = smc_session()['user_logged_id'];
        $this->year_id = Session::getCurrentYearID();
        $this->year_id_actual = Session::getCurrentYearIDActual();
        $this->current_date_time = date("Y-m-d H:i:s");
        $this->perm = new Permissions(Session::getUserID());
    }

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

    public function create($is_return = false) {
        $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']]);
//            }
//        }
        $check = CommonFunctions::check_lock_data(['type' => 'payment', 'start_date' => $this->info['Payment_Date'], 'is_actual' => $this->info['Status_ID']]);

//        $check = JV::checkCurrentYear($this->info['Payment_Date'], $this->info['Payment_Date'], $this->info['Status_ID']);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $this->info['pos_customer_id'] = isset($this->info['pos_customer_id']) ? $this->info['pos_customer_id'] : 0;

        if (!$this->perm->hasPermission($this->info['Status_ID'] == 1 ? ($this->info['IsReceipt'] ? 'act_receipt_without_close_invoice' : 'act_payment_without_close_purchase') : ($this->info['IsReceipt'] ? 'receipt_without_close_invoice' : 'payment_without_close_purchase') )) {
            $data_inv = Invoice::getUnpaidInvoicesByCustomer($this->info['Account_Code'], $this->info['Currency_ID'], null, $this->info['Status_ID'], $this->info['pos_customer_id'], ($this->info['IsReceipt'] ? 'rec' : 'pay'));
            if (!empty($data_inv) && empty($this->info['close_arr'])) {
                $error = array();
                $error[0] = CommonLanguage::getMessage('close_inv_first');
                return $error;
            }
        }
        $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']);

        $app_user_id = isset($this->info['app_user_id']) ? $this->info['app_user_id'] : 0;
        $payment_app_id = isset($this->info['payment_app_id']) ? $this->info['payment_app_id'] : 0;
        $app_branch_id = isset($this->info['app_branch_id']) ? $this->info['app_branch_id'] : 0;
        $returned_detail_id = isset($this->info['detail_id']) && !empty($this->info['is_detail']) ? $this->info['detail_id'] : 0;
        $receipt_detail_id = isset($this->info['detail_id']) && !empty($this->info['is_collect']) ? $this->info['detail_id'] : 0;


        //        $payment_prefix = isset($this->info['payment_prefix']) ? $this->info['payment_prefix'] : smc_session()['user_accounts']['serial_prefix'];
        $flag = $this->info['Status_ID'];
        if (!$this->stop_transaction_start_end)
            $this->db->query('START TRANSACTION');
        $year = $this->info['Status_ID'] == 1 ? $this->year_id_actual : $this->year_id;
        if (isset($this->info['payment_prefix'])) {
            $payment_prefix = $this->info['payment_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'];
            $payment_prefix = smc_session()['user_accounts']['serial_prefix'] ? smc_session()['user_accounts']['serial_prefix'] : $branch_prefix;
        }
        $this->info['is_pending'] = isset($this->info['is_pending']) ? $this->info['is_pending'] : 0;
        $this->info['is_return'] = isset($this->info['is_return']) ? $this->info['is_return'] : 0;
        $this->info['is_pos'] = isset($this->info['is_pos']) ? $this->info['is_pos'] : 0;

        $payment_id = isset($this->info['Payment_Number']) ? $this->info['Payment_Number'] : self::getmaxPaymentID($this->info['IsReceipt'], $this->info['Status_ID'], $payment_prefix, $this->info['is_pending'], $this->info['is_return']);
        //        echo $payment_id.'==';
        $rate = !empty($this->info['Currency_Rate']) ? $this->info['Currency_Rate'] : CurrencyFactory::getLastExRate($this->info['Currency_ID']);

        $second_rate = !empty($this->info['second_currency_rate']) ? $this->info['second_currency_rate'] : CurrencyFactory::getSecondCurrencyLastExRate($this->info['Currency_ID']);
        $jv = JV::returnJV($this->info['Status_ID']);
        $mydate = date("Y-m-d H:i:s");
        $date_time = isset($this->info['date_time']) ? $this->info['date_time'] : $mydate;
        $date_created = isset($this->info['date_created']) ? $this->info['date_created'] : $mydate;
        $logged_id = isset($this->info['Logged_ID']) ? $this->info['Logged_ID'] : smc_session()['user_logged_id'];
        $created_logged_id = isset($this->info['created_logged_id']) ? $this->info['created_logged_id'] : smc_session()['user_logged_id'];

        $this->info['total_payment'] = isset($this->info['total_payment']) ? $this->info['total_payment'] : 0;
        $this->info['subscription_fees_id'] = !empty($this->info['subscription_fees_id']) ? $this->info['subscription_fees_id'] : NULL;
        $this->info['commession_amount'] = isset($this->info['commession_amount']) ? $this->info['commession_amount'] : 0;
        $this->info['salesman_id'] = ($this->info['IsReceipt'] && smc_session()['default_setting']['sales_man_as_logged_user'] == 1 && empty($this->info['salesman_id']) && !empty(smc_session()['user_details']['Employee_ID'])) ? smc_session()['user_details']['Employee_ID'] : (!empty($this->info['salesman_id']) ? $this->info['salesman_id'] : 0);

        $this->info['Payment_Note'] = !empty($this->info['Payment_Note']) ? $this->info['Payment_Note'] : '';
        $data = [&$payment_id, &$payment_prefix, &$this->info['Account_Code'],
            &$this->info['total_payment'], &$this->info['Payment_Date'], &$this->info['Currency_ID'],
            &$this->info['Payment_Note'], &$this->info['IsReceipt'], &$logged_id, &$this->info['Status_ID'],
            &$year, &$rate, &$second_rate, &$payment_app_id, &$app_user_id, &$app_branch_id, &$this->info['branch_id'],
            &$this->info['job_id'], &$date_time, &$date_created,
            &$created_logged_id, &$this->info['project_id'], &$this->info['pos_customer_id'],
            &$this->info['subscription_fees_id'], $this->info['is_pending'], $this->info['is_return'],
            $returned_detail_id, $this->info['salesman_id'], $this->info['is_pos'], $receipt_detail_id];
        //           echo"<pre>"; die(print_r($data));
        $this->db->prepare_Stmt('INSERT INTO `tbl_payment_receipt`(
            ' . (!empty($this->info['fill_auto_id']) ? 'pay_rec_id,' : '') . '
            `pay_rec_nb`, `prefix`, `Account_ID`,
            `total_amount`, `payment_date`, `Currency_ID`, `Payment_Note`, `is_receipt`, `Logged_ID`, `is_actual`,
            `current_year_id`, `current_rate`, `second_currency_rate`, `payment_app_id`, `app_user_id`,
            `app_branch_id`, `branch_id`, `job_id`, `date_time`,  `date_created`,
            `created_logged_id`, `project_id`, `pos_customer_id`, `subscription_fees_id`,is_pending,is_return,
            returned_detail_id,salesman_id,is_pos,receipt_detail_id)
               VALUES(' . (!empty($this->info['fill_auto_id']) ? intval($this->info['fill_auto_id']) . ',' : '') . '?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', $data);
        $pay_id = $this->db->id();

        $arr_log = ['type' => ($this->info['IsReceipt'] ? ($this->info['is_return'] ? 'retr' : 'rec') : ($this->info['is_return'] ? 'retp' : 'pay')), 'main_id' => $pay_id,
            'action' => 0, 'date_time' => $mydate,
            'user_id' => smc_session()['user_logged_id']];
        CommonFunctions::add_user_log($arr_log);

        if (!empty($receipt_detail_id)) {
            $this->db->prepare_Stmt('update tbl_payment_receipt_details set is_collected=1 
                where payment_detail_id=?', [$receipt_detail_id]);
        }
        if (!empty($returned_detail_id)) {
            $this->db->prepare_Stmt('update tbl_payment_receipt_details set is_returned=1 
                where payment_detail_id=?', [$returned_detail_id]);
        }
        $job_trans_type = smc_session()['default_setting']['job_transaction_type'];
        $person_job = ($job_trans_type == 0 || $job_trans_type == 1 ? $this->info['job_id'] : 0);
        $opposite_job = ($job_trans_type == 0 || $job_trans_type == 2 ? $this->info['job_id'] : 0);
        foreach ($this->info['payment'] as $item) {

            $item['is_cheque'] = isset($item['is_cheque']) ? $item['is_cheque'] : 0;
            $item['user_ref'] = !empty($item['user_ref']) ? $item['user_ref'] : '';
            $item['Payment_Note'] = !empty($item['Payment_Note']) ? $item['Payment_Note'] : '';
            $item['Check_Date'] = !empty($item['Check_Date']) ? $item['Check_Date'] : null;
            $item['value_date'] = !empty($item['value_date']) ? $item['value_date'] : null;
            $item['commession_amount'] = isset($item['commession_amount']) ? $item['commession_amount'] : 0;
            $item['ex_rate2'] = isset($item['ex_rate2']) ? $item['ex_rate2'] : 0;
            $item['currency_id'] = !empty($item['currency_id']) ? $item['currency_id'] : $this->info['Currency_ID'];

            $det_second_rate = !empty($item['second_currency_rate']) ? $item['second_currency_rate'] : ($item['currency_id'] == $this->info['Currency_ID'] && !empty($this->info['second_currency_rate']) ? $this->info['second_currency_rate'] : CurrencyFactory::getSecondCurrencyLastExRate($item['currency_id']));
            $item['ex_rate'] = !empty($item['ex_rate']) ? $item['ex_rate'] : CurrencyFactory::getLastExRate($item['currency_id']);
            $item['user_ref'] = !empty($item['user_ref']) ? $item['user_ref'] : '';
            $commission_amount = abs($item['commession_amount']);
            $data = [&$pay_id, &$item['Payment_Type_ID'],
                &$item['Payment_Amount'], &$item['currency_id'],
                &$item['Payment_Note'],
                &$item['Check_Number'],
                &$item['Check_Date'],
                &$item['value_date'], &$item['ex_rate'], &$det_second_rate,
                &$item['is_cheque'], &$item['commession_account'],
                &$item['commession_amount'], &$item['user_ref'], &$item['ex_rate2']
            ];
            //           echo"<pre>"; die(print_r($data));
            $this->db->prepare_Stmt('INSERT INTO `tbl_payment_receipt_details`( `pay_rec_id`,
                `payment_account_id`,payment_amount, `currency_id`, `payment_note`, `cheque_nb`, `payment_due_date`, `value_date`,
                `current_rate`, `second_currency_rate`, `is_cheque`, `commission_acc_id`, `commission_amount`,user_ref, `ex_rate2`)
                values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', $data);
            $sub_id = $this->db->id();
            //            $arr_log = ['type' => ($this->info['IsReceipt'] ? 'rec' : 'pay'), 'main_id' => $pay_id,
            //                'action' => 0, 'date_time' => $mydate,
            //                'user_id' => smc_session()['user_logged_id']];
            //            CommonFunctions::add_user_log($arr_log);

            $pay_acc = $item['Payment_Type_ID'];
            $desc = empty($item['Payment_Note']) ? (($this->info['IsReceipt'] ? ($this->info['is_return'] ? "Return Receipt" : "Receipt from ") : ($this->info['is_return'] ? "Return Payment" : "Payment to ")) . $this->info['Account_Name']) : $item['Payment_Note'];
            $ref = ($this->info['is_return'] ? 'Return ' : '') . ($this->info['IsReceipt'] ? 'Rec.' : 'Pay.') . $payment_prefix . $payment_id;
            if ($this->info['is_pending'] == 0) {
                $main_type = ($this->info['IsReceipt'] ? ($this->info['is_return'] ? "retr" : "rec") : ($this->info['is_return'] ? "retp" : "pay"));
                $dat_2 = ["JV_ID" => $jv,
                    "Transaction_Reference" => $ref,
                    "Transaction_Account" => $this->info['Account_Code'],
                    "Transaction_Type" => ((( $this->info['IsReceipt'] && !$this->info['is_return']) || (!$this->info['IsReceipt'] && $this->info['is_return'])) ? "Credit" : "Debit"),
                    "Transaction_Amount" => $item['Payment_Amount'],
                    "Transaction_Date" => $this->info['Payment_Date'],
                    "user_ref" => $item['user_ref'],
                    "Currency_ID" => $item['currency_id'],
                    "Logged_ID" => $logged_id,
                    "created_logged_id" => $created_logged_id,
                    "date_created" => $date_created,
                    "date_time" => $date_time,
                    "Transaction_Description" => $desc,
                    "Current_Ex_Rate" => $item['ex_rate'],
                    "second_currency_rate" => $det_second_rate,
                    "main_id" => $pay_id,
                    "sub_id" => $sub_id,
                    "main_type" => $main_type,
                    "branch_id" => $this->info['branch_id'],
                    "prefix" => $payment_prefix,
                    "main_nb" => $payment_id,
                    "due_date" => $item['Check_Date'],
                    "job_id" => $person_job,
                    "project_id" => $this->info['project_id'],
                    "pos_customer_id" => $this->info['pos_customer_id'],
                    "salesman_id" => $this->info['salesman_id']
                ];
                JV::addTransaction($dat_2, $flag);
                //            $error = $this->db->error();
                //            echo"<pre>";die(print_r($error));
                $dat_1 = ["JV_ID" => $jv,
                    "Transaction_Reference" => $ref,
                    "Transaction_Account" => $pay_acc,
                    "Transaction_Type" => ((($this->info['IsReceipt'] && !$this->info['is_return']) || (!$this->info['IsReceipt'] && $this->info['is_return'])) ? "Debit" : "Credit"),
                    "Transaction_Amount" => $item['Payment_Amount'],
                    "Transaction_Date" => $this->info['Payment_Date'],
                    "user_ref" => $item['user_ref'],
                    "Currency_ID" => $item['currency_id'],
                    "Logged_ID" => $logged_id,
                    "created_logged_id" => $created_logged_id,
                    "date_created" => $date_created,
                    "date_time" => $date_time,
                    "Transaction_Description" => $desc,
                    "Current_Ex_Rate" => $item['ex_rate'],
                    "second_currency_rate" => $det_second_rate,
                    "main_id" => $pay_id,
                    "sub_id" => $sub_id,
                    "main_type" => $main_type,
                    "branch_id" => $this->info['branch_id'],
                    "prefix" => $payment_prefix,
                    "main_nb" => $payment_id,
                    "due_date" => $item['Check_Date'],
                    "value_date" => $item['value_date'],
                    "check_nb" => $item['Check_Number'],
                    "job_id" => $opposite_job,
                    "project_id" => $this->info['project_id'],
                    "salesman_id" => $this->info['salesman_id']
                ];
                JV::addTransaction($dat_1, $flag);
                $dat_12 = ["JV_ID" => $jv,
                    "Transaction_Reference" => $ref,
                    "Transaction_Account" => $pay_acc,
                    "Transaction_Type" => ((((($this->info['IsReceipt'] && !$this->info['is_return']) || (!$this->info['IsReceipt'] && $this->info['is_return'])) && $item['commession_amount'] > 0) || (((!$this->info['IsReceipt'] && !$this->info['is_return']) || ($this->info['IsReceipt'] && $this->info['is_return'])) && $item['commession_amount'] < 0)) ? "Credit" : "Debit"),
                    "Transaction_Amount" => $commission_amount,
                    "Transaction_Date" => $this->info['Payment_Date'],
                    "user_ref" => $item['user_ref'],
                    "Currency_ID" => $item['currency_id'],
                    "Logged_ID" => $logged_id,
                    "created_logged_id" => $created_logged_id,
                    "date_created" => $date_created,
                    "date_time" => $date_time,
                    "Transaction_Description" => $desc,
                    "Current_Ex_Rate" => $item['ex_rate'],
                    "second_currency_rate" => $det_second_rate,
                    "main_id" => $pay_id,
                    "sub_id" => $sub_id,
                    "main_type" => $main_type,
                    "branch_id" => $this->info['branch_id'],
                    "prefix" => $payment_prefix,
                    "main_nb" => $payment_id,
                    "due_date" => $item['Check_Date'],
                    "value_date" => $item['value_date'],
                    "check_nb" => $item['Check_Number'],
                    "job_id" => $opposite_job,
                    "project_id" => $this->info['project_id'],
                    "salesman_id" => $this->info['salesman_id']
                ];
                JV::addTransaction($dat_12, $flag);
                $dat_13 = ["JV_ID" => $jv,
                    "Transaction_Reference" => $ref,
                    "Transaction_Account" => $item['commession_account'],
                    "Transaction_Type" => ((((($this->info['IsReceipt'] && !$this->info['is_return']) || (!$this->info['IsReceipt'] && $this->info['is_return'])) && $item['commession_amount'] > 0) || (((!$this->info['IsReceipt'] && !$this->info['is_return']) || ($this->info['IsReceipt'] && $this->info['is_return'])) && $item['commession_amount'] < 0)) ? "Debit" : "Credit"),
                    "Transaction_Amount" => $commission_amount,
                    "Transaction_Date" => $this->info['Payment_Date'],
                    "user_ref" => $item['user_ref'],
                    "Currency_ID" => $item['currency_id'],
                    "Logged_ID" => $logged_id,
                    "created_logged_id" => $created_logged_id,
                    "date_created" => $date_created,
                    "date_time" => $date_time,
                    "Transaction_Description" => $desc,
                    "Current_Ex_Rate" => $item['ex_rate'],
                    "second_currency_rate" => $det_second_rate,
                    "main_id" => $pay_id,
                    "sub_id" => $sub_id,
                    "main_type" => $main_type,
                    "branch_id" => $this->info['branch_id'],
                    "prefix" => $payment_prefix,
                    "main_nb" => $payment_id,
                    "due_date" => $item['Check_Date'],
                    "value_date" => $item['value_date'],
                    "check_nb" => $item['Check_Number'],
                    "job_id" => $opposite_job,
                    "project_id" => $this->info['project_id'],
                    "salesman_id" => $this->info['salesman_id']
                ];
                JV::addTransaction($dat_13, $flag);
            }
        }

        //
        if ($this->info['is_return'] == 1 && !empty($returned_detail_id)) {
            $amount = 0;
            $query = $this->db->query('select pay_rec_id from tbl_payment_receipt_details
                where payment_detail_id = "' . $returned_detail_id . '"');
            $returned_pay_id = $this->db->assoc($query)['pay_rec_id'];
            //
            $not_rtrnd_amt = 0;
            $query = $this->db->query('select sum(d.payment_amount*d.current_rate/p.current_rate) as pay_total
                from tbl_payment_receipt_details d 
                join tbl_payment_receipt p on p.pay_rec_id = d.pay_rec_id where
                d.pay_rec_id = "' . $returned_pay_id . '" and payment_detail_id 
                    not in (select returned_detail_id from  tbl_payment_receipt) 
                    and payment_detail_id <> "' . $returned_detail_id . '"');
            $row = $this->db->assoc($query);
            if (!empty($row))
                $not_rtrnd_amt = $row['pay_total'];
            //
            $pay_inv_arr = array();
            if ($this->info['IsReceipt']) {
                $query = $this->db->query('select ip.inv_pur_id,ip.Payment_Amount,
                p.current_rate as pay_rate,i.current_rate as inv_rate
                from tbl_invoice_payments ip
                join tbl_invoices i on i.Invoice_ID_Auto = ip.inv_pur_id and ip.type="rec"
                join tbl_payment_receipt p on p.pay_rec_id = ip.record_id
                where ip.type="rec" and ip.record_id = "' . $returned_pay_id . '" order by Invoice_Due_Date asc');
            } else {
                $query = $this->db->query('select ip.inv_pur_id,ip.Payment_Amount,
                p.current_rate as pay_rate,i.current_rate as inv_rate
                from tbl_invoice_payments ip
                join tbl_inventory_purchases i on i.Purchase_ID_Auto = ip.inv_pur_id and ip.type="pay"
                join tbl_payment_receipt p on p.pay_rec_id = ip.record_id
                where ip.type="pay" and ip.record_id = "' . $returned_pay_id . '" order by Purchase_Due_Date asc');
            }
            while ($row = $this->db->assoc($query)) {
                $pay_amt = min($row['Payment_Amount'], $not_rtrnd_amt);

                if ($not_rtrnd_amt > 0) {
                    $not_rtrnd_amt-=$pay_amt;
                }
                if ($row['Payment_Amount'] > $pay_amt) {
                    if ($not_rtrnd_amt > 0)
                        $not_rtrnd_amt-=$pay_amt;
                    $remove_amt = $row['Payment_Amount'] - $pay_amt;
                    $pay_inv_arr[$row['Invoice_ID_Auto']] = array(
                        'inv_amt' => $remove_amt,
                        'pay_amt' => $remove_amt
                    );
                }
            }
            foreach ($pay_inv_arr as $inv_id => $row) {
                if ($this->info['IsReceipt']) {
                    $this->db->prepare_Stmt('update tbl_invoices 
                    set Payment_Paid_Amount = Payment_Paid_Amount - round(?,7)
                    where Invoice_ID_Auto = ?', [$row['inv_amt'], $inv_id]);
                } else {
                    $this->db->prepare_Stmt('update tbl_inventory_purchases 
                    set Payment_Paid_Amount = Payment_Paid_Amount - round(?,7)
                    where Purchase_ID_Auto = ?', [$row['inv_amt'], $inv_id]);
                }
                //
                $rec_type = $this->info['IsReceipt'] ? 'rec' : 'pay';
                $this->db->prepare_Stmt('update tbl_invoice_payments 
                    set Payment_Amount = Payment_Amount - ?
                    where inv_pur_id = ? and type = ?', [$row['pay_amt'], $inv_id, $rec_type]);
                //
                $this->db->prepare_Stmt('delete from tbl_invoice_payments where Payment_Amount=0 and type in ("rec","pay")');
            }
        }

//        $local_currency = CurrencyFactory::returnLocalCurrency();
        if (isset($this->info['close_arr'])) {
            $rec_type = $this->info['IsReceipt'] ? 'rec' : 'pay';
            $row_data = ['rate' => $rate];
            CommonBusiness::close_invoices($this->info['close_arr'], $pay_id, $rec_type, $row_data);
//            foreach ($this->info['close_arr'] as $close) {
//                $rec_type = $this->info['IsReceipt'] ? 'rec' : 'pay';
//                $this->db->prepare_Stmt('insert into  tbl_invoice_payments (Payment_Auto_ID,Invoice_ID_Auto,Payment_Amount,type)
//                values(?,?,?,?)', [&$pay_id, &$close['inv_id'], &$close['inv_amount'], $rec_type]);
//                $local_amount = $close['inv_amount'] * $rate;
//                $amount_inv = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($local_currency, $close['inv_currency'], $local_amount);
//                if ($this->info['IsReceipt']) {
//                    $this->db->prepare_Stmt('update tbl_invoices 
//                    set Payment_Paid_Amount = Payment_Paid_Amount + ?,is_main_transfer = 0 
//                        where Invoice_ID_Auto = ?', [&$amount_inv, &$close['inv_id']]);
//                } else {
//                    $this->db->prepare_Stmt('update tbl_inventory_purchases 
//                    set Payment_Paid_Amount = Payment_Paid_Amount + ?
//                        where Purchase_ID_Auto = ?', [&$amount_inv, &$close['inv_id']]);
//                }
//            }
        }



        $error = $this->db->error();
 
        if (empty($error)) {
            if (!$this->stop_transaction_start_end)
                $this->db->query('COMMIT');
            token_unique::remove_token();
        } else {
            if (!$this->stop_transaction_start_end)
                $this->db->query('ROLLBACK');
        }
        return $is_return ? [$payment_id, $payment_prefix, $pay_id] : array_unique($error);
    }

    //    public function create($is_return = false) {
    //        $check = JV::checkCurrentYear($this->info['Payment_Date'], $this->info['Payment_Date'], $this->info['Status_ID']);
    //        if ($check !== '') {
    //            $error = array();
    //            $error[0] = $check;
    //            return $error;
    //        }
    //        $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']);
    //
    //        $app_user_id = isset($this->info['app_user_id']) ? $this->info['app_user_id'] : 0;
    //        $payment_app_id = isset($this->info['payment_app_id']) ? $this->info['payment_app_id'] : 0;
    //        $app_branch_id = isset($this->info['app_branch_id']) ? $this->info['app_branch_id'] : 0;
    //
    //
    //        //        $payment_prefix = isset($this->info['payment_prefix']) ? $this->info['payment_prefix'] : smc_session()['user_accounts']['serial_prefix'];
    //        $flag = $this->info['Status_ID'];
    //        if (!$this->stop_transaction_start_end)
    //            $this->db->query('START TRANSACTION');
    //        $year = $this->info['Status_ID'] == 1 ? $this->year_id_actual : $this->year_id;
    //        if (isset($this->info['payment_prefix'])) {
    //            $payment_prefix = $this->info['payment_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'];
    //            $payment_prefix = smc_session()['user_accounts']['serial_prefix'] ? smc_session()['user_accounts']['serial_prefix'] : $branch_prefix;
    //        }
    //        $payment_id = isset($this->info['Payment_Number']) ? $this->info['Payment_Number'] : self::getmaxPaymentID($this->info['IsReceipt'], $this->info['Status_ID'], $payment_prefix);
    //        //        echo $payment_id.'==';
    //        $rate = !empty($this->info['Currency_Rate']) ? $this->info['Currency_Rate'] : CurrencyFactory::getLastExRate($this->info['Currency_ID']);
    //
    //        $second_rate = CurrencyFactory::getSecondCurrencyLastExRate($this->info['Currency_ID']);
    //        $jv = JV::returnJV($this->info['Status_ID']);
    //        $mydate = date("Y-m-d H:i:s");
    //        $date_time = isset($this->info['date_time']) ? $this->info['date_time'] : $mydate;
    //        $date_created = isset($this->info['date_created']) ? $this->info['date_created'] : $mydate;
    //        $logged_id = isset($this->info['Logged_ID']) ? $this->info['Logged_ID'] : smc_session()['user_logged_id'];
    //        $created_logged_id = isset($this->info['created_logged_id']) ? $this->info['created_logged_id'] : smc_session()['user_logged_id'];
    //        foreach ($this->info['payment'] as $item) {
    //            $item['is_cheque'] = isset($item['is_cheque']) ? $item['is_cheque'] : 0;
    //            $item['pos_customer_id'] = isset($item['pos_customer_id']) ? $item['pos_customer_id'] : 0;
    //            $item['user_ref'] = !empty($item['user_ref']) ? $item['user_ref'] : '';
    //            $item['subscription_fees_id'] = !empty($item['subscription_fees_id']) ? $item['subscription_fees_id'] : NULL;
    //            $item['Check_Date'] = !empty($item['Check_Date']) ? $item['Check_Date'] : null;
    //            $item['value_date'] = !empty($item['value_date']) ? $item['value_date'] : null;
    //            $item['commession_amount'] = isset($item['commession_amount']) ? $item['commession_amount'] : 0;
    //            $data = [&$payment_id,
    //                     &$this->info['Payment_Date'],
    //                     &$this->info['Currency_ID'],
    //                     &$this->info['Account_Code'],
    //                     &$item['Payment_Type_ID'],
    //                     &$item['Payment_Amount'],
    //                     &$item['Payment_Note'],
    //                     &$this->info['IsReceipt'],
    //                     &$logged_id,
    //                     &$created_logged_id,
    //                     &$this->info['Status_ID'],
    //                     &$item['Check_Number'],
    //                     &$item['Check_Date'],
    //                     &$item['value_date'],
    //                     &$item['is_cheque'],
    //                     &$this->info['branch_id'],
    //                     &$rate, &$second_rate,
    //                     &$app_user_id,
    //                     &$payment_app_id,
    //                     &$payment_prefix,
    //                     &$year,
    //                     &$date_time,
    //                     &$date_created,
    //                     &$this->info['job_id'],
    //                     &$this->info['project_id'],
    //                     &$item['commession_amount'],
    //                     &$item['commession_account'], &$app_branch_id, &$item['user_ref'], &$item['pos_customer_id'],
    //                     &$item['subscription_fees_id']
    //                    ];
    //            //           echo"<pre>"; die(print_r($data));
    //            $this->db->prepare_Stmt('
    //                                INSERT INTO
    //                                    tbl_payments_on_accounts
    //                                    (
    //                                    ' . (!empty($this->info['fill_auto_id']) ? 'Payment_Auto_ID,' : '') . '
    //                                    Payment_ID,
    //                                    Payment_Date,
    //                                    Currency_ID,
    //                                    Account_ID,
    //                                    Payment_Type_ID,
    //                                    Payment_Amount,
    //                                    Payment_Note,
    //                                    IsReceipt,
    //                                    Logged_ID,
    //                                    created_logged_id,
    //                                    Status_ID,
    //                                    Cheque_Number,
    //                                    Payment_Due_Date,
    //                                    value_date,
    //                                    is_cheque,
    //                                    branch_id,
    //                                    current_rate,second_currency_rate,
    //                                    app_user_id,
    //                                    payment_app_id,
    //                                    payment_prefix,
    //                                    current_year_id,date_time,date_created,job_id,project_id,commession_amount,commession_acc_id,app_branch_id,user_ref,pos_customer_id,subscription_fees_id
    //                                    )VALUES(' . (!empty($this->info['fill_auto_id']) ? intval($this->info['fill_auto_id']) . ',' : '') . '?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', $data);
    //            $pay_id = $this->db->id();
    //            $arr_log = ['type' => ($this->info['IsReceipt'] ? 'rec' : 'pay'), 'main_id' => $pay_id,
    //                        'action' => 0, 'date_time' => $mydate,
    //                        'user_id' => smc_session()['user_logged_id']];
    //            CommonFunctions::add_user_log($arr_log);
    //
    //            //            $payment_acc = $this->db->query("SELECT Account_ID FROM tbl_payment_types 
    //            //                                            WHERE Payment_Type_ID = '" . $item['Payment_Type_ID'] . "'");
    //            //            $pay_acc = $this->db->assoc($payment_acc)['Account_ID'];
    //            $pay_acc = $item['Payment_Type_ID'];
    //            $desc = empty($item['Payment_Note']) ? (($this->info['IsReceipt'] ? "Receipt from " : "Payment to ") . $this->info['Account_Name']) : $item['Payment_Note'];
    //            $ref = ($this->info['IsReceipt'] ? 'Rec.' : 'Pay.') . $payment_prefix . $payment_id;
    //
    //            $dat_2 = ["JV_ID" => $jv,
    //                      "Transaction_Reference" => $ref,
    //                      "Transaction_Account" => $this->info['Account_Code'],
    //                      "Transaction_Type" => ($this->info['IsReceipt'] ? "Credit" : "Debit"),
    //                      "Transaction_Amount" => $item['Payment_Amount'],
    //                      "Transaction_Date" => $this->info['Payment_Date'],
    //                      "user_ref" => $item['user_ref'],
    //                      "Currency_ID" => $this->info['Currency_ID'],
    //                      "Logged_ID" => $logged_id,
    //                      "created_logged_id" => $created_logged_id,
    //                      "date_created" => $date_created,
    //                      "date_time" => $date_time,
    //                      "Transaction_Description" => $desc,
    //                      "Current_Ex_Rate" => $rate,
    //                      "second_currency_rate" => $second_rate,
    //                      "main_id" => $payment_id,
    //                      "main_type" => ($this->info['IsReceipt'] ? "rec" : "pay"),
    //                      "branch_id" => $this->info['branch_id'],
    //                      "prefix" => $payment_prefix,
    //                      "main_nb" => $payment_id,
    //                      "due_date" => $item['Check_Date'],
    //                      "job_id" => $this->info['job_id'],
    //                      "project_id" => $this->info['project_id'],
    //                      "pos_customer_id" => $item['pos_customer_id']
    //                     ];
    //            JV::addTransaction($dat_2, $flag);
    //            //            $error = $this->db->error();
    //            //            echo"<pre>";die(print_r($error));
    //            $dat_1 = ["JV_ID" => $jv,
    //                      "Transaction_Reference" => $ref,
    //                      "Transaction_Account" => $pay_acc,
    //                      "Transaction_Type" => ($this->info['IsReceipt'] ? "Debit" : "Credit"),
    //                      "Transaction_Amount" => $item['Payment_Amount'],
    //                      "Transaction_Date" => $this->info['Payment_Date'],
    //                      "user_ref" => $item['user_ref'],
    //                      "Currency_ID" => $this->info['Currency_ID'],
    //                      "Logged_ID" => $logged_id,
    //                      "created_logged_id" => $created_logged_id,
    //                      "date_created" => $date_created,
    //                      "date_time" => $date_time,
    //                      "Transaction_Description" => $desc,
    //                      "Current_Ex_Rate" => $rate,
    //                      "second_currency_rate" => $second_rate,
    //                      "main_id" => $payment_id,
    //                      "main_type" => ($this->info['IsReceipt'] ? "rec" : "pay" ),
    //                      "branch_id" => $this->info['branch_id'],
    //                      "prefix" => $payment_prefix,
    //                      "main_nb" => $payment_id,
    //                      "due_date" => $item['Check_Date'],
    //                      "value_date" => $item['value_date'],
    //                      "check_nb" => $item['Check_Number'],
    //                      "job_id" => $this->info['job_id'],
    //                      "project_id" => $this->info['project_id']
    //                     ];
    //            JV::addTransaction($dat_1, $flag);
    //            $dat_12 = ["JV_ID" => $jv,
    //                       "Transaction_Reference" => $ref,
    //                       "Transaction_Account" => $pay_acc,
    //                       "Transaction_Type" => ($this->info['IsReceipt'] ? "Credit" : "Debit"),
    //                       "Transaction_Amount" => $item['commession_amount'],
    //                       "Transaction_Date" => $this->info['Payment_Date'],
    //                       "user_ref" => $item['user_ref'],
    //                       "Currency_ID" => $this->info['Currency_ID'],
    //                       "Logged_ID" => $logged_id,
    //                       "created_logged_id" => $created_logged_id,
    //                       "date_created" => $date_created,
    //                       "date_time" => $date_time,
    //                       "Transaction_Description" => $desc,
    //                       "Current_Ex_Rate" => $rate,
    //                       "second_currency_rate" => $second_rate,
    //                       "main_id" => $payment_id,
    //                       "main_type" => ($this->info['IsReceipt'] ? "rec" : "pay" ),
    //                       "branch_id" => $this->info['branch_id'],
    //                       "prefix" => $payment_prefix,
    //                       "main_nb" => $payment_id,
    //                       "due_date" => $item['Check_Date'],
    //                       "value_date" => $item['value_date'],
    //                       "check_nb" => $item['Check_Number'],
    //                       "job_id" => $this->info['job_id'],
    //                       "project_id" => $this->info['project_id']
    //                      ];
    //            JV::addTransaction($dat_12, $flag);
    //            $dat_13 = ["JV_ID" => $jv,
    //                       "Transaction_Reference" => $ref,
    //                       "Transaction_Account" => $item['commession_account'],
    //                       "Transaction_Type" => ($this->info['IsReceipt'] ? "Debit" : "Credit"),
    //                       "Transaction_Amount" => $item['commession_amount'],
    //                       "Transaction_Date" => $this->info['Payment_Date'],
    //                       "user_ref" => $item['user_ref'],
    //                       "Currency_ID" => $this->info['Currency_ID'],
    //                       "Logged_ID" => $logged_id,
    //                       "created_logged_id" => $created_logged_id,
    //                       "date_created" => $date_created,
    //                       "date_time" => $date_time,
    //                       "Transaction_Description" => $desc,
    //                       "Current_Ex_Rate" => $rate,
    //                       "second_currency_rate" => $second_rate,
    //                       "main_id" => $payment_id,
    //                       "main_type" => ($this->info['IsReceipt'] ? "rec" : "pay" ),
    //                       "branch_id" => $this->info['branch_id'],
    //                       "prefix" => $payment_prefix,
    //                       "main_nb" => $payment_id,
    //                       "due_date" => $item['Check_Date'],
    //                       "value_date" => $item['value_date'],
    //                       "check_nb" => $item['Check_Number'],
    //                       "job_id" => $this->info['job_id'],
    //                       "project_id" => $this->info['project_id']
    //                      ];
    //            JV::addTransaction($dat_13, $flag);
    //        }
    //
    //        $local_currency = CurrencyFactory::returnLocalCurrency();
    //        if ($this->info['IsReceipt'] && isset($this->info['close_arr'])) {
    //            foreach ($this->info['close_arr'] as $close) {
    //                $this->db->prepare_Stmt('insert into  tbl_invoice_payments (Payment_Auto_ID,Invoice_ID_Auto,Payment_Amount)
    //                values(?,?,?)', [&$pay_id, &$close['inv_id'], &$close['inv_amount']]);
    //                $local_amount = $close['inv_amount'] * $rate;
    //                $amount_inv = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($local_currency, $close['inv_currency'], $local_amount);
    //                $this->db->prepare_Stmt('update tbl_invoices 
    //                    set Payment_Paid_Amount = Payment_Paid_Amount + ?,is_main_transfer = 0 
    //                        where Invoice_ID_Auto = ?', [&$amount_inv, &$close['inv_id']]);
    //            }
    //        }
    //
    //
    //
    //        $error = $this->db->error();
    //        if (empty($error)) {
    //            if (!$this->stop_transaction_start_end)
    //                $this->db->query('COMMIT');
    //        } else {
    //            if (!$this->stop_transaction_start_end)
    //                $this->db->query('ROLLBACK');
    //        }
    //        return $is_return ? [$payment_id, $payment_prefix] : array_unique($error);
    //    }
    public function editPayment($is_return = false) {
        $check = CommonFunctions::check_lock_data(['type' => 'payment', 'start_date' => $this->info['Payment_Date'], 'is_actual' => $this->info['Status_ID']]);

//        $check = JV::checkCurrentYear($this->info['Payment_Date'], $this->info['Payment_Date'], $this->info['Status_ID']);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $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']);

        $flag = $this->info['Status_ID'];
        if (!$this->stop_transaction_start_end)
            $this->db->query('START TRANSACTION');
        //        $type = $this->info['IsReceipt'] ? 'rec' : 'pay';
        $main_type = ($this->info['IsReceipt'] ? ($this->info['is_return'] ? "retr" : "rec") : ($this->info['is_return'] ? "retp" : "pay"));

        $jv_query = $this->db->query("SELECT DISTINCT JV_ID,is_actual FROM tbl_accounting_transactions  
            WHERE main_id='" . $this->info['pay_id'] . "' AND main_type= '" . $main_type . "' ");
        $jv_assoc = $this->db->assoc($jv_query);

        if (!empty($jv_assoc) && $this->info['Status_ID'] == $jv_assoc['is_actual'])
            $jv = $jv_assoc['JV_ID'];
        else
            $jv = JV::returnJV($this->info['Status_ID']);

        $this->db->prepare_Stmt("DELETE FROM tbl_accounting_transactions  
            WHERE main_id= ? AND main_type= '" . $main_type . "' ", [&$this->info['pay_id']]);


        $rate = !empty($this->info['Currency_Rate']) ? $this->info['Currency_Rate'] : CurrencyFactory::getLastExRate($this->info['Currency_ID']);

        $second_rate = !empty($this->info['second_currency_rate']) ? $this->info['second_currency_rate'] : CurrencyFactory::getSecondCurrencyLastExRate($this->info['Currency_ID']);
        $mydate = date("Y-m-d H:i:s");
        $date_time = isset($this->info['date_time']) ? $this->info['date_time'] : $mydate;
        $logged_id = isset($this->info['Logged_ID']) ? $this->info['Logged_ID'] : smc_session()['user_logged_id'];
        $created_logged_id = $date_created = $old_currency = $old_rate = $payment_id = 0;
        $payment_prefix = '';
        $query = $this->db->query('select date_created,created_logged_id,Currency_ID,pay_rec_nb,
            prefix,pos_customer_id,current_rate 
            from tbl_payment_receipt where pay_rec_id = "' . $this->info['pay_id'] . '"');
        $row_pay = $this->db->assoc($query);
        $created_logged_id = $row_pay['created_logged_id'];
        $date_created = $row_pay['date_created'];
        $old_currency = $row_pay['Currency_ID'];
        $old_rate = $row_pay['current_rate'];
        $payment_id = $row_pay['pay_rec_nb'];
        $payment_prefix = $row_pay['prefix'];
        $this->info['pos_customer_id'] = !empty($this->info['pos_customer_id']) ? $this->info['pos_customer_id'] : $row_pay['pos_customer_id'];

        $this->info['is_pending'] = isset($this->info['is_pending']) ? $this->info['is_pending'] : 0;
        $this->info['is_return'] = isset($this->info['is_return']) ? $this->info['is_return'] : 0;
        $this->info['salesman_id'] = ($this->info['IsReceipt'] && smc_session()['default_setting']['sales_man_as_logged_user'] == 1 && empty($this->info['salesman_id']) && !empty(smc_session()['user_details']['Employee_ID'])) ? smc_session()['user_details']['Employee_ID'] : (!empty($this->info['salesman_id']) ? $this->info['salesman_id'] : 0);

        $data = [&$this->info['Account_Code'],
            &$this->info['total_payment'], &$this->info['Payment_Date'], &$this->info['Currency_ID'],
            &$this->info['Payment_Note'], &$logged_id,
            &$rate, &$second_rate, &$this->info['branch_id'],
            &$this->info['job_id'], &$date_time,
            &$this->info['project_id'], &$this->info['Status_ID'], &$this->info['is_pending'],
            &$this->info['is_return'], $this->info['salesman_id'],
            &$this->info['pay_id']];
        $this->db->prepare_Stmt('update `tbl_payment_receipt`
            set
             `Account_ID`=?,`total_amount`=?, `payment_date`=?, `Currency_ID`=?, `Payment_Note`=?,  `Logged_ID`=?, 
             `current_rate`=?, `second_currency_rate`=?, 
             `branch_id`=?, `job_id`=?, `date_time`=?,  `project_id`=?,
             is_main_transfer=0,is_actual = ?,is_pending=?,is_return=?,salesman_id=? where pay_rec_id = ?', $data);
        $pay_id = $this->info['pay_id'];
        $arr_log = ['type' => ($this->info['IsReceipt'] ? ($this->info['is_return'] ? 'retr' : 'rec') : ($this->info['is_return'] ? 'retp' : 'pay')), 'main_id' => $pay_id,
            'action' => 1, 'date_time' => $mydate,
            'user_id' => smc_session()['user_logged_id']];
        CommonFunctions::add_user_log($arr_log);
        //
        $record_type = $this->info['IsReceipt'] ? 'rec' : 'pay';
        CommonBusiness::return_inv_pur_close_amount($record_type, $old_currency, $old_rate, $pay_id);
//        if ($this->info['IsReceipt']) {
//            $this->db->prepare_Stmt('update tbl_invoices i join tbl_invoice_payments p 
//                                on i.Invoice_ID_Auto = p.inv_pur_id and p.type="rec"
//                                SET Payment_Paid_Amount = Payment_Paid_Amount -(if(Invoice_Currency = "' . $old_currency . '",Payment_Amount,((Payment_Amount*"' . $old_rate . '")/current_rate)))
//                                     ,is_main_transfer = 0
//                                    where record_id ="' . $pay_id . '" ');
//            $this->db->prepare_Stmt("DELETE FROM tbl_invoice_payments  WHERE record_id  ='" . $pay_id . "' and type='rec' ");
//        } else {
//            $this->db->prepare_Stmt('update tbl_inventory_purchases i  join tbl_invoice_payments p
//                                on i.Purchase_ID_Auto = p.inv_pur_id  and p.type="pay"
//                                SET Payment_Paid_Amount = Payment_Paid_Amount -(if(Currency_ID = "' . $old_currency . '",Payment_Amount,((Payment_Amount*"' . $old_rate . '")/current_rate)))
//                                    where record_id ="' . $pay_id . '" ');
//            $this->db->prepare_Stmt("DELETE FROM tbl_invoice_payments  WHERE record_id  ='" . $pay_id . "' and type='pay' ");
//        }


        $this->db->prepare_Stmt('delete from tbl_payment_receipt_details where pay_rec_id = ?', [&$pay_id]);
        $job_trans_type = smc_session()['default_setting']['job_transaction_type'];
        $person_job = ($job_trans_type == 0 || $job_trans_type == 1 ? $this->info['job_id'] : 0);
        $opposite_job = ($job_trans_type == 0 || $job_trans_type == 2 ? $this->info['job_id'] : 0);
        foreach ($this->info['payment'] as $item) {
            $item['is_cheque'] = isset($item['is_cheque']) ? $item['is_cheque'] : 0;
            $item['user_ref'] = !empty($item['user_ref']) ? $item['user_ref'] : '';
            $item['Check_Date'] = !empty($item['Check_Date']) ? $item['Check_Date'] : null;
            $item['value_date'] = !empty($item['value_date']) ? $item['value_date'] : null;
            $item['commession_amount'] = isset($item['commession_amount']) ? $item['commession_amount'] : 0;
            $item['currency_id'] = !empty($item['currency_id']) ? $item['currency_id'] : $this->info['Currency_ID'];
//            $det_second_rate = CurrencyFactory::getSecondCurrencyLastExRate($item['currency_id']);
            $det_second_rate = !empty($item['second_currency_rate']) ? $item['second_currency_rate'] : ($item['currency_id'] == $this->info['Currency_ID'] && !empty($this->info['second_currency_rate']) ? $this->info['second_currency_rate'] : CurrencyFactory::getSecondCurrencyLastExRate($item['currency_id']));

            $item['ex_rate'] = !empty($item['ex_rate']) ? $item['ex_rate'] : CurrencyFactory::getLastExRate($item['currency_id']);
            $item['user_ref'] = !empty($item['user_ref']) ? $item['user_ref'] : '';
            $commission_amount = abs($item['commession_amount']);
            $row_is_collected = !empty($item['is_collected']) ? $item['is_collected'] : 0;
            $row_is_returned = !empty($item['is_returned']) ? $item['is_returned'] : 0;
            $data = [&$pay_id, &$item['Payment_Type_ID'],
                &$item['Payment_Amount'], &$item['currency_id'],
                &$item['Payment_Note'],
                &$item['Check_Number'],
                &$item['Check_Date'],
                &$item['value_date'], &$item['ex_rate'], &$det_second_rate,
                &$item['is_cheque'], &$item['commession_account'],
                &$item['commession_amount'], &$item['user_ref'], $row_is_collected, $row_is_returned, &$item['ex_rate2']
            ];
            //           echo"<pre>"; die(print_r($data));
            $this->db->prepare_Stmt('INSERT INTO `tbl_payment_receipt_details`( 
                ' . (!empty($item['detail_id']) ? 'payment_detail_id,' : '') . '
                `pay_rec_id`,
                `payment_account_id`,payment_amount, `currency_id`, `payment_note`, `cheque_nb`, `payment_due_date`, `value_date`,
                `current_rate`, `second_currency_rate`, `is_cheque`, `commission_acc_id`, `commission_amount`,user_ref,
                is_returned,is_collected,ex_rate2)
                values(' . (!empty($item['detail_id']) ? $item['detail_id'] . ',' : '') . '?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', $data);
            $sub_id = $this->db->id();


            $pay_acc = $item['Payment_Type_ID'];
            $desc = empty($item['Payment_Note']) ? (($this->info['IsReceipt'] ? ($this->info['is_return'] ? "Return Receipt" : "Receipt from ") : ($this->info['is_return'] ? "Return Payment" : "Payment to ")) . $this->info['Account_Name']) : $item['Payment_Note'];
            $ref = ($this->info['is_return'] ? 'Return ' : '') . ($this->info['IsReceipt'] ? 'Rec.' : 'Pay.') . $payment_prefix . $payment_id;
            if ($this->info['is_pending'] == 0) {

                $dat_2 = ["JV_ID" => $jv,
                    "Transaction_Reference" => $ref,
                    "Transaction_Account" => $this->info['Account_Code'],
                    "Transaction_Type" => ((($this->info['IsReceipt'] && !$this->info['is_return']) || (!$this->info['IsReceipt'] && $this->info['is_return'])) ? "Credit" : "Debit"),
                    "Transaction_Amount" => $item['Payment_Amount'],
                    "Transaction_Date" => $this->info['Payment_Date'],
                    "user_ref" => $item['user_ref'],
                    "Currency_ID" => $item['currency_id'],
                    "Logged_ID" => $logged_id,
                    "created_logged_id" => $created_logged_id,
                    "date_created" => $date_created,
                    "date_time" => $date_time,
                    "Transaction_Description" => $desc,
                    "Current_Ex_Rate" => $item['ex_rate'],
                    "second_currency_rate" => $det_second_rate,
                    "main_id" => $pay_id,
                    "sub_id" => $sub_id,
                    "main_type" => $main_type,
                    "branch_id" => $this->info['branch_id'],
                    "prefix" => $payment_prefix,
                    "main_nb" => $payment_id,
                    "due_date" => $item['Check_Date'],
                    "job_id" => $person_job,
                    "project_id" => $this->info['project_id'],
                    "pos_customer_id" => $this->info['pos_customer_id'],
                    "salesman_id" => $this->info['salesman_id']
                ];
                JV::addTransaction($dat_2, $flag);
                //            $error = $this->db->error();
                //            echo"<pre>";die(print_r($error));
                $dat_1 = ["JV_ID" => $jv,
                    "Transaction_Reference" => $ref,
                    "Transaction_Account" => $pay_acc,
                    "Transaction_Type" => ((($this->info['IsReceipt'] && !$this->info['is_return']) || (!$this->info['IsReceipt'] && $this->info['is_return'])) ? "Debit" : "Credit"),
                    "Transaction_Amount" => $item['Payment_Amount'],
                    "Transaction_Date" => $this->info['Payment_Date'],
                    "user_ref" => $item['user_ref'],
                    "Currency_ID" => $item['currency_id'],
                    "Logged_ID" => $logged_id,
                    "created_logged_id" => $created_logged_id,
                    "date_created" => $date_created,
                    "date_time" => $date_time,
                    "Transaction_Description" => $desc,
                    "Current_Ex_Rate" => $item['ex_rate'],
                    "second_currency_rate" => $det_second_rate,
                    "main_id" => $pay_id,
                    "sub_id" => $sub_id,
                    "main_type" => $main_type,
                    "branch_id" => $this->info['branch_id'],
                    "prefix" => $payment_prefix,
                    "main_nb" => $payment_id,
                    "due_date" => $item['Check_Date'],
                    "value_date" => $item['value_date'],
                    "check_nb" => $item['Check_Number'],
                    "job_id" => $opposite_job,
                    "project_id" => $this->info['project_id'],
                    "salesman_id" => $this->info['salesman_id']
                ];
                JV::addTransaction($dat_1, $flag);
                $dat_12 = ["JV_ID" => $jv,
                    "Transaction_Reference" => $ref,
                    "Transaction_Account" => $pay_acc,
                    "Transaction_Type" => ((((($this->info['IsReceipt'] && !$this->info['is_return']) || (!$this->info['IsReceipt'] && $this->info['is_return'])) && $item['commession_amount'] > 0) || (((!$this->info['IsReceipt'] && !$this->info['is_return']) || ($this->info['IsReceipt'] && $this->info['is_return'])) && $item['commession_amount'] < 0)) ? "Credit" : "Debit"),
                    "Transaction_Amount" => $commission_amount,
                    "Transaction_Date" => $this->info['Payment_Date'],
                    "user_ref" => $item['user_ref'],
                    "Currency_ID" => $item['currency_id'],
                    "Logged_ID" => $logged_id,
                    "created_logged_id" => $created_logged_id,
                    "date_created" => $date_created,
                    "date_time" => $date_time,
                    "Transaction_Description" => $desc,
                    "Current_Ex_Rate" => $item['ex_rate'],
                    "second_currency_rate" => $det_second_rate,
                    "main_id" => $pay_id,
                    "sub_id" => $sub_id,
                    "main_type" => $main_type,
                    "branch_id" => $this->info['branch_id'],
                    "prefix" => $payment_prefix,
                    "main_nb" => $payment_id,
                    "due_date" => $item['Check_Date'],
                    "value_date" => $item['value_date'],
                    "check_nb" => $item['Check_Number'],
                    "job_id" => $opposite_job,
                    "project_id" => $this->info['project_id'],
                    "salesman_id" => $this->info['salesman_id']
                ];
                JV::addTransaction($dat_12, $flag);
                $dat_13 = ["JV_ID" => $jv,
                    "Transaction_Reference" => $ref,
                    "Transaction_Account" => $item['commession_account'],
                    "Transaction_Type" => ((((($this->info['IsReceipt'] && !$this->info['is_return']) || (!$this->info['IsReceipt'] && $this->info['is_return'])) && $item['commession_amount'] > 0) || (((!$this->info['IsReceipt'] && !$this->info['is_return']) || ($this->info['IsReceipt'] && $this->info['is_return'])) && $item['commession_amount'] < 0)) ? "Debit" : "Credit"),
                    "Transaction_Amount" => $commission_amount,
                    "Transaction_Date" => $this->info['Payment_Date'],
                    "user_ref" => $item['user_ref'],
                    "Currency_ID" => $item['currency_id'],
                    "Logged_ID" => $logged_id,
                    "created_logged_id" => $created_logged_id,
                    "date_created" => $date_created,
                    "date_time" => $date_time,
                    "Transaction_Description" => $desc,
                    "Current_Ex_Rate" => $item['ex_rate'],
                    "second_currency_rate" => $det_second_rate,
                    "main_id" => $pay_id,
                    "sub_id" => $sub_id,
                    "main_type" => $main_type,
                    "branch_id" => $this->info['branch_id'],
                    "prefix" => $payment_prefix,
                    "main_nb" => $payment_id,
                    "due_date" => $item['Check_Date'],
                    "value_date" => $item['value_date'],
                    "check_nb" => $item['Check_Number'],
                    "job_id" => $opposite_job,
                    "project_id" => $this->info['project_id'],
                    "salesman_id" => $this->info['salesman_id']
                ];
                JV::addTransaction($dat_13, $flag);
            }
        }

//        $local_currency = CurrencyFactory::returnLocalCurrency();
//                    echo"<pre>";die(print_r($this->info['close_arr']));

        if (isset($this->info['close_arr'])) {
            $rec_type = $this->info['IsReceipt'] ? 'rec' : 'pay';
            $row_data = ['rate' => $rate];
            CommonBusiness::close_invoices($this->info['close_arr'], $pay_id, $rec_type, $row_data);
//            foreach ($this->info['close_arr'] as $close) {
//                $rec_type = $this->info['IsReceipt'] ? 'rec' : 'pay';
//                $this->db->prepare_Stmt('insert into  tbl_invoice_payments (Payment_Auto_ID,Invoice_ID_Auto,Payment_Amount,type)
//                values(?,?,?,?)', [&$pay_id, &$close['inv_id'], &$close['inv_amount'], $rec_type]);
//                $local_amount = $close['inv_amount'] * $rate;
//                $amount_inv = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($local_currency, $close['inv_currency'], $local_amount);
//                if ($this->info['IsReceipt']) {
//                    $this->db->prepare_Stmt('update tbl_invoices 
//                    set Payment_Paid_Amount = Payment_Paid_Amount + ?,is_main_transfer = 0 
//                        where Invoice_ID_Auto = ?', [&$amount_inv, &$close['inv_id']]);
//                } else {
//                    $this->db->prepare_Stmt('update tbl_inventory_purchases 
//                    set Payment_Paid_Amount = Payment_Paid_Amount + ?
//                        where Purchase_ID_Auto = ?', [&$amount_inv, &$close['inv_id']]);
//                }
//            }
        }



        $error = $this->db->error();
        if (empty($error)) {
            if (!$this->stop_transaction_start_end)
                $this->db->query('COMMIT');
        } else {
            if (!$this->stop_transaction_start_end)
                $this->db->query('ROLLBACK');
        }
        return $is_return ? [$payment_id, $payment_prefix, $pay_id] : array_unique($error);
    }

    //    public function editPayment($is_return = false) {
    //        $year = $this->info['Status_ID'] == 1 ? $this->year_id_actual : $this->year_id;
    //        $check = JV::checkCurrentYear($this->info['Payment_Date'], $this->info['Payment_Date'], $this->info['Status_ID']);
    //        if ($check !== '') {
    //            $error = array();
    //            $error[0] = $check;
    //            return $error;
    //        }
    //        $type = $this->info['IsReceipt'] ? "rec" : "pay";
    //        //        $table = $this->info['Status_ID'] ? 'tbl_mgt_logs' : 'tbl_accounting_transactions';
    //        $flag = $this->info['Status_ID'];
    //        $this->db->query('START TRANSACTION');
    //        $mydate = date("Y-m-d H:i:s");
    //        $date_time = isset($this->info['date_time']) ? $this->info['date_time'] : $mydate;
    //        $jv_query = $this->db->query("SELECT DISTINCT JV_ID FROM tbl_accounting_transactions  
    //            WHERE main_id='" . $this->info['pay_id'] . "' AND main_type= '" . $type . "' and prefix='" . $this->info['prefix'] . "'
    //                AND current_year_id='" . $year . "' and is_actual = '" . $this->info['Status_ID'] . "'");
    //        $jv_assoc = $this->db->assoc($jv_query);
    //        if (count($jv_assoc) > 0)
    //            $jv = $jv_assoc['JV_ID'];
    //        else
    //            $jv = JV::returnJV($this->info['Status_ID']);
    //        $this->db->prepare_Stmt("DELETE FROM tbl_accounting_transactions  
    //            WHERE main_id= ? AND main_type= '" . $type . "' and prefix='" . $this->info['prefix'] . "'
    //                AND current_year_id='" . $year . "' and is_actual = ?", [&$this->info['pay_id'], &$flag]);
    //        $payment_id = $this->info['pay_id'];
    //        $query_pay = $this->db->query("select Payment_Auto_ID,current_rate,Currency_ID,payment_prefix,
    //                                        payment_app_id,app_user_id,date_created,created_logged_id
    //                                        FROM tbl_payments_on_accounts  
    //                                        WHERE Payment_ID= '" . $this->info['pay_id'] . "' 
    //                                        AND IsReceipt= '" . $this->info['IsReceipt'] . "'
    //                                        AND Status_ID= '" . $this->info['Status_ID'] . "' 
    //                                            and payment_prefix = '" . $this->info['prefix'] . "'
    //                                        AND current_year_id='" . $year . "' ");
    //        $pay_ids = array();
    //        $old_rate = 0;
    //        $old_currency = 0;
    //        $payment_app_id = 0;
    //        $app_user_id = 0;
    //        $date_created = '';
    //        $created_logged_id = 0;
    //        $prefix = $this->info['prefix'];
    //        while ($row = $this->db->assoc($query_pay)) {
    //            $payment_app_id = $row['payment_app_id'];
    //            $app_user_id = $row['app_user_id'];
    //            $date_created = $row['date_created'];
    //            $created_logged_id = $row['created_logged_id'];
    //            $old_rate = $row['current_rate'];
    //            $old_currency = $row['Currency_ID'];
    //            $pay_ids[] = $row['Payment_Auto_ID'];
    //        }
    //        $pay_arr = implode(',', $pay_ids);
    //        if ($this->info['IsReceipt']) {
    //            $this->db->prepare_Stmt('update tbl_invoices join tbl_invoice_payments using(Invoice_ID_Auto)
    //                                SET Payment_Paid_Amount = Payment_Paid_Amount -(if(Invoice_Currency = "' . $old_currency . '",Payment_Amount,((Payment_Amount*"' . $old_rate . '")/current_rate)))
    //                                     ,is_main_transfer = 0
    //                                    where Payment_Auto_ID in (' . $pay_arr . ') ');
    //        }
    //        $this->db->prepare_Stmt("DELETE FROM tbl_payments_on_accounts  WHERE Payment_Auto_ID in (" . $pay_arr . ") ");
    //        if ($this->info['IsReceipt']) {
    //            $this->db->prepare_Stmt("DELETE FROM tbl_invoice_payments  WHERE Payment_Auto_ID in (" . $pay_arr . ") ");
    //        }
    //        $rate = !empty($this->info['Currency_Rate']) ? $this->info['Currency_Rate'] : CurrencyFactory::getLastExRate($this->info['Currency_ID']);
    //        $second_rate = CurrencyFactory::getSecondCurrencyLastExRate($this->info['Currency_ID']);
    //        $logged_id = isset($this->info['Logged_ID']) ? $this->info['Logged_ID'] : smc_session()['user_logged_id'];
    //        //        $rate = CurrencyFactory::getLastExRate($this->info['Currency_ID']);
    //        foreach ($this->info['payment'] as $item) {
    //            $item['user_ref'] = !empty($item['user_ref']) ? $item['user_ref'] : '';
    //            $item['Check_Date'] = !empty($item['Check_Date']) ? $item['Check_Date'] : null;
    //            $item['value_date'] = !empty($item['value_date']) ? $item['value_date'] : null;
    //            $data = [&$payment_id,
    //                &$prefix,
    //                &$this->info['Payment_Date'],
    //                &$this->info['Currency_ID'],
    //                &$this->info['Account_Code'],
    //                &$item['Payment_Type_ID'],
    //                &$item['Payment_Amount'],
    //                &$item['Payment_Note'],
    //                &$this->info['IsReceipt'],
    //                &$this->info['branch_id'],
    //                &$created_logged_id,
    //                &$logged_id,
    //                &$this->info['Status_ID'],
    //                &$item['Check_Number'],
    //                &$item['Check_Date'],
    //                &$item['value_date'],
    //                &$rate,
    //                &$second_rate,
    //                &$payment_app_id,
    //                &$app_user_id,
    //                &$year,
    //                &$date_time,
    //                &$date_created,
    //                &$this->info['job_id'],
    //                &$this->info['project_id'],
    //                &$item['commession_amount'],
    //                &$item['commession_account'],
    //                &$item['user_ref']
    //            ];
    //            $this->db->prepare_Stmt('
    //                                INSERT INTO
    //                                    tbl_payments_on_accounts
    //                                    (
    //                                    Payment_ID,
    //                                    payment_prefix,
    //                                    Payment_Date,
    //                                    Currency_ID,
    //                                    Account_ID,
    //                                    Payment_Type_ID,
    //                                    Payment_Amount,
    //                                    Payment_Note,
    //                                    IsReceipt,
    //                                    branch_id,
    //                                    created_logged_id,
    //                                    Logged_ID,
    //                                    Status_ID,
    //                                    Cheque_Number,
    //                                    Payment_Due_Date,value_date,
    //                                    current_rate,second_currency_rate,
    //                                    payment_app_id,
    //                                    app_user_id,
    //                                    current_year_id,date_time,date_created,job_id,project_id,commession_amount,commession_acc_id,user_ref
    //                                    )VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', $data);
    //            $pay_id = $this->db->id();
    //            $arr_log = ['type' => ($this->info['IsReceipt'] ? 'rec' : 'pay'), 'main_id' => $pay_id,
    //                'action' => 1, 'date_time' => $mydate,
    //                'user_id' => smc_session()['user_logged_id']];
    //            CommonFunctions::add_user_log($arr_log);
    //
    //            //            $account = $this->db->query('SELECT Account_ID FROM tbl_payment_types WHERE Payment_Type_ID = "' . $item['Payment_Type_ID'] . '"');
    //            //            $account_code = $this->db->assoc($account)['Account_ID'];
    //            $account_code = $item['Payment_Type_ID'];
    //            $account_nameq = $this->db->query('SELECT Account_Name FROM tbl_chart_of_accounts WHERE Account_ID ="' . $this->info['Account_Code'] . '"');
    //            $acc_name = $this->db->assoc($account_nameq)['Account_Name'];
    //            $type = $this->info['IsReceipt'] ? "rec" : "pay";
    //            $ref = ($this->info['IsReceipt'] ? 'Rec.' : 'Pay.') . $this->info['prefix'] . $payment_id;
    //
    //            $desc = empty($item['Payment_Note']) ? (($this->info['IsReceipt'] ? 'Receipt from ' : 'Payment to ') . $acc_name) : $item['Payment_Note'];
    //
    //            //            echo"<pre>";die(print_r($dat_1));
    //            //             $error = $this->db->error();
    //            //            die(print_r($error));
    //            $dat_2 = ["JV_ID" => $jv,
    //                "Transaction_Reference" => $ref,
    //                "Transaction_Account" => $this->info['Account_Code'],
    //                "Transaction_Type" => ($this->info['IsReceipt'] ? "Credit" : "Debit"),
    //                "Transaction_Amount" => $item['Payment_Amount'],
    //                "Transaction_Date" => $this->info['Payment_Date'],
    //                "user_ref" => $item['user_ref'],
    //                "Currency_ID" => $this->info['Currency_ID'],
    //                "Logged_ID" => $logged_id,
    //                "created_logged_id" => $created_logged_id,
    //                "date_created" => $date_created,
    //                "date_time" => $date_time,
    //                "Transaction_Description" => $desc,
    //                "Current_Ex_Rate" => $rate,
    //                "second_currency_rate" => $second_rate,
    //                "main_id" => $payment_id,
    //                "main_type" => $type,
    //                "branch_id" => $this->info['branch_id'],
    //                "prefix" => $prefix,
    //                "main_nb" => $payment_id,
    //                "job_id" => $this->info['job_id'],
    //                "project_id" => $this->info['project_id']
    //            ];
    //            JV::addTransaction($dat_2, $flag);
    //            $dat_1 = ["JV_ID" => $jv,
    //                "Transaction_Reference" => $ref,
    //                "Transaction_Account" => $account_code,
    //                "Transaction_Type" => ($this->info['IsReceipt'] ? "Debit" : "Credit"),
    //                "Transaction_Amount" => $item['Payment_Amount'],
    //                "Transaction_Date" => $this->info['Payment_Date'],
    //                "user_ref" => $item['user_ref'],
    //                "Currency_ID" => $this->info['Currency_ID'],
    //                "Logged_ID" => $logged_id,
    //                "created_logged_id" => $created_logged_id,
    //                "date_created" => $date_created,
    //                "date_time" => $date_time,
    //                "Transaction_Description" => $desc,
    //                "Current_Ex_Rate" => $rate,
    //                "second_currency_rate" => $second_rate,
    //                "main_id" => $payment_id,
    //                "main_type" => $type,
    //                "branch_id" => $this->info['branch_id'],
    //                "prefix" => $prefix,
    //                "main_nb" => $payment_id,
    //                "due_date" => $item['Check_Date'],
    //                "value_date" => $item['value_date'],
    //                "check_nb" => $item['Check_Number'],
    //                "job_id" => $this->info['job_id'],
    //                "project_id" => $this->info['project_id']
    //            ];
    //            JV::addTransaction($dat_1, $flag);
    //            $dat_12 = ["JV_ID" => $jv,
    //                "Transaction_Reference" => $ref,
    //                "Transaction_Account" => $account_code,
    //                "Transaction_Type" => ($this->info['IsReceipt'] ? "Credit" : "Debit"),
    //                "Transaction_Amount" => $item['commession_amount'],
    //                "Transaction_Date" => $this->info['Payment_Date'],
    //                "user_ref" => $item['user_ref'],
    //                "Currency_ID" => $this->info['Currency_ID'],
    //                "Logged_ID" => $logged_id,
    //                "created_logged_id" => $created_logged_id,
    //                "date_created" => $date_created,
    //                "date_time" => $date_time,
    //                "Transaction_Description" => $desc,
    //                "Current_Ex_Rate" => $rate,
    //                "second_currency_rate" => $second_rate,
    //                "main_id" => $payment_id,
    //                "main_type" => ($this->info['IsReceipt'] ? "rec" : "pay" ),
    //                "branch_id" => $this->info['branch_id'],
    //                "prefix" => $prefix,
    //                "main_nb" => $payment_id,
    //                "due_date" => $item['Check_Date'],
    //                "value_date" => $item['value_date'],
    //                "check_nb" => $item['Check_Number'],
    //                "job_id" => $this->info['job_id'],
    //                "project_id" => $this->info['project_id']
    //            ];
    //            JV::addTransaction($dat_12, $flag);
    //            $dat_13 = ["JV_ID" => $jv,
    //                "Transaction_Reference" => $ref,
    //                "Transaction_Account" => $item['commession_account'],
    //                "Transaction_Type" => ($this->info['IsReceipt'] ? "Debit" : "Credit"),
    //                "Transaction_Amount" => $item['commession_amount'],
    //                "Transaction_Date" => $this->info['Payment_Date'],
    //                "user_ref" => $item['user_ref'],
    //                "Currency_ID" => $this->info['Currency_ID'],
    //                "Logged_ID" => $logged_id,
    //                "created_logged_id" => $created_logged_id,
    //                "date_created" => $date_created,
    //                "date_time" => $date_time,
    //                "Transaction_Description" => $desc,
    //                "Current_Ex_Rate" => $rate,
    //                "second_currency_rate" => $second_rate,
    //                "main_id" => $payment_id,
    //                "main_type" => ($this->info['IsReceipt'] ? "rec" : "pay" ),
    //                "branch_id" => $this->info['branch_id'],
    //                "prefix" => $prefix,
    //                "main_nb" => $payment_id,
    //                "due_date" => $item['Check_Date'],
    //                "value_date" => $item['value_date'],
    //                "check_nb" => $item['Check_Number'],
    //                "job_id" => $this->info['job_id'],
    //                "project_id" => $this->info['project_id']
    //            ];
    //            JV::addTransaction($dat_13, $flag);
    //        }
    //        $local_currency = CurrencyFactory::returnLocalCurrency();
    //        if ($this->info['IsReceipt'] && isset($this->info['close_arr'])) {
    //            foreach ($this->info['close_arr'] as $close) {
    //                $this->db->prepare_Stmt('insert into  tbl_invoice_payments (Payment_Auto_ID,Invoice_ID_Auto,Payment_Amount)
    //                values(?,?,?)', [&$pay_id, &$close['inv_id'], &$close['inv_amount']]);
    //                $local_amount = $close['inv_amount'] * $rate;
    //                $amount_inv = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($local_currency, $close['inv_currency'], $local_amount);
    //                $this->db->prepare_Stmt('update tbl_invoices 
    //                    set Payment_Paid_Amount = Payment_Paid_Amount + ?,is_main_transfer = 0
    //                    where Invoice_ID_Auto = ?', [&$amount_inv, &$close['inv_id']]);
    //            }
    //        }
    //
    //        $error = $this->db->error();
    //        if (empty($error)) {
    //            $this->db->query('COMMIT');
    //        } else {
    //            $this->db->query('ROLLBACK');
    //        }
    //        return $is_return ? [$payment_id, $prefix] : array_unique($error);
    //    }
    //    public function view($is_receipt, $pay_id = null, $acc_id = null, $date = null, $month = null, $year = null, $from = null, $to = null, $actual = null, $prefix = '') {
    //        //$is_receipt 1 -> receipt
    //        //            0 -> payment
    //        if ($actual == 1) {
    //            $year_id = $this->year_id_actual;
    //        } else {
    //            $year_id = $this->year_id;
    //        }
    //        $data = array();
    //        $condition = '';
    //        if (!is_null($pay_id))
    //            $condition .= ' AND Payment_ID = "' . $pay_id . '"';
    //        if (!is_null($acc_id))
    //            $condition .= ' AND Account_ID = "' . $acc_id . '"';
    //        if (!is_null($date))
    //            $condition .= ' AND Payment_Date = "' . $date . '"';
    //        if (!is_null($month))
    //            $condition .= ' AND MONTH(Payment_Date) = "' . $month . '"';
    //        if (!is_null($year))
    //            $condition .= ' AND YEAR(Payment_Date) = "' . $year . '"';
    //        if (!is_null($from) and ! is_null($to))
    //            $condition .= ' AND Payment_Date >= "' . $from . '" AND Payment_Date <= "' . $to . '"';
    //        if (!is_null($actual))
    //            $condition .= ' AND Status_ID= "' . $actual . '"';
    //        //        if($prefix !=''){
    //        $condition .= ' and payment_prefix = "' . $prefix . '"';
    //        //        }
    //        $query = $this->db->query('SELECT * from payment_list where
    //                                           IsReceipt = "' . $is_receipt . '" and current_year_id = "' . $year_id . '"' . $condition);
    //        while ($row = $this->db->assoc($query)) {
    //            $data[] = $row;
    //        }
    //        return $data;
    //    }
    //    public function viewAccountId() {
    //        $data = array();
    //        $query = $this->db->query('SELECT * FROM tbl_payments_on_accounts');
    //        while ($row = $this->db->assoc($query)) {
    //            $data[] = $row;
    //        }
    //        return $data;
    //    }

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

    public function viewPaymentTypes() {
        $data = array();
        $query = $this->db->query('select Account_ID, Account_Code, Account_Name,Currency_ID
                                    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 viewNeededAccounts() {
    //        $data = array();
    //        $query = $this->db->query('SELECT * FROM tbl_chart_of_accounts
    //            WHERE LEFT(Account_Code,1)=4 OR LEFT(Account_Code,1)=6 OR LEFT(Account_Code,2)=73 
    //            AND Account_Status = 1');
    //        while ($row = $this->db->assoc($query)) {
    //            $data[] = $row;
    //        }
    //        return $data;
    //    }
    //    public function Edit($id) {
    //        $data = [&$this->info['Account_Code'], &$this->info['Payment_Amount'], &$this->info['Payment_Type_ID'], &$this->info['Payment_Note'], &$id];
    //        $query = $this->db->query('
    //                      UPDATE
    //                          tbl_payments_on_accounts
    //                    SET
    //                        Account_Code    = ?,
    //                        Payment_Amount  = ?,
    //                        Payment_Type_ID = ?,
    //                        Payment_Note    = ?
    //                    WHERE
    //                        Payment_ID      = ?', $data);
    //        $error = $this->db->error();
    //        return $error;
    //    }

    public function getPaymentID() {
        $query = $this->db->query('SELECT pay_rec_id
           FROM tbl_payment_receipt where is_return=0 ORDER BY pay_rec_id DESC LIMIT 1');
        $row = $this->db->assoc($query);
        return $row;
    }

    public function getmaxPaymentID($is_receipt, $actual, $prefix, $is_pending = 0, $is_return = 0) {
        //smc_session()['user_accounts']['branch_id'] !='' ? smc_session()['user_accounts']['branch_id'] : smc_session()['default_branch_id']
        $year = $actual == 1 ? $this->year_id_actual : $this->year_id;
        $query = $this->db->query('SELECT MAX(pay_rec_nb) AS Max_Id FROM tbl_payment_receipt 
                where current_year_id = "' . $year . '" 
                and is_actual = "' . $actual . '" 
                and is_receipt = "' . $is_receipt . '" 
                and prefix = "' . $prefix . '"
                and is_pending = "' . $is_pending . '"
                and is_return = "' . $is_return . '"
                    ');
        $row = $this->db->assoc($query);
        return $row['Max_Id'] + 1;
    }

    public function viewPaymentsByID($id = null, $detail_id = null, $is_receipt = null) {
        $data = array();
        $cond = '';
        if (!empty($detail_id) && empty($id)) {
            $query = $this->db->query('select pay_rec_id 
                from tbl_payment_receipt_details where payment_detail_id = "' . $detail_id . '"');
            $id = $this->db->assoc($query)['pay_rec_id'];
            $cond .= ' and d.payment_detail_id = "' . $detail_id . '" ';
        }
        $query = $this->db->query('SELECT p.*,c.Account_Code as Current_Account_Code,
            c.Account_Name,cu.*,b.*,j.job_name,pr.project_name,
            concat_ws(" ",e.Employee_FirstName,e.Employee_MiddleName,e.Employee_LastName) as salesman_name,
            ifnull(pc.customer_name,"") as pos_customer_name
            FROM tbl_payment_receipt p
            join  tbl_common_currency cu on p.Currency_ID =  cu.Currency_ID
            join tbl_chart_of_accounts c on p.Account_ID = c.Account_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
            left join tbl_hrm_employee e on p.salesman_id = e.Employee_ID
            left join tbl_pos_customers pc on pc.customer_id = p.pos_customer_id
            where p.pay_rec_id = "' . smc_escape($id) . '" 
                ' . (is_null($is_receipt) ? '' : " and p.is_receipt = '" . ($is_receipt ? '1' : '0') . "' ") . ' ');
        $row = $this->db->assoc($query);
        if (empty($row))
            return $data;
        $data['main'] = $row;
        //
        $query_detail = $this->db->query('select d.*,cu.Currency_Symbol,cu.Currency_Name,
            c.Account_Code as payment_type_code,c.Account_Name as payment_type_name,
            m.Account_Code as commission_acc_code,m.Account_Name as commission_acc_name
            from tbl_payment_receipt_details d 
            join tbl_common_currency cu on cu.Currency_ID = d.currency_id
            join tbl_chart_of_accounts c on c.Account_ID = d.payment_account_id
            left join tbl_chart_of_accounts m on m.Account_ID = d.commission_acc_id
            where d.pay_rec_id = "' . smc_escape($id) . '"
            ' . $cond);
        while ($row = $this->db->assoc($query_detail)) {
            $data['payments'][] = $row;
        }
        //
        if ($is_receipt) {
            $data['pay_inv'] = Invoice::getUnpaidInvoicesByCustomer($data['main']['Account_ID'], $data['main']['Currency_ID'], $id, $data['main']['is_actual'], $data['main']['pos_customer_id'], 'rec');
        } else {
            $data['pay_inv'] = Purchase::getUnpaidInvoicesBySupplier($data['main']['Account_ID'], $data['main']['Currency_ID'], $id, $data['main']['is_actual'], 'pay');
        }
        //
        return $data;
    }

    //check_here
    //    public function viewPaymentsByID($id, $status, $is_receipt, $prefix = '') {
    //        $data = array();
    //        $year = $status == 1 ? $this->year_id_actual : $this->year_id;
    //        //        $currency = smc_session()[['local_curre']];
    //        $account = 0;
    //        $pay_ids = array();
    //        $query = $this->db->query('SELECT p.*,c.Account_Code as Current_Account_Code,
    //        c.Account_Name,cu.*,b.*,j.job_name,pr.project_name,concat(coa.Account_Name," - ",coa.Account_Code) as commession_acc_name
    //                                    FROM tbl_payments_on_accounts p
    //                                     join  tbl_common_currency cu
    //                                    on p.Currency_ID =  cu.Currency_ID
    //                                     join tbl_chart_of_accounts c
    //                                    on p.Account_ID = c.Account_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
    //                                    left join tbl_chart_of_accounts coa on coa.Account_ID = p.commession_acc_id
    //                                    where p.Payment_ID = "' . smc_escape($id) . '" 
    //                                    and current_year_id = "' . $year . '"
    //                                    and p.Status_ID = "' . $status . '"
    //                                    and IsReceipt = "' . $is_receipt . '"
    //                                    and payment_prefix = "' . $prefix . '"');
    //        while ($row = $this->db->assoc($query)) {
    //            $pay_ids[] = $row['Payment_Auto_ID'];
    //            $currency = $row['Currency_ID'];
    //            $account = $row['Account_ID'];
    //            $data['payments'][] = $row;
    //        }
    //        if (!empty($pay_ids)) {
    //            $pay_arr = implode(',', $pay_ids);
    //            $data['pay_inv'] = Invoice::getUnpaidInvoicesByCustomer($account, $currency, $pay_arr, $status);
    //        }
    //        return $data;
    //    }
    //    public function viewPaymentByAutoID($id) {
    //        $data = array();
    //        $query = $this->db->query('SELECT * from payment_list where Payment_Auto_ID = "' . $id . '" ');
    //        $data[] = $this->db->assoc($query);
    //        return $data;
    //    }

    public function getPaymentTypesByType($type) {
        $data = array();
        $cond = '';
        $sub_type = ($type == "credit_card" ? "credit" : $type);
        $cond .= 'AND main_type="pay" and concat(",",sub_type,",") like "%,' . $sub_type . ',%" ';
        //        if ($type == "cash")
        //            $cond .= 'AND main_type="pay" and  concat(",",sub_type,",") like "%,cash,%"  = 53';
        //        elseif ($type == "cheque" || $type == "credit_card")
        //            $cond .= 'AND LEFT(Account_Code,2) = 51';

        $query = $this->db->query('SELECT Account_ID as Account_ID,
                                          concat(Account_Name," - ",Account_Code) as Account_Name
                                    FROM 
                                        tbl_chart_of_accounts
                                        WHERE Status_ID = 1
                                    ' . $cond);

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

    public function getPaymentToPrint($id) {

        $query = $this->db->query('SELECT * from payment_list where
                                            Payment_Auto_ID = "' . $id . '"');
        $row = $this->db->assoc($query);
        //        echo"<pre>";die(print_r($row));
        $row['current_time'] = date("Y-m-d h:i a");
        $row['current_time_second'] = date("Y-m-d h:i:s a");
        $row['total_word_ar'] = Forms::num_to_word($row['Payment_Amount'], $row['Currency_ID'], 'ar');
        $row['total_word_en'] = Forms::num_to_word($row['Payment_Amount'], $row['Currency_ID'], 'en');
        $row['note_class'] = empty($row['Payment_Note']) ? 'hidden' : '';
        $row['person_name'] = !empty($row['pos_customer_name']) ? $row['pos_customer_name'] : $row['Account_Name'];
        $row['pos_customer_class'] = !empty($row['pos_customer_id']) ? '' : 'hidden';
        $row['title'] = empty($row['IsReceipt']) ? CommonLanguage::getMessage('payments') : CommonLanguage::getMessage('receipt');
        if ($row['Account_ID'] == 0)
            $row['current_balance'] = $row['previous_balance'] = '---';
        else {
            $report = new Report;
            //we used 2000-01-01 00:00:00 as from date for the case that transaction is entered in date of next current year with date created this year
            $options = ["Account_ID" => $row['Account_ID'], "Currency_ID" => $row['Currency_ID'], "From_Date" => '2000-01-01 00:00:00', "To_Date" => $row["date_created"],
                "Opening_Balance" => 0, "Is_Actual" => $row['Status_ID'], "Actual_Only" => false, "opening_only" => false];
            $temp_current_balance = $report->returnExactBalanceByID($options);
            $row['current_balance'] = CommonFunctions::formatNumber($temp_current_balance['Balance']);
            $amount = $row['IsReceipt'] == 1 ? $row['Payment_Amount'] : ($row['Payment_Amount'] * -1);
            $row['previous_balance'] = CommonFunctions::formatNumber(($temp_current_balance['Balance'] + ($amount)));
        }
        //        die(print_r($row));
        return $row;
    }

    public function payment_template_arr($pay_id = null) {
        //    echo $pay_id.'===';
        $report = new Report;

        $data = array('details' => array(), 'main' => array());
        $main = array();
        $condition = '';
        if (!is_null($pay_id))
            $condition .= ' AND Payment_Auto_ID = "' . $pay_id . '"';

        $query = $this->db->query('SELECT * from payment_list where 1=1 ' . $condition);
        $acc_id = '';
        $total = 0;
        if ($this->db->rowsno($query) == 0)
            return $data;
        while ($row = $this->db->assoc($query)) {
            //for detail rows 
            $main['IsReceipt'] = $row['IsReceipt'];
            $main['Account_Name'] = $row['Account_Name'] . ' - ' . $row['Account_Code'];
            $main['is_actual'] = $row['Status_ID'];
            $main['Account_Code'] = $row['Account_Code'];
            $main['Payment_Date'] = $row['Payment_Date'];
            $main['Payment_ID'] = $row['ID'];
            $main['Currency_ID'] = $row['Currency_ID'];
            $main['Currency_Symbol'] = $row['Currency_Symbol'];
            $main['date_created'] = $row['date_created'];
            $main['Payment_Type_Name'] = $row['Payment_Type_Name'];
            $main['Cheque_Number'] = $row['Cheque_Number'];
            $main['total_amount'] = $row['total_amount'];
            $main['main_Currency_ID'] = $row['main_Currency_ID'];
            $main['main_Currency_Symbol'] = $row['main_Currency_Symbol'];
            $main['main_Currency_Name'] = $row['main_Currency_Name'];
            $acc_id = $row['Account_ID'];
            //$total += $row['Payment_Amount'];
            $row['Payment_Amount'] = CommonFunctions::formatNumber($row['Payment_Amount']);
            $row['row_total_word_ar'] = Forms::num_to_word($row['Payment_Amount'], $main['Currency_ID'], 'ar');
            $row['row_total_word_en'] = Forms::num_to_word($row['Payment_Amount'], $main['Currency_ID'], 'en');
            $row['bank_name'] = (empty($main['Cheque_Number']) ? '' : $main['Payment_Type_Name']);
            $row['cash_flag'] = empty($main['Cheque_Number']) ? '' : 'hidden';
            $row['check_flag'] = !empty($main['Cheque_Number']) ? '' : 'hidden';
            $data['details'][] = $row;
        }
        //for main row [totals]
        $main['bank_name'] = (empty($main['Cheque_Number']) ? '' : $main['Payment_Type_Name']);
        $main['cash_flag'] = empty($main['Cheque_Number']) ? '' : 'hidden';
        $main['check_flag'] = !empty($main['Cheque_Number']) ? '' : 'hidden';
        $main['grand_total_word_ar'] = Forms::num_to_word($main['total_amount'], $main['main_Currency_ID'], 'ar');
        $main['grand_total_word_en'] = Forms::num_to_word($main['total_amount'], $main['main_Currency_ID'], 'en');
        $main['total_payment'] = CommonFunctions::formatNumber($main['total_amount']/* $total */);
//        $options = array("Account_ID" => $acc_id, "Currency_ID" => $main["Currency_ID"],
//            "from_create_time" => (smc_session()['start_date'] . ' 00:00:00'), "to_create_time" => $main["date_created"], "Is_Actual" => $main['is_actual']);
        //we used 2000-01-01 00:00:00 as from date for the case that transaction is entered in date of next current year with date created this year
        $options = ["Account_ID" => $acc_id, "Currency_ID" => $main["main_Currency_ID"], "From_Date" => '2000-01-01 00:00:00', "To_Date" => $row["date_created"],
            "Opening_Balance" => 0, "Is_Actual" => $main['is_actual'], "Actual_Only" => false, "opening_only" => false];
        $temp_current_balance = $report->returnExactBalanceByID($options);
        $main['current_balance'] = CommonFunctions::formatNumber($temp_current_balance['Balance']);
        $amount = $main['total_amount'] * ($main['IsReceipt'] == 1 ? 1 : -1);
        $main['previous_balance'] = CommonFunctions::formatNumber(($temp_current_balance['Balance'] + ($amount)));
        //
        $person = Supplier::ViewAllDetailsCustomerVendor($acc_id)[0];
        $person['email_class'] = empty($person['Email']) ? 'hidden' : '';
        $person['phone_class'] = empty($person['Phone']) ? 'hidden' : '';
        $person['reg_nb_class'] = empty($person['reg_nb']) ? 'hidden' : '';
        $data['main'] = array_merge($person, $main);
        //
        return $data;
    }

    //    public function payment_template_arr($is_receipt, $pay_id = null, $actual = null, $prefix = '') {
    //        $report = new Report;
    //        if ($actual == 1) {
    //            $year_id = $this->year_id_actual;
    //        } else {
    //            $year_id = $this->year_id;
    //        }
    //        $data = array('details' => array(), 'main' => array());
    //        $main = array();
    //        $condition = '';
    //        if (!is_null($pay_id))
    //            $condition .= ' AND Payment_ID = "' . $pay_id . '"';
    //
    //        if (!is_null($actual))
    //            $condition .= ' AND Status_ID= "' . $actual . '"';
    //        $condition .= ' and payment_prefix = "' . $prefix . '"';
    //        $query = $this->db->query('SELECT * from payment_list where
    //                                           IsReceipt = "' . $is_receipt . '" and current_year_id = "' . $year_id . '"' . $condition);
    //        $acc_id = '';
    //        $total = 0;
    //        if ($this->db->rowsno($query) == 0)
    //            return $data;
    //        while ($row = $this->db->assoc($query)) {
    //            $main['Payment_Date'] = $row['Payment_Date'];
    //            $main['Currency_ID'] = $row['Currency_ID'];
    //            $main['Currency_Symbol'] = $row['Currency_Symbol'];
    //            $main['date_created'] = $row['date_created'];
    //            $main['Payment_Type_Name'] = $row['Payment_Type_Name'];
    //            $main['Cheque_Number'] = $row['Cheque_Number'];
    //            $acc_id = $row['Account_ID'];
    //            $total += $row['Payment_Amount'];
    //            $row['Payment_Amount'] = CommonFunctions::formatNumber($row['Payment_Amount']);
    //            $data['details'][] = $row;
    //        }
    //
    //        $main['Payment_ID'] = $pay_id;
    //
    //        $main['bank_name'] = (empty($main['Cheque_Number']) ? '' : $main['Payment_Type_Name']);
    //        $main['cash_flag'] = empty($main['Cheque_Number']) ? '' : 'hidden';
    //        $main['check_flag'] = !empty($main['Cheque_Number']) ? '' : 'hidden';
    //
    //
    //
    //        $main['total_payment'] = CommonFunctions::formatNumber($total);
    //        $main['grand_total_word_ar'] = Forms::num_to_word($main['total_payment'], $main['Currency_ID'], 'ar');
    //        $main['grand_total_word_en'] = Forms::num_to_word($main['total_payment'], $main['Currency_ID'], 'en');
    //
    //        $options = array("Account_ID" => $acc_id, "Currency_ID" => $main["Currency_ID"],
    //            "from_create_time" => (smc_session()['start_date'] . ' 00:00:00'), "to_create_time" => $main["date_created"], "Is_Actual" => $actual);
    //        $current_balance = !$is_receipt ? 0 : $report->returnExactBalanceByID($options)['Balance'];
    //        $main['current_balance'] = number_Xformat($current_balance, 2);
    //
    //        $person = Supplier::ViewAllDetailsCustomerVendor($acc_id)[0];
    //        $person['email_class'] = empty($person['Email']) ? 'hidden' : '';
    //        $person['phone_class'] = empty($person['Phone']) ? 'hidden' : '';
    //        $person['reg_nb_class'] = empty($person['reg_nb']) ? 'hidden' : '';
    //        $data['main'] = array_merge($person, $main);
    //        echo"<pre>";die(print_r($data));
    //        return $data;
    //    }

    public function delete_payment_receipt($ids, $is_receipt, $is_return = 0,$from_pos=0) {
        $year_id = $this->year_id;
        if (smc_session()['year_closed'] == 1) {
            $error = array();
            $error[0] = CommonLanguage::getMessage('delete_year_alert');
            return $error;
        }
 
        if($from_pos==1){
            $id_pos = $ids;
            $ids = array();
            array_push($ids,$id_pos);
        }

        $mydate = date("Y-m-d H:i:s");
        $error_arr = array();
        if (!$this->stop_transaction_start_end)
            $this->db->query('START TRANSACTION');
        $type = $is_receipt ? ($is_return ? 'retr' : 'rec') : ($is_return ? 'retp' : 'pay');
        foreach ($ids as $id) {
            $query_det = $this->db->query('select returned_detail_id,current_rate,Currency_ID,payment_date,is_actual 
                from tbl_payment_receipt where pay_rec_id = "' . $id . '"');
            //
            $row_det = $this->db->assoc($query_det);
            $detail_id = $row_det['returned_detail_id'];
            $check = CommonFunctions::check_lock_data(['type' => 'payment', 'start_date' => $row_det['payment_date'], 'is_actual' => $row_det['is_actual']]);
            if ($check !== '') {
                continue;
            }
            $this->db->prepare_Stmt("delete from tbl_accounting_transactions  
            where main_id= ? and main_type= ? 
            ", [&$id, &$type]);
            //
            //
           
            $this->db->prepare_Stmt('update tbl_payment_receipt_details 
                set is_returned = 0 where payment_detail_id=?', [$detail_id]);
            //
            $query_det = $this->db->query('select receipt_detail_id 
                from tbl_payment_receipt where pay_rec_id = "' . $id . '"');
            //
            $detail_id = $this->db->assoc($query_det)['receipt_detail_id'];
            $this->db->prepare_Stmt('update tbl_payment_receipt_details 
                set is_collected = 0 where payment_detail_id=?', [$detail_id]);
            //
            $record_type = $is_receipt ? 'rec' : 'pay';
            CommonBusiness::return_inv_pur_close_amount($record_type, $row_det['Currency_ID'], $row_det['current_rate'], $id);
            //
            $this->db->prepare_Stmt("delete from tbl_payment_receipt  
            where pay_rec_id = ? ", [&$id]);
            //

            $arr_log = ['type' => $type, '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)) {
            if (!$this->stop_transaction_start_end)
                $this->db->query('COMMIT');
        } else {
            if (!$this->stop_transaction_start_end)
                $this->db->query('ROLLBACK');
        }

        return $error;
    }

    //    public function reorderPayRecNum($from_date, $is_actual, $is_receipt) {
    //        $year_id = $is_actual ? $this->year_id_actual : $this->year_id;
    //        $this->db->query("START TRANSACTION");
    //        $query_prefix = $this->db->query('select distinct payment_prefix from tbl_payments_on_accounts');
    //        while ($row_prefix = $this->db->assoc($query_prefix)) {
    //            $pay_num = 1;
    //            //
    //            $query = $this->db->query("SELECT *
    //            FROM tbl_payments_on_accounts WHERE payment_prefix = '" . $row_prefix['payment_prefix'] . "'
    //            ORDER BY Payment_Date, Payment_Auto_ID ");
    //            while ($row = $this->db->assoc($query)) {
    //                $trans_data = [&$pay_num, &$pay_num, &$pay_num, &$row_prefix['payment_prefix'], &$row['Payment_Auto_ID']];
    //                $this->db->prepare_Stmt('update tbl_accounting_transactions t
    //                    join tbl_payments_on_accounts p on p.Payment_Date = t.Transaction_Date 
    //                    and p.payment_prefix = t.prefix
    //                    and p.IsReceipt = 1 and t.main_type = "rec" 
    //                    and p.Payment_Amount = t.Transaction_Amount set t.main_nb = ?, p.Payment_ID = ?,t.main_id = ?
    //                    where   t.prefix = ? and p.Payment_Auto_ID = ?', $trans_data);
    //                echo $pay_num . '<br>';
    //                $pay_num++;
    //                //                $old = $row['Invoice_ID'];
    //            }
    //        }
    //        $error = $this->db->error();
    //        if (empty($error)) {
    //            $this->db->query('COMMIT');
    //        } else {
    //            $this->db->query('ROLLBACK');
    //        }
    //        return array_unique($error);
    //    }

    public function submitScheduleBills() {
        if (!$this->stop_transaction_start_end)
            $this->db->query("START TRANSACTION");
        $is_actual = $this->info['is_actual'];
        $check = CommonFunctions::check_lock_data(['type' => 'schedule_bill', 'start_date' => $this->info['bill_date'], 'is_actual' => $is_actual]);

//        $check = JV::checkCurrentYear($this->info['bill_date'], $this->info['bill_date'], $is_actual);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $is_generate_trans = smc_session()['default_setting']['schedule_bill_generate_trans'];
        $rate = CurrencyFactory::getLastExRate($this->info['currency_id']);
        $second_rate = CurrencyFactory::getSecondCurrencyLastExRate($this->info['currency_id']);
        $customer_account = Customer::getCustomerAccount($this->info['customer_id'])['Account_ID'];
        if ($is_generate_trans)  //to don't create new account 
            $customer_bill_acc = Customer::get_customer_bill_acc($this->info['customer_id']);
        $total_without_first = $this->info['main_total'] - $this->info['first_bill'];
        $this->info['account_id'] = !empty($this->info['account_id']) ? $this->info['account_id'] : 0;

        if ($this->info['bill_id'] == 0) {

            $bill_nb = self::getMaxScheduleBillNumber($is_actual);
            $this->db->prepare_Stmt('insert into tbl_accounting_schedule_bills
                                 (bill_nb,customer_id,account_id,pay_for,main_total,nb_of_bills,first_bill,bill_amount,
                                 last_bill,currency_id,payment_period,bill_date,start_date,bill_description,date_created,
                                 date_time,created_logged_id,logged_id,current_year_id,is_actual)
                                 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
        ', [&$bill_nb, &$this->info['customer_id'], &$this->info['account_id'],
                &$this->info['pay_for'], &$this->info['main_total'], &$this->info['nb_of_bills'],
                &$this->info['first_bill'], &$this->info['bill_amount'], &$this->info['last_bill'],
                &$this->info['currency_id'], &$this->info['payment_period'], &$this->info['bill_date'],
                &$this->info['start_date'], &$this->info['bill_description'], &$this->current_date_time,
                &$this->current_date_time, &$this->logged_id, &$this->logged_id, &$this->year_id,
                &$is_actual]);
            $bill_id = $this->db->id();
            $this->info['bill_id'] = $bill_id;
            if ($is_generate_trans) {
                $jv = JV::returnJV($is_actual);
                $ref = "Schedule Bills.{$bill_id}";
                if (!empty($this->info['account_id'])) {
                    $bill_main_trans = ["JV_ID" => $jv,
                        "Transaction_Reference" => $ref,
                        "Transaction_Account" => $this->info['account_id'],
                        "Transaction_Type" => "Credit",
                        "Transaction_Amount" => $this->info['main_total'],
                        "Transaction_Date" => $this->info['bill_date'],
                        "Currency_ID" => $this->info['currency_id'],
                        "Logged_ID" => $this->logged_id,
                        "created_logged_id" => $this->logged_id,
                        "date_created" => $this->current_date_time,
                        "date_time" => $this->current_date_time,
                        "Transaction_Description" => $this->info['bill_description'],
                        "Current_Ex_Rate" => $rate,
                        "second_currency_rate" => $second_rate,
                        "main_id" => $bill_id,
                        "main_type" => "sbl",
                        "main_nb" => $bill_id
                    ];
                    JV::addTransaction($bill_main_trans, $is_actual);
                    $bill_main_trans = ["JV_ID" => $jv,
                        "Transaction_Reference" => $ref,
                        "Transaction_Account" => $customer_account,
                        "Transaction_Type" => "Debit",
                        "Transaction_Amount" => $this->info['main_total'],
                        "Transaction_Date" => $this->info['bill_date'],
                        "Currency_ID" => $this->info['currency_id'],
                        "Logged_ID" => $this->logged_id,
                        "created_logged_id" => $this->logged_id,
                        "date_created" => $this->current_date_time,
                        "date_time" => $this->current_date_time,
                        "Transaction_Description" => $this->info['bill_description'],
                        "Current_Ex_Rate" => $rate,
                        "second_currency_rate" => $second_rate,
                        "main_id" => $bill_id,
                        "main_type" => "sbl",
                        "main_nb" => $bill_id
                    ];
                    JV::addTransaction($bill_main_trans, $is_actual);
                }
                $bill_main_trans = ["JV_ID" => $jv,
                    "Transaction_Reference" => $ref,
                    "Transaction_Account" => $customer_account,
                    "Transaction_Type" => "Credit",
                    "Transaction_Amount" => $total_without_first,
                    "Transaction_Date" => $this->info['bill_date'],
                    "Currency_ID" => $this->info['currency_id'],
                    "Logged_ID" => $this->logged_id,
                    "created_logged_id" => $this->logged_id,
                    "date_created" => $this->current_date_time,
                    "date_time" => $this->current_date_time,
                    "Transaction_Description" => $this->info['bill_description'],
                    "Current_Ex_Rate" => $rate,
                    "second_currency_rate" => $second_rate,
                    "main_id" => $bill_id,
                    "main_type" => "sbl",
                    "main_nb" => $bill_id
                ];
                JV::addTransaction($bill_main_trans, $is_actual);
            }
            //
            $bill_details = json_decode($this->info['bill_details'], true);
            foreach ($bill_details as $bill_detail) {
                $this->db->prepare_Stmt('insert into tbl_accounting_schedule_bills_details (bill_detail_nb,bill_detail_due_date,bill_detail_amount,bill_id)
                        VALUES (?,?,?,?)', [&$bill_detail['bill_detail_nb'], &$bill_detail['bill_detail_due_date'],
                    &$bill_detail['bill_detail_amount'], &$bill_id]);
                $bill_detail_id = $this->db->id();
                if ($is_generate_trans) {
                    $bill_detail_trans = ["JV_ID" => $jv,
                        "Transaction_Reference" => $ref,
                        "Transaction_Account" => $customer_bill_acc,
                        "Transaction_Type" => "Debit",
                        "Transaction_Amount" => $bill_detail['bill_detail_amount'],
                        "Transaction_Date" => $this->info['bill_date'],
                        "Currency_ID" => $this->info['currency_id'],
                        "Logged_ID" => $this->logged_id,
                        "created_logged_id" => $this->logged_id,
                        "date_created" => $this->current_date_time,
                        "date_time" => $this->current_date_time,
                        "Transaction_Description" => $this->info['bill_description'],
                        "Current_Ex_Rate" => $rate,
                        "second_currency_rate" => $second_rate,
                        "main_id" => $bill_id,
                        "main_type" => "sbl",
                        "main_nb" => $bill_id,
                        "sub_id" => $bill_detail_id,
                        "due_date" => $bill_detail['bill_detail_due_date']
                    ];
                    JV::addTransaction($bill_detail_trans, $is_actual);
                }
            }
        } else {
            $this->db->prepare_Stmt('update tbl_accounting_schedule_bills set
                                  customer_id = ?,account_id = ?,pay_for = ?,main_total = ?,nb_of_bills = ?,
                                  first_bill = ?,bill_amount = ?,last_bill = ?,currency_id = ?,payment_period = ?,
                                bill_date = ?,start_date = ?,bill_description = ?,date_time = ?,logged_id = ? 
                                 where bill_id = ?
        ', [&$this->info['customer_id'], &$this->info['account_id'],
                &$this->info['pay_for'], &$this->info['main_total'], &$this->info['nb_of_bills'],
                &$this->info['first_bill'], &$this->info['bill_amount'], &$this->info['last_bill'],
                &$this->info['currency_id'], &$this->info['payment_period'], &$this->info['bill_date'],
                &$this->info['start_date'], &$this->info['bill_description'], &$this->current_date_time,
                &$this->logged_id, &$this->info['bill_id']]);
            $ref = "Schedule Bills.{$this->info['bill_id']}";
            if ($this->info['is_used'] == 0) {
                if ($is_generate_trans) {
                    $transaction_old_query = $this->db->query('select JV_ID from tbl_accounting_transactions 
                        where main_id = "' . $this->info['bill_id'] . '" and main_type ="sbl" limit 1');
                    $row_jv = $this->db->assoc($transaction_old_query);
                    if (empty($row_jv))
                        $jv = JV::returnJV($is_actual);
                    else
                        $jv = $row_jv['JV_ID'];
                    $this->db->prepare_Stmt('delete from tbl_accounting_transactions where main_id = ? and main_type ="sbl"', [&$this->info['bill_id']]);
                }
                $this->db->prepare_Stmt('delete from tbl_accounting_schedule_bills_details where bill_id = ? ', [&$this->info['bill_id']]);
                if ($is_generate_trans) {
                    if (!empty($this->info['account_id'])) {
                        $bill_main_trans = ["JV_ID" => $jv,
                            "Transaction_Reference" => $ref,
                            "Transaction_Account" => $this->info['account_id'],
                            "Transaction_Type" => "Credit",
                            "Transaction_Amount" => $this->info['main_total'],
                            "Transaction_Date" => $this->info['bill_date'],
                            "Currency_ID" => $this->info['currency_id'],
                            "Logged_ID" => $this->logged_id,
                            "created_logged_id" => $this->logged_id,
                            "date_created" => $this->current_date_time,
                            "date_time" => $this->current_date_time,
                            "Transaction_Description" => $this->info['bill_description'],
                            "Current_Ex_Rate" => $rate,
                            "second_currency_rate" => $second_rate,
                            "main_id" => $this->info['bill_id'],
                            "main_type" => "sbl",
                            "main_nb" => $this->info['bill_id']
                        ];
                        JV::addTransaction($bill_main_trans, $is_actual);
                        $bill_main_trans = ["JV_ID" => $jv,
                            "Transaction_Reference" => $ref,
                            "Transaction_Account" => $customer_account,
                            "Transaction_Type" => "Debit",
                            "Transaction_Amount" => $this->info['main_total'],
                            "Transaction_Date" => $this->info['bill_date'],
                            "Currency_ID" => $this->info['currency_id'],
                            "Logged_ID" => $this->logged_id,
                            "created_logged_id" => $this->logged_id,
                            "date_created" => $this->current_date_time,
                            "date_time" => $this->current_date_time,
                            "Transaction_Description" => $this->info['bill_description'],
                            "Current_Ex_Rate" => $rate,
                            "second_currency_rate" => $second_rate,
                            "main_id" => $this->info['bill_id'],
                            "main_type" => "sbl",
                            "main_nb" => $this->info['bill_id']
                        ];
                        JV::addTransaction($bill_main_trans, $is_actual);
                    }
                    $bill_main_trans = ["JV_ID" => $jv,
                        "Transaction_Reference" => $ref,
                        "Transaction_Account" => $customer_account,
                        "Transaction_Type" => "Credit",
                        "Transaction_Amount" => $total_without_first,
                        "Transaction_Date" => $this->info['bill_date'],
                        "Currency_ID" => $this->info['currency_id'],
                        "Logged_ID" => $this->logged_id,
                        "created_logged_id" => $this->logged_id,
                        "date_created" => $this->current_date_time,
                        "date_time" => $this->current_date_time,
                        "Transaction_Description" => $this->info['bill_description'],
                        "Current_Ex_Rate" => $rate,
                        "second_currency_rate" => $second_rate,
                        "main_id" => $this->info['bill_id'],
                        "main_type" => "sbl",
                        "main_nb" => $this->info['bill_id']
                    ];
                    JV::addTransaction($bill_main_trans, $is_actual);
                }
                //
                $bill_details = json_decode($this->info['bill_details'], true);
                foreach ($bill_details as $bill_detail) {
                    $this->db->prepare_Stmt('insert into tbl_accounting_schedule_bills_details (bill_detail_nb,bill_detail_due_date,bill_detail_amount,bill_id)
                            VALUES (?,?,?,?)', [&$bill_detail['bill_detail_nb'], &$bill_detail['bill_detail_due_date'],
                        &$bill_detail['bill_detail_amount'], &$this->info['bill_id']]);
                    $bill_detail_id = $this->db->id();
                    if ($is_generate_trans) {
                        $bill_detail_trans = ["JV_ID" => $jv,
                            "Transaction_Reference" => $ref,
                            "Transaction_Account" => $customer_bill_acc,
                            "Transaction_Type" => "Debit",
                            "Transaction_Amount" => $bill_detail['bill_detail_amount'],
                            "Transaction_Date" => $this->info['bill_date'],
                            "Currency_ID" => $this->info['currency_id'],
                            "Logged_ID" => $this->logged_id,
                            "created_logged_id" => $this->logged_id,
                            "date_created" => $this->current_date_time,
                            "date_time" => $this->current_date_time,
                            "Transaction_Description" => $this->info['bill_description'],
                            "Current_Ex_Rate" => $rate,
                            "second_currency_rate" => $second_rate,
                            "main_id" => $this->info['bill_id'],
                            "main_type" => "sbl",
                            "main_nb" => $this->info['bill_id'],
                            "sub_id" => $bill_detail_id,
                            "due_date" => $bill_detail['bill_detail_due_date']
                        ];
                        JV::addTransaction($bill_detail_trans, $is_actual);
                    }
                }
            } else {
                $delete_condition = '';
                $old_transaction_data = array();
                //$jv_nb = '';
                $jv_nb = JV::returnJV($is_actual);
                $bill_details_id_paid_arr = json_decode($this->info['bill_detail_is_paid_arr'], true);
                $old_not_paid_ids = '';
                //we stop this since in case of no $is_generate_trans and select from bill details directly
                //                $currenct_transactions_query = $this->db->query(' select * from tbl_accounting_transactions
                //                    where main_id = "' . $this->info['bill_id'] . '" and main_type="sbl" 
                //                        and sub_id NOT in (' . implode(',', $bill_details_id_paid_arr) . ')');
                $currenct_transactions_query = $this->db->query(' select * from tbl_accounting_schedule_bills_details
                    where bill_id = "' . $this->info['bill_id'] . '" and bill_detail_id NOT in (' . implode(',', $bill_details_id_paid_arr) . ')');
                while ($row = $this->db->assoc($currenct_transactions_query)) {
                    //$jv_nb = $row['JV_ID'];
                    $old_transaction_data[$row['bill_detail_id']] = $row;
                    $old_not_paid_ids .= (empty($old_not_paid_ids) ? '' : ',') . $row['bill_detail_id'];
                }
                $bill_detail_ids_arr = json_decode($this->info['bill_detail_ids'], true);
                $array_difference = array_diff(array_keys($old_transaction_data), $bill_detail_ids_arr);
                if (!empty($array_difference))
                    $delete_condition .= ' and bill_detail_id in (' . implode(",", $array_difference) . ')';
                else
                    $delete_condition .= ' and 1=2 ';
                $this->db->prepare_Stmt('delete from tbl_accounting_schedule_bills_details where bill_id = ? ' . $delete_condition, [&$this->info['bill_id']]);
                $bill_details = json_decode($this->info['bill_details'], true);
                foreach ($bill_details as $bill_detail) {
                    if ($bill_detail['bill_detail_is_paid'] == 0) {
                        if ($bill_detail['bill_detail_id'] == 0) {
                            $this->db->prepare_Stmt('insert into tbl_accounting_schedule_bills_details (bill_detail_nb,bill_detail_due_date,bill_detail_amount,bill_id)
                                    VALUES (?,?,?,?)', [&$bill_detail['bill_detail_nb'], &$bill_detail['bill_detail_due_date'],
                                &$bill_detail['bill_detail_amount'], &$this->info['bill_id']]);
                            $bill_detail_id = $this->db->id();
                            if ($is_generate_trans) {
                                $bill_detail_trans = ["JV_ID" => $jv_nb,
                                    "Transaction_Reference" => $ref,
                                    "Transaction_Account" => $customer_bill_acc,
                                    "Transaction_Type" => "Debit",
                                    "Transaction_Amount" => $bill_detail['bill_detail_amount'],
                                    "Transaction_Date" => $this->info['bill_date'],
                                    "Currency_ID" => $this->info['currency_id'],
                                    "Logged_ID" => $this->logged_id,
                                    "created_logged_id" => $this->logged_id,
                                    "date_created" => $this->current_date_time,
                                    "date_time" => $this->current_date_time,
                                    "Transaction_Description" => $this->info['bill_description'],
                                    "Current_Ex_Rate" => $rate,
                                    "second_currency_rate" => $second_rate,
                                    "main_id" => $this->info['bill_id'],
                                    "main_type" => "sbl",
                                    "main_nb" => $this->info['bill_id'],
                                    "sub_id" => $bill_detail_id,
                                    "due_date" => $bill_detail['bill_detail_due_date']
                                ];
                                JV::addTransaction($bill_detail_trans, $is_actual);
                            }
                        } else {
                            if (!empty($old_transaction_data[$bill_detail['bill_detail_id']]) &&
                                    ($old_transaction_data[$bill_detail['bill_detail_id']]['bill_detail_amount'] != $bill_detail['bill_detail_amount'] || $old_transaction_data[$bill_detail['bill_detail_id']]['bill_detail_due_date'] != $bill_detail['bill_detail_due_date'])) {
                                if ($is_generate_trans) {
                                    $this->db->prepare_Stmt('update tbl_accounting_transactions set sub_id = NULL where 
                                        main_id = ? and sub_id = ? and main_type= "sbl"', [&$this->info['bill_id'], &$bill_detail['bill_detail_id']]);
                                }
                                $this->db->prepare_Stmt('update tbl_accounting_schedule_bills_details set bill_detail_nb = ?,bill_detail_due_date = ?
                                    ,bill_detail_amount = ? where bill_detail_id = ?', [&$bill_detail['bill_detail_nb'], &$bill_detail['bill_detail_due_date'],
                                    &$bill_detail['bill_detail_amount'], &$bill_detail['bill_detail_id']]);
                                if ($is_generate_trans) {
                                    $bill_detail_old_trans = ["JV_ID" => $jv_nb,
                                        "Transaction_Reference" => $ref,
                                        "Transaction_Account" => $customer_bill_acc,
                                        "Transaction_Type" => "Credit",
                                        "Transaction_Amount" => $old_transaction_data[$bill_detail['bill_detail_id']]['bill_detail_amount'],
                                        "Transaction_Date" => $this->info['bill_date'],
                                        "Currency_ID" => $this->info['currency_id'],
                                        "Logged_ID" => $this->logged_id,
                                        "created_logged_id" => $this->logged_id,
                                        "date_created" => $this->current_date_time,
                                        "date_time" => $this->current_date_time,
                                        "Transaction_Description" => $this->info['bill_description'],
                                        "Current_Ex_Rate" => $rate,
                                        "second_currency_rate" => $second_rate,
                                        "main_id" => $this->info['bill_id'],
                                        "main_type" => "sbl",
                                        "main_nb" => $this->info['bill_id'],
                                        "due_date" => $old_transaction_data[$bill_detail['bill_detail_id']]['bill_detail_due_date']
                                    ];
                                    JV::addTransaction($bill_detail_old_trans, $is_actual);
                                    $bill_detail_new_trans = ["JV_ID" => $jv_nb,
                                        "Transaction_Reference" => $ref,
                                        "Transaction_Account" => $customer_bill_acc,
                                        "Transaction_Type" => "Debit",
                                        "Transaction_Amount" => $bill_detail['bill_detail_amount'],
                                        "Transaction_Date" => $this->info['bill_date'],
                                        "Currency_ID" => $this->info['currency_id'],
                                        "Logged_ID" => $this->logged_id,
                                        "created_logged_id" => $this->logged_id,
                                        "date_created" => $this->current_date_time,
                                        "date_time" => $this->current_date_time,
                                        "Transaction_Description" => $this->info['bill_description'],
                                        "Current_Ex_Rate" => $rate,
                                        "second_currency_rate" => $second_rate,
                                        "main_id" => $this->info['bill_id'],
                                        "main_type" => "sbl",
                                        "main_nb" => $this->info['bill_id'],
                                        "sub_id" => $bill_detail['bill_detail_id'],
                                        "due_date" => $bill_detail['bill_detail_due_date']
                                    ];
                                    JV::addTransaction($bill_detail_new_trans, $is_actual);
                                }
                            } else
                                $this->db->prepare_Stmt('update tbl_accounting_schedule_bills_details set bill_detail_nb = ? where bill_detail_id = ?'
                                        , [&$bill_detail['bill_detail_nb'], &$bill_detail['bill_detail_id']]);
                        }
                    }
                }
                if ($is_generate_trans) {
                    foreach ($array_difference as $deleted_bill_detail) {
                        $this->db->prepare_Stmt('update tbl_accounting_transactions set sub_id = NULL where 
                                    main_id = ?  and sub_id = ? and main_type= "sbl"', [&$this->info['bill_id'], &$deleted_bill_detail]);
                        $bill_detail_old_trans = ["JV_ID" => $jv_nb,
                            "Transaction_Reference" => $ref,
                            "Transaction_Account" => $customer_bill_acc,
                            "Transaction_Type" => "Credit",
                            "Transaction_Amount" => $old_transaction_data[$deleted_bill_detail]['bill_detail_amount'],
                            "Transaction_Date" => $this->info['bill_date'],
                            "Currency_ID" => $this->info['currency_id'],
                            "Logged_ID" => $this->logged_id,
                            "created_logged_id" => $this->logged_id,
                            "date_created" => $this->current_date_time,
                            "date_time" => $this->current_date_time,
                            "Transaction_Description" => $this->info['bill_description'],
                            "Current_Ex_Rate" => $rate,
                            "second_currency_rate" => $second_rate,
                            "main_id" => $this->info['bill_id'],
                            "main_type" => "sbl",
                            "main_nb" => $this->info['bill_id'],
                            "due_date" => $old_transaction_data[$deleted_bill_detail]['bill_detail_due_date']
                        ];
                        JV::addTransaction($bill_detail_old_trans, $is_actual);
                    }
                }
            }
        }
        $error = $this->db->error();
        if (empty($error)) {
            if (!$this->stop_transaction_start_end)
                $this->db->query('COMMIT');
            return $this->info['bill_id'];
        } else {
            if (!$this->stop_transaction_start_end)
                $this->db->query('ROLLBACK');
            return array_unique($error);
        }
    }

    public function getMaxScheduleBillNumber($is_actual) {
        $query = $this->db->query('SELECT MAX(bill_id) AS Entry_ID 
            FROM tbl_accounting_schedule_bills where current_year_id = "' . $this->year_id . '" and is_actual ="' . $is_actual . '" ');
        $row = $this->db->assoc($query);
        $bill_nb = !empty($row) ? $row['Entry_ID'] + 1 : 1;
        return $bill_nb;
    }

    public function deleteScheduleBills() {
        $current_year_query = $this->db->query('select current_year_id,Transaction_Date,is_actual from tbl_accounting_transactions 
        where main_id = "' . $this->info['bill_id'] . '" and main_type in ("pbl","sbl") limit 1');
        $row = $this->db->assoc($current_year_query);
        $current_year_id = $row['current_year_id'];
        if ($this->year_id != $current_year_id) {
            $error = array();
            $error[0] = CommonLanguage::getMessage('edit_year_alert');
            return $error;
        }
        $check = CommonFunctions::check_lock_data(['type' => 'schedule_bill', 'start_date' => $row['Transaction_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_schedule_bills where bill_id = ?', [&$this->info['bill_id']]);
        $this->db->prepare_Stmt('delete from tbl_accounting_transactions where main_id = ? and main_type in ("sbl","pbl")', [&$this->info['bill_id']]);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function submitSchedulePaymentBill() {
        $this->db->query("START TRANSACTION");
        $is_actual = $this->info['is_actual'];
        $check = CommonFunctions::check_lock_data(['type' => 'schedule_bill', 'start_date' => $this->info['payment_date'], 'is_actual' => $is_actual]);

//        $check = JV::checkCurrentYear($this->info['payment_date'], $this->info['payment_date'], $is_actual);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $jv = JV::returnJV($is_actual);

        $is_generate_trans = smc_session()['default_setting']['schedule_bill_generate_trans'];
        if ($is_generate_trans)
            $customer_account = Customer::get_customer_bill_acc($this->info['customer_id']);
        else
            $customer_account = Customer::getCustomerAccount($this->info['customer_id'])['Account_ID'];
        //
        $ref = "Payment Schedule Bills. {$this->info['bill_id']}/{$this->info['bill_detail_id']}";
        $total_paid = 0;
        if (!empty($this->info['payment_arr'])) {
            $this->info['payment_arr'] = json_decode($this->info['payment_arr'], true);
            foreach ($this->info['payment_arr'] as $pay) {
                $rate = CurrencyFactory::getLastExRate($pay['currency_id']);
                $second_rate = CurrencyFactory::getSecondCurrencyLastExRate($pay['currency_id']);
                $total_paid += ($pay['payment_amount'] * $rate);
                $dat_1 = ["JV_ID" => $jv,
                    "Transaction_Reference" => $ref,
                    "Transaction_Account" => $customer_account,
                    "Transaction_Type" => "Credit",
                    "Transaction_Amount" => $pay['payment_amount'],
                    "Transaction_Date" => $this->info['payment_date'],
                    "Currency_ID" => $pay['currency_id'],
                    "user_ref" => $this->info['user_ref'],
                    "Logged_ID" => $this->logged_id,
                    "created_logged_id" => $this->logged_id,
                    "date_created" => $this->current_date_time,
                    "date_time" => $this->current_date_time,
                    "Transaction_Description" => 'bill payment',
                    "Current_Ex_Rate" => $rate,
                    "second_currency_rate" => $second_rate,
                    "main_id" => $this->info['bill_id'],
                    "main_type" => "pbl",
                    "sub_id" => $this->info['bill_detail_id'],
                ];
                JV::addTransaction($dat_1, $is_actual);
                $dat_2 = ["JV_ID" => $jv,
                    "Transaction_Reference" => $ref,
                    "Transaction_Account" => $pay['payment_account'],
                    "Transaction_Type" => "Debit",
                    "Transaction_Amount" => $pay['payment_amount'],
                    "Transaction_Date" => $this->info['payment_date'],
                    "Currency_ID" => $pay['currency_id'],
                    "user_ref" => $this->info['user_ref'],
                    "Logged_ID" => $this->logged_id,
                    "created_logged_id" => $this->logged_id,
                    "date_created" => $this->current_date_time,
                    "date_time" => $this->current_date_time,
                    "Transaction_Description" => 'bill payment',
                    "Current_Ex_Rate" => $rate,
                    "second_currency_rate" => $second_rate,
                    "main_id" => $this->info['bill_id'],
                    "main_type" => "pbl",
                    "sub_id" => $this->info['bill_detail_id'],
                    "subtype" => "cash"
                ];
                JV::addTransaction($dat_2, $is_actual);
            }
        }
        //        $total_payment_bill = self::getTotalPaymentBill();
        $is_paid_status = $this->info['total_amount'] == $total_paid ? 3 : ($total_paid == 0 ? 1 : 2);
        $total_paid = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies(smc_session()['local_currency'], $this->info['currency_id'], $total_paid);
        $this->db->prepare_Stmt('update tbl_accounting_schedule_bills_details set is_paid =  ?,bill_detail_paid_amount = bill_detail_paid_amount + ? where bill_detail_id = ?', [&$is_paid_status, &$total_paid, &$this->info['bill_detail_id']]);

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

    public static function getScheduleBillPaidAmount($bill_id) {
        $total_paid_amount = 0;
        $db = DBConnectorFactory::loadDriver();
        $query = $db->query('select sum(bill_detail_paid_amount) as total_paid_amount
                                  from tbl_accounting_schedule_bills_details
                                  where bill_id = "' . $bill_id . '" ');
        $total_paid_amount = $db->assoc($query)['total_paid_amount'];
        return $total_paid_amount;
    }

    public function deletePaymentScheduleBills() {
        $current_year_query = $this->db->query('select current_year_id from tbl_accounting_transactions 
            where main_id = "' . $this->info['main_id'] . '" and sub_id = "' . $this->info['sub_id'] . '"
            and JV_ID = "' . $this->info['JV_ID'] . '" and main_type in ("pbl") limit 1');
        $current_year_id = $this->db->assoc($current_year_query)['current_year_id'];
        if ($this->year_id != $current_year_id) {
            $error = array();
            $error[0] = CommonLanguage::getMessage('edit_year_alert');
            return $error;
        }
        $this->db->query("START TRANSACTION");

        //        $this->info['total_amount'] = $row['bill_detail_amount'];
        $payment_query = $this->db->query('select SUM(Transaction_Amount*Current_Ex_Rate) as total_amount,Transaction_Date
                                from tbl_accounting_transactions where main_id ="' . $this->info['main_id'] . '"
                                and sub_id = "' . $this->info['sub_id'] . '" and JV_ID = "' . $this->info['JV_ID'] . '" 
                                and main_type ="pbl"  and subtype="cash"');
        $row = $this->db->assoc($payment_query);
        $total = CurrencyFactory::convertCurrencyTimeExchangeRate(smc_session()['local_currency'], $this->info['currency_id'], $row['total_amount'], $row['Transaction_Date']);
        $this->db->prepare_Stmt('delete from tbl_accounting_transactions where main_id = ? and sub_id = ? 
            and JV_ID = ? and main_type in ("pbl") ', [&$this->info['main_id'], &$this->info['sub_id'], &$this->info['JV_ID']]);

        $this->info['bill_id'] = $this->info['main_id'];
        $this->info['bill_detail_id'] = $this->info['sub_id'];

        $total_payment_bill = self::getTotalPaymentBill() - $total;
        $total_payment_bill = round($total_payment_bill, 8);
        //        $is_paid_status = $this->info['total_amount'] == $total_payment_bill ? 3 : ($total_payment_bill == 0 ? 1 : 2);
        //        echo $total.'===';die();
        $this->db->prepare_Stmt('update tbl_accounting_schedule_bills_details set 
                is_paid = if(bill_detail_amount = "' . $total_payment_bill . '", 3, ' . ($total_payment_bill == 0 ? '1' : '2') . '),
                    bill_detail_paid_amount = bill_detail_paid_amount - ?
            where bill_detail_id = ?', [&$total, &$this->info['bill_detail_id']]);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function getTotalPaymentBill() {

        $payment_query = $this->db->query('select bill_detail_paid_amount 
             from tbl_accounting_schedule_bills_details where bill_detail_id="' . $this->info['bill_detail_id'] . '" ');
        $total = $this->db->assoc($payment_query)['bill_detail_paid_amount'];
        return $total ? $total : 0;
    }

    public function getScheduleBillsData($bill_id) {
        $data = array('is_used' => 0, 'main' => array(), 'details' => array());
        $is_used = 0;
        $bill_main_query = $this->db->query('
                                            select bills.*,
                                                   customers.Customer_Name,
                                                   currency.Currency_Code,
                                                   currency.Currency_Symbol,
                                                   currency.Currency_Name,
                                                   accounts.Account_Name,
                                                   accounts.Account_Code,
                                                   customers.Customer_Mobile,
                                                   customers.Customer_Address
                                            from tbl_accounting_schedule_bills bills
                                            inner join tbl_customers customers on customers.Customer_ID = bills.customer_id
                                            inner join tbl_common_currency currency on currency.Currency_ID = bills.currency_id
                                            left join tbl_chart_of_accounts accounts on accounts.Account_ID = bills.account_id
                                            where bills.bill_id = "' . $bill_id . '"
        ');
        $data['main'] = $this->db->assoc($bill_main_query);
        $data['main']['real_nb_of_bills'] = 0;
        $bill_details_query = $this->db->query('select bills_details.*,
                                                       bills.bill_nb,
                                                       bills.is_actual,
                                                       bills.main_total,
                                                       bills.currency_id,
                                                       trans.Transaction_ID,
                                                       trans.JV_ID,
                                                       trans.Transaction_Amount
                                                from tbl_accounting_schedule_bills_details bills_details
                                                inner join tbl_accounting_schedule_bills bills  using(bill_id)
                                                left join tbl_accounting_transactions trans 
                                                on trans.main_id = bills.bill_id and trans.sub_id = bills_details.bill_detail_id
                                                and trans.subtype= "cash"
                                                where bills.bill_id = "' . $bill_id . '"');
        while ($row = $this->db->assoc($bill_details_query)) {
            $data['main']['real_nb_of_bills'] ++;
            $row['bill_detail_amount_formatted'] = CommonFunctions::formatNumber($row['bill_detail_amount']);
            $row['bill_amount_word_ar'] = Forms::num_to_word($row['bill_detail_amount'], $row['currency_id'], 'ar');
            if (!empty($row['Transaction_ID']))
                $is_used++;
            $data['details'][$row['bill_detail_id']] = $row;
        }
        $data['details'] = array_values($data['details']);
        $data['is_used'] = $is_used;
        return $data;
    }

    public function payForScheduleBillSearch() {
        $data = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($this->info['term'])) {
            $cond .= ' and (pay_for LIKE  ? )';
            $data_filter[] = "%" . $this->info['term'] . "%";
        }
        $query = $this->db->prepare_Stmt('SELECT distinct(pay_for) as text
                                    from tbl_accounting_schedule_bills
                                    where 1=1
                                    ' . $cond . ' limit 30', $data_filter);
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function submitSubscriptionMultiPayment() {
        $this->stop_transaction_start_end = true;
        $this->db->query('START TRANSACTION');
        $data = $this->info['invoice_list'];
        $payment_date = empty($this->info['payment_date']) ? date('Y-m-d') : $this->info['payment_date'];
        $date_time = date("Y-m-d H:i:s");
        //        echo '<pre>';
        //        print_r($data);
        //        die('ok');
        $payment_type = $this->info['payment_type'];
        foreach ($data as $row) {
            $row['Payment_Date'] = $payment_date;
            $row['Status_ID'] = 0;
            $row['IsReceipt'] = 1;
            $row['Currency_ID'] = $row['currency_id'];
            $row['Account_Code'] = Customer::getCustomerAccount($row['customer_id'])['Account_ID'];
            $row['job_id'] = empty($row['job_id']) ? NULL : $row['job_id'];
            $row['project_id'] = empty($row['project_id']) ? NULL : $row['project_id'];
            $row['subscription_fees_id'] = $row['main_id'];
            $row['payment'][] = array(
                'is_cheque' => 0,
                'Payment_Type_ID' => $payment_type,
                'Payment_Amount' => $row['net_total'],
                'Payment_Note' => 'Receipt subscription fees',
                'Check_Number' => '',
                'Check_Date' => '',
                'value_date' => '',
                'user_ref' => ''
            );
            $this->setData($row)->create();
            $paid_status = ($row['current_remaining'] - $row['net_total']) <= 0 ? 3 : 2;
            $this->db->prepare_Stmt('update tbl_fees_subscription_main set paid_status = ?,paid_amount = paid_amount+ ?,date_time = ? where main_id = ? ', [&$paid_status, &$row['net_total'],$date_time, &$row['main_id']]);
        }
        $error = $this->db->error();
        //                print_r($error);
        //                die('ok');
        if (empty($error))
            $this->db->query('COMMIT');
        else
            $this->db->query('ROLLBACK');
        return $error;
    }

    public function import_bills() {
        $this->stop_transaction_start_end = true;
        $this->db->query('START TRANSACTION');
        $data = array();
        $customer_arr = array();
        $this->db->prepare_Stmt('update tbl_customers set Customer_Name = trim(Customer_Name)');
        $this->db->prepare_Stmt('update tbl_chart_of_accounts set Account_Name = trim(Account_Name)');
        $query = $this->db->query('select Customer_ID,Customer_Name from tbl_customers');
        while ($row = $this->db->assoc($query)) {
            $customer_arr[$row['Customer_Name']] = $row['Customer_ID'];
        }
        //        echo"<pre>";print_r($customer_arr);
        $query = $this->db->query('select * from abj_bills ');
        while ($row = $this->db->assoc($query)) {
            $data[$row['ref']][] = $row;
        }
        $result_arr = array();
        foreach ($data as $ref_id => $row) {
            $result_arr[$ref_id]['bill_id'] = 0;
            $result_arr[$ref_id]['main_total'] = 0;
            $result_arr[$ref_id]['bill_amount'] = 0;
            $result_arr[$ref_id]['nb_of_bills'] = 0;
            $result_arr[$ref_id]['first_bill'] = 0;
            $result_arr[$ref_id]['last_bill'] = 0;
            $result_arr[$ref_id]['account_id'] = 0;
            $result_arr[$ref_id]['pay_for'] = '';
            $result_arr[$ref_id]['currency_id'] = 144;
            $result_arr[$ref_id]['payment_period'] = 1;
            $result_arr[$ref_id]['bill_date'] = date('Y-m-d');
            $result_arr[$ref_id]['start_date'] = '';
            $result_arr[$ref_id]['bill_description'] = $ref_id;
            $result_arr[$ref_id]['current_year_id'] = $this->year_id;
            $result_arr[$ref_id]['is_actual'] = 0;
            $result_arr[$ref_id]['bill_details'] = array();
            //            $bill_nb=0;
            foreach ($row as $indx => $row_det) {
                $result_arr[$ref_id]['pay_for'] = $row_det['customer_name'];

                //                echo"<pre>";print_r($row_det);
                $result_arr[$ref_id]['customer_id'] = !empty($customer_arr[$row_det['customer_name']]) ? $customer_arr[$row_det['customer_name']] : '';
                $result_arr[$ref_id]['main_total'] += $row_det['bill_amount'];
                $result_arr[$ref_id]['nb_of_bills'] ++;
                //                if (empty($result_arr[$ref_id]['bill_date']))
                //                    $result_arr[$ref_id]['bill_date'] = $row_det['date'];
                if (empty($result_arr[$ref_id]['start_date']))
                    $result_arr[$ref_id]['start_date'] = $row_det['date'];
                if ($indx == 0) {
                    //                    $bill_nb = $row_det['BillNo'];
                    $result_arr[$ref_id]['bill_amount'] = $row_det['bill_amount'];
                }
                if ($indx == (count($row) - 1) && $row_det['bill_amount'] != $result_arr[$ref_id]['bill_amount'] && $indx > 0)
                    $result_arr[$ref_id]['last_bill'] = $row_det['bill_amount'];
                $result_arr[$ref_id]['bill_details'][] = array(
                    'bill_detail_nb' => $row_det['BillNo'],
                    'bill_detail_due_date' => $row_det['date'],
                    'bill_detail_amount' => $row_det['bill_amount'],
                );
                //                $bill_nb++;
            }
            if (!empty($result_arr[$ref_id]['customer_id'])) {
                $result_arr[$ref_id]['bill_details'] = json_encode($result_arr[$ref_id]['bill_details']);
                $this->info = $result_arr[$ref_id];
                $this->submitScheduleBills();
            }
        }
        //SELECT *,count(*) as mycount FROM `tbl_accounting_schedule_bills_details` group by `bill_detail_due_date`,`bill_id` having mycount > 1
        //SELECT b.bill_description as ref,b.pay_for,d.bill_detail_nb,d.bill_detail_due_date,count(*) as mycount FROM `tbl_accounting_schedule_bills_details` d join tbl_accounting_schedule_bills b using(bill_id) group by `bill_detail_due_date`,`bill_id` having mycount > 1
        //         echo "<pre>";
        //        echo count($result_arr).'===';
        //        die(count($result_arr));
        $error = $this->db->error();
        if (empty($error))
            $this->db->query('COMMIT');
        else
            $this->db->query('ROLLBACK');
        echo "<pre>";
        die(print_r($error));
        return $error;
    }

    public function settle_payment($payment_ids) {
        $this->db->query('START TRANSACTION');
        $logged_id = smc_session()['user_logged_id'];
        $date_time = date("Y-m-d H:i:s");
//        $this->db->prepare_Stmt('update tbl_payment_receipt set is_pending=0 
//            where pay_rec_id in (' . implode(',', $payment_ids) . ')');
        $query = $this->db->query('select * from
            tbl_payment_receipt_details d join tbl_payment_receipt p on p.pay_rec_id = d.pay_rec_id 
            where p.pay_rec_id in (' . implode(',', $payment_ids) . ')');
        while ($row = $this->db->assoc($query)) {
            $pay_nb = self::getmaxPaymentID($row['is_receipt'], $row['is_actual'], $row['prefix'], 0, $row['is_return']);
            $this->db->prepare_Stmt('update tbl_payment_receipt set date_time = ?,pay_rec_nb = ?,is_pending=0
            where pay_rec_id = ? ', [$date_time, $pay_nb, $row['pay_rec_id']]);
            $commission_amount = abs($row['commission_amount']);
            $pay_acc = $row['payment_account_id'];
            $row['Account_Name'] = JV::getAccountNameByID($row['Account_ID']);
            $desc = empty($row['Payment_Note']) ? (($row['is_receipt'] ? ($row['is_return'] ? "Return Receipt" : "Receipt from ") : ($row['is_return'] ? "Return Payment" : "Payment to ")) . $row['Account_Name']) : $row['Payment_Note'];
            $ref = ($row['is_return'] ? 'Return ' : '') . ($row['is_receipt'] ? 'Rec.' : 'Pay.') . $row['prefix'] . $row['pay_rec_nb'];
            $jv = JV::returnJV($row['is_actual']);
            $main_type = ($row['is_receipt'] ? ($row['is_return'] ? "retr" : "rec") : ($row['is_return'] ? "retp" : "pay"));
            $dat_2 = ["JV_ID" => $jv,
                "Transaction_Reference" => $ref,
                "Transaction_Account" => $row['Account_ID'],
                "Transaction_Type" => (($row['is_receipt'] || (!$row['is_receipt'] && $row['is_return'])) ? "Credit" : "Debit"),
                "Transaction_Amount" => $row['payment_amount'],
                "Transaction_Date" => $row['payment_date'],
                "user_ref" => $row['user_ref'],
                "Currency_ID" => $row['currency_id'],
                "Logged_ID" => $logged_id,
                "created_logged_id" => $logged_id,
                "date_created" => $date_time,
                "date_time" => $date_time,
                "Transaction_Description" => $desc,
                "Current_Ex_Rate" => $row['current_rate'],
                "second_currency_rate" => $row['second_currency_rate'],
                "main_id" => $row['pay_rec_id'],
                "sub_id" => $row['payment_detail_id'],
                "main_type" => $main_type,
                "branch_id" => $row['branch_id'],
                "prefix" => $row['prefix'],
                "main_nb" => $row['pay_rec_nb'],
                "due_date" => $row['payment_due_date'],
                "job_id" => $row['job_id'],
                "project_id" => $row['project_id'],
                "pos_customer_id" => $row['pos_customer_id']
            ];
            JV::addTransaction($dat_2, $row['is_actual']);
            //            $error = $this->db->error();
            //            echo"<pre>";die(print_r($error));
            $dat_1 = ["JV_ID" => $jv,
                "Transaction_Reference" => $ref,
                "Transaction_Account" => $pay_acc,
                "Transaction_Type" => (($row['is_receipt'] || (!$row['is_receipt'] && $row['is_return'])) ? "Debit" : "Credit"),
                "Transaction_Amount" => $row['payment_amount'],
                "Transaction_Date" => $row['payment_date'],
                "user_ref" => $row['user_ref'],
                "Currency_ID" => $row['currency_id'],
                "Logged_ID" => $logged_id,
                "created_logged_id" => $logged_id,
                "date_created" => $date_time,
                "date_time" => $date_time,
                "Transaction_Description" => $desc,
                "Current_Ex_Rate" => $row['current_rate'],
                "second_currency_rate" => $row['second_currency_rate'],
                "main_id" => $row['pay_rec_id'],
                "sub_id" => $row['payment_detail_id'],
                "main_type" => $main_type,
                "branch_id" => $row['branch_id'],
                "prefix" => $row['prefix'],
                "main_nb" => $row['pay_rec_nb'],
                "due_date" => $row['payment_due_date'],
                "value_date" => $row['value_date'],
                "check_nb" => $row['cheque_nb'],
                "job_id" => $row['job_id'],
                "project_id" => $row['project_id']
            ];
            JV::addTransaction($dat_1, $row['is_actual']);
            $dat_12 = ["JV_ID" => $jv,
                "Transaction_Reference" => $ref,
                "Transaction_Account" => $pay_acc,
                "Transaction_Type" => (((($row['is_receipt'] || (!$row['is_receipt'] && $row['is_return'])) && $row['commission_amount'] > 0) || ((!$row['is_receipt'] || ($row['is_receipt'] && $row['is_return'])) && $row['commission_amount'] < 0)) ? "Credit" : "Debit"),
                "Transaction_Amount" => $commission_amount,
                "Transaction_Date" => $row['payment_date'],
                "user_ref" => $row['user_ref'],
                "Currency_ID" => $row['currency_id'],
                "Logged_ID" => $logged_id,
                "created_logged_id" => $logged_id,
                "date_created" => $date_time,
                "date_time" => $date_time,
                "Transaction_Description" => $desc,
                "Current_Ex_Rate" => $row['current_rate'],
                "second_currency_rate" => $row['second_currency_rate'],
                "main_id" => $row['pay_rec_id'],
                "sub_id" => $row['payment_detail_id'],
                "main_type" => $main_type,
                "branch_id" => $row['branch_id'],
                "prefix" => $row['prefix'],
                "main_nb" => $row['pay_rec_nb'],
                "due_date" => $row['payment_due_date'],
                "job_id" => $row['job_id'],
                "project_id" => $row['project_id']
            ];
            JV::addTransaction($dat_12, $row['is_actual']);
            $dat_13 = ["JV_ID" => $jv,
                "Transaction_Reference" => $ref,
                "Transaction_Account" => $row['commission_acc_id'],
                "Transaction_Type" => (((($row['is_receipt'] || (!$row['is_receipt'] && $row['is_return'])) && $row['commission_amount'] > 0) || ((!$row['is_receipt'] || ($row['is_receipt'] && $row['is_return'])) && $row['commission_amount'] < 0)) ? "Debit" : "Credit"),
                "Transaction_Amount" => $commission_amount,
                "Transaction_Date" => $row['payment_date'],
                "user_ref" => $row['user_ref'],
                "Currency_ID" => $row['currency_id'],
                "Logged_ID" => $logged_id,
                "created_logged_id" => $logged_id,
                "date_created" => $date_time,
                "date_time" => $date_time,
                "Transaction_Description" => $desc,
                "Current_Ex_Rate" => $row['current_rate'],
                "second_currency_rate" => $row['second_currency_rate'],
                "main_id" => $row['pay_rec_id'],
                "sub_id" => $row['payment_detail_id'],
                "main_type" => $main_type,
                "branch_id" => $row['branch_id'],
                "prefix" => $row['prefix'],
                "main_nb" => $row['pay_rec_nb'],
                "due_date" => $row['payment_due_date'],
                "job_id" => $row['job_id'],
                "project_id" => $row['project_id']
            ];
            JV::addTransaction($dat_13, $row['is_actual']);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function get_total_payments_receipt() {
        $condition = '';
        if($this->perm->hasPermission('Branches')){
            $condition .= !empty($_GET['search_branch_id']) ? ' and branch_id ="' . ($_GET['search_branch_id']) . '"' : '';
        }else{
            $condition .= ' and branch_id ="' . (smc_session()['user_accounts']['branch_id']) . '"'  ;
        }
        
        
        if (isset($this->info['Payment_Date_From']) && isset($this->info['Payment_Date_To'])) {
            $IsReceipt = $this->info["Is_Receipt"];
            $FromDate = $this->info['Payment_Date_From'];
            $ToDate = $this->info['Payment_Date_To'];
            $condition .= ' and Payment_Date >= "' . $FromDate . '" AND Payment_Date <= "' . $ToDate . '"';
        }
        if (!empty($this->info['salesman_id']))
            $condition .= ' and salesman_id = "' . $this->info['salesman_id'] . '" ';
        if (!empty($this->info['user_id']))
            $condition .= ' and created_logged_id = "' . $this->info['user_id'] . '" ';
        if (isset($this->info['is_pending']) && $this->info['is_pending'] != '')
            $condition .= ' and is_pending = "' . $this->info['is_pending'] . '" ';
        $condition .= ' AND is_actual= "' . $this->info['Is_Actual'] . '" AND current_year_id = "' . $this->year_id . '"';
        $condition .= $IsReceipt ? ' AND is_receipt = 1' : ' AND is_receipt = 0';
        $condition .= ' AND is_return =  "' . $this->info['is_return'] . '"';
        $query = $this->db->query('select sum(d.payment_amount*d.current_rate) as total_first,
                                          sum(d.payment_amount*d.second_currency_rate) as total_second,branch_id
            from tbl_payment_receipt p join tbl_payment_receipt_details d 
            on d.pay_rec_id = p.pay_rec_id where 1=1 ' . $condition);
        $row = $this->db->assoc($query);
        $totals = CommonFunctions::formatNumber($row['total_first']) . ' ' . smc_session()['local_currency_symbol'] . '<br>';
        // HIDE HIDE yousifhide
        if (smc_session()['default_setting']['activate_second_currency'] == 1)
        $totals .= CommonFunctions::formatNumber($row['total_second']) . ' ' . smc_session()['second_currency']['symbol'];
        
        return $totals;
    }

    public function get_payment_detail($detail_id) {
        $data = array();
        $query = $this->db->query('select * from tbl_payment_receipt_details 
            where payment_detail_id = "' . $detail_id . '"');
        $row = $this->db->assoc($query);
        if (!empty($row))
            $data = $row;
        return $data;
    }

    public function generate_col_nb($actual) {
        //smc_session()['user_accounts']['branch_id'] !='' ? smc_session()['user_accounts']['branch_id'] : smc_session()['default_branch_id']
        $year = $actual == 1 ? $this->year_id_actual : $this->year_id;
        $query = $this->db->query('SELECT MAX(collection_nb) AS Max_Id FROM tbl_pay_rec_collection 
                where current_year_id = "' . $year . '" 
                and is_actual = "' . $actual . '" 
                    ');
        $row = $this->db->assoc($query);
        return $row['Max_Id'] + 1;
    }

    public function submit_collection() {
        $this->db->query('START TRANSACTION');
        $datetime = date('Y-m-d H:i:s');
        $logged_id = smc_session()['user_logged_id'];
        $this->info['current_rate'] = !empty($this->info['current_rate']) ? $this->info['current_rate'] : CurrencyFactory::getLastExRate($this->info['currency_id']);

        $this->info['second_currency_rate'] = !empty($this->info['second_currency_rate']) ? $this->info['second_currency_rate'] : CurrencyFactory::getSecondCurrencyLastExRate($this->info['currency_id']);
        if (empty($this->info['collection_id'])) {
            $created_date = date('Y-m-d H:i:s');
            $created_logged_id = smc_session()['user_logged_id'];

            $collection_nb = $this->generate_col_nb($this->info['is_actual']);
            $data = [$collection_nb, $this->info['collection_date'], $this->info['pay_detail_id'],
                $this->info['account_id'], $this->info['pay_amount'], $this->info['commission_acc_id'],
                $this->info['commission_amount'], $datetime, $datetime, $logged_id, $logged_id,
                $this->info['note'], $this->info['currency_id'], $this->info['current_rate'],
                $this->info['second_currency_rate'], $this->info['is_actual'], $this->info['job_id'],
                $this->info['project_id'], $this->info['branch_id'], $this->year_id];
            $this->db->prepare_Stmt('INSERT INTO `tbl_pay_rec_collection`(collection_nb, `collection_date`,
            `pay_detail_id`, `account_id`, `pay_amount`, `commission_acc_id`, `commission_amount`,
            `date_created`, `date_time`, `created_logged_id`, `Logged_ID`, `note`, `currency_id`,
            `current_rate`, `second_currency_rate`,is_actual,job_id,project_id,branch_id,current_year_id)
            values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', $data);
            $collection_id = $this->db->id();
            $this->db->prepare_Stmt('update tbl_payment_receipt_details set is_collected = 1
            where payment_detail_id = "' . $this->info['pay_detail_id'] . '"');
            $jv = JV::returnJV($this->info['is_actual']);
        } else {
            $query = $this->db->query('select * from tbl_pay_rec_collection 
                where collection_id = "' . $this->info['collection_id'] . '"');
            $row = $this->db->assoc($query);
            $created_date = $row['date_created'];
            $created_logged_id = $row['created_logged_id'];
            $collection_nb = $row['collection_nb'];
            $data = [$this->info['collection_date'],
                $this->info['account_id'], $this->info['pay_amount'], $this->info['commission_acc_id'],
                $this->info['commission_amount'], $datetime, $logged_id,
                $this->info['note'], $this->info['currency_id'], $this->info['current_rate'],
                $this->info['second_currency_rate'], $this->info['job_id'],
                $this->info['project_id'], $this->info['branch_id'], $this->info['collection_id']];
            $this->db->prepare_Stmt('update `tbl_pay_rec_collection` set `collection_date`=?,
             `account_id`=?, `pay_amount`=?, `commission_acc_id`=?, `commission_amount`=?,
            `date_time`=?,`Logged_ID`=?, `note`=?, `currency_id`=?,
            `current_rate`=?, `second_currency_rate`=?,job_id=?,project_id=?,branch_id=?
            where collection_id = ?', $data);
            $collection_id = $this->info['collection_id'];
            $jv_query = $this->db->query("SELECT distinct JV_ID FROM tbl_accounting_transactions  
                WHERE main_id='" . $this->info['collection_id'] . "' AND main_type= 'col' ");
            $jv_assoc = $this->db->assoc($jv_query);

            if (!empty($jv_assoc))
                $jv = $jv_assoc['JV_ID'];
            else
                $jv = JV::returnJV($this->info['is_actual']);
            $this->db->prepare_Stmt('delete from tbl_accounting_transactions 
                where main_id = ? and main_type="col"', [$this->info['collection_id']]);
        }
        $ref = 'collection';
        $dat_2 = ["JV_ID" => $jv,
            "Transaction_Reference" => $ref,
            "Transaction_Account" => $this->info['account_id'],
            "Transaction_Type" => "Debit",
            "Transaction_Amount" => $this->info['pay_amount'],
            "Transaction_Date" => $this->info['collection_date'],
            "Currency_ID" => $this->info['currency_id'],
            "Logged_ID" => $logged_id,
            "created_logged_id" => $created_logged_id,
            "date_created" => $created_date,
            "date_time" => $datetime,
            "Transaction_Description" => $ref,
            "Current_Ex_Rate" => $this->info['current_rate'],
            "second_currency_rate" => $this->info['second_currency_rate'],
            "main_id" => $collection_id,
            "main_type" => 'col',
            "branch_id" => $this->info['branch_id'],
            "main_nb" => $collection_nb,
            "job_id" => $this->info['job_id'],
            "project_id" => $this->info['project_id'],
        ];
        JV::addTransaction($dat_2, $this->info['is_actual']);
        //            $error = $this->db->error();
        //            echo"<pre>";die(print_r($error));
        $dat_1 = ["JV_ID" => $jv,
            "Transaction_Reference" => $ref,
            "Transaction_Account" => $this->info['pay_acc_id'],
            "Transaction_Type" => "Credit",
            "Transaction_Amount" => $this->info['pay_amount'],
            "Transaction_Date" => $this->info['collection_date'],
            "Currency_ID" => $this->info['currency_id'],
            "Logged_ID" => $logged_id,
            "created_logged_id" => $logged_id,
            "date_created" => $datetime,
            "date_time" => $datetime,
            "Transaction_Description" => $ref,
            "Current_Ex_Rate" => $this->info['current_rate'],
            "second_currency_rate" => $this->info['second_currency_rate'],
            "main_id" => $collection_id,
            "main_type" => 'col',
            "branch_id" => $this->info['branch_id'],
            "main_nb" => $collection_nb,
            "job_id" => $this->info['job_id'],
            "project_id" => $this->info['project_id'],
        ];
        JV::addTransaction($dat_1, $this->info['is_actual']);
        $dat_1 = ["JV_ID" => $jv,
            "Transaction_Reference" => $ref,
            "Transaction_Account" => $this->info['pay_acc_id'],
            "Transaction_Type" => "Debit",
            "Transaction_Amount" => $this->info['commission_amount'],
            "Transaction_Date" => $this->info['collection_date'],
            "Currency_ID" => $this->info['currency_id'],
            "Logged_ID" => $logged_id,
            "created_logged_id" => $logged_id,
            "date_created" => $datetime,
            "date_time" => $datetime,
            "Transaction_Description" => $ref,
            "Current_Ex_Rate" => $this->info['current_rate'],
            "second_currency_rate" => $this->info['second_currency_rate'],
            "main_id" => $collection_id,
            "main_type" => 'col',
            "branch_id" => $this->info['branch_id'],
            "main_nb" => $collection_nb,
            "job_id" => $this->info['job_id'],
            "project_id" => $this->info['project_id'],
        ];
        JV::addTransaction($dat_1, $this->info['is_actual']);
        $dat_1 = ["JV_ID" => $jv,
            "Transaction_Reference" => $ref,
            "Transaction_Account" => $this->info['commission_acc_id'],
            "Transaction_Type" => "Credit",
            "Transaction_Amount" => $this->info['commission_amount'],
            "Transaction_Date" => $this->info['collection_date'],
            "Currency_ID" => $this->info['currency_id'],
            "Logged_ID" => $logged_id,
            "created_logged_id" => $logged_id,
            "date_created" => $datetime,
            "date_time" => $datetime,
            "Transaction_Description" => $ref,
            "Current_Ex_Rate" => $this->info['current_rate'],
            "second_currency_rate" => $this->info['second_currency_rate'],
            "main_id" => $collection_id,
            "main_type" => 'col',
            "branch_id" => $this->info['branch_id'],
            "main_nb" => $collection_nb,
            "job_id" => $this->info['job_id'],
            "project_id" => $this->info['project_id'],
        ];
        JV::addTransaction($dat_1, $this->info['is_actual']);


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

    public function delete_collection($id) {
        $this->db->query('START TRANSACTION');
        $query = $this->db->query('select pay_detail_id from tbl_pay_rec_collection where collection_id = "' . $id . '"');
        $detail_id = $this->db->assoc($query)['pay_detail_id'];
        $this->db->prepare_Stmt('delete from tbl_accounting_transactions where main_type="col"
            and main_id = ?', [$id]);
        $this->db->prepare_Stmt('update tbl_payment_receipt_details set is_collected=0 
            where payment_detail_id = ?', [$detail_id]);
        $this->db->prepare_Stmt('delete from tbl_pay_rec_collection where collection_id = ?', [$id]);
        $error = $this->db->error();
        if (empty($error))
            $this->db->query('COMMIT');
        else
            $this->db->query('ROLLBACK');
        return $error;
    }

    public function get_collection_by_id($id) {
        $data = array();
        $query = $this->db->query('select c.*,
            if(isnull(cm.Account_Code),"",concat_ws(" - ",cm.Account_Name,cm.Account_Code)) as com_name,
            concat_ws(" - ",a.Account_Name,a.Account_Code) as acc_name,
            pd.payment_account_id,b.branch_name,
            pr.project_name,j.job_name
            from tbl_pay_rec_collection c 
            join tbl_chart_of_accounts a on a.Account_ID = c.account_id
            left join tbl_chart_of_accounts  cm on cm.Account_ID= c.commission_acc_id
            left join tbl_organization_branches b on b.branch_id = c.branch_id
            left join tbl_accounting_jobs j on j.job_id = c.job_id
            left join tbl_projects pr on pr.project_id = c.project_id
            join tbl_payment_receipt_details pd on pd.payment_detail_id = c.pay_detail_id
            where collection_id = "' . $id . '"');
        $row = $this->db->assoc($query);
        if (!empty($row))
            $data = $row;
        return $data;
    }

    public function change_pay_rec_actual($id_arr, $is_receipt, $is_return, $is_actual) {
        $this->db->query('START TRANSACTION');
        $date_time = date('Y-m-d H:i:s');
        $logged_id = smc_session()['user_logged_id'];
        $type = $is_receipt ? ($is_return ? 'retr' : 'rec') : ($is_return ? 'retp' : 'pay');
        foreach ($id_arr as $id) {
            $query = $this->db->query('select prefix,is_pending from tbl_payment_receipt 
                where pay_rec_id = "' . $id . '"');
            $pay_row = $this->db->assoc($query);
            $prefix = $pay_row['prefix'];
            $is_pending = $pay_row['is_pending'];
            $nb = self::getmaxPaymentID($is_receipt, $is_actual, $prefix, $is_pending, $is_return);
//            echo $nb.'=='.$prefix.'=='.$is_pending;
            $this->db->prepare_Stmt('update tbl_payment_receipt set pay_rec_nb = ?,
                is_actual=?,is_main_transfer=0,date_time=?,Logged_ID=?
            where pay_rec_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="' . $type . '"', [&$nb, &$is_actual, &$jv, &$id]);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

}
