JQGrid Advance Search - Können wir UND- und ODER-Operatoren gleichzeitig verwenden?
Ich verwende die JQGrid Advance Search-FunktionmultipleSearch: true, multipleGroup: true
.
Ich verwende auch Asp.net MVC und die klassische gespeicherte Prozedur ado.net +.
Wann immer Benutzer bei JGRID nach Daten suchen, gebe ich diese Suchkriterien als Parameterwerte an die gespeicherte Prozedur weiter. Sowie ...
<code>Select * From tableName Where @WhereClauseDynamic </code>
Also habe ich die Klasse "Where Clause Generator" erstellt.
<code>[ModelBinder(typeof(GridModelBinder))] public class JqGrid_Setting_VewModel { public bool IsSearch { get; set; } public int PageSize { get; set; } public int PageIndex { get; set; } public string SortColumn { get; set; } public string SortOrder { get; set; } public string Where { get; set; } } public class WhereClauseGenerator { private static readonly string[] FormatMapping = { " ({0} = '{1}') ", // "eq" - equal " ({0} <> {1}) ", // "ne" - not equal " ({0} < {1}) ", // "lt" - less than " ({0} <= {1}) ", // "le" - less than or equal to " ({0} > {1}) ", // "gt" - greater than " ({0} >= {1}) ", // "ge" - greater than or equal to " ({0} LIKE '{1}%') ", // "bw" - begins with " ({0} NOT LIKE '{1}%') ", // "bn" - does not begin with " ({0} LIKE '%{1}') ", // "ew" - ends with " ({0} NOT LIKE '%{1}') ", // "en" - does not end with " ({0} LIKE '%{1}%') ", // "cn" - contains " ({0} NOT LIKE '%{1}%') " // "nc" - does not contain }; public string Generator(Filter _Filter) { var sb = new StringBuilder(); foreach (Rule rule in _Filter.rules) { if (sb.Length != 0) sb.Append(_Filter.groupOp); sb.AppendFormat(FormatMapping[(int)rule.op], rule.field, rule.data); } return sb.ToString(); } } public class GridModelBinder : IModelBinder { public object BindModel(ControllerContext controllerContext, ModelBindingContext bindingContext) { try { var request = controllerContext.HttpContext.Request; var serializer = new JavaScriptSerializer(); var _WhereClauseGenerator = new WhereClauseGenerator(); var _IsSearch = bool.Parse(request["_search"] ?? "false"); var _PageIndex = int.Parse(request["page"] ?? "1"); var _PageSize = int.Parse(request["rows"] ?? "10"); var _SortColumn = request["sidx"] ?? ""; var _SortOrder = request["sord"] ?? "asc"; var _Where = request["filters"] ?? ""; return new JqGrid_Setting_VewModel { IsSearch = _IsSearch, PageIndex = _PageIndex, PageSize = _PageSize, SortColumn = _SortColumn, SortOrder = _SortOrder, Where = (_IsSearch == false || string.IsNullOrEmpty(_Where)) ? string.Empty : _WhereClauseGenerator.Generator(serializer.Deserialize<Filter>(_Where)) }; } catch { return null; } } } [DataContract] public class Filter { [DataMember] public GroupOp groupOp { get; set; } [DataMember] public List<Rule> rules { get; set; } } [DataContract] public class Rule { [DataMember] public string field { get; set; } [DataMember] public Operations op { get; set; } [DataMember] public string data { get; set; } } public enum GroupOp { AND, OR } public enum Operations { eq, // "equal" ne, // "not equal" lt, // "less" le, // "less or equal" gt, // "greater" ge, // "greater or equal" bw, // "begins with" bn, // "does not begin with" //in, // "in" //ni, // "not in" ew, // "ends with" en, // "does not end with" cn, // "contains" nc // "does not contain" } </code>
Wenn ich den oberen Code verwende, stimmt alles, wenn ich so suche
<code>{ "groupOp":"AND", "rules":[{"field":"Seminar_Code","op":"eq","data":"MED01"}, {"field":"Seminar_Code","op":"eq","data":"CMP05"}],"groups":[] } sb.ToString() // Output vlaue " (Seminar_Code = 'MED01') AND (Seminar_Code = 'CMP05') " </code>
Es ist also völlig richtig.
Aber wenn es um komplexere Suchanfragen wie diese geht ...
<code>{ "groupOp":"AND", "rules":[{"field":"Seminar_Code","op":"eq","data":"MED01"}, {"field":"Seminar_Code","op":"eq","data":"CMP05"}], "groups":[{ "groupOp":"OR", "rules": [{"field":"Seminar_Code","op":"eq","data":"CMP01"}],"groups":[]}] } sb.ToString() // Actual Output value is like that below " (Seminar_Code = 'MED01') AND (Seminar_Code = 'CMP05') " </code>
Aber was ich erwartet hatte, ist wie folgt ..
<code>" ((Seminar_Code = 'MED01') AND (Seminar_Code = 'CMP05')) OR ( Seminar_Code = 'CMP01' ) " </code>
Also wie könnte ich es richtig machen?
Unterstützt JQGrid mehrere Gruppenoperationen wie "AND" + "OR"? Unterstützt dieser nur einen Bediener gleichzeitig? Können wir "AND" und "OR" gleichzeitig verwenden?
Jeder Vorschlag wird geschätzt.