906 lines
41 KiB
PHP
906 lines
41 KiB
PHP
<?php
|
|
|
|
namespace App\Controllers;
|
|
|
|
use CodeIgniter\RESTful\ResourceController;
|
|
use App\Controllers\BaseController;
|
|
|
|
use PhpOffice\PhpSpreadsheet\IOFactory;
|
|
use PhpOffice\PhpSpreadsheet\Spreadsheet;
|
|
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
|
|
|
|
use App\Libraries\PartnerPayoutGridRetention;
|
|
use App\Models\AgentIncentiveFileModel;
|
|
use App\Models\PartnerInsurancePayoutGridModel;
|
|
use App\Models\AgentModel;
|
|
|
|
|
|
class AgentIncentiveController extends ResourceController
|
|
{
|
|
protected $AgentIncentiveFileModel;
|
|
protected $PayoutGridModel;
|
|
protected $PartnerAgentModel;
|
|
|
|
public function __construct()
|
|
{
|
|
$this->AgentIncentiveFileModel = new AgentIncentiveFileModel();
|
|
$this->PayoutGridModel = new PartnerInsurancePayoutGridModel();
|
|
$this->PartnerAgentModel = new AgentModel();
|
|
}
|
|
|
|
// GET /agent/gridFileList
|
|
public function gridFileList()
|
|
{
|
|
try {
|
|
$builder = $this->AgentIncentiveFileModel->builder();
|
|
|
|
// 1. Explicitly select and format dates in the SQL layer (Faster + Indian Format)
|
|
$builder->select("
|
|
paif.id,
|
|
paif.agent_id,
|
|
paif.incentive_month,
|
|
DATE_FORMAT(paif.incentive_month, '%d-%m-%Y') as vaild_from,
|
|
paif.incentive_file_name,
|
|
ps.name as created_by_name,
|
|
paif.created_on,
|
|
DATE_FORMAT(paif.created_on, '%d-%m-%Y %h:%i %p') as created_date
|
|
");
|
|
|
|
$builder->from('partner_agent_incentive_file paif');
|
|
|
|
// 2. The Join
|
|
$builder->join('partner_staff ps', 'ps.id = paif.created_by', 'left');
|
|
|
|
// 3. Filters
|
|
$builder->where('paif.is_active', 1);
|
|
$builder->where('paif.file_type', 'grid');
|
|
|
|
// 4. THE FIX: Group by the primary ID to stop the "5 rows" duplication
|
|
$builder->groupBy('paif.id');
|
|
|
|
// 5. Order
|
|
$builder->orderBy('paif.id', 'DESC');
|
|
|
|
$query = $builder->get();
|
|
$result = $query->getResult();
|
|
|
|
// Use $this->respond to maintain consistency with your other API methods
|
|
return $this->respond([
|
|
'status' => 'success', // Changed to 'success' to match your other methods
|
|
'code' => 200,
|
|
'data' => $result
|
|
], 200);
|
|
|
|
} catch (\Exception $e) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 500,
|
|
'data' => $e->getMessage()
|
|
], 500);
|
|
}
|
|
}
|
|
|
|
// -------------------------------------------------------------------------
|
|
// Upload Grid File (file_type = 'grid')
|
|
// Parses Excel and UPSERTs rows into partner_insurance_payout_grid
|
|
// -------------------------------------------------------------------------
|
|
public function uploadGridFile()
|
|
{
|
|
$localPath = null;
|
|
|
|
try {
|
|
/* ====================================================================
|
|
* STEP 1 — Validate POST input
|
|
* ==================================================================== */
|
|
$data = $this->request->getPost();
|
|
$month = $data['incentive_month'] ?? null;
|
|
$createdBy = $data['created_by'] ?? null;
|
|
|
|
/* ====================================================================
|
|
* STEP 2 — Validation checks for month
|
|
* ==================================================================== */
|
|
if (empty($month)) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 400,
|
|
'data' => 'Date is required.',
|
|
], 200);
|
|
}
|
|
|
|
/* ====================================================================
|
|
* STEP 3 — Validate uploaded file
|
|
* ==================================================================== */
|
|
$gridFile = $this->request->getFile('incentive_file_name');
|
|
if (!$gridFile || !$gridFile->isValid()) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 400,
|
|
'data' => 'No valid file uploaded.',
|
|
], 200);
|
|
}
|
|
|
|
/* ====================================================================
|
|
* STEP 4 — Upload file to storage (S3/local)
|
|
* ==================================================================== */
|
|
$gridFileName = storage_upload_if_valid($gridFile, 'agent', 'incentive_file');
|
|
if ($gridFileName === null) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 400,
|
|
'data' => 'No valid file uploaded.',
|
|
], 200);
|
|
}
|
|
|
|
$localPath = storage_local_path('agent', 'incentive_file', $gridFileName);
|
|
|
|
/* ====================================================================
|
|
* STEP 5 — Parse Excel FIRST (before saving file record)
|
|
* ==================================================================== */
|
|
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($localPath);
|
|
$rows = $spreadsheet->getActiveSheet()->toArray(null, true, true, false);
|
|
|
|
if (empty($rows)) {
|
|
storage_delete('agent', 'incentive_file', $gridFileName);
|
|
return $this->respond(['status' => 'failed', 'code' => 400, 'data' => 'File is empty.'], 200);
|
|
}
|
|
|
|
/* ====================================================================
|
|
* STEP 6 — Clean Numeric Helper
|
|
* ==================================================================== */
|
|
$extractNumeric = static function ($value): ?float {
|
|
$str = strtolower(trim((string) $value));
|
|
if ($str === '' || $str === 'nan') return null;
|
|
|
|
if (str_contains($str, '+')) {
|
|
$sum = 0.0; $hasValid = false;
|
|
foreach (explode('+', $str) as $part) {
|
|
$cleanPart = preg_replace('/[^0-9.]/', '', $part);
|
|
if (is_numeric($cleanPart)) { $sum += (float) $cleanPart; $hasValid = true; }
|
|
}
|
|
return $hasValid ? $sum : null;
|
|
}
|
|
$str = preg_replace('/[^0-9.]/', '', $str);
|
|
return is_numeric($str) ? (float) $str : null;
|
|
};
|
|
|
|
/* ====================================================================
|
|
* STEP 7 — Pre-validate ALL rows BEFORE inserting file record
|
|
* ==================================================================== */
|
|
$currentVehicleType = null;
|
|
$layoutHasComp = true;
|
|
$errors = [];
|
|
$validRows = [];
|
|
|
|
foreach ($rows as $rowIndex => $row) {
|
|
while (count($row) < 9) $row[] = null;
|
|
|
|
$col0 = trim((string) $row[0]);
|
|
$col1 = trim((string) $row[1]);
|
|
|
|
// ── Section Header Detection (vehicle type row) ──────────────────
|
|
if (
|
|
$col0 !== ''
|
|
&& ($col1 === '' || strtolower($col1) === 'nan')
|
|
&& strtoupper($col0) !== 'INSURER'
|
|
) {
|
|
$currentVehicleType = strtoupper($col0);
|
|
continue;
|
|
}
|
|
|
|
// ── Column Header Detection (Layout A vs B) ──────────────────────
|
|
if (strtoupper($col0) === 'INSURER') {
|
|
$col4Upper = strtoupper((string) $row[4]);
|
|
// Layout B = col E header is FUEL/PETROL/DIESEL (Private Car layout)
|
|
$layoutHasComp = !preg_match('/(FUEL|PETROL|DIESEL)/', $col4Upper);
|
|
continue;
|
|
}
|
|
|
|
// ── Skip completely empty rows ───────────────────────────────────
|
|
if (empty($col0)) continue;
|
|
|
|
// ── VALIDATION 1 : Vehicle type missing ──────────────────────────
|
|
if ($currentVehicleType === null) {
|
|
$errors[] = [
|
|
'row' => $rowIndex + 1,
|
|
'insurer' => $col0,
|
|
'message' => "Row " . ($rowIndex + 1) . " (Insurer: {$col0}): Vehicle type header is missing. "
|
|
. "Please add a vehicle type (e.g. 'TWO WHEELER NEW') before data rows.",
|
|
];
|
|
continue;
|
|
}
|
|
|
|
// ── VALIDATION 2 : Segment missing ───────────────────────────────
|
|
$segment = strtoupper(trim((string) $row[2]));
|
|
if ($segment === '' || strtolower($segment) === 'nan') {
|
|
$errors[] = [
|
|
'row' => $rowIndex + 1,
|
|
'insurer' => $col0,
|
|
'vehicle_type' => $currentVehicleType,
|
|
'message' => "Row " . ($rowIndex + 1) . " (Insurer: {$col0}, Vehicle Type: {$currentVehicleType}): "
|
|
. "Segment is empty. Please provide a valid segment value.",
|
|
];
|
|
continue;
|
|
}
|
|
|
|
// ── Map columns based on layout ───────────────────────────────────
|
|
if ($layoutHasComp) {
|
|
// Layout A: INSURER | RTO | SEGMENT | COMP | TP | REMARKS | ...
|
|
$insurer = strtoupper($col0);
|
|
$rto = strtoupper((string) $row[1]);
|
|
$comp = $extractNumeric($row[3]);
|
|
$tp = $extractNumeric($row[4]);
|
|
$od = null;
|
|
$fuel = null;
|
|
$brokerName = trim((string) $row[6]);
|
|
$brokerExcelComp = $extractNumeric($row[7]);
|
|
$brokerExcelTp = $extractNumeric($row[8]);
|
|
$brokerExcelOd = null;
|
|
} else {
|
|
// Layout B: INSURER | RTO | SEGMENT | COMP | FUEL | REMARKS | ...
|
|
$insurer = strtoupper($col0);
|
|
$rto = strtoupper((string) $row[1]);
|
|
$comp = $extractNumeric($row[3]);
|
|
$tp = null;
|
|
$od = null;
|
|
$fuel = strtoupper(trim((string) $row[4]));
|
|
$brokerName = trim((string) $row[6]);
|
|
$brokerExcelComp = null;
|
|
$brokerExcelTp = $extractNumeric($row[7]);
|
|
$brokerExcelOd = null;
|
|
}
|
|
|
|
// ── OD logic: col D has "OD x.xX" prefix → move to od field ──────
|
|
if ($comp !== null && stripos(trim((string) $row[3]), 'OD') !== false) {
|
|
$od = $comp; $comp = null;
|
|
$brokerExcelOd = $brokerExcelComp; $brokerExcelComp = null;
|
|
}
|
|
|
|
// ── OD logic: col E (TP) has "OD x.xX" prefix in Layout A ────────
|
|
if ($layoutHasComp && $tp !== null && stripos(trim((string) $row[4]), 'OD') !== false) {
|
|
$od = $tp; $tp = null;
|
|
$brokerExcelOd = $brokerExcelTp; $brokerExcelTp = null;
|
|
}
|
|
|
|
// ── Store valid parsed row ────────────────────────────────────────
|
|
$validRows[] = [
|
|
'vehicle_type' => $currentVehicleType,
|
|
'fuel' => $fuel,
|
|
'insurer' => $insurer,
|
|
'rto' => $rto,
|
|
'broker_name' => $brokerName,
|
|
'segment' => $segment,
|
|
'comp' => $comp,
|
|
'tp' => $tp,
|
|
'od' => $od,
|
|
'remarks' => trim((string) ($row[5] ?? '')),
|
|
'broker_comp' => $brokerExcelComp,
|
|
'broker_tp' => $brokerExcelTp,
|
|
'broker_od' => $brokerExcelOd,
|
|
'created_by' => $createdBy,
|
|
];
|
|
}
|
|
|
|
/* ====================================================================
|
|
* STEP 8 — If ANY validation errors, delete file & return errors
|
|
* Do NOT insert file record at all
|
|
* ==================================================================== */
|
|
if (!empty($errors)) {
|
|
storage_delete('agent', 'incentive_file', $gridFileName);
|
|
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 400,
|
|
'data' => [
|
|
'inserted' => 0,
|
|
'errors' => $errors,
|
|
'message' => count($errors) . ' row(s) had validation issues. File was not saved.',
|
|
],
|
|
], 200);
|
|
}
|
|
|
|
/* ====================================================================
|
|
* STEP 9 — All rows valid: NOW insert the file record
|
|
* ==================================================================== */
|
|
$fileData = [
|
|
'incentive_month' => $month,
|
|
'incentive_file_name' => $gridFileName,
|
|
'file_type' => 'grid',
|
|
'is_active' => 1,
|
|
'created_by' => $createdBy,
|
|
];
|
|
|
|
$fileId = $this->AgentIncentiveFileModel->insert($fileData, true);
|
|
if (empty($fileId) || (int)$fileId <= 0) {
|
|
$modelErrors = $this->AgentIncentiveFileModel->errors();
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 400,
|
|
'data' => !empty($modelErrors) ? $modelErrors : 'Unable to create incentive file record.',
|
|
], 200);
|
|
}
|
|
|
|
/* ====================================================================
|
|
* STEP 10 — Insert all valid rows with the file ID
|
|
* ==================================================================== */
|
|
$insertedCount = 0;
|
|
foreach ($validRows as $gridRow) {
|
|
$gridRow['partner_agent_incentive_file_id'] = $fileId;
|
|
$this->PayoutGridModel->insert($gridRow);
|
|
$insertedCount++;
|
|
}
|
|
|
|
/* ====================================================================
|
|
* STEP 11 — Full success response
|
|
* ==================================================================== */
|
|
return $this->respond([
|
|
'status' => 'success',
|
|
'code' => 200,
|
|
'data' => [
|
|
'file_id' => $fileId,
|
|
'inserted' => $insertedCount,
|
|
'message' => "Processed: {$insertedCount} new",
|
|
],
|
|
], 200);
|
|
|
|
} catch (\Exception $e) {
|
|
return $this->respond(['status' => 'failed', 'code' => 500, 'data' => $e->getMessage()], 500);
|
|
} finally {
|
|
storage_cleanup_temp($localPath);
|
|
}
|
|
}
|
|
// public function uploadGridFile_old()
|
|
// {
|
|
// try {
|
|
// /* ====================================================================
|
|
// * STEP 1 — Validate POST input (Agent ID removed)
|
|
// * ==================================================================== */
|
|
// $data = $this->request->getPost();
|
|
// $month = $data['incentive_month'] ?? null;
|
|
// $createdBy = $data['created_by'] ?? null;
|
|
|
|
// /* ====================================================================
|
|
// * STEP 2 — Validation now only checks for month
|
|
// * ==================================================================== */
|
|
// if (empty($month)) {
|
|
// return $this->respond([
|
|
// 'status' => 'failed',
|
|
// 'code' => 400,
|
|
// 'data' => 'Incentive month is required.',
|
|
// ], 200);
|
|
// }
|
|
|
|
// /* ====================================================================
|
|
// * STEP 3 — Validate uploaded file
|
|
// * ==================================================================== */
|
|
// $gridFile = $this->request->getFile('incentive_file_name');
|
|
// if (!$gridFile || !$gridFile->isValid()) {
|
|
// return $this->respond([
|
|
// 'status' => 'failed',
|
|
// 'code' => 400,
|
|
// 'data' => 'No valid file uploaded.',
|
|
// ], 200);
|
|
// }
|
|
|
|
// /* ====================================================================
|
|
// * STEP 4 — Move file
|
|
// * ==================================================================== */
|
|
// $uploadPath = WRITEPATH . 'uploads/agent/incentive_file/';
|
|
// if (!is_dir($uploadPath)) mkdir($uploadPath, 0777, true);
|
|
|
|
// $gridFileName = time() . '_' . $gridFile->getRandomName();
|
|
// $gridFile->move($uploadPath, $gridFileName);
|
|
|
|
// /* ====================================================================
|
|
// * STEP 5 — Save File record & GET THE ID
|
|
// * ==================================================================== */
|
|
// $fileData = [
|
|
// 'incentive_month' => $month,
|
|
// 'incentive_file_name' => $gridFileName,
|
|
// 'file_type' => 'grid',
|
|
// 'is_active' => 1,
|
|
// 'created_by' => $createdBy,
|
|
// ];
|
|
|
|
// // insert() with returnID enabled returns the inserted primary key in CI4.
|
|
// $fileId = $this->AgentIncentiveFileModel->insert($fileData, true);
|
|
// if (empty($fileId) || (int)$fileId <= 0) {
|
|
// $modelErrors = $this->AgentIncentiveFileModel->errors();
|
|
// return $this->respond([
|
|
// 'status' => 'failed',
|
|
// 'code' => 400,
|
|
// 'data' => !empty($modelErrors) ? $modelErrors : 'Unable to create incentive file record.',
|
|
// ], 200);
|
|
// }
|
|
|
|
// /* ====================================================================
|
|
// * STEP 6 — Parse Excel
|
|
// * ==================================================================== */
|
|
// $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($uploadPath . $gridFileName);
|
|
// $rows = $spreadsheet->getActiveSheet()->toArray(null, true, true, false);
|
|
|
|
// if (empty($rows)) {
|
|
// return $this->respond(['status' => 'success', 'code' => 200, 'data' => 'File empty.'], 200);
|
|
// }
|
|
|
|
// /* ====================================================================
|
|
// * STEP 7 — Clean Numeric Helper (Modified to store only numbers)
|
|
// * ==================================================================== */
|
|
// $extractNumeric = static function ($value): ?float {
|
|
// $str = strtolower(trim((string) $value));
|
|
// if ($str === '' || $str === 'nan') return null;
|
|
|
|
// if (str_contains($str, '+')) {
|
|
// $sum = 0.0; $hasValid = false;
|
|
// foreach (explode('+', $str) as $part) {
|
|
// $cleanPart = preg_replace('/[^0-9.]/', '', $part);
|
|
// if (is_numeric($cleanPart)) { $sum += (float) $cleanPart; $hasValid = true; }
|
|
// }
|
|
// return $hasValid ? $sum : null;
|
|
// }
|
|
// // Strip everything except numbers and decimals
|
|
// $str = preg_replace('/[^0-9.]/', '', $str);
|
|
// return is_numeric($str) ? (float) $str : null;
|
|
// };
|
|
|
|
// /* ====================================================================
|
|
// * STEP 8 — Process Rows
|
|
// * ==================================================================== */
|
|
// $currentVehicleType = null;
|
|
// $layoutHasComp = true;
|
|
// $insertedCount = 0;
|
|
|
|
// foreach ($rows as $row) {
|
|
// while (count($row) < 9) $row[] = null;
|
|
|
|
// $col0 = trim((string) $row[0]);
|
|
// $col1 = trim((string) $row[1]);
|
|
|
|
// // Section Header Detection
|
|
// if ($col0 !== '' && ($col1 === '' || strtolower($col1) === 'nan') && strtoupper($col0) !== 'INSURER') {
|
|
// $currentVehicleType = strtoupper($col0);
|
|
// continue;
|
|
// }
|
|
|
|
// // Column Header Detection (Layout A vs B)
|
|
// if (strtoupper($col0) === 'INSURER') {
|
|
// $col4Upper = strtoupper((string)$row[4]);
|
|
// $layoutHasComp = !preg_match('/(FUEL|PETROL|DIESEL|TP)/', $col4Upper);
|
|
// continue;
|
|
// }
|
|
|
|
// if (empty($col0) || $currentVehicleType === null) continue;
|
|
|
|
// // Mapping with Clean Numbers
|
|
// if ($layoutHasComp) {
|
|
// $insurer = strtoupper($col0);
|
|
// $rto = strtoupper((string)$row[1]);
|
|
// $segment = strtoupper((string)$row[2]);
|
|
// // We extract numeric values only for the storage
|
|
// $comp = $extractNumeric($row[3]);
|
|
// $tp = $extractNumeric($row[4]);
|
|
// $od = null;
|
|
// $fuel = null;
|
|
// $brokerName = trim((string)$row[6]);
|
|
// $brokerExcelComp = $extractNumeric($row[7]);
|
|
// $brokerExcelTp = $extractNumeric($row[8]);
|
|
// $brokerExcelOd = null;
|
|
|
|
// } else {
|
|
// $insurer = strtoupper($col0);
|
|
// $rto = strtoupper((string)$row[1]);
|
|
// $segment = strtoupper((string)$row[2]);
|
|
// $comp = $extractNumeric($row[3]);
|
|
// $tp = null;
|
|
// $od = null;
|
|
// $fuel = strtoupper((string)$row[4]);
|
|
// $brokerName = trim((string)$row[6]);
|
|
// $brokerExcelComp = null;
|
|
// $brokerExcelTp = $extractNumeric($row[7]);
|
|
// $brokerExcelOd = null;
|
|
// }
|
|
|
|
// // OD logic (if COMP contains an OD value)
|
|
// if ($comp !== null && stripos((string)$row[3], 'OD') !== false && $tp === null) {
|
|
// $od = $comp; $comp = null;
|
|
// $brokerExcelOd = $brokerExcelComp; $brokerExcelComp = null;
|
|
// }
|
|
|
|
// $gridRow = [
|
|
// 'partner_agent_incentive_file_id' => $fileId, // STORE THE FILE ID HERE
|
|
// 'vehicle_type' => $currentVehicleType,
|
|
// 'fuel' => $fuel,
|
|
// 'insurer' => $insurer,
|
|
// 'rto' => $rto,
|
|
// 'broker_name' => $brokerName,
|
|
// 'segment' => $segment,
|
|
// 'comp' => $comp,
|
|
// 'tp' => $tp,
|
|
// 'od' => $od,
|
|
// 'remarks' => trim((string)($row[5] ?? '')),
|
|
// 'broker_comp' => $brokerExcelComp,
|
|
// 'broker_tp' => $brokerExcelTp,
|
|
// 'broker_od' => $brokerExcelOd,
|
|
// ];
|
|
|
|
|
|
// $gridRow['created_by'] = $createdBy;
|
|
// $this->PayoutGridModel->insert($gridRow);
|
|
// $insertedCount++;
|
|
|
|
// }
|
|
|
|
// return $this->respond([
|
|
// 'status' => 'success',
|
|
// 'code' => 200,
|
|
// 'data' => [
|
|
// 'file_id' => $fileId,
|
|
// 'inserted' => $insertedCount,
|
|
// 'message' => "Processed: {$insertedCount} new",
|
|
// ],
|
|
// ], 200);
|
|
|
|
// } catch (\Exception $e) {
|
|
// return $this->respond(['status' => 'failed', 'code' => 500, 'data' => $e->getMessage()], 500);
|
|
// }
|
|
// }
|
|
|
|
// -------------------------------------------------------------------------
|
|
// List all grid records (optional utility endpoint)
|
|
// -------------------------------------------------------------------------
|
|
// GET /grid?role=Manager means all list (optional insurer, rto, segment, vehicle_type)
|
|
// GET /grid?role=Manager means all list (optional insurer, rto, segment, vehicle_type)
|
|
public function getGridData()
|
|
{
|
|
try {
|
|
$request = $this->request;
|
|
$role = $request->getGet('role');
|
|
$file_id = $request->getGet('file_id');
|
|
|
|
// 1. Role is required
|
|
if (!$role) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 400,
|
|
'data' => 'Role is required to fetch payout data.'
|
|
], 400);
|
|
}
|
|
|
|
// 2. Agent requires logged_id
|
|
if (strtolower(trim((string) $role)) === 'agent') {
|
|
$agentId = (int) trim((string) ($request->getGet('logged_id') ?? $request->getGet('agent_id') ?? 0));
|
|
if ($agentId <= 0) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 400,
|
|
'data' => 'logged_id is required for Agent role.'
|
|
], 400);
|
|
}
|
|
}
|
|
|
|
// 3. Extract Filters
|
|
$insurer = trim((string) ($request->getGet('insurer') ?? ''));
|
|
$rto = trim((string) ($request->getGet('rto') ?? ''));
|
|
$segment = trim((string) ($request->getGet('segment') ?? ''));
|
|
$vehicle_type = trim((string) ($request->getGet('vehicle_type') ?? ''));
|
|
$search = trim((string) ($request->getGet('search') ?? ''));
|
|
|
|
// ── 4. Resolve file_id ────────────────────────────────────────────────
|
|
$db = \Config\Database::connect();
|
|
|
|
if (!empty($file_id)) {
|
|
// If file_id is explicitly passed (any role), use it directly
|
|
$resolvedFileId = (int) $file_id;
|
|
} else {
|
|
// No file_id supplied — resolve latest from table (all roles)
|
|
$fileRow = $db->table('partner_agent_incentive_file')
|
|
->select('id')
|
|
->orderBy('id', 'DESC')
|
|
->limit(1)
|
|
->get()
|
|
->getRowArray();
|
|
|
|
if (empty($fileRow)) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 404,
|
|
'data' => 'No incentive file found.'
|
|
], 404);
|
|
}
|
|
|
|
$resolvedFileId = (int) $fileRow['id'];
|
|
}
|
|
|
|
// ── 5. Build Grid Query ───────────────────────────────────────────────
|
|
$builder = $this->PayoutGridModel->builder();
|
|
$builder->where('partner_agent_incentive_file_id', $resolvedFileId);
|
|
$builder->orderBy('vehicle_type', 'ASC')->orderBy('insurer', 'ASC');
|
|
|
|
// Apply filters
|
|
if ($insurer !== '') {
|
|
$builder->where('insurer', $insurer);
|
|
}
|
|
if ($rto !== '') {
|
|
$builder->where('rto', $rto);
|
|
}
|
|
if ($segment !== '') {
|
|
$builder->where('segment', $segment);
|
|
}
|
|
if ($vehicle_type !== '') {
|
|
$builder->where('vehicle_type', $vehicle_type);
|
|
}
|
|
if ($search !== '') {
|
|
$builder->groupStart()
|
|
->like('insurer', $search)
|
|
->orLike('vehicle_type', $search)
|
|
->orLike('segment', $search)
|
|
->orLike('rto', $search)
|
|
->orLike('remarks', $search)
|
|
->groupEnd();
|
|
}
|
|
|
|
if (in_array($role, ['Manager', 'Accounts'], true)) {
|
|
$builder->select('id, insurer, vehicle_type, segment, rto, comp, tp, od, fuel,
|
|
broker_name, remarks, broker_comp, broker_tp, broker_od,
|
|
created_by, created_at, updated_by, updated_at');
|
|
} else {
|
|
// Agent
|
|
$builder->select('id, insurer, vehicle_type, segment, rto, comp, tp, od, fuel,
|
|
broker_name, remarks, broker_comp, broker_tp, broker_od,
|
|
partner_agent_incentive_file_id, created_at, updated_at', false);
|
|
}
|
|
|
|
// ── 6. Execute ────────────────────────────────────────────────────────
|
|
$gridResults = $builder->get()->getResultArray();
|
|
|
|
if (empty($gridResults)) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 404,
|
|
'data' => 'No data found for the given file_id.'
|
|
], 404);
|
|
}
|
|
|
|
// ── 7. Role-based post-processing ─────────────────────────────────────
|
|
if (in_array($role, ['Manager', 'Accounts'], true)) {
|
|
$gridResults = PartnerPayoutGridRetention::applyManagerAccountsDisplayToRows($gridResults);
|
|
}
|
|
|
|
if (strtolower(trim((string) $role)) === 'agent') {
|
|
$agentId = (int) trim((string) ($request->getGet('logged_id') ?? $request->getGet('agent_id') ?? 0));
|
|
|
|
if ($agentId > 0) {
|
|
$gridResults = PartnerPayoutGridRetention::applyToRows($gridResults, $agentId, $db);
|
|
} else {
|
|
foreach ($gridResults as &$r) {
|
|
$r['oa'] = $r['comp'] ?? null;
|
|
$r['ob'] = $r['tp'] ?? null;
|
|
$r['oc'] = $r['od'] ?? null;
|
|
$r['partner_VT'] = '-';
|
|
$r['partner_RR'] = '-';
|
|
$r['comp'] = '-';
|
|
$r['tp'] = '-';
|
|
$r['od'] = '-';
|
|
}
|
|
unset($r);
|
|
}
|
|
}
|
|
|
|
// ── 8. Respond ────────────────────────────────────────────────────────
|
|
return $this->respond([
|
|
'status' => 'success',
|
|
'code' => 200,
|
|
'data' => [
|
|
'id' => $resolvedFileId,
|
|
'grid' => $gridResults,
|
|
'rtos' => $this->getUniqueColumnValues('rto', $resolvedFileId),
|
|
'segments' => $this->getUniqueColumnValues('segment', $resolvedFileId),
|
|
'vehicle_types' => $this->getUniqueColumnValues('vehicle_type', $resolvedFileId),
|
|
'insurers' => $this->getUniqueColumnValues('insurer', $resolvedFileId),
|
|
]
|
|
], 200);
|
|
|
|
} catch (\Exception $e) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 500,
|
|
'data' => $e->getMessage()
|
|
], 500);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Helper function to fetch unique non-empty values for a column
|
|
*/
|
|
private function getUniqueColumnValues($column, ?int $fileId = null)
|
|
{
|
|
$builder = $this->PayoutGridModel->select($column)
|
|
->distinct()
|
|
->where("$column IS NOT NULL")
|
|
->where("$column !=", '')
|
|
->orderBy($column, 'ASC');
|
|
|
|
if (!empty($fileId)) {
|
|
$builder->where('partner_agent_incentive_file_id', (int) $fileId);
|
|
}
|
|
|
|
$results = $builder->findAll();
|
|
|
|
return array_column($results, $column);
|
|
}
|
|
|
|
// -------------------------------------------------------------------------
|
|
// Update grid row by id
|
|
// Route: POST /agent/updateGrid
|
|
// -------------------------------------------------------------------------
|
|
public function updateGrid()
|
|
{
|
|
try {
|
|
$jsonData = (array) ($this->request->getJSON(true) ?? []);
|
|
$postData = $this->request->getPost() ?? [];
|
|
$data = !empty($jsonData) ? $jsonData : $postData;
|
|
|
|
$id = isset($data['id']) ? (int) $data['id'] : 0;
|
|
if ($id <= 0) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 400,
|
|
'data' => 'id is required.',
|
|
], 200);
|
|
}
|
|
|
|
$existing = $this->PayoutGridModel->find($id);
|
|
if (empty($existing)) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 404,
|
|
'data' => 'Grid record not found.',
|
|
], 200);
|
|
}
|
|
|
|
$updateData = [
|
|
'vehicle_type' => array_key_exists('vehicle_type', $data) ? $data['vehicle_type'] : null,
|
|
'fuel' => array_key_exists('fuel', $data) ? $data['fuel'] : null,
|
|
'rto' => array_key_exists('rto', $data) ? $data['rto'] : null,
|
|
'segment' => array_key_exists('segment', $data) ? $data['segment'] : null,
|
|
'broker_name' => array_key_exists('broker_name', $data) ? $data['broker_name'] : null,
|
|
'comp' => array_key_exists('comp', $data) ? $data['comp'] : null,
|
|
'tp' => array_key_exists('tp', $data) ? $data['tp'] : null,
|
|
'od' => array_key_exists('od', $data) ? $data['od'] : null,
|
|
'broker_comp' => array_key_exists('broker_comp', $data) ? $data['broker_comp'] : null,
|
|
'broker_tp' => array_key_exists('broker_tp', $data) ? $data['broker_tp'] : null,
|
|
'broker_od' => array_key_exists('broker_od', $data) ? $data['broker_od'] : null,
|
|
|
|
'remarks' => array_key_exists('remarks', $data) ? $data['remarks'] : null,
|
|
];
|
|
|
|
$hasAnyField = false;
|
|
foreach (array_keys($updateData) as $field) {
|
|
if (array_key_exists($field, $data)) {
|
|
$hasAnyField = true;
|
|
break;
|
|
}
|
|
}
|
|
|
|
if (!$hasAnyField) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 400,
|
|
'data' => 'No update fields provided.',
|
|
], 200);
|
|
}
|
|
|
|
if (array_key_exists('updated_by', $data)) {
|
|
$updateData['updated_by'] = $data['updated_by'];
|
|
}
|
|
|
|
$this->PayoutGridModel->updateGridById($id, $updateData);
|
|
$updated = $this->PayoutGridModel->find($id);
|
|
|
|
return $this->respond([
|
|
'status' => 'success',
|
|
'code' => 200,
|
|
'data' => $updated,
|
|
], 200);
|
|
} catch (\Exception $e) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 500,
|
|
'data' => $e->getMessage(),
|
|
], 500);
|
|
}
|
|
}
|
|
|
|
|
|
// -------------------------------------------------------------------------
|
|
// 2. Download Filtered Grid in Excel
|
|
// Route: $routes->get('agent/downloadGrid', 'AgentIncentiveController::downloadGridInExcel');
|
|
// Purpose: Applies the exact same filters as getGridData/(:num)(), but instead
|
|
// of returning JSON, it generates and downloads an Excel file.
|
|
// -------------------------------------------------------------------------
|
|
public function downloadGridInExcel()
|
|
{
|
|
try {
|
|
$request = $this->request;
|
|
|
|
// Get GET parameters for filtering
|
|
$role = $request->getGet('role');
|
|
$insurer = $request->getGet('insurer');
|
|
$rto = $request->getGet('rto');
|
|
$segment = $request->getGet('segment');
|
|
$vehicle_type = $request->getGet('vehicle_type');
|
|
|
|
$builder = $this->PayoutGridModel->builder();
|
|
$builder->orderBy('vehicle_type', 'ASC')->orderBy('insurer', 'ASC');
|
|
|
|
// Apply filters if the user is a Manager or Accounts role
|
|
if (in_array($role, ['Manager', 'Accounts'])) {
|
|
if (!empty($insurer)) $builder->where('insurer', $insurer);
|
|
if (!empty($rto)) $builder->where('rto', $rto);
|
|
if (!empty($segment)) $builder->where('segment', $segment);
|
|
if (!empty($vehicle_type)) $builder->where('vehicle_type', $vehicle_type);
|
|
}
|
|
|
|
// Fetch the filtered records
|
|
$records = $builder->get()->getResultArray();
|
|
|
|
if (empty($records)) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 404,
|
|
'data' => 'No records found to export based on your filters.'
|
|
], 404);
|
|
}
|
|
|
|
// --- Start Excel Generation ---
|
|
$spreadsheet = new Spreadsheet();
|
|
$sheet = $spreadsheet->getActiveSheet();
|
|
|
|
// Setup Header Row
|
|
$sheet->setCellValue('A1', 'Vehicle Type');
|
|
$sheet->setCellValue('B1', 'Insurer');
|
|
$sheet->setCellValue('C1', 'RTO');
|
|
$sheet->setCellValue('D1', 'Segment');
|
|
$sheet->setCellValue('E1', 'Broker Name'); // Changed from 'ID' for clarity
|
|
$sheet->setCellValue('F1', 'Comp');
|
|
$sheet->setCellValue('G1', 'TP');
|
|
$sheet->setCellValue('H1', 'OD');
|
|
|
|
// Populate Excel Rows
|
|
$rowNumber = 2; // Start on row 2 (row 1 is headers)
|
|
foreach ($records as $row) {
|
|
$sheet->setCellValue('A' . $rowNumber, $row['vehicle_type']);
|
|
$sheet->setCellValue('B' . $rowNumber, $row['insurer']);
|
|
$sheet->setCellValue('C' . $rowNumber, $row['rto']);
|
|
$sheet->setCellValue('D' . $rowNumber, $row['segment']);
|
|
$sheet->setCellValue('E' . $rowNumber, $row['broker_name']);
|
|
$sheet->setCellValue('F' . $rowNumber, $row['comp']);
|
|
$sheet->setCellValue('G' . $rowNumber, $row['tp']);
|
|
$sheet->setCellValue('H' . $rowNumber, $row['od']);
|
|
$rowNumber++;
|
|
}
|
|
|
|
// Auto-size columns for better readability
|
|
foreach (range('A', $sheet->getHighestColumn()) as $columnID) {
|
|
$sheet->getColumnDimension($columnID)->setAutoSize(true);
|
|
}
|
|
|
|
// Write the file to a temporary location
|
|
$writer = new Xlsx($spreadsheet);
|
|
$fileName = 'Filtered_Payout_Grid_' . date('Y-m-d_H-i') . '.xlsx';
|
|
$tempFile = tempnam(sys_get_temp_dir(), 'grid_export');
|
|
$writer->save($tempFile);
|
|
|
|
// Return the file as a direct download response
|
|
return $this->response->download($tempFile, null)->setFileName($fileName);
|
|
|
|
} catch (\Exception $e) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 500,
|
|
'data' => 'Failed to generate Excel file: ' . $e->getMessage()
|
|
], 500);
|
|
}
|
|
}
|
|
|
|
}
|