<?php
class Mutasiwarehouse extends MY_Controller
{
    var $db_inventory;
    var $load;
    function __construct()
    {
        parent::__construct();
        $this->db_inventory = $this->load->database("inventory", true);
    }

    function index()
    {
        $cek = $this->db_inventory->query("select database() as current_db")->result();
        echo "API Mutasiwarehouse";
        // print_r($cek); 
    }

    function getWarehouse()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $sql = "SELECT WarehouseID as warehouseId, 
                    WarehouseCode as warehouseCode,
                    WarehouseName as warehouseName
                    FROM warehouse
                    WHERE WarehouseIsActive = 'Y'";
            $qry = $this->db_inventory->query($sql);
            $last_qry = $this->db_inventory->last_query();

            if (!$qry) {
                $this->db_inventory->trans_rollback();
                $error = array(
                    "message" => $this->db_inventory->error()["message"],
                    "sql" => $last_qry
                );
                $this->sys_error_db($error);
                exit;
            }
            $rows = $qry->result_array();
            $result = array(
                "records" => $rows,
            );
            $this->sys_ok($result);
            exit;
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function getAlmari()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            $prm = $this->sys_input;
            $id = "id";
            if (isset($prm['id'])) {
                $id = trim($prm["id"]);
            }
            $sql = "SELECT WarehouseAlmariID as almariId,
                    WarehouseAlmariCode as almariCode,
                    WarehouseAlmariName as almariName,
                    WarehouseAlmariWarehouseID as warehouseId
                    FROM warehousealmari
                    WHERE WarehouseAlmariIsActive = 'Y'
                    AND WarehouseAlmariWarehouseID = ?";
            $qry = $this->db_inventory->query($sql, array($id));
            $last_qry = $this->db_inventory->last_query();

            if (!$qry) {
                $this->db_inventory->trans_rollback();
                $error = array(
                    "message" => $this->db_inventory->error()["message"],
                    "sql" => $last_qry
                );
                $this->sys_error_db($error);
                exit;
            }
            $rows = $qry->result_array();
            $result = array(
                "records" => $rows,
            );
            $this->sys_ok($result);
            exit;
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function getRack()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            $prm = $this->sys_input;
            $id = "id";
            if (isset($prm['id'])) {
                $id = trim($prm["id"]);
            }

            $sql = "SELECT WarehouseRackID as rackId,
                    WarehouseRackCode as rackCode,
                    WarehouseRackName as rackName,
                    WarehouseRackWarehouseAlmariID as almariId
                    FROM warehouserack
                    WHERE  WarehouseRackIsActive = 'Y'
                    AND WarehouseRackWarehouseAlmariID = ?";
            $qry = $this->db_inventory->query($sql, array($id));
            $last_qry = $this->db_inventory->last_query();

            if (!$qry) {
                $this->db_inventory->trans_rollback();
                $error = array(
                    "message" => $this->db_inventory->error()["message"],
                    "sql" => $last_qry
                );
                $this->sys_error_db($error);
                exit;
            }
            $rows = $qry->result_array();
            $result = array(
                "records" => $rows,
            );
            $this->sys_ok($result);
            exit;
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function search()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $prm = $this->sys_input;

            $warehouseId = "warehouseId";
            if (isset($prm['warehouseId'])) {
                $warehouseId = trim($prm["warehouseId"]);
            }

            $almariId = "almariId";
            if (isset($prm['almariId'])) {
                $almariId = trim($prm["almariId"]);
            }

            $rackId = "rackId";
            if (isset($prm['rackId'])) {
                $rackId = trim($prm["rackId"]);
            }

            $sortBy = $prm["sortBy"];
            $sortStatus = $prm["sortStatus"];
            if ($sortBy) {
                $q_sort = "ORDER BY " . $sortBy . " " . $sortStatus;
            }

            $number_offset = 0;
            $number_limit = 10;
            if ($prm["current_page"] > 0) {
                $number_offset = ($prm["current_page"] - 1) * $number_limit;
            }

            $count = "SELECT count(StockID) as total
                    FROM stock
                    JOIN warehouse ON StockWarehouseID = WarehouseID
                        AND WarehouseIsActive = 'Y' AND WarehouseID = ?
                    JOIN warehousealmari ON StockWarehouseAlmariID = WarehouseAlmariID
                        AND WarehouseAlmariIsActive = 'Y' AND StockWarehouseAlmariID = ?
                    JOIN warehouserack ON StockWarehouseRackID = WarehouseRackID
                        AND WarehouseRackIsActive = 'Y' AND StockWarehouseRackID = ?
                    JOIN item ON StockItemID = ItemID
                        AND ItemIsActive = 'Y'
                    JOIN itemunit ON StockItemUnitID = ItemUnitID
                        AND ItemUnitIsActive = 'Y' AND StockQty > 0";

            $qry_count = $this->db_inventory->query($count, [$warehouseId, $almariId, $rackId]);
            $tot_count = 0;
            $tot_page = 0;
            if ($qry_count) {
                $tot_count = $qry_count->result_array()[0]["total"];
                $tot_page = ceil($tot_count/$number_limit);
            } else {
                $this->sys_error_db("stock select count", $this->db_inventory);
                exit;
            }

            $sql = "SELECT StockID as stockId,
                    StockWarehouseID as warehouseId,
                    StockWarehouseAlmariID as almariId,
                    StockWarehouseRackID as rackId,
                    StockStockNumber as stockNo,
                    StockItemID as itemId,
                    ItemName as itemName,
                    StockItemUnitID as itemunitId,
                    ItemUnitName as itemunitName,
                    StockBatchNo as batchNo,
                    StockED as ed,
                    StockQty as qty
                    FROM stock
                    JOIN warehouse ON StockWarehouseID = WarehouseID
                        AND WarehouseIsActive = 'Y' AND WarehouseID = ?
                    JOIN warehousealmari ON StockWarehouseAlmariID = WarehouseAlmariID
                        AND WarehouseAlmariIsActive = 'Y' AND StockWarehouseAlmariID = ?
                    JOIN warehouserack ON StockWarehouseRackID = WarehouseRackID
                        AND WarehouseRackIsActive = 'Y' AND StockWarehouseRackID = ?
                    JOIN item ON StockItemID = ItemID
                        AND ItemIsActive = 'Y'
                    JOIN itemunit ON StockItemUnitID = ItemUnitID
                        AND ItemUnitIsActive = 'Y' AND StockQty > 0 $q_sort
                        LIMIT ? OFFSET ?";

            $qry = $this->db_inventory->query($sql, [
                $warehouseId,
                $almariId,
                $rackId,
                $number_limit,
                $number_offset
            ]);
            //echo $this->db_inventory->last_query();
            if ($qry) {
                $rows = $qry->result_array();
            } else {
                $this->sys_error_db("stock select", $this->db_inventory);
                exit;
            }


            $result = array(
                "total" => $tot_page,
                "total_filter" => $tot_count,
                "records" => $rows
            );
            $this->sys_ok($result);
            exit;
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function searchTo()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $prm = $this->sys_input;

            $warehouseId = "warehouseId";
            if (isset($prm['warehouseId'])) {
                $warehouseId = trim($prm["warehouseId"]);
            }

            $to_almariId = "to_almariId";
            if (isset($prm['to_almariId'])) {
                $to_almariId = trim($prm["to_almariId"]);
            }

            $to_rackId = "to_rackId";
            if (isset($prm['to_rackId'])) {
                $to_rackId = trim($prm["to_rackId"]);
            }

            $sortBy = $prm["sortBy"];
            $sortStatus = $prm["sortStatus"];
            if ($sortBy) {
                $q_sort = "ORDER BY " . $sortBy . " " . $sortStatus;
            }

            $number_offset = 0;
            $number_limit = 10;
            if ($prm["current_page"] > 0) {
                $number_offset = ($prm["current_page"] - 1) * $number_limit;
            }

            $count = "SELECT count(StockID) as total
                    FROM stock
                    JOIN warehouse ON StockWarehouseID = WarehouseID
                        AND WarehouseIsActive = 'Y' AND WarehouseID = ?
                    JOIN warehousealmari ON StockWarehouseAlmariID = WarehouseAlmariID
                        AND WarehouseAlmariIsActive = 'Y' AND StockWarehouseAlmariID = ?
                    JOIN warehouserack ON StockWarehouseRackID = WarehouseRackID
                        AND WarehouseRackIsActive = 'Y' AND StockWarehouseRackID = ?
                    JOIN item ON StockItemID = ItemID
                        AND ItemIsActive = 'Y'
                    JOIN itemunit ON StockItemUnitID = ItemUnitID
                        AND ItemUnitIsActive = 'Y' AND StockQty > 0";

            $qry_count = $this->db_inventory->query($count, [$warehouseId, $to_almariId, $to_rackId]);
            $tot_count = 0;
            $tot_page = 0;
            if ($qry_count) {
                $tot_count = $qry_count->result_array()[0]["total"];
                $tot_page = ceil($tot_count/$number_limit);
            } else {
                $this->sys_error_db("stock select count to", $this->db_inventory);
                exit;
            }

            $sql = "SELECT StockID as to_stockId,
                    StockWarehouseID as warehouseId,
                    StockWarehouseAlmariID as to_almariId,
                    StockWarehouseRackID as to_rackId,
                    StockStockNumber as stockNo,
                    StockItemID as itemId,
                    ItemName as itemName,
                    StockItemUnitID as itemunitId,
                    ItemUnitName as itemunitName,
                    StockBatchNo as batchNo,
                    StockED as ed,
                    StockQty as qty
                    FROM stock
                    JOIN warehouse ON StockWarehouseID = WarehouseID
                        AND WarehouseIsActive = 'Y' AND WarehouseID = ?
                    JOIN warehousealmari ON StockWarehouseAlmariID = WarehouseAlmariID
                        AND WarehouseAlmariIsActive = 'Y' AND StockWarehouseAlmariID = ?
                    JOIN warehouserack ON StockWarehouseRackID = WarehouseRackID
                        AND WarehouseRackIsActive = 'Y' AND StockWarehouseRackID = ?
                    JOIN item ON StockItemID = ItemID
                        AND ItemIsActive = 'Y'
                    JOIN itemunit ON StockItemUnitID = ItemUnitID
                        AND ItemUnitIsActive = 'Y' AND StockQty > 0 $q_sort
                        LIMIT ? OFFSET ?";

            $qry = $this->db_inventory->query($sql, [
                $warehouseId,
                $to_almariId,
                $to_rackId,
                $number_limit,
                $number_offset
            ]);
            if ($qry) {
                $rows = $qry->result_array();
            } else {
                $this->sys_error_db("stock select to", $this->db_inventory);
                exit;
            }

            $result = array(
                "total" => $tot_page,
                "total_filter" => $tot_count,
                "records" => $rows
            );
            $this->sys_ok($result);
            exit;
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function moveTo()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $this->db_inventory->trans_begin();
            $prm = $this->sys_input;
            $userid = $this->sys_user['M_UserID'];

            $warehouseId = "";
            if (isset($prm['warehouseId'])) {
                $warehouseId = trim($prm["warehouseId"]);
            }
            $almariId = "";
            if (isset($prm['almariId'])) {
                $almariId = trim($prm["almariId"]);
            }
            $rackId = "";
            if (isset($prm['rackId'])) {
                $rackId = trim($prm["rackId"]);
            }

            $to_almariId = "";
            if (isset($prm['to_almariId'])) {
                $to_almariId = trim($prm["to_almariId"]);
            }

            $to_rackId = "";
            if (isset($prm['to_rackId'])) {
                $to_rackId = trim($prm["to_rackId"]);
            }

            $stock_no = "";
            if (isset($prm['stock_no'])) {
                $stock_no = trim($prm["stock_no"]);
            }
            $itemId = "";
            if (isset($prm['itemId'])) {
                $itemId = trim($prm["itemId"]);
            }
            $itemunitId = "";
            if (isset($prm['itemunitId'])) {
                $itemunitId = trim($prm["itemunitId"]);
            }
            $batch_no = "";
            if (isset($prm['batch_no'])) {
                $batch_no = trim($prm["batch_no"]);
            }
            $ed = "";
            if (isset($prm['ed'])) {
                $ed = trim($prm["ed"]);
            }
            $qty = "";
            if (isset($prm['qty'])) {
                $qty = trim($prm["qty"]);
            }
            $stockIdFrom = "";
            if (isset($prm['stockIdFrom'])) {
                $stockIdFrom = trim($prm["stockIdFrom"]);
            }
            $stockIdTo = "";
            if (isset($prm['stockIdTo'])) {
                $stockIdTo = trim($prm["stockIdTo"]);
            }

            $sql_asal = "UPDATE stock 
                    SET
                    StockQty = StockQty - ?
                    WHERE StockWarehouseID = ?
                        AND StockWarehouseAlmariID = ?
                        AND StockWarehouseRackID = ?
                        AND StockItemID = ?";
            $qry_asal = $this->db_inventory->query($sql_asal, [$qty, $warehouseId, $almariId, $rackId, $itemId]);
            if (!$qry_asal) {
                $this->db_inventory->trans_rollback();
                $this->sys_error_db("Stock update error", $this->db_inventory);
                exit;
            }

            $sql_qty_tujuan = "INSERT INTO stock(
                StockWarehouseID,
                StockWarehouseAlmariID,
                StockWarehouseRackID,
                StockStockNumber,
                StockItemID,
                StockItemUnitID,
                StockBatchNo,
                StockED,
                StockQty,
                StockLastUpdated,
                StockUserID)
                VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), ?)
                ON DUPLICATE KEY UPDATE StockQty = StockQty + ?";

            $qry_qty_tujuan = $this->db_inventory->query($sql_qty_tujuan, [
                $warehouseId,
                $to_almariId,
                $to_rackId,
                $stock_no,
                $itemId,
                $itemunitId,
                $batch_no,
                $ed,
                $qty,
                $userid,
                $qty
            ]);
            if (!$qry_qty_tujuan) {
                $this->db_inventory->trans_rollback();
                $this->sys_error_db("Stock on duplicate key plus error", $this->db_inventory);
                exit;
            }

            $sql = "SELECT StockID as to_stockId
                    FROM stock 
                    WHERE StockWarehouseID = ?
                    AND StockWarehouseAlmariID = ?
                    AND StockWarehouseRackID = ?
                    AND StockItemID = ?";
            $qry = $this->db_inventory->query($sql, [$warehouseId, $to_almariId, $to_rackId, $itemId]);
            if ($qry) {
                $rows = $qry->result_array();
            } else {
                $this->sys_error_db("stock ID to", $this->db_inventory);
                exit;
            }
            if (count($rows) > 0) {
                $stockIdTo = $rows[0]["to_stockId"];
            } else if (count($rows) < 0) {
                $stockIdTo = 0;
            }

            // stocklog from
            $sql_log_from = "INSERT INTO stocklog(
                StockLogWarehouseID,
                StockLogWarehouseAlmariID,
                StockLogWarehouseRackID,
                StockLogDatetime,
                StockLogItemID,
                StockLogItemUnitID,
                StockLogStockNumber,
                StockLogBatchNo,
                StockLogED,
                StockLogReffID,
                StockLogStatus,
                StockLogQty,
                StockLogUserID) VALUES(?,?,?,NOW(),?,?,?,?,?,?,'MWF',?,?)";
            $qry_log_from = $this->db_inventory->query($sql_log_from, [
                $warehouseId,
                $almariId,
                $rackId,
                $itemId,
                $itemunitId,
                $stock_no,
                $batch_no,
                $ed,
                $stockIdFrom,
                $qty,
                $userid
            ]);
            if (!$qry_log_from) {
                $this->db_inventory->trans_rollback();
                $this->sys_error_db("insert stock log from", $this->db_inventory);
            }

            // stocklog to
            $sql_log_to = "INSERT INTO stocklog(
                StockLogWarehouseID,
                StockLogWarehouseAlmariID,
                StockLogWarehouseRackID,
                StockLogDatetime,
                StockLogItemID,
                StockLogItemUnitID,
                StockLogStockNumber,
                StockLogBatchNo,
                StockLogED,
                StockLogReffID,
                StockLogStatus,
                StockLogQty,
                StockLogUserID) VALUES(?,?,?,NOW(),?,?,?,?,?,?,'MWT',?,?)";
            $qry_log_to = $this->db_inventory->query($sql_log_to, [
                $warehouseId,
                $to_almariId,
                $to_rackId,
                $itemId,
                $itemunitId,
                $stock_no,
                $batch_no,
                $ed,
                $stockIdTo,
                $qty,
                $userid 
            ]);
            if (!$qry_log_to) {
                $this->db_inventory->trans_rollback();
                $this->sys_error_db("insert stock log to", $this->db_inventory);
            }

            $this->db_inventory->trans_commit();
            $result = array(
                "total" => 1,
                "affected_rows" => $this->db_inventory->affected_rows()
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
}