# FASE 2 — ARSITEKTUR SISTEM, DATABASE, MODUL & ATURAN PERHITUNGAN

Nama sistem: **SIPIUTANG** — Sistem Informasi Piutang & Penagihan
Pemilik: Roto-Rooter Indonesia

---

## 2.1 Arsitektur Aplikasi

### Tumpukan teknologi

| Lapisan | Teknologi | Alasan |
|---------|-----------|--------|
| Bahasa | PHP 8.2+ (diuji pada 8.4) | Tersedia di semua cPanel |
| Pola | Native PHP MVC (tanpa framework) | Tidak butuh Composer/Node untuk berjalan |
| Basis data | MySQL 5.7+ / MariaDB 10.3+ | Standar cPanel |
| Akses DB | PDO + *prepared statements* | Anti SQL injection |
| Frontend | Bootstrap 5.3 + Bootstrap Icons | Responsif desktop/tablet/ponsel |
| Chart | Chart.js 4 | Dashboard interaktif |
| Tabel | DataTables 1.13 (server-side untuk tabel besar) | Cepat pada 100.000+ baris |
| Excel | Reader/Writer XLSX **internal** (ZipArchive + XMLReader) | Nol dependensi; PhpSpreadsheet dipakai otomatis bila ada |
| PDF | Generator PDF **internal** (core font Helvetica) | Nol dependensi; Dompdf dipakai otomatis bila ada |
| Sesi | Sesi PHP + regenerasi ID + idle timeout | Keamanan |

> **Prinsip desain utama: nol dependensi wajib.** Seluruh fungsi — termasuk import Excel,
> export Excel, dan export PDF — bekerja pada shared hosting kosong tanpa menjalankan
> `composer install`. Bila `vendor/autoload.php` ada dan PhpSpreadsheet/Dompdf terpasang,
> sistem otomatis beralih memakainya (pola *adapter*).

### Struktur folder

```
sipiutang/
├── public/                     ← DocumentRoot (public_html/ atau subfolder)
│   ├── index.php               ← front controller tunggal
│   ├── .htaccess               ← URL rewrite + header keamanan
│   └── assets/{css,js,vendor}
├── app/
│   ├── Core/                   ← 15 kelas kerangka
│   │   ├── App.php  Router.php  Request.php  Response.php
│   │   ├── Controller.php  Model.php  View.php  Database.php
│   │   ├── Auth.php  Acl.php  Csrf.php  Session.php
│   │   ├── Validator.php  Flash.php  Settings.php  Audit.php
│   ├── Services/               ← logika bisnis
│   │   ├── ArCalculator.php    ← net, saldo, status, jatuh tempo
│   │   ├── AgingService.php    ← bucket konfigurabel
│   │   ├── AcpService.php      ← ACP Metode 1 & 2
│   │   ├── ImportService.php   ← ETL + 24 aturan validasi
│   │   ├── ExcelReader.php  ExcelWriter.php  PdfWriter.php  CsvWriter.php
│   │   ├── ReportBuilder.php   ← SQL generator whitelist
│   │   ├── DashboardService.php  CollectionService.php
│   │   ├── RevisionService.php   PaymentService.php
│   │   ├── ApprovalService.php   BackupService.php
│   │   └── DataQualityService.php
│   ├── Controllers/            ← 20 controller
│   ├── Models/                 ← 1 model per tabel utama
│   ├── Helpers/format.php      ← Rupiah, tanggal DD-MM-YYYY, angka
│   └── Views/                  ← template PHP + layout
├── config/config.php           ← dibuat oleh wizard instalasi
├── database/
│   ├── 01_schema.sql           ← 45 tabel
│   ├── 02_seed_master.sql      ← master data + role + permission + user demo
│   └── 03_seed_data.sql        ← 3.075 invoice hasil ETL dari Excel
├── install/index.php           ← wizard instalasi 5 langkah
├── storage/{uploads,exports,logs,tmp,backups}
└── docs/                       ← 6 dokumen Bahasa Indonesia
```

### Alur permintaan (request lifecycle)

```
public/index.php
  → memuat config + autoloader
  → Session::start()            (httponly, samesite=Lax, regenerate)
  → Settings::load()            (cache dari application_settings)
  → Router::dispatch(URI)
      → Auth::check()           (redirect /masuk bila belum login)
      → Auth::enforcePasswordChange()  (paksa ganti sandi awal)
      → Acl::can(modul, aksi)   (403 bila tidak berizin)
      → Csrf::verify()          (untuk POST/PUT/DELETE)
      → Controller->action()
          → Service (logika bisnis, transaksi DB)
          → Audit::log()        (untuk aksi tulis)
          → View::render()      (escaping otomatis)
```

### Model keamanan

| Ancaman | Mitigasi |
|---------|----------|
| SQL injection | 100% PDO prepared statement; report builder memakai *whitelist* field & operator, tidak pernah menyusun SQL dari input mentah |
| XSS | Helper `e()` (htmlspecialchars ENT_QUOTES, UTF-8) wajib di semua view; CSP header |
| CSRF | Token per sesi, diverifikasi pada semua POST; token dirotasi saat login |
| Sandi | `password_hash()` bcrypt cost 12; tidak pernah disimpan plaintext |
| Brute force | Maks 5 gagal / 15 menit per (username + IP), akun terkunci 15 menit, dicatat di `login_attempts` |
| Session hijacking | `session_regenerate_id(true)` saat login, fingerprint IP+UA, idle timeout 30 menit (konfigurabel) |
| Upload berbahaya | Whitelist ekstensi & MIME, blokir `php|phtml|phar|exe|sh|js|html`, nama file di-hash, disimpan di luar DocumentRoot, `.htaccess` deny |
| Hak akses | RBAC 6 role × 8 aksi × 24 modul di `role_permissions`; dicek di controller **dan** di query (data scoping) |
| Penghapusan data finansial | *Soft delete* (`deleted_at`) — tidak ada `DELETE` fisik pada tabel transaksi |
| Jejak audit | `audit_logs` mencatat nilai lama & baru (JSON), IP, user-agent |

### Pembatasan data per role (data scoping)

| Role | Cakupan data |
|------|--------------|
| Super Administrator, Finance AR, Management, Auditor | seluruh data |
| Collection | hanya invoice dengan `collection_officer_id = user.collection_officer_id` |
| Sales | hanya invoice dengan `salesperson_id = user.salesperson_id` |

Diterapkan otomatis oleh `Acl::scopeSql()` yang menyuntikkan klausa `WHERE` pada setiap
query daftar invoice, pembayaran, penagihan, dan laporan — sehingga tidak bisa dilewati
dengan memanipulasi parameter URL.

---

## 2.2 Struktur Database (45 tabel)

### Kelompok 1 — Keamanan & pengguna (7)

| Tabel | Isi kunci |
|-------|-----------|
| `users` | username(U), name, email(U), password_hash, role_id, must_change_password, is_active, failed_attempts, locked_until, last_login_at, area_id, business_unit_id, salesperson_id, collection_officer_id, soft delete |
| `roles` | code(U), name, description, is_system |
| `permissions` | module, action, name — UNIQUE(module, action) |
| `role_permissions` | role_id + permission_id (PK gabungan) |
| `user_roles` | user_id + role_id (role tambahan) |
| `login_attempts` | username, ip_address, success, created_at (index untuk rate limit) |
| `audit_logs` | user_id, user_name, module, entity_type, entity_id, action, old_values(JSON), new_values(JSON), ip_address, user_agent, approval_request_id, created_at |

### Kelompok 2 — Master data (17)

`business_units` · `areas` · `customers` · `outlets` · `salespersons` ·
`collection_officers` · `job_categories` · `job_types` · `revenue_categories` ·
`payment_terms` · `deduction_types` · `banks` · `revision_types` ·
`collection_statuses` · `payment_statuses` · `attachment_types` · `value_aliases`

Tabel `value_aliases` adalah kunci fleksibilitas import: memetakan nilai mentah Excel
(`RDN JKT`, `RESINDESIAL`, `INDUSTRIAL EQUIPEMNT`) ke ID master kanonik.

```sql
value_aliases(id, dimension, raw_value, target_id, secondary_target_id,
              canonical_text, is_auto, created_at)
UNIQUE KEY uq_alias (dimension, raw_value)
```

`dimension` ∈ {`area`, `job_category`, `job_type`, `revenue_category`, `salesperson`,
`send_channel`, `customer`, `outlet`}. Untuk `area`, `target_id` = areas.id dan
`secondary_target_id` = business_units.id.

### Kelompok 3 — Transaksi inti (9)

**`invoices`** — tabel utama (48 kolom)

```sql
id, invoice_no VARCHAR(60) UNIQUE, tax_invoice_no VARCHAR(60), so_no VARCHAR(60),
invoice_date DATE NOT NULL, wr_date, print_date, input_date, sent_date, received_date,
tax_invoice_date, due_date DATE NOT NULL, due_date_basis VARCHAR(20),
payment_term_id, payment_term_days SMALLINT, due_date_manual TINYINT,
area_id, business_unit_id, customer_id, outlet_id, salesperson_id,
collection_officer_id, job_category_id, job_type_id, revenue_category_id,
send_channel VARCHAR(30), send_pic VARCHAR(100), notes TEXT,
gross_amount DECIMAL(18,2), dpp_amount DECIMAL(18,2), vat_amount DECIMAL(18,2),
pph23_amount DECIMAL(18,2), admin_fee_amount DECIMAL(18,2),
other_deduction_amount DECIMAL(18,2), total_deduction DECIMAL(18,2),
net_amount DECIMAL(18,2), credit_note_amount DECIMAL(18,2),
paid_amount DECIMAL(18,2), outstanding_amount DECIMAL(18,2),
revenue_amount DECIMAL(18,2), is_revenue_counted TINYINT DEFAULT 1,
first_payment_date DATE, last_payment_date DATE, payment_count SMALLINT,
payment_status_code VARCHAR(30), doc_status ENUM('active','cancelled','replaced',
   'revised','written_off') DEFAULT 'active',
replaced_by_invoice_id, replaces_invoice_id, revision_id,
aging_days INT, collection_days INT, aging_recalc_date DATE,
dq_flags JSON, source ENUM('import','manual','revision'),
import_batch_id, import_row_no, source_sheet VARCHAR(60),
created_by, updated_by, created_at, updated_at, deleted_at
```

Index: `invoice_no`(U) · `tax_invoice_no` · `customer_id` · `outlet_id` · `invoice_date` ·
`due_date` · `payment_status_code` · `doc_status` · `area_id` · `business_unit_id` ·
`salesperson_id` · `collection_officer_id` · `outstanding_amount` ·
gabungan `(doc_status, payment_status_code, due_date)` untuk worklist penagihan ·
gabungan `(invoice_date, area_id)` untuk laporan bulanan.

Tabel transaksi lainnya:

| Tabel | Peran |
|-------|-------|
| `invoice_deductions` | rincian potongan per invoice (PPh23/ADM/LAIN/CN) + `base_amount`, `rate`, `amount` |
| `payments` | header pembayaran: payment_no(U), payment_date, customer_id, bank_id, method, reference_no, payment_type, amount, notes, status(draft/posted/reversed), approval_status, reversal_of_payment_id, created_by, approved_by |
| `payment_allocations` | alokasi 1 pembayaran ke N invoice: payment_id, invoice_id, amount |
| `invoice_revisions` | RF: rf_no(U), rf_date, old_invoice_id/no/date, new_invoice_id/no/date, original_amount, revised_amount, revision_type_id, old_invoice_action, reason, status, requested_by, approved_by |
| `collection_followups` | follow-up penagihan lengkap 13 field |
| `promise_to_pay` | janji bayar: promise_date, promise_amount, status(open/kept/broken/partial), actual_paid |
| `credit_notes` | nota kredit/penyesuaian saldo dengan approval |
| `attachments` | polimorfik: entity_type, entity_id, attachment_type_id, file_name, stored_name, mime, size, checksum |

### Kelompok 4 — Konfigurasi perhitungan (4)

| Tabel | Peran |
|-------|-------|
| `aging_configurations` | name, basis(outstanding/gross/net), reference(due_date/invoice_date/sent_date), include_paid, is_default |
| `aging_buckets` | aging_configuration_id, label, from_days, to_days (NULL = tak terbatas), color, sort_order |
| `acp_targets` | scope(global/area/business_unit/customer/salesperson/job_category), scope_id, year, month, target_days, tolerance_days |
| `application_settings` | setting_group, setting_key(U), setting_value, value_type, label, description, options_json |

### Kelompok 5 — Report builder (3)

| Tabel | Peran |
|-------|-------|
| `report_templates` | name, description, data_source, chart_type, group_by_json, pivot_json, options_json, owner_id, visibility(private/shared/public), is_system |
| `report_template_columns` | field_key, label, aggregate(none/sum/count/avg/min/max/pct/wavg/dcount), sort_order, sort_dir, width, format |
| `report_template_filters` | field_key, operator(13 jenis), value1, value2, conjunction(AND/OR), sort_order |

### Kelompok 6 — Import & approval (5)

| Tabel | Peran |
|-------|-------|
| `import_batches` | file_name, stored_name, sheet_name, import_type, mapping_json, options_json, total/success/failed/duplicate/warning_rows, status, error_summary, rolled_back_at, uploaded_by |
| `import_rows` | import_batch_id, row_number, raw_json, normalized_json, status, errors_json, invoice_id |
| `approval_flows` | module, action, level_count, approver_roles_json, is_active |
| `approval_requests` | module, entity_type, entity_id, action, payload_json, requested_by, status, current_level, total_level |
| `approval_histories` | approval_request_id, level, actor_id, decision, notes |

### Kelompok 7 — Pendukung (2)

`saved_filters` (filter tersimpan per user per modul) · `ar_period_snapshots`
(cache Saldo AR Awal/Akhir per periode × scope untuk ACP Metode 1)

### Diagram relasi inti

```
                    ┌──────────┐         ┌────────────┐
                    │ customers│────┬───▶│  outlets   │
                    └──────────┘    │    └────────────┘
                          ▲         │           ▲
                          │         │           │
┌────────┐  ┌───────────┐ │  ┌──────┴───────────┴──┐  ┌──────────────────┐
│ areas  │─▶│business_  │ │  │      invoices        │─▶│invoice_deductions│
└────────┘  │  units    │─┼─▶│  (48 kolom)          │  └──────────────────┘
            └───────────┘ │  └──┬───┬────┬────┬─────┘
┌──────────────┐          │     │   │    │    │
│ salespersons │──────────┘     │   │    │    └────────────┐
└──────────────┘                │   │    │                 │
┌────────────────────┐          │   │    │                 ▼
│collection_officers │──────────┘   │    │      ┌─────────────────────┐
└────────────────────┘              │    │      │ invoice_revisions   │
                                    │    │      │ old_id ◀──┐         │
        ┌───────────────────┐       │    │      │ new_id ◀──┘         │
        │payment_allocations│◀──────┘    │      └─────────────────────┘
        └─────────┬─────────┘            │
                  ▼                      ▼
            ┌──────────┐    ┌───────────────────────┐    ┌────────────────┐
            │ payments │    │ collection_followups  │───▶│ promise_to_pay │
            └──────────┘    └───────────────────────┘    └────────────────┘
```

---

## 2.3 Daftar Modul (24 modul, 9 di menu utama)

| # | Menu Indonesia | Modul teknis | Isi |
|---|----------------|--------------|-----|
| 1 | **Dashboard** | `dashboard` | 16 KPI + 13 chart + 13 filter + drill-down |
| 2 | **Piutang** | `piutang` | Daftar AR, aging, worklist, kartu piutang customer |
| 3 | **Invoice** | `invoice` | CRUD invoice + potongan + lampiran + riwayat |
| 4 | **Pembayaran** | `pembayaran` | Pembayaran multi-alokasi, DP, retensi, refund, reversal |
| 5 | **Penagihan** | `penagihan` | Follow-up, janji bayar, worklist 7 kategori |
| 6 | **Revisi Invoice** | `revisi` | RF: pengganti / batal / hapus buku + approval |
| 7 | **Import Data** | `import` | Wizard 10 langkah + riwayat + rollback |
| 8 | **Laporan** | `laporan` | 24 laporan siap pakai (AR aging, ACP, dll) |
| 9 | **Pembuat Laporan** | `report_builder` | Builder fleksibel + template + pivot + chart |
| 10 | **Data Master** | `master` | 17 sub-modul master data |
| 11 | **Persetujuan** | `persetujuan` | 9 jenis approval, inbox, riwayat |
| 12 | **Pengguna** | `pengguna` | User, role, permission matrix |
| 13 | **Log Aktivitas** | `audit` | Audit log dengan filter & diff nilai |
| 14 | **Pengaturan** | `pengaturan` | Settings, aging config, target ACP |
| 15 | **Pencadangan Data** | `backup` | Backup SQL, unduh, restore, jadwal |
| 16–24 | *(sub-modul)* | `aging`, `acp`, `kualitas_data`, `lampiran`, `nota_kredit`, `hapus_buku`, `kartu_piutang`, `forecast`, `profil` | |

---

## 2.4 Aturan Perhitungan (semua dijalankan di server, semua konfigurabel)

### R-01 · Dasar Pengenaan Pajak & PPN

```
DPP        = gross_amount / (1 + tarif_ppn/100)        default tarif_ppn = 11
vat_amount = gross_amount - DPP
```
Bila setting `pajak.nominal_termasuk_ppn = false`, maka `DPP = gross_amount` dan
`vat_amount = DPP × tarif_ppn/100`.

### R-02 · Potongan

```
PPh 23  = round(DPP × tarif_pph23/100, 2)             default tarif_pph23 = 2
Pot Adm = nilai manual  atau  DPP × tarif_adm/100
Pot Lain= nilai manual  atau  DPP × tarif_lain/100
total_deduction = Σ invoice_deductions.amount
```
Tiap `deduction_types` punya `calc_type` ∈ {`percent_of_dpp`, `percent_of_gross`,
`fixed`, `manual`} sehingga aturan bisa diubah tanpa mengubah kode.

### R-03 · Nilai bersih invoice

```
net_amount = gross_amount - total_deduction
```
(sesuai rumus Excel `W = J - T - U - V`)

### R-04 · Saldo piutang

```
paid_amount        = Σ payment_allocations.amount
                       WHERE payments.status = 'posted'
                         AND payments.approval_status = 'approved'
credit_note_amount = Σ credit_notes.amount WHERE status = 'approved'
outstanding_amount = net_amount - paid_amount - credit_note_amount
```
Selalu dihitung ulang di dalam **transaksi DB** setiap kali pembayaran/CN/potongan
ditambah, diubah, dibalik, atau dihapus.

### R-05 · Jatuh tempo

```
base_date = pilih menurut setting ar.dasar_jatuh_tempo:
              sent_date          → tanggal kirim   (DEFAULT)
              invoice_date       → tanggal invoice
              received_date      → tanggal terima
              tax_invoice_date   → tanggal faktur pajak
              manual             → due_date_manual
bila base_date NULL → fallback berurutan: sent_date → invoice_date → wr_date
term_days = prioritas: invoice.payment_term_days
                     → outlet.payment_term
                     → customer.payment_term
                     → kontrak/PMSC/By Case
                     → area
                     → business_unit
                     → setting global (30)
due_date = base_date + term_days hari
```
Hierarki termin 7 tingkat memenuhi permintaan "payment terms by customer / outlet /
invoice / contract / PMSC / By Case / area / business unit".

### R-06 · Status pembayaran (urutan evaluasi ketat)

```
1. doc_status = 'cancelled'    → "Dibatalkan"
2. doc_status = 'replaced'
   atau 'revised'              → "Direvisi"
3. doc_status = 'written_off'  → "Dihapuskan"
4. outstanding <= toleransi    → "Lunas"           (toleransi default Rp 1)
5. outstanding < 0             → "Lunas" + flag KELEBIHAN BAYAR
6. paid_amount > 0             → "Pembayaran Sebagian"
7. due_date <  tanggal_lapor   → "Terlambat"
8. due_date =  tanggal_lapor   → "Jatuh Tempo"
9. due_date >  tanggal_lapor   → "Belum Jatuh Tempo"
```
**Perbaikan atas Excel:** aturan 6 dievaluasi **sebelum** aturan 7–9 dan **setelah**
aturan 4, sehingga kasus DQ-03 (bayar 50%, ada tanggal bayar) benar terklasifikasi
`Pembayaran Sebagian`, bukan `Lunas`.

### R-07 · Umur piutang & hari penagihan (dua metrik terpisah)

```
Invoice BELUM lunas:
  aging_days = tanggal_lapor - due_date        (positif = terlambat)
                                               (negatif = belum jatuh tempo)
Invoice SUDAH lunas:
  collection_days       = last_payment_date - base_date   ← lama penagihan riil
  days_late             = last_payment_date - due_date     ← keterlambatan
```
`tanggal_lapor` **selalu parameter**, default hari ini (Asia/Jakarta), dapat dipilih
pengguna di setiap laporan & dashboard. Tidak pernah memakai `TODAY()` yang terpaku.

### R-08 · Bucket aging

```
Untuk setiap invoice yang memenuhi filter:
  nilai_aging = menurut aging_configurations.basis:
                   outstanding → outstanding_amount   (DEFAULT)
                   net         → net_amount
                   gross       → gross_amount
  hari        = menurut aging_configurations.reference:
                   due_date     → tanggal_lapor - due_date      (DEFAULT)
                   invoice_date → tanggal_lapor - invoice_date
                   sent_date    → tanggal_lapor - sent_date
  bucket      = aging_buckets pertama yang memenuhi
                   hari BETWEEN from_days AND COALESCE(to_days, 999999)
Invoice dengan doc_status ∈ (cancelled, replaced, revised, written_off) DIKECUALIKAN.
Invoice lunas dikecualikan kecuali aging_configurations.include_paid = 1.
```

Dua konfigurasi bawaan:

| Konfigurasi | Bucket |
|-------------|--------|
| **Standar Excel** (default) | 0–45 · 46–90 · 91–120 · >120 |
| **Overdue Bertingkat** | Belum Jatuh Tempo (≤ −1) · 1–30 · 31–60 · 61–90 · 91–120 · >120 |

Administrator dapat membuat konfigurasi & bucket sendiri tanpa batas.

### R-09 · ACP Metode 1 — Standar Finansial

```
Saldo AR pada tanggal D (untuk scope tertentu):
  AR(D) = Σ [ net_amount
              - Σ pembayaran s.d. D
              - Σ nota kredit s.d. D ]
          untuk invoice dengan invoice_date <= D
            dan doc_status = 'active'

AR Rata-rata = ( AR(awal_periode - 1 hari) + AR(akhir_periode) ) / 2

Penjualan Kredit Bersih = Σ net_amount invoice
                            WHERE invoice_date BETWEEN awal AND akhir
                              AND doc_status = 'active'
                              AND is_revenue_counted = 1

ACP (hari) = AR Rata-rata / Penjualan Kredit Bersih × jumlah_hari_periode
```
Bila `Penjualan Kredit Bersih = 0` → ACP = `null` dan ditampilkan `–` (tidak dibagi nol).
Periode: Bulanan · Kuartalan · YTD · Tahunan · Rentang bebas.

### R-10 · ACP Metode 2 — Operasional Penagihan

Populasi: invoice **lunas** dengan `last_payment_date` dalam periode terpilih.

```
hari_i         = last_payment_date_i - base_date_i
Rata-rata      = Σ hari_i / n
Tertimbang     = Σ (hari_i × net_i) / Σ net_i
Median         = persentil-50 dari hari_i
Min / Maks     = min(hari_i) / max(hari_i)
% Tepat Waktu  = jumlah(last_payment_date_i <= due_date_i) / n × 100
% Terlambat    = 100 - % Tepat Waktu
Rata-rata keterlambatan = Σ max(0, last_payment_date_i - due_date_i) / n
```
Metode aktif selalu ditampilkan sebagai *badge* di judul laporan:
`Metode: Standar Finansial` atau `Metode: Operasional Penagihan`.

### R-11 · Indikator target ACP

```
selisih = ACP_aktual - target_hari
🟢 Hijau  : selisih <= 0
🟡 Kuning : 0 < selisih <= toleransi_hari      (default toleransi = 5)
🔴 Merah  : selisih > toleransi_hari
```
Target dicari berurutan: scope paling spesifik (customer) → salesperson → job_category
→ area → business_unit → global; per bulan bila ada, jika tidak per tahun.

### R-12 · Anti dobel hitung revenue pada RF

```
Saat revisi RF DISETUJUI:
  BEGIN TRANSACTION
  invoice_lama.doc_status         = old_invoice_action   (replaced/cancelled/revised/written_off)
  invoice_lama.is_revenue_counted = 0
  invoice_lama.replaced_by_invoice_id = invoice_baru.id
  invoice_lama.outstanding_amount = 0          ← keluar dari AR aktif
  invoice_baru.replaces_invoice_id = invoice_lama.id
  invoice_baru.revision_id         = revisi.id
  invoice_baru.is_revenue_counted  = 1
  COMMIT

Revenue diakui = Σ dpp_amount  WHERE is_revenue_counted = 1 AND doc_status = 'active'
AR aktif       = Σ outstanding_amount WHERE doc_status = 'active'
```
Invoice lama **tidak pernah dihapus** — tetap dapat ditelusuri di Laporan Revisi Invoice
dan di riwayat invoice pengganti. Bila satu invoice baru menggantikan **beberapa** invoice
lama (kasus RF/26/0002 + RF/26/0003 → `FA/26/0643`), sistem mendukungnya karena relasi
`old_invoice_id → new_invoice_id` adalah N:1.

### R-13 · Perkiraan penerimaan kas (Collection Forecast)

```
Untuk setiap invoice belum lunas:
  tanggal_perkiraan = COALESCE(
      janji_bayar_terbuka_terdekat.promise_date,     ← bobot 90%
      due_date bila due_date >= tanggal_lapor,       ← bobot 80%
      tanggal_lapor + rata_rata_keterlambatan_customer  ← bobot 50%
  )
  nilai_tertimbang = outstanding_amount × bobot_keyakinan
Dikelompokkan per minggu / bulan.
```

### R-14 · Skor risiko customer

```
skor = 40% × (rasio_overdue)
     + 30% × (normalisasi rata_rata_hari_penagihan / termin)
     + 20% × (rasio_janji_bayar_gagal)
     + 10% × (rasio_pemakaian_limit_kredit)
kategori: 0–29 Rendah · 30–59 Sedang · 60–79 Tinggi · 80–100 Sangat Tinggi
```

---

## 2.5 Aturan Validasi Import (24 aturan)

| Kode | Aturan | Tingkat |
|------|--------|---------|
| V-01 | `NO INVOICE` wajib ada | ❌ Tolak |
| V-02 | `NO INVOICE` belum ada di database | ❌ Tolak / ⚠ Perbarui (pilihan pengguna) |
| V-03 | `NO INVOICE` tidak duplikat di dalam file yang sama | ❌ Tolak |
| V-04 | `CUSTOMER` wajib ada & tidak hanya spasi | ❌ Tolak |
| V-05 | `NOMINAL` wajib numerik > 0 | ❌ Tolak |
| V-06 | `NOMINAL` bukan teks/mata uang berformat (`Rp 1.000`, `1,000.00`) → dinormalisasi | ⚠ Peringatan |
| V-07 | `TGL INVOICE` wajib & valid (serial Excel / `dd-mm-yyyy` / `dd/mm/yy` / ISO) | ❌ Tolak |
| V-08 | `TGL INVOICE` tidak lebih dari 1 tahun ke depan | ⚠ Peringatan |
| V-09 | `NO FAKTUR PAJAK` tidak duplikat (database & file) | ⚠ Peringatan |
| V-10 | `NO FAKTUR PAJAK` panjang 16–17 karakter numerik | ⚠ Peringatan |
| V-11 | `TGL KIRIM` >= `TGL INVOICE` | ⚠ Peringatan (DQ-12) |
| V-12 | `TGL KIRIM` − `TGL INVOICE` <= 90 hari | ⚠ Peringatan (DQ-13) |
| V-13 | `TGL BAYAR` >= `TGL INVOICE` | ⚠ Peringatan |
| V-14 | `PEMBAYARAN` <= `net_amount` hasil hitung ulang | ⚠ Peringatan + catat kelebihan bayar |
| V-15 | `TGL BAYAR` ada tetapi `PEMBAYARAN` kosong | ⚠ Peringatan (DQ-mirip) |
| V-16 | `PEMBAYARAN` ada tetapi `TGL BAYAR` kosong → pakai `TGL INVOICE` | ⚠ Peringatan |
| V-17 | Sel berisi `#REF!` `#VALUE!` `#N/A` `#DIV/0!` `#NAME?` `#NULL!` `#NUM!` | ❌ Tolak sel, ⚠ baris tetap masuk dengan nilai NULL |
| V-18 | Potongan tidak melebihi `NOMINAL` | ❌ Tolak |
| V-19 | Potongan tidak negatif | ❌ Tolak |
| V-20 | Tarif PPh 23 efektif dalam rentang 0–20% | ⚠ Peringatan (DQ-22) |
| V-21 | Baris kosong berisi rumus dilewati tanpa error | ℹ Info |
| V-22 | Nilai `AREA`/`JOBS`/`PMSC`/`STATUS`/`SALES` baru → auto-create master + catat di `value_aliases` | ℹ Info |
| V-23 | RF: `NO INVOICE YANG DI RF` harus dapat di-resolve; bila tidak → status `menunggu_pencocokan` | ⚠ Peringatan (DQ-25) |
| V-24 | RF: `TGL RF` multi-format & angka tahun saja (`2025`) → parser bertingkat | ⚠ Peringatan (DQ-24) |

Setiap baris gagal dapat diunduh sebagai Excel berisi seluruh kolom asli + kolom
`BARIS`, `STATUS`, `PESAN KESALAHAN` sehingga bisa diperbaiki lalu diimpor ulang.

---

## 2.6 Rencana Implementasi

| Fase | Keluaran | Status |
|------|----------|--------|
| 1 | Analisis Excel — `01_ANALISIS_EXCEL.md` | ✅ |
| 2 | Arsitektur, skema, modul, aturan — dokumen ini | ✅ |
| 3.1 | `database/01_schema.sql` (45 tabel) | ✅ |
| 3.2 | `database/02_seed_master.sql` (master + RBAC + 6 user demo + 24 template laporan) | ✅ |
| 3.3 | Core framework 16 kelas | ✅ |
| 3.4 | Service kalkulasi: ArCalculator, AgingService, AcpService | ✅ |
| 3.5 | ExcelReader / ExcelWriter / PdfWriter / CsvWriter internal | ✅ |
| 3.6 | ImportService + wizard 10 langkah | ✅ |
| 3.7 | Modul Invoice, Pembayaran, Revisi RF, Penagihan | ✅ |
| 3.8 | Dashboard 16 KPI + 13 chart | ✅ |
| 3.9 | ReportBuilder + 24 laporan bawaan | ✅ |
| 3.10 | Approval, Audit, Master Data, Pengaturan, Backup | ✅ |
| 4 | ETL Excel → `database/03_seed_data.sql` (3.075 invoice) | ✅ |
| 5 | Pengujian 19 skenario + verifikasi angka | ✅ |
| 6 | Dokumentasi + wizard instalasi + paket ZIP | ✅ |