|
|
/**
|
|
|
* 用户数据模型
|
|
|
*
|
|
|
* 功能：封装所有与用户表相关的数据库操作
|
|
|
*
|
|
|
* 说明：Model层负责与数据库交互，不包含业务逻辑
|
|
|
*/
|
|
|
|
|
|
const { pool } = require('../config/database');
|
|
|
|
|
|
/**
|
|
|
* 用户模型类
|
|
|
*/
|
|
|
class User {
|
|
|
/**
|
|
|
* 根据ID查找用户
|
|
|
* @param {number} id - 用户ID
|
|
|
* @returns {Promise<object|null>} 用户对象或null
|
|
|
*/
|
|
|
static async findById(id) {
|
|
|
try {
|
|
|
const [rows] = await pool.query(
|
|
|
'SELECT * FROM users WHERE id = ?',
|
|
|
[id]
|
|
|
);
|
|
|
return rows[0] || null;
|
|
|
} catch (error) {
|
|
|
throw new Error('查询用户失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 根据用户名查找用户
|
|
|
* @param {string} username - 用户名
|
|
|
* @returns {Promise<object|null>} 用户对象或null
|
|
|
*/
|
|
|
static async findByUsername(username) {
|
|
|
try {
|
|
|
const [rows] = await pool.query(
|
|
|
'SELECT * FROM users WHERE username = ?',
|
|
|
[username]
|
|
|
);
|
|
|
return rows[0] || null;
|
|
|
} catch (error) {
|
|
|
throw new Error('查询用户失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 根据邮箱查找用户
|
|
|
* @param {string} email - 邮箱
|
|
|
* @returns {Promise<object|null>} 用户对象或null
|
|
|
*/
|
|
|
static async findByEmail(email) {
|
|
|
try {
|
|
|
const [rows] = await pool.query(
|
|
|
'SELECT * FROM users WHERE email = ?',
|
|
|
[email]
|
|
|
);
|
|
|
return rows[0] || null;
|
|
|
} catch (error) {
|
|
|
throw new Error('查询用户失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 创建新用户
|
|
|
* @param {object} userData - 用户数据
|
|
|
* @returns {Promise<object>} 创建的用户信息
|
|
|
*/
|
|
|
static async create(userData) {
|
|
|
try {
|
|
|
const { username, password, real_name, phone, email, role = 'customer' } = userData;
|
|
|
|
|
|
const [result] = await pool.query(
|
|
|
`INSERT INTO users (username, password, real_name, phone, email, role)
|
|
|
VALUES (?, ?, ?, ?, ?, ?)`,
|
|
|
[username, password, real_name, phone, email, role]
|
|
|
);
|
|
|
|
|
|
// 返回新创建的用户（不包含密码）
|
|
|
return await this.findByIdWithoutPassword(result.insertId);
|
|
|
} catch (error) {
|
|
|
// 处理唯一性约束错误
|
|
|
if (error.code === 'ER_DUP_ENTRY') {
|
|
|
throw new Error('用户名或邮箱已存在');
|
|
|
}
|
|
|
throw new Error('创建用户失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 更新用户信息
|
|
|
* @param {number} id - 用户ID
|
|
|
* @param {object} userData - 要更新的数据
|
|
|
* @returns {Promise<object>} 更新后的用户信息
|
|
|
*/
|
|
|
static async update(id, userData) {
|
|
|
try {
|
|
|
const { real_name, phone, email, avatar } = userData;
|
|
|
|
|
|
// 构建动态更新语句
|
|
|
const updates = [];
|
|
|
const values = [];
|
|
|
|
|
|
if (real_name !== undefined) {
|
|
|
updates.push('real_name = ?');
|
|
|
values.push(real_name);
|
|
|
}
|
|
|
if (phone !== undefined) {
|
|
|
updates.push('phone = ?');
|
|
|
values.push(phone);
|
|
|
}
|
|
|
if (email !== undefined) {
|
|
|
updates.push('email = ?');
|
|
|
values.push(email);
|
|
|
}
|
|
|
if (avatar !== undefined) {
|
|
|
updates.push('avatar = ?');
|
|
|
values.push(avatar);
|
|
|
}
|
|
|
|
|
|
if (updates.length === 0) {
|
|
|
throw new Error('没有要更新的数据');
|
|
|
}
|
|
|
|
|
|
values.push(id); // 添加WHERE条件的参数
|
|
|
|
|
|
await pool.query(
|
|
|
`UPDATE users SET ${updates.join(', ')} WHERE id = ?`,
|
|
|
values
|
|
|
);
|
|
|
|
|
|
return await this.findByIdWithoutPassword(id);
|
|
|
} catch (error) {
|
|
|
throw new Error('更新用户失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 更新密码
|
|
|
* @param {number} id - 用户ID
|
|
|
* @param {string} newPassword - 新密码（已加密）
|
|
|
* @returns {Promise<boolean>} 是否成功
|
|
|
*/
|
|
|
static async updatePassword(id, newPassword) {
|
|
|
try {
|
|
|
await pool.query(
|
|
|
'UPDATE users SET password = ? WHERE id = ?',
|
|
|
[newPassword, id]
|
|
|
);
|
|
|
return true;
|
|
|
} catch (error) {
|
|
|
throw new Error('更新密码失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 删除用户
|
|
|
* @param {number} id - 用户ID
|
|
|
* @returns {Promise<boolean>} 是否成功
|
|
|
*/
|
|
|
static async delete(id) {
|
|
|
try {
|
|
|
await pool.query('DELETE FROM users WHERE id = ?', [id]);
|
|
|
return true;
|
|
|
} catch (error) {
|
|
|
throw new Error('删除用户失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 获取用户列表（分页）
|
|
|
* @param {object} options - 查询选项
|
|
|
* @returns {Promise<object>} 用户列表和总数
|
|
|
*/
|
|
|
static async findAll(options = {}) {
|
|
|
try {
|
|
|
const { page = 1, pageSize = 10, role, status } = options;
|
|
|
const offset = (page - 1) * pageSize;
|
|
|
|
|
|
// 构建WHERE条件
|
|
|
const conditions = [];
|
|
|
const params = [];
|
|
|
|
|
|
if (role) {
|
|
|
conditions.push('role = ?');
|
|
|
params.push(role);
|
|
|
}
|
|
|
if (status !== undefined) {
|
|
|
conditions.push('status = ?');
|
|
|
params.push(status);
|
|
|
}
|
|
|
|
|
|
const whereClause = conditions.length > 0
|
|
|
? 'WHERE ' + conditions.join(' AND ')
|
|
|
: '';
|
|
|
|
|
|
// 查询总数
|
|
|
const [countResult] = await pool.query(
|
|
|
`SELECT COUNT(*) as total FROM users ${whereClause}`,
|
|
|
params
|
|
|
);
|
|
|
const total = countResult[0].total;
|
|
|
|
|
|
// 查询列表（排除密码）
|
|
|
const [rows] = await pool.query(
|
|
|
`SELECT id, username, real_name, phone, email, role, avatar, status, created_at, updated_at
|
|
|
FROM users ${whereClause}
|
|
|
ORDER BY created_at DESC
|
|
|
LIMIT ? OFFSET ?`,
|
|
|
[...params, pageSize, offset]
|
|
|
);
|
|
|
|
|
|
return {
|
|
|
list: rows,
|
|
|
total,
|
|
|
page: parseInt(page),
|
|
|
pageSize: parseInt(pageSize)
|
|
|
};
|
|
|
} catch (error) {
|
|
|
throw new Error('查询用户列表失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 查询用户但不返回密码
|
|
|
* @param {number} id - 用户ID
|
|
|
* @returns {Promise<object|null>} 用户对象（不含密码）
|
|
|
*/
|
|
|
static async findByIdWithoutPassword(id) {
|
|
|
try {
|
|
|
const [rows] = await pool.query(
|
|
|
`SELECT id, username, real_name, phone, email, role, avatar, status, created_at, updated_at
|
|
|
FROM users WHERE id = ?`,
|
|
|
[id]
|
|
|
);
|
|
|
return rows[0] || null;
|
|
|
} catch (error) {
|
|
|
throw new Error('查询用户失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 更新用户状态
|
|
|
* @param {number} id - 用户ID
|
|
|
* @param {number} status - 状态值（1启用/0禁用）
|
|
|
* @returns {Promise<boolean>} 是否成功
|
|
|
*/
|
|
|
static async updateStatus(id, status) {
|
|
|
try {
|
|
|
await pool.query(
|
|
|
'UPDATE users SET status = ? WHERE id = ?',
|
|
|
[status, id]
|
|
|
);
|
|
|
return true;
|
|
|
} catch (error) {
|
|
|
throw new Error('更新用户状态失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 检查用户名是否已存在
|
|
|
* @param {string} username - 用户名
|
|
|
* @param {number} excludeId - 排除的用户ID（用于更新时检查）
|
|
|
* @returns {Promise<boolean>} 是否存在
|
|
|
*/
|
|
|
static async isUsernameExists(username, excludeId = null) {
|
|
|
try {
|
|
|
let query = 'SELECT COUNT(*) as count FROM users WHERE username = ?';
|
|
|
const params = [username];
|
|
|
|
|
|
if (excludeId) {
|
|
|
query += ' AND id != ?';
|
|
|
params.push(excludeId);
|
|
|
}
|
|
|
|
|
|
const [rows] = await pool.query(query, params);
|
|
|
return rows[0].count > 0;
|
|
|
} catch (error) {
|
|
|
throw new Error('检查用户名失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 检查邮箱是否已存在
|
|
|
* @param {string} email - 邮箱
|
|
|
* @param {number} excludeId - 排除的用户ID
|
|
|
* @returns {Promise<boolean>} 是否存在
|
|
|
*/
|
|
|
static async isEmailExists(email, excludeId = null) {
|
|
|
try {
|
|
|
let query = 'SELECT COUNT(*) as count FROM users WHERE email = ?';
|
|
|
const params = [email];
|
|
|
|
|
|
if (excludeId) {
|
|
|
query += ' AND id != ?';
|
|
|
params.push(excludeId);
|
|
|
}
|
|
|
|
|
|
const [rows] = await pool.query(query, params);
|
|
|
return rows[0].count > 0;
|
|
|
} catch (error) {
|
|
|
throw new Error('检查邮箱失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
|
|
|
module.exports = User;
|
