private void WriteSelectExpression(ParserWriter writer, APSqlExpr selectExpr)
{
if (selectExpr is APSqlAggregationExpr)
{
APSqlAggregationExpr expr = selectExpr as APSqlAggregationExpr;
writer.Write(expr.AggregationType.ToString());
writer.Write("(");
if (expr.SelectMode == APSqlSelectMode.DISTINCT)
{
writer.Write("DISTINCT");
}
if (expr.RowSelectExpr is APSqlAsteriskExpr)
{
writer.Write("*");
}
else
{
WriteSelectExpression(writer, expr.RowSelectExpr);
}
writer.Write(")");
}
else if (selectExpr is APSqlDateGroupExpr)
{
APSqlDateGroupExpr expr = selectExpr as APSqlDateGroupExpr;
string datepart = "";
switch (expr.DateGroupMode)
{
case APSqlDateGroupMode.Day: datepart = "yyyy-mm-dd"; break;
case APSqlDateGroupMode.Week: datepart = "yyyy-IW"; break;
case APSqlDateGroupMode.Month: datepart = "yyyy-mm"; break;
case APSqlDateGroupMode.Quarter: datepart = "yyyy-Q"; break;
case APSqlDateGroupMode.Year: datepart = "yyyy"; break;
}
writer.Write("to_char(");
WriteSelectExpression(writer, expr.RawExpr);
writer.Write(", '{0}')", datepart);
}
else
{
writer.Write(selectExpr.SelectExpr);
}
}
private void WriteSelectExpression(ParserWriter writer, APSqlExpr selectExpr)
{
if (selectExpr is APSqlAggregationExpr)
{
APSqlAggregationExpr expr = selectExpr as APSqlAggregationExpr;
writer.Write(expr.AggregationType.ToString());
writer.Write("(");
if (expr.SelectMode == APSqlSelectMode.DISTINCT)
{
writer.Write("DISTINCT");
}
if (expr.RowSelectExpr is APSqlAsteriskExpr)
{
writer.Write("*");
}
else
{
WriteSelectExpression(writer, expr.RowSelectExpr);
}
writer.Write(")");
}
else if (selectExpr is APSqlDateGroupExpr)
{
APSqlDateGroupExpr expr = selectExpr as APSqlDateGroupExpr;
string datepart = "";
switch (expr.DateGroupMode)
{
case APSqlDateGroupMode.Day: datepart = "dd"; break;
case APSqlDateGroupMode.Week: datepart = "wk"; break;
case APSqlDateGroupMode.Month: datepart = "mm"; break;
case APSqlDateGroupMode.Quarter: datepart = "qq"; break;
case APSqlDateGroupMode.Year: datepart = "yy"; break;
}
writer.Write(String.Format("DATEADD( {0}, DATEDIFF( {0}, 0,", datepart));
WriteSelectExpression(writer, expr.RawExpr);
writer.Write("), 0 )");
}
else
{
writer.Write(selectExpr.SelectExpr);
}
}
/// <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;
}