/// <summary>
/// Create a new APRptFilter.
/// </summary>
/// <param name="column">The column defined</param>
/// <param name="comparator">Comparator.</param>
/// <param name="values">Values</param>
public APRptFilter(APRptColumn column, APRptFilterComparator comparator, params string[] values)
{
if (values.Length == 0)
throw new ArgumentException("values");
_column = column;
_comparator = comparator;
_values = values;
}
/// <summary>
/// Create a new APRptFilter.
/// </summary>
/// <param name="column">The column defined</param>
/// <param name="comparator">Comparator.</param>
/// <param name="values">Values</param>
public APRptFilter(APRptColumn column, APRptFilterComparator comparator, params string[] values)
{
if (values.Length == 0)
{
throw new ArgumentException("values");
}
_column = column;
_comparator = comparator;
_values = values;
}
/// <summary>
/// Create a new LookupAPRptColumn.
/// </summary>
/// <param name="selectExpr">SQL 'SELECT' Expression.</param>
/// <param name="id">Column unique ID.</param>
/// <param name="title">Title.</param>
/// <param name="joinTable">Join Table.</param>
/// <param name="joinType">Join Type.</param>
/// <param name="relationDef">Relation define.</param>
/// <param name="relationShowColumn">Show relation column.</param>
public LookupAPRptColumn(APSqlOperateExpr selectExpr, string id, string title,
APTableDef joinTable,
APSqlJoinType joinType,
APRelationDef relationDef,
APRptColumn relationShowColumn)
: base(selectExpr, id, title)
{
_joinTable = joinTable;
_joinType = joinType;
_relationDef = relationDef;
_relationShowColumn = relationShowColumn;
}
/// <summary>
/// Create a new LookupAPRptColumn.
/// </summary>
/// <param name="selectExpr">SQL 'SELECT' Expression.</param>
/// <param name="id">Column unique ID.</param>
/// <param name="title">Title.</param>
/// <param name="joinTable">Join Table.</param>
/// <param name="joinType">Join Type.</param>
/// <param name="relationDef">Relation define.</param>
/// <param name="relationShowColumn">Show relation column.</param>
public LookupAPRptColumn(APSqlOperateExpr selectExpr, string id, string title,
APTableDef joinTable,
APSqlJoinType joinType,
APRelationDef relationDef,
APRptColumn relationShowColumn)
: base(selectExpr, id, title)
{
_joinTable = joinTable;
_joinType = joinType;
_relationDef = relationDef;
_relationShowColumn = relationShowColumn;
}
/// <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);
}
/// <summary>
/// Read report group summary info.
/// </summary>
/// <param name="reader">The data reader.</param>
/// <returns>The group summary.</returns>
public virtual APRptJsonGroupSummary ReadGroupSummary(IDataReader reader)
{
// Group root, none text
APRptJsonGroupSummary rootSummary = new APRptJsonGroupSummary("");
while (reader.Read())
{
APRptJsonGroupSummary summary = rootSummary;
int count = (int)reader[COUNT_ALIAS];
summary.count += count;
foreach (APRptGroupDef groupDef in _def.Groups)
{
APRptColumn column = _source.AllColumns[groupDef.ColumnId];
object value = column.Json(reader);
object key;
string text;
if (value == null || value == DBNull.Value)
{
key = text = "";
}
else if (column is DateTimeAPRptColumn)
{
key = text = GetDateGroupText((DateTime)value, groupDef.DateGroupMode);
}
else if (column is LookupAPRptColumn)
{
key = reader[column.DataName];
text = Convert.ToString((column as LookupAPRptColumn).RelationShowColumn.Json(reader));
}
else
{
key = value;
text = Convert.ToString(value);
}
if (summary.subgroups != null && summary.subgroups.ContainsKey(key))
{
summary = summary.subgroups[key];
}
else
{
APRptJsonGroupSummary subSummary = new APRptJsonGroupSummary(text)
{
value = value
};
if (summary.subgroups == null)
{
summary.subgroups = new Dictionary <object, APRptJsonGroupSummary>();
}
summary.subgroups[key] = subSummary;
summary = subSummary;
}
summary.count += count;
}
}
return(rootSummary);
}
