public QueryFilter(WPOTableObject table)
{
Table = table;
Clear();
}
private string CreateQuery(QueryFilter filter)
{
StringBuilder sb = new StringBuilder("SELECT * FROM ");
sb.Append(filter.Table.TableName);
//JOIN CLAUSE FOR CLASS TABLE INHERITANCE
if (filter.Table.Inheritance == InheritanceType.ClassTable)
{
WPOTableObject tableObj = filter.Table;
while (tableObj.BaseTable != null)
{
sb.Append(" JOIN ");
sb.Append(tableObj.BaseTable.TableName);
sb.Append(" ON ");
sb.Append(tableObj.TableName);
sb.Append(".");
sb.Append(tableObj.PrimaryKey.ColumnName);
sb.Append(" = ");
sb.Append(tableObj.BaseTable.TableName);
sb.Append(".");
sb.Append(tableObj.BaseTable.PrimaryKey.ColumnName);
tableObj = tableObj.BaseTable;
}
}
//WHERE CLAUSE
string whereString = string.Empty;
QueryFilter.Statement.Operator prevOpearator = QueryFilter.Statement.Operator.UNDEFINED;
while (filter.Conditions != null)
{
if (string.IsNullOrEmpty(whereString))
{
whereString = filter.Conditions.Value;
}
else
{
whereString = "(" + whereString + ") " +
(prevOpearator == QueryFilter.Statement.Operator.OR ? "OR" : "AND") +
" (" + filter.Conditions.Value + ")";
}
prevOpearator = filter.Conditions.LogicalOperator;
filter.Conditions = filter.Conditions.NextStatement;
}
if (!string.IsNullOrEmpty(whereString))
{
sb.Append(" WHERE ");
sb.Append(whereString);
}
//ORDER CLAUSE
string orderString = string.Empty;
while (filter.Orders != null)
{
if (!string.IsNullOrEmpty(orderString))
{
orderString += ", ";
}
orderString += filter.Orders.ColumnName + (filter.Orders.OrderingType == QueryFilter.Order.Direction.ASC ? " ASC" : " DESC");
filter.Orders = filter.Orders.NextOrder;
}
if (!string.IsNullOrEmpty(orderString))
{
sb.Append(" ORDER BY ");
sb.Append(orderString);
}
//OFFSET & LIMIT
if ((filter.Skip.HasValue || filter.Take.HasValue) && string.IsNullOrEmpty(orderString))
{
sb.Append(" ORDER BY ");
sb.Append(filter.Table.PrimaryKey.ColumnName);
}
if (filter.Skip.HasValue || filter.Take.HasValue)
{
sb.Append(" OFFSET ");
sb.Append(filter.Skip ?? 0);
sb.Append(" ROWS");
}
if (filter.Take.HasValue)
{
sb.Append(" FETCH NEXT ");
sb.Append(filter.Take.Value);
sb.Append(" ROWS ONLY");
}
return(sb.Append(";").ToString());
}