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