<?php

class ReturnInvoice {

    protected $info = array();
    public $stop_transaction_start_end = false;
    public $permission;

    public function __construct() {
        $this->db = DBConnectorFactory::loadDriver();
        $this->year_id = Session::getCurrentYearID();
        $this->year_id_actual = Session::getCurrentYearIDActual();
        $this->permission = Session::getPermissionsObject();
        $this->depot = new WarehouseDepot();
    }

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

    //    private function returnCustomerVATAccount($cust_code) {
    //        $acc_last_digits = substr($cust_code, -4);
    //        $query = $this->db->query('SELECT 
    //                                        Account_Code
    //                                    FROM 
    //                                        tbl_chart_of_accounts
    //                                    WHERE 
    //                                        LEFT(Account_Code,4) = "4427"
    //                                    AND 
    //                                        RIGHT(Account_Code,4) = "' . $acc_last_digits . '"
    //                ');
    //        $row = $this->db->assoc($query);
    //        return $row['Account_Code'];
    //    }
    //    private function returnSuppVATAccount($supp_code) {
    //        $query = $this->db->query('SELECT VAT_Type_ID FROM tbl_organization WHERE Organization_ID = 1');
    //        $row = $this->db->assoc($query);
    //        $org_type = $row['VAT_Type_ID'];
    //        if ($org_type == 1)
    //            $vat_1 = "44210";
    //        else
    //            $vat_1 = "44211";
    //        $acc_supp_last_digits = substr($supp_code, -3);
    //        $query = $this->db->query('SELECT 
    //                                        Account_Code
    //                                    FROM 
    //                                        tbl_chart_of_accounts
    //                                    WHERE 
    //                                        LEFT(Account_Code,5) = "' . $vat_1 . '"
    //                                    AND 
    //                                        RIGHT(Account_Code,3) = "' . $acc_supp_last_digits . '"
    //                ');
    //        $row = $this->db->assoc($query);
    //        return $row['Account_Code'];
    //    }

    public function getMaxReturnInvoice() {
        $query = $this->db->query('SELECT Max(Return_Invoice_ID) AS Max_ID FROM tbl_accounting_return_invoices');
        $row = $this->db->assoc($query);
        $return_ID = $row['Max_ID'];
        return $return_ID;
    }

    public function getTotalItemPrice() {
        if ($this->info['Is_Customer_Invoice'] == 1)
            $query = $this->db->query('SELECT Product_Discount AS Discount,Product_Net_Price AS Price FROM tbl_invoice_products_details WHERE Product_ID = "' . $productID . '" AND Invoice_ID = "' . $this->info['Invoice_Purchase_ID'] . '"');
        else
            $query = $this->db->query('SELECT Item_Discount AS Discount,Item_Price AS Price FROM tbl_inventory_purchase_items WHERE Product_ID = "' . $productID . '" AND Purchase_ID = "' . $this->info['Invoice_Purchase_ID'] . '"');
        $row = $this->db->assoc($query);
        $product_discount = $row["Discount"];
        $product_price = $row["Price"];
        $total_discount = ($product_discount * $this->info['Products'][$i]['Return_Product_Quantity'] * $product_price) / 100;
        $total_price = $product_price * $this->info['Products'][$i]['Return_Product_Quantity'] + $total_discount;
        return $total_price;
    }

    public function returnProductCodeAndName() {
        if ($this->info['Is_Customer_Invoice'] == 1)
            $query = $this->db->query('
                                        SELECT 
                                            s.Product_Code,
                                            s.Product_Name
                                        FROM 
                                            tbl_invoice_products_details i
                                        LEFT JOIN 
                                            tbl_stock_products s
                                        ON
                                            s.Product_ID = i.Product_ID
                                        WHERE
                                            i.Invoice_ID = "' . $this->info['Invoice_Purchase_ID'] . '"

                    ');
        else
            $query = $this->db->query('
                                        SELECT 
                                            s.Product_Code,
                                            s.Product_Name
                                        FROM 
                                            tbl_inventory_purchase_items i
                                        LEFT JOIN 
                                            tbl_stock_products s
                                        ON
                                            s.Product_ID = i.Product_ID
                                        WHERE
                                            i.Purchase_ID = "' . $this->info['Invoice_Purchase_ID'] . '"
                    ');
        while ($row = $this->db->assoc($query))
            $data[] = $row;
        return $data;
    }

    public function returnDebtNotes($id = null, $date = null, $month = null, $year = null) {
        $data = array();
        $condition = '';
        if (!is_null($id))
            $condition .= ' AND r.Return_Invoice_ID = "' . $id . '"';
        if (!is_null($date))
            $condition .= ' AND r.Return_Invoice_Date = "' . $date . '"';
        if (!is_null($month))
            $condition .= ' AND MONTH(r.Return_Invoice_Date) = "' . $month . '"';
        if (!is_null($year))
            $condition .= ' AND YEAR(r.Return_Invoice_Date) = "' . $year . '"';
        $query = $this->db->query('SELECT 
                                        r.*,
										p.*,
										c.*,
										s.*
                                    FROM 
                                        tbl_accounting_return_invoices r
                                    JOIN 
                                        tbl_inventory_purchases p
                                    ON 
                                        r.Invoice_Purchase_ID = p.Purchase_ID
									JOIN 
                                        tbl_common_currency c
                                    ON 
                                        c.Currency_ID = p.Currency_ID
									JOIN 
                                        tbl_stock_suppliers s
                                    ON 
                                        s.Supplier_ID = p.Supplier_ID
                                    WHERE
                                        r.Is_Customer_Invoice = 0' . $condition);
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function returnCreditNotes($id = null, $date = null, $month = null, $year = null) {
        $data = array();
        $condition = '';
        if (!is_null($id))
            $condition .= ' AND r.Return_Invoice_ID = "' . $id . '"';
        if (!is_null($date))
            $condition .= ' AND r.Return_Invoice_Date = "' . $date . '"';
        if (!is_null($month))
            $condition .= ' AND MONTH(r.Return_Invoice_Date) = "' . $month . '"';
        if (!is_null($year))
            $condition .= ' AND YEAR(r.Return_Invoice_Date) = "' . $year . '"';
        $query = $this->db->query('SELECT 
                                        r.*,
										i.Invoice_ID,
										c.*,
										cs.*
                                    FROM 
                                        tbl_accounting_return_invoices r
                                    JOIN 
                                        tbl_invoices i
                                    ON 
                                        r.Invoice_Purchase_ID = i.Invoice_ID
									JOIN 
                                        tbl_common_currency c
                                    ON 
                                        c.Currency_ID = i.Invoice_Currency
									JOIN 
                                        tbl_customers cs
                                    ON 
                                        cs.Customer_ID = i.Customer_ID
                                    WHERE
                                        r.Is_Customer_Invoice = 1' . $condition);
        while ($row = $this->db->assoc($query)) {


            $data[] = $row;
        }
        return $data;
    }

    //    public function returnCreditInvoice($id) {
    //        $data = array();
    //        $query = $this->db->query('SELECT *
    //                                    FROM 
    //                                        invoice_returned_list
    //                                    WHERE 
    //                                        Auto_ID = "' . $id . '" and current_year_id = "' . $this->year_id . '"
    //                ');
    //        while ($row = $this->db->assoc($query)) {
    //            $data[] = $row;
    //        }
    //
    //        return $data;
    //    }
    //    public function ViewReturnInvoice($id) {
    //        $data = array();
    //        $query = $this->db->query('SELECT *
    //                                    FROM 
    //                                        returned_main_detailed
    //                                    
    //                                    WHERE 
    //                                        Return_Invoice_ID = "' . $id . '"
    //                                    and current_year_id = "' . $this->year_id . '"
    //                                   
    //                ');
    //        while ($row = $this->db->assoc($query)) {
    //            $data[] = $row;
    //        }
    //        return $data;
    //    }
    //    public function viewInvoiceDetails($inv_id) {
    //        $data = array();
    //        $query = $this->db->query('SELECT * FROM invoice_returned_list WHERE Inv_Pur_ID = "' . $inv_id . '" ');
    //        while ($row = $this->db->assoc($query))
    //            $data[] = $row;
    //        return $data;
    //    }
    //    public function viewPurchaseDetails($inv_id) {
    //        $data = array();
    //        $query = $this->db->query('SELECT * FROM purchase_returned_list WHERE Inv_Pur_ID = "' . $inv_id . '" ');
    //        while ($row = $this->db->assoc($query))
    //            $data[] = $row;
    //        return $data;
    //    }

    public function viewCustomerInvoices($term, $is_actual) {
        $data = array();
        $year_id = SESSION::getCurrentYearID();
        //        $cond = $is_actual ? 'is_actual = 1' : 'is_Actual = 0';
        $data_filter = ["%" . str_replace(' ', '%', $term) . "%"];

        $cond ='';
        $user_id =smc_session()['user_details']['User_ID'];
        if (smc_session()['user_details']['limit_customer_invoice'] == 1) {
            $cond = ' and i.Logged_ID = '.$user_id.'';
        }

        $query = $this->db->prepare_Stmt('SELECT
                                        concat(c.Customer_Name," ",i.Invoice_ID," ",i.Invoice_Date) AS Inv_Info,
                                        concat(c.Customer_Name," ",i.Invoice_ID," ",i.Invoice_Date) AS text,
                                        i.Invoice_ID_Auto AS Auto_ID,
                                        i.Invoice_ID
                                    FROM  tbl_customers c
                                    JOIN  tbl_invoices i ON  c.Customer_ID = i.Customer_ID
                                    WHERE 
                                        i.current_year_id = "' . $year_id . '" '.$cond.'
                                        AND concat(c.Customer_Name," ",i.Invoice_ID," ",i.Invoice_Date) like ?
                                        and is_actual = "' . $is_actual . '"
                                    ORDER BY i.Invoice_Date DESC LIMIT 20
                 ', $data_filter);

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

    public function viewSupplierPurchases($term, $is_actual) {
        $data = array();

        $year_id = SESSION::getCurrentYearID();
        $data_filter = ["%" . $term . "%", "%" . $term . "%", "%" . $term . "%"];
        $query = $this->db->prepare_Stmt('SELECT
                                        concat(c.Supplier_Name," ",i.Purchase_ID," ",i.Purchase_Date) AS Inv_Info,
                                        concat(c.Supplier_Name," ",i.Purchase_ID," ",i.Purchase_Date) AS text,
                                        i.Purchase_ID_Auto AS Auto_ID,
                                        i.Purchase_ID AS Invoice_ID
                                    FROM    
                                        tbl_stock_suppliers c
                                    JOIN 
                                        tbl_inventory_purchases i
                                    ON 
                                        c.Supplier_ID = i.Supplier_ID
                                    WHERE 
                                        i.current_year_id = "' . $year_id . '"
                                    AND 
                                        (c.Supplier_Name LIKE ?
                                    OR
                                        i.Purchase_Date LIKE ?
                                    OR
                                        i.Purchase_ID LIKE ?
                                        )
                                        and is_actual = "' . $is_actual . '"
                                    ORDER BY 
                                        i.Purchase_Date DESC
                                    LIMIT 10
                 ', $data_filter);

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

    public function getMaxReturn($prefix) {

        if (!$this->info['Is_Actual']) {
            $yearid = $this->year_id;
        } else {
            $yearid = $this->year_id_actual;
        }
        $query = $this->db->query('SELECT MAX(Return_ID) AS Entry_ID FROM tbl_accounting_return_invoices 
            where current_year_id = "' . $yearid . '" 
                and invoice_prefix = "' . $prefix . '"
                    and Is_Actual = "' . $this->info['Is_Actual'] . '"
                        AND Is_Customer_Invoice = "' . $this->info['Is_Customer_Invoice'] . '"

                    ');
        $row = $this->db->assoc($query);
        if (!empty($row))
            $Invoice = $row['Entry_ID'] + 1;
        else
            $Invoice = 1;

        return $Invoice;
    }

    public function ReturnNoInvoice($return_inv_num = 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']]);
//            }
//        }
        $this->depot->stop_transaction_start_end = true;
        //
        $this->info['Is_Actual'] = isset($this->info['Is_Actual']) ? $this->info['Is_Actual'] : 0;
        $check = CommonFunctions::check_lock_data(['type' => 'return', 'start_date' => $this->info['Return_Invoice_Date'], 'is_actual' => $this->info['Is_Actual']]);

//        $check = JV::checkCurrentYear($this->info['Return_Invoice_Date'], $this->info['Return_Invoice_Date'], $this->info['Is_Actual']);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $this->info['branch_id'] = isset($this->info['branch_id']) ? $this->info['branch_id'] : CommonBusiness::getBranchByDepot($this->info['Depot_ID']);
        if (isset($this->info['invoice_prefix'])) {
            $invoice_prefix = $this->info['invoice_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'];
            $invoice_prefix = smc_session()['user_accounts']['serial_prefix'] ? smc_session()['user_accounts']['serial_prefix'] : $branch_prefix;
        }
        $app_user_id = isset($this->info['app_user_id']) ? $this->info['app_user_id'] : 0;
        $invoice_app_id = isset($this->info['invoice_app_id']) ? $this->info['invoice_app_id'] : 0;
        $app_branch_id = isset($this->info['app_branch_id']) ? $this->info['app_branch_id'] : 0;
        $mydate = date("Y-m-d H:i:s");
        if ($this->info['Is_Customer_Invoice'] == 0) {
            $operation = "-";
        } else {
            $this->info['Account_Code'] = isset($this->info['Account_Code']) ? $this->info['Account_Code'] : smc_session()['user_accounts']['sales_account_id'];
            $operation = "+";
        }
        $this->info['Logged_ID'] = isset($this->info['Logged_ID']) ? $this->info['Logged_ID'] : smc_session()['user_logged_id'];
        $this->info['created_logged_id'] = isset($this->info['created_logged_id']) ? $this->info['created_logged_id'] : smc_session()['user_logged_id'];
        $this->info['date_created'] = isset($this->info['date_created']) ? $this->info['date_created'] : $mydate;
        $this->info['date_time'] = isset($this->info['date_time']) ? $this->info['date_time'] : $mydate;
        $inv_num = isset($this->info['Return_ID']) ? $this->info['Return_ID'] : self::getMaxReturn($invoice_prefix, $this->info['Is_Actual']);
        $sales_id = (smc_session()['default_setting']['sales_man_as_logged_user'] == 1 && empty($this->info['Sales_ID']) && !empty(smc_session()['user_details']['Employee_ID'])) ? smc_session()['user_details']['Employee_ID'] : (!empty($this->info['Sales_ID']) ? $this->info['Sales_ID'] : null);
        $this->info['salesman_id'] = $sales_id;
        if (!$this->stop_transaction_start_end)
            $this->db->query('START TRANSACTION');
        $this->info[0] = Session::getUserID();
        $inv_id = (isset($this->info['Invoice_Purchase_ID']) && $this->info['Invoice_Purchase_ID'] != '') ? $this->info['Invoice_Purchase_ID'] : NULL;
        $rate = !empty($this->info['Invoice_Currency_Rate']) ? $this->info['Invoice_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']);

        $this->info['invoice_nb'] = $invoice_prefix . $inv_num;
        $this->info['prefix'] = $invoice_prefix;
        $this->info['main_nb'] = $inv_num;
        $this->info['Cash_Amount'] = !empty($this->info['Cash_Amount']) ? $this->info['Cash_Amount'] : 0;
        $this->info['Check_Amount'] = !empty($this->info['Check_Amount']) ? $this->info['Check_Amount'] : 0;
        $this->info['Card_Amount'] = !empty($this->info['Card_Amount']) ? $this->info['Card_Amount'] : 0;
        $paid = $this->info['Card_Amount'] + $this->info['Cash_Amount'] + $this->info['Check_Amount'];
        $this->info['Return_Invoice_Note'] = empty($this->info['Return_Invoice_Note']) ? '' : $this->info['Return_Invoice_Note'];
        $this->info['customer_branch_id'] = empty($this->info['customer_branch_id']) ? NULL : $this->info['customer_branch_id'];
        $this->info['vat1_total'] = !empty($this->info['vat1_total']) ? $this->info['vat1_total'] : $this->info['Invoice_VAT'];
        $this->info['vat2_total'] = !empty($this->info['vat2_total']) ? $this->info['vat2_total'] : 0;
        if(empty($this->info['is_noqty'])){
            $this->info['is_noqty']=0;
        }
        
        $data = [
            &$inv_id,
            &$inv_num,
            &$this->info['Return_Invoice_Total'],
            &$this->info[($this->info['Is_Customer_Invoice'] == 0 ? 'Purchase_Net_Total' : 'Invoice_Net_Total')],
            &$paid,
            &$this->info['discount_amount'],
            &$this->info['discount_percent'],
            &$this->info['Return_Invoice_Note'],
            &$this->info['Return_Invoice_Date'],
            &$this->info['Invoice_VAT'],
            &$this->info['vat1_total'],
            &$this->info['vat2_total'],
            &$this->info['Is_Customer_Invoice'],
            &$this->info['Currency_ID'],
            &$this->info['Person_ID'],
            &$this->info['date_time'],
            &$this->info['date_created'],
            &$this->info['Logged_ID'],
            &$this->info['created_logged_id'],
            &$this->info['Depot_ID'],
            &$app_user_id,
            &$invoice_app_id,
            &$invoice_prefix,
            &$rate,
            &$second_rate,
            &$this->year_id,
            &$this->info['branch_id'],
            &$this->info['Class_ID'],
            &$sales_id,
            &$this->info['Account_Code'],
            &$this->info['Is_Actual'],
            &$this->info['job_id'],
            &$this->info['project_id'],
            &$this->info['tax_amount'], &$app_branch_id,
            &$this->info['customer_branch_id'], &$this->info['is_noqty']
        ];
        $this->db->prepare_Stmt("
                                    INSERT
                                    INTO
                                        tbl_accounting_return_invoices
                                    (" . (!empty($this->info['fill_auto_id']) ? 'Return_Invoice_ID,' : '') . "
                                        Invoice_Purchase_ID,
                                        Return_ID,
                                        Return_Invoice_Total,
                                        Return_Net_Total,
                                        Return_Paid_Total,
                                        discount_amount,
                                        discount_percent,
                                        Return_Invoice_Note,
                                        Return_Invoice_Date,
                                        Invoice_VAT,vat1_total,vat2_total,
                                        Is_Customer_Invoice,
                                        Currency_ID,
                                        Person_ID,
                                        date_time,
                                        date_created,
                                        Logged_ID,created_logged_id,
                                        Depot_ID,
                                        app_user_id,
                                        invoice_app_id,
                                        invoice_prefix,
                                        current_rate,second_currency_rate,
                                        current_year_id,
                                        branch_id,
                                        Class_ID,
                                        Sales_ID,
                                        Account_ID,
                                        Is_Actual,job_id,project_id,tax_amount,app_branch_id,customer_branch_id,
                                        disable_qty
                                    )
                                        VALUES
                                    (
                                        " . (!empty($this->info['fill_auto_id']) ? intval($this->info['fill_auto_id']) . ',' : '') . "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?
                                    )
                                ", $data);


        $return_id = $this->db->id();
        $arr_return = ['type' => 'ret', 'main_id' => $return_id, 'action' => 0,
            'date_time' => $mydate, 'user_id' => smc_session()['user_logged_id']];
        CommonFunctions::add_user_log($arr_return);
        $detail_ids = array();
        $prod_arr = array();
        $prod_ids_arr = array();
        $expiry_valid_arr = array();
        $serial_valid_arr = array();
        $lot_valid_arr = array();
        $qty_valid_arr = array();
        $depot_arr = array($this->info['Depot_ID']);
        $damaged_array = array();
        foreach ($this->info['Products'] as $items) {
            $items["vat2_percent"] = !empty($items["vat2_percent"]) ? $items["vat2_percent"] : 0;
            $items["vat2_amount"] = !empty($items["vat2_amount"]) ? $items["vat2_amount"] : 0;
            $item_desc = isset($items['product_description']) ? $items['product_description'] : '';
            $is_damaged = empty($items['is_damaged']) ? 0 : 1;
            $prod_ids_arr[] = $items['Product_ID'];
//            $quantity = Product::getConvertedQuantity($items['UOM_ID'], $items['Return_Product_Quantity'], $items['Product_ID']);
            $ProductQuantityRate = Product::getConvertedQuantity($items['UOM_ID'], 1, $items['Product_ID']);
            $quantity = $ProductQuantityRate * $items['Return_Product_Quantity'];
            $prod_arr[] = array("product_id" => $items['Product_ID'],
                "total_amount" => ($items['Return_Product_Total']),
                "description" => $item_desc, "vat_amount" => ($items['vat_val'] + $items['vat2_amount']));
            $expiry_date = !empty($items['expiry_date']) ? $items['expiry_date'] : null;
            $lot_nb = !empty($items['lot_nb']) ? $items['lot_nb'] : '';
            $serial_nb = !empty($items['serial_nb']) ? $items['serial_nb'] : '';
            $is_service = !empty($items['is_service']) ? $items['is_service'] : 0;

            $unit_cost = 0;
            if (empty($this->info['is_noqty'])) {
                if ($this->info['Is_Customer_Invoice'] == 0) {

                    $costdata = [
                        "Product_ID" => $items['Product_ID'],
                        "Quantity" => $quantity,
                        "Unit_Cost" => $items['Unit_Cost'],
                        "unit_price" => $items['Unit_Cost'],
                        "Currency_ID" => $this->info['Currency_ID'],
                        "date_time" => $this->info['date_created'],
                        "date_time_now" => $this->info['date_time'],
                        "current_rate" => $rate,
                        "depot_id" => $this->info['Depot_ID'],
                        "trans_date" => $this->info['Return_Invoice_Date'],
                        "current_quantity" => $items['Return_Product_Quantity'],
                        "uom_id" => $items['UOM_ID'],
                        "is_actual" => $this->info['Is_Actual'],
                        "expiry_date" => $expiry_date,
                        "lot_nb" => $lot_nb,
                        "serial_nb" => $serial_nb,
                        "main_id" => $return_id,
                        "is_service" => $is_service,
                        "main_nb" => $this->info['invoice_nb'],
                        "supplier_id" => $this->info['Person_ID']
                    ];
                    $cost = CommonBusiness::updateLinkQuantities($costdata, 'add_return_purchase', '-');
                    $unit_cost = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies(smc_session()['local_currency'], $this->info['Currency_ID'], $cost);

                    if (!empty($expiry_date) && !empty(smc_session()['default_setting']['activate_expiry_qty_exist'])) {
                        $expiry_valid_arr['expiry'][] = $expiry_date;
                        if (!isset($expiry_valid_arr['prod'][$items['Product_ID']][$this->info['Depot_ID']][$expiry_date]))
                            $expiry_valid_arr['prod'][$items['Product_ID']][$this->info['Depot_ID']][$expiry_date] = 0;
                        $expiry_valid_arr['prod'][$items['Product_ID']][$this->info['Depot_ID']][$expiry_date]+= $quantity;
                    }
                    if (!empty($serial_nb) && !empty(smc_session()['default_setting']['activate_serial_qty_exist'])) {
                        $serial_valid_arr['serial'][] = $serial_nb;
                        if (!isset($serial_valid_arr['prod'][$items['Product_ID']][$this->info['Depot_ID']][$serial_nb]))
                            $serial_valid_arr['prod'][$items['Product_ID']][$this->info['Depot_ID']][$serial_nb] = 0;
                        $serial_valid_arr['prod'][$items['Product_ID']][$this->info['Depot_ID']][$serial_nb]+= $quantity;
                    }
                    if (!empty($lot_nb) && !empty(smc_session()['default_setting']['activate_lot_qty_exist'])) {
                        $lot_valid_arr['lot'][] = $lot_nb;
                        if (!isset($lot_valid_arr['prod'][$items['Product_ID']][$this->info['Depot_ID']][$lot_nb]))
                            $lot_valid_arr['prod'][$items['Product_ID']][$this->info['Depot_ID']][$lot_nb] = 0;
                        $lot_valid_arr['prod'][$items['Product_ID']][$this->info['Depot_ID']][$lot_nb]+= $quantity;
                    }
                    if (!empty($quantity) && empty(smc_session()['default_setting']['negative_sales'])) {
                        if (!isset($qty_valid_arr[$items['Product_ID']][$this->info['Depot_ID']]))
                            $qty_valid_arr[$items['Product_ID']][$this->info['Depot_ID']] = 0;
                        $qty_valid_arr[$items['Product_ID']][$this->info['Depot_ID']]+= $quantity;
                    }
                } else {
                    //               
                    $costdata = [
                        "Product_ID" => $items['Product_ID'],
                        "Quantity" => $quantity,
                        "Unit_Cost" => '',
                        "unit_price" => $items['Unit_Cost'],
                        "Currency_ID" => $this->info['Currency_ID'],
                        "date_time" => $this->info['date_created'],
                        "date_time_now" => $this->info['date_time'],
                        "current_rate" => $rate,
                        "depot_id" => $this->info['Depot_ID'],
                        "trans_date" => $this->info['Return_Invoice_Date'],
                        "current_quantity" => $items['Return_Product_Quantity'],
                        "uom_id" => $items['UOM_ID'],
                        "is_actual" => $this->info['Is_Actual'],
                        "expiry_date" => $expiry_date,
                        "lot_nb" => $lot_nb,
                        "serial_nb" => $serial_nb,
                        "main_id" => $return_id,
                        "is_service" => $is_service,
                        "main_nb" => $this->info['invoice_nb'],
                        "customer_id" => $this->info['Person_ID']
                    ];
                    $product_links = CommonBusiness::get_product_links($items['Product_ID'], $items['UOM_ID'], $quantity, $items['Return_Product_Quantity'], $operation);

                    $unit_cost = 0;
                    foreach ($product_links as $link) {

                        $costdata["Quantity"] = $link['Quantity'];
                        $costdata["current_quantity"] = $link['current_quantity'];
                        $costdata["Product_ID"] = $link['Product_ID'];
                        $costdata["uom_id"] = $link['uom_id'];
                        $costdata['is_inverse'] = $link['is_oprtn_inverse'];
                        $tmp_cst_qty = 0;
                        if ($ProductQuantityRate != 0 && $items["Return_Product_Quantity"] != 0)
                            $tmp_cst_qty = ($link['Quantity'] / ($ProductQuantityRate * $items["Return_Product_Quantity"]));
                        $cost = CommonBusiness::updateLinkQuantities($costdata, 'add_return_invoice', $link['operation']) * $tmp_cst_qty;
                        //                    echo $cost.='==';
                        $unit_cost += CurrencyFactory::returnExchangedValueBetweenTwoCurrencies(smc_session()['local_currency'], $this->info['Currency_ID'], $cost);
                    }
//                $operation='-';
//                $cost = CommonBusiness::addCostByType($costdata, "add_return_invoice");
//                $unit_cost = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies(smc_session()['local_currency'], $this->info['Currency_ID'], $cost);
                }
            }
            //               echo"<pre>";die(print_r($product_links));


            $details = [
                &$return_id,
                &$items['Product_ID'],
                &$items['Item_Discount'],
                &$items['vat_percent'],
                &$items['vat_val'],
                &$items['vat2_percent'],
                &$items['vat2_amount'],
                &$quantity,
                &$items['Return_Product_Total'],
                &$items['Return_Product_Quantity'],
                &$items['Unit_Cost'],
                &$unit_cost,
                &$item_desc,
                &$items['purchase_detail_id'],
                &$items['UOM_ID'],
                &$expiry_date, &$lot_nb, &$serial_nb,
                &$is_damaged
            ];

            //            echo"<pre>";die(print_r($details));

            $this->db->prepare_Stmt("
                                        INSERT
                                        INTO
                                            tbl_accounting_return_items
                                        (
                                            Return_Invoice_ID,
                                            Product_ID,
                                            Product_Discount,
                                            vat_percent,
                                            vat_amount,vat2_percent,vat2_amount,
                                            Return_Product_Quantity,
                                            Return_Product_Total,
                                            Item_Quantity_Current,
                                            unit_price,
                                            unit_cost,
                                            product_description,
                                            return_detail_id,
                                            UOM_ID,
                                            expiry_date,lot_nb,serial_nb,is_damaged
                                        )
                                            VALUES
                                        (
                                            ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?
                                        )
                                    ", $details);
            $detail_id = $this->db->id();
            if ($is_damaged == 1) {
                $damaged_array[] = [
                    'UOM_ID' => $items['UOM_ID'],
                    'quantity' => $items['Return_Product_Quantity'],
                    'Product_ID' => $items['Product_ID'],
                    'expiry_date' => $expiry_date,
                    'lot_nb' => $lot_nb,
                    'serial_nb' => $serial_nb,
                    'return_detail_id' => $detail_id,
                    'is_service' => $is_service];
            }

            if (isset($items['invoice_detail_line']))
                $detail_ids[$items['invoice_detail_line']] = $detail_id;
        }
        if (!empty($damaged_array)) {
            $damaged_data = [
                'damaged_date' => $this->info['Return_Invoice_Date'],
                'return_id' => $return_id,
                'Depot_ID' => $this->info['Depot_ID'],
                'is_actual' => $this->info['Is_Actual'],
                'customer_id' => $this->info['Person_ID'],
                'logged_id' => smc_session()['user_logged_id'],
                'created_logged_id' => smc_session()['user_logged_id'],
                'date_time' => $this->info['date_time'],
                'date_created' => $this->info['date_time'],
                'note' => 'Automatic damage from return',
                'Products' => $damaged_array];
            $this->depot->setData($damaged_data)->add_damage();
        }

        if (!empty($expiry_valid_arr)) {
            $expiry_valid = CommonBusiness::validate_expiry_qty_exist($expiry_valid_arr, $depot_arr);
            if (!empty($expiry_valid)) {
                if (!$this->stop_transaction_start_end)
                    $this->db->query('ROLLBACK');
                return ['expiry_error', $expiry_valid];
            }
        }
        if (!empty($lot_valid_arr)) {
            $lot_valid = CommonBusiness::validate_lot_qty_exist($lot_valid_arr, $depot_arr);
            if (!empty($lot_valid)) {
                if (!$this->stop_transaction_start_end)
                    $this->db->query('ROLLBACK');
                return ['lot_error', $lot_valid];
            }
        }
        if (!empty($serial_valid_arr)) {
            $serial_valid = CommonBusiness::validate_serial_qty_exist($serial_valid_arr, $depot_arr);
            if (!empty($serial_valid)) {
                if (!$this->stop_transaction_start_end)
                    $this->db->query('ROLLBACK');
                return ['serial_error', $serial_valid];
            }
        }
        if (!empty($qty_valid_arr)) {
            $qty_valid = CommonBusiness::validate_product_qty_exist($qty_valid_arr, $depot_arr);
            if (!empty($qty_valid)) {
                if (!$this->stop_transaction_start_end)
                    $this->db->query('ROLLBACK');
                return ['qty_error', $qty_valid];
            }
        }
        $prod_str =implode( ',',$prod_ids_arr);
        $pr_trans_type = ($this->info['Is_Customer_Invoice'] == 0) ? 'rpr' : 'rin';
        $prod_trans_arr = CommonBusiness::get_item_acc_transactions($prod_arr, $prod_str, $pr_trans_type, $this->info['Account_Code'], $this->info['discount_percent'], null, 0, $this->info['tax_amount']);

        self::returnInvoiceTransactions($return_id, false, $prod_trans_arr);
        if (!empty($inv_id)) {
            if ($this->info['Is_Customer_Invoice'] == 1) {
                $this->db->prepare_Stmt('update tbl_invoices set Return_Invoice_Total = Return_Invoice_Total + ?,
                    return_paid_amount=return_paid_amount + ?,return_ids = if(return_ids="","' . $return_id . '",concat(return_ids,",","' . $return_id . '"))
                where Invoice_ID_Auto = ?', [$this->info['Invoice_Net_Total'], $paid, $inv_id]);
            } else {
                $this->db->prepare_Stmt('update tbl_inventory_purchases set Return_Purchase_Total = Return_Purchase_Total + ?,
                    return_paid_amount=return_paid_amount + ?,return_ids = if(return_ids="","' . $return_id . '",concat(return_ids,",","' . $return_id . '"))
                where Purchase_ID_Auto = ?', [$this->info['Purchase_Net_Total'], $paid, $inv_id]);
            }
        }
        $error = $this->db->error();
        if (empty($error)) {
            if (!$this->stop_transaction_start_end)
                $this->db->query("COMMIT");
            token_unique::remove_token();
            return $return_inv_num ? [$return_id, '', $detail_ids] : $return_id;
        } else {
            if (!$this->stop_transaction_start_end)
                $this->db->query("ROLLBACK");

            return array_unique($error);
        }
    }

    //    public function ReturnNoInvoice() {
    //        $check = JV::checkCurrentYear($this->info['Return_Invoice_Date'], $this->info['Return_Invoice_Date'], 0);
    //        if ($check !== '') {
    //            $error = array();
    //            $error[0] = $check;
    //            return $error;
    //        }
    //        $app_user_id = isset($this->info['app_user_id']) ? $this->info['app_user_id'] : 0;
    //        $invoice_app_id = isset($this->info['invoice_app_id']) ? $this->info['invoice_app_id'] : 0;
    //        $invoice_prefix = isset($this->info['invoice_prefix']) ? $this->info['invoice_prefix'] : smc_session()['user_accounts']['serial_prefix'];
    //
    //        $paid = $this->info['Card_Amount'] + $this->info['Cash_Amount'] + $this->info['Check_Amount'];
    //        $rem = $this->info['Return_Invoice_Total'] - $paid;
    //
    //
    //        $mydate = date("Y-m-d h:i:s a");
    //        $amount_vat = $this->info['Invoice_VAT'];
    //        if ($this->info['Is_Customer_Invoice'] == 0) {
    //            $account_debit = $this->info['Person_Account'];
    //            $debit_amount = $this->info['Purchase_Net_Total'];
    //            $payment_type = "Debit";
    //            $operation = "-";
    //            $s_account = JV::getAccountCodeByID($account_debit);
    //            $vat_account = Supplier::returnSuppVATAccount($s_account)['Account_ID'];
    //            $account_credit = $this->info['Account_Code'];
    //            $credit_amount = $rem;
    //            $tax_type = "Credit";
    //        } else {
    //            $this->info['Account_Code'] = isset($this->info['Account_Code']) ? $this->info['Account_Code'] : smc_session()['Sales_Account'];
    //            $account_debit = $this->info['Account_Code'];
    //            $account_credit = $this->info['Person_Account'];
    //            $c_account = JV::getAccountCodeByID($account_credit);
    //            $vat_account = Customer::returnCustomerVATAccount($c_account)['Account_ID'];
    //            $credit_amount = $this->info['Invoice_Net_Total'];
    //            $operation = "+";
    //            $debit_amount = $rem;
    //            $payment_type = "Credit";
    //            $tax_type = "Debit";
    //        }
    //        $inv_num = isset($this->info['Return_ID']) ? $this->info['Return_ID'] : self::getMaxReturn();
    //        $this->db->query('START TRANSACTION');
    //        $this->info[0] = Session::getUserID();
    ////$this->info['Return_Invoice_Note']='';
    //        $inv_id = $this->info['Invoice_Purchase_ID'] != '' ? $this->info['Invoice_Purchase_ID'] : NULL;
    //        $rate = isset($this->info['Invoice_Currency_Rate']) ? $this->info['Invoice_Currency_Rate'] : CurrencyFactory::getLastExRate($this->info['Currency_ID']);
    //        $data = [
    //            &$inv_id,
    //            &$inv_num,
    //            &$this->info['Return_Invoice_Total'],
    //            &$this->info[($this->info['Is_Customer_Invoice'] == 0 ? 'Purchase_Net_Total' : 'Invoice_Net_Total')],
    //            &$this->info['discount_amount'],
    //            &$this->info['discount_percent'],
    //            &$this->info['Return_Invoice_Note'],
    //            &$this->info['Return_Invoice_Date'],
    //            &$this->info['Invoice_VAT'],
    //            &$this->info['Is_Customer_Invoice'],
    //            &$this->info['Currency_ID'],
    //            &$this->info['Person_ID'],
    //            &$mydate,
    //            &$this->info[0],
    //            &$this->info['Depot_ID'],
    //            &$app_user_id,
    //            &$invoice_app_id,
    //            &$invoice_prefix,
    //            &$rate,
    //            &$this->year_id
    //        ];
    //        $this->db->prepare_Stmt("
    //                                    INSERT
    //                                    INTO
    //                                        tbl_accounting_return_invoices
    //                                    (
    //                                        Invoice_Purchase_ID,
    //                                        Return_ID,
    //                                        Return_Invoice_Total,
    //                                        Return_Net_Total,
    //                                        discount_amount,
    //                                        discount_percent,
    //                                        Return_Invoice_Note,
    //                                        Return_Invoice_Date,
    //                                        Invoice_VAT,
    //                                        Is_Customer_Invoice,
    //                                        Currency_ID,
    //                                        Person_ID,
    //                                        date_time,
    //                                        Logged_ID,
    //                                        Depot_ID,
    //                                        app_user_id,
    //                                        invoice_app_id,
    //                                        invoice_prefix,
    //                                        current_rate,
    //                                        current_year_id
    //                                    )
    //                                        VALUES
    //                                    (
    //                                        ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?
    //                                    )
    //                                ", $data);
    //
    //
    //        $return_id = $this->db->id();
    //        $main_type = $this->info['Is_Customer_Invoice'] ? 'rin' : 'rpr';
    //
    //        foreach ($this->info['Products'] as $items) {
    //            $quantity = Product::getConvertedQuantity($items['UOM_ID'], $items['Return_Product_Quantity'], $items['Product_ID']);
    //            $costdata = [
    //                "Product_ID" => $items['Product_ID'],
    //                "Quantity" => $quantity,
    //                "Unit_Cost" => $items['Unit_Cost'],
    //                "Currency_ID" => $this->info['Currency_ID'],
    //                "date_time" => $mydate,
    //                "current_rate" => $rate,
    //                "depot_id" => $this->info['Depot_ID'],
    //                "trans_date" => $this->info['Return_Invoice_Date'],
    //                "current_quantity" => $items['Return_Product_Quantity'],
    //                "uom_id" => $items['UOM_ID']
    //            ];
    //            $unit_cost = 0;
    //
    //            if ($this->info['Is_Customer_Invoice'] == 0) {
    //
    //                $costdata["Unit_Cost"] = $items['Unit_Cost'];
    //                $cost = self::updateReturnLinkQuantities($costdata, "add_return_purchase", "-");
    //            } else {
    //                $costdata["Unit_Cost"] = '';
    //                $link_query = $this->db->query('select * from tbl_products_link where main_product_id = "' . $items['Product_ID'] . '"');
    //                $count_row = $this->db->rowsno($link_query);
    //                if ($count_row == 0) {
    //                    $cost = self::updateReturnLinkQuantities($costdata, "add_return_invoice", "+");
    //                    $unit_cost = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies(smc_session()['local_currency'], $this->info['Currency_ID'], $cost);
    //                } else {
    //                    while ($link = $this->db->assoc($link_query)) {
    //                        $link_qty = $link['quantity'] * $quantity;
    //                        $costdata["Quantity"] = $link_qty;
    //                        $costdata["current_quantity"] = $link['current_quantity'] * $quantity;
    //                        $costdata["Product_ID"] = $link['sub_product_id'];
    //                        $costdata["uom_id"] = $link['UOM_ID'];
    //                        $cost = self::updateReturnLinkQuantities($costdata, "add_return_invoice", ($link['operation'] == '+' ? '-' : '+'));
    //                        $unit_cost += CurrencyFactory::returnExchangedValueBetweenTwoCurrencies(smc_session()['local_currency'], $this->info['Currency_ID'], $cost);
    //                    }
    //                }
    //            }
    //
    //            $details = [
    //                &$return_id,
    //                &$items['Product_ID'],
    //                &$items['Item_Discount'],
    //                &$items['vat_percent'],
    //                &$items['vat_val'],
    //                &$quantity,
    //                &$items['Return_Product_Total'],
    //                &$items['Return_Product_Quantity'],
    //                &$items['Unit_Cost'],
    //                &$unit_cost,
    //                &$items['UOM_ID']
    //            ];
    //
    //            $this->db->prepare_Stmt("
    //                                        INSERT
    //                                        INTO
    //                                            tbl_accounting_return_items
    //                                        (
    //                                            Return_Invoice_ID,
    //                                            Product_ID,
    //                                            Product_Discount,
    //                                            vat_percent,
    //                                            vat_amount,
    //                                            Return_Product_Quantity,
    //                                            Return_Product_Total,
    //                                            Item_Quantity_Current,
    //                                            unit_price,
    //                                            unit_cost,
    //                                            UOM_ID
    //                                        )
    //                                            VALUES
    //                                        (
    //                                            ?,?,?,?,?,?,?,?,?,?,?
    //                                        )
    //                                    ", $details);
    //
    //
    //
    ////$error = $this->db->error();
    ////        echo"<pre>";
    ////        die(print_r($details));
    //        }
    //
    //
    //        $jv = JV::returnJV();
    //        $type_2 = "Credit";
    //        $type_1 = "Debit";
    //        $ref = "Ret." . $return_id;
    //
    //
    //        $dat_1 = [
    //            "JV_ID" => $jv,
    //            "Transaction_Reference" => ($this->info['Account_Code'] == $account_debit ? 'ret' : $ref),
    //            "Transaction_Account" => $account_debit,
    //            "Transaction_Type" => $type_1,
    //            "Transaction_Amount" => $debit_amount,
    //            "Transaction_Date" => $this->info['Return_Invoice_Date'],
    //            "Currency_ID" => $this->info['Currency_ID'],
    //            "Logged_ID" => $this->info[0],
    //            "Transaction_Description" => $this->info['Return_Invoice_Note'],
    //            "Current_Ex_Rate" => "0",
    //            "main_id" => $return_id,
    //            "main_type" => $main_type
    //        ];
    //        JV::addTransaction($dat_1);
    ////        echo"<pre>";
    ////        die(print_r($dat_1));
    //
    //        $dat_2 = [
    //            "JV_ID" => $jv,
    //            "Transaction_Reference" => ($this->info['Account_Code'] == $account_credit ? 'ret' : $ref),
    //            "Transaction_Account" => $account_credit,
    //            "Transaction_Type" => $type_2,
    //            "Transaction_Amount" => $credit_amount,
    //            "Transaction_Date" => $this->info['Return_Invoice_Date'],
    //            "Currency_ID" => $this->info['Currency_ID'],
    //            "Logged_ID" => $this->info[0],
    //            "Transaction_Description" => $this->info['Return_Invoice_Note'],
    //            "Current_Ex_Rate" => "0",
    //            "main_id" => $return_id,
    //            "main_type" => $main_type
    //        ];
    //        JV::addTransaction($dat_2);
    //
    //        $dat_6 = array(
    //            "JV_ID" => $jv,
    //            "Transaction_Reference" => $ref,
    //            "Transaction_Account" => $vat_account,
    //            "Transaction_Type" => $tax_type,
    //            "Transaction_Amount" => $amount_vat,
    //            "Transaction_Date" => $this->info['Return_Invoice_Date'],
    //            "Currency_ID" => $this->info['Currency_ID'],
    //            "Logged_ID" => $this->info[0],
    //            "Transaction_Description" => $this->info['Return_Invoice_Note'],
    //            "Current_Ex_Rate" => "0",
    //            "main_id" => $return_id,
    //            "main_type" => $main_type
    //        );
    //        JV::addTransaction($dat_6);
    //
    //        $dat_3 = [
    //            "JV_ID" => $jv,
    //            "Transaction_Reference" => 'credit',
    //            "Transaction_Account" => $this->info['Card_Account'],
    //            "Transaction_Type" => $payment_type,
    //            "Transaction_Amount" => $this->info['Card_Amount'],
    //            "Transaction_Date" => $this->info['Return_Invoice_Date'],
    //            "Currency_ID" => $this->info['Currency_ID'],
    //            "Logged_ID" => $this->info[0],
    //            "Transaction_Description" => $this->info['Return_Invoice_Note'],
    //            "Current_Ex_Rate" => "0",
    //            "main_id" => $return_id,
    //            "main_type" => $main_type
    //        ];
    //        JV::addTransaction($dat_3);
    //
    //        $dat_4 = [
    //            "JV_ID" => $jv,
    //            "Transaction_Reference" => 'cash',
    //            "Transaction_Account" => $this->info['Cash_Account'],
    //            "Transaction_Type" => $payment_type,
    //            "Transaction_Amount" => $this->info['Cash_Amount'],
    //            "Transaction_Date" => $this->info['Return_Invoice_Date'],
    //            "Currency_ID" => $this->info['Currency_ID'],
    //            "Logged_ID" => $this->info[0],
    //            "Transaction_Description" => $this->info['Return_Invoice_Note'],
    //            "Current_Ex_Rate" => "0",
    //            "main_id" => $return_id,
    //            "main_type" => $main_type
    //        ];
    //        JV::addTransaction($dat_4);
    //
    //        $dat_5 = ["JV_ID" => $jv,
    //            "Transaction_Reference" => 'check',
    //            "Transaction_Account" => $this->info['Check_Account'],
    //            "Transaction_Type" => $payment_type,
    //            "Transaction_Amount" => $this->info['Check_Amount'],
    //            "Transaction_Date" => $this->info['Return_Invoice_Date'],
    //            "Currency_ID" => $this->info['Currency_ID'],
    //            "Logged_ID" => $this->info[0],
    //            "Transaction_Description" => $this->info['Return_Invoice_Note'],
    //            "Current_Ex_Rate" => "0",
    //            "main_id" => $return_id,
    //            "main_type" => $main_type
    //        ];
    //        JV::addTransaction($dat_5);
    //
    //
    //        $error = $this->db->error();
    //        if (empty($error)) {
    //            $this->db->query("COMMIT");
    //            return $return_id;
    //        } else {
    //            $this->db->query("ROLLBACK");
    //
    //            return array_unique($error);
    //        }
    //    }

    public function getReturnByID($id, $is_sales = null) {
        $data = array();
        $year = SESSION::getCurrentYearID();
        $query_main = $this->db->query('select 
                                            r.current_year_id,
                                            r.Return_Invoice_ID,
                                            r.invoice_prefix,
                                            r.Return_ID,
                                            r.Invoice_Purchase_ID,
                                            r.Return_Invoice_Date AS Date,
                                            r.Return_Invoice_Total AS Net_Total,
                                            r.Return_Net_Total as net,
                                            r.Invoice_VAT,
                                            r.Currency_ID,
                                            c.Currency_Symbol,
                                            c.Currency_Name,c.currency_round,
                                            r.Depot_ID,
                                            r.Person_ID,
                                            r.discount_amount,
                                            r.discount_percent as Discount,
                                            r.Is_Customer_Invoice,
                                            r.is_main_transfer,
                                            r.current_rate,
                                            r.Class_ID,
                                            pc.Class_Name,
                                            r.Sales_ID,
                                            concat(e.Employee_FirstName," ",e.Employee_LastName) as Employee_Name,
                                            r.Account_ID,
                                            r.Is_Actual,pc.is_ttc,
                                            coa.Account_Code,coa.Account_Name,r.job_id,j.job_name,
                                            r.tax_amount,pc.tax_amount as class_tax,
                                            r.Return_Invoice_Note,
                                            r.date_created,
                                            r.Return_Paid_Total,w.Depot_Name,r.project_id,pr.project_name,
                                            r.customer_branch_id,r.second_currency_rate,
                                            cust_branch.customer_branch_name,
											(r.Invoice_VAT*r.current_rate) as local_vat_amount
                                        from tbl_accounting_return_invoices r
                                        join tbl_chart_of_accounts coa on coa.Account_ID = r.Account_ID
                                        join tbl_common_currency c on c.Currency_ID = r.Currency_ID
                                        left join tbl_pricing_class pc on pc.Class_ID = r.Class_ID 
                                        left join tbl_hrm_employee e on e.Employee_ID = r.Sales_ID
                                        left join tbl_accounting_jobs j on j.job_id = r.job_id
                                        left join tbl_projects pr on pr.project_id = r.project_id
                                        left join tbl_customer_branches cust_branch on cust_branch.customer_branch_id = r.customer_branch_id
                                        join tbl_stock_depot_warehouse w on w.Depot_ID = r.Depot_ID
                                        where Return_Invoice_ID = "' . smc_escape($id) . '" and current_year_id = "' . $this->year_id . '"
                                        ' . (is_null($is_sales) ? '' : " and r.Is_Customer_Invoice = '" . ($is_sales ? '1' : '0') . "' ") . '
                                            ');
        $row = $this->db->assoc($query_main);
        if (empty($row))
            return $data;
        $row['shipping'] = 0;
        $row['delivery_fees_view'] = '';
        $row['postponed_note'] = '';
        $row['postponed_date_time'] = '';
        $row['order_note'] = '';
        $row['posponted_type'] = 0;
        $row['Is_Delivery'] = 0;
        $row['Delivery_Time'] = '';
//        $row['Sale_ID'] = 0;
//        $row['Employee_Name'] = '';
        $row['Customer_ID'] = $row['Person_ID'];
        $row['formatted_net_total'] = CommonFunctions::formatNumber($row['Net_Total']);
        $row['formatted_invoice_vat'] = CommonFunctions::formatNumber($row['Invoice_VAT']);
        $row['formatted_net'] = CommonFunctions::formatNumber($row['net']);
        $row['discount_amount'] = CommonFunctions::formatNumber($row['discount_amount']);
        $row['Discount'] = CommonFunctions::formatNumber($row['Discount']);
        $row['net_total_in_usd'] = CommonFunctions::formatNumber(CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($row['Currency_ID'], 144, $row['net']));
        $row['net_total_in_lb'] = CommonFunctions::formatNumber(CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($row['Currency_ID'], 84, $row['net']));
        $row['local_vat_amount'] = CommonFunctions::formatNumber($row['local_vat_amount']);
        $row['grand_total'] = CommonFunctions::formatNumber($row['net']);
        $row['grand_total_word_en'] = Forms::num_to_word($row['grand_total'], $row['Currency_ID'], 'en');
        $data['Main'] = $row;

        if ($data['Main']['Is_Customer_Invoice']) {
            $data['Person'] = Customer::getCustomerByID($data['Main']['Person_ID']);
            if ($row['Person_ID'] == 0)
                $data['Main']['current_balance'] = $data['Main']['previous_balance'] = $data['Main']['now_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
                $temp_current_balance = $report->returnCustomerExactBalance($row['Person_ID'], $row["Currency_ID"], '2000-01-01 00:00:00', $row["date_created"]/* date('Y-m-d') */, 0, $row['Is_Actual']);
                $data['Main']['current_balance'] = CommonFunctions::formatNumber($temp_current_balance['Balance']);
                $data['Main']['previous_balance'] = CommonFunctions::formatNumber(($temp_current_balance['Balance'] + ($row['net'] - $row['Return_Paid_Total'])));
            }
            $customer = new Customer;
            $total = Forms::DecimalNumber($customer->return_customer_all_balances($row['Person_ID'])[$this->permission->hasPermission('show_actual_balance') ? 'balance' : 'off_balance']);
            $symbol = CurrencyFactory::getDefaultCurrencySymbol();
            $data['Main']['now_balance'] = $total . ' ' . $symbol;
        } else {
            $data['Person'] = Supplier::getSupplierByID($data['Main']['Person_ID']);
            $data['Main']['current_balance'] = $data['Main']['previous_balance'] = $data['Main']['now_balance'] = '---';
        }
//        $tmp_data = array();
//        $rem_data = array();
//        if (!empty($data['Main']['Invoice_Purchase_ID'])) {
//
//            $query_ret = $this->db->query('select Product_ID, UOM_ID, sum(Item_Quantity_Current) as Item_Quantity 
//                from tbl_accounting_return_items join tbl_accounting_return_invoices
//                using(Return_Invoice_ID) where Invoice_Purchase_ID = "' . $data['Main']['Invoice_Purchase_ID'] . '"  
//                and Is_Customer_Invoice=1 group by Product_ID, UOM_ID');
//            while ($ret_row = $this->db->assoc($query_ret))
//                $tmp_data[$ret_row['Product_ID']][$ret_row['UOM_ID']] = $ret_row['Item_Quantity'];
//
//            $query_inv = $this->db->query('SELECT id.`Entery_ID` as invoice_entry, id.`Invoice_ID_Auto`,
//            id.`Product_ID` as invoice_product_id, id.`Product_Discount` as invoice_product_discount,
//            id.`Product_Net_Price` as invoice_unit_price, id.`Product_Quantity` as invoice_quantity,
//            id.`Product_Total_Price` as invoice_total_price, id.`Item_Quantity_Current` as invoice_current_quantity,
//            id.`UOM_ID` as invoice_uom_id, id.`Discount_Amount` as invoice_discount_amount,
//            id.`vat_percent` as invoice_vat_percent, id.`vat_amount` as invoice_vat_amount,
//            id.`vat2_percent` as invoice_vat2_percent, id.`vat2_amount` as invoice_vat2_amount,
//            p.*,id.product_description as invoice_item_desc,
//            id.`Item_Quantity_Current`/id.`Product_Quantity` as uom_conv,
//            u.UOM_Name,id.expiry_date,p.is_expiry,id.serial_nb,id.lot_nb,p.is_lot,p.is_serial
//            FROM 
//                `tbl_invoice_products_details` id 
//            join tbl_stock_products p on p.Product_ID = id.Product_ID 
//            join tbl_stock_uom u on u.UOM_ID = id.UOM_ID
//            where id.`Invoice_ID_Auto` = "' . $data['Main']['Invoice_Purchase_ID'] . '"  
//                 ');
//            while ($inv_row = $this->db->assoc($query_inv)) {
//                $inv_row['remaining_quantity'] = $inv_row['invoice_current_quantity'];
//                $prod_id = $inv_row['invoice_product_id'];
//                $uom_id = $inv_row['invoice_uom_id'];
//                if (isset($tmp_data[$prod_id][$uom_id])) {
//                    if ($tmp_data[$prod_id][$uom_id] > $inv_row['invoice_current_quantity']) {
//                        $inv_row['remaining_quantity'] = 0;
//                        $tmp_data[$prod_id][$uom_id] -= $inv_row['invoice_current_quantity'];
//                    } else {
//                        $inv_row['remaining_quantity'] -= $tmp_data[$prod_id][$uom_id];
//                        $tmp_data[$prod_id][$uom_id] = 0;
//                    }
//                }
//                $rem_data[$inv_row['invoice_product_id']][$inv_row['invoice_uom_id']] = $inv_row['remaining_quantity'];
//            }
//        }
//        echo"<pre>";print_r($rem_data);
//        print_r($tmp_data);
//        die();
        //
        $query_details = $this->db->query('select
                                                i.Return_Item_ID as Entery_ID,
                                                p.Return_Invoice_ID AS Invoice_ID_Auto,
                                                i.Item_Quantity_Current AS Item_Quantity_Current,
                                                ps.Product_Code AS Code,
                                                ps.Product_Name AS Name,
                                                ps.Product_VAT,
                                                ps.is_tax,ps.is_lot,ps.is_serial,ps.is_service,
                                                ps.duration_reservation,
                                                ps.force_change_price,
                                                ps.Product_Limit_Discount,
                                                ps.Category_ID,
                                                if(i.vat_percent <> 0,1,0) VAT,
                                                i.vat_percent,
                                                i.vat_amount,i.vat2_percent,i.vat2_amount,
                                                i.is_damaged,
                                                ps.Product_Quantity,
                                                i.Product_ID AS Product_ID,
                                                i.Product_Discount AS Product_Discount,
                                                i.product_description,
                                                i.unit_price AS Product_Net_Price,
                                                i.Return_Product_Total AS Product_Total_Price,
                                                (i.Return_Product_Quantity/i.Item_Quantity_Current) AS uom_con,
                                                 i.UOM_ID,i.unit_cost,
                                                 uc.Bar_Code,i.expiry_date,ps.is_expiry,
                                                GROUP_CONCAT(uom_from separator "/") AS Product_UOM,
                                                ps.Status_ID,i.lot_nb,i.serial_nb,
                                                (( if(isnull(l.Product_Depot_Quantity),0,l.Product_Depot_Quantity) /uc.conversion_amount)+ i.Item_Quantity_Current) as Product_Quantity,
                                                (( if(isnull(l.Product_Depot_Quantity),0,l.Product_Depot_Quantity)/uc.conversion_amount)+i.Item_Quantity_Current) AS Quantity
                                            from 
                                                tbl_accounting_return_items i 
                                            left join 
                                                tbl_accounting_return_invoices p 
                                            on
                                                p.Return_Invoice_ID = i.Return_Invoice_ID
                                            LEFT JOIN
                                                uom_conversions uc
                                            ON 
                                                uc.Product_ID = i.Product_ID
                                            left join 
                                                tbl_stock_products ps 
                                            on
                                                ps.Product_ID = i.Product_ID
                                            left  join
                                                tbl_stock_product_location l
                                            on 
                                                l.Location_Product_ID = i.Product_ID 
                                            and 
                                                l.Product_Depot_ID = p.Depot_ID
                                            where
                                                p.current_year_id = "' . $year . '"
                                            and 
                                                p.Return_Invoice_ID = "' . $id . '"
                                            GROUP BY i.Return_Item_ID
                                            order by p.Return_Invoice_ID

                                            ');
        $i = 0;
        while ($row = $this->db->assoc($query_details)) {
            $row['damage_class'] = empty($row['is_damaged']) ? 'hidden' : '';
            $row['unit_tax'] = $data['Main']['class_tax'];
            $row['ttc_price'] = $row['Product_Net_Price'] * (1 + ($row['vat_percent'] / 100));
            $row['ttc_price'] = $row['ttc_price'] * (1 + ($row['vat2_percent'] / 100));
            $i++;
            $row['reserve_from'] = '';
            $row['reserve_to'] = '';
            $row['count_nb'] = $i;
            $row['total_price_before_vat_disc'] = $row['Item_Quantity_Current'] * $row['Product_Net_Price'];
            $row['total_price_before_vat_disc'] = CommonFunctions::formatNumber($row['total_price_before_vat_disc']);
            
            $row['Product_Discount'] = CommonFunctions::formatNumber($row['Product_Discount']);
            $row['current_product_net_price'] = CommonFunctions::formatNumber($row['Product_Net_Price']);
            $row['formatted_vat_total_price'] = CommonFunctions::formatNumber($row['vat_amount'] + $row['Product_Total_Price']);
//            $row['remaining_quantity'] = !empty($data['Main']['Invoice_Purchase_ID']) && !empty($rem_data[$row['Product_ID']][$row['UOM_ID']]) ? $rem_data[$row['Product_ID']][$row['UOM_ID']] : 0;
            $data['Details'][] = $row;
        }




        $query = $this->db->query("SELECT 
                                            t.Transaction_Amount,
                                            t.Transaction_Reference,
                                            t.Account_ID,
                                            p.Account_Name AS P_Name,
                                            t.check_nb,t.due_date,t.value_date
                                        FROM 
                                            tbl_accounting_transactions t
                                        LEFT JOIN 
                                            tbl_chart_of_accounts p
                                        ON 
                                            t.Account_ID = p.Account_ID
                                       WHERE 
                                             t.main_id = '" . $id . "'
                                        AND 
                                            t.main_type = '" . ($data['Main']['Is_Customer_Invoice'] ? 'rin' : 'rpr') . "'
                                        AND 
                                            t.Transaction_Reference in ('cash','check','credit')
                                        ");
        $data['Payment'] = array();
        while ($row = $this->db->assoc($query)) {
            if (stripos($row['Transaction_Reference'], 'cash') !== false)
                $data['Payment']['Cash'] = $row;
            if (stripos($row['Transaction_Reference'], 'check') !== false)
                $data['Payment']['Cheque'] = $row;
            if (stripos($row['Transaction_Reference'], 'credit') !== false)
                $data['Payment']['Credit_Card'] = $row;
        }
        //                echo '<pre>';
        //                print_r($data);
        //                die('kk');
        return $data;
    }

    public function returnInvoiceTransactions($return_id, $is_modify, $prod_trans_arr) {
        $flag = $this->info['Is_Actual'];
        if ($is_modify) {
            $jv_query = $this->db->query("SELECT DISTINCT JV_ID,is_actual FROM tbl_accounting_transactions  WHERE main_id='" . $return_id . "' AND main_type= '" . ($this->info['Is_Customer_Invoice'] ? 'rin' : 'rpr') . "'");
            $jv_assoc = $this->db->assoc($jv_query);
            if (!empty($jv_assoc) && $jv_assoc['is_actual'] == $this->info['Is_Actual'])
                $jv = $jv_assoc['JV_ID'];
            else
                $jv = JV::returnJV($flag);
            $this->db->prepare_Stmt("DELETE FROM tbl_accounting_transactions  WHERE main_id= ? AND main_type= '" . ($this->info['Is_Customer_Invoice'] ? 'rin' : 'rpr') . "'", [&$this->info['Return_ID']]);
        } else
            $jv = JV::returnJV($flag);
        //        $person_account_code = JV::getAccountCodeByID($this->info['Person_Account']);
        $vat_account = $this->info['Is_Customer_Invoice'] == 0 ? Supplier::returnSuppVATAccount($this->info['Person_ID'])['Account_ID'] : Customer::returnCustomerVATAccount($this->info['Person_ID'])['Account_ID'];
        //        $rate = CurrencyFactory::getLastExRate($this->info['Currency_ID']);
        $rate = !empty($this->info['Invoice_Currency_Rate']) ? $this->info['Invoice_Currency_Rate'] : CurrencyFactory::getLastExRate($this->info['Currency_ID']);
        $second_rate = CurrencyFactory::getSecondCurrencyLastExRate($this->info['Currency_ID']);

        $paid = $this->info['Card_Amount'] + $this->info['Cash_Amount'] + $this->info['Check_Amount'];
        $net_total = ($this->info['Is_Customer_Invoice'] == 0 ? $this->info['Purchase_Net_Total'] : $this->info['Invoice_Net_Total']);
        $net_total_without_vat = $net_total - $this->info['Invoice_VAT'];
        $main_type = $this->info['Is_Customer_Invoice'] ? 'rin' : 'rpr';
        $ref = "Ret." . $this->info['invoice_nb'];
        $this->info['Return_Invoice_Note'] = empty($this->info['Return_Invoice_Note']) ? $ref : $this->info['Return_Invoice_Note'];
        $main_ref = 'ret';
        $this->info['salesman_id'] = !empty($this->info['salesman_id']) ? $this->info['salesman_id'] : 0;
        $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 ($prod_trans_arr as $trans) {
            //            $trans_desc = $trans[]
            if (isset($trans['main'])) {
                $trans_main = $trans['main'];
                $data_trans = ["JV_ID" => $jv,
                    "Transaction_Reference" => $main_ref,
                    "Transaction_Account" => $trans_main['trans_account'],
                    "Transaction_Type" => ($this->info['Is_Customer_Invoice'] == 0 ? 'Credit' : 'Debit'),
                    "Transaction_Amount" => $trans_main['trans_amount'],
                    "Transaction_Date" => $this->info['Return_Invoice_Date'],
                    "Currency_ID" => $this->info['Currency_ID'],
                    "Logged_ID" => $this->info['Logged_ID'],
                    "created_logged_id" => $this->info['created_logged_id'],
                    "date_time" => $this->info['date_time'],
                    "date_created" => $this->info['date_created'],
                    "Transaction_Description" => (empty($trans_main['trans_desc']) ? $this->info['Return_Invoice_Note'] : $trans_main['trans_desc']),
                    "Current_Ex_Rate" => $rate,
                    "second_currency_rate" => $second_rate,
                    "main_id" => $return_id,
                    "main_type" => $main_type,
                    "branch_id" => $this->info['branch_id'],
                    "subtype" => $main_ref,
                    "main_nb" => $this->info['main_nb'],
                    "prefix" => $this->info['prefix'],
                    "job_id" => $opposite_job,
                    "project_id" => $this->info['project_id'],
                    "salesman_id" => $this->info['salesman_id']
                ];
                JV::addTransaction($data_trans, $flag);
            }


            if (isset($trans['person'])) {
                $trans_person = $trans['person'];

                $data_trans = ["JV_ID" => $jv,
                    "Transaction_Reference" => $ref,
                    "Transaction_Account" => $this->info['Person_Account'],
                    "Transaction_Type" => ($this->info['Is_Customer_Invoice'] == 0 ? 'Debit' : 'Credit'),
                    "Transaction_Amount" => $trans_person['trans_amount'],
                    "Transaction_Date" => $this->info['Return_Invoice_Date'],
                    "Currency_ID" => $this->info['Currency_ID'],
                    "Logged_ID" => $this->info['Logged_ID'],
                    "created_logged_id" => $this->info['created_logged_id'],
                    "date_time" => $this->info['date_time'],
                    "date_created" => $this->info['date_created'],
                    "Transaction_Description" => (empty($trans_person['trans_desc']) ? $this->info['Return_Invoice_Note'] : $trans_person['trans_desc']) . (empty($this->info['customer_branch_name']) ? '' : (' - ' . $this->info['customer_branch_name'])),
                    "Current_Ex_Rate" => $rate,
                    "second_currency_rate" => $second_rate,
                    "main_id" => $return_id,
                    "main_type" => $main_type,
                    "branch_id" => $this->info['branch_id'],
                    "main_nb" => $this->info['main_nb'],
                    "prefix" => $this->info['prefix'],
                    "job_id" => $person_job,
                    "project_id" => $this->info['project_id'],
                    "salesman_id" => $this->info['salesman_id']
                ];
                JV::addTransaction($data_trans, $flag);
                //                 $error = $this->db->error();
                //            die(print_r($error));
            }
        }

        //vat transaction
        $dat_7 = array(
            "JV_ID" => $jv,
            "Transaction_Reference" => $ref,
            "Transaction_Account" => ($this->info['Is_Customer_Invoice'] == 1 ? (!empty(smc_session()['account_setting']['customer_fixed_vat_acc_id']) ? smc_session()['account_setting']['customer_fixed_vat_acc_id'] : $vat_account) : $vat_account),
            "Transaction_Type" => ($this->info['Is_Customer_Invoice'] == 0 ? 'Credit' : 'Debit'),
            "Transaction_Amount" => $this->info['vat1_total'],
            "Transaction_Date" => $this->info['Return_Invoice_Date'],
            "Currency_ID" => $this->info['Currency_ID'],
            "Logged_ID" => $this->info['Logged_ID'],
            "created_logged_id" => $this->info['created_logged_id'],
            "date_time" => $this->info['date_time'],
            "date_created" => $this->info['date_created'],
            "Transaction_Description" => $this->info['Return_Invoice_Note'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $return_id,
            "main_type" => $main_type,
            "branch_id" => $this->info['branch_id'],
            "main_nb" => $this->info['main_nb'],
            "prefix" => $this->info['prefix'],
            "job_id" => $opposite_job,
            "project_id" => $this->info['project_id'],
            "salesman_id" => $this->info['salesman_id'],
            "subtype" => "vat1"
        );
        JV::addTransaction($dat_7, $flag);
        $dat_7 = array(
            "JV_ID" => $jv,
            "Transaction_Reference" => $ref,
            "Transaction_Account" => ($this->info['Is_Customer_Invoice'] == 1 ? (!empty(smc_session()['account_setting']['customer_fixed_vat_acc_id']) ? smc_session()['account_setting']['customer_fixed_vat_acc_id'] : $vat_account) : $vat_account),
            "Transaction_Type" => ($this->info['Is_Customer_Invoice'] == 0 ? 'Credit' : 'Debit'),
            "Transaction_Amount" => $this->info['vat2_total'],
            "Transaction_Date" => $this->info['Return_Invoice_Date'],
            "Currency_ID" => $this->info['Currency_ID'],
            "Logged_ID" => $this->info['Logged_ID'],
            "created_logged_id" => $this->info['created_logged_id'],
            "date_time" => $this->info['date_time'],
            "date_created" => $this->info['date_created'],
            "Transaction_Description" => $this->info['Return_Invoice_Note'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $return_id,
            "main_type" => $main_type,
            "branch_id" => $this->info['branch_id'],
            "main_nb" => $this->info['main_nb'],
            "prefix" => $this->info['prefix'],
            "job_id" => $opposite_job,
            "project_id" => $this->info['project_id'],
            "salesman_id" => $this->info['salesman_id'],
            "subtype" => "vat2"
        );
        JV::addTransaction($dat_7, $flag);
        //paid amount on customer/supplier transaction
        $dat_3 = [
            "JV_ID" => $jv,
            "Transaction_Reference" => $ref,
            "Transaction_Account" => $this->info['Person_Account'],
            "Transaction_Type" => ($this->info['Is_Customer_Invoice'] == 0 ? 'Credit' : 'Debit'),
            "Transaction_Amount" => $paid,
            "Transaction_Date" => $this->info['Return_Invoice_Date'],
            "Currency_ID" => $this->info['Currency_ID'],
            "Logged_ID" => $this->info['Logged_ID'],
            "created_logged_id" => $this->info['created_logged_id'],
            "date_time" => $this->info['date_time'],
            "date_created" => $this->info['date_created'],
            "Transaction_Description" => $this->info['Return_Invoice_Note'] . (empty($this->info['customer_branch_name']) ? '' : (empty($this->info['Return_Invoice_Note']) ? '' : (' - ' . $this->info['customer_branch_name']))),
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $return_id,
            "main_type" => $main_type,
            "branch_id" => $this->info['branch_id'],
            "main_nb" => $this->info['main_nb'],
            "prefix" => $this->info['prefix'],
            "job_id" => $person_job,
            "project_id" => $this->info['project_id'],
            "salesman_id" => $this->info['salesman_id']
        ];
        JV::addTransaction($dat_3, $flag);
        //credit card transaction
        $dat_4 = [
            "JV_ID" => $jv,
            "Transaction_Reference" => 'credit',
            "Transaction_Account" => $this->info['Card_Account'],
            "Transaction_Type" => ($this->info['Is_Customer_Invoice'] == 0 ? 'Debit' : 'Credit'),
            "Transaction_Amount" => $this->info['Card_Amount'],
            "Transaction_Date" => $this->info['Return_Invoice_Date'],
            "Currency_ID" => $this->info['Currency_ID'],
            "Logged_ID" => $this->info['Logged_ID'],
            "created_logged_id" => $this->info['created_logged_id'],
            "date_time" => $this->info['date_time'],
            "date_created" => $this->info['date_created'],
            "Transaction_Description" => $this->info['Return_Invoice_Note'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $return_id,
            "main_type" => $main_type,
            "branch_id" => $this->info['branch_id'],
            "subtype" => 'credit',
            "main_nb" => $this->info['main_nb'],
            "prefix" => $this->info['prefix'],
            "job_id" => $opposite_job,
            "project_id" => $this->info['project_id'],
            "salesman_id" => $this->info['salesman_id']
        ];
        JV::addTransaction($dat_4, $flag);
        //cash transaction
        $dat_5 = [
            "JV_ID" => $jv,
            "Transaction_Reference" => 'cash',
            "Transaction_Account" => $this->info['Cash_Account'],
            "Transaction_Type" => ($this->info['Is_Customer_Invoice'] == 0 ? 'Debit' : 'Credit'),
            "Transaction_Amount" => $this->info['Cash_Amount'],
            "Transaction_Date" => $this->info['Return_Invoice_Date'],
            "Currency_ID" => $this->info['Currency_ID'],
            "Logged_ID" => $this->info['Logged_ID'],
            "created_logged_id" => $this->info['created_logged_id'],
            "date_time" => $this->info['date_time'],
            "date_created" => $this->info['date_created'],
            "Transaction_Description" => $this->info['Return_Invoice_Note'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $return_id,
            "main_type" => $main_type,
            "branch_id" => $this->info['branch_id'],
            "subtype" => 'cash',
            "main_nb" => $this->info['main_nb'],
            "prefix" => $this->info['prefix'],
            "job_id" => $opposite_job,
            "project_id" => $this->info['project_id'],
            "salesman_id" => $this->info['salesman_id']
        ];
        JV::addTransaction($dat_5, $flag);

        $dat_6 = ["JV_ID" => $jv,
            "Transaction_Reference" => 'check',
            "Transaction_Account" => $this->info['Check_Account'],
            "Transaction_Type" => ($this->info['Is_Customer_Invoice'] == 0 ? 'Debit' : 'Credit'),
            "Transaction_Amount" => $this->info['Check_Amount'],
            "Transaction_Date" => $this->info['Return_Invoice_Date'],
            "Currency_ID" => $this->info['Currency_ID'],
            "Logged_ID" => $this->info['Logged_ID'],
            "created_logged_id" => $this->info['created_logged_id'],
            "date_time" => $this->info['date_time'],
            "date_created" => $this->info['date_created'],
            "Transaction_Description" => $this->info['Return_Invoice_Note'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $return_id,
            "main_type" => $main_type,
            "branch_id" => $this->info['branch_id'],
            "due_date" => (!empty($this->info['check_due_date']) ? $this->info['check_due_date'] : ""),
            "check_nb" => (empty($this->info['Check_Num']) ? '' : $this->info['Check_Num']),
            "value_date" => (empty($this->info['value_date']) ? '' : $this->info['value_date']),
            "subtype" => 'check',
            "main_nb" => $this->info['main_nb'],
            "prefix" => $this->info['prefix'],
            "job_id" => $opposite_job,
            "project_id" => $this->info['project_id'],
            "salesman_id" => $this->info['salesman_id']
        ];
        JV::addTransaction($dat_6, $flag);
    }

    public function editReturnNoInvoice($return_inv_num = false) {
        if (empty($this->info['Return_Invoice_Date'])) {
            $query = $this->db->query("SELECT Return_Invoice_Date
                from tbl_accounting_return_invoices WHERE Return_Invoice_ID = '" . $this->info['Return_ID'] . "'");
            $row_main = $this->db->assoc($query);
            $this->info['Return_Invoice_Date'] = $row_main['Return_Invoice_Date'];
        }
        $check = CommonFunctions::check_lock_data(['type' => 'return', 'start_date' => $this->info['Return_Invoice_Date'], 'is_actual' => $this->info['Is_Actual']]);

//        $check = JV::checkCurrentYear($this->info['Return_Invoice_Date'], $this->info['Return_Invoice_Date'], 0);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $this->info['Logged_ID'] = Session::getUserID();
        $rate = !empty($this->info['Invoice_Currency_Rate']) ? $this->info['Invoice_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']);

        //        $rate = CurrencyFactory::getLastExRate($this->info['Currency_ID']);

        if ($this->info['Is_Customer_Invoice'] == 0) {
            $operation = "-";
            $edit_op = "+";
        } else {
            $operation = "+";
            $edit_op = "-";
        }
        $sales_id = (smc_session()['default_setting']['sales_man_as_logged_user'] == 1 && empty($this->info['Sales_ID']) && !empty(smc_session()['user_details']['Employee_ID'])) ? smc_session()['user_details']['Employee_ID'] : (!empty($this->info['Sales_ID']) ? $this->info['Sales_ID'] : null);
        $this->info['salesman_id'] = $sales_id;
        if (!$this->stop_transaction_start_end)
            $this->db->query('START TRANSACTION');
        //        $this->info['Logged_ID'] = isset($this->info['Logged_ID']) ? $this->info['Logged_ID'] : Session::getUserID();


        $mydate = date("Y-m-d H:i:s");
        $old_date_time = $mydate;
        $this->info['date_created'] = $mydate;
        $this->info['date_time'] = isset($this->info['date_time']) ? $this->info['date_time'] : $mydate;
        $this->info['created_logged_id'] = $this->info['Logged_ID'];
        $old_return_nb = '';
        $old_net_total = 0;
        $old_paid_amount = 0;
        $old_inv_pur_id = 0;
        
        $query = $this->db->query("select date_time,Invoice_Purchase_ID,Return_Net_Total,Return_Paid_Total,
                invoice_prefix,Return_ID from tbl_accounting_return_invoices 
                where Return_Invoice_ID = '" . $this->info['Return_ID'] . "'");
        
        $row_old  = $this->db->assoc($query);

        $old_inv_pur_id =  $row_old['Invoice_Purchase_ID']== null?'':$row_old['Invoice_Purchase_ID'];
        $old_net_total = $row_old['Return_Net_Total'];
        $old_paid_amount = $row_old['Return_Paid_Total'];
        $old_date_time = $row_old['date_time'];
        $old_return_nb = $row_old['invoice_prefix'] . $row_old['Return_ID'];

       
        if (empty($this->info['is_noqty'])) {

            $query = $this->db->query("SELECT 
                                        p.Product_ID as Item_ID,
                                        p.Return_Product_Quantity AS Quantity,
                                        p.Item_Quantity_Current AS Quantity_Current,
                                        p.UOM_ID,
                                        p.unit_price,
                                        p.unit_cost,
                                        i.Currency_ID,
                                        i.Depot_ID,i.Is_Actual,
                                        i.date_time,p.lot_nb,p.serial_nb,pr.is_service,p.is_damaged,
                                        i.current_rate,i.date_created,i.created_logged_id,p.expiry_date,i.Return_Invoice_Date,
                                        i.invoice_prefix,i.Return_ID,i.Person_ID,
                                        i.Return_Net_Total,i.Return_Paid_Total,i.Invoice_Purchase_ID
                                    FROM tbl_accounting_return_items p
                                    JOIN tbl_accounting_return_invoices i USING(Return_Invoice_ID)
                                    join tbl_stock_products pr on pr.Product_ID = p.Product_ID
                                    WHERE 
                                        p.Return_Invoice_ID = '" . $this->info['Return_ID'] . "'
                ");

            while ($row = $this->db->assoc($query)) {

                $this->info['date_created'] = $row['date_created'];
                $this->info['created_logged_id'] = $row['created_logged_id'];
                $old_expiry_date = !empty($row['expiry_date']) ? $row['expiry_date'] : null;
                $old_lot_nb = !empty($row['lot_nb']) ? $row['lot_nb'] : '';
                $old_serial_nb = !empty($row['serial_nb']) ? $row['serial_nb'] : '';


                $costdata = [
                    "Product_ID" => $row['Item_ID'],
                    "Quantity" => $row['Quantity'],
                    "Unit_Cost" => $row[($this->info['Is_Customer_Invoice'] == 0 ? 'unit_price' : 'unit_cost')],
                    "unit_price" => $row['unit_price'],
                    "Currency_ID" => $row['Currency_ID'],
                    "date_time" => $row['date_time'],
                    "date_time_now" => $this->info['date_time'],
                    "current_rate" => $row['current_rate'],
                    "depot_id" => $row['Depot_ID'],
                    "trans_date" => $this->info['Return_Invoice_Date'],
                    "current_quantity" => $row['Quantity_Current'],
                    "uom_id" => $row['UOM_ID'],
                    "is_actual" => $row['Is_Actual'],
                    "expiry_date" => $old_expiry_date,
                    "lot_nb" => $old_lot_nb,
                    "serial_nb" => $old_serial_nb,
                    "main_id" => $this->info['Return_ID'],
                    "is_return_damage" => $row['is_damaged'],
                    "is_service" => $row['is_service'],
                    "main_nb" => $old_return_nb,
                    "supplier_id" => ($this->info['Is_Customer_Invoice'] == 0 ? $row['Person_ID'] : null),
                    "customer_id" => ($this->info['Is_Customer_Invoice'] == 1 ? $row['Person_ID'] : null),
                ];
                if ($this->info['Is_Customer_Invoice'] == 1) {
                    $product_links = CommonBusiness::get_product_links($row['Item_ID'], $row['UOM_ID'], $row['Quantity'], $row['Quantity_Current'], '+');
                    foreach ($product_links as $link) {
                        $costdata["Quantity"] = $link['Quantity'];
                        $costdata["current_quantity"] = $link['current_quantity'];
                        $costdata["Product_ID"] = $link['Product_ID'];
                        $costdata["uom_id"] = $link['uom_id'];
                        $costdata['is_inverse'] = $link['is_oprtn_inverse'];
                        if (!empty($link['is_main_prdct']))
                            $costdata['Unit_Cost'] = $row['unit_cost'];
                        else {
                            $cost = CommonBusiness::viewCurrentProductCost($link['Product_ID']);
                            $costdata['Unit_Cost'] = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies(smc_session()['local_currency'], $this->info['Currency_ID'], $cost);
                        }
                        CommonBusiness::updateLinkQuantities($costdata, "edit_return_invoice_old", ($link['operation'] == '+' ? '-' : '+'));
                    }
                } else {
                    CommonBusiness::updateLinkQuantities($costdata, "edit_return_purchase_old", '+');
                }
            }
        }
        

        if ($this->info['Is_Customer_Invoice'] == 1) {
            $query_damage = $this->db->query('select damage_id from tbl_damage 
                where return_id = "' . $this->info['Return_ID'] . '"');
            $damage_row = $this->db->assoc($query_damage);
            if (!empty($damage_row['damage_id'])) {
                $this->depot->stop_transaction_start_end = true;
                $this->depot->delete_damage($damage_row['damage_id']);
            }
        }
        if (!empty($old_inv_pur_id)) {
            if ($this->info['Is_Customer_Invoice'] == 1) {
                $this->db->prepare_Stmt('update tbl_invoices set Return_Invoice_Total = Return_Invoice_Total - ?,
                    return_paid_amount=return_paid_amount - ?,
                    return_ids = trim("," from replace(concat(",",return_ids,","),concat(",","' . $this->info['Return_ID'] . '",","),","))
                where Invoice_ID_Auto = ?', [$old_net_total, $old_paid_amount, $old_inv_pur_id]);
            } else {
                $this->db->prepare_Stmt('update tbl_inventory_purchases set Return_Purchase_Total = Return_Purchase_Total - ?,
                    return_paid_amount=return_paid_amount - ?,
                    return_ids = trim("," from replace(concat(",",return_ids,","),concat(",","' . $this->info['Return_ID'] . '",","),","))
                where Purchase_ID_Auto = ?', [$old_net_total, $old_paid_amount, $old_inv_pur_id]);
            }
        }

        $this->info['branch_id'] = CommonBusiness::getBranchByDepot($this->info['Depot_ID']);
        $inv_id = !empty($this->info['Invoice_Purchase_ID']) ? $this->info['Invoice_Purchase_ID'] : NULL;
        $paid = $this->info['Card_Amount'] + $this->info['Cash_Amount'] + $this->info['Check_Amount'];
        $this->info['customer_branch_id'] = empty($this->info['customer_branch_id']) ? NULL : $this->info['customer_branch_id'];
        $this->info['vat1_total'] = !empty($this->info['vat1_total']) ? $this->info['vat1_total'] : $this->info['Invoice_VAT'];
        $this->info['vat2_total'] = !empty($this->info['vat2_total']) ? $this->info['vat2_total'] : 0;

        $data = [
            &$this->info['Return_Invoice_Total'],
            &$this->info[($this->info['Is_Customer_Invoice'] == 0 ? 'Purchase_Net_Total' : 'Invoice_Net_Total')],
            &$paid,
            &$this->info['Invoice_VAT'],
            &$this->info['vat1_total'],
            &$this->info['vat2_total'],
            &$this->info['discount_amount'],
            &$this->info['discount_percent'],
            &$this->info['Return_Invoice_Note'],
            &$this->info['Return_Invoice_Date'],
            &$this->info['Currency_ID'],
            &$this->info['Person_ID'],
            &$this->info['Depot_ID'],
            &$rate,
            &$second_rate,
            &$this->info['branch_id'],
            &$this->info['Class_ID'],
            &$sales_id,
            &$this->info['Account_Code'],
            &$this->info['job_id'],
            &$this->info['project_id'],
            &$this->info['tax_amount'],
            &$this->info['Logged_ID'],
            &$this->info['date_time'],
            &$this->info['Is_Actual'],
            &$this->info['customer_branch_id'],
            &$this->info['Return_ID']
        ];
        $this->db->prepare_Stmt("
                                    update tbl_accounting_return_invoices
                                    set
                                        Return_Invoice_Total = ?,
                                        Return_Net_Total = ?,
                                        Return_Paid_Total = ?,
                                        Invoice_VAT = ?,
                                        vat1_total=?,vat2_total=?,
                                        discount_amount = ?,
                                        discount_percent = ?,
                                        Return_Invoice_Note = ?,
                                        Return_Invoice_Date = ?,
                                        Currency_ID = ?,
                                        Person_ID = ?,
                                        Depot_ID = ?,
                                        current_rate = ?,second_currency_rate=?,
                                        branch_id = ?,
                                        Class_ID = ?,
                                        Sales_ID = ?,
                                        Account_ID = ?,
                                        job_id=?,project_id=?,
                                        tax_amount=?,Logged_ID=?,date_time=?,is_main_transfer = 0,Is_Actual = ?,
                                        customer_branch_id = ?
                                    where 
                                        Return_Invoice_ID = ?
                                ", $data);

        $return_id = $this->info['Return_ID'];
        $arr_return = ['type' => 'ret', 'main_id' => $return_id, 'action' => 1,
            'date_time' => $mydate, 'user_id' => smc_session()['user_logged_id']];
        CommonFunctions::add_user_log($arr_return);
        $delete_stock = [&$this->info['Return_ID']];

        $this->db->prepare_Stmt("DELETE FROM tbl_accounting_return_items  WHERE Return_Invoice_ID = ?", $delete_stock);
        $query_inv_num = $this->db->query('select invoice_prefix ,Return_ID
            from tbl_accounting_return_invoices where Return_Invoice_ID = "' . $this->info['Return_ID'] . '" ');
        $inv_num_arr = $this->db->assoc($query_inv_num);
        $this->info['invoice_nb'] = $inv_num_arr['invoice_prefix'] . $inv_num_arr['Return_ID'];
        $this->info['main_nb'] = $inv_num_arr['Return_ID'];
        $this->info['prefix'] = $inv_num_arr['invoice_prefix'];
        $detail_ids = array();
        $prod_arr = array();
        $prod_ids_arr = array();
        $trans_type = $this->info['Is_Customer_Invoice'] == 0 ? 'rpr' : 'rin';
        CommonBusiness::update_trans_record_current($trans_type, $return_id);
        $expiry_valid_arr = array();
        $serial_valid_arr = array();
        $lot_valid_arr = array();
        $qty_valid_arr = array();
        $depot_arr = array($this->info['Depot_ID']);
        $damaged_array = array();
       

        foreach ($this->info['Products'] as $items) {
            //            echo'herettt';
            $prod_ids_arr[] = $items['Product_ID'];
            $item_desc = isset($items['product_description']) ? $items['product_description'] : '';
            $is_damaged = empty($items['is_damaged']) ? 0 : 1;
            $items["vat2_percent"] = !empty($items["vat2_percent"]) ? $items["vat2_percent"] : 0;
            $items["vat2_amount"] = !empty($items["vat2_amount"]) ? $items["vat2_amount"] : 0;
//            $quantity = Product::getConvertedQuantity($items['UOM_ID'], $items['Return_Product_Quantity'], $items['Product_ID']);
            $ProductQuantityRate = Product::getConvertedQuantity($items['UOM_ID'], 1, $items['Product_ID']);
            $quantity = $ProductQuantityRate * $items['Return_Product_Quantity'];
            $prod_arr[] = array("product_id" => $items['Product_ID'],
                "total_amount" => ($items['Return_Product_Total']),
                "description" => $item_desc, "vat_amount" => ($items['vat_val'] + $items['vat2_amount']));
            $expiry_date = !empty($items['expiry_date']) ? $items['expiry_date'] : null;
            $lot_nb = !empty($items['lot_nb']) ? $items['lot_nb'] : '';
            $serial_nb = !empty($items['serial_nb']) ? $items['serial_nb'] : '';
            $is_service = !empty($items['is_service']) ? $items['is_service'] : 0;
            if (empty($this->info['is_noqty'])) {
                if ($this->info['Is_Customer_Invoice'] == 0) {
                    $costdata = [
                        "Product_ID" => $items['Product_ID'],
                        "Quantity" => $quantity,
                        "Unit_Cost" => $items['Unit_Cost'],
                        "unit_price" => $items['Unit_Cost'],
                        "Currency_ID" => $this->info['Currency_ID'],
                        "date_time" => $old_date_time,
                        "date_time_now" => $this->info['date_time'],
                        "current_rate" => $rate,
                        "depot_id" => $this->info['Depot_ID'],
                        "trans_date" => $this->info['Return_Invoice_Date'],
                        "current_quantity" => $items['Return_Product_Quantity'],
                        "uom_id" => $items['UOM_ID'],
                        "is_actual" => $this->info['Is_Actual'],
                        "expiry_date" => $expiry_date,
                        "lot_nb" => $lot_nb,
                        "serial_nb" => $serial_nb,
                        "main_id" => $this->info['Return_ID'],
                        "is_service" => $is_service,
                        "supplier_id" => $this->info['Person_ID'],
                        "main_nb" => $old_return_nb
                    ];
                    $cost = CommonBusiness::updateLinkQuantities($costdata, 'edit_return_purchase_new', '-');
                    $unit_cost = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies(smc_session()['local_currency'], $this->info['Currency_ID'], $cost);
                    if (!empty($expiry_date) && !empty(smc_session()['default_setting']['activate_expiry_qty_exist'])) {
                        $expiry_valid_arr['expiry'][] = $expiry_date;
                        if (!isset($expiry_valid_arr['prod'][$items['Product_ID']][$this->info['Depot_ID']][$expiry_date]))
                            $expiry_valid_arr['prod'][$items['Product_ID']][$this->info['Depot_ID']][$expiry_date] = 0;
                        $expiry_valid_arr['prod'][$items['Product_ID']][$this->info['Depot_ID']][$expiry_date]+= $quantity;
                    }
                    if (!empty($serial_nb) && !empty(smc_session()['default_setting']['activate_serial_qty_exist'])) {
                        $serial_valid_arr['serial'][] = $serial_nb;
                        if (!isset($serial_valid_arr['prod'][$items['Product_ID']][$this->info['Depot_ID']][$serial_nb]))
                            $serial_valid_arr['prod'][$items['Product_ID']][$this->info['Depot_ID']][$serial_nb] = 0;
                        $serial_valid_arr['prod'][$items['Product_ID']][$this->info['Depot_ID']][$serial_nb]+= $quantity;
                    }
                    if (!empty($lot_nb) && !empty(smc_session()['default_setting']['activate_lot_qty_exist'])) {
                        $lot_valid_arr['lot'][] = $lot_nb;
                        if (!isset($lot_valid_arr['prod'][$items['Product_ID']][$this->info['Depot_ID']][$lot_nb]))
                            $lot_valid_arr['prod'][$items['Product_ID']][$this->info['Depot_ID']][$lot_nb] = 0;
                        $lot_valid_arr['prod'][$items['Product_ID']][$this->info['Depot_ID']][$lot_nb]+= $quantity;
                    }
                    if (!empty($quantity) && empty(smc_session()['default_setting']['negative_sales'])) {
                        if (!isset($qty_valid_arr[$items['Product_ID']][$this->info['Depot_ID']]))
                            $qty_valid_arr[$items['Product_ID']][$this->info['Depot_ID']] = 0;
                        $qty_valid_arr[$items['Product_ID']][$this->info['Depot_ID']]+= $quantity;
                    }
//                CommonBusiness::addCostByType($costdata, "edit_return_purchase_new");
//                $unit_cost = 0;
                } else {
                    $costdata = [
                        "Product_ID" => $items['Product_ID'],
                        "Quantity" => $quantity,
                        "Unit_Cost" => '',
                        "unit_price" => $items['Unit_Cost'],
                        "Currency_ID" => $this->info['Currency_ID'],
                        "date_time" => $old_date_time,
                        "date_time_now" => $this->info['date_time'],
                        "current_rate" => $rate,
                        "depot_id" => $this->info['Depot_ID'],
                        "trans_date" => $this->info['Return_Invoice_Date'],
                        "current_quantity" => $items['Return_Product_Quantity'],
                        "uom_id" => $items['UOM_ID'],
                        "is_actual" => $this->info['Is_Actual'],
                        "expiry_date" => $expiry_date,
                        "lot_nb" => $lot_nb,
                        "serial_nb" => $serial_nb,
                        "main_id" => $this->info['Return_ID'],
                        "is_service" => $is_service,
                        "customer_id" => $this->info['Person_ID'],
                        "main_nb" => $old_return_nb
                    ];
//                $cost = CommonBusiness::addCostByType($costdata, "edit_return_invoice_new");
//                $unit_cost = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies(smc_session()['local_currency'], $this->info['Currency_ID'], $cost);
                    $product_links = CommonBusiness::get_product_links($items['Product_ID'], $items['UOM_ID'], $quantity, $items['Return_Product_Quantity'], $operation);
                    $unit_cost = 0;
                    foreach ($product_links as $link) {

                        $costdata["Quantity"] = $link['Quantity'];
                        $costdata["current_quantity"] = $link['current_quantity'];
                        $costdata["Product_ID"] = $link['Product_ID'];
                        $costdata["uom_id"] = $link['uom_id'];
                        $costdata['is_inverse'] = $link['is_oprtn_inverse'];
                        $tmp_cst_qty = 0;
                        if ($ProductQuantityRate != 0 && $items["Return_Product_Quantity"] != 0)
                            $tmp_cst_qty = ($link['Quantity'] / ($ProductQuantityRate * $items["Return_Product_Quantity"]));
                        $cost = CommonBusiness::updateLinkQuantities($costdata, 'edit_return_invoice_new', $link['operation']) * $tmp_cst_qty;
                        //                    echo $cost.='==';
                        $unit_cost += CurrencyFactory::returnExchangedValueBetweenTwoCurrencies(smc_session()['local_currency'], $this->info['Currency_ID'], $cost);
                    }
                }
            }
            if($this->info['is_noqty']){
                if(empty($unit_cost)){
                    $unit_cost=$items['Unit_Cost'];
                }
            }
    
            $details = [
                &$return_id,
                &$items['Product_ID'],
                &$items['vat_percent'],
                &$items['vat_val'],
                &$items['vat2_percent'],
                &$items['vat2_amount'],
                &$items['Item_Discount'],
                &$quantity,
                &$items['Return_Product_Total'],
                &$items['Return_Product_Quantity'],
                &$items['Unit_Cost'],
                &$unit_cost,
                &$item_desc,
                &$items['purchase_detail_id'],
                &$items['UOM_ID'],
                &$expiry_date,
                &$lot_nb, &$serial_nb,
                &$is_damaged
            ];

            $this->db->prepare_Stmt("
                                        INSERT
                                        INTO
                                            tbl_accounting_return_items
                                        (
                                            Return_Invoice_ID,
                                            Product_ID,
                                            vat_percent,
                                            vat_amount,vat2_percent,vat2_amount,
                                            Product_Discount,
                                            Return_Product_Quantity,
                                            Return_Product_Total,
                                            Item_Quantity_Current,
                                            unit_price,
                                            unit_cost,
                                            product_description,
                                            return_detail_id,
                                            UOM_ID,
                                            expiry_date,lot_nb,serial_nb,is_damaged
                                        )
                                            VALUES
                                        (
                                            ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?
                                        )
                                    ", $details);
            $detail_id = $this->db->id();
            if ($is_damaged == 1) {
                $damaged_array[] = [
                    'UOM_ID' => $items['UOM_ID'],
                    'quantity' => $items['Return_Product_Quantity'],
                    'Product_ID' => $items['Product_ID'],
                    'expiry_date' => $expiry_date,
                    'lot_nb' => $lot_nb,
                    'serial_nb' => $serial_nb,
                    'return_detail_id' => $detail_id,
                    'is_service' => $is_service];

//                $this->depot->setData($damaged_array)->addDamagedQuantities();
            }
            //            $error = $this->db->error();
            //            die(print_r($error));
            if (isset($items['invoice_detail_line']))
                $detail_ids[$items['invoice_detail_line']] = $this->db->id();

//         
        }
        
        if (!empty($damaged_array)) {
            $damaged_data = [
                'damaged_date' => $this->info['Return_Invoice_Date'],
                'return_id' => $return_id,
                'Depot_ID' => $this->info['Depot_ID'],
                'is_actual' => $this->info['Is_Actual'],
                'customer_id' => $this->info['Person_ID'],
                'logged_id' => smc_session()['user_logged_id'],
                'created_logged_id' => smc_session()['user_logged_id'],
                'date_time' => $this->info['date_time'],
                'date_created' => $this->info['date_time'],
                'note' => 'Automatic damage from return',
                'Products' => $damaged_array];
            $this->depot->setData($damaged_data)->add_damage();
        }

        if (!empty($expiry_valid_arr)) {
            $expiry_valid = CommonBusiness::validate_expiry_qty_exist($expiry_valid_arr, $depot_arr);
            if (!empty($expiry_valid)) {
                if (!$this->stop_transaction_start_end)
                    $this->db->query('ROLLBACK');
                return ['expiry_error', $expiry_valid];
            }
        }
        if (!empty($lot_valid_arr)) {
            $lot_valid = CommonBusiness::validate_lot_qty_exist($lot_valid_arr, $depot_arr);
            if (!empty($lot_valid)) {
                if (!$this->stop_transaction_start_end)
                    $this->db->query('ROLLBACK');
                return ['lot_error', $lot_valid];
            }
        }
        if (!empty($serial_valid_arr)) {
            $serial_valid = CommonBusiness::validate_serial_qty_exist($serial_valid_arr, $depot_arr);
            if (!empty($serial_valid)) {
                if (!$this->stop_transaction_start_end)
                    $this->db->query('ROLLBACK');
                return ['serial_error', $serial_valid];
            }
        }
        if (!empty($qty_valid_arr)) {
            $qty_valid = CommonBusiness::validate_product_qty_exist($qty_valid_arr, $depot_arr);
            if (!empty($qty_valid)) {
                if (!$this->stop_transaction_start_end)
                    $this->db->query('ROLLBACK');
                return ['qty_error', $qty_valid];
            }
        }
        $prod_str =implode( ',',$prod_ids_arr);
        $pr_trans_type = ($this->info['Is_Customer_Invoice'] == 0) ? 'rpr' : 'rin';
        $prod_trans_arr = CommonBusiness::get_item_acc_transactions($prod_arr, $prod_str, $pr_trans_type, $this->info['Account_Code'], $this->info['discount_percent'], null, 0, $this->info['tax_amount']);

        if (!empty($inv_id)) {
            if ($this->info['Is_Customer_Invoice'] == 1) {
                $this->db->prepare_Stmt('update tbl_invoices set Return_Invoice_Total = Return_Invoice_Total + ?,
                    return_paid_amount=return_paid_amount + ?,return_ids = if(return_ids="","' . $return_id . '",concat(return_ids,",","' . $return_id . '"))
                where Invoice_ID_Auto = ?', [$this->info['Invoice_Net_Total'], $paid, $inv_id]);
            } else {
                $this->db->prepare_Stmt('update tbl_inventory_purchases set Return_Purchase_Total = Return_Purchase_Total + ?,
                    return_paid_amount=return_paid_amount + ?,return_ids = if(return_ids="","' . $return_id . '",concat(return_ids,",","' . $return_id . '"))
                where Purchase_ID_Auto = ?', [$this->info['Purchase_Net_Total'], $paid, $inv_id]);
            }
        }
// echo"<pre>";
//        die(print_r($prod_trans_arr));
        self::returnInvoiceTransactions($this->info['Return_ID'], true, $prod_trans_arr);

        $error = $this->db->error();
        if (empty($error)) {
            if (!$this->stop_transaction_start_end)
                $this->db->query("COMMIT");
            return $return_inv_num ? [$return_id, '', $detail_ids] : $return_id;
        } else {
            if (!$this->stop_transaction_start_end)
                $this->db->query("ROLLBACK");

            return array_unique($error);
        }
    }

    //    public function editReturnNoInvoice() {
    //        $check = JV::checkCurrentYear($this->info['Return_Invoice_Date'], $this->info['Return_Invoice_Date'], 0);
    //        if ($check !== '') {
    //            $error = array();
    //            $error[0] = $check;
    //            return $error;
    //        }
    //
    //
    //        $this->info['Logged_ID'] = Session::getUserID();
    //
    //        $jv_query = $this->db->query("SELECT DISTINCT JV_ID FROM tbl_accounting_transactions  WHERE main_id='" . $this->info['Return_ID'] . "' AND main_type= '" . ($this->info['Is_Customer_Invoice'] ? 'rin' : 'rpr') . "'");
    //        $jv = $this->db->assoc($jv_query)['JV_ID'];
    //        $this->db->prepare_Stmt("DELETE FROM tbl_accounting_transactions  WHERE main_id= ? AND main_type= '" . ($this->info['Is_Customer_Invoice'] ? 'rin' : 'rpr') . "'", [&$this->info['Return_ID']]);
    //
    //
    //        $rate = CurrencyFactory::getLastExRate($this->info['Currency_ID']);
    //        $paid = $this->info['Card_Amount'] + $this->info['Cash_Amount'] + $this->info['Check_Amount'];
    //        $rem = $this->info['Return_Invoice_Total'] - $paid;
    //
    //        if ($this->info['Is_Customer_Invoice'] == 0) {
    //            $account_debit = $this->info['Person_Account'];
    //            $debit_amount = $this->info['Purchase_Net_Total'];
    //            $payment_type = "Debit";
    //            $operation = "-";
    //            $s_account = JV::getAccountCodeByID($account_debit);
    //            $vat_account = Supplier::returnSuppVATAccount($s_account)['Account_ID'];
    //            $account_credit = $this->info['Account_Code'];
    //            $credit_amount = $rem;
    //            $tax_type = "Credit";
    //            $edit_op = "+";
    //            $amount_vat = $this->info['Purchase_Net_Total'] - $this->info['TotalTax'];
    //        } else {
    //            $account_debit = $this->info['Account_Code'];
    //            $account_credit = $this->info['Person_Account'];
    //            $c_account = JV::getAccountCodeByID($account_credit);
    //            $vat_account = Customer::returnCustomerVATAccount($c_account)['Account_ID'];
    //            $credit_amount = $this->info['Invoice_Net_Total'];
    //            $operation = "+";
    //            $debit_amount = $rem;
    //            $payment_type = "Credit";
    //            $tax_type = "Debit";
    //            $edit_op = "-";
    //            $amount_vat = $this->info['Invoice_Net_Total'] - $this->info['TotalTax'];
    //        }
    //
    //        $this->db->query('START TRANSACTION');
    //        $this->info[0] = Session::getUserID();
    //
    //        $mydate = date("Y-m-d h:i:s a");
    //        $old_date_time = $mydate;
    ////     
    //        $inv_id = NULL;
    //        $data = [
    //
    //            &$this->info['Return_Invoice_Total'],
    //            &$this->info[($this->info['Is_Customer_Invoice'] == 0 ? 'Purchase_Net_Total' : 'Invoice_Net_Total')],
    //            &$this->info['Invoice_VAT'],
    //            &$this->info['discount_amount'],
    //            &$this->info['discount_percent'],
    //            &$this->info['Return_Invoice_Note'],
    //            &$this->info['Return_Invoice_Date'],
    //            &$this->info['Currency_ID'],
    //            &$this->info['Person_ID'],
    //            &$this->info['Depot_ID'],
    //            &$rate,
    //            &$this->info['Return_ID']
    //        ];
    //        $this->db->prepare_Stmt("
    //                                    update tbl_accounting_return_invoices
    //                                    set
    //                                        Return_Invoice_Total = ?,
    //                                        Return_Net_Total = ?,
    //                                        Invoice_VAT = ?,
    //                                        discount_amount = ?,
    //                                        discount_percent = ?,
    //                                        Return_Invoice_Note = ?,
    //                                        Return_Invoice_Date = ?,
    //                                        Currency_ID = ?,
    //                                        Person_ID = ?,
    //                                        Depot_ID = ?,
    //                                        current_rate = ?
    //                                    where 
    //                                        Return_Invoice_ID = ?
    //                                ", $data);
    //
    ////        $error = $this->db->error();
    ////        echo"<pre>";
    ////        die(print_r($error));
    //        $return_id = $this->info['Return_ID'];
    //        $main_type = $this->info['Is_Customer_Invoice'] ? 'rin' : 'rpr';
    //        $op = $this->info['Is_Customer_Invoice'] ? '-' : '+';
    //        $query = $this->db->query("SELECT 
    //                                        if(isNull(l.link_id), p.Product_ID, l.sub_product_id) as sub_product_id,
    //                                        if(isNull(l.link_id), p.Item_Quantity_Current, (p.Return_Product_Quantity * l.current_quantity)) as Current_Quantity,
    //                                        if(isNull(l.link_id), p.Return_Product_Quantity, (p.Return_Product_Quantity * l.quantity)) as Quantity,
    //                                        if(isNull(l.link_id), '" . $op . "', l.operation) as operation,
    //                                        if(isNull(l.link_id), p.UOM_ID, l.UOM_ID) as UOM_ID,
    //                                        p.unit_price,
    //                                        p.unit_cost,
    //                                        i.Currency_ID,
    //                                        i.Depot_ID,
    //                                        i.date_time,
    //                                        i.current_rate,
    //                                        if(isNull(l.link_id), 0, 1) as link_id
    //                                    FROM 
    //                                        tbl_accounting_return_items p
    //                                    left JOIN
    //                                        tbl_products_link l
    //                                    on p.Product_ID = l.main_product_id
    //                                    join tbl_accounting_return_invoices i
    //                                    on i.Return_Invoice_ID = p.Return_Invoice_ID
    //                                    WHERE 
    //                                        p.Return_Invoice_ID = '" . $return_id . "'
    //                ");
    //        while ($row = $this->db->assoc($query)) {
    //
    //            $old_date_time = $row['date_time'];
    //            $costdata = [
    //                "Product_ID" => $row['sub_product_id'],
    //                "Quantity" => $row['Quantity'],
    //                "Unit_Cost" => $row['unit_price'],
    //                "Currency_ID" => $row['Currency_ID'],
    //                "date_time" => $row['date_time'],
    //                "current_rate" => $row['current_rate'],
    //                "depot_id" => $row['Depot_ID'],
    //                "trans_date" => $this->info['Return_Invoice_Date'],
    //                "current_quantity" => $row['Current_Quantity'],
    //                "uom_id" => $row['UOM_ID']
    //            ];
    //            if ($this->info['Is_Customer_Invoice'] == 0) {
    //                self::updateReturnLinkQuantities($costdata, "edit_return_purchase_old", "+");
    //            } else {
    //                $sub_cost = $row['link_id'] == 0 ? $row['unit_cost'] : CommonBusiness::viewCurrentProductCost($row['sub_product_id']);
    //                $costdata["Unit_Cost"] = $sub_cost;
    //                self::updateReturnLinkQuantities($costdata, "edit_return_invoice_old", "-");
    //            }
    //        }
    //
    //
    //        $delete_stock = [&$this->info['Return_ID']];
    //        $this->db->prepare_Stmt("DELETE FROM tbl_accounting_return_items  WHERE Return_Invoice_ID = ?", $delete_stock);
    //
    //        foreach ($this->info['Products'] as $items) {
    //            $quantity = Product::getConvertedQuantity($items['UOM_ID'], $items['Return_Product_Quantity'], $items['Product_ID']);
    //            $costdata = [
    //                "Product_ID" => $items['Product_ID'],
    //                "Quantity" => $quantity,
    //                "Unit_Cost" => $items['Unit_Cost'],
    //                "Currency_ID" => $this->info['Currency_ID'],
    //                "date_time" => $mydate,
    //                "current_rate" => $rate,
    //                "depot_id" => $this->info['Depot_ID'],
    //                "trans_date" => $this->info['Return_Invoice_Date'],
    //                "current_quantity" => $items['Return_Product_Quantity'],
    //                "uom_id" => $items['UOM_ID']
    //            ];
    //            $unit_cost = 0;
    //
    //            if ($this->info['Is_Customer_Invoice'] == 0) {
    //
    //                $costdata["Unit_Cost"] = $items['Unit_Cost'];
    //                $cost = self::updateReturnLinkQuantities($costdata, "edit_return_purchase_new", "-");
    //            } else {
    //                $costdata["Unit_Cost"] = '';
    //                $link_query = $this->db->query('select * from tbl_products_link where main_product_id = "' . $items['Product_ID'] . '"');
    //                $count_row = $this->db->rowsno($link_query);
    //                if ($count_row == 0) {
    //                    $cost = self::updateReturnLinkQuantities($costdata, "edit_return_invoice_new", "+");
    //                    $unit_cost = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies(smc_session()['local_currency'], $this->info['Currency_ID'], $cost);
    //                } else {
    //                    while ($link = $this->db->assoc($link_query)) {
    //                        $link_qty = $link['quantity'] * $quantity;
    //                        $costdata["Quantity"] = $link_qty;
    //                        $costdata["current_quantity"] = $link['current_quantity'] * $quantity;
    //                        $costdata["Product_ID"] = $link['sub_product_id'];
    //                        $costdata["uom_id"] = $link['UOM_ID'];
    //                        $cost = self::updateReturnLinkQuantities($costdata, "edit_return_invoice_new", ($link['operation'] == '+' ? '-' : '+'));
    //                        $unit_cost += CurrencyFactory::returnExchangedValueBetweenTwoCurrencies(smc_session()['local_currency'], $this->info['Currency_ID'], $cost);
    //                    }
    //                }
    //            }
    //            $details = [
    //                &$return_id,
    //                &$items['Product_ID'],
    //                &$items['vat_percent'],
    //                &$items['vat_val'],
    //                &$items['Item_Discount'],
    //                &$quantity,
    //                &$items['Return_Product_Total'],
    //                &$items['Return_Product_Quantity'],
    //                &$items['Unit_Cost'],
    //                &$unit_cost,
    //                &$items['UOM_ID']
    //            ];
    //
    //            $this->db->prepare_Stmt("
    //                                        INSERT
    //                                        INTO
    //                                            tbl_accounting_return_items
    //                                        (
    //                                            Return_Invoice_ID,
    //                                            Product_ID,
    //                                            vat_percent,
    //                                            vat_amount,
    //                                            Product_Discount,
    //                                            Return_Product_Quantity,
    //                                            Return_Product_Total,
    //                                            Item_Quantity_Current,
    //                                            unit_price,
    //                                            unit_cost,
    //                                            UOM_ID
    //                                        )
    //                                            VALUES
    //                                        (
    //                                            ?,?,?,?,?,?,?,?,?,?,?
    //                                        )
    //                                    ", $details);
    //
    //
    //
    //
    //
    //// 
    //        }
    //
    //
    //        $jv = JV::returnJV();
    //        $type_2 = "Credit";
    //        $type_1 = "Debit";
    //        $ref = "Ret." . $return_id;
    //
    //
    //        $dat_1 = [
    //            "JV_ID" => $jv,
    //            "Transaction_Reference" => ($this->info['Account_Code'] == $account_debit ? 'ret' : $ref),
    //            "Transaction_Account" => $account_debit,
    //            "Transaction_Type" => $type_1,
    //            "Transaction_Amount" => $debit_amount,
    //            "Transaction_Date" => $this->info['Return_Invoice_Date'],
    //            "Currency_ID" => $this->info['Currency_ID'],
    //            "Logged_ID" => $this->info[0],
    //            "Transaction_Description" => $this->info['Return_Invoice_Note'],
    //            "Current_Ex_Rate" => "0",
    //            "main_id" => $return_id,
    //            "main_type" => $main_type
    //        ];
    //        JV::addTransaction($dat_1);
    //
    //        $dat_2 = [
    //            "JV_ID" => $jv,
    //            "Transaction_Reference" => ($this->info['Account_Code'] == $account_credit ? 'ret' : $ref),
    //            "Transaction_Account" => $account_credit,
    //            "Transaction_Type" => $type_2,
    //            "Transaction_Amount" => $credit_amount,
    //            "Transaction_Date" => $this->info['Return_Invoice_Date'],
    //            "Currency_ID" => $this->info['Currency_ID'],
    //            "Logged_ID" => $this->info[0],
    //            "Transaction_Description" => $this->info['Return_Invoice_Note'],
    //            "Current_Ex_Rate" => "0",
    //            "main_id" => $return_id,
    //            "main_type" => $main_type
    //        ];
    //        JV::addTransaction($dat_2);
    //
    //        $dat_6 = array(
    //            "JV_ID" => $jv,
    //            "Transaction_Reference" => $ref,
    //            "Transaction_Account" => $vat_account,
    //            "Transaction_Type" => $tax_type,
    //            "Transaction_Amount" => $amount_vat,
    //            "Transaction_Date" => $this->info['Return_Invoice_Date'],
    //            "Currency_ID" => $this->info['Currency_ID'],
    //            "Logged_ID" => $this->info[0],
    //            "Transaction_Description" => $this->info['Return_Invoice_Note'],
    //            "Current_Ex_Rate" => "0",
    //            "main_id" => $return_id,
    //            "main_type" => $main_type
    //        );
    //        JV::addTransaction($dat_6);
    //
    //        $dat_3 = [
    //            "JV_ID" => $jv,
    //            "Transaction_Reference" => 'credit',
    //            "Transaction_Account" => $this->info['Card_Account'],
    //            "Transaction_Type" => $payment_type,
    //            "Transaction_Amount" => $this->info['Card_Amount'],
    //            "Transaction_Date" => $this->info['Return_Invoice_Date'],
    //            "Currency_ID" => $this->info['Currency_ID'],
    //            "Logged_ID" => $this->info[0],
    //            "Transaction_Description" => $this->info['Return_Invoice_Note'],
    //            "Current_Ex_Rate" => "0",
    //            "main_id" => $return_id,
    //            "main_type" => $main_type
    //        ];
    //        JV::addTransaction($dat_3);
    //
    //        $dat_4 = [
    //            "JV_ID" => $jv,
    //            "Transaction_Reference" => 'cash',
    //            "Transaction_Account" => $this->info['Cash_Account'],
    //            "Transaction_Type" => $payment_type,
    //            "Transaction_Amount" => $this->info['Cash_Amount'],
    //            "Transaction_Date" => $this->info['Return_Invoice_Date'],
    //            "Currency_ID" => $this->info['Currency_ID'],
    //            "Logged_ID" => $this->info[0],
    //            "Transaction_Description" => $this->info['Return_Invoice_Note'],
    //            "Current_Ex_Rate" => "0",
    //            "main_id" => $return_id,
    //            "main_type" => $main_type
    //        ];
    //        JV::addTransaction($dat_4);
    //
    //        $dat_5 = ["JV_ID" => $jv,
    //            "Transaction_Reference" => 'check',
    //            "Transaction_Account" => $this->info['Check_Account'],
    //            "Transaction_Type" => $payment_type,
    //            "Transaction_Amount" => $this->info['Check_Amount'],
    //            "Transaction_Date" => $this->info['Return_Invoice_Date'],
    //            "Currency_ID" => $this->info['Currency_ID'],
    //            "Logged_ID" => $this->info[0],
    //            "Transaction_Description" => $this->info['Return_Invoice_Note'],
    //            "Current_Ex_Rate" => "0",
    //            "main_id" => $return_id,
    //            "main_type" => $main_type
    //        ];
    //        JV::addTransaction($dat_5);
    //
    //
    //        $error = $this->db->error();
    //        if (empty($error)) {
    //            $this->db->query("COMMIT");
    //            return $return_id;
    //        } else {
    //            $this->db->query("ROLLBACK");
    //
    //            return array_unique($error);
    //        }
    //    }

    public function getReturnCustomerDataByID($id, $return_id = null) {
        $data = array();
        $query_main = $this->db->query('select 
                                            current_year_id,
                                            Invoice_ID AS ID,
                                            Invoice_ID_Auto AS Auto_ID,
                                            Invoice_Date AS Date,
                                            Invoice_Due_Date AS Due_Date,
                                            Invoice_Discount AS Discount,
                                            discount_amount,
                                            Invoice_VAT AS VAT,
                                            Invoice_Sub_Total AS Sub_Total,
                                            Invoice_Net_Total AS Net_Total,
                                            Invoice_Paid_Amount AS Paid_Amount,
                                            Currency_ID,
                                            Currency_Symbol,
                                            Currency_Name,
                                            Depot_ID,
                                            Customer_ID,
                                            Customer_Name,
                                            truck,
                                            Invoice_Shipping as shipping,Account_ID,Account_Name
                                        from invoice_list_main where Invoice_ID_Auto = "' . smc_escape($id) . '" and current_year_id = "' . $this->year_id . '"');
        $row = $this->db->assoc($query_main);
        $data['Main'] = $row;
        $data['Person'] = Customer::getCustomerByID($data['Main']['Customer_ID']);
        //
        $ret_cond = '';

        if (!empty($return_id)) {
            $ret_cond = ' and Return_Invoice_ID <> "' . $return_id . '" ';
            //
            $return_arr = array();
            $return_query = $this->db->query('select Product_ID, UOM_ID, sum(Item_Quantity_Current) as Item_Quantity 
            from tbl_accounting_return_items where Return_Invoice_ID = "' . $return_id . '" 
                group by Product_ID, UOM_ID');
            while ($row = $this->db->assoc($return_query)) {
                $return_arr[$row['Product_ID']][$row['UOM_ID']] = $row['Item_Quantity'];
            }
        }
//        echo"<pre>";die(print_r($return_arr));
        $tmp_data = array();

        $query_ret = $this->db->query('select Product_ID, UOM_ID, sum(Item_Quantity_Current) as Item_Quantity 
            from tbl_accounting_return_items join tbl_accounting_return_invoices
            using(Return_Invoice_ID) where Invoice_Purchase_ID = "' . $id . '"  and Is_Customer_Invoice=1
                ' . $ret_cond . ' group by Product_ID, UOM_ID
            ');
        while ($ret_row = $this->db->assoc($query_ret))
            $tmp_data[$ret_row['Product_ID']][$ret_row['UOM_ID']] = $ret_row['Item_Quantity'];
        $query_inv = $this->db->query('SELECT id.`Entery_ID` as invoice_entry, id.`Invoice_ID_Auto`,
            id.`Product_ID` as invoice_product_id, id.`Product_Discount` as invoice_product_discount,
            id.`Product_Net_Price` as invoice_unit_price, id.`Product_Quantity` as invoice_quantity,
            id.`Product_Total_Price` as invoice_total_price, id.`Item_Quantity_Current` as invoice_current_quantity,
            id.`UOM_ID` as invoice_uom_id, id.`Discount_Amount` as invoice_discount_amount,
            id.`vat_percent` as invoice_vat_percent, id.`vat_amount` as invoice_vat_amount,
            id.`vat2_percent` as invoice_vat2_percent, id.`vat2_amount` as invoice_vat2_amount,
            p.*,id.product_description as invoice_item_desc,
            id.`Item_Quantity_Current`/id.`Product_Quantity` as uom_conv,
            u.UOM_Name,id.expiry_date,p.is_expiry,id.serial_nb,id.lot_nb,p.is_lot,p.is_serial
            FROM 
                `tbl_invoice_products_details` id 
            join tbl_stock_products p on p.Product_ID = id.Product_ID 
            join tbl_stock_uom u on u.UOM_ID = id.UOM_ID
            where id.`Invoice_ID_Auto` = "' . $id . '"  
                 ');
        while ($inv_row = $this->db->assoc($query_inv)) {
            $inv_row['remaining_quantity'] = $inv_row['invoice_current_quantity'];
            $prod_id = $inv_row['invoice_product_id'];
            $uom_id = $inv_row['invoice_uom_id'];
            $inv_row['ttc_price'] = $inv_row['invoice_unit_price'] * (1 + ($inv_row['vat_percent'] / 100));
            if (isset($tmp_data[$prod_id][$uom_id])) {
                if ($tmp_data[$prod_id][$uom_id] > $inv_row['invoice_current_quantity']) {
                    $inv_row['remaining_quantity'] = 0;
                    $tmp_data[$prod_id][$uom_id] -= $inv_row['invoice_current_quantity'];
                } else {
                    $inv_row['remaining_quantity'] -= $tmp_data[$prod_id][$uom_id];
                    $tmp_data[$prod_id][$uom_id] = 0;
                }
            }
            if (!empty($return_id)) {
                if (!isset($return_arr[$prod_id][$uom_id]))
                    $inv_row['cur_qty'] = 0;
                else
                    $inv_row['cur_qty'] = $return_arr[$prod_id][$uom_id];
            } else {
                $inv_row['cur_qty'] = $inv_row['remaining_quantity'];
            }
            $data['return'][] = $inv_row;
        }
        return $data;
    }

    public function getReturnSupplierDataByID($id, $return_id = null) {
        $data = array();
        $query_main = $this->db->query('select current_year_id,
                                            Purchase_ID AS ID,
                                            Purchase_ID_Auto AS Auto_ID,
                                            Purchase_Date AS Date,
                                            Purchase_Due_Date AS Due_Date,
                                            Purchase_Discount AS Discount,
                                            discount_amount,
                                            Purchase_VAT AS VAT,
                                            Purchase_Sub_Total AS Sub_Total,
                                            Purchase_Net_Total AS Net_Total,
                                            Purchase_Paid_Amount AS Paid_Amount,
                                            Currency_ID,
                                            Currency_Symbol,
                                            Currency_Name,
                                            Depot_ID,
                                            Supplier_Invoice_ID,
                                            Supplier_ID,
                                            Purchase_Shipping as shipping,Account_ID
                                        from purchase_main_list where Purchase_ID_Auto = "' . smc_escape($id) . '" AND current_year_id = "' . $this->year_id . '"');
        $row = $this->db->assoc($query_main);
        $data['Main'] = $row;
        $ref = "Pur." . $id;
        $acc_query = $this->db->query("SELECT 
                                            p.Account_Code as Transaction_Account,
                                            p.Account_Name,
                                            t.Transaction_Reference
                                        FROM 
                                            tbl_accounting_transactions t
                                        LEFT JOIN 
                                            tbl_chart_of_accounts p
                                        ON 
                                            t.Account_ID = p.Account_ID
                                        WHERE 
                                            t.main_id = '" . $id . "'
                                        AND 
                                            t.main_type = 'pur'
                                        AND 
                                        t.Transaction_Reference = 'pur' or t.Transaction_Reference = 'ship'
                                        ");
        while ($acc_row = $this->db->assoc($acc_query)) {
            $data['Main']['shipping_account'] = '';
            $data['Main']['shipping_name'] = '';
            if ($acc_row['Transaction_Reference'] === 'ship') {
                $data['Main']['shipping_account'] = $acc_row['Transaction_Account'];
                $data['Main']['shipping_name'] = $acc_row['Account_Name'];
            } else {
                $data['Main']['Transaction_Account'] = $acc_row['Transaction_Account'];
                $data['Main']['Account_Name'] = $acc_row['Account_Name'];
            }
        }

        $data['Person'] = Supplier::getSupplierByID($data['Main']['Supplier_ID']);
        //
        $ret_cond = '';

        if (!empty($return_id)) {
            $ret_cond = ' and Return_Invoice_ID <> "' . $return_id . '" ';
            //
            $return_arr = array();
            $return_query = $this->db->query('select Product_ID, UOM_ID, sum(Item_Quantity_Current) as Item_Quantity 
            from tbl_accounting_return_items where Return_Invoice_ID = "' . $return_id . '" 
                group by Product_ID, UOM_ID');
            while ($row = $this->db->assoc($return_query)) {
                $return_arr[$row['Product_ID']][$row['UOM_ID']] = $row['Item_Quantity'];
            }
        }
        $tmp_data = array();
        $query_ret = $this->db->query('select Product_ID, UOM_ID, sum(Item_Quantity_Current) as Item_Quantity from tbl_accounting_return_items join tbl_accounting_return_invoices
            using(Return_Invoice_ID) where Invoice_Purchase_ID = "' . $id . '" and Is_Customer_Invoice=0
                ' . $ret_cond . ' group by Product_ID, UOM_ID
            ');
        while ($ret_row = $this->db->assoc($query_ret))
            $tmp_data[$ret_row['Product_ID']][$ret_row['UOM_ID']] = $ret_row['Item_Quantity'];
        $query_inv = $this->db->query('SELECT id.`Item_ID` as invoice_entry, id.`Purchase_ID_Auto`,
            id.`Product_ID` as invoice_product_id, id.`Item_Discount` as invoice_product_discount,
            id.`Item_Price` as invoice_unit_price, id.`Item_Quantity` as invoice_quantity,
            id.`Product_Total_Price` as invoice_total_price, id.`Item_Quantity_Current` as invoice_current_quantity,
            id.`UOM_ID` as invoice_uom_id, 
            id.`vat_percent` as invoice_vat_percent, id.`vat_amount` as invoice_vat_amount,
            id.`vat2_percent` as invoice_vat2_percent, id.`vat2_amount` as invoice_vat2_amount,
            p.*,
            id.`Item_Quantity_Current`/id.`Item_Quantity` as uom_conv,
            u.UOM_Name,id.serial_nb,id.lot_nb,p.is_lot,p.is_serial
            FROM 
                `tbl_inventory_purchase_items` id 
            join tbl_stock_products p on p.Product_ID = id.Product_ID 
            join tbl_stock_uom u on u.UOM_ID = id.UOM_ID
            where id.`Purchase_ID_Auto` = "' . $id . '"  
                 ');
        while ($inv_row = $this->db->assoc($query_inv)) {
            $inv_row['remaining_quantity'] = $inv_row['invoice_current_quantity'];
            $prod_id = $inv_row['invoice_product_id'];
            $uom_id = $inv_row['invoice_uom_id'];
            if (isset($tmp_data[$prod_id][$uom_id])) {
                if ($tmp_data[$prod_id][$uom_id] > $inv_row['invoice_current_quantity']) {
                    $inv_row['remaining_quantity'] = 0;
                    $tmp_data[$prod_id][$uom_id] -= $inv_row['invoice_current_quantity'];
                } else {
                    $inv_row['remaining_quantity'] -= $tmp_data[$prod_id][$uom_id];
                    $tmp_data[$prod_id][$uom_id] = 0;
                }
            }
            if (!empty($return_id)) {
                if (!isset($return_arr[$prod_id][$uom_id]))
                    $inv_row['cur_qty'] = 0;
                else
                    $inv_row['cur_qty'] = $return_arr[$prod_id][$uom_id];
            } else {
                $inv_row['cur_qty'] = $inv_row['remaining_quantity'];
            }
            $data['return'][] = $inv_row;
        }
        return $data;
    }

    public function updateReturnLinkQuantities($data, $method, $operation) {
        $cost = CommonBusiness::addCostByType($data, $method);
        $up_quan = [&$data['Quantity'], &$data['Product_ID']];
        $this->db->prepare_Stmt("UPDATE 
                                                tbl_stock_products 
                                        SET 
                                                Product_Quantity = Product_Quantity " . $operation . " ?
                                        WHERE 
                                                Product_ID = ? 
                                    ", $up_quan);
        $sub_qty = &$data['Quantity'] * (-1);
        $dep_dat = [&$data['depot_id'], &$data['Product_ID'], &$sub_qty, &$data['Quantity']];
        $this->db->prepare_Stmt("insert into  
                                            tbl_stock_product_location 
                                            (Product_Depot_ID,Location_Product_ID,Product_Depot_Quantity)
                                            values (?,?,?)
                                            on duplicate key update
                                            Product_Depot_Quantity = Product_Depot_Quantity " . $operation . " ?

                                    ", $dep_dat);
        return $cost;
    }

    public static function deleteReturn($id_arr, $stop_trans = false) {
        if (smc_session()['year_closed'] == 1) {
            $error = array();
            $error[0] = CommonLanguage::getMessage('delete_year_alert');
            return $error;
        }
        $db = DBConnectorFactory::loadDriver();
        $mydate = date("Y-m-d H:i:s");
        //        $depot_query = $db->query('select Depot_ID,Is_Customer_Invoice,Is_Actual from  tbl_accounting_return_invoices where Return_Invoice_ID = "' . $id . '"');
        //        $row_ret= $db->assoc($depot_query);
        //        $depot_id = $row_ret['Depot_ID'];
        //        $Is_Customer_Invoice = $row_ret['Is_Customer_Invoice'];
        if (!$stop_trans)
            $db->query('START TRANSACTION');
        $depot = new WarehouseDepot;
        foreach ($id_arr as $id) {
            $query = $db->query('SELECT `Return_Invoice_Date`,Is_Actual 
                 FROM `tbl_accounting_return_invoices` WHERE `Return_Invoice_ID` = "' . $id . '"');
            $row = $db->assoc($query);
            $check = CommonFunctions::check_lock_data(['type' => 'return', 'start_date' => $row['Return_Invoice_Date'], 'is_actual' => $row['Is_Actual']]);
            if ($check !== '') {
                continue;
            }
            $db->prepare_Stmt('delete from tbl_accounting_transactions where main_id= ? and main_type in("rpr","rin")', [&$id]);
            $query = $db->query("SELECT 
                                        p.Product_ID as Item_ID,
                                        p.Return_Product_Quantity AS Quantity,
                                        p.Item_Quantity_Current AS Quantity_Current,
                                        p.UOM_ID,
                                        p.unit_price,
                                        p.unit_cost,
                                        i.Currency_ID,
                                        i.Depot_ID,
                                        i.date_time,
                                        i.current_rate,i.date_created,i.created_logged_id,p.expiry_date,
                                        p.lot_nb,p.serial_nb,pr.is_service,p.is_damaged,
                                        i.Is_Customer_Invoice,i.Is_Actual,i.Return_Invoice_Date,
                                        i.Return_ID,i.invoice_prefix,i.Person_ID,
                                        i.Return_Net_Total,i.Return_Paid_Total,i.Invoice_Purchase_ID
                                    FROM 
                                        tbl_accounting_return_items p
                                     JOIN 
                                        tbl_accounting_return_invoices i               
                                        USING(Return_Invoice_ID)
                                    join tbl_stock_products pr on pr.Product_ID = p.Product_ID
                                    WHERE 
                                        p.Return_Invoice_ID = '" . $id . "'
                ");

            $Is_Customer_Invoice = 1;
            $depot_id = 0;
            $old_return_nb = '';
            $old_inv_pur_id = 0;
            $old_net_total = 0;
            $old_paid_amount = 0;
            while ($row = $db->assoc($query)) {
                $old_inv_pur_id = $row['Invoice_Purchase_ID'];
                $old_net_total = $row['Return_Net_Total'];
                $old_paid_amount = $row['Return_Paid_Total'];
                $Is_Customer_Invoice = $row['Is_Customer_Invoice'];
                $depot_id = $row['Depot_ID'];
                $old_expiry_date = !empty($row['expiry_date']) ? $row['expiry_date'] : null;
                $old_lot_nb = !empty($row['lot_nb']) ? $row['lot_nb'] : '';
                $old_serial_nb = !empty($row['serial_nb']) ? $row['serial_nb'] : '';
                $old_return_nb = $row['invoice_prefix'] . $row['Return_ID'];
                $costdata = [
                    "Product_ID" => $row['Item_ID'],
                    "Quantity" => $row['Quantity'],
                    "Unit_Cost" => $row[($row['Is_Customer_Invoice'] == 0 ? 'unit_price' : 'unit_cost')],
                    "unit_price" => $row['unit_price'],
                    "Currency_ID" => $row['Currency_ID'],
                    "date_time" => $row['date_time'],
                    "date_time_now" => $mydate,
                    "current_rate" => $row['current_rate'],
                    "depot_id" => $row['Depot_ID'],
                    "trans_date" => $row['Return_Invoice_Date'],
                    "current_quantity" => $row['Quantity_Current'],
                    "uom_id" => $row['UOM_ID'],
                    "is_actual" => $row['Is_Actual'],
                    "expiry_date" => $old_expiry_date,
                    "lot_nb" => $old_lot_nb,
                    "serial_nb" => $old_serial_nb,
                    "main_id" => $id,
                    "is_return_damage" => $row['is_damaged'],
                    "is_service" => $row['is_service'],
                    "supplier_id" => ($row['Is_Customer_Invoice'] == 0 ? $row['Person_ID'] : null),
                    "customer_id" => ($row['Is_Customer_Invoice'] == 1 ? $row['Person_ID'] : null),
                    "main_nb" => $old_return_nb
                ];
//                CommonBusiness::addCostByType($costdata, ($row['Is_Customer_Invoice'] == 0 ? "edit_return_purchase_old" : "edit_return_invoice_old"));
                if ($row['Is_Customer_Invoice'] == 1) {
                    $product_links = CommonBusiness::get_product_links($row['Item_ID'], $row['UOM_ID'], $row['Quantity'], $row['Quantity_Current'], '+');
//                echo"<pre>";
//                die(print_r($product_links));
                    foreach ($product_links as $link) {
                        $costdata["Quantity"] = $link['Quantity'];
                        $costdata["current_quantity"] = $link['current_quantity'];
                        $costdata["Product_ID"] = $link['Product_ID'];
                        $costdata["uom_id"] = $link['uom_id'];
                        $costdata['is_inverse'] = $link['is_oprtn_inverse'];
                        if (!empty($link['is_main_prdct']))
                            $costdata['Unit_Cost'] = $row['unit_cost'];
                        else {
                            $cost = CommonBusiness::viewCurrentProductCost($link['Product_ID']);
                            $costdata['Unit_Cost'] = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies(smc_session()['local_currency'], $row['Currency_ID'], $cost);
                        }
//                        $cost = CommonBusiness::viewCurrentProductCost($link['Product_ID']);
//                        $costdata['Unit_Cost'] = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies(smc_session()['local_currency'], $row['Currency_ID'], $cost);
                        // echo $costdata['Unit_Cost'].='==';
                        CommonBusiness::updateLinkQuantities($costdata, "delete_return_invoice", ($link['operation'] == '+' ? '-' : '+'));
                        //                    echo $cost.='==';
                        //                $unit_cost += CurrencyFactory::returnExchangedValueBetweenTwoCurrencies(smc_session()['local_currency'], $this->info['Invoice_Currency'], $cost);
                    }
                } else {
                    CommonBusiness::updateLinkQuantities($costdata, "delete_return_purchase", '+');
                }
//                if ($row['is_damaged'] == 1 && $row['Is_Customer_Invoice'] == 1 && !empty($row['damage_id'])) {
//                    $depot = new WarehouseDepot();
//                    $depot->stop_transaction_start_end = true;
//                    $depot->delete_damage($row['damage_id']);
//                }
            }
            if ($Is_Customer_Invoice == 1) {
                $query_damage = $db->query('select damage_id from tbl_damage 
                where return_id = "' . $id . '"');
                $damage_row = $db->assoc($query_damage);
                if (!empty($damage_row['damage_id'])) {
                    $depot->stop_transaction_start_end = true;
                    $depot->delete_damage($damage_row['damage_id']);
                }
            }
            if (!empty($old_inv_pur_id)) {
                if ($Is_Customer_Invoice == 1) {
                    $db->prepare_Stmt('update tbl_invoices set Return_Invoice_Total = Return_Invoice_Total - ?,
                        return_paid_amount=return_paid_amount - ?,return_ids = trim("," from replace(concat(",",return_ids,","),concat(",","' . $id . '",","),","))
                where Invoice_ID_Auto = ?', [$old_net_total, $old_paid_amount, $old_inv_pur_id]);
                } else {
                    $db->prepare_Stmt('update tbl_inventory_purchases set Return_Purchase_Total = Return_Purchase_Total - ?,
                        return_paid_amount=return_paid_amount - ?,return_ids = trim("," from replace(concat(",",return_ids,","),concat(",","' . $id . '",","),","))
                where Purchase_ID_Auto = ?', [$old_net_total, $old_paid_amount, $old_inv_pur_id]);
                }
            }
            $trans_type = $Is_Customer_Invoice == 1 ? 'rin' : 'rpr';
            CommonBusiness::update_trans_record_current($trans_type, $id);

//            $query_returned_items = $db->query('select group_concat(Return_Item_ID) as return_detail_ids from tbl_accounting_return_items where Return_Invoice_ID = "' . $id . '"');
//            $row_returned_items = $db->assoc($query_returned_items);
//            if (!empty($row_returned_items)) {
//                $db->prepare_Stmt('delete from tbl_stock_damaged_reasons where return_detail_id in (' . $row_returned_items['return_detail_ids'] . ')');
//            }
            $db->prepare_Stmt("DELETE FROM tbl_accounting_return_items  WHERE Return_Invoice_ID = ?", [&$id]);
            $db->prepare_Stmt('delete from tbl_accounting_return_invoices where Return_Invoice_ID= ? ', [&$id]);
            $arr_return = ['type' => 'ret', 'main_id' => $id, 'action' => 2,
                'date_time' => $mydate, 'user_id' => smc_session()['user_logged_id']];
            CommonFunctions::add_user_log($arr_return);
        }
        $error = $db->error();
        if (empty($error)) {
            if (!$stop_trans)
                $db->query('COMMIT');
        } else {
            if (!$stop_trans)
                $db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function get_return_invoices_total($options) {
        $cond = ' ';
        if (isset($options['customer_id']) && $options['customer_id'] != '') {
            $cond .= ' and Person_ID = "' . $options['customer_id'] . '"';
        }

        if (!empty($options['ret_list_nb'])) {
            $cond .= ' and concat(invoice_prefix,Return_ID) = "' . $options['ret_list_nb'] . '"';
        }
        if($this->permission->hasPermission('Branches')){
            $cond .= !empty($_GET['search_branch_id_for_return']) ? ' and tbl_accounting_return_invoices.branch_id ="' . ($_GET['search_branch_id_for_return']) . '"' : '';
        }else{
            $cond .= ' and tbl_accounting_return_invoices.branch_id ="' . (smc_session()['user_accounts']['branch_id']) . '"'  ;
        }
        

        $cond .= ' and Is_Actual = 0';

        if (!$this->permission->hasPermission('view_all_pos_returns')) {
            $cond .= ' and Logged_ID = "' . smc_session()['user_logged_id'] . '"';
        }
        if (!empty($options['Date_From']) || !empty($options['Date_To'])) {
            $cond .= ' and Return_Invoice_Date between  "' . $options['Date_From'] . '"  and  "' . $options['Date_To'] . '" ';
        }

        $cond .= ' AND current_year_id = "' . $this->year_id . '"';
        $query = $this->db->query('select sum(Return_Net_Total*current_rate) as net_total,
            sum(Return_Paid_Total*current_rate) as paid_amount
            from tbl_accounting_return_invoices where isnull(exchange_id) and Is_Customer_Invoice = 1 ' . $cond);
        $row = $this->db->assoc($query);
        return $row;
    }

    public function change_return_actual($id_arr, $is_actual, $is_customer) {
        if (!$this->stop_transaction_start_end)
            $this->db->query('START TRANSACTION');
        $type = $is_customer ? 'rin' : 'rpr';
        $this->info['Is_Actual'] = $is_actual;
        $this->info['Is_Customer_Invoice'] = $is_customer;
        //        die($is_vendor.'=='.$this->info['Is_Customer_Invoice'])
        $is_valid = true;
        foreach ($id_arr as $id) {
            $query = $this->db->query('select invoice_prefix,Invoice_Purchase_ID
                from tbl_accounting_return_invoices where Return_Invoice_ID = "' . $id . '"');
            $return_row = $this->db->assoc($query);
            $inv_id = $return_row['Invoice_Purchase_ID'];
            if (!empty($inv_id)) {
                $is_valid = false;
                continue;
            }
            $prefix = $return_row['invoice_prefix'];
            $inv_nb = self::getMaxReturn($prefix);
            $this->db->prepare_Stmt('update tbl_accounting_return_invoices set Return_ID = ?,is_actual=?
            where Return_Invoice_ID = ?', [&$inv_nb, &$is_actual, &$id]);
            $jv = JV::returnJV($is_actual);
            $ref = "Ret." . $prefix . $inv_nb;
            //            echo $is_actual.'=='.$id.'=='.$type.'=='.$jv;
            $this->db->prepare_Stmt('update tbl_accounting_transactions set main_nb= ?, 
            is_actual = ?,JV_ID = ?
            where main_id = ? and main_type=?', [&$inv_nb, &$is_actual, &$jv, &$id, &$type]);
            $this->db->prepare_Stmt('update tbl_accounting_transactions set Transaction_Reference = ?
            where main_id = ? and main_type=? and subtype=""', [&$ref, &$id, &$type]);
            $this->db->prepare_Stmt('update tbl_stock_product_transactions set is_actual = ?
            where main_id = ? and Transaction_Type_ID in (8,13,9,14) ', [&$is_actual, &$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 [$error, $is_valid];
    }

    public function get_return_to_invoice($id) {
        $data = array();
        $year = SESSION::getCurrentYearID();
        $query_main = $this->db->query('select 
                                            r.current_year_id,
                                            r.Return_Invoice_ID as Auto_ID,
                                            r.invoice_prefix,
                                            r.Return_ID as ID,
                                            r.Return_Invoice_Date AS Date,r.Return_Invoice_Date as Due_Date,
                                            r.Return_Invoice_Total AS Sub_Total,
                                            r.Return_Net_Total as Net_Total,
                                            r.Invoice_VAT as VAT,
                                            r.Currency_ID,
                                            c.Currency_Symbol,
                                            c.Currency_Name,c.currency_round,
                                            r.Depot_ID,r.Person_ID,
                                            r.discount_amount,
                                            r.discount_percent as Discount,
                                            r.Is_Customer_Invoice,
                                            r.is_main_transfer,
                                            r.current_rate,
                                            r.Class_ID,
                                            pc.Class_Name,
                                            r.Sales_ID,
                                            concat(e.Employee_FirstName," ",e.Employee_LastName) as Employee_Name,
                                            r.Account_ID,pc.is_ttc,
                                            coa.Account_Code,coa.Account_Name,r.job_id,j.job_name,
                                            r.tax_amount,pc.tax_amount as class_tax,
                                            r.Return_Invoice_Note as Invoice_Description,"" as truck,0 as shipping,0 as is_branch_transfer,
                                            r.Return_Paid_Total as Paid_Amount,w.Depot_Name,r.project_id,pr.project_name,
                                            "" as shipping_account,"" as shipping_id,"" as shipping_name
                                        from tbl_accounting_return_invoices r
                                        join tbl_chart_of_accounts coa on coa.Account_ID = r.Account_ID
                                        join tbl_common_currency c on c.Currency_ID = r.Currency_ID
                                        left join tbl_pricing_class pc on pc.Class_ID = r.Class_ID 
                                        left join tbl_hrm_employee e on e.Employee_ID = r.Sales_ID
                                        left join tbl_accounting_jobs j on j.job_id = r.job_id
                                        left join tbl_projects pr on pr.project_id = r.project_id
                                        join tbl_stock_depot_warehouse w on w.Depot_ID = r.Depot_ID
                                        where Return_Invoice_ID = "' . smc_escape($id) . '" and current_year_id = "' . $this->year_id . '"');
        $row = $this->db->assoc($query_main);
        $row['Customer_ID'] = $row['Supplier_ID'] = $row['Person_ID'];
        $data['Main'] = $row;

        if ($data['Main']['Is_Customer_Invoice']) {
            $data['Person'] = Customer::getCustomerByID($data['Main']['Person_ID']);
        } else {
            $data['Person'] = Supplier::getSupplierByID($data['Main']['Person_ID']);
        }
        $last_price_arr = array();
        if (!is_null($data['Main']['Person_ID']) && $this->permission->hasPermission('view_sales_last_price') && $data['Main']['Is_Customer_Invoice'] == 1) {
            $product_ids = array();
            $prod_query = $this->db->query('select distinct Product_ID 
                from tbl_accounting_return_items where Return_Invoice_ID = "' . $id . '"');
            while ($row = $this->db->assoc($prod_query)) {
                $product_ids[] = $row['Product_ID'];
            }
            $last_price_arr = CommonBusiness::viewLastProductCustomerClassPrice($product_ids, $data['Main']['Person_ID'], $data['Main']['Class_ID']);
        }

        //        echo"<pre>";
        //        die(print_r($data));
        $query_details = $this->db->query('select
                                                i.Return_Item_ID as Entery_ID,
                                                p.Return_Invoice_ID AS Invoice_ID_Auto,
                                                i.Item_Quantity_Current AS Item_Quantity_Current,
                                                ps.Product_Code AS Code,
                                                ps.Product_Name AS Name,
                                                ps.Product_VAT,
                                                ps.is_tax,ps.is_lot,ps.is_serial,ps.is_service,
                                                ps.Product_Limit_Discount,
                                                ps.Category_ID,
                                                if(i.vat_percent <> 0,1,0) VAT,
                                                i.vat_percent,
                                                i.vat_amount,
                                                i.Product_ID AS Product_ID,
                                                i.Product_Discount AS Product_Discount,
                                                i.product_description,
                                                i.unit_price AS Product_Net_Price,
                                                i.Return_Product_Total AS Product_Total_Price,
                                                (i.Return_Product_Quantity/i.Item_Quantity_Current) AS uom_con,
                                                 i.UOM_ID,i.unit_cost,
                                                 uc.Bar_Code,i.expiry_date,ps.is_expiry,
                                                GROUP_CONCAT(uom_from separator "/") AS Product_UOM,
                                                ps.Status_ID,i.lot_nb,i.serial_nb,
                                                (( if(isnull(l.Product_Depot_Quantity),0,l.Product_Depot_Quantity) /uc.conversion_amount)) as Product_Quantity,
                                                (( if(isnull(l.Product_Depot_Quantity),0,l.Product_Depot_Quantity)/uc.conversion_amount)) AS Quantity
                                            from 
                                                tbl_accounting_return_items i 
                                            left join 
                                                tbl_accounting_return_invoices p 
                                            on
                                                p.Return_Invoice_ID = i.Return_Invoice_ID
                                            LEFT JOIN
                                                uom_conversions uc
                                            ON 
                                                uc.Product_ID = i.Product_ID
                                            left join 
                                                tbl_stock_products ps 
                                            on
                                                ps.Product_ID = i.Product_ID
                                            left  join
                                                tbl_stock_product_location l
                                            on 
                                                l.Location_Product_ID = i.Product_ID 
                                            and 
                                                l.Product_Depot_ID = p.Depot_ID
                                            where
                                                p.current_year_id = "' . $year . '"
                                            and 
                                                p.Return_Invoice_ID = "' . $id . '"
                                            GROUP BY i.Return_Item_ID
                                            order by p.Return_Invoice_ID

                                            ');
        while ($row = $this->db->assoc($query_details)) {
            $row['unit_tax'] = $data['Main']['class_tax'];
            $row['ttc_price'] = $row['Product_Net_Price'] * (1 + ($row['vat_percent'] / 100));

            $row['cost_percent'] = 0;
            $row['Delivery_Report_ID'] = 0;
            $row['reserve_to'] = '';
            $last_cost = CommonBusiness::viewLastProductCost($row["Product_ID"]);
            $row['LAST_COST'] = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies(smc_session()['local_currency'], $data['Main']['Currency_ID'], $last_cost);
            $last_price = 0;
            if (array_key_exists($row['Product_ID'], $last_price_arr)) {
//                $last_price = CommonBusiness::viewLastProductCustomerClassPrice($row["Product_ID"], $data['Main']['Person_ID'], $data['Main']['Class_ID']);
                $last_price = $last_price = $last_price_arr[$row['Product_ID']];
            }
            $row['last_price'] = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies(smc_session()['local_currency'], $data['Main']['Currency_ID'], $last_price);

            $data['Details'][] = $row;
        }

        //        $query = $this->db->query("SELECT 
        //                                            t.Transaction_Amount,
        //                                            t.Transaction_Reference,
        //                                            t.Account_ID,
        //                                            p.Account_Name AS P_Name,
        //                                            t.check_nb,t.due_date,t.value_date
        //                                        FROM 
        //                                            tbl_accounting_transactions t
        //                                        LEFT JOIN 
        //                                            tbl_chart_of_accounts p
        //                                        ON 
        //                                            t.Account_ID = p.Account_ID
        //                                       WHERE 
        //                                             t.main_id = '" . $id . "'
        //                                        AND 
        //                                            t.main_type = '" . ($data['Main']['Is_Customer_Invoice'] ? 'rin' : 'rpr') . "'
        //                                        AND 
        //                                            t.Transaction_Reference in ('cash','check','credit')
        //                                        ");
        $data['Payment'] = array();
        //        while ($row = $this->db->assoc($query)) {
        //            if (stripos($row['Transaction_Reference'], 'cash') !== false)
        //                $data['Payment']['Cash'] = $row;
        //            if (stripos($row['Transaction_Reference'], 'check') !== false)
        //                $data['Payment']['Cheque'] = $row;
        //            if (stripos($row['Transaction_Reference'], 'credit') !== false)
        //                $data['Payment']['Credit_Card'] = $row;
        //        }
        //                echo '<pre>';
        //                print_r($data);
        //                die('kk');
        return $data;
    }

}
