please in this codes (<?php // public/reports/generatereports.php // Now include the…
- User
please in this codes (<?php // public/reports/generate_reports.php // Now include the header after handling actions require_once DIR . '/../../includes/header.php';
// Enable error reporting for debugging (disable in production) ini_set('display_errors', 1); ini_set('display_startup_errors', 1); error_reporting(E_ALL);
// Include authentication and database connection require_once '../../includes/auth.php'; require_once '../../includes/db_connect.php';
// Initialize success and error variables error = '';
// Initialize filter variables filter_date_to = ''; $filter_status = '';
// Initialize conditions and parameters for prepared statements params = []; $types = '';
// Handle GET request for filtering if (_SERVER['REQUEST_METHOD'] == 'GET') { if (!empty(_GET['filter_date_from'])) { _GET['filter_date_from']); params[] = types .= 's'; } if (!empty(_GET['filter_date_to'])) { filter_date_to = trim(conditions[] = "DATE(orders.order_date) <= ?"; filter_date_to; types .= 's'; } if (!empty(_GET['filter_status'])) { _GET['filter_status']); params[] = types .= 's'; } }
conditions) > 0) { conditions); }
// Prepare the SQL statement with filters for the Orders Table where ORDER BY orders.order_date DESC";
conn->prepare(stmt) { if (count(params) > 0) { stmt->bind_param(params); }
if ($stmt->execute()) { $orders_result = $stmt->get_result(); if (!$orders_result) { $error = "Failed to retrieve orders."; } } else { $error = "Failed to execute orders query."; }} else { $error = "Failed to prepare orders query."; }
// Handle Export to CSV if (isset(_GET['export'])) { // Re-execute the query without pagination for export export_sql = "SELECT orders.*, customers.name as customer_name, products.name as product_name FROM orders JOIN customers ON orders.customer_id = customers.id JOIN products ON orders.product_id = products.id export_stmt = export_sql); if (export_stmt) { if (count(params) > 0) { types, ...$params); }
if ($export_stmt->execute()) { $export_result = $export_stmt->get_result(); if ($export_result) { // Set headers for CSV download header('Content-Type: text/csv; charset=utf-8'); header('Content-Disposition: attachment; filename=orders_report.csv'); $output = fopen('php://output', 'w'); // Column headings fputcsv($output, ['Order ID', 'Customer Name', 'Product', 'Quantity', 'Packaging Preferences', 'Status', 'Order Date']); // Data rows if ($export_result->num_rows > 0) { while ($row = $export_result->fetch_assoc()) { fputcsv($output, [ $row['id'], $row['customer_name'], $row['product_name'], $row['quantity'], $row['packaging_preferences'], $row['status'], $row['order_date'] ]); } } fclose($output); exit(); } else { $error = "Failed to retrieve export data."; } } else { $error = "Failed to execute export query."; } } else { $error = "Failed to prepare export query."; }}
// Fetch data for Charts
// 1. Orders by Status where GROUP BY status"; conn->prepare(status_stmt) { if (count(params) > 0) { status_stmt->bind_param(params); }
if ($status_stmt->execute()) { $status_result = $status_stmt->get_result(); $statuses = []; $status_counts = []; if ($status_result && $status_result->num_rows > 0) { while ($row = $status_result->fetch_assoc()) { $statuses[] = $row['status']; $status_counts[] = (int)$row['count']; } } } else { $error .= " Failed to execute orders by status query."; }} else { $error .= " Failed to prepare orders by status query."; }
// 2. Orders Over Time (Monthly) where GROUP BY month ORDER BY month ASC "; conn->prepare(orders_over_time_stmt) { if (count(params) > 0) { orders_over_time_stmt->bind_param(params); }
if ($orders_over_time_stmt->execute()) { $orders_over_time_result = $orders_over_time_stmt->get_result(); $months = []; $orders_counts = []; // Fetch all results while ($row = $orders_over_time_result->fetch_assoc()) { $months[] = date("M Y", strtotime($row['month'] . "-01")); $orders_counts[] = (int)$row['count']; } // Ensure that all months in the last 12 months are represented $all_months = []; $all_counts = []; for ($i = 11; $i >= 0; $i--) { $month = date('Y-m', strtotime("-$i months")); $formatted_month = date("M Y", strtotime($month . "-01")); if (in_array($formatted_month, $months)) { $index = array_search($formatted_month, $months); $all_months[] = $formatted_month; $all_counts[] = $orders_counts[$index]; } else { $all_months[] = $formatted_month; $all_counts[] = 0; } } $months = $all_months; $orders_counts = $all_counts; } else { $error .= " Failed to execute orders over time query."; }} else { $error .= " Failed to prepare orders over time query."; }
// 3. Top 5 Products Sales where GROUP BY products.name ORDER BY total_sales DESC LIMIT 5 "; conn->prepare(top_products_stmt) { if (count(params) > 0) { top_products_stmt->bind_param(params); }
if ($top_products_stmt->execute()) { $top_products_result = $top_products_stmt->get_result(); $top_products = []; $top_sales = []; if ($top_products_result && $top_products_result->num_rows > 0) { while ($row = $top_products_result->fetch_assoc()) { $top_products[] = $row['name']; $top_sales[] = (float)$row['total_sales']; } } } else { $error .= " Failed to execute top products sales query."; }} else { $error .= " Failed to prepare top products sales query."; }
?>
<!-- Tailwind CSS --> <script src="https://cdn.tailwindcss.com"></script> <!-- Chart.js --> <script src="https://cdn.jsdelivr.net/npm/chart.js"></script> <!-- Google Fonts --> <link href="https://fonts.googleapis.com/css2?family=Cairo:wght@400;600;700&display=swap" rel="stylesheet"> <!-- Custom Styles --> <style> body { font-family: 'Cairo', sans-serif; } </style><!-- Charts Section --> <section class="mb-8"> <div class="bg-white p-6 rounded-lg shadow-md"> <h2 class="text-xl font-semibold mb-4">Sales and Orders Overview</h2> <div class="grid grid-cols-1 lg:grid-cols-3 gap-6"> <!-- Orders by Status Pie Chart --> <div class="bg-gray-50 p-4 rounded-lg shadow"> <h3 class="text-lg font-medium mb-2">Orders by Status</h3> <canvas id="ordersStatusChart"></canvas> </div> <!-- Orders Over Time Line Chart --> <div class="bg-gray-50 p-4 rounded-lg shadow"> <h3 class="text-lg font-medium mb-2">Orders Over Time</h3> <canvas id="ordersOverTimeChart"></canvas> </div> <!-- Top 5 Products Sales Bar Chart --> <div class="bg-gray-50 p-4 rounded-lg shadow"> <h3 class="text-lg font-medium mb-2">Top 5 Products by Sales</h3> <canvas id="topProductsChart"></canvas> </div> </div> </div> </section> <!-- Filters Section --> <section class="mb-8"> <div class="bg-white p-6 rounded-lg shadow-md"> <h2 class="text-xl font-semibold mb-4">Filter Reports</h2> <form method="GET" action="" class="grid grid-cols-1 md:grid-cols-4 gap-4"> <!-- Filter by Date From --> <div> <label for="filter_date_from" class="block text-sm font-medium text-gray-700">Date From</label> <input type="date" id="filter_date_from" name="filter_date_from" class="mt-1 block w-full border border-gray-300 rounded-md shadow-sm p-2 focus:ring-blue-500 focus:border-blue-500" value="<?= htmlspecialchars($filter_date_from) ?>"> </div> <!-- Filter by Date To --> <div> <label for="filter_date_to" class="block text-sm font-medium text-gray-700">Date To</label> <input type="date" id="filter_date_to" name="filter_date_to" class="mt-1 block w-full border border-gray-300 rounded-md shadow-sm p-2 focus:ring-blue-500 focus:border-blue-500" value="<?= htmlspecialchars($filter_date_to) ?>"> </div> <!-- Filter by Status --> <div> <label for="filter_status" class="block text-sm font-medium text-gray-700">Status</label> <select id="filter_status" name="filter_status" class="mt-1 block w-full border border-gray-300 rounded-md shadow-sm p-2 focus:ring-blue-500 focus:border-blue-500"> <option value="">All</option> <option value="Pending" <?= ($filter_status == 'Pending') ? 'selected' : '' ?>>Pending</option> <option value="In Progress" <?= ($filter_status == 'In Progress') ? 'selected' : '' ?>>In Progress</option> <option value="Completed" <?= ($filter_status == 'Completed') ? 'selected' : '' ?>>Completed</option> <option value="Delivered" <?= ($filter_status == 'Delivered') ? 'selected' : '' ?>>Delivered</option> </select> </div> <!-- Action Buttons --> <div class="flex items-end space-x-4"> <button type="submit" class="w-full bg-blue-600 text-white px-4 py-2 rounded-md shadow hover:bg-blue-700">Filter</button> <a href="generate_reports.php?export=1<?= (!empty($filter_date_from)) ? '&filter_date_from=' . urlencode($filter_date_from) : '' ?><?= (!empty($filter_date_to)) ? '&filter_date_to=' . urlencode($filter_date_to) : '' ?><?= (!empty($filter_status)) ? '&filter_status=' . urlencode($filter_status) : '' ?>" class="w-full bg-green-600 text-white px-4 py-2 rounded-md shadow hover:bg-green-700 text-center">Export CSV</a> </div> </form> </div> </section> <!-- Display Success or Error Messages --> <?php if (!empty($success)): ?> <div class="bg-green-100 border border-green-400 text-green-700 px-4 py-3 rounded relative mb-6" role="alert"> <span class="block sm:inline"><?= htmlspecialchars($success) ?></span> <span class="absolute top-0 bottom-0 right-0 px-4 py-3"> <svg class="fill-current h-6 w-6 text-green-500" role="button" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 20 20"> <title>Close</title> <path d="M14.348 5.652a1 1 0 00-1.414 0L10 8.586 7.066 5.652a1 1 0 10-1.414 1.414L8.586 10l-2.934 2.934a1 1 0 101.414 1.414L10 11.414l2.934 2.934a1 1 0 001.414-1.414L11.414 10l2.934-2.934a1 1 0 000-1.414z"/> </svg> </span> </div> <?php endif; ?> <?php if (!empty($error)): ?> <div class="bg-red-100 border border-red-400 text-red-700 px-4 py-3 rounded relative mb-6" role="alert"> <span class="block sm:inline"><?= htmlspecialchars($error) ?></span> <span class="absolute top-0 bottom-0 right-0 px-4 py-3"> <svg class="fill-current h-6 w-6 text-red-500" role="button" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 20 20"> <title>Close</title> <path d="M14.348 5.652a1 1 0 00-1.414 0L10 8.586 7.066 5.652a1 1 0 10-1.414 1.414L8.586 10l-2.934 2.934a1 1 0 101.414 1.414L10 11.414l2.934 2.934a1 1 0 001.414-1.414L11.414 10l2.934-2.934a1 1 0 000-1.414z"/> </svg> </span> </div> <?php endif; ?> <!-- Reports Table --> <section> <div class="bg-white p-6 rounded-lg shadow-md"> <h2 class="text-xl font-semibold mb-4">Orders Report</h2> <div class="overflow-x-auto"> <table class="min-w-full bg-white"> <thead class="bg-gray-800 text-white"> <tr> <th class="w-1/12 text-left py-3 px-4 uppercase font-semibold text-sm">Order ID</th> <th class="w-2/12 text-left py-3 px-4 uppercase font-semibold text-sm">Customer Name</th> <th class="w-2/12 text-left py-3 px-4 uppercase font-semibold text-sm">Product</th> <th class="w-1/12 text-left py-3 px-4 uppercase font-semibold text-sm">Quantity</th> <th class="w-2/12 text-left py-3 px-4 uppercase font-semibold text-sm">Packaging Preferences</th> <th class="w-2/12 text-left py-3 px-4 uppercase font-semibold text-sm">Status</th> <th class="w-3/12 text-left py-3 px-4 uppercase font-semibold text-sm">Order Date</th> </tr> </thead> <tbody class="text-gray-700"> <?php if ($orders_result && $orders_result->num_rows > 0): ?> <?php while($order = $orders_result->fetch_assoc()): ?> <tr class="border-b"> <td class="py-3 px-4"><?= htmlspecialchars($order['id']) ?></td> <td class="py-3 px-4"><?= htmlspecialchars($order['customer_name']) ?></td> <td class="py-3 px-4"><?= htmlspecialchars($order['product_name']) ?></td> <td class="py-3 px-4"><?= htmlspecialchars($order['quantity']) ?></td> <td class="py-3 px-4"><?= htmlspecialchars($order['packaging_preferences']) ?></td> <td class="py-3 px-4"> <?php $status = htmlspecialchars($order['status']); $badge = ''; switch ($status) { case 'Pending': $badge = 'bg-yellow-400 text-white'; break; case 'In Progress': $badge = 'bg-green-500 text-white'; break; case 'Completed': $badge = 'bg-blue-500 text-white'; break; case 'Delivered': $badge = 'bg-red-500 text-white'; break; default: $badge = 'bg-gray-500 text-white'; } ?> <span class="px-2 py-1 <?= $badge ?> rounded-full text-xs font-semibold"><?= $status ?></span> </td> <td class="py-3 px-4"><?= date("d M Y, h:i A", strtotime($order['order_date'])) ?></td> </tr> <?php endwhile; ?> <?php else: ?> <tr> <td colspan="7" class="text-center py-4">No orders found for the selected criteria.</td> </tr> <?php endif; ?> </tbody> </table> </div> </div> </section> </main> <!-- Footer --> <footer class="bg-gray-800 text-white py-4 mt-8 rounded-lg shadow-md"> <p class="text-center">© 2024 Kaak Alia Bakery. All rights reserved.</p> </footer> <!-- Chart.js Initialization --> <script> document.addEventListener('DOMContentLoaded', function () { // 1. Orders by Status Pie Chart const ordersStatusCtx = document.getElementById('ordersStatusChart').getContext('2d'); const ordersStatusChart = new Chart(ordersStatusCtx, { type: 'pie', data: { labels: <?= json_encode($statuses) ?>, datasets: [{ data: <?= json_encode($status_counts) ?>, backgroundColor: [ '#F59E0B', // Yellow-400 '#10B981', // Green-500 '#3B82F6', // Blue-500 '#EF4444', // Red-500 '#6B7280' // Gray-500 for unknown ], borderColor: '#FFFFFF', borderWidth: 2 }] }, options: { responsive: true, plugins: { legend: { position: 'bottom', labels: { color: '#6B7280', font: { size: 14 } } }, title: { display: false } } } }); // 2. Orders Over Time Line Chart const ordersOverTimeCtx = document.getElementById('ordersOverTimeChart').getContext('2d'); const ordersOverTimeChart = new Chart(ordersOverTimeCtx, { type: 'line', data: { labels: <?= json_encode($months) ?>, datasets: [{ label: 'Number of Orders', data: <?= json_encode($orders_counts) ?>, fill: false, borderColor: '#3B82F6', // Blue-500 backgroundColor: '#3B82F6', tension: 0.3, pointBackgroundColor: '#3B82F6', pointBorderColor: '#FFFFFF', pointHoverBackgroundColor: '#FFFFFF', pointHoverBorderColor: '#3B82F6', }] }, options: { responsive: true, plugins: { legend: { display: false }, title: { display: false }, tooltip: { callbacks: { label: function(context) { return ` ${context.parsed.y} Orders`; } } } }, scales: { y: { beginAtZero: true, ticks: { color: '#6B7280', stepSize: 1 }, grid: { color: '#E5E7EB' } }, x: { ticks: { color: '#6B7280' }, grid: { color: '#E5E7EB' } } } } }); // 3. Top 5 Products Sales Bar Chart const topProductsCtx = document.getElementById('topProductsChart').getContext('2d'); const topProductsChart = new Chart(topProductsCtx, { type: 'bar', data: { labels: <?= json_encode($top_products) ?>, datasets: [{ label: 'Total Sales (₱)', data: <?= json_encode($top_sales) ?>, backgroundColor: '#10B981', // Green-500 borderColor: '#10B981', borderWidth: 1 }] }, options: { responsive: true, plugins: { legend: { display: false }, title: { display: false }, tooltip: { callbacks: { label: function(context) { return ` ₱${context.parsed.y.toLocaleString()}`; } } } }, scales: { y: { beginAtZero: true, ticks: { color: '#6B7280', callback: function(value) { return '₱' + value.toLocaleString(); } }, grid: { color: '#E5E7EB' } }, x: { ticks: { color: '#6B7280' }, grid: { color: '#E5E7EB' } } } } }); }); </script>Warning: Cannot modify header information - headers already sent by (output started at /home/u406552082/domains/longevacare.com/public_html/kaakalia/includes/header.php:90) in /home/u406552082/domains/longevacare.com/public_html/kaakalia/public/reports/generate_reports.php on line 102 "Order ID","Customer Name",Product,Quantity,"Packaging Preferences",Status,"Order Date" 29,order_84317,"Special Occasion Box - Kaak",2,0,Pending,"2024-11-08 15:44:34" 28,order_1496,"Kaak - Regular Box Half-kilo",1,0,Pending,"2024-11-08 15:12:14" 27,order_1496,"Special Occasion Box - Kaak",1,,Completed,"2024-11-08 11:34:14" 26,order_8496,"Special Occasion Box",1,,Pending,"2024-11-08 11:31:21" 25,order_1496,"Savory Cake",1,"This Order is so Important and type card, (Mahmoud Tawfik)",Pending,"2024-11-08 11:27:17" 24,order_94513,"Kaak - Regular Box One-kilo",1,,Pending,"2024-11-08 11:17:02" 23,order_94513,"Kaak - Regular Box One-kilo",1,,Pending,"2024-11-08 11:17:00" 22,order_94513,"Kaak - Regular Box One-kilo",1,,Pending,"2024-11-08 11:16:51" 21,order_14513,"Special Occasion Box",1,"This Order is so Important and type card, (Mahmoud Tawfik)",Pending,"2024-11-08 11:01:58" 20,order_1496,"Special Occasion Box",1,"This Order is so Important and type card, (Mahmoud Tawfik)",Pending,"2024-11-08 09:24:01" 19,order_1496,"Visitor’s Basket - Large",1,,Pending,"2024-11-08 09:18:40" 18,order_02496,"Kaak - Regular Box Half-kilo",1,,Pending,"2024-11-08 09:17:18" 17,order_12496,"Special Occasion Box - Kaak",1,,Pending,"2024-11-08 09:16:01" 16,order_8496,"Special Occasion Box - Kaak",1,"This Order is so Important and type card, (Mahmoud Tawfik)",Pending,"2024-11-08 08:36:30" 15,order_18113,"Special Occasion Box - Kaak",1,"This Order is so Important and type card, (Mahmoud Tawfik)",Pending,"2024-11-08 08:34:48" 14,order_1296,"Special Occasion Box - Kaak",1,"This Order is so Important and type card, (Mahmoud Tawfik)",Pending,"2024-11-08 08:18:54" 13,order_12123,"Visitor’s Basket - Large",1,"This Order is so Important and type card, (Mahmoud Tawfik)",Completed,"2024-11-07 18:41:47" 8,order_14313,"Visitor’s Basket - Large",1,"This Order is so Important and type card, (Mahmoud Tawfik)",Pending,"2024-11-07 18:36:59" 7,order_2196,"Special Occasion Box",1,"This Order is so Important and type card, (Mahmoud Tawfik)",Pending,"2024-11-07 18:09:05" 6,order_3496,"Special Occasion Box",1,"This Order is so Important and type card, (Mahmoud Tawfik)",Pending,"2024-11-07 18:00:07" 5,order_12113,"Visitor’s Basket - Small",1,"This Order is so Important and type card, (Mahmoud Tawfik)",Delivered,"2024-11-07 17:47:39" 4,order_8496,"Visitor’s Basket - Large",2,"This Order is so Important and type card, (Mahmoud Tawfik)",Completed,"2024-11-06 18:00:08" 3,order_94513,"Kaak - Regular Box One-kilo",1,"This Order is so Important and type card, (Mahmoud Tawfik)",Completed,"2024-11-06 16:29:21") so fix it and give me all info and type full codes and this info databsae (-- phpMyAdmin SQL Dump -- version 5.2.1 -- https://www.phpmyadmin.net/
-- Host: 127.0.0.1:3306 -- Generation Time: Nov 08, 2024 at 03:05 PM -- Server version: 10.11.9-MariaDB -- PHP Version: 7.2.34
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; START TRANSACTION; SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /; /!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /; /!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /; /!40101 SET NAMES utf8mb4 */;
-- -- Database:
u406552082_kaakalia
-- -- Table structure for table
customersCREATE TABLE
customers(idint(11) NOT NULL,namevarchar(100) NOT NULL,contactvarchar(50) DEFAULT NULL,delivery_addresstext DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- -- Table structure for table
ordersCREATE TABLE
orders(idint(11) NOT NULL,customer_idint(11) NOT NULL,product_idint(11) NOT NULL,statusenum('Pending','In Progress','Completed','Delivered') DEFAULT 'Pending',order_datedatetime DEFAULT current_timestamp(),delivery_datetimedatetime DEFAULT NULL,delivery_datedate DEFAULT NULL,quantityint(11) NOT NULL DEFAULT 1,packaging_preferencesvarchar(255) DEFAULT NULL,discount_amountdecimal(10,2) DEFAULT 0.00,discount_reasonvarchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- -- Table structure for table
order_itemsCREATE TABLE
order_items(idint(11) NOT NULL,order_idint(11) NOT NULL,product_idint(11) NOT NULL,quantityint(11) NOT NULL,packaging_preferencesvarchar(255) DEFAULT NULL,discount_amountdecimal(10,2) DEFAULT 0.00,discount_reasonvarchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- -- Table structure for table
productsCREATE TABLE
products(idint(11) NOT NULL,namevarchar(100) NOT NULL,sizevarchar(50) DEFAULT NULL,pricedecimal(10,2) NOT NULL,categoryvarchar(50) DEFAULT NULL,thumbnailvarchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- -- Table structure for table
usersCREATE TABLE
users(idint(11) NOT NULL,usernamevarchar(50) NOT NULL,passwordvarchar(255) NOT NULL,roleenum('admin','staff') NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;-- -- Indexes for dumped tables
-- -- Indexes for table
customersALTER TABLE
customersADD PRIMARY KEY (id);-- -- Indexes for table
ordersALTER TABLE
ordersADD PRIMARY KEY (id), ADD KEYcustomer_id(customer_id);-- -- Indexes for table
order_itemsALTER TABLE
order_itemsADD PRIMARY KEY (id), ADD KEYorder_id(order_id), ADD KEYproduct_id(product_id);-- -- Indexes for table
productsALTER TABLE
productsADD PRIMARY KEY (id);-- -- Indexes for table
usersALTER TABLE
usersADD PRIMARY KEY (id), ADD UNIQUE KEYusername(username);-- -- AUTO_INCREMENT for dumped tables
-- -- AUTO_INCREMENT for table
customersALTER TABLE
customersMODIFYidint(11) NOT NULL AUTO_INCREMENT;-- -- AUTO_INCREMENT for table
ordersALTER TABLE
ordersMODIFYidint(11) NOT NULL AUTO_INCREMENT;-- -- AUTO_INCREMENT for table
order_itemsALTER TABLE
order_itemsMODIFYidint(11) NOT NULL AUTO_INCREMENT;-- -- AUTO_INCREMENT for table
productsALTER TABLE
productsMODIFYidint(11) NOT NULL AUTO_INCREMENT;-- -- AUTO_INCREMENT for table
usersALTER TABLE
usersMODIFYidint(11) NOT NULL AUTO_INCREMENT;-- -- Constraints for dumped tables
-- -- Constraints for table
ordersALTER TABLE
ordersADD CONSTRAINTorders_ibfk_1FOREIGN KEY (customer_id) REFERENCEScustomers(id), ADD CONSTRAINTorders_ibfk_2FOREIGN KEY (product_id) REFERENCESproducts(id);-- -- Constraints for table
order_itemsALTER TABLE
order_itemsADD CONSTRAINTorder_items_ibfk_1FOREIGN KEY (order_id) REFERENCESorders(id) ON DELETE CASCADE, ADD CONSTRAINTorder_items_ibfk_2FOREIGN KEY (product_id) REFERENCESproducts(id) ON DELETE CASCADE; COMMIT;/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /; /!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /; /!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;)
- Assistant
The issue you're encountering stems from the fact that HTTP headers must be sent before any actual output is generated. In your current setup, the
header.phpfile is included before handling the CSV export. Ifheader.phpor any of the included files (likeauth.phpordb_connect.php) output anything (even a single whitespace), PHP will start sending the response to the browser. Once output starts, you cannot modify HTTP headers, which is why you're seeing the warnings:Warning: Cannot modify header information - headers already sent by (output started at /path/to/header.php:90) in /path/to/generate_reports.php on line 101To resolve this, you need to handle the CSV export before any output is sent to the browser. This means moving the CSV export logic to the very beginning of your script, before including any other files that might produce output.
Below is the corrected and complete
generate_reports.phpfile with the necessary modifications:<?php // public/reports/generate_reports.php // Enable error reporting for debugging (disable in production) ini_set('display_errors', 1); ini_set('display_startup_errors', 1); error_reporting(E_ALL); // Include authentication and database connection require_once '../../includes/auth.php'; require_once '../../includes/db_connect.php'; // Initialize success and error variables $success = ''; $error = ''; // Initialize filter variables $filter_date_from = ''; $filter_date_to = ''; $filter_status = ''; // Initialize conditions and parameters for prepared statements $conditions = []; $params = []; $types = ''; // Handle GET request for filtering if ($_SERVER['REQUEST_METHOD'] == 'GET') { if (!empty($_GET['filter_date_from'])) { $filter_date_from = trim($_GET['filter_date_from']); $conditions[] = "DATE(orders.order_date) >= ?"; $params[] = $filter_date_from; $types .= 's'; } if (!empty($_GET['filter_date_to'])) { $filter_date_to = trim($_GET['filter_date_to']); $conditions[] = "DATE(orders.order_date) <= ?"; $params[] = $filter_date_to; $types .= 's'; } if (!empty($_GET['filter_status'])) { $filter_status = trim($_GET['filter_status']); $conditions[] = "orders.status = ?"; $params[] = $filter_status; $types .= 's'; } } $where = ''; if (count($conditions) > 0) { $where = "WHERE " . implode(" AND ", $conditions); } // **Handle Export to CSV Before Any Output** if (isset($_GET['export'])) { // Prepare the SQL statement with filters for export $export_sql = "SELECT orders.*, customers.name as customer_name, products.name as product_name FROM orders JOIN customers ON orders.customer_id = customers.id JOIN products ON orders.product_id = products.id $where ORDER BY orders.order_date DESC"; $export_stmt = $conn->prepare($export_sql); if ($export_stmt) { if (count($params) > 0) { $export_stmt->bind_param($types, ...$params); } if ($export_stmt->execute()) { $export_result = $export_stmt->get_result(); if ($export_result) { // Set headers for CSV download header('Content-Type: text/csv; charset=utf-8'); header('Content-Disposition: attachment; filename=orders_report.csv'); $output = fopen('php://output', 'w'); // Column headings fputcsv($output, ['Order ID', 'Customer Name', 'Product', 'Quantity', 'Packaging Preferences', 'Status', 'Order Date']); // Data rows if ($export_result->num_rows > 0) { while ($row = $export_result->fetch_assoc()) { fputcsv($output, [ $row['id'], $row['customer_name'], $row['product_name'], $row['quantity'], $row['packaging_preferences'], $row['status'], $row['order_date'] ]); } } fclose($output); exit(); // Ensure no further output is sent } else { $error = "Failed to retrieve export data."; } } else { $error = "Failed to execute export query."; } } else { $error = "Failed to prepare export query."; } } // Now include the header after handling export require_once __DIR__ . '/../../includes/header.php'; // Prepare the SQL statement with filters for the Orders Table $orders_sql = "SELECT orders.*, customers.name as customer_name, products.name as product_name FROM orders JOIN customers ON orders.customer_id = customers.id JOIN products ON orders.product_id = products.id $where ORDER BY orders.order_date DESC"; $stmt = $conn->prepare($orders_sql); if ($stmt) { if (count($params) > 0) { $stmt->bind_param($types, ...$params); } if ($stmt->execute()) { $orders_result = $stmt->get_result(); if (!$orders_result) { $error = "Failed to retrieve orders."; } } else { $error = "Failed to execute orders query."; } } else { $error = "Failed to prepare orders query."; } // Fetch data for Charts // 1. Orders by Status $status_sql = "SELECT status, COUNT(*) as count FROM orders $where GROUP BY status"; $status_stmt = $conn->prepare($status_sql); if ($status_stmt) { if (count($params) > 0) { $status_stmt->bind_param($types, ...$params); } if ($status_stmt->execute()) { $status_result = $status_stmt->get_result(); $statuses = []; $status_counts = []; if ($status_result && $status_result->num_rows > 0) { while ($row = $status_result->fetch_assoc()) { $statuses[] = $row['status']; $status_counts[] = (int)$row['count']; } } } else { $error .= " Failed to execute orders by status query."; } } else { $error .= " Failed to prepare orders by status query."; } // 2. Orders Over Time (Monthly) $orders_over_time_sql = " SELECT DATE_FORMAT(order_date, '%Y-%m') as month, COUNT(*) as count FROM orders $where GROUP BY month ORDER BY month ASC "; $orders_over_time_stmt = $conn->prepare($orders_over_time_sql); if ($orders_over_time_stmt) { if (count($params) > 0) { $orders_over_time_stmt->bind_param($types, ...$params); } if ($orders_over_time_stmt->execute()) { $orders_over_time_result = $orders_over_time_stmt->get_result(); $months = []; $orders_counts = []; // Fetch all results while ($row = $orders_over_time_result->fetch_assoc()) { $months[] = date("M Y", strtotime($row['month'] . "-01")); $orders_counts[] = (int)$row['count']; } // Ensure that all months in the last 12 months are represented $all_months = []; $all_counts = []; for ($i = 11; $i >= 0; $i--) { $month = date('Y-m', strtotime("-$i months")); $formatted_month = date("M Y", strtotime($month . "-01")); if (in_array($formatted_month, $months)) { $index = array_search($formatted_month, $months); $all_months[] = $formatted_month; $all_counts[] = $orders_counts[$index]; } else { $all_months[] = $formatted_month; $all_counts[] = 0; } } $months = $all_months; $orders_counts = $all_counts; } else { $error .= " Failed to execute orders over time query."; } } else { $error .= " Failed to prepare orders over time query."; } // 3. Top 5 Products Sales $top_products_sql = " SELECT products.name, SUM(orders.quantity * products.price - IFNULL(orders.discount_amount, 0)) as total_sales FROM orders JOIN products ON orders.product_id = products.id $where GROUP BY products.name ORDER BY total_sales DESC LIMIT 5 "; $top_products_stmt = $conn->prepare($top_products_sql); if ($top_products_stmt) { if (count($params) > 0) { $top_products_stmt->bind_param($types, ...$params); } if ($top_products_stmt->execute()) { $top_products_result = $top_products_stmt->get_result(); $top_products = []; $top_sales = []; if ($top_products_result && $top_products_result->num_rows > 0) { while ($row = $top_products_result->fetch_assoc()) { $top_products[] = $row['name']; $top_sales[] = (float)$row['total_sales']; } } } else { $error .= " Failed to execute top products sales query."; } } else { $error .= " Failed to prepare top products sales query."; } ?> <!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>Generate Reports - Kaak Alia Management System</title> <!-- Tailwind CSS --> <script src="https://cdn.tailwindcss.com"></script> <!-- Chart.js --> <script src="https://cdn.jsdelivr.net/npm/chart.js"></script> <!-- Google Fonts --> <link href="https://fonts.googleapis.com/css2?family=Cairo:wght@400;600;700&display=swap" rel="stylesheet"> <!-- Custom Styles --> <style> body { font-family: 'Cairo', sans-serif; } </style> </head> <body class="bg-gradient-to-b from-gray-100 to-blue-50 min-h-screen"> <main class="container mx-auto p-4"> <!-- Header Content --> <header class="bg-gray-800 text-white py-4 rounded-lg shadow-md mb-6"> <h1 class="text-2xl font-bold text-center">Kaak Alia Management System</h1> </header> <!-- Charts Section --> <section class="mb-8"> <div class="bg-white p-6 rounded-lg shadow-md"> <h2 class="text-xl font-semibold mb-4">Sales and Orders Overview</h2> <div class="grid grid-cols-1 lg:grid-cols-3 gap-6"> <!-- Orders by Status Pie Chart --> <div class="bg-gray-50 p-4 rounded-lg shadow"> <h3 class="text-lg font-medium mb-2">Orders by Status</h3> <canvas id="ordersStatusChart"></canvas> </div> <!-- Orders Over Time Line Chart --> <div class="bg-gray-50 p-4 rounded-lg shadow"> <h3 class="text-lg font-medium mb-2">Orders Over Time</h3> <canvas id="ordersOverTimeChart"></canvas> </div> <!-- Top 5 Products Sales Bar Chart --> <div class="bg-gray-50 p-4 rounded-lg shadow"> <h3 class="text-lg font-medium mb-2">Top 5 Products by Sales</h3> <canvas id="topProductsChart"></canvas> </div> </div> </div> </section> <!-- Filters Section --> <section class="mb-8"> <div class="bg-white p-6 rounded-lg shadow-md"> <h2 class="text-xl font-semibold mb-4">Filter Reports</h2> <form method="GET" action="" class="grid grid-cols-1 md:grid-cols-4 gap-4"> <!-- Filter by Date From --> <div> <label for="filter_date_from" class="block text-sm font-medium text-gray-700">Date From</label> <input type="date" id="filter_date_from" name="filter_date_from" class="mt-1 block w-full border border-gray-300 rounded-md shadow-sm p-2 focus:ring-blue-500 focus:border-blue-500" value="<?= htmlspecialchars($filter_date_from) ?>"> </div> <!-- Filter by Date To --> <div> <label for="filter_date_to" class="block text-sm font-medium text-gray-700">Date To</label> <input type="date" id="filter_date_to" name="filter_date_to" class="mt-1 block w-full border border-gray-300 rounded-md shadow-sm p-2 focus:ring-blue-500 focus:border-blue-500" value="<?= htmlspecialchars($filter_date_to) ?>"> </div> <!-- Filter by Status --> <div> <label for="filter_status" class="block text-sm font-medium text-gray-700">Status</label> <select id="filter_status" name="filter_status" class="mt-1 block w-full border border-gray-300 rounded-md shadow-sm p-2 focus:ring-blue-500 focus:border-blue-500"> <option value="">All</option> <option value="Pending" <?= ($filter_status == 'Pending') ? 'selected' : '' ?>>Pending</option> <option value="In Progress" <?= ($filter_status == 'In Progress') ? 'selected' : '' ?>>In Progress</option> <option value="Completed" <?= ($filter_status == 'Completed') ? 'selected' : '' ?>>Completed</option> <option value="Delivered" <?= ($filter_status == 'Delivered') ? 'selected' : '' ?>>Delivered</option> </select> </div> <!-- Action Buttons --> <div class="flex items-end space-x-4"> <button type="submit" class="w-full bg-blue-600 text-white px-4 py-2 rounded-md shadow hover:bg-blue-700">Filter</button> <a href="generate_reports.php?export=1<?= (!empty($filter_date_from)) ? '&filter_date_from=' . urlencode($filter_date_from) : '' ?><?= (!empty($filter_date_to)) ? '&filter_date_to=' . urlencode($filter_date_to) : '' ?><?= (!empty($filter_status)) ? '&filter_status=' . urlencode($filter_status) : '' ?>" class="w-full bg-green-600 text-white px-4 py-2 rounded-md shadow hover:bg-green-700 text-center">Export CSV</a> </div> </form> </div> </section> <!-- Display Success or Error Messages --> <?php if (!empty($success)): ?> <div class="bg-green-100 border border-green-400 text-green-700 px-4 py-3 rounded relative mb-6" role="alert"> <span class="block sm:inline"><?= htmlspecialchars($success) ?></span> <span class="absolute top-0 bottom-0 right-0 px-4 py-3"> <svg class="fill-current h-6 w-6 text-green-500" role="button" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 20 20"> <title>Close</title> <path d="M14.348 5.652a1 1 0 00-1.414 0L10 8.586 7.066 5.652a1 1 0 10-1.414 1.414L8.586 10l-2.934 2.934a1 1 0 101.414 1.414L10 11.414l2.934 2.934a1 1 0 001.414-1.414L11.414 10l2.934-2.934a1 1 0 000-1.414z"/> </svg> </span> </div> <?php endif; ?> <?php if (!empty($error)): ?> <div class="bg-red-100 border border-red-400 text-red-700 px-4 py-3 rounded relative mb-6" role="alert"> <span class="block sm:inline"><?= htmlspecialchars($error) ?></span> <span class="absolute top-0 bottom-0 right-0 px-4 py-3"> <svg class="fill-current h-6 w-6 text-red-500" role="button" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 20 20"> <title>Close</title> <path d="M14.348 5.652a1 1 0 00-1.414 0L10 8.586 7.066 5.652a1 1 0 10-1.414 1.414L8.586 10l-2.934 2.934a1 1 0 101.414 1.414L10 11.414l2.934 2.934a1 1 0 001.414-1.414L11.414 10l2.934-2.934a1 1 0 000-1.414z"/> </svg> </span> </div> <?php endif; ?> <!-- Reports Table --> <section> <div class="bg-white p-6 rounded-lg shadow-md"> <h2 class="text-xl font-semibold mb-4">Orders Report</h2> <div class="overflow-x-auto"> <table class="min-w-full bg-white"> <thead class="bg-gray-800 text-white"> <tr> <th class="w-1/12 text-left py-3 px-4 uppercase font-semibold text-sm">Order ID</th> <th class="w-2/12 text-left py-3 px-4 uppercase font-semibold text-sm">Customer Name</th> <th class="w-2/12 text-left py-3 px-4 uppercase font-semibold text-sm">Product</th> <th class="w-1/12 text-left py-3 px-4 uppercase font-semibold text-sm">Quantity</th> <th class="w-2/12 text-left py-3 px-4 uppercase font-semibold text-sm">Packaging Preferences</th> <th class="w-2/12 text-left py-3 px-4 uppercase font-semibold text-sm">Status</th> <th class="w-3/12 text-left py-3 px-4 uppercase font-semibold text-sm">Order Date</th> </tr> </thead> <tbody class="text-gray-700"> <?php if ($orders_result && $orders_result->num_rows > 0): ?> <?php while($order = $orders_result->fetch_assoc()): ?> <tr class="border-b"> <td class="py-3 px-4"><?= htmlspecialchars($order['id']) ?></td> <td class="py-3 px-4"><?= htmlspecialchars($order['customer_name']) ?></td> <td class="py-3 px-4"><?= htmlspecialchars($order['product_name']) ?></td> <td class="py-3 px-4"><?= htmlspecialchars($order['quantity']) ?></td> <td class="py-3 px-4"><?= htmlspecialchars($order['packaging_preferences']) ?></td> <td class="py-3 px-4"> <?php $status = htmlspecialchars($order['status']); $badge = ''; switch ($status) { case 'Pending': $badge = 'bg-yellow-400 text-white'; break; case 'In Progress': $badge = 'bg-green-500 text-white'; break; case 'Completed': $badge = 'bg-blue-500 text-white'; break; case 'Delivered': $badge = 'bg-red-500 text-white'; break; default: $badge = 'bg-gray-500 text-white'; } ?> <span class="px-2 py-1 <?= $badge ?> rounded-full text-xs font-semibold"><?= $status ?></span> </td> <td class="py-3 px-4"><?= date("d M Y, h:i A", strtotime($order['order_date'])) ?></td> </tr> <?php endwhile; ?> <?php else: ?> <tr> <td colspan="7" class="text-center py-4">No orders found for the selected criteria.</td> </tr> <?php endif; ?> </tbody> </table> </div> </div> </section> </main> <!-- Footer --> <footer class="bg-gray-800 text-white py-4 mt-8 rounded-lg shadow-md"> <p class="text-center">© 2024 Kaak Alia Bakery. All rights reserved.</p> </footer> <!-- Chart.js Initialization --> <script> document.addEventListener('DOMContentLoaded', function () { // 1. Orders by Status Pie Chart const ordersStatusCtx = document.getElementById('ordersStatusChart').getContext('2d'); const ordersStatusChart = new Chart(ordersStatusCtx, { type: 'pie', data: { labels: <?= json_encode($statuses) ?>, datasets: [{ data: <?= json_encode($status_counts) ?>, backgroundColor: [ '#F59E0B', // Yellow-400 '#10B981', // Green-500 '#3B82F6', // Blue-500 '#EF4444', // Red-500 '#6B7280' // Gray-500 for unknown ], borderColor: '#FFFFFF', borderWidth: 2 }] }, options: { responsive: true, plugins: { legend: { position: 'bottom', labels: { color: '#6B7280', font: { size: 14 } } }, title: { display: false } } } }); // 2. Orders Over Time Line Chart const ordersOverTimeCtx = document.getElementById('ordersOverTimeChart').getContext('2d'); const ordersOverTimeChart = new Chart(ordersOverTimeCtx, { type: 'line', data: { labels: <?= json_encode($months) ?>, datasets: [{ label: 'Number of Orders', data: <?= json_encode($orders_counts) ?>, fill: false, borderColor: '#3B82F6', // Blue-500 backgroundColor: '#3B82F6', tension: 0.3, pointBackgroundColor: '#3B82F6', pointBorderColor: '#FFFFFF', pointHoverBackgroundColor: '#FFFFFF', pointHoverBorderColor: '#3B82F6', }] }, options: { responsive: true, plugins: { legend: { display: false }, title: { display: false }, tooltip: { callbacks: { label: function(context) { return ` ${context.parsed.y} Orders`; } } } }, scales: { y: { beginAtZero: true, ticks: { color: '#6B7280', stepSize: 1 }, grid: { color: '#E5E7EB' } }, x: { ticks: { color: '#6B7280' }, grid: { color: '#E5E7EB' } } } } }); // 3. Top 5 Products Sales Bar Chart const topProductsCtx = document.getElementById('topProductsChart').getContext('2d'); const topProductsChart = new Chart(topProductsCtx, { type: 'bar', data: { labels: <?= json_encode($top_products) ?>, datasets: [{ label: 'Total Sales (₱)', data: <?= json_encode($top_sales) ?>, backgroundColor: '#10B981', // Green-500 borderColor: '#10B981', borderWidth: 1 }] }, options: { responsive: true, plugins: { legend: { display: false }, title: { display: false }, tooltip: { callbacks: { label: function(context) { return ` ₱${context.parsed.y.toLocaleString()}`; } } } }, scales: { y: { beginAtZero: true, ticks: { color: '#6B7280', callback: function(value) { return '₱' + value.toLocaleString(); } }, grid: { color: '#E5E7EB' } }, x: { ticks: { color: '#6B7280' }, grid: { color: '#E5E7EB' } } } } }); }); </script> </body> </html>Key Changes and Explanations:
-
Export Handling Moved to the Top:
- Before including any other files (
header.php, etc.), the script now checks if theexportparameter is set in the GET request. - If
export=1is detected, it executes the export logic immediately, sends the CSV headers, outputs the CSV data, and then callsexit()to stop further script execution. This ensures that no other output interferes with the headers required for the CSV download.
- Before including any other files (
-
Including
header.phpAfter Export Handling:- Since
header.phpmight produce output (e.g., HTML markup, whitespace), it's crucial to only include it after ensuring that no CSV export is requested. By moving the export logic above therequire_once __DIR__ . '/../../includes/header.php';line, you prevent any accidental output from occurring before the CSV headers are sent.
- Since
-
Consistent CSV Formatting:
- Consistency in Quotes: Ensure that all CSV fields are properly quoted, especially if they contain commas or special characters. The
fputcsv()function handles this automatically, so no additional quotes are necessary in the header definition. - Correct Column Headers: The headers in the CSV are now consistently formatted without unnecessary quotes:
fputcsv($output, ['Order ID', 'Customer Name', 'Product', 'Quantity', 'Packaging Preferences', 'Status', 'Order Date']);
- Consistency in Quotes: Ensure that all CSV fields are properly quoted, especially if they contain commas or special characters. The
-
Ensuring No Extra Output:
- The
exit();function call after the CSV export ensures that the script stops executing, preventing any further output that might interfere with the CSV file download.
- The
-
Security and Data Sanitization:
htmlspecialcharsis used when outputting data to prevent XSS attacks. This is especially important if any of the data can be manipulated by users.
-
Database Schema:
- Ensure that your database schema matches the fields used in the SQL queries. From the provided SQL dump, the tables
customers,orders,order_items,products, andusersare correctly set up with the necessary fields.
- Ensure that your database schema matches the fields used in the SQL queries. From the provided SQL dump, the tables
Additional Recommendations:
-
Separate Export Logic (Optional):
- For better separation of concerns, consider creating a separate PHP file dedicated to handling CSV exports (e.g.,
export_orders.php). This file would only contain the export logic and can be linked directly from your UI. This approach minimizes the risk of accidental output and makes the codebase cleaner.
- For better separation of concerns, consider creating a separate PHP file dedicated to handling CSV exports (e.g.,
-
Output Buffering (Alternative Solution):
- As an alternative to moving the export logic, you can use PHP's output buffering functions to control when output is sent to the browser. However, this is generally not recommended for this use case because handling the export before any output is more straightforward and less error-prone.
-
Error Handling Enhancements:
- Implement more robust error handling mechanisms, such as logging errors to a file instead of displaying them to the user, especially in a production environment. This prevents sensitive information from being exposed.
-
Use Prepared Statements Correctly:
- Ensure that all SQL queries use prepared statements to protect against SQL injection attacks. Your current implementation already uses prepared statements, which is good practice.
-
Checking for Null Values:
- In your CSV export, ensure that
packaging_preferencesand other nullable fields are handled correctly to avoid issues with missing data.
- In your CSV export, ensure that
By implementing these changes, your CSV export functionality should work correctly without encountering the "headers already sent" warning. This ensures a smooth user experience when exporting reports.
-