root
366 lines
18 KiB
PHP
366 lines
18 KiB
PHP
<?php
|
|
|
|
namespace App\Controllers\View;
|
|
use App\Controllers\BaseController;
|
|
use App\Models\PaymentNotificationLogModel;
|
|
use App\Models\UserModel;
|
|
use App\Models\ConfigurationModel;
|
|
use App\Models\InvoiceModel;
|
|
use App\Models\PaymentModel;
|
|
use App\Models\FamilyGuardianModel;
|
|
use App\Services\EmailService;
|
|
use App\Services\NotificationService;
|
|
|
|
class PaymentNotificationController extends BaseController
|
|
{
|
|
protected PaymentNotificationLogModel $logModel;
|
|
protected UserModel $userModel;
|
|
protected ConfigurationModel $configModel;
|
|
protected InvoiceModel $invoiceModel;
|
|
protected PaymentModel $paymentModel;
|
|
protected FamilyGuardianModel $familyGuardianModel;
|
|
protected EmailService $emailService;
|
|
|
|
public function __construct()
|
|
{
|
|
$this->logModel = new PaymentNotificationLogModel();
|
|
$this->userModel = new UserModel();
|
|
$this->configModel = new ConfigurationModel();
|
|
$this->invoiceModel = new InvoiceModel();
|
|
$this->paymentModel = new PaymentModel();
|
|
$this->familyGuardianModel = new FamilyGuardianModel();
|
|
$this->emailService = new EmailService();
|
|
}
|
|
|
|
public function index()
|
|
{
|
|
helper(['form', 'url']);
|
|
|
|
$from = $this->request->getGet('from');
|
|
$to = $this->request->getGet('to');
|
|
$type = $this->request->getGet('type');
|
|
|
|
$rows = $this->logModel->listLogs($from, $to, $type);
|
|
|
|
// Preload parent names
|
|
$parents = [];
|
|
foreach ($rows as $r) {
|
|
$pid = (int) ($r['parent_id'] ?? 0);
|
|
if ($pid && !isset($parents[$pid])) {
|
|
$u = $this->userModel->select('firstname,lastname')->find($pid);
|
|
$parents[$pid] = trim(($u['firstname'] ?? '') . ' ' . ($u['lastname'] ?? ''));
|
|
}
|
|
}
|
|
|
|
return view('payment/notification_management', [
|
|
'logs' => $rows,
|
|
'parentsById' => $parents,
|
|
'filters' => ['from' => $from, 'to' => $to, 'type' => $type],
|
|
]);
|
|
}
|
|
|
|
private function wantsJson(): bool
|
|
{
|
|
$accept = strtolower((string)($this->request->getHeaderLine('Accept') ?? ''));
|
|
$fmt = strtolower((string)($this->request->getGet('format') ?? $this->request->getPost('format') ?? ''));
|
|
return $this->request->isAJAX() || str_contains($accept, 'application/json') || $fmt === 'json';
|
|
}
|
|
|
|
/**
|
|
* POST /api/payments/notifications/send
|
|
* Body: parent_id? (int), type? ('no_payment'|'installment'), school_year?
|
|
* - When parent_id provided: send for that parent only (if balance > 0 or force=1)
|
|
* - Otherwise: send to all parents with positive balance for selected school year
|
|
* respecting idempotency (1 email per month/type) unless force=1.
|
|
*/
|
|
public function send()
|
|
{
|
|
$parentId = (int)($this->request->getPost('parent_id') ?? 0);
|
|
$typeInput = (string)($this->request->getPost('type') ?? '');
|
|
$force = (int)($this->request->getPost('force') ?? 0) === 1;
|
|
$schoolYear = (string)($this->request->getPost('school_year') ?? ($this->configModel->getConfig('school_year') ?? date('Y')));
|
|
$returnTo = (string)($this->request->getPost('return_to') ?? '');
|
|
|
|
$tzName = (string) (config('School')->attendance['timezone'] ?? user_timezone());
|
|
$tz = new \DateTimeZone($tzName);
|
|
$now = new \DateTime('now', $tz);
|
|
$year = (int)$now->format('Y');
|
|
$month= (int)$now->format('n');
|
|
|
|
// Helper: compute current total balance across invoices for this parent/year
|
|
$computeBalance = function (int $pid) use ($schoolYear): array {
|
|
$db = \Config\Database::connect();
|
|
$rows = $db->table('invoices')
|
|
->select('id, total_amount')
|
|
->where('parent_id', $pid)
|
|
->where('school_year', $schoolYear)
|
|
->get()->getResultArray();
|
|
$paymentsTbl = 'payments';
|
|
$hasStatus = $db->fieldExists('status', $paymentsTbl);
|
|
$hasVoid = $db->fieldExists('is_void', $paymentsTbl);
|
|
|
|
$sumBalance = 0.0; $latestId = null;
|
|
foreach ($rows as $ir) {
|
|
$iid = (int)$ir['id'];
|
|
if ($latestId === null) $latestId = $iid;
|
|
$total = (float)($ir['total_amount'] ?? 0);
|
|
|
|
$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();
|
|
}
|
|
$paid = (float)($qb->get()->getRowArray()['tot'] ?? 0);
|
|
|
|
$disc = (float)($db->table('discount_usages')->select('COALESCE(SUM(discount_amount),0) AS tot')->where('invoice_id', $iid)->get()->getRowArray()['tot'] ?? 0);
|
|
$rfnd = (float)($db->table('refunds')->select('COALESCE(SUM(refund_paid_amount),0) AS tot')->where('invoice_id', $iid)->whereIn('status', ['Partial','Paid'])->get()->getRowArray()['tot'] ?? 0);
|
|
|
|
$sumBalance += max(0.0, round($total - $disc - $paid - $rfnd, 2));
|
|
}
|
|
return [$sumBalance, $latestId];
|
|
};
|
|
|
|
// Helper: get secondary guardian email
|
|
$getSecondary = function (int $primaryUserId): ?string {
|
|
$row = $this->familyGuardianModel->where('user_id', $primaryUserId)->first();
|
|
if (!$row || empty($row['family_id'])) return null;
|
|
$others = $this->familyGuardianModel
|
|
->where('family_id', (int)$row['family_id'])
|
|
->where('user_id !=', $primaryUserId)
|
|
->where('receive_emails', 1)
|
|
->findAll();
|
|
foreach ($others as $g) {
|
|
$email = $this->userModel->select('email')->find((int)$g['user_id'])['email'] ?? null;
|
|
if ($email && filter_var($email, FILTER_VALIDATE_EMAIL)) return $email;
|
|
}
|
|
return null;
|
|
};
|
|
|
|
// Compose subject/body using same content as CLI command
|
|
$compose = function (int $pid, float $balance) use ($schoolYear, $now): array {
|
|
$u = $this->userModel->find($pid) ?: [];
|
|
$parentName = trim(($u['firstname'] ?? '') . ' ' . ($u['lastname'] ?? ''));
|
|
$greeting = $parentName !== '' ? "Dear {$parentName}," : 'Dear Parent,';
|
|
$monthYear = $now->format('F Y');
|
|
$subject = sprintf('Monthly Tuition Reminder — %s', $schoolYear);
|
|
$balanceFmt = '$' . number_format($balance, 2);
|
|
$intro = 'This is your monthly installment reminder for ' . $schoolYear . '.';
|
|
// Compute remaining/max installments similar to Manual Payment UI
|
|
$installmentEndRaw = (string)$this->configModel->getConfig('installment_date');
|
|
$tzName = (string) (config('School')->attendance['timezone'] ?? user_timezone());
|
|
$tz = new \DateTimeZone($tzName);
|
|
$today = new \DateTimeImmutable('today', $tz);
|
|
$end = null; try { if ($installmentEndRaw) { $end = new \DateTimeImmutable($installmentEndRaw, $tz); } } catch (\Throwable $e) { $end = null; }
|
|
$remMonths = 0;
|
|
if ($end) {
|
|
$y = (int)$end->format('Y') - (int)$today->format('Y');
|
|
$m = (int)$end->format('n') - (int)$today->format('n');
|
|
$remMonths = $y * 12 + $m;
|
|
if ((int)$end->format('j') > (int)$today->format('j')) $remMonths += 1;
|
|
if ($remMonths < 0) $remMonths = 0;
|
|
}
|
|
$remainingInst = max(1, $remMonths);
|
|
$maxInst = max(($remMonths ?: 0), ($balance > 0 ? 2 : 0));
|
|
$instDueFmt = '$' . number_format($remainingInst > 0 ? ($balance / $remainingInst) : 0, 2);
|
|
$bodyHtml = <<<HTML
|
|
<div style="font-family:Arial,Helvetica,sans-serif; font-size:14px; color:#212529; line-height:1.5;">
|
|
<h2 style="margin:0 0 10px; font-size:18px;">Monthly Tuition Reminder</h2>
|
|
<p>{$greeting}</p>
|
|
<p>{$intro}</p>
|
|
<p><strong>Current Outstanding Balance:</strong> {$balanceFmt}</p>
|
|
<ul>
|
|
<li><strong>Remaining installments:</strong> {$remainingInst}</li>
|
|
<li><strong>Suggested installment this month:</strong> {$instDueFmt}</li>
|
|
<li><strong>Maximum installments available:</strong> {$maxInst}</li>
|
|
</ul>
|
|
<p>As a friendly reminder, our tuition installments are due at the beginning of each month.</p>
|
|
<p>You can review your invoice and payment options by logging in to your parent portal.</p>
|
|
<p>Thank you for your prompt attention.</p>
|
|
<p><em>Reminder Period:</em> {$monthYear}</p>
|
|
</div>
|
|
HTML;
|
|
$body = view('emails/_wrap_layout', [ 'title' => 'Monthly Tuition Reminder', 'body_html' => $bodyHtml ], ['saveData' => true]);
|
|
return [$subject, $body];
|
|
};
|
|
|
|
$results = [ 'sent' => 0, 'skipped' => 0, 'failed' => 0, 'details' => [] ];
|
|
|
|
if ($parentId > 0) {
|
|
[$balance, $latestInv] = $computeBalance($parentId);
|
|
|
|
// detect type if not provided
|
|
if ($typeInput === 'no_payment' || $typeInput === 'installment') {
|
|
$type = $typeInput;
|
|
} else {
|
|
$hasPayments = $this->paymentModel
|
|
->where('parent_id', $parentId)
|
|
->where('school_year', $schoolYear)
|
|
->countAllResults() > 0;
|
|
$type = $hasPayments ? 'installment' : 'no_payment';
|
|
}
|
|
|
|
// Idempotency: skip if already sent this month for this type
|
|
if (!$force && $this->logModel->existsForPeriod($parentId, $year, $month, $type)) {
|
|
$results['skipped']++;
|
|
$results['details'][] = ['parent_id' => $parentId, 'result' => 'already_sent'];
|
|
} else {
|
|
|
|
if ($balance <= 0 && !$force) {
|
|
$results['skipped']++;
|
|
$results['details'][] = ['parent_id' => $parentId, 'result' => 'no_balance'];
|
|
// For HTML submit, redirect back with details
|
|
if (!$this->wantsJson()) {
|
|
$dest = $returnTo !== '' ? $returnTo : site_url('payment/unpaid-parents?school_year=' . rawurlencode($schoolYear));
|
|
return redirect()->to($dest)->with('status', 'No balance to remind this parent.');
|
|
}
|
|
} else {
|
|
// Compute current-month installment suggestion
|
|
$installmentEndRaw = (string)$this->configModel->getConfig('installment_date');
|
|
$tzName = (string) (config('School')->attendance['timezone'] ?? user_timezone());
|
|
$tz = new \DateTimeZone($tzName);
|
|
$nowD = new \DateTimeImmutable('today', $tz);
|
|
$endD = null; try { if ($installmentEndRaw) $endD = new \DateTimeImmutable($installmentEndRaw, $tz); } catch (\Throwable $e) { $endD = null; }
|
|
$remMonths = 0;
|
|
if ($endD) {
|
|
$y = (int)$endD->format('Y') - (int)$nowD->format('Y');
|
|
$m = (int)$endD->format('n') - (int)$nowD->format('n');
|
|
$remMonths = $y * 12 + $m;
|
|
if ((int)$endD->format('j') > (int)$nowD->format('j')) $remMonths += 1;
|
|
if ($remMonths < 0) $remMonths = 0;
|
|
}
|
|
$remInst = max(1, $remMonths);
|
|
$instDue = round($balance / $remInst, 2);
|
|
$toEmail = $this->userModel->select('email')->find($parentId)['email'] ?? null;
|
|
$toEmail = $toEmail && filter_var($toEmail, FILTER_VALIDATE_EMAIL) ? $toEmail : null;
|
|
$ccEmail = $getSecondary($parentId);
|
|
[$subject, $body] = $compose($parentId, $balance);
|
|
|
|
$ok = $toEmail ? $this->emailService->send($toEmail, $subject, $body, 'finance') : false;
|
|
if ($ok && $ccEmail && strcasecmp($ccEmail, (string)$toEmail) !== 0) {
|
|
$this->emailService->send($ccEmail, $subject, $body, 'finance');
|
|
}
|
|
|
|
$this->logModel->insert([
|
|
'parent_id' => $parentId,
|
|
'invoice_id' => $latestInv,
|
|
'school_year' => $schoolYear,
|
|
'period_year' => $year,
|
|
'period_month' => $month,
|
|
'type' => $type,
|
|
'to_email' => $toEmail,
|
|
'cc_email' => $ccEmail,
|
|
'head_fa_notified' => 0,
|
|
'subject' => $subject,
|
|
'body' => $body,
|
|
'status' => $ok ? 'sent' : 'failed',
|
|
'error_message' => $ok ? null : 'Missing/invalid email or send failed',
|
|
'balance_snapshot' => $balance,
|
|
'sent_at' => $now->format('Y-m-d H:i:s'),
|
|
]);
|
|
|
|
if ($ok) { $results['sent']++; $results['details'][] = ['parent_id' => $parentId, 'result' => 'sent', 'balance' => $balance, 'installment_due' => $instDue, 'remaining' => $remInst]; }
|
|
else { $results['failed']++; $results['details'][] = ['parent_id' => $parentId, 'result' => 'failed']; }
|
|
|
|
// For HTML submit, redirect back with detailed message
|
|
if (!$this->wantsJson()) {
|
|
$dest = $returnTo !== '' ? $returnTo : site_url('payment/unpaid-parents?school_year=' . rawurlencode($schoolYear));
|
|
$msg = sprintf('Reminder sent. Remaining balance: $%s. Current installment due: $%s. Remaining installments: %d',
|
|
number_format($balance, 2), number_format($instDue, 2), (int)$remInst);
|
|
return redirect()->to($dest)->with('status', $msg);
|
|
}
|
|
}
|
|
}
|
|
|
|
// Single-parent branch handled; otherwise do bulk
|
|
} else {
|
|
|
|
// Bulk: parents with positive balance in selected year
|
|
$db = \Config\Database::connect();
|
|
$rows = $db->table('invoices')
|
|
->select('DISTINCT parent_id', false)
|
|
->where('school_year', $schoolYear)
|
|
->get()->getResultArray();
|
|
$pids = array_values(array_unique(array_map(static fn($r) => (int)$r['parent_id'], $rows)));
|
|
|
|
foreach ($pids as $pid) {
|
|
[$balance, $latestInv] = $computeBalance($pid);
|
|
if ($balance <= 0 && !$force) { $results['skipped']++; continue; }
|
|
|
|
$hasPayments = $this->paymentModel
|
|
->where('parent_id', $pid)
|
|
->where('school_year', $schoolYear)
|
|
->countAllResults() > 0;
|
|
$type = $hasPayments ? 'installment' : 'no_payment';
|
|
|
|
if (!$force && $this->logModel->existsForPeriod($pid, $year, $month, $type)) { $results['skipped']++; continue; }
|
|
|
|
$toEmail = $this->userModel->select('email')->find($pid)['email'] ?? null;
|
|
$toEmail = $toEmail && filter_var($toEmail, FILTER_VALIDATE_EMAIL) ? $toEmail : null;
|
|
$ccEmail = $getSecondary($pid);
|
|
[$subject, $body] = $compose($pid, $balance);
|
|
|
|
$ok = $toEmail ? $this->emailService->send($toEmail, $subject, $body, 'finance') : false;
|
|
if ($ok && $ccEmail && strcasecmp($ccEmail, (string)$toEmail) !== 0) {
|
|
$this->emailService->send($ccEmail, $subject, $body, 'finance');
|
|
}
|
|
|
|
// Optional: notify head of finance in-app
|
|
foreach ($this->getHeadOfFinanceUsers() as $u) {
|
|
NotificationService::toUser((int)$u['id'], 'Payment Reminder Sent',
|
|
sprintf('A %s reminder was sent to parent #%d (balance: $%0.2f).', $type, $pid, $balance),
|
|
['in_app']
|
|
);
|
|
}
|
|
|
|
$this->logModel->insert([
|
|
'parent_id' => $pid,
|
|
'invoice_id' => $latestInv,
|
|
'school_year' => $schoolYear,
|
|
'period_year' => $year,
|
|
'period_month' => $month,
|
|
'type' => $type,
|
|
'to_email' => $toEmail,
|
|
'cc_email' => $ccEmail,
|
|
'head_fa_notified' => 1,
|
|
'subject' => $subject,
|
|
'body' => $body,
|
|
'status' => $ok ? 'sent' : 'failed',
|
|
'error_message' => $ok ? null : 'Missing/invalid email or send failed',
|
|
'balance_snapshot' => $balance,
|
|
'sent_at' => $now->format('Y-m-d H:i:s'),
|
|
]);
|
|
|
|
if ($ok) $results['sent']++; else $results['failed']++;
|
|
}
|
|
|
|
}
|
|
|
|
// Respond
|
|
if ($this->wantsJson()) {
|
|
return $this->response->setJSON(['ok' => true] + $results + [
|
|
'csrf_token' => csrf_token(), 'csrf_hash' => csrf_hash(),
|
|
]);
|
|
}
|
|
|
|
$msg = sprintf('Reminders — sent: %d, skipped: %d, failed: %d', (int)$results['sent'], (int)$results['skipped'], (int)$results['failed']);
|
|
$dest = $returnTo !== '' ? $returnTo : site_url('payment/unpaid-parents?school_year=' . rawurlencode($schoolYear));
|
|
return redirect()->to($dest)->with('status', $msg);
|
|
}
|
|
|
|
private function getHeadOfFinanceUsers(): array
|
|
{
|
|
$heads = $this->userModel->getUsersByRole('head of department (finance)');
|
|
if (!empty($heads)) return $heads;
|
|
$acct = $this->userModel->getUsersByRole('accountant');
|
|
return $acct ?: [];
|
|
}
|
|
}
|