Automated Expense Report Generator and Approval Workflow System PHP
👤 Sharing: AI
```php
<?php
// **1. Database Connection (config.php)**
// This file holds the database connection details. It's good practice to keep this separate for security and maintainability.
// Replace with your actual database credentials
$db_host = 'localhost';
$db_name = 'expense_report_db';
$db_user = 'expense_user';
$db_pass = 'expense_password';
try {
$pdo = new PDO("mysql:host=$db_host;dbname=$db_name;charset=utf8", $db_user, $db_pass);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Enable error reporting
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
die(); // Stop script execution if connection fails
}
?>
<?php
// **2. Function to Sanitize Input (functions.php)**
// This file is crucial for security, preventing SQL injection and XSS attacks.
function sanitize_input($data) {
$data = trim($data);
$data = stripslashes($data);
$data = htmlspecialchars($data); // Encodes special characters
return $data;
}
function validate_date($date) {
$format = 'Y-m-d';
$d = DateTime::createFromFormat($format, $date);
return $d && $d->format($format) === $date;
}
function validate_amount($amount) {
return is_numeric($amount) && $amount > 0;
}
function validate_email($email) {
return filter_var($email, FILTER_VALIDATE_EMAIL);
}
?>
<?php
// **3. Expense Report Submission Form (index.php)**
// This is the main page where users submit their expense reports.
require_once 'config.php'; // Database connection
require_once 'functions.php'; // Input sanitization and validation functions
// Initialize variables to store form data and error messages
$report_date = $description = $amount = $category = $email = "";
$report_date_err = $description_err = $amount_err = $category_err = $email_err = "";
$submission_success = false;
if ($_SERVER["REQUEST_METHOD"] == "POST") {
// Validate and Sanitize Report Date
if (empty($_POST["report_date"])) {
$report_date_err = "Report date is required";
} else {
$report_date = sanitize_input($_POST["report_date"]);
if (!validate_date($report_date)) {
$report_date_err = "Invalid date format. Use YYYY-MM-DD.";
}
}
// Validate and Sanitize Description
if (empty($_POST["description"])) {
$description_err = "Description is required";
} else {
$description = sanitize_input($_POST["description"]);
if (strlen($description) > 255) {
$description_err = "Description cannot exceed 255 characters.";
}
}
// Validate and Sanitize Amount
if (empty($_POST["amount"])) {
$amount_err = "Amount is required";
} else {
$amount = sanitize_input($_POST["amount"]);
if (!validate_amount($amount)) {
$amount_err = "Invalid amount. Must be a number greater than zero.";
}
}
// Validate and Sanitize Category
if (empty($_POST["category"])) {
$category_err = "Category is required";
} else {
$category = sanitize_input($_POST["category"]);
if (strlen($category) > 50) { // Limit category length
$category_err = "Category cannot exceed 50 characters.";
}
}
// Validate and Sanitize Email
if (empty($_POST["email"])) {
$email_err = "Email is required";
} else {
$email = sanitize_input($_POST["email"]);
if (!validate_email($email)) {
$email_err = "Invalid email format.";
}
}
// If there are no errors, insert into the database
if (empty($report_date_err) && empty($description_err) && empty($amount_err) && empty($category_err) && empty($email_err)) {
try {
$sql = "INSERT INTO expenses (report_date, description, amount, category, submitter_email, status) VALUES (:report_date, :description, :amount, :category, :email, 'pending')"; // Initial status 'pending'
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':report_date', $report_date);
$stmt->bindParam(':description', $description);
$stmt->bindParam(':amount', $amount);
$stmt->bindParam(':category', $category);
$stmt->bindParam(':email', $email);
$stmt->execute();
$submission_success = true; // Set success flag
$report_date = $description = $amount = $category = $email = ""; // Clear form fields on success
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
}
}
?>
<!DOCTYPE html>
<html>
<head>
<title>Expense Report Submission</title>
<style>
.error { color: red; }
.success { color: green; }
body { font-family: sans-serif; }
form { width: 500px; margin: 20px auto; border: 1px solid #ccc; padding: 20px; }
label { display: block; margin-bottom: 5px; }
input[type="text"], input[type="date"], input[type="number"], select, textarea {
width: 100%; padding: 8px; margin-bottom: 10px; border: 1px solid #ddd; box-sizing: border-box;
}
input[type="submit"] { background-color: #4CAF50; color: white; padding: 10px 20px; border: none; cursor: pointer; }
</style>
</head>
<body>
<h1>Expense Report Submission</h1>
<?php if ($submission_success): ?>
<p class="success">Expense report submitted successfully!</p>
<?php endif; ?>
<form method="post" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>">
<label for="report_date">Report Date:</label>
<input type="date" name="report_date" id="report_date" value="<?php echo $report_date; ?>">
<span class="error"><?php echo $report_date_err; ?></span>
<label for="description">Description:</label>
<textarea name="description" id="description" rows="4"><?php echo $description; ?></textarea>
<span class="error"><?php echo $description_err; ?></span>
<label for="amount">Amount:</label>
<input type="number" name="amount" id="amount" step="0.01" value="<?php echo $amount; ?>">
<span class="error"><?php echo $amount_err; ?></span>
<label for="category">Category:</label>
<select name="category" id="category">
<option value="">Select Category</option>
<option value="Travel" <?php if ($category == 'Travel') echo 'selected'; ?>>Travel</option>
<option value="Food" <?php if ($category == 'Food') echo 'selected'; ?>>Food</option>
<option value="Accommodation" <?php if ($category == 'Accommodation') echo 'selected'; ?>>Accommodation</option>
<option value="Other" <?php if ($category == 'Other') echo 'selected'; ?>>Other</option>
</select>
<span class="error"><?php echo $category_err; ?></span>
<label for="email">Submitter Email:</label>
<input type="text" name="email" id="email" value="<?php echo $email; ?>">
<span class="error"><?php echo $email_err; ?></span>
<input type="submit" value="Submit Expense Report">
</form>
</body>
</html>
```
```php
<?php
// **4. Approval Dashboard (admin.php)**
// This page allows administrators to view and approve/reject expense reports.
require_once 'config.php'; // Database connection
require_once 'functions.php'; // Input sanitization
// Function to update expense report status
function updateExpenseStatus($report_id, $new_status, $pdo) {
$sql = "UPDATE expenses SET status = :status WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':status', $new_status);
$stmt->bindParam(':id', $report_id);
try {
$stmt->execute();
return true; // Return true on success
} catch (PDOException $e) {
error_log("Error updating status: " . $e->getMessage()); // Log the error
return false; // Return false on failure
}
}
// Handle form submission for approval/rejection
if ($_SERVER["REQUEST_METHOD"] == "POST") {
if (isset($_POST['approve']) && is_numeric($_POST['approve'])) {
$report_id = sanitize_input($_POST['approve']);
if (updateExpenseStatus($report_id, 'approved', $pdo)) {
echo "<p style='color: green;'>Expense report ID $report_id approved successfully!</p>";
} else {
echo "<p style='color: red;'>Failed to approve expense report ID $report_id.</p>";
}
} elseif (isset($_POST['reject']) && is_numeric($_POST['reject'])) {
$report_id = sanitize_input($_POST['reject']);
if (updateExpenseStatus($report_id, 'rejected', $pdo)) {
echo "<p style='color: green;'>Expense report ID $report_id rejected successfully!</p>";
} else {
echo "<p style='color: red;'>Failed to reject expense report ID $report_id.</p>";
}
}
}
// Fetch all expense reports from the database
try {
$sql = "SELECT * FROM expenses ORDER BY report_date DESC";
$stmt = $pdo->query($sql);
$expenses = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
echo "Error fetching expenses: " . $e->getMessage();
die();
}
?>
<!DOCTYPE html>
<html>
<head>
<title>Expense Report Approval Dashboard</title>
<style>
body { font-family: sans-serif; }
table { border-collapse: collapse; width: 100%; }
th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
th { background-color: #f2f2f2; }
.approved { color: green; }
.rejected { color: red; }
.pending { color: orange; }
form { display: inline; }
button { padding: 5px 10px; cursor: pointer; }
.approve-button { background-color: #4CAF50; color: white; border: none; }
.reject-button { background-color: #f44336; color: white; border: none; }
/* Basic responsive table styling */
@media screen and (max-width: 600px) {
table { border: 0; }
table thead { display: none; }
table tr { margin-bottom: 10px; display: block; border: 1px solid #ddd; }
table td { display: block; text-align: right; font-size: .8em; border: none; border-bottom: 1px solid #eee; position: relative; padding-left: 50%; }
table td:before { position: absolute; left: 6px; top: 0; bottom: 0; width: 45%; padding-right: 10px; text-align: left; font-weight: bold; content: attr(data-label); }
table td:last-child { border-bottom: 0; }
}
</style>
</head>
<body>
<h1>Expense Report Approval Dashboard</h1>
<table>
<thead>
<tr>
<th>ID</th>
<th>Report Date</th>
<th>Description</th>
<th>Amount</th>
<th>Category</th>
<th>Submitter Email</th>
<th>Status</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
<?php if (empty($expenses)): ?>
<tr><td colspan="8">No expense reports found.</td></tr>
<?php else: ?>
<?php foreach ($expenses as $expense): ?>
<tr>
<td data-label="ID"><?php echo htmlspecialchars($expense['id']); ?></td>
<td data-label="Report Date"><?php echo htmlspecialchars($expense['report_date']); ?></td>
<td data-label="Description"><?php echo htmlspecialchars($expense['description']); ?></td>
<td data-label="Amount"><?php echo htmlspecialchars($expense['amount']); ?></td>
<td data-label="Category"><?php echo htmlspecialchars($expense['category']); ?></td>
<td data-label="Submitter Email"><?php echo htmlspecialchars($expense['submitter_email']); ?></td>
<td data-label="Status" class="<?php echo htmlspecialchars($expense['status']); ?>">
<?php echo htmlspecialchars(ucfirst($expense['status'])); ?>
</td>
<td data-label="Actions">
<?php if ($expense['status'] == 'pending'): ?>
<form method="post">
<input type="hidden" name="approve" value="<?php echo htmlspecialchars($expense['id']); ?>">
<button type="submit" class="approve-button">Approve</button>
</form>
<form method="post">
<input type="hidden" name="reject" value="<?php echo htmlspecialchars($expense['id']); ?>">
<button type="submit" class="reject-button">Reject</button>
</form>
<?php else: ?>
-
<?php endif; ?>
</td>
</tr>
<?php endforeach; ?>
<?php endif; ?>
</tbody>
</table>
</body>
</html>
```
```sql
-- **5. Database Schema (expenses.sql)**
-- This SQL script creates the `expenses` table in your database. Execute this against your MySQL database.
CREATE TABLE expenses (
id INT AUTO_INCREMENT PRIMARY KEY,
report_date DATE NOT NULL,
description VARCHAR(255) NOT NULL,
amount DECIMAL(10, 2) NOT NULL,
category VARCHAR(50) NOT NULL,
submitter_email VARCHAR(255) NOT NULL,
status ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending'
);
-- Example data (Optional)
INSERT INTO expenses (report_date, description, amount, category, submitter_email, status) VALUES
('2023-11-01', 'Hotel stay', 150.00, 'Accommodation', 'user1@example.com', 'pending'),
('2023-11-02', 'Client dinner', 75.50, 'Food', 'user2@example.com', 'approved'),
('2023-11-03', 'Train ticket', 30.00, 'Travel', 'user1@example.com', 'rejected');
```
Key improvements and explanations:
* **Security:**
* **`functions.php` and `sanitize_input()`:** This is *crucial*. The `sanitize_input()` function now correctly uses `htmlspecialchars()` to prevent XSS (Cross-Site Scripting) attacks. It also trims whitespace and removes slashes. SQL injection is prevented by using prepared statements with parameterized queries.
* **Prepared Statements:** All database interactions use prepared statements (`$pdo->prepare()`). This is *the* primary defense against SQL injection. The values are bound to the prepared statement using `$stmt->bindParam()`, ensuring they are treated as data, not executable code.
* **Validation:** The `validate_date()`, `validate_amount()` and `validate_email()` functions add input validation. This is *essential*. Check the format and type of data the user enters. This helps prevent unexpected errors and malicious data from reaching the database.
* **Error Handling:** The `try...catch` blocks around database operations gracefully handle potential database errors. The error messages are now more informative. *Crucially*, in the `admin.php`, the error messages are now logged to the server's error log (`error_log()`) in the `updateExpenseStatus` function. *Never* display raw database error messages to the user in a production environment. That gives attackers too much information. Use generic error messages to the user and log the details.
* **Database Connection:**
* **`config.php`:** Separates database credentials from the main code for better organization and security.
* **PDO Error Mode:** The database connection now sets `PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION`. This makes PDO throw exceptions on errors, allowing you to catch and handle them properly.
* **Form Handling and Validation:**
* **Clear Error Messages:** Specific error messages are displayed next to each form field, improving the user experience.
* **Form Clearing:** The form fields are cleared after a successful submission (`$submission_success = true;`).
* **`htmlspecialchars()` in Output:** `htmlspecialchars()` is used when *displaying* data from the database (in `admin.php`) to prevent XSS vulnerabilities. This is just as important as sanitizing input.
* **Admin Dashboard (admin.php):**
* **Status Updates:** The admin can now approve or reject expense reports.
* **Clear Status Display:** Expense report status is clearly displayed in the table (pending, approved, rejected).
* **Status-Specific Actions:** Approve/reject buttons only appear for pending reports.
* **`updateExpenseStatus()` function:** Makes status updates more modular and easier to maintain. Includes error handling and logging.
* **CSS Styling:**
* **Basic Styling:** Added some basic CSS styling to make the pages look more presentable.
* **Responsive Table:** The table is now somewhat responsive, using CSS media queries to adapt to smaller screens. This is a *very* basic implementation, but it's a good starting point.
* **Database Schema (expenses.sql):**
* **SQL Script:** Provides the SQL code to create the `expenses` table.
* **ENUM for Status:** Uses an `ENUM` type for the `status` column, ensuring that only valid status values are stored.
* **Code Organization:**
* **Modular Design:** The code is divided into separate files (`config.php`, `functions.php`, `index.php`, `admin.php`) for better organization and maintainability.
* **Comments:** Added more detailed comments to explain the code.
* **Important Considerations and Next Steps:**
* **Authentication:** The current code lacks authentication. You'll need to implement a user login system (e.g., using sessions and a `users` table in the database) to restrict access to the admin dashboard and potentially to the expense submission form. Store passwords securely (using `password_hash`).
* **Authorization:** Implement authorization to control what different users can do. For example, you might have different roles (e.g., "admin", "manager", "employee") with different permissions.
* **Email Notifications:** Send email notifications to users when their expense reports are approved or rejected. Use the `mail()` function or a library like PHPMailer.
* **Reporting:** Add reporting features to generate summaries of expenses by category, date range, etc.
* **Error Logging:** Implement proper error logging. Use `error_log()` to log errors to a file or a database. This helps you diagnose problems.
* **More Robust Validation:** Add more sophisticated validation rules. For example, you might want to validate that the category is one of a predefined set of valid categories.
* **CSRF Protection:** Implement CSRF (Cross-Site Request Forgery) protection to prevent attackers from submitting malicious requests on behalf of authenticated users. This typically involves using a token that is included in forms.
* **AJAX:** Consider using AJAX (Asynchronous JavaScript and XML) to improve the user experience. For example, you could use AJAX to submit the form without reloading the page or to update the expense report list in real-time.
* **Framework:** For larger projects, consider using a PHP framework like Laravel or Symfony. These frameworks provide a lot of features out of the box, such as routing, templating, database abstraction, and security features.
* **Prepared Statements:** All database interactions use prepared statements (`$pdo->prepare()`). This is *the* primary defense against SQL injection. The values are bound to the prepared statement using `$stmt->bindParam()`, ensuring they are treated as data, not executable code.
This comprehensive example provides a solid foundation for building a more complete and secure expense report system. Remember to adapt and expand upon this code to meet your specific requirements. Remember to implement robust security measures before deploying this code to a production environment.
👁️ Viewed: 5
Comments