116 lines
8.5 KiB
JavaScript
116 lines
8.5 KiB
JavaScript
// services/sales/reports.js
|
||
// msale's reports, rebuilt on the app DB (+ live SAP open quantities):
|
||
// dashboard — KPIs, status pipeline, monthly trend, top customers, days-to-close
|
||
// orderWise — one row per order
|
||
// itemWise — qty/value per item
|
||
// pending — open (un-delivered) qty per posted order line, from SAP RDR1.OpenQty
|
||
// customerPending — customer × item matrix of pending qty
|
||
// All are scoped exactly like the order list (division / mapped / own).
|
||
'use strict';
|
||
const { query, round2 } = require('./db');
|
||
const { statusLabel } = require('./constants');
|
||
const orders = require('./orders');
|
||
const masters = require('./masters');
|
||
const sap = require('./sap');
|
||
|
||
async function scopeWhere(actor, f, alias = 'o') {
|
||
const { prof, cards } = await orders.scopeFor(actor);
|
||
const w = [`${alias}.STATUS<>11`]; const p = [];
|
||
if (!prof.divisions.length) return { empty: true };
|
||
w.push(`${alias}.DIVISION_ID IN (${prof.divisions.map(Number).join(',')})`);
|
||
if (prof.scope === 'mapped') { if (!cards.length) return { empty: true }; w.push(`${alias}.CARD_CODE IN (${cards.map(() => '?').join(',')})`); p.push(...cards); }
|
||
if (prof.scope === 'own') { w.push(`${alias}.CREATED_BY_TYPE='user' AND ${alias}.CREATED_BY=?`); p.push(String(actor.user.id)); }
|
||
if (f.divisionId) { w.push(`${alias}.DIVISION_ID=?`); p.push(parseInt(f.divisionId)); }
|
||
if (f.soType) { w.push(`${alias}.SO_TYPE=?`); p.push(parseInt(f.soType)); }
|
||
if (f.cardCode) { w.push(`${alias}.CARD_CODE=?`); p.push(f.cardCode); }
|
||
if (f.statuses) { const st = String(f.statuses).split(',').map(Number).filter(Boolean); if (st.length) w.push(`${alias}.STATUS IN (${st.join(',')})`); }
|
||
if (f.from) { w.push(`${alias}.CREATED_AT>=?`); p.push(f.from); }
|
||
if (f.to) { w.push(`${alias}.CREATED_AT<DATEADD(day,1,CAST(? AS date))`); p.push(f.to); }
|
||
return { w, p };
|
||
}
|
||
|
||
async function dashboard(actor, f = {}) {
|
||
const sc = await scopeWhere(actor, f);
|
||
if (sc.empty) return { kpis: {}, byStatus: [], monthly: [], topCustomers: [], byDivision: [] };
|
||
const W = sc.w.join(' AND ');
|
||
const byStatus = await query(`SELECT o.STATUS, o.ORDER_TYPE, COUNT(*) N, SUM(o.GRAND_TOTAL) V FROM dbo.ZSO_ORDERS o WHERE ${W} GROUP BY o.STATUS, o.ORDER_TYPE`, sc.p);
|
||
const monthly = await query(`SELECT FORMAT(o.CREATED_AT,'yyyy-MM') M, COUNT(*) N, SUM(o.GRAND_TOTAL) V FROM dbo.ZSO_ORDERS o WHERE ${W} AND o.STATUS<>10
|
||
AND o.CREATED_AT>=DATEADD(month,-11,DATEFROMPARTS(YEAR(GETDATE()),MONTH(GETDATE()),1)) GROUP BY FORMAT(o.CREATED_AT,'yyyy-MM') ORDER BY M`, sc.p);
|
||
const top = await query(`SELECT TOP 10 o.CARD_CODE, MAX(o.CARD_NAME) CARD_NAME, COUNT(*) N, SUM(o.GRAND_TOTAL) V FROM dbo.ZSO_ORDERS o WHERE ${W} AND o.STATUS<>10
|
||
GROUP BY o.CARD_CODE ORDER BY V DESC`, sc.p);
|
||
const byDiv = await query(`SELECT o.DIVISION_ID, COUNT(*) N, SUM(o.GRAND_TOTAL) V FROM dbo.ZSO_ORDERS o WHERE ${W} AND o.STATUS<>10 GROUP BY o.DIVISION_ID`, sc.p);
|
||
// Days from order creation to SAP posting (turnaround), posted orders only.
|
||
const tat = await query(`SELECT AVG(CAST(DATEDIFF(hour,o.CREATED_AT,o.SAP_POSTED_AT) AS float))/24.0 AVGD, COUNT(*) N FROM dbo.ZSO_ORDERS o WHERE ${W} AND o.SAP_POSTED_AT IS NOT NULL`, sc.p);
|
||
const divs = Object.fromEntries((await masters.listDivisions(true)).map(d => [d.id, d.name]));
|
||
const sumBy = pred => byStatus.filter(pred).reduce((a, r) => ({ n: a.n + r.N, v: a.v + Number(r.V || 0) }), { n: 0, v: 0 });
|
||
return {
|
||
kpis: {
|
||
total: sumBy(r => r.STATUS !== 10), cancelled: sumBy(r => r.STATUS === 10),
|
||
awaitingAction: sumBy(r => [1, 2, 3, 5].includes(r.STATUS)), awaitingPayment: sumBy(r => [4, 6].includes(r.STATUS)),
|
||
readyForSap: sumBy(r => r.STATUS === 7), inSap: sumBy(r => r.STATUS === 8), closed: sumBy(r => r.STATUS === 9),
|
||
avgDaysToSap: tat[0] && tat[0].AVGD != null ? round2(tat[0].AVGD) : null,
|
||
},
|
||
byStatus: byStatus.map(r => ({ status: r.STATUS, label: statusLabel(r.STATUS, r.ORDER_TYPE), orderType: r.ORDER_TYPE, count: r.N, value: round2(r.V) })),
|
||
monthly: monthly.map(r => ({ month: r.M, count: r.N, value: round2(r.V) })),
|
||
topCustomers: top.map(r => ({ cardCode: r.CARD_CODE, cardName: r.CARD_NAME, count: r.N, value: round2(r.V) })),
|
||
byDivision: byDiv.map(r => ({ divisionId: r.DIVISION_ID, division: divs[r.DIVISION_ID] || r.DIVISION_ID, count: r.N, value: round2(r.V) })),
|
||
};
|
||
}
|
||
|
||
async function orderWise(actor, f = {}) {
|
||
const sc = await scopeWhere(actor, f);
|
||
if (sc.empty) return [];
|
||
const rows = await query(`SELECT o.ID, o.REF_NO, o.CREATED_AT, o.SO_TYPE, o.ORDER_TYPE, o.CARD_CODE, o.CARD_NAME, o.DIVISION_ID, o.CUST_ORDER_NO, o.DELIVERY_DATE,
|
||
o.SUB_TOTAL, o.GRAND_TOTAL, o.STATUS, o.SAP_DOC_NUM, o.SAP_POSTED_AT, o.PAID_AMOUNT, o.PAYMENT_REF,
|
||
(SELECT SUM(QTY) FROM dbo.ZSO_ORDER_ITEMS i WHERE i.ORDER_ID=o.ID AND i.ACTIVE=1) QTY
|
||
FROM dbo.ZSO_ORDERS o WHERE ${sc.w.join(' AND ')} ORDER BY o.ID DESC`, sc.p);
|
||
return rows.map(r => ({ id: r.ID, refNo: r.REF_NO, date: r.CREATED_AT, soType: r.SO_TYPE, orderType: r.ORDER_TYPE || (r.SO_TYPE === 1 ? 'TRADE' : 'IN-DIRECT'),
|
||
cardCode: r.CARD_CODE, cardName: r.CARD_NAME, divisionId: r.DIVISION_ID, custOrderNo: r.CUST_ORDER_NO || '', deliveryDate: r.DELIVERY_DATE, qty: Number(r.QTY || 0),
|
||
subTotal: Number(r.SUB_TOTAL), grandTotal: Number(r.GRAND_TOTAL), status: r.STATUS, statusLabel: statusLabel(r.STATUS, r.ORDER_TYPE), sapDocNum: r.SAP_DOC_NUM,
|
||
sapPostedAt: r.SAP_POSTED_AT, paidAmount: r.PAID_AMOUNT == null ? null : Number(r.PAID_AMOUNT), paymentRef: r.PAYMENT_REF || '' }));
|
||
}
|
||
|
||
async function itemWise(actor, f = {}) {
|
||
const sc = await scopeWhere(actor, f);
|
||
if (sc.empty) return [];
|
||
const rows = await query(`SELECT i.ITEM_CODE, MAX(i.DISPLAY_CODE) DISPLAY_CODE, MAX(i.DESCRIPTION) DESCRIPTION, COUNT(DISTINCT o.ID) ORDERS, SUM(i.QTY) QTY, SUM(i.LINE_TOTAL) VAL,
|
||
SUM(CASE WHEN i.SPECIAL_PRICE>0 THEN 1 ELSE 0 END) SPECIAL_LINES
|
||
FROM dbo.ZSO_ORDERS o JOIN dbo.ZSO_ORDER_ITEMS i ON i.ORDER_ID=o.ID AND i.ACTIVE=1 WHERE ${sc.w.join(' AND ')} AND o.STATUS<>10
|
||
GROUP BY i.ITEM_CODE ORDER BY VAL DESC`, sc.p);
|
||
return rows.map(r => ({ itemCode: r.ITEM_CODE, displayCode: r.DISPLAY_CODE, description: r.DESCRIPTION, orders: r.ORDERS, qty: Number(r.QTY), value: round2(r.VAL),
|
||
avgPrice: Number(r.QTY) ? round2(Number(r.VAL) / Number(r.QTY)) : 0, specialLines: r.SPECIAL_LINES }));
|
||
}
|
||
|
||
// Pending = qty still open on the SAP order lines of orders in SAP (status 8).
|
||
async function pending(actor, f = {}) {
|
||
const sc = await scopeWhere(actor, { ...f, statuses: '8' });
|
||
if (sc.empty) return [];
|
||
const ords = await query(`SELECT o.ID, o.REF_NO, o.CARD_CODE, o.CARD_NAME, o.DIVISION_ID, o.COMPANY, o.CREATED_AT, o.DELIVERY_DATE FROM dbo.ZSO_ORDERS o WHERE ${sc.w.join(' AND ')}`, sc.p);
|
||
if (!ords.length) return [];
|
||
const company = ords[0].COMPANY || (await masters.getSettings()).company;
|
||
const open = await sap.openQtyByWebNo(company, ords.map(o => o.ID));
|
||
const byId = Object.fromEntries(ords.map(o => [o.ID, o]));
|
||
return open.filter(l => l.openQty > 0 && (!f.itemCode || l.itemCode === f.itemCode)).map(l => {
|
||
const o = byId[l.webNo] || {};
|
||
return { orderId: l.webNo, refNo: o.REF_NO, cardCode: o.CARD_CODE, cardName: o.CARD_NAME, divisionId: o.DIVISION_ID, orderDate: o.CREATED_AT, deliveryDate: o.DELIVERY_DATE,
|
||
sapDocNum: l.sapDocNum, itemCode: l.itemCode, description: l.description, qty: l.qty, openQty: l.openQty, deliveredQty: round2(l.qty - l.openQty), openValue: round2(l.openQty * l.price),
|
||
ageDays: o.CREATED_AT ? Math.floor((Date.now() - new Date(o.CREATED_AT)) / 86400000) : null };
|
||
}).sort((a, b) => (b.ageDays || 0) - (a.ageDays || 0));
|
||
}
|
||
|
||
async function customerPending(actor, f = {}) {
|
||
const lines = await pending(actor, f);
|
||
const items = {}; const cust = {};
|
||
lines.forEach(l => {
|
||
items[l.itemCode] = items[l.itemCode] || { itemCode: l.itemCode, description: l.description, total: 0 };
|
||
items[l.itemCode].total = round2(items[l.itemCode].total + l.openQty);
|
||
cust[l.cardCode] = cust[l.cardCode] || { cardCode: l.cardCode, cardName: l.cardName, qty: {}, total: 0, value: 0 };
|
||
cust[l.cardCode].qty[l.itemCode] = round2((cust[l.cardCode].qty[l.itemCode] || 0) + l.openQty);
|
||
cust[l.cardCode].total = round2(cust[l.cardCode].total + l.openQty);
|
||
cust[l.cardCode].value = round2(cust[l.cardCode].value + l.openValue);
|
||
});
|
||
return { items: Object.values(items).sort((a, b) => b.total - a.total), customers: Object.values(cust).sort((a, b) => b.value - a.value) };
|
||
}
|
||
|
||
module.exports = { dashboard, orderWise, itemWise, pending, customerPending };
|