1677 lines
68 KiB
PHP
1677 lines
68 KiB
PHP
<?php
|
|
|
|
namespace App\Controllers;
|
|
use CodeIgniter\RESTful\ResourceController;
|
|
use App\Models\PolicyModel;
|
|
use App\Models\EnquiryModel;
|
|
use App\Models\QuotationModel;
|
|
use App\Models\InvoiceModel;
|
|
use App\Models\InvoiceItemModel;
|
|
use App\Models\InvoiceUtrModel;
|
|
use App\Models\AgentIncentiveFileModel;
|
|
use CodeIgniter\Database\Exceptions\DataException;
|
|
use PhpOffice\PhpSpreadsheet\IOFactory;
|
|
|
|
class InvoiceController extends ResourceController
|
|
{
|
|
protected $PolicyModel;
|
|
protected $QuotationModel;
|
|
protected $EnquiryModel;
|
|
protected $InvoiceModel;
|
|
protected $InvoiceItemModel;
|
|
protected $InvoiceUtrModel;
|
|
protected $AgentIncentiveFileModel;
|
|
protected $db;
|
|
|
|
public function __construct()
|
|
{
|
|
$this->PolicyModel = new PolicyModel();
|
|
$this->QuotationModel = new QuotationModel();
|
|
$this->EnquiryModel = new EnquiryModel();
|
|
$this->InvoiceModel = new InvoiceModel();
|
|
$this->InvoiceItemModel = new InvoiceItemModel();
|
|
$this->InvoiceUtrModel = new InvoiceUtrModel();
|
|
$this->AgentIncentiveFileModel = new AgentIncentiveFileModel();
|
|
$this->db = \Config\Database::connect();
|
|
}
|
|
|
|
public function invoiceList()
|
|
{
|
|
try {
|
|
$fromDateRaw = $this->request->getGet('from_date');
|
|
$toDateRaw = $this->request->getGet('to_date');
|
|
$agentNameRaw = $this->request->getGet('agent_name');
|
|
$agentCodeRaw = $this->request->getGet('agent_code');
|
|
$policyNumberRaw = $this->request->getGet('policy_no');
|
|
|
|
$agentName = trim((string) ($agentNameRaw ?? ''));
|
|
$agentCode = trim((string) ($agentCodeRaw ?? ''));
|
|
$policyNumber = trim((string) ($policyNumberRaw ?? ''));
|
|
|
|
|
|
$fromParsed = $this->parseOptionalDmYDate($fromDateRaw);
|
|
$toParsed = $this->parseOptionalDmYDate($toDateRaw);
|
|
|
|
if ($fromParsed['error'] !== null) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 400,
|
|
'data' => $fromParsed['error'],
|
|
], 400);
|
|
}
|
|
if ($toParsed['error'] !== null) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 400,
|
|
'data' => $toParsed['error'],
|
|
], 400);
|
|
}
|
|
|
|
if ($fromParsed['ymd'] !== null && $toParsed['ymd'] !== null && $fromParsed['ymd'] > $toParsed['ymd']) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 400,
|
|
'data' => 'from_date must be on or before to_date',
|
|
], 400);
|
|
}
|
|
|
|
$query = $this->InvoiceModel
|
|
->select('partner_invoice.*,
|
|
PB.name as broker_name,
|
|
pos.name as pos_name,
|
|
FORMAT(partner_invoice.invoice_amount, 2, "en_IN") AS invoice_amount_indian_format,
|
|
DATE_FORMAT(partner_invoice.invoice_date, "%d-%m-%Y") AS invoice_date_ui_format,
|
|
(
|
|
SELECT GROUP_CONCAT(pa.name ORDER BY pa.id SEPARATOR ", ")
|
|
FROM partner_agent pa
|
|
WHERE JSON_SEARCH(partner_invoice.agent_id, "one", CAST(pa.id AS CHAR)) IS NOT NULL
|
|
) AS partner_names,
|
|
(
|
|
SELECT GROUP_CONCAT(piu.utr_no ORDER BY piu.id SEPARATOR ", ")
|
|
FROM partner_invoice_utr piu
|
|
WHERE piu.invoice_id = partner_invoice.id
|
|
AND piu.is_active = 1
|
|
) AS utr_numbers,
|
|
(
|
|
SELECT GROUP_CONCAT(DISTINCT pp.vehicle_type ORDER BY pp.vehicle_type SEPARATOR ", ")
|
|
FROM partner_invoice_items pii
|
|
LEFT JOIN partner_policy pp ON pp.id = pii.policy_id
|
|
WHERE pii.invoice_id = partner_invoice.id
|
|
AND pii.is_active = 1
|
|
) AS vehicle_types,
|
|
partner_invoice.invoice_amount AS invoiced_amount,
|
|
(
|
|
COALESCE((
|
|
SELECT SUM(piu.amount)
|
|
FROM partner_invoice_utr piu
|
|
WHERE piu.invoice_id = partner_invoice.id
|
|
AND piu.is_active = 1
|
|
), 0.00)
|
|
) AS payout_amount,
|
|
(
|
|
partner_invoice.invoice_amount - (
|
|
COALESCE((
|
|
SELECT SUM(piu.amount)
|
|
FROM partner_invoice_utr piu
|
|
WHERE piu.invoice_id = partner_invoice.id
|
|
AND piu.is_active = 1
|
|
), 0.00)
|
|
)
|
|
) AS balance_amount',
|
|
false)
|
|
->join('partner_brokers PB', 'PB.id = partner_invoice.broker_id', 'left')
|
|
->join('partner_pos pos', 'pos.id = partner_invoice.pos_id', 'left')
|
|
->where('partner_invoice.is_active', 1);
|
|
|
|
if ($fromParsed['ymd'] !== null) {
|
|
$query->where('DATE(partner_invoice.invoice_date) >=', $fromParsed['ymd']);
|
|
}
|
|
if ($toParsed['ymd'] !== null) {
|
|
$query->where('DATE(partner_invoice.invoice_date) <=', $toParsed['ymd']);
|
|
}
|
|
|
|
if ($agentName !== '' || $agentCode !== '') {
|
|
$agentQuery = $this->db->table('partner_agent')->select('id');
|
|
|
|
if ($agentName !== '') {
|
|
$agentQuery->like('name', $agentName);
|
|
}
|
|
if ($agentCode !== '') {
|
|
$agentQuery->like('agent_code', $agentCode);
|
|
}
|
|
|
|
$agentIds = array_column($agentQuery->get()->getResultArray(), 'id');
|
|
|
|
if (empty($agentIds)) {
|
|
return $this->respond(['status' => 'success', 'code' => 200, 'data' => []], 200);
|
|
}
|
|
|
|
$query->groupStart();
|
|
foreach ($agentIds as $agentId) {
|
|
$query->orWhere(
|
|
'JSON_SEARCH(partner_invoice.agent_id, "one", CAST(' . (int) $agentId . ' AS CHAR)) IS NOT NULL',
|
|
null,
|
|
false
|
|
);
|
|
}
|
|
$query->groupEnd();
|
|
}
|
|
|
|
if ($policyNumber !== '') {
|
|
$policyLike = '%' . $this->db->escapeLikeString($policyNumber) . '%';
|
|
$query->where(
|
|
'EXISTS (
|
|
SELECT 1 FROM partner_invoice_items pii
|
|
WHERE pii.invoice_id = partner_invoice.id
|
|
AND pii.is_active = 1
|
|
AND pii.policy_no LIKE ' . $this->db->escape($policyLike) . '
|
|
)',
|
|
null,
|
|
false
|
|
);
|
|
}
|
|
|
|
$data = $query->orderBy('partner_invoice.id', 'DESC')->findAll();
|
|
|
|
return $this->respond(['status' => 'success', 'code' => 200,'data' => $data ], 200);
|
|
|
|
} catch (\Exception $e) {
|
|
return $this->respond(['status' => 'failed', 'code' => 500, 'data' => $e->getMessage()], 500);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Optional invoice list date filter: format d-m-Y (e.g. 10-03-2026). Empty / null = no filter.
|
|
*
|
|
* @return array{ymd: ?string, error: ?string}
|
|
*/
|
|
private function parseOptionalDmYDate($value): array
|
|
{
|
|
if ($value === null) {
|
|
return ['ymd' => null, 'error' => null];
|
|
}
|
|
$v = trim((string) $value);
|
|
if ($v === '') {
|
|
return ['ymd' => null, 'error' => null];
|
|
}
|
|
|
|
$dt = \DateTime::createFromFormat('d-m-Y', $v);
|
|
if ($dt === false || $dt->format('d-m-Y') !== $v) {
|
|
return [
|
|
'ymd' => null,
|
|
'error' => 'Invalid date format; use d-m-Y (e.g. 10-03-2026)',
|
|
];
|
|
}
|
|
|
|
return ['ymd' => $dt->format('Y-m-d'), 'error' => null];
|
|
}
|
|
|
|
public function findInvoiceWithItems()
|
|
{
|
|
try {
|
|
$id = $this->request->getGet('id');
|
|
|
|
if (!$id) {
|
|
return $this->respond(['status' => 'failed', 'code' => 400, 'data' => 'ID is required'], 400);
|
|
}
|
|
|
|
$invoice = $this->InvoiceModel->where('id', $id)->first();
|
|
|
|
if (!$invoice) {
|
|
return $this->respond(['status' => 'failed', 'code' => 404, 'data' => 'Invoice not found'], 404);
|
|
}
|
|
|
|
$items = $this->InvoiceItemModel->select('partner_invoice_items.* , pp.issued_date , pe.name as customer_name , pq.premium_amount')
|
|
->join('partner_policy pp','pp.id = partner_invoice_items.policy_id ', 'left')
|
|
->join('partner_enquiry pe','pe.id = pp.enquiry_id ', 'left')
|
|
->join('partner_quotation pq','pq.id = pp.quotation_id ', 'left')
|
|
->where('partner_invoice_items.invoice_id', $id)
|
|
->where('partner_invoice_items.is_active', 1)
|
|
->findAll();
|
|
|
|
$paymentHistory = $this->InvoiceUtrModel
|
|
->select('id, invoice_id, amount as paid_amount, utr_no, utr_date as paid_date, created_at, created_by')
|
|
->where('invoice_id', $id)
|
|
->where('is_active', 1)
|
|
->where('amount >', 0)
|
|
->orderBy('id', 'DESC')
|
|
->findAll();
|
|
|
|
$utrPaidAmount = (float) (
|
|
$this->InvoiceUtrModel
|
|
->selectSum('amount', 'total')
|
|
->where('invoice_id', $id)
|
|
->where('is_active', 1)
|
|
->first()['total'] ?? 0
|
|
);
|
|
|
|
$totalPaidAmount = $utrPaidAmount;
|
|
$invoiceAmount = (float) ($invoice['invoice_amount'] ?? 0);
|
|
$balanceAmount = max($invoiceAmount - $totalPaidAmount, 0);
|
|
|
|
|
|
return $this->respond([
|
|
'status' => 'success',
|
|
'code' => 200,
|
|
'data' => [
|
|
'invoice' => $invoice,
|
|
'items' => $items,
|
|
'payment_history' => $paymentHistory,
|
|
'paid_amount' => $totalPaidAmount,
|
|
'balance_amount' => $balanceAmount
|
|
]
|
|
], 200);
|
|
|
|
} catch (\Exception $e) {
|
|
return $this->respond(['status' => 'failed', 'code' => 500, 'data' => $e->getMessage()], 500);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* GET invoice_id, manager_id — invoice header + active line items (policies under this manager only).
|
|
*/
|
|
public function invoiceView()
|
|
{
|
|
try {
|
|
$invoiceId = (int) $this->request->getGet('invoice_id');
|
|
$managerId = (int) $this->request->getGet('manager_id');
|
|
|
|
if ($invoiceId <= 0 || $managerId <= 0) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 400,
|
|
'data' => 'invoice_id and manager_id are required',
|
|
], 400);
|
|
}
|
|
|
|
$invoice = $this->InvoiceModel
|
|
->where('id', $invoiceId)
|
|
->where('is_active', 1)
|
|
->first();
|
|
|
|
if (! $invoice) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 404,
|
|
'data' => 'Invoice not found',
|
|
], 404);
|
|
}
|
|
|
|
$forbidden = $this->db->query(
|
|
'SELECT 1 AS x FROM partner_invoice_items pii '
|
|
. 'INNER JOIN partner_policy pp ON pp.id = pii.policy_id '
|
|
. 'WHERE pii.invoice_id = ? AND pii.is_active = 1 '
|
|
. 'AND (pp.manager_id IS NULL OR pp.manager_id != ?) '
|
|
. 'LIMIT 1',
|
|
[$invoiceId, $managerId]
|
|
)->getRow();
|
|
|
|
if ($forbidden !== null) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 403,
|
|
'data' => 'You do not have access to this invoice',
|
|
], 403);
|
|
}
|
|
|
|
$items = $this->InvoiceItemModel
|
|
->select(
|
|
'partner_invoice_items.*, pp.vehicle_type, pp.issued_date, pe.name AS customer_name, pp.premium_amount, '
|
|
. 'pa.agent_code, pa.name AS agent_name, CONCAT_WS(" - ", pa.agent_code, pa.name) AS agent_code_name',
|
|
false
|
|
)
|
|
->join('partner_policy pp', 'pp.id = partner_invoice_items.policy_id', 'left')
|
|
->join('partner_enquiry pe', 'pe.id = pp.enquiry_id', 'left')
|
|
->join('partner_quotation pq', 'pq.id = pp.quotation_id', 'left')
|
|
->join('partner_agent pa', 'pa.id = pp.agent_id', 'left')
|
|
->where('partner_invoice_items.invoice_id', $invoiceId)
|
|
->where('partner_invoice_items.is_active', 1)
|
|
->findAll();
|
|
|
|
return $this->respond([
|
|
'status' => 'success',
|
|
'code' => 200,
|
|
'data' => [
|
|
'invoice' => $invoice,
|
|
'items' => $items,
|
|
],
|
|
], 200);
|
|
} catch (\Exception $e) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 500,
|
|
'data' => $e->getMessage(),
|
|
], 500);
|
|
}
|
|
}
|
|
|
|
public function createOrUpdateInvoice()
|
|
{
|
|
$this->db->transBegin();
|
|
|
|
try {
|
|
$input = $this->request->getJSON(true);
|
|
|
|
// Normalize pos_id
|
|
$posIdRaw = $input['pos_id'] ?? 0;
|
|
$posId = is_numeric($posIdRaw) ? (int)$posIdRaw : 0;
|
|
|
|
$payoutStatus = isset($input['payout_status']) ? (int) $input['payout_status'] : 1;
|
|
|
|
$invoiceId = $input['id'] ?? null;
|
|
$isCreate = empty($invoiceId);
|
|
|
|
// ─────────────────────────────────────────────────────────────
|
|
// PRE-SAVE VALIDATION: UTR checks (only on CREATE)
|
|
// Run BEFORE any insert/update so nothing is written on failure
|
|
// ─────────────────────────────────────────────────────────────
|
|
if ($isCreate && !empty($input['utrs']) && is_array($input['utrs'])) {
|
|
|
|
$seenUtrs = [];
|
|
|
|
foreach ($input['utrs'] as $utrRow) {
|
|
|
|
$utrNo = trim((string)($utrRow['utr_no'] ?? ''));
|
|
|
|
if ($utrNo === '') {
|
|
continue;
|
|
}
|
|
|
|
// 1. Duplicate within the same request
|
|
if (in_array($utrNo, $seenUtrs, true)) {
|
|
$this->db->transRollback();
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 400,
|
|
'message' => "Duplicate UTR found: \"{$utrNo}\" appears more than once in your submission. Please remove the duplicate and try again.",
|
|
], 400);
|
|
}
|
|
|
|
$seenUtrs[] = $utrNo;
|
|
|
|
// 2. Already exists in DB (used in another payout)
|
|
$exists = $this->InvoiceUtrModel
|
|
->where('utr_no', $utrNo)
|
|
->where('is_active', 1)
|
|
->first();
|
|
|
|
if ($exists) {
|
|
$this->db->transRollback();
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 400,
|
|
'message' => "UTR \"{$utrNo}\" is already linked to another payout. Please verify the UTR number or contact support if you believe this is an error.",
|
|
], 400);
|
|
}
|
|
}
|
|
}
|
|
// ─────────────────────────────────────────────────────────────
|
|
|
|
// Basic invoice data
|
|
$invoiceData = [
|
|
'invoice_amount' => $input['invoice_amount'] ?? 0,
|
|
'broker_id' => $input['broker_id'] ?? 0,
|
|
'agent_id' => json_encode($input['agent_id'] ?? []),
|
|
'pos_id' => $posId,
|
|
'till_date' => $input['till_date'] ? date('Y-m-d', strtotime($input['till_date'])) : null,
|
|
'invoice_date' => $input['invoice_date'] ? date('Y-m-d', strtotime($input['invoice_date'])) : null,
|
|
'payout_status' => $payoutStatus,
|
|
'is_active' => 1,
|
|
'updated_at' => date('Y-m-d H:i:s'),
|
|
'updated_by' => $input['updated_by'] ?? 0
|
|
];
|
|
|
|
if (!$invoiceId) {
|
|
|
|
// Generate invoice number only for CREATE
|
|
$invoiceData['invoice_no'] = $this->generateInvoiceNo($posId);
|
|
|
|
$invoiceData['created_at'] = date('Y-m-d H:i:s');
|
|
$invoiceData['created_by'] = $input['created_by'] ?? 0;
|
|
|
|
$invoiceId = $this->InvoiceModel->insert($invoiceData);
|
|
|
|
if ($invoiceId === false) {
|
|
$this->db->transRollback();
|
|
log_message('error', json_encode($this->InvoiceModel->errors()));
|
|
return $this->respond(['status' => 'failed', 'message' => 'Failed to create invoice', 'error' => json_encode($this->InvoiceModel->errors())], 500);
|
|
}
|
|
} else {
|
|
|
|
// Invoice number should NOT change on update
|
|
unset($invoiceData['invoice_no']);
|
|
|
|
$this->InvoiceModel->update($invoiceId, $invoiceData);
|
|
}
|
|
|
|
// Insert or update items
|
|
if (!empty($input['items']) && is_array($input['items'])) {
|
|
|
|
foreach ($input['items'] as $item) {
|
|
|
|
$itemData = [
|
|
'invoice_id' => $invoiceId,
|
|
'policy_id' => $item['policy_id'],
|
|
'policy_no' => $item['policy_no'],
|
|
'commission_amount' => $item['commission_amount'] ?? 0,
|
|
'is_active' => $item['is_active'] ?? 1,
|
|
'updated_at' => date('Y-m-d H:i:s'),
|
|
'updated_by' => $input['updated_by'] ?? 0
|
|
];
|
|
|
|
// UPDATE (if id exists)
|
|
if (!empty($item['id'])) {
|
|
|
|
$this->InvoiceItemModel->update($item['id'], $itemData);
|
|
|
|
}else{ // INSERT (if id missing)
|
|
|
|
$itemData['created_at'] = date('Y-m-d H:i:s');
|
|
$itemData['created_by'] = $input['created_by'] ?? 0;
|
|
$this->InvoiceItemModel->insert($itemData);
|
|
|
|
//update pos_id to policy table
|
|
$this->PolicyModel->update($item['policy_id'], ['pos_id' => $posId ]);
|
|
}
|
|
}
|
|
}
|
|
|
|
// UTR save — validations already passed above, just insert
|
|
if ($isCreate && !empty($input['utrs']) && is_array($input['utrs'])) {
|
|
|
|
foreach ($input['utrs'] as $utrRow) {
|
|
|
|
$utrNo = trim((string)($utrRow['utr_no'] ?? ''));
|
|
if ($utrNo === '') continue;
|
|
|
|
$utrDate = !empty($utrRow['utr_date'])
|
|
? date('Y-m-d', strtotime($utrRow['utr_date']))
|
|
: null;
|
|
|
|
$amount = isset($utrRow['amount']) ? (float) $utrRow['amount'] : 0;
|
|
|
|
$result_utr = $this->InvoiceUtrModel->insert([
|
|
'invoice_id' => $invoiceId,
|
|
'utr_no' => $utrNo,
|
|
'utr_date' => $utrDate,
|
|
'amount' => $amount,
|
|
'is_active' => 1,
|
|
'created_at' => date('Y-m-d H:i:s'),
|
|
'created_by' => $input['created_by'] ?? 0,
|
|
]);
|
|
|
|
if ($result_utr === false) {
|
|
$this->db->transRollback();
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 500,
|
|
'message' => 'Could not save UTR details. Please try again.',
|
|
], 500);
|
|
}
|
|
}
|
|
}
|
|
|
|
if ($this->db->transStatus() === false) {
|
|
$this->db->transRollback();
|
|
throw new DataException("Transaction failed");
|
|
}
|
|
|
|
$this->db->transCommit();
|
|
|
|
return $this->respond([
|
|
'status' => 'success',
|
|
'code' => 200,
|
|
'data' => ['invoice_id' => $invoiceId]
|
|
], 200);
|
|
|
|
} catch (\Exception $e) {
|
|
$this->db->transRollback();
|
|
return $this->respond(['status' => 'failed', 'code' => 500, 'data' => $e->getMessage()], 500);
|
|
}
|
|
}
|
|
|
|
private function generateInvoiceNo($pos_id)
|
|
{
|
|
$pos_id = !empty($pos_id) ? $pos_id : 0;
|
|
$year = date('Y');
|
|
|
|
// If pos_id is missing/invalid (0/null/"w"), generate invoice as MIG/Year/NNNNN
|
|
// Otherwise generate as NIIB/{pos_id}/Year/NNNNN
|
|
$prefix = ($pos_id > 0)
|
|
? "NIIB/{$pos_id}/{$year}/"
|
|
: "MIG/{$year}/";
|
|
|
|
// Get last invoice of current year
|
|
$lastInvoice = $this->InvoiceModel
|
|
->select('invoice_no')
|
|
->like('invoice_no', $prefix, 'after')
|
|
->where('pos_id',$pos_id)
|
|
->orderBy('id', 'DESC')
|
|
->first();
|
|
|
|
if ($lastInvoice && isset($lastInvoice['invoice_no'])) {
|
|
// Extract numeric part
|
|
$lastNumber = intval(substr($lastInvoice['invoice_no'], -5));
|
|
$nextNumber = $lastNumber + 1;
|
|
} else {
|
|
$nextNumber = 1;
|
|
}
|
|
|
|
return $prefix . str_pad($nextNumber, 5, '0', STR_PAD_LEFT);
|
|
}
|
|
|
|
|
|
public function deleteInvoice()
|
|
{
|
|
try {
|
|
$id = $this->request->getGet('id');
|
|
|
|
if (!$id) {
|
|
return $this->respond(['status' => 'failed', 'code' => 400, 'data' => 'ID is required'], 400);
|
|
}
|
|
|
|
$this->InvoiceModel->update($id, ['is_active' => 0]);
|
|
$this->InvoiceItemModel->where('invoice_id', $id)->set(['is_active' => 0])->update();
|
|
|
|
return $this->respond(['status' => 'success', 'code' => 200, 'data' => 'Invoice deleted'], 200);
|
|
|
|
} catch (\Exception $e) {
|
|
return $this->respond(['status' => 'failed', 'code' => 500, 'data' => $e->getMessage()], 500);
|
|
}
|
|
}
|
|
|
|
public function getCommissionRateList()
|
|
{
|
|
try {
|
|
$input = $this->request->getJSON(true);
|
|
if (! is_array($input)) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 400,
|
|
'message'=> 'JSON body is required',
|
|
], 400);
|
|
}
|
|
|
|
if (empty($input['manager_id'])) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 400,
|
|
'message'=> 'manager_id is required',
|
|
], 400);
|
|
}
|
|
|
|
$managerId = (int) $input['manager_id'];
|
|
$invoiceIdRaw = $input['invoice_id'] ?? null;
|
|
$invoiceId = ($invoiceIdRaw !== null && $invoiceIdRaw !== '' && (int) $invoiceIdRaw > 0)
|
|
? (int) $invoiceIdRaw
|
|
: 0;
|
|
|
|
// Unused policies: no active invoice line on an active invoice (same as getAgentUnusedCommissionList).
|
|
// Do not LEFT JOIN all invoice_items — inactive rows duplicate policies and break "unused" detection.
|
|
if ($invoiceId === 0) {
|
|
$query = $this->PolicyModel
|
|
->select(
|
|
'partner_policy.policy_number as policy_no, partner_policy.id as policy_id, partner_policy.issued_date, '
|
|
. 'partner_policy.commission_amount, partner_policy.insured_name as customer_name, pe.agent_id, '
|
|
. 'pa.name as agent_name, pa.agent_code, partner_policy.premium_amount, '
|
|
. "NULL AS invoice_id, NULL AS invoice_no, '' AS utr_no",
|
|
false
|
|
)
|
|
->join('partner_enquiry pe', 'pe.id = partner_policy.enquiry_id', 'left')
|
|
->join('partner_quotation pq', 'pq.id = partner_policy.quotation_id', 'left')
|
|
->join('partner_agent pa', 'pa.id = pe.agent_id', 'left')
|
|
->where('partner_policy.is_active', 1)
|
|
// ->where('partner_policy.commission_amount >', 0)
|
|
// ->where('partner_policy.is_data_accuracy_checked', 1)
|
|
->where('partner_policy.manager_id', $managerId)
|
|
->where('partner_policy.policy_number IS NOT NULL', null, false)
|
|
->where(
|
|
'NOT EXISTS (SELECT 1 FROM partner_invoice_items pii2 '
|
|
. 'INNER JOIN partner_invoice pi2 ON pi2.id = pii2.invoice_id AND pi2.is_active = 1 '
|
|
. 'WHERE pii2.policy_id = partner_policy.id AND pii2.is_active = 1)',
|
|
null,
|
|
false
|
|
);
|
|
} else {
|
|
$query = $this->PolicyModel
|
|
->select(
|
|
'partner_policy.policy_number as policy_no, partner_policy.id as policy_id, partner_policy.issued_date, '
|
|
. 'partner_policy.commission_amount, partner_policy.insured_name as customer_name, pe.agent_id, '
|
|
. 'pa.name as agent_name, pa.agent_code, partner_policy.premium_amount, pi.id as invoice_id, pi.invoice_no, '
|
|
. 'COALESCE(('
|
|
. 'SELECT GROUP_CONCAT(piu.utr_no ORDER BY piu.id SEPARATOR ", ") '
|
|
. 'FROM partner_invoice_utr piu '
|
|
. 'WHERE piu.invoice_id = pi.id AND piu.is_active = 1'
|
|
. "), '') as utr_no",
|
|
false
|
|
)
|
|
->join('partner_enquiry pe', 'pe.id = partner_policy.enquiry_id', 'left')
|
|
->join('partner_quotation pq', 'pq.id = partner_policy.quotation_id', 'left')
|
|
->join('partner_agent pa', 'pa.id = pe.agent_id', 'left')
|
|
->join(
|
|
'partner_invoice_items pii',
|
|
'pii.policy_id = partner_policy.id AND pii.is_active = 1 AND pii.invoice_id = ' . $invoiceId,
|
|
'inner'
|
|
)
|
|
->join('partner_invoice pi', 'pi.id = pii.invoice_id AND pi.is_active = 1', 'inner')
|
|
->where('partner_policy.is_active', 1)
|
|
->where('partner_policy.commission_amount >', 0)
|
|
->where('partner_policy.is_data_accuracy_checked', 1)
|
|
->where('partner_policy.manager_id', $managerId)
|
|
->where('partner_policy.policy_number IS NOT NULL', null, false)
|
|
->where('pi.id', $invoiceId)
|
|
->distinct();
|
|
}
|
|
|
|
$this->applyCommissionRateListPayloadFilters($query, $input);
|
|
|
|
$query->orderBy('partner_policy.issued_date', 'DESC');
|
|
$query->orderBy('partner_policy.id', 'DESC');
|
|
|
|
$data = $query->findAll();
|
|
|
|
$total_commission = 0;
|
|
foreach ($data as $policy) {
|
|
$total_commission += (float) ($policy['commission_amount'] ?? 0);
|
|
}
|
|
|
|
return $this->respond([
|
|
'status' => 'success',
|
|
'code' => 200,
|
|
'data' => $data,
|
|
'total_commission' => $total_commission,
|
|
'total_policies' => count($data),
|
|
], 200);
|
|
} catch (\Exception $e) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 500,
|
|
'message'=> $e->getMessage(),
|
|
], 500);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Optional filters for getCommissionRateList JSON: agent_id, from_date, to_date, pos_id.
|
|
*/
|
|
private function applyCommissionRateListPayloadFilters($query, array $input): void
|
|
{
|
|
if (! empty($input['agent_id'])) {
|
|
if (is_array($input['agent_id'])) {
|
|
$query->whereIn('pe.agent_id', $input['agent_id']);
|
|
} else {
|
|
$query->where('pe.agent_id', $input['agent_id']);
|
|
}
|
|
}
|
|
if (! empty($input['from_date'])) {
|
|
$query->where('partner_policy.issued_date >=', date('Y-m-d', strtotime((string) $input['from_date'])));
|
|
}
|
|
if (! empty($input['to_date'])) {
|
|
$query->where('partner_policy.issued_date <=', date('Y-m-d', strtotime((string) $input['to_date'])));
|
|
}
|
|
if (! empty($input['pos_id'])) {
|
|
$query->where('partner_policy.pos_id', $input['pos_id']);
|
|
}
|
|
}
|
|
|
|
public function getAgentUnusedCommissionList()
|
|
{
|
|
try {
|
|
|
|
$manager_id = $this->request->getGet('manager_id');
|
|
$from_date = $this->request->getGet('from_date');
|
|
$to_date = $this->request->getGet('to_date');
|
|
|
|
|
|
if ($manager_id === null || $manager_id === '') {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 400,
|
|
'message'=> 'manager_id is required',
|
|
], 400);
|
|
}
|
|
|
|
// Unused = policy has no active invoice line on an active invoice (same idea as commissionPayoutReport pending).
|
|
// NOT EXISTS avoids false "invoiced" when only inactive items/invoices exist, and avoids join fan-out.
|
|
$query = $this->PolicyModel
|
|
->select([
|
|
'pe.agent_id',
|
|
'MAX(pa.name) as agent_name',
|
|
'MAX(pa.agent_code) as agent_code',
|
|
'SUM(partner_policy.commission_amount) as unused_commission_amount',
|
|
'COUNT(partner_policy.id) as total_policies',
|
|
])
|
|
->join('partner_enquiry pe', 'pe.id = partner_policy.enquiry_id', 'left')
|
|
->join('partner_agent pa', 'pa.id = pe.agent_id', 'left')
|
|
->where('partner_policy.is_active', 1)
|
|
// ->where('partner_policy.commission_amount >', 0)
|
|
// ->where('partner_policy.is_data_accuracy_checked', 1)
|
|
->where('partner_policy.manager_id', (int) $manager_id)
|
|
->where('partner_policy.policy_number IS NOT NULL', null, false)
|
|
->where('pe.agent_id IS NOT NULL', null, false)
|
|
->where(
|
|
'NOT EXISTS (SELECT 1 FROM partner_invoice_items pii2 '
|
|
. 'INNER JOIN partner_invoice pi2 ON pi2.id = pii2.invoice_id AND pi2.is_active = 1 '
|
|
. 'WHERE pii2.policy_id = partner_policy.id AND pii2.is_active = 1)',
|
|
null,
|
|
false
|
|
)
|
|
->groupBy('pe.agent_id');
|
|
|
|
if(!empty($from_date) && !empty($to_date)){
|
|
$query->where('partner_policy.issued_date >=', date('Y-m-d', strtotime((string) $from_date)));
|
|
$query->where('partner_policy.issued_date <=', date('Y-m-d', strtotime((string) $to_date)));
|
|
}
|
|
|
|
$data = $query->findAll();
|
|
|
|
// Grand total (optional but useful for FE)
|
|
$grand_total = 0;
|
|
foreach ($data as $row) {
|
|
$grand_total += (float)$row['unused_commission_amount'];
|
|
}
|
|
|
|
return $this->respond([
|
|
'status' => 'success',
|
|
'code' => 200,
|
|
'data' => $data,
|
|
'grand_total_unused_commission' => $grand_total,
|
|
'total_agents' => count($data)
|
|
], 200);
|
|
|
|
} catch (\Exception $e) {
|
|
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 500,
|
|
'message'=> $e->getMessage()
|
|
], 500);
|
|
}
|
|
}
|
|
|
|
public function updateUtrDetails()
|
|
{
|
|
$this->db->transBegin();
|
|
|
|
try {
|
|
$input = $this->request->getJSON(true);
|
|
|
|
$invoiceId = (int)($input['invoice_id'] ?? 0);
|
|
$paidAmount = (float)($input['paid_amount'] ?? 0);
|
|
$paidDateRaw = $input['paid_date'] ?? null;
|
|
$utrId = (int)($input['utr_id'] ?? 0);
|
|
$utrNoInput = trim((string)($input['utr_no'] ?? ''));
|
|
|
|
if ($invoiceId <= 0 || $paidAmount < 0 || empty($paidDateRaw)) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 400,
|
|
'message'=> 'invoice_id, paid_amount and paid_date are required'
|
|
], 400);
|
|
}
|
|
|
|
$invoice = $this->InvoiceModel
|
|
->where('id', $invoiceId)
|
|
->where('is_active', 1)
|
|
->first();
|
|
|
|
if (empty($invoice)) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 404,
|
|
'message'=> 'Invoice not found'
|
|
], 404);
|
|
}
|
|
|
|
$paidDateTs = strtotime($paidDateRaw);
|
|
if ($paidDateTs === false) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 400,
|
|
'message'=> 'Invalid paid_date format'
|
|
], 400);
|
|
}
|
|
$paidDate = date('Y-m-d', $paidDateTs);
|
|
|
|
$utrPaidAmount = (float) (
|
|
$this->InvoiceUtrModel
|
|
->selectSum('amount', 'total')
|
|
->where('invoice_id', $invoiceId)
|
|
->where('is_active', 1)
|
|
->first()['total'] ?? 0
|
|
);
|
|
|
|
$currentTotalPaid = $utrPaidAmount;
|
|
$invoiceAmount = (float) ($invoice['invoice_amount'] ?? 0);
|
|
$remainingBalance = max($invoiceAmount - $currentTotalPaid, 0);
|
|
|
|
/*
|
|
* If utr_id is empty/0 from request:
|
|
* 1) create a fresh UTR row with given utr_no (or generated fallback),
|
|
* 2) keep amount = 0 first,
|
|
* 3) then continue existing update flow using the new utr_id.
|
|
*/
|
|
if ($utrId <= 0) {
|
|
$utrNo = $utrNoInput !== '' ? $utrNoInput : ('UTR' . date('YmdHis'));
|
|
while (
|
|
!empty(
|
|
$this->InvoiceUtrModel
|
|
->where('invoice_id', $invoiceId)
|
|
->where('utr_no', $utrNo)
|
|
->where('is_active', 1)
|
|
->first()
|
|
)
|
|
) {
|
|
usleep(200000);
|
|
$utrNo = 'UTR' . date('YmdHis');
|
|
}
|
|
|
|
$insertedUtrId = $this->InvoiceUtrModel->insert([
|
|
'invoice_id' => $invoiceId,
|
|
'utr_no' => $utrNo,
|
|
'utr_date' => null,
|
|
'amount' => 0,
|
|
'is_active' => 1,
|
|
'created_at' => date('Y-m-d H:i:s'),
|
|
'created_by' => (int)($input['created_by'] ?? 0),
|
|
'updated_at' => date('Y-m-d H:i:s'),
|
|
'updated_by' => (int)($input['updated_by'] ?? 0),
|
|
]);
|
|
|
|
if ($insertedUtrId === false) {
|
|
$this->db->transRollback();
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 500,
|
|
'message'=> 'Failed to create UTR',
|
|
'error' => $this->InvoiceUtrModel->errors()
|
|
], 500);
|
|
}
|
|
|
|
$utrId = (int)$insertedUtrId;
|
|
}
|
|
|
|
$utrQuery = $this->InvoiceUtrModel
|
|
->where('invoice_id', $invoiceId)
|
|
->where('is_active', 1)
|
|
->where('id', $utrId);
|
|
$utrRow = $utrQuery->first();
|
|
|
|
if (empty($utrRow)) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 404,
|
|
'message'=> 'Selected UTR not found for this invoice'
|
|
], 404);
|
|
}
|
|
|
|
$existingUtrAmount = (float)($utrRow['amount'] ?? 0);
|
|
if ($paidAmount > ($remainingBalance + $existingUtrAmount)) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 400,
|
|
'message'=> 'Paid amount exceeds invoice balance'
|
|
], 400);
|
|
}
|
|
if ($paidAmount < $existingUtrAmount) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 400,
|
|
'message'=> 'Paid amount cannot be less than existing paid amount'
|
|
], 400);
|
|
}
|
|
|
|
$otherUtrPaidAmount = (float) (
|
|
$this->InvoiceUtrModel
|
|
->selectSum('amount', 'total')
|
|
->where('invoice_id', $invoiceId)
|
|
->where('is_active', 1)
|
|
->where('id !=', (int)$utrRow['id'])
|
|
->first()['total'] ?? 0
|
|
);
|
|
$maxAllowedForSelectedUtr = max($invoiceAmount - $otherUtrPaidAmount, 0);
|
|
if ($paidAmount > $maxAllowedForSelectedUtr) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 400,
|
|
'message'=> 'Paid amount exceeds invoice balance'
|
|
], 400);
|
|
}
|
|
|
|
$updated = $this->InvoiceUtrModel->update((int)$utrRow['id'], [
|
|
'amount' => $paidAmount,
|
|
'utr_date' => $paidDate,
|
|
'updated_at' => date('Y-m-d H:i:s'),
|
|
'updated_by' => (int)($input['updated_by'] ?? 0),
|
|
]);
|
|
|
|
if ($updated === false) {
|
|
$this->db->transRollback();
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 500,
|
|
'message'=> 'Failed to update UTR payment',
|
|
'error' => $this->InvoiceUtrModel->errors()
|
|
], 500);
|
|
}
|
|
|
|
$latestTotalPaid = ($currentTotalPaid - $existingUtrAmount) + $paidAmount;
|
|
$newBalance = max($invoiceAmount - $latestTotalPaid, 0);
|
|
$payoutStatus = $newBalance == 0.0 ? 2 : 1;
|
|
|
|
$this->InvoiceModel->update($invoiceId, [
|
|
'payout_status' => $payoutStatus,
|
|
'updated_at' => date('Y-m-d H:i:s'),
|
|
'updated_by' => (int)($input['updated_by'] ?? 0),
|
|
]);
|
|
|
|
if ($this->db->transStatus() === false) {
|
|
$this->db->transRollback();
|
|
throw new DataException('Transaction failed');
|
|
}
|
|
|
|
$this->db->transCommit();
|
|
|
|
return $this->respond([
|
|
'status' => 'success',
|
|
'code' => 200,
|
|
'data' => [
|
|
'utr_id' => (int)$utrRow['id'],
|
|
'utr_no' => (string)$utrRow['utr_no'],
|
|
'invoice_id' => $invoiceId,
|
|
'paid_amount' => $latestTotalPaid,
|
|
'balance_amount' => $newBalance
|
|
]
|
|
], 200);
|
|
} catch (\Exception $e) {
|
|
$this->db->transRollback();
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 500,
|
|
'message'=> $e->getMessage()
|
|
], 500);
|
|
}
|
|
}
|
|
|
|
public function addInvoicePayment()
|
|
{
|
|
return $this->updateUtrDetails();
|
|
}
|
|
|
|
public function listUtrDetails()
|
|
{
|
|
try {
|
|
$invoiceId = (int)($this->request->getGet('invoice_id') ?? 0);
|
|
if ($invoiceId <= 0) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 400,
|
|
'message'=> 'invoice_id is required'
|
|
], 400);
|
|
}
|
|
|
|
$invoice = $this->InvoiceModel
|
|
->select('id, invoice_no, invoice_amount')
|
|
->where('id', $invoiceId)
|
|
->where('is_active', 1)
|
|
->first();
|
|
|
|
if (empty($invoice)) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 404,
|
|
'message'=> 'Invoice not found'
|
|
], 404);
|
|
}
|
|
|
|
$history = $this->db->table('partner_invoice_utr piu')
|
|
->select('
|
|
piu.id,
|
|
piu.invoice_id,
|
|
pi.invoice_no,
|
|
pi.invoice_amount AS total_amount,
|
|
piu.amount AS paid_amount,
|
|
piu.utr_no,
|
|
piu.utr_date AS paid_date,
|
|
piu.created_at,
|
|
COALESCE(psu.name, psc.name, "-") AS createdby_name,
|
|
(
|
|
pi.invoice_amount - COALESCE((
|
|
SELECT SUM(piu2.amount)
|
|
FROM partner_invoice_utr piu2
|
|
WHERE piu2.invoice_id = piu.invoice_id
|
|
AND piu2.is_active = 1
|
|
AND piu2.amount > 0
|
|
AND piu2.id <= piu.id
|
|
), 0)
|
|
) AS balance_amount
|
|
', false)
|
|
->join('partner_invoice pi', 'pi.id = piu.invoice_id', 'inner')
|
|
->join('partner_staff psc', 'psc.id = piu.created_by', 'left')
|
|
->join('partner_staff psu', 'psu.id = piu.updated_by', 'left')
|
|
->where('piu.invoice_id', $invoiceId)
|
|
->where('piu.is_active', 1)
|
|
->where('piu.amount >', 0)
|
|
->orderBy('piu.id', 'DESC')
|
|
->get()
|
|
->getResultArray();
|
|
|
|
// Return unique UTR list for this invoice (remove duplicates by utr_no).
|
|
// Keep only unpaid UTR rows (amount 0 or null).
|
|
$availableUtrs = $this->db->table('partner_invoice_utr')
|
|
->select('MIN(id) as id, utr_no')
|
|
->where('invoice_id', $invoiceId)
|
|
->where('is_active', 1)
|
|
->where('(amount IS NULL OR amount <= 0)', null, false)
|
|
->where('TRIM(COALESCE(utr_no, "")) !=', '')
|
|
->groupBy('utr_no')
|
|
->orderBy('MIN(id)', 'ASC', false)
|
|
->get()
|
|
->getResultArray();
|
|
|
|
$paidToDate = (float) (
|
|
$this->InvoiceUtrModel
|
|
->selectSum('amount', 'total')
|
|
->where('invoice_id', $invoiceId)
|
|
->where('is_active', 1)
|
|
->first()['total'] ?? 0
|
|
);
|
|
$balanceAmount = max(((float)$invoice['invoice_amount']) - $paidToDate, 0);
|
|
|
|
return $this->respond([
|
|
'status' => 'success',
|
|
'code' => 200,
|
|
'data' => $history,
|
|
'available_utrs' => $availableUtrs,
|
|
'invoice'=> [
|
|
'invoice_id' => (int)$invoice['id'],
|
|
'invoice_no' => $invoice['invoice_no'],
|
|
'total_amount' => (float)$invoice['invoice_amount'],
|
|
'paid_to_date' => $paidToDate,
|
|
'balance_amount' => $balanceAmount,
|
|
],
|
|
], 200);
|
|
} catch (\Exception $e) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 500,
|
|
'message'=> $e->getMessage()
|
|
], 500);
|
|
}
|
|
}
|
|
|
|
public function addPaymentHistory()
|
|
{
|
|
return $this->listUtrDetails();
|
|
}
|
|
|
|
public function utrDetails()
|
|
{
|
|
try {
|
|
$invoiceId = (int)($this->request->getGet('invoice_id') ?? 0);
|
|
if ($invoiceId <= 0) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 400,
|
|
'message'=> 'invoice_id is required'
|
|
], 400);
|
|
}
|
|
|
|
$invoice = $this->InvoiceModel
|
|
->select('id, invoice_no, invoice_amount')
|
|
->where('id', $invoiceId)
|
|
->where('is_active', 1)
|
|
->first();
|
|
|
|
if (empty($invoice)) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 404,
|
|
'message'=> 'Invoice not found'
|
|
], 404);
|
|
}
|
|
|
|
$utrDetails = $this->InvoiceUtrModel
|
|
->select('id, invoice_id, amount as paid_amount, utr_no, utr_date')
|
|
->where('invoice_id', $invoiceId)
|
|
->where('is_active', 1)
|
|
->orderBy('id', 'ASC')
|
|
->findAll();
|
|
|
|
$paidToDate = (float) (
|
|
$this->InvoiceUtrModel
|
|
->selectSum('amount', 'total')
|
|
->where('invoice_id', $invoiceId)
|
|
->where('is_active', 1)
|
|
->first()['total'] ?? 0
|
|
);
|
|
|
|
$balanceAmount = max(((float)$invoice['invoice_amount']) - $paidToDate, 0);
|
|
|
|
return $this->respond([
|
|
'status' => 'success',
|
|
'code' => 200,
|
|
'data' => [
|
|
'utrs_details' => $utrDetails,
|
|
'invoice' => [
|
|
'invoice_id' => (int)$invoice['id'],
|
|
'invoice_no' => $invoice['invoice_no'],
|
|
'total_amount' => (float)$invoice['invoice_amount'],
|
|
'paid_to_date' => $paidToDate,
|
|
'balance_amount' => $balanceAmount,
|
|
],
|
|
],
|
|
], 200);
|
|
} catch (\Exception $e) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 500,
|
|
'message'=> $e->getMessage()
|
|
], 500);
|
|
}
|
|
}
|
|
|
|
public function bulkUploadCommission()
|
|
{
|
|
$this->db->transBegin();
|
|
try {
|
|
$input = $this->request->getJSON(true);
|
|
if (!is_array($input)) {
|
|
$input = [];
|
|
}
|
|
|
|
$post = $this->request->getPost();
|
|
if (!is_array($post)) {
|
|
$post = [];
|
|
}
|
|
|
|
$rows = $input['rows'] ?? [];
|
|
$updatedBy = (int)($input['updated_by'] ?? ($post['created_by'] ?? 0));
|
|
|
|
// Multipart flow: read excel on backend and store upload metadata.
|
|
$file = $this->request->getFile('file_name');
|
|
if ($file && $file->isValid()) {
|
|
$month = date('Y-m-d');
|
|
$storedFileName = storage_upload_if_valid($file, 'agent', 'incentive_file');
|
|
if ($storedFileName === null) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 400,
|
|
'message' => 'No valid file uploaded',
|
|
], 400);
|
|
}
|
|
|
|
$this->AgentIncentiveFileModel->insert([
|
|
'incentive_month' => $month,
|
|
'incentive_file_name' => $storedFileName,
|
|
'file_type' => 'invoice',
|
|
'is_active' => 1,
|
|
'created_by' => $updatedBy > 0 ? $updatedBy : null,
|
|
], true);
|
|
|
|
$localPath = null;
|
|
try {
|
|
$localPath = storage_local_path('agent', 'incentive_file', $storedFileName);
|
|
$spreadsheet = IOFactory::load($localPath);
|
|
$excelRows = $spreadsheet->getActiveSheet()->toArray(null, true, true, false);
|
|
|
|
if (empty($excelRows)) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 400,
|
|
'message' => 'Uploaded file is empty',
|
|
], 400);
|
|
}
|
|
|
|
$headerRow = $excelRows[0] ?? [];
|
|
$headerIndex = [];
|
|
foreach ($headerRow as $idx => $headerValue) {
|
|
$normalized = preg_replace('/[^a-z0-9]/', '', strtolower(trim((string)$headerValue)));
|
|
if (!empty($normalized)) {
|
|
$headerIndex[$normalized] = (int)$idx;
|
|
}
|
|
}
|
|
|
|
$policyIdx = $headerIndex['policynumber'] ?? null;
|
|
$invoiceIdx = $headerIndex['invoicenumber'] ?? null;
|
|
$commissionIdx = $headerIndex['commission'] ?? ($headerIndex['commissionamount'] ?? null);
|
|
|
|
if ($policyIdx === null || $commissionIdx === null) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 400,
|
|
'message' => 'Expected columns: Policy Number, Invoice Number, Commission or Commission Amount',
|
|
], 400);
|
|
}
|
|
|
|
$fallbackInvoiceNo = trim((string)($post['invoice_number'] ?? ''));
|
|
$rows = [];
|
|
foreach ($excelRows as $index => $row) {
|
|
if ($index === 0) {
|
|
continue;
|
|
}
|
|
|
|
$policyNo = trim((string)($row[$policyIdx] ?? ''));
|
|
$invoiceNoFromFile = $invoiceIdx !== null ? trim((string)($row[$invoiceIdx] ?? '')) : '';
|
|
$invoiceNo = $invoiceNoFromFile !== '' ? $invoiceNoFromFile : $fallbackInvoiceNo;
|
|
$commissionRaw = trim((string)($row[$commissionIdx] ?? ''));
|
|
|
|
if ($policyNo === '' && $invoiceNo === '' && $commissionRaw === '') {
|
|
continue;
|
|
}
|
|
|
|
$commission = (float)str_replace(',', '', $commissionRaw);
|
|
if ($policyNo === '' || $invoiceNo === '' || !is_numeric(str_replace(',', '', $commissionRaw))) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 400,
|
|
'message' => 'Invalid data at line ' . ($index + 1),
|
|
], 400);
|
|
}
|
|
|
|
$rows[] = [
|
|
'line_no' => $index + 1,
|
|
'policy_number' => $policyNo,
|
|
'invoice_no' => $invoiceNo,
|
|
'commission_amount' => $commission,
|
|
];
|
|
}
|
|
} finally {
|
|
storage_cleanup_temp($localPath);
|
|
}
|
|
}
|
|
|
|
if (empty($rows) || !is_array($rows)) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 400,
|
|
'message' => 'rows is required',
|
|
], 400);
|
|
}
|
|
|
|
$mismatchRows = [];
|
|
$validRows = [];
|
|
$skippedRows = 0;
|
|
|
|
foreach ($rows as $row) {
|
|
$policyNo = trim((string)($row['policy_number'] ?? ''));
|
|
$invoiceNo = trim((string)($row['invoice_no'] ?? ''));
|
|
$commissionAmount = (float)($row['commission_amount'] ?? 0);
|
|
$lineNo = (int)($row['line_no'] ?? 0);
|
|
|
|
if ($policyNo === '' || $invoiceNo === '') {
|
|
$skippedRows++;
|
|
continue;
|
|
}
|
|
|
|
$record = $this->db->table('partner_invoice_items pii')
|
|
->select('
|
|
pii.id as item_id,
|
|
pii.invoice_id,
|
|
pii.policy_id,
|
|
pii.policy_no,
|
|
pi.invoice_no,
|
|
pi.agent_id as invoice_agent_json,
|
|
pe.agent_id as policy_agent_id
|
|
')
|
|
->join('partner_invoice pi', 'pi.id = pii.invoice_id', 'inner')
|
|
->join('partner_policy pp', 'pp.id = pii.policy_id', 'left')
|
|
->join('partner_enquiry pe', 'pe.id = pp.enquiry_id', 'left')
|
|
->where('pii.is_active', 1)
|
|
->where('pi.is_active', 1)
|
|
->where('pi.invoice_no', $invoiceNo)
|
|
->where('pii.policy_no', $policyNo)
|
|
->get()
|
|
->getRowArray();
|
|
|
|
if (empty($record)) {
|
|
$skippedRows++;
|
|
continue;
|
|
}
|
|
|
|
$invoiceAgentIds = $this->extractAgentIdsFromInvoice($record['invoice_agent_json'] ?? '');
|
|
$policyAgentId = isset($record['policy_agent_id']) ? (int)$record['policy_agent_id'] : 0;
|
|
$targetAgentId = !empty($invoiceAgentIds) ? (int)$invoiceAgentIds[0] : 0;
|
|
$isMismatch = $policyAgentId > 0 && !in_array($policyAgentId, $invoiceAgentIds, true);
|
|
|
|
if ($isMismatch) {
|
|
$mismatchRows[] = [
|
|
'line_no' => $lineNo,
|
|
'item_id' => (int)$record['item_id'],
|
|
'invoice_id' => (int)$record['invoice_id'],
|
|
'policy_id' => (int)$record['policy_id'],
|
|
'policy_number' => $policyNo,
|
|
'invoice_no' => $invoiceNo,
|
|
'commission_amount' => $commissionAmount,
|
|
'policy_agent_id' => $policyAgentId,
|
|
'target_agent_id' => $targetAgentId,
|
|
];
|
|
continue;
|
|
}
|
|
|
|
$validRows[] = [
|
|
'item_id' => (int)$record['item_id'],
|
|
'invoice_id' => (int)$record['invoice_id'],
|
|
'policy_id' => (int)$record['policy_id'],
|
|
'commission_amount' => $commissionAmount,
|
|
];
|
|
}
|
|
|
|
if (!empty($mismatchRows)) {
|
|
$this->ensureBulkUploadStagingTable();
|
|
$token = $this->generateProceedToken();
|
|
|
|
$this->db->table('invoice_bulk_upload_staging')->insert([
|
|
'proceed_token' => $token,
|
|
'payload_json' => json_encode([
|
|
'rows' => $rows,
|
|
'updated_by' => $updatedBy,
|
|
]),
|
|
'mismatch_json' => json_encode($mismatchRows),
|
|
'created_at' => date('Y-m-d H:i:s'),
|
|
'created_by' => $updatedBy,
|
|
'is_active' => 1,
|
|
]);
|
|
|
|
// Keep staged token/payload so proceed API can continue later.
|
|
$this->db->transCommit();
|
|
return $this->respond([
|
|
'status' => 'partner_mismatch',
|
|
'code' => 200,
|
|
'message' => 'Partner mismatch detected',
|
|
'mismatch_count' => count($mismatchRows),
|
|
'mismatches' => $mismatchRows,
|
|
'proceed_token' => $token,
|
|
], 200);
|
|
}
|
|
|
|
$updatedCount = $this->applyBulkCommissionRows($validRows, $updatedBy);
|
|
|
|
if ($this->db->transStatus() === false) {
|
|
$this->db->transRollback();
|
|
throw new DataException('Transaction failed');
|
|
}
|
|
|
|
$this->db->transCommit();
|
|
return $this->respond([
|
|
'status' => 'success',
|
|
'code' => 200,
|
|
'message' => 'Bulk upload completed',
|
|
'updated_count' => $updatedCount,
|
|
'skipped_count' => $skippedRows,
|
|
], 200);
|
|
} catch (\Exception $e) {
|
|
$this->db->transRollback();
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 500,
|
|
'message' => $e->getMessage(),
|
|
], 500);
|
|
}
|
|
}
|
|
|
|
public function bulkUploadCommissionProceed()
|
|
{
|
|
$this->db->transBegin();
|
|
try {
|
|
$input = $this->request->getJSON(true);
|
|
$proceedToken = trim((string)($input['proceed_token'] ?? ''));
|
|
$forceReassign = (int)($input['force_reassign_partner'] ?? 0);
|
|
$updatedBy = (int)($input['updated_by'] ?? 0);
|
|
|
|
if ($proceedToken === '' || $forceReassign !== 1) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 400,
|
|
'message' => 'proceed_token and force_reassign_partner=1 are required',
|
|
], 400);
|
|
}
|
|
|
|
$this->ensureBulkUploadStagingTable();
|
|
$staging = $this->db->table('invoice_bulk_upload_staging')
|
|
->where('proceed_token', $proceedToken)
|
|
->where('is_active', 1)
|
|
->get()
|
|
->getRowArray();
|
|
|
|
if (empty($staging)) {
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 404,
|
|
'message' => 'Invalid or expired proceed token',
|
|
], 404);
|
|
}
|
|
|
|
$payload = json_decode($staging['payload_json'] ?? '{}', true);
|
|
$rows = $payload['rows'] ?? [];
|
|
$mismatchRows = json_decode($staging['mismatch_json'] ?? '[]', true);
|
|
|
|
foreach ($mismatchRows as $mismatch) {
|
|
$policyId = (int)($mismatch['policy_id'] ?? 0);
|
|
$targetAgentId = (int)($mismatch['target_agent_id'] ?? 0);
|
|
if ($policyId <= 0 || $targetAgentId <= 0) {
|
|
continue;
|
|
}
|
|
|
|
$policy = $this->PolicyModel->select('enquiry_id')->where('id', $policyId)->first();
|
|
if (!empty($policy['enquiry_id'])) {
|
|
$this->EnquiryModel->update((int)$policy['enquiry_id'], [
|
|
'agent_id' => $targetAgentId,
|
|
'updated_at' => date('Y-m-d H:i:s'),
|
|
'updated_by' => $updatedBy,
|
|
]);
|
|
}
|
|
}
|
|
|
|
$finalRows = [];
|
|
foreach ($rows as $row) {
|
|
$policyNo = trim((string)($row['policy_number'] ?? ''));
|
|
$invoiceNo = trim((string)($row['invoice_no'] ?? ''));
|
|
$commissionAmount = (float)($row['commission_amount'] ?? 0);
|
|
if ($policyNo === '' || $invoiceNo === '') {
|
|
continue;
|
|
}
|
|
|
|
$record = $this->db->table('partner_invoice_items pii')
|
|
->select('pii.id as item_id, pii.invoice_id, pii.policy_id')
|
|
->join('partner_invoice pi', 'pi.id = pii.invoice_id', 'inner')
|
|
->where('pii.is_active', 1)
|
|
->where('pi.is_active', 1)
|
|
->where('pi.invoice_no', $invoiceNo)
|
|
->where('pii.policy_no', $policyNo)
|
|
->get()
|
|
->getRowArray();
|
|
|
|
if (empty($record)) {
|
|
continue;
|
|
}
|
|
|
|
$finalRows[] = [
|
|
'item_id' => (int)$record['item_id'],
|
|
'invoice_id' => (int)$record['invoice_id'],
|
|
'policy_id' => (int)$record['policy_id'],
|
|
'commission_amount' => $commissionAmount,
|
|
];
|
|
}
|
|
|
|
$updatedCount = $this->applyBulkCommissionRows($finalRows, $updatedBy);
|
|
|
|
$this->db->table('invoice_bulk_upload_staging')
|
|
->where('id', (int)$staging['id'])
|
|
->update([
|
|
'is_active' => 0,
|
|
'updated_at' => date('Y-m-d H:i:s'),
|
|
'updated_by' => $updatedBy,
|
|
]);
|
|
|
|
if ($this->db->transStatus() === false) {
|
|
$this->db->transRollback();
|
|
throw new DataException('Transaction failed');
|
|
}
|
|
|
|
$this->db->transCommit();
|
|
return $this->respond([
|
|
'status' => 'success',
|
|
'code' => 200,
|
|
'message' => 'Bulk upload completed with partner reassignment',
|
|
'updated_count' => $updatedCount,
|
|
], 200);
|
|
} catch (\Exception $e) {
|
|
$this->db->transRollback();
|
|
return $this->respond([
|
|
'status' => 'failed',
|
|
'code' => 500,
|
|
'message' => $e->getMessage(),
|
|
], 500);
|
|
}
|
|
}
|
|
|
|
private function applyBulkCommissionRows(array $rows, int $updatedBy): int
|
|
{
|
|
$updatedCount = 0;
|
|
foreach ($rows as $row) {
|
|
$itemId = (int)($row['item_id'] ?? 0);
|
|
$invoiceId = (int)($row['invoice_id'] ?? 0);
|
|
$policyId = (int)($row['policy_id'] ?? 0);
|
|
$commissionAmount = (float)($row['commission_amount'] ?? 0);
|
|
if ($itemId <= 0) {
|
|
continue;
|
|
}
|
|
|
|
$affected = $this->db->table('partner_invoice_items')
|
|
->where('id', $itemId)
|
|
->where('invoice_id', $invoiceId)
|
|
->where('policy_id', $policyId)
|
|
->where('is_active', 1)
|
|
->update([
|
|
'commission_amount' => $commissionAmount,
|
|
'updated_at' => date('Y-m-d H:i:s'),
|
|
'updated_by' => $updatedBy,
|
|
]);
|
|
|
|
if ($affected) {
|
|
$updatedCount++;
|
|
}
|
|
}
|
|
|
|
return $updatedCount;
|
|
}
|
|
|
|
private function extractAgentIdsFromInvoice($agentJson): array
|
|
{
|
|
if (is_array($agentJson)) {
|
|
return array_values(array_map('intval', $agentJson));
|
|
}
|
|
if ($agentJson === null || $agentJson === '') {
|
|
return [];
|
|
}
|
|
|
|
$decoded = json_decode((string)$agentJson, true);
|
|
if (is_array($decoded)) {
|
|
return array_values(array_map('intval', $decoded));
|
|
}
|
|
|
|
if (is_numeric($agentJson)) {
|
|
return [(int)$agentJson];
|
|
}
|
|
|
|
return [];
|
|
}
|
|
|
|
private function generateProceedToken(): string
|
|
{
|
|
return bin2hex(random_bytes(16));
|
|
}
|
|
|
|
private function ensureBulkUploadStagingTable(): void
|
|
{
|
|
$sql = "CREATE TABLE IF NOT EXISTS invoice_bulk_upload_staging (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
proceed_token VARCHAR(64) NOT NULL UNIQUE,
|
|
payload_json LONGTEXT NULL,
|
|
mismatch_json LONGTEXT NULL,
|
|
is_active TINYINT(1) NOT NULL DEFAULT 1,
|
|
created_at DATETIME NULL,
|
|
created_by INT NULL,
|
|
updated_at DATETIME NULL,
|
|
updated_by INT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
|
|
$this->db->query($sql);
|
|
}
|
|
|
|
|
|
// public function getCommissionRateList()
|
|
// {
|
|
// try {
|
|
|
|
// $input = $this->request->getJSON(true);
|
|
|
|
// if (empty($input['broker_id']) || empty($input['issued_date'])) {
|
|
// return $this->respond([ 'status' => 'failed', 'code' => 400, 'message'=> 'issued_date are required' ], 400);
|
|
// }
|
|
|
|
// $invoice_id = isset($input['invoice_id']) ? $input['invoice_id'] : '';
|
|
|
|
|
|
// $data_previous = $this->PolicyModel
|
|
// ->select('partner_policy.policy_number as policy_no,partner_policy.id as policy_id , partner_policy.issued_date, partner_policy.commission_amount, pe.name as customer_name , pq.premium_amount')
|
|
// ->join(
|
|
// 'partner_enquiry pe',
|
|
// 'pe.id = partner_policy.enquiry_id AND pe.broker_id = ' . (int)$input['broker_id'],
|
|
// 'left'
|
|
// )
|
|
// ->join('partner_quotation pq','pq.id = partner_policy.quotation_id ', 'left')
|
|
// // ->where('partner_policy.pos_id', $input['pos_id'])
|
|
// ->where('partner_policy.issued_date <=', date('Y-m-d', strtotime($input['issued_date'])))
|
|
// ->where('partner_policy.is_active', 1)
|
|
// ->where('partner_policy.is_data_accuracy_checked', 1)
|
|
// ->findAll();
|
|
|
|
|
|
// $query = $this->PolicyModel
|
|
// ->select('partner_policy.policy_number as policy_no, partner_policy.id as policy_id, partner_policy.issued_date, partner_policy.commission_amount, pe.name as customer_name, pq.premium_amount, pi.id as invoice_id, pi.invoice_no')
|
|
// ->join(
|
|
// 'partner_enquiry pe',
|
|
// 'pe.id = partner_policy.enquiry_id AND pe.broker_id = ' . (int)$input['broker_id'],
|
|
// 'left'
|
|
// )
|
|
// ->join('partner_quotation pq','pq.id = partner_policy.quotation_id ', 'left')
|
|
// ->join('partner_invoice_items pii', 'pii.policy_id = partner_policy.id', 'left' )
|
|
// ->join('partner_invoice pi', 'pi.id = pii.invoice_id AND pi.is_active = 1', 'left')
|
|
// // ->where('partner_policy.pos_id', $input['pos_id'])
|
|
// ->where('partner_policy.issued_date <=', date('Y-m-d', strtotime($input['issued_date'])))
|
|
// ->where('partner_policy.is_active', 1)
|
|
// ->where('partner_policy.commission_amount > 0')
|
|
// ->where('partner_policy.is_data_accuracy_checked', 1);
|
|
// if (empty($invoice_id)) {
|
|
// $query->where('pii.policy_id IS NULL');
|
|
// } else {
|
|
// $query->where('pi.id', $invoice_id);
|
|
// }
|
|
|
|
// $data = $query->findAll();
|
|
|
|
// return $this->respond([
|
|
// 'status' => 'success',
|
|
// 'code' => 200,
|
|
// 'previous' => $data_previous,
|
|
// 'data' => $data
|
|
// ], 200);
|
|
|
|
// } catch (\Exception $e) {
|
|
// return $this->respond([
|
|
// 'status' => 'failed',
|
|
// 'code' => 500,
|
|
// 'message'=> $e->getMessage()
|
|
// ], 500);
|
|
// }
|
|
// }
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
}
|