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)
{
switch (item.FilterProperty)
{
case IFilterProperty.All:
{
query += @"
AND (FALSE " ;
ICollection <Column> cols = nHManager.Instance.GetTableColumns(item.EntityType);
foreach (Column col in nHManager.Instance.GetTableColumns(item.EntityType))
{
if (col.Type is NHibernate.Type.StringType)
{
query += " OR ";
query += String.Format(likePattern, tableAlias, col.Name, GET_OPERATOR(item.Operation), item.ValueToString.ToLower());
}
else if ((col.Type is NHibernate.Type.DateTimeType) ||
(col.Type is NHibernate.Type.DateType))
{
query += " OR ";
query += String.Format(likeDatePattern, tableAlias, col.Name, GET_OPERATOR(item.Operation), item.ValueToString.ToLower());
}
else if ((col.Type is NHibernate.Type.Int32Type) ||
(col.Type is NHibernate.Type.Int64Type))
{
query += " OR ";
query += String.Format(likeIntPattern, tableAlias, col.Name, GET_OPERATOR(item.Operation), item.ValueToString.ToLower());
}
}
if (foreignFields != null)
{
NHibernate.Mapping.Column fcol;
foreach (KeyValuePair <String, ForeignField> field in foreignFields)
{
fcol = field.Value.Column;
if (fcol != null)
{
if (fcol.Type is NHibernate.Type.StringType)
{
query += " OR ";
query += String.Format(likePattern, field.Value.TableAlias, fcol.Name, GET_OPERATOR(item.Operation), item.ValueToString.ToLower());
}
else if ((fcol.Type is NHibernate.Type.DateTimeType) ||
(fcol.Type is NHibernate.Type.DateType))
{
query += " OR ";
query += String.Format(likeDatePattern, field.Value.TableAlias, fcol.Name, GET_OPERATOR(item.Operation), item.ValueToString.ToLower());
}
else if ((fcol.Type is NHibernate.Type.Int32Type) ||
(fcol.Type is NHibernate.Type.Int64Type))
{
query += " OR ";
query += String.Format(likeIntPattern, field.Value.TableAlias, fcol.Name, GET_OPERATOR(item.Operation), item.ValueToString.ToLower());
}
}
}
}
query += ")";
}
break;
case IFilterProperty.ByParamenter:
{
Column col = nHManager.Instance.GetTableColumn(item.EntityType, item.Property);
switch (item.Operation)
{
case Operation.Contains:
query += " AND ";
if (col.Type is NHibernate.Type.StringType)
{
query += String.Format(likePattern, tableAlias, col.Name, GET_OPERATOR(item.Operation), item.ValueToString.ToLower());
}
else if ((col.Type is NHibernate.Type.DateTimeType) ||
(col.Type is NHibernate.Type.DateType))
{
query += String.Format(likeDatePattern, tableAlias, col.Name, GET_OPERATOR(item.Operation), item.ValueToString.ToLower());
}
else if ((col.Type is NHibernate.Type.Int32Type) ||
(col.Type is NHibernate.Type.Int64Type))
{
query += String.Format(likeIntPattern, tableAlias, col.Name, GET_OPERATOR(item.Operation), item.ValueToString.ToLower());
}
break;
case Operation.StartsWith:
query += " AND ";
if (col.Type is NHibernate.Type.StringType)
{
query += String.Format(StartPattern, tableAlias, col.Name, GET_OPERATOR(item.Operation), item.ValueToString.ToLower());
}
else if ((col.Type is NHibernate.Type.DateTimeType) ||
(col.Type is NHibernate.Type.DateType))
{
query += String.Format(StartDatePattern, tableAlias, col.Name, GET_OPERATOR(item.Operation), item.ValueToString.ToLower());
}
else if ((col.Type is NHibernate.Type.Int32Type) ||
(col.Type is NHibernate.Type.Int64Type))
{
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
if ((item.TableColumn.Type is NHibernate.Type.DateTimeType) ||
(item.TableColumn.Type is NHibernate.Type.DateType) ||
(item.TableColumn.Type is NHibernate.Type.StringType))
{
query += String.Format(betweenPattern, tableAlias, item.TableColumn.Name, GET_OPERATOR(item.Operation), item.Value.ToString(), item.Value.ToString());
}
else
{
query += String.Format(betweenIntPattern, tableAlias, item.TableColumn.Name, GET_OPERATOR(item.Operation), item.Value, item.Value);
}
break;
default:
query += " AND ";
if ((item.TableColumn.Type is NHibernate.Type.DateTimeType) ||
(item.TableColumn.Type is NHibernate.Type.DateType) ||
(item.TableColumn.Type is NHibernate.Type.StringType))
{
query += String.Format(stringPattern, tableAlias, item.TableColumn.Name, GET_OPERATOR(item.Operation), item.Value.ToString());
}
else
{
query += String.Format(intPattern, tableAlias, item.TableColumn.Name, GET_OPERATOR(item.Operation), item.Value);
}
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));
}