<?php
include 'topmost.php';
ini_set('max_execution_time', '0');

/*
|--------------------------------------------------------------------------
| COLLAPSED STOCK QUERY
|--------------------------------------------------------------------------
*/
$sql = "
SELECT 
    p.id,
    p.item_name,
    p.size,
    p.color,
    p.brand,
    p.company,
    p.item_cost,
    p.item_img,

    SUM(CASE 
        WHEN sk.branch_code = 0 THEN sk.item_qty 
        ELSE 0 
    END) AS total_stock,

    SUM(CASE 
        WHEN sk.item_qty < 0 THEN sk.item_qty 
        ELSE 0 
    END) AS total_supplied,

    MAX(CASE 
        WHEN sk.branch_code = 0 THEN sk.item_time 
        ELSE NULL 
    END) AS last_stock_time,

    SUBSTRING_INDEX(
        GROUP_CONCAT(
            CASE 
                WHEN sk.branch_code = 0 THEN sk.item_qty 
                ELSE NULL 
            END
            ORDER BY sk.id DESC
        ),
        ',', 1
    ) AS last_stock_qty

FROM products p
LEFT JOIN storekeeper sk 
    ON sk.item_name = p.id

WHERE p.tm_status = 1
GROUP BY p.id
ORDER BY p.item_name ASC
";

$products = QueryDB($sql);
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>My Outlet</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<?php include 'toplinks.php'; ?>
</head>

<body id="page-top">
<div id="wrapper">
<?php include 'menus.php'; ?>
<?php include 'top.php'; ?>

<div class="container-fluid">

<div class="d-sm-flex align-items-center justify-content-between mb-4">
<?php include 'greet.php'; ?>
<?php include 'process.php'; ?>
</div>

<!-- SUMMARY CARDS -->
<div class="row">

<div class="col-md-4 mb-3">
<div class="card border-left-primary shadow">
<div class="card-body">
<div class="text-xs font-weight-bold text-primary">TOTAL PRODUCTS</div>
<div class="h5 font-weight-bold">
<?= QueryDB("SELECT COUNT(*) FROM products")->fetchColumn(); ?>
</div>
</div>
</div>
</div>

<div class="col-md-4 mb-3">
<div class="card border-left-success shadow">
<div class="card-body">
<div class="text-xs font-weight-bold text-success">TOTAL BRANCHES</div>
<div class="h5 font-weight-bold">
<?= QueryDB("SELECT COUNT(*) FROM branch")->fetchColumn(); ?>
</div>
</div>
</div>
</div>

<div class="col-md-4 mb-3">
<div class="card border-left-info shadow">
<div class="card-body">
<div class="text-xs font-weight-bold text-info">WAREHOUSE STOCK</div>
<div class="h5 font-weight-bold">
<?php
$sum = QueryDB("SELECT SUM(item_qty) FROM storekeeper")->fetchColumn();
echo number_format($sum ?? 0);
?>
</div>
</div>
</div>
</div>

</div>

<!-- STOCK TABLE -->
<div class="card shadow mb-4">
<div class="card-header bg-success text-white d-flex justify-content-between">
<h6 class="m-0">WAREHOUSE STOCK VIEW</h6>
<button class="btn btn-dark btn-sm" id="exp_whrhs_stock">
<i class="fa fa-file-excel"></i> Export CSV
</button>
</div>

<div class="card-body table-responsive">
<table class="table table-bordered whrhs_stock" id="dataTable" width="100%">
<thead class="small">
<tr>
<th>#</th>
<th>Name</th>
<th>Size</th>
<th>Color</th>
<th>Brand</th>
<th>Company</th>
<th>Cost</th>
<th>Image</th>
<th>Last Stock Date</th>
<th>Last Qty</th>
<th>Total</th>
<th>Supplied</th>
<th>Current Qty</th>
<th>Action</th>
</tr>
</thead>
<tbody>

<?php
$sn = 1;
foreach ($products as $row):

$currentQty = ($row['total_stock'] ?? 0) - abs($row['total_supplied'] ?? 0);
?>
<tr class="small">
<td><?= $sn++ ?></td>
<td><?= htmlspecialchars($row['item_name']) ?></td>
<td><?= $sized_by[$row['size']] ?? '-' ?></td>
<td><?= $colored_by[$row['color']] ?? '-' ?></td>
<td><?= $brand[$row['brand']] ?? '-' ?></td>
<td><?= $company[$row['company']] ?? '-' ?></td>

<td>&#8358;<?= number_format($row['item_cost'], 2) ?></td>

<td>
<?php
if (!empty($row['item_img'])) {
    $img = explode(',', $row['item_img'])[0];
    echo '<img src="'.$img.'" class="img-thumbnail" style="height:60px">';
}
?>
</td>

<td>
<?= $row['last_stock_time']
    ? date('D jS M, Y', $row['last_stock_time'])
    : '-' ?>
</td>

<td><?= $row['last_stock_qty'] ?? 0 ?></td>
<td><?= $row['total_stock'] ?? 0 ?></td>
<td><?= abs($row['total_supplied'] ?? 0) ?></td>
<td><?= $currentQty ?></td>

<td>
<a href="whrs_itemView?sel_product=<?= $row['id'] ?>" class="btn btn-info btn-sm">
Supply History
</a>
</td>
</tr>
<?php endforeach; ?>

</tbody>
</table>
</div>
</div>

</div>

<?php include 'bottom_most.php'; ?>

<script>
var fileName = "<?= date('Y-m-d') ?>_Warehouse_Stock";
$('#exp_whrhs_stock').on('click', function () {
$('.whrhs_stock').table2excel({
exclude: ".noExl",
filename: fileName + ".xls"
});
});
</script>

</body>
</html>
