public static string GET_FILTERS_SQL(FilterList filters, string tableAlias, CultureInfo cultureInfo, Dictionary <String, ForeignField> foreignFields = null)
{
string queryPattern = "(TRUE {0})";
string stringPattern = @"{0}.""{1}"" {2} '{3}'";
string intPattern = @"{0}.""{1}"" {2} {3}";
string likePattern = @"{0}.""{1}"" {2} '%{3}%'";
string likeIntPattern = @"TRIM(TO_CHAR({0}.""{1}"", '999999999999')) {2} '%{3}%'";
string likeDatePattern = @"TO_CHAR({0}.""{1}"", '" + cultureInfo.DateTimeFormat.ShortDatePattern + "') {2} '%{3}%'";
string StartPattern = @"{0}.""{1}"" {2} '{3}%'";
string StartIntPattern = @"TRIM(TO_CHAR({0}.""{1}"", '999999999999')) {2} '{3}%'";
string StartDatePattern = @"TO_CHAR({0}.""{1}"", '" + cultureInfo.DateTimeFormat.ShortDatePattern + "') {2} '{3}%'";
string betweenPattern = @"{0}.""{1}"" {2} '{3}' AND '{4}'";
string betweenIntPattern = @"{0}.""{1}"" {2} {3} AND {4}";
string query = string.Empty;
if (filters != null)
{
foreach (FilterItem item in filters)
{
Type entityRecordType = AppControllerBase.AppControler.RecordEntities[item.EntityType];
switch (item.FilterProperty)
{
case IFilterProperty.All:
{
query += @"
AND (FALSE " ;
ICollection <Column> cols = nHManager.Instance.GetTableColumns(entityRecordType);
foreach (Column col in nHManager.Instance.GetTableColumns(entityRecordType))
{
if (col.Value.Type.Name == "String")
{
query += " OR ";
query += String.Format(likePattern, tableAlias, col.Name, GET_OPERATOR(item.Operation), item.ValueToString.ToLower());
}
else if ((col.Value.Type.Name == "DateTime") ||
(col.Value.Type.Name == "Date"))
{
query += " OR ";
query += String.Format(likeDatePattern, tableAlias, col.Name, GET_OPERATOR(item.Operation), item.ValueToSQL.ToString().ToLower());
}
else if ((col.Value.Type.Name == "Int32") ||
(col.Value.Type.Name == "Int64"))
{
query += " OR ";
query += String.Format(likeIntPattern, tableAlias, col.Name, GET_OPERATOR(item.Operation), item.ValueToString.ToString().ToLower());
}
}
if (foreignFields != null)
{
NHibernate.Mapping.Column fcol;
foreach (KeyValuePair <String, ForeignField> field in foreignFields)
{
fcol = field.Value.Column;
if (fcol != null)
{
if (fcol.Value.Type.Name == "String")
{
query += " OR ";
query += String.Format(likePattern, field.Value.TableAlias, fcol.Name, GET_OPERATOR(item.Operation), item.ValueToString.ToLower());
}
else if ((fcol.Value.Type.Name == "DateTime") ||
(fcol.Value.Type.Name == "Date"))
{
query += " OR ";
query += String.Format(likeDatePattern, field.Value.TableAlias, fcol.Name, GET_OPERATOR(item.Operation), item.ValueToSQL);
}
else if ((fcol.Value.Type.Name == "Int32") ||
(fcol.Value.Type.Name == "Int64"))
{
query += " OR ";
query += String.Format(likeIntPattern, field.Value.TableAlias, fcol.Name, GET_OPERATOR(item.Operation), item.ValueToString.ToString().ToLower());
}
}
}
}
query += ")";
}
break;
case IFilterProperty.ByParamenter:
{
Column col = GetColumn(item, foreignFields);
string tablealias = (foreignFields != null && foreignFields.ContainsKey(item.Property))
? foreignFields[item.Property].TableAlias
: tableAlias;
switch (item.Operation)
{
case Operation.Contains:
query += " AND ";
if (col.Value.Type.Name == "String")
{
query += String.Format(likePattern, tablealias, col.Name, GET_OPERATOR(item.Operation), item.ValueToString.ToLower());
}
else if ((col.Value.Type.Name == "DateTime") ||
(col.Value.Type.Name == "Date"))
{
query += String.Format(likeDatePattern, tablealias, col.Name, GET_OPERATOR(item.Operation), item.ValueToSQL);
}
else if ((col.Value.Type.Name == "Int32") ||
(col.Value.Type.Name == "Int64"))
{
query += String.Format(likeIntPattern, tablealias, col.Name, GET_OPERATOR(item.Operation), item.ValueToString.ToLower());
}
break;
case Operation.StartsWith:
query += " AND ";
if (col.Value.Type.Name == "String")
{
query += String.Format(StartPattern, tablealias, col.Name, GET_OPERATOR(item.Operation), item.ValueToString.ToLower());
}
else if ((col.Value.Type.Name == "DateTime") ||
(col.Value.Type.Name == "Date"))
{
query += String.Format(StartDatePattern, tablealias, col.Name, GET_OPERATOR(item.Operation), item.ValueToSQL);
}
else if ((col.Value.Type.Name == "Int32") ||
(col.Value.Type.Name == "Int64"))
{
query += String.Format(StartIntPattern, tablealias, col.Name, GET_OPERATOR(item.Operation), item.ValueToString.ToLower());
}
break;
case Operation.Between:
query += " AND ";
// TODO : Aggregate second item value to the filter
// Necessary second item value in order to this work
switch (item.TableColumn.Value.Type.Name)
{
case "DateTime":
case "Date":
case "String":
query += String.Format(betweenPattern, tablealias, col.Name, GET_OPERATOR(item.Operation), item.ValueToSQL, item.ValueToSQL);
break;
default:
query += String.Format(betweenIntPattern, tablealias, col.Name, GET_OPERATOR(item.Operation), item.Value, item.Value);
break;
}
break;
default:
query += " AND ";
switch (item.TableColumn.Value.Type.Name)
{
case "DateTime":
case "Date":
case "String":
query += String.Format(stringPattern, tablealias, col.Name, GET_OPERATOR(item.Operation), item.ValueToSQL);
break;
default:
query += String.Format(intPattern, tablealias, col.Name, GET_OPERATOR(item.Operation), item.Value);
break;
}
break;
}
}
break;
}
}
}
return(String.Format(queryPattern, query));
}
public static string GET_FILTERS_SQL(FilterList filters, string tableAlias, Dictionary <String, ForeignField> foreignFields = null)
{
return(GET_FILTERS_SQL(filters, tableAlias, CultureInfo.CurrentCulture, foreignFields));
}
