82 lines
3.5 KiB
JavaScript
82 lines
3.5 KiB
JavaScript
// routes/inventoryStatusReport.js
|
||
// Mirrors SAP B1's own "Inventory Status" report (Inventory → Inventory
|
||
// Reports → Inventory Status): per-item In Stock / Committed / Ordered /
|
||
// Available, aggregated across whichever warehouses the user selects,
|
||
// filterable by Item Code range, Preferred Vendor range, and Item Group.
|
||
'use strict';
|
||
const express = require('express');
|
||
const router = express.Router();
|
||
const { verifyToken } = require('../middleware/auth');
|
||
const { getPool } = require('../services/sqlPool');
|
||
|
||
const cq = (req) => req.query?.company || req.body?.company || null;
|
||
|
||
// Available = In Stock − Committed + Ordered — same formula SAP's own
|
||
// Inventory Status report uses (verified live against a real item: In Stock
|
||
// 2,817.648 − Committed 103,891.201 + Ordered 3,900 = Available −97,173.553,
|
||
// matching SAP's screen exactly).
|
||
router.get('/', verifyToken, async (req, res) => {
|
||
try {
|
||
const co = cq(req);
|
||
const pool = await getPool(co);
|
||
const sqlEsc = (v) => String(v).replace(/'/g, "''");
|
||
|
||
const itemFrom = (req.query.itemFrom || '').trim();
|
||
const itemTo = (req.query.itemTo || '').trim();
|
||
const vendorFrom = (req.query.vendorFrom || '').trim();
|
||
const vendorTo = (req.query.vendorTo || '').trim();
|
||
const itemGroup = (req.query.itemGroup || '').trim();
|
||
const hideZeroStock = req.query.hideZeroStock === '1';
|
||
const warehouses = (req.query.warehouses || '').split(',').map((w) => w.trim()).filter(Boolean);
|
||
|
||
const where = [];
|
||
if (itemFrom) where.push(`T0."ItemCode" >= '${sqlEsc(itemFrom)}'`);
|
||
if (itemTo) where.push(`T0."ItemCode" <= '${sqlEsc(itemTo)}'`);
|
||
if (vendorFrom) where.push(`T0."CardCode" >= '${sqlEsc(vendorFrom)}'`);
|
||
if (vendorTo) where.push(`T0."CardCode" <= '${sqlEsc(vendorTo)}'`);
|
||
if (itemGroup) where.push(`T0."ItmsGrpCod" = ${parseInt(itemGroup)}`);
|
||
|
||
// No warehouses selected = every warehouse (matches leaving every
|
||
// checkbox unticked on SAP's own selection screen). Otherwise scope the
|
||
// OITW join to just the picked ones, in the JOIN condition — not a
|
||
// WHERE clause — so an item with stock in ONLY unselected warehouses
|
||
// still appears (with everything zeroed out), same as SAP's own report.
|
||
const whJoin = warehouses.length
|
||
? `AND T1."WhsCode" IN (${warehouses.map((w) => `'${sqlEsc(w)}'`).join(',')})`
|
||
: '';
|
||
|
||
const havingZero = hideZeroStock ? `HAVING SUM(ISNULL(T1."OnHand",0)) <> 0` : '';
|
||
|
||
const query = `
|
||
SELECT T0."ItemCode", T0."ItemName", T0."InvntryUom" AS "Uom",
|
||
SUM(ISNULL(T1."OnHand",0)) AS "InStock",
|
||
SUM(ISNULL(T1."IsCommited",0)) AS "Committed",
|
||
SUM(ISNULL(T1."OnOrder",0)) AS "Ordered"
|
||
FROM [dbo].[OITM] T0
|
||
LEFT JOIN [dbo].[OITW] T1 ON T1."ItemCode" = T0."ItemCode" ${whJoin}
|
||
${where.length ? `WHERE ${where.join(' AND ')}` : ''}
|
||
GROUP BY T0."ItemCode", T0."ItemName", T0."InvntryUom"
|
||
${havingZero}
|
||
ORDER BY T0."ItemCode"`;
|
||
|
||
const result = await pool.request().query(query);
|
||
const data = result.recordset.map((r) => {
|
||
const inStock = Number(r.InStock) || 0;
|
||
const committed = Number(r.Committed) || 0;
|
||
const ordered = Number(r.Ordered) || 0;
|
||
return {
|
||
itemCode: r.ItemCode,
|
||
itemName: r.ItemName || '',
|
||
inStock, committed, ordered,
|
||
available: inStock - committed + ordered,
|
||
uom: r.Uom || '',
|
||
};
|
||
});
|
||
res.json({ success: true, data });
|
||
} catch (err) {
|
||
res.status(500).json({ success: false, message: err.message });
|
||
}
|
||
});
|
||
|
||
module.exports = router;
|