feature: payment instruction & cashier case cicilan dan downpayment

application/controllers/map/InventarisCoaMapping.php

@@ -1,8 +1,10 @@
 <?php
 
-class InventarisCoaMapping extends MY_Controller {
+class InventarisCoaMapping extends MY_Controller
+{
     var $db;
-    public function index() {
+    public function index()
+    {
         echo "Inventaris COA Mapping API";
     }
 
@@ -11,8 +13,226 @@ class InventarisCoaMapping extends MY_Controller {
         parent::__construct();
     }
 
+
+    ## QUERY ##
+    public function getListCoa()
+    {
+        try {
+            if (!$this->isLogin) {
+                throw new Exception('Invalid token');
+            }
+
+            $para = $this->sys_input;
+            $keyword = "%" . $para['keyword'] . "%";
+
+            $sql = "SELECT
+                    coaID,
+                    coaAccountNo,
+                    coaDescription
+                FROM coa
+                WHERE coaIsInput = 'Y'
+                    AND (
+                        coaDescription LIKE ?
+                        OR coaAccountNo LIKE ?
+                    )
+                    AND coaIsActive = 'Y'
+                LIMIT 15";
+            $que = $this->db->query($sql, [$keyword, $keyword]);
+            if (!$que) {
+                throw new Exception('failed to query data inventaris gol', 1);
+            }
+            $data = $que->result_array();
+
+            $this->sys_ok($data);
+        } catch (Exception $e) {
+            $msg = '[Error] ' . $e->getMessage();
+            $code = $e->getCode();
+            if ($code == 0) {
+                $this->sys_error($msg);
+            } else {
+                $this->sys_error_db($msg);
+            }
+            exit;
+        }
+    }
+
+    public function getListInventarisGol()
+    {
+        try {
+            if (!$this->isLogin) {
+                throw new Exception('Invalid token');
+            }
+
+            $sql = "SELECT
+                M_InventarisGolID,
+                M_InventarisGolCode,
+                M_InventarisGolName
+            FROM m_inventaris_gol gol
+            WHERE gol.M_InventarisGolIsActive = 'Y'
+            AND NOT EXISTS (
+                SELECT 1
+                FROM m_inventaris_coa_mapping m
+                WHERE m.M_InventarisCoaMappingM_InventarisGolID = gol.M_InventarisGolID
+                AND m.M_InventarisCoaMappingIsActive = 'Y'
+            );";
+            $que = $this->db->query($sql);
+            if (!$que) {
+                throw new Exception('failed to query data inventaris gol', 1);
+            }
+            $data = $que->result_array();
+
+            $output = [
+                'records' => $data,
+                'total' => count($data)
+            ];
+
+            $this->sys_ok($output);
+        } catch (Exception $e) {
+            $msg = '[Error] ' . $e->getMessage();
+            $code = $e->getCode();
+            if ($code == 0) {
+                $this->sys_error($msg);
+            } else {
+                $this->sys_error_db($msg);
+            }
+            exit;
+        }
+    }
+
+    public function getListInventorygolMapping()
+    {
+        try {
+            if (!$this->isLogin) {
+                throw new Exception('invalid token');
+            }
+
+            $para = $this->sys_input;
+            $keyword = "%" . $para['keyword'] . "%";
+
+            $limit = 10;
+            $offset = 0;
+            if ($para['currpage'] > 0) {
+                $offset = ($para['currpage'] - 1) * $limit;
+            }
+
+            $sql = "SELECT
+                    M_InventarisGolID,
+                    M_InventarisGolCode,
+                    M_InventarisGolName,
+                    M_InventarisCoaMappingID,
+                    M_InventarisCoaMappingCoaInventarisID,
+                    coaInv.coaAccountNo          AS CoaInventarisAccountNo,
+                    coaInv.coaDescription        AS CoaInventarisDescription,
+                    M_InventarisCoaMappingCoaHutangID,
+                    coaHtg.coaAccountNo          AS CoaHutangAccountNo,
+                    coaHtg.coaDescription        AS CoaHutangDescription,
+                    M_InventarisCoaMappingCoaBebanPenyusutanID,
+                    coaBbn.coaAccountNo          AS CoaBebanPenyusutanAccountNo,
+                    coaBbn.coaDescription        AS CoaBebanPenyusutanDescription,
+                    M_InventarisCoaMappingCoaAkumulasiPenyusutanID,
+                    coaAkm.coaAccountNo          AS CoaAkumulasiPenyusutanAccountNo,
+                    coaAkm.coaDescription        AS CoaAkumulasiPenyusutanDescription,
+                    M_InventarisCoaMappingCoaLabaPelepasanID,
+                    coaLab.coaAccountNo          AS CoaLabaPelepasanAccountNo,
+                    coaLab.coaDescription        AS CoaLabaPelepasanDescription,
+                    M_InventarisCoaMappingCoaRugiPelepasanID,
+                    coaRug.coaAccountNo          AS CoaRugiPelepasanAccountNo,
+                    coaRug.coaDescription        AS CoaRugiPelepasanDescription
+                FROM m_inventaris_gol
+                JOIN m_inventaris_coa_mapping
+                    ON M_InventarisCoaMappingM_InventarisGolID = M_InventarisGolID
+                    AND M_InventarisCoaMappingIsActive = 'Y'
+                    AND M_InventarisGolIsActive = 'Y'
+                    AND M_InventarisGolName LIKE ?
+                LEFT JOIN coa AS coaInv
+                    ON M_InventarisCoaMappingCoaInventarisID = coaInv.coaID
+                LEFT JOIN coa AS coaHtg
+                    ON M_InventarisCoaMappingCoaHutangID = coaHtg.coaID
+                LEFT JOIN coa AS coaBbn
+                    ON M_InventarisCoaMappingCoaBebanPenyusutanID = coaBbn.coaID
+                LEFT JOIN coa AS coaAkm
+                    ON M_InventarisCoaMappingCoaAkumulasiPenyusutanID = coaAkm.coaID
+                LEFT JOIN coa AS coaLab
+                    ON M_InventarisCoaMappingCoaLabaPelepasanID = coaLab.coaID
+                LEFT JOIN coa AS coaRug
+                    ON M_InventarisCoaMappingCoaRugiPelepasanID = coaRug.coaID
+                WHERE M_InventarisGolIsActive = 'Y'";
+
+            $sql_data = $sql . " ORDER BY M_InventarisGolID LIMIT ? OFFSET ? ";
+            $que = $this->db->query($sql_data, [
+                $keyword,
+                $limit,
+                $offset
+            ]);
+            if (!$que) {
+                throw new Exception('failed to query data mapping coa inventaris golongan', 1);
+            }
+
+            $sql_total = "SELECT COUNT(*) AS total FROM ($sql) AS x";
+            $que_total = $this->db->query($sql_total, [$keyword]);
+            if (!$que_total) {
+                throw new Exception('failed to get total rows data', 1);
+            }
+
+            $this->sys_ok([
+                "records" => $que->result_array(),
+                "total" => $que_total->row_array()['total']
+            ]);
+        } catch (Exception $e) {
+            $msg = '[Error] ' . $e->getMessage();
+            $code = $e->getCode();
+            if ($code == 0) {
+                $this->sys_error($msg);
+            } else {
+                $this->sys_error_db($msg);
+            }
+            exit;
+        }
+    }
+
+    public function getInvCoaMappingDetail()
+    {
+        try {
+            if (!$this->isLogin) {
+                $this->sys_error("invalid token");
+                exit;
+            }
+
+            $para = $this->sys_input;
+
+            $sql = "SELECT
+                M_InventarisCoaMappingID,
+                M_InventarisCoaMappingM_InventarisGolID,
+                M_InventarisCoaMappingCoaInventarisID,
+                M_InventarisCoaMappingCoaHutangID,
+                M_InventarisCoaMappingCoaPembelianID,
+                M_InventarisCoaMappingCoaBebanPenyusutanID,
+                M_InventarisCoaMappingCoaAkumulasiPenyusutanID,
+                M_InventarisCoaMappingCoaLabaPelepasanID,
+                M_InventarisCoaMappingCoaRugiPelepasanID,
+                M_InventarisCoaMappingCreatedUserID,
+                M_InventarisCoaMappingCreated,
+                M_InventarisCoaMappingLastUpdated
+            FROM m_inventaris_coa_mapping
+            WHERE M_InventarisCoaMappingIsActive = 'Y'
+                AND M_InventarisCoaMappingID = ?";
+            $query = $this->db->query($sql, [$para['M_InventarisCoaMappingID']]);
+            if (!$query) {
+                $this->sys_error_db("[Error] get data m_inventaris_coa_mapping");
+                exit;
+            }
+            $data = $query->row_array();
+
+            $this->sys_ok($data);
+        } catch (Exception $exc) {
+            $msg = $exc->getMessage();
+            $this->sys_error($msg);
+        }
+    }
+
     ## MUTATIONS ##
-    public function createInvCoaMapping() {
+    public function createInvCoaMapping()
+    {
         try {
             if (!$this->isLogin) {
                 $this->sys_error("invalid token");
@@ -64,48 +284,8 @@ class InventarisCoaMapping extends MY_Controller {
         }
     }
 
-    ## QUERY ##
-    public function getInvCoaMapping() {
-        try {
-            if (!$this->isLogin) {
-                $this->sys_error("invalid token");
-                exit;
-            }
-
-            $para = $this->sys_input;
-
-            $sql = "SELECT
-                M_InventarisCoaMappingID,
-                M_InventarisCoaMappingM_InventarisGolID,
-                M_InventarisCoaMappingCoaInventarisID,
-                M_InventarisCoaMappingCoaHutangID,
-                M_InventarisCoaMappingCoaPembelianID,
-                M_InventarisCoaMappingCoaBebanPenyusutanID,
-                M_InventarisCoaMappingCoaAkumulasiPenyusutanID,
-                M_InventarisCoaMappingCoaLabaPelepasanID,
-                M_InventarisCoaMappingCoaRugiPelepasanID,
-                M_InventarisCoaMappingCreatedUserID,
-                M_InventarisCoaMappingCreated,
-                M_InventarisCoaMappingLastUpdated
-            FROM m_inventaris_coa_mapping
-            WHERE M_InventarisCoaMappingIsActive = 'Y'
-                AND M_InventarisCoaMappingID = ?";
-            $query = $this->db->query($sql, [$para['M_InventarisCoaMappingID']]);
-            if (!$query) {
-                $this->sys_error_db("[Error] get data m_inventaris_coa_mapping");
-                exit;
-            }
-            $data = $query->row_array();
-
-            $this->sys_ok($data);
-        } catch (Exception $exc) {
-            $msg = $exc->getMessage();
-            $this->sys_error($msg);
-        }
-    }
-
-    ## MUTATIONS ##
-    public function editInvCoaMapping() {
+    public function editInvCoaMapping()
+    {
         try {
             if (!$this->isLogin) {
                 $this->sys_error("invalid token");
@@ -154,7 +334,8 @@ class InventarisCoaMapping extends MY_Controller {
         }
     }
 
-    public function deleteInvCoaMapping() {
+    public function deleteInvCoaMapping()
+    {
         try {
             if (!$this->isLogin) {
                 $this->sys_error("invalid token");
@@ -185,8 +366,129 @@ class InventarisCoaMapping extends MY_Controller {
         }
     }
 
+
+    ## QUERY ITEM ##
+    public function getListItemInventaris()
+    {
+        try {
+            if (!$this->isLogin) {
+                throw new Exception('Invalid token');
+            }
+
+            $para = $this->sys_input;
+
+            $sql = "SELECT
+                    M_ItemID,
+                    M_ItemCode,
+                    M_ItemDesc,
+                    M_ItemM_InventarisGolID AS itemGolID,
+                    IFNULL(M_InventarisItemCoaMappingID, 0) AS itemCoaMapID,
+                    M_InventarisItemCoaMappingCoaInventarisID,
+                    coaInv.coaAccountNo          AS CoaInventarisAccountNo,
+                    coaInv.coaDescription        AS CoaInventarisDescription,
+                    M_InventarisItemCoaMappingCoaHutangID,
+                    coaHtg.coaAccountNo          AS CoaHutangAccountNo,
+                    coaHtg.coaDescription        AS CoaHutangDescription,
+                    M_InventarisItemCoaMappingCoaBebanPenyusutanID,
+                    coaBbn.coaAccountNo          AS CoaBebanPenyusutanAccountNo,
+                    coaBbn.coaDescription        AS CoaBebanPenyusutanDescription,
+                    M_InventarisItemCoaMappingCoaAkumulasiPenyusutanID,
+                    coaAkm.coaAccountNo          AS CoaAkumulasiPenyusutanAccountNo,
+                    coaAkm.coaDescription        AS CoaAkumulasiPenyusutanDescription,
+                    M_InventarisItemCoaMappingCoaLabaPelepasanID,
+                    coaLab.coaAccountNo          AS CoaLabaPelepasanAccountNo,
+                    coaLab.coaDescription        AS CoaLabaPelepasanDescription,
+                    M_InventarisItemCoaMappingCoaRugiPelepasanID,
+                    coaRug.coaAccountNo          AS CoaRugiPelepasanAccountNo,
+                    coaRug.coaDescription        AS CoaRugiPelepasanDescription
+                FROM m_item
+                LEFT JOIN m_inventaris_item_coa_mapping 
+                    ON M_ItemID = M_InventarisItemCoaMappingM_ItemID
+                    AND M_InventarisItemCoaMappingIsActive = 'Y'
+                LEFT JOIN coa AS coaInv
+                    ON M_InventarisItemCoaMappingCoaInventarisID = coaInv.coaID
+                LEFT JOIN coa AS coaHtg
+                    ON M_InventarisItemCoaMappingCoaHutangID = coaHtg.coaID
+                LEFT JOIN coa AS coaBbn
+                    ON M_InventarisItemCoaMappingCoaBebanPenyusutanID = coaBbn.coaID
+                LEFT JOIN coa AS coaAkm
+                    ON M_InventarisItemCoaMappingCoaAkumulasiPenyusutanID = coaAkm.coaID
+                LEFT JOIN coa AS coaLab
+                    ON M_InventarisItemCoaMappingCoaLabaPelepasanID = coaLab.coaID
+                LEFT JOIN coa AS coaRug
+                    ON M_InventarisItemCoaMappingCoaRugiPelepasanID = coaRug.coaID
+                WHERE M_ItemItem_CategoryID = 2
+                    AND M_ItemM_InventarisGolID = ?
+                    AND M_ItemIsActive = 'Y'";
+            $que = $this->db->query($sql, [
+                $para['golID']
+            ]);
+            if (!$que) {
+                throw new Exception('failed to query data inventaris gol', 1);
+            }
+            $data = $que->result_array();
+
+            $output = [
+                'records' => $data,
+                'total' => count($data)
+            ];
+
+            $this->sys_ok($output);
+        } catch (Exception $e) {
+            $msg = '[Error] ' . $e->getMessage();
+            $code = $e->getCode();
+            if ($code == 0) {
+                $this->sys_error($msg);
+            } else {
+                $this->sys_error_db($msg);
+            }
+            exit;
+        }
+    }
+
+    public function getInvItemCoaMapping()
+    {
+        try {
+            if (!$this->isLogin) {
+                $this->sys_error("invalid token");
+                exit;
+            }
+
+            $para = $this->sys_input;
+
+            $sql = "SELECT
+                M_InventarisItemCoaMappingID,
+                M_InventarisItemCoaMappingM_ItemID,
+                M_InventarisItemCoaMappingCoaInventarisID,
+                M_InventarisItemCoaMappingCoaHutangID,
+                M_InventarisItemCoaMappingCoaPembelianID,
+                M_InventarisItemCoaMappingCoaBebanPenyusutanID,
+                M_InventarisItemCoaMappingCoaAkumulasiPenyusutanID,
+                M_InventarisItemCoaMappingCoaLabaPelepasanID,
+                M_InventarisItemCoaMappingCoaRugiPelepasanID,
+                M_InventarisItemCoaMappingCreatedUserID,
+                M_InventarisItemCoaMappingCreated,
+                M_InventarisItemCoaMappingLastUpdated
+            FROM m_inventaris_item_coa_mapping
+            WHERE M_InventarisItemCoaMappingIsActive = 'Y'
+                AND M_InventarisItemCoaMappingID = ?";
+            $query = $this->db->query($sql, [$para['M_InventarisItemCoaMappingID']]);
+            if (!$query) {
+                $this->sys_error_db("[Error] get data m_inventaris_item_coa_mapping");
+                exit;
+            }
+            $data = $query->row_array();
+
+            $this->sys_ok($data);
+        } catch (Exception $exc) {
+            $msg = $exc->getMessage();
+            $this->sys_error($msg);
+        }
+    }
+
     ## MUTATIONS ITEM ##
-    public function createInvItemCoaMapping() {
+    public function createInvItemCoaMapping()
+    {
         try {
             if (!$this->isLogin) {
                 $this->sys_error("invalid token");
@@ -231,55 +533,15 @@ class InventarisCoaMapping extends MY_Controller {
             $insertID = $this->db->insert_id();
 
             $this->db->trans_commit();
-            $this->sys_ok($insertID);
+            $this->sys_ok("[Success] insert coa item");
         } catch (Exception $exc) {
             $msg = $exc->getMessage();
             $this->sys_error($msg);
         }
     }
 
-    ## QUERY ITEM ##
-    public function getInvItemCoaMapping() {
-        try {
-            if (!$this->isLogin) {
-                $this->sys_error("invalid token");
-                exit;
-            }
-
-            $para = $this->sys_input;
-
-            $sql = "SELECT
-                M_InventarisItemCoaMappingID,
-                M_InventarisItemCoaMappingM_ItemID,
-                M_InventarisItemCoaMappingCoaInventarisID,
-                M_InventarisItemCoaMappingCoaHutangID,
-                M_InventarisItemCoaMappingCoaPembelianID,
-                M_InventarisItemCoaMappingCoaBebanPenyusutanID,
-                M_InventarisItemCoaMappingCoaAkumulasiPenyusutanID,
-                M_InventarisItemCoaMappingCoaLabaPelepasanID,
-                M_InventarisItemCoaMappingCoaRugiPelepasanID,
-                M_InventarisItemCoaMappingCreatedUserID,
-                M_InventarisItemCoaMappingCreated,
-                M_InventarisItemCoaMappingLastUpdated
-            FROM m_inventaris_item_coa_mapping
-            WHERE M_InventarisItemCoaMappingIsActive = 'Y'
-                AND M_InventarisItemCoaMappingID = ?";
-            $query = $this->db->query($sql, [$para['M_InventarisItemCoaMappingID']]);
-            if (!$query) {
-                $this->sys_error_db("[Error] get data m_inventaris_item_coa_mapping");
-                exit;
-            }
-            $data = $query->row_array();
-
-            $this->sys_ok($data);
-        } catch (Exception $exc) {
-            $msg = $exc->getMessage();
-            $this->sys_error($msg);
-        }
-    }
-
-    ## MUTATIONS ITEM ##
-    public function editInvItemCoaMapping() {
+    public function editInvItemCoaMapping()
+    {
         try {
             if (!$this->isLogin) {
                 $this->sys_error("invalid token");
@@ -328,7 +590,8 @@ class InventarisCoaMapping extends MY_Controller {
         }
     }
 
-    public function deleteInvItemCoaMapping() {
+    public function deleteInvItemCoaMapping()
+    {
         try {
             if (!$this->isLogin) {
                 $this->sys_error("invalid token");

application/controllers/mockup/purchase/faktur/Fakturv4.php

@@ -496,6 +496,7 @@ class Fakturv4 extends MY_Controller
                         ELSE ''
                     END as WarehouseName,
                     ReceiveOrderPoID,
+                    ReceiveOrderPoTypePurchase,
                     PurchaseOrderItemCategoryID
                 FROM supplier_invoice
                 JOIN supplier_invoice_detail ON SupplierInvoiceID = SupplierInvoiceDetailSupplierInvoiceID
@@ -588,12 +589,11 @@ class Fakturv4 extends MY_Controller
                     'P'
                 ) AS DiscountType,
                 SupplierInvoiceReceiveOrderPoID,
-                ReceiveOrderPoNumber
+                ReceiveOrderPoNumber,
+                ReceiveOrderPoTypePurchase AS typePurchase
                 FROM supplier_invoice
                 JOIN supplier ON SupplierInvoiceSupplierID = SupplierID
                 JOIN receive_order_po ON ReceiveOrderPoID = SupplierInvoiceReceiveOrderPoID
-                -- JOIN purchase_order ON SupplierInvoicePurchaseOrderID = PurchaseOrderID
-                -- AND PurchaseOrderIsActive = 'Y'
                 WHERE SupplierInvoiceIsActive = 'Y'
                     AND SupplierInvoiceID = ?";
             $que = $this->db->query($sql, $para['SInvoiceID']);
@@ -601,6 +601,7 @@ class Fakturv4 extends MY_Controller
                 $this->sys_error_db('[Error] get detail data invoice');
                 exit;
             }
+            $data = $que->row_array();
 
             $sqldet = "SELECT
                     SupplierInvoiceDetailID,
@@ -636,7 +637,30 @@ class Fakturv4 extends MY_Controller
                 exit;
             }
 
-            $data = $que->result_array()[0];
+            if (isset($data['typePurchase']) && $data['typePurchase'] == 'aset') {
+                $sql_dp = "SELECT
+                        IFNULL(SupplierDownpaymentAmount, 0) AS dp_amount
+                    FROM supplier_invoice
+                    JOIN receive_order_po
+                        ON ReceiveOrderPoID = SupplierInvoiceReceiveOrderPoID
+                    JOIN purchase_order_asset_contract
+                        ON PurchaseOrderAssetContractReceiveOrderPoID = ReceiveOrderPoID
+                        AND PurchaseOrderAssetContractIsActive = 'Y'
+                    JOIN supplier_downpayment
+                        ON SupplierDownpaymentPurchasOrderID = PurchaseOrderAssetContractPurchaseOrderID
+                        AND SupplierDownpaymentIsActive = 'Y'
+                    WHERE SupplierInvoiceID = ?";
+                $que_dp = $this->db->query($sql_dp, [
+                    $para['SInvoiceID']
+                ]);
+                if (!$que_dp) {
+                    $this->sys_error_db("[Error] get info contract asset");
+                    exit;
+                }
+                $dp_amount = $que_dp->row_array()['dp_amount'];
+                $data['dp_amount'] = $dp_amount;
+            }
+
             $data['detail'] = $quedet->result_array();
 
             $result = $data;
@@ -1317,7 +1341,8 @@ class Fakturv4 extends MY_Controller
                     supplier_invoice_detail.*,
                     ReceiveOrderPoNumber,
                     ReceiveOrderPoM_BranchCode,
-                    ReceiveOrderPoS_RegionalID
+                    ReceiveOrderPoS_RegionalID,
+                    ReceiveOrderPoTypePurchase AS typePurchase
                 FROM supplier_invoice
                 JOIN supplier_invoice_detail ON SupplierInvoiceDetailSupplierInvoiceID = SupplierInvoiceID
                     AND SupplierInvoiceDetailIsActive = 'Y'
@@ -1453,7 +1478,6 @@ class Fakturv4 extends MY_Controller
             $coapph = $quepph->result_array()[0];
 
             $pphval = doubleval($invoice[0]['SupplierInvoiceTaxAmountPph']);
-
             if ($pphval > 0.00) {
                 $insertpph = $this->InsertJurnalTx(
                     $jurnalID,
@@ -1465,22 +1489,22 @@ class Fakturv4 extends MY_Controller
                 );
                 if (!$insertpph['status']) {
                     $this->db->trans_rollback();
-                    $this->sys_error_db($insertpajak['msg']);
+                    $this->sys_error_db($insertpph['msg']);
                     exit;
                 }
             }
 
             # insert shipping cost ke jurnal #
             // ----------------------------------------------------------------------------------------
-            $queryshipcost = $this->db->query($sqlcoa, ['5320700001']);
-            if (!$queryshipcost) {
-                $this->db->trans_rollback();
-                $this->sys_error_db("[Error] get coa cost ");
-                exit;
-            }
-            $coashipcost = $queryshipcost->result_array()[0];
-
             if ($ship_cost > 0.00) {
+                $queryshipcost = $this->db->query($sqlcoa, ['5320700001']);
+                if (!$queryshipcost) {
+                    $this->db->trans_rollback();
+                    $this->sys_error_db("[Error] get coa cost ");
+                    exit;
+                }
+                $coashipcost = $queryshipcost->result_array()[0];
+
                 $insertcost = $this->InsertJurnalTx(
                     $jurnalID,
                     $coashipcost['coaID'],
@@ -1496,6 +1520,63 @@ class Fakturv4 extends MY_Controller
                 }
             }
 
+            /* Insert jurnal tx for down payment asset */
+            // ----------------------------------------------------------------------------------------
+            if (isset($invoice[0]['typePurchase']) && $invoice[0]['typePurchase'] == 'aset') {
+                $sql_dpasset = "SELECT
+                        PurchaseOrderAssetContractID,
+                        PurchaseOrderAssetContractName,
+                        SupplierDownpaymentAmount,
+                        coaID,
+                        coaDescription
+                    FROM purchase_order_asset_contract
+                    JOIN purchase_order ON PurchaseOrderID =  PurchaseOrderAssetContractPurchaseOrderID
+                    JOIN supplier_downpayment
+                        ON SupplierDownpaymentPurchasOrderID = PurchaseOrderID
+                        AND SupplierDownpaymentIsActive = 'Y'
+                    JOIN purchase_order_detail
+                        ON PurchaseOrderID = PurchaseOrderDetailPurchaseOrderID
+                        AND PurchaseOrderDetailIsActive = 'Y'
+                    JOIN m_item ON M_ItemID = PurchaseOrderDetailItemID
+                        AND M_ItemItem_CategoryID = 3
+                    JOIN fa_class ON Fa_ClassID = M_ItemFa_ClassID
+                        AND Fa_ClassIsActive = 'Y'
+                    JOIN coa ON coaID = Fa_ClassDownPaymentCoaID
+                        AND coaIsActive = 'Y'
+                    WHERE PurchaseOrderAssetContractReceiveOrderPoID = ?
+                        AND PurchaseOrderAssetContractIsActive = 'Y'
+                    LIMIT 1";
+                $que_dpasset = $this->db->query($sql_dpasset, [
+                    $invoice[0]['SupplierInvoiceReceiveOrderPoID']
+                ]);
+                if (!$que_dpasset) {
+                    $this->db->trans_rollback();
+                    $this->sys_error_db("[Error] query dp asset not error");
+                    exit;
+                }
+                $dpasset_coa = $que_dpasset->row_array();
+                if (!empty($dpasset_coa)) {
+                    $totalDP = round($dpasset_coa['SupplierDownpaymentAmount'], 2);
+                    if ($totalDP > 0) {
+                        $insert_dp = $this->InsertJurnalTx(
+                            $jurnalID,
+                            $dpasset_coa['coaID'],
+                            $dpasset_coa['coaDescription'],
+                            $user['M_UserID'],
+                            0,
+                            $totalDP
+                        );
+                        if (!$insert_dp['status']) {
+                            $this->db->trans_rollback();
+                            $this->sys_error_db($insert_dp['msg']);
+                            exit;
+                        }
+
+                        $invoice = $this->CalcProrateDownPaymentAsset($invoice, $totalDP);
+                    }
+                }
+            }
+
             // insert jurnal tx hutang per item (kredit)
             // ----------------------------------------------------------------------------------------
             foreach ($invoice as $key => $inv) {
@@ -1603,52 +1684,20 @@ class Fakturv4 extends MY_Controller
                         $this->sys_error_db($status['msg']);
                         exit;
                     }
-                } else if ($item['M_ItemItem_CategoryID'] == '3' && !empty($item['M_ItemFa_ClassID'])) {
-                    $sqlsgp = "SELECT * FROM fa_class WHERE Fa_ClassID = ? AND Fa_ClassIsActive = 'Y'";
-                    $quesgp = $this->db->query($sqlsgp, [$item['M_ItemFa_ClassID']]);
-                    if (!$quesgp) {
-                        $this->db->trans_rollback();
-                        $this->sys_error_db("[Error] get map_nat_subgroup");
-                        exit;
-                    }
-                    $itemGroup = $quesgp->row_array();
-
-                    if (empty($itemGroup)) {
-                        $this->db->trans_rollback();
-                        $this->sys_error_db("[Error] fa class item tidak ditemukan / belum dimapping");
-                        exit;
-                    }
-
-                    $price = (doubleval($inv['SupplierInvoiceDetailTotal']) - doubleval($inv['SupplierInvoiceDetailDiscountPoProrata']));
-                    // $taxPPH  =  doubleval($inv['SupplierInvoiceTaxPercentPph']) * $price / 100;
-                    $taxPPN  =  doubleval($inv['SupplierInvoiceTaxPercentPpn']) * $price / 100;
-
-                    $kredit = round($price +  $taxPPN, 2);
-
-                    $status = $this->InsertJurnalTx(
-                        $jurnalID,
-                        $itemGroup['Fa_ClassCoaID'],
-                        $itemGroup['Fa_ClassCoaDesc'],
-                        $user['M_UserID'],
-                        0,
-                        $kredit
-                    );
-                    if (!$status) {
-                        $this->sys_error_db($status['msg']);
-                        exit;
-                    }
+                } else if ($item['M_ItemItem_CategoryID'] == '3') {
+                    $this->InsertJurnalTxAsset($jurnalID, $item['M_ItemID'], $inv, $user['M_UserID']);
                 } else if ($item['M_ItemItem_CategoryID'] == '4') {
                     $this->InsertJurnalTxJasa($jurnalID, $item['M_ItemID'], $inv, $user['M_UserID']);
                 }
             }
 
             $sqlinvgr = "INSERT INTO jurnal_addon (
-                jurnalAddOnJurnalID,
-                jurnalAddOnCode,
-                jurnalAddOnValue,
-                jurnalAddOnCreated,
-                jurnalAddOnCreatedUserID
-            ) VALUES (?,?,?,NOW(),?)";
+                    jurnalAddOnJurnalID,
+                    jurnalAddOnCode,
+                    jurnalAddOnValue,
+                    jurnalAddOnCreated,
+                    jurnalAddOnCreatedUserID
+                ) VALUES (?,?,?,NOW(),?)";
             $queinvgr = $this->db->query($sqlinvgr, [
                 $jurnalID,
                 'INVGR',
@@ -1674,13 +1723,13 @@ class Fakturv4 extends MY_Controller
     private function InsertJurnalTxJasa($jurnalID, $itemid, $invoice, $userid)
     {
         $sql_coa = "SELECT
-            JasaCoaMapHutangCoaID,
-            JasaCoaMapHutangCoaNo,
-            JasaCoaMapHutangCoaDesc
-        FROM jasa_coa_map
-        JOIN coa ON coaID = JasaCoaMapHutangCoaID
-            AND coaIsActive = 'Y'
-        WHERE JasaCoaMapM_ItemID = ?";
+                JasaCoaMapHutangCoaID,
+                JasaCoaMapHutangCoaNo,
+                JasaCoaMapHutangCoaDesc
+            FROM jasa_coa_map
+            JOIN coa ON coaID = JasaCoaMapHutangCoaID
+                AND coaIsActive = 'Y'
+            WHERE JasaCoaMapM_ItemID = ?";
         $que_coa = $this->db->query($sql_coa, [$itemid]);
         if (!$que_coa) {
             $this->db->trans_rollback();
@@ -1717,6 +1766,93 @@ class Fakturv4 extends MY_Controller
         }
     }
 
+    private function InsertJurnalTxAsset($jurnalID, $itemid, $invoice, $userid)
+    {
+        $sql_coa = "SELECT
+                Fa_ClassHutangCoaID,
+                Fa_ClassHutangCoaAccountNo,
+                Fa_ClassHutangCoaDesc
+            FROM m_item
+            JOIN fa_class ON Fa_ClassID = M_ItemFa_ClassID
+            WHERE M_ItemID = ?";
+        $que_coa = $this->db->query($sql_coa, $itemid);
+        if (!$que_coa) {
+            $this->db->trans_rollback();
+            $this->sys_error_db("[Error] failed get coa hutang asset");
+            exit;
+        }
+        $coaasset = $que_coa->row_array();
+        if ($que_coa->num_rows() <= 0) {
+            $this->db->trans_rollback();
+            $this->sys_error_db("[Error] aset debt coa not found");
+            exit;
+        }
+
+        $invtotal = doubleval($invoice['SupplierInvoiceDetailTotal']);
+        $disprorata = doubleval($invoice['SupplierInvoiceDetailDiscountPoProrata']);
+
+        $price = $invtotal - $disprorata;
+        $taxPPN = doubleval($invoice['SupplierInvoiceTaxPercentPpn']) * $price / 100;
+        $kredit = round($price + $taxPPN, 2);
+        $finalValue = round($kredit - doubleval($invoice['dpReduction']), 2);
+
+        $insert = $this->InsertJurnalTx(
+            $jurnalID,
+            $coaasset['Fa_ClassHutangCoaID'],
+            $coaasset['Fa_ClassHutangCoaDesc'],
+            $userid,
+            0,
+            $finalValue
+        );
+        if (!$insert) {
+            $this->db->trans_rollback();
+            $this->sys_error_db($insert['msg']);
+            exit;
+        }
+    }
+
+    private function CalcProrateDownPaymentAsset($items, $dpAmount)
+    {
+        $totalKredit = 0;
+        foreach ($items as $key => $value) {
+            $invtotal = doubleval($value['SupplierInvoiceDetailTotal']);
+            $disprorata = doubleval($value['SupplierInvoiceDetailDiscountPoProrata']);
+
+            $price = $invtotal - $disprorata;
+            $taxPPN = doubleval($value['SupplierInvoiceTaxPercentPpn']) * $price / 100;
+            $kredit = round($price + $taxPPN, 2);
+            $totalKredit = $totalKredit + $kredit;
+
+            $items[$key]['kredit'] = $kredit;
+        }
+
+        if ($totalKredit <= 0) {
+            foreach ($items as $key => $value) {
+                $items[$key]['dpReduction'] = 0;
+            }
+            return $items;
+        }
+
+        $runningReduction = 0;
+        end($items);
+        $lastKey = key($items);
+        reset($items);
+
+        foreach ($items as $key => $value) {
+            if ($key === $lastKey) {
+                $reduction = round($dpAmount - $runningReduction, 2);
+            } else {
+                $ratio = $value['kredit'] / $totalKredit;
+                $reduction = round($dpAmount * $ratio, 2);
+                $runningReduction += $reduction;
+            }
+
+            $items[$key]['dpReduction'] = $reduction;
+        }
+
+        return $items;
+    }
+
     function InsertJurnalTx($jurnalID, $coaID, $desc, $userID, $debit = 0, $kredit = 0): array
     {
         try {

application/controllers/mockup/receive-item-po/ReceiveItemPoInventaris.php

@@ -1705,22 +1705,38 @@ class ReceiveItemPoInventaris extends MY_Controller
                         exit;
                     }
 
-                    $sql_coa_inv = "SELECT
-                            CoaMapInventarisCoaID,
-                            CoaMapInventarisCoaNo,
-                            CoaMapInventarisCoaDesc
-                        FROM coa_map_inventaris
-                        WHERE CoaMapInventarisM_InventarisGolID = ?
-                            AND CoaMapInventarisIsActive = 'Y'";
-                    $que_coa_inv = $this->db->query($sql_coa_inv, [$item_cek['M_ItemM_InventarisGolID']]);
-                    if (!$que_coa_inv) {
+                    $sql_invcoa = "SELECT
+                            COALESCE(
+                                i.M_InventarisItemCoaMappingID,
+                                g.M_InventarisCoaMappingID
+                            )                                                   AS MappingID,
+                            COALESCE(
+                                i.M_InventarisItemCoaMappingCoaInventarisID,
+                                g.M_InventarisCoaMappingCoaInventarisID
+                            )                                                   AS CoaInventarisID,
+                            c.coaDescription                                    AS CoaInventarisDesc
+                        FROM (SELECT 1) AS inventory
+                        LEFT JOIN m_inventaris_item_coa_mapping i
+                            ON i.M_InventarisItemCoaMappingM_ItemID = ?
+                            AND i.M_InventarisItemCoaMappingIsActive = 'Y'
+                        LEFT JOIN m_inventaris_coa_mapping g
+                            ON g.M_InventarisCoaMappingM_InventarisGolID = ?
+                            AND g.M_InventarisCoaMappingIsActive = 'Y'
+                        LEFT JOIN coa c
+                            ON c.coaID = COALESCE(i.M_InventarisItemCoaMappingCoaInventarisID, g.M_InventarisCoaMappingCoaInventarisID)
+                            AND c.coaIsActive = 'Y'";
+                    $que_invcoa = $this->db->query($sql_invcoa, [
+                        $item_cek['M_ItemID'],
+                        $item_cek['M_ItemM_InventarisGolID']
+                    ]);
+                    if (!$que_invcoa) {
                         $this->db->trans_rollback();
                         $this->sys_error_db("[Error] failed to get coa biaya item inventaris");
                         exit;
                     }
-                    $iteminv_coa = $que_coa_inv->row_array();
+                    $iteminv_coa = $que_invcoa->row_array();
 
-                    if (empty($iteminv_coa['CoaMapInventarisCoaID'])) {
+                    if (empty($iteminv_coa['CoaInventarisID'])) {
                         $this->db->trans_rollback();
                         $this->sys_error_db("[Error] coa gol inventaris not found");
                         exit;
@@ -1740,8 +1756,8 @@ class ReceiveItemPoInventaris extends MY_Controller
                     ) VALUES (?,?,?,?,?,?)";
                     $que_tx_jurnal = $this->db->query($sql_tx_jurnal, [
                         $jurnal_ID,
-                        $iteminv_coa['CoaMapInventarisCoaID'],
-                        $iteminv_coa['CoaMapInventarisCoaDesc'],
+                        $iteminv_coa['CoaInventarisID'],
+                        $iteminv_coa['CoaInventarisDesc'],
                         $debet,
                         0,
                         $user['M_UserID']
@@ -1754,7 +1770,7 @@ class ReceiveItemPoInventaris extends MY_Controller
                     }
                     $juranl_tx_ID = $this->db->insert_id();
                     $each_desc[] = "- Add detail Jurnal debet {$item_cek['M_ItemDesc']}
-                        {$iteminv_coa['CoaMapInventarisCoaDesc']} sejumlah {$this->formatRupiah($debet)}";
+                        {$iteminv_coa['CoaInventarisDesc']} sejumlah {$this->formatRupiah($debet)}";
 
                     # INSERT JURNAL ADDON  #
                     $sql_addon_jurnal = "INSERT INTO jurnal_addon (
@@ -1944,20 +1960,6 @@ class ReceiveItemPoInventaris extends MY_Controller
                             $this->sys_error_db("[Error] insert update stock");
                             exit;
                         }
-
-                        // insert stockid barcode barang
-                        // $sql_update_barcode = "UPDATE t_barcode_barang SET
-                        //     T_BarcodeBarangStockID = ?
-                        //     WHERE T_BarcodeBarangReceiveOrderPoDetailID = ?";
-                        // $qry_update_barcode = $this->db->query($sql_update_barcode, [
-                        //     $stock_ID,
-                        //     $detail['ReceiveOrderPoDetailID']
-                        // ]);
-                        // if (!$qry_update_barcode) {
-                        //     $this->db->trans_rollback();
-                        //     $this->sys_error_db("[Error] update barcode stockID", $this->db);
-                        //     exit;
-                        // }
                     } else {
                         $sql_insert_stock = "INSERT INTO stock (
                             StockWarehouseAlmariID,
@@ -1985,23 +1987,9 @@ class ReceiveItemPoInventaris extends MY_Controller
                             exit;
                         }
 
-                        $stockID = $this->db->insert_id();
+                        $stock_ID = $this->db->insert_id();
                         $stock_qty_ori = 0;
                         $stock_qty_end = intval($batch['qty']);
-
-                        // insert stockid barcode barang
-                        // $sql_update_barcode = "UPDATE t_barcode_barang SET
-                        //     T_BarcodeBarangStockID = ?
-                        //     WHERE T_BarcodeBarangReceiveOrderPoDetailID = ?";
-                        // $qry_update_barcode = $this->db->query($sql_update_barcode, [
-                        //     $stockID,
-                        //     $detail['ReceiveOrderPoDetailID']
-                        // ]);
-                        // if (!$qry_update_barcode) {
-                        //     $this->db->trans_rollback();
-                        //     $this->sys_error_db("[Error] update barcode stockID", $this->db);
-                        //     exit;
-                        // }
                     }
 
                     // insert stock card

application/controllers/mockup/scheduler/JurnalPiCicilanOrderAset.http

@@ -0,0 +1,90 @@
+@host = https://accone.aplikasi.web.id/one-api
+@date = 2026-07-14
+@coaBayarID = 1110100001
+@coaHutangID = 2110100030
+
+# =============================================================================
+# JurnalPiCicilanOrderAset — Contoh Pemanggilan Endpoint
+# =============================================================================
+# Urutan penggunaan yang disarankan:
+#   1. ListEligiblePurchaseOrderAssetContracts  → lihat kontrak yang akan diproses
+#   2. GenerateJournalsFromContracts (dryRun=Y) → simulasi tanpa simpan ke DB
+#   3. GenerateJournalsFromContracts (dryRun=N) → generate jurnal sesungguhnya
+#   4. CreateJournal                            → buat jurnal manual 1 per 1
+# =============================================================================
+
+
+### 1. Lihat kontrak aset yang eligible untuk dibuatkan jurnal bulan ini
+GET {{host}}/index.php/mockup/scheduler/JurnalPiCicilanOrderAset/ListEligiblePurchaseOrderAssetContracts
+Content-Type: application/json
+
+{
+    "startDate": "2026-07-01",
+    "endDate": "2026-07-31"
+}
+
+
+### 2. Simulasi generate jurnal (dryRun=Y) — tidak menyimpan ke database
+POST {{host}}/index.php/mockup/scheduler/JurnalPiCicilanOrderAset/GenerateJournalsFromContracts
+Content-Type: application/json
+
+{
+    "date"       : "{{date}}",
+    "dryRun"     : "Y"
+}
+
+
+### 3. Generate jurnal untuk semua kontrak eligible bulan ini (sesungguhnya)
+POST {{host}}/index.php/mockup/scheduler/JurnalPiCicilanOrderAset/GenerateJournalsFromContracts
+Content-Type: application/json
+
+{
+    "date"       : "{{date}}",
+    "dryRun"     : "N"
+}
+
+
+### 4. Generate jurnal untuk 1 kontrak tertentu saja (contractID=99)
+POST {{host}}/index.php/mockup/scheduler/JurnalPiCicilanOrderAset/GenerateJournalsFromContracts
+Content-Type: application/json
+
+{
+    "date"       : "{{date}}",
+    "dryRun"     : "Y",
+    "contractID" : 00
+}
+
+
+### 5. Buat jurnal manual (1 jurnal, input detail sendiri)
+POST {{host}}/index.php/mockup/scheduler/JurnalPiCicilanOrderAset/CreateJournal
+Content-Type: application/json
+
+{
+    "date"           : "{{date}}",
+    "branchCode"     : "BA",
+    "regionalID"     : 8,
+    "branchCompanyID": 1,
+    "jurnalTypeID"   : 11,
+    "title"          : "Jurnal PI Cicilan Order Aset",
+    "description"    : "Cicilan ke-1 dari 12 | Kontrak Kendaraan Operasional | Periode 2026-07",
+    "details"        : [
+        {
+            "coaID"      : {{coaHutangID}},
+            "description": "Hutang cicilan aset - Kendaraan Operasional",
+            "debit"      : 15000000,
+            "credit"     : 0,
+            "addonCode"  : "PIINSTALMENT",
+            "addonValue" : "42",
+            "mItemID"    : 123
+        },
+        {
+            "coaID"      : {{coaBayarID}},
+            "description": "Pembayaran cicilan aset - Kendaraan Operasional",
+            "debit"      : 0,
+            "credit"     : 15000000,
+            "addonCode"  : "PIINSTALMENT",
+            "addonValue" : "42",
+            "mItemID"    : 123
+        }
+    ]
+}

application/controllers/mockup/scheduler/JurnalPiCicilanOrderAset.php

@@ -0,0 +1,970 @@
+<?php
+
+/**
+ * JurnalPiCicilanOrderAset
+ *
+ * Controller scheduler untuk membuat jurnal akuntansi atas Purchase Invoice (PI)
+ * cicilan order aset secara otomatis.
+ *
+ * Alur bisnis proses:
+ *   1. Validasi parameter input (tanggal, branchCode, title, description, details)
+ *   2. Insert header jurnal ke tabel `jurnal`
+ *      - Nomor jurnal di-generate via fn_numbering('J')
+ *      - Tipe jurnal: PAYMENT
+ *      - Berisi: title, description, tanggal, regionalID, kode cabang, branchCompanyID
+ *   3. Insert baris transaksi ke tabel `jurnal_tx` (untuk setiap baris cicilan)
+ *      - Referensi ke jurnalID header
+ *      - COA (Chart of Account) dan deskripsinya:
+ *          * Sisi hutang cicilan → nilai DEBIT (diambil dari HutangCoaID di fa_class)
+ *          * Sisi pembayaran cicilan → nilai KREDIT (diambil dari ItemCoaID di fa_class)
+ *   4. Insert data tambahan ke tabel `jurnal_addon` (jika ada addonValue atau mItemID)
+ *      - Referensi ke jurnalID dan jurnalTxID
+ *      - Berisi: addonCode (default: "PIINSTALMENT"), addonValue, mItemID
+ *
+ * Endpoint:
+ *   POST /mockup/scheduler/JurnalPiCicilanOrderAset/CreateJournal
+ *   POST /mockup/scheduler/JurnalPiCicilanOrderAset/GenerateJournalsFromContracts
+ *   GET  /mockup/scheduler/JurnalPiCicilanOrderAset/ListEligiblePurchaseOrderAssetContracts
+ */
+class JurnalPiCicilanOrderAset extends MY_Controller
+{
+    var $db;
+
+    /**
+     * Endpoint default — menampilkan nama controller sebagai penanda aktif.
+     */
+    public function index()
+    {
+        echo "Jurnal PI Cicilan Order Aset";
+    }
+
+    /**
+     * Konstruktor — inisialisasi parent controller.
+     */
+    public function __construct()
+    {
+        parent::__construct();
+    }
+
+    /**
+     * CreateJournal — Membuat jurnal PI cicilan order aset.
+     *
+     * Proses bisnis:
+     *   1. Validasi semua parameter wajib: date, branchCode, title, description, details
+     *   2. Ambil periodeID dari tanggal transaksi (tabel periode)
+     *   3. Ambil regionalID dari branchCode (tabel m_branch)
+     *   4. Ambil branchCompanyID dari branchCode (tabel m_branch_companydetail)
+     *   5. Ambil jurnalTypeID dari kode tipe jurnal (default: "PAYMENT")
+     *   6. Generate nomor jurnal otomatis via fn_numbering('J')
+     *   7. Validasi balance: total debit harus sama dengan total credit
+     *   8. INSERT header jurnal ke tabel `jurnal`:
+     *      - jurnalNo         : nomor jurnal hasil generate
+     *      - jurnalJurnalTypeID: tipe PAYMENT
+     *      - jurnalTitle      : judul jurnal
+     *      - jurnalDescription: keterangan jurnal
+     *      - jurnalDate       : tanggal transaksi
+     *      - JurnalS_RegionalID: ID regional cabang
+     *      - jurnalM_BranchCode: kode cabang
+     *   9. Untuk setiap baris detail, INSERT ke `jurnal_tx`:
+     *      - jurnalTxJurnalID  : referensi ke header jurnal
+     *      - jurnalTxCoaID     : COA yang digunakan (hutang cicilan / kas/bank)
+     *      - jurnalTxDescription: keterangan baris (nama cicilan/aset)
+     *      - jurnalTxDebit     : nilai hutang (sisi debit)
+     *      - jurnalTxCredit    : nilai bayar (sisi kredit)
+     *  10. Jika ada addonValue atau mItemID, INSERT ke `jurnal_addon`:
+     *      - jurnalAddOnJurnalID  : referensi ke header jurnal
+     *      - jurnalAddOnJurnalTxID: referensi ke baris jurnal_tx
+     *      - jurnalAddOnCode      : kode addon (default: "PIINSTALMENT")
+     *      - jurnalAddOnValue     : nilai tambahan (misal: nomor PI/kontrak)
+     *      - jurnalAddOnM_ItemID  : ID item aset yang terkait
+     *
+     * Parameter (POST JSON):
+     *   - date          : Tanggal jurnal (YYYY-MM-DD). Default: hari ini.
+     *   - branchCode    : Kode cabang. WAJIB.
+     *   - title         : Judul jurnal. WAJIB.
+     *   - description   : Keterangan jurnal. WAJIB.
+     *   - details       : Array baris transaksi. WAJIB, minimal 1 baris.
+     *       - coaID       : ID Chart of Account. WAJIB per baris.
+     *       - description : Keterangan baris.
+     *       - debit       : Nilai debit (hutang cicilan).
+     *       - credit      : Nilai kredit (pembayaran cicilan).
+     *       - addonValue  : (opsional) Nilai addon, misal nomor PI.
+     *       - mItemID     : (opsional) ID item aset.
+     *       - addonCode   : (opsional) Kode addon. Default: "PIINSTALMENT".
+     *   - periodeID     : (opsional) ID periode. Default: diambil dari tanggal.
+     *   - regionalID    : (opsional) ID regional. Default: diambil dari branchCode.
+     *   - branchCompanyID: (opsional) ID branch company. Default: diambil dari branchCode.
+     *   - jurnalTypeID  : (opsional) ID tipe jurnal. Default: diambil dari jurnalTypeCode.
+     *   - jurnalTypeCode: (opsional) Kode tipe jurnal. Default: "PAYMENT".
+     *   - userID        : ID user yang membuat jurnal.
+     *
+     * @return JSON { jurnalID, jurnalNo, detailCount, addonCount }
+     */
+    public function CreateJournal()
+    {
+        try {
+            $para = $this->sys_input;
+
+            $date = isset($para["date"]) && $para["date"] != "" ? $para["date"] : date("Y-m-d");
+            if (!$this->isValidDate($date)) {
+                throw new Exception("Format tanggal tidak valid. Gunakan YYYY-MM-DD.");
+            }
+
+            $branchCode = isset($para["branchCode"]) ? trim($para["branchCode"]) : "";
+            if ($branchCode == "") {
+                throw new Exception("branchCode wajib diisi.");
+            }
+
+            $title = isset($para["title"]) ? trim($para["title"]) : "";
+            if ($title == "") {
+                throw new Exception("title wajib diisi.");
+            }
+
+            $description = isset($para["description"]) ? trim($para["description"]) : "";
+            if ($description == "") {
+                throw new Exception("description wajib diisi.");
+            }
+
+            $details = isset($para["details"]) && is_array($para["details"]) ? $para["details"] : [];
+            if (count($details) == 0) {
+                throw new Exception("details wajib diisi minimal 1 baris.");
+            }
+
+            $periodeID = isset($para["periodeID"]) && (int) $para["periodeID"] > 0
+                ? (int) $para["periodeID"]
+                : $this->getPeriodeIDByDate($date);
+            if ($periodeID <= 0) {
+                throw new Exception("Periode jurnal tidak ditemukan untuk tanggal {$date}.");
+            }
+
+            $regionalID = isset($para["regionalID"]) && (int) $para["regionalID"] > 0
+                ? (int) $para["regionalID"]
+                : $this->getRegionalIDByBranchCode($branchCode);
+            if ($regionalID <= 0) {
+                throw new Exception("regionalID wajib diisi atau harus bisa diturunkan dari branchCode.");
+            }
+
+            $branchCompanyID = isset($para["branchCompanyID"]) && (int) $para["branchCompanyID"] > 0
+                ? (int) $para["branchCompanyID"]
+                : $this->getBranchCompanyIDByBranchCode($branchCode);
+            if ($branchCompanyID <= 0) {
+                throw new Exception("branchCompanyID wajib diisi atau harus bisa diturunkan dari branchCode.");
+            }
+
+            $jurnalTypeID = isset($para["jurnalTypeID"]) && (int) $para["jurnalTypeID"] > 0
+                ? (int) $para["jurnalTypeID"]
+                : $this->getJurnalTypeID(isset($para["jurnalTypeCode"]) && $para["jurnalTypeCode"] != "" ? $para["jurnalTypeCode"] : "PAYMENT");
+            if ($jurnalTypeID <= 0) {
+                throw new Exception("jurnalTypeID / jurnalTypeCode tidak valid.");
+            }
+
+            $userID = 0;
+
+            $result = $this->insertJournal(
+                $date,
+                $branchCode,
+                $title,
+                $description,
+                $details,
+                $periodeID,
+                $regionalID,
+                $branchCompanyID,
+                $jurnalTypeID,
+                $userID
+            );
+
+            if ($result === false) {
+                exit;
+            }
+
+            $this->sys_ok($result);
+        } catch (Exception $exc) {
+            $this->sys_error($exc->getMessage());
+        }
+    }
+
+    /**
+     * GenerateJournalsFromContracts — Generate jurnal otomatis dari seluruh kontrak aset eligible.
+     *
+     * Fungsi ini mengintegrasikan data dari getEligiblePurchaseOrderAssetContracts()
+     * dengan proses pembuatan jurnal (insertJournal()) secara otomatis.
+     *
+     * Untuk setiap kontrak yang eligible, dibuat 1 jurnal dengan 2 baris transaksi:
+     *   - Baris DEBIT  : COA hutang cicilan (HutangCoaID dari fa_class) — mencatat hutang cicilan
+     *   - Baris KREDIT : COA kas/bank/aset (ItemCoaID dari fa_class) — mencatat pembayaran cicilan
+     *   Addon (jurnal_addon): menyimpan ID kontrak sebagai addonValue dan ItemID sebagai mItemID
+     *
+     * Parameter (POST JSON):
+     *   - date       : Tanggal jurnal (YYYY-MM-DD). Default: hari ini.
+     *   - userID     : ID user yang membuat jurnal.
+     *   - dryRun     : "Y" = simulasi tanpa simpan ke DB. Default: "N".
+     *   - contractID : (opsional) Filter hanya 1 kontrak tertentu.
+     *
+     * Syarat kontrak dilewati (skipped):
+     *   - branchCode kosong (data cabang belum lengkap)
+     *   - COA hutang (HutangCoaID) tidak ditemukan di fa_class
+     *   - COA bayar/aset (ItemCoaID) tidak ditemukan di fa_class
+     *
+     * @return JSON { tanggal, totalDibuat, totalDilewati, daftarDibuat[], daftarDilewati[] }
+     */
+    public function GenerateJournalsFromContracts()
+    {
+        try {
+            $para = $this->sys_input;
+
+            $date = isset($para["date"]) && $para["date"] != "" ? $para["date"] : date("Y-m-d");
+            if (!$this->isValidDate($date)) {
+                throw new Exception("Format tanggal tidak valid. Gunakan YYYY-MM-DD.");
+            }
+
+            $userID  = 0;
+            $dryRun  = isset($para["dryRun"])  && ($para["dryRun"] === true || $para["dryRun"] == "Y" || $para["dryRun"] == "1");
+            $filterContractID = isset($para["contractID"]) && (int) $para["contractID"] > 0
+                ? (int) $para["contractID"]
+                : null;
+
+            $monthStart = date("Y-m-01", strtotime($date));
+            $monthEnd   = date("Y-m-t",  strtotime($date));
+
+            // Ambil semua kontrak yang eligible untuk periode bulan ini
+            $contracts = $this->getEligiblePurchaseOrderAssetContracts($monthStart, $monthEnd);
+
+            $dibuat   = [];
+            $dilewati = [];
+
+            foreach ($contracts as $kontrak) {
+                $contractID = (int) $kontrak["PurchaseOrderAssetContractID"];
+
+                // Filter 1 kontrak jika diminta
+                if ($filterContractID !== null && $contractID !== $filterContractID) {
+                    continue;
+                }
+
+                $branchCode      = $kontrak["M_BranchCode"]      ?? "";
+                $regionalID      = (int) ($kontrak["M_BranchS_RegionalID"] ?? 0);
+                $branchCompanyID = (int) ($kontrak["M_BranchCompanyID"]    ?? 0);
+                $coaHutangID     = (int) ($kontrak["HutangCoaID"]          ?? 0); // COA debit dari fa_class (Fa_ClassHutangCoaID)
+                $coaBayarID      = (int) ($kontrak["ItemCoaID"]            ?? 0); // COA kredit dari fa_class (Fa_ClassCoaID)
+                $jumlah          = (float) $kontrak["PurchaseOrderAssetContractInstallmentPayAmount"];
+                $itemID          = (int) ($kontrak["PurchaseOrderSummaryItemID"] ?? 0);
+                $namaKontrak     = $kontrak["PurchaseOrderAssetContractName"] != ""
+                    ? $kontrak["PurchaseOrderAssetContractName"]
+                    : "Kontrak ID {$contractID}";
+                $nomorPO         = $kontrak["PurchaseOrderNumber"] ?? "";
+                $cicilanKe       = ((int) $kontrak["PurchaseOrderAssetContractInstallmentPaid"]) + 1;
+                $totalCicilan    = (int) $kontrak["PurchaseOrderAssetContractInstallmentNumber"];
+                $periode         = date("Y-m", strtotime($date));
+                $invoiceNumber   = $kontrak["SupplierInvoiceNumber"] ?? "";
+                $invoiceID       = (int) ($kontrak["SupplierInvoiceID"] ?? 0);
+
+                // Validasi: branchCode harus ada
+                if ($branchCode == "") {
+                    $dilewati[] = [
+                        "kontrakID" => $contractID,
+                        "alasan"    => "Kode cabang (branchCode) tidak ditemukan. Pastikan warehouse PO sudah terhubung ke data cabang."
+                    ];
+                    continue;
+                }
+
+                // Validasi: COA hutang harus ada (untuk sisi DEBIT)
+                if ($coaHutangID <= 0) {
+                    $dilewati[] = [
+                        "kontrakID"  => $contractID,
+                        "branchCode" => $branchCode,
+                        "alasan"     => "COA hutang tidak ditemukan. Pastikan item PO memiliki Fa_Class dengan Fa_ClassHutangCoaID."
+                    ];
+                    continue;
+                }
+
+                // Validasi: COA bayar/aset harus ada (untuk sisi KREDIT)
+                if ($coaBayarID <= 0) {
+                    $dilewati[] = [
+                        "kontrakID"  => $contractID,
+                        "branchCode" => $branchCode,
+                        "alasan"     => "COA bayar/aset tidak ditemukan. Pastikan item PO memiliki Fa_Class dengan Fa_ClassCoaID."
+                    ];
+                    continue;
+                }
+
+                // Mode simulasi: catat tanpa simpan ke DB
+                if ($dryRun) {
+                    $dibuat[] = [
+                        "kontrakID"      => $contractID,
+                        "branchCode"     => $branchCode,
+                        "namaKontrak"    => $namaKontrak,
+                        "invoiceNumber"  => $invoiceNumber,
+                        "jumlah"         => $jumlah,
+                        "coaHutangID"    => $coaHutangID,
+                        "coaBayarID"     => $coaBayarID,
+                        "keterangan"     => "Mode simulasi (dryRun), data tidak disimpan."
+                    ];
+                    continue;
+                }
+
+                // Susun title dan description jurnal
+                $title       = "PI Cicilan Aset - {$invoiceNumber}";
+                $description = "Jurnal cicilan ke-{$cicilanKe} dari {$totalCicilan} | {$namaKontrak} | PO: {$nomorPO} | Periode {$periode}";
+
+                // Ambil periodeID dari tanggal
+                $periodeID = $this->getPeriodeIDByDate($date);
+                if ($periodeID <= 0) {
+                    $dilewati[] = [
+                        "kontrakID"  => $contractID,
+                        "branchCode" => $branchCode,
+                        "alasan"     => "Periode jurnal tidak ditemukan untuk tanggal {$date}."
+                    ];
+                    continue;
+                }
+
+                // Ambil jurnalTypeID untuk tipe PAYMENT
+                $jurnalTypeID = $this->getJurnalTypeID("PAYMENTINV");
+                if ($jurnalTypeID <= 0) {
+                    $dilewati[] = [
+                        "kontrakID"  => $contractID,
+                        "branchCode" => $branchCode,
+                        "alasan"     => "Tipe jurnal PAYMENT tidak ditemukan di tabel jurnal_type."
+                    ];
+                    continue;
+                }
+
+                // Ambil description detail dari database fa_class dengan fallback jika kosong
+                $descHutang = isset($kontrak["HutangCoaDesc"]) && $kontrak["HutangCoaDesc"] != ""
+                    ? $kontrak["HutangCoaDesc"]
+                    : "Hutang cicilan aset - {$namaKontrak}";
+                $descBayar = isset($kontrak["ItemCoaDesc"]) && $kontrak["ItemCoaDesc"] != ""
+                    ? $kontrak["ItemCoaDesc"]
+                    : "Pembayaran cicilan aset - {$namaKontrak}";
+
+                // Susun 2 baris detail jurnal:
+                //   Baris 1 — DEBIT  : hutang cicilan aset (COA dari fa_class)
+                //   Baris 2 — KREDIT : pembayaran cicilan (COA kas/bank dari parameter)
+                $details = [
+                    [
+                        "coaID"       => $coaHutangID,
+                        "description" => $descHutang,
+                        "debit"       => $jumlah,
+                        "credit"      => 0,
+                        "addonValue"  => $invoiceNumber,
+                        "mItemID"     => $itemID,
+                        "addonCode"   => "PIINSTALMENT"
+                    ],
+                    [
+                        "coaID"       => $coaBayarID,
+                        "description" => $descBayar,
+                        "debit"       => 0,
+                        "credit"      => $jumlah,
+                        "addonValue"  => $invoiceNumber,
+                        "mItemID"     => $itemID,
+                        "addonCode"   => "PIINSTALMENT"
+                    ]
+                ];
+
+
+                // Panggil insertJournal() untuk simpan ke DB
+                $result = $this->insertJournal(
+                    $date,
+                    $branchCode,
+                    $title,
+                    $description,
+                    $details,
+                    $periodeID,
+                    $regionalID,
+                    $branchCompanyID,
+                    $jurnalTypeID,
+                    $userID
+                );
+
+                if ($result === false) {
+                    $dilewati[] = [
+                        "kontrakID"  => $contractID,
+                        "branchCode" => $branchCode,
+                        "alasan"     => "Gagal menyimpan jurnal ke database."
+                    ];
+                    continue;
+                }
+
+                $dibuat[] = [
+                    "kontrakID"   => $contractID,
+                    "branchCode"  => $branchCode,
+                    "namaKontrak" => $namaKontrak,
+                    "jurnalID"    => $result["jurnalID"],
+                    "jurnalNo"    => $result["jurnalNo"],
+                    "jumlah"      => $jumlah
+                ];
+            }
+
+            $this->sys_ok([
+                "tanggal"        => $date,
+                "periodeAwal"    => $monthStart,
+                "periodeAkhir"   => $monthEnd,
+                "totalDibuat"    => count($dibuat),
+                "totalDilewati"  => count($dilewati),
+                "daftarDibuat"   => $dibuat,
+                "daftarDilewati" => $dilewati
+            ]);
+        } catch (Exception $exc) {
+            $this->sys_error($exc->getMessage());
+        }
+    }
+
+
+    /**
+     * ListEligiblePurchaseOrderAssetContracts — Menampilkan daftar kontrak aset
+     * yang memenuhi syarat untuk dibuatkan jurnal PI cicilan pada periode tertentu.
+     *
+     * Syarat kontrak eligible:
+     *   - Kontrak aktif dan berstatus "belum lunas"
+     *   - Nilai cicilan > 0
+     *   - Jumlah cicilan terbayar < total cicilan
+     *   - Tanggal kontrak masuk dalam rentang startDate–endDate
+     *   - PO sudah berstatus Approved
+     *   - Barang sudah diterima (receive_order_po sudah confirmed)
+     *
+     * Endpoint ini digunakan untuk preview/verifikasi sebelum proses generate jurnal
+     * dijalankan secara massal oleh CRON.
+     *
+     * Parameter (GET/POST):
+     *   - startDate : Awal periode (YYYY-MM-DD). Default: tanggal 1 bulan ini.
+     *   - endDate   : Akhir periode (YYYY-MM-DD). Default: tanggal akhir bulan ini.
+     *
+     * @return JSON { startDate, endDate, total, records[] }
+     */
+    public function ListEligiblePurchaseOrderAssetContracts()
+    {
+        try {
+            $para = $this->sys_input;
+
+            $startDate = isset($para["startDate"]) && $para["startDate"] != ""
+                ? $para["startDate"]
+                : date("Y-m-01");
+            $endDate = isset($para["endDate"]) && $para["endDate"] != ""
+                ? $para["endDate"]
+                : date("Y-m-t");
+
+            if (!$this->isValidDate($startDate) || !$this->isValidDate($endDate)) {
+                throw new Exception("Format tanggal tidak valid. Gunakan YYYY-MM-DD.");
+            }
+
+            if (strtotime($startDate) > strtotime($endDate)) {
+                throw new Exception("startDate tidak boleh lebih besar dari endDate");
+            }
+
+            $records = $this->getEligiblePurchaseOrderAssetContracts($startDate, $endDate);
+
+            $this->sys_ok([
+                "startDate" => $startDate,
+                "endDate"   => $endDate,
+                "total"     => count($records),
+                "records"   => $records
+            ]);
+        } catch (Exception $exc) {
+            $this->sys_error($exc->getMessage());
+        }
+    }
+
+    // =========================================================================
+    // FUNGSI PRIVATE / HELPER
+    // =========================================================================
+
+    /**
+     * insertJournal — Menyimpan 1 jurnal lengkap ke database (header + tx + addon).
+     *
+     * Fungsi internal yang dipanggil oleh CreateJournal() dan GenerateJournalsFromContracts().
+     * Mengelola transaksi DB secara penuh (begin → commit/rollback).
+     *
+     * Alur:
+     *   1. Validasi balance debit == kredit
+     *   2. Generate nomor jurnal via fn_numbering('J')
+     *   3. INSERT header ke tabel `jurnal`
+     *   4. INSERT baris transaksi ke `jurnal_tx` (per detail)
+     *   5. INSERT addon ke `jurnal_addon` (jika addonValue atau mItemID ada)
+     *
+     * @param string $date           Tanggal jurnal (YYYY-MM-DD)
+     * @param string $branchCode     Kode cabang
+     * @param string $title          Judul jurnal
+     * @param string $description    Keterangan jurnal
+     * @param array  $details        Baris transaksi [ coaID, description, debit, credit, addonValue?, mItemID?, addonCode? ]
+     * @param int    $periodeID      ID periode akuntansi
+     * @param int    $regionalID     ID regional
+     * @param int    $branchCompanyID ID branch company
+     * @param int    $jurnalTypeID   ID tipe jurnal
+     * @param int    $userID         ID user pembuat jurnal
+     *
+     * @return array|false  Array { jurnalID, jurnalNo, detailCount, addonCount }, atau false jika gagal
+     */
+    private function insertJournal($date, $branchCode, $title, $description, $details, $periodeID, $regionalID, $branchCompanyID, $jurnalTypeID, $userID)
+    {
+        // Validasi balance sebelum menyimpan
+        $totalDebit  = 0;
+        $totalCredit = 0;
+        foreach ($details as $idx => $detail) {
+            $debit  = isset($detail["debit"])  ? (float) $detail["debit"]  : 0;
+            $credit = isset($detail["credit"]) ? (float) $detail["credit"] : 0;
+            if ($debit < 0 || $credit < 0) {
+                $this->sys_error("Nilai debit/credit tidak boleh negatif pada detail ke-" . ($idx + 1));
+                return false;
+            }
+            $totalDebit  += $debit;
+            $totalCredit += $credit;
+        }
+
+        if (round($totalDebit, 2) !== round($totalCredit, 2)) {
+            $this->sys_error("Total debit dan credit harus sama. Debit={$totalDebit}, Credit={$totalCredit}");
+            return false;
+        }
+
+        $jurnalNo = $this->generateJournalNumber();
+        if ($jurnalNo === false || $jurnalNo == "") {
+            $this->sys_error("Gagal generate nomor jurnal.");
+            return false;
+        }
+
+        $this->db->trans_begin();
+
+        // -----------------------------------------------------------------
+        // INSERT header jurnal ke tabel `jurnal`
+        // -----------------------------------------------------------------
+        $sqlHeader = "INSERT INTO jurnal (
+                jurnalM_BranchCompanyID,
+                JurnalS_RegionalID,
+                jurnalM_BranchCode,
+                jurnalperiodeID,
+                jurnalNo,
+                jurnalTitle,
+                jurnalDescription,
+                jurnalDate,
+                jurnalJurnalTypeID,
+                jurnalCreated,
+                jurnalM_UserID
+            ) VALUES (?,?,?,?,?,?,?,?,?,NOW(),?)";
+
+        $qryHeader = $this->db->query($sqlHeader, [
+            $branchCompanyID,
+            $regionalID,
+            $branchCode,
+            $periodeID,
+            $jurnalNo,
+            $title,
+            $description,
+            $date,
+            $jurnalTypeID,
+            $userID
+        ]);
+        if (!$qryHeader) {
+            $this->db->trans_rollback();
+            $this->sys_error_db("Gagal insert jurnal header.");
+            return false;
+        }
+
+        $jurnalID         = $this->db->insert_id();
+        $createdAddonCount = 0;
+
+        // -----------------------------------------------------------------
+        // INSERT baris transaksi ke tabel `jurnal_tx` (per baris cicilan)
+        // -----------------------------------------------------------------
+        foreach ($details as $idx => $detail) {
+            $coaID         = isset($detail["coaID"])       ? (int)   $detail["coaID"]    : 0;
+            $descriptionTx = isset($detail["description"]) ? trim($detail["description"]) : "";
+            $debit         = isset($detail["debit"])       ? (float) $detail["debit"]     : 0;
+            $credit        = isset($detail["credit"])      ? (float) $detail["credit"]    : 0;
+
+            if ($coaID <= 0) {
+                $this->db->trans_rollback();
+                $this->sys_error_db("coaID wajib diisi pada detail ke-" . ($idx + 1));
+                return false;
+            }
+
+            // Sisi debit  = hutang cicilan (COA hutang aset dari fa_class)
+            // Sisi kredit = pembayaran cicilan (COA kas/bank)
+            $sqlTx = "INSERT INTO jurnal_tx (
+                    jurnalTxJurnalID,
+                    jurnalTxCoaID,
+                    jurnalTxDescription,
+                    jurnalTxDebit,
+                    jurnalTxCredit,
+                    jurnalTxCreated,
+                    jurnalTxM_UserID
+                ) VALUES (?,?,?,?,?,NOW(),?)";
+
+            $qryTx = $this->db->query($sqlTx, [
+                $jurnalID,
+                $coaID,
+                $descriptionTx,
+                $debit,
+                $credit,
+                $userID
+            ]);
+            if (!$qryTx) {
+                $this->db->trans_rollback();
+                $this->sys_error_db("Gagal insert jurnal_tx pada detail ke-" . ($idx + 1));
+                return false;
+            }
+
+            $jurnalTxID = $this->db->insert_id();
+
+            // -----------------------------------------------------------------
+            // INSERT addon ke `jurnal_addon` (jika addonValue atau mItemID ada)
+            // addonValue  = ID kontrak aset (untuk tracing)
+            // mItemID     = ID item aset dari purchase_order_summary
+            // -----------------------------------------------------------------
+            $addonValue = isset($detail["addonValue"]) ? trim($detail["addonValue"]) : "";
+            $mItemID    = isset($detail["mItemID"])    ? (int) $detail["mItemID"]    : 0;
+            $addonCode  = isset($detail["addonCode"]) && $detail["addonCode"] != "" ? trim($detail["addonCode"]) : "PIINSTALMENT";
+
+            if ($addonValue != "" || $mItemID > 0) {
+                $sqlAddon = "INSERT INTO jurnal_addon (
+                        jurnalAddOnJurnalID,
+                        jurnalAddOnJurnalTxID,
+                        jurnalAddOnCode,
+                        jurnalAddOnValue,
+                        jurnalAddOnM_ItemID,
+                        jurnalAddOnCreated,
+                        jurnalAddOnCreatedUserID
+                    ) VALUES (?,?,?,?,?,NOW(),?)";
+
+                $qryAddon = $this->db->query($sqlAddon, [
+                    $jurnalID,
+                    $jurnalTxID,
+                    $addonCode,
+                    $addonValue,
+                    $mItemID > 0 ? $mItemID : null,
+                    $userID
+                ]);
+                if (!$qryAddon) {
+                    $this->db->trans_rollback();
+                    $this->sys_error_db("Gagal insert jurnal_addon pada detail ke-" . ($idx + 1));
+                    return false;
+                }
+                $createdAddonCount++;
+            }
+        }
+
+        if ($this->db->trans_status() === false) {
+            $this->db->trans_rollback();
+            $this->sys_error_db("Transaksi jurnal gagal.");
+            return false;
+        }
+
+        $this->db->trans_commit();
+
+        return [
+            "jurnalID"    => $jurnalID,
+            "jurnalNo"    => $jurnalNo,
+            "detailCount" => count($details),
+            "addonCount"  => $createdAddonCount
+        ];
+    }
+
+    /**
+     * generateJournalNumber — Generate nomor jurnal otomatis.
+     *
+     * Memanggil stored function MySQL fn_numbering('J') untuk mendapatkan
+     * nomor jurnal berikutnya sesuai urutan penomoran yang berlaku.
+     *
+     * @return string|false  Nomor jurnal (misal: "J/2025/07/0001"), atau false jika gagal.
+     */
+    private function generateJournalNumber()
+    {
+        $qry = $this->db->query("SELECT fn_numbering('J') AS JNumber");
+        if (!$qry || $qry->num_rows() == 0) {
+            return false;
+        }
+        return $qry->row_array()["JNumber"];
+    }
+
+    /**
+     * getPeriodeIDByDate — Ambil ID periode akuntansi berdasarkan tanggal transaksi.
+     *
+     * Periode diambil dari tabel `periode` yang aktif dan mencakup tanggal yang diberikan.
+     * Digunakan untuk mengisi field jurnalperiodeID pada header jurnal.
+     *
+     * @param string $date  Tanggal transaksi (format YYYY-MM-DD)
+     * @return int          periodeID yang sesuai, atau 0 jika tidak ditemukan
+     */
+    private function getPeriodeIDByDate($date)
+    {
+        $sql = "SELECT periodeID
+                FROM periode
+                WHERE periodeIsActive = 'Y'
+                    AND DATE(?) BETWEEN periodeStartDate AND periodeEndDate
+                LIMIT 1";
+        $qry = $this->db->query($sql, [$date]);
+        if (!$qry || $qry->num_rows() == 0) {
+            return 0;
+        }
+        return (int) $qry->row_array()["periodeID"];
+    }
+
+    /**
+     * getBranchCompanyIDByBranchCode — Ambil ID branch company berdasarkan kode cabang.
+     *
+     * Data diambil dari tabel `m_branch_companydetail` yang aktif.
+     * Digunakan untuk mengisi field jurnalM_BranchCompanyID pada header jurnal.
+     *
+     * @param string $branchCode  Kode cabang (misal: "JKT-01")
+     * @return int                branchCompanyID yang sesuai, atau 0 jika tidak ditemukan
+     */
+    private function getBranchCompanyIDByBranchCode($branchCode)
+    {
+        $sql = "SELECT M_BranchCompanyDetailM_BranchCompanyID AS branchCompanyID
+                FROM m_branch_companydetail
+                WHERE M_BranchCompanyDetailM_BranchCode = ?
+                    AND M_BranchCompanyDetailIsActive = 'Y'
+                LIMIT 1";
+        $qry = $this->db->query($sql, [$branchCode]);
+        if (!$qry || $qry->num_rows() == 0) {
+            return 0;
+        }
+        return (int) $qry->row_array()["branchCompanyID"];
+    }
+
+    /**
+     * getRegionalIDByBranchCode — Ambil ID regional berdasarkan kode cabang.
+     *
+     * Data diambil dari tabel `m_branch` yang aktif.
+     * Digunakan untuk mengisi field JurnalS_RegionalID pada header jurnal.
+     *
+     * @param string $branchCode  Kode cabang (misal: "JKT-01")
+     * @return int                regionalID yang sesuai, atau 0 jika tidak ditemukan
+     */
+    private function getRegionalIDByBranchCode($branchCode)
+    {
+        $sql = "SELECT M_BranchS_RegionalID AS regionalID
+                FROM m_branch
+                WHERE M_BranchCode = ?
+                    AND M_BranchIsActive = 'Y'
+                LIMIT 1";
+        $qry = $this->db->query($sql, [$branchCode]);
+        if (!$qry || $qry->num_rows() == 0) {
+            return 0;
+        }
+        return (int) $qry->row_array()["regionalID"];
+    }
+
+    /**
+     * getJurnalTypeID — Ambil ID tipe jurnal berdasarkan kode tipe.
+     *
+     * Data diambil dari tabel `jurnal_type` yang aktif.
+     * Untuk PI cicilan order aset, kode yang digunakan adalah "PAYMENT".
+     *
+     * @param string $jurnalTypeCode  Kode tipe jurnal (misal: "PAYMENT")
+     * @return int                    JurnalTypeID yang sesuai, atau 0 jika tidak ditemukan
+     */
+    private function getJurnalTypeID($jurnalTypeCode)
+    {
+        $sql = "SELECT JurnalTypeID
+                FROM jurnal_type
+                WHERE JurnalTypeCode = ?
+                    AND JurnalTypeIsActive = 'Y'
+                LIMIT 1";
+        $qry = $this->db->query($sql, [$jurnalTypeCode]);
+        if (!$qry || $qry->num_rows() == 0) {
+            return 0;
+        }
+        return (int) $qry->row_array()["JurnalTypeID"];
+    }
+
+    /**
+     * getEligiblePurchaseOrderAssetContracts — Ambil kontrak aset yang layak dibuatkan jurnal.
+     *
+     * Query ini mengambil semua kontrak dari `purchase_order_asset_contract` yang memenuhi
+     * syarat berikut:
+     *   - Kontrak aktif, status "belum lunas", nilai cicilan > 0
+     *   - Cicilan terbayar masih kurang dari total cicilan
+     *   - Tanggal kontrak masuk dalam rentang periode yang diberikan
+     *   - Tanggal jatuh tempo cicilan sudah lewat atau sama dengan hari ini
+     *   - PO sudah Approved dan aktif
+     *   - Barang sudah diterima dan receive order sudah confirmed
+     *
+     * Data yang dikembalikan mencakup:
+     *   - Detail kontrak (ID, nama, tanggal, nilai cicilan)
+     *   - Data PO (nomor PO, supplier, payment term, warehouse)
+     *   - Data cabang (kode cabang, nama, regionalID, branchCompanyID)
+     *   - Data item PO pertama (sebagai referensi baris jurnal)
+     *
+     * @param string $startDate  Awal periode filter (YYYY-MM-DD)
+     * @param string $endDate    Akhir periode filter (YYYY-MM-DD)
+     * @return array             Array of row kontrak yang eligible
+     */
+    private function getEligiblePurchaseOrderAssetContracts($startDate, $endDate)
+    {
+        $sql = "SELECT
+                    c.PurchaseOrderAssetContractID,
+                    si.SupplierInvoiceID,
+                    si.SupplierInvoiceNumber,
+                    c.PurchaseOrderAssetContractPurchaseOrderID,
+                    c.PurchaseOrderAssetContractName,
+                    c.PurchaseOrderAssetContractStartDate,
+                    c.PurchaseOrderAssetContractEndDate,
+                    c.PurchaseOrderAssetContractInstallmentNumber,
+                    c.PurchaseOrderAssetContractInstallmentPaid,
+                    c.PurchaseOrderAssetContractInstallmentDate,
+                    c.PurchaseOrderAssetContractInstallmentPayAmount,
+                    c.PurchaseOrderAssetContractReceiveOrderPoID,
+                    po.PurchaseOrderID,
+                    po.PurchaseOrderNumber,
+                    po.PurchaseOrderSupplierID,
+                    po.PurchaseOrderPaymentTerm,
+                    po.PurchaseOrderWarehouseType,
+                    po.PurchaseOrderWarehouseID,
+                    wh.WarehouseCode,
+                    wh.WarehouseName,
+                    wh.WarehouseType,
+                    wh.WarehouseS_RegionalID AS WarehouseS_RegionalID,
+                    wh.WarehouseM_BranchID AS WarehouseM_BranchID,
+                    ro.ReceiveOrderPoID,
+                    ro.ReceiveOrderPoConfirmed,
+                    mi.M_ItemID AS ItemID,
+                    mi.M_ItemCode AS ItemCode,
+                    mi.M_ItemDesc AS ItemDesc,
+                    mi.M_ItemFa_ClassID,
+                    fc.Fa_ClassID AS ItemFaClassID,
+                    fc.Fa_ClassName AS ItemFaClassName,
+                    coa_bayar.coaID AS ItemCoaID,
+                    coa_bayar.coaAccountNo AS ItemCoaAccountNo,
+                    coa_bayar.coaDescription AS ItemCoaDesc,
+                    coa_hutang.coaID AS HutangCoaID,
+                    coa_hutang.coaAccountNo AS HutangCoaAccountNo,
+                    coa_hutang.coaDescription AS HutangCoaDesc,
+                    m_branch.M_BranchCode,
+                    m_branch.M_BranchName,
+                    m_branch.M_BranchS_RegionalID,
+                    m_branch_companydetail.M_BranchCompanyDetailM_BranchCompanyID AS M_BranchCompanyID,
+                    ps.PurchaseOrderSummaryID,
+                    ps.PurchaseOrderSummaryItemID,
+                    ps.PurchaseOrderSummaryItemUnitID
+                FROM purchase_order_asset_contract c
+
+                -- Pastikan PO sudah Approved dan aktif
+                JOIN purchase_order po
+                    ON po.PurchaseOrderID = c.PurchaseOrderAssetContractPurchaseOrderID
+                    AND po.PurchaseOrderIsActive = 'Y'
+                    AND po.PurchaseOrderStatus = 'Approved'
+
+                -- Data warehouse/gudang tujuan PO
+                LEFT JOIN warehouse wh
+                    ON wh.WarehouseID = po.PurchaseOrderWarehouseID
+
+                -- Ambil 1 item PO pertama sebagai referensi baris jurnal
+                LEFT JOIN (
+                    SELECT ps0.*
+                    FROM purchase_order_summary ps0
+                    JOIN (
+                        SELECT
+                            PurchaseOrderSummaryPurchaseOrderID,
+                            MIN(PurchaseOrderSummaryID) AS PurchaseOrderSummaryID
+                        FROM purchase_order_summary
+                        WHERE PurchaseOrderSummaryIsActive = 'Y'
+                        GROUP BY PurchaseOrderSummaryPurchaseOrderID
+                    ) psx
+                        ON psx.PurchaseOrderSummaryID = ps0.PurchaseOrderSummaryID
+                ) ps
+                    ON ps.PurchaseOrderSummaryPurchaseOrderID = po.PurchaseOrderID
+
+                -- Hubungkan dengan detail supplier_invoice_detail terlebih dahulu
+                JOIN supplier_invoice_detail sid
+                    ON sid.SupplierInvoiceDetailPurchaseOrderID = po.PurchaseOrderID
+                    AND sid.SupplierInvoiceDetailPurchaseOrderSummaryID = ps.PurchaseOrderSummaryID
+                    AND sid.SupplierInvoiceDetailIsActive = 'Y'
+
+                -- Baru hubungkan ke header supplier_invoice untuk mengambil nomor invoice
+                JOIN supplier_invoice si
+                    ON si.SupplierInvoiceID = sid.SupplierInvoiceDetailSupplierInvoiceID
+                    AND si.SupplierInvoiceDate >= DATE(?)
+                    AND si.SupplierInvoiceDate <= DATE(?)
+                    AND si.SupplierInvoiceIsActive = 'Y'
+
+                -- Pastikan barang sudah diterima (receive order confirmed)
+                JOIN (
+                    SELECT
+                        rd.ReceiveOrderPoDetailPurchaseOrderID,
+                        MIN(ro0.ReceiveOrderPoID) AS ReceiveOrderPoID,
+                        MAX(ro0.ReceiveOrderPoConfirmed) AS ReceiveOrderPoConfirmed
+                    FROM receive_order_po ro0
+                    JOIN receive_order_po_detail rd
+                        ON rd.ReceiveOrderPoDetailReceiveOrderPoID = ro0.ReceiveOrderPoID
+                        AND rd.ReceiveOrderPoDetailIsActive = 'Y'
+                    WHERE ro0.ReceiveOrderPoIsActive = 'Y'
+                        AND ro0.ReceiveOrderPoConfirmed = 'Y'
+                    GROUP BY rd.ReceiveOrderPoDetailPurchaseOrderID
+                ) ro
+                    ON ro.ReceiveOrderPoDetailPurchaseOrderID = po.PurchaseOrderID
+
+                -- Data cabang dari warehouse
+                LEFT JOIN m_branch
+                    ON m_branch.M_BranchID = wh.WarehouseM_BranchID
+                    AND m_branch.M_BranchIsActive = 'Y'
+
+                -- Data branch company untuk header jurnal
+                LEFT JOIN m_branch_companydetail
+                    ON m_branch_companydetail.M_BranchCompanyDetailM_BranchCode = m_branch.M_BranchCode
+                    AND m_branch_companydetail.M_BranchCompanyDetailIsActive = 'Y'
+
+                -- COA jurnal diambil dari item -> fa_class
+                LEFT JOIN m_item mi
+                    ON mi.M_ItemID = ps.PurchaseOrderSummaryItemID
+                    AND mi.M_ItemIsActive = 'Y'
+                LEFT JOIN fa_class fc
+                    ON fc.Fa_ClassID = mi.M_ItemFa_ClassID
+                    AND fc.Fa_ClassIsActive = 'Y'
+
+                -- Join langsung ke tabel coa untuk memastikan kevalidan akun dan deskripsi
+                LEFT JOIN coa coa_hutang
+                    ON coa_hutang.coaID = fc.Fa_ClassHutangCoaID
+                    AND coa_hutang.coaIsActive = 'Y'
+                LEFT JOIN coa coa_bayar
+                    ON coa_bayar.coaID = fc.Fa_ClassCoaID
+                    AND coa_bayar.coaIsActive = 'Y'
+
+                WHERE c.PurchaseOrderAssetContractIsActive = 'Y'
+                    AND c.PurchaseOrderAssetContractStatus = 'belum lunas'
+                    -- Hanya kontrak yang ada nilai cicilannya
+                    AND IFNULL(c.PurchaseOrderAssetContractInstallmentPayAmount, 0) > 0
+                    -- Hanya kontrak yang belum selesai seluruh cicilannya
+                    AND IFNULL(c.PurchaseOrderAssetContractInstallmentPaid, 0) < IFNULL(c.PurchaseOrderAssetContractInstallmentNumber, 0)
+                    -- Kontrak sudah mulai sebelum atau pada akhir periode
+                    AND DATE(c.PurchaseOrderAssetContractStartDate) <= DATE(?)
+                    -- Kontrak belum berakhir (atau tidak ada tanggal akhir)
+                    AND (
+                        c.PurchaseOrderAssetContractEndDate IS NULL
+                        OR DATE(c.PurchaseOrderAssetContractEndDate) >= DATE(?)
+                    )
+                    -- Tanggal jatuh tempo cicilan sudah tiba
+                    AND IFNULL(c.PurchaseOrderAssetContractInstallmentDate, 1) <= ?
+                    -- Pastikan belum pernah dibuatkan jurnal untuk invoice cicilan ini
+                    AND NOT EXISTS (
+                        SELECT 1
+                        FROM jurnal_addon ja
+                        JOIN jurnal j ON j.jurnalID = ja.jurnalAddOnJurnalID
+                        WHERE ja.jurnalAddOnCode = 'PIINSTALMENT'
+                            AND ja.jurnalAddOnValue = si.SupplierInvoiceNumber
+                    )
+                ORDER BY c.PurchaseOrderAssetContractID ASC";
+
+        $qry = $this->db->query($sql, [
+            $startDate,
+            $endDate,
+            $endDate,
+            $startDate,
+            (int) date("d", strtotime($endDate))
+        ]);
+        if (!$qry) {
+            $this->sys_error_db("Gagal mengambil data purchase_order_asset_contract.");
+            exit;
+        }
+
+        return $qry->result_array();
+    }
+
+    /**
+     * isValidDate — Validasi format tanggal YYYY-MM-DD.
+     *
+     * @param string $tanggal  String tanggal yang akan divalidasi
+     * @return bool            true jika format valid, false jika tidak
+     */
+    private function isValidDate($tanggal)
+    {
+        $d = DateTime::createFromFormat("Y-m-d", $tanggal);
+        return $d && $d->format("Y-m-d") === $tanggal;
+    }
+}

application/controllers/mockup/scheduler/PurchaseInvoiceInstallment.http

@@ -0,0 +1,65 @@
+
+# ============================================================
+# FILE   : PurchaseInvoiceInstallment.http
+# MODUL  : Scheduler — Generate Supplier Installment Cicilan Aset
+# ============================================================
+#
+# DESKRIPSI
+# ---------
+# File ini berisi HTTP request untuk menguji endpoint scheduler
+# yang secara otomatis membuat baris cicilan ke tabel
+# supplier_installment setiap bulan.
+#
+# CARA PAKAI
+# ----------
+# 1. Gunakan ekstensi "REST Client" di VS Code (humao.rest-client)
+#    atau tool sejenis (IntelliJ HTTP Client, Postman, dll).
+# 2. Ganti nilai @host sesuai environment.
+# 3. Klik "Send Request" di atas tiap blok ###
+#
+# ALUR
+# ----
+#  [1] GenerateMonthlyInvoices → Jalankan proses generate installment
+#
+# CATATAN
+# -------
+# - Default periode: bulan berjalan (tanggal 1 s/d akhir bulan)
+# - Aman dijalankan ulang — dedup berdasarkan PO + bulan mencegah
+#   duplikasi
+# - Tidak membuat jurnal — jurnal dibuat saat pembayaran cashier
+# - Tidak update kontrak — InstallmentPaid diupdate saat pembayaran
+# ============================================================
+
+@host = https://accone.aplikasi.web.id/one-api
+
+# ============================================================
+# [1] GENERATE INSTALLMENT BULANAN (MAIN ENDPOINT)
+# ============================================================
+# Satu request ini akan:
+#   Step 1: Validasi kontrak (aktif, belum lunas, dalam rentang)
+#   Step 2: Validasi PO (Approved, Active)
+#   Step 3: Validasi RO (Confirmed, Active)
+#   Step 4: Validasi parent invoice (IsInstallment = 'Y')
+#   Step 5: Cek dedup (belum ada installment bulan ini)
+#   Step 6: INSERT ke supplier_installment (Status = 'Pending')
+#
+# Response:
+#   - totalEligible : jumlah kontrak lolos validasi
+#   - totalInserted : jumlah baris berhasil diinsert
+#   - created[]     : detail tiap installment yang dibuat
+# ============================================================
+
+### [1] Generate installment untuk periode tertentu
+POST {{host}}/mockup/scheduler/PurchaseInvoiceInstallment/GenerateMonthlyInvoices
+Content-Type: application/json
+
+{
+    "startDate": "2026-07-01",
+    "endDate": "2026-07-31"
+}
+
+### [1a] Generate installment bulan berjalan (tanpa parameter)
+POST {{host}}/mockup/scheduler/PurchaseInvoiceInstallment/GenerateMonthlyInvoices
+Content-Type: application/json
+
+{}

application/controllers/mockup/scheduler/PurchaseInvoiceInstallment.php

@@ -0,0 +1,302 @@
+<?php
+
+/**
+ * PurchaseInvoiceInstallment
+ *
+ * CRON scheduler untuk membuat baris cicilan otomatis ke tabel supplier_installment
+ * setiap bulan berdasarkan kontrak aset yang aktif dan belum lunas.
+ *
+ * Endpoint:
+ *   POST /scheduler/PurchaseInvoiceInstallment/GenerateMonthlyInvoices
+ */
+class PurchaseInvoiceInstallment extends MY_Controller
+{
+    var $db;
+
+    public function index()
+    {
+        echo "Purchase Invoice Installment — Auto Generate Installment Cicilan Aset";
+    }
+
+    public function __construct()
+    {
+        parent::__construct();
+    }
+
+    /**
+     * GenerateMonthlyInvoices — Step 6
+     *
+     * Creates supplier_installment rows for all eligible contracts this month.
+     * No journal is created here — that happens at cashier payment time.
+     * Contract InstallmentPaid is NOT updated here — that happens at payment time.
+     *
+     * Called by CRON daily at 1:00 AM.
+     * Defaults to current month if no startDate/endDate provided.
+     */
+    public function GenerateMonthlyInvoices()
+    {
+        try {
+            $para = $this->sys_input;
+
+            $userID = !empty($this->sys_user["M_UserID"])
+                ? (int) $this->sys_user["M_UserID"]
+                : 0;
+
+            $startDate = !empty($para["startDate"])
+                ? $para["startDate"]
+                : date("Y-m-01");
+
+            $endDate = !empty($para["endDate"])
+                ? $para["endDate"]
+                : date("Y-m-t");
+
+            if (!$this->isValidDate($startDate) || !$this->isValidDate($endDate)) {
+                throw new Exception("Format tanggal tidak valid.");
+            }
+
+            if (strtotime($startDate) > strtotime($endDate)) {
+                throw new Exception("startDate > endDate.");
+            }
+
+            // ── Steps 1–5: Get eligible contracts ────────────────────
+            $eligible = $this->getEligibleInstallments($startDate, $endDate);
+
+            $created  = [];
+            $inserted = 0;
+
+            $this->db->trans_begin();
+
+            foreach ($eligible as $row) {
+
+                $amount      = (float) $row["PurchaseOrderAssetContractInstallmentPayAmount"];
+                $dayOfMonth  = (int) ($row["PurchaseOrderAssetContractInstallmentDate"] ?? 1);
+                $lastDay     = (int) date("t", strtotime($endDate));
+                $dayOfMonth  = min($dayOfMonth, $lastDay);
+                $installDate = date("Y-m", strtotime($endDate)) . "-" . str_pad($dayOfMonth, 2, "0", STR_PAD_LEFT);
+                $dueDate     = date("Y-m-d", strtotime($installDate . " +7 days"));
+                $createdBy   = (int) ($row["PurchaseOrderAssetContractCreatedUserID"] ?? $userID);
+
+                // ── Step 6: INSERT supplier_installment ──────────────
+                $sql = "INSERT INTO supplier_installment (
+                        SupplierInstallmentPurchaseOrderID,
+                        SupplierInstallmentSupplierID,
+                        SupplierInstallmentSupplierInvoiceID,
+                        SupplierInstallmentAmount,
+                        SupplierInstallmentDate,
+                        SupplierInstallmentDueDate,
+                        SupplierInstallmentPaymentID,
+                        SupplierInstallmentStatus,
+                        SupplierInstallmentIsLunas,
+                        SupplierInstallmentIsActive,
+                        SupplierInstallmentCreated,
+                        SupplierInstallmentCreatedUserID
+                    ) VALUES (?, ?, ?, ?, ?, ?, 0, 'Pending', 'N', 'Y', NOW(), ?)";
+
+                $que = $this->db->query($sql, [
+                    $row["PurchaseOrderID"],
+                    $row["PurchaseOrderSupplierID"],
+                    $row["SupplierInvoiceID"],
+                    $amount,
+                    $installDate,
+                    $dueDate,
+                    $createdBy
+                ]);
+
+                if (!$que) {
+                    $this->db->trans_rollback();
+                    $this->sys_error_db("Gagal insert supplier_installment.");
+                    exit;
+                }
+
+                $installmentID = $this->db->insert_id();
+                $inserted++;
+
+                $created[] = [
+                    "installmentID"       => $installmentID,
+                    "contractID"          => $row["PurchaseOrderAssetContractID"],
+                    "purchaseOrderID"     => $row["PurchaseOrderID"],
+                    "parentInvoiceID"     => $row["SupplierInvoiceID"],
+                    "parentInvoiceNumber" => $row["SupplierInvoiceNumber"],
+                    "amount"              => $amount,
+                    "installDate"         => $installDate,
+                    "dueDate"             => $dueDate
+                ];
+            }
+
+            if ($this->db->trans_status() === false) {
+                $this->db->trans_rollback();
+                $this->sys_error_db("Transaksi gagal.");
+                exit;
+            }
+
+            $this->db->trans_commit();
+
+            $this->sys_ok([
+                "startDate"      => $startDate,
+                "endDate"        => $endDate,
+                "totalEligible"  => count($eligible),
+                "totalInserted"  => $inserted,
+                "created"        => $created
+            ]);
+
+        } catch (Exception $exc) {
+            if ($this->db->trans_status() === false) {
+                $this->db->trans_rollback();
+            }
+            $this->sys_error($exc->getMessage());
+        }
+    }
+
+    /**
+     * Ambil data user berdasarkan userID untuk keperluan CRON.
+     * Jika user tidak ditemukan, kembalikan data default (tanpa branch/regional).
+     *
+     * @param int $userID
+     * @return array
+     */
+    private function getCronUser($userID)
+    {
+        $sql = "SELECT
+                M_UserID,
+                M_UserM_BranchID   AS M_BranchID,
+                M_UserS_RegionalID AS S_RegionalID
+            FROM m_user
+            WHERE M_UserID = ?
+            LIMIT 1";
+
+        $qry = $this->db->query($sql, [$userID]);
+        if ($qry && $qry->num_rows() > 0) {
+            $user = $qry->row_array();
+            $user["loginLevel"] = "branch";
+            return $user;
+        }
+
+        // User tidak ditemukan, gunakan data kosong agar proses tetap berjalan
+        return [
+            "M_UserID"     => $userID,
+            "M_BranchID"   => 0,
+            "S_RegionalID" => 0,
+            "loginLevel"   => "branch"
+        ];
+    }
+
+    /**
+     * Hitung tanggal jatuh tempo berdasarkan tanggal acuan dan payment term (dalam hari).
+     *
+     * @param string $tanggal  Format YYYY-MM-DD
+     * @param int    $term     Jumlah hari payment term
+     * @return string          Tanggal jatuh tempo (YYYY-MM-DD)
+     */
+    private function hitungJatuhTempo($tanggal, $term)
+    {
+        $hari = is_numeric($term) ? (int) $term : 0;
+        if ($hari < 0) {
+            $hari = 0;
+        }
+
+        return date("Y-m-d", strtotime($tanggal . " +" . $hari . " days"));
+    }
+
+    /**
+     * Validasi apakah string adalah tanggal yang valid dengan format YYYY-MM-DD.
+     *
+     * @param string $tanggal
+     * @return bool
+     */
+    private function isValidDate($tanggal)
+    {
+        $d = DateTime::createFromFormat("Y-m-d", $tanggal);
+        return $d && $d->format("Y-m-d") === $tanggal;
+    }
+
+    /**
+     * getEligibleInstallments — Steps 1–5 combined
+     *
+     * Returns contracts that:
+     *   Step 1: Are active, not paid off, within date range, due date reached
+     *   Step 2: Have approved & active PO
+     *   Step 3: Have confirmed & active RO
+     *   Step 4: Have a parent supplier_invoice with IsInstallment = 'Y'
+     *   Step 5: Do NOT already have a supplier_installment for this month
+     *
+     * @param string $startDate  YYYY-MM-DD
+     * @param string $endDate    YYYY-MM-DD
+     * @return array
+     */
+    private function getEligibleInstallments($startDate, $endDate)
+    {
+        $dayOfMonth = (int) date("d", strtotime($endDate));
+
+        $sql = "SELECT
+                c.PurchaseOrderAssetContractID,
+                c.PurchaseOrderAssetContractInstallmentPayAmount,
+                c.PurchaseOrderAssetContractInstallmentDate,
+                c.PurchaseOrderAssetContractCreatedUserID,
+                po.PurchaseOrderID,
+                po.PurchaseOrderSupplierID,
+                si.SupplierInvoiceID,
+                si.SupplierInvoiceNumber
+            FROM purchase_order_asset_contract c
+
+            -- Step 2: PO must be Approved and Active
+            JOIN purchase_order po
+                ON po.PurchaseOrderID = c.PurchaseOrderAssetContractPurchaseOrderID
+                AND po.PurchaseOrderIsActive = 'Y'
+                AND po.PurchaseOrderStatus = 'Approved'
+
+            -- Step 3: RO must be Confirmed and Active
+            JOIN (
+                SELECT
+                    rd.ReceiveOrderPoDetailPurchaseOrderID,
+                    MIN(ro0.ReceiveOrderPoID)        AS ReceiveOrderPoID,
+                    MAX(ro0.ReceiveOrderPoConfirmed)  AS ReceiveOrderPoConfirmed
+                FROM receive_order_po ro0
+                JOIN receive_order_po_detail rd
+                    ON rd.ReceiveOrderPoDetailReceiveOrderPoID = ro0.ReceiveOrderPoID
+                    AND rd.ReceiveOrderPoDetailIsActive = 'Y'
+                WHERE ro0.ReceiveOrderPoIsActive = 'Y'
+                    AND ro0.ReceiveOrderPoConfirmed = 'Y'
+                GROUP BY rd.ReceiveOrderPoDetailPurchaseOrderID
+            ) ro ON ro.ReceiveOrderPoDetailPurchaseOrderID = po.PurchaseOrderID
+
+            -- Step 4: Parent invoice must exist with IsInstallment = 'Y'
+            JOIN supplier_invoice si
+                ON si.SupplierInvoiceReceiveOrderPoID = ro.ReceiveOrderPoID
+                AND si.SupplierInvoiceIsActive = 'Y'
+                AND si.SupplierInvoiceIsInstallment = 'Y'
+
+            -- Step 1: Contract eligibility
+            WHERE c.PurchaseOrderAssetContractIsActive = 'Y'
+                AND c.PurchaseOrderAssetContractStatus = 'belum lunas'
+                AND IFNULL(c.PurchaseOrderAssetContractInstallmentPayAmount, 0) > 0
+                AND IFNULL(c.PurchaseOrderAssetContractInstallmentPaid, 0)
+                    < IFNULL(c.PurchaseOrderAssetContractInstallmentNumber, 0)
+                AND DATE(c.PurchaseOrderAssetContractStartDate) <= DATE(?)
+                AND (
+                    c.PurchaseOrderAssetContractEndDate IS NULL
+                    OR DATE(c.PurchaseOrderAssetContractEndDate) >= DATE(?)
+                )
+                AND IFNULL(c.PurchaseOrderAssetContractInstallmentDate, 1) <= ?
+
+            -- Step 5: Dedup — no existing supplier_installment this month (any status)
+            AND NOT EXISTS (
+                SELECT 1
+                FROM supplier_installment inst
+                WHERE inst.SupplierInstallmentIsActive = 'Y'
+                    AND inst.SupplierInstallmentPurchaseOrderID = po.PurchaseOrderID
+                    AND DATE_FORMAT(inst.SupplierInstallmentDate, '%Y-%m') = DATE_FORMAT(?, '%Y-%m')
+            )
+
+            ORDER BY c.PurchaseOrderAssetContractID ASC";
+
+        $params = [$endDate, $startDate, $dayOfMonth, $endDate];
+
+        $qry = $this->db->query($sql, $params);
+        if (!$qry) {
+            $this->sys_error_db("Gagal mengambil daftar installment eligible.");
+            exit;
+        }
+
+        return $qry->result_array();
+    }
+}

application/controllers/mockup/scheduler/PurchaseInvoiceInstallmentInsert.php

@@ -0,0 +1,220 @@
+<?php
+
+/**
+ * PurchaseInvoiceInstallmentInsert
+ *
+ * Endpoint khusus untuk menyimpan supplier_invoice dan supplier_invoice_detail
+ * dari payload yang dikirim controller generator via cURL.
+ */
+class PurchaseInvoiceInstallmentInsert extends MY_Controller
+{
+    var $db;
+
+    public function index()
+    {
+        echo "Purchase Invoice Installment Insert";
+    }
+
+    public function __construct()
+    {
+        parent::__construct();
+    }
+
+    public function InsertSupplierInvoice()
+    {
+        try {
+            $para = $this->sys_input;
+
+            $required = [
+                "nomorPI",
+                "tanggalPI",
+                "tanggalJatuhTempo",
+                "jumlahCicilan",
+                "catatan",
+                "deskripsi",
+                "userID",
+                "purchaseOrderAssetContractID",
+                "purchaseOrderID",
+                "receiveOrderPoID",
+                "supplierID",
+                "purchaseOrderSummaryID",
+                "purchaseOrderSummaryItemID",
+                "purchaseOrderSummaryItemUnitID"
+            ];
+
+            foreach ($required as $field) {
+                if (!isset($para[$field]) || $para[$field] === "" || $para[$field] === null) {
+                    throw new Exception("Field wajib belum lengkap: " . $field);
+                }
+            }
+
+            $tanggalPI = $para["tanggalPI"];
+            $tanggalJatuhTempo = $para["tanggalJatuhTempo"];
+            if (!$this->isValidDate($tanggalPI) || !$this->isValidDate($tanggalJatuhTempo)) {
+                throw new Exception("Format tanggal tidak valid. Gunakan YYYY-MM-DD.");
+            }
+
+            $jumlahCicilan = (float) $para["jumlahCicilan"];
+            if ($jumlahCicilan <= 0) {
+                throw new Exception("jumlahCicilan harus lebih besar dari 0.");
+            }
+
+            $receiveOrderPoID = (int) $para["receiveOrderPoID"];
+            $monthKey = date("Y-m", strtotime($tanggalPI));
+
+            $sqlDuplikat = "SELECT SupplierInvoiceID, SupplierInvoiceNumber
+                FROM supplier_invoice
+                WHERE SupplierInvoiceIsActive = 'Y'
+                    AND SupplierInvoiceStatus = 'Draft'
+                    AND SupplierInvoiceReceiveOrderPoID = ?
+                    AND DATE_FORMAT(SupplierInvoiceDate, '%Y-%m') = ?
+                LIMIT 1";
+
+            $qryDuplikat = $this->db->query($sqlDuplikat, [$receiveOrderPoID, $monthKey]);
+            if (!$qryDuplikat) {
+                $this->sys_error_db("Gagal mengecek duplikasi supplier_invoice.");
+                exit;
+            }
+
+            if ($qryDuplikat->num_rows() > 0) {
+                $existing = $qryDuplikat->row_array();
+                $this->sys_ok([
+                    "duplicate" => true,
+                    "supplierInvoiceID" => $existing["SupplierInvoiceID"],
+                    "supplierInvoiceNumber" => $existing["SupplierInvoiceNumber"]
+                ]);
+                return;
+            }
+
+            $this->db->trans_begin();
+
+            $sqlHeader = "INSERT INTO supplier_invoice (
+                    SupplierInvoiceNumber,
+                    SupplierInvoiceReceiveOrderPoID,
+                    SupplierInvoiceDate,
+                    SupplierInvoiceDueDate,
+                    SupplierInvoiceDraftPaymentDate,
+                    SupplierInvoiceSupplierID,
+                    SupplierInvoiceSupplierInvoiceNumber,
+                    SupplierInvoiceSupplierInvoiceDate,
+                    SupplierInvoiceSubTotal,
+                    SupplierInvoiceDiscountPercent,
+                    SupplierInvoiceDiscountAmount,
+                    SupplierInvoiceTaxPercentPph,
+                    SupplierInvoiceTaxAmountPph,
+                    SupplierInvoiceTaxPercentPpn,
+                    SupplierInvoiceTaxAmountPpn,
+                    SupplierInvoiceShippingCost,
+                    SupplierInvoiceAdjustmentAmount,
+                    SupplierInvoiceAdjustmentNote,
+                    SupplierInvoiceGrandTotal,
+                    SupplierInvoiceUnpaid,
+                    SupplierInvoiceNote,
+                    SupplierInvoiceStatus,
+                    SupplierInvoiceCreatedUserID
+                ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
+
+            $qryHeader = $this->db->query($sqlHeader, [
+                $para["nomorPI"],
+                $receiveOrderPoID,
+                $tanggalPI,
+                $tanggalJatuhTempo,
+                $tanggalPI,
+                (int) $para["supplierID"],
+                null,
+                null,
+                $jumlahCicilan,
+                0,
+                0,
+                0,
+                0,
+                0,
+                0,
+                0,
+                0,
+                null,
+                $jumlahCicilan,
+                $jumlahCicilan,
+                $para["catatan"],
+                "Draft",
+                (int) $para["userID"]
+            ]);
+
+            if (!$qryHeader) {
+                $this->db->trans_rollback();
+                $this->sys_error_db("Gagal insert header supplier_invoice.");
+                exit;
+            }
+
+            $supplierInvoiceID = $this->db->insert_id();
+
+            $sqlDetail = "INSERT INTO supplier_invoice_detail (
+                    SupplierInvoiceDetailSupplierInvoiceID,
+                    SupplierInvoiceDetailPurchaseOrderID,
+                    SupplierInvoiceDetailReceiveOrderPoID,
+                    SupplierInvoiceDetailPurchaseOrderSummaryID,
+                    SupplierInvoiceDetailItemID,
+                    SupplierInvoiceDetailItemUnitID,
+                    SupplierInvoiceDetailDescription,
+                    SupplierInvoiceDetailQty,
+                    SupplierInvoiceDetailPrice,
+                    SupplierInvoiceDetailDiscountPercent,
+                    SupplierInvoiceDetailDiscountDiscountRupiah,
+                    SupplierInvoiceDetailDiscountDiscountType,
+                    SupplierInvoiceDetailDiscountPoProrata,
+                    SupplierInvoiceDetailDiscountAmount,
+                    SupplierInvoiceDetailTotal,
+                    SupplierInvoiceDetailUnpaid,
+                    SupplierInvoiceDetailCreatedUserID
+                ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
+
+            $qryDetail = $this->db->query($sqlDetail, [
+                $supplierInvoiceID,
+                (int) $para["purchaseOrderID"],
+                $receiveOrderPoID,
+                (int) $para["purchaseOrderSummaryID"],
+                (int) $para["purchaseOrderSummaryItemID"],
+                (int) $para["purchaseOrderSummaryItemUnitID"],
+                $para["deskripsi"],
+                1,
+                $jumlahCicilan,
+                0,
+                0,
+                "R",
+                0,
+                0,
+                $jumlahCicilan,
+                $jumlahCicilan,
+                (int) $para["userID"]
+            ]);
+
+            if (!$qryDetail) {
+                $this->db->trans_rollback();
+                $this->sys_error_db("Gagal insert detail supplier_invoice_detail.");
+                exit;
+            }
+
+            if ($this->db->trans_status() === false) {
+                $this->db->trans_rollback();
+                $this->sys_error_db("Transaksi insert supplier_invoice gagal.");
+                exit;
+            }
+
+            $this->db->trans_commit();
+
+            $this->sys_ok([
+                "duplicate" => false,
+                "supplierInvoiceID" => $supplierInvoiceID,
+                "supplierInvoiceNumber" => $para["nomorPI"]
+            ]);
+        } catch (Exception $exc) {
+            $this->sys_error($exc->getMessage());
+        }
+    }
+
+    private function isValidDate($tanggal)
+    {
+        $d = DateTime::createFromFormat("Y-m-d", $tanggal);
+        return $d && $d->format("Y-m-d") === $tanggal;
+    }
+}

application/controllers/mockup/supplierpaymentapprovedv3/Billv2.php

@@ -28,29 +28,92 @@ class Billv2 extends MY_Controller {
                 $offset = ($params['currentpage'] - 1) * $limit;
             }
 
-            $sql_base = "SELECT 
-                SupplierPaymentID,
-                SupplierPaymentDate,
-                SupplierPaymentNumber,
-                SupplierPaymentAmount,
-                SupplierPaymentStatus,
-                SupplierPaymentIsVerif,
-                SupplierPaymentIsApproved,
-                SupplierInvoiceID,
-                SupplierInvoiceNumber,
-                SupplierInvoiceDraftPaymentDate,
-                SupplierCode,
-                SupplierName
-            FROM supplier_payment 
-            JOIN supplier_invoice ON SupplierInvoiceID = SupplierPaymentSupplierInvoiceID 
-                AND SupplierPaymentNumber LIKE ?
-                AND (SupplierPaymentDate BETWEEN DATE(?) AND DATE(?))
-                AND (SupplierPaymentStatus = ? OR ? = 'All')
-            JOIN supplier ON SupplierID = SupplierInvoiceSupplierID
-            WHERE SupplierPaymentIsActive = 'Y' 
-            ORDER BY SupplierPaymentID DESC";
+            // ── UNION base — invoice + downpayment + installment ───
+            $sql_base = "
+                SELECT
+                    sp.SupplierPaymentID,
+                    sp.SupplierPaymentDate,
+                    sp.SupplierPaymentNumber,
+                    sp.SupplierPaymentAmount,
+                    sp.SupplierPaymentStatus,
+                    sp.SupplierPaymentIsVerif,
+                    sp.SupplierPaymentIsApproved,
+                    sp.SupplierPaymentIsActive,
+                    si.SupplierInvoiceID,
+                    si.SupplierInvoiceNumber,
+                    si.SupplierInvoiceDraftPaymentDate,
+                    sup.SupplierCode,
+                    sup.SupplierName,
+                    'INVOICE' AS type
+                FROM supplier_payment sp
+                JOIN supplier_invoice si
+                    ON si.SupplierInvoiceID = sp.SupplierPaymentSupplierInvoiceID
+                JOIN supplier sup
+                    ON sup.SupplierID = si.SupplierInvoiceSupplierID
+                WHERE sp.SupplierPaymentSupplierInvoiceID > 0
+
+                UNION ALL
+
+                SELECT
+                    sp.SupplierPaymentID,
+                    sp.SupplierPaymentDate,
+                    sp.SupplierPaymentNumber,
+                    sp.SupplierPaymentAmount,
+                    sp.SupplierPaymentStatus,
+                    sp.SupplierPaymentIsVerif,
+                    sp.SupplierPaymentIsApproved,
+                    sp.SupplierPaymentIsActive,
+                    dp.SupplierDownpaymentID * -1               AS SupplierInvoiceID,
+                    CONCAT('DP-', po.PurchaseOrderNumber)       AS SupplierInvoiceNumber,
+                    dp.SupplierDownpaymentDueDate               AS SupplierInvoiceDraftPaymentDate,
+                    dp_sup.SupplierCode                         AS SupplierCode,
+                    dp_sup.SupplierName                         AS SupplierName,
+                    'DP'                                        AS type
+                FROM supplier_payment sp
+                JOIN supplier_downpayment dp
+                    ON dp.SupplierDownpaymentID = sp.SupplierPaymentSupplierDownpaymentID
+                JOIN supplier dp_sup
+                    ON dp_sup.SupplierID = dp.SupplierDownpaymentSupplierID
+                JOIN purchase_order po
+                    ON po.PurchaseOrderID = dp.SupplierDownpaymentPurchasOrderID
+                WHERE sp.SupplierPaymentSupplierDownpaymentID IS NOT NULL
+
+                UNION ALL
+
+                SELECT
+                    sp.SupplierPaymentID,
+                    sp.SupplierPaymentDate,
+                    sp.SupplierPaymentNumber,
+                    sp.SupplierPaymentAmount,
+                    sp.SupplierPaymentStatus,
+                    sp.SupplierPaymentIsVerif,
+                    sp.SupplierPaymentIsApproved,
+                    sp.SupplierPaymentIsActive,
+                    0                                                AS SupplierInvoiceID,
+                    CONCAT('INST-', si_inst.SupplierInvoiceNumber)   AS SupplierInvoiceNumber,
+                    inst.SupplierInstallmentDueDate                  AS SupplierInvoiceDraftPaymentDate,
+                    inst_sup.SupplierCode                            AS SupplierCode,
+                    inst_sup.SupplierName                            AS SupplierName,
+                    'INSTALLMENT'                                    AS type
+                FROM supplier_payment sp
+                JOIN supplier_installment inst
+                    ON inst.SupplierInstallmentID = sp.SupplierPaymentSupplierInstallmentID
+                JOIN supplier_invoice si_inst
+                    ON si_inst.SupplierInvoiceID = inst.SupplierInstallmentSupplierInvoiceID
+                JOIN supplier inst_sup
+                    ON inst_sup.SupplierID = inst.SupplierInstallmentSupplierID
+                WHERE sp.SupplierPaymentSupplierInstallmentID IS NOT NULL";
+
+            // ── Outer: common filters + ordering + pagination ───────
+            $sql_data = "
+                SELECT * FROM ($sql_base) AS combined
+                WHERE SupplierPaymentIsActive = 'Y'
+                    AND SupplierPaymentNumber LIKE ?
+                    AND (SupplierPaymentDate BETWEEN DATE(?) AND DATE(?))
+                    AND (SupplierPaymentStatus = ? OR ? = 'All')
+                ORDER BY SupplierPaymentID DESC
+                LIMIT ? OFFSET ?";
 
-            $sql_data = $sql_base . " LIMIT ? OFFSET ? ";
             $que_data = $this->db->query($sql_data, [
                 $keyword, $params['startdate'], $params['enddate'],
                 $params['status'], $params['status'], $limit, $offset
@@ -59,7 +122,13 @@ class Billv2 extends MY_Controller {
                 throw new Exception("[Error] failed get data supplier payment", 2);
             }
 
-            $sql_total = "SELECT COUNT(*) AS total FROM ($sql_base) AS x";
+            // ── COUNT — wrap UNION in outer filter ──────────────────
+            $sql_total = "SELECT COUNT(*) AS total FROM ($sql_base) AS combined
+                WHERE SupplierPaymentIsActive = 'Y'
+                    AND SupplierPaymentNumber LIKE ?
+                    AND (SupplierPaymentDate BETWEEN DATE(?) AND DATE(?))
+                    AND (SupplierPaymentStatus = ? OR ? = 'All')";
+
             $que_total = $this->db->query($sql_total, [
                 $keyword, $params['startdate'], $params['enddate'],
                 $params['status'], $params['status']
@@ -70,11 +139,10 @@ class Billv2 extends MY_Controller {
 
             $output = [
                 "records" => $que_data->result_array(),
-                "total" =>$que_total->row_array()['total']
+                "total"   => $que_total->row_array()['total']
             ];
-            
+
             $this->sys_ok($output);
-            exit;
         } catch (Exception $exc) {
             $message = $exc->getMessage();
             $code = $exc->getCode();
@@ -96,70 +164,169 @@ class Billv2 extends MY_Controller {
 
             $para = $this->sys_input;
 
-            $sql = "SELECT 
-                SupplierInvoiceID, 
-                SupplierInvoiceRefNumber,
-                SupplierInvoiceDeliveryOrderNumber,
-                SupplierInvoiceSupplierInvoiceNumber,
-                SupplierInvoiceSupplierInvoiceDate,
-                SupplierInvoiceSubTotal,
-                SupplierInvoiceTaxPercentPph,
-                SupplierInvoiceTaxPercentPpn,
-                SupplierInvoiceTaxAmountPpn,
-                SupplierInvoiceDiscountAmount,
-                SupplierInvoiceDiscountPercent,
-                SupplierInvoiceShippingCost,
-                SupplierInvoiceGrandTotal,
-                SupplierInvoiceAdjustmentAmount,
-                SupplierInvoiceAdjustmentNote,
-                SupplierInvoiceNote,
-                IF (SupplierInvoiceDiscountAmount > 0, 'R', 'P') AS DiscountType
-            FROM supplier_payment 
-            JOIN supplier_invoice ON SupplierPaymentSupplierInvoiceID = SupplierInvoiceID 
-                AND SupplierPaymentID = ?
-                AND SupplierPaymentIsActive = 'Y'";
-            $que = $this->db->query($sql, [$para['paymentID']]);
-            if (!$que) {
-                throw new Exception("[Error] failed get row data", 2);
-            }
-            $data = $que->row_array();
+            // ── Detect payment type ──────────────────────────────────
+            $sql_type = "SELECT
+                    SupplierPaymentSupplierInvoiceID,
+                    SupplierPaymentSupplierDownpaymentID,
+                    SupplierPaymentSupplierInstallmentID
+                FROM supplier_payment
+                WHERE SupplierPaymentID = ? AND SupplierPaymentIsActive = 'Y'";
 
-            $sql_detail = "SELECT 
-                SupplierInvoiceDetailID,
-                SupplierInvoiceDetailSupplierInvoiceID,
-                SupplierInvoiceDetailPurchaseOrderID,
-                SupplierInvoiceDetailPurchaseOrderSummaryID,
-                SupplierInvoiceDetailReceiveOrderPoID,
-                SupplierInvoiceDetailReceiveOrderPoDetailID,
-                SupplierInvoiceDetailItemID,
-                SupplierInvoiceDetailItemUnitID,
-                SupplierInvoiceDetailDescription,
-                SupplierInvoiceDetailQty,
-                SupplierInvoiceDetailPrice,
-                SupplierInvoiceDetailDiscountPercent,
-                SupplierInvoiceDetailDiscountDiscountRupiah,
-                SupplierInvoiceDetailDiscountDiscountType,
-                SupplierInvoiceDetailDiscountAmount,
-                (SupplierInvoiceDetailPrice - SupplierInvoiceDetailDiscountAmount) AS DiscountedPrice,
-                SupplierInvoiceDetailDiscountPoProrata,
-                SupplierInvoiceDetailTotal,
-                M_ItemCode,
-                M_ItemDesc
-            FROM supplier_payment_detail 
-            JOIN supplier_invoice_detail ON SupplierInvoiceDetailIsActive = 'Y'
-                AND SupplierPaymentDetailSupplierPaymentID = ?
-                AND SupplierInvoiceDetailSupplierInvoiceID = ?
-            JOIN m_item ON M_ItemID = SupplierInvoiceDetailItemID 
-                AND M_ItemIsActive = 'Y'
-            GROUP BY SupplierInvoiceDetailID";
-            $que_detail = $this->db->query($sql_detail, [
-                $para['paymentID'], $data['SupplierInvoiceID']
-            ]);
-            if (!$que_detail) {
-                throw new Exception("[Error] failed to get item payments", 2);
+            $que_type = $this->db->query($sql_type, [$para['paymentID']]);
+            if (!$que_type) {
+                throw new Exception("[Error] failed get payment header", 2);
+            }
+            $payment = $que_type->row_array();
+            if (!$payment) {
+                throw new Exception("[Error] payment not found", 2);
             }
 
-            $data['detail'] = $que_detail->result_array();
+            // ── INVOICE branch ──────────────────────────────────────
+            if ($payment['SupplierPaymentSupplierInvoiceID'] > 0) {
+                $sql = "SELECT
+                        si.SupplierInvoiceSubTotal,
+                        si.SupplierInvoiceShippingCost,
+                        si.SupplierInvoiceDiscountPercent,
+                        si.SupplierInvoiceDiscountAmount,
+                        si.SupplierInvoiceTaxPercentPpn,
+                        si.SupplierInvoiceTaxAmountPpn,
+                        si.SupplierInvoiceGrandTotal,
+                        si.SupplierInvoiceID,
+                        'INVOICE' AS type
+                    FROM supplier_payment sp
+                    JOIN supplier_invoice si
+                        ON si.SupplierInvoiceID = sp.SupplierPaymentSupplierInvoiceID
+                    WHERE sp.SupplierPaymentID = ? AND sp.SupplierPaymentIsActive = 'Y'";
+
+                $que = $this->db->query($sql, [$para['paymentID']]);
+                if (!$que) {
+                    throw new Exception("[Error] failed get row data", 2);
+                }
+                $data = $que->row_array();
+
+                $sql_detail = "SELECT
+                        M_ItemDesc,
+                        SupplierInvoiceDetailQty,
+                        SupplierInvoiceDetailPrice,
+                        SupplierInvoiceDetailDiscountAmount,
+                        (SupplierInvoiceDetailPrice - SupplierInvoiceDetailDiscountAmount) AS DiscountedPrice,
+                        SupplierInvoiceDetailTotal
+                    FROM supplier_payment_detail
+                    JOIN supplier_invoice_detail
+                        ON SupplierInvoiceDetailIsActive = 'Y'
+                        AND SupplierPaymentDetailSupplierPaymentID = ?
+                        AND SupplierInvoiceDetailSupplierInvoiceID = ?
+                    JOIN m_item
+                        ON M_ItemID = SupplierInvoiceDetailItemID AND M_ItemIsActive = 'Y'
+                    GROUP BY SupplierInvoiceDetailID";
+
+                $que_detail = $this->db->query($sql_detail, [
+                    $para['paymentID'], $data['SupplierInvoiceID']
+                ]);
+                if (!$que_detail) {
+                    throw new Exception("[Error] failed to get item payments", 2);
+                }
+
+                unset($data['SupplierInvoiceID']);
+                $data['detail'] = $que_detail->result_array();
+
+            // ── DOWNPAYMENT branch ──────────────────────────────────
+            } elseif ($payment['SupplierPaymentSupplierDownpaymentID'] > 0) {
+                $sql = "SELECT
+                        dp.SupplierDownpaymentPurchasOrderID,
+                        dp.SupplierDownpaymentAmount   AS SupplierInvoiceSubTotal,
+                        0                              AS SupplierInvoiceShippingCost,
+                        0                              AS SupplierInvoiceDiscountPercent,
+                        0                              AS SupplierInvoiceDiscountAmount,
+                        0                              AS SupplierInvoiceTaxPercentPpn,
+                        0                              AS SupplierInvoiceTaxAmountPpn,
+                        dp.SupplierDownpaymentAmount   AS SupplierInvoiceGrandTotal,
+                        'DP'                           AS type
+                    FROM supplier_payment sp
+                    JOIN supplier_downpayment dp
+                        ON dp.SupplierDownpaymentID = sp.SupplierPaymentSupplierDownpaymentID
+                    WHERE sp.SupplierPaymentID = ? AND sp.SupplierPaymentIsActive = 'Y'";
+
+                $que = $this->db->query($sql, [$para['paymentID']]);
+                if (!$que) {
+                    throw new Exception("[Error] failed get DP row data", 2);
+                }
+                $data = $que->row_array();
+
+                $sql_detail = "SELECT
+                        CONCAT('DP-', M_ItemDesc)                                       AS M_ItemDesc,
+                        PurchaseOrderDetailQty                                          AS SupplierInvoiceDetailQty,
+                        PurchaseOrderDetailPrice                                        AS SupplierInvoiceDetailPrice,
+                        PurchaseOrderSummaryDiscountAmount                              AS SupplierInvoiceDetailDiscountAmount,
+                        (PurchaseOrderDetailPrice - PurchaseOrderSummaryDiscountAmount) AS DiscountedPrice,
+                        PurchaseOrderSummaryTotal                                       AS SupplierInvoiceDetailTotal
+                    FROM supplier_downpayment
+                    JOIN purchase_order
+                        ON SupplierDownpaymentPurchasOrderID = PurchaseOrderID
+                    JOIN purchase_order_detail
+                        ON PurchaseOrderDetailPurchaseOrderID = PurchaseOrderID
+                        AND PurchaseOrderDetailIsActive = 'Y'
+                    JOIN purchase_order_summary
+                        ON PurchaseOrderSummaryID = PurchaseOrderDetailPurchaseSummaryID
+                        AND PurchaseOrderSummaryIsActive = 'Y'
+                    JOIN m_item
+                        ON M_ItemID = PurchaseOrderDetailItemID
+                    WHERE SupplierDownpaymentPurchasOrderID = ?";
+
+                $que_detail = $this->db->query($sql_detail, [
+                    $data['SupplierDownpaymentPurchasOrderID']
+                ]);
+                if (!$que_detail) {
+                    throw new Exception('failed to get dp detail', 2);
+                }
+
+                $data['detail'] = $que_detail->result_array();
+            } else {
+                $sql = "SELECT
+                        inst.SupplierInstallmentAmount        AS SupplierInvoiceSubTotal,
+                        0                                      AS SupplierInvoiceShippingCost,
+                        0                                      AS SupplierInvoiceDiscountPercent,
+                        0                                      AS SupplierInvoiceDiscountAmount,
+                        0                                      AS SupplierInvoiceTaxPercentPpn,
+                        0                                      AS SupplierInvoiceTaxAmountPpn,
+                        inst.SupplierInstallmentAmount        AS SupplierInvoiceGrandTotal,
+                        si.SupplierInvoiceID,
+                        'INSTALLMENT'                          AS type
+                    FROM supplier_payment sp
+                    JOIN supplier_installment inst
+                        ON inst.SupplierInstallmentID = sp.SupplierPaymentSupplierInstallmentID
+                    JOIN supplier_invoice si
+                        ON si.SupplierInvoiceID = inst.SupplierInstallmentSupplierInvoiceID
+                    WHERE sp.SupplierPaymentID = ? AND sp.SupplierPaymentIsActive = 'Y'";
+
+                $que = $this->db->query($sql, [$para['paymentID']]);
+                if (!$que) {
+                    throw new Exception("[Error] failed get Installment row data", 2);
+                }
+                $data = $que->row_array();
+
+                $sql_detail = "SELECT
+                        M_ItemDesc,
+                        SupplierInvoiceDetailQty,
+                        SupplierInvoiceDetailPrice,
+                        SupplierInvoiceDetailDiscountAmount,
+                        (SupplierInvoiceDetailPrice - SupplierInvoiceDetailDiscountAmount) AS DiscountedPrice,
+                        SupplierInvoiceDetailTotal
+                    FROM supplier_invoice_detail
+                    JOIN m_item
+                        ON M_ItemID = SupplierInvoiceDetailItemID AND M_ItemIsActive = 'Y'
+                    WHERE SupplierInvoiceDetailSupplierInvoiceID = ?
+                        AND SupplierInvoiceDetailIsActive = 'Y'
+                    GROUP BY SupplierInvoiceDetailID";
+
+                $que_detail = $this->db->query($sql_detail, [$data['SupplierInvoiceID']]);
+                if (!$que_detail) {
+                    throw new Exception("[Error] failed to get installment item payments", 2);
+                }
+
+                unset($data['SupplierInvoiceID']);
+                $data['detail'] = $que_detail->result_array();
+            }
 
             $this->sys_ok($data);
         } catch (Exception $exc) {
@@ -214,12 +381,12 @@ class Billv2 extends MY_Controller {
             $user = $this->sys_user;
 
             if ($para['userlevel'] == '1') {
-                $sql = "UPDATE supplier_payment SET 
+                $sql = "UPDATE supplier_payment SET
                     SupplierPaymentIsVerif = 'Y',
                     SupplierPaymentStatus = 'Verified',
                     SupplierPaymentVerifUserID = ?,
                     SupplierPaymentVerifDate = NOW()
-                WHERE SupplierPaymentID = ? 
+                WHERE SupplierPaymentID = ?
                     AND SupplierPaymentIsActive = 'Y'";
                 $query = $this->db->query($sql, [
                     $user['M_UserID'], $para['paymentID']
@@ -231,12 +398,12 @@ class Billv2 extends MY_Controller {
             }
 
             if ($para['userlevel'] == '2') {
-                $sql = "UPDATE supplier_payment SET 
+                $sql = "UPDATE supplier_payment SET
                     SupplierPaymentIsApproved = 'Y',
                     SupplierPaymentStatus = 'Approved',
                     SupplierPaymentApprovedUserID = ?,
                     SupplierPaymentApprovedDate = NOW()
-                WHERE SupplierPaymentID = ? 
+                WHERE SupplierPaymentID = ?
                     AND SupplierPaymentIsActive = 'Y'";
                 $query = $this->db->query($sql, [
                     $user['M_UserID'], $para['paymentID']
@@ -261,4 +428,4 @@ class Billv2 extends MY_Controller {
             exit;
         }
     }
-}
+}

application/controllers/mockup/supplierpaymentv4/Bill.php

@@ -1,20 +1,22 @@
 <?php
 class Bill extends MY_Controller
 {
-   var $db_onedev;
-   public function index()
-   {
-      echo "Bill API";
-   }
-   public function __construct()
-   {
-      parent::__construct();
-      $this->db_onedev = $this->load->database("onedev", true);
-   }
-   
-   public function add_notes($orderid){
-	   $sql = "	SELECT SupplierPaymentSupplierInvoiceID as note_order_id,
-				SupplierPaymentID as note_id, 
+    var $db_onedev;
+    public function index()
+    {
+        echo "Bill API";
+    }
+
+    public function __construct()
+    {
+        parent::__construct();
+        $this->db_onedev = $this->load->database("onedev", true);
+    }
+
+    public function add_notes($orderid)
+    {
+        $sql = "SELECT SupplierPaymentSupplierInvoiceID as note_order_id,
+				SupplierPaymentID as note_id,
 				SupplierPaymentDetailSupplierInvoiceDetailID as detail_id,
 				SupplierPaymentDate as note_date,
 				SupplierPaymentNumber as note_number,
@@ -41,63 +43,205 @@ class Bill extends MY_Controller
 				LEFT JOIN m_user a ON SupplierPaymentApprovedUserID = a.M_UserID
 				LEFT JOIN m_user b ON SupplierPaymentVerifUserID = b.M_UserID
 				WHERE
-				SupplierPaymentSupplierInvoiceID = {$orderid} 
+				SupplierPaymentSupplierInvoiceID = {$orderid}
 				AND
 			 	SupplierPaymentIsActive = 'Y'
 				GROUP BY SupplierPaymentID";
-		$query = $this->db_onedev->query($sql);
-		if ($query) {
-			$rows = $query->result_array();
-			if($rows){
-				foreach($rows as $k => $v){
-					$rows[$k]['tests'] = $this->add_tests($v['note_id']);
-				}
-			}
-			return $rows;
-			
-		} else {
-			$this->sys_error_db("get notes", $this->db_onedev);
-			exit;
-		} 
-   }
-   public function add_tagihans($orderid){
-	$sql = "SELECT SupplierInvoiceID as tagihan_id, 
-			 PurchaseOrderNumber as tagihan_number,
-			 jurnalTxDescription as pasien,
-			 jurnalTxCredit as tagihan_total,
-			 IF(SupplierPaymentDetailID IS NULL , jurnalTxCredit, jurnalTxCredit - SUM(SupplierPaymentDetailAmount)) as tagihan_tagihan,
-			 0 as tagihan_bayar,
-			 DATE_FORMAT(SupplierInvoiceDueDate,'%d-%m-%Y') as tagihan_duedate,
-			 SupplierInvoiceIsActive as tagihan_active,
-			 'N' as show_detail,
-			 jurnalTxID SupplierInvoiceDetailID,
-			 PurchaseOrderID SupplierInvoiceDetailPurchaseOrderID
+        $query = $this->db_onedev->query($sql);
+        if ($query) {
+            $rows = $query->result_array();
+            if ($rows) {
+                foreach ($rows as $k => $v) {
+                    $rows[$k]['tests'] = $this->add_tests($v['note_id']);
+                }
+            }
+            return $rows;
+        } else {
+            $this->sys_error_db("get notes", $this->db_onedev);
+            exit;
+        }
+    }
 
-			 FROM supplier_invoice
-			 JOIN receive_order_po ON ReceiveOrderPoID = SupplierInvoiceReceiveOrderPoID
-			 JOIN receive_order_po_detail ON ReceiveOrderPoDetailReceiveOrderPoID = ReceiveOrderPoID
-			 JOIN purchase_order ON ReceiveOrderPoDetailPurchaseOrderID = PurchaseOrderID
-			 JOIN jurnal_addon ON jurnalAddOnValue = SupplierInvoiceNumber
-			 JOIN jurnal_tx ON jurnalTxJurnalID = jurnalAddOnJurnalID AND jurnalTxCredit <> 0 AND jurnalTxCoaID <> 563
-			 LEFT JOIN supplier_payment ON SupplierPaymentSupplierInvoiceID = SupplierInvoiceID AND SupplierInvoiceIsActive = 'Y'
-			 LEFT JOIN supplier_payment_detail ON SupplierPaymentDetailSupplierPaymentID = SupplierPaymentID AND  SupplierPaymentDetailSupplierInvoiceDetailID = jurnalTxID AND SupplierPaymentDetailIsActive = 'Y'
-			 WHERE
-			 SupplierInvoiceID = {$orderid}
-			 GROUP BY jurnalTxID
-			 ";
-	 $query = $this->db_onedev->query($sql);
-	 if ($query) {
-		 $rows = $query->result_array();
-		 return $rows;
-		 
-	 } else {
-		 $this->sys_error_db("get notes", $this->db_onedev);
-		 exit;
-	 } 
-}
-   public function add_tests($orderid){
-	$sql = "	SELECT SupplierPaymentSupplierInvoiceID as note_order_id,
-			 SupplierPaymentID as note_id, 
+    public function add_notes_downpayment($orderid)
+    {
+        $sql = "SELECT SupplierPaymentSupplierDownpaymentID AS note_order_id,
+                SupplierPaymentID AS note_id,
+                SupplierDownpaymentID AS detail_id,
+                SupplierPaymentDate AS note_date,
+                SupplierPaymentNumber AS note_number,
+                GROUP_CONCAT(DISTINCT coaDescription separator ' , ') AS paymenttypes_name,
+                SUM(SupplierDownpaymentAmount) AS note_amount,
+                n.M_UserUsername AS note_user,
+                SupplierDownpaymentIsActive AS note_active,
+                'N' AS show_detail,
+                SupplierPaymentNote AS keterangan,
+                SupplierPaymentCoaID,
+                coaID,
+                coaDescription,
+                SupplierPaymentIsConfirm,
+                CONCAT(
+                    'Confirmed by : ',c.M_UserUsername, ' ',
+                    DATE_FORMAT(SupplierPaymentConfirmDate, '%d-%m-%Y %H:%i')
+                ) AS d_confirm,
+                SupplierPaymentIsApproved,
+                CONCAT(
+                    'Approved by : ',a.M_UserUsername, ' ',
+                    DATE_FORMAT(SupplierPaymentApprovedDate,'%d-%m-%Y %H:%i')
+                ) AS d_approved,
+                CONCAT(
+                    'Verified by : ',b.M_UserUsername, ' ',
+                    DATE_FORMAT(SupplierPaymentVerifDate,'%d-%m-%Y %H:%i')
+                ) AS d_verif
+            FROM supplier_payment
+            JOIN supplier_downpayment
+                ON SupplierDownpaymentID = SupplierPaymentSupplierDownpaymentID
+                AND SupplierDownpaymentIsActive = 'Y'
+            LEFT JOIN coa ON SupplierPaymentCoaID = coaID
+            LEFT JOIN m_user n ON SupplierPaymentUserID = n.M_UserID
+            LEFT JOIN m_user c ON SupplierPaymentConfirmUserID = c.M_UserID
+            LEFT JOIN m_user a ON SupplierPaymentApprovedUserID = a.M_UserID
+            LEFT JOIN m_user b ON SupplierPaymentVerifUserID = b.M_UserID
+            WHERE SupplierPaymentSupplierDownpaymentID = {$orderid}
+                AND SupplierPaymentIsActive = 'Y'
+            GROUP BY SupplierPaymentID";
+        $query = $this->db_onedev->query($sql);
+        if ($query) {
+            $rows = $query->result_array();
+            return $rows ?: [];
+        } else {
+            $this->sys_error_db("get DP notes", $this->db_onedev);
+            exit;
+        }
+    }
+
+    public function add_notes_installment($orderid)
+    {
+        $sql = "SELECT SupplierPaymentSupplierInstallmentID as note_order_id,
+                SupplierPaymentID as note_id,
+                SupplierInstallmentID as detail_id,
+                SupplierPaymentDate as note_date,
+                SupplierPaymentNumber as note_number,
+                GROUP_CONCAT(DISTINCT coaDescription separator ' , ') as paymenttypes_name,
+                SUM(SupplierInstallmentAmount) as note_amount,
+                n.M_UserUsername as note_user,
+                SupplierInstallmentIsActive as note_active,
+                'N' as show_detail,
+                SupplierPaymentNote as keterangan,
+                SupplierPaymentCoaID,
+                coaID,
+                coaDescription,
+                SupplierPaymentIsConfirm,
+                CONCAT(
+                    'Confirmed by : ',c.M_UserUsername, ' ',
+                    DATE_FORMAT(SupplierPaymentConfirmDate,'%d-%m-%Y %H:%i')
+                ) AS d_confirm,
+                SupplierPaymentIsApproved,
+                CONCAT(
+                    'Approved by : ',a.M_UserUsername, ' ',
+                    DATE_FORMAT(SupplierPaymentApprovedDate,'%d-%m-%Y %H:%i')
+                ) AS d_approved,
+                CONCAT(
+                    'Verified by : ',b.M_UserUsername, ' ',
+                    DATE_FORMAT(SupplierPaymentVerifDate,'%d-%m-%Y %H:%i')
+                ) AS d_verif
+            FROM supplier_payment
+            JOIN supplier_installment
+                ON SupplierInstallmentID = SupplierPaymentSupplierInstallmentID
+                AND SupplierInstallmentIsActive = 'Y'
+            LEFT JOIN coa ON SupplierPaymentCoaID = coaID
+            LEFT JOIN m_user n ON SupplierPaymentUserID = n.M_UserID
+            LEFT JOIN m_user c ON SupplierPaymentConfirmUserID = c.M_UserID
+            LEFT JOIN m_user a ON SupplierPaymentApprovedUserID = a.M_UserID
+            LEFT JOIN m_user b ON SupplierPaymentVerifUserID = b.M_UserID
+            WHERE SupplierPaymentSupplierInstallmentID = {$orderid}
+                AND SupplierPaymentIsActive = 'Y'
+            GROUP BY SupplierPaymentID";
+        $query = $this->db_onedev->query($sql);
+        if ($query) {
+            $rows = $query->result_array();
+            return $rows ?: [];
+        } else {
+            $this->sys_error_db("get Installment notes", $this->db_onedev);
+            exit;
+        }
+    }
+
+    public function add_tagihans($orderid)
+    {
+        $sql_dp = "SELECT
+                Fa_ClassDownPaymentCoaID
+            FROM supplier_invoice_detail
+            JOIN m_item
+                ON M_ItemID = SupplierInvoiceDetailItemID
+                AND SupplierInvoiceDetailSupplierInvoiceID = ?
+                AND SupplierInvoiceDetailIsActive = 'Y'
+                AND SupplierInvoiceDetailIsVerified = 'Y'
+            JOIN fa_class
+                ON Fa_ClassID = M_ItemFa_ClassID
+                AND Fa_ClassIsActive = 'Y'";
+        $que_dp = $this->db_onedev->query($sql_dp, [$orderid]);
+        if (!$que_dp) {
+            $this->sys_error_db("failed to get coa id dp tagihans", $this->db_onedev);
+            exit;
+        }
+        $dataDP = $que_dp->result_array();
+        $excludeIds = array_column($dataDP, 'Fa_ClassDownPaymentCoaID');
+        $excludeIds[] = 563;                          // always excluded
+        $excludeIds = array_unique($excludeIds);
+        $excludeIds = array_values($excludeIds);     // re-index
+
+        $notInPlaceholders = implode(',', array_fill(0, count($excludeIds), '?'));
+
+        $sql = "SELECT
+                SupplierInvoiceID as tagihan_id,
+                PurchaseOrderNumber as tagihan_number,
+                jurnalTxDescription as pasien,
+                jurnalTxCredit as tagihan_total,
+                IF(SupplierPaymentDetailID IS NULL , jurnalTxCredit, jurnalTxCredit - SUM(SupplierPaymentDetailAmount)) as tagihan_tagihan,
+                0 as tagihan_bayar,
+                DATE_FORMAT(SupplierInvoiceDueDate,'%d-%m-%Y') as tagihan_duedate,
+                SupplierInvoiceIsActive as tagihan_active,
+                'N' as show_detail,
+                jurnalTxID SupplierInvoiceDetailID,
+                PurchaseOrderID SupplierInvoiceDetailPurchaseOrderID
+            FROM supplier_invoice
+            JOIN receive_order_po
+                ON ReceiveOrderPoID = SupplierInvoiceReceiveOrderPoID
+            JOIN receive_order_po_detail
+                ON ReceiveOrderPoDetailReceiveOrderPoID = ReceiveOrderPoID
+            JOIN purchase_order
+                ON ReceiveOrderPoDetailPurchaseOrderID = PurchaseOrderID
+            JOIN jurnal_addon
+                ON jurnalAddOnValue = SupplierInvoiceNumber
+                AND jurnalAddOnIsActive = 'Y'
+            JOIN jurnal_tx
+                ON jurnalTxJurnalID = jurnalAddOnJurnalID
+                AND jurnalTxIsActive = 'Y'
+                AND jurnalTxCredit <> 0
+                AND jurnalTxCoaID NOT IN ($notInPlaceholders)
+            LEFT JOIN supplier_payment
+                ON SupplierPaymentSupplierInvoiceID = SupplierInvoiceID
+                AND SupplierInvoiceIsActive = 'Y'
+            LEFT JOIN supplier_payment_detail
+                ON SupplierPaymentDetailSupplierPaymentID = SupplierPaymentID
+                AND  SupplierPaymentDetailSupplierInvoiceDetailID = jurnalTxID
+                AND SupplierPaymentDetailIsActive = 'Y'
+            WHERE SupplierInvoiceID = ?
+            GROUP BY jurnalTxID";
+        $query = $this->db_onedev->query($sql, array_merge($excludeIds, [$orderid]));
+        if ($query) {
+            $rows = $query->result_array();
+            return $rows;
+        } else {
+            $this->sys_error_db("get tagihans", $this->db_onedev);
+            exit;
+        }
+    }
+
+    public function add_tests($orderid)
+    {
+        $sql = "	SELECT SupplierPaymentSupplierInvoiceID as note_order_id,
+			 SupplierPaymentID as note_id,
 			 SupplierPaymentDate as note_date,
 			 SupplierPaymentNumber as note_number,
 			 GROUP_CONCAT(coaDescription separator ' , ') as paymenttypes_name,
@@ -108,150 +252,537 @@ class Bill extends MY_Controller
 			 SupplierInvoiceDetailTotal,
 			 SupplierPaymentDetailAmount
 			 FROM supplier_payment
-			 JOIN supplier_payment_detail ON SupplierPaymentDetailSupplierPaymentID = SupplierPaymentID 
+			 JOIN supplier_payment_detail ON SupplierPaymentDetailSupplierPaymentID = SupplierPaymentID
 			 LEFT JOIN supplier_invoice_detail ON SupplierPaymentDetailSupplierInvoiceDetailID = SupplierInvoiceDetailID
 			 LEFT JOIN purchase_order ON SupplierInvoiceDetailPurchaseOrderID = PurchaseOrderID
 			 LEFT JOIN coa ON SupplierPaymentCoaID = coaID
 			 LEFT JOIN m_user ON SupplierPaymentDetailUserID = M_UserID
 			 WHERE
-			 SupplierPaymentID = {$orderid} 
+			 SupplierPaymentID = {$orderid}
 			 GROUP BY SupplierPaymentDetailID";
-	 $query = $this->db_onedev->query($sql);
-	 if ($query) {
-		 $rows = $query->result_array();
-		 if($rows){
-		 }
-		 return $rows;
-		 
-	 } else {
-		 $this->sys_error_db("get notes", $this->db_onedev);
-		 exit;
-	 } 
-}
-   public function search()
-   {
-	    //# cek token valid
-        if (! $this->isLogin) {
-        $this->sys_error("Invalid Token");
-        exit;
+        $query = $this->db_onedev->query($sql);
+        if ($query) {
+            $rows = $query->result_array();
+            if ($rows) {
+            }
+            return $rows;
+        } else {
+            $this->sys_error_db("get notes", $this->db_onedev);
+            exit;
         }
-      $prm = $this->sys_input;
-	  $supplier = $prm["supplier"];
-      $search = $prm["search"];
-      $status = $prm["status"];
-	  $startdate = $prm["startdate"];
-	  $enddate = $prm["enddate"];
-	  $regionalid = $this->sys_user['S_RegionalID'];
+    }
 
-	  $number_limit = 10;
-	  $number_offset = ($prm['current_page'] - 1) * $number_limit ;
+    public function search_old()
+    {
+        //# cek token valid
+        if (! $this->isLogin) {
+            $this->sys_error("Invalid Token");
+            exit;
+        }
 
-	  $where = "SupplierInvoiceIsActive = 'Y'
-	  AND SupplierInvoiceStatus = 'Approved'
-	  AND SupplierInvoiceGrandTotal > 0 
-	  AND IF(SupplierPaymentID IS NULL,'N','Y') = '{$status}' 
-	  AND (SupplierInvoiceNumber LIKE '%{$search}%' AND SupplierName LIKE '%{$supplier}%')
-	  AND ReceiveOrderPoS_RegionalID = {$regionalid}
-	  AND SupplierInvoiceDraftPaymentDate BETWEEN '{$startdate}' AND '{$enddate}'";
-		
-	  
-	    
+        $prm = $this->sys_input;
+        $supplier = $prm["supplier"];
+        $search = $prm["search"];
+        $status = $prm["status"];
+        $startdate = $prm["startdate"];
+        $enddate = $prm["enddate"];
+        $regionalid = $this->sys_user['S_RegionalID'];
 
-      $sql = "	SELECT count(*) as total
-				FROM supplier_invoice
-				JOIN jurnal_addon ON jurnalAddOnValue = SupplierInvoiceNumber
-				LEFT JOIN supplier_payment ON SupplierInvoiceID = SupplierPaymentSupplierInvoiceID AND SupplierPaymentIsActive = 'Y'
-				LEFT JOIN supplier ON SupplierInvoiceSupplierID = SupplierID
-				JOIN receive_order_po ON SupplierInvoiceReceiveOrderPoID = ReceiveOrderPoID
-				WHERE
-				$where 
-				";
- // echo $sql;
-		$query = $this->db_onedev->query($sql, $sql_param);
+        $number_limit = 10;
+        $number_offset = ($prm['current_page'] - 1) * $number_limit;
 
-		
-		$tot_count = 0;
-		$tot_page = 0;
-		if ($query) {
-			$tot_count = $query->result_array()[0]["total"];
-			$tot_page = ceil($tot_count/$number_limit);
-		} else {
-			$this->sys_error_db("supplier_invoice count", $this->db_onedev);
-			exit;
-		}    
-	  
-	  
-		$sql = 	"SELECT supplier_invoice.*,
-				SupplierName,
-				'' M_MouName,
-				0 as totalbill,
-				0 as paid,
-				0 as unpaid,
-				SupplierInvoiceIsLunas as flaglunas,
-				0 as SupplierPaymentID,
-				'' as SupplierPaymentNumber,
-				0 as SupplierPaymentAmount,			
-				'' as SupplierPaymentDate,
-				'' as SupplierInvoiceIssueRefNumber,
-				'' as notes,
-				'' as tagihans,
-				'N' as isbillterpusat,
-				DATE_FORMAT(SupplierInvoiceDraftPaymentDate,'%d-%m-%Y') as tanggalbayar,
-				DATE_FORMAT(SupplierInvoiceDraftPaymentDate,'%d%m%Y') as tanggalbayartext,
-				IF(SupplierPaymentID IS NULL,'N','Y') as status_invoice,
-				IFNULL(SupplierPaymentIsApproved,'N') as SupplierPaymentIsApproved,
-				IFNULL(SupplierPaymentIsVerif,'N') as SupplierPaymentIsVerif,
-				IFNULL(SupplierPaymentCashierNumber,'') SupplierPaymentCashierNumber
-
-				FROM supplier_invoice
-				LEFT JOIN supplier ON SupplierInvoiceSupplierID = SupplierID
-				JOIN jurnal_addon ON jurnalAddOnValue = SupplierInvoiceNumber
-				JOIN receive_order_po ON SupplierInvoiceReceiveOrderPoID = ReceiveOrderPoID
-				LEFT JOIN supplier_payment ON SupplierPaymentSupplierInvoiceID = SupplierInvoiceID AND SupplierPaymentIsActive = 'Y'
-				WHERE
-				$where 
-				GROUP BY SupplierInvoiceID
-				ORDER BY SupplierInvoiceID ASC
-				limit $number_limit offset $number_offset";
-		//echo $sql;
-		$query = $this->db_onedev->query($sql, $sql_param);
-		$rows = $query->result_array();
-		if($rows){
-			foreach($rows as $k => $v){
-				$s_payment = $this->db_onedev->query("SELECT GROUP_CONCAT(SupplierPaymentNumber SEPARATOR ', ') as SupplierPaymentNumber,
-				SUM(IFNULL(SupplierPaymentAmount,0)) as SupplierPaymentAmount,
-				IFNULL(SupplierPaymentID,0) SupplierPaymentID,
-				GROUP_CONCAT(DATE_FORMAT(SupplierPaymentDate,'%d-%m-%Y') SEPARATOR ', ') as SupplierPaymentDate
-				FROM supplier_payment
-				WHERE SupplierPaymentIsActive = 'Y' AND SupplierPaymentSupplierInvoiceID = {$v['SupplierInvoiceID']}")->row();
-
-				$s_jurnal = $this->db_onedev->query("SELECT SUM(jurnalTxCredit) totalbill
-				FROM supplier_invoice
-				JOIN jurnal_addon ON jurnalAddOnValue = SupplierInvoiceNumber
-			 	JOIN jurnal_tx ON jurnalTxJurnalID = jurnalAddOnJurnalID AND jurnalTxCredit <> 0 AND jurnalTxCoaID <> 563
-				WHERE SupplierInvoiceID = {$v['SupplierInvoiceID']}
-				GROUP BY SupplierInvoiceID")->row();
-				$amount = $s_payment->SupplierPaymentAmount ? $s_payment->SupplierPaymentAmount : "0.00";
-				$unpaid = (float)$s_jurnal->totalbill - (float)$amount;
-				$rows[$k]['SupplierPaymentID'] = $s_payment->SupplierPaymentID ? $s_payment->SupplierPaymentID : '0';
-				$rows[$k]['SupplierPaymentNumber'] = $s_payment->SupplierPaymentNumber ? $s_payment->SupplierPaymentNumber : '';
-				$rows[$k]['SupplierPaymentAmount'] = $amount;
-				$rows[$k]['SupplierPaymentDate'] = $s_payment->SupplierPaymentDate;
-				$rows[$k]['paid'] = $amount;
-				$rows[$k]['totalbill'] = $s_jurnal->totalbill ? $s_jurnal->totalbill : "0.00";
-				$rows[$k]['unpaid'] = number_format($unpaid, 2, '.', '');
-
-				$rows[$k]['notes'] = $this->add_notes($v['SupplierInvoiceID']);
-				$rows[$k]['tagihans'] = $this->add_tagihans($v['SupplierInvoiceID']);
-
-			}
-		}
-	  
-
-		$result = array("total" => $tot_page, "records" => $rows, "sql"=> $this->db_onedev->last_query());
-		$this->sys_ok($result);
-		exit;
-   }
+        $where = "SupplierInvoiceIsActive = 'Y'
+    AND SupplierInvoiceStatus = 'Approved'
+    AND SupplierInvoiceGrandTotal > 0
+    AND IF(SupplierPaymentID IS NULL,'N','Y') = '{$status}'
+    AND (SupplierInvoiceNumber LIKE '%{$search}%' AND SupplierName LIKE '%{$supplier}%')
+    AND ReceiveOrderPoS_RegionalID = {$regionalid}
+    AND SupplierInvoiceDraftPaymentDate BETWEEN '{$startdate}' AND '{$enddate}'";
 
 
+
+
+        $sql = "	SELECT count(*) as total
+    	FROM supplier_invoice
+    	JOIN jurnal_addon ON jurnalAddOnValue = SupplierInvoiceNumber
+    	LEFT JOIN supplier_payment ON SupplierInvoiceID = SupplierPaymentSupplierInvoiceID AND SupplierPaymentIsActive = 'Y'
+    	LEFT JOIN supplier ON SupplierInvoiceSupplierID = SupplierID
+    	JOIN receive_order_po ON SupplierInvoiceReceiveOrderPoID = ReceiveOrderPoID
+    	WHERE
+    	$where
+    	";
+        // echo $sql;
+        $query = $this->db_onedev->query($sql, $sql_param);
+
+
+        $tot_count = 0;
+        $tot_page = 0;
+        if ($query) {
+            $tot_count = $query->result_array()[0]["total"];
+            $tot_page = ceil($tot_count / $number_limit);
+        } else {
+            $this->sys_error_db("supplier_invoice count", $this->db_onedev);
+            exit;
+        }
+
+
+        $sql =     "SELECT supplier_invoice.*,
+    	SupplierName,
+    	'' M_MouName,
+    	0 as totalbill,
+    	0 as paid,
+    	0 as unpaid,
+    	SupplierInvoiceIsLunas as flaglunas,
+    	0 as SupplierPaymentID,
+    	'' as SupplierPaymentNumber,
+    	0 as SupplierPaymentAmount,
+    	'' as SupplierPaymentDate,
+    	'' as SupplierInvoiceIssueRefNumber,
+    	'' as notes,
+    	'' as tagihans,
+    	'N' as isbillterpusat,
+    	DATE_FORMAT(SupplierInvoiceDraftPaymentDate,'%d-%m-%Y') as tanggalbayar,
+    	DATE_FORMAT(SupplierInvoiceDraftPaymentDate,'%d%m%Y') as tanggalbayartext,
+    	IF(SupplierPaymentID IS NULL,'N','Y') as status_invoice,
+    	IFNULL(SupplierPaymentIsApproved,'N') as SupplierPaymentIsApproved,
+    	IFNULL(SupplierPaymentIsVerif,'N') as SupplierPaymentIsVerif,
+    	IFNULL(SupplierPaymentCashierNumber,'') SupplierPaymentCashierNumber
+
+    	FROM supplier_invoice
+    	LEFT JOIN supplier ON SupplierInvoiceSupplierID = SupplierID
+    	JOIN jurnal_addon ON jurnalAddOnValue = SupplierInvoiceNumber
+    	JOIN receive_order_po ON SupplierInvoiceReceiveOrderPoID = ReceiveOrderPoID
+    	LEFT JOIN supplier_payment ON SupplierPaymentSupplierInvoiceID = SupplierInvoiceID AND SupplierPaymentIsActive = 'Y'
+    	WHERE
+    	$where
+    	GROUP BY SupplierInvoiceID
+    	ORDER BY SupplierInvoiceID ASC
+    	limit $number_limit offset $number_offset";
+        //echo $sql;
+        $query = $this->db_onedev->query($sql, $sql_param);
+        $rows = $query->result_array();
+        if ($rows) {
+            foreach ($rows as $k => $v) {
+                $s_payment = $this->db_onedev->query("SELECT GROUP_CONCAT(SupplierPaymentNumber SEPARATOR ', ') as SupplierPaymentNumber,
+    	SUM(IFNULL(SupplierPaymentAmount,0)) as SupplierPaymentAmount,
+    	IFNULL(SupplierPaymentID,0) SupplierPaymentID,
+    	GROUP_CONCAT(DATE_FORMAT(SupplierPaymentDate,'%d-%m-%Y') SEPARATOR ', ') as SupplierPaymentDate
+    	FROM supplier_payment
+    	WHERE SupplierPaymentIsActive = 'Y' AND SupplierPaymentSupplierInvoiceID = {$v['SupplierInvoiceID']}")->row();
+
+                $s_jurnal = $this->db_onedev->query("SELECT SUM(jurnalTxCredit) totalbill
+    	FROM supplier_invoice
+    	JOIN jurnal_addon ON jurnalAddOnValue = SupplierInvoiceNumber
+     	JOIN jurnal_tx ON jurnalTxJurnalID = jurnalAddOnJurnalID AND jurnalTxCredit <> 0 AND jurnalTxCoaID <> 563
+    	WHERE SupplierInvoiceID = {$v['SupplierInvoiceID']}
+    	GROUP BY SupplierInvoiceID")->row();
+                $amount = $s_payment->SupplierPaymentAmount ? $s_payment->SupplierPaymentAmount : "0.00";
+                $unpaid = (float)$s_jurnal->totalbill - (float)$amount;
+                $rows[$k]['SupplierPaymentID'] = $s_payment->SupplierPaymentID ? $s_payment->SupplierPaymentID : '0';
+                $rows[$k]['SupplierPaymentNumber'] = $s_payment->SupplierPaymentNumber ? $s_payment->SupplierPaymentNumber : '';
+                $rows[$k]['SupplierPaymentAmount'] = $amount;
+                $rows[$k]['SupplierPaymentDate'] = $s_payment->SupplierPaymentDate;
+                $rows[$k]['paid'] = $amount;
+                $rows[$k]['totalbill'] = $s_jurnal->totalbill ? $s_jurnal->totalbill : "0.00";
+                $rows[$k]['unpaid'] = number_format($unpaid, 2, '.', '');
+
+                $rows[$k]['notes'] = $this->add_notes($v['SupplierInvoiceID']);
+                $rows[$k]['tagihans'] = $this->add_tagihans($v['SupplierInvoiceID']);
+            }
+        }
+
+
+        $result = array("total" => $tot_page, "records" => $rows, "sql" => $this->db_onedev->last_query());
+        $this->sys_ok($result);
+        exit;
+    }
+
+    /**
+     * search — unified invoice + downpayment list:
+     *   - Only columns the frontend actually reads (no wildcard, no dead aliases)
+     *   - UNION ALL merges supplier_invoice and supplier_downpayment
+     *   - DP rows enriched via SupplierPaymentSupplierDownpaymentID FK
+     *   - All queries use PDO parameterised placeholders
+     */
+    public function search()
+    {
+        if (! $this->isLogin) {
+            $this->sys_error("Invalid Token");
+            exit;
+        }
+
+        $prm = $this->sys_input;
+        $supplier   = $prm["supplier"];
+        $search     = $prm["search"];
+        $status     = $prm["status"];
+        $startdate  = $prm["startdate"];
+        $enddate    = $prm["enddate"];
+        $regionalid = $this->sys_user['S_RegionalID'];
+
+        $number_limit  = 10;
+        $number_offset = ((int)$prm['current_page'] - 1) * $number_limit;
+
+        // ── INVOICE WHERE ──────────────────────────────────────────
+        $inv_where = " SupplierInvoiceIsActive = 'Y'
+            AND SupplierInvoiceStatus = 'Approved'
+            AND SupplierInvoiceGrandTotal > 0
+            AND SupplierInvoiceIsInstallment = 'N'
+            AND IF(SupplierPaymentID IS NULL,'N','Y') = ?
+            AND SupplierInvoiceNumber LIKE ?
+            AND SupplierName LIKE ?
+            AND ReceiveOrderPoS_RegionalID = ?
+            AND SupplierInvoiceDraftPaymentDate BETWEEN ? AND ? ";
+
+        $inv_params = [
+            $status,
+            '%' . $search . '%',
+            '%' . $supplier . '%',
+            $regionalid,
+            $startdate,
+            $enddate,
+        ];
+
+        // ── DOWNPAYMENT WHERE ──────────────────────────────────────
+        $dp_where = " SupplierDownpaymentIsActive = 'Y'
+            AND IF(SupplierDownpaymentStatus = 'Paid','Y','N') = ?
+            AND PurchaseOrderNumber LIKE ?
+            AND SupplierName LIKE ?
+            AND PurchaseOrderS_RegionalID = ?
+            AND SupplierDownpaymentDueDate BETWEEN ? AND ? ";
+
+        $dp_params = [
+            $status,
+            '%' . $search . '%',
+            '%' . $supplier . '%',
+            $regionalid,
+            $startdate,
+            $enddate,
+        ];
+
+        // ── INSTALLMENT WHERE ───────────────────────────────────
+        $inst_where = " inst.SupplierInstallmentIsActive = 'Y'
+            AND IF(sp.SupplierPaymentID IS NULL,'N','Y') = ?
+            AND CONCAT('INST-', si.SupplierInvoiceNumber) LIKE ?
+            AND sup.SupplierName LIKE ?
+            AND rop.ReceiveOrderPoS_RegionalID = ?
+            AND inst.SupplierInstallmentDueDate BETWEEN ? AND ? ";
+
+        $inst_params = [
+            $status,
+            '%' . $search . '%',
+            '%' . $supplier . '%',
+            $regionalid,
+            $startdate,
+            $enddate,
+        ];
+
+        // ── COUNT query — UNION of all three sources ────────────
+        $sql_count = "SELECT SUM(cnt) as total FROM (
+            SELECT count(*) as cnt
+            FROM supplier_invoice
+            JOIN jurnal_addon ON jurnalAddOnValue = SupplierInvoiceNumber
+            LEFT JOIN supplier_payment
+                ON SupplierInvoiceID = SupplierPaymentSupplierInvoiceID
+                AND SupplierPaymentIsActive = 'Y'
+            LEFT JOIN supplier ON SupplierInvoiceSupplierID = SupplierID
+            JOIN receive_order_po ON SupplierInvoiceReceiveOrderPoID = ReceiveOrderPoID
+            WHERE $inv_where
+
+            UNION ALL
+
+            SELECT count(*) as cnt
+            FROM supplier_downpayment
+            JOIN purchase_order ON PurchaseOrderID = SupplierDownpaymentPurchasOrderID
+            JOIN supplier ON SupplierID = SupplierDownpaymentSupplierID
+            WHERE $dp_where
+
+            UNION ALL
+
+            SELECT count(*) as cnt
+            FROM supplier_installment inst
+            LEFT JOIN supplier_payment sp
+                ON sp.SupplierPaymentSupplierInstallmentID = inst.SupplierInstallmentID
+                AND sp.SupplierPaymentIsActive = 'Y'
+            JOIN supplier_invoice si
+                ON si.SupplierInvoiceID = inst.SupplierInstallmentSupplierInvoiceID
+            JOIN receive_order_po rop
+                ON rop.ReceiveOrderPoID = si.SupplierInvoiceReceiveOrderPoID
+            JOIN supplier sup
+                ON sup.SupplierID = inst.SupplierInstallmentSupplierID
+            WHERE $inst_where
+        ) AS combined";
+
+        $count_params = array_merge($inv_params, $dp_params, $inst_params);
+        $query = $this->db_onedev->query($sql_count, $count_params);
+
+        $tot_count = 0;
+        $tot_page  = 0;
+        if ($query) {
+            $tot_count = $query->result_array()[0]["total"];
+            $tot_page  = ceil($tot_count / $number_limit);
+        } else {
+            $this->sys_error_db("payment instructions count", $this->db_onedev);
+            exit;
+        }
+
+        // ── DATA query — UNION with identical columns ──────────────
+        $sql_data = "
+            SELECT * FROM (
+                SELECT
+                    SupplierInvoiceID,
+                    0                                         as DownpaymentID,
+                    0                                         as InstallmentID,
+                    SupplierInvoiceNumber,
+                    SupplierInvoiceDraftPaymentDate,
+                    SupplierInvoiceIsLunas                    as flaglunas,
+                    SupplierName,
+                    DATE_FORMAT(SupplierInvoiceDraftPaymentDate,'%d-%m-%Y') as tanggalbayar,
+                    IFNULL(SupplierPaymentIsApproved,'N')    as SupplierPaymentIsApproved,
+                    IFNULL(SupplierPaymentCashierNumber,'')   as SupplierPaymentCashierNumber,
+                    0                                         as totalbill,
+                    0                                         as paid,
+                    0                                         as unpaid,
+                    0                                         as SupplierPaymentID,
+                    ''                                        as SupplierPaymentNumber,
+                    0                                         as SupplierPaymentAmount,
+                    ''                                        as SupplierPaymentDate,
+                    ''                                        as notes,
+                    ''                                        as tagihans,
+                    0                                         as parent_invoice_id,
+                    'INVOICE'                                 as type
+                FROM supplier_invoice
+                LEFT JOIN supplier ON SupplierInvoiceSupplierID = SupplierID
+                JOIN jurnal_addon ON jurnalAddOnValue = SupplierInvoiceNumber
+                JOIN receive_order_po ON SupplierInvoiceReceiveOrderPoID = ReceiveOrderPoID
+                LEFT JOIN supplier_payment
+                    ON SupplierPaymentSupplierInvoiceID = SupplierInvoiceID
+                    AND SupplierPaymentIsActive = 'Y'
+                WHERE $inv_where
+                GROUP BY SupplierInvoiceID
+
+                UNION ALL
+
+                SELECT
+                    0                                             as SupplierInvoiceID,
+                    SupplierDownpaymentID                         as DownpaymentID,
+                    0                                             as InstallmentID,
+                    CONCAT('DP-', PurchaseOrderNumber)            as SupplierInvoiceNumber,
+                    SupplierDownpaymentDueDate                    as SupplierInvoiceDraftPaymentDate,
+                    SupplierDownpaymentIsLunas                    as flaglunas,
+                    SupplierName,
+                    DATE_FORMAT(SupplierDownpaymentDueDate,'%d-%m-%Y') as tanggalbayar,
+                    IFNULL(SupplierPaymentIsApproved,'N')         as SupplierPaymentIsApproved,
+                    IFNULL(SupplierPaymentCashierNumber,'')       as SupplierPaymentCashierNumber,
+                    SupplierDownpaymentAmount                     as totalbill,
+                    CASE WHEN SupplierDownpaymentStatus = 'Paid'
+                         THEN SupplierDownpaymentAmount
+                         ELSE 0 END                               as paid,
+                    CASE WHEN SupplierDownpaymentStatus = 'Paid'
+                         THEN 0
+                         ELSE SupplierDownpaymentAmount END       as unpaid,
+                    0                                             as SupplierPaymentID,
+                    ''                                            as SupplierPaymentNumber,
+                    0                                             as SupplierPaymentAmount,
+                    ''                                            as SupplierPaymentDate,
+                    ''                                            as notes,
+                    ''                                            as tagihans,
+                    0                                             as parent_invoice_id,
+                    'DP'                                          as type
+                FROM supplier_downpayment
+                JOIN purchase_order ON PurchaseOrderID = SupplierDownpaymentPurchasOrderID
+                JOIN supplier ON SupplierID = SupplierDownpaymentSupplierID
+                LEFT JOIN supplier_payment
+                    ON SupplierPaymentSupplierDownpaymentID = SupplierDownpaymentID
+                    AND SupplierPaymentIsActive = 'Y'
+                WHERE $dp_where
+
+                UNION ALL
+
+                SELECT
+                    0                                                      as SupplierInvoiceID,
+                    0                                                      as DownpaymentID,
+                    inst.SupplierInstallmentID                             as InstallmentID,
+                    CONCAT('INST-', si.SupplierInvoiceNumber)              as SupplierInvoiceNumber,
+                    inst.SupplierInstallmentDueDate                        as SupplierInvoiceDraftPaymentDate,
+                    inst.SupplierInstallmentIsLunas                        as flaglunas,
+                    sup.SupplierName,
+                    DATE_FORMAT(inst.SupplierInstallmentDueDate,'%d-%m-%Y') as tanggalbayar,
+                    IFNULL(sp.SupplierPaymentIsApproved,'N')               as SupplierPaymentIsApproved,
+                    IFNULL(sp.SupplierPaymentCashierNumber,'')             as SupplierPaymentCashierNumber,
+                    inst.SupplierInstallmentAmount                         as totalbill,
+                    CASE WHEN inst.SupplierInstallmentStatus = 'Paid'
+                         THEN inst.SupplierInstallmentAmount
+                         ELSE 0 END                                        as paid,
+                    CASE WHEN inst.SupplierInstallmentStatus = 'Paid'
+                         THEN 0
+                         ELSE inst.SupplierInstallmentAmount END           as unpaid,
+                    0                                                      as SupplierPaymentID,
+                    ''                                                     as SupplierPaymentNumber,
+                    0                                                      as SupplierPaymentAmount,
+                    ''                                                     as SupplierPaymentDate,
+                    ''                                                     as notes,
+                    ''                                                     as tagihans,
+                    si.SupplierInvoiceID                                   as parent_invoice_id,
+                    'INSTALLMENT'                                          as type
+                FROM supplier_installment inst
+                LEFT JOIN supplier_payment sp
+                    ON sp.SupplierPaymentSupplierInstallmentID = inst.SupplierInstallmentID
+                    AND sp.SupplierPaymentIsActive = 'Y'
+                JOIN supplier_invoice si
+                    ON si.SupplierInvoiceID = inst.SupplierInstallmentSupplierInvoiceID
+                JOIN receive_order_po rop
+                    ON rop.ReceiveOrderPoID = si.SupplierInvoiceReceiveOrderPoID
+                JOIN supplier sup
+                    ON sup.SupplierID = inst.SupplierInstallmentSupplierID
+                WHERE $inst_where
+            ) AS combined
+            ORDER BY SupplierInvoiceID ASC
+            LIMIT ? OFFSET ?";
+
+        $data_params = array_merge($inv_params, $dp_params, $inst_params, [$number_limit, $number_offset]);
+        $query = $this->db_onedev->query($sql_data, $data_params);
+        if (!$query) {
+            $this->sys_error_db("payment instructions data", $this->db_onedev);
+            exit;
+        }
+        $rows  = $query->result_array();
+
+        // ── ENRICH — per-row sub-queries ───────────────────────────
+        if ($rows) {
+            foreach ($rows as $k => $v) {
+
+                // ── Downpayment branch ──────────────────────────
+                if ($v['type'] === 'DP') {
+                    $dp_id = $v['DownpaymentID'];
+
+                    $s_payment = $this->db_onedev->query("
+                        SELECT
+                            GROUP_CONCAT(SupplierPaymentNumber SEPARATOR ', ') as SupplierPaymentNumber,
+                            SUM(IFNULL(SupplierPaymentAmount,0))                as SupplierPaymentAmount,
+                            IFNULL(SupplierPaymentID,0)                         as SupplierPaymentID,
+                            GROUP_CONCAT(
+                                DATE_FORMAT(SupplierPaymentDate,'%d-%m-%Y')
+                                SEPARATOR ', '
+                            )                                                   as SupplierPaymentDate
+                        FROM supplier_payment
+                        WHERE SupplierPaymentIsActive = 'Y'
+                            AND SupplierPaymentSupplierDownpaymentID = ?",
+                        [$dp_id]
+                    )->row();
+
+                    $amount = $s_payment->SupplierPaymentAmount
+                        ? $s_payment->SupplierPaymentAmount
+                        : "0.00";
+                    $rows[$k]['SupplierPaymentID']     = $s_payment->SupplierPaymentID
+                        ? $s_payment->SupplierPaymentID
+                        : '0';
+                    $rows[$k]['SupplierPaymentNumber'] = $s_payment->SupplierPaymentNumber
+                        ? $s_payment->SupplierPaymentNumber
+                        : '';
+                    $rows[$k]['SupplierPaymentAmount'] = $amount;
+                    $rows[$k]['SupplierPaymentDate']   = $s_payment->SupplierPaymentDate;
+                    $rows[$k]['paid']                  = $amount;
+                    $rows[$k]['notes']                 = $this->add_notes_downpayment($dp_id);
+                    $rows[$k]['tagihans']              = [];
+                    // totalbill, unpaid already correct from UNION CASE
+                    continue;
+                }
+
+                // ── Installment branch ─────────────────────────
+                if ($v['type'] === 'INSTALLMENT') {
+                    $inst_id = $v['InstallmentID'];
+                    $parent_invoice_id = $v['parent_invoice_id'];
+
+                    $s_payment = $this->db_onedev->query("
+                        SELECT
+                            GROUP_CONCAT(SupplierPaymentNumber SEPARATOR ', ') as SupplierPaymentNumber,
+                            SUM(IFNULL(SupplierPaymentAmount,0))                as SupplierPaymentAmount,
+                            IFNULL(SupplierPaymentID,0)                         as SupplierPaymentID,
+                            GROUP_CONCAT(
+                                DATE_FORMAT(SupplierPaymentDate,'%d-%m-%Y')
+                                SEPARATOR ', '
+                            )                                                   as SupplierPaymentDate
+                        FROM supplier_payment
+                        WHERE SupplierPaymentIsActive = 'Y'
+                            AND SupplierPaymentSupplierInstallmentID = ?",
+                        [$inst_id]
+                    )->row();
+
+                    $amount = $s_payment->SupplierPaymentAmount
+                        ? $s_payment->SupplierPaymentAmount
+                        : "0.00";
+                    $rows[$k]['SupplierPaymentID']     = $s_payment->SupplierPaymentID
+                        ? $s_payment->SupplierPaymentID
+                        : '0';
+                    $rows[$k]['SupplierPaymentNumber'] = $s_payment->SupplierPaymentNumber
+                        ? $s_payment->SupplierPaymentNumber
+                        : '';
+                    $rows[$k]['SupplierPaymentAmount'] = $amount;
+                    $rows[$k]['SupplierPaymentDate']   = $s_payment->SupplierPaymentDate;
+                    $rows[$k]['paid']                  = $amount;
+                    $rows[$k]['notes']                 = $this->add_notes_installment($inst_id);
+                    $rows[$k]['tagihans']              = $this->add_tagihans($parent_invoice_id);
+                    // totalbill, unpaid already correct from UNION CASE
+                    continue;
+                }
+
+                // ── Invoice branch ───────────────────────────────
+                $inv_id = $v['SupplierInvoiceID'];
+
+                $s_payment = $this->db_onedev->query("
+                    SELECT
+                        GROUP_CONCAT(SupplierPaymentNumber SEPARATOR ', ') as SupplierPaymentNumber,
+                        SUM(IFNULL(SupplierPaymentAmount,0))                as SupplierPaymentAmount,
+                        IFNULL(SupplierPaymentID,0)                         as SupplierPaymentID,
+                        GROUP_CONCAT(
+                            DATE_FORMAT(SupplierPaymentDate,'%d-%m-%Y')
+                            SEPARATOR ', '
+                        )                                                   as SupplierPaymentDate
+                    FROM supplier_payment
+                    WHERE SupplierPaymentIsActive = 'Y'
+                        AND SupplierPaymentSupplierInvoiceID = ?",
+                    [$inv_id]
+                )->row();
+
+                $s_jurnal = $this->db_onedev->query("
+                    SELECT SUM(jurnalTxCredit) totalbill
+                    FROM supplier_invoice
+                    JOIN jurnal_addon ON jurnalAddOnValue = SupplierInvoiceNumber
+                    JOIN jurnal_tx
+                        ON jurnalTxJurnalID = jurnalAddOnJurnalID
+                        AND jurnalTxCredit <> 0
+                        AND jurnalTxCoaID <> 563
+                    WHERE SupplierInvoiceID = ?
+                    GROUP BY SupplierInvoiceID",
+                    [$inv_id]
+                )->row();
+
+                $amount = $s_payment->SupplierPaymentAmount
+                    ? $s_payment->SupplierPaymentAmount
+                    : "0.00";
+                $unpaid = (float)$s_jurnal->totalbill - (float)$amount;
+
+                $rows[$k]['SupplierPaymentID']     = $s_payment->SupplierPaymentID
+                    ? $s_payment->SupplierPaymentID
+                    : '0';
+                $rows[$k]['SupplierPaymentNumber'] = $s_payment->SupplierPaymentNumber
+                    ? $s_payment->SupplierPaymentNumber
+                    : '';
+                $rows[$k]['SupplierPaymentAmount'] = $amount;
+                $rows[$k]['SupplierPaymentDate']   = $s_payment->SupplierPaymentDate;
+                $rows[$k]['paid']                  = $amount;
+                $rows[$k]['totalbill']             = $s_jurnal->totalbill
+                    ? $s_jurnal->totalbill
+                    : "0.00";
+                $rows[$k]['unpaid']                = number_format($unpaid, 2, '.', '');
+
+                $rows[$k]['notes']    = $this->add_notes($inv_id);
+                $rows[$k]['tagihans'] = $this->add_tagihans($inv_id);
+            }
+        }
+
+        $result = array("total" => $tot_page, "records" => $rows);
+        $this->sys_ok($result);
+        exit;
+    }
 }