# Withdrawal / Not-Withdrawal Audit Report (Count Wise)

## Scope

- Module: SPHF Stage Bottlenecks KPI Drill
- Tenant DB used for this extraction: sphf28
- Runtime path verified in controller: source fallback path is used when precomputed withdrawal rows are unavailable.

## Data Source: Withdrawal / Not-Withdrawal Kahan Se Aa Raha Hai

The drill logic resolves withdrawal status from two possible paths:

1. Precomputed path (preferred when available)
- Table: sphf_normalized_kpi
- Columns: w_stage, w_is_withdrawal, w_days, w_alarm
- Controller check:
  - hasColumns(w_stage, w_is_withdrawal, w_days, w_alarm)
  - hasWithdrawalPrecomputeRows() => whereNotNull(w_stage)->exists()

2. Source fallback path (used when precompute missing/not populated)
- Base table: sphf_normalized_kpi n
- Joined table: financials f on f.uuid = n.uuid
- Withdrawal raw source columns:
  - first_withdrawl
  - second_withdrawl
  - third_withdrawl

## Stage Mapping Used for Source Fallback

| Withdrawal Stage | Pair Key (n.p_pair_key) | Financial Column |
|---|---|---|
| w_1st | tranche_1_to_plinth | first_withdrawl |
| w_2nd | tranche_2_to_lintel | second_withdrawl |
| w_3rd | tranche_3_to_roof | third_withdrawl |

Additional pair-key normalization present in controller:

- tranche_1_to_plinth, plinth_to_tranche_2 -> w_1st
- tranche_2_to_lintel, lintel_to_tranche_3 -> w_2nd
- tranche_3_to_roof, roof_to_tranche_4 -> w_3rd

## Record Eligibility (Common Filter)

Only active, non-completed progress rows are counted:

```sql
n.p_valid = true
AND UPPER(TRIM(n.p_stage)) != 'COMPLETED'
```

## Exact Withdrawal / Not-Withdrawal Conditions

For each stage-specific financial column (first_withdrawl / second_withdrawl / third_withdrawl):

- Withdrawal:

```sql
lower(coalesce(f.<withdraw_col>::text,'')) in ('1','true','t','yes','y')
```

- Not Withdrawal:

```sql
lower(coalesce(f.<withdraw_col>::text,'')) in ('0','false','f','no','n','')
```

Note: Null/empty values are treated as Not Withdrawal by design.

## Threshold Policy Implemented (Not-Withdrawal Alarm Split)

Controller helper withdrawalThresholdsByStage() defines:

| Stage | Green | Yellow | Amber | Red |
|---|---:|---:|---:|---:|
| w_1st | <=25 | <=35 | <=45 | >=60 |
| w_2nd | <=15 | <=25 | <=35 | >=50 |
| w_3rd | <=15 | <=25 | <=35 | >=50 |
| w_4th | <=15 | <=25 | <=35 | >=50 |

Effective alarm CASE currently used by withdrawalAlarmCaseSql():

- w_1st:
  - green: days <= 25
  - yellow: 25 < days <= 35
  - orange: 35 < days < 60
  - red: days >= 60

- w_2nd / w_3rd / w_4th:
  - green: days <= 15
  - yellow: 15 < days <= 25
  - orange: 25 < days < 50
  - red: days >= 50

## Stage-Wise Count Output (Current Snapshot)

Extraction query pattern:

- Source: sphf_normalized_kpi n JOIN financials f ON f.uuid = n.uuid
- Filters: active/non-completed + stage pair_key mapping above
- Split: withdrawal_count, not_withdrawal_count
- For not_withdrawal only: green/yellow/orange/red by stage threshold rules

### w_1st (pair_key: tranche_1_to_plinth, col: first_withdrawl)

| Metric | Count |
|---|---:|
| withdrawal_count | 15,761 |
| not_withdrawal_count | 288,859 |
| not_withdrawal_green | 0 |
| not_withdrawal_yellow | 32,091 |
| not_withdrawal_orange | 0 |
| not_withdrawal_red | 256,768 |

Validation: yellow + orange + red = 32,091 + 0 + 256,768 = 288,859 (matches not_withdrawal_count)

### w_2nd (pair_key: tranche_2_to_lintel, col: second_withdrawl)

| Metric | Count |
|---|---:|
| withdrawal_count | 9,823 |
| not_withdrawal_count | 162,681 |
| not_withdrawal_green | 0 |
| not_withdrawal_yellow | 20,323 |
| not_withdrawal_orange | 28,532 |
| not_withdrawal_red | 113,826 |

Validation: yellow + orange + red = 20,323 + 28,532 + 113,826 = 162,681

### w_3rd (pair_key: tranche_3_to_roof, col: third_withdrawl)

| Metric | Count |
|---|---:|
| withdrawal_count | 24,268 |
| not_withdrawal_count | 255,251 |
| not_withdrawal_green | 0 |
| not_withdrawal_yellow | 23,057 |
| not_withdrawal_orange | 27,273 |
| not_withdrawal_red | 204,921 |

Validation: yellow + orange + red = 23,057 + 27,273 + 204,921 = 255,251

## Controller Reference (Where Logic Lives)

- Stage bottlenecks drill entry and withdrawal drill routing:
  - buildKpiDrillWithdrawal(...)
  - buildKpiDrillWithdrawalFromSource(...)

- Threshold and alarm derivation:
  - withdrawalThresholdsByStage()
  - withdrawalAlarmCaseSql(stageExpr, daysExpr)

- Source mapping:
  - withdrawalSourceStageMap()
  - resolveWithdrawalSourceStageKey(...)

## Important Note

The configured threshold array includes amber boundaries (45/35), but the effective CASE currently collapses orange as a continuous range up to red boundary (for example, w_1st orange is >35 and <60). This report reflects the behavior currently running in controller logic.