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