414 lines
16 KiB
PHP
414 lines
16 KiB
PHP
<?php
|
|
|
|
namespace App\Commands;
|
|
|
|
use App\Libraries\TPAClaimsImportServices\IciciClaimImportService;
|
|
use CodeIgniter\CLI\BaseCommand;
|
|
use CodeIgniter\CLI\CLI;
|
|
|
|
/**
|
|
* Temporary dry-run helper for ICICI existing-ticket status update logic.
|
|
*
|
|
* Usage:
|
|
* php spark tpa:test-icici-status --scenario
|
|
* php spark tpa:test-icici-status --scenario --apply
|
|
* php spark tpa:test-icici-status 68
|
|
* php spark tpa:test-icici-status 68 --apply
|
|
*/
|
|
class TestIciciStatusUpdate extends BaseCommand
|
|
{
|
|
protected $group = 'TPA';
|
|
protected $name = 'tpa:test-icici-status';
|
|
protected $description = 'Dry-run / apply ICICI existing ticket status update logic';
|
|
protected $usage = 'tpa:test-icici-status [file_id] [--scenario] [--apply] [--new-status STATUS]';
|
|
protected $arguments = [
|
|
'file_id' => 'Optional claim_dump_files.id to process',
|
|
];
|
|
protected $options = [
|
|
'--scenario' => 'Seed a controlled pending dump row for one existing ICICI ticket and test status update',
|
|
'--apply' => 'Actually run runTicketMasterInsert (default is map-only dry-run)',
|
|
'--new-status' => 'Dump status string to use in scenario (default: REJECTED)',
|
|
];
|
|
|
|
public function run(array $params)
|
|
{
|
|
helper('utility_helper');
|
|
|
|
$db = db_connect();
|
|
$iciciTpaId = (int) env('ICICI_PRIMARY_KEY_CONSTANT');
|
|
$fileId = $this->resolveFileId($params);
|
|
$apply = $this->hasFlag('apply');
|
|
$scenario = $this->hasFlag('scenario');
|
|
$newStatus = $this->resolveOptionValue('new-status', 'REJECTED');
|
|
|
|
CLI::write("ICICI_PRIMARY_KEY_CONSTANT = {$iciciTpaId}", 'yellow');
|
|
CLI::write('Parsed args: file_id=' . $fileId . ', apply=' . ($apply ? 'yes' : 'no') . ', scenario=' . ($scenario ? 'yes' : 'no') . ', new_status=' . $newStatus, 'yellow');
|
|
|
|
if ($scenario) {
|
|
$fileId = $this->seedScenario($db, $iciciTpaId, $newStatus);
|
|
if ($fileId <= 0) {
|
|
return;
|
|
}
|
|
}
|
|
|
|
if ($fileId <= 0) {
|
|
$recent = $db->table('claim_dump_files')
|
|
->select('id, tpa_id, client_id, client_policy_id, file_name, status, created_at')
|
|
->where('tpa_id', $iciciTpaId)
|
|
->orderBy('id', 'DESC')
|
|
->limit(10)
|
|
->get()
|
|
->getResultArray();
|
|
|
|
CLI::write('No --file_id provided. Recent ICICI claim_dump_files:', 'cyan');
|
|
if (empty($recent)) {
|
|
CLI::error('No ICICI claim dump files found.');
|
|
$this->printMatchPreview($db, $iciciTpaId);
|
|
return;
|
|
}
|
|
|
|
foreach ($recent as $row) {
|
|
CLI::write(json_encode($row));
|
|
}
|
|
|
|
CLI::newLine();
|
|
CLI::write('Controlled scenario test:', 'green');
|
|
CLI::write(' php spark tpa:test-icici-status --scenario');
|
|
CLI::write(' php spark tpa:test-icici-status --scenario --apply');
|
|
CLI::write('Existing file (use positional file_id):');
|
|
CLI::write(' php spark tpa:test-icici-status 68');
|
|
CLI::write(' php spark tpa:test-icici-status 68 --apply');
|
|
$this->printMatchPreview($db, $iciciTpaId);
|
|
return;
|
|
}
|
|
|
|
$file = $db->table('claim_dump_files')->where('id', $fileId)->get()->getRowArray();
|
|
if (empty($file)) {
|
|
CLI::error("claim_dump_files id={$fileId} not found");
|
|
return;
|
|
}
|
|
|
|
CLI::write('File: ' . json_encode([
|
|
'id' => $file['id'],
|
|
'tpa_id' => $file['tpa_id'],
|
|
'status' => $file['status'],
|
|
'file_name' => $file['file_name'],
|
|
'client_policy_id' => $file['client_policy_id'],
|
|
]), 'cyan');
|
|
|
|
if ((int) $file['tpa_id'] !== $iciciTpaId) {
|
|
CLI::error("File tpa_id={$file['tpa_id']} is not ICICI ({$iciciTpaId}). Aborting.");
|
|
return;
|
|
}
|
|
|
|
$pendingDump = $db->table('claims_dump_icici')
|
|
->where('file_id', $fileId)
|
|
->where('is_active', 1)
|
|
->where('ticket_id IS NULL', null, false)
|
|
->where('master_reject_reason IS NULL', null, false)
|
|
->countAllResults();
|
|
|
|
CLI::write("Pending dump rows for mapClaimMasterData: {$pendingDump}", 'yellow');
|
|
|
|
$service = new IciciClaimImportService();
|
|
|
|
if ($apply) {
|
|
CLI::write('APPLY mode: running runTicketMasterInsert...', 'light_red');
|
|
|
|
$pendingBefore = $db->table('claims_dump_icici')
|
|
->select('id, ticket_id, master_reject_reason, updated_status, employee_member_id, uhid, claimed_amount, doa')
|
|
->where('file_id', $fileId)
|
|
->where('is_active', 1)
|
|
->get()
|
|
->getResultArray();
|
|
CLI::write('Dump rows BEFORE: ' . json_encode($pendingBefore, JSON_PRETTY_PRINT));
|
|
|
|
$result = $service->runTicketMasterInsert(['file_id' => $fileId]);
|
|
CLI::write('Result: ' . json_encode($result, JSON_PRETTY_PRINT));
|
|
|
|
$pendingAfter = $db->table('claims_dump_icici')
|
|
->select('id, ticket_id, master_reject_reason, updated_status, employee_member_id, uhid, claimed_amount, doa')
|
|
->where('file_id', $fileId)
|
|
->where('is_active', 1)
|
|
->get()
|
|
->getResultArray();
|
|
CLI::write('Dump rows AFTER: ' . json_encode($pendingAfter, JSON_PRETTY_PRINT));
|
|
|
|
$ticketIds = array_values(array_filter(array_column($pendingAfter, 'ticket_id')));
|
|
if (!empty($ticketIds)) {
|
|
$ticketsAfter = $db->table('ticket_master')
|
|
->select('id, claim_status_id, claim_dump_ref_id, emp_code, tpa_no, claim_amount, doa')
|
|
->whereIn('id', $ticketIds)
|
|
->get()
|
|
->getResultArray();
|
|
CLI::write('Linked tickets AFTER: ' . json_encode($ticketsAfter, JSON_PRETTY_PRINT));
|
|
}
|
|
|
|
return;
|
|
}
|
|
|
|
CLI::write('DRY-RUN mode: calling mapClaimMasterData only (no DB writes)...', 'green');
|
|
|
|
$ref = new \ReflectionClass($service);
|
|
$method = $ref->getMethod('mapClaimMasterData');
|
|
$method->setAccessible(true);
|
|
$mapped = $method->invoke($service, $fileId);
|
|
|
|
if (empty($mapped['status'])) {
|
|
CLI::error('mapClaimMasterData failed: ' . ($mapped['message'] ?? 'unknown'));
|
|
if (!empty($mapped['error_data'])) {
|
|
CLI::write(json_encode($mapped['error_data'], JSON_PRETTY_PRINT));
|
|
}
|
|
return;
|
|
}
|
|
|
|
$inserts = $mapped['mapped_array'] ?? [];
|
|
$rejects = $mapped['rejected_reason_array'] ?? [];
|
|
$updates = $mapped['status_update_array'] ?? [];
|
|
|
|
CLI::newLine();
|
|
CLI::write('=== RESULT COUNTS ===', 'yellow');
|
|
CLI::write('New tickets to insert : ' . count($inserts));
|
|
CLI::write('Status updates (existing) : ' . count($updates));
|
|
CLI::write('Rejected / skipped rows : ' . count($rejects));
|
|
|
|
CLI::newLine();
|
|
CLI::write('=== STATUS UPDATE ARRAY ===', 'cyan');
|
|
CLI::write(empty($updates) ? '(none)' : json_encode($updates, JSON_PRETTY_PRINT));
|
|
|
|
CLI::newLine();
|
|
CLI::write('=== REJECT / SKIP REASONS (first 20) ===', 'cyan');
|
|
CLI::write(json_encode(array_slice($rejects, 0, 20), JSON_PRETTY_PRINT));
|
|
|
|
if (!empty($updates)) {
|
|
CLI::newLine();
|
|
CLI::write('Ticket fields BEFORE update:', 'yellow');
|
|
$ids = array_column($updates, 'id');
|
|
$before = $db->table('ticket_master')
|
|
->select('id, emp_code, tpa_no, claim_amount, doa, claim_status_id, claim_dump_ref_id, file_id')
|
|
->whereIn('id', $ids)
|
|
->get()
|
|
->getResultArray();
|
|
CLI::write(json_encode($before, JSON_PRETTY_PRINT));
|
|
}
|
|
|
|
CLI::newLine();
|
|
CLI::write('To apply for real:', 'green');
|
|
CLI::write(" php spark tpa:test-icici-status {$fileId} --apply");
|
|
}
|
|
|
|
private function resolveFileId(array $params): int
|
|
{
|
|
if (!empty($params['file_id'])) {
|
|
return (int) $params['file_id'];
|
|
}
|
|
if (!empty($params[0]) && is_numeric($params[0])) {
|
|
return (int) $params[0];
|
|
}
|
|
|
|
// Fallback for --file_id=68 / --file_id 68 (CI option parsing can miss these)
|
|
$argv = $_SERVER['argv'] ?? [];
|
|
foreach ($argv as $i => $arg) {
|
|
if (preg_match('/^--file[_-]id=(.+)$/', (string) $arg, $m)) {
|
|
return (int) $m[1];
|
|
}
|
|
if (in_array($arg, ['--file_id', '--file-id'], true) && isset($argv[$i + 1])) {
|
|
return (int) $argv[$i + 1];
|
|
}
|
|
}
|
|
|
|
$opt = CLI::getOption('file_id') ?? CLI::getOption('file-id');
|
|
return (int) ($opt ?? 0);
|
|
}
|
|
|
|
private function hasFlag(string $name): bool
|
|
{
|
|
if (CLI::getOption($name) !== null) {
|
|
return true;
|
|
}
|
|
|
|
$argv = $_SERVER['argv'] ?? [];
|
|
return in_array('--' . $name, $argv, true);
|
|
}
|
|
|
|
private function resolveOptionValue(string $name, string $default): string
|
|
{
|
|
$opt = CLI::getOption($name);
|
|
if (is_string($opt) && $opt !== '') {
|
|
return $opt;
|
|
}
|
|
|
|
$argv = $_SERVER['argv'] ?? [];
|
|
foreach ($argv as $i => $arg) {
|
|
if (preg_match('/^--' . preg_quote($name, '/') . '=(.+)$/', (string) $arg, $m)) {
|
|
return trim($m[1]);
|
|
}
|
|
if ($arg === '--' . $name && isset($argv[$i + 1]) && strpos((string) $argv[$i + 1], '--') !== 0) {
|
|
return (string) $argv[$i + 1];
|
|
}
|
|
}
|
|
|
|
return $default;
|
|
}
|
|
|
|
/**
|
|
* Create a temporary claim_dump_files + one pending claims_dump_icici row
|
|
* matching an existing ticket, with a different dump status.
|
|
*/
|
|
private function seedScenario($db, int $iciciTpaId, string $newStatus): int
|
|
{
|
|
CLI::write("Seeding controlled ICICI status-update scenario (new_status={$newStatus})...", 'cyan');
|
|
|
|
$ticket = $db->table('ticket_master')
|
|
->select('id, client_id, client_policy_id, emp_code, tpa_no, claim_amount, doa, claim_status_id, claim_dump_ref_id, tpa_id')
|
|
->where('tpa_id', $iciciTpaId)
|
|
->where('is_active', 1)
|
|
->where('emp_code IS NOT NULL', null, false)
|
|
->where('tpa_no IS NOT NULL', null, false)
|
|
->where('claim_amount IS NOT NULL', null, false)
|
|
->where('doa IS NOT NULL', null, false)
|
|
->orderBy('id', 'DESC')
|
|
->get()
|
|
->getRowArray();
|
|
|
|
if (empty($ticket)) {
|
|
// Fall back to any ticket, then override tpa matching by using that ticket's keys
|
|
$ticket = $db->table('ticket_master')
|
|
->select('id, client_id, client_policy_id, emp_code, tpa_no, claim_amount, doa, claim_status_id, claim_dump_ref_id, tpa_id')
|
|
->where('is_active', 1)
|
|
->where('claim_created_by', 'DUMP_TPA')
|
|
->where('emp_code IS NOT NULL', null, false)
|
|
->where('tpa_no IS NOT NULL', null, false)
|
|
->where('claim_amount IS NOT NULL', null, false)
|
|
->where('doa IS NOT NULL', null, false)
|
|
->orderBy('id', 'DESC')
|
|
->get()
|
|
->getRowArray();
|
|
}
|
|
|
|
if (empty($ticket)) {
|
|
CLI::error('No suitable existing ticket found to seed a status-update scenario.');
|
|
return 0;
|
|
}
|
|
|
|
CLI::write('Using existing ticket: ' . json_encode([
|
|
'id' => $ticket['id'],
|
|
'emp_code' => $ticket['emp_code'],
|
|
'tpa_no' => $ticket['tpa_no'],
|
|
'claim_amount' => $ticket['claim_amount'],
|
|
'doa' => $ticket['doa'],
|
|
'claim_status_id' => $ticket['claim_status_id'],
|
|
'claim_dump_ref_id' => $ticket['claim_dump_ref_id'] ?? null,
|
|
'tpa_id' => $ticket['tpa_id'],
|
|
]), 'yellow');
|
|
|
|
// Clear stale claim_dump_ref_id so the scenario can re-test link backfill.
|
|
if (!empty($ticket['claim_dump_ref_id'])) {
|
|
$db->table('ticket_master')->where('id', $ticket['id'])->update([
|
|
'claim_dump_ref_id' => null,
|
|
]);
|
|
$ticket['claim_dump_ref_id'] = null;
|
|
CLI::write('Cleared stale ticket.claim_dump_ref_id for retest.', 'yellow');
|
|
}
|
|
|
|
// Ensure a physical file exists for resolveTpaClaimDumpFile if --apply uses controller later
|
|
$uploadDir = WRITEPATH . 'uploads' . DIRECTORY_SEPARATOR . 'claim_dump_excel' . DIRECTORY_SEPARATOR;
|
|
if (!is_dir($uploadDir)) {
|
|
mkdir($uploadDir, 0775, true);
|
|
}
|
|
$fileName = 'icici_status_test_' . date('Ymd_His') . '.xlsx';
|
|
$filePath = $uploadDir . $fileName;
|
|
if (!is_file($filePath)) {
|
|
file_put_contents($filePath, 'placeholder');
|
|
}
|
|
|
|
$fileInsert = [
|
|
'tpa_id' => $iciciTpaId,
|
|
'client_id' => $ticket['client_id'],
|
|
'client_policy_id' => $ticket['client_policy_id'],
|
|
'file_name' => $fileName,
|
|
'status' => 'pending',
|
|
'created_by' => 1,
|
|
'created_at' => date('Y-m-d H:i:s'),
|
|
'is_active' => 1,
|
|
];
|
|
|
|
// claim_dump_files may have different nullable columns; insert only known-safe ones
|
|
$fileCols = array_column($db->query('SHOW COLUMNS FROM claim_dump_files')->getResultArray(), 'Field');
|
|
$fileInsert = array_intersect_key($fileInsert, array_flip($fileCols));
|
|
|
|
$db->table('claim_dump_files')->insert($fileInsert);
|
|
$fileId = (int) $db->insertID();
|
|
if ($fileId <= 0) {
|
|
CLI::error('Failed to create claim_dump_files row for scenario.');
|
|
return 0;
|
|
}
|
|
|
|
$dumpInsert = [
|
|
'client_id' => $ticket['client_id'],
|
|
'client_policy_id' => $ticket['client_policy_id'],
|
|
'file_id' => $fileId,
|
|
'employee_member_id' => $ticket['emp_code'],
|
|
'uhid' => $ticket['tpa_no'],
|
|
'claimed_amount' => $ticket['claim_amount'],
|
|
'doa' => $ticket['doa'],
|
|
'updated_status' => $newStatus,
|
|
'relation' => 'SELF',
|
|
'is_active' => 1,
|
|
'created_by' => 1,
|
|
'created_at' => date('Y-m-d H:i:s'),
|
|
'ticket_id' => null,
|
|
'master_reject_reason' => null,
|
|
];
|
|
|
|
$dumpCols = array_column($db->query('SHOW COLUMNS FROM claims_dump_icici')->getResultArray(), 'Field');
|
|
$dumpInsert = array_intersect_key($dumpInsert, array_flip($dumpCols));
|
|
|
|
$db->table('claims_dump_icici')->insert($dumpInsert);
|
|
$dumpId = (int) $db->insertID();
|
|
|
|
CLI::write("Seeded file_id={$fileId}, dump_id={$dumpId}", 'green');
|
|
CLI::write("Expected: if ticket claim_status_id ({$ticket['claim_status_id']}) != mapped REJECTED(8)/chosen status, status_update_array should contain ticket {$ticket['id']}", 'green');
|
|
|
|
return $fileId;
|
|
}
|
|
|
|
private function printMatchPreview($db, int $iciciTpaId): void
|
|
{
|
|
CLI::newLine();
|
|
CLI::write('Preview: dump rows that match existing tickets (possible status updates)', 'cyan');
|
|
|
|
$sql = "
|
|
SELECT
|
|
cd.id AS dump_id,
|
|
cd.file_id,
|
|
cd.employee_member_id,
|
|
cd.uhid,
|
|
cd.claimed_amount,
|
|
cd.doa,
|
|
cd.updated_status AS dump_status,
|
|
tm.id AS ticket_id,
|
|
tm.claim_status_id AS current_status_id
|
|
FROM claims_dump_icici cd
|
|
INNER JOIN ticket_master tm
|
|
ON tm.is_active = 1
|
|
AND tm.emp_code = cd.employee_member_id
|
|
AND tm.tpa_no = cd.uhid
|
|
AND tm.claim_amount = cd.claimed_amount
|
|
AND tm.doa = cd.doa
|
|
WHERE cd.is_active = 1
|
|
AND tm.tpa_id = ?
|
|
ORDER BY cd.id DESC
|
|
LIMIT 10
|
|
";
|
|
|
|
$rows = $db->query($sql, [$iciciTpaId])->getResultArray();
|
|
if (empty($rows)) {
|
|
CLI::write('No overlapping dump/ticket pairs found.');
|
|
return;
|
|
}
|
|
|
|
CLI::write(json_encode($rows, JSON_PRETTY_PRINT));
|
|
}
|
|
}
|