public ActionResult PageView(string tmplCode)
{
UIFO uiFO = FormulaHelper.CreateFO <UIFO>();
ViewBag.ListHtml = uiFO.CreateListHtml(tmplCode);
ViewBag.Script = uiFO.CreateListScript(tmplCode);
return(View());
}
public string ExportPDF(string fileName, string formID, string tmpCode)
{
SQLHelper sqlHeper = SQLHelper.CreateSqlHelper(ConnEnum.Base);
var dtWordTmpl = sqlHeper.ExecuteDataTable(string.Format("select * from S_UI_Word where Code='{0}'", tmpCode));
if (dtWordTmpl.Rows.Count == 0)
{
return("");
}
UIFO uiFO = FormulaHelper.CreateFO <UIFO>();
DataSet ds = uiFO.GetWordDataSource(tmpCode, formID);
#region 获取word导出的版本
DataRow wordTmplRow = dtWordTmpl.Rows[0];
DateTime date;
if (ds.Tables[0].Columns.Contains("CreateTime"))
{
date = DateTime.Parse(ds.Tables[0].Rows[0]["CreateTime"].ToString());
}
else
{
date = DateTime.Parse(ds.Tables[0].Rows[0]["CreateDate"].ToString());
}
foreach (DataRow row in dtWordTmpl.Rows)
{
var _startDate = DateTime.Parse(row["VersionStartDate"].ToString());
var _endDate = DateTime.MaxValue;
if (row["VersionEndDate"].ToString() != "")
{
_endDate = DateTime.Parse(row["VersionEndDate"].ToString());
}
if (date > _startDate && date < _endDate)
{
wordTmplRow = row;
break;
}
}
int?versionNum = 1;
if (wordTmplRow["VersionNum"].ToString() != "")
{
versionNum = int.Parse(wordTmplRow["VersionNum"].ToString());
}
string tmplName = dtWordTmpl.Rows[0]["Code"].ToString() + "_" + versionNum + ".docx";
string tempPath = Server.MapPath("/") + "WordTemplate/" + tmplName;
if (System.IO.File.Exists(tempPath) == false)
{
tmplName = dtWordTmpl.Rows[0]["Code"].ToString() + ".docx";
tempPath = Server.MapPath("/") + "WordTemplate/" + tmplName;
}
#endregion
var export = new Formula.ImportExport.AsposeWordExporter();
byte[] result = export.ExportPDF(ds, tempPath);
var name = fileName.IndexOf(".pdf") >= 0 ? fileName : fileName + ".pdf";
return(FileStoreHelper.UploadFile(name, result));
}
public JsonResult DoUpdate(string listData)
{
UIFO uiFO = new UIFO();
var list = JsonHelper.ToList(listData);
foreach (var item in list)
{
switch (item["Type"].ToString())
{
case "Form":
DeleteWhereSaveCode("Base", "S_UI_Form", item["ID"].ToString(), item["Code"].ToString());
UpdateUI(item, "S_UI_Form");
//不生成数据库表
//uiFO.ReleaseForm(item["ID"].ToString());
RenameCode("Base", "S_UI_Form", item["ID"].ToString(), item["Code"].ToString());
break;
case "List":
DeleteWhereSaveCode("Base", "S_UI_List", item["ID"].ToString(), item["Code"].ToString());
UpdateUI(item, "S_UI_List");
RenameCode("Base", "S_UI_List", item["ID"].ToString(), item["Code"].ToString());
break;
case "Selector":
DeleteWhereSaveCode("Base", "S_UI_Selector", item["ID"].ToString(), item["Code"].ToString());
UpdateUI(item, "S_UI_Selector");
RenameCode("Base", "S_UI_Selector", item["ID"].ToString(), item["Code"].ToString());
break;
case "Word":
DeleteWhereSaveCode("Base", "S_UI_Word", item["ID"].ToString(), item["Code"].ToString());
UpdateUI(item, "S_UI_Word");
RenameCode("Base", "S_UI_Word", item["ID"].ToString(), item["Code"].ToString());
break;
case "Report":
DeleteWhereSaveCode("Base", "S_R_Define", item["ID"].ToString(), item["Code"].ToString());
UpdateReport(item);
RenameCode("Base", "S_R_Define", item["ID"].ToString(), item["Code"].ToString());
break;
case "Workflow":
DeleteWhereSaveCode("Workflow", "S_WF_DefFlow", item["ID"].ToString(), item["Code"].ToString());
UpdateWorkflow(item);
RenameCode("Workflow", "S_WF_DefFlow", item["ID"].ToString(), item["Code"].ToString());
break;
case "Enum":
DeleteWhereSaveCode("Base", "S_M_EnumDef", item["ID"].ToString(), item["Code"].ToString());
UpdateEnum(item);
RenameCode("Base", "S_M_EnumDef", item["ID"].ToString(), item["Code"].ToString());
break;
}
}
return(Json(""));
}
public FileResult Export(string tmplCode, string id)
{
if (string.IsNullOrEmpty(tmplCode))
{
throw new Exception("缺少参数TmplCode");
}
SQLHelper sqlHeper = SQLHelper.CreateSqlHelper(ConnEnum.Base);
var dtWordTmpl = sqlHeper.ExecuteDataTable(string.Format("select * from S_UI_Word where Code='{0}'", tmplCode));
if (dtWordTmpl.Rows.Count == 0)
{
throw new Exception("Word导出定义不存在");
}
#region 预览时的自动ID
if (Request["TopID"] == "true" && string.IsNullOrEmpty(id))
{
SQLHelper sqlHelperWord = SQLHelper.CreateSqlHelper(dtWordTmpl.Rows[0]["ConnName"].ToString());
string sql = string.Format("select top 1 ID from ({0}) a", dtWordTmpl.Rows[0]["SQL"]);
var obj = sqlHelperWord.ExecuteScalar(sql);
if (obj != null)
{
id = obj.ToString();
}
}
#endregion
if (string.IsNullOrEmpty(id))
{
throw new Exception("缺少参数ID");
}
string tmplName = dtWordTmpl.Rows[0]["Code"].ToString() + ".docx";
string tempPath = Server.MapPath("/") + "WordTemplate/" + tmplName;
UIFO uiFO = FormulaHelper.CreateFO <UIFO>();
DataSet ds = uiFO.GetWordDataSource(tmplCode, id);
AsposeWordExporter export = new AsposeWordExporter();
byte[] result = export.ExportWord(ds, tempPath);
MemoryStream docStream = new MemoryStream(result);
string realFileName = dtWordTmpl.Rows[0]["Name"].ToString();
var explorerName = HttpContext.Request.Browser.Browser.ToUpper();
if (explorerName == "IE" || explorerName == "INTERNETEXPLORER" || HttpContext.Request.UserAgent.ToString().IndexOf("rv:11") > 0)
{
realFileName = HttpUtility.UrlEncode(realFileName, System.Text.Encoding.UTF8);
realFileName = realFileName.Replace("+", "%20");
}
return(base.File(docStream.ToArray(), "application/msword", realFileName + ".doc"));
}
public JsonResult GetTree(string tmplCode, QueryBuilder qb)
{
var listDef = entities.Set <S_UI_Layout>().Where(c => c.Code == tmplCode).First();
SQLHelper sqlHeler = SQLHelper.CreateSqlHelper(listDef.ConnName);
UIFO uiFO = FormulaHelper.CreateFO <UIFO>();
string sql = uiFO.ReplaceString(listDef.SQL);
return(Json(sqlHeler.ExecuteDataTable(sql)));
}
private string GetSerialNumber(string formCode, string SerialNumberSettings, bool applySerialNumber, DataRow row = null, Dictionary <string, string> dic = null)
{
UIFO uiFO = FormulaHelper.CreateFO <UIFO>();
var serialNumberDic = JsonHelper.ToObject(SerialNumberSettings);
string tmpl = serialNumberDic["Tmpl"].ToString();
string resetRule = serialNumberDic["ResetRule"].ToString();
string CategoryCode = "";
string SubCategoryCode = "";
string OrderNumCode = "";
string PrjCode = "";
string OrgCode = "";
string UserCode = "";
if (serialNumberDic.ContainsKey("CategoryCode"))
{
CategoryCode = uiFO.ReplaceString(serialNumberDic["CategoryCode"].ToString(), row, dic);
}
if (serialNumberDic.ContainsKey("SubCategoryCode"))
{
SubCategoryCode = uiFO.ReplaceString(serialNumberDic["SubCategoryCode"].ToString(), row, dic);
}
if (serialNumberDic.ContainsKey("OrderNumCode"))
{
OrderNumCode = uiFO.ReplaceString(serialNumberDic["OrderNumCode"].ToString(), row, dic);
}
if (serialNumberDic.ContainsKey("PrjCode"))
{
PrjCode = uiFO.ReplaceString(serialNumberDic["PrjCode"].ToString(), row, dic);
}
if (serialNumberDic.ContainsKey("OrgCode"))
{
OrgCode = uiFO.ReplaceString(serialNumberDic["OrgCode"].ToString(), row, dic);
}
if (serialNumberDic.ContainsKey("UserCode"))
{
UserCode = uiFO.ReplaceString(serialNumberDic["UserCode"].ToString(), row, dic);
}
SerialNumberParam param = new SerialNumberParam()
{
Code = formCode,
PrjCode = PrjCode,
OrgCode = OrgCode,
UserCode = UserCode,
CategoryCode = CategoryCode,
SubCategoryCode = SubCategoryCode,
OrderNumCode = OrderNumCode
};
string SerialNumber = SerialNumberHelper.GetSerialNumberString(tmpl, param, resetRule, applySerialNumber);
return(SerialNumber);
}
public virtual JsonResult GetTotal(string tmplCode)
{
var listDef = entities.Set <S_UI_List>().SingleOrDefault(c => c.Code == tmplCode);
SQLHelper sqlHeler = SQLHelper.CreateSqlHelper(listDef.ConnName);
UIFO uiFO = FormulaHelper.CreateFO <UIFO>();
string sql = listDef.SQL;
sql = uiFO.ReplaceString(sql);
return(Json(sqlHeler.ExecuteDataTable(sql).Rows.Count));
}
public virtual JsonResult GetSelectedList(string tmplCode, string ids)
{
var listDef = entities.Set <S_UI_List>().SingleOrDefault(c => c.Code == tmplCode);
SQLHelper sqlHeler = SQLHelper.CreateSqlHelper(listDef.ConnName);
UIFO uiFO = FormulaHelper.CreateFO <UIFO>();
string sql = listDef.SQL;
sql = uiFO.ReplaceString(sql);
sql = string.Format("select * form ({0}) as dt0 where ID in ('{2}')", sql, ids.Replace(",", "','"));
var dt = sqlHeler.ExecuteDataTable(sql);
return(Json(dt));
}
//翻译DataTable中的枚举
private void TransferEnum(DataTable dt, DataTable dtFields, DataRow rowDataSetDefine)
{
var enumService = FormulaHelper.GetService <IEnumService>();
UIFO uiFO = new UIFO();
foreach (DataRow field in dtFields.Rows)
{
string code = field["Code"].ToString();
string enumKey = field["EnumKey"].ToString();
if (enumKey == "")
{
continue;
}
uiFO.TransferEnum(dt, rowDataSetDefine["ConnName"].ToString(), rowDataSetDefine["TableNames"].ToString().Split(',')[0], code, enumKey);
}
}
public HttpResponseMessage Export(string id)
{
string tmplCode = Request.RequestUri.ParseQueryString().Get("tmplCode");
if (string.IsNullOrEmpty(tmplCode))
{
throw new Exception("缺少参数TmplCode");
}
SQLHelper sqlHeper = SQLHelper.CreateSqlHelper(ConnEnum.Base);
var dtWordTmpl = sqlHeper.ExecuteDataTable(string.Format("select * from S_UI_Word where Code='{0}'", tmplCode));
if (dtWordTmpl.Rows.Count == 0)
{
throw new Exception("Word导出定义不存在");
}
if (string.IsNullOrEmpty(id))
{
throw new Exception("缺少参数ID");
}
string tmplName = dtWordTmpl.Rows[0]["Code"].ToString() + ".docx";
var path = System.AppDomain.CurrentDomain.BaseDirectory.TrimEnd('\\');
string tempPath = path.Substring(0, path.LastIndexOf('\\') + 1) + "WordTemplate/" + tmplName;// Server.MapPath("/") +
UIFO uiFO = FormulaHelper.CreateFO <UIFO>();
DataSet ds = uiFO.GetWordDataSource(tmplCode, id);
AsposeWordExporter export = new AsposeWordExporter();
byte[] bytesArray = export.ExportWord(ds, tempPath);
string fileName = dtWordTmpl.Rows[0]["Name"].ToString();
HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK);
result.Content = new ByteArrayContent(bytesArray);
result.Content.Headers.ContentLength = bytesArray.Length;
result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream");
result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment");
result.Content.Headers.ContentDisposition.FileName = fileName;
return(result);
}
//翻译DataTable中的枚举
private void TransferEnum(DataTable dt, S_R_DataSet dataSet)
{
//if (dtFields == null)
// return;
UIFO uiFO = new UIFO();
foreach (var field in dataSet.S_R_Field)
{
string code = field.Code;
string enumKey = field.EnumKey;
if (string.IsNullOrEmpty(enumKey))
{
continue;
}
//uiFO.TransferEnum(dt, code, dataSet.ConnName, dataSet.TableNames.Split(',')[0], enumKey);
uiFO.TransferEnum(dt, dataSet.ConnName, dataSet.TableNames.Split(',')[0], code, enumKey);
}
}
public JsonResult GetCustomField()
{
UIFO fo = new UIFO();
DataTable table = new DataTable();
table.Columns.Add("value");
string sql = fo.ReplaceString(Request["SQL"]);
string connName = Convert.ToString(Request["ConnName"]);
SQLHelper sqlHelper = SQLHelper.CreateSqlHelper(connName);
var tb = sqlHelper.ExecuteDataTable(sql);
if (tb.Columns.Contains("Column1"))
{
throw new Exception("请为查询语名的列命名!如:select '{CurrentUserID}' as CurrentUserID");
}
foreach (var col in tb.Columns)
{
table.Rows.Add(col.ToString());
}
return(Json(table));
}
public JsonResult ImportItemFromLayout(string formID)
{
var form = entities.Set <S_UI_Form>().SingleOrDefault(c => c.ID == formID);
if (string.IsNullOrEmpty(form.Layout))
{
throw new Exception("布局还没有建立");
}
UIFO uiFO = FormulaHelper.CreateFO <UIFO>();
Regex reg = new Regex(UIFO.uiRegStr);
var matchs = reg.Matches(form.Layout, 0);
var list = JsonHelper.ToObject <List <FormItem> >(form.Items ?? "[]");
int index = list.Count;
foreach (Match match in matchs)
{
string name = match.Value.Trim('{', '}');
if (list.SingleOrDefault(c => c.Name == name) == null)
{
list.Add(new FormItem
{
ID = FormulaHelper.CreateGuid(),
Code = uiFO.GetHanZiPinYinString(name),
Name = name,
Enabled = "true",
Visible = "true",
DefaultValue = "",
ItemType = "TextBox",
FieldType = "nvarchar(200)"
});
}
}
return(Json(list));
}
public FileResult Export(string tmplCode, string id)
{
if (string.IsNullOrEmpty(tmplCode))
{
throw new Exception("缺少参数TmplCode");
}
SQLHelper sqlHeper = SQLHelper.CreateSqlHelper(ConnEnum.Base);
var dtWordTmpl = sqlHeper.ExecuteDataTable(string.Format("select TOP 1 * from S_UI_Word where Code='{0}' ORDER BY VersionNum DESC", tmplCode));
if (dtWordTmpl.Rows.Count == 0)
{
throw new Exception("Word导出定义不存在");
}
#region 预览时的自动ID
if (Request["TopID"] == "true" && string.IsNullOrEmpty(id))
{
SQLHelper sqlHelperWord = SQLHelper.CreateSqlHelper(dtWordTmpl.Rows[0]["ConnName"].ToString());
string sql = string.Format("select top 1 ID from ({0}) a", dtWordTmpl.Rows[0]["SQL"]);
if (Config.Constant.IsOracleDb)
{
sql = string.Format("select ID from({0}) a where rownum=1", dtWordTmpl.Rows[0]["SQL"]);
}
try
{
var obj = sqlHelperWord.ExecuteScalar(sql);
if (obj != null)
{
id = obj.ToString();
}
}
catch
{
// Response.Write("SQL错误，或者表不存在！");
Response.Redirect("/MvcConfig/Error.html?errMsg=SQL错误，或者表不存在！");
Response.End();
return(null);
}
if (string.IsNullOrEmpty(id))
{
Response.Write("表中没有数据，无法导出Word。");
Response.End();
return(null);
}
}
#endregion
if (string.IsNullOrEmpty(id))
{
throw new Exception("缺少参数ID");
}
//string tmplName = dtWordTmpl.Rows[0]["Code"].ToString() + ".docx";
//string tempPath = Server.MapPath("/") + "WordTemplate/" + tmplName;
UIFO uiFO = FormulaHelper.CreateFO <UIFO>();
DataSet ds = uiFO.GetWordDataSource(tmplCode, id);
DataRow wordTmplRow = dtWordTmpl.Rows[0];
DateTime date;
if (ds.Tables[0].Columns.Contains("CreateTime"))
{
date = DateTime.Parse(ds.Tables[0].Rows[0]["CreateTime"].ToString());
}
else
{
date = DateTime.Parse(ds.Tables[0].Rows[0]["CreateDate"].ToString());
}
foreach (DataRow row in dtWordTmpl.Rows)
{
var _startDate = DateTime.Parse(row["VersionStartDate"].ToString());
var _endDate = DateTime.MaxValue;
if (row["VersionEndDate"].ToString() != "")
{
_endDate = DateTime.Parse(row["VersionEndDate"].ToString());
}
if (date > _startDate && date < _endDate)
{
wordTmplRow = row;
break;
}
}
int?versionNum = 1;
if (wordTmplRow["VersionNum"].ToString() != "")
{
versionNum = int.Parse(wordTmplRow["VersionNum"].ToString());
}
string tempPath = HttpContext.Server.MapPath("/") + "WordTemplate/" + wordTmplRow["Code"].ToString() + "_" + versionNum + ".docx";
//兼容以前没有版本时
if (System.IO.File.Exists(tempPath) == false)
{
tempPath = HttpContext.Server.MapPath("/") + "WordTemplate/" + wordTmplRow["Code"].ToString() + ".docx";
}
AsposeWordExporter export = new AsposeWordExporter();
byte[] result = null;
if (Request["pdf"] == "true")
{
result = export.ExportPDF(ds, tempPath, delegate(FieldMergingArgs e)
{
if (e.FieldValue != null)
{
string strValues = e.FieldValue.ToString();
if (HtmlStringHelper.CheckHtml(strValues))
{
DocumentBuilder builder = new DocumentBuilder(e.Document);
builder.MoveToMergeField(e.FieldName);
builder.InsertHtml(strValues);
}
}
});
}
else
{
result = export.ExportWord(ds, tempPath, delegate(FieldMergingArgs e)
{
if (e.FieldValue != null)
{
string strValues = e.FieldValue.ToString();
if (HtmlStringHelper.CheckHtml(strValues))
{
DocumentBuilder builder = new DocumentBuilder(e.Document);
builder.MoveToMergeField(e.FieldName);
builder.InsertHtml(strValues);
}
}
});
}
MemoryStream docStream = new MemoryStream(result);
string realFileName = dtWordTmpl.Rows[0]["Name"].ToString();
if (wordTmplRow["WordNameTmpl"].ToString() != "")
{
realFileName = uiFO.ReplaceString(wordTmplRow["WordNameTmpl"].ToString(), ds.Tables[0].Rows[0]);
}
if (!String.IsNullOrEmpty(Request["filename"]))
{
realFileName = Request["filename"];
}
var explorerName = HttpContext.Request.Browser.Browser.ToUpper();
if (explorerName == "IE" || explorerName == "INTERNETEXPLORER" || HttpContext.Request.UserAgent.ToString().IndexOf("rv:11") > 0)
{
realFileName = HttpUtility.UrlEncode(realFileName, System.Text.Encoding.UTF8);
realFileName = realFileName.Replace("+", "%20");
}
if (Request["pdf"] == "true")
{
return(base.File(docStream.ToArray(), "application/pdf", realFileName + ".pdf"));
}
else
{
return(base.File(docStream.ToArray(), "application/msword", realFileName + ".doc"));
}
}
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));
}
}
public ActionResult Reorganize()
{
var SpaceID = GetQueryString("SpaceID");
//baseEntities.Set<S_UI_Form>()
var space = DocConfigHelper.CreateConfigSpaceByID(SpaceID);
if (space == null)
{
throw new BusinessException("未指定档案实体空间，无法获取数据访问对象");
}
//var config = space.S_DOC_ReorganizeConfig.FirstOrDefault();
//if (config == null || string.IsNullOrEmpty(config.Items))
// throw new BusinessException("请先配置档案整编区定义");
var sbField = new System.Text.StringBuilder();
var sbScript = new System.Text.StringBuilder();
var sbSelector = new System.Text.StringBuilder();
//var fields = JsonHelper.ToList(config.Items);
var baseEntities = FormulaHelper.GetEntities <BaseEntities>();
var formDef = baseEntities.Set <S_UI_Form>().FirstOrDefault(a => a.Code == "Reorganize");
if (formDef == null || string.IsNullOrEmpty(formDef.Items))
{
throw new BusinessException("请先配置表单");
}
var field = JsonHelper.ToList(formDef.Items).FirstOrDefault(a => a.GetValue("Code").ToString() == "DocumentList");
var fo = new UIFO();
//foreach (var field in fields)
//{
//var settingStr = field.GetValue("Settings");
if (field == null || string.IsNullOrEmpty(field.GetValue("Code").ToString()))
{
throw new BusinessException("请先配置表单子表");
}
var settings = JsonHelper.ToObject(field.GetValue("Settings"));
var documentLists = JsonHelper.ToList(settings.GetValue("listData"));//拿到子表字段
foreach (var docList in documentLists)
{
string header = docList.GetValue("Name");
var settingStrDocList = docList.GetValue("Settings");
var settingsDocList = JsonHelper.ToObject(settingStrDocList);
if (docList.GetValue("Code").ToString().Equals("ReorganizePath") && !Convert.ToBoolean(docList.GetValue("Visible")))//当整编目录在子表中时隐藏的,改为显示,
{
docList.SetValue("Visible", "true");
}
if (Convert.ToBoolean(docList.GetValue("Visible"))) //该字段是否可见
{
string selector = settingsDocList.GetValue("SelectorKey");
if (!string.IsNullOrEmpty(selector))//弹出选择
{
sbField.AppendFormat("<div name='{3}' field='{3}' {0} {1} header='{2}' allowdrag='true' headeralign='center'></div>", fo.GetMiniuiSettings(docList)
, fo.GetMiniuiSettings(docList.GetValue("Settings"))
, header, docList.GetValue("Code") + "Name");
}
else
{
sbField.AppendFormat("<div name='{3}' field='{3}' {0} {1} header='{2}' allowdrag='true' headeralign='center'></div>", fo.GetMiniuiSettings(docList)
, fo.GetMiniuiSettings(docList.GetValue("Settings"))
, header, docList.GetValue("Code"));
}
}
if (string.IsNullOrEmpty(settingStrDocList))
{
continue;
}
//子表枚举
string enumKey = settingsDocList.GetValue("data");
if (!string.IsNullOrEmpty(enumKey))
{
string tableName = formDef.TableName.Split(',')[0];
var key = GetEnumKey(formDef.ConnName, tableName, docList.GetValue("Code"), enumKey);
string enumStr = GetEnumString(formDef.ConnName, tableName, docList.GetValue("Code"), enumKey);
if (string.IsNullOrEmpty(enumStr))
{
enumStr = "[]";
}
sbScript.AppendFormat("\n var {0} = {1}", key, enumStr);
sbScript.AppendFormat("\n addGridEnum('fileTreeGrid', '{0}', '{1}');", field.GetValue("Code"), key);
}
//}
}
ViewBag.FileTreeFieldsHtml = sbField.ToString();
ViewBag.FileTreeFieldsScript = sbScript.ToString();
return(View());
}
public HttpResponseMessage Export(string id)
{
string tmplCode = Request.RequestUri.ParseQueryString().Get("tmplCode");
if (string.IsNullOrEmpty(tmplCode))
{
throw new Exception("缺少参数TmplCode");
}
SQLHelper sqlHeper = SQLHelper.CreateSqlHelper(ConnEnum.Base);
var dtWordTmpl = sqlHeper.ExecuteDataTable(string.Format("select * from S_UI_Word where Code='{0}'", tmplCode));
if (dtWordTmpl.Rows.Count == 0)
{
throw new Exception("Word导出定义不存在");
}
if (string.IsNullOrEmpty(id))
{
throw new Exception("缺少参数ID");
}
//string tmplName = dtWordTmpl.Rows[0]["Code"].ToString() + ".docx";
////var path = System.AppDomain.CurrentDomain.BaseDirectory.TrimEnd('\\');
////string tempPath = path.Substring(0, path.LastIndexOf('\\') + 1) + "WordTemplate/" + tmplName;// Server.MapPath("/") +
//string tempPath = System.Web.HttpContext.Current.Server.MapPath("/") + "WordTemplate/" + tmplName;
//word历史版本功能
UIFO uiFO = FormulaHelper.CreateFO <UIFO>();
DataSet ds = uiFO.GetWordDataSource(tmplCode, id, false);//客户端转图访问时，没有当前用户，防止报错
DataRow wordTmplRow = dtWordTmpl.Rows[0];
DateTime date;
if (ds.Tables[0].Columns.Contains("CreateTime"))
{
date = DateTime.Parse(ds.Tables[0].Rows[0]["CreateTime"].ToString());
}
else
{
date = DateTime.Parse(ds.Tables[0].Rows[0]["CreateDate"].ToString());
}
foreach (DataRow row in dtWordTmpl.Rows)
{
var _startDate = DateTime.Parse(row["VersionStartDate"].ToString());
var _endDate = DateTime.MaxValue;
if (row["VersionEndDate"].ToString() != "")
{
_endDate = DateTime.Parse(row["VersionEndDate"].ToString());
}
if (date > _startDate && date < _endDate)
{
wordTmplRow = row;
break;
}
}
int?versionNum = 1;
if (wordTmplRow["VersionNum"].ToString() != "")
{
versionNum = int.Parse(wordTmplRow["VersionNum"].ToString());
}
string tempPath = HttpContext.Current.Server.MapPath("/") + "WordTemplate/" + wordTmplRow["Code"].ToString() + "_" + versionNum + ".docx";
//兼容以前没有版本时
if (System.IO.File.Exists(tempPath) == false)
{
tempPath = HttpContext.Current.Server.MapPath("/") + "WordTemplate/" + wordTmplRow["Code"].ToString() + ".docx";
}
AsposeWordExporter export = new AsposeWordExporter();
byte[] bytesArray = export.ExportWord(ds, tempPath, delegate(FieldMergingArgs e) {
string strValues = e.FieldValue == null?"":e.FieldValue.ToString();
if (HtmlStringHelper.CheckHtml(strValues))
{
DocumentBuilder builder = new DocumentBuilder(e.Document);
builder.MoveToMergeField(e.FieldName);
builder.InsertHtml(strValues);
}
});
string fileName = dtWordTmpl.Rows[0]["Name"].ToString();
HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK);
result.Content = new ByteArrayContent(bytesArray);
result.Content.Headers.ContentLength = bytesArray.Length;
result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream");
result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment");
result.Content.Headers.ContentDisposition.FileName = fileName;
return(result);
}
public ActionResult DocumentManager()
{
var baseDbContext = FormulaHelper.GetEntities <BaseEntities>();
var level = String.IsNullOrEmpty(this.GetQueryString("Level").Trim()) ? "true" : this.GetQueryString("Level");
ViewBag.DisplayLevel = level;
string tmplCode = "DocumentBaseInfo";
var formInfo = baseDbContext.S_UI_Form.FirstOrDefault(c => c.Code == tmplCode);
if (formInfo == null)
{
ViewBag.BaseHtml = "";
ViewBag.BaseScrpitHtml = "";
}
else
{
//var uiFo = FormulaHelper.CreateFO<Base.Logic.BusinessFacade.UIFO>();
//ViewBag.BaseHtml = uiFo.CreateFormHtml(tmplCode, null);
//ViewBag.BaseScrpitHtml = uiFo.CreateFormScript(tmplCode);
var uiFO = FormulaHelper.CreateFO <UIFO>();
var formDef = UIFO.GetFormDef(tmplCode, Request["ID"]);
ViewBag.BaseHtml = uiFO.CreateFormHtml(formDef);
if (ViewBag.BaseScrpitHtml != null && !String.IsNullOrEmpty(ViewBag.BaseScrpitHtml))
{
ViewBag.BaseScrpitHtml += "\n " + uiFO.CreateFormScript(formDef);
}
else
{
ViewBag.BaseScrpitHtml = uiFO.CreateFormScript(formDef);
}
}
var engineeringInfoID = this.GetQueryString("EngineeringInfoID");
var engineeringInfo = this.GetEntityByID <S_I_Engineering>(engineeringInfoID);
if (engineeringInfo != null)
{
if (engineeringInfo.Mode == null)
{
throw new Formula.Exceptions.BusinessValidationException("未找到该项目的项目模式");
}
var infrastructureDbContext = FormulaHelper.GetEntities <InfrastructureEntities>();
var folderTemplate = infrastructureDbContext.Set <S_T_FolderTemplate>().FirstOrDefault(a => a.ModeKey.Contains(engineeringInfo.Mode.ID));
if (folderTemplate == null)
{
throw new Formula.Exceptions.BusinessValidationException("没有找到指定的文档目录结构模板");
}
ViewBag.QuickSearchName = "";
ViewBag.QuickSearchCode = "";
if (!string.IsNullOrEmpty(folderTemplate.DisplayColJson))
{
var dicList = JsonHelper.ToList(folderTemplate.DisplayColJson);
ViewBag.ColDefine = dicList;
ViewBag.QuickSearchName = string.Join("或", dicList.Where(a => a.GetValue("IsQuickSearch") == "true")
.Select(a => a.GetValue("Name")));
ViewBag.QuickSearchCode = string.Join(",", dicList.Where(a => a.GetValue("IsQuickSearch") == "true")
.Select(a => a.GetValue("Code")));
}
}
return(View());
}
public ActionResult List()
{
var engineeringInfoID = this.GetQueryString("EngineeringInfoID");
var folderKey = this.GetQueryString("FolderKey");
var engineeringInfo = this.GetEntityByID <S_I_Engineering>(engineeringInfoID);
if (engineeringInfo == null)
{
throw new Formula.Exceptions.BusinessValidationException("没有找到指定的工程信息");
}
ViewBag.EngineeringInfoID = engineeringInfo.ID;
var folder = engineeringInfo.S_D_Folder.FirstOrDefault(c => c.FolderKey == folderKey);
ViewBag.HasAttr = false;
if (folder == null)
{
folder = engineeringInfo.S_D_Folder.FirstOrDefault(c => c.Code == folderKey);
if (folder == null)
{
return(View());
}
}
ViewBag.FolderID = folder.ID;
string tmplCode = "DocumentBaseInfo";
var baseDbContext = FormulaHelper.GetEntities <BaseEntities>();
var formInfo = baseDbContext.S_UI_Form.FirstOrDefault(c => c.Code == tmplCode);
if (formInfo == null)
{
ViewBag.BaseHtml = "";
ViewBag.BaseScrpitHtml = "";
}
else
{
//var uiFo = FormulaHelper.CreateFO<Base.Logic.BusinessFacade.UIFO>();
//ViewBag.BaseHtml = uiFo.CreateFormHtml(tmplCode, null);
//ViewBag.BaseScrpitHtml = uiFo.CreateFormScript(tmplCode);
var uiFO = FormulaHelper.CreateFO <UIFO>();
var formDef = UIFO.GetFormDef(tmplCode, Request["ID"]);
ViewBag.BaseHtml = uiFO.CreateFormHtml(formDef);
if (ViewBag.BaseScrpitHtml != null && !String.IsNullOrEmpty(ViewBag.BaseScrpitHtml))
{
ViewBag.BaseScrpitHtml += "\n " + uiFO.CreateFormScript(formDef);
}
else
{
ViewBag.BaseScrpitHtml = uiFO.CreateFormScript(formDef);
}
}
if (!String.IsNullOrEmpty(folder.AttrDefine))
{
var attrList = JsonHelper.ToList(folder.AttrDefine);
if (attrList.Count > 0)
{
ViewBag.HasAttr = true;
}
}
return(View());
}
public ActionResult PageView(string tmplCode)
{
var listDef = entities.Set <S_UI_List>().SingleOrDefault(c => c.Code == tmplCode);
UIFO uiFO = FormulaHelper.CreateFO <UIFO>();
ViewBag.ListHtml = uiFO.CreateListHtml(tmplCode);
ViewBag.Script = uiFO.CreateListScript(tmplCode);
ViewBag.FixedFields = string.Format("var FixedFields={0};", Newtonsoft.Json.JsonConvert.SerializeObject(uiFO.GetFixedWidthFields(tmplCode)));
ViewBag.Title = listDef.Name;
#region TAB查询
var fields = JsonHelper.ToList(listDef.LayoutField);
var tab = new Tab();
foreach (var field in fields)
{
if (!field.ContainsKey("Settings"))
{
continue;
}
var settings = JsonHelper.ToObject(field["Settings"].ToString());
if (!settings.ContainsKey("EnumKey") || settings["EnumKey"].ToString() == "")
{
continue;
}
if (!settings.ContainsKey("TabSearchName") || settings["TabSearchName"].ToString() == "")
{
continue;
}
Category category = null;
var hasAllAttr = true;
string enumKey = settings["EnumKey"].ToString();
if (settings.ContainsKey("ShowAll") && settings["ShowAll"].ToString() == "false" &&
settings.ContainsKey("TabSearchDefault") && !String.IsNullOrEmpty(settings["TabSearchDefault"].ToString()))
{
hasAllAttr = false;
}
if (enumKey.StartsWith("["))
{
category = CategoryFactory.GetCategoryByString(enumKey, settings["TabSearchName"].ToString(), field["field"].ToString(), hasAllAttr);
}
else
{
category = CategoryFactory.GetCategory(enumKey, settings["TabSearchName"].ToString(), field["field"].ToString(), hasAllAttr);
}
//默认值
if (settings.ContainsKey("TabSearchDefault") && settings["TabSearchDefault"].ToString() != "")
{
category.SetDefaultItem(settings["TabSearchDefault"].ToString());
}
else
{
category.SetDefaultItem();
}
//多选或单选
if (settings.ContainsKey("TabSearchMulti") && settings["TabSearchMulti"].ToString() == "false")
{
category.Multi = false;
}
else
{
category.Multi = true;
}
tab.Categories.Add(category);
}
if (tab.Categories.Count > 0)
{
tab.IsDisplay = true;
ViewBag.Tab = tab;
ViewBag.Layout = "~/Views/Shared/_AutoListLayoutTab.cshtml";
}
else
{
ViewBag.Layout = "~/Views/Shared/_Layout.cshtml";
}
#endregion
return(View());
}
public 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>();
string sql = uiFO.ReplaceString(listDef.SQL);
DataTable dtTmpl = sqlHeler.ExecuteDataTable(string.Format("select * from ({0}) as 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.Equal, Request[key], "Group1", null);
}
}
var data = sqlHeler.ExecuteGridData(sql, qb);
#region 计算汇总
var fields = JsonHelper.ToList(listDef.LayoutField);
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;
}
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 collectSql = string.Format("select {0} from (select * from ({1}) as tb where 1=1 {2}) as T"
, sb.ToString().Trim(',')
, sql
, qb.GetWhereString(false) + FormulaHelper.CreateAuthDataFilter().GetWhereString(false)
);
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];
}
else
{
data.avgData[col.ColumnName] = dtCollect.Rows[0][col];
}
}
}
#endregion
return(Json(data));
}
public FileResult SqlFile(string defID, string fileCode, string tableName)
{
string sql = string.Format("select * from {1} where ID in('{0}')", defID.Replace(",", "','"), tableName);
SQLHelper sqlHelper = SQLHelper.CreateSqlHelper(ConnEnum.Base);
DataTable dt = sqlHelper.ExecuteDataTable(sql);
//表单定义和word模板同时导出历史版本
if (tableName.ToUpper() == "S_UI_FORM" || tableName.ToUpper() == "S_UI_WORD")
{
var codes = string.Join("','", dt.AsEnumerable().Select(c => c["Code"].ToString()).ToArray());
sql = string.Format("select * from {1} where Code in('{0}')", codes, tableName);
dt = sqlHelper.ExecuteDataTable(sql);
}
StringBuilder result = new StringBuilder();;
if (Config.Constant.IsOracleDb)
{
//声明变量
result.Append("DECLARE");
result.AppendLine();
foreach (DataColumn col in dt.Columns)
{
if (col.DataType == typeof(string))
{
result.AppendFormat("{0} nclob;", col.ColumnName);
result.AppendLine();
}
}
result.AppendFormat("begin"); //begin
result.AppendFormat(SQLHelper.CreateUpdateSql(tableName, dt));
result.AppendFormat("end");
}
else
{
#region SqlServer版
result.AppendLine(String.Format("USE [{0}] \n", sqlHelper.DbName));
result.AppendLine(SQLHelper.CreateUpdateSql(tableName, dt));
//如果导出的是表单，则增加生成表的Sql
if (tableName == "S_UI_Form")
{
UIFO uiFO = new UIFO();
StringBuilder sb = new StringBuilder("\n\n");
foreach (DataRow row in dt.Rows)
{
sb.AppendLine(String.Format("USE [{0}_{1}]", sqlHelper.DbName.Split('_').First(), row["ConnName"]));
sb.AppendLine();
sb.AppendLine(uiFO.CreateReleaseFormSql(row["ID"].ToString()));
}
result.AppendLine(sb.ToString());
}
#endregion
}
MemoryStream ms = new MemoryStream(System.Text.Encoding.Default.GetBytes(result.ToString()));
ms.Position = 0;
return(File(ms, "application/octet-stream ; Charset=UTF8", fileCode + ".sql"));
}
public void Validate()
{
var rangeSQL = this.ModelDic.GetValue("ApportionSQL");
Regex reg = new Regex("\\{[0-9a-zA-Z_\\.]*\\}");
var formDef = UIFO.GetFormDef("CostApportion", string.Empty);
var formItems = JsonHelper.ToObject <List <FormItem> >(formDef.Items);
#region 校验计算范围的SQL语句定义是否正确
var variateList = reg.Matches(rangeSQL);
foreach (Match variate in variateList)
{
string value = variate.Value.Trim('{', '}');
var matchDatas = value.Split('.');
if (matchDatas.Length == 1)
{
throw new Formula.Exceptions.BusinessValidationException("计算数据源中定义变量必须指定变量的来源，以固定格式{XXX.XXX}来定义");
}
if (matchDatas[0] == InputPre)
{
if (this.InputDefineList.Count == 0 || this.InputDefineList.Count(c => c.GetValue("Code") == matchDatas[1]) == 0)
{
throw new Formula.Exceptions.BusinessValidationException("计算数据源定义中，未能找到编号为【" + matchDatas[1] + "】的输入控件定义");
}
}
else if (matchDatas[0] == ParamPre)
{
if (this.ParamDefineList.Count == 0 || this.ParamDefineList.Count(c => c.GetValue("Code") == matchDatas[1]) == 0)
{
throw new Formula.Exceptions.BusinessValidationException("计算数据源定义中，未能找到编号为【" + matchDatas[1] + "】的参数数据源定义");
}
}
else if (matchDatas[0] == FormPre)
{
if (formItems.Count == 0 || formItems.Count(c => c.Code == matchDatas[1]) == 0)
{
throw new Formula.Exceptions.BusinessValidationException("计算数据源定义中，未能在分摊页面找到编号为【" + matchDatas[1] + "】的控件定义");
}
}
else
{
throw new Formula.Exceptions.BusinessValidationException("计算数据源定义中，无法识别数据获取对象，获取对象前缀必须是 Input 或 Param");
}
}
#endregion
#region 校验计算公式定义
foreach (var calDefine in this.CaculateDefineList)
{
var content = calDefine.GetValue("Content");
if (String.IsNullOrEmpty(content))
{
continue;
}
if (reg.IsMatch(content))
{
Match m = reg.Match(content);
string value = m.Value.Trim('{', '}');
var matchDatas = value.Split('.');
if (matchDatas.Length == 1)
{
throw new Formula.Exceptions.BusinessValidationException("计算配置定义中，字段【" + calDefine.GetValue("Code") + "】的定义不正确，以固定格式{XXX.XXX}来定义");
}
if (matchDatas[0] == InputPre)
{
if (this.InputDefineList.Count == 0 || this.InputDefineList.Count(c => c.GetValue("Code") == matchDatas[1]) == 0)
{
throw new Formula.Exceptions.BusinessValidationException("计算数据源定义中，未能找到编号为【" + matchDatas[1] + "】的输入控件定义");
}
}
else if (matchDatas[0] == ParamPre)
{
if (this.ParamDefineList.Count == 0 || this.ParamDefineList.Count(c => c.GetValue("Code") == matchDatas[1]) == 0)
{
throw new Formula.Exceptions.BusinessValidationException("计算数据源定义中，未能找到编号为【" + matchDatas[1] + "】的参数数据源定义");
}
}
else if (matchDatas[0] == FormPre)
{
if (formItems.Count == 0 || formItems.Count(c => c.Code == matchDatas[1]) == 0)
{
throw new Formula.Exceptions.BusinessValidationException("计算数据源定义中，未能在分摊页面找到编号为【" + matchDatas[1] + "】的控件定义");
}
}
else if (matchDatas[0] == RangePre)
{
if (this.RangeStructDt == null)
{
throw new Formula.Exceptions.BusinessValidationException("计算数据源定义错误，请检查SQL语句");
}
if (!this.RangeStructDt.Columns.Contains(matchDatas[1]))
{
throw new Formula.Exceptions.BusinessValidationException("计算配置定义中，字段【" + calDefine.GetValue("Code") + "】的定义不正确，计算数据源不包含【" + matchDatas[1] + "】的字段");
}
}
else
{
throw new Formula.Exceptions.BusinessValidationException("计算配置定义中，字段【" + calDefine.GetValue("Code") + "】的定义不正确，前缀只能是 【" + InputPre + "】或【" + ParamPre + "】或【" + RangePre + "】");
}
}
}
#endregion
#region 校验参数数据源的SQL定义是否正确
foreach (var item in this.ParamDefineList)
{
if (String.IsNullOrEmpty(item.GetValue("SQL")))
{
throw new Formula.Exceptions.BusinessValidationException("编号为【" + item.GetValue("Code") + "】的参数数据源SQL语句未定义");
}
var matchs = reg.Matches(item.GetValue("SQL"));
foreach (Match m in matchs)
{
var value = m.Value.Trim('{', '}');
var matchDatas = value.Split('.');
if (matchDatas.Length == 1)
{
throw new Formula.Exceptions.BusinessValidationException("参数数据源定义中，字段【" + item.GetValue("Code") + "】的定义不正确，以固定格式{XXX.XXX}来定义");
}
else if (matchDatas[0] == InputPre)
{
if (this.InputDefineList.Count == 0 || this.InputDefineList.Count(c => c.GetValue("Code") == matchDatas[1]) == 0)
{
throw new Formula.Exceptions.BusinessValidationException("参数数据源定义中，未能找到编号为【" + matchDatas[1] + "】的输入控件定义");
}
}
else if (matchDatas[0] == FormPre)
{
if (formItems.Count == 0 || formItems.Count(c => c.Code == matchDatas[1]) == 0)
{
throw new Formula.Exceptions.BusinessValidationException("参数数据源定义中，未能在分摊页面找到编号为【" + matchDatas[1] + "】的控件定义");
}
}
else
{
throw new Formula.Exceptions.BusinessValidationException("参数数据源定义中，字段【" + item.GetValue("Code") + "】的定义不正确，前缀只能是 【" + InputPre + "】");
}
}
}
#endregion
}
public FileResult ExportSql(string categoryFullID, string startDate)
{
var key = FormulaHelper.CreateGuid();
StringBuilder sb = new StringBuilder();
sb.AppendFormat(@"
--本SQL使用方法：
--1、冲突确认，即执行后返回的列表信息即冲突信息；
--2、在系统中处理冲突；
--3、再次执行本sql；
");
SQLHelper sqlHelper = SQLHelper.CreateSqlHelper(ConnEnum.Base);
SQLHelper flowSqlHelper = SQLHelper.CreateSqlHelper(ConnEnum.WorkFlow);
string preDBName = sqlHelper.DbName.Split('_').First();
sb.AppendFormat("USE [{0}]", sqlHelper.DbName);
sb.AppendLine();
sb.AppendFormat(@"
--SQL只能执行一次
if exists (select ID from S_UI_ModifyLogLog where ID='{0}' and IOType='导入')
begin
select '本SQL已经执行过，不能再次执行！'
return
end
", key);
sb.AppendLine();
string sql = "select * from S_M_Category where FullID like '" + categoryFullID + "%'";
var dtCategory = sqlHelper.ExecuteDataTable(sql);
//模块名称
string categoryName = dtCategory.AsEnumerable().SingleOrDefault(c => c["FullID"].ToString() == categoryFullID)["Name"].ToString();
if (categoryFullID != "0")
{
var parentCategory = sqlHelper.ExecuteDataTable("select * from S_M_Category where ID='" + dtCategory.AsEnumerable().SingleOrDefault(c => c["FullID"].ToString() == categoryFullID)["ParentID"].ToString() + "'");
sb.AppendFormat(@"
--模块不存在，不能导入
if not exists (select ID from S_M_Category where ID='{0}')
begin
select '模块“{1}”不存在，不能导入！'
return
end
", parentCategory.Rows[0]["ID"], parentCategory.Rows[0]["Name"]);
}
sb.AppendLine();
sb.Append("--更新模块开始");
sb.AppendFormat(SQLHelper.CreateUpdateSql("S_M_Category", dtCategory));
sb.Append("--更新模块结束");
sb.AppendLine();
sb.AppendLine();
sql = string.Format("select * from {2} where CategoryID in('{0}') and ModifyTime>'{1}'", string.Join("','", dtCategory.AsEnumerable().Select(c => c["ID"].ToString()).ToArray()), startDate, "{0}");
var dtForm = sqlHelper.ExecuteDataTable(string.Format(sql, "S_UI_Form"));
var dtList = sqlHelper.ExecuteDataTable(string.Format(sql, "S_UI_List"));
var dtPivot = sqlHelper.ExecuteDataTable(string.Format(sql, "S_UI_Pivot"));
var dtSelector = sqlHelper.ExecuteDataTable(string.Format(sql, "S_UI_Selector"));
var dtWord = sqlHelper.ExecuteDataTable(string.Format(sql, "S_UI_Word"));
var dtExcelPrint = sqlHelper.ExecuteDataTable(string.Format(sql, "S_UI_ExcelPrint"));
var dtReport = sqlHelper.ExecuteDataTable(string.Format(sql, "S_R_Define"));
var dtEnum = sqlHelper.ExecuteDataTable(string.Format(sql, "S_M_EnumDef"));
var dtWorkflow = sqlHelper.ExecuteDataTable(string.Format(sql, flowSqlHelper.DbName + ".dbo.S_WF_DefFlow"));
sb.AppendLine();
sb.AppendFormat("update S_UI_Form set Collision='Collision' where (Collision is null or Collision not in('{2}','keep','cover')) and ID in('{0}') and ModifyTime > '{1}'", string.Join("','", dtForm.AsEnumerable().Select(c => c["ID"]).ToArray()), startDate, key);
sb.AppendLine();
sb.AppendFormat("update S_UI_List set Collision='Collision' where (Collision is null or Collision not in('{2}','keep','cover')) and ID in('{0}') and ModifyTime > '{1}'", string.Join("','", dtList.AsEnumerable().Select(c => c["ID"]).ToArray()), startDate, key);
sb.AppendLine();
sb.AppendFormat("update S_UI_Pivot set Collision='Collision' where (Collision is null or Collision not in('{2}','keep','cover')) and ID in('{0}') and ModifyTime > '{1}'", string.Join("','", dtPivot.AsEnumerable().Select(c => c["ID"]).ToArray()), startDate, key);
sb.AppendLine();
sb.AppendFormat("update S_UI_Selector set Collision='Collision' where (Collision is null or Collision not in('{2}','keep','cover')) and ID in('{0}') and ModifyTime > '{1}'", string.Join("','", dtSelector.AsEnumerable().Select(c => c["ID"]).ToArray()), startDate, key);
sb.AppendLine();
sb.AppendFormat("update S_UI_Word set Collision='Collision' where (Collision is null or Collision not in('{2}','keep','cover')) and ID in('{0}') and ModifyTime > '{1}'", string.Join("','", dtWord.AsEnumerable().Select(c => c["ID"]).ToArray()), startDate, key);
sb.AppendLine();
sb.AppendFormat("update S_UI_ExcelPrint set Collision='Collision' where (Collision is null or Collision not in('{2}','keep','cover')) and ID in('{0}') and ModifyTime > '{1}'", string.Join("','", dtExcelPrint.AsEnumerable().Select(c => c["ID"]).ToArray()), startDate, key);
sb.AppendLine();
sb.AppendFormat("update S_R_Define set Collision='Collision' where (Collision is null or Collision not in('{2}','keep','cover')) and ID in('{0}') and ModifyTime > '{1}'", string.Join("','", dtReport.AsEnumerable().Select(c => c["ID"]).ToArray()), startDate, key);
sb.AppendLine();
sb.AppendFormat("update S_M_EnumDef set Collision='Collision' where (Collision is null or Collision not in('{2}','keep','cover')) and ID in('{0}') and ModifyTime > '{1}'", string.Join("','", dtEnum.AsEnumerable().Select(c => c["ID"]).ToArray()), startDate, key);
sb.AppendLine();
sb.AppendFormat("update {3}.dbo.S_WF_DefFlow set Collision='Collision' where (Collision is null or Collision not in('{2}','keep','cover')) and ID in('{0}') and ModifyTime > '{1}'", string.Join("','", dtWorkflow.AsEnumerable().Select(c => c["ID"]).ToArray()), startDate, key, flowSqlHelper.DbName);
sb.AppendLine();
//如果存在日志修改记录，则停止执行sql
sb.AppendLine();
sb.AppendFormat(@"
--如果存在冲突则终止执行sql
if exists (select ID from S_UI_Form where Collision = 'Collision'
union
select ID from S_UI_List where Collision = 'Collision'
union
select ID from S_UI_Pivot where Collision = 'Collision'
union
select ID from S_UI_Selector where Collision = 'Collision'
union
select ID from S_UI_Word where Collision = 'Collision'
union
select ID from S_UI_ExcelPrint where Collision = 'Collision'
union
select ID from S_R_Define where Collision = 'Collision'
union
select ID from S_M_EnumDef where Collision = 'Collision'
union
select ID from {0}.dbo.S_WF_DefFlow where Collision = 'Collision')
begin
select '请先处理冲突！'
return
end
", flowSqlHelper.DbName);
//表单
UIFO uiFO = new UIFO();
foreach (DataRow row in dtForm.Rows)
{
sb.AppendFormat(@"
if not exists (select * from S_UI_Form where Collision in('keep','{0}') and ID ='{1}')
begin
delete S_UI_Form where ID='{1}'
{2}
{3}
{4}
end
", key
, row["ID"]
, SQLHelper.CreateInsertSql("S_UI_Form", row)
, string.Format("USE [{0}_{1}]", preDBName, row["ConnName"])
, uiFO.CreateReleaseFormSql(row["ID"].ToString())
);
sb.AppendFormat("USE [{0}_Base]", preDBName);
sb.AppendLine();
}
//列表
foreach (DataRow row in dtList.Rows)
{
sb.AppendFormat(@"
if not exists (select * from S_UI_List where Collision in('keep','{0}') and ID ='{1}')
begin
delete S_UI_List where ID='{1}'
{2}
end
", key, row["ID"], SQLHelper.CreateInsertSql("S_UI_List", row));
}
//Word
foreach (DataRow row in dtWord.Rows)
{
sb.AppendFormat(@"
if not exists (select * from S_UI_Word where Collision in('keep','{0}') and ID ='{1}')
begin
delete S_UI_Word where ID='{1}'
{2}
end
", key, row["ID"], SQLHelper.CreateInsertSql("S_UI_Word", row));
}
//ExcelPrint
foreach (DataRow row in dtExcelPrint.Rows)
{
sb.AppendFormat(@"
if not exists (select * from S_UI_ExcelPrint where Collision in('keep','{0}') and ID ='{1}')
begin
delete S_UI_ExcelPrint where ID='{1}'
{2}
end
", key, row["ID"], SQLHelper.CreateInsertSql("S_UI_ExcelPrint", row));
}
//选择器
foreach (DataRow row in dtSelector.Rows)
{
sb.AppendFormat(@"
if not exists (select * from S_UI_Selector where Collision in('keep','{0}') and ID ='{1}')
begin
delete S_UI_Selector where ID='{1}'
{2}
end
", key, row["ID"], SQLHelper.CreateInsertSql("S_UI_Selector", row));
}
//透视表
foreach (DataRow row in dtPivot.Rows)
{
sb.AppendFormat(@"
if not exists (select * from S_UI_Pivot where Collision in('keep','{0}') and ID ='{1}')
begin
delete S_UI_Pivot where ID='{1}'
{2}
end
", key, row["ID"], SQLHelper.CreateInsertSql("S_UI_Pivot", row));
}
//报表
foreach (DataRow row in dtReport.Rows)
{
var dtReportSub = sqlHelper.ExecuteDataTable(string.Format("select * from S_R_DataSet where DefineID in('{0}')", row["ID"]));
var dtReportSubSub = sqlHelper.ExecuteDataTable(string.Format("select * from S_R_Field where DataSetID in('{0}')", string.Join("','", dtReportSub.AsEnumerable().Select(c => c["ID"].ToString()))));
sb.AppendFormat(@"
if not exists (select * from S_R_Define where Collision in('keep','{0}') and ID ='{1}')
begin
delete S_R_Define where ID='{1}'
{2}
{3}
{4}
end
", key
, row["ID"]
, SQLHelper.CreateInsertSql("S_R_Define", row)
, SQLHelper.CreateInsertSql("S_R_DataSet", dtReportSub)
, SQLHelper.CreateInsertSql("S_R_Field", dtReportSubSub)
);
}
//枚举
foreach (DataRow row in dtEnum.Rows)
{
var dtEnumSub = sqlHelper.ExecuteDataTable(string.Format("select * from S_M_EnumItem where EnumDefID in('{0}')", row["ID"]));
sb.AppendFormat(@"
if not exists (select * from S_M_EnumDef where Collision in('keep','{0}') and ID ='{1}')
begin
delete S_M_EnumDef where ID='{1}'
{2}
{3}
end
", key
, row["ID"]
, SQLHelper.CreateInsertSql("S_M_EnumDef", row)
, SQLHelper.CreateInsertSql("S_M_EnumItem", dtEnumSub));
}
//流程
sb.AppendLine();
sb.AppendFormat("USE [{0}]", flowSqlHelper.DbName);
sb.AppendLine();
foreach (DataRow row in dtWorkflow.Rows)
{
sql = string.Format("select * from S_WF_DefStep where DefFlowID='{0}'", row["ID"]);
DataTable dtDefStep = flowSqlHelper.ExecuteDataTable(sql);
sql = string.Format("select * from S_WF_DefRouting where DefFlowID='{0}'", row["ID"]);
DataTable dtDefRouting = flowSqlHelper.ExecuteDataTable(sql);
sql = string.Format("select * from S_WF_DefSubForm where DefFlowID='{0}'", row["ID"]);
DataTable dtDefSubForm = flowSqlHelper.ExecuteDataTable(sql);
sb.AppendFormat(@"
if not exists (select * from S_WF_DefFlow where Collision in('keep','{0}') and ID ='{1}')
begin
delete S_WF_DefFlow where ID='{1}'
{2}
{3}
{4}
{5}
end
", key
, row["ID"]
, SQLHelper.CreateInsertSql("S_WF_DefFlow", row)
, SQLHelper.CreateInsertSql("S_WF_DefStep", dtDefStep)
, SQLHelper.CreateInsertSql("S_WF_DefRouting", dtDefRouting)
, SQLHelper.CreateInsertSql("S_WF_DefSubForm", dtDefSubForm)
);
}
sb.AppendFormat("USE [{0}]", sqlHelper.DbName);
//记录更新
StringBuilder sb1 = new StringBuilder();
sb1.AppendFormat("【表单名称】{0}\n", dtForm.Rows.Count == 0 ? "无" : string.Join(",", dtForm.AsEnumerable().Select(c => c["Name"].ToString()).ToArray()));
sb1.AppendFormat("【列表名称】{0}\n", dtList.Rows.Count == 0 ? "无" : string.Join(",", dtList.AsEnumerable().Select(c => c["Name"].ToString()).ToArray()));
sb1.AppendFormat("【流程名称】{0}\n", dtWorkflow.Rows.Count == 0 ? "无" : string.Join(",", dtWorkflow.AsEnumerable().Select(c => c["Name"].ToString()).ToArray()));
sb1.AppendFormat("【报表名称】{0}\n", dtReport.Rows.Count == 0 ? "无" : string.Join(",", dtReport.AsEnumerable().Select(c => c["Name"].ToString()).ToArray()));
sb1.AppendFormat("【选择器名称】{0}\n", dtSelector.Rows.Count == 0 ? "无" : string.Join(",", dtSelector.AsEnumerable().Select(c => c["Name"].ToString()).ToArray()));
sb1.AppendFormat("【透视表名称】{0}\n", dtPivot.Rows.Count == 0 ? "无" : string.Join(",", dtPivot.AsEnumerable().Select(c => c["Name"].ToString()).ToArray()));
sb1.AppendFormat("【Word导出名称】{0}\n", dtWord.Rows.Count == 0 ? "无" : string.Join(",", dtWord.AsEnumerable().Select(c => c["Name"].ToString()).ToArray()));
sb1.AppendFormat("【枚举名称】{0}\n", dtEnum.Rows.Count == 0 ? "无" : string.Join(",", dtEnum.AsEnumerable().Select(c => c["Name"].ToString()).ToArray()));
S_UI_ModifyLogLog log = new S_UI_ModifyLogLog();
log.ID = key;
log.IOTime = DateTime.Now;
log.IOType = "导出";
log.CategoryName = categoryName;
log.RelateData = sb1.ToString();
entities.Set <S_UI_ModifyLogLog>().Add(log);
entities.SaveChanges();
sb.AppendLine();
sb.AppendFormat(@"
delete S_UI_ModifyLogLog where ID='{0}'
insert into S_UI_ModifyLogLog ([ID],[IOTime],[IOType],[RelateData],[CategoryName]) values ('{0}','{1}','导入','{2}','{3}')
", key, DateTime.Now, sb1.ToString(), categoryName);
//更新冲突状态为完成
sb.AppendFormat("USE [{0}]", sqlHelper.DbName);
sb.AppendLine();
sb.AppendFormat("update S_UI_Form set Collision='{1}' where ID in('{0}')", string.Join("','", dtForm.AsEnumerable().Select(c => c["ID"].ToString())), key);
sb.AppendLine();
sb.AppendFormat("update S_UI_List set Collision='{1}' where ID in('{0}')", string.Join("','", dtList.AsEnumerable().Select(c => c["ID"].ToString())), key);
sb.AppendLine();
sb.AppendFormat("update S_UI_Pivot set Collision='{1}' where ID in('{0}')", string.Join("','", dtPivot.AsEnumerable().Select(c => c["ID"].ToString())), key);
sb.AppendLine();
sb.AppendFormat("update S_UI_Selector set Collision='{1}' where ID in('{0}')", string.Join("','", dtSelector.AsEnumerable().Select(c => c["ID"].ToString())), key);
sb.AppendLine();
sb.AppendFormat("update S_UI_Word set Collision='{1}' where ID in('{0}')", string.Join("','", dtWord.AsEnumerable().Select(c => c["ID"].ToString())), key);
sb.AppendLine();
sb.AppendFormat("update S_R_Define set Collision='{1}' where ID in('{0}')", string.Join("','", dtReport.AsEnumerable().Select(c => c["ID"].ToString())), key);
sb.AppendLine();
sb.AppendFormat("update S_M_EnumDef set Collision='{1}' where ID in('{0}')", string.Join("','", dtEnum.AsEnumerable().Select(c => c["ID"].ToString())), key);
sb.AppendLine();
sb.AppendFormat("update {2}.dbo.S_WF_DefFlow set Collision='{1}' where ID in('{0}')", string.Join("','", dtWorkflow.AsEnumerable().Select(c => c["ID"].ToString())), key, flowSqlHelper.DbName);
MemoryStream ms = new MemoryStream(System.Text.Encoding.Default.GetBytes(sb.ToString()));
ms.Position = 0;
return(File(ms, "application/octet-stream ; Charset=UTF8", "base" + startDate + "更新脚本.sql"));
}
public JsonResult GetList(string tmplCode, QueryBuilder qb)
{
var listDef = entities.Set <S_UI_Layout>().Where(c => c.Code == tmplCode).First();
SQLHelper sqlHeler = SQLHelper.CreateSqlHelper(listDef.ConnName);
UIFO uiFO = FormulaHelper.CreateFO <UIFO>();
string sql = uiFO.ReplaceString(listDef.SQL);
#region 地址栏过滤
// string sqlTmpl = string.Format(@"SELECT fieldCode= a.name , description= isnull(g.[value],''),fieldType=b.name,sortIndex=a.column_id
//FROM sys.columns a left join sys.extended_properties g on (a.object_id = g.major_id AND g.minor_id = a.column_id)
//left join systypes b on a.user_type_id=b.xusertype
//WHERE object_id =(SELECT object_id FROM sys.tables WHERE name in('{0}'))", listDef.TableNames.Replace(",", "','"));
// if (Config.Constant.IsOracleDb)
// {
// sqlTmpl = string.Format(@"select column_name as fieldCode from user_tab_columns where table_name in('{0}')", listDef.TableNames.Replace(",", "','"));
// }
// DataTable dtTmpl = sqlHeler.ExecuteDataTable(sqlTmpl);
// //地址栏参数作为查询条件
// var queryDtTmpl = dtTmpl.AsEnumerable();
// 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 (queryDtTmpl.Count(c => c["fieldCode"].ToString().ToLower() == key.ToLower()) > 0)
// qb.Add(key, QueryMethod.In, Request[key]);
// }
DataTable dtTmpl = sqlHeler.ExecuteDataTable(sql, qb);
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
GridData data = null;
if (listDef.LayoutGrid.Contains("\"showPager\":\"false\""))
{
data = new GridData(sqlHeler.ExecuteDataTable(sql, (SearchCondition)qb, listDef.OrderBy));
}
else
{
data = sqlHeler.ExecuteGridData(sql, qb, listDef.OrderBy);
}
#region 计算汇总
var fields = JsonHelper.ToList(listDef.LayoutField);
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 = "";
if (Config.Constant.IsOracleDb)
{
collectSql = string.Format("select {0} from (select * from ({1}) tb where 1=1 {2}) T"
, sb.ToString().Trim(',')
, sql
, qb.GetWhereString(false) + FormulaHelper.CreateAuthDataFilter().GetWhereString(false) + companyAuth
);
}
else
{
collectSql = string.Format("select {0} from (select * from ({1}) as tb where 1=1 {2}) as T"
, sb.ToString().Trim(',')
, sql
, qb.GetWhereString(false) + FormulaHelper.CreateAuthDataFilter().GetWhereString(false) + companyAuth
);
}
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));
}
