/// <summary>
/// Create a new APRptColumn.
/// </summary>
/// <param name="selectExpr">SQL 'SELECT' Expression.</param>
/// <param name="id">Column unique ID.</param>
/// <param name="title">Column Title.</param>
protected APRptColumn(APSqlOperateExpr selectExpr, string id, string title)
{
_selectExpr = selectExpr;
if (selectExpr is APSqlColumnExpr)
{
var column = (selectExpr as APSqlColumnExpr).ColumnDef;
_id = String.IsNullOrEmpty(id) ? column.SelectExpr : id;
_title = String.IsNullOrEmpty(title) ? column.Display : title;
_dataName = column.ColumnName;
_required = column.Required;
_isNullable = column.IsNullable;
}
else
{
var paramName = (selectExpr as APSqlOperateExpr).ParamName;
_id = String.IsNullOrEmpty(id) ? paramName : id;
_title = String.IsNullOrEmpty(title) ? paramName : title;
_dataName = paramName;
_required = false;
_isNullable = true;
}
}
/// <summary>
/// Create a new DateTimeAPRptColumn.
/// </summary>
/// <param name="selectExpr">SQL 'SELECT' Expression.</param>
/// <param name="id">Column unique ID.</param>
/// <param name="title">Title.</param>
/// <param name="dateTimeType">DateTime type.</param>
/// <param name="minValue">The min value.</param>
/// <param name="maxValue">The max value.</param>
public DateTimeAPRptColumn(APSqlOperateExpr selectExpr, string id, string title, APRptDateTimeType dateTimeType, DateTime minValue, DateTime maxValue)
: base(selectExpr, id, title)
{
_dateTimeType = dateTimeType;
_minValue = minValue;
_maxValue = maxValue;
}
/// <summary>
/// Create a new DecimalAPRptColumn.
/// </summary>
/// <param name="selectExpr">SQL 'SELECT' Expression.</param>
/// <param name="id">Column unique ID.</param>
/// <param name="title">Title.</param>
/// <param name="scale">Scale.</param>
/// <param name="minValue">The min value.</param>
/// <param name="maxValue">The max value.</param>
public DecimalAPRptColumn(APSqlOperateExpr selectExpr, string id, string title, int scale, Decimal minValue = Decimal.MinValue, Decimal maxValue = Decimal.MaxValue)
: base(selectExpr, id, title)
{
_minValue = minValue;
_maxValue = maxValue;
_scale = scale;
}
/// <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 NumberAPRptColumn.
/// </summary>
/// <param name="selectExpr">SQL 'SELECT' Expression.</param>
/// <param name="id">Column unique ID.</param>
/// <param name="title">Title.</param>
public NumberAPRptColumn(APSqlOperateExpr selectExpr, string id, string title)
: base(selectExpr, id, title)
{
}
/// <summary>
/// Parse query where phrase.
/// </summary>
/// <param name="comparator">Comparator.</param>
/// <param name="values">Values.</param>
/// <returns>An APSqlWherePhrase.</returns>
/// <exception cref="APRptFilterParseException">Throw exception on parse error.</exception>
public override APSqlWherePhrase ParseQueryWherePhrase(APRptFilterComparator comparator, params string[] values)
{
if (values.Length == 0)
{
throw APRptFilterParseException.ValuesCountCannotBeZero();
}
APSqlOperateExpr expr = SelectExpr;
if (comparator == APRptFilterComparator.Equals)
{
if (values.Length == 1)
{
return(GetQueryWherePhrase(APSqlConditionOperator.Equals, values[0]));
}
// Change operator to 'IN'
return(new APSqlConditionPhrase(expr, APSqlConditionOperator.In, GetFilterValues(values)));
}
else if (comparator == APRptFilterComparator.NotEqual)
{
if (values.Length == 1)
{
return(GetQueryWherePhrase(APSqlConditionOperator.NotEqual, values[0]));
}
// Change operator to 'NOT IN'
return(new APSqlConditionPhrase(expr, APSqlConditionOperator.NotIn, GetFilterValues(values)));
}
else if (comparator == APRptFilterComparator.LessThan ||
comparator == APRptFilterComparator.LessOrEqual ||
comparator == APRptFilterComparator.GreaterThan ||
comparator == APRptFilterComparator.GreaterOrEqual)
{
if (values.Length > 1)
{
throw APRptFilterParseException.UnsupportMultiValues(comparator);
}
return(GetQueryWherePhrase((APSqlConditionOperator)comparator, values[0]));
}
else if (comparator == APRptFilterComparator.Between)
{
if (values.Length != 2)
{
throw APRptFilterParseException.BetweenMustHaveTwoValues();
}
object v1 = TryGetFilterValue(values[0]);
object v2 = TryGetFilterValue(values[1]);
if (v1 == DBNull.Value || v2 == DBNull.Value)
{
throw APRptFilterParseException.UnsupportDBNull();
}
return(new APSqlConditionPhrase(expr, APSqlConditionOperator.Between, new object[2] {
v1, v2
}));
}
throw APRptFilterParseException.UnsupportFilterComparator(GetType(), comparator);
}
/// <summary>
/// Build a SQL aggregation Expression of count.
/// </summary>
/// <param name="expr">Operate expression.</param>
/// <returns>SQL aggregation Expression of count.</returns>
public static APSqlAggregationExpr Count(this APSqlOperateExpr expr)
{
return(new APSqlAggregationExpr(expr, APSqlAggregationType.COUNT));
}
/// <summary>
/// Build a SQL aggregation Expression of checksum.
/// </summary>
/// <param name="expr">Operate expression.</param>
/// <returns>SQL aggregation Expression of checksum.</returns>
public static APSqlAggregationExpr Checksum(this APSqlOperateExpr expr)
{
return(new APSqlAggregationExpr(expr, APSqlAggregationType.CHECKSUM));
}
/// <summary>
/// Build a SQL aggregation Expression of average.
/// </summary>
/// <param name="expr">Operate expression.</param>
/// <returns>SQL aggregation Expression of average.</returns>
public static APSqlAggregationExpr Avg(this APSqlOperateExpr expr)
{
return(new APSqlAggregationExpr(expr, APSqlAggregationType.AVG));
}
/// <summary>
/// Build a SQL aggregation Expression of statistical standard deviation for the population.
/// </summary>
/// <param name="expr">Operate expression.</param>
/// <returns>SQL aggregation Expression of statistical standard deviation for the population.</returns>
public static APSqlAggregationExpr Stdevp(this APSqlOperateExpr expr)
{
return(new APSqlAggregationExpr(expr, APSqlAggregationType.STDEVP));
}
/// <summary>
/// Build a SQL aggregation Expression of min.
/// </summary>
/// <param name="expr">Operate expression.</param>
/// <returns>SQL aggregation Expression of min.</returns>
public static APSqlAggregationExpr Min(this APSqlOperateExpr expr)
{
return(new APSqlAggregationExpr(expr, APSqlAggregationType.MIN));
}
/// <summary>
/// Gets command parameter name of SQL assignment Expression.
/// </summary>
/// <param name="assignmentExpr">'SELECT' phrase.</param>
/// <returns>The parameter name.</returns>
public virtual string GetParameterName(APSqlOperateExpr assignmentExpr)
{
return assignmentExpr.ParamName + _index++;
}
/// <summary>
/// Create a new CurrencyAPRptColumn.
/// </summary>
/// <param name="selectExpr">SQL 'SELECT' Expression.</param>
/// <param name="id">Column unique ID.</param>
/// <param name="title">Title.</param>
/// <param name="scale">Scale.</param>
/// <param name="minValue">The min value.</param>
/// <param name="maxValue">The max value.</param>
public CurrencyAPRptColumn(APSqlOperateExpr selectExpr, string id, string title, int scale = 2, Decimal minValue = Decimal.MinValue, Decimal maxValue = Decimal.MaxValue)
: base(selectExpr, id, title, scale, minValue, maxValue)
{
}
/// <summary>
/// Create a 'ORDER BY' phrase.
/// </summary>
/// <param name="expr">SQL 'SELECT' Expression.</param>
public APSqlOrderPhrase(APSqlOperateExpr expr)
: this(expr, APSqlOrderAccording.Asc)
{
}
/// <summary>
/// Create a 'ORDER BY' phrase.
/// </summary>
/// <param name="expr">SQL 'SELECT' Expression.</param>
/// <param name="orderAccording">Order according.</param>
public APSqlOrderPhrase(APSqlOperateExpr expr, APSqlOrderAccording orderAccording)
{
_expr = expr;
_orderAccording = orderAccording;
}
/// <summary>
/// Create a new Int64APRptColumn.
/// </summary>
/// <param name="selectExpr">SQL 'SELECT' Expression.</param>
/// <param name="id">Column unique ID.</param>
/// <param name="title">Title.</param>
/// <param name="minValue">The min value.</param>
/// <param name="maxValue">The max value.</param>
public Int64APRptColumn(APSqlOperateExpr selectExpr, string id, string title, Int64 minValue = Int64.MinValue, Int64 maxValue = Int64.MaxValue)
: base(selectExpr, id, title)
{
_minValue = minValue;
_maxValue = maxValue;
}
/// <summary>
/// Build a SQL aggregation Expression of grouping.
/// </summary>
/// <param name="expr">Operate expression.</param>
/// <returns>SQL aggregation Expression of grouping.</returns>
public static APSqlAggregationExpr Grouping(this APSqlOperateExpr expr)
{
return(new APSqlAggregationExpr(expr, APSqlAggregationType.GROUPING));
}
/// <summary>
/// Create a new 'SELECT' phrase with alias.
/// </summary>
/// <param name="selectExpr">The SQL 'SELECT' Expression, can be alias.</param>
/// <param name="alias">Alias.</param>
/// <returns>'SELECT' phrase.</returns>
public static APSqlSelectPhrase As(this APSqlOperateExpr selectExpr, string alias)
{
return(new APSqlSelectPhrase(selectExpr, alias));
}
/// <summary>
/// Create a new MultiLineTextAPRptColumn.
/// </summary>
/// <param name="selectExpr">SQL 'SELECT' Expression.</param>
/// <param name="id">Column unique ID.</param>
/// <param name="title">Title.</param>
/// <param name="dataLength">Data length.</param>
public MultiLineTextAPRptColumn(APSqlOperateExpr selectExpr, string id, string title, int dataLength)
: base(selectExpr, id, title, dataLength)
{
}
/// <summary>
/// Build a SQL aggregation Expression of sum.
/// </summary>
/// <param name="expr">Operate expression.</param>
/// <returns>SQL aggregation Expression of sum.</returns>
public static APSqlAggregationExpr Sum(this APSqlOperateExpr expr)
{
return(new APSqlAggregationExpr(expr, APSqlAggregationType.SUM));
}
/// <summary>
/// Parse query where phrase.
/// </summary>
/// <param name="comparator">Comparator.</param>
/// <param name="values">Values.</param>
/// <returns>An APSqlWherePhrase.</returns>
/// <exception cref="APRptFilterParseException">Throw exception on parse error.</exception>
public override APSqlWherePhrase ParseQueryWherePhrase(APRptFilterComparator comparator, params string[] values)
{
if (values.Length == 0)
{
throw APRptFilterParseException.ValuesCountCannotBeZero();
}
APSqlOperateExpr expr = SelectExpr;
if (comparator == APRptFilterComparator.Equals)
{
if (values.Length == 1)
{
return(GetQueryWherePhrase(APSqlConditionOperator.Equals, values[0]));
}
// Change operator to 'IN'
return(new APSqlConditionPhrase(expr, APSqlConditionOperator.In, GetFilterValues(values)));
}
else if (comparator == APRptFilterComparator.NotEqual)
{
if (values.Length == 1)
{
return(GetQueryWherePhrase(APSqlConditionOperator.NotEqual, values[0]));
}
// Change operator to 'NOT IN'
return(new APSqlConditionPhrase(expr, APSqlConditionOperator.NotIn, GetFilterValues(values)));
}
else if (comparator == APRptFilterComparator.LessThan ||
comparator == APRptFilterComparator.LessOrEqual ||
comparator == APRptFilterComparator.GreaterThan ||
comparator == APRptFilterComparator.GreaterOrEqual)
{
if (values.Length > 1)
{
throw APRptFilterParseException.UnsupportMultiValues(comparator);
}
return(GetQueryWherePhrase((APSqlConditionOperator)comparator, values[0]));
}
// todo: here use APDatabase.Provider.Wildcard, when multi-providers may by error wildcard char.
else if (comparator == APRptFilterComparator.StartsWith)
{
if (values.Length == 1)
{
object v = TryGetFilterValue(values[0]);
if (v == DBNull.Value)
{
throw APRptFilterParseException.UnsupportDBNull();
}
string likeString = (v as String) + APDalProvider.Wildcard;
return(new APSqlConditionPhrase(expr, APSqlConditionOperator.Like, likeString));
}
List <APSqlWherePhrase> list = new List <APSqlWherePhrase>();
foreach (string s in values)
{
object v = TryGetFilterValue(s);
if (v == DBNull.Value)
{
throw APRptFilterParseException.UnsupportDBNull();
}
string likeString = (v as String) + APDalProvider.Wildcard;
list.Add(new APSqlConditionPhrase(expr, APSqlConditionOperator.Like, likeString));
}
return(new APSqlConditionOrPhrase(list));
}
else if (comparator == APRptFilterComparator.Contains || comparator == APRptFilterComparator.DoesNotContain)
{
if (values.Length == 1)
{
object v = TryGetFilterValue(values[0]);
if (v == DBNull.Value)
{
throw APRptFilterParseException.UnsupportDBNull();
}
string likeString = APDalProvider.Wildcard + (v as String) + APDalProvider.Wildcard;
if (comparator == APRptFilterComparator.Contains)
{
return(new APSqlConditionPhrase(expr, APSqlConditionOperator.Like, likeString));
}
else
{
return(new APSqlConditionPhrase(expr, APSqlConditionOperator.NotLike, likeString));
}
}
List <APSqlWherePhrase> list = new List <APSqlWherePhrase>();
foreach (string s in values)
{
object v = TryGetFilterValue(s);
if (v == DBNull.Value)
{
throw APRptFilterParseException.UnsupportDBNull();
}
string likeString = APDalProvider.Wildcard + (v as String) + APDalProvider.Wildcard;
list.Add(new APSqlConditionPhrase(expr, APSqlConditionOperator.NotLike, likeString));
}
if (comparator == APRptFilterComparator.Contains)
{
return(new APSqlConditionOrPhrase(list));
}
return(!new APSqlConditionOrPhrase(list));
}
throw APRptFilterParseException.UnsupportFilterComparator(GetType(), comparator);
}
/// <summary>
/// Build a SQL aggregation Expression of statistical variance for the population.
/// </summary>
/// <param name="expr">Operate expression.</param>
/// <returns>SQL aggregation Expression of statistical variance for the population.</returns>
public static APSqlAggregationExpr Varp(this APSqlOperateExpr expr)
{
return(new APSqlAggregationExpr(expr, APSqlAggregationType.VARP));
}
/// <summary>
/// Create a new TextAPRptColumn.
/// </summary>
/// <param name="selectExpr">SQL 'SELECT' Expression.</param>
/// <param name="id">Column unique ID.</param>
/// <param name="title">Title.</param>
/// <param name="dataLength">Data length.</param>
public TextAPRptColumn(APSqlOperateExpr selectExpr, string id, string title, int dataLength)
: base(selectExpr, id, title)
{
_dataLength = dataLength;
}
/// <summary>
/// Build a SQL aggregation Expression of average.
/// </summary>
/// <param name="expr">Operate expression.</param>
/// <param name="mode">Select mode, 'ALL' or 'DISTINCT'.</param>
/// <returns>SQL aggregation Expression of average.</returns>
public static APSqlAggregationExpr Avg(this APSqlOperateExpr expr, APSqlSelectMode mode)
{
return(new APSqlAggregationExpr(expr, APSqlAggregationType.AVG, mode));
}
/// <summary>
/// Create a new APRptColumn.
/// </summary>
/// <param name="selectExpr">SQL 'SELECT' Expression.</param>
protected APRptColumn(APSqlOperateExpr selectExpr)
: this(selectExpr, null, null)
{
}
/// <summary>
/// Build a SQL aggregation Expression of checksum agg.
/// </summary>
/// <param name="expr">Operate expression.</param>
/// <param name="mode">Select mode, 'ALL' or 'DISTINCT'.</param>
/// <returns>SQL aggregation Expression of checksum agg.</returns>
public static APSqlAggregationExpr ChecksumAgg(this APSqlOperateExpr expr, APSqlSelectMode mode)
{
return(new APSqlAggregationExpr(expr, APSqlAggregationType.CHECKSUM_AGG, mode));
}
/// <summary>
/// Create a 'ORDER BY' phrase.
/// </summary>
/// <param name="expr">SQL 'SELECT' Expression.</param>
/// <param name="orderAccording">Order according.</param>
public APSqlOrderPhrase(APSqlOperateExpr expr, APSqlOrderAccording orderAccording)
{
_expr = expr;
_orderAccording = orderAccording;
}
/// <summary>
/// Create a new DoubleAPRptColumn.
/// </summary>
/// <param name="selectExpr">SQL 'SELECT' Expression.</param>
/// <param name="id">Column unique ID.</param>
/// <param name="title">Title.</param>
/// <param name="minValue">The min value.</param>
/// <param name="maxValue">The max value.</param>
public DoubleAPRptColumn(APSqlOperateExpr selectExpr, string id, string title, Double minValue = Double.MinValue, Double maxValue = Double.MaxValue)
: base(selectExpr, id, title)
{
_minValue = minValue;
_maxValue = maxValue;
}
/// <summary>
/// Create a new RegexAPRptColumn.
/// </summary>
/// <param name="selectExpr">SQL 'SELECT' Expression.</param>
/// <param name="id">Column unique ID.</param>
/// <param name="title">Title.</param>
/// <param name="dataLength">Data length.</param>
/// <param name="pattern">Pattern of Regex.</param>
/// <param name="message">Invalidated message.</param>
public RegexAPRptColumn(APSqlOperateExpr selectExpr, string id, string title, int dataLength, string pattern, string message)
: base(selectExpr, id, title, dataLength)
{
_pattern = pattern;
_message = message;
}
/// <summary>
/// Create a new EmailAPRptColumn.
/// </summary>
/// <param name="selectExpr">SQL 'SELECT' Expression.</param>
/// <param name="id">Column unique ID.</param>
/// <param name="title">Title.</param>
/// <param name="dataLength">Data Length</param>
public EmailAPRptColumn(APSqlOperateExpr selectExpr, string id, string title, int dataLength = 320)
: base(selectExpr, id, title, dataLength, RegexAPRptColumn.RegexEmail, "")
{
}
/// <summary>
/// Create a new UInt32APRptColumn.
/// </summary>
/// <param name="selectExpr">SQL 'SELECT' Expression.</param>
/// <param name="id">Column unique ID.</param>
/// <param name="title">Title.</param>
/// <param name="minValue">The min value.</param>
/// <param name="maxValue">The max value.</param>
public UInt32APRptColumn(APSqlOperateExpr selectExpr, string id, string title, UInt32 minValue = UInt32.MinValue, UInt32 maxValue = UInt32.MaxValue)
: base(selectExpr, id, title)
{
_minValue = minValue;
_maxValue = maxValue;
}
/// <summary>
/// Create a 'ORDER BY' phrase.
/// </summary>
/// <param name="expr">SQL 'SELECT' Expression.</param>
public APSqlOrderPhrase(APSqlOperateExpr expr)
: this(expr, APSqlOrderAccording.Asc)
{
}
/// <summary>
/// Build a SQL aggregation Expression of count big.
/// </summary>
/// <param name="expr">Operate expression.</param>
/// <param name="mode">Select mode, 'ALL' or 'DISTINCT'.</param>
/// <returns>SQL aggregation Expression of count big.</returns>
public static APSqlAggregationExpr CountBig(this APSqlOperateExpr expr, APSqlSelectMode mode)
{
return(new APSqlAggregationExpr(expr, APSqlAggregationType.COUNT_BIG, mode));
}
