public void SaveState()
{
#region 保存状态
string paras = "";
paras += "@Num=" + this.DDL_Num.SelectedItemStringVal;
paras += "@FXWay=" + this.DDL_FXWay.SelectedItemStringVal;
try
{
paras += "@V1=" + this.DDL_V1.SelectedItemStringVal;
paras += "@V2=" + this.DDL_V2.SelectedItemStringVal;
}
catch
{
}
paras += "@D=" + this.DDL_D.SelectedItemStringVal;
paras += "@Order=" + this.DDL_Order.SelectedItemStringVal;
paras += "@Group=" + this.DDL_Group.SelectedItemStringVal;
ur.CfgKey = this.EnsName + "_Con";
ur.MyPK = WebUser.No + "_" + ur.CfgKey;
ur.FK_Emp = WebUser.NoOfSessionID;
ur.Paras = paras;
ur.Save();
#endregion
}
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 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 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));
}