<?php
include 'db.php';

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

$today = date('Y-m-d');

$from_date = $_GET['from'] ?? $today;
$to_date   = $_GET['to'] ?? $today;
$type      = $_GET['type'] ?? '';
$search    = $_GET['search'] ?? '';

$where = [];

if($from_date){
    $where[] = "DATE(p.payment_date) >= '".$conn->real_escape_string($from_date)."'";
}
if($to_date){
    $where[] = "DATE(p.payment_date) <= '".$conn->real_escape_string($to_date)."'";
}
if($type){
    $where[] = "p.type = '".$conn->real_escape_string($type)."'";
}
if($search){
    $s = $conn->real_escape_string($search);
    $where[] = "(p.reference LIKE '%$s%' OR g.name LIKE '%$s%')";
}

$where_sql = $where ? "WHERE ".implode(" AND ", $where) : "";

$data = $conn->query("
    SELECT p.*, g.name AS guest_name
    FROM payments p
    LEFT JOIN guests g ON p.guest_id=g.id
    $where_sql
    ORDER BY p.payment_date DESC
");

echo "<table border='1'>";
echo "<tr>
        <th>Date</th>
        <th>Guest</th>
        <th>Reference</th>
        <th>Method</th>
        <th>Type</th>
        <th>Amount</th>
        <th>Received By</th>
      </tr>";

while($row = $data->fetch_assoc()){
    echo "<tr>
            <td>{$row['payment_date']}</td>
            <td>{$row['guest_name']}</td>
            <td>{$row['reference']}</td>
            <td>{$row['method']}</td>
            <td>{$row['type']}</td>
            <td>{$row['amount']}</td>
            <td>{$row['received_by']}</td>
          </tr>";
}
echo "</table>";
?>