/// <summary>
/// Возврат списка пользователей с пагинацией, фильтром или сортировкой
/// </summary>
/// <param name="skip"></param>
/// <param name="limit"></param>
/// <returns></returns>
public async Task <UserPaging> GetAll(int skip, int limit, string filter)
{
using (var conn = new SqlConnection(AppSettings.ConnectionString))
{
string sqlfilter, sql, sqlc;
CreateFilter(filter, out sqlfilter, out sql, out sqlc);
var result = (await conn.QueryAsync <User, Brigade, User>(sql,
(user, brigade) => {
user.Brigade = brigade;
return(user);
},
new { skip = skip, limit = limit })
).ToArray();
foreach (var item in result)
{
item.PasswordHash = null;
}
var count = conn.ExecuteScalar <int>(sqlc);
var output = new UserPaging()
{
SortOptions = new SortOptions(),
Data = result,
Total = count
};
return(output);
}
}
/// <summary>
/// Возврат списка пользователей с пагинацией
/// </summary>
/// <param name="skip"></param>
/// <param name="limit"></param>
/// <returns></returns>
public async Task <UserPaging> GetAll(int skip, int limit)
{
using (var conn = new SqlConnection(AppSettings.ConnectionString))
{
var sql = Sql.SqlQueryCach["Users.AllPaging"];
var result = (await conn.QueryAsync <User, Brigade, User>(sql,
(user, brigade) => {
user.Brigade = brigade;
return(user);
},
new { skip = skip, limit = limit })
).ToArray();
foreach (var item in result)
{
item.PasswordHash = null;
}
var sqlc = Sql.SqlQueryCach["Users.CountAll"];
var count = conn.ExecuteScalar <int>(sqlc);
var output = new UserPaging()
{
Data = result,
Total = count
};
return(output);
}
}
public async Task <UserPaging> GetAllWithLogin(int skip, int limit, string search = null, string sort = null)
{
using (var conn = new SqlConnection(AppSettings.ConnectionString))
{
SortOptions sortOptions = new SortOptions();
string orderBy = "ORDER BY id";
if (sort != null)
{
sortOptions = JsonConvert.DeserializeObject <SortOptions>(sort);
if (sortOptions.Column != null)
{
orderBy = $"ORDER BY {sortOptions.Column} {(sortOptions.Direction == 1 ? "DESC" : "ASC")}";
}
}
var sql = Sql.SqlQueryCach["Users.AllWithLoginPaging"];
if (search != null)
{
sql = sql.Replace("--and", $"AND (Login LIKE '%' + @search + '%' OR Name LIKE '%' + @search + '%') ");
}
sql += $"{Environment.NewLine} {orderBy} OFFSET @skip ROWS FETCH NEXT @limit ROWS ONLY;";
var result = (await conn.QueryAsync <User>(sql, new { skip = skip, limit = limit, search })).ToArray();
var sqlRoleR = new UserRoleRepository();
foreach (var item in result)
{
item.PasswordHash = null;
item.Role = sqlRoleR.GetById(item.RoleId);
}
sqlRoleR.Dispose();
var sqlc = Sql.SqlQueryCach["Users.CountAllWithLogin"];
if (search != null)
{
sqlc = sqlc.Replace("--and", $"AND (Login LIKE '%' + @search + '%' OR Name LIKE '%' + @search + '%') ");
}
var count = conn.ExecuteScalar <int>(sqlc, new { search });
var output = new UserPaging()
{
SortOptions = sortOptions,
Data = result,
Total = count
};
return(output);
}
}
