/// <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;
}
}