Ejemplo n.º 1
0
        /// <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;
            }
        }
Ejemplo n.º 2
0
        /// <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;
            }
        }
Ejemplo n.º 3
0
        /// <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;
            }
        }