private static List <string> ParseWhereGroup(WhereGroup whereGroup, List <SelectModel> tables, bool processExternals)
{
if (!processExternals && whereGroup.IsExternal)
{
return(new List <string>());
}
var parsedWheres = new List <string>();
var statement = "(";
foreach (var whereStatement in whereGroup.WhereStatements)
{
if (parsedWheres.Count > 0)
{
statement = string.Empty;
}
if (!string.IsNullOrEmpty(whereStatement.Value))
{
ChangeComparison(whereStatement);
}
var tableAlias = tables.FirstOrDefault(x => x.TableId == whereStatement.TableId).TableAlias;
statement += UseAlias(whereStatement, tableAlias) ?
$"{tableAlias}.{ whereStatement.ColumnAlias} " :
$"{whereStatement.ColumnAlias}";
if (whereStatement.Value == null)
{
var compareTableAlias = tables.FirstOrDefault(t => t.TableId == whereStatement.CompareWithColumn.TableId).TableAlias;
statement += $"{whereStatement.Comparison} {compareTableAlias }.{whereStatement.CompareWithColumn.ColumnAlias}";
}
else
{
statement += $"{whereStatement.Comparison} {whereStatement.Value}";
}
statement += whereStatement.UseOr ? " or " : " and ";
parsedWheres.Add(statement);
}
if (parsedWheres.Count == 0 && whereGroup.WhereGroups.Count > 0)
{
parsedWheres.Add(statement);
}
parsedWheres.AddRange(whereGroup.WhereGroups.SelectMany(where => ParseWhereGroup(where, tables, processExternals)));
var last = parsedWheres.LastOrDefault();
if (last != null)
{
var lastOr = last.LastIndexOf("or");
var lastAnd = last.LastIndexOf("and");
if (lastAnd != -1 || lastOr != -1)
{
last = last.Insert(lastOr > lastAnd ? lastOr : lastAnd, ")");
parsedWheres[parsedWheres.Count - 1] = last;
}
}
return(parsedWheres);
}
/// <summary>
/// Fetch all records of type T that satisfy @whereConfitions adding one blank field with name @blankName
/// </summary>
/// <param name="whereConditions"></param>
/// <returns>List of records of type T</returns>
public static List <T> ListWithBlank(string blankName, WhereGroup whereConditions)
{
List <T> list = List(whereConditions);
T blank = new T();
blank.SetName(blankName);
list.Insert(0, blank);
return(list);
}
public void WhereGroup_2OR()
{
WhereGroup wg = new WhereGroup(true)
{
new WhereCondition("A", "B"),
new WhereCondition("C", "D"),
};
string correctValue = "([A] = 'B' OR [C] = 'D')";
string testValue = wg.Build();
MyAssert.Equals(correctValue, testValue);
}
/// <summary>
/// Fetch all records of type T that satisfy @whereConfitions
/// </summary>
/// <param name="whereConditions"></param>
/// <returns>List of records of type T</returns>
public static List <T> List(WhereGroup whereConditions)
{
var result = new List <T>();
DataTable dt = DBConn.Instance.GetAllFromTable((new T()).Table(), whereConditions);
foreach (DataRow row in dt.Rows)
{
T obj = new T();
obj.Apply(row);
result.Add(obj);
}
return(result);
}
/// <summary>
/// Find one record that satisfies @whereConfitions
/// </summary>
/// <param name="whereConditions"></param>
/// <returns></returns>
public static T FindOne(WhereGroup whereConditions)
{
T obj = new T();
DataRow row = DBConn.Instance.GetOneFromTable(obj.Table(), whereConditions);
if (row == null)
{
throw new KeyNotFoundException();
}
obj.Apply(row);
return(obj);
}
private void ChangeDateTypeColumnsName(WhereGroup filter, TableDataModel tableInfo)
{
if (filter == null)
{
return;
}
foreach (var where in filter.WhereStatements)
{
if (tableInfo.Columns.ContainsKey(where.ColumnAlias) && tableInfo.Columns[where.ColumnAlias] == Enums.ColumnTypes.Date && where.Value.Length == 4)
{
where.ColumnAlias = $"year({where.ColumnAlias})";
}
}
filter.WhereGroups.ForEach(x => ChangeDateTypeColumnsName(x, tableInfo));
}
public Query AddWhere(int pGroupIndex, string pTableName, string pFieldName, FieldType pType, Operator pOperator = Operator._Equal, string value = "", Condition pCondition = Condition._And)
{
WhereGroup wg;
if (this.WhereGroups.Count > pGroupIndex)
{
wg = this.WhereGroups[pGroupIndex];
wg.whereCases.Add(new WhereCase(pGroupIndex, pTableName, pFieldName, pType, pOperator, value, pCondition));
}
else
{
wg = new WhereGroup();
wg.whereCases.Add(new WhereCase(pGroupIndex, pTableName, pFieldName, pType, pOperator, value, pCondition));
this.WhereGroups.Add(wg);
}
return(this);
}
public Query AddWhere(int pGroupIndex, string pTableName, string pFieldName, string pJoinTable, string pJoinField, JoinType pJoinType = JoinType._InnerJoin, Condition pCondition = Condition._And)
{
WhereGroup wg;
if (this.WhereGroups.Count > pGroupIndex)
{
wg = this.WhereGroups[pGroupIndex];
wg.whereCases.Add(new WhereCase(pGroupIndex, pTableName, pFieldName, pJoinTable, pJoinField,
pJoinType, pCondition));
}
else
{
wg = new WhereGroup();
wg.whereCases.Add(new WhereCase(pGroupIndex, pTableName, pFieldName, pJoinTable, pJoinField,
pJoinType, pCondition));
this.WhereGroups.Add(wg);
}
return(this);
}
private List <string> ParseJoinCondition(WhereGroup condition, string fromAlias, string toAlias)
{
var parsedWheres = new List <string>();
var statement = "(";
foreach (var whereStatement in condition.WhereStatements)
{
if (parsedWheres.Count > 0)
{
statement = string.Empty;
}
statement += UseAlias(whereStatement, fromAlias) ?
$"{fromAlias}.{ whereStatement.ColumnAlias} " :
$"{whereStatement.ColumnAlias}";
statement += $"{whereStatement.Comparison} {toAlias}.{whereStatement.CompareWithColumn.ColumnAlias}";
statement += whereStatement.UseOr ? " or " : " and ";
parsedWheres.Add(statement);
}
if (parsedWheres.Count == 0 && condition.WhereGroups.Count > 0)
{
parsedWheres.Add(statement);
}
parsedWheres.AddRange(condition.WhereGroups.SelectMany(where => ParseJoinCondition(where, fromAlias, toAlias)));
var last = parsedWheres.LastOrDefault();
if (last != null)
{
var lastOr = last.LastIndexOf("or");
var lastAnd = last.LastIndexOf("and");
if (lastOr != -1 || lastAnd != -1)
{
last = last.Insert(lastOr > lastAnd ? lastOr : lastAnd, ")");
parsedWheres[parsedWheres.Count - 1] = last;
}
}
return(parsedWheres);
}
/// <summary>
/// Ctor. Creates the original WHERE clause; that <see cref="WhereGroup"/> is a logical AND.
/// </summary>
public OQuery()
{
Where = new WhereGroup(LogicalGroupComposition.And);
}
