118 lines
4.9 KiB
JavaScript
118 lines
4.9 KiB
JavaScript
// services/itemGroupClassStore.js
|
|
// Admin-configured mapping: SAP Item Group (OITB.ItmsGrpCod) → which Work
|
|
// Order table its items should land in — RAW / PACK / COMPONENT. Lets the
|
|
// portal replace the hardcoded Solution-name/PK-code heuristics in
|
|
// work-order.html with an explicit, editable rule per item group.
|
|
const sql = require('mssql');
|
|
|
|
const TABLE = `[dbo].[ZITEM_GROUP_CLASS]`;
|
|
|
|
let _conn = null;
|
|
|
|
async function getConn() {
|
|
if (_conn) return _conn;
|
|
const config = {
|
|
server: process.env.APP_SQL_HOST,
|
|
port: parseInt(process.env.APP_SQL_PORT),
|
|
user: process.env.APP_SQL_USER,
|
|
password: process.env.APP_SQL_PASSWORD,
|
|
database: process.env.APP_SQL_DATABASE,
|
|
options: { encrypt: true, trustServerCertificate: true },
|
|
};
|
|
_conn = await sql.connect(config);
|
|
return _conn;
|
|
}
|
|
|
|
async function exec(sqlQuery, params = []) {
|
|
const conn = await getConn();
|
|
const request = conn.request();
|
|
params.forEach((param, index) => { request.input(`param${index}`, param); });
|
|
const replacedSql = sqlQuery.replace(/\?/g, (match, offset, string) => {
|
|
const paramIndex = (string.slice(0, offset).match(/\?/g) || []).length;
|
|
return `@param${paramIndex}`;
|
|
});
|
|
const result = await request.query(replacedSql);
|
|
return result.recordset || [];
|
|
}
|
|
|
|
function isAlreadyExists(e) {
|
|
const m = (e.message || '').toLowerCase();
|
|
return m.includes('already exists') || m.includes('duplicate') || m.includes('existing object') || m.includes('there is already an object');
|
|
}
|
|
|
|
async function bootstrap() {
|
|
console.log('[ITEM-GROUP-CLASS-STORE] Checking table', TABLE, '...');
|
|
await exec(`
|
|
CREATE TABLE ${TABLE} (
|
|
ITMSGRPCOD INT PRIMARY KEY,
|
|
ITMSGRPNAM NVARCHAR(100),
|
|
CLASSIFICATION NVARCHAR(20),
|
|
UPDATED_BY NVARCHAR(50),
|
|
UPDATED_AT DATETIME2
|
|
)
|
|
`).catch(e => {
|
|
if (isAlreadyExists(e)) { console.log('[ITEM-GROUP-CLASS-STORE] Table exists — OK'); }
|
|
else throw e;
|
|
});
|
|
console.log('[ITEM-GROUP-CLASS-STORE] ✅ Ready');
|
|
}
|
|
|
|
// { ITMSGRPCOD: 'RAW'|'PACK'|'COMPONENT' } for groups that have a classification set
|
|
async function getMap() {
|
|
const rows = await exec(`SELECT ITMSGRPCOD, CLASSIFICATION FROM ${TABLE} WHERE CLASSIFICATION IS NOT NULL AND CLASSIFICATION <> ''`);
|
|
const map = {};
|
|
rows.forEach(r => { map[r.ITMSGRPCOD] = r.CLASSIFICATION; });
|
|
return map;
|
|
}
|
|
|
|
async function getAll() {
|
|
const rows = await exec(`SELECT ITMSGRPCOD, ITMSGRPNAM, CLASSIFICATION FROM ${TABLE}`);
|
|
const map = {};
|
|
rows.forEach(r => { map[r.ITMSGRPCOD] = r.CLASSIFICATION || ''; });
|
|
return map;
|
|
}
|
|
|
|
// CLASSIFICATION is stored as a comma-separated set of tags — a group can be
|
|
// tagged BOTH 'RAW' and 'PACK' at once (its items then land in BOTH the Raw
|
|
// Material and Packing Material tables), or just 'COMPONENT' (mutually
|
|
// exclusive with RAW/PACK — the Components table is the "none of the above"
|
|
// bucket, so RAW/PACK always win if COMPONENT is also present).
|
|
// 'SFG' (Semi-Finished Good) is a separate, orthogonal axis — it marks a
|
|
// group's items as SFG PRODUCTS in their own right (used by the Batch
|
|
// Issuance SFG workflow to decide which items may be intimated without a
|
|
// Requirement), independent of how those same items route when they appear
|
|
// as an INGREDIENT inside another product's BOM — so it can coexist with
|
|
// RAW/PACK/COMPONENT rather than being mutually exclusive with them.
|
|
const VALID_TOKENS = new Set(['RAW', 'PACK', 'COMPONENT', 'SFG']);
|
|
function normalizeClassification(raw) {
|
|
const tokens = [...new Set((raw || '').split(',').map(s => s.trim().toUpperCase()).filter(t => VALID_TOKENS.has(t)))];
|
|
if (tokens.includes('COMPONENT') && (tokens.includes('RAW') || tokens.includes('PACK')))
|
|
return tokens.filter(t => t !== 'COMPONENT').join(',');
|
|
return tokens.join(',');
|
|
}
|
|
|
|
async function setClassification(itmsGrpCod, itmsGrpNam, classification, updatedBy) {
|
|
const cls = normalizeClassification(classification);
|
|
const idRows = await exec(`
|
|
MERGE ${TABLE} AS tgt
|
|
USING (SELECT ? AS ITMSGRPCOD) AS src
|
|
ON tgt.ITMSGRPCOD = src.ITMSGRPCOD
|
|
WHEN MATCHED THEN UPDATE SET
|
|
ITMSGRPNAM = ?, CLASSIFICATION = ?, UPDATED_BY = ?, UPDATED_AT = SYSDATETIME()
|
|
WHEN NOT MATCHED THEN INSERT (ITMSGRPCOD, ITMSGRPNAM, CLASSIFICATION, UPDATED_BY, UPDATED_AT)
|
|
VALUES (?, ?, ?, ?, SYSDATETIME());
|
|
SELECT ? AS ITMSGRPCOD;
|
|
`, [itmsGrpCod, itmsGrpNam || '', cls, updatedBy || '', itmsGrpCod, itmsGrpNam || '', cls, updatedBy || '', itmsGrpCod]);
|
|
return idRows[0]?.ITMSGRPCOD;
|
|
}
|
|
|
|
// SAP Item Group codes tagged 'SFG' — used server-side to verify a Batch
|
|
// Issuance SFG intimation's products are actually SFG-classified, rather
|
|
// than trusting the client's own tab/mode selection.
|
|
async function getSfgGroupCodes() {
|
|
const map = await getMap();
|
|
return Object.keys(map).filter(code => (map[code] || '').split(',').includes('SFG')).map(Number);
|
|
}
|
|
|
|
module.exports = { bootstrap, getMap, getAll, setClassification, getSfgGroupCodes, VALID_TOKENS };
|