/// <summary> /// 获取角色信息List /// </summary> /// <returns></returns> public UserListData GetUserList(List <SearchCondition> searchList, int pageIndex, int pageSize, string orderBy) { try { UserListData result = new UserListData(); #region 初始化数据 string sqlString = @"SELECT DISTINCT u.UserInfoId, u.Name AS UserName, u.Account AS UserAccount, u.Email AS UserEmail, CASE WHEN u.IsDeleted = 0 THEN '可用' ELSE '禁用' END UserStatus FROM UserInfo u LEFT JOIN UserInRole r ON u.UserInfoId = r.UserInfoId LEFT JOIN RoleInfo uir ON r.RoleInfoId = uir.RoleInfoId WHERE 1 = 1 "; Dictionary <string, object> paramList = new Dictionary <string, object>(); #endregion #region 条件过滤 if (searchList != null && searchList.Count > 0) { searchList.ForEach(s => { switch (s.Type) { case Constants.COMMON_SEARCHCONDITIONTYPE_EQUAL: sqlString += string.Concat(" AND ", s.Table, s.Key, " = @", s.Key); paramList.Add(string.Concat("@", s.Key), s.Value); break; case Constants.COMMON_SEARCHCONDITIONTYPE_LIKE: sqlString += string.Concat(" AND ", s.Table, s.Key, " like @", s.Key); paramList.Add(string.Concat("@", s.Key), s.Value + "%"); break; case Constants.COMMON_SEARCHCONDITIONTYPE_RANGE: if (s.Key.ToLower().Contains("after")) { sqlString += string.Concat(" AND ", s.Table, s.Key.Replace("after", ""), " >= @", s.Key); paramList.Add(string.Concat("@", s.Key), Cast.ConToDateTime(s.Value).ToString("yyyy-MM-dd HH:mm:ss")); } else if (s.Key.ToLower().Contains("before")) { sqlString += string.Concat(" AND ", s.Table, s.Key.Replace("before", ""), " <= @", s.Key); paramList.Add(string.Concat("@", s.Key), Cast.ConToDateTime(s.Value).AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")); } else { throw new Exception("调用的参数错误,无法认定区间起始:" + s.Value); } break; case Constants.COMMON_SEARCHCONDITIONTYPE_CUSTOMER: break; } }); } #endregion #region 查询数据 DataTable dtResult = null; int recordCount = 0; if (pageSize > 0 && pageIndex > 0) { dtResult = _sql.Query(sqlString, paramList, orderBy, pageSize, pageIndex, out recordCount); } else { sqlString += orderBy; dtResult = _sql.Query(sqlString, paramList); } #endregion #region 构建结果 if (dtResult != null && dtResult.Rows.Count > 0) { if (pageSize > 0 && pageIndex > 0) { result.RecordCount = recordCount; result.RecordList = dtResult.ToModelList <UserListModel>(); result.RecordList.ForEach(item => { item.UserRoles = GetUserRolesByUserId(item.UserInfoId); DataTable dtLog = _sql.Query("SELECT TOP 1 LoginOn, ClientIp FROM tbl_loginlog WHERE UserInfoId = @userId Order By LoginOn DESC", new Dictionary <string, object> { { "@userId", item.UserInfoId } }); if (dtLog != null && dtLog.Rows.Count > 0) { item.LoginOn = Cast.ConToDateTimeString(dtLog.Rows[0]["LoginOn"]); item.LoginClientIp = Cast.ConToString(dtLog.Rows[0]["ClientIp"]); } try { item.LoginStatus = CacheHelper.Exists(Constants.Redis_Chat_Prefix + item.UserInfoId.ToUpper()) ? "在线" : "离线"; } catch (Exception) { item.LoginStatus = "未知"; } }); } else { result.ExportBuffString = ExcelHelper.DownloadExcel(dtResult); result.RecordCount = dtResult.Rows.Count; } } #endregion return(result); } catch (Exception ex) { _log.Error(ex); throw ex; } }
/// <summary> /// 获取定时任务列表数据 /// </summary> /// <param name="searchList"></param> /// <returns></returns> public List <JobInfoListModel> GetQuartzList(List <SearchCondition> searchList) { try { List <JobInfoListModel> result = new List <JobInfoListModel>(); #region 初始化数据 string sqlString = @"SELECT * FROM JobInfo WHERE IsDeleted = 0 "; Dictionary <string, object> paramList = new Dictionary <string, object>(); #endregion #region 条件过滤 if (searchList != null && searchList.Count > 0) { searchList.ForEach(s => { switch (s.Type) { case Constants.COMMON_SEARCHCONDITIONTYPE_EQUAL: sqlString += string.Concat(" AND ", s.Table, s.Key, " = @", s.Key); paramList.Add(string.Concat("@", s.Key), s.Value); break; case Constants.COMMON_SEARCHCONDITIONTYPE_LIKE: sqlString += string.Concat(" AND ", s.Table, s.Key, " like @", s.Key); paramList.Add(string.Concat("@", s.Key), s.Value + "%"); break; case Constants.COMMON_SEARCHCONDITIONTYPE_RANGE: if (s.Key.ToLower().Contains("after")) { sqlString += string.Concat(" AND ", s.Table, s.Key.Replace("after", ""), " >= @", s.Key); paramList.Add(string.Concat("@", s.Key), Cast.ConToDateTime(s.Value).ToString("yyyy-MM-dd HH:mm:ss")); } else if (s.Key.ToLower().Contains("before")) { sqlString += string.Concat(" AND ", s.Table, s.Key.Replace("before", ""), " <= @", s.Key); paramList.Add(string.Concat("@", s.Key), Cast.ConToDateTime(s.Value).AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")); } else { throw new Exception("调用的参数错误,无法认定区间起始:" + s.Value); } break; case Constants.COMMON_SEARCHCONDITIONTYPE_CUSTOMER: break; } }); } #endregion #region 查询数据 sqlString += " ORDER BY JobGroup ASC, JobName ASC"; DataTable dtResult = _sql.Query(sqlString, paramList); #endregion #region 构建结果 if (dtResult != null && dtResult.Rows.Count > 0) { result = dtResult.ToModelList <JobInfoListModel>(); } #endregion return(result); } catch (Exception ex) { _log.Error(ex); throw ex; } }
/// <summary> /// 获取附件列表信息 /// </summary> /// <param name="searchList"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public AttachmentListData GetAttachmentList(List <SearchCondition> searchList, int pageIndex, int pageSize) { try { AttachmentListData result = new AttachmentListData(); #region 初始化数据 string sqlString = @"SELECT * FROM Attachment WHERE IsDeleted = 0"; Dictionary <string, object> paramList = new Dictionary <string, object>(); #endregion #region 条件过滤 if (searchList != null && searchList.Count > 0) { searchList.ForEach(s => { switch (s.Type) { case Constants.COMMON_SEARCHCONDITIONTYPE_EQUAL: sqlString += string.Concat(" AND ", s.Table, s.Key, " = @", s.Key); paramList.Add(string.Concat("@", s.Key), s.Value); break; case Constants.COMMON_SEARCHCONDITIONTYPE_LIKE: sqlString += string.Concat(" AND ", s.Table, s.Key, " like @", s.Key); paramList.Add(string.Concat("@", s.Key), s.Value + "%"); break; case Constants.COMMON_SEARCHCONDITIONTYPE_RANGE: if (s.Key.ToLower().Contains("after")) { sqlString += string.Concat(" AND ", s.Table, s.Key.Replace("after", ""), " >= @", s.Key); paramList.Add(string.Concat("@", s.Key), Cast.ConToDateTime(s.Value).ToString("yyyy-MM-dd HH:mm:ss")); } else if (s.Key.ToLower().Contains("before")) { sqlString += string.Concat(" AND ", s.Table, s.Key.Replace("before", ""), " <= @", s.Key); paramList.Add(string.Concat("@", s.Key), Cast.ConToDateTime(s.Value).AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")); } else { throw new Exception("调用的参数错误,无法认定区间起始:" + s.Value); } break; case Constants.COMMON_SEARCHCONDITIONTYPE_CUSTOMER: break; } }); } #endregion #region 查询数据 DataTable dtResult = null; int recordCount = 0; if (pageSize > 0 && pageIndex > 0) { dtResult = _sql.Query(sqlString, paramList, " ORDER BY CreatedOn DESC", pageSize, pageIndex, out recordCount); } else { sqlString += " ORDER BY CreatedOn DESC"; dtResult = _sql.Query(sqlString, paramList); } #endregion #region 构建结果 if (dtResult != null && dtResult.Rows.Count > 0) { result.RecordCount = recordCount; result.RecordList = dtResult.ToModelList <AttachmentListModel>(); } #endregion return(result); } catch (Exception ex) { _log.Error(ex); throw ex; } }