public static string BuildOlapStage(string tableName, OlapStage stage)
{
StringBuilder sb = new StringBuilder();
sb.Append(Select(stage.aggregations.Union(stage.left).Union(stage.top).ToArray(), tableName));
if (stage.left.Length > 0 || stage.top.Length > 0)
sb.AppendFormat(" GROUP BY {0}", string.Join(", ", stage.left.Union(stage.top)));
return sb.ToString();
}
public OlapView(string stringKey, SQLiteConnection conn, string tableName, OlapStage stage)
{
m_conn = conn;
m_tableName = tableName;
m_stage = stage;
SetResourceReference(StageNameProperty, stringKey);
RefreshView();
InitializeComponent();
}
public static Grid BuildOlapView(SQLiteConnection conn, string tableName, OlapStage stage)
{
using (TempTable olapStage = new TempTable(conn, QueryBuilder.BuildOlapStage(tableName, stage)))
{
OlapDimensionsTree leftTree = BuildDimensionsTree(olapStage, stage.left);
OlapDimensionsTree topTree = BuildDimensionsTree(olapStage, stage.top);
bool bLeftTotals = true;
bool bTopTotals = true;
bool bGrandTotals = true;
Grid grid = new Grid();
grid.ShowGridLines = true;
foreach (var d in stage.left)
grid.ColumnDefinitions.Add(CreateColumn());
if (bLeftTotals || bGrandTotals)
grid.ColumnDefinitions.Add(CreateColumn());
foreach (var d in stage.top)
grid.RowDefinitions.Add(CreateRow());
if (bTopTotals || bGrandTotals)
grid.RowDefinitions.Add(CreateRow());
leftTree.IterateLeaves((n, f) => grid.RowDefinitions.Add(CreateRow()));
topTree.IterateLeaves((n, f) => grid.ColumnDefinitions.Add(CreateColumn()));
int nStartRow = topTree.Height - 1;
int nStartColumn = leftTree.Height - 1;
// ... ttt
int row = nStartRow;
int column = nStartColumn - 1;
int nLeftTotals = leftTree.IterateLeaves((n, f) => grid.Children.Add(CreateCell(row++, column, n.value, true, new OlapCellInfo(n, null))));
row = nStartRow - 1;
column = nStartColumn;
int nTopTotals = topTree.IterateLeaves((n, f) => grid.Children.Add(CreateCell(row, column++, n.value, true, new OlapCellInfo(null, n))));
long[] leftTotals = new long[nLeftTotals];
long[] topTotals = new long[nTopTotals];
long grandTotals = 0;
row = nStartRow;
leftTree.IterateLeaves((leftValue, leftFilters) =>
{
column = nStartColumn;
topTree.IterateLeaves((topValue, topFilters) =>
{
long val = olapStage.SelectData(leftFilters, topFilters, stage.aggregations[0]);
if (0 != val)
{
if (bLeftTotals)
leftTotals[row - nStartRow] = leftTotals[row - nStartRow] + val;
if (bTopTotals)
topTotals[column - nStartColumn] = topTotals[column - nStartColumn] + val;
if (bGrandTotals)
grandTotals += val;
grid.Children.Add(CreateCell(row, column, val.ToString(), false, new OlapCellInfo(leftValue, topValue)));
}
++column;
});
++row;
});
if (bLeftTotals)
for (row = 0; row < nLeftTotals; ++row)
grid.Children.Add(CreateCell(nStartRow + row, nStartColumn + nTopTotals, leftTotals[row].ToString(), false, null));
if (bTopTotals)
for (column = 0; column < nTopTotals; ++column)
grid.Children.Add(CreateCell(nStartRow + nLeftTotals, nStartColumn + column, topTotals[column].ToString(), false, null));
if (bGrandTotals)
grid.Children.Add(CreateCell(nStartRow + nLeftTotals, nStartColumn + nTopTotals, grandTotals.ToString(), false, null));
return grid;
}
}