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