public virtual void Pivot2()
{
PivotTable pivot = new PivotTable();
SetupPivot(pivot);
pivot.BaseSql = "select o.*, c.name, p.name productName from orders o inner join customers c on o.customerId = c.customerId inner join products p on o.productId = p.productId";
pivot.Function = SqlAggregationFunction.Count;
pivot.ValueField = "productName";
pivot.RowField = "productName";
PivotColumn pivotCol = new PivotColumn("name", SqlDataType.String);
pivotCol.Values.Add(PivotColumnValue.CreateScalar("c1", "John"));
pivotCol.Values.Add(PivotColumnValue.CreateScalar("c2", "Lucinda"));
pivotCol.Values.Add(PivotColumnValue.CreateScalar("c3", "Mark"));
pivot.Columns.Add(pivotCol);
pivot.WithTotal = false;
SelectQuery pivotQuery = pivot.BuildPivotSql();
DataTable data = Fill(pivotQuery);
if (data == null)
{
return;
}
WriteTable(data);
}
public ActionResult Index(IndexViewModel model)
{
model.pivot = SampleDB.Data.ToPivotTable(
PivotColumn <MockData> .Build("country", "gender"),
PivotColumn <MockData> .Build("stock_market"),
PivotMeasure <MockData> .Build("stock")
);
return(View(model));
}
public virtual void Pivot1()
{
SelectQuery baseQuery = new SelectQuery();
baseQuery.Columns.Add(new SelectColumn("*"));
baseQuery.FromClause.BaseTable = FromTerm.Table("orders");
PivotTable pivot = new PivotTable();
SetupPivot(pivot);
pivot.BaseQuery = baseQuery;
pivot.Function = SqlAggregationFunction.Sum;
pivot.ValueField = "quantaty";
pivot.RowField = "customerId";
PivotColumn pivotCol = new PivotColumn("date", SqlDataType.Date);
TimePeriod currentYear = TimePeriod.FromToday(TimePeriodType.Year);
pivotCol.Values.Add(PivotColumnValue.CreateRange("PreviousYears", new Range(null, currentYear.Add(-1).PeriodStartDate)));
pivotCol.Values.Add(PivotColumnValue.CreateRange("LastYear", new Range(currentYear.Add(-1).PeriodStartDate, currentYear.PeriodStartDate)));
pivotCol.Values.Add(PivotColumnValue.CreateRange("FollowingYears", new Range(currentYear.PeriodStartDate, null)));
pivot.Columns.Add(pivotCol);
pivotCol = new PivotColumn("productId", SqlDataType.Number);
pivotCol.Values.Add(PivotColumnValue.CreateScalar("product1", 1));
pivotCol.Values.Add(PivotColumnValue.CreateRange("product2", new Range(2, 3)));
pivot.Columns.Add(pivotCol);
SelectQuery pivotQuery = pivot.BuildPivotSql();
DataTable data = Fill(pivotQuery);
if (data == null)
{
return;
}
WriteTable(data);
Console.WriteLine("Drill down");
SelectQuery drillDownQuery = pivot.BuildDrillDownSql(SqlConstant.Number(1), "LastYear");
data = Fill(drillDownQuery);
if (data == null)
{
return;
}
WriteTable(data);
Console.WriteLine("Drill down");
drillDownQuery = pivot.BuildDrillDownSql(null, "LastYear");
data = Fill(drillDownQuery);
if (data == null)
{
return;
}
WriteTable(data);
}
public ActionResult Total(IndexViewModel model)
{
model.pivot = SampleDB.Data.ToPivotTable(
PivotColumn <MockData> .Build("country", "gender"),
PivotColumn <MockData> .Build("stock_market"),
PivotMeasure <MockData> .Build("stock")
);
model.Option.Header[HeaderType.Row].RenderTotal = true;
model.Option.HeaderCellOption[model.pivot.ColumnByName("country")].RenderTotal = true;
return(View(model));
}
#pragma warning disable CA1043 // インデクサーには整数または文字列引数を使用します
public PivotAxisRenderOption this[PivotColumn <T> index] {
#pragma warning restore CA1043
get {
if (!options.ContainsKey(index))
{
options.Add(index, new PivotAxisRenderOption()
{
TotalCssClass = Properties.Resources.TotalCellTitle,
TotalTitle = Properties.Resources.TotalCellTitle,
});
}
return(options[index]);
}
}
public ActionResult Measure(IndexViewModel model)
{
model.pivot = SampleDB.Data.ToPivotTable(
PivotColumn <MockData> .Build("country", "gender"),
PivotColumn <MockData> .Build("stock_market"),
new List <PivotMeasure <MockData> >()
{
PivotMeasure <MockData> .Sum("Sum", (t) => t.stock),
PivotMeasure <MockData> .Average("Avg", (t) => t.stock),
PivotMeasure <MockData> .Min("Min", (t) => t.stock),
PivotMeasure <MockData> .Max("Max", (t) => t.stock),
}
);
return(View(model));
}
