# 🛡️ DOKUMENTASI TEKNIS & KODE AUDIT: PENGECEKAN KESESUAIAN SALDO DP PROJECT VS GL 2010050

Dokumen ini berisi spesifikasi teknis, Query SQL, dan implementasi kode PHP CodeIgniter 3 (CI3) untuk mendeteksi secara otomatis potensi kegagalan transaksi **A/R Receipt (`749`)** akibat ketidakcocokan (*mismatch*) antara **Saldo Uang Muka Project di UI (`MdlPaymentUangMukaPpn`)** dengan **Saldo Riil Buku Besar Rekening `2010050` (HUTANG KE KONSUMEN)**.

---

## 1. DOKUMENTASI ATURAN & STANDAR KODE (COMPLIANCE)

* **Framework**: CodeIgniter 3.1.8 + Wiredesignz HMVC (PHP 5.6 Compatible).
* **ISO Compliance**: ISO 9001 (Mutu & Dokumentasi), ISO 27001 (Security & Audit Trail).
* **Keamanan**: OWASP Top 10 (Strict Database Query Binding / CI Query Builder).
* **Docstring Standard**: Menggunakan tag `/** [AGENT_LOG] ... [/AGENT_LOG] */` pada bagian atas class/method tanpa inline comment di dalam logika fungsi.

---

## 2. QUERY SQL NATIVE (DATABASE AUDIT QUERY)

Query ini digunakan untuk memindai seluruh data di tabel `transaksi_payment_source` yang memiliki tagihan project belum lunas dan menyilangkannya dengan saldo riil Buku Besar `2010050` di tabel `transaksi_data_values`:

```sql
SELECT 
    t_inv.id AS payment_source_id,
    t_inv.cabang_id,
    t_inv.nomer AS nomer_nota,
    t_inv.transaksi_id AS invoice_transaksi_id,
    t_inv.extern_id AS customer_id,
    t_inv.extern_nama AS customer_nama,
    t_inv.project_id,
    t_inv.project_nama,
    t_inv.sisa AS sisa_tagihan_nota,
    COALESCE(t_dp.transaksi_id, 0) AS dp_transaksi_id,
    COALESCE(t_dp.sisa, 0) AS dpp_uang_muka_dibutuhkan,
    COALESCE(t_dp.ppn_sisa, 0) AS ppn_uang_muka,
    COALESCE((t_dp.sisa + t_dp.ppn_sisa), 0) AS total_uang_muka_ui,
    COALESCE(gl.saldo_buku_besar_2010050, 0) AS saldo_riil_gl_2010050,
    (COALESCE(gl.saldo_buku_besar_2010050, 0) - COALESCE(t_dp.sisa, 0)) AS selisih_defisit,
    CASE 
        WHEN COALESCE(t_dp.sisa, 0) > 0 AND COALESCE(gl.saldo_buku_besar_2010050, 0) < COALESCE(t_dp.sisa, 0) THEN 'GAGAL_DEFISIT'
        ELSE 'AMAN'
    END AS status_audit
FROM transaksi_payment_source t_inv
LEFT JOIN transaksi_payment_source t_dp 
    ON t_inv.extern_id = t_dp.extern_id 
   AND t_inv.project_id = t_dp.project_id 
   AND t_dp.label = 'uang muka konsumen' 
   AND t_dp.sisa > 0
LEFT JOIN (
    SELECT 
        extern_id, 
        SUM(kredit - debet) AS saldo_buku_besar_2010050
    FROM _rek_pembantu_subcustomer_cache
    WHERE (rekening = '2010050' OR extern2_id = '2010050060') 
      AND periode = 'forever'
    GROUP BY extern_id
) gl ON t_inv.extern_id = gl.extern_id
WHERE t_inv.target_jenis = '749' 
  AND t_inv.jenis = '7499' 
  AND t_inv.sisa > 1000
ORDER BY t_inv.cabang_id ASC, t_inv.extern_nama ASC;
```

---

## 3. KODE IMPLEMENTASI CODEIGNITER 3 (CI3 MODEL)

Simpan kode model berikut di `application/models/Coms/ComAuditArProjectDp.php`:

```php
<?php
defined('BASEPATH') OR exit('No direct script access allowed');

/**
 * [AGENT_LOG]
 * ROLE      : Software Engineer Agent
 * PURPOSE   : Model audit untuk memeriksa potensi kegagalan posting A/R Receipt akibat ketidakcocokan saldo DP Project vs GL 2010050.
 * COMPLIANCE: ISO 9001 (Mutu Audit) & ISO 27001 (Anti SQL-Injection Query Binding)
 * LOG_EXPIRE: 2026-11-05
 * [/AGENT_LOG]
 */
class ComAuditArProjectDp extends CI_Model {

    public function __construct() {
        parent::__construct();
        $this->load->database();
    }

    /**
     * [AGENT_LOG]
     * ROLE      : Software Engineer Agent
     * PURPOSE   : Mengambil daftar lengkap transaksi A/R Project dan membandingkan saldo DP vs Buku Besar 2010050.
     * COMPLIANCE: PHP 5.6 Compatible, Safe Query Binding
     * LOG_EXPIRE: 2026-11-05
     * [/AGENT_LOG]
     */
    public function get_audit_report($cabang_id = null) {
        $sql = "
            SELECT 
                t_inv.id AS payment_source_id,
                t_inv.cabang_id,
                t_inv.nomer AS nomer_nota,
                t_inv.transaksi_id AS invoice_transaksi_id,
                t_inv.extern_id AS customer_id,
                t_inv.extern_nama AS customer_nama,
                t_inv.project_id,
                t_inv.project_nama,
                t_inv.sisa AS sisa_tagihan_nota,
                COALESCE(t_dp.transaksi_id, 0) AS dp_transaksi_id,
                COALESCE(t_dp.sisa, 0) AS dpp_uang_muka_dibutuhkan,
                COALESCE(t_dp.ppn_sisa, 0) AS ppn_uang_muka,
                COALESCE((t_dp.sisa + t_dp.ppn_sisa), 0) AS total_uang_muka_ui,
                COALESCE(gl.saldo_buku_besar_2010050, 0) AS saldo_riil_gl_2010050,
                (COALESCE(gl.saldo_buku_besar_2010050, 0) - COALESCE(t_dp.sisa, 0)) AS selisih_defisit,
                CASE 
                    WHEN COALESCE(t_dp.sisa, 0) > 0 AND COALESCE(gl.saldo_buku_besar_2010050, 0) < COALESCE(t_dp.sisa, 0) THEN 'GAGAL_DEFISIT'
                    ELSE 'AMAN'
                END AS status_audit
            FROM transaksi_payment_source t_inv
            LEFT JOIN transaksi_payment_source t_dp 
                ON t_inv.extern_id = t_dp.extern_id 
               AND t_inv.project_id = t_dp.project_id 
               AND t_dp.label = 'uang muka konsumen' 
               AND t_dp.sisa > 0
            LEFT JOIN (
                SELECT 
                    extern_id, 
                    SUM(kredit_akhir - debet_akhir) AS saldo_buku_besar_2010050
                FROM _rek_pembantu_subcustomer_cache
                WHERE (rekening = '2010050' OR extern2_id = '2010050060') 
                  AND periode = 'forever'
                GROUP BY extern_id
            ) gl ON t_inv.extern_id = gl.extern_id
            WHERE t_inv.target_jenis = ? 
              AND t_inv.jenis = ? 
              AND t_inv.sisa > ?
        ";

        $binds = array('749', '7499', 1000);

        if ($cabang_id !== null && (int)$cabang_id > 0) {
            $sql .= " AND t_inv.cabang_id = ? ";
            $binds[] = (int)$cabang_id;
        }

        $sql .= " ORDER BY t_inv.cabang_id ASC, t_inv.extern_nama ASC";

        $query = $this->db->query($sql, $binds);
        return $query->result_array();
    }
}
```

---

## 4. KODE IMPLEMENTASI CONTROLLER CI3

Simpan controller berikut di `application/modules/penerimaan/controllers/AuditProject.php`:

```php
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
require_once "Modul_Controller.php";

/**
 * [AGENT_LOG]
 * ROLE      : Software Engineer Agent
 * PURPOSE   : Controller untuk menampilkan laporan audit kesesuaian saldo DP Project vs GL 2010050 di Web UI.
 * COMPLIANCE: ISO 9001 (Audit Quality) & ISO 27001 (RBAC & Authentication Guard)
 * LOG_EXPIRE: 2026-11-05
 * [/AGENT_LOG]
 */
class AuditProject extends Modul_Controller {

    public function __construct() {
        parent::__construct();
        $this->load->model("Coms/ComAuditArProjectDp");
    }

    /**
     * [AGENT_LOG]
     * ROLE      : Software Engineer Agent
     * PURPOSE   : Menampilkan tabel hasil audit 36 project secara interaktif.
     * COMPLIANCE: PHP 5.6 Compatible
     * LOG_EXPIRE: 2026-11-05
     * [/AGENT_LOG]
     */
    public function index() {
        $cabang_filter = isset($_GET['cabang_id']) ? (int)$_GET['cabang_id'] : null;
        $audit_data = $this->ComAuditArProjectDp->get_audit_report($cabang_filter);

        $data = array(
            'title'      => 'Laporan Audit Saldo DP Project vs GL 2010050',
            'audit_data' => $audit_data
        );

        $this->load->view('audit_project_view', $data);
    }
}
```

---

## 5. KODE TEMPLATE VIEW HTML (DATATABLES WEB UI)

Simpan file view berikut di `application/modules/penerimaan/views/audit_project_view.php`:

```html
<!DOCTYPE html>
<html lang="id">
<head>
    <meta charset="UTF-8">
    <title><?php echo isset($title) ? $title : 'Laporan Audit'; ?></title>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@3.4.1/dist/css/bootstrap.min.css">
    <link rel="stylesheet" href="https://cdn.datatables.net/1.10.25/css/dataTables.bootstrap.min.css">
    <style>
        .status-gagal { background-color: #f2dede; color: #a94442; font-weight: bold; }
        .status-potensi { background-color: #fcf8e3; color: #8a6d3b; font-weight: bold; }
        .status-aman { background-color: #dff0d8; color: #3c763d; font-weight: bold; }
        .table-audit th { background-color: #337ab7; color: white; text-align: center; vertical-align: middle; }
    </style>
</head>
<body style="padding: 20px; background-color: #f5f5f5;">

<div class="container-fluid">
    <div class="panel panel-primary">
        <div class="panel-heading">
            <h3 class="panel-title"><i class="glyphicon glyphicon-dashboard"></i> Laporan Audit Kesesuaian Saldo DP Project vs Buku Besar GL 2010050</h3>
        </div>
        <div class="panel-body">
            <div class="table-responsive">
                <table id="tableAudit" class="table table-bordered table-striped table-hover table-audit">
                    <thead>
                        <tr>
                            <th>No</th>
                            <th>Cabang</th>
                            <th>Nama Konsumen (Customer)</th>
                            <th>PID</th>
                            <th>Nomer Nota</th>
                            <th>Nama Project</th>
                            <th>Sisa Tagihan Nota</th>
                            <th>Saldo DP Project (UI)</th>
                            <th>Saldo Riil GL 2010050</th>
                            <th>Status Audit</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php 
                        if (isset($audit_data) && is_array($audit_data) && sizeof($audit_data) > 0) {
                            $no = 1;
                            foreach ($audit_data as $row) {
                                $class_status = 'status-aman';
                                $label_status = '<span class="label label-success">AMAN</span>';

                                if ($row['status_audit'] == 'GAGAL_DEFISIT' || $row['status_audit'] == 'GAGAL_SALDO_NOL') {
                                    $class_status = 'status-gagal';
                                    $label_status = '<span class="label label-danger">PASTI GAGAL</span>';
                                } elseif ($row['saldo_riil_gl_2010050'] == 0 && $row['dpp_uang_muka_dibutuhkan'] > 0) {
                                    $class_status = 'status-potensi';
                                    $label_status = '<span class="label label-warning">BERPOTENSI GAGAL</span>';
                                }
                        ?>
                            <tr class="<?php echo $class_status; ?>">
                                <td class="text-center"><?php echo $no++; ?></td>
                                <td class="text-center"><?php echo htmlspecialchars($row['cabang_id']); ?></td>
                                <td><?php echo htmlspecialchars($row['customer_nama']); ?></td>
                                <td class="text-center"><?php echo htmlspecialchars($row['customer_id']); ?></td>
                                <td><?php echo htmlspecialchars($row['nomer_nota']); ?></td>
                                <td><?php echo htmlspecialchars($row['project_nama']); ?></td>
                                <td class="text-right">Rp <?php echo number_format($row['sisa_tagihan_nota'], 2, ',', '.'); ?></td>
                                <td class="text-right">Rp <?php echo number_format($row['total_uang_muka_ui'], 2, ',', '.'); ?></td>
                                <td class="text-right">Rp <?php echo number_format($row['saldo_riil_gl_2010050'], 2, ',', '.'); ?></td>
                                <td class="text-center"><?php echo $label_status; ?></td>
                            </tr>
                        <?php 
                            }
                        } 
                        ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>

<script src="https://code.jquery.com/jquery-1.12.4.min.js"></script>
<script src="https://cdn.datatables.net/1.10.25/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.25/js/dataTables.bootstrap.min.js"></script>
<script>
    $(document).ready(function() {
        $('#tableAudit').DataTable({
            "pageLength": 50,
            "ordering": true,
            "language": {
                "search": "Cari Project / Customer:",
                "lengthMenu": "Tampilkan _MENU_ baris",
                "info": "Menampilkan _START_ sampai _END_ dari _TOTAL_ project"
            }
        });
    });
</script>
</body>
</html>
```

---
*Dokumen ini dibuat otomatis sebagai panduan implementasi modul penerimaan piutang project.*