<?php

class PurchaseOrderInventaris extends MY_Controller
{
    var $db;

    public function index()
    {
        echo "Purchase Order API";
    }

    public function __construct()
    {
        parent::__construct();
    }

    function search()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
            }
            $payload = $this->sys_input;
            $userId = $this->sys_user["M_UserID"];

            $query = "SELECT po.*,
            CASE 
                WHEN po.PurchaseOrderDiscountPercent = 0 THEN 'Absolute'
                WHEN po.PurchaseOrderDiscountAmount = 0 THEN 'Percentage'
                    ELSE '' 
                END AS DiscountType,
            CASE 
                WHEN po.PurchaseOrderDiscountPercent <> 0 THEN (po.PurchaseOrderDiscountPercent / 100) * PurchaseOrderSubTotal
                WHEN po.PurchaseOrderDiscountAmount <> 0 THEN PurchaseOrderDiscountAmount
                    ELSE '' 
                END AS DiscountAmount,
            PurchaseOrderTaxAmountPph + PurchaseOrderTaxAmountPpn AS TaxAmount,
            DATE_FORMAT(PurchaseOrderDate, '%d %M %Y') AS DisplayDate,
            SupplierCode,
            SupplierName,
            WarehouseCode,
            IFNULL(itemCategoryID, '') as itemCategoryID,
            IFNULL(itemCategoryName, '') as itemCategoryName,
            CASE
                WHEN WarehouseType = 'B' THEN CONCAT(WarehouseCode,' ', WarehouseName, ' - ', M_BranchName) 
                WHEN WarehouseType = 'R' THEN CONCAT(WarehouseCode,' ', WarehouseName, ' - ', S_RegionalName) 
                ELSE ''
            END as WarehouseName
            FROM purchase_order po
            JOIN item_category ON PurchaseOrderItemCategoryID = itemCategoryID 
            AND itemCategoryID = 2
            LEFT JOIN supplier ON SupplierID = PurchaseOrderSupplierID 
            LEFT JOIN warehouse ON WarehouseID = PurchaseOrderWarehouseID
            LEFT JOIN s_regional ON S_RegionalID = WarehouseS_RegionalID
            LEFT JOIN m_branch ON M_BranchID = WarehouseM_BranchID
            WHERE po.PurchaseOrderIsActive = 'Y'
            AND (PurchaseOrderNumber LIKE '%" . $payload["search"] . "%')";

            $queryCount = "SELECT count(*) as total
                          FROM purchase_order po
                            JOIN item_category ON PurchaseOrderItemCategoryID = itemCategoryID 
                            AND itemCategoryID = 2
                          WHERE PurchaseOrderIsActive = 'Y'
                          AND (PurchaseOrderNumber LIKE '%" . $payload["search"] . "%')";

            if ((isset($payload["startDate"]) && isset($payload["endDate"])) && (trim($payload["startDate"]) !== "" && trim($payload["endDate"]) !== "")) {
                $query .= " AND (PurchaseOrderDate BETWEEN '{$payload["startDate"]}' AND '{$payload["endDate"]}')";
                $queryCount .= " AND (PurchaseOrderDate BETWEEN '{$payload["startDate"]}' AND '{$payload["endDate"]}')";
            }

            if ($payload["status"] !== 'All') {
                $query .= " AND PurchaseOrderStatus = '{$payload["status"]}'";
                $queryCount .= " AND PurchaseOrderStatus = '{$payload["status"]}'";
            }

            $exec = $this->db->query($queryCount, []);

            $numberLimit = 20;
            $numberOffset = 0;
            if ($payload["currentPage"] > 0) {
                $numberOffset = ($payload["currentPage"] - 1) * $numberLimit;
            }

            $totalCount = 0;
            $totalPage = 0;

            if ($exec) {
                $totalCount = $exec->result_array()[0]["total"];
                $totalPage = ceil($totalCount / $numberLimit);
            } else {
                $this->db->trans_rollback();
                $this->sys_error_db("select purchase order", $this->db);
                exit;
            }

            $query .= " ORDER BY PurchaseOrderNumber DESC 
                       LIMIT {$numberLimit} OFFSET {$numberOffset}";
            $exec = $this->db->query($query, []);

            if ($exec) {
                $rows = $exec->result_array();
            } else {
                $this->db->trans_rollback();
                $this->sys_error_db("select purchase order", $this->db);
                exit;
            }

            // Untuk setiap baris tambahakan Total Real Price dan Total Diskon Per Item
            // Agar tidak misleading dengan Jurnal Penerimaan Barang dan Faktur
            foreach ($rows as $key => $row) {
                $rows[$key]['TotalRealPrice'] = 0;
                $rows[$key]['TotalDiskonPerItem'] = 0;

                // Hitung Total Real Price
                $sql = "SELECT PurchaseOrderSummaryPurchaseOrderID, 
                        SUM(PurchaseOrderSummaryQty * PurchaseOrderSummaryPrice) AS TotalRealPrice,
                        SUM(PurchaseOrderSummaryQty * PurchaseOrderSummaryDiscountAmount) AS TotalDiskonPerItem
                        FROM purchase_order_summary
                        WHERE PurchaseOrderSummaryPurchaseOrderID = ?";
                $qry = $this->db->query($sql, [$row['PurchaseOrderID']]);
                if ($qry) {
                    $summaryRow = $qry->row_array();
                    $rows[$key]['TotalRealPrice'] = floatval($summaryRow['TotalRealPrice']);
                    $rows[$key]['TotalDiskonPerItem'] = floatval($summaryRow['TotalDiskonPerItem']);
                } else {
                    $rows[$key]['TotalRealPrice'] = 0;
                    $rows[$key]['TotalDiskonPerItem'] = 0;
                    $this->db->trans_rollback();
                    $this->sys_error_db("select purchase order", $this->db);
                    exit;
                }
            }

            $result = array(
                "totalPage" => $totalPage,
                "totalFilter" => $totalCount,
                "records" => $rows,
                "sql" => $this->db->last_query()
            );

            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function getSupplier()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            $payload = $this->sys_input;
            $userId = $this->sys_user["M_UserID"];

            $search = '%%';
            if (isset($payload['search'])) {
                $search = trim($payload["search"]);
                if ($search != "") {
                    $search = '%' . $search . '%';
                } else {
                    $search = '%%';
                }
            }

            $sql = "SELECT SupplierID,
                    SupplierCode,
                    SupplierName
                    FROM supplier
                    WHERE SupplierIsActive = 'Y'
                    AND SupplierName LIKE '{$search}'
                    ORDER BY SupplierName ASC ";
            $qry = $this->db->query($sql, []);
            if (!$qry) {
                $this->db->trans_rollback();
                $this->sys_error_db("select supplier", $this->db);;
                exit;
            }

            $rows = $qry->result_array();

            $result = array(
                "records" => $rows,
                "sql" => $this->db->last_query()
            );

            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    function getItemCategory()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            $payload = $this->sys_input;


            $sql = "SELECT * FROM item_category WHERE ItemCategoryIsActive = 'Y' AND itemCategoryID = 2";
            $qry = $this->db->query($sql, []);
            if (!$qry) {
                $this->db->trans_rollback();
                $this->sys_error_db("select supplier", $this->db);;
                exit;
            }

            $rows = $qry->result_array();

            $result = array(
                "records" => $rows,
                "sql" => $this->db->last_query()
            );

            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function getWarehouse()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            $payload = $this->sys_input;
            $userId = $this->sys_user["M_UserID"];
            $regionalId = $payload["regionalId"];
            $categoryID = $payload['categoryID'];

            $search = '%%';
            if (isset($payload['search'])) {
                $search = trim($payload["search"]);
                if ($search != "") {
                    $search = '%' . $search . '%';
                } else {
                    $search = '%%';
                }
            }

            $isRegional = "X";
            if ($categoryID == '1') {
                $isRegional = 'R';
            }

            $sql = "SELECT 
                    WarehouseID,
                    WarehouseCode,
                    CASE
                        WHEN WarehouseType = 'B' THEN CONCAT(WarehouseCode,' ', WarehouseName, ' - ', M_BranchName) 
                        WHEN WarehouseType = 'R' THEN CONCAT(WarehouseCode,' ', WarehouseName, ' - ', S_RegionalName) 
                    ELSE ''
                    END WarehouseName,
                    WarehouseType,
                    WarehouseIsDefault,
                    WarehouseS_RegionalID,
                    WarehouseM_BranchID,
                    S_RegionalID,
                    S_RegionalName
                    FROM warehouse
                    JOIN s_regional ON WarehouseS_RegionalID = S_RegionalID AND S_RegionalIsActive = 'Y'
                    LEFT JOIN m_branch ON WarehouseM_BranchID = M_BranchID AND M_BranchIsActive = 'Y'
                    where WarehouseS_RegionalID = ?
                        AND WarehouseIsActive = 'Y'
                        AND WarehouseIsTransit = 'N'
                        AND (WarehouseType = ? OR ? = 'X')
                    ORDER BY WarehouseID ASC ";
            $qry = $this->db->query($sql, [$regionalId, $isRegional, $isRegional]);
            if (!$qry) {
                $this->db->trans_rollback();
                $this->sys_error_db("select warehouse", $this->db);;
                exit;
            }

            $rows = $qry->result_array();

            $result = array(
                "records" => $rows,
                "sql" => $this->db->last_query()
            );

            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function getItem()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
            }

            $regionalId = $this->sys_user["S_RegionalID"];
            $payload = $this->sys_input;
            $itemCategoryID = $payload['itemCategoryID'];
            $supplierID = $payload['supplierID'];

            $search = '%%';
            if (isset($payload['search'])) {
                $search = trim($payload["search"]);
                if ($search != "") {
                    $search = '%' . $search . '%';
                } else {
                    $search = '%%';
                }
            }

            if (!isset($supplierID)) {
                $this->sys_error("Supplier ID is required");
                exit;
            }

            $numberLimit = 10;
            $numberOffset = 0;
            if (isset($payload["currentPage"]) && $payload["currentPage"] > 0) {
                $numberOffset = ($payload["currentPage"] - 1) * $numberLimit;
            }

            $countSql = "SELECT COUNT(*) as total
                FROM purchase_request_flag prf
                JOIN purchase_request_detail prd ON prf.PurchaseRequestFlagPurchaseRequestDetailID = prd.PurchaseRequestDetailID 
                JOIN m_item mi ON prd.PurchaseRequestDetailM_ItemID = mi.M_ItemID AND mi.M_ItemDesc LIKE ? AND mi.M_ItemItem_CategoryID = ?
                JOIN supplier_price sp ON mi.M_ItemID = sp.SupplierPriceM_ItemID AND sp.SupplierPriceSupplierID = ? AND sp.SupplierPriceIsActive = 'Y'
                WHERE prf.PurchaseRequestFlagStatus LIKE '%PO%'
                AND prf.PurchaseRequestFlagS_RegionalID = ?
                AND prf.PurchaseRequestFlagQtyRest > 0
                AND (prf.PurchaseRequestFlagQtyRest - prf.PurchaseRequestFlagQtyProses) > 0";

            $countQry = $this->db->query($countSql, [$search, $itemCategoryID, $supplierID, $regionalId]);

            if (!$countQry) {
                $this->db->trans_rollback();
                $this->sys_error_db("count items error", $this->db);
                exit;
            }

            $totalCount = $countQry->row()->total;
            $totalPage = ceil($totalCount / $numberLimit);

            $sql = "SELECT DISTINCT
                    PurchaseRequestFlagID,
                    PurchaseRequestFlagM_BranchCode as BranchCode,
                    PurchaseRequestFlagQtyRest - PurchaseRequestFlagQtyProses as UnprocessFlagQty,
                    PurchaseRequestDetailID,
                    PurchaseRequestDetailPurchaseRequestID as PurchaseRequestID,
                    PurchaseRequestDetailQty as RequestQty,
                    PurchaseRequestFlagQtyProses AS QtyProses,
                    (PurchaseRequestDetailQty - PurchaseRequestFlagQtyProses) AS RemainingQty,
                    M_ItemID,
                    M_ItemCode,
                    M_ItemDesc,
                    requnit.ItemUnitID as ReqItemUnitID,
                    requnit.ItemUnitName as ReqItemUnitName,
                    purunit.ItemUnitID as PoItemUnitID,
                    purunit.ItemUnitName as PoItemUnitName,
                    SupplierPricePrice as Price,
                    SupplierPriceID,
                    S_RegionalName,
                    S_RegionalID,
                    CASE
                        WHEN M_BranchID IS NULL OR M_BranchID = 0 THEN wr.WarehouseID
                        WHEN M_BranchID IS NOT NULL OR M_BranchID <> 0 THEN wb.WarehouseID
                    END as WarehouseID,
                    CASE
                        WHEN M_BranchID IS NULL OR M_BranchID = 0 THEN CONCAT(wr.WarehouseCode,' ', wr.WarehouseName, ' - ', S_RegionalName)
                        WHEN M_BranchID IS NOT NULL OR M_BranchID <> 0 THEN CONCAT(wb.WarehouseCode,' ', wb.WarehouseName, ' - ', M_BranchName)
                    END as WarehouseName,
                    M_BranchName,
                    M_BranchName as RequesterBranchName, -- Agar sama dengan getItemUpdate saja, tidak perlu edit FE
                    M_BranchID,
                    PurchaseRequestNumber,
                    0 as discount,
                    'R' as discountType,
                    'SPLIT_PR' as qty_mode
                    FROM purchase_request_flag
                    JOIN purchase_request_detail
                        ON PurchaseRequestFlagPurchaseRequestDetailID = PurchaseRequestDetailID
                        AND PurchaseRequestFlagStatus LIKE '%PO%'
                        AND PurchaseRequestFlagS_RegionalID = ?
                        AND PurchaseRequestFlagQtyRest > 0
                    JOIN m_item 
                        ON PurchaseRequestDetailM_ItemID = M_ItemID
                        AND M_ItemDesc LIKE ?
                        AND M_ItemItem_CategoryID = {$itemCategoryID}
                    JOIN supplier_price  
                        ON M_ItemID = SupplierPriceM_ItemID
                        AND SupplierPriceSupplierID = ?
                        AND SupplierPriceIsActive = 'Y' 
                    JOIN itemunit requnit
                        ON PurchaseRequestDetailItemUnitID = requnit.ItemUnitID
                    LEFT JOIN itemunitmap
                        ON PurchaseRequestDetailM_ItemID = ItemUnitMapM_ItemID
                        AND ItemUnitMapIsPurchase = 'Y'
                    LEFT JOIN itemunit purunit
                        ON ItemUnitMapItemUnitID = purunit.ItemUnitID
                    JOIN s_regional 
                        ON PurchaseRequestFlagS_RegionalID = S_RegionalID AND S_RegionalIsActive = 'Y'
                    JOIN warehouse wr 
                        ON S_RegionalID  = wr.WarehouseS_RegionalID
                        AND wr.WarehouseType = 'R'
                        AND wr.WarehouseIsActive = 'Y' 
                        AND wr.WarehouseIsDefault = 'Y'
                    LEFT JOIN m_branch 
                        ON PurchaseRequestFlagM_BranchCode = M_BranchCode 
                        AND M_BranchIsActive = 'Y'
                    LEFT JOIN warehouse wb 
                        ON S_RegionalID  = wb.WarehouseS_RegionalID
                        AND M_BranchID = wb.WarehouseM_BranchID
                        AND wb.WarehouseType = 'B'
                        AND wb.WarehouseIsActive = 'Y' 
                        AND wb.WarehouseIsDefault = 'Y'
                    LEFT JOIN purchase_order_detail
                        ON PurchaseRequestFlagID = PurchaseOrderDetailPurchaseRequestFlagID
                        AND PurchaseRequestDetailPurchaseRequestID = PurchaseOrderDetailPurchaseRequestID
                        AND PurchaseRequestDetailID = PurchaseOrderDetailPurchaseRequestDetailID
                        AND PurchaseOrderDetailIsActive = 'Y'
                    LEFT JOIN purchase_request
                        ON PurchaseRequestDetailPurchaseRequestID = PurchaseRequestID
                    WHERE ((PurchaseRequestFlagQtyRest - PurchaseRequestFlagQtyProses) > 0)
                    ORDER BY M_ItemDesc ASC, PurchaseRequestNumber DESC
                    LIMIT {$numberLimit} OFFSET {$numberOffset}";

            $qry = $this->db->query($sql, [$payload['regionalId'], $search, $supplierID]);

            // echo $this->db->last_query();
            // exit;

            if (!$qry) {
                $this->db->trans_rollback();
                $this->sys_error_db("select items error", $this->db);
                exit;
            }

            $rows = $qry->result_array();

            foreach ($rows as &$row) {
                // Cek Unit Request dan Purchase sama atau tidak

                // TODO: Jika PurchaseItemUnitID == null maka jangan tampilkan item dan continue

                // step 1 hitung sisa split
                $remainingQty = floatval($row['RemainingQty']);

                if ($remainingQty <= 0) {
                    continue; // sudah habis displit, jangan tampilkan
                }

                // default PoQty = sisa PR (BELUM KONVERSI UNIT)
                $row['PoQty'] = $remainingQty;

                // step 2 konversi unit
                // Jika beda maka lakukan konversi rounding up ke unit purchase dan ganti PurchaseQty
                if ($row['ReqItemUnitID'] !== $row['PoItemUnitID']) {
                    // Lookup unitconvert
                    $sqlConvert = "SELECT UnitConvertAmount FROM unitconvert
                        WHERE UnitConvertFromItemUnitID = ?
                        AND UnitConvertToItemUnitID = ?
                        AND UnitConvertIsActive = 'Y'
                        ORDER BY UnitConvertID DESC LIMIT 1";
                    $qryConvert = $this->db->query($sqlConvert, [$row['PoItemUnitID'], $row['ReqItemUnitID']]);
                    if (!$qryConvert) {
                        $this->db->trans_rollback();
                        $this->sys_error_db("select items error", $this->db);
                        exit;
                    }

                    // Kalau tidak ditemukan konversi maka tambahkan flag 'UnitReqPoConvertStatus'
                    if ($qryConvert->num_rows() == 0) {
                        $row['PoQty'] = 0;
                        $row['UnitReqPoConvertStatus'] = 'failed';
                        $row['UnitReqPoConvertMsg'] = "Mapping konversi Req Unit {$row['ItemUnitName']} dengan PO Unit {$row['PoItemUnitName']} tidak ada";
                    } else {
                        // Kalau ada maka kalkulasi round up unit req ke unit purchase
                        $convertValue = $qryConvert->row()->UnitConvertAmount;

                        // RoundUpPoQty = Jumlah Request / Nilai Konversi lalu bulatkan ke atas integer
                        // Misal: jika hasil 0.1 -> 1, jika 1.6 -> 2
                        $roundUpPoQty = ceil($remainingQty / $convertValue);

                        $row['PoQty'] = $roundUpPoQty;
                        $row['UnitReqPoConvertStatus'] = 'success';
                        $row['UnitReqPoConvertMsg'] = "Berhasil mapping konversi Req {$remainingQty} {$row['ItemUnitName']} ke PO {$roundUpPoQty} {$row['PoItemUnitName']}";
                    }
                }
                // Jika sama maka PoQty = remainingQty	
                else {
                    $row['UnitReqPoConvertStatus'] = 'without';
                    $row['UnitReqPoConvertMsg'] = "Tidak perlu mapping konversi Req {$remainingQty} {$row['ItemUnitName']} ke PO {$roundUpPoQty} {$row['PoItemUnitName']}";
                }

                $row['Total'] = floatval($row['Price']) * floatval($row['PoQty']);
                $row['keyID'] = "{$row['M_ItemID']}_{$row['PoItemUnitID']}";
                $row['DefaultPurchase'] = $this->getDefaultPurchase($supplierID, $row['M_ItemID']);
            }
            unset($row); // unset to prevent accidental modication

            $result = array(
                "totalRow" => $totalCount,
                "totalPage" => $totalPage,
                "records" => $rows
            );

            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    /** 
     * Refactored on 20 June 2025
     * Whats new:
     * > List Item PR dengan Item Unit yang berbeda dengan SupplierPrice tetap ditampilkan asal ada di unitconvert
     * > Kalau Unit Request dan Unit Convert berbeda, lakukan konversi dan rounding up request ke unit purchase
     * > Refactor SQL Count
     */
    function getItem_stable()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
            }

            $regionalId = $this->sys_user["S_RegionalID"];
            $payload = $this->sys_input;
            $itemCategoryID = $payload['itemCategoryID'];
            $supplierID = $payload['supplierID'];

            $search = '%%';
            if (isset($payload['search'])) {
                $search = trim($payload["search"]);
                if ($search != "") {
                    $search = '%' . $search . '%';
                } else {
                    $search = '%%';
                }
            }

            if (!isset($supplierID)) {
                $this->sys_error("Supplier ID is required");
                exit;
            }

            $numberLimit = 10;
            $numberOffset = 0;
            if (isset($payload["currentPage"]) && $payload["currentPage"] > 0) {
                $numberOffset = ($payload["currentPage"] - 1) * $numberLimit;
            }

            $countSql = "SELECT COUNT(*) as total
                FROM purchase_request_flag prf
                JOIN purchase_request_detail prd ON prf.PurchaseRequestFlagPurchaseRequestDetailID = prd.PurchaseRequestDetailID 
                JOIN m_item mi ON prd.PurchaseRequestDetailM_ItemID = mi.M_ItemID AND mi.M_ItemDesc LIKE ? AND mi.M_ItemItem_CategoryID = ?
                JOIN supplier_price sp ON mi.M_ItemID = sp.SupplierPriceM_ItemID AND sp.SupplierPriceSupplierID = ? AND sp.SupplierPriceIsActive = 'Y'
                WHERE prf.PurchaseRequestFlagStatus LIKE '%PO%'
                AND prf.PurchaseRequestFlagS_RegionalID = ?
                AND prf.PurchaseRequestFlagQtyRest > 0
                AND (prf.PurchaseRequestFlagQtyRest - prf.PurchaseRequestFlagQtyProses) > 0";

            $countQry = $this->db->query($countSql, [$search, $itemCategoryID, $supplierID, $regionalId]);

            if (!$countQry) {
                $this->db->trans_rollback();
                $this->sys_error_db("count items error", $this->db);
                exit;
            }

            $totalCount = $countQry->row()->total;
            $totalPage = ceil($totalCount / $numberLimit);

            $sql = "SELECT 
                    PurchaseRequestFlagID,
                    PurchaseRequestFlagM_BranchCode as BranchCode,
                    -- PurchaseRequestFlagQtyRest - PurchaseRequestFlagQtyProses as FlagQty,
                    PurchaseRequestDetailQty as FlagQty,
                    PurchaseRequestFlagQtyRest - PurchaseRequestFlagQtyProses as PoQty,
                    PurchaseRequestFlagQtyRest - PurchaseRequestFlagQtyProses as PoQtyBefore,
                    PurchaseRequestDetailID,
                    PurchaseRequestDetailPurchaseRequestID,
                    PurchaseRequestDetailQty as RequestQty,
                    PurchaseRequestDetailItemUnitID as RequestItemUnitID,
                    M_ItemID,
                    M_ItemCode,
                    M_ItemDesc,
                    M_ItemPurchaseUOM,
                    iu1.ItemUnitName,
                    iu1.ItemUnitID,
                    ItemUnitMapItemUnitID as PoItemUnitID,
                    iu2.ItemUnitName as PoItemUnitName,
                    SupplierPricePrice as Price,
                    S_RegionalName,
                    S_RegionalID,
                    CASE
                        WHEN M_BranchID IS NULL OR M_BranchID = 0 THEN wr.WarehouseID
                        WHEN M_BranchID IS NOT NULL OR M_BranchID <> 0 THEN wb.WarehouseID
                    END as WarehouseID,
                    CASE
                        WHEN M_BranchID IS NULL OR M_BranchID = 0 THEN CONCAT(wr.WarehouseCode,' ', wr.WarehouseName, ' - ', S_RegionalName)
                        WHEN M_BranchID IS NOT NULL OR M_BranchID <> 0 THEN CONCAT(wb.WarehouseCode,' ', wb.WarehouseName, ' - ', M_BranchName)
                    END as WarehouseName,
                    M_BranchName,
                    M_BranchID,
                    PurchaseRequestNumber,
                    0 as discount,
                    'R' as discountType
                    FROM purchase_request_flag
                    JOIN purchase_request_detail
                        ON PurchaseRequestFlagPurchaseRequestDetailID = PurchaseRequestDetailID
                        AND PurchaseRequestFlagStatus LIKE '%PO%'
                        AND PurchaseRequestFlagS_RegionalID = ?
                        AND PurchaseRequestFlagQtyRest > 0
                    JOIN m_item 
                        ON PurchaseRequestDetailM_ItemID = M_ItemID
                        AND M_ItemDesc LIKE ?
                        AND M_ItemItem_CategoryID = {$itemCategoryID}
                    JOIN supplier_price  
                        ON M_ItemID = SupplierPriceM_ItemID
                        AND SupplierPriceSupplierID = ?
                        -- AND PurchaseRequestDetailItemUnitID = SupplierPriceItemUnitID --Agar PR dengan Item yang belum ada di Supplier tetap bisa dilakukan PO
                        AND SupplierPriceIsActive = 'Y' 
                    JOIN itemunit iu1
                        ON PurchaseRequestDetailItemUnitID = iu1.ItemUnitID
                    LEFT JOIN itemunitmap
                        ON PurchaseRequestDetailM_ItemID = ItemUnitMapM_ItemID
                        AND ItemUnitMapIsPurchase = 'Y'
                    LEFT JOIN itemunit iu2
                        ON ItemUnitMapItemUnitID = iu2.ItemUnitID
                    JOIN s_regional 
                        ON PurchaseRequestFlagS_RegionalID = S_RegionalID AND S_RegionalIsActive = 'Y'
                    JOIN warehouse wr 
                        ON S_RegionalID  = wr.WarehouseS_RegionalID
                        AND wr.WarehouseType = 'R'
                        AND wr.WarehouseIsActive = 'Y' 
                        AND wr.WarehouseIsDefault = 'Y'
                    LEFT JOIN m_branch 
                        ON PurchaseRequestFlagM_BranchCode = M_BranchCode 
                        AND M_BranchIsActive = 'Y'
                    LEFT JOIN warehouse wb 
                        ON S_RegionalID  = wb.WarehouseS_RegionalID
                        AND M_BranchID = wb.WarehouseM_BranchID
                        AND wb.WarehouseType = 'B'
                        AND wb.WarehouseIsActive = 'Y' 
                        AND wb.WarehouseIsDefault = 'Y'
                    LEFT JOIN purchase_order_detail
                        ON PurchaseRequestFlagID = PurchaseOrderDetailPurchaseRequestFlagID
                        AND PurchaseRequestDetailPurchaseRequestID = PurchaseOrderDetailPurchaseRequestID
                        AND PurchaseRequestDetailID = PurchaseOrderDetailPurchaseRequestDetailID
                        AND PurchaseOrderDetailIsActive = 'Y'
                    LEFT JOIN purchase_request
                        ON PurchaseRequestDetailPurchaseRequestID = PurchaseRequestID
                    WHERE ((PurchaseRequestFlagQtyRest - PurchaseRequestFlagQtyProses) > 0)
                    ORDER BY M_ItemDesc ASC
                    LIMIT {$numberLimit} OFFSET {$numberOffset}";

            $qry = $this->db->query($sql, [$payload['regionalId'], $search, $supplierID]);

            if (!$qry) {
                $this->db->trans_rollback();
                $this->sys_error_db("select items error", $this->db);
                exit;
            }

            $rows = $qry->result_array();

            foreach ($rows as &$row) {
                // Cek Unit Request dan Purchase sama atau tidak

                // TODO: Jika PurchaseItemUnitID == null maka jangan tampilkan item dan continue

                // Jika beda maka lakukan konversi rounding up ke unit purchase dan ganti PurchaseQty
                if ($row['RequestItemUnitID'] !== $row['PoItemUnitID']) {
                    // Lookup unitconvert
                    $sqlConvert = "SELECT UnitConvertAmount FROM unitconvert
                        WHERE UnitConvertFromItemUnitID = ?
                        AND UnitConvertToItemUnitID = ?
                        AND UnitConvertIsActive = 'Y'
                        ORDER BY UnitConvertID DESC LIMIT 1";
                    $qryConvert = $this->db->query($sqlConvert, [$row['PoItemUnitID'], $row['RequestItemUnitID']]);
                    if (!$qryConvert) {
                        $this->db->trans_rollback();
                        $this->sys_error_db("select items error", $this->db);
                        exit;
                    }

                    // Kalau tidak ditemukan konversi maka tambahkan flag 'UnitReqPoConvertStatus'
                    if ($qryConvert->num_rows() == 0) {
                        $row['PoQty'] = 0;
                        $row['UnitReqPoConvertStatus'] = 'failed';
                        $row['UnitReqPoConvertMsg'] = "Mapping konversi Req Unit {$row['ItemUnitName']} dengan PO Unit {$row['PoItemUnitName']} tidak ada";
                    } else {
                        // Kalau ada maka kalkulasi round up unit req ke unit purchase
                        $convertValue = $qryConvert->row()->UnitConvertAmount;

                        // RoundUpPoQty = Jumlah Request / Nilai Konversi lalu bulatkan ke atas integer
                        // Misal: jika hasil 0.1 -> 1, jika 1.6 -> 2
                        $roundUpPoQty = ceil($row['RequestQty'] / $convertValue);

                        $row['PoQty'] = $roundUpPoQty;
                        $row['UnitReqPoConvertStatus'] = 'success';
                        $row['UnitReqPoConvertMsg'] = "Berhasil mapping konversi Req {$row['RequestQty']} {$row['ItemUnitName']} ke PO {$roundUpPoQty} {$row['PoItemUnitName']}";
                    }
                }
                // Jika sama maka PoQty = RequestQty	
                else {
                    $row['UnitReqPoConvertStatus'] = 'without';
                    $row['UnitReqPoConvertMsg'] = "Tidak perlu mapping konversi Req {$row['RequestQty']} {$row['ItemUnitName']} ke PO {$roundUpPoQty} {$row['PoItemUnitName']}";
                }

                $row['Total'] = floatval($row['Price']) * floatval($row['PoQty']);
                $row['keyID'] = "{$row['M_ItemID']}_{$row['ItemUnitID']}";
                // $row['DefaultPurchase'] = $this->getDefaultPurchase($row['M_ItemID'], $row['ItemUnitID']);
                $row['DefaultPurchase'] = $this->getDefaultPurchase($supplierID, $row['M_ItemID']);
            }
            unset($row); // unset to prevent accidental modication

            $result = array(
                "totalRow" => $totalCount,
                "totalPage" => $totalPage,
                // "sql" => $this->db->last_query(),
                "records" => $rows
            );

            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function getItem_old()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
            }

            $payload = $this->sys_input;
            $itemCategoryID = $payload['itemCategoryID'];
            $regionalId = $this->sys_user["S_RegionalID"];

            $search = '%%';
            if (isset($payload['search'])) {
                $search = trim($payload["search"]);
                if ($search != "") {
                    $search = '%' . $search . '%';
                } else {
                    $search = '%%';
                }
            }

            if (!isset($payload['supplierID'])) {
                $this->sys_error("Supplier ID is required");
                exit;
            }

            $numberLimit = 10;
            $numberOffset = 0;
            if (isset($payload["currentPage"]) && $payload["currentPage"] > 0) {
                $numberOffset = ($payload["currentPage"] - 1) * $numberLimit;
            }

            $countSql = "SELECT 
                        COUNT(PurchaseRequestFlagID) as total
                    FROM purchase_request_flag
                    JOIN purchase_request_detail
                        ON PurchaseRequestFlagPurchaseRequestDetailID = PurchaseRequestDetailID
                        AND PurchaseRequestFlagStatus LIKE '%PO%'
                        AND PurchaseRequestFlagS_RegionalID = ?
                    JOIN m_item 
                        ON PurchaseRequestDetailM_ItemID =M_ItemID
                        AND M_ItemDesc LIKE ?
                        AND M_ItemItem_CategoryID = {$itemCategoryID}
                    JOIN supplier_price  
                        ON M_ItemID = SupplierPriceM_ItemID
                        AND SupplierPriceSupplierID = ?
                        AND PurchaseRequestDetailItemUnitID = SupplierPriceItemUnitID 
                        AND SupplierPriceIsActive = 'Y'
                    JOIN itemunit
                        ON PurchaseRequestDetailItemUnitID = ItemUnitID
                    JOIN s_regional 
                        ON PurchaseRequestFlagS_RegionalID = S_RegionalID AND S_RegionalIsActive = 'Y'
                    JOIN warehouse wr 
                        ON S_RegionalID  = wr.WarehouseS_RegionalID
                        AND wr.WarehouseType = 'R'
                        AND wr.WarehouseIsActive = 'Y' 
                        AND wr.WarehouseIsDefault = 'Y'
                    LEFT JOIN m_branch 
                        ON PurchaseRequestFlagM_BranchCode = M_BranchCode 
                        AND M_BranchIsActive = 'Y'
                    LEFT JOIN warehouse wb 
                        ON S_RegionalID  = wb.WarehouseS_RegionalID
                        AND M_BranchID = wb.WarehouseM_BranchID
                        AND wb.WarehouseType = 'B'
                        AND wb.WarehouseIsActive = 'Y' 
                        AND wb.WarehouseIsDefault = 'Y'
                    WHERE ((PurchaseRequestFlagQtyRest - PurchaseRequestFlagQtyProses) > 0)";

            $countQry = $this->db->query($countSql, [$payload['regionalId'],  $search, $payload['supplierID'],]);

            if (!$countQry) {
                $this->db->trans_rollback();
                $this->sys_error_db("count items error", $this->db);
                exit;
            }

            $totalCount = $countQry->row()->total;
            $totalPage = ceil($totalCount / $numberLimit);

            $sql = "SELECT 
                    PurchaseRequestFlagID,
                    PurchaseRequestDetailID,
                    PurchaseRequestDetailPurchaseRequestID,
                    M_ItemID,
                    M_ItemCode,
                    M_ItemDesc,
                    M_ItemPurchaseUOM,
                    ItemUnitName,
                    ItemUnitID,
                    SupplierPricePrice as Price,
                    PurchaseRequestDetailQty as RequestQty,
                    PurchaseRequestFlagM_BranchCode as BranchCode,
                    PurchaseRequestFlagQtyRest - PurchaseRequestFlagQtyProses as FlagQty,
                    PurchaseRequestFlagQtyRest - PurchaseRequestFlagQtyProses as PoQty,
                    S_RegionalName,
                    S_RegionalID,
                    M_BranchName,
                    M_BranchID,
                    PurchaseRequestNumber,
                    CASE
                    WHEN M_BranchID IS NULL OR M_BranchID = 0 THEN wr.WarehouseID
                    WHEN M_BranchID IS NOT NULL OR M_BranchID <> 0 THEN wb.WarehouseID
                    END as WarehouseID,
                    CASE
                    WHEN M_BranchID IS NULL OR M_BranchID = 0 THEN CONCAT(wr.WarehouseCode,' ', wr.WarehouseName, ' - ', S_RegionalName)
                    WHEN M_BranchID IS NOT NULL OR M_BranchID <> 0 THEN CONCAT(wb.WarehouseCode,' ', wb.WarehouseName, ' - ', M_BranchName)
                    END as WarehouseName ,
                    0 as discount,
                    'R' as discountType
                    FROM purchase_request_flag
                    JOIN purchase_request_detail
                        ON PurchaseRequestFlagPurchaseRequestDetailID = PurchaseRequestDetailID
                        AND PurchaseRequestFlagStatus LIKE '%PO%'
                        AND PurchaseRequestFlagS_RegionalID = ?
                        AND PurchaseRequestFlagQtyRest > 0
                    JOIN m_item 
                        ON PurchaseRequestDetailM_ItemID =M_ItemID
                        AND M_ItemDesc LIKE ?
                        AND M_ItemItem_CategoryID = {$itemCategoryID}
                    JOIN supplier_price  
                        ON M_ItemID = SupplierPriceM_ItemID
                        AND SupplierPriceSupplierID = ?
                        AND PurchaseRequestDetailItemUnitID = SupplierPriceItemUnitID
                        AND SupplierPriceIsActive = 'Y' 
                    JOIN itemunit
                        ON PurchaseRequestDetailItemUnitID = ItemUnitID
                    JOIN s_regional 
                        ON PurchaseRequestFlagS_RegionalID = S_RegionalID AND S_RegionalIsActive = 'Y'
                    JOIN warehouse wr 
                        ON S_RegionalID  = wr.WarehouseS_RegionalID
                        AND wr.WarehouseType = 'R'
                        AND wr.WarehouseIsActive = 'Y' 
                        AND wr.WarehouseIsDefault = 'Y'
                    LEFT JOIN m_branch 
                        ON PurchaseRequestFlagM_BranchCode = M_BranchCode 
                        AND M_BranchIsActive = 'Y'
                    LEFT JOIN warehouse wb 
                        ON S_RegionalID  = wb.WarehouseS_RegionalID
                        AND M_BranchID = wb.WarehouseM_BranchID
                        AND wb.WarehouseType = 'B'
                        AND wb.WarehouseIsActive = 'Y' 
                        AND wb.WarehouseIsDefault = 'Y'
                    LEFT JOIN purchase_order_detail
                        ON PurchaseRequestFlagID = PurchaseOrderDetailPurchaseRequestFlagID
                        AND PurchaseRequestDetailPurchaseRequestID = PurchaseOrderDetailPurchaseRequestID
                        AND PurchaseRequestDetailID = PurchaseOrderDetailPurchaseRequestDetailID
                        AND PurchaseOrderDetailIsActive = 'Y'
                    LEFT JOIN purchase_request
                        ON PurchaseRequestDetailPurchaseRequestID = PurchaseRequestID
                    WHERE ((PurchaseRequestFlagQtyRest - PurchaseRequestFlagQtyProses) > 0)
                    ORDER BY M_ItemDesc ASC
                    LIMIT {$numberLimit} OFFSET {$numberOffset}";

            $qry = $this->db->query($sql, [$payload['regionalId'], $search, $payload['supplierID']]);

            if (!$qry) {
                $this->db->trans_rollback();
                $this->sys_error_db("select items error", $this->db);
                exit;
            }

            $rows = $qry->result_array();

            foreach ($rows as $key => $row) {
                $rows[$key]['Total'] = floatval($row['Price']) * floatval($row['FlagQty']);
                $rows[$key]['keyID'] = "{$row['M_ItemID']}_{$row['ItemUnitID']}";
                $rows[$key]['DefaultPurchase'] = $this->getDefaultPurchase($row['M_ItemID'], $row['ItemUnitID']);
            }

            $result = array(
                "totalPage" => $totalPage,
                "records" => $rows,
                "sql" => $this->db->last_query()
            );

            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function saveOrder()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
            }

            // Debugging
            // echo json_encode($this->sys_input, JSON_PRETTY_PRINT);
            // exit;

            $this->db->trans_begin(true); // Memulai transaksi

            $payload    = $this->sys_input;
            $user       = $this->sys_user;
            $userId     = $this->sys_user["M_UserID"];
            $regId      = $this->sys_user["S_RegionalID"];

            $areaid = $user['M_BranchID'];
            $areatype = 'B';
            if ($user['loginLevel'] == 'regional') {
                $areaid = $user['S_RegionalID'];
                $areatype = 'R';
            }

            $sqlusrdivisi = "SELECT M_UserDivisionDivisionID FROM m_userdivision 
                WHERE M_UserDivisionM_UserID = ? AND M_UserDivisionIsActive = 'Y'";
            $queusrdivisi = $this->db->query($sqlusrdivisi, [$userId]);
            if (!$queusrdivisi) {
                $this->db->trans_rollback();
                $this->sys_error_db("[Error] get user divisi", $this->db);
                exit;
            }
            $userDivID = $queusrdivisi->row_array()['M_UserDivisionDivisionID'];

            $sqlnum = "SELECT `fn_penomoran`(?, ?, ?, ?, ?, ?) AS numpd;";
            $quenum = $this->db->query($sqlnum, ['PO', $userDivID, $areatype, $areaid, 'SM', 'N']);
            if (!$quenum) {
                $this->db->trans_rollback();
                $this->sys_error_db("[Error] generate number doc", $this->db);
                exit;
            }
            $numpd = $quenum->row_array()['numpd'];

            // Step 1: Insert ke tabel purchase_order
            $sqlPurchaseOrder = "INSERT INTO purchase_order (
                PurchaseOrderNumber,
                PurchaseOrderItemCategoryID,
                PurchaseOrderDate,
                PurchaseOrderRefNumber,
                PurchaseOrderSupplierID,
                PurchaseOrderS_RegionalID,
                PurchaseOrderTaxPpnType,
                PurchaseOrderTaxPercentPph,
                PurchaseOrderTaxPercentPpn,
                PurchaseOrderPaymentTerm,
                PurchaseOrderDiscountPercent,
                PurchaseOrderDiscountAmount,
                PurchaseOrderWarehouseType,
                PurchaseOrderWarehouseID,
                PurchaseOrderNote,
                PurchaseOrderSubTotal,
                PurchaseOrderTaxAmountPph,
                PurchaseOrderTaxAmountPpn,
                PurchaseOrderGrandTotal,
                PurchaseOrderStatus,
                PurchaseOrderIsActive,
                PurchaseOrderUserID,
                PurchaseOrderCreated,
                PurchaseOrderLastUpdated,
                PurchaseOrderShippingCost,
                PurchaseOrderShippingCostStatus
            ) VALUES (
                ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW(), ?, ?
            )";

            $exec = $this->db->query($sqlPurchaseOrder, [
                $numpd,
                $payload['ItemCategoryID'],
                $payload['Date'],
                $payload['RefNumber'],
                $payload['SupplierID'],
                $regId,
                $payload['TaxPpnType'],
                $payload['TaxPercentPph'],
                $payload['TaxPercentPpn'],
                $payload['PaymentTerm'],
                $payload['DiscountPercent'],
                $payload['DiscountAmount'],
                $payload['WarehouseType'],
                $payload['WarehouseID'],
                $payload['Note'],
                $payload['SubTotal'],
                $payload['TaxAmountPph'],
                $payload['TaxAmountPpn'],
                $payload['GrandTotal'],
                'Draft',
                'Y',
                $userId,
                $payload['ShippingCost'],
                $payload['ShippingCostStatus'] ? 'Y' : 'N'
            ]);


            if (!$exec) {
                $this->sys_error_db("purchase_order insert error", $this->db);
                $this->db->trans_rollback();
                exit;
            }
            $purchaseOrderId = $this->db->insert_id(); // Mendapatkan ID terakhir

            // Step 2: Insert ke tabel purchase_order_summary
            foreach ($payload['Summary'] as $summary) {
                $discountAmount = 0;
                if ($summary['discountType'] == 'R') $discountAmount = doubleval($summary['discount']);
                if ($summary['discountType'] == 'P') $discountAmount = (doubleval($summary['discount']) / 100) *  doubleval($summary['RealPrice']);
                $sqlSummary = "INSERT INTO purchase_order_summary (
                                    PurchaseOrderSummaryPurchaseOrderID,
                                    PurchaseOrderSummaryItemID,
                                    PurchaseOrderSummaryItemUnitID,
                                    PurchaseOrderSummaryQty,
                                    PurchaseOrderSummaryPrice,
                                    PurchaseOrderSummaryTotal,
                                    PurchaseOrderSummaryIsActive,
                                    PurchaseOrderSummaryCreatedUserID,
                                    PurchaseOrderSummaryCreated,
                                    PurchaseOrderSummaryDiscountRupiah,
                                    PurchaseOrderSummaryDiscountPercent,
                                    PurchaseOrderSummaryDiscountAmount,
                                    PurchaseOrderSummaryDiscountType                                    
                                ) VALUES (?,?,?,?,?,?,'Y',?,NOW(), ?,?,?,?)";

                $exec = $this->db->query($sqlSummary, [
                    $purchaseOrderId,
                    $summary['M_ItemID'],
                    $summary['PoItemUnitID'],
                    $summary['TotalPoQty'],
                    $summary['RealPrice'],
                    $summary['Total'],
                    $userId,
                    $summary['discountType'] == 'R' ? $summary['discount'] : 0,
                    $summary['discountType'] == 'P' ? $summary['discount'] : 0,
                    $discountAmount,
                    $summary['discountType']
                ]);


                if (!$exec) {
                    $this->sys_error_db("purchase_order_summary insert error", $this->db);
                    $this->db->trans_rollback();
                    exit;
                }
                $summaryId = $this->db->insert_id();

                // Step 3: Insert ke tabel purchase_order_detail
                foreach ($summary['Details'] as $detail) {

                    $qtyMode = $detail['qty_mode'];

                    // Ambil snapshot PR detail
                    $prSnapshot  = $this->getPurchaseRequestDetailSnapshot(
                        $detail['PurchaseRequestDetailID']
                    );

                    // validasi qty
                    $this->validatePoQty(
                        (int) $detail['PurchaseRequestFlagID'],
                        (float) $detail['PoQty'],
                        $qtyMode,
                        $detail['M_ItemCode'],
                        $detail['M_ItemDesc']
                    );

                    // LOG
                    $this->handlePurchaseRequestImpact(
                        $detail,
                        $prSnapshot,
                        $purchaseOrderId,
                        $userId
                    );

                    // UPDATE PR (JIKA EDIT)
                    if ($qtyMode === 'EDIT_PR') {
                        $this->updatePrQty(
                            $detail['PurchaseRequestDetailID'],
                            $detail['PoQty'],
                            $detail['RequestQty'],
                            $detail['Total'],
                            $userId
                        );
                    }

                    if ($this->isPriceChanged($prSnapshot, $detail)) {
                        $this->updatePrPrice(
                            $detail['PurchaseRequestDetailID'],
                            $detail['Price'],
                            $detail['Total'],
                            $userId
                        );
                    }

                    // UPDATE FLAG
                    if ($qtyMode === 'SPLIT_PR') {
                        $this->updateFlagQtyProses(
                            $detail['PurchaseRequestFlagID'],
                            $detail['PoQty']
                        );
                    } else {
                        $this->syncFlagAfterEdit(
                            $detail['PurchaseRequestFlagID'],
                            $detail['PurchaseRequestDetailID'],
                            $detail['PoQty'],
                            $detail['RequestQty']
                        );
                    }

                    // INSERT PO DETAIL
                    $this->insertPoDetail(
                        $purchaseOrderId,
                        $summaryId,
                        $detail,
                        $userId
                    );
                }
            }

            $poAfter = $this->getDataPo($purchaseOrderId);

            $this->insertLog(
                $purchaseOrderId,
                "ADD",
                $payload,
                [],
                $poAfter,
                $userId,
                "Add purchase order {$numpd}"
            );

            // notification
            // $this->saveNotification($userId, $payload['Date'], $numpd);

            // Commit transaksi jika semua berhasil
            $this->db->trans_commit();

            $result = array(
                "total" => 1,
                "records" => array("PurchaseOrderID" => $purchaseOrderId)
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $this->db->trans_rollback(); // Rollback jika ada kesalahan
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    private function updateFlagQtyProses(int $flagId, float $qty)
    {
        $this->db->query(
            "UPDATE purchase_request_flag
         SET PurchaseRequestFlagQtyProses = PurchaseRequestFlagQtyProses + ?
         WHERE PurchaseRequestFlagID = ?",
            [$qty, $flagId]
        );
    }

    private function syncFlagAfterEdit(int $flagId, int $prDetailId, float $qty, float $RequestQty)
    {
        $sql_get = "SELECT count(*)  as total
            FROM acc_one_log.purchase_request_detail_log
            WHERE PurchaseRequestDetailLogType = 'SPLIT_TO_PO'
            AND PurchaseRequestDetailLogIsActive = 'Y'
            AND PurchaseRequestDetailLogPurchaseRequestDetailID = ?";
        $qry_get = $this->db->query($sql_get, [$prDetailId]);
        if (!$qry_get) {
            $this->sys_error_db("get purchase_request_detail_log error", $this->db);
            exit;
        }

        $total = $qry_get->result_array()[0]['total'];

        if (intval($total) > 0) {

            // jika update qty lebih besar dari request
            if ($qty > $RequestQty) {
                $qtyadd = $qty - $RequestQty;

                $this->db->query(
                    "UPDATE purchase_request_flag
                SET PurchaseRequestFlagQty = PurchaseRequestFlagQty + ?,
                    PurchaseRequestFlagQtyRest = PurchaseRequestFlagQtyRest + ?,
                    PurchaseRequestFlagQtyProses = PurchaseRequestFlagQtyProses + ?
                WHERE PurchaseRequestFlagID = ?",
                    [$qtyadd, $qtyadd, $qty, $flagId]
                );
            } else {
                // jika update qty lebih kecil dari request
                $qtyadd = $RequestQty - $qty;

                $this->db->query(
                    "UPDATE purchase_request_flag
                SET PurchaseRequestFlagQty = PurchaseRequestFlagQty + ?,
                    PurchaseRequestFlagQtyRest = PurchaseRequestFlagQtyRest + ?,
                    PurchaseRequestFlagQtyProses = PurchaseRequestFlagQtyProses + ?
                WHERE PurchaseRequestFlagID = ?",
                    [$qtyadd, $qtyadd, $qty, $flagId]
                );
            }
        } else {
            $this->db->query(
                "UPDATE purchase_request_flag
            SET PurchaseRequestFlagQty = ?,
                PurchaseRequestFlagQtyRest = ?,
                PurchaseRequestFlagQtyProses = ?
            WHERE PurchaseRequestFlagID = ?",
                [$qty, $qty, $qty, $flagId]
            );
        }
    }

    private function insertPoDetail(
        int $poId,
        int $summaryId,
        array $detail,
        int $userId
    ) {

        $sql = "INSERT INTO purchase_order_detail (
            PurchaseOrderDetailPurchaseOrderID,
            PurchaseOrderDetailPurchaseSummaryID,
            PurchaseOrderDetailPurchaseRequestID,
            PurchaseOrderDetailPurchaseRequestDetailID,
            PurchaseOrderDetailPurchaseRequestFlagID,
            PurchaseOrderDetailItemID,
            PurchaseOrderDetailItemUnitID,
            PurchaseOrderDetailFlagQty,
            PurchaseOrderDetailRequestQty,
            PurchaseOrderDetailQty,
            PurchaseOrderDetailPrice,
            PurchaseOrderDetailTotal,
            PurchaseOrderDetailWarehouseID,
            PurchaseOrderDetailIsActive,
            PurchaseOrderDetailUserID,
            PurchaseOrderDetailCreatedUserID,
            PurchaseOrderDetailCreated,
            PurchaseOrderDetailLastUpdated
        ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?, 'Y',?,?,NOW(),NOW())";

        $exec =  $this->db->query($sql, [
            $poId,
            $summaryId,
            $detail['PurchaseRequestID'],
            $detail['PurchaseRequestDetailID'],
            $detail['PurchaseRequestFlagID'],
            $detail['M_ItemID'],
            $detail['PoItemUnitID'],
            $detail['qty_mode'],
            $detail['RequestQty'],
            $detail['PoQty'],
            $detail['Price'],
            $detail['Total'],
            $detail['warehouse']['WarehouseID'],
            $userId,
            $userId
        ]);

        if (!$exec) {
            $this->sys_error_db("purchase_order_detail insert error", $this->db);
            $this->db->trans_rollback();
            exit;
        }
    }

    private function validatePoQty(
        int $prFlagId,
        float $poQty,
        string $qtyMode,
        string $itemCode,
        string $itemDesc
    ) {
        $row = $this->db->query(
            "SELECT PurchaseRequestFlagQty, PurchaseRequestFlagQtyProses
         FROM purchase_request_flag
         WHERE PurchaseRequestFlagID = ?
         AND PurchaseRequestFlagIsActive = 'Y'",
            [$prFlagId]
        )->row_array();

        if (!$row) {
            $this->sys_error("PR Flag tidak ditemukan");
            exit;
        }

        $prQty     = (float) $row['PurchaseRequestFlagQty'];
        $qtyProses = (float) $row['PurchaseRequestFlagQtyProses'];
        $sisa      = $prQty - $qtyProses;

        if ($qtyMode === 'SPLIT_PR' && $poQty > $sisa) {
            $this->sys_error("Qty split melebihi sisa PR untuk item {$itemCode} - {$itemDesc} ");
            exit;
        }

        if ($qtyMode === 'EDIT_PR' && $poQty <= 0) {
            $this->sys_error("Qty edit tidak valid");
            exit;
        }
    }

    private function handlePurchaseRequestImpact(
        array $detail,
        array $pr,
        int $purchaseOrderId,
        int $userId
    ) {
        $mode = $detail['qty_mode'];

        /**
         * 1. SPLIT PO
         */
        if ($mode === 'SPLIT_PR') {
            $this->logSplitPo($detail, $purchaseOrderId, $userId);
            return;
        }

        /**
         * 2. EDIT QTY
         */
        if (
            $mode === 'EDIT_PR'
            && $this->isQtyChanged($pr, $detail)
        ) {
            $this->logQtyChange(
                $pr,
                $detail,
                $purchaseOrderId,
                $userId
            );
        }

        /**
         * 3. EDIT PRICE
         */
        if ($this->isPriceChanged($pr, $detail)) {
            $this->logPriceChange(
                $pr,
                $detail,
                $purchaseOrderId,
                $userId
            );
        }
    }

    private function getPurchaseRequestDetailSnapshot(int $prDetailId): array
    {
        $sql = "SELECT 
                PurchaseRequestDetailQty,
                PurchaseRequestDetailPrice
            FROM purchase_request_detail
            WHERE PurchaseRequestDetailID = ?
            AND PurchaseRequestDetailIsActive = 'Y'";

        $qry = $this->db->query($sql, [$prDetailId]);

        if (!$qry || !$qry->row_array()) {
            $this->sys_error("PR Detail tidak ditemukan");
            $this->db->trans_rollback();
            exit;
        }

        return $qry->row_array();
    }

    private function isQtyChanged(array $pr, array $detail): bool
    {
        return floatval($pr['PurchaseRequestDetailQty']) !== floatval($detail['PoQty']);
    }

    private function isPriceChanged(array $pr, array $detail): bool
    {
        return floatval($pr['PurchaseRequestDetailPrice']) !== floatval($detail['Price']);
    }

    private function updatePrQty(int $prDetailId, float $newQty, float $RequestQty, float $total, int $userid)
    {
        $sql_get = "SELECT count(*)  as total
            FROM acc_one_log.purchase_request_detail_log
            WHERE PurchaseRequestDetailLogType = 'SPLIT_TO_PO'
            AND PurchaseRequestDetailLogIsActive = 'Y'
            AND PurchaseRequestDetailLogPurchaseRequestDetailID = ?";
        $qry_get = $this->db->query($sql_get, [$prDetailId]);
        if (!$qry_get) {
            $this->sys_error_db("get purchase_request_detail_log error", $this->db);
            exit;
        }

        $totalrow = $qry_get->result_array()[0]['total'];

        if (intval($totalrow) > 0) {
            // jika update qty lebih besar dari request
            if ($newQty > $RequestQty) {
                $qtyadd = $newQty - $RequestQty;

                $sql = "UPDATE purchase_request_detail
                    SET PurchaseRequestDetailQty = PurchaseRequestDetailQty + ?,
                        PurchaseRequestDetailTotal = ?,
                        PurchaseRequestDetailLastUpdated = NOW(),
                        PurchaseRequestDetailLastUpdatedUserID = ?
                    WHERE PurchaseRequestDetailID = ?";

                $this->db->query($sql, [$qtyadd, $total, $userid, $prDetailId]);
            } else {
                // jika update qty lebih kecil dari request
                $qtyadd = $RequestQty - $newQty;

                $sql = "UPDATE purchase_request_detail
                    SET PurchaseRequestDetailQty = PurchaseRequestDetailQty + ?,
                        PurchaseRequestDetailTotal = ?,
                        PurchaseRequestDetailLastUpdated = NOW(),
                        PurchaseRequestDetailLastUpdatedUserID = ?
                    WHERE PurchaseRequestDetailID = ?";

                $this->db->query($sql, [$qtyadd, $total, $userid, $prDetailId]);
            }
        } else {
            $sql = "UPDATE purchase_request_detail
                SET PurchaseRequestDetailQty = ?,
                    PurchaseRequestDetailTotal = ?,
                    PurchaseRequestDetailLastUpdated = NOW(),
                    PurchaseRequestDetailLastUpdatedUserID = ?
                WHERE PurchaseRequestDetailID = ?";

            $this->db->query($sql, [$newQty, $total, $userid, $prDetailId]);
        }
    }

    private function updatePrPrice(int $prDetailId, float $newPrice, float $total, int $userid)
    {
        $sql = "UPDATE purchase_request_detail
            SET PurchaseRequestDetailPrice = ?,
                PurchaseRequestDetailTotal = ?,
                PurchaseRequestDetailLastUpdated = NOW(),
                PurchaseRequestDetailLastUpdatedUserID = ?
            WHERE PurchaseRequestDetailID = ?";

        $this->db->query($sql, [$newPrice, $total, $userid, $prDetailId]);
    }

    private function logSplitPo(array $detail, int $poId, int $userId)
    {
        $this->insertPurchaseRequestDetailLog([
            'pr_detail_id' => $detail['PurchaseRequestDetailID'],
            'pr_id'        => $detail['PurchaseRequestID'],
            'po_id'        => $poId,
            'type'         => 'SPLIT_TO_PO',
            'new_qty'      => $detail['PoQty'],
            'new_price'    => $detail['Price'],
            'user_id'      => $userId
        ]);
    }

    private function logQtyChange(
        array $pr,
        array $detail,
        int $poId,
        int $userId
    ) {
        $this->insertPurchaseRequestDetailLog([
            'pr_detail_id' => $detail['PurchaseRequestDetailID'],
            'pr_id'        => $detail['PurchaseRequestID'],
            'po_id'        => $poId,
            'type'         => 'QTY_CHANGE',
            'old_qty'      => $pr['PurchaseRequestDetailQty'],
            'new_qty'      => $detail['PoQty'],
            'user_id'      => $userId
        ]);
    }

    private function logPriceChange(
        array $pr,
        array $detail,
        int $poId,
        int $userId
    ) {
        $this->insertPurchaseRequestDetailLog([
            'pr_detail_id' => $detail['PurchaseRequestDetailID'],
            'pr_id'        => $detail['PurchaseRequestID'],
            'po_id'        => $poId,
            'type'         => 'PRICE_CHANGE',
            'old_price'    => $pr['PurchaseRequestDetailPrice'],
            'new_price'    => $detail['Price'],
            'user_id'      => $userId
        ]);
    }

    private function insertPurchaseRequestDetailLog(array $data)
    {
        $sql = "INSERT INTO acc_one_log.purchase_request_detail_log (
        PurchaseRequestDetailLogPurchaseRequestDetailID,
        PurchaseRequestDetailLogPurchaseRequestID,
        PurchaseRequestDetailLogPurchaseOrderID,
        PurchaseRequestDetailLogType,
        PurchaseRequestDetailLogOldQty,
        PurchaseRequestDetailLogNewQty,
        PurchaseRequestDetailLogOldPrice,
        PurchaseRequestDetailLogNewPrice,
        PurchaseRequestDetailLogCreated,
        PurchaseRequestDetailLogUserID
    ) VALUES (?,?,?,?,?,?,?,?,NOW(),?)";

        $params = [
            $data['pr_detail_id'],
            $data['pr_id'],
            $data['po_id'],
            $data['type'],
            $data['old_qty']   ?? null,
            $data['new_qty']   ?? null,
            $data['old_price'] ?? null,
            $data['new_price'] ?? null,
            $data['user_id']
        ];

        if (!$this->db->query($sql, $params)) {
            $this->sys_error_db("Gagal insert purchase_request_detail_log", $this->db);
            $this->db->trans_rollback();
            exit;
        }
    }

    function deleteOrder()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
            }

            $this->db->trans_begin(); // Start transaction

            $payload = $this->sys_input;
            $userId = $this->sys_user["M_UserID"];
            $purchaseOrderId = $payload["PurchaseOrderId"];

            // Get PO data before deletion for logging
            $poBefore = $this->getDataPo($purchaseOrderId);

            // Check if PO exists and is active
            $sqlCheckPO = "SELECT * FROM purchase_order 
                        WHERE PurchaseOrderID = ? 
                        AND PurchaseOrderIsActive = 'Y'";
            $execCheck = $this->db->query($sqlCheckPO, [$purchaseOrderId]);

            if (!$execCheck || $execCheck->num_rows() == 0) {
                $this->sys_error("Purchase order not found or already deleted");
                $this->db->trans_rollback();
                exit;
            }

            $po = $execCheck->row_array();

            // Only allow deletion of POs in 'Draft' status
            if ($po['PurchaseOrderStatus'] != 'Draft') {
                $this->sys_error("Cannot delete purchase order with status '{$po['PurchaseOrderStatus']}'. Only Draft orders can be deleted.");
                $this->db->trans_rollback();
                exit;
            }

            // Step 1: Get all purchase_order_detail records to update purchase_request_flag
            $sqlDetails = "SELECT * FROM purchase_order_detail 
                        LEFT JOIN purchase_request_detail
                        ON PurchaseOrderDetailPurchaseRequestDetailID = PurchaseRequestDetailID
                        WHERE PurchaseOrderDetailPurchaseOrderID = ? 
                        AND PurchaseOrderDetailIsActive = 'Y'";
            $execDetails = $this->db->query($sqlDetails, [$purchaseOrderId]);

            if (!$execDetails) {
                $this->sys_error_db("Error retrieving purchase order details", $this->db);
                $this->db->trans_rollback();
                exit;
            }

            $details = $execDetails->result_array();

            // Step 2: Update purchase_request_flag for each detail (decrement QtyProses)
            foreach ($details as $detail) {
                $sqlUpdateFlag = "UPDATE purchase_request_flag 
                                SET PurchaseRequestFlagQtyProses = PurchaseRequestFlagQtyProses - {$detail['PurchaseOrderDetailQty']} 
                                WHERE PurchaseRequestFlagID = {$detail['PurchaseOrderDetailPurchaseRequestFlagID']}";

                $execUpdateFlag = $this->db->query($sqlUpdateFlag);

                if (!$execUpdateFlag) {
                    $this->sys_error_db("Error updating purchase_request_flag", $this->db);
                    $this->db->trans_rollback();
                    exit;
                }
            }

            // Step 3: Soft delete purchase_order_detail
            $sqlDeleteDetail = "UPDATE purchase_order_detail 
                            SET PurchaseOrderDetailIsActive = 'N', 
                                PurchaseOrderDetailDeleted = NOW(),
                                PurchaseOrderDetailDeletedUserID = ? 
                            WHERE PurchaseOrderDetailPurchaseOrderID = ?";

            $execDeleteDetail = $this->db->query($sqlDeleteDetail, [$userId, $purchaseOrderId]);

            if (!$execDeleteDetail) {
                $this->sys_error_db("Error deleting purchase_order_detail", $this->db);
                $this->db->trans_rollback();
                exit;
            }

            // Step 4: Soft delete purchase_order_summary
            $sqlDeleteSummary = "UPDATE purchase_order_summary 
                                SET PurchaseOrderSummaryIsActive = 'N',
                                    PurchaseOrderSummaryDeleted = NOW(),
                                    PurchaseOrderSummaryDeletedUserID = ? 
                                WHERE PurchaseOrderSummaryPurchaseOrderID = ?";

            $execDeleteSummary = $this->db->query($sqlDeleteSummary, [$userId, $purchaseOrderId]);

            if (!$execDeleteSummary) {
                $this->sys_error_db("Error deleting purchase_order_summary", $this->db);
                $this->db->trans_rollback();
                exit;
            }

            // Step 5: Soft delete purchase_order
            $sqlDeletePO = "UPDATE purchase_order 
                        SET PurchaseOrderIsActive = 'N',
                            PurchaseOrderDeleted = NOW(),
                            PurchaseOrderDeletedUserID = ? 
                        WHERE PurchaseOrderID = ?";

            $execDeletePO = $this->db->query($sqlDeletePO, [$userId, $purchaseOrderId]);

            if (!$execDeletePO) {
                $this->sys_error_db("Error deleting purchase_order", $this->db);
                $this->db->trans_rollback();
                exit;
            }

            $sqlDeleteLog = "UPDATE acc_one_log.purchase_request_detail_log SET PurchaseRequestDetailLogIsActive = 'N'
                WHERE PurchaseRequestDetailLogPurchaseOrderID = ?";
            $qryDeleteLog = $this->db->query($sqlDeleteLog, [$purchaseOrderId]);
            if (!$qryDeleteLog) {
                $this->sys_error_db("Error deleting purchase_request_detail_log", $this->db);
                $this->db->trans_rollback();
                exit;
            }

            // Add log entry for the deletion
            $this->insertLog(
                $purchaseOrderId,
                "DELETE",
                $payload,
                $poBefore,
                [], // No after data since it's deleted
                $userId,
                "Delete purchase order {$po['PurchaseOrderNumber']}"
            );

            // Commit transaction if all operations succeed
            $this->db->trans_commit();

            $result = array(
                "success" => true,
                "message" => "Purchase order {$po['PurchaseOrderNumber']} has been deleted successfully"
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $this->db->trans_rollback(); // Rollback on any error
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function getItemUpdate()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
            }

            $payload = $this->sys_input;
            $regionalId = $this->sys_user["S_RegionalID"];

            $sql = "SELECT * FROM purchase_order 
                    WHERE PurchaseOrderID = ?";
            $qry = $this->db->query($sql, [$payload['POID']]);

            if (!$qry) {
                $this->db->trans_rollback();
                $this->sys_error_db("select PO error", $this->db);
                exit;
            }
            $PO = $qry->row_array();
            $sql = "SELECT
                    PurchaseOrderID,
                    PurchaseOrderSupplierID,
                    PurchaseOrderTaxPpnType as TaxPpnType,
                    PurchaseOrderSummaryID,
                    PurchaseOrderSummaryQty as TotalPoQty, 
                    PurchaseOrderSummaryPrice as SummaryPrice, 
                    PurchaseOrderSummaryTotal as SummaryTotal,
                    PurchaseOrderSummaryDiscountAmount summaryDiscountAmount,
                    PurchaseOrderSummaryDiscountType  as discountType,
                    CASE
                        WHEN PurchaseOrderSummaryDiscountType = 'R' THEN PurchaseOrderSummaryDiscountRupiah
                        WHEN  PurchaseOrderSummaryDiscountType = 'P' THEN PurchaseOrderSummaryDiscountPercent
                        ELSE 0
                    END as discount,

                    PurchaseOrderDetailID,
                    PurchaseOrderDetailPrice as Price,
                    PurchaseOrderDetailTotal as Total,
                    PurchaseOrderDetailQty as PoQty,

                    PurchaseRequestFlagID,
                    PurchaseRequestFlagM_BranchCode as BranchCode,
                    PurchaseRequestFlagQtyRest - PurchaseRequestFlagQtyProses as UnprocessFlagQty,
                    PurchaseRequestFlagQtyProses AS QtyProses,
                    PurchaseRequestFlagQty - PurchaseRequestFlagQtyProses AS RemainingQty,

                    PurchaseRequestDetailID,
                    PurchaseRequestDetailPurchaseRequestID,
                    PurchaseRequestDetailQty as RequestQty,
                    unitreq.ItemUnitID as ReqItemUnitID,
                    unitreq.ItemUnitName as ReqItemUnitName,

                    itemdtl.M_ItemID,
                    itemdtl.M_ItemCode,
                    itemdtl.M_ItemDesc,
                    unitdtl.ItemUnitID as PoItemUnitID,
                    unitdtl.ItemUnitName as PoItemUnitName,
                    
                    unitsum.ItemUnitID summaryItemUnitID,
                    unitsum.ItemUnitName SummaryItemUnitName,
                    itemsum.M_ItemID SummaryItemID,
                    itemsum.M_ItemCode SummaryItemCode,
                    itemsum.M_ItemDesc SummaryItemDesc,

                    reqbranch.M_BranchName as RequesterBranchName,
                    SupplierPriceID,

                    S_RegionalName,
                    S_RegionalID,
                    warehouseBranch.M_BranchName,
                    warehouseBranch.M_BranchID,
                    WarehouseID,
                    PurchaseRequestNumber,
                    CASE
                        WHEN warehouseBranch.M_BranchID IS NULL OR warehouseBranch.M_BranchID = 0 THEN CONCAT(WarehouseCode,' ', WarehouseName, ' - ', S_RegionalName)
                        WHEN warehouseBranch.M_BranchID IS NOT NULL OR warehouseBranch.M_BranchID <> 0 THEN CONCAT(WarehouseCode,' ', WarehouseName, ' - ', warehouseBranch.M_BranchName)
                    END as WarehouseName,
                    PurchaseOrderDetailFlagQty as qty_mode
                    FROM purchase_order
                    JOIN purchase_order_summary
                        ON PurchaseOrderID = PurchaseOrderSummaryPurchaseOrderID 
                        AND PurchaseOrderIsActive = 'Y'
                        AND PurchaseOrderSummaryPurchaseOrderID = ?
                    JOIN purchase_order_detail
                        ON PurchaseOrderID = PurchaseOrderDetailPurchaseOrderID
                        AND PurchaseOrderSummaryID = PurchaseOrderDetailPurchaseSummaryID
                        AND PurchaseOrderDetailIsActive = 'Y'
                    JOIN purchase_request_flag
                        ON PurchaseOrderDetailPurchaseRequestFlagID = PurchaseRequestFlagID
                    JOIN purchase_request_detail
                        ON PurchaseRequestFlagPurchaseRequestDetailID = PurchaseRequestDetailID
                        AND PurchaseRequestFlagStatus LIKE '%PO%'
                    LEFT JOIN purchase_request
                        ON PurchaseRequestDetailPurchaseRequestID = PurchaseRequestID
                    JOIN m_item itemsum
                        ON PurchaseOrderSummaryItemID=itemsum.M_ItemID
                    JOIN m_item itemdtl
                        ON PurchaseRequestDetailM_ItemID =itemdtl.M_ItemID
                    JOIN supplier_price
                        ON itemdtl.M_ItemID = SupplierPriceM_ItemID
                        AND PurchaseOrderSupplierID = SupplierPriceSupplierID
                        AND SupplierPriceIsActive = 'Y'
                        AND SupplierPriceItemUnitID = PurchaseOrderDetailItemUnitID
                    JOIN itemunit unitsum
                        ON PurchaseOrderSummaryItemUnitID = unitsum.ItemUnitID -- Unit Purchase
                    JOIN itemunit unitdtl
                        ON PurchaseOrderDetailItemUnitID = unitdtl.ItemUnitID -- Unit Purchase
                    JOIN itemunit unitreq
                        ON PurchaseRequestDetailItemUnitID = unitreq.ItemUnitID -- Unit Request
                    JOIN s_regional
                        ON PurchaseRequestFlagS_RegionalID = S_RegionalID AND S_RegionalIsActive = 'Y'
                    JOIN warehouse 
                        ON PurchaseOrderDetailWarehouseID = WarehouseID
                    LEFT JOIN m_branch warehouseBranch
                        ON WarehouseM_BranchID = warehouseBranch. M_BranchID
                        AND M_BranchIsActive = 'Y'
                    LEFT JOIN m_branch reqbranch
                        ON PurchaseRequestFlagM_BranchCode = reqbranch.M_BranchCode 
                        AND reqbranch.M_BranchIsActive = 'Y'
                    ORDER BY M_ItemDesc ASC";

            $qry = $this->db->query($sql, [$payload['POID']]);

            if (!$qry) {
                $this->db->trans_rollback();
                $this->sys_error_db("select items error", $this->db);
                exit;
            }

            $rows = $qry->result_array();
            $tmpSummaryID = [];
            $summary = [];

            $totalRealPrice = 0;

            /** 
             * A little Note:
             * data.records diambil dari $rows original hasil query ($row) 
             * data.summary diambil dari $rows dengan unique PurchaseOrderSummaryID 
             * (gabungan  dari $rows)
             */

            // Compose header data.summary
            foreach ($rows as $key => $row) {
                $rows[$key]['keyID'] = "{$row['M_ItemID']}_{$row['PoItemUnitID']}";

                $row['M_ItemID'] = $row['SummaryItemID'];
                $row['M_ItemCode'] = $row['SummaryItemCode'];
                $row['M_ItemDesc'] = $row['SummaryItemDesc'];

                $summaryprice = (float) $row['SummaryPrice'];

                $rows[$key]['RealPrice'] = ($summaryprice == floor($summaryprice)) ? (int) $summaryprice : $summaryprice;
                $row['Price'] = doubleval($row['SummaryPrice']) - doubleval($row['summaryDiscountAmount']);
                // $row['RealPrice'] = $row['SummaryPrice'];
                $row['RealPrice'] = ($summaryprice == floor($summaryprice)) ? (int) $summaryprice : $summaryprice;

                $row['Total'] = $row['SummaryTotal'];

                $rows[$key]['warehouse'] = array(
                    "WarehouseID" => $row['WarehouseID'],
                    "WarehouseName" => $row['WarehouseName'],
                );
                $rows[$key]['DefaultPurchase'] = $this->getDefaultPurchase($row['PurchaseOrderSupplierID'], $row['M_ItemID']);

                if (count($tmpSummaryID) == 0) {
                    $tmpSummaryID[] = $row['PurchaseOrderSummaryID'];
                    $summary[] = $row;
                } else {
                    if (!in_array($row['PurchaseOrderSummaryID'], $tmpSummaryID)) {
                        $tmpSummaryID[] = $row['PurchaseOrderSummaryID'];
                        $summary[] = $row;
                    }
                }
                $totalRealPrice += $row['SummaryPrice'] * $row['SummaryQty'];
            }

            // Compose data.summary.Detail
            foreach ($summary as $key => $sm) {
                $summary[$key]['keyID'] = "{$sm['M_ItemID']}_{$sm['PoItemUnitID']}";
                foreach ($rows as $key1 => $row) {
                    if ($row['PurchaseOrderSummaryID'] == $sm['PurchaseOrderSummaryID']) {
                        $summary[$key]['Details'][] = $row;
                    }
                }
            }

            // Sisipkan total real price ke $PO
            $PO['TotalRealPrice'] = $totalRealPrice;

            $result = array(
                "records" => $rows,
                "summary" => $summary,
                "po" => $PO,
                // "sql" => $this->db->last_query()
            );

            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function updateOrder()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
            }

            // Debugging
            // echo json_encode($this->sys_input, JSON_PRETTY_PRINT);
            // exit;

            $this->db->trans_begin();

            $payload = $this->sys_input;
            $userId = $this->sys_user["M_UserID"];
            $purchaseOrderId = $payload['PO']["PurchaseOrderID"];
            $isApprovalEdit = $payload["IsApprovalEdit"] ?? false;
            $updatedDetail = [];

            $sql = "SELECT * FROM purchase_order
                WHERE PurchaseOrderID = ?;";
            $exec = $this->db->query($sql, [$purchaseOrderId]);
            $updateMsg = [];

            $po = [];
            if (!$exec) {
                $this->db->trans_rollback();
                $this->sys_error_db("select purchase order error", $this->db);
                exit;
            }
            $po = $exec->row_array();

            if ($po['PurchaseOrderStatus'] != 'Draft' && !$isApprovalEdit) {
                $this->sys_error("Status Form {$po['PurchaseOrderStatus']} tidak dapat di ubah karena statusnya sudah bukan draft");
                exit;
            }
            if ($po['PurchaseOrderIsActive'] != 'Y') {
                $this->sys_error("{$po['PurchaseOrderStatus']} tidak dapat di ubah karena data sudah terhapus");
                exit;
            }

            $poBefore = $this->getDataPo($purchaseOrderId);

            $shippingCostStatus = $payload['ShippingCostStatus'] ? 'Y' : 'N';

            // Step 1: Update purchase_order
            $sqlUpdatePO = "UPDATE purchase_order 
                    SET 
                        PurchaseOrderDate = ?,
                        PurchaseOrderItemCategoryID = ?,
                        PurchaseOrderRefNumber = ?,
                        PurchaseOrderSupplierID = ?,
                        PurchaseOrderTaxPpnType = ?,
                        PurchaseOrderTaxPercentPph = ?,
                        PurchaseOrderTaxPercentPpn = ?,
                        PurchaseOrderPaymentTerm = ?,
                        PurchaseOrderDiscountPercent = ?,
                        PurchaseOrderDiscountAmount = ?,
                        PurchaseOrderWarehouseType = ?,
                        PurchaseOrderWarehouseID = ?,
                        PurchaseOrderNote = ?,
                        PurchaseOrderSubTotal = ?,
                        PurchaseOrderTaxAmountPph = ?,
                        PurchaseOrderTaxAmountPpn = ?,
                        PurchaseOrderGrandTotal = ?,
                        PurchaseOrderLastUpdated = NOW(),
                        PurchaseOrderUserID = ?,
                        PurchaseOrderShippingCost = ?,
                        PurchaseOrderShippingCostStatus = ?
                    WHERE PurchaseOrderID = ?
                    AND PurchaseOrderIsActive = 'Y'";

            // And modify the query execution:
            $exec = $this->db->query($sqlUpdatePO, [
                $payload['Date'],
                $payload['ItemCategoryID'],
                $payload['RefNumber'],
                $payload['SupplierID'],
                $payload['TaxPpnType'],
                $payload['TaxPercentPph'],
                $payload['TaxPercentPpn'],
                $payload['PaymentTerm'],
                $payload['DiscountPercent'],
                $payload['DiscountAmount'],
                $payload['WarehouseType'],
                $payload['WarehouseID'], // bisa null kalau warehouseType == single
                $payload['Note'],
                $payload['SubTotal'],
                $payload['TaxAmountPph'],
                $payload['TaxAmountPpn'],
                $payload['GrandTotal'],
                $userId,
                $payload['ShippingCost'],
                $shippingCostStatus,
                $purchaseOrderId
            ]);
            if (!$exec) {
                $this->db->trans_rollback();
                $this->sys_error_db("Error updating purchase_order", $this->db);
                return;
            }
            $updateMsg[] = "- Update data purchase order \n";

            // Step 2: Nonaktifkan semua purchase_order_summary dan purchase_order_detail
            $sqlDeactivateSummary = "UPDATE purchase_order_summary 
                SET PurchaseOrderSummaryIsActive = 'N' ,
                    PurchaseOrderSummaryDeletedUserID = ?,
                    PurchaseOrderSummaryDeleted = NOW()
                WHERE PurchaseOrderSummaryPurchaseOrderID = ?";

            $exec = $this->db->query($sqlDeactivateSummary, [$userId, $purchaseOrderId]);
            if (!$exec) {
                $this->db->trans_rollback();
                $this->sys_error_db("Error deactivating purchase_order_summary", $this->db);
                return;
            }

            $sqlDeactivateDetail = "UPDATE purchase_order_detail 
                SET PurchaseOrderDetailIsActive = 'N' ,
                    PurchaseOrderDetailLastUpdated = NOW()
                WHERE PurchaseOrderDetailPurchaseOrderID = ?";

            $exec = $this->db->query($sqlDeactivateDetail, [$purchaseOrderId]);
            if (!$exec) {
                $this->db->trans_rollback();
                $this->sys_error_db("Error deactivating purchase_order_detail", $this->db);
                return;
            }

            // Step 3: Insert/Update purchase_order_summary dan purchase_order_detail
            foreach ($payload['Summary'] as $summary) {
                // Insert baru untuk purchase_order_summary jika ada item tambahan saat edit
                if ($summary['PurchaseOrderID'] == 0 || $summary['PurchaseOrderSummaryID'] == 0) {
                    $discountAmount = 0;
                    if ($summary['discountType'] == 'R') $discountAmount = doubleval($summary['discount']);
                    if ($summary['discountType'] == 'P') $discountAmount = (doubleval($summary['discount']) / 100) *  doubleval($summary['RealPrice']);
                    $updateMsg[] =  "- Add data purchase order summary {$summary['M_ItemDesc']} {$summary['TotalPoQty']} {$summary['ItemUnitName']} \n";
                    $sqlInsertSummary = "INSERT INTO purchase_order_summary (
                        PurchaseOrderSummaryPurchaseOrderID,
                        PurchaseOrderSummaryItemID,
                        PurchaseOrderSummaryItemUnitID,
                        PurchaseOrderSummaryQty,
                        PurchaseOrderSummaryPrice,
                        PurchaseOrderSummaryTotal,
                        PurchaseOrderSummaryIsActive,
                        PurchaseOrderSummaryCreatedUserID,
                        PurchaseOrderSummaryCreated,
                        PurchaseOrderSummaryDiscountRupiah,
                        PurchaseOrderSummaryDiscountPercent,
                        PurchaseOrderSummaryDiscountAmount,
                        PurchaseOrderSummaryDiscountType
                    ) VALUES (?,?,?,?,?,?,'Y',?,NOW(),?,?,?,?)";

                    $exec = $this->db->query($sqlInsertSummary, [
                        $purchaseOrderId,
                        $summary['M_ItemID'],
                        $summary['PoItemUnitID'],
                        $summary['TotalPoQty'],
                        $summary['RealPrice'],
                        $summary['Total'],
                        $userId,
                        $summary['discountType'] == 'R' ? $summary['discount'] : 0,
                        $summary['discountType'] == 'P' ? $summary['discount'] : 0,
                        $discountAmount,
                        $summary['discountType']
                    ]);
                    if (!$exec) {
                        $this->db->trans_rollback();
                        $this->sys_error_db("Error inserting purchase_order_summary", $this->db);
                        return;
                    }

                    $summaryId = $this->db->insert_id();

                    // Insert baru untuk purchase_order_detail
                    foreach ($summary['Details'] as $detail) {

                        $qtyMode = $detail['qty_mode'];

                        // Ambil snapshot PR detail
                        $prSnapshot  = $this->getPurchaseRequestDetailSnapshot(
                            $detail['PurchaseRequestDetailID']
                        );

                        // LOG (TANPA UPDATE)
                        $this->handlePurchaseRequestImpactEdit(
                            $detail,
                            $prSnapshot,
                            $purchaseOrderId,
                            $userId
                        );

                        // Ambil data lama PO detail
                        $oldDetail = $this->db->query(
                            "SELECT PurchaseOrderDetailQty, PurchaseOrderDetailPurchaseRequestFlagID
                                FROM purchase_order_detail
                                WHERE PurchaseOrderDetailID = ?",
                            [$detail['PurchaseOrderDetailID']]
                        )->row();

                        // Lock PR Flag
                        $flag = $this->db->query(
                            "SELECT PurchaseRequestFlagQty, PurchaseRequestFlagQtyProses
                                FROM purchase_request_flag
                                WHERE PurchaseRequestFlagID = ?
                                FOR UPDATE",
                            [$oldDetail->PurchaseOrderDetailPurchaseRequestFlagID]
                        )->row();

                        // Hitung total PO qty
                        $totalPo = $this->db->query(
                            "SELECT SUM(PurchaseOrderDetailQty) AS total
                                FROM purchase_order_detail
                                WHERE PurchaseOrderDetailPurchaseRequestFlagID = ?
                                AND PurchaseOrderDetailIsActive = 'Y'",
                            [$oldDetail->PurchaseOrderDetailPurchaseRequestFlagID]
                        )->row()->total;

                        // validasi qty
                        $this->validatePoQtyEdit(
                            (float) $detail['PoQty'],
                            (float) $oldDetail->PurchaseOrderDetailQty,
                            (float) $totalPo,
                            (float) $flag->PurchaseRequestFlagQty,
                            (float) $flag->PurchaseRequestFlagQtyProses,
                            $detail['qty_mode'],
                            $detail['M_ItemCode'],
                            $detail['M_ItemDesc']
                        );

                        // UPDATE PR (JIKA EDIT)
                        if ($qtyMode === 'EDIT_PR') {
                            $this->updatePrQty(
                                $detail['PurchaseRequestDetailID'],
                                $detail['PoQty'],
                                $detail['RequestQty'],
                                $detail['Total'],
                                $userId
                            );
                        }

                        if ($this->isPriceChanged($prSnapshot, $detail)) {
                            $this->updatePrPrice(
                                $detail['PurchaseRequestDetailID'],
                                $detail['Price'],
                                $detail['Total'],
                                $userId
                            );
                        }

                        // UPDATE FLAG
                        if ($qtyMode === 'SPLIT_PR') {
                            $this->updateFlagQtyProsesEdit(
                                $detail['PurchaseRequestFlagID'],
                                $detail['PoQty'],
                                $detail['QtyProses']
                            );
                        } else {
                            $this->syncFlagAfterEditforUpdatePo(
                                $detail['PurchaseRequestFlagID'],
                                $detail['PurchaseRequestDetailID'],
                                $detail['PoQty'],
                                $detail['RequestQty']
                            );
                        }

                        $updateMsg[] =  "- Add data purchase order detail summary {$summary['M_ItemDesc']} {$detail['PoQty']} {$detail['ItemUnitName']} \n";

                        // INSERT PO DETAIL
                        $sqlInsertDetail = "INSERT INTO purchase_order_detail (
                            PurchaseOrderDetailPurchaseOrderID,
                            PurchaseOrderDetailPurchaseSummaryID,
                            PurchaseOrderDetailPurchaseRequestID,
                            PurchaseOrderDetailPurchaseRequestDetailID,
                            PurchaseOrderDetailPurchaseRequestFlagID,
                            PurchaseOrderDetailItemID,
                            PurchaseOrderDetailItemUnitID,
                            PurchaseOrderDetailFlagQty,
                            PurchaseOrderDetailRequestQty,
                            PurchaseOrderDetailQty,
                            PurchaseOrderDetailPrice,
                            PurchaseOrderDetailTotal,
                            PurchaseOrderDetailWarehouseID,
                            PurchaseOrderDetailIsActive,
                            PurchaseOrderDetailUserID,
                            PurchaseOrderDetailCreated,
                            PurchaseOrderDetailLastUpdated
                        ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NOW(),NOW())";

                        $exec = $this->db->query($sqlInsertDetail, [
                            $purchaseOrderId,
                            $summaryId,
                            $detail['PurchaseRequestID'],
                            $detail['PurchaseRequestDetailID'],
                            $detail['PurchaseRequestFlagID'],
                            $detail['M_ItemID'],
                            $detail['PoItemUnitID'],
                            $detail['qty_mode'],
                            $detail['RequestQty'],
                            $detail['PoQty'],
                            $detail['Price'],
                            $detail['Total'],
                            $detail['warehouse']['WarehouseID'],
                            'Y',
                            $userId,
                        ]);
                        if (!$exec) {
                            $this->db->trans_rollback();
                            $this->sys_error_db("Error inserting purchase_order_detail", $this->db);
                            return;
                        }
                        $sqlUpdateFlag = "UPDATE purchase_request_flag
                                    SET PurchaseRequestFlagQtyProses = PurchaseRequestFlagQtyProses + {$detail['PoQty']}
                                    WHERE PurchaseRequestFlagID = {$detail['PurchaseRequestFlagID']}";

                        $exec = $this->db->query($sqlUpdateFlag);

                        if (!$exec) {
                            $this->sys_error_db("purchase_request_flag update error", $this->db);
                            $this->db->trans_rollback();
                            exit;
                        }
                    }
                } else {
                    // Update purchase_order_summary untuk item yang diedit
                    $updateMsg[] =  "- Update data purchase order summary {$summary['M_ItemDesc']} {$summary['TotalPoQty']} {$summary['ItemUnitName']} \n";

                    $discountAmount = 0;
                    if ($summary['discountType'] == 'R') $discountAmount = doubleval($summary['discount']);
                    if ($summary['discountType'] == 'P') $discountAmount = (doubleval($summary['discount']) / 100) *  doubleval($summary['RealPrice']);
                    $sqlUpdateSummary = "UPDATE purchase_order_summary 
                    SET 
                        PurchaseOrderSummaryQty = ?,
                        PurchaseOrderSummaryPrice = ?,
                        PurchaseOrderSummaryTotal = ?,
                        PurchaseOrderSummaryIsActive = 'Y',
                        PurchaseOrderSummaryUpdated = NOW(),
                        PurchaseOrderSummaryUpdatedUserID = ?,
                        PurchaseOrderSummaryDiscountRupiah = ?,
                        PurchaseOrderSummaryDiscountPercent = ?,
                        PurchaseOrderSummaryDiscountAmount = ?,
                        PurchaseOrderSummaryDiscountType = ?
                    WHERE PurchaseOrderSummaryID = ?";

                    $exec = $this->db->query($sqlUpdateSummary, [
                        $summary['TotalPoQty'],
                        $summary['RealPrice'],
                        $summary['Total'],
                        $userId,
                        $summary['discountType'] == 'R' ? $summary['discount'] : 0,
                        $summary['discountType'] == 'P' ? $summary['discount'] : 0,
                        $discountAmount,
                        $summary['discountType'],
                        $summary['PurchaseOrderSummaryID'],
                    ]);
                    if (!$exec) {
                        $this->db->trans_rollback();
                        $this->sys_error_db("Error updating purchase_order_summary", $this->db);
                        return;
                    }
                    // Update purchase_order_detail
                    foreach ($summary['Details'] as $detail) {
                        if (
                            $detail['PurchaseOrderDetailID'] !== null && $detail['PurchaseOrderDetailID'] != 0
                            && $detail['PurchaseOrderDetailID'] != '0'
                        ) {
                            $updatedDetail[] = $detail['PurchaseOrderDetailID'];
                            // Update data yang sudah ada
                            $updateMsg[] =  "- Update data purchase order summary detail {$summary['M_ItemDesc']} {$detail['PoQty']} {$detail['ItemUnitName']} \n";

                            $qtyMode = $detail['qty_mode'];

                            // Ambil snapshot PR detail
                            $prSnapshot  = $this->getPurchaseRequestDetailSnapshot(
                                $detail['PurchaseRequestDetailID']
                            );

                            // LOG (TANPA UPDATE)
                            $this->handlePurchaseRequestImpactEdit(
                                $detail,
                                $prSnapshot,
                                $purchaseOrderId,
                                $userId
                            );

                            // Ambil data lama PO detail
                            $oldDetail = $this->db->query(
                                "SELECT PurchaseOrderDetailQty, PurchaseOrderDetailPurchaseRequestFlagID
                                FROM purchase_order_detail
                                WHERE PurchaseOrderDetailID = ?",
                                [$detail['PurchaseOrderDetailID']]
                            )->row();

                            // Lock PR Flag
                            $flag = $this->db->query(
                                "SELECT PurchaseRequestFlagQty, PurchaseRequestFlagQtyProses
                                FROM purchase_request_flag
                                WHERE PurchaseRequestFlagID = ?
                                FOR UPDATE",
                                [$oldDetail->PurchaseOrderDetailPurchaseRequestFlagID]
                            )->row();

                            // Hitung total PO qty
                            $totalPo = $this->db->query(
                                "SELECT SUM(PurchaseOrderDetailQty) AS total
                                FROM purchase_order_detail
                                WHERE PurchaseOrderDetailPurchaseRequestFlagID = ?
                                AND PurchaseOrderDetailIsActive = 'Y'",
                                [$oldDetail->PurchaseOrderDetailPurchaseRequestFlagID]
                            )->row()->total;

                            // validasi qty
                            $this->validatePoQtyEdit(
                                (float) $detail['PoQty'],
                                (float) $oldDetail->PurchaseOrderDetailQty,
                                (float) $totalPo,
                                (float) $flag->PurchaseRequestFlagQty,
                                (float) $flag->PurchaseRequestFlagQtyProses,
                                $detail['qty_mode'],
                                $detail['M_ItemCode'],
                                $detail['M_ItemDesc']
                            );

                            // UPDATE PR (JIKA EDIT)
                            if ($qtyMode === 'EDIT_PR') {
                                $this->updatePrQty(
                                    $detail['PurchaseRequestDetailID'],
                                    $detail['PoQty'],
                                    $detail['RequestQty'],
                                    $detail['Total'],
                                    $userId
                                );
                            }

                            if ($this->isPriceChanged($prSnapshot, $detail)) {
                                $this->updatePrPrice(
                                    $detail['PurchaseRequestDetailID'],
                                    $detail['Price'],
                                    $detail['Total'],
                                    $userId
                                );
                            }

                            // UPDATE FLAG
                            if ($qtyMode === 'SPLIT_PR') {
                                $this->updateFlagQtyProsesEdit(
                                    $detail['PurchaseRequestFlagID'],
                                    $detail['PoQty'],
                                    $detail['QtyProses']
                                );
                            } else {
                                $this->syncFlagAfterEditforUpdatePo(
                                    $detail['PurchaseRequestFlagID'],
                                    $detail['PurchaseRequestDetailID'],
                                    $detail['PoQty'],
                                    $detail['RequestQty']
                                );
                            }

                            $sqlUpdateDetail = "UPDATE purchase_order_detail 
                            SET 
                                PurchaseOrderDetailFlagQty = '{$detail['qty_mode']}',
                                PurchaseOrderDetailRequestQty = {$detail['RequestQty']},
                                PurchaseOrderDetailQty = {$detail['PoQty']},
                                PurchaseOrderDetailPrice = {$detail['Price']},
                                PurchaseOrderDetailTotal = {$detail['Total']},
                                PurchaseOrderDetailWarehouseID = {$detail['warehouse']['WarehouseID']},
                                PurchaseOrderDetailIsActive = 'Y',
                                PurchaseOrderDetailLastUpdated = NOW()
                            WHERE PurchaseOrderDetailID = {$detail['PurchaseOrderDetailID']} ";

                            $exec = $this->db->query($sqlUpdateDetail);
                            if (!$exec) {
                                $this->db->trans_rollback();
                                $this->sys_error_db("Error updating purchase_order_detail", $this->db);
                                return;
                            }
                        } else {
                            $updateMsg[] =  "- Add data purchase order summary detail {$summary['M_ItemDesc']} {$detail['PoQty']} {$detail['ItemUnitName']} \n";

                            $qtyMode = $detail['qty_mode'];

                            // Ambil snapshot PR detail
                            $prSnapshot  = $this->getPurchaseRequestDetailSnapshot(
                                $detail['PurchaseRequestDetailID']
                            );

                            // LOG (TANPA UPDATE)
                            $this->handlePurchaseRequestImpactEdit(
                                $detail,
                                $prSnapshot,
                                $purchaseOrderId,
                                $userId
                            );

                            // Ambil data lama PO detail
                            $oldDetail = $this->db->query(
                                "SELECT PurchaseOrderDetailQty, PurchaseOrderDetailPurchaseRequestFlagID
                                FROM purchase_order_detail
                                WHERE PurchaseOrderDetailID = ?",
                                [$detail['PurchaseOrderDetailID']]
                            )->row();

                            // Lock PR Flag
                            $flag = $this->db->query(
                                "SELECT PurchaseRequestFlagQty, PurchaseRequestFlagQtyProses
                                FROM purchase_request_flag
                                WHERE PurchaseRequestFlagID = ?
                                FOR UPDATE",
                                [$oldDetail->PurchaseOrderDetailPurchaseRequestFlagID]
                            )->row();

                            // Hitung total PO qty
                            $totalPo = $this->db->query(
                                "SELECT SUM(PurchaseOrderDetailQty) AS total
                                FROM purchase_order_detail
                                WHERE PurchaseOrderDetailPurchaseRequestFlagID = ?
                                AND PurchaseOrderDetailIsActive = 'Y'",
                                [$oldDetail->PurchaseOrderDetailPurchaseRequestFlagID]
                            )->row()->total;

                            // validasi qty
                            $this->validatePoQtyEdit(
                                (float) $detail['PoQty'],
                                (float) $oldDetail->PurchaseOrderDetailQty,
                                (float) $totalPo,
                                (float) $flag->PurchaseRequestFlagQty,
                                (float) $flag->PurchaseRequestFlagQtyProses,
                                $detail['qty_mode'],
                                $detail['M_ItemCode'],
                                $detail['M_ItemDesc']
                            );

                            // UPDATE PR (JIKA EDIT)
                            if ($qtyMode === 'EDIT_PR') {
                                $this->updatePrQty(
                                    $detail['PurchaseRequestDetailID'],
                                    $detail['PoQty'],
                                    $detail['RequestQty'],
                                    $detail['Total'],
                                    $userId
                                );
                            }

                            if ($this->isPriceChanged($prSnapshot, $detail)) {
                                $this->updatePrPrice(
                                    $detail['PurchaseRequestDetailID'],
                                    $detail['Price'],
                                    $detail['Total'],
                                    $userId
                                );
                            }

                            // UPDATE FLAG
                            if ($qtyMode === 'SPLIT_PR') {
                                $this->updateFlagQtyProsesEdit(
                                    $detail['PurchaseRequestFlagID'],
                                    $detail['PoQty'],
                                    $detail['QtyProses']
                                );
                            } else {
                                $this->syncFlagAfterEditforUpdatePo(
                                    $detail['PurchaseRequestFlagID'],
                                    $detail['PurchaseRequestDetailID'],
                                    $detail['PoQty'],
                                    $detail['RequestQty']
                                );
                            }

                            // Insert data baru
                            $sqlInsertDetail = "INSERT INTO purchase_order_detail (
                                PurchaseOrderDetailPurchaseOrderID,
                                PurchaseOrderDetailPurchaseSummaryID,
                                PurchaseOrderDetailPurchaseRequestID,
                                PurchaseOrderDetailPurchaseRequestDetailID,
                                PurchaseOrderDetailPurchaseRequestFlagID,
                                PurchaseOrderDetailItemID,
                                PurchaseOrderDetailItemUnitID,
                                PurchaseOrderDetailFlagQty,
                                PurchaseOrderDetailRequestQty,
                                PurchaseOrderDetailQty,
                                PurchaseOrderDetailPrice,
                                PurchaseOrderDetailTotal,
                                PurchaseOrderDetailWarehouseID,
                                PurchaseOrderDetailIsActive,
                                PurchaseOrderDetailUserID,
                                PurchaseOrderDetailCreated,
                                PurchaseOrderDetailLastUpdated
                            ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,NOW(),NOW())";

                            $exec = $this->db->query($sqlInsertDetail, [
                                $purchaseOrderId,
                                $summary['PurchaseOrderSummaryID'],
                                $detail['PurchaseRequestID'],
                                $detail['PurchaseRequestDetailID'],
                                $detail['PurchaseRequestFlagID'],
                                $detail['M_ItemID'],
                                $detail['PoItemUnitID'],
                                $detail['qty_Mode'],
                                $detail['RequestQty'],
                                $detail['PoQty'],
                                $detail['Price'],
                                $detail['Total'],
                                $detail['warehouse']['WarehouseID'],
                                'Y',
                                $userId,
                            ]);
                            if (!$exec) {
                                $this->db->trans_rollback();
                                $this->sys_error_db("Error inserting purchase_order_detail", $this->db);
                                return;
                            }
                        }
                    }
                }
            }
            //cari dari deleted item yang benar benar di deleted mana
            $deletedID = [];
            foreach ($payload['deletedItem'] as $key => $deleted) {
                if (!in_array($deleted['PurchaseOrderDetailID'], $updatedDetail)) {
                    $deletedID[] = $deleted;
                    $updateMsg[] =  "- Delete data purchase order detail {$deleted['M_ItemDesc']} {$deleted['PoQty']} {$deleted['ItemUnitName']} \n";

                    $sqlUpdateFlag = "UPDATE purchase_request_flag
                                    SET PurchaseRequestFlagQtyProses = PurchaseRequestFlagQtyProses - {$deleted['PoQty']}
                                    WHERE PurchaseRequestFlagID = {$deleted['PurchaseRequestFlagID']}";

                    $exec = $this->db->query($sqlUpdateFlag);

                    if (!$exec) {
                        $this->sys_error_db("purchase_request_flag update error", $this->db);
                        $this->db->trans_rollback();
                        exit;
                    }
                }
            }
            $poAfter = $this->getDataPo($purchaseOrderId);

            $this->insertLog(
                $purchaseOrderId,
                "UPDATE",
                $payload,
                $poBefore,
                $poAfter,
                $userId,
                implode($updateMsg)
            );


            // Commit transaction jika semua berhasil
            if ($this->db->trans_status() === FALSE) {
                $this->db->trans_rollback();
                $this->sys_error_db("Error updating purchase order", $this->db);
            } else {
                $this->db->trans_commit();
                $this->sys_ok([
                    "Message" => "Success",
                    "Deleted" => $deletedID,
                ]);
            }
        } catch (Exception $exc) {
            $this->db->trans_rollback();
            $this->sys_error_db("Error updating purchase order", $exc);
        }
    }

    private function handlePurchaseRequestImpactEdit(
        array $detail,
        array $pr,
        int $purchaseOrderId,
        int $userId
    ) {
        $mode = $detail['qty_mode'];

        /**
         * 1. SPLIT PO
         */
        if ($mode === 'SPLIT_PR') {
            $this->logSplitPoEdit($detail, $purchaseOrderId, $userId);
            return;
        }

        /**
         * 2. EDIT QTY
         */
        if (
            $mode === 'EDIT_PR'
            && $this->isQtyChanged($pr, $detail)
        ) {
            $this->logQtyChange(
                $pr,
                $detail,
                $purchaseOrderId,
                $userId
            );
        }

        /**
         * 3. EDIT PRICE
         */
        if ($this->isPriceChanged($pr, $detail)) {
            $this->logPriceChange(
                $pr,
                $detail,
                $purchaseOrderId,
                $userId
            );
        }
    }

    private function logSplitPoEdit(array $detail, int $poId, int $userId)
    {
        $this->insertPurchaseRequestDetailLog([
            'pr_detail_id' => $detail['PurchaseRequestDetailID'],
            'pr_id'        => $detail['PurchaseRequestDetailPurchaseRequestID'],
            'po_id'        => $poId,
            'type'         => 'SPLIT_TO_PO',
            'old_qty'      => $detail['RemainingQty'],
            'new_qty'      => $detail['PoQty'],
            'old_price'    => $detail['SummaryPrice'],
            'new_price'    => $detail['Price'],
            'user_id'      => $userId
        ]);
    }

    private function validatePoQtyEdit(
        float $poQtyBaru,
        float $poQtyLama,
        float $totalPoQtySemua,
        float $requestQty,
        float $qtyProsesLama,
        string $qtyMode,
        string $itemCode,
        string $itemDesc
    ) {
        // Qty PO harus > 0
        if ($poQtyBaru <= 0) {
            $this->sys_error("Qty PO tidak valid untuk item {$itemCode} - {$itemDesc}");
            exit;
        }

        // Total PO lain (selain PO ini)
        $totalPoQtyLain = $totalPoQtySemua - $poQtyLama;

        // Total PO baru setelah edit
        $totalPoQtyBaru = $totalPoQtyLain + $poQtyBaru;

        // TOTAL PO tidak boleh > PR
        if ($totalPoQtyBaru > $requestQty) {
            $this->sys_error(
                "Total Qty PO ({$totalPoQtyBaru}) melebihi Qty PR ({$requestQty}) untuk item {$itemCode} - {$itemDesc}"
            );
            exit;
        }

        // Hitung delta
        $delta = $poQtyBaru - $poQtyLama;

        // Qty proses baru
        $qtyProsesBaru = $qtyProsesLama + $delta;

        // Qty proses tidak boleh minus
        if ($qtyProsesBaru < 0) {
            $this->sys_error(
                "Qty proses menjadi negatif untuk item {$itemCode} - {$itemDesc}"
            );
            exit;
        }

        // Qty proses tidak boleh > PR
        // if ($qtyProsesBaru > $requestQty) {
        //     $this->sys_error(
        //         "Qty proses melebihi Qty PR untuk item {$itemCode} - {$itemDesc}"
        //     );
        //     exit;
        // }

        // Mode safety (optional, untuk future)
        if (!in_array($qtyMode, ['SPLIT_PR', 'EDIT_PR'], true)) {
            $this->sys_error("Mode qty tidak dikenal");
            exit;
        }
    }

    private function updateFlagQtyProsesEdit(
        int $flagId,
        float $poQtyBaru,
        float $poQtyLama
    ) {
        // Selisih perubahan PO detail
        $delta = $poQtyBaru - $poQtyLama;

        if ($delta == 0) {
            return; // tidak ada perubahan
        }

        $this->db->query(
            "UPDATE purchase_request_flag
            SET PurchaseRequestFlagQtyProses = GREATEST(
                PurchaseRequestFlagQtyProses + ?, 0
            )
            WHERE PurchaseRequestFlagID = ?",
            [$delta, $flagId]
        );
    }

    private function syncFlagAfterEditforUpdatePo(int $flagId, int $prDetailId, float $qty, float $RequestQty)
    {
        $sql_get = "SELECT count(*)  as total
            FROM acc_one_log.purchase_request_detail_log
            WHERE PurchaseRequestDetailLogType = 'SPLIT_TO_PO'
            AND PurchaseRequestDetailLogIsActive = 'Y'
            AND PurchaseRequestDetailLogPurchaseRequestDetailID = ?";
        $qry_get = $this->db->query($sql_get, [$prDetailId]);
        if (!$qry_get) {
            $this->sys_error_db("get purchase_request_detail_log error", $this->db);
            exit;
        }

        $total = $qry_get->result_array()[0]['total'];

        if (intval($total) > 0) {

            // jika update qty lebih besar dari request
            if ($qty > $RequestQty) {
                $qtyadd = $qty - $RequestQty;

                $this->db->query(
                    "UPDATE purchase_request_flag
                SET PurchaseRequestFlagQty = PurchaseRequestFlagQty + ?,
                    PurchaseRequestFlagQtyRest = PurchaseRequestFlagQtyRest + ?,
                    PurchaseRequestFlagQtyProses = ?
                WHERE PurchaseRequestFlagID = ?",
                    [$qtyadd, $qtyadd, $qty, $flagId]
                );
            } else {
                // jika update qty lebih kecil dari request
                $qtyadd = $RequestQty - $qty;

                $this->db->query(
                    "UPDATE purchase_request_flag
                SET PurchaseRequestFlagQty = PurchaseRequestFlagQty + ?,
                    PurchaseRequestFlagQtyRest = PurchaseRequestFlagQtyRest + ?,
                    PurchaseRequestFlagQtyProses = ?
                WHERE PurchaseRequestFlagID = ?",
                    [$qtyadd, $qtyadd, $qty, $flagId]
                );
            }
        } else {
            $this->db->query(
                "UPDATE purchase_request_flag
            SET PurchaseRequestFlagQty = ?,
                PurchaseRequestFlagQtyRest = ?,
                PurchaseRequestFlagQtyProses = ?
            WHERE PurchaseRequestFlagID = ?",
                [$qty, $qty, $qty, $flagId]
            );
        }
    }

    function requestOrder()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            $this->db->trans_begin();

            $payload = $this->sys_input;
            $userId = $this->sys_user["M_UserID"];
            $poBefore = $this->getDataPo($payload["ID"]);
            $sql = "SELECT * FROM purchase_order
                WHERE PurchaseOrderID = ?;";
            $exec = $this->db->query($sql, [$payload["ID"]]);

            $po = [];
            if (!$exec) {
                $this->db->trans_rollback();
                $this->sys_error_db("select purchase order error", $this->db);
                exit;
            }
            $po = $exec->row_array();

            if ($po['PurchaseOrderStatus'] != 'Draft') {
                $this->sys_error("{$po['PurchaseOrderStatus']} tidak dapat di ubah karena statusnya sudah bukan draft");
                exit;
            }
            if ($po['PurchaseOrderIsActive'] != 'Y') {
                $this->sys_error("{$po['PurchaseOrderStatus']} tidak dapat di ubah karena data sudah terhapus");
                exit;
            }

            $sql = "UPDATE purchase_order SET
                    PurchaseOrderStatus = 'Pending',
                    PurchaseOrderLastUpdated = NOW(),
                    PurchaseOrderUserID = {$userId}
                    WHERE PurchaseOrderID = {$payload["ID"]}
                    AND PurchaseOrderIsActive = 'Y'";
            $exec = $this->db->query($sql, []);

            if (!$exec) {
                $this->db->trans_rollback();
                $this->sys_error_db("update purchase order status error", $this->db);
                exit;
            }

            $sqlDetail = "UPDATE purchase_order_detail SET
                         PurchaseOrderDetailLastUpdated = NOW(),
                         PurchaseOrderDetailUserID = {$userId}
                         WHERE PurchaseOrderDetailPurchaseOrderID = {$payload["ID"]}
                         AND PurchaseOrderDetailIsActive = 'Y'";
            $exec = $this->db->query($sqlDetail, []);

            if (!$exec) {
                $this->db->trans_rollback();
                $this->sys_error_db("update purchase order detail error", $this->db);
                exit;
            }
            $poAfter = $this->getDataPo($payload["ID"]);

            $this->insertLog($payload["ID"], "REQUEST PO", $payload, $poBefore, $poAfter, $userId);

            $this->db->trans_commit();

            $sql = "SELECT *, 
                    ROW_NUMBER() OVER(ORDER BY PurchaseOrderNumber) RowNumber 
                    FROM purchase_order 
                    WHERE PurchaseOrderIsActive = 'Y' 
                    AND PurchaseOrderID = {$payload["ID"]}";
            $exec = $this->db->query($sql, []);

            $row = [];
            if (!$exec) {
                $this->db->trans_rollback();
                $this->sys_error_db("select purchase order error", $this->db);
                exit;
            } else {
                $row = $exec->result_array();
            }

            $result = array("total" => 1, "records" => $row);
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function approvePOrder()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            $this->db->trans_begin();
            $payload = $this->sys_input;
            $userId = $this->sys_user["M_UserID"];
            $approvelevel = $payload['approvelevel'];

            if ($approvelevel == '1') {
                $sql = "UPDATE purchase_order SET
                    PurchaseOrderApprovedManagerUserID = ?,
                    PurchaseOrderLastUpdated = NOW()
                    WHERE PurchaseOrderID = ?
                    AND PurchaseOrderIsActive = 'Y'";
                $exec = $this->db->query($sql, [$userId, $payload['poID']]);
                if (!$exec) {
                    $this->db->trans_rollback();
                    $this->sys_error_db("[Error] update approved by manager user id");
                    exit;
                }
            } else if ($approvelevel == '2') {
                $sql = "UPDATE purchase_order SET
                        PurchaseOrderStatus = 'Approved',
                        PurchaseOrderLastUpdated = NOW(),
                        PurchaseOrderApprovedUserID = ?
                        WHERE PurchaseOrderID = ?
                        AND PurchaseOrderIsActive = 'Y'";
                $exec = $this->db->query($sql, [$userId, $payload['poID']]);
                if (!$exec) {
                    $this->db->trans_rollback();
                    $this->sys_error_db("[Error] update status PO ke approved");
                    exit;
                }

                $sqlDetail = "UPDATE purchase_order_detail SET
                            PurchaseOrderDetailLastUpdated = NOW(),
                            PurchaseOrderDetailLastUpdatedUserID = ?
                            WHERE PurchaseOrderDetailPurchaseOrderID = ?
                            AND PurchaseOrderDetailIsActive = 'Y'";
                $exec = $this->db->query($sqlDetail, [$userId, $payload['poID']]);
                if (!$exec) {
                    $this->db->trans_rollback();
                    $this->sys_error_db("[Error] update status PO Detail ke approved");
                    exit;
                }

                $sqlSummary = "UPDATE purchase_order_summary SET
                            PurchaseOrderSummaryUpdated = NOW(),
                            PurchaseOrderSummaryUpdatedUserID = ?
                            WHERE PurchaseOrderSummaryPurchaseOrderID = ?
                            AND PurchaseOrderSummaryIsActive = 'Y'";
                $exec = $this->db->query($sqlSummary, [$userId, $payload['poID']]);
                if (!$exec) {
                    $this->db->trans_rollback();
                    $this->sys_error_db("[Error] update status PO Detail ke approved");
                    exit;
                }
            }

            $this->db->trans_commit();
            $this->sys_ok("[Success] success update status PO to Approved");
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    /** 
     * Function untuk mengecek:
     * Apakah selectedItem dari FE boleh multiple gudang atau tidak
     * 
     * @param: array PurchaseRequestDetailID 
     * @return: boolean true jika boleh multiple, false jika tidak
     * Author: Mario
     * Date: 24 Juni 2025
     */
    function isValidMultipleWarehouse()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            $prm = $this->sys_input;
            if (!isset($prm['PurchaseRequestDetailIDs']) || empty($prm['PurchaseRequestDetailIDs'])) {
                $this->sys_error("PurchaseRequestDetailIDs tidak boleh kosong");
                exit;
            }
            $arrPrDetailID = $prm['PurchaseRequestDetailIDs'];

            $placeholders_1 = implode(',', array_fill(0, count($arrPrDetailID), '?'));

            $sqlPurchase = "SELECT 
                PurchaseRequestDetailID,
                PurchaseRequestNumber as PRNumber,
                PurchaseRequestDetailM_ItemID,
                PurchaseRequestDetailItemUnitID as RequestItemUnitID,
                PurchaseRequestDetailQty as RequestQty,
                ItemUnitMapItemUnitID as PurchaseItemUnitID,
                ItemUnitMapIsPurchase,
                M_ItemDesc as ItemName
                FROM purchase_request_detail
                JOIN purchase_request
                    ON PurchaseRequestID = PurchaseRequestDetailPurchaseRequestID
                LEFT JOIN itemunitmap
                    ON ItemUnitMapM_ItemID = PurchaseRequestDetailM_ItemID
                    AND ItemUnitMapItemUnitID = PurchaseRequestDetailItemUnitID
                    AND ItemUnitMapIsActive = 'Y'
                    AND ItemUnitMapIsPurchase = 'Y'
                LEFT JOIN m_item
                    ON M_ItemID = PurchaseRequestDetailM_ItemID
                WHERE PurchaseRequestDetailID IN ($placeholders_1)";
            $qryPurchase = $this->db->query($sqlPurchase, $arrPrDetailID);
            if (!$qryPurchase) {
                $this->sys_error_db("Gagal query sqlPurchase di isValidMultipleWarehouse()");
                exit;
            }

            $rstPurchases = $qryPurchase->result_array();

            if (!empty($rstPurchases)) {
                // Cek Apakah semua ItemUnitMapIsPurchase = 'Y'
                // Jika ada != 'Y' maka masukkan ke $nonPurchaseItemIDs
                $nonPurchaseItemIDs = array_reduce($rstPurchases, function ($result, $item) {
                    if ($item['ItemUnitMapIsPurchase'] !== 'Y') {
                        $result[] = $item['PurchaseRequestDetailID'];
                    }
                    return $result;
                }, []);

                // Jika semua ItemUnitMapIsPurchase = 'Y' maka return true boleh multiple
                if (empty($nonPurchaseItemIDs)) {
                    $this->sys_ok([
                        'status' => true,
                        'message' => 'Bisa multiple gudang karena semua Unit Item Request = Purchase'
                    ]);
                    exit;
                }

                // Jika tidak, maka lanjut query unitconvert untuk PrID yang ada di $nonPurchaseItemIDs
                $placeholders_2 = implode(',', array_fill(0, count($nonPurchaseItemIDs), '?'));

                $sqlConvert = "SELECT 
                    PurchaseRequestDetailID,
                    PurchaseRequestDetailM_ItemID,
                    PurchaseRequestDetailItemUnitID as RequestItemUnitID,
                    PurchaseRequestDetailQty as RequestQty,
                    PurchaseRequestNumber as PRNumber,
                    M_ItemDesc as ItemName,
                    ItemUnitMapItemUnitID as PurchaseItemUnitID,
                    ItemUnitMapIsPurchase,
                    UnitConvertAmount,
                    reqUnit.ItemUnitName as RequestItemUnitName,
                    purUnit.ItemUnitName as PurchaseItemUnitName
                    FROM purchase_request_detail
                    JOIN purchase_request
                        ON PurchaseRequestID = PurchaseRequestDetailPurchaseRequestID
                    JOIN m_item
                        ON M_ItemID = PurchaseRequestDetailM_ItemID
                    LEFT JOIN itemunitmap
                        ON ItemUnitMapM_ItemID = PurchaseRequestDetailM_ItemID
                        AND ItemUnitMapIsPurchase = 'Y'
                        AND ItemUnitMapIsActive = 'Y'
                    LEFT JOIN unitconvert
                        ON UnitConvertFromItemUnitID = ItemUnitMapItemUnitID
                        AND UnitConvertToItemUnitID = PurchaseRequestDetailItemUnitID
                        AND UnitConvertIsActive = 'Y'
                    LEFT JOIN itemunit reqUnit
                        ON reqUnit.ItemUnitID = PurchaseRequestDetailItemUnitID
                    LEFT JOIN itemunit purUnit
                        ON purUnit.ItemUnitID = ItemUnitMapItemUnitID
                    WHERE PurchaseRequestDetailID IN ($placeholders_2)";
                $qryConvert = $this->db->query($sqlConvert, $nonPurchaseItemIDs);

                if (!$qryConvert) {
                    $this->sys_error_db("Gagal query sqlConvert di isValidMultipleWarehouse()");
                    exit;
                }
                if ($qryConvert->num_rows() == 0) {
                    $this->sys_ok([
                        'status' => false,
                        'message' => "Tidak bisa Multiple Gudang karena tidak ada mapping Konversi Unit Item untuk PurchaseRequestDetailID: " . implode(', ', $nonPurchaseItemIDs)
                    ]);
                    exit;
                }

                $rstConvert = $qryConvert->result_array();

                foreach ($rstConvert as $cvt) {
                    if (!$cvt['UnitConvertAmount'] || $cvt['UnitConvertAmount'] == null) {
                        $this->sys_ok([
                            'status' => false,
                            'message' => "Tidak bisa Multiple Gudang karena tidak ada Konversi Unit dari Unit {$cvt['PurchaseItemUnitName']} (Purchase) ke Unit {$cvt['RequestItemUnitName']} (Request) untuk Item {$cvt['ItemName']} (PR Number: {$cvt['PRNumber']})"
                        ]);
                        exit;
                    }

                    if ($cvt['RequestQty'] % $cvt['UnitConvertAmount'] != 0) {
                        $this->sys_ok([
                            'status' => false,
                            'message' => "Tidak bisa Multiple Gudang. Jumlah Item Request: {$cvt['ItemName']} (PR Number: {$cvt['PRNumber']}) minimal {$cvt['UnitConvertAmount']} {$cvt['RequestItemUnitName']} per {$cvt['PurchaseItemUnitName']} atau kelipatannya. Request saat ini: {$cvt['RequestQty']} {$cvt['RequestItemUnitName']}.",
                        ]);
                        exit;
                    }
                };

                // Kalau semuanya habis dibagi maka return true bisa multiple
                $this->sys_ok([
                    'status' => true,
                    'message' => 'Bisa multiple gudang karena ada Unit Convert yang valid untuk semua Item Unit Request'
                ]);
                exit;
            } else {
                $this->sys_ok([
                    'status' => false,
                    'message' => 'Tidak bisa multiple karena tidak menemukan PurchaseRequestDetailID: ' . implode(', ', $arrPrDetailID)
                ]);
            }
        } catch (Exception $e) {
            $message = $e->getMessage();
            $this->sys_error($message);
        }
    }

    function getDefaultPurchase_old($itemID, $unitTo)
    {
        try {
            $sql = "SELECT 
                ItemUnitID,
                ItemUnitCode,
                ItemUnitName,
                ItemUnitMapIsPurchase,
                ItemUnitMapMin,
                ItemUnitMapM_ItemID ,
                1 as UnitConvertAmount,
                SupplierPricePrice,
                'Y' isPurchased
                    FROM itemunitmap
                    JOIN m_item
                    ON ItemUnitMapM_ItemID  = M_itemID
                    JOIN itemunit
                    ON ItemUnitMapItemUnitID  = ItemUnitID
                    JOIN supplier_price 
                    ON ItemUnitMapM_ItemID  = SupplierPriceM_ItemID
                    AND ItemUnitMapItemUnitID  = SupplierPriceItemUnitID
                    WHERE ItemUnitMapM_ItemID = ?
                    AND ItemUnitMapItemUnitID = ?
                    AND ItemUnitMapIsPurchase = 'Y'; ";
            $exec = $this->db->query($sql, [$itemID, $unitTo]);

            if (!$exec) {
                $this->sys_error_db("Error get default purchase", $this->db);
                exit;
            }
            $rowDefault = $exec->row_array();

            if (!empty($rowDefault)) {
                return $rowDefault;
            }


            $sql = "SELECT 
                    ItemUnitID,
                    ItemUnitCode,
                    ItemUnitName,
                    ItemUnitMapIsPurchase,
                    ItemUnitMapMin,
                    ItemUnitMapM_ItemID ,
                    UnitConvertAmount,
                    SupplierPricePrice,
                    'N' isPurchased
                    FROM itemunitmap
                    JOIN itemunit
                    ON ItemUnitMapItemUnitID = ItemUnitID
                    JOIN unitconvert
                    ON ItemUnitID = UnitConvertFromItemUnitID  
                    AND UnitConvertToItemUnitID  = ?
                    AND UnitConvertIsActive = 'Y'
                    JOIN supplier_price
                    ON UnitConvertFromItemUnitID = SupplierPriceItemUnitID
                    AND SupplierPriceM_ItemID = ? 
                    WHERE ItemUnitMapIsPurchase = 'Y'
                    AND ItemUnitMapIsActive = 'Y'
                    AND ItemUnitMapM_ItemID = ?";
            $exec = $this->db->query($sql, [$unitTo, $itemID, $itemID]);

            if (!$exec) {
                $this->sys_error_db("Error get default purchase", $this->db);
                exit;
            }
            $row = $exec->result_array();

            $retVal = new stdClass(); // Menggunakan object kosong
            if (count($row) > 0) {
                $retVal = $row[0];
            }
            return $retVal;
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function getDefaultPurchase($supplierID, $itemID)
    {
        try {
            $sql = "SELECT 
                ItemUnitID,
                ItemUnitCode,
                ItemUnitName,
                ItemUnitMapIsPurchase,
                ItemUnitMapMin,
                ItemUnitMapM_ItemID ,
                COALESCE(UnitConvertAmount, 1) as UnitConvertAmount,
                SupplierPricePrice,
                'Y' isPurchased
                FROM supplier_price
                JOIN m_item
                    ON M_ItemID  = SupplierPriceM_ItemID
                JOIN itemunitmap
                    ON ItemUnitMapM_ItemID = SupplierPriceM_ItemID
                    AND ItemUnitMapIsPurchase = 'Y'
                JOIN itemunit
                    ON ItemUnitID = ItemUnitMapItemUnitID
                LEFT JOIN unitconvert
                    -- Unit From pakai Unit Purchase 
                    ON UnitConvertFromItemUnitID = ItemUnitMapItemUnitID
                WHERE SupplierPriceSupplierID = ?
                AND SupplierPriceM_ItemID = ? ";
            $exec = $this->db->query($sql, [$supplierID, $itemID]);

            if (!$exec) {
                $this->sys_error_db("Error get default purchase", $this->db);
                exit;
            }
            $rowDefault = $exec->row_array();

            if (empty($rowDefault)) {
                return new stdClass(); // Return an empty object
            }

            return $rowDefault;
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function getDataPo($poID)
    {
        $sql = "SELECT * FROM purchase_order
                WHERE PurchaseOrderID = ?;";
        $exec = $this->db->query($sql, [$poID]);

        $po = [];
        if (!$exec) {
            $this->db->trans_rollback();
            $this->sys_error_db("select purchase order error", $this->db);
            exit;
        }
        $po = $exec->row_array();
        $sql = "SELECT * FROM purchase_order_summary
                WHERE PurchaseOrderSummaryPurchaseOrderID = ?
                AND PurchaseOrderSummaryIsActive = 'Y';";
        $exec = $this->db->query($sql, [$poID]);

        $pos = [];
        if (!$exec) {
            $this->db->trans_rollback();
            $this->sys_error_db("select purchase order summary error", $this->db);
            exit;
        }
        $pos = $exec->result_array();

        $sql = "SELECT * FROM purchase_order_detail
                WHERE PurchaseOrderDetailPurchaseOrderID = ?
                AND PurchaseOrderDetailIsActive = 'Y';";
        $exec = $this->db->query($sql, [$poID]);

        $pod = [];
        if (!$exec) {
            $this->db->trans_rollback();
            $this->sys_error_db("select purchase order summary error", $this->db);
            exit;
        }
        $pod = $exec->result_array();
        $po['Summary'] = $pos;
        $po['Details'] = $pod;
        return $po;
    }

    function getPoBySupplier()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            $prm = $this->sys_input;
            $supplierID = $prm["supplierID"];

            $sql = "SELECT po.*,
                CASE 
                    WHEN po.PurchaseOrderDiscountPercent = 0 THEN 'Absolute'
                    WHEN po.PurchaseOrderDiscountAmount = 0 THEN 'Percentage'
                        ELSE '' 
                    END AS DiscountType,
                CASE 
                    WHEN po.PurchaseOrderDiscountPercent <> 0 THEN (po.PurchaseOrderDiscountPercent / 100) * PurchaseOrderSubTotal
                    WHEN po.PurchaseOrderDiscountAmount <> 0 THEN PurchaseOrderDiscountAmount
                        ELSE '' 
                    END AS DiscountAmount,
                PurchaseOrderTaxAmountPph + PurchaseOrderTaxAmountPpn AS TaxAmount,
                DATE_FORMAT(PurchaseOrderDate, '%d %M %Y') AS DisplayDate,
                SupplierCode,
                SupplierName,
                WarehouseCode,
                IFNULL(itemCategoryID, '') as itemCategoryID,
                IFNULL(itemCategoryName, '') as itemCategoryName,
                CASE
                    WHEN WarehouseType = 'B' THEN CONCAT(WarehouseCode,' ', WarehouseName, ' - ', M_BranchName) 
                    WHEN WarehouseType = 'R' THEN CONCAT(WarehouseCode,' ', WarehouseName, ' - ', S_RegionalName) 
                    ELSE ''
                END as WarehouseName
                FROM purchase_order po
                JOIN supplier ON SupplierID = PurchaseOrderSupplierID 
                LEFT JOIN item_category ON PurchaseOrderItemCategoryID = itemCategoryID 
                LEFT JOIN warehouse ON WarehouseID = PurchaseOrderWarehouseID
                LEFT JOIN s_regional ON S_RegionalID = WarehouseS_RegionalID
                LEFT JOIN m_branch ON M_BranchID = WarehouseM_BranchID
                WHERE po.PurchaseOrderIsActive = 'Y'
                AND po.PurchaseOrderSupplierID = ?";

            $queryCount = "SELECT count(*) as total
                          FROM purchase_order po
                          WHERE PurchaseOrderIsActive = 'Y'
                          AND PurchaseOrderSupplierID = ?";

            $qryCount = $this->db->query($queryCount, [$supplierID]);

            $numberLimit = 20;
            $numberOffset = 0;
            if ($prm["currentPage"] > 0) {
                $numberOffset = ($prm["currentPage"] - 1) * $numberLimit;
            }


            if ($qryCount) {
                $totalCount = $qryCount->result_array()[0]["total"];
                $totalPage = ceil($totalCount / $numberLimit);
            } else {
                $this->db->trans_rollback();
                $this->sys_error_db("get listing count po by supplier error", $this->db);
                exit;
            }

            $sql .= " ORDER BY PurchaseOrderCreated DESC
                    LIMIT ? OFFSET ?";
            $exec = $this->db->query($sql, [
                $supplierID,
                $numberLimit,
                $numberOffset
            ]);
            if ($exec) {
                $rows = $exec->result_array();
            } else {
                $this->db->trans_rollback();
                $this->sys_error_db("select purchase order", $this->db);
                exit;
            }

            foreach ($rows as $key => $row) {
                $rows[$key]['TotalRealPrice'] = 0;
                $rows[$key]['TotalDiskonPerItem'] = 0;

                // Hitung Total Real Price
                $sql = "SELECT PurchaseOrderSummaryPurchaseOrderID, 
                        SUM(PurchaseOrderSummaryQty * PurchaseOrderSummaryPrice) AS TotalRealPrice,
                        SUM(PurchaseOrderSummaryQty * PurchaseOrderSummaryDiscountAmount) AS TotalDiskonPerItem
                        FROM purchase_order_summary
                        WHERE PurchaseOrderSummaryPurchaseOrderID = ?";
                $qry = $this->db->query($sql, [$row['PurchaseOrderID']]);
                if ($qry) {
                    $summaryRow = $qry->row_array();
                    $rows[$key]['TotalRealPrice'] = floatval($summaryRow['TotalRealPrice']);
                    $rows[$key]['TotalDiskonPerItem'] = floatval($summaryRow['TotalDiskonPerItem']);
                } else {
                    $rows[$key]['TotalRealPrice'] = 0;
                    $rows[$key]['TotalDiskonPerItem'] = 0;
                    $this->db->trans_rollback();
                    $this->sys_error_db("select purchase order", $this->db);
                    exit;
                }
            }

            $result = array(
                "totalPage" => $totalPage,
                "totalFilter" => $totalCount,
                "records" => $rows
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function insertLog($poID, $type, $prm, $dataBefore, $dataAfter, $userID, $desc = '')
    {
        // Query untuk menyisipkan log ke dalam tabel purchase_order_log
        $sql = "INSERT INTO acc_one_log.purchase_order_log (
                    PurchaseOrderLogPurchaseOrderID,
                    PurchaseOrderLogType,
                    PurchaseOrderLogPrm,
                    PurchaseOrderLogJSONBefore,
                    PurchaseOrderLogJSONAfter,
                    PurchaseOrderLogCreated,
                    PurchaseOrderLogUserID,
                    PurchaseOrderLogTypeDesc
                ) VALUES (
                    ?, ?, ?, ?, ?, NOW(), ?, ?
                )";

        // Eksekusi query dengan parameter yang diberikan
        $exec = $this->db->query($sql, [
            $poID,
            $type, // PurchaseOrderLogType
            json_encode($prm), // PurchaseOrderLogPurchaseOrderID
            json_encode($dataBefore), // PurchaseOrderLogJSONBefore
            json_encode($dataAfter), // PurchaseOrderLogJSONAfter
            $userID, // PurchaseOrderLogUserID
            $desc
        ]);

        // Jika query gagal, lempar error
        if (!$exec) {
            $this->sys_error_db("Error inserting log into purchase_order_log", $this->db);
            $this->db->trans_rollback();
            exit;
        }
    }

    function getApproveLevel()
    {
        if (!$this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }
        $userId = $this->sys_user["M_UserID"];

        $sqlevel = "SELECT M_UserM_ApproveLevelID FROM m_user WHERE M_UserIsActive = 'Y' AND M_UserID = ? ";
        $qulevel = $this->db->query($sqlevel, [$userId]);
        if (!$qulevel) {
            $this->sys_error_db("[Error] get approval level user");
            exit;
        }
        $data = $qulevel->row_array();

        $this->sys_ok($data);
    }

    // notifications
    function saveNotification($userId, $date, $number)
    {
        try {
            $this->db->trans_begin();

            $xdate = date('d-m-Y');

            $sql_user = "SELECT M_UserID, M_UserUsername
                FROM m_user
                WHERE M_UserIsActive = 'Y'
                AND M_UserID = ?";
            $qry_user = $this->db->query($sql_user, [$userId]);
            if ($qry_user) {
                $username = $qry_user->row_array();
            } else {
                $this->sys_error_db("select user error", $this->db);
                exit;
            }

            $description = "Telah dibuat <strong>PURCHASE ORDER</strong> pada tanggal <strong>" . $xdate . "</strong> oleh <strong>" . $username['M_UserUsername'] . "</strong> dengan nomor <strong>" . $number . "</strong>";

            $sql_menu = "SELECT S_MenuID,
                S_MenuName,
                S_MenuUrl,
                S_MenuLevel
                FROM s_menu
                WHERE S_MenuID = 45";
            $qry_menu = $this->db->query($sql_menu, []);
            if ($qry_menu) {
                $rowurl = $qry_menu->row_array();
            } else {
                $this->sys_error_db("select menu error", $this->db);
                exit;
            }

            $url = $rowurl['S_MenuUrl'] . "?date=" . $date . "&search=" . $number;

            $sql = "INSERT INTO notification(
                NotificationType,
                NotificationDescription,
                NotificationUrl,
                NotificationUserID,
                NotificationCreated) VALUES(?,?,?,?,NOW())";
            $qry = $this->db->query($sql, [
                'PO',
                $description,
                $url,
                $userId
            ]);
            if (!$qry) {
                $this->db->trans_rollback();
                $this->sys_error_db("insert notification error", $this->db);
                exit;
            }

            $lastid = $this->db->insert_id();

            $sqldetail = "INSERT INTO notification_detail(
                NotificationDetailNotificationID,
                NotificationDetailM_UserID,
                NotificationDetailStatus,
                NotificationDetailCreated) VALUES(?,?,?,NOW())";
            $qrydetail = $this->db->query($sqldetail, [
                $lastid,
                $userId,
                'unread'
            ]);
            if (!$qrydetail) {
                $this->db->trans_rollback();
                $this->sys_error_db("insert notification detail error", $this->db);
                exit;
            }

            $this->db->trans_commit();
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
}