query(" SELECT i.*, c.name as customer_name, c.email as customer_email FROM invoices i JOIN customers c ON i.customer_id = c.id WHERE i.status IN ('Unpaid', 'Partial', 'Overdue') AND i.due_date < CURDATE() AND (i.last_reminded_at IS NULL OR i.last_reminded_at < DATE_SUB(NOW(), INTERVAL 7 DAY)) AND i.deleted_at IS NULL ")->fetchAll(); foreach ($overdue_invoices as $invoice) { echo "Sending reminder for Invoice #{$invoice['invoice_number']} to {$invoice['customer_email']}..." . PHP_EOL; $subject = "Payment Reminder: Invoice #{$invoice['invoice_number']}"; $body = "

Payment Reminder

Dear {$invoice['customer_name']},

This is a friendly reminder that your invoice #{$invoice['invoice_number']} with an amount of " . format_currency($invoice['total_amount']) . " was due on {$invoice['due_date']}.

Please kindly arrange for payment at your earliest convenience. We have attached the invoice for your reference.

Thank you for your business!

"; $pdf_content = DocumentService::generateInvoicePDF($invoice['id'], 'S'); $opts = [ 'attachments' => [ [ 'data' => $pdf_content, 'name' => "Invoice_{$invoice['invoice_number']}.pdf" ] ] ]; $res = MailService::sendMail($invoice['customer_email'], $subject, $body, null, $opts); if ($res['success']) { $stmt = db()->prepare("UPDATE invoices SET last_reminded_at = NOW(), status = 'Overdue' WHERE id = ?"); $stmt->execute([$invoice['id']]); echo "Successfully reminded." . PHP_EOL; } else { echo "Failed to send: " . $res['error'] . PHP_EOL; } } // 2. Quotation Follow-up Reminders $pending_quotations = db()->query(" SELECT q.*, c.name as customer_name, c.email as customer_email FROM quotations q JOIN customers c ON q.customer_id = c.id WHERE q.status = 'Sent' AND q.expiry_date >= CURDATE() AND (q.last_reminded_at IS NULL OR q.last_reminded_at < DATE_SUB(NOW(), INTERVAL 5 DAY)) AND q.deleted_at IS NULL ")->fetchAll(); foreach ($pending_quotations as $quote) { echo "Sending follow-up for Quotation #{$quote['quotation_number']} to {$quote['customer_email']}..." . PHP_EOL; $subject = "Follow-up: Quotation #{$quote['quotation_number']}"; $body = "

Quotation Follow-up

Dear {$quote['customer_name']},

We're following up on the quotation #{$quote['quotation_number']} we sent you on {$quote['issue_date']}.

The quotation is valid until {$quote['expiry_date']}. Please let us know if you have any questions or if you're ready to proceed. We have attached the quotation for your convenience.

Best regards,
The Sales Team

"; $pdf_content = DocumentService::generateQuotationPDF($quote['id'], 'S'); $opts = [ 'attachments' => [ [ 'data' => $pdf_content, 'name' => "Quotation_{$quote['quotation_number']}.pdf" ] ] ]; $res = MailService::sendMail($quote['customer_email'], $subject, $body, null, $opts); if ($res['success']) { $stmt = db()->prepare("UPDATE quotations SET last_reminded_at = NOW() WHERE id = ?"); $stmt->execute([$quote['id']]); echo "Successfully sent follow-up." . PHP_EOL; } else { echo "Failed to send: " . $res['error'] . PHP_EOL; } } echo "Reminder Scheduler finished." . PHP_EOL;