/// <summary>
/// Build database SQL query clause for one criterion.
/// Assumes field is date.
/// </summary>
/// <param name="prefix">operator to precede clause, e.g. "AND"</param>
/// <param name="sf">StationField which describes the desired field</param>
/// <param name="c"></param>
/// <returns>empty string if c is null</returns>
public static string AddSqlDateClause(string prefix, StationField sf, Criterion c)
{
string clause = "";
if (c != null)
{
clause = " " + prefix + " ";
if (c.IsUnaryOperator())
{
clause += SqlUnaryOperation(c, sf);
}
else
{ // binary operator
// TODO Idea: recognize keyword like "TODAY", would require a special clause
// TODO "equals" for date doesn't work -- need to compare only the date,
// not the time (or adjust the end time to 11:59:59)
clause += sf.Location + " ";
clause += SqlBinaryOperator(c.Operation) +
" TO_DATE('" + c.Value + "','" + SqlDateFormat(c.Value) + "') ";
}
}
return clause;
}
/// <summary>
/// Build database SQL query clause for one criterion.
/// Assumes field is numeric.
/// </summary>
/// <param name="prefix">operator to precede clause, e.g. "AND"</param>
/// <param name="sf">StationField which describes the desired field</param>
/// <param name="c"></param>
/// <returns>empty string if c is null</returns>
public static string AddSqlNumberClause(string prefix, StationField sf, Criterion c)
{
string clause = "";
if (c != null)
{
clause = " " + prefix + " ";
if (c.IsUnaryOperator())
{
clause += SqlUnaryOperation(c, sf);
}
else
{ // binary operator
clause += sf.Location + " ";
clause += SqlBinaryOperator(c.Operation) +
// still use quotes in case it's not really a number field
// or the operator is "LIKE"
" '" + c.Value + "' ";
}
}
return clause;
}
/// <summary>
/// Build database SQL query clause for one criterion.
/// Assumes field is string or string-compatible.
/// </summary>
/// <param name="prefix">operator to precede clause, e.g. "AND"</param>
/// <param name="sf">StationField which describes the desired field</param>
/// <param name="c"></param>
/// <returns>empty string if c is null</returns>
public static string AddSqlTextClause(string prefix, StationField sf, Criterion c)
{
string clause = "";
if (c != null)
{
clause = " " + prefix + " ";
if (c.IsUnaryOperator())
{
clause += SqlUnaryTextOperation(c, sf);
}
else
{ // binary operator
clause += sf.Location + " ";
clause += SqlBinaryOperator(c.Operation) +
" '" + c.Value + "' ";
}
}
return clause;
}
/// <summary>
/// Build SQL clause for unary operation, for text fields.
/// </summary>
/// <returns>clause like " (COLUMN is null OR TRIM(COLUMN) = '') "</returns>
private static string SqlUnaryTextOperation(Criterion c, StationField sf)
{
if (c == null || sf == null) return "";
string clause;
string column = sf.Location;
if (c.Operation == (Criterion.Empty))
{
clause = " (" + column + " is null OR TRIM(" + column + ") = '') ";
}
else if (c.Operation == (Criterion.NotEmpty))
{
clause = " (" + column + " is not null AND TRIM(" + column + ") <> '') ";
}
else
{
throw new Exception("unrecognized unary operation: " + c.Operation);
}
return clause;
}
protected virtual IQueryable<FileInfo> ApplyUpdateDateCriteria(IQueryable<FileInfo> query, Criterion c)
{
DateTime d = DateTime.Parse(c.Value);
if (c.Operation == Criterion.GreaterThan)
query = query.Where(f => f.LastWriteTime > d);
else if (c.Operation == Criterion.GreaterThanOrEqual)
query = query.Where(f => f.LastWriteTime >= d);
else if (c.Operation == Criterion.LessThan)
query = query.Where(f => f.LastWriteTime < d);
else if (c.Operation == Criterion.LessThanOrEqual)
query = query.Where(f => f.LastWriteTime <= d);
else
{
throw new InvalidOperationException(string.Format("Can't use {0} operator with Update Date ", c.Operation));
}
return query;
}
protected virtual IQueryable<FileInfo> ApplyStuckCriteria(IQueryable<FileInfo> query, Criterion c)
{
TimeSpan oldAge = new TimeSpan(0, 0, AgeLimit);
query = query.Where(f => DateTime.Now - f.LastWriteTime > oldAge);
return query;
}
protected virtual IQueryable<FileInfo> ApplyRequestIdCriteria(IQueryable<FileInfo> query, Criterion c)
{
string id = c.Value;
if (c.Operation == Criterion.Equal)
query = query.Where(f => CompareFileId(f, id) == 0);
else if (c.Operation == Criterion.NotEqual)
query = query.Where(f => CompareFileId(f, id) != 0);
else if (c.Operation == Criterion.GreaterThan)
query = query.Where(f => CompareFileId(f, id) > 0);
else if (c.Operation == Criterion.GreaterThanOrEqual)
query = query.Where(f => CompareFileId(f, id) >= 0);
else if (c.Operation == Criterion.LessThan)
query = query.Where(f => CompareFileId(f, id) < 0);
else if (c.Operation == Criterion.LessThanOrEqual)
query = query.Where(f => CompareFileId(f, id) <= 0);
else
{
throw new InvalidOperationException(string.Format("Can't use {0} operator with ID based on file name", c.Operation));
}
return query;
}
/// <summary>
/// Perform search using the specified temporary criterion.
/// But put the Search object's Criteria back to its original set.
/// </summary>
/// <param name="c">Only criterion to be used for this search</param>
/// <param name="role">if null, not restricted to any role</param>
public List<Instance> DoSearchWithSpecifiedCriterion(Criterion c, string role)
{
ICollection<Criterion> newCrit = new List<Criterion>();
newCrit.Add(c);
return DoSearchWithSpecifiedCriteria(newCrit, role);
}
/// <summary>
/// Perform search using the existing criteria plus an additional temporary criterion.
/// </summary>
/// <param name="c">Additional criterion for this search only</param>
/// <param name="role">if null, not restricted to any role</param>
public List<Instance> DoSearchWithAdditionalCriterion(Criterion c, string role)
{
ICollection<Criterion> newCrit = new List<Criterion>(this.Crit); // make copy of the current criteria
newCrit.Add(c);
return DoSearchWithSpecifiedCriteria(newCrit, role);
}