root
1428 lines
60 KiB
PHP
Executable File
1428 lines
60 KiB
PHP
Executable File
<?php
|
|
|
|
namespace App\Controllers\View;
|
|
use App\Controllers\BaseController;
|
|
use App\Models\DiscountUsageModel;
|
|
use App\Models\InvoiceModel;
|
|
use App\Models\PaymentModel;
|
|
use App\Models\RefundModel;
|
|
use App\Models\ExpenseModel;
|
|
use App\Models\ReimbursementModel;
|
|
use App\Models\UserModel;
|
|
|
|
require_once APPPATH . 'ThirdParty/fpdf/fpdf.php';
|
|
use FPDF;
|
|
|
|
class FinancialController extends BaseController
|
|
{
|
|
private function wantsJson(): bool
|
|
{
|
|
$accept = strtolower((string)($this->request->getHeaderLine('Accept') ?? ''));
|
|
return $this->request->isAJAX() || str_contains($accept, 'application/json');
|
|
}
|
|
|
|
public function financialReport()
|
|
{
|
|
$invoiceModel = new InvoiceModel();
|
|
$paymentModel = new PaymentModel();
|
|
$refundModel = new RefundModel();
|
|
$expenseModel = new ExpenseModel();
|
|
$reimbursementModel = new ReimbursementModel();
|
|
$discountModel = new DiscountUsageModel();
|
|
|
|
$dateFrom = $this->request->getGet('date_from');
|
|
$dateTo = $this->request->getGet('date_to');
|
|
$schoolYear = $this->request->getGet('school_year');
|
|
|
|
// === Apply filters to models (invoice/refund/discount/expense/reimb) ===
|
|
if ($schoolYear) {
|
|
$invoiceModel->where('invoices.school_year', $schoolYear);
|
|
$refundModel->where('school_year', $schoolYear);
|
|
$discountModel->where('school_year', $schoolYear);
|
|
// If these tables have school_year columns:
|
|
$expenseModel->where('school_year', $schoolYear);
|
|
$reimbursementModel->where('school_year', $schoolYear);
|
|
// Payments are filtered separately below to ensure reuse across queries
|
|
}
|
|
|
|
// Apply date filters (support from-only and to-only) using domain-appropriate fields
|
|
$hasFrom = !empty($dateFrom);
|
|
$hasTo = !empty($dateTo);
|
|
if ($hasFrom) {
|
|
// Invoices: prefer issue_date, fallback to created_at
|
|
$invoiceModel->where('DATE(COALESCE(invoices.issue_date, invoices.created_at)) >=', $dateFrom);
|
|
// Refunds: prefer refunded_at, fallback to created_at
|
|
$refundModel->where('DATE(COALESCE(refunded_at, created_at)) >=', $dateFrom);
|
|
// Discounts: prefer used_at, fallback to created_at
|
|
$discountModel->where('DATE(COALESCE(used_at, created_at)) >=', $dateFrom);
|
|
// Expenses & Reimbursements: created_at
|
|
$expenseModel->where('DATE(created_at) >=', $dateFrom);
|
|
$reimbursementModel->where('DATE(created_at) >=', $dateFrom);
|
|
}
|
|
if ($hasTo) {
|
|
$invoiceModel->where('DATE(COALESCE(invoices.issue_date, invoices.created_at)) <=', $dateTo);
|
|
$refundModel->where('DATE(COALESCE(refunded_at, created_at)) <=', $dateTo);
|
|
$discountModel->where('DATE(COALESCE(used_at, created_at)) <=', $dateTo);
|
|
$expenseModel->where('DATE(created_at) <=', $dateTo);
|
|
$reimbursementModel->where('DATE(created_at) <=', $dateTo);
|
|
}
|
|
|
|
// === Invoices ===
|
|
// Business rule: one active invoice per parent per school year.
|
|
// If legacy data has multiple invoices, show only the latest one to avoid reporting older invoices as "Unpaid"
|
|
// after new charges/payments update the current invoice.
|
|
$db = \Config\Database::connect();
|
|
if (!empty($schoolYear)) {
|
|
$latestSub = $db->table('invoices')
|
|
->select('parent_id, MAX(id) AS max_id')
|
|
->where('school_year', $schoolYear)
|
|
->groupBy('parent_id')
|
|
->getCompiledSelect();
|
|
|
|
$invBuilder = $db->table('invoices')
|
|
->select("invoices.*, CONCAT(users.firstname, ' ', users.lastname) AS parent_name", false)
|
|
->join("($latestSub) latest", 'latest.max_id = invoices.id', 'inner', false)
|
|
->join('users', 'users.id = invoices.parent_id', 'left');
|
|
|
|
if ($hasFrom) {
|
|
$invBuilder->where('DATE(COALESCE(invoices.issue_date, invoices.created_at)) >=', $dateFrom);
|
|
}
|
|
if ($hasTo) {
|
|
$invBuilder->where('DATE(COALESCE(invoices.issue_date, invoices.created_at)) <=', $dateTo);
|
|
}
|
|
|
|
$invoices = $invBuilder->get()->getResultArray();
|
|
} else {
|
|
$invoices = $invoiceModel
|
|
->select("invoices.*, CONCAT(users.firstname, ' ', users.lastname) AS parent_name")
|
|
->join('users', 'users.id = invoices.parent_id')
|
|
->findAll();
|
|
}
|
|
|
|
$invoiceIds = array_values(array_unique(array_filter(array_map(static function ($inv) {
|
|
$id = (int)($inv['id'] ?? 0);
|
|
return $id > 0 ? $id : null;
|
|
}, $invoices))));
|
|
|
|
// Helper to build a fresh, filtered PaymentModel each time (so filters don't get lost between queries)
|
|
$buildPaymentModel = function () use ($schoolYear, $dateFrom, $dateTo) {
|
|
$pm = new PaymentModel();
|
|
if (!empty($dateFrom)) {
|
|
$pm->where('DATE(payment_date) >=', $dateFrom);
|
|
}
|
|
if (!empty($dateTo)) {
|
|
$pm->where('DATE(payment_date) <=', $dateTo);
|
|
}
|
|
return $pm;
|
|
};
|
|
// Filter out void/failed/chargeback/etc. payments (align with balance logic elsewhere)
|
|
$applyPaymentFilters = function ($qb) {
|
|
try {
|
|
$db = \Config\Database::connect();
|
|
$hasStatus = $db->fieldExists('status', 'payments');
|
|
$hasVoid = $db->fieldExists('is_void', 'payments');
|
|
$exclude = ['void', 'voided', 'refunded', 'failed', 'chargeback', 'declined', 'reversed', 'canceled', 'cancelled'];
|
|
|
|
if ($hasStatus) {
|
|
$qb->groupStart()
|
|
->whereNotIn('status', $exclude)
|
|
->orWhere('status IS NULL', null, false)
|
|
->groupEnd();
|
|
}
|
|
if ($hasVoid) {
|
|
$qb->groupStart()
|
|
->where('is_void', 0)
|
|
->orWhere('is_void IS NULL', null, false)
|
|
->groupEnd();
|
|
}
|
|
} catch (\Throwable $e) {
|
|
// If schema checks fail, fall back to unfiltered results.
|
|
}
|
|
return $qb;
|
|
};
|
|
|
|
// === Payments aggregated by invoice_id (for the "Paid" column) ===
|
|
$paymentsQuery = $applyPaymentFilters($buildPaymentModel());
|
|
if (!empty($invoiceIds)) {
|
|
$paymentsQuery->whereIn('invoice_id', $invoiceIds);
|
|
} else {
|
|
$paymentsQuery->where('invoice_id', -1);
|
|
}
|
|
$payments = $paymentsQuery
|
|
->select('invoice_id, SUM(paid_amount) AS paid_amount')
|
|
->where('invoice_id IS NOT NULL')
|
|
->groupBy('invoice_id')
|
|
->findAll();
|
|
|
|
// === Per-invoice breakdown by normalized method (cash/credit/check) ===
|
|
// Normalization rules:
|
|
// - 'cash' => 'cash'
|
|
// - 'check','cheque' => 'check'
|
|
// - 'credit','card','credit card','debit','debit card','visa','mastercard' => 'credit'
|
|
// - everything else => 'other' (not shown per-invoice, still included in grand totals)
|
|
$paymentBreakdownRows = $applyPaymentFilters($buildPaymentModel())
|
|
->select("
|
|
invoice_id,
|
|
CASE
|
|
WHEN LOWER(TRIM(payment_method)) = 'cash' THEN 'cash'
|
|
WHEN LOWER(TRIM(payment_method)) IN ('check','cheque') THEN 'check'
|
|
WHEN LOWER(TRIM(payment_method)) IN ('credit','card','credit card','debit','debit card','visa','mastercard') THEN 'credit'
|
|
ELSE 'other'
|
|
END AS method,
|
|
SUM(paid_amount) AS amount
|
|
")
|
|
->where('invoice_id IS NOT NULL')
|
|
->whereIn('invoice_id', $invoiceIds ?: [-1])
|
|
->groupBy('invoice_id, method')
|
|
->findAll();
|
|
|
|
// Map to [invoice_id => ['cash' => x, 'credit' => y, 'check' => z, 'other' => o]]
|
|
$paymentBreakdown = [];
|
|
foreach ($paymentBreakdownRows as $row) {
|
|
$iid = (int)($row['invoice_id'] ?? 0);
|
|
if ($iid <= 0) continue;
|
|
$method = (string)$row['method'];
|
|
$amount = (float)$row['amount'];
|
|
if (!isset($paymentBreakdown[$iid])) $paymentBreakdown[$iid] = [];
|
|
$paymentBreakdown[$iid][$method] = $amount;
|
|
}
|
|
|
|
// === Overall totals by method + grand total ===
|
|
$paymentTotalsRow = $applyPaymentFilters($buildPaymentModel())
|
|
->select("
|
|
SUM(paid_amount) AS total_all,
|
|
SUM(CASE WHEN LOWER(TRIM(payment_method)) = 'cash' THEN paid_amount ELSE 0 END) AS total_cash,
|
|
SUM(CASE WHEN LOWER(TRIM(payment_method)) IN ('check','cheque') THEN paid_amount ELSE 0 END) AS total_check,
|
|
SUM(CASE WHEN LOWER(TRIM(payment_method)) IN ('credit','card','credit card','debit','debit card','visa','mastercard') THEN paid_amount ELSE 0 END) AS total_credit
|
|
")
|
|
->where('invoice_id IS NOT NULL')
|
|
->whereIn('invoice_id', $invoiceIds ?: [-1])
|
|
->first() ?? [];
|
|
|
|
$paymentTotals = [
|
|
'total_all' => (float)($paymentTotalsRow['total_all'] ?? 0),
|
|
'total_cash' => (float)($paymentTotalsRow['total_cash'] ?? 0),
|
|
'total_check' => (float)($paymentTotalsRow['total_check'] ?? 0),
|
|
'total_credit' => (float)($paymentTotalsRow['total_credit'] ?? 0),
|
|
];
|
|
|
|
// === Refunds (grouped) ===
|
|
$refunds = $refundModel
|
|
->select('invoice_id, school_year, SUM(refund_paid_amount) AS total_refunded')
|
|
->where('invoice_id IS NOT NULL')
|
|
->whereIn('status', ['Partial', 'Paid'])
|
|
->groupBy(['invoice_id', 'school_year'])
|
|
->findAll();
|
|
|
|
// === Discounts (grouped) ===
|
|
$discounts = $discountModel
|
|
->select('invoice_id, school_year, SUM(discount_amount) AS discount_amount')
|
|
->where('invoice_id IS NOT NULL')
|
|
->groupBy(['invoice_id', 'school_year'])
|
|
->get()
|
|
->getResultArray();
|
|
|
|
// === Expenses ===
|
|
$expenses = $expenseModel
|
|
->select('category, SUM(amount) AS total_amount')
|
|
->groupBy('category')
|
|
->findAll();
|
|
|
|
// === Reimbursements ===
|
|
$reimbursements = $reimbursementModel
|
|
->select('status, SUM(amount) AS total_amount')
|
|
->groupBy('status')
|
|
->findAll();
|
|
|
|
// --- School year options (for detailed view & JSON) ---
|
|
$schoolYears = [];
|
|
try {
|
|
$db = \Config\Database::connect();
|
|
$rows = $db->table('invoices')
|
|
->select('DISTINCT school_year', false)
|
|
->where('school_year IS NOT NULL', null, false)
|
|
->orderBy('school_year', 'DESC')
|
|
->get()->getResultArray();
|
|
foreach ($rows as $r) {
|
|
$val = (string)($r['school_year'] ?? '');
|
|
if ($val !== '' && !in_array($val, $schoolYears, true)) $schoolYears[] = $val;
|
|
}
|
|
} catch (\Throwable $e) {}
|
|
if (empty($schoolYears) && !empty($schoolYear)) {
|
|
$schoolYears[] = (string)$schoolYear;
|
|
}
|
|
|
|
$eventFeesTotal = $this->getEventFeesTotal($schoolYear, $dateFrom, $dateTo);
|
|
|
|
// JSON API support
|
|
if ($this->wantsJson() || strtolower((string)($this->request->getGet('format') ?? '')) === 'json') {
|
|
return $this->response->setJSON([
|
|
'ok' => true,
|
|
'selectedYear' => $schoolYear,
|
|
'dateFrom' => $dateFrom,
|
|
'dateTo' => $dateTo,
|
|
'schoolYears' => $schoolYears,
|
|
'invoices' => $invoices,
|
|
'payments' => $payments,
|
|
'paymentBreakdown' => $paymentBreakdown,
|
|
'paymentTotals' => $paymentTotals,
|
|
'refunds' => $refunds,
|
|
'expenses' => $expenses,
|
|
'reimbursements' => $reimbursements,
|
|
'discounts' => $discounts,
|
|
'eventFeesTotal' => $eventFeesTotal,
|
|
'csrf_token' => csrf_token(),
|
|
'csrf_hash' => csrf_hash(),
|
|
]);
|
|
}
|
|
|
|
return view('payment/financial_report', [
|
|
'invoices' => $invoices,
|
|
'payments' => $payments, // for Paid column
|
|
'paymentBreakdown' => $paymentBreakdown, // per-invoice Cash/Credit/Check
|
|
'paymentTotals' => $paymentTotals, // summary totals + grand total
|
|
'refunds' => $refunds,
|
|
'expenses' => $expenses,
|
|
'reimbursements' => $reimbursements,
|
|
'discounts' => $discounts,
|
|
'eventFeesTotal' => $eventFeesTotal,
|
|
'selectedYear' => $schoolYear,
|
|
'schoolYears' => $schoolYears,
|
|
'dateFrom' => $dateFrom,
|
|
'dateTo' => $dateTo,
|
|
]);
|
|
}
|
|
|
|
|
|
public function financialReportSummary()
|
|
{
|
|
$dateFrom = $this->request->getGet('date_from');
|
|
$dateTo = $this->request->getGet('date_to');
|
|
$schoolYear = $this->request->getGet('school_year');
|
|
|
|
$data = $this->getFinancialSummary($dateFrom, $dateTo, $schoolYear);
|
|
|
|
// Build school year options from invoices table (fallback to configured year)
|
|
$schoolYears = [];
|
|
try {
|
|
$db = \Config\Database::connect();
|
|
$rows = $db->table('invoices')
|
|
->select('DISTINCT school_year', false)
|
|
->where('school_year IS NOT NULL', null, false)
|
|
->orderBy('school_year', 'DESC')
|
|
->get()->getResultArray();
|
|
foreach ($rows as $r) {
|
|
$val = (string)($r['school_year'] ?? '');
|
|
if ($val !== '' && !in_array($val, $schoolYears, true)) $schoolYears[] = $val;
|
|
}
|
|
} catch (\Throwable $e) {}
|
|
if (empty($schoolYears) && !empty($data['schoolYear'])) {
|
|
$schoolYears[] = (string)$data['schoolYear'];
|
|
}
|
|
$data['schoolYears'] = $schoolYears;
|
|
if ($this->wantsJson() || strtolower((string)($this->request->getGet('format') ?? '')) === 'json') {
|
|
return $this->response->setJSON(['ok' => true] + $data + [
|
|
'csrf_token' => csrf_token(),
|
|
'csrf_hash' => csrf_hash(),
|
|
]);
|
|
}
|
|
return view('payment/financial_report_summary', $data);
|
|
}
|
|
|
|
public function financialReportData()
|
|
{
|
|
$this->response->setHeader('Accept', 'application/json');
|
|
return $this->financialReport();
|
|
}
|
|
|
|
public function financialReportSummaryData()
|
|
{
|
|
$this->response->setHeader('Accept', 'application/json');
|
|
return $this->financialReportSummary();
|
|
}
|
|
|
|
public function downloadCsv()
|
|
{
|
|
|
|
$invoiceModel = new InvoiceModel();
|
|
$refundModel = new RefundModel();
|
|
$discountModel = new DiscountUsageModel();
|
|
$expenseModel = new ExpenseModel();
|
|
$reimbursementModel = new ReimbursementModel();
|
|
|
|
$dateFrom = $this->request->getGet('date_from');
|
|
$dateTo = $this->request->getGet('date_to');
|
|
$schoolYear = $this->request->getGet('school_year');
|
|
|
|
// Invoices with filters (school year + date range on issue_date/created_at)
|
|
$invBuilder = $invoiceModel
|
|
->select("invoices.*, CONCAT(users.firstname, ' ', users.lastname) AS parent_name")
|
|
->join('users', 'users.id = invoices.parent_id');
|
|
if (!empty($schoolYear)) {
|
|
$invBuilder->where('invoices.school_year', $schoolYear);
|
|
}
|
|
if (!empty($dateFrom)) {
|
|
$invBuilder->where('DATE(COALESCE(invoices.issue_date, invoices.created_at)) >=', $dateFrom);
|
|
}
|
|
if (!empty($dateTo)) {
|
|
$invBuilder->where('DATE(COALESCE(invoices.issue_date, invoices.created_at)) <=', $dateTo);
|
|
}
|
|
$invoices = $invBuilder->findAll();
|
|
|
|
$hasFrom = !empty($dateFrom);
|
|
$hasTo = !empty($dateTo);
|
|
|
|
$buildPaymentModel = function () use ($schoolYear, $dateFrom, $dateTo) {
|
|
$pm = new PaymentModel();
|
|
if ($schoolYear) {
|
|
$pm->where('school_year', $schoolYear);
|
|
}
|
|
if (!empty($dateFrom)) {
|
|
$pm->where('DATE(payment_date) >=', $dateFrom);
|
|
}
|
|
if (!empty($dateTo)) {
|
|
$pm->where('DATE(payment_date) <=', $dateTo);
|
|
}
|
|
return $pm;
|
|
};
|
|
$applyPaymentFilters = function ($qb) {
|
|
try {
|
|
$db = \Config\Database::connect();
|
|
$hasStatus = $db->fieldExists('status', 'payments');
|
|
$hasVoid = $db->fieldExists('is_void', 'payments');
|
|
$exclude = ['void', 'voided', 'refunded', 'failed', 'chargeback', 'declined', 'reversed', 'canceled', 'cancelled'];
|
|
if ($hasStatus) {
|
|
$qb->groupStart()
|
|
->whereNotIn('status', $exclude)
|
|
->orWhere('status IS NULL', null, false)
|
|
->groupEnd();
|
|
}
|
|
if ($hasVoid) {
|
|
$qb->groupStart()
|
|
->where('is_void', 0)
|
|
->orWhere('is_void IS NULL', null, false)
|
|
->groupEnd();
|
|
}
|
|
} catch (\Throwable $e) {
|
|
}
|
|
return $qb;
|
|
};
|
|
|
|
$payments = $applyPaymentFilters($buildPaymentModel())
|
|
->select('invoice_id, SUM(paid_amount) AS paid_amount')
|
|
->where('invoice_id IS NOT NULL')
|
|
->groupBy('invoice_id')
|
|
->findAll();
|
|
|
|
$paymentsMap = [];
|
|
foreach ($payments as $payment) {
|
|
$paymentsMap[(int)($payment['invoice_id'] ?? 0)] = (float)($payment['paid_amount'] ?? 0);
|
|
}
|
|
|
|
$paymentBreakdownRows = $applyPaymentFilters($buildPaymentModel())
|
|
->select("
|
|
invoice_id,
|
|
CASE
|
|
WHEN LOWER(TRIM(payment_method)) = 'cash' THEN 'cash'
|
|
WHEN LOWER(TRIM(payment_method)) IN ('check','cheque') THEN 'check'
|
|
WHEN LOWER(TRIM(payment_method)) IN ('credit','card','credit card','debit','debit card','visa','mastercard') THEN 'credit'
|
|
ELSE 'other'
|
|
END AS method,
|
|
SUM(paid_amount) AS amount
|
|
")
|
|
->where('invoice_id IS NOT NULL')
|
|
->groupBy('invoice_id, method')
|
|
->findAll();
|
|
|
|
$paymentBreakdown = [];
|
|
foreach ($paymentBreakdownRows as $row) {
|
|
$iid = (int)($row['invoice_id'] ?? 0);
|
|
if ($iid <= 0) continue;
|
|
$method = (string)$row['method'];
|
|
$amount = (float)($row['amount'] ?? 0);
|
|
if (!isset($paymentBreakdown[$iid])) $paymentBreakdown[$iid] = [];
|
|
$paymentBreakdown[$iid][$method] = $amount;
|
|
}
|
|
|
|
// Expenses summary with filters
|
|
$expBuilder = $expenseModel
|
|
->select('category, SUM(amount) AS total_amount');
|
|
if (!empty($schoolYear)) {
|
|
$expBuilder->where('school_year', $schoolYear);
|
|
}
|
|
if (!empty($dateFrom)) {
|
|
$expBuilder->where('DATE(created_at) >=', $dateFrom);
|
|
}
|
|
if (!empty($dateTo)) {
|
|
$expBuilder->where('DATE(created_at) <=', $dateTo);
|
|
}
|
|
$expenses = $expBuilder->groupBy('category')->findAll();
|
|
|
|
// Reimbursements summary with filters
|
|
$reimbBuilder = $reimbursementModel
|
|
->select('status, SUM(amount) AS total_amount');
|
|
if (!empty($schoolYear)) {
|
|
$reimbBuilder->where('school_year', $schoolYear);
|
|
}
|
|
if (!empty($dateFrom)) {
|
|
$reimbBuilder->where('DATE(created_at) >=', $dateFrom);
|
|
}
|
|
if (!empty($dateTo)) {
|
|
$reimbBuilder->where('DATE(created_at) <=', $dateTo);
|
|
}
|
|
$reimbursements = $reimbBuilder->groupBy('status')->findAll();
|
|
|
|
// Refunds (grouped)
|
|
if (!empty($schoolYear)) {
|
|
$refundModel->where('school_year', $schoolYear);
|
|
}
|
|
if ($hasFrom) {
|
|
$refundModel->where('DATE(COALESCE(refunded_at, created_at)) >=', $dateFrom);
|
|
}
|
|
if ($hasTo) {
|
|
$refundModel->where('DATE(COALESCE(refunded_at, created_at)) <=', $dateTo);
|
|
}
|
|
$refundsData = $refundModel
|
|
->select('invoice_id, SUM(refund_paid_amount) AS total_refunded')
|
|
->where('invoice_id IS NOT NULL')
|
|
->whereIn('status', ['Partial', 'Paid'])
|
|
->groupBy('invoice_id')
|
|
->findAll();
|
|
$refunds = [];
|
|
foreach ($refundsData as $refund) {
|
|
$refunds[$refund['invoice_id']] = $refund['total_refunded'];
|
|
}
|
|
|
|
$discountBuilder = $discountModel;
|
|
if (!empty($schoolYear)) {
|
|
$discountBuilder->where('school_year', $schoolYear);
|
|
}
|
|
if ($hasFrom) {
|
|
$discountBuilder->where('DATE(COALESCE(used_at, created_at)) >=', $dateFrom);
|
|
}
|
|
if ($hasTo) {
|
|
$discountBuilder->where('DATE(COALESCE(used_at, created_at)) <=', $dateTo);
|
|
}
|
|
$discountsData = $discountBuilder
|
|
->select('invoice_id, SUM(discount_amount) AS discount_amount')
|
|
->where('invoice_id IS NOT NULL')
|
|
->groupBy('invoice_id')
|
|
->get()
|
|
->getResultArray();
|
|
|
|
$discounts = [];
|
|
foreach ($discountsData as $disc) {
|
|
$discounts[$disc['invoice_id']] = $disc['discount_amount'];
|
|
}
|
|
|
|
$filename = 'financial_report_' . date('Ymd_His') . '.csv';
|
|
|
|
header('Content-Type: text/csv');
|
|
header('Content-Disposition: attachment; filename="' . $filename . '"');
|
|
|
|
$out = fopen('php://output', 'w');
|
|
|
|
// Invoices section
|
|
fputcsv($out, ['Invoice #', 'Parent', 'School Year', 'Total', 'Paid', 'Cash', 'Credit', 'Check', 'Balance', 'Refund', 'Discount', 'Status']);
|
|
$sumTotal = 0.0;
|
|
$sumPaid = 0.0;
|
|
$sumCash = 0.0;
|
|
$sumCredit = 0.0;
|
|
$sumCheck = 0.0;
|
|
$sumBalance = 0.0;
|
|
$sumRefund = 0.0;
|
|
$sumDiscount = 0.0;
|
|
foreach ($invoices as $inv) {
|
|
$invoiceId = (int)($inv['id'] ?? 0);
|
|
$paid = (float)($paymentsMap[$invoiceId] ?? 0);
|
|
$bd = $paymentBreakdown[$invoiceId] ?? [];
|
|
$cash = (float)($bd['cash'] ?? 0);
|
|
$credit = (float)($bd['credit'] ?? 0);
|
|
$check = (float)($bd['check'] ?? 0);
|
|
$refunded = (float)($refunds[$invoiceId] ?? 0);
|
|
$discount = (float)($discounts[$invoiceId] ?? 0);
|
|
$total = (float)($inv['total_amount'] ?? 0);
|
|
$balance = $total - $paid - $discount - $refunded;
|
|
if ($balance < 0) $balance = 0.0;
|
|
$status = ($balance === 0.0) ? 'Paid' : 'Unpaid';
|
|
|
|
fputcsv($out, [
|
|
$inv['invoice_number'],
|
|
$inv['parent_name'] ?? '',
|
|
$inv['school_year'],
|
|
$total,
|
|
$paid,
|
|
$cash,
|
|
$credit,
|
|
$check,
|
|
$balance,
|
|
$refunded,
|
|
$discount,
|
|
$status,
|
|
]);
|
|
|
|
$sumTotal += $total;
|
|
$sumPaid += $paid;
|
|
$sumCash += $cash;
|
|
$sumCredit += $credit;
|
|
$sumCheck += $check;
|
|
$sumBalance += $balance;
|
|
$sumRefund += $refunded;
|
|
$sumDiscount += $discount;
|
|
}
|
|
fputcsv($out, [
|
|
'TOTALS',
|
|
'',
|
|
'',
|
|
$sumTotal,
|
|
$sumPaid,
|
|
$sumCash,
|
|
$sumCredit,
|
|
$sumCheck,
|
|
$sumBalance,
|
|
$sumRefund,
|
|
$sumDiscount,
|
|
'',
|
|
]);
|
|
|
|
// Expenses section
|
|
fputcsv($out, []); // blank line
|
|
fputcsv($out, ['Expenses Summary']);
|
|
fputcsv($out, ['Category', 'Total Amount']);
|
|
foreach ($expenses as $exp) {
|
|
fputcsv($out, [
|
|
$exp['category'],
|
|
$exp['total_amount']
|
|
]);
|
|
}
|
|
|
|
// Reimbursements section
|
|
fputcsv($out, []); // blank line
|
|
fputcsv($out, ['Reimbursements Summary']);
|
|
fputcsv($out, ['Status', 'Total Amount']);
|
|
foreach ($reimbursements as $r) {
|
|
fputcsv($out, [
|
|
$r['status'],
|
|
$r['total_amount']
|
|
]);
|
|
}
|
|
|
|
fclose($out);
|
|
exit;
|
|
}
|
|
|
|
public function downloadFinancialReport()
|
|
{
|
|
helper('filesystem');
|
|
|
|
$dateFrom = $this->request->getGet('date_from');
|
|
$dateTo = $this->request->getGet('date_to');
|
|
$schoolYear = $this->request->getGet('school_year');
|
|
$data = $this->getFinancialSummary($dateFrom, $dateTo, $schoolYear);
|
|
|
|
// === Generate charts ===
|
|
$this->generateBarChart($data);
|
|
$this->generatePieChart($data);
|
|
|
|
$pdf = new \FPDF();
|
|
$pdf->AddPage();
|
|
$pdf->SetFont('Arial', 'B', 16);
|
|
$pdf->Cell(0, 10, 'Financial Report for School Year: ' . $data['schoolYear'], 0, 1, 'C');
|
|
|
|
$pdf->Ln(10);
|
|
$pdf->SetFont('Arial', 'B', 12);
|
|
$pdf->Cell(130, 10, 'Description', 1);
|
|
$pdf->Cell(60, 10, 'Amount (USD)', 1);
|
|
$pdf->Ln();
|
|
|
|
$rows = [
|
|
'Total Charges' => $data['totalCharges'],
|
|
'Total Extra Charges' => $data['totalExtraCharges'] ?? 0,
|
|
'Total Discounts' => $data['totalDiscounts'],
|
|
'Total Refunds' => $data['totalRefunds'],
|
|
'Total Expenses' => $data['totalExpenses'],
|
|
'Total Reimbursements' => $data['totalReimbursements'],
|
|
'Donation to School (Masjid/Donation reimbursements)' => $data['donationToSchool'] ?? 0,
|
|
'Net Amount (Earned Income)' => $data['netAmount'],
|
|
'Amount Collected (Paid)' => $data['amountCollected'],
|
|
'Amount Unpaid (Outstanding)' => $data['totalUnpaid'],
|
|
];
|
|
|
|
$pdf->SetFont('Arial', '', 11);
|
|
foreach ($rows as $label => $amount) {
|
|
$pdf->Cell(130, 10, $label, 1);
|
|
$pdf->Cell(60, 10, '$' . number_format($amount, 2), 1);
|
|
$pdf->Ln();
|
|
}
|
|
|
|
// === Add Bar Chart ===
|
|
$barChart = WRITEPATH . 'reports/bar_chart.png';
|
|
if (file_exists($barChart)) {
|
|
$pdf->Ln(10);
|
|
$pdf->SetFont('Arial', 'B', 12);
|
|
$pdf->Cell(0, 10, 'Summary Graph', 0, 1);
|
|
$pdf->Image($barChart, null, null, 180);
|
|
}
|
|
|
|
// === Add Pie Chart ===
|
|
$pieChart = WRITEPATH . 'reports/pie_chart.png';
|
|
if (file_exists($pieChart)) {
|
|
$pdf->Ln(10);
|
|
$pdf->SetFont('Arial', 'B', 12);
|
|
$pdf->Cell(0, 10, 'Expense Breakdown', 0, 1);
|
|
$pdf->Image($pieChart, null, null, 120);
|
|
}
|
|
|
|
// Output file
|
|
$pdf->Output('D', 'Financial_Report_' . $data['schoolYear'] . '.pdf');
|
|
exit;
|
|
}
|
|
|
|
private function generateBarChart(array $data)
|
|
{
|
|
$chartData = [
|
|
'type' => 'bar',
|
|
'data' => [
|
|
'labels' => ['Charges', 'Paid', 'Unpaid', 'Discounts', 'Refunds', 'Expenses', 'Reimbursements', 'Net'],
|
|
'datasets' => [[
|
|
'label' => 'Amount (USD)',
|
|
'data' => [
|
|
$data['totalCharges'],
|
|
$data['amountCollected'],
|
|
$data['totalUnpaid'],
|
|
$data['totalDiscounts'],
|
|
$data['totalRefunds'],
|
|
$data['totalExpenses'],
|
|
$data['totalReimbursements'],
|
|
$data['netAmount'],
|
|
],
|
|
'backgroundColor' => 'rgba(54, 162, 235, 0.6)',
|
|
]]
|
|
]
|
|
];
|
|
|
|
$url = 'https://quickchart.io/chart?c=' . urlencode(json_encode($chartData));
|
|
file_put_contents(WRITEPATH . 'reports/bar_chart.png', file_get_contents($url));
|
|
}
|
|
|
|
private function generatePieChart(array $data)
|
|
{
|
|
$chartData = [
|
|
'type' => 'pie',
|
|
'data' => [
|
|
'labels' => ['Expenses', 'Reimbursements'],
|
|
'datasets' => [[
|
|
'data' => [
|
|
$data['totalExpenses'],
|
|
$data['totalReimbursements'],
|
|
],
|
|
'backgroundColor' => ['#e74c3c', '#8e44ad']
|
|
]]
|
|
]
|
|
];
|
|
|
|
$url = 'https://quickchart.io/chart?c=' . urlencode(json_encode($chartData));
|
|
file_put_contents(WRITEPATH . 'reports/pie_chart.png', file_get_contents($url));
|
|
}
|
|
|
|
private function getFinancialSummary(?string $dateFrom = null, ?string $dateTo = null, ?string $schoolYear = null): array
|
|
{
|
|
$invoiceModel = new \App\Models\InvoiceModel();
|
|
$paymentModel = new \App\Models\PaymentModel();
|
|
$refundModel = new \App\Models\RefundModel();
|
|
$expenseModel = new \App\Models\ExpenseModel();
|
|
$reimbursementModel = new \App\Models\ReimbursementModel();
|
|
$discountModel = new \App\Models\DiscountUsageModel();
|
|
$configModel = new \App\Models\ConfigurationModel();
|
|
$additionalModel = new \App\Models\AdditionalChargeModel();
|
|
|
|
// Allow override via parameter; fallback to configured year
|
|
$schoolYear = $schoolYear ?: $configModel->getConfig('school_year');
|
|
$derivedDateFrom = null;
|
|
$derivedDateTo = null;
|
|
if (!empty($schoolYear) && empty($dateFrom) && empty($dateTo)) {
|
|
if (preg_match('/^(\\d{4})[^\\d]?(\\d{4})$/', $schoolYear, $m)) {
|
|
$derivedDateFrom = $m[1] . '-01-01';
|
|
$derivedDateTo = $m[2] . '-12-31';
|
|
}
|
|
}
|
|
|
|
$db = \Config\Database::connect();
|
|
|
|
// === Invoices: Total Charges ===
|
|
$invoiceBuilder = $invoiceModel->where('school_year', $schoolYear);
|
|
$invoiceDateFrom = $dateFrom ?: $derivedDateFrom;
|
|
$invoiceDateTo = $dateTo ?: $derivedDateTo;
|
|
if (!empty($invoiceDateFrom)) {
|
|
$invoiceBuilder->where('DATE(COALESCE(invoices.issue_date, invoices.created_at)) >=', $invoiceDateFrom);
|
|
}
|
|
if (!empty($invoiceDateTo)) {
|
|
$invoiceBuilder->where('DATE(COALESCE(invoices.issue_date, invoices.created_at)) <=', $invoiceDateTo);
|
|
}
|
|
$invoices = $invoiceBuilder->findAll();
|
|
$totalCharges = array_sum(array_column($invoices, 'total_amount'));
|
|
$invoiceIds = array_values(array_unique(array_filter(array_map(static function ($inv) {
|
|
$id = (int)($inv['id'] ?? 0);
|
|
return $id > 0 ? $id : null;
|
|
}, $invoices))));
|
|
|
|
// === Additional Charges ===
|
|
$hasExplicitDates = !empty($dateFrom) || !empty($dateTo);
|
|
$sumExpr = "COALESCE(SUM(ac.amount), 0) AS amount";
|
|
|
|
$acAllBuilder = $db->table('additional_charges ac')
|
|
->select($sumExpr, false)
|
|
->where('ac.school_year', $schoolYear)
|
|
->where('ac.status !=', 'void');
|
|
if ($hasExplicitDates) {
|
|
if (!empty($dateFrom)) $acAllBuilder->where('DATE(ac.due_date) >=', $dateFrom);
|
|
if (!empty($dateTo)) $acAllBuilder->where('DATE(ac.due_date) <=', $dateTo);
|
|
}
|
|
$acAllRow = $acAllBuilder->get()->getRowArray();
|
|
$totalExtraCharges = isset($acAllRow['amount']) ? (float) $acAllRow['amount'] : 0.00;
|
|
|
|
// Extra charges not reflected in invoice totals (unapplied or missing invoice_id)
|
|
$extraByParent = [];
|
|
$extraChargesUnapplied = 0.0;
|
|
$acExtraBuilder = $db->table('additional_charges ac')
|
|
->select('ac.parent_id, ' . $sumExpr, false)
|
|
->where('ac.school_year', $schoolYear)
|
|
->where('ac.status !=', 'void')
|
|
->groupStart()
|
|
->where('ac.invoice_id IS NULL', null, false)
|
|
->orWhere('ac.invoice_id', 0)
|
|
->orWhere('ac.status !=', 'applied')
|
|
->groupEnd()
|
|
->groupBy('ac.parent_id');
|
|
if ($hasExplicitDates) {
|
|
if (!empty($dateFrom)) $acExtraBuilder->where('DATE(ac.due_date) >=', $dateFrom);
|
|
if (!empty($dateTo)) $acExtraBuilder->where('DATE(ac.due_date) <=', $dateTo);
|
|
}
|
|
$acExtraRows = $acExtraBuilder->get()->getResultArray();
|
|
foreach ($acExtraRows as $row) {
|
|
$pid = (int)($row['parent_id'] ?? 0);
|
|
if ($pid <= 0) continue;
|
|
$amt = (float)($row['amount'] ?? 0);
|
|
if (abs($amt) < 0.00001) continue;
|
|
$extraByParent[$pid] = $amt;
|
|
$extraChargesUnapplied += $amt;
|
|
}
|
|
|
|
$totalCharges += $extraChargesUnapplied;
|
|
|
|
// === Payments: Total Paid ===
|
|
$paymentBuilder = $paymentModel->where('school_year', $schoolYear);
|
|
if (!empty($invoiceDateFrom)) {
|
|
$paymentBuilder->where('DATE(payment_date) >=', $invoiceDateFrom);
|
|
}
|
|
if (!empty($invoiceDateTo)) {
|
|
$paymentBuilder->where('DATE(payment_date) <=', $invoiceDateTo);
|
|
}
|
|
$payHasStatus = $db->fieldExists('status', 'payments');
|
|
$payHasVoid = $db->fieldExists('is_void', 'payments');
|
|
if ($payHasStatus) {
|
|
$paymentBuilder->groupStart()
|
|
->whereNotIn('status', ['void','voided','refunded','failed','chargeback','declined','reversed','canceled','cancelled'])
|
|
->orWhere('status IS NULL', null, false)
|
|
->groupEnd();
|
|
}
|
|
if ($payHasVoid) {
|
|
$paymentBuilder->groupStart()
|
|
->where('is_void', 0)
|
|
->orWhere('is_void IS NULL', null, false)
|
|
->groupEnd();
|
|
}
|
|
$paymentResult = $paymentBuilder->selectSum('paid_amount')->get()->getRowArray();
|
|
$totalPaid = isset($paymentResult['paid_amount']) ? (float) $paymentResult['paid_amount'] : 0.00;
|
|
|
|
// === Per-invoice paid/discount/refund totals for outstanding balance ===
|
|
$paidByInvoice = [];
|
|
$discountByInvoice = [];
|
|
$refundByInvoice = [];
|
|
if (!empty($invoiceIds)) {
|
|
$paidRows = $db->table('payments')
|
|
->select('invoice_id, COALESCE(SUM(paid_amount),0) AS total_paid')
|
|
->whereIn('invoice_id', $invoiceIds);
|
|
if (!empty($schoolYear)) {
|
|
$paidRows->where('school_year', $schoolYear);
|
|
}
|
|
if (!empty($invoiceDateFrom)) {
|
|
$paidRows->where('DATE(payment_date) >=', $invoiceDateFrom);
|
|
}
|
|
if (!empty($invoiceDateTo)) {
|
|
$paidRows->where('DATE(payment_date) <=', $invoiceDateTo);
|
|
}
|
|
if ($payHasStatus) {
|
|
$paidRows->groupStart()
|
|
->whereNotIn('status', ['void','voided','refunded','failed','chargeback','declined','reversed','canceled','cancelled'])
|
|
->orWhere('status IS NULL', null, false)
|
|
->groupEnd();
|
|
}
|
|
if ($payHasVoid) {
|
|
$paidRows->groupStart()
|
|
->where('is_void', 0)
|
|
->orWhere('is_void IS NULL', null, false)
|
|
->groupEnd();
|
|
}
|
|
$paidRows = $paidRows->groupBy('invoice_id')->get()->getResultArray();
|
|
foreach ($paidRows as $r) {
|
|
$iid = (int)($r['invoice_id'] ?? 0);
|
|
if ($iid > 0) $paidByInvoice[$iid] = (float)($r['total_paid'] ?? 0);
|
|
}
|
|
|
|
$discRows = $db->table('discount_usages')
|
|
->select('invoice_id, COALESCE(SUM(discount_amount),0) AS total_disc')
|
|
->whereIn('invoice_id', $invoiceIds);
|
|
if (!empty($dateFrom)) {
|
|
$discRows->where('DATE(COALESCE(used_at, created_at)) >=', $dateFrom);
|
|
}
|
|
if (!empty($dateTo)) {
|
|
$discRows->where('DATE(COALESCE(used_at, created_at)) <=', $dateTo);
|
|
}
|
|
$discRows = $discRows->groupBy('invoice_id')->get()->getResultArray();
|
|
foreach ($discRows as $r) {
|
|
$iid = (int)($r['invoice_id'] ?? 0);
|
|
if ($iid > 0) $discountByInvoice[$iid] = (float)($r['total_disc'] ?? 0);
|
|
}
|
|
|
|
$refRows = $db->table('refunds')
|
|
->select('invoice_id, COALESCE(SUM(refund_paid_amount),0) AS total_refund')
|
|
->whereIn('invoice_id', $invoiceIds)
|
|
->whereIn('status', ['Partial', 'Paid']);
|
|
if (!empty($dateFrom)) {
|
|
$refRows->where('DATE(COALESCE(refunded_at, created_at)) >=', $dateFrom);
|
|
}
|
|
if (!empty($dateTo)) {
|
|
$refRows->where('DATE(COALESCE(refunded_at, created_at)) <=', $dateTo);
|
|
}
|
|
$refRows = $refRows->groupBy('invoice_id')->get()->getResultArray();
|
|
foreach ($refRows as $r) {
|
|
$iid = (int)($r['invoice_id'] ?? 0);
|
|
if ($iid > 0) $refundByInvoice[$iid] = (float)($r['total_refund'] ?? 0);
|
|
}
|
|
}
|
|
|
|
// === Expenses ===
|
|
$expenseBuilder = $expenseModel->where('school_year', $schoolYear);
|
|
if (!empty($invoiceDateFrom)) {
|
|
$expenseBuilder->where('DATE(created_at) >=', $invoiceDateFrom);
|
|
}
|
|
if (!empty($invoiceDateTo)) {
|
|
$expenseBuilder->where('DATE(created_at) <=', $invoiceDateTo);
|
|
}
|
|
$expenseResult = $expenseBuilder->selectSum('amount')->get()->getRowArray();
|
|
$totalExpenses = isset($expenseResult['amount']) ? (float) $expenseResult['amount'] : 0.00;
|
|
|
|
// === Reimbursements ===
|
|
// Reimbursements: include rows missing school_year by falling back to expense.school_year
|
|
$normalizeYear = static function (?string $year): string {
|
|
return preg_replace('/[^0-9]/', '', (string) $year);
|
|
};
|
|
$normalizedYear = $normalizeYear($schoolYear);
|
|
|
|
$reimbBuilder = $db->table('reimbursements r')
|
|
->select('SUM(r.amount) AS amount')
|
|
->join('expenses e', 'e.id = r.expense_id', 'left');
|
|
if (!empty($schoolYear)) {
|
|
$reimbBuilder
|
|
->groupStart()
|
|
->groupStart()
|
|
->where('r.school_year', $schoolYear)
|
|
->orWhere(
|
|
"REPLACE(REPLACE(REPLACE(REPLACE(r.school_year, ' ', ''), '-', ''), '/', ''), '\\\\', '')",
|
|
$normalizedYear
|
|
)
|
|
->groupEnd()
|
|
->orGroupStart()
|
|
->where('r.school_year IS NULL', null, false)
|
|
->groupStart()
|
|
->where('e.school_year', $schoolYear)
|
|
->orWhere(
|
|
"REPLACE(REPLACE(REPLACE(REPLACE(e.school_year, ' ', ''), '-', ''), '/', ''), '\\\\', '')",
|
|
$normalizedYear
|
|
)
|
|
->groupEnd()
|
|
->groupEnd()
|
|
->groupEnd();
|
|
}
|
|
if (!empty($invoiceDateFrom)) {
|
|
$reimbBuilder->where('DATE(COALESCE(r.created_at, e.created_at)) >=', $invoiceDateFrom);
|
|
}
|
|
if (!empty($invoiceDateTo)) {
|
|
$reimbBuilder->where('DATE(COALESCE(r.created_at, e.created_at)) <=', $invoiceDateTo);
|
|
}
|
|
$reimbursementResult = $reimbBuilder->get()->getRowArray();
|
|
$totalReimbursements = isset($reimbursementResult['amount']) ? (float) $reimbursementResult['amount'] : 0.00;
|
|
|
|
// Fallback: include closed batch items that never created a reimbursement record
|
|
$batchFallback = $db->table('reimbursement_batch_items bi')
|
|
->select('SUM(e.amount) AS amount')
|
|
->join('reimbursement_batches b', 'b.id = bi.batch_id', 'inner')
|
|
->join('expenses e', 'e.id = bi.expense_id', 'inner')
|
|
->join('reimbursements r', 'r.expense_id = e.id', 'left')
|
|
->where('b.status', 'closed')
|
|
->where('bi.unassigned_at IS NULL', null, false)
|
|
->where('r.id IS NULL');
|
|
if (!empty($schoolYear)) {
|
|
$batchFallback
|
|
->groupStart()
|
|
->where('e.school_year', $schoolYear)
|
|
->orWhere(
|
|
"REPLACE(REPLACE(REPLACE(REPLACE(e.school_year, ' ', ''), '-', ''), '/', ''), '\\\\', '')",
|
|
$normalizedYear
|
|
)
|
|
->groupEnd();
|
|
}
|
|
if (!empty($invoiceDateFrom)) {
|
|
$batchFallback->where('DATE(COALESCE(e.created_at, b.closed_at)) >=', $invoiceDateFrom);
|
|
}
|
|
if (!empty($invoiceDateTo)) {
|
|
$batchFallback->where('DATE(COALESCE(e.created_at, b.closed_at)) <=', $invoiceDateTo);
|
|
}
|
|
$batchFallbackRow = $batchFallback->get()->getRowArray();
|
|
$fallbackAmount = isset($batchFallbackRow['amount']) ? (float) $batchFallbackRow['amount'] : 0.00;
|
|
$totalReimbursements += $fallbackAmount;
|
|
|
|
// Donations line: donations captured as expenses (category=Donation) plus legacy Masjid/Donation reimbursements.
|
|
$donationExpense = 0.0;
|
|
$donationExpenseBuilder = (new ExpenseModel())
|
|
->where('school_year', $schoolYear)
|
|
->where('category', 'Donation');
|
|
if (!empty($invoiceDateFrom)) {
|
|
$donationExpenseBuilder->where('DATE(created_at) >=', $invoiceDateFrom);
|
|
}
|
|
if (!empty($invoiceDateTo)) {
|
|
$donationExpenseBuilder->where('DATE(created_at) <=', $invoiceDateTo);
|
|
}
|
|
$donationExpenseRow = $donationExpenseBuilder->selectSum('amount')->get()->getRowArray();
|
|
$donationExpense = isset($donationExpenseRow['amount']) ? (float) $donationExpenseRow['amount'] : 0.00;
|
|
|
|
$donationReimb = 0.0;
|
|
$specialRecipientIds = array_map('intval', array_keys(ReimbursementController::SPECIAL_RECIPIENTS));
|
|
if (!empty($specialRecipientIds)) {
|
|
$donationBuilder = $db->table('reimbursements r')
|
|
->select('SUM(r.amount) AS amount')
|
|
->join('expenses e', 'e.id = r.expense_id', 'left')
|
|
->whereIn('r.reimbursed_to', $specialRecipientIds);
|
|
if (!empty($schoolYear)) {
|
|
$donationBuilder
|
|
->groupStart()
|
|
->groupStart()
|
|
->where('r.school_year', $schoolYear)
|
|
->orWhere(
|
|
"REPLACE(REPLACE(REPLACE(REPLACE(r.school_year, ' ', ''), '-', ''), '/', ''), '\\\\', '')",
|
|
$normalizedYear
|
|
)
|
|
->groupEnd()
|
|
->orGroupStart()
|
|
->where('r.school_year IS NULL', null, false)
|
|
->groupStart()
|
|
->where('e.school_year', $schoolYear)
|
|
->orWhere(
|
|
"REPLACE(REPLACE(REPLACE(REPLACE(e.school_year, ' ', ''), '-', ''), '/', ''), '\\\\', '')",
|
|
$normalizedYear
|
|
)
|
|
->groupEnd()
|
|
->groupEnd()
|
|
->groupEnd();
|
|
}
|
|
if (!empty($invoiceDateFrom)) {
|
|
$donationBuilder->where('DATE(COALESCE(r.created_at, e.created_at)) >=', $invoiceDateFrom);
|
|
}
|
|
if (!empty($invoiceDateTo)) {
|
|
$donationBuilder->where('DATE(COALESCE(r.created_at, e.created_at)) <=', $invoiceDateTo);
|
|
}
|
|
$donationRow = $donationBuilder->get()->getRowArray();
|
|
$donationReimb = isset($donationRow['amount']) ? (float) $donationRow['amount'] : 0.00;
|
|
}
|
|
|
|
$donationToSchool = $donationExpense + $donationReimb;
|
|
$totalReimbursements = max(0.0, $totalReimbursements - $donationReimb);
|
|
|
|
// === Refunds ===
|
|
$refundBuilder = $refundModel
|
|
->where('school_year', $schoolYear)
|
|
->whereIn('status', ['Partial', 'Paid'])
|
|
->where('refund_paid_amount IS NOT NULL');
|
|
|
|
if (!empty($dateFrom)) {
|
|
$refundBuilder->where('DATE(COALESCE(refunded_at, created_at)) >=', $dateFrom);
|
|
}
|
|
if (!empty($dateTo)) {
|
|
$refundBuilder->where('DATE(COALESCE(refunded_at, created_at)) <=', $dateTo);
|
|
}
|
|
|
|
$refundResult = $refundBuilder
|
|
->selectSum('refund_paid_amount')
|
|
->get()
|
|
->getRowArray();
|
|
|
|
$totalRefunds = isset($refundResult['refund_paid_amount']) ? (float) $refundResult['refund_paid_amount'] : 0.00;
|
|
|
|
// === Discounts ===
|
|
$discountBuilder = $discountModel
|
|
->join('invoices', 'invoices.id = discount_usages.invoice_id')
|
|
->where('invoices.school_year', $schoolYear);
|
|
if (!empty($dateFrom)) {
|
|
$discountBuilder->where('DATE(COALESCE(discount_usages.used_at, discount_usages.created_at)) >=', $dateFrom);
|
|
}
|
|
if (!empty($dateTo)) {
|
|
$discountBuilder->where('DATE(COALESCE(discount_usages.used_at, discount_usages.created_at)) <=', $dateTo);
|
|
}
|
|
$discountResult = $discountBuilder->selectSum('discount_amount')->get()->getRowArray();
|
|
$totalDiscounts = isset($discountResult['discount_amount']) ? (float) $discountResult['discount_amount'] : 0.00;
|
|
|
|
// === Net & Unpaid ===
|
|
// Sum positive per-parent balances (per-invoice balance + unapplied extra charges)
|
|
$parentBalances = [];
|
|
foreach ($invoices as $inv) {
|
|
$iid = (int)($inv['id'] ?? 0);
|
|
$pid = (int)($inv['parent_id'] ?? 0);
|
|
if ($iid <= 0 || $pid <= 0) continue;
|
|
$total = (float)($inv['total_amount'] ?? 0);
|
|
$paid = (float)($paidByInvoice[$iid] ?? 0);
|
|
$disc = (float)($discountByInvoice[$iid] ?? 0);
|
|
$ref = (float)($refundByInvoice[$iid] ?? 0);
|
|
$bal = max(0.0, round($total - $disc - $paid - $ref, 2));
|
|
if (!isset($parentBalances[$pid])) $parentBalances[$pid] = 0.0;
|
|
$parentBalances[$pid] += $bal;
|
|
}
|
|
foreach ($extraByParent as $pid => $extra) {
|
|
if (!isset($parentBalances[$pid])) $parentBalances[$pid] = 0.0;
|
|
$parentBalances[$pid] += (float)$extra;
|
|
}
|
|
$totalUnpaid = 0.0;
|
|
foreach ($parentBalances as $bal) {
|
|
if ($bal > 0.00001) $totalUnpaid += $bal;
|
|
}
|
|
$amountCollected = $totalPaid;
|
|
$netAmount = ($totalCharges - $totalDiscounts - $totalRefunds);
|
|
|
|
$totalEventFees = $this->getEventFeesTotal($schoolYear, $invoiceDateFrom, $invoiceDateTo);
|
|
return [
|
|
'schoolYear' => $schoolYear,
|
|
'dateFrom' => $dateFrom,
|
|
'dateTo' => $dateTo,
|
|
'totalCharges' => $totalCharges,
|
|
'totalExtraCharges' => $totalExtraCharges,
|
|
'totalDiscounts' => $totalDiscounts,
|
|
'totalRefunds' => $totalRefunds,
|
|
'totalExpenses' => $totalExpenses,
|
|
'totalReimbursements' => $totalReimbursements,
|
|
'donationToSchool' => $donationToSchool,
|
|
'totalPaid' => $totalPaid,
|
|
'amountCollected' => $amountCollected,
|
|
'totalUnpaid' => $totalUnpaid,
|
|
'netAmount' => $netAmount,
|
|
'totalEventFees' => $totalEventFees,
|
|
];
|
|
}
|
|
|
|
private function getEventFeesTotal(?string $schoolYear, ?string $dateFrom, ?string $dateTo): float
|
|
{
|
|
$db = \Config\Database::connect();
|
|
$builder = $db->table('event_charges ec')
|
|
->select('COALESCE(SUM(ec.charged),0) AS amount', false);
|
|
if (!empty($schoolYear)) {
|
|
$builder->where('ec.school_year', $schoolYear);
|
|
}
|
|
if (!empty($dateFrom)) {
|
|
$builder->where('DATE(ec.created_at) >=', $dateFrom);
|
|
}
|
|
if (!empty($dateTo)) {
|
|
$builder->where('DATE(ec.created_at) <=', $dateTo);
|
|
}
|
|
$row = $builder->get()->getRowArray();
|
|
return $row ? (float)($row['amount'] ?? 0) : 0.0;
|
|
}
|
|
|
|
|
|
/**
|
|
* Management page: list parents with outstanding balances (> 0) for a school year.
|
|
* Supports HTML and JSON (Accept: application/json or format=json).
|
|
*/
|
|
public function unpaidParents()
|
|
{
|
|
$configModel = new \App\Models\ConfigurationModel();
|
|
$schoolYear = trim((string)($this->request->getGet('school_year') ?? ''));
|
|
if ($schoolYear === '') {
|
|
$schoolYear = (string) ($configModel->getConfig('school_year') ?? date('Y'));
|
|
}
|
|
|
|
// Build school year options from invoices
|
|
$schoolYears = [];
|
|
try {
|
|
$db = \Config\Database::connect();
|
|
$rows = $db->table('invoices')
|
|
->select('DISTINCT school_year', false)
|
|
->where('school_year IS NOT NULL', null, false)
|
|
->orderBy('school_year', 'DESC')
|
|
->get()->getResultArray();
|
|
foreach ($rows as $r) {
|
|
$val = (string)($r['school_year'] ?? '');
|
|
if ($val !== '' && !in_array($val, $schoolYears, true)) $schoolYears[] = $val;
|
|
}
|
|
} catch (\Throwable $e) {}
|
|
if (empty($schoolYears) && !empty($schoolYear)) {
|
|
$schoolYears[] = (string)$schoolYear;
|
|
}
|
|
|
|
// Aggregate balances by parent for selected school year
|
|
// IMPORTANT: Compute current balance = total - payments - discounts - refundsPaid
|
|
// rather than trusting invoices.balance which may become stale.
|
|
$db = \Config\Database::connect();
|
|
$invRows = $db->table('invoices i')
|
|
->select('i.id, i.parent_id, i.total_amount, u.firstname, u.lastname, u.email')
|
|
->join('users u', 'u.id = i.parent_id', 'inner')
|
|
->where('i.school_year', $schoolYear)
|
|
->orderBy('i.parent_id', 'ASC')
|
|
->orderBy('i.id', 'ASC')
|
|
->get()->getResultArray();
|
|
|
|
// Group by parent and compute balances dynamically
|
|
$byParent = [];
|
|
$paymentsTbl = 'payments';
|
|
$hasStatus = $db->fieldExists('status', $paymentsTbl);
|
|
$hasVoid = $db->fieldExists('is_void', $paymentsTbl);
|
|
|
|
foreach ($invRows as $r) {
|
|
$iid = (int)($r['id'] ?? 0);
|
|
$pid = (int)($r['parent_id'] ?? 0);
|
|
if ($iid <= 0 || $pid <= 0) continue;
|
|
|
|
// Sum payments for this invoice (exclude void/failed if such columns exist)
|
|
$qb = $db->table('payments')->select('COALESCE(SUM(paid_amount),0) AS tot')->where('invoice_id', $iid);
|
|
if ($hasStatus) {
|
|
$qb->groupStart()
|
|
->whereNotIn('status', ['void','voided','refunded','failed','chargeback','declined','reversed','canceled','cancelled'])
|
|
->orWhere('status IS NULL', null, false)
|
|
->groupEnd();
|
|
}
|
|
if ($hasVoid) {
|
|
$qb->groupStart()
|
|
->where('is_void', 0)
|
|
->orWhere('is_void IS NULL', null, false)
|
|
->groupEnd();
|
|
}
|
|
$paidRow = $qb->get()->getRowArray();
|
|
$paidSum = (float)($paidRow['tot'] ?? 0);
|
|
|
|
// Sum discounts for this invoice
|
|
$discRow = $db->table('discount_usages')
|
|
->select('COALESCE(SUM(discount_amount),0) AS tot')
|
|
->where('invoice_id', $iid)
|
|
->get()->getRowArray();
|
|
$discSum = (float)($discRow['tot'] ?? 0);
|
|
|
|
// Sum refunds PAID for this invoice (Partial/Paid only)
|
|
$refRow = $db->table('refunds')
|
|
->select('COALESCE(SUM(refund_paid_amount),0) AS tot')
|
|
->where('invoice_id', $iid)
|
|
->whereIn('status', ['Partial','Paid'])
|
|
->get()->getRowArray();
|
|
$refSum = (float)($refRow['tot'] ?? 0);
|
|
|
|
$total = (float)($r['total_amount'] ?? 0);
|
|
$balance = max(0.0, round($total - $discSum - $paidSum - $refSum, 2));
|
|
|
|
if (!isset($byParent[$pid])) {
|
|
$byParent[$pid] = [
|
|
'parent_id' => $pid,
|
|
'firstname' => (string)($r['firstname'] ?? ''),
|
|
'lastname' => (string)($r['lastname'] ?? ''),
|
|
'email' => (string)($r['email'] ?? ''),
|
|
'total_invoice' => 0.0,
|
|
'total_balance' => 0.0,
|
|
'total_paid' => 0.0,
|
|
'total_discount'=> 0.0,
|
|
];
|
|
}
|
|
$byParent[$pid]['total_invoice'] += $total;
|
|
$byParent[$pid]['total_balance'] += $balance;
|
|
$byParent[$pid]['total_paid'] += $paidSum; // aggregated payments (for type classification)
|
|
$byParent[$pid]['total_discount']+= $discSum;
|
|
}
|
|
|
|
// Add extra charges not yet applied to invoices (or missing invoice_id)
|
|
$extraRows = $db->table('additional_charges ac')
|
|
->select(
|
|
"ac.parent_id,
|
|
u.firstname,
|
|
u.lastname,
|
|
u.email,
|
|
COALESCE(SUM(ac.amount), 0) AS total_extra",
|
|
false
|
|
)
|
|
->join('users u', 'u.id = ac.parent_id', 'left')
|
|
->where('ac.school_year', $schoolYear)
|
|
->where('ac.status !=', 'void')
|
|
->groupStart()
|
|
->where('ac.invoice_id IS NULL', null, false)
|
|
->orWhere('ac.invoice_id', 0)
|
|
->orWhere('ac.status !=', 'applied')
|
|
->groupEnd()
|
|
->groupBy('ac.parent_id')
|
|
->get()
|
|
->getResultArray();
|
|
|
|
foreach ($extraRows as $er) {
|
|
$pid = (int)($er['parent_id'] ?? 0);
|
|
if ($pid <= 0) continue;
|
|
$extra = (float)($er['total_extra'] ?? 0);
|
|
if (abs($extra) < 0.00001) continue;
|
|
if (!isset($byParent[$pid])) {
|
|
$byParent[$pid] = [
|
|
'parent_id' => $pid,
|
|
'firstname' => (string)($er['firstname'] ?? ''),
|
|
'lastname' => (string)($er['lastname'] ?? ''),
|
|
'email' => (string)($er['email'] ?? ''),
|
|
'total_invoice' => 0.0,
|
|
'total_balance' => 0.0,
|
|
'total_paid' => 0.0,
|
|
'total_discount'=> 0.0,
|
|
];
|
|
}
|
|
$byParent[$pid]['total_invoice'] += $extra;
|
|
$byParent[$pid]['total_balance'] += $extra;
|
|
}
|
|
|
|
$eventFeesPerParent = [];
|
|
try {
|
|
$eventFeesRows = $db->table('event_charges ec')
|
|
->select('ec.parent_id, COALESCE(SUM(ec.charged),0) AS event_fees', false)
|
|
->where('ec.school_year', $schoolYear)
|
|
->groupBy('ec.parent_id')
|
|
->get()
|
|
->getResultArray();
|
|
foreach ($eventFeesRows as $row) {
|
|
$pid = (int)($row['parent_id'] ?? 0);
|
|
if ($pid <= 0) continue;
|
|
$eventFeesPerParent[$pid] = (float)($row['event_fees'] ?? 0);
|
|
}
|
|
} catch (\Throwable $e) {
|
|
// ignore, fallback to no event fees data
|
|
}
|
|
|
|
// Reduce into rows list; only parents with positive balance
|
|
// Also compute remaining installments and suggested monthly amount
|
|
$rows = [];
|
|
// Installment end date from config (same source as manual pay UI)
|
|
$installmentEndRaw = (string) (new \App\Models\ConfigurationModel())->getConfig('installment_date');
|
|
$tzName = (string) (config('School')->attendance['timezone'] ?? user_timezone());
|
|
$tzNY = new \DateTimeZone($tzName);
|
|
$todayNY = new \DateTimeImmutable('today', $tzNY);
|
|
$endDate = null;
|
|
if ($installmentEndRaw) {
|
|
try { $endDate = new \DateTimeImmutable($installmentEndRaw, $tzNY); } catch (\Throwable $e) { $endDate = null; }
|
|
}
|
|
$monthsUntil = function (?\DateTimeImmutable $end) use ($todayNY): int {
|
|
if (!$end) return 0;
|
|
$y = (int)$end->format('Y') - (int)$todayNY->format('Y');
|
|
$m = (int)$end->format('n') - (int)$todayNY->format('n');
|
|
$months = $y * 12 + $m;
|
|
if ((int)$end->format('j') > (int)$todayNY->format('j')) $months += 1;
|
|
return max(0, $months);
|
|
};
|
|
|
|
foreach ($byParent as $pid => $agg) {
|
|
$balance = (float)($agg['total_balance'] ?? 0);
|
|
if ($balance > 0.00001) {
|
|
$remMonths = $monthsUntil($endDate);
|
|
$remainingInstallments = max(1, $remMonths);
|
|
$instAmount = round($balance / $remainingInstallments, 2);
|
|
|
|
$rows[] = [
|
|
'parent_id' => $pid,
|
|
'firstname' => $agg['firstname'],
|
|
'lastname' => $agg['lastname'],
|
|
'email' => $agg['email'],
|
|
'total_invoice' => (float)$agg['total_invoice'],
|
|
'total_balance' => $balance,
|
|
'total_discount'=> (float)($agg['total_discount'] ?? 0),
|
|
'remaining_installments' => $remainingInstallments,
|
|
'installment_amount' => $instAmount,
|
|
];
|
|
}
|
|
}
|
|
|
|
// Order by total_balance desc (to mimic previous builder ordering)
|
|
usort($rows, static function ($a, $b) {
|
|
return ($b['total_balance'] <=> $a['total_balance']);
|
|
});
|
|
|
|
// Compute a global next installment date (first day of next month, local time)
|
|
$tz = new \DateTimeZone($tzName);
|
|
$now = new \DateTime('now', $tz);
|
|
$nextInstallmentDt = (clone $now)->modify('first day of next month');
|
|
$nextInstallmentYmd = $nextInstallmentDt->format('Y-m-d');
|
|
|
|
// Determine whether each parent has any payments for this year (installment vs no_payment)
|
|
$parentIds = array_values(array_unique(array_map(static fn($r) => (int)($r['parent_id'] ?? 0), $rows)));
|
|
$hasPayments = [];
|
|
$paidTotals = [];
|
|
$paymentCounts = [];
|
|
if (!empty($parentIds)) {
|
|
try {
|
|
$pRows = $db->table('payments')
|
|
->select('parent_id, SUM(paid_amount) AS total_paid, COUNT(*) AS payment_count')
|
|
->whereIn('parent_id', $parentIds)
|
|
->where('school_year', $schoolYear)
|
|
->groupBy('parent_id')
|
|
->get()->getResultArray();
|
|
foreach ($pRows as $pr) {
|
|
$pid = (int)($pr['parent_id'] ?? 0);
|
|
if ($pid > 0) {
|
|
$hasPayments[$pid] = true;
|
|
$paidTotals[$pid] = (float)($pr['total_paid'] ?? 0);
|
|
$paymentCounts[$pid] = (int)($pr['payment_count'] ?? 0);
|
|
}
|
|
}
|
|
} catch (\Throwable $e) {
|
|
// ignore; default type will be 'no_payment'
|
|
}
|
|
}
|
|
|
|
$dataRows = array_map(function(array $r) use ($hasPayments, $paidTotals, $paymentCounts, $nextInstallmentYmd, $eventFeesPerParent) {
|
|
$pid = (int)($r['parent_id'] ?? 0);
|
|
$name = trim((string)($r['firstname'] ?? '') . ' ' . (string)($r['lastname'] ?? ''));
|
|
return [
|
|
'parent_id' => $pid,
|
|
'parent_name' => $name,
|
|
'email' => (string)($r['email'] ?? ''),
|
|
'total_invoice' => (float)($r['total_invoice'] ?? 0),
|
|
'total_balance' => (float)($r['total_balance'] ?? 0),
|
|
'total_discount'=> (float)($r['total_discount'] ?? 0),
|
|
'remaining_installments' => (int)($r['remaining_installments'] ?? 0),
|
|
'installment_amount' => (float)($r['installment_amount'] ?? 0),
|
|
'type' => isset($hasPayments[$pid]) ? 'installment' : 'no_payment',
|
|
'total_paid' => isset($paidTotals[$pid]) ? (float)$paidTotals[$pid] : 0.0,
|
|
'payment_count' => isset($paymentCounts[$pid]) ? (int)$paymentCounts[$pid] : 0,
|
|
'has_installment'=> isset($hasPayments[$pid]) ? 1 : 0,
|
|
'next_installment' => $nextInstallmentYmd,
|
|
'event_fees' => (float)($eventFeesPerParent[$pid] ?? 0),
|
|
];
|
|
}, $rows);
|
|
|
|
if ($this->wantsJson() || strtolower((string)($this->request->getGet('format') ?? '')) === 'json') {
|
|
return $this->response->setJSON([
|
|
'ok' => true,
|
|
'school_year' => $schoolYear,
|
|
'schoolYears' => $schoolYears,
|
|
'results' => $dataRows,
|
|
'csrf_token' => csrf_token(),
|
|
'csrf_hash' => csrf_hash(),
|
|
]);
|
|
}
|
|
|
|
return view('payment/unpaid_parents', [
|
|
'school_year' => $schoolYear,
|
|
'schoolYears' => $schoolYears,
|
|
'rows' => $dataRows,
|
|
]);
|
|
}
|
|
|
|
|
|
|
|
|
|
}
|