678 lines
33 KiB
JavaScript
678 lines
33 KiB
JavaScript
const { all, audit, json, now, one, parseJson, run, tx } = require('../db');
|
||
const { computeNextDue, projectUsageDue } = require('./pmScheduling');
|
||
|
||
const FREQUENCY_UNITS = ['days', 'weeks', 'months', 'quarters', 'semiannual', 'years'];
|
||
|
||
function todayStr() {
|
||
return new Date().toISOString().slice(0, 10);
|
||
}
|
||
|
||
function addInterval(dateStr, value, unit) {
|
||
const date = new Date(`${dateStr || todayStr()}T00:00:00`);
|
||
const v = Math.max(1, Number(value) || 1);
|
||
switch (unit) {
|
||
case 'days': date.setDate(date.getDate() + v); break;
|
||
case 'weeks': date.setDate(date.getDate() + v * 7); break;
|
||
case 'quarters': date.setMonth(date.getMonth() + v * 3); break;
|
||
case 'semiannual': date.setMonth(date.getMonth() + v * 6); break;
|
||
case 'years': date.setFullYear(date.getFullYear() + v); break;
|
||
case 'months':
|
||
default: date.setMonth(date.getMonth() + v); break;
|
||
}
|
||
return date.toISOString().slice(0, 10);
|
||
}
|
||
|
||
// ---- PM plan templates -----------------------------------------------------
|
||
|
||
function round2(value) {
|
||
return Math.round((Number(value || 0) + Number.EPSILON) * 100) / 100;
|
||
}
|
||
|
||
function yearOf(value) {
|
||
return value ? Number(String(value).slice(0, 4)) : null;
|
||
}
|
||
|
||
function planSteps(planId) {
|
||
return all('SELECT id, step_order, title, details, est_minutes FROM pm_plan_steps WHERE plan_id = ? ORDER BY step_order, id', [planId]);
|
||
}
|
||
|
||
function planComponents(planId) {
|
||
return all('SELECT id, sort_order, part_number, description, supplier, cost FROM pm_plan_components WHERE plan_id = ? ORDER BY sort_order, id', [planId]);
|
||
}
|
||
|
||
// Usage meters declared on a plan template (e.g. Operating Hours / hrs / due every 500).
|
||
function planMeters(planId) {
|
||
return all('SELECT id, sort_order, label, unit, usage_interval FROM pm_plan_meters WHERE plan_id = ? ORDER BY sort_order, id', [planId]);
|
||
}
|
||
|
||
// Per-schedule meters with their reading log, used by the engine and surfaced to the UI.
|
||
function assetPmMeters(apId) {
|
||
return all('SELECT * FROM asset_pm_meters WHERE asset_pm_id = ? ORDER BY sort_order, id', [apId]).map((m) => {
|
||
const readings = all(
|
||
'SELECT reading, reading_date FROM pm_meter_readings WHERE asset_pm_meter_id = ? ORDER BY reading_date, id',
|
||
[m.id]
|
||
);
|
||
return { ...m, readings, projected_usage_due_date: projectUsageDue(m, readings, todayStr()) };
|
||
});
|
||
}
|
||
|
||
function writePlanMeters(planId, meters) {
|
||
run('DELETE FROM pm_plan_meters WHERE plan_id = ?', [planId]);
|
||
(meters || []).forEach((meter, index) => {
|
||
const label = (meter.label || '').trim();
|
||
if (!label) return;
|
||
run(
|
||
'INSERT INTO pm_plan_meters (plan_id, sort_order, label, unit, usage_interval) VALUES (?, ?, ?, ?, ?)',
|
||
[planId, index, label, (meter.unit || '').trim() || null, Number(meter.usage_interval) || 0]
|
||
);
|
||
});
|
||
}
|
||
|
||
// Guidance photos for a plan. `withData` is false for list views (omits the base64 blob).
|
||
function planPhotos(planId, withData = true) {
|
||
const columns = withData ? 'id, name, mime_type, caption, data_base64, sort_order' : 'id, name, mime_type, caption, sort_order';
|
||
return all(`SELECT ${columns} FROM pm_plan_photos WHERE plan_id = ? ORDER BY sort_order, id`, [planId])
|
||
.map((p) => ({ id: p.id, name: p.name, mime: p.mime_type, caption: p.caption, ...(withData ? { data: p.data_base64 } : {}) }));
|
||
}
|
||
|
||
function stepsMinutes(steps) {
|
||
return (steps || []).reduce((sum, step) => sum + (Number(step.est_minutes) || 0), 0);
|
||
}
|
||
|
||
function componentsTotal(components) {
|
||
return round2((components || []).reduce((sum, component) => sum + (Number(component.cost) || 0), 0));
|
||
}
|
||
|
||
function planFromRow(row, { withPhotoData = true } = {}) {
|
||
if (!row) return null;
|
||
const components = planComponents(row.id);
|
||
const photos = planPhotos(row.id, withPhotoData);
|
||
return {
|
||
...row,
|
||
lifespan_adjust: Boolean(row.lifespan_adjust),
|
||
steps: planSteps(row.id),
|
||
components,
|
||
components_total: componentsTotal(components),
|
||
meters: planMeters(row.id),
|
||
photos,
|
||
photo_count: photos.length
|
||
};
|
||
}
|
||
|
||
function listPlans(companyId) {
|
||
return all('SELECT * FROM pm_plans WHERE (? IS NULL OR entity_id = ?) ORDER BY name', [companyId || null, companyId || null])
|
||
.map((row) => planFromRow(row, { withPhotoData: false }));
|
||
}
|
||
|
||
function getPlan(id) {
|
||
return planFromRow(one('SELECT * FROM pm_plans WHERE id = ?', [id]));
|
||
}
|
||
|
||
function writeSteps(planId, steps) {
|
||
run('DELETE FROM pm_plan_steps WHERE plan_id = ?', [planId]);
|
||
(steps || []).forEach((step, index) => {
|
||
const title = (step.title || '').trim();
|
||
if (!title) return;
|
||
const est = step.est_minutes === '' || step.est_minutes == null ? null : Number(step.est_minutes);
|
||
run('INSERT INTO pm_plan_steps (plan_id, step_order, title, details, est_minutes) VALUES (?, ?, ?, ?, ?)', [planId, index, title, step.details || null, est]);
|
||
});
|
||
}
|
||
|
||
function writeComponents(planId, components) {
|
||
run('DELETE FROM pm_plan_components WHERE plan_id = ?', [planId]);
|
||
(components || []).forEach((component, index) => {
|
||
const part = (component.part_number || '').trim();
|
||
const description = (component.description || '').trim();
|
||
if (!part && !description && !Number(component.cost)) return;
|
||
run(
|
||
'INSERT INTO pm_plan_components (plan_id, sort_order, part_number, description, supplier, cost) VALUES (?, ?, ?, ?, ?, ?)',
|
||
[planId, index, component.part_number || null, component.description || null, component.supplier || null, Number(component.cost) || 0]
|
||
);
|
||
});
|
||
}
|
||
|
||
// Reconcile guidance photos: existing items (carry an id) keep their stored blob and
|
||
// only update caption/order; new items (carry data) are inserted; missing ones are removed.
|
||
function writePhotos(planId, photos) {
|
||
const existing = all('SELECT id FROM pm_plan_photos WHERE plan_id = ?', [planId]).map((r) => r.id);
|
||
const keep = new Set();
|
||
(photos || []).forEach((photo, index) => {
|
||
if (photo.id && existing.includes(photo.id)) {
|
||
keep.add(photo.id);
|
||
run('UPDATE pm_plan_photos SET caption = ?, sort_order = ? WHERE id = ? AND plan_id = ?', [photo.caption || null, index, photo.id, planId]);
|
||
} else if (photo.data) {
|
||
const result = run(
|
||
'INSERT INTO pm_plan_photos (plan_id, name, mime_type, caption, data_base64, sort_order, created_at) VALUES (?, ?, ?, ?, ?, ?, ?)',
|
||
[planId, photo.name || null, photo.mime || photo.mime_type || 'image/jpeg', photo.caption || null, photo.data, index, now()]
|
||
);
|
||
keep.add(result.lastInsertRowid);
|
||
}
|
||
});
|
||
for (const id of existing) {
|
||
if (!keep.has(id)) run('DELETE FROM pm_plan_photos WHERE id = ?', [id]);
|
||
}
|
||
}
|
||
|
||
// Estimated minutes are derived from the step times when steps are supplied.
|
||
function resolveEstimatedMinutes(body, fallback) {
|
||
if (Array.isArray(body.steps)) {
|
||
const total = stepsMinutes(body.steps);
|
||
if (total > 0) return total;
|
||
}
|
||
if (body.estimated_minutes != null && body.estimated_minutes !== '') return Number(body.estimated_minutes);
|
||
return fallback ?? null;
|
||
}
|
||
|
||
function createPlan(body, userId, companyId) {
|
||
const ts = now();
|
||
return tx(() => {
|
||
const result = run(
|
||
`INSERT INTO pm_plans (entity_id, name, description, category, frequency_value, frequency_unit, estimated_minutes, instructions, lifespan_adjust, created_by, created_at, updated_at)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
|
||
[
|
||
companyId || null,
|
||
body.name || 'PM plan', body.description || null, body.category || null,
|
||
Number(body.frequency_value) || 1, FREQUENCY_UNITS.includes(body.frequency_unit) ? body.frequency_unit : 'months',
|
||
resolveEstimatedMinutes(body, null), body.instructions || null, body.lifespan_adjust ? 1 : 0,
|
||
userId, ts, ts
|
||
]
|
||
);
|
||
writeSteps(result.lastInsertRowid, body.steps);
|
||
writeComponents(result.lastInsertRowid, body.components);
|
||
writePlanMeters(result.lastInsertRowid, body.meters);
|
||
writePhotos(result.lastInsertRowid, body.photos);
|
||
audit(userId, 'create', 'pm_plan', result.lastInsertRowid, null, { name: body.name });
|
||
return getPlan(result.lastInsertRowid);
|
||
});
|
||
}
|
||
|
||
function updatePlan(id, body, userId) {
|
||
const before = one('SELECT * FROM pm_plans WHERE id = ?', [id]);
|
||
if (!before) return null;
|
||
return tx(() => {
|
||
run(
|
||
`UPDATE pm_plans SET name = ?, description = ?, category = ?, frequency_value = ?, frequency_unit = ?,
|
||
estimated_minutes = ?, instructions = ?, lifespan_adjust = ?, updated_at = ? WHERE id = ?`,
|
||
[
|
||
body.name ?? before.name, body.description ?? before.description, body.category ?? before.category,
|
||
body.frequency_value != null ? Number(body.frequency_value) : before.frequency_value,
|
||
FREQUENCY_UNITS.includes(body.frequency_unit) ? body.frequency_unit : before.frequency_unit,
|
||
resolveEstimatedMinutes(body, before.estimated_minutes),
|
||
body.instructions ?? before.instructions,
|
||
body.lifespan_adjust !== undefined ? (body.lifespan_adjust ? 1 : 0) : before.lifespan_adjust,
|
||
now(), id
|
||
]
|
||
);
|
||
if (Array.isArray(body.steps)) writeSteps(id, body.steps);
|
||
if (Array.isArray(body.components)) writeComponents(id, body.components);
|
||
if (Array.isArray(body.meters)) writePlanMeters(id, body.meters);
|
||
if (Array.isArray(body.photos)) writePhotos(id, body.photos);
|
||
audit(userId, 'update', 'pm_plan', id, before, { name: body.name });
|
||
return getPlan(id);
|
||
});
|
||
}
|
||
|
||
function deletePlan(id, userId) {
|
||
const before = one('SELECT * FROM pm_plans WHERE id = ?', [id]);
|
||
if (!before) return false;
|
||
run('DELETE FROM pm_plans WHERE id = ?', [id]);
|
||
audit(userId, 'delete', 'pm_plan', id, before, null);
|
||
return true;
|
||
}
|
||
|
||
// ---- Per-asset PM schedules ------------------------------------------------
|
||
|
||
function assetPmRows(assetId) {
|
||
const settings = engineSettings();
|
||
return all(
|
||
`SELECT ap.*, p.name AS plan_name, p.description AS plan_description, p.instructions AS plan_instructions,
|
||
u.name AS assigned_to_name, a.in_service_date AS asset_in_service_date, a.useful_life_months AS asset_useful_life_months
|
||
FROM asset_pm_plans ap
|
||
LEFT JOIN pm_plans p ON p.id = ap.plan_id
|
||
LEFT JOIN users u ON u.id = ap.assigned_to
|
||
LEFT JOIN assets a ON a.id = ap.asset_id
|
||
WHERE ap.asset_id = ?
|
||
ORDER BY ap.active DESC, ap.next_due_date IS NULL, ap.next_due_date`,
|
||
[assetId]
|
||
).map((row) => {
|
||
const components = row.plan_id ? planComponents(row.plan_id) : [];
|
||
const completions = all(
|
||
`SELECT c.id, c.completed_at, c.notes, c.notes_json, c.steps_json, c.cost, c.components_json,
|
||
c.rating_safety, c.rating_physical, c.rating_operating,
|
||
CASE WHEN c.signature IS NOT NULL THEN 1 ELSE 0 END AS has_signature, c.next_due_date,
|
||
u.name AS completed_by_name,
|
||
(SELECT COUNT(*) FROM pm_completion_photos ph WHERE ph.completion_id = c.id) AS photo_count
|
||
FROM pm_completions c LEFT JOIN users u ON u.id = c.completed_by
|
||
WHERE c.asset_pm_id = ? ORDER BY c.completed_at DESC LIMIT 12`,
|
||
[row.id]
|
||
).map((c) => ({
|
||
...c,
|
||
has_signature: Boolean(c.has_signature),
|
||
notes: parseJson(c.notes_json, c.notes ? [c.notes] : []),
|
||
steps: parseJson(c.steps_json, []),
|
||
components: parseJson(c.components_json, []),
|
||
ratings: { safety: c.rating_safety, physical: c.rating_physical, operating: c.rating_operating }
|
||
}));
|
||
const totalCost = round2(completions.reduce((sum, c) => sum + Number(c.cost || 0), 0));
|
||
const meters = assetPmMeters(row.id);
|
||
// Re-derive the per-signal breakdown for display (the persisted next_due_date stays authoritative).
|
||
const projection = computeNextDue(
|
||
{ lastDate: row.last_completed_date || row.start_date, frequency_value: row.frequency_value, frequency_unit: row.frequency_unit, lifespan_adjust: Boolean(row.lifespan_adjust) },
|
||
{ in_service_date: row.asset_in_service_date, useful_life_months: row.asset_useful_life_months },
|
||
meters,
|
||
settings,
|
||
todayStr()
|
||
);
|
||
return {
|
||
...row,
|
||
active: Boolean(row.active),
|
||
lifespan_adjust: Boolean(row.lifespan_adjust),
|
||
steps: row.plan_id ? planSteps(row.plan_id) : [],
|
||
plan_photos: row.plan_id ? planPhotos(row.plan_id) : [],
|
||
components,
|
||
meters,
|
||
signals: projection.signals,
|
||
due_driver: projection.driver,
|
||
expected_cost: componentsTotal(components),
|
||
total_cost: totalCost,
|
||
last_cost: completions[0] ? round2(completions[0].cost) : 0,
|
||
completions
|
||
};
|
||
});
|
||
}
|
||
|
||
function attachPlan(assetId, body, userId) {
|
||
const ts = now();
|
||
const plan = body.plan_id ? one('SELECT * FROM pm_plans WHERE id = ?', [body.plan_id]) : null;
|
||
const value = Number(body.frequency_value) || (plan ? plan.frequency_value : 1);
|
||
const unit = FREQUENCY_UNITS.includes(body.frequency_unit) ? body.frequency_unit : (plan ? plan.frequency_unit : 'months');
|
||
const startDate = body.start_date || todayStr();
|
||
const nextDue = body.next_due_date || startDate;
|
||
const lifespanAdjust = body.lifespan_adjust !== undefined ? (body.lifespan_adjust ? 1 : 0) : (plan && plan.lifespan_adjust ? 1 : 0);
|
||
const result = run(
|
||
`INSERT INTO asset_pm_plans (asset_id, plan_id, frequency_value, frequency_unit, start_date, end_date, next_due_date, assigned_to, active, notes, lifespan_adjust, created_at, updated_at)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?, 1, ?, ?, ?, ?)`,
|
||
[assetId, body.plan_id || null, value, unit, startDate, body.end_date || null, nextDue, body.assigned_to || null, body.notes || null, lifespanAdjust, ts, ts]
|
||
);
|
||
// Copy the plan's meter definitions onto the schedule; the first threshold is one interval out.
|
||
const meters = Array.isArray(body.meters) ? body.meters : (body.plan_id ? planMeters(body.plan_id) : []);
|
||
meters.forEach((meter, index) => {
|
||
const label = (meter.label || '').trim();
|
||
if (!label) return;
|
||
const interval = Number(meter.usage_interval) || 0;
|
||
run(
|
||
`INSERT INTO asset_pm_meters (asset_pm_id, sort_order, label, unit, usage_interval, baseline_reading, due_at_reading)
|
||
VALUES (?, ?, ?, ?, ?, 0, ?)`,
|
||
[result.lastInsertRowid, index, label, (meter.unit || '').trim() || null, interval, interval > 0 ? interval : null]
|
||
);
|
||
});
|
||
audit(userId, 'attach', 'asset_pm', result.lastInsertRowid, null, { asset_id: assetId, plan_id: body.plan_id });
|
||
return one('SELECT * FROM asset_pm_plans WHERE id = ?', [result.lastInsertRowid]);
|
||
}
|
||
|
||
function updateAssetPm(assetId, apId, body, userId) {
|
||
const before = one('SELECT * FROM asset_pm_plans WHERE id = ? AND asset_id = ?', [apId, assetId]);
|
||
if (!before) return null;
|
||
run(
|
||
`UPDATE asset_pm_plans SET frequency_value = ?, frequency_unit = ?, start_date = ?, end_date = ?,
|
||
next_due_date = ?, assigned_to = ?, active = ?, notes = ?, lifespan_adjust = ?, updated_at = ? WHERE id = ?`,
|
||
[
|
||
body.frequency_value != null ? Number(body.frequency_value) : before.frequency_value,
|
||
FREQUENCY_UNITS.includes(body.frequency_unit) ? body.frequency_unit : before.frequency_unit,
|
||
body.start_date ?? before.start_date,
|
||
body.end_date !== undefined ? body.end_date : before.end_date,
|
||
body.next_due_date ?? before.next_due_date,
|
||
body.assigned_to !== undefined ? body.assigned_to : before.assigned_to,
|
||
body.active === undefined ? before.active : (body.active ? 1 : 0),
|
||
body.notes ?? before.notes,
|
||
body.lifespan_adjust !== undefined ? (body.lifespan_adjust ? 1 : 0) : before.lifespan_adjust,
|
||
now(), apId
|
||
]
|
||
);
|
||
audit(userId, 'update', 'asset_pm', apId, before, body);
|
||
return one('SELECT * FROM asset_pm_plans WHERE id = ?', [apId]);
|
||
}
|
||
|
||
function detachAssetPm(assetId, apId, userId) {
|
||
const result = run('DELETE FROM asset_pm_plans WHERE id = ? AND asset_id = ?', [apId, assetId]);
|
||
if (!result.changes) return false;
|
||
audit(userId, 'detach', 'asset_pm', apId, null, { asset_id: assetId });
|
||
return true;
|
||
}
|
||
|
||
function clampRating(value) {
|
||
if (value == null || value === '') return null;
|
||
return Math.max(1, Math.min(5, Number(value)));
|
||
}
|
||
|
||
// Composite next-due for a schedule given the date it was last serviced. Loads the schedule's meters
|
||
// (with their reading history) and the owning asset's life, runs the earliest-wins engine, and applies
|
||
// the asset's maintenance end-date cutoff. Shared by completion and standalone reading logs.
|
||
function scheduleNextDue(ap, lastDate) {
|
||
const asset = one('SELECT in_service_date, useful_life_months FROM assets WHERE id = ?', [ap.asset_id]) || {};
|
||
const projection = computeNextDue(
|
||
{ lastDate, frequency_value: ap.frequency_value, frequency_unit: ap.frequency_unit, lifespan_adjust: Boolean(ap.lifespan_adjust) },
|
||
asset,
|
||
assetPmMeters(ap.id),
|
||
engineSettings(),
|
||
todayStr()
|
||
);
|
||
let nextDue = projection.next_due_date;
|
||
let active = ap.active;
|
||
if (ap.end_date && nextDue && nextDue > ap.end_date) {
|
||
nextDue = null;
|
||
active = 0; // schedule complete — past the asset's maintenance end date
|
||
}
|
||
return { nextDue, active };
|
||
}
|
||
|
||
// Record a meter reading against a schedule's meter (resets its baseline/threshold when this is a
|
||
// service completion). Insert into the log so the usage-rate projection stays current.
|
||
function recordMeterReading(meter, reading, readingDate, source, completionId, userId) {
|
||
const value = Number(reading);
|
||
if (!Number.isFinite(value)) return;
|
||
if (source === 'completion') {
|
||
// Completing service resets the cycle: the next threshold is one interval past this reading.
|
||
const dueAt = Number(meter.usage_interval) > 0 ? value + Number(meter.usage_interval) : null;
|
||
run('UPDATE asset_pm_meters SET baseline_reading = ?, due_at_reading = ?, last_reading = ?, last_reading_date = ? WHERE id = ?',
|
||
[value, dueAt, value, readingDate, meter.id]);
|
||
} else {
|
||
run('UPDATE asset_pm_meters SET last_reading = ?, last_reading_date = ? WHERE id = ?', [value, readingDate, meter.id]);
|
||
}
|
||
run('INSERT INTO pm_meter_readings (asset_pm_meter_id, reading, reading_date, source, completion_id, created_by, created_at) VALUES (?, ?, ?, ?, ?, ?, ?)',
|
||
[meter.id, value, readingDate, source, completionId || null, userId || null, now()]);
|
||
}
|
||
|
||
function completePm(assetId, apId, body, userId) {
|
||
const ap = one('SELECT * FROM asset_pm_plans WHERE id = ? AND asset_id = ?', [apId, assetId]);
|
||
if (!ap) return null;
|
||
if (!body.signature) throw Object.assign(new Error('A signature is required to complete a PM'), { status: 400 });
|
||
|
||
const ts = now();
|
||
const completedDate = (body.completed_at || ts).slice(0, 10);
|
||
const planComps = ap.plan_id ? planComponents(ap.plan_id) : [];
|
||
const components = Array.isArray(body.components) ? body.components : planComps;
|
||
const cost = body.cost != null && body.cost !== '' ? round2(body.cost) : componentsTotal(planComps);
|
||
const notesList = Array.isArray(body.notes_list) ? body.notes_list.filter((n) => String(n || '').trim()) : (body.notes ? [body.notes] : []);
|
||
const ratings = body.ratings || {};
|
||
const photos = Array.isArray(body.photos) ? body.photos.filter((p) => p && p.data) : [];
|
||
const meterReadings = Array.isArray(body.meter_readings) ? body.meter_readings : [];
|
||
|
||
return tx(() => {
|
||
const inserted = run(
|
||
`INSERT INTO pm_completions (asset_pm_id, completed_at, completed_by, notes, notes_json, steps_json, cost, components_json,
|
||
signature, rating_safety, rating_physical, rating_operating, next_due_date, created_at)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
|
||
[
|
||
apId, ts, userId, notesList.join('\n') || null, json(notesList), json(body.steps || []), cost, json(components),
|
||
body.signature, clampRating(ratings.safety), clampRating(ratings.physical), clampRating(ratings.operating), null, ts
|
||
]
|
||
);
|
||
for (const photo of photos) {
|
||
run('INSERT INTO pm_completion_photos (completion_id, name, mime_type, data_base64, created_at) VALUES (?, ?, ?, ?, ?)', [
|
||
inserted.lastInsertRowid, photo.name || null, photo.mime || photo.mime_type || 'image/jpeg', photo.data, ts
|
||
]);
|
||
}
|
||
// Reset each meter's cycle from the reading captured during service.
|
||
for (const entry of meterReadings) {
|
||
const meter = one('SELECT * FROM asset_pm_meters WHERE id = ? AND asset_pm_id = ?', [entry.asset_pm_meter_id, apId]);
|
||
if (meter && entry.reading != null && entry.reading !== '') {
|
||
recordMeterReading(meter, entry.reading, completedDate, 'completion', inserted.lastInsertRowid, userId);
|
||
}
|
||
}
|
||
// Earliest-wins next-due now reflects the reset meters and the lifespan curve.
|
||
const { nextDue, active } = scheduleNextDue(ap, completedDate);
|
||
run('UPDATE pm_completions SET next_due_date = ? WHERE id = ?', [nextDue, inserted.lastInsertRowid]);
|
||
run('UPDATE asset_pm_plans SET last_completed_date = ?, next_due_date = ?, active = ?, updated_at = ? WHERE id = ?', [completedDate, nextDue, active, ts, apId]);
|
||
audit(userId, 'complete', 'asset_pm', apId, null, { next_due_date: nextDue, completion_id: inserted.lastInsertRowid });
|
||
return one('SELECT * FROM asset_pm_plans WHERE id = ?', [apId]);
|
||
});
|
||
}
|
||
|
||
// Standalone usage reading (quick-log / inspection) — updates the meter's current reading and
|
||
// recomputes the schedule's next-due so a faster-than-expected usage rate pulls service in sooner.
|
||
function logReading(assetId, apId, body, userId) {
|
||
const ap = one('SELECT * FROM asset_pm_plans WHERE id = ? AND asset_id = ?', [apId, assetId]);
|
||
if (!ap) return null;
|
||
const meter = one('SELECT * FROM asset_pm_meters WHERE id = ? AND asset_pm_id = ?', [body.asset_pm_meter_id, apId]);
|
||
if (!meter) throw Object.assign(new Error('Meter not found on this schedule'), { status: 404 });
|
||
if (body.reading == null || body.reading === '' || !Number.isFinite(Number(body.reading))) {
|
||
throw Object.assign(new Error('A numeric reading is required'), { status: 400 });
|
||
}
|
||
const readingDate = (body.reading_date || now()).slice(0, 10);
|
||
const source = body.source === 'inspection' ? 'inspection' : 'manual';
|
||
return tx(() => {
|
||
recordMeterReading(meter, body.reading, readingDate, source, null, userId);
|
||
const lastDate = ap.last_completed_date || ap.start_date || readingDate;
|
||
const { nextDue, active } = scheduleNextDue(ap, lastDate);
|
||
run('UPDATE asset_pm_plans SET next_due_date = ?, active = ?, updated_at = ? WHERE id = ?', [nextDue, active, now(), apId]);
|
||
audit(userId, 'reading', 'asset_pm', apId, null, { meter: meter.label, reading: Number(body.reading), next_due_date: nextDue });
|
||
return one('SELECT * FROM asset_pm_plans WHERE id = ?', [apId]);
|
||
});
|
||
}
|
||
|
||
function listCompletions(query = {}) {
|
||
return all(
|
||
`SELECT c.id, c.completed_at, c.cost, c.notes_json, c.rating_safety, c.rating_physical, c.rating_operating,
|
||
CASE WHEN c.signature IS NOT NULL THEN 1 ELSE 0 END AS has_signature,
|
||
u.name AS completed_by_name, a.asset_id AS asset_code, a.description AS asset_description, p.name AS plan_name,
|
||
(SELECT COUNT(*) FROM pm_completion_photos ph WHERE ph.completion_id = c.id) AS photo_count
|
||
FROM pm_completions c
|
||
JOIN asset_pm_plans ap ON ap.id = c.asset_pm_id
|
||
JOIN assets a ON a.id = ap.asset_id
|
||
LEFT JOIN pm_plans p ON p.id = ap.plan_id
|
||
LEFT JOIN users u ON u.id = c.completed_by
|
||
WHERE (? IS NULL OR ap.asset_id = ?)
|
||
ORDER BY c.completed_at DESC, c.id DESC
|
||
LIMIT 500`,
|
||
[query.asset_id || null, query.asset_id || null]
|
||
).map((r) => ({ ...r, has_signature: Boolean(r.has_signature), notes: parseJson(r.notes_json, []) }));
|
||
}
|
||
|
||
function getCompletion(id) {
|
||
const row = one(
|
||
`SELECT c.*, u.name AS completed_by_name, a.asset_id AS asset_code, a.description AS asset_description, p.name AS plan_name
|
||
FROM pm_completions c
|
||
JOIN asset_pm_plans ap ON ap.id = c.asset_pm_id
|
||
JOIN assets a ON a.id = ap.asset_id
|
||
LEFT JOIN pm_plans p ON p.id = ap.plan_id
|
||
LEFT JOIN users u ON u.id = c.completed_by
|
||
WHERE c.id = ?`,
|
||
[id]
|
||
);
|
||
if (!row) return null;
|
||
return {
|
||
id: row.id,
|
||
asset_code: row.asset_code,
|
||
asset_description: row.asset_description,
|
||
plan_name: row.plan_name,
|
||
completed_at: row.completed_at,
|
||
completed_by_name: row.completed_by_name,
|
||
cost: row.cost,
|
||
notes: parseJson(row.notes_json, row.notes ? [row.notes] : []),
|
||
steps: parseJson(row.steps_json, []),
|
||
components: parseJson(row.components_json, []),
|
||
signature: row.signature || null,
|
||
ratings: { safety: row.rating_safety, physical: row.rating_physical, operating: row.rating_operating },
|
||
photos: all('SELECT id, name, mime_type, data_base64 FROM pm_completion_photos WHERE completion_id = ? ORDER BY id', [row.id])
|
||
.map((p) => ({ id: p.id, name: p.name, mime: p.mime_type, data: p.data_base64 }))
|
||
};
|
||
}
|
||
|
||
// ---- Settings & alert candidates ------------------------------------------
|
||
|
||
function getSettings() {
|
||
const rows = all("SELECT key, value FROM app_settings WHERE key LIKE 'pm_%'");
|
||
const map = Object.fromEntries(rows.map((r) => [r.key, r.value]));
|
||
return {
|
||
pm_default_frequency_value: Number(map.pm_default_frequency_value || 3),
|
||
pm_default_frequency_unit: map.pm_default_frequency_unit || 'months',
|
||
pm_lead_days: Number(map.pm_lead_days || 7),
|
||
pm_usage_tracking_enabled: map.pm_usage_tracking_enabled === '1',
|
||
pm_lifespan_curve_enabled: map.pm_lifespan_curve_enabled === '1',
|
||
pm_lifespan_min_factor: map.pm_lifespan_min_factor != null ? Number(map.pm_lifespan_min_factor) : 0.5,
|
||
pm_lifespan_curve_exponent: map.pm_lifespan_curve_exponent != null ? Number(map.pm_lifespan_curve_exponent) : 2,
|
||
// Nightly recompute job: refreshes projected due-dates so dynamic schedules don't drift between
|
||
// completions/readings. pm_last_recompute_at is job-managed (read-only to clients).
|
||
pm_recompute_enabled: map.pm_recompute_enabled === '1',
|
||
pm_recompute_hour: map.pm_recompute_hour != null ? Number(map.pm_recompute_hour) : 2,
|
||
pm_last_recompute_at: map.pm_last_recompute_at || null
|
||
};
|
||
}
|
||
|
||
function saveSettings(body, userId) {
|
||
const set = (k, v) => run('INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value, updated_at = excluded.updated_at', [k, String(v), now()]);
|
||
if (body.pm_default_frequency_value !== undefined) set('pm_default_frequency_value', Number(body.pm_default_frequency_value) || 3);
|
||
if (body.pm_default_frequency_unit !== undefined) set('pm_default_frequency_unit', FREQUENCY_UNITS.includes(body.pm_default_frequency_unit) ? body.pm_default_frequency_unit : 'months');
|
||
if (body.pm_lead_days !== undefined) set('pm_lead_days', Number(body.pm_lead_days) || 7);
|
||
if (body.pm_usage_tracking_enabled !== undefined) set('pm_usage_tracking_enabled', body.pm_usage_tracking_enabled ? 1 : 0);
|
||
if (body.pm_lifespan_curve_enabled !== undefined) set('pm_lifespan_curve_enabled', body.pm_lifespan_curve_enabled ? 1 : 0);
|
||
// Floor multiplier is clamped to a sane 0.1–1.0; exponent to 0.5–6.
|
||
if (body.pm_lifespan_min_factor !== undefined) set('pm_lifespan_min_factor', Math.min(1, Math.max(0.1, Number(body.pm_lifespan_min_factor) || 0.5)));
|
||
if (body.pm_lifespan_curve_exponent !== undefined) set('pm_lifespan_curve_exponent', Math.min(6, Math.max(0.5, Number(body.pm_lifespan_curve_exponent) || 2)));
|
||
if (body.pm_recompute_enabled !== undefined) set('pm_recompute_enabled', body.pm_recompute_enabled ? 1 : 0);
|
||
if (body.pm_recompute_hour !== undefined) set('pm_recompute_hour', Math.min(23, Math.max(0, Math.round(Number(body.pm_recompute_hour)) || 0)));
|
||
audit(userId, 'update', 'pm_settings', null, null, body);
|
||
return getSettings();
|
||
}
|
||
|
||
// Map persisted settings into the shape pmScheduling.computeNextDue expects.
|
||
function engineSettings(settings = getSettings()) {
|
||
return {
|
||
usage_enabled: settings.pm_usage_tracking_enabled,
|
||
curve_enabled: settings.pm_lifespan_curve_enabled,
|
||
min_factor: settings.pm_lifespan_min_factor,
|
||
curve_exponent: settings.pm_lifespan_curve_exponent
|
||
};
|
||
}
|
||
|
||
// Alert candidates for PM schedules that are due/overdue.
|
||
function pmAlertCandidates() {
|
||
const lead = getSettings().pm_lead_days;
|
||
const today = todayStr();
|
||
const horizon = new Date(Date.now() + lead * 86400000).toISOString().slice(0, 10);
|
||
const rows = all(
|
||
`SELECT ap.*, p.name AS plan_name, a.asset_id AS asset_code
|
||
FROM asset_pm_plans ap
|
||
LEFT JOIN pm_plans p ON p.id = ap.plan_id
|
||
LEFT JOIN assets a ON a.id = ap.asset_id
|
||
WHERE ap.active = 1 AND ap.next_due_date IS NOT NULL`
|
||
);
|
||
const list = [];
|
||
for (const ap of rows) {
|
||
const name = ap.plan_name || 'Preventative maintenance';
|
||
const where = ap.asset_code ? ` on ${ap.asset_code}` : '';
|
||
// Meters at/over their threshold are reported in the alert text (e.g. "Operating Hours 512/500").
|
||
const crossed = assetPmMeters(ap.id)
|
||
.filter((m) => m.last_reading != null && m.due_at_reading != null && Number(m.last_reading) >= Number(m.due_at_reading))
|
||
.map((m) => `${m.label} ${Math.round(m.last_reading)}/${Math.round(m.due_at_reading)}${m.unit ? ' ' + m.unit : ''}`);
|
||
const usageNote = crossed.length ? ` Usage threshold reached: ${crossed.join('; ')}.` : '';
|
||
if (ap.next_due_date < today || crossed.length) {
|
||
list.push({ type: 'pm_overdue', reference_type: 'asset_pm', reference_id: ap.id, asset_id: ap.asset_id, severity: 'critical', title: `PM overdue: ${name}`, message: `${name}${where} was due ${ap.next_due_date}.${usageNote}`, due_date: ap.next_due_date });
|
||
} else if (ap.next_due_date <= horizon) {
|
||
list.push({ type: 'pm_due', reference_type: 'asset_pm', reference_id: ap.id, asset_id: ap.asset_id, severity: 'warning', title: `PM due: ${name}`, message: `${name}${where} is due ${ap.next_due_date}.${usageNote}`, due_date: ap.next_due_date });
|
||
}
|
||
}
|
||
return list;
|
||
}
|
||
|
||
// ---- Nightly recompute -----------------------------------------------------
|
||
|
||
// Re-derive next_due_date for every active schedule. Usage projections shift as time passes (a meter
|
||
// that has crossed its threshold becomes due "today"), so without a periodic sweep a schedule that
|
||
// isn't completed or read keeps a stale projected date. Cheap: one engine pass per active schedule.
|
||
function recomputeAllSchedules(userId) {
|
||
const rows = all('SELECT * FROM asset_pm_plans WHERE active = 1');
|
||
let updated = 0;
|
||
for (const ap of rows) {
|
||
const lastDate = ap.last_completed_date || ap.start_date || todayStr();
|
||
const { nextDue, active } = scheduleNextDue(ap, lastDate);
|
||
if (nextDue !== ap.next_due_date || (active ? 1 : 0) !== (ap.active ? 1 : 0)) {
|
||
run('UPDATE asset_pm_plans SET next_due_date = ?, active = ?, updated_at = ? WHERE id = ?', [nextDue, active, now(), ap.id]);
|
||
updated += 1;
|
||
}
|
||
}
|
||
const ranAt = now();
|
||
run(
|
||
"INSERT INTO app_settings (key, value, updated_at) VALUES ('pm_last_recompute_at', ?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value, updated_at = excluded.updated_at",
|
||
[ranAt, ranAt]
|
||
);
|
||
if (userId) audit(userId, 'recompute', 'asset_pm', null, null, { scanned: rows.length, updated });
|
||
return { scanned: rows.length, updated, ran_at: ranAt };
|
||
}
|
||
|
||
// Self-gating entry point for the scheduler: runs once per day at the configured hour when enabled.
|
||
function runScheduledRecompute() {
|
||
const s = getSettings();
|
||
if (!s.pm_recompute_enabled) return { skipped: 'disabled' };
|
||
if (new Date().getHours() !== s.pm_recompute_hour) return { skipped: 'off-hour' };
|
||
if (s.pm_last_recompute_at && String(s.pm_last_recompute_at).slice(0, 10) === todayStr()) return { skipped: 'already-ran-today' };
|
||
return recomputeAllSchedules(null);
|
||
}
|
||
|
||
// Maintenance-cost "ledger" for the PM book: actual PM spend per asset for a year.
|
||
function pmBookLedger(year, companyId) {
|
||
const rows = all(
|
||
`SELECT c.cost, c.completed_at, a.asset_id, a.description
|
||
FROM pm_completions c
|
||
JOIN asset_pm_plans ap ON ap.id = c.asset_pm_id
|
||
JOIN assets a ON a.id = ap.asset_id
|
||
WHERE (? IS NULL OR a.entity_id = ?)`,
|
||
[companyId || null, companyId || null]
|
||
);
|
||
const byAsset = {};
|
||
let total = 0;
|
||
let count = 0;
|
||
for (const row of rows) {
|
||
if (yearOf(row.completed_at) !== Number(year)) continue;
|
||
const cost = Number(row.cost || 0);
|
||
const entry = byAsset[row.asset_id] || (byAsset[row.asset_id] = { asset_id: row.asset_id, description: row.description, completions: 0, cost: 0, last_service: null });
|
||
entry.completions += 1;
|
||
entry.cost += cost;
|
||
const day = String(row.completed_at).slice(0, 10);
|
||
if (!entry.last_service || day > entry.last_service) entry.last_service = day;
|
||
total += cost;
|
||
count += 1;
|
||
}
|
||
const assets = Object.values(byAsset).map((a) => ({ ...a, cost: round2(a.cost) })).sort((a, b) => b.cost - a.cost);
|
||
// Post PM spend to the company's configured maintenance expense / clearing accounts (admin-set defaults).
|
||
const defaults = require('./glAccounts').getDefaults(companyId);
|
||
const accounts = [
|
||
{ account: defaults.pm_expense_account || '6450', type: 'PM / maintenance expense', debit: round2(total), credit: 0 },
|
||
{ account: defaults.pm_clearing_account || '2000', type: 'Maintenance clearing', debit: 0, credit: round2(total) }
|
||
];
|
||
return {
|
||
year: Number(year),
|
||
kind: 'maintenance',
|
||
rule_set: {},
|
||
summary: { assets: assets.length, completions: count, cost: round2(total) },
|
||
accounts,
|
||
accountTotals: { debit: round2(total), credit: round2(total) },
|
||
assets
|
||
};
|
||
}
|
||
|
||
module.exports = {
|
||
FREQUENCY_UNITS,
|
||
addInterval,
|
||
assetPmRows,
|
||
pmBookLedger,
|
||
attachPlan,
|
||
completePm,
|
||
createPlan,
|
||
deletePlan,
|
||
detachAssetPm,
|
||
getCompletion,
|
||
getPlan,
|
||
listCompletions,
|
||
getSettings,
|
||
listPlans,
|
||
logReading,
|
||
pmAlertCandidates,
|
||
recomputeAllSchedules,
|
||
runScheduledRecompute,
|
||
saveSettings,
|
||
updateAssetPm,
|
||
updatePlan
|
||
};
|