BDS Insurer statement lets finance users upload an insurer-provided Excel statement, validate each row against NHance policy transactions (pt_co_share_details + policy_transaction), and persist matched brokerage amounts into co_share_stmt_details. The admin UI is app/Views/insurer_statement_list.php; all server logic lives in PolicyTransactionController under the policy_tranction/statement route group.

i
Auth Statement routes use the authMVC filter. Upload and list are browser AJAX/form calls from an authenticated session, not public API endpoints.

Overview

flowchart LR A[Upload Excel] --> B[validateInsurerStatement] B --> C{All rows OK?} C -->|Yes| D[updateInsurerStatement] C -->|No| E[file_status failed] D --> F[co_share_stmt_details]

Row validation logic

validateInsurerStatement() checks that every Excel line maps to a real NHance transaction for the selected insurer branch. Each row is matched on policy number (column B) and endorsement number (column C).

flowchart TD A["Read Excel"] --> B["Fetch NHance rows for policies in the file"] B --> C["Check each non-empty row"] C --> D{"Policy and endorsement found in NHance?"} D -->|"Yes, first time in file"| E["Row valid"] D -->|"Same combo again"| F["Duplicate"] D -->|"Policy not found"| G["Invalid policy"] D -->|"Policy OK, wrong endorsement"| H["Invalid endorsement"] E --> I{"Any bad rows?"} F --> I G --> I H --> I I -->|No| J["Validation passes"] I -->|Yes| K["Validation fails"]

In short:

Key files and routes

AreaLocation
UI app/Views/insurer_statement_list.php — DataTable list, upload modal, validation error modal, invoice modal
Controller app/Controllers/PolicyTransactionController.php
Statement header model app/Models/InsurerStatements.php → insurer_statements
Line-item model app/Models/COShareStmtDetailsModel.php → co_share_stmt_details
NHance source rows app/Models/PTCOShareDetailsModel.php → getNonReconcileredPolicyTransactionByPolicyAndEndorsement()
Sample Excel public/sample_excel/insurer_stament_sample.xlsx

Routes (prefix policy_tranction/statement, filter authMVC):

MethodRouteHandler
GETliststatementList
POSTuploaduploadInsurerStatement
GETdownloadSampleInsurerStatementSample file download
GETdownloadInsurerStatement/(:num)Uploaded file download
GETgetFileErr/(:any)Validation failure JSON for modal
GETgetInsurerStatementMonthUsed to disable already-used statement numbers
GETdeleteStatement/(:any)Soft-delete statement + related rows
GETgetPaymentDetails/(:any)Invoice modal data
POSTsaveInvoicePaymentDetailsInvoice / payment save
 
$routes->group('policy_tranction', ['filter' => 'authMVC'], function ($routes) {
    $routes->group('statement', ['filter' => 'authMVC'], function ($routes) {
        $routes->get('list', 'PolicyTransactionController::statementList');
        $routes->post('upload', 'PolicyTransactionController::uploadInsurerStatement');
        // ...
    });
});

Statement list UI

statementList() loads insurers/branches via insurerBranchModel::getInsurerBranchesWithInsurerNames(), invoice status labels, and statements from the last 180 days (is_active = 1). The view shows:

Upload form fields (#insurer_statement_upload_form): insurer (insurer_id-branch_id), statement month (flatpickr), statement no (1–7), Excel file. Submit is AJAX POST to relative upload. On success the page reloads; on validation failure the API still returns HTTP 200 with dataStatus: false and error_data.

uploadInsurerStatement()

  1. Validates uploaded file: Excel MIME types, max 16 MB (max_size[statement,16384] KB).
  2. Moves file to WRITEPATH . 'uploads/statements/' (see createStatementFolder() for folder creation).
  3. Parses POST: insurer as {insurer_id}-{branch_id}, statement_month (converted to first-of-month Y-m-d), statement_no → stmt_sno.
  4. Inserts insurer_statements row via InsurerStatements model.
  5. Calls validateInsurerStatement(['file_id' => $file_id]).
  6. If validation passes, calls updateInsurerStatement(['file_id' => $file_id]).
  7. On validation failure: responds with dataStatus: false, error_data, error_code (HTTP 200).
  8. On success: sets invoice_status = 'pending' and returns dataStatus: true.

validateInsurerStatement($params)

Runs immediately after upload (and can be re-run manually in dev with a hard-coded file_id in statementList() comment).

Steps

  1. Load insurer_statements by file_id; fail if missing or physical file absent under writable/uploads/statements/.
  2. Load active sheet via PhpSpreadsheet; drop header row; sanitize with ExcelSanitizeHelper::sanitizeArrayData().
  3. Collect unique policy numbers from column B (index 1), skipping empty rows via check_row_is_empty_or_null().
  4. Fetch NHance candidates: PTCOShareDetailsModel::getNonReconcileredPolicyTransactionByPolicyAndEndorsement(insurer_id, branch_id, policy_no[]) — matches pt.policy_no for completed transactions on that insurer branch.
  5. Build lookup keys policy_no|endorsement_no (sanitized) for source and Excel rows.
  6. For each non-empty Excel row, match policy + endorsement; track duplicates in $matched_entry.
  7. On mismatch, append row-wise HTML messages under error_data[row_index].
  8. Update insurer_statements.line_items, file_status (success / failed), reason (JSON).

sanitizeStatementLookupValue()

Private helper trims Unicode spaces and strips zero-width / BOM characters from policy and endorsement values before comparison — avoids “looks equal” mismatches in Excel.

Validation error codes

error_codeMeaningUI
0DB row missing or physical file not foundPlain message in modal
1Legacy: list of row numbers (old format)Comma-separated row list
2Row-wise validation (current)Modal lists each row with policy / endorsement / duplicate messages

Row keys in error_data are the array index from the Excel loop (first data row is typically 1 after header removal), not necessarily the Excel row number on sheet.

updateInsurerStatement($params)

Runs only when validation returned status: true.

  1. Same file load / sanitize path as validation.
  2. Rebuild policy list and source lookup (policy + endorsement → array of pt_co_share_details rows).
  3. For each Excel row with a matching source entry, compute brokerage totals and variance:
  4. COShareStmtDetailsModel::insertBatch($data_to_update) — one row per matched Excel line.
  5. Sets file_status = success, invoice_status = pending, clears/sets reason.

Excel column mapping (0-based index)

Header row is removed; data columns used by validation/update:

IndexColumnUse
1BPolicy number (required for matching)
2CEndorsement number
3DActual BP amount
4EActual TP amount
5FActual BP brokerage
6GActual TP brokerage
7HReward

Download the canonical layout from the list page link → downloadSampleInsurerStatement.

NHance source query

getNonReconcileredPolicyTransactionByPolicyAndEndorsement() joins:

Matching is on sanitized policy_no + endorsement_no. The method name suggests “non-reconciled” but the current query does not filter statement_id IS NULL; be aware when re-uploading or debugging duplicate reconciliation.

Invoice status and delete

After a successful upload, users manage invoice lifecycle from the list (separate from upload/validate):

getInsurerStatementMonth returns successful statements for insurer+month so the UI can disable statement numbers already used (disableStatementNo() in the view).

Developer steps

  1. Ensure writable/uploads/statements/ exists and is writable (or call createStatementFolder() once).
  2. Open policy_tranction/statement/list in a logged-in session.
  3. Use sample Excel; pick insurer branch and month; choose an unused statement number (1–7 per insurer/month).
  4. Confirm policy/endorsement exist on a completed BDS transaction for that insurer branch.
  5. On failure, open the alert icon → modal calls getFileErr/{id} and renders reason JSON.
  6. To debug validation only: temporarily uncomment the validateInsurerStatement / updateInsurerStatement one-liner in statementList() with a known file_id.

Common pitfalls