public async Task<IEnumerable<IDictionary<string, object>>> GenerateAsync(Criteria criteria)
{
var baseQuery = await GetReportQueryAsync(criteria);
var columnDetails = await GetColumnDetailsAsync(baseQuery);
var tsql = QueryGenerator.RenderReportQuery(baseQuery, criteria, columnDetails);
return await ExecuteResultsAsync(tsql);
}
public IEnumerable<Dictionary<string, object>> Generate(Criteria criteria)
{
var baseQuery = GetReportQuery(criteria);
var columnDetails = GetColumnDetails(baseQuery);
var tsql = QueryGenerator.RenderReportQuery(baseQuery, criteria, columnDetails);
return ExecuteResults(tsql);
}
private static string AddParameters(Criteria criteria)
{
var sb = new StringBuilder();
foreach (var kp in criteria.Parameters)
{
sb.AppendFormat(" {0}", AddCondition(kp.Condition));
if (kp.IsNot)
{
sb.Append(" NOT");
}
// Add Column reference
sb.AppendFormat(" [{0}]", kp.Key);
// Add Operator and Value(s)
// Handle IN clauses
if (kp.Operator == Operator.In)
{
sb.Append(" IN (");
if (kp.Values != null && kp.Values.Count > 0)
{
sb.Append(AddValues(kp.Values));
}
else
{
sb.Append(AddValue(kp.Value));
}
sb.Append(")");
}
else if (kp.Operator == Operator.Between)
{
if (kp.Values.Count == 2)
{
sb.AppendFormat(" BETWEEN '{0}' AND '{1}'", kp.Values[0], kp.Values[1]);
}
}
else
{
sb.AppendFormat(" {0} {1}", AddOperator(kp.Operator), AddValue(kp.Value));
}
}
return sb.ToString();
}
internal static string RenderReportQuery(string baseQuery, Criteria criteria, ICollection<ColumnDetail> columnDetails)
{
var qryFrom = columnDetails.Select(r => r.BaseTableName).FirstOrDefault();
var sb = new StringBuilder();
sb.AppendFormat("DECLARE @page INT = {0}, @pageSize INT = {1};", criteria.Page, criteria.PageSize);
sb.AppendLine(";WITH cteData AS");
sb.AppendLine("(");
sb.AppendLine("SELECT");
sb.AppendLine(AddRowNumber(columnDetails));
sb.AppendLine(AddColumns(criteria.Columns));
sb.AppendFormat("FROM [{0}]", qryFrom);
sb.AppendLine("WHERE (1=1)");
// Add Parameters
sb.Append(AddParameters(criteria));
sb.AppendLine(")");
sb.AppendLine("SELECT * FROM cteData WHERE (1=1)");
sb.AppendLine(" AND [RowId] BETWEEN ((@page - 1) * @pageSize) + 1 AND (@page * @pageSize)");
return sb.ToString();
}
private async Task<string> GetReportQueryAsync(Criteria criteria)
{
var query = String.Empty;
if (criteria.ReportId.HasValue)
{
var report = await m_db.GetReportByIdAsync(criteria.ReportId.Value);
if (report != null)
{
query = report.Query;
}
}
else if (String.IsNullOrEmpty(criteria.TableName) == false)
{
query = String.Format("SELECT * FROM {0}", criteria.TableName);
}
if(String.IsNullOrEmpty(query))
{
throw new Exception("Unable to materize base query");
}
return query;
}