<?php
require_once 'config.php';
require_login();
require_permission('stock');

$group_id     = (int)($_GET['group_id'] ?? 0);
$only_nonzero = isset($_GET['only_nonzero']) && $_GET['only_nonzero'] === '1';
$search       = trim($_GET['q'] ?? '');

$where = [];
if ($group_id > 0) {
    $where[] = 'i.item_group_id = ' . $group_id;
}
if ($search !== '') {
    $esc = $mysqli->real_escape_string($search);
    $where[] = "(i.name LIKE '%{$esc}%' OR i.code LIKE '%{$esc}%')";
}
$where_sql = $where ? 'WHERE ' . implode(' AND ', $where) : '';

$sql = "
SELECT
    i.code,
    i.name,
    i.unit,
    g.name AS group_name,
    COALESCE(rin.total_in, 0)  AS qty_in,
    COALESCE(rout.total_out, 0) AS qty_out,
    COALESCE(rin.total_in, 0) - COALESCE(rout.total_out, 0) AS qty_stock
FROM items i
LEFT JOIN item_groups g ON g.id = i.item_group_id
LEFT JOIN (
    SELECT ri.item_id, SUM(ri.quantity) AS total_in
    FROM receipt_items ri
    JOIN receipts r ON r.id = ri.receipt_id
    GROUP BY ri.item_id
) AS rin ON rin.item_id = i.id
LEFT JOIN (
    SELECT ci.item_id, SUM(ci.quantity) AS total_out
    FROM consumption_items ci
    JOIN consumption_orders o ON o.id = ci.order_id
    GROUP BY ci.item_id
) AS rout ON rout.item_id = i.id
{$where_sql}
";

if ($only_nonzero) {
    $sql .= " HAVING qty_stock <> 0";
}
$sql .= " ORDER BY i.name";

$res = $mysqli->query($sql);

header('Content-Type: application/vnd.ms-excel; charset=utf-8');
header('Content-Disposition: attachment; filename="stanje_skladista_' . date('Ymd_His') . '.xls"');
echo "Šifra\tNaziv\tGrupa\tUlaz\tIzlaz\tStanje\tJM\n";

while ($row = $res->fetch_assoc()) {
    $in    = number_format((float)$row['qty_in'], 3, ',', '.');
    $out   = number_format((float)$row['qty_out'], 3, ',', '.');
    $stock = number_format((float)$row['qty_stock'], 3, ',', '.');

    echo $row['code'] . "\t" .
         $row['name'] . "\t" .
         $row['group_name'] . "\t" .
         $in . "\t" .
         $out . "\t" .
         $stock . "\t" .
         $row['unit'] . "\n";
}
exit;
