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