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