protected void Page_Load(object sender, EventArgs e) { this.Title = "您好:" + WebUser.No + ";选择范围"; UserRegedit ur = new UserRegedit(); ur.MyPK = this.MyPK; if (ur.RetrieveFromDBSources() == 0) { ur.MyPK = this.MyPK; ur.FK_Emp = WebUser.No; ur.CfgKey = this.EnsName + "_SearchAttrs"; ur.Insert(); } Entity en = BP.DA.ClassFactory.GetEns(this.EnsName).GetNewEntity; Attr attr = en.EnMap.GetAttrByKey(this.AttrKey); string cfgVal = ur.MVals; if (string.IsNullOrEmpty(cfgVal)) { cfgVal = ""; } AtPara ap = new AtPara(cfgVal); cfgVal = ap.GetValStrByKey(this.AttrKey); if (string.IsNullOrEmpty(cfgVal)) { cfgVal = ""; } if (attr.IsEnum) { this.BindEnum(ur, attr, cfgVal); } else { this.BindEns(ur, attr, cfgVal); } }
public void SaveSearchState(string ensName, string key) { if (string.IsNullOrEmpty(ensName)) { throw new Exception("@EnsName 为空" + ensName); } UserRegedit ur = new UserRegedit(); ur.MyPK = WebUser.No + ensName + "_SearchAttrs"; ur.RetrieveFromDBSources(); ur.FK_Emp = WebUser.No; ur.CfgKey = "SearchAttrs"; ur.SearchKey = key; if (key == "" || key == null) { try { ur.SearchKey = this.GetTBByID("TB_Key").Text; } catch { } } //查询时间. try { ur.DTFrom_Data = this.GetTBByID("TB_S_From").Text; ur.DTTo_Data = this.GetTBByID("TB_S_To").Text; ur.DTFrom_Datatime = this.GetTBByID("TB_S_From").Text; ur.DTTo_Datatime = this.GetTBByID("TB_S_To").Text; } catch { } string str = ""; foreach (Control ti in this.Controls) { if (ti.ID == null) { continue; } if (ti.ID.IndexOf("DDL_") == -1) { continue; } DDL ddl = (DDL)ti; if (ddl.Items.Count == 0) { continue; } str += "@" + ti.ID + "=" + ddl.SelectedItemStringVal; } ur.FK_Emp = WebUser.No; ur.CfgKey = ensName + "_SearchAttrs"; ur.Vals = str; try { ur.SearchKey = this.GetTBByID("TB_Key").Text; } catch { } ur.Save(); }
public QueryObject InitQueryObjectByEns(Entities ens, bool IsShowSearchKey, DTSearchWay dw, string dtKey, Attrs attrs, AttrsOfSearch attrsOfSearch, AttrSearchs searchAttrs) { QueryObject qo = new QueryObject(ens); #region 关键字 string keyVal = ""; //Attrs attrs = en.EnMap.Attrs; if (IsShowSearchKey) { TB keyTB = this.GetTBByID("TB_Key"); if (keyTB != null) { keyVal = keyTB.Text.Trim(); } else { UserRegedit ur = new UserRegedit(); //ur.MyPK = WebUser.No + ens.GetNewEntity.ClassID + "_SearchAttrs"; QueryObject urObj = new QueryObject(ur); urObj.AddWhere("MyPK", WebUser.No + ens.GetNewEntity.ClassID + "_SearchAttrs"); urObj.DoQuery(); keyVal = ur.SearchKey; } this.Page.Session["SKey"] = keyVal; } if (keyVal.Length >= 1) { Attr attrPK = new Attr(); foreach (Attr attr in attrs) { if (attr.IsPK) { attrPK = attr; break; } } int i = 0; foreach (Attr attr in attrs) { switch (attr.MyFieldType) { case FieldType.Enum: case FieldType.FK: case FieldType.PKFK: continue; default: break; } if (attr.MyDataType != DataType.AppString) { continue; } if (attr.MyFieldType == FieldType.RefText) { continue; } if (attr.Key == "FK_Dept") { continue; } i++; if (i == 1) { /* 第一次进来。 */ qo.addLeftBracket(); switch (SystemConfig.AppCenterDBType) { case BP.DA.DBType.Oracle: case BP.DA.DBType.Informix: qo.AddWhere(attr.Key, " LIKE ", " '%'||" + SystemConfig.AppCenterDBVarStr + "SKey||'%'"); break; case BP.DA.DBType.MySQL: qo.AddWhere(attr.Key, " LIKE ", "CONCAT('%'," + SystemConfig.AppCenterDBVarStr + "SKey,'%')"); break; default: qo.AddWhere(attr.Key, " LIKE ", " '%'+" + SystemConfig.AppCenterDBVarStr + "SKey+'%'"); break; } continue; } qo.addOr(); switch (SystemConfig.AppCenterDBType) { case BP.DA.DBType.Oracle: case BP.DA.DBType.Informix: qo.AddWhere(attr.Key, " LIKE ", "'%'||" + SystemConfig.AppCenterDBVarStr + "SKey||'%'"); break; case BP.DA.DBType.MySQL: qo.AddWhere(attr.Key, " LIKE ", "CONCAT('%'," + SystemConfig.AppCenterDBVarStr + "SKey,'%')"); break; default: qo.AddWhere(attr.Key, " LIKE ", "'%'+" + SystemConfig.AppCenterDBVarStr + "SKey+'%'"); break; } } qo.MyParas.Add("SKey", keyVal); qo.addRightBracket(); } else { qo.addLeftBracket(); qo.AddWhere("abc", "all"); qo.addRightBracket(); } #endregion #region 普通属性 string opkey = ""; // 操作符号。 foreach (AttrOfSearch attr in attrsOfSearch) { if (attr.IsHidden) { qo.addAnd(); qo.addLeftBracket(); qo.AddWhere(attr.RefAttrKey, attr.DefaultSymbol, attr.DefaultValRun); qo.addRightBracket(); continue; } if (attr.SymbolEnable == true) { opkey = this.GetDDLByKey("DDL_" + attr.Key).SelectedItemStringVal; if (opkey == "all") { continue; } } else { opkey = attr.DefaultSymbol; } qo.addAnd(); qo.addLeftBracket(); if (attr.DefaultVal.Length >= 8) { string date = "2005-09-01"; try { /* 就可能是年月日。 */ string y = this.GetDDLByKey("DDL_" + attr.Key + "_Year").SelectedItemStringVal; string m = this.GetDDLByKey("DDL_" + attr.Key + "_Month").SelectedItemStringVal; string d = this.GetDDLByKey("DDL_" + attr.Key + "_Day").SelectedItemStringVal; date = y + "-" + m + "-" + d; if (opkey == "<=") { DateTime dt = DataType.ParseSysDate2DateTime(date).AddDays(1); date = dt.ToString(DataType.SysDataFormat); } } catch { } qo.AddWhere(attr.RefAttrKey, opkey, date); } else { qo.AddWhere(attr.RefAttrKey, opkey, this.GetTBByID("TB_" + attr.Key).Text); } qo.addRightBracket(); } #endregion #region 外键 foreach (AttrSearch attr1 in searchAttrs) { Attr attr = attr1.HisAttr; if (attr.MyFieldType == FieldType.RefText) { continue; } DDL ddl = this.GetDDLByKey("DDL_" + attr.Key); if (ddl.Items.Count == 0) { continue; } string selectVal = ddl.SelectedItemStringVal; if (selectVal == "all") { continue; } if (selectVal == "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(attr.Key); if (instr == null || instr == "") { if (attr.Key == "FK_Dept" || attr.Key == "FK_Unit") { if (attr.Key == "FK_Dept") { selectVal = WebUser.FK_Dept; ddl.SelectedIndex = 0; } //if (attr.Key == "FK_Unit") //{ // selectVal = 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); qo.addAnd(); qo.addLeftBracket(); qo.AddWhereIn(attr.Key, "(" + instr + ")"); qo.addRightBracket(); continue; } } qo.addAnd(); qo.addLeftBracket(); if (attr.UIBindKey == "BP.Port.Depts" || attr.UIBindKey == "BP.Port.Units") //判断特殊情况。 { qo.AddWhere(attr.Key, " LIKE ", selectVal + "%"); } else { qo.AddWhere(attr.Key, selectVal); } //qo.AddWhere(attr.Key,this.GetDDLByKey("DDL_"+attr.Key).SelectedItemStringVal ) ; qo.addRightBracket(); } #endregion . if (dw != DTSearchWay.None) { string dtFrom = this.GetTBByID("TB_S_From").Text.Trim(); string dtTo = this.GetTBByID("TB_S_To").Text.Trim(); if (dw == DTSearchWay.ByDate) { qo.addAnd(); qo.addLeftBracket(); qo.SQL = dtKey + " >= '" + dtFrom + " 01:01'"; qo.addAnd(); qo.SQL = dtKey + " <= '" + dtTo + " 23:59'"; qo.addRightBracket(); //qo.AddWhere(dtKey, ">=", dtFrom+" 01:01"); //qo.addAnd(); //qo.AddWhere(dtKey, "<=", dtTo + " 23:59"); //qo.addRightBracket(); } if (dw == DTSearchWay.ByDateTime) { qo.addAnd(); qo.addLeftBracket(); qo.SQL = dtKey + " >= '" + dtFrom + "'"; qo.addAnd(); qo.SQL = dtKey + " <= '" + dtTo + "'"; qo.addRightBracket(); //qo.addAnd(); //qo.addLeftBracket(); //qo.AddWhere(dtKey, ">=", dtFrom); //qo.addAnd(); //qo.AddWhere(dtKey, "<=", dtTo); //qo.addRightBracket(); } } // throw new Exception(qo.SQL); return(qo); }
void btn_Click(object sender, EventArgs e) { UserRegedit ur = new UserRegedit(); ur.MyPK = this.MyPK; ur.RetrieveFromDBSources(); ur.FK_Emp = WebUser.No; ur.CfgKey = this.EnsName + "_SearchAttrs"; Entity en = BP.En.ClassFactory.GetEns(this.EnsName).GetNewEntity; Attr attr = en.EnMap.GetAttrByKey(this.AttrKey); string cfgVal = ur.MVals; AtPara ap = new AtPara(cfgVal); string old_Val = ap.GetValStrByKey(this.AttrKey); string keys = "@" + this.AttrKey + "="; if (attr.IsEnum) { SysEnums ses = new SysEnums(attr.UIBindKey); foreach (SysEnum item in ses) { if (this.Pub1.GetCBByID("CB_" + item.IntKey).Checked == false) { continue; } keys += "." + item.IntKey + "."; } if (ur.MVals.Contains("@" + this.AttrKey)) { ur.MVals = ur.MVals.Replace("@" + this.AttrKey + "=" + old_Val, keys); } else { ur.MVals = ur.MVals + keys; } ur.DirectUpdate(); } else { Entities ens = BP.En.ClassFactory.GetEns(attr.UIBindKey); ens.RetrieveAll(); foreach (Entity item in ens) { if (this.Pub1.GetCBByID("CB_" + item.GetValStrByKey(attr.UIRefKeyValue)).Checked == false) { continue; } keys += "." + item.GetValStrByKey(attr.UIRefKeyValue) + "."; } if (ur.MVals.Contains("@" + this.AttrKey)) { ur.MVals = ur.MVals.Replace("@" + this.AttrKey + "=" + old_Val, keys); } else { ur.MVals = ur.MVals + keys; } ur.DirectUpdate(); } keys = keys.Replace("@" + this.AttrKey + "=", ""); this.WinClose(keys); }
void btn_Click(object sender, EventArgs e) { UserRegedit ur = new UserRegedit(); ur.MyPK = this.MyPK; ur.RetrieveFromDBSources(); ur.FK_Emp = WebUser.No; ur.CfgKey = this.EnsName + "_SearchAttrs"; Entity en = BP.DA.ClassFactory.GetEns(this.EnsName).GetNewEntity; Attr attr = en.EnMap.GetAttrByKey(this.AttrKey); string cfgVal = ur.MVals; AtPara ap = new AtPara(cfgVal); string old_Val = ap.GetValStrByKey(this.AttrKey); string keys = "@" + this.AttrKey + "="; if (attr.IsEnum) { SysEnums ses = new SysEnums(attr.UIBindKey); foreach (SysEnum item in ses) { if (this.Pub1.GetCBByID("CB_" + item.IntKey).Checked == false) { continue; } keys += "." + item.IntKey + "."; } if (ur.MVals.Contains("@" + this.AttrKey)) { ur.MVals = ur.MVals.Replace("@" + this.AttrKey + "=" + old_Val, keys); } else { ur.MVals = ur.MVals + keys; } ur.DirectUpdate(); } else { if (attr.UIBindKey == "BP.Port.Depts") { DataTable dt; if (WebUser.No == "admin") { dt = DBAccess.RunSQLReturnTable("SELECT No,Name FROM Port_Dept "); } else { dt = DBAccess.RunSQLReturnTable("SELECT No,Name FROM Port_Dept WHERE No IN (SELECT FK_Dept FROM WF_DeptFlowSearch WHERE FK_Emp='" + WebUser.No + "' AND FK_Flow='" + this.FK_Flow + "')"); } foreach (DataRow dr in dt.Rows) { if (this.Pub1.GetCBByID("CB_" + dr[0].ToString()).Checked == false) { continue; } keys += "." + dr[0].ToString() + "."; } } else { Entities ens = BP.DA.ClassFactory.GetEns(attr.UIBindKey); ens.RetrieveAll(); foreach (Entity item in ens) { if (this.Pub1.GetCBByID("CB_" + item.GetValStrByKey(attr.UIRefKeyValue)).Checked == false) { continue; } keys += "." + item.GetValStrByKey(attr.UIRefKeyValue) + "."; } } if (ur.MVals.Contains("@" + this.AttrKey)) { ur.MVals = ur.MVals.Replace("@" + this.AttrKey + "=" + old_Val, keys); } else { ur.MVals = ur.MVals + keys; } ur.DirectUpdate(); } keys = keys.Replace("@" + this.AttrKey + "=", ""); this.WinClose(keys); }
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); }
public void BindData() { string ensname = this.Request.QueryString["EnsName"]; if (ensname == null) { ensname = this.Request.QueryString["EnsName"]; } Entities ens = BP.En.ClassFactory.GetEns(ensname); Entity en = ens.GetNewEntity; QueryObject qo = new QueryObject(ens); string[] strs = this.Request.RawUrl.Split('&'); string[] strs1 = this.Request.RawUrl.Split('&'); foreach (string str in strs) { if (str.IndexOf("EnsName") != -1) { continue; } string[] mykey = str.Split('='); string key = mykey[0]; if (key == "OID" || key == "MyPK") { continue; } if (key == "FK_Dept") { this.FK_Dept = mykey[1]; continue; } if (en.EnMap.Attrs.Contains(key) == false) { continue; } if (mykey[1] == "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); string val = ""; if (instr == null || instr == "") { if (key == "FK_Dept" || key == "FK_Unit") { if (key == "FK_Dept") { val = WebUser.FK_Dept; } } else { continue; } } else { instr = instr.Replace("..", "."); instr = instr.Replace(".", "','"); instr = instr.Substring(2); instr = instr.Substring(0, instr.Length - 2); qo.AddWhereIn(mykey[0], instr); } } else { qo.AddWhere(mykey[0], mykey[1]); } qo.addAnd(); } if (this.FK_Dept != null && (this.Request.QueryString["FK_Emp"] == null || this.Request.QueryString["FK_Emp"] == "all")) { if (this.FK_Dept.Length == 2) { qo.AddWhere("FK_Dept", " = ", "all"); qo.addAnd(); } else { if (this.FK_Dept.Length == 8) { //if (this.Request.QueryString["ByLike"] != "1") qo.AddWhere("FK_Dept", " = ", this.FK_Dept); } else { qo.AddWhere("FK_Dept", " like ", this.FK_Dept + "%"); } qo.addAnd(); } } qo.AddHD(); if (this.DoType == "Exp") { /*如果是导出,就把它导出到excel.*/ this.ExportDGToExcel(qo.DoQueryToTable(), en.EnMap, en.EnDesc + "数据导出"); this.WinClose(); return; } int num = qo.DoQuery(); // Log.DebugWriteWarning(qo.SQL); // Log.DefaultLogWriteLineError(qo.SQL); this.ShowTitle = ens.GetNewEntity.EnMap.EnDesc + ",数据:" + num + " 条。"; this.UCSys1.DataPanelDtl(ens, null); }
public DataTable Search_Data() { DataSet ds = new DataSet(); #region 查询语句 MapData md = new MapData(this.FrmID); //取出来查询条件. BP.Sys.UserRegedit ur = new UserRegedit(); ur.MyPK = WebUser.No + "_" + this.FrmID + "_SearchAttrs"; ur.RetrieveFromDBSources(); GEEntitys rpts = new GEEntitys(this.FrmID); Attrs attrs = rpts.GetNewEntity.EnMap.Attrs; QueryObject qo = new QueryObject(rpts); #region 关键字字段. string keyWord = ur.SearchKey; if (md.GetParaBoolen("IsSearchKey") && DataType.IsNullOrEmpty(keyWord) == false && keyWord.Length >= 1) { Attr attrPK = new Attr(); foreach (Attr attr in attrs) { if (attr.IsPK) { attrPK = attr; break; } } int i = 0; string enumKey = ","; //求出枚举值外键. foreach (Attr attr in attrs) { switch (attr.MyFieldType) { case FieldType.Enum: enumKey = "," + attr.Key + "Text,"; break; case FieldType.FK: continue; default: break; } if (attr.MyDataType != DataType.AppString) { continue; } //排除枚举值关联refText. if (attr.MyFieldType == FieldType.RefText) { if (enumKey.Contains("," + attr.Key + ",") == true) { continue; } } if (attr.Key == "FK_Dept") { continue; } i++; if (i == 1) { /* 第一次进来。 */ qo.addLeftBracket(); if (SystemConfig.AppCenterDBVarStr == "@" || SystemConfig.AppCenterDBVarStr == "?") { qo.AddWhere(attr.Key, " LIKE ", SystemConfig.AppCenterDBType == DBType.MySQL ? (" CONCAT('%'," + SystemConfig.AppCenterDBVarStr + "SKey,'%')") : (" '%'+" + SystemConfig.AppCenterDBVarStr + "SKey+'%'")); } else { qo.AddWhere(attr.Key, " LIKE ", " '%'||" + SystemConfig.AppCenterDBVarStr + "SKey||'%'"); } continue; } qo.addOr(); if (SystemConfig.AppCenterDBVarStr == "@" || SystemConfig.AppCenterDBVarStr == "?") { qo.AddWhere(attr.Key, " LIKE ", SystemConfig.AppCenterDBType == DBType.MySQL ? ("CONCAT('%'," + SystemConfig.AppCenterDBVarStr + "SKey,'%')") : ("'%'+" + SystemConfig.AppCenterDBVarStr + "SKey+'%'")); } else { qo.AddWhere(attr.Key, " LIKE ", "'%'||" + SystemConfig.AppCenterDBVarStr + "SKey||'%'"); } } qo.MyParas.Add("SKey", keyWord); qo.addRightBracket(); } else { qo.AddHD(); } #endregion 关键字段查询 #region 时间段的查询 if (md.GetParaInt("DTSearchWay") != (int)DTSearchWay.None && DataType.IsNullOrEmpty(ur.DTFrom) == false) { string dtFrom = ur.DTFrom; // this.GetTBByID("TB_S_From").Text.Trim().Replace("/", "-"); string dtTo = ur.DTTo; // this.GetTBByID("TB_S_To").Text.Trim().Replace("/", "-"); //按日期查询 if (md.GetParaInt("DTSearchWay") == (int)DTSearchWay.ByDate) { qo.addAnd(); qo.addLeftBracket(); dtTo += " 23:59:59"; qo.SQL = md.GetParaString("DTSearchKey") + " >= '" + dtFrom + "'"; qo.addAnd(); qo.SQL = md.GetParaString("DTSearchKey") + " <= '" + dtTo + "'"; qo.addRightBracket(); } if (md.GetParaInt("DTSearchWay") == (int)DTSearchWay.ByDateTime) { //取前一天的24:00 if (dtFrom.Trim().Length == 10) //2017-09-30 { dtFrom += " 00:00:00"; } if (dtFrom.Trim().Length == 16) //2017-09-30 00:00 { dtFrom += ":00"; } dtFrom = DateTime.Parse(dtFrom).AddDays(-1).ToString("yyyy-MM-dd") + " 24:00"; if (dtTo.Trim().Length < 11 || dtTo.Trim().IndexOf(' ') == -1) { dtTo += " 24:00"; } qo.addAnd(); qo.addLeftBracket(); qo.SQL = md.GetParaString("DTSearchKey") + " >= '" + dtFrom + "'"; qo.addAnd(); qo.SQL = md.GetParaString("DTSearchKey") + " <= '" + dtTo + "'"; qo.addRightBracket(); } } #endregion 时间段的查询 #region 外键或者枚举的查询 //获得关键字. AtPara ap = new AtPara(ur.Vals); foreach (string str in ap.HisHT.Keys) { var val = ap.GetValStrByKey(str); if (val.Equals("all")) { continue; } qo.addAnd(); qo.addLeftBracket(); qo.AddWhere(str, ap.GetValStrByKey(str)); qo.addRightBracket(); } #endregion 外键或者枚举的查询 #endregion 查询语句 qo.addOrderBy("OID"); return(qo.DoQueryToTable()); }
public string Search_Init() { DataSet ds = new DataSet(); #region 查询显示的列 MapAttrs mapattrs = new MapAttrs(); mapattrs.Retrieve(MapAttrAttr.FK_MapData, this.FrmID, MapAttrAttr.Idx); DataRow row = null; DataTable dt = new DataTable("Attrs"); dt.Columns.Add("KeyOfEn", typeof(string)); dt.Columns.Add("Name", typeof(string)); dt.Columns.Add("Width", typeof(int)); dt.Columns.Add("UIContralType", typeof(int)); dt.Columns.Add("LGType", typeof(int)); //设置标题、单据号位于开始位置 foreach (MapAttr attr in mapattrs) { string searchVisable = attr.atPara.GetValStrByKey("SearchVisable"); if (searchVisable == "0") { continue; } if (attr.UIVisible == false) { continue; } row = dt.NewRow(); row["KeyOfEn"] = attr.KeyOfEn; row["Name"] = attr.Name; row["Width"] = attr.UIWidthInt; row["UIContralType"] = attr.UIContralType; row["LGType"] = attr.LGType; dt.Rows.Add(row); } ds.Tables.Add(dt); #endregion 查询显示的列 #region 查询语句 MapData md = new MapData(this.FrmID); //取出来查询条件. BP.Sys.UserRegedit ur = new UserRegedit(); ur.MyPK = WebUser.No + "_" + this.FrmID + "_SearchAttrs"; ur.RetrieveFromDBSources(); GEEntitys rpts = new GEEntitys(this.FrmID); Attrs attrs = rpts.GetNewEntity.EnMap.Attrs; QueryObject qo = new QueryObject(rpts); #region 关键字字段. string keyWord = ur.SearchKey; if (md.GetParaBoolen("IsSearchKey") && DataType.IsNullOrEmpty(keyWord) == false && keyWord.Length >= 1) { Attr attrPK = new Attr(); foreach (Attr attr in attrs) { if (attr.IsPK) { attrPK = attr; break; } } int i = 0; string enumKey = ","; //求出枚举值外键. foreach (Attr attr in attrs) { switch (attr.MyFieldType) { case FieldType.Enum: enumKey = "," + attr.Key + "Text,"; break; case FieldType.FK: continue; default: break; } if (attr.MyDataType != DataType.AppString) { continue; } //排除枚举值关联refText. if (attr.MyFieldType == FieldType.RefText) { if (enumKey.Contains("," + attr.Key + ",") == true) { continue; } } if (attr.Key == "FK_Dept") { continue; } i++; if (i == 1) { /* 第一次进来。 */ qo.addLeftBracket(); if (SystemConfig.AppCenterDBVarStr == "@" || SystemConfig.AppCenterDBVarStr == "?") { qo.AddWhere(attr.Key, " LIKE ", SystemConfig.AppCenterDBType == DBType.MySQL ? (" CONCAT('%'," + SystemConfig.AppCenterDBVarStr + "SKey,'%')") : (" '%'+" + SystemConfig.AppCenterDBVarStr + "SKey+'%'")); } else { qo.AddWhere(attr.Key, " LIKE ", " '%'||" + SystemConfig.AppCenterDBVarStr + "SKey||'%'"); } continue; } qo.addOr(); if (SystemConfig.AppCenterDBVarStr == "@" || SystemConfig.AppCenterDBVarStr == "?") { qo.AddWhere(attr.Key, " LIKE ", SystemConfig.AppCenterDBType == DBType.MySQL ? ("CONCAT('%'," + SystemConfig.AppCenterDBVarStr + "SKey,'%')") : ("'%'+" + SystemConfig.AppCenterDBVarStr + "SKey+'%'")); } else { qo.AddWhere(attr.Key, " LIKE ", "'%'||" + SystemConfig.AppCenterDBVarStr + "SKey||'%'"); } } qo.MyParas.Add("SKey", keyWord); qo.addRightBracket(); } else { qo.AddHD(); } #endregion 关键字段查询 #region 时间段的查询 if (md.GetParaInt("DTSearchWay") != (int)DTSearchWay.None && DataType.IsNullOrEmpty(ur.DTFrom) == false) { string dtFrom = ur.DTFrom; // this.GetTBByID("TB_S_From").Text.Trim().Replace("/", "-"); string dtTo = ur.DTTo; // this.GetTBByID("TB_S_To").Text.Trim().Replace("/", "-"); //按日期查询 if (md.GetParaInt("DTSearchWay") == (int)DTSearchWay.ByDate) { qo.addAnd(); qo.addLeftBracket(); dtTo += " 23:59:59"; qo.SQL = md.GetParaString("DTSearchKey") + " >= '" + dtFrom + "'"; qo.addAnd(); qo.SQL = md.GetParaString("DTSearchKey") + " <= '" + dtTo + "'"; qo.addRightBracket(); } if (md.GetParaInt("DTSearchWay") == (int)DTSearchWay.ByDateTime) { //取前一天的24:00 if (dtFrom.Trim().Length == 10) //2017-09-30 { dtFrom += " 00:00:00"; } if (dtFrom.Trim().Length == 16) //2017-09-30 00:00 { dtFrom += ":00"; } dtFrom = DateTime.Parse(dtFrom).AddDays(-1).ToString("yyyy-MM-dd") + " 24:00"; if (dtTo.Trim().Length < 11 || dtTo.Trim().IndexOf(' ') == -1) { dtTo += " 24:00"; } qo.addAnd(); qo.addLeftBracket(); qo.SQL = md.GetParaString("DTSearchKey") + " >= '" + dtFrom + "'"; qo.addAnd(); qo.SQL = md.GetParaString("DTSearchKey") + " <= '" + dtTo + "'"; qo.addRightBracket(); } } #endregion 时间段的查询 #region 外键或者枚举的查询 //获得关键字. AtPara ap = new AtPara(ur.Vals); foreach (string str in ap.HisHT.Keys) { var val = ap.GetValStrByKey(str); if (val.Equals("all")) { continue; } qo.addAnd(); qo.addLeftBracket(); qo.AddWhere(str, ap.GetValStrByKey(str)); qo.addRightBracket(); } #endregion 外键或者枚举的查询 #endregion 查询语句 //获得行数. ur.SetPara("RecCount", qo.GetCount()); ur.Save(); qo.DoQuery("OID", this.PageSize, this.PageIdx); DataTable mydt = rpts.ToDataTableField(); mydt.TableName = "DT"; ds.Tables.Add(mydt); //把数据加入里面. return(BP.Tools.Json.ToJson(ds)); }