public PagedSearchDTO <PersonVO> FindWtihPagedSearch(string name, string sortDirection, int pagesize, int page)
{
page = (page > 0) ? page - 1 : 0;
string query = @"select * from Person p WHERE 1 = 1 ";
if (!string.IsNullOrEmpty(name))
{
query = query + $"and p.firstname like '%{ name }%'";
}
query = query + $" ORDER BY p.firstname { sortDirection } LIMIT { pagesize } OFFSET { page }";
var persons = _repository.FindWithPagedSearch(query);
//string countQuery = @"select * from Person p WHERE 1 = 1 ";
//if (!string.IsNullOrEmpty(name))
// countQuery = countQuery + $"and p.firstname like '%{ name }%'";
var totalResults = _repository.FindWithPagedSearch(query).Count();
var ret = new PagedSearchDTO <PersonVO>
{
CurrentPage = page + 1,
List = _conveter.ParseList(persons),
PageSize = pagesize,
SortDirections = sortDirection,
TotalResults = totalResults
};
return(ret);
}
public async Task <PagedSearchDTO <ReserverDto> > FindWithPageSearchForUser(
string equipment,
string sortDirection,
int pageSize,
int page,
DateTime?date,
int status)
{
var sort = (!string.IsNullOrWhiteSpace(sortDirection) && !sortDirection.Equals("desc")) ? "asc" : "desc";
var size = (pageSize < 1) ? 10 : pageSize;
var offset = page > 0 ? (page - 1) * size : 0;
int userId = int.Parse(_user.Id);
var reservations = await _repository.FindWithPagedSearchForUser(userId, equipment, size, offset, date, status);
var totalResult = _repository.GetCountResUser(userId, equipment, date, status);
var searchPage = new PagedSearchDTO <ReserverDto>
{
CurrentPage = page,
List = _mapper.Map <List <ReserverDto> >(reservations),
PageSize = size,
SortDirections = sort,
TotalResults = totalResult
};
return(searchPage);
}
public async Task <PagedSearchDTO <UserDto> > FindWithPageSearch(string fullname, string sortDirection, int pageSize, int page)
{
var sort = (!string.IsNullOrWhiteSpace(sortDirection) && !sortDirection.Equals("desc")) ? "asc" : "desc";
var size = (pageSize < 1) ? 10 : pageSize;
var offset = page > 0 ? (page - 1) * size : 0;
var user = await _repository.FindWithPagedSearch(fullname, size, offset);
var totalResult = _repository.GetCount(fullname);
var userDto = _mapper.Map <List <UserDto> >(user);
var searchPage = new PagedSearchDTO <UserDto>
{
CurrentPage = page,
List = userDto,
PageSize = size,
SortDirections = sort,
TotalResults = totalResult
};
return(searchPage);
}
public async Task <PagedSearchDTO <EquipmentDto> > FindWithPageSearch(string name, string sortDirection, int pageSize, int page)
{
var sort = (!string.IsNullOrWhiteSpace(sortDirection) && !sortDirection.Equals("desc")) ? "asc" : "desc";
var size = (pageSize < 1) ? 10 : pageSize;
var offset = page > 0 ? (page - 1) * size : 0;
string query = @"select * from equipments e where 1 = 1 ";
if (!string.IsNullOrWhiteSpace(name))
{
query += $"and e.name like '%{name}%'";
}
query += $"order by e.name {sort} limit {size} offset {offset}";
string countQuery = @"select count(*) from equipments e where 1 = 1 ";
if (!string.IsNullOrWhiteSpace(name))
{
countQuery += $"and e.name like '%{name}%'";
}
var equipments = await _repository.FindWithPagedSearch(query);
int totalResult = _repository.GetCount(countQuery);
var result = _mapper.Map <List <EquipmentDto> >(equipments);
var searchPage = new PagedSearchDTO <EquipmentDto>
{
CurrentPage = page,
List = result,
PageSize = size,
SortDirections = sort,
TotalResults = totalResult
};
return(searchPage);
}