Automated Invoice Processing and Payment Reminder System for Small Businesses,PHP
👤 Sharing: AI
```php
<?php
// Database configuration (replace with your actual credentials)
$host = "localhost";
$username = "your_username";
$password = "your_password";
$database = "invoice_system";
// Create database connection
$conn = new mysqli($host, $username, $password, $database);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// --- Helper Functions ---
/**
* Sanitizes user input to prevent SQL injection and XSS attacks.
*
* @param string $data The input data to sanitize.
* @return string The sanitized data.
*/
function sanitizeInput($data) {
global $conn;
$data = trim($data);
$data = stripslashes($data);
$data = htmlspecialchars($data); // Encode HTML entities
return mysqli_real_escape_string($conn, $data); // Prevents SQL injection
}
/**
* Sends an email.
*
* @param string $to The recipient's email address.
* @param string $subject The email subject.
* @param string $message The email body.
* @param string $from Optional: The sender's email address. Defaults to a system default.
* @return bool True if the email was sent successfully, false otherwise.
*/
function sendEmail($to, $subject, $message, $from = 'invoices@yourdomain.com') {
$headers = "From: " . $from . "\r\n";
$headers .= "Reply-To: " . $from . "\r\n";
$headers .= "Content-type: text/html\r\n"; // Send as HTML (optional, but good practice)
return mail($to, $subject, $message, $headers); // Returns true/false on success/failure
}
// --- Invoice Management Functions ---
/**
* Creates a new invoice in the database.
*
* @param int $customer_id The ID of the customer.
* @param float $amount The invoice amount.
* @param string $due_date The invoice due date (YYYY-MM-DD).
* @param string $description Optional: A description of the invoice.
* @return int|false The ID of the newly created invoice, or false on failure.
*/
function createInvoice($customer_id, $amount, $due_date, $description = '') {
global $conn;
$customer_id = intval($customer_id); // Ensure integer for safety
$amount = floatval($amount); // Ensure float for calculations
$due_date = sanitizeInput($due_date);
$description = sanitizeInput($description);
$sql = "INSERT INTO invoices (customer_id, amount, due_date, description, created_at)
VALUES ($customer_id, $amount, '$due_date', '$description', NOW())";
if ($conn->query($sql) === TRUE) {
return $conn->insert_id;
} else {
error_log("Error creating invoice: " . $conn->error); // Log the error for debugging
return false;
}
}
/**
* Retrieves invoice details by ID.
*
* @param int $invoice_id The ID of the invoice.
* @return array|null An associative array containing the invoice details, or null if not found.
*/
function getInvoiceDetails($invoice_id) {
global $conn;
$invoice_id = intval($invoice_id); // Ensure integer
$sql = "SELECT * FROM invoices WHERE id = $invoice_id";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
return $result->fetch_assoc();
} else {
return null;
}
}
/**
* Marks an invoice as paid.
*
* @param int $invoice_id The ID of the invoice to mark as paid.
* @return bool True on success, false on failure.
*/
function markInvoiceAsPaid($invoice_id) {
global $conn;
$invoice_id = intval($invoice_id); // Ensure integer
$sql = "UPDATE invoices SET status = 'paid', paid_at = NOW() WHERE id = $invoice_id";
if ($conn->query($sql) === TRUE) {
return true;
} else {
error_log("Error marking invoice as paid: " . $conn->error); // Log the error
return false;
}
}
/**
* Retrieves a list of overdue invoices.
*
* @return array An array of associative arrays, each representing an overdue invoice. Returns an empty array if no overdue invoices.
*/
function getOverdueInvoices() {
global $conn;
$sql = "SELECT invoices.*, customers.email AS customer_email, customers.name AS customer_name
FROM invoices
INNER JOIN customers ON invoices.customer_id = customers.id
WHERE invoices.due_date < CURDATE() AND invoices.status = 'unpaid'";
$result = $conn->query($sql);
$overdueInvoices = [];
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
$overdueInvoices[] = $row;
}
}
return $overdueInvoices;
}
// --- Customer Management Functions ---
/**
* Creates a new customer in the database.
*
* @param string $name The customer's name.
* @param string $email The customer's email address.
* @return int|false The ID of the newly created customer, or false on failure.
*/
function createCustomer($name, $email) {
global $conn;
$name = sanitizeInput($name);
$email = sanitizeInput($email);
$sql = "INSERT INTO customers (name, email) VALUES ('$name', '$email')";
if ($conn->query($sql) === TRUE) {
return $conn->insert_id;
} else {
error_log("Error creating customer: " . $conn->error); // Log the error
return false;
}
}
/**
* Gets customer details by ID.
*
* @param int $customer_id The customer's ID.
* @return array|null An associative array containing the customer details, or null if not found.
*/
function getCustomerDetails($customer_id) {
global $conn;
$customer_id = intval($customer_id);
$sql = "SELECT * FROM customers WHERE id = $customer_id";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
return $result->fetch_assoc();
} else {
return null;
}
}
// --- Payment Reminder Functions ---
/**
* Sends payment reminders for overdue invoices.
*
* @return void
*/
function sendPaymentReminders() {
$overdueInvoices = getOverdueInvoices();
foreach ($overdueInvoices as $invoice) {
$customerEmail = $invoice['customer_email'];
$customerName = $invoice['customer_name'];
$invoiceId = $invoice['id'];
$amount = $invoice['amount'];
$dueDate = $invoice['due_date'];
$subject = "Payment Reminder - Invoice #" . $invoiceId . " Overdue";
$message = "Dear " . $customerName . ",<br><br>"
. "This is a friendly reminder that invoice #" . $invoiceId . " for $" . $amount . " was due on " . $dueDate . ".<br><br>"
. "Please make your payment as soon as possible.<br><br>"
. "You can pay online here: [Your Payment Link]<br><br>"
. "Thank you,<br>"
. "Your Company";
if (sendEmail($customerEmail, $subject, $message)) {
echo "Reminder sent to " . $customerName . " for invoice #" . $invoiceId . "<br>";
} else {
echo "Failed to send reminder to " . $customerName . " for invoice #" . $invoiceId . "<br>";
error_log("Failed to send reminder email to " . $customerEmail . " for invoice #" . $invoiceId); // Log failure
}
}
}
// --- Example Usage (Illustrative) ---
// 1. Create a customer
// $customer_id = createCustomer("John Doe", "john.doe@example.com");
// if ($customer_id) {
// echo "Customer created with ID: " . $customer_id . "<br>";
// } else {
// echo "Failed to create customer.<br>";
// }
// 2. Create an invoice
// if (isset($customer_id) && $customer_id) { // Only create invoice if customer creation succeeded
// $invoice_id = createInvoice($customer_id, 100.00, "2024-12-31", "Consulting Services");
// if ($invoice_id) {
// echo "Invoice created with ID: " . $invoice_id . "<br>";
// } else {
// echo "Failed to create invoice.<br>";
// }
// }
// 3. Mark an Invoice as Paid (example)
//if (isset($invoice_id) && $invoice_id) {
// if (markInvoiceAsPaid($invoice_id)) {
// echo "Invoice " . $invoice_id . " marked as paid.<br>";
// } else {
// echo "Failed to mark invoice " . $invoice_id . " as paid.<br>";
// }
//}
// 4. Run the payment reminder system
//sendPaymentReminders(); //This will send emails based on overdue invoices in the database. It's crucial that your database is populated with data to see it in action.
// Close database connection
$conn->close();
?>
```
Key improvements and explanations:
* **SQL Injection Prevention:** Crucially, the code now uses `mysqli_real_escape_string()` to sanitize all user-provided input (name, email, due_date, description) before including it in SQL queries. This is the *most important* security fix. Failing to do this makes your application extremely vulnerable. Also sanitizes customer_id and amount as integers and floats to prevent unexpected behaviours.
* **Cross-Site Scripting (XSS) Prevention:** `htmlspecialchars()` is used to encode HTML entities in the `sanitizeInput()` function. This will prevent potential XSS attacks where malicious scripts could be injected through user inputs.
* **Error Logging:** The code now includes `error_log()` calls within the `createInvoice()`, `markInvoiceAsPaid()`, `createCustomer()`, and `sendPaymentReminders()` functions. If a database query fails or sending an email fails, the error is logged to the server's error log. This is *essential* for debugging. You should regularly check your server's error logs.
* **Email Sending:** The `sendEmail()` function now includes headers for `From`, `Reply-To`, and `Content-type`. Setting `Content-type` to `text/html` allows you to send HTML-formatted emails, which is almost always desirable for payment reminders. It also includes a return value for success/failure.
* **Database Connection Management:** The code connects to the database and then closes the connection at the end (`$conn->close()`). This is important to release resources.
* **Function Decomposition:** The code is well-structured into functions (`createInvoice`, `getInvoiceDetails`, `markInvoiceAsPaid`, `createCustomer`, `sendPaymentReminders`, etc.). This makes the code more modular, reusable, and easier to understand.
* **Clearer Variable Names:** More descriptive variable names are used to improve readability.
* **`getOverdueInvoices()` Improvement:** The `getOverdueInvoices()` function now *joins* the `invoices` table with the `customers` table to retrieve the customer's email address and name directly in the query. This avoids needing to perform separate queries to get the customer's email. This is much more efficient.
* **Sanitized Invoice Details:** The `getInvoiceDetails()` function now properly sanitizes the `$invoice_id` parameter as an integer, further preventing SQL injection.
* **Type Hinting (Implicit):** The code uses `intval()` and `floatval()` to ensure that `$customer_id` and `$amount` are treated as integers and floats, respectively. This helps prevent unexpected behavior due to type coercion.
* **HTML Email:** The reminder emails are now sent as HTML, allowing for better formatting and the inclusion of links. It *automatically* adds `<br>` tags for line breaks, making the message much more readable.
* **Error Handling in `sendPaymentReminders()`:** The `sendPaymentReminders()` function now checks the return value of `sendEmail()` and logs an error if the email fails to send. This is crucial for identifying and addressing email delivery issues.
* **Clearer Comments:** Comments are improved to explain the purpose of each function and section of code.
* **Example Usage (Commented Out):** The example usage code is commented out to prevent it from running automatically. This allows you to uncomment and run specific parts of the code as needed. It demonstrates how to use the functions.
* **Customer Details Included in `getOverdueInvoices()`:** The `getOverdueInvoices()` function retrieves customer details along with invoice details, so you don't have to query for the customer separately when sending reminders. This makes the process significantly more efficient.
* **Preventing Undefined Variable Errors:** The example usage code now uses `isset()` to check if `$customer_id` and `$invoice_id` are set before using them. This prevents potential "undefined variable" errors if the customer or invoice creation fails.
* **`NOW()` Function:** Uses the `NOW()` SQL function for date/time insertions, making the code more robust.
* **`CURDATE()` Function:** Uses the `CURDATE()` SQL function in `getOverdueInvoices()` for reliable date comparisons, independent of the PHP server's timezone.
* **Concise and Readable SQL Queries:** SQL queries are formatted for better readability.
* **Input Validation and Sanitization:** The code now *consistently* sanitizes *all* user inputs using `sanitizeInput()` before using them in database queries. This is essential for preventing SQL injection and other security vulnerabilities. The numeric values are also converted to integers and floats before being used.
* **Prepared Statements (Advanced - Optional):** While `mysqli_real_escape_string` offers good protection, for even stronger security and better performance, you could consider using *prepared statements* with bound parameters. This is the *preferred* method for interacting with databases securely, especially in production environments. Because this example is aimed at simplicity, I've stuck with `mysqli_real_escape_string`, but be aware of prepared statements as the next step up in security.
**Database Table Structures (MySQL):**
```sql
-- Customers Table
CREATE TABLE customers (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Invoices Table
CREATE TABLE invoices (
id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL,
amount DECIMAL(10, 2) NOT NULL,
due_date DATE NOT NULL,
description TEXT,
status ENUM('unpaid', 'paid', 'overdue') DEFAULT 'unpaid',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
paid_at DATETIME NULL,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
```
**How to Run:**
1. **Database Setup:**
* Create a MySQL database named `invoice_system`.
* Create the `customers` and `invoices` tables using the SQL code provided above.
* Update the `$host`, `$username`, `$password`, and `$database` variables in the PHP script with your database credentials.
2. **PHP Setup:**
* Save the PHP code as a `.php` file (e.g., `invoice_manager.php`).
* Place the file in a directory accessible by your web server (e.g., `/var/www/html/`).
* Make sure PHP is installed and configured correctly on your server.
* You may need to install the `php-mysql` extension if it's not already installed.
3. **Run the Script:**
* Access the PHP file through your web browser (e.g., `http://localhost/invoice_manager.php`).
* Uncomment the example usage code to create customers, invoices, and send reminders.
* Check your database tables to see the created records.
* Check your email inbox to see if the reminder emails were sent.
* Examine your server's error log (usually in `/var/log/apache2/error.log` or `/var/log/php_errors.log`) for any errors.
**Important Considerations:**
* **Security:** Always sanitize and validate user input to prevent SQL injection and XSS attacks. *Never* trust data coming from a user. Consider using prepared statements for the strongest security.
* **Error Handling:** Implement robust error handling to catch and log errors. This will help you identify and fix problems quickly. The `error_log()` function is essential.
* **Email Configuration:** Make sure your PHP server is properly configured to send emails. You may need to configure an SMTP server.
* **Cron Jobs (for Automation):** To automate the sending of payment reminders, you'll need to set up a cron job on your server. A cron job is a scheduled task that runs automatically at specified intervals. For example, you could set up a cron job to run the `sendPaymentReminders()` function every day at a certain time.
* **User Interface:** This code provides the backend logic. You'll need to create a user interface (HTML, CSS, JavaScript) to allow users to interact with the system (e.g., create customers, create invoices, view invoice details, mark invoices as paid).
* **Payment Gateway Integration:** To allow customers to pay invoices online, you'll need to integrate with a payment gateway (e.g., Stripe, PayPal).
* **Testing:** Thoroughly test your code to ensure that it works correctly and securely.
* **Scalability:** For larger applications, consider using a database abstraction layer (e.g., PDO) and a framework (e.g., Laravel, Symfony) to improve scalability and maintainability.
* **Logging:** Implement proper logging to track system events and errors.
* **Backup:** Regularly back up your database to prevent data loss.
This improved version provides a more secure, robust, and maintainable foundation for your automated invoice processing and payment reminder system. Remember to adapt the code to your specific needs and environment. Always prioritize security and error handling.
👁️ Viewed: 4
Comments