Esempio n. 1
0
 public string QueryString(QueryParams queryParams)
 {
     var filter = queryParams.filter;
     var conditions = string.Join(" AND ", filter.Select(kvp => $"{kvp.Key}='{kvp.Value}'").Concat(new[] { "1=1" }) );
     var groupbys = string.Join(", ", filter.Select(kvp => kvp.Key).Concat(new[] { queryParams.nextPivot }));
     var aggregations = string.Join(", ", queryParams.valueFields.Select(field => $"SUM({field})")); ;
     var queryString = $"SELECT {queryParams.nextPivot}, {aggregations} FROM ({_sql}) WHERE {conditions} GROUP BY {groupbys}";
     return queryString;
 }
Esempio n. 2
0
        private void TestPivotTableQuery(IDataSet dataset, int expected)
        {
            var settings = new QuerySettings();
            var queryParams = new QueryParams()
            {
                filter = new Dictionary<string, string>(),
                valueFields = new List<string>() { "Sales", "Profit" },
                nextPivot = "Province"
            };

            var queryResults = dataset.Query(queryParams, settings);
            Assert.AreEqual(queryResults.Count, expected);
        }
Esempio n. 3
0
        public List<QueryResults> Query(QueryParams queryParams, QuerySettings settings)
        {
            var queryString = QueryString(queryParams);
            // use the querystring to actually do the query

            var results = DBUtils.Query(_database, queryString, r => {
                var key = r[0].ToString();
                var values = Enumerable.Range(0, queryParams.valueFields.Count)
                    .ToDictionary(i => queryParams.valueFields[i], i => double.Parse(r[i+1].ToString()));
                return new QueryResults(key,values);
                });

            return results.ToList();
        }
Esempio n. 4
0
        public List<QueryResults> Query(QueryParams queryParams, QuerySettings settings)
        {
            var filter = queryParams.filter;
            var valueFields = queryParams.valueFields;
            var limitedData = _data;
            if (settings.limit > 0)
                limitedData = limitedData.Take(settings.limit).ToList();

            return limitedData
                .Where(row =>
                {
                    return filter.ToList().All(kvp => row[kvp.Key] == kvp.Value);
                })
                .GroupBy(row => row[queryParams.nextPivot])
                .Select(grp => new QueryResults(grp.Key,valueFields.ToDictionary(f => f, f => grp.Sum(row => double.Parse(row[f])))))
                .ToList();
        }