root
708 lines
27 KiB
PHP
708 lines
27 KiB
PHP
<?php
|
|
|
|
namespace App\Libraries;
|
|
|
|
use App\Interfaces\TuitionCalculatorInterface;
|
|
use App\Libraries\Tuition\NewTuitionCalculatorService;
|
|
use App\Libraries\Tuition\OldTuitionCalculatorService;
|
|
use App\Models\AdditionalChargeModel;
|
|
use App\Models\ClassSectionModel;
|
|
use App\Models\ConfigurationModel;
|
|
use App\Models\DiscountUsageModel;
|
|
use App\Models\EnrollmentModel;
|
|
use App\Models\EventChargesModel;
|
|
use App\Models\InvoiceEventModel;
|
|
use App\Models\InvoiceStudentListModel;
|
|
use App\Models\InvoiceModel;
|
|
use App\Models\PaymentModel;
|
|
use App\Models\RefundModel;
|
|
use App\Models\RefundPayoutModel;
|
|
use App\Models\StudentClassModel;
|
|
use App\Models\StudentModel;
|
|
|
|
class InvoiceLedgerService
|
|
{
|
|
protected InvoiceModel $invoiceModel;
|
|
protected PaymentModel $paymentModel;
|
|
protected RefundModel $refundModel;
|
|
protected ?RefundPayoutModel $refundPayoutModel = null;
|
|
protected DiscountUsageModel $discountUsageModel;
|
|
protected AdditionalChargeModel $additionalChargeModel;
|
|
protected ConfigurationModel $configurationModel;
|
|
protected EnrollmentModel $enrollmentModel;
|
|
protected StudentClassModel $studentClassModel;
|
|
protected ClassSectionModel $classSectionModel;
|
|
protected EventChargesModel $eventChargesModel;
|
|
protected InvoiceEventModel $invoiceEventModel;
|
|
protected InvoiceStudentListModel $invoiceStudentListModel;
|
|
protected StudentModel $studentModel;
|
|
protected TuitionCalculatorInterface $oldCalculator;
|
|
protected TuitionCalculatorInterface $newCalculator;
|
|
|
|
public function __construct()
|
|
{
|
|
$this->invoiceModel = new InvoiceModel();
|
|
$this->paymentModel = new PaymentModel();
|
|
$this->refundModel = new RefundModel();
|
|
$this->refundPayoutModel = new RefundPayoutModel();
|
|
$this->discountUsageModel = new DiscountUsageModel();
|
|
$this->additionalChargeModel = new AdditionalChargeModel();
|
|
$this->configurationModel = new ConfigurationModel();
|
|
$this->enrollmentModel = new EnrollmentModel();
|
|
$this->studentClassModel = new StudentClassModel();
|
|
$this->classSectionModel = new ClassSectionModel();
|
|
$this->eventChargesModel = new EventChargesModel();
|
|
$this->invoiceEventModel = new InvoiceEventModel();
|
|
$this->invoiceStudentListModel = new InvoiceStudentListModel();
|
|
$this->studentModel = new StudentModel();
|
|
$this->oldCalculator = new OldTuitionCalculatorService();
|
|
$this->newCalculator = new NewTuitionCalculatorService();
|
|
}
|
|
|
|
public function calculateInvoice(int $invoiceId): array
|
|
{
|
|
$invoice = $this->loadInvoice($invoiceId);
|
|
if ($invoice === null) {
|
|
throw new \RuntimeException('Invoice not found.');
|
|
}
|
|
|
|
$frozenTotals = $this->calculateFrozenLineTotals($invoiceId);
|
|
if ($frozenTotals !== null) {
|
|
$tuitionTotal = $this->fromCentsNumber($frozenTotals['tuition_cents']);
|
|
$eventTotal = $this->fromCentsNumber($frozenTotals['event_cents']);
|
|
$additionalTotal = $this->fromCentsNumber($frozenTotals['additional_cents']);
|
|
$frozenTotalCents = $frozenTotals['total_cents'];
|
|
} else {
|
|
$tuitionTotal = $this->calculateTuitionTotal($invoice);
|
|
$eventTotal = $this->calculateEventTotal($invoice);
|
|
$additionalTotal = $this->calculateAdditionalCharges($invoiceId);
|
|
$frozenTotalCents = null;
|
|
}
|
|
$discountRawTotal = $this->calculateDiscounts($invoiceId);
|
|
$paidTotal = $this->calculateValidPayments($invoiceId);
|
|
$refundPaidTotal = $this->calculatePaidRefunds($invoiceId);
|
|
|
|
$tuitionCents = $this->toCents($tuitionTotal);
|
|
$eventCents = $this->toCents($eventTotal);
|
|
$additionalCents = $this->toCents($additionalTotal);
|
|
$discountRawCents = $this->toCents($discountRawTotal);
|
|
$paidCents = $this->toCents($paidTotal);
|
|
$refundPaidCents = $this->toCents($refundPaidTotal);
|
|
|
|
if ($frozenTotalCents !== null) {
|
|
$totalAmountCents = $frozenTotalCents;
|
|
$discountBaseCents = max(0, (int) ($frozenTotals['discount_eligible_base_cents'] ?? ($tuitionCents + $additionalCents)));
|
|
$discountCents = $discountBaseCents > 0 ? min($discountRawCents, $discountBaseCents) : 0;
|
|
} elseif ($this->isCarryForwardInvoice($invoice)) {
|
|
$totalAmountCents = $this->toCents((float) ($invoice['total_amount'] ?? 0));
|
|
$discountBaseCents = 0;
|
|
$discountCents = 0;
|
|
} else {
|
|
$discountBaseCents = max(0, $tuitionCents + $additionalCents);
|
|
$discountCents = min($discountRawCents, $discountBaseCents);
|
|
$totalAmountCents = $tuitionCents + $eventCents + $additionalCents;
|
|
}
|
|
|
|
$netChargeCents = $totalAmountCents - $discountCents;
|
|
$rawBalanceCents = $netChargeCents - $paidCents + $refundPaidCents;
|
|
$balanceCents = max(0, $rawBalanceCents);
|
|
$customerCreditCents = max(0, $paidCents - $refundPaidCents - $netChargeCents);
|
|
|
|
if ($balanceCents === 0) {
|
|
$status = FinancialStatus::INVOICE_PAID;
|
|
} elseif ($paidCents > 0 || $discountCents > 0 || $refundPaidCents > 0) {
|
|
$status = FinancialStatus::INVOICE_PARTIALLY_PAID;
|
|
} else {
|
|
$status = FinancialStatus::INVOICE_UNPAID;
|
|
}
|
|
|
|
return [
|
|
'invoice_id' => $invoiceId,
|
|
'gross_charge_cents' => $totalAmountCents,
|
|
'discount_eligible_base_cents' => $discountBaseCents,
|
|
'requested_discount_cents' => $discountRawCents,
|
|
'applied_discount_cents' => $discountCents,
|
|
'net_charge_cents' => $netChargeCents,
|
|
'totalAmountCents' => $totalAmountCents,
|
|
'discountCents' => $discountCents,
|
|
'paidCents' => $paidCents,
|
|
'completedRefundCents' => $refundPaidCents,
|
|
'rawBalanceCents' => $rawBalanceCents,
|
|
'balanceDueCents' => $balanceCents,
|
|
'customerCreditCents' => $customerCreditCents,
|
|
'tuition_total' => $this->fromCents($tuitionCents),
|
|
'event_total' => $this->fromCents($eventCents),
|
|
'additional_total' => $this->fromCents($additionalCents),
|
|
'discount_total' => $this->fromCents($discountCents),
|
|
'discount_raw_total' => $this->fromCents($discountRawCents),
|
|
'paid_amount' => $this->fromCents($paidCents),
|
|
'refund_paid_total' => $this->fromCents($refundPaidCents),
|
|
'total_amount' => $this->fromCents($totalAmountCents),
|
|
'customer_credit' => $this->fromCents($customerCreditCents),
|
|
'balance' => $this->fromCents($balanceCents),
|
|
'status' => $status,
|
|
'has_discount' => $discountCents > 0 ? 1 : 0,
|
|
];
|
|
}
|
|
|
|
public function storedInvoiceLedger(int $invoiceId): array
|
|
{
|
|
$invoice = $this->loadInvoice($invoiceId);
|
|
if ($invoice === null) {
|
|
throw new \RuntimeException('Invoice not found.');
|
|
}
|
|
|
|
$totalAmountCents = $this->toCents((float) ($invoice['total_amount'] ?? 0));
|
|
$paidCents = $this->toCents((float) ($invoice['paid_amount'] ?? $this->calculateValidPayments($invoiceId)));
|
|
$balanceCents = $this->toCents((float) ($invoice['balance'] ?? 0));
|
|
$refundPaidCents = $this->toCents($this->calculatePaidRefunds($invoiceId));
|
|
|
|
$discountCents = 0;
|
|
if ($this->invoiceModel->db->fieldExists('discount', $this->invoiceModel->table)) {
|
|
$discountCents = $this->toCents((float) ($invoice['discount'] ?? 0));
|
|
}
|
|
if ($discountCents === 0) {
|
|
$discountCents = $this->toCents($this->calculateDiscounts($invoiceId));
|
|
}
|
|
|
|
$netChargeCents = max(0, $totalAmountCents - $discountCents);
|
|
$rawBalanceCents = $balanceCents;
|
|
$customerCreditCents = max(0, -1 * $balanceCents);
|
|
$balanceDueCents = max(0, $balanceCents);
|
|
$status = (string) ($invoice['status'] ?? '');
|
|
if ($status === '') {
|
|
if ($balanceDueCents === 0) {
|
|
$status = FinancialStatus::INVOICE_PAID;
|
|
} elseif ($paidCents > 0 || $discountCents > 0 || $refundPaidCents > 0) {
|
|
$status = FinancialStatus::INVOICE_PARTIALLY_PAID;
|
|
} else {
|
|
$status = FinancialStatus::INVOICE_UNPAID;
|
|
}
|
|
}
|
|
|
|
return [
|
|
'invoice_id' => $invoiceId,
|
|
'gross_charge_cents' => $totalAmountCents,
|
|
'discount_eligible_base_cents' => $totalAmountCents,
|
|
'requested_discount_cents' => $discountCents,
|
|
'applied_discount_cents' => $discountCents,
|
|
'net_charge_cents' => $netChargeCents,
|
|
'totalAmountCents' => $totalAmountCents,
|
|
'discountCents' => $discountCents,
|
|
'paidCents' => $paidCents,
|
|
'completedRefundCents' => $refundPaidCents,
|
|
'rawBalanceCents' => $rawBalanceCents,
|
|
'balanceDueCents' => $balanceDueCents,
|
|
'customerCreditCents' => $customerCreditCents,
|
|
'tuition_total' => '0.00',
|
|
'event_total' => '0.00',
|
|
'additional_total' => '0.00',
|
|
'discount_total' => $this->fromCents($discountCents),
|
|
'discount_raw_total' => $this->fromCents($discountCents),
|
|
'paid_amount' => $this->fromCents($paidCents),
|
|
'refund_paid_total' => $this->fromCents($refundPaidCents),
|
|
'total_amount' => $this->fromCents($totalAmountCents),
|
|
'customer_credit' => $this->fromCents($customerCreditCents),
|
|
'balance' => $this->fromCents($balanceDueCents),
|
|
'status' => $status,
|
|
'has_discount' => $discountCents > 0 ? 1 : (int) ($invoice['has_discount'] ?? 0),
|
|
];
|
|
}
|
|
|
|
public function recalculateInvoice(int $invoiceId): array
|
|
{
|
|
$invoice = $this->loadInvoice($invoiceId);
|
|
if ($invoice === null) {
|
|
throw new \RuntimeException('Invoice not found.');
|
|
}
|
|
|
|
$this->syncInvoiceStudentsList($invoice);
|
|
$calculation = $this->calculateInvoice($invoiceId);
|
|
$payload = [
|
|
'total_amount' => $calculation['total_amount'],
|
|
'paid_amount' => $calculation['paid_amount'],
|
|
'balance' => $calculation['balance'],
|
|
'status' => $calculation['status'],
|
|
'has_discount' => $calculation['has_discount'],
|
|
'updated_at' => utc_now(),
|
|
];
|
|
|
|
if ($this->invoiceModel->db->fieldExists('discount', $this->invoiceModel->table)) {
|
|
$payload['discount'] = $calculation['discount_total'];
|
|
}
|
|
|
|
if (!$this->invoiceModel->update($invoiceId, $payload)) {
|
|
throw new FinancialPersistenceException('INVOICE_LEDGER_UPDATE_FAILED', $this->invoiceModel->errors());
|
|
}
|
|
|
|
return $calculation;
|
|
}
|
|
|
|
public function syncInvoiceStudentsList(array $invoice): void
|
|
{
|
|
if (! $this->invoiceStudentListModel->db->tableExists('invoice_students_list')) {
|
|
return;
|
|
}
|
|
|
|
if ($this->isCarryForwardInvoice($invoice)) {
|
|
return;
|
|
}
|
|
|
|
$invoiceId = (int) ($invoice['id'] ?? 0);
|
|
$parentId = (int) ($invoice['parent_id'] ?? 0);
|
|
$schoolYear = trim((string) ($invoice['school_year'] ?? ''));
|
|
if ($invoiceId <= 0 || $parentId <= 0 || $schoolYear === '') {
|
|
return;
|
|
}
|
|
|
|
$hasTuitionFeeColumn = $this->invoiceStudentListModel->db->fieldExists('tuition_fee', 'invoice_students_list');
|
|
$existingRows = $this->invoiceStudentListModel
|
|
->select('student_id')
|
|
->where('invoice_id', $invoiceId)
|
|
->findAll();
|
|
$existingStudentIds = [];
|
|
foreach ($existingRows as $row) {
|
|
$studentId = (int) ($row['student_id'] ?? 0);
|
|
if ($studentId > 0) {
|
|
$existingStudentIds[$studentId] = true;
|
|
}
|
|
}
|
|
|
|
$eligibleStatuses = ['enrolled', 'payment pending', 'withdrawn', 'refund pending', 'withdraw under review'];
|
|
$enrollments = $this->enrollmentModel
|
|
->where('parent_id', $parentId)
|
|
->where('school_year', $schoolYear)
|
|
->findAll();
|
|
|
|
$snapshotRows = [];
|
|
$studentIds = [];
|
|
foreach ($enrollments as $enrollment) {
|
|
$status = strtolower(trim((string) ($enrollment['enrollment_status'] ?? '')));
|
|
$studentId = (int) ($enrollment['student_id'] ?? 0);
|
|
if ($studentId <= 0 || isset($existingStudentIds[$studentId]) || ! in_array($status, $eligibleStatuses, true)) {
|
|
continue;
|
|
}
|
|
|
|
if (! $this->studentClassModel->hasNonEventAssignment($studentId, $schoolYear)) {
|
|
continue;
|
|
}
|
|
|
|
$snapshotRows[] = [
|
|
'student_id' => $studentId,
|
|
'enrolled' => in_array($status, ['enrolled', 'payment pending'], true) ? 1 : 0,
|
|
];
|
|
$studentIds[] = $studentId;
|
|
$existingStudentIds[$studentId] = true;
|
|
}
|
|
|
|
$studentIds = array_values(array_unique($studentIds));
|
|
if ($studentIds === []) {
|
|
return;
|
|
}
|
|
|
|
$studentRows = $this->studentModel
|
|
->select('id, firstname, lastname, school_id')
|
|
->whereIn('id', $studentIds)
|
|
->findAll();
|
|
$studentsById = [];
|
|
foreach ($studentRows as $studentRow) {
|
|
$studentsById[(int) ($studentRow['id'] ?? 0)] = $studentRow;
|
|
}
|
|
|
|
$now = utc_now();
|
|
foreach ($snapshotRows as $snapshotRow) {
|
|
$studentId = (int) ($snapshotRow['student_id'] ?? 0);
|
|
$student = $studentsById[$studentId] ?? null;
|
|
if ($student === null) {
|
|
continue;
|
|
}
|
|
|
|
$this->invoiceStudentListModel->insert([
|
|
'invoice_id' => $invoiceId,
|
|
'student_id' => $studentId,
|
|
'student_firstname' => (string) ($student['firstname'] ?? ''),
|
|
'student_lastname' => (string) ($student['lastname'] ?? ''),
|
|
'school_id' => (int) ($student['school_id'] ?? 0),
|
|
'enrolled' => (int) ($snapshotRow['enrolled'] ?? 0),
|
|
'school_year' => $schoolYear,
|
|
'created_at' => $now,
|
|
'updated_at' => $now,
|
|
] + ($hasTuitionFeeColumn ? ['tuition_fee' => '0.00'] : []));
|
|
}
|
|
}
|
|
|
|
public function recalculate(int $invoiceId): array
|
|
{
|
|
return $this->recalculateInvoice($invoiceId);
|
|
}
|
|
|
|
/**
|
|
* Refresh frozen tuition invoice lines to match the current enrollment/class assignment state.
|
|
* Event and additional charge lines are left unchanged.
|
|
*/
|
|
public function syncTuitionLines(int $invoiceId): bool
|
|
{
|
|
return false;
|
|
}
|
|
|
|
public function getValidPaymentTotalCents(int $invoiceId): int
|
|
{
|
|
return $this->toCents($this->calculateValidPayments($invoiceId));
|
|
}
|
|
|
|
public function issueInitialInvoiceLines(
|
|
int $invoiceId,
|
|
float $tuitionAmount,
|
|
float $eventAmount,
|
|
array $metadata = []
|
|
): int {
|
|
if ($invoiceId <= 0) {
|
|
throw new \RuntimeException('Invoice ID is required to issue invoice lines.');
|
|
}
|
|
return 0;
|
|
}
|
|
|
|
protected function loadInvoice(int $invoiceId): ?array
|
|
{
|
|
return $this->invoiceModel->find($invoiceId);
|
|
}
|
|
|
|
protected function calculateFrozenLineTotals(int $invoiceId): ?array
|
|
{
|
|
return null;
|
|
}
|
|
|
|
protected function invoiceHasLines(int $invoiceId): bool
|
|
{
|
|
return false;
|
|
}
|
|
|
|
protected function invoiceLinesAvailable(): bool
|
|
{
|
|
return false;
|
|
}
|
|
|
|
protected function isCarryForwardInvoice(array $invoice): bool
|
|
{
|
|
$invoiceNumber = (string) ($invoice['invoice_number'] ?? '');
|
|
if (str_starts_with($invoiceNumber, 'CF-')) {
|
|
return true;
|
|
}
|
|
|
|
if (strcasecmp((string) ($invoice['semester'] ?? ''), 'Opening Balance') === 0) {
|
|
return true;
|
|
}
|
|
|
|
$description = strtolower((string) ($invoice['description'] ?? ''));
|
|
|
|
return str_contains($description, 'carried over')
|
|
|| str_contains($description, 'carry-forward')
|
|
|| str_contains($description, 'carry over')
|
|
|| str_contains($description, 'previous school year');
|
|
}
|
|
|
|
public function invoiceIsCarryForward(array $invoice): bool
|
|
{
|
|
return $this->isCarryForwardInvoice($invoice);
|
|
}
|
|
|
|
public function carryForwardDisplayDescription(array $invoice): string
|
|
{
|
|
$sourceYear = $this->carryForwardSourceSchoolYear($invoice);
|
|
if ($sourceYear !== '') {
|
|
return 'Carry over balance from last year ' . $sourceYear;
|
|
}
|
|
|
|
return 'Carry over balance from last year';
|
|
}
|
|
|
|
/**
|
|
* Issue a single carry-over line on an opening-balance invoice. No student names are used.
|
|
*/
|
|
public function issueCarryForwardInvoiceLine(int $invoiceId, float $amount, string $description): int
|
|
{
|
|
return 0;
|
|
}
|
|
|
|
private function carryForwardSourceSchoolYear(array $invoice): string
|
|
{
|
|
$description = (string) ($invoice['description'] ?? '');
|
|
if (preg_match('/last year\s+(\d{4}-\d{4})/i', $description, $matches) === 1) {
|
|
return $matches[1];
|
|
}
|
|
if (preg_match('/previous school year\s+(\d{4}-\d{4})/i', $description, $matches) === 1) {
|
|
return $matches[1];
|
|
}
|
|
|
|
$invoiceNumber = (string) ($invoice['invoice_number'] ?? '');
|
|
if (preg_match('/^CF-(\d{8})-/i', $invoiceNumber, $matches) === 1) {
|
|
$compact = $matches[1];
|
|
if (strlen($compact) === 8) {
|
|
return substr($compact, 0, 4) . '-' . substr($compact, 4, 4);
|
|
}
|
|
}
|
|
|
|
$targetYear = trim((string) ($invoice['school_year'] ?? ''));
|
|
if (preg_match('/^(\d{4})-(\d{4})$/', $targetYear, $matches) === 1) {
|
|
return ((int) $matches[1] - 1) . '-' . ((int) $matches[2] - 1);
|
|
}
|
|
|
|
return '';
|
|
}
|
|
|
|
protected function calculateTuitionTotal(array $invoice): float
|
|
{
|
|
$parentId = (int) ($invoice['parent_id'] ?? 0);
|
|
$schoolYear = (string) ($invoice['school_year'] ?? '');
|
|
if ($parentId <= 0 || $schoolYear === '') {
|
|
return 0.0;
|
|
}
|
|
|
|
$students = $this->loadTuitionStudents($parentId, $schoolYear);
|
|
$config = $this->getTuitionConfig();
|
|
$calculator = $this->resolveActiveCalculator();
|
|
|
|
return (float) ($calculator->calculateFamilyTuition($students, $config)['total'] ?? 0.0);
|
|
}
|
|
|
|
protected function calculateEventTotal(array $invoice): float
|
|
{
|
|
$invoiceId = (int) ($invoice['id'] ?? 0);
|
|
$invoiceEventRows = $this->invoiceEventModel
|
|
->select('COALESCE(SUM(amount),0) AS total_amount')
|
|
->where('invoice_id', $invoiceId)
|
|
->findAll();
|
|
|
|
if (!empty($invoiceEventRows)) {
|
|
$total = (float) ($invoiceEventRows[0]['total_amount'] ?? 0);
|
|
if ($total > 0) {
|
|
return $total;
|
|
}
|
|
}
|
|
|
|
$rows = $this->eventChargesModel
|
|
->select('COALESCE(SUM(charged),0) AS total_amount')
|
|
->where('parent_id', (int) ($invoice['parent_id'] ?? 0))
|
|
->where('school_year', (string) ($invoice['school_year'] ?? ''))
|
|
->where('semester', (string) ($invoice['semester'] ?? ''))
|
|
->findAll();
|
|
|
|
return (float) ($rows[0]['total_amount'] ?? 0);
|
|
}
|
|
|
|
protected function calculateAdditionalCharges(int $invoiceId): float
|
|
{
|
|
$rows = $this->additionalChargeModel
|
|
->select("COALESCE(SUM(CASE WHEN charge_type = 'deduct' THEN -ABS(amount) ELSE ABS(amount) END),0) AS total_amount", false)
|
|
->where('invoice_id', $invoiceId)
|
|
->where('status', FinancialStatus::ADDITIONAL_CHARGE_APPLIED)
|
|
->findAll();
|
|
|
|
return (float) ($rows[0]['total_amount'] ?? 0);
|
|
}
|
|
|
|
protected function calculateDiscounts(int $invoiceId): float
|
|
{
|
|
if ($this->discountUsageModel->db->fieldExists('applied_discount_cents', $this->discountUsageModel->table)) {
|
|
$row = $this->discountUsageModel
|
|
->select('COALESCE(SUM(applied_discount_cents),0) AS total_cents')
|
|
->where('invoice_id', $invoiceId)
|
|
->first();
|
|
|
|
return ((int) ($row['total_cents'] ?? 0)) / 100;
|
|
}
|
|
|
|
$row = $this->discountUsageModel
|
|
->select('COALESCE(SUM(discount_amount),0) AS total_amount')
|
|
->where('invoice_id', $invoiceId)
|
|
->first();
|
|
|
|
return (float) ($row['total_amount'] ?? 0);
|
|
}
|
|
|
|
protected function calculateValidPayments(int $invoiceId): float
|
|
{
|
|
$query = $this->paymentModel
|
|
->select('COALESCE(SUM(paid_amount),0) AS total_amount')
|
|
->where('invoice_id', $invoiceId);
|
|
|
|
if ($this->paymentModel->db->fieldExists('status', $this->paymentModel->table)) {
|
|
$query->groupStart()
|
|
->whereIn('status', FinancialStatus::VALID_PAYMENT_STATUSES)
|
|
->orWhere('status IS NULL', null, false)
|
|
->groupEnd();
|
|
}
|
|
|
|
if ($this->paymentModel->db->fieldExists('is_void', $this->paymentModel->table)) {
|
|
$query->groupStart()
|
|
->where('is_void', 0)
|
|
->orWhere('is_void IS NULL', null, false)
|
|
->groupEnd();
|
|
}
|
|
|
|
$row = $query->first();
|
|
|
|
return (float) ($row['total_amount'] ?? 0);
|
|
}
|
|
|
|
protected function calculatePaidRefunds(int $invoiceId): float
|
|
{
|
|
if ($this->refundPayoutsAvailable()) {
|
|
$row = $this->refundPayoutModel()
|
|
->select("COALESCE(SUM(CASE
|
|
WHEN refund_payouts.payout_type = 'cash_out' AND refund_payouts.status = 'completed' THEN refund_payouts.amount_cents
|
|
WHEN refund_payouts.payout_type = 'reversal' AND refund_payouts.status = 'completed' THEN -refund_payouts.amount_cents
|
|
ELSE 0
|
|
END),0) AS total_cents", false)
|
|
->join('refunds', 'refunds.id = refund_payouts.refund_id', 'inner')
|
|
->where('refunds.invoice_id', $invoiceId)
|
|
->first();
|
|
|
|
return max(0, (float) ($row['total_cents'] ?? 0)) / 100;
|
|
}
|
|
|
|
$row = $this->refundModel
|
|
->select('COALESCE(SUM(refund_paid_amount),0) AS total_amount')
|
|
->where('invoice_id', $invoiceId)
|
|
->whereIn('status', FinancialStatus::REFUND_REDUCES_INVOICE_STATUSES)
|
|
->first();
|
|
|
|
return (float) ($row['total_amount'] ?? 0);
|
|
}
|
|
|
|
protected function refundPayoutsAvailable(): bool
|
|
{
|
|
try {
|
|
return $this->refundPayoutModel()->db->tableExists('refund_payouts');
|
|
} catch (\Throwable $e) {
|
|
return false;
|
|
}
|
|
}
|
|
|
|
protected function refundPayoutModel(): RefundPayoutModel
|
|
{
|
|
if ($this->refundPayoutModel === null) {
|
|
$this->refundPayoutModel = new RefundPayoutModel();
|
|
}
|
|
|
|
return $this->refundPayoutModel;
|
|
}
|
|
|
|
protected function loadTuitionStudents(int $parentId, string $schoolYear): array
|
|
{
|
|
$enrollments = $this->enrollmentModel
|
|
->where('parent_id', $parentId)
|
|
->where('school_year', $schoolYear)
|
|
->findAll();
|
|
|
|
if (empty($enrollments)) {
|
|
return [];
|
|
}
|
|
|
|
$refundDeadline = (string) ($this->configurationModel->getConfig('refund_deadline') ?? '');
|
|
$includeWithdrawn = !$this->isWithinRefundWindow($refundDeadline);
|
|
$eligibleStatuses = ['enrolled', 'payment pending'];
|
|
|
|
if ($includeWithdrawn) {
|
|
array_push($eligibleStatuses, 'withdrawn', 'refund pending', 'withdraw under review');
|
|
}
|
|
|
|
$students = [];
|
|
foreach ($enrollments as $enrollment) {
|
|
$status = strtolower(trim((string) ($enrollment['enrollment_status'] ?? '')));
|
|
$studentId = (int) ($enrollment['student_id'] ?? 0);
|
|
if ($studentId <= 0 || !in_array($status, $eligibleStatuses, true)) {
|
|
continue;
|
|
}
|
|
|
|
if (!$this->studentClassModel->hasNonEventAssignment($studentId, $schoolYear)) {
|
|
continue;
|
|
}
|
|
|
|
$gradeName = $this->resolveGradeName($studentId, $schoolYear, $enrollment['class_section_id'] ?? null);
|
|
$student = $this->studentModel->find($studentId) ?? [];
|
|
|
|
$students[] = [
|
|
'student_id' => $studentId,
|
|
'student_name' => trim(((string) ($student['firstname'] ?? '')) . ' ' . ((string) ($student['lastname'] ?? ''))),
|
|
'grade_level' => $gradeName,
|
|
];
|
|
}
|
|
|
|
return $students;
|
|
}
|
|
|
|
protected function resolveGradeName(int $studentId, string $schoolYear, $classSectionId = null): string
|
|
{
|
|
$sectionId = $classSectionId;
|
|
if (empty($sectionId)) {
|
|
$row = $this->studentClassModel
|
|
->select('class_section_id')
|
|
->where('student_id', $studentId)
|
|
->where('school_year', $schoolYear)
|
|
->orderBy('updated_at', 'DESC')
|
|
->first();
|
|
$sectionId = $row['class_section_id'] ?? null;
|
|
}
|
|
|
|
if (empty($sectionId)) {
|
|
return 'N/A';
|
|
}
|
|
|
|
$name = $this->classSectionModel->getClassSectionNameBySectionId($sectionId);
|
|
|
|
return is_string($name) && $name !== '' ? strtoupper(trim($name)) : 'N/A';
|
|
}
|
|
|
|
protected function resolveActiveCalculator(): TuitionCalculatorInterface
|
|
{
|
|
$version = strtolower(trim((string) ($this->configurationModel->getConfig('tuition_calculator_version') ?? 'old')));
|
|
|
|
return $version === 'new' ? $this->newCalculator : $this->oldCalculator;
|
|
}
|
|
|
|
protected function getTuitionConfig(): array
|
|
{
|
|
return [
|
|
'grade_fee' => $this->configurationModel->getConfig('grade_fee'),
|
|
'first_student_fee' => $this->configurationModel->getConfig('first_student_fee'),
|
|
'second_student_fee' => $this->configurationModel->getConfig('second_student_fee'),
|
|
'youth_fee' => $this->configurationModel->getConfig('first_student_fee'),
|
|
'new_tuition_full_amount' => $this->configurationModel->getConfig('new_tuition_full_amount'),
|
|
'new_tuition_second_student_discount' => $this->configurationModel->getConfig('new_tuition_second_student_discount'),
|
|
];
|
|
}
|
|
|
|
protected function isWithinRefundWindow(string $refundDeadline): bool
|
|
{
|
|
if ($refundDeadline === '') {
|
|
return true;
|
|
}
|
|
|
|
try {
|
|
$timeZone = new \DateTimeZone((string) (config('School')->attendance['timezone'] ?? user_timezone()));
|
|
$today = new \DateTimeImmutable('today', $timeZone);
|
|
$deadline = new \DateTimeImmutable($refundDeadline, $timeZone);
|
|
|
|
return $today <= $deadline;
|
|
} catch (\Throwable $e) {
|
|
return true;
|
|
}
|
|
}
|
|
|
|
protected function toCents($amount): int
|
|
{
|
|
return (int) round(((float) $amount) * 100);
|
|
}
|
|
|
|
protected function fromCents(int $cents): string
|
|
{
|
|
return number_format($cents / 100, 2, '.', '');
|
|
}
|
|
|
|
protected function fromCentsNumber(int $cents): float
|
|
{
|
|
return $cents / 100;
|
|
}
|
|
}
|