protected MdQueryResult CreateEmptyResult(MdQuery q) { var result = new MdQueryResult(); foreach (var dim in q.Dimensions) { var dimModel = model.Dimensions.Single(p => p.Name == dim); result.Headers.Add(new HeaderCell(dim, dimModel.Caption, dimModel.Type, HeaderType.DimensionHeader)); } foreach (var measure in q.Measures) { var measureModel = model.Measures.Single(p => p.Name == measure); result.Headers.Add(new HeaderCell(measure, measureModel.Caption, measureModel.Type, HeaderType.MeasureHeader)); } return(result); }
public virtual MdQueryResult Execute(MdQuery q) { var data = q.SourceData as IEnumerable; if (data != null) { var result = CreateEmptyResult(q); var groupSb = new StringBuilder(); var measureSb = new StringBuilder(); foreach (var dim in q.Dimensions) { var dimModel = this.model.Dimensions.Single(p => p.Name == dim); if (string.IsNullOrEmpty(dimModel.Expression)) { groupSb.AppendFormat("{0} as {0},", dim); measureSb.AppendFormat("Key.{0} as {0},", dim); } else { groupSb.AppendFormat("{0} as {1},", dimModel.Expression, dim); measureSb.AppendFormat("Key.{0} as {0},", dim); } } groupSb.Remove(groupSb.Length - 1, 1); var groupQueryString = string.Format("new ({0})", groupSb.ToString()); var groupQuery = data.AsQueryable().GroupBy(groupQueryString, "it"); foreach (var measure in q.Measures) { var measureData = model.Measures.Single(p => p.Name == measure); if (string.IsNullOrEmpty(measureData.Expression)) { measureSb.AppendFormat("{0}({1}) as {1},", AggregateFunc(measureData.Aggregation), measure); } else { measureSb.AppendFormat("{0}({1}) as {2},", AggregateFunc(measureData.Aggregation), measureData.Expression, measure); } } measureSb.Remove(measureSb.Length - 1, 1); var measureQueryString = string.Format("new ({0})", measureSb.ToString()); var measureQuery = groupQuery.Select(measureQueryString); var sortBuffer = new StringBuilder(); for (int i = 0; i < q.Sort.Count; i++) { var sort = q.Sort[i]; sortBuffer.AppendFormat("{0} {1},", sort.Column, sort.Direction.ToString()); } if (sortBuffer.Length > 0) { sortBuffer.Remove(sortBuffer.Length-1, 1); var sortExp = sortBuffer.ToString(); if (!string.IsNullOrEmpty(sortExp)) measureQuery = measureQuery.OrderBy(sortExp); } if (q.Skip > 0) measureQuery = measureQuery.Skip(q.Skip); if (q.Take > 0) measureQuery = measureQuery.Take(q.Take); foreach (var resultItem in measureQuery) { var row = new List<DataCell>(); foreach (var dim in q.Dimensions) { var value = getDynamicValue(resultItem, dim); var formatInfo = model.Dimensions.Single(p => p.Name == dim).DefaultFormat; var cell = new DataCell(value, formatInfo == null ? null: formatInfo.FormatString); row.Add(cell); } foreach (var measure in q.Measures) { var value = getDynamicValue(resultItem, measure); var formatInfo = model.Measures.Single(p => p.Name == measure).DefaultFormat; var cell = new DataCell(value, formatInfo == null ? null : formatInfo.FormatString); row.Add(cell); } result.Items.Add(row); } return result; } else return null; }
protected MdQueryResult CreateEmptyResult(MdQuery q) { var result = new MdQueryResult(); foreach (var dim in q.Dimensions) { var dimModel = model.Dimensions.Single(p => p.Name == dim); result.Headers.Add(new HeaderCell(dim, dimModel.Caption, dimModel.Type, HeaderType.DimensionHeader)); } foreach (var measure in q.Measures) { var measureModel = model.Measures.Single(p => p.Name == measure); result.Headers.Add(new HeaderCell(measure, measureModel.Caption, measureModel.Type, HeaderType.MeasureHeader)); } return result; }
static void Main(string[] args) { var file = "sales.csv"; var table = CsvEngine.CsvToDataTable(file, ';'); var columnTypes = new Dictionary<string, Type>(); columnTypes.Add("Year", typeof(string)); columnTypes.Add("Month", typeof(string)); columnTypes.Add("Quantity", typeof(int)); columnTypes.Add("VAT", typeof(decimal)); columnTypes.Add("Unit_Price", typeof(decimal)); columnTypes.Add("Total_Price", typeof(decimal)); var newTable = new DataTable(); newTable.TableName = "Product Sales"; foreach (DataColumn col in table.Columns) { var t = typeof(string); if (!columnTypes.TryGetValue(col.ColumnName, out t)) t= typeof(string); newTable.Columns.Add(col.ColumnName, t); } foreach (DataRow row in table.Rows) { newTable.Rows.Add(row.ItemArray); } var cubeModel = CubeModel.GenerateFromDataTable(newTable); cubeModel.Dimensions.RemoveAll(p => p.Name == "Year"); cubeModel.Dimensions.RemoveAll(p => p.Name == "Month"); cubeModel.Dimensions.Add(new DimensionModel("Year", DataType.Text)); cubeModel.Dimensions.Add(new DimensionModel("Month", DataType.Text)); cubeModel.Dimensions.Add(new DimensionModel("Quarter", DataType.Text) { Expression = "Year + \" \" + Month" }); cubeModel.Measures.Add(new MeasureModel("CalcField", DataType.Integer) { Expression = "Total_Price * 100" }); foreach (var item in cubeModel.Dimensions) { item.Caption = item.Name.Replace('_', ' '); } foreach (var item in cubeModel.Measures) { item.Caption = item.Name.Replace('_', ' '); } cubeModel.Measures.Single(p => p.Name == "Total_Price").Caption = "Total Price"; cubeModel.Measures.Single(p => p.Name == "Unit_Price").Caption = "Unit Price"; cubeModel.Measures.Single(p => p.Name == "Total_Price").DefaultFormat = new DataFormat() { FormatString = "C" }; cubeModel.Measures.Single(p => p.Name == "Unit_Price").DefaultFormat = new DataFormat() { FormatString = "C" }; var ds = new DataSet(); ds.Tables.Add(newTable); ds.WriteXml(cubeModel.Name + "-DataSet.xml"); DataEngine engine = new DataEngine(cubeModel); var query = new MdQuery(); query.SourceData = DynamicObject.ListFromDataTable(newTable); query.Dimensions.Add("Year"); query.Dimensions.Add("Month"); query.Dimensions.Add("Quarter"); query.Measures.Add("Unit_Price"); query.Measures.Add("Total_Price"); query.Measures.Add("CalcField"); query.Sort.Add(new JDash.InMemoryAnalytics.Engine.Sort("Quarter", JDash.InMemoryAnalytics.Engine.SortDirection.Descending)); query.Take = 5; query.Skip= 5; var result = engine.Execute(query); foreach (var header in result.Headers) { Console.Write(header.Caption + "\t"); } Console.WriteLine(); foreach (var row in result.Items) { foreach (var cell in row) { Console.Write(cell.FormattedValue + "\t"); } Console.WriteLine(); } Console.ReadLine(); }
public virtual MdQueryResult Execute(MdQuery q) { var data = q.SourceData as IEnumerable; if (data != null) { var result = CreateEmptyResult(q); var groupSb = new StringBuilder(); var measureSb = new StringBuilder(); foreach (var dim in q.Dimensions) { var dimModel = this.model.Dimensions.Single(p => p.Name == dim); if (string.IsNullOrEmpty(dimModel.Expression)) { groupSb.AppendFormat("{0} as {0},", dim); measureSb.AppendFormat("Key.{0} as {0},", dim); } else { groupSb.AppendFormat("{0} as {1},", dimModel.Expression, dim); measureSb.AppendFormat("Key.{0} as {0},", dim); } } groupSb.Remove(groupSb.Length - 1, 1); var groupQueryString = string.Format("new ({0})", groupSb.ToString()); var groupQuery = data.AsQueryable().GroupBy(groupQueryString, "it"); foreach (var measure in q.Measures) { var measureData = model.Measures.Single(p => p.Name == measure); if (string.IsNullOrEmpty(measureData.Expression)) { measureSb.AppendFormat("{0}({1}) as {1},", AggregateFunc(measureData.Aggregation), measure); } else { measureSb.AppendFormat("{0}({1}) as {2},", AggregateFunc(measureData.Aggregation), measureData.Expression, measure); } } measureSb.Remove(measureSb.Length - 1, 1); var measureQueryString = string.Format("new ({0})", measureSb.ToString()); var measureQuery = groupQuery.Select(measureQueryString); var sortBuffer = new StringBuilder(); for (int i = 0; i < q.Sort.Count; i++) { var sort = q.Sort[i]; sortBuffer.AppendFormat("{0} {1},", sort.Column, sort.Direction.ToString()); } if (sortBuffer.Length > 0) { sortBuffer.Remove(sortBuffer.Length - 1, 1); var sortExp = sortBuffer.ToString(); if (!string.IsNullOrEmpty(sortExp)) { measureQuery = measureQuery.OrderBy(sortExp); } } if (q.Skip > 0) { measureQuery = measureQuery.Skip(q.Skip); } if (q.Take > 0) { measureQuery = measureQuery.Take(q.Take); } foreach (var resultItem in measureQuery) { var row = new List <DataCell>(); foreach (var dim in q.Dimensions) { var value = getDynamicValue(resultItem, dim); var formatInfo = model.Dimensions.Single(p => p.Name == dim).DefaultFormat; var cell = new DataCell(value, formatInfo == null ? null: formatInfo.FormatString); row.Add(cell); } foreach (var measure in q.Measures) { var value = getDynamicValue(resultItem, measure); var formatInfo = model.Measures.Single(p => p.Name == measure).DefaultFormat; var cell = new DataCell(value, formatInfo == null ? null : formatInfo.FormatString); row.Add(cell); } result.Items.Add(row); } return(result); } else { return(null); } }