public string SearchRole(PaginationData pd)
{
ListViewData <RoleInfo> view = new ListViewData <RoleInfo>();
view.PageData.PageIndex = pd.PageIndex;
view.PageData.PageSize = pd.PageSize;
view.PageData.PagintionJsFunction = "window.System.RoleTab.searchRole()";
view.PageData.OrderBy = pd.OrderBy;
string sql = @"SELECT A.*, CASE WHEN B.OrganizationName IS NOT NULL THEN STUFF(B.OrganizationName, 1, 1, '') ELSE '' END AS OrganizationName,
CASE WHEN C.UserName IS NOT NULL THEN STUFF(C.UserName, 1, 1, '') ELSE '' END AS UserName
FROM dbo.RoleInfo AS A
OUTER APPLY (SELECT ';' + IB.Name
FROM dbo.RoleToBusiness AS IA
INNER JOIN dbo.Organization AS IB ON IA.BusinessGuid = IB.UnitGuid
WHERE IB.Name > ''
ORDER BY IB.Code
FOR XML PATH('')) AS B(OrganizationName)
OUTER APPLY (SELECT ';' + IB.UserName
FROM dbo.RoleToBusiness AS IA
INNER JOIN dbo.UserInfo AS IB ON IA.BusinessGuid = IB.UserGuid
WHERE IB.UserName > ''
ORDER BY IB.UserCode
FOR XML PATH('')) AS C(UserName)";
view.List = new Pagintion(sql, null, view.PageData).ToList <RoleInfo>();
string table = UserControlExcutor.RenderUserControl("/System/UserControl/RoleList.ascx", view.List);
string pagination = UserControlExcutor.RenderUserControl("/System/CommonUserControl/Pagination.ascx", view.PageData);
return(JsonHelper.ToJson(new { table = table, pagination = pagination }));
}
public void ApplyOrderBy([IncludeDataSources(TestProvName.AllSqlServer2008Plus)] string context)
{
var data = PaginationData.Seed();
using (var db = GetDataContext(context))
using (var table = db.CreateLocalTable(data))
{
var query = table.Where(x => x.Id % 2 == 0).ApplyOrderBy(new [] { Tuple.Create("Id", false), Tuple.Create("Value", true) });
var pageSize = 20;
var pagination1 = query.Paginate(1, pageSize);
var pagination2 = query.Paginate(2, pageSize, true);
var byKey1 = query.GetPageByCondition(pageSize, x => x.Id == pagination1.Items[1].Id);
var byKey2 = query.GetPageByCondition(pageSize, x => x.Id == pagination2.Items[pageSize - 1].Id, true);
Assert.That(byKey1.Page, Is.EqualTo(pagination1.Page));
Assert.That(byKey1.TotalCount, Is.EqualTo(pagination1.TotalCount));
Assert.That(byKey2.Page, Is.EqualTo(pagination2.Page));
Assert.That(byKey2.TotalCount, Is.EqualTo(pagination2.TotalCount));
var pageNumber1 = query.GetPageNumberByCondition(pageSize, x => x.Id == pagination1.Items[1].Id);
var pageNumber2 = query.GetPageNumberByCondition(pageSize, x => x.Id == pagination2.Items[pageSize - 1].Id, true);
Assert.That(pageNumber1, Is.EqualTo(pagination1.Page));
Assert.That(pageNumber2, Is.EqualTo(pagination2.Page));
AreEqualWithComparer(pagination1.Items, byKey1.Items);
AreEqualWithComparer(pagination2.Items, byKey2.Items);
}
}
public IActionResult GetAll(
int?pageNumber, int?pageSize, string sortCol,
string sortDir, string searchTerms)
{
OperationResult result = _artistEntityBusiness.ListItems(
pageNumber, pageSize, sortCol, sortDir, searchTerms);
var paginationData = new PaginationData
{
OffsetFromZero = (int)result.ObjectsDictionary["offset"],
PageNumber = (int)result.ObjectsDictionary["pageIndex"],
PageSize = (int)result.ObjectsDictionary["sizeOfPage"],
OffsetUpperBound = (int)result.ObjectsDictionary["offsetUpperBound"],
TotalNumberOfRecords = (int)result.ObjectsDictionary["totalNumberOfRecords"],
TotalNumberOfPages = (int)result.ObjectsDictionary["totalNumberOfPages"],
SearchTermsCommaSeparated = result.ObjectsDictionary["searchTerms"].ToString(),
SortColumn = result.ObjectsDictionary["sortCol"].ToString(),
SortDirection = result.ObjectsDictionary["sortDir"].ToString()
};
var list = result.ObjectsDictionary["list"] as IEnumerable <Artist>;
var toReturn = Json(new { list, paginationData });
return(toReturn);
}
public IActionResult List(
int?pageNumber, int?pageSize, string sortCol,
string sortDir, string searchTerms)
{
return(ExecuteExceptionsHandledActionResult(() =>
{
OperationResult result = _customerBusiness.ListItems(
pageNumber, pageSize, sortCol ?? "CustomerId", sortDir, searchTerms);
var paginationData = new PaginationData
{
OffsetFromZero = (int)result.ObjectsDictionary["offset"],
PageNumber = (int)result.ObjectsDictionary["pageIndex"],
PageSize = (int)result.ObjectsDictionary["sizeOfPage"],
OffsetUpperBound = (int)result.ObjectsDictionary["offsetUpperBound"],
TotalNumberOfRecords = (int)result.ObjectsDictionary["totalNumberOfRecords"],
TotalNumberOfPages = (int)result.ObjectsDictionary["totalNumberOfPages"],
SearchTermsCommaSeparated = result.ObjectsDictionary["searchTerms"].ToString(),
SortColumn = result.ObjectsDictionary["sortCol"].ToString(),
SortDirection = result.ObjectsDictionary["sortDir"].ToString()
};
var list = result.ObjectsDictionary["list"] as IEnumerable <Customer>;
var toReturn = Json(new { list, paginationData });
return toReturn;
}));
}
public async Task TestsAsync([IncludeDataSources(TestProvName.AllSqlServer2008Plus)] string context)
{
var data = PaginationData.Seed();
using (var db = GetDataContext(context))
using (var table = db.CreateLocalTable(data))
{
var query = table.Where(x => x.Id % 2 == 0).OrderBy(x => x.Id).ThenByDescending(x => x.Value);
var pageSize = 20;
var pagination1 = await query.PaginateAsync(1, pageSize);
var pagination2 = await query.PaginateAsync(2, pageSize, true);
var byKey1 = await query.GetPageByConditionAsync(pageSize, x => x.Id == pagination1.Items[1].Id);
var byKey2 = await query.GetPageByConditionAsync(pageSize, x => x.Id == pagination2.Items[pageSize - 1].Id, true);
Assert.That(byKey1.Page, Is.EqualTo(pagination1.Page));
Assert.That(byKey1.TotalCount, Is.EqualTo(pagination1.TotalCount));
Assert.That(byKey2.Page, Is.EqualTo(pagination2.Page));
Assert.That(byKey2.TotalCount, Is.EqualTo(pagination2.TotalCount));
var pageNumber1 = await query.GetPageNumberByConditionAsync(pageSize, x => x.Id == pagination1.Items[1].Id);
var pageNumber2 = await query.GetPageNumberByConditionAsync(pageSize, x => x.Id == pagination2.Items[pageSize - 1].Id, true);
Assert.That(pageNumber1, Is.EqualTo(pagination1.Page));
Assert.That(pageNumber2, Is.EqualTo(pagination2.Page));
AreEqualWithComparer(pagination1.Items, byKey1.Items);
AreEqualWithComparer(pagination2.Items, byKey2.Items);
}
}
public (PaginationData paginationData, IEnumerable <Animal> animals) GetAnimals(int category = 0, int page = 1)
{
//Disallow a page smaller than 1.
if (page < 1)
{
throw new ArgumentOutOfRangeException(nameof(page), "The page cant be smaller than 1");
}
//Make sure requested category exists
if (category != 0 && petShop.Categories.FirstOrDefault(cat => cat.CategoryId == category) == null)
{
throw new ArgumentOutOfRangeException(nameof(category), "No such category exists!");
}
//Select relevant animals, and if needed filter by category.
IEnumerable <Animal> animals = petShop.Animals
.Include(animal => animal.Category)
.OrderByDescending(animal => animal.Comments.Count);
if (category != 0)
{
animals = animals.Where(animal => animal.Category.CategoryId == category);
}
//Check last page, and make sure we didn't pass it.
int lastPage = (animals.Count() - 1) / AnimalsPerPage + 1;
if (page > lastPage)
{
throw new ArgumentOutOfRangeException(nameof(page), "There is no data for the page requsted.");
}
//Return pagination data, and relevant animals.
PaginationData pagination = new PaginationData(page, lastPage);
return(pagination, animals.Skip((page - 1) * AnimalsPerPage).Take(AnimalsPerPage));
}
private IEnumerable <dynamic> Query(EntityMetadata entityMetadata, BindedEntityQuery query, SearchRequestDto searchDTO)
{
//TODO: hack to avoid garbage data and limit size of list queries.
var paginationData = PaginationData.GetInstance(searchDTO, entityMetadata);
var rows = GetDao(entityMetadata).FindByNativeQuery(query.Sql, query.Parameters, paginationData, searchDTO.QueryAlias);
return(rows);
}
public static PaginationData <TModel> ToPageData <TModel>(this PagedList <TModel> source)
where TModel : class
{
var result = new PaginationData <TModel>(source.Total);
result.List.AddRange(source);
return(result);
}
public PaginationModel(PaginationData paginationData, int totalCount)
{
this.PageNumber = (paginationData.PageNumber < totalCount)
? paginationData.PageNumber
: totalCount;
this.PageSize = paginationData.PageSize;
this.TotalCount = totalCount;
}
public void Offset_ValidParameters_ShouldHaveExpectedValue(int index, int size, int expected)
{
// arrange
var paginationModel = new PaginationData(index, size);
// act
var offset = paginationModel.Offset;
offset.Should().Be(expected);
}
public static PaginationData <TModel> ToPageData <TEntity, TModel>(this PagedList <TEntity> source)
where TEntity : class
where TModel : class
{
var models = source.MapTo <List <TEntity>, List <TModel> >();
var result = new PaginationData <TModel>(source.Total);
result.List.AddRange(models);
return(result);
}
public void ReturnsAPaginationStruct()
{
var pagination = PaginationData.From(_jToken);
Assert.Multiple(() =>
{
Assert.AreEqual(1, pagination.CurrentPage);
Assert.AreEqual(2, pagination.PerPage);
Assert.AreEqual(5, pagination.TotalEntries);
Assert.AreEqual(3, pagination.TotalPages);
});
}
public async Task <IServiceResult> ListAsync(PaginationData pagination)
{
try
{
var result = await _phoneRepository.ListAsync(pagination, x => x.Customer);
return(new SuccessResult <PaginationResult <Phone> >(result));
}
catch (Exception e)
{
_logger.LogError(e, "Error while listing phones");
return(new FailResult());
}
}
public IActionResult Index(int pageNumber = 1, QuotationSortType sortOrder = QuotationSortType.None, QuotationFilterData filterData = null)
{
var paginationData = new PaginationData(pageNumber);
var quotations = QuotationRepository.Get(sortOrder, filterData, paginationData).ToList();
var quotationModels = Mapper.Map <List <QuotationModel> >(quotations);
var quotationCount = QuotationRepository.GetTotalCount(sortOrder, filterData);
var paginationModel = new PaginationModel(paginationData, quotationCount);
var model = new QuotationViewModel(quotationModels, sortOrder, filterData, paginationModel);
return(View("Index", model));
}
public async Task <IServiceResult> ListAsync(PaginationData pagination)
{
try
{
var paginationResult = await _addressRepository.ListAsync(pagination);
return(new SuccessResult <PaginationResult <Address> >(paginationResult));
}
catch (Exception e)
{
_logger.LogError(e, "Error while listing addresses");
return(new FailResult());
}
}
public async Task <IServiceResult> ListAsync(PaginationData pagination)
{
try
{
var phones = await _localPhoneRepository.ListAsync(pagination, x => x.PhoneAddress);
return(new SuccessResult <PaginationResult <LocalPhone> >(phones));
}
catch (Exception e)
{
_logger.LogError(e, "Error while retrieving data");
return(new FailResult());
}
}
public async Task <IServiceResult> ListAsync(PaginationData pagination)
{
try
{
var customers = await _customerRepository.ListAsync(pagination);
return(new SuccessResult <PaginationResult <Customer> >(customers));
}
catch (Exception e)
{
_logger.LogError(e, "Error while retrieving data");
return(new FailResult());
}
}
protected async Task <PaginatedResult <T> > Paginate <T>
(
IQueryable <T> data,
PaginationData paginationData,
params Expression <Func <T, object> >[] keys
)
{
var skipRows = (paginationData.CurrentPage - 1) * paginationData.RowsPerPage;
IOrderedQueryable <T> orderedData = null;
for (int i = 0; i < keys.Count(); ++i)
{
if (i == 0)
{
orderedData = data.OrderBy(keys[i]);
}
else
{
orderedData = orderedData.ThenBy(keys[i]);
}
}
var paginated = orderedData.Skip(skipRows).Take(paginationData.RowsPerPage);
var result = new PaginatedResult <T>
{
Result = await paginated.ToListAsync(),
PaginationData = new PaginationDataResult()
};
var dataCount = await orderedData.CountAsync();
var pages = dataCount % paginationData.RowsPerPage > 0 ?
(dataCount / paginationData.RowsPerPage) + 1 :
dataCount / paginationData.RowsPerPage;
result.PaginationData.PageCount = pages;
result.PaginationData.CurrentPage = paginationData.CurrentPage;
result.PaginationData.RowsPerPage = paginationData.RowsPerPage;
result.PaginationData.RowCount = dataCount;
if (result.PaginationData.CurrentPage > result.PaginationData.PageCount && result.PaginationData.PageCount != 0)
{
result.PaginationData.CurrentPage = result.PaginationData.PageCount;
}
return(result);
}
public ListReponse(PaginatedList <TEntity> paginated, IList <TDTO> dtoList)
{
if (paginated.DataCount > 0)
{
Pagination = new PaginationData
{
CurrentPage = paginated.CurrentPage,
DataCount = paginated.DataCount,
PagesCount = paginated.PagesCount,
PageSize = paginated.PageSize
}
}
;
Data = dtoList;
}
public IList <T> FindByNativeQuery <T>(String queryst, PaginationData paginationData = null, params object[] parameters) where T : class
{
using (var session = SessionManager.Instance.OpenSession()) {
using (session.BeginTransaction()) {
var query = BuildQuery(queryst, parameters, session, true);
if (paginationData != null)
{
var pageSize = paginationData.PageSize;
query.SetMaxResults(pageSize);
query.SetFirstResult((paginationData.PageNumber - 1) * pageSize + 1);
}
return(query.List <T>());
}
}
}
private async Task <PaginationData> GetPaginationData(PaginationFilter filter, string route, int?totalRecordsCount = null)
{
var validFilter = new PaginationFilter(filter.PageNumber, filter.PageSize);
var totalRecords = await this._entityRepositry.GetAll().CountAsync();
var paginationData = new PaginationData()
{
TotalRecords = totalRecordsCount != null? (int)totalRecordsCount:totalRecords,
ValidFilter = validFilter,
Route = route,
UriService = _uriService,
};
return(paginationData);
}
protected void FixPageData(PaginationData paginationData)
{
if (paginationData.CurrentPage < 1)
{
paginationData.CurrentPage = 1;
}
if (paginationData.CurrentPage == 0)
{
paginationData.CurrentPage = 1;
}
if (paginationData.RowsPerPage == 0)
{
paginationData.RowsPerPage = 1;
}
}
public IActionResult GetCoursesForPagination(PaginationData data)
{
int page = data.RequestPage ?? 0;
_pageSize = data.PageLimit ?? 4;
var itemsToSkip =
page == 0 ? 0 : (page - 1) * _pageSize;
var container = new ApiContainer();
var filter = new CourseFilter()
{
Category = data.Category,
UserSearchInput = data.UserSearchInput
};
var courses = _data.GetCourses(filter).OrderBy(t => t.Id).Skip(itemsToSkip).Take(_pageSize);
if (!courses.Any())
{
return(NotFound());
}
var dataRez = new List <CourseDto>();
var model = courses.Select(c => _mapper.Map <Course, CourseDto>(c));
foreach (var course in courses)
{
var dtoModel = _mapper.Map <Course, CourseDto>(course);
dtoModel.StudentCount = course.Users.Count();
dtoModel.UserRaiting = 5;
dtoModel.Author =
string.IsNullOrEmpty(course.Author.Name) || string.IsNullOrEmpty(course.Author.Surname)
? course.Author.UserName
: $"{course.Author.Name} {course.Author.Surname}";
dataRez.Add(dtoModel);
}
container.Data = dataRez;
container.CurrentPage = page;
container.TotalRecord = _data.GetCourses(filter).Count();
container.PageLimit = _pageSize;
container.Fetched = courses.Count();
return(Ok(container));
}
public string SecondSearch(Guid parentGuid, PaginationData pd)
{
NavigationListViewData view = new NavigationListViewData();
view.pageDataChildren = new BaseFrameWork.Model.PaginationData();
view.pageDataChildren.PageIndex = pd.PageIndex;
view.pageDataChildren.PageSize = pd.PageSize;
view.pageDataChildren.PagintionJsFunction = "window.System.NavigationList.secondSearch()";
view.pageDataChildren.OrderBy = pd.OrderBy;
string sql = "select A.*, B.Name AS ParentName from dbo.Menu AS A INNER JOIN dbo.Menu AS B ON A.ParentGuid = B.MenuGuid where A.ParentGuid = @ParentGuid";
view.menuListChildren = new Pagintion(sql, new { ParentGuid = parentGuid }, view.pageDataChildren).ToList <Menu>();
string table = UserControlExcutor.RenderUserControl("/System/UserControl/NavigationListSecond.ascx", view);
string pagination = UserControlExcutor.RenderUserControl("/System/CommonUserControl/Pagination.ascx", view.pageDataChildren);
return(JsonHelper.ToJson(new { table = table, pagination = pagination }));
}
public string FirstSearch(PaginationData pd)
{
NavigationListViewData view = new NavigationListViewData();
view.pageDataParents = new BaseFrameWork.Model.PaginationData();
view.pageDataParents.PageIndex = pd.PageIndex;
view.pageDataParents.PageSize = pd.PageSize;
view.pageDataParents.PagintionJsFunction = "window.System.NavigationList.firstSearch()";
view.pageDataParents.OrderBy = pd.OrderBy;
string sql = "select * from dbo.Menu where ParentGuid IS NULL";
view.menuListParents = new Pagintion(sql, null, view.pageDataParents).ToList <Menu>();
string table = UserControlExcutor.RenderUserControl("/System/UserControl/NavigationListFirst.ascx", view);
string pagination = UserControlExcutor.RenderUserControl("/System/CommonUserControl/Pagination.ascx", view.pageDataParents);
return(JsonHelper.ToJson(new { table = table, pagination = pagination }));
}
public string Search(PaginationData pd)
{
PriceListViewData view = new PriceListViewData();
view.PageData = new BaseFrameWork.Model.PaginationData();
view.PageData.PageIndex = pd.PageIndex;
view.PageData.PageSize = pd.PageSize;
view.PageData.PagintionJsFunction = "window.System.PriceList.search()";
view.PageData.OrderBy = pd.OrderBy;
string sql = @"SELECT * FROM dbo.PriceList";
view.List = new Pagintion(sql, null, view.PageData).ToList <PriceList>();
string table = UserControlExcutor.RenderUserControl("/System/UserControl/PriceList.ascx", view);
string pagination = UserControlExcutor.RenderUserControl("/System/CommonUserControl/Pagination.ascx", view.PageData);
return(JsonHelper.ToJson(new { table = table, pagination = pagination }));
}
public string FirstSearch(PaginationData pd)
{
BusinessTypeListViewData view = new BusinessTypeListViewData();
view.pageDataParents = new BaseFrameWork.Model.PaginationData();
view.pageDataParents.PageIndex = pd.PageIndex;
view.pageDataParents.PageSize = pd.PageSize;
view.pageDataParents.PagintionJsFunction = "window.System.BusinessTypeList.firstSearch()";
view.pageDataParents.OrderBy = pd.OrderBy;
string sql = "SELECT * FROM BusinessType WHERE ParentID IS NULL";
view.menuListParents = new Pagintion(sql, null, view.pageDataParents).ToList <BusinessType>();
string table = UserControlExcutor.RenderUserControl("/System/UserControl/BusinessTypeListFirst.ascx", view);
string pagination = UserControlExcutor.RenderUserControl("/System/CommonUserControl/Pagination.ascx", view.pageDataParents);
return(JsonHelper.ToJson(new { table = table, pagination = pagination }));
}
public string SecondSearch(Guid parentID, PaginationData pd)
{
BusinessTypeListViewData view = new BusinessTypeListViewData();
view.pageDataChildren = new BaseFrameWork.Model.PaginationData();
view.pageDataChildren.PageIndex = pd.PageIndex;
view.pageDataChildren.PageSize = pd.PageSize;
view.pageDataChildren.PagintionJsFunction = "window.System.BusinessTypeList.secondSearch()";
view.pageDataChildren.OrderBy = pd.OrderBy;
string sql = "SELECT A.*, B.[Name] AS ParentName FROM BusinessType AS A INNER JOIN BusinessType AS B ON A.ParentID = B.TypeID WHERE B.TypeID = @TypeID";
view.menuListChildren = new Pagintion(sql, new { TypeID = parentID }, view.pageDataChildren).ToList <BusinessType>();
string table = UserControlExcutor.RenderUserControl("/System/UserControl/BusinessTypeListSecond.ascx", view);
string pagination = UserControlExcutor.RenderUserControl("/System/CommonUserControl/Pagination.ascx", view.pageDataChildren);
return(JsonHelper.ToJson(new { table = table, pagination = pagination }));
}
public async Task <PaginatedResult <LineTotalAndOrderQtySumPerDayDto, LineTotalAndOrderQtySumPerTimePeriodDto> > GetLineTotalAndOrderQuantitySumPerDay
(
DateTime startDate,
DateTime endDate,
PaginationData paginationData
)
{
var startData = _purchaseOrderDetailRepository.All();
var dataInDateRange = startData
.Where
(
x => x.DueDate >= startDate.Date &&
x.DueDate <= endDate.Date
).Select(x => new { x.DueDate, x.LineTotal, x.OrderQty });
var groupedByDayWithSum = dataInDateRange.GroupBy(x => x.DueDate)
.Select
(
grp =>
new LineTotalAndOrderQtySumPerDayDto
{
Date = grp.Key,
LineTotalSum = grp.Sum(a => a.LineTotal) ?? 0,
OrderQtySum = grp.Sum(b => b.OrderQty)
}
);
var paginated = await Paginate(groupedByDayWithSum, paginationData, x => x.Date);
var result = new PaginatedResult <LineTotalAndOrderQtySumPerDayDto, LineTotalAndOrderQtySumPerTimePeriodDto>
{
PaginationData = paginated.PaginationData,
Result = paginated.Result,
AdditionalData = new LineTotalAndOrderQtySumPerTimePeriodDto()
{
LineTotalSumPerPeriod = paginated.Result.Sum(x => x.LineTotalSum),
OrderQtySumPerPeriod = paginated.Result.Sum(x => x.OrderQtySum)
}
};
return(result);
}
public async Task <PaginationResult <TModel> > ListAsync(PaginationData pagination, params Expression <Func <TModel, object> >[] includes)
{
var filteredQuery = Set
.AsNoTracking()
.Where(x => x.IsActive);
var query = AggregateIncludes(filteredQuery, includes)
.OrderBy(x => x.Id)
.Skip((pagination.CurrentPage - 1) * pagination.PerPage)
.Take(pagination.PerPage);
var list = await query.ToListAsync();
return(new PaginationResult <TModel>
{
Elements = list,
Pagination = pagination,
Total = await filteredQuery.CountAsync()
});
}
