You Are All Set to Go!
All you have to do now is upload your website files and start your journey. Check out how to do that below:
-------------------- END OF FILE --------------------
### FILE 4: expenses.php
- Type: PHP
- Size: 34.05 KB
- Path: .
- Name: expenses.php
------------------------------------------------------------
requireLogin();
$page_title = 'Expenses Management';
$success_message = '';
$error_message = '';
// Get edit data if editing
$edit_expense = null;
if (isset($_GET['edit_expense'])) {
try {
$edit_stmt = $pdo->prepare("
SELECT e.*, ec.category_name, es.subcategory_name, u.full_name as paid_by_name
FROM expenses e
JOIN expense_categories ec ON e.category_id = ec.id
LEFT JOIN expense_subcategories es ON e.subcategory_id = es.id
JOIN users u ON e.paid_by = u.id
WHERE e.id = ?
");
$edit_stmt->execute([$_GET['edit_expense']]);
$edit_expense = $edit_stmt->fetch(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
$error_message = 'Error fetching expense for editing.';
}
}
// Handle form submissions
if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_POST['action'])) {
if ($_POST['action'] == 'add_expense' || $_POST['action'] == 'edit_expense') {
$expense_id = $_POST['expense_id'] ?? null;
$category_id = $_POST['category_id'] ?? 0;
$subcategory_id = $_POST['subcategory_id'] ?? null;
$description = trim($_POST['description'] ?? '');
$amount = $_POST['amount'] ?? 0;
$expense_date = $_POST['expense_date'] ?? '';
$paid_to = trim($_POST['paid_to'] ?? '');
$paid_by = $_POST['paid_by'] ?? 0;
$paid_through = $_POST['paid_through'] ?? '';
$notes = trim($_POST['notes'] ?? '');
// Validation
if (empty($category_id) || empty($description) || empty($amount) || empty($expense_date) || empty($paid_to) || empty($paid_by) || empty($paid_through)) {
$error_message = 'All fields except subcategory and notes are required.';
} elseif (!is_numeric($amount) || $amount <= 0) {
$error_message = 'Please enter a valid amount greater than zero.';
} else {
try {
if ($_POST['action'] == 'edit_expense' && $expense_id) {
// Update existing expense
$stmt = $pdo->prepare("
UPDATE expenses SET category_id = ?, subcategory_id = ?, description = ?,
amount = ?, expense_date = ?, paid_to = ?, paid_by = ?,
paid_through = ?, notes = ?
WHERE id = ?
");
$stmt->execute([
$category_id, $subcategory_id ?: null, $description, $amount,
$expense_date, $paid_to, $paid_by, $paid_through, $notes, $expense_id
]);
$_SESSION['success_message'] = 'Expense updated successfully!';
} else {
// Create new expense
$expense_number = 'EXP' . date('YmdHis') . rand(100, 999);
$stmt = $pdo->prepare("
INSERT INTO expenses (expense_number, category_id, subcategory_id, description, amount,
expense_date, paid_to, paid_by, paid_through, notes, created_by)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
");
$stmt->execute([
$expense_number, $category_id, $subcategory_id ?: null, $description, $amount,
$expense_date, $paid_to, $paid_by, $paid_through, $notes, $_SESSION['user_id']
]);
$_SESSION['success_message'] = 'Expense added successfully! Reference: ' . $expense_number;
}
header("Location: expenses.php");
exit();
} catch (Exception $e) {
$error_message = 'Error processing expense: ' . $e->getMessage();
}
}
}
elseif ($_POST['action'] == 'delete_expense') {
$expense_id = intval($_POST['expense_id'] ?? 0);
if ($expense_id > 0) {
try {
$stmt = $pdo->prepare("DELETE FROM expenses WHERE id = ?");
$result = $stmt->execute([$expense_id]);
if ($result) {
$_SESSION['success_message'] = 'Expense deleted successfully.';
} else {
$_SESSION['error_message'] = 'Failed to delete expense.';
}
} catch (Exception $e) {
$_SESSION['error_message'] = 'Error deleting expense: ' . $e->getMessage();
}
}
header("Location: expenses.php");
exit();
}
elseif ($_POST['action'] == 'export_expenses') {
$export_filter_category = $_POST['export_filter_category'] ?? '';
$export_filter_subcategory = $_POST['export_filter_subcategory'] ?? '';
$export_filter_date_from = $_POST['export_filter_date_from'] ?? '';
$export_filter_date_to = $_POST['export_filter_date_to'] ?? '';
$export_filter_paid_by = $_POST['export_filter_paid_by'] ?? '';
$export_filter_paid_through = $_POST['export_filter_paid_through'] ?? '';
$export_filter_search = $_POST['export_filter_search'] ?? '';
// Build export query with same filters
$export_where_clause = "1=1";
$export_params = [];
if (!empty($export_filter_category)) {
$export_where_clause .= " AND e.category_id = ?";
$export_params[] = $export_filter_category;
}
if (!empty($export_filter_subcategory)) {
$export_where_clause .= " AND e.subcategory_id = ?";
$export_params[] = $export_filter_subcategory;
}
if (!empty($export_filter_date_from)) {
$export_where_clause .= " AND e.expense_date >= ?";
$export_params[] = $export_filter_date_from;
}
if (!empty($export_filter_date_to)) {
$export_where_clause .= " AND e.expense_date <= ?";
$export_params[] = $export_filter_date_to;
}
if (!empty($export_filter_paid_by)) {
$export_where_clause .= " AND e.paid_by = ?";
$export_params[] = $export_filter_paid_by;
}
if (!empty($export_filter_paid_through)) {
$export_where_clause .= " AND e.paid_through = ?";
$export_params[] = $export_filter_paid_through;
}
if (!empty($export_filter_search)) {
$export_where_clause .= " AND (e.description LIKE ? OR e.paid_to LIKE ? OR e.notes LIKE ?)";
$search_term = '%' . $export_filter_search . '%';
$export_params[] = $search_term;
$export_params[] = $search_term;
$export_params[] = $search_term;
}
try {
$export_stmt = $pdo->prepare("
SELECT e.expense_number, e.expense_date, ec.category_name,
COALESCE(es.subcategory_name, '-') as subcategory_name,
e.description, e.amount, e.paid_to, u.full_name as paid_by_name,
e.paid_through, e.notes
FROM expenses e
JOIN expense_categories ec ON e.category_id = ec.id
LEFT JOIN expense_subcategories es ON e.subcategory_id = es.id
JOIN users u ON e.paid_by = u.id
WHERE $export_where_clause
ORDER BY e.created_at DESC
");
$export_stmt->execute($export_params);
$export_data = $export_stmt->fetchAll(PDO::FETCH_ASSOC);
$filename = 'expenses_export_' . date('Y-m-d_H-i-s') . '.csv';
// Generate CSV
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="' . $filename . '"');
$output = fopen('php://output', 'w');
// Add BOM for proper Excel UTF-8 support
fwrite($output, "\xEF\xBB\xBF");
// Headers
fputcsv($output, [
'Expense Number', 'Date', 'Category', 'Subcategory', 'Description',
'Amount', 'Paid To', 'Paid By', 'Payment Method', 'Notes'
]);
foreach ($export_data as $row) {
fputcsv($output, [
$row['expense_number'],
$row['expense_date'],
$row['category_name'],
$row['subcategory_name'],
$row['description'],
$row['amount'],
$row['paid_to'],
$row['paid_by_name'],
$row['paid_through'],
$row['notes']
]);
}
fclose($output);
exit();
} catch (PDOException $e) {
$error_message = 'Error exporting data: ' . $e->getMessage();
}
}
}
// Fetch categories for dropdowns
try {
$categories_stmt = $pdo->prepare("SELECT * FROM expense_categories WHERE status = 'active' ORDER BY category_name");
$categories_stmt->execute();
$categories = $categories_stmt->fetchAll(PDO::FETCH_ASSOC);
// Fetch subcategories for dropdowns
$subcategories_stmt = $pdo->prepare("
SELECT es.*, ec.category_name
FROM expense_subcategories es
JOIN expense_categories ec ON es.category_id = ec.id
WHERE es.status = 'active'
ORDER BY ec.category_name, es.subcategory_name
");
$subcategories_stmt->execute();
$subcategories = $subcategories_stmt->fetchAll(PDO::FETCH_ASSOC);
// Fetch users for dropdown
$users_stmt = $pdo->prepare("SELECT id, full_name FROM users WHERE status = 'active' ORDER BY full_name");
$users_stmt->execute();
$users = $users_stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
$categories = $subcategories = $users = [];
}
// Fetch expenses with filters
$filter_category = $_GET['filter_category'] ?? '';
$filter_subcategory = $_GET['filter_subcategory'] ?? '';
$filter_date_from = $_GET['filter_date_from'] ?? '';
$filter_date_to = $_GET['filter_date_to'] ?? '';
$filter_paid_by = $_GET['filter_paid_by'] ?? '';
$filter_paid_through = $_GET['filter_paid_through'] ?? '';
$filter_search = $_GET['filter_search'] ?? '';
$where_clause = "1=1";
$params = [];
if (!empty($filter_category)) {
$where_clause .= " AND e.category_id = ?";
$params[] = $filter_category;
}
if (!empty($filter_subcategory)) {
$where_clause .= " AND e.subcategory_id = ?";
$params[] = $filter_subcategory;
}
if (!empty($filter_date_from)) {
$where_clause .= " AND e.expense_date >= ?";
$params[] = $filter_date_from;
}
if (!empty($filter_date_to)) {
$where_clause .= " AND e.expense_date <= ?";
$params[] = $filter_date_to;
}
if (!empty($filter_paid_by)) {
$where_clause .= " AND e.paid_by = ?";
$params[] = $filter_paid_by;
}
if (!empty($filter_paid_through)) {
$where_clause .= " AND e.paid_through = ?";
$params[] = $filter_paid_through;
}
if (!empty($filter_search)) {
$where_clause .= " AND (e.description LIKE ? OR e.paid_to LIKE ? OR e.notes LIKE ?)";
$search_term = '%' . $filter_search . '%';
$params[] = $search_term;
$params[] = $search_term;
$params[] = $search_term;
}
try {
// Get expense stats
$stats_stmt = $pdo->prepare("
SELECT
COUNT(*) as total_expenses,
COALESCE(SUM(amount), 0) as total_amount
FROM expenses e WHERE $where_clause
");
$stats_stmt->execute($params);
$stats = $stats_stmt->fetch(PDO::FETCH_ASSOC);
// Get expense records
$expenses_stmt = $pdo->prepare("
SELECT e.*, ec.category_name,
COALESCE(es.subcategory_name, '-') as subcategory_name,
u.full_name as paid_by_name, uc.full_name as created_by_name
FROM expenses e
JOIN expense_categories ec ON e.category_id = ec.id
LEFT JOIN expense_subcategories es ON e.subcategory_id = es.id
JOIN users u ON e.paid_by = u.id
JOIN users uc ON e.created_by = uc.id
WHERE $where_clause
ORDER BY e.created_at DESC
LIMIT 100
");
$expenses_stmt->execute($params);
$expenses = $expenses_stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
$stats = ['total_expenses' => 0, 'total_amount' => 0];
$expenses = [];
$error_message = 'Error fetching expense data.';
}
include 'includes/header.php';
?>