416 lines
15 KiB
Markdown
416 lines
15 KiB
Markdown
# Claims Collection V2 — HR Dashboard API
|
||
|
||
**Created:** 2026-06-02
|
||
**Updated:** 2026-06-03
|
||
**Controller:** `App\Controllers\ClaimsCollectionV2DashboardController`
|
||
**Model:** `App\Models\ClaimsCollectionV2DashboardModel`
|
||
**Source queries:** `metabase_raw_queries.csv` → collection `Claims Collection V2` (31 original) + 5 KPIs added in-model (232–236)
|
||
**Base app URL (local):** `https://localhost/PHP828APPS/ruc/nhance/index.php`
|
||
**Total KPIs:** 36
|
||
|
||
---
|
||
|
||
## Overview
|
||
|
||
Each KPI is a separate PHP method on the model, registered in `KPI_MAP` (Metabase question id → slug) and `KPI_LABELS` (slug → display label).
|
||
|
||
The API exposes them in three ways:
|
||
|
||
| What | URL fragment | Use case |
|
||
|------|-------------|----------|
|
||
| **Single KPI** | `kpi/{slug or Metabase id}` | FE loads one card at a time |
|
||
| **All KPIs** | `all` | FE loads entire dashboard in one call (36 KPIs) |
|
||
| **Debug / preview** | `debug` / `preview` | Admin checks raw output in browser |
|
||
|
||
All endpoints require `client_policy` or `client_policy_id` as a query param
|
||
(or route segment for `debug/{id}` / `preview/{id}`).
|
||
|
||
---
|
||
|
||
## Route stacks
|
||
|
||
### 1. Admin — `authMVC` (session login required)
|
||
|
||
Prefix: `util/claims-collection-v2`
|
||
|
||
| Method | Path | Handler | Purpose |
|
||
|--------|------|---------|---------|
|
||
| GET | `util/claims-collection-v2/preview` | `::preview` | UI debug grid in browser |
|
||
| GET | `util/claims-collection-v2/preview/{policy_id}` | `::preview` | Same, policy in URL |
|
||
| GET | `util/claims-collection-v2/debug` | `::debug` | Raw JSON dump (all KPIs) |
|
||
| GET | `util/claims-collection-v2/debug/{policy_id}` | `::debug` | Same, policy in URL |
|
||
| GET | `util/claims-collection-v2/all` | `::all` | JSON — all 36 KPIs |
|
||
| GET | `util/claims-collection-v2/kpi/{slug\|id}` | `::kpi` | JSON — single KPI |
|
||
|
||
**Filters applied:** `authMVC`, `AclFilter`, `HttpRequestLog`, `Cors`, `SecurityInputFilter`
|
||
|
||
---
|
||
|
||
### 2. Frontend / HR App — `authJWT` (Bearer token required)
|
||
|
||
Prefix: `employeeRest/claims-collection-v2`
|
||
|
||
| Method | Path | Handler | Purpose |
|
||
|--------|------|---------|---------|
|
||
| GET | `employeeRest/claims-collection-v2/all` | `::all` | JSON — all 36 KPIs |
|
||
| GET | `employeeRest/claims-collection-v2/kpi/{slug\|id}` | `::kpi` | JSON — single KPI |
|
||
| GET | `employeeRest/claims-collection-v2/preview` | `::preview` | UI grid (FE debug) |
|
||
| GET | `employeeRest/claims-collection-v2/preview/{policy_id}` | `::preview` | Same, policy in URL |
|
||
| GET | `employeeRest/claims-collection-v2/debug` | `::debug` | Raw JSON (FE debug) |
|
||
| GET | `employeeRest/claims-collection-v2/debug/{policy_id}` | `::debug` | Same, policy in URL |
|
||
|
||
**Filters applied:** `GlobalPostFileUploadGuard`, `ratelimit`, `appSignature`, `authJWT`
|
||
|
||
---
|
||
|
||
## Parameters
|
||
|
||
| Param | Location | Type | Required | Notes |
|
||
|-------|----------|------|----------|-------|
|
||
| `client_policy` | query string | int | **Yes** (API) | Policy ID |
|
||
| `client_policy_id` | query string | int | **Yes** (API) | Alias for above |
|
||
| `{policy_id}` | URL segment | int | No | Only on `preview/{id}` and `debug/{id}`; falls back to 4687 if omitted |
|
||
| `{slug\|id}` | URL segment | string/int | Yes (kpi only) | KPI method slug OR Metabase question id |
|
||
|
||
> **Default policy ID (4687)** is only the method-signature default for `preview` and `debug`.
|
||
> Change it by passing the query param or URL segment — never hardcoded elsewhere.
|
||
|
||
---
|
||
|
||
## How to call — Admin (browser / Postman, session cookie)
|
||
|
||
Open in browser while logged in to admin:
|
||
|
||
```
|
||
# UI preview page (loads grid, click "Load all KPIs")
|
||
GET /index.php/util/claims-collection-v2/preview?client_policy=4687
|
||
|
||
# Preview with policy in URL
|
||
GET /index.php/util/claims-collection-v2/preview/4687
|
||
|
||
# Raw JSON — all 36 KPIs
|
||
GET /index.php/util/claims-collection-v2/debug?client_policy=4687
|
||
|
||
# Raw JSON — single KPI by slug
|
||
GET /index.php/util/claims-collection-v2/kpi/incurred_ratio?client_policy=4687
|
||
|
||
# Raw JSON — single KPI by Metabase question id
|
||
GET /index.php/util/claims-collection-v2/kpi/207?client_policy=4687
|
||
|
||
# Exposure KPIs (added 2026-06-03)
|
||
GET /index.php/util/claims-collection-v2/kpi/inception_emp_lives?client_policy=4687
|
||
GET /index.php/util/claims-collection-v2/kpi/current_emp_lives?client_policy=4687
|
||
GET /index.php/util/claims-collection-v2/kpi/claim_value_by_month?client_policy=4687
|
||
GET /index.php/util/claims-collection-v2/kpi/claim_amount_by_relationship?client_policy=4687
|
||
GET /index.php/util/claims-collection-v2/kpi/top_10_ailments_by_claim_count?client_policy=4687
|
||
```
|
||
|
||
---
|
||
|
||
## How to call — Frontend / HR App (JWT)
|
||
|
||
```http
|
||
GET /index.php/employeeRest/claims-collection-v2/all?client_policy=4687
|
||
Authorization: Bearer <jwt_token>
|
||
X-App-Signature: <app_signature>
|
||
Content-Type: application/json
|
||
```
|
||
|
||
```http
|
||
GET /index.php/employeeRest/claims-collection-v2/kpi/incurred_ratio?client_policy=4687
|
||
Authorization: Bearer <jwt_token>
|
||
X-App-Signature: <app_signature>
|
||
```
|
||
|
||
```http
|
||
GET /index.php/employeeRest/claims-collection-v2/kpi/inception_emp_lives?client_policy=4687
|
||
Authorization: Bearer <jwt_token>
|
||
X-App-Signature: <app_signature>
|
||
```
|
||
|
||
```http
|
||
GET /index.php/employeeRest/claims-collection-v2/kpi/claim_amount_by_relationship?client_policy=4687
|
||
Authorization: Bearer <jwt_token>
|
||
X-App-Signature: <app_signature>
|
||
```
|
||
|
||
```http
|
||
GET /index.php/employeeRest/claims-collection-v2/kpi/235?client_policy=4687
|
||
Authorization: Bearer <jwt_token>
|
||
X-App-Signature: <app_signature>
|
||
```
|
||
|
||
---
|
||
|
||
## Sample responses
|
||
|
||
### `kpi/{slug}` — single-row KPI
|
||
|
||
```json
|
||
{
|
||
"status": true,
|
||
"policy_id": 4687,
|
||
"kpi_id": 207,
|
||
"kpi": "incurred_ratio",
|
||
"label": "Incurred Ratio",
|
||
"rows": [
|
||
{ "incurred_ratio": "72.34%" }
|
||
]
|
||
}
|
||
```
|
||
|
||
### `kpi/inception_emp_lives` — exposure (single row)
|
||
|
||
```json
|
||
{
|
||
"status": true,
|
||
"policy_id": 4687,
|
||
"kpi_id": 232,
|
||
"kpi": "inception_emp_lives",
|
||
"label": "Inception Employees & Lives",
|
||
"rows": [
|
||
{ "inception_emp": "150", "inception_lives": "420" }
|
||
]
|
||
}
|
||
```
|
||
|
||
### `kpi/current_emp_lives` — exposure (single row)
|
||
|
||
```json
|
||
{
|
||
"status": true,
|
||
"policy_id": 4687,
|
||
"kpi_id": 233,
|
||
"kpi": "current_emp_lives",
|
||
"label": "Current Employees & Lives",
|
||
"rows": [
|
||
{ "current_emp": "145", "current_lives": "410", "avg_family_size": "2.83" }
|
||
]
|
||
}
|
||
```
|
||
|
||
### `kpi/claim_value_by_month` — time series (multi-row)
|
||
|
||
```json
|
||
{
|
||
"status": true,
|
||
"policy_id": 4687,
|
||
"kpi_id": 234,
|
||
"kpi": "claim_value_by_month",
|
||
"label": "Claim Value by Month",
|
||
"rows": [
|
||
{ "claim_month": "Jan 2024", "month_sort": "2024-01", "claim_value": "125000.00" },
|
||
{ "claim_month": "Feb 2024", "month_sort": "2024-02", "claim_value": "98000.50" }
|
||
]
|
||
}
|
||
```
|
||
|
||
> Sort charts by `month_sort` (ISO `YYYY-MM`), not `claim_month` (display label).
|
||
|
||
### `all`
|
||
|
||
```json
|
||
{
|
||
"status": true,
|
||
"policy_id": 4687,
|
||
"data": {
|
||
"policy_exposure_summary": {
|
||
"id": 181,
|
||
"label": "POLICY & EXPOSURE SUMMARY",
|
||
"rows": [
|
||
{
|
||
"policy_start_date": "2024-01-01",
|
||
"policy_end_date": "2024-12-31",
|
||
"insurer_name": "HDFC Ergo",
|
||
"tpa_name": "Medi Assist"
|
||
}
|
||
]
|
||
},
|
||
"incurred_ratio": {
|
||
"id": 207,
|
||
"label": "Incurred Ratio",
|
||
"rows": [{ "incurred_ratio": "72.34%" }]
|
||
},
|
||
"inception_emp_lives": {
|
||
"id": 232,
|
||
"label": "Inception Employees & Lives",
|
||
"rows": [{ "inception_emp": "150", "inception_lives": "420" }]
|
||
},
|
||
"current_emp_lives": {
|
||
"id": 233,
|
||
"label": "Current Employees & Lives",
|
||
"rows": [{ "current_emp": "145", "current_lives": "410", "avg_family_size": "2.83" }]
|
||
},
|
||
"claim_value_by_month": {
|
||
"id": 234,
|
||
"label": "Claim Value by Month",
|
||
"rows": [
|
||
{ "claim_month": "Jan 2024", "month_sort": "2024-01", "claim_value": "125000.00" }
|
||
]
|
||
}
|
||
}
|
||
}
|
||
```
|
||
|
||
### Error — missing policy id
|
||
|
||
```json
|
||
{
|
||
"status": false,
|
||
"message": "client_policy or client_policy_id is required."
|
||
}
|
||
```
|
||
**HTTP 422**
|
||
|
||
### Error — unknown KPI
|
||
|
||
```json
|
||
{
|
||
"status": false,
|
||
"message": "Unknown KPI. Pass Metabase id or method slug.",
|
||
"allowed": {
|
||
"181": "policy_exposure_summary",
|
||
"207": "incurred_ratio",
|
||
"232": "inception_emp_lives",
|
||
"233": "current_emp_lives",
|
||
"234": "claim_value_by_month",
|
||
"235": "claim_amount_by_relationship",
|
||
"236": "top_10_ailments_by_claim_count"
|
||
}
|
||
}
|
||
```
|
||
**HTTP 404** — `allowed` lists the full `KPI_MAP` (36 entries).
|
||
|
||
---
|
||
|
||
## All 36 KPIs
|
||
|
||
| Metabase ID | Method slug | Label | Rows |
|
||
|-------------|-------------|-------|------|
|
||
| 181 | `policy_exposure_summary` | POLICY & EXPOSURE SUMMARY | single |
|
||
| 185 | `premium_as_on_date` | PREMIUM AS ON DATE | single |
|
||
| 186 | `claims_experience_summary` | CLAIMS EXPERIENCE SUMMARY | single |
|
||
| 190 | `claim_amount_by_gender` | Claim Amount by Gender | multi |
|
||
| 191 | `age_band` | Age Band | multi |
|
||
| 194 | `top_5_hospitals_by_incurred_amount` | Top 5 Hospitals by Incurred amount | multi |
|
||
| 197 | `claims_incidence_rate` | Claims Incidence Rate | single |
|
||
| 198 | `policy_start_date` | Policy Start Date | single |
|
||
| 199 | `policy_end_date` | Policy End Date | single |
|
||
| 200 | `insurer` | Insurer | single |
|
||
| 201 | `tpa` | TPA | single |
|
||
| 203 | `earned_premium` | Earned Premium | single |
|
||
| 204 | `total_claims` | Total Claims | single |
|
||
| 206 | `incurred_amount` | Incurred Amount | single |
|
||
| 207 | `incurred_ratio` | Incurred Ratio | single |
|
||
| 208 | `projected_claims` | Projected Claims | single |
|
||
| 209 | `projected_ratio` | Projected Ratio | single |
|
||
| 213 | `total_reimbursement_amount` | Total Reimbursement Amount | single |
|
||
| 214 | `total_reimbursement_amt_pct` | Total Reimbursement Amt % | single |
|
||
| 215 | `cashless_claim_amt` | Cashless Claim Amt | single |
|
||
| 216 | `cashless_claim_amt_pct` | Cashless Claim Amt % | single |
|
||
| 217 | `total_incurred_by_city` | Total Incurred by city | multi |
|
||
| 219 | `claim_amount_by_claim_status` | Claim Amount by Claim Status | multi |
|
||
| 220 | `hospitals_in_detail` | Hospitals in detail | multi |
|
||
| 221 | `hospital_city_wise_si_limit_pregnancy` | Hospital city wise SI Limit - Pregnancy | multi |
|
||
| 224 | `s_pregnancy_normal_delivery_exceeded_amt` | S-PREGNANCY - NORMAL DELIVERY Exceeded Amt | multi |
|
||
| 225 | `s_pregnancy_c_sec_avg_exceeded_amt` | S-Pregnancy C-Sec avg exceeded amt | multi |
|
||
| 228 | `cataract_exceeded_claim_amount` | Cataract exceeded claim amount | multi |
|
||
| 229 | `cataract_avg_exceeded_amount` | Cataract avg exceeded amount | multi |
|
||
| 230 | `hospital_city_wise_si_limit_cataract` | Hospital city wise SI Limit - Cataract | multi |
|
||
| 231 | `total_incurred_by_cliam_status` | Total Incurred by Cliam Status | multi |
|
||
| 232 | `inception_emp_lives` | Inception Employees & Lives | single |
|
||
| 233 | `current_emp_lives` | Current Employees & Lives | single |
|
||
| 234 | `claim_value_by_month` | Claim Value by Month | multi |
|
||
| 235 | `claim_amount_by_relationship` | Claim Amount by Relationship | multi |
|
||
| 236 | `top_10_ailments_by_claim_count` | Top 10 Ailments by Claim Count | multi |
|
||
|
||
> **Note:** KPIs 214 and 216 were renamed from the auto-generated slug to avoid collision with 213 and 215.
|
||
|
||
> **Note:** IDs **232–236** were assigned in-app for KPIs added outside the original Metabase CSV export. Confirm real Metabase question IDs and update `KPI_MAP` if they differ.
|
||
|
||
---
|
||
|
||
## Output columns — KPIs 232–236
|
||
|
||
| Slug | Row fields | Description |
|
||
|------|------------|-------------|
|
||
| `inception_emp_lives` | `inception_emp`, `inception_lives` | Distinct Self employees and all lives at inception (`change_event` contains inception) |
|
||
| `current_emp_lives` | `current_emp`, `current_lives`, `avg_family_size` | Active employees/lives; `avg_family_size` = lives ÷ employees (2 dp) |
|
||
| `claim_value_by_month` | `claim_month`, `month_sort`, `claim_value` | Monthly sum of `claim_amount`; ordered by `month_sort` |
|
||
| `claim_amount_by_relationship` | `relationship`, `claim_count`, `count_pct`, `claim_value`, `value_pct` | Claims by `ticket_master.relationship`; `count_pct` / `value_pct` are numeric (not `%` strings) |
|
||
| `top_10_ailments_by_claim_count` | `ailment`, `claim_count`, `count_pct`, `claim_value`, `value_pct` | Top 10 `tpa_ailments` (non-null); `claim_value` formatted via `FORMAT()`; pct fields as `%` strings |
|
||
|
||
---
|
||
|
||
### `kpi/claim_amount_by_relationship` — breakdown (multi-row)
|
||
|
||
```json
|
||
{
|
||
"status": true,
|
||
"policy_id": 4687,
|
||
"kpi_id": 235,
|
||
"kpi": "claim_amount_by_relationship",
|
||
"label": "Claim Amount by Relationship",
|
||
"rows": [
|
||
{
|
||
"relationship": "Self",
|
||
"claim_count": "42",
|
||
"count_pct": "35.00",
|
||
"claim_value": "850000.00",
|
||
"value_pct": "40.50"
|
||
},
|
||
{
|
||
"relationship": "Spouse",
|
||
"claim_count": "28",
|
||
"count_pct": "23.33",
|
||
"claim_value": "520000.00",
|
||
"value_pct": "24.80"
|
||
}
|
||
]
|
||
}
|
||
```
|
||
|
||
> Not the same as `claim_amount_by_gender` (#190), which groups by employee `gender` via join — not `ticket_master.relationship`.
|
||
|
||
---
|
||
|
||
## Files
|
||
|
||
| File | Purpose |
|
||
|------|---------|
|
||
| `app/Models/ClaimsCollectionV2DashboardModel.php` | 36 KPI query methods, `KPI_MAP`, `KPI_LABELS`, `getAllKpis()`, `getKpi()` |
|
||
| `app/Controllers/ClaimsCollectionV2DashboardController.php` | `kpi()`, `all()`, `preview()`, `debug()` |
|
||
| `app/Views/claims_collection_v2_dashboard.php` | Admin/FE debug preview UI (KPI card grid) |
|
||
| `app/Config/Routes.php` | Both route groups (search `claims-collection-v2`) |
|
||
| `tests/smoke_claims_collection_v2.php` | CLI smoke test — run: `php tests/smoke_claims_collection_v2.php 4687` |
|
||
| `metabase_raw_queries.csv` | Source of truth for original Metabase SQL queries |
|
||
| `hr-dashboard.md` | This document |
|
||
|
||
---
|
||
|
||
## FE integration notes
|
||
|
||
- Call `all` once on dashboard mount; render each `data[method].rows` into its card (36 keys under `data`).
|
||
- Call `kpi/{slug}` for lazy/on-demand loading of individual cards.
|
||
- `policy_id` should come from the HR session / selected policy context — never hardcoded.
|
||
- All rows are raw arrays; formatting (currency, %, dates) is already applied inside the SQL where applicable (`FORMAT()`, `CONCAT()`, `DATE_FORMAT()`).
|
||
- `rows` may be empty `[]` if no data exists for that policy — handle gracefully in UI.
|
||
- **Single-row KPIs** (e.g. `incurred_ratio`, `inception_emp_lives`): use `rows[0]`.
|
||
- **Multi-row KPIs** (e.g. `claim_value_by_month`, `age_band`): iterate `rows`; for time series use `month_sort` for sort order.
|
||
- **Exposure block:** `inception_emp_lives` + `current_emp_lives` pair for inception vs current headcount.
|
||
|
||
---
|
||
|
||
## BE notes
|
||
|
||
- To add a new KPI: add an entry to `KPI_MAP` + `KPI_LABELS` in the model, write `public function my_kpi(int $policyId): array`, update this doc and bump the smoke test KPI count.
|
||
- All queries use named binding `:policy_id:` (CodeIgniter style, replaces Metabase `{{policy_id}}`).
|
||
- Literal `\t` / `\n` in CSV SQL is normalized in `runKpiQuery()` — safe to re-generate from CSV.
|
||
- Run `php tests/smoke_claims_collection_v2.php {policy_id}` after any model change (expects `KPI_MAP` count === 36).
|
||
|
||
---
|
||
|
||
## Related dashboards
|
||
|
||
- **Enrollment Collection V1** (10 KPIs): see `enrollment-dashboard.md` — prefix `enrollment-collection-v1`
|