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)
{
settings = settings ?? new QuerySettings();
return _data.Query(queryParams, settings);
}
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();
}
