293 lines
14 KiB
PHP
293 lines
14 KiB
PHP
<?php
|
||
/**
|
||
* BDS commission — content only
|
||
* app/Views/docs/bds-commission.php
|
||
*
|
||
* Based on:
|
||
* - app/Views/commission_file_upload.php
|
||
* - app/Views/commission_rules_list.php
|
||
* - app/Controllers/RuleImportController.php
|
||
* - app/Controllers/InsuranceCommissionController.php
|
||
*/
|
||
?>
|
||
|
||
<p>
|
||
<strong>BDS commission</strong> is a two-part flow: admins upload commission <strong>rules</strong>
|
||
(Excel) per insurer, month, and department; partner/BDS systems then call an API to
|
||
<strong>calculate payout</strong> for a policy using those rules.
|
||
</p>
|
||
|
||
<h2 id="overview">Overview</h2>
|
||
|
||
<div class="mermaid-wrapper">
|
||
<div class="mermaid">
|
||
flowchart LR
|
||
A["Upload rules Excel"] --> B["Save JSON on disk"]
|
||
B --> C["Edit rules in UI"]
|
||
D["Policy data POST"] --> E["Load matching JSON"]
|
||
E --> F["Match rule and return payout"]
|
||
</div>
|
||
</div>
|
||
|
||
<p><strong>In short:</strong></p>
|
||
<ul>
|
||
<li><strong>Setup:</strong> <code>RuleImportController</code> + <code>commission_file_upload.php</code> — import and manage rules.</li>
|
||
<li><strong>Runtime:</strong> <code>POST getCommission</code> → <code>InsuranceCommissionController::initiateCommissionCalc</code> — read JSON, pick first matching rule, return payout.</li>
|
||
<li>Rules file path: <code>writable/uploads/commission/rules/{MONYYYY}/{insurer_id}_{department}.json</code> (e.g. <code>SEP2025/5_motor.json</code>).</li>
|
||
</ul>
|
||
|
||
<h2 id="key-files">Key files</h2>
|
||
|
||
<table>
|
||
<thead>
|
||
<tr><th>Part</th><th>File</th></tr>
|
||
</thead>
|
||
<tbody>
|
||
<tr><td>Upload list UI</td><td><code>app/Views/commission_file_upload.php</code></td></tr>
|
||
<tr><td>Rules editor UI</td><td><code>app/Views/commission_rules_list.php</code></td></tr>
|
||
<tr><td>Upload and editor API</td><td><code>app/Controllers/RuleImportController.php</code></td></tr>
|
||
<tr><td>Excel parsing</td><td><code>ruleImportService</code> (via <code>Config\Services::ruleImportService()</code>)</td></tr>
|
||
<tr><td>Payout calculation API</td><td><code>app/Controllers/InsuranceCommissionController.php</code></td></tr>
|
||
<tr><td>Upload metadata DB</td><td><code>commission_files</code> (<code>CommissionFilesModel</code>)</td></tr>
|
||
</tbody>
|
||
</table>
|
||
|
||
<h2 id="routes">Routes</h2>
|
||
|
||
<p><strong>Admin (commission group):</strong></p>
|
||
<table>
|
||
<thead>
|
||
<tr><th>Route</th><th>Handler</th></tr>
|
||
</thead>
|
||
<tbody>
|
||
<tr><td><code>GET/POST commission/list</code></td><td><code>commissionFileUploadList</code></td></tr>
|
||
<tr><td><code>POST commission/upload</code></td><td><code>upload</code></td></tr>
|
||
<tr><td><code>GET commission/sample_file</code></td><td>Sample CSV download</td></tr>
|
||
<tr><td><code>GET commission/downloadErrorFile</code></td><td>Annotated error Excel</td></tr>
|
||
<tr><td><code>GET commission/checkSameEntry</code></td><td>Duplicate insurer + month + department check</td></tr>
|
||
<tr><td><code>GET commission/rules/list/(:id)</code></td><td><code>ruleList</code> — rules editor page</td></tr>
|
||
<tr><td><code>POST commission/rules/save/</code></td><td><code>saveRule</code></td></tr>
|
||
<tr><td><code>POST commission/rules/remove/</code></td><td><code>removeRule</code></td></tr>
|
||
<tr><td><code>GET commission/checkRuleUsage</code></td><td>Whether rule is used on partner policies</td></tr>
|
||
<tr><td><code>GET commission/deleteCommissionData/(:id)</code></td><td>Soft-delete file + mark rules deleted</td></tr>
|
||
</tbody>
|
||
</table>
|
||
|
||
<p><strong>Runtime API:</strong></p>
|
||
<pre><code class="language-text">POST getCommission
|
||
→ InsuranceCommissionController::initiateCommissionCalc
|
||
→ filter: CommissionApiFilter</code></pre>
|
||
|
||
<h2 id="commission-file-format">How to build the commission file</h2>
|
||
|
||
<p>
|
||
Use the template from the upload screen (<strong>Download sample file</strong>) or copy from
|
||
<code>public/sample_excel/sample_commission.csv</code>. Dev copies also live under
|
||
<code>writable/uploads/commission/files/</code> (e.g. <code>sample_commission.csv</code>,
|
||
<code>Sample_commission_file-New.xlsx</code>). After a successful import, see the generated JSON under
|
||
<code>writable/uploads/commission/rules/{MONYYYY}/</code> (example: <code>NOV2025/1_motor.json</code>).
|
||
</p>
|
||
|
||
<div class="callout warning">
|
||
<span>!</span>
|
||
<div>
|
||
<strong>Use the current column layout</strong>
|
||
Some older CSVs in <code>writable/uploads/commission/files/</code> use legacy headers
|
||
(<code>Rule Name</code>, <code>Commission Params(TP:OD:PA)</code>). The importer expects the
|
||
<strong>S.No</strong> layout below (<code>RuleImportService</code>). Wrong headers fail with
|
||
“Missing required columns”.
|
||
</div>
|
||
</div>
|
||
|
||
<h3 id="required-columns">Required columns (row 1 headers)</h3>
|
||
|
||
<p>Header text must match <strong>exactly</strong> (one row per rule, starting row 2). Empty cells are allowed and simply skip that condition.</p>
|
||
|
||
<table>
|
||
<thead>
|
||
<tr><th>Column</th><th>Purpose</th><th>Example</th></tr>
|
||
</thead>
|
||
<tbody>
|
||
<tr><td>S.No</td><td>Serial (not used in logic)</td><td>1</td></tr>
|
||
<tr><td>Premium Type</td><td>Maps to <code>policy_type</code></td><td>OD, TP, COM</td></tr>
|
||
<tr><td>Vehicle Type</td><td><code>vehicle_type</code>; comma = multiple (IN)</td><td>Two Wheeler, Four Wheeler</td></tr>
|
||
<tr><td>Vehicle Sub Type</td><td><code>vehicle_sub_type</code></td><td>Car, PCV</td></tr>
|
||
<tr><td>Make / Model</td><td>Vehicle make and model</td><td>Honda, i10</td></tr>
|
||
<tr><td>CC Min / CC Max</td><td>Cubic capacity range</td><td>1000, 3000</td></tr>
|
||
<tr><td>Fuel Type</td><td>Comma-separated fuels</td><td>Petrol, Diesel</td></tr>
|
||
<tr><td>Vehicle Age Min / Max</td><td>Vehicle age range (years)</td><td>1, 5</td></tr>
|
||
<tr><td>Vehicle Weight Min / Max</td><td>Weight range (kg)</td><td>2000, 3000</td></tr>
|
||
<tr><td>RTO State / RTO Code</td><td><code>geo_rto_state</code>, <code>geo_rto_city</code></td><td>RJ, 41</td></tr>
|
||
<tr><td>Renewal Type</td><td><code>renewal_type</code></td><td>Online, Cash, Card</td></tr>
|
||
<tr><td>Commission Type</td><td><code>percentage</code>, <code>composite</code>, <code>flat</code>, <code>tiered</code></td><td>percentage</td></tr>
|
||
<tr><td>Commission Value</td><td>% or flat amount (required for percentage / flat)</td><td>15 or 500</td></tr>
|
||
<tr><td>Commission Params (TP)</td><td>Composite: % on TP premium</td><td>18</td></tr>
|
||
<tr><td>Commission Params (OD)</td><td>Composite: % on OD premium</td><td>10</td></tr>
|
||
<tr><td>Commission Params (PA)</td><td>Composite: % on PA premium</td><td>0 or empty</td></tr>
|
||
</tbody>
|
||
</table>
|
||
|
||
<h3 id="commission-types">Commission types (what to fill)</h3>
|
||
|
||
<table>
|
||
<thead>
|
||
<tr><th>Type</th><th>Fill in sheet</th><th>Becomes in JSON</th></tr>
|
||
</thead>
|
||
<tbody>
|
||
<tr>
|
||
<td><code>percentage</code></td>
|
||
<td>Commission Value = e.g. <code>10</code></td>
|
||
<td>10% of <code>premium</code></td>
|
||
</tr>
|
||
<tr>
|
||
<td><code>composite</code></td>
|
||
<td>Leave Value empty; set TP / OD / PA param columns (percent each)</td>
|
||
<td>Split % on <code>tp_premium</code>, <code>od_premium</code>, <code>pa_premium</code></td>
|
||
</tr>
|
||
<tr>
|
||
<td><code>flat</code></td>
|
||
<td>Commission Value = fixed rupee amount e.g. <code>500</code></td>
|
||
<td>Fixed payout on <code>premium</code></td>
|
||
</tr>
|
||
</tbody>
|
||
</table>
|
||
|
||
<h3 id="commission-file-samples">Sample rows (from project files)</h3>
|
||
|
||
<p><strong>Template header + rows</strong> (<code>public/sample_excel/sample_commission.csv</code>):</p>
|
||
<pre><code class="language-text">S.No,Premium Type,Vehicle Type,...,Commission Type,Commission Value,Commission Params (TP),Commission Params (OD),Commission Params (PA)
|
||
1,OD,Car,...,percentage,15,,,
|
||
2,TP,Two Wheeler,...,composite,,18,10,
|
||
3,COM,PCV,...,percentage,25,,,
|
||
4,COM,GCV,...,composite,,,10,</code></pre>
|
||
|
||
<p><strong>Example A — composite two-wheeler</strong> (from <code>writable/.../rules/NOV2025/1_motor.json</code>):</p>
|
||
<ul>
|
||
<li>Vehicle Type = <code>Two Wheeler</code>, CC Min/Max = <code>100</code></li>
|
||
<li>Commission Type = <code>composite</code>, TP = <code>10</code>, OD = <code>25</code></li>
|
||
<li>Result: 10% on TP premium + 25% on OD premium when policy matches</li>
|
||
</ul>
|
||
|
||
<p><strong>Example B — percentage four-wheeler</strong>:</p>
|
||
<ul>
|
||
<li>Vehicle Type = <code>Four Wheeler</code>, CC = <code>1000</code>, Vehicle Age Min/Max = <code>5</code></li>
|
||
<li>Commission Type = <code>percentage</code>, Commission Value = <code>10</code></li>
|
||
<li>Result: 10% of total premium</li>
|
||
</ul>
|
||
|
||
<p><strong>Example C — composite TP-only</strong>:</p>
|
||
<ul>
|
||
<li>Premium Type = <code>TP</code>, Vehicle Type = <code>Four Wheeler</code></li>
|
||
<li>Commission Type = <code>composite</code>, Commission Params (TP) = <code>10</code></li>
|
||
<li>Result: 10% on TP premium only</li>
|
||
</ul>
|
||
|
||
<p><strong>Example D — flat amount</strong>:</p>
|
||
<ul>
|
||
<li>Vehicle Type = <code>Two Wheeler,Four Wheeler</code> (comma → matches either)</li>
|
||
<li>Commission Type = <code>flat</code>, Commission Value = <code>500</code></li>
|
||
<li>Result: fixed ₹500 when matched</li>
|
||
</ul>
|
||
|
||
<p><strong>Tips:</strong></p>
|
||
<ul>
|
||
<li>Min must not be greater than Max (CC, age, weight) or the row fails validation.</li>
|
||
<li>On failure, download the <strong>annotated</strong> file — errors are written into the sheet.</li>
|
||
<li>Supported formats: <code>.csv</code>, <code>.xlsx</code>, <code>.xls</code>, <code>.ods</code>.</li>
|
||
<li>Upload UI departments: <code>motor</code>, <code>health</code>; import logic is built for <strong>motor</strong> columns today.</li>
|
||
</ul>
|
||
|
||
<h2 id="upload-flow">Rule upload</h2>
|
||
|
||
<p>From <code>commission_file_upload.php</code> the user picks insurer, commission month, department (motor / health), and an Excel/CSV file.</p>
|
||
|
||
<ol>
|
||
<li><code>checkSameEntry</code> — if a successful upload already exists for the same trio, SweetAlert offers <strong>Overwrite</strong> or <strong>Append</strong> (<code>overwrite=1</code> or <code>0</code> on POST).</li>
|
||
<li><code>upload</code> — stores file under <code>writable/uploads/commission/files/</code>, inserts <code>commission_files</code> row (<code>pending</code>).</li>
|
||
<li><code>ruleImportService->processUpload()</code> — validates Excel rows; on success returns <code>rules</code> array; on failure returns <code>annotated_file</code> for download.</li>
|
||
<li>On success — writes JSON to <code>rules/{MONYYYY}/{insurer_id}_{department}.json</code>; sets <code>file_status=success</code> and <code>rules_count</code>.</li>
|
||
<li>On failure — <code>file_status=failed</code>; user downloads <code>annotated_{filename}</code> via <code>downloadErrorFile</code>.</li>
|
||
</ol>
|
||
|
||
<h2 id="rules-editor">Rules editor</h2>
|
||
|
||
<p>
|
||
For successful uploads, action <strong>View Rules</strong> opens
|
||
<code>commission/rules/list/{file_id}</code> (<code>commission_rules_list.php</code>).
|
||
</p>
|
||
<ul>
|
||
<li>Lists rules from the JSON file for that upload’s insurer, month, and department.</li>
|
||
<li><code>saveRule</code> — create or update a rule (conditions + calculation + name) in the JSON via <code>updateCommissionRules()</code>.</li>
|
||
<li><code>removeRule</code> — soft-delete one rule (<code>is_deleted=true</code>).</li>
|
||
<li><code>checkRuleUsage</code> — warns if <code>partner_policy.commission_applied_rule</code> references the rule.</li>
|
||
<li>Deleting the whole upload marks all rules with that <code>file_id</code> as deleted in JSON, then sets <code>commission_files.is_active=0</code>.</li>
|
||
</ul>
|
||
|
||
<h2 id="rule-json">Rule JSON shape</h2>
|
||
|
||
<p>Each rule is roughly:</p>
|
||
<pre><code class="language-json">{
|
||
"id": "rule_…",
|
||
"name": "Rule name",
|
||
"department": "motor",
|
||
"file_id": 12,
|
||
"is_deleted": false,
|
||
"conditions": [
|
||
{ "field": "vehicle_type", "operator": "==", "value": "car" }
|
||
],
|
||
"calculation": {
|
||
"type": "percentage",
|
||
"value": 10,
|
||
"on": "premium"
|
||
}
|
||
}</code></pre>
|
||
|
||
<p>Calculation types in <code>InsuranceCommissionController</code>: <code>percentage</code>, <code>composite</code>, <code>fixed</code>. Conditions support <code>==</code>, <code>!=</code>, <code>></code>, <code>>=</code>, <code><</code>, <code><=</code>, <code>between</code>, <code>in</code>.</p>
|
||
|
||
<h2 id="calculation-api">Commission calculation API</h2>
|
||
|
||
<p><code>initiateCommissionCalc()</code> expects POST/JSON including at least:</p>
|
||
<ul>
|
||
<li><code>policy_issue_date</code> — used to pick folder <code>{MON}{YEAR}</code> (e.g. SEP2025)</li>
|
||
<li><code>insurer_id</code></li>
|
||
<li><code>department</code> — motor, health, etc.</li>
|
||
<li>Fields referenced in rule conditions and calculation bases (e.g. <code>premium</code>, <code>od_premium</code>)</li>
|
||
</ul>
|
||
|
||
<div class="mermaid-wrapper">
|
||
<div class="mermaid">
|
||
flowchart TD
|
||
A["POST getCommission"] --> B{"Required fields present?"}
|
||
B -->|No| C["Validation error"]
|
||
B -->|Yes| D["Load rules JSON for month, insurer, department"]
|
||
D --> E{"File exists?"}
|
||
E -->|No| F["Rules file not found"]
|
||
E -->|Yes| G["Find first rule where all conditions match"]
|
||
G --> H{"Rule found?"}
|
||
H -->|No| I["No matching rules"]
|
||
H -->|Yes| J["Apply calculation type"]
|
||
J --> K["Return payout and rule"]
|
||
</div>
|
||
</div>
|
||
|
||
<p>Rules with <code>is_deleted: false</code> are loaded; the <strong>first</strong> matching rule wins (no priority field yet).</p>
|
||
|
||
<h2 id="developer-steps">Developer steps</h2>
|
||
|
||
<ol>
|
||
<li>Open <code>/commission/list</code> (logged-in admin).</li>
|
||
<li>Download sample file, fill rules for insurer + month + department, upload.</li>
|
||
<li>If validation fails, download the annotated error file and fix the sheet.</li>
|
||
<li>Use <strong>View Rules</strong> to tweak conditions or calculation without re-uploading the whole file.</li>
|
||
<li>Test payout: <code>POST getCommission</code> with the same insurer, department, and a <code>policy_issue_date</code> in that commission month.</li>
|
||
</ol>
|
||
|
||
<h2 id="pitfalls">Common pitfalls</h2>
|
||
|
||
<ul>
|
||
<li><strong>Month folder must match policy date</strong> — upload uses commission month; API uses <code>policy_issue_date</code> to resolve the same <code>MONYYYY</code> folder.</li>
|
||
<li><strong>Append vs overwrite</strong> — append merges JSON arrays; overwrite backs up the old file then replaces.</li>
|
||
<li><strong>Departments</strong> — upload UI currently offers motor and health; API department string must match the JSON filename slug.</li>
|
||
<li><strong>HTTP 200 on upload errors</strong> — check <code>status</code> and <code>code</code> in the JSON body, not only HTTP status.</li>
|
||
</ul>
|