<?php

class WarehouseDepot {

    protected $info = array();
    protected $AllAccounts = array();
    private $db;
    private $year_id;
    public $stop_transaction_start_end = false;

    public function __construct() {
        $this->db = DBConnectorFactory::loadDriver();
        $this->perm = new Permissions(Session::getUserID());
        $this->year_id = Session::getCurrentYearID();
    }

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

        return $this;
    }

    public function addDepot() {
        $this->db->query('START TRANSACTION');
        $logged_ID = Session::getUserID();
        $depotDetails = [&$this->info['Depot_Name'], &$this->info['Depot_Location'], &$this->info['Depot_Area'],
            &$this->info['Depot_Code'], &$this->info['Depot_Max_Num_Goods'], &$this->info['Depot_Min_Num_Goods'],
            &$this->info['Depot_UOM'], &$this->info['Depot_PIC_ID'], &$this->info['Depot_Category_ID'],
            &$this->info['Depot_Type_Of_Storage'], &$logged_ID, &$this->info['branch_id']];
        $this->db->prepare_Stmt('INSERT INTO tbl_stock_depot_warehouse
                                            (

                                                Depot_Name,
                                                Depot_Location,
                                                Depot_Area,
                                                Depot_Code,
                                                Depot_Max_Num_Goods,
                                                Depot_Min_Num_Goods,
                                                Depot_UOM,
                                                Depot_PIC_ID,
                                                Depot_Category_ID,
                                                Depot_Type_Of_Storage,
                                                Logged_ID,
                                                branch_id
                                            )
                                                VALUES
                                            (?,?,?,?,?,?,?,?,?,?,?,?)
                                    ', $depotDetails);
        $depot_id = $this->db->id();
        if (!empty($this->info['shelfs_name'])) {
            $this->info['shelfs_name'] = json_decode($this->info['shelfs_name'], true);
            foreach ($this->info['shelfs_name'] as $shelf) {
                $data = [
                    &$depot_id,
                    &$shelf['shelf_name']
                ];
                $this->db->prepare_Stmt('insert into tbl_shelfs_depot (depot_id,shelf_name) values(?,?)', $data);
            }
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function updateDepot() {
        $this->db->query('START TRANSACTION');
        $check = self::checkDepotIfUsed($this->info['Depot_ID']);
        $query = $this->db->query('select branch_id from tbl_stock_depot_warehouse where Depot_ID = "' . $this->info['Depot_ID'] . '"');
        $old_branch_id = $this->db->assoc($query)['branch_id'];
        if (($old_branch_id != $this->info['branch_id']) && $check) {
            $error[] = CommonLanguage::getMessage('check_depot');
            return $error;
        }
        $logged_ID = Session::getUserID();
        $depotDetails = [&$this->info['Depot_Name'], &$this->info['Depot_Location'], &$this->info['Depot_Area'], &$this->info['Depot_Code'],
            &$this->info['Depot_Max_Num_Goods'], &$this->info['Depot_Min_Num_Goods'], &$this->info['Depot_UOM'], &$this->info['Depot_PIC_ID'],
            &$this->info['Depot_Category_ID'], &$this->info['Depot_Type_Of_Storage'], &$logged_ID, &$this->info['branch_id'], &$this->info['Depot_ID']];
        $this->db->prepare_Stmt('UPDATE 
                                tbl_stock_depot_warehouse
                                SET
                                       Depot_Name              =  ?,
                                       Depot_Location          =  ?,
                                       Depot_Area              =  ?,
                                       Depot_Code              =  ?,
                                       Depot_Max_Num_Goods     =  ?,
                                       Depot_Min_Num_Goods     =  ?,
                                       Depot_UOM               =  ?,
                                       Depot_PIC_ID            =  ?,
                                       Depot_Category_ID       =  ?,
                                       Depot_Type_Of_Storage   =  ?,
                                       Logged_ID               =  ?,
                                       branch_id               =  ?
                                     WHERE
                                         Depot_ID            = ? 
                                ', $depotDetails);

        $shelf_depot_ids = array();
        if (!empty($this->info['shelfs_name'])) {
            $this->info['shelfs_name'] = json_decode($this->info['shelfs_name'], true);
            foreach ($this->info['shelfs_name'] as $shelf) {
                if (!empty($shelf['shelf_depot_id'])) {
                    $data = [
                        &$shelf['shelf_name'],
                        &$this->info['Depot_ID'],
                        &$shelf['shelf_depot_id']
                    ];
                    $this->db->prepare_Stmt('update tbl_shelfs_depot set shelf_name = ? where depot_id = ? and shelf_depot_id = ?', $data);
                    $shelf_depot_ids[] = $shelf['shelf_depot_id'];
                } else {
                    $data = [
                        &$this->info['Depot_ID'],
                        &$shelf['shelf_name']
                    ];
                    $this->db->prepare_Stmt('insert into tbl_shelfs_depot (depot_id,shelf_name) values(?,?)', $data);
                    $shelf_depot_ids[] = $this->db->id();
                }
            }
        }
        $select_query = $this->db->prepare_Stmt('select * from tbl_shelfs_depot where depot_id = ? ' . (empty($shelf_depot_ids) ? '' : ' and shelf_depot_id not in (' . implode(',', $shelf_depot_ids) . ') ') . ' ', [&$this->info['Depot_ID']]);
        if ($this->db->rowsno($select_query) > 0) {
            $del_shelf_depot_ids = array();
            while ($row = $this->db->assoc($select_query))
                $del_shelf_depot_ids[] = $row['shelf_depot_id'];
            $product_query = $this->db->prepare_Stmt('select Product_ID from tbl_product_shelf where shelf_depot_id in (' . implode(',', $del_shelf_depot_ids) . ') limit 1');
            if ($this->db->rowsno($product_query) > 0) {
                $error[] = CommonLanguage::getMessage('check_product_shelf_location');
                return $error;
            } else {
                $this->db->prepare_Stmt('delete from tbl_shelfs_depot where shelf_depot_id in (' . implode(',', $del_shelf_depot_ids) . ') ');
            }
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function addDepotCategory() {
        $this->db->prepare_Stmt('INSERT INTO tbl_stock_categories
                                            (Category_Name)
                                                VALUES
                                            (?)
                                    ', [&$this->info['Depot_Category']]);
        return $this->db->error();
    }

    public function getLastOfType($table, $ColName) {
        $query = $this->db->query("SELECT * FROM $table ORDER BY $ColName DESC limit 1");
        $row = $this->db->assoc($query);
        $retrievedData[] = $row;

        return $retrievedData;
    }

   public function addProductLocation() {
       $date = Date::getToday();
       if (!$this->stop_transaction_start_end)
           $this->db->query('START TRANSACTION');
       //        $total = 0.0;
       $mydate = date("Y-m-d H:i:s");
       $this->info['trans_note'] = !empty($this->info['trans_note']) ? $this->info['trans_note'] : '';
       foreach ($this->info['Products'] as $item) {
           $product_quantity = Product::getConvertedQuantity($item['UOM_ID'], $item['Item_Quantity'], $item['Product_ID']);
           //            $total += $product_quantity;
           $rate = CurrencyFactory::getLastExRate($item['Currency_ID']);
           $expiry_date = !empty($item['expiry_date']) ? $item['expiry_date'] : null;
           $lot_nb = !empty($item['lot_nb']) ? $item['lot_nb'] : '';
           $serial_nb = !empty($item['serial_nb']) ? $item['serial_nb'] : '';
           $new_cost = $item['Item_Cost'] / ($product_quantity / $item['Item_Quantity']);
           $costdata = [
               "Product_ID" => $item['Product_ID'],
               "Quantity" => $product_quantity,
               "Unit_Cost" => $new_cost,
               "Currency_ID" => $item['Currency_ID'],
               "date_time" => $mydate,
               "date_time_now" => $mydate,
               "current_rate" => $rate,
               "depot_id" => $item['Depot_ID'],
               "trans_date" => $date,
               "current_quantity" => $item['Item_Quantity'],
               "uom_id" => $item['UOM_ID'],
               "is_actual" => 0,
               "expiry_date" => $expiry_date,
               "lot_nb" => $lot_nb,
               "serial_nb" => $serial_nb,
               "main_id" => 0,
               "is_service" => 0,
               "trans_note" => $this->info['trans_note']
           ];
           CommonBusiness::addCostByType($costdata, 'assign');

           $main = [&$product_quantity, &$item['Product_ID']];
           $this->db->prepare_Stmt("UPDATE 
                                   tbl_stock_products
                           SET 
                                   Product_Quantity = Product_Quantity + ?
                           WHERE 
                                   Product_ID = ?
                           ", $main);
           $location = [
               &$item['Product_ID'],
               &$item['Depot_ID'],
               &$product_quantity,
               &$product_quantity
           ];
           $this->db->prepare_Stmt("INSERT INTO tbl_stock_product_location
                                   (
                                           Location_Product_ID,
                                           Product_Depot_ID,
                                           Product_Depot_Quantity
                                   )
                                           VALUES
                                   (
                                           ?,?,?
                                   )
                               ON DUPLICATE KEY
                               UPDATE Product_Depot_Quantity = Product_Depot_Quantity + ?


                                   ", $location);
       }
       $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 array_unique($error);
   }
   public function multiProductDamaged() {
       $this->db->query('START TRANSACTION');
       $this->stop_transaction_start_end = true;
       foreach ($this->info['Products'] as $item) {
           $item['logged_id'] = Session::getUserID();
           $this->setData($this->info)->add_damage();
       }
       $error = $this->db->error();
       if (empty($error)) {
           $this->db->query("COMMIT");
       } else {
           $this->db->query("ROLLBACK");
       }
       return array_unique($error);
   }

    public function getDepots($flag = null) {
        $data = array();
        $query = $this->db->query('SELECT 
                                        d.*,
                                        concat(d.Depot_Code," - ",d.Depot_Name) as Depot_Name,
                                        e.*,
										c.*,
                                        u.*,
                                        CONCAT(e.Employee_FirstName, " ", e.Employee_MiddleName, " ", e.Employee_LastName, " ") AS Employee_Name

                                    FROM 
                                        tbl_stock_depot_warehouse d
                                    LEFT JOIN 
                                        tbl_hrm_employee e
                                    ON
                                        e.Employee_ID= d.Depot_PIC_ID
                                    LEFT JOIN 
                                        tbl_stock_uom u
                                    ON
                                        u.UOM_ID = d.Depot_UOM
                                    LEFT JOIN 
                                        tbl_stock_categories c
                                    ON
                                        c.Category_ID = d.Depot_Category_ID
                                    WHERE 
                                        d.Status_ID = 1
                                    ORDER BY
                                        d.Depot_ID


                ');
        $i = 0;
        while ($row = $this->db->assoc($query)) {
            if (is_null($flag)) {
                $data[$row['Depot_ID']] = $row;
            } else {
                $data[$i][0] = $row['Depot_ID'];
                $data[$i][1] = $row['Depot_Code'];
                $data[$i][2] = $row['Depot_Name'];
                $data[$i][3] = $row['Category_Name'];
                $data[$i][4] = $row['Depot_Type_Of_Storage'];
                $data[$i][5] = $row['Employee_Name'];
                $i++;
            }
        }

        return $data;
    }

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

    public function getAllDepot($branch_id = null) {
        $data = array();
        $cond = ' ';
        if (!empty($branch_id))
            $cond .= ' and branch_id = "' . $branch_id . '"';
        $query = $this->db->query('SELECT *,concat(Depot_Code," - ",Depot_Name) as Depot_Name FROM tbl_stock_depot_warehouse WHERE Status_ID = 1 ' . $cond);
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function getDepot($CatID) {
        $query = $this->db->query('SELECT * FROM tbl_stock_depot_warehouse WHERE Status_ID = 1 AND  Depot_Category_ID = "' . $CatID . '"');
        $result = $this->db->rowsno($query);
        if ($result == 0)
            return array();
        else {


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

    public function getProductLocation($flag = null) {
        $data = array();

        $query = $this->db->query('SELECT 

                                        l.*,
                                        d.*,
                                        p.*
                                    FROM 
                                        tbl_stock_product_location l
                                    LEFT JOIN 
                                        tbl_stock_products p
                                    ON
                                        l.Location_Product_ID = p.Product_ID
                                    LEFT JOIN 
                                        tbl_stock_depot_warehouse d
                                    ON
                                        l.Product_Depot_ID = d.Depot_ID
                                    WHERE 
                                        d.Status_ID = 1 
                                    AND 
                                        l.Status_ID = 1
									GROUP BY
										l.Location_Product_ID
                ');
        $i = 0;
        while ($row = $this->db->assoc($query))
            if (is_null($flag)) {
                $data[] = $row;
            } else {
                $data[$i][0] = $row['Product_ID'];
                $data[$i][1] = $row['Product_Code'];
                $data[$i][2] = $row['Product_Name'];
                $data[$i][3] = $row['Product_Quantity'];
                $i++;
            }

        return $data;
    }

    public function getProductLocationInfo($code = null, $id = null) {
        $condition = '';
        if (!is_null($code))
            $condition .= ' AND Product_Code = "' . $code . '"';
        if (!is_null($id))
            $condition .= ' AND Product_ID = "' . $id . '"';

        $data = array();
        $query = $this->db->query('SELECT
                                        l.*,
                                        d.*,
                                        p.*
                                    FROM 
                                        tbl_stock_product_location l
                                    LEFT JOIN 
                                        tbl_stock_products p
                                    ON
                                        l.Location_Product_ID = p.Product_ID
                                    LEFT JOIN 
                                        tbl_stock_depot_warehouse d
                                    ON
                                        l.Product_Depot_ID = d.Depot_ID
                                    WHERE 
                                        d.Status_ID = 1 
                                    AND 
                                        l.Status_ID = 1
                                    AND 
                                        l.Parent_ID = 0


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

    public function getDepotInfo($ID) {
        $data = array();
        $query = $this->db->query('SELECT 
                                        d.*,
                                        e.Employee_ID,
                                        e.Employee_Name,
                                        c.*,
                                        u.*,
                                        b.*
                                    FROM 
                                        tbl_stock_depot_warehouse d
                                    LEFT JOIN 
                                        view_employees e
                                    ON
                                        e.Employee_ID= d.Depot_PIC_ID

                                    LEFT JOIN 
                                        tbl_stock_uom u
                                    ON
                                        u.UOM_ID = d.Depot_UOM
                                    LEFT JOIN 
                                        tbl_stock_categories c
                                    ON
                                        c.Category_ID = d.Depot_Category_ID
                                        left join tbl_organization_branches b
                                        on b.branch_id = d.branch_id
                                    WHERE 
                                        d.Depot_ID = "' . $ID . '"
                                    AND 
                                        d.Status_ID = 1 
                    ');
        $row = $this->db->assoc($query);
        $data[] = $row;
        $query_shelf = $this->db->prepare_Stmt('select * from tbl_shelfs_depot where depot_id = ? order by shelf_depot_id asc', [&$row['Depot_ID']]);
        while ($row_shelf = $this->db->assoc($query_shelf)) {
            $data['shelfs'][] = $row_shelf;
        }
        return $data;
    }

    public function getMaxRequestID($request_prefix) {
        $query = $this->db->query('select MAX(Request_Number) as max_request from tbl_stock_request
            where serial_prefix = "' . $request_prefix . '"');
        $row = $this->db->assoc($query);
        if (!empty($row))
            $request = $row['max_request'] + 1;
        else
            $request = 1;

        return $request;
    }

    public function getMaxDamageID($damage_prefix, $is_actual = 0) {
        $query = $this->db->query('select MAX(damage_nb) as max_nb from tbl_damage
            where damage_prefix = "' . $damage_prefix . '" and is_actual = "' . $is_actual . '"');
        $row = $this->db->assoc($query);
        if (!empty($row))
            $damage = $row['max_nb'] + 1;
        else
            $damage = 1;
        return $damage;
    }

    public function createRequest() {
        $error = token_unique::is_valid_token();
        if (!empty($error))
            return $error;
        $request_date = isset($this->info['Request_Date']) ? $this->info['Request_Date'] : Date::getToday();

        $check = CommonFunctions::check_lock_data(['type' => 'request', 'start_date' => $request_date, 'is_actual' => 0]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            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']]);
//            }
//        }
        //        sleep(5);
        $app_user_id = isset($this->info['app_user_id']) ? $this->info['app_user_id'] : 0;
        $is_main_transfer = isset($this->info['is_main_transfer']) ? $this->info['is_main_transfer'] : 0;
        $to_branch_id = isset($this->info['to_branch_id']) ? $this->info['to_branch_id'] : CommonBusiness::getBranchByDepot($this->info['Request_To']);
        if (isset($this->info['request_prefix']))
            $request_prefix = $this->info['request_prefix'];
        else {
            $query_branch = $this->db->query('select branch_prefix from tbl_organization_branches where branch_id = "' . $to_branch_id . '"');
            $branch_prefix = $this->db->assoc($query_branch)['branch_prefix'];
            $request_prefix = smc_session()['user_accounts']['serial_prefix'] ? smc_session()['user_accounts']['serial_prefix'] : $branch_prefix;
        }
        $app_request_id = isset($this->info['app_request_id']) ? $this->info['app_request_id'] : 0;
        $online_request_id = isset($this->info['online_request_id']) ? $this->info['online_request_id'] : null;
        $app_branch_id = isset($this->info['app_branch_id']) ? $this->info['app_branch_id'] : 0;
        $this->info['Invoice_ID_Auto'] = !empty($this->info['Invoice_ID_Auto']) ? $this->info['Invoice_ID_Auto'] : null;
        //        $request_prefix = isset($this->info['request_prefix']) ? $this->info['request_prefix'] : smc_session()['user_accounts']['serial_prefix'];
        $request_id = isset($this->info['Request_Number']) ? $this->info['Request_Number'] : self::getMaxRequestID($request_prefix);
        $this->info['req_nb'] = $request_prefix . $request_id;
        if (!$this->stop_transaction_start_end)
            $this->db->query('START TRANSACTION');
        $mydate = date("Y-m-d H:i:s");
        $this->info['created_logged_id'] = isset($this->info['created_logged_id']) ? $this->info['created_logged_id'] : smc_session()['user_logged_id'];
        $this->info['Logged_ID'] = isset($this->info['Logged_ID']) ? $this->info['Logged_ID'] : smc_session()['user_logged_id'];
        $this->info['date_time'] = isset($this->info['date_time']) ? $this->info['date_time'] : $mydate;
        $this->info['date_created'] = isset($this->info['date_created']) ? $this->info['date_created'] : $mydate;
        $customer_id = (isset($this->info['customer_id']) && is_numeric($this->info['customer_id'])) ? $this->info['customer_id'] : NULL;
        $ticketing_id = !empty($this->info['ticketing_id']) ? $this->info['ticketing_id'] : NULL;
        $main = [&$request_id, &$request_date,
            &$this->info['Request_From'],
            &$this->info['Request_To'],
            &$this->info['Delivery_Date'],
            &$this->info['Logged_ID'],
            &$this->info['created_logged_id'],
            &$app_user_id,
            &$app_request_id,
            &$request_prefix,
            &$to_branch_id,
            &$this->info['date_time'],
            &$this->info['date_created'],
            &$is_main_transfer, &$app_branch_id, &$this->info['note'], &$customer_id,
            &$ticketing_id, $this->info['Invoice_ID_Auto'], $online_request_id
        ];
        $this->db->prepare_Stmt('INSERT INTO tbl_stock_request
            (' . (!empty($this->info['fill_auto_id']) ? 'Request_ID,' : '') . 'Request_Number,
            Request_Date,
            Request_From,
            Request_To,
            Delivery_Date,
            Logged_ID,created_logged_id,
            app_user_id,
            app_request_id,
            serial_prefix,to_branch_id,date_time,date_created,is_main_transfer,
            app_branch_id,Rejection_Comment,customer_id,ticketing_id,invoice_auto_id,online_request_id)
            VALUES(' . (!empty($this->info['fill_auto_id']) ? intval($this->info['fill_auto_id']) . ',' : '') . '?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', $main);
        $parent_id = $this->db->id();
        $expiry_valid_arr = array();
        $serial_valid_arr = array();
        $lot_valid_arr = array();
        $qty_valid_arr = array();
        $depot_arr = array($this->info['Request_To']);
        foreach ($this->info['Products'] as $item) {
            $expiry_date = !empty($item['expiry_date']) ? $item['expiry_date'] : null;
            $lot_nb = !empty($item['lot_nb']) ? $item['lot_nb'] : '';
            $serial_nb = !empty($item['serial_nb']) ? $item['serial_nb'] : '';
            $default_qty = Product::getConvertedQuantity($item['UOM_ID'], $item['Request_Qty'], $item['Product_ID']);
            $details = [&$parent_id, &$item['Product_ID'], &$default_qty, &$item['UOM_ID'],
                &$item['Request_Qty'], &$expiry_date, &$lot_nb, &$serial_nb];
            $this->db->prepare_Stmt('INSERT INTO tbl_stock_request_details( Request_ID,Product_ID,Request_Qty,
                Request_UOM,Current_Quantity,expiry_date,lot_nb,serial_nb)VALUES(?,?,?,?,?,?,?,?)', $details);
            $entry_id = $this->db->id();
            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'][$item['Product_ID']][$this->info['Request_To']][$expiry_date]))
                    $expiry_valid_arr['prod'][$item['Product_ID']][$this->info['Request_To']][$expiry_date] = 0;
                $expiry_valid_arr['prod'][$item['Product_ID']][$this->info['Request_To']][$expiry_date]+= $default_qty;
            }
            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'][$item['Product_ID']][$this->info['Request_To']][$serial_nb]))
                    $serial_valid_arr['prod'][$item['Product_ID']][$this->info['Request_To']][$serial_nb] = 0;
                $serial_valid_arr['prod'][$item['Product_ID']][$this->info['Request_To']][$serial_nb]+= $default_qty;
            }
            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'][$item['Product_ID']][$this->info['Request_To']][$lot_nb]))
                    $lot_valid_arr['prod'][$item['Product_ID']][$this->info['Request_To']][$lot_nb] = 0;
                $lot_valid_arr['prod'][$item['Product_ID']][$this->info['Request_To']][$lot_nb]+= $default_qty;
            }
            if (!empty($default_qty) && empty(smc_session()['default_setting']['negative_sales'])) {
                if (!isset($qty_valid_arr[$item['Product_ID']][$this->info['Request_To']]))
                    $qty_valid_arr[$item['Product_ID']][$this->info['Request_To']] = 0;
                $qty_valid_arr[$item['Product_ID']][$this->info['Request_To']]+= $default_qty;
            }
            if (!empty($item['criteria_details'])) {
                $criteria_details = $item['criteria_details'];
                foreach ($criteria_details as $details) {
                    $this->db->prepare_Stmt("INSERT INTO
                                        tbl_request_product_criteria 
                                        (Entry_ID,Quantity,criteria_detail_id,criteria_note)
                                        VALUES
                                        (?,?,?,?)
                    ", [&$entry_id, &$details['Quantity'], &$details['criteria_detail_id'],
                        &$details['criteria_note']]);
                }
            }
        }

        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];
            }
        }
        $this->info['Request_ID'] = $parent_id;
        if (!empty($this->info['is_confirmed'])) {
            self::confirmTransfer(1, 1);
        }


        if (!empty($this->info['is_data_collector'])) {
            $update = [&$this->info['file_id']];
            $this->db->prepare_Stmt("UPDATE
                                    tbl_app_file_main_data
                                SET
                                    status_id = 0
                                WHERE
                                    file_id = ?
                ", $update);
        }
        $error = $this->db->error();
        if (empty($error)) {
            if (!$this->stop_transaction_start_end)
                $this->db->query('COMMIT');
            token_unique::remove_token();
            return [$parent_id, $request_id];
        }
        else {
            if (!$this->stop_transaction_start_end)
                $this->db->query('ROLLBACK');
            return array_unique($error);
        }
    }

    public function getMaxRequest() {
        $query = $this->db->query('SELECT MAX(Request_ID) AS Max_Request FROM tbl_stock_request');
        $row = $this->db->fetch($query);
        $request_id = $row['Max_Request'];
        return $request_id;
    }

    public function viewRequestList($request_id = null, $date = null, $flag = null) {
        $condition = '';
        if (!is_null($request_id))
            $condition .= ' AND r.Request_ID = "' . $request_id . '"';
        if (!is_null($date))
            $condition .= ' AND r.Request_Date = "' . $date . '"';
        $data = array();
        $query = $this->db->query('SELECT 
        ' . (is_null($flag) ? 'r.*,' : '') . '
                                        r.Request_ID,
                                        r.Request_ID,
                                        r.Delivery_Date,
										f.Depot_Name AS Req_From,
										t.Depot_Name AS Req_To,
										r.Status_ID,
                                                                                st.Transfer_Date
									FROM 
										tbl_stock_request r
									LEFT JOIN 
										tbl_stock_depot_warehouse f
									ON 
										f.Depot_ID = r.Request_From
									LEFT JOIN 
										tbl_stock_depot_warehouse t
									ON 
										t.Depot_ID = r.Request_To
                                    LEFT JOIN
                                        tbl_stock_transfer st
                                    ON
                                         st.Request_ID = r.Request_ID
									WHERE 
										f.Status_ID = 1
				' . $condition);

        while ($row = (!is_null($flag) ? $this->db->fetch_row($query) : $this->db->assoc($query))) {
            if (!is_null($flag)) {
                $temp = $row[5];
                //                die($temp.' '.$row[5]);
                array_pop($row);
                $row["Status_ID"] = $temp;
            }
            $row['reason_of_rejection_class'] = ($row['Status_ID'] == 4 && !empty($row['Rejection_Comment'])) ? '' : 'hidden';
            $row['status_title'] = ($row['Status_ID'] == 3) ? CommonLanguage::getMessage('Transfer_Goods') : CommonLanguage::getMessage('Request');
            $row['request_class'] = $row['Status_ID'] == 3 ? 'hidden' : '';
            $row['transfer_class'] = $row['Status_ID'] != 3 ? 'hidden' : '';
            $data[] = $row;
        }

        return $data;
    }

    public function viewRequestListConfirmAndTransfer($status, $date, $flag = null) {

        $data = array();
        $query = $this->db->query('SELECT 
        ' . (is_null($flag) ? 'r.*,' : '') . '
                                        r.Request_ID,
                                        r.Request_ID,
										t.Depot_Name AS Req_To,
										f.Depot_Name AS Req_From

									FROM 
										tbl_stock_request r
									LEFT JOIN 
										tbl_stock_depot_warehouse f
									ON 
										f.Depot_ID = r.Request_From
									LEFT JOIN 
										tbl_stock_depot_warehouse t
									ON 
										t.Depot_ID = r.Request_To
									WHERE 
										r.Status_ID = "' . $status . '"
                                    AND     
										r.Request_Date = "' . $date . '"
                                    AND 
                                        f.Status_ID = 1
				');
        while ($row = is_null($flag) ? $this->db->assoc($query) : $this->db->fetch_row($query))
            $data[] = $row;
        return $data;
    }

    public function viewRequestDetails($request_id) {
        $data = array();
        $query = $this->db->query('SELECT 
                                        r.*,
                                        u.UOM_Name,
                                        p.Product_Name,
                                        p.Product_Code,
                                        if(isnull(l.Product_Depot_Quantity),0,l.Product_Depot_Quantity) as total_default_quantity
                                    FROM 
                                    tbl_stock_request_details r
                                    join tbl_stock_request sr 
                                    on r.Request_ID = sr.Request_ID
                                    LEFT JOIN tbl_stock_uom u
                                    on u.UOM_ID = r.Request_UOM
                                    join tbl_stock_products p
                                    on p.Product_ID = r.Product_ID
                                    left join tbl_stock_product_location l 
                                    on l.Product_Depot_ID =sr.Request_To 
                                    and l.Location_Product_ID = r.Product_ID
                                    WHERE 
                                        r.Request_ID ="' . $request_id . '"

				');
        $i = 1;
        while ($row = $this->db->assoc($query)) {
            $row['counter'] = $i;
            $row['red_box_class'] = $row['Request_Qty'] > $row['total_default_quantity'] ? 'red-box' : '';
            $data[] = $row;
            //
            //            $product = $this->db->query('SELECT 
            //                                            Product_Name AS Name,
            //                                            Product_Code AS Code
            //                                    FROM 
            //                                            tbl_stock_products 
            //                                    WHERE 
            //                                            Product_ID = "' . $row['Product_ID'] . '"
            //                                    ');
            //            $product_row = $this->db->assoc($product);
            //
            //
            //
            //            $data[$i]['Product_Name'] = $product_row['Name'];
            //            $data[$i]['Product_Code'] = $product_row['Code'];
            $i++;
        }
        return $data;
    }

    public function rejectTransfer() {
        $mydate = date("Y-m-d H:i:s");
        $logged_id = smc_session()['user_logged_id'];
        $this->db->prepare_Stmt('UPDATE 
                                    tbl_stock_request
                            SET 
                                    Status_ID = 4,
                                    Rejection_Comment = ?,
                                    Logged_ID = ?,date_time = ?,is_main_transfer = 0
                            WHERE 
                                    Request_ID = ?', [&$this->info['Rejection_Comment'], &$logged_id, &$mydate, &$this->info['Request_ID']]);
        $error = $this->db->error();
        return $error;
    }

    public function confirmTransfer($flag = 0, $is_edit = 0) {
        //        $mydate = date("Y-m-d H:i:s");
        $mydate = isset($this->info['date_time']) ? $this->info['date_time'] : date("Y-m-d H:i:s");
        $customer_id = (isset($this->info['customer_id']) && is_numeric($this->info['customer_id'])) ? $this->info['customer_id'] : NULL;

        $logged_id = smc_session()['user_logged_id'];
        if ($flag == 0) {
            if (!$this->stop_transaction_start_end)
                $this->db->query('START TRANSACTION');
        }
        $is_main_transfer = isset($this->info['is_main_transfer']) ? $this->info['is_main_transfer'] : 0;

        $req = [&$logged_id, &$mydate, &$is_main_transfer, &$this->info['Request_ID']];
        $this->db->prepare_Stmt("UPDATE 
                                tbl_stock_request
                        SET 
                                Status_ID = 3,
                                    Logged_ID = ?,date_time = ?,is_main_transfer = ?

                        WHERE 
                                Request_ID = ?", $req);
        $Transfer_To_Depot_ID = $this->info['Request_From'];
        $Transfer_From_Depot_ID = $this->info['Request_To'];
        $today = Date::getToday();
        $request_date = isset($this->info['Request_Date']) ? $this->info['Request_Date'] : $today;
        CommonBusiness::update_trans_record_current('trf', $this->info['Request_ID']);
        foreach ($this->info['Products'] as $item) {

            $ProductQuantity = Product::getConvertedQuantity($item['UOM_ID'], $item['Request_Qty'], $item['Product_ID']);
            $expiry_date = !empty($item['expiry_date']) ? $item['expiry_date'] : null;
            $lot_nb = !empty($item['lot_nb']) ? $item['lot_nb'] : '';
            $serial_nb = !empty($item['serial_nb']) ? $item['serial_nb'] : '';
            $is_service = !empty($item['is_service']) ? $item['is_service'] : 0;
            $costdata = [
                "Product_ID" => $item['Product_ID'],
                "Quantity" => $ProductQuantity,
                "depot_id" => $Transfer_From_Depot_ID,
                "trans_date" => $request_date,
                "Currency_ID" => smc_session()['local_currency'],
                "current_rate" => 1,
                "current_quantity" => $item['Request_Qty'],
                "uom_id" => $item['UOM_ID'],
                "Operation" => "-",
                "is_actual" => 0,
                "expiry_date" => $expiry_date,
                "lot_nb" => $lot_nb,
                "serial_nb" => $serial_nb,
                "main_id" => $this->info['Request_ID'],
                "date_time" => $mydate,
                "date_time_now" => $mydate,
                "is_service" => $is_service,
                "main_nb" => $this->info['req_nb'],
                "customer_id" => $customer_id
            ];
            CommonBusiness::addCostByType($costdata, $is_edit ? "edit_transfer_new" : "transfer");
            $costdata_to = [
                "Product_ID" => $item['Product_ID'],
                "Quantity" => $ProductQuantity,
                "depot_id" => $Transfer_To_Depot_ID,
                "trans_date" => $request_date,
                "Currency_ID" => smc_session()['local_currency'],
                "current_rate" => 1,
                "current_quantity" => $item['Request_Qty'],
                "uom_id" => $item['UOM_ID'],
                "Operation" => "+",
                "is_actual" => 0,
                "expiry_date" => $expiry_date,
                "lot_nb" => $lot_nb,
                "serial_nb" => $serial_nb,
                "main_id" => $this->info['Request_ID'],
                "date_time" => $mydate,
                "date_time_now" => $mydate,
                "is_service" => $is_service,
                "main_nb" => $this->info['req_nb'],
                "customer_id" => $customer_id
            ];
            CommonBusiness::addCostByType($costdata_to, $is_edit ? "edit_transfer_new" : "transfer");
            if ($is_service != 1) {
                $up_min = [
                    &$item['Product_ID'],
                    &$Transfer_To_Depot_ID,
                    &$ProductQuantity,
                    &$ProductQuantity
                ];
                $this->db->prepare_Stmt("  INSERT INTO tbl_stock_product_location
                                        (
                                                Location_Product_ID,
                                                Product_Depot_ID,
                                                Product_Depot_Quantity
                                        )
                                                VALUES
                                        (
                                                ?,?,?
                                        )
                                        on duplicate key update
                                 Product_Depot_Quantity = Product_Depot_Quantity + ?
            ", $up_min);
                $neg_qty = ($ProductQuantity * -1);
                $location = [
                    &$item['Product_ID'],
                    &$Transfer_From_Depot_ID,
                    &$neg_qty,
                    &$ProductQuantity
                ];
                $this->db->prepare_Stmt("INSERT INTO tbl_stock_product_location
                                        (
                                                Location_Product_ID,
                                                Product_Depot_ID,
                                                Product_Depot_Quantity
                                        )
                                                VALUES
                                        (
                                                ?,?,?
                                        )
                                    ON DUPLICATE KEY
                                    UPDATE Product_Depot_Quantity = Product_Depot_Quantity - ?


                                        ", $location);
            }
        }
        if ($flag == 0) {
            $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 array_unique($error);
        }
    }

    public function getMaxTransfer() {
        $query = $this->db->query('SELECT MAX(Transfer_ID) AS Max_Transfer FROM tbl_stock_transfer');
        $row = $this->db->fetch($query);
        $request_id = $row['Max_Transfer'];
        return $request_id;
    }

    public function viewTransferReport($status, $date) {

        $products = array();
        $query1 = $this->db->query('SELECT 
                                        p.Product_Code AS Code,
                                        p.Product_Name AS Name,
                                        f.Depot_Name AS Depot_From,
                                        t.Depot_Name AS Depot_To,
                                        rd.Request_Qty AS Quantity,
                                        tr.Transfer_Date AS Date

								    FROM 
										tbl_stock_request r
                                    LEFT JOIN 
										tbl_stock_request_details rd
									ON 
										r.Request_ID = rd.Request_ID
									LEFT JOIN 
										tbl_stock_depot_warehouse f
									ON 
										f.Depot_ID = r.Request_From
									LEFT JOIN 
										tbl_stock_depot_warehouse t
									ON 
										t.Depot_ID = r.Request_To
                                    LEFT JOIN 
										tbl_stock_products p
									ON 
										p.Product_ID = rd.Product_ID
                                    LEFT JOIN 
										tbl_stock_transfer tr
									ON 
										tr.Request_ID = r.Request_ID

									WHERE 
										r.Status_ID = "' . $status . '"
                                    AND     
										tr.Transfer_Date = "' . $date . '"
                                    AND 
                                        rd.Type_ID = 0
                                    AND 
                                        f.Status_ID = 1
				');

        while ($row = $this->db->assoc($query1))
            $products[] = $row;

        return array_merge($products);
    }

    public function adjustQuantities() {
        $this->db->query('START TRANSACTION');
//        $mydate = date("Y-m-d H:i:s");
        $data = array('is_actual' => $this->info['is_actual'],
            'assign_type' => 1,
            'Currency_ID' => smc_session()['default_setting']['local_currency'],
            'assign_date' => $this->info['Adjust_Date'],
            'Depot_ID' => $this->info['Depot_ID'],
            'note' => $this->info['adjust_note'],
            'Products' => array());
        foreach ($this->info['Products'] as $item) {
            $avg_cost_arr = CommonBusiness::viewCostByAverage($item['Product_ID']);
            $cost = $avg_cost_arr[$item['Product_ID']];
            if (!empty($item['expiry_arr'])) {
                foreach ($item['expiry_arr'] as $exp) {
                    $expiry_date = !empty($exp['expiry_date']) ? $exp['expiry_date'] : null;
                    $lot_nb = !empty($exp['lot_nb']) ? $exp['lot_nb'] : '';
                    $serial_nb = !empty($exp['serial_nb']) ? $exp['serial_nb'] : '';
                    $data['Products'][] = array(
                        'diff_qty' => ($exp['qty'] * ($item['Operation'] == "-" ? -1 : 1)),
                        'UOM_ID' => $item['UOM_ID'],
                        'quantity' => $item['real_qty'],
                        'Product_ID' => $item['Product_ID'],
                        "expiry_date" => $expiry_date,
                        "lot_nb" => $lot_nb,
                        "serial_nb" => $serial_nb,
                        "note" => $this->info['adjust_note'],
                        "is_service" => 0,
                        "unit_cost" => $cost
                    );
                }
            } else {
                $data['Products'][] = array(
                    'diff_qty' => ($item['Quantity'] * ($item['Operation'] == "-" ? -1 : 1)),
                    'UOM_ID' => $item['UOM_ID'],
                    'quantity' => $item['real_qty'],
                    'Product_ID' => $item['Product_ID'],
                    "expiry_date" => '',
                    "lot_nb" => '',
                    "serial_nb" => '',
                    "note" => $this->info['adjust_note'],
                    "is_service" => 0,
                    "unit_cost" => $cost
                );
            }
        }
//        echo"<pre>";die(print_r($data));

        if (!empty($this->info['file_id'])) {
            $this->db->prepare_Stmt('update tbl_app_file_main_data set status_id = 2 where file_id = ?', [&$this->info['file_id']]);
        }
        $this->setData($data)->add_assign(true);
        $error = $this->db->error();

        if (empty($error)) {
//            echo"<pre>hereee";
//            die(print_r($error));
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

//    public function adjustQuantities() {
//        if (!$this->stop_transaction_start_end)
//            $this->db->query('START TRANSACTION');
//        $mydate = date("Y-m-d H:i:s");
//        foreach ($this->info['Products'] as $item) {
//            $item['Operation'] = $item['Operation'] == "-" ? "-" : "+";
//            $stock = [
//                &$item['Quantity'],
//                &$item['Product_ID']
//            ];
//            $this->db->prepare_Stmt("UPDATE 
//                                        tbl_stock_products 
//                                     SET 
//                                        Product_Quantity = Product_Quantity " . $item['Operation'] . " ?
//                                     WHERE 
//                                        Product_ID = ?
//                    ", $stock);
//
//            $location = [
//                &$item['Product_ID'],
//                &$this->info['Depot_ID'],
//                &$item['Quantity'],
//                &$item['Quantity']
//            ];
//
//            $this->db->prepare_Stmt("INSERT INTO tbl_stock_product_location
//                                        (
//                                                Location_Product_ID,
//                                                Product_Depot_ID,
//                                                Product_Depot_Quantity
//                                        )
//                                                VALUES
//                                        (
//                                                ?,?,?
//                                        )on duplicate key UPDATE 
//
//                                        Product_Depot_Quantity = Product_Depot_Quantity " . $item['Operation'] . " ?
//                    ", $location);
//
//            //            $costdata = [
//            //                "Product_ID" => $item['Product_ID'],
//            //                "Quantity" => $item['Quantity'],
//            //                "UOM_ID" => $item['UOM_ID'],
//            //                "Depot_ID" => $this->info['Depot_ID'],
//            //                "Date" => $this->info['Adjust_Date'],
//            //                "Logged_ID" => $this->info[0],
//            //                "Operation" => $item['Operation'],
//            //                "Type" => "6"
//            //            ];
//            if (!empty($item['expiry_arr'])) {
//                foreach ($item['expiry_arr'] as $exp) {
//                    $expiry_date = !empty($exp['expiry_date']) ? $exp['expiry_date'] : null;
//                    $lot_nb = !empty($exp['lot_nb']) ? $exp['lot_nb'] : '';
//                    $serial_nb = !empty($exp['serial_nb']) ? $exp['serial_nb'] : '';
//                    $costdata = [
//                        "Product_ID" => $item['Product_ID'],
//                        "Quantity" => $exp['qty'],
//                        "Unit_Cost" => "",
//                        "Currency_ID" => smc_session()['local_currency'],
//                        "date_time" => $mydate,
//                        "date_time_now" => $mydate,
//                        "current_rate" => 1,
//                        "depot_id" => $this->info['Depot_ID'],
//                        "trans_date" => $this->info['Adjust_Date'],
//                        "current_quantity" => $exp['qty'],
//                        "uom_id" => $item['UOM_ID'],
//                        "is_actual" => 0,
//                        "main_id" => 0,
//                        "expiry_date" => $expiry_date,
//                        "lot_nb" => $lot_nb,
//                        "serial_nb" => $serial_nb,
//                        "is_service" => 0,
//                        "trans_note" => $this->info['adjust_note']
//                    ];
//                    if ($item['Operation'] == "+")
//                        $costdata['time_order'] = 'desc';
//                    CommonBusiness::addCostByType($costdata, "adjust_" . ($item['Operation'] == "+" ? 'positive' : 'negative'));
//                }
//            }else {
//                $expiry_date = !empty($exp['expiry_date']) ? $exp['expiry_date'] : null;
//                $lot_nb = !empty($exp['lot_nb']) ? $exp['lot_nb'] : '';
//                $serial_nb = !empty($exp['serial_nb']) ? $exp['serial_nb'] : '';
//                $costdata = [
//                    "Product_ID" => $item['Product_ID'],
//                    "Quantity" => $item['Quantity'],
//                    "Unit_Cost" => "",
//                    "Currency_ID" => smc_session()['local_currency'],
//                    "date_time" => $mydate,
//                    "date_time_now" => $mydate,
//                    "current_rate" => 1,
//                    "depot_id" => $this->info['Depot_ID'],
//                    "trans_date" => $this->info['Adjust_Date'],
//                    "current_quantity" => $item['Quantity'],
//                    "uom_id" => $item['UOM_ID'],
//                    "is_actual" => 0,
//                    "main_id" => 0,
//                    "expiry_date" => null,
//                    "lot_nb" => $lot_nb,
//                    "serial_nb" => $serial_nb,
//                    "is_service" => 0,
//                    "trans_note" => $this->info['adjust_note']
//                ];
//                if ($item['Operation'] == "+")
//                    $costdata['time_order'] = 'desc';
//                CommonBusiness::addCostByType($costdata, "adjust_" . ($item['Operation'] == "+" ? 'positive' : 'negative'));
//            }
//            //            if ($item['Operation'] == "+") {
//            //                self::addCostDataAdjustP($costdata);
//            //            } else {
//            //                self::addCostDataAdjustN($costdata);
//            //            }
//        }
//        if (!empty($this->info['file_id'])) {
//            $this->db->prepare_Stmt('update tbl_app_file_main_data set status_id = 2 where file_id = ?', [&$this->info['file_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 array_unique($error);
//    }
//    public function addDamagedQuantities() {
//        if (!$this->stop_transaction_start_end)
//            $this->db->query('START TRANSACTION');
//        $date = isset($this->info['damage_date']) ? $this->info['damage_date'] : Date::getToday();
//        $ProductQuantity = Product::getConvertedQuantity($this->info['UOM_ID'], $this->info['Damaged_Quantity'], $this->info['Product_ID']);
//        $app_user_id = isset($this->info['app_user_id']) ? $this->info['app_user_id'] : 0;
//        $app_damage_id = isset($this->info['app_damage_id']) ? $this->info['app_damage_id'] : 0;
//        $branch_id = CommonBusiness::getBranchByDepot($this->info['Depot_ID']);
//        if (isset($this->info['damage_prefix']))
//            $damage_prefix = $this->info['damage_prefix'];
//        else {
//            $query_branch = $this->db->query('select branch_prefix from tbl_organization_branches where branch_id = "' . $branch_id . '"');
//            $branch_prefix = $this->db->assoc($query_branch)['branch_prefix'];
//            $damage_prefix = smc_session()['user_accounts']['serial_prefix'] ? smc_session()['user_accounts']['serial_prefix'] : $branch_prefix;
//        }
//        $damage_nb = isset($this->info['damage_nb']) ? $this->info['damage_nb'] : self::getMaxDamageID($damage_prefix);
//        $expiry_date = !empty($this->info['expiry_date']) ? $this->info['expiry_date'] : null;
//        $return_detail_id = !empty($this->info['return_detail_id']) ? $this->info['return_detail_id'] : null;
//        $customer_id = isset($this->info['customer_id']) ? $this->info['customer_id'] : NULL;
//        $lot_nb = !empty($this->info['lot_nb']) ? $this->info['lot_nb'] : '';
//        $serial_nb = !empty($this->info['serial_nb']) ? $this->info['serial_nb'] : '';
//        $damaged = [
//            &$this->info['Product_ID'],
//            &$this->info['Depot_ID'],
//            &$ProductQuantity,
//            &$date,
//            &$this->info['logged_id'],
//            &$this->info['Damaged_Quantity'],
//            &$this->info['UOM_ID'],
//            &$this->info['Damaged_Reason'],
//            &$app_user_id, &$app_damage_id, &$damage_nb, &$damage_prefix, &$expiry_date, &$lot_nb, &$serial_nb,
//            &$customer_id,
//            &$return_detail_id
//        ];
//        $this->db->prepare_Stmt("INSERT INTO 
//                                tbl_stock_damaged_reasons (
//                                    Product_ID,
//                                    Depot_ID,
//                                    Damaged_Quantity,
//                                    Adjust_Date,
//                                    Logged_ID,
//                                    Item_Quantity_Current,
//                                    UOM_ID,
//                                    Damaged_Reason,
//                                    app_user_id,app_damage_id,damage_nb,damage_prefix,expiry_date,lot_nb,serial_nb,
//                                    customer_id,
//                                    return_detail_id
//                                )
//                            VALUES (
//                                ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?
//                            )
//
//        ", $damaged);
//        $damage_id = $this->db->id();
//        $mydate = date("Y-m-d H:i:s");
//        $today = Date::getToday();
//        $costdata = [
//            "Product_ID" => $this->info['Product_ID'],
//            "Quantity" => $ProductQuantity,
//            "depot_id" => $this->info['Depot_ID'],
//            "trans_date" => $today,
//            "Unit_Cost" => "",
//            "Currency_ID" => smc_session()['local_currency'],
//            "date_time" => $mydate,
//            "date_time_now" => $mydate,
//            "current_rate" => 1,
//            "current_quantity" => $this->info['Damaged_Quantity'],
//            "uom_id" => $this->info['UOM_ID'],
//            "Operation" => "-",
//            "is_actual" => 0,
//            "expiry_date" => $expiry_date,
//            "lot_nb" => $lot_nb,
//            "serial_nb" => $serial_nb,
//            "main_id" => $damage_id,
//            "is_service" => 0,
//            "main_nb" => $damage_prefix . $damage_nb,
//            "customer_id" => $customer_id,
//            "trans_note" => $this->info['Damaged_Reason']
//        ];
//        $product_links = CommonBusiness::get_product_links($this->info['Product_ID'], $this->info['UOM_ID'], $ProductQuantity, $this->info['Damaged_Quantity'], '-');
//        $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'];
//            $costdata['Unit_Cost'] = CommonBusiness::viewCurrentProductCost($link['Product_ID']);
//            $cost +=$costdata['Unit_Cost'];
//            CommonBusiness::updateLinkQuantities($costdata, "damage", $link['operation']);
//            $up_min = [
//                $link['Product_ID'],
//                &$this->info['Depot_ID'],
//                &$link['Quantity'],
//                &$link['Quantity']
//            ];
//            //            $this->db->prepare_Stmt("  INSERT INTO tbl_stock_product_location
//            //                                        (
//            //                                                Location_Product_ID,
//            //                                                Product_Depot_ID,
//            //                                                Product_Depot_Quantity
//            //                                        )
//            //                                                VALUES
//            //                                        (
//            //                                                ?,?,?
//            //                                        )
//            //                                        on duplicate key update
//            //                                 Product_Depot_Quantity = Product_Depot_Quantity - ?
//            //            ", $up_min);
//            //            $this->db->prepare_Stmt('update tbl_stock_products 
//            //                    set Product_Quantity = Product_Quantity - ? where Product_ID = ? ', [&$link['Quantity'], &$link['Product_ID']]);
//        }
//
//        $this->db->prepare_Stmt('update tbl_stock_damaged_reasons set unit_cost=? where Damaged_ID = ?', [&$cost, $damage_id]);
//        //        echo $cost.'===';die();
//        //                $error = $this->db->error();die(print_r($error));
//        //        $up_min = [
//        //            &$this->info['Product_ID'],
//        //            &$this->info['Depot_ID'],
//        //            &$ProductQuantity,
//        //            &$ProductQuantity
//        //        ];
//        //        $this->db->prepare_Stmt("  INSERT INTO tbl_stock_product_location
//        //                                        (
//        //                                                Location_Product_ID,
//        //                                                Product_Depot_ID,
//        //                                                Product_Depot_Quantity
//        //                                        )
//        //                                                VALUES
//        //                                        (
//        //                                                ?,?,?
//        //                                        )
//        //                                        on duplicate key update
//        //                                 Product_Depot_Quantity = Product_Depot_Quantity - ?
//        //            ", $up_min);
//        //        $this->db->prepare_Stmt('update tbl_stock_products 
//        //                    set Product_Quantity = Product_Quantity - ? where Product_ID = ? ', [&$ProductQuantity, &$this->info['Product_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 array_unique($error);
//    }

    public function viewAmountByExpiry($product_id, $category_id, $depot_id, $expiry_date) {
        $quantity = 0.0;
        $query = $this->db->query('SELECT 
                                        SUM(Product_Quantity) as Added_Quantity
                                    FROM 
                                        tbl_stock_product_transactions 
                                    WHERE 
                                        Product_ID = "' . $product_id . '"
                                    AND 
                                        Category_ID = "' . $category_id . '"
                                    AND 
                                        Depot_ID = "' . $depot_id . '"
                                    AND 
                                        Expiry_Date = "' . $expiry_date . '"
                                    AND 
                                        Operation_Type = "+"

                ');
        $row = $this->db->assoc($query);
        $query2 = $this->db->query('SELECT 
                                        SUM(Product_Quantity) as Subtracted_Quantity
                                    FROM 
                                        tbl_stock_product_transactions 
                                    WHERE 
                                        Product_ID = "' . $product_id . '"
                                    AND 
                                        Category_ID = "' . $category_id . '"
                                    AND 
                                        Depot_ID = "' . $depot_id . '"
                                    AND 
                                        Expiry_Date = "' . $expiry_date . '"
                                    AND 
                                        Operation_Type = "-"

                ');
        $row2 = $this->db->assoc($query2);
        if (!is_null($row2['Subtracted_Quantity']))
            $subtracted = $row2['Subtracted_Quantity'];
        else
            $subtracted = 0;
        $quantity = $row['Added_Quantity'] - $subtracted;

        return $quantity;
    }

    public function stockCard($product_id, $type = 0, $date_from = null, $date_to = null, $depot_id = null, $currency_id = 1) {
        $data = array('details' => array());
        $condition = '';
        $Cost = 0.0;
        $price = 0.0;
        if ($type != 0)
            $condition .= ' AND Transaction_Type_ID = "' . $type . '"';
        if ($depot_id != '')
            $condition .= ' AND Depot_ID = "' . $depot_id . '"';
        if (!is_null($date_from) and ! is_null($date_to))
            $condition .= ' AND Transaction_Date >= "' . $date_from . '" AND Transaction_Date <= "' . $date_to . '"';
        $query = $this->db->query('SELECT * from stock_transactions

                                    WHERE 
                                        Product_ID = "' . $product_id . '"

                ' . $condition);

        $amount_per_stock = 0;
        while ($row = $this->db->assoc($query)) {
            $row['Currency_Symbol'] = $currency_id == 1 ? smc_session()['local_currency_symbol'] : smc_session()['second_currency']['symbol'];
            $price = ( $currency_id == 1 ? $row['Unit_Cost'] : $row['second_currency_cost']) * $row['Product_Quantity'];
            if ($row['Operation_Type'] == '+') {
                $amount_per_stock += ($row['Product_Quantity'] / $row['uom_rate']);
                $Cost += $price; //CurrencyFactory::returnExchangedValueByExchangedID($row['Exchange_Rate_ID'], $price, $row['Currency_ID']);
            } else if ($row['Operation_Type'] == '-') {

                $amount_per_stock -= ($row['Product_Quantity'] / $row['uom_rate']);
                $Cost -= $price;
            }
            $row['Balance'] = $amount_per_stock;

            $data['details'][] = $row;
            $data['default_view_uom'] = $row['default_view_uom'];
        }
        $data['Amount_Per_Stock'] = $amount_per_stock;
        $data['Cost'] = $Cost;
        $data['Currency_Symbol'] = $currency_id == 1 ? smc_session()['local_currency_symbol'] : smc_session()['second_currency']['symbol'];

        return $data;
    }

    public function getExpiryAmounts($product_id, $category_id, $from_depot) {
        $data = array();
        $expiry = $this->db->query('SELECT 
                                        DISTINCT Expiry_Date 
                                    FROM 
                                        tbl_stock_product_transactions 
                                    WHERE 
                                        Product_ID = "' . $product_id . '"
                                    AND 
                                        Category_ID = "' . $category_id . '"
                                    AND 
                                        Depot_ID = "' . $from_depot . '"
                                    ORDER BY 
                                        Expiry_Date
                                    ');
        $i = 0;
        while ($row_expiry = $this->db->assoc($expiry)) {
            $query = $this->db->query('SELECT 
                                            *
                                        FROM 
                                            tbl_stock_product_transactions 
                                        WHERE 
                                            Product_ID = "' . $product_id . '"
                                        AND 
                                            Category_ID = "' . $category_id . '"
                                        AND 
                                            Depot_ID = "' . $from_depot . '"
                                        AND 
                                            Expiry_Date = "' . $row_expiry['Expiry_Date'] . '"

                    ');
            $amount_per_expiry_date = 0;
            while ($row = $this->db->assoc($query)) {
                if ($row['Operation_Type'] == '+')
                    $amount_per_expiry_date += $row['Product_Quantity'];
                else if ($row['Operation_Type'] == '-')
                    $amount_per_expiry_date -= $row['Product_Quantity'];
            }
            if ($amount_per_expiry_date > 0) {
                $data[$i]['Amount'] = $amount_per_expiry_date;
                $data[$i]['Expiry_Date'] = $row_expiry['Expiry_Date'];
                $i++;
            }
        }
        return $data;
    }

    public function viewProductsByType($type) {
        $data = array();
        $product = $this->db->query('SELECT 
                                            Product_Code AS CODE,
                                            Product_Name AS NAME,
                                            Product_ID AS ID
                                        FROM 
                                            tbl_stock_products 
                                        WHERE 
                                            Status_ID = 1

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

    public function keepRecordsReport() {
        $data = array();
        $query = $this->db->query('SELECT 
                                        k.*,
                                        d.Record_Date,
                                        w.Depot_Name
                                    FROM 
                                        tbl_stock_keep_records k
                                    LEFT JOIN 
                                        tbl_stock_keep_records_dates d
                                    ON 
                                        k.Record_Date_ID = d.Record_Date_ID
                                    LEFT JOIN 
                                        tbl_stock_depot_warehouse w
                                    ON 
                                        k.Depot_ID = w.Depot_ID

                ');
        while ($row = $this->db->assoc($query)) {
            $query2 = $this->db->query('SELECT 
                                                Product_Code AS Code,
                                                Product_Name AS Name,
                                                Product_Quantity AS Current_Quantity,
                                            FROM 
                                                tbl_stock_products
                                            WHERE 
                                                Product_ID = "' . $row['Product_Item_ID'] . '"
                        ');
            $row2 = $this->db->assoc($query2);
            $row['Category_Name'] = "Final Product";

            $row['Code'] = $row2['Code'];
            $row['Product_Name'] = $row2['Name'];
            $row['Current_Quantity'] = $row2['Current_Quantity'];
            $data[] = $row;
        }

        return $data;
    }

    public function getTransactionsProductType() {
        $data = array();
        $query = $this->db->query('SELECT * FROM tbl_stock_transactions_types ');


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

            $data[] = $row;
        }

        return $data;
    }

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

    //    public function agingReportItems() {
    //        $data = array();
    //        $total_m1 = 0.0;
    //        $total_m2 = 0.0;
    //        $total_m3 = 0.0;
    //        $total_m4 = 0.0;
    //        $total_more = 0.0;
    //        $method = $this->db->query('SELECT 
    //                                        d.Method_ID,
    //                                        m.Pricing_Method_Name
    //                                    FROM 
    //                                        tbl_stock_default_pricing_method d
    //                                    LEFT JOIN 
    //                                        tbl_stock_pricing_methods m
    //                                    ON 
    //                                        m.Pricing_Method_ID = d.Method_ID
    //                                    WHERE 
    //                                        d.Default_Method_ID = 1
    //                ');
    //        $method_row = $this->db->assoc($method);
    //        $product = $this->db->query('SELECT 
    //                                            Product_Code ,
    //                                            Product_Name,
    //                                            Product_ID,
    //                                            Product_Quantity
    //                                        FROM 
    //                                            tbl_stock_products 
    //                                        WHERE 
    //                                            Status_ID = 1
    //
    //                    ');
    //        while ($product_row = $this->db->assoc($product)) {
    //            $today = Date::getToday();
    //            $product_cost = 0.0;
    //            if ($method_row['Method_ID'] == 1 || $method_row['Method_ID'] == 2) {
    //                $query = $this->db->query('SELECT
    //                                                Unit_Cost * (Product_Quantity - Product_Released_Quantity) AS Product_Cost,
    //                                                Currency_ID,
    //                                                Exchange_Rate_ID
    //                                            FROM
    //                                                tbl_stock_products_costs
    //                                            WHERE 
    //                                                Product_Quantity - Product_Released_Quantity > 0
    //                                            AND
    //                                                Product_ID = "' . $product_row['Product_ID'] . '"
    //                        ');
    //                while ($row = $this->db->assoc($query)) {
    //                    $cost = CurrencyFactory::returnExchangedValueByExchangedID($row['Exchange_Rate_ID'], $row['Product_Cost'], $row['Currency_ID']);
    //                    $product_cost += $cost;
    //                }
    //            } else {
    //                $cost_array = self::viewCostByAverage($product_row['Product_ID']);
    //                $product_cost = $cost_array['Unit_Cost'] * $product_row['Product_Quantity'];
    //            }
    //
    //            $last_sold_date = $this->db->query('SELECT 
    //                                                    Max(Transaction_Date) AS Max_Date
    //                                                FROM 
    //                                                    tbl_stock_product_transactions 
    //                                                WHERE 
    //                                                    Product_ID = "' . $product_row['Product_ID'] . '"
    //                                                AND 
    //                                                    Transaction_Type_ID = 2
    //                            ');
    //            $last_sold_date_row = $this->db->assoc($last_sold_date)['Max_Date'];
    //            if (is_null($last_sold_date_row)) {
    //                $last_sold_date = $this->db->query('SELECT 
    //                                                        MIN(Transaction_Date) AS Max_Date
    //                                                    FROM 
    //                                                        tbl_stock_product_transactions 
    //                                                    WHERE 
    //                                                        Product_ID = "' . $product_row['Product_ID'] . '"
    //                                                    
    //                                ');
    //                $last_sold_date_row = $this->db->assoc($last_sold_date)['Max_Date'];
    //            }
    //
    //            $diff = date_diff(date_create($last_sold_date_row), date_create($today)); // to calculate the remaining                                                                                                              days from due date to now
    //            $due = $diff->format("%a");
    //            if ($due < 30) {
    //                $m1 = 'C: ' . $product_cost . ' <-> Q: ' . $product_row['Product_Quantity'];
    //                $m2 = 'C: 0 <-> Q: 0';
    //                $m3 = 'C: 0 <-> Q: 0';
    //                $m4 = 'C: 0 <-> Q: 0';
    //                $more = 'C: 0 <-> Q: 0';
    //                $total_m1 += $product_cost;
    //            } else if ($due > 29 and $due < 60) {
    //                $m1 = 'C: 0 <-> Q: 0';
    //                $m2 = 'C: ' . $product_cost . ' <-> Q: ' . $product_row['Product_Quantity'];
    //                $m3 = 'C: 0 <-> Q: 0';
    //                $m4 = 'C: 0 <-> Q: 0';
    //                $more = 'C: 0 <-> Q: 0';
    //                $total_m2 += $product_cost;
    //            } else if ($due > 59 and $due < 90) {
    //                $m1 = 'C: 0 <-> Q: 0';
    //                $m2 = 'C: 0 <-> Q: 0';
    //                $m3 = 'C: ' . $product_cost . ' <-> Q: ' . $product_row['Product_Quantity'];
    //                $m4 = 'C: 0 <-> Q: 0';
    //                $more = 'C: 0 <-> Q: 0';
    //                $total_m3 += $product_cost;
    //            } else if ($due > 89 and $due < 120) {
    //                $m1 = 'C: 0 <-> Q: 0';
    //                $m2 = 'C: 0 <-> Q: 0';
    //                $m3 = 'C: 0 <-> Q: 0';
    //                $m4 = 'C: ' . $product_cost . ' <-> Q: ' . $product_row['Product_Quantity'];
    //                $more = 'C: 0 <-> Q: 0';
    //                $total_m4 += $product_cost;
    //            } else {
    //                $m1 = 'C: 0 <-> Q: 0';
    //                $m2 = 'C: 0 <-> Q: 0';
    //                $m3 = 'C: 0 <-> Q: 0';
    //                $m4 = 'C: 0 <-> Q: 0';
    //                $more = 'C: ' . $product_cost . ' <-> Q: ' . $product_row['Product_Quantity'];
    //                $total_more += $product_cost;
    //            }
    //            $data[] = array(
    //                'Product_ID' => $product_row['Product_ID'],
    //                'Product_Code' => $product_row['Product_Code'],
    //                'Product_Name' => $product_row['Product_Name'],
    //                'M1' => $m1,
    //                'M2' => $m2,
    //                'M3' => $m3,
    //                'M4' => $m4,
    //                'More' => $more
    //            );
    //        }
    //
    //        $data['Totals'] = array(
    //            'M1_Total' => $total_m1,
    //            'M2_Total' => $total_m2,
    //            'M3_Total' => $total_m3,
    //            'M4_Total' => $total_m4,
    //            'More_Total' => $total_more
    //        );
    //        return $data;
    //    }

    public function getDepotProfile($ID) {
        $data = array();
        $query = $this->db->query('SELECT 
                                        d.Depot_Code AS Code,
                                        d.Depot_Name AS Name,
                                        c.Category_Name AS Category,
                                        CONCAT(e.Employee_FirstName, " ", e.Employee_MiddleName, " ", e.Employee_LastName, " ") AS PIC,
                                        CONCAT(d.Depot_Max_Num_Goods," ", u.UOM_Name) AS Maximum_Goods,
                                        CONCAT(d.Depot_Min_Num_Goods," ", u.UOM_Name) AS Minimum_Goods,
                                        d.Depot_Location AS Location,
                                        d.Depot_Area AS Depot_Area,
                                        b.branch_name as branch
                                    FROM 
                                        tbl_stock_depot_warehouse d
                                    LEFT JOIN 
                                        tbl_hrm_employee e
                                    ON
                                        e.Employee_ID= d.Depot_PIC_ID

                                    LEFT JOIN 
                                        tbl_stock_uom u
                                    ON
                                        u.UOM_ID = d.Depot_UOM
                                    LEFT JOIN 
                                        tbl_stock_categories c
                                    ON
                                        c.Category_ID = d.Depot_Category_ID
                                        left join tbl_organization_branches b 
                                        on b.branch_id = d.branch_id
                                    WHERE 
                                        d.Depot_ID = "' . $ID . '"
                                    AND 
                                        d.Status_ID = 1 
                    ');
        $row = $this->db->assoc($query);
        $row['shelfs'] = '';
        $query_shelf = $this->db->prepare_Stmt('select shelf_name from tbl_shelfs_depot where depot_id = ?', [&$ID]);
        while ($row_shelf = $this->db->assoc($query_shelf)) {
            $row['shelfs'] .= (empty($row['shelfs']) ? '' : ', ') . $row_shelf['shelf_name'];
        }
        $data[] = $row;
        return $data;
    }

    public function sendmail_DepotAdmin($from_depot, $to_depot) {
        require dirname(__FILE__) . '/../../libs/Mail/class.phpmailer.php';
        $users = $this->db->query('SELECT 
                                            e.Employee_FirstName,
                                            e.Employee_LastName,
                                            e.Employee_Email
                                        FROM 
                                            tbl_hrm_employee e
                                        LEFT JOIN 
                                            tbl_stock_depot_warehouse  d
                                        ON
                                            e.Employee_ID = d.Depot_PIC_ID
                                        WHERE 
                                             d.Depot_ID = "' . $from_depot . '"
                                        AND 
                                            d.Status_ID = 1
                                        AND 
                                            e.Status_ID = 1
                                        ');

        foreach ($users as $user) {
            $UserEmail = $user['Employee_Email'];
            $UserName = $user['Employee_FirstName'] . ' ' . $user['Employee_LastName'];
        }
        $receivers = $this->db->query('SELECT 
                                            e.Employee_FirstName,
                                            e.Employee_LastName,
                                            e.Employee_Email
                                        FROM 
                                            tbl_hrm_employee e
                                        LEFT JOIN 
                                            tbl_stock_depot_warehouse  d
                                        ON
                                            e.Employee_ID = d.Depot_PIC_ID
                                        WHERE 
                                             d.Depot_ID = "' . $to_depot . '"
                                        AND 
                                            d.Status_ID = 1
                                        AND 
                                            e.Status_ID = 1
        ');
        $receipientsEmails = array();
        $receipientsNames = array();
        foreach ($receivers as $receiver) {
            $ReceiverEmail = $receiver['Employee_Email'];
            $ReceiverName = $receiver['Employee_FirstName'] . ' ' . $receiver['Employee_LastName'];
            array_push($receipientsEmails, $ReceiverEmail);
            array_push($receipientsNames, $ReceiverName);
        }
        $receipients = [$receipientsEmails[0]];

        $ccMails = [];
        $bccMails = [];

        $request_id = self::getMaxRequest();
        $Subject = "Request Order #" . $request_id;

        //		$request_details  = self::viewRequestDetails($request_id);
        $BodyHTML = "Dear " . $receipientsNames[0] . " ,<br/><br/>
                                A new Request Order has been created ,Please Check it.<br/><br/>Thank You.<br/><br/>";
        $BodyText = str_replace("<br/><br/>", "\n\n", $BodyHTML);
        $sentEmail = SendEmail([$UserEmail, $UserName], $receipients, $ccMails, $bccMails, $Subject, $BodyHTML, $BodyText);
        if (!$sentEmail)
            return false;
        return true;
    }

    public function depotSearch($term = null, $page, $limit, $branch_id = null, $depots = '') {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' and concat(Depot_Code," - ",Depot_Name) LIKE ? ';
            $data_filter[] = "%" . $term . "%";
        }
        if (!empty($branch_id)) {
            $cond .= ' and branch_id = "' . $branch_id . '" ';
        }
        if (!empty($depots)) {
            $cond .= ' and Depot_ID in (' . $depots . ') ';
        }
        $query = $this->db->prepare_Stmt('select 
                                    Depot_ID as id,
                                    concat(Depot_Code," - ",Depot_Name) as text
                                from 
                                    tbl_stock_depot_warehouse where Status_ID = 1
                                ' . $cond . '
                                limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function getRequestorderByID($request_id) {
        $data = array();
        $entry_ids_array = array();
        $query = $this->db->query('SELECT r.Request_ID,concat(r.serial_prefix,r.Request_Number) as req_id,
         r.Request_Date,r.Request_From,r.Request_To,r.Status_ID,r.Delivery_Date,r.customer_id,r.ticketing_id,
         is_main_transfer' . (!empty(smc_session()['default_setting']['activate_customer_request_transfer']) ?
                        ',customers.Customer_Name' : '') . '
          From tbl_stock_request r
           ' . (!empty(smc_session()['default_setting']['activate_customer_request_transfer']) ?
                        ' left join tbl_customers customers on customers.Customer_ID = r.customer_id ' : '') . '
                                        Where 
                                            (r.Status_ID = 1 or r.Status_ID = 3)
                                        AND    
                                            r.Request_ID = "' . $request_id . '"
        ');
        $data['main'] = $this->db->assoc($query);
        //
        $query = $this->db->query('SELECT rd.Entry_ID,rd.Request_Qty,rd.Current_Quantity,
           rd.Request_UOM,rd.Product_ID,p.Product_Code,p.Product_Name,p.is_lot,p.is_serial,p.is_service,
           c.Cat_ID,c.Cat_Name,p.Status_ID as product_status,u.conversion_amount,rd.expiry_date,
           p.is_expiry,rd.lot_nb,rd.serial_nb,
           (if(isnull(l.Product_Depot_Quantity),0,l.Product_Depot_Quantity)+rd.Request_Qty) as total_default_quantity,
           rd.delivered_def_qty,rd.delivered_current_qty,rd.delivered_note
            From tbl_stock_request_details rd
            inner join tbl_stock_products p on rd.Product_ID = p.Product_ID
            inner join tbl_uom_conversions u
            on u.Product_ID = rd.Product_ID and rd.Request_UOM = u.uom_from
            left join tbl_stock_product_location l
            on l.Location_Product_ID = rd.Product_ID and l.Product_Depot_ID = "' . $data['main']['Request_To'] . '"
            join tbl_product_cat c on c.Cat_ID = p.Category_ID 
            Where rd.Request_ID = "' . $request_id . '"');
        $prod_ids = array();
        while ($row = $this->db->assoc($query)) {
            $data['Request'][$row['Entry_ID']] = $row;
            $prod_ids[$row['Product_ID']] = $row['Product_ID'];
            $entry_ids_array[] = $row['Entry_ID'];
        }
        if (!empty($entry_ids_array)) {
            $entry_ids_str = implode(',', $entry_ids_array);
            $criteria_details_query = $this->db->query('select  details.Entry_ID,
                                                            crit_details.criteria_detail_id,
                                                            crit_details.criteria_detail_name, 
                                                            details.criteria_note,
                                                            crit_details.criteria_id
                                                    from tbl_request_product_criteria details
                                                    inner join tbl_product_criteria_detail crit_details
                                                    using(criteria_detail_id)
                                                    where details.Entry_ID IN (' . $entry_ids_str . ')
                                                    ');
            while ($row = $this->db->assoc($criteria_details_query)) {
                $data['Request'][$row['Entry_ID']]['criteria'][] = $row;
            }
        }
        if (!empty($prod_ids)) {
            $query_uom = $this->db->query('select uom_from,Product_ID from tbl_uom_conversions where Product_ID in(' . implode(',', $prod_ids) . ')');
            while ($row_uom = $this->db->assoc($query_uom)) {
                $data['uoms'][$row_uom['Product_ID']][] = $row_uom['uom_from'];
            }
        }
        //        die(print_r($data));
        return $data;
    }

    public function editRequest() {
        //        echo'here';
        //        $app_user_id = isset($this->info['app_user_id']) ? $this->info['app_user_id'] : 0;
        //        $app_request_id = isset($this->info['app_request_id']) ? $this->info['app_request_id'] : 0;
        //        $request_prefix = isset($this->info['request_prefix']) ? $this->info['request_prefix'] : smc_session()['user_accounts']['serial_prefix'];
        //        $request_id = isset($this->info['Request_Number']) ? $this->info['Request_Number'] : self::getMaxRequestID();
        //        $request_date = isset($this->info['Request_Date']) ? $this->info['Request_Date'] : Date::getToday();
        $check = CommonFunctions::check_lock_data(['type' => 'request', 'start_date' => $this->info['Request_Date'], 'is_actual' => 0]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        if (!$this->stop_transaction_start_end)
            $this->db->query('START TRANSACTION');
        $mydate = date("Y-m-d H:i:s");
        $date_time = isset($this->info['date_time']) ? $this->info['date_time'] : $mydate;
        $logged_id = isset($this->info['Logged_ID']) ? isset($this->info['Logged_ID']) : smc_session()['user_logged_id'];
        $req_nb = '';
        $query = $this->db->query('select Request_Number,serial_prefix from tbl_stock_request 
            where Request_ID = "' . $this->info['Request_ID'] . '"');
        $req_row = $this->db->assoc($query);
        $req_nb = $req_row['Request_Number'];
        $this->info['req_nb'] = $req_row['serial_prefix'] . $req_nb;
        if ($this->info['Status_ID'] == 3) {
            //            echo 'here';die();
            $query_det = $this->db->query('select d.*,r.*,pr.is_service from tbl_stock_request_details d 
                join tbl_stock_request r
                on r.Request_ID = d.Request_ID
                join tbl_stock_products pr on pr.Product_ID = d.Product_ID
                where r.Request_ID = "' . $this->info['Request_ID'] . '"');
            while ($item = $this->db->assoc($query_det)) {
                //                $ProductQuantity = Product::getConvertedQuantity($item['Request_UOM'], $item['Request_Qty'], $item['Product_ID']);
                $expiry_date = !empty($item['expiry_date']) ? $item['expiry_date'] : null;
                $old_customer = !empty($item['customer_id']) ? $item['customer_id'] : 0;
                $costdata = [
                    "Product_ID" => $item['Product_ID'],
                    "Quantity" => $item['Request_Qty'],
                    "depot_id" => $item['Request_From'],
                    "trans_date" => $item['Delivery_Date'],
                    "Currency_ID" => smc_session()['local_currency'],
                    "current_rate" => 1,
                    "current_quantity" => $item['Current_Quantity'],
                    "uom_id" => $item['Request_UOM'],
                    "Operation" => "-",
                    "is_actual" => 0,
                    "expiry_date" => $expiry_date,
                    "lot_nb" => $item['lot_nb'],
                    "serial_nb" => $item['serial_nb'],
                    "main_id" => $this->info['Request_ID'],
                    "date_time" => $date_time,
                    "date_time_now" => $date_time,
                    "is_service" => $item['is_service'],
                    "main_nb" => $req_nb,
                    "customer_id" => $old_customer
                ];
                CommonBusiness::addCostByType($costdata, "edit_transfer_old");
                $costdata_to = [
                    "Product_ID" => $item['Product_ID'],
                    "Quantity" => $item['Request_Qty'],
                    "depot_id" => $item['Request_To'],
                    "trans_date" => $item['Delivery_Date'],
                    "current_quantity" => $item['Current_Quantity'],
                    "uom_id" => $item['Request_UOM'],
                    "Operation" => "+",
                    "is_actual" => 0,
                    "expiry_date" => $expiry_date,
                    "lot_nb" => $item['lot_nb'],
                    "serial_nb" => $item['serial_nb'],
                    "main_id" => $this->info['Request_ID'],
                    "date_time" => $date_time,
                    "date_time_now" => $date_time,
                    "is_service" => $item['is_service'],
                    "main_nb" => $req_nb,
                    "customer_id" => $old_customer
                ];
                CommonBusiness::addCostByType($costdata_to, "edit_transfer_old");
                if ($item['is_service'] != 1) {
                    $up_min = [
                        &$item['Product_ID'],
                        &$item['Request_To'],
                        &$item['Request_Qty'],
                        &$item['Request_Qty']
                    ];
                    $this->db->prepare_Stmt("  INSERT INTO tbl_stock_product_location
                                        (
                                                Location_Product_ID,
                                                Product_Depot_ID,
                                                Product_Depot_Quantity
                                        )
                                                VALUES
                                        (
                                                ?,?,?
                                        )
                                        on duplicate key update
                                 Product_Depot_Quantity = Product_Depot_Quantity + ?
            ", $up_min);
                    $location = [
                        &$item['Product_ID'],
                        &$item['Request_From'],
                        &$item['Request_Qty'],
                        &$item['Request_Qty']
                    ];
                    $this->db->prepare_Stmt("INSERT INTO tbl_stock_product_location
                                        (
                                                Location_Product_ID,
                                                Product_Depot_ID,
                                                Product_Depot_Quantity
                                        )
                                                VALUES
                                        (
                                                ?,?,?
                                        )
                                    ON DUPLICATE KEY
                                    UPDATE Product_Depot_Quantity = Product_Depot_Quantity - ?


                                        ", $location);
                }
            }
        }
        $request_id = $this->info['Request_ID'];
        $this->db->prepare_Stmt("DELETE criteria FROM  tbl_request_product_criteria criteria
                                inner join tbl_stock_request_details details on details.Entry_ID = criteria.Entry_ID
                                where details.Request_ID = ?", [$request_id]);

        $this->db->prepare_Stmt('delete from tbl_stock_request_details where Request_ID = "' . $request_id . '"');
        $is_main_transfer = isset($this->info['is_main_transfer']) ? $this->info['is_main_transfer'] : 0;
        $customer_id = (isset($this->info['customer_id']) && is_numeric($this->info['customer_id'])) ? $this->info['customer_id'] : NULL;

        $main = [&$this->info['Request_Date'],
            &$this->info['Request_From'],
            &$this->info['Request_To'],
            &$this->info['Delivery_Date'],
            &$date_time, &$logged_id, &$is_main_transfer, $this->info['Status_ID'], $this->info['note'],
            &$customer_id, &$request_id
        ];
        $this->db->prepare_Stmt('update tbl_stock_request
            set 
            Request_Date = ?,
            Request_From = ?,
            Request_To = ?,
            Delivery_Date = ?,date_time=?,Logged_ID = ?,is_main_transfer = ?,Status_ID=?,Rejection_Comment=?,
            customer_id = ?
                where Request_ID = ?', $main);
        //        $parent_id = $this->db->id();
        $expiry_valid_arr = array();
        $serial_valid_arr = array();
        $lot_valid_arr = array();
        $qty_valid_arr = array();
        $depot_arr = array($this->info['Request_To']);
        foreach ($this->info['Products'] as $item) {
            $expiry_date = !empty($item['expiry_date']) ? $item['expiry_date'] : null;
            $lot_nb = !empty($item['lot_nb']) ? $item['lot_nb'] : '';
            $serial_nb = !empty($item['serial_nb']) ? $item['serial_nb'] : '';
            $current_qty = Product::getConvertedQuantity($item['UOM_ID'], $item['Request_Qty'], $item['Product_ID']);
            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'][$item['Product_ID']][$this->info['Request_To']][$expiry_date]))
                    $expiry_valid_arr['prod'][$item['Product_ID']][$this->info['Request_To']][$expiry_date] = 0;
                $expiry_valid_arr['prod'][$item['Product_ID']][$this->info['Request_To']][$expiry_date]+= $current_qty;
            }
            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'][$item['Product_ID']][$this->info['Request_To']][$serial_nb]))
                    $serial_valid_arr['prod'][$item['Product_ID']][$this->info['Request_To']][$serial_nb] = 0;
                $serial_valid_arr['prod'][$item['Product_ID']][$this->info['Request_To']][$serial_nb]+= $current_qty;
            }
            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'][$item['Product_ID']][$this->info['Request_To']][$lot_nb]))
                    $lot_valid_arr['prod'][$item['Product_ID']][$this->info['Request_To']][$lot_nb] = 0;
                $lot_valid_arr['prod'][$item['Product_ID']][$this->info['Request_To']][$lot_nb]+= $current_qty;
            }
            if (!empty($current_qty) && empty(smc_session()['default_setting']['negative_sales'])) {
                if (!isset($qty_valid_arr[$item['Product_ID']][$this->info['Request_To']]))
                    $qty_valid_arr[$item['Product_ID']][$this->info['Request_To']] = 0;
                $qty_valid_arr[$item['Product_ID']][$this->info['Request_To']]+= $current_qty;
            }
            $details = [&$request_id, &$item['Product_ID'], &$current_qty, &$item['UOM_ID'],
                &$item['Request_Qty'], &$expiry_date, &$lot_nb, &$serial_nb];
            //            echo"<pre>";
            //            print_r($details)
            $this->db->prepare_Stmt('INSERT INTO tbl_stock_request_details( Request_ID,Product_ID,
                Request_Qty,Request_UOM,Current_Quantity,expiry_date,lot_nb,serial_nb)VALUES(?,?,?,?,?,?,?,?)', $details);
            $entry_id = $this->db->id();
            if (!empty($item['criteria_details'])) {
                $criteria_details = $item['criteria_details'];
                foreach ($criteria_details as $details) {
                    $this->db->prepare_Stmt("INSERT INTO
                                        tbl_request_product_criteria 
                                        (Entry_ID,Quantity,criteria_detail_id,criteria_note)
                                        VALUES
                                        (?,?,?,?)
                    ", [&$entry_id, &$details['Quantity'], &$details['criteria_detail_id'],
                        &$details['criteria_note']]);
                }
            }
        }
        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];
            }
        }
        //        $this->info['Request_ID'] = $request_id;
        if ($this->info['is_confirmed'] || $this->info['Status_ID'] == 3) {
            self::confirmTransfer(1, 1);
        }
        $error = $this->db->error();
        if (empty($error)) {
            if (!$this->stop_transaction_start_end)
                $this->db->query('COMMIT');
            return [$request_id, $req_nb];
        }
        else {
            if (!$this->stop_transaction_start_end)
                $this->db->query('ROLLBACK');
            return array_unique($error);
        }
    }

    //    public function editRequestOrder($id, $status) {
    //        $data = array();
    //        if (!$this->stop_transaction_start_end)
    //          $this->db->query('START TRANSACTION');
    //
    //
    //        if ($status == 3) {
    //            $query = $this->db->query('SELECT
    //                                        *
    //                                   From 
    //                                        tbl_stock_request 
    //
    //                                    Where
    //                                       Request_ID = "' . $id . '"
    //
    //                                        ');
    //            $row = $this->db->assoc($query);
    //            if (!$this->stop_transaction_start_end)
    //              $this->db->query('START TRANSACTION');
    //            $query1 = $this->db->query('SELECT * From tbl_stock_request_details where Request_ID = "' . $id . '"');
    //            $Transfer_From_Depot_ID = $row['Request_To'];
    //            $Transfer_To_Depot_ID = $row['Request_From'];
    //            $today = $row['Request_Date'];
    //            while ($row1 = $this->db->assoc($query1)) {
    //
    //
    //                $ProductQuantity = Product::getConvertedQuantity($row1['Request_UOM'], $row1['Request_Qty'], $row1['Product_ID']);
    ////call the same function 2 times (transfer in & out)
    //                //operation (in=>+ out=>-)
    ////                $costdata = [
    ////                    "Product_ID" => $row1['Product_ID'],
    ////                    "Quantity" => $ProductQuantity,
    ////                    "Quantity_Current" => $row1['Request_Qty'],
    ////                    "UOM_ID" => $row1['Request_UOM'],
    ////                    "Depot_ID_From" => $Transfer_From_Depot_ID,
    ////                    "Depot_ID_To" => $Transfer_To_Depot_ID,
    ////                    "Type" => "4",
    ////                    "Date" => $today,
    ////                    "Logged_ID" => $this->info[0]
    ////                ];
    ////
    ////                self::addCostDataForTransfer($costdata);
    //                $costdata = [
    //                    "Product_ID" => $row1['Product_ID'],
    //                    "Quantity" => $row1['Request_Qty'],
    //                    "depot_id" => $Transfer_From_Depot_ID,
    //                    "trans_date" => $today,
    //                    "current_quantity" => $row1['Request_Qty'],
    //                    "uom_id" => $row1['Request_UOM'],
    //                    "Operation" => "-"
    //                ];
    //                CommonBusiness::addCostByType($costdata, "transfer");
    //                $costdata_to = [
    //                    "Product_ID" => $row1['Product_ID'],
    //                    "Quantity" => $row1['Request_Qty'],
    //                    "depot_id" => $Transfer_To_Depot_ID,
    //                    "trans_date" => $today,
    //                    "current_quantity" => $row1['Request_Qty'],
    //                    "uom_id" => $row1['Request_UOM'],
    //                    "Operation" => "+"
    //                ];
    //                CommonBusiness::addCostByType($costdata_to, "transfer");
    //                $up_min = [
    //                    &$row1['Product_ID'],
    //                    &$Transfer_To_Depot_ID,
    //                    &$row1['Request_Qty'],
    //                    &$row1['Request_Qty']
    //                ];
    //                $this->db->prepare_Stmt(" INSERT INTO tbl_stock_product_location
    //                                        (
    //                                                Location_Product_ID,
    //                                                Product_Depot_ID,
    //                                                Product_Depot_Quantity
    //                                        )
    //                                                VALUES
    //                                        (
    //                                                ?,?,?
    //                                        )
    //                                    ON DUPLICATE KEY
    //                                    UPDATE
    //                                 Product_Depot_Quantity = Product_Depot_Quantity - ?
    //            ", $up_min);
    //                $location = [
    //                    & $row1['Product_ID'],
    //                    &$Transfer_From_Depot_ID,
    //                    &$row1['Request_Qty'],
    //                    &$row1['Request_Qty']
    //                ];
    //                $this->db->prepare_Stmt("INSERT INTO tbl_stock_product_location
    //                                        (
    //                                                Location_Product_ID,
    //                                                Product_Depot_ID,
    //                                                Product_Depot_Quantity
    //                                        )
    //                                                VALUES
    //                                        (
    //                                                ?,?,?
    //                                        )
    //                                    ON DUPLICATE KEY
    //                                    UPDATE Product_Depot_Quantity = Product_Depot_Quantity + ?
    //                                        ", $location);
    //            }
    //        }
    //
    //        $delete = $this->db->query('Delete r.*,rd.* from tbl_stock_request r left join tbl_stock_request_details rd on r.Request_ID = rd.Request_ID where r.Request_ID = "' . $id . '"');
    //        $edit_request = self::createRequest($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 array_unique($error);
    //    }

    public function depotcatSearch($term = null, $page, $limit) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' where Category_Name LIKE ? ';
            $data_filter[] = "%" . $term . "%";
        }
        $query = $this->db->query('select 
                                    Category_ID as id,
                                    Category_Name as text
                                from 
                                    tbl_stock_categories
                                ' . $cond . '
                                limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function checkDepotIfUsed($depot_id) {

        $db = DBConnectorFactory::loadDriver();
        $query_invoice = $db->query('select * from tbl_invoices where Depot_ID = "' . $depot_id . '"');
        $row_invoice = $db->assoc($query_invoice);
        if (!empty($row_invoice)) {
            return true;
        }
        $query_return = $db->query('select * from tbl_accounting_return_invoices where Depot_ID = "' . $depot_id . '"');
        $row_return = $db->assoc($query_return);
        if (!empty($row_return)) {
            return true;
        }
        $query_purchase = $db->query('select * from tbl_inventory_purchases where Depot_ID = "' . $depot_id . '"');
        $row_purchase = $db->assoc($query_purchase);
        if (!empty($row_purchase)) {
            return true;
        }
        $query_request = $db->query('select * from tbl_stock_request where Request_From = "' . $depot_id . '" or Request_To = "' . $depot_id . '"');
        $row_request = $db->assoc($query_request);
        if (!empty($row_request)) {
            return true;
        }
        return false;
    }

    public function adjustmentFileData($file_id) {
        $query = $this->db->query('SELECT file_name FROM tbl_app_file_main_data WHERE file_id = "' . $file_id . '"');
        $row = $this->db->assoc($query);
        $path_name = $row['file_name'];
        $path = appFiles . $path_name;
        $first = file_get_contents($path);
        $result = json_decode($first, true);
        $stock_id = 1;
        $products = $result['data'];
        if (!$this->stop_transaction_start_end)
            $this->db->query('START TRANSACTION');
        $exists = $this->db->query('SELECT file_id from tbl_app_temp_main_adj where file_id = "' . $file_id . '"');
        $row_ex = $this->db->assoc($exists);
        if (!empty($row_ex)) {
            $data = [
                &$file_id,
                &$stock_id
            ];
            $this->db->prepare_Stmt("INSERT INTO tbl_app_temp_main_adj (
                                                        file_id, 
                                                        stock_id
                                                        ) 
                                        VALUES (
                                              ?,?      
                                            )
                                ", $data);
            $parent_id = $this->db->id();
            foreach ($products as $item) {
                $select = $this->db->query('SELECT uom_from AS UOM_ID,Product_ID FROM tbl_uom_conversions WHERE Bar_Code = "' . $item['barcode'] . '"');
                $row = $this->db->assoc($select);
                $data = [
                    &$parent_id,
                    &$row['Product_ID'],
                    &$row['UOM_ID'],
                    &$item['quantity']
                ];
                $this->db->prepare_Stmt("INSERT INTO tbl_app_temp_adjustments (
                                                        temp_main_id,
                                                        Product_ID, 
                                                        UOM_ID,
                                                        quantity
                                                        ) 
                                        VALUES (
                                              ?,?,?,?      
                                            )
                                ", $data);
            }
        }
        $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 array_unique($error);
    }

    public function exportAppAdjustment($id) {
        $objPHPExcel = new PHPExcel();
        // Set the active Excel worksheet to sheet 0
        $objPHPExcel->setActiveSheetIndex(0);
        $rowCount = 1;
        $query = $this->db->query('SELECT * FROM app_temp_adjustments WHERE file_id = "' . $id . '"');
        while ($row = $this->db->assoc($query)) {
            $objPHPExcel->getActiveSheet()->SetCellValue('A' . $rowCount, $row['Bar_Code']);
            $objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, $row['Product_Name']);
            $objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, $row['quantity']);
            $rowCount++;
        }
        $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
        // Write the Excel file to filename some_excel_file.xlsx in the current directory
        //$objWriter->save('some_excel_file.xlsx'); 
        header('Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment; filename="adjusted_products.xlsx"');
        $objWriter->save('php://output');
        exit;
    }

    public function shelfdepotSearch($term = null, $page, $limit, $pid) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' and shelf_name LIKE ? ';
            $data_filter[] = "%" . $term . "%";
        }
        if (!is_null($pid)) {
            $cond .= ' and depot_id = ? ';
            $data_filter[] = $pid;
        }

        $query = $this->db->prepare_Stmt('SELECT shelf_depot_id as id,shelf_name as text FROM `tbl_shelfs_depot` where true
            ' . $cond . ' limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function getConsumedProduct($start_Date = null, $end_Date = null, $cat_id = null, $depot_id = null, $type = null, $type_id = null, $pur_cat = null) {
        $data = array();
        $start_Date = empty($start_Date) ? date('Y-m-01') : $start_Date;
        $end_Date = empty($end_Date) ? date('Y-m-d') : $end_Date;
        $type_id = empty($type_id) ? smc_session()['stock_method'] : $type_id;
        $type = empty($type) ? "method" : $type;
        $date_arr = [&$start_Date, &$end_Date];

        $prev_arr = [&$start_Date];
        $cond = '';
        $total_prv_qty = $total_qty_in = $total_qty_out = $total_cost = $total_qty_remaining = $total_cost_remain = 0.0;
        if (!empty($pur_cat)) {
            $date_arr[] = &$pur_cat;
            $prev_arr[] = &$pur_cat;
            $cond .= ' and  p.pur_cat_id = ? ';
        }
        if (!empty($cat_id)) {
            $date_arr[] = &$cat_id;
            $prev_arr[] = &$cat_id;
            $cond .= ' and  p.Category_ID = ? ';
        }
        if (!empty($depot_id)) {
            $date_arr[] = &$depot_id;
            $prev_arr[] = &$depot_id;
            $cond .= ' and  t.Depot_ID = ? ';
        }
        $remain_cost_arr = array();
        //we depend on $in_types not +,- since in edit putrchase as ex: there is +,- but we must take the difference only.
        $in_types = array(1, 9, 10, 12, 14, 15, 16, 35, 37, 38, 55, 56);
        //        if(!empty($this->info['with_transfers']) && !empty($depot_id))
        //            $in_types[] = 
        $in_out_types = array(4, 6, 58, 59, 61, 62);
        $query = $this->db->prepare_Stmt(' SELECT t.Transaction_Date,t.Unit_Cost,p.Product_ID,
            p.Product_Code,p.Product_Name, t.Transaction_Type_ID,u.UOM_Name
                /*,sum(if(t.Operation_Type= "+",t.Product_Quantity,0)) as qty_in
                ,sum(if(t.Operation_Type= "-",t.Product_Quantity,0)) as qty_out*/
                , sum(if(t.Operation_Type= "+",+1,-1)*(t.Product_Quantity/if(p.default_view_uom_id=0,1,p.default_view_rate))) as sum_qty
                , sum(if(t.Operation_Type= "-",(t.Unit_Cost*t.Product_Quantity),0)) as sum_cost_out
                , sum(if(t.Operation_Type= "-",t.Product_Quantity,0)) as sum_cost_qty,
                if(p.default_view_uom_id=0,1,p.default_view_rate) as uom_rate
                FROM `tbl_stock_product_transactions` t 
                 join tbl_stock_products p on (t.Product_ID = p.Product_ID)
                 join tbl_stock_uom u on if(p.default_view_uom_id=0,p.UOM_ID,p.default_view_uom_id) = u.UOM_ID
                where  t.Transaction_Date between ? and  ? and t.is_current = 1 ' . $cond . ' group by t.Product_ID, t.Transaction_Type_ID
         ', $date_arr);
        while ($row = $this->db->assoc($query)) {
            $row['qty_in'] = $row['qty_out'] = $row['prv_qty'] = 0;
            if (in_array($row['Transaction_Type_ID'], $in_types) || ($row['sum_qty'] > 0 && in_array($row['Transaction_Type_ID'], $in_out_types))) {
                $row['qty_in'] += $row['sum_qty'];
                $row['sum_cost_qty'] = $row['sum_cost_out'] = 0;
            } else
                $row['qty_out'] += -$row['sum_qty'];
            $row['qty_remaining'] = $row['qty_in'] - $row['qty_out'];
            //
            if (isset($data[$row['Product_ID']])) {
                $data[$row['Product_ID']]['qty_in'] += $row['qty_in'];
                $data[$row['Product_ID']]['qty_out'] += $row['qty_out'];
                $data[$row['Product_ID']]['qty_remaining'] += $row['qty_remaining'];
                $data[$row['Product_ID']]['sum_cost_out'] += $row['sum_cost_out'];
                $data[$row['Product_ID']]['sum_cost_qty'] += $row['sum_cost_qty'];
                $data[$row['Product_ID']]['cost_out'] += $row['sum_cost_out'];
            } else {
                $row['currency_symbol'] = smc_session()['local_currency_symbol'];
                $row['cost_out'] = $row['sum_cost_out'];
                $data[$row['Product_ID']] = $row;
            }
            $remain_cost_arr[$row['Product_ID']]['quantity'] = $data[$row['Product_ID']]['qty_remaining'];
            //
            $total_qty_in += floatval($row['qty_in']);
            $total_qty_out += floatval($row['qty_out']);
            $total_cost += floatval($row['sum_cost_out']);    //can not be calculated for unit cost 
            $total_qty_remaining += floatval($row['qty_remaining']);
        }
        //          echo"<pre>";die(print_r($data))
        //        echo implode(',', array_keys($data)).'===';
        //        if (!empty($data)) {

        $query_prv_balance = $this->db->prepare_Stmt('SELECT t.Transaction_Date,t.Unit_Cost,p.Product_ID,
                sum(if(t.Operation_Type= "+",t.Product_Quantity,-t.Product_Quantity)/if(p.default_view_uom_id=0,1,p.default_view_rate)) as previous_qty  
                , if(p.default_view_uom_id=0,1,p.default_view_rate) as uom_rate,
                p.Product_Code,p.Product_Name, t.Transaction_Type_ID,u.UOM_Name
                FROM `tbl_stock_product_transactions` t 
                 join tbl_stock_products p on (t.Product_ID = p.Product_ID)
                 join tbl_stock_uom u on if(p.default_view_uom_id=0,p.UOM_ID,p.default_view_uom_id) = u.UOM_ID
                where t.Transaction_Date < ? and t.is_current = 1
                /*and p.Product_ID in (' . implode(',', array_keys($data)) . ') */' . $cond . ' 
                    group by t.Product_ID having previous_qty <> 0 ', $prev_arr);
        while ($row_prv = $this->db->assoc($query_prv_balance)) {
            if (!isset($data[$row_prv['Product_ID']])) {
                $row_prv['qty_remaining'] = $row_prv['qty_in'] = $row_prv['qty_out'] = $row_prv['sum_cost_out'] = $row_prv['sum_cost_qty'] = $row_prv['cost_out'] = 0;
                $row_prv['currency_symbol'] = smc_session()['local_currency_symbol'];
                $remain_cost_arr[$row_prv['Product_ID']]['quantity'] = 0;
                $data[$row_prv['Product_ID']] = $row_prv;
            }
            $data[$row_prv['Product_ID']]['prv_qty'] = $row_prv['previous_qty'];
            $data[$row_prv['Product_ID']]['qty_remaining'] += $row_prv['previous_qty'];
            $remain_cost_arr[$row_prv['Product_ID']]['quantity'] += $row_prv['previous_qty'];
            $total_qty_remaining += floatval($row_prv['previous_qty']);
            $total_prv_qty += floatval($row_prv['previous_qty']);
        }
        //         echo'here';

        if ($type == 'price') {
            $result_data = Product::get_product_price($remain_cost_arr, $type_id);
        } else {
            $result_data = Product::get_unit_cost_by_method($type, $type_id, $remain_cost_arr);
        }
        //
        //            $result_data = Product::get_unit_cost_by_method($type, $type_id, $remain_cost_arr);
        //            echo"<pre>";
        //            die(print_r($result_data));
        foreach ($data as $prod_id => &$prod_row) {
            $prod_row['cost_qty_remaining'] = ($result_data[$prod_id]['Price'] * $prod_row['qty_remaining'] * $prod_row['uom_rate']);
            $total_cost_remain += ($result_data[$prod_id]['Price'] * $prod_row['qty_remaining'] * $prod_row['uom_rate']);
        }
        //        }
        return (array('data' => $data, 'total_prv_qty' => $total_prv_qty, 'total_qty_in' => $total_qty_in, 'total_qty_out' => $total_qty_out, 'total_cost' => $total_cost, 'total_qty_remaining' => $total_qty_remaining, 'total_cost_remain' => $total_cost_remain));
    }

    //    public function get_total_adjustment_cost($depot,$cost){
    //        
    //    }

    public function adjustment_report($date_from, $date_to, $grouped = 1, $depot = null, $cat = null) {
        $data = array();
        $cond = '';
        if (!empty($depot))
            $cond .= ' and t.Depot_ID = "' . $depot . '"';
        if (!empty($cat))
            $cond .= ' and p.Category_ID = "' . $cat . '"';
        $total = 0;
        if ($grouped) {
            $query = $this->db->query('select p.Product_Code,p.Product_Name,
            sum(if(t.Operation_Type = "+",t.Product_Quantity,t.Product_Quantity*(-1))) as qty,
            u.UOM_Name,
            sum(if(t.Operation_Type = "+",t.Product_Quantity,t.Product_Quantity*(-1))*t.Unit_Cost) as total_cost,
            "-" as date,"" as note
             from tbl_stock_product_transactions t 
             inner join tbl_stock_products p on p.Product_ID = t.Product_ID
             inner join tbl_stock_uom u on u.UOM_ID = p.UOM_ID
             where t.Transaction_Type_ID = 6 
            and t.Transaction_Date between "' . $date_from . '" and "' . $date_to . '" ' . $cond . ' group by t.Product_ID');
        } else {
            $query = $this->db->query('select p.Product_Code,p.Product_Name,
            if(t.Operation_Type = "+",t.Product_Quantity,t.Product_Quantity*(-1)) as qty,
            u.UOM_Name,
            if(t.Operation_Type = "+",t.Product_Quantity,t.Product_Quantity*(-1))*t.Unit_Cost as total_cost,
            t.Transaction_Date as date,t.trans_note as note
            from tbl_stock_product_transactions t
            inner join tbl_stock_products p on p.Product_ID = t.Product_ID 
            inner join tbl_stock_uom u on u.UOM_ID = p.UOM_ID
            where t.Transaction_Type_ID = 6 
            and t.Transaction_Date between "' . $date_from . '" and "' . $date_to . '" ' . $cond);
        }
        while ($row = $this->db->assoc($query)) {
            $total += $row['total_cost'];
            $row['total_cost'] = CommonFunctions::formatNumber($row['total_cost']);
            if (!$this->perm->hasPermission('show_cost_stock_adj'))
                unset($row['total_cost']);
            $data[] = array_values($row);
        }
        return ["html" => $data, "total_cost" => CommonFunctions::formatNumber($total)];
    }

    public function searchDamageReason($term) {
        $data = array();
        $query = $this->db->query('select *,damage_note as text from tbl_damage where damage_note like "%' . $term . '%"  limit 30');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function delete_request($request_arr) {
        $msg_arr = array();
        $error_arr = array();
        if (!$this->stop_transaction_start_end)
            $this->db->query('START TRANSACTION');
        foreach ($request_arr as $request_id) {
            $query_check = $this->db->query('select concat(serial_prefix,Request_Number) as req_nb
                ,is_main_transfer,Status_ID,Request_From,Request_To, Delivery_Date,Request_Date
                from tbl_stock_request where  Request_ID = "' . $request_id . '"');
            $row = $this->db->assoc($query_check);
            if ($row['is_main_transfer'] != 0) {
                $msg_arr[] = $row['req_nb'];
                continue;
            }
            $check = CommonFunctions::check_lock_data(['type' => 'request', 'start_date' => $row['Request_Date'], 'is_actual' => 0]);
            if ($check !== '') {
                $error_arr[0] = $check;
            }
            if ($row['Status_ID'] == 3) {
                //            echo 'here';die();
                $query_det = $this->db->query('select d.*,pr.is_service,request.Request_Number
                    from tbl_stock_request_details d
                    inner join tbl_stock_request request on request.Request_ID = d.Request_ID
                    join tbl_stock_products pr on pr.Product_ID = d.Product_ID
                where d.Request_ID = "' . $request_id . '"');
                while ($item = $this->db->assoc($query_det)) {
                    //                $ProductQuantity = Product::getConvertedQuantity($item['Request_UOM'], $item['Request_Qty'], $item['Product_ID']);
                    $expiry_date = !empty($item['expiry_date']) ? $item['expiry_date'] : null;
                    $costdata = [
                        "Product_ID" => $item['Product_ID'],
                        "Quantity" => $item['Request_Qty'],
                        "depot_id" => $row['Request_From'],
                        "trans_date" => $row['Delivery_Date'],
                        "Currency_ID" => smc_session()['local_currency'],
                        "current_rate" => 1,
                        "current_quantity" => $item['Current_Quantity'],
                        "uom_id" => $item['Request_UOM'],
                        "Operation" => "-",
                        "is_actual" => 0,
                        "expiry_date" => $expiry_date,
                        "lot_nb" => $item['lot_nb'],
                        "serial_nb" => $item['serial_nb'],
                        "main_id" => $request_id,
                        "date_time_now" => date('Y-m-d H:i:s'),
                        "is_service" => $item['is_service'],
                        "main_nb" => $item['Request_Number']
                    ];
                    CommonBusiness::addCostByType($costdata, "delete_transfer");
                    $costdata_to = [
                        "Product_ID" => $item['Product_ID'],
                        "Quantity" => $item['Request_Qty'],
                        "depot_id" => $row['Request_To'],
                        "trans_date" => $row['Delivery_Date'],
                        "Currency_ID" => smc_session()['local_currency'],
                        "current_rate" => 1,
                        "current_quantity" => $item['Current_Quantity'],
                        "uom_id" => $item['Request_UOM'],
                        "Operation" => "+",
                        "is_actual" => 0,
                        "expiry_date" => $expiry_date,
                        "lot_nb" => $item['lot_nb'],
                        "serial_nb" => $item['serial_nb'],
                        "main_id" => $request_id,
                        "date_time_now" => date('Y-m-d H:i:s'),
                        "is_service" => $item['is_service'],
                        "main_nb" => $item['Request_Number']
                    ];
                    CommonBusiness::addCostByType($costdata_to, "delete_transfer");
                    if ($item['is_service'] != 1) {
                        $up_min = [
                            &$item['Product_ID'],
                            &$row['Request_To'],
                            &$item['Request_Qty'],
                            &$item['Request_Qty']
                        ];
                        $this->db->prepare_Stmt("  INSERT INTO tbl_stock_product_location
                                        (
                                                Location_Product_ID,
                                                Product_Depot_ID,
                                                Product_Depot_Quantity
                                        )
                                                VALUES
                                        (
                                                ?,?,?
                                        )
                                        on duplicate key update
                                 Product_Depot_Quantity = Product_Depot_Quantity + ?
            ", $up_min);
                        $location = [
                            &$item['Product_ID'],
                            &$row['Request_From'],
                            &$item['Request_Qty'],
                            &$item['Request_Qty']
                        ];
                        $this->db->prepare_Stmt("INSERT INTO tbl_stock_product_location
                                        (
                                                Location_Product_ID,
                                                Product_Depot_ID,
                                                Product_Depot_Quantity
                                        )
                                                VALUES
                                        (
                                                ?,?,?
                                        )
                                    ON DUPLICATE KEY
                                    UPDATE Product_Depot_Quantity = Product_Depot_Quantity - ?


                                        ", $location);
                    }
                }
                CommonBusiness::update_trans_record_current('trf', $request_id);
            }
            $this->db->prepare_Stmt('delete from tbl_stock_request_details where Request_ID = "' . $request_id . '"');
            $this->db->prepare_Stmt('delete from tbl_stock_request where Request_ID = "' . $request_id . '"');
        }
        $error = $this->db->error();
        if (empty($error))
            if (!$this->stop_transaction_start_end)
                $this->db->query('COMMIT');
            else {
                $msg_arr = array();
                if (!$this->stop_transaction_start_end)
                    $this->db->query('ROLLBAK');
            }
        $msg = '';
        if (!empty($msg_arr))
            $msg = implode(',', $msg_arr) . ' ' . CommonLanguage::getMessage('is_transfered_main');
        if(!empty($error_arr))
            $msg.=$error_arr[0];
        return [$error, $msg];
    }

    public function get_total_damaged_cost() {
        $cond = '';
        $total_cost = 0;
        if (!empty($this->info['From_Date']) && !empty($this->info['To_Date']))
            $cond .= '  and Adjust_Date <= "' . $this->info['To_Date'] . '" AND Adjust_Date >= "' . $this->info['From_Date'] . '"';
        if ($this->info['depot_id'] !== 'All') {
            $cond .= ' AND Depot_ID = "' . $this->info['depot_id'] . '"';
        }
        if (!empty($this->info['category_id'])) {
            $cond .= ' AND category_id = "' . $this->info['category_id'] . '"';
        }
        if (!empty($this->info['brand_id'])) {
            $cond .= ' AND brand_id = "' . $this->info['brand_id'] . '"';
        }
        if (is_numeric($this->info['customer_id']))
            $cond .= ' and customer_id = "' . $this->info['customer_id'] . '"';
        $query = $this->db->query('select sum(unit_cost*Damaged_Quantity) as total_cost 
            from damaged_products where 1=1 ' . $cond);
        $row = $this->db->assoc($query);
        if (!empty($row))
            $total_cost = $row['total_cost'];
        return CommonFunctions::formatNumber($total_cost);
    }

//    public function delete_damage($id) {
//        if (!$this->stop_transaction_start_end)
//            $this->db->query('START TRANSACTION');
//        $query = $this->db->query('select * from tbl_stock_damaged_reasons where Damaged_ID = "' . $id . '"');
//        $row = $this->db->assoc($query);
//        $date_time = date('Y-m-d H:i:s');
//        $today = date("Y-m-d");
//        $costdata = [
//            "Product_ID" => $row['Product_ID'],
//            "Quantity" => $row['Damaged_Quantity'],
//            "Unit_Cost" => $row['unit_cost'],
//            "unit_price" => '',
//            "Currency_ID" => smc_session()['local_currency'],
//            "date_time" => $date_time,
//            "date_time_now" => $date_time,
//            "current_rate" => 1,
//            "depot_id" => $row['Depot_ID'],
//            "trans_date" => $today,
//            "current_quantity" => $row['Item_Quantity_Current'],
//            "uom_id" => $row['UOM_ID'],
//            "is_actual" => 0,
//            "expiry_date" => $row['expiry_date'],
//            "lot_nb" => $row['lot_nb'],
//            "serial_nb" => $row['serial_nb'],
//            "main_id" => $id,
//            "is_service" => 0,
//            "main_nb" => $row['damage_prefix'] . $row['damage_nb'],
//            "customer_id" => $row['customer_id']
//        ];
//        $product_links = CommonBusiness::get_product_links($row['Product_ID'], $row['UOM_ID'], $row['Damaged_Quantity'], $row['Item_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 {
//                $costdata['Unit_Cost'] = CommonBusiness::viewCurrentProductCost($link['Product_ID']);
//            }
//            CommonBusiness::updateLinkQuantities($costdata, "delete_damage", ($link['operation'] == '+' ? '-' : '+'));
//
//            CommonBusiness::update_trans_record_current('dmg', $id);
//            //            $this->db->prepare_Stmt('update tbl_stock_products set Product_Quantity=Product_Quantity + ? 
//            //            where Product_ID = ?', [& $link['Quantity'], &$link['Product_ID']]);
//            //            $this->db->prepare_Stmt('update  tbl_stock_product_location set Product_Depot_Quantity=Product_Depot_Quantity+?
//            //            where Location_Product_ID = ? and Product_Depot_ID=?', [& $link['Quantity'], &$link['Product_ID'], &$row['Depot_ID']]);
//        }
//        $this->db->prepare_Stmt('delete from tbl_stock_damaged_reasons where Damaged_ID = ?', [&$id]);
//
//        //        CommonBusiness::addCostByType($costdata, "delete_damage");
//        $error = $this->db->error();
//        if (empty($error)) {
//            if (!$this->stop_transaction_start_end)
//                $this->db->query('COMMIT');
//        } else {
//            if (!$this->stop_transaction_start_end)
//                $this->db->query('ROLLBACK');
//        }
//        return $error;
//    }

    public function add_damage() {
        $date = isset($this->info['damage_date']) ? $this->info['damage_date'] : Date::getToday();

        $error = token_unique::is_valid_token();
        if (!empty($error))
            return $error;
        $check = CommonFunctions::check_lock_data(['type' => 'dmg', 'start_date' => $date, 'is_actual' => $this->info['is_actual']]);

//        $check = JV::checkCurrentYear($date, $date, $this->info['is_actual']);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        if (!$this->stop_transaction_start_end)
            $this->db->query('START TRANSACTION');
        $app_user_id = isset($this->info['app_user_id']) ? $this->info['app_user_id'] : 0;
        $app_damage_id = isset($this->info['app_damage_id']) ? $this->info['app_damage_id'] : 0;
        $app_branch_id = isset($this->info['app_branch_id']) ? $this->info['app_branch_id'] : 0;

        $branch_id = CommonBusiness::getBranchByDepot($this->info['Depot_ID']);
        if (isset($this->info['damage_prefix']))
            $damage_prefix = $this->info['damage_prefix'];
        else {
            $query_branch = $this->db->query('select branch_prefix from tbl_organization_branches where branch_id = "' . $branch_id . '"');
            $branch_prefix = $this->db->assoc($query_branch)['branch_prefix'];
            $damage_prefix = smc_session()['user_accounts']['serial_prefix'] ? smc_session()['user_accounts']['serial_prefix'] : $branch_prefix;
        }
        $damage_nb = isset($this->info['damage_nb']) ? $this->info['damage_nb'] : self::getMaxDamageID($damage_prefix, $this->info['is_actual']);
        $datetime = date('Y-m-d H:i:s');
        $customer_id = isset($this->info['customer_id']) ? $this->info['customer_id'] : NULL;
        $this->info['return_id'] = isset($this->info['return_id']) ? $this->info['return_id'] : NULL;
        $this->info['created_logged_id'] = isset($this->info['created_logged_id']) ? $this->info['created_logged_id'] : smc_session()['user_logged_id'];
        $this->info['Logged_ID'] = isset($this->info['Logged_ID']) ? $this->info['Logged_ID'] : smc_session()['user_logged_id'];
        $this->info['date_created'] = isset($this->info['date_created']) ? $this->info['date_created'] : $datetime;
        $this->info['date_time'] = isset($this->info['date_time']) ? $this->info['date_time'] : $datetime;

        $data = [$damage_nb, $damage_prefix, $date, $customer_id, $this->info['Depot_ID'], $this->info['note'],
            $this->year_id, $this->info['is_actual'], $this->info['created_logged_id'], $this->info['Logged_ID'],
            $this->info['date_created'], $this->info['date_time'], $app_user_id, $app_damage_id, $this->info['return_id'],
            $app_branch_id, $branch_id];
        $this->db->prepare_Stmt('INSERT INTO `tbl_damage`( `damage_nb`, `damage_prefix`,
                `damage_date`, `customer_id`, `Depot_ID`, `damage_note`, `current_year_id`, `is_actual`,
                `created_logged_id`, `Logged_ID`, `date_created`, `date_time`, `app_user_id`, `app_damage_id`,
                 `return_id`,app_branch_id,branch_id) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ', $data);
        $damage_id = $this->db->id();

        foreach ($this->info['Products'] as $item) {
            $expiry_date = !empty($item['expiry_date']) ? $item['expiry_date'] : null;
            $ProductQuantity = Product::getConvertedQuantity($item['UOM_ID'], $item['quantity'], $item['Product_ID']);
//            die($ProductQuantity.'====');
            $return_detail_id = !empty($item['return_detail_id']) ? $item['return_detail_id'] : null;
            $lot_nb = !empty($item['lot_nb']) ? $item['lot_nb'] : '';
            $serial_nb = !empty($item['serial_nb']) ? $item['serial_nb'] : '';
            $item['note'] = !empty($item['note']) ? $item['note'] : '';
            $item['is_service'] = isset($item['is_service']) ? $item['is_service'] : 0;
            $costdata = [
                "Product_ID" => $item['Product_ID'],
                "Quantity" => $ProductQuantity,
                "depot_id" => $this->info['Depot_ID'],
                "trans_date" => $date,
                "Unit_Cost" => "",
                "Currency_ID" => smc_session()['local_currency'],
                "date_time" => $datetime,
                "date_time_now" => date('Y-m-d H:i:s'),
                "current_rate" => 1,
                "current_quantity" => $item['quantity'],
                "uom_id" => $item['UOM_ID'],
                "Operation" => "-",
                "is_actual" => $this->info['is_actual'],
                "expiry_date" => $expiry_date,
                "lot_nb" => $lot_nb,
                "serial_nb" => $serial_nb,
                "main_id" => $damage_id,
                "is_service" => $item['is_service'],
                "main_nb" => $damage_prefix . $damage_nb,
                "customer_id" => $customer_id,
                "trans_note" => $item['note']
            ];
            $product_links = CommonBusiness::get_product_links($item['Product_ID'], $item['UOM_ID'], $ProductQuantity, $item['quantity'], '-');
            $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'];
                $costdata['Unit_Cost'] = CommonBusiness::viewCurrentProductCost($link['Product_ID']);
                $cost +=$costdata['Unit_Cost'];
                CommonBusiness::updateLinkQuantities($costdata, "damage", $link['operation']);
            }
            $damaged = [
                $damage_id, &$item['Product_ID'], &$ProductQuantity, $item['quantity'], &$item['UOM_ID'],
                $expiry_date, &$lot_nb, &$serial_nb, $cost, $return_detail_id, $item['note']];
            $this->db->prepare_Stmt("INSERT INTO `tbl_damage_details`( `damage_id`, `product_id`, `quantity`,
                `current_quantity`, `UOM_ID`, `expiry_date`, `lot_nb`, `serial_nb`, `unit_cost`,
                `return_detail_id`, `detail_note`) values(?,?,?,?,?,?,?,?,?,?,?)

        ", $damaged);
        }


        $error = $this->db->error();
        if (empty($error)) {
            if (!$this->stop_transaction_start_end)
                $this->db->query("COMMIT");
                
            token_unique::remove_token();
            if(!empty($damage_id)){
                return [$damage_id];
            }
        } else {
            if (!$this->stop_transaction_start_end)
                $this->db->query("ROLLBACK");
        }
        return array_unique($error);
    }

    public function edit_damage() {
        if (!$this->stop_transaction_start_end)
            $this->db->query('START TRANSACTION');

        $date = isset($this->info['damage_date']) ? $this->info['damage_date'] : Date::getToday();
        $check = CommonFunctions::check_lock_data(['type' => 'dmg', 'start_date' => $date, 'is_actual' => $this->info['is_actual']]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $app_user_id = isset($this->info['app_user_id']) ? $this->info['app_user_id'] : 0;
        $app_damage_id = isset($this->info['app_damage_id']) ? $this->info['app_damage_id'] : 0;
        $app_branch_id = isset($this->info['app_branch_id']) ? $this->info['app_branch_id'] : 0;

        $branch_id = CommonBusiness::getBranchByDepot($this->info['Depot_ID']);
        if (isset($this->info['damage_prefix']))
            $damage_prefix = $this->info['damage_prefix'];
        else {
            $query_branch = $this->db->query('select branch_prefix from tbl_organization_branches where branch_id = "' . $branch_id . '"');
            $branch_prefix = $this->db->assoc($query_branch)['branch_prefix'];
            $damage_prefix = smc_session()['user_accounts']['serial_prefix'] ? smc_session()['user_accounts']['serial_prefix'] : $branch_prefix;
        }
        $damage_nb = isset($this->info['damage_nb']) ? $this->info['damage_nb'] : self::getMaxDamageID($damage_prefix, $this->info['is_actual']);
        $datetime = date('Y-m-d H:i:s');
        $customer_id = isset($this->info['customer_id']) ? $this->info['customer_id'] : NULL;
        $this->info['return_id'] = isset($this->info['return_id']) ? $this->info['return_id'] : NULL;
        $this->info['created_logged_id'] = isset($this->info['created_logged_id']) ? $this->info['created_logged_id'] : smc_session()['user_logged_id'];
        $this->info['Logged_ID'] = isset($this->info['Logged_ID']) ? $this->info['Logged_ID'] : smc_session()['user_logged_id'];
        $this->info['date_created'] = isset($this->info['date_created']) ? $this->info['date_created'] : $datetime;
        $this->info['date_time'] = isset($this->info['date_time']) ? $this->info['date_time'] : $datetime;
        $mydate = date('Y-m-d H:i:s');

        $query = $this->db->query("SELECT 
                                        d.product_id,d.current_quantity,d.quantity,
                                        d.UOM_ID,d.unit_cost,i.Depot_ID,i.date_time,
                                        i.damage_prefix, i.is_actual,i.damage_id,i.created_logged_id,i.date_created,
                                        d.expiry_date,i.app_damage_id,i.app_branch_id,i.app_user_id,d.lot_nb,
                                        d.serial_nb,pr.is_service,
                                        i.customer_id,d.detail_id,i.damage_nb
                                    FROM 
                                        tbl_damage_details d
                                    join tbl_damage i on i.damage_id = d.damage_id
                                    join tbl_stock_products pr on pr.Product_ID = d.product_id
                                    WHERE d.damage_id = '" . $this->info['damage_id'] . "' 
                ");
        while ($row = $this->db->assoc($query)) {
            //if already filled keep it as it is, it may be filled from sync, app. ditribution,..
            $app_damage_id = empty($row['app_damage_id']) ? $app_damage_id : $row['app_damage_id'];
            $app_branch_id = empty($row['app_branch_id']) ? $app_branch_id : $row['app_branch_id'];
            $app_user_id = empty($row['app_user_id']) ? $app_user_id : $row['app_user_id'];
            $main_nb = $row['damage_nb'];
            $damage_num = $row['damage_prefix'] . $row['damage_nb'];
            $prefix = $row['damage_prefix'];
            $old_created_logged_id = $row['created_logged_id'];
            $old_date_time = $row['date_time'];
            $old_date_created = $row['date_created'];
            $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'] : '';
//            $ProductQuantityRate = $row['Quantity'] / $row['Current_Quantity'];
            $costdata = [
                "Product_ID" => '',
                "Quantity" => '',
                "Unit_Cost" => '',
                "unit_price" => '',
                "Currency_ID" => smc_session()['default_setting']['local_currency'],
                "date_time" => $row['date_time'],
                "date_time_now" => $mydate,
                "current_rate" => 1,
                "depot_id" => $row['Depot_ID'],
                "trans_date" => $date,
                "current_quantity" => '',
                "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['damage_id'],
                "is_service" => $row['is_service'],
                "main_nb" => $damage_num,
                "customer_id" => $row['customer_id']
            ];

            $product_links = CommonBusiness::get_product_links($row['product_id'], $row['UOM_ID'], $row['quantity'], $row['current_quantity'], '-', 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'];
                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'], smc_session()['local_currency'], $cost);
                }
                CommonBusiness::updateLinkQuantities($costdata, "edit_damage_old", ($link['operation'] == '+' ? '-' : '+'));
            }
        }
        CommonBusiness::update_trans_record_current('dmg', $this->info['damage_id']);



        $data = [ $date, $customer_id, $this->info['Depot_ID'], $this->info['note'],
            $this->info['created_logged_id'], $this->info['Logged_ID'],
            $this->info['date_created'], $this->info['date_time'], $app_user_id, $app_damage_id,
            $app_branch_id, $branch_id, $this->info['damage_id']];
        $this->db->prepare_Stmt('update `tbl_damage` set 
                `damage_date`=?, `customer_id`=?, `Depot_ID`=?, `damage_note`=?, 
                `created_logged_id`=?, `Logged_ID`=?, `date_created`=?, `date_time`=?, `app_user_id`=?,
                `app_damage_id`=?,app_branch_id=?,branch_id=? where damage_id=?', $data);
        $damage_id = $this->info['damage_id'];
        $this->db->prepare_Stmt('delete from tbl_damage_details where damage_id = ?', [$damage_id]);
        foreach ($this->info['Products'] as $item) {
            $expiry_date = !empty($item['expiry_date']) ? $item['expiry_date'] : null;
            $ProductQuantity = Product::getConvertedQuantity($item['UOM_ID'], $item['quantity'], $item['Product_ID']);
            $return_detail_id = !empty($item['return_detail_id']) ? $item['return_detail_id'] : null;
            $lot_nb = !empty($item['lot_nb']) ? $item['lot_nb'] : '';
            $serial_nb = !empty($item['serial_nb']) ? $item['serial_nb'] : '';
            $item['note'] = !empty($item['note']) ? $item['note'] : '';
            $item['is_service'] = isset($item['is_service']) ? $item['is_service'] : 0;
            $costdata = [
                "Product_ID" => $item['Product_ID'],
                "Quantity" => $ProductQuantity,
                "depot_id" => $this->info['Depot_ID'],
                "trans_date" => $date,
                "Unit_Cost" => "",
                "Currency_ID" => smc_session()['local_currency'],
                "date_time" => $datetime,
                "date_time_now" => date('Y-m-d H:i:s'),
                "current_rate" => 1,
                "current_quantity" => $item['quantity'],
                "uom_id" => $item['UOM_ID'],
                "Operation" => "-",
                "is_actual" => $this->info['is_actual'],
                "expiry_date" => $expiry_date,
                "lot_nb" => $lot_nb,
                "serial_nb" => $serial_nb,
                "main_id" => $damage_id,
                "is_service" => $item['is_service'],
                "main_nb" => $damage_prefix . $damage_nb,
                "customer_id" => $customer_id,
                "trans_note" => $item['note']
            ];
            $product_links = CommonBusiness::get_product_links($item['Product_ID'], $item['UOM_ID'], $ProductQuantity, $item['quantity'], '-');
            $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'];
                $costdata['Unit_Cost'] = CommonBusiness::viewCurrentProductCost($link['Product_ID']);
                $cost +=$costdata['Unit_Cost'];
                CommonBusiness::updateLinkQuantities($costdata, "edit_damage_new", $link['operation']);
            }
            $damaged = [
                $damage_id, &$item['Product_ID'], &$ProductQuantity, $item['quantity'], &$item['UOM_ID'],
                $expiry_date, &$lot_nb, &$serial_nb, $cost, $return_detail_id, $item['note']];
            $this->db->prepare_Stmt("INSERT INTO `tbl_damage_details`( `damage_id`, `product_id`, `quantity`,
                `current_quantity`, `UOM_ID`, `expiry_date`, `lot_nb`, `serial_nb`, `unit_cost`,
                `return_detail_id`, `detail_note`) values(?,?,?,?,?,?,?,?,?,?,?)

        ", $damaged);
        }


        $error = $this->db->error();
        if (empty($error)) {
            if (!$this->stop_transaction_start_end)
                $this->db->query("COMMIT");
                if(!empty($this->info['damage_id'])){
                    return [$this->info['damage_id']];
                }
        } else {
            if (!$this->stop_transaction_start_end)
                $this->db->query("ROLLBACK");
        }
        return array_unique($error);
    }

    public function get_damage_by_id($id) {
        $data = array();
        $query = $this->db->query('SELECT d.*,ifnull(c.Customer_Name,"") as Customer_Name,
            dp.Depot_Name
            FROM `tbl_damage` d 
            join tbl_stock_depot_warehouse dp on dp.Depot_ID = d.Depot_ID
            left join tbl_customers c on c.Customer_ID = d.customer_id
            WHERE damage_id = "' . $id . '"');
        $data['main'] = $this->db->assoc($query);
        //
        $query = $this->db->query('select d.*,p.Product_Code,p.Product_Name,is_lot,p.is_serial,p.is_service,
            (if(isnull(l.Product_Depot_Quantity),0,l.Product_Depot_Quantity)+d.quantity) as total_default_quantity,
            c.Cat_Name,p.is_expiry,p.Category_ID,uc.conversion_amount,u.UOM_Name 
            from tbl_damage_details d join tbl_stock_products p 
            on p.Product_ID = d.product_id join tbl_stock_uom u on u.UOM_ID = d.UOM_ID 
            join tbl_product_cat c on c.Cat_ID = p.Category_ID
            inner join tbl_uom_conversions uc
            on uc.Product_ID = d.product_id and d.UOM_ID = uc.uom_from
            left join tbl_stock_product_location l
            on l.Location_Product_ID = d.Product_ID and l.Product_Depot_ID = "' . $data['main']['Depot_ID'] . '"
            where d.damage_id = "' . $id . '"');
        $prod_ids = array();
        while ($row = $this->db->assoc($query)) {
            $data['details'][] = $row;
            $prod_ids[$row['product_id']] = $row['product_id'];
        }
        if (!empty($prod_ids)) {
            $query_uom = $this->db->query('select uom_from,Product_ID from tbl_uom_conversions where Product_ID in(' . implode(',', $prod_ids) . ')');
            while ($row_uom = $this->db->assoc($query_uom)) {
                $data['uoms'][$row_uom['Product_ID']][] = $row_uom['uom_from'];
            }
        }
        return $data;
    }

    public function delete_damage($id) {
        if (!$this->stop_transaction_start_end)
            $this->db->query('START TRANSACTION');
        $mydate = date('Y-m-d H:i:s');
        $date = date('Y-m-d');
        $query = $this->db->query("SELECT 
                                        d.product_id,d.current_quantity,d.quantity,
                                        d.UOM_ID,d.unit_cost,i.depot_id,i.date_time,
                                        i.damage_prefix, i.is_actual,i.damage_id,i.created_logged_id,i.date_created,
                                        d.expiry_date,i.app_damage_id,i.app_branch_id,i.app_user_id,d.lot_nb,
                                        d.serial_nb,pr.is_service,
                                        i.customer_id,d.detail_id,i.damage_nb
                                    FROM 
                                        tbl_damage_details d
                                    join tbl_damage i on i.damage_id = d.damage_id
                                    join tbl_stock_products pr on pr.Product_ID = d.product_id
                                    WHERE d.damage_id = '" . $id . "' 
                ");
        while ($row = $this->db->assoc($query)) {
            $damage_num = $row['damage_prefix'] . $row['damage_nb'];
            $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" => '',
                "Quantity" => '',
                "Unit_Cost" => '',
                "unit_price" => '',
                "Currency_ID" => smc_session()['default_setting']['local_currency'],
                "date_time" => $row['date_time'],
                "date_time_now" => $mydate,
                "current_rate" => 1,
                "depot_id" => $row['depot_id'],
                "trans_date" => $date,
                "current_quantity" => '',
                "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_service" => $row['is_service'],
                "main_nb" => $damage_num,
                "customer_id" => $row['customer_id']
            ];

            $product_links = CommonBusiness::get_product_links($row['product_id'], $row['UOM_ID'], $row['quantity'], $row['current_quantity'], '-', 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'];
                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'], smc_session()['local_currency'], $cost);
                }
                CommonBusiness::updateLinkQuantities($costdata, "delete_damage", ($link['operation'] == '+' ? '-' : '+'));
            }
        }
        CommonBusiness::update_trans_record_current('dmg', $id);
        $this->db->prepare_Stmt('delete from tbl_damage_details where damage_id = ?', [&$id]);
        $this->db->prepare_Stmt('delete from tbl_damage where damage_id = ?', [&$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;
    }

    public function get_assign_nb($is_actual = 0, $type = 0) {
        $query = $this->db->query('select MAX(assign_nb) as max_nb from tbl_assign_product
            where is_actual = "' . $is_actual . '" and assign_type="' . $type . '"');
        $row = $this->db->assoc($query);
        if (!empty($row))
            $assign = $row['max_nb'] + 1;
        else
            $assign = 1;
        return $assign;
    }

    public function add_assign($stop_trans = false) {
        $error = token_unique::is_valid_token();
//        echo"<pre>";die(print_r($error));
        if (!empty($error))
            return $error;
        $check = CommonFunctions::check_lock_data(['type' => 'assign', 'start_date' => $this->info['assign_date'], 'is_actual' => $this->info['is_actual']]);

//        $check = JV::checkCurrentYear($this->info['assign_date'], $this->info['assign_date'], $this->info['is_actual']);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        if (!$stop_trans)
            $this->db->query('START TRANSACTION');
        $mydate = date("Y-m-d H:i:s");
        $logged_id = smc_session()['user_logged_id'];
        $assign_nb = $this->get_assign_nb($this->info['is_actual'], $this->info['assign_type']);
        $rate = CurrencyFactory::getLastExRate($this->info['Currency_ID']);
        $second_rate = CurrencyFactory::getSecondCurrencyLastExRate($this->info['Currency_ID']);

        $data = [$assign_nb, $this->info['assign_date'], $this->info['Depot_ID'], $this->info['note'],
            $this->info['assign_type'], $this->year_id, $mydate, $mydate, $logged_id, $logged_id,
            $this->info['Currency_ID'], $rate, $second_rate, $this->info['is_actual']];
        $this->db->prepare_Stmt('INSERT INTO `tbl_assign_product`(`assign_nb`, `assign_date`, `Depot_ID`,
            `assign_note`, `assign_type`, `current_year_id`, `date_created`, `date_time`, `created_logged_id`,
            `Logged_ID`,Currency_ID,current_rate,second_currency_rate,is_actual) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)', $data);
        $assign_id = $this->db->id();
        foreach ($this->info['Products'] as $item) {
            $diff_qty = abs($item['diff_qty']);
            $product_quantity = Product::getConvertedQuantity($item['UOM_ID'], $item['quantity'], $item['Product_ID']);
            $real_prd_diff_qty = Product::getConvertedQuantity($item['UOM_ID'], $item['diff_qty'], $item['Product_ID']);
            $prd_diff_qty = abs($real_prd_diff_qty);
            //            $total += $product_quantity;
//            $rate = CurrencyFactory::getLastExRate($item['Currency_ID']);
            $expiry_date = !empty($item['expiry_date']) ? $item['expiry_date'] : null;
            $lot_nb = !empty($item['lot_nb']) ? $item['lot_nb'] : '';
            $serial_nb = !empty($item['serial_nb']) ? $item['serial_nb'] : '';
            $new_cost = $item['unit_cost'] / ($prd_diff_qty / $diff_qty);
            $detail_data = [$assign_id, $item['Product_ID'], $item['UOM_ID'], $item['quantity'], $product_quantity,
                $item['diff_qty'], $real_prd_diff_qty, $item['note'], $item['unit_cost'], $lot_nb, $serial_nb, $expiry_date];
            $this->db->prepare_Stmt('INSERT INTO `tbl_assign_product_details`(`assign_id`, `Product_ID`, `UOM_ID`,
                `current_quantity`, `quantity`, `current_diff_qty`, `diff_qty`, `detail_note`, `unit_cost`, `lot_nb`, 
                `serial_nb`, `expiry_date`) values(?,?,?,?,?,?,?,?,?,?,?,?)', $detail_data);
            $costdata = [
                "Product_ID" => $item['Product_ID'],
                "Quantity" => $prd_diff_qty,
                "Unit_Cost" => $new_cost,
                "Currency_ID" => $this->info['Currency_ID'],
                "date_time" => $mydate,
                "date_time_now" => $mydate,
                "current_rate" => $rate,
                "depot_id" => $this->info['Depot_ID'],
                "trans_date" => $this->info['assign_date'],
                "current_quantity" => $diff_qty,
                "uom_id" => $item['UOM_ID'],
                "is_actual" => $this->info['is_actual'],
                "expiry_date" => $expiry_date,
                "lot_nb" => $lot_nb,
                "serial_nb" => $serial_nb,
                "main_id" => $assign_id,
                "is_service" => $item['is_service'],
                "trans_note" => !empty($item['note']) ? $item['note'] : $this->info['note']
            ];
            $type = $this->info['assign_type'] == 0 ? 'assign' : ($item['diff_qty'] >= 0 ? 'adjust_positive' : 'adjust_negative');

            CommonBusiness::updateLinkQuantities($costdata, $type, ($item['diff_qty'] >= 0 ? '+' : '-'));
        }
        $error = $this->db->error();

        if (empty($error)) {
            if (!$stop_trans) {
//                echo"<pre>";die(print_r($error));
                $this->db->query("COMMIT");
            }
            token_unique::remove_token();
            return [$assign_id];
        } else {
            if (!$stop_trans) {
                $this->db->query("ROLLBACK");
            }
        }
        return array_unique($error);
    }

    public function edit_assign() {
        if (!$this->stop_transaction_start_end)
            $this->db->query('START TRANSACTION');
        $check = CommonFunctions::check_lock_data(['type' => 'assign', 'start_date' => $this->info['assign_date'], 'is_actual' => $this->info['is_actual']]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $mydate = date("Y-m-d H:i:s");
        $logged_id = smc_session()['user_logged_id'];
        $rate = CurrencyFactory::getLastExRate($this->info['Currency_ID']);
        $second_rate = CurrencyFactory::getSecondCurrencyLastExRate($this->info['Currency_ID']);
        //
        $query = $this->db->query('select d.Product_ID,d.diff_qty,d.unit_cost,a.Currency_ID,a.current_rate,
            a.date_time,a.Depot_ID,d.current_diff_qty,d.UOM_ID,a.is_actual,d.expiry_date,d.lot_nb,d.serial_nb,
            p.is_service
            from tbl_assign_product_details d join tbl_assign_product a
            on a.assign_id = d.assign_id 
            join tbl_stock_products p on p.Product_ID = d.Product_ID
            where d.assign_id = "' . $this->info['assign_id'] . '"
            ');
        while ($row = $this->db->assoc($query)) {
            $old_diff_qty = abs($row['diff_qty']);
            $old_current_diff_qty = abs($row['current_diff_qty']);
            $costdata = [
                "Product_ID" => $row['Product_ID'],
                "Quantity" => $old_diff_qty,
                "Unit_Cost" => $row['unit_cost'],
                "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" => $this->info['assign_date'],
                "current_quantity" => $old_current_diff_qty,
                "uom_id" => $row['UOM_ID'],
                "is_actual" => $row['is_actual'],
                "expiry_date" => $row['expiry_date'],
                "lot_nb" => $row['lot_nb'],
                "serial_nb" => $row['serial_nb'],
                "main_id" => $this->info['assign_id'],
                "is_service" => $row['is_service']
            ];
            $type = $this->info['assign_type'] == 0 ? 'edit_assign_old' : ($row['diff_qty'] >= 0 ? 'edit_adjust_old_positive' : 'edit_adjust_old_negative');
//            echo $type.'===';die();
            CommonBusiness::updateLinkQuantities($costdata, $type, ($row['diff_qty'] >= 0 ? '-' : '+'));
        }
        CommonBusiness::update_trans_record_current($this->info['assign_type'] == 0 ? 'asg' : 'adj', $this->info['assign_id']);
        //

        $data = [$this->info['assign_date'], $this->info['Depot_ID'], $this->info['note'],
            $mydate, $logged_id, $this->info['Currency_ID'], $rate, $second_rate, $this->info['assign_id']];
        $this->db->prepare_Stmt('update `tbl_assign_product` set `assign_date`=?, `Depot_ID`=?,
            `assign_note`=?, `date_time`=?,`Logged_ID`=?,Currency_ID=?,current_rate=?,
            second_currency_rate=? where assign_id = ?', $data);
        $assign_id = $this->info['assign_id'];
        $this->db->prepare_Stmt('delete from tbl_assign_product_details where assign_id = ?', [$this->info['assign_id']]);
        foreach ($this->info['Products'] as $item) {
            $diff_qty = abs($item['diff_qty']);
            $product_quantity = Product::getConvertedQuantity($item['UOM_ID'], $item['quantity'], $item['Product_ID']);
            $real_prd_diff_qty = Product::getConvertedQuantity($item['UOM_ID'], $item['diff_qty'], $item['Product_ID']);
            $prd_diff_qty = abs($real_prd_diff_qty);
            $expiry_date = !empty($item['expiry_date']) ? $item['expiry_date'] : null;
            $lot_nb = !empty($item['lot_nb']) ? $item['lot_nb'] : '';
            $serial_nb = !empty($item['serial_nb']) ? $item['serial_nb'] : '';
            $new_cost = $item['unit_cost'] / ($prd_diff_qty / $diff_qty);
            $detail_data = [$assign_id, $item['Product_ID'], $item['UOM_ID'], $item['quantity'], $product_quantity,
                $item['diff_qty'], $real_prd_diff_qty, $item['note'], $new_cost, $lot_nb, $serial_nb, $expiry_date];
            $this->db->prepare_Stmt('INSERT INTO `tbl_assign_product_details`(`assign_id`, `Product_ID`, `UOM_ID`,
                `current_quantity`, `quantity`, `current_diff_qty`, `diff_qty`, `detail_note`, `unit_cost`, `lot_nb`, 
                `serial_nb`, `expiry_date`) values(?,?,?,?,?,?,?,?,?,?,?,?)', $detail_data);
            $costdata = [
                "Product_ID" => $item['Product_ID'],
                "Quantity" => $prd_diff_qty,
                "Unit_Cost" => $new_cost,
                "Currency_ID" => $this->info['Currency_ID'],
                "date_time" => $mydate,
                "date_time_now" => $mydate,
                "current_rate" => $rate,
                "depot_id" => $this->info['Depot_ID'],
                "trans_date" => $this->info['assign_date'],
                "current_quantity" => $diff_qty,
                "uom_id" => $item['UOM_ID'],
                "is_actual" => $this->info['is_actual'],
                "expiry_date" => $expiry_date,
                "lot_nb" => $lot_nb,
                "serial_nb" => $serial_nb,
                "main_id" => $assign_id,
                "is_service" => $item['is_service'],
                "trans_note" => !empty($item['note']) ? $item['note'] : $this->info['note']
            ];
            $type = $this->info['assign_type'] == 0 ? 'edit_assign_new' : ($item['diff_qty'] >= 0 ? 'edit_adjust_new_positive' : 'edit_adjust_new_negative');
            CommonBusiness::updateLinkQuantities($costdata, $type, ($item['diff_qty'] >= 0 ? '+' : '-'));
        }
        $error = $this->db->error();
        if (empty($error)) {
            if (!$this->stop_transaction_start_end)
                $this->db->query("COMMIT");
                if(!empty($this->info['assign_id']))
                    return [$this->info['assign_id']];

        } else {
            if (!$this->stop_transaction_start_end)
                $this->db->query("ROLLBACK");
        }
        return array_unique($error);
    }

    public function delete_assign($id) {
        $this->db->query('START TRANSACTION');
        $mydate = date('Y-m-d H:i:s');
        $today = date('Y-m-d');
        $assign_type = 0;
        $query = $this->db->query('select assign_date,is_actual from tbl_assign_product 
            where assign_id = "' . $id . '"');
        $row = $this->db->assoc($query);
        $check = CommonFunctions::check_lock_data(['type' => 'assign', 'start_date' => $row['assign_date'], 'is_actual' => $row['is_actual']]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $query = $this->db->query('select d.Product_ID,d.diff_qty,d.unit_cost,a.Currency_ID,a.current_rate,
            a.date_time,a.Depot_ID,d.current_diff_qty,d.UOM_ID,a.is_actual,d.expiry_date,d.lot_nb,d.serial_nb,
            p.is_service,a.assign_type
            from tbl_assign_product_details d join tbl_assign_product a
            on a.assign_id = d.assign_id 
            join tbl_stock_products p on p.Product_ID = d.Product_ID
            where d.assign_id = "' . $id . '"
            ');
        while ($row = $this->db->assoc($query)) {
            $old_diff_qty = abs($row['diff_qty']);
            $old_current_diff_qty = abs($row['current_diff_qty']);
            $assign_type = $row['assign_type'];
            $costdata = [
                "Product_ID" => $row['Product_ID'],
                "Quantity" => $old_diff_qty,
                "Unit_Cost" => $row['unit_cost'],
                "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" => $today,
                "current_quantity" => $old_current_diff_qty,
                "uom_id" => $row['UOM_ID'],
                "is_actual" => $row['is_actual'],
                "expiry_date" => $row['expiry_date'],
                "lot_nb" => $row['lot_nb'],
                "serial_nb" => $row['serial_nb'],
                "main_id" => $id,
                "is_service" => $row['is_service']
            ];
            $type = $row['assign_type'] == 0 ? 'delete_assign' : ($row['diff_qty'] >= 0 ? 'delete_adjust_positive' : 'delete_adjust_negative');

            CommonBusiness::updateLinkQuantities($costdata, $type, ($row['diff_qty'] >= 0 ? '-' : '+'));
        }
        CommonBusiness::update_trans_record_current($assign_type == 0 ? 'asg' : 'adj', $id);

        $this->db->prepare_Stmt('delete from tbl_assign_product_details where assign_id = ?', [$id]);
        $this->db->prepare_Stmt('delete from tbl_assign_product where assign_id = ?', [$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 array_unique($error);
    }

    public function get_assign_by_id($id) {
        $data = array();
        $query = $this->db->query('SELECT d.*,dp.Depot_Name,cu.*
            FROM `tbl_assign_product` d 
            join tbl_stock_depot_warehouse dp on dp.Depot_ID = d.Depot_ID
            join tbl_common_currency cu on cu.Currency_ID = d.Currency_ID
            WHERE assign_id = "' . $id . '"');
        $data['main'] = $this->db->assoc($query);
        //
        $query = $this->db->query('select d.*,p.Product_Code,p.Product_Name,is_lot,p.is_serial,p.is_service,UOM_Name,
            (if(isnull(l.Product_Depot_Quantity),0,l.Product_Depot_Quantity)+d.diff_qty) as total_default_quantity,
            (d.current_quantity-d.current_diff_qty) as system_quantity,
            c.Cat_Name,p.is_expiry,p.Category_ID,uc.conversion_amount 
            from tbl_assign_product_details d join tbl_stock_products p 
            on p.Product_ID = d.product_id join tbl_stock_uom u on u.UOM_ID = d.UOM_ID 
            join tbl_product_cat c on c.Cat_ID = p.Category_ID
            inner join tbl_uom_conversions uc
            on uc.Product_ID = d.Product_ID and d.UOM_ID = uc.uom_from
            left join tbl_stock_product_location l
            on l.Location_Product_ID = d.Product_ID and l.Product_Depot_ID = "' . $data['main']['Depot_ID'] . '"
            where d.assign_id = "' . $id . '"');
        $prod_ids = array();
        while ($row = $this->db->assoc($query)) {
            $data['details'][] = $row;
            $prod_ids[$row['Product_ID']] = $row['Product_ID'];
        }
        if (!empty($prod_ids)) {
            $query_uom = $this->db->query('select uom_from,Product_ID from tbl_uom_conversions where Product_ID in(' . implode(',', $prod_ids) . ')');
            while ($row_uom = $this->db->assoc($query_uom)) {
                $data['uoms'][$row_uom['Product_ID']][] = $row_uom['uom_from'];
            }
        }
        return $data;
    }

    public function confirm_delivered_items() {
        $this->db->query('START TRANSACTION');
        if (!empty($this->info['Products'])) {
            foreach ($this->info['Products'] as $item) {
                $default_qty = Product::getConvertedQuantity($item['UOM_ID'], $item['Request_Qty'], $item['Product_ID']);
                $data = [$default_qty, $item['Request_Qty'], $item['detail_note'], $item['entry_id']];
                $this->db->prepare_Stmt('update tbl_stock_request_details 
                    set delivered_def_qty=?,delivered_current_qty=?,delivered_note=? where Entry_ID=?', $data);
            }
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query("COMMIT");
        } else {
            $this->db->query("ROLLBACK");
        }
        return array_unique($error);
    }

    public function add_stock_acc_adjust() {
        $error = token_unique::is_valid_token();
        if (!empty($error))
            return $error;
        $check = CommonFunctions::check_lock_data(['type' => 'adjust', 'start_date' => $this->info['assign_date'], 'is_actual' => $this->info['is_actual']]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $this->db->query('START TRANSACTION');
        $mydate = date("Y-m-d H:i:s");
        $logged_id = smc_session()['user_logged_id'];
        $adjust_nb = $this->get_adjust_nb($this->info['is_actual']);
        $rate = CurrencyFactory::getLastExRate($this->info['Currency_ID']);
        $second_rate = CurrencyFactory::getSecondCurrencyLastExRate($this->info['Currency_ID']);

        $data = [$adjust_nb, $this->info['assign_date'], $this->info['credit_acc_id'],
            $this->info['debit_acc_id'], $this->info['Depot_ID'], $this->info['note'],
            $logged_id, $logged_id, $mydate, $mydate, $this->year_id, $this->info['is_actual'],
            $this->info['Currency_ID'], $rate, $second_rate];
        $this->db->prepare_Stmt('INSERT INTO `tbl_stock_acc_adjustment`( `adjust_nb`, `adjust_date`,
            `credit_acc_id`, `debit_acc_id`, `depot_id`, `note`, `created_logged_id`, `Logged_ID`, `date_created`,
            `date_time`, `current_year_id`, `is_actual`, `Currency_ID`, `current_rate`, `second_currency_rate`)
            values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', $data);
        $adjust_id = $this->db->id();
        $total = 0;
        foreach ($this->info['Products'] as $item) {
            $total+=($item['diff_qty'] * $item['unit_cost']);
            $default_qty = Product::getConvertedQuantity($item['UOM_ID'], $item['diff_qty'], $item['Product_ID']);
            $expiry_date = !empty($item['expiry_date']) ? $item['expiry_date'] : null;
            $lot_nb = !empty($item['lot_nb']) ? $item['lot_nb'] : '';
            $serial_nb = !empty($item['serial_nb']) ? $item['serial_nb'] : '';
            $new_cost = $item['unit_cost'] / ($default_qty / $item['diff_qty']);
            $detail_data = [$adjust_id, $item['Product_ID'], $item['UOM_ID'], $default_qty, $item['diff_qty'],
                $item['note'], $expiry_date, $serial_nb, $lot_nb, $item['unit_cost']];
            $this->db->prepare_Stmt('INSERT INTO `tbl_stock_acc_adjust_details`(`adjust_id`, `Product_ID`,
                `UOM_ID`, `quantity`, `current_quantity`, `note`, `expiry_date`, `serial_nb`, `lot_nb`, `cost`)
                values(?,?,?,?,?,?,?,?,?,?)', $detail_data);
            $costdata = [
                "Product_ID" => $item['Product_ID'],
                "Quantity" => $default_qty,
                "Unit_Cost" => $new_cost,
                "Currency_ID" => $this->info['Currency_ID'],
                "date_time" => $mydate,
                "date_time_now" => $mydate,
                "current_rate" => $rate,
                "depot_id" => $this->info['Depot_ID'],
                "trans_date" => $this->info['assign_date'],
                "current_quantity" => $item['diff_qty'],
                "uom_id" => $item['UOM_ID'],
                "is_actual" => $this->info['is_actual'],
                "expiry_date" => $expiry_date,
                "lot_nb" => $lot_nb,
                "serial_nb" => $serial_nb,
                "main_id" => $adjust_id,
                "is_service" => $item['is_service'],
                "trans_note" => !empty($item['note']) ? $item['note'] : $this->info['note']
            ];

            CommonBusiness::updateLinkQuantities($costdata, 'add_stock_acc_adjust', '-');
        }
        $jv = JV::returnJV($this->info['is_actual']);
        $branch_id = CommonBusiness::getBranchByDepot($this->info['Depot_ID']);
        $dat_2 = ["JV_ID" => $jv,
            "Transaction_Reference" => $adjust_nb,
            "Transaction_Account" => $this->info['debit_acc_id'],
            "Transaction_Type" => 'Debit',
            "Transaction_Amount" => $total,
            "Transaction_Date" => $this->info['assign_date'],
            "Currency_ID" => $this->info['Currency_ID'],
            "Logged_ID" => $logged_id,
            "created_logged_id" => $logged_id,
            "date_created" => $mydate,
            "date_time" => $mydate,
            "Transaction_Description" => $this->info['note'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $adjust_id,
            "main_type" => 'sad',
            "branch_id" => $branch_id,
            "main_nb" => $adjust_nb
        ];
        JV::addTransaction($dat_2, $this->info['is_actual']);
        $dat_2 = ["JV_ID" => $jv,
            "Transaction_Reference" => $adjust_nb,
            "Transaction_Account" => $this->info['credit_acc_id'],
            "Transaction_Type" => 'Credit',
            "Transaction_Amount" => $total,
            "Transaction_Date" => $this->info['assign_date'],
            "Currency_ID" => $this->info['Currency_ID'],
            "Logged_ID" => $logged_id,
            "created_logged_id" => $logged_id,
            "date_created" => $mydate,
            "date_time" => $mydate,
            "Transaction_Description" => $this->info['note'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $adjust_id,
            "main_type" => 'sad',
            "branch_id" => $branch_id,
            "main_nb" => $adjust_nb
        ];
        JV::addTransaction($dat_2, $this->info['is_actual']);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query("COMMIT");
            token_unique::remove_token();
        } else {
            $this->db->query("ROLLBACK");
        }
        return array_unique($error);
    }

    public function get_adjust_nb($is_actual = 0) {
        $query = $this->db->query('select MAX(adjust_nb) as max_nb from  tbl_stock_acc_adjustment
            where is_actual = "' . $is_actual . '" ');
        $row = $this->db->assoc($query);
        if (!empty($row))
            $assign = $row['max_nb'] + 1;
        else
            $assign = 1;
        return $assign;
    }

    public function edit_stock_acc_adjust() {
        $error = token_unique::is_valid_token();
        if (!empty($error))
            return $error;
        $check = CommonFunctions::check_lock_data(['type' => 'adjust', 'start_date' => $this->info['assign_date'], 'is_actual' => $this->info['is_actual']]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $this->db->query('START TRANSACTION');
        $mydate = date("Y-m-d H:i:s");
        $logged_id = smc_session()['user_logged_id'];
        $adjust_nb = '';
        $rate = CurrencyFactory::getLastExRate($this->info['Currency_ID']);
        $second_rate = CurrencyFactory::getSecondCurrencyLastExRate($this->info['Currency_ID']);
        $query = $this->db->query('select d.Product_ID,d.quantity,d.cost,a.Currency_ID,a.current_rate,
            a.date_time,a.Depot_ID,d.current_quantity,d.UOM_ID,a.is_actual,d.expiry_date,d.lot_nb,d.serial_nb,
            p.is_service,a.adjust_nb
            from tbl_stock_acc_adjust_details d join tbl_stock_acc_adjustment a
            on a.adjust_id = d.adjust_id
            join tbl_stock_products p on p.Product_ID = d.Product_ID
            where d.adjust_id = "' . $this->info['adjust_id'] . '"
            ');
        while ($row = $this->db->assoc($query)) {
            $adjust_nb = $row['adjust_nb'];
            $costdata = [
                "Product_ID" => $row['Product_ID'],
                "Quantity" => $row['quantity'],
                "Unit_Cost" => $row['cost'],
                "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" => $this->info['assign_date'],
                "current_quantity" => $row['current_quantity'],
                "uom_id" => $row['UOM_ID'],
                "is_actual" => $row['is_actual'],
                "expiry_date" => $row['expiry_date'],
                "lot_nb" => $row['lot_nb'],
                "serial_nb" => $row['serial_nb'],
                "main_id" => $this->info['adjust_id'],
                "is_service" => $row['is_service']
            ];
            CommonBusiness::updateLinkQuantities($costdata, 'edit_stock_acc_adjust_old', '+');
        }
        CommonBusiness::update_trans_record_current('sad', $this->info['adjust_id']);
        $data = [ $this->info['assign_date'], $this->info['credit_acc_id'],
            $this->info['debit_acc_id'], $this->info['Depot_ID'], $this->info['note'],
            $logged_id, $mydate, $this->info['Currency_ID'], $rate, $second_rate, $this->info['adjust_id']];
        $this->db->prepare_Stmt('update `tbl_stock_acc_adjustment` set `adjust_date`=?,
            `credit_acc_id`=?, `debit_acc_id`=?, `depot_id`=?, `note`=?,  `Logged_ID`=?,
            `date_time`=?,`Currency_ID`=?, `current_rate`=?, `second_currency_rate`=?
            where adjust_id = ?', $data);
        $adjust_id = $this->info['adjust_id'];
        //
        $jv_query = $this->db->query("SELECT DISTINCT JV_ID FROM tbl_accounting_transactions  WHERE main_id='" . $this->info['adjust_id'] . "' 
            AND main_type= 'sad' ");
        $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_stock_acc_adjust_details where adjust_id = ?', [$adjust_id]);
        $this->db->prepare_Stmt('delete from tbl_accounting_transactions where main_id = ? and main_type="sad"', [$adjust_id]);
        $total = 0;
        foreach ($this->info['Products'] as $item) {
            $total+=($item['diff_qty'] * $item['unit_cost']);
            $default_qty = Product::getConvertedQuantity($item['UOM_ID'], $item['diff_qty'], $item['Product_ID']);
            $expiry_date = !empty($item['expiry_date']) ? $item['expiry_date'] : null;
            $lot_nb = !empty($item['lot_nb']) ? $item['lot_nb'] : '';
            $serial_nb = !empty($item['serial_nb']) ? $item['serial_nb'] : '';
            $new_cost = $item['unit_cost'] / ($default_qty / $item['diff_qty']);
            $detail_data = [$adjust_id, $item['Product_ID'], $item['UOM_ID'], $default_qty, $item['diff_qty'],
                $item['note'], $expiry_date, $serial_nb, $lot_nb, $item['unit_cost']];
            $this->db->prepare_Stmt('INSERT INTO `tbl_stock_acc_adjust_details`(`adjust_id`, `Product_ID`,
                `UOM_ID`, `quantity`, `current_quantity`, `note`, `expiry_date`, `serial_nb`, `lot_nb`, `cost`)
                values(?,?,?,?,?,?,?,?,?,?)', $detail_data);
            $costdata = [
                "Product_ID" => $item['Product_ID'],
                "Quantity" => $default_qty,
                "Unit_Cost" => $new_cost,
                "Currency_ID" => $this->info['Currency_ID'],
                "date_time" => $mydate,
                "date_time_now" => $mydate,
                "current_rate" => $rate,
                "depot_id" => $this->info['Depot_ID'],
                "trans_date" => $this->info['assign_date'],
                "current_quantity" => $item['diff_qty'],
                "uom_id" => $item['UOM_ID'],
                "is_actual" => $this->info['is_actual'],
                "expiry_date" => $expiry_date,
                "lot_nb" => $lot_nb,
                "serial_nb" => $serial_nb,
                "main_id" => $adjust_id,
                "is_service" => $item['is_service'],
                "trans_note" => !empty($item['note']) ? $item['note'] : $this->info['note']
            ];

            CommonBusiness::updateLinkQuantities($costdata, 'edit_stock_acc_adjust_new', '-');
        }
        $jv = JV::returnJV($this->info['is_actual']);
        $branch_id = CommonBusiness::getBranchByDepot($this->info['Depot_ID']);
        $dat_2 = ["JV_ID" => $jv,
            "Transaction_Reference" => $adjust_nb,
            "Transaction_Account" => $this->info['debit_acc_id'],
            "Transaction_Type" => 'Debit',
            "Transaction_Amount" => $total,
            "Transaction_Date" => $this->info['assign_date'],
            "Currency_ID" => $this->info['Currency_ID'],
            "Logged_ID" => $logged_id,
            "created_logged_id" => $logged_id,
            "date_created" => $mydate,
            "date_time" => $mydate,
            "Transaction_Description" => $this->info['note'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $adjust_id,
            "main_type" => 'sad',
            "branch_id" => $branch_id,
            "main_nb" => $adjust_nb
        ];
        JV::addTransaction($dat_2, $this->info['is_actual']);
        $dat_2 = ["JV_ID" => $jv,
            "Transaction_Reference" => $adjust_nb,
            "Transaction_Account" => $this->info['credit_acc_id'],
            "Transaction_Type" => 'Credit',
            "Transaction_Amount" => $total,
            "Transaction_Date" => $this->info['assign_date'],
            "Currency_ID" => $this->info['Currency_ID'],
            "Logged_ID" => $logged_id,
            "created_logged_id" => $logged_id,
            "date_created" => $mydate,
            "date_time" => $mydate,
            "Transaction_Description" => $this->info['note'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $adjust_id,
            "main_type" => 'sad',
            "branch_id" => $branch_id,
            "main_nb" => $adjust_nb
        ];
        JV::addTransaction($dat_2, $this->info['is_actual']);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query("COMMIT");
            token_unique::remove_token();
        } else {
            $this->db->query("ROLLBACK");
        }
        return array_unique($error);
    }

    public function delete_stock_acc_adjust($id) {
        $query = $this->db->query('select adjust_date,is_actual from tbl_stock_acc_adjustment
            where adjust_id = "' . $id . '" ');
        $row = $this->db->assoc($query);
        $check = CommonFunctions::check_lock_data(['type' => 'adjust', 'start_date' => $row['adjust_date'], 'is_actual' => $row['is_actual']]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $this->db->query('START TRANSACTION');
        $mydate = date('Y-m-d H:i:s');
        $today = date('Y-m-d');
        $query = $this->db->query('select d.Product_ID,d.quantity,d.cost,a.Currency_ID,a.current_rate,
            a.date_time,a.Depot_ID,d.current_quantity,d.UOM_ID,a.is_actual,d.expiry_date,d.lot_nb,d.serial_nb,
            p.is_service
            from tbl_stock_acc_adjust_details d join tbl_stock_acc_adjustment a
            on a.adjust_id = d.adjust_id
            join tbl_stock_products p on p.Product_ID = d.Product_ID
            where d.adjust_id = "' . $id . '"
            ');
        while ($row = $this->db->assoc($query)) {

            $costdata = [
                "Product_ID" => $row['Product_ID'],
                "Quantity" => $row['quantity'],
                "Unit_Cost" => $row['cost'],
                "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" => $today,
                "current_quantity" => $row['current_quantity'],
                "uom_id" => $row['UOM_ID'],
                "is_actual" => $row['is_actual'],
                "expiry_date" => $row['expiry_date'],
                "lot_nb" => $row['lot_nb'],
                "serial_nb" => $row['serial_nb'],
                "main_id" => $id,
                "is_service" => $row['is_service']
            ];
            CommonBusiness::updateLinkQuantities($costdata, 'delete_stock_acc_adjust', '+');
        }
        CommonBusiness::update_trans_record_current('sad', $id);
        $this->db->prepare_Stmt('delete from tbl_accounting_transactions where main_id=? and main_type="sad" ', [$id]);
        $this->db->prepare_Stmt('delete from tbl_stock_acc_adjust_details where adjust_id = ?', [$id]);
        $this->db->prepare_Stmt('delete from tbl_stock_acc_adjustment where adjust_id = ?', [$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 array_unique($error);
    }

    public function get_stock_acc_adjust_by_id($id) {
        $data = array();
        $query = $this->db->query('SELECT d.*,dp.Depot_Name,cu.*,
            concat_ws(" - ",da.Account_Code,da.Account_Name) as debit_acc_name,
            concat_ws(" - ",ca.Account_Code,ca.Account_Name) as credit_acc_name
            FROM `tbl_stock_acc_adjustment` d 
            join tbl_stock_depot_warehouse dp on dp.Depot_ID = d.depot_id
            join tbl_common_currency cu on cu.Currency_ID = d.Currency_ID
            join tbl_chart_of_accounts da on da.Account_ID = d.debit_acc_id
            join tbl_chart_of_accounts ca on ca.Account_ID = d.credit_acc_id
            WHERE adjust_id = "' . $id . '"');
        $data['main'] = $this->db->assoc($query);
        //
        $query = $this->db->query('select d.*,p.Product_Code,p.Product_Name,is_lot,p.is_serial,p.is_service,
            (if(isnull(l.Product_Depot_Quantity),0,l.Product_Depot_Quantity)+d.quantity) as total_default_quantity,
            c.Cat_Name,p.is_expiry,p.Category_ID,uc.conversion_amount 
            from tbl_stock_acc_adjust_details d join tbl_stock_products p 
            on p.Product_ID = d.product_id join tbl_stock_uom u on u.UOM_ID = d.UOM_ID 
            join tbl_product_cat c on c.Cat_ID = p.Category_ID
            inner join tbl_uom_conversions uc
            on uc.Product_ID = d.Product_ID and d.UOM_ID = uc.uom_from
            left join tbl_stock_product_location l
            on l.Location_Product_ID = d.Product_ID and l.Product_Depot_ID = "' . $data['main']['depot_id'] . '"
            where d.adjust_id = "' . $id . '"');
        $prod_ids = array();
        while ($row = $this->db->assoc($query)) {
            $data['details'][] = $row;
            $prod_ids[$row['Product_ID']] = $row['Product_ID'];
        }
        if (!empty($prod_ids)) {
            $query_uom = $this->db->query('select uom_from,Product_ID from tbl_uom_conversions where Product_ID in(' . implode(',', $prod_ids) . ')');
            while ($row_uom = $this->db->assoc($query_uom)) {
                $data['uoms'][$row_uom['Product_ID']][] = $row_uom['uom_from'];
            }
        }
        return $data;
    }

}
