root
300 lines
13 KiB
PHP
300 lines
13 KiB
PHP
<?php
|
|
|
|
namespace App\Services\Finance;
|
|
|
|
use App\Models\Configuration;
|
|
use App\Models\FinanceFollowUpNote;
|
|
use Carbon\Carbon;
|
|
use Illuminate\Support\Facades\DB;
|
|
use Illuminate\Support\Facades\Schema;
|
|
|
|
class ParentPaymentFollowUpService
|
|
{
|
|
public function report(array $filters): array
|
|
{
|
|
$schoolYear = $filters['school_year'] ?? (Configuration::getConfig('school_year') ?? date('Y'));
|
|
$minimumBalance = (float) ($filters['minimum_balance'] ?? 0);
|
|
$includePaid = filter_var($filters['include_paid_invoices'] ?? false, FILTER_VALIDATE_BOOLEAN);
|
|
|
|
$invoiceRows = $this->invoiceRows($schoolYear, $filters);
|
|
$paidByInvoice = $this->paymentTotalsByInvoice($schoolYear);
|
|
$discountByInvoice = $this->discountTotalsByInvoice();
|
|
$refundByInvoice = $this->refundTotalsByInvoice();
|
|
$latestNotes = $this->latestNotesByParent($schoolYear);
|
|
$students = $this->studentsByParent();
|
|
|
|
$parents = [];
|
|
foreach ($invoiceRows as $invoice) {
|
|
$invoiceId = (int) ($invoice->id ?? 0);
|
|
$parentId = (int) ($invoice->parent_id ?? 0);
|
|
if ($parentId <= 0) {
|
|
continue;
|
|
}
|
|
|
|
$total = (float) ($invoice->total_amount ?? 0);
|
|
$paid = (float) ($paidByInvoice[$invoiceId] ?? 0);
|
|
$discounted = (float) ($discountByInvoice[$invoiceId] ?? 0);
|
|
$refunded = (float) ($refundByInvoice[$invoiceId] ?? 0);
|
|
$open = max(0, $total - $paid - $discounted - $refunded);
|
|
if (! $includePaid && $open <= 0) {
|
|
continue;
|
|
}
|
|
|
|
if (! isset($parents[$parentId])) {
|
|
$parents[$parentId] = [
|
|
'parent_id' => $parentId,
|
|
'parent_name' => $invoice->parent_name ?? null,
|
|
'parent_email' => $invoice->parent_email ?? null,
|
|
'parent_phone' => $invoice->parent_phone ?? null,
|
|
'student_names' => $students[$parentId] ?? [],
|
|
'school_year' => $schoolYear,
|
|
'invoice_count' => 0,
|
|
'open_invoice_count' => 0,
|
|
'total_invoiced' => 0.0,
|
|
'total_paid' => 0.0,
|
|
'total_discounted' => 0.0,
|
|
'total_refunded' => 0.0,
|
|
'open_balance' => 0.0,
|
|
'oldest_unpaid_invoice_date' => null,
|
|
'days_overdue' => 0,
|
|
'aging_bucket' => 'current',
|
|
'last_payment_date' => null,
|
|
'last_payment_amount' => 0.0,
|
|
'last_contacted_at' => null,
|
|
'last_contacted_by' => null,
|
|
'follow_up_status' => 'not_contacted',
|
|
'next_follow_up_date' => null,
|
|
'promise_to_pay_date' => null,
|
|
'promise_to_pay_amount' => null,
|
|
'escalation_level' => 0,
|
|
'notes_count' => 0,
|
|
'risk_flags' => [],
|
|
];
|
|
}
|
|
|
|
$row = &$parents[$parentId];
|
|
$row['invoice_count']++;
|
|
$row['total_invoiced'] += $total;
|
|
$row['total_paid'] += $paid;
|
|
$row['total_discounted'] += $discounted;
|
|
$row['total_refunded'] += $refunded;
|
|
$row['open_balance'] += $open;
|
|
if ($open > 0) {
|
|
$row['open_invoice_count']++;
|
|
$due = $invoice->due_date ?? $invoice->issue_date ?? $invoice->created_at ?? null;
|
|
if ($due && ($row['oldest_unpaid_invoice_date'] === null || strtotime((string) $due) < strtotime((string) $row['oldest_unpaid_invoice_date']))) {
|
|
$row['oldest_unpaid_invoice_date'] = substr((string) $due, 0, 10);
|
|
}
|
|
}
|
|
unset($row);
|
|
}
|
|
|
|
foreach ($parents as $parentId => &$row) {
|
|
$note = $latestNotes[$parentId] ?? null;
|
|
if ($note) {
|
|
$row['last_contacted_at'] = $note->created_at ? (string) $note->created_at : null;
|
|
$row['last_contacted_by'] = $note->created_by ?? null;
|
|
$row['follow_up_status'] = $note->status ?? 'not_contacted';
|
|
$row['next_follow_up_date'] = $note->next_follow_up_date ?? null;
|
|
$row['promise_to_pay_date'] = $note->promise_to_pay_date ?? null;
|
|
$row['promise_to_pay_amount'] = $note->promise_to_pay_amount !== null ? (float) $note->promise_to_pay_amount : null;
|
|
$row['escalation_level'] = (int) ($note->escalation_level ?? 0);
|
|
}
|
|
$row['notes_count'] = FinanceFollowUpNote::query()->where('parent_id', $parentId)->when($schoolYear, fn ($q) => $q->where('school_year', $schoolYear))->count();
|
|
$row['days_overdue'] = $this->daysOverdue($row['oldest_unpaid_invoice_date']);
|
|
$row['aging_bucket'] = $this->agingBucket($row['days_overdue']);
|
|
$row['risk_flags'] = $this->riskFlags($row);
|
|
}
|
|
unset($row);
|
|
|
|
$rows = array_values(array_filter($parents, function (array $row) use ($filters, $minimumBalance) {
|
|
if ($row['open_balance'] < $minimumBalance) {
|
|
return false;
|
|
}
|
|
if (! empty($filters['aging_bucket']) && $row['aging_bucket'] !== $filters['aging_bucket']) {
|
|
return false;
|
|
}
|
|
if (! empty($filters['follow_up_status']) && $row['follow_up_status'] !== $filters['follow_up_status']) {
|
|
return false;
|
|
}
|
|
if (! empty($filters['next_follow_up_due_before']) && (empty($row['next_follow_up_date']) || strtotime($row['next_follow_up_date']) > strtotime($filters['next_follow_up_due_before']))) {
|
|
return false;
|
|
}
|
|
if (! empty($filters['promise_to_pay_due_before']) && (empty($row['promise_to_pay_date']) || strtotime($row['promise_to_pay_date']) > strtotime($filters['promise_to_pay_due_before']))) {
|
|
return false;
|
|
}
|
|
|
|
return true;
|
|
}));
|
|
|
|
usort($rows, fn ($a, $b) => [$b['open_balance'], $b['days_overdue']] <=> [$a['open_balance'], $a['days_overdue']]);
|
|
|
|
return ['schoolYear' => $schoolYear, 'generatedAt' => now()->toISOString(), 'results' => $rows];
|
|
}
|
|
|
|
public function storeNote(int $parentId, array $payload, ?int $actorId): array
|
|
{
|
|
$note = FinanceFollowUpNote::query()->create([
|
|
'parent_id' => $parentId,
|
|
'invoice_id' => $payload['invoice_id'] ?? null,
|
|
'school_year' => $payload['school_year'] ?? null,
|
|
'status' => $payload['status'] ?? 'contacted',
|
|
'contact_method' => $payload['contact_method'] ?? null,
|
|
'promise_to_pay_date' => $payload['promise_to_pay_date'] ?? null,
|
|
'promise_to_pay_amount' => $payload['promise_to_pay_amount'] ?? null,
|
|
'next_follow_up_date' => $payload['next_follow_up_date'] ?? null,
|
|
'escalation_level' => $payload['escalation_level'] ?? 0,
|
|
'note' => $payload['note'] ?? null,
|
|
'created_by' => $actorId,
|
|
]);
|
|
|
|
return $note->toArray();
|
|
}
|
|
|
|
public function csvRows(array $report): array
|
|
{
|
|
$rows = [['Parent ID', 'Parent', 'Email', 'Phone', 'Students', 'School Year', 'Invoices', 'Open Invoices', 'Total Invoiced', 'Total Paid', 'Open Balance', 'Oldest Unpaid', 'Days Overdue', 'Aging', 'Status', 'Next Follow-Up', 'Promise Date', 'Promise Amount', 'Risk Flags']];
|
|
foreach ($report['results'] as $r) {
|
|
$rows[] = [
|
|
$r['parent_id'], $r['parent_name'], $r['parent_email'], $r['parent_phone'], implode('; ', $r['student_names'] ?? []), $r['school_year'], $r['invoice_count'], $r['open_invoice_count'], $r['total_invoiced'], $r['total_paid'], $r['open_balance'], $r['oldest_unpaid_invoice_date'], $r['days_overdue'], $r['aging_bucket'], $r['follow_up_status'], $r['next_follow_up_date'], $r['promise_to_pay_date'], $r['promise_to_pay_amount'], implode('; ', $r['risk_flags'] ?? []),
|
|
];
|
|
}
|
|
|
|
return $rows;
|
|
}
|
|
|
|
private function invoiceRows(string $schoolYear, array $filters)
|
|
{
|
|
$q = DB::table('invoices')->where('invoices.school_year', $schoolYear);
|
|
if (! empty($filters['parent_id'])) {
|
|
$q->where('invoices.parent_id', $filters['parent_id']);
|
|
}
|
|
if (! empty($filters['date_from'])) {
|
|
$q->whereDate('invoices.created_at', '>=', $filters['date_from']);
|
|
}
|
|
if (! empty($filters['date_to'])) {
|
|
$q->whereDate('invoices.created_at', '<=', $filters['date_to']);
|
|
}
|
|
if (Schema::hasTable('users')) {
|
|
$name = $this->nameExpression('users');
|
|
$q->leftJoin('users', 'users.id', '=', 'invoices.parent_id')
|
|
->addSelect('invoices.*')
|
|
->addSelect(DB::raw($name.' as parent_name'))
|
|
->addSelect('users.email as parent_email');
|
|
if (Schema::hasColumn('users', 'phone')) {
|
|
$q->addSelect('users.phone as parent_phone');
|
|
} else {
|
|
$q->addSelect(DB::raw('NULL as parent_phone'));
|
|
}
|
|
} else {
|
|
$q->select('invoices.*', DB::raw('NULL as parent_name'), DB::raw('NULL as parent_email'), DB::raw('NULL as parent_phone'));
|
|
}
|
|
|
|
return $q->get();
|
|
}
|
|
|
|
private function paymentTotalsByInvoice(string $schoolYear): array
|
|
{
|
|
if (! Schema::hasTable('payments')) {
|
|
return [];
|
|
}
|
|
|
|
return DB::table('payments')->select('invoice_id', DB::raw('COALESCE(SUM(paid_amount),0) total'), DB::raw('MAX(payment_date) last_payment_date'))
|
|
->where('school_year', $schoolYear)->whereNotIn('status', ['void', 'voided', 'failed', 'cancelled', 'canceled', 'reversed'])
|
|
->groupBy('invoice_id')->pluck('total', 'invoice_id')->map(fn ($v) => (float) $v)->all();
|
|
}
|
|
|
|
private function discountTotalsByInvoice(): array
|
|
{
|
|
if (! Schema::hasTable('discount_usages')) {
|
|
return [];
|
|
}
|
|
$col = Schema::hasColumn('discount_usages', 'discount_amount') ? 'discount_amount' : 'amount';
|
|
|
|
return DB::table('discount_usages')->select('invoice_id', DB::raw('COALESCE(SUM('.$col.'),0) total'))->groupBy('invoice_id')->pluck('total', 'invoice_id')->map(fn ($v) => (float) $v)->all();
|
|
}
|
|
|
|
private function refundTotalsByInvoice(): array
|
|
{
|
|
if (! Schema::hasTable('refunds')) {
|
|
return [];
|
|
}
|
|
$col = Schema::hasColumn('refunds', 'refund_amount') ? 'refund_amount' : (Schema::hasColumn('refunds', 'amount') ? 'amount' : null);
|
|
if (! $col) {
|
|
return [];
|
|
}
|
|
|
|
return DB::table('refunds')->select('invoice_id', DB::raw('COALESCE(SUM('.$col.'),0) total'))->whereNotIn('status', ['void', 'voided', 'cancelled', 'canceled'])->groupBy('invoice_id')->pluck('total', 'invoice_id')->map(fn ($v) => (float) $v)->all();
|
|
}
|
|
|
|
private function latestNotesByParent(string $schoolYear): array
|
|
{
|
|
return FinanceFollowUpNote::query()->where('school_year', $schoolYear)->orderByDesc('created_at')->get()->unique('parent_id')->keyBy('parent_id')->all();
|
|
}
|
|
|
|
private function studentsByParent(): array
|
|
{
|
|
if (! Schema::hasTable('students') || ! Schema::hasColumn('students', 'parent_id')) {
|
|
return [];
|
|
}
|
|
$name = $this->nameExpression('students');
|
|
|
|
return DB::table('students')->select('parent_id', DB::raw($name.' as student_name'))->whereNotNull('parent_id')->get()->groupBy('parent_id')->map(fn ($rows) => $rows->pluck('student_name')->filter()->values()->all())->all();
|
|
}
|
|
|
|
private function nameExpression(string $table): string
|
|
{
|
|
if (Schema::hasColumn($table, 'name')) {
|
|
return $table.'.name';
|
|
}
|
|
if (Schema::hasColumn($table, 'full_name')) {
|
|
return $table.'.full_name';
|
|
}
|
|
$driver = DB::connection()->getDriverName();
|
|
$concat = $driver === 'sqlite' ? '%s || \' \' || %s' : "CONCAT(%s, ' ', %s)";
|
|
if (Schema::hasColumn($table, 'firstname') && Schema::hasColumn($table, 'lastname')) {
|
|
return sprintf($concat, $table.'.firstname', $table.'.lastname');
|
|
}
|
|
if (Schema::hasColumn($table, 'first_name') && Schema::hasColumn($table, 'last_name')) {
|
|
return sprintf($concat, $table.'.first_name', $table.'.last_name');
|
|
}
|
|
|
|
return "CAST($table.id AS CHAR)";
|
|
}
|
|
|
|
private function daysOverdue(?string $date): int
|
|
{
|
|
if (! $date) {
|
|
return 0;
|
|
}
|
|
|
|
return max(0, now()->startOfDay()->diffInDays(Carbon::parse($date)->startOfDay(), false) * -1);
|
|
}
|
|
|
|
private function agingBucket(int $days): string
|
|
{
|
|
return match (true) {
|
|
$days <= 0 => 'current', $days <= 30 => '1-30 days overdue', $days <= 60 => '31-60 days overdue', $days <= 90 => '61-90 days overdue', default => '90+ days overdue'
|
|
};
|
|
}
|
|
|
|
private function riskFlags(array $row): array
|
|
{
|
|
$flags = [];
|
|
if ($row['days_overdue'] > 90) {
|
|
$flags[] = '90+ days overdue';
|
|
}
|
|
if ($row['open_balance'] > 1000) {
|
|
$flags[] = 'high balance';
|
|
}
|
|
if ($row['promise_to_pay_date'] && strtotime($row['promise_to_pay_date']) < strtotime(date('Y-m-d'))) {
|
|
$flags[] = 'missed promise-to-pay';
|
|
}
|
|
if (! $row['parent_email'] && ! $row['parent_phone']) {
|
|
$flags[] = 'missing contact info';
|
|
}
|
|
|
|
return $flags;
|
|
}
|
|
}
|