isLogin) {
$this->sys_error("Invalid Token");
}
$payload = $this->sys_input;
$userId = $this->sys_user["M_UserID"];
$approveLevelID = $this->sys_user['M_UserM_ApproveLevelID'];
$flag = $payload['flag'];
if ($approveLevelID == '1') {
if ($flag == 'G') {
$sqlPurchaseOrder = " AND po.PurchaseOrderApprovedManagerUserID = 0";
}
} else if ($approveLevelID == '2') {
if ($flag == 'G') {
$sqlPurchaseOrder = " AND po.PurchaseOrderApprovedUserID IS NULL";
}
}
$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 = 1
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'
$sqlPurchaseOrder
AND (PurchaseOrderNumber LIKE '%" . $payload["search"] . "%')";
$queryCount = "SELECT count(*) as total
FROM purchase_order po
JOIN item_category ON PurchaseOrderItemCategoryID = itemCategoryID
AND itemCategoryID = 1
WHERE PurchaseOrderIsActive = 'Y'
$sqlPurchaseOrder
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 PurchaseOrderDate DESC, 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 = '1'";
$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
PurchaseRequestFlagID,
PurchaseRequestFlagM_BranchCode as BranchCode,
PurchaseRequestFlagQtyRest - PurchaseRequestFlagQtyProses as UnprocessFlagQty,
PurchaseRequestDetailID,
PurchaseRequestDetailPurchaseRequestID as PurchaseRequestID,
PurchaseRequestDetailQty as RequestQty,
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,
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
FROM purchase_request_flag
JOIN purchase_request_detail
ON PurchaseRequestFlagPurchaseRequestDetailID = PurchaseRequestDetailID
AND PurchaseRequestFlagStatus LIKE '%PO%'
AND PurchaseRequestFlagS_RegionalID = ?
AND PurchaseRequestFlagQtyRest > 0
JOIN purchase_request ON PurchaseRequestDetailPurchaseRequestID = PurchaseRequestID
JOIN supplier_price ON
PurchaseRequestDetailM_ItemID = SupplierPriceM_ItemID AND
SupplierPriceItemUnitID = PurchaseRequestDetailItemUnitID AND (
SupplierPriceFlagQty ='T' OR
(
(SupplierPriceMinInclusive = 'Y' AND PurchaseRequestDetailQty >= SupplierPriceMinQty) OR
(SupplierPriceMinInclusive = 'N' AND PurchaseRequestDetailQty > SupplierPriceMinQty)
) AND
(
(SupplierPriceMaxInclusive = 'Y' AND PurchaseRequestDetailQty <= SupplierPriceMaxQty) OR
(SupplierPriceMaxInclusive = 'N' AND PurchaseRequestDetailQty < SupplierPriceMaxQty))
)
AND SupplierPriceIsActive = 'Y'
AND SupplierPriceSupplierID = ?
JOIN m_item
ON PurchaseRequestDetailM_ItemID = M_ItemID
AND M_ItemDesc LIKE ?
AND M_ItemItem_CategoryID = {$itemCategoryID}
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'
WHERE ((PurchaseRequestFlagQtyRest - PurchaseRequestFlagQtyProses) > 0)
AND PurchaseRequestFlagIsActive = 'Y'
ORDER BY M_ItemDesc ASC, PurchaseRequestNumber DESC
LIMIT {$numberLimit} OFFSET {$numberOffset}";
$qry = $this->db->query($sql, [$payload['regionalId'], $supplierID, $search]);
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['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($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['PoQty'] = $row['RequestQty'];
$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['PoItemUnitID']}";
$row['DefaultPurchase'] = $this->getDefaultPurchase($supplierID, $row['M_ItemID'], $row['RequestQty']);
}
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"];
// $pdSql = "SELECT `fn_numbering`('PO') AS PO";
// $exec = $this->db->query($pdSql, []);
// $pd = "";
// if (!$exec) {
// $this->sys_error_db("purchase order insert error", $this->db);
// $this->db->trans_rollback();
// exit;
// } else {
// $pd = $exec->result_array()[0]["PO"];
// }
$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,
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['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) {
$sql = "SELECT * FROM purchase_request_flag
WHERE PurchaseRequestFlagID = {$detail['PurchaseRequestFlagID']}
AND PurchaseRequestFlagIsActive = 'Y'";
$qry = $this->db->query($sql);
if (!$qry) {
$this->sys_error_db("Error checking purchase_request_flag", $this->db);
$this->db->trans_rollback();
exit;
}
$row = $qry->row_array();
if (!$row) {
$this->sys_error("Data {$detail['M_ItemCode']} - {$detail['M_ItemDesc']} cabang {$detail['M_BranchName']} regional {$detail['S_RegionalName']} tidak ditemukan, silahkan refresh item");
$this->db->trans_rollback();
exit;
}
$qtyRest = floatval($row['PurchaseRequestFlagQtyRest']);
$qtyProses = floatval($row['PurchaseRequestFlagQtyProses']);
if (($qtyRest - $qtyProses) <= 0) {
$this->sys_error("Qty {$detail['M_ItemCode']} - {$detail['M_ItemDesc']} cabang {$detail['M_BranchName']} regional {$detail['S_RegionalName']} tidak mencukupi karena sisa qty sudah habis diproses, silahkan refresh item");
$this->db->trans_rollback();
exit;
}
$sqlDetail = "INSERT INTO purchase_order_detail (
PurchaseOrderDetailPurchaseOrderID,
PurchaseOrderDetailPurchaseSummaryID,
PurchaseOrderDetailPurchaseRequestID,
PurchaseOrderDetailPurchaseRequestDetailID,
PurchaseOrderDetailPurchaseRequestFlagID,
PurchaseOrderDetailItemID,
PurchaseOrderDetailItemUnitID,
PurchaseOrderDetailRequestQty,
PurchaseOrderDetailQty,
PurchaseOrderDetailPrice,
PurchaseOrderDetailTotal,
PurchaseOrderDetailWarehouseID,
PurchaseOrderDetailIsActive,
PurchaseOrderDetailUserID,
PurchaseOrderDetailCreatedUserID,
PurchaseOrderDetailCreated,
PurchaseOrderDetailLastUpdated
) VALUES (
{$purchaseOrderId},
{$summaryId},
{$detail['PurchaseRequestID']},
{$detail['PurchaseRequestDetailID']},
{$detail['PurchaseRequestFlagID']},
{$detail['M_ItemID']},
{$detail['PoItemUnitID']},
{$detail['PoQty']},
{$detail['PoQty']},
{$detail['Price']},
{$detail['Total']},
{$detail['warehouse']['WarehouseID']},
'Y',
{$userId},
{$userId},
NOW(),
NOW()
)";
$exec = $this->db->query($sqlDetail);
if (!$exec) {
$this->sys_error_db("purchase_order_detail insert error", $this->db);
$this->db->trans_rollback();
exit;
}
// Step 4: Update tabel purchase_request_flag
$sqlUpdateFlag = "UPDATE purchase_request_flag
SET PurchaseRequestFlagQtyProses = PurchaseRequestFlagQtyProses + {$detail['RequestQty']}
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;
}
}
}
$poAfter = $this->getDataPo($purchaseOrderId);
$this->insertLog(
$purchaseOrderId,
"ADD",
$payload,
[],
$poAfter,
$userId,
"Add purchase order {$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);
}
}
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['PurchaseRequestDetailQty']}
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;
}
// 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,
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,
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,
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
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
PurchaseRequestDetailM_ItemID = SupplierPriceM_ItemID
AND PurchaseOrderSupplierID = SupplierPriceSupplierID
AND SupplierPriceItemUnitID = PurchaseRequestDetailItemUnitID
AND (SupplierPriceFlagQty ='T' OR
(
(SupplierPriceMinInclusive = 'Y' AND PurchaseRequestDetailQty >= SupplierPriceMinQty) OR
(SupplierPriceMinInclusive = 'N' AND PurchaseRequestDetailQty > SupplierPriceMinQty)
) AND
(
(SupplierPriceMaxInclusive = 'Y' AND PurchaseRequestDetailQty <= SupplierPriceMaxQty) OR
(SupplierPriceMaxInclusive = 'N' AND PurchaseRequestDetailQty < SupplierPriceMaxQty)
)
)
AND SupplierPriceIsActive = 'Y'
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'];
$rows[$key]['RealPrice'] = $row['SummaryPrice'];
$row['Price'] = doubleval($row['SummaryPrice']) - doubleval($row['summaryDiscountAmount']);
$row['RealPrice'] = $row['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'], $row['PoQty']);
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");
}
$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 = ?,
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['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) {
$sql = "SELECT * FROM purchase_request_flag
WHERE PurchaseRequestFlagID = ?
AND PurchaseRequestFlagIsActive = 'Y'";
$qry = $this->db->query($sql, [$detail['PurchaseRequestFlagID']]);
if (!$qry) {
$this->sys_error_db("Error checking purchase_request_flag", $this->db);
$this->db->trans_rollback();
exit;
}
$row = $qry->row_array();
if (!$row) {
$this->sys_error("Data {$detail['M_ItemCode']} - {$detail['M_ItemDesc']} cabang {$detail['M_BranchName']} regional {$detail['S_RegionalName']} tidak ditemukan, silahkan refresh item");
$this->db->trans_rollback();
exit;
}
$qtyRest = floatval($row['PurchaseRequestFlagQtyRest']);
$qtyProses = floatval($row['PurchaseRequestFlagQtyProses']);
if (($qtyRest - $qtyProses) <= 0) {
$this->sys_error("Qty {$detail['M_ItemCode']} - {$detail['M_ItemDesc']} cabang {$detail['M_BranchName']} regional {$detail['S_RegionalName']} tidak mencukupi karena sisa qty sudah habis diproses, silahkan refresh item");
$this->db->trans_rollback();
exit;
}
$updateMsg[] = "- Add data purchase order detail summary {$summary['M_ItemDesc']} {$detail['PoQty']} {$detail['ItemUnitName']} \n";
$sqlInsertDetail = "INSERT INTO purchase_order_detail (
PurchaseOrderDetailPurchaseOrderID,
PurchaseOrderDetailPurchaseSummaryID,
PurchaseOrderDetailPurchaseRequestID,
PurchaseOrderDetailPurchaseRequestDetailID,
PurchaseOrderDetailPurchaseRequestFlagID,
PurchaseOrderDetailItemID,
PurchaseOrderDetailItemUnitID,
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['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['RequestQty']}
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";
$sqlUpdateDetail = "UPDATE purchase_order_detail
SET
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";
$sql = "SELECT * FROM purchase_request_flag
WHERE PurchaseRequestFlagID = ?
AND PurchaseRequestFlagIsActive = 'Y'";
$qry = $this->db->query($sql, $detail['PurchaseRequestFlagID']);
if (!$qry) {
$this->sys_error_db("Error checking purchase_request_flag", $this->db);
$this->db->trans_rollback();
exit;
}
$row = $qry->row_array();
if (!$row) {
$this->sys_error("Data {$detail['M_ItemCode']} - {$detail['M_ItemDesc']} cabang {$detail['M_BranchName']} regional {$detail['S_RegionalName']} tidak ditemukan, silahkan refresh item");
$this->db->trans_rollback();
exit;
}
$qtyRest = floatval($row['PurchaseRequestFlagQtyRest']);
$qtyProses = floatval($row['PurchaseRequestFlagQtyProses']);
if (($qtyRest - $qtyProses) <= 0) {
$this->sys_error("Qty {$detail['M_ItemCode']} - {$detail['M_ItemDesc']} cabang {$detail['M_BranchName']} regional {$detail['S_RegionalName']} tidak mencukupi karena sisa qty sudah habis diproses, silahkan refresh item");
$this->db->trans_rollback();
exit;
}
// Insert data baru
$sqlInsertDetail = "INSERT INTO purchase_order_detail (
PurchaseOrderDetailPurchaseOrderID,
PurchaseOrderDetailPurchaseSummaryID,
PurchaseOrderDetailPurchaseRequestID,
PurchaseOrderDetailPurchaseRequestDetailID,
PurchaseOrderDetailPurchaseRequestFlagID,
PurchaseOrderDetailItemID,
PurchaseOrderDetailItemUnitID,
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['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['RequestQty']}
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;
}
}
}
}
}
//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['RequestQty']}
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);
}
}
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"]]);
$branchId = $this->sys_user['M_BranchID'];
$regionalId = $this->sys_user['S_RegionalID'];
$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();
}
$number = $row[0]['PurchaseOrderNumber'];
// notification
$this->saveNotification($userId, $payload['Date'], $number, $payload["ID"], $branchId, $regionalId);
$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->readNotif($approvelevel, $userId, $payload['poID']);
$this->db->trans_commit();
$this->sys_ok("[Success] success update status PO to Approved");
} catch (Exception $exc) {
$message = $exc->getMessage();
$this->sys_error($message);
}
}
// read notification
function readNotif($userlevel, $userId, $refID)
{
$this->db->trans_begin();
// cari user penerima notifikasi
$sql_get = "SELECT PurchaseOrderID,
M_UserID,
M_UserUsername,
NotificationID,
NotificationDetailID,
NotificationDetailStatus,
NotificationDetailM_UserID,
NotificationDetailM_ApproveLevelID
FROM purchase_order
JOIN notification ON PurchaseOrderID = NotificationRefID
AND PurchaseOrderIsActive = 'Y'
JOIN notification_detail ON NotificationID = NotificationDetailNotificationID
JOIN m_user ON NotificationUserID = M_UserID
WHERE PurchaseOrderID = ?
AND NotificationDetailStatus = 'unread'";
$qry = $this->db->query($sql_get, [$refID]);
if (!$qry) {
$this->sys_error_db("select user notification error", $this->db);
exit;
}
$rowsuser = $qry->result_array();
foreach ($rowsuser as $user) {
if ($userlevel === '1') {
// verifikasi manager
$sql = "UPDATE notification_detail SET
NotificationDetailStatus = 'read',
NotificationDetailLastUpdated = NOW(),
NotificationDetailUserID = ?
WHERE NotificationDetailNotificationID = ?
AND NotificationDetailM_ApproveLevelID = ?";
$qry = $this->db->query($sql, [$userId, $user["NotificationID"], $user["NotificationDetailM_ApproveLevelID"]]);
if (!$qry) {
$this->sys_error_db("update notification error", $this->db);
exit;
}
} else if ($userlevel === '2') {
// approve kepala cabang / regional
$sql = "UPDATE notification_detail SET
NotificationDetailStatus = 'read',
NotificationDetailLastUpdated = NOW(),
NotificationDetailUserID = ?
WHERE NotificationDetailNotificationID = ?
AND NotificationDetailM_ApproveLevelID = ?";
$qry = $this->db->query($sql, [$userId, $user["NotificationID"], $user["NotificationDetailM_ApproveLevelID"]]);
if (!$qry) {
$this->sys_error_db("update notification error", $this->db);
exit;
}
}
}
$this->db->trans_commit();
}
/**
* 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, $qty)
{
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 = ?
AND SupplierPriceIsActive = 'Y'
AND (
SupplierPriceFlagQty = 'T'
OR (
(
(SupplierPriceMinInclusive = 'Y' AND $qty >= SupplierPriceMinQty)
OR
(SupplierPriceMinInclusive = 'N' AND $qty > SupplierPriceMinQty)
)
AND
(
(SupplierPriceMaxInclusive = 'Y' AND $qty <= SupplierPriceMaxQty)
OR
(SupplierPriceMaxInclusive = 'N' AND $qty < SupplierPriceMaxQty)
)
)
)";
$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 getDefaultSupplierPrice()
{
try {
if (!$this->isLogin) {
$this->sys_error("Invalid Token");
exit;
}
$prm = $this->sys_input;
$supplierID = $prm['supplierID'];
$itemID = $prm['itemID'];
$qty = $prm['qty'];
$sql = "SELECT
SupplierPricePrice
FROM supplier_price
JOIN m_item
ON M_ItemID = SupplierPriceM_ItemID
WHERE SupplierPriceSupplierID = ?
AND SupplierPriceM_ItemID = ?
AND SupplierPriceIsActive = 'Y'
AND (
SupplierPriceFlagQty = 'T'
OR (
(
(SupplierPriceMinInclusive = 'Y' AND $qty >= SupplierPriceMinQty)
OR
(SupplierPriceMinInclusive = 'N' AND $qty > SupplierPriceMinQty)
)
AND
(
(SupplierPriceMaxInclusive = 'Y' AND $qty <= SupplierPriceMaxQty)
OR
(SupplierPriceMaxInclusive = 'N' AND $qty < SupplierPriceMaxQty)
)
)
)";
$exec = $this->db->query($sql, [$supplierID, $itemID]);
if (!$exec) {
$this->sys_error_db("Error get default purchase", $this->db);
exit;
}
$rowDefault = $exec->row_array();
$result = array(
"records" => $rowDefault
);
$this->sys_ok($result);
} 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, $dateRequest, $number, $refID, $branchId, $regionalId)
{
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 = "Pembuatan PURCHASE ORDER pada tanggal " . $xdate . " oleh " . $username['M_UserUsername'] . " dengan nomor " . $number . "";
$sql_menu = "SELECT S_MenuID,
S_MenuName,
S_MenuUrl,
S_MenuLevel
FROM s_menu
WHERE S_MenuID = 82";
$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'] . "?flag=N&status=Pending&startdate=" . $dateRequest . "&search=" . $number;
// cari user penerima notifikasi
if ($branchId != 0) {
// user cabang
$sql_receive_user = "SELECT * FROM m_user
JOIN m_branch ON M_UserM_BranchID = M_BranchID
AND M_BranchIsActive = 'Y'
AND M_BranchID = ?
WHERE M_UserIsActive = 'Y'
AND M_UserM_ApproveLevelID IN (1,2)";
$qry_receive_user = $this->db->query($sql_receive_user, [$branchId]);
if ($qry_receive_user) {
$rowsuser = $qry_receive_user->result_array();
} else {
$this->sys_error_db("select receive user error", $this->db);
exit;
}
} else if ($branchId == 0) {
// user regional
$sql_receive_user = "SELECT * FROM m_user
JOIN s_regional ON M_UserS_RegionalID = S_RegionalID
AND S_RegionalIsActive = 'Y'
AND S_RegionalID = ?
WHERE M_UserIsActive = 'Y'
AND M_UserM_BranchID = 0
AND M_UserM_ApproveLevelID IN (1,2)";
$qry_receive_user = $this->db->query($sql_receive_user, [$regionalId]);
if ($qry_receive_user) {
$rowsuser = $qry_receive_user->result_array();
} else {
$this->sys_error_db("select receive user error", $this->db);
exit;
}
}
$sql = "INSERT INTO notification(
NotificationRefID,
NotificationType,
NotificationDescription,
NotificationUrl,
NotificationUserID,
NotificationCreated) VALUES(?,?,?,?,?,NOW())";
$qry = $this->db->query($sql, [
$refID,
'Purchase Order',
$description,
$url,
$userId
]);
if (!$qry) {
$this->db->trans_rollback();
$this->sys_error_db("insert notification error", $this->db);
exit;
}
$lastid = $this->db->insert_id();
// insert ke detail notification
foreach ($rowsuser as $row) {
$sql_detail = "INSERT INTO notification_detail(
NotificationDetailNotificationID,
NotificationDetailM_UserID,
NotificationDetailM_ApproveLevelID,
NotificationDetailStatus,
NotificationDetailCreated,
NotificationDetailUserID) VALUES(?,?,?,?,NOW(),?)";
$qry_detail = $this->db->query($sql_detail, [
$lastid,
$row['M_UserID'], // user penerima notifikasi
$row['M_UserM_ApproveLevelID'],
'unread',
$userId
]);
if (!$qry_detail) {
$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);
}
}
}