public void GetDataReport(ReportSeachWhereOR whereOR , out DataTable reReport, out DataTable reList) { SearchReportDataToTemp(whereOR); string SqlReport = @"select val.*,tc.ChannelName from ( select deviceno,ChannelNo,monitordate, round( avg(MonitorValue),2) avgValue, max(MonitorValue) maxValue,min(MonitorValue) minValue from ReportTemp group by deviceno,ChannelNo,monitordate ) as val left join t_Channel tc on tc.DeviceID= val.deviceno and tc.ChannelNo= val.ChannelNo order by monitordate;"; DataTable dtReport = db.ExecuteQuery(SqlReport); reReport = dtReport; string sqlList = @"select val.*,tc.ChannelName from ( select deviceno,ChannelNo, round( avg(MonitorValue),2) avgValue, max(MonitorValue) maxValue,min(MonitorValue) minValue from ReportTemp group by deviceno,ChannelNo ) as val left join t_Channel tc on tc.DeviceID= val.deviceno and tc.ChannelNo= val.ChannelNo; "; DataTable dtList = db.ExecuteQuery(sqlList); reList = dtList; }
public void SearchReportDataToTemp(ReportSeachWhereOR whereOR) { DateTime begin = whereOR.StartTime; DateTime end = whereOR.EndTime; int stationID = whereOR.StationID; string devName = whereOR.DeviceName; int nDeviceID = whereOR.DeviceID; string Datastr = whereOR.GetDataConver(); string ChanncelWhere = whereOR.GetChanncelWhere("t1.channelno"); string sqlTruncate = " truncate table ReportTemp;"; db.ExecuteNoQuery(sqlTruncate); // 以1970年为限,(年份-1970)×12+月份为数值,一直循环到结束时间 int t1 = (begin.Year - 1970) * 12 + begin.Month - 1; int t2 = (end.Year - 1970) * 12 + end.Month - 1; int t = 0; for (t = t1; t <= t2; t++) { int year = t / 12 + 1970; int month = t % 12 + 1; // 生成表名 string strmonth = string.Format("{0:00}", month);// 00001234 string targetTable = "t_" + stationID.ToString().Trim() + "_" + devName + "_" + Convert.ToString(year) + "_" + strmonth; //string tableName2 = targetTable; string tableNametemp = "\"" + targetTable + "\""; string tableName2 = ""; foreach (char c in tableNametemp) { if (c == '(' || c == ')') { tableName2 += new string('/', 1); } tableName2 += new string(c, 1); } string strSQL = "SELECT count(*) FROM dbo.sysobjects WHERE id = OBJECT_ID(N'" + tableName2 + "') AND OBJECTPROPERTY(id, N'IsUserTable') = 1"; string s = db.ExecuteScalar(strSQL).ToString(); if (s == "" || Convert.ToInt32(s) <= 0) continue; // 根据表名和时间返回数据 string time1 = begin.ToString("yyyy-MM-dd HH:mm:ss"); string time2 = end.ToString("yyyy-MM-dd HH:mm:ss"); string strdev = nDeviceID.ToString(); string strSql = string.Format(@"insert into ReportTemp ([deviceno],[channelno],[monitorvalue],[monitordate],MonitorTime) select t1.deviceid as deviceno, t1.channelno as channelno, convert(float,t1.monitorvalue) as monitorvalue,{0} as monitordate,MonitorTime from {1} t1 where t1.MonitorTime between '{2}' and '{3}' and ({4})", Datastr, tableName2, time1, time2, ChanncelWhere); //ChanncelWhere db.ExecuteNoQuery(strSql); }// end for }
public void SearchData(ReportSeachWhereOR whereOR) { //ReportSeachDA DataTable dtReport=new DataTable(); DataTable dtList=new DataTable(); new ReportSeachDA().GetDataReport(whereOR, out dtReport, out dtList); gvList.DataSource = dtList; gvList.DataBind(); //编写数据 BindSeries(dtReport, whereOR); }
public void Init(ReportSeachWhereOR whereOR) { lblName.Text = whereOR.ReportName; lblTime.Text = DateTime.Now.ToString("yyyy-MM-dd"); lblType.Text = whereOR.ReportTypeName;//.Replace("历史(", "").Replace(")", ""); lblProp.Text = whereOR.ListChanncel[0].ChanncelName; chtReport.Titles[0].Text = string.Format("{0}:{1}", whereOR.ReportName, whereOR.ListChanncel[0].ChanncelName); }
protected void btnSearch_Click(object sender, EventArgs e) { if (dpdDeviceid.SelectedItem == null) { base.AlertNormal("请选择设备!"); return; } ReportSeachWhereOR whereOR = new ReportSeachWhereOR(); whereOR.StationID = Convert.ToInt32(dpdStationID.SelectedValue); whereOR.DeviceType = Convert.ToInt32(dpdDeviceType.SelectedValue); whereOR.DeviceID = Convert.ToInt32(dpdDeviceid.SelectedValue); whereOR.DeviceName = dpdDeviceid.SelectedItem.Text; whereOR.StartTime = Convert.ToDateTime(txtStartTime.Text); whereOR.EndTime = Convert.ToDateTime(txtEndTime.Text); whereOR.ReportType = dpdDtaill.SelectedValue; whereOR.ReportTypeName = dpdDtaill.SelectedItem.Text; whereOR.ReportName = GetReportType(Request.QueryString["type"]); List<SearchChanncelOR> listChanncels = new List<SearchChanncelOR>(); foreach (ListItem li in listSelectChannelNo.Items) { listChanncels.Add(new SearchChanncelOR() { ChanncelNo = Convert.ToInt32(li.Value), ChanncelName = li.Text }); } if (listChanncels.Count == 0) { base.AlertNormal("请选择通道!"); return; } whereOR.ListChanncel = listChanncels; Session["SearchWhere"] = whereOR; Response.Redirect("DBSearchDetail.aspx"); }
public void BindSeries(DataTable dtReport, ReportSeachWhereOR whereOR) { if (dtReport == null) return; if (dtReport.Rows.Count == 0) return; foreach (SearchChanncelOR obj in whereOR.ListChanncel) { dtReport.DefaultView.RowFilter = string.Format(" ChannelNo={0}", obj.ChanncelNo); DataTable dt = dtReport.DefaultView.ToTable(); Series ser = new Series(); ser.Points.DataBindXY(dt.Rows, "monitordate", dt.Rows, "avgValue"); ser.ChartType = SeriesChartType.Line; //ser.IsValueShownAsLabel = true; ser.MarkerStyle = MarkerStyle.Circle; ser.MarkerSize = 3; ser.LegendText = obj.ChanncelName; chtReport.Series.Add(ser); } }
public void SecondMiddlewareInit(int DeviceID, DateTime begin, DateTime end) { ReportSeachWhereOR whereOR = new ReportSeachWhereOR(); whereOR.StartTime = begin; whereOR.EndTime = end; DeviceOR devObj = new DeviceDA().SelectDeviceORByID(DeviceID.ToString()); if (devObj == null) { throw new Exception("设备不存!"); } List<int> ListDevs = new List<int>(); ListDevs.Add(devObj.DeviceID); //获取中间件 whereOR.ListDevices = GetBussMiddleware(DeviceID); whereOR.StationID = devObj.StationID; whereOR.ReportType = "day"; /* * web 活动会话数 21102 * 数据库连接池 活动连接 22603 * 当前 JVM堆大小(kb) 22505 * */ whereOR.ListChanncel = new List<SearchChanncelOR>() { new SearchChanncelOR(){ ChanncelNo= 21102}//--活动会话数 ,new SearchChanncelOR(){ ChanncelNo= 22603}//--活动连接 ,new SearchChanncelOR(){ ChanncelNo= 22505} //JVM堆大小 }; PDFReportSearch DataDA = new PDFReportSearch(); DataDA.SearchReportDataToTemp(whereOR); }
/// <summary> /// /// </summary> /// <returns></returns> public void SecondDBInit(int DeviceID, DateTime begin, DateTime end) { ReportSeachWhereOR whereOR = new ReportSeachWhereOR(); whereOR.StartTime = begin; whereOR.EndTime = end; DeviceOR devObj = new DeviceDA().SelectDeviceORByID(DeviceID.ToString()); if (devObj == null) { throw new Exception("设备不存!"); } List<int> ListDevs = new List<int>(); ListDevs.Add(devObj.DeviceID); //获取数据库设备 whereOR.ListDevices = GetBussDataBase(DeviceID); // whereOR.DeviceName = devObj.DeviceName; // whereOR.DeviceType = devObj.DeviceTypeID; whereOR.StationID = devObj.StationID; whereOR.ReportType = "day"; whereOR.ListChanncel = new List<SearchChanncelOR>() { new SearchChanncelOR(){ ChanncelNo= 42109}//--连接数 ,new SearchChanncelOR(){ ChanncelNo= 42105}//--%可用 ,new SearchChanncelOR(){ ChanncelNo= 41601} //缓冲器 ,new SearchChanncelOR(){ ChanncelNo= 41602} //数据字典 ,new SearchChanncelOR(){ ChanncelNo= 41603} //库 ,new SearchChanncelOR(){ ChanncelNo= 41101} //连接时间 }; PDFReportSearch DataDA = new PDFReportSearch(); DataDA.SearchReportDataToTemp(whereOR); }
/// <summary> /// /// </summary> /// <returns></returns> public void InitHostInfo(int DeviceID, DateTime begin, DateTime end) { ReportSeachWhereOR whereOR = new ReportSeachWhereOR(); whereOR.StartTime = begin; whereOR.EndTime = end; DeviceOR devObj = new DeviceDA().SelectDeviceORByID(DeviceID.ToString()); if (devObj == null) { throw new Exception("设备不存!"); } List<int> ListDevs = new List<int>(); ListDevs.Add(devObj.DeviceID); //获取数据库设备 whereOR.ListDevices = GetBussDataHost(DeviceID); whereOR.StationID = devObj.StationID; whereOR.ReportType = "day"; whereOR.ListChanncel = new List<SearchChanncelOR>() { new SearchChanncelOR(){ ChanncelNo= 25201}//--主机CPU利用率 ,new SearchChanncelOR(){ ChanncelNo= 25202}//--内存压力 ,new SearchChanncelOR(){ ChanncelNo= 25203} //磁盘 }; PDFReportSearch DataDA = new PDFReportSearch(); DataDA.SearchReportDataToTemp(whereOR); }