<?php
include 'db.php';

$from = $_GET['from'] ?? date('Y-m-01');
$to   = $_GET['to'] ?? date('Y-m-d');

header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=Bar_Sales_Analytics.xls");

echo "<table border='1'>";
echo "<tr>
<th>#</th>
<th>Guest</th>
<th>Sale Date</th>
<th>Total Amount</th>
<th>Paid</th>
<th>Balance</th>
</tr>";

$i=1;

$sales = $conn->query("
SELECT s.*, g.name AS guest_name
FROM sales s
LEFT JOIN guests g ON s.customer_id=g.id
WHERE s.sales_date BETWEEN '$from' AND '$to'
AND rest=0
ORDER BY s.sales_date DESC
");

while($s=$sales->fetch_assoc()){

    $items = $conn->query("
        SELECT price,qty
        FROM sale_items
        WHERE sale_id=".$s['id']
    );

    $total = 0;

    while($it=$items->fetch_assoc()){
        $total += $it['price'] * $it['qty'];
    }

    $paid = $conn->query("
        SELECT COALESCE(SUM(amount),0) AS total_paid
        FROM payments
        WHERE id=".$s['id']." AND status='Paid'
    ")->fetch_assoc()['total_paid'];

    $balance = $total - $paid;

    echo "<tr>
    <td>$i</td>
    <td>{$s['guest_name']}</td>
    <td>{$s['sales_date']}</td>
    <td>".number_format($total,2)."</td>
    <td>".number_format($paid,2)."</td>
    <td>".number_format($balance,2)."</td>
    </tr>";

    $i++;
}

echo "</table>";
?>