248 lines
8.6 KiB
TypeScript
248 lines
8.6 KiB
TypeScript
/**
|
||
* 导入历史工单数据
|
||
* 用法: npx tsx scripts/import-tickets.ts [excel文件路径]
|
||
* 默认路径: templates-docs/工单跟踪记录-总表.xlsx
|
||
*/
|
||
|
||
import Database from 'better-sqlite3'
|
||
import path from 'path'
|
||
import fs from 'fs'
|
||
import bcrypt from 'bcryptjs'
|
||
|
||
const XLSX = require('xlsx')
|
||
|
||
const dbPath = process.env.DATABASE_PATH || './data/issue.db'
|
||
const dbDir = path.dirname(dbPath)
|
||
if (!fs.existsSync(dbDir)) fs.mkdirSync(dbDir, { recursive: true })
|
||
|
||
const db = new Database(dbPath)
|
||
db.pragma('journal_mode = WAL')
|
||
db.pragma('foreign_keys = ON')
|
||
|
||
function initDb() {
|
||
db.exec(`
|
||
CREATE TABLE IF NOT EXISTS users (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
username TEXT NOT NULL UNIQUE,
|
||
password_hash TEXT NOT NULL,
|
||
display_name TEXT NOT NULL,
|
||
email TEXT,
|
||
role TEXT NOT NULL DEFAULT 'viewer',
|
||
is_active INTEGER NOT NULL DEFAULT 1,
|
||
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
||
updated_at TEXT NOT NULL DEFAULT (datetime('now'))
|
||
);
|
||
CREATE TABLE IF NOT EXISTS roles (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
name TEXT NOT NULL UNIQUE,
|
||
display_name TEXT NOT NULL,
|
||
permissions TEXT NOT NULL DEFAULT '[]',
|
||
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
||
);
|
||
CREATE TABLE IF NOT EXISTS sessions (
|
||
id TEXT PRIMARY KEY,
|
||
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
expires_at TEXT NOT NULL,
|
||
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
||
);
|
||
CREATE TABLE IF NOT EXISTS tickets (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
ticket_no TEXT NOT NULL UNIQUE,
|
||
device_ip TEXT,
|
||
device_sn TEXT,
|
||
device_name TEXT,
|
||
content TEXT,
|
||
assign_time TEXT,
|
||
close_time TEXT,
|
||
duration_minutes INTEGER,
|
||
availability REAL,
|
||
process_summary TEXT,
|
||
conclusion TEXT,
|
||
fault_category TEXT,
|
||
fault_subcategory TEXT,
|
||
parts_replaced TEXT,
|
||
current_status TEXT NOT NULL DEFAULT 'open',
|
||
counted_in_sla INTEGER NOT NULL DEFAULT 1,
|
||
responsibility TEXT,
|
||
created_by INTEGER REFERENCES users(id),
|
||
updated_by INTEGER REFERENCES users(id),
|
||
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
||
updated_at TEXT NOT NULL DEFAULT (datetime('now'))
|
||
);
|
||
CREATE TABLE IF NOT EXISTS ticket_steps (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
ticket_id INTEGER NOT NULL REFERENCES tickets(id) ON DELETE CASCADE,
|
||
step_order INTEGER NOT NULL,
|
||
time_node TEXT,
|
||
handler TEXT,
|
||
description TEXT,
|
||
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
||
);
|
||
CREATE TABLE IF NOT EXISTS reports (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
report_type TEXT NOT NULL,
|
||
period_start TEXT,
|
||
period_end TEXT,
|
||
format TEXT NOT NULL DEFAULT 'pdf',
|
||
file_path TEXT,
|
||
file_name TEXT,
|
||
status TEXT NOT NULL DEFAULT 'pending',
|
||
error_message TEXT,
|
||
created_by INTEGER REFERENCES users(id),
|
||
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
||
);
|
||
CREATE TABLE IF NOT EXISTS audit_logs (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
user_id INTEGER REFERENCES users(id),
|
||
action TEXT NOT NULL,
|
||
entity_type TEXT NOT NULL,
|
||
entity_id INTEGER,
|
||
details TEXT,
|
||
ip_address TEXT,
|
||
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
||
);
|
||
CREATE INDEX IF NOT EXISTS idx_tickets_device_ip ON tickets(device_ip);
|
||
CREATE INDEX IF NOT EXISTS idx_tickets_assign_time ON tickets(assign_time);
|
||
CREATE INDEX IF NOT EXISTS idx_tickets_current_status ON tickets(current_status);
|
||
CREATE INDEX IF NOT EXISTS idx_ticket_steps_ticket_id ON ticket_steps(ticket_id);
|
||
`)
|
||
|
||
const existingAdmin = db.prepare('SELECT id FROM users WHERE username = ?').get('admin')
|
||
if (!existingAdmin) {
|
||
const passwordHash = bcrypt.hashSync('admin123', 10)
|
||
db.prepare('INSERT INTO users (username, password_hash, display_name, role) VALUES (?, ?, ?, ?)')
|
||
.run('admin', passwordHash, '系统管理员', 'admin')
|
||
}
|
||
|
||
const defaultRoles = [
|
||
{ name: 'admin', display_name: '管理员', permissions: '["*"]' },
|
||
{ name: 'operator', display_name: '运维人员', permissions: '["tickets:read","tickets:write","reports:read"]' },
|
||
{ name: 'viewer', display_name: '查看者', permissions: '["tickets:read","reports:read"]' },
|
||
]
|
||
for (const r of defaultRoles) {
|
||
const ex = db.prepare('SELECT id FROM roles WHERE name = ?').get(r.name)
|
||
if (!ex) db.prepare('INSERT INTO roles (name, display_name, permissions) VALUES (?, ?, ?)')
|
||
.run(r.name, r.display_name, r.permissions)
|
||
}
|
||
}
|
||
|
||
function excelDateToJS(val: any): string | null {
|
||
if (!val) return null
|
||
if (val instanceof Date) return val.toISOString().replace('T', ' ').slice(0, 19)
|
||
if (typeof val === 'number') {
|
||
// Excel serial date: days since 1900-01-01 (with 1900 leap year bug)
|
||
const utcDays = val - 25569
|
||
const utcSeconds = utcDays * 86400
|
||
const d = new Date(utcSeconds * 1000)
|
||
return d.toISOString().replace('T', ' ').slice(0, 19)
|
||
}
|
||
const s = String(val).trim()
|
||
return s || null
|
||
}
|
||
|
||
function cleanValue(val: any): any {
|
||
if (val === null || val === undefined || val === '' || (typeof val === 'number' && isNaN(val))) return null
|
||
if (typeof val === 'string' && val.trim() === '') return null
|
||
if (typeof val === 'string' && val.trim() === '/') return null
|
||
return val
|
||
}
|
||
|
||
function importTicketSheet(ws: any) {
|
||
const data = XLSX.utils.sheet_to_json(ws, { header: 1, defval: '' })
|
||
if (data.length < 3) {
|
||
console.log(' Sheet has insufficient data')
|
||
return 0
|
||
}
|
||
|
||
// Headers in row 0, title row in row 0 too, actual data from row 2
|
||
const headers = data[0] as string[]
|
||
const rows = data.slice(2) // Skip header row + title row
|
||
|
||
const colIdx: Record<string, number> = {}
|
||
for (let i = 0; i < headers.length; i++) {
|
||
colIdx[headers[i]] = i
|
||
}
|
||
|
||
const stmt = db.prepare(`
|
||
INSERT OR REPLACE INTO tickets (
|
||
ticket_no, device_ip, device_sn, device_name, content,
|
||
assign_time, close_time, duration_minutes, availability,
|
||
process_summary, conclusion, fault_category, fault_subcategory,
|
||
parts_replaced, current_status, counted_in_sla
|
||
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
||
`)
|
||
|
||
let count = 0
|
||
const statusMap: Record<string, string> = {
|
||
'已恢复': 'resolved',
|
||
'处理中': 'in_progress',
|
||
'已结单': 'closed',
|
||
'待处理': 'open',
|
||
'已关闭': 'closed',
|
||
}
|
||
|
||
const transaction = db.transaction(() => {
|
||
for (const row of rows) {
|
||
const r = row as any[]
|
||
if (!r || r.length === 0) continue
|
||
|
||
const ticketNo = cleanValue(r[colIdx['故障编号/工单号']])
|
||
if (!ticketNo) continue
|
||
|
||
const rawStatus = cleanValue(r[colIdx['目前状态']]) || 'resolved'
|
||
const mappedStatus = statusMap[String(rawStatus)] || 'resolved'
|
||
|
||
const slaVal = cleanValue(r[colIdx['是否计入SLA']])
|
||
const countedInSla = slaVal === '是' ? 1 : 0
|
||
|
||
stmt.run(
|
||
ticketNo,
|
||
cleanValue(r[colIdx['故障节点']]), // device_ip
|
||
cleanValue(r[colIdx['SN']]), // device_sn
|
||
null, // device_name (not in Excel)
|
||
cleanValue(r[colIdx['故障表现/工单内容']]), // content
|
||
excelDateToJS(r[colIdx['派单时间']]), // assign_time
|
||
excelDateToJS(r[colIdx['结单时间']]), // close_time
|
||
cleanValue(r[colIdx['处理时长\n（分钟）']]), // duration_minutes
|
||
cleanValue(r[colIdx['单次月度可用性']]), // availability
|
||
cleanValue(r[colIdx['处理过程']]), // process_summary
|
||
cleanValue(r[colIdx['故障结论']]), // conclusion
|
||
cleanValue(r[colIdx['故障分类']]), // fault_category
|
||
cleanValue(r[colIdx['故障分类二']]), // fault_subcategory
|
||
cleanValue(r[colIdx['是否更换配件']]), // parts_replaced
|
||
mappedStatus,
|
||
countedInSla,
|
||
)
|
||
count++
|
||
}
|
||
})
|
||
|
||
transaction()
|
||
return count
|
||
}
|
||
|
||
async function main() {
|
||
const excelPath = process.argv[2] || path.join(__dirname, '..', 'templates-docs', '工单跟踪记录-总表.xlsx')
|
||
|
||
if (!fs.existsSync(excelPath)) {
|
||
console.error(`Excel file not found: ${excelPath}`)
|
||
process.exit(1)
|
||
}
|
||
|
||
console.log('Initializing database...')
|
||
initDb()
|
||
|
||
console.log(`Reading Excel: ${excelPath}`)
|
||
const wb = XLSX.readFile(excelPath)
|
||
|
||
console.log('Importing tickets from 故障记录汇总...')
|
||
const sheet = wb.Sheets['故障记录汇总']
|
||
const count = importTicketSheet(sheet)
|
||
console.log(` Imported ${count} tickets`)
|
||
|
||
console.log(`\nTotal: ${count} tickets imported`)
|
||
db.close()
|
||
}
|
||
|
||
main().catch(console.error)
|