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