public virtual JsonResult GetList(string tmplCode, QueryBuilder qb)
{
var listDef = entities.Set <S_UI_List>().SingleOrDefault(c => c.Code == tmplCode);
SQLHelper sqlHeler = SQLHelper.CreateSqlHelper(listDef.ConnName);
UIFO uiFO = FormulaHelper.CreateFO <UIFO>();
var lefSettings = JsonHelper.ToObject(listDef.LayoutGrid);
if (lefSettings.GetValue("isTreeGrid") == true.ToString().ToLower() &&
this.Request["IsLoadChildren"] == true.ToString().ToLower())
{
//异步加载树列表
var node = String.IsNullOrEmpty(this.Request["NodeInfo"]) ? new Dictionary <string, object>() :
JsonHelper.ToObject <Dictionary <string, object> >(this.Request["NodeInfo"]);
var defaultValues = JsonHelper.ToList(listDef.DefaultValueSettings);
var nodeTypeField = String.IsNullOrEmpty(lefSettings.GetValue("NodeTypeField")) ? "NodeType" : lefSettings.GetValue("NodeTypeField");
var nodeType = String.IsNullOrEmpty(node.GetValue(nodeTypeField)) ? "Root" : node.GetValue(nodeTypeField);
var treeGridDataSource = defaultValues.Where(c => c.GetValue("treeGridSource") == true.ToString().ToLower()).ToList();
var dataSource = new Dictionary <string, object>();
if (treeGridDataSource.Count == 1)
{
dataSource = treeGridDataSource.FirstOrDefault();
}
else
{
dataSource = treeGridDataSource.FirstOrDefault(c => c.GetValue("Code").Split(',').Contains(nodeType));
}
if (dataSource == null)
{
return(Json(new DataTable()));
}
var db = SQLHelper.CreateSqlHelper(dataSource.GetValue("ConnName"));
var sourceSQL = dataSource.GetValue("SQL");
sourceSQL = uiFO.ReplaceDicString(sourceSQL, null, node);
var children = db.ExecuteDataTable(sourceSQL, new SearchCondition());
return(Json(children));
}
else
{
string sql = listDef.SQL;
#region TAB查询
//解决tab查询需要在sql中间的问题
var tabData = Request["queryTabData"];
var fields = JsonHelper.ToList(listDef.LayoutField);
var tabEmbeddedFields = new Dictionary <string, string>();
foreach (var field in fields)
{
if (!field.ContainsKey("Settings"))
{
continue;
}
var settings = JsonHelper.ToObject(field["Settings"].ToString());
if (!settings.ContainsKey("TabSearchName") || settings["TabSearchName"].ToString() == "")
{
continue;
}
if (!settings.ContainsKey("Embedded") || settings["Embedded"].ToString() != true.ToString().ToLower())
{
continue;
}
var cnd = qb.Items.FirstOrDefault(c => c.Field == field["field"].ToString());
if (cnd != null)
{
qb.Items.Remove(cnd);
var value = cnd.Value.ToString();
if (value.Contains(",") && (cnd.Method == QueryMethod.InLike || cnd.Method == QueryMethod.In))
{
value = value.Replace(",", "','");
}
tabEmbeddedFields.Add(field["field"].ToString(), value);
}
else
{
if (!settings.ContainsKey("EnumKey") || settings["EnumKey"].ToString() == "")
{
continue;
}
string enumKey = settings["EnumKey"].ToString();
var enumList = new List <Dictionary <string, object> >();
if (enumKey.StartsWith("["))
{
enumList = JsonHelper.ToList(enumKey);
}
else
{
var enumServcie = FormulaHelper.GetService <IEnumService>();
var dataTable = enumServcie.GetEnumTable(enumKey);
enumList = FormulaHelper.DataTableToListDic(dataTable);
}
var value = String.Join(",", enumList.Select(c => c["value"].ToString()).ToList());
if (value.Contains(","))
{
value = value.Replace(",", "','");
}
tabEmbeddedFields.Add(field["field"].ToString(), value);
}
}
#endregion
sql = uiFO.ReplaceString(sql, null, tabEmbeddedFields);
#region 地址栏过滤
DataTable dtTmpl = sqlHeler.ExecuteDataTable(string.Format("SELECT * FROM ({0}) T WHERE 1=2", sql));
foreach (string key in Request.QueryString.Keys)
{
if (string.IsNullOrEmpty(key))
{
continue;
}
if ("ID,FullID,FULLID,TmplCode,IsPreView,_winid,_t".Split(',').Contains(key) || key.StartsWith("$"))
{
continue;
}
if (dtTmpl.Columns.Contains(key))
{
qb.Add(key, QueryMethod.In, Request[key]);
}
;
}
#endregion
//列表定义支持加密字段
if (System.Configuration.ConfigurationManager.AppSettings["FieldEncryption"] == "True")
{
StringBuilder sbField = new StringBuilder();
foreach (DataColumn col in dtTmpl.Columns)
{
if (col.DataType == typeof(byte[]) && fields.Count(c => c["field"].ToString() == col.ColumnName) > 0)
{
sbField.AppendFormat(",{0}=convert(nvarchar(500), DecryptByKey({0}))", col.ColumnName);
}
else
{
sbField.AppendFormat(",{0}", col.ColumnName);
}
}
sql = string.Format("select {1} from ({0}) as dt0", sql, sbField.ToString().Trim(' ', ','));
}
GridData data = null;
if (listDef.LayoutGrid.Contains("\"showPager\":\"false\""))
{
qb.PageSize = 0;
}
data = sqlHeler.ExecuteGridData(sql, qb, listDef.OrderBy);
#region 计算汇总
StringBuilder sb = new StringBuilder();
foreach (var field in fields)
{
if (field.ContainsKey("Settings") == false)
{
continue;
}
var settings = JsonHelper.ToObject(field["Settings"].ToString());
if (settings.ContainsKey("Collect") == false || settings["Collect"].ToString() == "")
{
continue;
}
if (Config.Constant.IsOracleDb)
{
sb.AppendFormat(",{1}({0}) as {0}", field["field"], settings["Collect"]);
}
else
{
sb.AppendFormat(",{0}={1}({0})", field["field"], settings["Collect"]);
}
if (settings["Collect"].ToString() == "sum")
{
data.sumData.Add(field["field"].ToString(), null);
}
else
{
data.avgData.Add(field["field"].ToString(), null);
}
}
if (sb.Length > 0)
{
string companyAuth = "";
if (System.Configuration.ConfigurationManager.AppSettings["CorpAuthEnabled"] == "True")
{
var dt = sqlHeler.ExecuteDataTable(string.Format("select * from ({0}) tempDt1 where 1=2", sql));
if (dt.Columns.Contains("CompanyID"))
{
companyAuth = string.Format(" and CompanyID='{0}'", FormulaHelper.GetUserInfo().UserCompanyID);
}
}
string collectSql = "";
SearchCondition authCnd = FormulaHelper.CreateAuthDataFilter();
collectSql = string.Format("select * from ({0}) sourceTable1 where 1=1 {1} {2}", sql, authCnd.GetWhereString(false), companyAuth);
collectSql = string.Format("select {2} from ({0}) sourceTable {1}", collectSql, qb.GetWhereString(), sb.ToString().Trim(','));
DataTable dtCollect = sqlHeler.ExecuteDataTable(collectSql);
foreach (DataColumn col in dtCollect.Columns)
{
if (data.sumData.ContainsKey(col.ColumnName))
{
data.sumData[col.ColumnName] = dtCollect.Rows[0][col] is DBNull ? 0 : dtCollect.Rows[0][col];
}
else
{
data.avgData[col.ColumnName] = dtCollect.Rows[0][col] is DBNull ? 0 : dtCollect.Rows[0][col];
}
}
}
//汇总数据小数点
foreach (var field in fields)
{
if (field.ContainsKey("Settings") == false)
{
continue;
}
var settings = JsonHelper.ToObject(field["Settings"].ToString());
if (settings.ContainsKey("Collect") == false || settings["Collect"].ToString() == "")
{
continue;
}
int decimalPlaces = 2;
if (settings.ContainsKey("decimalPlaces") && settings["decimalPlaces"].ToString() != "")
{
decimalPlaces = Convert.ToInt32(settings["decimalPlaces"]);
}
string fieldCode = field["field"].ToString();
if (data.sumData.ContainsKey(fieldCode))
{
data.sumData[fieldCode] = string.Format("{0:F" + decimalPlaces + "}", Convert.ToDouble(data.sumData[fieldCode]));
}
else
{
data.avgData[fieldCode] = string.Format("{0:F" + decimalPlaces + "}", Convert.ToDouble(data.avgData[fieldCode]));
}
}
#endregion
return(Json(data));
}
}
