32 lines
912 B
PHP
32 lines
912 B
PHP
<?php
|
|
/**
|
|
* alert_low_stock.php — Called by Node.js scheduler daily at 8am.
|
|
* Returns companies that have one or more SKUs below their min_stock threshold.
|
|
* Response: { success: 1, alerts: [{ company_id, count }] }
|
|
*/
|
|
require __DIR__ . '/../assets/utils/cron_auth.php';
|
|
|
|
$sth = $pdo2->query(
|
|
"SELECT e.company_id, COUNT(*) AS count
|
|
FROM (
|
|
SELECT company_id, product_sku, SUM(total_in - total_out) AS balance
|
|
FROM etl_stock_summary
|
|
WHERE company_id > 0
|
|
GROUP BY company_id, product_sku
|
|
) e
|
|
JOIN md_product p
|
|
ON p.sku = e.product_sku
|
|
AND p.company_id = e.company_id
|
|
AND p.status = 1
|
|
WHERE p.min_stock > 0
|
|
AND e.balance < p.min_stock
|
|
GROUP BY e.company_id
|
|
HAVING count > 0"
|
|
|
|
);
|
|
|
|
$answer['success'] = 1;
|
|
$answer['alerts'] = $sth->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
exit(json_encode($answer));
|