# Estimates UAT Case to Endpoint and Query Mapping

## 1. Tujuan

Dokumen ini memetakan setiap UAT case KPI/pivot ke endpoint controller, method service/model, dan sumber query SQL agar audit traceability enterprise terpenuhi.

## 2. Legend

| Layer | Keterangan |
|---|---|
| UI | View / frontend request |
| API | Controller endpoint |
| Service | Business logic service |
| Model | Data access and SQL |

## 3. Endpoint Inventory (Modul Estimates)

| Endpoint | Method Controller | File |
|---|---|---|
| `estimates/list_data` | `list_data()` | `app/Controllers/Estimates.php` |
| `estimates/kpi_core_data` | `kpi_core_data()` | `app/Controllers/Estimates.php` |
| `estimates/pivot_owner_month_status_data` | `pivot_owner_month_status_data()` | `app/Controllers/Estimates.php` |
| `estimates/estimate_list_data_of_client/{id}` | `estimate_list_data_of_client()` | `app/Controllers/Estimates.php` |
| `estimates/save` | `save()` | `app/Controllers/Estimates.php` |
| `estimates/update_estimate_status/{estimate_id}/{status}` | `update_estimate_status()` | `app/Controllers/Estimates.php` |
| `estimates/update_activity_status` | `update_activity_status()` | `app/Controllers/Estimates.php` |
| `estimates/activity_timeline/{estimate_id}` | `activity_timeline()` | `app/Controllers/Estimates.php` |
| `estimates/activity_snapshot/{estimate_id}/{version_no}` | `activity_snapshot()` | `app/Controllers/Estimates.php` |
| `estimates/activity_compare/{estimate_id}/{from}/{to}` | `activity_compare()` | `app/Controllers/Estimates.php` |
| `estimates/delete` | `delete()` | `app/Controllers/Estimates.php` |
| `estimates/prioritas_data_summary_data` | `prioritas_data_summary_data()` | `app/Controllers/Estimates.php` |
| `estimates/prioritas_data_list_data` | `prioritas_data_list_data()` | `app/Controllers/Estimates.php` |

## 4. UAT Mapping Matrix

| UAT Case ID | KPI/Pivot | UI Trigger | API Endpoint | Controller Method | Service/Model Method | SQL/Data Source | Validation Rule |
|---|---|---|---|---|---|---|---|
| UAT-KPI-01 | Estimate Count | Estimates table load | `estimates/list_data` | `list_data()` | `Estimates_model->get_details($options)` | `SELECT ... FROM estimates ... WHERE estimates.deleted=0` | UI count = DB count by same filter |
| UAT-KPI-02 | Estimate Value | Estimates table load | `estimates/list_data` | `list_data()` | `Estimates_model->get_details($options)` | `estimate_value_calculation` join `estimate_items` + tax/discount | UI sum currency = DB sum formula |
| UAT-KPI-03 | Accepted Rate | Status filter/dashboard | `estimates/list_data` | `list_data()` | `Estimates_model->get_details($options)` | `estimates.status` (`accepted`,`declined/rejected`) | Formula denominator fixed |
| UAT-KPI-07 | KPI Core API | KPI card backend | `estimates/kpi_core_data` | `kpi_core_data()` | `Estimates_model->get_details()` + `_get_estimate_priority_summary()` | aggregate count/value/status + lead priority SQL | API payload konsisten dengan list/summary |
| UAT-KPI-04 | Draft Aging 3+ | Quick summary card | `estimates/kpi_core_data` | `kpi_core_data()` | `_get_estimate_priority_summary()` via `data.lead_priority` | SQL `SUM(CASE WHEN draft_age_days >=3 THEN 1 END)` | Boundary day-2/day-3 valid |
| UAT-KPI-05 | Draft Aging 7+ | Quick summary card | `estimates/kpi_core_data` | `kpi_core_data()` | `_get_estimate_priority_summary()` via `data.lead_priority` | SQL `SUM(CASE WHEN draft_age_days >=7 THEN 1 END)` | Boundary day-6/day-7 valid |
| UAT-KPI-06 | Ready SO | Quick summary card | `estimates/kpi_core_data` | `kpi_core_data()` | `_get_estimate_priority_summary()` via `data.lead_priority` | SQL `NOT(missing_any_sql)` | Ready count sesuai rule missing data |
| UAT-PVT-01 | Owner x Month x Status | AppTable + filters | `estimates/list_data` | `list_data()` | `prepare_estimate_filter_options()` + `Estimates_model->get_details()` | owner matrix filter + `estimate_date` range | Parent-child total konsisten |
| UAT-PVT-07 | Owner x Month x Status API | Pivot backend aggregate | `estimates/pivot_owner_month_status_data` | `pivot_owner_month_status_data()` | `Estimates_model->get_details()` + grouped aggregation | grouped by owner/month/status with totals | Sum pivot = total KPI by same filter |
| UAT-PVT-02 | Source/Sub-source | Priority table/filter | `estimates/prioritas_data_list_data` | `prioritas_data_list_data()` | `_build_estimate_priority_query_context()` | source/sub-source join di query context | Child subtotal = parent total |
| UAT-PVT-03 | Aging Board | Priority tab | `estimates/prioritas_data_list_data` | `prioritas_data_list_data()` | `_build_estimate_priority_query_context()` | draft age bucket + missing fields SQL | Queue sesuai filter owner/domain |
| UAT-PVT-04 | Activity Audit | Timeline panel | `estimates/activity_timeline/{id}` | `activity_timeline()` | `EstimateActivityHistoryService->get_timeline()` | `Estimate_activity_history_model->get_timeline_rows()` | Event count sama dgn history rows |
| UAT-PVT-05 | Snapshot Audit | Snapshot action | `estimates/activity_snapshot/{id}/{v}` | `activity_snapshot()` | `EstimateActivityHistoryService->get_snapshot()` | `estimate_activity_history.snapshot_json` by version | Hash/snapshot konsisten |
| UAT-PVT-06 | Compare Versions | Compare action | `estimates/activity_compare/{id}/{from}/{to}` | `activity_compare()` | `EstimateActivityHistoryService->compare_versions()` | compare `snapshot_json` antar versi | Diff paths valid |
| UAT-SEC-01 | Staff access matrix | Table/view/detail | `estimates/list_data` etc | `validate_estimate_access()` | `can_access_this_estimate()` + `prepare_estimate_filter_options()` | Security_Controller scope matrix | Staff hanya data sesuai scope |
| UAT-SEC-02 | Client access | Client portal list | `estimate_list_data_of_client()` | `estimate_list_data_of_client()` | `exclude_draft=true` for client | `status!='draft'` enforced | Client tidak melihat draft |
| UAT-SEC-03 | Snapshot permission | Snapshot API call | `estimates/activity_snapshot/...` | `activity_snapshot()` | `_current_user_can_view_estimate_activity_snapshot()` | role permission key `can_view_estimate_activity_snapshot` | Unauthorized returns 403 |
| UAT-NF-01 | Export consistency | Excel/Print export | AppTable export layer | View `estimates/index.php` | AppTable `printColumns/xlsColumns` | same source `estimates/list_data` | Export angka sama UI |
| UAT-NF-02 | Soft delete exclusion | Delete + reload | `estimates/delete` + `list_data` | `delete()` + `list_data()` | `Estimates_model->delete()` + `get_details()` | `WHERE estimates.deleted=0` | Deleted rows excluded |

## 5. Access and Scope Traceability

| Access Rule | Controller/Helper | Technical Note |
|---|---|---|
| Estimate scope by role | `prepare_estimate_filter_options()` in `Security_Controller` | injects owner_id, owner_id_list, permission_estimate, matrix flags |
| Matrix enforcement | `enforce_estimate_access_matrix` options | limits to creator/lead owner/team manager scope |
| Client scope | `estimate_list_data_of_client()` | forces `exclude_draft` and client contact access check |
| Snapshot access | `_current_user_can_view_estimate_activity_snapshot()` | permission gate before snapshot/compare |

## 6. Reconciliation Query Reference

### 6.1 Baseline Count and Value
```sql
SELECT COUNT(*) AS total_rows,
       SUM(estimate_value) AS total_value
FROM (
  -- mirror logic from Estimates_model->get_details() with same filters
) t;
```

### 6.2 Status Distribution
```sql
SELECT status, COUNT(*) AS total
FROM estimates
WHERE deleted=0
GROUP BY status;
```

### 6.3 Activity Timeline Count
```sql
SELECT COUNT(*) AS total_activity
FROM estimate_activity_history
WHERE estimate_id = :estimate_id;
```

## 7. Expected Artifacts per UAT Run

1. Endpoint response capture (JSON sample) per case.
2. DB query output screenshot/file per case.
3. Comparison sheet `UI vs API vs DB`.
4. Defect ticket link jika mismatch.

## 8. Approval

| Role | Name | Approved (Y/N) | Date | Notes |
|---|---|---|---|---|
| BI Lead | | | | |
| QA Lead | | | | |
| Product Owner | | | | |
| Tech Lead | | | | |