<?php
class Warehouse extends MY_Controller
{
    var $inventory;
    var $log_inventory;
    function __construct()
    {
        parent::__construct();
        $this->inventory = $this->load->database("inventory", true);
        $this->log_inventory = $this->load->database("inventory_log", true);
    }
    function index()
    {
        echo "Api: warehouse";
    }
    function search()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $prm = $this->sys_input;
            $search = "%%";
            if (isset($prm['search'])) {
                $search = trim($prm["search"]);
                $search = '%' . $prm['search'] . '%';
            }
            $order_by = "WarehouseLocationCode";
            if (isset($prm['order_by'])) {
                $order_by = trim($prm["order_by"]);
            }
            $order_type = "asc";
            if (isset($prm['order_type'])) {
                $order_type = trim($prm["order_type"]);
            }
            $order = $order_by . ' ' . $order_type;
            $perpage = 10;
            $offset = ($prm['current_page'] - 1) * $perpage;

            $count = "SELECT COUNT(*) as total 
                        FROM warehouselocation 
                        WHERE (WarehouseLocationName LIKE ? OR  WarehouseLocationCode  LIKE ?)
                        AND WarehouseLocationIsActive= 'Y'";
            $qry_count = $this->inventory->query($count, array($search, $search));
            $total_count = 0;
            $total_page = 0;
            if ($qry_count) {
                $total_count = $qry_count->row()->total;
                $total_page = ceil($total_count / $perpage);
            } else {
                $this->sys_error_db("Warehouse count error", $this->inventory->last_query());
                exit;
            }
            $rows = [];
            $sql = "SELECT
                    WarehouseLocationID as id, 
                    WarehouseLocationCode as code , 
                    WarehouseLocationName as name ,
                    fn_get_stock(WarehouseLocationID) as locationStock,
                    WarehouseLocationUserID as userId
                    FROM warehouselocation
                    WHERE (WarehouseLocationName  LIKE ? OR WarehouseLocationCode LIKE ?)
                    AND WarehouseLocationIsActive= 'Y'
                    ORDER BY $order 
                    LIMIT ? OFFSET ?";
            $qry = $this->inventory->query($sql, array($search, $search, $perpage, $offset));
            $lst_qryyy = $this->inventory->last_query();
            if ($qry) {
                $rows = $qry->result_array();
                if (count($rows) > 0) {
                    foreach ($rows as $key => $value) {
                        $sql = "SELECT 
                                WarehouseID AS wId, 
                                WarehouseCode AS wCode, 
                                WarehouseName AS wName,
                                WarehouseWarehouseLocationID AS wLocationId,
                                WarehouseIsOffice as isOffice,
                                WarehouseUserID AS wUserId,
                                fn_get_stock_warehouse(WarehouseID) AS warehouseStock
                                FROM warehouse 
                                WHERE WarehouseIsActive ='Y' 
                                AND WarehouseWarehouseLocationID = ? ";
                        $qry = $this->inventory->query($sql, array($value['id']));
                        if ($qry) {
                            $warehouse =
                                $qry->result_array();
                            if (count($warehouse) > 0) {
                                foreach ($warehouse as $index => $data) {
                                    $sql = "SELECT * FROM warehousealmari
                                            WHERE WarehouseAlmariWarehouseID = ? 
                                            AND WarehouseAlmariLocationID = ?
                                            AND WarehouseAlmariIsActive = 'Y'";
                                    $qry = $this->inventory->query($sql, array($data['wId'], $value['id']));
                                    if (!$qry) {
                                        $this->sys_error_db("Expedition data error", $this->inventory->last_query());
                                        exit;
                                    }
                                    $warehouse[$index]['almari'] = count($qry->result_array());
                                }
                            }
                            $rows[$key]['warehouse'] =
                                $warehouse;
                        }
                    }
                }
            } else {
                $this->sys_error_db("Expedition data error", $this->inventory->last_query());
                exit;
            }
            $result = array(
                "total" => $total_page,
                "total_filter" => $total_count,
                "records" => $rows,
                "qry" => $lst_qryyy
            );
            $this->sys_ok($result);
            exit;
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    function add()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $prm = $this->sys_input;
            $userid = $this->sys_user['M_UserID'];
            $name = "";
            if (isset($prm['name'])) {
                $name = trim($prm["name"]);
            }
            $isOffice = "";
            if (isset($prm['isOffice'])) {
                $isOffice = trim($prm["isOffice"]);
            }
            $name = "";
            if (isset($prm['name'])) {
                $name = trim($prm["name"]);
            }
            if ($name == "") {
                $error = array(
                    "message" => "name is mandatory",
                );
                $this->sys_error_db($error);
                exit;
            }
            $this->inventory->trans_start();
            $this->inventory->trans_strict(FALSE);
            $last_id = 0;
            $staff_log = array();
            $sql = "INSERT INTO warehouselocation
                    (WarehouseLocationCode,
                    WarehouseLocationName,
                    WarehouseLocationIsActive,
                    WarehouseLocationCreated,
                    WarehouseLocationLastUpdated,
                    WarehouseLocationUserID)
                    VALUES
                    ((select fn_numbering('WL')),
                    ?,
                    'Y',
                    NOW(),
                    NOW(),
                    ?)";
            $qry = $this->inventory->query($sql, array($name,  $userid));
            if ($qry) {
                $last_id = $this->inventory->insert_id();
            } else {
                $this->sys_error_db("save warehouse location error", $this->inventory->last_query());
                exit;
            }
            if (count($prm['warehouse']) > 0) {
                foreach ($prm['warehouse'] as $key => $value) {
                    $wName = trim($value['wName']);
                    $isOffice = trim($value['isOffice']);
                    $almari = trim($value['almari']);
                    if ($isOffice == 'Y') {
                        // IS OFFICE = Y
                        $sql  = "INSERT INTO warehouse
                            (WarehouseCode,
                            WarehouseName,
                            WarehouseWarehouseLocationID,
                            WarehouseIsActive,
                            WarehouseIsOffice,
                            WarehouseCreated,
                            WarehouseLastUpdated,
                            WarehouseUserID)
                            VALUES 
                            ((select fn_numbering('WH')),
                            ?,
                            ?,
                            'Y',
                            ?,
                            NOW(),
                            NOW(),
                            ?)";
                        $qry = $this->inventory->query($sql, array($wName, $last_id, $isOffice, $userid));
                        $insert_id = $this->inventory->insert_id();
                        if (!$qry) {
                            $this->sys_error_db("save warehouse error", $this->inventory->last_query());
                            exit;
                        }
                        // INSERT ALMARI

                        $sql = "INSERT INTO warehousealmari(
                                WarehouseAlmariLocationID,
                                WarehouseAlmariWarehouseID,
                                WarehouseAlmariCode,
                                WarehouseAlmariName,
                                WarehouseAlmariRows,
                                WarehouseAlmariCols,
                                WarehouseAlmariIsActive,
                                WarehouseAlmariUserID,
                                WarehouseAlmariCreated,
                                WarehouseAlmariLastUpdated)
                                VALUES(
                                ?,?,(select fn_numbering('AL')),?, ?,?,'Y',?,NOW(),NOW())";
                        $qry = $this->inventory->query($sql, array(
                            $last_id, $insert_id, "Almari Office " . $wName, 1, 1, $userid
                        ));
                        if ($qry) {
                            $inserted_almari_id = $this->inventory->insert_id();

                            $sql = "SELECT * FROM warehousealmari WHERE WarehouseAlmariID = ?";
                            $qry = $this->inventory->query($sql, array($inserted_almari_id));
                            $almari = $qry->row_array();
                            $almariCode = $almari['WarehouseAlmariCode'];
                            if (!$qry) {
                                $error = array(
                                    "message" => $this->inventory->error()["message"],
                                    "qry" => $this->inventory->last_query()
                                );
                                $this->sys_error_db($error);
                                exit;
                            }
                            //INSERT RAK
                            $name = "Rak Office 1 x 1";
                            $row = 1;
                            $col = 1;
                            $code = $almariCode . $row . $col;
                            $sql = "INSERT INTO warehouserack(
                            WarehouseRackWarehouseAlmariID,
                            WarehouseRackCode,
                            WarehouseRackName,
                            WarehouseRackRow,
                            WarehouseRackCol,
                            WarehouseRackIsActive,
                            WarehouseRackUserID,
                            WarehouseRackCreated,
                            WarehouseRackLastUpdated)
                            VALUES (
                            ?, ?, ?, ?,?,'Y', ?,NOW(),NOW()
                            )";
                            $qry = $this->inventory->query($sql, array($inserted_almari_id, $code, $name,  $row, $col, $userid));
                            if (!$qry) {
                                $error = array(
                                    "message" => $this->inventory->error()["message"],
                                    "qry" => $this->inventory->last_query()
                                );
                                $this->sys_error_db($error);
                                exit;
                            } else {
                                $rak_inserted_id = $this->inventory->insert_id();
                                $sql = "SELECT * FROM warehouserack WHERE WarehouseRackID = ?";
                                $qry = $this->inventory->query($sql, array($rak_inserted_id));

                                if (!$qry) {
                                    $error = array(
                                        "message" => $this->inventory->error()["message"],
                                        "qry" => $this->inventory->last_query()
                                    );
                                    $this->sys_error_db($error);
                                    exit;
                                } else {
                                    $rak_json_after  = $qry->row_array();

                                    $sql = "INSERT INTO warehouserack_log
                                    (WarehouseRackLogStatus,
                                    WarehouseRackLogWarehouseRackId,
                                    WarehouseRackLogJSONBefore,
                                    WarehouseRackLogJSONAfter,
                                    WarehouseRackLogUserId,
                                    WarehouseRackLogCreated)
                                    VALUES(
                                    'ADD', ?,NULL,?,?, NOW()
                                    )";
                                    $qry = $this->log_inventory->query($sql, array($rak_inserted_id, json_encode($rak_json_after), $userid));

                                    if (!$qry) {
                                        $error = array(
                                            "message" => $this->log_inventory->error()["message"],
                                            "qry" => $this->log_inventory->last_query()
                                        );
                                        $this->sys_error_db($error);
                                        exit;
                                    }
                                }
                            }
                            // END INSERT RAK
                        }
                        // END INSERT ALMARI

                        // ALMARI LOG
                        $sql = "SELECT * FROM warehousealmari WHERE WarehouseAlmariID= ?";
                        $qry = $this->inventory->query($sql, array($inserted_almari_id));

                        if (!$qry) {
                            $error = array(
                                "message" => $this->inventory->error()["message"],
                                "qry" => $this->inventory->last_query()
                            );
                            $this->sys_error_db($error);
                            exit;
                        } else {
                            $almari_json_after = $qry->row_array();
                            $sql_rak_json_after = "SELECT * FROM warehouserack  WHERE WarehouseRackWarehouseAlmariID= ?";
                            $qry_rak_json_after = $this->inventory->query($sql_rak_json_after, array($last_id));
                            if (!$qry_rak_json_after) {
                                $error = array(
                                    "message" => $this->inventory->error()["message"],
                                    "qry" => $this->inventory->last_query()
                                );
                                $this->sys_error_db($error);
                                exit;
                            }
                            $rak_json_after = $qry_rak_json_after->result_array();
                            $almari_json_after['rack'] = $rak_json_after;
                            $sql = "INSERT INTO warehousealmari_log
                                    (WarehouseAlmariLogStatus,
                                    WarehouseAlmariLogWarehouseAlmariId,
                                    WarehouseAlmariLogJSONBefore,
                                    WarehouseAlmariLogJSONAfter,
                                    WarehouseAlmariLogUserId,
                                    WarehouseAlmariLogCreated)
                                    VALUES(
                                    'ADD', ?,NULL,?,?,NOW()
                                    )";
                            $qry = $this->log_inventory->query($sql, array($inserted_almari_id, json_encode($almari_json_after), $userid));

                            if (!$qry) {
                                $error = array(
                                    "message" => $this->log_inventory->error()["message"],
                                    "qry" => $this->log_inventory->last_query()
                                );
                                $this->sys_error_db($error);
                                exit;
                            }
                        }
                        // END ALMARI LOG

                        $sql_json_after = "SELECT * FROM 
                                        warehouse 
                                        WHERE 
                                        WarehouseID = ?";
                        $qry_json_after = $this->inventory->query($sql_json_after, [$insert_id]);

                        $json_after = $qry_json_after->row_array();

                        array_push($staff_log, $qry_json_after->row_array());

                        if (!$qry_json_after) {
                            $error = array(
                                "message" => $this->inventory->error()["message"],
                                "qry" => $this->inventory->last_query()

                            );
                            $this->sys_error_db($error);
                            exit;
                        }

                        $sql_insert_log = "INSERT INTO warehouse_log
                                        (WarehouseLogStatus,
                                        WarehouseLogWarehouseID,
                                        WarehouseLogJSONBefore,
                                        WarehouseLogJSONAfter,
                                        WarehouseLogUserID,
                                        WarehouseLogCreated)
                                        VALUES 
                                        ('ADD',
                                        ?,
                                        NULL,
                                        ?,
                                        ?,
                                        NOW())";
                        $qry_insert_log = $this->log_inventory->query($sql_insert_log, [$insert_id, json_encode($json_after), $userid]);

                        if (!$qry_insert_log) {
                            $error = array(
                                "message" => $this->log_inventory->error()["message"],
                                "qry" => $this->log_inventory->last_query()

                            );
                            $this->sys_error_db($error);
                            exit;
                        }
                    } else {
                        //IS OFFICE = N
                        $sql  = "INSERT INTO warehouse
                            (WarehouseCode,
                            WarehouseName,
                            WarehouseWarehouseLocationID,
                            WarehouseIsActive,
                            WarehouseIsOffice,
                            WarehouseCreated,
                            WarehouseLastUpdated,
                            WarehouseUserID)
                            VALUES 
                            ((select fn_numbering('WH')),
                            ?,
                            ?,
                            'Y',
                            ?,
                            NOW(),
                            NOW(),
                            ?)";
                        $qry = $this->inventory->query($sql, array($wName, $last_id, $isOffice, $userid));
                        $insert_id = $this->inventory->insert_id();
                        if (!$qry) {
                            $this->sys_error_db("save warehouse error", $this->inventory->last_query());
                            exit;
                        }
                        $sql_json_after = "SELECT * FROM 
                                        warehouse 
                                        WHERE 
                                        WarehouseID = ?";
                        $qry_json_after = $this->inventory->query($sql_json_after, [$insert_id]);

                        $json_after = $qry_json_after->row_array();

                        array_push($staff_log, $qry_json_after->row_array());

                        if (!$qry_json_after) {
                            $error = array(
                                "message" => $this->inventory->error()["message"],
                                "qry" => $this->inventory->last_query()

                            );
                            $this->sys_error_db($error);
                            exit;
                        }

                        $sql_insert_log = "INSERT INTO warehouse_log
                                        (WarehouseLogStatus,
                                        WarehouseLogWarehouseID,
                                        WarehouseLogJSONBefore,
                                        WarehouseLogJSONAfter,
                                        WarehouseLogUserID,
                                        WarehouseLogCreated)
                                        VALUES 
                                        ('ADD',
                                        ?,
                                        NULL,
                                        ?,
                                        ?,
                                        NOW())";
                        $qry_insert_log = $this->log_inventory->query($sql_insert_log, [$insert_id, json_encode($json_after), $userid]);

                        if (!$qry_insert_log) {
                            $error = array(
                                "message" => $this->log_inventory->error()["message"],
                                "qry" => $this->log_inventory->last_query()

                            );
                            $this->sys_error_db($error);
                            exit;
                        }
                    }
                }
            }

            $sql_json_after = "SELECT * FROM 
                                warehouselocation
                                WHERE WarehouseLocationID = ?";
            $affected_rows = $this->inventory->affected_rows();
            $qry_json_after = $this->inventory->query($sql_json_after, [$last_id]);
            $json_after = $qry_json_after->row_array();
            if (!$qry_json_after) {
                $error = array(
                    "message" => $this->inventory->error()["message"],
                    "qry" => $this->inventory->last_query()
                );
                $this->sys_error_db($error);
                exit;
            }
            $json_after['warehouse'] = $staff_log;
            $sql_insert_log = "INSERT INTO warehouselocation_log
                                (WarehouseLocationLogStatus,
                                WarehouseLocationLogWarehouseLocationID,
                                WarehouseLocationLogJSONBefore,
                                WarehouseLocationLogJSONAfter,
                                WarehouseLocationLogUserID,
                                WarehouseLocationLogCreated)
                                VALUES 
                                ('ADD',
                                ?,
                                NULL,
                                ?,
                                ?,
                                NOW())";
            $qry_insert_log = $this->log_inventory->query($sql_insert_log, [$last_id, json_encode($json_after), $userid]);
            if (!$qry_insert_log) {
                $error = array(
                    "msg" => "failed insert expedition log",
                    "message" => $this->log_inventory->error()["message"],
                    "qry" => $this->log_inventory->last_query(),
                    "json_after" => $json_after
                );
                $this->sys_error_db($error);
                exit;
            }
            $this->inventory->trans_complete();

            $result = array(
                "affected_rows" => $affected_rows,
                "inserted_id" => $last_id,

            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    function edit()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $prm = $this->sys_input;
            $userid = $this->sys_user['M_UserID'];
            $name = "";
            if (isset($prm['name'])) {
                $name = trim($prm["name"]);
            }
            $id = "";
            if (isset($prm['id'])) {
                $id = trim($prm["id"]);
            }
            $deleted_warehouse = $prm["deleted_warehouse"];
            if ($name == "" || $id == "") {
                $error = array(
                    "message" => "name and id is mandatory",
                );
                $this->sys_error_db($error);
                exit;
            }
            //UPDATE WAREHOUSELOCATION
            $sql_json_before = "SELECT * FROM 
                                warehouselocation
                                WHERE WarehouseLocationID = ?";
            $affected_rows = $this->inventory->affected_rows();
            $qry_json_before = $this->inventory->query($sql_json_before, [$id]);
            if (!$qry_json_before) {
                $error = array(
                    "message" => $this->inventory->error()["message"],
                    "sql" => $this->inventory->last_query()
                );
                $this->sys_error_db($error);
                exit;
            }
            $json_warehouse_before = $qry_json_before->row_array();

            $sql_json_before = "SELECT * FROM 
                                        warehouse 
                                        WHERE WarehouseWarehouseLocationID = ?";
            $qry_json_before = $this->inventory->query($sql_json_before, [$id]);
            if (!$qry_json_before) {
                $error = array(
                    "message" => $this->inventory->error()["message"],
                    "sql" => $this->inventory->last_query()
                );
                $this->sys_error_db($error);
                exit;
            }
            $json_warehouse_before['warehouse'] = $qry_json_before->result_array();

            $this->inventory->trans_start();
            $this->inventory->trans_strict(FALSE);
            $sql = "UPDATE warehouselocation 
                    SET WarehouseLocationName = ?,
                    WarehouseLocationLastUpdated = NOW()
                    WHERE WarehouseLocationID = ?";
            $qry = $this->inventory->query($sql, [$name, $id]);
            if (!$qry) {
                $error = array(
                    "message" => $this->inventory->error()["message"],
                    "sql" => $this->inventory->last_query()
                );
                $this->sys_error_db($error);
                exit;
            }
            //DELETED WAREHOUSE

            if (count($deleted_warehouse) > 0) {
                foreach ($deleted_warehouse as $key => $value) {
                    $sql = "UPDATE warehouse 
                                SET 
                                WarehouseLastUpdated = NOW(),
                                WarehouseIsActive = 'N'
                                WHERE WarehouseID = ? ";
                    $qry = $this->inventory->query($sql, [intval($value)]);
                    $last_qry = $this->inventory->last_query();
                    if (!$qry) {
                        $error = array(
                            "message" => $this->inventory->error()["message"],
                            "sql" => $this->inventory->last_query(),
                            "msg" => "delete warehouse failed"
                        );
                        $this->sys_error_db($error);
                        exit;
                    }
                    $sql_after = "SELECT * FROM 
                                            warehouse 
                                            where WarehouseID = ?";
                    $qry_after = $this->inventory->query($sql_after, [intval($value)]);
                    if (!$qry_after) {
                        $error = array(
                            "message" => $this->inventory->error()["message"],
                            "sql" => $this->inventory->last_query()
                        );
                        $this->sys_error_db($error);
                        exit;
                    }
                    $json_after = json_encode($qry_after->row_array());
                    $sql_insert_log = "INSERT INTO warehouse_log
                                        (WarehouseLogStatus,
                                        WarehouseLogWarehouseID,
                                        WarehouseLogJSONBefore,
                                        WarehouseLogJSONAfter,
                                        WarehouseLogUserID,
                                        WarehouseLogCreated)
                                        VALUES 
                                        ('DELETE',
                                        ?,
                                        NULL,
                                        ?,
                                        ?,
                                        NOW())";
                    $qry_insert_log = $this->log_inventory->query($sql_insert_log, [intval($value), $json_after, $userid]);

                    if (!$qry_insert_log) {
                        $error = array(
                            "message" => $this->log_inventory->error()["message"],
                            "qry" => $this->log_inventory->last_query()

                        );
                        $this->sys_error_db($error);
                        exit;
                    }

                    $sql = "SELECT * FROM warehousealmari 
                            WHERE WarehouseAlmariWarehouseID = ? 
                            AND WarehouseAlmariIsActive = 'Y' ";
                    $qry = $this->inventory->query($sql, [intval($value)]);
                    if (!$qry) {
                        $error = array(
                            "message" => $this->inventory->error()["message"],
                            "sql" => $this->inventory->last_query()
                        );
                        $this->sys_error_db($error);
                        exit;
                    }
                    $almari = $qry->result_array();
                    if (count($almari) > 0) {
                        foreach ($almari as $key => $val) {
                            $sql = "UPDATE warehousealmari
                                    SET WarehouseAlmariIsActive = 'N',
                                    WarehouseAlmariLastUpdated =NOW()
                                    WHERE WarehouseAlmariID = ?";
                            $qry = $this->inventory->query($sql, array($val['WarehouseAlmariID']));
                            if (!$qry) {
                                $error = array(
                                    "message" => $this->inventory->error()["message"],
                                    "qry" => $this->inventory->last_query()
                                );
                                $this->sys_error_db($error);
                                exit;
                            }
                            $sql = "UPDATE warehouserack
                            SET WarehouseRackIsActive = 'N',
                            WarehouseRackLastUpdated =NOW()
                            WHERE WarehouseRackWarehouseAlmariID = ?";
                            $qry = $this->inventory->query($sql, array($val['WarehouseAlmariID']));
                            if (!$qry) {
                                $error = array(
                                    "message" => $this->inventory->error()["message"],
                                    "qry" => $this->inventory->last_query()
                                );
                                $this->sys_error_db($error);
                                exit;
                            }
                            $sql_rak_json_after = "SELECT * FROM warehouserack  WHERE WarehouseRackWarehouseAlmariID= ?";
                            $qry_rak_json_after = $this->inventory->query($sql_rak_json_after, array($val['WarehouseAlmariID']));
                            if (!$qry_rak_json_after) {
                                $error = array(
                                    "message" => $this->inventory->error()["message"],
                                    "qry" => $this->inventory->last_query()
                                );
                                $this->sys_error_db($error);
                                exit;
                            }
                            $rak_json_after = $qry_rak_json_after->result_array();

                            $sql_rak_log = "INSERT INTO warehouserack_log
                                    (WarehouseRackLogStatus,
                                    WarehouseRackLogWarehouseRackId,
                                    WarehouseRackLogJSONBefore,
                                    WarehouseRackLogJSONAfter,
                                    WarehouseRackLogUserId,
                                    WarehouseRackLogCreated)
                                    VALUES(
                                    'DELETE', ?,NULL,?,?, NOW()
                                    )";
                            $qry_rak_log = $this->log_inventory->query($sql_rak_log, array(
                                $val['WarehouseAlmariID'],
                                json_encode($rak_json_after),
                                $userid
                            ));
                            if (!$qry_rak_log) {
                                $error = array(
                                    "message" => $this->log_inventory->error()["message"],
                                    "qry" => $this->log_inventory->last_query()
                                );
                                $this->sys_error_db($error);
                                exit;
                            }
                            $sql_almari_json_after = "SELECT * FROM warehousealmari WHERE WarehouseAlmariID= ?";
                            $qry_almari_json_after = $this->inventory->query($sql_almari_json_after, array($val['WarehouseAlmariID']));
                            if (!$qry_almari_json_after) {
                                $error = array(
                                    "message" => $this->inventory->error()["message"],
                                    "qry" => $this->inventory->last_query()
                                );
                                $this->sys_error_db($error);
                                exit;
                            }
                            $almari_json_after = $qry_almari_json_after->row_array();
                            $almari_json_after['rack'] = $rak_json_after;
                            $sql_almari_log = "INSERT INTO warehousealmari_log
                                (WarehouseAlmariLogStatus,
                                WarehouseAlmariLogWarehouseAlmariId,
                                WarehouseAlmariLogJSONBefore,
                                WarehouseAlmariLogJSONAfter,
                                WarehouseAlmariLogUserId,
                                WarehouseAlmariLogCreated)
                                VALUES(
                                'DELETE', ?,NULL,?,?,NOW()
                                )";
                            $qry_almari_log = $this->log_inventory->query($sql_almari_log, array(
                                $val['WarehouseAlmariID'],
                                json_encode($almari_json_after),
                                $userid
                            ));
                            if (!$qry_almari_log) {
                                $error = array(
                                    "message" => $this->log_inventory->error()["message"],
                                    "qry" => $this->log_inventory->last_query()
                                );
                                $this->sys_error_db($error);
                                exit;
                            }
                        }
                    }
                }
            }
            //END UPDATE WAREHOUSELOCATION

            // CEK WAREHOUSE 
            if (count($prm['warehouse']) > 0) {
                $sql =  "UPDATE warehouse SET 
                    WarehouseIsActive = 'N'
                    WHERE WarehouseWarehouseLocationID = ?";
                $qry = $this->inventory->query($sql, [$id]);
                if (!$qry) {
                    $error = array(
                        "message" => $this->inventory->error()["message"],
                        "sql" => $this->inventory->last_query()
                    );
                    $this->sys_error_db($error);
                    exit;
                }
                //UPDATE WAREHOUSE 
                foreach ($prm['warehouse'] as $key => $value) {
                    $wName = trim($value['wName']);
                    $wId = trim($value['wId']);
                    $isOffice = trim($value['isOffice']);
                    $almari = trim($value['almari']);
                    //apabila id lebih dari 0 atau data sudah ada 
                    if (intval($wId) > 0) {
                        $sql_before = "SELECT * FROM 
                                            warehouse 
                                            where WarehouseID = ?";

                        $qry_before = $this->inventory->query($sql_before, [$wId]);
                        if (!$qry_before) {
                            $error = array(
                                "message" => $this->inventory->error()["message"],
                                "sql" => $this->inventory->last_query()
                            );
                            $this->sys_error_db($error);
                            exit;
                        }
                        $json_before = json_encode($qry_before->row_array());
                        // OFFICE Y && Almari == 0 
                        if ($isOffice == 'Y' && intval($almari) == 0) {
                            $sql = "UPDATE warehouse 
                                SET WarehouseName  = ?,
                                WarehouseIsOffice = ?,
                                WarehouseLastUpdated = NOW(),
                                WarehouseIsActive = 'Y'
                                WHERE WarehouseID = ? ";
                            $qry = $this->inventory->query($sql, [$wName, $isOffice, $wId]);
                            $last_qry = $this->inventory->last_query();
                            if (!$qry) {
                                $error = array(
                                    "message" => $this->inventory->error()["message"],
                                    "sql" => $this->inventory->last_query()
                                );
                                $this->sys_error_db($error);
                                exit;
                            }
                            $sql_after = "SELECT * FROM 
                                            warehouse 
                                            where WarehouseID = ?";
                            $qry_after = $this->inventory->query($sql_after, [$wId]);
                            if (!$qry_after) {
                                $error = array(
                                    "message" => $this->inventory->error()["message"],
                                    "sql" => $this->inventory->last_query()
                                );
                                $this->sys_error_db($error);
                                exit;
                            }
                            $json_after = json_encode($qry_after->row_array());
                            $sql_insert_log = "INSERT INTO warehouse_log
                                        (WarehouseLogStatus,
                                        WarehouseLogWarehouseID,
                                        WarehouseLogJSONBefore,
                                        WarehouseLogJSONAfter,
                                        WarehouseLogUserID,
                                        WarehouseLogCreated)
                                        VALUES 
                                        ('EDIT',
                                        ?,
                                        ?,
                                        ?,
                                        ?,
                                        NOW())";
                            $qry_insert_log = $this->log_inventory->query($sql_insert_log, [$wId, $json_before, $json_after, $userid]);

                            if (!$qry_insert_log) {
                                $error = array(
                                    "message" => $this->log_inventory->error()["message"],
                                    "qry" => $this->log_inventory->last_query()

                                );
                                $this->sys_error_db($error);
                                exit;
                            }

                            // INSERT ALMARI

                            $sql = "INSERT INTO warehousealmari(
                                WarehouseAlmariLocationID,
                                WarehouseAlmariWarehouseID,
                                WarehouseAlmariCode,
                                WarehouseAlmariName,
                                WarehouseAlmariRows,
                                WarehouseAlmariCols,
                                WarehouseAlmariIsActive,
                                WarehouseAlmariUserID,
                                WarehouseAlmariCreated,
                                WarehouseAlmariLastUpdated)
                                VALUES(
                                ?,?,(select fn_numbering('AL')),?, ?,?,'Y',?,NOW(),NOW())";
                            $qry = $this->inventory->query($sql, array(
                                $id, $wId, "Almari Office " . $wName, 1, 1, $userid
                            ));
                            if ($qry) {
                                $inserted_almari_id = $this->inventory->insert_id();

                                $sql = "SELECT * FROM warehousealmari WHERE WarehouseAlmariID = ?";
                                $qry = $this->inventory->query($sql, array($inserted_almari_id));
                                $almari = $qry->row_array();
                                $almariCode = $almari['WarehouseAlmariCode'];
                                if (!$qry) {
                                    $error = array(
                                        "message" => $this->inventory->error()["message"],
                                        "qry" => $this->inventory->last_query()
                                    );
                                    $this->sys_error_db($error);
                                    exit;
                                }
                                //INSERT RAK
                                $name = "Rak Office 1 x 1";
                                $row = 1;
                                $col = 1;
                                $code = $almariCode . $row . $col;
                                $sql = "INSERT INTO warehouserack(
                                        WarehouseRackWarehouseAlmariID,
                                        WarehouseRackCode,
                                        WarehouseRackName,
                                        WarehouseRackRow,
                                        WarehouseRackCol,
                                        WarehouseRackIsActive,
                                        WarehouseRackUserID,
                                        WarehouseRackCreated,
                                        WarehouseRackLastUpdated)
                                        VALUES (
                                        ?, ?, ?, ?,?,'Y', ?,NOW(),NOW()
                                        )";
                                $qry = $this->inventory->query($sql, array($inserted_almari_id, $code, $name,  $row, $col, $userid));
                                if (!$qry) {
                                    $error = array(
                                        "message" => $this->inventory->error()["message"],
                                        "qry" => $this->inventory->last_query()
                                    );
                                    $this->sys_error_db($error);
                                    exit;
                                } else {
                                    $rak_inserted_id = $this->inventory->insert_id();
                                    $sql = "SELECT * FROM warehouserack WHERE WarehouseRackID = ?";
                                    $qry = $this->inventory->query($sql, array($rak_inserted_id));

                                    if (!$qry) {
                                        $error = array(
                                            "message" => $this->inventory->error()["message"],
                                            "qry" => $this->inventory->last_query()
                                        );
                                        $this->sys_error_db($error);
                                        exit;
                                    } else {
                                        $rak_json_after  = $qry->row_array();

                                        $sql = "INSERT INTO warehouserack_log
                                    (WarehouseRackLogStatus,
                                    WarehouseRackLogWarehouseRackId,
                                    WarehouseRackLogJSONBefore,
                                    WarehouseRackLogJSONAfter,
                                    WarehouseRackLogUserId,
                                    WarehouseRackLogCreated)
                                    VALUES(
                                    'ADD', ?,NULL,?,?, NOW()
                                    )";
                                        $qry = $this->log_inventory->query($sql, array($rak_inserted_id, json_encode($rak_json_after), $userid));

                                        if (!$qry) {
                                            $error = array(
                                                "message" => $this->log_inventory->error()["message"],
                                                "qry" => $this->log_inventory->last_query()
                                            );
                                            $this->sys_error_db($error);
                                            exit;
                                        }
                                    }
                                }
                                // END INSERT RAK
                            }
                            // END INSERT ALMARI

                            // ALMARI LOG
                            $sql = "SELECT * FROM warehousealmari WHERE WarehouseAlmariID= ?";
                            $qry = $this->inventory->query($sql, array($inserted_almari_id));

                            if (!$qry) {
                                $error = array(
                                    "message" => $this->inventory->error()["message"],
                                    "qry" => $this->inventory->last_query()
                                );
                                $this->sys_error_db($error);
                                exit;
                            } else {
                                $almari_json_after = $qry->row_array();
                                $sql_rak_json_after = "SELECT * FROM warehouserack  WHERE WarehouseRackWarehouseAlmariID= ?";
                                $qry_rak_json_after = $this->inventory->query($sql_rak_json_after, array($wId));
                                if (!$qry_rak_json_after) {
                                    $error = array(
                                        "message" => $this->inventory->error()["message"],
                                        "qry" => $this->inventory->last_query()
                                    );
                                    $this->sys_error_db($error);
                                    exit;
                                }
                                $rak_json_after = $qry_rak_json_after->result_array();
                                $almari_json_after['rack'] = $rak_json_after;
                                $sql = "INSERT INTO warehousealmari_log
                                    (WarehouseAlmariLogStatus,
                                    WarehouseAlmariLogWarehouseAlmariId,
                                    WarehouseAlmariLogJSONBefore,
                                    WarehouseAlmariLogJSONAfter,
                                    WarehouseAlmariLogUserId,
                                    WarehouseAlmariLogCreated)
                                    VALUES(
                                    'ADD', ?,NULL,?,?,NOW()
                                    )";
                                $qry = $this->log_inventory->query($sql, array($inserted_almari_id, json_encode($almari_json_after), $userid));

                                if (!$qry) {
                                    $error = array(
                                        "message" => $this->log_inventory->error()["message"],
                                        "qry" => $this->log_inventory->last_query()
                                    );
                                    $this->sys_error_db($error);
                                    exit;
                                }
                            }
                            // END ALMARI LOG
                        } else {
                            $sql = "UPDATE warehouse 
                                SET WarehouseName  = ?,
                                WarehouseIsOffice = ?,
                                WarehouseLastUpdated = NOW(),
                                WarehouseIsActive = 'Y'
                                WHERE WarehouseID = ? ";
                            $qry = $this->inventory->query($sql, [$wName, $isOffice,  $wId]);
                            $last_qry = $this->inventory->last_query();
                            if (!$qry) {
                                $error = array(
                                    "message" => $this->inventory->error()["message"],
                                    "sql" => $this->inventory->last_query()
                                );
                                $this->sys_error_db($error);
                                exit;
                            }
                            $sql_after = "SELECT * FROM 
                                            warehouse 
                                            where WarehouseID = ?";
                            $qry_after = $this->inventory->query($sql_after, [$wId]);
                            if (!$qry_after) {
                                $error = array(
                                    "message" => $this->inventory->error()["message"],
                                    "sql" => $this->inventory->last_query()
                                );
                                $this->sys_error_db($error);
                                exit;
                            }
                            $json_after = json_encode($qry_after->row_array());
                            $sql_insert_log = "INSERT INTO warehouse_log
                                        (WarehouseLogStatus,
                                        WarehouseLogWarehouseID,
                                        WarehouseLogJSONBefore,
                                        WarehouseLogJSONAfter,
                                        WarehouseLogUserID,
                                        WarehouseLogCreated)
                                        VALUES 
                                        ('EDIT',
                                        ?,
                                        ?,
                                        ?,
                                        ?,
                                        NOW())";
                            $qry_insert_log = $this->log_inventory->query($sql_insert_log, [$wId, $json_before, $json_after, $userid]);

                            if (!$qry_insert_log) {
                                $error = array(
                                    "message" => $this->log_inventory->error()["message"],
                                    "qry" => $this->log_inventory->last_query()

                                );
                                $this->sys_error_db($error);
                                exit;
                            }
                        }

                        //END UPDATE WAREHOUSE
                    } else {
                        //apabila id kurang dari 1 atau merupakan data tambahan baru 
                        if ($isOffice == 'Y') {
                            $sql  = "INSERT INTO warehouse
                            (WarehouseCode,
                            WarehouseName,
                            WarehouseWarehouseLocationID,
                            WarehouseIsActive,
                            WarehouseIsOffice,
                            WarehouseCreated,
                            WarehouseLastUpdated,
                            WarehouseUserID)
                            VALUES 
                            ((select fn_numbering('WH')),
                            ?,
                            ?,
                            'Y',
                            ?,
                            NOW(),
                            NOW(),
                            ?)";
                            $qry = $this->inventory->query($sql, array($wName, $id,  $isOffice, $userid));
                            $insert_id = $this->inventory->insert_id();
                            if (!$qry) {
                                $this->sys_error_db("save warehouse error", $this->inventory->last_query());
                                exit;
                            }
                            $sql_json_after = "SELECT * FROM 
                                        warehouse 
                                        WHERE 
                                        WarehouseID = ?";
                            $qry_json_after = $this->inventory->query($sql_json_after, [$insert_id]);

                            $json_after = $qry_json_after->row_array();

                            array_push($staff_log, $qry_json_after->row_array());

                            if (!$qry_json_after) {
                                $error = array(
                                    "message" => $this->inventory->error()["message"],
                                    "qry" => $this->inventory->last_query()

                                );
                                $this->sys_error_db($error);
                                exit;
                            }

                            // INSERT ALMARI

                            $sql = "INSERT INTO warehousealmari(
                                WarehouseAlmariLocationID,
                                WarehouseAlmariWarehouseID,
                                WarehouseAlmariCode,
                                WarehouseAlmariName,
                                WarehouseAlmariRows,
                                WarehouseAlmariCols,
                                WarehouseAlmariIsActive,
                                WarehouseAlmariUserID,
                                WarehouseAlmariCreated,
                                WarehouseAlmariLastUpdated)
                                VALUES(
                                ?,?,(select fn_numbering('AL')),?, ?,?,'Y',?,NOW(),NOW())";
                            $qry = $this->inventory->query($sql, array(
                                $id, $insert_id, "Almari Office " . $wName, 1, 1, $userid
                            ));
                            if ($qry) {
                                $inserted_almari_id = $this->inventory->insert_id();

                                $sql = "SELECT * FROM warehousealmari WHERE WarehouseAlmariID = ?";
                                $qry = $this->inventory->query($sql, array($inserted_almari_id));
                                $almari = $qry->row_array();
                                $almariCode = $almari['WarehouseAlmariCode'];
                                if (!$qry) {
                                    $error = array(
                                        "message" => $this->inventory->error()["message"],
                                        "qry" => $this->inventory->last_query()
                                    );
                                    $this->sys_error_db($error);
                                    exit;
                                }
                                //INSERT RAK
                                $name = "Rak Office 1 x 1";
                                $row = 1;
                                $col = 1;
                                $code = $almariCode . $row . $col;
                                $sql = "INSERT INTO warehouserack(
                                        WarehouseRackWarehouseAlmariID,
                                        WarehouseRackCode,
                                        WarehouseRackName,
                                        WarehouseRackRow,
                                        WarehouseRackCol,
                                        WarehouseRackIsActive,
                                        WarehouseRackUserID,
                                        WarehouseRackCreated,
                                        WarehouseRackLastUpdated)
                                        VALUES (
                                        ?, ?, ?, ?,?,'Y', ?,NOW(),NOW()
                                        )";
                                $qry = $this->inventory->query($sql, array($inserted_almari_id, $code, $name,  $row, $col, $userid));
                                if (!$qry) {
                                    $error = array(
                                        "message" => $this->inventory->error()["message"],
                                        "qry" => $this->inventory->last_query()
                                    );
                                    $this->sys_error_db($error);
                                    exit;
                                } else {
                                    $rak_inserted_id = $this->inventory->insert_id();
                                    $sql = "SELECT * FROM warehouserack WHERE WarehouseRackID = ?";
                                    $qry = $this->inventory->query($sql, array($rak_inserted_id));

                                    if (!$qry) {
                                        $error = array(
                                            "message" => $this->inventory->error()["message"],
                                            "qry" => $this->inventory->last_query()
                                        );
                                        $this->sys_error_db($error);
                                        exit;
                                    } else {
                                        $rak_json_after  = $qry->row_array();

                                        $sql = "INSERT INTO warehouserack_log
                                    (WarehouseRackLogStatus,
                                    WarehouseRackLogWarehouseRackId,
                                    WarehouseRackLogJSONBefore,
                                    WarehouseRackLogJSONAfter,
                                    WarehouseRackLogUserId,
                                    WarehouseRackLogCreated)
                                    VALUES(
                                    'ADD', ?,NULL,?,?, NOW()
                                    )";
                                        $qry = $this->log_inventory->query($sql, array($rak_inserted_id, json_encode($rak_json_after), $userid));

                                        if (!$qry) {
                                            $error = array(
                                                "message" => $this->log_inventory->error()["message"],
                                                "qry" => $this->log_inventory->last_query()
                                            );
                                            $this->sys_error_db($error);
                                            exit;
                                        }
                                    }
                                }
                                // END INSERT RAK
                            }
                            // END INSERT ALMARI

                            // ALMARI LOG
                            $sql = "SELECT * FROM warehousealmari WHERE WarehouseAlmariID= ?";
                            $qry = $this->inventory->query($sql, array($inserted_almari_id));

                            if (!$qry) {
                                $error = array(
                                    "message" => $this->inventory->error()["message"],
                                    "qry" => $this->inventory->last_query()
                                );
                                $this->sys_error_db($error);
                                exit;
                            } else {
                                $almari_json_after = $qry->row_array();
                                $sql_rak_json_after = "SELECT * FROM warehouserack  WHERE WarehouseRackWarehouseAlmariID= ?";
                                $qry_rak_json_after = $this->inventory->query($sql_rak_json_after, array($id));
                                if (!$qry_rak_json_after) {
                                    $error = array(
                                        "message" => $this->inventory->error()["message"],
                                        "qry" => $this->inventory->last_query()
                                    );
                                    $this->sys_error_db($error);
                                    exit;
                                }
                                $rak_json_after = $qry_rak_json_after->result_array();
                                $almari_json_after['rack'] = $rak_json_after;
                                $sql = "INSERT INTO warehousealmari_log
                                    (WarehouseAlmariLogStatus,
                                    WarehouseAlmariLogWarehouseAlmariId,
                                    WarehouseAlmariLogJSONBefore,
                                    WarehouseAlmariLogJSONAfter,
                                    WarehouseAlmariLogUserId,
                                    WarehouseAlmariLogCreated)
                                    VALUES(
                                    'ADD', ?,NULL,?,?,NOW()
                                    )";
                                $qry = $this->log_inventory->query($sql, array($inserted_almari_id, json_encode($almari_json_after), $userid));

                                if (!$qry) {
                                    $error = array(
                                        "message" => $this->log_inventory->error()["message"],
                                        "qry" => $this->log_inventory->last_query()
                                    );
                                    $this->sys_error_db($error);
                                    exit;
                                }
                            }
                            // END ALMARI LOG

                            $sql_insert_log = "INSERT INTO warehouse_log
                                        (WarehouseLogStatus,
                                        WarehouseLogWarehouseID,
                                        WarehouseLogJSONBefore,
                                        WarehouseLogJSONAfter,
                                        WarehouseLogUserID,
                                        WarehouseLogCreated)
                                        VALUES 
                                        ('ADD',
                                        ?,
                                        NULL,
                                        ?,
                                        ?,
                                        NOW())";
                            $qry_insert_log = $this->log_inventory->query($sql_insert_log, [$insert_id, json_encode($json_after), $userid]);

                            if (!$qry_insert_log) {
                                $error = array(
                                    "message" => $this->log_inventory->error()["message"],
                                    "qry" => $this->log_inventory->last_query()

                                );
                                $this->sys_error_db($error);
                                exit;
                            }
                        } else {
                            $sql  = "INSERT INTO warehouse
                            (WarehouseCode,
                            WarehouseName,
                            WarehouseWarehouseLocationID,
                            WarehouseIsActive,
                            WarehouseIsOffice,
                            WarehouseCreated,
                            WarehouseLastUpdated,
                            WarehouseUserID)
                            VALUES 
                            ((select fn_numbering('WH')),
                            ?,
                            ?,
                            'Y',
                            ?,
                            NOW(),
                            NOW(),
                            ?)";
                            $qry = $this->inventory->query($sql, array($wName, $id, $isOffice, $userid));
                            $insert_id = $this->inventory->insert_id();
                            if (!$qry) {
                                $this->sys_error_db("save warehouse error", $this->inventory->last_query());
                                exit;
                            }
                            $sql_json_after = "SELECT * FROM 
                                        warehouse 
                                        WHERE 
                                        WarehouseID = ?";
                            $qry_json_after = $this->inventory->query($sql_json_after, [$insert_id]);

                            $json_after = $qry_json_after->row_array();

                            array_push($staff_log, $qry_json_after->row_array());

                            if (!$qry_json_after) {
                                $error = array(
                                    "message" => $this->inventory->error()["message"],
                                    "qry" => $this->inventory->last_query()

                                );
                                $this->sys_error_db($error);
                                exit;
                            }

                            $sql_insert_log = "INSERT INTO warehouse_log
                                        (WarehouseLogStatus,
                                        WarehouseLogWarehouseID,
                                        WarehouseLogJSONBefore,
                                        WarehouseLogJSONAfter,
                                        WarehouseLogUserID,
                                        WarehouseLogCreated)
                                        VALUES 
                                        ('ADD',
                                        ?,
                                        NULL,
                                        ?,
                                        ?,
                                        NOW())";
                            $qry_insert_log = $this->log_inventory->query($sql_insert_log, [$insert_id, json_encode($json_after), $userid]);

                            if (!$qry_insert_log) {
                                $error = array(
                                    "message" => $this->log_inventory->error()["message"],
                                    "qry" => $this->log_inventory->last_query()

                                );
                                $this->sys_error_db($error);
                                exit;
                            }
                        }
                    }
                }
            } else {
                $error = array(
                    "message" => "warehouse is mandatory",
                );
                $this->sys_error_db($error);
                exit;
            }
            $this->inventory->trans_complete();
            $sql_json_after = "SELECT * FROM 
                                warehouselocation
                                WHERE WarehouseLocationID = ?";
            $affected_rows = $this->inventory->affected_rows();
            $qry_json_after = $this->inventory->query($sql_json_after, [$id]);
            if (!$qry_json_after) {
                $error = array(
                    "message" => $this->inventory->error()["message"],
                    "sql" => $this->inventory->last_query()
                );
                $this->sys_error_db($error);
                exit;
            }
            $json_warehouse_after = $qry_json_after->row_array();

            $sql_json_after = "SELECT * FROM 
                                        warehouse 
                                        WHERE WarehouseWarehouseLocationID = ?";
            $qry_json_after = $this->inventory->query($sql_json_after, [$id]);
            if (!$qry_json_after) {
                $error = array(
                    "message" => $this->inventory->error()["message"],
                    "sql" => $this->inventory->last_query()
                );
                $this->sys_error_db($error);
                exit;
            }
            $json_warehouse_after['warehouse'] = $qry_json_after->result_array();
            $sql_insert_log = "INSERT INTO warehouselocation_log
                                (WarehouseLocationLogStatus,
                                WarehouseLocationLogWarehouseLocationID,
                                WarehouseLocationLogJSONBefore,
                                WarehouseLocationLogJSONAfter,
                                WarehouseLocationLogUserID,
                                WarehouseLocationLogCreated)
                                VALUES 
                                ('EDIT',
                                ?,
                                ?,
                                ?,
                                ?,
                                NOW())";
            $qry_insert_log = $this->log_inventory->query($sql_insert_log, [$id, json_encode($json_warehouse_before), json_encode($json_warehouse_after), $userid]);
            if (!$qry_insert_log) {
                $error = array(
                    "msg" => "failed insert expedition log",
                    "message" => $this->log_inventory->error()["message"],
                    "qry" => $this->log_inventory->last_query(),
                    "json_after" => $json_after
                );
                $this->sys_error_db($error);
                exit;
            }
            $this->inventory->trans_complete();

            $result = array(
                "affected_rows" => $affected_rows,
                "inserted_id" => $id,
                "json_before" => $json_warehouse_before,
                "json_after" => $json_warehouse_after

            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    function delete()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $prm = $this->sys_input;
            $userid = $this->sys_user['M_UserID'];
            $id = "";
            if (isset($prm['id'])) {
                $id = trim($prm["id"]);
            }
            $warehouse = [];
            if (isset($prm['warehouse'])) {
                $warehouse = $prm['warehouse'];
            }
            if ($id == "" || !$id) {
                $error = array(
                    "message" => "id is mandatory",
                );
                $this->sys_error_db($error);
                exit;
            }

            $this->inventory->trans_start();
            $this->inventory->trans_strict(FALSE);
            $sql = "UPDATE warehouselocation 
                    SET WarehouseLocationIsActive = 'N',
                    WarehouseLocationLastUpdated = NOW(),
                    WarehouseLocationUserID = ?
                    WHERE WarehouseLocationID = ?";
            $qry = $this->inventory->query($sql, [$userid, $id]);
            if (!$qry) {
                $error = array(
                    "message" => $this->inventory->error()["message"],
                    "sql" => $this->inventory->last_query()
                );
                $this->sys_error_db($error);
                exit;
            } else {
                if (count($warehouse) > 0) {
                    foreach ($warehouse as $key => $value) {
                        $sql = "UPDATE warehouse 
                                SET 
                                WarehouseLastUpdated = NOW(),
                                WarehouseIsActive = 'N'
                                WHERE WarehouseID = ? ";
                        $qry = $this->inventory->query($sql, [intval($value['wId'])]);
                        $last_qry = $this->inventory->last_query();
                        if (!$qry) {
                            $error = array(
                                "message" => $this->inventory->error()["message"],
                                "sql" => $this->inventory->last_query(),
                                "msg" => "delete warehouse failed"
                            );
                            $this->sys_error_db($error);
                            exit;
                        }
                        $sql_after = "SELECT * FROM 
                                            warehouse 
                                            where WarehouseID = ?";
                        $qry_after = $this->inventory->query($sql_after, [intval($value['wId'])]);
                        if (!$qry_after) {
                            $error = array(
                                "message" => $this->inventory->error()["message"],
                                "sql" => $this->inventory->last_query()
                            );
                            $this->sys_error_db($error);
                            exit;
                        }
                        $json_after = json_encode($qry_after->row_array());
                        $sql_insert_log = "INSERT INTO warehouse_log
                                        (WarehouseLogStatus,
                                        WarehouseLogWarehouseID,
                                        WarehouseLogJSONBefore,
                                        WarehouseLogJSONAfter,
                                        WarehouseLogUserID,
                                        WarehouseLogCreated)
                                        VALUES 
                                        ('DELETE',
                                        ?,
                                        NULL,
                                        ?,
                                        ?,
                                        NOW())";
                        $qry_insert_log = $this->log_inventory->query($sql_insert_log, [intval($value['wId']), $json_after, $userid]);

                        if (!$qry_insert_log) {
                            $error = array(
                                "message" => $this->log_inventory->error()["message"],
                                "qry" => $this->log_inventory->last_query()

                            );
                            $this->sys_error_db($error);
                            exit;
                        }

                        $sql = "SELECT * FROM warehousealmari 
                            WHERE WarehouseAlmariWarehouseID = ? 
                            AND WarehouseAlmariIsActive = 'Y' ";
                        $qry = $this->inventory->query($sql, [intval($value['wId'])]);
                        if (!$qry) {
                            $error = array(
                                "message" => $this->inventory->error()["message"],
                                "sql" => $this->inventory->last_query()
                            );
                            $this->sys_error_db($error);
                            exit;
                        }
                        $almari = $qry->result_array();
                        if (count($almari) > 0) {
                            foreach ($almari as $key => $val) {
                                $sql = "UPDATE warehousealmari
                                    SET WarehouseAlmariIsActive = 'N',
                                    WarehouseAlmariLastUpdated =NOW()
                                    WHERE WarehouseAlmariID = ?";
                                $qry = $this->inventory->query($sql, array($val['WarehouseAlmariID']));
                                if (!$qry) {
                                    $error = array(
                                        "message" => $this->inventory->error()["message"],
                                        "qry" => $this->inventory->last_query()
                                    );
                                    $this->sys_error_db($error);
                                    exit;
                                }
                                $sql = "UPDATE warehouserack
                                        SET WarehouseRackIsActive = 'N',
                                        WarehouseRackLastUpdated =NOW()
                                        WHERE WarehouseRackWarehouseAlmariID = ?";
                                $qry = $this->inventory->query($sql, array($val['WarehouseAlmariID']));
                                if (!$qry) {
                                    $error = array(
                                        "message" => $this->inventory->error()["message"],
                                        "qry" => $this->inventory->last_query()
                                    );
                                    $this->sys_error_db($error);
                                    exit;
                                }
                                $sql_rak_json_after = "SELECT * FROM warehouserack  WHERE WarehouseRackWarehouseAlmariID= ?";
                                $qry_rak_json_after = $this->inventory->query($sql_rak_json_after, array($val['WarehouseAlmariID']));
                                if (!$qry_rak_json_after) {
                                    $error = array(
                                        "message" => $this->inventory->error()["message"],
                                        "qry" => $this->inventory->last_query()
                                    );
                                    $this->sys_error_db($error);
                                    exit;
                                }
                                $rak_json_after = $qry_rak_json_after->result_array();

                                $sql_rak_log = "INSERT INTO warehouserack_log
                                    (WarehouseRackLogStatus,
                                    WarehouseRackLogWarehouseRackId,
                                    WarehouseRackLogJSONBefore,
                                    WarehouseRackLogJSONAfter,
                                    WarehouseRackLogUserId,
                                    WarehouseRackLogCreated)
                                    VALUES(
                                    'DELETE', ?,NULL,?,?, NOW()
                                    )";
                                $qry_rak_log = $this->log_inventory->query($sql_rak_log, array(
                                    $val['WarehouseAlmariID'],
                                    json_encode($rak_json_after),
                                    $userid
                                ));
                                if (!$qry_rak_log) {
                                    $error = array(
                                        "message" => $this->log_inventory->error()["message"],
                                        "qry" => $this->log_inventory->last_query()
                                    );
                                    $this->sys_error_db($error);
                                    exit;
                                }
                                $sql_almari_json_after = "SELECT * FROM warehousealmari WHERE WarehouseAlmariID= ?";
                                $qry_almari_json_after = $this->inventory->query($sql_almari_json_after, array($val['WarehouseAlmariID']));
                                if (!$qry_almari_json_after) {
                                    $error = array(
                                        "message" => $this->inventory->error()["message"],
                                        "qry" => $this->inventory->last_query()
                                    );
                                    $this->sys_error_db($error);
                                    exit;
                                }
                                $almari_json_after = $qry_almari_json_after->row_array();
                                $almari_json_after['rack'] = $rak_json_after;
                                $sql_almari_log = "INSERT INTO warehousealmari_log
                                (WarehouseAlmariLogStatus,
                                WarehouseAlmariLogWarehouseAlmariId,
                                WarehouseAlmariLogJSONBefore,
                                WarehouseAlmariLogJSONAfter,
                                WarehouseAlmariLogUserId,
                                WarehouseAlmariLogCreated)
                                VALUES(
                                'DELETE', ?,NULL,?,?,NOW()
                                )";
                                $qry_almari_log = $this->log_inventory->query($sql_almari_log, array(
                                    $val['WarehouseAlmariID'],
                                    json_encode($almari_json_after),
                                    $userid
                                ));
                                if (!$qry_almari_log) {
                                    $error = array(
                                        "message" => $this->log_inventory->error()["message"],
                                        "qry" => $this->log_inventory->last_query()
                                    );
                                    $this->sys_error_db($error);
                                    exit;
                                }
                            }
                        }
                    }
                }
            }


            $this->inventory->trans_complete();
            $sql_json_after = "SELECT * FROM 
                                warehouselocation
                                WHERE WarehouseLocationID = ?";
            $qry_json_after = $this->inventory->query($sql_json_after, [$id]);
            if (!$qry_json_after) {
                $error = array(
                    "message" => $this->inventory->error()["message"],
                    "sql" => $this->inventory->last_query()

                );
                $this->sys_error_db($error);
                exit;
            }
            $json_after = $qry_json_after->row_array();
            $sql_json_after = "SELECT * FROM 
                                        warehouse 
                                        WHERE WarehouseWarehouseLocationID = ?";
            $qry_json_after = $this->inventory->query($sql_json_after, [$id]);
            $sql_insert_warehouse_log = "INSERT INTO warehouse_log
                                        (WarehouseLogStatus,
                                        WarehouseLogWarehouseID,
                                        WarehouseLogJSONBefore,
                                        WarehouseLogJSONAfter,
                                        WarehouseLogUserID,
                                        WarehouseLogCreated)
                                        VALUES 
                                        ('DELETE',
                                        ?,
                                        NULL,
                                        ?,
                                        ?,
                                        NOW())";
            $qry_insert_warehouse_log = $this->log_inventory->query($sql_insert_warehouse_log, [$id, json_encode($qry_json_after->result_array()), $userid]);

            if (!$qry_insert_warehouse_log) {
                $error = array(
                    "message" => $this->log_inventory->error()["message"],
                    "qry" => $this->log_inventory->last_query()

                );
                $this->sys_error_db($error);
                exit;
            }
            $json_after['warehouse'] = $qry_json_after->result_array();
            if (!$qry_json_after) {
                $error = array(
                    "message" => $this->inventory->error()["message"],
                    "sql" => $this->inventory->last_query()

                );
                $this->sys_error_db($error);
                exit;
            }
            $sql_insert_log = "INSERT INTO warehouselocation_log
                                (WarehouseLocationLogStatus,
                                WarehouseLocationLogWarehouseLocationID,
                                WarehouseLocationLogJSONBefore,
                                WarehouseLocationLogJSONAfter,
                                WarehouseLocationLogUserID,
                                WarehouseLocationLogCreated)
                                VALUES 
                                ('DELETE',
                                ?,
                                NULL,
                                ?,
                                ?,
                                NOW())";
            $qry_insert_log = $this->log_inventory->query($sql_insert_log, [$id, json_encode($json_after), $userid]);
            if (!$qry_insert_log) {
                $error = array(
                    "msg" => "failed insert warehouse log",
                    "message" => $this->log_inventory->error()["message"],
                    "qry" => $this->log_inventory->last_query(),
                    "json_after" => $json_after
                );
                $this->sys_error_db($error);
                exit;
            }
            $this->inventory->trans_complete();

            $result = array(
                "message" => "success",
                "json_after " => $json_after

            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
}