115 lines
4.1 KiB
JavaScript
115 lines
4.1 KiB
JavaScript
// services/reqCalcGroupStore.js
|
|
// Saved "Item Groups" for the Requirement Calculator (public/requirements.html
|
|
// Calculator tab) — a user-defined preset list of item codes (e.g. "All Blood
|
|
// Bag Items") that can be picked to fetch all its items into the calculator
|
|
// worksheet at once, instead of searching and adding items one by one.
|
|
// Shared company-wide: every user with Requirements access can see and use
|
|
// any group. Only the creator or an admin may edit/delete one.
|
|
'use strict';
|
|
const sql = require('mssql');
|
|
|
|
const TABLE = `[dbo].[ZREQCALC_GROUPS]`;
|
|
|
|
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('[REQCALC-GROUPS] Checking table', TABLE, '...');
|
|
await exec(`
|
|
CREATE TABLE ${TABLE} (
|
|
ID INT IDENTITY(1,1) PRIMARY KEY,
|
|
NAME NVARCHAR(150) NOT NULL,
|
|
ITEMS NVARCHAR(MAX),
|
|
COMPANY NVARCHAR(60),
|
|
CREATED_BY NVARCHAR(50),
|
|
CREATED_NAME NVARCHAR(100),
|
|
CREATED_AT DATETIME2,
|
|
UPDATED_AT DATETIME2
|
|
)
|
|
`).catch(e => { if (isAlreadyExists(e)) { console.log('[REQCALC-GROUPS] Table exists — OK'); } else throw e; });
|
|
console.log('[REQCALC-GROUPS] ✅ Ready');
|
|
}
|
|
|
|
function toTs(d) { return d.toISOString().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,
|
|
name: row.NAME || '',
|
|
items: safeJson(row.ITEMS, []), // [{itemCode,itemName}]
|
|
company: row.COMPANY || '',
|
|
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,
|
|
};
|
|
}
|
|
|
|
// Shared company-wide — every group for the given company, regardless of creator.
|
|
async function listGroups(company) {
|
|
const rows = await exec(
|
|
`SELECT * FROM ${TABLE} ${company ? 'WHERE COMPANY = ?' : ''} ORDER BY NAME ASC`,
|
|
company ? [company] : []
|
|
);
|
|
return rows.map(fromRow);
|
|
}
|
|
|
|
async function getGroup(id) {
|
|
const rows = await exec(`SELECT * FROM ${TABLE} WHERE ID = ?`, [id]);
|
|
return fromRow(rows[0]);
|
|
}
|
|
|
|
async function createGroup(g) {
|
|
const now = toTs(new Date());
|
|
const rows = await exec(
|
|
`INSERT INTO ${TABLE} (NAME, ITEMS, COMPANY, CREATED_BY, CREATED_NAME, CREATED_AT, UPDATED_AT)
|
|
OUTPUT INSERTED.*
|
|
VALUES (?, ?, ?, ?, ?, ?, ?)`,
|
|
[g.name, JSON.stringify(g.items || []), g.company || '', g.createdBy || '', g.createdByName || '', now, now]
|
|
);
|
|
return fromRow(rows[0]);
|
|
}
|
|
|
|
async function updateGroup(id, g) {
|
|
const now = toTs(new Date());
|
|
const rows = await exec(
|
|
`UPDATE ${TABLE} SET NAME = ?, ITEMS = ?, UPDATED_AT = ? OUTPUT INSERTED.* WHERE ID = ?`,
|
|
[g.name, JSON.stringify(g.items || []), now, id]
|
|
);
|
|
return fromRow(rows[0]);
|
|
}
|
|
|
|
async function deleteGroup(id) {
|
|
await exec(`DELETE FROM ${TABLE} WHERE ID = ?`, [id]);
|
|
}
|
|
|
|
module.exports = { bootstrap, listGroups, getGroup, createGroup, updateGroup, deleteGroup };
|