public JsonResult GetRoleList(QueryBuilder qb)
{
string roleType = this.Request["roleType"];
if (String.IsNullOrEmpty(roleType))
{
roleType = Project.Logic.RoleType.ProjectRoleType.ToString();
}
if (roleType == Project.Logic.RoleType.ProjectRoleType.ToString())
{
var list = entities.Set <S_D_RoleDefine>().WhereToGridData(qb);
return(Json(list));
}
else
{
var service = FormulaHelper.GetService <IRoleService>();
var list = service.GetSysRoles().AsQueryable().Where(qb).ToList();
var data = FormulaHelper.CollectionToListDic <Config.Role>(list);
foreach (var item in data)
{
item["RoleName"] = item["Name"].ToString();
item["RoleCode"] = item["Code"].ToString();
item["RoleType"] = Project.Logic.RoleType.SysRoleType.ToString();
}
var gridData = new GridData(data);
return(Json(gridData));
}
}
public JsonResult GetRoleList(QueryBuilder qb)
{
string roleType = this.Request["roleType"];
qb.PageSize = 0;
if (String.IsNullOrEmpty(roleType))
{
roleType = EPC.Logic.RoleType.ProjectRoleType.ToString();
}
if (roleType == EPC.Logic.RoleType.ProjectRoleType.ToString())
{
qb.SortField = "SortIndex";
qb.SortOrder = "asc";
string sql = "select ID,RoleCode,RoleName,'ProjectRoleType' as RoleType,SortIndex from S_T_RoleDefine";
var list = this.SqlHelper.ExecuteGridData(sql, qb);
return(Json(list));
}
else
{
var service = FormulaHelper.GetService <IRoleService>();
var list = service.GetSysRoles().AsQueryable().Where(qb).ToList();
var data = FormulaHelper.CollectionToListDic <Config.Role>(list);
foreach (var item in data)
{
item["RoleName"] = item["Name"].ToString();
item["RoleCode"] = item["Code"].ToString();
item["RoleType"] = EPC.Logic.RoleType.SysRoleType.ToString();
}
var gridData = new GridData(data);
return(Json(gridData));
}
}
protected override void AfterGetData(Dictionary <string, object> dic, bool isNew, string upperVersionID)
{
string tmplCode = Request["TmplCode"];
var formInfo = baseEntities.Set <S_UI_Form>().SingleOrDefault(c => c.Code == tmplCode);
if (formInfo == null)
{
throw new Exception("没有找到编号为【" + tmplCode + "】的表单定义");
}
var items = JsonHelper.ToList(formInfo.Items).Where(c => c.GetValue("ItemType") == "SubTable").ToList();
string ContractID = dic.GetValue("ContractID");
if (isNew)
{
ContractID = GetQueryString("ContractID");
foreach (var item in items)
{
var tableName = formInfo.TableName + "_" + item.GetValue("Code");
var contractTableName = "S_P_ContractInfo" + "_" + item.GetValue("Code");
string sql = "SELECT count(0) as TableCount FROM sysobjects WHERE name='{0}'";
var obj = Convert.ToInt32(this.EPCSQLDB.ExecuteScalar(String.Format(sql, contractTableName)));
if (obj > 0)
{
sql = "Select * from {0} where S_P_ContractInfoID='{1}'";
var subTable = this.EPCSQLDB.ExecuteDataTable(String.Format(sql, contractTableName, ContractID));
var subTableData = new List <Dictionary <string, object> >();
foreach (DataRow subItem in subTable.Rows)
{
var TmpDic = Formula.FormulaHelper.DataRowToDic(subItem);
//if (item.GetValue("Code") == "PaymentObj")
//{
// string paymentID = subItem["ID"].ToString();
// bool CantChange = false;
// if (EPCEntites.Set<S_P_Payment_PaymentObjRelation>().Any(a => a.PaymentObjID == paymentID)
// || EPCEntites.Set<S_P_Invoice_PaymentObjRelation>().Any(a => a.S_P_PaymentObjID == paymentID))
// {
// CantChange = true;
// }
// TmpDic.SetValue("CantChange", CantChange);
//}
TmpDic.SetValue("ID", "");
TmpDic.SetValue("OrlID", subItem["ID"]);
subTableData.Add(TmpDic);
}
var json = JsonHelper.ToJson(subTableData);
dic.SetValue(item.GetValue("Code"), json);//子表json赋值
}
}
}
var lastVersionID = dic.GetValue("LastVersionID");
if (string.IsNullOrEmpty(lastVersionID))
{
var contract = EPCEntites.Set <S_P_ContractInfo>().Find(ContractID);
dic.SetValue("LastVersionData", JsonHelper.ToJson(contract));
var cbsInfo = contract.S_P_ContractInfo_CBSInfo;
var cbsDics = FormulaHelper.CollectionToListDic(cbsInfo);
foreach (var cbsDic in cbsDics)
{
cbsDic.SetValue("OrlID", cbsDic.GetValue("ID"));
}
var content = contract.S_P_ContractInfo_Content;
var contentDics = FormulaHelper.CollectionToListDic(content);
foreach (var contentDic in contentDics)
{
contentDic.SetValue("OrlID", contentDic.GetValue("ID"));
}
var payment = contract.S_P_ContractInfo_PaymentObj;
var paymentDics = FormulaHelper.CollectionToListDic(payment);
foreach (var paymentDic in paymentDics)
{
paymentDic.SetValue("OrlID", paymentDic.GetValue("ID"));
}
dic.SetValue("LastCBSInfo", JsonHelper.ToJson(cbsDics));
dic.SetValue("LastContent", JsonHelper.ToJson(contentDics));
dic.SetValue("LastPaymentObj", JsonHelper.ToJson(paymentDics));
}
else
{
S_P_ProcurementContractChange change = EPCEntites.Set <S_P_ProcurementContractChange>().Find(lastVersionID);
if (change != null)
{
S_P_ContractInfo contract = new S_P_ContractInfo();
FormulaHelper.UpdateEntity(contract, change.ToDic());//只带合同信息,去掉审批字段数据
dic.SetValue("LastVersionData", JsonHelper.ToJson(contract));
dic.SetValue("LastCBSInfo", JsonHelper.ToJson(change.S_P_ProcurementContractChange_CBSInfo));
dic.SetValue("LastContent", JsonHelper.ToJson(change.S_P_ProcurementContractChange_Content));
dic.SetValue("LastPaymentObj", JsonHelper.ToJson(change.S_P_ProcurementContractChange_PaymentObj));
}
}
}
public JsonResult GetList(QueryBuilder qb, string DefinInfo, string Code)
{
string sql = @"select S_I_Engineering.*,
isnull(ContractValue,0) as ContractValue from S_I_Engineering
left join (select Sum(ContractRMBValue) as ContractValue,ProjectInfo from dbo.S_M_ContractInfo
where SignDate is not null
group by ProjectInfo) contractInfo
on S_I_Engineering.ID=contractInfo.ProjectInfo ";
var engineerInfoDt = this.SqlHelper.ExecuteDataTable(sql, qb);
var engineeringInfoIDs = (from d in engineerInfoDt.AsEnumerable()
select d.Field <string>("ID")).ToList <string>();
#region 设置查询的科目
var defineList = new List <Dictionary <string, object> >();
if (String.IsNullOrEmpty(DefinInfo))
{
var infrasEntities = FormulaHelper.GetEntities <InfrastructureEntities>();
var define = infrasEntities.S_T_CBSDefine.FirstOrDefault(c => c.Code == Code);
if (define == null)
{
define = infrasEntities.S_T_CBSDefine.FirstOrDefault();
}
if (define == null)
{
throw new Formula.Exceptions.BusinessValidationException("");
}
var root = define.S_T_CBSNodeTemplate.FirstOrDefault(c => c.NodeType == "Root");
var nodes = root.Children.Where(c => c.DefineType == "Static").OrderBy(c => c.SortIndex).ToList();
defineList = FormulaHelper.CollectionToListDic <S_T_CBSNodeTemplate>(nodes);
}
else
{
defineList = JsonHelper.ToList(DefinInfo);
}
#endregion
var defineIds = new List <string>();
foreach (var item in defineList)
{
defineIds.Add(item["ID"].ToString());
engineerInfoDt.Columns.Add(item["ID"].ToString() + "_Budget", typeof(decimal));
engineerInfoDt.Columns.Add(item["ID"].ToString() + "_Settle", typeof(decimal));
}
var cbsList = this.entities.Set <S_I_CBS>().Where(c => engineeringInfoIDs.Contains(c.EngineeringInfoID) && defineIds.Contains(c.CBSDefineID)).ToList();
foreach (DataRow item in engineerInfoDt.Rows)
{
var id = item["ID"].ToString();
var nodes = cbsList.Where(c => c.EngineeringInfoID == id).ToList();
foreach (var define in defineList)
{
item[define["ID"].ToString() + "_Budget"] = 0;
item[define["ID"].ToString() + "_Settle"] = 0;
}
foreach (var node in nodes)
{
if (node.Budget.HasValue)
{
item[node.CBSDefineID + "_Budget"] = node.Budget ?? 0;
}
if (node.Settle.HasValue)
{
item[node.CBSDefineID + "_Settle"] = node.Settle ?? 0;
}
}
}
var data = new GridData(engineerInfoDt);
data.total = qb.TotolCount;
return(Json(data));
}
public JsonResult GetList(QueryBuilder qb)
{
var QueryType = this.Request["QueryType"].ToString();
var ConfigID = this.Request["ConfigID"].ToString();
var SpaceID = this.Request["SpaceID"].ToString();
var QueryList = this.Request["AdvanceQueryList"].ToString();
List <Dictionary <string, object> > queryList = JsonHelper.ToList(QueryList);
List <Dictionary <string, object> > attrList = JsonHelper.ToList("");//结果字段对应的属性列表
var enumService = FormulaHelper.GetService <IEnumService>();
#region 拼查询sql
string fileFields = string.Empty;
string resultFileds = string.Empty;
string nodeStr = string.Empty;
string resultSql = string.Empty;
if (QueryType == "File")
{
var nodeConfig = this.Space.S_DOC_File.FirstOrDefault(d => d.ID == ConfigID);
if (nodeConfig == null)
{
throw new Formula.Exceptions.BusinessException("未能找ID为【" + ConfigID + "】查询结果定义对象");
}
resultSql = "select main.*{0} from S_FileInfo main {1} where ConfigID='" + ConfigID + "' and State='" + DocState.Published.ToString() + "' ";
attrList = FormulaHelper.CollectionToListDic(nodeConfig.S_DOC_FileAttr);
}
else if (QueryType == "Node")
{
var nodeConfig = this.Space.S_DOC_Node.FirstOrDefault(d => d.ID == ConfigID);
if (nodeConfig == null)
{
throw new Formula.Exceptions.BusinessException("未能找ID为【" + ConfigID + "】查询结果定义对象");
}
resultSql = "select main.*{0} from S_NodeInfo main {1} where ConfigID='" + ConfigID + "' and State='" + DocState.Published.ToString() + "' ";
attrList = FormulaHelper.CollectionToListDic(nodeConfig.S_DOC_NodeAttr);
}
else
{
throw new Formula.Exceptions.BusinessException("未定义查询类型【" + QueryType + "】");
}
//查询条件作为查询对象属性
string typeSql = string.Empty;
var types = queryList.Select(a => a.GetValue("TypeValue")).Distinct().ToList();//编目节点
string relateField = QueryType == "File" ? "FullNodeID" : "FullPathID";
foreach (var type in types)
{
var _queryList = queryList.Where(a => a.GetValue("TypeValue") == type).ToList();
var name = _queryList.FirstOrDefault().GetValue("Type");
string sql = " left join (select ID{2} from S_NodeInfo where ConfigID='{0}' and State='{4}' ) {1} on charindex({1}.ID,main.{3},1)>0 ";
string attrField = string.Empty;
foreach (var queryItem in _queryList)
{
var value = queryItem.GetValue("Value");
var af = queryItem.GetValue("AttrField");
var group = queryItem.GetValue("Group");
if (string.IsNullOrEmpty(value))
{
continue;
}
string fieldStr = af;
if (ConfigID != type)
{
fieldStr = name + "_" + af;
attrField += "," + af + " as " + fieldStr; //属性名
//结果列表字段
resultFileds += "," + fieldStr; //属性名
}
}
if (!string.IsNullOrEmpty(attrField))
{
sql = string.Format(sql, type, name, attrField, relateField, DocState.Published.ToString());
//查询条件 是 查询结果自身的属性
typeSql += sql;
}
}
resultSql = string.Format(resultSql, resultFileds, typeSql);
#endregion
#region 拼结果列字段
//枚举key列表
List <Dictionary <string, object> > enumList = JsonHelper.ToList("");
//查询列表字段
List <Dictionary <string, object> > columnList = JsonHelper.ToList("");
var listCofig = this.Space.S_DOC_ListConfig.FirstOrDefault(d => d.RelationID == ConfigID);
var gridColumnDetailList = listCofig.S_DOC_ListConfigDetail.Where(d => d.Dispaly == "True").OrderBy(d => d.DetailSort).ToList();
columnList.Add(new Dictionary <string, object>()
{
{ "type", "checkcolumn" }
}); //选择列
//columnList.Add(new Dictionary<string, object>() { { "type", "indexcolumn" } });//序号
columnList.Add(new Dictionary <string, object>()
{
{ "header", "" }, { "field", "View" }, { "width", "70" }
, { "align", "center" }, { "headerAlign", "center" }
}); //查看
string attrFieldStr = "FileAttrField";
if (QueryType != "File")
{
attrFieldStr = "AttrField";
}
foreach (var item in gridColumnDetailList)
{
var attrDic = attrList.FirstOrDefault(a => a.GetValue(attrFieldStr) == item.AttrField);
if (attrDic == null)
{
continue;
}
var column = new Dictionary <string, object>();
column.Add("header", item.AttrName);
column.Add("field", item.AttrField);
column.Add("name", item.AttrField);
column.Add("width", Convert.ToInt32(item.Width));
column.Add("allowSort", Convert.ToBoolean(item.AllowSort));
//if (Convert.ToBoolean(item.AllowSort))
// column.Add("sortField", item.AttrField);
column.Add("align", item.Align);
column.Add("headerAlign", "center");
var dataType = attrDic.GetValue("DataType");
if (dataType == AttrDataType.DateTime.ToString())
{
column.Add("dataType", "date");
column.Add("dateFormat", "yyyy-MM-dd");
}
//处理枚举
var isEnum = attrDic.GetValue("IsEnum");
if (isEnum == TrueOrFalse.True.ToString())
{
var enumKey = attrDic.GetValue("EnumKey");
var enumName = enumKey.Split('.').Last();
var enumJson = enumService.GetEnumJson(enumKey);
column.Add("renderer", "onEnumRender");
enumList.Add(new Dictionary <string, object>()
{
{ "Field", item.AttrField },
{ "EnumKey", enumKey },
{ "EnumName", enumName },
{ "EnumData", string.Format("var {0} = {1};", enumName, enumJson) }
});
}
columnList.Add(column);
}
#endregion
DocInstance.Logic.DocInstanceHepler.QueryBuilderExtend(qb, ConfigID, queryList);
DataTable dt = this.InstanceDB.ExecuteDataTable(resultSql, qb);
Dictionary <string, object> result = new Dictionary <string, object>();
result.SetValue("data", dt);
result.SetValue("total", qb.TotolCount);
result.SetValue("columns", columnList);
result.SetValue("enums", enumList);
#region 保存查询数据
var userDic = new Dictionary <string, object>();
userDic.Add("QueryType", QueryType);
userDic.Add("QueryNode", ConfigID);
userDic.Add("List", queryList);
var entities = FormulaHelper.GetEntities <DocConstEntities>();
var userInfo = FormulaHelper.GetUserInfo();
var userQueryInfo = entities.Set <S_UserAdvanceQueryInfo>().FirstOrDefault(d => d.UserID == userInfo.UserID);
if (userQueryInfo == null)
{
userQueryInfo = new S_UserAdvanceQueryInfo();
userQueryInfo.ID = FormulaHelper.CreateGuid();
userQueryInfo.UserID = userInfo.UserID;
userQueryInfo.CreateDate = DateTime.Now;
entities.Set <S_UserAdvanceQueryInfo>().Add(userQueryInfo);
}
userQueryInfo.QueryData = JsonHelper.ToJson <Dictionary <string, object> >(userDic);
userQueryInfo.ModifyDate = DateTime.Now;
entities.SaveChanges();
#endregion
return(Json(result));
}
public ActionResult ExportExcel(string VersionID, string jsonColumns, string title)
{
var columns = Newtonsoft.Json.JsonConvert.DeserializeObject <List <Formula.ImportExport.ColumnInfo> >(jsonColumns);
var exporter = new MvcAdapter.ImportExport.AsposeExcelExporter();
byte[] templateBuffer = null;
var excelKey = "BudgetBOQ";
var path = System.Configuration.ConfigurationManager.AppSettings["ExcelTemplatePath"];
var templatePath = path.EndsWith("\\") ? string.Format("{0}{1}_New.xls", path, excelKey) : string.Format("{0}\\{1}_New.xls", path, excelKey);
templatePath = Server.MapPath("/") + templatePath;
if (System.IO.File.Exists(templatePath))
{
Formula.LogWriter.Info(string.Format("ExportExcel - 采用自定义模板，模板路径为：{0}", templatePath));
templateBuffer = Formula.ImportExport.FileHelper.GetFileBuffer(templatePath);
}
else
{
templateBuffer = exporter.ParseTemplate(columns, excelKey, title);
}
var dt = new DataTable();
foreach (var item in columns)
{
if (dt.Columns.Contains(item.FieldName))
{
continue;
}
dt.Columns.Add(item.FieldName);
}
var detailList = GetSearchList(null, VersionID, "", "true");
List <Dictionary <string, object> > dicList = FormulaHelper.CollectionToListDic(detailList);
foreach (var dic in dicList)
{
var detailRow = dt.NewRow();
foreach (var detailColumn in columns)
{
if (detailColumn.FieldName == "Name")
{
string space = "";
if (!string.IsNullOrEmpty(dic.GetValue("CBSFullID")))
{
int spaceCount = dic.GetValue("CBSFullID").Split(',').Length;
for (int i = 0; i < spaceCount; i++)
{
space += " ";
}
}
detailRow[detailColumn.FieldName] = space + dic.GetValue(detailColumn.FieldName);
}
else
{
detailRow[detailColumn.FieldName] = dic.GetValue(detailColumn.FieldName);
}
}
dt.Rows.Add(detailRow);
}
dt.TableName = "BudgetBOQ";
var buffer = exporter.Export(dt, templateBuffer);
return(File(buffer, "application/vnd.ms-excel", Url.Encode(title) + ".xls"));
}
