1045 lines
38 KiB
JavaScript
1045 lines
38 KiB
JavaScript
const path = require('path');
|
||
const express = require('express');
|
||
const cors = require('cors');
|
||
const bcrypt = require('bcryptjs');
|
||
const jwt = require('jsonwebtoken');
|
||
const sqlite3 = require('sqlite3').verbose();
|
||
|
||
const PORT = process.env.PORT || 3000;
|
||
const JWT_SECRET = process.env.JWT_SECRET || 'CHANGE_ME_TO_A_RANDOM_SECRET';
|
||
const DB_PATH = process.env.DB_PATH || path.join(__dirname, 'data.sqlite');
|
||
|
||
const app = express();
|
||
app.use(cors());
|
||
app.use(express.json());
|
||
|
||
// ===== DB =====
|
||
const db = new sqlite3.Database(DB_PATH);
|
||
|
||
function run(sql, params = []) {
|
||
return new Promise((resolve, reject) => {
|
||
db.run(sql, params, function (err) {
|
||
if (err) return reject(err);
|
||
resolve({ lastID: this.lastID, changes: this.changes });
|
||
});
|
||
});
|
||
}
|
||
|
||
function get(sql, params = []) {
|
||
return new Promise((resolve, reject) => {
|
||
db.get(sql, params, (err, row) => {
|
||
if (err) return reject(err);
|
||
resolve(row);
|
||
});
|
||
});
|
||
}
|
||
|
||
function all(sql, params = []) {
|
||
return new Promise((resolve, reject) => {
|
||
db.all(sql, params, (err, rows) => {
|
||
if (err) return reject(err);
|
||
resolve(rows);
|
||
});
|
||
});
|
||
}
|
||
|
||
async function initDb() {
|
||
await run('PRAGMA foreign_keys = ON;');
|
||
|
||
await run(`
|
||
CREATE TABLE IF NOT EXISTS users (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
username TEXT UNIQUE NOT NULL,
|
||
name TEXT,
|
||
password_hash TEXT NOT NULL,
|
||
role TEXT NOT NULL CHECK(role IN ('admin','leader','staff')),
|
||
phone TEXT,
|
||
email TEXT,
|
||
status TEXT NOT NULL DEFAULT 'active',
|
||
creator_id INTEGER,
|
||
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
||
FOREIGN KEY(creator_id) REFERENCES users(id)
|
||
);
|
||
`);
|
||
|
||
await run(`
|
||
CREATE TABLE IF NOT EXISTS clients (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
staff_id INTEGER NOT NULL,
|
||
regDate TEXT, -- YYYY-MM-DD
|
||
customerName TEXT NOT NULL,
|
||
phone TEXT,
|
||
expireDate TEXT, -- YYYY-MM-DD
|
||
amount REAL NOT NULL DEFAULT 0,
|
||
serviceType TEXT,
|
||
remark TEXT,
|
||
lastRenewAt TEXT,
|
||
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
||
updated_at TEXT NOT NULL DEFAULT (datetime('now')),
|
||
FOREIGN KEY(staff_id) REFERENCES users(id)
|
||
);
|
||
`);
|
||
|
||
const admin = await get('SELECT * FROM users WHERE username = ?', ['admin']);
|
||
if (!admin) {
|
||
const hash = await bcrypt.hash('admin123456', 10);
|
||
await run(
|
||
'INSERT INTO users(username, name, password_hash, role, status) VALUES(?,?,?,?,?)',
|
||
['admin', '老板', hash, 'admin', 'active']
|
||
);
|
||
console.log('已创建默认管理员账号：xs / xs123456...');
|
||
}
|
||
|
||
const staff = await get('SELECT * FROM users WHERE username = ?', ['staff1']);
|
||
if (!staff) {
|
||
const hash = await bcrypt.hash('staff123456', 10);
|
||
await run(
|
||
'INSERT INTO users(username, name, password_hash, role, status) VALUES(?,?,?,?,?)',
|
||
['staff1', '员工1', hash, 'staff', 'active']
|
||
);
|
||
console.log('已创建默认员工账号：staff1 / staff123456');
|
||
}
|
||
|
||
// 创建默认组长账号
|
||
const leader = await get('SELECT * FROM users WHERE username = ?', ['leader1']);
|
||
if (!leader) {
|
||
const hash = await bcrypt.hash('leader123456', 10);
|
||
await run(
|
||
'INSERT INTO users(username, name, password_hash, role, status) VALUES(?,?,?,?,?)',
|
||
['leader1', '组长1', hash, 'leader', 'active']
|
||
);
|
||
console.log('已创建默认组长账号：leader1 / leader123456');
|
||
}
|
||
}
|
||
|
||
function asyncHandler(fn) {
|
||
return (req, res, next) => Promise.resolve(fn(req, res, next)).catch(next);
|
||
}
|
||
|
||
// ===== Auth middleware =====
|
||
function authRequired(req, res, next) {
|
||
const auth = req.headers.authorization || '';
|
||
const token = auth.startsWith('Bearer ') ? auth.slice(7) : '';
|
||
if (!token) return res.status(401).json({ message: '未登录' });
|
||
try {
|
||
const payload = jwt.verify(token, JWT_SECRET);
|
||
req.user = payload; // {id, username, role, name}
|
||
next();
|
||
} catch (e) {
|
||
return res.status(401).json({ message: '登录已失效，请重新登录' });
|
||
}
|
||
}
|
||
|
||
function roleRequired(roles) {
|
||
return (req, res, next) => {
|
||
if (!req.user) return res.status(401).json({ message: '未登录' });
|
||
if (!roles.includes(req.user.role)) return res.status(403).json({ message: '无权限' });
|
||
next();
|
||
};
|
||
}
|
||
|
||
// ===== date helpers =====
|
||
function parseDateValue(value) {
|
||
if (!value) return null;
|
||
const d = new Date(value);
|
||
if (Number.isNaN(d.getTime())) return null;
|
||
return d;
|
||
}
|
||
function isExpired(expireDate) {
|
||
const d = parseDateValue(expireDate);
|
||
if (!d) return false;
|
||
return d.getTime() < Date.now();
|
||
}
|
||
function isExpiringSoon(expireDate, days = 7) {
|
||
const d = parseDateValue(expireDate);
|
||
if (!d) return false;
|
||
const diff = d.getTime() - Date.now();
|
||
return diff >= 0 && diff <= days * 24 * 60 * 60 * 1000;
|
||
}
|
||
|
||
// ===== Common: build client status (for response) =====
|
||
function getClientStatus(expireDate, days = 7) {
|
||
if (isExpired(expireDate)) return 'expired';
|
||
if (isExpiringSoon(expireDate, days)) return 'expiring';
|
||
return 'active';
|
||
}
|
||
|
||
// ===== API: Auth =====
|
||
app.post('/api/auth/login', asyncHandler(async (req, res) => {
|
||
const { username, password } = req.body || {};
|
||
if (!username || !password) return res.status(400).json({ message: '缺少账号或密码' });
|
||
|
||
const user = await get('SELECT * FROM users WHERE username = ?', [username]);
|
||
if (!user) return res.status(401).json({ message: '账号或密码错误' });
|
||
|
||
if (user.status === 'inactive') return res.status(403).json({ message: '该账号已被禁用，请联系管理员' });
|
||
|
||
const ok = await bcrypt.compare(password, user.password_hash);
|
||
if (!ok) return res.status(401).json({ message: '账号或密码错误' });
|
||
|
||
const token = jwt.sign(
|
||
{ id: user.id, username: user.username, role: user.role, name: user.name },
|
||
JWT_SECRET,
|
||
{ expiresIn: '7d' }
|
||
);
|
||
|
||
res.json({
|
||
token,
|
||
user: { id: user.id, username: user.username, name: user.name, role: user.role }
|
||
});
|
||
}));
|
||
|
||
app.get('/api/auth/me', authRequired, asyncHandler(async (req, res) => {
|
||
const u = await get(
|
||
'SELECT id, username, name, role, phone, email, status FROM users WHERE id=?',
|
||
[req.user.id]
|
||
);
|
||
res.json({ user: u });
|
||
}));
|
||
|
||
// ======================================================================
|
||
// ✅ 新增：Dashboard Summary（总览卡片 + 员工统计）
|
||
// - admin：返回全局 stats + staffStats 列表
|
||
// - staff：返回自己的 stats（不返回全部员工）
|
||
// ======================================================================
|
||
app.get('/api/dashboard/summary', authRequired, asyncHandler(async (req, res) => {
|
||
const days = Number(req.query.days || 7);
|
||
|
||
// 取所有客户（admin） or 取自己的客户（staff）
|
||
let rows;
|
||
if (req.user.role === 'admin') {
|
||
rows = await all(`SELECT staff_id, expireDate, amount FROM clients`);
|
||
} else {
|
||
rows = await all(`SELECT staff_id, expireDate, amount FROM clients WHERE staff_id = ?`, [req.user.id]);
|
||
}
|
||
|
||
let totalAmount = 0;
|
||
let totalClients = rows.length;
|
||
let expiringCount = 0;
|
||
let expiredCount = 0;
|
||
|
||
for (const r of rows) {
|
||
totalAmount += Number(r.amount || 0);
|
||
if (isExpired(r.expireDate)) expiredCount += 1;
|
||
else if (isExpiringSoon(r.expireDate, days)) expiringCount += 1;
|
||
}
|
||
|
||
const stats = { totalAmount, totalClients, expiringCount, expiredCount };
|
||
|
||
if (req.user.role !== 'admin') {
|
||
return res.json({ stats });
|
||
}
|
||
|
||
// admin：补员工统计
|
||
const staffRows = await all(`
|
||
SELECT u.id, u.username, u.name, u.phone, u.email, u.status, u.created_at, u.creator_id, c.name as creator_name, c.username as creator_username
|
||
FROM users u
|
||
LEFT JOIN users c ON u.creator_id = c.id
|
||
WHERE u.role='staff'
|
||
ORDER BY u.id ASC
|
||
`);
|
||
|
||
const map = new Map();
|
||
staffRows.forEach(s => {
|
||
map.set(s.id, {
|
||
id: s.id,
|
||
username: s.username,
|
||
name: s.name,
|
||
phone: s.phone,
|
||
email: s.email,
|
||
status: s.status,
|
||
created_at: s.created_at,
|
||
creator_id: s.creator_id,
|
||
creator_name: s.creator_name || s.creator_username || 'Admin',
|
||
clients: 0,
|
||
total: 0,
|
||
expired: 0,
|
||
expiring: 0
|
||
});
|
||
});
|
||
|
||
rows.forEach(c => {
|
||
const t = map.get(c.staff_id);
|
||
if (!t) return;
|
||
t.clients += 1;
|
||
t.total += Number(c.amount || 0);
|
||
if (isExpired(c.expireDate)) t.expired += 1;
|
||
else if (isExpiringSoon(c.expireDate, days)) t.expiring += 1;
|
||
});
|
||
|
||
res.json({ stats, staffStats: Array.from(map.values()) });
|
||
}));
|
||
|
||
// ======================================================================
|
||
// ✅ 新增：Staff 通用接口（更贴近前端）
|
||
// - GET /api/staff admin:员工列表（含聚合） leader:员工列表（不含聚合） staff:返回自己
|
||
// - POST /api/staff admin/leader:新增员工
|
||
// - PUT /api/staff/:id admin/leader:编辑员工
|
||
// - DELETE /api/staff/:id admin/leader:禁用员工
|
||
// ======================================================================
|
||
app.get('/api/staff', authRequired, asyncHandler(async (req, res) => {
|
||
// staff 只能看自己
|
||
if (req.user.role === 'staff') {
|
||
const me = await get(`
|
||
SELECT id, username, name, phone, email, status, created_at
|
||
FROM users
|
||
WHERE id=? AND status != 'inactive'
|
||
`, [req.user.id]);
|
||
|
||
if (!me) {
|
||
return res.json({ staff: [] });
|
||
}
|
||
|
||
return res.json({ staff: [me] });
|
||
}
|
||
|
||
// admin 和 leader 都可以查看员工列表
|
||
// admin 可以看到所有 staff 和 leader，leader 只能看自己创建的 staff
|
||
const includeInactive = req.query.includeInactive === 'true';
|
||
const statusFilter = includeInactive ? '' : "AND u.status != 'inactive'";
|
||
|
||
let staffRows;
|
||
if (req.user.role === 'admin') {
|
||
staffRows = await all(`
|
||
SELECT u.id, u.username, u.name, u.phone, u.email, u.status, u.role, u.creator_id, u.created_at
|
||
FROM users u
|
||
WHERE u.role IN ('staff', 'leader') ${statusFilter}
|
||
ORDER BY u.status ASC, u.id ASC
|
||
`);
|
||
} else {
|
||
// leader 只能看自己创建的 staff
|
||
staffRows = await all(`
|
||
SELECT u.id, u.username, u.name, u.phone, u.email, u.status, u.role, u.creator_id, u.created_at
|
||
FROM users u
|
||
WHERE u.role = 'staff' AND u.creator_id = ? ${statusFilter}
|
||
ORDER BY u.status ASC, u.id ASC
|
||
`, [req.user.id]);
|
||
}
|
||
|
||
// leader 也返回员工的客户统计数据
|
||
if (req.user.role === 'leader') {
|
||
const staffIds = staffRows.map(s => s.id);
|
||
if (staffIds.length === 0) {
|
||
return res.json({ staff: [] });
|
||
}
|
||
const clientRows = await all(`SELECT staff_id, expireDate, amount FROM clients WHERE staff_id IN (${staffIds.join(',')})`);
|
||
const byStaff = new Map();
|
||
staffRows.forEach((s) => {
|
||
byStaff.set(s.id, {
|
||
...s,
|
||
clientsCount: 0,
|
||
totalAmount: 0,
|
||
expiredCount: 0,
|
||
expiringCount: 0
|
||
});
|
||
});
|
||
const now = new Date();
|
||
const expireThreshold = new Date(now.getTime() + 7 * 24 * 60 * 60 * 1000);
|
||
clientRows.forEach((c) => {
|
||
const staff = byStaff.get(c.staff_id);
|
||
if (!staff) return;
|
||
staff.clientsCount++;
|
||
staff.totalAmount += c.amount || 0;
|
||
if (c.expireDate) {
|
||
const exp = new Date(c.expireDate);
|
||
if (exp < now) staff.expiredCount++;
|
||
else if (exp <= expireThreshold) staff.expiringCount++;
|
||
}
|
||
});
|
||
return res.json({ staff: Array.from(byStaff.values()) });
|
||
}
|
||
|
||
// admin 返回完整统计数据
|
||
const clientRows = await all('SELECT staff_id, expireDate, amount FROM clients');
|
||
const byStaff = new Map();
|
||
staffRows.forEach((s) => {
|
||
byStaff.set(s.id, {
|
||
...s,
|
||
clientsCount: 0,
|
||
totalAmount: 0,
|
||
expiredCount: 0,
|
||
expiringCount: 0
|
||
});
|
||
});
|
||
|
||
clientRows.forEach((c) => {
|
||
const target = byStaff.get(c.staff_id);
|
||
if (!target) return;
|
||
target.clientsCount += 1;
|
||
target.totalAmount += Number(c.amount || 0);
|
||
if (isExpired(c.expireDate)) target.expiredCount += 1;
|
||
if (isExpiringSoon(c.expireDate)) target.expiringCount += 1;
|
||
});
|
||
|
||
res.json({ staff: Array.from(byStaff.values()) });
|
||
}));
|
||
|
||
|
||
app.post('/api/staff', authRequired, roleRequired(['admin', 'leader']), asyncHandler(async (req, res) => {
|
||
const b = req.body || {};
|
||
if (!b.username || !b.password || !b.name) {
|
||
return res.status(400).json({ message: '用户名、姓名和密码不能为空' });
|
||
}
|
||
const existing = await get('SELECT id FROM users WHERE username = ?', [b.username]);
|
||
if (existing) return res.status(400).json({ message: '用户名已存在' });
|
||
|
||
// 只有 admin 可以创建 leader，leader 只能创建 staff
|
||
let role = 'staff';
|
||
if (b.role === 'leader' && req.user.role === 'admin') {
|
||
role = 'leader';
|
||
}
|
||
|
||
// 记录创建者 ID
|
||
const creatorId = req.user.id;
|
||
|
||
const hash = await bcrypt.hash(b.password, 10);
|
||
const r = await run(
|
||
'INSERT INTO users(username, name, password_hash, role, phone, email, status, creator_id) VALUES(?,?,?,?,?,?,?,?)',
|
||
[b.username, b.name, hash, role, b.phone || null, b.email || null, b.status || 'active', creatorId]
|
||
);
|
||
const row = await get('SELECT id, username, name, phone, email, status, role, creator_id, created_at FROM users WHERE id=?', [r.lastID]);
|
||
res.json({ staff: row });
|
||
}));
|
||
|
||
app.put('/api/staff/:id', authRequired, roleRequired(['admin', 'leader']), asyncHandler(async (req, res) => {
|
||
const id = Number(req.params.id);
|
||
if (!id) return res.status(400).json({ message: '参数错误' });
|
||
|
||
const existing = await get('SELECT * FROM users WHERE id=?', [id]);
|
||
if (!existing) return res.status(404).json({ message: '用户不存在' });
|
||
|
||
// 权限检查
|
||
if (existing.role === 'admin') return res.status(403).json({ message: '不能操作管理员账号' });
|
||
if (existing.role === 'leader' && req.user.role !== 'admin') return res.status(403).json({ message: '无权操作组长' });
|
||
// 组长只能操作自己创建的员工
|
||
if (req.user.role === 'leader' && existing.creator_id !== req.user.id) {
|
||
return res.status(403).json({ message: '无权操作此员工' });
|
||
}
|
||
|
||
const b = req.body || {};
|
||
const updates = [];
|
||
const params = [];
|
||
const fields = ['name', 'phone', 'email', 'username']; // 允许修改用户名
|
||
|
||
for (const f of fields) {
|
||
if (Object.prototype.hasOwnProperty.call(b, f)) {
|
||
// 如果修改用户名，检查唯一性
|
||
if (f === 'username' && b.username !== existing.username) {
|
||
const uCheck = await get('SELECT id FROM users WHERE username = ?', [b.username]);
|
||
if (uCheck) return res.status(400).json({ message: '用户名已存在' });
|
||
}
|
||
updates.push(`${f} = ?`);
|
||
params.push(b[f]);
|
||
}
|
||
}
|
||
|
||
// 只有 admin 可以修改角色
|
||
if (req.user.role === 'admin' && b.role && ['leader', 'staff'].includes(b.role)) {
|
||
updates.push('role = ?');
|
||
params.push(b.role);
|
||
}
|
||
|
||
// 只有 admin 可以修改所属组长 (creator_id)
|
||
// 传入 id 为 leader id，如果为 'admin' 或空则设为当前 admin id
|
||
if (req.user.role === 'admin' && Object.prototype.hasOwnProperty.call(b, 'creator_id')) {
|
||
let newCreatorId = Number(b.creator_id);
|
||
if (!newCreatorId) newCreatorId = req.user.id; // 默认为当前操作者（admin）
|
||
|
||
// 验证一下是否存在且是 leader/admin
|
||
if (newCreatorId !== req.user.id) {
|
||
const leaderCheck = await get('SELECT id, role FROM users WHERE id=?', [newCreatorId]);
|
||
if (!leaderCheck) return res.status(400).json({ message: '指定的组长不存在' });
|
||
}
|
||
|
||
updates.push('creator_id = ?');
|
||
params.push(newCreatorId);
|
||
}
|
||
|
||
if (b.password) {
|
||
const hash = await bcrypt.hash(b.password, 10);
|
||
updates.push('password_hash = ?');
|
||
params.push(hash);
|
||
}
|
||
|
||
if (updates.length > 0) {
|
||
params.push(id);
|
||
await run(`UPDATE users SET ${updates.join(', ')} WHERE id = ?`, params);
|
||
}
|
||
|
||
const row = await get('SELECT id, username, name, phone, email, status, role, creator_id, created_at FROM users WHERE id=?', [id]);
|
||
res.json({ staff: row });
|
||
}));
|
||
|
||
app.post('/api/staff/:id/reset_password', authRequired, roleRequired(['admin', 'leader']), asyncHandler(async (req, res) => {
|
||
const id = Number(req.params.id);
|
||
if (!id) return res.status(400).json({ message: '参数错误' });
|
||
|
||
const b = req.body || {};
|
||
if (!b.password) return res.status(400).json({ message: '新密码不能为空' });
|
||
|
||
const existing = await get('SELECT id, role, creator_id FROM users WHERE id=?', [id]);
|
||
if (!existing) return res.status(404).json({ message: '用户不存在' });
|
||
if (existing.role === 'admin') return res.status(403).json({ message: '不能操作管理员账号' });
|
||
if (existing.role === 'leader' && req.user.role !== 'admin') return res.status(403).json({ message: '无权操作组长' });
|
||
// 组长只能操作自己创建的员工
|
||
if (req.user.role === 'leader' && existing.creator_id !== req.user.id) {
|
||
return res.status(403).json({ message: '无权操作此员工' });
|
||
}
|
||
|
||
const hash = await bcrypt.hash(b.password, 10);
|
||
await run('UPDATE users SET password_hash = ? WHERE id = ?', [hash, id]);
|
||
res.json({ success: true });
|
||
}));
|
||
|
||
app.delete('/api/staff/:id', authRequired, roleRequired(['admin', 'leader']), asyncHandler(async (req, res) => {
|
||
const id = Number(req.params.id);
|
||
if (!id) return res.status(400).json({ message: '参数错误' });
|
||
|
||
const existing = await get('SELECT * FROM users WHERE id=?', [id]);
|
||
if (!existing) return res.status(404).json({ message: '用户不存在' });
|
||
if (existing.role === 'admin') return res.status(403).json({ message: '不能操作管理员账号' });
|
||
if (existing.role === 'leader' && req.user.role !== 'admin') return res.status(403).json({ message: '无权操作组长' });
|
||
// 组长只能操作自己创建的员工
|
||
if (req.user.role === 'leader' && existing.creator_id !== req.user.id) {
|
||
return res.status(403).json({ message: '无权操作此员工' });
|
||
}
|
||
|
||
await run('UPDATE users SET status = \'inactive\' WHERE id = ?', [id]);
|
||
res.json({ success: true });
|
||
}));
|
||
|
||
// 启用/禁用用户状态切换
|
||
app.post('/api/staff/:id/toggle_status', authRequired, roleRequired(['admin', 'leader']), asyncHandler(async (req, res) => {
|
||
const id = Number(req.params.id);
|
||
if (!id) return res.status(400).json({ message: '参数错误' });
|
||
|
||
const existing = await get('SELECT * FROM users WHERE id=?', [id]);
|
||
if (!existing) return res.status(404).json({ message: '用户不存在' });
|
||
if (existing.role === 'admin') return res.status(403).json({ message: '不能操作管理员账号' });
|
||
if (existing.role === 'leader' && req.user.role !== 'admin') return res.status(403).json({ message: '无权操作组长' });
|
||
// 组长只能操作自己创建的员工
|
||
if (req.user.role === 'leader' && existing.creator_id !== req.user.id) {
|
||
return res.status(403).json({ message: '无权操作此员工' });
|
||
}
|
||
|
||
const newStatus = existing.status === 'active' ? 'inactive' : 'active';
|
||
await run('UPDATE users SET status = ? WHERE id = ?', [newStatus, id]);
|
||
res.json({ success: true, status: newStatus });
|
||
}));
|
||
|
||
// ======================================================================
|
||
// ✅ 改造：Clients 列表支持筛选/搜索/分页（你前端筛选栏 & 客户页需要）
|
||
// GET /api/clients
|
||
// query:
|
||
// staffId / staffName(可选) / status(active|expiring|expired) / serviceType / q
|
||
// dateFrom / dateTo (按 regDate) / page / pageSize / days(默认7,expiring阈值)
|
||
// ======================================================================
|
||
app.get('/api/clients', authRequired, asyncHandler(async (req, res) => {
|
||
const {
|
||
staffId,
|
||
status = 'all',
|
||
serviceType = 'all',
|
||
q = '',
|
||
dateFrom,
|
||
dateTo,
|
||
page = 1,
|
||
pageSize = 20,
|
||
days = 7
|
||
} = req.query;
|
||
|
||
const p = Math.max(1, Number(page || 1));
|
||
const ps = Math.min(200, Math.max(1, Number(pageSize || 20)));
|
||
const expDays = Number(days || 7);
|
||
|
||
const where = [];
|
||
const params = [];
|
||
|
||
// 权限限制
|
||
if (req.user.role === 'staff') {
|
||
// 员工只能看自己的客户
|
||
where.push('c.staff_id = ?');
|
||
params.push(req.user.id);
|
||
} else if (req.user.role === 'leader') {
|
||
// 组长只能看自己创建的员工的客户
|
||
if (staffId && staffId !== 'all') {
|
||
// 验证该员工是否属于此组长
|
||
const staffUser = await get('SELECT id, creator_id FROM users WHERE id = ?', [Number(staffId)]);
|
||
if (!staffUser || staffUser.creator_id !== req.user.id) {
|
||
return res.status(403).json({ message: '无权查看此员工客户' });
|
||
}
|
||
where.push('c.staff_id = ?');
|
||
params.push(Number(staffId));
|
||
} else {
|
||
// 获取此组长创建的所有员工 ID
|
||
const ownStaff = await all('SELECT id FROM users WHERE creator_id = ? AND role = ?', [req.user.id, 'staff']);
|
||
const staffIds = ownStaff.map(s => s.id);
|
||
if (staffIds.length === 0) {
|
||
return res.json({ clients: [], pagination: { page: 1, pageSize: ps, total: 0, totalPages: 0 } });
|
||
}
|
||
where.push(`c.staff_id IN (${staffIds.join(',')})`);
|
||
}
|
||
} else if (staffId && staffId !== 'all') {
|
||
// admin 可以看任何员工
|
||
where.push('c.staff_id = ?');
|
||
params.push(Number(staffId));
|
||
}
|
||
|
||
if (serviceType && serviceType !== 'all') {
|
||
where.push('c.serviceType = ?');
|
||
params.push(serviceType);
|
||
}
|
||
|
||
if (q && String(q).trim()) {
|
||
const like = `%${String(q).trim()}%`;
|
||
where.push('(c.customerName LIKE ? OR c.phone LIKE ? OR c.remark LIKE ?)');
|
||
params.push(like, like, like);
|
||
}
|
||
|
||
// regDate 范围筛选
|
||
if (dateFrom) {
|
||
where.push('date(c.regDate) >= date(?)');
|
||
params.push(dateFrom);
|
||
}
|
||
if (dateTo) {
|
||
where.push('date(c.regDate) <= date(?)');
|
||
params.push(dateTo);
|
||
}
|
||
|
||
// 状态筛选（expireDate）
|
||
// 注意：expireDate 允许为空，空则默认当 active
|
||
if (status === 'expired') {
|
||
where.push("c.expireDate IS NOT NULL AND date(c.expireDate) < date('now')");
|
||
} else if (status === 'expiring') {
|
||
where.push(`c.expireDate IS NOT NULL AND date(c.expireDate) >= date('now') AND date(c.expireDate) <= date('now', ?)`);
|
||
params.push(`+${expDays} day`);
|
||
} else if (status === 'active') {
|
||
// active: expireDate 为空 或 expireDate > now+days 或 expireDate 在未来但>days
|
||
// 简化：not expired and not expiring
|
||
where.push(`(
|
||
c.expireDate IS NULL
|
||
OR date(c.expireDate) > date('now', ?)
|
||
)`);
|
||
params.push(`+${expDays} day`);
|
||
}
|
||
|
||
const whereSql = where.length ? `WHERE ${where.join(' AND ')}` : '';
|
||
|
||
// 总数
|
||
const totalRow = await get(`SELECT COUNT(1) AS cnt FROM clients c ${whereSql}`, params);
|
||
const total = totalRow ? totalRow.cnt : 0;
|
||
|
||
// 分页数据
|
||
const offset = (p - 1) * ps;
|
||
const list = await all(
|
||
`
|
||
SELECT c.*, u.name AS staff_name, u.username AS staff_username
|
||
FROM clients c
|
||
LEFT JOIN users u ON u.id = c.staff_id
|
||
${whereSql}
|
||
ORDER BY c.id DESC
|
||
LIMIT ? OFFSET ?
|
||
`,
|
||
[...params, ps, offset]
|
||
);
|
||
|
||
// 补一个计算出来的 status 字段，前端更好用
|
||
const clients = list.map(row => ({
|
||
...row,
|
||
status: getClientStatus(row.expireDate, expDays)
|
||
}));
|
||
|
||
res.json({
|
||
page: p,
|
||
pageSize: ps,
|
||
total,
|
||
clients
|
||
});
|
||
}));
|
||
|
||
// ======================================================================
|
||
// Staff clients endpoints (for staff page)
|
||
// ======================================================================
|
||
app.get('/api/staff/clients', authRequired, roleRequired(['staff', 'admin']), asyncHandler(async (req, res) => {
|
||
const days = Number(req.query.days || 7);
|
||
const rows = await all(
|
||
`SELECT c.*
|
||
FROM clients c
|
||
WHERE c.staff_id = ?
|
||
ORDER BY c.id DESC`,
|
||
[req.user.id]
|
||
);
|
||
const clients = rows.map(row => ({ ...row, status: getClientStatus(row.expireDate, days) }));
|
||
res.json({ clients });
|
||
}));
|
||
|
||
app.post('/api/staff/clients', authRequired, roleRequired(['staff', 'admin']), asyncHandler(async (req, res) => {
|
||
const b = req.body || {};
|
||
if (!b.customerName) return res.status(400).json({ message: '客户姓名不能为空' });
|
||
|
||
const amount = Number(b.amount || 0);
|
||
const now = new Date().toISOString();
|
||
|
||
const r = await run(`
|
||
INSERT INTO clients(staff_id, regDate, customerName, phone, expireDate, amount, serviceType, remark, lastRenewAt, created_at, updated_at)
|
||
VALUES(?,?,?,?,?,?,?,?,?,?,?)
|
||
`, [
|
||
req.user.id,
|
||
b.regDate || null,
|
||
b.customerName,
|
||
b.phone || null,
|
||
b.expireDate || null,
|
||
amount,
|
||
b.serviceType || null,
|
||
b.remark || '',
|
||
b.lastRenewAt || null,
|
||
now,
|
||
now
|
||
]);
|
||
|
||
const row = await get('SELECT * FROM clients WHERE id=?', [r.lastID]);
|
||
res.json({ client: { ...row, status: getClientStatus(row.expireDate) } });
|
||
}));
|
||
|
||
app.put('/api/staff/clients/:id', authRequired, roleRequired(['staff', 'admin']), asyncHandler(async (req, res) => {
|
||
const id = Number(req.params.id);
|
||
if (!id) return res.status(400).json({ message: '参数错误' });
|
||
|
||
const existing = await get('SELECT * FROM clients WHERE id=?', [id]);
|
||
if (!existing) return res.status(404).json({ message: '客户不存在' });
|
||
|
||
if (req.user.role !== 'admin' && existing.staff_id !== req.user.id) {
|
||
return res.status(403).json({ message: '无权限修改该客户' });
|
||
}
|
||
|
||
const b = req.body || {};
|
||
const fields = ['regDate', 'customerName', 'phone', 'expireDate', 'amount', 'serviceType', 'remark', 'lastRenewAt'];
|
||
const updates = [];
|
||
const params = [];
|
||
|
||
for (const f of fields) {
|
||
if (!Object.prototype.hasOwnProperty.call(b, f)) continue;
|
||
updates.push(`${f} = ?`);
|
||
if (f === 'amount') params.push(Number(b[f] || 0));
|
||
else params.push(b[f]);
|
||
}
|
||
|
||
if (updates.length === 0) {
|
||
const row = await get('SELECT * FROM clients WHERE id=?', [id]);
|
||
return res.json({ client: { ...row, status: getClientStatus(row.expireDate) } });
|
||
}
|
||
|
||
updates.push('updated_at = ?');
|
||
params.push(new Date().toISOString());
|
||
params.push(id);
|
||
|
||
await run(`UPDATE clients SET ${updates.join(', ')} WHERE id = ?`, params);
|
||
|
||
const row = await get('SELECT * FROM clients WHERE id=?', [id]);
|
||
res.json({ client: { ...row, status: getClientStatus(row.expireDate) } });
|
||
}));
|
||
|
||
// New client
|
||
app.post('/api/clients', authRequired, roleRequired(['staff', 'admin']), asyncHandler(async (req, res) => {
|
||
const b = req.body || {};
|
||
const staffId = (req.user.role === 'admin' && b.staff_id) ? Number(b.staff_id) : req.user.id;
|
||
|
||
if (!b.customerName) return res.status(400).json({ message: '客户姓名不能为空' });
|
||
|
||
const amount = Number(b.amount || 0);
|
||
const now = new Date().toISOString();
|
||
|
||
const r = await run(`
|
||
INSERT INTO clients(staff_id, regDate, customerName, phone, expireDate, amount, serviceType, remark, lastRenewAt, created_at, updated_at)
|
||
VALUES(?,?,?,?,?,?,?,?,?,?,?)
|
||
`, [
|
||
staffId,
|
||
b.regDate || null,
|
||
b.customerName,
|
||
b.phone || null,
|
||
b.expireDate || null,
|
||
amount,
|
||
b.serviceType || null,
|
||
b.remark || '',
|
||
b.lastRenewAt || null,
|
||
now,
|
||
now
|
||
]);
|
||
|
||
const row = await get('SELECT * FROM clients WHERE id=?', [r.lastID]);
|
||
res.json({ client: { ...row, status: getClientStatus(row.expireDate) } });
|
||
}));
|
||
|
||
// 更新客户
|
||
app.put('/api/clients/:id', authRequired, asyncHandler(async (req, res) => {
|
||
const id = Number(req.params.id);
|
||
if (!id) return res.status(400).json({ message: '参数错误' });
|
||
|
||
const existing = await get('SELECT * FROM clients WHERE id=?', [id]);
|
||
if (!existing) return res.status(404).json({ message: '客户不存在' });
|
||
|
||
if (req.user.role !== 'admin' && existing.staff_id !== req.user.id) {
|
||
return res.status(403).json({ message: '无权限修改该客户' });
|
||
}
|
||
|
||
const b = req.body || {};
|
||
const fields = ['regDate', 'customerName', 'phone', 'expireDate', 'amount', 'serviceType', 'remark', 'lastRenewAt', 'staff_id'];
|
||
const updates = [];
|
||
const params = [];
|
||
|
||
for (const f of fields) {
|
||
if (!Object.prototype.hasOwnProperty.call(b, f)) continue;
|
||
|
||
// staff_id 只有 admin 能改
|
||
if (f === 'staff_id' && req.user.role !== 'admin') continue;
|
||
|
||
updates.push(`${f} = ?`);
|
||
if (f === 'amount') params.push(Number(b[f] || 0));
|
||
else if (f === 'staff_id') params.push(Number(b[f]));
|
||
else params.push(b[f]);
|
||
}
|
||
|
||
if (updates.length === 0) return res.json({ client: { ...existing, status: getClientStatus(existing.expireDate) } });
|
||
|
||
updates.push('updated_at = ?');
|
||
params.push(new Date().toISOString());
|
||
params.push(id);
|
||
|
||
await run(`UPDATE clients SET ${updates.join(', ')} WHERE id = ?`, params);
|
||
|
||
const row = await get('SELECT * FROM clients WHERE id=?', [id]);
|
||
res.json({ client: { ...row, status: getClientStatus(row.expireDate) } });
|
||
}));
|
||
|
||
// ✅ 新增：删除客户（单个）
|
||
app.delete('/api/clients/:id', authRequired, asyncHandler(async (req, res) => {
|
||
const id = Number(req.params.id);
|
||
if (!id) return res.status(400).json({ message: '参数错误' });
|
||
|
||
const existing = await get('SELECT * FROM clients WHERE id=?', [id]);
|
||
if (!existing) return res.status(404).json({ message: '客户不存在' });
|
||
|
||
if (req.user.role !== 'admin' && existing.staff_id !== req.user.id) {
|
||
return res.status(403).json({ message: '无权限删除该客户' });
|
||
}
|
||
|
||
await run('DELETE FROM clients WHERE id=?', [id]);
|
||
res.json({ success: true });
|
||
}));
|
||
|
||
// ======================================================================
|
||
// ✅ 新增：到期提醒列表（你左侧“到期提醒”页会用到）
|
||
// GET /api/reminders?type=expiring|expired&days=7&staffId=xxx
|
||
// ======================================================================
|
||
app.get('/api/reminders', authRequired, asyncHandler(async (req, res) => {
|
||
const type = req.query.type || 'expiring';
|
||
const days = Number(req.query.days || 7);
|
||
const staffId = req.query.staffId;
|
||
|
||
const where = [];
|
||
const params = [];
|
||
|
||
if (req.user.role !== 'admin') {
|
||
where.push('c.staff_id = ?');
|
||
params.push(req.user.id);
|
||
} else if (staffId && staffId !== 'all') {
|
||
where.push('c.staff_id = ?');
|
||
params.push(Number(staffId));
|
||
}
|
||
|
||
if (type === 'expired') {
|
||
where.push("c.expireDate IS NOT NULL AND date(c.expireDate) < date('now')");
|
||
} else {
|
||
where.push(`c.expireDate IS NOT NULL AND date(c.expireDate) >= date('now') AND date(c.expireDate) <= date('now', ?)`);
|
||
params.push(`+${days} day`);
|
||
}
|
||
|
||
const whereSql = where.length ? `WHERE ${where.join(' AND ')}` : '';
|
||
const rows = await all(`
|
||
SELECT c.*, u.name AS staff_name, u.username AS staff_username
|
||
FROM clients c
|
||
LEFT JOIN users u ON u.id = c.staff_id
|
||
${whereSql}
|
||
ORDER BY date(c.expireDate) ASC, c.id DESC
|
||
`, params);
|
||
|
||
res.json({
|
||
type,
|
||
days,
|
||
clients: rows.map(r => ({ ...r, status: getClientStatus(r.expireDate, days) }))
|
||
});
|
||
}));
|
||
|
||
// ======================================================================
|
||
// ✅ 新增：导出客户 CSV（简单实用，Excel 也能打开）
|
||
// GET /api/export/clients?...（同 /api/clients 的筛选参数）
|
||
// ======================================================================
|
||
app.get('/api/export/clients', authRequired, asyncHandler(async (req, res) => {
|
||
// 复用 /api/clients 的筛选逻辑，但这里直接查全部，不分页
|
||
const {
|
||
staffId,
|
||
status = 'all',
|
||
serviceType = 'all',
|
||
q = '',
|
||
dateFrom,
|
||
dateTo,
|
||
days = 7
|
||
} = req.query;
|
||
|
||
const expDays = Number(days || 7);
|
||
|
||
const where = [];
|
||
const params = [];
|
||
|
||
if (req.user.role !== 'admin') {
|
||
where.push('c.staff_id = ?');
|
||
params.push(req.user.id);
|
||
} else if (staffId && staffId !== 'all') {
|
||
where.push('c.staff_id = ?');
|
||
params.push(Number(staffId));
|
||
}
|
||
|
||
if (serviceType && serviceType !== 'all') {
|
||
where.push('c.serviceType = ?');
|
||
params.push(serviceType);
|
||
}
|
||
|
||
if (q && String(q).trim()) {
|
||
const like = `%${String(q).trim()}%`;
|
||
where.push('(c.customerName LIKE ? OR c.phone LIKE ? OR c.remark LIKE ?)');
|
||
params.push(like, like, like);
|
||
}
|
||
|
||
if (dateFrom) {
|
||
where.push('date(c.regDate) >= date(?)');
|
||
params.push(dateFrom);
|
||
}
|
||
if (dateTo) {
|
||
where.push('date(c.regDate) <= date(?)');
|
||
params.push(dateTo);
|
||
}
|
||
|
||
if (status === 'expired') {
|
||
where.push("c.expireDate IS NOT NULL AND date(c.expireDate) < date('now')");
|
||
} else if (status === 'expiring') {
|
||
where.push(`c.expireDate IS NOT NULL AND date(c.expireDate) >= date('now') AND date(c.expireDate) <= date('now', ?)`);
|
||
params.push(`+${expDays} day`);
|
||
} else if (status === 'active') {
|
||
where.push(`(
|
||
c.expireDate IS NULL
|
||
OR date(c.expireDate) > date('now', ?)
|
||
)`);
|
||
params.push(`+${expDays} day`);
|
||
}
|
||
|
||
const whereSql = where.length ? `WHERE ${where.join(' AND ')}` : '';
|
||
|
||
const rows = await all(`
|
||
SELECT c.*, u.name AS staff_name, u.username AS staff_username
|
||
FROM clients c
|
||
LEFT JOIN users u ON u.id = c.staff_id
|
||
${whereSql}
|
||
ORDER BY c.id DESC
|
||
`, params);
|
||
|
||
// CSV
|
||
const headers = [
|
||
'id', 'staff_name', 'staff_username', 'customerName', 'phone', 'serviceType', 'regDate', 'expireDate', 'amount', 'status', 'remark'
|
||
];
|
||
const escapeCsv = (v) => {
|
||
if (v === null || v === undefined) return '';
|
||
const s = String(v);
|
||
if (/[",\n]/.test(s)) return `"${s.replace(/"/g, '""')}"`;
|
||
return s;
|
||
};
|
||
|
||
const lines = [];
|
||
lines.push(headers.join(','));
|
||
for (const r of rows) {
|
||
const statusText = getClientStatus(r.expireDate, expDays);
|
||
const line = [
|
||
r.id,
|
||
r.staff_name || '',
|
||
r.staff_username || '',
|
||
r.customerName || '',
|
||
r.phone || '',
|
||
r.serviceType || '',
|
||
r.regDate || '',
|
||
r.expireDate || '',
|
||
r.amount ?? 0,
|
||
statusText,
|
||
r.remark || ''
|
||
].map(escapeCsv).join(',');
|
||
lines.push(line);
|
||
}
|
||
|
||
const csv = '\ufeff' + lines.join('\n'); // 带 BOM，Excel 打开中文不乱码
|
||
res.setHeader('Content-Type', 'text/csv; charset=utf-8');
|
||
res.setHeader('Content-Disposition', `attachment; filename="clients_${Date.now()}.csv"`);
|
||
res.send(csv);
|
||
}));
|
||
|
||
// ======================================================================
|
||
// 兼容保留：你原来的 admin 接口（不想改前端也能用）
|
||
// ======================================================================
|
||
app.get('/api/admin/staff', authRequired, roleRequired(['admin']), asyncHandler(async (req, res) => {
|
||
// 直接代理到 /api/staff
|
||
req.user.role = 'admin';
|
||
return app._router.handle(req, res, () => { }, 'get', '/api/staff');
|
||
}));
|
||
|
||
app.post('/api/admin/staff', authRequired, roleRequired(['admin']), asyncHandler(async (req, res) => {
|
||
return app._router.handle(req, res, () => { }, 'post', '/api/staff');
|
||
}));
|
||
|
||
app.put('/api/admin/staff/:id', authRequired, roleRequired(['admin']), asyncHandler(async (req, res) => {
|
||
return app._router.handle(req, res, () => { }, 'put', `/api/staff/${req.params.id}`);
|
||
}));
|
||
|
||
app.delete('/api/admin/staff/:id', authRequired, roleRequired(['admin']), asyncHandler(async (req, res) => {
|
||
return app._router.handle(req, res, () => { }, 'delete', `/api/staff/${req.params.id}`);
|
||
}));
|
||
|
||
app.get('/api/admin/stats', authRequired, roleRequired(['admin']), asyncHandler(async (req, res) => {
|
||
// 兼容：用新 summary 的 stats
|
||
const fakeReq = { ...req, query: { ...req.query } };
|
||
// 直接再算一次（避免 hack router）
|
||
const rows = await all('SELECT expireDate, amount FROM clients');
|
||
let totalAmount = 0;
|
||
let totalClients = rows.length;
|
||
let activeClients = 0;
|
||
let expiringSoonClients = 0;
|
||
for (const r of rows) {
|
||
totalAmount += Number(r.amount || 0);
|
||
if (!isExpired(r.expireDate)) activeClients += 1;
|
||
if (isExpiringSoon(r.expireDate)) expiringSoonClients += 1;
|
||
}
|
||
res.json({ stats: { totalAmount, totalClients, activeClients, expiringSoonClients } });
|
||
}));
|
||
|
||
// ===== 静态前端文件 =====
|
||
const publicDir = path.join(__dirname, 'public');
|
||
app.use(express.static(publicDir));
|
||
app.get('/', (req, res) => res.redirect('/index.html'));
|
||
|
||
// ===== error handler =====
|
||
app.use((err, req, res, next) => {
|
||
console.error(err);
|
||
res.status(500).json({ message: '服务器错误' });
|
||
});
|
||
|
||
async function main() {
|
||
if (process.argv.includes('--initdb')) {
|
||
await initDb();
|
||
process.exit(0);
|
||
}
|
||
await initDb();
|
||
app.listen(PORT, () => {
|
||
console.log(`CRM running on http://0.0.0.0:${PORT}`);
|
||
});
|
||
}
|
||
|
||
main().catch(err => {
|
||
console.error(err);
|
||
process.exit(1);
|
||
});
|