106 lines
5.6 KiB
JavaScript
106 lines
5.6 KiB
JavaScript
'use strict';
|
|
// routes/prodDashboard.js — Production Dashboard analytics, open to ANY
|
|
// logged-in user (page visibility is controlled by the 'production-dashboard'
|
|
// MODULE grant, like every other production page). Read-only aggregates:
|
|
// - SAP: OWOR (orders), OIGN/IGN1 BaseType=202 (receipts from production)
|
|
// - Local: ZPRODUCTION_ORDERS (portal workflow stages)
|
|
const express = require('express');
|
|
const router = express.Router();
|
|
const { verifyToken } = require('../middleware/auth');
|
|
const { getPool } = require('../services/sqlPool');
|
|
const { getPool: getAppPool } = require('../services/appSqlPool');
|
|
|
|
async function hana(sqlText) {
|
|
const pool = await getPool();
|
|
return (await pool.request().query(sqlText)).recordset || [];
|
|
}
|
|
async function appq(sqlText) {
|
|
const pool = await getAppPool();
|
|
return (await pool.request().query(sqlText)).recordset || [];
|
|
}
|
|
|
|
// Superseded by the redesigned Production Dashboard (Work Order + Rejection
|
|
// Register + OEE) — this SAP OWOR/IGN1-based summary now backs the
|
|
// admin-only legacy page (public/production-dashboard-legacy.html) only,
|
|
// so it's admin-gated here too rather than relying on the client alone.
|
|
router.get('/summary', verifyToken, async (req, res) => {
|
|
if (req.user?.role !== 'admin')
|
|
return res.status(403).json({ success: false, message: 'This legacy dashboard is admin-only.' });
|
|
try {
|
|
const DS = /^\d{4}-\d{2}-\d{2}$/;
|
|
const iso = d => d.toISOString().slice(0, 10);
|
|
let from = DS.test(req.query.from || '') ? req.query.from : null;
|
|
let to = DS.test(req.query.to || '') ? req.query.to : null;
|
|
if (!from || !to) {
|
|
const now = new Date(); to = iso(now);
|
|
const f = new Date(now); f.setMonth(f.getMonth() - 6); from = iso(f);
|
|
}
|
|
const like = s => String(s || '').replace(/'/g, "''").slice(0, 60).trim();
|
|
const itemQ = like(req.query.itemQ);
|
|
const itemCond = itemQ ? ` AND (T1.ItemCode LIKE '%${itemQ}%' OR T1.Dscription LIKE '%${itemQ}%')` : '';
|
|
const rc = `T0.DocDate>='${from}' AND T0.DocDate<='${to}'`; // receipts range
|
|
const oc = `PostDate>='${from}' AND PostDate<='${to}'`; // orders range
|
|
|
|
// ── KPIs ─────────────────────────────────────────────────────────────
|
|
const k = (await hana(`SELECT
|
|
(SELECT ISNULL(SUM(T1.Quantity),0) FROM IGN1 T1 JOIN OIGN T0 ON T0.DocEntry=T1.DocEntry WHERE T1.BaseType=202 AND ${rc}) AS outputQty,
|
|
(SELECT COUNT(DISTINCT T0.DocEntry) FROM IGN1 T1 JOIN OIGN T0 ON T0.DocEntry=T1.DocEntry WHERE T1.BaseType=202 AND ${rc}) AS receiptDocs,
|
|
(SELECT COUNT(DISTINCT T1.ItemCode) FROM IGN1 T1 JOIN OIGN T0 ON T0.DocEntry=T1.DocEntry WHERE T1.BaseType=202 AND ${rc}) AS itemsProduced,
|
|
(SELECT COUNT(*) FROM OWOR WHERE ${oc}) AS ordersCreated,
|
|
(SELECT ISNULL(SUM(PlannedQty),0) FROM OWOR WHERE ${oc}) AS plannedQtyCreated,
|
|
(SELECT COUNT(*) FROM OWOR WHERE ${oc} AND Status='L') AS ordersClosed,
|
|
(SELECT COUNT(*) FROM OWOR WHERE Status='P') AS openPlanned,
|
|
(SELECT COUNT(*) FROM OWOR WHERE Status='R') AS openReleased`))[0] || {};
|
|
|
|
// ── Monthly production output (receipts from production) ─────────────
|
|
const monthly = await hana(`
|
|
SELECT YEAR(T0.DocDate) y, MONTH(T0.DocDate) m,
|
|
SUM(T1.Quantity) AS qty, COUNT(DISTINCT T0.DocEntry) AS docs
|
|
FROM IGN1 T1 JOIN OIGN T0 ON T0.DocEntry=T1.DocEntry
|
|
WHERE T1.BaseType=202 AND ${rc}
|
|
GROUP BY YEAR(T0.DocDate), MONTH(T0.DocDate)
|
|
ORDER BY y, m`);
|
|
|
|
// ── Top produced items in range (searchable) ─────────────────────────
|
|
const topItems = await hana(`
|
|
SELECT TOP ${itemQ ? 12 : 8} T1.ItemCode AS code, MAX(T1.Dscription) AS name, SUM(T1.Quantity) AS qty
|
|
FROM IGN1 T1 JOIN OIGN T0 ON T0.DocEntry=T1.DocEntry
|
|
WHERE T1.BaseType=202 AND ${rc}${itemCond}
|
|
GROUP BY T1.ItemCode ORDER BY qty DESC`);
|
|
|
|
// ── Portal workflow funnel (local tracking, point-in-time) ───────────
|
|
const STEPS = ['Release', 'Issuance', 'Receipt from Production', 'Transfer to Finished Goods', 'Close'];
|
|
let stages = STEPS.map((label, i) => ({ stage: i, label, count: 0 }));
|
|
let rejected = 0;
|
|
try {
|
|
const rows = await appq(`SELECT STAGE, STATUS, COUNT(*) n FROM [dbo].[ZPRODUCTION_ORDERS] WHERE IS_DELETED=0 AND STATUS IN ('IN_PROGRESS','REJECTED') GROUP BY STAGE, STATUS`);
|
|
rows.forEach(r => {
|
|
if (r.STATUS === 'REJECTED') rejected += Number(r.n) || 0;
|
|
else if (stages[r.STAGE]) stages[r.STAGE].count = Number(r.n) || 0;
|
|
});
|
|
} catch (e) { console.warn('[PROD-DASH] local stages failed (non-fatal):', e.message); }
|
|
|
|
res.json({ success: true, data: {
|
|
from, to,
|
|
kpi: {
|
|
outputQty: Number(k.outputQty) || 0,
|
|
receiptDocs: Number(k.receiptDocs) || 0,
|
|
itemsProduced: Number(k.itemsProduced) || 0,
|
|
ordersCreated: Number(k.ordersCreated) || 0,
|
|
plannedQtyCreated:Number(k.plannedQtyCreated) || 0,
|
|
ordersClosed: Number(k.ordersClosed) || 0,
|
|
openPlanned: Number(k.openPlanned) || 0,
|
|
openReleased: Number(k.openReleased) || 0,
|
|
},
|
|
monthly: monthly.map(r => ({ y: r.y, m: r.m, qty: Number(r.qty) || 0, docs: Number(r.docs) || 0 })),
|
|
topItems: topItems.map(r => ({ code: r.code, name: r.name, qty: Number(r.qty) || 0 })),
|
|
stages, rejected,
|
|
}});
|
|
} catch (err) {
|
|
console.error('[PROD-DASH] summary failed:', err.message);
|
|
res.status(500).json({ success: false, message: err.message });
|
|
}
|
|
});
|
|
|
|
module.exports = router;
|