root
1426 lines
68 KiB
PHP
1426 lines
68 KiB
PHP
<?php
|
|
|
|
namespace App\Controllers\View;
|
|
|
|
use App\Controllers\BaseController;
|
|
use App\Libraries\FinancialAttachmentService;
|
|
use App\Libraries\FinancialPersistenceException;
|
|
use App\Libraries\FinancialStatus;
|
|
use App\Libraries\InvoiceLedgerService;
|
|
use App\Libraries\ParentLedgerService;
|
|
use App\Libraries\RefundEligibilityService;
|
|
use App\Models\RefundModel;
|
|
use App\Models\RefundPayoutModel;
|
|
use App\Models\UserModel;
|
|
use App\Models\PaymentModel;
|
|
use App\Models\ConfigurationModel;
|
|
use App\Models\InvoiceModel;
|
|
use App\Models\EnrollmentModel;
|
|
use App\Services\EnrollmentStatusService;
|
|
use CodeIgniter\Exceptions\PageNotFoundException;
|
|
|
|
class RefundController extends BaseController
|
|
{
|
|
protected RefundModel $refundModel;
|
|
protected RefundPayoutModel $refundPayoutModel;
|
|
protected UserModel $userModel;
|
|
protected PaymentModel $paymentModel;
|
|
protected ConfigurationModel $configModel;
|
|
protected InvoiceModel $invoiceModel;
|
|
protected EnrollmentModel $enrollmentModel;
|
|
protected InvoiceLedgerService $invoiceLedgerService;
|
|
protected ParentLedgerService $parentLedgerService;
|
|
protected RefundEligibilityService $refundEligibilityService;
|
|
protected FinancialAttachmentService $financialAttachmentService;
|
|
protected $db;
|
|
|
|
// Allowed request types (mapped to your `refunds.request` column)
|
|
private const REQUEST_TYPES = ['overpayment','duplicate'];
|
|
// Allowed finalization decisions
|
|
private const DECISIONS = ['approved','rejected'];
|
|
private const PAYMENT_METHODS = ['Check','Online','Cash'];
|
|
|
|
public function __construct()
|
|
{
|
|
$this->refundModel = new RefundModel();
|
|
$this->refundPayoutModel = new RefundPayoutModel();
|
|
$this->userModel = new UserModel();
|
|
$this->paymentModel = new PaymentModel();
|
|
$this->configModel = new ConfigurationModel();
|
|
$this->invoiceModel = new InvoiceModel();
|
|
$this->enrollmentModel = new EnrollmentModel();
|
|
$this->invoiceLedgerService = new InvoiceLedgerService();
|
|
$this->parentLedgerService = new ParentLedgerService();
|
|
$this->refundEligibilityService = new RefundEligibilityService();
|
|
$this->financialAttachmentService = new FinancialAttachmentService();
|
|
$this->db = \Config\Database::connect();
|
|
}
|
|
|
|
private function requireWrite($result, string $code, $model = null): void
|
|
{
|
|
if ($result === false || $result === null || $result === 0) {
|
|
$details = is_object($model) && method_exists($model, 'errors') ? (array)$model->errors() : [];
|
|
throw new FinancialPersistenceException($code, $details);
|
|
}
|
|
}
|
|
|
|
private function requireTransactionStatus($db, string $code = 'TRANSACTION_FAILED'): void
|
|
{
|
|
if (method_exists($db, 'transStatus') && $db->transStatus() === false) {
|
|
throw new FinancialPersistenceException($code);
|
|
}
|
|
}
|
|
|
|
private function refundStatusForStorage(string $status): string
|
|
{
|
|
return match (FinancialStatus::normalizeRefundStatus($status)) {
|
|
FinancialStatus::REFUND_APPROVED => 'Approved',
|
|
FinancialStatus::REFUND_REJECTED => 'Rejected',
|
|
FinancialStatus::REFUND_PARTIALLY_PAID => 'Partial',
|
|
FinancialStatus::REFUND_PAID => 'Paid',
|
|
default => 'Pending',
|
|
};
|
|
}
|
|
|
|
private function refundOpenStatuses(): array
|
|
{
|
|
return ['Pending', 'Approved', 'Partial', 'pending', 'requested', 'approved', 'partial', 'partially_paid'];
|
|
}
|
|
|
|
private function refundFailureResponse(
|
|
string $publicCode,
|
|
string $publicMessage,
|
|
\Throwable $e,
|
|
array $context = []
|
|
) {
|
|
$context += [
|
|
'request_id' => service('request')->getHeaderLine('X-Request-ID') ?: bin2hex(random_bytes(8)),
|
|
'user_id' => session()->get('user_id'),
|
|
];
|
|
log_message('error', $publicCode . ': ' . $e->getMessage() . "\n" . $e->getTraceAsString() . ' context=' . json_encode($context));
|
|
|
|
return $this->response->setJSON([
|
|
'success' => false,
|
|
'code' => $publicCode,
|
|
'message' => $publicMessage,
|
|
]);
|
|
}
|
|
|
|
/** Get current school year from configuration and derive the current semester from calendar dates. */
|
|
private function getCurrentTerm(): array
|
|
{
|
|
$rows = $this->configModel
|
|
->select('config_key, config_value')
|
|
->whereIn('config_key', ['school_year'])
|
|
->findAll();
|
|
|
|
$map = [];
|
|
foreach ($rows as $r) {
|
|
$map[$r['config_key']] = $r['config_value'];
|
|
}
|
|
return [
|
|
'school_year' => $map['school_year'] ?? date('Y') . '-' . (date('Y') + 1),
|
|
'semester' => getSemester(),
|
|
];
|
|
}
|
|
|
|
/**
|
|
* Detect overpayments per parent for current school_year and create/update Pending refunds.
|
|
* When $triggerEvents is true, emits refundPending for newly created rows only.
|
|
*
|
|
* @return array [created_ids => int[], updated_ids => int[]]
|
|
*/
|
|
private function recalcOverpayments(bool $triggerEvents = false): array
|
|
{
|
|
$created = [];
|
|
$updated = [];
|
|
$term = $this->getCurrentTerm();
|
|
$year = (string)$term['school_year'];
|
|
$sem = (string)$term['semester'];
|
|
|
|
// Per-invoice overpayment detection (current school year)
|
|
try {
|
|
$invRows = $this->invoiceModel
|
|
->select('id, parent_id, school_year')
|
|
->where('school_year', $year)
|
|
->findAll();
|
|
if ($invRows) {
|
|
foreach ($invRows as $inv) {
|
|
$iid = (int)$inv['id'];
|
|
$pid = (int)$inv['parent_id'];
|
|
$ledger = $this->invoiceLedgerService->calculateInvoice($iid);
|
|
$over = (float)($ledger['customer_credit'] ?? 0.0);
|
|
if ($over > 0.0001) {
|
|
|
|
// If ANY open refund exists for this invoice, update it instead of creating another line
|
|
$openRow = $this->refundModel
|
|
->where('invoice_id', $iid)
|
|
->where('source_type', 'invoice_overpayment')
|
|
->where('source_id', $iid)
|
|
->whereIn('status', $this->refundOpenStatuses())
|
|
->orderBy('id', 'DESC')
|
|
->first();
|
|
if ($openRow) {
|
|
// Prefer merging into 'overpayment' if present, else update the open row
|
|
$this->requireWrite($this->refundModel->update(
|
|
(int)$openRow['id'],
|
|
$this->buildRefundRecalculationUpdate($openRow, $over, [
|
|
'source_type' => 'invoice_overpayment',
|
|
'source_id' => $iid,
|
|
])
|
|
), 'REFUND_RECALC_UPDATE_FAILED', $this->refundModel);
|
|
$updated[] = (int)$openRow['id'];
|
|
continue;
|
|
}
|
|
|
|
// Existing overpayment refund for this invoice?
|
|
$existing = $this->refundModel
|
|
->where('invoice_id', $iid)
|
|
->where('source_type', 'invoice_overpayment')
|
|
->where('source_id', $iid)
|
|
->whereIn('status', array_merge($this->refundOpenStatuses(), ['Paid', 'paid']))
|
|
->orderBy('id', 'DESC')
|
|
->first();
|
|
|
|
if (!$existing || FinancialStatus::normalizeRefundStatus($existing['status'] ?? null) === FinancialStatus::REFUND_PAID) {
|
|
$ok = $this->refundModel->insert([
|
|
'parent_id' => $pid,
|
|
'school_year' => $year,
|
|
'semester' => $sem,
|
|
'invoice_id' => $iid,
|
|
'refund_amount' => $over,
|
|
'requested_amount_cents' => (int) round($over * 100),
|
|
'approved_amount_cents' => null,
|
|
'currency' => 'USD',
|
|
'refund_paid_amount' => 0.00,
|
|
'status' => $this->refundStatusForStorage(FinancialStatus::REFUND_REQUESTED),
|
|
'request' => 'overpayment',
|
|
'source_type' => 'invoice_overpayment',
|
|
'source_id' => $iid,
|
|
'reason' => 'Auto-detected per-invoice overpayment',
|
|
'updated_by' => session()->get('user_id'),
|
|
'created_at' => utc_now(),
|
|
'updated_at' => utc_now(),
|
|
]);
|
|
$this->requireWrite($ok, 'REFUND_RECALC_INSERT_FAILED', $this->refundModel);
|
|
if ($ok) {
|
|
$created[] = (int)$this->refundModel->getInsertID();
|
|
if ($triggerEvents) {
|
|
$user = (new UserModel())->select('id, email, firstname, lastname')->find($pid) ?: [];
|
|
$eventData = [
|
|
'user_id' => (int)($user['id'] ?? $pid),
|
|
'email' => $user['email'] ?? null,
|
|
'firstname' => $user['firstname'] ?? null,
|
|
'lastname' => $user['lastname'] ?? null,
|
|
'amount' => $over,
|
|
'portalLink'=> base_url('/login'),
|
|
];
|
|
\CodeIgniter\Events\Events::trigger('refundPending', $eventData, []);
|
|
}
|
|
}
|
|
} else {
|
|
// Keep Pending/Partial in sync with current overpayment
|
|
if (in_array(FinancialStatus::normalizeRefundStatus($existing['status'] ?? null), [FinancialStatus::REFUND_REQUESTED, FinancialStatus::REFUND_PARTIALLY_PAID], true)) {
|
|
$this->requireWrite($this->refundModel->update(
|
|
(int)$existing['id'],
|
|
$this->buildRefundRecalculationUpdate($existing, $over, [
|
|
'source_type' => 'invoice_overpayment',
|
|
'source_id' => $iid,
|
|
])
|
|
), 'REFUND_RECALC_UPDATE_FAILED', $this->refundModel);
|
|
$updated[] = (int)$existing['id'];
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
} catch (\Throwable $e) {
|
|
log_message('error', 'recalcOverpayments per-invoice failed: ' . $e->getMessage());
|
|
}
|
|
|
|
return ['created_ids' => $created, 'updated_ids' => $updated];
|
|
}
|
|
|
|
/**
|
|
* Parent financial summary for the selected term.
|
|
*/
|
|
private function getParentFinancialSummary(int $parentId, string $schoolYear, string $semester): array
|
|
{
|
|
$projection = $this->parentLedgerService->getParentProjection($parentId, $schoolYear, $semester);
|
|
|
|
return [
|
|
'total_invoiced' => (float)($projection['gross_charges'] ?? 0),
|
|
'net_invoice_charges' => (float)($projection['net_invoice_charges'] ?? 0),
|
|
'total_paid_in' => (float)($projection['valid_payments'] ?? 0),
|
|
'total_refunded' => (float)($projection['completed_refunds'] ?? 0),
|
|
'approved_refund_reservations' => (float)($projection['approved_refund_reservations'] ?? 0),
|
|
'unapplied_balance' => (float)($projection['customer_credit'] ?? 0),
|
|
'available_refundable_credit' => (float)($projection['available_refundable_credit'] ?? 0),
|
|
'projection' => $projection,
|
|
];
|
|
}
|
|
|
|
private function buildRefundRecalculationUpdate(array $refund, float $calculatedAmount, array $extra = []): array
|
|
{
|
|
$refundId = (int)($refund['id'] ?? 0);
|
|
$calculatedCents = max(0, (int)round($calculatedAmount * 100));
|
|
$paidCents = $this->refundEligibilityService->getCompletedPayoutTotalCentsForRefund($refundId);
|
|
$status = strtolower((string)($refund['status'] ?? ''));
|
|
$isApprovedState = in_array($status, ['approved', 'partial', 'paid', 'partially_paid'], true);
|
|
$targetCents = $isApprovedState ? max($calculatedCents, $paidCents) : $calculatedCents;
|
|
$now = utc_now();
|
|
|
|
$update = array_merge([
|
|
'refund_amount' => $targetCents / 100,
|
|
'updated_at' => $now,
|
|
'updated_by' => session()->get('user_id'),
|
|
], $extra);
|
|
|
|
if ($isApprovedState) {
|
|
$update['approved_amount_cents'] = $targetCents;
|
|
} else {
|
|
$update['requested_amount_cents'] = $targetCents;
|
|
}
|
|
|
|
if ($isApprovedState && $paidCents > $calculatedCents) {
|
|
$message = sprintf(
|
|
'Completed payouts (%0.2f) exceed recalculated refundable credit (%0.2f).',
|
|
$paidCents / 100,
|
|
$calculatedCents / 100
|
|
);
|
|
$update['reconciliation_status'] = 'requires_review';
|
|
$update['reconciliation_reason'] = $message;
|
|
$update['reconciliation_required_at'] = $now;
|
|
log_message('critical', 'Refund reconciliation required for refund #' . $refundId . ': ' . $message);
|
|
} else {
|
|
$update['reconciliation_status'] = null;
|
|
$update['reconciliation_reason'] = null;
|
|
$update['reconciliation_required_at'] = null;
|
|
}
|
|
|
|
return $update;
|
|
}
|
|
|
|
/** Optional helper if you want a quick API for balances in UI */
|
|
public function parentBalances(int $parentId)
|
|
{
|
|
$t = $this->getCurrentTerm();
|
|
return $this->response->setJSON($this->getParentFinancialSummary($parentId, $t['school_year'], $t['semester']));
|
|
}
|
|
|
|
/**
|
|
* Create refund request.
|
|
* Stores source/type in `refunds.request` as: overpayment|duplicate
|
|
*
|
|
* @param int $parentId
|
|
* @param float $amount
|
|
* @param string|null $requestType overpayment|duplicate
|
|
* @param int|null $invoiceId required for overpayment; derived for duplicate
|
|
* @param int|null $paymentId useful for duplicate
|
|
* @param string|null $reason
|
|
*/
|
|
public function requestRefund(
|
|
?int $parentId = null,
|
|
?float $amount = null,
|
|
?string $requestType = null,
|
|
?int $invoiceId = null,
|
|
?int $paymentId = null,
|
|
?string $reason = null
|
|
) {
|
|
$parentId = (int)($parentId ?: $this->request->getPost('parent_id'));
|
|
$amount = (float)($amount ?: $this->request->getPost('amount') ?: $this->request->getPost('refund_amount'));
|
|
$requestType = $requestType ?: $this->request->getPost('request_type') ?: $this->request->getPost('request') ?: 'overpayment';
|
|
$invoiceId = $invoiceId ?: ($this->request->getPost('invoice_id') !== null ? (int) $this->request->getPost('invoice_id') : null);
|
|
$paymentId = $paymentId ?: ($this->request->getPost('payment_id') !== null ? (int) $this->request->getPost('payment_id') : null);
|
|
$reason = $reason ?: $this->request->getPost('reason');
|
|
|
|
$requestType = strtolower((string)$requestType);
|
|
if (!in_array($requestType, self::REQUEST_TYPES, true)) {
|
|
return $this->response->setJSON(['error' => 'Invalid refund request type.']);
|
|
}
|
|
if ($parentId <= 0) {
|
|
return $this->response->setJSON(['error' => 'Parent ID is required.']);
|
|
}
|
|
|
|
$parent = $this->userModel->find($parentId);
|
|
if (!$parent) {
|
|
return $this->response->setJSON(['error' => 'Parent not found.']);
|
|
}
|
|
if ($amount <= 0) {
|
|
return $this->response->setJSON(['error' => 'Refund amount must be > 0.']);
|
|
}
|
|
|
|
$term = $this->getCurrentTerm();
|
|
$schoolYear = $term['school_year'];
|
|
$semester = $term['semester'];
|
|
|
|
if ($requestType === 'overpayment') {
|
|
if (empty($invoiceId)) {
|
|
return $this->response->setJSON(['error' => 'invoice_id is required for overpayment refunds.']);
|
|
}
|
|
$inv = $this->invoiceModel->find($invoiceId);
|
|
if (!$inv || (int)$inv['parent_id'] !== $parentId) {
|
|
return $this->response->setJSON(['error' => 'Invoice not found for parent.']);
|
|
}
|
|
// Optionally cap to eligible amount per your policy.
|
|
}
|
|
|
|
if ($requestType === 'duplicate') {
|
|
if (empty($paymentId)) {
|
|
return $this->response->setJSON(['error' => 'payment_id is required for duplicate payment refunds.']);
|
|
}
|
|
$payment = $this->paymentModel->find($paymentId);
|
|
if (!$payment || (int)($payment['parent_id'] ?? 0) !== $parentId) {
|
|
return $this->response->setJSON(['error' => 'Payment not found for parent.']);
|
|
}
|
|
if (!empty($invoiceId) && (int)$invoiceId !== (int)($payment['invoice_id'] ?? 0)) {
|
|
return $this->response->setJSON(['error' => 'Duplicate payment refund invoice does not match the payment invoice.']);
|
|
}
|
|
$invoiceId = (int)($payment['invoice_id'] ?? 0) ?: null;
|
|
}
|
|
|
|
$sourceType = match ($requestType) {
|
|
'overpayment' => 'invoice_overpayment',
|
|
'duplicate' => 'payment_duplicate',
|
|
};
|
|
$sourceId = $sourceType === 'payment_duplicate' ? (int)$paymentId : (int)($invoiceId ?? 0);
|
|
if ($sourceId <= 0) {
|
|
return $this->response->setJSON(['error' => 'A refund source record is required.']);
|
|
}
|
|
|
|
try {
|
|
$eligibility = $this->refundEligibilityService->calculateAvailableCredit(
|
|
$parentId,
|
|
$invoiceId,
|
|
$sourceType,
|
|
$sourceId
|
|
);
|
|
$this->refundEligibilityService->validateRequestedAmount($eligibility, (int)round($amount * 100));
|
|
} catch (\Throwable $e) {
|
|
return $this->response->setJSON(['error' => $e->getMessage()]);
|
|
}
|
|
|
|
$payload = [
|
|
'parent_id' => $parentId,
|
|
'school_year' => $schoolYear,
|
|
'semester' => $semester,
|
|
'invoice_id' => $invoiceId,
|
|
'refund_amount' => $amount,
|
|
'requested_amount_cents' => (int) round($amount * 100),
|
|
'approved_amount_cents' => null,
|
|
'currency' => 'USD',
|
|
'refund_paid_amount' => 0.00, // IMPORTANT: your column is NOT NULL
|
|
'status' => $this->refundStatusForStorage(FinancialStatus::REFUND_REQUESTED),
|
|
'reason' => $reason,
|
|
'request' => $requestType, // <- store the source/type here
|
|
'source_type' => $sourceType,
|
|
'source_id' => $sourceId,
|
|
'note' => $paymentId ? ('duplicate-of-payment#' . $paymentId) : null,
|
|
'created_at' => utc_now(),
|
|
'updated_at' => utc_now(),
|
|
];
|
|
$ok = $this->refundModel->insert($payload);
|
|
|
|
if (!$ok) {
|
|
return $this->response->setJSON(['error' => 'Failed to create refund request.']);
|
|
}
|
|
$this->requireWrite($ok, 'REFUND_REQUEST_INSERT_FAILED', $this->refundModel);
|
|
|
|
if (!empty($invoiceId)) {
|
|
try {
|
|
$this->invoiceLedgerService->recalculateInvoice((int) $invoiceId);
|
|
} catch (\Throwable $e) {
|
|
log_message('error', 'requestRefund recalc failed: ' . $e->getMessage());
|
|
}
|
|
}
|
|
|
|
// Fire refundPending notification/event for newly created refunds
|
|
try {
|
|
$user = $this->userModel->select('id, email, firstname, lastname')->find($parentId) ?: [];
|
|
$eventData = [
|
|
'user_id' => (int)($user['id'] ?? $parentId),
|
|
'email' => $user['email'] ?? null,
|
|
'firstname' => $user['firstname'] ?? null,
|
|
'lastname' => $user['lastname'] ?? null,
|
|
'amount' => (float)$amount,
|
|
'portalLink'=> base_url('/login'),
|
|
];
|
|
\CodeIgniter\Events\Events::trigger('refundPending', $eventData, []);
|
|
} catch (\Throwable $e) {
|
|
log_message('error', 'requestRefund: failed to trigger refundPending: ' . $e->getMessage());
|
|
}
|
|
|
|
return $this->response->setJSON(['success' => 'Refund requested']);
|
|
}
|
|
|
|
// Approve refund (no money movement)
|
|
public function approveRefund(int $refundId)
|
|
{
|
|
$this->db->transBegin();
|
|
|
|
try {
|
|
$refund = $this->db->query('SELECT * FROM refunds WHERE id = ? FOR UPDATE', [$refundId])->getRowArray();
|
|
if (!$refund) {
|
|
throw new \RuntimeException('Refund not found');
|
|
}
|
|
if (FinancialStatus::normalizeRefundStatus($refund['status'] ?? null) !== FinancialStatus::REFUND_REQUESTED) {
|
|
throw new \RuntimeException('Only pending refunds can be approved.');
|
|
}
|
|
if (!empty($refund['invoice_id'])) {
|
|
$this->db->query('SELECT id FROM invoices WHERE id = ? FOR UPDATE', [(int) $refund['invoice_id']]);
|
|
}
|
|
|
|
$sourceType = (string)($refund['source_type'] ?? '');
|
|
$sourceId = (int)($refund['source_id'] ?? 0);
|
|
if ($sourceType === '' || $sourceId <= 0) {
|
|
throw new \RuntimeException('Refund source is required for approval.');
|
|
}
|
|
$this->lockRefundSourceForUpdate($sourceType, $sourceId, isset($refund['invoice_id']) ? (int)$refund['invoice_id'] : null);
|
|
$requestedCents = (int)round(((float)($refund['refund_amount'] ?? 0)) * 100);
|
|
$eligibility = $this->refundEligibilityService->calculateAvailableCredit(
|
|
(int)$refund['parent_id'],
|
|
isset($refund['invoice_id']) ? (int)$refund['invoice_id'] : null,
|
|
$sourceType,
|
|
$sourceId,
|
|
$refundId
|
|
);
|
|
$this->refundEligibilityService->validateRequestedAmount($eligibility, $requestedCents);
|
|
|
|
$ok = $this->refundModel->update($refundId, [
|
|
'status' => $this->refundStatusForStorage(FinancialStatus::REFUND_APPROVED),
|
|
'approved_amount_cents' => $requestedCents,
|
|
'approved_at' => utc_now(),
|
|
'approved_by' => session()->get('user_id'),
|
|
'updated_at' => utc_now(),
|
|
'updated_by' => session()->get('user_id'),
|
|
]);
|
|
$this->requireWrite($ok, 'REFUND_APPROVE_UPDATE_FAILED', $this->refundModel);
|
|
|
|
if (!empty($refund['invoice_id'])) {
|
|
$this->invoiceLedgerService->recalculateInvoice((int) $refund['invoice_id']);
|
|
}
|
|
|
|
$this->requireTransactionStatus($this->db);
|
|
$this->db->transCommit();
|
|
} catch (\Throwable $e) {
|
|
$this->db->transRollback();
|
|
return $this->refundFailureResponse('REFUND_APPROVAL_FAILED', 'The refund could not be approved.', $e, [
|
|
'refund_id' => $refundId,
|
|
]);
|
|
}
|
|
|
|
return $this->response->setJSON($ok ? ['success' => 'Refund approved'] : ['error' => 'Approve failed']);
|
|
}
|
|
|
|
// Reject refund
|
|
public function rejectRefund(int $refundId)
|
|
{
|
|
$this->db->transBegin();
|
|
|
|
try {
|
|
$refund = $this->db->query('SELECT * FROM refunds WHERE id = ? FOR UPDATE', [$refundId])->getRowArray();
|
|
if (!$refund) {
|
|
throw new \RuntimeException('Refund not found.');
|
|
}
|
|
if (FinancialStatus::normalizeRefundStatus($refund['status'] ?? null) !== FinancialStatus::REFUND_REQUESTED) {
|
|
throw new \RuntimeException('Only requested refunds can be rejected.');
|
|
}
|
|
if (!empty($refund['invoice_id'])) {
|
|
$this->db->query('SELECT id FROM invoices WHERE id = ? FOR UPDATE', [(int) $refund['invoice_id']]);
|
|
}
|
|
|
|
$ok = $this->refundModel->update($refundId, [
|
|
'status' => $this->refundStatusForStorage(FinancialStatus::REFUND_REJECTED),
|
|
'reason' => $this->request->getPost('reason') ?: ($refund['reason'] ?? null),
|
|
'approved_at' => utc_now(),
|
|
'approved_by' => session()->get('user_id'),
|
|
'updated_at' => utc_now(),
|
|
'updated_by' => session()->get('user_id'),
|
|
]);
|
|
$this->requireWrite($ok, 'REFUND_REJECT_UPDATE_FAILED', $this->refundModel);
|
|
|
|
if (!empty($refund['invoice_id'])) {
|
|
$this->invoiceLedgerService->recalculateInvoice((int) $refund['invoice_id']);
|
|
}
|
|
|
|
$this->requireTransactionStatus($this->db);
|
|
$this->db->transCommit();
|
|
} catch (\Throwable $e) {
|
|
$this->db->transRollback();
|
|
return $this->refundFailureResponse('REFUND_REJECTION_FAILED', 'The refund could not be rejected.', $e, [
|
|
'refund_id' => $refundId,
|
|
]);
|
|
}
|
|
|
|
return $this->response->setJSON($ok ? ['success' => 'Refund rejected'] : ['error' => 'Reject failed']);
|
|
}
|
|
|
|
/**
|
|
* POST: refund_id, paid_amount, payment_method (Check|Online|Cash), check_number, check_file (optional)
|
|
* Marks payout, which can be partial, after rechecking the refund under a row lock.
|
|
*/
|
|
public function payRefund(int $refundId)
|
|
{
|
|
return $this->updatePayment($refundId);
|
|
}
|
|
|
|
public function updatePayment(?int $routeRefundId = null)
|
|
{
|
|
$refundId = (int)($routeRefundId ?: $this->request->getPost('refund_id'));
|
|
$newPaidAmount = (float)$this->request->getPost('paid_amount');
|
|
$refundMethod = $this->request->getPost('payment_method'); // Check|Online|Cash
|
|
$checkNbr = $this->request->getPost('check_number');
|
|
$idempotencyKey = trim((string)($this->request->getPost('idempotency_key') ?? ''));
|
|
$externalReference = trim((string)($this->request->getPost('external_reference') ?? ''));
|
|
|
|
if ($refundId <= 0) return $this->response->setJSON(['error' => 'Missing refund ID.']);
|
|
if ($newPaidAmount <= 0) return $this->response->setJSON(['error' => 'Valid paid amount is required.']);
|
|
if (!in_array($refundMethod, self::PAYMENT_METHODS, true)) {
|
|
return $this->response->setJSON(['error' => 'Invalid refund payment method.']);
|
|
}
|
|
if ($refundMethod === 'Check' && trim((string) $checkNbr) === '') {
|
|
return $this->response->setJSON(['error' => 'Check number is required for check refunds.']);
|
|
}
|
|
if ($idempotencyKey === '') {
|
|
return $this->response->setJSON(['error' => 'Missing payout idempotency key.']);
|
|
}
|
|
|
|
$refund = $this->refundModel->find($refundId);
|
|
if (!$refund) return $this->response->setJSON(['error' => 'Refund not found.']);
|
|
|
|
$stagedEvidence = null;
|
|
if ($refundMethod === 'Check') {
|
|
try {
|
|
$checkFile = $this->request->getFile('check_file');
|
|
$stagedEvidence = $this->financialAttachmentService->stageUploadedFile($checkFile, 'checks');
|
|
} catch (\RuntimeException $e) {
|
|
return $this->response->setJSON(['error' => $e->getMessage()]);
|
|
}
|
|
}
|
|
|
|
$db = db_connect();
|
|
$db->transBegin();
|
|
$payoutId = null;
|
|
$affectedInvoiceId = 0;
|
|
$evidenceWarning = null;
|
|
|
|
try {
|
|
$lockedRefund = $db->query('SELECT * FROM refunds WHERE id = ? FOR UPDATE', [$refundId])->getRowArray();
|
|
if (!$lockedRefund) {
|
|
throw new \RuntimeException('Refund not found.');
|
|
}
|
|
|
|
$newPaidCents = (int)round($newPaidAmount * 100);
|
|
$payoutFingerprint = $this->buildPayoutFingerprint(
|
|
'refund_payout',
|
|
$refundId,
|
|
$newPaidCents,
|
|
$refundMethod,
|
|
(string)($lockedRefund['currency'] ?? 'USD') ?: 'USD',
|
|
$externalReference !== '' ? $externalReference : null
|
|
);
|
|
$existingPayout = $this->refundPayoutModel->where('idempotency_key', $idempotencyKey)->first();
|
|
if ($existingPayout) {
|
|
if (!$this->payoutFingerprintMatches($existingPayout, 'refund_payout', $payoutFingerprint)) {
|
|
$this->financialAttachmentService->discardStagedFile($stagedEvidence);
|
|
$db->transCommit();
|
|
return $this->response->setStatusCode(409)->setJSON(['error' => 'IDEMPOTENCY_CONFLICT']);
|
|
}
|
|
|
|
$this->financialAttachmentService->discardStagedFile($stagedEvidence);
|
|
$db->transCommit();
|
|
return $this->response->setJSON(['success' => 'Payment already recorded.']);
|
|
}
|
|
|
|
if (!in_array(FinancialStatus::normalizeRefundStatus($lockedRefund['status'] ?? null), [FinancialStatus::REFUND_APPROVED, FinancialStatus::REFUND_PARTIALLY_PAID], true)) {
|
|
throw new \RuntimeException('Refund must be Approved/Partial to pay.');
|
|
}
|
|
|
|
$approvedCents = isset($lockedRefund['approved_amount_cents']) && $lockedRefund['approved_amount_cents'] !== null
|
|
? (int)$lockedRefund['approved_amount_cents']
|
|
: (int)round(((float)($lockedRefund['refund_amount'] ?? 0)) * 100);
|
|
$oldPaidCents = $this->refundEligibilityService->getNetCompletedCashOutCentsForRefund($refundId);
|
|
$oldPaid = $oldPaidCents / 100;
|
|
$target = $approvedCents / 100;
|
|
$total = $oldPaid + $newPaidAmount;
|
|
|
|
if ($newPaidCents > max(0, $approvedCents - $oldPaidCents)) {
|
|
throw new \RuntimeException('Total paid cannot exceed approved refund amount.');
|
|
}
|
|
|
|
$isOnline = $refundMethod === 'Online';
|
|
$payoutStatus = $isOnline ? 'processing' : 'completed';
|
|
$newStatus = ($total < $target) ? FinancialStatus::REFUND_PARTIALLY_PAID : FinancialStatus::REFUND_PAID;
|
|
|
|
if (empty($lockedRefund['invoice_id'])) {
|
|
throw new \RuntimeException('Refund payout requires a specific invoice credit source.');
|
|
}
|
|
$sourceType = (string)($lockedRefund['source_type'] ?? '');
|
|
$sourceId = (int)($lockedRefund['source_id'] ?? 0);
|
|
if ($sourceType === '' || $sourceId <= 0) {
|
|
throw new \RuntimeException('Refund source is required for payout.');
|
|
}
|
|
|
|
$affectedInvoiceId = (int) ($lockedRefund['invoice_id'] ?? 0);
|
|
if ($affectedInvoiceId > 0) {
|
|
$db->query('SELECT id FROM invoices WHERE id = ? FOR UPDATE', [$affectedInvoiceId]);
|
|
}
|
|
$this->lockRefundSourceForUpdate($sourceType, $sourceId, $affectedInvoiceId ?: null);
|
|
|
|
$eligibility = $this->refundEligibilityService->calculateAvailableCredit(
|
|
(int)$lockedRefund['parent_id'],
|
|
$affectedInvoiceId > 0 ? $affectedInvoiceId : null,
|
|
$sourceType,
|
|
$sourceId,
|
|
$refundId
|
|
);
|
|
$this->refundEligibilityService->validateRequestedAmount($eligibility, $newPaidCents);
|
|
|
|
$payoutId = $this->refundPayoutModel->insert([
|
|
'refund_id' => $refundId,
|
|
'school_year' => (string)($lockedRefund['school_year'] ?? ''),
|
|
'amount_cents' => $newPaidCents,
|
|
'currency' => (string)($lockedRefund['currency'] ?? 'USD') ?: 'USD',
|
|
'payout_type' => 'cash_out',
|
|
'payment_method' => $refundMethod,
|
|
'status' => $payoutStatus,
|
|
'external_reference' => $externalReference !== '' ? $externalReference : null,
|
|
'check_number' => $checkNbr,
|
|
'check_date' => null,
|
|
'evidence_path' => null,
|
|
'idempotency_key' => $idempotencyKey,
|
|
'operation_type' => 'refund_payout',
|
|
'request_fingerprint_hash' => $payoutFingerprint,
|
|
'processed_by' => session()->get('user_id'),
|
|
'processed_at' => utc_now(),
|
|
'reversed_payout_id' => null,
|
|
'failure_code' => null,
|
|
'failure_message' => null,
|
|
'created_at' => utc_now(),
|
|
'updated_at' => utc_now(),
|
|
]);
|
|
if (!$payoutId) {
|
|
throw new \RuntimeException('Refund payout could not be recorded.');
|
|
}
|
|
$this->requireWrite($payoutId, 'REFUND_PAYOUT_INSERT_FAILED', $this->refundPayoutModel);
|
|
|
|
$refundProjection = [
|
|
'updated_at' => utc_now(),
|
|
'updated_by' => session()->get('user_id'),
|
|
'refund_method' => $refundMethod,
|
|
'check_nbr' => $checkNbr,
|
|
'check_file' => $lockedRefund['check_file'] ?? null,
|
|
'invoice_id' => $affectedInvoiceId ?: null,
|
|
];
|
|
if (!$isOnline) {
|
|
$refundProjection['refund_paid_amount'] = $total;
|
|
$refundProjection['status'] = $this->refundStatusForStorage($newStatus);
|
|
$refundProjection['refunded_at'] = utc_now();
|
|
}
|
|
if (!$this->refundModel->update($refundId, $refundProjection)) {
|
|
throw new FinancialPersistenceException('REFUND_PROJECTION_UPDATE_FAILED', $this->refundModel->errors());
|
|
}
|
|
|
|
if (!$isOnline && $newStatus === FinancialStatus::REFUND_PAID) {
|
|
$this->markWithdrawalEnrollmentComplete($lockedRefund);
|
|
}
|
|
|
|
if (!$isOnline && $affectedInvoiceId > 0) {
|
|
$this->invoiceLedgerService->recalculateInvoice($affectedInvoiceId);
|
|
}
|
|
|
|
$this->requireTransactionStatus($db);
|
|
$db->transCommit();
|
|
} catch (\Throwable $e) {
|
|
$db->transRollback();
|
|
$this->financialAttachmentService->discardStagedFile($stagedEvidence);
|
|
return $this->refundFailureResponse('REFUND_PAYOUT_FAILED', 'The refund could not be processed.', $e, [
|
|
'refund_id' => $refundId,
|
|
'idempotency_key' => $idempotencyKey,
|
|
]);
|
|
}
|
|
|
|
if ($stagedEvidence !== null && $payoutId !== null) {
|
|
try {
|
|
$finalName = $this->financialAttachmentService->finalizeStagedFile($stagedEvidence);
|
|
$this->requireWrite($this->refundPayoutModel->update((int)$payoutId, [
|
|
'evidence_path' => $finalName,
|
|
'updated_at' => utc_now(),
|
|
]), 'REFUND_PAYOUT_EVIDENCE_UPDATE_FAILED', $this->refundPayoutModel);
|
|
$this->requireWrite($this->refundModel->update($refundId, [
|
|
'check_file' => $finalName,
|
|
'updated_at' => utc_now(),
|
|
'updated_by' => session()->get('user_id'),
|
|
]), 'REFUND_EVIDENCE_UPDATE_FAILED', $this->refundModel);
|
|
} catch (\Throwable $e) {
|
|
$this->financialAttachmentService->discardStagedFile($stagedEvidence);
|
|
$evidenceWarning = 'Payment recorded, but evidence could not be finalized.';
|
|
log_message('critical', 'Refund payout evidence incomplete for payout #' . (int)$payoutId . ': ' . $e->getMessage());
|
|
try {
|
|
$this->requireWrite($this->refundPayoutModel->update((int)$payoutId, [
|
|
'failure_code' => 'EVIDENCE_INCOMPLETE',
|
|
'failure_message' => $evidenceWarning,
|
|
'updated_at' => utc_now(),
|
|
]), 'REFUND_PAYOUT_EVIDENCE_MARK_FAILED', $this->refundPayoutModel);
|
|
} catch (\Throwable $markError) {
|
|
log_message('critical', 'Unable to mark refund payout evidence incomplete for payout #' . (int)$payoutId . ': ' . $markError->getMessage());
|
|
}
|
|
}
|
|
}
|
|
|
|
$payload = ['success' => 'Payment updated successfully.'];
|
|
if ($evidenceWarning !== null) {
|
|
$payload['warning'] = $evidenceWarning;
|
|
}
|
|
|
|
return $this->response->setJSON($payload);
|
|
}
|
|
|
|
private function markWithdrawalEnrollmentComplete(array $refund): void
|
|
{
|
|
if ((string)($refund['source_type'] ?? '') !== 'tuition_withdrawal') {
|
|
return;
|
|
}
|
|
|
|
$calculationId = (int)($refund['withdrawal_calculation_id'] ?? 0);
|
|
if ($calculationId <= 0 || ! $this->db->tableExists('withdrawal_financial_calculations')) {
|
|
return;
|
|
}
|
|
|
|
$calculation = $this->db->query(
|
|
'SELECT enrollment_id FROM withdrawal_financial_calculations WHERE id = ? FOR UPDATE',
|
|
[$calculationId]
|
|
)->getRowArray();
|
|
$enrollmentId = (int)($calculation['enrollment_id'] ?? 0);
|
|
if ($enrollmentId <= 0) {
|
|
return;
|
|
}
|
|
|
|
$enrollment = $this->db->query(
|
|
'SELECT * FROM enrollments WHERE id = ? FOR UPDATE',
|
|
[$enrollmentId]
|
|
)->getRowArray();
|
|
if (! $enrollment) {
|
|
return;
|
|
}
|
|
|
|
$statusService = new EnrollmentStatusService($this->db);
|
|
$statusService->upsertStatus([
|
|
'id' => (int)$enrollment['id'],
|
|
'student_id' => (int)$enrollment['student_id'],
|
|
'parent_id' => (int)$enrollment['parent_id'],
|
|
'school_year' => (string)$enrollment['school_year'],
|
|
'semester' => (string)($enrollment['semester'] ?? ''),
|
|
'enrollment_status' => 'withdrawn',
|
|
'admission_status' => (string)($enrollment['admission_status'] ?? 'pending'),
|
|
'is_withdrawn' => 1,
|
|
'withdrawal_date' => $enrollment['withdrawal_date'] ?? null,
|
|
'updated_at' => utc_now(),
|
|
], (int)(session()->get('user_id') ?? 0) ?: null, 'withdrawal_refund_paid');
|
|
}
|
|
|
|
public function reversePayout(?int $routePayoutId = null)
|
|
{
|
|
$payoutId = (int)($routePayoutId ?: $this->request->getPost('payout_id'));
|
|
$reason = trim((string)($this->request->getPost('reason') ?? ''));
|
|
$idempotencyKey = trim((string)($this->request->getPost('idempotency_key') ?? ''));
|
|
$amountPost = $this->request->getPost('amount');
|
|
$requestedCents = $amountPost !== null && $amountPost !== ''
|
|
? (int)round(((float)$amountPost) * 100)
|
|
: null;
|
|
|
|
if ($payoutId <= 0) {
|
|
return $this->response->setJSON(['error' => 'Missing refund payout ID.']);
|
|
}
|
|
if ($reason === '') {
|
|
return $this->response->setJSON(['error' => 'Reversal reason is required.']);
|
|
}
|
|
if ($idempotencyKey === '') {
|
|
return $this->response->setJSON(['error' => 'Missing reversal idempotency key.']);
|
|
}
|
|
if ($requestedCents !== null && $requestedCents <= 0) {
|
|
return $this->response->setJSON(['error' => 'Reversal amount must be greater than zero.']);
|
|
}
|
|
|
|
$db = db_connect();
|
|
$db->transBegin();
|
|
|
|
try {
|
|
$requestedFingerprint = null;
|
|
$existingReversal = $this->refundPayoutModel->where('idempotency_key', $idempotencyKey)->first();
|
|
|
|
$payout = $db->query('SELECT * FROM refund_payouts WHERE id = ? FOR UPDATE', [$payoutId])->getRowArray();
|
|
if (!$payout) {
|
|
throw new \RuntimeException('Refund payout not found.');
|
|
}
|
|
if ((string)($payout['payout_type'] ?? '') !== 'cash_out' || (string)($payout['status'] ?? '') !== 'completed') {
|
|
throw new \RuntimeException('Only completed cash-out payouts can be reversed.');
|
|
}
|
|
|
|
$refundId = (int)($payout['refund_id'] ?? 0);
|
|
$refund = $db->query('SELECT * FROM refunds WHERE id = ? FOR UPDATE', [$refundId])->getRowArray();
|
|
if (!$refund) {
|
|
throw new \RuntimeException('Refund not found.');
|
|
}
|
|
if (!empty($refund['invoice_id'])) {
|
|
$db->query('SELECT id FROM invoices WHERE id = ? FOR UPDATE', [(int)$refund['invoice_id']]);
|
|
}
|
|
|
|
$db->query('SELECT id FROM refund_payouts WHERE reversed_payout_id = ? FOR UPDATE', [$payoutId])->getResultArray();
|
|
$unreversedCents = $this->calculateUnreversedPayoutAmountCents((int)$payout['id'], (int)$payout['amount_cents']);
|
|
if ($unreversedCents <= 0) {
|
|
throw new \RuntimeException('Refund payout is already fully reversed.');
|
|
}
|
|
$reverseCents = $requestedCents ?? $unreversedCents;
|
|
if ($reverseCents > $unreversedCents) {
|
|
throw new \RuntimeException('Reversal amount exceeds unreversed payout amount.');
|
|
}
|
|
$requestedFingerprint = $this->buildPayoutFingerprint(
|
|
'refund_reversal',
|
|
$refundId,
|
|
$reverseCents,
|
|
(string)($payout['payment_method'] ?? ''),
|
|
(string)($payout['currency'] ?? $refund['currency'] ?? 'USD') ?: 'USD',
|
|
'reversal:' . $payoutId
|
|
);
|
|
if ($existingReversal) {
|
|
if (!$this->payoutFingerprintMatches($existingReversal, 'refund_reversal', $requestedFingerprint)) {
|
|
$db->transCommit();
|
|
return $this->response->setStatusCode(409)->setJSON(['error' => 'IDEMPOTENCY_CONFLICT']);
|
|
}
|
|
|
|
$db->transCommit();
|
|
return $this->response->setJSON(['success' => 'Payout reversal already recorded.']);
|
|
}
|
|
|
|
$reversalId = $this->refundPayoutModel->insert([
|
|
'refund_id' => $refundId,
|
|
'school_year' => (string)($refund['school_year'] ?? ''),
|
|
'amount_cents' => $reverseCents,
|
|
'currency' => (string)($payout['currency'] ?? $refund['currency'] ?? 'USD') ?: 'USD',
|
|
'payout_type' => 'reversal',
|
|
'payment_method' => $payout['payment_method'] ?? null,
|
|
'status' => 'completed',
|
|
'external_reference' => 'reversal:' . $payoutId,
|
|
'check_number' => null,
|
|
'check_date' => null,
|
|
'evidence_path' => null,
|
|
'idempotency_key' => $idempotencyKey,
|
|
'operation_type' => 'refund_reversal',
|
|
'request_fingerprint_hash' => $requestedFingerprint,
|
|
'processed_by' => session()->get('user_id'),
|
|
'processed_at' => utc_now(),
|
|
'reversed_payout_id' => $payoutId,
|
|
'failure_code' => null,
|
|
'failure_message' => $reason,
|
|
'created_at' => utc_now(),
|
|
'updated_at' => utc_now(),
|
|
]);
|
|
if (!$reversalId) {
|
|
throw new \RuntimeException('Refund payout reversal could not be recorded.');
|
|
}
|
|
$this->requireWrite($reversalId, 'REFUND_PAYOUT_REVERSAL_INSERT_FAILED', $this->refundPayoutModel);
|
|
|
|
$netPaidCents = $this->refundEligibilityService->getNetCompletedCashOutCentsForRefund($refundId);
|
|
$approvedCents = isset($refund['approved_amount_cents']) && $refund['approved_amount_cents'] !== null
|
|
? (int)$refund['approved_amount_cents']
|
|
: (int)round(((float)($refund['refund_amount'] ?? 0)) * 100);
|
|
$newStatus = $netPaidCents <= 0 ? FinancialStatus::REFUND_APPROVED : ($netPaidCents < $approvedCents ? FinancialStatus::REFUND_PARTIALLY_PAID : FinancialStatus::REFUND_PAID);
|
|
|
|
if (!$this->refundModel->update($refundId, [
|
|
'refund_paid_amount' => $netPaidCents / 100,
|
|
'status' => $this->refundStatusForStorage($newStatus),
|
|
'refunded_at' => $netPaidCents > 0 ? ($refund['refunded_at'] ?? utc_now()) : null,
|
|
'updated_at' => utc_now(),
|
|
'updated_by' => session()->get('user_id'),
|
|
'note' => trim((string)($refund['note'] ?? '') . "\nReversal: " . $reason),
|
|
])) {
|
|
throw new FinancialPersistenceException('REFUND_REVERSAL_PROJECTION_UPDATE_FAILED', $this->refundModel->errors());
|
|
}
|
|
|
|
if (!empty($refund['invoice_id'])) {
|
|
$this->invoiceLedgerService->recalculateInvoice((int)$refund['invoice_id']);
|
|
}
|
|
|
|
$this->requireTransactionStatus($db);
|
|
$db->transCommit();
|
|
} catch (\Throwable $e) {
|
|
$db->transRollback();
|
|
return $this->refundFailureResponse('REFUND_PAYOUT_REVERSAL_FAILED', 'The refund payout could not be reversed.', $e, [
|
|
'payout_id' => $payoutId,
|
|
'idempotency_key' => $idempotencyKey,
|
|
]);
|
|
}
|
|
|
|
return $this->response->setJSON(['success' => 'Refund payout reversed successfully.']);
|
|
}
|
|
|
|
private function calculateUnreversedPayoutAmountCents(int $payoutId, int $originalAmountCents): int
|
|
{
|
|
$row = $this->refundPayoutModel->db->table('refund_payouts')
|
|
->select('COALESCE(SUM(amount_cents),0) AS reversed_cents')
|
|
->where('reversed_payout_id', $payoutId)
|
|
->where('payout_type', 'reversal')
|
|
->where('status', 'completed')
|
|
->get()
|
|
->getRowArray();
|
|
|
|
return max(0, $originalAmountCents - (int)($row['reversed_cents'] ?? 0));
|
|
}
|
|
|
|
public function processRefunds()
|
|
{
|
|
return $this->recalculateOverpayments();
|
|
}
|
|
|
|
/** Keep your listing; added extra fields for clarity */
|
|
public function listRefunds()
|
|
{
|
|
// Repair legacy withdrawal placeholders only; overpayment recalculation remains explicit.
|
|
$this->repairPendingWithdrawalRefunds();
|
|
$withdrawalReviews = $this->pendingWithdrawalReviews();
|
|
|
|
// 2) List refunds with joins
|
|
$refunds = $this->refundModel
|
|
->select('refunds.*,
|
|
i.invoice_number,
|
|
u.firstname, u.lastname, u.school_id,
|
|
a.firstname AS approved_by_firstname,
|
|
a.lastname AS approved_by_lastname')
|
|
->join('invoices i', 'refunds.invoice_id = i.id', 'left')
|
|
->join('users u', 'refunds.parent_id = u.id')
|
|
->join('users a', 'refunds.approved_by = a.id', 'left')
|
|
->orderBy('refunds.created_at', 'DESC')
|
|
->findAll();
|
|
|
|
foreach ($refunds as &$r) {
|
|
$r['approved_by_name'] = trim(($r['approved_by_firstname'] ?? '') . ' ' . ($r['approved_by_lastname'] ?? '')) ?: '-';
|
|
$r['available_refundable_credit_cents'] = null;
|
|
$r['available_refundable_credit'] = null;
|
|
$sourceType = (string)($r['source_type'] ?? '');
|
|
$sourceId = (int)($r['source_id'] ?? 0);
|
|
if ($sourceType !== '' && $sourceId > 0) {
|
|
try {
|
|
$eligibility = $this->refundEligibilityService->calculateAvailableCredit(
|
|
(int)$r['parent_id'],
|
|
!empty($r['invoice_id']) ? (int)$r['invoice_id'] : null,
|
|
$sourceType,
|
|
$sourceId,
|
|
(int)$r['id']
|
|
);
|
|
$r['available_refundable_credit_cents'] = $eligibility->availableAmountCents;
|
|
$r['available_refundable_credit'] = $eligibility->availableAmountCents / 100;
|
|
$r['approved_refund_reservation_cents'] = $eligibility->reservedAmountCents;
|
|
} catch (\Throwable $e) {
|
|
log_message('error', 'Refund eligibility projection failed for refund ' . (int)$r['id'] . ': ' . $e->getMessage());
|
|
}
|
|
}
|
|
try {
|
|
$projection = $this->parentLedgerService->getParentProjection(
|
|
(int)$r['parent_id'],
|
|
(string)($r['school_year'] ?? ''),
|
|
!empty($r['semester']) ? (string)$r['semester'] : null
|
|
);
|
|
$r['parent_available_refundable_credit_cents'] = (int)($projection['availableRefundableCreditCents'] ?? 0);
|
|
$r['parent_available_refundable_credit'] = (float)($projection['available_refundable_credit'] ?? 0);
|
|
} catch (\Throwable $e) {
|
|
log_message('error', 'Parent refund projection failed for refund ' . (int)$r['id'] . ': ' . $e->getMessage());
|
|
}
|
|
}
|
|
unset($r);
|
|
|
|
$parentId = !empty($refunds) ? $refunds[0]['parent_id'] : null;
|
|
|
|
return view('refunds/list', [
|
|
'refunds' => $refunds,
|
|
'parentId' => $parentId,
|
|
'withdrawalReviews' => $withdrawalReviews,
|
|
]);
|
|
}
|
|
|
|
/** @return list<array<string,mixed>> */
|
|
private function pendingWithdrawalReviews(): array
|
|
{
|
|
try {
|
|
if (! $this->db->tableExists('withdrawal_financial_calculations')) {
|
|
return [];
|
|
}
|
|
|
|
return $this->db->table('withdrawal_financial_calculations wfc')
|
|
->select('wfc.*,
|
|
i.invoice_number,
|
|
u.firstname AS parent_firstname,
|
|
u.lastname AS parent_lastname,
|
|
s.firstname AS student_firstname,
|
|
s.lastname AS student_lastname')
|
|
->join('invoices i', 'wfc.invoice_id = i.id', 'left')
|
|
->join('users u', 'wfc.parent_id = u.id', 'left')
|
|
->join('students s', 'wfc.student_id = s.id', 'left')
|
|
->whereIn('wfc.status', ['preview', 'requires_review'])
|
|
->where('wfc.posted_at', null)
|
|
->orderBy('wfc.calculated_at', 'DESC')
|
|
->get()
|
|
->getResultArray();
|
|
} catch (\Throwable $e) {
|
|
log_message('error', 'Pending withdrawal review lookup failed: ' . $e->getMessage());
|
|
|
|
return [];
|
|
}
|
|
}
|
|
|
|
private function repairPendingWithdrawalRefunds(): void
|
|
{
|
|
try {
|
|
$rows = $this->refundModel
|
|
->groupStart()
|
|
->where('refund_amount <=', 0)
|
|
->orWhere('source_type IS NULL', null, false)
|
|
->orWhere("(request = 'tuition' AND source_type = 'invoice_overpayment')", null, false)
|
|
->orWhere('source_id IS NULL', null, false)
|
|
->groupEnd()
|
|
->whereIn('status', ['Pending', 'pending', 'requested'])
|
|
->like('reason', 'Withdrawal under review')
|
|
->findAll();
|
|
|
|
foreach ($rows as $refund) {
|
|
$parentId = (int)($refund['parent_id'] ?? 0);
|
|
$schoolYear = (string)($refund['school_year'] ?? '');
|
|
if ($parentId <= 0 || $schoolYear === '') {
|
|
continue;
|
|
}
|
|
|
|
$invoice = $this->invoiceModel
|
|
->where('parent_id', $parentId)
|
|
->where('school_year', $schoolYear)
|
|
->orderBy('created_at', 'DESC')
|
|
->first();
|
|
if (!$invoice) {
|
|
continue;
|
|
}
|
|
|
|
$this->refundModel->update((int)$refund['id'], [
|
|
'invoice_id' => (int)$invoice['id'],
|
|
'currency' => 'USD',
|
|
'request' => 'tuition',
|
|
'source_type' => 'tuition_withdrawal',
|
|
'source_id' => (int)$invoice['id'],
|
|
'reconciliation_status' => 'requires_review',
|
|
'reconciliation_reason' => 'Legacy pending withdrawal refund requires recalculation through the withdrawal financial review workflow.',
|
|
'reconciliation_required_at' => utc_now(),
|
|
'updated_at' => utc_now(),
|
|
]);
|
|
}
|
|
|
|
if ($this->db->tableExists('withdrawal_financial_calculations')) {
|
|
$postedRows = $this->db->table('refunds r')
|
|
->select('r.id, r.refund_amount, r.requested_amount_cents, wfc.posted_at, wfc.posted_by')
|
|
->join('withdrawal_financial_calculations wfc', 'wfc.id = r.withdrawal_calculation_id', 'inner')
|
|
->where('r.source_type', 'tuition_withdrawal')
|
|
->whereIn('r.status', ['Pending', 'pending', 'requested'])
|
|
->where('wfc.status', 'posted')
|
|
->where('wfc.posted_at IS NOT NULL', null, false)
|
|
->get()
|
|
->getResultArray();
|
|
|
|
foreach ($postedRows as $refund) {
|
|
$amountCents = (int)($refund['requested_amount_cents'] ?? 0);
|
|
if ($amountCents <= 0) {
|
|
$amountCents = (int)round(((float)($refund['refund_amount'] ?? 0)) * 100);
|
|
}
|
|
if ($amountCents <= 0) {
|
|
continue;
|
|
}
|
|
|
|
$this->refundModel->update((int)$refund['id'], [
|
|
'status' => $this->refundStatusForStorage(FinancialStatus::REFUND_APPROVED),
|
|
'approved_amount_cents' => $amountCents,
|
|
'approved_at' => $refund['posted_at'] ?? utc_now(),
|
|
'approved_by' => !empty($refund['posted_by']) ? (int)$refund['posted_by'] : null,
|
|
'updated_at' => utc_now(),
|
|
]);
|
|
}
|
|
}
|
|
} catch (\Throwable $e) {
|
|
log_message('error', 'Pending withdrawal refund repair failed: ' . $e->getMessage());
|
|
}
|
|
}
|
|
|
|
/** Manual endpoint to recalculate/sync overpayments and optionally notify newly created entries. */
|
|
public function recalculateOverpayments()
|
|
{
|
|
// Optional targeted invoice_number to handle cross-year cases
|
|
$invoiceNumber = (string)($this->request->getPost('invoice_number') ?? '');
|
|
if ($invoiceNumber !== '') {
|
|
try {
|
|
$inv = $this->invoiceModel->where('invoice_number', $invoiceNumber)->first();
|
|
if ($inv) {
|
|
$pid = (int)$inv['parent_id'];
|
|
$iid = (int)$inv['id'];
|
|
$year = (string)$inv['school_year'];
|
|
|
|
$ledger = $this->invoiceLedgerService->calculateInvoice($iid);
|
|
$over = (float)($ledger['customer_credit'] ?? 0.0);
|
|
if ($over > 0.0001) {
|
|
// If there is ANY open refund for this invoice (any request), update it rather than create
|
|
$openRow = $this->refundModel
|
|
->where('invoice_id', $iid)
|
|
->where('source_type', 'invoice_overpayment')
|
|
->where('source_id', $iid)
|
|
->whereIn('status', $this->refundOpenStatuses())
|
|
->orderBy('id', 'DESC')
|
|
->first();
|
|
if ($openRow) {
|
|
$this->requireWrite($this->refundModel->update(
|
|
(int)$openRow['id'],
|
|
$this->buildRefundRecalculationUpdate($openRow, $over, [
|
|
'request' => 'overpayment',
|
|
'source_type' => 'invoice_overpayment',
|
|
'source_id' => $iid,
|
|
])
|
|
), 'REFUND_RECALC_UPDATE_FAILED', $this->refundModel);
|
|
return redirect()->to(site_url('refunds/list'))->with('success', 'Overpayment updated for invoice ' . esc($invoiceNumber));
|
|
}
|
|
|
|
// Else, if no open rows exist, either update existing overpayment (Paid) — skip creating new
|
|
$existing = $this->refundModel
|
|
->where('invoice_id', $iid)
|
|
->where('source_type', 'invoice_overpayment')
|
|
->where('source_id', $iid)
|
|
->orderBy('id', 'DESC')
|
|
->first();
|
|
if ($existing && FinancialStatus::normalizeRefundStatus($existing['status'] ?? null) !== FinancialStatus::REFUND_PAID) {
|
|
// Open/non-paid overpayment rows remain the active reservation for this source.
|
|
return redirect()->to(site_url('refunds/list'))->with('info', 'Overpayment was already resolved for this invoice.');
|
|
}
|
|
// As a last resort, create a single overpayment row
|
|
$insertId = $this->refundModel->insert([
|
|
'parent_id' => $pid,
|
|
'school_year' => $year,
|
|
'semester' => $inv['semester'] ?? null,
|
|
'invoice_id' => $iid,
|
|
'refund_amount' => $over,
|
|
'requested_amount_cents' => (int) round($over * 100),
|
|
'approved_amount_cents' => null,
|
|
'currency' => 'USD',
|
|
'refund_paid_amount' => 0.00,
|
|
'status' => $this->refundStatusForStorage(FinancialStatus::REFUND_REQUESTED),
|
|
'request' => 'overpayment',
|
|
'source_type' => 'invoice_overpayment',
|
|
'source_id' => $iid,
|
|
'reason' => 'Auto-detected per-invoice overpayment (manual)',
|
|
'updated_by' => session()->get('user_id'),
|
|
'created_at' => utc_now(),
|
|
'updated_at' => utc_now(),
|
|
]);
|
|
$this->requireWrite($insertId, 'REFUND_RECALC_INSERT_FAILED', $this->refundModel);
|
|
$user = $this->userModel->select('id, email, firstname, lastname')->find($pid) ?: [];
|
|
$eventData = [
|
|
'user_id' => (int)($user['id'] ?? $pid),
|
|
'email' => $user['email'] ?? null,
|
|
'firstname' => $user['firstname'] ?? null,
|
|
'lastname' => $user['lastname'] ?? null,
|
|
'amount' => $over,
|
|
'portalLink'=> base_url('/login'),
|
|
];
|
|
\CodeIgniter\Events\Events::trigger('refundPending', $eventData, []);
|
|
return redirect()->to(site_url('refunds/list'))->with('success', 'Overpayment detected and refund created for invoice ' . esc($invoiceNumber));
|
|
}
|
|
return redirect()->to(site_url('refunds/list'))->with('info', 'No overpayment detected for invoice ' . esc($invoiceNumber));
|
|
}
|
|
return redirect()->to(site_url('refunds/list'))->with('error', 'Invoice not found: ' . esc($invoiceNumber));
|
|
} catch (\Throwable $e) {
|
|
return redirect()->to(site_url('refunds/list'))->with('error', 'Failed to recalc for invoice: ' . $e->getMessage());
|
|
}
|
|
}
|
|
|
|
$res = $this->recalcOverpayments(true);
|
|
$nNew = count($res['created_ids'] ?? []);
|
|
$nUpd = count($res['updated_ids'] ?? []);
|
|
return redirect()->to(site_url('refunds/list'))
|
|
->with('success', "Recalculated overpayments: created {$nNew}, updated {$nUpd}.");
|
|
}
|
|
|
|
/** Decision endpoint (Approved/Rejected) with reason */
|
|
public function updateStatus(?int $routeRefundId = null)
|
|
{
|
|
$refundId = (int)($routeRefundId ?: $this->request->getPost('refund_id'));
|
|
$status = FinancialStatus::normalizeRefundStatus((string)$this->request->getPost('status'));
|
|
$reason = $this->request->getPost('reason');
|
|
|
|
if ($refundId <= 0 || !in_array($status, self::DECISIONS, true)) {
|
|
return $this->response->setJSON(['error' => 'Invalid status update request.']);
|
|
}
|
|
if (empty($reason)) {
|
|
return $this->response->setJSON(['error' => 'Reason is required for approval or rejection.']);
|
|
}
|
|
|
|
$this->db->transBegin();
|
|
|
|
try {
|
|
$refund = $this->db->query('SELECT * FROM refunds WHERE id = ? FOR UPDATE', [$refundId])->getRowArray();
|
|
if (!$refund) {
|
|
throw new \RuntimeException('Refund not found.');
|
|
}
|
|
if (FinancialStatus::normalizeRefundStatus($refund['status'] ?? null) !== FinancialStatus::REFUND_REQUESTED) {
|
|
throw new \RuntimeException('Only pending refunds can be approved or rejected.');
|
|
}
|
|
if (!empty($refund['invoice_id'])) {
|
|
$this->db->query('SELECT id FROM invoices WHERE id = ? FOR UPDATE', [(int) $refund['invoice_id']]);
|
|
}
|
|
|
|
$approvedCents = null;
|
|
if ($status === FinancialStatus::REFUND_APPROVED) {
|
|
$sourceType = (string)($refund['source_type'] ?? '');
|
|
$sourceId = (int)($refund['source_id'] ?? 0);
|
|
if ($sourceType === '' || $sourceId <= 0) {
|
|
throw new \RuntimeException('Refund source is required for approval.');
|
|
}
|
|
$approvedCents = (int)round(((float)($refund['refund_amount'] ?? 0)) * 100);
|
|
$this->lockRefundSourceForUpdate($sourceType, $sourceId, isset($refund['invoice_id']) ? (int)$refund['invoice_id'] : null);
|
|
$eligibility = $this->refundEligibilityService->calculateAvailableCredit(
|
|
(int)$refund['parent_id'],
|
|
isset($refund['invoice_id']) ? (int)$refund['invoice_id'] : null,
|
|
$sourceType,
|
|
$sourceId,
|
|
$refundId
|
|
);
|
|
$this->refundEligibilityService->validateRequestedAmount($eligibility, $approvedCents);
|
|
}
|
|
|
|
$ok = $this->refundModel->update($refundId, [
|
|
'status' => $this->refundStatusForStorage($status),
|
|
'reason' => $reason,
|
|
'approved_amount_cents' => $approvedCents,
|
|
'approved_at' => utc_now(),
|
|
'approved_by' => session()->get('user_id'),
|
|
'updated_at' => utc_now(),
|
|
'updated_by' => session()->get('user_id'),
|
|
]);
|
|
$this->requireWrite($ok, 'REFUND_STATUS_UPDATE_FAILED', $this->refundModel);
|
|
|
|
if (!empty($refund['invoice_id'])) {
|
|
$this->invoiceLedgerService->recalculateInvoice((int) $refund['invoice_id']);
|
|
}
|
|
|
|
$this->requireTransactionStatus($this->db);
|
|
$this->db->transCommit();
|
|
} catch (\Throwable $e) {
|
|
$this->db->transRollback();
|
|
return $this->refundFailureResponse('REFUND_STATUS_UPDATE_FAILED', 'The refund status could not be updated.', $e, [
|
|
'refund_id' => $refundId,
|
|
]);
|
|
}
|
|
|
|
return $this->response->setJSON($ok ? ['success' => 'Refund status updated successfully.']
|
|
: ['error' => 'Failed to update refund status.']);
|
|
}
|
|
|
|
public function serveRefundFile(int $refundId, string $mode = 'download')
|
|
{
|
|
$refund = $this->refundModel->find($refundId);
|
|
if (!$refund || empty($refund['check_file'])) {
|
|
throw PageNotFoundException::forPageNotFound('Refund file not found.');
|
|
}
|
|
|
|
if (!$this->canViewRefund($refund)) {
|
|
return $this->response->setStatusCode(403);
|
|
}
|
|
|
|
$path = $this->financialAttachmentService->resolvePath('checks', (string) $refund['check_file']);
|
|
if ($path === null) {
|
|
throw PageNotFoundException::forPageNotFound('Refund file not found.');
|
|
}
|
|
|
|
if ($mode === 'inline') {
|
|
return $this->response
|
|
->setHeader('Content-Type', $this->financialAttachmentService->detectMime($path))
|
|
->setHeader('Content-Disposition', 'inline; filename="' . basename($path) . '"')
|
|
->setBody(file_get_contents($path));
|
|
}
|
|
|
|
return $this->response->download($path, null);
|
|
}
|
|
|
|
private function buildPayoutFingerprint(
|
|
string $operationType,
|
|
int $refundId,
|
|
int $amountCents,
|
|
string $paymentMethod,
|
|
string $currency,
|
|
?string $externalReference
|
|
): string {
|
|
$payload = [
|
|
'operation_type' => $operationType,
|
|
'refund_id' => $refundId,
|
|
'amount_cents' => $amountCents,
|
|
'payment_method' => $paymentMethod,
|
|
'currency' => strtoupper($currency ?: 'USD'),
|
|
'external_reference' => $externalReference,
|
|
];
|
|
|
|
return hash('sha256', json_encode($payload, JSON_UNESCAPED_SLASHES));
|
|
}
|
|
|
|
private function lockRefundSourceForUpdate(string $sourceType, int $sourceId, ?int $invoiceId): void
|
|
{
|
|
if (in_array($sourceType, ['invoice_overpayment', 'tuition_withdrawal'], true)) {
|
|
$this->db->query('SELECT id FROM invoices WHERE id = ? FOR UPDATE', [$invoiceId ?: $sourceId])->getRowArray();
|
|
} elseif (in_array($sourceType, ['payment_duplicate', 'payment_correction'], true)) {
|
|
$payment = $this->db->query('SELECT * FROM payments WHERE id = ? FOR UPDATE', [$sourceId])->getRowArray();
|
|
if (!$payment) {
|
|
throw new \RuntimeException('Refund payment source not found.');
|
|
}
|
|
$lockedInvoiceId = $invoiceId ?: (int)($payment['invoice_id'] ?? 0);
|
|
if ($lockedInvoiceId > 0) {
|
|
$this->db->query('SELECT id FROM invoices WHERE id = ? FOR UPDATE', [$lockedInvoiceId])->getRowArray();
|
|
}
|
|
if ($this->db->tableExists('payment_corrections')) {
|
|
$this->db->query(
|
|
'SELECT id FROM payment_corrections WHERE payment_id = ? AND status = ? FOR UPDATE',
|
|
[$sourceId, 'approved']
|
|
)->getResultArray();
|
|
}
|
|
} elseif (in_array($sourceType, ['credit_memo', 'administrative_credit'], true)) {
|
|
throw new \RuntimeException('Unsupported refund source type.');
|
|
}
|
|
|
|
$this->db->query(
|
|
"SELECT id FROM refunds
|
|
WHERE source_type = ?
|
|
AND source_id = ?
|
|
AND status IN ('Approved','Partial','approved','partially_paid')
|
|
FOR UPDATE",
|
|
[$sourceType, $sourceId]
|
|
)->getResultArray();
|
|
}
|
|
|
|
private function payoutFingerprintMatches(array $existingPayout, string $operationType, string $requestFingerprint): bool
|
|
{
|
|
$storedOperation = (string)($existingPayout['operation_type'] ?? '');
|
|
$storedFingerprint = (string)($existingPayout['request_fingerprint_hash'] ?? '');
|
|
|
|
if ($storedOperation === '' && $storedFingerprint === '') {
|
|
return false;
|
|
}
|
|
|
|
return $storedOperation === $operationType && hash_equals($storedFingerprint, $requestFingerprint);
|
|
}
|
|
|
|
private function canViewRefund(array $refund): bool
|
|
{
|
|
$roles = array_map('strtolower', (array) (session()->get('roles') ?? []));
|
|
$activeRole = strtolower((string) (session()->get('role') ?? ''));
|
|
if ($activeRole !== '' && !in_array($activeRole, $roles, true)) {
|
|
$roles[] = $activeRole;
|
|
}
|
|
|
|
foreach (['administrator', 'administrative staff', 'principal', 'teacher', 'teacher_assistant', 'head fa', 'head of fa', 'head_of_fa', 'financial_contributor'] as $role) {
|
|
if (in_array($role, $roles, true)) {
|
|
return true;
|
|
}
|
|
}
|
|
|
|
return in_array('parent', $roles, true) && (int) ($refund['parent_id'] ?? 0) === (int) session()->get('user_id');
|
|
}
|
|
}
|