public object HandleQueryByParameter(UserQuery userQuery, out string sql)
{
sql = "1=1";
if (userQuery.AccountEmailFilter.IsNotNullAndWhiteSpace())
{
sql += $"and account_email like %@account_email% ";
}
if (userQuery.IsActivated.HasValue)
{
sql += $"and is_activated = @is_activated ";
}
if (userQuery.IsBanned.HasValue)
{
sql += $"and is_banned = @is_banned ";
}
if (userQuery.IsModerated.HasValue)
{
sql += $"and is_moderated = @is_moderated ";
}
if (userQuery.Keyword.IsNotNullAndWhiteSpace())
{
sql += $"and username like %@username% or nickname like %@nickname% ";
}
if (userQuery.RoleName.IsNotNullAndWhiteSpace())
{
sql += $"and userid in (select userid from sc_users_in_roles where rolename = @rolename ";
}
if (userQuery.RegisterTimeLowerLimit.HasValue)
{
sql += $"and date_created >= @date_created_lower";
}
if (userQuery.RegisterTimeUpperLimit.HasValue)
{
sql += $"and date_created <= @date_created_upper ";
}
if (userQuery.UserRankLowerLimit.HasValue)
{
sql += $"and rank >= @rank_lower ";
}
if (userQuery.UserRankUpperLimit.HasValue)
{
sql += $"and rank <= @rank_upper ";
}
return(new
{
account_email = userQuery.AccountEmailFilter,
is_activated = userQuery.IsActivated.HasValue ? 1 : 0,
is_banned = userQuery.IsBanned.HasValue ? 1 : 0,
is_moderated = userQuery.IsModerated.HasValue ? 1 : 0,
username = userQuery.Keyword,
nickname = userQuery.Keyword,
rolename = userQuery.RoleName,
date_created_lower = userQuery.RegisterTimeLowerLimit,
date_created_upper = userQuery.RegisterTimeUpperLimit,
rank_lower = userQuery.UserRankLowerLimit,
rank_upper = userQuery.UserRankUpperLimit
});
}
/// <summary>
/// 根据筛选条件 （获取分页组件）
/// </summary>
/// <param name="userService"></param>
/// <param name="userQuery">筛选条件</param>
/// <param name="pageIndex">页码</param>
/// <param name="pageSize">显示条数</param>
/// <returns></returns>
public static PagingDataSet <User> GetUsers(this IUserService userService, UserQuery userQuery, int pageIndex, int pageSize)
{
IUserRepository repository = userService.GetUserRepository();
return(repository.GetUsers(userQuery, pageIndex, pageSize));
}
