Skip to content
TheChatArchiveThe Chat Archive
o1-mini-2024-09-12· OpenAIAi2 WildChat-4.8M (AI2)

in this database (-- phpMyAdmin SQL Dump -- version 5.2.1 -- https://www.phpmyadmin.net/…

  1. 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 ads

    CREATE TABLE ads ( id int(11) NOT NULL, user_id int(11) NOT NULL, name varchar(255) NOT NULL, ad_type enum('Post','Story') NOT NULL, related_id int(11) NOT NULL, budget decimal(10,2) NOT NULL, total_spent decimal(10,2) DEFAULT 0.00, start_date date NOT NULL, end_date date NOT NULL, status enum('Active','Paused','Completed') DEFAULT 'Active', created_at datetime DEFAULT current_timestamp(), updated_at datetime DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


    -- -- Table structure for table ad_spend_logs

    CREATE TABLE ad_spend_logs ( id int(11) NOT NULL, ad_id int(11) NOT NULL, amount decimal(10,2) NOT NULL, spend_date datetime DEFAULT current_timestamp(), description varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


    -- -- Table structure for table customers

    CREATE TABLE customers ( id int(11) NOT NULL, name varchar(100) NOT NULL, contact varchar(50) DEFAULT NULL, delivery_address text DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


    -- -- Table structure for table drivers

    CREATE TABLE drivers ( id int(11) NOT NULL, name varchar(100) NOT NULL, number varchar(15) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


    -- -- Table structure for table orders

    CREATE TABLE orders ( id int(11) NOT NULL, customer_id int(11) NOT NULL, status enum('Pending','In Progress','Completed','Delivered') DEFAULT 'Pending', priority enum('Normal','Important','Quick') DEFAULT 'Normal', order_date datetime DEFAULT current_timestamp(), delivery_datetime datetime DEFAULT NULL, delivery_date date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


    -- -- Table structure for table order_items

    CREATE TABLE order_items ( id int(11) NOT NULL, order_id int(11) NOT NULL, product_id int(11) NOT NULL, quantity int(11) NOT NULL DEFAULT 1, packaging_preferences varchar(255) DEFAULT NULL, discount_percentage decimal(5,2) DEFAULT 0.00, discount_reason varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


    -- -- Table structure for table products

    CREATE TABLE products ( id int(11) NOT NULL, name varchar(100) NOT NULL, size varchar(50) DEFAULT NULL, price decimal(10,2) NOT NULL, category varchar(50) DEFAULT NULL, thumbnail varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


    -- -- Table structure for table users

    CREATE TABLE users ( id int(11) NOT NULL, username varchar(50) NOT NULL, password varchar(255) NOT NULL, role enum('admin','staff') NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


    -- -- Table structure for table workspace_notes

    CREATE TABLE workspace_notes ( id int(11) NOT NULL, user_id int(11) NOT NULL, title varchar(255) NOT NULL, content text NOT NULL, created_at datetime DEFAULT current_timestamp(), updated_at datetime DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


    -- -- Table structure for table workspace_plans

    CREATE TABLE workspace_plans ( id int(11) NOT NULL, user_id int(11) NOT NULL, title varchar(255) NOT NULL, description text DEFAULT NULL, due_date datetime DEFAULT NULL, status enum('Not Started','In Progress','Completed') DEFAULT 'Not Started', created_at datetime DEFAULT current_timestamp(), updated_at datetime DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


    -- -- Table structure for table workspace_posts

    CREATE TABLE workspace_posts ( id int(11) NOT NULL, user_id int(11) NOT NULL, platform enum('Instagram','Facebook','Twitter','Other') NOT NULL, content text NOT NULL, media_url varchar(255) DEFAULT NULL, scheduled_datetime datetime DEFAULT NULL, status enum('Scheduled','Posted','Failed') DEFAULT 'Scheduled', created_at datetime DEFAULT current_timestamp(), updated_at datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(), type enum('Post','Story') DEFAULT 'Post' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    -- -- Indexes for dumped tables

    -- -- Indexes for table ads

    ALTER TABLE ads ADD PRIMARY KEY (id), ADD KEY user_id (user_id), ADD KEY related_id (related_id);

    -- -- Indexes for table ad_spend_logs

    ALTER TABLE ad_spend_logs ADD PRIMARY KEY (id), ADD KEY ad_id (ad_id);

    -- -- Indexes for table customers

    ALTER TABLE customers ADD PRIMARY KEY (id);

    -- -- Indexes for table drivers

    ALTER TABLE drivers ADD PRIMARY KEY (id);

    -- -- Indexes for table orders

    ALTER TABLE orders ADD PRIMARY KEY (id), ADD KEY customer_id (customer_id), ADD KEY idx_orders_customer_id (customer_id);

    -- -- Indexes for table order_items

    ALTER TABLE order_items ADD PRIMARY KEY (id), ADD KEY order_id (order_id), ADD KEY product_id (product_id), ADD KEY idx_order_items_order_id (order_id), ADD KEY idx_order_items_product_id (product_id);

    -- -- Indexes for table products

    ALTER TABLE products ADD PRIMARY KEY (id);

    -- -- Indexes for table users

    ALTER TABLE users ADD PRIMARY KEY (id), ADD UNIQUE KEY username (username);

    -- -- Indexes for table workspace_notes

    ALTER TABLE workspace_notes ADD PRIMARY KEY (id), ADD KEY user_id (user_id);

    -- -- Indexes for table workspace_plans

    ALTER TABLE workspace_plans ADD PRIMARY KEY (id), ADD KEY user_id (user_id);

    -- -- Indexes for table workspace_posts

    ALTER TABLE workspace_posts ADD PRIMARY KEY (id), ADD KEY user_id (user_id);

    -- -- AUTO_INCREMENT for dumped tables

    -- -- AUTO_INCREMENT for table ads

    ALTER TABLE ads MODIFY id int(11) NOT NULL AUTO_INCREMENT;

    -- -- AUTO_INCREMENT for table ad_spend_logs

    ALTER TABLE ad_spend_logs MODIFY id int(11) NOT NULL AUTO_INCREMENT;

    -- -- AUTO_INCREMENT for table customers

    ALTER TABLE customers MODIFY id int(11) NOT NULL AUTO_INCREMENT;

    -- -- AUTO_INCREMENT for table drivers

    ALTER TABLE drivers MODIFY id int(11) NOT NULL AUTO_INCREMENT;

    -- -- AUTO_INCREMENT for table orders

    ALTER TABLE orders MODIFY id int(11) NOT NULL AUTO_INCREMENT;

    -- -- AUTO_INCREMENT for table order_items

    ALTER TABLE order_items MODIFY id int(11) NOT NULL AUTO_INCREMENT;

    -- -- AUTO_INCREMENT for table products

    ALTER TABLE products MODIFY id int(11) NOT NULL AUTO_INCREMENT;

    -- -- AUTO_INCREMENT for table users

    ALTER TABLE users MODIFY id int(11) NOT NULL AUTO_INCREMENT;

    -- -- AUTO_INCREMENT for table workspace_notes

    ALTER TABLE workspace_notes MODIFY id int(11) NOT NULL AUTO_INCREMENT;

    -- -- AUTO_INCREMENT for table workspace_plans

    ALTER TABLE workspace_plans MODIFY id int(11) NOT NULL AUTO_INCREMENT;

    -- -- AUTO_INCREMENT for table workspace_posts

    ALTER TABLE workspace_posts MODIFY id int(11) NOT NULL AUTO_INCREMENT;

    -- -- Constraints for dumped tables

    -- -- Constraints for table ads

    ALTER TABLE ads ADD CONSTRAINT fk_ads_related_id FOREIGN KEY (related_id) REFERENCES workspace_posts (id) ON DELETE CASCADE, ADD CONSTRAINT fk_ads_user_id FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE;

    -- -- Constraints for table ad_spend_logs

    ALTER TABLE ad_spend_logs ADD CONSTRAINT fk_ad_spend_logs_ad_id FOREIGN KEY (ad_id) REFERENCES ads (id) ON DELETE CASCADE;

    -- -- Constraints for table orders

    ALTER TABLE orders ADD CONSTRAINT fk_orders_customer_id FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT orders_ibfk_1 FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE;

    -- -- Constraints for table order_items

    ALTER TABLE order_items ADD CONSTRAINT fk_order_items_order_id FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT fk_order_items_product_id FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT order_items_ibfk_1 FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE, ADD CONSTRAINT order_items_ibfk_2 FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE;

    -- -- Constraints for table workspace_notes

    ALTER TABLE workspace_notes ADD CONSTRAINT fk_workspace_notes_user_id FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE;

    -- -- Constraints for table workspace_plans

    ALTER TABLE workspace_plans ADD CONSTRAINT fk_workspace_plans_user_id FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE;

    -- -- Constraints for table workspace_posts

    ALTER TABLE workspace_posts ADD CONSTRAINT fk_workspace_posts_user_id FOREIGN KEY (user_id) REFERENCES users (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 success=′′;success = ''; error = '';

    // Initialize filter variables filterdatefrom=′′;filter_date_from = ''; filter_date_to = ''; $filter_status = '';

    // Initialize conditions and parameters for prepared statements conditions=[];conditions = []; params = []; $types = '';

    // Handle GET request for filtering if (_SERVER['REQUEST_METHOD'] === 'GET') { if (!empty(_GET['filter_date_from'])) { filterdatefrom=trim(filter_date_from = trim(_GET['filter_date_from']); conditions[]="DATE(orders.orderdate)>=?";conditions[] = "DATE(orders.order_date) >= ?"; params[] = filterdatefrom;filter_date_from; types .= 's'; } if (!empty(_GET['filter_date_to'])) { filter_date_to = trim(GET[′filterdateto′]);_GET['filter_date_to']); conditions[] = "DATE(orders.order_date) <= ?"; params[]=params[] = filter_date_to; types .= 's'; } if (!empty(_GET['filter_status'])) { filterstatus=trim(filter_status = trim(_GET['filter_status']); conditions[]="orders.status=?";conditions[] = "orders.status = ?"; params[] = filterstatus;filter_status; types .= 's'; } }

    where=′′;if(count(where = ''; if (count(conditions) > 0) { where="WHERE".implode("AND",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'])) { // 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 orderssql="SELECTorders.idASorderid,orders.status,orders.orderdate,orders.deliverydatetime,orders.deliverydate,orderitems.quantity,orderitems.packagingpreferences,orderitems.discountpercentage,orderitems.discountreason,customers.nameAScustomername,products.nameASproductname,products.priceASproductpriceFROMordersJOINcustomersONorders.customerid=customers.idJOINorderitemsONorders.id=orderitems.orderidJOINproductsONorderitems.productid=products.idorders_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=stmt = conn->prepare(orderssql);if(orders_sql); if (stmt) { if (count(params) > 0) { stmt->bind_param(types,...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 statussql="SELECTstatus,COUNT(∗)ascountFROMordersstatus_sql = "SELECT status, COUNT(*) as count FROM orders where GROUP BY status"; statusstmt=status_stmt = conn->prepare(statussql);if(status_sql); if (status_stmt) { if (count(params) > 0) { status_stmt->bind_param(types,...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) ordersovertimesql="SELECTDATEFORMAT(orders.orderdate,′FROMordersorders_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 "; ordersovertimestmt=orders_over_time_stmt = conn->prepare(ordersovertimesql);if(orders_over_time_sql); if (orders_over_time_stmt) { if (count(params) > 0) { orders_over_time_stmt->bind_param(types,...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 topproductssql="SELECTproducts.nameASproductname,SUM(orderitems.quantity∗products.price−(orderitems.quantity∗products.price∗IFNULL(orderitems.discountpercentage,0)/100))astotalsalesFROMordersJOINorderitemsONorders.id=orderitems.orderidJOINproductsONorderitems.productid=products.idtop_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 "; topproductsstmt=top_products_stmt = conn->prepare(topproductssql);if(top_products_sql); if (top_products_stmt) { if (count(params) > 0) { top_products_stmt->bind_param(types,...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['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">&copy; 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>
    
  2. Assistant

    Certainly! Below is the complete updated PHP code for generate_reports.php that allows exporting all relevant data (orders, workspace_plans, workspace_posts, workspace_notes, and ads) 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">&copy; 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

    1. Export Functionality for Multiple Tables:

      • Switch Statement: The switch statement handles different export types based on the export GET 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.
    2. Export Buttons:

      • Orders Export: The existing "Export Orders CSV" button is retained.
      • Additional Export Buttons: New buttons are added for exporting plans, posts, notes, and ads. These buttons pass the respective export type via the export GET 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.
    3. 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.
    4. 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.
    5. Additional Enhancements:

      • Date Formatting: Dates are formatted for better readability in the CSV files.
      • Summary Sections: For the orders export, a summary section is added at the end of the CSV to provide total paid orders and the total paid amount.

    Usage Instructions

    1. Accessing the Reports Page:

      • Navigate to the generate_reports.php page within your application's public reports directory.
    2. 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 orders export but can be utilized for other exports if you adjust the SQL queries accordingly.
    3. 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_plans data based on the applied filters.
    4. 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 switch statement 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.