<?php
include 'db.php';

// Get filters
$date_from = $_GET['date_from'] ?? '';
$date_to = $_GET['date_to'] ?? '';
$department = $_GET['department'] ?? '';
$product = $_GET['product'] ?? '';

// Build WHERE
$where = "WHERE 1=1";
if($date_from != '') $where .= " AND DATE(pd.dispatched_at) >= '$date_from'";
if($date_to != '') $where .= " AND DATE(pd.dispatched_at) <= '$date_to'";
if($department != '') $where .= " AND pd.department_id = '$department'";
if($product != '') $where .= " AND pp.product_name LIKE '%$product%'";

// Fetch data
$sql = "
SELECT pd.*, pp.product_name, pp.batch_number, d.name AS department
FROM perishable_dispatch pd
LEFT JOIN perishable_products pp ON pd.perishable_id = pp.id
LEFT JOIN departments d ON pd.department_id = d.id
$where
ORDER BY pd.dispatched_at DESC
";
$data = $conn->query($sql);

// Set headers for Excel
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=dispatch_report.xls");
header("Cache-Control: max-age=0");

// Start output
echo "<table border='1'>";
echo "<tr>
        <th>Date</th>
        <th>Product</th>
        <th>Batch</th>
        <th>Department</th>
        <th>Quantity</th>
        <th>Collected By</th>
        <th>Issued By</th>
      </tr>";

while($row = $data->fetch_assoc()){
    echo "<tr>";
    echo "<td>".date('Y-m-d', strtotime($row['dispatched_at']))."</td>";
    echo "<td>".htmlspecialchars($row['product_name'])."</td>";
    echo "<td>".$row['batch_number']."</td>";
    echo "<td>".$row['department']."</td>";
    echo "<td>".$row['quantity']."</td>";
    echo "<td>".$row['collected_by']."</td>";
    echo "<td>".$row['dispatched_by']."</td>";
    echo "</tr>";
}

echo "</table>";
exit;
?>