1553 lines
64 KiB
PHP
1553 lines
64 KiB
PHP
<?php
|
|
|
|
class ReceiveOrderService extends MY_Controller {
|
|
var $db;
|
|
public function index() {
|
|
echo 'api receive order service';
|
|
}
|
|
|
|
public function __construct()
|
|
{
|
|
parent::__construct();
|
|
}
|
|
|
|
# QUERY #
|
|
public function searchOrderJasa() {
|
|
try {
|
|
if (!$this->isLogin) {
|
|
$this->sys_error("invalid token");
|
|
exit;
|
|
}
|
|
|
|
$user = $this->sys_user;
|
|
$para = $this->sys_input;
|
|
$keyword = $para['keyword'] . "%";
|
|
|
|
$sql = "SELECT
|
|
PurchaseOrderID,
|
|
PurchaseOrderDate,
|
|
PurchaseOrderNumber,
|
|
PurchaseOrderNote,
|
|
T_KontrakJasaID,
|
|
T_KontrakJasaM_BranchCode AS branchcode,
|
|
T_KontrakJasaJenisKontrak,
|
|
T_KontrakJasaStartDate,
|
|
T_KontrakJasaEndDate,
|
|
T_KontrakJasaJumlahPI,
|
|
T_KontrakJasaUsedCount,
|
|
CONCAT(T_KontrakJasaStartDate, ' - ', T_KontrakJasaEndDate) AS periode,
|
|
SupplierID,
|
|
SupplierCode,
|
|
SupplierName
|
|
FROM purchase_order
|
|
JOIN t_kontrak_jasa ON T_KontrakJasaPurchaseOrderID = PurchaseOrderID
|
|
AND PurchaseOrderItemCategoryID = '4'
|
|
AND PurchaseOrderStatus = 'Approved'
|
|
AND PurchaseOrderNumber LIKE ?
|
|
AND PurchaseOrderSupplierID = ?
|
|
AND PurchaseOrderS_RegionalID = ?
|
|
AND T_KontrakJasaM_BranchCode = ?
|
|
AND T_KontrakJasaUsedCount != T_KontrakJasaJumlahPI
|
|
AND T_KontrakJasaStatus = 'active'
|
|
JOIN supplier ON SupplierID = PurchaseOrderSupplierID
|
|
WHERE PurchaseOrderIsActive = 'Y'
|
|
LIMIT 10";
|
|
$que = $this->db->query($sql, [
|
|
$keyword, $para['supplierID'],
|
|
$user['S_RegionalID'], $para['branchcode'],
|
|
]);
|
|
if (!$que) {
|
|
$this->sys_error_db("[Error] failed get data order jasa approved");
|
|
}
|
|
$data = $que->result_array();
|
|
|
|
foreach ($data as $key => $obj) {
|
|
$sql_detail = "SELECT
|
|
M_ItemID,
|
|
M_ItemCode,
|
|
M_ItemDesc,
|
|
ItemUnitID,
|
|
ItemUnitCode,
|
|
ItemUnitName,
|
|
PurchaseOrderDetailID AS PODetailID,
|
|
PurchaseOrderDetailPurchaseSummaryID AS POSummaryID,
|
|
PurchaseOrderDetailPurchaseOrderID AS PurchaseOrderID,
|
|
PurchaseOrderDetailQty AS qty,
|
|
PoItemReceiveID,
|
|
PoItemReceivePrice AS price
|
|
FROM purchase_order_detail
|
|
JOIN po_item_receive ON PoItemReceiveIsActive = 'Y'
|
|
AND PoItemReceivePurchaseOrderDetailID = PurchaseOrderDetailID
|
|
JOIN m_item ON M_ItemID = PurchaseOrderDetailItemID
|
|
JOIN itemunit ON ItemUnitID = PurchaseOrderDetailItemUnitID
|
|
WHERE PurchaseOrderDetailPurchaseOrderID = ?
|
|
AND PurchaseOrderDetailIsActive = 'Y'";
|
|
$que_detail = $this->db->query($sql_detail, [$obj['PurchaseOrderID']]);
|
|
if (!$que_detail) {
|
|
$this->sys_error_db("[Error] failed get data from purchase order detail");
|
|
exit;
|
|
}
|
|
|
|
$detail = $que_detail->result_array();
|
|
$data[$key]['detail'] = $detail;
|
|
}
|
|
|
|
$output = [
|
|
"records" => $data
|
|
];
|
|
$this->sys_ok($output);
|
|
} catch (Exception $exc) {
|
|
$msg = $exc->getMessage();
|
|
$this->sys_error($msg);
|
|
}
|
|
}
|
|
|
|
public function getListingVendor() {
|
|
try {
|
|
if (!$this->isLogin) {
|
|
$this->sys_error("invalid token");
|
|
exit;
|
|
}
|
|
|
|
$para = $this->sys_input;
|
|
|
|
$keyword = "%";
|
|
if ($para['search'] != '') {
|
|
$keyword .= $para['search'] . "%";
|
|
}
|
|
|
|
$sql = "SELECT
|
|
SupplierID,
|
|
SupplierCode,
|
|
SupplierName
|
|
FROM supplier
|
|
WHERE SupplierIsActive = 'Y'
|
|
AND SupplierName LIKE ?";
|
|
$que = $this->db->query($sql, [$keyword]);
|
|
if (!$que) {
|
|
$this->sys_error_db("[Error] failed to get data supplier");
|
|
exit;
|
|
}
|
|
$data = $que->result_array();
|
|
|
|
$this->sys_ok($data);
|
|
} catch (Exception $exc) {
|
|
$msg = $exc->getMessage();
|
|
$this->sys_error($msg);
|
|
}
|
|
}
|
|
|
|
public function getListBranch() {
|
|
try {
|
|
if (!$this->isLogin) {
|
|
$this->sys_error("invalid token");
|
|
exit;
|
|
}
|
|
|
|
$user = $this->sys_user;
|
|
|
|
$sql = "SELECT
|
|
M_BranchID,
|
|
M_BranchCode,
|
|
M_BranchName
|
|
FROM m_branch
|
|
WHERE M_BranchIsActive = 'Y'
|
|
AND M_BranchS_RegionalID = ?";
|
|
$que = $this->db->query($sql, [$user['S_RegionalID']]);
|
|
if (!$que) {
|
|
$this->sys_error_db("[Error] failed get data from m branch");
|
|
exit;
|
|
}
|
|
|
|
$data = $que->result_array();
|
|
|
|
$this->sys_ok($data);
|
|
} catch (Exception $exc) {
|
|
$msg = $exc->getMessage();
|
|
$this->sys_error($msg);
|
|
}
|
|
}
|
|
|
|
public function getDaftarStaff() {
|
|
try {
|
|
if (!$this->isLogin) {
|
|
$this->sys_error("invalid token");
|
|
exit;
|
|
}
|
|
|
|
$user = $this->sys_user;
|
|
|
|
$sql = "SELECT
|
|
M_UserID,
|
|
M_UserUsername
|
|
FROM m_user
|
|
WHERE M_UserM_BranchID = ?
|
|
AND M_UserS_RegionalID = ?
|
|
AND M_UserIsActive = 'Y'
|
|
ORDER BY M_UserUsername";
|
|
$que = $this->db->query($sql, [$user['M_BranchID'], $user['S_RegionalID']]);
|
|
if (!$que) {
|
|
$this->sys_error_db("[Error] get staff");
|
|
exit;
|
|
}
|
|
$data = $que->result_array();
|
|
|
|
$this->sys_ok($data);
|
|
} catch (Exception $exc) {
|
|
$msg = $exc->getMessage();
|
|
$this->sys_error($msg);
|
|
}
|
|
}
|
|
|
|
public function getListingROJasa() {
|
|
try {
|
|
if (!$this->isLogin) {
|
|
$this->sys_error("invalid token");
|
|
exit;
|
|
}
|
|
|
|
$para = $this->sys_input;
|
|
$user = $this->sys_user;
|
|
|
|
$keyword = "%";
|
|
if ($para['search'] != '') {
|
|
$keyword .= $para['search'] . "%";
|
|
}
|
|
|
|
$offset = 0;
|
|
$limit = 10;
|
|
if ($para['currpage'] > 0) {
|
|
$offset = ($para['currpage'] - 1) * $limit;
|
|
}
|
|
|
|
$confirmed = "X";
|
|
if ($para['status'] === 'Confirmed') {
|
|
$confirmed = "Y";
|
|
}
|
|
if ($para['status'] === 'Unconfirmed') {
|
|
$confirmed = 'N';
|
|
}
|
|
|
|
$sql_base = "SELECT DISTINCT
|
|
ReceiveOrderPoID,
|
|
ReceiveOrderPoNumber,
|
|
ReceiveOrderPoIDate,
|
|
ReceiveOrderPoRefNumber,
|
|
ReceiveOrderPoNote,
|
|
ReceiveOrderPoConfirmed,
|
|
SupplierID,
|
|
SupplierName,
|
|
PurchaseOrderID,
|
|
T_KontrakJasaID
|
|
FROM receive_order_po
|
|
JOIN receive_order_po_detail
|
|
ON ReceiveOrderPoDetailReceiveOrderPoID = ReceiveOrderPoID
|
|
AND ReceiveOrderPoIDate BETWEEN DATE(?) AND DATE(?)
|
|
AND (ReceiveOrderPoConfirmed = ? OR 'X' = ?)
|
|
AND ReceiveOrderPoNumber LIKE ?
|
|
AND ReceiveOrderPoDetailIsActive = 'Y'
|
|
JOIN purchase_order
|
|
ON ReceiveOrderPoDetailPurchaseOrderID = PurchaseOrderID
|
|
AND PurchaseOrderItemCategoryID = 4 -- Filter category here
|
|
JOIN m_branch
|
|
ON M_BranchCode = ReceiveOrderPoM_BranchCode
|
|
AND ReceiveOrderPoM_BranchCode = ?
|
|
JOIN t_kontrak_jasa
|
|
ON T_KontrakJasaPurchaseOrderID = PurchaseOrderID
|
|
AND T_KontrakJasaM_BranchCode = ReceiveOrderPoM_BranchCode
|
|
JOIN supplier
|
|
ON ReceiveOrderPoSupplierID = SupplierID
|
|
WHERE ReceiveOrderPoIsActive = 'Y'
|
|
ORDER BY ReceiveOrderPoID DESC ";
|
|
|
|
$sql_data = $sql_base . " LIMIT ? OFFSET ? ";
|
|
$que_data = $this->db->query($sql_data, [
|
|
$para['startdate'], $para['enddate'],
|
|
$confirmed, $confirmed, $keyword,
|
|
$user['M_BranchCode'], $limit, $offset
|
|
]);
|
|
if (!$que_data) {
|
|
$this->sys_error_db("[Error] failed to get data receive order");
|
|
exit;
|
|
}
|
|
$data = $que_data->result_array();
|
|
|
|
$sql_total = "SELECT COUNT(*) AS total FROM ($sql_base) AS x";
|
|
$que_total = $this->db->query($sql_total, [
|
|
$para['startdate'], $para['enddate'],
|
|
$confirmed, $confirmed, $keyword, $user['M_BranchCode']
|
|
]);
|
|
if (!$que_total) {
|
|
$this->sys_error_db("[Error] failed to get total data receive order");
|
|
exit;
|
|
}
|
|
$total = $que_total->row_array()['total'];
|
|
|
|
$output = [
|
|
"records" => $data,
|
|
"total" => $total
|
|
];
|
|
|
|
$this->sys_ok($output);
|
|
} catch (Exception $exc) {
|
|
$msg = $exc->getMessage();
|
|
$this->sys_error($msg);
|
|
}
|
|
}
|
|
|
|
public function getDetailDataROJasa() {
|
|
try {
|
|
if (!$this->isLogin) {
|
|
$this->sys_error("invalid token");
|
|
exit;
|
|
}
|
|
|
|
$para = $this->sys_input;
|
|
$user = $this->sys_user;
|
|
|
|
$sql_header = "SELECT
|
|
ReceiveOrderPoID,
|
|
ReceiveOrderPoNumber,
|
|
ReceiveOrderPoSupplierID AS supplierID,
|
|
ReceiveOrderPoIDate AS rodate,
|
|
ReceiveOrderPoM_BranchCode AS branchcode,
|
|
ReceiveOrderPoRefNumber AS reference,
|
|
ReceiveOrderPoDONumber AS orderjasano,
|
|
ReceiveOrderPoNote AS catatan,
|
|
T_KontrakJasaID AS TJasaID,
|
|
T_KontrakJasaJenisKontrak AS typekontrak,
|
|
T_KontrakJasaStartDate AS startdate,
|
|
T_KontrakJasaEndDate AS enddate,
|
|
T_KontrakJasaJumlahPI AS stock,
|
|
T_KontrakJasaUsedCount AS used
|
|
FROM receive_order_po
|
|
JOIN receive_order_po_detail
|
|
ON ReceiveOrderPoDetailReceiveOrderPoID = ReceiveOrderPoID
|
|
AND ReceiveOrderPoDetailIsActive = 'Y'
|
|
JOIN t_kontrak_jasa
|
|
ON T_KontrakJasaPurchaseOrderID = ReceiveOrderPoDetailPurchaseOrderID
|
|
AND T_KontrakJasaM_BranchCode = ReceiveOrderPoM_BranchCode
|
|
WHERE ReceiveOrderPoID = ?
|
|
AND ReceiveOrderPoIsActive = 'Y'";
|
|
$que_header = $this->db->query($sql_header, [$para['roid']]);
|
|
if (!$que_header) {
|
|
$this->sys_error_db("[Error] failed get header data RO");
|
|
exit;
|
|
}
|
|
$header = $que_header->row_array();
|
|
$header['usedcount'] = "{$header['used']} / {$header['stock']}";
|
|
|
|
$sql_detail = "SELECT
|
|
M_ItemID,
|
|
M_ItemCode,
|
|
M_ItemDesc,
|
|
ItemUnitID,
|
|
ItemUnitCode,
|
|
ItemUnitName,
|
|
ReceiveOrderPoDetailID,
|
|
ReceiveOrderPoDetailPurchaseOrderID AS PurchaseOrderID,
|
|
ReceiveOrderPoDetailPurchaseOrderDetailID AS PODetailID,
|
|
ReceiveOrderPoDetailPurchaseOrderSummaryID AS POSummaryID,
|
|
ReceiveOrderPoDetailPoItemReceiveID AS PoItemReceiveID,
|
|
ReceiveOrderPoDetailQty AS qty,
|
|
ReceiveOrderPoDetailPrice AS price
|
|
FROM receive_order_po_detail
|
|
JOIN m_item ON M_ItemID = ReceiveOrderPoItemID
|
|
JOIN itemunit ON ItemUnitID = ReceiveOrderPoItemUnitID
|
|
WHERE ReceiveOrderPoDetailReceiveOrderPoID = ?
|
|
AND ReceiveOrderPoDetailIsActive = 'Y'";
|
|
$que_detail = $this->db->query($sql_detail, [$para['roid']]);
|
|
if (!$que_detail) {
|
|
$this->sys_error_db("[Error] failed to get detail data ro");
|
|
exit;
|
|
}
|
|
$detail = $que_detail->result_array();
|
|
|
|
foreach ($detail as $key => $obj) {
|
|
$sql_inspeksi = "SELECT
|
|
OrderJasaInspeksiID,
|
|
OrderJasaInspeksiReceiveOrderPoDetailID,
|
|
OrderJasaInspeksiHasilPengerjaan AS hasil_pengerjaan,
|
|
OrderJasaInspeksiCatatanPengerjaan AS catatan_pengerjaan,
|
|
OrderJasaInspeksiKesimpulan AS kesimpulan,
|
|
OrderJasaInspeksiCatatan AS catatan_kesimpulan,
|
|
OrderJasaInspeksiStaffPemeriksaID AS pemeriksa,
|
|
OrderJasaInspeksiPetugasPengerjaan AS pekerja
|
|
FROM order_jasa_inspeksi
|
|
WHERE OrderJasaInspeksiReceiveOrderPoDetailID = ?
|
|
AND OrderJasaInspeksiIsActive = 'Y'";
|
|
$que_inspeksi = $this->db->query($sql_inspeksi, [
|
|
$obj['ReceiveOrderPoDetailID']
|
|
]);
|
|
if (!$que_inspeksi) {
|
|
$this->sys_error_db("[Error] failed to get data inspeksi ro");
|
|
exit;
|
|
}
|
|
$inspeksi = $que_inspeksi->row_array();
|
|
|
|
$detail[$key]['inspeksi'] = $inspeksi;
|
|
}
|
|
|
|
$output = [
|
|
"header" => $header,
|
|
"detail" => $detail
|
|
];
|
|
|
|
$this->sys_ok($output);
|
|
} catch (Exception $exc) {
|
|
$msg = $exc->getMessage();
|
|
$this->sys_error($msg);
|
|
}
|
|
}
|
|
|
|
public function getListAttachment() {
|
|
try {
|
|
if (!$this->isLogin) {
|
|
$this->sys_error("invalid token");
|
|
exit;
|
|
}
|
|
|
|
$para = $this->sys_input;
|
|
|
|
$sql = "SELECT
|
|
ReceiveOrderPoDocumentID AS attach_id,
|
|
ReceiveOrderPoDocumentFile AS img_url,
|
|
ReceiveOrderPoDocumentCreated AS created
|
|
FROM receive_order_po_document
|
|
WHERE ReceiveOrderPoDocumentIsActive = 'Y'
|
|
AND ReceiveOrderPoDocumentReceiveOrderPoID = ?
|
|
AND ReceiveOrderPoDocumentType = 'jasa'";
|
|
$que = $this->db->query($sql, [$para['roID']]);
|
|
if (!$que) {
|
|
$this->sys_error_db("[Error] failed get data attachment");
|
|
exit;
|
|
}
|
|
$data = $que->result_array();
|
|
|
|
$this->sys_ok($data);
|
|
} catch (Exception $exc) {
|
|
$msg = $exc->getMessage();
|
|
$this->sys_error($msg);
|
|
}
|
|
}
|
|
|
|
private function generateNoLogistik($user, $typeLogistik) : string {
|
|
$areaid = ($user['loginLevel'] == 'regional') ? $user['S_RegionalID'] : $user['M_BranchID'];
|
|
$areatype = ($user['loginLevel'] == 'regional') ? 'R' : 'B';
|
|
|
|
$sql_usrdiv = "SELECT M_UserDivisionDivisionID FROM m_userdivision
|
|
WHERE M_UserDivisionM_UserID = ? AND M_UserDivisionIsActive = 'Y'";
|
|
$que_usrdiv = $this->db->query($sql_usrdiv, [$user['M_UserID']]);
|
|
if (!$que_usrdiv) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed get user divisi");
|
|
exit;
|
|
}
|
|
|
|
$usrdiv = $que_usrdiv->row_array()['M_UserDivisionDivisionID'];
|
|
if (!isset($usrdiv)) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error("[Error] user divisi ID is NULL");
|
|
exit;
|
|
}
|
|
|
|
$sql_numdoc = "SELECT `fn_penomoran`(?,?,?,?,?,?) AS numdoc";
|
|
$que_numdoc = $this->db->query($sql_numdoc, [
|
|
$typeLogistik, $usrdiv, $areatype, $areaid, 'SM', 'Y'
|
|
]);
|
|
if (!$que_numdoc) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed to generate doc number");
|
|
exit;
|
|
}
|
|
|
|
$numdoc = $que_numdoc->row_array()['numdoc'];
|
|
if (!isset($numdoc)) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error("[Error] doc number is NULL, failed to generate");
|
|
exit;
|
|
}
|
|
|
|
return $numdoc;
|
|
}
|
|
|
|
# MUTATIONS #
|
|
public function generatePOItemReceive() {
|
|
try {
|
|
if (!$this->isLogin) {
|
|
$this->sys_error("invalid token");
|
|
exit;
|
|
}
|
|
|
|
$this->db->trans_begin();
|
|
$para = $this->sys_input;
|
|
$user = $this->sys_user;
|
|
|
|
$sql_po = "SELECT * FROM purchase_order WHERE PurchaseOrderID = ?";
|
|
$que_po = $this->db->query($sql_po, [$para['poID']]);
|
|
if (!$que_po) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed get current data PO");
|
|
exit;
|
|
}
|
|
|
|
$data_po = $que_po->row_array();
|
|
if ($data_po['PurchaseOrderStatus'] != 'Approved') {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] PO aren't approved yet");
|
|
exit;
|
|
}
|
|
|
|
$sql_detailpo = "SELECT
|
|
IFNULL(PoItemReceiveID, 0) as receivedID,
|
|
IFNULL(PoItemReceiveQtyReceived, 0) as qtyReceived,
|
|
PurchaseOrderSummaryID as orderSummaryID,
|
|
PurchaseOrderSummaryItemID as itemID,
|
|
PurchaseOrderSummaryItemUnitID as itemUnitID,
|
|
PurchaseOrderSummaryQty as qty,
|
|
PurchaseOrderSummaryPrice as price,
|
|
m_itemDesc as itemName,
|
|
ItemUnitName as itemUnitName,
|
|
PurchaseOrderDetailID
|
|
FROM purchase_order_summary
|
|
JOIN purchase_order_detail ON PurchaseOrderSummaryItemID = PurchaseOrderDetailItemID
|
|
AND PurchaseOrderDetailPurchaseOrderID = PurchaseOrderSummaryPurchaseOrderID
|
|
AND PurchaseOrderSummaryItemUnitID = PurchaseOrderDetailItemUnitID
|
|
AND PurchaseOrderDetailIsActive = 'Y'
|
|
JOIN m_item ON PurchaseOrderSummaryItemID = M_ItemID
|
|
JOIN itemunit ON PurchaseOrderSummaryItemUnitID = ItemUnitID
|
|
LEFT JOIN po_item_receive ON PurchaseOrderSummaryItemID = PoItemReceiveItemID
|
|
AND PurchaseOrderSummaryPurchaseOrderID = PoItemReceivePurchaseOrderID
|
|
WHERE PurchaseOrderSummaryPurchaseOrderID = ?
|
|
AND PurchaseOrderSummaryIsActive = 'Y'
|
|
AND IFNULL(PoItemReceiveQtyReceived, 0) = 0";
|
|
$que_detailpo = $this->db->query($sql_detailpo, [$para['poID']]);
|
|
if (!$que_detailpo) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed getting current po detail data");
|
|
exit;
|
|
}
|
|
$data_detailpo = $que_detailpo->result_array();
|
|
|
|
foreach ($data_detailpo as $key => $ele) {
|
|
$sql_itemrec = "INSERT INTO po_item_receive (
|
|
PoItemReceivePurchaseOrderID,
|
|
PoItemReceivePurchaseOrderSummaryID,
|
|
PoItemReceivePurchaseOrderDetailID,
|
|
PoItemReceiveItemID,
|
|
PoItemReceiveItemUnitID,
|
|
PoItemReceiveQty,
|
|
PoItemReceiveQtyReceived,
|
|
PoItemReceiveCreated,
|
|
PoItemReceiveCreatedUserID,
|
|
PoItemReceivePrice
|
|
) VALUES (?,?,?,?,?,?,0,NOW(),?,?)";
|
|
$que_itemrec = $this->db->query($sql_itemrec, [
|
|
$para['poID'], $ele['orderSummaryID'], $ele['PurchaseOrderDetailID'],
|
|
$ele['itemID'], $ele['itemUnitID'], $ele['qty'],
|
|
$user['M_UserID'], $ele['price']
|
|
]);
|
|
if (!$que_itemrec) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed insert table po item receive");
|
|
exit;
|
|
}
|
|
}
|
|
|
|
$this->db->trans_commit();
|
|
$this->sys_ok("[success] generate data po item receive");
|
|
} catch (Exception $exc) {
|
|
$msg = $exc->getMessage();
|
|
$this->sys_error($msg);
|
|
}
|
|
}
|
|
|
|
public function saveReceiveOrderJasa() {
|
|
try {
|
|
if (!$this->isLogin) {
|
|
$this->sys_error("invalid token");
|
|
exit;
|
|
}
|
|
|
|
$this->db->trans_begin();
|
|
$para = $this->sys_input;
|
|
$user = $this->sys_user;
|
|
|
|
# CHECK penggunaan jasa di kontrak #
|
|
$sql_jasa = "SELECT CASE
|
|
WHEN EXISTS (
|
|
SELECT 1
|
|
FROM t_kontrak_jasa
|
|
WHERE T_KontrakJasaJumlahPI != T_KontrakJasaUsedCount
|
|
AND T_KontrakJasaIsActive = 'Y'
|
|
AND T_KontrakJasaID = ?
|
|
) THEN 1
|
|
ELSE 0
|
|
END AS result";
|
|
$que_jasa = $this->db->query($sql_jasa, [$para['TJasaID']]);
|
|
if (!$que_jasa) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed to get current service data");
|
|
exit;
|
|
}
|
|
$isUsedAll = $que_jasa->row_array()['result'];
|
|
if ($isUsedAll == '0') {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] contract has been used all");
|
|
exit;
|
|
}
|
|
|
|
# GENERATE number ro #
|
|
$num_gr = $this->generateNoLogistik($user, 'GR');
|
|
|
|
# INSERT into table receive order po #
|
|
$sql_insro = "INSERT INTO receive_order_po (
|
|
ReceiveOrderPoNumber,
|
|
ReceiveOrderPoSupplierID,
|
|
ReceiveOrderPoIDate,
|
|
ReceiveOrderPoM_BranchCode,
|
|
ReceiveOrderPoS_RegionalID,
|
|
ReceiveOrderPoNote,
|
|
ReceiveOrderPoRefNumber,
|
|
ReceiveOrderPoDONumber,
|
|
ReceiveOrderPoCreatedUserID
|
|
) VALUES (?,?,?,?,?,?,?,?,?)";
|
|
$que_insro = $this->db->query($sql_insro, [
|
|
$num_gr, $para['supplierID'], $para['rodate'],
|
|
$para['branchcode'], $user['S_RegionalID'], $para['catatan'],
|
|
$para['reference'], $para['orderjasano'], $user['M_UserID']
|
|
]);
|
|
if (!$que_insro) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed to insert data to table receive order po");
|
|
exit;
|
|
}
|
|
$ROID = $this->db->insert_id();
|
|
|
|
foreach ($para['detail'] as $key => $obj) {
|
|
$sql_insde = "INSERT INTO receive_order_po_detail (
|
|
ReceiveOrderPoDetailReceiveOrderPoID,
|
|
ReceiveOrderPoDetailPurchaseOrderID,
|
|
ReceiveOrderPoDetailPurchaseOrderSummaryID,
|
|
ReceiveOrderPoDetailPurchaseOrderDetailID,
|
|
ReceiveOrderPoDetailPoItemReceiveID,
|
|
ReceiveOrderPoDetailQty,
|
|
ReceiveOrderPoDetailPrice,
|
|
ReceiveOrderPoDetailTotal,
|
|
ReceiveOrderPoItemID,
|
|
ReceiveOrderPoItemUnitID,
|
|
ReceiveOrderPoDetailCreatedUserID
|
|
) VALUES (?,?,?,?,?,?,?,?,?,?,?)";
|
|
$que_insde = $this->db->query($sql_insde, [
|
|
$ROID, $obj['PurchaseOrderID'], $obj['POSummaryID'], $obj['PODetailID'],
|
|
$obj['PoItemReceiveID'], $obj['qty'], $obj['price'],
|
|
(doubleval($obj['price']) * intval($obj['qty'])),
|
|
$obj['M_ItemID'], $obj['ItemUnitID'], $user['M_UserID']
|
|
]);
|
|
if (!$que_insde) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed insert detail");
|
|
exit;
|
|
}
|
|
$ROdetailID = $this->db->insert_id();
|
|
|
|
/* insert data inspeksi */
|
|
$inspeksi = $obj['inspeksi'];
|
|
$sql_inspeksi = "INSERT INTO order_jasa_inspeksi (
|
|
OrderJasaInspeksiReceiveOrderPoDetailID,
|
|
OrderJasaInspeksiHasilPengerjaan,
|
|
OrderJasaInspeksiCatatanPengerjaan,
|
|
OrderJasaInspeksiKesimpulan,
|
|
OrderJasaInspeksiCatatan,
|
|
OrderJasaInspeksiStaffPemeriksaID,
|
|
OrderJasaInspeksiPetugasPengerjaan
|
|
) VALUES (?,?,?,?,?,?,?)";
|
|
$que_inspeksi = $this->db->query($sql_inspeksi, [
|
|
$ROdetailID, $inspeksi['hasil_pengerjaan'],
|
|
$inspeksi['catatan_pengerjaan'], $inspeksi['kesimpulan'],
|
|
$inspeksi['catatan_kesimpulan'], $inspeksi['pemeriksa'],
|
|
$inspeksi['pekerja']
|
|
]);
|
|
if (!$que_inspeksi) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed insert inspeksi jasa");
|
|
exit;
|
|
}
|
|
}
|
|
|
|
$this->db->trans_commit();
|
|
$this->sys_ok("[Success] create receive order jasa");
|
|
} catch (Exception $exc) {
|
|
$msg = $exc->getMessage();
|
|
$this->sys_error($msg);
|
|
}
|
|
}
|
|
|
|
public function editReceiveOrderJasa() {
|
|
try {
|
|
if (!$this->isLogin) {
|
|
$this->sys_error("invalid token");
|
|
exit;
|
|
}
|
|
|
|
$this->db->trans_begin();
|
|
$para = $this->sys_input;
|
|
$user = $this->sys_user;
|
|
|
|
|
|
# update table receive order po #
|
|
$sql_header = "UPDATE receive_order_po SET
|
|
ReceiveOrderPoSupplierID = ?,
|
|
ReceiveOrderPoIDate = ?,
|
|
ReceiveOrderPoM_BranchCode = ?,
|
|
ReceiveOrderPoS_RegionalID = ?,
|
|
ReceiveOrderPoNote = ?,
|
|
ReceiveOrderPoRefNumber = ?,
|
|
ReceiveOrderPoDONumber = ?
|
|
WHERE ReceiveOrderPoID = ?
|
|
AND ReceiveOrderPoIsActive = 'Y'";
|
|
$que_header = $this->db->query($sql_header, [
|
|
$para['supplierID'], $para['rodate'], $para['branchcode'],
|
|
$user['S_RegionalID'], $para['catatan'], $para['reference'],
|
|
$para['orderjasano'], $para['ReceiveOrderPoID']
|
|
]);
|
|
if (!$que_header) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed update table receive order po");
|
|
exit;
|
|
}
|
|
|
|
# update status receive order po detail isActive into 'N' #
|
|
$sql_active = "UPDATE receive_order_po_detail
|
|
JOIN order_jasa_inspeksi
|
|
ON OrderJasaInspeksiReceiveOrderPoDetailID = ReceiveOrderPoDetailID
|
|
SET ReceiveOrderPoDetailIsActive = 'N',
|
|
ReceiveOrderPoDetailDeleted = NOW(),
|
|
ReceiveOrderPoDetailDeletedUserID = ?,
|
|
OrderJasaInspeksiIsActive = 'N',
|
|
OrderJasaInspeksiLastUpdated = NOW(),
|
|
OrderJasaInspeksiUserID = ?
|
|
WHERE ReceiveOrderPoDetailReceiveOrderPoID = ?";
|
|
$que_active = $this->db->query($sql_active, [
|
|
$user['M_UserID'], $user['M_UserID'], $para['ReceiveOrderPoID']
|
|
]);
|
|
if (!$que_active) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed update ro detail & inspeksi isActive to N");
|
|
exit;
|
|
}
|
|
|
|
# insert new detail & inspeksi data #
|
|
foreach ($para['detail'] as $key => $obj) {
|
|
$sql_detail = "INSERT INTO receive_order_po_detail (
|
|
ReceiveOrderPoDetailReceiveOrderPoID,
|
|
ReceiveOrderPoDetailPurchaseOrderID,
|
|
ReceiveOrderPoDetailPurchaseOrderSummaryID,
|
|
ReceiveOrderPoDetailPurchaseOrderDetailID,
|
|
ReceiveOrderPoDetailPoItemReceiveID,
|
|
ReceiveOrderPoDetailQty,
|
|
ReceiveOrderPoDetailPrice,
|
|
ReceiveOrderPoDetailTotal,
|
|
ReceiveOrderPoItemID,
|
|
ReceiveOrderPoItemUnitID,
|
|
ReceiveOrderPoDetailCreatedUserID
|
|
) VALUES (?,?,?,?,?,?,?,?,?,?,?)";
|
|
$que_detail = $this->db->query($sql_detail, [
|
|
$para['ReceiveOrderPoID'], $obj['PurchaseOrderID'], $obj['POSummaryID'],
|
|
$obj['PODetailID'], $obj['PoItemReceiveID'], $obj['qty'], $obj['price'],
|
|
(doubleval($obj['price']) * intval($obj['qty'])), $obj['M_ItemID'],
|
|
$obj['ItemUnitID'], $user['M_UserID']
|
|
]);
|
|
if (!$que_detail) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed to insert new ro detail");
|
|
exit;
|
|
}
|
|
$ROdetailID = $this->db->insert_id();
|
|
|
|
$inspek = $obj['inspeksi'];
|
|
$sql_inspek = "INSERT INTO order_jasa_inspeksi (
|
|
OrderJasaInspeksiReceiveOrderPoDetailID,
|
|
OrderJasaInspeksiHasilPengerjaan,
|
|
OrderJasaInspeksiCatatanPengerjaan,
|
|
OrderJasaInspeksiKesimpulan,
|
|
OrderJasaInspeksiCatatan,
|
|
OrderJasaInspeksiStaffPemeriksaID,
|
|
OrderJasaInspeksiPetugasPengerjaan
|
|
) VALUES (?,?,?,?,?,?,?)";
|
|
$que_inspek = $this->db->query($sql_inspek, [
|
|
$ROdetailID, $inspek['hasil_pengerjaan'],
|
|
$inspek['catatan_pengerjaan'], $inspek['kesimpulan'],
|
|
$inspek['catatan_kesimpulan'], $inspek['pemeriksa'], $inspek['pekerja']
|
|
]);
|
|
if (!$que_inspek) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed to insert new inspeksi");
|
|
exit;
|
|
}
|
|
}
|
|
|
|
$this->db->trans_commit();
|
|
$this->sys_ok("[success] update receive order jasa");
|
|
} catch (Exception $exc) {
|
|
$msg = $exc->getMessage();
|
|
$this->sys_error($msg);
|
|
}
|
|
}
|
|
|
|
public function uploadAttachment() {
|
|
try {
|
|
if (!$this->isLogin) {
|
|
$this->sys_error("invalid token");
|
|
exit;
|
|
}
|
|
|
|
$this->db->trans_begin();
|
|
$para = $this->sys_input;
|
|
$user = $this->sys_user;
|
|
|
|
$basepath = "/home/one/project/accone/one-media/order-jasa/";
|
|
$year = date("Y");
|
|
$targetpath = $basepath . $year . "/";
|
|
|
|
if (!is_dir($targetpath)) {
|
|
mkdir($targetpath, 0755, true);
|
|
}
|
|
|
|
$config['upload_path'] = $targetpath;
|
|
$config['allowed_types'] = 'jpg|jpeg|png';
|
|
$config['max_size'] = '10000';
|
|
$count = count($_FILES['files']['name']);
|
|
$this->load->library('upload', $config);
|
|
|
|
for ($i=0; $i < $count; $i++) {
|
|
if (!empty($_FILES['files']['name'][$i])) {
|
|
$_FILES['file']['name'] = $_FILES['files']['name'][$i];
|
|
$_FILES['file']['type'] = $_FILES['files']['type'][$i];
|
|
$_FILES['file']['tmp_name'] = $_FILES['files']['tmp_name'][$i];
|
|
$_FILES['file']['error'] = $_FILES['files']['error'][$i];
|
|
$_FILES['file']['size'] = $_FILES['files']['size'][$i];
|
|
|
|
$ext = pathinfo($_FILES['file']['name'], PATHINFO_EXTENSION);
|
|
$random_str = bin2hex(random_bytes(4));
|
|
$new_filename = "SVC" . $random_str . "_" . $para['ronumber'] . "." . $ext;
|
|
$config['file_name'] = $new_filename;
|
|
$this->upload->initialize($config);
|
|
|
|
if ($this->upload->do_upload('file')) {
|
|
$upload_data = $this->upload->data();
|
|
$filename = $upload_data['file_name'];
|
|
|
|
$sql_insert = "INSERT INTO receive_order_po_document (
|
|
ReceiveOrderPoDocumentReceiveOrderPoID,
|
|
ReceiveOrderPoDocumentDate,
|
|
ReceiveOrderPoDocumentFile,
|
|
ReceiveOrderPoDocumentType,
|
|
ReceiveOrderPoDocumentIsActive,
|
|
ReceiveOrderPoDocumentCreated,
|
|
ReceiveOrderPoDocumentUserID
|
|
) VALUES (?, NOW(), ?, ?, 'Y', NOW(), ?)";
|
|
$que_insert = $this->db->query($sql_insert, [
|
|
$para['roID'], $filename, 'jasa', $user['M_UserID']
|
|
]);
|
|
if (!$que_insert) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed insert attachment table ro documents");
|
|
exit;
|
|
}
|
|
} else {
|
|
$this->db->trans_rollback();
|
|
$error = $this->upload->display_errors();
|
|
$this->sys_error("[Error] " . $error);
|
|
exit;
|
|
}
|
|
}
|
|
}
|
|
|
|
$this->db->trans_commit();
|
|
$this->sys_ok("[Success] upload attachment order jasa");
|
|
} catch (Exception $exc) {
|
|
$msg = $exc->getMessage();
|
|
$this->sys_error($msg);
|
|
}
|
|
}
|
|
|
|
public function deleteReceiveOrder() {
|
|
try {
|
|
if (!$this->isLogin) {
|
|
$this->sys_error("invalid token");
|
|
exit;
|
|
}
|
|
|
|
$this->db->trans_begin();
|
|
|
|
$para = $this->sys_input;
|
|
$user = $this->sys_user;
|
|
|
|
$ROID = $para['roid'];
|
|
|
|
# soft delete table receive order po #
|
|
$sql_del = "UPDATE receive_order_po SET
|
|
ReceiveOrderPoIsActive = 'N',
|
|
ReceiveOrderPoDeleted = NOW(),
|
|
ReceiveOrderPoDeletedUserID = ?
|
|
WHERE ReceiveOrderPoID = ?";
|
|
$que_del = $this->db->query($sql_del, [
|
|
$user['M_UserID'], $ROID
|
|
]);
|
|
if (!$que_del) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed to upadte status receive order po");
|
|
exit;
|
|
}
|
|
|
|
$sql_deldet = "UPDATE receive_order_po_detail SET
|
|
ReceiveOrderPoDetailIsActive = 'N',
|
|
ReceiveOrderPoDetailDeletedUserID = ?,
|
|
ReceiveOrderPoDetailDeleted = NOW()
|
|
WHERE ReceiveOrderPoDetailReceiveOrderPoID = ?";
|
|
$que_deldet = $this->db->query($sql_deldet, [
|
|
$user['M_UserID'], $ROID
|
|
]);
|
|
if (!$que_deldet) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed to update status receive order po detail");
|
|
exit;
|
|
}
|
|
|
|
$sql_inspek = "UPDATE order_jasa_inspeksi
|
|
JOIN receive_order_po_detail
|
|
ON ReceiveOrderPoDetailID = OrderJasaInspeksiReceiveOrderPoDetailID
|
|
SET
|
|
OrderJasaInspeksiIsActive = 'N',
|
|
OrderJasaInspeksiUserID = ?,
|
|
OrderJasaInspeksiLastUpdated = NOW()
|
|
WHERE ReceiveOrderPoDetailReceiveOrderPoID = ?";
|
|
$que_inspek = $this->db->query($sql_inspek, [
|
|
$user['M_UserID'], $ROID
|
|
]);
|
|
if (!$que_inspek) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed softdelete table inspek jasa");
|
|
exit;
|
|
}
|
|
|
|
$sql_attachment = "UPDATE receive_order_po_document SET
|
|
ReceiveOrderPoDocumentIsActive = 'N',
|
|
ReceiveOrderPoDocumentLastUpdated = NOW(),
|
|
ReceiveOrderPoDocumentUserID = ?
|
|
WHERE ReceiveOrderPoDocumentReceiveOrderPoID = ?
|
|
AND ReceiveOrderPoDocumentType = 'jasa'";
|
|
$que_attachment = $this->db->query($sql_attachment, [
|
|
$user['M_UserID'], $ROID
|
|
]);
|
|
if (!$que_attachment) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed softdelete attachment jasa");
|
|
exit;
|
|
}
|
|
|
|
$desc = "delete receive order jasa dengan id {$ROID}";
|
|
$this->insertLogReceiveOrderPO($ROID, 'DELETE', '', '', $user['M_UserID'], $desc);
|
|
$this->insertUserActivty($ROID, 'RO', 'DELETE', $user['M_UserID'], $desc);
|
|
|
|
$this->db->trans_commit();
|
|
$this->sys_ok("[success] delete receive order");
|
|
} catch (Exception $exc) {
|
|
$msg = $exc->getMessage();
|
|
$this->sys_error($msg);
|
|
}
|
|
}
|
|
|
|
public function confirmReceiveOrder() {
|
|
try {
|
|
if (!$this->isLogin) {
|
|
$this->sys_error("invalid token");
|
|
exit;
|
|
}
|
|
|
|
$this->db->trans_begin();
|
|
$para = $this->sys_input;
|
|
$user = $this->sys_user;
|
|
|
|
$roID = $para['roid'];
|
|
|
|
# CHECK & UPDATE penggunaan jasa di kontrak #
|
|
$sql_jasa = "SELECT
|
|
T_KontrakJasaJumlahPI AS stock,
|
|
T_KontrakJasaUsedCount AS used,
|
|
T_KontrakJasaStatus AS status
|
|
FROM t_kontrak_jasa
|
|
WHERE T_KontrakJasaIsActive = 'Y'
|
|
AND T_KontrakJasaID = ?";
|
|
$que_jasa = $this->db->query($sql_jasa, [$para['TJasaID']]);
|
|
if (!$que_jasa) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed to get current service data");
|
|
exit;
|
|
}
|
|
$currJasa = $que_jasa->row_array();
|
|
if ($currJasa['stock'] == $currJasa['used']) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] contract has been used all");
|
|
exit;
|
|
}
|
|
|
|
$upStatus = $currJasa['status'];
|
|
$updUsed = intval($currJasa['used']) + 1;
|
|
if (intval($currJasa['stock'] == $updUsed)) {
|
|
$upStatus = 'completed';
|
|
}
|
|
|
|
$sql_upjasa = "UPDATE t_kontrak_jasa SET
|
|
T_KontrakJasaUsedCount = ?,
|
|
T_KontrakJasaStatus = ?,
|
|
T_KontrakJasaLastUpdated = NOW()
|
|
WHERE T_KontrakJasaID = ?";
|
|
$que_upjasa = $this->db->query($sql_upjasa, [
|
|
$updUsed, $upStatus, $para['TJasaID']
|
|
]);
|
|
if (!$que_upjasa) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed to update table t_kontrak_jasa");
|
|
exit;
|
|
}
|
|
|
|
# DATA ORDER JASA #
|
|
$sql_dataro = "SELECT
|
|
receive_order_po.*,
|
|
SupplierName
|
|
FROM receive_order_po
|
|
JOIN supplier ON ReceiveOrderPoSupplierID = SupplierID
|
|
WHERE ReceiveOrderPoID = ?";
|
|
$que_dataro = $this->db->query($sql_dataro, [$roID]);
|
|
if (!$que_dataro) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed to get current data RO");
|
|
exit;
|
|
}
|
|
$data_ro = $que_dataro->row_array();
|
|
|
|
$sql_detailro = "SELECT
|
|
receive_order_po_detail.*,
|
|
PurchaseOrderID,
|
|
PurchaseOrderNumber,
|
|
PurchaseOrderTaxPercentPph,
|
|
PurchaseOrderTaxPercentPpn,
|
|
PurchaseOrderSummaryDiscountAmount as DiscountPerItem,
|
|
PurchaseOrderSummaryQty as QtyAllPO,
|
|
PurchaseOrderSummaryTotal as TotalAllPO,
|
|
PurchaseOrderDiscountPercent AS DiscPOPercent,
|
|
PurchaseOrderDiscountAmount AS DiscPORupiah
|
|
FROM receive_order_po_detail
|
|
JOIN purchase_order ON ReceiveOrderPoDetailPurchaseOrderID = PurchaseOrderID
|
|
JOIN purchase_order_summary ON ReceiveOrderPoDetailPurchaseOrderSummaryID = PurchaseOrderSummaryID
|
|
AND PurchaseOrderSummaryIsActive = 'Y'
|
|
WHERE ReceiveOrderPoDetailReceiveOrderPoID = ?
|
|
AND ReceiveOrderPoDetailIsActive='Y'
|
|
GROUP BY ReceiveOrderPoDetailID";
|
|
$que_detailro = $this->db->query($sql_detailro, [$roID]);
|
|
if (!$que_detailro) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed get detail data current RO");
|
|
exit;
|
|
}
|
|
$detail_ro = $que_detailro->result_array();
|
|
|
|
# UPDATE STATUS RO #
|
|
$sql_updatero = "UPDATE receive_order_po SET
|
|
ReceiveOrderPoConfirmed = 'Y',
|
|
ReceiveOrderPoConfirmedDate = NOW(),
|
|
ReceiveOrderPoConfirmedUserID = ?
|
|
WHERE ReceiveOrderPoID = ?";
|
|
$que_updatero = $this->db->query($sql_updatero, [$user['M_UserID'], $roID]);
|
|
if (!$que_updatero) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed to update status confirmed ro");
|
|
exit;
|
|
}
|
|
|
|
# PREPARE DATA FOR JURNAL #
|
|
$sql_nojurnal = "SELECT `fn_numbering`('J') AS numbering";
|
|
$que_nojurnal = $this->db->query($sql_nojurnal, []);
|
|
if (!$que_nojurnal) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed to generate no jurnal");
|
|
exit;
|
|
}
|
|
$nojurnal = $que_nojurnal->row_array()['numbering'];
|
|
|
|
$sql_periode = "SELECT periodeID FROM periode
|
|
WHERE DATE(NOW()) BETWEEN periodeStartDate AND periodeEndDate
|
|
AND periodeIsActive = 'Y' AND periodeIsClosed = 'N'";
|
|
$que_periode = $this->db->query($sql_periode, []);
|
|
if (!$que_periode) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed get periode data");
|
|
exit;
|
|
}
|
|
if ($que_periode->num_rows() === 0) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] waktu periode tidak ditemukan");
|
|
exit;
|
|
}
|
|
$periode_jurnal = $que_periode->row_array()['periodeID'];
|
|
|
|
$sql_typejurnal = "SELECT JurnalTypeID FROM jurnal_type WHERE JurnalTypeCode = ? AND JurnalTypeIsActive = 'Y'";
|
|
$que_typejurnal = $this->db->query($sql_typejurnal, ['AUTOGOODRECEIVE']);
|
|
if (!$que_typejurnal) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed to get jurnal type");
|
|
exit;
|
|
}
|
|
if ($que_typejurnal->num_rows === 0) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] tipe jurnal tidak ditemukan");
|
|
exit;
|
|
}
|
|
$typejurnal = $que_typejurnal->row_array()['JurnalTypeID'];
|
|
|
|
# INSERT JURNAL HEADER #
|
|
$title = "Jurnal Pemakaian Jasa RO {$data_ro['ReceiveOrderPoNumber']} ";
|
|
$title .= "pada tanggal {$data_ro['ReceiveOrderPoIDate']}";
|
|
$desc = "Referensi dari PO {$data_ro['ReceiveOrderPoRefNumber']} ";
|
|
$desc .= "dari supplier {$data_ro['SupplierName']}";
|
|
|
|
$sql_headerjurnal = "INSERT INTO jurnal (
|
|
jurnalM_BranchCompanyID,
|
|
JurnalS_RegionalID,
|
|
jurnalM_BranchCode,
|
|
jurnalperiodeID,
|
|
jurnalNo,
|
|
jurnalTitle,
|
|
jurnalDescription,
|
|
jurnalDate,
|
|
jurnalJurnalTypeID,
|
|
jurnalM_UserID
|
|
) VALUES (?,?,?,?,?,?,?,NOW(),?,?)";
|
|
$que_headerjurnal = $this->db->query($sql_headerjurnal, [
|
|
$user['M_BranchCompanyID'], $user['S_RegionalID'], $user['M_BranchCode'],
|
|
$periode_jurnal, $nojurnal, $title, $desc, $typejurnal, $user['M_UserID']
|
|
]);
|
|
if (!$que_headerjurnal) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed insert data into table jurnal");
|
|
exit;
|
|
}
|
|
$jurnalID = $this->db->insert_id();
|
|
|
|
# INSERT JURNAL TX #
|
|
|
|
$total_debet = 0.00;
|
|
$total_diskonitem = 0.00;
|
|
$total_diskonpo = 0.00;
|
|
foreach ($detail_ro as $key => $elem) {
|
|
$sql_itemdata = "SELECT * FROM m_item WHERE M_ItemID = ?";
|
|
$que_itemdata = $this->db->query($sql_itemdata, [$elem['ReceiveOrderPoItemID']]);
|
|
if (!$que_itemdata) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed gettting data item");
|
|
exit;
|
|
}
|
|
$itemdata = $que_itemdata->row_array();
|
|
|
|
/* cek kategori item */
|
|
if (empty($itemdata['M_ItemItem_CategoryID'])) {
|
|
$desc = "kategori item {$itemdata['M_ItemDesc']} tidak ditemukan pada ";
|
|
$desc .= "PO {$elem['PurchaseOrderNumber']}";
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] {$desc}");
|
|
exit;
|
|
}
|
|
|
|
$sql_coajasa = "SELECT
|
|
JasaCoaMapCoaID,
|
|
JasaCoaMapCoaNo,
|
|
JasaCoaMapCoaDesc
|
|
FROM jasa_coa_map
|
|
JOIN coa ON coaID = JasaCoaMapCoaID
|
|
AND coaIsActive = 'Y'
|
|
WHERE JasaCoaMapM_ItemID = ?
|
|
AND JasaCoaMapCoaIsActive = 'Y'";
|
|
$que_coajasa = $this->db->query($sql_coajasa, [$elem['ReceiveOrderPoItemID']]);
|
|
if (!$que_coajasa) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed to get coa jasa");
|
|
exit;
|
|
}
|
|
|
|
$coajasa = $que_coajasa->row_array();
|
|
if ($que_coajasa->num_rows() <= 0) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] account jasa {$itemdata['M_ItemDesc']} tidak ditemukan");
|
|
exit;
|
|
}
|
|
|
|
$debet = doubleval($elem['ReceiveOrderPoDetailTotal']);
|
|
$total_debet = round($total_debet + $debet, 2);
|
|
|
|
/* INSERT table jurnal tx item */
|
|
$sql_jurnaltx = "INSERT INTO jurnal_tx (
|
|
jurnalTxJurnalID,
|
|
jurnalTxCoaID,
|
|
jurnalTxDescription,
|
|
jurnalTxDebit,
|
|
jurnalTxCredit,
|
|
jurnalTxM_UserID
|
|
) VALUES (?,?,?,?,?,?)";
|
|
$que_jurnaltx = $this->db->query($sql_jurnaltx, [
|
|
$jurnalID, $coajasa['JasaCoaMapCoaID'],
|
|
$coajasa['JasaCoaMapCoaDesc'], $debet, 0, $user['M_UserID']
|
|
]);
|
|
if (!$que_jurnaltx) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed insert jurnaltx {$itemdata['M_ItemDesc']}");
|
|
exit;
|
|
}
|
|
$jurnalTXID = $this->db->insert_id();
|
|
|
|
/* INSERT table jurnal addon item */
|
|
$sql_jurnaladdon = "INSERT INTO jurnal_addon (
|
|
jurnalAddOnJurnalID,
|
|
jurnalAddOnJurnalTxID,
|
|
jurnalAddOnCode,
|
|
jurnalAddOnValue,
|
|
jurnalAddOnCreated,
|
|
jurnalAddOnCreatedUserID
|
|
) VALUES (?,?,'PONUMB',?,NOW(),?)";
|
|
$que_jurnaladdon = $this->db->query($sql_jurnaladdon, [
|
|
$jurnalID, $jurnalTXID, $elem['PurchaseOrderNumber'],
|
|
$user['M_UserID']
|
|
]);
|
|
if (!$que_jurnaladdon) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed insert jurnal addon {$itemdata['M_ItemDesc']}");
|
|
exit;
|
|
}
|
|
|
|
/* INSERT table jurnal tx kredit diskon per item */
|
|
$sql_qtypo = "SELECT
|
|
SUM(subquery.PurchaseOrderSummaryQty) as totalAllQtyPO,
|
|
SUM(subquery.PurchaseOrderSummaryTotal) as totalAllPricePO
|
|
FROM (
|
|
SELECT
|
|
PurchaseOrderSummaryQty,
|
|
PurchaseOrderSummaryTotal
|
|
FROM receive_order_po_detail
|
|
JOIN purchase_order_summary ON PurchaseOrderSummaryIsActive = 'Y'
|
|
AND PurchaseOrderSummaryPurchaseOrderID = ReceiveOrderPoDetailPurchaseOrderID
|
|
WHERE ReceiveOrderPoDetailReceiveOrderPoID = ?
|
|
GROUP BY PurchaseOrderSummaryID
|
|
) AS subquery";
|
|
$que_qtypo = $this->db->query($sql_qtypo, [$roID]);
|
|
if (!$que_qtypo) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] get detail qty & price po");
|
|
exit;
|
|
}
|
|
$data_po = $que_qtypo->row_array();
|
|
|
|
/* kalkulasi diskon per item */
|
|
$diskon = round(intval($elem['ReceiveOrderPoDetailQty']) * floatval($elem['DiscountPerItem']), 2);
|
|
$total_diskonitem = round($total_diskonitem + $diskon, 2);
|
|
|
|
/* kalkulasi diskon prorata PO per item */
|
|
$diskon_po = doubleval($elem['DiscPORupiah']);
|
|
if (doubleval($elem['DiscPOPercent']) != 0.00) {
|
|
$diskon_po = doubleval($data_po['totalAllPricePO']) * doubleval($elem['DiscPOPercent']) / 100;
|
|
}
|
|
$diskon_proratapo = $diskon_po * (doubleval($elem['TotalAllPO'])) / doubleval($data_po['totalAllPricePO']);
|
|
$diskon_prorata_item = round((intval($elem['ReceiveOrderPoDetailQty']) / intval($elem['QtyAllPO'])) * $diskon_proratapo, 2);
|
|
|
|
$total_diskonpo = round($total_diskonpo + $diskon_prorata_item, 2);
|
|
$input_diskon = [$diskon, $diskon_prorata_item];
|
|
|
|
/* get account diskon */
|
|
$sql_coadiskon = "SELECT * FROM coa WHERE coaAccountNo = ? LIMIT 1";
|
|
$que_coadiskon = $this->db->query($sql_coadiskon, ['2190100001']);
|
|
if (!$que_coadiskon) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed get account diskon");
|
|
exit;
|
|
}
|
|
$coa_diskon = $que_coadiskon->row_array();
|
|
foreach ($input_diskon as $key => $val) {
|
|
if (doubleval($val) <= 0.00) {
|
|
continue;
|
|
}
|
|
$que_jurnaltx = $this->db->query($sql_jurnaltx, [
|
|
$jurnalID, $coa_diskon['coaID'], $coa_diskon['coaDescription'],
|
|
0, $val, $user['M_UserID']
|
|
]);
|
|
if (!$que_jurnaltx) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed insert jurnal tx diskon");
|
|
exit;
|
|
}
|
|
$jurnalTXID_diskon = $this->db->insert_id();
|
|
|
|
$que_jurnaladdon = $this->db->query($sql_jurnaladdon, [
|
|
$jurnalID, $jurnalTXID_diskon,
|
|
$elem['PurchaseOrderNumber'], $user['M_UserID']
|
|
]);
|
|
if (!$que_jurnaladdon) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed insert jurnal addon diskon");
|
|
exit;
|
|
}
|
|
}
|
|
|
|
/* save diskon prorata item */
|
|
$sql_saveprorate = "UPDATE receive_order_po_detail SET
|
|
ReceiveOrderPoDetailDiskonPoProrata = ?
|
|
WHERE ReceiveOrderPoDetailID = ?";
|
|
$que_saveprorate = $this->db->query($sql_saveprorate, [
|
|
$diskon_prorata_item, $elem['ReceiveOrderPoDetailID']
|
|
]);
|
|
if (!$que_saveprorate) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] save diskon prorate");
|
|
exit;
|
|
}
|
|
}
|
|
|
|
# INSERT jurnal tx for GRNI #
|
|
$total_grni = round($total_debet - ($total_diskonitem + $total_diskonpo), 2);
|
|
$sql_grni = "INSERT INTO jurnal_tx (
|
|
jurnalTxJurnalID,
|
|
jurnalTxCoaID,
|
|
jurnalTxDescription,
|
|
jurnalTxDebit,
|
|
jurnalTxCredit,
|
|
jurnalTxM_UserID
|
|
) SELECT ?, coaID, coaDescription, 0, ?, ?
|
|
FROM coa WHERE coaAccountNo = '2110100030' LIMIT 1";
|
|
$que_grni = $this->db->query($sql_grni, [
|
|
$jurnalID, $total_grni, $user['M_UserID']
|
|
]);
|
|
if (!$que_grni) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] insert jurnal tx GRNI");
|
|
exit;
|
|
}
|
|
$jurnalTXID_GRNI = $this->db->insert_id();
|
|
|
|
$sql_addon_grni = "INSERT INTO jurnal_addon (
|
|
jurnalAddOnJurnalID,
|
|
jurnalAddOnJurnalTxID,
|
|
jurnalAddOnCode,
|
|
jurnalAddOnValue,
|
|
jurnalAddOnCreated,
|
|
jurnalAddOnCreatedUserID
|
|
) VALUES (?,?,'RONUMB',?,NOW(),?)";
|
|
$que_addon_grni = $this->db->query($sql_addon_grni, [
|
|
$jurnalID, $jurnalTXID_GRNI, $data_ro['ReceiveOrderPoNumber'],
|
|
$user['M_UserID']
|
|
]);
|
|
if (!$que_addon_grni) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] insert jurnal addon GRNI");
|
|
exit;
|
|
}
|
|
|
|
# Generate Purchase Invoice #
|
|
$this->generatePurchaseInvoice($user, $roID, $total_grni, $total_diskonpo);
|
|
|
|
$this->db->trans_commit();
|
|
$this->sys_ok("[Success] order received");
|
|
} catch (Exception $exc) {
|
|
$msg = $exc->getMessage();
|
|
$this->sys_error($msg);
|
|
}
|
|
}
|
|
|
|
private function generatePurchaseInvoice($user, $roid, $grni, $diskonpo) {
|
|
$num_pi = $this->generateNoLogistik($user, 'PI');
|
|
|
|
# prepare data #
|
|
$sql_dataro = "SELECT DISTINCT
|
|
receive_order_po.* ,
|
|
PurchaseOrderID,
|
|
PurchaseOrderTaxPpnType AS tax_type,
|
|
PurchaseOrderTaxPercentPpn AS tax_percent,
|
|
PurchaseOrderTaxAmountPpn AS tax_amount
|
|
FROM receive_order_po
|
|
JOIN receive_order_po_detail ON ReceiveOrderPoDetailReceiveOrderPoID = ReceiveOrderPoID
|
|
AND ReceiveOrderPoDetailIsActive = 'Y'
|
|
AND ReceiveOrderPoIsActive = 'Y'
|
|
JOIN purchase_order ON ReceiveOrderPoDetailPurchaseOrderID = PurchaseOrderID
|
|
WHERE ReceiveOrderPoID = ?";
|
|
$que_dataro = $this->db->query($sql_dataro, [$roid]);
|
|
if (!$que_dataro) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed get current data for generate PI");
|
|
exit;
|
|
}
|
|
$data_ro = $que_dataro->row_array();
|
|
|
|
$sql_detailro = "SELECT
|
|
receive_order_po_detail.*,
|
|
M_ItemDesc,
|
|
PurchaseOrderSummaryDiscountType AS DiscountType,
|
|
PurchaseOrderSummaryDiscountRupiah AS DiscountRupiah,
|
|
PurchaseOrderSummaryDiscountPercent AS DiscountPercent,
|
|
PurchaseOrderSummaryDiscountAmount as DiscountPerItem
|
|
FROM receive_order_po_detail
|
|
JOIN m_item ON M_ItemID = ReceiveOrderPoItemID AND M_ItemIsActive = 'Y'
|
|
JOIN purchase_order_summary ON ReceiveOrderPoDetailPurchaseOrderSummaryID = PurchaseOrderSummaryID
|
|
WHERE ReceiveOrderPoDetailIsActive = 'Y'
|
|
AND ReceiveOrderPoDetailReceiveOrderPoID = ?";
|
|
$que_detailro = $this->db->query($sql_detailro, [$roid]);
|
|
if (!$que_detailro) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed to get data detail ro for generate PI");
|
|
exit;
|
|
}
|
|
$detail_ro = $que_detailro->result_array();
|
|
|
|
# INSERT header supplier invoice #
|
|
$due_date = new DateTime($data_ro['ReceiveOrderPoIDate']);
|
|
$due_date->add(new DateInterval('P7D'));
|
|
$due_date = $due_date->format('Y-m-d');
|
|
|
|
$subtotal = round(($grni + $diskonpo), 2);
|
|
|
|
$donumber = "-";
|
|
if ($data_ro['ReceiveOrderPoDONumber'] != '' ) {
|
|
$donumber = $data_ro['ReceiveOrderPoDONumber'];
|
|
}
|
|
|
|
$sql_insert_pi = "INSERT INTO supplier_invoice (
|
|
SupplierInvoiceNumber,
|
|
SupplierInvoiceReceiveOrderPoID,
|
|
SupplierInvoiceDate,
|
|
SupplierInvoiceDueDate,
|
|
SupplierInvoiceSupplierID,
|
|
SupplierInvoiceRefNumber,
|
|
SupplierInvoiceDeliveryOrderNumber,
|
|
SupplierInvoiceSubTotal,
|
|
SupplierInvoiceDiscountPercent,
|
|
SupplierInvoiceDiscountAmount,
|
|
SupplierInvoiceTaxPercentPpn,
|
|
SupplierInvoiceTaxAmountPpn,
|
|
SupplierInvoiceGrandTotal,
|
|
SupplierInvoiceUnpaid,
|
|
SupplierInvoiceNote,
|
|
SupplierInvoiceReceiveDate,
|
|
SupplierInvoiceReceivedBy,
|
|
SupplierInvoiceCreatedUserID
|
|
) VALUES (?,?,NOW(),?,?,?,?,?,?,?,?,?,?,?,?,NOW(),?,?)";
|
|
$que_insert_pi = $this->db->query($sql_insert_pi, [
|
|
$num_pi, $data_ro['ReceiveOrderPoID'], $due_date, $data_ro['ReceiveOrderPoSupplierID'],
|
|
$data_ro['ReceiveOrderPoNumber'], $donumber, $subtotal, 0.00, $diskonpo,
|
|
$data_ro['tax_percent'], $data_ro['tax_amount'], $grni, $grni,
|
|
$data_ro['ReceiveOrderPoNote'], $user['M_UserID'], $user['M_UserID']
|
|
]);
|
|
if (!$que_insert_pi) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed insert data into table PI");
|
|
exit;
|
|
}
|
|
$Pinvoice_ID = $this->db->insert_id();
|
|
|
|
# INSERT supplier invoice detail #
|
|
$sql_ins_itemPI = "INSERT INTO supplier_invoice_detail (
|
|
SupplierInvoiceDetailSupplierInvoiceID,
|
|
SupplierInvoiceDetailPurchaseOrderID,
|
|
SupplierInvoiceDetailPurchaseOrderSummaryID,
|
|
SupplierInvoiceDetailReceiveOrderPoID,
|
|
SupplierInvoiceDetailReceiveOrderPoDetailID,
|
|
SupplierInvoiceDetailItemID,
|
|
SupplierInvoiceDetailItemUnitID,
|
|
SupplierInvoiceDetailDescription,
|
|
SupplierInvoiceDetailQty,
|
|
SupplierInvoiceDetailPrice,
|
|
SupplierInvoiceDetailDiscountPercent,
|
|
SupplierInvoiceDetailDiscountDiscountRupiah,
|
|
SupplierInvoiceDetailDiscountDiscountType,
|
|
SupplierInvoiceDetailDiscountPoProrata,
|
|
SupplierInvoiceDetailDiscountAmount,
|
|
SupplierInvoiceDetailTotal,
|
|
SupplierInvoiceDetailUnpaid,
|
|
SupplierInvoiceDetailCreatedUserID
|
|
) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
|
|
foreach ($detail_ro as $key => $elem) {
|
|
$itemprice = doubleval($elem['ReceiveOrderPoDetailPrice']) - doubleval($elem['DiscountPerItem']);
|
|
$totalprice = round($itemprice * intval($elem['ReceiveOrderPoDetailQty']), 2);
|
|
|
|
$que_ins_itemPI = $this->db->query($sql_ins_itemPI, [
|
|
$Pinvoice_ID, $elem['ReceiveOrderPoDetailPurchaseOrderID'],
|
|
$elem['ReceiveOrderPoDetailPurchaseOrderSummaryID'],
|
|
$elem['ReceiveOrderPoDetailReceiveOrderPoID'], $elem['ReceiveOrderPoDetailID'],
|
|
$elem['ReceiveOrderPoItemID'], $elem['ReceiveOrderPoItemUnitID'], $elem['M_ItemDesc'],
|
|
$elem['ReceiveOrderPoDetailQty'], $elem['ReceiveOrderPoDetailPrice'],
|
|
$elem['DiscountPercent'], $elem['DiscountRupiah'], $elem['DiscountType'],
|
|
$elem['ReceiveOrderPoDetailDiskonPoProrata'], $elem['DiscountPerItem'],
|
|
$totalprice, $totalprice, $user['M_UserID']
|
|
]);
|
|
if (!$que_ins_itemPI) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed insert table detail PI");
|
|
exit;
|
|
}
|
|
}
|
|
|
|
$desc = "generate PI untuk order jasa dari RO {$data_ro['ReceiveOrderPoNumber']}";
|
|
$this->insertUserActivty($Pinvoice_ID, 'INV', 'CREATE', $user['M_UserID'], $desc);
|
|
}
|
|
|
|
private function insertLogReceiveOrderPO($roid, $type, $dataold, $datanew, $userid, $desc) {
|
|
$sql = "INSERT INTO acc_one_log.receive_order_po_log (
|
|
ReceiveOrderPoLogReceiveOrderPoID,
|
|
ReceiveOrderPoLogType,
|
|
ReceiveOrderPoLogDesc,
|
|
ReceiveOrderPoLogJsonBefore,
|
|
ReceiveOrderPoLogJsonAfter,
|
|
ReceiveOrderPoLogUserID,
|
|
ReceiveOrderPoLogCreated
|
|
) VALUES (?,?,?,?,?,?,NOW())";
|
|
$que = $this->db->query($sql, [
|
|
$roid, $type, $desc,
|
|
!empty($dataold) ? json_encode($dataold) : '',
|
|
!empty($datanew) ? json_encode($datanew) : '',
|
|
$userid
|
|
]);
|
|
if (!$que) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed insert log receive order po");
|
|
exit;
|
|
}
|
|
}
|
|
|
|
private function insertUserActivty($id, $typePL, $status, $userid, $desc) {
|
|
$sql = "INSERT INTO user_activity (
|
|
UserActivityCode,
|
|
UserActivityStatus,
|
|
UserActivityDescription,
|
|
UserActivityRefID,
|
|
UserActivityUserID,
|
|
UserActivityCreated
|
|
) VALUES (?,?,?,?,?,NOW())";
|
|
$que = $this->db->query($sql, [$typePL, $status, $desc, $id, $userid]);
|
|
if (!$que) {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("[Error] failed insert user activity");
|
|
exit;
|
|
}
|
|
}
|
|
|
|
public function template() {
|
|
try {
|
|
if (!$this->isLogin) {
|
|
$this->sys_error("invalid token");
|
|
exit;
|
|
}
|
|
|
|
|
|
$this->sys_ok();
|
|
} catch (Exception $exc) {
|
|
$msg = $exc->getMessage();
|
|
$this->sys_error($msg);
|
|
}
|
|
}
|
|
} |