2937 lines
132 KiB
PHP
2937 lines
132 KiB
PHP
<?php
|
|
|
|
class PurchaseOrderV2 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"];
|
|
$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 <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 = 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);
|
|
}
|
|
}
|
|
}
|