3274 lines
148 KiB
PHP
3274 lines
148 KiB
PHP
<?php
|
||
|
||
namespace App\Controllers;
|
||
|
||
use App\Libraries\PartnerPayoutGridRetention;
|
||
use CodeIgniter\RESTful\ResourceController;
|
||
|
||
use PhpOffice\PhpSpreadsheet\Spreadsheet;
|
||
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
|
||
use PhpOffice\PhpSpreadsheet\IOFactory;
|
||
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
|
||
use PhpOffice\PhpSpreadsheet\Style\Border;
|
||
use PhpOffice\PhpSpreadsheet\Cell\Coordinate; // <--- THIS MUST BE HERE
|
||
use PhpOffice\PhpSpreadsheet\Style\Alignment;
|
||
|
||
class ExcelExportController extends ResourceController
|
||
{
|
||
protected $db;
|
||
|
||
public function __construct()
|
||
{
|
||
$this->db = \Config\Database::connect();
|
||
}
|
||
|
||
|
||
/**
|
||
* Centralized function to create the spreadsheet, apply styling,
|
||
* set headers, and stream the file output.
|
||
*
|
||
* @param array $header Column headers (1D array)
|
||
* @param array $data Policy data including the total row (2D array)
|
||
* @param string $title Spreadsheet title
|
||
* @param string $fileName Output file name
|
||
* @param string $totalLabelCol Column letter for the 'TOTAL:' label (e.g., 'C' or 'E')
|
||
* @param string $totalValueCol Column letter for the total value (e.g., 'D' or 'F')
|
||
* @return void
|
||
*/
|
||
private function streamExcelFile(array $header, array $data, string $title, string $fileName, bool $showTotal = true): void
|
||
{
|
||
while (ob_get_level() > 0) {
|
||
ob_end_clean();
|
||
}
|
||
|
||
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
|
||
$sheet = $spreadsheet->getActiveSheet();
|
||
|
||
$columnCount = count($header);
|
||
$lastCol = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($columnCount);
|
||
$rowCount = count($data);
|
||
|
||
// 1. Title
|
||
$sheet->mergeCells("A1:{$lastCol}1");
|
||
$sheet->setCellValue('A1', $title);
|
||
$sheet->getStyle('A1')->getFont()->setBold(true)->setSize(14);
|
||
$sheet->getStyle('A1')->getAlignment()->setHorizontal('center');
|
||
|
||
// 2. Headers
|
||
$sheet->fromArray($header, NULL, 'A2');
|
||
$sheet->getStyle("A2:{$lastCol}2")->getFont()->setBold(true);
|
||
$sheet->getStyle("A2:{$lastCol}2")->getFill()
|
||
->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
|
||
->getStartColor()->setARGB('F2F2F2');
|
||
|
||
// 3. Data
|
||
$sheet->fromArray($data, NULL, 'A3');
|
||
|
||
// 4. Dynamic Total Row Styling
|
||
if ($rowCount > 0 && $showTotal === true) {
|
||
$totalRowNumber = $rowCount + 2;
|
||
|
||
// Bold the entire last row
|
||
$sheet->getStyle("A{$totalRowNumber}:{$lastCol}{$totalRowNumber}")->getFont()->setBold(true);
|
||
|
||
// Find the second to last column letter for "Total:" alignment
|
||
$labelColLetter = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($columnCount - 1);
|
||
|
||
// Right-align the "Total:" label (usually second to last column)
|
||
$sheet->getStyle($labelColLetter . $totalRowNumber)->getAlignment()->setHorizontal('right');
|
||
|
||
// Format the last column (the sum) as a number
|
||
$sheet->getStyle($lastCol . $totalRowNumber)->getNumberFormat()->setFormatCode('#,##0.00');
|
||
/**
|
||
* ACCOUNTING FORMAT:
|
||
* The "_-₹*" part tells Excel to put the ₹ on the left and
|
||
* the "*" creates a gap that pushes the numbers to the right.
|
||
*/
|
||
$accountingFormat = '_-₹* #,##0.00_-;_-₹* -#,##0.00_-;_-₹* "-"??_-;_-@_-';
|
||
|
||
$sheet->getStyle("{$lastCol}3:{$lastCol}{$totalRowNumber}")
|
||
->getNumberFormat()
|
||
->setFormatCode($accountingFormat);
|
||
|
||
// 4. Force Right Alignment for the numeric part
|
||
$sheet->getStyle("{$lastCol}3:{$lastCol}{$totalRowNumber}")
|
||
->getAlignment()
|
||
->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
|
||
|
||
}
|
||
|
||
|
||
// 5. Auto-size
|
||
for ($i = 1; $i <= $columnCount; $i++) {
|
||
$col = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($i);
|
||
$sheet->getColumnDimension($col)->setAutoSize(true);
|
||
}
|
||
|
||
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
|
||
header("Content-Disposition: attachment; filename=\"{$fileName}\"");
|
||
header("Cache-Control: max-age=0");
|
||
|
||
$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
|
||
$writer->save('php://output');
|
||
exit();
|
||
}
|
||
|
||
// 1) Broker generate the Excel file
|
||
public function downloadExcelBroker()
|
||
{
|
||
try{
|
||
$managerId = $this->request->getGet('manager_id');
|
||
$brokerId = $this->request->getGet('broker_id');
|
||
$month = $this->request->getGet('month');
|
||
|
||
if (empty($managerId) || empty($brokerId) || empty($month)) {
|
||
|
||
$msg = "Missing: ";
|
||
$msg .= empty($managerId) ? "manager_id " : "";
|
||
$msg .= empty($brokerId) ? "broker_id " : "";
|
||
$msg .= empty($month) ? "month " : "";
|
||
$ref = ['debug_msg' => $msg];
|
||
// $ref = ['debug_msg' => 'Missing required parameters: manager_id, broker_id, and month must be provided.'];
|
||
return $this->response->setJSON(['status' => 'error','code' => 400,'data' => [],'message' => 'Missing required parameters','ref' => $ref])->setStatusCode(200);
|
||
}
|
||
|
||
$data = $this->getDataBroker($managerId, $brokerId, $month);
|
||
if (empty($data)) {
|
||
throw new \RuntimeException('No policies found...', 404);
|
||
}
|
||
$lastIndex = count(reset($data)) - 1;
|
||
// usort($data, function($a, $b) use ($lastIndex) {
|
||
// return (float)$a[$lastIndex] <=> (float)$b[$lastIndex];
|
||
// });
|
||
usort($data, function($a, $b) use ($lastIndex) {
|
||
return (float)$b[$lastIndex] <=> (float)$a[$lastIndex];
|
||
});
|
||
$finalData = [];
|
||
$serialNo = 1;
|
||
$totalSum = 0;
|
||
|
||
foreach ($data as $row) {
|
||
$indexedRow = array_values($row);
|
||
$lastIndex = count($indexedRow) - 1;
|
||
$totalSum += (float) $indexedRow[$lastIndex];
|
||
unset($indexedRow[$lastIndex]);
|
||
$finalData[] = array_merge([$serialNo++], $indexedRow);
|
||
}
|
||
// foreach ($data as $row) {
|
||
// $finalData[] = array_merge([$serialNo++], $row);
|
||
// }
|
||
|
||
$header = ['S.No',
|
||
'Received Date', 'Assigned To',
|
||
'Partner Name' , 'Partner Code',
|
||
'Insurer Name' ,'Insurer Short Name',
|
||
'Insured Name', 'Vehicle Number',
|
||
'Payment Mode', 'Plan Type',
|
||
'Broker Name', 'Issue Date', 'Policy Number', 'Premium Amount'];
|
||
|
||
|
||
|
||
// Calculate and append total
|
||
$lastColIndex = count($header) - 1; // Get index of the last column (Premium)
|
||
// $grandTotal = array_sum(array_column($finalData, $lastColIndex));
|
||
|
||
$totalRow = array_fill(0, count($header), ''); // Create empty row of same width
|
||
$totalRow[$lastColIndex - 1] = 'Total:'; // Put label in 2nd to last
|
||
$totalRow[$lastColIndex] = $totalSum;
|
||
$finalData[] = $totalRow;
|
||
|
||
$monthName = ($month === 'current') ? date('M_Y') : date('M_Y', strtotime('-1 month'));
|
||
$brokerName = $finalData[0][11] ?? '';
|
||
|
||
$fileName = "Broker_Policies_Details_{$monthName}.xlsx";
|
||
$titleName = str_replace(['_', '.xlsx'], [' ', ''], $fileName);
|
||
|
||
|
||
// --- CALL CENTRALIZED FUNCTION ---
|
||
$this->streamExcelFile(
|
||
$header,
|
||
$finalData,
|
||
$titleName,
|
||
$fileName,
|
||
true // Pass 'true' to show/bold the total row
|
||
);
|
||
|
||
|
||
return;
|
||
|
||
} catch (\Throwable $e) {
|
||
|
||
// ... (Your error handling remains the same) ...
|
||
$ref = [];
|
||
// Case 1: No Data Found (Soft Success JSON)
|
||
if ($e instanceof \RuntimeException && $e->getCode() === 404) {
|
||
$ref['debug_msg'] = 'No Data Found';
|
||
return $this->respond([
|
||
'status' => 'success',
|
||
'code' => 200,
|
||
'data' => [],
|
||
'message' => 'No Data Found',
|
||
'ref' => $ref
|
||
], 200);
|
||
}
|
||
|
||
// Case 2: Database or Other Error (Error JSON)
|
||
$isDbError = $e instanceof \CodeIgniter\Database\Exceptions\DatabaseException
|
||
|| $e instanceof \mysqli_sql_exception
|
||
|| $e instanceof \PDOException;
|
||
|
||
if ($isDbError) {
|
||
$ref['debug_info'] = $e->getFile() . " / LN : " . $e->getLine();
|
||
$ref['debug_msg'] = 'Database Error Occurred: ' . $e->getMessage();
|
||
$message = 'Database Error Occurred.';
|
||
} else {
|
||
$ref['debug_msg'] = 'An unexpected error occurred: ' . $e->getMessage();
|
||
$message = 'Something went wrong. Please try again later.';
|
||
}
|
||
|
||
return $this->respond([
|
||
'status' => 'error',
|
||
'code' => 500,
|
||
'data' => [],
|
||
'message'=> $message,
|
||
'ref' => $ref
|
||
], 200);
|
||
}
|
||
}
|
||
|
||
// 2) Product generate the Excel file
|
||
public function downloadExcelProduct()
|
||
{
|
||
try{
|
||
$managerId = $this->request->getGet('manager_id');
|
||
$vehicleType = $this->request->getGet('vehicle_type');
|
||
$month = $this->request->getGet('month');
|
||
|
||
if (empty($managerId) || empty($vehicleType) || empty($month)) {
|
||
|
||
|
||
$msg = "Missing: ";
|
||
$msg .= empty($managerId) ? "manager_id " : "";
|
||
$msg .= empty($brokerId) ? "broker_id " : "";
|
||
$msg .= empty($month) ? "month " : "";
|
||
$ref = ['debug_msg' => $msg];
|
||
// $ref = ['debug_msg' => 'Missing required parameters: manager_id, broker_id, and month must be provided.'];
|
||
return $this->response->setJSON(['status' => 'error','code' => 400,'data' => [],'message' => 'Missing required parameters','ref' => $ref])->setStatusCode(200);
|
||
|
||
}
|
||
|
||
$data = $this->getDataProduct($managerId, $vehicleType, $month);
|
||
if (empty($data)) {
|
||
throw new \RuntimeException('No policies found for this vehicle type...', 404);
|
||
}
|
||
$lastIndex = count(reset($data)) - 1;
|
||
// usort($data, function($a, $b) use ($lastIndex) {
|
||
// return (float)$a[$lastIndex] <=> (float)$b[$lastIndex];
|
||
// });
|
||
usort($data, function($a, $b) use ($lastIndex) {
|
||
return (float)$b[$lastIndex] <=> (float)$a[$lastIndex];
|
||
});
|
||
$finalData = [];
|
||
$serialNo = 1;
|
||
$totalSum = 0;
|
||
|
||
foreach ($data as $row) {
|
||
$indexedRow = array_values($row);
|
||
$lastIndex = count($indexedRow) - 1;
|
||
$totalSum += (float) $indexedRow[$lastIndex];
|
||
unset($indexedRow[$lastIndex]);
|
||
$finalData[] = array_merge([$serialNo++], $indexedRow);
|
||
}
|
||
// foreach ($data as $row) {
|
||
// $finalData[] = array_merge([$serialNo++], $row);
|
||
// }
|
||
// $header = ['Vehicle Type', 'Issue Date', 'Policy Number', 'Premium Amount'];
|
||
$header = ['S.No',
|
||
'Received Date', 'Assigned To',
|
||
'Partner Name' , 'Partner Code',
|
||
'Insurer Name' ,'Insurer Short Name',
|
||
'Insured Name', 'Vehicle Number',
|
||
'Payment Mode', 'Plan Type',
|
||
'Vehicle Type', 'Issue Date', 'Policy Number', 'Premium Amount'];
|
||
|
||
|
||
|
||
// Calculate and append total
|
||
$lastColIndex = count($header) - 1; // Get index of the last column (Premium)
|
||
// $grandTotal = array_sum(array_column($finalData, $lastColIndex));
|
||
$fmt = new \NumberFormatter('en_IN', \NumberFormatter::CURRENCY);
|
||
$formattedGrandTotal = $fmt->formatCurrency($totalSum, "INR");
|
||
|
||
$totalRow = array_fill(0, count($header), ''); // Create empty row of same width
|
||
$totalRow[$lastColIndex - 1] = 'Total:'; // Put label in 2nd to last
|
||
$totalRow[$lastColIndex] = $totalSum;
|
||
$finalData[] = $totalRow;
|
||
|
||
$monthName = ($month === 'current') ? date('M_Y') : date('M_Y', strtotime('-1 month'));
|
||
$vehicleType = $finalData[0][11] ?? '';
|
||
|
||
|
||
$fileName = "Product_Policies_Details_{$monthName}.xlsx";
|
||
$titleName = str_replace(['_', '.xlsx'], [' ', ''], $fileName);
|
||
|
||
// --- CALL CENTRALIZED FUNCTION ---
|
||
$this->streamExcelFile(
|
||
$header,
|
||
$finalData,
|
||
$titleName,
|
||
$fileName,
|
||
true // This will ignore the bold/alignment logic for the last row
|
||
);
|
||
return;
|
||
} catch (\Throwable $e) {
|
||
|
||
// ... (Your error handling remains the same) ...
|
||
$ref = [];
|
||
// Case 1: No Data Found (Soft Success JSON)
|
||
if ($e instanceof \RuntimeException && $e->getCode() === 404) {
|
||
$ref['debug_msg'] = 'No Data Found';
|
||
return $this->respond([
|
||
'status' => 'success',
|
||
'code' => 200,
|
||
'data' => [],
|
||
'message' => 'No Data Found',
|
||
'ref' => $ref
|
||
], 200);
|
||
}
|
||
|
||
// Case 2: Database or Other Error (Error JSON)
|
||
$isDbError = $e instanceof \CodeIgniter\Database\Exceptions\DatabaseException
|
||
|| $e instanceof \mysqli_sql_exception
|
||
|| $e instanceof \PDOException;
|
||
|
||
if ($isDbError) {
|
||
$ref['debug_info'] = $e->getFile() . " / LN : " . $e->getLine();
|
||
$ref['debug_msg'] = 'Database Error Occurred: ' . $e->getMessage();
|
||
$message = 'Database Error Occurred.';
|
||
} else {
|
||
$ref['debug_msg'] = 'An unexpected error occurred: ' . $e->getMessage();
|
||
$message = 'Something went wrong. Please try again later.';
|
||
}
|
||
|
||
return $this->respond([
|
||
'status' => 'error',
|
||
'code' => 500,
|
||
'data' => [],
|
||
'message'=> $message,
|
||
'ref' => $ref
|
||
], 200);
|
||
}
|
||
}
|
||
|
||
// 3) Insurer generate the Excel file
|
||
public function downloadExcelInsurer()
|
||
{
|
||
try{
|
||
$managerId = $this->request->getGet('manager_id');
|
||
$insurerId = $this->request->getGet('insurer_id');
|
||
$month = $this->request->getGet('month');
|
||
|
||
// --- ADDED VALIDATION (Simplified logic for clarity) ---
|
||
if (empty($managerId) || empty($insurerId) || empty($month)) {
|
||
$msg = "Missing: ";
|
||
$msg .= empty($managerId) ? "manager_id " : "";
|
||
$msg .= empty($insurerId) ? "insurer_id " : "";
|
||
$msg .= empty($month) ? "month " : "";
|
||
$ref = ['debug_msg' => $msg];
|
||
// Status code remains 200, but JSON code is 400 as per your preference
|
||
return $this->response->setJSON([
|
||
'status' => 'error',
|
||
'code' => 400,
|
||
'data' => [],
|
||
'message' => 'Missing required parameters',
|
||
'ref' => $ref
|
||
])->setStatusCode(200);
|
||
}
|
||
|
||
$data = $this->getDataInsurer($managerId, $insurerId, $month);
|
||
|
||
// --- 1. MODIFIED LOGIC: Throw RuntimeException if no data ---
|
||
if (empty($data)) {
|
||
throw new \RuntimeException('No policies Details found for this insurer.', 404);
|
||
}
|
||
$lastIndex = count(reset($data)) - 1;
|
||
// usort($data, function($a, $b) use ($lastIndex) {
|
||
// return (float)$a[$lastIndex] <=> (float)$b[$lastIndex];
|
||
// });
|
||
usort($data, function($a, $b) use ($lastIndex) {
|
||
return (float)$b[$lastIndex] <=> (float)$a[$lastIndex];
|
||
});
|
||
$finalData = [];
|
||
$serialNo = 1;
|
||
$totalSum = 0;
|
||
|
||
foreach ($data as $row) {
|
||
$indexedRow = array_values($row);
|
||
$lastIndex = count($indexedRow) - 1;
|
||
$totalSum += (float) $indexedRow[$lastIndex];
|
||
unset($indexedRow[$lastIndex]);
|
||
$finalData[] = array_merge([$serialNo++], $indexedRow);
|
||
}
|
||
// foreach ($data as $row) {
|
||
// $finalData[] = array_merge([$serialNo++], $row);
|
||
// }
|
||
|
||
|
||
// Define column headers
|
||
$header = ['S.No',
|
||
'Received Date', 'Assigned To',
|
||
'Partner Name' , 'Partner Code',
|
||
'Vehicle Number',
|
||
'Payment Mode', 'Plan Type',
|
||
'Insurer Name', 'Insurer Short Name','Insured Name', 'Policy Issued Date','Policy Number', 'Premium Amount'];
|
||
|
||
|
||
// Calculate and append total
|
||
$lastColIndex = count($header) - 1; // Get index of the last column (Premium)
|
||
// $grandTotal = array_sum(array_column($finalData, $lastColIndex));
|
||
$fmt = new \NumberFormatter('en_IN', \NumberFormatter::CURRENCY);
|
||
$formattedGrandTotal = $fmt->formatCurrency($totalSum, "INR");
|
||
|
||
$totalRow = array_fill(0, count($header), ''); // Create empty row of same width
|
||
$totalRow[$lastColIndex - 1] = 'Total:'; // Put label in 2nd to last
|
||
$totalRow[$lastColIndex] = $totalSum;
|
||
$finalData[] = $totalRow;
|
||
|
||
$monthName = ($month === 'current') ? date('M_Y') : date('M_Y', strtotime('-1 month'));
|
||
$InsurerName = $finalData[0][8] ?? "" ;
|
||
|
||
|
||
$fileName = "Insurer_Policies_Details_{$monthName}.xlsx";
|
||
$titleName = str_replace(['_', '.xlsx'], [' ', ''], $fileName);
|
||
|
||
// --- CALL CENTRALIZED FUNCTION ---
|
||
$this->streamExcelFile(
|
||
$header,
|
||
$finalData,
|
||
$titleName,
|
||
$fileName,
|
||
true // This will ignore the bold/alignment logic for the last row
|
||
);
|
||
|
||
return; // STOP CI4 rendering anything else
|
||
|
||
} catch (\Throwable $e) {
|
||
|
||
// ... (Your error handling remains the same) ...
|
||
$ref = [];
|
||
// Case 1: No Data Found (Soft Success JSON)
|
||
if ($e instanceof \RuntimeException && $e->getCode() === 404) {
|
||
$ref['debug_msg'] = 'No Data Found';
|
||
return $this->respond([
|
||
'status' => 'success',
|
||
'code' => 200,
|
||
'data' => [],
|
||
'message' => 'No Data Found',
|
||
'ref' => $ref
|
||
], 200);
|
||
}
|
||
|
||
// Case 2: Database or Other Error (Error JSON)
|
||
$isDbError = $e instanceof \CodeIgniter\Database\Exceptions\DatabaseException
|
||
|| $e instanceof \mysqli_sql_exception
|
||
|| $e instanceof \PDOException;
|
||
|
||
if ($isDbError) {
|
||
$ref['debug_info'] = $e->getFile() . " / LN : " . $e->getLine();
|
||
$ref['debug_msg'] = 'Database Error Occurred: ' . $e->getMessage();
|
||
$message = 'Database Error Occurred.';
|
||
} else {
|
||
$ref['debug_msg'] = 'An unexpected error occurred: ' . $e->getMessage();
|
||
$message = 'Something went wrong. Please try again later.';
|
||
}
|
||
|
||
return $this->respond([
|
||
'status' => 'error',
|
||
'code' => 500,
|
||
'data' => [],
|
||
'message'=> $message,
|
||
'ref' => $ref
|
||
], 200);
|
||
}
|
||
}
|
||
|
||
// 4) LowPremium generate the Excel file (Below 5OK)
|
||
public function downloadExcelLowPremium()
|
||
{
|
||
try{
|
||
$managerId = $this->request->getGet('manager_id');
|
||
$from_date = $this->request->getGet('from_date');
|
||
$to_date = $this->request->getGet('to_date');
|
||
|
||
if (empty($managerId)) {
|
||
$msg = "Missing: ";
|
||
$msg .= empty($managerId) ? "manager_id " : "";
|
||
$ref = ['debug_msg' => $msg];
|
||
// $ref = ['debug_msg' => 'Missing required parameters: manager_id, broker_id, and month must be provided.'];
|
||
return $this->response->setJSON(['status' => 'error','code' => 400,'data' => [],'message' => 'Missing required parameters','ref' => $ref])->setStatusCode(200);
|
||
}
|
||
|
||
if (empty($from_date) || empty($to_date)) {
|
||
|
||
// Default: last 10 days
|
||
$fromObj = new \DateTime('-10 days');
|
||
$toObj = new \DateTime();
|
||
|
||
} else {
|
||
|
||
// Convert d-m-Y → DateTime
|
||
$fromObj = \DateTime::createFromFormat('d-m-Y', $from_date);
|
||
$toObj = \DateTime::createFromFormat('d-m-Y', $to_date);
|
||
|
||
if (!$fromObj || !$toObj) {
|
||
return $this->response->setJSON([
|
||
'status' => 'error',
|
||
'message' => 'Invalid date format (expected d-m-Y)',
|
||
]);
|
||
}
|
||
}
|
||
|
||
// For DB query
|
||
$fromDate = $fromObj->format('Y-m-d 00:00:00');
|
||
$toDate = $toObj->format('Y-m-d 23:59:59');
|
||
|
||
$data = $this->getDataLowPremiumAgent($managerId,$fromDate,$toDate);
|
||
if (empty($data)) {
|
||
throw new \RuntimeException('No low premium agents found...', 404);
|
||
}
|
||
|
||
$finalData = [];
|
||
$serialNo = 1;
|
||
$totalSum = 0;
|
||
|
||
foreach ($data as $row) {
|
||
// 1. Convert associative row to indexed array
|
||
$indexedRow = array_values($row);
|
||
|
||
// 2. Add to total sum using the last index (total_premium_amount)
|
||
$lastIndex = count($indexedRow) - 1;
|
||
$totalSum += (float) $indexedRow[$lastIndex];
|
||
|
||
// 3. Remove columns you don't want in the Excel
|
||
unset($indexedRow[$lastIndex]); // Remove total_premium_amount (raw number)
|
||
unset($indexedRow[4]); // Remove last_business_date
|
||
unset($indexedRow[0]); // Remove agent_id
|
||
|
||
// 4. Re-index the array so it's clean (0, 1, 2...)
|
||
$cleanedRow = array_values($indexedRow);
|
||
|
||
// 5. Merge with Serial Number
|
||
$finalData[] = array_merge([$serialNo++], $cleanedRow);
|
||
}
|
||
|
||
$header = ['S.No', 'Partner Code','Partner Name','Partner Status' , 'Premium Amount'];
|
||
|
||
|
||
|
||
// Calculate and append total
|
||
$lastColIndex = count($header) - 1; // Get index of the last column (Premium)
|
||
|
||
$totalRow = array_fill(0, count($header), ''); // Create empty row of same width
|
||
$totalRow[$lastColIndex - 1] = 'Total:'; // Put label in 2nd to last
|
||
$totalRow[$lastColIndex] = $totalSum;
|
||
$finalData[] = $totalRow;
|
||
|
||
// Note: $month variable is undefined, assuming you want current month for filename
|
||
$monthName = date('M_Y');
|
||
$PartnerName = $finalData[0][2] ?? '';
|
||
$fileName = 'Non_Performing_Partner_Business_Less_than_50000_Policy_Details.xlsx';
|
||
$titleName = str_replace(['_', '.xlsx'], [' ', ''], $fileName);
|
||
|
||
// --- CALL CENTRALIZED FUNCTION ---
|
||
$this->streamExcelFile(
|
||
$header,
|
||
$finalData,
|
||
$titleName,
|
||
$fileName,
|
||
true // This will ignore the bold/alignment logic for the last row
|
||
);
|
||
|
||
return;
|
||
} catch (\Throwable $e) {
|
||
|
||
// ... (Your error handling remains the same) ...
|
||
$ref = [];
|
||
// Case 1: No Data Found (Soft Success JSON)
|
||
if ($e instanceof \RuntimeException && $e->getCode() === 404) {
|
||
$ref['debug_msg'] = 'No Data Found';
|
||
return $this->respond([
|
||
'status' => 'success',
|
||
'code' => 200,
|
||
'data' => [],
|
||
'message' => 'No Data Found',
|
||
'ref' => $ref
|
||
], 200);
|
||
}
|
||
|
||
// Case 2: Database or Other Error (Error JSON)
|
||
$isDbError = $e instanceof \CodeIgniter\Database\Exceptions\DatabaseException
|
||
|| $e instanceof \mysqli_sql_exception
|
||
|| $e instanceof \PDOException;
|
||
|
||
if ($isDbError) {
|
||
$ref['debug_info'] = $e->getFile() . " / LN : " . $e->getLine();
|
||
$ref['debug_msg'] = 'Database Error Occurred: ' . $e->getMessage();
|
||
$message = 'Database Error Occurred.';
|
||
} else {
|
||
$ref['debug_msg'] = 'An unexpected error occurred: ' . $e->getMessage();
|
||
$message = 'Something went wrong. Please try again later.';
|
||
}
|
||
|
||
return $this->respond([
|
||
'status' => 'error',
|
||
'code' => 500,
|
||
'data' => [],
|
||
'message'=> $message,
|
||
'ref' => $ref
|
||
], 200);
|
||
}
|
||
}
|
||
|
||
// 5.1) AgentMonthly generate the Excel file
|
||
public function downloadExcelPerformingAgentsByID()
|
||
{
|
||
try{
|
||
$managerId = $this->request->getGet('manager_id');
|
||
$agentId = $this->request->getGet('agent_id');
|
||
$from_date = $this->request->getGet('from_date');
|
||
$to_date = $this->request->getGet('to_date');
|
||
// $month = $this->request->getGet('month');
|
||
|
||
if (empty($managerId) || empty($agentId)) {
|
||
|
||
$msg = "Missing: ";
|
||
$msg .= empty($managerId) ? "manager_id " : "";
|
||
$msg .= empty($agentId) ? "agent_id " : "";
|
||
// $msg .= empty($month) ? "month " : "";
|
||
$ref = ['debug_msg' => $msg];
|
||
return $this->response->setJSON(['status' => 'error','code' => 400,'data' => [],'message' => 'Missing required parameters','ref' => $ref])->setStatusCode(200);
|
||
}
|
||
|
||
if (empty($from_date) || empty($to_date)) {
|
||
|
||
// Default: last 10 days
|
||
$fromObj = new \DateTime('-10 days');
|
||
$toObj = new \DateTime();
|
||
|
||
} else {
|
||
|
||
// Convert d-m-Y → DateTime
|
||
$fromObj = \DateTime::createFromFormat('d-m-Y', $from_date);
|
||
$toObj = \DateTime::createFromFormat('d-m-Y', $to_date);
|
||
|
||
if (!$fromObj || !$toObj) {
|
||
return $this->response->setJSON([
|
||
'status' => 'error',
|
||
'message' => 'Invalid date format (expected d-m-Y)',
|
||
]);
|
||
}
|
||
}
|
||
|
||
// For DB query
|
||
$fromDate = $fromObj->format('Y-m-d 00:00:00');
|
||
$toDate = $toObj->format('Y-m-d 23:59:59');
|
||
|
||
|
||
// Renamed helper function for consistency: getAgentMonthlyPoliciesData
|
||
$data = $this->getDataPerformingAgentsByID($managerId, $agentId,$fromDate,$toDate); // , $month);
|
||
|
||
if (empty($data)) {
|
||
throw new \RuntimeException('No policies found for this agent for the specified month.', 404);
|
||
}
|
||
$lastIndex = count(reset($data)) - 1;
|
||
// usort($data, function($a, $b) use ($lastIndex) {
|
||
// return (float)$a[$lastIndex] <=> (float)$b[$lastIndex];
|
||
// });
|
||
usort($data, function($a, $b) use ($lastIndex) {
|
||
return (float)$b[$lastIndex] <=> (float)$a[$lastIndex];
|
||
});
|
||
|
||
$header = ['S.No',
|
||
'Received Date', 'Assigned To',
|
||
'Insurer Name', 'Insurer Short Name',
|
||
'Insured Name', 'Vehicle Number',
|
||
'Payment Mode', 'Plan Type', 'Issue Date',
|
||
'Partner Code', 'Partner Name',
|
||
'Policy Number', 'Premium Amount'];
|
||
|
||
|
||
$finalData = [];
|
||
$serialNo = 1;
|
||
$totalSum = 0;
|
||
|
||
foreach ($data as $row) {
|
||
$indexedRow = array_values($row);
|
||
$lastIndex = count($indexedRow) - 1;
|
||
$totalSum += (float) $indexedRow[$lastIndex];
|
||
unset($indexedRow[$lastIndex]);
|
||
$finalData[] = array_merge([$serialNo++], $indexedRow);
|
||
}
|
||
// foreach ($data as $row) {
|
||
// $finalData[] = array_merge([$serialNo++], $row);
|
||
// }
|
||
|
||
// Calculate and append total
|
||
$lastColIndex = count($header) - 1; // Get index of the last column (Premium)
|
||
// $grandTotal = array_sum(array_column($finalData, $lastColIndex));
|
||
$fmt = new \NumberFormatter('en_IN', \NumberFormatter::CURRENCY);
|
||
$formattedGrandTotal = $fmt->formatCurrency($totalSum, "INR");
|
||
|
||
$totalRow = array_fill(0, count($header), ''); // Create empty row of same width
|
||
$totalRow[$lastColIndex - 1] = 'Total:'; // Put label in 2nd to last
|
||
$totalRow[$lastColIndex] = $totalSum;
|
||
$finalData[] = $totalRow;
|
||
|
||
// $monthName = ($month === 'current') ? date('M_Y') : date('M_Y', strtotime('-1 month'));
|
||
|
||
$PartnerName = $finalData[0][11] ?? '';
|
||
|
||
$fileName = 'Performing_Partner_Policy_Details.xlsx';
|
||
$titleName = str_replace(['_', '.xlsx'], [' ', ''], $fileName);
|
||
|
||
// --- CALL CENTRALIZED FUNCTION ---
|
||
$this->streamExcelFile(
|
||
$header,
|
||
$finalData,
|
||
$titleName,
|
||
$fileName,
|
||
true // This will ignore the bold/alignment logic for the last row
|
||
);
|
||
|
||
|
||
return;
|
||
} catch (\Throwable $e) {
|
||
|
||
// ... (Your error handling remains the same) ...
|
||
$ref = [];
|
||
// Case 1: No Data Found (Soft Success JSON)
|
||
if ($e instanceof \RuntimeException && $e->getCode() === 404) {
|
||
$ref['message'] = 'No Data Found';
|
||
return $this->respond([
|
||
'status' => 'success',
|
||
'code' => 200,
|
||
'data' => [],
|
||
'message' => 'No Data Found',
|
||
'ref' => $ref
|
||
], 200);
|
||
}
|
||
|
||
// Case 2: Database or Other Error (Error JSON)
|
||
$isDbError = $e instanceof \CodeIgniter\Database\Exceptions\DatabaseException
|
||
|| $e instanceof \mysqli_sql_exception
|
||
|| $e instanceof \PDOException;
|
||
|
||
if ($isDbError) {
|
||
$ref['debug_info'] = $e->getFile() . " / LN : " . $e->getLine();
|
||
$ref['message'] = 'Database Error Occurred.';
|
||
$message = 'Database Error Occurred.';
|
||
} else {
|
||
$ref['message'] = 'An unexpected error occurred: ' . $e->getMessage();
|
||
$message = 'An unexpected error occurred: ' . $e->getMessage();
|
||
}
|
||
|
||
return $this->respond([
|
||
'status' => 'error',
|
||
'code' => 500,
|
||
'data' => [],
|
||
'message'=> $message,
|
||
'ref' => $ref
|
||
], 200);
|
||
}
|
||
}
|
||
|
||
// 5.2) Top 50 Agent the Excel file
|
||
public function downloadExcelPerformingAgentsTop50()
|
||
{
|
||
|
||
try{
|
||
$managerId = $this->request->getGet('manager_id');
|
||
$from_date = $this->request->getGet('from_date');
|
||
$to_date = $this->request->getGet('to_date');
|
||
|
||
if (empty($managerId)) {
|
||
$msg = "Missing: manager_id";
|
||
$ref = ['debug_msg' => $msg];
|
||
return $this->response->setJSON(['status' => 'error', 'code' => 400, 'data' => [], 'message' => 'Missing required parameters', 'ref' => $ref])->setStatusCode(200);
|
||
}
|
||
|
||
if (empty($from_date) || empty($to_date)) {
|
||
|
||
// Default: last 10 days
|
||
$fromObj = new \DateTime('-10 days');
|
||
$toObj = new \DateTime();
|
||
|
||
} else {
|
||
|
||
// Convert d-m-Y → DateTime
|
||
$fromObj = \DateTime::createFromFormat('d-m-Y', $from_date);
|
||
$toObj = \DateTime::createFromFormat('d-m-Y', $to_date);
|
||
|
||
if (!$fromObj || !$toObj) {
|
||
return $this->response->setJSON([
|
||
'status' => 'error',
|
||
'message' => 'Invalid date format (expected d-m-Y)',
|
||
]);
|
||
}
|
||
}
|
||
|
||
// For DB query
|
||
$fromDate = $fromObj->format('Y-m-d 00:00:00');
|
||
$toDate = $toObj->format('Y-m-d 23:59:59');
|
||
|
||
// For filename
|
||
$startDateFile = $fromObj->format('d_m_Y');
|
||
$endDateFile = $toObj->format('d_m_Y');
|
||
|
||
// For title
|
||
$startDateTitle = $fromObj->format('d-m-Y');
|
||
$endDateTitle = $toObj->format('d-m-Y');
|
||
|
||
// Renamed helper function for consistency: getAgentMonthlyPoliciesData
|
||
$data = $this->getDataPerformingAgentsTop50($managerId,$fromDate,$toDate);
|
||
$header = ['S.No', 'Partner Code', 'Partner Name', 'Policy Count', 'Premium Amount'];
|
||
|
||
if (empty($data)) {
|
||
throw new \RuntimeException('No policies found.', 404);
|
||
}
|
||
|
||
$lastIndex = count(reset($data)) - 1;
|
||
// usort($data, function($a, $b) use ($lastIndex) {
|
||
// return (float)$a[$lastIndex] <=> (float)$b[$lastIndex];
|
||
// });
|
||
usort($data, function($a, $b) use ($lastIndex) {
|
||
return (float)$b[$lastIndex] <=> (float)$a[$lastIndex];
|
||
});
|
||
$finalData = [];
|
||
$serialNo = 1;
|
||
$totalSum = 0;
|
||
|
||
foreach ($data as $row) {
|
||
$indexedRow = array_values($row);
|
||
$lastIndex = count($indexedRow) - 1;
|
||
$totalSum += (float) $indexedRow[$lastIndex];
|
||
unset($indexedRow[$lastIndex]);
|
||
$finalData[] = array_merge([$serialNo++], $indexedRow);
|
||
}
|
||
// foreach ($data as $row) {
|
||
// $finalData[] = array_merge([$serialNo++], $row);
|
||
// }
|
||
|
||
|
||
// Calculate and append total
|
||
$lastColIndex = count($header) - 1; // Get index of the last column (Premium)
|
||
// $grandTotal = array_sum(array_column($finalData, $lastColIndex));
|
||
$fmt = new \NumberFormatter('en_IN', \NumberFormatter::CURRENCY);
|
||
$formattedGrandTotal = $fmt->formatCurrency($totalSum, "INR");
|
||
|
||
$totalRow = array_fill(0, count($header), ''); // Create empty row of same width
|
||
$totalRow[$lastColIndex - 1] = 'Total:'; // Put label in 2nd to last
|
||
$totalRow[$lastColIndex] = $totalSum;
|
||
$finalData[] = $totalRow;
|
||
|
||
$fileName = 'Performing_Partner_Top_50_Policies_Details_Monthly_Report.xlsx';
|
||
|
||
// --- CALL CENTRALIZED FUNCTION ---
|
||
$this->streamExcelFile(
|
||
$header,
|
||
$finalData,
|
||
"Performing Partner ( Top 50 ) Policies Details - From ".$startDateTitle." to ".$endDateTitle." Report",
|
||
$fileName,
|
||
true // This will ignore the bold/alignment logic for the last row
|
||
);
|
||
|
||
return;
|
||
} catch (\Throwable $e) {
|
||
|
||
// ... (Your error handling remains the same) ...
|
||
$ref = [];
|
||
// Case 1: No Data Found (Soft Success JSON)
|
||
if ($e instanceof \RuntimeException && $e->getCode() === 404) {
|
||
$ref['message'] = 'No Data Found';
|
||
return $this->respond([
|
||
'status' => 'success',
|
||
'code' => 200,
|
||
'data' => [],
|
||
'message' => 'No Data Found',
|
||
'ref' => $ref
|
||
], 200);
|
||
}
|
||
|
||
// Case 2: Database or Other Error (Error JSON)
|
||
$isDbError = $e instanceof \CodeIgniter\Database\Exceptions\DatabaseException
|
||
|| $e instanceof \mysqli_sql_exception
|
||
|| $e instanceof \PDOException;
|
||
|
||
if ($isDbError) {
|
||
$ref['debug_info'] = $e->getFile() . " / LN : " . $e->getLine();
|
||
$ref['message'] = 'Database Error Occurred.';
|
||
$message = 'Database Error Occurred.';
|
||
} else {
|
||
$ref['message'] = 'An unexpected error occurred: ' . $e->getMessage();
|
||
$message = 'An unexpected error occurred: ' . $e->getMessage();
|
||
}
|
||
|
||
return $this->respond([
|
||
'status' => 'error',
|
||
'code' => 500,
|
||
'data' => [],
|
||
'message'=> $message,
|
||
'ref' => $ref
|
||
], 200);
|
||
}
|
||
}
|
||
|
||
// 6) Agents who all are Without Policies generate the Excel file (last 10 days no Policies created by the agents)
|
||
public function downloadExcelWithoutPolicies()
|
||
{
|
||
try{
|
||
$managerId = $this->request->getGet('manager_id');
|
||
$from_date = $this->request->getGet('from_date');
|
||
$to_date = $this->request->getGet('to_date');
|
||
|
||
if (empty($managerId)) {
|
||
$msg = "Missing: ";
|
||
$msg .= empty($managerId) ? "manager_id " : "";
|
||
$ref = ['debug_msg' => $msg];
|
||
// $ref = ['debug_msg' => 'Missing required parameters: manager_id, broker_id, and month must be provided.'];
|
||
return $this->response->setJSON(['status' => 'error','code' => 400,'data' => [],'message' => 'Missing required parameters','ref' => $ref])->setStatusCode(200);
|
||
}
|
||
|
||
if (empty($from_date) || empty($to_date)) {
|
||
|
||
// Default: last 10 days
|
||
$fromObj = new \DateTime('-10 days');
|
||
$toObj = new \DateTime();
|
||
|
||
} else {
|
||
|
||
// Convert d-m-Y → DateTime
|
||
$fromObj = \DateTime::createFromFormat('d-m-Y', $from_date);
|
||
$toObj = \DateTime::createFromFormat('d-m-Y', $to_date);
|
||
|
||
if (!$fromObj || !$toObj) {
|
||
return $this->response->setJSON([
|
||
'status' => 'error',
|
||
'message' => 'Invalid date format (expected d-m-Y)',
|
||
]);
|
||
}
|
||
}
|
||
|
||
// For DB query
|
||
$fromDate = $fromObj->format('Y-m-d 00:00:00');
|
||
$toDate = $toObj->format('Y-m-d 23:59:59');
|
||
|
||
// For filename
|
||
$startDateFile = $fromObj->format('d_m_Y');
|
||
$endDateFile = $toObj->format('d_m_Y');
|
||
|
||
// For title
|
||
$startDateTitle = $fromObj->format('d-m-Y');
|
||
$endDateTitle = $toObj->format('d-m-Y');
|
||
|
||
|
||
$data = $this->getDataAgentsWithoutPolicies($managerId,$fromDate,$toDate);
|
||
|
||
if (empty($data)) {
|
||
throw new \RuntimeException('No Partners without policies found.', 404);
|
||
}
|
||
|
||
$header = ['S.No','Partner Code','Partner Name', 'Email', 'Mobile'];
|
||
|
||
$finalData = [];
|
||
$serialNo = 1; // Start counter at 1
|
||
|
||
// 2. Loop through and prepend the serial number
|
||
foreach ($data as $row) {
|
||
// We create a new array for each row
|
||
$finalData[] = [
|
||
$serialNo++,
|
||
$row[0],
|
||
$row[1],
|
||
$row[2],
|
||
$row[3]
|
||
];
|
||
}
|
||
|
||
|
||
|
||
// --- Filename Generation ---
|
||
|
||
// $fileName = 'Non_Performing_Partners_No_Business_For_Last_10_Days_' . $startDateFile . '_to_' . $endDateFile . '.xlsx';
|
||
$fileName = 'Non_Performing_Partners_No_Business_From_' . $startDateFile . '_To_' . $endDateFile . '.xlsx';
|
||
|
||
|
||
// Example output: Agents_Without_Policies_20251206_to_20251215.xlsx
|
||
|
||
// --- CENTRALIZED CALL ---
|
||
$this->streamExcelFile(
|
||
$header,
|
||
$finalData,
|
||
"Non Performing Partners ( No Business From " . $startDateTitle . " to " . $endDateTitle . " )",
|
||
$fileName,
|
||
false // This will ignore the bold/alignment logic for the last row
|
||
);
|
||
|
||
return;
|
||
} catch (\Throwable $e) {
|
||
|
||
// ... (Your error handling remains the same) ...
|
||
$ref = [];
|
||
// Case 1: No Data Found (Soft Success JSON)
|
||
if ($e instanceof \RuntimeException && $e->getCode() === 404) {
|
||
$ref['message'] = 'No Data Found';
|
||
return $this->respond([
|
||
'status' => 'success',
|
||
'code' => 200,
|
||
'data' => [],
|
||
'ref' => $ref
|
||
], 200);
|
||
}
|
||
|
||
// Case 2: Database or Other Error (Error JSON)
|
||
$isDbError = $e instanceof \CodeIgniter\Database\Exceptions\DatabaseException
|
||
|| $e instanceof \mysqli_sql_exception
|
||
|| $e instanceof \PDOException;
|
||
|
||
if ($isDbError) {
|
||
$ref['debug_info'] = $e->getFile() . " / LN : " . $e->getLine();
|
||
$ref['message'] = 'Database Error Occurred.';
|
||
} else {
|
||
$ref['message'] = 'An unexpected error occurred: ' . $e->getMessage();
|
||
}
|
||
|
||
return $this->respond([
|
||
'status' => 'error',
|
||
'code' => $e->getCode() ?: 500,
|
||
'data' => [],
|
||
'ref' => $ref
|
||
], 200);
|
||
}
|
||
}
|
||
|
||
// 7 Function to generate the Excel file
|
||
public function downloadExcelStaffAndProduct()
|
||
{
|
||
try{
|
||
$managerId = $this->request->getGet('manager_id');
|
||
$executiveId = $this->request->getGet('sales_executive_id');
|
||
$vehicleType = $this->request->getGet('vehicle_type');
|
||
$month = $this->request->getGet('month');
|
||
|
||
// --- ADDED VALIDATION (Simplified logic for clarity) ---
|
||
if (empty($managerId) || empty($executiveId) || empty($vehicleType) || empty($month)) {
|
||
$msg = "Missing: ";
|
||
$msg .= empty($managerId) ? "manager_id " : "";
|
||
$msg .= empty($executiveId) ? "sales_executive_id " : "";
|
||
$msg .= empty($vehicleType) ? "vehicle_type " : "";
|
||
$msg .= empty($month) ? "month " : "";
|
||
$ref = ['debug_msg' => $msg];
|
||
// $ref = ['debug_msg' => 'Missing required parameters: manager_id, broker_id, and month must be provided.'];
|
||
return $this->response->setJSON(['status' => 'error','code' => 400,'data' => [],'message' => 'Missing required parameters','ref' => $ref])->setStatusCode(200);
|
||
|
||
}
|
||
|
||
$data = $this->getDataStaffAndProduct($managerId, $executiveId,$vehicleType, $month);
|
||
if (empty($data)) {
|
||
throw new \RuntimeException('No policies found for the specified month.', 404);
|
||
}
|
||
$lastIndex = count(reset($data)) - 1;
|
||
// usort($data, function($a, $b) use ($lastIndex) {
|
||
// return (float)$a[$lastIndex] <=> (float)$b[$lastIndex];
|
||
// });
|
||
usort($data, function($a, $b) use ($lastIndex) {
|
||
return (float)$b[$lastIndex] <=> (float)$a[$lastIndex];
|
||
});
|
||
$finalData = [];
|
||
$serialNo = 1;
|
||
$totalSum = 0;
|
||
|
||
foreach ($data as $row) {
|
||
$indexedRow = array_values($row);
|
||
$lastIndex = count($indexedRow) - 1;
|
||
$totalSum += (float) $indexedRow[$lastIndex];
|
||
unset($indexedRow[$lastIndex]);
|
||
$finalData[] = array_merge([$serialNo++], $indexedRow);
|
||
}
|
||
// foreach ($data as $row) {
|
||
// $finalData[] = array_merge([$serialNo++], $row);
|
||
// }
|
||
$executiveName = $finalData[0][11] ?? '';
|
||
$vehicleType = $finalData[0][12] ?? '';
|
||
// Define column headers
|
||
|
||
$header = ['S.No',
|
||
'Received Date', 'Assigned To',
|
||
'Partner Name' , 'Partner Code',
|
||
'Insurer Name' ,'Insurer Short Name',
|
||
'Insured Name', 'Vehicle Number',
|
||
'Payment Mode', 'Plan Type',
|
||
'Sales Executive Name', 'Vehicle Type', 'Policy Number','Issued Date', 'Premium Amount'];
|
||
|
||
// --- 1. MODIFIED LOGIC: Throw RuntimeException if no data ---
|
||
|
||
|
||
// Calculate and append total
|
||
$lastColIndex = count($header) - 1; // Get index of the last column (Premium)
|
||
// $grandTotal = array_sum(array_column($finalData, $lastColIndex));
|
||
|
||
$totalRow = array_fill(0, count($header), ''); // Create empty row of same width
|
||
$totalRow[$lastColIndex - 1] = 'Total:'; // Put label in 2nd to last
|
||
$totalRow[$lastColIndex] = $totalSum;
|
||
$finalData[] = $totalRow;
|
||
|
||
|
||
$monthName = ($month === 'current') ? date('M_Y') : date('M_Y', strtotime('-1 month'));
|
||
$fileName = "Sales_Executive_Product_Wise_" . $monthName . ".xlsx";
|
||
$titleName = str_replace(['_', '.xlsx'], [' ', ''], $fileName);
|
||
|
||
$this->streamExcelFile($header,$finalData,$titleName,$fileName,true);
|
||
|
||
return; // STOP CI4 rendering anything else
|
||
|
||
} catch (\Throwable $e) {
|
||
|
||
// ... (Your error handling remains the same) ...
|
||
$ref = [];
|
||
// Case 1: No Data Found (Soft Success JSON)
|
||
if ($e instanceof \RuntimeException && $e->getCode() === 404) {
|
||
$ref['message'] = 'No Data Found';
|
||
return $this->respond([
|
||
'status' => 'success',
|
||
'code' => 200,
|
||
'data' => [],
|
||
'message' => 'No Data Found',
|
||
'ref' => $ref
|
||
], 200);
|
||
}
|
||
|
||
// Case 2: Database or Other Error (Error JSON)
|
||
$isDbError = $e instanceof \CodeIgniter\Database\Exceptions\DatabaseException
|
||
|| $e instanceof \mysqli_sql_exception
|
||
|| $e instanceof \PDOException;
|
||
|
||
if ($isDbError) {
|
||
$ref['debug_info'] = $e->getFile() . " / LN : " . $e->getLine();
|
||
$ref['message'] = 'Database Error Occurred.';
|
||
$message = 'Database Error Occurred.';
|
||
} else {
|
||
$ref['message'] = 'An unexpected error occurred: ' . $e->getMessage();
|
||
$message = 'An unexpected error occurred: ' . $e->getMessage();
|
||
}
|
||
|
||
return $this->respond([
|
||
'status' => 'error',
|
||
'code' => 500,
|
||
'data' => [],
|
||
'message'=> $message,
|
||
'ref' => $ref
|
||
], 200);
|
||
}
|
||
}
|
||
|
||
// 8 ZIP inside 3 Excel
|
||
public function downloadExcelAsZIP($managerId = null)
|
||
{
|
||
// Ensure managerId is retrieved correctly if not passed as argument
|
||
$managerId = $managerId ?? $this->request->getGet('manager_id');
|
||
|
||
if (empty($managerId)) {
|
||
return $this->response->setJSON([
|
||
'status' => 'error',
|
||
'code' => 400,
|
||
'message' => 'Missing required parameter: manager_id'
|
||
]);
|
||
}
|
||
|
||
$fromDate = date('Y-m-d', strtotime('-10 days'));
|
||
$toDate = date('Y-m-d');
|
||
// 1. Fetch Data First
|
||
$monthlyData = $this->getDataPerformingAgentsByID($managerId,null,$fromDate,$toDate);
|
||
$lowPremiumData = $this->getDataLowPremiumAgent($managerId,$fromDate,$toDate);
|
||
$inactiveData = $this->getDataAgentsWithoutPolicies($managerId,$fromDate,$toDate);
|
||
|
||
// 2. Add Total Rows where needed (Premium is usually the last index)
|
||
if (!empty($monthlyData)) {
|
||
$totalMonthly = array_sum(array_column($monthlyData, 12)); // Index 12 is 'premium_amount'
|
||
$monthlyData[] = ['', '', '', '', '', '', '', '', '', '', '', 'Total:', $totalMonthly];
|
||
}
|
||
|
||
if (!empty($lowPremiumData)) {
|
||
$totalLow = array_sum(array_column($lowPremiumData, 13)); // Index 13 is 'premium_amount'
|
||
$lowPremiumData[] = ['', '', '', '', '', '', '', '', '', '', '', '', 'Total:', $totalLow];
|
||
}
|
||
|
||
// 3. Prepare Report Configuration
|
||
$reports = [
|
||
'Monthly_Policies_Details.xlsx' => ['data' => $monthlyData, 'header' => ['Received Date', 'Assigned To', 'Insurer', 'Short Name', 'Insured', 'Reg No', 'Mode', 'Plan', 'Agent', 'Code', 'Policy #', 'Issued Date', 'Premium'], 'title' => "Monthly Report",'showTotal' => true],
|
||
'Low_Premium_Policies_Details.xlsx' => ['data' => $lowPremiumData, 'header' => ['Received Date', 'Assigned To', 'Insurer', 'Short Name', 'Insured', 'Reg No', 'Mode', 'Plan', 'Agent', 'Code', 'Status', 'Policy #', 'Issued Date', 'Premium'], 'title' => "Low Premium Report",'showTotal' => true],
|
||
'Partners_Without_Policies_Details.xlsx' => ['data' => $inactiveData, 'header' => ['Partner Name', 'Partner Code', 'Email', 'Mobile'], 'title' => "Inactive Partners",'showTotal' => false]
|
||
];
|
||
|
||
// 4. Initialize Zip
|
||
$zipFileName = 'Agent_Reports_' . date('Y-m-d_H-i') . '.zip';
|
||
$zipFilePath = tempnam(sys_get_temp_dir(), 'zip');
|
||
|
||
$zip = new \ZipArchive();
|
||
if ($zip->open($zipFilePath, \ZipArchive::CREATE | \ZipArchive::OVERWRITE) === TRUE) {
|
||
foreach ($reports as $name => $config) {
|
||
$excelContent = $this->generateExcelBinary($config['header'], $config['data'], $config['title'],$config['showTotal']);
|
||
$zip->addFromString($name, $excelContent);
|
||
}
|
||
$zip->close();
|
||
}
|
||
|
||
// 5. Direct Download
|
||
// Instead of creating a file on disk, we can send the ZIP data directly.
|
||
// This avoids the "deleteFileAfterSend" error entirely.
|
||
|
||
$zipData = file_get_contents($zipFilePath); // Read the zip file into a variable
|
||
unlink($zipFilePath); // Delete the temp file immediately
|
||
|
||
return $this->response->download($zipFileName, $zipData);
|
||
}
|
||
|
||
|
||
// GENERATE ZIP
|
||
private function generateExcelBinary(array $header, array $data, string $title, bool $showTotal = true): string
|
||
{
|
||
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
|
||
$sheet = $spreadsheet->getActiveSheet();
|
||
|
||
// 1. Calculate dimensions
|
||
$columnCount = count($header);
|
||
$lastCol = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($columnCount);
|
||
$rowCount = count($data);
|
||
|
||
// 2. Title Styling
|
||
$sheet->mergeCells("A1:{$lastCol}1");
|
||
$sheet->setCellValue('A1', $title);
|
||
$sheet->getStyle('A1')->getFont()->setBold(true)->setSize(14);
|
||
$sheet->getStyle('A1')->getAlignment()->setHorizontal('center');
|
||
|
||
// 3. Headers (Row 2)
|
||
$sheet->fromArray($header, NULL, 'A2');
|
||
$sheet->getStyle("A2:{$lastCol}2")->getFont()->setBold(true);
|
||
$sheet->getStyle("A2:{$lastCol}2")->getFill()
|
||
->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
|
||
->getStartColor()->setARGB('F2F2F2'); // Light gray background
|
||
|
||
// 4. Data (Starting Row 3)
|
||
$sheet->fromArray($data, NULL, 'A3');
|
||
|
||
// 5. Bold the Total Row (The last row of data)
|
||
if ($rowCount > 0 && $showTotal === true) {
|
||
$totalRowNumber = $rowCount + 2; // Row 1 (Title) + Row 2 (Header) + Data Rows
|
||
$sheet->getStyle("A{$totalRowNumber}:{$lastCol}{$totalRowNumber}")->getFont()->setBold(true);
|
||
|
||
// Optional: Add a top border to the total row to make it look professional
|
||
// $sheet->getStyle("A{$totalRowNumber}:{$lastCol}{$totalRowNumber}")->getBorders()->getTop()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
|
||
}
|
||
|
||
// 6. Auto-size Columns
|
||
// Using column index to avoid issues with double-letter columns (e.g., AA, AB)
|
||
for ($i = 1; $i <= $columnCount; $i++) {
|
||
$colLetter = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($i);
|
||
$sheet->getColumnDimension($colLetter)->setAutoSize(true);
|
||
}
|
||
|
||
// 7. Capture Binary Output
|
||
$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
|
||
|
||
// Use Output Buffering to grab the file content as a string
|
||
ob_start();
|
||
$writer->save('php://output');
|
||
$excelBinary = ob_get_clean();
|
||
|
||
return $excelBinary;
|
||
}
|
||
|
||
public function downloadExcelStaffPendingSummary()
|
||
{
|
||
try{
|
||
$managerId = $this->request->getGet('manager_id');
|
||
|
||
|
||
// --- ADDED VALIDATION (Simplified logic for clarity) ---
|
||
if (empty($managerId)) {
|
||
$msg = "Missing: manager_id";
|
||
$ref = ['debug_msg' => $msg];
|
||
return $this->response->setJSON(['status' => 'error', 'code' => 400, 'data' => [], 'message' => 'Missing required parameters', 'ref' => $ref])->setStatusCode(200);
|
||
}
|
||
|
||
$data = $this->getDataStaffPendingSummary($managerId);
|
||
|
||
if (empty($data)) {
|
||
throw new \RuntimeException('No summary found.', 404);
|
||
}
|
||
|
||
// Define column headers
|
||
$header = ['S.No', 'Staff Name', 'Today Assigned', 'Pending', 'In Progress', 'Completed', 'Previous Pending'];
|
||
|
||
// Initialize totals
|
||
$sums = array_fill(2, 5, 0); // Start from index 2, cover 5 columns
|
||
$finalData = [];
|
||
$serialNo = 1;
|
||
|
||
// Process data and calculate sums
|
||
foreach ($data as $row) {
|
||
// Convert row to indexed array values to ensure merge works correctly
|
||
$rowData = array_values($row);
|
||
|
||
// Add Serial Number
|
||
$finalData[] = array_merge([$serialNo++], $rowData);
|
||
|
||
// Summing columns index 2 to 6
|
||
for ($i = 2; $i <= 6; $i++) {
|
||
// Use index $i-1 because $row doesn't have S.No yet, but $rowData does
|
||
$sums[$i] += (int)($rowData[$i - 1] ?? 0);
|
||
}
|
||
}
|
||
|
||
// Create the Total Row
|
||
$totalRow = array_fill(0, count($header), ''); // Create empty row
|
||
$totalRow[1] = 'Total:'; // Label under "Staff Name"
|
||
|
||
foreach ($sums as $index => $sumValue) {
|
||
// This ensures that even if the sum is 0, it puts the digit '0' in the cell
|
||
$totalRow[$index] = $sumValue;
|
||
}
|
||
|
||
// Fill sums into the total row
|
||
foreach ($sums as $index => $sumValue) {
|
||
$totalRow[$index] = $sumValue;
|
||
}
|
||
|
||
// Add to final data
|
||
$finalData[] = $totalRow;
|
||
|
||
$fileName = 'Staff_Level_Pending_Summary.xlsx';
|
||
|
||
|
||
// --- CALL CENTRALIZED FUNCTION ---
|
||
$this->streamExcelFile(
|
||
$header,
|
||
$finalData,
|
||
"Staff Level Pending Summary",
|
||
$fileName,
|
||
false
|
||
);
|
||
|
||
return; // STOP CI4 rendering anything else
|
||
|
||
} catch (\Throwable $e) {
|
||
|
||
// ... (Your error handling remains the same) ...
|
||
$ref = [];
|
||
// Case 1: No Data Found (Soft Success JSON)
|
||
if ($e instanceof \RuntimeException && $e->getCode() === 404) {
|
||
$ref['message'] = 'No Data Found';
|
||
return $this->respond([
|
||
'status' => 'success',
|
||
'code' => 200,
|
||
'data' => [],
|
||
'message' => 'No Data Found',
|
||
'ref' => $ref
|
||
], 200);
|
||
}
|
||
|
||
// Case 2: Database or Other Error (Error JSON)
|
||
$isDbError = $e instanceof \CodeIgniter\Database\Exceptions\DatabaseException
|
||
|| $e instanceof \mysqli_sql_exception
|
||
|| $e instanceof \PDOException;
|
||
|
||
if ($isDbError) {
|
||
$ref['debug_info'] = $e->getFile() . " / LN : " . $e->getLine();
|
||
$ref['message'] = 'Database Error Occurred.';
|
||
$message = 'Database Error Occurred.';
|
||
} else {
|
||
$ref['message'] = 'An unexpected error occurred: ' . $e->getMessage();
|
||
$message = 'An unexpected error occurred: ' . $e->getMessage();
|
||
}
|
||
|
||
return $this->respond([
|
||
'status' => 'error',
|
||
'code' => 500,
|
||
'data' => [],
|
||
'message'=> $message,
|
||
'ref' => $ref
|
||
], 200);
|
||
}
|
||
}
|
||
|
||
|
||
public function downloadExcelStaffPendingSummaryByID()
|
||
{
|
||
try{
|
||
$managerId = $this->request->getGet('manager_id');
|
||
$staffId = $this->request->getGet('staff_id');
|
||
$status = $this->request->getGet('status');
|
||
|
||
|
||
// --- ADDED VALIDATION (Simplified logic for clarity) ---
|
||
if (empty($managerId) || empty($staffId) || empty($status)) {
|
||
$msg = "Missing: ";
|
||
$msg .= empty($managerId) ? "manager_id " : "";
|
||
$msg .= empty($staffId) ? "staff_id " : "";
|
||
$msg .= empty($status) ? "status " : "";
|
||
$ref = ['debug_msg' => $msg];
|
||
// $ref = ['debug_msg' => 'Missing required parameters: manager_id, broker_id, and month must be provided.'];
|
||
return $this->response->setJSON(['status' => 'error','code' => 400,'data' => [],'message' => 'Missing required parameters','ref' => $ref])->setStatusCode(200);
|
||
|
||
}
|
||
|
||
$data = $this->getDataStaffPendingSummaryByID($managerId,$staffId,$status);
|
||
|
||
if (empty($data)) {
|
||
throw new \RuntimeException('No summary found.', 404);
|
||
}
|
||
|
||
// Define column headers
|
||
|
||
|
||
$header = ['S.No', 'Received Date',
|
||
'Assigned To(Staff Name)',
|
||
'Partner Name', 'Partner Code',
|
||
'Insurer Name', 'Insurer Short Name',
|
||
'Insured Name', 'Vehicle Number',
|
||
'Payment Mode', 'Plan Type'];
|
||
// Initialize totals
|
||
$lastIndex = count(reset($data)) - 1;
|
||
// usort($data, function($a, $b) use ($lastIndex) {
|
||
// return (float)$a[$lastIndex] <=> (float)$b[$lastIndex];
|
||
// });
|
||
usort($data, function($a, $b) use ($lastIndex) {
|
||
return (float)$b[$lastIndex] <=> (float)$a[$lastIndex];
|
||
});
|
||
$finalData = [];
|
||
$serialNo = 1;
|
||
|
||
foreach ($data as $row) {
|
||
$finalData[] = array_merge([$serialNo++], $row);
|
||
}
|
||
|
||
$staffName = $finalData[0][2] ?? 'Staff';
|
||
|
||
$cleanStaffName = str_replace(' ', '_', strtolower($staffName));
|
||
$cleanStaffName2 = ucwords($staffName);
|
||
|
||
$cleanStatus = str_replace('_', ' ', strtolower($status));
|
||
$cleanStatus2 = ucwords(str_replace('_', ' ', strtolower($status)));
|
||
|
||
$fileName = $cleanStaffName . '_' . $cleanStatus . '_Summary.xlsx';
|
||
|
||
// --- CALL CENTRALIZED FUNCTION ---
|
||
$this->streamExcelFile(
|
||
$header,
|
||
$finalData,
|
||
$cleanStaffName2 ." ". $cleanStatus2." Summary",
|
||
$fileName,
|
||
false
|
||
);
|
||
|
||
return; // STOP CI4 rendering anything else
|
||
|
||
} catch (\Throwable $e) {
|
||
|
||
// ... (Your error handling remains the same) ...
|
||
$ref = [];
|
||
// Case 1: No Data Found (Soft Success JSON)
|
||
if ($e instanceof \RuntimeException && $e->getCode() === 404) {
|
||
$ref['message'] = 'No Data Found';
|
||
return $this->respond([
|
||
'status' => 'success',
|
||
'code' => 200,
|
||
'data' => [],
|
||
'message' => 'No Data Found',
|
||
'ref' => $ref
|
||
], 200);
|
||
}
|
||
|
||
// Case 2: Database or Other Error (Error JSON)
|
||
$isDbError = $e instanceof \CodeIgniter\Database\Exceptions\DatabaseException
|
||
|| $e instanceof \mysqli_sql_exception
|
||
|| $e instanceof \PDOException;
|
||
|
||
if ($isDbError) {
|
||
$ref['debug_info'] = $e->getFile() . " / LN : " . $e->getLine();
|
||
$ref['message'] = 'Database Error Occurred.';
|
||
$message = 'Database Error Occurred.';
|
||
} else {
|
||
$ref['message'] = 'An unexpected error occurred: ' . $e->getMessage();
|
||
$message = 'An unexpected error occurred: ' . $e->getMessage();
|
||
}
|
||
|
||
return $this->respond([
|
||
'status' => 'error',
|
||
'code' => 500,
|
||
'data' => [],
|
||
'message'=> $message,
|
||
'ref' => $ref
|
||
], 200);
|
||
}
|
||
}
|
||
|
||
|
||
public function downloadExcelEndorsement()
|
||
{
|
||
try{
|
||
$managerId = $this->request->getGet('manager_id');
|
||
$search = $this->request->getGet('search');
|
||
$agent_id = $this->request->getGet('agent_id');
|
||
$policy_number = $this->request->getGet('policy_number');
|
||
$from_date = $this->request->getGet('from_date');
|
||
$to_date = $this->request->getGet('to_date');
|
||
$endorsement_type = $this->request->getGet('endorsement_type');
|
||
$insurer_id = $this->request->getGet('insurer_id');
|
||
$status = $this->request->getGet('status');
|
||
$verification = $this->request->getGet('verification');
|
||
|
||
|
||
// --- ADDED VALIDATION (Simplified logic for clarity) ---
|
||
if (empty($managerId)) {
|
||
$msg = "Missing: manager_id";
|
||
$ref = ['debug_msg' => $msg];
|
||
return $this->response->setJSON(['status' => 'error', 'code' => 400, 'data' => [], 'message' => 'Missing required parameters', 'ref' => $ref])->setStatusCode(200);
|
||
}
|
||
|
||
// $data = $this->getDataEndorsement($managerId,$search);
|
||
$data = $this->getDataEndorsement($managerId,$search,$agent_id,$policy_number,$from_date,$to_date,$endorsement_type,$insurer_id,$status,$verification);
|
||
|
||
|
||
if (empty($data)) {
|
||
throw new \RuntimeException('No Endorsement Details found.', 404);
|
||
}
|
||
|
||
$header = [
|
||
'S.No',
|
||
'Created Date',
|
||
'Internal/External',
|
||
'Created By',
|
||
'Policy Number',
|
||
'Start Date', 'End Date',
|
||
'Vehicle No',
|
||
'Type Of Endorsement',
|
||
'Endorsement Number',
|
||
'Insured Name',
|
||
'Insurer',
|
||
'Broker',
|
||
'Partner Code And Name',
|
||
'Contact Person',
|
||
'Remarks',
|
||
'Is Financial',
|
||
'Status','Verification','Pending Days',
|
||
'Endorsement Premium Amount',
|
||
'Commission Amount',
|
||
];
|
||
|
||
$finalData = [];
|
||
$serialNo = 1;
|
||
$totalSum = 0;
|
||
|
||
// foreach ($data as $row) {
|
||
// $indexedRow = array_values($row);
|
||
// $lastIndex = count($indexedRow) - 1;
|
||
// $totalSum += (float) $indexedRow[$lastIndex];
|
||
// unset($indexedRow[$lastIndex]);
|
||
// $finalData[] = array_merge([$serialNo++], $indexedRow);
|
||
// }
|
||
|
||
foreach ($data as $row) {
|
||
|
||
$indexedRow = array_values($row);
|
||
|
||
// Commission Amount is last column
|
||
$commissionIndex = count($indexedRow) - 1;
|
||
|
||
$totalSum += (float) $indexedRow[$commissionIndex];
|
||
|
||
$finalData[] = array_merge([$serialNo++], $indexedRow);
|
||
}
|
||
|
||
// Calculate and append total
|
||
$lastColIndex = count($header) - 1; // Get index of the last column (Premium)
|
||
// $grandTotal = array_sum(array_column($finalData, $lastColIndex));
|
||
|
||
$totalRow = array_fill(0, count($header), ''); // Create empty row of same width
|
||
$totalRow[$lastColIndex - 1] = 'Total:'; // Put label in 2nd to last
|
||
$totalRow[$lastColIndex] = $totalSum;
|
||
$finalData[] = $totalRow;
|
||
|
||
$fileName = 'Endorsement_Report_' . date('YmdHis') . '.xlsx';
|
||
|
||
// --- CALL CENTRALIZED FUNCTION ---
|
||
$this->streamExcelFile(
|
||
$header,
|
||
$finalData,
|
||
"Endorsement Report",
|
||
$fileName,
|
||
true
|
||
);
|
||
|
||
return; // STOP CI4 rendering anything else
|
||
|
||
} catch (\Throwable $e) {
|
||
|
||
// ... (Your error handling remains the same) ...
|
||
$ref = [];
|
||
// Case 1: No Data Found (Soft Success JSON)
|
||
if ($e instanceof \RuntimeException && $e->getCode() === 404) {
|
||
$ref['message'] = 'No Data Found';
|
||
return $this->respond([
|
||
'status' => 'success',
|
||
'code' => 200,
|
||
'data' => [],
|
||
'message' => 'No Data Found',
|
||
'ref' => $ref
|
||
], 200);
|
||
}
|
||
|
||
// Case 2: Database or Other Error (Error JSON)
|
||
$isDbError = $e instanceof \CodeIgniter\Database\Exceptions\DatabaseException
|
||
|| $e instanceof \mysqli_sql_exception
|
||
|| $e instanceof \PDOException;
|
||
|
||
if ($isDbError) {
|
||
$ref['debug_info'] = $e->getFile() . " / LN : " . $e->getLine();
|
||
$ref['message'] = 'Database Error Occurred.';
|
||
$message = 'Database Error Occurred.';
|
||
} else {
|
||
$ref['message'] = 'An unexpected error occurred: ' . $e->getMessage();
|
||
$message = 'An unexpected error occurred: ' . $e->getMessage();
|
||
}
|
||
|
||
return $this->respond([
|
||
'status' => 'error',
|
||
'code' => 500,
|
||
'data' => [],
|
||
'message'=> $message,
|
||
'ref' => $ref
|
||
], 200);
|
||
}
|
||
}
|
||
|
||
|
||
/**
|
||
* Executes the detailed policy query and extracts the data for the spreadsheet.
|
||
* @param string $managerId
|
||
* @param int $brokerId
|
||
* @param string $month ('current' or 'previous')
|
||
* @return array
|
||
*/
|
||
private function getDataBroker($managerId, $brokerId, $month): array
|
||
{
|
||
// --- Correction 1: Initialize Database Connection ---
|
||
|
||
$data = [];
|
||
|
||
try {
|
||
$builder = $this->db->table('partner_policy pp');
|
||
// Note: I removed 'broker_id' from SELECT as it's redundant in the Excel sheet details
|
||
|
||
|
||
$builder->select('
|
||
DATE_FORMAT(pe.created_on, "%d-%m-%Y %h:%i %p") AS received_date,
|
||
S.name as assigned_to_name,
|
||
A.name as agent_name, A.agent_code,
|
||
I.name as insurer_name,I.short_name as insurer_short_name,
|
||
pe.name as insured_name,
|
||
pe.reg_no,
|
||
pm.value as payment_mode_value,
|
||
ipti.insurance_plan_type,
|
||
pb.name AS broker_name, DATE_FORMAT(pp.issued_date, "%d-%m-%Y") AS issued_date,CONCAT("`", pp.policy_number) AS policy_number,
|
||
CONCAT("₹ ", FORMAT(pp.premium_amount, 2, "en_IN")) AS premium_amount,
|
||
pp.premium_amount AS raw_premium');
|
||
$builder->join('partner_enquiry pe', 'pe.id = pp.enquiry_id', 'left');
|
||
$builder->join('partner_brokers pb', 'pb.id = pe.broker_id', 'left');
|
||
$builder->join('partner_quotation Q', 'Q.enquiry_id = pe.id', 'left');
|
||
$builder->join('partner_payment_mode_master pm', 'pm.id = Q.payment_mode_id', 'left');
|
||
$builder->join('partner_agent A', 'A.id = pe.agent_id', 'left');
|
||
$builder->join('partner_insurance_plan_type_master ipti', 'ipti.id = Q.insurance_plan_type_id', 'left');
|
||
$builder->join('insurers I', 'I.id = Q.insurer_id', 'left');
|
||
$builder->join('partner_staff S', 'S.id = pe.assigned_to', 'left');
|
||
|
||
// --- Correction 2: Use Robust Date Range Filtering ---
|
||
if ($month === "current") {
|
||
// WHERE issued_date >= start_of_current_month AND issued_date < start_of_next_month
|
||
$builder->where("pp.issued_date >= DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01')", NULL, FALSE);
|
||
$builder->where("pp.issued_date < DATE_FORMAT(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01')", NULL, FALSE);
|
||
}
|
||
else if ($month === "previous") {
|
||
// WHERE issued_date >= start_of_previous_month AND issued_date < start_of_current_month
|
||
$builder->where("pp.issued_date >= DATE_FORMAT(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01')", NULL, FALSE);
|
||
$builder->where("pp.issued_date < DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01')", NULL, FALSE);
|
||
}
|
||
|
||
// Broker and Manager Filter
|
||
$builder->where('pp.manager_id', $managerId);
|
||
$builder->where('pb.id', $brokerId);
|
||
$builder->orderBy('pp.issued_date', 'DESC');
|
||
|
||
$results = $builder->get()->getResultArray();
|
||
|
||
// Restructure data for PhpSpreadsheet (2D array of values)
|
||
foreach ($results as $row) {
|
||
$data[] = array_values($row);
|
||
}
|
||
|
||
} catch (\CodeIgniter\Database\Exceptions\DatabaseException $e) {
|
||
log_message('error', 'Database Error during Excel fetch: ' . $e->getMessage());
|
||
// Re-throw the exception so it is caught by the main function's catch block
|
||
throw $e;
|
||
}
|
||
|
||
return $data;
|
||
}
|
||
|
||
/**
|
||
* Executes the detailed policy query and extracts the data for the spreadsheet.
|
||
* @param string $managerId
|
||
* @param string $vehicleType
|
||
* @param string $month ('current' or 'previous')
|
||
* @return array
|
||
*/
|
||
private function getDataProduct($managerId, $vehicleType, $month): array
|
||
{
|
||
// --- Correction 1: Initialize Database Connection ---
|
||
|
||
$data = [];
|
||
|
||
try {
|
||
$builder = $this->db->table('partner_policy pp');
|
||
// Note: I removed 'broker_id' from SELECT as it's redundant in the Excel sheet details
|
||
$builder->select('
|
||
DATE_FORMAT(pe.created_on, "%d-%m-%Y %h:%i %p") AS received_date,
|
||
S.name as assigned_to_name,
|
||
A.name as agent_name, A.agent_code,
|
||
I.name as insurer_name,I.short_name as insurer_short_name,
|
||
pe.name as insured_name,
|
||
pe.reg_no,
|
||
pm.value as payment_mode_value,
|
||
ipti.insurance_plan_type,
|
||
pp.vehicle_type, DATE_FORMAT(pp.issued_date, "%d-%m-%Y") AS issued_date,CONCAT("`", pp.policy_number) AS policy_number,
|
||
CONCAT("₹ ", FORMAT(pp.premium_amount, 2, "en_IN")) AS premium_amount,
|
||
pp.premium_amount AS raw_premium');
|
||
|
||
$builder->join('partner_enquiry pe', 'pe.id = pp.enquiry_id', 'left');
|
||
$builder->join('partner_quotation Q', 'Q.enquiry_id = pe.id', 'left');
|
||
$builder->join('partner_payment_mode_master pm', 'pm.id = Q.payment_mode_id', 'left');
|
||
$builder->join('partner_agent A', 'A.id = pe.agent_id', 'left');
|
||
$builder->join('partner_insurance_plan_type_master ipti', 'ipti.id = Q.insurance_plan_type_id', 'left');
|
||
$builder->join('insurers I', 'I.id = Q.insurer_id', 'left');
|
||
$builder->join('partner_staff S', 'S.id = pe.assigned_to', 'left');
|
||
|
||
|
||
// --- Correction 2: Use Robust Date Range Filtering ---
|
||
if ($month === "current") {
|
||
// WHERE issued_date >= start_of_current_month AND issued_date < start_of_next_month
|
||
$builder->where("pp.issued_date >= DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01')", NULL, FALSE);
|
||
$builder->where("pp.issued_date < DATE_FORMAT(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01')", NULL, FALSE);
|
||
}
|
||
else if ($month === "previous") {
|
||
// WHERE issued_date >= start_of_previous_month AND issued_date < start_of_current_month
|
||
$builder->where("pp.issued_date >= DATE_FORMAT(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01')", NULL, FALSE);
|
||
$builder->where("pp.issued_date < DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01')", NULL, FALSE);
|
||
}
|
||
|
||
// Broker and Manager Filter
|
||
$builder->where('pp.manager_id', $managerId);
|
||
$builder->where('pp.vehicle_type', $vehicleType);
|
||
|
||
|
||
$results = $builder->get()->getResultArray();
|
||
|
||
// Restructure data for PhpSpreadsheet (2D array of values)
|
||
foreach ($results as $row) {
|
||
$data[] = array_values($row);
|
||
}
|
||
|
||
} catch (\CodeIgniter\Database\Exceptions\DatabaseException $e) {
|
||
log_message('error', 'Database Error during Excel fetch: ' . $e->getMessage());
|
||
// Re-throw the exception so it is caught by the main function's catch block
|
||
throw $e;
|
||
}
|
||
|
||
return $data;
|
||
}
|
||
|
||
/**
|
||
* Executes the detailed policy query and extracts the data for the spreadsheet.
|
||
* @param string $managerId
|
||
* @param int $insurerId
|
||
* @param string $month ('current' or 'previous')
|
||
* @return array
|
||
*/
|
||
private function getDataInsurer($managerId, $insurerId, $month): array
|
||
{
|
||
// --- Correction 1: Initialize Database Connection ---
|
||
|
||
$data = [];
|
||
|
||
try {
|
||
$builder = $this->db->table('partner_policy pp');
|
||
// Note: I removed 'broker_id' from SELECT as it's redundant in the Excel sheet details
|
||
$builder->select('
|
||
DATE_FORMAT(pe.created_on, "%d-%m-%Y %h:%i %p") AS received_date,
|
||
S.name as assigned_to_name,
|
||
A.name as agent_name, A.agent_code,
|
||
pe.reg_no,
|
||
pm.value as payment_mode_value,
|
||
ipti.insurance_plan_type,
|
||
i.name AS insurer_name,i.short_name,pp.insured_name, DATE_FORMAT(pp.issued_date, "%d-%m-%Y") AS issued_date,CONCAT("`", pp.policy_number) AS policy_number,
|
||
CONCAT("₹ ", FORMAT(pp.premium_amount, 2, "en_IN")) AS premium_amount,
|
||
pp.premium_amount AS raw_premium');
|
||
|
||
$builder->join('partner_quotation pq', 'pq.id = pp.quotation_id', 'left');
|
||
$builder->join('insurers i', 'i.id = pq.insurer_id', 'left');
|
||
|
||
$builder->join('partner_enquiry pe', 'pe.id = pp.enquiry_id', 'left');
|
||
$builder->join('partner_payment_mode_master pm', 'pm.id = pq.payment_mode_id', 'left');
|
||
$builder->join('partner_agent A', 'A.id = pe.agent_id', 'left');
|
||
$builder->join('partner_insurance_plan_type_master ipti', 'ipti.id = pq.insurance_plan_type_id', 'left');
|
||
$builder->join('partner_staff S', 'S.id = pe.assigned_to', 'left');
|
||
|
||
// --- Correction 2: Use Robust Date Range Filtering ---
|
||
if ($month === "current") {
|
||
// WHERE issued_date >= start_of_current_month AND issued_date < start_of_next_month
|
||
$builder->where("pp.issued_date >= DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01')", NULL, FALSE);
|
||
$builder->where("pp.issued_date < DATE_FORMAT(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01')", NULL, FALSE);
|
||
}
|
||
else if ($month === "previous") {
|
||
// WHERE issued_date >= start_of_previous_month AND issued_date < start_of_current_month
|
||
$builder->where("pp.issued_date >= DATE_FORMAT(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01')", NULL, FALSE);
|
||
$builder->where("pp.issued_date < DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01')", NULL, FALSE);
|
||
}
|
||
|
||
// Broker and Manager Filter
|
||
$builder->where('pp.manager_id', $managerId);
|
||
$builder->where('i.id', $insurerId);
|
||
$builder->where('i.is_active',1);
|
||
$builder->orderBy('pp.issued_date', 'DESC');
|
||
// print_r($builder->getCompiledSelect());die;
|
||
$results = $builder->get()->getResultArray();
|
||
|
||
// Restructure data for PhpSpreadsheet (2D array of values)
|
||
foreach ($results as $row) {
|
||
$data[] = array_values($row);
|
||
}
|
||
|
||
} catch (\CodeIgniter\Database\Exceptions\DatabaseException $e) {
|
||
log_message('error', 'Database Error during Excel fetch: ' . $e->getMessage());
|
||
// Re-throw the exception so it is caught by the main function's catch block
|
||
throw $e;
|
||
}
|
||
|
||
return $data;
|
||
}
|
||
|
||
/**
|
||
* Executes the detailed policy query and extracts the data for the spreadsheet.
|
||
* @param string $managerId
|
||
* @param int $executiveId
|
||
* @param string $vehicleType
|
||
* @param string $month ('current' or 'previous')
|
||
* @return array
|
||
*/
|
||
private function getDataStaffAndProduct($managerId, $executiveId,$vehicleType, $month): array
|
||
{
|
||
// --- Correction 1: Initialize Database Connection ---
|
||
|
||
$data = [];
|
||
|
||
try {
|
||
$builder = $this->db->table('partner_policy pp');
|
||
|
||
$builder->select('
|
||
DATE_FORMAT(pe.created_on, "%d-%m-%Y %h:%i %p") AS received_date,
|
||
S.name as assigned_to_name,
|
||
pa.name as agent_name, pa.agent_code,
|
||
I.name as insurer_name,I.short_name as insurer_short_name,
|
||
pe.name as insured_name,
|
||
pe.reg_no,
|
||
pm.value as payment_mode_value,
|
||
ipti.insurance_plan_type,
|
||
pse.name as sales_executive_name, pp.vehicle_type AS vechile_type,CONCAT("`", pp.policy_number) AS policy_number,DATE_FORMAT(pp.issued_date, "%d-%m-%Y") AS issued_date,
|
||
CONCAT("₹ ", FORMAT(pp.premium_amount, 2, "en_IN")) AS premium_amount,
|
||
pp.premium_amount AS raw_premium');
|
||
|
||
$builder->join('partner_agent pa', 'pa.id = pp.agent_id', 'left');
|
||
$builder->join('partner_sales_executive pse', 'pse.id = pa.sales_executive_id', 'left');
|
||
|
||
$builder->join('partner_enquiry pe', 'pe.id = pp.enquiry_id', 'left');
|
||
$builder->join('partner_brokers pb', 'pb.id = pe.broker_id', 'left');
|
||
$builder->join('partner_quotation Q', 'Q.enquiry_id = pe.id', 'left');
|
||
$builder->join('partner_payment_mode_master pm', 'pm.id = Q.payment_mode_id', 'left');
|
||
|
||
$builder->join('partner_insurance_plan_type_master ipti', 'ipti.id = Q.insurance_plan_type_id', 'left');
|
||
$builder->join('insurers I', 'I.id = Q.insurer_id', 'left');
|
||
$builder->join('partner_staff S', 'S.id = pe.assigned_to', 'left');
|
||
|
||
|
||
// --- Correction 2: Use Robust Date Range Filtering ---
|
||
if ($month === "current") {
|
||
// WHERE issued_date >= start_of_current_month AND issued_date < start_of_next_month
|
||
$builder->where("pp.issued_date >= DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01')", NULL, FALSE);
|
||
$builder->where("pp.issued_date < DATE_FORMAT(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01')", NULL, FALSE);
|
||
}
|
||
else if ($month === "previous") {
|
||
// WHERE issued_date >= start_of_previous_month AND issued_date < start_of_current_month
|
||
$builder->where("pp.issued_date >= DATE_FORMAT(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01')", NULL, FALSE);
|
||
$builder->where("pp.issued_date < DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01')", NULL, FALSE);
|
||
}
|
||
|
||
// Broker and Manager Filter
|
||
$builder->where('pp.manager_id', $managerId);
|
||
$builder->where('pa.sales_executive_id', $executiveId);
|
||
$builder->where('pa.sales_executive_id IS NOT NULL');
|
||
$builder->where('pse.is_active', 1);
|
||
$builder->where('pp.vehicle_type', $vehicleType);
|
||
$builder->orderBy('pp.issued_date', 'DESC');
|
||
$results = $builder->get()->getResultArray();
|
||
|
||
// Restructure data for PhpSpreadsheet (2D array of values)
|
||
foreach ($results as $row) {
|
||
$data[] = array_values($row);
|
||
}
|
||
|
||
} catch (\CodeIgniter\Database\Exceptions\DatabaseException $e) {
|
||
log_message('error', 'Database Error during Excel fetch: ' . $e->getMessage());
|
||
// Re-throw the exception so it is caught by the main function's catch block
|
||
throw $e;
|
||
}
|
||
|
||
return $data;
|
||
}
|
||
|
||
|
||
|
||
/**
|
||
* Executes the detailed policy query and extracts the data for the spreadsheet.
|
||
* @param string $managerId
|
||
* @param int $AgentId
|
||
* @param string $month ('current' or 'previous')
|
||
* @return array
|
||
*/
|
||
private function getDataPerformingAgentsByID($managerId, $AgentId,$fromDate,$toDate): array
|
||
{
|
||
$data = [];
|
||
|
||
try {
|
||
$builder = $this->db->table('partner_policy pp');
|
||
|
||
// 1. Properly aligned Select Statement
|
||
$builder->select('
|
||
DATE_FORMAT(pe.created_on, "%d-%m-%Y %h:%i %p") AS received_date,
|
||
S.name AS assigned_to_name,
|
||
I.name AS insurer_name,
|
||
I.short_name AS insurer_short_name,
|
||
pe.name AS insured_name,
|
||
pe.reg_no,
|
||
pm.value AS payment_mode_value,
|
||
ipti.insurance_plan_type,
|
||
DATE_FORMAT(pp.issued_date, "%d-%m-%Y") AS issued_date,
|
||
pa.agent_code AS agent_code,
|
||
pa.name AS agent_name,
|
||
CONCAT("`", pp.policy_number) AS policy_number,
|
||
CONCAT("₹ ", FORMAT(pp.premium_amount, 2, "en_IN")) AS premium_amount,
|
||
pp.premium_amount AS raw_premium');
|
||
|
||
// 2. Joins
|
||
$builder->join('partner_agent pa', 'pp.agent_id = pa.id', 'inner');
|
||
$builder->join('partner_enquiry pe', 'pe.id = pp.enquiry_id', 'left');
|
||
$builder->join('partner_brokers pb', 'pb.id = pe.broker_id', 'left');
|
||
$builder->join('partner_quotation Q', 'Q.enquiry_id = pe.id', 'left');
|
||
$builder->join('partner_payment_mode_master pm', 'pm.id = Q.payment_mode_id', 'left');
|
||
$builder->join('partner_insurance_plan_type_master ipti', 'ipti.id = Q.insurance_plan_type_id', 'left');
|
||
$builder->join('insurers I', 'I.id = Q.insurer_id', 'left');
|
||
$builder->join('partner_staff S', 'S.id = pe.assigned_to', 'left');
|
||
|
||
// 3. Where Conditions
|
||
// $builder->where('pp.manager_id', $managerId);
|
||
$builder->where('pp.policy_number IS NOT NULL');
|
||
$builder->where('pa.id', $AgentId);
|
||
|
||
// $fromDate = date('Y-m-d', strtotime('-10 days'));
|
||
// $toDate = date('Y-m-d');
|
||
|
||
|
||
|
||
$builder->where('pp.issued_date >=', $fromDate);
|
||
$builder->where('pp.issued_date <=', $toDate);
|
||
|
||
|
||
// Current Month Date Range Filter
|
||
// if ($month === "current") {
|
||
// // WHERE issued_date >= start_of_current_month AND issued_date < start_of_next_month
|
||
// $builder->where("pp.issued_date >= DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01')", NULL, FALSE);
|
||
// $builder->where("pp.issued_date < DATE_FORMAT(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01')", NULL, FALSE);
|
||
// }
|
||
// else if ($month === "previous") {
|
||
// // WHERE issued_date >= start_of_previous_month AND issued_date < start_of_current_month
|
||
// $builder->where("pp.issued_date >= DATE_FORMAT(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01')", NULL, FALSE);
|
||
// $builder->where("pp.issued_date < DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01')", NULL, FALSE);
|
||
// }
|
||
|
||
// 4. Order and Limit
|
||
$builder->orderBy('pp.issued_date', 'DESC');
|
||
|
||
// 5. Fetch and Restructure
|
||
$results = $builder->get()->getResultArray();
|
||
|
||
foreach ($results as $row) {
|
||
// Using array_values ensures we return a indexed 2D array for PhpSpreadsheet
|
||
$data[] = array_values($row);
|
||
}
|
||
|
||
} catch (\CodeIgniter\Database\Exceptions\DatabaseException $e) {
|
||
log_message('error', 'Database Error during Excel fetch: ' . $e->getMessage());
|
||
throw $e;
|
||
}
|
||
|
||
return $data;
|
||
}
|
||
|
||
|
||
public function getDataPerformingAgentsTop50($manager_id,$fromDate,$toDate){
|
||
|
||
$data = [];
|
||
|
||
try {
|
||
$builder = $this->db->table('partner_policy pp');
|
||
// $fromDate = date('Y-m-d', strtotime('-10 days'));
|
||
// $toDate = date('Y-m-d');
|
||
|
||
$builder->select("
|
||
pa.agent_code,
|
||
pa.name AS agent_name,
|
||
COUNT(pp.policy_number) AS policy_count,
|
||
CONCAT('₹ ', FORMAT(SUM(pp.premium_amount), 2, 'en_IN')) AS premium_amount,
|
||
SUM(pp.premium_amount) AS raw_premium");
|
||
|
||
$builder->join('partner_agent pa', 'pp.agent_id = pa.id', 'inner');
|
||
$builder->join('partner_enquiry pe', 'pe.id = pp.enquiry_id', 'left');
|
||
|
||
// $builder->where("MONTH(pp.issued_date) = MONTH('$customDate')", null, false);
|
||
// $builder->where("YEAR(pp.issued_date) = YEAR('$customDate')", null, false);
|
||
$builder->where('pp.issued_date >=', $fromDate);
|
||
$builder->where('pp.issued_date <=', $toDate);
|
||
|
||
// $builder->where('pp.manager_id',$manager_id);
|
||
|
||
$builder->groupBy(['pa.id']);
|
||
$builder->orderBy('policy_count', 'DESC');
|
||
$builder->limit(50);
|
||
|
||
$results = $builder->get()->getResultArray();
|
||
|
||
// Restructure data for PhpSpreadsheet (2D array of values)
|
||
foreach ($results as $row) {
|
||
$data[] = array_values($row);
|
||
}
|
||
|
||
} catch (\CodeIgniter\Database\Exceptions\DatabaseException $e) {
|
||
log_message('error', 'Database Error during Excel fetch: ' . $e->getMessage());
|
||
// Re-throw the exception so it is caught by the main function's catch block
|
||
throw $e;
|
||
}
|
||
|
||
return $data;
|
||
|
||
}
|
||
|
||
/**
|
||
* Executes the detailed policy query and extracts the data for the spreadsheet.
|
||
* @param string $managerId
|
||
* @return array
|
||
*/
|
||
private function getDataAgentsWithoutPolicies($managerId,$fromDate,$toDate): array
|
||
{
|
||
$data = [];
|
||
|
||
try {
|
||
|
||
// 1. Select the required columns
|
||
$builder = $this->db->table('partner_agent pa');
|
||
$builder->select('
|
||
pa.id AS agent_id,
|
||
pa.agent_code,
|
||
pa.name AS agent_name,
|
||
pa.email,
|
||
pa.mobile,
|
||
pa.is_active,
|
||
MAX(hist.issued_date) AS last_issued_date
|
||
');
|
||
|
||
// 2. INNER JOIN ensures they have at least one policy in history (skips new/empty agents)
|
||
$builder->join('partner_policy hist', 'pa.id = hist.agent_id', 'inner');
|
||
|
||
// 3. LEFT JOIN checks for policies in the last 10 days
|
||
// $builder->join('partner_policy recent',
|
||
// "pa.id = recent.agent_id AND recent.issued_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 10 DAY)",
|
||
// 'left'
|
||
// );
|
||
$builder->join('partner_policy recent',"pa.id = recent.agent_id
|
||
AND recent.issued_date >= '{$fromDate}'
|
||
AND recent.issued_date <= '{$toDate}'",
|
||
'left'
|
||
);
|
||
|
||
// 4. Filters
|
||
$builder->where('pa.manager_id', $managerId);
|
||
$builder->where('pa.is_active', 1);
|
||
$builder->where('recent.id', NULL); // Keeps only those with NO activity in the 10-day window
|
||
|
||
// 5. Grouping and Ordering
|
||
$builder->groupBy('pa.id');
|
||
$builder->orderBy('pa.name', 'ASC');
|
||
|
||
// 6. Execute
|
||
$query = $builder->get();
|
||
$results = $query->getResultArray();
|
||
|
||
// 7. To verify the exact SQL for debugging:
|
||
// echo (string) $db->getLastQuery();
|
||
// echo $this->db->getLastQuery()->getQuery();die;
|
||
|
||
|
||
|
||
foreach ($results as $row) {
|
||
// Now you can use unset because $row is an array
|
||
unset($row['agent_id']);
|
||
unset($row['is_active']);
|
||
// If you want to show last_issued_date in the Excel, don't unset it here.
|
||
// If you want to skip it, keep the unset.
|
||
unset($row['last_issued_date']);
|
||
|
||
$data[] = array_values($row);
|
||
}
|
||
|
||
} catch (\CodeIgniter\Database\Exceptions\DatabaseException $e) {
|
||
log_message('error', 'Database Error during Excel fetch: ' . $e->getMessage());
|
||
throw $e;
|
||
}
|
||
|
||
return $data;
|
||
}
|
||
|
||
/**
|
||
* Executes the detailed policy query and extracts the data for the spreadsheet.
|
||
* @param string $managerId
|
||
* @return array
|
||
*/
|
||
//any changed in this query also change in do the same in DashboardController - getDataLowPremiumAgent
|
||
public function getDataLowPremiumAgent($manager_id,$fromDate,$toDate){
|
||
|
||
$builder = $this->db->table('partner_policy pp');
|
||
|
||
$builder->select("
|
||
pa.id AS agent_id,
|
||
pa.agent_code,
|
||
pa.name AS agent_name,
|
||
CASE
|
||
WHEN pa.is_active = 1 THEN 'Active'
|
||
ELSE 'Inactive'
|
||
END AS agent_status,
|
||
MAX(pp.issued_date) AS last_business_date,
|
||
CONCAT('₹ ', FORMAT(SUM(pp.premium_amount), 2, 'en_IN')) AS display_premium,
|
||
SUM(pp.premium_amount) AS total_premium_amount
|
||
");
|
||
|
||
$builder->join('partner_agent pa', 'pa.id = pp.agent_id', 'inner');
|
||
|
||
$builder->where('pp.manager_id', $manager_id);
|
||
|
||
$builder->having('total_premium_amount <', 50000);
|
||
|
||
$builder->where('pp.issued_date >=', $fromDate);
|
||
$builder->where('pp.issued_date <=', $toDate);
|
||
|
||
// RAW condition must be inside where() with FALSE
|
||
// $builder->where("
|
||
// pp.issued_date >= DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 DAY)
|
||
// ", null, false);
|
||
|
||
// groupBy must be array of separate fields
|
||
$builder->groupBy([
|
||
'pa.id',
|
||
'pa.name',
|
||
'pa.agent_code',
|
||
'pa.is_active'
|
||
]);
|
||
|
||
// orderBy must not contain semicolon + must be separated
|
||
$builder->orderBy('total_premium_amount', 'DESC');
|
||
$builder->orderBy('last_business_date', 'ASC');
|
||
return $builder->get()->getResultArray();
|
||
}
|
||
|
||
// Second Comment
|
||
// private function getDataLowPremiumAgent($managerId): array
|
||
// {
|
||
// $data = [];
|
||
|
||
// try {
|
||
// // 1. Get the list of Agent IDs who have TOTAL premium < 50,000
|
||
// $agentIdList = $this->db->table('partner_policy')
|
||
// ->select('agent_id')
|
||
// ->where('manager_id', $managerId)
|
||
// ->groupBy('agent_id')
|
||
// ->having('SUM(premium_amount) <', 50000)
|
||
// ->get()
|
||
// ->getResultArray();
|
||
|
||
// // Extract IDs into a simple array: [1, 5, 10...]
|
||
// $agentIds = array_column($agentIdList, 'agent_id');
|
||
|
||
// // 2. If no agents found, return empty
|
||
// if (empty($agentIds)) {
|
||
// return [];
|
||
// }
|
||
|
||
// // 3. Fetch detailed records for ONLY these agents
|
||
// $builder = $this->db->table('partner_policy pp');
|
||
|
||
// $builder->select("
|
||
// DATE_FORMAT(pe.created_on, '%d-%m-%Y %h:%i %p') AS received_date,
|
||
// S.name as assigned_to_name,
|
||
// I.name as insurer_name,
|
||
// I.short_name as insurer_short_name,
|
||
// pe.name as insured_name,
|
||
// pe.reg_no,
|
||
// pm.value as payment_mode_value,
|
||
// ipti.insurance_plan_type,
|
||
// pa.name AS agent_name,
|
||
// pa.agent_code,
|
||
// CASE
|
||
// WHEN pa.is_active = 1 THEN 'Active Partner'
|
||
// ELSE 'Inactive Partner'
|
||
// END AS agent_status,
|
||
// CONCAT('\t', pp.policy_number) AS policy_number,
|
||
// DATE_FORMAT(pp.issued_date, '%d-%m-%Y') AS issued_date,
|
||
// /* Indian Rupee Formatting for individual policy premium */
|
||
// CONCAT('₹ ', FORMAT(pp.premium_amount, 2, 'en_IN')) AS premium_amount,
|
||
// pp.premium_amount AS raw_premium
|
||
// ");
|
||
|
||
// $builder->join('partner_agent pa', 'pa.id = pp.agent_id', 'inner');
|
||
// $builder->join('partner_enquiry pe', 'pe.id = pp.enquiry_id', 'left');
|
||
// $builder->join('partner_quotation Q', 'Q.enquiry_id = pe.id', 'left');
|
||
// $builder->join('partner_payment_mode_master pm', 'pm.id = Q.payment_mode_id', 'left');
|
||
// $builder->join('partner_insurance_plan_type_master ipti', 'ipti.id = Q.insurance_plan_type_id', 'left');
|
||
// $builder->join('insurers I', 'I.id = Q.insurer_id', 'left');
|
||
// $builder->join('partner_staff S', 'S.id = pe.assigned_to', 'left');
|
||
|
||
// // WHERE filters
|
||
// $builder->where('pp.manager_id', $managerId);
|
||
// $builder->whereIn('pp.agent_id', $agentIds); // <--- This filters by the 104 agents found
|
||
// $builder->where('pp.premium_amount IS NOT NULL');
|
||
|
||
// $builder->orderBy('pp.issued_date', 'DESC');
|
||
|
||
// $results = $builder->get()->getResultArray();
|
||
|
||
// // Restructure for Excel (array_values removes the string keys)
|
||
// foreach ($results as $row) {
|
||
// $data[] = array_values($row);
|
||
// }
|
||
|
||
// } catch (\Exception $e) {
|
||
// log_message('error', 'Excel Fetch Error: ' . $e->getMessage());
|
||
// return [];
|
||
// }
|
||
|
||
// return $data;
|
||
// }
|
||
|
||
// First Comment
|
||
// private function getDataLowPremiumAgent($managerId): array
|
||
// {
|
||
// // --- Correction 1: Initialize Database Connection ---
|
||
|
||
// $data = [];
|
||
|
||
// try {
|
||
// $builder = $this->db->table('partner_policy pp');
|
||
|
||
// $builder->select("
|
||
// DATE_FORMAT(pe.created_on, '%d-%m-%Y %h:%i %p') AS received_date,
|
||
// S.name as assigned_to_name,
|
||
// I.name as insurer_name,I.short_name as insurer_short_name,
|
||
// pe.name as insured_name,
|
||
// pe.reg_no,
|
||
// pm.value as payment_mode_value,
|
||
// ipti.insurance_plan_type,
|
||
// pa.name AS agent_name,
|
||
// pa.agent_code,
|
||
// CASE
|
||
// WHEN pa.is_active = 1 THEN 'Active Partner'
|
||
// ELSE 'Inactive Partner'
|
||
// END AS agent_status,
|
||
// CONCAT('\t', pp.policy_number) AS policy_number,
|
||
// DATE_FORMAT(pp.issued_date, '%d-%m-%Y') AS issued_date,
|
||
// CONCAT('₹ ', FORMAT(pp.premium_amount, 2, 'en_IN')) AS premium_amount,
|
||
// pp.premium_amount AS raw_premium
|
||
// ");
|
||
|
||
// $builder->join('partner_agent pa', 'pa.id = pp.agent_id', 'inner');
|
||
|
||
// $builder->join('partner_enquiry pe', 'pe.id = pp.enquiry_id', 'left');
|
||
// $builder->join('partner_quotation Q', 'Q.enquiry_id = pe.id', 'left');
|
||
// $builder->join('partner_payment_mode_master pm', 'pm.id = Q.payment_mode_id', 'left');
|
||
|
||
// $builder->join('partner_insurance_plan_type_master ipti', 'ipti.id = Q.insurance_plan_type_id', 'left');
|
||
// $builder->join('insurers I', 'I.id = Q.insurer_id', 'left');
|
||
// $builder->join('partner_staff S', 'S.id = pe.assigned_to', 'left');
|
||
|
||
// // WHERE conditions
|
||
// $builder->where('pp.manager_id', $managerId);
|
||
// // $builder->where('pa.id', $agentId);
|
||
// $builder->having('raw_premium <', 50000);
|
||
// $builder->orderBy('pp.issued_date', 'DESC');
|
||
|
||
// $results = $builder->get()->getResultArray();
|
||
|
||
// // Restructure data for PhpSpreadsheet (2D array of values)
|
||
// foreach ($results as $row) {
|
||
// $data[] = array_values($row);
|
||
// }
|
||
|
||
// } catch (DatabaseException $e) {
|
||
// log_message('error', 'Database Error during Excel fetch: ' . $e->getMessage());
|
||
// // Re-throw the exception so it is caught by the main function's catch block
|
||
// throw $e;
|
||
// }
|
||
|
||
// return $data;
|
||
// }
|
||
|
||
private function getDataStaffPendingSummary($managerId): array
|
||
{
|
||
|
||
$data = [];
|
||
|
||
try {
|
||
|
||
$results = $this->db->table('partner_enquiry pe')
|
||
->select("
|
||
ps.name AS staff_name,
|
||
COUNT(pe.id) AS total_assigned,
|
||
SUM(CASE WHEN DATE(pe.created_on) = CURDATE() THEN 1 ELSE 0 END) AS today_assigned,
|
||
SUM(CASE WHEN pe.enquiry_status = 'Assigned' AND DATE(pe.created_on) = CURDATE() THEN 1 ELSE 0 END) AS today_pending,
|
||
SUM(CASE WHEN pe.enquiry_status = 'In progress' AND DATE(pe.created_on) = CURDATE() THEN 1 ELSE 0 END) AS today_in_progress,
|
||
SUM(CASE WHEN pe.enquiry_status = 'Completed' AND DATE(pe.created_on) = CURDATE() THEN 1 ELSE 0 END) AS today_completed,
|
||
/* SUM(CASE WHEN pe.enquiry_status != 'Completed' THEN 1 ELSE 0 END) AS previous_total_pending,
|
||
Logic: (All Pending) - (All Assigned Today) */
|
||
GREATEST(0,
|
||
SUM(CASE WHEN pe.enquiry_status != 'Completed' THEN 1 ELSE 0 END) -
|
||
SUM(CASE WHEN DATE(pe.created_on) = CURDATE() THEN 1 ELSE 0 END)
|
||
) AS previous_total_pending
|
||
")
|
||
->join('partner_staff ps', 'ps.id = pe.assigned_to', 'left')
|
||
->where('pe.manager_id', $managerId)
|
||
->where('pe.is_active', 1)
|
||
->where('pe.assigned_to IS NOT NULL')
|
||
->where('pe.assigned_to !=', 0)
|
||
->groupBy('pe.assigned_to')
|
||
->orderBy('ps.name', 'ASC')
|
||
->get()
|
||
->getResultArray();
|
||
|
||
|
||
// Restructure data for PhpSpreadsheet (2D array of values)
|
||
foreach ($results as $row) {
|
||
unset($row['total_assigned']);
|
||
$data[] = array_values($row);
|
||
}
|
||
|
||
} catch (\CodeIgniter\Database\Exceptions\DatabaseException $e) {
|
||
log_message('error', 'Database Error during Excel fetch: ' . $e->getMessage());
|
||
// Re-throw the exception so it is caught by the main function's catch block
|
||
throw $e;
|
||
}
|
||
|
||
return $data;
|
||
}
|
||
|
||
private function getDataStaffPendingSummaryByID($managerId, $staffId, $status): array
|
||
{
|
||
try {
|
||
$builder = $this->db->table('partner_enquiry pe');
|
||
|
||
|
||
$builder->select('
|
||
DATE_FORMAT(pe.created_on, "%d-%m-%Y %h:%i %p") AS received_date,
|
||
ps.name AS staff_name,
|
||
A.name as agent_name,
|
||
A.agent_code,
|
||
I.name as insurer_name,
|
||
I.short_name as insurer_short_name,
|
||
pe.name as insured_name,
|
||
CONCAT("\t", pe.reg_no) AS reg_no,
|
||
pm.value as payment_mode_value,
|
||
ipti.insurance_plan_type
|
||
', false); // 'false' prevents CI from automatically escaping quotes in functions like DATE_FORMAT
|
||
|
||
$builder->join('partner_policy pp', 'pp.enquiry_id = pe.id', 'left');
|
||
$builder->join('partner_staff ps', 'ps.id = pe.assigned_to', 'left');
|
||
$builder->join('partner_brokers pb', 'pb.id = pe.broker_id', 'left');
|
||
$builder->join('partner_quotation Q', 'Q.enquiry_id = pe.id', 'left');
|
||
$builder->join('partner_payment_mode_master pm', 'pm.id = Q.payment_mode_id', 'left');
|
||
$builder->join('partner_agent A', 'A.id = pe.agent_id', 'left');
|
||
$builder->join('partner_insurance_plan_type_master ipti', 'ipti.id = Q.insurance_plan_type_id', 'left');
|
||
$builder->join('insurers I', 'I.id = Q.insurer_id', 'left');
|
||
|
||
$builder->where('pe.manager_id', $managerId);
|
||
$builder->where('pe.assigned_to', $staffId);
|
||
$builder->where('pe.is_active', 1);
|
||
|
||
// Date boundaries
|
||
$todayStart = date('Y-m-d 00:00:00');
|
||
$todayEnd = date('Y-m-d 23:59:59');
|
||
|
||
switch ($status) {
|
||
case 'TodayAssigned':
|
||
$builder->where('pe.created_on >=', $todayStart);
|
||
$builder->where('pe.created_on <=', $todayEnd);
|
||
break;
|
||
case 'Assigned':
|
||
$builder->where('pe.enquiry_status', 'Assigned');
|
||
$builder->where('pe.created_on >=', $todayStart);
|
||
$builder->where('pe.created_on <=', $todayEnd);
|
||
break;
|
||
case 'InProgress':
|
||
$builder->where('pe.enquiry_status', 'In progress');
|
||
$builder->where('pe.created_on >=', $todayStart);
|
||
$builder->where('pe.created_on <=', $todayEnd);
|
||
break;
|
||
case 'Completed':
|
||
$builder->where('pe.enquiry_status', 'Completed');
|
||
$builder->where('pe.created_on >=', $todayStart);
|
||
$builder->where('pe.created_on <=', $todayEnd);
|
||
break;
|
||
|
||
case 'PrevPending':
|
||
/** * To match your Summary Logic (SUM CASE WHEN status != 'Completed'):
|
||
* We remove the date restriction.
|
||
* This shows ALL records that are not completed, regardless of date.
|
||
*/
|
||
$builder->where('pe.enquiry_status !=', 'Completed');
|
||
$builder->where('pe.created_on <', $todayStart);
|
||
break;
|
||
}
|
||
|
||
$results = $builder->get()->getResultArray();
|
||
|
||
$data = [];
|
||
foreach ($results as $row) {
|
||
$data[] = array_values($row);
|
||
}
|
||
|
||
return $data;
|
||
|
||
} catch (\Exception $e) {
|
||
log_message('error', 'Excel Fetch Error: ' . $e->getMessage());
|
||
throw $e;
|
||
}
|
||
}
|
||
|
||
|
||
private function getDataEndorsement($managerId, $search = '',
|
||
$agent_id = null,
|
||
$policy_number = null,
|
||
$from_date = null,
|
||
$to_date = null,
|
||
$endorsement_type = null,
|
||
$insurer_id = null,
|
||
$status = null,
|
||
$verification = null
|
||
): array
|
||
{
|
||
try {
|
||
$builder = $this->db->table('partner_endorsement_request per');
|
||
$builder->select("
|
||
DATE_FORMAT(per.created_at, '%d-%m-%Y %h:%i %p') AS received_date,
|
||
per.policy_from,
|
||
ps.name AS staff_name,
|
||
per.policy_number,
|
||
CASE
|
||
WHEN per.policy_start_date = '0000-00-00'
|
||
OR YEAR(per.policy_start_date) < 2000
|
||
THEN NULL
|
||
ELSE DATE_FORMAT(per.policy_start_date, '%Y-%m-%d')
|
||
END AS policy_start_date,
|
||
CASE
|
||
WHEN per.policy_end_date = '0000-00-00'
|
||
OR YEAR(per.policy_end_date) < 2000
|
||
THEN NULL
|
||
ELSE DATE_FORMAT(per.policy_end_date, '%Y-%m-%d')
|
||
END AS policy_end_date,
|
||
per.reg_no,
|
||
petm.endorsement_type,
|
||
per.endorsement_no,
|
||
per.insured_name,
|
||
CONCAT(I.name, ' - ', I.short_name) AS insurer_name,
|
||
pb.name AS broker_name,
|
||
CONCAT(pa.agent_code, ' - ', pa.name) AS agent_details,
|
||
per.contact_person,
|
||
per.endorsement_description,
|
||
per.financia_or_non_financial,
|
||
per.status,
|
||
CASE
|
||
WHEN per.is_data_accuracy_checked = 0 THEN 'To Verify'
|
||
ELSE 'Verified'
|
||
END AS verification_status,
|
||
CASE
|
||
WHEN per.status = 'Closed' THEN '-'
|
||
ELSE CONCAT(DATEDIFF(CURDATE(), per.created_at),' days')
|
||
END AS pending_days,
|
||
per.endorsement_premium,
|
||
per.commission_amount,
|
||
");
|
||
|
||
// ✅ Correct joins (NO duplicates)
|
||
$builder->join('partner_policy pp', 'pp.policy_number = per.policy_number', 'left');
|
||
$builder->join('partner_enquiry pe', 'pe.id = pp.enquiry_id', 'left');
|
||
$builder->join('partner_agent pa', 'pa.id = per.agent_id', 'left');
|
||
$builder->join('partner_staff ps', 'ps.id = per.created_by', 'left');
|
||
$builder->join('insurers I', 'I.id = per.insurer_id', 'left');
|
||
$builder->join('partner_endorsement_type_master petm', 'petm.id = per.endorsement_type', 'left');
|
||
$builder->join('partner_brokers pb', 'pb.id = per.broker_id', 'left');
|
||
$builder->join('clients c', 'c.id = per.client_id', 'left');
|
||
$builder->join('partner_payment_mode_master pm', 'pm.id = per.payment_mode_id', 'left');
|
||
$builder->where('per.manager_id', $managerId);
|
||
$builder->where('per.is_active', 1);
|
||
|
||
// Filters
|
||
if (!empty($agent_id)) {
|
||
$builder->where('per.agent_id', $agent_id);
|
||
}
|
||
|
||
if (!empty($policy_number)) {
|
||
$builder->where('per.policy_number', $policy_number);
|
||
}
|
||
|
||
if (!empty($from_date) && !empty($to_date)) {
|
||
$builder->where('per.created_at >=', $from_date . ' 00:00:00');
|
||
$builder->where('per.created_at <=', $to_date . ' 23:59:59');
|
||
}
|
||
|
||
if (!empty($endorsement_type) && $endorsement_type != 'All') {
|
||
$builder->where('per.endorsement_type', $endorsement_type);
|
||
}
|
||
|
||
if (!empty($insurer_id)) {
|
||
$builder->where('per.insurer_id', $insurer_id);
|
||
}
|
||
|
||
if (!empty($status) && $status != 'All') {
|
||
$builder->where('per.status', $status);
|
||
}
|
||
|
||
if (!empty($verification) && $verification != 'All') {
|
||
if ($verification == 'Verified') {
|
||
$builder->where('per.is_data_accuracy_checked', 1);
|
||
} elseif ($verification == 'To Verify') {
|
||
$builder->where('per.is_data_accuracy_checked', 0);
|
||
}
|
||
}
|
||
|
||
// Search filter
|
||
// if (!empty($search)) {
|
||
// $search = strtolower(trim($search));
|
||
|
||
// $builder->groupStart()
|
||
// ->like('LOWER(per.policy_from)', $search)
|
||
// ->orLike('LOWER(per.status)', $search)
|
||
// ->orLike('LOWER(per.reg_no)', $search)
|
||
// ->orLike('LOWER(per.policy_start_date)', $search)
|
||
// ->orLike('LOWER(per.policy_end_date)', $search)
|
||
// ->orLike('LOWER(I.name)', $search)
|
||
// ->orLike('LOWER(I.insurer_short_name)', $search)
|
||
// ->orLike('LOWER(per.policy_number)', $search)
|
||
// ->orLike('LOWER(petm.endorsement_type)', $search)
|
||
// ->orLike('LOWER(per.endorsement_no)', $search)
|
||
// ->groupEnd();
|
||
// }
|
||
if (!empty($search)) {
|
||
$search = strtolower(trim($search));
|
||
|
||
$builder->groupStart()
|
||
|
||
// Normal columns
|
||
->like('LOWER(per.policy_from)', $search)
|
||
->orLike('LOWER(per.status)', $search)
|
||
->orLike('LOWER(per.reg_no)', $search)
|
||
->orLike('LOWER(per.policy_number)', $search)
|
||
->orLike('LOWER(per.endorsement_no)', $search)
|
||
|
||
// Insurer
|
||
->orLike('LOWER(I.name)', $search)
|
||
->orLike('LOWER(I.short_name)', $search)
|
||
|
||
// Endorsement type
|
||
->orLike('LOWER(petm.endorsement_type)', $search)
|
||
|
||
// ✅ Broker name
|
||
->orLike('LOWER(pb.name)', $search)
|
||
|
||
// ✅ Agent code + name (CONCAT)
|
||
->orLike("LOWER(CONCAT(pa.agent_code, ' - ', pa.name))", $search)
|
||
->orLike("LOWER(pa.agent_code)", $search)
|
||
->orLike("LOWER(pa.name)", $search)
|
||
|
||
// ✅ Verification (CASE condition)
|
||
->orLike("
|
||
LOWER(
|
||
CASE
|
||
WHEN per.is_data_accuracy_checked = 0 THEN 'to verify'
|
||
ELSE 'verified'
|
||
END
|
||
)", $search)
|
||
|
||
// ✅ Received date (formatted)
|
||
->orLike("DATE_FORMAT(per.created_at, '%d-%m-%Y %h:%i %p')", $search)
|
||
|
||
// ✅ Policy dates (formatted)
|
||
->orLike("DATE_FORMAT(per.policy_start_date, '%Y-%m-%d')", $search)
|
||
->orLike("DATE_FORMAT(per.policy_end_date, '%Y-%m-%d')", $search)
|
||
|
||
->groupEnd();
|
||
}
|
||
|
||
|
||
$builder->groupBy('per.id');
|
||
|
||
$results = $builder->get()->getResultArray();
|
||
|
||
// Convert associative rows to indexed rows (Excel-friendly)
|
||
$data = [];
|
||
foreach ($results as $row) {
|
||
$data[] = array_values($row);
|
||
}
|
||
// foreach ($results as $row) {
|
||
// foreach ($row as $key => $value) {
|
||
// if ($value === null || $value === '') {
|
||
// $row[$key] = '-';
|
||
// }
|
||
// }
|
||
// $data[] = array_values($row);
|
||
// }
|
||
|
||
|
||
return $data;
|
||
|
||
} catch (\Exception $e) {
|
||
log_message('error', 'Excel Fetch Error: ' . $e->getMessage());
|
||
throw $e;
|
||
}
|
||
}
|
||
|
||
// grid/download?role=Manager means all list (optional insurer, rto, segment, vehicle_type, search, logged_id)
|
||
public function downloadExcelGrid()
|
||
{
|
||
try {
|
||
$request = $this->request;
|
||
|
||
// ✅ Inputs
|
||
$role = trim((string) ($request->getGet('role') ?? ''));
|
||
$fileId = trim((string) ($request->getGet('file_id') ?? ''));
|
||
$insurer = trim((string) ($request->getGet('insurer') ?? ''));
|
||
$rto = trim((string) ($request->getGet('rto') ?? ''));
|
||
$segment = trim((string) ($request->getGet('segment') ?? ''));
|
||
$vehicleType = trim((string) ($request->getGet('vehicle_type') ?? ''));
|
||
$search = trim((string) ($request->getGet('search') ?? ''));
|
||
$loggedId = trim((string) ($request->getGet('logged_id') ?? ''));
|
||
|
||
if ($role === '') {
|
||
return $this->response->setJSON([
|
||
'status' => 'failed',
|
||
'code' => 400,
|
||
'data' => 'Role is required to export payout grid.',
|
||
]);
|
||
}
|
||
|
||
$isAgent = strtolower($role) === 'agent';
|
||
|
||
// ✅ partner_VT / partner_RR + Agent comp/tp/od = partner_RR − original (Agent only)
|
||
$metaMap = [];
|
||
$masterVt = [];
|
||
if ($isAgent && $loggedId !== '') {
|
||
$agentPk = (int) $loggedId;
|
||
$metaMap = PartnerPayoutGridRetention::buildPartnerMetaMap($this->db, $agentPk);
|
||
$masterVt = PartnerPayoutGridRetention::buildMasterNormLabelToVehicleTypeIdMap($this->db);
|
||
}
|
||
|
||
// ✅ Main query
|
||
$builder = $this->db->table('partner_insurance_payout_grid');
|
||
|
||
if ($fileId !== '') {
|
||
$builder->where('partner_agent_incentive_file_id', $fileId);
|
||
} else {
|
||
$maxFileId = $this->db->table('partner_insurance_payout_grid')
|
||
->selectMax('partner_agent_incentive_file_id')
|
||
->get()
|
||
->getRow()
|
||
->partner_agent_incentive_file_id ?? null;
|
||
|
||
if (!empty($maxFileId)) {
|
||
$builder->where('partner_agent_incentive_file_id', $maxFileId);
|
||
}
|
||
}
|
||
|
||
// ✅ Filters
|
||
if ($insurer !== '') $builder->where('insurer', $insurer);
|
||
if ($rto !== '') $builder->where('rto', $rto);
|
||
if ($segment !== '') $builder->where('segment', $segment);
|
||
if ($vehicleType !== '') $builder->where('vehicle_type', $vehicleType);
|
||
|
||
// ✅ Search
|
||
if ($search !== '') {
|
||
$builder->groupStart()
|
||
->like('insurer', $search)
|
||
->orLike('vehicle_type', $search)
|
||
->orLike('segment', $search)
|
||
->orLike('rto', $search)
|
||
->orLike('remarks', $search)
|
||
->groupEnd();
|
||
}
|
||
|
||
// ✅ Fetch rows
|
||
$rows = $builder
|
||
->select('id, insurer, vehicle_type, segment, rto, comp, tp, od, remarks')
|
||
->orderBy('id', 'DESC')
|
||
->get()
|
||
->getResultArray();
|
||
|
||
// =========================
|
||
// ✅ HEADERS
|
||
// =========================
|
||
$headers = ['S.No', 'Insurer', 'Vehicle Type', 'Segment' , 'RTO', 'Comp', 'TP' , 'OD', 'Remarks'];
|
||
|
||
// =========================
|
||
// ✅ DATA
|
||
// =========================
|
||
$data = [];
|
||
$sNo = 1;
|
||
|
||
foreach ($rows as $row) {
|
||
|
||
$compRaw = $row['comp'] ?? null;
|
||
$tpRaw = $row['tp'] ?? null;
|
||
$odRaw = $row['od'] ?? null;
|
||
|
||
$compOut = PartnerPayoutGridRetention::gridPayoutDisplayManagerAccounts($compRaw);
|
||
$tpOut = PartnerPayoutGridRetention::gridPayoutDisplayManagerAccounts($tpRaw);
|
||
$odOut = PartnerPayoutGridRetention::gridPayoutDisplayManagerAccounts($odRaw);
|
||
|
||
$pm = null;
|
||
// ✅ Agent + logged_id: PRR match + per-column calc only when original value is positive
|
||
if ($isAgent && $loggedId !== '') {
|
||
$pm = PartnerPayoutGridRetention::resolvePartnerMetaForGridRow($row, $metaMap, $masterVt);
|
||
if ($pm === null) {
|
||
$compOut = '-';
|
||
$tpOut = '-';
|
||
$odOut = '-';
|
||
} else {
|
||
$rr = (float) $pm['partner_RR'];
|
||
$compOut = PartnerPayoutGridRetention::agentPayoutColumnFromPartnerRr($compRaw, $rr);
|
||
$tpOut = PartnerPayoutGridRetention::agentPayoutColumnFromPartnerRr($tpRaw, $rr);
|
||
$odOut = PartnerPayoutGridRetention::agentPayoutColumnFromPartnerRr($odRaw, $rr);
|
||
}
|
||
}
|
||
|
||
$line = [
|
||
$sNo++,
|
||
$row['insurer'] ?? '-',
|
||
$row['vehicle_type'] ?? '-',
|
||
$row['segment'] ?? '-',
|
||
$row['rto'] ?? '-',
|
||
];
|
||
|
||
$line[] = $compOut;
|
||
$line[] = $tpOut;
|
||
$line[] = $odOut;
|
||
$line[] = $row['remarks'] ?? '-';
|
||
|
||
$data[] = $line;
|
||
}
|
||
|
||
// ✅ Export
|
||
$fileName = "Payout_" . date('Ymd_His') . ".xlsx";
|
||
|
||
return $this->streamExcelFile($headers, $data, 'Payout', $fileName, false);
|
||
|
||
} catch (\Throwable $e) {
|
||
return $this->response->setJSON([
|
||
'status' => 'failed',
|
||
'code' => 500,
|
||
'data' => $e->getMessage(),
|
||
]);
|
||
}
|
||
}
|
||
|
||
public function exportAgentRetentionRateExcel()
|
||
{
|
||
try {
|
||
$vehicleTypes = $this->db->table('vehicle_type')
|
||
->select('id, vehicle_type')
|
||
->where('is_active', 1)
|
||
->orderBy('vehicle_type', 'ASC')
|
||
->get()
|
||
->getResultArray();
|
||
|
||
$segments = $this->db->table('partner_segment ps')
|
||
->select('ps.id, ps.segment, ps.vehicle_type_id, vt.vehicle_type')
|
||
->join('vehicle_type vt', 'vt.id = ps.vehicle_type_id', 'inner')
|
||
->where('ps.is_active', 1)
|
||
->where('vt.is_active', 1)
|
||
->orderBy('vt.vehicle_type', 'ASC')
|
||
->orderBy('ps.vehicle_type_id', 'ASC')
|
||
->orderBy('ps.id', 'ASC')
|
||
->orderBy('ps.segment', 'ASC')
|
||
->get()
|
||
->getResultArray();
|
||
|
||
$agents = $this->db->table('partner_agent')
|
||
->select('id, agent_code')
|
||
->where('is_active', 1)
|
||
->where('agent_code IS NOT NULL', null, false)
|
||
->where('agent_code !=', '')
|
||
->orderBy('agent_code', 'ASC')
|
||
->get()
|
||
->getResultArray();
|
||
|
||
$rates = $this->db->table('partner_retention_rate')
|
||
->select('agent_id, vehicle_type_id, segment_id, retention_rate')
|
||
->where('is_active', 1)
|
||
->get()
|
||
->getResultArray();
|
||
|
||
$rateMap = [];
|
||
foreach ($rates as $r) {
|
||
$aId = (int) ($r['agent_id'] ?? 0);
|
||
$vId = (int) ($r['vehicle_type_id'] ?? 0);
|
||
$sId = (int) ($r['segment_id'] ?? 0);
|
||
if ($aId <= 0 || $vId <= 0 || $sId <= 0) {
|
||
continue;
|
||
}
|
||
$rateMap[$aId . '_' . $vId . '_' . $sId] = (float) ($r['retention_rate'] ?? 0);
|
||
}
|
||
|
||
$segmentRows = [];
|
||
foreach ($segments as $seg) {
|
||
$vId = (int) ($seg['vehicle_type_id'] ?? 0);
|
||
$sId = (int) ($seg['id'] ?? 0);
|
||
$vehicleTypeName = (string) ($seg['vehicle_type'] ?? '');
|
||
if ($vId <= 0 || $sId <= 0 || $vehicleTypeName === '') {
|
||
continue;
|
||
}
|
||
$segmentRows[] = [
|
||
'vehicle_type_id' => $vId,
|
||
'vehicle_type' => $vehicleTypeName,
|
||
'segment_id' => $sId,
|
||
'segment' => (string) ($seg['segment'] ?? ''),
|
||
];
|
||
}
|
||
|
||
$spreadsheet = new Spreadsheet();
|
||
$sheet = $spreadsheet->getActiveSheet();
|
||
|
||
$sheet->setCellValue('A1', 'Vehicle Type');
|
||
$sheet->setCellValue('B1', 'Segment');
|
||
|
||
$colIndex = 3;
|
||
foreach ($agents as $agent) {
|
||
$cell = Coordinate::stringFromColumnIndex($colIndex++) . '1';
|
||
$sheet->setCellValue($cell, (string) ($agent['agent_code'] ?? ''));
|
||
}
|
||
|
||
$rowIndex = 2;
|
||
foreach ($segmentRows as $sr) {
|
||
$vehicleTypeId = (int) $sr['vehicle_type_id'];
|
||
$segmentId = (int) $sr['segment_id'];
|
||
$sheet->setCellValue('A' . $rowIndex, (string) $sr['vehicle_type']);
|
||
$sheet->setCellValue('B' . $rowIndex, (string) $sr['segment']);
|
||
|
||
$colIndex = 3;
|
||
foreach ($agents as $agent) {
|
||
$agentId = (int) ($agent['id'] ?? 0);
|
||
$key = $agentId . '_' . $vehicleTypeId . '_' . $segmentId;
|
||
$cell = Coordinate::stringFromColumnIndex($colIndex++) . $rowIndex;
|
||
$sheet->setCellValue($cell, (float) ($rateMap[$key] ?? 0));
|
||
}
|
||
$rowIndex++;
|
||
}
|
||
|
||
$lastCol = Coordinate::stringFromColumnIndex(max(2, count($agents) + 2));
|
||
$lastRow = max(1, $rowIndex - 1);
|
||
$sheet->getStyle("A1:{$lastCol}1")->getFont()->setBold(true);
|
||
$sheet->getStyle("A1:{$lastCol}{$lastRow}")->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
|
||
|
||
// Input validation for retention cells:
|
||
// - Applies to C2:lastCol(lastRow)
|
||
// - Allows decimal value between 0 and 100 only
|
||
// - Blocks negatives, values > 100, and text/special characters
|
||
if (count($agents) > 0 && $lastRow >= 2) {
|
||
$validation = $sheet->getCell('C2')->getDataValidation();
|
||
$validation->setType(DataValidation::TYPE_DECIMAL);
|
||
$validation->setErrorStyle(DataValidation::STYLE_STOP);
|
||
$validation->setAllowBlank(true);
|
||
$validation->setShowInputMessage(true);
|
||
$validation->setShowErrorMessage(true);
|
||
$validation->setOperator(DataValidation::OPERATOR_BETWEEN);
|
||
$validation->setFormula1('0');
|
||
$validation->setFormula2('100');
|
||
$validation->setPromptTitle('Valid retention rate');
|
||
$validation->setPrompt('Enter a number between 0 and 100');
|
||
$validation->setErrorTitle('Invalid value');
|
||
$validation->setError('Only numeric values from 0 to 100 are allowed.');
|
||
for ($row = 2; $row <= $lastRow; $row++) {
|
||
for ($col = 3; $col <= (count($agents) + 2); $col++) {
|
||
$cell = Coordinate::stringFromColumnIndex($col) . $row;
|
||
$sheet->getCell($cell)->setDataValidation(clone $validation);
|
||
}
|
||
}
|
||
}
|
||
|
||
for ($i = 1; $i <= count($agents) + 2; $i++) {
|
||
$col = Coordinate::stringFromColumnIndex($i);
|
||
$sheet->getColumnDimension($col)->setAutoSize(true);
|
||
}
|
||
|
||
$fileName = 'RentationRate_' . date('Ymd_His') . '.xlsx';
|
||
while (ob_get_level() > 0) {
|
||
ob_end_clean();
|
||
}
|
||
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
|
||
header('Content-Disposition: attachment; filename="' . $fileName . '"');
|
||
header('Cache-Control: max-age=0');
|
||
$writer = new Xlsx($spreadsheet);
|
||
$writer->save('php://output');
|
||
exit();
|
||
} catch (\Throwable $e) {
|
||
return $this->response->setJSON([
|
||
'status' => 'failed',
|
||
'code' => 500,
|
||
'data' => $e->getMessage(),
|
||
]);
|
||
}
|
||
}
|
||
|
||
public function importAgentRetentionRateExcel()
|
||
{
|
||
try {
|
||
$file = $this->request->getFile('retention_excel');
|
||
if (!$file || !$file->isValid()) {
|
||
$file = $this->request->getFile('retention_rate_excel');
|
||
}
|
||
if (!$file || !$file->isValid()) {
|
||
return $this->respond([
|
||
'status' => 'failed',
|
||
'code' => 200,
|
||
'data' => 'Valid Excel file is required in retention_excel / retention_rate_excel',
|
||
], 200);
|
||
}
|
||
|
||
$spreadsheet = IOFactory::load($file->getTempName());
|
||
$sheet = $spreadsheet->getActiveSheet();
|
||
$rows = $sheet->toArray(null, true, true, false);
|
||
|
||
if (empty($rows) || empty($rows[0])) {
|
||
return $this->respond([
|
||
'status' => 'failed',
|
||
'code' => 200,
|
||
'data' => 'Excel file is empty',
|
||
], 200);
|
||
}
|
||
|
||
$headerRow = $rows[0];
|
||
$headerAgents = [];
|
||
for ($col = 2; $col < count($headerRow); $col++) {
|
||
$name = trim((string) ($headerRow[$col] ?? ''));
|
||
if ($name === '') {
|
||
continue;
|
||
}
|
||
$headerAgents[$col] = strtolower($name);
|
||
}
|
||
|
||
if (empty($headerAgents)) {
|
||
return $this->respond([
|
||
'status' => 'failed',
|
||
'code' => 200,
|
||
'data' => 'Agent code headers are missing in row 1',
|
||
], 200);
|
||
}
|
||
|
||
$vehicleTypeRows = $this->db->table('vehicle_type')
|
||
->select('id, vehicle_type')
|
||
->where('is_active', 1)
|
||
->get()
|
||
->getResultArray();
|
||
$vehicleTypeMap = [];
|
||
foreach ($vehicleTypeRows as $vt) {
|
||
$key = strtolower(trim((string) ($vt['vehicle_type'] ?? '')));
|
||
if ($key !== '') {
|
||
$vehicleTypeMap[$key] = (int) $vt['id'];
|
||
}
|
||
}
|
||
|
||
$segmentRows = $this->db->table('partner_segment')
|
||
->select('id, segment, vehicle_type_id')
|
||
->where('is_active', 1)
|
||
->get()
|
||
->getResultArray();
|
||
$segmentMap = [];
|
||
foreach ($segmentRows as $seg) {
|
||
$vId = (int) ($seg['vehicle_type_id'] ?? 0);
|
||
$segName = strtolower(trim((string) ($seg['segment'] ?? '')));
|
||
if ($vId > 0 && $segName !== '') {
|
||
$segmentMap[$vId . '_' . $segName] = (int) $seg['id'];
|
||
}
|
||
}
|
||
|
||
$agentRows = $this->db->table('partner_agent')
|
||
->select('id, agent_code')
|
||
->where('is_active', 1)
|
||
->where('agent_code IS NOT NULL', null, false)
|
||
->where('agent_code !=', '')
|
||
->get()
|
||
->getResultArray();
|
||
$agentMap = [];
|
||
foreach ($agentRows as $agent) {
|
||
$key = strtolower(trim((string) ($agent['agent_code'] ?? '')));
|
||
if ($key !== '') {
|
||
$agentMap[$key] = (int) $agent['id'];
|
||
}
|
||
}
|
||
|
||
$unknownAgentCodes = [];
|
||
$validHeaderAgents = [];
|
||
foreach ($headerAgents as $colIndex => $agentCode) {
|
||
if (isset($agentMap[$agentCode])) {
|
||
$validHeaderAgents[$colIndex] = $agentMap[$agentCode];
|
||
} else {
|
||
$unknownAgentCodes[] = trim((string) ($headerRow[$colIndex] ?? ''));
|
||
}
|
||
}
|
||
|
||
if (empty($validHeaderAgents)) {
|
||
return $this->respond([
|
||
'status' => 'failed',
|
||
'code' => 200,
|
||
'data' => 'No valid agent code headers found in DB',
|
||
'report' => [
|
||
'unknown_agent_codes' => array_values(array_unique($unknownAgentCodes)),
|
||
],
|
||
], 200);
|
||
}
|
||
|
||
$existingRows = $this->db->table('partner_retention_rate')
|
||
->select('id, agent_id, vehicle_type_id, segment_id, retention_rate')
|
||
->get()
|
||
->getResultArray();
|
||
|
||
$existingMap = [];
|
||
foreach ($existingRows as $er) {
|
||
$key = ((int) $er['agent_id']) . '_' . ((int) $er['vehicle_type_id']) . '_' . ((int) ($er['segment_id'] ?? 0));
|
||
$existingMap[$key] = [
|
||
'id' => (int) $er['id'],
|
||
'rate' => (float) ($er['retention_rate'] ?? 0),
|
||
];
|
||
}
|
||
|
||
$now = date('Y-m-d H:i:s');
|
||
$inserted = 0;
|
||
$updated = 0;
|
||
$skippedZero = 0;
|
||
$skippedSame = 0;
|
||
$skippedEmptyRow = 0;
|
||
$skippedEmptyCell = 0;
|
||
$skippedUnknownVehicleType = 0;
|
||
$skippedUnknownSegment = 0;
|
||
$invalidRange = 0;
|
||
$invalidNumber = 0;
|
||
$unknownVehicleTypes = [];
|
||
$unknownSegments = [];
|
||
$invalidCells = [];
|
||
$agentWiseSkippedCounts = []; // keyed by row reference "vehicle_type | segment"
|
||
|
||
$this->db->transStart();
|
||
|
||
for ($r = 1; $r < count($rows); $r++) {
|
||
$row = $rows[$r];
|
||
$vehicleTypeNameRaw = trim((string) ($row[0] ?? ''));
|
||
$segmentNameRaw = trim((string) ($row[1] ?? ''));
|
||
if ($vehicleTypeNameRaw === '' && $segmentNameRaw === '') {
|
||
$skippedEmptyRow++;
|
||
continue;
|
||
}
|
||
|
||
$vehicleTypeKey = strtolower($vehicleTypeNameRaw);
|
||
if (!isset($vehicleTypeMap[$vehicleTypeKey])) {
|
||
$skippedUnknownVehicleType++;
|
||
if ($vehicleTypeNameRaw !== '') {
|
||
$unknownVehicleTypes[] = $vehicleTypeNameRaw;
|
||
}
|
||
continue;
|
||
}
|
||
|
||
$vehicleTypeId = (int) $vehicleTypeMap[$vehicleTypeKey];
|
||
$segmentKey = strtolower($segmentNameRaw);
|
||
$segmentMapKey = $vehicleTypeId . '_' . $segmentKey;
|
||
if ($segmentNameRaw === '' || !isset($segmentMap[$segmentMapKey])) {
|
||
$skippedUnknownSegment++;
|
||
if ($segmentNameRaw !== '') {
|
||
$unknownSegments[] = $segmentNameRaw;
|
||
}
|
||
continue;
|
||
}
|
||
$segmentId = (int) $segmentMap[$segmentMapKey];
|
||
|
||
$rowRef = trim($vehicleTypeNameRaw . ' | ' . $segmentNameRaw);
|
||
if ($rowRef !== '' && !isset($agentWiseSkippedCounts[$rowRef])) {
|
||
$agentWiseSkippedCounts[$rowRef] = 0;
|
||
}
|
||
|
||
foreach ($validHeaderAgents as $colIndex => $agentId) {
|
||
$rawRate = $row[$colIndex] ?? null;
|
||
$rawText = trim((string) $rawRate);
|
||
|
||
if ($rawText === '') {
|
||
$skippedEmptyCell++;
|
||
if ($rowRef !== '') {
|
||
$agentWiseSkippedCounts[$rowRef]++;
|
||
}
|
||
continue;
|
||
}
|
||
|
||
if (!is_numeric($rawText)) {
|
||
$invalidNumber++;
|
||
if ($rowRef !== '') {
|
||
$agentWiseSkippedCounts[$rowRef]++;
|
||
}
|
||
if (count($invalidCells) < 20) {
|
||
$invalidCells[] = "R" . ($r + 1) . "C" . ($colIndex + 1) . " invalid number";
|
||
}
|
||
continue;
|
||
}
|
||
|
||
$rate = (float) $rawText;
|
||
if (!is_finite($rate)) {
|
||
$invalidNumber++;
|
||
if ($rowRef !== '') {
|
||
$agentWiseSkippedCounts[$rowRef]++;
|
||
}
|
||
if (count($invalidCells) < 20) {
|
||
$invalidCells[] = "R" . ($r + 1) . "C" . ($colIndex + 1) . " invalid number";
|
||
}
|
||
continue;
|
||
}
|
||
|
||
if ($rate < 0 || $rate > 100) {
|
||
$invalidRange++;
|
||
if ($rowRef !== '') {
|
||
$agentWiseSkippedCounts[$rowRef]++;
|
||
}
|
||
if (count($invalidCells) < 20) {
|
||
$invalidCells[] = "R" . ($r + 1) . "C" . ($colIndex + 1) . " out of range (0-100)";
|
||
}
|
||
continue;
|
||
}
|
||
|
||
// Performance optimization:
|
||
// Skip zero values to avoid unnecessary DB lookup/write.
|
||
if (round($rate, 2) == 0.00) {
|
||
$skippedZero++;
|
||
if ($rowRef !== '') {
|
||
$agentWiseSkippedCounts[$rowRef]++;
|
||
}
|
||
continue;
|
||
}
|
||
|
||
$key = $agentId . '_' . $vehicleTypeId . '_' . $segmentId;
|
||
$existing = $existingMap[$key] ?? null;
|
||
|
||
if ($existing) {
|
||
if (round((float) $existing['rate'], 2) === round((float) $rate, 2)) {
|
||
$skippedSame++;
|
||
if ($rowRef !== '') {
|
||
$agentWiseSkippedCounts[$rowRef]++;
|
||
}
|
||
continue;
|
||
}
|
||
$this->db->table('partner_retention_rate')
|
||
->where('id', (int) $existing['id'])
|
||
->update([
|
||
'retention_rate' => round($rate, 2),
|
||
'is_active' => 1,
|
||
'updated_on' => $now,
|
||
]);
|
||
$existingMap[$key]['rate'] = round($rate, 2);
|
||
$updated++;
|
||
} else {
|
||
$this->db->table('partner_retention_rate')
|
||
->insert([
|
||
'agent_id' => $agentId,
|
||
'vehicle_type_id' => $vehicleTypeId,
|
||
'segment_id' => $segmentId,
|
||
'retention_rate' => round($rate, 2),
|
||
'is_active' => 1,
|
||
'created_on' => $now,
|
||
]);
|
||
$existingMap[$key] = [
|
||
'id' => (int) $this->db->insertID(),
|
||
'rate' => round($rate, 2),
|
||
];
|
||
$inserted++;
|
||
}
|
||
}
|
||
}
|
||
|
||
$this->db->transComplete();
|
||
if ($this->db->transStatus() === false) {
|
||
throw new \RuntimeException('Import transaction failed');
|
||
}
|
||
|
||
return $this->respond([
|
||
'status' => 'success',
|
||
'code' => 200,
|
||
'data' => [
|
||
'inserted_rows' => $inserted,
|
||
'updated_rows' => $updated,
|
||
'inserted_cells' => $inserted,
|
||
'updated_cells' => $updated,
|
||
'skipped_zero' => $skippedZero,
|
||
'skipped_same' => $skippedSame,
|
||
'skipped_empty_row' => $skippedEmptyRow,
|
||
'skipped_empty_cell' => $skippedEmptyCell,
|
||
'skipped_unknown_vehicle_type' => $skippedUnknownVehicleType,
|
||
'skipped_unknown_segment' => $skippedUnknownSegment,
|
||
'invalid_range' => $invalidRange,
|
||
'invalid_number' => $invalidNumber,
|
||
],
|
||
'report' => [
|
||
'unknown_agent_codes' => array_values(array_unique($unknownAgentCodes)),
|
||
'unknown_vehicle_types'=> array_values(array_unique($unknownVehicleTypes)),
|
||
'unknown_segments' => array_values(array_unique($unknownSegments)),
|
||
'invalid_cells' => $invalidCells,
|
||
'agent_wise_skipped_counts' => $agentWiseSkippedCounts,
|
||
],
|
||
'message' => 'Retention rate import completed',
|
||
], 200);
|
||
} catch (\Throwable $e) {
|
||
return $this->respond([
|
||
'status' => 'failed',
|
||
'code' => 500,
|
||
'data' => $e->getMessage(),
|
||
], 500);
|
||
}
|
||
}
|
||
|
||
|
||
|
||
}
|