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