public string Format(Filter filter, string originalQuery, out string totalRecordsQuery)
{
//{0}: sort column
//{1}: sort column order
//{2}: number of rows per page
//{3}: original query without order by
//{4}: where clause
//{5}: page number
string template = @"
SELECT * FROM
(
SELECT
(
(
(ROW_NUMBER() OVER (ORDER BY [OriginalTable].[{0}] {1}) - 1) -- sort column and sort column order
/ {2} -- number of rows per page
)
) + 1 as [PageNumber], * FROM (
-- Start Original Query
-- Restriction: Original Query cannot have an ORDER BY clause
{3}
-- End Original Query
) [OriginalTable] WHERE ({4}) -- where clause
) [PagedTable] WHERE [PagedTable].[PageNumber] IN ({5}) -- page number
";
string sortColumnOrder = filter.SortAscending ? "asc" : "desc";
string whereClause = GetWhereClause(filter, "OriginalTable");
totalRecordsQuery = "SELECT COUNT([OriginalTable].[" + filter.SortColumnName + "]) FROM (" + originalQuery + ") [OriginalTable] WHERE (" + whereClause + ")";
return string.Format(template, filter.SortColumnName, sortColumnOrder, filter.RowsPerPage, originalQuery, whereClause, filter.Page);
}
private string GetWhereClause(Filter filter, string tableName)
{
if (filter.FilterRows.Count == 0) return "0 = 0";
string whereClause = "(" + filter.FilterRows[0].FieldOperator.GetFilter(GetFullyQualifiedName(tableName, filter.FilterRows[0].FieldName), filter.FilterRows[0].FieldValue) + ")";
for (int i = 1; i < filter.FilterRows.Count; i++)
{
string filterTemplate = filter.FilterOperator.GetTemplate();
whereClause += string.Format(filterTemplate, filter.FilterRows[i].FieldOperator.GetFilter(GetFullyQualifiedName(tableName, filter.FilterRows[i].FieldName), filter.FilterRows[i].FieldValue));
}
return whereClause;
}
public Filter Parse(QueryStringInfo queryStringInfo)
{
Filter filter = new Filter
{
Page = queryStringInfo.page,
RowsPerPage = queryStringInfo.rows,
SortColumnName = queryStringInfo.sidx,
SortAscending = queryStringInfo.sord.ToUpper().Equals("ASC")
};
filter.FilterOperator = new FilterOperatorFactory().Build(queryStringInfo.filters.groupOp);
foreach (QueryStringFilterRule queryStringFilterRule in queryStringInfo.filters.rules)
{
filter.FilterRows.Add(new FilterRow
{
FieldName = queryStringFilterRule.field,
FieldValue = queryStringFilterRule.data,
FieldOperator = new FieldOperatorFactory().Build(queryStringFilterRule.op)
});
}
return filter;
}
