root
347 lines
13 KiB
PHP
347 lines
13 KiB
PHP
<?php
|
|
|
|
namespace App\Libraries;
|
|
|
|
use App\Models\InvoiceModel;
|
|
use App\Models\PaymentModel;
|
|
use App\Models\PaymentCorrectionModel;
|
|
use App\Models\RefundModel;
|
|
use App\Models\RefundPayoutModel;
|
|
|
|
class RefundEligibilityService
|
|
{
|
|
protected InvoiceLedgerService $invoiceLedgerService;
|
|
protected RefundModel $refundModel;
|
|
protected RefundPayoutModel $refundPayoutModel;
|
|
protected PaymentModel $paymentModel;
|
|
protected PaymentCorrectionModel $paymentCorrectionModel;
|
|
protected InvoiceModel $invoiceModel;
|
|
|
|
public function __construct()
|
|
{
|
|
$this->invoiceLedgerService = new InvoiceLedgerService();
|
|
$this->refundModel = new RefundModel();
|
|
$this->refundPayoutModel = new RefundPayoutModel();
|
|
$this->paymentModel = new PaymentModel();
|
|
$this->paymentCorrectionModel = new PaymentCorrectionModel();
|
|
$this->invoiceModel = new InvoiceModel();
|
|
}
|
|
|
|
public function calculateAvailableCredit(
|
|
int $parentId,
|
|
?int $invoiceId,
|
|
string $sourceType,
|
|
int $sourceId,
|
|
?int $excludeRefundId = null
|
|
): RefundEligibilityResult {
|
|
$sourceCreditCents = $this->calculateSourceCreditCents($parentId, $invoiceId, $sourceType, $sourceId);
|
|
$completedPayoutCents = $this->completedPayoutsAffectAvailability($sourceType)
|
|
? $this->calculateCompletedPayoutCents($sourceType, $sourceId, $excludeRefundId)
|
|
: 0;
|
|
$reservedAmountCents = $this->calculateReservedAmountCents($sourceType, $sourceId, $excludeRefundId, $invoiceId);
|
|
$availableAmountCents = max(0, $sourceCreditCents - $completedPayoutCents - $reservedAmountCents);
|
|
|
|
$reasons = [];
|
|
if ($sourceCreditCents <= 0) {
|
|
$reasons[] = 'NO_SOURCE_CREDIT';
|
|
}
|
|
if ($completedPayoutCents > 0) {
|
|
$reasons[] = 'HAS_COMPLETED_PAYOUTS';
|
|
}
|
|
if ($reservedAmountCents > 0) {
|
|
$reasons[] = 'HAS_APPROVED_RESERVATIONS';
|
|
}
|
|
if ($availableAmountCents <= 0) {
|
|
$reasons[] = 'NO_AVAILABLE_CREDIT';
|
|
}
|
|
|
|
return new RefundEligibilityResult(
|
|
$sourceCreditCents,
|
|
$completedPayoutCents,
|
|
$reservedAmountCents,
|
|
$availableAmountCents,
|
|
array_values(array_unique($reasons))
|
|
);
|
|
}
|
|
|
|
public function validateRequestedAmount(RefundEligibilityResult $eligibility, int $requestedAmountCents): void
|
|
{
|
|
if ($requestedAmountCents <= 0) {
|
|
throw new \RuntimeException('Refund amount must be greater than zero.');
|
|
}
|
|
|
|
if ($requestedAmountCents > $eligibility->availableAmountCents) {
|
|
throw new \RuntimeException('Refund amount exceeds available source credit.');
|
|
}
|
|
}
|
|
|
|
public function getCompletedPayoutTotalCentsForRefund(int $refundId): int
|
|
{
|
|
return $this->getNetCompletedCashOutCentsForRefund($refundId);
|
|
}
|
|
|
|
public function getNetCompletedCashOutCentsForRefund(int $refundId): int
|
|
{
|
|
if ($refundId <= 0) {
|
|
return 0;
|
|
}
|
|
|
|
if ($this->refundPayoutsAvailable()) {
|
|
$row = $this->refundPayoutModel->db->query(
|
|
"SELECT
|
|
COALESCE(SUM(CASE
|
|
WHEN payout_type = 'cash_out' AND status = 'completed' THEN amount_cents
|
|
WHEN payout_type = 'reversal' AND status = 'completed' THEN -amount_cents
|
|
ELSE 0
|
|
END), 0) AS total_cents
|
|
FROM refund_payouts
|
|
WHERE refund_id = ?",
|
|
[$refundId]
|
|
)->getRowArray();
|
|
|
|
return max(0, (int)($row['total_cents'] ?? 0));
|
|
}
|
|
|
|
$refund = $this->refundModel->find($refundId);
|
|
if (!$refund) {
|
|
return 0;
|
|
}
|
|
|
|
return (int)round(((float)($refund['refund_paid_amount'] ?? 0)) * 100);
|
|
}
|
|
|
|
public function getUnreversedPayoutAmountCents(int $payoutId): int
|
|
{
|
|
if ($payoutId <= 0 || !$this->refundPayoutsAvailable()) {
|
|
return 0;
|
|
}
|
|
|
|
$row = $this->refundPayoutModel->db->query(
|
|
"SELECT p.amount_cents
|
|
- COALESCE(SUM(CASE
|
|
WHEN r.payout_type = 'reversal' AND r.status = 'completed' THEN r.amount_cents
|
|
ELSE 0
|
|
END), 0) AS unreversed_cents
|
|
FROM refund_payouts p
|
|
LEFT JOIN refund_payouts r ON r.reversed_payout_id = p.id
|
|
WHERE p.id = ?
|
|
GROUP BY p.id, p.amount_cents",
|
|
[$payoutId]
|
|
)->getRowArray();
|
|
|
|
return max(0, (int)($row['unreversed_cents'] ?? 0));
|
|
}
|
|
|
|
public function findPayoutsExceedingApprovedAmounts(): array
|
|
{
|
|
if ($this->refundPayoutsAvailable()) {
|
|
return $this->refundModel->db->query(
|
|
"SELECT r.id AS refund_id,
|
|
COALESCE(r.approved_amount_cents, ROUND(COALESCE(r.refund_amount, 0) * 100)) AS approved_amount_cents,
|
|
COALESCE(SUM(CASE
|
|
WHEN p.payout_type = 'cash_out' AND p.status = 'completed' THEN p.amount_cents
|
|
WHEN p.payout_type = 'reversal' AND p.status = 'completed' THEN -p.amount_cents
|
|
ELSE 0
|
|
END), 0) AS completed_payout_cents
|
|
FROM refunds r
|
|
LEFT JOIN refund_payouts p ON p.refund_id = r.id
|
|
GROUP BY r.id, r.approved_amount_cents, r.refund_amount
|
|
HAVING completed_payout_cents > approved_amount_cents"
|
|
)->getResultArray();
|
|
}
|
|
|
|
return $this->refundModel->db->query(
|
|
"SELECT id AS refund_id,
|
|
COALESCE(approved_amount_cents, ROUND(COALESCE(refund_amount, 0) * 100)) AS approved_amount_cents,
|
|
ROUND(COALESCE(refund_paid_amount, 0) * 100) AS completed_payout_cents
|
|
FROM refunds
|
|
WHERE ROUND(COALESCE(refund_paid_amount, 0) * 100)
|
|
> COALESCE(approved_amount_cents, ROUND(COALESCE(refund_amount, 0) * 100))"
|
|
)->getResultArray();
|
|
}
|
|
|
|
protected function calculateSourceCreditCents(int $parentId, ?int $invoiceId, string $sourceType, int $sourceId): int
|
|
{
|
|
return match ($sourceType) {
|
|
'invoice_overpayment' => $this->invoiceCreditCents($parentId, $invoiceId ?: $sourceId),
|
|
'tuition_withdrawal' => $this->invoiceCreditCents($parentId, $invoiceId ?: $sourceId),
|
|
'payment_duplicate', 'payment_correction' => $this->paymentCreditCents($parentId, $invoiceId, $sourceId),
|
|
'credit_memo', 'administrative_credit' => 0,
|
|
default => 0,
|
|
};
|
|
}
|
|
|
|
protected function completedPayoutsAffectAvailability(string $sourceType): bool
|
|
{
|
|
return ! in_array($sourceType, ['invoice_overpayment', 'tuition_withdrawal'], true);
|
|
}
|
|
|
|
protected function invoicePaidCents(int $parentId, int $invoiceId): int
|
|
{
|
|
if ($invoiceId <= 0) {
|
|
return 0;
|
|
}
|
|
|
|
$invoice = $this->invoiceModel->find($invoiceId);
|
|
if (!$invoice || (int)($invoice['parent_id'] ?? 0) !== $parentId) {
|
|
return 0;
|
|
}
|
|
|
|
$ledger = $this->invoiceLedgerService->calculateInvoice($invoiceId);
|
|
|
|
return max(0, (int)($ledger['paidCents'] ?? 0));
|
|
}
|
|
|
|
protected function invoiceCreditCents(int $parentId, int $invoiceId): int
|
|
{
|
|
if ($invoiceId <= 0) {
|
|
return 0;
|
|
}
|
|
|
|
$invoice = $this->invoiceModel->find($invoiceId);
|
|
if (!$invoice || (int)($invoice['parent_id'] ?? 0) !== $parentId) {
|
|
return 0;
|
|
}
|
|
|
|
$ledger = $this->invoiceLedgerService->calculateInvoice($invoiceId);
|
|
|
|
return (int)($ledger['customerCreditCents'] ?? 0);
|
|
}
|
|
|
|
protected function paymentCreditCents(int $parentId, ?int $invoiceId, int $paymentId): int
|
|
{
|
|
if ($paymentId <= 0) {
|
|
return 0;
|
|
}
|
|
|
|
$payment = $this->paymentModel->find($paymentId);
|
|
if (!$payment || (int)($payment['parent_id'] ?? 0) !== $parentId) {
|
|
return 0;
|
|
}
|
|
|
|
if ($invoiceId !== null && $invoiceId > 0 && (int)($payment['invoice_id'] ?? 0) !== $invoiceId) {
|
|
return 0;
|
|
}
|
|
|
|
$status = FinancialStatus::normalizePaymentStatus($payment['status'] ?? null);
|
|
if (!in_array($status, [FinancialStatus::PAYMENT_RECORDED], true)) {
|
|
return 0;
|
|
}
|
|
|
|
if (!$this->paymentCorrectionsAvailable()) {
|
|
return 0;
|
|
}
|
|
|
|
$correction = $this->paymentCorrectionModel
|
|
->where('payment_id', $paymentId)
|
|
->where('invoice_id', (int)($payment['invoice_id'] ?? 0))
|
|
->where('parent_id', $parentId)
|
|
->where('status', 'approved')
|
|
->orderBy('approved_at', 'DESC')
|
|
->orderBy('id', 'DESC')
|
|
->first();
|
|
if (!$correction) {
|
|
return 0;
|
|
}
|
|
|
|
return max(0, (int)($correction['approved_refundable_cents'] ?? 0));
|
|
}
|
|
|
|
protected function paymentCorrectionsAvailable(): bool
|
|
{
|
|
try {
|
|
return $this->paymentCorrectionModel->db->tableExists('payment_corrections');
|
|
} catch (\Throwable $e) {
|
|
return false;
|
|
}
|
|
}
|
|
|
|
protected function calculateCompletedPayoutCents(string $sourceType, int $sourceId, ?int $excludeRefundId): int
|
|
{
|
|
$refundIds = $this->matchingRefundIds($sourceType, $sourceId, $excludeRefundId);
|
|
if ($refundIds === []) {
|
|
return 0;
|
|
}
|
|
|
|
if ($this->refundPayoutsAvailable()) {
|
|
$row = $this->refundPayoutModel->db->table('refund_payouts')
|
|
->select("COALESCE(SUM(CASE
|
|
WHEN payout_type = 'cash_out' AND status = 'completed' THEN amount_cents
|
|
WHEN payout_type = 'reversal' AND status = 'completed' THEN -amount_cents
|
|
ELSE 0
|
|
END),0) AS total_cents", false)
|
|
->whereIn('refund_id', $refundIds)
|
|
->get()
|
|
->getRowArray();
|
|
|
|
return max(0, (int)($row['total_cents'] ?? 0));
|
|
}
|
|
|
|
$row = $this->refundModel
|
|
->select('COALESCE(SUM(refund_paid_amount),0) AS total_paid')
|
|
->whereIn('id', $refundIds)
|
|
->whereIn('status', ['Partial', 'Paid', 'partial', 'paid', 'partially_paid'])
|
|
->first();
|
|
|
|
return (int)round(((float)($row['total_paid'] ?? 0)) * 100);
|
|
}
|
|
|
|
protected function calculateReservedAmountCents(string $sourceType, int $sourceId, ?int $excludeRefundId, ?int $invoiceId = null): int
|
|
{
|
|
$query = $this->refundModel
|
|
->select('id, refund_amount, requested_amount_cents, refund_paid_amount, approved_amount_cents');
|
|
|
|
if (in_array($sourceType, ['invoice_overpayment', 'tuition_withdrawal'], true)) {
|
|
$invoiceSourceId = $invoiceId !== null && $invoiceId > 0 ? $invoiceId : $sourceId;
|
|
$query->where('invoice_id', $invoiceSourceId)
|
|
->whereIn('source_type', ['invoice_overpayment', 'tuition_withdrawal']);
|
|
} else {
|
|
$query->where('source_type', $sourceType)
|
|
->where('source_id', $sourceId);
|
|
}
|
|
|
|
$query->whereIn('status', ['Pending', 'requested', 'Approved', 'Partial', 'pending', 'approved', 'partially_paid']);
|
|
|
|
if ($excludeRefundId !== null && $excludeRefundId > 0) {
|
|
$query->where('id !=', $excludeRefundId);
|
|
}
|
|
|
|
$reserved = 0;
|
|
foreach ($query->findAll() as $refund) {
|
|
$approved = isset($refund['approved_amount_cents']) && $refund['approved_amount_cents'] !== null
|
|
? (int)$refund['approved_amount_cents']
|
|
: ((int)($refund['requested_amount_cents'] ?? 0) ?: (int)round(((float)($refund['refund_amount'] ?? 0)) * 100));
|
|
$paid = $this->getCompletedPayoutTotalCentsForRefund((int)($refund['id'] ?? 0));
|
|
$reserved += max(0, $approved - $paid);
|
|
}
|
|
|
|
return $reserved;
|
|
}
|
|
|
|
protected function matchingRefundIds(string $sourceType, int $sourceId, ?int $excludeRefundId): array
|
|
{
|
|
$query = $this->refundModel
|
|
->select('id')
|
|
->where('source_type', $sourceType)
|
|
->where('source_id', $sourceId);
|
|
|
|
if ($excludeRefundId !== null && $excludeRefundId > 0) {
|
|
$query->where('id !=', $excludeRefundId);
|
|
}
|
|
|
|
return array_values(array_filter(array_map(
|
|
static fn(array $row): int => (int)($row['id'] ?? 0),
|
|
$query->findAll()
|
|
)));
|
|
}
|
|
|
|
protected function refundPayoutsAvailable(): bool
|
|
{
|
|
try {
|
|
return $this->refundPayoutModel->db->tableExists('refund_payouts');
|
|
} catch (\Throwable $e) {
|
|
return false;
|
|
}
|
|
}
|
|
}
|