in this database (-- phpMyAdmin SQL Dump -- version 5.2.1 -- https://www.phpmyadmin.net/…
- User
in this database (-- phpMyAdmin SQL Dump -- version 5.2.1 -- https://www.phpmyadmin.net/
-- Host: 127.0.0.1:3306 -- Generation Time: Nov 10, 2024 at 02:45 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
adsCREATE TABLE
ads(idint(11) NOT NULL,user_idint(11) NOT NULL,namevarchar(255) NOT NULL,ad_typeenum('Post','Story') NOT NULL,related_idint(11) NOT NULL,budgetdecimal(10,2) NOT NULL,total_spentdecimal(10,2) DEFAULT 0.00,start_datedate NOT NULL,end_datedate NOT NULL,statusenum('Active','Paused','Completed') DEFAULT 'Active',created_atdatetime DEFAULT current_timestamp(),updated_atdatetime DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- -- Table structure for table
ad_spend_logsCREATE TABLE
ad_spend_logs(idint(11) NOT NULL,ad_idint(11) NOT NULL,amountdecimal(10,2) NOT NULL,spend_datedatetime DEFAULT current_timestamp(),descriptionvarchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- -- 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
driversCREATE TABLE
drivers(idint(11) NOT NULL,namevarchar(100) NOT NULL,numbervarchar(15) NOT 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,statusenum('Pending','In Progress','Completed','Delivered') DEFAULT 'Pending',priorityenum('Normal','Important','Quick') DEFAULT 'Normal',order_datedatetime DEFAULT current_timestamp(),delivery_datetimedatetime DEFAULT NULL,delivery_datedate 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 DEFAULT 1,packaging_preferencesvarchar(255) DEFAULT NULL,discount_percentagedecimal(5,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;
-- -- Table structure for table
workspace_notesCREATE TABLE
workspace_notes(idint(11) NOT NULL,user_idint(11) NOT NULL,titlevarchar(255) NOT NULL,contenttext NOT NULL,created_atdatetime DEFAULT current_timestamp(),updated_atdatetime DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- -- Table structure for table
workspace_plansCREATE TABLE
workspace_plans(idint(11) NOT NULL,user_idint(11) NOT NULL,titlevarchar(255) NOT NULL,descriptiontext DEFAULT NULL,due_datedatetime DEFAULT NULL,statusenum('Not Started','In Progress','Completed') DEFAULT 'Not Started',created_atdatetime DEFAULT current_timestamp(),updated_atdatetime DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- -- Table structure for table
workspace_postsCREATE TABLE
workspace_posts(idint(11) NOT NULL,user_idint(11) NOT NULL,platformenum('Instagram','Facebook','Twitter','Other') NOT NULL,contenttext NOT NULL,media_urlvarchar(255) DEFAULT NULL,scheduled_datetimedatetime DEFAULT NULL,statusenum('Scheduled','Posted','Failed') DEFAULT 'Scheduled',created_atdatetime DEFAULT current_timestamp(),updated_atdatetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),typeenum('Post','Story') DEFAULT 'Post' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;-- -- Indexes for dumped tables
-- -- Indexes for table
adsALTER TABLE
adsADD PRIMARY KEY (id), ADD KEYuser_id(user_id), ADD KEYrelated_id(related_id);-- -- Indexes for table
ad_spend_logsALTER TABLE
ad_spend_logsADD PRIMARY KEY (id), ADD KEYad_id(ad_id);-- -- Indexes for table
customersALTER TABLE
customersADD PRIMARY KEY (id);-- -- Indexes for table
driversALTER TABLE
driversADD PRIMARY KEY (id);-- -- Indexes for table
ordersALTER TABLE
ordersADD PRIMARY KEY (id), ADD KEYcustomer_id(customer_id), ADD KEYidx_orders_customer_id(customer_id);-- -- Indexes for table
order_itemsALTER TABLE
order_itemsADD PRIMARY KEY (id), ADD KEYorder_id(order_id), ADD KEYproduct_id(product_id), ADD KEYidx_order_items_order_id(order_id), ADD KEYidx_order_items_product_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);-- -- Indexes for table
workspace_notesALTER TABLE
workspace_notesADD PRIMARY KEY (id), ADD KEYuser_id(user_id);-- -- Indexes for table
workspace_plansALTER TABLE
workspace_plansADD PRIMARY KEY (id), ADD KEYuser_id(user_id);-- -- Indexes for table
workspace_postsALTER TABLE
workspace_postsADD PRIMARY KEY (id), ADD KEYuser_id(user_id);-- -- AUTO_INCREMENT for dumped tables
-- -- AUTO_INCREMENT for table
adsALTER TABLE
adsMODIFYidint(11) NOT NULL AUTO_INCREMENT;-- -- AUTO_INCREMENT for table
ad_spend_logsALTER TABLE
ad_spend_logsMODIFYidint(11) NOT NULL AUTO_INCREMENT;-- -- AUTO_INCREMENT for table
customersALTER TABLE
customersMODIFYidint(11) NOT NULL AUTO_INCREMENT;-- -- AUTO_INCREMENT for table
driversALTER TABLE
driversMODIFYidint(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;-- -- AUTO_INCREMENT for table
workspace_notesALTER TABLE
workspace_notesMODIFYidint(11) NOT NULL AUTO_INCREMENT;-- -- AUTO_INCREMENT for table
workspace_plansALTER TABLE
workspace_plansMODIFYidint(11) NOT NULL AUTO_INCREMENT;-- -- AUTO_INCREMENT for table
workspace_postsALTER TABLE
workspace_postsMODIFYidint(11) NOT NULL AUTO_INCREMENT;-- -- Constraints for dumped tables
-- -- Constraints for table
adsALTER TABLE
adsADD CONSTRAINTfk_ads_related_idFOREIGN KEY (related_id) REFERENCESworkspace_posts(id) ON DELETE CASCADE, ADD CONSTRAINTfk_ads_user_idFOREIGN KEY (user_id) REFERENCESusers(id) ON DELETE CASCADE;-- -- Constraints for table
ad_spend_logsALTER TABLE
ad_spend_logsADD CONSTRAINTfk_ad_spend_logs_ad_idFOREIGN KEY (ad_id) REFERENCESads(id) ON DELETE CASCADE;-- -- Constraints for table
ordersALTER TABLE
ordersADD CONSTRAINTfk_orders_customer_idFOREIGN KEY (customer_id) REFERENCEScustomers(id) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINTorders_ibfk_1FOREIGN KEY (customer_id) REFERENCEScustomers(id) ON DELETE CASCADE;-- -- Constraints for table
order_itemsALTER TABLE
order_itemsADD CONSTRAINTfk_order_items_order_idFOREIGN KEY (order_id) REFERENCESorders(id) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINTfk_order_items_product_idFOREIGN KEY (product_id) REFERENCESproducts(id) ON DELETE CASCADE ON UPDATE CASCADE, ADD 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;-- -- Constraints for table
workspace_notesALTER TABLE
workspace_notesADD CONSTRAINTfk_workspace_notes_user_idFOREIGN KEY (user_id) REFERENCESusers(id) ON DELETE CASCADE;-- -- Constraints for table
workspace_plansALTER TABLE
workspace_plansADD CONSTRAINTfk_workspace_plans_user_idFOREIGN KEY (user_id) REFERENCESusers(id) ON DELETE CASCADE;-- -- Constraints for table
workspace_postsALTER TABLE
workspace_postsADD CONSTRAINTfk_workspace_posts_user_idFOREIGN KEY (user_id) REFERENCESusers(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 */;), i want to update this codes (<?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 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); }
// Function to sanitize CSV data function sanitize_csv(data) { return mb_convert_encoding(data, 'UTF-8', 'UTF-8'); }
// Handle Export to CSV Before Any Output if (isset(_GET['export'])) { // Prepare the SQL statement with filters for export export_sql = "SELECT orders.id AS order_id, orders.status, orders.order_date, orders.delivery_datetime, orders.delivery_date, order_items.quantity, order_items.packaging_preferences, order_items.discount_percentage, order_items.discount_reason, customers.name AS customer_name, customers.contact AS customer_contact, customers.delivery_address, products.name AS product_name, products.size AS product_size, products.category AS product_category, products.price AS product_price FROM orders JOIN customers ON orders.customer_id = customers.id JOIN order_items ON orders.id = order_items.order_id JOIN products ON order_items.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 with UTF-8 BOM for Arabic support header('Content-Type: text/csv; charset=UTF-8'); header('Content-Disposition: attachment; filename=orders_report.csv'); // Open the output stream $output = fopen('php://output', 'w'); // Write UTF-8 BOM fprintf($output, chr(0xEF) . chr(0xBB) . chr(0xBF)); // Column headings including the new columns fputcsv($output, [ 'Order ID', 'Status', 'Order Date', 'Delivery Date & Time', 'Delivery Date', 'Quantity', 'Packaging Preferences', 'Discount Percentage (%)', 'Discount Amount (KD)', 'Discount Reason', 'Subtotal (KD)', 'Total Amount (KD)', 'Payment Status', // New Column 'Total Paid Amount (KD)', // New Column 'Customer Name', 'Customer Contact', 'Delivery Address', 'Product Name', 'Product Size', 'Product Category', 'Product Price (KD)' ]); // Initialize summary variables $total_orders = 0; $total_paid_amount = 0.00; // Data rows if ($export_result->num_rows > 0) { while ($row = $export_result->fetch_assoc()) { // Calculate Subtotal and Total Amount $subtotal = $row['quantity'] * $row['product_price']; $discount_amount = ($subtotal * $row['discount_percentage']) / 100; $total_amount = $subtotal - $discount_amount; // Determine Payment Status $payment_status = ($row['status'] === 'Delivered') ? 'Paid' : 'Unpaid'; // Calculate Total Paid Amount (only if Paid) $total_paid = ($payment_status === 'Paid') ? $total_amount : '0.00'; // Update summary if paid if ($payment_status === 'Paid') { $total_paid_amount += $total_amount; } // Format dates for better readability $order_date = date("d/m/Y H:i", strtotime($row['order_date'])); $delivery_datetime = $row['delivery_datetime'] ? date("d/m/Y H:i", strtotime($row['delivery_datetime'])) : 'N/A'; $delivery_date = $row['delivery_date'] ? date("d/m/Y", strtotime($row['delivery_date'])) : 'N/A'; fputcsv($output, [ sanitize_csv($row['order_id']), sanitize_csv($row['status']), sanitize_csv($order_date), sanitize_csv($delivery_datetime), sanitize_csv($delivery_date), sanitize_csv($row['quantity']), sanitize_csv($row['packaging_preferences'] ?? 'N/A'), sanitize_csv(number_format((float)$row['discount_percentage'], 2)), sanitize_csv(number_format((float)$discount_amount, 2)), sanitize_csv($row['discount_reason'] ?? 'N/A'), sanitize_csv(number_format((float)$subtotal, 2)), sanitize_csv(number_format((float)$total_amount, 2)), sanitize_csv($payment_status), // New Column sanitize_csv(number_format((float)$total_paid, 2)), // New Column sanitize_csv($row['customer_name']), sanitize_csv($row['customer_contact'] ?? 'N/A'), sanitize_csv($row['delivery_address'] ?? 'N/A'), sanitize_csv($row['product_name']), sanitize_csv($row['product_size'] ?? 'N/A'), sanitize_csv($row['product_category'] ?? 'N/A'), sanitize_csv(number_format((float)$row['product_price'], 2)) ]); } // Add a blank row before summary fputcsv($output, []); // Summary section fputcsv($output, ['Total Orders', $total_orders]); fputcsv($output, ['Total Paid Amount (KD)', number_format($total_paid_amount, 2)]); } 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 to prevent "headers already sent" issues require_once DIR . '/../../includes/header.php';
// 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."; }
// 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['product_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 flex-col space-y-4 justify-end"> <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['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", 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 (KD)', 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 ` KD${context.parsed.y.toLocaleString()}`; } } } }, scales: { y: { beginAtZero: true, ticks: { color: '#6B7280', callback: function(value) { return 'KD ' + value.toLocaleString(); } }, grid: { color: '#E5E7EB' } }, x: { ticks: { color: '#6B7280' }, grid: { color: '#E5E7EB' } } } } }); }); </script> - Assistant
Certainly! Below is the complete updated PHP code for
generate_reports.phpthat allows exporting all relevant data (orders,workspace_plans,workspace_posts,workspace_notes, andads) into separate CSV files. Each export option is accessible via its own export button on the web page. Additionally, the export functionality is designed to handle filters where applicable.<?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); } // Function to sanitize CSV data function sanitize_csv($data) { return mb_convert_encoding($data, 'UTF-8', 'UTF-8'); } // **Handle Export to CSV Before Any Output** if (isset($_GET['export'])) { // Get the table to export $export = $_GET['export']; // Switch based on export type switch ($export) { case 'orders': // Prepare the SQL statement with filters for export $export_sql = "SELECT orders.id AS order_id, orders.status, orders.order_date, orders.delivery_datetime, orders.delivery_date, order_items.quantity, order_items.packaging_preferences, order_items.discount_percentage, order_items.discount_reason, customers.name AS customer_name, customers.contact AS customer_contact, customers.delivery_address, products.name AS product_name, products.size AS product_size, products.category AS product_category, products.price AS product_price FROM orders JOIN customers ON orders.customer_id = customers.id JOIN order_items ON orders.id = order_items.order_id JOIN products ON order_items.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 with UTF-8 BOM for Arabic support header('Content-Type: text/csv; charset=UTF-8'); header('Content-Disposition: attachment; filename=orders_report.csv'); // Open the output stream $output = fopen('php://output', 'w'); // Write UTF-8 BOM fprintf($output, chr(0xEF) . chr(0xBB) . chr(0xBF)); // Column headings including the new columns fputcsv($output, [ 'Order ID', 'Status', 'Order Date', 'Delivery Date & Time', 'Delivery Date', 'Quantity', 'Packaging Preferences', 'Discount Percentage (%)', 'Discount Amount (KD)', 'Discount Reason', 'Subtotal (KD)', 'Total Amount (KD)', 'Payment Status', 'Total Paid Amount (KD)', 'Customer Name', 'Customer Contact', 'Delivery Address', 'Product Name', 'Product Size', 'Product Category', 'Product Price (KD)' ]); // Initialize summary variables $total_orders = 0; $total_paid_amount = 0.00; // Data rows if ($export_result->num_rows > 0) { while ($row = $export_result->fetch_assoc()) { // Calculate Subtotal and Total Amount $subtotal = $row['quantity'] * $row['product_price']; $discount_amount = ($subtotal * $row['discount_percentage']) / 100; $total_amount = $subtotal - $discount_amount; // Determine Payment Status $payment_status = ($row['status'] === 'Delivered') ? 'Paid' : 'Unpaid'; // Calculate Total Paid Amount (only if Paid) $total_paid = ($payment_status === 'Paid') ? $total_amount : '0.00'; // Update summary if paid if ($payment_status === 'Paid') { $total_paid_amount += $total_amount; $total_orders++; } // Format dates for better readability $order_date = date("d/m/Y H:i", strtotime($row['order_date'])); $delivery_datetime = $row['delivery_datetime'] ? date("d/m/Y H:i", strtotime($row['delivery_datetime'])) : 'N/A'; $delivery_date = $row['delivery_date'] ? date("d/m/Y", strtotime($row['delivery_date'])) : 'N/A'; fputcsv($output, [ sanitize_csv($row['order_id']), sanitize_csv($row['status']), sanitize_csv($order_date), sanitize_csv($delivery_datetime), sanitize_csv($delivery_date), sanitize_csv($row['quantity']), sanitize_csv($row['packaging_preferences'] ?? 'N/A'), sanitize_csv(number_format((float)$row['discount_percentage'], 2)), sanitize_csv(number_format((float)$discount_amount, 2)), sanitize_csv($row['discount_reason'] ?? 'N/A'), sanitize_csv(number_format((float)$subtotal, 2)), sanitize_csv(number_format((float)$total_amount, 2)), sanitize_csv($payment_status), sanitize_csv(number_format((float)$total_paid, 2)), sanitize_csv($row['customer_name']), sanitize_csv($row['customer_contact'] ?? 'N/A'), sanitize_csv($row['delivery_address'] ?? 'N/A'), sanitize_csv($row['product_name']), sanitize_csv($row['product_size'] ?? 'N/A'), sanitize_csv($row['product_category'] ?? 'N/A'), sanitize_csv(number_format((float)$row['product_price'], 2)) ]); } // Add a blank row before summary fputcsv($output, []); // Summary section fputcsv($output, ['Total Paid Orders', $total_orders]); fputcsv($output, ['Total Paid Amount (KD)', number_format($total_paid_amount, 2)]); } 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."; } break; case 'plans': // Export workspace_plans $plans_sql = "SELECT workspace_plans.id AS plan_id, workspace_plans.user_id, users.username, workspace_plans.title, workspace_plans.description, workspace_plans.due_date, workspace_plans.status, workspace_plans.created_at, workspace_plans.updated_at FROM workspace_plans JOIN users ON workspace_plans.user_id = users.id $where ORDER BY workspace_plans.due_date DESC"; $plans_stmt = $conn->prepare($plans_sql); if ($plans_stmt) { if (count($params) > 0) { $plans_stmt->bind_param($types, ...$params); } if ($plans_stmt->execute()) { $plans_result = $plans_stmt->get_result(); if ($plans_result) { // Set headers for CSV download header('Content-Type: text/csv; charset=UTF-8'); header('Content-Disposition: attachment; filename=plans_report.csv'); // Open the output stream $output = fopen('php://output', 'w'); // Write UTF-8 BOM fprintf($output, chr(0xEF) . chr(0xBB) . chr(0xBF)); // Column headings fputcsv($output, [ 'Plan ID', 'User ID', 'Username', 'Title', 'Description', 'Due Date', 'Status', 'Created At', 'Updated At' ]); // Data rows if ($plans_result->num_rows > 0) { while ($row = $plans_result->fetch_assoc()) { fputcsv($output, [ sanitize_csv($row['plan_id']), sanitize_csv($row['user_id']), sanitize_csv($row['username']), sanitize_csv($row['title']), sanitize_csv($row['description'] ?? 'N/A'), sanitize_csv($row['due_date'] ? date("d/m/Y", strtotime($row['due_date'])) : 'N/A'), sanitize_csv($row['status']), sanitize_csv(date("d/m/Y H:i", strtotime($row['created_at']))), sanitize_csv(date("d/m/Y H:i", strtotime($row['updated_at']))) ]); } } fclose($output); exit(); // Ensure no further output is sent } else { $error = "Failed to retrieve plans export data."; } } else { $error = "Failed to execute plans export query."; } } else { $error = "Failed to prepare plans export query."; } break; case 'posts': // Export workspace_posts $posts_sql = "SELECT workspace_posts.id AS post_id, workspace_posts.user_id, users.username, workspace_posts.platform, workspace_posts.content, workspace_posts.media_url, workspace_posts.scheduled_datetime, workspace_posts.status, workspace_posts.created_at, workspace_posts.updated_at, workspace_posts.type FROM workspace_posts JOIN users ON workspace_posts.user_id = users.id $where ORDER BY workspace_posts.scheduled_datetime DESC"; $posts_stmt = $conn->prepare($posts_sql); if ($posts_stmt) { if (count($params) > 0) { $posts_stmt->bind_param($types, ...$params); } if ($posts_stmt->execute()) { $posts_result = $posts_stmt->get_result(); if ($posts_result) { // Set headers for CSV download header('Content-Type: text/csv; charset=UTF-8'); header('Content-Disposition: attachment; filename=posts_report.csv'); // Open the output stream $output = fopen('php://output', 'w'); // Write UTF-8 BOM fprintf($output, chr(0xEF) . chr(0xBB) . chr(0xBF)); // Column headings fputcsv($output, [ 'Post ID', 'User ID', 'Username', 'Platform', 'Content', 'Media URL', 'Scheduled Date & Time', 'Status', 'Created At', 'Updated At', 'Type' ]); // Data rows if ($posts_result->num_rows > 0) { while ($row = $posts_result->fetch_assoc()) { fputcsv($output, [ sanitize_csv($row['post_id']), sanitize_csv($row['user_id']), sanitize_csv($row['username']), sanitize_csv($row['platform']), sanitize_csv($row['content'] ?? 'N/A'), sanitize_csv($row['media_url'] ?? 'N/A'), sanitize_csv($row['scheduled_datetime'] ? date("d/m/Y H:i", strtotime($row['scheduled_datetime'])) : 'N/A'), sanitize_csv($row['status']), sanitize_csv(date("d/m/Y H:i", strtotime($row['created_at']))), sanitize_csv(date("d/m/Y H:i", strtotime($row['updated_at']))), sanitize_csv($row['type']) ]); } } fclose($output); exit(); // Ensure no further output is sent } else { $error = "Failed to retrieve posts export data."; } } else { $error = "Failed to execute posts export query."; } } else { $error = "Failed to prepare posts export query."; } break; case 'notes': // Export workspace_notes $notes_sql = "SELECT workspace_notes.id AS note_id, workspace_notes.user_id, users.username, workspace_notes.title, workspace_notes.content, workspace_notes.created_at, workspace_notes.updated_at FROM workspace_notes JOIN users ON workspace_notes.user_id = users.id $where ORDER BY workspace_notes.created_at DESC"; $notes_stmt = $conn->prepare($notes_sql); if ($notes_stmt) { if (count($params) > 0) { $notes_stmt->bind_param($types, ...$params); } if ($notes_stmt->execute()) { $notes_result = $notes_stmt->get_result(); if ($notes_result) { // Set headers for CSV download header('Content-Type: text/csv; charset=UTF-8'); header('Content-Disposition: attachment; filename=notes_report.csv'); // Open the output stream $output = fopen('php://output', 'w'); // Write UTF-8 BOM fprintf($output, chr(0xEF) . chr(0xBB) . chr(0xBF)); // Column headings fputcsv($output, [ 'Note ID', 'User ID', 'Username', 'Title', 'Content', 'Created At', 'Updated At' ]); // Data rows if ($notes_result->num_rows > 0) { while ($row = $notes_result->fetch_assoc()) { fputcsv($output, [ sanitize_csv($row['note_id']), sanitize_csv($row['user_id']), sanitize_csv($row['username']), sanitize_csv($row['title']), sanitize_csv($row['content'] ?? 'N/A'), sanitize_csv(date("d/m/Y H:i", strtotime($row['created_at']))), sanitize_csv(date("d/m/Y H:i", strtotime($row['updated_at']))) ]); } } fclose($output); exit(); // Ensure no further output is sent } else { $error = "Failed to retrieve notes export data."; } } else { $error = "Failed to execute notes export query."; } } else { $error = "Failed to prepare notes export query."; } break; case 'ads': // Export ads $ads_sql = "SELECT ads.id AS ad_id, ads.user_id, users.username, ads.name, ads.ad_type, ads.related_id, workspace_posts.content AS related_post_content, ads.budget, ads.total_spent, ads.start_date, ads.end_date, ads.status, ads.created_at, ads.updated_at FROM ads JOIN users ON ads.user_id = users.id LEFT JOIN workspace_posts ON ads.related_id = workspace_posts.id $where ORDER BY ads.created_at DESC"; $ads_stmt = $conn->prepare($ads_sql); if ($ads_stmt) { if (count($params) > 0) { $ads_stmt->bind_param($types, ...$params); } if ($ads_stmt->execute()) { $ads_result = $ads_stmt->get_result(); if ($ads_result) { // Set headers for CSV download header('Content-Type: text/csv; charset=UTF-8'); header('Content-Disposition: attachment; filename=ads_report.csv'); // Open the output stream $output = fopen('php://output', 'w'); // Write UTF-8 BOM fprintf($output, chr(0xEF) . chr(0xBB) . chr(0xBF)); // Column headings fputcsv($output, [ 'Ad ID', 'User ID', 'Username', 'Name', 'Ad Type', 'Related ID', 'Related Post Content', 'Budget (KD)', 'Total Spent (KD)', 'Start Date', 'End Date', 'Status', 'Created At', 'Updated At' ]); // Data rows if ($ads_result->num_rows > 0) { while ($row = $ads_result->fetch_assoc()) { fputcsv($output, [ sanitize_csv($row['ad_id']), sanitize_csv($row['user_id']), sanitize_csv($row['username']), sanitize_csv($row['name']), sanitize_csv($row['ad_type']), sanitize_csv($row['related_id']), sanitize_csv($row['related_post_content'] ?? 'N/A'), sanitize_csv(number_format((float)$row['budget'], 2)), sanitize_csv(number_format((float)$row['total_spent'], 2)), sanitize_csv($row['start_date'] ? date("d/m/Y", strtotime($row['start_date'])) : 'N/A'), sanitize_csv($row['end_date'] ? date("d/m/Y", strtotime($row['end_date'])) : 'N/A'), sanitize_csv($row['status']), sanitize_csv(date("d/m/Y H:i", strtotime($row['created_at']))), sanitize_csv(date("d/m/Y H:i", strtotime($row['updated_at']))) ]); } } fclose($output); exit(); // Ensure no further output is sent } else { $error = "Failed to retrieve ads export data."; } } else { $error = "Failed to execute ads export query."; } } else { $error = "Failed to prepare ads export query."; } break; default: $error = "Invalid export type selected."; break; } } // Now include the header after handling export to prevent "headers already sent" issues require_once __DIR__ . '/../../includes/header.php'; // Prepare the SQL statement with filters for the Orders Table $orders_sql = "SELECT orders.id AS order_id, orders.status, orders.order_date, orders.delivery_datetime, orders.delivery_date, order_items.quantity, order_items.packaging_preferences, order_items.discount_percentage, order_items.discount_reason, customers.name AS customer_name, products.name AS product_name, products.price AS product_price FROM orders JOIN customers ON orders.customer_id = customers.id JOIN order_items ON orders.id = order_items.order_id JOIN products ON order_items.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 // Initialize variables for charts $statuses = []; $status_counts = []; $months = []; $orders_counts = []; $top_products = []; $top_sales = []; // 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(); 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(orders.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(); 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 AS product_name, SUM(order_items.quantity * products.price - (order_items.quantity * products.price * IFNULL(order_items.discount_percentage, 0)/100)) as total_sales FROM orders JOIN order_items ON orders.id = order_items.order_id JOIN products ON order_items.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(); if ($top_products_result && $top_products_result->num_rows > 0) { while ($row = $top_products_result->fetch_assoc()) { $top_products[] = $row['product_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 (Only applicable to Orders) --> <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 flex-col space-y-4 justify-end"> <button type="submit" class="w-full bg-blue-600 text-white px-4 py-2 rounded-md shadow hover:bg-blue-700">Filter</button> <!-- Export Buttons --> <a href="generate_reports.php?export=orders<?= !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 Orders CSV</a> </div> </form> <!-- Additional Export Buttons for other tables --> <div class="grid grid-cols-1 md:grid-cols-2 lg:grid-cols-3 gap-4 mt-6"> <a href="generate_reports.php?export=plans<?= !empty($filter_date_from) ? '&filter_date_from=' . urlencode($filter_date_from) : '' ?><?= !empty($filter_date_to) ? '&filter_date_to=' . urlencode($filter_date_to) : '' ?>" class="bg-green-600 text-white px-4 py-2 rounded-md shadow hover:bg-green-700 text-center">Export Plans CSV</a> <a href="generate_reports.php?export=posts<?= !empty($filter_date_from) ? '&filter_date_from=' . urlencode($filter_date_from) : '' ?><?= !empty($filter_date_to) ? '&filter_date_to=' . urlencode($filter_date_to) : '' ?>" class="bg-green-600 text-white px-4 py-2 rounded-md shadow hover:bg-green-700 text-center">Export Posts CSV</a> <a href="generate_reports.php?export=notes<?= !empty($filter_date_from) ? '&filter_date_from=' . urlencode($filter_date_from) : '' ?><?= !empty($filter_date_to) ? '&filter_date_to=' . urlencode($filter_date_to) : '' ?>" class="bg-green-600 text-white px-4 py-2 rounded-md shadow hover:bg-green-700 text-center">Export Notes CSV</a> <a href="generate_reports.php?export=ads<?= !empty($filter_date_from) ? '&filter_date_from=' . urlencode($filter_date_from) : '' ?><?= !empty($filter_date_to) ? '&filter_date_to=' . urlencode($filter_date_to) : '' ?>" class="bg-green-600 text-white px-4 py-2 rounded-md shadow hover:bg-green-700 text-center">Export Ads CSV</a> </div> </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['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", 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 (KD)', 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 ` KD${context.parsed.y.toLocaleString()}`; } } } }, scales: { y: { beginAtZero: true, ticks: { color: '#6B7280', callback: function(value) { return 'KD ' + value.toLocaleString(); } }, grid: { color: '#E5E7EB' } }, x: { ticks: { color: '#6B7280' }, grid: { color: '#E5E7EB' } } } } }); }); </script> </body> </html>Explanation of the Updates
-
Export Functionality for Multiple Tables:
- Switch Statement: The
switchstatement handles different export types based on theexportGET parameter (orders,plans,posts,notes,ads). - Prepared Statements: Each case prepares its own SQL query, binds parameters if necessary, executes the query, and fetches the results.
- CSV Generation: For each export type, the script sets appropriate headers, writes a UTF-8 BOM for encoding consistency (especially important for languages like Arabic), defines column headers, iterates through the fetched data, and writes each row to the CSV.
- Error Handling: If any step fails (preparing the query, executing it, fetching results), an error message is set accordingly.
- Switch Statement: The
-
Export Buttons:
- Orders Export: The existing "Export Orders CSV" button is retained.
- Additional Export Buttons: New buttons are added for exporting
plans,posts,notes, andads. These buttons pass the respective export type via theexportGET parameter. - Preserving Filters: When exporting, the current filter values (
filter_date_from,filter_date_to,filter_status) are appended to the export URLs to ensure consistency in the exported data.
-
HTML Structure:
- Export Buttons Layout: The export buttons for different tables are organized in a responsive grid below the filtering form to maintain a clean and user-friendly interface.
-
Security Considerations:
- Authentication: Ensure that only authorized users can access this report generation page. This is handled by including
auth.php. - Input Sanitization: All user inputs are sanitized using
htmlspecialchars()to prevent XSS attacks. Additionally, prepared statements are used to prevent SQL injection. - Access Control for Exports: Depending on your application's requirements, you might want to restrict certain export types to specific user roles.
- Authentication: Ensure that only authorized users can access this report generation page. This is handled by including
-
Additional Enhancements:
- Date Formatting: Dates are formatted for better readability in the CSV files.
- Summary Sections: For the
ordersexport, a summary section is added at the end of the CSV to provide total paid orders and the total paid amount.
Usage Instructions
-
Accessing the Reports Page:
- Navigate to the
generate_reports.phppage within your application's public reports directory.
- Navigate to the
-
Applying Filters:
- Use the "Date From", "Date To", and "Status" filters to narrow down the data you wish to view or export. These filters primarily apply to the
ordersexport but can be utilized for other exports if you adjust the SQL queries accordingly.
- Use the "Date From", "Date To", and "Status" filters to narrow down the data you wish to view or export. These filters primarily apply to the
-
Exporting Data:
- After setting the desired filters, click on the respective "Export [Table] CSV" button to download the CSV file for that particular table.
- For example, clicking on "Export Plans CSV" will download the
workspace_plansdata based on the applied filters.
-
Viewing Reports:
- The main section displays a table of orders based on the selected filters.
- Charts provide visual summaries:
- Orders by Status: A pie chart showing the distribution of orders across different statuses.
- Orders Over Time: A line chart depicting the number of orders over the past 12 months.
- Top 5 Products by Sales: A bar chart highlighting the top-selling products.
Final Notes
-
Extensibility: You can further extend this functionality by adding more export options for other tables as needed. Follow the established pattern in the
switchstatement to handle new exports. -
Performance Considerations: For large datasets, exporting directly via PHP might lead to performance issues. Consider implementing pagination or asynchronous export processes for very large tables.
-
User Experience: Ensure that users are informed about the export status, especially for large exports. Implementing progress indicators or notifications can enhance user experience.
This comprehensive update should fulfill your requirement to export all necessary data from your database into CSV files, each accessible via dedicated export buttons on the reports page.
-