77 lines
3.0 KiB
JavaScript
77 lines
3.0 KiB
JavaScript
// services/warehouseTranTypeStore.js
|
|
// Admin-managed mapping of Warehouse → Transaction Type (Complete='C' /
|
|
// Reject='R') for Receipt from Production. When a warehouse is mapped, the
|
|
// receipt page shows its transaction type automatically (the user can't pick
|
|
// it). Unmapped warehouses default to Complete. Company-scoped. App DB only.
|
|
'use strict';
|
|
const sql = require('mssql');
|
|
|
|
const TABLE = `[dbo].[ZWAREHOUSE_TRANTYPE]`;
|
|
|
|
let _conn = null;
|
|
async function getConn() {
|
|
if (_conn) return _conn;
|
|
_conn = await sql.connect({
|
|
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 },
|
|
});
|
|
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, (m, offset, string) => {
|
|
const i = (string.slice(0, offset).match(/\?/g) || []).length;
|
|
return `@param${i}`;
|
|
});
|
|
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('[WH-TRANTYPE-STORE] Checking table', TABLE, '...');
|
|
await exec(`
|
|
CREATE TABLE ${TABLE} (
|
|
ID INT IDENTITY(1,1) PRIMARY KEY,
|
|
COMPANY NVARCHAR(60) NOT NULL,
|
|
WHS_CODE NVARCHAR(20) NOT NULL,
|
|
TRAN_TYPE CHAR(1) NOT NULL, -- 'C' Complete | 'R' Reject
|
|
UPDATED_AT DATETIME2
|
|
)
|
|
`).catch(e => { if (isAlreadyExists(e)) console.log('[WH-TRANTYPE-STORE] Table exists — OK'); else throw e; });
|
|
await exec(`CREATE UNIQUE INDEX IDX_ZWH_TT_UN ON ${TABLE} ([COMPANY],[WHS_CODE])`).catch(() => {});
|
|
console.log('[WH-TRANTYPE-STORE] ✅ Ready');
|
|
}
|
|
|
|
// { whCode: 'C'|'R', ... } for a company
|
|
async function getMap(company) {
|
|
const rows = await exec(`SELECT WHS_CODE, TRAN_TYPE FROM ${TABLE} WHERE COMPANY = ?`, [company || '']);
|
|
const map = {};
|
|
rows.forEach(r => { map[r.WHS_CODE] = (r.TRAN_TYPE === 'R' ? 'R' : 'C'); });
|
|
return map;
|
|
}
|
|
|
|
// Replace the whole mapping for a company with the given { whCode: 'C'|'R' }.
|
|
async function setMap(company, map) {
|
|
const co = company || '';
|
|
const now = new Date().toISOString().replace('T', ' ').replace('Z', '').substring(0, 23);
|
|
await exec(`DELETE FROM ${TABLE} WHERE COMPANY = ?`, [co]);
|
|
const entries = Object.entries(map || {}).filter(([wh, t]) => wh && (t === 'C' || t === 'R'));
|
|
for (const [wh, t] of entries) {
|
|
await exec(`INSERT INTO ${TABLE} (COMPANY, WHS_CODE, TRAN_TYPE, UPDATED_AT) VALUES (?, ?, ?, ?)`,
|
|
[co, String(wh).trim(), t, now]);
|
|
}
|
|
return { count: entries.length };
|
|
}
|
|
|
|
module.exports = { bootstrap, getMap, setMap };
|