<?php

class DeliveryNote {

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

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

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

    public function getPurchaseOrderTerms($id, $is_invoice = 0) {
        $data = array();
        $query = $this->db->query('SELECT * FROM tbl_purchase_order_terms WHERE Order_ID = "' . smc_escape($id) . '" AND Is_Invoice = "' . smc_escape($is_invoice) . '"');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function confirmTermConditions() {
        $this->db->query('START TRANSACTION');
        foreach ($this->info['term'] as $term) {
            $details = [&$term['term_input']];
            $this->db->prepare_Stmt('UPDATE tbl_purchase_order_terms SET Status_ID = 1 WHERE Term_ID = ?', $details);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query("COMMIT");
        } else {
            $this->db->query("ROLLBACK");
        }
        return array_unique($error);
    }

    public function confirmPurchaseOrder() {
        $this->db->prepare_Stmt('UPDATE tbl_purchase_order SET Status_ID = 29 WHERE Order_ID = ?', [&$this->info['order_confirm']]);
        $error = $this->db->error();
        return array_unique($error);
    }

    public function add_delivery_note() {
        $check = CommonFunctions::check_lock_data(['type' => 'delivery', 'start_date' => $this->info['Delivery_Report_Date'], 'is_actual' => $this->info['is_actual']]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $order_id = !empty($this->info['order_id']) ? $this->info['order_id'] : 0;
        $purchase_id = !empty($this->info['purchase_id']) ? $this->info['purchase_id'] : 0;
        if (!empty($order_id)) {
            $check = $this->check_delivered_ordered_items($order_id, $this->info['Products']);
            if ($check) {
                $error = array();
                $error[0] = CommonLanguage::getMessage('delivered_exceeded_ordered');
                return $error;
            }
        }
        $disable_qty = 1;
        if (!empty($purchase_id)) {
            $check = $this->check_delivered_purchase_items($purchase_id, $this->info['Products']);
            if ($check) {
                $error = array();
                $error[0] = CommonLanguage::getMessage('delivered_exceeded_purchased');
                return $error;
            }
            $query_purchase = $this->db->query('select disable_qty from tbl_inventory_purchases
                where Purchase_ID_Auto ="' . $purchase_id . '"');
            $disable_qty = $this->db->assoc($query_purchase)['disable_qty'];
        }
        $this->db->query('START TRANSACTION');
        $delivery_nb = $this->get_delivery_nb($this->info['IsSales'], $this->info['is_actual']);
        $date_time = date('Y-m-d H:i:s');
        $logged_id = smc_session()['user_logged_id'];
        $branch_id = CommonBusiness::getBranchByDepot($this->info['Depot_ID']);
        $this->info['Currency_ID'] = !empty($this->info['Currency_ID']) ? $this->info['Currency_ID'] : smc_session()['local_currency'];
        $rate = !empty($this->info['current_rate']) ? $this->info['current_rate'] : CurrencyFactory::getLastExRate($this->info['Currency_ID']);
        $second_rate = CurrencyFactory::getSecondCurrencyLastExRate($this->info['Currency_ID']);
        $this->info['current_invoice_type'] = !empty($this->info['current_invoice_type']) ? $this->info['current_invoice_type'] : null;
        $this->info['clearing_inv_date'] = !empty($this->info['clearing_inv_date']) ? $this->info['clearing_inv_date'] : null;
        $this->info['idf_date'] = !empty($this->info['idf_date']) ? $this->info['idf_date'] : null;
        $this->info['stock_delivery_date'] = !empty($this->info['stock_delivery_date']) ? $this->info['stock_delivery_date'] : null;
        $this->info['ccvr_date'] = !empty($this->info['ccvr_date']) ? $this->info['ccvr_date'] : null;
        $this->info['ceps_date'] = !empty($this->info['ceps_date']) ? $this->info['ceps_date'] : null;
        $this->info['debit_acc_id'] = !empty($this->info['debit_acc_id']) ? $this->info['debit_acc_id'] : null;
        $this->info['credit_acc_id'] = !empty($this->info['credit_acc_id']) ? $this->info['credit_acc_id'] : null;
        $data = [&$this->info['Person_ID'], &$this->info['Delivery_Report_Date'], &$this->info['IsSales'],
            &$order_id, &$delivery_nb, &$date_time, &$date_time, &$logged_id, &$logged_id, &$this->info['Depot_ID'],
            &$this->info['delivery_note'], &$branch_id, &$this->info['job_id'], &$this->info['project_id'],
            &$this->info['is_actual'], &$this->year_id, &$this->info['delivery_ref'],
            &$this->info['extra_cost'], &$this->info['cost_dist_method'], $this->info['shipping'],
            $this->info['Currency_ID'], $rate, $second_rate, $purchase_id, $this->info['current_invoice_type'],
            $this->info['stock_delivery_nb'], $this->info['stock_delivery_date'],
            $this->info['idf_nb'], $this->info['idf_date'],
            $this->info['clearing_agent_name'], $this->info['clearing_inv_nb'],
            $this->info['clearing_inv_date'], $this->info['container_deposit'], $this->info['ceps_nb'],
            $this->info['ceps_date'], $this->info['ccvr_nb'], $this->info['ccvr_date'], $this->info['debit_acc_id'], $this->info['credit_acc_id']];
        $this->db->prepare_Stmt('INSERT INTO `tbl_inventory_delivery_report`( `Person_ID`,
            `Delivery_Report_Date`, `Is_Customer_Invoice`, `Order_ID`, `delivery_nb`, `date_created`,
            `date_time`, `Logged_ID`, `created_logged_id`,  `Depot_ID`,
            `delivery_note`, `branch_id`, `job_id`, `project_id`, `is_actual`,current_year_id,delivery_ref,
            extra_cost,cost_dist_method,shipping,Currency_ID,current_rate,second_currency_rate,purchase_id,
            invoice_type_id,stock_delivery_nb,stock_delivery_date,idf_nb,idf_date,clearing_agent_name,clearing_inv_nb,
            clearing_inv_date,container_deposit,ceps_nb,ceps_date,ccvr_nb,ccvr_date,debit_acc_id,credit_acc_id)
            values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', $data);
        $delivery_id = $this->db->id();
        $jv = JV::returnJV($this->info['is_actual']);

        if (!empty($this->info['extra_cost_arr'])) {
            foreach ($this->info['extra_cost_arr'] as $ex) {
                $deb_acc = !empty($ex['debit_acc_id']) ? $ex['debit_acc_id'] : null;
                $cre_acc = !empty($ex['credit_acc_id']) ? $ex['credit_acc_id'] : null;
                $ex_second_rate = CurrencyFactory::getSecondCurrencyLastExRate($ex['currency_id']);
                $data_extra = [$delivery_id, $ex['amount'], $ex['currency_id'], $ex['current_rate'], $ex_second_rate,
                    $ex['type'], $deb_acc, $cre_acc, $ex['note']];
                $this->db->prepare_Stmt('INSERT INTO `tbl_purchase_delivery_extra_cost`( `delivery_id`, `amount`,
                `currency_id`, `current_rate`, `second_currency_rate`,type,debit_acc_id,credit_acc_id,note)
                values(?,?,?,?,?,?,?,?,?)', $data_extra);
                if (!empty($deb_acc) && !empty($cre_acc)) {
                    $data_trans = ["JV_ID" => $jv,
                        "Transaction_Reference" => 'extra cost',
                        "Transaction_Account" => $deb_acc,
                        "Transaction_Type" => "Debit",
                        "Transaction_Amount" => $ex['amount'],
                        "Transaction_Date" => $this->info['Delivery_Report_Date'],
                        "Currency_ID" => $ex['currency_id'],
                        "Logged_ID" => $logged_id,
                        "Transaction_Description" => $ex['note'],
                        "Current_Ex_Rate" => $ex['current_rate'],
                        "second_currency_rate" => $ex_second_rate,
                        "main_id" => $delivery_id,
                        "main_type" => "dln",
                        "branch_id" => $branch_id,
                        "main_nb" => $delivery_nb,
                        "job_id" => $this->info['job_id'],
                        "project_id" => $this->info['project_id'],
                    ];
                    JV::addTransaction($data_trans, $this->info['is_actual']);
                    $data_trans = ["JV_ID" => $jv,
                        "Transaction_Reference" => 'extra cost',
                        "Transaction_Account" => $cre_acc,
                        "Transaction_Type" => "Credit",
                        "Transaction_Amount" => $ex['amount'],
                        "Transaction_Date" => $this->info['Delivery_Report_Date'],
                        "Currency_ID" => $ex['currency_id'],
                        "Logged_ID" => $logged_id,
                        "Transaction_Description" => $ex['note'],
                        "Current_Ex_Rate" => $ex['current_rate'],
                        "second_currency_rate" => $ex_second_rate,
                        "main_id" => $delivery_id,
                        "main_type" => "dln",
                        "branch_id" => $branch_id,
                        "main_nb" => $delivery_nb,
                        "job_id" => $this->info['job_id'],
                        "project_id" => $this->info['project_id'],
                    ];
                    JV::addTransaction($data_trans, $this->info['is_actual']);
                }
            }
        }

        if ($order_id != 0) {
            $this->db->prepare_Stmt('update tbl_purchase_order set is_converted = 1 where Order_ID = ? ', [&$order_id]);
        }
        $total_extra_cost = $this->info['extra_cost'] + $this->info['shipping'];
        $total = 0;
        foreach ($this->info['Products'] as $item) {
            $cost_percent = 0;
            if (!empty($total_extra_cost)) {
                if ($this->info['cost_dist_method'] == 0) {
                    $cost_percent = (($item['Item_Quantity'] == 0 || $this->info['total_qty'] == 0) ? 0 : ($item['Item_Quantity'] / $this->info['total_qty']));
                } else if ($this->info['cost_dist_method'] == 1) {
                    $cost_percent = (($item['total_cost'] == 0 || $this->info['total_cost'] == 0) ? 0 : ($item['total_cost'] / ($this->info['total_cost'])));
                } else if ($this->info['cost_dist_method'] == 2) {
                    $cost_percent = $item['cost_percent'] / 100;
                }
            }
            $ProductQuantity = Product::getConvertedQuantity($item['UOM_ID'], $item['Item_Quantity'], $item['Product_ID']);
            $item['expiry_date'] = !empty($item['expiry_date']) ? $item['expiry_date'] : null;
            $item['is_service'] = !empty($item['is_service']) ? $item['is_service'] : 0;
            $item['additional_cost'] = !empty($item['additional_cost']) ? $item['additional_cost'] : 0;

            $new_cost = ($item['additional_cost'] / $ProductQuantity ) + ($item['item_cost'] / ($ProductQuantity / $item['Item_Quantity']) ) + ($ProductQuantity == 0 ? 0 : ($total_extra_cost * $cost_percent / $ProductQuantity));
//            echo $new_cost . '==='.$cost_percent.'===';
//            echo (($item['additional_cost'] / ($ProductQuantity / $item['Item_Quantity'])) / $item['Item_Quantity']).'====';
//            echo ($item['item_cost'] / $ProductQuantity / $item['Item_Quantity'] ).'====';
//            echo ($ProductQuantity == 0 ? 0 : ($total_extra_cost * $cost_percent / $ProductQuantity)).'====';
//            echo $ProductQuantity.'===='.$total_extra_cost.'==='.$cost_percent.'====';
//            echo $this->info['extra_cost'].'===';
            $total+=($item['item_cost'] * $item['Item_Quantity']);

            $costdata = [
                "Product_ID" => $item['Product_ID'],
                "Quantity" => $ProductQuantity,
                "Unit_Cost" => $new_cost,
                "Currency_ID" => $this->info['Currency_ID'],
                "date_time" => $date_time,
                "current_rate" => $rate,
                "depot_id" => $this->info['Depot_ID'],
                "trans_date" => $this->info['Delivery_Report_Date'],
                "current_quantity" => $item['Item_Quantity'],
                "uom_id" => $item['UOM_ID'],
                "is_actual" => $this->info['is_actual'],
                "lot_nb" => $item['lot_nb'],
                "serial_nb" => $item['serial_nb'],
                "expiry_date" => $item['expiry_date'],
                "main_id" => $delivery_id,
                "is_service" => $item['is_service'],
                "main_nb" => $delivery_nb,
                "customer_id" => ($this->info['IsSales'] == 1 ? $this->info['Person_ID'] : null ),
                "supplier_id" => ($this->info['IsSales'] == 0 ? $this->info['Person_ID'] : null )
            ];
            $method = $this->info['IsSales'] == 1 ? 'add_customer_delivery' : 'add_supplier_delivery';
            $operation = $this->info['IsSales'] == 1 ? '-' : '+';
//            echo $order_id.'==='.$purc

            if (empty($order_id) && $disable_qty == 1) {
                $cost = CommonBusiness::updateLinkQuantities($costdata, $method, $operation) / $rate;
//                $item['item_cost'] = ($cost * ($ProductQuantity / $item['Item_Quantity']));
            }
            $item['local_item_cost'] = $item['item_cost'] * $rate;
            $item['order_detail_id'] = !empty($item['order_detail_id']) ? $item['order_detail_id'] : null;
            $item_data = [&$delivery_id, &$item['Product_ID'], &$ProductQuantity, &$item['Item_Quantity'],
                &$item['UOM_ID'], &$item['product_description'], &$item['lot_nb'], &$item['serial_nb'],
                &$item['expiry_date'], &$item['local_item_cost'], &$item['order_detail_id'], $new_cost,
                &$item['item_cost'], $item['cost_percent'], $item['additional_cost']];
            $this->db->prepare_Stmt('INSERT INTO `tbl_inventory_delivery_report_items`
                ( `Delivery_Report_ID`, `Product_ID`, `Product_Delivery_Quantity`,
                `Item_Quantity_Current`, `UOM_ID`, `description`, `lot_nb`, `serial_nb`, `expiry_date`,
                `unit_cost`,order_detail_id,real_cost,current_cost,cost_percent,additional_cost) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', $item_data);
            $delivery_detail_id = $this->db->id();
            if (!empty($item['cost_arr'])) {
                $item['cost_arr'] = json_decode($item['cost_arr'], true);
                foreach ($item['cost_arr'] as $ex) {
                    $deb_acc = !empty($ex['debit_acc_id']) ? $ex['debit_acc_id'] : null;
                    $cre_acc = !empty($ex['credit_acc_id']) ? $ex['credit_acc_id'] : null;
                    $ex_second_rate = CurrencyFactory::getSecondCurrencyLastExRate($ex['currency_id']);
                    $data_extra = [$delivery_id, $delivery_detail_id, $ex['amount'], $ex['currency_id'], $ex['current_rate'], $ex_second_rate,
                        $ex['type'], $deb_acc, $cre_acc, $ex['note']];
                    $this->db->prepare_Stmt('INSERT INTO `tbl_purchase_delivery_extra_cost`( `delivery_id`,
                       delivery_detail_id, `amount`,
                `currency_id`, `current_rate`, `second_currency_rate`,type,debit_acc_id,credit_acc_id,note)
                values(?,?,?,?,?,?,?,?,?,?)', $data_extra);
                    if (!empty($deb_acc) && !empty($cre_acc)) {
                        $data_trans = ["JV_ID" => $jv,
                            "Transaction_Reference" => 'extra cost',
                            "Transaction_Account" => $deb_acc,
                            "Transaction_Type" => "Debit",
                            "Transaction_Amount" => $ex['amount'],
                            "Transaction_Date" => $this->info['Delivery_Report_Date'],
                            "Currency_ID" => $ex['currency_id'],
                            "Logged_ID" => $logged_id,
                            "Transaction_Description" => $ex['note'],
                            "Current_Ex_Rate" => $ex['current_rate'],
                            "second_currency_rate" => $ex_second_rate,
                            "main_id" => $delivery_id,
                            "main_type" => "dln",
                            "branch_id" => $branch_id,
                            "main_nb" => $delivery_nb,
                            "job_id" => $this->info['job_id'],
                            "project_id" => $this->info['project_id'],
                        ];
                        JV::addTransaction($data_trans, $this->info['is_actual']);
                        $data_trans = ["JV_ID" => $jv,
                            "Transaction_Reference" => 'extra cost',
                            "Transaction_Account" => $cre_acc,
                            "Transaction_Type" => "Credit",
                            "Transaction_Amount" => $ex['amount'],
                            "Transaction_Date" => $this->info['Delivery_Report_Date'],
                            "Currency_ID" => $ex['currency_id'],
                            "Logged_ID" => $logged_id,
                            "Transaction_Description" => $ex['note'],
                            "Current_Ex_Rate" => $ex['current_rate'],
                            "second_currency_rate" => $ex_second_rate,
                            "main_id" => $delivery_id,
                            "main_type" => "dln",
                            "branch_id" => $branch_id,
                            "main_nb" => $delivery_nb,
                            "job_id" => $this->info['job_id'],
                            "project_id" => $this->info['project_id'],
                        ];
                        JV::addTransaction($data_trans, $this->info['is_actual']);
                    }
                }
            }
        }
        if (!empty($this->info['debit_acc_id']) && !empty($this->info['credit_acc_id'])) {
            $branch_id = CommonBusiness::getBranchByDepot($this->info['Depot_ID']);
            $dat_2 = ["JV_ID" => $jv,
                "Transaction_Reference" => $delivery_nb,
                "Transaction_Account" => $this->info['debit_acc_id'],
                "Transaction_Type" => 'Debit',
                "Transaction_Amount" => $total,
                "Transaction_Date" => $this->info['Delivery_Report_Date'],
                "Currency_ID" => $this->info['Currency_ID'],
                "Logged_ID" => $logged_id,
                "created_logged_id" => $logged_id,
                "date_created" => $date_time,
                "date_time" => $date_time,
                "Transaction_Description" => $this->info['delivery_note'],
                "Current_Ex_Rate" => $rate,
                "second_currency_rate" => $second_rate,
                "main_id" => $delivery_id,
                "main_type" => 'dln',
                "branch_id" => $branch_id,
                "main_nb" => $delivery_nb
            ];
            JV::addTransaction($dat_2, $this->info['is_actual']);
            $dat_2 = ["JV_ID" => $jv,
                "Transaction_Reference" => $delivery_nb,
                "Transaction_Account" => $this->info['credit_acc_id'],
                "Transaction_Type" => 'Credit',
                "Transaction_Amount" => $total,
                "Transaction_Date" => $this->info['Delivery_Report_Date'],
                "Currency_ID" => $this->info['Currency_ID'],
                "Logged_ID" => $logged_id,
                "created_logged_id" => $logged_id,
                "date_created" => $date_time,
                "date_time" => $date_time,
                "Transaction_Description" => $this->info['delivery_note'],
                "Current_Ex_Rate" => $rate,
                "second_currency_rate" => $second_rate,
                "main_id" => $delivery_id,
                "main_type" => 'dln',
                "branch_id" => $branch_id,
                "main_nb" => $delivery_nb
            ];
            JV::addTransaction($dat_2, $this->info['is_actual']);
        }
//        die();
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query("COMMIT");
            if(!empty($delivery_id)){
                return [$delivery_id];
            }
        } else {
            $this->db->query("ROLLBACK");
        }
        return $error;
    }

    public function get_delivery_nb($is_sales, $is_actual) {
        $query = $this->db->query('SELECT MAX(delivery_nb) AS Entry_ID 
            FROM tbl_inventory_delivery_report where  Is_Customer_Invoice = "' . $is_sales . '" 
                and is_actual = "' . $is_actual . '" and current_year_id = "' . $this->year_id . '"');
        $row = $this->db->assoc($query);
        if (!empty($row))
            $nb = $row['Entry_ID'] + 1;
        else
            $nb = 1;
        return $nb;
    }

    public function edit_delivery_note() {
        $check = CommonFunctions::check_lock_data(['type' => 'delivery', 'start_date' => $this->info['Delivery_Report_Date'], 'is_actual' => $this->info['is_actual']]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $this->db->query('START TRANSACTION');
        $date_time = date('Y-m-d H:i:s');
        $logged_id = smc_session()['user_logged_id'];
        $branch_id = CommonBusiness::getBranchByDepot($this->info['Depot_ID']);
        $this->info['Currency_ID'] = !empty($this->info['Currency_ID']) ? $this->info['Currency_ID'] : smc_session()['local_currency'];
        $rate = !empty($this->info['current_rate']) ? $this->info['current_rate'] : CurrencyFactory::getLastExRate($this->info['Currency_ID']);
        $second_rate = CurrencyFactory::getSecondCurrencyLastExRate($this->info['Currency_ID']);
        $query_order = $this->db->query('select Order_ID,purchase_id,delivery_nb,date_created,created_logged_id from tbl_inventory_delivery_report 
            where Delivery_Report_ID = ?', [&$this->info['Delivery_Report_ID']]);
        $order_row = $this->db->assoc($query_order);
        $old_delivery_nb = $order_row['delivery_nb'];
        $old_date_created = $order_row['date_created'];
        $old_logged = $order_row['created_logged_id'];
        $order_id = $order_row['Order_ID'];
        if (!empty($order_id)) {
            $check = $this->check_delivered_ordered_items($order_id, $this->info['Products'], $this->info['Delivery_Report_ID']);
            if ($check) {
                $error = array();
                $error[0] = CommonLanguage::getMessage('delivered_exceeded_ordered');
                return $error;
            }
        }
        $disable_qty = 1;
        $purchase_id = $order_row['purchase_id'];
        if (!empty($purchase_id)) {
            $check = $this->check_delivered_purchase_items($purchase_id, $this->info['Products']);
            if ($check) {
                $error = array();
                $error[0] = CommonLanguage::getMessage('delivered_exceeded_purchased');
                return $error;
            }
            $query_purchase = $this->db->query('select disable_qty from tbl_inventory_purchases
                where Purchase_ID_Auto ="' . $purchase_id . '"');
            $disable_qty = $this->db->assoc($query_purchase)['disable_qty'];
        }
        if ($disable_qty == 1) {
            $query_det = $this->db->prepare_Stmt('select d.*,p.date_time,p.Delivery_Report_Date,
            p.Depot_ID,p.is_actual,p.Is_Customer_Invoice,pr.is_service,p.Currency_ID,p.current_rate,
            p.delivery_nb,p.Person_ID
            from tbl_inventory_delivery_report_items d
            join tbl_inventory_delivery_report p using(Delivery_Report_ID)
            join tbl_stock_products pr on pr.Product_ID = d.Product_ID
            where Delivery_Report_ID = ?', [&$this->info['Delivery_Report_ID']]);

            while ($row_det = $this->db->assoc($query_det)) {
                $old_expiry_date = !empty($row_det['expiry_date']) ? $row_det['expiry_date'] : null;
                $old_delivery_nb = $row_det['delivery_nb'];
                $costdata = [
                    "Product_ID" => $row_det['Product_ID'],
                    "Quantity" => $row_det['Product_Delivery_Quantity'],
                    "Unit_Cost" => $row_det['real_cost'],
                    "Currency_ID" => $row_det['Currency_ID'],
                    "date_time" => $row_det['date_time'],
                    "current_rate" => $row_det['current_rate'],
                    "depot_id" => $row_det['Depot_ID'],
                    "trans_date" => $row_det['Delivery_Report_Date'],
                    "current_quantity" => $row_det['Item_Quantity_Current'],
                    "uom_id" => $row_det['UOM_ID'],
                    "is_actual" => $row_det['is_actual'],
                    'lot_nb' => $row_det['lot_nb'],
                    'serial_nb' => $row_det['serial_nb'],
                    "expiry_date" => $old_expiry_date,
                    "main_id" => $this->info['Delivery_Report_ID'],
                    "is_service" => $row_det['is_service'],
                    "main_nb" => $row_det['delivery_nb'],
                    "customer_id" => ($row_det['Is_Customer_Invoice'] == 1 ? $row_det['Person_ID'] : null ),
                    "supplier_id" => ($row_det['Is_Customer_Invoice'] == 0 ? $row_det['Person_ID'] : null )
                ];
                CommonBusiness::updateLinkQuantities($costdata, ($row_det['Is_Customer_Invoice'] == 1 ? 'edit_customer_delivery_old' : 'edit_supplier_delivery_old'), ($row_det['Is_Customer_Invoice'] == 1 ? '+' : '-'));
            }
        }
        $this->info['clearing_inv_date'] = !empty($this->info['clearing_inv_date']) ? $this->info['clearing_inv_date'] : null;
        $this->info['idf_date'] = !empty($this->info['idf_date']) ? $this->info['idf_date'] : null;
        $this->info['stock_delivery_date'] = !empty($this->info['stock_delivery_date']) ? $this->info['stock_delivery_date'] : null;
        $this->info['ccvr_date'] = !empty($this->info['ccvr_date']) ? $this->info['ccvr_date'] : null;
        $this->info['ceps_date'] = !empty($this->info['ceps_date']) ? $this->info['ceps_date'] : null;
        $this->info['debit_acc_id'] = !empty($this->info['debit_acc_id']) ? $this->info['debit_acc_id'] : null;
        $this->info['credit_acc_id'] = !empty($this->info['credit_acc_id']) ? $this->info['credit_acc_id'] : null;

        $data = [&$this->info['Person_ID'], &$this->info['Delivery_Report_Date'],
            &$date_time, &$logged_id, &$this->info['Depot_ID'], &$this->info['delivery_note'],
            &$branch_id, &$this->info['job_id'], &$this->info['project_id'],
            &$this->info['delivery_ref'], &$this->info['extra_cost'], &$this->info['cost_dist_method'], $this->info['shipping'],
            $this->info['Currency_ID'], $rate, $second_rate,
            $this->info['stock_delivery_nb'], $this->info['stock_delivery_date'],
            $this->info['idf_nb'], $this->info['idf_date'],
            $this->info['clearing_agent_name'], $this->info['clearing_inv_nb'],
            $this->info['clearing_inv_date'], $this->info['container_deposit'], $this->info['ceps_nb'],
            $this->info['ceps_date'], $this->info['ccvr_nb'], $this->info['ccvr_date'],
            $this->info['debit_acc_id'], $this->info['credit_acc_id'], &$this->info['Delivery_Report_ID']];
        $this->db->prepare_Stmt('update `tbl_inventory_delivery_report` set `Person_ID`=?,
            `Delivery_Report_Date`=?,
            `date_time`=?, `Logged_ID`=?, `Depot_ID`=?,`delivery_note`=?, `branch_id`=?,
            `job_id`=?, `project_id`=?,delivery_ref=?,
            extra_cost=?,cost_dist_method=?,shipping=?,Currency_ID=?,current_rate=?,second_currency_rate=?,
            stock_delivery_nb=?,stock_delivery_date=?,idf_nb=?,idf_date=?,clearing_agent_name=?,clearing_inv_nb=?,
            clearing_inv_date=?,container_deposit=?,ceps_nb=?,ceps_date=?,ccvr_nb=?,ccvr_date=?,
            debit_acc_id=?,credit_acc_id=?
            where Delivery_Report_ID = ?', $data);
        $delivery_id = $this->info['Delivery_Report_ID'];
        $this->db->prepare_Stmt('delete from tbl_inventory_delivery_report_items where Delivery_Report_ID=?', [&$delivery_id]);
        $jv_query = $this->db->query("SELECT DISTINCT JV_ID FROM tbl_accounting_transactions
            WHERE main_id='" . $delivery_id . "' AND main_type= 'dln' ");
        $jv_assoc = $this->db->assoc($jv_query);
        if (!empty($jv_assoc))
            $jv = $jv_assoc['JV_ID'];
        else
            $jv = JV::returnJV($this->info['is_actual']);
        //
        $this->db->prepare_Stmt('delete from tbl_accounting_transactions where main_id = ? and main_type="dln" ', [$delivery_id]);
        $this->db->prepare_Stmt('delete from tbl_purchase_delivery_extra_cost where delivery_id = "' . $delivery_id . '"');

        if (!empty($this->info['extra_cost_arr'])) {
            foreach ($this->info['extra_cost_arr'] as $ex) {
                $deb_acc = !empty($ex['debit_acc_id']) ? $ex['debit_acc_id'] : null;
                $cre_acc = !empty($ex['credit_acc_id']) ? $ex['credit_acc_id'] : null;
                $ex_second_rate = CurrencyFactory::getSecondCurrencyLastExRate($ex['currency_id']);
                $data_extra = [$delivery_id, $ex['amount'], $ex['currency_id'], $ex['current_rate'], $ex_second_rate,
                    $ex['type'], $deb_acc, $cre_acc, $ex['note']];
                $this->db->prepare_Stmt('INSERT INTO `tbl_purchase_delivery_extra_cost`( `delivery_id`, `amount`,
                `currency_id`, `current_rate`, `second_currency_rate`,type,debit_acc_id,credit_acc_id,note)
                values(?,?,?,?,?,?,?,?,?)', $data_extra);
                if (!empty($deb_acc) && !empty($cre_acc)) {
                    $data_trans = ["JV_ID" => $jv,
                        "Transaction_Reference" => 'extra cost',
                        "Transaction_Account" => $deb_acc,
                        "Transaction_Type" => "Debit",
                        "Transaction_Amount" => $ex['amount'],
                        "Transaction_Date" => $this->info['Delivery_Report_Date'],
                        "Currency_ID" => $ex['currency_id'],
                        "Logged_ID" => $logged_id,
                        "Transaction_Description" => $ex['note'],
                        "Current_Ex_Rate" => $ex['current_rate'],
                        "second_currency_rate" => $ex_second_rate,
                        "main_id" => $delivery_id,
                        "main_type" => "dln",
                        "branch_id" => $branch_id,
                        "main_nb" => $old_delivery_nb,
                        "job_id" => $this->info['job_id'],
                        "project_id" => $this->info['project_id'],
                    ];
                    JV::addTransaction($data_trans, $this->info['is_actual']);
                    $data_trans = ["JV_ID" => $jv,
                        "Transaction_Reference" => 'extra cost',
                        "Transaction_Account" => $cre_acc,
                        "Transaction_Type" => "Credit",
                        "Transaction_Amount" => $ex['amount'],
                        "Transaction_Date" => $this->info['Delivery_Report_Date'],
                        "Currency_ID" => $ex['currency_id'],
                        "Logged_ID" => $logged_id,
                        "Transaction_Description" => $ex['note'],
                        "Current_Ex_Rate" => $ex['current_rate'],
                        "second_currency_rate" => $ex_second_rate,
                        "main_id" => $delivery_id,
                        "main_type" => "dln",
                        "branch_id" => $branch_id,
                        "main_nb" => $old_delivery_nb,
                        "job_id" => $this->info['job_id'],
                        "project_id" => $this->info['project_id'],
                    ];
                    JV::addTransaction($data_trans, $this->info['is_actual']);
                }
            }
        }

        $total_extra_cost = $this->info['extra_cost'] + $this->info['shipping'];
        CommonBusiness::update_trans_record_current(($this->info['IsSales'] == 1 ? 'dlc' : 'dls'), $this->info['Delivery_Report_ID']);
        $total = 0;
        foreach ($this->info['Products'] as $item) {
            $cost_percent = 0;
            if (!empty($total_extra_cost)) {
                if ($this->info['cost_dist_method'] == 0) {
                    $cost_percent = (($item['Item_Quantity'] == 0 || $this->info['total_qty'] == 0) ? 0 : ($item['Item_Quantity'] / $this->info['total_qty']));
                } else if ($this->info['cost_dist_method'] == 1) {
                    $cost_percent = (($item['total_cost'] == 0 || $this->info['total_cost'] == 0) ? 0 : ($item['total_cost'] / ($this->info['total_cost'])));
                } else if ($this->info['cost_dist_method'] == 2) {
                    $cost_percent = $item['cost_percent'] / 100;
                }
            }
            $ProductQuantity = Product::getConvertedQuantity($item['UOM_ID'], $item['Item_Quantity'], $item['Product_ID']);
            $item['expiry_date'] = !empty($item['expiry_date']) ? $item['expiry_date'] : null;
            $item['is_service'] = !empty($item['is_service']) ? $item['is_service'] : 0;
//            $new_cost = (($item['additional_cost'] / ($ProductQuantity / $item['Item_Quantity'])) / $item['Item_Quantity']) + ($item['item_cost'] / ($ProductQuantity / $item['Item_Quantity']) ) + ($ProductQuantity == 0 ? 0 : ($total_extra_cost * $cost_percent / $ProductQuantity));
                        $new_cost = ($item['additional_cost'] / $ProductQuantity ) + ($item['item_cost'] / ($ProductQuantity / $item['Item_Quantity']) ) + ($ProductQuantity == 0 ? 0 : ($total_extra_cost * $cost_percent / $ProductQuantity));

            $total+=($item['item_cost'] * $item['Item_Quantity']);
            $costdata = [
                "Product_ID" => $item['Product_ID'],
                "Quantity" => $ProductQuantity,
                "Unit_Cost" => $new_cost,
                "Currency_ID" => $this->info['Currency_ID'],
                "date_time" => $date_time,
                "current_rate" => $rate,
                "depot_id" => $this->info['Depot_ID'],
                "trans_date" => $this->info['Delivery_Report_Date'],
                "current_quantity" => $item['Item_Quantity'],
                "uom_id" => $item['UOM_ID'],
                "is_actual" => $this->info['is_actual'],
                "lot_nb" => $item['lot_nb'],
                "serial_nb" => $item['serial_nb'],
                "expiry_date" => $item['expiry_date'],
                "main_id" => $delivery_id,
                "is_service" => $item['is_service'],
                "main_nb" => $old_delivery_nb,
                "customer_id" => ($this->info['IsSales'] == 1 ? $this->info['Person_ID'] : null ),
                "supplier_id" => ($this->info['IsSales'] == 0 ? $this->info['Person_ID'] : null )
            ];
            $method = $this->info['IsSales'] == 1 ? 'edit_customer_delivery_new' : 'edit_supplier_delivery_new';
            $operation = $this->info['IsSales'] == 1 ? '-' : '+';
            if (empty($order_id) && $disable_qty == 1) {
                $cost = CommonBusiness::updateLinkQuantities($costdata, $method, $operation) / $rate;
//                $item['item_cost'] = $cost * ($ProductQuantity / $item['Item_Quantity']);
            }
            $item['local_item_cost'] = $item['item_cost'] * $rate;

            $item['order_detail_id'] = !empty($item['order_detail_id']) ? $item['order_detail_id'] : null;
            $item_data = [&$delivery_id, &$item['Product_ID'], &$ProductQuantity, &$item['Item_Quantity'],
                &$item['UOM_ID'], &$item['product_description'], &$item['lot_nb'], &$item['serial_nb'],
                &$item['expiry_date'], &$item['local_item_cost'], &$item['order_detail_id'], $new_cost, &$item['item_cost'],
                $item['cost_percent'], $item['additional_cost']];
            $this->db->prepare_Stmt('INSERT INTO `tbl_inventory_delivery_report_items`
                ( `Delivery_Report_ID`, `Product_ID`, `Product_Delivery_Quantity`,
                `Item_Quantity_Current`, `UOM_ID`, `description`, `lot_nb`, `serial_nb`, `expiry_date`,
                `unit_cost`,order_detail_id,real_cost,current_cost,cost_percent,additional_cost) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', $item_data);
            $delivery_detail_id = $this->db->id();
            if (!empty($item['cost_arr'])) {
                $item['cost_arr'] = json_decode($item['cost_arr'], true);
                foreach ($item['cost_arr'] as $ex) {
                    $deb_acc = !empty($ex['debit_acc_id']) ? $ex['debit_acc_id'] : null;
                    $cre_acc = !empty($ex['credit_acc_id']) ? $ex['credit_acc_id'] : null;
                    $ex_second_rate = CurrencyFactory::getSecondCurrencyLastExRate($ex['currency_id']);
                    $data_extra = [$delivery_id, $delivery_detail_id, $ex['amount'], $ex['currency_id'], $ex['current_rate'], $ex_second_rate,
                        $ex['type'], $deb_acc, $cre_acc, $ex['note']];
                    $this->db->prepare_Stmt('INSERT INTO `tbl_purchase_delivery_extra_cost`( `delivery_id`,
                       delivery_detail_id, `amount`,
                `currency_id`, `current_rate`, `second_currency_rate`,type,debit_acc_id,credit_acc_id,note)
                values(?,?,?,?,?,?,?,?,?,?)', $data_extra);
                    if (!empty($deb_acc) && !empty($cre_acc)) {
                        $data_trans = ["JV_ID" => $jv,
                            "Transaction_Reference" => 'extra cost',
                            "Transaction_Account" => $deb_acc,
                            "Transaction_Type" => "Debit",
                            "Transaction_Amount" => $ex['amount'],
                            "Transaction_Date" => $this->info['Delivery_Report_Date'],
                            "Currency_ID" => $ex['currency_id'],
                            "Logged_ID" => $logged_id,
                            "Transaction_Description" => $ex['note'],
                            "Current_Ex_Rate" => $ex['current_rate'],
                            "second_currency_rate" => $ex_second_rate,
                            "main_id" => $delivery_id,
                            "main_type" => "dln",
                            "branch_id" => $branch_id,
                            "main_nb" => $old_delivery_nb,
                            "job_id" => $this->info['job_id'],
                            "project_id" => $this->info['project_id'],
                        ];
                        JV::addTransaction($data_trans, $this->info['is_actual']);
                        $data_trans = ["JV_ID" => $jv,
                            "Transaction_Reference" => 'extra cost',
                            "Transaction_Account" => $cre_acc,
                            "Transaction_Type" => "Credit",
                            "Transaction_Amount" => $ex['amount'],
                            "Transaction_Date" => $this->info['Delivery_Report_Date'],
                            "Currency_ID" => $ex['currency_id'],
                            "Logged_ID" => $logged_id,
                            "Transaction_Description" => $ex['note'],
                            "Current_Ex_Rate" => $ex['current_rate'],
                            "second_currency_rate" => $ex_second_rate,
                            "main_id" => $delivery_id,
                            "main_type" => "dln",
                            "branch_id" => $branch_id,
                            "main_nb" => $old_delivery_nb,
                            "job_id" => $this->info['job_id'],
                            "project_id" => $this->info['project_id'],
                        ];
                        JV::addTransaction($data_trans, $this->info['is_actual']);
                    }
                }
            }
        }
        if (!empty($this->info['debit_acc_id']) && !empty($this->info['credit_acc_id'])) {
            $branch_id = CommonBusiness::getBranchByDepot($this->info['Depot_ID']);
            $dat_2 = ["JV_ID" => $jv,
                "Transaction_Reference" => $old_delivery_nb,
                "Transaction_Account" => $this->info['debit_acc_id'],
                "Transaction_Type" => 'Debit',
                "Transaction_Amount" => $total,
                "Transaction_Date" => $this->info['Delivery_Report_Date'],
                "Currency_ID" => $this->info['Currency_ID'],
                "Logged_ID" => $logged_id,
                "created_logged_id" => $old_logged,
                "date_created" => $old_date_created,
                "date_time" => $date_time,
                "Transaction_Description" => $this->info['delivery_note'],
                "Current_Ex_Rate" => $rate,
                "second_currency_rate" => $second_rate,
                "main_id" => $delivery_id,
                "main_type" => 'dln',
                "branch_id" => $branch_id,
                "main_nb" => $old_delivery_nb
            ];
            JV::addTransaction($dat_2, $this->info['is_actual']);
            $dat_2 = ["JV_ID" => $jv,
                "Transaction_Reference" => $old_delivery_nb,
                "Transaction_Account" => $this->info['credit_acc_id'],
                "Transaction_Type" => 'Credit',
                "Transaction_Amount" => $total,
                "Transaction_Date" => $this->info['Delivery_Report_Date'],
                "Currency_ID" => $this->info['Currency_ID'],
                "Logged_ID" => $logged_id,
                "created_logged_id" => $old_logged,
                "date_created" => $old_date_created,
                "date_time" => $date_time,
                "Transaction_Description" => $this->info['delivery_note'],
                "Current_Ex_Rate" => $rate,
                "second_currency_rate" => $second_rate,
                "main_id" => $delivery_id,
                "main_type" => 'dln',
                "branch_id" => $branch_id,
                "main_nb" => $old_delivery_nb
            ];
            JV::addTransaction($dat_2, $this->info['is_actual']);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query("COMMIT");
            if(!empty($this->info['Delivery_Report_ID'])){
                return [$this->info['Delivery_Report_ID']];
            }
        } else {
            $this->db->query("ROLLBACK");
        }
        return array_unique($error);
    }

    //    public function confirmSalesOrder(){
    //        $this->db->prepare_Stmt('UPDATE tbl_garage_sales_order SET Sales_Status = 29 WHERE Sales_Order_ID = ?',[&$this->info['order_confirm']]);
    //        $error = $this->db->error();
    //        return array_unique($error);
    //    }

    public function get_delivery_note_by_id($id) {
        $data = array("Main" => array(), "Details" => array(), "Person" => array());
        $query_main = $this->db->query('select r.current_year_id,r.Is_Customer_Invoice,
         r.delivery_nb AS ID,r.Delivery_Report_ID AS Auto_ID,r.Delivery_Report_Date AS Date,
         r.Depot_ID,r.Person_ID,r.delivery_note,r.job_id,r.project_id,j.job_name,p.project_name,r.delivery_ref,
        r.Currency_ID,cu.Currency_Name,cu.Currency_Symbol,r.current_rate,r.cost_dist_method,r.shipping,
        r.extra_cost,r.stock_delivery_nb,r.stock_delivery_date,r.idf_nb,r.idf_date,r.clearing_agent_name,
        r.clearing_inv_date,r.clearing_inv_nb,r.container_deposit,r.invoice_type_id,
        ifnull(concat(deb.Account_Code," - ",deb.Account_Name),"") as debit_acc_name,
        ifnull(concat(cre.Account_Code," - ",cre.Account_Name),"") as credit_acc_name,r.debit_acc_id,r.credit_acc_id
            from tbl_inventory_delivery_report r left join tbl_accounting_jobs j 
            on j.job_id = r.job_id left join tbl_projects p on p.project_id = r.project_id
            join tbl_common_currency cu on cu.Currency_ID = r.Currency_ID
            left join tbl_chart_of_accounts deb on deb.Account_ID = r.debit_acc_id
            left join tbl_chart_of_accounts cre on cre.Account_ID = r.credit_acc_id
            where Delivery_Report_ID = "' . smc_escape($id) . '" ');
        $row = $this->db->assoc($query_main);
        if (!empty($row)) {
            $data['Main'] = $row;

            if ($data['Main']['Is_Customer_Invoice'] == 1) {
                $data['Person'] = Customer::getCustomerByID($data['Main']['Person_ID']);
            } else {
                $data['Person'] = Supplier::getSupplierByID($data['Main']['Person_ID']);
            }
            $query_details = $this->db->query('select
                                                i.Entry_ID,
                                                p.Delivery_Report_ID,
                                                i.Item_Quantity_Current AS Item_Quantity_Current,
                                                ps.Product_Code AS Code,
                                                ps.Product_Name AS Name,ps.is_lot,ps.is_serial,ps.is_service,
                                                ps.Status_ID,
                                                (( 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,
                                                i.Product_ID,i.order_detail_id,
                                                i.description as product_description,
                                                uc.Bar_Code,i.unit_cost,i.current_cost,
                                                (i.Product_Delivery_Quantity/i.Item_Quantity_Current) AS uom_con,
                                                 i.UOM_ID,"" as expiry_date,ps.is_expiry,i.lot_nb,i.serial_nb,i.real_cost,i.current_cost,i.cost_percent,
                                                 i.additional_cost,
                                                GROUP_CONCAT(uom_from separator "/") AS Product_UOM
                                            from tbl_inventory_delivery_report_items i 
                                            join tbl_inventory_delivery_report p 
                                            on p.Delivery_Report_ID = i.Delivery_Report_ID
                                            join uom_conversions uc ON uc.Product_ID = i.Product_ID
                                            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 = "' . $data['Main']['Depot_ID'] . '"
                                            where p.Delivery_Report_ID = "' . $id . '"
                                            GROUP BY i.Entry_ID
                                            order by i.Entry_ID

                                            ');
            while ($row = $this->db->assoc($query_details)) {
                $row['cost_arr'] = array();
                $data['Details'][$row['Entry_ID']] = $row;
            }
        }
        $data['extra_cost'] = array();
        $query_ex = $this->db->query('select c.*,
            ifnull(concat(deb.Account_Code," - ",deb.Account_Name),"") as debit_acc_name,
            ifnull(concat(cre.Account_Code," - ",cre.Account_Name),"") as credit_acc_name
            from tbl_purchase_delivery_extra_cost c
            left join tbl_chart_of_accounts deb on deb.Account_ID = c.debit_acc_id
            left join tbl_chart_of_accounts cre on cre.Account_ID = c.credit_acc_id
            where delivery_id = "' . $id . '"');
        while ($row = $this->db->assoc($query_ex)) {
            if (empty($row['delivery_detail_id'])) {
                $data['extra_cost'][] = $row;
            } else {
                $data['Details'][$row['delivery_detail_id']]['cost_arr'][] = $row;
            }
        }

//                                            echo"<pre>";
//                                            die(print_r($data));
        return $data;
    }

    public function get_order_for_delivery_note($id) {
        $data = array("Main" => array(), "Details" => array(), "Person" => array());
        $query_main = $this->db->query('select r.current_year_id,r.is_sales as Is_Customer_Invoice,
         r.Order_ID_Auto AS ID,r.Order_ID AS Auto_ID,r.Order_Date AS Date,
         r.Depot_ID,r.Supplier_ID as Person_ID,r.order_description as delivery_note,
         r.job_id,r.project_id,j.job_name,p.project_name,r.Currency_ID,cu.*
            from tbl_purchase_order r left join tbl_accounting_jobs j 
            on j.job_id = r.job_id left join tbl_projects p on p.project_id = r.project_id
            join tbl_common_currency cu on cu.Currency_ID = r.Currency_ID
            where Order_ID = "' . smc_escape($id) . '" ');
        $row = $this->db->assoc($query_main);
        if (!empty($row)) {
            $data['Main'] = $row;

            if ($data['Main']['Is_Customer_Invoice'] == 1) {
                $data['Person'] = Customer::getCustomerByID($data['Main']['Person_ID']);
            } else {
                $data['Person'] = Supplier::getSupplierByID($data['Main']['Person_ID']);
            }
            $qty_cond = '';
            if (smc_session()['default_setting']['negative_sales'] == 0)
                $qty_cond = ' having remaining_qty>0 ';
            $query_details = $this->db->query('select
                                                i.Entry_ID,
                                                p.Order_ID,p.current_rate,
                                                i.Item_Quantity_Current AS Item_Quantity_Current,
                                                ps.Product_Code AS Code,
                                                ps.Product_Name AS Name,ps.is_lot,ps.is_serial,ps.is_service,
                                                ps.Status_ID,
                                                (( 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,
                                                i.Item_ID as Product_ID,
                                                "" as product_description,
                                                uc.Bar_Code,i.item_cost,i.other_cost,
                                                (i.Quantity/i.Item_Quantity_Current) AS uom_con,
                                                 i.UOM_ID,"" as expiry_date,ps.is_expiry, "" as lot_nb, "" as serial_nb,
                                                i.UOM_ID AS Product_UOM,
                                                sum(dr.Item_Quantity_Current) as total_delivered,
                                                (i.Item_Quantity_Current - sum(dr.Item_Quantity_Current)) as remaining_qty
                                            from tbl_purchase_order_details i 
                                            join tbl_purchase_order p 
                                            on p.Order_ID = i.Order_ID
                                                  left join tbl_inventory_delivery_report_items dr on dr.order_detail_id = i.Entry_ID
                                            join uom_conversions uc ON uc.Product_ID = i.Item_ID and uc.uom_from = i.UOM_ID
                                            join tbl_stock_products ps on ps.Product_ID = i.Item_ID
                                            left join tbl_stock_product_location l on l.Location_Product_ID = i.Item_ID and l.Product_Depot_ID = "' . $data['Main']['Depot_ID'] . '"
                                            where p.Order_ID = "' . $id . '"
                                            GROUP BY i.Entry_ID
                                            ' . $qty_cond . '
                                            order by i.Entry_ID

                                            ');
                                            $row_count = $this->db->rowsno($query_details);
                                             if($row_count == 0){
                                                $query_details = $this->db->query('select
                                                i.Entry_ID,
                                                p.Order_ID,p.current_rate,
                                                i.Item_Quantity_Current AS Item_Quantity_Current,
                                                ps.Product_Code AS Code,
                                                ps.Product_Name AS Name,ps.is_lot,ps.is_serial,ps.is_service,
                                                ps.Status_ID,
                                                (( 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,
                                                i.Item_ID as Product_ID,
                                                "" as product_description,
                                                uc.Bar_Code,i.item_cost,i.other_cost,
                                                (i.Quantity/i.Item_Quantity_Current) AS uom_con,
                                                 i.UOM_ID,"" as expiry_date,ps.is_expiry, "" as lot_nb, "" as serial_nb,
                                                i.UOM_ID AS Product_UOM,
                                                0 as total_delivered,
                                                i.Item_Quantity_Current  as remaining_qty
                                            from tbl_purchase_order_details i 
                                            join tbl_purchase_order p 
                                            on p.Order_ID = i.Order_ID
                                            join uom_conversions uc ON uc.Product_ID = i.Item_ID and uc.uom_from = i.UOM_ID
                                            join tbl_stock_products ps on ps.Product_ID = i.Item_ID
                                            left join tbl_stock_product_location l on l.Location_Product_ID = i.Item_ID and l.Product_Depot_ID = "' . $data['Main']['Depot_ID'] . '"
                                            where p.Order_ID = "' . $id . '"
                                            GROUP BY i.Entry_ID
                                             
                                            order by i.Entry_ID

                                            ');
                                            }


            while ($row = $this->db->assoc($query_details)) {
                $row['unit_cost'] = ($row['item_cost'] + $row['other_cost']) * $row['current_rate'];
                $row['remaining'] = $row['Item_Quantity_Current'] - $row['total_delivered'];
                $data['Details'][] = $row;
            }
        }

//                                            echo"<pre>";
//                                            die(print_r($data));
        return $data;
    }

    public function get_order_delivered_data($order_id) {
        $data = array();
        $query_main = $this->db->query('select 
                                            current_year_id,
                                            concat(sale_prefix,Order_ID_Auto) AS ID,
                                            Order_ID AS Auto_ID,
                                            Order_Date AS Date,
                                            sale_due_date AS Due_Date,
                                            discount_percent AS Discount,
                                            discount_amount,
                                            total_vat AS VAT,
                                            sub_total AS Sub_Total,
                                            net_total AS Net_Total,
                                            Currency_ID,
                                            Currency_Symbol,
                                            Currency_Name,
                                            currency_round,
                                            Depot_ID,
                                            Supplier_ID as Person_ID,
                                            Person_Name,
                                            Class_ID,
                                            Class_Name,
                                            order_description as Invoice_Description,due_date_period ,
                                           is_ttc,supp_class_id,supp_class_name,
                                           current_rate,is_sales,created_logged_id,date_time,
                                           Order_ID_Auto,job_id,project_id,job_name,project_name,version_nb
                                        from purchase_order_list where Order_ID = "' . smc_escape($order_id) . '" ');
        $row = $this->db->assoc($query_main);
        $data['Main'] = $row;
        $emp_query = $this->db->query('select u.Employee_ID,
            concat(e.Employee_FirstName," ",e.Employee_MiddleName," ",e.Employee_LastName) as Employee_Name
            from tbl_common_users u join tbl_hrm_employee e using(Employee_ID)
            where User_ID = "' . $data['Main']['created_logged_id'] . '"');
        $emp_data = $this->db->assoc($emp_query);
        $data['Main']['Sales_ID'] = $emp_data['Employee_ID'];
        $data['Main']['Employee_Name'] = $emp_data['Employee_Name'];
        if ($data['Main']['is_sales']) {
            $data['Person'] = Customer::getCustomerByID($data['Main']['Person_ID']);
        } else {
            $data['Person'] = Supplier::getSupplierByID($data['Main']['Person_ID']);
        }

        $query_details = $this->db->query('select i.Entry_ID AS Entery_ID, p.Delivery_Report_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_Name AS Product_Name,ps.Product_Limit_Discount,
          ps.is_tax,ps.is_lot,ps.is_serial,ps.Status_ID,ps.Product_VAT AS VAT,ps.is_service,
          (( 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,
           i.Product_ID,i.description as product_description,uc.Bar_Code,
           (i.Product_Delivery_Quantity/i.Item_Quantity_Current) AS uom_con,i.UOM_ID,"" as expiry_date,ps.is_expiry,
           0 as unit_tax,0 as total_tax,i.lot_nb,i.serial_nb,
          GROUP_CONCAT(uom_from separator "/") AS Product_UOM,
          "" as TAG,"" as add_ons,"hidden" as link_product_class,
          i.unit_cost as item_cost,0 as other_cost,i.unit_cost
          from tbl_inventory_delivery_report_items i 
          left join tbl_inventory_delivery_report p on p.Delivery_Report_ID = i.Delivery_Report_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 = "' . $data['Main']['Depot_ID'] . '"
          where p.current_year_id = "' . $this->year_id . '" and p.Order_ID = "' . $order_id . '"
          GROUP BY i.Entry_ID order by i.Entry_ID');
        //        $query_details = $this->db->query('select * from invoice_details where Invoice_ID_Auto = "'.smc_escape($id).'"');
        while ($row = $this->db->assoc($query_details)) {
            $query_det = $this->db->query('select i.vat_percent,i.vat_amount,i.discount_percent  AS Product_Discount,
                i.Unit_Price AS Product_Net_Price,i.total_price AS Product_Total_Price,i.supp_class_id,pc.Class_Name
                from tbl_purchase_order_details i  left join tbl_pricing_class pc on pc.Class_ID = i.supp_class_id
                where Item_ID = "' . $row['Product_ID'] . '" and UOM_ID = "' . $row['UOM_ID'] . '"
                    and Order_ID = "' . $order_id . '" limit 1');
            $row_data = $this->db->assoc($query_det);
            $row['supp_class_name'] = $row_data['Class_Name'];
            $row['vat_percent'] = $row_data['vat_percent'];
            $row['vat_amount'] = $row_data['vat_amount'];
            $row['Product_Discount'] = $row_data['Product_Discount'];
            $row['Product_Net_Price'] = $row_data['Product_Net_Price'];
            $row['Product_Total_Price'] = $row_data['Product_Net_Price'] * $row['Item_Quantity_Current'];
            $row['supp_class_id'] = $row_data['supp_class_id'];
            $row['ttc_price'] = $row_data['Product_Net_Price'] * (1 + ($row_data['vat_percent'] / 100));
            $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 (!is_null($data['Main']['Person_ID']) && $this->permissions->hasPermission('view_sales_last_price') && $data['Main']['is_sales'] == 1) {
                $last_price = CommonBusiness::viewLastProductCustomerClassPrice($row["Product_ID"], $data['Main']['Person_ID'], $data['Main']['Class_ID']);
            }
            $row['last_price'] = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies(smc_session()['local_currency'], $data['Main']['Currency_ID'], $last_price);


            //            $row['unit_cost'] = CommonBusiness::viewCurrentProductCost($row['Product_ID']) / $data['Main']['current_rate'];
            $data['Details'][$row['Entery_ID']] = $row;
        }
        //        echo"<pre>";die(print_r($data));
        return $data;
    }

    public function check_delivery_invoice($id, $is_sales) {
        //        echo $id.'==='.$is_sales;die();
        $row_count = 0;
        if ($is_sales == 1) {
            $query = $this->db->query('select Delivery_Report_ID 
                from tbl_invoice_products_details where Delivery_Report_ID = "' . $id . '"');
            $row_count = $this->db->rowsno($query);
        } else {
            $query = $this->db->query('select Delivery_Report_ID 
                from tbl_inventory_purchase_items where Delivery_Report_ID = "' . $id . '"');
            $row_count = $this->db->rowsno($query);
        }
        return $row_count > 0 ? true : false;
    }

    public function check_delivered_ordered_items($order_id, $products, $delivery_id = null) {
        //        return true;
        $order_data = array();
        $delivery_data = array();
        $cond = ' ';
        if (!empty($delivery_id))
            $cond.=' and Delivery_Report_ID <> "' . $delivery_id . '" ';
        $query_order = $this->db->query('select 
            Item_ID,Quantity from tbl_purchase_order_details where Order_ID = "' . $order_id . '"');
        while ($row = $this->db->assoc($query_order)) {
            if (empty($order_data[$row['Item_ID']]))
                $order_data[$row['Item_ID']] = $row['Quantity'];
            else
                $order_data[$row['Item_ID']] +=$row['Quantity'];
        }
        $query_delivery = $this->db->query('select i.Product_ID,i.Product_Delivery_Quantity
            from tbl_inventory_delivery_report_items i 
            join tbl_inventory_delivery_report d using(Delivery_Report_ID)
            where d.Order_ID = "' . $order_id . '" ' . $cond);
        while ($row = $this->db->assoc($query_delivery)) {
            if (empty($delivery_data[$row['Product_ID']]))
                $delivery_data[$row['Product_ID']] = $row['Product_Delivery_Quantity'];
            else
                $delivery_data[$row['Product_ID']] +=$row['Product_Delivery_Quantity'];
        }
        foreach ($products as $item) {
            $ProductQuantity = Product::getConvertedQuantity($item['UOM_ID'], $item['Item_Quantity'], $item['Product_ID']);
            if (empty($delivery_data[$item['Product_ID']]))
                $delivery_data[$item['Product_ID']] = $ProductQuantity;
            else
                $delivery_data[$item['Product_ID']] += $ProductQuantity;
        }
        $flag = false;
        foreach ($delivery_data as $prod_id => $qty) {
            if ($qty > $order_data[$prod_id])
                $flag = true;
        }
        return $flag;
        //        $diff_arr = array_diff_key($delivery_data, $order_data);
        //        echo"<pre>";die(print_r($diff_arr));
    }

    public function check_delivered_purchase_items($purchase_id, $products) {
        //        return true;
        $purchase_data = array();
        $delivery_data = array();
        $query_order = $this->db->query('select 
            Product_ID,Item_Quantity from tbl_inventory_purchase_items 
            where Purchase_ID_Auto = "' . $purchase_id . '"');
        while ($row = $this->db->assoc($query_order)) {
            if (empty($purchase_data[$row['Product_ID']]))
                $purchase_data[$row['Product_ID']] = $row['Item_Quantity'];
            else
                $purchase_data[$row['Product_ID']] +=$row['Item_Quantity'];
        }
//        echo"<pre>";die(print_r($purchase_data));
        foreach ($products as $item) {
            $ProductQuantity = Product::getConvertedQuantity($item['UOM_ID'], $item['Item_Quantity'], $item['Product_ID']);
            if (empty($delivery_data[$item['Product_ID']]))
                $delivery_data[$item['Product_ID']] = $ProductQuantity;
            else
                $delivery_data[$item['Product_ID']] += $ProductQuantity;
        }
        $flag = false;
        foreach ($delivery_data as $prod_id => $qty) {
            if ($qty > $purchase_data[$prod_id])
                $flag = true;
        }
        return $flag;
    }

    //public function get_delivered_to_invoice($id) {
    //        $data = array("Main" => array(), "Details" => array(), "Person" => array());
    //        $query_main = $this->db->query('select r.current_year_id,r.Is_Customer_Invoice,
    //         r.delivery_nb AS ID,r.Delivery_Report_ID AS Auto_ID,r.Delivery_Report_Date AS Date,
    //         r.Delivery_Report_Date AS Due_Date,
    //         r.Depot_ID,r.Person_ID,r.delivery_note,r.job_id,r.project_id,j.job_name,p.project_name,r.delivery_ref
    //            from tbl_inventory_delivery_report r left join tbl_accounting_jobs j 
    //            on j.job_id = r.job_id left join tbl_projects p on p.project_id = r.project_id
    //            where Delivery_Report_ID = "' . smc_escape($id) . '" ');
    //        $row = $this->db->assoc($query_main);
    //        if (!empty($row)) {
    //            $row['Discount']=0;
    //            $row['discount_amount']=0;
    //            $row['Customer_ID']=$row['Supplier_ID'] =$row['Person_ID'];
    //            $data['Main'] = $row;
    //
    //            if ($data['Main']['Is_Customer_Invoice'] == 1) {
    //                $data['Person'] = Customer::getCustomerByID($data['Main']['Person_ID']);
    //            } else {
    //                $data['Person'] = Supplier::getSupplierByID($data['Main']['Person_ID']);
    //            }
    //            $query_details = $this->db->query('select
    //                                                i.Entry_ID,
    //                                                p.Delivery_Report_ID,
    //                                                i.Item_Quantity_Current AS Item_Quantity_Current,
    //                                                ps.Product_Code AS Code,
    //                                                ps.Product_Name AS Name,ps.is_lot,ps.is_serial,
    //                                                ps.Status_ID,
    //                                                (( 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,
    //                                                i.Product_ID,i.order_detail_id,
    //                                                i.description as product_description,
    //                                                uc.Bar_Code,i.unit_cost,
    //                                                (i.Product_Delivery_Quantity/i.Item_Quantity_Current) AS uom_con,
    //                                                 i.UOM_ID,"" as expiry_date,ps.is_expiry,i.lot_nb,i.serial_nb,
    //                                                GROUP_CONCAT(uom_from separator "/") AS Product_UOM
    //                                            from tbl_inventory_delivery_report_items i 
    //                                            join tbl_inventory_delivery_report p 
    //                                            on p.Delivery_Report_ID = i.Delivery_Report_ID
    //                                            join uom_conversions uc ON uc.Product_ID = i.Product_ID
    //                                            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 = "' . $data['Main']['Depot_ID'] . '"
    //                                            where p.Delivery_Report_ID = "' . $id . '"
    //                                            GROUP BY i.Entry_ID
    //                                            order by i.Entry_ID
    //
    //                                            ');
    //            while ($row = $this->db->assoc($query_details)) {
    //                $data['Details'][] = $row;
    //            }
    //        }
    //
    ////                                    echo"<pre>";
    ////                                    die(print_r($data));
    //        return $data;
    //    }
    public function deleteDeliveryNote() {
        $this->db->query('START TRANSACTION');
        $invoice_count = $purchase_count = 0;
        if ($this->info['is_vendor'] == 0) {
            $invoice_query = $this->db->query('select * from tbl_invoice_products_details 
                                            where Delivery_Report_ID = "' . $this->info['Delivery_Report_ID'] . '" limit 1');
            $invoice_count = $this->db->rowsno($invoice_query);
        } else {
            $purchase_query = $this->db->query('select * from tbl_inventory_purchase_items 
                                            where Delivery_Report_ID = "' . $this->info['Delivery_Report_ID'] . '" limit 1');
            $purchase_count = $this->db->rowsno($purchase_query);
        }
        if ($invoice_count > 0 || $purchase_count > 0)
            return [CommonLanguage::getMessage('unable_delete_delivery_note')];
        //
        $query = $this->db->query('select Delivery_Report_Date,is_actual from tbl_inventory_delivery_report 
            where Delivery_Report_ID = "'.$this->info['Delivery_Report_ID'].'"');
        $row = $this->db->assoc($query);
        $check = CommonFunctions::check_lock_data(['type' => 'delivery', 'start_date' => $row['Delivery_Report_Date'], 'is_actual' => $row['is_actual']]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $query_det = $this->db->prepare_Stmt('select d.*,p.date_time,p.Delivery_Report_Date,
            p.Depot_ID,p.is_actual,p.Is_Customer_Invoice,pr.is_service,p.Currency_ID,p.current_rate,
            p.delivery_nb,p.Person_ID
            from tbl_inventory_delivery_report_items d
            join tbl_inventory_delivery_report p using(Delivery_Report_ID)
            join tbl_stock_products pr on pr.Product_ID = d.Product_ID
            where Delivery_Report_ID = ?', [&$this->info['Delivery_Report_ID']]);
        while ($row_det = $this->db->assoc($query_det)) {
            $old_expiry_date = !empty($row_det['expiry_date']) ? $row_det['expiry_date'] : null;
            $costdata = [
                "Product_ID" => $row_det['Product_ID'],
                "Quantity" => $row_det['Product_Delivery_Quantity'],
                "Unit_Cost" => $row_det['real_cost'],
                "Currency_ID" => $row_det['Currency_ID'],
                "date_time" => $row_det['date_time'],
                "current_rate" => $row_det['current_rate'],
                "depot_id" => $row_det['Depot_ID'],
                "trans_date" => $row_det['Delivery_Report_Date'],
                "current_quantity" => $row_det['Item_Quantity_Current'],
                "uom_id" => $row_det['UOM_ID'],
                "is_actual" => $row_det['is_actual'],
                'lot_nb' => $row_det['lot_nb'],
                'serial_nb' => $row_det['serial_nb'],
                "expiry_date" => $old_expiry_date,
                "main_id" => $this->info['Delivery_Report_ID'],
                "is_service" => $row_det['is_service'],
                "main_nb" => $row_det['delivery_nb'],
                "customer_id" => ($row_det['Is_Customer_Invoice'] == 1 ? $row_det['Person_ID'] : null ),
                "supplier_id" => ($row_det['Is_Customer_Invoice'] == 0 ? $row_det['Person_ID'] : null )
            ];
            CommonBusiness::updateLinkQuantities($costdata, ($row_det['Is_Customer_Invoice'] == 1 ? 'delete_customer_delivery' : 'delete_supplier_delivery'), ($row_det['Is_Customer_Invoice'] == 1 ? '+' : '-'));
        }

        $this->db->prepare_Stmt('delete from tbl_inventory_delivery_report_items 
                                 where Delivery_Report_ID = ?', [&$this->info['Delivery_Report_ID']]);
        $this->db->prepare_Stmt('delete from tbl_inventory_delivery_report
                                 where Delivery_Report_ID = ?', [&$this->info['Delivery_Report_ID']]);
        CommonBusiness::update_trans_record_current(($this->info['is_vendor'] == 1 ? 'dls' : 'dlc'), $this->info['Delivery_Report_ID']);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query("COMMIT");
        } else {
            $this->db->query("ROLLBACK");
        }
        return array_unique($error);
    }

    public function check_purchase_converted($pid) {
        $is_converted = 0;
        $query = $this->db->query('select purchase_id from tbl_inventory_delivery_report
            where purchase_id = "' . $pid . '"');
        $row_count = $this->db->rowsno($query);
        if (!empty($row_count))
            $is_converted = 1;
//        echo $is_converted.'==';die();
        return $is_converted;
    }

    public function check_purchase_is_from_delivery($pid) {
        $is_delivery = 0;
        $query = $this->db->query('select * from tbl_inventory_purchase_items
            where Purchase_ID_Auto = "' . $pid . '" and Delivery_Report_ID is not null and Delivery_Report_ID <>0');
        $row_count = $this->db->rowsno($query);
        if (!empty($row_count))
            $is_delivery = 1;
//        echo $is_delivery.'==';die();
        return $is_delivery;
    }

    public function check_delivery_converted($delivery_id, $is_sales) {
        if (!$is_sales) {
            $query = $this->db->query('select p.Purchase_ID as rec_nb from tbl_inventory_purchase_items i
            join tbl_inventory_purchases p on p.Purchase_ID_Auto = i.Purchase_ID_Auto
            where Delivery_Report_ID = "' . $delivery_id . '" limit 1');
        } else {
            $query = $this->db->query('select concat_ws(i.invoice_prefix,i.Invoice_ID) as rec_nb
               from tbl_invoice_products_details d
               join tbl_invoices i on i.Invoice_ID_Auto = d.Invoice_ID_Auto
            where Delivery_Report_ID = "' . $delivery_id . '" limit 1');
        }
        $row = $this->db->assoc($query);
        $rec_nb = $row['rec_nb'];
        return $rec_nb;
    }

    public function get_delivery_to_invoice($delivery_id) {
        $data = array();
        $query_main = $this->db->query('select r.current_year_id,r.`delivery_nb` AS ID,r.`Delivery_Report_ID` AS Auto_ID,r.`Delivery_Report_Date` AS Date,
            r.`Delivery_Report_Date` AS Due_Date, 0 AS Discount,0 as discount_amount,0  AS VAT,0 AS Sub_Total,
             0 AS Net_Total,cu.Currency_ID,cu.Currency_Symbol,cu.Currency_Name,cu.currency_round,r.Depot_ID,
             r.Person_ID,r.`delivery_note` as Invoice_Description,
             r.current_rate,r.`Is_Customer_Invoice` as is_sales,
             r.created_logged_id,r.date_time,r.job_id,r.project_id,j.job_name,p.project_name,r.Person_ID as Customer_ID,
                         r.cost_dist_method

            from tbl_inventory_delivery_report r join tbl_common_currency cu on cu.Currency_ID = r.Currency_ID
            left join tbl_accounting_jobs j on j.job_id = r.job_id
            left join tbl_projects p on p.project_id = r.project_id
            where Delivery_Report_ID = "' . smc_escape($delivery_id) . '" ');
        $row = $this->db->assoc($query_main);
        $data['Main'] = $row;
        $emp_query = $this->db->query('select u.Employee_ID,
            concat(e.Employee_FirstName," ",e.Employee_MiddleName," ",e.Employee_LastName) as Employee_Name
            from tbl_common_users u join tbl_hrm_employee e using(Employee_ID)
            where User_ID = "' . $data['Main']['created_logged_id'] . '"');
        $emp_data = $this->db->assoc($emp_query);
        $data['Main']['Sales_ID'] = $emp_data['Employee_ID'];
        $data['Main']['Employee_Name'] = $emp_data['Employee_Name'];
        if ($data['Main']['is_sales']) {
            $data['Person'] = Customer::getCustomerByID($data['Main']['Person_ID']);
        } else {
            $data['Person'] = Supplier::getSupplierByID($data['Main']['Person_ID']);
        }

        $query_details = $this->db->query('select i.Entry_ID AS Entery_ID, p.Delivery_Report_ID AS Invoice_ID_Auto,
          i.Item_Quantity_Current AS Item_Quantity_Current,ps.Product_Code AS Code,ps.vat_percent,
          ps.Product_Name AS Name,ps.Product_Name AS Product_Name,ps.Product_Limit_Discount,
          ps.is_tax,ps.is_lot,ps.is_serial,ps.Status_ID,ps.Product_VAT AS VAT,ps.is_service,
          (( 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,
           i.Product_ID,i.description as product_description,uc.Bar_Code,
           (i.Product_Delivery_Quantity/i.Item_Quantity_Current) AS uom_con,i.UOM_ID,"" as expiry_date,ps.is_expiry,
           0 as unit_tax,0 as total_tax,i.lot_nb,i.serial_nb,
          GROUP_CONCAT(uom_from separator "/") AS Product_UOM,0 as Product_Discount,
          "" as TAG,"" as add_ons,"hidden" as link_product_class,l.Product_Depot_Quantity as depot_qty,
          i.unit_cost as item_cost,0 as other_cost,i.unit_cost,p.Depot_ID as depot_id,"" as Product_Net_Price,"" as vat_amount
          from tbl_inventory_delivery_report_items i 
          left join tbl_inventory_delivery_report p on p.Delivery_Report_ID = i.Delivery_Report_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 = "' . $data['Main']['Depot_ID'] . '"
          where p.current_year_id = "' . $this->year_id . '" and p.Delivery_Report_ID = "' . $delivery_id . '"
          GROUP BY i.Entry_ID order by i.Entry_ID');
        //        $query_details = $this->db->query('select * from invoice_details where Invoice_ID_Auto = "'.smc_escape($id).'"');
        while ($row = $this->db->assoc($query_details)) {

            $last_cost = CommonBusiness::viewLastProductCost($row["Product_ID"]);
            $row['LAST_COST'] = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies(smc_session()['local_currency'], $data['Main']['Currency_ID'], $last_cost);
           
            $data['Details'][$row['Entery_ID']] = $row;
        }
//                echo"<pre>";die(print_r($data));
        return $data;
    }

}
