534 lines
16 KiB
JavaScript
534 lines
16 KiB
JavaScript
// node_api/routes/assets.js
|
||
const express = require('express');
|
||
const { requirePermission } = require('../middleware/auth');
|
||
const { getPool } = require('../db/connection');
|
||
|
||
const router = express.Router();
|
||
|
||
// Get database pool
|
||
const pool = getPool();
|
||
|
||
/**
|
||
* Get all assets for the user's organization (Enhanced)
|
||
*/
|
||
router.get('/', requirePermission('read'), async (req, res) => {
|
||
try {
|
||
const query = `
|
||
SELECT
|
||
a.id,
|
||
a.name,
|
||
a.description,
|
||
a.asset_identifier,
|
||
a.serial_number,
|
||
a.model_number,
|
||
a.manufacturer,
|
||
a.acquisition_date,
|
||
a.acquisition_cost,
|
||
a.status,
|
||
a.notes,
|
||
a.date_created,
|
||
a.date_updated,
|
||
a.created_by,
|
||
a.updated_by,
|
||
a.organization_id,
|
||
|
||
-- New enhanced fields
|
||
a.depreciation_percentage_rate,
|
||
a.expected_useful_life_months,
|
||
a.last_maintenance_date,
|
||
a.next_maintenance_date,
|
||
a.maintenance_interval_days,
|
||
a.maintenance_provider,
|
||
a.compliance_status,
|
||
a.last_inspection_date,
|
||
a.next_inspection_date,
|
||
a.current_usage_hours,
|
||
a.total_usage_hours,
|
||
a.utilization_rate,
|
||
|
||
-- Existing relationships
|
||
c.id as category_id,
|
||
c.name as category_name,
|
||
l.id as location_id,
|
||
l.name as location_name,
|
||
f.id as image_id,
|
||
f.filename_download as image_filename,
|
||
|
||
-- New relationships
|
||
at.id as asset_type_id,
|
||
at.name as asset_type_name,
|
||
wt.id as warranty_type_id,
|
||
wt.name as warranty_type_name
|
||
|
||
FROM assets a
|
||
LEFT JOIN asset_categories c ON a.category_id = c.id
|
||
LEFT JOIN locations l ON a.location_id = l.id
|
||
LEFT JOIN directus_files f ON a.image_url = f.id
|
||
LEFT JOIN asset_types at ON a.asset_type_id = at.id
|
||
LEFT JOIN warranty_types wt ON a.warranty_type_id = wt.id
|
||
WHERE a.organization_id = $1::uuid
|
||
ORDER BY a.date_created DESC
|
||
`;
|
||
|
||
const result = await pool.query(query, [req.user.organization_id]);
|
||
|
||
// Transform the data to match frontend expectations
|
||
const assets = result.rows.map(row => ({
|
||
id: row.id,
|
||
name: row.name,
|
||
description: row.description,
|
||
asset_identifier: row.asset_identifier,
|
||
serial_number: row.serial_number,
|
||
model_number: row.model_number,
|
||
manufacturer: row.manufacturer,
|
||
acquisition_date: row.acquisition_date,
|
||
acquisition_cost: row.acquisition_cost,
|
||
status: row.status,
|
||
notes: row.notes,
|
||
date_created: row.date_created,
|
||
date_updated: row.date_updated,
|
||
created_by: row.created_by,
|
||
updated_by: row.updated_by,
|
||
organization_id: row.organization_id,
|
||
|
||
// Enhanced fields
|
||
depreciation_percentage_rate: row.depreciation_percentage_rate,
|
||
expected_useful_life_months: row.expected_useful_life_months,
|
||
last_maintenance_date: row.last_maintenance_date,
|
||
next_maintenance_date: row.next_maintenance_date,
|
||
maintenance_interval_days: row.maintenance_interval_days,
|
||
maintenance_provider: row.maintenance_provider,
|
||
compliance_status: row.compliance_status,
|
||
last_inspection_date: row.last_inspection_date,
|
||
next_inspection_date: row.next_inspection_date,
|
||
current_usage_hours: row.current_usage_hours,
|
||
total_usage_hours: row.total_usage_hours,
|
||
utilization_rate: row.utilization_rate,
|
||
|
||
// Existing relationships
|
||
category_id: row.category_id ? {
|
||
id: row.category_id,
|
||
name: row.category_name
|
||
} : null,
|
||
location_id: row.location_id ? {
|
||
id: row.location_id,
|
||
name: row.location_name
|
||
} : null,
|
||
image_url: row.image_id ? {
|
||
id: row.image_id,
|
||
filename_download: row.image_filename
|
||
} : null,
|
||
|
||
// New relationships
|
||
asset_type_id: row.asset_type_id ? {
|
||
id: row.asset_type_id,
|
||
name: row.asset_type_name
|
||
} : null,
|
||
warranty_type_id: row.warranty_type_id ? {
|
||
id: row.warranty_type_id,
|
||
name: row.warranty_type_name
|
||
} : null
|
||
}));
|
||
|
||
console.log(`📊 Retrieved ${assets.length} enhanced assets for user ${req.user.email}`);
|
||
res.json({ data: assets });
|
||
|
||
} catch (error) {
|
||
console.error('Get assets error:', error);
|
||
res.status(500).json({ error: 'Failed to retrieve assets' });
|
||
}
|
||
});
|
||
|
||
/**
|
||
* Get a specific asset by ID
|
||
*/
|
||
router.get('/:id', requirePermission('read'), async (req, res) => {
|
||
try {
|
||
const { id } = req.params;
|
||
|
||
const query = `
|
||
SELECT
|
||
a.id,
|
||
a.name,
|
||
a.description,
|
||
a.asset_identifier,
|
||
a.serial_number,
|
||
a.model_number,
|
||
a.manufacturer,
|
||
a.acquisition_date,
|
||
a.acquisition_cost,
|
||
a.status,
|
||
a.notes,
|
||
a.date_created,
|
||
a.date_updated,
|
||
a.created_by,
|
||
a.updated_by,
|
||
a.organization_id,
|
||
c.id as category_id,
|
||
c.name as category_name,
|
||
l.id as location_id,
|
||
l.name as location_name,
|
||
f.id as image_id,
|
||
f.filename_download as image_filename
|
||
FROM assets a
|
||
LEFT JOIN asset_categories c ON a.category_id = c.id
|
||
LEFT JOIN locations l ON a.location_id = l.id
|
||
LEFT JOIN directus_files f ON a.image_url = f.id
|
||
WHERE a.id = $1::uuid AND a.organization_id = $2::uuid
|
||
`;
|
||
|
||
const result = await pool.query(query, [id, req.user.organization_id]);
|
||
|
||
if (result.rows.length === 0) {
|
||
return res.status(404).json({ error: 'Asset not found' });
|
||
}
|
||
|
||
const row = result.rows[0];
|
||
const asset = {
|
||
id: row.id,
|
||
name: row.name,
|
||
description: row.description,
|
||
asset_identifier: row.asset_identifier,
|
||
serial_number: row.serial_number,
|
||
model_number: row.model_number,
|
||
manufacturer: row.manufacturer,
|
||
acquisition_date: row.acquisition_date,
|
||
acquisition_cost: row.acquisition_cost,
|
||
status: row.status,
|
||
notes: row.notes,
|
||
date_created: row.date_created,
|
||
date_updated: row.date_updated,
|
||
created_by: row.created_by,
|
||
updated_by: row.updated_by,
|
||
organization_id: row.organization_id,
|
||
category_id: row.category_id ? {
|
||
id: row.category_id,
|
||
name: row.category_name
|
||
} : null,
|
||
location_id: row.location_id ? {
|
||
id: row.location_id,
|
||
name: row.location_name
|
||
} : null,
|
||
image_url: row.image_id ? {
|
||
id: row.image_id,
|
||
filename_download: row.image_filename
|
||
} : null
|
||
};
|
||
|
||
console.log(`🔍 Retrieved asset ${id} for user ${req.user.email}`);
|
||
res.json({ data: asset });
|
||
|
||
} catch (error) {
|
||
console.error('Get asset error:', error);
|
||
res.status(500).json({ error: 'Failed to retrieve asset' });
|
||
}
|
||
});
|
||
|
||
/**
|
||
* Update an asset
|
||
*/
|
||
router.patch('/:id', requirePermission('update'), async (req, res) => {
|
||
try {
|
||
const { id } = req.params;
|
||
const updateData = req.body;
|
||
|
||
// Build dynamic update query
|
||
const updateFields = [];
|
||
const values = [];
|
||
let paramCount = 1;
|
||
|
||
// Add updatable fields (including new enhanced fields)
|
||
const allowedFields = [
|
||
'name', 'description', 'asset_identifier', 'serial_number',
|
||
'model_number', 'manufacturer', 'acquisition_date', 'acquisition_cost',
|
||
'status', 'notes', 'category_id', 'location_id', 'image_url',
|
||
|
||
// Enhanced fields
|
||
'depreciation_percentage_rate', 'expected_useful_life_months',
|
||
'asset_type_id', 'warranty_type_id',
|
||
'last_maintenance_date', 'next_maintenance_date', 'maintenance_interval_days', 'maintenance_provider',
|
||
'compliance_status', 'last_inspection_date', 'next_inspection_date',
|
||
'current_usage_hours', 'total_usage_hours', 'utilization_rate'
|
||
];
|
||
|
||
for (const field of allowedFields) {
|
||
if (updateData[field] !== undefined) {
|
||
updateFields.push(`${field} = $${paramCount}`);
|
||
values.push(updateData[field]);
|
||
paramCount++;
|
||
}
|
||
}
|
||
|
||
if (updateFields.length === 0) {
|
||
return res.status(400).json({ error: 'No valid fields to update' });
|
||
}
|
||
|
||
// Add timestamp and user tracking
|
||
updateFields.push(`date_updated = NOW()`);
|
||
updateFields.push(`updated_by = $${paramCount}`);
|
||
values.push(req.user.id);
|
||
paramCount++;
|
||
|
||
// Add where conditions
|
||
values.push(id);
|
||
values.push(req.user.organization_id);
|
||
|
||
const updateQuery = `
|
||
UPDATE assets
|
||
SET ${updateFields.join(', ')}
|
||
WHERE id = $${paramCount}::uuid AND organization_id = $${paramCount + 1}::uuid
|
||
RETURNING id
|
||
`;
|
||
|
||
const updateResult = await pool.query(updateQuery, values);
|
||
|
||
if (updateResult.rows.length === 0) {
|
||
return res.status(404).json({ error: 'Asset not found or unauthorized' });
|
||
}
|
||
|
||
// Fetch the updated asset with relationships
|
||
const fetchQuery = `
|
||
SELECT
|
||
a.id,
|
||
a.name,
|
||
a.description,
|
||
a.asset_identifier,
|
||
a.serial_number,
|
||
a.model_number,
|
||
a.manufacturer,
|
||
a.acquisition_date,
|
||
a.acquisition_cost,
|
||
a.status,
|
||
a.notes,
|
||
a.date_created,
|
||
a.date_updated,
|
||
a.created_by,
|
||
a.updated_by,
|
||
a.organization_id,
|
||
c.id as category_id,
|
||
c.name as category_name,
|
||
l.id as location_id,
|
||
l.name as location_name,
|
||
f.id as image_id,
|
||
f.filename_download as image_filename
|
||
FROM assets a
|
||
LEFT JOIN asset_categories c ON a.category_id = c.id
|
||
LEFT JOIN locations l ON a.location_id = l.id
|
||
LEFT JOIN directus_files f ON a.image_url = f.id
|
||
WHERE a.id = $1::uuid AND a.organization_id = $2::uuid
|
||
`;
|
||
|
||
const result = await pool.query(fetchQuery, [id, req.user.organization_id]);
|
||
const row = result.rows[0];
|
||
|
||
const asset = {
|
||
id: row.id,
|
||
name: row.name,
|
||
description: row.description,
|
||
asset_identifier: row.asset_identifier,
|
||
serial_number: row.serial_number,
|
||
model_number: row.model_number,
|
||
manufacturer: row.manufacturer,
|
||
acquisition_date: row.acquisition_date,
|
||
acquisition_cost: row.acquisition_cost,
|
||
status: row.status,
|
||
notes: row.notes,
|
||
date_created: row.date_created,
|
||
date_updated: row.date_updated,
|
||
created_by: row.created_by,
|
||
updated_by: row.updated_by,
|
||
organization_id: row.organization_id,
|
||
category_id: row.category_id ? {
|
||
id: row.category_id,
|
||
name: row.category_name
|
||
} : null,
|
||
location_id: row.location_id ? {
|
||
id: row.location_id,
|
||
name: row.location_name
|
||
} : null,
|
||
image_url: row.image_id ? {
|
||
id: row.image_id,
|
||
filename_download: row.image_filename
|
||
} : null
|
||
};
|
||
|
||
console.log(`✏️ Updated asset ${id} for user ${req.user.email}`);
|
||
res.json({ data: asset });
|
||
|
||
} catch (error) {
|
||
console.error('Update asset error:', error);
|
||
console.error('Error details:', {
|
||
message: error.message,
|
||
code: error.code,
|
||
detail: error.detail,
|
||
constraint: error.constraint
|
||
});
|
||
res.status(500).json({
|
||
error: 'Failed to update asset',
|
||
details: process.env.NODE_ENV === 'development' ? error.message : undefined
|
||
});
|
||
}
|
||
});
|
||
|
||
/**
|
||
* Create a new asset
|
||
*/
|
||
router.post('/', requirePermission('create'), async (req, res) => {
|
||
try {
|
||
const assetData = req.body;
|
||
|
||
const insertQuery = `
|
||
INSERT INTO assets (
|
||
name, description, asset_identifier, serial_number, model_number, manufacturer,
|
||
acquisition_date, acquisition_cost, status, notes,
|
||
category_id, location_id, image_url,
|
||
depreciation_percentage_rate, expected_useful_life_months,
|
||
asset_type_id, warranty_type_id,
|
||
last_maintenance_date, next_maintenance_date, maintenance_interval_days, maintenance_provider,
|
||
compliance_status, last_inspection_date, next_inspection_date,
|
||
current_usage_hours, total_usage_hours, utilization_rate,
|
||
organization_id, created_by, date_created
|
||
) VALUES (
|
||
$1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, NOW()
|
||
) RETURNING id
|
||
`;
|
||
|
||
const values = [
|
||
assetData.name,
|
||
assetData.description,
|
||
assetData.asset_identifier,
|
||
assetData.serial_number,
|
||
assetData.model_number,
|
||
assetData.manufacturer,
|
||
assetData.acquisition_date,
|
||
assetData.acquisition_cost,
|
||
assetData.status || 'active',
|
||
assetData.notes,
|
||
assetData.category_id,
|
||
assetData.location_id,
|
||
assetData.image_url,
|
||
assetData.depreciation_percentage_rate || 0.00,
|
||
assetData.expected_useful_life_months || 60,
|
||
assetData.asset_type_id,
|
||
assetData.warranty_type_id,
|
||
assetData.last_maintenance_date,
|
||
assetData.next_maintenance_date,
|
||
assetData.maintenance_interval_days || 0,
|
||
assetData.maintenance_provider,
|
||
assetData.compliance_status || 'compliant',
|
||
assetData.last_inspection_date,
|
||
assetData.next_inspection_date,
|
||
assetData.current_usage_hours || 0.00,
|
||
assetData.total_usage_hours || 0.00,
|
||
assetData.utilization_rate || 0.00,
|
||
req.user.organization_id,
|
||
req.user.id
|
||
];
|
||
|
||
const result = await pool.query(insertQuery, values);
|
||
const assetId = result.rows[0].id;
|
||
|
||
// Fetch the created asset with relationships
|
||
const fetchQuery = `
|
||
SELECT
|
||
a.id,
|
||
a.name,
|
||
a.description,
|
||
a.asset_identifier,
|
||
a.serial_number,
|
||
a.model_number,
|
||
a.manufacturer,
|
||
a.acquisition_date,
|
||
a.acquisition_cost,
|
||
a.status,
|
||
a.notes,
|
||
a.date_created,
|
||
a.date_updated,
|
||
a.created_by,
|
||
a.updated_by,
|
||
a.organization_id,
|
||
c.id as category_id,
|
||
c.name as category_name,
|
||
l.id as location_id,
|
||
l.name as location_name,
|
||
f.id as image_id,
|
||
f.filename_download as image_filename
|
||
FROM assets a
|
||
LEFT JOIN asset_categories c ON a.category_id = c.id
|
||
LEFT JOIN locations l ON a.location_id = l.id
|
||
LEFT JOIN directus_files f ON a.image_url = f.id
|
||
WHERE a.id = $1
|
||
`;
|
||
|
||
const fetchResult = await pool.query(fetchQuery, [assetId]);
|
||
const row = fetchResult.rows[0];
|
||
|
||
const asset = {
|
||
id: row.id,
|
||
name: row.name,
|
||
description: row.description,
|
||
asset_identifier: row.asset_identifier,
|
||
serial_number: row.serial_number,
|
||
model_number: row.model_number,
|
||
manufacturer: row.manufacturer,
|
||
acquisition_date: row.acquisition_date,
|
||
acquisition_cost: row.acquisition_cost,
|
||
status: row.status,
|
||
notes: row.notes,
|
||
date_created: row.date_created,
|
||
date_updated: row.date_updated,
|
||
created_by: row.created_by,
|
||
updated_by: row.updated_by,
|
||
organization_id: row.organization_id,
|
||
category_id: row.category_id ? {
|
||
id: row.category_id,
|
||
name: row.category_name
|
||
} : null,
|
||
location_id: row.location_id ? {
|
||
id: row.location_id,
|
||
name: row.location_name
|
||
} : null,
|
||
image_url: row.image_id ? {
|
||
id: row.image_id,
|
||
filename_download: row.image_filename
|
||
} : null
|
||
};
|
||
|
||
console.log(`➕ Created asset ${assetId} for user ${req.user.email}`);
|
||
res.status(201).json({ data: asset });
|
||
|
||
} catch (error) {
|
||
console.error('Create asset error:', error);
|
||
res.status(500).json({ error: 'Failed to create asset' });
|
||
}
|
||
});
|
||
|
||
/**
|
||
* Delete an asset
|
||
*/
|
||
router.delete('/:id', requirePermission('delete'), async (req, res) => {
|
||
try {
|
||
const { id } = req.params;
|
||
|
||
const deleteQuery = `
|
||
DELETE FROM assets
|
||
WHERE id = $1::uuid AND organization_id = $2::uuid
|
||
RETURNING id
|
||
`;
|
||
|
||
const result = await pool.query(deleteQuery, [id, req.user.organization_id]);
|
||
|
||
if (result.rows.length === 0) {
|
||
return res.status(404).json({ error: 'Asset not found or unauthorized' });
|
||
}
|
||
|
||
console.log(`🗑️ Deleted asset ${id} for user ${req.user.email}`);
|
||
res.status(204).send();
|
||
|
||
} catch (error) {
|
||
console.error('Delete asset error:', error);
|
||
res.status(500).json({ error: 'Failed to delete asset' });
|
||
}
|
||
});
|
||
|
||
module.exports = router; |