124 lines
6.6 KiB
JavaScript
124 lines
6.6 KiB
JavaScript
// routes/inventoryPostingList.js
|
|
// Mirrors SAP B1's own "Inventory Posting List" report (Inventory →
|
|
// Inventory Reports → Inventory Posting List): every stock-moving
|
|
// transaction (OINM) for a range of items over a date range, with a running
|
|
// quantity Balance per item, filterable by Item Code range, Item Group,
|
|
// and Warehouse(s) — same selection-criteria shape as the Inventory Status
|
|
// Report.
|
|
//
|
|
// NOTE — "Split Display by Batch/Serial Numbers" (visible on SAP's own
|
|
// selection screen) is deliberately NOT implemented here: OINM has no
|
|
// reliable, verified link back to the batch/serial actually posted on each
|
|
// transaction in this database (the obvious ITL1→OBTN join came back empty
|
|
// against real data), and showing a guessed/wrong batch number would be
|
|
// worse than not showing one at all. Every other column is real, verified
|
|
// OINM data.
|
|
'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;
|
|
|
|
// Real SAP TransType codes seen against this database's own transaction
|
|
// history (verified live: 60/59/67/15/18/20/14/16/19/13/21 account for
|
|
// nearly every row) — full descriptive labels rather than guessed 2-letter
|
|
// SAP abbreviations, since those couldn't be verified. Anything outside this
|
|
// list (e.g. a UDO-driven custom type) falls back to "Type <code>" rather
|
|
// than asserting a label that might be wrong.
|
|
const TRANS_TYPE_LABELS = {
|
|
13: 'AR Invoice', 14: 'AR Credit Memo', 15: 'Delivery', 16: 'AR Return',
|
|
17: 'Reserve Invoice', 18: 'Goods Receipt PO', 19: 'Goods Return',
|
|
20: 'AP Invoice', 21: 'AP Credit Memo',
|
|
59: 'Inventory Receipt', 60: 'Inventory Issue', 67: 'Stock Transfer',
|
|
};
|
|
function transLabel(t) { return TRANS_TYPE_LABELS[t] || `Type ${t}`; }
|
|
|
|
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 itemGroup = (req.query.itemGroup || '').trim();
|
|
const warehouses = (req.query.warehouses || '').split(',').map((w) => w.trim()).filter(Boolean);
|
|
const dateFrom = (req.query.dateFrom || '').trim();
|
|
const dateTo = (req.query.dateTo || '').trim();
|
|
const hideNoQtyChange = req.query.hideNoQtyChange === '1';
|
|
if (!dateFrom || !dateTo) return res.status(400).json({ success: false, message: 'dateFrom and dateTo are required' });
|
|
|
|
// Resolve the matching item set first — same range+group filter as the
|
|
// Inventory Status Report, so the two stay consistent.
|
|
const itemWhere = [];
|
|
if (itemFrom) itemWhere.push(`"ItemCode" >= '${sqlEsc(itemFrom)}'`);
|
|
if (itemTo) itemWhere.push(`"ItemCode" <= '${sqlEsc(itemTo)}'`);
|
|
if (itemGroup) itemWhere.push(`"ItmsGrpCod" = ${parseInt(itemGroup)}`);
|
|
const itemRows = await pool.request().query(`SELECT "ItemCode" FROM [dbo].[OITM] ${itemWhere.length ? 'WHERE ' + itemWhere.join(' AND ') : ''}`);
|
|
const itemCodes = itemRows.recordset.map((r) => r.ItemCode);
|
|
if (!itemCodes.length) return res.json({ success: true, data: [] });
|
|
// Guard against an unbounded range (e.g. both From/To left blank) —
|
|
// matching against every item in SAP would make the OINM scan below
|
|
// enormous. 2000 items is already a generous ceiling for one report run.
|
|
if (itemCodes.length > 2000) return res.status(400).json({ success: false, message: `${itemCodes.length} items match this filter — narrow the Item Code range or Item Group first (max 2000 at a time).` });
|
|
const itemList = itemCodes.map((c) => `'${sqlEsc(c)}'`).join(',');
|
|
|
|
const whWhere = warehouses.length ? `AND "Warehouse" IN (${warehouses.map((w) => `'${sqlEsc(w)}'`).join(',')})` : '';
|
|
|
|
// Opening balance per item — every transaction strictly BEFORE the
|
|
// selected date range, so "Balance" in the results is a real absolute
|
|
// stock level, not just a delta within the window (matches what SAP's
|
|
// own report shows).
|
|
const openingRows = await pool.request().query(`
|
|
SELECT "ItemCode", SUM(ISNULL("InQty",0)) - SUM(ISNULL("OutQty",0)) AS "Opening"
|
|
FROM [dbo].[OINM]
|
|
WHERE "ItemCode" IN (${itemList}) ${whWhere} AND "DocDate" < '${sqlEsc(dateFrom)}'
|
|
GROUP BY "ItemCode"`);
|
|
const openingByItem = {};
|
|
openingRows.recordset.forEach((r) => { openingByItem[r.ItemCode] = Number(r.Opening) || 0; });
|
|
|
|
const qtyFilter = hideNoQtyChange ? `AND (ISNULL("InQty",0)<>0 OR ISNULL("OutQty",0)<>0)` : '';
|
|
const rows = await pool.request().query(`
|
|
SELECT T0."ItemCode", T0."Dscription", T0."DocDate", T0."CreateDate", T0."Warehouse",
|
|
T0."InQty", T0."OutQty", T0."CardCode", T0."CardName", T0."Ref1", T0."Ref2",
|
|
T0."TransType", T0."TransNum", T0."DocLineNum", T0."CalcPrice", T0."Price", T0."Comments"
|
|
FROM [dbo].[OINM] T0
|
|
WHERE T0."ItemCode" IN (${itemList}) ${whWhere}
|
|
AND T0."DocDate" >= '${sqlEsc(dateFrom)}' AND T0."DocDate" <= '${sqlEsc(dateTo)}'
|
|
${qtyFilter}
|
|
ORDER BY T0."ItemCode", T0."DocDate", T0."TransNum"`);
|
|
|
|
// Running balance computed here (not trusted from OINM.Balance, which is
|
|
// only populated on certain valuation checkpoints, not every row —
|
|
// verified live: most receipt/issue rows carry a NULL Balance) — a
|
|
// simple per-item cumulative sum seeded from the opening balance above.
|
|
const runningByItem = {};
|
|
const data = rows.recordset.map((r) => {
|
|
const itemCode = r.ItemCode;
|
|
if (!(itemCode in runningByItem)) runningByItem[itemCode] = openingByItem[itemCode] || 0;
|
|
const inQty = Number(r.InQty) || 0;
|
|
const outQty = Number(r.OutQty) || 0;
|
|
runningByItem[itemCode] += inQty - outQty;
|
|
return {
|
|
itemCode, itemName: r.Dscription || '',
|
|
docDate: r.DocDate, warehouse: r.Warehouse || '',
|
|
transType: r.TransType, docLabel: transLabel(r.TransType),
|
|
docRef: r.Ref1 || '', ref2: r.Ref2 || '',
|
|
docRow: (r.DocLineNum || 0) + 1,
|
|
bpName: r.CardName || '', cardCode: r.CardCode || '',
|
|
inQty, outQty,
|
|
price: (r.CalcPrice != null && r.CalcPrice !== 0) ? Number(r.CalcPrice) : Number(r.Price) || 0,
|
|
balance: runningByItem[itemCode],
|
|
remarks: r.Comments || '',
|
|
};
|
|
});
|
|
res.json({ success: true, data, openingBalances: openingByItem });
|
|
} catch (err) {
|
|
res.status(500).json({ success: false, message: err.message });
|
|
}
|
|
});
|
|
|
|
module.exports = router;
|