selfrelease
1426 lines
74 KiB
JavaScript
1426 lines
74 KiB
JavaScript
import express from 'express'
|
||
import { DatabaseSync } from 'node:sqlite'
|
||
import { mkdirSync, readdirSync, readFileSync, existsSync, createWriteStream, renameSync, unlinkSync } from 'node:fs'
|
||
import { dirname, join, extname } from 'node:path'
|
||
import { fileURLToPath } from 'node:url'
|
||
import { randomBytes } from 'node:crypto'
|
||
|
||
const root = dirname(dirname(fileURLToPath(import.meta.url)))
|
||
const dataDir = join(root, 'data')
|
||
const migrationsDir = join(root, 'migrations')
|
||
mkdirSync(dataDir, { recursive: true })
|
||
|
||
const dbPath = process.env.DATABASE_PATH || join(dataDir, 'reporter-station.db')
|
||
const db = new DatabaseSync(dbPath)
|
||
db.exec(`PRAGMA journal_mode = WAL; PRAGMA foreign_keys = ON;`)
|
||
|
||
// 运行所有待执行迁移（幂等性由 schema_migrations 表保障）
|
||
const applied = new Set(db.prepare('SELECT name FROM schema_migrations').all().map(r => r.name))
|
||
for (const file of readdirSync(migrationsDir).filter(f => f.endsWith('.sql') && !f.startsWith('_')).sort()) {
|
||
const name = file.replace('.sql', '')
|
||
if (applied.has(name)) continue
|
||
db.exec(readFileSync(join(migrationsDir, file), 'utf-8'))
|
||
db.prepare('INSERT INTO schema_migrations (name) VALUES (?)').run(name)
|
||
}
|
||
|
||
// ── Express 基础 ────────────────────────────────────────────────────────────
|
||
const app = express()
|
||
app.use(express.json({ limit: '1mb' }))
|
||
app.use('/attachments', express.static(join(root, 'public', 'attachments')))
|
||
|
||
// ── 身份认证（支持 token 会话 + header 模拟回退） ─────────────────────────
|
||
const identities = {
|
||
headquarters: { name: '林致远', station: null },
|
||
station: { name: '苏明远', station: '北京记者站' },
|
||
reporter: { name: '林晓', station: '北京记者站' },
|
||
}
|
||
|
||
/** 记录系统操作日志 */
|
||
function logSystem(actorRole, actorName, module, action, targetType, targetId, detail) {
|
||
try {
|
||
db.prepare(`INSERT INTO system_logs (actor_role, actor_name, module, action, target_type, target_id, detail)
|
||
VALUES (?,?,?,?,?,?,?)`).run(actorRole, actorName, module, action, targetType || null, String(targetId || ''), detail || null)
|
||
} catch (e) { console.error('logSystem error:', e.message) }
|
||
}
|
||
|
||
app.use((req, res, next) => {
|
||
const token = req.header('x-auth-token')
|
||
if (token) {
|
||
const session = db.prepare(`
|
||
SELECT s.*, p.station FROM login_sessions s
|
||
LEFT JOIN people p ON p.code = s.user_code
|
||
WHERE s.token = ? AND s.revoked_at IS NULL AND s.expires_at > datetime('now','localtime')
|
||
`).get(token)
|
||
if (session) {
|
||
req.user = { role: session.role, name: session.user_name, station: session.station || identities[session.role]?.station || null }
|
||
req.authToken = token
|
||
return next()
|
||
}
|
||
return res.status(401).json({ message: '会话已过期，请重新登录' })
|
||
}
|
||
// 回退：header 模拟（演示用）
|
||
const role = req.header('x-user-role') || 'headquarters'
|
||
req.user = identities[role] ? { role, ...identities[role] } : { role: 'headquarters', ...identities.headquarters }
|
||
next()
|
||
})
|
||
|
||
// ── 通用 SQL 片段 ───────────────────────────────────────────────────────────
|
||
const recordSelect = `
|
||
SELECT id,title,type,reporter,station,occurred_date AS date,platform,status,score,
|
||
description,review_note AS reviewNote,attachments,
|
||
datetime(created_at,'localtime') AS createdAt,
|
||
datetime(updated_at,'localtime') AS updatedAt
|
||
FROM work_records WHERE deleted_at IS NULL`
|
||
|
||
// ── 辅助函数 ────────────────────────────────────────────────────────────────
|
||
function canAccessRecord(user, record) {
|
||
if (user.role === 'headquarters') return true
|
||
if (user.role === 'station') return record.station === user.station
|
||
return record.reporter === user.name
|
||
}
|
||
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
// 公共路由
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
|
||
app.get('/api/health', (_req, res) => res.json({ status: 'ok', database: 'sqlite' }))
|
||
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
// 认证 API
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
|
||
app.post('/api/auth/login', (req, res) => {
|
||
const { code, password } = req.body
|
||
if (!code?.trim()) return res.status(400).json({ message: '请输入工号' })
|
||
|
||
// 查找人员（支持用工号或姓名登录）
|
||
const person = db.prepare(`
|
||
SELECT id, code, name, station, title, status FROM people
|
||
WHERE (code = ? OR name = ?) AND deleted_at IS NULL
|
||
`).get(code.trim(), code.trim())
|
||
|
||
if (!person) return res.status(404).json({ message: '用户不存在' })
|
||
if (person.status === 'inactive') return res.status(403).json({ message: '账号已停用，请联系管理员' })
|
||
|
||
// 根据职务推断角色
|
||
let role = 'reporter'
|
||
if (person.station === '总部' || person.title === '总部管理员') role = 'headquarters'
|
||
else if (person.title?.includes('负责人') || person.title?.includes('站长')) role = 'station'
|
||
|
||
// 生成会话 token
|
||
const token = randomBytes(32).toString('hex')
|
||
const expiresAt = new Date(Date.now() + 2 * 60 * 60 * 1000).toISOString().replace('T', ' ').slice(0, 19)
|
||
db.prepare(`INSERT INTO login_sessions (token, user_code, user_name, role, station, expires_at)
|
||
VALUES (?,?,?,?,?,?)`).run(token, person.code, person.name, role, person.station, expiresAt)
|
||
|
||
logSystem(role, person.name, 'auth', 'login', 'user', person.id, `用户登录，角色: ${role}`)
|
||
res.json({ token, role, name: person.name, station: person.station, code: person.code })
|
||
})
|
||
|
||
app.post('/api/auth/logout', (req, res) => {
|
||
if (req.authToken) {
|
||
db.prepare(`UPDATE login_sessions SET revoked_at = datetime('now','localtime') WHERE token = ?`).run(req.authToken)
|
||
logSystem(req.user.role, req.user.name, 'auth', 'logout', 'user', null, '用户退出')
|
||
}
|
||
res.json({ message: '已退出登录' })
|
||
})
|
||
|
||
app.get('/api/auth/check', (req, res) => {
|
||
if (!req.authToken) return res.json({ authenticated: false, role: req.user.role, name: req.user.name, demo: true })
|
||
res.json({ authenticated: true, role: req.user.role, name: req.user.name, station: req.user.station })
|
||
})
|
||
|
||
// 系统操作日志查询
|
||
app.get('/api/system-logs', (req, res) => {
|
||
if (req.user.role !== 'headquarters')
|
||
return res.status(403).json({ message: '仅总部管理员可查看系统日志' })
|
||
const { module, action, actor, page = '1', pageSize = '50' } = req.query
|
||
let sql = `SELECT id, actor_role AS actorRole, actor_name AS actorName, module, action,
|
||
target_type AS targetType, target_id AS targetId, detail,
|
||
datetime(created_at,'localtime') AS createdAt
|
||
FROM system_logs WHERE 1=1`
|
||
const params = []
|
||
if (module) { sql += ' AND module = ?'; params.push(module) }
|
||
if (action) { sql += ' AND action = ?'; params.push(action) }
|
||
if (actor) { sql += ' AND actor_name LIKE ?'; params.push(`%${actor}%`) }
|
||
sql += ' ORDER BY created_at DESC LIMIT ? OFFSET ?'
|
||
const limit = Number(pageSize)
|
||
const offset = (Number(page) - 1) * limit
|
||
params.push(limit, offset)
|
||
res.json(db.prepare(sql).all(...params))
|
||
})
|
||
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
// 工作记录（已有）
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
|
||
app.get('/api/records', (req, res) => {
|
||
let sql = recordSelect
|
||
const params = []
|
||
if (req.user.role === 'station') { sql += ' AND station = ?'; params.push(req.user.station) }
|
||
if (req.user.role === 'reporter') { sql += ' AND reporter = ?'; params.push(req.user.name) }
|
||
if (req.query.status) { sql += ' AND status = ?'; params.push(req.query.status) }
|
||
if (req.query.type) { sql += ' AND type = ?'; params.push(req.query.type) }
|
||
if (req.query.startDate) { sql += ' AND occurred_date >= ?'; params.push(req.query.startDate) }
|
||
if (req.query.endDate) { sql += ' AND occurred_date <= ?'; params.push(req.query.endDate) }
|
||
if (req.query.keyword) { sql += ' AND title LIKE ?'; params.push(`%${req.query.keyword}%`) }
|
||
sql += ' ORDER BY updated_at DESC'
|
||
res.json(db.prepare(sql).all(...params))
|
||
})
|
||
|
||
app.get('/api/records/:id/audit', (req, res) => {
|
||
const record = db.prepare(`${recordSelect} AND id = ?`).get(req.params.id)
|
||
if (!record || !canAccessRecord(req.user, record)) return res.status(404).json({ message: '记录不存在' })
|
||
const logs = db.prepare(`
|
||
SELECT actor_role AS actorRole, actor_name AS actorName, action,
|
||
from_status AS fromStatus, to_status AS toStatus, score, note,
|
||
created_at AS createdAt
|
||
FROM audit_logs WHERE record_id = ? ORDER BY id`).all(req.params.id)
|
||
res.json(logs)
|
||
})
|
||
|
||
app.post('/api/records', (req, res) => {
|
||
const { title, type, date, platform, description = '', attachments, isDraft } = req.body
|
||
if (!title?.trim() || !type || !date || !platform?.trim())
|
||
return res.status(400).json({ message: '请完整填写必填项' })
|
||
if (req.user.role === 'headquarters')
|
||
return res.status(403).json({ message: '总部管理员不能代替记者填报' })
|
||
const reporter = req.user.role === 'reporter' ? req.user.name : '林晓'
|
||
const station = req.user.station
|
||
const id = `WK-${date.replaceAll('-', '')}-${String(Date.now()).slice(-4)}`
|
||
const status = isDraft ? 'draft' : 'station_review'
|
||
db.prepare(`
|
||
INSERT INTO work_records (id,title,type,reporter,station,occurred_date,platform,status,description,attachments)
|
||
VALUES (?,?,?,?,?,?,?,?,?,?)`).run(id, title.trim(), type, reporter, station, date, platform.trim(), status, description, attachments ? JSON.stringify(attachments) : null)
|
||
db.prepare(`
|
||
INSERT INTO audit_logs (record_id,actor_role,actor_name,action,to_status,note)
|
||
VALUES (?,?,?,?,?,?)`).run(id, req.user.role, req.user.name, isDraft ? 'save_draft' : 'submit', status, isDraft ? '保存草稿' : '提交工作记录')
|
||
logSystem(req.user.role, req.user.name, 'record', isDraft ? 'save_draft' : 'create', 'work_record', id, `标题: ${title}`)
|
||
res.status(201).json(db.prepare(`${recordSelect} AND id = ?`).get(id))
|
||
})
|
||
|
||
// 保存草稿
|
||
app.patch('/api/records/:id/draft', (req, res) => {
|
||
const record = db.prepare(`${recordSelect} AND id = ?`).get(req.params.id)
|
||
if (!record) return res.status(404).json({ message: '记录不存在' })
|
||
if (record.status !== 'draft' && record.status !== 'returned')
|
||
return res.status(409).json({ message: '仅草稿或退回状态可编辑' })
|
||
if (record.reporter !== req.user.name && req.user.role !== 'headquarters')
|
||
return res.status(403).json({ message: '仅填报人可编辑' })
|
||
const { title, type, date, platform, description, attachments } = req.body
|
||
const updates = []
|
||
const params = []
|
||
if (title !== undefined) { updates.push('title = ?'); params.push(title.trim()) }
|
||
if (type !== undefined) { updates.push('type = ?'); params.push(type) }
|
||
if (date !== undefined) { updates.push('occurred_date = ?'); params.push(date) }
|
||
if (platform !== undefined) { updates.push('platform = ?'); params.push(platform.trim()) }
|
||
if (description !== undefined) { updates.push('description = ?'); params.push(description) }
|
||
if (attachments !== undefined) { updates.push('attachments = ?'); params.push(attachments ? JSON.stringify(attachments) : null) }
|
||
if (updates.length === 0) return res.status(400).json({ message: '无有效更新字段' })
|
||
updates.push('updated_at = CURRENT_TIMESTAMP')
|
||
params.push(req.params.id)
|
||
db.prepare(`UPDATE work_records SET ${updates.join(', ')} WHERE id = ?`).run(...params)
|
||
res.json(db.prepare(`${recordSelect} AND id = ?`).get(req.params.id))
|
||
})
|
||
|
||
// 提交草稿为审核
|
||
app.post('/api/records/:id/submit', (req, res) => {
|
||
const record = db.prepare(`${recordSelect} AND id = ?`).get(req.params.id)
|
||
if (!record) return res.status(404).json({ message: '记录不存在' })
|
||
if (record.status !== 'draft' && record.status !== 'returned')
|
||
return res.status(409).json({ message: '仅草稿或退回状态可提交' })
|
||
db.prepare(`UPDATE work_records SET status = 'station_review', updated_at = CURRENT_TIMESTAMP WHERE id = ?`).run(req.params.id)
|
||
db.prepare(`INSERT INTO audit_logs (record_id,actor_role,actor_name,action,from_status,to_status,note)
|
||
VALUES (?,?,?,?,?,?,?)`).run(record.id, req.user.role, req.user.name, 'submit', record.status, 'station_review', '提交审核')
|
||
res.json(db.prepare(`${recordSelect} AND id = ?`).get(record.id))
|
||
})
|
||
|
||
// 工作记录版本历史
|
||
app.get('/api/records/:id/versions', (req, res) => {
|
||
const record = db.prepare(`${recordSelect} AND id = ?`).get(req.params.id)
|
||
if (!record || !canAccessRecord(req.user, record)) return res.status(404).json({ message: '记录不存在' })
|
||
const versions = db.prepare(`SELECT id, version_no AS versionNo, title, type, platform, description, attachments, edited_by AS editedBy,
|
||
datetime(edited_at,'localtime') AS editedAt FROM record_versions WHERE record_id = ? ORDER BY version_no DESC`).all(req.params.id)
|
||
res.json(versions)
|
||
})
|
||
|
||
// 附件上传
|
||
app.post('/api/records/:id/attachments', (req, res) => {
|
||
const record = db.prepare(`${recordSelect} AND id = ?`).get(req.params.id)
|
||
if (!record) return res.status(404).json({ message: '记录不存在' })
|
||
if (!canAccessRecord(req.user, record)) return res.status(403).json({ message: '无权操作' })
|
||
const { files } = req.body
|
||
if (!files?.length) return res.status(400).json({ message: '请提供文件信息' })
|
||
const existing = record.attachments ? JSON.parse(record.attachments) : []
|
||
const updated = [...existing, ...files]
|
||
db.prepare(`UPDATE work_records SET attachments = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?`).run(JSON.stringify(updated), req.params.id)
|
||
logSystem(req.user.role, req.user.name, 'record', 'upload_attachment', 'work_record', req.params.id, `上传 ${files.length} 个附件`)
|
||
res.json(db.prepare(`${recordSelect} AND id = ?`).get(req.params.id))
|
||
})
|
||
|
||
// 删除附件
|
||
app.delete('/api/records/:id/attachments/:index', (req, res) => {
|
||
const record = db.prepare(`${recordSelect} AND id = ?`).get(req.params.id)
|
||
if (!record) return res.status(404).json({ message: '记录不存在' })
|
||
if (!canAccessRecord(req.user, record)) return res.status(403).json({ message: '无权操作' })
|
||
const attachments = record.attachments ? JSON.parse(record.attachments) : []
|
||
const idx = Number(req.params.index)
|
||
if (idx < 0 || idx >= attachments.length) return res.status(400).json({ message: '附件索引无效' })
|
||
attachments.splice(idx, 1)
|
||
db.prepare(`UPDATE work_records SET attachments = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?`).run(attachments.length ? JSON.stringify(attachments) : null, req.params.id)
|
||
res.json(db.prepare(`${recordSelect} AND id = ?`).get(req.params.id))
|
||
})
|
||
|
||
app.patch('/api/records/:id/review', (req, res) => {
|
||
const record = db.prepare(`${recordSelect} AND id = ?`).get(req.params.id)
|
||
if (!record || !canAccessRecord(req.user, record))
|
||
return res.status(404).json({ message: '记录不存在' })
|
||
const { decision, score, note = '' } = req.body
|
||
if (!['pass', 'return'].includes(decision))
|
||
return res.status(400).json({ message: '无效的审核决定' })
|
||
if (decision === 'return' && !note.trim())
|
||
return res.status(400).json({ message: '退回时必须填写原因' })
|
||
|
||
const expected = req.user.role === 'station'
|
||
? 'station_review' : req.user.role === 'headquarters' ? 'headquarters_review' : null
|
||
if (!expected || record.status !== expected)
|
||
return res.status(409).json({ message: '无权处理当前状态' })
|
||
|
||
const nextStatus = decision === 'return'
|
||
? 'returned'
|
||
: req.user.role === 'station' ? 'headquarters_review' : 'archived'
|
||
const finalScore = Number.isFinite(Number(score)) ? Number(score) : record.score
|
||
|
||
db.exec('BEGIN')
|
||
try {
|
||
db.prepare(`
|
||
UPDATE work_records
|
||
SET status = ?, score = ?, review_note = ?, updated_at = CURRENT_TIMESTAMP
|
||
WHERE id = ?`).run(nextStatus, finalScore, note.trim() || '材料完整，审核通过。', record.id)
|
||
db.prepare(`
|
||
INSERT INTO audit_logs (record_id,actor_role,actor_name,action,from_status,to_status,score,note)
|
||
VALUES (?,?,?,?,?,?,?,?)`)
|
||
.run(record.id, req.user.role, req.user.name, decision, record.status, nextStatus, finalScore, note)
|
||
db.exec('COMMIT')
|
||
} catch (e) {
|
||
db.exec('ROLLBACK')
|
||
res.status(500).json({ message: '服务端处理失败' })
|
||
}
|
||
res.json(db.prepare(`${recordSelect} AND id = ?`).get(record.id))
|
||
})
|
||
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
// TASK-BE-001：人员 CRUD API
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
|
||
app.get('/api/people', (req, res) => {
|
||
let sql = `SELECT id,code,name,station,title,phone,
|
||
joined_at AS joinedAt,status,
|
||
datetime(created_at,'localtime') AS createdAt,
|
||
datetime(updated_at,'localtime') AS updatedAt
|
||
FROM people WHERE deleted_at IS NULL`
|
||
const params = []
|
||
if (req.user.role === 'station') { sql += ' AND station = ?'; params.push(req.user.station) }
|
||
if (req.query.station) { sql += ' AND station = ?'; params.push(req.query.station) }
|
||
if (req.query.status) { sql += ' AND status = ?'; params.push(req.query.status) }
|
||
if (req.query.name) { sql += ' AND name LIKE ?'; params.push(`%${req.query.name}%`) }
|
||
sql += ' ORDER BY station, name'
|
||
res.json(db.prepare(sql).all(...params))
|
||
})
|
||
|
||
app.get('/api/people/:id', (req, res) => {
|
||
const person = db.prepare(`SELECT id,code,name,station,title,phone,
|
||
joined_at AS joinedAt,status,
|
||
datetime(created_at,'localtime') AS createdAt,
|
||
datetime(updated_at,'localtime') AS updatedAt
|
||
FROM people WHERE id = ? AND deleted_at IS NULL`).get(req.params.id)
|
||
if (!person) return res.status(404).json({ message: '人员不存在' })
|
||
res.json(person)
|
||
})
|
||
|
||
app.post('/api/people', (req, res) => {
|
||
if (req.user.role !== 'headquarters')
|
||
return res.status(403).json({ message: '仅总部管理员可新增人员' })
|
||
const { name, station, title, phone, joinedAt } = req.body
|
||
if (!name?.trim() || !station?.trim())
|
||
return res.status(400).json({ message: '姓名和所属站点为必填项' })
|
||
|
||
const code = `PERSON_${Date.now()}`
|
||
try {
|
||
db.prepare(`
|
||
INSERT INTO people (code,name,station,title,phone,joined_at)
|
||
VALUES (?,?,?,?,?,?)`).run(code, name.trim(), station.trim(), title?.trim() || null,
|
||
phone?.trim() || null, joinedAt || null)
|
||
const person = db.prepare(`SELECT id,code,name,station,title,phone,
|
||
joined_at AS joinedAt,status,datetime(created_at,'localtime') AS createdAt,
|
||
datetime(updated_at,'localtime') AS updatedAt FROM people WHERE code = ?`).get(code)
|
||
res.status(201).json(person)
|
||
} catch (e) {
|
||
if (e.message.includes('UNIQUE')) return res.status(409).json({ message: '人员编码已存在' })
|
||
throw e
|
||
}
|
||
})
|
||
|
||
app.patch('/api/people/:id', (req, res) => {
|
||
const person = db.prepare('SELECT * FROM people WHERE id = ?').get(req.params.id)
|
||
if (!person) return res.status(404).json({ message: '人员不存在' })
|
||
if (req.user.role === 'station' && person.station !== req.user.station)
|
||
return res.status(403).json({ message: '无权编辑其他站点人员' })
|
||
|
||
const { title, phone, status } = req.body
|
||
const updates = []
|
||
const params = []
|
||
if (title !== undefined) { updates.push('title = ?'); params.push(title.trim()) }
|
||
if (phone !== undefined) { updates.push('phone = ?'); params.push(phone?.trim() || null) }
|
||
if (status !== undefined) {
|
||
if (!['active', 'inactive'].includes(status))
|
||
return res.status(400).json({ message: 'status 只能是 active 或 inactive' })
|
||
updates.push('status = ?'); params.push(status)
|
||
}
|
||
if (updates.length === 0) return res.status(400).json({ message: '无有效更新字段' })
|
||
|
||
updates.push('updated_at = CURRENT_TIMESTAMP')
|
||
params.push(req.params.id)
|
||
db.prepare(`UPDATE people SET ${updates.join(', ')} WHERE id = ?`).run(...params)
|
||
const updated = db.prepare(`SELECT id,code,name,station,title,phone,
|
||
joined_at AS joinedAt,status,datetime(created_at,'localtime') AS createdAt,
|
||
datetime(updated_at,'localtime') AS updatedAt FROM people WHERE id = ?`).get(req.params.id)
|
||
res.json(updated)
|
||
})
|
||
|
||
app.delete('/api/people/:id', (req, res) => {
|
||
if (req.user.role !== 'headquarters')
|
||
return res.status(403).json({ message: '仅总部管理员可删除人员' })
|
||
const person = db.prepare('SELECT * FROM people WHERE id = ? AND deleted_at IS NULL').get(req.params.id)
|
||
if (!person) return res.status(404).json({ message: '人员不存在' })
|
||
db.prepare('UPDATE people SET deleted_at = datetime(\'now\',\'localtime\') WHERE id = ?').run(req.params.id)
|
||
logSystem(req.user.role, req.user.name, 'people', 'delete', 'person', req.params.id, `删除人员: ${person.name}`)
|
||
res.json({ message: '删除成功' })
|
||
})
|
||
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
// TASK-BE-002：记者站 CRUD API
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
|
||
app.get('/api/stations', (req, res) => {
|
||
let sql = `SELECT id,code,name,region,address,leader,phone,status,
|
||
established_at AS establishedAt,
|
||
datetime(created_at,'localtime') AS createdAt,
|
||
datetime(updated_at,'localtime') AS updatedAt
|
||
FROM stations WHERE deleted_at IS NULL`
|
||
const params = []
|
||
if (req.query.status) { sql += ' AND status = ?'; params.push(req.query.status) }
|
||
if (req.query.region) { sql += ' AND region = ?'; params.push(req.query.region) }
|
||
sql += ' ORDER BY region, name'
|
||
res.json(db.prepare(sql).all(...params))
|
||
})
|
||
|
||
app.get('/api/stations/:id', (req, res) => {
|
||
const station = db.prepare(`SELECT id,code,name,region,address,leader,phone,status,
|
||
established_at AS establishedAt,
|
||
datetime(created_at,'localtime') AS createdAt,
|
||
datetime(updated_at,'localtime') AS updatedAt
|
||
FROM stations WHERE id = ?`).get(req.params.id)
|
||
if (!station) return res.status(404).json({ message: '记者站不存在' })
|
||
res.json(station)
|
||
})
|
||
|
||
app.post('/api/stations', (req, res) => {
|
||
if (req.user.role !== 'headquarters')
|
||
return res.status(403).json({ message: '仅总部管理员可新增记者站' })
|
||
const { name, code, region, address, leader, phone, establishedAt } = req.body
|
||
if (!name?.trim() || !code?.trim())
|
||
return res.status(400).json({ message: '站点名称和编码为必填项' })
|
||
|
||
try {
|
||
db.prepare(`
|
||
INSERT INTO stations (code,name,region,address,leader,phone,established_at)
|
||
VALUES (?,?,?,?,?,?,?)`)
|
||
.run(code.trim(), name.trim(), region?.trim() || null, address?.trim() || null,
|
||
leader?.trim() || null, phone?.trim() || null, establishedAt || null)
|
||
const station = db.prepare(`SELECT id,code,name,region,address,leader,phone,status,
|
||
established_at AS establishedAt,datetime(created_at,'localtime') AS createdAt,
|
||
datetime(updated_at,'localtime') AS updatedAt FROM stations WHERE code = ?`).get(code.trim())
|
||
res.status(201).json(station)
|
||
} catch (e) {
|
||
if (e.message.includes('UNIQUE')) return res.status(409).json({ message: '站点编码已存在' })
|
||
throw e
|
||
}
|
||
})
|
||
|
||
app.patch('/api/stations/:id', (req, res) => {
|
||
const station = db.prepare('SELECT * FROM stations WHERE id = ?').get(req.params.id)
|
||
if (!station) return res.status(404).json({ message: '记者站不存在' })
|
||
if (req.user.role !== 'headquarters')
|
||
return res.status(403).json({ message: '仅总部管理员可编辑记者站' })
|
||
|
||
const { name, region, address, leader, phone, status, establishedAt } = req.body
|
||
const updates = []
|
||
const params = []
|
||
if (name !== undefined) { updates.push('name = ?'); params.push(name.trim()) }
|
||
if (region !== undefined) { updates.push('region = ?'); params.push(region?.trim() || null) }
|
||
if (address !== undefined) { updates.push('address = ?'); params.push(address?.trim() || null) }
|
||
if (leader !== undefined) { updates.push('leader = ?'); params.push(leader?.trim() || null) }
|
||
if (phone !== undefined) { updates.push('phone = ?'); params.push(phone?.trim() || null) }
|
||
if (status !== undefined) {
|
||
if (!['active', 'inactive'].includes(status))
|
||
return res.status(400).json({ message: 'status 只能是 active 或 inactive' })
|
||
updates.push('status = ?'); params.push(status)
|
||
}
|
||
if (establishedAt !== undefined) { updates.push('established_at = ?'); params.push(establishedAt || null) }
|
||
if (updates.length === 0) return res.status(400).json({ message: '无有效更新字段' })
|
||
|
||
updates.push('updated_at = CURRENT_TIMESTAMP')
|
||
params.push(req.params.id)
|
||
db.prepare(`UPDATE stations SET ${updates.join(', ')} WHERE id = ?`).run(...params)
|
||
const updated = db.prepare(`SELECT id,code,name,region,address,leader,phone,status,
|
||
established_at AS establishedAt,datetime(created_at,'localtime') AS createdAt,
|
||
datetime(updated_at,'localtime') AS updatedAt FROM stations WHERE id = ?`).get(req.params.id)
|
||
res.json(updated)
|
||
})
|
||
|
||
app.delete('/api/stations/:id', (req, res) => {
|
||
if (req.user.role !== 'headquarters')
|
||
return res.status(403).json({ message: '仅总部管理员可删除记者站' })
|
||
const station = db.prepare('SELECT * FROM stations WHERE id = ? AND deleted_at IS NULL').get(req.params.id)
|
||
if (!station) return res.status(404).json({ message: '记者站不存在' })
|
||
const used = db.prepare('SELECT COUNT(*) AS count FROM people WHERE station = ? AND deleted_at IS NULL').get(station.name).count
|
||
if (used > 0) return res.status(409).json({ message: `该站点下有 ${used} 名人员，请先移除后再删除` })
|
||
db.prepare('UPDATE stations SET deleted_at = datetime(\'now\',\'localtime\') WHERE id = ?').run(req.params.id)
|
||
logSystem(req.user.role, req.user.name, 'stations', 'delete', 'station', req.params.id, `删除站点: ${station.name}`)
|
||
res.json({ message: '删除成功' })
|
||
})
|
||
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
// TASK-BE-003：通知公告 API
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
|
||
app.get('/api/notices', (req, res) => {
|
||
let sql = `SELECT id,code,title,content,priority,scope,stations,roles,attachment,
|
||
published_by AS publishedBy,
|
||
datetime(published_at,'localtime') AS publishedAt,
|
||
datetime(created_at,'localtime') AS createdAt
|
||
FROM notices WHERE 1=1`
|
||
const params = []
|
||
if (req.query.priority) { sql += ' AND priority = ?'; params.push(req.query.priority) }
|
||
if (req.query.scope) { sql += ' AND scope = ?'; params.push(req.query.scope) }
|
||
sql += ' ORDER BY published_at DESC'
|
||
res.json(db.prepare(sql).all(...params))
|
||
})
|
||
|
||
app.get('/api/notices/:id', (req, res) => {
|
||
const notice = db.prepare(`SELECT id,code,title,content,priority,scope,stations,roles,attachment,
|
||
published_by AS publishedBy,
|
||
datetime(published_at,'localtime') AS publishedAt,
|
||
datetime(created_at,'localtime') AS createdAt
|
||
FROM notices WHERE id = ?`).get(req.params.id)
|
||
if (!notice) return res.status(404).json({ message: '通知不存在' })
|
||
res.json(notice)
|
||
})
|
||
|
||
app.get('/api/notices/:id/receipts', (req, res) => {
|
||
const notice = db.prepare('SELECT * FROM notices WHERE id = ?').get(req.params.id)
|
||
if (!notice) return res.status(404).json({ message: '通知不存在' })
|
||
const receipts = db.prepare(`
|
||
SELECT id,notice_id AS noticeId,receiver_name AS receiverName,receiver_role AS receiverRole,
|
||
read,confirmed,datetime(read_at,'localtime') AS readAt,
|
||
datetime(confirmed_at,'localtime') AS confirmedAt,
|
||
datetime(created_at,'localtime') AS createdAt
|
||
FROM notice_receipts WHERE notice_id = ?`).all(req.params.id)
|
||
res.json(receipts)
|
||
})
|
||
|
||
app.post('/api/notices', (req, res) => {
|
||
if (req.user.role !== 'headquarters')
|
||
return res.status(403).json({ message: '仅总部管理员可发布通知' })
|
||
const { title, content, priority, scope, stations, roles } = req.body
|
||
if (!title?.trim()) return res.status(400).json({ message: '通知标题为必填项' })
|
||
if (!['normal', 'high', 'urgent'].includes(priority || 'normal'))
|
||
return res.status(400).json({ message: 'priority 只能是 normal / high / urgent' })
|
||
if (!['all', 'station', 'role'].includes(scope || 'all'))
|
||
return res.status(400).json({ message: 'scope 只能是 all / station / role' })
|
||
|
||
const code = `NOTICE_${Date.now()}`
|
||
db.prepare(`
|
||
INSERT INTO notices (code,title,content,priority,scope,stations,roles,published_by)
|
||
VALUES (?,?,?,?,?,?,?,?)`)
|
||
.run(code, title.trim(), content?.trim() || null, priority || 'normal',
|
||
scope || 'all', stations ? JSON.stringify(stations) : null,
|
||
roles ? JSON.stringify(roles) : null, req.user.name)
|
||
|
||
// 写入回执记录
|
||
const noticeId = db.prepare('SELECT last_insert_rowid() AS id').get().id
|
||
const receivers = buildReceipts(req.user, { scope: scope || 'all', stations, roles })
|
||
if (receivers.length > 0) {
|
||
const insertReceipt = db.prepare(`
|
||
INSERT INTO notice_receipts (notice_id,receiver_name,receiver_role)
|
||
VALUES (?,?,?)`)
|
||
for (const r of receivers) insertReceipt.run(noticeId, r.name, r.role)
|
||
}
|
||
|
||
const notice = db.prepare(`SELECT id,code,title,content,priority,scope,stations,roles,attachment,
|
||
published_by AS publishedBy,
|
||
datetime(published_at,'localtime') AS publishedAt,
|
||
datetime(created_at,'localtime') AS createdAt
|
||
FROM notices WHERE id = ?`).get(noticeId)
|
||
res.status(201).json(notice)
|
||
})
|
||
|
||
app.patch('/api/notices/:id', (req, res) => {
|
||
if (req.user.role !== 'headquarters')
|
||
return res.status(403).json({ message: '仅总部管理员可编辑通知' })
|
||
const notice = db.prepare('SELECT * FROM notices WHERE id = ?').get(req.params.id)
|
||
if (!notice) return res.status(404).json({ message: '通知不存在' })
|
||
|
||
const { title, content, priority } = req.body
|
||
const updates = []
|
||
const params = []
|
||
if (title !== undefined) { updates.push('title = ?'); params.push(title.trim()) }
|
||
if (content !== undefined) { updates.push('content = ?'); params.push(content?.trim() || null) }
|
||
if (priority !== undefined) {
|
||
if (!['normal', 'high', 'urgent'].includes(priority))
|
||
return res.status(400).json({ message: 'priority 无效' })
|
||
updates.push('priority = ?'); params.push(priority)
|
||
}
|
||
if (updates.length === 0) return res.status(400).json({ message: '无有效更新字段' })
|
||
|
||
updates.push('updated_at = CURRENT_TIMESTAMP')
|
||
params.push(req.params.id)
|
||
db.prepare(`UPDATE notices SET ${updates.join(', ')} WHERE id = ?`).run(...params)
|
||
const updated = db.prepare(`SELECT id,code,title,content,priority,scope,stations,roles,attachment,
|
||
published_by AS publishedBy,
|
||
datetime(published_at,'localtime') AS publishedAt,
|
||
datetime(created_at,'localtime') AS createdAt
|
||
FROM notices WHERE id = ?`).get(req.params.id)
|
||
res.json(updated)
|
||
})
|
||
|
||
app.post('/api/notices/:id/read', (req, res) => {
|
||
const notice = db.prepare('SELECT * FROM notices WHERE id = ?').get(req.params.id)
|
||
if (!notice) return res.status(404).json({ message: '通知不存在' })
|
||
const receipt = db.prepare(`
|
||
SELECT * FROM notice_receipts
|
||
WHERE notice_id = ? AND receiver_name = ? AND receiver_role = ?`)
|
||
.get(req.params.id, req.user.name, req.user.role)
|
||
if (!receipt) return res.status(404).json({ message: '无对应回执记录' })
|
||
db.prepare(`UPDATE notice_receipts SET read = 1, read_at = CURRENT_TIMESTAMP
|
||
WHERE id = ?`).run(receipt.id)
|
||
res.json({ message: '已标记为已读' })
|
||
})
|
||
|
||
app.post('/api/notices/:id/confirm', (req, res) => {
|
||
if (req.user.role !== 'headquarters')
|
||
return res.status(403).json({ message: '仅总部管理员可确认回执' })
|
||
const notice = db.prepare('SELECT * FROM notices WHERE id = ?').get(req.params.id)
|
||
if (!notice) return res.status(404).json({ message: '通知不存在' })
|
||
const { receiverName, receiverRole } = req.body
|
||
if (!receiverName || !receiverRole)
|
||
return res.status(400).json({ message: 'receiverName 和 receiverRole 为必填项' })
|
||
const receipt = db.prepare(`
|
||
SELECT * FROM notice_receipts
|
||
WHERE notice_id = ? AND receiver_name = ? AND receiver_role = ?`)
|
||
.get(req.params.id, receiverName, receiverRole)
|
||
if (!receipt) return res.status(404).json({ message: '回执记录不存在' })
|
||
db.prepare(`UPDATE notice_receipts SET confirmed = 1, confirmed_at = CURRENT_TIMESTAMP
|
||
WHERE id = ?`).run(receipt.id)
|
||
res.json({ message: '回执已确认' })
|
||
})
|
||
|
||
/** 根据通知范围构建应接收回执的人员列表 */
|
||
function buildReceipts(user, { scope, stations, roles }) {
|
||
const receivers = []
|
||
if (scope === 'all') {
|
||
const people = db.prepare('SELECT name,station FROM people WHERE status = ?').all('active')
|
||
for (const p of people) receivers.push({ name: p.name, role: 'reporter' })
|
||
} else if (scope === 'station') {
|
||
const stationList = stations || [user.station]
|
||
for (const s of stationList) {
|
||
const people = db.prepare('SELECT name FROM people WHERE station = ? AND status = ?').all(s, 'active')
|
||
for (const p of people) receivers.push({ name: p.name, role: 'reporter' })
|
||
}
|
||
} else if (scope === 'role') {
|
||
const roleList = roles || ['reporter', 'station']
|
||
for (const r of roleList) {
|
||
const people = db.prepare('SELECT name FROM people WHERE status = ?').all('active')
|
||
for (const p of people) receivers.push({ name: p.name, role: r })
|
||
}
|
||
}
|
||
return receivers
|
||
}
|
||
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
// TASK-BE-004：数据统计 API
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
|
||
app.get('/api/stats/overview', (req, res) => {
|
||
const isHq = req.user.role === 'headquarters'
|
||
const stationFilter = isHq ? '' : ' AND station = ?'
|
||
const stationParams = isHq ? [] : [req.user.station]
|
||
|
||
const total = db.prepare(`SELECT COUNT(*) AS count FROM work_records WHERE deleted_at IS NULL${stationFilter}`).get(...stationParams).count
|
||
const draft = db.prepare(`SELECT COUNT(*) AS count FROM work_records WHERE deleted_at IS NULL${stationFilter} AND status = 'draft'`).get(...stationParams).count
|
||
const reviewing = db.prepare(`SELECT COUNT(*) AS count FROM work_records WHERE deleted_at IS NULL${stationFilter} AND status IN ('station_review','headquarters_review')`).get(...stationParams).count
|
||
const archived = db.prepare(`SELECT COUNT(*) AS count FROM work_records WHERE deleted_at IS NULL${stationFilter} AND status = 'archived'`).get(...stationParams).count
|
||
const returned = db.prepare(`SELECT COUNT(*) AS count FROM work_records WHERE deleted_at IS NULL${stationFilter} AND status = 'returned'`).get(...stationParams).count
|
||
const avgScore = db.prepare(`SELECT ROUND(AVG(score),1) AS avg FROM work_records WHERE deleted_at IS NULL${stationFilter} AND score IS NOT NULL`).get(...stationParams).avg
|
||
|
||
// 按月统计（最近 6 个月）
|
||
const monthlyRows = db.prepare(`SELECT
|
||
strftime('%Y-%m', occurred_date) AS month,
|
||
COUNT(*) AS count,
|
||
ROUND(AVG(score),1) AS avgScore
|
||
FROM work_records WHERE deleted_at IS NULL${stationFilter} AND occurred_date >= date('now','-6 months')
|
||
GROUP BY strftime('%Y-%m', occurred_date)
|
||
ORDER BY month DESC`).all(...stationParams)
|
||
|
||
res.json({ total, draft, reviewing, archived, returned, avgScore, monthly: monthlyRows })
|
||
})
|
||
|
||
app.get('/api/stats/records', (req, res) => {
|
||
const groupBy = req.query.groupBy || 'station'
|
||
const isHq = req.user.role === 'headquarters'
|
||
const stationCon = isHq ? '' : ' AND station = ?'
|
||
const stationParams = isHq ? [] : [req.user.station]
|
||
let rows
|
||
|
||
if (groupBy === 'station') {
|
||
rows = db.prepare(`SELECT
|
||
station AS name,
|
||
COUNT(*) AS total,
|
||
SUM(CASE WHEN status = 'archived' THEN 1 ELSE 0 END) AS archived,
|
||
SUM(CASE WHEN status = 'returned' THEN 1 ELSE 0 END) AS returned,
|
||
ROUND(AVG(CASE WHEN score IS NOT NULL THEN score END),1) AS avgScore
|
||
FROM work_records WHERE deleted_at IS NULL${stationCon}
|
||
GROUP BY station ORDER BY total DESC`).all(...stationParams)
|
||
} else if (groupBy === 'reporter') {
|
||
const whereCon = isHq ? '' : ' AND reporter = ?'
|
||
const whereParams = isHq ? [] : [req.user.name]
|
||
rows = db.prepare(`SELECT
|
||
reporter AS name,
|
||
station,
|
||
COUNT(*) AS total,
|
||
SUM(CASE WHEN status = 'archived' THEN 1 ELSE 0 END) AS archived,
|
||
SUM(CASE WHEN status = 'returned' THEN 1 ELSE 0 END) AS returned,
|
||
ROUND(AVG(CASE WHEN score IS NOT NULL THEN score END),1) AS avgScore
|
||
FROM work_records WHERE deleted_at IS NULL${whereCon} GROUP BY reporter, station ORDER BY total DESC`).all(...whereParams)
|
||
} else if (groupBy === 'type') {
|
||
rows = db.prepare(`SELECT
|
||
type AS name,
|
||
COUNT(*) AS total,
|
||
SUM(CASE WHEN status = 'archived' THEN 1 ELSE 0 END) AS archived,
|
||
ROUND(AVG(CASE WHEN score IS NOT NULL THEN score END),1) AS avgScore
|
||
FROM work_records WHERE deleted_at IS NULL${stationCon}
|
||
GROUP BY type ORDER BY total DESC`).all(...stationParams)
|
||
} else {
|
||
return res.status(400).json({ message: 'groupBy 必须是 station / reporter / type' })
|
||
}
|
||
|
||
res.json(rows)
|
||
})
|
||
|
||
app.get('/api/stats/scores', (req, res) => {
|
||
const isHq = req.user.role === 'headquarters'
|
||
const whereCon = isHq ? '' : ' AND station = ?'
|
||
const whereParams = isHq ? [] : [req.user.station]
|
||
const rows = db.prepare(`SELECT
|
||
reporter AS name,
|
||
station,
|
||
COUNT(*) AS submitted,
|
||
SUM(CASE WHEN status = 'archived' THEN 1 ELSE 0 END) AS archived,
|
||
SUM(CASE WHEN status = 'returned' THEN 1 ELSE 0 END) AS returned,
|
||
ROUND(SUM(score),1) AS totalScore,
|
||
ROUND(AVG(score),1) AS avgScore,
|
||
MAX(score) AS maxScore,
|
||
MIN(score) AS minScore
|
||
FROM work_records WHERE deleted_at IS NULL${whereCon} AND score IS NOT NULL
|
||
GROUP BY reporter ORDER BY totalScore DESC`).all(...whereParams)
|
||
res.json(rows)
|
||
})
|
||
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
// TASK-RULE-004：考核规则 CRUD API
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
|
||
app.get('/api/rules', (req, res) => {
|
||
const { status, period_type } = req.query
|
||
let sql = `SELECT id,code,name,description,period_type AS periodType,
|
||
period_start AS periodStart,period_end AS periodEnd,
|
||
status,version,parent_id AS parentId,
|
||
created_by AS createdBy,
|
||
datetime(created_at,'localtime') AS createdAt,
|
||
datetime(updated_at,'localtime') AS updatedAt
|
||
FROM rules WHERE 1=1`
|
||
const params = []
|
||
if (status) { sql += ' AND status = ?'; params.push(status) }
|
||
if (period_type) { sql += ' AND period_type = ?'; params.push(period_type) }
|
||
sql += ' ORDER BY created_at DESC'
|
||
res.json(db.prepare(sql).all(...params))
|
||
})
|
||
|
||
app.get('/api/rules/:id', (req, res) => {
|
||
const rule = db.prepare(`SELECT id,code,name,description,period_type AS periodType,
|
||
period_start AS periodStart,period_end AS periodEnd,
|
||
status,version,parent_id AS parentId,
|
||
created_by AS createdBy,
|
||
datetime(created_at,'localtime') AS createdAt,
|
||
datetime(updated_at,'localtime') AS updatedAt
|
||
FROM rules WHERE id = ?`).get(req.params.id)
|
||
if (!rule) return res.status(404).json({ message: '规则不存在' })
|
||
const items = db.prepare(`SELECT id,rule_id AS ruleId,category,name,metric_key AS metricKey,
|
||
weight,min_score AS minScore,max_score AS maxScore,
|
||
formula_type AS formulaType,formula_params AS formulaParams,
|
||
display_order AS displayOrder,enabled,
|
||
datetime(created_at,'localtime') AS createdAt,
|
||
datetime(updated_at,'localtime') AS updatedAt
|
||
FROM rule_items WHERE rule_id = ? ORDER BY display_order`).all(req.params.id)
|
||
res.json({ ...rule, items })
|
||
})
|
||
|
||
app.post('/api/rules', (req, res) => {
|
||
if (req.user.role !== 'headquarters')
|
||
return res.status(403).json({ message: '仅总部管理员可创建考核规则' })
|
||
const { name, description, period_type, period_start, period_end, items } = req.body
|
||
if (!name?.trim()) return res.status(400).json({ message: '规则名称为必填项' })
|
||
if (!period_type) return res.status(400).json({ message: '考核周期类型为必填项' })
|
||
|
||
const code = `RULE_${Date.now()}`
|
||
db.exec('BEGIN')
|
||
try {
|
||
db.prepare(`INSERT INTO rules (code,name,description,period_type,period_start,period_end,status,created_by)
|
||
VALUES (?,?,?,?,?,?,?,?)`)
|
||
.run(code, name.trim(), description?.trim() || null,
|
||
period_type, period_start || null, period_end || null,
|
||
'draft', req.user.name)
|
||
const { id } = db.prepare('SELECT last_insert_rowid() AS id').get()
|
||
if (items?.length) {
|
||
for (const item of items) {
|
||
db.prepare(`INSERT INTO rule_items (rule_id,category,name,metric_key,weight,min_score,max_score,formula_type,formula_params,display_order)
|
||
VALUES (?,?,?,?,?,?,?,?,?,?)`)
|
||
.run(id, item.category, item.name, item.metric_key, item.weight,
|
||
item.min_score ?? 0, item.max_score ?? 100,
|
||
item.formula_type, JSON.stringify(item.formula_params ?? {}),
|
||
item.display_order ?? 0)
|
||
}
|
||
}
|
||
db.exec('COMMIT')
|
||
const rule = db.prepare(`SELECT id,code,name,description,period_type AS periodType,
|
||
period_start AS periodStart,period_end AS periodEnd,
|
||
status,version,parent_id AS parentId,
|
||
created_by AS createdBy,
|
||
datetime(created_at,'localtime') AS createdAt,
|
||
datetime(updated_at,'localtime') AS updatedAt
|
||
FROM rules WHERE id = ?`).get(id)
|
||
const ruleItems = db.prepare(`SELECT id,rule_id AS ruleId,category,name,metric_key AS metricKey,
|
||
weight,min_score AS minScore,max_score AS maxScore,
|
||
formula_type AS formulaType,formula_params AS formulaParams,
|
||
display_order AS displayOrder,enabled,
|
||
datetime(created_at,'localtime') AS createdAt,
|
||
datetime(updated_at,'localtime') AS updatedAt
|
||
FROM rule_items WHERE rule_id = ? ORDER BY display_order`).all(id)
|
||
res.status(201).json({ ...rule, items: ruleItems })
|
||
} catch (e) {
|
||
db.exec('ROLLBACK')
|
||
res.status(500).json({ message: '服务端处理失败' })
|
||
}
|
||
})
|
||
|
||
app.patch('/api/rules/:id', (req, res) => {
|
||
if (req.user.role !== 'headquarters')
|
||
return res.status(403).json({ message: '仅总部管理员可编辑考核规则' })
|
||
const rule = db.prepare('SELECT * FROM rules WHERE id = ?').get(req.params.id)
|
||
if (!rule) return res.status(404).json({ message: '规则不存在' })
|
||
if (rule.status === 'active')
|
||
return res.status(409).json({ message: '已激活的规则不可直接编辑，请新建版本' })
|
||
|
||
const { name, description, period_start, period_end, items } = req.body
|
||
const updates = []
|
||
const params = []
|
||
if (name !== undefined) { updates.push('name = ?'); params.push(name.trim()) }
|
||
if (description !== undefined) { updates.push('description = ?'); params.push(description?.trim() || null) }
|
||
if (period_start !== undefined) { updates.push('period_start = ?'); params.push(period_start || null) }
|
||
if (period_end !== undefined) { updates.push('period_end = ?'); params.push(period_end || null) }
|
||
|
||
db.exec('BEGIN')
|
||
try {
|
||
if (updates.length > 0) {
|
||
updates.push('updated_at = CURRENT_TIMESTAMP')
|
||
params.push(req.params.id)
|
||
db.prepare(`UPDATE rules SET ${updates.join(', ')} WHERE id = ?`).run(...params)
|
||
}
|
||
if (items !== undefined) {
|
||
db.prepare('DELETE FROM rule_items WHERE rule_id = ?').run(req.params.id)
|
||
for (const item of items) {
|
||
db.prepare(`INSERT INTO rule_items (rule_id,category,name,metric_key,weight,min_score,max_score,formula_type,formula_params,display_order)
|
||
VALUES (?,?,?,?,?,?,?,?,?,?)`)
|
||
.run(req.params.id, item.category, item.name, item.metric_key, item.weight,
|
||
item.min_score ?? 0, item.max_score ?? 100,
|
||
item.formula_type, JSON.stringify(item.formula_params ?? {}),
|
||
item.display_order ?? 0)
|
||
}
|
||
}
|
||
db.exec('COMMIT')
|
||
const updated = db.prepare(`SELECT id,code,name,description,period_type AS periodType,
|
||
period_start AS periodStart,period_end AS periodEnd,
|
||
status,version,parent_id AS parentId,
|
||
created_by AS createdBy,
|
||
datetime(created_at,'localtime') AS createdAt,
|
||
datetime(updated_at,'localtime') AS updatedAt
|
||
FROM rules WHERE id = ?`).get(req.params.id)
|
||
const ruleItems = db.prepare(`SELECT id,rule_id AS ruleId,category,name,metric_key AS metricKey,
|
||
weight,min_score AS minScore,max_score AS maxScore,
|
||
formula_type AS formulaType,formula_params AS formulaParams,
|
||
display_order AS displayOrder,enabled,
|
||
datetime(created_at,'localtime') AS createdAt,
|
||
datetime(updated_at,'localtime') AS updatedAt
|
||
FROM rule_items WHERE rule_id = ? ORDER BY display_order`).all(req.params.id)
|
||
res.json({ ...updated, items: ruleItems })
|
||
} catch (e) {
|
||
db.exec('ROLLBACK')
|
||
res.status(500).json({ message: '服务端处理失败' })
|
||
}
|
||
})
|
||
|
||
app.post('/api/rules/:id/activate', (req, res) => {
|
||
if (req.user.role !== 'headquarters')
|
||
return res.status(403).json({ message: '仅总部管理员可激活考核规则' })
|
||
const rule = db.prepare('SELECT * FROM rules WHERE id = ?').get(req.params.id)
|
||
if (!rule) return res.status(404).json({ message: '规则不存在' })
|
||
if (rule.status === 'active')
|
||
return res.status(409).json({ message: '该规则已激活' })
|
||
|
||
db.exec('BEGIN')
|
||
try {
|
||
// 同一周期类型的已激活规则全部归档
|
||
db.prepare(`UPDATE rules SET status = 'archived', updated_at = CURRENT_TIMESTAMP
|
||
WHERE period_type = ? AND status = 'active'`).run(rule.period_type)
|
||
// 激活当前规则
|
||
db.prepare(`UPDATE rules SET status = 'active', updated_at = CURRENT_TIMESTAMP WHERE id = ?`)
|
||
.run(req.params.id)
|
||
db.exec('COMMIT')
|
||
const updated = db.prepare(`SELECT id,code,name,description,period_type AS periodType,
|
||
period_start AS periodStart,period_end AS periodEnd,
|
||
status,version,parent_id AS parentId,
|
||
created_by AS createdBy,
|
||
datetime(created_at,'localtime') AS createdAt,
|
||
datetime(updated_at,'localtime') AS updatedAt
|
||
FROM rules WHERE id = ?`).get(req.params.id)
|
||
res.json(updated)
|
||
} catch (e) {
|
||
db.exec('ROLLBACK')
|
||
res.status(500).json({ message: '服务端处理失败' })
|
||
}
|
||
})
|
||
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
// TASK-RULE-005：评分 API + 计算引擎
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
|
||
// 指标聚合查询：从 work_records 提取各项指标数据
|
||
function buildMetricQuery(ruleId, reporter, station, periodStart, periodEnd) {
|
||
const sql = `SELECT
|
||
COUNT(*) AS count_total,
|
||
SUM(CASE WHEN type = 'video' THEN 1 ELSE 0 END) AS count_video,
|
||
ROUND(AVG(CASE WHEN score IS NOT NULL THEN score END), 1) AS avg_score,
|
||
SUM(CASE WHEN type = 'article' THEN 1 ELSE 0 END) * 1.0 /
|
||
NULLIF(COUNT(*), 0) AS original_ratio,
|
||
SUM(CASE WHEN occurred_date <= datetime(occurred_date, '+7 days') AND status = 'archived' THEN 1 ELSE 0 END) * 1.0 /
|
||
NULLIF(COUNT(*), 0) AS on_time_rate,
|
||
SUM(CASE WHEN status = 'archived' THEN 1 ELSE 0 END) * 1.0 /
|
||
NULLIF(COUNT(*), 0) AS pass_rate
|
||
FROM work_records
|
||
WHERE reporter = ? AND station = ?
|
||
AND occurred_date >= ? AND occurred_date <= ?`
|
||
return db.prepare(sql).get(reporter, station, periodStart, periodEnd)
|
||
}
|
||
|
||
// 计算单个指标项得分
|
||
function computeItemScore(item, metricValue) {
|
||
if (metricValue === null || metricValue === undefined) return 0
|
||
const { formula_type, formula_params: rawParams } = item
|
||
const params = typeof rawParams === 'string' ? JSON.parse(rawParams) : rawParams
|
||
let score = 0
|
||
if (formula_type === 'count') {
|
||
const { threshold = 10, base_score = 60, bonus_per_item = 4, max_bonus = 20 } = params
|
||
const over = Math.max(0, metricValue - threshold)
|
||
score = Math.min(base_score + over * bonus_per_item, base_score + max_bonus)
|
||
} else if (formula_type === 'avg_score') {
|
||
score = Math.min(Math.max(metricValue, 0), 100)
|
||
} else if (formula_type === 'rate') {
|
||
const { target = 0.85, base_score = 60, bonus_per_pct = 40 } = params
|
||
const ratio = Math.min(metricValue / target, 1.5)
|
||
score = Math.min(base_score + (ratio - 1) * bonus_per_pct, 100)
|
||
} else {
|
||
score = Math.min(Math.max(metricValue, 0), 100)
|
||
}
|
||
return Math.round(score * 100) / 100
|
||
}
|
||
|
||
app.get('/api/scores', (req, res) => {
|
||
const { reporter, station, period, rule_id } = req.query
|
||
let sql = `SELECT s.id,s.code,s.rule_id AS ruleId,s.reporter,s.station,
|
||
s.period,s.period_type AS periodType,
|
||
s.total_score AS totalScore,
|
||
s.quality_score AS qualityScore,
|
||
s.quantity_score AS quantityScore,
|
||
s.efficiency_score AS efficiencyScore,
|
||
s.compliance_score AS complianceScore,
|
||
s.item_details AS itemDetails,
|
||
datetime(s.computed_at,'localtime') AS computedAt,
|
||
datetime(s.created_at,'localtime') AS createdAt,
|
||
datetime(s.updated_at,'localtime') AS updatedAt,
|
||
r.name AS ruleName
|
||
FROM scores s
|
||
LEFT JOIN rules r ON s.rule_id = r.id
|
||
WHERE 1=1`
|
||
const params = []
|
||
if (!req.user.role.includes('headquarters')) {
|
||
sql += ' AND (s.reporter = ? OR s.station = ?)'
|
||
params.push(req.user.name, req.user.station)
|
||
}
|
||
if (reporter) { sql += ' AND s.reporter = ?'; params.push(reporter) }
|
||
if (station) { sql += ' AND s.station = ?'; params.push(station) }
|
||
if (period) { sql += ' AND s.period = ?'; params.push(period) }
|
||
if (rule_id) { sql += ' AND s.rule_id = ?'; params.push(rule_id) }
|
||
sql += ' ORDER BY s.computed_at DESC'
|
||
const rows = db.prepare(sql).all(...params)
|
||
// 反序列化 item_details
|
||
for (const r of rows) {
|
||
if (r.itemDetails) { r.items = JSON.parse(r.itemDetails); delete r.itemDetails }
|
||
}
|
||
res.json(rows)
|
||
})
|
||
|
||
app.get('/api/scores/:id', (req, res) => {
|
||
const score = db.prepare(`SELECT s.id,s.code,s.rule_id AS ruleId,s.reporter,s.station,
|
||
s.period,s.period_type AS periodType,
|
||
s.total_score AS totalScore,
|
||
s.quality_score AS qualityScore,
|
||
s.quantity_score AS quantityScore,
|
||
s.efficiency_score AS efficiencyScore,
|
||
s.compliance_score AS complianceScore,
|
||
s.item_details AS itemDetails,
|
||
datetime(s.computed_at,'localtime') AS computedAt,
|
||
datetime(s.created_at,'localtime') AS createdAt,
|
||
datetime(s.updated_at,'localtime') AS updatedAt,
|
||
r.name AS ruleName
|
||
FROM scores s LEFT JOIN rules r ON s.rule_id = r.id
|
||
WHERE s.id = ?`).get(req.params.id)
|
||
if (!score) return res.status(404).json({ message: '评分记录不存在' })
|
||
if (req.user.role !== 'headquarters' &&
|
||
score.reporter !== req.user.name && score.station !== req.user.station)
|
||
return res.status(403).json({ message: '无权查看该评分记录' })
|
||
if (score.itemDetails) { score.items = JSON.parse(score.itemDetails); delete score.itemDetails }
|
||
res.json(score)
|
||
})
|
||
|
||
app.post('/api/scores/compute', (req, res) => {
|
||
if (req.user.role !== 'headquarters')
|
||
return res.status(403).json({ message: '仅总部管理员可触发评分计算' })
|
||
const { rule_id, reporters, period, period_type } = req.body
|
||
if (!rule_id) return res.status(400).json({ message: 'rule_id 为必填项' })
|
||
if (!period) return res.status(400).json({ message: 'period 为必填项' })
|
||
if (!period_type) return res.status(400).json({ message: 'period_type 为必填项' })
|
||
|
||
const rule = db.prepare('SELECT * FROM rules WHERE id = ?').get(rule_id)
|
||
if (!rule) return res.status(404).json({ message: '规则不存在' })
|
||
|
||
// 确定考核时间范围
|
||
const periodStart = rule.period_start || period
|
||
const periodEnd = rule.period_end || period
|
||
|
||
// 获取待计算记者列表
|
||
let targetReporters
|
||
if (reporters?.length) {
|
||
targetReporters = reporters
|
||
} else {
|
||
targetReporters = db.prepare(`SELECT DISTINCT reporter,station FROM work_records
|
||
WHERE occurred_date >= ? AND occurred_date <= ?
|
||
${req.user.role === 'station' ? 'AND station = ?' : ''}`)
|
||
.all(...(req.user.role === 'station' ? [periodStart, periodEnd, req.user.station] : [periodStart, periodEnd]))
|
||
.map(r => ({ reporter: r.reporter, station: r.station }))
|
||
}
|
||
|
||
if (targetReporters.length === 0)
|
||
return res.status(201).json({ message: `指定范围（${period}）内无记者数据`, results: [] })
|
||
|
||
const items = db.prepare(`SELECT * FROM rule_items WHERE rule_id = ? AND enabled = 1 ORDER BY display_order`)
|
||
.all(rule_id)
|
||
|
||
// 按维度分组权重
|
||
const catWeights = {}
|
||
for (const it of items) {
|
||
catWeights[it.category] = (catWeights[it.category] || 0) + it.weight
|
||
}
|
||
|
||
const results = []
|
||
db.exec('BEGIN')
|
||
try {
|
||
for (const { reporter, station } of targetReporters) {
|
||
const metrics = buildMetricQuery(rule_id, reporter, station, periodStart, periodEnd)
|
||
let totalScore = 0
|
||
const itemDetails = []
|
||
const catScores = { quantity: 0, quality: 0, efficiency: 0, compliance: 0 }
|
||
|
||
for (const item of items) {
|
||
const metricValue = metrics[item.metric_key] ?? null
|
||
const raw = computeItemScore(item, metricValue)
|
||
const weighted = raw * item.weight
|
||
totalScore += weighted
|
||
catScores[item.category] = (catScores[item.category] || 0) + weighted
|
||
itemDetails.push({
|
||
item_id: item.id,
|
||
category: item.category,
|
||
name: item.name,
|
||
metric_key: item.metric_key,
|
||
metric_value: metricValue,
|
||
raw_score: raw,
|
||
weight: item.weight,
|
||
weighted_score: Math.round(weighted * 100) / 100,
|
||
})
|
||
}
|
||
|
||
const code = `SCORE_${Date.now()}_${Math.random().toString(36).slice(2, 6)}`
|
||
db.prepare(`INSERT OR REPLACE INTO scores
|
||
(code,rule_id,reporter,station,period,period_type,total_score,
|
||
quality_score,quantity_score,efficiency_score,compliance_score,item_details,computed_at)
|
||
VALUES (?,?,?,?,?,?,?,?,?,?,?,?,CURRENT_TIMESTAMP)`)
|
||
.run(code, rule_id, reporter, station, period, period_type,
|
||
Math.round(totalScore * 100) / 100,
|
||
Math.round((catScores.quality || 0) * 100) / 100,
|
||
Math.round((catScores.quantity || 0) * 100) / 100,
|
||
Math.round((catScores.efficiency || 0) * 100) / 100,
|
||
Math.round((catScores.compliance || 0) * 100) / 100,
|
||
JSON.stringify(itemDetails))
|
||
results.push({ reporter, station, totalScore: Math.round(totalScore * 100) / 100 })
|
||
}
|
||
db.exec('COMMIT')
|
||
res.status(201).json({ message: `已为 ${results.length} 名记者计算评分`, results })
|
||
} catch (e) {
|
||
db.exec('ROLLBACK')
|
||
res.status(500).json({ message: '服务端处理失败' })
|
||
}
|
||
})
|
||
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
// 积分排行榜
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
|
||
app.get('/api/leaderboard', (req, res) => {
|
||
const { period, limit = '50', group_by = 'reporter' } = req.query
|
||
if (!['reporter', 'station'].includes(group_by))
|
||
return res.status(400).json({ message: 'group_by 必须是 reporter 或 station' })
|
||
|
||
// 先找最新评分记录对应的规则 ID
|
||
const ruleRow = db.prepare(
|
||
'SELECT DISTINCT rule_id FROM scores WHERE period = ? ORDER BY computed_at DESC LIMIT 1'
|
||
).get(period || '2026-Q3')
|
||
if (!ruleRow) return res.json({ period, groupBy: group_by, ranks: [] })
|
||
|
||
const ruleId = ruleRow.rule_id
|
||
|
||
if (group_by === 'reporter') {
|
||
// 记者排名：取每位记者 latest score，关联 station 名称
|
||
const rows = db.prepare(`
|
||
SELECT s.reporter AS name, s.station,
|
||
MAX(s.total_score) AS totalScore,
|
||
MAX(s.computed_at) AS latestAt
|
||
FROM scores s
|
||
WHERE s.period = ? AND s.rule_id = ? AND s.reporter IS NOT NULL
|
||
GROUP BY s.reporter, s.station
|
||
ORDER BY totalScore DESC, latestAt ASC
|
||
LIMIT ?`
|
||
).all(period || '2026-Q3', ruleId, Number(limit))
|
||
|
||
// 标注当前登录人是否在榜上
|
||
const me = rows.findIndex(r => r.name === req.user.name)
|
||
const ranked = rows.map((r, i) => ({ rank: i + 1, ...r }))
|
||
res.json({ period: period || '2026-Q3', groupBy: 'reporter', myRank: me >= 0 ? me + 1 : null, ranks: ranked })
|
||
} else {
|
||
// 记者站排名：取每站平均总分
|
||
const rows = db.prepare(`
|
||
SELECT s.station AS name,
|
||
ROUND(AVG(s.total_score), 2) AS avgScore,
|
||
COUNT(DISTINCT s.reporter) AS reporterCount,
|
||
ROUND(AVG(s.quality_score),2) AS avgQuality,
|
||
ROUND(AVG(s.quantity_score),2) AS avgQuantity,
|
||
ROUND(AVG(s.efficiency_score),2) AS avgEfficiency,
|
||
ROUND(AVG(s.compliance_score),2) AS avgCompliance
|
||
FROM scores s
|
||
WHERE s.period = ? AND s.rule_id = ? AND s.station IS NOT NULL
|
||
GROUP BY s.station
|
||
ORDER BY avgScore DESC
|
||
LIMIT ?`
|
||
).all(period || '2026-Q3', ruleId, Number(limit))
|
||
|
||
const me = rows.findIndex(r => r.name === req.user.station)
|
||
const ranked = rows.map((r, i) => ({ rank: i + 1, ...r }))
|
||
res.json({ period: period || '2026-Q3', groupBy: 'station', myRank: me >= 0 ? me + 1 : null, ranks: ranked })
|
||
}
|
||
})
|
||
|
||
app.get('/api/me/summary', (req, res) => {
|
||
// 当前登录人的考核汇总：最新周期得分 + 排名
|
||
const period = req.query.period || '2026-Q3'
|
||
const role = req.user.role
|
||
|
||
if (role === 'headquarters') {
|
||
return res.json({ period, score: null, rank: null, total: 0, message: '总部管理员无个人考核数据' })
|
||
}
|
||
|
||
const meField = role === 'station' ? 'station' : 'reporter'
|
||
const meValue = role === 'station' ? req.user.station : req.user.name
|
||
|
||
const myScore = db.prepare(`
|
||
SELECT s.total_score AS totalScore, s.quality_score, s.quantity_score,
|
||
s.efficiency_score, s.compliance_score, s.period, s.computed_at AS computedAt,
|
||
r.name AS ruleName
|
||
FROM scores s LEFT JOIN rules r ON s.rule_id = r.id
|
||
WHERE s.${meField} = ? AND s.period = ?
|
||
ORDER BY s.computed_at DESC LIMIT 1`
|
||
).get(meValue, period)
|
||
|
||
if (!myScore) return res.json({ period, score: null, rank: null, total: 0 })
|
||
|
||
const total = db.prepare(`
|
||
SELECT COUNT(*) AS cnt FROM (
|
||
SELECT DISTINCT reporter FROM scores WHERE period = ? AND rule_id = ?
|
||
)`
|
||
).get(period, myScore.ruleName ? db.prepare('SELECT id FROM rules WHERE name = ?').get(myScore.ruleName)?.id : 0)
|
||
|
||
const rank = db.prepare(`
|
||
SELECT COUNT(*) + 1 AS rank FROM scores
|
||
WHERE period = ? AND rule_id = ?
|
||
AND (total_score > ? OR (total_score = ? AND computed_at < ?))`
|
||
).get(period, myScore.ruleName ? db.prepare('SELECT id FROM rules WHERE name = ?').get(myScore.ruleName)?.id : 0,
|
||
myScore.totalScore, myScore.totalScore, myScore.computedAt)
|
||
|
||
res.json({
|
||
period,
|
||
score: myScore,
|
||
rank: rank?.rank ?? null,
|
||
total: total?.cnt ?? 0,
|
||
})
|
||
})
|
||
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
// 通知删除 + 撤回
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
|
||
app.delete('/api/notices/:id', (req, res) => {
|
||
if (req.user.role !== 'headquarters')
|
||
return res.status(403).json({ message: '仅总部管理员可删除通知' })
|
||
const notice = db.prepare('SELECT * FROM notices WHERE id = ?').get(req.params.id)
|
||
if (!notice) return res.status(404).json({ message: '通知不存在' })
|
||
db.exec('BEGIN')
|
||
try {
|
||
db.prepare('DELETE FROM notice_receipts WHERE notice_id = ?').run(req.params.id)
|
||
db.prepare('DELETE FROM notices WHERE id = ?').run(req.params.id)
|
||
db.exec('COMMIT')
|
||
logSystem(req.user.role, req.user.name, 'notices', 'delete', 'notice', req.params.id, `删除通知: ${notice.title}`)
|
||
res.json({ message: '删除成功' })
|
||
} catch (e) {
|
||
db.exec('ROLLBACK')
|
||
res.status(500).json({ message: '删除失败' })
|
||
}
|
||
})
|
||
|
||
// 撤回通知
|
||
app.post('/api/notices/:id/withdraw', (req, res) => {
|
||
if (req.user.role !== 'headquarters')
|
||
return res.status(403).json({ message: '仅总部管理员可撤回通知' })
|
||
const notice = db.prepare('SELECT * FROM notices WHERE id = ?').get(req.params.id)
|
||
if (!notice) return res.status(404).json({ message: '通知不存在' })
|
||
if (notice.status === 'withdrawn')
|
||
return res.status(409).json({ message: '该通知已撤回' })
|
||
db.prepare(`UPDATE notices SET status = 'withdrawn', withdrawn_at = datetime('now','localtime'), updated_at = datetime('now','localtime') WHERE id = ?`).run(req.params.id)
|
||
logSystem(req.user.role, req.user.name, 'notices', 'withdraw', 'notice', req.params.id, `撤回通知: ${notice.title}`)
|
||
res.json({ message: '通知已撤回' })
|
||
})
|
||
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
// 申诉复议 API
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
|
||
app.get('/api/appeals', (req, res) => {
|
||
let sql = `SELECT id,code,record_id AS recordId,appellant,station,reason,status,
|
||
handler,handler_role AS handlerRole,response,
|
||
datetime(created_at,'localtime') AS createdAt,
|
||
datetime(updated_at,'localtime') AS updatedAt,
|
||
datetime(handled_at,'localtime') AS handledAt
|
||
FROM appeals WHERE 1=1`
|
||
const params = []
|
||
if (req.user.role === 'reporter') { sql += ' AND appellant = ?'; params.push(req.user.name) }
|
||
if (req.user.role === 'station') { sql += ' AND station = ?'; params.push(req.user.station) }
|
||
if (req.query.status) { sql += ' AND status = ?'; params.push(req.query.status) }
|
||
sql += ' ORDER BY created_at DESC'
|
||
res.json(db.prepare(sql).all(...params))
|
||
})
|
||
|
||
app.post('/api/appeals', (req, res) => {
|
||
const { recordId, reason } = req.body
|
||
if (!recordId || !reason?.trim())
|
||
return res.status(400).json({ message: '记录ID和申诉原因为必填项' })
|
||
const record = db.prepare(`${recordSelect} AND id = ?`).get(recordId)
|
||
if (!record) return res.status(404).json({ message: '工作记录不存在' })
|
||
if (record.reporter !== req.user.name && req.user.role !== 'headquarters')
|
||
return res.status(403).json({ message: '仅填报人可申诉' })
|
||
if (record.status !== 'archived' && record.status !== 'returned')
|
||
return res.status(409).json({ message: '仅已归档或已退回的记录可申诉' })
|
||
|
||
const code = `APPEAL_${Date.now()}`
|
||
db.prepare(`INSERT INTO appeals (code,record_id,appellant,station,reason,status)
|
||
VALUES (?,?,?,?,?,?)`).run(code, recordId, req.user.name, record.station, reason.trim(), 'pending')
|
||
logSystem(req.user.role, req.user.name, 'appeals', 'create', 'appeal', code, `申诉记录: ${recordId}`)
|
||
res.status(201).json({ message: '申诉已提交', code })
|
||
})
|
||
|
||
app.patch('/api/appeals/:id', (req, res) => {
|
||
if (req.user.role === 'reporter')
|
||
return res.status(403).json({ message: '记者无权处理申诉' })
|
||
const appeal = db.prepare('SELECT * FROM appeals WHERE id = ?').get(req.params.id)
|
||
if (!appeal) return res.status(404).json({ message: '申诉不存在' })
|
||
if (appeal.status !== 'pending')
|
||
return res.status(409).json({ message: '该申诉已处理' })
|
||
const { decision, response } = req.body
|
||
if (!['uphold', 'overturn'].includes(decision))
|
||
return res.status(400).json({ message: 'decision 必须是 uphold 或 overturn' })
|
||
|
||
const status = decision === 'uphold' ? 'rejected' : 'accepted'
|
||
db.prepare(`UPDATE appeals SET status = ?, handler = ?, handler_role = ?, response = ?, handled_at = datetime('now','localtime'), updated_at = datetime('now','localtime') WHERE id = ?`)
|
||
.run(status, req.user.name, req.user.role, response?.trim() || null, req.params.id)
|
||
|
||
// 如果复议成功，退回记录到填报人修改
|
||
if (decision === 'overturn') {
|
||
db.prepare(`UPDATE work_records SET status = 'returned', updated_at = CURRENT_TIMESTAMP WHERE id = ?`).run(appeal.record_id)
|
||
}
|
||
|
||
logSystem(req.user.role, req.user.name, 'appeals', decision, 'appeal', req.params.id, `处理申诉: ${appeal.code}`)
|
||
res.json({ message: decision === 'uphold' ? '申诉已驳回' : '申诉已通过，记录已退回' })
|
||
})
|
||
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
// 人员调站
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
|
||
app.post('/api/people/:id/transfer', (req, res) => {
|
||
if (req.user.role !== 'headquarters')
|
||
return res.status(403).json({ message: '仅总部管理员可调站' })
|
||
const person = db.prepare('SELECT * FROM people WHERE id = ? AND deleted_at IS NULL').get(req.params.id)
|
||
if (!person) return res.status(404).json({ message: '人员不存在' })
|
||
const { toStation, reason } = req.body
|
||
if (!toStation?.trim()) return res.status(400).json({ message: '目标站点为必填项' })
|
||
|
||
const fromStation = person.station
|
||
db.exec('BEGIN')
|
||
try {
|
||
db.prepare('UPDATE people SET station = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?').run(toStation.trim(), req.params.id)
|
||
db.prepare(`INSERT INTO person_transfers (person_id,from_station,to_station,reason,operated_by)
|
||
VALUES (?,?,?,?,?)`).run(req.params.id, fromStation, toStation.trim(), reason?.trim() || null, req.user.name)
|
||
db.exec('COMMIT')
|
||
logSystem(req.user.role, req.user.name, 'people', 'transfer', 'person', req.params.id, `${person.name}: ${fromStation} -> ${toStation}`)
|
||
res.json({ message: `已将 ${person.name} 从 ${fromStation} 调至 ${toStation}` })
|
||
} catch (e) {
|
||
db.exec('ROLLBACK')
|
||
res.status(500).json({ message: '调站失败' })
|
||
}
|
||
})
|
||
|
||
app.get('/api/people/:id/transfers', (req, res) => {
|
||
const transfers = db.prepare(`SELECT id,from_station AS fromStation,to_station AS toStation,reason,operated_by AS operatedBy,
|
||
datetime(transferred_at,'localtime') AS transferredAt
|
||
FROM person_transfers WHERE person_id = ? ORDER BY transferred_at DESC`).all(req.params.id)
|
||
res.json(transfers)
|
||
})
|
||
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
// 导出 API（CSV 格式）
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
|
||
function csvEscape(value) {
|
||
if (value === null || value === undefined) return ''
|
||
const str = String(value)
|
||
if (str.includes(',') || str.includes('"') || str.includes('\n')) {
|
||
return `"${str.replace(/"/g, '""')}"`
|
||
}
|
||
return str
|
||
}
|
||
|
||
app.get('/api/export/records', (req, res) => {
|
||
let sql = recordSelect
|
||
const params = []
|
||
if (req.user.role === 'station') { sql += ' AND station = ?'; params.push(req.user.station) }
|
||
if (req.user.role === 'reporter') { sql += ' AND reporter = ?'; params.push(req.user.name) }
|
||
if (req.query.startDate) { sql += ' AND occurred_date >= ?'; params.push(req.query.startDate) }
|
||
if (req.query.endDate) { sql += ' AND occurred_date <= ?'; params.push(req.query.endDate) }
|
||
if (req.query.type) { sql += ' AND type = ?'; params.push(req.query.type) }
|
||
if (req.query.status) { sql += ' AND status = ?'; params.push(req.query.status) }
|
||
sql += ' ORDER BY updated_at DESC'
|
||
const rows = db.prepare(sql).all(...params)
|
||
const headers = ['ID', '标题', '类型', '填报人', '站点', '日期', '平台', '状态', '得分', '说明']
|
||
const csv = [headers.join(',')]
|
||
for (const r of rows) {
|
||
csv.push([r.id, r.title, r.type, r.reporter, r.station, r.date, r.platform, r.status, r.score ?? '', r.description ?? ''].map(csvEscape).join(','))
|
||
}
|
||
logSystem(req.user.role, req.user.name, 'export', 'records', 'work_records', null, `导出 ${rows.length} 条记录`)
|
||
res.setHeader('Content-Type', 'text/csv; charset=utf-8')
|
||
res.setHeader('Content-Disposition', `attachment; filename="work_records_${Date.now()}.csv"`)
|
||
res.send('\ufeff' + csv.join('\n'))
|
||
})
|
||
|
||
app.get('/api/export/people', (req, res) => {
|
||
if (req.user.role === 'reporter')
|
||
return res.status(403).json({ message: '无权导出人员数据' })
|
||
let sql = `SELECT id,code,name,station,title,phone,joined_at AS joinedAt,status FROM people WHERE deleted_at IS NULL`
|
||
const params = []
|
||
if (req.user.role === 'station') { sql += ' AND station = ?'; params.push(req.user.station) }
|
||
sql += ' ORDER BY station, name'
|
||
const rows = db.prepare(sql).all(...params)
|
||
const headers = ['ID', '工号', '姓名', '站点', '职务', '手机号', '入站时间', '状态']
|
||
const csv = [headers.join(',')]
|
||
for (const r of rows) {
|
||
csv.push([r.id, r.code, r.name, r.station, r.title ?? '', r.phone ?? '', r.joinedAt ?? '', r.status].map(csvEscape).join(','))
|
||
}
|
||
logSystem(req.user.role, req.user.name, 'export', 'people', 'people', null, `导出 ${rows.length} 条人员`)
|
||
res.setHeader('Content-Type', 'text/csv; charset=utf-8')
|
||
res.setHeader('Content-Disposition', `attachment; filename="people_${Date.now()}.csv"`)
|
||
res.send('\ufeff' + csv.join('\n'))
|
||
})
|
||
|
||
app.get('/api/export/scores', (req, res) => {
|
||
const { period } = req.query
|
||
let sql = `SELECT s.reporter,s.station,s.period,s.total_score AS totalScore,
|
||
s.quality_score AS qualityScore,s.quantity_score AS quantityScore,
|
||
s.efficiency_score AS efficiencyScore,s.compliance_score AS complianceScore,
|
||
r.name AS ruleName
|
||
FROM scores s LEFT JOIN rules r ON s.rule_id = r.id WHERE 1=1`
|
||
const params = []
|
||
if (!req.user.role.includes('headquarters')) {
|
||
sql += ' AND (s.reporter = ? OR s.station = ?)'
|
||
params.push(req.user.name, req.user.station)
|
||
}
|
||
if (period) { sql += ' AND s.period = ?'; params.push(period) }
|
||
sql += ' ORDER BY s.total_score DESC'
|
||
const rows = db.prepare(sql).all(...params)
|
||
const headers = ['记者', '站点', '周期', '总分', '质量得分', '数量得分', '效率得分', '合规得分', '规则']
|
||
const csv = [headers.join(',')]
|
||
for (const r of rows) {
|
||
csv.push([r.reporter, r.station, r.period, r.totalScore ?? '', r.qualityScore ?? '', r.quantityScore ?? '', r.efficiencyScore ?? '', r.complianceScore ?? '', r.ruleName ?? ''].map(csvEscape).join(','))
|
||
}
|
||
logSystem(req.user.role, req.user.name, 'export', 'scores', 'scores', null, `导出 ${rows.length} 条评分`)
|
||
res.setHeader('Content-Type', 'text/csv; charset=utf-8')
|
||
res.setHeader('Content-Disposition', `attachment; filename="scores_${Date.now()}.csv"`)
|
||
res.send('\ufeff' + csv.join('\n'))
|
||
})
|
||
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
// 错误处理
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
|
||
app.use((err, _req, res, _next) => {
|
||
console.error(err)
|
||
res.status(500).json({ message: '服务端处理失败' })
|
||
})
|
||
|
||
// ── 启动 ──────────────────────────────────────────────────────────────────
|
||
const port = Number(process.env.API_PORT || 8787)
|
||
app.listen(port, '127.0.0.1', () => console.log(`API ready at http://127.0.0.1:${port}`)) |