第一节：剖析DB设计和业务接口的编写
一. DB设计
1. 说明
框架的基础功能包括：用户管理、角色管理、登录日志、操作日志模块，1个角色可以配置多个权限，1个用户可以配置多个角色，相关表设计如下，大致思路：登录→根据userId拿到所有角色Id→获取所有角色Id对应的权限信息→对这些权限信息去重。
2.详细结构分享
(1). 用户表
(2). 角色表
(3). 菜单权限表
(4). 用户-角色关联表
(5). 角色-菜单权限关联表
(6). 操作日志表
(7). 登录日志表
二. 业务接口编写
1. 说明
控制器按区域存放，路由匹配规则为：[Route("Api/[area]/[controller]/[action]")]，相关接口设计详见下面代码，在后续章节将结合EasyUI框架中的各种组件来分析接口业务。
2. 详细接口分享
(1). 主控制器- SysMainApiController
[Area("AdminApi_Areas")] [Route("Api/[area]/[controller]/[action]")] [SkipLogin] public class SysMainApiController : Controller { public IBaseService _baseService; public IConfiguration _configuration; public SysMainApiController(IBaseService baseService, IConfiguration configuration) { this._baseService = baseService; this._configuration = configuration; } #region 01-校验登录 /// <summary> /// 校验登录 /// </summary> /// <param name="userAccount">账号</param> /// <param name="passWord">密码</param> /// <returns></returns> [SkipAll] public IActionResult CheckLogin(string userAccount, string passWord) { try { //1.校验账号是否存在 var userInfor = _baseService.Entities<T_SysUser>().Where(u => u.userAccount == userAccount).FirstOrDefault(); if (userInfor != null) { //2. 账号和密码是否匹配 var passWord1 = SecurityHelp.SHA(passWord); if (passWord1.Equals(userInfor.userPwd, StringComparison.InvariantCultureIgnoreCase)) { //3. 产生token进行返回 //过期时间(可以不设置，下面表示签名后 12个小时过期) double exp = (DateTime.UtcNow.AddHours(12) - new DateTime(1970, 1, 1)).TotalSeconds; //进行组装 var payload = new Dictionary<string, object> { {"userId", userInfor.id }, {"userAccount", userInfor.userAccount }, {"exp",exp } }; var token = JWTHelp.JWTJiaM(payload, _configuration["JWTSecret"]); //4.记录登录日志 T_SysLoginLog sysLoginLog = new T_SysLoginLog() { id = Guid.NewGuid().ToString("N"), userId = userInfor.id, userAccount = userInfor.userAccount, loginTime = DateTime.Now, delFlag = 0, loginIp = HttpContext.Connection.RemoteIpAddress.ToString() }; _baseService.Add(sysLoginLog); return Json(new { status = "ok", msg = "登录成功", data = token }); } else { //密码不正确 return Json(new { status = "error", msg = "密码不正确", data = "" }); } } else { return Json(new { status = "error", msg = "账号不存在", data = "" }); }; } catch (Exception ex) { LogUtils.Error(ex); ; return Json(new { status = "error", msg = "登录失败", data = "" }); } } #endregion #region 02-获取左侧菜单权限结构(EasyUI版本)【已修改】 /// <summary> /// 获取权限信息 /// </summary> /// <returns></returns> [HttpPost] public async Task<string> GetEasyLeftMenu() { try { var jwtData = JsonHelp.ToObject<JwtData>(ControllerContext.RouteData.Values["auth"].ToString()); var userId = jwtData.userId; //根据用户Id，查出来所有的角色，然后把所有角色对应的权限id查出来并去重 var data1 = _baseService.Entities<T_SysUserRole>(); var data2 = _baseService.Entities<T_SysRolePer>(); var data = await (from a in data1 join b in data2 on a.roleId equals b.roleId where a.userId == userId select b).Select(p => p.perId).Distinct().ToListAsync(); //根据权限Id组装固定格式的权限信息进行反馈 var perList = await _baseService.GetListByAsync<T_SysPermisson>(u => u.delFlag == 0 && data.Contains(u.id)); List<EasyUiMenuInfor> menuList = GetOwnAllPer(perList, "1"); return JsonHelp.ToJsonString(menuList); } catch (Exception ex) { LogUtils.Error(ex); return ""; } } /// <summary> /// 获取所有自己的菜单信息 /// </summary> /// <param name="perList">所有的自己的菜单权限</param> /// <param name="parentId">父菜单id</param> /// <returns></returns> public List<EasyUiMenuInfor> GetOwnAllPer(List<T_SysPermisson> perList, string parentId) { var perListMain = perList.Where(p => p.parentId == parentId).OrderBy(u => u.sortFlag).ToList(); if (perListMain.Count() > 0) { //最新的组装形式 List<EasyUiMenuInfor> menuList = new List<EasyUiMenuInfor>(); foreach (var item in perListMain) { EasyUiMenuInfor menuItem = new EasyUiMenuInfor(); menuItem.id = item.id; menuItem.text = item.menuName; var myUrl = item.frontUrl; menuItem.attributes = myUrl; menuItem.iconCls = item.iconClassName; menuItem.children = GetChildPer(perList, item.id); menuItem.state = menuItem.children.Count() == 0 ? "open" : "closed"; menuList.Add(menuItem); } return menuList; } else { return new List<EasyUiMenuInfor>(); //返回空集合 } } /// <summary> /// 获取所有的子菜单权限 /// </summary> /// <param name="perList"></param> /// <param name="childId"></param> /// <returns></returns> public List<EasyUiMenuInfor> GetChildPer(List<T_SysPermisson> perList, string childId) { var perListMain = perList.Where(p => p.parentId == childId).OrderBy(u => u.sortFlag).ToList(); if (perListMain.Count() > 0) { return GetOwnAllPer(perList, childId); } else { return new List<EasyUiMenuInfor>(); //返回空集合 } } #endregion }
(2). 用户相关控制器-SysUserApiController
[Area("AdminApi_Areas")] [Route("Api/[area]/[controller]/[action]")] [SkipLogin] public class SysUserApiController : Controller { public IBaseService _baseService; public SysUserApiController(IBaseService baseService) { this._baseService = baseService; } #region 01-条件查询用户信息【已修改】 /// <summary> /// 条件查询用户信息 /// </summary> /// <param name="userAccount">账号</param> /// <param name="userRealName">姓名</param> /// <param name="userPhone">电话</param> /// <param name="userSex">性别</param> /// <param name="pc">页面分页查询 封装实体</param> /// <returns></returns> [HttpPost] public async Task<IActionResult> GetUserInforByCondition(string userAccount, string userRealName, string userPhone, int? userSex, PagingClass pc) { try { //1. 数据源 var data = _baseService.EntitiesNoTrack<T_SysUser>().Where(u => u.delFlag == 0); #region 2. 条件搜索 //2. 条件搜索 //2.1 账号 if (!string.IsNullOrEmpty(userAccount)) { data = data.Where(u => u.userAccount.Contains(userAccount)); } //2.2 姓名 if (!string.IsNullOrEmpty(userRealName)) { data = data.Where(u => u.userRealName.Contains(userRealName)); } //2.3 电话 if (!string.IsNullOrEmpty(userPhone)) { data = data.Where(u => u.userPhone.Contains(userPhone)); } //2.4 性别 if (userSex != null && userSex != -1) { data = data.Where(u => u.userSex == userSex); } #endregion //3. 总条数 int totalCount = await data.CountAsync(); if (totalCount == 0) { return Json(new { total = 0, rows = "" }); } //4. 列排序 //此处默认是根据addTime降序排列，如需修改，在此处修改 pc.order = string.IsNullOrEmpty(pc.order) ? "Desc" : pc.order; pc.sort = string.IsNullOrEmpty(pc.sort) ? "addTime" : pc.sort; data = data.DataSorting(pc.sort, pc.order); //5. 分页 data = data.Skip((pc.page - 1) * pc.rows).Take(pc.rows); //6. 按需查询 var pageData = await (from a in data select new { a.id, a.userAccount, a.userRealName, a.userSex, a.userPhone, a.userRemark, a.addTime }).ToListAsync(); //7. 返回数据 //7.判断是否有数据 if (pageData.Count() == 0) { return Json(new { total = 0, rows = "" }); } else { return Json(new { total = totalCount, rows = pageData }); } } catch (Exception ex) { LogUtils.Error(ex); return Json(new { total = 0, rows = "" }); } } #endregion #region 02-新增用户【已修改】 /// <summary> /// 新增用户 /// </summary> /// <param name="tSysUser">用户实体</param> /// <returns></returns> [HttpPost] public async Task<IActionResult> AddUser(T_SysUser tSysUser) { try { var Num = await _baseService.EntitiesNoTrack<T_SysUser>().Where(u => u.userAccount == tSysUser.userAccount).CountAsync(); if (Num > 0) { return Json(new { status = "error", msg = "账号重复", data = "" }); } tSysUser.id = Guid.NewGuid().ToString("N"); tSysUser.userPwd = SecurityHelp.SHA(tSysUser.userPwd); tSysUser.addTime = DateTime.Now; tSysUser.delFlag = 0; var saveCount = await _baseService.AddAsync(tSysUser); if (saveCount > 0) { return Json(new { status = "ok", msg = "增加成功", data = "" }); } } catch (Exception ex) { LogUtils.Error(ex); } return Json(new { status = "error", msg = "增加失败", data = "" }); } #endregion #region 03-修改用户【已修改】 /// <summary> /// 修改用户 /// </summary> /// <param name="tSysUser">用户实体</param> /// <returns></returns> [HttpPost] public async Task<IActionResult> EditUser(T_SysUser tSysUser) { try { //判断账号是否重复 var count = await _baseService.EntitiesNoTrack<T_SysUser>().Where(u => u.id != tSysUser.id && u.userAccount == tSysUser.userAccount).CountAsync(); if (count > 0) { return Json(new { status = "error", msg = "账号重复", data = "" }); } else { var user1 = await _baseService.Entities<T_SysUser>().Where(u => u.id == tSysUser.id).FirstOrDefaultAsync(); user1.userAccount = tSysUser.userAccount; //判断是否修改过密码 user1.userPwd = tSysUser.userPwd.Equals("******") ? user1.userPwd : SecurityHelp.SHA(tSysUser.userPwd); user1.userRealName = tSysUser.userRealName; user1.userSex = tSysUser.userSex; user1.userPhone = tSysUser.userPhone; user1.userRemark = tSysUser.userRemark; var saveCount = await _baseService.ModifyAsync(user1); //修改的位置不需要判断是否大于0,可能是原值修改 return Json(new { status = "ok", msg = "修改成功", data = "" }); } } catch (Exception ex) { LogUtils.Error(ex); } return Json(new { status = "error", msg = "修改失败", data = "" }); } #endregion #region 04-删除用户【已修改】 /// <summary> /// 删除用户 /// </summary> /// <param name="delIds">删除用的用户编号ID数组</param> /// <returns></returns> [HttpPost] public async Task<IActionResult> DelUser(string[] delIds) { try { //1. 删除用户 await _baseService.DelByNoAsync<T_SysUser>(u => delIds.Contains(u.id)); //2. 删除用户和角色之间的关联 await _baseService.DelByNoAsync<T_SysUserRole>(u => delIds.Contains(u.userId)); int count = await _baseService.SaveChangeAsync(); if (count >= 0) { return Json(new { status = "ok", msg = "删除成功", data = "" }); } } catch (Exception ex) { LogUtils.Error(ex); } return Json(new { status = "error", msg = "删除失败", data = "" }); } #endregion #region 05-设置用户角色信息【已修改】 /// <summary> /// 设置用户角色信息 /// </summary> /// <param name="userId">用户编号ID</param> /// <param name="roleIds">角色编号ID数组</param> /// <returns></returns> [HttpPost] public async Task<IActionResult> SetUserRoles(string userId, string[] roleIds) { try { //1. 先把该用户对应的角色信息都删掉 await _baseService.DelByNoAsync<T_SysUserRole>(u => u.userId == userId); //2. 绑定新的角色信息 var nowTime = DateTime.Now; for (int i = 0; i < roleIds.Length; i++) { T_SysUserRole sysUserRole = new T_SysUserRole() { id = Guid.NewGuid().ToString("N"), userId = userId, roleId = roleIds[i], addTime = nowTime }; await _baseService.AddNoAsync(sysUserRole); } int saveCount = await _baseService.SaveChangeAsync(); if (saveCount >= 0) { return Json(new { status = "ok", msg = "设置成功", data = "" }); } } catch (Exception ex) { LogUtils.Error(ex); } return Json(new { status = "error", msg = "设置失败", data = "" }); } #endregion #region 06-导出Excel表格【已修改】 /// <summary> /// 导出Excel表格 /// </summary> /// <param name="userAccount">账号</param> /// <param name="userRealName">姓名</param> /// <param name="userPhone">电话</param> /// <param name="userSex">性别</param> /// <param name="pc">页面分页查询 封装实体</param> /// <param name="_hostingEnvironment">注入的IWebHostEnvironment对象</param> /// <returns></returns> public async Task<FileResult> DownLoadFileExcel(string userAccount, string userRealName, string userPhone, int? userSex, PagingClass pc, [FromServices] IWebHostEnvironment _hostingEnvironment) { try { List<T_SysUser> exportData = new List<T_SysUser>(); //获取需要导出的数据 { //1. 数据源 var data = _baseService.EntitiesNoTrack<T_SysUser>().Where(u => u.delFlag == 0); #region 2. 条件搜索 //2. 条件搜索 //2.1 账号 if (!string.IsNullOrEmpty(userAccount)) { data = data.Where(u => u.userAccount.Contains(userAccount)); } //2.2 姓名 if (!string.IsNullOrEmpty(userRealName)) { data = data.Where(u => u.userRealName.Contains(userRealName)); } //2.3 电话 if (!string.IsNullOrEmpty(userPhone)) { data = data.Where(u => u.userPhone.Contains(userPhone)); } //2.4 性别 if (userSex != null && userSex != -1) { data = data.Where(u => u.userSex == userSex); } #endregion //3. 列排序 //此处默认是根据addTime降序排列，如需修改，在此处修改 pc.order = string.IsNullOrEmpty(pc.order) ? "Desc" : pc.order; pc.sort = string.IsNullOrEmpty(pc.sort) ? "addTime" : pc.sort; data = data.DataSorting(pc.sort, pc.order); exportData = await data.ToListAsync(); } //4. 创建Excel相关的信息 //4.1 目录相关 var myPath = _hostingEnvironment.ContentRootPath + @"\DownLoad\Excel\"; if (!Directory.Exists(myPath)) { Directory.CreateDirectory(myPath); } string pathFileName = myPath + $"{DateTime.Now.ToString("yyyyMMddHHmmssffffff")}.xlsx"; FileInfo newFile = new FileInfo(pathFileName); if (newFile.Exists) { newFile.Delete(); newFile = new FileInfo(pathFileName); } //4.2 创建Excel ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial; using (ExcelPackage package = new ExcelPackage(newFile)) { //sheet名称 ExcelWorksheet workSheet = package.Workbook.Worksheets.Add("用户信息"); //设置标题 string[] titleArry = { "账号", "姓名", "性别", "电话", "说明", "添加时间" }; for (int i = 0; i < titleArry.Length; i++) { workSheet.Cells[1, i + 1].Value = titleArry[i]; } //设置列宽 int[] columnWidthArry = { 30, 15, 5, 30, 40, 30 }; for (int i = 0; i < columnWidthArry.Length; i++) { workSheet.Column(i + 1).Width = columnWidthArry[i]; } //单元格内容赋值 int row = 2; foreach (var item in exportData) { workSheet.Cells[row, 1].Value = item.userAccount; workSheet.Cells[row, 2].Value = item.userRealName; workSheet.Cells[row, 3].Value = item.userSex == 0 ? "女" : "男"; workSheet.Cells[row, 4].Value = item.userPhone; workSheet.Cells[row, 5].Value = item.userRemark; workSheet.Cells[row, 6].Value = item.addTime; workSheet.Cells[row, 6].Style.Numberformat.Format = "yyyy-MM-dd HH:mm:ss"; row++; } workSheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; //水平居中 await package.SaveAsync(); } //5. 返回下载内容 string fileName = DateTime.Now.ToString("yyyyMMddHHmmssffffff") + ".xlsx"; //展现给用户看的文件名 return PhysicalFile(pathFileName, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName); } catch (Exception ex) { LogUtils.Error(ex); return PhysicalFile("", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", ""); } } #endregion #region 07-得到可用的所有角色信息【已修改】 /// <summary> /// 得到可用的所有角色信息 /// </summary> /// <returns>角色信息Json字符串</returns> [HttpPost] public async Task<IActionResult> GetAllRoleList() { try { var rolesList = await _baseService.EntitiesNoTrack<T_SysRole>().Where(a => a.delFlag == 0).Select(a => new { roleId = a.id, roleName = a.roleName }).ToListAsync(); return Json(rolesList); } catch (Exception ex) { LogUtils.Error(ex); return Content(""); //没实际作用 } } #endregion #region 08-根据用户编号得到角色ID信息【已修改】 /// <summary> /// 根据用户编号得到角色ID信息 /// <param name="userId">用户编号ID</param> /// </summary> /// <returns></returns> [HttpPost] public async Task<IActionResult> GetRoleIdByUserId(string userId) { try { //1.查询该用户对应的角色id的信息 List<string> listRoleIds = await _baseService.EntitiesNoTrack<T_SysUserRole>() .Where(ur => ur.userId == userId) .Select(ur => ur.roleId) .ToListAsync(); return Json(listRoleIds); } catch (Exception ex) { LogUtils.Error(ex); return Content(""); //没实际作用 } } #endregion }
(3). 角色相关控制器-SysRoleApiController
[Area("AdminApi_Areas")] [Route("Api/[area]/[controller]/[action]")] [SkipLogin] public class SysRoleApiController : Controller { public IBaseService _baseService; public SysRoleApiController(IBaseService baseService) { this._baseService = baseService; } /*****************************接口调用**************************************/ #region 01-角色管理首页列表【已修改】 /// <summary> /// 角色管理首页列表 /// </summary> /// <param name="pc">页面分页查询 封装实体</param> /// <param name="tSysRole">角色实体</param> /// <returns></returns> [HttpPost] public async Task<IActionResult> GetRoleTableList(PagingClass pc, T_SysRole tSysRole) { try { //获取数据 var data = _baseService.EntitiesNoTrack<T_SysRole>().AsQueryable(); //2.筛选条件 //角色名称 if (!string.IsNullOrEmpty(tSysRole.roleName)) { data = data.Where(u => u.roleName.Contains(tSysRole.roleName)); } //添加日期 if (!string.IsNullOrEmpty(pc.operDateStart)) { var startTime = Convert.ToDateTime(pc.operDateStart); data = data.Where(u => u.addTime >= startTime); } if (!string.IsNullOrEmpty(pc.operDateEnd)) { var endTime = Convert.ToDateTime(pc.operDateEnd); data = data.Where(u => u.addTime <= endTime); } //3. 总条数 int totalCount = await data.CountAsync(); if (totalCount == 0) { return Json(new { total = 0, rows = "" }); } //4.列排序 //此处默认是根据addTime降序排列，如需修改，在此处修改 pc.order = string.IsNullOrEmpty(pc.order) ? "Desc" : pc.order; pc.sort = string.IsNullOrEmpty(pc.sort) ? "addTime" : pc.sort; data = data.DataSorting(pc.sort, pc.order); //5.分页 data = data.Skip((pc.page - 1) * pc.rows).Take(pc.rows); //6. 按需查询 var pageData = await (from a in data select new { a.id, a.roleName, a.roleRemark, a.addTime }).ToListAsync(); //7.判断是否有数据 if (pageData.Count() == 0) { return Json(new { total = 0, rows = "" }); } else { return Json(new { total = totalCount, rows = pageData }); } } catch (Exception ex) { LogUtils.Error(ex); return Json(new { total = 0, rows = "" }); } } #endregion #region 02-添加角色【已修改】 /// <summary> /// 添加角色 /// </summary> /// <param name="sysRoleInfor">角色实体</param> /// <returns></returns> [HttpPost] public async Task<IActionResult> AddRole(T_SysRole sysRoleInfor) { try { var Num =await _baseService.EntitiesNoTrack<T_SysRole>().Where(u => u.roleName == sysRoleInfor.roleName).CountAsync(); if (Num > 0) { return Json(new { status = "error", msg = "角色名重复", data = "" }); } sysRoleInfor.id = Guid.NewGuid().ToString("N"); sysRoleInfor.addTime = DateTime.Now; sysRoleInfor.delFlag = 0; _baseService.AddNo(sysRoleInfor); int flag = await _baseService.SaveChangeAsync(); if (flag > 0) { return Json(new { status = "ok", msg = "新增成功", data = "" }); } else { return Json(new { status = "error", msg = "新增失败", data = "" }); } } catch (Exception ex) { LogUtils.Error(ex); return Json(new { status = "error", msg = "新增失败" }); } } #endregion #region 03-修改角色【已修改】 /// <summary> /// 修改角色 /// </summary> /// <param name="tSysRole">数据库角色实体</param> /// <returns></returns> [HttpPost] public async Task<IActionResult> EditRole(T_SysRole tSysRole) { try { //1. 判断角色名是否重复 var count = await _baseService.EntitiesNoTrack<T_SysRole>().Where(u => u.roleName == tSysRole.roleName && u.id != tSysRole.id).CountAsync(); if (count > 0) { return Json(new { status = "error", msg = "角色名重复", data = "" }); } //2. 修改角色信息 var role =await _baseService.Entities<T_SysRole>().Where(u => u.id == tSysRole.id).FirstOrDefaultAsync(); role.roleName = tSysRole.roleName; role.roleRemark = tSysRole.roleRemark; int flag =await _baseService.SaveChangeAsync(); //修改的位置不需要判断是否大于0,可能是原值修改 return Json(new { status = "ok", msg = "修改成功", data = "" }); } catch (Exception ex) { LogUtils.Error(ex); return Json(new { status = "error", msg = "修改失败" }); } } #endregion #region 04-删除角色【已修改】 /// <summary> /// 删除角色 /// </summary> /// <param name="delIds">删除角色编号集合</param> /// <returns></returns> [HttpPost] public async Task<IActionResult> DelRole(List<string> delIds) { try { //删除角色 var listDel =await _baseService.EntitiesNoTrack<T_SysRole>().Where(u => delIds.Contains(u.id)).ToListAsync(); listDel.ForEach(item => { _baseService.DelNo(item); }); //删除用户关联 var listuserDel = await _baseService.EntitiesNoTrack<T_SysUserRole>().Where(u => delIds.Contains(u.roleId)).ToListAsync(); listuserDel.ForEach(item => { _baseService.DelNo(item); }); //删除角色权限之间的关联 var listPerDel = await _baseService.EntitiesNoTrack<T_SysRolePer>().Where(u => delIds.Contains(u.roleId)).ToListAsync(); listPerDel.ForEach(item => { _baseService.DelNo(item); }); int flag =await _baseService.SaveChangeAsync(); if (flag > 0) { return Json(new { status = "ok", msg = "删除成功" }); } else { return Json(new { code = "error", msg = "删除失败" }); } } catch (Exception ex) { LogUtils.Error(ex); return Json(new { status = "error", msg = "删除失败" }); } } #endregion #region 05-获取权限列表【已修改】 /// <summary> /// 获取权限列表 /// </summary> /// <param name="roleId">角色编号id</param> /// <returns></returns> [HttpPost] public async Task<IActionResult> GetAllPersForTree(string roleId) { try { List<T_SysPermisson> listPers =await _baseService.EntitiesNoTrack<T_SysPermisson>().Where(u => u.delFlag == 0).OrderBy(a => a.sortFlag).ToListAsync(); //一级权限 var onePerList = listPers.Where(u => u.parentId == "1").OrderBy(u => u.sortFlag).ToList(); //2. 获取该角色自身的权限id var ownPerIds =await _baseService.EntitiesNoTrack<T_SysRolePer>().Where(u => u.roleId == roleId).Select(u => u.perId).ToListAsync(); //递归调用 TreeInfor[] treeArry = GetAllTreeList(listPers, "1", ownPerIds); return Json(treeArry); } catch (Exception ex) { LogUtils.Error(ex); return Json(""); } } #endregion #region 06-设置权限【已修改】 /// <summary> /// 设置权限 /// </summary> /// <param name="roleId">角色编号id</param> /// <param name="perIds">权限编号id集合</param> /// <returns></returns> [HttpPost] public async Task<IActionResult> SetPer(string roleId, string[] perIds) { try { //1. 先把该角色对应的所有权限信息删掉 var delList =await _baseService.EntitiesNoTrack<T_SysRolePer>().Where<T_SysRolePer>(u => u.roleId == roleId).ToListAsync(); delList.ForEach(item => { _baseService.DelNo(item); }); //2. 绑定新的权限信息 var nowTime = DateTime.Now; for (int i = 0; i < perIds.Length; i++) { T_SysRolePer sysRolePer = new T_SysRolePer() { id = Guid.NewGuid().ToString("N"), roleId = roleId, perId = perIds[i], addTime = nowTime }; await _baseService.AddNoAsync(sysRolePer); } int count =await _baseService.SaveChangeAsync(); if (count > 0) { return Json(new { status = "ok", msg = "设置成功", data = "" }); } else { return Json(new { status = "error", msg = "设置失败", data = "" }); } } catch (Exception ex) { LogUtils.Error(ex); return Json(new { status = "error", msg = "设置失败", data = "" }); } } #endregion #region 07-导出Excel表格【已修改】 /// <summary> /// 导出Excel表格 /// </summary> /// <param name="modelParam">角色参数帮助类</param> /// <param name="_hostingEnvironment">注入本地环境变量</param> /// <returns></returns> public async Task<FileResult> DownLoadFileExcel(PagingClass pc, T_SysRole tSysRole, [FromServices] IWebHostEnvironment _hostingEnvironment) { try { List<T_SysRole> exportData = new List<T_SysRole>(); //1. 获取需要导出的数据 { //1.1获取数据 var data = _baseService.EntitiesNoTrack<T_SysRole>().AsQueryable(); //1.2 筛选条件 //角色名称 if (!string.IsNullOrEmpty(tSysRole.roleName)) { data = data.Where(u => u.roleName.Contains(tSysRole.roleName)); } //添加日期 if (!string.IsNullOrEmpty(pc.operDateStart)) { var startTime = Convert.ToDateTime(pc.operDateStart); data = data.Where(u => u.addTime >= startTime); } if (!string.IsNullOrEmpty(pc.operDateEnd)) { var endTime = Convert.ToDateTime(pc.operDateEnd); data = data.Where(u => u.addTime <= endTime); } //1.3.列排序 //此处默认是根据addTime降序排列，如需修改，在此处修改 pc.order = string.IsNullOrEmpty(pc.order) ? "Desc" : pc.order; pc.sort = string.IsNullOrEmpty(pc.sort) ? "addTime" : pc.sort; data = data.DataSorting(pc.sort, pc.order); exportData = await data.ToListAsync(); } //2. 创建Excel相关的信息 //2.1 目录相关 var myPath = _hostingEnvironment.ContentRootPath + @"\DownLoad\Excel\"; if (!Directory.Exists(myPath)) { Directory.CreateDirectory(myPath); } string pathFileName = myPath + $"{DateTime.Now.ToString("yyyyMMddHHmmssffffff")}.xlsx"; FileInfo newFile = new FileInfo(pathFileName); if (newFile.Exists) { newFile.Delete(); newFile = new FileInfo(pathFileName); } //2.2 创建Excel using (ExcelPackage package = new ExcelPackage(newFile)) { //sheet名称 ExcelWorksheet workSheet = package.Workbook.Worksheets.Add("角色信息"); //设置标题 string[] titleArry = { "角色名称", "角色备注", "添加时间" }; for (int i = 0; i < titleArry.Length; i++) { workSheet.Cells[1, i + 1].Value = titleArry[i]; } //设置列宽 int[] columnWidthArry = { 30, 45, 20 }; for (int i = 0; i < columnWidthArry.Length; i++) { workSheet.Column(i + 1).Width = columnWidthArry[i]; } //单元格内容赋值 int row = 2; foreach (var item in exportData) { workSheet.Cells[row, 1].Value = item.roleName; workSheet.Cells[row, 2].Value = item.roleRemark; workSheet.Cells[row, 3].Value = item.addTime; workSheet.Cells[row, 3].Style.Numberformat.Format = "yyyy-MM-dd HH:mm:ss"; row++; } workSheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; //水平居中 await package.SaveAsync(); } //3. 返回下载内容 string fileName = DateTime.Now.ToString("yyyyMMddHHmmssffffff") + ".xlsx"; //展现给用户看的文件名 return PhysicalFile(pathFileName, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName); } catch (Exception ex) { LogUtils.Error(ex); return PhysicalFile("", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", ""); } } #endregion #region 08-获取角色下用户【已修改】 /// <summary> /// 获取角色下用户 /// </summary> /// <param name="pc">页面分页查询 封装实体</param> /// <param name="tSysRole">角色实体</param> /// <returns></returns> [HttpPost] public async Task<IActionResult> GetRoleUser(PagingClass pc, T_SysRole tSysRole) { try { //1.获取角色下用户 var userRoleData = _baseService.EntitiesNoTrack<T_SysUserRole>().Where(u => u.roleId == tSysRole.id); var userData = _baseService.EntitiesNoTrack<T_SysUser>(); var data = from user in userData join roleu in userRoleData on user.id equals roleu.userId select new { id = roleu.id, userAccount = user.userAccount, userRealName = user.userRealName, userSex = user.userSex, userPhone = user.userPhone, userRemark = user.userRemark, addTime = user.addTime }; //2. 总条数 int totalCount =await data.CountAsync(); if (totalCount == 0) { return Json(new { total = 0, rows = "" }); } //3.分页 var pageData = await data.OrderBy(b => b.addTime).Skip((pc.page - 1) * pc.rows).Take(pc.rows).ToListAsync(); //4.判断是否有数据 if (pageData.Count() == 0) { return Json(new { total = 0, rows = "" }); } else { return Json(new { total = totalCount, rows = pageData }); } } catch (Exception ex) { LogUtils.Error(ex); return Json(new { total = 0, rows = "" }); } } #endregion #region 09-删除角色下用户【已修改】 /// <summary> /// 删除角色下用户 /// </summary> /// <param name="delIds">角色用户关联编号集合</param> /// <returns></returns> [HttpPost] public async Task<IActionResult> DelRoleUser(List<string> delIds) { try { //根据角色用户关联编号id进行删除关联数据 var data =await _baseService.EntitiesNoTrack<T_SysUserRole>().Where(u => delIds.Contains(u.id)).ToListAsync(); data.ForEach(item => { _baseService.DelNo(item); }); int flag =await _baseService.SaveChangeAsync(); if (flag > 0) { return Json(new { status = "ok", msg = "删除成功" }); } else { return Json(new { status = "error", msg = "删除失败" }); } } catch (Exception ex) { LogUtils.Error(ex); return Json(new { status = "error", msg = "删除失败" }); } } #endregion /**************************************方法封装******************************************/ #region 01-获取所有的树形菜单 /// <summary> /// 获取所有的树形菜单 /// </summary> /// <param name="pAllList">所有的权限信息</param> /// <param name="parentId">所需最高级的父级id</param> /// <param name="ownPerIds">当前角色的权限ID</param> /// <returns></returns> private TreeInfor[] GetAllTreeList(List<T_SysPermisson> pAllList, string parentId, List<string> ownPerIds) { var data = pAllList.Where(u => u.parentId == parentId).OrderBy(u => u.sortFlag).ToList(); List<TreeInfor> myNeedList = new List<TreeInfor>(); if (data.Count() > 0) { foreach (var item in data) { TreeInfor treeChild = new TreeInfor(); treeChild.id = item.id; treeChild.title = item.menuName; treeChild.children = GetChildList(pAllList, item.id, ownPerIds); if (treeChild.children.Length == 0) { treeChild.@checked = ownPerIds.Contains(item.id) ? true : false; //只要该节点下还有子菜单，就不能设置它，要根据子菜单下的选中状态来决定 } myNeedList.Add(treeChild); } return myNeedList.ToArray(); } else { return new TreeInfor[] { }; //返回空数组 } } #endregion #region 02-获取子菜单 /// <summary> /// 获取子菜单 /// </summary> /// <param name="pAllList">所有的权限信息</param> /// <param name="childPerId">子菜单权限Id</param> /// <param name="ownPerIds">当前角色的权限ID</param> /// <returns></returns> private TreeInfor[] GetChildList(List<T_SysPermisson> pAllList, string childPerId, List<string> ownPerIds) { var data = pAllList.Where(u => u.parentId == childPerId).OrderBy(u => u.sortFlag).ToList(); if (data.Count() > 0) { return GetAllTreeList(pAllList, childPerId, ownPerIds); } else { return new TreeInfor[] { }; //返回空数组 } } #endregion }
(4). 登录日志相关控制器-SysLoginLogApiController
[Area("AdminApi_Areas")] [Route("Api/[area]/[controller]/[action]")] [SkipLogin] /// <summary> /// 登录日志接口 /// </summary> public class SysLoginLogApiController : Controller { public IBaseService _baseService; public SysLoginLogApiController(IBaseService baseService) { this._baseService = baseService; } #region 01-获取登录日志信息(已修改) /// <summary> /// 获取登录日志信息 /// </summary> /// <param name="pc">表格信息</param> /// <param name="tSysLoginLog">日志实体,接收查询参数</param> /// <returns></returns> public async Task<JsonResult> GetLoginLogInfor(PagingClass pc, T_SysLoginLog tSysLoginLog) { try { //1. 数据源 var data = _baseService.EntitiesNoTrack<T_SysLoginLog>().Where(u => u.delFlag == 0); //2. 条件搜索 //2.1 登录账号 if (!string.IsNullOrEmpty(tSysLoginLog.userAccount)) { data = data.Where(u => u.userAccount.Contains(tSysLoginLog.userAccount)); } //2.2 登录ip if (!string.IsNullOrEmpty(tSysLoginLog.loginIp)) { data = data.Where(u => u.loginIp.Contains(tSysLoginLog.loginIp)); } //2.3 登录时间 if (!string.IsNullOrEmpty(pc.operDateStart)) { var startTime = Convert.ToDateTime(pc.operDateStart); data = data.Where(u => u.loginTime >= startTime); } if (!string.IsNullOrEmpty(pc.operDateEnd)) { var endTime = Convert.ToDateTime(pc.operDateEnd); data = data.Where(u => u.loginTime <= endTime); } //3. 总条数 int totalCount = await data.CountAsync(); if (totalCount == 0) { return Json(new { total = 0, rows = "" }); } //4.列排序 //此处默认是根据时间降序排列，如需修改，在此处修改 pc.order = string.IsNullOrEmpty(pc.order) ? "Desc" : pc.order; pc.sort = string.IsNullOrEmpty(pc.sort) ? "loginTime" : pc.sort; data = data.DataSorting(pc.sort, pc.order); //5.分页 data = data.Skip((pc.page - 1) * pc.rows).Take(pc.rows); //6. 按需查询 var pageData = await (from a in data select new { a.id, a.userAccount, a.loginIp, a.loginTime }).ToListAsync(); //7.判断是否有数据 if (pageData.Count() == 0) { return Json(new { total = 0, rows = "" }); } else { return Json(new { total = totalCount, rows = pageData }); } } catch (Exception ex) { LogUtils.Error(ex); return Json(new { total = 0, rows = "" }); } } #endregion #region 02-删除日志(已修改) /// <summary> /// 删除日志 /// </summary> /// <param name="delIds">删除用的id数组</param> /// <returns></returns> public async Task<JsonResult> DelLoginLog(string[] delIds) { try { int count = await _baseService.DelByAsync<T_SysLoginLog>(u => delIds.Contains(u.id)); if (count > 0) { return Json(new { status = "ok", msg = "删除成功", data = "" }); } return Json(new { status = "error", msg = "删除失败", data = "" }); } catch (Exception ex) { LogUtils.Error(ex); return Json(new { status = "error", msg = "删除失败", data = "" }); } } #endregion #region 03-导出Excel表格(已修改) /// <summary> /// 导出Excel表格 /// </summary> /// <param name="pc">表格必备参数</param> /// <param name="tSysLoginLog">日志实体，接收查询条件</param> /// <returns></returns> public async Task<FileResult> DownLoadFileExcel(PagingClass pc, T_SysLoginLog tSysLoginLog, [FromServices] IWebHostEnvironment _hostingEnvironment) { try { List<T_SysLoginLog> exportData = new List<T_SysLoginLog>(); //1. 获取需要导出的数据 { //1. 数据源 var data = _baseService.EntitiesNoTrack<T_SysLoginLog>().Where(u => u.delFlag == 0); //2. 条件搜索 //2.1 登录账号 if (!string.IsNullOrEmpty(tSysLoginLog.userAccount)) { data = data.Where(u => u.userAccount.Contains(tSysLoginLog.userAccount)); } //2.2 登录ip if (!string.IsNullOrEmpty(tSysLoginLog.loginIp)) { data = data.Where(u => u.loginIp.Contains(tSysLoginLog.loginIp)); } //2.3 登录时间 if (!string.IsNullOrEmpty(pc.operDateStart)) { var startTime = Convert.ToDateTime(pc.operDateStart); data = data.Where(u => u.loginTime >= startTime); } if (!string.IsNullOrEmpty(pc.operDateEnd)) { var endTime = Convert.ToDateTime(pc.operDateEnd); data = data.Where(u => u.loginTime <= endTime); } //4.列排序 //此处默认是根据addTime降序排列，如需修改，在此处修改 pc.order = string.IsNullOrEmpty(pc.order) ? "Desc" : pc.order; pc.sort = string.IsNullOrEmpty(pc.sort) ? "loginTime" : pc.sort; data = data.DataSorting(pc.sort, pc.order); exportData = await data.ToListAsync(); } //2. 创建Excel相关的信息 //2.1 目录相关 var myPath = _hostingEnvironment.ContentRootPath + @"\DownLoad\Excel\"; if (!Directory.Exists(myPath)) { Directory.CreateDirectory(myPath); } string pathFileName = myPath + $"{DateTime.Now.ToString("yyyyMMddHHmmssffffff")}.xlsx"; FileInfo newFile = new FileInfo(pathFileName); if (newFile.Exists) { newFile.Delete(); newFile = new FileInfo(pathFileName); } //2.2 创建Excel using (ExcelPackage package = new ExcelPackage(newFile)) { //sheet名称 ExcelWorksheet workSheet = package.Workbook.Worksheets.Add("登录日志"); //设置标题 string[] titleArry = { "登录名称", "登录ip", "登录时间" }; for (int i = 0; i < titleArry.Length; i++) { workSheet.Cells[1, i + 1].Value = titleArry[i]; } //设置列宽 int[] columnWidthArry = { 30, 45, 20 }; for (int i = 0; i < columnWidthArry.Length; i++) { workSheet.Column(i + 1).Width = columnWidthArry[i]; } //单元格内容赋值 int row = 2; foreach (var item in exportData) { workSheet.Cells[row, 1].Value = item.userAccount; workSheet.Cells[row, 2].Value = item.loginIp; workSheet.Cells[row, 3].Value = item.loginTime; workSheet.Cells[row, 3].Style.Numberformat.Format = "yyyy-MM-dd HH:mm:ss"; row++; } workSheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; //水平居中 await package.SaveAsync(); } //3. 返回下载内容 string fileName = DateTime.Now.ToString("yyyyMMddHHmmssffffff") + ".xlsx"; //展现给用户看的文件名 return PhysicalFile(pathFileName, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName); } catch (Exception ex) { LogUtils.Error(ex); return PhysicalFile("", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", ""); } } #endregion }
(5). 操作日志相关控制器 -SysOperateLogApiController
[Area("AdminApi_Areas")] [Route("Api/[area]/[controller]/[action]")] [SkipLogin] /// <summary> /// 操作日志接口 /// </summary> public class SysOperateLogApiController : Controller { public IBaseService _baseService; public SysOperateLogApiController(IBaseService baseService) { this._baseService = baseService; } #region 01-获取操作日志信息(已修改) /// <summary> /// 获取操作日志信息 /// </summary> /// <param name="pc">表格基础信息</param> /// <param name="tSysOperLog">日志实体,接收查询参数</param> /// <returns></returns> public async Task<JsonResult> GetOperLogInfor(PagingClass pc, T_SysOperLog tSysOperLog) { try { //1. 数据源 var data = _baseService.EntitiesNoTrack<T_SysOperLog>().Where(u => u.delFlag == 0); //2. 条件搜索 //2.1 操作人账号 if (!string.IsNullOrEmpty(tSysOperLog.userAccount)) { data = data.Where(u => u.userAccount.Contains(tSysOperLog.userAccount)); } //2.2 操作信息 if (!string.IsNullOrEmpty(tSysOperLog.operMessage)) { data = data.Where(u => u.operMessage.Contains(tSysOperLog.operMessage)); } //2.3 操作时间 if (!string.IsNullOrEmpty(pc.operDateStart)) { var startTime = Convert.ToDateTime(pc.operDateStart); data = data.Where(u => u.operTime >= startTime); } if (!string.IsNullOrEmpty(pc.operDateEnd)) { var endTime = Convert.ToDateTime(pc.operDateEnd); data = data.Where(u => u.operTime <= endTime); } //3. 总条数 int totalCount = await data.CountAsync(); if (totalCount == 0) { return Json(new { total = 0, rows = "" }); } //4.列排序 //此处默认是根据事件降序排列，如需修改，在此处修改 pc.sort = string.IsNullOrEmpty(pc.sort) ? "operTime" : pc.sort; pc.order = string.IsNullOrEmpty(pc.order) ? "Desc" : pc.order; data = data.DataSorting(pc.sort, pc.order); //5.分页 data = data.Skip((pc.page - 1) * pc.rows).Take(pc.rows); //6. 按需查询 var pageData = await (from a in data select new { a.id, a.userAccount, a.operMessage, a.operTime }).ToListAsync(); //7.判断是否有数据 if (pageData.Count() == 0) { return Json(new { total = 0, rows = "" }); } else { return Json(new { total = totalCount, rows = pageData }); } } catch (Exception ex) { LogUtils.Error(ex); return Json(new { total = 0, rows = "" }); } } #endregion #region 02-删除日志(已修改) /// <summary> /// 删除日志 /// </summary> /// <param name="delIds">删除用的id数组</param> /// <returns></returns> public async Task<JsonResult> DelOperateLog(string[] delIds) { try { int count = await _baseService.DelByAsync<T_SysOperLog>(u => delIds.Contains(u.id)); if (count > 0) { return Json(new { status = "ok", msg = "删除成功", data = "" }); } return Json(new { status = "error", msg = "删除失败", data = "" }); } catch (Exception ex) { LogUtils.Error(ex); return Json(new { status = "error", msg = "删除失败", data = "" }); } } #endregion #region 03-导出Excel表格(已修改) /// <summary> /// 导出Excel表格 /// </summary> /// <param name="pc">表格必备参数</param> /// <param name="tSysOperLog">日志实体，接收查询条件</param> /// <returns></returns> public async Task<FileResult> DownLoadFileExcel(PagingClass pc, T_SysOperLog tSysOperLog, [FromServices] IWebHostEnvironment _hostingEnvironment) { try { List<T_SysOperLog> exportData = new List<T_SysOperLog>(); //1. 获取需要导出的数据 { //1. 数据源 var data = _baseService.EntitiesNoTrack<T_SysOperLog>().Where(u => u.delFlag == 0); //2. 条件搜索 //2.1 操作人账号 if (!string.IsNullOrEmpty(tSysOperLog.userAccount)) { data = data.Where(u => u.userAccount.Contains(tSysOperLog.userAccount)); } //2.2 操作信息 if (!string.IsNullOrEmpty(tSysOperLog.operMessage)) { data = data.Where(u => u.operMessage.Contains(tSysOperLog.operMessage)); } //2.3 操作时间 if (!string.IsNullOrEmpty(pc.operDateStart)) { var startTime = Convert.ToDateTime(pc.operDateStart + " 00:00:00"); data = data.Where(u => u.operTime >= startTime); } if (!string.IsNullOrEmpty(pc.operDateEnd)) { var endTime = Convert.ToDateTime(pc.operDateEnd + " 23:59:59"); data = data.Where(u => u.operTime <= endTime); } //4.列排序 //此处默认是根据addTime降序排列，如需修改，在此处修改 pc.sort = string.IsNullOrEmpty(pc.sort) ? "operTime" : pc.sort; pc.order = string.IsNullOrEmpty(pc.order) ? "Desc" : pc.order; data = data.DataSorting(pc.sort, pc.order); exportData =await data.ToListAsync(); } //2. 创建Excel相关的信息 //2.1 目录相关 var myPath = _hostingEnvironment.ContentRootPath + @"\DownLoad\Excel\"; if (!Directory.Exists(myPath)) { Directory.CreateDirectory(myPath); } string pathFileName = myPath + $"{DateTime.Now.ToString("yyyyMMddHHmmssffffff")}.xlsx"; FileInfo newFile = new FileInfo(pathFileName); if (newFile.Exists) { newFile.Delete(); newFile = new FileInfo(pathFileName); } //2.2 创建Excel using (ExcelPackage package = new ExcelPackage(newFile)) { //sheet名称 ExcelWorksheet workSheet = package.Workbook.Worksheets.Add("操作日志"); //设置标题 string[] titleArry = { "操作人账号", "操作内容", "操作时间" }; for (int i = 0; i < titleArry.Length; i++) { workSheet.Cells[1, i + 1].Value = titleArry[i]; } //设置列宽 int[] columnWidthArry = { 30, 45, 20 }; for (int i = 0; i < columnWidthArry.Length; i++) { workSheet.Column(i + 1).Width = columnWidthArry[i]; } //单元格内容赋值 int row = 2; foreach (var item in exportData) { workSheet.Cells[row, 1].Value = item.userAccount; workSheet.Cells[row, 2].Value = item.operMessage; workSheet.Cells[row, 3].Value = item.operTime; workSheet.Cells[row, 3].Style.Numberformat.Format = "yyyy-MM-dd HH:mm:ss"; row++; } workSheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; //水平居中 await package.SaveAsync(); } //3. 返回下载内容 string fileName = DateTime.Now.ToString("yyyyMMddHHmmssffffff") + ".xlsx"; //展现给用户看的文件名 return PhysicalFile(pathFileName, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName); } catch (Exception ex) { LogUtils.Error(ex); return PhysicalFile("", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", ""); } } #endregion }
!
- 作 者 : Yaopengfei(姚鹏飞)
- 博客地址 : http://www.cnblogs.com/yaopengfei/
- 声 明1 : 如有错误，欢迎讨论，请勿谩骂^_^。
- 声 明2 : 原创博客请在转载时保留原文链接或在文章开头加上本人博客地址，否则保留追究法律责任的权利。
