1042 lines
38 KiB
JavaScript
1042 lines
38 KiB
JavaScript
import express from 'express';
|
|
import mysql from 'mysql2/promise';
|
|
import cors from 'cors';
|
|
import dotenv from 'dotenv';
|
|
import fs from 'fs';
|
|
|
|
dotenv.config();
|
|
|
|
const app = express();
|
|
app.use(cors());
|
|
app.use(express.json({ limit: '50mb' }));
|
|
app.use('/uploads', express.static('uploads')); // 업로드 파일 정적 서빙
|
|
|
|
// uploads 폴더가 없으면 생성
|
|
if (!fs.existsSync('uploads')) {
|
|
fs.mkdirSync('uploads');
|
|
}
|
|
|
|
// MySQL Pool Configuration
|
|
const pool = mysql.createPool({
|
|
host: process.env.DB_HOST,
|
|
user: process.env.DB_USER,
|
|
password: process.env.DB_PASS,
|
|
database: process.env.DB_NAME,
|
|
port: parseInt(process.env.DB_PORT || '3306'),
|
|
waitForConnections: true,
|
|
connectionLimit: 10,
|
|
queueLimit: 0
|
|
});
|
|
|
|
// Database startup check (ensure job_spec_standards table exists)
|
|
(async () => {
|
|
let connection;
|
|
try {
|
|
connection = await pool.getConnection();
|
|
await connection.query(`
|
|
CREATE TABLE IF NOT EXISTS job_spec_standards (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
job_name VARCHAR(100) UNIQUE NOT NULL,
|
|
cpu_standard VARCHAR(255),
|
|
ram_standard VARCHAR(100),
|
|
gpu_standard VARCHAR(100),
|
|
min_score INT DEFAULT 0,
|
|
remarks TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
`);
|
|
console.log('✅ job_spec_standards table verification completed.');
|
|
} catch (err) {
|
|
console.error('❌ Failed to verify/create job_spec_standards table:', err);
|
|
} finally {
|
|
if (connection) connection.release();
|
|
}
|
|
})();
|
|
|
|
// Error Handler
|
|
const handleError = (res, err, label) => {
|
|
console.error(`❌ [${label}] Error:`, err);
|
|
res.status(500).json({ error: err.message });
|
|
};
|
|
|
|
// --- Global Constants ---
|
|
const CATEGORY_TABLE_MAP = {
|
|
pc: 'asset_core',
|
|
server: 'asset_core',
|
|
storage: 'asset_core',
|
|
network: 'asset_core',
|
|
equipment: 'asset_core',
|
|
officeSupplies: 'asset_core',
|
|
survey: 'asset_core',
|
|
vip: 'asset_core',
|
|
pcParts: 'asset_core',
|
|
swInternal: 'asset_software_perpetual',
|
|
swExternal: 'asset_software_subscription',
|
|
swUsers: 'asset_software_assignment',
|
|
users: 'system_users',
|
|
logs: 'asset_history'
|
|
};
|
|
|
|
const ASSET_TABLES = [
|
|
'asset_core'
|
|
];
|
|
|
|
// --- API Endpoints ---
|
|
|
|
// 1. Generic Batch Save (Dynamic Table Detection)
|
|
app.post('/api/:table/batch', async (req, res) => {
|
|
const { table } = req.params;
|
|
const dbTable = CATEGORY_TABLE_MAP[table] || table;
|
|
const data = req.body;
|
|
if (!Array.isArray(data)) return res.status(400).json({ error: 'Data must be an array' });
|
|
|
|
let connection;
|
|
try {
|
|
connection = await pool.getConnection();
|
|
await connection.beginTransaction();
|
|
|
|
const [columns] = await connection.query(`DESCRIBE ${dbTable}`);
|
|
const validFields = columns.map(c => c.Field);
|
|
|
|
await connection.query(`DELETE FROM ${dbTable}`);
|
|
|
|
if (data.length > 0) {
|
|
const placeholders = validFields.map(() => '?').join(', ');
|
|
const sql = `INSERT INTO ${dbTable} (${validFields.join(', ')}) VALUES (${placeholders})`;
|
|
|
|
for (const item of data) {
|
|
const values = validFields.map(field => {
|
|
const val = item[field];
|
|
return val === undefined ? null : val;
|
|
});
|
|
await connection.query(sql, values);
|
|
}
|
|
}
|
|
|
|
await connection.commit();
|
|
res.json({ success: true, count: data.length });
|
|
} catch (err) {
|
|
if (connection) await connection.rollback();
|
|
handleError(res, err, 'BATCH SAVE');
|
|
} finally {
|
|
if (connection) connection.release();
|
|
}
|
|
});
|
|
|
|
// 2. Get All Assets (Integrated Master Data from Normalized V3 Schema)
|
|
app.get('/api/assets/master', async (req, res) => {
|
|
let connection;
|
|
try {
|
|
connection = await pool.getConnection();
|
|
|
|
const masterData = {
|
|
pc: [], server: [], storage: [], network: [],
|
|
equipment: [], officeSupplies: [], survey: [], vip: [], pcParts: [],
|
|
swInternal: [], swExternal: [], swUsers: [], users: [], logs: [], partsMaster: []
|
|
};
|
|
|
|
// Load from V3 Normalized Schema
|
|
const [rows] = await connection.query(`
|
|
SELECT
|
|
c.*,
|
|
s.hw_status, s.model_name, s.mainboard, s.os, s.cpu, s.ram, s.gpu,
|
|
s.monitoring, s.price, s.monitor_inch, s.serial_num,
|
|
l.location, l.location_detail, l.location_photo, l.loc_x, l.loc_y,
|
|
(
|
|
SELECT JSON_ARRAYAGG(JSON_OBJECT('type', net_type, 'name', net_name, 'val1', net_value1, 'val2', net_value2))
|
|
FROM asset_remote WHERE asset_id = c.id AND is_active = 1
|
|
) as remotes,
|
|
(
|
|
SELECT JSON_ARRAYAGG(JSON_OBJECT('type', disk_type, 'capacity', capacity, 'unit', unit, 'slot', slot_no))
|
|
FROM asset_volume WHERE asset_id = c.id
|
|
) as volumes
|
|
FROM asset_core c
|
|
LEFT JOIN asset_spec s ON c.id = s.asset_id
|
|
LEFT JOIN asset_location l ON l.id = (
|
|
SELECT id FROM asset_location
|
|
WHERE asset_id = c.id AND is_active = 1
|
|
ORDER BY created_at DESC LIMIT 1
|
|
)
|
|
`);
|
|
|
|
const catMap = {
|
|
'PC': 'pc', '서버': 'server', '저장매체': 'storage', '네트워크': 'network',
|
|
'업무지원장비': 'equipment', '사무가구': 'officeSupplies', '공간정보장비': 'survey',
|
|
'내빈/외빈': 'vip', 'PC부품': 'pcParts'
|
|
};
|
|
|
|
rows.forEach(row => {
|
|
const key = catMap[row.category] || 'pc';
|
|
masterData[key].push(row);
|
|
});
|
|
|
|
const [swInternal] = await connection.query('SELECT * FROM asset_software_perpetual');
|
|
const [swExternal] = await connection.query('SELECT * FROM asset_software_subscription');
|
|
const [swUsers] = await connection.query('SELECT * FROM asset_software_assignment');
|
|
const [users] = await connection.query('SELECT * FROM system_users');
|
|
const [logs] = await connection.query('SELECT * FROM asset_history ORDER BY created_at DESC');
|
|
const [partsMaster] = await connection.query('SELECT * FROM hardware_components_master ORDER BY category, component_name');
|
|
const [jobSpecs] = await connection.query('SELECT * FROM job_spec_standards ORDER BY job_name');
|
|
|
|
masterData.swInternal = swInternal;
|
|
masterData.swExternal = swExternal;
|
|
masterData.swUsers = swUsers;
|
|
masterData.users = users;
|
|
masterData.logs = logs;
|
|
masterData.partsMaster = partsMaster;
|
|
masterData.jobSpecs = jobSpecs;
|
|
|
|
res.json(masterData);
|
|
} catch (err) {
|
|
handleError(res, err, 'MASTER DATA');
|
|
} finally {
|
|
if (connection) connection.release();
|
|
}
|
|
});
|
|
|
|
// 3. Asset Save (Surgical Split to Normalized V3 Tables)
|
|
app.post('/api/asset/:category/save', async (req, res) => {
|
|
const asset = req.body;
|
|
let connection;
|
|
try {
|
|
connection = await pool.getConnection();
|
|
await connection.beginTransaction();
|
|
|
|
// 3.0 History Tracking & Auto Field Update
|
|
const [oldCoreRows] = await connection.query('SELECT * FROM asset_core WHERE id = ?', [asset.id]);
|
|
const [oldSpecRows] = await connection.query('SELECT * FROM asset_spec WHERE asset_id = ?', [asset.id]);
|
|
const oldCore = oldCoreRows[0] || {};
|
|
const oldSpec = oldSpecRows[0] || {};
|
|
|
|
const historyLogs = [];
|
|
const logDate = new Date().toISOString().split('T')[0]; // YYYY-MM-DD
|
|
const logUser = '관리자';
|
|
|
|
// 3.0.1 Core 변동 감지 (Dept, User)
|
|
const oldDept = oldCore.current_dept || '';
|
|
const newDept = asset.current_dept || '';
|
|
if (newDept !== '' && oldDept !== newDept) {
|
|
asset.previous_dept = oldDept;
|
|
historyLogs.push({
|
|
event_type: 'DEPT_CHANGE',
|
|
old_dept: oldDept || null,
|
|
new_dept: newDept,
|
|
details: `[조직 변동] ${oldDept || '(없음)'} -> ${newDept}`
|
|
});
|
|
}
|
|
|
|
const oldUser = oldCore.user_current || '';
|
|
const newUser = asset.user_current || '';
|
|
if (newUser !== '' && oldUser !== newUser) {
|
|
asset.previous_user = oldUser;
|
|
historyLogs.push({
|
|
event_type: 'USER_CHANGE',
|
|
old_user: oldUser || null,
|
|
new_user: newUser,
|
|
details: `[사용자 변동] ${oldUser || '(없음)'} -> ${newUser}`
|
|
});
|
|
}
|
|
|
|
// 3.0.2 Spec 변동 감지 (CPU, RAM, GPU, OS, Mainboard 등)
|
|
const specFieldsToTrack = [
|
|
{ key: 'cpu', label: 'CPU' },
|
|
{ key: 'ram', label: 'RAM' },
|
|
{ key: 'gpu', label: 'GPU' },
|
|
{ key: 'os', label: 'OS' },
|
|
{ key: 'mainboard', label: '메인보드' }
|
|
];
|
|
|
|
specFieldsToTrack.forEach(field => {
|
|
const oldVal = String(oldSpec[field.key] || '').trim();
|
|
const newVal = String(asset[field.key] || '').trim();
|
|
if (newVal !== '' && oldVal !== newVal) {
|
|
historyLogs.push({
|
|
event_type: 'SPEC_CHANGE',
|
|
details: `[사양 변경] ${field.label}: ${oldVal || '(없음)'} -> ${newVal}`
|
|
});
|
|
}
|
|
});
|
|
|
|
// 3.0.3 상태 변경 감지
|
|
const oldStatus = oldSpec.hw_status || '';
|
|
const newStatus = asset.hw_status || '';
|
|
if (newStatus !== '' && oldStatus !== newStatus) {
|
|
historyLogs.push({
|
|
event_type: 'STATUS_CHANGE',
|
|
details: `[상태 변경] ${oldStatus || '(없음)'} -> ${newStatus}`
|
|
});
|
|
}
|
|
|
|
// 로그 일괄 삽입
|
|
for (const log of historyLogs) {
|
|
await connection.query(
|
|
`INSERT INTO asset_history (asset_id, event_type, old_dept, new_dept, old_user, new_user, details, log_date, log_user)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
|
|
[asset.id, log.event_type, log.old_dept || null, log.new_dept || null, log.old_user || null, log.new_user || null, log.details, logDate, logUser]
|
|
);
|
|
}
|
|
|
|
// 3.1 asset_core
|
|
const coreFields = ['id', 'asset_code', 'category', 'asset_type', 'current_role', 'asset_purpose', 'service_type', 'purchase_corp', 'purchase_date', 'purchase_amount', 'purchase_vendor', 'approval_document', 'memo', 'manager_primary', 'manager_secondary', 'current_dept', 'previous_dept', 'user_current', 'previous_user', 'emp_no', 'user_position'];
|
|
const coreData = {};
|
|
coreFields.forEach(f => { if (asset[f] !== undefined) coreData[f] = asset[f]; });
|
|
const coreKeys = Object.keys(coreData);
|
|
|
|
console.log(`[DEBUG] Saving Asset ID: ${asset.id}, Code: ${asset.asset_code}`);
|
|
const [existingCore] = await connection.query('SELECT id FROM asset_core WHERE id = ?', [asset.id]);
|
|
console.log(`[DEBUG] Existing Core Check for ${asset.id}: Found ${existingCore.length}`);
|
|
|
|
if (existingCore.length > 0) {
|
|
// UPDATE
|
|
const updateKeys = coreKeys.filter(k => k !== 'id');
|
|
const coreSql = `UPDATE asset_core SET ${updateKeys.map(k => `${k} = ?`).join(', ')} WHERE id = ?`;
|
|
const [updRes] = await connection.query(coreSql, [...updateKeys.map(k => coreData[k]), asset.id]);
|
|
console.log(`[DEBUG] Core UPDATE result: affectedRows=${updRes.affectedRows}`);
|
|
} else {
|
|
// INSERT
|
|
const coreSql = `INSERT INTO asset_core (${coreKeys.join(', ')}) VALUES (${coreKeys.map(() => '?').join(', ')})`;
|
|
const [insRes] = await connection.query(coreSql, Object.values(coreData));
|
|
console.log(`[DEBUG] Core INSERT result: affectedRows=${insRes.affectedRows}`);
|
|
}
|
|
|
|
// 3.2 asset_spec
|
|
const specFields = ['hw_status', 'model_name', 'mainboard', 'os', 'cpu', 'ram', 'gpu', 'monitoring', 'price', 'monitor_inch', 'serial_num'];
|
|
const specData = { asset_id: asset.id };
|
|
specFields.forEach(f => { if (asset[f] !== undefined) specData[f] = asset[f]; });
|
|
const specKeys = Object.keys(specData);
|
|
const [specExists] = await connection.query('SELECT id FROM asset_spec WHERE asset_id = ?', [asset.id]);
|
|
if (specExists.length > 0) {
|
|
const updateSql = `UPDATE asset_spec SET ${specKeys.filter(k => k !== 'asset_id').map(k => `${k} = ?`).join(', ')} WHERE asset_id = ?`;
|
|
await connection.query(updateSql, [...specKeys.filter(k => k !== 'asset_id').map(k => specData[k]), asset.id]);
|
|
} else {
|
|
await connection.query(`INSERT INTO asset_spec (${specKeys.join(', ')}) VALUES (${specKeys.map(() => '?').join(', ')})`, Object.values(specData));
|
|
}
|
|
|
|
// 3.3 asset_volume
|
|
await connection.query('DELETE FROM asset_volume WHERE asset_id = ?', [asset.id]);
|
|
if (asset.volumes) {
|
|
try {
|
|
let vols = typeof asset.volumes === 'string' ? JSON.parse(asset.volumes) : asset.volumes;
|
|
if (Array.isArray(vols)) {
|
|
for (let i = 0; i < vols.length; i++) {
|
|
const v = vols[i];
|
|
if (v.type && v.capacity) {
|
|
await connection.query(
|
|
'INSERT INTO asset_volume (asset_id, disk_type, capacity, unit, slot_no) VALUES (?, ?, ?, ?, ?)',
|
|
[asset.id, v.type, v.capacity, v.unit || 'GB', v.slot || (i + 1)]
|
|
);
|
|
}
|
|
}
|
|
}
|
|
} catch(e) { console.error('Volume parse error', e); }
|
|
}
|
|
|
|
// 3.4 asset_location
|
|
if (asset.location || asset.location_detail) {
|
|
const [locActive] = await connection.query('SELECT * FROM asset_location WHERE asset_id = ? AND is_active = 1', [asset.id]);
|
|
const isChanged = locActive.length === 0 || locActive[0].location !== asset.location || locActive[0].location_detail !== asset.location_detail || locActive[0].loc_x !== asset.loc_x || locActive[0].loc_y !== asset.loc_y;
|
|
if (isChanged) {
|
|
await connection.query('UPDATE asset_location SET is_active = 0, deactivated_at = NOW() WHERE asset_id = ? AND is_active = 1', [asset.id]);
|
|
await connection.query(`INSERT INTO asset_location (asset_id, location, location_detail, location_photo, loc_x, loc_y, is_active) VALUES (?, ?, ?, ?, ?, ?, 1)`,
|
|
[asset.id, asset.location, asset.location_detail, asset.location_photo, asset.loc_x, asset.loc_y]);
|
|
}
|
|
}
|
|
|
|
// 3.5 asset_remote (Dynamic Array Logic)
|
|
if (asset.remotes) {
|
|
try {
|
|
let nets = typeof asset.remotes === 'string' ? JSON.parse(asset.remotes) : asset.remotes;
|
|
if (Array.isArray(nets)) {
|
|
await connection.query('UPDATE asset_remote SET is_active = 0, deactivated_at = NOW() WHERE asset_id = ? AND is_active = 1', [asset.id]);
|
|
for (const n of nets) {
|
|
if (n.type) {
|
|
await connection.query(
|
|
'INSERT INTO asset_remote (asset_id, net_type, net_name, net_value1, net_value2, is_active) VALUES (?, ?, ?, ?, ?, 1)',
|
|
[asset.id, n.type, n.name || '', n.val1 || '', n.val2 || '']
|
|
);
|
|
}
|
|
}
|
|
}
|
|
} catch(e) { console.error('Remote data parse error', e); }
|
|
} else {
|
|
// Fallback for UI that hasn't sent the networks array yet
|
|
if (asset.ip_address || asset.mac_address || asset.remote_tool) {
|
|
const [netActive] = await connection.query('SELECT * FROM asset_remote WHERE asset_id = ? AND is_active = 1', [asset.id]);
|
|
const isChanged = netActive.length === 0 || netActive[0].net_value1 !== asset.ip_address || netActive[0].net_value2 !== asset.mac_address || netActive[0].net_name !== asset.remote_tool;
|
|
if (isChanged) {
|
|
await connection.query('UPDATE asset_remote SET is_active = 0, deactivated_at = NOW() WHERE asset_id = ? AND is_active = 1', [asset.id]);
|
|
if (asset.ip_address || asset.mac_address) {
|
|
await connection.query('INSERT INTO asset_remote (asset_id, net_type, net_name, net_value1, net_value2, is_active) VALUES (?, ?, ?, ?, ?, 1)', [asset.id, 'IP', '기본망', asset.ip_address, asset.mac_address]);
|
|
}
|
|
if (asset.remote_tool || asset.remote_id || asset.remote_pw) {
|
|
await connection.query('INSERT INTO asset_remote (asset_id, net_type, net_name, net_value1, net_value2, is_active) VALUES (?, ?, ?, ?, ?, 1)', [asset.id, 'REMOTE', asset.remote_tool, asset.remote_id, asset.remote_pw]);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
await connection.commit();
|
|
console.log(`💾 [V3 ASSET SAVE] ID: ${asset.id}`);
|
|
res.json({ success: true });
|
|
} catch (err) {
|
|
if (connection) await connection.rollback();
|
|
handleError(res, err, 'ASSET SAVE V3');
|
|
} finally {
|
|
if (connection) connection.release();
|
|
}
|
|
});
|
|
|
|
// 3.6 PC Flow Transaction (Checkout, Return, Move)
|
|
app.post('/api/pc/flow', async (req, res) => {
|
|
const { action, assetId, userName, dept, empNo, position, date, details, manager } = req.body;
|
|
let connection;
|
|
try {
|
|
connection = await pool.getConnection();
|
|
await connection.beginTransaction();
|
|
|
|
if (action === 'checkout') {
|
|
await connection.query(
|
|
`UPDATE asset_core
|
|
SET user_current = ?, emp_no = ?, current_dept = ?, user_position = ?
|
|
WHERE id = ?`,
|
|
[userName, empNo, dept, position, assetId]
|
|
);
|
|
await connection.query(
|
|
`UPDATE asset_spec SET hw_status = '운영' WHERE asset_id = ?`,
|
|
[assetId]
|
|
);
|
|
} else if (action === 'return') {
|
|
await connection.query(
|
|
`UPDATE asset_core
|
|
SET previous_user = user_current, previous_dept = current_dept,
|
|
user_current = '', emp_no = '', user_position = ''
|
|
WHERE id = ?`,
|
|
[assetId]
|
|
);
|
|
await connection.query(
|
|
`UPDATE asset_spec SET hw_status = '재고' WHERE asset_id = ?`,
|
|
[assetId]
|
|
);
|
|
} else if (action === 'move') {
|
|
await connection.query(
|
|
`UPDATE asset_core
|
|
SET previous_user = user_current, previous_dept = current_dept,
|
|
user_current = ?, emp_no = ?, current_dept = ?, user_position = ?
|
|
WHERE id = ?`,
|
|
[userName, empNo, dept, position, assetId]
|
|
);
|
|
await connection.query(
|
|
`UPDATE asset_spec SET hw_status = '운영' WHERE asset_id = ?`,
|
|
[assetId]
|
|
);
|
|
} else {
|
|
throw new Error('Invalid action type');
|
|
}
|
|
|
|
// Insert into asset_history
|
|
await connection.query(
|
|
`INSERT INTO asset_history (asset_id, log_date, log_user, details)
|
|
VALUES (?, ?, ?, ?)`,
|
|
[assetId, date || new Date().toISOString().split('T')[0], manager || 'system', details]
|
|
);
|
|
|
|
await connection.commit();
|
|
console.log(`💾 [PC FLOW TRANSACTION] Action: ${action}, Asset ID: ${assetId}`);
|
|
res.json({ success: true });
|
|
} catch (err) {
|
|
if (connection) await connection.rollback();
|
|
handleError(res, err, 'PC FLOW TRANSACTION');
|
|
} finally {
|
|
if (connection) connection.release();
|
|
}
|
|
});
|
|
|
|
// 4. Asset Delete
|
|
app.delete('/api/asset/:category/:id', async (req, res) => {
|
|
const { category, id } = req.params;
|
|
|
|
// Define mapping for which base table handles the delete
|
|
const deleteTableMap = {
|
|
pc: 'asset_core',
|
|
server: 'asset_core',
|
|
storage: 'asset_core',
|
|
network: 'asset_core',
|
|
equipment: 'asset_core',
|
|
officeSupplies: 'asset_core',
|
|
survey: 'asset_core',
|
|
vip: 'asset_core',
|
|
pcParts: 'asset_core',
|
|
swInternal: 'asset_software_perpetual',
|
|
swExternal: 'asset_software_subscription',
|
|
swUsers: 'asset_software_assignment',
|
|
users: 'system_users'
|
|
};
|
|
|
|
const table = deleteTableMap[category];
|
|
|
|
if (!table) return res.status(400).json({ error: 'Invalid category for deletion' });
|
|
|
|
try {
|
|
const connection = await pool.getConnection();
|
|
// For asset_core, ON DELETE CASCADE will handle spec, location, remote, volume
|
|
await connection.query(`DELETE FROM ${table} WHERE id = ?`, [id]);
|
|
connection.release();
|
|
console.log(`🗑️ [ASSET DELETE] Category: ${category}, ID: ${id}`);
|
|
res.json({ success: true });
|
|
} catch (err) {
|
|
handleError(res, err, 'ASSET DELETE');
|
|
}
|
|
});
|
|
|
|
// 5. Generate Next Asset Code
|
|
app.get('/api/generate-asset-code', async (req, res) => {
|
|
const { prefix, purchaseDate } = req.query;
|
|
if (!prefix) return res.status(400).json({ error: 'Prefix is required' });
|
|
try {
|
|
const connection = await pool.getConnection();
|
|
const datePart = purchaseDate ? purchaseDate.toString().replace(/-/g, '').substring(0, 6) : '';
|
|
const searchPattern = datePart ? `${prefix}-${datePart}-%` : `${prefix}-%`;
|
|
let maxNum = 0;
|
|
for (const table of ASSET_TABLES) {
|
|
try {
|
|
const [rows] = await connection.query(`SELECT asset_code FROM ${table} WHERE asset_code LIKE ?`, [searchPattern]);
|
|
rows.forEach(row => {
|
|
const parts = row.asset_code.split('-');
|
|
const num = parseInt(parts[parts.length - 1]);
|
|
if (!isNaN(num) && num > maxNum) maxNum = num;
|
|
});
|
|
} catch (err) {}
|
|
}
|
|
const nextNum = maxNum + 1;
|
|
const nextCode = datePart ? `${prefix}-${datePart}-${String(nextNum).padStart(4, '0')}` : `${prefix}-${String(nextNum).padStart(4, '0')}`;
|
|
connection.release();
|
|
res.json({ nextCode });
|
|
} catch (err) { handleError(res, err, 'GENERATE CODE'); }
|
|
});
|
|
|
|
function getCleanMapKey(path) {
|
|
let clean = path.replace('img/location_photo/', '').replace('.png', '');
|
|
clean = clean.replace('서관', 'W').replace('동관', 'E');
|
|
clean = clean.replace('한맥빌딩/MDF실/MDF_', 'HAN-MDF-');
|
|
clean = clean.replace('기술개발센터/서버실/서버실_', 'DEV-SVR-');
|
|
clean = clean.replace(/\//g, '-');
|
|
return clean;
|
|
}
|
|
|
|
function getLocationName(path) {
|
|
if (path.includes('IDC')) return 'IDC';
|
|
if (path.includes('한맥빌딩')) return '한맥빌딩';
|
|
if (path.includes('기술개발센터')) return '기술개발센터';
|
|
return '기타';
|
|
}
|
|
|
|
function getLocationDetail(path, idx) {
|
|
let clean = path.replace('img/location_photo/', '').replace('.png', '');
|
|
let parts = clean.split('/');
|
|
let lastPart = parts[parts.length - 1];
|
|
return `${lastPart} 구역 자리 #${idx + 1}`;
|
|
}
|
|
|
|
// 6. Map Config API
|
|
app.get('/api/maps', async (req, res) => {
|
|
try {
|
|
const query = `
|
|
SELECT
|
|
pl.location_code,
|
|
pl.location_name,
|
|
pl.location_detail,
|
|
pl.map_image,
|
|
pl.map_x,
|
|
pl.map_y,
|
|
pl.map_w,
|
|
pl.map_h,
|
|
al.asset_id
|
|
FROM physical_locations pl
|
|
LEFT JOIN asset_location al ON al.physical_location_code = pl.location_code AND al.is_active = 1
|
|
`;
|
|
const [rows] = await pool.query(query);
|
|
|
|
const mapConfig = {};
|
|
rows.forEach(row => {
|
|
const mapPath = row.map_image;
|
|
if (!mapConfig[mapPath]) {
|
|
mapConfig[mapPath] = [];
|
|
}
|
|
mapConfig[mapPath].push({
|
|
x: parseFloat(row.map_x).toFixed(2),
|
|
y: parseFloat(row.map_y).toFixed(2),
|
|
w: parseFloat(row.map_w).toFixed(2),
|
|
h: parseFloat(row.map_h).toFixed(2),
|
|
asset_id: row.asset_id
|
|
});
|
|
});
|
|
|
|
res.json(mapConfig);
|
|
} catch (err) {
|
|
handleError(res, err, 'GET MAPS');
|
|
}
|
|
});
|
|
|
|
// 6.5. Get Hardware Components Master List
|
|
app.get('/api/hardware-components', async (req, res) => {
|
|
try {
|
|
const [rows] = await pool.query('SELECT * FROM hardware_components_master ORDER BY category, component_name');
|
|
res.json(rows);
|
|
} catch (err) {
|
|
handleError(res, err, 'GET HARDWARE COMPONENTS');
|
|
}
|
|
});
|
|
|
|
// 6.6. Save Hardware Component (Add or Update)
|
|
app.post('/api/hardware-components/save', async (req, res) => {
|
|
const { id, category, component_name, score_tier, deduction } = req.body;
|
|
let connection;
|
|
try {
|
|
connection = await pool.getConnection();
|
|
if (id) {
|
|
await connection.query(
|
|
'UPDATE hardware_components_master SET category = ?, component_name = ?, score_tier = ?, deduction = ? WHERE id = ?',
|
|
[category, component_name, score_tier, deduction, id]
|
|
);
|
|
} else {
|
|
await connection.query(
|
|
'INSERT INTO hardware_components_master (category, component_name, score_tier, deduction) VALUES (?, ?, ?, ?)',
|
|
[category, component_name, score_tier, deduction]
|
|
);
|
|
}
|
|
res.json({ success: true });
|
|
} catch (err) {
|
|
handleError(res, err, 'SAVE HARDWARE COMPONENT');
|
|
} finally {
|
|
if (connection) connection.release();
|
|
}
|
|
});
|
|
|
|
// 6.7. Delete Hardware Component
|
|
app.delete('/api/hardware-components/:id', async (req, res) => {
|
|
const { id } = req.params;
|
|
let connection;
|
|
try {
|
|
connection = await pool.getConnection();
|
|
await connection.query('DELETE FROM hardware_components_master WHERE id = ?', [id]);
|
|
res.json({ success: true });
|
|
} catch (err) {
|
|
handleError(res, err, 'DELETE HARDWARE COMPONENT');
|
|
} finally {
|
|
if (connection) connection.release();
|
|
}
|
|
});
|
|
|
|
// 6.7.1. Get Job Spec Standards
|
|
app.get('/api/job-specs', async (req, res) => {
|
|
try {
|
|
const [rows] = await pool.query('SELECT * FROM job_spec_standards ORDER BY job_name');
|
|
res.json(rows);
|
|
} catch (err) {
|
|
handleError(res, err, 'GET JOB SPECS');
|
|
}
|
|
});
|
|
|
|
// 6.7.2. Save Job Spec Standard (Add or Update)
|
|
app.post('/api/job-specs/save', async (req, res) => {
|
|
const { id, job_name, cpu_standard, ram_standard, gpu_standard, min_score, remarks } = req.body;
|
|
let connection;
|
|
try {
|
|
connection = await pool.getConnection();
|
|
if (id) {
|
|
await connection.query(
|
|
'UPDATE job_spec_standards SET job_name = ?, cpu_standard = ?, ram_standard = ?, gpu_standard = ?, min_score = ?, remarks = ? WHERE id = ?',
|
|
[job_name, cpu_standard, ram_standard, gpu_standard, min_score, remarks, id]
|
|
);
|
|
} else {
|
|
await connection.query(
|
|
'INSERT INTO job_spec_standards (job_name, cpu_standard, ram_standard, gpu_standard, min_score, remarks) VALUES (?, ?, ?, ?, ?, ?)',
|
|
[job_name, cpu_standard, ram_standard, gpu_standard, min_score, remarks]
|
|
);
|
|
}
|
|
res.json({ success: true });
|
|
} catch (err) {
|
|
handleError(res, err, 'SAVE JOB SPEC');
|
|
} finally {
|
|
if (connection) connection.release();
|
|
}
|
|
});
|
|
|
|
// 6.7.3. Delete Job Spec Standard
|
|
app.delete('/api/job-specs/:id', async (req, res) => {
|
|
const { id } = req.params;
|
|
let connection;
|
|
try {
|
|
connection = await pool.getConnection();
|
|
await connection.query('DELETE FROM job_spec_standards WHERE id = ?', [id]);
|
|
res.json({ success: true });
|
|
} catch (err) {
|
|
handleError(res, err, 'DELETE JOB SPEC');
|
|
} finally {
|
|
if (connection) connection.release();
|
|
}
|
|
});
|
|
|
|
// 6.8. Get System Users List
|
|
app.get('/api/system-users', async (req, res) => {
|
|
try {
|
|
const [rows] = await pool.query('SELECT * FROM system_users ORDER BY user_name');
|
|
res.json(rows);
|
|
} catch (err) {
|
|
handleError(res, err, 'GET SYSTEM USERS');
|
|
}
|
|
});
|
|
|
|
// 6.9. Save System User (Add or Update)
|
|
app.post('/api/system-users/save', async (req, res) => {
|
|
const { id, emp_no, user_name, dept_name, position, status } = req.body;
|
|
let connection;
|
|
try {
|
|
connection = await pool.getConnection();
|
|
if (id) {
|
|
await connection.query(
|
|
'UPDATE system_users SET emp_no = ?, user_name = ?, dept_name = ?, position = ?, status = ? WHERE id = ?',
|
|
[emp_no, user_name, dept_name, position, status, id]
|
|
);
|
|
} else {
|
|
const newId = 'USER-' + Math.random().toString(36).substring(2, 9).toUpperCase();
|
|
await connection.query(
|
|
'INSERT INTO system_users (id, emp_no, user_name, dept_name, position, status) VALUES (?, ?, ?, ?, ?, ?)',
|
|
[newId, emp_no, user_name, dept_name, position, status]
|
|
);
|
|
}
|
|
res.json({ success: true });
|
|
} catch (err) {
|
|
handleError(res, err, 'SAVE SYSTEM USER');
|
|
} finally {
|
|
if (connection) connection.release();
|
|
}
|
|
});
|
|
|
|
// 6.10. Delete System User
|
|
app.delete('/api/system-users/:id', async (req, res) => {
|
|
const { id } = req.params;
|
|
let connection;
|
|
try {
|
|
connection = await pool.getConnection();
|
|
await connection.query('DELETE FROM system_users WHERE id = ?', [id]);
|
|
res.json({ success: true });
|
|
} catch (err) {
|
|
handleError(res, err, 'DELETE SYSTEM USER');
|
|
} finally {
|
|
if (connection) connection.release();
|
|
}
|
|
});
|
|
|
|
app.post('/api/maps/save', async (req, res) => {
|
|
let connection;
|
|
try {
|
|
const { path, boxes } = req.body;
|
|
if (!path) return res.status(400).json({ error: 'Path is required' });
|
|
if (!Array.isArray(boxes)) return res.status(400).json({ error: 'Boxes must be an array' });
|
|
|
|
connection = await pool.getConnection();
|
|
await connection.beginTransaction();
|
|
|
|
const cleanKey = getCleanMapKey(path);
|
|
const locName = getLocationName(path);
|
|
|
|
// 1. Get old location codes for this map
|
|
const [oldLocs] = await connection.query(
|
|
'SELECT location_code FROM physical_locations WHERE map_image = ?',
|
|
[path]
|
|
);
|
|
const oldLocCodes = oldLocs.map(r => r.location_code);
|
|
|
|
// 2. Deactivate and clear foreign key references in asset_location to these old location codes
|
|
if (oldLocCodes.length > 0) {
|
|
await connection.query(
|
|
'UPDATE asset_location SET is_active = 0, deactivated_at = NOW(), physical_location_code = NULL WHERE physical_location_code IN (?)',
|
|
[oldLocCodes]
|
|
);
|
|
}
|
|
|
|
// 3. Delete old physical locations for this map
|
|
await connection.query(
|
|
'DELETE FROM physical_locations WHERE map_image = ?',
|
|
[path]
|
|
);
|
|
|
|
// 4. Insert new physical locations and setup asset_location mappings
|
|
for (let i = 0; i < boxes.length; i++) {
|
|
const box = boxes[i];
|
|
const padIdx = String(i + 1).padStart(3, '0');
|
|
const locCode = `LOC-${cleanKey}-${padIdx}`;
|
|
const locDetail = getLocationDetail(path, i);
|
|
|
|
// Insert physical location
|
|
await connection.query(`
|
|
INSERT INTO physical_locations
|
|
(location_code, location_name, location_detail, map_image, map_x, map_y, map_w, map_h)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?)
|
|
`, [locCode, locName, locDetail, path, box.x, box.y, box.w, box.h]);
|
|
|
|
// If asset_id is mapped, update asset_location
|
|
if (box.asset_id) {
|
|
// Deactivate old active locations for this asset
|
|
await connection.query(
|
|
'UPDATE asset_location SET is_active = 0, deactivated_at = NOW() WHERE asset_id = ? AND is_active = 1',
|
|
[box.asset_id]
|
|
);
|
|
|
|
// Insert new active location mapping
|
|
await connection.query(`
|
|
INSERT INTO asset_location
|
|
(asset_id, location, location_detail, location_photo, loc_x, loc_y, physical_location_code, is_active)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, 1)
|
|
`, [box.asset_id, locName, locDetail, path, box.x, box.y, locCode]);
|
|
}
|
|
}
|
|
|
|
await connection.commit();
|
|
res.json({ success: true, message: 'Map and Database synced successfully' });
|
|
} catch (err) {
|
|
if (connection) await connection.rollback();
|
|
handleError(res, err, 'SAVE MAPS SYNC');
|
|
} finally {
|
|
if (connection) connection.release();
|
|
}
|
|
});
|
|
|
|
// ==========================================
|
|
// 8. QR Asset Audit & Scan APIs
|
|
// ==========================================
|
|
|
|
// GET all physical locations
|
|
app.get('/api/physical-locations', async (req, res) => {
|
|
try {
|
|
const [rows] = await pool.query('SELECT * FROM physical_locations ORDER BY location_code');
|
|
res.json(rows);
|
|
} catch (err) {
|
|
handleError(res, err, 'GET PHYSICAL LOCATIONS');
|
|
}
|
|
});
|
|
|
|
// POST register scan (mobile)
|
|
app.post('/api/audit/scan', async (req, res) => {
|
|
let connection;
|
|
try {
|
|
const { asset_code, physical_location_code } = req.body;
|
|
if (!asset_code || !physical_location_code) {
|
|
return res.status(400).json({ error: 'asset_code and physical_location_code are required' });
|
|
}
|
|
|
|
connection = await pool.getConnection();
|
|
|
|
// Verify if asset exists
|
|
const [assets] = await connection.query('SELECT id FROM asset_core WHERE asset_code = ?', [asset_code]);
|
|
if (assets.length === 0) {
|
|
return res.status(404).json({ error: `Asset with code ${asset_code} not found` });
|
|
}
|
|
|
|
// Insert pending audit record
|
|
const [result] = await connection.query(
|
|
'INSERT INTO asset_audit_pending (asset_code, physical_location_code, status) VALUES (?, ?, ?)',
|
|
[asset_code, physical_location_code, 'PENDING']
|
|
);
|
|
|
|
res.json({ success: true, pending_id: result.insertId });
|
|
} catch (err) {
|
|
handleError(res, err, 'REGISTER SCAN');
|
|
} finally {
|
|
if (connection) connection.release();
|
|
}
|
|
});
|
|
|
|
// GET pending audits list (admin)
|
|
app.get('/api/audit/pending', async (req, res) => {
|
|
try {
|
|
const [rows] = await pool.query(`
|
|
SELECT
|
|
ap.*,
|
|
c.id AS asset_id,
|
|
c.asset_purpose,
|
|
c.asset_type,
|
|
pl.location_name,
|
|
pl.location_detail,
|
|
pl.map_image,
|
|
l.location AS old_location,
|
|
l.location_detail AS old_location_detail
|
|
FROM asset_audit_pending ap
|
|
JOIN asset_core c ON c.asset_code = ap.asset_code
|
|
JOIN physical_locations pl ON pl.location_code = ap.physical_location_code
|
|
LEFT JOIN asset_location l ON l.asset_id = c.id AND l.is_active = 1
|
|
ORDER BY ap.scanned_at DESC
|
|
`);
|
|
res.json(rows);
|
|
} catch (err) {
|
|
handleError(res, err, 'GET PENDING AUDITS');
|
|
}
|
|
});
|
|
|
|
// POST approve audits (admin)
|
|
app.post('/api/audit/approve', async (req, res) => {
|
|
let connection;
|
|
try {
|
|
const { pending_ids, processed_by } = req.body;
|
|
if (!Array.isArray(pending_ids) || pending_ids.length === 0) {
|
|
return res.status(400).json({ error: 'pending_ids must be a non-empty array' });
|
|
}
|
|
|
|
connection = await pool.getConnection();
|
|
await connection.beginTransaction();
|
|
|
|
let mapConfigChanged = false;
|
|
let mapConfig = {};
|
|
if (fs.existsSync('map_config.json')) {
|
|
mapConfig = JSON.parse(fs.readFileSync('map_config.json', 'utf8') || '{}');
|
|
}
|
|
|
|
for (const pendingId of pending_ids) {
|
|
// 1. Get pending scan details
|
|
const [pendings] = await connection.query(
|
|
'SELECT asset_code, physical_location_code FROM asset_audit_pending WHERE id = ? AND status = ?',
|
|
[pendingId, 'PENDING']
|
|
);
|
|
if (pendings.length === 0) continue;
|
|
|
|
const { asset_code, physical_location_code } = pendings[0];
|
|
|
|
// 2. Get asset ID
|
|
const [assets] = await connection.query('SELECT id FROM asset_core WHERE asset_code = ?', [asset_code]);
|
|
if (assets.length === 0) continue;
|
|
const assetId = assets[0].id;
|
|
|
|
// 3. Get physical location details
|
|
const [locations] = await connection.query(
|
|
'SELECT location_name, location_detail, map_image, map_x, map_y FROM physical_locations WHERE location_code = ?',
|
|
[physical_location_code]
|
|
);
|
|
if (locations.length === 0) continue;
|
|
const loc = locations[0];
|
|
|
|
// 4. Deactivate old active locations for this asset
|
|
await connection.query(
|
|
'UPDATE asset_location SET is_active = 0, deactivated_at = NOW() WHERE asset_id = ? AND is_active = 1',
|
|
[assetId]
|
|
);
|
|
|
|
// 5. Insert new active location
|
|
await connection.query(`
|
|
INSERT INTO asset_location
|
|
(asset_id, location, location_detail, location_photo, loc_x, loc_y, physical_location_code, is_active)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, 1)
|
|
`, [assetId, loc.location_name, loc.location_detail, loc.map_image, loc.map_x, loc.map_y, physical_location_code]);
|
|
|
|
// 6. Update pending audit status
|
|
await connection.query(
|
|
'UPDATE asset_audit_pending SET status = ?, processed_at = NOW(), processed_by = ? WHERE id = ?',
|
|
['APPROVED', processed_by || 'ADMIN', pendingId]
|
|
);
|
|
|
|
// 7. Sync map_config.json
|
|
// Remove asset from any other map coordinates
|
|
for (const [mapPath, boxes] of Object.entries(mapConfig)) {
|
|
let changed = false;
|
|
const newBoxes = boxes.map(b => {
|
|
if (b.asset_id === assetId) {
|
|
changed = true;
|
|
return { ...b, asset_id: null };
|
|
}
|
|
return b;
|
|
});
|
|
if (changed) {
|
|
mapConfig[mapPath] = newBoxes;
|
|
mapConfigChanged = true;
|
|
}
|
|
}
|
|
|
|
// Add asset to the new map coordinate box matching map_image, map_x, map_y
|
|
if (mapConfig[loc.map_image]) {
|
|
const ax = parseFloat(loc.map_x);
|
|
const ay = parseFloat(loc.map_y);
|
|
const boxes = mapConfig[loc.map_image];
|
|
const matchedBox = boxes.find(b => {
|
|
const bx = parseFloat(b.x);
|
|
const by = parseFloat(b.y);
|
|
return Math.abs(bx - ax) < 0.1 && Math.abs(by - ay) < 0.1;
|
|
});
|
|
if (matchedBox) {
|
|
matchedBox.asset_id = assetId;
|
|
mapConfigChanged = true;
|
|
}
|
|
}
|
|
}
|
|
|
|
if (mapConfigChanged) {
|
|
fs.writeFileSync('map_config.json', JSON.stringify(mapConfig, null, 2));
|
|
}
|
|
|
|
await connection.commit();
|
|
res.json({ success: true, message: 'Audits approved successfully' });
|
|
} catch (err) {
|
|
if (connection) await connection.rollback();
|
|
handleError(res, err, 'APPROVE AUDITS');
|
|
} finally {
|
|
if (connection) connection.release();
|
|
}
|
|
});
|
|
|
|
// POST reject audits (admin)
|
|
app.post('/api/audit/reject', async (req, res) => {
|
|
let connection;
|
|
try {
|
|
const { pending_ids, processed_by } = req.body;
|
|
if (!Array.isArray(pending_ids) || pending_ids.length === 0) {
|
|
return res.status(400).json({ error: 'pending_ids must be a non-empty array' });
|
|
}
|
|
|
|
connection = await pool.getConnection();
|
|
await connection.beginTransaction();
|
|
|
|
for (const pendingId of pending_ids) {
|
|
await connection.query(
|
|
'UPDATE asset_audit_pending SET status = ?, processed_at = NOW(), processed_by = ? WHERE id = ? AND status = ?',
|
|
['REJECTED', processed_by || 'ADMIN', pendingId, 'PENDING']
|
|
);
|
|
}
|
|
|
|
await connection.commit();
|
|
res.json({ success: true, message: 'Audits rejected successfully' });
|
|
} catch (err) {
|
|
if (connection) await connection.rollback();
|
|
handleError(res, err, 'REJECT AUDITS');
|
|
} finally {
|
|
if (connection) connection.release();
|
|
}
|
|
});
|
|
|
|
// 7. File Upload API (Base64)
|
|
app.post('/api/upload', (req, res) => {
|
|
try {
|
|
const { fileName, fileData } = req.body;
|
|
if (!fileName || !fileData) return res.status(400).json({ error: 'FileName and FileData are required' });
|
|
|
|
// base64 데이터에서 실제 바이너리 추출
|
|
const base64Data = fileData.replace(/^data:.*;base64,/, "");
|
|
const buffer = Buffer.from(base64Data, 'base64');
|
|
|
|
// 고유한 파일명 생성 (타임스탬프 결합)
|
|
const timestamp = Date.now();
|
|
const safeFileName = `${timestamp}_${fileName.replace(/[^a-zA-Z0-9._-]/g, '_')}`;
|
|
const filePath = `uploads/${safeFileName}`;
|
|
|
|
fs.writeFileSync(filePath, buffer);
|
|
|
|
console.log(`파일 업로드 성공: ${filePath}`);
|
|
res.json({ success: true, filePath: `/${filePath}`, fileName: safeFileName });
|
|
} catch (err) {
|
|
handleError(res, err, 'FILE UPLOAD');
|
|
}
|
|
});
|
|
|
|
app.listen(process.env.PORT || 3000, '0.0.0.0', () => {
|
|
console.log(`📡 ITAM BACKEND SERVER RUNNING ON PORT ${process.env.PORT || 3000} (V3 Normalized)`);
|
|
});
|