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)); }