toDateString();
return DB::table('invoices as i')
->join('users as u', 'u.id', '=', 'i.parent_id')
->select(
'u.id as user_id',
'u.firstname',
'u.lastname',
'u.email',
DB::raw('MIN(i.due_date) as due_date'),
DB::raw('SUM(i.balance) as balance')
)
->whereNotNull('i.due_date')
->where('i.due_date', '<', $today)
->where('i.balance', '>', 0)
->whereNotIn(DB::raw('LOWER(i.status)'), ['paid'])
->groupBy('u.id', 'u.firstname', 'u.lastname', 'u.email')
->get()
->map(fn ($row) => (array) $row)
->all();
}
public function sendReminders(array $users): array
{
$sent = 0;
$failed = 0;
foreach ($users as $user) {
$userId = (int) ($user['user_id'] ?? 0);
$email = (string) ($user['email'] ?? '');
$name = trim((string) ($user['firstname'] ?? '').' '.(string) ($user['lastname'] ?? ''));
$this->notifier->notifyUser(
$userId,
'Payment Missed',
'You have a missed payment. Please pay to avoid penalty.',
['in_app'],
'payment',
'parent'
);
if ($email !== '') {
$subject = 'Payment Missed';
$body = 'Dear '.e($name !== '' ? $name : 'Parent').',
'
.'You have a missed payment. Please pay to avoid penalty.
';
$ok = $this->emailService->send($email, $subject, $body, 'finance');
$ok ? $sent++ : $failed++;
}
}
return [
'sent' => $sent,
'failed' => $failed,
];
}
}