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
}
