310 lines
15 KiB
PHP
310 lines
15 KiB
PHP
<?php
|
||
/**
|
||
* BDS Insurer statement — content only
|
||
* app/Views/docs/bds-insurer-statement.php
|
||
*
|
||
* Based on:
|
||
* - app/Views/insurer_statement_list.php
|
||
* - app/Controllers/PolicyTransactionController.php
|
||
* (statementList, uploadInsurerStatement, validateInsurerStatement, updateInsurerStatement)
|
||
*/
|
||
?>
|
||
|
||
<p>
|
||
<strong>BDS Insurer statement</strong> lets finance users upload an insurer-provided Excel statement,
|
||
validate each row against NHance policy transactions (<code>pt_co_share_details</code> +
|
||
<code>policy_transaction</code>), and persist matched brokerage amounts into
|
||
<code>co_share_stmt_details</code>. The admin UI is
|
||
<code>app/Views/insurer_statement_list.php</code>; all server logic lives in
|
||
<code>PolicyTransactionController</code> under the <code>policy_tranction/statement</code> route group.
|
||
</p>
|
||
|
||
<div class="callout info">
|
||
<span>i</span>
|
||
<div>
|
||
<strong>Auth</strong>
|
||
Statement routes use the <code>authMVC</code> filter. Upload and list are browser AJAX/form calls from an authenticated session, not public API endpoints.
|
||
</div>
|
||
</div>
|
||
|
||
<h2 id="overview">Overview</h2>
|
||
|
||
<div class="mermaid-wrapper">
|
||
<div class="mermaid">
|
||
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]
|
||
</div>
|
||
</div>
|
||
|
||
<h2 id="row-validation-flowchart">Row validation logic</h2>
|
||
|
||
<p>
|
||
<code>validateInsurerStatement()</code> checks that every Excel line maps to a real NHance transaction
|
||
for the selected insurer branch. Each row is matched on <strong>policy number</strong> (column B) and
|
||
<strong>endorsement number</strong> (column C).
|
||
</p>
|
||
|
||
<div class="mermaid-wrapper">
|
||
<div class="mermaid">
|
||
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"]
|
||
</div>
|
||
</div>
|
||
|
||
<p><strong>In short:</strong></p>
|
||
<ul>
|
||
<li>Empty rows are ignored.</li>
|
||
<li>A row is <strong>valid</strong> only if that policy + endorsement exists in NHance and appears once in the upload.</li>
|
||
<li>If anything fails, the whole file is marked <code>failed</code> and errors are shown per row in the UI.</li>
|
||
</ul>
|
||
|
||
<h2 id="key-files-routes">Key files and routes</h2>
|
||
|
||
<table>
|
||
<thead>
|
||
<tr><th>Area</th><th>Location</th></tr>
|
||
</thead>
|
||
<tbody>
|
||
<tr>
|
||
<td>UI</td>
|
||
<td><code>app/Views/insurer_statement_list.php</code> — DataTable list, upload modal, validation error modal, invoice modal</td>
|
||
</tr>
|
||
<tr>
|
||
<td>Controller</td>
|
||
<td><code>app/Controllers/PolicyTransactionController.php</code></td>
|
||
</tr>
|
||
<tr>
|
||
<td>Statement header model</td>
|
||
<td><code>app/Models/InsurerStatements.php</code> → <code>insurer_statements</code></td>
|
||
</tr>
|
||
<tr>
|
||
<td>Line-item model</td>
|
||
<td><code>app/Models/COShareStmtDetailsModel.php</code> → <code>co_share_stmt_details</code></td>
|
||
</tr>
|
||
<tr>
|
||
<td>NHance source rows</td>
|
||
<td><code>app/Models/PTCOShareDetailsModel.php</code> → <code>getNonReconcileredPolicyTransactionByPolicyAndEndorsement()</code></td>
|
||
</tr>
|
||
<tr>
|
||
<td>Sample Excel</td>
|
||
<td><code>public/sample_excel/insurer_stament_sample.xlsx</code></td>
|
||
</tr>
|
||
</tbody>
|
||
</table>
|
||
|
||
<p>Routes (prefix <code>policy_tranction/statement</code>, filter <code>authMVC</code>):</p>
|
||
|
||
<table>
|
||
<thead>
|
||
<tr><th>Method</th><th>Route</th><th>Handler</th></tr>
|
||
</thead>
|
||
<tbody>
|
||
<tr><td>GET</td><td><code>list</code></td><td><code>statementList</code></td></tr>
|
||
<tr><td>POST</td><td><code>upload</code></td><td><code>uploadInsurerStatement</code></td></tr>
|
||
<tr><td>GET</td><td><code>downloadSampleInsurerStatement</code></td><td>Sample file download</td></tr>
|
||
<tr><td>GET</td><td><code>downloadInsurerStatement/(:num)</code></td><td>Uploaded file download</td></tr>
|
||
<tr><td>GET</td><td><code>getFileErr/(:any)</code></td><td>Validation failure JSON for modal</td></tr>
|
||
<tr><td>GET</td><td><code>getInsurerStatementMonth</code></td><td>Used to disable already-used statement numbers</td></tr>
|
||
<tr><td>GET</td><td><code>deleteStatement/(:any)</code></td><td>Soft-delete statement + related rows</td></tr>
|
||
<tr><td>GET</td><td><code>getPaymentDetails/(:any)</code></td><td>Invoice modal data</td></tr>
|
||
<tr><td>POST</td><td><code>saveInvoicePaymentDetails</code></td><td>Invoice / payment save</td></tr>
|
||
</tbody>
|
||
</table>
|
||
|
||
<pre><code class="language-php">$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');
|
||
// ...
|
||
});
|
||
});</code></pre>
|
||
|
||
<h2 id="statement-list-ui">Statement list UI</h2>
|
||
|
||
<p>
|
||
<code>statementList()</code> loads insurers/branches via
|
||
<code>insurerBranchModel::getInsurerBranchesWithInsurerNames()</code>, invoice status labels, and
|
||
statements from the last <strong>180 days</strong> (<code>is_active = 1</code>). The view shows:
|
||
</p>
|
||
|
||
<ul>
|
||
<li>Insurer branch, statement month, statement serial no, filename (download link), line items count</li>
|
||
<li><code>file_status</code> — <code>success</code> or <code>failed</code> (failed rows show an alert icon → error modal)</li>
|
||
<li><code>invoice_status</code> — pending / generated / sent / payment received</li>
|
||
<li>Actions (non-failed only): invoice status update, delete</li>
|
||
</ul>
|
||
|
||
<p>
|
||
Upload form fields (<code>#insurer_statement_upload_form</code>): insurer
|
||
(<code>insurer_id-branch_id</code>), statement month (flatpickr), statement no (1–7), Excel file.
|
||
Submit is AJAX POST to relative <code>upload</code>. On success the page reloads; on validation failure
|
||
the API still returns HTTP 200 with <code>dataStatus: false</code> and <code>error_data</code>.
|
||
</p>
|
||
|
||
<h2 id="upload-flow">uploadInsurerStatement()</h2>
|
||
|
||
<ol>
|
||
<li>Validates uploaded file: Excel MIME types, max 16 MB (<code>max_size[statement,16384]</code> KB).</li>
|
||
<li>Moves file to <code>WRITEPATH . 'uploads/statements/'</code> (see <code>createStatementFolder()</code> for folder creation).</li>
|
||
<li>Parses POST: <code>insurer</code> as <code>{insurer_id}-{branch_id}</code>, <code>statement_month</code> (converted to first-of-month <code>Y-m-d</code>), <code>statement_no</code> → <code>stmt_sno</code>.</li>
|
||
<li>Inserts <code>insurer_statements</code> row via <code>InsurerStatements</code> model.</li>
|
||
<li>Calls <code>validateInsurerStatement(['file_id' => $file_id])</code>.</li>
|
||
<li>If validation passes, calls <code>updateInsurerStatement(['file_id' => $file_id])</code>.</li>
|
||
<li>On validation failure: responds with <code>dataStatus: false</code>, <code>error_data</code>, <code>error_code</code> (HTTP 200).</li>
|
||
<li>On success: sets <code>invoice_status = 'pending'</code> and returns <code>dataStatus: true</code>.</li>
|
||
</ol>
|
||
|
||
<h2 id="validate-flow">validateInsurerStatement($params)</h2>
|
||
|
||
<p>Runs immediately after upload (and can be re-run manually in dev with a hard-coded <code>file_id</code> in <code>statementList()</code> comment).</p>
|
||
|
||
<h3 id="validate-steps">Steps</h3>
|
||
|
||
<ol>
|
||
<li>Load <code>insurer_statements</code> by <code>file_id</code>; fail if missing or physical file absent under <code>writable/uploads/statements/</code>.</li>
|
||
<li>Load active sheet via PhpSpreadsheet; drop header row; sanitize with <code>ExcelSanitizeHelper::sanitizeArrayData()</code>.</li>
|
||
<li>Collect unique policy numbers from column <strong>B</strong> (index <code>1</code>), skipping empty rows via <code>check_row_is_empty_or_null()</code>.</li>
|
||
<li>Fetch NHance candidates:
|
||
<code>PTCOShareDetailsModel::getNonReconcileredPolicyTransactionByPolicyAndEndorsement(insurer_id, branch_id, policy_no[])</code>
|
||
— matches <code>pt.policy_no</code> for completed transactions on that insurer branch.</li>
|
||
<li>Build lookup keys <code>policy_no|endorsement_no</code> (sanitized) for source and Excel rows.</li>
|
||
<li>For each non-empty Excel row, match policy + endorsement; track duplicates in <code>$matched_entry</code>.</li>
|
||
<li>On mismatch, append row-wise HTML messages under <code>error_data[row_index]</code>.</li>
|
||
<li>Update <code>insurer_statements.line_items</code>, <code>file_status</code> (<code>success</code> / <code>failed</code>), <code>reason</code> (JSON).</li>
|
||
</ol>
|
||
|
||
<h3 id="sanitize">sanitizeStatementLookupValue()</h3>
|
||
|
||
<p>Private helper trims Unicode spaces and strips zero-width / BOM characters from policy and endorsement values before comparison — avoids “looks equal” mismatches in Excel.</p>
|
||
|
||
<h3 id="validation-errors">Validation error codes</h3>
|
||
|
||
<table>
|
||
<thead>
|
||
<tr><th>error_code</th><th>Meaning</th><th>UI</th></tr>
|
||
</thead>
|
||
<tbody>
|
||
<tr><td><code>0</code></td><td>DB row missing or physical file not found</td><td>Plain message in modal</td></tr>
|
||
<tr><td><code>1</code></td><td>Legacy: list of row numbers (old format)</td><td>Comma-separated row list</td></tr>
|
||
<tr><td><code>2</code></td><td>Row-wise validation (current)</td><td>Modal lists each row with policy / endorsement / duplicate messages</td></tr>
|
||
</tbody>
|
||
</table>
|
||
|
||
<p>Row keys in <code>error_data</code> are the <strong>array index</strong> from the Excel loop (first data row is typically <code>1</code> after header removal), not necessarily the Excel row number on sheet.</p>
|
||
|
||
<h2 id="update-flow">updateInsurerStatement($params)</h2>
|
||
|
||
<p>Runs only when validation returned <code>status: true</code>.</p>
|
||
|
||
<ol>
|
||
<li>Same file load / sanitize path as validation.</li>
|
||
<li>Rebuild policy list and source lookup (policy + endorsement → array of <code>pt_co_share_details</code> rows).</li>
|
||
<li>For each Excel row with a matching source entry, compute brokerage totals and variance:
|
||
<ul>
|
||
<li>BP: amount col <code>3</code>, brokerage col <code>5</code></li>
|
||
<li>TP: amount col <code>4</code>, brokerage col <code>6</code></li>
|
||
<li>TEP: forced to <code>0</code> in current code</li>
|
||
<li><code>reward</code> from col <code>7</code></li>
|
||
<li><code>variance = exp_amt - total_amt</code> (from source <code>exp_amt</code>)</li>
|
||
</ul>
|
||
</li>
|
||
<li><code>COShareStmtDetailsModel::insertBatch($data_to_update)</code> — one row per matched Excel line.</li>
|
||
<li>Sets <code>file_status = success</code>, <code>invoice_status = pending</code>, clears/sets <code>reason</code>.</li>
|
||
</ol>
|
||
|
||
<h2 id="excel-columns">Excel column mapping (0-based index)</h2>
|
||
|
||
<p>Header row is removed; data columns used by validation/update:</p>
|
||
|
||
<table>
|
||
<thead>
|
||
<tr><th>Index</th><th>Column</th><th>Use</th></tr>
|
||
</thead>
|
||
<tbody>
|
||
<tr><td><code>1</code></td><td>B</td><td>Policy number (required for matching)</td></tr>
|
||
<tr><td><code>2</code></td><td>C</td><td>Endorsement number</td></tr>
|
||
<tr><td><code>3</code></td><td>D</td><td>Actual BP amount</td></tr>
|
||
<tr><td><code>4</code></td><td>E</td><td>Actual TP amount</td></tr>
|
||
<tr><td><code>5</code></td><td>F</td><td>Actual BP brokerage</td></tr>
|
||
<tr><td><code>6</code></td><td>G</td><td>Actual TP brokerage</td></tr>
|
||
<tr><td><code>7</code></td><td>H</td><td>Reward</td></tr>
|
||
</tbody>
|
||
</table>
|
||
|
||
<p>Download the canonical layout from the list page link → <code>downloadSampleInsurerStatement</code>.</p>
|
||
|
||
<h2 id="nhance-source-query">NHance source query</h2>
|
||
|
||
<p><code>getNonReconcileredPolicyTransactionByPolicyAndEndorsement()</code> joins:</p>
|
||
|
||
<ul>
|
||
<li><code>pt_co_share_details</code> (active) → <code>policy_transaction</code> (active, <code>status = completed</code>)</li>
|
||
<li>Filtered by <code>insurer_id</code>, <code>insurer_branch_id</code>, and <code>pt.policy_no IN (...)</code></li>
|
||
</ul>
|
||
|
||
<p>
|
||
Matching is on sanitized <code>policy_no</code> + <code>endorsement_no</code>. The method name suggests
|
||
“non-reconciled” but the current query does <strong>not</strong> filter <code>statement_id IS NULL</code>;
|
||
be aware when re-uploading or debugging duplicate reconciliation.
|
||
</p>
|
||
|
||
<h2 id="invoice-and-delete">Invoice status and delete</h2>
|
||
|
||
<p>After a successful upload, users manage invoice lifecycle from the list (separate from upload/validate):</p>
|
||
|
||
<ul>
|
||
<li><code>invoice_status</code>: <code>pending</code>, <code>generated</code>, <code>sent</code>, <code>payment_received</code></li>
|
||
<li><code>saveInvoicePaymentDetails</code> — JSON POST from invoice modal</li>
|
||
<li><code>deleteStatement($id)</code> — soft-deletes <code>co_share_stmt_details</code>, <code>inv_payment_details</code>, and <code>insurer_statements</code> for that id</li>
|
||
</ul>
|
||
|
||
<p>
|
||
<code>getInsurerStatementMonth</code> returns successful statements for insurer+month so the UI can
|
||
disable statement numbers already used (<code>disableStatementNo()</code> in the view).
|
||
</p>
|
||
|
||
<h2 id="developer-steps">Developer steps</h2>
|
||
|
||
<ol>
|
||
<li>Ensure <code>writable/uploads/statements/</code> exists and is writable (or call <code>createStatementFolder()</code> once).</li>
|
||
<li>Open <code>policy_tranction/statement/list</code> in a logged-in session.</li>
|
||
<li>Use sample Excel; pick insurer branch and month; choose an unused statement number (1–7 per insurer/month).</li>
|
||
<li>Confirm policy/endorsement exist on a <strong>completed</strong> BDS transaction for that insurer branch.</li>
|
||
<li>On failure, open the alert icon → modal calls <code>getFileErr/{id}</code> and renders <code>reason</code> JSON.</li>
|
||
<li>To debug validation only: temporarily uncomment the <code>validateInsurerStatement</code> / <code>updateInsurerStatement</code> one-liner in <code>statementList()</code> with a known <code>file_id</code>.</li>
|
||
</ol>
|
||
|
||
<h2 id="common-pitfalls">Common pitfalls</h2>
|
||
|
||
<ul>
|
||
<li><strong>Hidden Excel characters</strong> — policy/endorsement must pass <code>sanitizeStatementLookupValue()</code>; re-type values if NHance shows a match but upload fails.</li>
|
||
<li><strong>Duplicate policy + endorsement</strong> in the same file → <code>error_code 2</code>, duplicate message on second row.</li>
|
||
<li><strong>Validation failed but file on disk</strong> — <code>insurer_statements</code> row remains; user sees <code>failed</code> status; re-upload needs a new statement or delete the failed row.</li>
|
||
<li><strong>Statement number reuse</strong> — only successful uploads for that insurer/month block numbers in the dropdown via <code>getInsurerStatementMonth</code>.</li>
|
||
<li><strong>Upload response HTTP 200 on error</strong> — front-end checks <code>dataStatus</code>, not status code alone.</li>
|
||
<li><strong>Legacy handlers</strong> — <code>validateInsurerStatementOld</code>, <code>updateInsurerStatementOLD</code> remain in the controller; production path is the non-<code>Old</code> methods documented here.</li>
|
||
</ul>
|
||
|
||
<h2 id="related-bds">Related BDS features</h2>
|
||
|
||
<ul>
|
||
<li>BDS reports: <code>policy_tranction/report/list</code>, variance, finance, outstanding lists</li>
|
||
<li>Daily BDS cron mail: <code>cronDailyBDSReport</code> (separate from statement upload)</li>
|
||
</ul>
|