protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { if (!string.IsNullOrEmpty(Page.Request.QueryString["sourceID"])) { hidsourceid.Value = Page.Request.QueryString["sourceID"]; } if (!string.IsNullOrEmpty(Page.Request.QueryString["templateID"])) { hidTemplateID.Value = Page.Request.QueryString["templateID"]; } if (!string.IsNullOrEmpty(hidsourceid.Value) && !string.IsNullOrEmpty(hidTemplateID.Value)) { string sourceid = hidsourceid.Value.Trim(); BS_DATASOURCEBLL bll = new BS_DATASOURCEBLL(); CMICT.CSP.Model.BS_DATASOURCE model = bll.GetModel(Guid.Parse(sourceid)); if (model != null) { dc = new DisplayConfigComponent(model.SourceIP, model.UserName, model.Password, model.DBName); } BindColumnList(); BindControltype(); BindComparetype(); BindInfoIfEdit(hidTemplateID.Value); } } }
//根据sourceid获取数据源信息 protected void BindColumnList() { string sourceid = hidsourceid.Value.Trim(); BS_DATASOURCEBLL blld = new BS_DATASOURCEBLL(); CMICT.CSP.Model.BS_DATASOURCE model = blld.GetModel(Guid.Parse(sourceid)); string templateid = hidTemplateID.Value.Trim(); if (model != null) { if (!string.IsNullOrEmpty(templateid))//绑定已经配置的列名与显示名信息 { DataTable dtt = bll.GetUserQueryColListByTemplateID(Guid.Parse(templateid)); if (dtt != null && dtt.Rows.Count > 0) { string configcolinfo = string.Empty; foreach (DataRow drr in dtt.Rows) { configcolinfo += Convert.ToString(drr["name"]) + "|" + Convert.ToString(drr["DisplayName"]) + ";"; } hidconfigcolinfo.Value = configcolinfo; } } DataTable dt = dc.GetColumnListByType(model.ObjectType, model.ObjectName, ""); //绑定默认筛选条件中列名下拉 ddlColumnName.DataSource = dt; ddlColumnName.DataTextField = "name"; ddlColumnName.DataValueField = "data_type"; ddlColumnName.DataBind(); ddlColumnName.Items.Insert(0, new ListItem("请选择", "")); ViewState["DfColumnList"] = dt; //绑定排序中列名下拉 ddluqcol.DataSource = dt; ddluqcol.DataTextField = "name"; ddluqcol.DataValueField = "name"; ddluqcol.DataBind(); ddluqcol.Items.Insert(0, new ListItem("请选择", "")); ViewState["ColumnList"] = dt; if (model.ObjectType == "PROC") { if (dt != null && dt.Rows.Count > 0) { string paralist = string.Empty; foreach (DataRow dr in dt.Rows) { string cname = Convert.ToString(dr["name"]).Trim(); if (cname.IndexOf('@') != -1) { paralist += cname + ","; } } hidprocparalist.Value = paralist.TrimEnd(','); } } } }
//删除 protected void lbtndel_Click(object sender, EventArgs e) { string SourceID = hidsourceid.Value.Trim(); if (!string.IsNullOrEmpty(SourceID)) { BS_DATASOURCEBLL bll = new BS_DATASOURCEBLL(); if (bll.Delete(Guid.Parse(SourceID))) { hidsourceid.Value = ""; ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "dsdelsuc", "layer.alert('数据源删除成功!',9);", true); BindDataSourceList(); } else { ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "dsdelerror", "layer.alert('数据源删除失败!',8);", true); } } }
protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { if (!string.IsNullOrEmpty(Page.Request.QueryString["sourceID"])) { hidsourceid.Value = Page.Request.QueryString["sourceID"]; string sourceid = hidsourceid.Value.Trim(); BS_DATASOURCEBLL bll = new BS_DATASOURCEBLL(); CMICT.CSP.Model.BS_DATASOURCE model = bll.GetModel(Guid.Parse(sourceid)); if (model != null) { dcserver = new DisplayConfigComponent(model.SourceIP, model.UserName, model.Password, model.DBName); if (model.ObjectType.ToLower() == "proc") { hiddbtype.Value = "proc"; } } } try { if (!string.IsNullOrEmpty(Page.Request.QueryString["templateID"])) { hidTemplateID.Value = Page.Request.QueryString["templateID"]; BindDisplayType(); BindColumnIfEdit(hidTemplateID.Value); BindColumnList(); BindCalColumnList(); BindDecimal();//绑定小数位数 } else { ScriptManager.RegisterStartupScript(this.Page, this.Page.GetType(), "alertnomodel", "layer.alert('未获取到模板信息,请联系管理员!',8);", true); } }catch (Exception ex) { BaseComponent.Error("DisplayConfig-pageload:" + ex.Message); ScriptManager.RegisterStartupScript(this.Page, this.Page.GetType(), "alertnomodelerror", "layer.alert('数据源连接失败,请联系管理员!',8);", true); } } }
//绑定计算列信息 protected void BindCalColumnList() { //if (ddlDisplayType.SelectedValue == "COLUMN" || ddlDisplayType.SelectedValue == "ROW") //if (!string.IsNullOrEmpty(ddlDisplayType.SelectedValue)) //{ string sourceid = hidsourceid.Value.Trim(); BS_DATASOURCEBLL bll = new BS_DATASOURCEBLL(); CMICT.CSP.Model.BS_DATASOURCE model = bll.GetModel(Guid.Parse(sourceid)); if (model != null) { DataTable dt = dcserver.GetColumnListByTypeOnlyNum(model.ObjectType, model.ObjectName); //if (ddlDisplayType.SelectedValue == "COLUMN") //{ rptcallist.DataSource = dt; rptcallist.DataBind(); //} //if (ddlDisplayType.SelectedValue == "ROW") //{ //string temid = hidTemplateID.Value.Trim(); //QueryConfigComponent qcc = new QueryConfigComponent(); //DataTable gcdt = qcc.GetUserQueryColListByTemplateID(Guid.Parse(temid), 1); //if (gcdt != null && gcdt.Rows.Count > 0) //{ // ddlgroupcalcol.DataSource = dt; // ddlgroupcalcol.DataTextField = "name"; // ddlgroupcalcol.DataValueField = "name"; // ddlgroupcalcol.DataBind(); //ddlgroupcalcol.Items.Insert(0, new ListItem("请选择", "")); //} //绑定计算方式 DataTable dtt = BaseComponent.GetLookupValuesByType("BS_GROUPY_COMPUTERTYPE"); ddlcomputertype.DataSource = dtt; ddlcomputertype.DataTextField = "LOOKUP_VALUE_NAME"; ddlcomputertype.DataValueField = "LOOKUP_VALUE"; ddlcomputertype.DataBind(); ddlcomputertype.Items.Insert(0, new ListItem("请选择", "")); // } } //} }
protected void ShowInfo(string sourceID) { BindBigCategory(); if (!string.IsNullOrEmpty(sourceID)) { BS_DATASOURCEBLL bll = new BS_DATASOURCEBLL(); CMICT.CSP.Model.BS_DATASOURCE model = bll.GetModel(Guid.Parse(sourceID)); if (model != null) { hidOperType.Value = "edit"; ddlCATEGORY.SelectedValue = model.BigCategory; BindSmallCategory(model.BigCategory); ddlsmallcategory.SelectedValue = model.SmallCategory; BindDataSource(model.BigCategory, model.SmallCategory); ddlSourceName.SelectedValue = model.SourceID.ToString(); txtCATEGORY.Text = model.BigCategory; txtsmallcategory.Text = model.SmallCategory; txtSourceName.Text = model.SourceName; } } }
//根据sourceid获取数据源信息 protected void BindColumnList() { string sourceid = hidsourceid.Value.Trim(); BS_DATASOURCEBLL bll = new BS_DATASOURCEBLL(); CMICT.CSP.Model.BS_DATASOURCE model = bll.GetModel(Guid.Parse(sourceid)); if (model != null) { DataTable dt = dcserver.GetColumnListByType(model.ObjectType, model.ObjectName); ColumnList.DataSource = dt; ColumnList.DataBind(); //绑定排序中列名下拉 ddlColumnName.DataSource = dt; ddlColumnName.DataTextField = "name"; ddlColumnName.DataValueField = "name"; ddlColumnName.DataBind(); ddlColumnName.Items.Insert(0, new ListItem("请选择", "")); //if (ddlDisplayType.SelectedValue == "ROW") //{ // rptgroupcol.DataSource = dt; // rptgroupcol.DataBind(); //} } }
private void AddTemplateByGuid(string guid) { Guid templateID = new Guid(guid); BS_TEMPLATE_MAINBLL templateBll = new BS_TEMPLATE_MAINBLL(); BS_TEMPLATE_MAIN template = templateBll.GetModel(templateID); if (template == null) { return; //模板已删除 } if (template.TemplateStatus == "DISABLE") //|| template.TemplateStatus == "DRAFT") { return; //模板为禁用状态 } TemplateModel templateModel = new TemplateModel(); templateModel.TemplateName = template.TemplateName; templateModel.TemplateDesc = template.TemplateDesc; templateModel.IsReleased = (template.TemplateStatus == "FREE" || template.TemplateStatus == "ENABLE"); //GridBuilder GridBuilder gb = new GridBuilder(); gb.DisplayType = template.DiaplayType; gb.ColumnSize = template.ColumnSize; templateModel.GridBuilder = gb; //SQLBuilder templateModel.SQLBuilder = new SQLBuilder(); BS_DATASOURCEBLL sourceBll = new BS_DATASOURCEBLL(); BS_DATASOURCE source = sourceBll.GetModel(template.SourceID); templateModel.SQLBuilder.IsProcudure = source.ObjectType == "PROC"; if (templateModel.SQLBuilder.IsProcudure) { templateModel.SQLBuilder.ProcCalColumns = new Dictionary <string, string>(); } templateModel.SQLBuilder.ConnectionStrings = BuildConnection(source.SourceIP, source.UserName, source.Password, source.DBName); if (templateModel.SQLBuilder.IsProcudure) { templateModel.SQLBuilder.TableName = source.ObjectName; } else { templateModel.SQLBuilder.TableName = "[" + source.ObjectName + "]"; } //Columns DisplayConfigComponent displayCom = new DisplayConfigComponent(); DataTable columnsData = displayCom.GetColumnListByTemplateID(templateID); DataTable computeColumns = displayCom.GetCalListByTemplateID(templateID); DataTable queryData = new DataTable(); queryData.Columns.Add("IsCal"); queryData.Columns.Add("ColumnName"); queryData.Columns.Add("DisplayName"); queryData.Columns.Add("Sequence", typeof(double)); queryData.Columns.Add("MergeColumnName"); queryData.Columns.Add("DecimalCount"); if (columnsData != null && columnsData.Rows.Count > 0) { foreach (DataRow dr in columnsData.Rows) { if (!bool.Parse(Convert.ToString(dr["Visiable"]))) { continue; } DataRow newDr = queryData.NewRow(); newDr["IsCal"] = "0"; newDr["ColumnName"] = dr["ColumnName"]; newDr["DisplayName"] = dr["DisplayName"]; newDr["Sequence"] = string.IsNullOrEmpty(Convert.ToString(dr["Sequence"])) ? double.MaxValue : dr["Sequence"]; newDr["MergeColumnName"] = dr["MergeColumnName"]; newDr["DecimalCount"] = "-1"; queryData.Rows.Add(newDr); } } if (computeColumns != null && computeColumns.Rows.Count > 0) { foreach (DataRow dr in computeColumns.Rows) { DataRow newDr = queryData.NewRow(); newDr["IsCal"] = "1"; newDr["ColumnName"] = dr["ComputeColumn"]; newDr["DisplayName"] = dr["DisplayName"]; newDr["Sequence"] = string.IsNullOrEmpty(Convert.ToString(dr["Sequence"])) ? double.MaxValue : dr["Sequence"]; newDr["MergeColumnName"] = dr["MergeColumnName"]; newDr["DecimalCount"] = dr["DecimalCount"]; queryData.Rows.Add(newDr); } } DataView dv = queryData.DefaultView; dv.Sort = "Sequence Asc,DisplayName Asc"; DataTable sortedColumns = dv.ToTable(); string columns = ""; //列名 string displayNames = ""; //显示名 string mergeNames = ""; //合并表头 string queryFields = ""; //用于拼sql语句 int calSeq = 1; foreach (DataRow dr in sortedColumns.Rows) { if (Convert.ToString(dr["IsCal"]) == "0") { displayNames += Convert.ToString(dr["DisplayName"]) + ","; mergeNames += Convert.ToString(dr["MergeColumnName"]) + ","; queryFields += Convert.ToString(dr["ColumnName"]) + ","; if (!templateModel.SQLBuilder.IsProcudure) { columns += "[" + Convert.ToString(dr["ColumnName"]) + "],"; } } else { string decimalcount = Convert.ToString(dr["DecimalCount"]); if (string.IsNullOrEmpty(Convert.ToString(dr["Sequence"]))) { continue; } queryFields += "bs_cal_col" + calSeq + ","; mergeNames += Convert.ToString(dr["MergeColumnName"]) + ","; displayNames += Convert.ToString(dr["DisplayName"]) + ","; string calstr = Convert.ToString(dr["ColumnName"]); string proccalstr = string.Empty; if (!string.IsNullOrEmpty(decimalcount) && decimalcount != "-1") { //proccalstr = "(Convert((" + calstr + ") * 1000 + 0.000000001,'System.Int32') / 1000)+'d3'"; proccalstr = "(Convert(" + calstr + ",'System.String'))+'|" + decimalcount + "'"; calstr = "Convert(decimal(20," + decimalcount + ")," + calstr + ")"; } if (templateModel.SQLBuilder.IsProcudure) { templateModel.SQLBuilder.ProcCalColumns.Add("bs_cal_col" + calSeq, proccalstr); //templateModel.SQLBuilder.ProcCalColumns.Add("bs_cal_col" + calSeq, Convert.ToString(dr["ColumnName"])); Convert(" + calstr + ",decimal(20," + decimalcount + ")) } else { //columns += "(" + Convert.ToString(dr["ColumnName"]) + ") as bs_cal_col" + calSeq + ","; columns += "(" + calstr + ") as bs_cal_col" + calSeq + ","; } calSeq++; } } //排序:Sequence为空时,排在最后 foreach (DataRow dr in sortedColumns.Rows) { if (!string.IsNullOrEmpty(Convert.ToString(dr["Sequence"]))) { continue; } queryFields += "bs_cal_col" + calSeq + ","; string decimalcount = Convert.ToString(dr["DecimalCount"]); mergeNames += Convert.ToString(dr["MergeColumnName"]) + ","; displayNames += Convert.ToString(dr["DisplayName"]) + ","; string calstr = Convert.ToString(dr["ColumnName"]); string proccalstr = string.Empty; if (!string.IsNullOrEmpty(decimalcount) && decimalcount != "-1") { //proccalstr = "(Convert((" + calstr + ") * 1000 + 0.000000001,'System.Int32') / 1000)+'d3'"; proccalstr = "(Convert(" + calstr + ",'System.String'))+'|" + decimalcount + "'"; calstr = "Convert(decimal(20," + decimalcount + ")," + calstr + ")"; } if (templateModel.SQLBuilder.IsProcudure) { templateModel.SQLBuilder.ProcCalColumns.Add("bs_cal_col" + calSeq, proccalstr); } else { columns += "(" + calstr + ") as bs_cal_col" + calSeq + ","; } calSeq++; } queryFields = queryFields.TrimEnd(','); columns = columns.TrimEnd(','); if (!string.IsNullOrEmpty(mergeNames)) { mergeNames = mergeNames.Substring(0, mergeNames.Length - 1); } displayNames = displayNames.TrimEnd(','); templateModel.SQLBuilder.ColumnNames = queryFields; templateModel.SQLBuilder.DisplayNames = displayNames; templateModel.SQLBuilder.MergeColumnNames = mergeNames; templateModel.SQLBuilder.SelectSQL = columns; //隐藏列 DataTable commColumns = GetCommunicationColumns(guid); if (commColumns != null && commColumns.Rows.Count > 0) { string hiddenColumns = string.Empty; string hiddenSelectColumns = string.Empty; foreach (DataRow dr in commColumns.Rows) { string hiddenColumn = Convert.ToString(dr["SourceColumnName"]); if (!queryFields.Split(',').Contains(hiddenColumn)) { hiddenSelectColumns += "[" + Convert.ToString(dr["SourceColumnName"]) + "],"; hiddenColumns += Convert.ToString(dr["SourceColumnName"]) + ","; } } if (!string.IsNullOrEmpty(hiddenColumns.TrimEnd(','))) { templateModel.SQLBuilder.SelectSQL += "," + hiddenSelectColumns.TrimEnd(','); templateModel.SQLBuilder.HiddenNames = hiddenColumns.TrimEnd(','); } } //DefaultQuery QueryConfigComponent queryCom = new QueryConfigComponent(); StringBuilder defaultQuery = new StringBuilder(); defaultQuery.Append(" where 1=1 "); StringBuilder subQuery = new StringBuilder(); string mainLogic = string.Empty; int i = 1; while (true) { DataTable queryDt = queryCom.GetDefaultQueryListInfoByTemplateID(templateID, i); if (queryDt == null || queryDt.Rows.Count == 0) { break; } if (Convert.ToString(queryDt.Rows[0]["SubLogic"]) == "proc") //处理存储过程参数 { Dictionary <string, string> list = new Dictionary <string, string>(); foreach (DataRow dr in queryDt.Rows) { list.Add(Convert.ToString(dr["ColumnName"]), Convert.ToString(dr["CompareValue"])); } templateModel.SQLBuilder.Parameters = list; i++; continue; } mainLogic = Convert.ToString(queryDt.Rows[0]["MainLogic"]); string subLogic = Convert.ToString(queryDt.Rows[0]["SubLogic"]); subQuery.Append(" ("); foreach (DataRow dr in queryDt.Rows) { if (templateModel.SQLBuilder.IsProcudure) { subQuery.Append(Convert.ToString(dr["ColumnName"]) + " " + string.Format(FormatCompare(Convert.ToString(dr["Compare"]), templateModel.SQLBuilder.IsProcudure), Convert.ToString(dr["CompareValue"]))); } else { subQuery.Append("[" + Convert.ToString(dr["ColumnName"]) + "] " + string.Format(FormatCompare(Convert.ToString(dr["Compare"]), templateModel.SQLBuilder.IsProcudure), Convert.ToString(dr["CompareValue"]))); } subQuery.Append(" " + subLogic + " "); } subQuery.Remove(subQuery.Length - subLogic.Length - 1, subLogic.Length); subQuery.Append(") "); subQuery.Append(mainLogic); i++; } if (subQuery.Length != 0) { subQuery.Remove(subQuery.Length - mainLogic.Length, mainLogic.Length); defaultQuery.Append(" and ("); defaultQuery.Append(subQuery.ToString()); defaultQuery.Append(")"); } templateModel.SQLBuilder.DefauleQuery = defaultQuery.ToString(); //QueryControls QueryConfigComponent queryBll = new QueryConfigComponent(); DataTable query = queryBll.GetUserQueryListByTemplateIDForModel(templateID); if (query != null && query.Rows.Count > 0) { templateModel.QueryControls = new List <QueryControls>(); foreach (DataRow dr in query.Rows) { QueryControls controls = new QueryControls(); controls.Reminder = Convert.ToString(dr["Reminder"]); controls.DefautValue = Convert.ToString(dr["DefaultValue"]); if (templateModel.SQLBuilder.IsProcudure) { controls.ColumnName = Convert.ToString(dr["ColumnName"]); } else { controls.ColumnName = "[" + Convert.ToString(dr["ColumnName"]) + "]"; } controls.DisplayName = Convert.ToString(dr["DisplayName"]); controls.ControlType = Convert.ToString(dr["ControlType"]); controls.Compare = controls.ColumnName + FormatCompare(Convert.ToString(dr["Compare"]), templateModel.SQLBuilder.IsProcudure); if (Convert.ToString(dr["Compare"]) == "CONTAIN" || Convert.ToString(dr["Compare"]) == "NOTCONTAIN") { controls.IsLike = true; } else { controls.IsLike = false; } if (controls.ControlType == "ENUM" || controls.ControlType == "MULTENUM" || controls.ControlType == "MATCH") { controls.SourceSql = "select distinct(" + controls.ColumnName + ") as EnumValue from " + templateModel.SQLBuilder.TableName + " " + templateModel.SQLBuilder.DefauleQuery; } templateModel.QueryControls.Add(controls); } } //DefaultOrderby DataTable orderby = displayCom.GetSortListByTemplateID(templateID); if (orderby != null && orderby.Rows.Count > 0) { string orderbySQL = ""; foreach (DataRow dr in orderby.Rows) { if (templateModel.SQLBuilder.IsProcudure) { orderbySQL += Convert.ToString(dr["SortColumn"]) + " " + Convert.ToString(dr["Type"]) + ","; } else { orderbySQL += "[" + Convert.ToString(dr["SortColumn"]) + "] " + Convert.ToString(dr["Type"]) + ","; } } orderbySQL = orderbySQL.TrimEnd(','); templateModel.SQLBuilder.Orderby = orderbySQL; } //Groupby DataTable groupData = displayCom.GetGroupByListByTemplateID(templateID); if (groupData != null && groupData.Rows.Count > 0) { templateModel.GroupBy = new List <GroupBy>(); foreach (DataRow dr in groupData.Rows) { GroupBy group = new GroupBy(); group.Columns = Convert.ToString(dr["Columns"]); group.IsAtLast = Convert.ToString(dr["Location"]) == "LAST"; Dictionary <string, string> dic = new Dictionary <string, string>(); foreach (string groupSql in Convert.ToString(dr["ComputeColumn"]).Split(';')) { string[] gs = groupSql.Split(','); //string key = groupSql.Substring(groupSql.IndexOf('(')).TrimEnd(')').TrimStart('('); string key = gs[0].Substring(gs[0].IndexOf('(')).TrimEnd(')').TrimStart('('); dic.Add(key, groupSql); } group.GroupByColumns = dic; templateModel.GroupBy.Add(group); } } //Communication CommunicationConfigComponent communicationCom = new CommunicationConfigComponent(); DataTable commData = communicationCom.GetCommunicationByTemplateID(guid); if (commData != null && commData.Rows.Count > 0) { templateModel.Communication = new List <Communication>(); foreach (DataRow dr in commData.Rows) { Communication comModel = new Communication(); comModel.SourceTemplateID = Convert.ToString(dr["SourceTemplateID"]); comModel.CoumunicationID = Convert.ToString(dr["CommunicationID"]); comModel.Fields = new Dictionary <string, string>(); DataTable commDetailData = communicationCom.GetCommunicationFields(Convert.ToString(dr["CommunicationID"])); if (commDetailData != null && commDetailData.Rows.Count > 0) { foreach (DataRow dr2 in commDetailData.Rows) { if (dr2["SourceColumnName"] == null || string.IsNullOrEmpty(dr2["SourceColumnName"].ToString())) { continue; } comModel.Fields.Add(Convert.ToString(dr2["SourceColumnName"]), Convert.ToString(dr2["TargetColumnName"])); } } templateModel.Communication.Add(comModel); } } templateModel.SQLBuilder.PageSize = Convert.ToString(template.PageSize); if (templateModel.SQLBuilder.IsProcudure != true && templateModel.GroupBy == null) { templateModel.IsTruePaged = true; } else { templateModel.IsTruePaged = false; } MemcachedCache mc = new MemcachedCache(); mc.Put <TemplateModel>(cacheHeader + guid.ToLower(), templateModel); }