/// <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);
}
/// <summary>
/// Build a APQuery.
/// </summary>
/// <param name="additionCondition">Addition condition.</param>
/// <param name="additionOrders">Addition orders</param>
/// <param name="fuzzySearchString">Fuzzy search string.</param>
/// <returns>The APQuery.</returns>
public virtual APSqlSelectCommand BuildQuery(APSqlWherePhrase additionCondition = null,
IEnumerable <APSqlOrderPhrase> additionOrders = null,
string fuzzySearchString = null)
{
List <APSqlSelectPhrase> select = _source.GetPrimeSelectPhrases();
List <APSqlFromPhrase> from = _source.GetPrimeFormPhrases();
List <APSqlWherePhrase> where = _source.GetPrimeWherePhrases();
// Base build.
foreach (APRptReferDef refer in _def.Refers)
{
APRptColumn column = _source.AllColumns[refer.ColumnId];
column.AddToQuerySelectPhrases(select);
column.AddToQueryFromPhrases(from);
column.AddToQueryWherePhrases(where);
}
var query = APQuery
.select(select)
.from(from)
.primary(_source.GetPrimaryExpr());
// Order build.
List <APSqlOrderPhrase> orderby = new List <APSqlOrderPhrase>();
if (_def.Orders.Count > 0)
{
foreach (APRptOrderDef order in _def.Orders)
{
orderby.Add(_source.AllColumns[order.ColumnId].GetQueryOrderByPhrase(order.According));
}
}
if (additionOrders != null)
{
foreach (APSqlOrderPhrase order in additionOrders)
{
orderby.Add(order);
}
}
query.order_by(orderby);
// Filter build.
if (_def.Condition.Filters.Count > 0)
{
where.Add(new APRptConditionBuilder(_def.Condition, _source.AllColumns).BuildCondition());
}
// Additions condition.
if (additionCondition != null)
{
where.Add(additionCondition);
}
// Fuzzy search condition.
APSqlWherePhrase fuzzyWhere;
if (!String.IsNullOrEmpty(fuzzySearchString) && (fuzzyWhere = _source.GetFuzzySearchPhrase(fuzzySearchString)) != null)
{
where.Add(fuzzyWhere);
}
query.where (where);
return(query);
}