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; }