256 lines
10 KiB
JavaScript
256 lines
10 KiB
JavaScript
// services/requirementStore.js
|
|
// Stores logistics "Requirements Management" entries in SQL: ZREQUIREMENTS
|
|
// Ref No format: REQ-{MM}-{YY}-{NNNN} (NNNN = per month-year running number)
|
|
const sql = require('mssql');
|
|
|
|
const TABLE = `[dbo].[ZREQUIREMENTS]`;
|
|
|
|
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('[REQ-STORE] Checking table', TABLE, '...');
|
|
await exec(`
|
|
CREATE TABLE ${TABLE} (
|
|
ID INT IDENTITY(1,1) PRIMARY KEY,
|
|
REF_NO NVARCHAR(30) NOT NULL,
|
|
PERIOD_FROM DATE,
|
|
PERIOD_TO DATE,
|
|
LINES NVARCHAR(MAX),
|
|
STATUS NVARCHAR(30) DEFAULT 'OPEN',
|
|
REMARKS NVARCHAR(MAX),
|
|
CREATED_BY NVARCHAR(50),
|
|
CREATED_NAME NVARCHAR(100),
|
|
CREATED_AT DATETIME2,
|
|
UPDATED_AT DATETIME2,
|
|
IS_DELETED BIT DEFAULT 0,
|
|
COMPANY NVARCHAR(60)
|
|
)
|
|
`).catch(e => {
|
|
if (isAlreadyExists(e)) { console.log('[REQ-STORE] Table exists — OK'); }
|
|
else throw e;
|
|
});
|
|
// Migrations for existing tables
|
|
await exec(`
|
|
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ZREQUIREMENTS' AND COLUMN_NAME='IS_DELETED')
|
|
ALTER TABLE ${TABLE} ADD IS_DELETED BIT DEFAULT 0
|
|
`).catch(e => console.log('[REQ-STORE] IS_DELETED migration:', e.message));
|
|
await exec(`
|
|
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ZREQUIREMENTS' AND COLUMN_NAME='UPDATED_AT')
|
|
ALTER TABLE ${TABLE} ADD UPDATED_AT DATETIME2
|
|
`).catch(e => console.log('[REQ-STORE] UPDATED_AT migration:', e.message));
|
|
// Production ↔ Store review loop (Admin → System Settings → "Requirement
|
|
// — Store Review Workflow", optional/off by default): Production shares a
|
|
// Requirement with Store, Store cross-checks it against SAP's own MRP
|
|
// Wizard output and edits the line items accordingly, then reverts it back
|
|
// to Production — who then raises the Batch Intimation as normal.
|
|
// REVIEW_STAGE: 0 = not shared yet (or feature unused), 1 = shared with
|
|
// Store (pending their review), 2 = Store reverted — done, single round
|
|
// trip only (no back-and-forth). ORIGINAL_LINES snapshots what LINES
|
|
// looked like at the moment it was shared, so Production can see exactly
|
|
// what Store changed ("before/after") once it comes back.
|
|
const addCol = (col, ddl) => exec(`
|
|
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ZREQUIREMENTS' AND COLUMN_NAME='${col}')
|
|
ALTER TABLE ${TABLE} ADD ${ddl}
|
|
`).catch(e => console.log(`[REQ-STORE] ${col} migration:`, e.message));
|
|
await addCol('REVIEW_STAGE', 'REVIEW_STAGE INT DEFAULT 0');
|
|
await addCol('ORIGINAL_LINES', 'ORIGINAL_LINES NVARCHAR(MAX)');
|
|
await addCol('SHARED_BY', 'SHARED_BY NVARCHAR(50)');
|
|
await addCol('SHARED_NAME', 'SHARED_NAME NVARCHAR(100)');
|
|
await addCol('SHARED_AT', 'SHARED_AT DATETIME2');
|
|
await addCol('REVIEWED_BY', 'REVIEWED_BY NVARCHAR(50)');
|
|
await addCol('REVIEWED_NAME', 'REVIEWED_NAME NVARCHAR(100)');
|
|
await addCol('REVIEWED_AT', 'REVIEWED_AT DATETIME2');
|
|
await addCol('REVIEW_REMARKS', 'REVIEW_REMARKS NVARCHAR(MAX)');
|
|
console.log('[REQ-STORE] ✅ Ready');
|
|
}
|
|
|
|
function toTs(isoStr) {
|
|
if (!isoStr) return null;
|
|
return isoStr.replace('T', ' ').replace('Z', '').substring(0, 23);
|
|
}
|
|
|
|
function safeJson(val, fallback) {
|
|
if (!val) return fallback;
|
|
try { return JSON.parse(val); } catch { return fallback; }
|
|
}
|
|
|
|
function fromRow(row) {
|
|
if (!row) return null;
|
|
return {
|
|
id: row.ID,
|
|
refNo: row.REF_NO || '',
|
|
periodFrom: row.PERIOD_FROM ? new Date(row.PERIOD_FROM).toISOString().slice(0, 10) : null,
|
|
periodTo: row.PERIOD_TO ? new Date(row.PERIOD_TO).toISOString().slice(0, 10) : null,
|
|
lines: safeJson(row.LINES, []),
|
|
status: row.STATUS || 'OPEN',
|
|
remarks: row.REMARKS || '',
|
|
createdBy: row.CREATED_BY || '',
|
|
createdByName: row.CREATED_NAME || '',
|
|
createdAt: row.CREATED_AT ? new Date(row.CREATED_AT).toISOString() : null,
|
|
updatedAt: row.UPDATED_AT ? new Date(row.UPDATED_AT).toISOString() : null,
|
|
isDeleted: !!row.IS_DELETED,
|
|
company: row.COMPANY || '',
|
|
reviewStage: row.REVIEW_STAGE != null ? row.REVIEW_STAGE : 0,
|
|
originalLines: safeJson(row.ORIGINAL_LINES, null),
|
|
sharedBy: row.SHARED_BY || '',
|
|
sharedByName: row.SHARED_NAME || '',
|
|
sharedAt: row.SHARED_AT ? new Date(row.SHARED_AT).toISOString() : null,
|
|
reviewedBy: row.REVIEWED_BY || '',
|
|
reviewedByName: row.REVIEWED_NAME || '',
|
|
reviewedAt: row.REVIEWED_AT ? new Date(row.REVIEWED_AT).toISOString() : null,
|
|
reviewRemarks: row.REVIEW_REMARKS || '',
|
|
};
|
|
}
|
|
|
|
// Generate REQ-MM-YY-NNNN — NNNN is a running number within the current month/year.
|
|
async function generateRefNo(company) {
|
|
const now = new Date();
|
|
const mm = String(now.getMonth() + 1).padStart(2, '0');
|
|
const yy = String(now.getFullYear()).slice(-2);
|
|
const prefix = `REQ-${mm}-${yy}-`;
|
|
const rows = await exec(
|
|
`SELECT REF_NO FROM ${TABLE} WHERE REF_NO LIKE ? ${company ? 'AND COMPANY = ?' : ''}`,
|
|
company ? [prefix + '%', company] : [prefix + '%']
|
|
);
|
|
let max = 0;
|
|
rows.forEach(r => {
|
|
const n = parseInt((r.REF_NO || '').slice(prefix.length), 10);
|
|
if (!isNaN(n) && n > max) max = n;
|
|
});
|
|
const next = String(max + 1).padStart(4, '0');
|
|
return prefix + next;
|
|
}
|
|
|
|
async function insertRequirement(r) {
|
|
const now = toTs(new Date().toISOString());
|
|
const refNo = await generateRefNo(r.company);
|
|
// 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} (
|
|
REF_NO, PERIOD_FROM, PERIOD_TO, LINES, STATUS, REMARKS,
|
|
CREATED_BY, CREATED_NAME, CREATED_AT, COMPANY
|
|
) VALUES (?,?,?,?,?,?,?,?,?,?);
|
|
SELECT SCOPE_IDENTITY() AS ID;
|
|
`, [
|
|
refNo,
|
|
r.periodFrom || null,
|
|
r.periodTo || null,
|
|
JSON.stringify(r.lines || []),
|
|
'OPEN',
|
|
r.remarks || '',
|
|
r.createdBy,
|
|
r.createdByName || r.createdBy,
|
|
now,
|
|
r.company || '',
|
|
]);
|
|
const id = idRows[0].ID;
|
|
return { id, refNo, ...r, status: 'OPEN', createdAt: new Date().toISOString() };
|
|
}
|
|
|
|
async function listRequirements({ mine, company, status, includeDeleted } = {}) {
|
|
const all = await exec(`SELECT * FROM ${TABLE} ORDER BY CREATED_AT DESC`);
|
|
return all.map(fromRow).filter(r => {
|
|
if (!includeDeleted && r.isDeleted) return false;
|
|
if (status && status !== 'ALL' && r.status !== status.toUpperCase()) return false;
|
|
if (mine && r.createdBy !== 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 updateRequirement(id, patch) {
|
|
const now = toTs(new Date().toISOString());
|
|
await exec(`
|
|
UPDATE ${TABLE}
|
|
SET PERIOD_FROM = ?, PERIOD_TO = ?, LINES = ?, REMARKS = ?, UPDATED_AT = ?
|
|
WHERE ID = ? AND (IS_DELETED = 0 OR IS_DELETED IS NULL)
|
|
`, [
|
|
patch.periodFrom || null,
|
|
patch.periodTo || null,
|
|
JSON.stringify(patch.lines || []),
|
|
patch.remarks || '',
|
|
now,
|
|
parseInt(id),
|
|
]);
|
|
return findById(id);
|
|
}
|
|
|
|
async function updateStatus(id, status) {
|
|
await exec(`UPDATE ${TABLE} SET STATUS = ? WHERE ID = ?`, [String(status).toUpperCase(), parseInt(id)]);
|
|
}
|
|
|
|
async function softDelete(id) {
|
|
await exec(`UPDATE ${TABLE} SET IS_DELETED = 1, UPDATED_AT = ? WHERE ID = ?`,
|
|
[toTs(new Date().toISOString()), parseInt(id)]);
|
|
}
|
|
|
|
// Production → Store: snapshots the CURRENT lines into ORIGINAL_LINES (the
|
|
// "before" for the eventual before/after view) and moves to stage 1.
|
|
async function shareWithStore(id, { by, byName }) {
|
|
const now = toTs(new Date().toISOString());
|
|
const req = await findById(id);
|
|
if (!req) throw new Error('Requirement not found');
|
|
await exec(`
|
|
UPDATE ${TABLE} SET REVIEW_STAGE = 1, ORIGINAL_LINES = ?, SHARED_BY = ?, SHARED_NAME = ?, SHARED_AT = ?, UPDATED_AT = ?
|
|
WHERE ID = ?
|
|
`, [JSON.stringify(req.lines || []), by || '', byName || by || '', now, now, parseInt(id)]);
|
|
return findById(id);
|
|
}
|
|
|
|
// Store → Production: overwrites LINES with Store's edited version and moves
|
|
// to stage 2 (done — single round trip, no further back-and-forth).
|
|
async function revertToProduction(id, { by, byName, lines, remarks }) {
|
|
const now = toTs(new Date().toISOString());
|
|
await exec(`
|
|
UPDATE ${TABLE} SET REVIEW_STAGE = 2, LINES = ?, REVIEWED_BY = ?, REVIEWED_NAME = ?, REVIEWED_AT = ?, REVIEW_REMARKS = ?, UPDATED_AT = ?
|
|
WHERE ID = ?
|
|
`, [JSON.stringify(lines || []), by || '', byName || by || '', now, remarks || '', now, parseInt(id)]);
|
|
return findById(id);
|
|
}
|
|
|
|
module.exports = {
|
|
bootstrap, generateRefNo, insertRequirement,
|
|
listRequirements, findById, updateRequirement, updateStatus, softDelete,
|
|
shareWithStore, revertToProduction,
|
|
};
|