306 lines
17 KiB
JavaScript
306 lines
17 KiB
JavaScript
'use strict';
|
||
const sql = require('mssql');
|
||
|
||
async function getPool() {
|
||
return require('./appSqlPool').getPool();
|
||
}
|
||
|
||
async function bootstrap() {
|
||
const pool = await getPool();
|
||
await pool.request().query(`
|
||
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='project_requests' AND xtype='U')
|
||
CREATE TABLE project_requests (
|
||
id INT IDENTITY(1,1) PRIMARY KEY,
|
||
project_code NVARCHAR(50) UNIQUE,
|
||
project_name NVARCHAR(200) NOT NULL,
|
||
project_category NCHAR(1) NOT NULL DEFAULT 'B',
|
||
total_days INT,
|
||
initiation_dt DATE,
|
||
est_start_dt DATE,
|
||
est_finish_dt DATE,
|
||
purpose NVARCHAR(MAX),
|
||
roi NVARCHAR(MAX),
|
||
labour_cost DECIMAL(15,2) DEFAULT 0,
|
||
material_cost DECIMAL(15,2) DEFAULT 0,
|
||
consultancy_fees DECIMAL(15,2) DEFAULT 0,
|
||
promotional_fees DECIMAL(15,2) DEFAULT 0,
|
||
other_cost DECIMAL(15,2) DEFAULT 0,
|
||
total_est_cost DECIMAL(15,2) DEFAULT 0,
|
||
department_name NVARCHAR(100),
|
||
prepared_by_name NVARCHAR(100),
|
||
prepared_by_desig NVARCHAR(100),
|
||
accountable_person NVARCHAR(100),
|
||
team_members NVARCHAR(MAX),
|
||
status NVARCHAR(50) DEFAULT 'DRAFT',
|
||
authorised_by NVARCHAR(100),
|
||
authorised_dt DATE,
|
||
comments NVARCHAR(MAX),
|
||
submitted_by NVARCHAR(100),
|
||
project_type NVARCHAR(20) DEFAULT 'NEW',
|
||
req_new_project NVARCHAR(MAX),
|
||
req_upgradation NVARCHAR(MAX),
|
||
time_cost_recovery NVARCHAR(200),
|
||
remarks_p2 NVARCHAR(MAX),
|
||
attachments NVARCHAR(MAX),
|
||
created_at DATETIME DEFAULT GETDATE(),
|
||
updated_at DATETIME DEFAULT GETDATE()
|
||
)
|
||
`);
|
||
|
||
// safe-add every column that might be missing on older DBs
|
||
const alter = pool.request();
|
||
for (const col of [
|
||
`IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='project_type') ALTER TABLE project_requests ADD project_type NVARCHAR(20) DEFAULT 'NEW'`,
|
||
`IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='req_new_project') ALTER TABLE project_requests ADD req_new_project NVARCHAR(MAX)`,
|
||
`IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='req_upgradation') ALTER TABLE project_requests ADD req_upgradation NVARCHAR(MAX)`,
|
||
`IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='time_cost_recovery') ALTER TABLE project_requests ADD time_cost_recovery NVARCHAR(200)`,
|
||
`IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='remarks_p2') ALTER TABLE project_requests ADD remarks_p2 NVARCHAR(MAX)`,
|
||
`IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='attachments') ALTER TABLE project_requests ADD attachments NVARCHAR(MAX)`,
|
||
// purchase
|
||
`IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='purchase_verified_dt') ALTER TABLE project_requests ADD purchase_verified_dt DATE`,
|
||
`IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='purchase_applicable') ALTER TABLE project_requests ADD purchase_applicable BIT DEFAULT 1`,
|
||
// engineering
|
||
`IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='engineering_verified_dt') ALTER TABLE project_requests ADD engineering_verified_dt DATE`,
|
||
`IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='engineering_applicable') ALTER TABLE project_requests ADD engineering_applicable BIT DEFAULT 1`,
|
||
// qa
|
||
`IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='qa_verified_dt') ALTER TABLE project_requests ADD qa_verified_dt DATE`,
|
||
`IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='qa_applicable') ALTER TABLE project_requests ADD qa_applicable BIT DEFAULT 1`,
|
||
// qc
|
||
`IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='qc_verified_dt') ALTER TABLE project_requests ADD qc_verified_dt DATE`,
|
||
`IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='qc_applicable') ALTER TABLE project_requests ADD qc_applicable BIT DEFAULT 1`,
|
||
// legal
|
||
`IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='legal_verified_dt') ALTER TABLE project_requests ADD legal_verified_dt DATE`,
|
||
`IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='legal_applicable') ALTER TABLE project_requests ADD legal_applicable BIT DEFAULT 1`,
|
||
// project owner
|
||
`IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='owner_approved_dt') ALTER TABLE project_requests ADD owner_approved_dt DATE`,
|
||
`IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='owner_applicable') ALTER TABLE project_requests ADD owner_applicable BIT DEFAULT 1`,
|
||
// plant manager
|
||
`IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='plant_applicable') ALTER TABLE project_requests ADD plant_applicable BIT DEFAULT 1`,
|
||
// finance (last step)
|
||
`IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='finance_verified_dt') ALTER TABLE project_requests ADD finance_verified_dt DATE`,
|
||
`IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='finance_verified_by') ALTER TABLE project_requests ADD finance_verified_by NVARCHAR(100)`,
|
||
`IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='finance_applicable') ALTER TABLE project_requests ADD finance_applicable BIT DEFAULT 1`,
|
||
]) await alter.query(col);
|
||
|
||
console.log('[ProjectStore] ✅ Table ready');
|
||
}
|
||
|
||
async function nextCode() {
|
||
const pool = await getPool();
|
||
// Financial year: April–March e.g. May 2026 → "26-27", Jan 2027 → "26-27"
|
||
const now = new Date();
|
||
const month = now.getMonth() + 1;
|
||
const year = now.getFullYear();
|
||
const fyStart = month >= 4 ? year : year - 1;
|
||
const fy = `${String(fyStart).slice(-2)}-${String(fyStart + 1).slice(-2)}`;
|
||
const prefix = `MIPL/${fy}/`;
|
||
|
||
// Max from existing MIPL codes (handles gaps after deletion)
|
||
const r1 = await pool.request().query(
|
||
`SELECT MAX(CAST(SUBSTRING(project_code, ${prefix.length + 1}, 10) AS INT)) AS mipl_max
|
||
FROM project_requests WHERE project_code LIKE '${prefix}%'`
|
||
);
|
||
// Total project count covers migration from old PRJ-YYYY format
|
||
const r2 = await pool.request().query(
|
||
`SELECT COUNT(*) AS total FROM project_requests`
|
||
);
|
||
const miplMax = r1.recordset[0].mipl_max || 0;
|
||
const total = r2.recordset[0].total || 0;
|
||
// Floor of 11 so the sequence never generates below 012
|
||
const next = Math.max(miplMax, total, 11) + 1;
|
||
return `${prefix}${String(next).padStart(3, '0')}`;
|
||
}
|
||
|
||
async function create(data) {
|
||
const pool = await getPool();
|
||
const code = await nextCode();
|
||
const r = pool.request();
|
||
r.input('code', sql.NVarChar, code);
|
||
r.input('name', sql.NVarChar, data.project_name);
|
||
r.input('cat', sql.NChar, data.project_category || 'B');
|
||
r.input('days', sql.Int, data.total_days || null);
|
||
r.input('init_dt', sql.Date, data.initiation_dt || null);
|
||
r.input('start_dt',sql.Date, data.est_start_dt || null);
|
||
r.input('end_dt', sql.Date, data.est_finish_dt || null);
|
||
r.input('purpose', sql.NVarChar, data.purpose || null);
|
||
r.input('roi', sql.NVarChar, data.roi || null);
|
||
r.input('labour', sql.Decimal, parseFloat(data.labour_cost) || 0);
|
||
r.input('material',sql.Decimal, parseFloat(data.material_cost) || 0);
|
||
r.input('consult', sql.Decimal, parseFloat(data.consultancy_fees) || 0);
|
||
r.input('promo', sql.Decimal, parseFloat(data.promotional_fees) || 0);
|
||
r.input('other', sql.Decimal, parseFloat(data.other_cost) || 0);
|
||
r.input('total', sql.Decimal, parseFloat(data.total_est_cost) || 0);
|
||
r.input('dept', sql.NVarChar, data.department_name || null);
|
||
r.input('pbname', sql.NVarChar, data.prepared_by_name || null);
|
||
r.input('pbdesig', sql.NVarChar, data.prepared_by_desig || null);
|
||
r.input('acct', sql.NVarChar, data.accountable_person || null);
|
||
r.input('team', sql.NVarChar, JSON.stringify(data.team_members || []));
|
||
r.input('status', sql.NVarChar, data.status || 'DRAFT');
|
||
r.input('subby', sql.NVarChar, data.submitted_by || null);
|
||
r.input('ptype', sql.NVarChar, data.project_type || 'NEW');
|
||
r.input('reqnew', sql.NVarChar, data.req_new_project || null);
|
||
r.input('requpg', sql.NVarChar, data.req_upgradation || null);
|
||
r.input('tcr', sql.NVarChar, data.time_cost_recovery || null);
|
||
r.input('rem2', sql.NVarChar, data.remarks_p2 || null);
|
||
// BIT flags — use explicit 0/1, never || null (0 is valid)
|
||
r.input('qa_app', sql.Bit, data.qa_applicable === 0 ? 0 : 1);
|
||
r.input('qc_app', sql.Bit, data.qc_applicable === 0 ? 0 : 1);
|
||
const res = await r.query(`
|
||
INSERT INTO project_requests (
|
||
project_code, project_name, project_category, total_days,
|
||
initiation_dt, est_start_dt, est_finish_dt, purpose, roi,
|
||
labour_cost, material_cost, consultancy_fees, promotional_fees,
|
||
other_cost, total_est_cost, department_name, prepared_by_name,
|
||
prepared_by_desig, accountable_person, team_members, status, submitted_by,
|
||
project_type, req_new_project, req_upgradation, time_cost_recovery, remarks_p2,
|
||
qa_applicable, qc_applicable
|
||
) VALUES (
|
||
@code,@name,@cat,@days,@init_dt,@start_dt,@end_dt,@purpose,@roi,
|
||
@labour,@material,@consult,@promo,@other,@total,
|
||
@dept,@pbname,@pbdesig,@acct,@team,@status,@subby,
|
||
@ptype,@reqnew,@requpg,@tcr,@rem2,
|
||
@qa_app,@qc_app
|
||
);
|
||
SELECT SCOPE_IDENTITY() AS id;
|
||
`);
|
||
return { id: res.recordset[0].id, project_code: code };
|
||
}
|
||
|
||
async function list({ status, search } = {}) {
|
||
const pool = await getPool();
|
||
const r = pool.request();
|
||
let q = `SELECT id, project_code, project_name, project_category, status,
|
||
department_name, prepared_by_name, est_start_dt, est_finish_dt,
|
||
total_est_cost, submitted_by, created_at
|
||
FROM project_requests WHERE 1=1`;
|
||
if (status) { r.input('st', sql.NVarChar, status); q += ' AND status=@st'; }
|
||
if (search) { r.input('s', sql.NVarChar, `%${search}%`); q += ' AND (project_name LIKE @s OR project_code LIKE @s)'; }
|
||
q += ' ORDER BY created_at DESC';
|
||
const res = await r.query(q);
|
||
return res.recordset || [];
|
||
}
|
||
|
||
async function findById(id) {
|
||
const pool = await getPool();
|
||
const res = await pool.request().input('id', sql.Int, parseInt(id))
|
||
.query('SELECT * FROM project_requests WHERE id=@id');
|
||
const row = res.recordset[0];
|
||
if (!row) return null;
|
||
try { row.team_members = JSON.parse(row.team_members || '[]'); } catch { row.team_members = []; }
|
||
try { row.attachments = JSON.parse(row.attachments || '[]'); } catch { row.attachments = []; }
|
||
return row;
|
||
}
|
||
|
||
async function update(id, data) {
|
||
const pool = await getPool();
|
||
const r = pool.request();
|
||
r.input('id', sql.Int, parseInt(id));
|
||
const sets = ['updated_at=GETDATE()'];
|
||
|
||
const map = {
|
||
project_name: ['nm', sql.NVarChar], project_category: ['cat', sql.NChar],
|
||
total_days: ['days',sql.Int], initiation_dt: ['idt', sql.Date],
|
||
est_start_dt: ['sdt', sql.Date], est_finish_dt: ['edt', sql.Date],
|
||
purpose: ['pur', sql.NVarChar], roi: ['roi', sql.NVarChar],
|
||
labour_cost: ['lab', sql.Decimal], material_cost: ['mat', sql.Decimal],
|
||
consultancy_fees: ['con', sql.Decimal], promotional_fees: ['pro', sql.Decimal],
|
||
other_cost: ['oth', sql.Decimal], total_est_cost: ['tot', sql.Decimal],
|
||
department_name: ['dep', sql.NVarChar], prepared_by_name: ['pbn', sql.NVarChar],
|
||
prepared_by_desig: ['pbd', sql.NVarChar], accountable_person: ['acc', sql.NVarChar],
|
||
status: ['sta', sql.NVarChar],
|
||
authorised_by: ['aub', sql.NVarChar], authorised_dt: ['aud', sql.Date],
|
||
comments: ['com', sql.NVarChar],
|
||
project_type: ['pty', sql.NVarChar], req_new_project: ['rnw', sql.NVarChar],
|
||
req_upgradation: ['rug', sql.NVarChar], time_cost_recovery: ['tcr', sql.NVarChar],
|
||
remarks_p2: ['rm2', sql.NVarChar],
|
||
// purchase
|
||
purchase_verified_dt: ['pvd', sql.Date], purchase_applicable: ['pap', sql.Bit],
|
||
// engineering
|
||
engineering_verified_dt: ['evd', sql.Date], engineering_applicable: ['eap', sql.Bit],
|
||
// qa
|
||
qa_verified_dt: ['qad', sql.Date], qa_applicable: ['qaa', sql.Bit],
|
||
// qc
|
||
qc_verified_dt: ['qcd', sql.Date], qc_applicable: ['qca', sql.Bit],
|
||
// legal
|
||
legal_verified_dt: ['lvd', sql.Date], legal_applicable: ['lap', sql.Bit],
|
||
// owner
|
||
owner_approved_dt: ['oad', sql.Date], owner_applicable: ['oap', sql.Bit],
|
||
// plant
|
||
plant_applicable: ['pla', sql.Bit],
|
||
// finance
|
||
finance_verified_dt: ['fvd', sql.Date], finance_verified_by: ['fvb', sql.NVarChar],
|
||
finance_applicable: ['fap', sql.Bit],
|
||
};
|
||
|
||
for (const [col, [param, type]] of Object.entries(map)) {
|
||
if (data[col] !== undefined) {
|
||
// Use ?? so 0/false are stored as-is (|| null would wrongly convert 0 → null for BIT columns)
|
||
const v = data[col];
|
||
r.input(param, type, (v !== null && v !== undefined && v !== '') ? v : null);
|
||
sets.push(`${col}=@${param}`);
|
||
}
|
||
}
|
||
if (data.team_members !== undefined) {
|
||
r.input('tm', sql.NVarChar, JSON.stringify(data.team_members));
|
||
sets.push('team_members=@tm');
|
||
}
|
||
if (data.attachments !== undefined) {
|
||
r.input('att', sql.NVarChar, JSON.stringify(data.attachments));
|
||
sets.push('attachments=@att');
|
||
}
|
||
await r.query(`UPDATE project_requests SET ${sets.join(',')} WHERE id=@id`);
|
||
}
|
||
|
||
// Approval step map: Purchase → Engineering → QA → QC → Legal → Owner → Plant → Finance
|
||
const STEP_MAP = [
|
||
{ key: 'purchase', from: 'PENDING', to: 'PURCHASE', dtCol: 'purchase_verified_dt', appCol: 'purchase_applicable' },
|
||
{ key: 'engineering', from: 'PURCHASE', to: 'ENGINEERING', dtCol: 'engineering_verified_dt', appCol: 'engineering_applicable' },
|
||
{ key: 'qa', from: 'ENGINEERING', to: 'QA_DONE', dtCol: 'qa_verified_dt', appCol: 'qa_applicable' },
|
||
{ key: 'qc', from: 'QA_DONE', to: 'QC_DONE', dtCol: 'qc_verified_dt', appCol: 'qc_applicable' },
|
||
{ key: 'legal', from: 'QC_DONE', to: 'LEGAL', dtCol: 'legal_verified_dt', appCol: 'legal_applicable' },
|
||
{ key: 'owner', from: 'LEGAL', to: 'OWNER', dtCol: 'owner_approved_dt', appCol: 'owner_applicable' },
|
||
{ key: 'plant', from: 'OWNER', to: 'PLANT', dtCol: 'authorised_dt', appCol: 'plant_applicable', nameCol: 'authorised_by' },
|
||
{ key: 'finance', from: 'PLANT', to: 'APPROVED', dtCol: 'finance_verified_dt', appCol: 'finance_applicable' },
|
||
];
|
||
|
||
async function stepApprove(id, { step, applicable, date, name }) {
|
||
const p = await findById(id);
|
||
if (!p) throw new Error('Project not found');
|
||
const s = STEP_MAP.find(m => m.key === step);
|
||
if (!s) throw new Error('Invalid approval step: ' + step);
|
||
if (p.status !== s.from) throw new Error(`Expected status ${s.from}, current is ${p.status}`);
|
||
const updates = { status: s.to };
|
||
if (applicable && date) updates[s.dtCol] = date;
|
||
if (!applicable && s.appCol) updates[s.appCol] = 0;
|
||
if (s.nameCol && name) updates[s.nameCol] = name;
|
||
await update(id, updates);
|
||
|
||
// Auto-advance through any consecutive steps pre-marked as N/A (e.g. QA/QC not applicable in team)
|
||
// Auto-advance through any consecutive steps pre-marked as N/A
|
||
for (let i = 0; i < STEP_MAP.length; i++) {
|
||
const current = await findById(id);
|
||
if (!current || current.status === 'APPROVED' || current.status === 'REJECTED') break;
|
||
const next = STEP_MAP.find(m => m.from === current.status);
|
||
if (!next) break;
|
||
// If no appCol, step is always required — stop
|
||
if (!next.appCol) break;
|
||
const appVal = current[next.appCol];
|
||
// SQL Server BIT returns as JS boolean (true/false) OR number (1/0).
|
||
// null/undefined = never explicitly set = treat as applicable, do NOT skip.
|
||
const isNotApplicable = (appVal === false || appVal === 0);
|
||
if (isNotApplicable) {
|
||
await update(id, { status: next.to });
|
||
} else {
|
||
break;
|
||
}
|
||
}
|
||
}
|
||
|
||
async function remove(id) {
|
||
const pool = await getPool();
|
||
await pool.request().input('id', sql.Int, parseInt(id))
|
||
.query('DELETE FROM project_requests WHERE id=@id');
|
||
}
|
||
|
||
module.exports = { bootstrap, create, list, findById, update, remove, stepApprove, nextCode };
|