285 lines
11 KiB
JavaScript
285 lines
11 KiB
JavaScript
const Database = require('better-sqlite3');
|
||
const path = require('path');
|
||
|
||
const dbPath = path.join(__dirname, 'data', 'device_manager.db');
|
||
|
||
// 确保 data 目录存在
|
||
const fs = require('fs');
|
||
const dataDir = path.join(__dirname, 'data');
|
||
if (!fs.existsSync(dataDir)) {
|
||
fs.mkdirSync(dataDir, { recursive: true });
|
||
}
|
||
|
||
const db = new Database(dbPath);
|
||
|
||
// 启用 WAL 模式提升性能
|
||
db.pragma('journal_mode = WAL');
|
||
|
||
// 初始化数据库表
|
||
function initDatabase() {
|
||
// 设备表
|
||
db.exec(`
|
||
CREATE TABLE IF NOT EXISTS devices (
|
||
id TEXT PRIMARY KEY,
|
||
name TEXT NOT NULL,
|
||
type TEXT NOT NULL,
|
||
model TEXT,
|
||
serial_number TEXT,
|
||
status TEXT DEFAULT '正常',
|
||
location TEXT,
|
||
purchase_date TEXT,
|
||
warranty_date TEXT,
|
||
responsible_person TEXT,
|
||
description TEXT,
|
||
created_at TEXT DEFAULT (datetime('now', 'localtime')),
|
||
updated_at TEXT DEFAULT (datetime('now', 'localtime'))
|
||
)
|
||
`);
|
||
|
||
// 人员表
|
||
db.exec(`
|
||
CREATE TABLE IF NOT EXISTS personnel (
|
||
id TEXT PRIMARY KEY,
|
||
name TEXT NOT NULL,
|
||
employee_id TEXT UNIQUE,
|
||
department TEXT,
|
||
position TEXT,
|
||
phone TEXT,
|
||
email TEXT,
|
||
status TEXT DEFAULT '在职',
|
||
entry_date TEXT,
|
||
description TEXT,
|
||
created_at TEXT DEFAULT (datetime('now', 'localtime')),
|
||
updated_at TEXT DEFAULT (datetime('now', 'localtime'))
|
||
)
|
||
`);
|
||
|
||
// 通知表
|
||
db.exec(`
|
||
CREATE TABLE IF NOT EXISTS notifications (
|
||
id TEXT PRIMARY KEY,
|
||
title TEXT NOT NULL,
|
||
content TEXT NOT NULL,
|
||
type TEXT DEFAULT '普通',
|
||
priority TEXT DEFAULT '普通',
|
||
is_read INTEGER DEFAULT 0,
|
||
target TEXT DEFAULT '全部',
|
||
created_at TEXT DEFAULT (datetime('now', 'localtime')),
|
||
updated_at TEXT DEFAULT (datetime('now', 'localtime'))
|
||
)
|
||
`);
|
||
|
||
// 用户表
|
||
db.exec(`
|
||
CREATE TABLE IF NOT EXISTS users (
|
||
id TEXT PRIMARY KEY,
|
||
username TEXT NOT NULL UNIQUE,
|
||
password TEXT NOT NULL,
|
||
role TEXT DEFAULT 'user',
|
||
display_name TEXT,
|
||
created_at TEXT DEFAULT (datetime('now', 'localtime'))
|
||
)
|
||
`);
|
||
|
||
// 用户表增加 personnel_id 关联字段（人员与账户 1:1）
|
||
const userCols = db.prepare('PRAGMA table_info(users)').all();
|
||
if (!userCols.some(c => c.name === 'personnel_id')) {
|
||
db.exec('ALTER TABLE users ADD COLUMN personnel_id TEXT');
|
||
}
|
||
// 用户表增加最后登录时间字段（用于在线状态展示）
|
||
if (!userCols.some(c => c.name === 'last_login')) {
|
||
db.exec('ALTER TABLE users ADD COLUMN last_login TEXT');
|
||
}
|
||
|
||
// 设备表增加报废相关字段
|
||
const deviceCols = db.prepare('PRAGMA table_info(devices)').all();
|
||
if (!deviceCols.some(c => c.name === 'scrap_reason')) {
|
||
db.exec("ALTER TABLE devices ADD COLUMN scrap_reason TEXT DEFAULT ''");
|
||
db.exec("ALTER TABLE devices ADD COLUMN scrap_date TEXT DEFAULT ''");
|
||
db.exec("ALTER TABLE devices ADD COLUMN scrap_operator TEXT DEFAULT ''");
|
||
}
|
||
|
||
// 维修记录表
|
||
db.exec(`
|
||
CREATE TABLE IF NOT EXISTS maintenance_records (
|
||
id TEXT PRIMARY KEY,
|
||
device_id TEXT NOT NULL,
|
||
device_name TEXT DEFAULT '',
|
||
type TEXT DEFAULT '报修',
|
||
remark TEXT DEFAULT '',
|
||
operator TEXT DEFAULT '',
|
||
created_at TEXT DEFAULT (datetime('now', 'localtime'))
|
||
)
|
||
`);
|
||
|
||
// 设备操作日志表
|
||
db.exec(`
|
||
CREATE TABLE IF NOT EXISTS device_logs (
|
||
id TEXT PRIMARY KEY,
|
||
device_id TEXT NOT NULL,
|
||
device_name TEXT DEFAULT '',
|
||
action TEXT NOT NULL,
|
||
detail TEXT DEFAULT '',
|
||
operator TEXT DEFAULT '',
|
||
created_at TEXT DEFAULT (datetime('now', 'localtime'))
|
||
)
|
||
`);
|
||
|
||
// 设备借用记录表（借用/归还时间由服务器自动记录）
|
||
db.exec(`
|
||
CREATE TABLE IF NOT EXISTS device_loans (
|
||
id TEXT PRIMARY KEY,
|
||
device_id TEXT NOT NULL,
|
||
device_name TEXT DEFAULT '',
|
||
borrower_id TEXT DEFAULT '',
|
||
borrower TEXT DEFAULT '',
|
||
loan_photo TEXT DEFAULT '',
|
||
return_photo TEXT DEFAULT '',
|
||
status TEXT DEFAULT '借用中',
|
||
loan_time TEXT DEFAULT (datetime('now', 'localtime')),
|
||
return_time TEXT DEFAULT '',
|
||
remark TEXT DEFAULT '',
|
||
created_by TEXT DEFAULT '',
|
||
created_at TEXT DEFAULT (datetime('now', 'localtime'))
|
||
)
|
||
`);
|
||
|
||
// 账户操作日志表
|
||
db.exec(`
|
||
CREATE TABLE IF NOT EXISTS user_logs (
|
||
id TEXT PRIMARY KEY,
|
||
username TEXT DEFAULT '',
|
||
action TEXT NOT NULL,
|
||
detail TEXT DEFAULT '',
|
||
operator TEXT DEFAULT '',
|
||
created_at TEXT DEFAULT (datetime('now', 'localtime'))
|
||
)
|
||
`);
|
||
|
||
// 会话表（单聊 direct / 群聊 group）
|
||
db.exec(`
|
||
CREATE TABLE IF NOT EXISTS conversations (
|
||
id TEXT PRIMARY KEY,
|
||
type TEXT NOT NULL,
|
||
name TEXT DEFAULT '',
|
||
creator_id TEXT,
|
||
created_at TEXT DEFAULT (datetime('now', 'localtime'))
|
||
)
|
||
`);
|
||
|
||
// 会话成员表（last_read_message_id 用于未读计数）
|
||
db.exec(`
|
||
CREATE TABLE IF NOT EXISTS conversation_members (
|
||
conversation_id TEXT NOT NULL,
|
||
user_id TEXT NOT NULL,
|
||
role TEXT DEFAULT 'member',
|
||
last_read_message_id INTEGER DEFAULT 0,
|
||
joined_at TEXT DEFAULT (datetime('now', 'localtime')),
|
||
PRIMARY KEY (conversation_id, user_id)
|
||
)
|
||
`);
|
||
|
||
// 消息表（自增 id 兼作排序游标）
|
||
db.exec(`
|
||
CREATE TABLE IF NOT EXISTS messages (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
conversation_id TEXT NOT NULL,
|
||
sender_id TEXT NOT NULL,
|
||
content TEXT NOT NULL,
|
||
created_at TEXT DEFAULT (datetime('now', 'localtime'))
|
||
)
|
||
`);
|
||
|
||
// 聊天附件表（上传的图片/视频/文件）
|
||
db.exec(`
|
||
CREATE TABLE IF NOT EXISTS chat_files (
|
||
id TEXT PRIMARY KEY,
|
||
uploader_id TEXT NOT NULL,
|
||
file_name TEXT NOT NULL,
|
||
stored_name TEXT NOT NULL,
|
||
file_size INTEGER DEFAULT 0,
|
||
file_mime TEXT DEFAULT '',
|
||
created_at TEXT DEFAULT (datetime('now', 'localtime'))
|
||
)
|
||
`);
|
||
|
||
// 迁移：消息表补充媒体类型列（兼容旧库）
|
||
const msgCols = db.prepare('PRAGMA table_info(messages)').all().map(c => c.name);
|
||
if (!msgCols.includes('msg_type')) {
|
||
db.exec("ALTER TABLE messages ADD COLUMN msg_type TEXT DEFAULT 'text'");
|
||
}
|
||
if (!msgCols.includes('file_id')) {
|
||
db.exec("ALTER TABLE messages ADD COLUMN file_id TEXT DEFAULT ''");
|
||
}
|
||
|
||
// 推送事件表（聊天消息/设备报修/维修完成等，客户端轮询拉取）
|
||
db.exec(`
|
||
CREATE TABLE IF NOT EXISTS push_events (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
user_id TEXT NOT NULL,
|
||
kind TEXT NOT NULL,
|
||
title TEXT NOT NULL,
|
||
body TEXT DEFAULT '',
|
||
created_at TEXT DEFAULT (datetime('now', 'localtime'))
|
||
)
|
||
`);
|
||
db.exec('CREATE INDEX IF NOT EXISTS idx_push_events_user ON push_events (user_id, id)');
|
||
|
||
// 插入示例数据
|
||
const deviceCount = db.prepare('SELECT COUNT(*) as count FROM devices').get();
|
||
if (deviceCount.count === 0) {
|
||
const insertDevice = db.prepare(`
|
||
INSERT INTO devices (id, name, type, model, serial_number, status, location, purchase_date, warranty_date, responsible_person, description)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
||
`);
|
||
|
||
insertDevice.run('d001', '联想ThinkPad X1 Carbon', '笔记本电脑', 'X1 Carbon Gen 11', 'SN2024001', '正常', '办公室A区', '2024-01-15', '2027-01-15', '张三', '高管使用笔记本电脑');
|
||
insertDevice.run('d002', '戴尔U2723QE显示器', '显示器', 'U2723QE', 'SN2024002', '正常', '办公室A区', '2024-02-20', '2027-02-20', '李四', '27寸4K显示器');
|
||
insertDevice.run('d003', 'HP LaserJet Pro打印机', '打印机', 'M404dn', 'SN2024003', '维修中', '打印室', '2023-06-10', '2026-06-10', '王五', '网络打印机');
|
||
insertDevice.run('d004', '华为交换机', '网络设备', 'S5735-L24T4S', 'SN2024004', '正常', '机房', '2023-03-01', '2028-03-01', '赵六', '24口千兆交换机');
|
||
insertDevice.run('d005', 'ThinkStation P360', '台式机', 'P360 Tower', 'SN2024005', '已报废', '仓库', '2022-01-01', '2025-01-01', '张三', '开发工作站');
|
||
}
|
||
|
||
const personnelCount = db.prepare('SELECT COUNT(*) as count FROM personnel').get();
|
||
if (personnelCount.count === 0) {
|
||
const insertPerson = db.prepare(`
|
||
INSERT INTO personnel (id, name, employee_id, department, position, phone, email, status, entry_date, description)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
||
`);
|
||
|
||
insertPerson.run('p001', '张三', 'EMP001', '拍摄组', '拍摄设备主管', '13800138001', 'zhangsan@company.com', '在职', '2020-03-15', '负责拍摄设备与摄像器材管理');
|
||
insertPerson.run('p002', '李四', 'EMP002', '拍摄组', '摄影助理', '13800138002', 'lisi@company.com', '在职', '2021-06-01', '负责显示设备管理');
|
||
insertPerson.run('p003', '王五', 'EMP003', '后勤组', '后勤专员', '13800138003', 'wangwu@company.com', '在职', '2022-01-10', '负责办公设备与物资保障');
|
||
insertPerson.run('p004', '赵六', 'EMP004', '后勤组', '设备维护员', '13800138004', 'zhaoliu@company.com', '在职', '2019-09-01', '负责设备维护与保养');
|
||
insertPerson.run('p005', '钱七', 'EMP005', '监督组', '监督主管', '13800138005', 'qianqi@company.com', '在职', '2018-05-20', '负责设备使用监督与盘点核查');
|
||
}
|
||
|
||
const notifCount = db.prepare('SELECT COUNT(*) as count FROM notifications').get();
|
||
if (notifCount.count === 0) {
|
||
const insertNotif = db.prepare(`
|
||
INSERT INTO notifications (id, title, content, type, priority, is_read, target)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?)
|
||
`);
|
||
|
||
insertNotif.run('n001', '设备盘点通知', '请各部门于本月底前完成设备盘点工作，并提交盘点报告。', '通知', '高', 0, '全部');
|
||
insertNotif.run('n002', '打印机维修通知', '打印室HP打印机正在进行维修，预计明天恢复使用。', '公告', '普通', 0, '全部');
|
||
insertNotif.run('n003', '新设备到货通知', '新采购的10台联想笔记本已到货，请相关人员前来领取。', '通知', '普通', 1, '拍摄组');
|
||
insertNotif.run('n004', '系统升级提醒', '设备管理系统将于本周六凌晨2点进行升级维护。', '提醒', '高', 0, '全部');
|
||
}
|
||
|
||
// 初始化默认账户（管理员 + 示例普通用户）
|
||
const userCount = db.prepare('SELECT COUNT(*) as count FROM users').get();
|
||
if (userCount.count === 0) {
|
||
const insertUser = db.prepare(`
|
||
INSERT INTO users (id, username, password, role, display_name, personnel_id)
|
||
VALUES (?, ?, ?, ?, ?, ?)
|
||
`);
|
||
insertUser.run('u001', 'admin', 'admin123', 'admin', '系统管理员', null);
|
||
insertUser.run('u002', 'zhangsan', '123456', 'user', '张三', 'p001');
|
||
}
|
||
}
|
||
|
||
initDatabase();
|
||
|
||
module.exports = db;
|