public virtual GridData GetGridData(HttpContext context) { string view = context.Request["view"]; string columns = context.Request["columns"]; string sortname = context.Request["sortname"]; string sortorder = context.Request["sortorder"]; string _pagenumber = context.Request["page"]; string _pagesize = context.Request["pagesize"]; string procedure = context.Request["procedure"]; string where = context.Request["where"]; if (string.IsNullOrEmpty(procedure)) { int?pagenumber = null, pagesize = null; //可分页 if (!_pagenumber.IsNullOrEmpty() && !_pagesize.IsNullOrEmpty()) { pagenumber = _pagenumber.ToInt(); pagesize = _pagesize.ToInt(); if (pagesize == 0) { pagesize = 20; } } //可排序 if (!sortname.IsNullOrEmpty()) { sortorder = sortorder.IsNullOrEmpty() || sortorder.EqualsTo("asc") ? "asc" : "desc"; } /* * where 为 json参数,格式如下: * { * "roles":[ * {"field":"ID","value":112,"op":"equal"}, * {"field":"Time","value":"2011-3-4","op":"greaterorequal"} * ], * "op":"and","groups":null * } * FilterTranslator可以为以上格式的where表达式翻译为sql,并生成参数列表(FilterParam[]) */ var dpRule = new DataPrivilegeRule(DbHelper.Db); var whereTranslator = new FilterTranslator(); if (!where.IsNullOrEmpty()) { //反序列化Filter Group JSON whereTranslator.Group = JSONHelper.FromJson <FilterGroup>(where); //合并数据权限规则 whereTranslator.Group = dpRule.GetRuleGroup(view, whereTranslator.Group); } else { //如果没有定义前台搜索规则 whereTranslator.Group = dpRule.GetRuleGroup(view, whereTranslator.Group); } if (whereTranslator.Group != null && whereTranslator.Group.rules != null) { whereTranslator.Group.rules = whereTranslator.Group.rules.Where(i => i.value != null && i.value.ToString() != "全部" && i.value != "" && i.value != "undefined").ToList(); } whereTranslator.Translate(); if (string.IsNullOrEmpty(columns)) { return(GetGridData(view, whereTranslator.CommandText, sortname, sortorder, pagenumber, pagesize, whereTranslator.Parms.ToArray())); } else { return(GetGridData(view, columns, whereTranslator.CommandText, sortname, sortorder, pagenumber, pagesize, whereTranslator.Parms.ToArray())); } } else { if (string.IsNullOrEmpty(where)) { return(GetGridData(procedure, null)); } else { var whereTranslator = new FilterTranslator(); whereTranslator.Group = JSONHelper.FromJson <FilterGroup>(where); DbParameter[] parms = new DbParameter[whereTranslator.Group.rules.Count]; for (int i = 0; i < parms.Count(); i++) { DbParameter p = this.DbProvider.DbProviderFactory.CreateParameter(); FilterRule rule = whereTranslator.Group.rules[i]; p.ParameterName = "@" + rule.field.ToLower(); switch (rule.type) { case "string": p.DbType = DbType.String; break; case "int": p.DbType = DbType.Int32; break; } p.Value = string.IsNullOrEmpty(rule.value.ToString()) || rule.value.ToString().Equals("全部")?"":rule.value.ToString(); parms[i] = p; } return(GetGridData(procedure, parms)); } } }
public string TranslateRule(FilterRule rule) { StringBuilder bulider = new StringBuilder(); if (rule == null) return " 1=1 "; //如果字段名采用了 用户信息参数 if (currentParmMatch.ContainsKey(rule.field)) { var field = currentParmMatch[rule.field](); bulider.Append(paramPrefixToken + CreateFilterParam(field, "int")); } else { bulider.Append(leftToken + rule.field + rightToken); } //操作符 bulider.Append(GetOperatorQueryText(rule.op)); var op = rule.op.ToLower(); if (op == "like" || op == "endwith") { var value = rule.value.ToString(); if (!value.StartsWith(this.likeToken.ToString())) { rule.value = this.likeToken + value; } } if (op == "like" || op == "startwith") { var value = rule.value.ToString(); if (!value.EndsWith(this.likeToken.ToString())) { rule.value = value + this.likeToken; } } if (op == "in" || op == "notin") { var values = rule.value.ToString().Split(','); var appended = false; bulider.Append("("); foreach (var value in values) { if (appended) bulider.Append(","); //如果值使用了 用户信息参数 比如: in ({CurrentRoleID},4) if (currentParmMatch.ContainsKey(value)) { var val = currentParmMatch[value](); bulider.Append(paramPrefixToken + CreateFilterParam(val, "int")); } else { bulider.Append(paramPrefixToken + CreateFilterParam(value, rule.type)); } appended = true; } bulider.Append(")"); } //is null 和 is not null 不需要值 else if (op != "isnull" && op != "isnotnull") { //如果值使用了 用户信息参数 比如 [EmptID] = {CurrentEmptID} if (rule.value != null && currentParmMatch.ContainsKey(rule.value.ToStr())) { var value = currentParmMatch[rule.value.ToStr()](); bulider.Append(paramPrefixToken + CreateFilterParam(value, "int")); } else { bulider.Append(paramPrefixToken + CreateFilterParam(rule.value, rule.type)); } } return bulider.ToString(); }
public string TranslateRule(FilterRule rule) { StringBuilder bulider = new StringBuilder(); if (rule == null) { return(" 1=1 "); } //如果字段名采用了 用户信息参数 if (currentParmMatch.ContainsKey(rule.field)) { var field = currentParmMatch[rule.field](); bulider.Append(paramPrefixToken + CreateFilterParam(field, "int")); } else { if (currentStringMath.ContainsKey(rule.field)) { var field = currentStringMath[rule.field](); bulider.Append(paramPrefixToken + CreateFilterParam(field, "sting")); } bulider.Append(leftToken + rule.field + rightToken); } //操作符 bulider.Append(GetOperatorQueryText(rule.op)); var op = rule.op.ToLower(); if (op == "like" || op == "endwith") { var value = rule.value.ToString().Trim(); if (!value.StartsWith(this.likeToken.ToString())) { rule.value = this.likeToken + value; } } if (op == "like" || op == "startwith") { var value = rule.value.ToString().Trim(); if (!value.EndsWith(this.likeToken.ToString())) { rule.value = value + this.likeToken; } } if (op == "in" || op == "notin") { var values = rule.value.ToString().Split(','); var appended = false; bulider.Append("("); foreach (var value in values) { if (appended) { bulider.Append(","); } //如果值使用了 用户信息参数 比如: in ({CurrentRoleID},4) if (currentParmMatch.ContainsKey(value)) { var val = currentParmMatch[value](); bulider.Append(paramPrefixToken + CreateFilterParam(val, "int")); } else { if (currentStringMath.ContainsKey(value)) { int v = 0; var val = currentStringMath[value](); if (val.ToString() == "'undefined'") { return(string.Empty); } bulider.Append(paramPrefixToken + CreateFilterParam(val, "string")); if (!int.TryParse(val, out v)) { bulider.Replace(bulider.ToString().Substring(bulider.ToString().IndexOf("(") + 1), val); } } else { bulider.Append(paramPrefixToken + CreateFilterParam(value, rule.type)); } } appended = true; } bulider.Append(")"); } //is null 和 is not null 不需要值 else if (op != "isnull" && op != "isnotnull") { //如果值使用了 用户信息参数 比如 [EmptID] = {CurrentEmptID} if (rule.value != null && currentParmMatch.ContainsKey(rule.value.ToStr())) { var value = currentParmMatch[rule.value.ToStr()](); bulider.Append(paramPrefixToken + CreateFilterParam(value, "int")); } else { bulider.Append(paramPrefixToken + CreateFilterParam(rule.value, rule.type)); } } return(bulider.ToString()); }