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