<?php
include 'includes/auth.php';
include 'db.php';
include 'includes/header.php';

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

$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
");
?>

<div class="container-fluid mt-4">

<h3>
<i class="fa fa-chart-bar"></i>
Low Stock Requisition Report
</h3>

<form method="GET" class="row mb-4">

<div class="col-md-3">
<label>From Date</label>
<input type="date" name="from"
class="form-control"
value="<?= $from ?>">
</div>

<div class="col-md-3">
<label>To Date</label>
<input type="date" name="to"
class="form-control"
value="<?= $to ?>">
</div>

<div class="col-md-2">
<label>&nbsp;</label>
<button class="btn btn-primary form-control">
<i class="fa fa-search"></i> Filter
</button>
</div>

<div class="col-md-2">
<label>&nbsp;</label>
<a href="print_requisition_report.php?from=<?= $from ?>&to=<?= $to ?>"
class="btn btn-success form-control"
target="_blank">
<i class="fa fa-print"></i> Print
</a>
</div>

</form>

<div class="row">

<div class="col-md-4">
<div class="card bg-primary text-white shadow">
<div class="card-body">
<h5>Total Requisitions</h5>
<h2><?= number_format($summary['total_requisitions'] ?? 0) ?></h2>
</div>
</div>
</div>

<div class="col-md-4">
<div class="card bg-warning text-dark shadow">
<div class="card-body">
<h5>Total Items Requested</h5>
<h2><?= number_format($summary['total_items'] ?? 0) ?></h2>
</div>
</div>
</div>

<div class="col-md-4">
<div class="card bg-success text-white shadow">
<div class="card-body">
<h5>Total Value</h5>
<h2>
MWK <?= number_format($summary['total_value'] ?? 0,2) ?>
</h2>
</div>
</div>
</div>

</div>

<div class="card mt-4">
<div class="card-header">
Detailed Requisition Listing
</div>

<div class="card-body">

<table class="table table-bordered table-striped">

<thead class="table-dark">
<tr>
<th>ID</th>
<th>Date</th>
<th>Items</th>
<th>Total Cost</th>
<th>Action</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>

<td>
<a href="view_requisition.php?id=<?= $row['id'] ?>"
class="btn btn-sm btn-info">
View
</a>
</td>

</tr>

<?php endwhile; ?>

</tbody>

</table>

</div>
</div>

</div>

<?php include 'includes/footer.php'; ?>