223 lines
7.7 KiB
JavaScript
223 lines
7.7 KiB
JavaScript
// services/bomRequestStore.js
|
|
// Stores BOM Create/Update approval requests in SQL: ZBOM_REQUESTS
|
|
const sql = require('mssql');
|
|
const { DEFAULT_COMPANY } = require('./companyConfig');
|
|
|
|
const DB_SCHEMA = DEFAULT_COMPANY;
|
|
const TABLE = `[dbo].[ZBOM_REQUESTS]`;
|
|
const SEQ = `[dbo].[ZBOM_REQUESTS_SEQ]`;
|
|
|
|
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('[BOM-STORE] Checking table', TABLE, '...');
|
|
await exec(`
|
|
CREATE TABLE ${TABLE} (
|
|
ID INT IDENTITY(1,1) PRIMARY KEY,
|
|
TYPE NVARCHAR(10) NOT NULL,
|
|
ITEM_CODE NVARCHAR(50) NOT NULL,
|
|
ITEM_NAME NVARCHAR(200),
|
|
QTY DECIMAL(18,4) DEFAULT 1,
|
|
BOM_TYPE NVARCHAR(30) DEFAULT 'Production',
|
|
WAREHOUSE NVARCHAR(20),
|
|
DISTR_RULE NVARCHAR(50),
|
|
PROJECT NVARCHAR(50),
|
|
COMPONENTS NVARCHAR(MAX),
|
|
ORIGINAL_DATA NVARCHAR(MAX),
|
|
STATUS NVARCHAR(30) DEFAULT 'PENDING',
|
|
SUBMITTED_BY NVARCHAR(50),
|
|
SUBMITTED_NAME NVARCHAR(100),
|
|
SUBMITTED_AT DATETIME2,
|
|
APPROVAL_LOG NVARCHAR(MAX),
|
|
REJECTED_BY NVARCHAR(50),
|
|
REJECTED_AT DATETIME2,
|
|
SAP_PUSHED_AT DATETIME2,
|
|
SAP_PUSHED_BY NVARCHAR(50),
|
|
SAP_RESULT NVARCHAR(MAX),
|
|
COMPANY NVARCHAR(60)
|
|
)
|
|
`).catch(e => {
|
|
if (isAlreadyExists(e)) { console.log('[BOM-STORE] Table exists — OK'); }
|
|
else throw e;
|
|
});
|
|
// Migration: add COMPANY column if missing
|
|
await exec(`
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
|
|
WHERE TABLE_NAME='ZBOM_REQUESTS' AND COLUMN_NAME='COMPANY'
|
|
)
|
|
ALTER TABLE [dbo].[ZBOM_REQUESTS] ADD [COMPANY] NVARCHAR(50) DEFAULT '${DB_SCHEMA}'
|
|
`).catch(e => {
|
|
console.log('[BOM-STORE] COMPANY column migration:', e.message);
|
|
});
|
|
console.log('[BOM-STORE] ✅ Ready');
|
|
}
|
|
|
|
function toTs(isoStr) {
|
|
if (!isoStr) return null;
|
|
return isoStr.replace('T', ' ').replace('Z', '').substring(0, 23);
|
|
}
|
|
|
|
function fromRow(row) {
|
|
if (!row) return null;
|
|
return {
|
|
id: row.ID,
|
|
type: row.TYPE,
|
|
itemCode: row.ITEM_CODE || '',
|
|
itemName: row.ITEM_NAME || '',
|
|
qty: Number(row.QTY) || 1,
|
|
bomType: row.BOM_TYPE || 'Production',
|
|
warehouse: row.WAREHOUSE || '',
|
|
distrRule: row.DISTR_RULE || '',
|
|
project: row.PROJECT || '',
|
|
components: safeJson(row.COMPONENTS, []),
|
|
originalData: safeJson(row.ORIGINAL_DATA, null),
|
|
status: row.STATUS || 'PENDING',
|
|
submittedBy: row.SUBMITTED_BY || '',
|
|
submittedByName: row.SUBMITTED_NAME || '',
|
|
submittedAt: row.SUBMITTED_AT ? new Date(row.SUBMITTED_AT).toISOString() : null,
|
|
approvalLog: safeJson(row.APPROVAL_LOG, []),
|
|
rejectedBy: row.REJECTED_BY || null,
|
|
rejectedAt: row.REJECTED_AT ? new Date(row.REJECTED_AT).toISOString() : null,
|
|
sapPushedAt: row.SAP_PUSHED_AT ? new Date(row.SAP_PUSHED_AT).toISOString() : null,
|
|
sapPushedBy: row.SAP_PUSHED_BY || null,
|
|
sapResult: safeJson(row.SAP_RESULT, null),
|
|
company: row.COMPANY || '',
|
|
};
|
|
}
|
|
|
|
function safeJson(val, fallback) {
|
|
if (!val) return fallback;
|
|
try { return JSON.parse(val); } catch { return fallback; }
|
|
}
|
|
|
|
async function insertBomRequest(b) {
|
|
const now = toTs(new Date().toISOString());
|
|
|
|
// INSERT and SELECT SCOPE_IDENTITY() must be one batch — as two separate
|
|
// exec() calls, a pooled connection can route the second one to a
|
|
// DIFFERENT physical connection than the one that just inserted, where
|
|
// SCOPE_IDENTITY() correctly returns NULL (see workOrderStore.js's
|
|
// insertWorkOrder() for the full write-up of this bug class).
|
|
const idRows = await exec(`
|
|
INSERT INTO ${TABLE} (
|
|
TYPE, ITEM_CODE, ITEM_NAME, QTY, BOM_TYPE, WAREHOUSE, DISTR_RULE, PROJECT,
|
|
COMPONENTS, ORIGINAL_DATA, STATUS, SUBMITTED_BY, SUBMITTED_NAME, SUBMITTED_AT,
|
|
APPROVAL_LOG, COMPANY
|
|
) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);
|
|
SELECT SCOPE_IDENTITY() AS ID;
|
|
`, [
|
|
b.type, b.itemCode, b.itemName || '', Number(b.qty) || 1,
|
|
b.bomType || 'Production', b.warehouse || '', b.distrRule || '', b.project || '',
|
|
JSON.stringify(b.components || []),
|
|
b.originalData ? JSON.stringify(b.originalData) : null,
|
|
'PENDING', b.submittedBy, b.submittedByName || b.submittedBy, now,
|
|
JSON.stringify([]),
|
|
b.company || '',
|
|
]);
|
|
const id = idRows[0].ID;
|
|
return { id, ...b, status: 'PENDING', submittedAt: new Date().toISOString(), approvalLog: [] };
|
|
}
|
|
|
|
async function listRequests({ status, type, mine, company } = {}) {
|
|
// Fetch all then filter in JS for reliability
|
|
const all = await exec(`SELECT * FROM ${TABLE} ORDER BY SUBMITTED_AT DESC`);
|
|
return all.map(fromRow).filter(r => {
|
|
if (status && status !== 'ALL' && r.status !== status.toUpperCase()) return false;
|
|
if (type && r.type !== type.toUpperCase()) return false;
|
|
if (mine && r.submittedBy !== mine) return false;
|
|
if (company && r.company && r.company !== company) return false;
|
|
return true;
|
|
});
|
|
}
|
|
|
|
async function findById(id) {
|
|
const rows = await exec(`SELECT * FROM ${TABLE} WHERE ID = ?`, [parseInt(id)]);
|
|
return rows.length ? fromRow(rows[0]) : null;
|
|
}
|
|
|
|
async function updateRequest(id, patch) {
|
|
const FIELD_MAP = {
|
|
status: 'STATUS',
|
|
approvalLog: 'APPROVAL_LOG',
|
|
rejectedBy: 'REJECTED_BY',
|
|
rejectedAt: 'REJECTED_AT',
|
|
sapPushedAt: 'SAP_PUSHED_AT',
|
|
sapPushedBy: 'SAP_PUSHED_BY',
|
|
sapResult: 'SAP_RESULT',
|
|
};
|
|
|
|
function prepareValue(key, val) {
|
|
if (key === 'approvalLog' || key === 'sapResult') return JSON.stringify(val || []);
|
|
if (key === 'rejectedAt' || key === 'sapPushedAt') return toTs(val);
|
|
return val;
|
|
}
|
|
|
|
const sets = []; const vals = [];
|
|
for (const [jsKey, colName] of Object.entries(FIELD_MAP)) {
|
|
if (patch[jsKey] !== undefined) {
|
|
sets.push(`${colName} = ?`);
|
|
vals.push(prepareValue(jsKey, patch[jsKey]));
|
|
}
|
|
}
|
|
if (!sets.length) return;
|
|
vals.push(parseInt(id));
|
|
await exec(`UPDATE ${TABLE} SET ${sets.join(', ')} WHERE ID = ?`, vals);
|
|
}
|
|
|
|
async function getStats() {
|
|
const rows = await exec(`
|
|
SELECT STATUS, TYPE, COUNT(*) AS CNT
|
|
FROM ${TABLE}
|
|
GROUP BY STATUS, TYPE
|
|
ORDER BY STATUS, TYPE
|
|
`);
|
|
const stats = { byStatus: {}, byType: { CREATE: 0, UPDATE: 0 }, total: 0 };
|
|
rows.forEach(r => {
|
|
const s = r.STATUS || r.status || r.S;
|
|
const t = r.TYPE || r.type || r.T;
|
|
const c = Number(r.CNT || r.cnt || r.C) || 0;
|
|
stats.byStatus[s] = (stats.byStatus[s] || 0) + c;
|
|
stats.byType[t] = (stats.byType[t] || 0) + c;
|
|
stats.total += c;
|
|
});
|
|
return stats;
|
|
}
|
|
|
|
module.exports = { bootstrap, insertBomRequest, listRequests, findById, updateRequest, getStats }; |