/// <summary>
/// Build group query command.
/// </summary>
/// <returns>The command.</returns>
public virtual APSqlSelectCommand BuildGroupQuery()
{
List <APSqlSelectPhrase> select = _source.GetGroupSelectPhrases();
List <APSqlFromPhrase> from = _source.GetPrimeFormPhrases();
List <APSqlWherePhrase> where = _source.GetPrimeWherePhrases();
List <APSqlExprPhrase> group = _source.GetGroupPhrases();
List <APSqlOrderPhrase> order = new List <APSqlOrderPhrase>();
foreach (APRptGroupDef def in _def.Groups)
{
APRptColumn column = _source.AllColumns[def.ColumnId];
// DateTime must grouping by datetime function.
if (column.FilterType == APRptFilterType.DateTime)
{
APSqlExpr expr = new APSqlDateGroupExpr(column.SelectExpr, def.DateGroupMode);
select.Add(expr);
group.Add(expr);
}
else
{
column.AddToQuerySelectPhrases(select);
column.AddToQueryGroupPhrases(group);
}
column.AddToQueryFromPhrases(from);
column.AddToQueryWherePhrases(where);
order.Add(column.GetQueryOrderByPhrase(def.According));
}
select.Add(APSqlAsteriskExpr.Expr.Count().As(COUNT_ALIAS));
// Build query
var query = APQuery
.select(select)
.from(from)
.where (where);
if (group.Count > 0)
{
query
.group_by(group)
.order_by(order);
}
return(query);
}
/// <summary>
/// Build maxtrix query command.
/// </summary>
/// <returns>The command.</returns>
public virtual APSqlSelectCommand BuildMatrixQuery()
{
List <APSqlSelectPhrase> select = _source.GetPrimeSelectPhrases();
List <APSqlFromPhrase> from = _source.GetPrimeFormPhrases();
List <APSqlWherePhrase> where = _source.GetPrimeWherePhrases();
List <APSqlOrderPhrase> order = new List <APSqlOrderPhrase>();
foreach (APRptGroupDef def in _def.Groups)
{
APRptColumn column = _source.AllColumns[def.ColumnId];
column.AddToQueryFromPhrases(from);
column.AddToQueryWherePhrases(where);
order.Add(column.GetQueryOrderByPhrase(def.According));
}
foreach (APRptReferDef def in _def.Refers)
{
APRptColumn column = _source.AllColumns[def.ColumnId];
column.AddToQuerySelectPhrases(select);
column.AddToQueryFromPhrases(from);
column.AddToQueryWherePhrases(where);
}
foreach (APRptOrderDef def in _def.Orders)
{
APRptColumn column = _source.AllColumns[def.ColumnId];
order.Add(column.GetQueryOrderByPhrase(def.According));
}
// Build query
var query = APQuery
.select(select)
.from(from)
.where (where);
return(query);
}