/// <summary> /// 获得分组的数据源 /// </summary> /// <returns></returns> public string Dot2Dot_GenerGroupEntitis() { string key = this.GetRequestVal("DefaultGroupAttrKey"); //实体集合. string ensName = this.GetRequestVal("EnsOfM"); Entities ens = ClassFactory.GetEns(ensName); Entity en = ens.GetNewEntity; Attrs attrs = en.EnMap.Attrs; Attr attr = attrs.GetAttrByKey(key); if (attr == null) { return("err@设置的分组外键错误[" + key + "],不存在[" + ensName + "]或者已经被删除."); } if (attr.MyFieldType == FieldType.Normal) { return("err@设置的默认分组[" + key + "]不能是普通字段."); } if (attr.MyFieldType == FieldType.FK) { Entities ensFK = attr.HisFKEns; ensFK.Clear(); ensFK.RetrieveAll(); return(ensFK.ToJson()); } if (attr.MyFieldType == FieldType.Enum) { /* 如果是枚举 */ SysEnums ses = new SysEnums(); ses.Retrieve(SysEnumAttr.IntKey, attr.UIBindKey); //ses.ToStringOfSQLModelByKey BP.Pub.NYs nys = new Pub.NYs(); foreach (SysEnum item in ses) { BP.Pub.NY ny = new Pub.NY(); ny.No = item.IntKey.ToString(); ny.Name = item.Lab; nys.AddEntity(ny); } return(nys.ToJson()); } return("err@设置的默认分组[" + key + "]不能是普通字段."); }
public void InitLeft() { this.Left.AddTable(); string paras = this.ur.Paras; #region 项 this.Left.AddTR(); this.Left.AddTDTitle("colspan=2", "分析数据"); this.Left.AddTREnd(); DDL ddl = new DDL(); ddl.ID = "DDL_Num"; ddl.AutoPostBack = true; ddl.SelectedIndexChanged += new EventHandler(ddl_SelectedIndexChanged); ListItem li = null; this.Left.AddTR(); this.Left.AddTD("数据项"); Attrs attrs = this.HisMD.AttrsInTableEns; attrs.AddTBInt("MyNum", 1, "流程数量", true, true); foreach (Attr attr in attrs) { if (attr.UIContralType != UIContralType.TB) { continue; } if (attr.UIVisible == false) { continue; } if (attr.IsNum == false) { continue; } if (attr.Key == "OID" || attr.Key == "WorkID" || attr.Key == "FID" || attr.Key == "MID") { continue; } li = new ListItem(attr.Desc, attr.Key); if (paras.Contains("@Num=" + attr.Key)) { li.Selected = true; } ddl.Items.Add(li); } this.Left.AddTD(ddl); this.Left.AddTREnd(); this.Left.AddTR(); this.Left.AddTD("分析方式"); ddl = new DDL(); ddl.ID = "DDL_FXWay"; ddl.AutoPostBack = true; li = new ListItem("求和", "SUM"); if (paras.Contains("@FXWay=SUM")) { li.Selected = true; } ddl.Items.Add(li); li = new ListItem("求平均", "AVG"); if (paras.Contains("@FXWay=AVG")) { li.Selected = true; } ddl.Items.Add(li); ddl.SelectedIndexChanged += new EventHandler(ddl_SelectedIndexChanged); this.Left.AddTD(ddl); this.Left.AddTREnd(); ddl = new DDL(); ddl.ID = "DDL_Order"; ddl.AutoPostBack = true; ddl.SelectedIndexChanged += new EventHandler(ddl_SelectedIndexChanged); li = new ListItem("升序", "UP"); if (paras.Contains("@Order=UP")) { li.Selected = true; } ddl.Items.Add(li); li = new ListItem("降序", "DESC"); if (paras.Contains("@Order=DESC")) { li.Selected = true; } ddl.Items.Add(li); this.Left.AddTR(); this.Left.AddTD("排序方式"); this.Left.AddTD(ddl); this.Left.AddTREnd(); #endregion 项 #region 比较对象 this.Left.AddTR(); this.Left.AddTDTitle("colspan=2", "比较内容"); this.Left.AddTREnd(); ddl = new DDL(); ddl.ID = "DDL_D"; ddl.AutoPostBack = true; ddl.SelectedIndexChanged += new EventHandler(ddl_SelectedIndexChanged); this.Left.AddTR(); this.Left.AddTD("比较选项"); string dAttr = null; foreach (Attr attr in attrs) { if (attr.UIContralType == UIContralType.DDL) { li = new ListItem(attr.Desc, attr.Key); if (paras.Contains("@D=" + attr.Key)) { dAttr = attr.Key; li.Selected = true; } ddl.Items.Add(li); } } this.Left.AddTD(ddl); this.Left.AddTREnd(); /*获取分析项目的数据。*/ SysEnums sesD = null; Entities ensD = null; Map map = this.HisMD.HisEn.EnMap; Attr attrD1 = attrs.GetAttrByKey(this.DDL_D.SelectedItemStringVal); if (attrD1.IsEnum) { sesD = new SysEnums(attrD1.UIBindKey); } else { ensD = attrD1.HisFKEns; } this.Left.AddTR(); this.Left.AddTD("基准项目"); ddl = new DDL(); ddl.ID = "DDL_V1"; ddl.AutoPostBack = true; ddl.SelectedIndexChanged += new EventHandler(ddl_SelectedIndexChanged); if (sesD != null) { if (sesD.Count == 0) { throw new Exception(attrD1.UIBindKey); } foreach (SysEnum se in sesD) { li = new ListItem(se.Lab, se.IntKey.ToString()); if (paras.Contains("@V1=" + se.IntKey)) { li.Selected = true; } ddl.Items.Add(li); } } if (ensD != null) { ensD.RetrieveAll(); if (ensD.Count == 0) { ensD.RetrieveAll(); } // throw new Exception(attrD1.UIBindKey); foreach (Entity en in ensD) { li = new ListItem(en.GetValStrByKey("Name"), en.GetValStrByKey("No")); if (paras.Contains("@V1=" + li.Value)) { li.Selected = true; } ddl.Items.Add(li); } } this.Left.AddTD(ddl); this.Left.AddTREnd(); this.Left.AddTR(); this.Left.AddTD("对比项目"); ddl = new DDL(); ddl.ID = "DDL_V2"; ddl.AutoPostBack = true; ddl.SelectedIndexChanged += new EventHandler(ddl_SelectedIndexChanged); if (sesD != null) { foreach (SysEnum se in sesD) { li = new ListItem(se.Lab, se.IntKey.ToString()); if (paras.Contains("@V2=" + se.IntKey)) { li.Selected = true; } ddl.Items.Add(li); } } if (ensD != null) { foreach (Entity en in ensD) { li = new ListItem(en.GetValStrByKey("Name"), en.GetValStrByKey("No")); if (paras.Contains("@V2=" + li.Value)) { li.Selected = true; } ddl.Items.Add(li); } } this.Left.AddTD(ddl); this.Left.AddTREnd(); #endregion 横纬度 ddl = new DDL(); ddl.ID = "DDL_Group"; ddl.AutoPostBack = true; ddl.SelectedIndexChanged += new EventHandler(ddl_SelectedIndexChanged); this.Left.AddTR(); this.Left.AddTD("分组项目"); foreach (Attr attr in attrs) { if (attr.UIContralType == UIContralType.DDL) { li = new ListItem(attr.Desc, attr.Key); if (paras.Contains("@Group=" + attr.Key)) { li.Selected = true; } ddl.Items.Add(li); } } this.Left.AddTD(ddl); this.Left.AddTREnd(); this.Left.AddTableEnd(); }
public DataTable BindDG() { #region 校验选择项目。 if (this.DDL_V1.SelectedIndex == this.DDL_V2.SelectedIndex) { if (this.DDL_V1.SelectedIndex == 0) { this.DDL_V2.SelectedIndex = 1; } else { this.DDL_V2.SelectedIndex = 0; } } if (this.DDL_Group.SelectedIndex == this.DDL_D.SelectedIndex) { if (this.DDL_Group.SelectedIndex == 0) { this.DDL_D.SelectedIndex = 1; } else { this.DDL_D.SelectedIndex = 0; } } this.SaveState(); #endregion 校验选择项目。 Attrs attrs = this.HisMD.AttrsInTableEns; Attr d1 = attrs.GetAttrByKey(this.DDL_D.SelectedItemStringVal); Attr dNum = attrs.GetAttrByKey(this.DDL_Num.SelectedItemStringVal); string v1 = this.DDL_V1.SelectedItemStringVal; string v2 = this.DDL_V2.SelectedItemStringVal; string groupAttr = this.DDL_Group.SelectedItemStringVal; string fxField = this.DDL_FXWay.SelectedItemStringVal + "(" + dNum.Key + ")"; string sql1 = "SELECT " + groupAttr + "," + d1.Key + "," + fxField + " FROM " + this.HisMD.No + " WHERE " + d1.Field + "='" + this.DDL_V1.SelectedItemStringVal + "' GROUP BY " + groupAttr + "," + d1.Key; string sql2 = "SELECT " + groupAttr + "," + d1.Key + "," + fxField + " FROM " + this.HisMD.No + " WHERE " + d1.Field + "='" + this.DDL_V2.SelectedItemStringVal + "' GROUP BY " + groupAttr + "," + d1.Key; // throw new Exception(sql1); DataTable dt1 = DBAccess.RunSQLReturnTable(sql1); DataTable dt2 = DBAccess.RunSQLReturnTable(sql2); this.Bind(dt1, dt2, "sss"); return(null); SysEnums sesD1 = null; Entities ensD1 = null; SysEnums sesD2 = null; Entities ensD2 = null; Map map = this.HisMD.HisEn.EnMap; #region 生成两个纬度报表 Attr attrD1 = attrs.GetAttrByKey(this.DDL_V1.SelectedItemStringVal); if (attrD1.IsEnum) { sesD1 = new SysEnums(attrD1.UIBindKey); } else { ensD1 = attrD1.HisFKEns; } Attr attrD2 = attrs.GetAttrByKey(this.DDL_V2.SelectedItemStringVal); if (attrD2.IsEnum) { sesD2 = new SysEnums(attrD2.UIBindKey); } else { ensD2 = attrD2.HisFKEns; } #endregion #region 生成执行的原始sql string Condition = ""; //处理特殊字段的条件问题。 Paras myps = new BP.DA.Paras(); string sql = "SELECT " + attrD1.Key + "," + attrD2.Key + ", " + this.DDL_FXWay.SelectedItemStringVal + "(" + this.DDL_Num.SelectedItemStringVal + ") FROM " + map.PhysicsTable; // 找到 WHERE 数据。 string where = " WHERE "; string whereOfLJ = " WHERE "; // 累计的where. string url = ""; foreach (Control item in this.ToolBar1.Controls) { if (item.ID == null) { continue; } if (item.ID.IndexOf("DDL_") == -1) { continue; } if (item.ID.IndexOf("DDL_Form_") == 0 || item.ID.IndexOf("DDL_To_") == 0) { continue; } string key = item.ID.Substring("DDL_".Length); DDL ddl = (DDL)item; if (ddl.SelectedItemStringVal == "all") { continue; } string val = ddl.SelectedItemStringVal; if (val == null) { continue; } if (val == "mvals") { UserRegedit sUr = new UserRegedit(); sUr.MyPK = WebUser.No + this.EnsName + "_SearchAttrs"; sUr.RetrieveFromDBSources(); /* 如果是多选值 */ string cfgVal = sUr.MVals; AtPara ap = new AtPara(cfgVal); string instr = ap.GetValStrByKey(key); if (instr == null || instr == "") { if (key == "FK_Dept" || key == "FK_Unit") { if (key == "FK_Dept") { val = WebUser.FK_Dept; ddl.SelectedIndex = 0; } if (key == "FK_Unit") { // val = WebUser.FK_Unit; ddl.SelectedIndex = 0; } } else { continue; } } else { instr = instr.Replace("..", "."); instr = instr.Replace(".", "','"); instr = instr.Substring(2); instr = instr.Substring(0, instr.Length - 2); where += " " + key + " IN (" + instr + ") AND "; continue; } } if (key == "FK_Dept") { if (val.Length == 8) { where += " FK_Dept =" + SystemConfig.AppCenterDBVarStr + "V_Dept AND "; } else { switch (SystemConfig.AppCenterDBType) { case DBType.Oracle: case DBType.Informix: where += " FK_Dept LIKE '%'||:V_Dept||'%' AND "; break; case DBType.MSSQL: default: where += " FK_Dept LIKE " + SystemConfig.AppCenterDBVarStr + "V_Dept+'%' AND "; break; } } myps.Add("V_Dept", val); } else { where += " " + key + " =" + SystemConfig.AppCenterDBVarStr + key + " AND "; if (key != "FK_NY") { whereOfLJ += " " + key + " =" + SystemConfig.AppCenterDBVarStr + key + " AND "; } myps.Add(key, val); } } #endregion #region 加上 where like 条件 try { string key = this.ToolBar1.GetTBByID("TB_Key").Text.Trim(); if (key.Length > 1) { string whereLike = ""; bool isAddAnd = false; foreach (Attr likeKey in attrs) { if (likeKey.IsNum) { continue; } if (likeKey.IsRefAttr) { continue; } switch (likeKey.Field) { case "MyFileExt": case "MyFilePath": case "WebPath": continue; default: break; } if (isAddAnd == false) { isAddAnd = true; whereLike += " " + likeKey.Field + " LIKE '%" + key + "%' "; } else { whereLike += " AND " + likeKey.Field + " LIKE '%" + key + "%'"; } } whereLike += " "; where += whereLike; } } catch { } #endregion #region 加上日期时间段. if (map.DTSearchWay != DTSearchWay.None) { string dtFrom = this.ToolBar1.GetTBByID("TB_S_From").Text.Trim(); string dtTo = this.ToolBar1.GetTBByID("TB_S_To").Text.Trim(); string field = map.DTSearchKey; if (map.DTSearchWay == DTSearchWay.ByDate) { where += "( " + field + ">='" + dtFrom + " 01:01' AND " + field + "<='" + dtTo + " 23:59') "; } else { where += "("; where += field + " >='" + dtFrom + "' AND " + field + "<='" + dtTo + "'"; where += ")"; } } if (where == " WHERE ") { where = "" + Condition.Replace("and", ""); whereOfLJ = "" + Condition.Replace("and", ""); } else { where = where.Substring(0, where.Length - " AND ".Length) + Condition; whereOfLJ = whereOfLJ.Substring(0, whereOfLJ.Length - " AND ".Length) + Condition; } #endregion sql += where + " GROUP BY " + attrD1.Key + "," + attrD2.Key; myps.SQL = sql; DataTable dt = DBAccess.RunSQLReturnTable(myps); return(null); }
public DataTable BindDG() { //处理数据源是否正确. if (this.DDL_D1.Items.Count <= 1 || this.DDL_Num.Items.Count == 0) { this.Right.Clear(); this.ToolBar1.Visible = false; this.Right.AddMsgGreen("提示:", "<h2>没有足够的纬度或者没有数据分析项目。</h2>"); return(null); } //不能让两个维度选择一致. if (this.DDL_D1.SelectedItemStringVal == this.DDL_D2.SelectedItemStringVal) { if (this.DDL_D1.SelectedIndex == 0) { this.DDL_D2.SelectedIndex = 1; } else { this.DDL_D2.SelectedIndex = 0; } } Attrs attrs = this.currEn.EnMap.Attrs; SysEnums sesD1 = null; Entities ensD1 = null; SysEnums sesD2 = null; Entities ensD2 = null; Map map = this.currEn.EnMap; #region 生成两个纬度报表 Attr attrD1 = attrs.GetAttrByKey(this.DDL_D1.SelectedItemStringVal); if (attrD1.IsEnum) { sesD1 = new SysEnums(attrD1.UIBindKey); } else { ensD1 = attrD1.HisFKEns; if (ensD1.Count == 0) { ensD1.RetrieveAll(); } } Attr attrD2 = attrs.GetAttrByKey(this.DDL_D2.SelectedItemStringVal); if (attrD2.IsEnum) { sesD2 = new SysEnums(attrD2.UIBindKey); } else { ensD2 = attrD2.HisFKEns; if (ensD2.Count == 0) { ensD2.RetrieveAll(); } } #endregion #region 生成执行的原始sql string Condition = ""; //处理特殊字段的条件问题。 Paras myps = new BP.DA.Paras(); string sql = "SELECT " + attrD1.Key + "," + attrD2.Key + ", " + this.DDL_FXWay.SelectedItemStringVal + "(" + this.DDL_Num.SelectedItemStringVal + ") FROM " + map.PhysicsTable; // 找到 WHERE 数据。 string where = " WHERE "; string whereOfLJ = " WHERE "; // 累计的where. string url = ""; foreach (Control item in this.ToolBar1.Controls) { if (item.ID == null) { continue; } if (item.ID.IndexOf("DDL_") == -1) { continue; } if (item.ID.IndexOf("DDL_Form_") == 0 || item.ID.IndexOf("DDL_To_") == 0) { continue; } string key = item.ID.Substring("DDL_".Length); DDL ddl = (DDL)item; if (ddl.SelectedItemStringVal == "all") { continue; } string val = ddl.SelectedItemStringVal; if (val == null) { continue; } if (val == "mvals") { UserRegedit sUr = new UserRegedit(); sUr.MyPK = WebUser.No + this.RptNo + "_SearchAttrs"; sUr.RetrieveFromDBSources(); /* 如果是多选值 */ string cfgVal = sUr.MVals; AtPara ap = new AtPara(cfgVal); string instr = ap.GetValStrByKey(key); if (instr == null || instr == "") { if (key == "FK_Dept" || key == "FK_Unit") { if (key == "FK_Dept") { val = WebUser.FK_Dept; ddl.SelectedIndex = 0; } if (key == "FK_Unit") { // val = WebUser.FK_Unit; ddl.SelectedIndex = 0; } } else { continue; } } else { instr = instr.Replace("..", "."); instr = instr.Replace(".", "','"); instr = instr.Substring(2); instr = instr.Substring(0, instr.Length - 2); where += " " + key + " IN (" + instr + ") AND "; continue; } } if (key == "FK_Dept") { if (val.Length == 8) { where += " FK_Dept =" + SystemConfig.AppCenterDBVarStr + "V_Dept AND "; } else { switch (SystemConfig.AppCenterDBType) { case DBType.Oracle: case DBType.Informix: where += " FK_Dept LIKE '%'||:V_Dept||'%' AND "; break; case DBType.MSSQL: default: where += " FK_Dept LIKE " + SystemConfig.AppCenterDBVarStr + "V_Dept+'%' AND "; break; } } myps.Add("V_Dept", val); } else { where += " " + key + " =" + SystemConfig.AppCenterDBVarStr + key + " AND "; if (key != "FK_NY") { whereOfLJ += " " + key + " =" + SystemConfig.AppCenterDBVarStr + key + " AND "; } myps.Add(key, val); } } #endregion #region 加上 where like 条件 try { string key = this.ToolBar1.GetTBByID("TB_Key").Text.Trim(); if (key.Length > 1) { string whereLike = ""; bool isAddAnd = false; foreach (Attr likeKey in attrs) { if (likeKey.IsNum) { continue; } if (likeKey.IsRefAttr) { continue; } switch (likeKey.Field) { case "MyFileExt": case "MyFilePath": case "WebPath": continue; default: break; } if (isAddAnd == false) { isAddAnd = true; whereLike += " " + likeKey.Field + " LIKE '%" + key + "%' "; } else { whereLike += " AND " + likeKey.Field + " LIKE '%" + key + "%'"; } } whereLike += " "; where += whereLike; } } catch { } #endregion #region 加上日期时间段. if (map.DTSearchWay != DTSearchWay.None) { string dtFrom = this.ToolBar1.GetTBByID("TB_S_From").Text.Trim(); string dtTo = this.ToolBar1.GetTBByID("TB_S_To").Text.Trim(); string field = map.DTSearchKey; if (map.DTSearchWay == DTSearchWay.ByDate) { where += "( " + field + ">='" + dtFrom + " 01:01' AND " + field + "<='" + dtTo + " 23:59') "; } else { where += "("; where += field + " >='" + dtFrom + "' AND " + field + "<='" + dtTo + "'"; where += ")"; } } if (where == " WHERE ") { where = "" + Condition.Replace("and", ""); whereOfLJ = "" + Condition.Replace("and", ""); } else { where = where.Substring(0, where.Length - " AND ".Length) + Condition; whereOfLJ = whereOfLJ.Substring(0, whereOfLJ.Length - " AND ".Length) + Condition; } #endregion sql += where + " GROUP BY " + attrD1.Key + "," + attrD2.Key; myps.SQL = sql; DataTable dt = DBAccess.RunSQLReturnTable(myps); string leftMsg = this.DDL_FXWay.SelectedItem.Text + ":" + this.DDL_Num.SelectedItem.Text; #region 生成表格 - 生成标题 this.Right.AddTable("class='Table' cellspacing='0' cellpadding='0' border='0' style='width:100%'"); this.Right.AddTR(); this.Right.AddTDGroupTitle(leftMsg); if (sesD1 != null) { foreach (SysEnum se in sesD1) { this.Right.AddTDGroupTitle(se.Lab); } } if (ensD1 != null) { if (ensD1.Count == 0) { ensD1.RetrieveAll(); } foreach (Entity en in ensD1) { this.Right.AddTDGroupTitle(en.GetValStrByKey("Name")); } } this.Right.AddTREnd(); #endregion .生成标题. #region 生成单元格 if (sesD2 != null) { foreach (SysEnum se in sesD2) { this.Right.AddTR(); this.Right.AddTDGroupTitle(se.Lab); if (sesD1 != null) { foreach (SysEnum seD1 in sesD1) { this.Right.AddTD("onclick='' ", this.GetIt(dt, seD1.IntKey.ToString(), se.IntKey.ToString())); } } if (ensD1 != null) { foreach (Entity enD1 in ensD1) { this.Right.AddTD(this.GetIt(dt, enD1.GetValStrByKey("No"), se.IntKey.ToString())); } } this.Right.AddTREnd(); } } if (ensD2 != null) { foreach (Entity en in ensD2) { this.Right.AddTR(); this.Right.AddTDGroupTitle(en.GetValStrByKey("Name")); if (sesD1 != null) { foreach (SysEnum seD1 in sesD1) { this.Right.AddTD(this.GetIt(dt, seD1.IntKey.ToString(), en.GetValStrByKey("No"))); } } if (ensD1 != null) { foreach (Entity enD1 in ensD1) { this.Right.AddTD(this.GetIt(dt, enD1.GetValStrByKey("No"), en.GetValStrByKey("No"))); } } this.Right.AddTREnd(); } } this.Right.AddTableEnd(); #endregion .生成单元格. #region 保存状态 string paras = ""; if (this.CB_IsImg.Checked) { paras = "@IsImg=1"; } else { paras = "@IsImg=0"; } if (this.CB_IsRate.Checked) { paras += "@IsRate=1"; } else { paras += "@IsRate=0"; } if (this.CB_IsNull.Checked) { paras += "@IsNull=1"; } else { paras += "@IsNull=0"; } paras += "@Num=" + this.DDL_Num.SelectedItemStringVal; paras += "@FXWay=" + this.DDL_FXWay.SelectedItemStringVal; paras += "@D1=" + this.DDL_D1.SelectedItemStringVal; paras += "@D1_Order=" + this.DDL_D1_Order.SelectedItemStringVal; paras += "@D2=" + this.DDL_D2.SelectedItemStringVal; paras += "@D2_Order=" + this.DDL_D2_Order.SelectedItemStringVal; paras += "@W=" + this.Left.GetTextBoxByID("TB_W").Text; paras += "@H=" + this.Left.GetTextBoxByID("TB_H").Text; ur.CfgKey = this.RptNo + "_D3"; ur.MyPK = WebUser.No + "_" + ur.CfgKey; ur.FK_Emp = WebUser.NoOfSessionID; ur.Paras = paras; ur.Save(); #endregion return(null); }