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