173 lines
8.9 KiB
JavaScript
173 lines
8.9 KiB
JavaScript
'use strict';
|
||
// routes/requirementCalc.js — "Requirement Calculator": a worksheet under the
|
||
// Requirements module that computes, per item, a suggested production
|
||
// requirement from historical sale quantity and current stock, so a planner
|
||
// can push the result straight into a real Requirement record (POST
|
||
// /api/requirements already accepts {periodFrom,periodTo,lines[]} — this
|
||
// route only supplies the numbers that feed that form, it does not persist
|
||
// anything itself).
|
||
//
|
||
// Formula (per the reference worksheet):
|
||
// Average Sale/Month = (Qty sold in Period A + Qty sold in Period B) / (months spanned by A+B, typically 10)
|
||
// Total FG = Stock + Quarantine Stock
|
||
// Stock in Hand (mo) = Total FG / Average Sale per Month
|
||
// Requirement = Average Sale per Month × 2 − Total FG
|
||
//
|
||
// Admin → System Settings can disable either Period A or Period B (at least
|
||
// one must always stay enabled). With only one period enabled, Average
|
||
// Sale/Month instead uses just that period's qty ÷ the actual span of its
|
||
// own From/To dates (auto-derived — the manual "Months" divisor field is
|
||
// only meaningful when averaging A+B together).
|
||
//
|
||
// Sale Qty and Stock are looked up live from SAP (INV1/OINV net of RIN1/ORIN
|
||
// returns; OITW.OnHand). There is no "Quarantine" warehouse concept anywhere
|
||
// else in this codebase, so the caller must supply which warehouse code(s)
|
||
// represent normal Stock and Quarantine Stock for this calculation (picked
|
||
// from GET /api/sap/lookup/warehouses) — nothing is hardcoded/guessed here.
|
||
const express = require('express');
|
||
const router = express.Router();
|
||
const { verifyToken } = require('../middleware/auth');
|
||
const { getPool } = require('../services/sqlPool');
|
||
const appSettings = require('../services/appSettingsStore');
|
||
// NOTE: getPool() connects to the single fixed SQL_DATABASE from .env (same
|
||
// as every other direct-SQL route in this app) — it is not company-switched
|
||
// per request, unlike the SAP Service-Layer calls elsewhere.
|
||
|
||
// Whole+fractional months between two ISO dates, inclusive on both ends —
|
||
// used to auto-derive the divisor when only ONE comparison period is enabled
|
||
// (Admin → System Settings → Requirement Calculator), instead of the manual
|
||
// "Months" field which only makes sense when averaging A+B together.
|
||
function monthSpan(from, to) {
|
||
const days = (new Date(to + 'T00:00:00Z') - new Date(from + 'T00:00:00Z')) / 86400000 + 1;
|
||
return Math.max(days / 30.44, 1 / 30.44); // never zero — a same-day range still spans a fraction of a month
|
||
}
|
||
|
||
// Net quantity sold (Invoices minus Returns/Credit Memos) for one item in one
|
||
// date range. DocDate is inclusive on both ends.
|
||
async function saleQty(pool, itemCode, from, to) {
|
||
const sql = `
|
||
SELECT
|
||
(
|
||
ISNULL((SELECT SUM(inv."Quantity") FROM [dbo].[INV1] inv
|
||
JOIN [dbo].[OINV] h ON h."DocEntry" = inv."DocEntry"
|
||
WHERE inv."ItemCode" = @itemCode AND h."DocDate" BETWEEN @from AND @to AND h."CANCELED" = 'N'), 0)
|
||
-
|
||
ISNULL((SELECT SUM(r."Quantity") FROM [dbo].[RIN1] r
|
||
JOIN [dbo].[ORIN] rh ON rh."DocEntry" = r."DocEntry"
|
||
WHERE r."ItemCode" = @itemCode AND rh."DocDate" BETWEEN @from AND @to AND rh."CANCELED" = 'N'), 0)
|
||
) AS "Qty"
|
||
`;
|
||
const r = await pool.request().input('itemCode', itemCode).input('from', from).input('to', to).query(sql);
|
||
return Number(r.recordset?.[0]?.Qty) || 0;
|
||
}
|
||
|
||
// On-hand quantity for one item, optionally restricted to one warehouse
|
||
// (blank/omitted = summed across all warehouses).
|
||
async function stockQty(pool, itemCode, whsCode) {
|
||
const req = pool.request().input('itemCode', itemCode);
|
||
let where = `"ItemCode" = @itemCode`;
|
||
if (whsCode) { req.input('whsCode', whsCode); where += ` AND "WhsCode" = @whsCode`; }
|
||
const r = await req.query(`SELECT ISNULL(SUM("OnHand"), 0) AS "Qty" FROM [dbo].[OITW] WHERE ${where}`);
|
||
return Number(r.recordset?.[0]?.Qty) || 0;
|
||
}
|
||
|
||
// GET /api/requirement-calc/line — one item's full computed row.
|
||
router.get('/line', verifyToken, async (req, res) => {
|
||
const { itemCode, periodAFrom, periodATo, periodBFrom, periodBTo, stockWhs, quarantineWhs, months } = req.query;
|
||
if (!itemCode) return res.status(400).json({ success: false, message: 'itemCode is required' });
|
||
|
||
// Which periods the admin has enabled — see Admin → System Settings.
|
||
const aOn = appSettings.reqCalcPeriodAEnabled();
|
||
const bOn = appSettings.reqCalcPeriodBEnabled();
|
||
if (aOn && (!periodAFrom || !periodATo)) return res.status(400).json({ success: false, message: 'Period A date range is required' });
|
||
if (bOn && (!periodBFrom || !periodBTo)) return res.status(400).json({ success: false, message: 'Period B date range is required' });
|
||
|
||
try {
|
||
const pool = await getPool();
|
||
const [qtyA, qtyB, stock, quarantine] = await Promise.all([
|
||
aOn ? saleQty(pool, itemCode, periodAFrom, periodATo) : Promise.resolve(0),
|
||
bOn ? saleQty(pool, itemCode, periodBFrom, periodBTo) : Promise.resolve(0),
|
||
stockQty(pool, itemCode, stockWhs || null),
|
||
quarantineWhs ? stockQty(pool, itemCode, quarantineWhs) : Promise.resolve(0),
|
||
]);
|
||
|
||
// Both enabled: existing behavior — (A+B) ÷ manual Months divisor.
|
||
// Only one enabled: that period's own qty ÷ the actual span of its own
|
||
// From/To dates (auto-calculated — the manual Months field doesn't apply).
|
||
let avgSalePerMonth;
|
||
if (aOn && bOn) {
|
||
const divisor = Math.max(1, parseFloat(months) || 10);
|
||
avgSalePerMonth = (qtyA + qtyB) / divisor;
|
||
} else if (aOn) {
|
||
avgSalePerMonth = qtyA / monthSpan(periodAFrom, periodATo);
|
||
} else {
|
||
avgSalePerMonth = qtyB / monthSpan(periodBFrom, periodBTo);
|
||
}
|
||
const totalFG = stock + quarantine;
|
||
const stockInHandMonths = avgSalePerMonth > 0 ? totalFG / avgSalePerMonth : 0;
|
||
const requirement = avgSalePerMonth * 2 - totalFG;
|
||
res.json({
|
||
success: true,
|
||
data: {
|
||
itemCode, saleQtyPeriodA: qtyA, saleQtyPeriodB: qtyB,
|
||
avgSalePerMonth, stock, quarantineStock: quarantine, totalFG,
|
||
stockInHandMonths, requirement,
|
||
},
|
||
});
|
||
} catch (err) { res.status(500).json({ success: false, message: err.message }); }
|
||
});
|
||
|
||
// ── Saved Item Groups (self-service presets for the Calculator) ──────────
|
||
// Shared company-wide: everyone with Requirements access can see and use any
|
||
// group; only its creator or an admin may edit/delete it.
|
||
const groupStore = require('../services/reqCalcGroupStore');
|
||
|
||
function canEditGroup(req, group) {
|
||
return req.user?.role === 'admin' || group.createdBy === req.user?.username;
|
||
}
|
||
|
||
router.get('/groups', verifyToken, async (req, res) => {
|
||
try {
|
||
const groups = await groupStore.listGroups(req.query.company || '');
|
||
res.json({ success: true, data: groups });
|
||
} catch (err) { res.status(500).json({ success: false, message: err.message }); }
|
||
});
|
||
|
||
router.post('/groups', verifyToken, async (req, res) => {
|
||
try {
|
||
const { name, items, company } = req.body || {};
|
||
if (!name || !String(name).trim()) return res.status(400).json({ success: false, message: 'Group name is required' });
|
||
if (!Array.isArray(items) || !items.length) return res.status(400).json({ success: false, message: 'Select at least one item' });
|
||
const group = await groupStore.createGroup({
|
||
name: String(name).trim(), items, company: company || '',
|
||
createdBy: req.user.username, createdByName: req.user.name || req.user.username,
|
||
});
|
||
res.json({ success: true, data: group });
|
||
} catch (err) { res.status(500).json({ success: false, message: err.message }); }
|
||
});
|
||
|
||
router.put('/groups/:id', verifyToken, async (req, res) => {
|
||
try {
|
||
const existing = await groupStore.getGroup(req.params.id);
|
||
if (!existing) return res.status(404).json({ success: false, message: 'Group not found' });
|
||
if (!canEditGroup(req, existing)) return res.status(403).json({ success: false, message: 'Only the creator or an admin can edit this group' });
|
||
const { name, items } = req.body || {};
|
||
if (!name || !String(name).trim()) return res.status(400).json({ success: false, message: 'Group name is required' });
|
||
if (!Array.isArray(items) || !items.length) return res.status(400).json({ success: false, message: 'Select at least one item' });
|
||
const group = await groupStore.updateGroup(req.params.id, { name: String(name).trim(), items });
|
||
res.json({ success: true, data: group });
|
||
} catch (err) { res.status(500).json({ success: false, message: err.message }); }
|
||
});
|
||
|
||
router.delete('/groups/:id', verifyToken, async (req, res) => {
|
||
try {
|
||
const existing = await groupStore.getGroup(req.params.id);
|
||
if (!existing) return res.status(404).json({ success: false, message: 'Group not found' });
|
||
if (!canEditGroup(req, existing)) return res.status(403).json({ success: false, message: 'Only the creator or an admin can delete this group' });
|
||
await groupStore.deleteGroup(req.params.id);
|
||
res.json({ success: true });
|
||
} catch (err) { res.status(500).json({ success: false, message: err.message }); }
|
||
});
|
||
|
||
module.exports = router;
|