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));
}