public IActionResult Index(int ChoosedPage) { long TotalCountOfPolicy = API.Policy.Count(); long ElementRowCountOnPage = 6; var Paginate = Pagination.Calculate(TotalCountOfPolicy, ChoosedPage, ElementRowCountOnPage); var Policies = API.Policy.TakeList(Convert.ToInt32(Paginate.CurrentPage), Convert.ToInt32(Paginate.RangeSize)); ViewData["Policies"] = MapEmegenlerPolicy_TO_PolictView(Policies); ViewData["Paginate"] = Paginate; return(View()); }
public async Task ToExcelDocument() { Pagination page = new Pagination(); page.Calculate(1); List <TestingEntity> entities = new List <TestingEntity>() { new TestingEntity() { } }; PaginatedCollection <TestingEntity> collection = new PaginatedCollection <TestingEntity>(page, entities); SpreadsheetDocument excel = await collection.ToExcelDocument(); Assert.True(!excel.IsNotValid()); }
/// <summary> /// Retrieves all the entities that match with the /// page request and query conditions specified /// and includes the column select collection /// during the selection /// </summary> /// <param name="pagination">Page request</param> /// <param name="columns">Column selection collection</param> /// <param name="conditions">Query conditions to apply</param> /// <returns>ComplexResponse of Paginated collection</returns> public async Task <ModelResponse <PaginatedCollection <T> > > GetRecords(Pagination pagination, string[] columns = null, List <QueryCondition <T> > conditions = null) { //Verify pagination instance if (pagination == null) { return(new ModelResponse <PaginatedCollection <T> >(false, @"The specified pagination instance is not valid.")); } //Initialize select columns if (columns.IsNotValid()) { columns = Columns; } //Verify integrity Columns (All the supplied columns must exists inside the current entity) if (!columns.ToList().TrueForAll(Columns.Contains)) { return(new ModelResponse <PaginatedCollection <T> >(false, @"The supplied columns does not exist in the current entity.")); } //Inicialize query conditions if (conditions.IsNotValid()) { conditions = new List <QueryCondition <T> >(); } else { //Validate query conditions integrity (All the supplied property-column query condition reference must exists inside the current entity) if (!conditions.Select(c => c.Property).ToList().TrueForAll(Columns.Contains)) { return(new ModelResponse <PaginatedCollection <T> >(false, @"The supplied columns does not exist in the current entity.")); } } ModelResponse <PaginatedCollection <T> > response; try { //Build count query using the given parameters StringBuilder sqlCount = new StringBuilder($@"Select Cast(count(*) as BigInt) as Total From [dbo].[{TableName}] "); //Conditions to apply to List <string> defaultConditions = new List <string>(); //Parameter list to pass List <SqlParameter> parameterList = new List <SqlParameter>(); //Include or not deleted records if (!pagination.IncludeAll) { defaultConditions.Add(@"[Deleted] Is Null"); } //Include keyword search inside searchable columns if (!pagination.KeyWords.IsNotValid() && !SearchColumns.IsNotValid()) { defaultConditions.Add($@"[{string.Join(@"] Like '%' + @SearchKeyWord + '%' Or [", SearchColumns)}] Like '%' + @SearchKeyWord + '%'"); parameterList.Add(new SqlParameter(@"@SearchKeyWord", pagination.KeyWords)); } //Include date range start if (!pagination.Start.IsNotValid()) { defaultConditions.Add(@"[Created] >= @StartAt"); parameterList.Add(new SqlParameter(@"@StartAt", pagination.Start)); } //Include date range end if (!pagination.End.IsNotValid()) { defaultConditions.Add(@"[Created] <= @EndAt"); parameterList.Add(new SqlParameter(@"@EntAt", pagination.End)); } //Adds any conditions if applies if (defaultConditions.Any()) { sqlCount.Append($@"Where ({string.Join(@") And (", defaultConditions)}) "); } //Command instance SqlServerCommand command = new SqlServerCommand(ConnectionString); //Get the user supplied conditions string userSqlConditions = conditions.ToSqlQuery(out SqlParameter[] userParameters); //Add the supplied user conditions if (!userSqlConditions.IsNotValid()) { //Add 'where' clausule if there is not any default condition if (!defaultConditions.Any()) { sqlCount.Append(@"Where "); } sqlCount .Append(@"And ") .Append(userSqlConditions); parameterList.AddRange(userParameters); } sqlCount.Append(@";"); //Params added SqlParameter[] parameters = parameterList.ToArray(); //Count results ModelResponse <List <ResultTable> > resultCount = await command.Query(sqlCount.ToString(), parameters); if (resultCount.Correct) { //Get the total records long total = resultCount.Model .First() .GetFirstResult <long>(@"Total"); //The sql select query result is succeded but it has not returned any record if (total.Equals(0)) { return(new ModelResponse <PaginatedCollection <T> >(false, @"The sql select query result is succeded but it has not returned any record.")); } //Calculate the pagination size by the given total records pagination.Calculate(total); //Sql select instance StringBuilder sqlSelect = new StringBuilder($@"Select [{string.Join(@"], [", columns)}] From [dbo].[{TableName}] "); //Apply the same conditions as the count sql if (defaultConditions.Any()) { sqlSelect.Append($@"Where ({string.Join(@") And (", defaultConditions)})"); } //Add the supplied user conditions if (!userSqlConditions.IsNotValid()) { //Add 'where' clausule if there is not any default condition if (!defaultConditions.Any()) { sqlSelect.Append(@"Where "); } sqlSelect .Append(@" And ") .Append(userSqlConditions); } //Skip and take records ordered by ascending id sqlSelect .Append($@"Order By [Id] Asc ") .Append($@"Offset {pagination.RequestedIndex * pagination.PageSize} Rows ") .Append($@"Fetch Next {pagination.PageSize} Rows Only;"); //Page result ModelResponse <List <ResultTable> > resultPage = await command.Query(sqlSelect.ToString(), parameters); if (resultPage.Correct) { //Create the page reference PaginatedCollection <T> page = new PaginatedCollection <T>(pagination, resultPage.Model.First().ToEntities <T>()); //Create response response = new ModelResponse <PaginatedCollection <T> >(page); } else { //The sql select query result is succeded but it has not returned any record response = new ModelResponse <PaginatedCollection <T> >(false, resultPage.Message); } } else { //The sql count query result is succeded but it has not returned any record response = new ModelResponse <PaginatedCollection <T> >(false, resultCount.Message); } } catch (Exception ex) { response = new ModelResponse <PaginatedCollection <T> >(ex); } return(response); }