public JsonResult GetAggregate(DataQueryOptions options) { (var source, var conn, var groupers, var aggregators) = InitializeOptions(options); if (groupers.Count == 0 && aggregators.Count == 0) { return(null); } var sql = PrepareSQL(source, options, groupers, aggregators); var sqlText = string.Join("\n", new string[] { sql.select, sql.from, sql.where, sql.group, sql.having, sql.order }.Where(s => s.NotBlank())); return(ExecuteSQL(conn, sqlText)); }
public JsonResult GetDetail(DataQueryOptions options, string group) { (var source, var conn, var groupers, var aggregators) = InitializeOptions(options); if (groupers.Count == 0 && aggregators.Count == 0) { return(null); } var criteriaObject = Newtonsoft.Json.Linq.JObject.Parse(group); var criteria = new Dictionary <string, string>(); foreach (var c in source.Columns.Where(c => c.CanGroup)) { if (criteriaObject[c.Code] != null) { criteria.Add(c.Code, criteriaObject[c.Code].ToString()); } } if (!criteria.Any()) { return(null); } var sql = PrepareSQL(source, options, groupers, aggregators); var sqlSelect = $"SELECT {string.Join(",", source.Columns.Select(c => $"[{c.Code}]"))}, 1 AS [__RowCount]"; var criteriaWhere = string.Join(" AND ", criteria.Select(c => $"[{c.Key}] = '{c.Value}'")); var sqlWhere = sql.where.NotBlank() ? $"{sql.where} AND ({criteriaWhere})" : $"WHERE ({criteriaWhere})"; var sqlText = string.Join("\n", new string[] { sqlSelect, sql.from, sqlWhere }); return(ExecuteSQL(conn, sqlText)); }
protected (SourceDefinition source, ConnectionDefinition conn, List <string> groupers, Dictionary <string, string> aggregators) InitializeOptions(DataQueryOptions options) { var groupers = new List <string>(); var aggregators = new Dictionary <string, string>(); var config = GetConfiguration(); var source = config.Sources.FirstOrDefault(x => x.Code == options.Source); if (source == null) { throw new RFLogicException(this, $"Unable to find source {options.Source}"); } var conn = config.Connections.FirstOrDefault(x => x.Code == source.ConnectionCode); if (conn == null) { throw new RFLogicException(this, $"Unable to find connection {source.ConnectionCode}"); } if (options == null) { return(source, conn, groupers, aggregators); } var breakdown = new SortedSet <string>((options.Breakdown ?? new string[0]).Union(source.Columns.Where(c => c.CanGroup && c.Mandatory).Select(c => c.Code))); var include = new SortedSet <string>(options.Data ?? new string[0]); foreach (var c in source.Columns) { if (c.CanGroup && breakdown.Contains(c.Code)) { groupers.Add($"[{c.Code}]"); } else if (c.Aggregator.NotBlank() && include.Contains(c.Code)) { if (c.Aggregator.Equals("Sum", StringComparison.InvariantCultureIgnoreCase)) { aggregators.Add($"[{c.Code}]", $"{c.Aggregator}(ISNULL([{c.Code}], 0))"); } else if (c.Aggregator.Equals("Avg", StringComparison.InvariantCultureIgnoreCase) || c.Aggregator.Equals("Max", StringComparison.InvariantCultureIgnoreCase) || c.Aggregator.Equals("Min", StringComparison.InvariantCultureIgnoreCase)) { aggregators.Add($"[{c.Code}]", $"{c.Aggregator}([{c.Code}])"); } } } return(source, conn, groupers, aggregators); }
protected (string select, string from, string where, string group, string having, string order) PrepareSQL(SourceDefinition source, DataQueryOptions options, List <string> groupers, Dictionary <string, string> aggregators) { var sqlSelect = $"SELECT\n{string.Join(",\n", groupers.Union(aggregators.Select(a => $"{a.Value} AS {a.Key}")).Select(s => $"\t{s}"))},\n\tCOUNT(*) as [__RowCount]"; var sqlFrom = $"FROM\n\t{source.Code}"; var sqlFilter = options.SqlFilter() ?? String.Empty; var sqlNonZero = aggregators.Any() ? string.Join(" OR ", aggregators.Select(a => $"{a.Key} IS NOT NULL")) : String.Empty; var sqlWhere = sqlFilter.NotBlank() || sqlNonZero.NotBlank() ? $"WHERE\n\t({string.Join(" AND\n\t", new string[] { sqlFilter, sqlNonZero }.Where(s => s.NotBlank()).Select(s => $" ({ s }) "))})" : String.Empty; var sqlGroup = groupers.Any() ? $"GROUP BY\n\t{string.Join(",", groupers)}" : String.Empty; var sqlHaving = options.ExcludeZero && aggregators.Any() ? $"HAVING\n\t{string.Join(" OR\n\t", aggregators.Select(a => $"{a.Value} <> 0"))}" : String.Empty; var sqlOrder = groupers.Any() ? $"ORDER BY\n\t{string.Join(",", groupers)}" : String.Empty; return(sqlSelect, sqlFrom, sqlWhere, sqlGroup, sqlHaving, sqlOrder); }