/// <summary>
/// 获取 ligerGrid(Tree) 所需要的JSON
/// </summary>
/// <param name="dbContext"></param>
/// <param name="context"></param>
/// <returns></returns>
public static string GetGridTreeJSON(this DbContext dbContext, HttpContext context)
{
string view = context.Request["view"];
string where = context.Request["where"];
string idfield = context.Request["idfield"];
string pidfield = context.Request["pidfield"];
string sql = "select * from [{0}] where {1}";
var whereTranslator = new FilterTranslator();
var dpRule = new DataPrivilegeRule(dbContext);
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);
}
whereTranslator.Translate();
where = whereTranslator.CommandText;
sql = sql.FormatWith(view, where.IsNullOrEmpty() ? " 1=1 " : where);
//创建command
var cmd = CreateCommand(dbContext, sql, whereTranslator.Parms.ToArray());
//使用liger.Data内置的数据适配器预处理command
//比如对整数类型、日期类型的处理
dbContext.Db.DbProvider.PrepareCommand(cmd);
//获取树格式对象的JSON，这个方法首先会执行command，并且对结果进行格式化(转化为树格式)
List <Hashtable> o = JSONHelper.ArrayToTreeData(cmd, idfield, pidfield) as List <Hashtable>;
string json = @"{""Rows"":" + JSONHelper.ToJson(o) + @",""Total"":""" + o.Count + @"""}";
return(json);
}
/// <summary>
/// 获取 ligerTree 所需要的JSON
/// </summary>
/// <param name="dbContext"></param>
/// <param name="context"></param>
/// <returns></returns>
public static string GetTreeJSON(this DbContext dbContext, HttpContext context)
{
string view = context.Request["view"];
string where = context.Request["where"];
string idfield = context.Request["idfield"];
string pidfield = context.Request["pidfield"];
string textfield = context.Request["textfield"];
string iconfield = context.Request["iconfield"];
string iconroot = context.Request["iconroot"] ?? "";
string root = context.Request["root"];
string rooticon = context.Request["rooticon"];
string sql = "select {0} from [{1}] where {2}";
string sqlselect = "[" + textfield + "] as [text]";
if (iconfield.HasValue())
{
sqlselect += ",'" + iconroot + "'+[" + iconfield + "] as [icon]";
}
if (idfield.HasValue())
{
sqlselect += ",[" + idfield + "] as [id]";
}
if (idfield.HasValue())
{
sqlselect += ",[" + idfield + "]";
}
if (pidfield.HasValue())
{
sqlselect += ",[" + pidfield + "]";
}
/*
* 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 whereTranslator = new FilterTranslator();
var dpRule = new DataPrivilegeRule(dbContext);
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);
}
whereTranslator.Translate();
where = whereTranslator.CommandText;
sql = sql.FormatWith(sqlselect, view, where.IsNullOrEmpty() ? " 1=1 " : where);
//创建command
var cmd = CreateCommand(dbContext, sql, whereTranslator.Parms.ToArray());
//使用liger.Data内置的数据适配器预处理command
//比如对整数类型、日期类型的处理
dbContext.Db.DbProvider.PrepareCommand(cmd);
//获取树格式对象的JSON，这个方法首先会执行command，并且对结果进行格式化(转化为树格式)
string json = JSONHelper.GetArrayJSON(cmd, idfield, pidfield);
if (!root.IsNullOrEmpty())
{
json = @"[{""text"":""" + root + @""",""children"":" + json;
if (rooticon.HasValue())
{
json += @",""icon"":""" + rooticon + @"""";
}
json += "}]";
}
return(json);
}
/// <summary>
/// 获取 ligerComboBox 所需要的JSON
/// </summary>
/// <param name="dbContext"></param>
/// <param name="context"></param>
/// <returns></returns>
public static string GetSelectJSON(this DbContext dbContext, HttpContext context)
{
string view = context.Request["view"];
string where = context.Request["where"];
string idfield = context.Request["idfield"];
string textfield = context.Request["textfield"];
string distinct = context.Request["distinct"];
string defaultValues = context.Request["defaultValues"];
string needAll = context.Request["needAll"];
string sql = "select "
+ ((distinct.HasValue() && distinct != "false") ? "distinct" : "")
+ @" {0} from [{1}] where {2}";
var sqlselect = "";
if (idfield.HasValue())
{
sqlselect += ",[" + idfield + "] as [id]";
sqlselect += ",[" + idfield + "] as [value]";
}
if (textfield.HasValue())
{
sqlselect += ",[" + textfield + "] as [text]";
}
var whereTranslator = new FilterTranslator();
if (!where.IsNullOrEmpty())
{
//反序列化Filter Group JSON
whereTranslator.Group = JSONHelper.FromJson <FilterGroup>(where);
if (dbContext != DbHelper.DBCustome)
{
var dpRule = new DataPrivilegeRule(DbHelper.Db);
whereTranslator.Group = dpRule.GetRuleGroup(view, whereTranslator.Group);
}
//翻译，这一步是必须的
whereTranslator.Translate();
where = whereTranslator.CommandText;
}
sql = sql.FormatWith(sqlselect.HasValue() ? sqlselect.Substring(1) : "*", view, where.IsNullOrEmpty() ? " 1=1 " : where);
//创建command
var cmd = CreateCommand(dbContext, sql, whereTranslator.Parms.ToArray());
//使用liger.Data内置的数据适配器预处理command
//比如对整数类型、日期类型的处理
dbContext.Db.DbProvider.PrepareCommand(cmd);
var ds = dbContext.ExecuteDataSet(cmd);
if (!defaultValues.IsNullOrEmpty())
{
foreach (string item in defaultValues.Split(','))
{
DataRow newRow = ds.Tables[0].NewRow();
newRow["id"] = item;
newRow["value"] = item;
newRow["text"] = item;
ds.Tables[0].Rows.Add(newRow);
}
}
if (needAll == "1")
{
DataRow row = ds.Tables[0].NewRow();
row["id"] = "全部";
row["value"] = "全部";
row["text"] = "全部";
ds.Tables[0].Rows.Add(row);
}
var json = new Liger.Common.JSON.DataSetJSONSerializer().Serialize(ds);
return(json);
}
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));
}
}
}
