260 lines
11 KiB
PHP
260 lines
11 KiB
PHP
<?php
|
||
/**
|
||
* Deletion (employee Excel upload) — content only
|
||
* app/Views/docs/deletion.php
|
||
*
|
||
* Based on:
|
||
* - app/Views/employee_upload.php
|
||
* - app/Controllers/EmployeeServiceController.php
|
||
* (excelFileFormatValidation, excelFileDataValidation, employeeDisembark)
|
||
* - app/Controllers/EmployeeController.php
|
||
* (employeesUplodWithEvents, initializeDeletionProcessForTpaApiData)
|
||
* - app/Controllers/JobWorker.php
|
||
*/
|
||
?>
|
||
|
||
<p>
|
||
<strong>Deletion</strong> removes active members from a client policy via Excel upload
|
||
(<code>files.action = deletion</code>). Unlike inception, the final step does not delete rows immediately —
|
||
it creates <strong>pending endorsement</strong> records on <code>employee_polices</code> for approval/processing later.
|
||
</p>
|
||
|
||
<p>
|
||
Core processing lives in <code>EmployeeServiceController::employeeDisembark</code>.
|
||
<code>EmployeeController::initializeDeletionProcessForTpaApiData</code> is a separate TPA-reconcile path that
|
||
builds a deletion Excel file and calls the same disembark function.
|
||
</p>
|
||
|
||
<h2 id="overview">Overview</h2>
|
||
|
||
<div class="mermaid-wrapper">
|
||
<div class="mermaid">
|
||
flowchart LR
|
||
A["Upload Excel action deletion"] --> B["excelFileFormatValidation"]
|
||
B --> C["excelFileDataValidation"]
|
||
C --> D["employeeDisembark job"]
|
||
D --> E["emp_endorsement pending rows"]
|
||
B -->|errors| F["files.status failed"]
|
||
C -->|errors| F
|
||
D -->|always| G["files.status success"]
|
||
</div>
|
||
</div>
|
||
|
||
<p><strong>In short:</strong></p>
|
||
<ul>
|
||
<li><strong>Step 1 — Format:</strong> 7 columns (A–G), mandatory exit fields per action code <code>D</code>.</li>
|
||
<li><strong>Step 2 — Data:</strong> Member must exist in DB (emp code + name + active policy); code 10 if not found.</li>
|
||
<li><strong>Step 3 — Disembark:</strong> Writes pending deletion endorsements; Self row removes whole family, dependent row removes one member.</li>
|
||
<li>Files < 1 MB run format validation inline; data validation and disembark are always queued.</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/employee_upload.php</code> — action <strong>Deletion</strong></td>
|
||
</tr>
|
||
<tr>
|
||
<td>Upload</td>
|
||
<td><code>EmployeeController::employeesUplodWithEvents</code></td>
|
||
</tr>
|
||
<tr>
|
||
<td>Validation</td>
|
||
<td><code>EmployeeServiceController::excelFileFormatValidation</code>, <code>excelFileDataValidation</code></td>
|
||
</tr>
|
||
<tr>
|
||
<td>Deletion process</td>
|
||
<td><code>EmployeeServiceController::employeeDisembark</code></td>
|
||
</tr>
|
||
<tr>
|
||
<td>TPA auto-deletion</td>
|
||
<td><code>EmployeeController::initializeDeletionProcessForTpaApiData</code></td>
|
||
</tr>
|
||
<tr>
|
||
<td>Job</td>
|
||
<td><code>employeeDisembark</code> in <code>JobWorker.php</code></td>
|
||
</tr>
|
||
<tr>
|
||
<td>Endorsements</td>
|
||
<td><code>EmpEndorsementModel</code> → <code>emp_endorsement</code> (<code>actions = d</code>, <code>status = pending</code>)</td>
|
||
</tr>
|
||
</tbody>
|
||
</table>
|
||
|
||
<p><strong>Routes</strong> (group <code>/employee</code>, <code>authMVC</code>):</p>
|
||
<ul>
|
||
<li><code>GET /employee/upload</code> — upload screen</li>
|
||
<li><code>POST /employee/upload</code> — <code>upload-action-type=deletion</code></li>
|
||
<li><code>GET /employee/excel_error/{file_id}</code> — read <code>files.reason</code> after validation failure</li>
|
||
</ul>
|
||
|
||
<div class="callout info">
|
||
<span>i</span>
|
||
<div>
|
||
<strong><code>files.policy_id</code></strong> is the <strong>client policy id</strong>. Member lookup joins
|
||
<code>employees</code> + <code>employee_polices</code> on that policy and branch.
|
||
</div>
|
||
</div>
|
||
|
||
<h2 id="sync-vs-jobs">Sync vs background jobs</h2>
|
||
|
||
<table>
|
||
<thead>
|
||
<tr><th>Step</th><th>< 1 MB</th><th>≥ 1 MB</th></tr>
|
||
</thead>
|
||
<tbody>
|
||
<tr><td>Format validation</td><td>Inline on upload</td><td>Job <code>excelFileFormatValidation</code></td></tr>
|
||
<tr><td>Data validation</td><td>Job <code>excelFileDataValidation</code></td><td>Same</td></tr>
|
||
<tr><td>Disembark</td><td>Job <code>employeeDisembark</code></td><td>Same</td></tr>
|
||
</tbody>
|
||
</table>
|
||
|
||
<h2 id="format-validation">Step 1: excelFileFormatValidation</h2>
|
||
|
||
<ul>
|
||
<li>Uses <code>$deletion_excel_columns</code> — <strong>7 columns (A–G)</strong>.</li>
|
||
<li>Action code <code>D</code> drives mandatory fields: Change event, Date of exit, Reason for exit, Claim status.</li>
|
||
<li>Date of exit format: <code>d-M-Y</code>.</li>
|
||
<li>Claim status allowed: <code>0</code> or <code>1</code>.</li>
|
||
</ul>
|
||
|
||
<h3 id="format-errors">Format error codes</h3>
|
||
|
||
<table>
|
||
<thead>
|
||
<tr><th>Code</th><th>Meaning</th></tr>
|
||
</thead>
|
||
<tbody>
|
||
<tr><td>1</td><td>Mandatory missing</td></tr>
|
||
<tr><td>2</td><td>Wrong format</td></tr>
|
||
<tr><td>3</td><td>Not in allowed list</td></tr>
|
||
<tr><td>4</td><td>Custom validation failed</td></tr>
|
||
<tr><td>5</td><td>File / policy problem</td></tr>
|
||
<tr><td>6</td><td>Column headers wrong</td></tr>
|
||
</tbody>
|
||
</table>
|
||
|
||
<h2 id="data-validation">Step 2: excelFileDataValidation</h2>
|
||
|
||
<p>Rows are grouped by EMP ID. For deletion, the main check is <code>name_and_empid_check_in_db</code>:</p>
|
||
<ul>
|
||
<li>Each row must match an <strong>active</strong> employee + <strong>active</strong> <code>employee_polices</code> row on the uploaded policy/branch.</li>
|
||
<li>If not found → error code <strong>10</strong> (“Record Not found”) on that Excel row.</li>
|
||
</ul>
|
||
|
||
<p>On success → queues <code>employeeDisembark</code> (not <code>employeesOnboardPreprocess</code>).</p>
|
||
|
||
<h2 id="disembark">Step 3: employeeDisembark</h2>
|
||
|
||
<p>Loads the Excel again and processes each non-empty row.</p>
|
||
|
||
<div class="mermaid-wrapper">
|
||
<div class="mermaid">
|
||
flowchart TD
|
||
A["Match emp_code plus name on active policy"] --> B{"Found?"}
|
||
B -->|No| C["Log error skip row"]
|
||
B -->|Yes| D{"Pending deletion endorsement?"}
|
||
D -->|Yes| E["Log skip row"]
|
||
D -->|No| F{"Relationship Self?"}
|
||
F -->|No| G["Endorse this member only"]
|
||
F -->|Yes| H["Endorse all active family members"]
|
||
G --> I["emp_endorsement pending"]
|
||
H --> I
|
||
</div>
|
||
</div>
|
||
|
||
<p><strong>Per matched member</strong>, four pending endorsement rows are inserted on <code>employee_polices</code>:</p>
|
||
<ul>
|
||
<li><code>date_of_exit</code> ← Excel column E (converted to <code>Y-m-d</code>)</li>
|
||
<li><code>reason_for_exit</code> ← column F</li>
|
||
<li><code>status</code> → <code>inactive</code></li>
|
||
<li><code>claim_status</code> ← column G</li>
|
||
</ul>
|
||
|
||
<p>
|
||
Returns an array of processed <code>employee.id</code> values. Sets <code>files.status = success</code> when the loop finishes
|
||
(even if some rows were skipped — check logs for “not found” or “existing endorsement pending”).
|
||
</p>
|
||
|
||
<h2 id="tpa-auto">TPA auto-deletion (EmployeeController)</h2>
|
||
|
||
<p><code>initializeDeletionProcessForTpaApiData($file_id)</code>:</p>
|
||
<ol>
|
||
<li>Reads TPA reconcile file context (<code>tpa_api_data</code>, <code>action_flag_status = D</code>).</li>
|
||
<li>Builds candidate members and writes <code>writable/uploads/excel/tpa_auto_deletion_{fileId}_{timestamp}.xls</code>.</li>
|
||
<li>Inserts a new <code>files</code> row with <code>action = deletion</code>.</li>
|
||
<li>Calls <code>employeeDisembark(['file_id' => $newFileId])</code> synchronously.</li>
|
||
<li>Exports endorsement data for TPA via <code>getDeletionEmployeeDataForExportExcel</code>.</li>
|
||
</ol>
|
||
|
||
<h2 id="excel-columns">Deletion Excel columns</h2>
|
||
|
||
<table>
|
||
<thead>
|
||
<tr><th>Col</th><th>Header</th><th>Required</th><th>Notes</th></tr>
|
||
</thead>
|
||
<tbody>
|
||
<tr><td>A</td><td>S.No</td><td>Yes</td><td></td></tr>
|
||
<tr><td>B</td><td>EMP ID</td><td>Yes</td><td>Employee / family code</td></tr>
|
||
<tr><td>C</td><td>NAME OF EMP/DEP</td><td>Yes</td><td>Must match DB name exactly</td></tr>
|
||
<tr><td>D</td><td>Change event</td><td>Yes</td><td>e.g. <code>deletion</code></td></tr>
|
||
<tr><td>E</td><td>Date of exit</td><td>Yes</td><td><code>d-M-Y</code></td></tr>
|
||
<tr><td>F</td><td>Reason for exit</td><td>Yes</td><td></td></tr>
|
||
<tr><td>G</td><td>Claim status</td><td>Yes</td><td><code>0</code> or <code>1</code></td></tr>
|
||
</tbody>
|
||
</table>
|
||
|
||
<h2 id="row-example">Row layout examples</h2>
|
||
|
||
<p><strong>Delete one dependent</strong> — only that name appears; Self row is not required in the file.</p>
|
||
|
||
<table>
|
||
<thead>
|
||
<tr><th>Row</th><th>EMP ID</th><th>NAME</th><th>Change event</th><th>Date of exit</th><th>Reason</th><th>Claim</th></tr>
|
||
</thead>
|
||
<tbody>
|
||
<tr><td>2</td><td>EMP001</td><td>Arjun Kumar</td><td>deletion</td><td>19-May-2026</td><td>Resigned</td><td>0</td></tr>
|
||
</tbody>
|
||
</table>
|
||
<p>Result: endorsements for <strong>Arjun only</strong> (relationship ≠ Self).</p>
|
||
|
||
<p><strong>Delete entire family</strong> — list the <strong>Self</strong> row; disembark loads all active family members for that EMP ID.</p>
|
||
|
||
<table>
|
||
<thead>
|
||
<tr><th>Row</th><th>EMP ID</th><th>NAME</th><th>Change event</th><th>Date of exit</th><th>Reason</th><th>Claim</th></tr>
|
||
</thead>
|
||
<tbody>
|
||
<tr><td>2</td><td>EMP001</td><td>Raj Kumar</td><td>deletion</td><td>19-May-2026</td><td>Resigned</td><td>0</td></tr>
|
||
</tbody>
|
||
</table>
|
||
<p>Result: pending endorsements for <strong>Self + all active dependents</strong> on that policy (same exit date/reason/claim from the row).</p>
|
||
|
||
<h2 id="developer-steps">Developer steps</h2>
|
||
|
||
<ol>
|
||
<li>Upload via <code>/employee/upload</code> with action <code>deletion</code>; note <code>file_id</code>.</li>
|
||
<li>If validation fails, use <code>GET /employee/excel_error/{file_id}</code> — look for code <strong>10</strong> (member not in DB).</li>
|
||
<li>After success, query <code>emp_endorsement</code> where <code>file_id</code> = upload id, <code>actions = 'd'</code>, <code>status = 'pending'</code>.</li>
|
||
<li>If rows were skipped, search logs for <code>not found</code> or <code>Existing endorsement pending</code>.</li>
|
||
<li>TPA path: trace <code>initializeDeletionProcessForTpaApiData</code> and the generated <code>tpa_auto_deletion_*.xls</code> file.</li>
|
||
</ol>
|
||
|
||
<h2 id="pitfalls">Common pitfalls</h2>
|
||
|
||
<ul>
|
||
<li><strong>Name mismatch</strong> — Excel name must match <code>employees.name</code> exactly (case/spacing).</li>
|
||
<li><strong>Not active</strong> — only <code>emp_status = active</code> and <code>employee_polices.status = active</code> match.</li>
|
||
<li><strong>Duplicate pending deletion</strong> — row skipped if a pending deletion endorsement already exists for that policy row.</li>
|
||
<li><strong>Self vs dependent</strong> — wrong relationship in the row changes scope (one member vs whole family).</li>
|
||
<li><strong>File always success after disembark</strong> — <code>files.status</code> does not reflect per-row skips; use endorsements table + logs.</li>
|
||
<li><strong>Not immediate delete</strong> — members stay active until endorsements are approved/applied downstream.</li>
|
||
</ul>
|
||
|
||
<p>Related: <a href="<?= base_url('docs/inception') ?>">Inception</a> (onboard pipeline uses the same upload screen and first two validation steps).</p>
|