405 lines
19 KiB
JavaScript
405 lines
19 KiB
JavaScript
'use strict';
|
||
const express = require('express');
|
||
const router = express.Router();
|
||
const { verifyToken } = require('../middleware/auth');
|
||
const { getPool } = require('../services/sqlPool');
|
||
const costingStore = () => require('../services/costingStore');
|
||
|
||
// pl_account_map / pl_overrides live on the app's own database now — the
|
||
// ledger functions below used to JOIN them with OACT/JDT1 (real SAP data)
|
||
// in one query, which only worked when both lived on the same server. Now
|
||
// they fetch each side separately (SAP pool for OACT/JDT1, costingStore for
|
||
// the app tables) and merge in JS. This helper sums override_val for a set
|
||
// of account codes from costingStore.getOverrides()'s result.
|
||
function sumOverridesForCodes(overrides, codes) {
|
||
const set = new Set(codes);
|
||
return overrides
|
||
.filter(o => set.has(o.acct_code))
|
||
.reduce((s, o) => s + (parseFloat(o.override_val) || 0), 0);
|
||
}
|
||
|
||
const BB_GROUPS = ['FG BLOOD BAG'];
|
||
const CAPD_GROUPS = ['FG CAPD', 'FG CAPD ACCESSORIES'];
|
||
|
||
// Employee P&L accounts to exclude from cost
|
||
// 5103030xxx = S&M department accounts; 5240040xxx = other excluded codes
|
||
const EXCLUDE_EMP_CODES = [
|
||
'5103030001','5103030002','5103030003','5103030004','5103030005',
|
||
'5103030006','5103030008','5103030010','5103030012','5103030013',
|
||
'5240040001','5240040002','5240040003','5240040004','5240040005',
|
||
'5240040006','5240040008','5240040009','5240040010','5240040012',
|
||
'5240040013','5240040015',
|
||
];
|
||
|
||
// ── Production: stock transfer receipts into WH01 (TransType=67) via OINM ──
|
||
async function fetchProduction(pool, from, to) {
|
||
const r = await pool.request()
|
||
.input('from', from).input('to', to)
|
||
.query(`
|
||
SELECT O.ItemCode,
|
||
MAX(T0.ItemName) AS ItemDescription,
|
||
MAX(T2.ItmsGrpNam) AS ItemGroup,
|
||
SUM(O.InQty) AS ProductionQty
|
||
FROM OINM O
|
||
INNER JOIN OITM T0 ON T0.ItemCode = O.ItemCode
|
||
INNER JOIN OITB T2 ON T2.ItmsGrpCod = T0.ItmsGrpCod
|
||
WHERE O.TransType = 67
|
||
AND O.DocDate >= @from AND O.DocDate <= @to
|
||
AND O.Warehouse = '01'
|
||
AND T2.ItmsGrpNam IN ('FG BLOOD BAG','FG CAPD','FG CAPD ACCESSORIES')
|
||
GROUP BY O.ItemCode
|
||
ORDER BY MAX(T2.ItmsGrpNam), O.ItemCode
|
||
`);
|
||
return r.recordset || [];
|
||
}
|
||
|
||
// ── Sales: invoices minus credit notes ─────────────────────────────────────
|
||
async function fetchSales(pool, from, to) {
|
||
const r = await pool.request()
|
||
.input('from', from).input('to', to)
|
||
.query(`
|
||
SELECT ItemCode, SUM(SalesQty) AS SalesQty
|
||
FROM (
|
||
SELECT T0.ItemCode, SUM(T0.Quantity) AS SalesQty
|
||
FROM INV1 T0
|
||
INNER JOIN OINV T1 ON T1.DocEntry = T0.DocEntry
|
||
WHERE T1.DocDate >= @from AND T1.DocDate <= @to
|
||
AND T1.CANCELED = 'N' AND T0.ItemCode <> ''
|
||
GROUP BY T0.ItemCode
|
||
UNION ALL
|
||
SELECT T0.ItemCode, -SUM(T0.Quantity) AS SalesQty
|
||
FROM RIN1 T0
|
||
INNER JOIN ORIN T1 ON T1.DocEntry = T0.DocEntry
|
||
WHERE T1.DocDate >= @from AND T1.DocDate <= @to
|
||
AND T1.CANCELED = 'N' AND T0.ItemCode <> ''
|
||
GROUP BY T0.ItemCode
|
||
) S
|
||
GROUP BY ItemCode
|
||
`);
|
||
return r.recordset || [];
|
||
}
|
||
|
||
// ── BOM price per finished item (any price list, Price > 0) ─────────────────
|
||
async function fetchBOM(pool) {
|
||
const r = await pool.request().query(`
|
||
SELECT T1.Code AS ItemCode,
|
||
MIN(P.Price) AS BOMPrice
|
||
FROM OITT T1
|
||
INNER JOIN OITM T0 ON T0.ItemCode = T1.Code
|
||
LEFT JOIN ITM1 P ON P.ItemCode = T1.Code
|
||
LEFT JOIN OITB T2 ON T2.ItmsGrpCod = T0.ItmsGrpCod
|
||
WHERE T2.ItmsGrpNam IN ('FG BLOOD BAG','FG CAPD','FG CAPD ACCESSORIES')
|
||
AND P.Price > 0
|
||
GROUP BY T1.Code
|
||
`);
|
||
return r.recordset || [];
|
||
}
|
||
|
||
// ── Employee pool from P&L trial balance ────────────────────────────────────
|
||
// Formula: Employee-mapped accounts (excl. S&M names + listed codes)
|
||
// + MEDICAL EXPENSES (5190090026) + FESTIVAL EXPENSES (5190090029)
|
||
// + NET = period_debit - period_credit + provision (pl_overrides)
|
||
async function fetchEmployeePool(pool, from, to) {
|
||
try {
|
||
// pl_account_map (app DB): which accounts are on the "Employee" sheet.
|
||
const mapping = await costingStore().getMapping();
|
||
const empCodes = mapping
|
||
.filter(m => m.sheet === 'Employee' && !EXCLUDE_EMP_CODES.includes(m.acct_code))
|
||
.map(m => m.acct_code);
|
||
|
||
let net = 0;
|
||
if (empCodes.length) {
|
||
const req = pool.request().input('from', from).input('to', to);
|
||
const inList = empCodes.map((c, i) => { req.input(`e${i}`, c); return `@e${i}`; }).join(',');
|
||
const r = await req.query(`
|
||
SELECT
|
||
SUM(CASE WHEN T1.RefDate BETWEEN @from AND @to
|
||
THEN ISNULL(T1.Debit,0) - ISNULL(T1.Credit,0) ELSE 0 END) AS net
|
||
FROM OACT T0
|
||
LEFT JOIN JDT1 T1 ON T0.AcctCode = T1.Account
|
||
WHERE T0.AcctCode IN (${inList})
|
||
AND T0.AcctName NOT LIKE '%S&M%'
|
||
`);
|
||
net += parseFloat(r.recordset[0]?.net || 0);
|
||
}
|
||
|
||
// Medical Expenses + Festival Expenses: gross Debit only (not net)
|
||
const rMed = await pool.request()
|
||
.input('from', from).input('to', to)
|
||
.query(`
|
||
SELECT
|
||
SUM(CASE WHEN T1.RefDate BETWEEN @from AND @to
|
||
THEN ISNULL(T1.Debit,0) ELSE 0 END) AS net
|
||
FROM OACT T0
|
||
LEFT JOIN JDT1 T1 ON T0.AcctCode = T1.Account
|
||
WHERE T0.AcctCode IN ('5190090026', '5190090029')
|
||
`);
|
||
net += parseFloat(rMed.recordset[0]?.net || 0);
|
||
|
||
// pl_overrides (app DB): provisions for every matched account this period.
|
||
const overrides = await costingStore().getOverrides(from, to);
|
||
const prov = sumOverridesForCodes(overrides, [...empCodes, '5190090026', '5190090029']);
|
||
|
||
return net + prov;
|
||
} catch (_) {
|
||
return 0;
|
||
}
|
||
}
|
||
|
||
// Shared helper for the 4 fixed-account-list ledgers below: SAP debit-credit
|
||
// net for a set of account codes over the period, via the SAP pool.
|
||
async function fetchLedgerNet(pool, codes, from, to) {
|
||
const req = pool.request().input('fromDate', from).input('toDate', to);
|
||
const inList = codes.map((c, i) => { req.input(`c${i}`, c); return `@c${i}`; }).join(',');
|
||
const r = await req.query(`
|
||
SELECT
|
||
SUM(CASE WHEN T1.[RefDate] BETWEEN @fromDate AND @toDate
|
||
THEN ISNULL(T1.[Debit],0) - ISNULL(T1.[Credit],0) ELSE 0 END) AS net
|
||
FROM OACT T0
|
||
LEFT JOIN JDT1 T1 ON T0.[AcctCode] = T1.[Account]
|
||
WHERE T0.[AcctCode] IN (${inList})
|
||
`);
|
||
return parseFloat(r.recordset[0]?.net || 0);
|
||
}
|
||
|
||
// ── Boiler cost: mirrors P&L fetchTrialBalance formula exactly ───────────────
|
||
// NET = period_debit - period_credit + provision (pl_overrides.override_val)
|
||
async function fetchBoilerLedger(pool, from, to) {
|
||
try {
|
||
const codes = ['5110010010'];
|
||
const [net, overrides] = await Promise.all([
|
||
fetchLedgerNet(pool, codes, from, to),
|
||
costingStore().getOverrides(from, to),
|
||
]);
|
||
return net + sumOverridesForCodes(overrides, codes);
|
||
} catch (_) { return 0; }
|
||
}
|
||
|
||
// ── Power cost: GENSET (5110010008) + ELECTRICITY (5110010009) + provisions ───
|
||
async function fetchPowerLedger(pool, from, to) {
|
||
try {
|
||
const codes = ['5110010008', '5110010009'];
|
||
const [net, overrides] = await Promise.all([
|
||
fetchLedgerNet(pool, codes, from, to),
|
||
costingStore().getOverrides(from, to),
|
||
]);
|
||
return net + sumOverridesForCodes(overrides, codes);
|
||
} catch (_) { return 0; }
|
||
}
|
||
|
||
// ── R&D cost: R&D expense accounts + provisions ───────────────────────────────
|
||
async function fetchRdLedger(pool, from, to) {
|
||
try {
|
||
const codes = ['5240040018', '5240040019', '5240040020', '5240040021'];
|
||
const [net, overrides] = await Promise.all([
|
||
fetchLedgerNet(pool, codes, from, to),
|
||
costingStore().getOverrides(from, to),
|
||
]);
|
||
return net + sumOverridesForCodes(overrides, codes);
|
||
} catch (_) { return 0; }
|
||
}
|
||
|
||
// ── R&M cost: all Repair & Maintenance accounts + provisions ─────────────────
|
||
async function fetchRepairLedger(pool, from, to) {
|
||
try {
|
||
const codes = ['5190090010', '5190090011', '5190090012', '5190090013', '5190090028'];
|
||
const [net, overrides] = await Promise.all([
|
||
fetchLedgerNet(pool, codes, from, to),
|
||
costingStore().getOverrides(from, to),
|
||
]);
|
||
return net + sumOverridesForCodes(overrides, codes);
|
||
} catch (_) { return 0; }
|
||
}
|
||
|
||
// ── Split a pool into BB / CAPD using custom % or RM-based auto ──────────────
|
||
function splitPool(total, bbPctStr, bbRM, totalRM) {
|
||
const frac = bbPctStr != null
|
||
? Math.min(100, Math.max(0, parseFloat(bbPctStr) || 0)) / 100
|
||
: totalRM > 0 ? bbRM / totalRM : 0;
|
||
return [total * frac, total * (1 - frac)];
|
||
}
|
||
|
||
// ── Main data endpoint ───────────────────────────────────────────────────────
|
||
router.get('/data', verifyToken, async (req, res) => {
|
||
try {
|
||
const {
|
||
from, to, salaryPeriod, overrideEmpPool, overrideBoilerLedger, overridePowerLedger, overrideRepairLedger, overrideRdLedger,
|
||
bbEmpPct, bbQaPct, bbQcPct, bbBoilerPct, bbPowerPct, bbRepairPct, bbRdPct,
|
||
empPowerPct, qaPowerPct, qcPowerPct, boilerPowerPct, repairPowerPct, rdPowerPct,
|
||
} = req.query;
|
||
if (!from || !to)
|
||
return res.status(400).json({ success: false, message: 'from and to dates are required' });
|
||
|
||
const pool = await getPool();
|
||
const [production, sales, bom, liveEmpPool, liveBoilerLedger, livePowerLedger, liveRepairLedger, liveRdLedger] = await Promise.all([
|
||
fetchProduction(pool, from, to),
|
||
fetchSales(pool, from, to),
|
||
fetchBOM(pool),
|
||
overrideEmpPool != null ? Promise.resolve(null) : fetchEmployeePool(pool, from, to),
|
||
overrideBoilerLedger != null ? Promise.resolve(null) : fetchBoilerLedger(pool, from, to),
|
||
overridePowerLedger != null ? Promise.resolve(null) : fetchPowerLedger(pool, from, to),
|
||
overrideRepairLedger != null ? Promise.resolve(null) : fetchRepairLedger(pool, from, to),
|
||
overrideRdLedger != null ? Promise.resolve(null) : fetchRdLedger(pool, from, to),
|
||
]);
|
||
const boilerLedger = overrideBoilerLedger != null ? parseFloat(overrideBoilerLedger) : liveBoilerLedger;
|
||
const powerLedger = overridePowerLedger != null ? parseFloat(overridePowerLedger) : livePowerLedger;
|
||
const repairLedger = overrideRepairLedger != null ? parseFloat(overrideRepairLedger) : liveRepairLedger;
|
||
const rdLedger = overrideRdLedger != null ? parseFloat(overrideRdLedger) : liveRdLedger;
|
||
const plEmpPool = overrideEmpPool != null ? parseFloat(overrideEmpPool) : liveEmpPool;
|
||
|
||
const salesMap = {};
|
||
sales.forEach(s => { salesMap[s.ItemCode] = parseFloat(s.SalesQty) || 0; });
|
||
const bomMap = {};
|
||
bom.forEach(b => { bomMap[b.ItemCode] = parseFloat(b.BOMPrice) || 0; });
|
||
|
||
// Salary module: supports comma-separated multiple periods
|
||
let salBB = 0, salCAPD = 0, salQA = 0, salQC = 0, salBoiler = 0, salRD = 0, grandTotalSalary = 0;
|
||
if (salaryPeriod) {
|
||
try {
|
||
const ss = require('../services/salaryStore');
|
||
const periods = salaryPeriod.split(',').map(p => p.trim()).filter(Boolean);
|
||
for (const sp of periods) {
|
||
const summary = await ss.getSummary(sp);
|
||
// Case-insensitive lookup for each department
|
||
const dept = k => {
|
||
const entry = Object.entries(summary.total)
|
||
.find(([d]) => d.toLowerCase() === k);
|
||
return entry ? parseFloat(entry[1] || 0) : 0;
|
||
};
|
||
salBB += dept('blood bag');
|
||
salCAPD += dept('capd');
|
||
salQA += dept('qa');
|
||
salQC += dept('qc');
|
||
salBoiler += dept('boiler');
|
||
salRD += dept('r&d');
|
||
grandTotalSalary += Object.values(summary.total)
|
||
.reduce((s, v) => s + (parseFloat(v) || 0), 0);
|
||
}
|
||
} catch (_) {}
|
||
}
|
||
|
||
// Boiler: ledger (5110010010 + provision) + BOILER dept salary from salary module
|
||
const boilerSalary = salBoiler;
|
||
const totalBoiler = boilerLedger + boilerSalary;
|
||
// Power: GENSET (5110010008) + ELECTRICITY (5110010009) — no salary component
|
||
const totalPower = powerLedger;
|
||
// R&M: all Repair & Maintenance accounts — no salary component
|
||
const totalRepair = repairLedger;
|
||
// R&D: ledger (5240040018-21 + provisions) + R&D dept salary
|
||
const rdSalary = salRD;
|
||
const totalRd = rdLedger + rdSalary;
|
||
|
||
const bbItems = production.filter(p => BB_GROUPS.includes(p.ItemGroup));
|
||
const capdItems = production.filter(p => CAPD_GROUPS.includes(p.ItemGroup));
|
||
|
||
// RM Consumption per item = Production Qty × BOM Price × 115%
|
||
const rmOf = p =>
|
||
parseFloat(((parseFloat(p.ProductionQty) || 0) * (bomMap[p.ItemCode] || 0) * 1.15).toFixed(2));
|
||
|
||
// Section RM totals
|
||
const bbRM = bbItems.reduce((s, p) => s + rmOf(p), 0);
|
||
const capdRM = capdItems.reduce((s, p) => s + rmOf(p), 0);
|
||
const totalRM = bbRM + capdRM; // QA & QC use combined RM as base
|
||
|
||
// ── Distribute Power pool into other cost heads ───────────────────────────
|
||
const pctVal = v => Math.min(100, Math.max(0, parseFloat(v) || 0)) / 100;
|
||
const distPowerEmp = totalPower * pctVal(empPowerPct);
|
||
const distPowerQa = totalPower * pctVal(qaPowerPct);
|
||
const distPowerQc = totalPower * pctVal(qcPowerPct);
|
||
const distPowerBoiler = totalPower * pctVal(boilerPowerPct);
|
||
const distPowerRepair = totalPower * pctVal(repairPowerPct);
|
||
const distPowerRd = totalPower * pctVal(rdPowerPct);
|
||
const distPowerTotal = distPowerEmp + distPowerQa + distPowerQc + distPowerBoiler + distPowerRepair + distPowerRd;
|
||
const standalonePower = Math.max(0, totalPower - distPowerTotal);
|
||
|
||
// Adjusted pools: base + absorbed power share
|
||
const adjEmpPool = plEmpPool + distPowerEmp;
|
||
const adjQaPool = salQA + distPowerQa;
|
||
const adjQcPool = salQC + distPowerQc;
|
||
const adjBoilerPool = totalBoiler + distPowerBoiler;
|
||
const adjRepairPool = totalRepair + distPowerRepair;
|
||
const adjRdPool = totalRd + distPowerRd;
|
||
|
||
// ── Section pool allocation ────────────────────────────────────────────────
|
||
// Employee: custom % override OR salary-dept-ratio (BB/CAPD dept salary)
|
||
let bbEmpPool, capdEmpPool;
|
||
if (bbEmpPct != null) {
|
||
[bbEmpPool, capdEmpPool] = splitPool(adjEmpPool, bbEmpPct, bbRM, totalRM);
|
||
} else {
|
||
bbEmpPool = (salBB > 0 && grandTotalSalary > 0) ? adjEmpPool * salBB / grandTotalSalary : adjEmpPool;
|
||
capdEmpPool = (salCAPD > 0 && grandTotalSalary > 0) ? adjEmpPool * salCAPD / grandTotalSalary : 0;
|
||
}
|
||
// QA, QC, Boiler, Power: custom % override OR RM-proportional (auto)
|
||
const [bbQaPool, capdQaPool] = splitPool(adjQaPool, bbQaPct, bbRM, totalRM);
|
||
const [bbQcPool, capdQcPool] = splitPool(adjQcPool, bbQcPct, bbRM, totalRM);
|
||
const [bbBoilerPool, capdBoilerPool] = splitPool(adjBoilerPool, bbBoilerPct, bbRM, totalRM);
|
||
const [bbPowerPool, capdPowerPool] = splitPool(standalonePower,bbPowerPct, bbRM, totalRM);
|
||
const [bbRepairPool, capdRepairPool] = splitPool(adjRepairPool, bbRepairPct, bbRM, totalRM);
|
||
const [bbRdPool, capdRdPool] = splitPool(adjRdPool, bbRdPct, bbRM, totalRM);
|
||
|
||
// ── Build rows: per-item cost = sectionPool × itemRM / sectionRM ──────────
|
||
const buildRows = (items, sectionRM, empPool, qaPool, qcPool, boilerPool, powerPool, repairPool, rdPool) => items.map(p => {
|
||
const prodQty = parseFloat(p.ProductionQty) || 0;
|
||
const salesQty = salesMap[p.ItemCode] || 0;
|
||
const rmCost = rmOf(p);
|
||
const calc = pool => sectionRM > 0 ? parseFloat((pool * rmCost / sectionRM).toFixed(2)) : 0;
|
||
return {
|
||
itemCode: p.ItemCode,
|
||
itemDescription: p.ItemDescription,
|
||
itemGroup: p.ItemGroup,
|
||
productionQty: prodQty,
|
||
salesQty,
|
||
rmConsumption: rmCost,
|
||
employeeCost: calc(empPool),
|
||
qaCost: calc(qaPool),
|
||
qcCost: calc(qcPool),
|
||
boilerCost: calc(boilerPool),
|
||
powerCost: calc(powerPool),
|
||
repairCost: calc(repairPool),
|
||
rdCost: calc(rdPool),
|
||
};
|
||
});
|
||
|
||
const bloodbag = buildRows(bbItems, bbRM, bbEmpPool, bbQaPool, bbQcPool, bbBoilerPool, bbPowerPool, bbRepairPool, bbRdPool);
|
||
const capd = buildRows(capdItems, capdRM, capdEmpPool, capdQaPool, capdQcPool, capdBoilerPool, capdPowerPool, capdRepairPool, capdRdPool);
|
||
|
||
// Auto % for reference (returned so frontend can show baseline)
|
||
const rmBbPct = totalRM > 0 ? bbRM / totalRM * 100 : 0;
|
||
const empBbPct = grandTotalSalary > 0 ? salBB / grandTotalSalary * 100 : (bbRM > 0 ? rmBbPct : 0);
|
||
const pct = (part, total) => total > 0 ? part / total * 100 : 0;
|
||
const allocation = {
|
||
emp: { bbPool: bbEmpPool, capdPool: capdEmpPool, bbPct: pct(bbEmpPool, adjEmpPool), autoPct: empBbPct, powerAbsorbed: distPowerEmp },
|
||
qa: { bbPool: bbQaPool, capdPool: capdQaPool, bbPct: pct(bbQaPool, adjQaPool), autoPct: rmBbPct, powerAbsorbed: distPowerQa },
|
||
qc: { bbPool: bbQcPool, capdPool: capdQcPool, bbPct: pct(bbQcPool, adjQcPool), autoPct: rmBbPct, powerAbsorbed: distPowerQc },
|
||
boiler: { bbPool: bbBoilerPool, capdPool: capdBoilerPool, bbPct: pct(bbBoilerPool, adjBoilerPool), autoPct: rmBbPct, powerAbsorbed: distPowerBoiler },
|
||
power: { bbPool: bbPowerPool, capdPool: capdPowerPool, bbPct: pct(bbPowerPool, standalonePower), autoPct: rmBbPct, powerAbsorbed: 0 },
|
||
repair: { bbPool: bbRepairPool, capdPool: capdRepairPool, bbPct: pct(bbRepairPool, adjRepairPool), autoPct: rmBbPct, powerAbsorbed: distPowerRepair },
|
||
rd: { bbPool: bbRdPool, capdPool: capdRdPool, bbPct: pct(bbRdPool, adjRdPool), autoPct: rmBbPct, powerAbsorbed: distPowerRd },
|
||
};
|
||
|
||
res.json({
|
||
success: true,
|
||
data: { bloodbag, capd },
|
||
boilerInfo: { ledger: boilerLedger, salary: boilerSalary, total: totalBoiler },
|
||
powerInfo: { total: totalPower },
|
||
repairInfo: { total: totalRepair },
|
||
rdInfo: { ledger: rdLedger, salary: rdSalary, total: totalRd },
|
||
allocation,
|
||
});
|
||
} catch (err) {
|
||
res.status(500).json({ success: false, message: err.message });
|
||
}
|
||
});
|
||
|
||
// ── Salary periods (for the dropdown) ──────────────────────────────────────
|
||
router.get('/salary-periods', verifyToken, async (req, res) => {
|
||
try {
|
||
const periods = await require('../services/salaryStore').getPeriods();
|
||
res.json({ success: true, data: periods });
|
||
} catch (err) {
|
||
res.status(500).json({ success: false, message: err.message });
|
||
}
|
||
});
|
||
|
||
module.exports = router;
|