<?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=Restaurant_Sales_Report.xls");

echo "<table border='1'>";
echo "<tr>
<th>#</th>
<th>Order ID</th>
<th>Date</th>
<th>Total Amount</th>
<th>Paid</th>
<th>Balance</th>
<th>Status</th>
</tr>";

$i=1;

$orders = $conn->query("
SELECT *
FROM restaurant_orders
WHERE DATE(created_at) BETWEEN '$from' AND '$to'
ORDER BY created_at DESC
");

while($o=$orders->fetch_assoc()){

    $items = $conn->query("
    SELECT price,qty
    FROM restaurant_order_items
    WHERE order_id=".$o['id']
    );

    $total=0;

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

    $paid = ($o['status']=='PAID' ? $o['grand_total'] : 0);
    $balance = $total - $paid;

    echo "<tr>
    <td>$i</td>
    <td>{$o['id']}</td>
    <td>{$o['created_at']}</td>
    <td>".number_format($total,2)."</td>
    <td>".number_format($paid,2)."</td>
    <td>".number_format($balance,2)."</td>
    <td>{$o['status']}</td>
    </tr>";

$i++;
}

echo "</table>";
?>