public void AutoInsert() { DateTime dt = DateTime.Now.AddDays(-1); String strRemove = dt.AddDays(1).ToString("yyyy-MM-dd") + "-07"; int validateRowCount = 0; #region Insert Data to DB Table //LCA_DT_PERIOD (LCA_LINE,INFO_DATE,LINE_STATUS,STATUS_TIMESPAN,DATE_TYPE) #region Caculate the validate row count for (int i = 0; (i < DGVlineHourly.RowCount - 1 && DGVlineHourly[0, i].Value.ToString() != strRemove); i++) { validateRowCount += 1; } #endregion #region Get Date Type int intTemp; if (DGVlineHourly.RowCount <= 0) { MessageBox.Show("Please Load Hourly Data First!"); return; } List<string> dayShift = new List<string>(validateRowCount); for (int i = 0; (i < DGVlineHourly.RowCount - 1 && DGVlineHourly[0, i].Value.ToString() != strRemove); i++) { intTemp = Convert.ToInt32(DGVlineHourly[0, i].Value.ToString().Remove(0, DGVlineHourly[0, i].Value.ToString().Length - 2)); if (intTemp >= 7 && intTemp <= 18) { dayShift.Add("Day"); } else { dayShift.Add("Night"); } } #endregion #region Get Info_Date List List<string> info_Date = new List<string>(validateRowCount); for (int i = 0; (i < DGVlineHourly.RowCount - 1 && DGVlineHourly[0, i].Value.ToString() != strRemove); i++) { info_Date.Add(DGVlineHourly[0, i].Value.ToString()); } #endregion #region Get LINE_STATUS List List<string> line_status_run = new List<string>(validateRowCount); List<string> line_status_stop = new List<string>(validateRowCount); List<string> line_status_pause = new List<string>(validateRowCount); for (int i = 0; (i < DGVlineHourly.RowCount - 1 && DGVlineHourly[0, i].Value.ToString() != strRemove); i++) { line_status_run.Add("Run"); line_status_stop.Add("Stop"); line_status_pause.Add("Pause"); } #endregion #region Get STATUS_TIMESPAN List List<double> line_status_runtime = new List<double>(validateRowCount); List<double> line_status_stoptime = new List<double>(validateRowCount); List<double> line_status_pausetime = new List<double>(validateRowCount); for (int i = 0; (i < DGVlineHourly.RowCount - 1 && DGVlineHourly[0, i].Value.ToString() != strRemove); i++) { line_status_runtime.Add(Convert.ToDouble(DGVlineHourly[1, i].Value.ToString())); line_status_stoptime.Add(Convert.ToDouble(DGVlineHourly[2, i].Value.ToString())); line_status_pausetime.Add(Convert.ToDouble(DGVlineHourly[3, i].Value.ToString())); } #endregion #region Create SQL & Insert DB OleDbUtility oleUtility = new OleDbUtility("ods", "users", "users"); oleUtility.getConnection(); oleUtility.OpenConnection(); String strSQLPause, strSQLStop, strSQLRun; int blExcute; String str_info_date; for (int i = 0; (i < DGVlineHourly.RowCount - 1 && DGVlineHourly[0, i].Value.ToString() != strRemove); i++) { str_info_date = info_Date[i].Substring(0, 10) + " " + info_Date[i].Substring(11, 2) + ":00:00"; strSQLRun = "insert into LCA_DT_PERIOD (LCA_LINE,INFO_DATE,LINE_STATUS,STATUS_TIMESPAN,DATE_TYPE) values ('F30'," + "to_date('" + str_info_date + "','yyyy-mm-dd hh24:mi:ss')" + ",'" + line_status_run[i] + "','" + line_status_runtime[i].ToString() + "','" + dayShift[i] + "')"; strSQLStop = "insert into LCA_DT_PERIOD (LCA_LINE,INFO_DATE,LINE_STATUS,STATUS_TIMESPAN,DATE_TYPE) values ('F30'," + "to_date('" + str_info_date + "','yyyy-mm-dd hh24:mi:ss')" + ",'" + line_status_stop[i] + "','" + line_status_stoptime[i].ToString() + "','" + dayShift[i] + "')"; strSQLPause = "insert into LCA_DT_PERIOD (LCA_LINE,INFO_DATE,LINE_STATUS,STATUS_TIMESPAN,DATE_TYPE) values ('F30'," + "to_date('" + str_info_date + "','yyyy-mm-dd hh24:mi:ss')" + ",'" + line_status_pause[i] + "','" + line_status_pausetime[i].ToString() + "','" + dayShift[i] + "')"; try { blExcute = oleUtility.SQLExecute(strSQLRun); blExcute = oleUtility.SQLExecute(strSQLStop); blExcute = oleUtility.SQLExecute(strSQLPause); } catch { break; } } #endregion #region Save Log String fileName = "Hourly Data " + dt.ToString("yyyyMMdd"); DataTable dtNew = new DataTable(); dtNew = WriteDataGridToTable(DGVlineHourly, "HourlyData"); LCADownTimeDataDriver dataDriver = LCADownTimeDataDriver.Instance; dataDriver.WriteDataToFile(dtNew, fileName); #endregion oleUtility.Commit(); oleUtility.destroy(); #endregion }
public void CreateCustomHourlyChart(DateTime dtFrom, DateTime dtEnd) { startDateTime = dtFrom; endDateTime = dtEnd; #region load datasource LCADownTimeDataDriver dataDriver = LCADownTimeDataDriver.Instance; if (dataDriver.LCADataSet.Tables.Count > 0) { dataDriver.LCADataSet.Tables.Clear(); } LCAMachineState2 lcaDownTime = new LCAMachineState2(); String sql = LCAMachineState2.CreateSql(startDateTime.ToString("dd-MM-yyyy HH:mm:ss"), endDateTime.ToString("dd-MM-yyyy HH:mm:ss"),strCellNo); dataDriver.CrunchTable(sql, "LCA_DOWNTIME"); for (int i = 0; i < dataDriver.LCADataSet.Tables["LCA_DOWNTIME"].Rows.Count; i++) { if ((DateTime)dataDriver.LCADataSet.Tables["LCA_DOWNTIME"].Rows[i][LCAMachineState2.HEADERCHAR.STOP_TIME.ToString()] > dtEnd) { dataDriver.LCADataSet.Tables["LCA_DOWNTIME"].Rows[i][LCAMachineState2.HEADERCHAR.STOP_TIME.ToString()] = dtEnd; TimeSpan tempSpan = dtEnd - Convert.ToDateTime(dataDriver.LCADataSet.Tables["LCA_DOWNTIME"].Rows[i][LCAMachineState2.HEADERCHAR.START_TIME.ToString()]); dataDriver.LCADataSet.Tables["LCA_DOWNTIME"].Rows[i][LCAMachineState2.HEADERCHAR.TIME_OF_STATE.ToString()] = tempSpan.TotalSeconds; } } DGVstationState.DataSource = dataDriver.LCADataSet.Tables["LCA_DOWNTIME"]; #endregion #region UI if (dataDriver.LCADataSet.Tables["LCA_DOWNTIME"].Rows.Count == 0) { MessageBox.Show("No Record Found"); BtnDrawStationTimeSpanChart.Enabled = false; BtnStationHourlyReport.Enabled = false; BtnReportLine.Enabled = false; BtnDrawLineTimeSpanChart.Enabled = false; buManualImport.Enabled = false; LbxState.Enabled = false; LbxStation.Enabled = false; return; } LbxState.Enabled = true; LbxStation.Enabled = true; tabControl1.SelectedTab = TPStationState; #endregion #region Loda maxdate&mindate from Data Resouce DateTime thisStartTime, thisEndTime; minDateTime = startDateTime; maxDateTime = startDateTime; for (int i = 0; i < dataDriver.LCADataSet.Tables["LCA_DOWNTIME"].Rows.Count; i++) { thisStartTime = (DateTime)dataDriver.LCADataSet.Tables["LCA_DOWNTIME"].Rows[i][LCAMachineState2.HEADERCHAR.START_TIME.ToString()]; thisEndTime = (DateTime)dataDriver.LCADataSet.Tables["LCA_DOWNTIME"].Rows[i][LCAMachineState2.HEADERCHAR.STOP_TIME.ToString()]; if (thisStartTime < minDateTime) { minDateTime = thisStartTime; } if (thisEndTime > maxDateTime) { maxDateTime = thisEndTime; } } totalSecondSpan = (maxDateTime.Ticks - minDateTime.Ticks) / TimeSpan.TicksPerSecond; totalHourSpan = (maxDateTime.Ticks - minDateTime.Ticks) / TimeSpan.TicksPerHour; #endregion #region Load Station Data #region validate Table DataTable dt = (DataTable)DGVstationState.DataSource; DataTable dtNew = new DataTable("LCAHourlyDownTime"); if (dt == null || dt.Rows.Count == 0) { MessageBox.Show("Please Load Data First"); return; } #endregion #region station column&hour row add dtNew.Columns.Add(LCAMachineStateToPeriod.HEADERCHAR.Period.ToString()); foreach (LCABaseInfo.STATION_NAME station in (LCABaseInfo.STATION_NAME[])Enum.GetValues(typeof(LCABaseInfo.STATION_NAME))) { dtNew.Columns.Add(station.ToString()); } for (int i = 0; i < totalHourSpan + 1; i++) { DataRow newRow = dtNew.NewRow(); newRow[LCAMachineStateToPeriod.HEADERCHAR.Period.ToString()] = minDateTime.AddHours(i).ToString("yyyy-MM-dd-HH"); foreach (LCABaseInfo.STATION_NAME station in (LCABaseInfo.STATION_NAME[])Enum.GetValues(typeof(LCABaseInfo.STATION_NAME))) { newRow[station.ToString()] = 0; } dtNew.Rows.Add(newRow); } #endregion for (int i = 0; i < dt.Rows.Count; i++) { DataRow dr = dt.Rows[i]; //LCAMachineState2 lcaDownTime = new LCAMachineState2(); lcaDownTime.START_TIME = (DateTime)dr[LCAMachineState2.HEADERCHAR.START_TIME.ToString()]; lcaDownTime.STOP_TIME = (DateTime)dr[LCAMachineState2.HEADERCHAR.STOP_TIME.ToString()]; lcaDownTime.TIME_OF_STATE = System.Convert.ToInt32(dr[LCAMachineState2.HEADERCHAR.TIME_OF_STATE.ToString()]); lcaDownTime.CURRENTSTATE = System.Convert.ToInt32(dr[LCAMachineState2.HEADERCHAR.CURRENTSTATE.ToString()]); lcaDownTime.STATION_NAME = dr[LCAMachineState2.HEADERCHAR.STATION_NAME.ToString()].ToString(); #region check select option Boolean IsState = false; Boolean IsStation = false; foreach (String state in LbxState.SelectedItems) { if (lcaDownTime.CURRENTSTATE == (int)Enum.Parse(typeof(LCABaseInfo.OEE_STATE), state)) { IsState = true; break; } } if (!IsState) continue; foreach (String station in LbxStation.SelectedItems) { if (lcaDownTime.STATION_NAME == station) { IsStation = true; break; } } if (!IsStation) continue; #endregion #region hourly downtime calculation int HourIndex = Convert.ToInt32((lcaDownTime.START_TIME.Ticks - minDateTime.Ticks) / TimeSpan.TicksPerHour); int hourlydowntime = System.Convert.ToInt32(dtNew.Rows[HourIndex][lcaDownTime.STATION_NAME].ToString()); int stateTime = (int)lcaDownTime.TIME_OF_STATE; if (lcaDownTime.START_TIME.Second + lcaDownTime.START_TIME.Minute * 60 + stateTime > 3600) { dtNew.Rows[HourIndex][lcaDownTime.STATION_NAME] = hourlydowntime + 3600 - lcaDownTime.START_TIME.Second - lcaDownTime.START_TIME.Minute * 60; int j = 1; for (j = 0; j < (stateTime + lcaDownTime.START_TIME.Second + lcaDownTime.START_TIME.Minute * 60) / 3600 - 1; j++) { if (HourIndex + j + 1 > totalHourSpan - 1) { break; } dtNew.Rows[HourIndex + j + 1][lcaDownTime.STATION_NAME] = System.Convert.ToInt32(dtNew.Rows[HourIndex + j + 1][lcaDownTime.STATION_NAME].ToString()) + 3600; } if (HourIndex + j + 1 > totalHourSpan - 1) { continue; } dtNew.Rows[HourIndex + j + 1][lcaDownTime.STATION_NAME] = System.Convert.ToInt32(dtNew.Rows[HourIndex + j + 1][lcaDownTime.STATION_NAME].ToString()) + (lcaDownTime.START_TIME.Second + lcaDownTime.START_TIME.Minute * 60 + stateTime) % 3600; } else { dtNew.Rows[HourIndex][lcaDownTime.STATION_NAME] = hourlydowntime + stateTime; } #endregion } DGVstationHourly.DataSource = dtNew; tabControl1.SelectedTab = TPStationHourly; #endregion #region Draw Station Span Chart GetStationSpanChart(); #endregion #region Load Line Data #region validate table if (!dataDriver.LCADataSet.Tables.Contains("LCA_DOWNTIME") || dataDriver.LCADataSet.Tables["LCA_DOWNTIME"].Rows.Count < 1) { MessageBox.Show("Please load Souce Data Firstly"); return; } dataDriver.WriteTableToList("LCA_DOWNTIME", typeof(LCAMachineState2).FullName); List<object> dataList = dataDriver.LCADataList; DataTable dtLineNew = new DataTable("LCALineDownTime"); if (dataList == null || dataList.Count == 0) { MessageBox.Show("Please Load Data First"); return; } #endregion #region initial first state row DataRow iniRow; iniRow = dtLineNew.NewRow(); foreach (LCALineState.HEADERCHAR field in Enum.GetValues(typeof(LCALineState.HEADERCHAR))) { dtLineNew.Columns.Add(field.ToString(), typeof(LCALineState).GetProperty(field.ToString()).PropertyType); //dtNew.Columns.Add(field.ToString()); } iniRow[LCALineState.HEADERCHAR.START_TIME.ToString()] = startDateTime; iniRow[LCALineState.HEADERCHAR.STOP_TIME.ToString()] = startDateTime; //iniRow[LCALineState.HEADERCHAR.START_TIME.ToString()] = startDateTime.ToString("yyyy-MM-dd HH:mm:ss"); //iniRow[LCALineState.HEADERCHAR.STOP_TIME.ToString()] = startDateTime.ToString("yyyy-MM-dd HH:mm:ss"); iniRow[LCALineState.HEADERCHAR.TIME_OF_STATE.ToString()] = 0; iniRow[LCALineState.HEADERCHAR.HLD1_STATE.ToString()] = 1000; iniRow[LCALineState.HEADERCHAR.HLD2_STATE.ToString()] = 1000; iniRow[LCALineState.HEADERCHAR.BSE_STATE.ToString()] = 1000; iniRow[LCALineState.HEADERCHAR.SWG1_STATE.ToString()] = 1000; iniRow[LCALineState.HEADERCHAR.SWG2_STATE.ToString()] = 1000; iniRow[LCALineState.HEADERCHAR.ULRT_STATE.ToString()] = 1000; iniRow[LCALineState.HEADERCHAR.STATION.ToString()] = "START"; iniRow[LCALineState.HEADERCHAR.ERROR_DESCRIPTION.ToString()] = ""; iniRow[LCALineState.HEADERCHAR.LCA_LINE.ToString()] = ""; dtLineNew.Rows.Add(iniRow); #endregion #region add new state row DataRow drLine; LCALineState[] LineStateArray = new LCALineState[dataList.Count]; LCAMachineState2 thisDownTime, nextDownTime; for (int i = 0; i < dataList.Count; i++) { thisDownTime = (LCAMachineState2)dataList[i]; if (i == dataList.Count - 1) { nextDownTime = new LCAMachineState2(); nextDownTime.START_TIME = maxDateTime; } else { nextDownTime = (LCAMachineState2)dataList[i + 1]; } DataRow lastRow = dtLineNew.Rows[i]; drLine = dtLineNew.NewRow(); foreach (LCALineState.HEADERCHAR field in Enum.GetValues(typeof(LCALineState.HEADERCHAR))) { drLine[field.ToString()] = lastRow[field.ToString()]; } drLine[LCALineState.HEADERCHAR.START_TIME.ToString()] = thisDownTime.START_TIME.ToString("yyyy-MM-dd HH:mm:ss"); drLine[LCALineState.HEADERCHAR.STOP_TIME.ToString()] = nextDownTime.START_TIME.ToString("yyyy-MM-dd HH:mm:ss"); drLine[LCALineState.HEADERCHAR.TIME_OF_STATE.ToString()] = (nextDownTime.START_TIME.Ticks - thisDownTime.START_TIME.Ticks) / TimeSpan.TicksPerSecond; drLine[LCALineState.HEADERCHAR.LCA_LINE.ToString()] = thisDownTime.LINE; drLine[LCALineState.HEADERCHAR.STATION.ToString()] = thisDownTime.STATION_NAME; drLine[LCALineState.HEADERCHAR.ERROR_DESCRIPTION.ToString()] = thisDownTime.ERROR_DESCRIPTION; drLine[LCALineState.HEADERCHAR.LCA_LINE.ToString()] = thisDownTime.LINE; int MachineState = (int)thisDownTime.CURRENTSTATE; switch (thisDownTime.STATION_NAME) { case "HgaLoad1": drLine[LCALineState.HEADERCHAR.HLD1_STATE.ToString()] = MachineState; break; case "HgaLoad2": drLine[LCALineState.HEADERCHAR.HLD2_STATE.ToString()] = MachineState; break; case "BossEngage": drLine[LCALineState.HEADERCHAR.BSE_STATE.ToString()] = MachineState; break; case "SWAGE1": drLine[LCALineState.HEADERCHAR.SWG1_STATE.ToString()] = MachineState; break; case "SWAGE2": drLine[LCALineState.HEADERCHAR.SWG2_STATE.ToString()] = MachineState; break; case "ULRT": drLine[LCALineState.HEADERCHAR.ULRT_STATE.ToString()] = MachineState; break; default: break; } dtLineNew.Rows.Add(drLine); } #endregion dataDriver.addTable(dtLineNew); DGVlineState.DataSource = dtLineNew; tabControl1.SelectedTab = TPLineState; #endregion #region Draw Line Data Chart #region Draw Line D/T Chart #region validate table dataDriver = LCADownTimeDataDriver.Instance; if (!dataDriver.LCADataSet.Tables.Contains("LCALineDownTime") || dataDriver.LCADataSet.Tables["LCALineDownTime"].Rows.Count < 1) { MessageBox.Show("Please load Souce Data Firstly"); return; } dataDriver.WriteTableToList("LCALineDownTime", typeof(LCALineState).FullName); List<object> dataLineList = dataDriver.LCADataList; if (dataList == null || dataList.Count == 0) { MessageBox.Show("Please Load Data and LCA line State Report First"); return; } #endregion #region initial Draw Chart setting TimeSpanBarChartDraw ChartDraw = TimeSpanBarChartDraw.Instance; ChartDraw.IniGraphics(); ChartDraw.ChartAreaStartX = 80; ChartDraw.ChartAreaStartY = 80; ChartDraw.BarStartX = 100; ChartDraw.BarStartX = 100; #endregion #region Draw Series Legends SeriesLegend[] SeriesLegends = new SeriesLegend[LCADrawInfo.LineState_Colors().Count]; int SereiesIndex = 0; foreach (KeyValuePair<String, Color> lineStateColor in LCADrawInfo.LineState_Colors()) { SeriesLegends[SereiesIndex] = new SeriesLegend(SereiesIndex, lineStateColor.Key.ToString(), lineStateColor.Value); SereiesIndex++; } ChartDraw.DrawSeriesLengends(SeriesLegends); #endregion DataTable dtLineChartNew = new DataTable("LCALineHourlyDownTime"); LCADownTimeRule rule = LCADownTimeRule.Instance; #region station column&hour row add dtLineChartNew.Columns.Add(LCAMachineStateToPeriod.HEADERCHAR.Period.ToString()); foreach (LCABaseInfo.LINE_STATE lineState in (LCABaseInfo.LINE_STATE[])Enum.GetValues(typeof(LCABaseInfo.LINE_STATE))) { dtLineChartNew.Columns.Add(lineState.ToString()); } for (int i = 0; i < totalHourSpan + 1; i++) { DataRow newRow = dtLineChartNew.NewRow(); newRow[LCAMachineStateToPeriod.HEADERCHAR.Period.ToString()] = minDateTime.AddHours(i).ToString("yyyy-MM-dd-HH"); foreach (LCABaseInfo.LINE_STATE lineState in (LCABaseInfo.LINE_STATE[])Enum.GetValues(typeof(LCABaseInfo.LINE_STATE))) { newRow[lineState.ToString()] = 0; } dtLineChartNew.Rows.Add(newRow); } #endregion for (int i = 0; i < dataDriver.LCADataList.Count; i++) { LCALineState lineDowntime = (LCALineState)dataList[i]; #region Draw Time Span Bar float barStartX = (float)(lineDowntime.START_TIME.Ticks - minDateTime.Ticks) / TimeSpan.TicksPerSecond / totalSecondSpan * ChartDraw.ChartWitdh; float barWidth = (float)lineDowntime.TIME_OF_STATE / totalSecondSpan * ChartDraw.ChartWitdh; TimeSpanBar bar = new TimeSpanBar(0, barStartX, barWidth); bar.BarColor = LCADrawInfo.LineState_Colors()["Pause"]; if (rule.IsLineDownTime(lineDowntime)) { bar.BarColor = LCADrawInfo.LineState_Colors()["Stop"]; } if (rule.IsLineUpTime(lineDowntime)) { bar.BarColor = LCADrawInfo.LineState_Colors()["Run"]; } ChartDraw.DrawBar(bar); #endregion LCAMachineStateToPeriod stateToHour = new LCAMachineStateToPeriod(); lineDowntime.LCA_LINE = "LCA_CELL_2"; #region hourly downtime calculation int HourIndex = Convert.ToInt32((lineDowntime.START_TIME.Ticks - minDateTime.Ticks) / TimeSpan.TicksPerHour); String lineState = LCABaseInfo.LINE_STATE.Pause.ToString(); if (rule.IsLineUpTime(lineDowntime)) { lineState = LCABaseInfo.LINE_STATE.Run.ToString(); } if (rule.IsLineDownTime(lineDowntime)) { lineState = LCABaseInfo.LINE_STATE.Stop.ToString(); } int hourlydowntime = System.Convert.ToInt32(dtLineChartNew.Rows[HourIndex][lineState].ToString()); int stateTime = (int)lineDowntime.TIME_OF_STATE; if (lineDowntime.START_TIME.Second + lineDowntime.START_TIME.Minute * 60 + stateTime > 3600) { dtLineChartNew.Rows[HourIndex][lineState] = hourlydowntime + 3600 - lineDowntime.START_TIME.Second - lineDowntime.START_TIME.Minute * 60; int j = 1; for (j = 0; j < (stateTime + lineDowntime.START_TIME.Second + lineDowntime.START_TIME.Minute * 60) / 3600 - 1; j++) { if (HourIndex + j + 1 > totalHourSpan - 1) { break; } dtLineChartNew.Rows[HourIndex + j + 1][lineState] = System.Convert.ToInt32(dtLineChartNew.Rows[HourIndex + j + 1][lineState].ToString()) + 3600; } if (HourIndex + j + 1 > totalHourSpan - 1) { continue; } dtLineChartNew.Rows[HourIndex + j + 1][lineState] = System.Convert.ToInt32(dtLineChartNew.Rows[HourIndex + j + 1][lineState].ToString()) + (lineDowntime.START_TIME.Second + lineDowntime.START_TIME.Minute * 60 + stateTime) % 3600; } else { dtLineChartNew.Rows[HourIndex][lineState] = hourlydowntime + stateTime; } #endregion } #endregion #region Draw Output Interval Chart #region Get Hourly Output Data string strFrom, strEnd; OleDbUtility oleUtility = new OleDbUtility("ods", "users", "users"); oleUtility.getConnection(); oleUtility.OpenConnection(); strFrom = dtFrom.ToString("yyyy-MM-dd HH:mm"); strEnd = dtEnd.ToString("yyyy-MM-dd HH:mm"); string strSQL = "select to_char(lca.birth_date,'yyyy-MM-dd-hh24') as hourly,count(*) as output from lca.lca_data2 lca where to_char(lca.birth_date,'yyyy-MM-dd hh24:mi') between '" + strFrom + "' and '" + strEnd + "' and lca_line='"+strCellNo+"' group by to_char(lca.birth_date,'yyyy-MM-dd-hh24') order by to_char(lca.birth_date,'yyyy-MM-dd-hh24')"; DataTable dtOutput = new DataTable("OutputSourceData"); dtOutput = oleUtility.SQLQuery_DataTable(strSQL); dtLineChartNew.Columns.Add("Output"); for (int i = 0, j = 0; i < dtLineChartNew.Rows.Count; i++) { if (i >= dtLineChartNew.Rows.Count || j >= dtOutput.Rows.Count) { break; } else { if (dtLineChartNew.Rows[i][0].ToString() == dtOutput.Rows[j][0].ToString()) { dtLineChartNew.Rows[i]["Output"] = dtOutput.Rows[j][1]; j++; } else { dtLineChartNew.Rows[i]["Output"] = 0; } } } //DataTable dtCombine = dataDriver.CombineDataTable(dtNew, dataDriver.LCADataSet.Tables["OutputSourceData"], "HourlyOutput"); #endregion #region Draw TimeSpan Grid SeriesGrid lineGrid = new SeriesGrid(0, "LCA_CELL_2"); ChartDraw.DrawSeriersGrid(lineGrid); for (int i = 0; i < totalHourSpan + 2; i++) { String LabelText = (minDateTime.AddHours(i).Hour).ToString(); TimeSpanGrid gird = new TimeSpanGrid(LabelText, (float)3600 / totalSecondSpan * ChartDraw.ChartWitdh * i, 0, 500); ChartDraw.DrawTimeSpanGrid(gird); } #endregion #region Draw Chart Area TimeIntervalChartDraw graph = TimeIntervalChartDraw.Instance; IntervalChartArea chartArea = new IntervalChartArea(100, 150, 800, 430); graph.colorSplit = 8; graph.IniGraphics(ChartDraw.ChartImg); graph.AddChartArea(chartArea); #endregion #region Get Time Span DataTable dtSQL = new DataTable("SQLTabel"); DataTable dtInterval = new DataTable("IntervalTabel"); dtInterval.Columns.Add("Time", typeof(int)); dtInterval.Columns.Add("Interval", typeof(int)); DataTable dtIntervalGrid = new DataTable("GridData"); dtIntervalGrid.Columns.Add("Time", typeof(DateTime)); dtIntervalGrid.Columns.Add("Interval", typeof(int)); TimeSpan span = dtEnd - dtFrom; strFrom = dtFrom.ToString("yyyy-MM-dd HH:mm:ss"); strEnd = dtEnd.ToString("yyyy-MM-dd HH:mm:ss"); #endregion #region Draw Grid Int32 yMid = Convert.ToInt32(textMid.Text); Int32 yMax = Convert.ToInt32(textMax.Text); span = dtEnd - dtFrom; LegendGrid grid = new LegendGrid(Convert.ToInt32(span.TotalHours), yMid); graph.AddAxis(grid); #endregion #region Output Data strSQL = "select to_char(birth_date,'yyyy-MM-dd hh24:mi:ss') as dateChar from lca.lca_data2 where to_char(birth_date,'yyyy-MM-dd hh24:mi:ss') between '" + strFrom + "' and '" + strEnd + "' and lca_line='2' order by birth_date"; dtSQL = oleUtility.SQLQuery_DataTable(strSQL); if (dtSQL.Rows.Count == 0) { return; } int intRed = 0; int intGreen = 0; int intYellow = 0; #endregion #region Draw First Point IntervalBar barPoint = new IntervalBar(); DataRow newIntervalRow = dtInterval.NewRow(); span = Convert.ToDateTime(dtSQL.Rows[0][0].ToString()) - Convert.ToDateTime(strFrom); newIntervalRow[0] = span.TotalSeconds; newIntervalRow[1] = span.TotalSeconds; if (Convert.ToInt32(newIntervalRow[1]) >= yMid && Convert.ToInt32(newIntervalRow[1]) < yMax) { barPoint.BarHeight = yMid; barPoint.BarColor = Color.Yellow; intYellow++; } else if (Convert.ToInt32(newIntervalRow[1]) >= yMax) { barPoint.BarHeight = yMid; barPoint.BarColor = Color.Orchid; intRed++; } else { barPoint.BarHeight = Convert.ToInt32(newIntervalRow[1]); barPoint.BarColor = Color.LightSkyBlue; intGreen++; } barPoint.XStart = 0; barPoint.YStart = barPoint.BarHeight; barPoint.BarWidth = Convert.ToInt32(newIntervalRow[1]); graph.DrawBarPoint(barPoint); dtInterval.Rows.Add(newIntervalRow); DataRow dtGrid = dtIntervalGrid.NewRow(); if (Convert.ToInt32(newIntervalRow[1]) >= yMax) { dtGrid[0] = Convert.ToDateTime(strFrom).AddSeconds(Convert.ToInt32(newIntervalRow[0])); dtGrid[1] = newIntervalRow[1]; dtIntervalGrid.Rows.Add(dtGrid); } #endregion #region Draw Other Points for (int i = 1; i < dtSQL.Rows.Count; i++) { newIntervalRow = dtInterval.NewRow(); span = Convert.ToDateTime(dtSQL.Rows[i][0].ToString()) - Convert.ToDateTime(strFrom); newIntervalRow[0] = span.TotalSeconds; newIntervalRow[1] = span.TotalSeconds - Convert.ToInt32(dtInterval.Rows[i - 1][0].ToString()); if (Convert.ToInt32(newIntervalRow[1]) >= yMid && Convert.ToInt32(newIntervalRow[1]) < yMax) { barPoint.BarHeight = yMid; barPoint.BarColor = Color.Yellow; intYellow++; } else if (Convert.ToInt32(newIntervalRow[1]) >= yMax) { barPoint.BarHeight = yMid; barPoint.BarColor = Color.Orchid; intRed++; dtGrid = dtIntervalGrid.NewRow(); dtGrid[0] = Convert.ToDateTime(strFrom).AddSeconds(Convert.ToInt32(newIntervalRow[0])); dtGrid[1] = newIntervalRow[1]; dtIntervalGrid.Rows.Add(dtGrid); } else { barPoint.BarHeight = Convert.ToInt32(newIntervalRow[1]); barPoint.BarColor = Color.LightSkyBlue; intGreen++; } barPoint.YStart = barPoint.BarHeight; barPoint.BarWidth = Convert.ToInt32(newIntervalRow[1]); barPoint.XStart = Convert.ToInt32(newIntervalRow[0]) - Convert.ToInt32(newIntervalRow[1]); graph.DrawBarPoint(barPoint); dtInterval.Rows.Add(newIntervalRow); } #endregion #endregion #region Show Chart ON Form dtInvervalAbnormal.DataSource = dtIntervalGrid; graph.AddCountLabel(intRed, intGreen, intYellow); graph.DrawStartLabel(dtSQL.Rows[0][0].ToString()); graph.DrawEndLabel(dtSQL.Rows[dtSQL.Rows.Count - 1][0].ToString()); String fileName = "LineDT" + startDateTime.ToString("yyyy-MM-dd-HH"); DGVlineHourly.DataSource = dtLineChartNew; tabControl1.SelectedTab = TPLineHourly; graph.saveImg(fileName); graph.saveWebImg(fileName); //ChartForm newForm = new ChartForm(); //newForm.Text = graph.ImgFileName; //newForm.Height = ChartDraw.ChartHeight; //newForm.Width = ChartDraw.ChartWitdh; //newForm.BackgroundImage = graph.ChartImg; //newForm.BackgroundImageLayout = ImageLayout.Stretch; //newForm.Show(); #endregion #endregion }
//Button Line Hourly Report Chart public DataTable GetHourData() { #region 注释 /* #region validate table LCADownTimeDataDriver dataDriver = LCADownTimeDataDriver.Instance; if (!dataDriver.LCADataSet.Tables.Contains("LCALineDownTime") || dataDriver.LCADataSet.Tables["LCALineDownTime"].Rows.Count < 1) { MessageBox.Show("Please load Souce Data Firstly"); return; } dataDriver.WriteTableToList("LCALineDownTime", typeof(LCALineState).FullName); List<object> dataList = dataDriver.LCADataList; if (dataList == null || dataList.Count == 0) { MessageBox.Show("Please Load Data and LCA line State Report First"); return; } #endregion DataTable dtNew = new DataTable("LCALineHourlyDownTime"); LCADownTimeRule rule = LCADownTimeRule.Instance; #region station column&hour row add dtNew.Columns.Add(LCAMachineStateToPeriod.HEADERCHAR.Period.ToString()); foreach (LCABaseInfo.LINE_STATE lineState in (LCABaseInfo.LINE_STATE[])Enum.GetValues(typeof(LCABaseInfo.LINE_STATE))) { dtNew.Columns.Add(lineState.ToString()); } for (int i = 0; i < totalHourSpan; i++) { DataRow newRow = dtNew.NewRow(); newRow[LCAMachineStateToPeriod.HEADERCHAR.Period.ToString()] = minDateTime.AddHours(i).ToString("yyyy-MM-dd-HH"); foreach (LCABaseInfo.LINE_STATE lineState in (LCABaseInfo.LINE_STATE[])Enum.GetValues(typeof(LCABaseInfo.LINE_STATE))) { newRow[lineState.ToString()] = 0; } dtNew.Rows.Add(newRow); } #endregion for (int i = 0; i < dataDriver.LCADataList.Count; i++) { LCALineState lineDowntime = (LCALineState)dataList[i]; LCAMachineStateToPeriod stateToHour = new LCAMachineStateToPeriod(); lineDowntime.LCA_LINE = "LCA_CELL_2"; #region hourly downtime calculation int HourIndex = Convert.ToInt32((lineDowntime.START_TIME.Ticks - minDateTime.Ticks) / TimeSpan.TicksPerHour); String lineState = LCABaseInfo.LINE_STATE.Pause.ToString(); if (rule.IsLineUpTime(lineDowntime)) { lineState = LCABaseInfo.LINE_STATE.Run.ToString(); } if (rule.IsLineDownTime(lineDowntime)) { lineState = LCABaseInfo.LINE_STATE.Stop.ToString(); } int hourlydowntime = System.Convert.ToInt32(dtNew.Rows[HourIndex][lineState].ToString()); int stateTime = (int)lineDowntime.TIME_OF_STATE; if (lineDowntime.START_TIME.Second + lineDowntime.START_TIME.Minute * 60 + stateTime > 3600) { dtNew.Rows[HourIndex][lineState] = hourlydowntime + 3600 - lineDowntime.START_TIME.Second - lineDowntime.START_TIME.Minute * 60; int j = 1; for (j = 0; j < (stateTime + lineDowntime.START_TIME.Second + lineDowntime.START_TIME.Minute * 60) / 3600 - 1; j++) { if (HourIndex + j + 1 > totalHourSpan - 1) { break; } dtNew.Rows[HourIndex + j + 1][lineState] = System.Convert.ToInt32(dtNew.Rows[HourIndex + j + 1][lineState].ToString()) + 3600; } if (HourIndex + j + 1 > totalHourSpan - 1) { continue; } dtNew.Rows[HourIndex + j + 1][lineState] = System.Convert.ToInt32(dtNew.Rows[HourIndex + j + 1][lineState].ToString()) + (lineDowntime.START_TIME.Second + lineDowntime.START_TIME.Minute * 60 + stateTime) % 3600; } else { dtNew.Rows[HourIndex][lineState] = hourlydowntime + stateTime; } #endregion } #region Get Hourly Output Data string strFrom, strEnd; OleDbUtility oleUtility = new OleDbUtility("ods", "users", "users"); oleUtility.getConnection(); oleUtility.OpenConnection(); strFrom = dateTimePicker1.Value.AddDays(-1).ToString("yyyy-MM-dd") + " 07:00"; strEnd = dateTimePicker1.Value.ToString("yyyy-MM-dd") + " 07:00"; string strSQL = "select to_char(lca.birth_date,'yyyy-MM-dd-hh24') as hourly,count(*) as output from lca.lca_data2 lca where to_char(lca.birth_date,'yyyy-MM-dd hh24:mi') between '" + strFrom +"' and '"+ strEnd + "' group by to_char(lca.birth_date,'yyyy-MM-dd-hh24') order by to_char(lca.birth_date,'yyyy-MM-dd-hh24')" ; DataTable dtOutput = new DataTable("OutputSourceData"); dtOutput = oleUtility.SQLQuery_DataTable(strSQL); dtNew.Columns.Add("Output"); for (int i = 0,j=0; i < dtNew.Rows.Count; i++) { if (i >= dtNew.Rows.Count || j >= dtOutput.Rows.Count) { break; } else { if (dtNew.Rows[i][0].ToString() == dtOutput.Rows[j][0].ToString()) { dtNew.Rows[i]["Output"] = dtOutput.Rows[j][1]; j++; } else { dtNew.Rows[i]["Output"] = 0; } } } //DataTable dtCombine = dataDriver.CombineDataTable(dtNew, dataDriver.LCADataSet.Tables["OutputSourceData"], "HourlyOutput"); DGVlineHourly.DataSource = dtNew; */ #endregion #region validate table LCADownTimeDataDriver dataDriver = LCADownTimeDataDriver.Instance; if (!dataDriver.LCADataSet.Tables.Contains("LCALineDownTime") || dataDriver.LCADataSet.Tables["LCALineDownTime"].Rows.Count < 1) { MessageBox.Show("Please load Souce Data Firstly"); return null; } dataDriver.WriteTableToList("LCALineDownTime", typeof(LCALineState).FullName); List<object> dataList = dataDriver.LCADataList; if (dataList == null || dataList.Count == 0) { MessageBox.Show("Please Load Data and LCA line State Report First"); return null; } #endregion #region initial Draw Chart setting TimeSpanBarChartDraw ChartDraw = TimeSpanBarChartDraw.Instance; ChartDraw.IniGraphics(); ChartDraw.ChartAreaStartX = 80; ChartDraw.ChartAreaStartY = 80; ChartDraw.BarStartX = 100; ChartDraw.BarStartX = 100; #endregion #region Draw Series Legends SeriesLegend[] SeriesLegends = new SeriesLegend[LCADrawInfo.LineState_Colors().Count]; int SereiesIndex = 0; foreach (KeyValuePair<String, Color> lineStateColor in LCADrawInfo.LineState_Colors()) { SeriesLegends[SereiesIndex] = new SeriesLegend(SereiesIndex, lineStateColor.Key.ToString(), lineStateColor.Value); SereiesIndex++; } ChartDraw.DrawSeriesLengends(SeriesLegends); #endregion DataTable dtNew = new DataTable("LCALineHourlyDownTime"); LCADownTimeRule rule = LCADownTimeRule.Instance; #region station column&hour row add dtNew.Columns.Add(LCAMachineStateToPeriod.HEADERCHAR.Period.ToString()); foreach (LCABaseInfo.LINE_STATE lineState in (LCABaseInfo.LINE_STATE[])Enum.GetValues(typeof(LCABaseInfo.LINE_STATE))) { dtNew.Columns.Add(lineState.ToString()); } for (int i = 0; i < totalHourSpan + 1; i++) { DataRow newRow = dtNew.NewRow(); newRow[LCAMachineStateToPeriod.HEADERCHAR.Period.ToString()] = minDateTime.AddHours(i).ToString("yyyy-MM-dd-HH"); foreach (LCABaseInfo.LINE_STATE lineState in (LCABaseInfo.LINE_STATE[])Enum.GetValues(typeof(LCABaseInfo.LINE_STATE))) { newRow[lineState.ToString()] = 0; } dtNew.Rows.Add(newRow); } #endregion for (int i = 0; i < dataDriver.LCADataList.Count; i++) { LCALineState lineDowntime = (LCALineState)dataList[i]; #region Draw Time Span Bar float barStartX = (float)(lineDowntime.START_TIME.Ticks - minDateTime.Ticks) / TimeSpan.TicksPerSecond / totalSecondSpan * ChartDraw.ChartWitdh; float barWidth = (float)lineDowntime.TIME_OF_STATE / totalSecondSpan * ChartDraw.ChartWitdh; TimeSpanBar bar = new TimeSpanBar(0, barStartX, barWidth); bar.BarColor = LCADrawInfo.LineState_Colors()["Pause"]; if (rule.IsLineDownTime(lineDowntime)) { bar.BarColor = LCADrawInfo.LineState_Colors()["Stop"]; } if (rule.IsLineUpTime(lineDowntime)) { bar.BarColor = LCADrawInfo.LineState_Colors()["Run"]; } ChartDraw.DrawBar(bar); #endregion LCAMachineStateToPeriod stateToHour = new LCAMachineStateToPeriod(); lineDowntime.LCA_LINE = "LCA_CELL_2"; #region hourly downtime calculation int HourIndex = Convert.ToInt32((lineDowntime.START_TIME.Ticks - minDateTime.Ticks) / TimeSpan.TicksPerHour); String lineState = LCABaseInfo.LINE_STATE.Pause.ToString(); if (rule.IsLineUpTime(lineDowntime)) { lineState = LCABaseInfo.LINE_STATE.Run.ToString(); } if (rule.IsLineDownTime(lineDowntime)) { lineState = LCABaseInfo.LINE_STATE.Stop.ToString(); } int hourlydowntime = System.Convert.ToInt32(dtNew.Rows[HourIndex][lineState].ToString()); int stateTime = (int)lineDowntime.TIME_OF_STATE; if (lineDowntime.START_TIME.Second + lineDowntime.START_TIME.Minute * 60 + stateTime > 3600) { dtNew.Rows[HourIndex][lineState] = hourlydowntime + 3600 - lineDowntime.START_TIME.Second - lineDowntime.START_TIME.Minute * 60; int j = 1; for (j = 0; j < (stateTime + lineDowntime.START_TIME.Second + lineDowntime.START_TIME.Minute * 60) / 3600 - 1; j++) { if (HourIndex + j + 1 > totalHourSpan - 1) { break; } dtNew.Rows[HourIndex + j + 1][lineState] = System.Convert.ToInt32(dtNew.Rows[HourIndex + j + 1][lineState].ToString()) + 3600; } if (HourIndex + j + 1 > totalHourSpan - 1) { continue; } dtNew.Rows[HourIndex + j + 1][lineState] = System.Convert.ToInt32(dtNew.Rows[HourIndex + j + 1][lineState].ToString()) + (lineDowntime.START_TIME.Second + lineDowntime.START_TIME.Minute * 60 + stateTime) % 3600; } else { dtNew.Rows[HourIndex][lineState] = hourlydowntime + stateTime; } #endregion } #region Get Hourly Output Data string strFrom, strEnd; OleDbUtility oleUtility = new OleDbUtility("ods", "users", "users"); oleUtility.getConnection(); oleUtility.OpenConnection(); strFrom = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd") + " 07:00"; strEnd = DateTime.Now.ToString("yyyy-MM-dd") + " 07:00"; switch (LbxShift.SelectedItem.ToString()) { case "Day": strFrom = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd") + " 07:00"; strEnd = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd") + " 19:00"; break; case "Night": strFrom = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd") + " 19:00"; strEnd = DateTime.Now.ToString("yyyy-MM-dd") + " 07:00"; break; default: strFrom = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd") + " 07:00"; strEnd = DateTime.Now.ToString("yyyy-MM-dd") + " 07:00"; break; } string strSQL = "select to_char(lca.birth_date,'yyyy-MM-dd-hh24') as hourly,count(*) as output from lca.lca_data2 lca where to_char(lca.birth_date,'yyyy-MM-dd hh24:mi') between '" + strFrom + "' and '" + strEnd + "' and lca_line='"+ strCellNo + "' group by to_char(lca.birth_date,'yyyy-MM-dd-hh24') order by to_char(lca.birth_date,'yyyy-MM-dd-hh24')"; DataTable dtOutput = new DataTable("OutputSourceData"); dtOutput = oleUtility.SQLQuery_DataTable(strSQL); dtNew.Columns.Add("Output"); for (int i = 0, j = 0; i < dtNew.Rows.Count; i++) { if (i >= dtNew.Rows.Count || j >= dtOutput.Rows.Count) { break; } else { if (dtNew.Rows[i][0].ToString() == dtOutput.Rows[j][0].ToString()) { dtNew.Rows[i]["Output"] = dtOutput.Rows[j][1]; j++; } else { dtNew.Rows[i]["Output"] = 0; } } } dtOutput.Clear(); for (int i = 0; i < dtNew.Rows.Count; i++) { DataRow dtRowOutput = dtOutput.NewRow(); dtRowOutput[0] = dtNew.Rows[i]["Output"]; dtOutput.Rows.Add(dtRowOutput); } //DataTable dtCombine = dataDriver.CombineDataTable(dtNew, dataDriver.LCADataSet.Tables["OutputSourceData"], "HourlyOutput"); #endregion SeriesGrid lineGrid = new SeriesGrid(0, "LCA_CELL_2"); ChartDraw.DrawSeriersGrid(lineGrid); #region Draw TimeSpan Grid for (int i = 0; i < totalHourSpan + 2; i++) { String LabelText = (minDateTime.AddHours(i).Hour).ToString(); TimeSpanGrid gird = new TimeSpanGrid(LabelText, (float)3600 / totalSecondSpan * ChartDraw.ChartWitdh * i, 0, 500); ChartDraw.DrawTimeSpanGrid(gird); } #endregion #region Draw Interval Chart switch (LbxShift.SelectedItem.ToString()) { case "All Shift": strFrom = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd") + " 07:00:00"; strEnd = DateTime.Now.ToString("yyyy-MM-dd") + " 07:00:00"; break; case "Day": strFrom = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd") + " 07:00:00"; strEnd = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd") + " 19:00:00"; break; case "Night": strFrom = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd") + " 19:00:00"; strEnd = DateTime.Now.ToString("yyyy-MM-dd") + " 07:00:00"; break; } TimeSpan span = Convert.ToDateTime(strEnd) - Convert.ToDateTime(strFrom); //Get Interval Data TimeIntervalChartDraw graph = TimeIntervalChartDraw.Instance; #region Draw Chart Area IntervalChartArea chartArea = new IntervalChartArea(100, 150, 800, 430); graph.colorSplit = 8; graph.IniGraphics(ChartDraw.ChartImg); graph.AddChartArea(chartArea); #endregion DataTable dtSQL = new DataTable("SQLTabel"); DataTable dtInterval = new DataTable("IntervalTabel"); dtInterval.Columns.Add("x", typeof(int)); dtInterval.Columns.Add("y", typeof(int)); #region Draw Grid Int32 yMid = Convert.ToInt32(textMid.Text); Int32 yMax = Convert.ToInt32(textMax.Text); span = Convert.ToDateTime(strEnd) - Convert.ToDateTime(strFrom); LegendGrid grid = new LegendGrid(Convert.ToInt32(span.TotalHours), yMid); graph.AddAxis(grid); #endregion #region Get Source Data strSQL = "select to_char(birth_date,'yyyy-MM-dd hh24:mi:ss') as dateChar from lca.lca_data2 where to_char(birth_date,'yyyy-MM-dd hh24:mi:ss') between '" + strFrom + "' and '" + strEnd + "' and lca_line='"+strCellNo+"' order by birth_date"; dtSQL = oleUtility.SQLQuery_DataTable(strSQL); if (dtSQL.Rows.Count == 0) { return null; } #endregion int intRed = 0; int intGreen = 0; int intYellow = 0; IntervalBar barPoint = new IntervalBar(); DataRow newIntervalRow = dtInterval.NewRow(); span = Convert.ToDateTime(dtSQL.Rows[0][0].ToString()) - Convert.ToDateTime(strFrom); newIntervalRow[0] = span.TotalSeconds; newIntervalRow[1] = span.TotalSeconds; #region Draw Point if (Convert.ToInt32(newIntervalRow[1]) >= yMid && Convert.ToInt32(newIntervalRow[1]) < yMax) { barPoint.BarHeight = yMid; barPoint.BarColor = Color.Yellow; intYellow++; } else if (Convert.ToInt32(newIntervalRow[1]) >= yMax) { barPoint.BarHeight = yMid; barPoint.BarColor = Color.Orchid; intRed++; } else { barPoint.BarHeight = Convert.ToInt32(newIntervalRow[1]); barPoint.BarColor = Color.LightSkyBlue; intGreen++; } barPoint.XStart = 0; barPoint.YStart = barPoint.BarHeight; barPoint.BarWidth = Convert.ToInt32(newIntervalRow[1]); graph.DrawBarPoint(barPoint); dtInterval.Rows.Add(newIntervalRow); for (int i = 1; i < dtSQL.Rows.Count; i++) { newIntervalRow = dtInterval.NewRow(); span = Convert.ToDateTime(dtSQL.Rows[i][0].ToString()) - Convert.ToDateTime(strFrom); newIntervalRow[0] = span.TotalSeconds; newIntervalRow[1] = span.TotalSeconds - Convert.ToInt32(dtInterval.Rows[i - 1][0].ToString()); if (Convert.ToInt32(newIntervalRow[1]) >= yMid && Convert.ToInt32(newIntervalRow[1]) < yMax) { barPoint.BarHeight = yMid; barPoint.BarColor = Color.Yellow; intYellow++; } else if (Convert.ToInt32(newIntervalRow[1]) >= yMax) { barPoint.BarHeight = yMid; barPoint.BarColor = Color.Orchid; intRed++; } else { barPoint.BarHeight = Convert.ToInt32(newIntervalRow[1]); barPoint.BarColor = Color.LightSkyBlue; intGreen++; } barPoint.YStart = barPoint.BarHeight; barPoint.BarWidth = Convert.ToInt32(newIntervalRow[1]); barPoint.XStart = Convert.ToInt32(newIntervalRow[0]) - Convert.ToInt32(newIntervalRow[1]); graph.DrawBarPoint(barPoint); dtInterval.Rows.Add(newIntervalRow); } #endregion //graph.AddSeries(dtInterval); graph.AddCountLabel(intRed, intGreen, intYellow); graph.DrawStartLabel(dtSQL.Rows[0][0].ToString()); graph.DrawEndLabel(dtSQL.Rows[dtSQL.Rows.Count - 1][0].ToString()); #endregion String fileName = ""; switch (LbxShift.SelectedItem.ToString()) { case "All Shift": fileName = "LineDT" + startDateTime.ToString("yyyy-MM-dd"); break; case "Day": fileName = "LineDT" + startDateTime.ToString("yyyy-MM-dd") + "M"; break; case "Night": fileName = "LineDT" + startDateTime.ToString("yyyy-MM-dd") + "N"; break; } DGVlineHourly.DataSource = dtNew; buManualImport.Enabled = true; //dataDriver.WriteDataToFile(dtNew, fileName); tabControl1.SelectedTab = TPLineHourly; //ChartDraw.saveImg(fileName); //ChartForm newForm = new ChartForm(); //newForm.Text = ChartDraw.ImgFileName; //newForm.Height = ChartDraw.ChartHeight; //newForm.Width = ChartDraw.ChartWitdh; //newForm.BackgroundImage = ChartDraw.ChartImg; //newForm.BackgroundImageLayout = ImageLayout.Stretch; graph.saveImg(fileName); graph.saveWebImg(fileName); ChartForm newForm = new ChartForm(); newForm.Text = graph.ImgFileName; newForm.Height = ChartDraw.ChartHeight; newForm.Width = ChartDraw.ChartWitdh; newForm.BackgroundImage = graph.ChartImg; newForm.BackgroundImageLayout = ImageLayout.Stretch; return dtOutput; //newForm.Show(); }
//public bool IsDateTimeType() public void WriteDataTableToDB(DataTable dt, string tableName) { string strTemp; int blEx; OleDbUtility oleDB = new OleDbUtility("ods", "users", "users"); oleDB.getConnection(); oleDB.OpenConnection(); string strsql = "select * from " + tableName + " where lca_line=''"; DataTable dtDB = oleDB.SQLQuery_DataTable(strsql); if (dtDB.Columns.Count == dt.Columns.Count) { for (int i = 0; i < dt.Rows.Count; i++) { strsql = "insert into " + tableName + " values ("; for (int j = 0; j < dt.Columns.Count; j++) { if (j == 0 && dt.Rows[i][j].ToString() == "") { dt.Rows[i][j] = "LCA"; } if (dt.Rows[i][j].GetType() == typeof(DateTime)) { strTemp ="to_date('"+ Convert.ToDateTime(dt.Rows[i][j]).ToString("yyyy-MM-dd HH:mm:ss")+"','yyyy-MM-dd hh24:mi:ss')"; } else { strTemp = dt.Rows[i][j].ToString(); } if (j == dt.Columns.Count - 1) { if (dt.Rows[i][j].GetType() == typeof(DateTime)) { strsql = strsql + strTemp; } else { strsql = strsql + "'" +strTemp + "'"; } } else { if (dt.Rows[i][j].GetType() == typeof(DateTime)) { strsql = strsql + strTemp + ","; } else { strsql = strsql + "'" +strTemp + "',"; } } } strsql=strsql+")"; try { blEx = oleDB.SQLExecute(strsql); } catch { break; } } } else { throw new Exception("The two tables have different columns."); } oleDB.Commit(); oleDB.destroy(); }
public void AutoCreatChartForLastDay(DateTime fromDate, DateTime endDate,DataTable dtTemp) { #region 重新初始化Chart panel2.Controls.Remove(chDailyReport1); chDailyReport1 = new Chart(); chDailyReport1.Dock = System.Windows.Forms.DockStyle.Fill; ChartArea chartArea1 = new ChartArea(); chartArea1.Name = "ChartArea1"; chDailyReport1.ChartAreas.Add(chartArea1); Legend legend1 = new Legend(); legend1.Name = "Legend1"; chDailyReport1.Legends.Add(legend1); chDailyReport1.Location = new System.Drawing.Point(0, 0); chDailyReport1.Name = "chDailyReport1"; Series series4 = new Series("Hour"); series4.ChartArea = "ChartArea1"; series4.ChartType = Dundas.Charting.WinControl.SeriesChartType.Column.ToString(); series4.CustomAttributes = "DrawingStyle=Cylinder, PointWidth=0.4"; series4.Color = System.Drawing.Color.FromArgb(((int)(((byte)(0)))), ((int)(((byte)(64)))), ((int)(((byte)(0))))); series4.Legend = "Legend1"; series4.Name = "Hour"; Series series5 = new Series("Output"); series5.ChartArea = "ChartArea1"; series5.ChartType = Dundas.Charting.WinControl.SeriesChartType.Column.ToString(); series5.CustomAttributes = "DrawingStyle=Cylinder, PointWidth=0.4"; series5.Color = System.Drawing.Color.FromArgb(((int)(((byte)(0)))), ((int)(((byte)(192)))), ((int)(((byte)(0))))); series5.Legend = "Legend1"; series5.Name = "Output"; series5.ShowLabelAsValue = true; series4.Label = "#VAL{P}"; //series5.Label = "#VAL{P}"; series4.Font = new Font("Arial",6,FontStyle.Regular); series5.Font = new Font("Arial", 6, FontStyle.Regular); chDailyReport1.ChartAreas[0].AxisY.LabelStyle.Format = "0%"; chDailyReport1.ChartAreas[0].AxisX.MajorGrid.Enabled = chDailyReport1.ChartAreas[0].AxisY.MajorGrid.Enabled = false;//不显示网格线 chDailyReport1.ChartAreas[0].AxisY.Interval = 10;//设置刻度间隔为10% //chDailyReport1.ChartAreas[0].AxisX.Interval = 1; chDailyReport1.ChartAreas[0].AxisY2.MajorGrid.Enabled = false;//不显示网格线 chDailyReport1.ChartAreas[0].AxisY2.Interval =100;//设置刻度间隔为100 chDailyReport1.ChartAreas[0].AxisY2.Maximum = 1000; chDailyReport1.ChartAreas[0].AxisX.LabelStyle.Interval = 1; series5.YAxisType = AxisType.Secondary; chDailyReport1.Series.Add(series4); chDailyReport1.Series.Add(series5); //设置Title Title titleChart = new Title("LCA U/T Chart Show"); titleChart.Font = new Font("Arial", 14); chDailyReport1.Titles.Add(titleChart); chDailyReport1.Size = new System.Drawing.Size(panel2.Width, 220); chDailyReport1.TabIndex = 5; chDailyReport1.Text = "chart1"; panel2.Controls.Add(chDailyReport1); #endregion endDate = endDate.AddDays(1); String strFromDate, strEndDate, strSql; //, strTemp, strTempEnd; strFromDate = fromDate.ToString("yyyy-MM-dd")+ " 07:00:00"; strEndDate = endDate.ToString("yyyy-MM-dd") + " 06:59:59"; //Create SQL for DataGridView strSql = "select to_char((info_date-to_dsinterval('0 07:00:00')),'yyyy-mm-dd') as Info_Date,line_status as Status,sum(status_timespan) as Status_Time ,date_type from lca_dt_period where to_char(info_date,'yyyy-mm-dd hh24:mi:ss') between '" + strFromDate + "' and '" + strEndDate + "' group by to_char((info_date-to_dsinterval('0 07:00:00')),'yyyy-mm-dd'), line_status,date_type order by info_date,line_status"; #region Create table for Daily Report LCADownTimeDataDriver dataDriver = LCADownTimeDataDriver.Instance; if (dataDriver.LCADataSet.Tables.Count > 0) { dataDriver.LCADataSet.Tables.Clear(); } DataTable dttDailyReport = new DataTable("D/T Daily Report"); dttDailyReport.Columns.Add("Date", System.Type.GetType("System.String")); dttDailyReport.Columns.Add("Status", System.Type.GetType("System.String")); dttDailyReport.Columns.Add("Status_Time", System.Type.GetType("System.Int32")); dttDailyReport.Columns.Add("DayShift", System.Type.GetType("System.String")); dataDriver.CrunchTable(strSql, "D/T Daily Report"); dgvDailyData.DataSource = dataDriver.LCADataSet.Tables["D/T Daily Report"]; #endregion //Caculate the days //TimeSpan tmSpan = endDate - fromDate; //int days = tmSpan.Days; //DateTime dtTemp = fromDate; //int intOneDayStop, intOneDayPause, intOneDayRun, intDayShiftRun, intNightShiftRun; //double perOneDayRun, perOneDayStop, perOneDayPause, perDayShiftRun, perNightShiftRun; #region Initial OLEDB OleDbUtility oleu = new OleDbUtility("ods", "users", "users"); oleu.getConnection(); oleu.OpenConnection(); #endregion strSql = "select to_char(info_date,'hh24') as hour ,status_timespan from lca_dt_period a where to_char(info_date,'yyyy-MM-dd hh24:mi:ss') between '" + strFromDate + "' and '" + strEndDate + "' and line_status='Run' order by a.info_date"; DataTable dtHourly = new DataTable("Hourly Record"); dtHourly = oleu.SQLQuery_DataTable(strSql); #region Hourly Chart int intHourRun = 0; double perHourRun; for (int i = 0; i < 24; i++) { intHourRun = Convert.ToInt32(dtHourly.Rows[i][1].ToString()); if (intHourRun != 0) { perHourRun = Math.Round((double)(intHourRun) / (double)3600, 2); chDailyReport1.Series["Hour"].Points.AddXY(dtHourly.Rows[i][0].ToString(), perHourRun * 100); chDailyReport1.Series["Output"].Points.AddXY(dtHourly.Rows[i][0].ToString(),Convert.ToInt32(dtTemp.Rows[i][0])); } } #endregion #region Daily Report /* if (days >= 0) { for (int i = 0; i < days; i++) { #region Stop Time SQL strTemp = dtTemp.ToString("yyyy-MM-dd HH:mm:ss").Substring(0, 10); strTempEnd = dtTemp.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss").Substring(0, 10); strSql = "select sum(status_timespan) as status_Time from lca_dt_period where line_status='Stop' and to_char(info_date,'yyyy-mm-dd hh24:mi:ss') between '" + strTemp + " 07:00:00" + "' and '" + strTempEnd + " 06:59:59" + "'"; DataTable dt = oleu.SQLQuery_DataTable(strSql); if (Convert.ToString(dt.Rows[0][0]) != "") { intOneDayStop = Convert.ToInt32(dt.Rows[0][0]); } else { intOneDayStop = 0; } perOneDayStop = Math.Round((double)intOneDayStop / 86400, 2); #endregion #region Pause Time SQL strSql = "select sum(status_timespan) from lca_dt_period where line_status='Pause' and to_char(info_date,'yyyy-mm-dd hh24:mi:ss') between '" + strTemp + " 07:00:00" + "' and '" + strTempEnd + " 06:59:59" + "'"; dt.Clear(); dt = oleu.SQLQuery_DataTable(strSql); if (Convert.ToString(dt.Rows[0][0]) != "") { intOneDayPause = Convert.ToInt32(dt.Rows[0][0]); } else { intOneDayPause = 0; } perOneDayPause = Math.Round((double)intOneDayPause / 86400, 2); #endregion #region Run Time SQL strSql = "select sum(status_timespan) from lca_dt_period where line_status='Run' and to_char(info_date,'yyyy-mm-dd hh24:mi:ss') between '" + strTemp + " 07:00:00" + "' and '" + strTempEnd + " 06:59:59" + "'"; dt.Clear(); dt = oleu.SQLQuery_DataTable(strSql); if (Convert.ToString(dt.Rows[0][0]) != "") { intOneDayRun = Convert.ToInt32(dt.Rows[0][0]); } else { intOneDayRun = 0; } perOneDayRun = Math.Round((double)intOneDayRun / 86400, 2); #endregion #region Day Shift Run Time SQL strSql = "select sum(status_timespan) from lca_dt_period where date_type='Day' and line_status='Run' and to_char(info_date,'yyyy-mm-dd hh24:mi:ss') between '" + strTemp + " 07:00:00" + "' and '" + strTempEnd + " 06:59:59" + "'"; dt.Clear(); dt = oleu.SQLQuery_DataTable(strSql); if (Convert.ToString(dt.Rows[0][0]) != "") { intDayShiftRun = Convert.ToInt32(dt.Rows[0][0]); } else { intDayShiftRun = 0; } perDayShiftRun = Math.Round((double)intDayShiftRun / 43200, 4); #endregion #region Night Shift Run Time SQL strSql = "select sum(status_timespan) from lca_dt_period where date_type='Night' and line_status='Run' and to_char(info_date,'yyyy-mm-dd hh24:mi:ss') between '" + strTemp + " 07:00:00" + "' and '" + strTempEnd + " 06:59:59" + "'"; dt.Clear(); dt = oleu.SQLQuery_DataTable(strSql); if (Convert.ToString(dt.Rows[0][0]) != "") { intNightShiftRun = Convert.ToInt32(dt.Rows[0][0]); } else { intNightShiftRun = 0; } perNightShiftRun = Math.Round((double)intNightShiftRun / 43200, 4); #endregion #region Hourly Run Time SQL if (perDayShiftRun != (double)0) { chDailyReport1.Series["Day"].Points.AddXY(strTemp, perDayShiftRun * 100); } if (perNightShiftRun != (double)0) { chDailyReport1.Series["Night"].Points.AddXY(strTemp, perNightShiftRun * 100); } dtTemp = dtTemp.AddDays(1); } } else { throw new Exception("End Date can't earlier than From Date!"); } #endregion */ #endregion oleu.destroy(); #region Save Log String chartPath = @"D:\tomcat\webapps\HSAPE\store\" + fromDate.ToString("yyyyMMdd") + "_" + endDate.AddDays(-1).ToString("yyyyMMdd") + "Daily Chart.Gif"; chDailyReport1.SaveAsImage(chartPath, System.Drawing.Imaging.ImageFormat.Gif); String fileName = "DailyData " + fromDate.ToString("yyyyMMdd") + "-" + endDate.ToString("yyyyMMdd"); DataTable dtNew = new DataTable(); dtNew = FormLCADowntTime.Instance.WriteDataGridToTable(dgvDailyData, "HourlyData"); dataDriver.WriteDataToFile(dtNew, fileName); #endregion }
public void btCreateChart_Click(object sender, EventArgs e) { #region 重新初始化Chart panel2.Controls.Remove(chDailyReport1); chDailyReport1 = new Chart(); chDailyReport1.Dock = System.Windows.Forms.DockStyle.Fill; ChartArea chartArea1 = new ChartArea(); chartArea1.Name = "ChartArea1"; chDailyReport1.ChartAreas.Add(chartArea1); Legend legend1 = new Legend(); legend1.Name = "Legend1"; chDailyReport1.Legends.Add(legend1); chDailyReport1.Location = new System.Drawing.Point(0, 0); chDailyReport1.Name = "chDailyReport1"; #region series1~3 /* Series series1 = new Series("STOP"); series1.ChartArea = "ChartArea1"; series1.ChartType=Dundas.Charting.WinControl.SeriesChartType.StackedColumn.ToString(); //series1.Color // series1.CustomProperties = "DrawingStyle=Cylinder, PointWidth=0.5, LabelStyle=Center"; series1.Legend = "Legend1"; series1.Name = "STOP"; Series series2 = new Series("PAUSE"); series2.ChartArea = "ChartArea1"; series2.ChartType =Dundas.Charting.WinControl.SeriesChartType.StackedColumn.ToString();; series2.Color = System.Drawing.Color.FromArgb(((int)(((byte)(192)))), ((int)(((byte)(192)))), ((int)(((byte)(0))))); //series2.CustomProperties = "DrawingStyle=Cylinder, PointWidth=0.5, LabelStyle=Center"; series2.Legend = "Legend1"; series2.Name = "PAUSE"; Series series3 = new Series("RUN"); series3.ChartArea = "ChartArea1"; series3.ChartType = Dundas.Charting.WinControl.SeriesChartType.StackedColumn.ToString(); //series1.CustomProperties = "DrawingStyle=Wedge"; //series3.CustomProperties = "DrawingStyle=Cylinder, PointWidth=0.5, LabelStyle=Center"; series3.Color = System.Drawing.Color.Green; series3.Legend = "Legend1"; series3.Name = "RUN"; */ #endregion Series series4 = new Series("Day"); series4.ChartArea = "ChartArea1"; series4.ChartType = Dundas.Charting.WinControl.SeriesChartType.Column.ToString(); series4.CustomAttributes = "DrawingStyle=Cylinder, PointWidth=0.7"; series4.Color = System.Drawing.Color.FromArgb(((int)(((byte)(0)))), ((int)(((byte)(64)))), ((int)(((byte)(0))))); series4.Legend = "Legend1"; series4.Name = "Day"; Series series5 = new Series("Night"); series5.ChartArea = "ChartArea1"; series5.ChartType = Dundas.Charting.WinControl.SeriesChartType.Column.ToString(); series5.CustomAttributes = "DrawingStyle=Cylinder, PointWidth=0.7"; series5.Color = System.Drawing.Color.FromArgb(((int)(((byte)(0)))), ((int)(((byte)(192)))), ((int)(((byte)(0))))); series5.Legend = "Legend1"; series5.Name = "Night"; chDailyReport1.Series.Add(series4); chDailyReport1.Series.Add(series5); series4.Label = "#VAL{P}"; series5.Label = "#VAL{P}"; chDailyReport1.ChartAreas[0].AxisY.LabelStyle.Format="0%"; chDailyReport1.ChartAreas[0].AxisX.MajorGrid.Enabled = chDailyReport1.ChartAreas[0].AxisY.MajorGrid.Enabled = false;//不显示网格线 chDailyReport1.ChartAreas[0].AxisY.Interval = 10;//设置刻度间隔为10% chDailyReport1.ChartAreas[0].AxisX.LabelStyle.Interval = 1; chDailyReport1.ChartAreas[0].AxisX.LabelStyle.FontAngle = 90; //设置Title Title titleChart = new Title("LCA U/T Chart Show"); titleChart.Font = new Font("Arial",14); chDailyReport1.Titles.Add(titleChart); chDailyReport1.Size = new System.Drawing.Size(panel2.Width, 220); chDailyReport1.TabIndex = 5; chDailyReport1.Text = "chart1"; panel2.Controls.Add(chDailyReport1); #endregion dtFromDate = Convert.ToDateTime(dpFromDate.Text); dtEndDate = Convert.ToDateTime(dpEndDate.Text); dtEndDate = dtEndDate.AddDays(1); #region Query Data From DB Between From To End String strFromDate, strEndDate, strSql, strTemp, strTempEnd; strFromDate = dtFromDate.ToString("yyyy-MM-dd HH:mm:ss").Substring(0, 10) + " 07:00:00"; strEndDate = dtEndDate.ToString("yyyy-MM-dd HH:mm:ss").Substring(0, 10) + " 06:59:59"; //Create SQL for DataGridView strSql = "select to_char((info_date-to_dsinterval('0 07:00:00')),'yyyy-mm-dd') as Info_Date,line_status as Status,sum(status_timespan) as Status_Time ,date_type from lca_dt_period where to_char(info_date,'yyyy-mm-dd hh24:mi:ss') between '" + strFromDate + "' and '" + strEndDate + "' and lca_line='" + FormLCADowntTime.Instance.GetLCALine() +"' group by to_char((info_date-to_dsinterval('0 07:00:00')),'yyyy-mm-dd'), line_status,date_type order by info_date,line_status"; #region Create table for Daily Report LCADownTimeDataDriver dataDriver = LCADownTimeDataDriver.Instance; if (dataDriver.LCADataSet.Tables.Count > 0) { dataDriver.LCADataSet.Tables.Clear(); } DataTable dttDailyReport = new DataTable("D/T Daily Report"); dttDailyReport.Columns.Add("Date", System.Type.GetType("System.String")); dttDailyReport.Columns.Add("Status", System.Type.GetType("System.String")); dttDailyReport.Columns.Add("Status_Time", System.Type.GetType("System.Int32")); dttDailyReport.Columns.Add("DayShift", System.Type.GetType("System.String")); dataDriver.CrunchTable(strSql, "D/T Daily Report"); dgvDailyData.DataSource = dataDriver.LCADataSet.Tables["D/T Daily Report"]; #endregion //Caculate the days TimeSpan tmSpan = dtEndDate - dtFromDate; int days = tmSpan.Days; DateTime dtTemp = dtFromDate; int intOneDayStop, intOneDayPause,intOneDayRun,intDayShiftRun,intNightShiftRun; double perOneDayRun,perOneDayStop,perOneDayPause,perDayShiftRun,perNightShiftRun; #region Initial OLEDB OleDbUtility oleu = new OleDbUtility("ods", "users", "users"); oleu.getConnection(); oleu.OpenConnection(); #endregion if (days >= 0) { for (int i = 0; i < days ; i++) { #region Stop Time SQL strTemp = dtTemp.ToString("yyyy-MM-dd HH:mm:ss").Substring(0, 10); strTempEnd = dtTemp.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss").Substring(0, 10); strSql = "select sum(status_timespan) as status_Time from lca_dt_period where line_status='Stop' and to_char(info_date,'yyyy-mm-dd hh24:mi:ss') between '" + strTemp + " 07:00:00" + "' and '" + strTempEnd + " 06:59:59" + "'"; DataTable dt = oleu.SQLQuery_DataTable(strSql); if (Convert.ToString(dt.Rows[0][0]) != "") { intOneDayStop = Convert.ToInt32(dt.Rows[0][0]); } else { intOneDayStop = 0; } perOneDayStop = Math.Round((double)intOneDayStop/86400,2); #endregion #region Pause Time SQL strSql = "select sum(status_timespan) from lca_dt_period where line_status='Pause' and to_char(info_date,'yyyy-mm-dd hh24:mi:ss') between '" + strTemp + " 07:00:00" + "' and '" + strTempEnd + " 06:59:59" + "'"; dt.Clear(); dt = oleu.SQLQuery_DataTable(strSql); if (Convert.ToString(dt.Rows[0][0]) != "") { intOneDayPause = Convert.ToInt32(dt.Rows[0][0]); } else { intOneDayPause = 0; } perOneDayPause = Math.Round((double)intOneDayPause / 86400, 2); #endregion #region Run Time SQL strSql = "select sum(status_timespan) from lca_dt_period where line_status='Run' and to_char(info_date,'yyyy-mm-dd hh24:mi:ss') between '" + strTemp + " 07:00:00" + "' and '" + strTempEnd + " 06:59:59" + "'"; dt.Clear(); dt = oleu.SQLQuery_DataTable(strSql); if (Convert.ToString(dt.Rows[0][0]) != "") { intOneDayRun = Convert.ToInt32(dt.Rows[0][0]); } else { intOneDayRun = 0; } perOneDayRun = Math.Round((double)intOneDayRun / 86400, 2); #endregion #region Day Shift Run Time SQL strSql = "select sum(status_timespan) from lca_dt_period where date_type='Day' and line_status='Run' and to_char(info_date,'yyyy-mm-dd hh24:mi:ss') between '" + strTemp + " 07:00:00" + "' and '" + strTempEnd + " 06:59:59" + "'"; dt.Clear(); dt = oleu.SQLQuery_DataTable(strSql); if (Convert.ToString(dt.Rows[0][0]) != "") { intDayShiftRun = Convert.ToInt32(dt.Rows[0][0]); } else { intDayShiftRun = 0; } perDayShiftRun = Math.Round((double)intDayShiftRun / 43200, 4); #endregion #region Night Shift Run Time SQL strSql = "select sum(status_timespan) from lca_dt_period where date_type='Night' and line_status='Run' and to_char(info_date,'yyyy-mm-dd hh24:mi:ss') between '" + strTemp + " 07:00:00" + "' and '" + strTempEnd + " 06:59:59" + "'"; dt.Clear(); dt = oleu.SQLQuery_DataTable(strSql); if (Convert.ToString(dt.Rows[0][0]) != "") { intNightShiftRun = Convert.ToInt32(dt.Rows[0][0]); } else { intNightShiftRun = 0; } perNightShiftRun = Math.Round((double)intNightShiftRun / 43200, 4); #endregion //chDailyReport1.Series["STOP"].Points.AddXY(strTemp, perOneDayStop); //chDailyReport1.Series["PAUSE"].Points.AddXY(strTemp, intOneDayPause); //chDailyReport1.Series["RUN"].Points.AddXY(strTemp, perOneDayRun); if (perDayShiftRun != (double)0) { chDailyReport1.Series["Day"].Points.AddXY(strTemp, perDayShiftRun * 100); } if(perNightShiftRun!=(double)0) { chDailyReport1.Series["Night"].Points.AddXY(strTemp, perNightShiftRun*100); } dtTemp = dtTemp.AddDays(1); } } else { throw new Exception("End Date can't earlier than From Date!"); } #endregion oleu.destroy(); #region Save Log String chartPath = @"C:\LCA Data\" + dtFromDate.ToString("yyyyMMdd") + "_" + dtEndDate.AddDays(-1).ToString("yyyyMMdd") + "Daily Chart.Gif"; chDailyReport1.SaveAsImage(chartPath, System.Drawing.Imaging.ImageFormat.Gif); String fileName = "DailyData " + dtFromDate.ToString("yyyyMMdd") + "-" + dtEndDate.ToString("yyyyMMdd"); DataTable dtNew = new DataTable(); dtNew=FormLCADowntTime.Instance.WriteDataGridToTable(dgvDailyData, "HourlyData"); dataDriver.WriteDataToFile(dtNew, fileName); #endregion #region Send Email //SmtpClient _smtp = new SmtpClient("mailhost.woo.sing.seagate.com"); //MailMessage message = new MailMessage("*****@*****.**", "*****@*****.**"); //message.Body = "This is a test e-mail message sent by an application. "; //string someArrows = new string(new char[] { '\u2190', '\u2191', '\u2192', '\u2193' }); //message.Body += Environment.NewLine + someArrows; //message.BodyEncoding = System.Text.Encoding.UTF8; //message.Subject = "test message 1" + someArrows; //message.SubjectEncoding = System.Text.Encoding.UTF8; //_smtp.Send(message); #endregion }