372 lines
16 KiB
JavaScript
372 lines
16 KiB
JavaScript
import express from 'express';
|
|
import mysql from 'mysql2/promise';
|
|
import cors from 'cors';
|
|
import dotenv from 'dotenv';
|
|
|
|
dotenv.config();
|
|
|
|
const app = express();
|
|
app.use(cors());
|
|
app.use(express.json({ limit: '100mb' }));
|
|
|
|
// Request Logger
|
|
app.use((req, res, next) => {
|
|
console.log(`[${new Date().toISOString()}] ${req.method} ${req.url}`);
|
|
next();
|
|
});
|
|
|
|
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'),
|
|
charset: 'utf8mb4'
|
|
});
|
|
|
|
const handleError = (res, err, context, isGet = false) => {
|
|
console.error(`❌ [${context}] Error:`, err.message);
|
|
if (isGet) res.json([]);
|
|
else res.status(500).json({ error: err.message });
|
|
};
|
|
|
|
// --- Mapping Definitions ---
|
|
|
|
const HW_SELECT_FIELDS = `
|
|
id,
|
|
asset_type AS \`type\`,
|
|
corp AS \`법인\`,
|
|
asset_code AS \`자산코드\`,
|
|
purchase_date AS \`구매일\`,
|
|
user_name AS \`사용자\`,
|
|
dept AS \`현사용조직\`,
|
|
prev_org AS \`이전사용조직\`,
|
|
location AS \`위치\`,
|
|
manager_primary AS \`담당자_정\`,
|
|
manager_secondary AS \`담당자_부\`,
|
|
product_name AS \`모델명\`,
|
|
usage_category AS \`상세용도\`,
|
|
usage_description AS \`상세\`,
|
|
os AS \`OS\`,
|
|
cpu AS \`CPU\`,
|
|
gpu AS \`GPU\`,
|
|
ram AS \`RAM\`,
|
|
storage1 AS \`SSD1\`,
|
|
storage2 AS \`SSD2\`,
|
|
storage3 AS \`SSD3\`,
|
|
mainboard AS \`메인보드\`,
|
|
ip_address AS \`IP주소\`,
|
|
remote_tool AS \`원격접속\`,
|
|
server_id AS \`서버ID\`,
|
|
server_pw AS \`서버PW\`,
|
|
monitoring AS \`모니터링\`,
|
|
price AS \`금액\`,
|
|
vendor AS \`납품업체\`,
|
|
remarks AS \`비고\`,
|
|
asset_category AS \`category\`
|
|
`;
|
|
|
|
const HW_REVERSE_MAP = {
|
|
'id': 'id',
|
|
'type': 'asset_type',
|
|
'법인': 'corp',
|
|
'자산코드': 'asset_code',
|
|
'구매일': 'purchase_date',
|
|
'구매연월': 'purchase_date',
|
|
'사용자': 'user_name',
|
|
'현사용조직': 'dept',
|
|
'이전사용조직': 'prev_org',
|
|
'위치': 'location',
|
|
'담당자_정': 'manager_primary',
|
|
'담당자_부': 'manager_secondary',
|
|
'모델명': 'product_name',
|
|
'상세용도': 'usage_category',
|
|
'상세': 'usage_description',
|
|
'OS': 'os',
|
|
'CPU': 'cpu',
|
|
'GPU': 'gpu',
|
|
'RAM': 'ram',
|
|
'SSD1': 'storage1',
|
|
'SSD2': 'storage2',
|
|
'SSD3': 'storage3',
|
|
'메인보드': 'mainboard',
|
|
'IP주소': 'ip_address',
|
|
'원격접속': 'remote_tool',
|
|
'서버ID': 'server_id',
|
|
'서버PW': 'server_pw',
|
|
'모니터링': 'monitoring',
|
|
'금액': 'price',
|
|
'납품업체': 'vendor',
|
|
'비고': 'remarks',
|
|
'category': 'asset_category'
|
|
};
|
|
|
|
const mapObject = (obj, mapping) => {
|
|
const mapped = {};
|
|
Object.entries(obj).forEach(([key, val]) => {
|
|
const dbKey = mapping[key] || key;
|
|
mapped[dbKey] = val;
|
|
});
|
|
return mapped;
|
|
};
|
|
|
|
// --- GET Routes ---
|
|
|
|
app.get('/api/pc', async (req, res) => {
|
|
try {
|
|
const [rows] = await pool.query(`SELECT ${HW_SELECT_FIELDS} FROM asset_hardware WHERE asset_category = "개인PC" OR asset_code LIKE "PC%"`);
|
|
console.log(`📡 [GET /api/pc] Returning ${rows.length} rows`);
|
|
res.json(rows.map(r => ({ ...r, type: '개인PC', 구매연월: r.구매일 })));
|
|
} catch (err) { handleError(res, err, 'GET /api/pc', true); }
|
|
});
|
|
|
|
app.get('/api/server', async (req, res) => {
|
|
try {
|
|
const [rows] = await pool.query(`SELECT ${HW_SELECT_FIELDS} FROM asset_hardware WHERE asset_category = "서버" OR asset_code LIKE "SVR%"`);
|
|
console.log(`📡 [GET /api/server] Returning ${rows.length} rows`);
|
|
res.json(rows.map(r => ({ ...r, type: '서버', 구매연월: r.구매일 })));
|
|
} catch (err) { handleError(res, err, 'GET /api/server', true); }
|
|
});
|
|
|
|
app.get('/api/storage', async (req, res) => {
|
|
try {
|
|
const [rows] = await pool.query(`SELECT ${HW_SELECT_FIELDS} FROM asset_hardware WHERE asset_category = "스토리지" OR asset_code LIKE "STO%"`);
|
|
console.log(`📡 [GET /api/storage] Returning ${rows.length} rows`);
|
|
res.json(rows.map(r => ({ ...r, type: '스토리지', 구매연월: r.구매일 })));
|
|
} catch (err) { handleError(res, err, 'GET /api/storage', true); }
|
|
});
|
|
|
|
app.get('/api/equip', async (req, res) => {
|
|
try {
|
|
const [rows] = await pool.query(`SELECT ${HW_SELECT_FIELDS} FROM asset_hardware WHERE asset_category = "전산비품" OR asset_code LIKE "EQP%"`);
|
|
console.log(`📡 [GET /api/equip] Returning ${rows.length} rows`);
|
|
res.json(rows.map(r => ({ ...r, type: '전산비품', 구매연월: r.구매일 })));
|
|
} catch (err) { handleError(res, err, 'GET /api/equip', true); }
|
|
});
|
|
|
|
app.get('/api/mobile', async (req, res) => {
|
|
try {
|
|
const [rows] = await pool.query(`SELECT ${HW_SELECT_FIELDS} FROM asset_hardware WHERE asset_category = "모바일기기" OR asset_category LIKE "%모바일%" OR asset_code LIKE "MOB%"`);
|
|
console.log(`📡 [GET /api/mobile] Returning ${rows.length} rows`);
|
|
res.json(rows.map(r => ({ ...r, type: '모바일기기', 구매연월: r.구매일 })));
|
|
} catch (err) { handleError(res, err, 'GET /api/mobile', true); }
|
|
});
|
|
|
|
app.get('/api/asset/software/subscription', async (req, res) => {
|
|
try {
|
|
const [rows] = await pool.query(`
|
|
SELECT
|
|
id, category AS \`분야\`, corp AS \`법인\`, dept AS \`부서\`, product_name AS \`제품명\`,
|
|
quantity AS \`수량\`, price AS \`금액\`, purchase_date AS \`구매일\`, start_date AS \`시작일\`,
|
|
expiry_date AS \`만료일\`, vendor AS \`납품업체\`, remarks AS \`비고\`, license_type AS \`라이선스유형\`, account_name AS \`계정명\`
|
|
FROM asset_software_subscription
|
|
`);
|
|
console.log(`📡 [GET /api/asset/software/subscription] Returning ${rows.length} rows`);
|
|
res.json(rows.map(r => ({ ...r, type: '구독SW' })));
|
|
} catch (err) { handleError(res, err, 'GET /api/asset/software/subscription', true); }
|
|
});
|
|
|
|
app.get('/api/asset/software/perpetual', async (req, res) => {
|
|
try {
|
|
const [rows] = await pool.query(`
|
|
SELECT
|
|
id, category AS \`분야\`, corp AS \`법인\`, dept AS \`부서\`, product_name AS \`제품명\`,
|
|
quantity AS \`수량\`, price AS \`금액\`, purchase_date AS \`구매일\`, start_date AS \`시작일\`,
|
|
expiry_date AS \`만료일\`, vendor AS \`납품업체\`, remarks AS \`비고\`, license_key AS \`라이선스키\`, account_name AS \`계정명\`
|
|
FROM asset_software_perpetual
|
|
`);
|
|
console.log(`📡 [GET /api/asset/software/perpetual] Returning ${rows.length} rows`);
|
|
res.json(rows.map(r => ({ ...r, type: '영구SW' })));
|
|
} catch (err) { handleError(res, err, 'GET /api/asset/software/perpetual', true); }
|
|
});
|
|
|
|
app.get('/api/asset/cloud', async (req, res) => {
|
|
try {
|
|
const [rows] = await pool.query(`
|
|
SELECT
|
|
id, platform_name AS \`플랫폼명\`, corp AS \`법인\`, dept AS \`부서\`, product_name AS \`제품명\`,
|
|
account_name AS \`계정명\`, pay_method AS \`결제수단\`, pay_day AS \`결제일\`, card_num AS \`연결카드번호\`,
|
|
monthly_cost AS \`당월청구액\`, remarks AS \`비고\`
|
|
FROM asset_cloud
|
|
`);
|
|
console.log(`📡 [GET /api/asset/cloud] Returning ${rows.length} rows`);
|
|
res.json(rows.map(r => ({ ...r, type: '클라우드' })));
|
|
} catch (err) { handleError(res, err, 'GET /api/asset/cloud', true); }
|
|
});
|
|
|
|
app.get('/api/asset/domain', async (req, res) => {
|
|
try {
|
|
const [rows] = await pool.query(`SELECT * FROM asset_domain`);
|
|
res.json(rows);
|
|
} catch (err) { handleError(res, err, 'GET /api/asset/domain', true); }
|
|
});
|
|
|
|
app.get('/api/asset/software/assignment', async (req, res) => {
|
|
try {
|
|
const [rows] = await pool.query(`
|
|
SELECT
|
|
id, sw_id, corp AS \`법인\`, dept AS \`부서\`, position AS \`직위\`, user_name AS \`이름\`, usage_period AS \`사용기간\`, doc_name AS \`신청서명\`
|
|
FROM asset_software_assignment
|
|
`);
|
|
res.json(rows);
|
|
} catch (err) { handleError(res, err, 'GET /api/asset/software/assignment', true); }
|
|
});
|
|
|
|
app.get('/api/asset/history', async (req, res) => {
|
|
try {
|
|
const [rows] = await pool.query(`
|
|
SELECT
|
|
id, asset_id AS assetId, log_date AS \`date\`, log_user AS \`user\`, details, cost
|
|
FROM asset_history
|
|
`);
|
|
res.json(rows);
|
|
} catch (err) { handleError(res, err, 'GET /api/asset/history', true); }
|
|
});
|
|
|
|
// --- POST Batch Routes ---
|
|
|
|
async function saveHwBatch(category, items, res) {
|
|
const connection = await pool.getConnection();
|
|
try {
|
|
await connection.beginTransaction();
|
|
// 1. 해당 카테고리 기존 데이터 삭제
|
|
await connection.query('DELETE FROM asset_hardware WHERE asset_category = ?', [category]);
|
|
|
|
// 2. 새 데이터 삽입
|
|
for (const item of items) {
|
|
const dbRow = mapObject(item, HW_REVERSE_MAP);
|
|
dbRow.asset_category = category;
|
|
if (!dbRow.id) dbRow.id = Math.random().toString(36).substring(2, 9);
|
|
|
|
// DB 컬럼에 없는 필드 제거
|
|
const validColumns = ['id', 'asset_category', 'corp', 'asset_code', 'purchase_date', 'asset_type', 'usage_category', 'user_name', 'usage_description', 'dept', 'prev_org', 'location', 'manager_primary', 'manager_secondary', 'ip_address', 'remote_tool', 'server_id', 'server_pw', 'product_name', 'mainboard', 'os', 'cpu', 'ram', 'gpu', 'storage1', 'storage2', 'storage3', 'monitoring', 'price', 'vendor', 'remarks', 'storage_location', 'status'];
|
|
const filteredRow = {};
|
|
validColumns.forEach(col => { if (dbRow[col] !== undefined) filteredRow[col] = dbRow[col]; });
|
|
|
|
await connection.query('INSERT INTO asset_hardware SET ?', [filteredRow]);
|
|
}
|
|
await connection.commit();
|
|
res.json({ success: true, count: items.length });
|
|
} catch (err) {
|
|
await connection.rollback();
|
|
handleError(res, err, `BATCH SAVE ${category}`);
|
|
} finally {
|
|
connection.release();
|
|
}
|
|
}
|
|
|
|
app.post('/api/pc/batch', (req, res) => saveHwBatch('개인PC', req.body, res));
|
|
app.post('/api/server/batch', (req, res) => saveHwBatch('서버', req.body, res));
|
|
app.post('/api/storage/batch', (req, res) => saveHwBatch('스토리지', req.body, res));
|
|
app.post('/api/equip/batch', (req, res) => saveHwBatch('전산비품', req.body, res));
|
|
app.post('/api/mobile/batch', (req, res) => saveHwBatch('모바일기기', req.body, res));
|
|
|
|
app.post('/api/asset/software/subscription/batch', async (req, res) => {
|
|
const connection = await pool.getConnection();
|
|
try {
|
|
await connection.beginTransaction();
|
|
await connection.query('DELETE FROM asset_software_subscription');
|
|
const mapping = { '분야': 'category', '법인': 'corp', '부서': 'dept', '제품명': 'product_name', '수량': 'quantity', '금액': 'price', '구매일': 'purchase_date', '시작일': 'start_date', '만료일': 'expiry_date', '납품업체': 'vendor', '비고': 'remarks', '라이선스유형': 'license_type', '계정명': 'account_name' };
|
|
for (const item of req.body) {
|
|
const dbRow = mapObject(item, mapping);
|
|
if (!dbRow.id) dbRow.id = Math.random().toString(36).substring(2, 9);
|
|
const filteredRow = {};
|
|
['id', 'corp', 'category', 'dept', 'product_name', 'license_type', 'quantity', 'price', 'purchase_date', 'start_date', 'expiry_date', 'vendor', 'remarks', 'account_name'].forEach(c => { if (dbRow[c] !== undefined) filteredRow[c] = dbRow[c]; });
|
|
await connection.query('INSERT INTO asset_software_subscription SET ?', [filteredRow]);
|
|
}
|
|
await connection.commit();
|
|
res.json({ success: true });
|
|
} catch (err) { await connection.rollback(); handleError(res, err, 'BATCH SW SUB'); } finally { connection.release(); }
|
|
});
|
|
|
|
app.post('/api/asset/software/perpetual/batch', async (req, res) => {
|
|
const connection = await pool.getConnection();
|
|
try {
|
|
await connection.beginTransaction();
|
|
await connection.query('DELETE FROM asset_software_perpetual');
|
|
const mapping = { '분야': 'category', '법인': 'corp', '부서': 'dept', '제품명': 'product_name', '수량': 'quantity', '금액': 'price', '구매일': 'purchase_date', '시작일': 'start_date', '만료일': 'expiry_date', '납품업체': 'vendor', '비고': 'remarks', '라이선스키': 'license_key', '계정명': 'account_name' };
|
|
for (const item of req.body) {
|
|
const dbRow = mapObject(item, mapping);
|
|
if (!dbRow.id) dbRow.id = Math.random().toString(36).substring(2, 9);
|
|
const filteredRow = {};
|
|
['id', 'corp', 'category', 'dept', 'product_name', 'license_key', 'quantity', 'price', 'purchase_date', 'start_date', 'expiry_date', 'vendor', 'remarks', 'account_name'].forEach(c => { if (dbRow[c] !== undefined) filteredRow[c] = dbRow[c]; });
|
|
await connection.query('INSERT INTO asset_software_perpetual SET ?', [filteredRow]);
|
|
}
|
|
await connection.commit();
|
|
res.json({ success: true });
|
|
} catch (err) { await connection.rollback(); handleError(res, err, 'BATCH SW PERM'); } finally { connection.release(); }
|
|
});
|
|
|
|
app.post('/api/asset/cloud/batch', async (req, res) => {
|
|
const connection = await pool.getConnection();
|
|
try {
|
|
await connection.beginTransaction();
|
|
await connection.query('DELETE FROM asset_cloud');
|
|
const mapping = { '플랫폼명': 'platform_name', '법인': 'corp', '부서': 'dept', '제품명': 'product_name', '계정명': 'account_name', '결제수단': 'pay_method', '결제일': 'pay_day', '연결카드번호': 'card_num', '당월청구액': 'monthly_cost', '비고': 'remarks' };
|
|
for (const item of req.body) {
|
|
const dbRow = mapObject(item, mapping);
|
|
if (!dbRow.id) dbRow.id = Math.random().toString(36).substring(2, 9);
|
|
const filteredRow = {};
|
|
['id', 'platform_name', 'corp', 'dept', 'product_name', 'account_name', 'pay_method', 'pay_day', 'card_num', 'monthly_cost', 'remarks'].forEach(c => { if (dbRow[c] !== undefined) filteredRow[c] = dbRow[c]; });
|
|
await connection.query('INSERT INTO asset_cloud SET ?', [filteredRow]);
|
|
}
|
|
await connection.commit();
|
|
res.json({ success: true });
|
|
} catch (err) { await connection.rollback(); handleError(res, err, 'BATCH CLOUD'); } finally { connection.release(); }
|
|
});
|
|
|
|
app.post('/api/asset/software/assignment/batch', async (req, res) => {
|
|
const connection = await pool.getConnection();
|
|
try {
|
|
await connection.beginTransaction();
|
|
await connection.query('DELETE FROM asset_software_assignment');
|
|
const mapping = { '법인': 'corp', '부서': 'dept', '직위': 'position', '이름': 'user_name', '사용기간': 'usage_period', '신청서명': 'doc_name' };
|
|
for (const item of req.body) {
|
|
const dbRow = mapObject(item, mapping);
|
|
delete dbRow.id; // Auto-increment
|
|
await connection.query('INSERT INTO asset_software_assignment SET ?', [dbRow]);
|
|
}
|
|
await connection.commit();
|
|
res.json({ success: true });
|
|
} catch (err) { await connection.rollback(); handleError(res, err, 'BATCH SW ASSIGN'); } finally { connection.release(); }
|
|
});
|
|
|
|
app.post('/api/asset/history/batch', async (req, res) => {
|
|
const connection = await pool.getConnection();
|
|
try {
|
|
await connection.beginTransaction();
|
|
await connection.query('DELETE FROM asset_history');
|
|
for (const item of req.body) {
|
|
const dbRow = {
|
|
asset_id: item.assetId,
|
|
log_date: item.date,
|
|
log_user: item.user,
|
|
details: item.details,
|
|
cost: item.cost || 0
|
|
};
|
|
await connection.query('INSERT INTO asset_history SET ?', [dbRow]);
|
|
}
|
|
await connection.commit();
|
|
res.json({ success: true });
|
|
} catch (err) { await connection.rollback(); handleError(res, err, 'BATCH HISTORY'); } finally { connection.release(); }
|
|
});
|
|
|
|
app.get('/api/generate-asset-code', async (req, res) => {
|
|
try {
|
|
const { prefix } = req.query;
|
|
if (!prefix) return res.status(400).json({ error: 'Prefix is required' });
|
|
const [rows] = await pool.query('SELECT asset_code FROM asset_hardware WHERE asset_code LIKE ? ORDER BY asset_code DESC LIMIT 1', [`${prefix}%`]);
|
|
let nextNum = 1;
|
|
if (rows.length > 0) {
|
|
const lastCode = rows[0].asset_code;
|
|
const lastNum = parseInt(lastCode.split('-').pop() || '0');
|
|
nextNum = lastNum + 1;
|
|
}
|
|
res.json({ nextCode: `${prefix}${String(nextNum).padStart(3, '0')}` });
|
|
} catch (err) { handleError(res, err, 'GENERATE CODE'); }
|
|
});
|
|
|
|
app.listen(3000, '0.0.0.0', () => {
|
|
console.log('📡 ITAM BACKEND SERVER RUNNING ON PORT 3000 (Safe Korean Mapping)');
|
|
});
|