<?php
include 'db.php';

$from = $_GET['from'];
$to   = $_GET['to'];

$summary = $conn->query("
SELECT
COUNT(DISTINCT r.id) total_requisitions,
SUM(i.quantity) total_items,
SUM(i.quantity * p.buying_price) total_value
FROM low_stock_requisitions r
JOIN low_stock_requisition_items i
ON r.id=i.requisition_id
JOIN products p
ON p.id=i.product_id
WHERE DATE(r.created_at)
BETWEEN '$from' AND '$to'
")->fetch_assoc();

$report = $conn->query("
SELECT
r.id,
r.created_at,
COUNT(i.id) item_count,
SUM(i.quantity*p.buying_price) total_cost
FROM low_stock_requisitions r
LEFT JOIN low_stock_requisition_items i
ON r.id=i.requisition_id
LEFT JOIN products p
ON p.id=i.product_id
WHERE DATE(r.created_at)
BETWEEN '$from' AND '$to'
GROUP BY r.id
ORDER BY r.id DESC
");
?>

<!DOCTYPE html>
<html>
<head>
<title>Requisition Report</title>

<style>

body{
font-family:Arial;
margin:30px;
}

table{
width:100%;
border-collapse:collapse;
}

table th,
table td{
border:1px solid #000;
padding:8px;
}

th{
background:#eee;
}

.card{
display:inline-block;
width:30%;
border:1px solid #000;
padding:15px;
margin-right:1%;
text-align:center;
}

@media print{
button{
display:none;
}
}

</style>

</head>

<body onload="window.print()">

<button onclick="window.print()">Print</button>

<h2 align="center">
LOW STOCK REQUISITION REPORT
</h2>

<p>
From:
<strong><?= $from ?></strong>
&nbsp;&nbsp;&nbsp;
To:
<strong><?= $to ?></strong>
</p>

<div class="card">
<h4>Total Requisitions</h4>
<h2><?= $summary['total_requisitions'] ?></h2>
</div>

<div class="card">
<h4>Total Items</h4>
<h2><?= $summary['total_items'] ?></h2>
</div>

<div class="card">
<h4>Total Value</h4>
<h2>
MWK <?= number_format($summary['total_value'],2) ?>
</h2>
</div>

<br><br>

<table>

<thead>
<tr>
<th>ID</th>
<th>Date</th>
<th>Items</th>
<th>Total Cost</th>
</tr>
</thead>

<tbody>

<?php while($row=$report->fetch_assoc()): ?>

<tr>
<td><?= $row['id'] ?></td>

<td>
<?= date('d M Y',strtotime($row['created_at'])) ?>
</td>

<td><?= $row['item_count'] ?></td>

<td>
MWK <?= number_format($row['total_cost'],2) ?>
</td>

</tr>

<?php endwhile; ?>

</tbody>

</table>

<br><br>

<div style="margin-top:80px;">
<table style="border:none;">
<tr>
<td style="border:none;width:50%;">
_________________________<br>
Prepared By
</td>

<td style="border:none;">
_________________________<br>
Approved By
</td>
</tr>
</table>
</div>

</body>
</html>