/// <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();
}
if (values.Length > 1)
{
throw APRptFilterParseException.UnsupportMultiValues(comparator);
}
if (comparator == APRptFilterComparator.Equals)
{
return(GetQueryWherePhrase(APSqlConditionOperator.Equals, values[0]));
}
else if (comparator == APRptFilterComparator.NotEqual)
{
return(GetQueryWherePhrase(APSqlConditionOperator.NotEqual, values[0]));
}
throw APRptFilterParseException.UnsupportFilterComparator(GetType(), comparator);
}
/// <summary>
/// Parse 'WHERE' phrase value.
/// </summary>
/// <param name="value">The value.</param>
/// <returns>Parsed value.</returns>
protected override object TryGetFilterValue(string value)
{
try
{
foreach (KeyValuePair <T, string> pair in Dictionary)
{
if (pair.Value == value)
{
return(pair.Key);
}
}
if (typeof(T).IsEnum)
{
return((T)Enum.Parse(typeof(T), value));
}
else
{
return((T)Convert.ChangeType(value, typeof(T)));
}
}
catch (Exception ex)
{
throw APRptFilterParseException.InvalidValue(value, typeof(T), ex);
}
}
/// <summary>
/// Parse 'WHERE' phrase value.
/// </summary>
/// <param name="value">The value.</param>
/// <returns>Parsed value.</returns>
protected override object TryGetFilterValue(string value)
{
double retVal;
if (!Double.TryParse(value, out retVal))
{
throw APRptFilterParseException.InvalidNumber(typeof(Double));
}
return(retVal);
}
/// <summary>
/// Parse 'WHERE' phrase value.
/// </summary>
/// <param name="value">The value.</param>
/// <returns>Parsed value.</returns>
protected override object TryGetFilterValue(string value)
{
long retVal;
if (!Int64.TryParse(value, out retVal))
{
throw APRptFilterParseException.InvalidNumber(typeof(Int64));
}
return(retVal);
}
/// <summary>
/// Parse 'WHERE' phrase value.
/// </summary>
/// <param name="value">The value.</param>
/// <returns>Parsed value.</returns>
protected override object TryGetFilterValue(string value)
{
ushort retVal;
if (!UInt16.TryParse(value, out retVal))
{
throw APRptFilterParseException.InvalidNumber(typeof(UInt16));
}
return(retVal);
}
/// <summary>
/// Parse 'WHERE' phrase value.
/// </summary>
/// <param name="value">The value.</param>
/// <returns>Parsed value.</returns>
protected override object TryGetFilterValue(string value)
{
bool retVal;
if (!Boolean.TryParse(value, out retVal))
{
throw APRptFilterParseException.InvalidValue(value, typeof(bool), null);
}
return(retVal);
}
/// <summary>
/// Parse 'WHERE' phrase value.
/// </summary>
/// <param name="value">The value.</param>
/// <returns>Parsed value.</returns>
protected override object TryGetFilterValue(string value)
{
value = (string)base.TryGetFilterValue(value);
DateTime tmp;
if (DateTime.TryParse(value, out tmp))
{
return(tmp);
}
DateRange range = ParseDateRange(value);
if (range != null)
{
return(range);
}
throw APRptFilterParseException.InvalidDatetime();
}
/// <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>
/// 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>
/// Parse query where phrase.
/// Can parse special date value, include:<br/>
///
/// <b>YESTERDAY</b><br/>
/// Starts at 12:00:00 a.m. on the day before the current day and continues for 24 hours.<br/>
/// <b>TODAY</b><br/>
/// Starts at 12:00:00 a.m. on the current day and continues for 24 hours.<br/>
/// <b>TOMORROW</b><br/>
/// Starts at 12:00:00 a.m. on the day after the current day and continues for 24 hours.<br/>
/// <b>LAST WEEK</b><br/>
/// Starts at 12:00:00 a.m. on the first day of the week before the current week and continues for seven days.<br/>
/// <b>THIS WEEK</b><br/>
/// Starts at 12:00:00 a.m. on the first day of the current week and continues for seven days.<br/>
/// <b>NEXT WEEK</b><br/>
/// Starts at 12:00:00 a.m. on the first day of the week after the current week and continues for seven days.<br/>
/// <b>LAST MONTH</b><br/>
/// Starts at 12:00:00 a.m. on the first day of the month before the current month and continues for all the days of that month.<br/>
/// <b>THIS MONTH</b><br/>
/// Starts at 12:00:00 a.m. on the first day of the current month and continues for all the days of that month.<br/>
/// <b>NEXT MONTH</b><br/>
/// Starts at 12:00:00 a.m. on the first day of the month after the current month and continues for all the days of that month.<br/>
/// <b>LAST 90 DAYS</b><br/>
/// Starts at 12:00:00 a.m. 90 days before the current day and continues up to the current second. (The range includes today.)<br/>
/// <b>NEXT 90 DAYS</b><br/>
/// Starts at 12:00:00 a.m. on the day after the current day and continues for 90 days. (The range does not include today.)<br/>
/// <b>LAST n DAYS</b><br/>
/// Starts at 12:00:00 a.m. n days before the current day and continues up to the current second. (The range includes today.)<br/>
/// <b>NEXT n DAYS</b><br/>
/// Starts at 12:00:00 a.m. on the next day and continues for the next n days. (The range does not include today.)<br/>
/// <b>LAST n MONTHS</b><br/>
/// Starts at 12:00:00 a.m. on the first day of the month n months ago and continues to the end of the month before the current month. (The range does not include the current month.)<br/>
/// <b>NEXT n MONTHS</b><br/>
/// Starts at 12:00:00 a.m. on the first day of the month after the current month and continues to the end of the month n months in the future. (The range does not include the current month.)<br/>
/// <b>LAST QUARTER</b><br/>
/// Starts at 12:00:00 a.m. on the first day of the quarter before the current quarter and continues to the end of that quarter.<br/>
/// <b>THIS QUARTER</b><br/>
/// Starts at 12:00:00 a.m. on the first day of the current quarter and continues to the end of the quarter.<br/>
/// <b>NEXT QUARTER</b><br/>
/// Starts at 12:00:00 a.m. on the first day of the quarter after the current quarter and continues to the end of that quarter.<br/>
/// <b>LAST n QUARTERS</b><br/>
/// Starts at 12:00:00 a.m. on the first day of the quarter n quarters ago and continues to the end of the quarter before the current quarter. (The range does not include the current quarter.)<br/>
/// <b>NEXT n QUARTERS</b><br/>
/// Starts at 12:00:00 a.m. on the first day of the quarter after the current quarter and continues to the end of the quarter n quarters in the future. (The range does not include the current quarter.)<br/>
/// <b>LAST YEAR</b><br/>
/// Starts at 12:00:00 a.m. on January 1 of the year before the current year and continues through the end of December 31 of that year.<br/>
/// <b>THIS YEAR</b><br/>
/// Starts at 12:00:00 a.m. on January 1 of the current year and continues through the end of December 31 of the current year.<br/>
/// <b>NEXT YEAR</b><br/>
/// Starts at 12:00:00 a.m. on January 1 of the year after the current year and continues through the end of December 31 of that year.<br/>
/// <b>LAST n YEARS</b><br/>
/// Starts at 12:00:00 a.m. on January 1 of the year n years ago and continues through December 31 of the year before the current year.<br/>
/// <b>NEXT n YEARS</b><br/>
/// Starts at 12:00:00 a.m. on January 1 of the year after the current year and continues through the end of December 31 of the nth year.<br/>
/// </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)
{
object v = TryGetFilterValue(values[0]);
if (v is DateRange)
{
DateRange dr = (DateRange)v;
return(new APSqlConditionPhrase(expr, APSqlConditionOperator.GreaterThanOrEqual, dr.Start)
& new APSqlConditionPhrase(expr, APSqlConditionOperator.LessThan, dr.End));
}
else if (v is DateTime)
{
DateTime dt = (DateTime)v;
return(new APSqlConditionPhrase(expr, APSqlConditionOperator.Equals, dt));
}
throw APRptFilterParseException.InvalidDatetime();
}
else
{
List <APSqlWherePhrase> list = new List <APSqlWherePhrase>();
foreach (string value in values)
{
object v = TryGetFilterValue(value);
if (v is DateTime)
{
list.Add(new APSqlConditionPhrase(expr, APSqlConditionOperator.Equals, v));
}
else if (v is DateRange)
{
throw APRptFilterParseException.UnsupportSpecialDateValueOrOnlyOne(comparator);
}
else
{
throw APRptFilterParseException.InvalidDatetime();
}
}
return(new APSqlConditionOrPhrase(list));
}
}
else if (comparator == APRptFilterComparator.NotEqual)
{
if (values.Length == 1)
{
object v = TryGetFilterValue(values[0]);
if (v is DateRange)
{
DateRange dr = (DateRange)v;
return(new APSqlConditionPhrase(expr, APSqlConditionOperator.LessThan, dr.Start)
| new APSqlConditionPhrase(expr, APSqlConditionOperator.GreaterThanOrEqual, dr.End));
}
else if (v is DateTime)
{
DateTime dt = (DateTime)v;
return(new APSqlConditionPhrase(expr, APSqlConditionOperator.NotEqual, dt));
}
throw APRptFilterParseException.InvalidDatetime();
}
else
{
List <APSqlWherePhrase> list = new List <APSqlWherePhrase>();
foreach (string value in values)
{
object v = TryGetFilterValue(value);
if (v is DateTime)
{
list.Add(new APSqlConditionPhrase(expr, APSqlConditionOperator.NotEqual, v));
}
else if (v is DateRange)
{
throw APRptFilterParseException.UnsupportSpecialDateValueOrOnlyOne(comparator);
}
else
{
throw APRptFilterParseException.InvalidDatetime();
}
}
return(new APSqlConditionAndPhrase(list));
}
}
else if (comparator == APRptFilterComparator.LessThan ||
comparator == APRptFilterComparator.LessOrEqual ||
comparator == APRptFilterComparator.GreaterThan ||
comparator == APRptFilterComparator.GreaterOrEqual)
{
if (values.Length > 1)
{
throw APRptFilterParseException.UnsupportMultiValues(comparator);
}
object v = TryGetFilterValue(values[0]);
if (v is DateTime)
{
return(new APSqlConditionPhrase(expr, (APSqlConditionOperator)comparator, v));
}
else if (v is DateRange)
{
throw APRptFilterParseException.UnsupportSpecialDateValueOrOnlyOne(comparator);
}
else
{
throw APRptFilterParseException.InvalidDatetime();
}
}
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();
}
if (v1 is DateRange)
{
v1 = (v1 as DateRange).Start;
}
if (v2 is DateRange)
{
v2 = (v2 as DateRange).Start;
}
if (v1 is DateTime && v2 is DateTime)
{
return(new APSqlConditionPhrase(expr, APSqlConditionOperator.Between, new object[2] {
v1, v2
}));
}
throw APRptFilterParseException.InvalidDatetime();
}
throw APRptFilterParseException.UnsupportFilterComparator(GetType(), comparator);
}