const connection = require('../sql'); class UserService { // 查询总条数 async listCount(projectId, userName) { const statement = "SELECT COUNT(`id`) total FROM project_user WHERE project_id = ? and (user_name LIKE COALESCE(CONCAT('%',?,'%'), user_name) OR ? IS NULL);"; const [result] = await connection.execute(statement, [projectId, userName || null, userName || null]); return result[0]; } // 查询列表 async list(projectId, userName, pageNum = 1, pageSize = 10) { const offset = (+pageNum - 1) * pageSize + ''; const limit = pageSize; const statement = ` SELECT id, system_role as systemRole, project_id as projectId, role_id as roleId, user_id as userId, user_name as userName, updated_at as updatedAt, created_at as createdAt FROM project_user WHERE project_id = ? and (user_name LIKE COALESCE(CONCAT('%',?,'%'), user_name) OR ? IS NULL) ORDER BY id DESC LIMIT ${offset},${limit}; `; const [result] = await connection.execute(statement, [projectId, userName || null, userName || null]); return result; } async createUser(params) { const statement = 'INSERT INTO project_user (user_id, user_name, project_id, system_role, role_id, created_uid, created_uname) VALUES (?, ?, ?, ?, ?, ?, ?);'; const [result] = await connection.execute(statement, [ params.userId, params.userName, params.projectId, params.systemRole, params.roleId, params.createUserId, params.createUserName, ]); return result; } async deleteUser(id) { const statement = 'DELETE FROM project_user WHERE id = ?;'; const [result] = await connection.execute(statement, [id]); return result; } async deleteUserByProjectId(projectId) { const statement = 'DELETE FROM project_user WHERE project_id = ?;'; const [result] = await connection.execute(statement, [projectId]); return result; } async updateUser(params) { const statement = 'UPDATE project_user SET system_role = ?, role_id = ? WHERE id = ?;'; const [result] = await connection.execute(statement, [params.systemRole, params.roleId, params.id]); return result; } // 查询详情 async detail(id) { const statement = ` SELECT id, system_role as systemRole, project_id as projectId, role_id as roleId, user_id as userId, user_name as userName, updated_at as updatedAt, created_at as createdAt FROM project_user WHERE id = ?; `; const [result] = await connection.execute(statement, [id]); return result[0]; } // 根据项目ID和账号查询用户角色信息 async getUserRole(userId, projectId) { const statement = ` SELECT id, system_role as systemRole, project_id as projectId, role_id as roleId, user_id as userId, user_name as userName, updated_at as updatedAt, created_at as createdAt FROM project_user WHERE user_id = ? and project_id = ?; `; const [result] = await connection.execute(statement, [userId, projectId]); return result[0]; } // 用户注销 async deleteAllProjectUser(userId) { const statement = 'DELETE FROM project_user WHERE created_uid = ?;'; const [result] = await connection.execute(statement, [userId]); return result; } } module.exports = new UserService();