123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789 |
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Text;
- using System.Data.SqlClient;
- using System.Data;
- using System.IO;
- using System.Collections.Concurrent;
- using System.Diagnostics;
- namespace BEMSDataGateway
- {
- static class Table
- {
- public const string RawT = "BemsMonitoringPointHistory15minRawData";
- public const string FiFt = "BemsMonitoringPointHistory15min";
- public const string Hourly = "BemsMonitoringPointHistoryHourly";
- public const string Daily = "BemsMonitoringPointHistoryDaily";
- }
- class My_DB_DML
- {
- public string ConnDBInfo() // DB접속 정보를 읽는 함수
- {
- Readini Ri = new Readini();
- Ri.ReadData();
- string strconInfo = @"Data Source = " + Ri.UserIP + "," + Ri.UserPort + "; "
- + "Initial Catalog =" + Ri.UserDB + "; "
- + "Persist Security Info=True; "
- + "User ID =" + Ri.UserID + "; "
- + "Password =" + Ri.UserPW + "";
- return strconInfo;
- }
- private StringBuilder sb_delete = new StringBuilder();
- public void DB_Min_Delete(IDictionary<string, float[]> MyDic, int NowMin)
- {
- LogFileCreate LFC = new LogFileCreate();
- int tmpNowMin = 0;
- string InsertDate1 = "";
- if (NowMin == 00)
- {
- tmpNowMin = 45;
- InsertDate1 = DateTime.Now.AddHours(-1).ToString("yyyy-MM-dd HH:" + tmpNowMin + ":00.000");
- }
- else
- {
- tmpNowMin = NowMin - 15;
- InsertDate1 = DateTime.Now.ToString("yyyy-MM-dd HH:" + tmpNowMin + ":00.000");
- }
- string query = "";
- string[] TmpStringValue = new string[8];
- string strcon = ConnDBInfo();
- using (SqlConnection conn = new SqlConnection(strcon))
- {
- conn.Open();
- foreach (KeyValuePair<string, float[]> kv in MyDic)
- {
- /// Key값과 Value값을 담기 위한 TKey, TValue
- string TmpPoint = kv.Key;
- float[] TmpValue = kv.Value;
- if (TmpValue[7] != 7 || TmpValue[7] != 3) // 현재값과 RAW 아니면 삭제해야함
- {
- sb_delete.Append("DELETE FROM ");
- sb_delete.Append(Table.FiFt);
- sb_delete.Append(" WHERE SiteId = ");
- sb_delete.Append(TmpValue[2]);
- sb_delete.Append(" AND FacilityTypeId = ");
- sb_delete.Append(TmpValue[3]);
- sb_delete.Append(" AND FacilityCode = ");
- sb_delete.Append(TmpValue[4]);
- sb_delete.Append(" AND PropertyId = ");
- sb_delete.Append(TmpValue[5]);
- sb_delete.Append(" AND CreatedDateTime = '" + InsertDate1 + "'");
- query = sb_delete.ToString();
- SqlCommand cmd = new SqlCommand(query, conn);
- cmd.ExecuteNonQuery();
- cmd.Dispose();
- }
- }
- conn.Close();
- conn.Dispose();
- }
- sb_delete.Clear();
- }
- public void DB_Min_Insert(IDictionary<string, float[]> MyDic, int NowMin)
- {
- LogFileCreate LFC = new LogFileCreate();
- int tmpNowMin = 0;
- string InsertDate1 = "";
- if (NowMin == 00)
- {
- tmpNowMin = 45;
- InsertDate1 = DateTime.Now.AddHours(-1).ToString("yyyy-MM-dd HH:" + tmpNowMin + ":00.000");
- }
- else
- {
- tmpNowMin = NowMin - 15;
- InsertDate1 = DateTime.Now.ToString("yyyy-MM-dd HH:" + tmpNowMin + ":00.000");
- }
- string query = "";
- string InsertDate2 = DateTime.Now.ToString("yyyy-MM-dd HH:" + NowMin + ":00.000");
- string[] TmpStringValue = new string[8];
- float InsertValue = 0;
- string strcon = ConnDBInfo();
- try
- {
- using (SqlConnection conn = new SqlConnection(strcon))
- {
- foreach (KeyValuePair<string, float[]> kv in MyDic)
- {
- /// Key값과 Value값을 담기 위한 TKey, TValue
- string TmpPoint = kv.Key;
- float[] TmpValue = kv.Value;
- if (TmpValue[7] != 7)
- {
- conn.Open();
- switch (NowMin)
- {
- case 15:
- InsertValue = TmpValue[8];
- break;
- case 30:
- InsertValue = TmpValue[9];
- break;
- case 45:
- InsertValue = TmpValue[10];
- break;
- case 00:
- InsertValue = TmpValue[11];
- break;
- }
- if (TmpValue[7] == 2 && InsertValue < 0) //적산값인데 0보다 작으면...
- {
- InsertValue = 0;
- }
- if (TmpValue[7] == 3) // 현재값
- {
- query = "INSERT INTO " + Table.FiFt + "(SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,CurrentValue) "
- + "VALUES(" + TmpValue[2] + "," + TmpValue[3] + "," + TmpValue[4] + "," + TmpValue[5] + ",'" + InsertDate2 + "'," + Math.Round(InsertValue, 4, MidpointRounding.AwayFromZero) + ")";
- }
- else
- {
- query = "INSERT INTO " + Table.FiFt + "(SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,CurrentValue) "
- + "VALUES(" + TmpValue[2] + "," + TmpValue[3] + "," + TmpValue[4] + "," + TmpValue[5] + ",'" + InsertDate1 + "'," + Math.Round(InsertValue, 4, MidpointRounding.AwayFromZero) + ")";
- }
- if (InsertDate2 == DateTime.Now.ToString("yyyy-MM-dd 00:15:00.000") && TmpValue[7] != 3)
- {
- query += "INSERT INTO " + Table.Daily + "(SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,DailyValue,MaxValue,MinValue) "
- + "VALUES(" + TmpValue[2] + "," + TmpValue[3] + "," + TmpValue[4] + "," + TmpValue[5] + ",'" + Convert.ToDateTime(InsertDate2).ToString("yyyy-MM-dd 00:00:00.000") + "'," + Math.Round(InsertValue, 4, MidpointRounding.AwayFromZero) + "," + Math.Round(InsertValue, 4, MidpointRounding.AwayFromZero) + "," + Math.Round(InsertValue, 4, MidpointRounding.AwayFromZero) + ")";
- }
- using (SqlCommand cmd = new SqlCommand(query, conn))
- {
- cmd.ExecuteNonQuery();
- cmd.Dispose();
- conn.Close();
- }
- }
- }
- conn.Dispose();
- }
- }
- catch (SqlException e)
- {
- if (Form1.Enable_EnglishMode)//영어
- {
- LFC.Log("[DBinsert.cs] DB_Min_Insert Error : " + e + Environment.NewLine + Environment.NewLine);
- LFC.Log("[DBinsert.cs] DB_Min_Insert Error query: " + query);
- }
- else//한글
- {
- LFC.Log("[DBinsert.cs] DB_Min_Insert 에러 : " + e + Environment.NewLine + Environment.NewLine);
- LFC.Log("[DBinsert.cs] DB_Min_Insert 에러 쿼리문: " + query);
- }
- }
- }
- public void DB_Min_Insert_Now(IDictionary<string, float[]> MyDic, int NowMin)
- {
- LogFileCreate LFC = new LogFileCreate();
- string query = "";
- string InsertDate2 = DateTime.Now.ToString("yyyy-MM-dd HH:" + NowMin + ":00.000");
- string[] TmpStringValue = new string[8];
- string strcon = ConnDBInfo();
- float InsertValue = 0;
- using (SqlConnection conn = new SqlConnection(strcon))
- {
- foreach (KeyValuePair<string, float[]> kv in MyDic)
- {
- /// Key값과 Value값을 담기 위한 TKey, TValue
- string TmpPoint = kv.Key;
- float[] TmpValue = kv.Value;
- if (TmpValue[7] != 7)
- {
- if (TmpValue[7] == 2 || TmpValue[7] == 5) //적산이거나 적산2인 경우.
- {
- InsertValue = 0;
- }
- else if (TmpValue[7] == 6) // 현재값2인 경우
- {
- if (TmpValue[13] > 0)
- {
- InsertValue = 1;
- }
- else
- InsertValue = 0;
- }
- else
- {
- InsertValue = TmpValue[13];
- }
- conn.Open();
- query = "INSERT INTO " + Table.FiFt + "(SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,CurrentValue) "
- + "VALUES(" + TmpValue[2] + "," + TmpValue[3] + "," + TmpValue[4] + "," + TmpValue[5] + ",'" + InsertDate2 + "'," + Math.Round(InsertValue, 4, MidpointRounding.AwayFromZero) + ")";
- SqlCommand cmd = new SqlCommand(query, conn);
- cmd.ExecuteNonQuery();
- cmd.Dispose();
- conn.Close();
- }
- }
- conn.Dispose();
- }
- }
- public void DB_Hour_Update(IDictionary<string, float[]> MyDic, int NowMin, string Hour)
- {
- string query = "";
- string ConnInfo = "";
- string strcon = ConnDBInfo();
- string HourDate = "";
- string NowHourDate = "";
- if (NowMin == 0) //XX시 00분일때는 Hourly의 값이 XX-1에 들어가야함.
- {
- HourDate = Convert.ToDateTime(Hour).AddHours(-1).ToString("yyyy-MM-dd HH:00:00.000");
- }
- else
- {
- HourDate = Hour; // 그렇지 않다면 그냥 HOur
- }
- My_DB_DML MDD = new My_DB_DML();
- ReturnValue RV = new ReturnValue();
- ConnInfo = MDD.ConnDBInfo(); // DB접속정보를 담고 있음.
- using (SqlConnection conn = new SqlConnection(strcon))
- {
- foreach (KeyValuePair<string, float[]> kv in MyDic)
- {
- /// Key값과 Value값을 담기 위한 TKey, TValue
- string TmpPoint = kv.Key;
- float[] TmpValue = kv.Value;
- if (TmpValue[7] != 7) // 7이란 값 변경에 대한것을 의미..
- {
- conn.Open(); // 접속
- if (TmpValue[7] == 2 && TmpValue[12] < 0) //적산값인데 0보다 작으면...
- {
- TmpValue[12] = 0;
- }
- if (NowMin == 15) // XX시 15분이라면. 무조건 Insert.
- {
- query = "INSERT INTO " + Table.Hourly + "(SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,CurrentValue)"
- + " VALUES(" + TmpValue[2] + ", "
- + "" + TmpValue[3] + ", "
- + "" + TmpValue[4] + ", "
- + "" + TmpValue[5] + ", "
- + "'" + HourDate + "', "
- + "" + Math.Round(TmpValue[12], 4, MidpointRounding.AwayFromZero) + ")";
- }
- else // 그렇지 않다면 Update
- {
- if (TmpValue[7] == 3 && NowMin == 0) //현재값이라면..
- {
- NowHourDate = Convert.ToDateTime(Hour).ToString("yyyy-MM-dd HH:00:00.000");
- query = "INSERT INTO " + Table.Hourly + "(SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,CurrentValue)"
- + " VALUES(" + TmpValue[2] + ", "
- + "" + TmpValue[3] + ", "
- + "" + TmpValue[4] + ", "
- + "" + TmpValue[5] + ", "
- + "'" + NowHourDate + "', "
- + "" + Math.Round(TmpValue[12], 4, MidpointRounding.AwayFromZero) + ")";
- }
- else
- {
- query = "UPDATE " + Table.Hourly + " SET CurrentValue = " + Math.Round(TmpValue[12], 4, MidpointRounding.AwayFromZero) + " WHERE "
- + "SiteId = " + TmpValue[2] + " "
- + "AND FacilityTypeId = " + TmpValue[3] + " "
- + "AND FacilityCode = " + TmpValue[4] + " "
- + "AND PropertyId = " + TmpValue[5] + " "
- + "AND CreatedDateTime = '" + HourDate + "'";
- }
- }
- SqlCommand cmd = new SqlCommand(query, conn);
- cmd.ExecuteNonQuery();
- cmd.Dispose();
- conn.Close();
- }
- }
- conn.Dispose();
- }
- }
- #region StringBuilder
- private StringBuilder Up_Avg_sb = new StringBuilder();
- private StringBuilder Tmp1_sb = new StringBuilder();
- private StringBuilder Up_Sum_sb = new StringBuilder();
- private StringBuilder Tmp2_sb = new StringBuilder();
- private StringBuilder Up_Sum2_sb = new StringBuilder();
- private StringBuilder Tmp3_sb = new StringBuilder();
- private StringBuilder Up_On_sb = new StringBuilder();
- private StringBuilder Tmp4_sb = new StringBuilder();
- private StringBuilder Tmp5_sb = new StringBuilder();
- private StringBuilder Tmp6_sb = new StringBuilder();
- private StringBuilder Tmp7_sb = new StringBuilder();
- private StringBuilder Tmp8_sb = new StringBuilder();
- private StringBuilder Tmp9_sb = new StringBuilder();
- private StringBuilder Tmp10_sb = new StringBuilder();
- #endregion
- public void DB_Day_Update(IDictionary<string, float[]> MyDic, int NowMin, string Hour)
- {
- string ConnInfo = "";
- string strcon = ConnDBInfo();
- string DailyDate = "";
- string Max_DailyDate = "";
- string Min_DailyDate = "";
- float[] SelectValue = new float[2];
- if (NowMin == 00 && (Convert.ToDateTime(Hour).ToString("HH") == "00"))
- {
- DailyDate = Convert.ToDateTime(Hour).AddDays(-1).ToString("yyyy-MM-dd 00:00:00.000");
- LogFileCreate LFC = new LogFileCreate();
- if (Form1.Enable_EnglishMode)//영어
- {
- LFC.Log("[DBinsert.cs] DailyDate at 00:00= " + DailyDate + Environment.NewLine);
- }
- else//한글
- {
- LFC.Log("[DBinsert.cs] 00시00분 일때의 DailyDate = " + DailyDate + Environment.NewLine);
- }
- Max_DailyDate = Convert.ToDateTime(Hour).AddDays(-1).ToString("yyyy-MM-dd 00:00:00.000");
- Min_DailyDate = Convert.ToDateTime(Hour).AddDays(-1).ToString("yyyy-MM-dd 00:00:00.000");
- }
- else
- {
- DailyDate = Convert.ToDateTime(Hour).ToString("yyyy-MM-dd 00:00:00.000");
- Max_DailyDate = Convert.ToDateTime(Hour).ToString("yyyy-MM-dd 00:00:00.000");
- Min_DailyDate = Convert.ToDateTime(Hour).ToString("yyyy-MM-dd 00:00:00.000");
- }
- My_DB_DML MDD = new My_DB_DML();
- ReturnValue RV = new ReturnValue();
- ConnInfo = MDD.ConnDBInfo(); // DB접속정보를 담고 있음.
- using (SqlConnection conn = new SqlConnection(strcon))
- {
- foreach (KeyValuePair<string, float[]> kv in MyDic)
- {
- object o = 0;
- object o2 = 0;
- object o3 = 0;
- object o4 = 0;
- string query = "";
- string returnquery = "";
- float InsertDailyValue = 0;
- float AvgDailyValue = 0;
- float SumDailyValue = 0;
- float Sum2DailyValue = 0;
- float OnDailyValue = 0;
- /// Key값과 Value값을 담기 위한 TKey, TValue
- string TmpPoint = kv.Key;
- float[] TmpValue = kv.Value;
- if (TmpValue[7] != 3)
- {
- switch (NowMin)
- {
- case 15:
- InsertDailyValue = (TmpValue[8]);
- break;
- case 30:
- InsertDailyValue = (TmpValue[9]);
- break;
- case 45:
- InsertDailyValue = (TmpValue[10]);
- break;
- case 00:
- InsertDailyValue = (TmpValue[11]);
- break;
- }
- }
- else // 3일때.
- {
- InsertDailyValue = TmpValue[12];
- }
- if (TmpValue[7] == 2 && InsertDailyValue < 0) //적산값인데 0보다 작으면...
- {
- InsertDailyValue = 0;
- }
- conn.Open();
- switch (Convert.ToInt32(TmpValue[7]))
- {
- case 0:
- return;
- case 1:
- Up_Avg_sb.Append("SELECT AVG(CurrentValue) FROM BemsMonitoringPointHistory15min ");
- Up_Avg_sb.Append("WHERE CreatedDateTime BETWEEN '" + Convert.ToDateTime(DailyDate).ToString("yyyy-MM-dd 00:00:00") + "'");
- Up_Avg_sb.Append("AND '" + Convert.ToDateTime(DailyDate).ToString("yyyy-MM-dd 23:45:00") + "' ");
- Up_Avg_sb.Append("AND SiteId = " + TmpValue[2] + " ");
- Up_Avg_sb.Append("AND FacilityTypeId = " + TmpValue[3] + " ");
- Up_Avg_sb.Append("AND FacilityCode = " + TmpValue[4] + " ");
- Up_Avg_sb.Append("AND PropertyId = " + TmpValue[5] + " ");
- returnquery = Up_Avg_sb.ToString();
- SqlCommand cmd = new SqlCommand(returnquery, conn);
- o = cmd.ExecuteScalar();
- cmd.Dispose();
- Up_Avg_sb.Clear();
- if (o.GetType() != typeof(DBNull))
- {
- AvgDailyValue = (float)Convert.ToDouble(o);
- }
- else
- {
- return;
- }
- Tmp1_sb.Append("UPDATE ");
- Tmp1_sb.Append(Table.Daily);
- Tmp1_sb.Append(" SET DailyValue = ");
- Tmp1_sb.Append(Math.Round(AvgDailyValue, 4, MidpointRounding.AwayFromZero));
- Tmp1_sb.Append(" WHERE ");
- query = Tmp1_sb.ToString();
- Tmp1_sb.Clear();
- break;
- case 2:
- Up_Sum_sb.Append("SELECT SUM(CurrentValue) FROM BemsMonitoringPointHistory15min ");
- Up_Sum_sb.Append("WHERE CreatedDateTime BETWEEN '" + Convert.ToDateTime(DailyDate).ToString("yyyy-MM-dd 00:00:00") + "'");
- Up_Sum_sb.Append("AND '" + Convert.ToDateTime(DailyDate).ToString("yyyy-MM-dd 23:45:00") + "' ");
- Up_Sum_sb.Append("AND SiteId = " + TmpValue[2] + " ");
- Up_Sum_sb.Append("AND FacilityTypeId = " + TmpValue[3] + " ");
- Up_Sum_sb.Append("AND FacilityCode = " + TmpValue[4] + " ");
- Up_Sum_sb.Append("AND PropertyId = " + TmpValue[5] + " ");
- returnquery = Up_Sum_sb.ToString();
- SqlCommand cmd2 = new SqlCommand(returnquery, conn);
- o2 = cmd2.ExecuteScalar();
- cmd2.Dispose();
- Up_Sum_sb.Clear();
- if (o2.GetType() != typeof(DBNull))
- {
- SumDailyValue = (float)Convert.ToDouble(o2);
- }
- else
- {
- return;
- }
- Tmp2_sb.Append("UPDATE ");
- Tmp2_sb.Append(Table.Daily);
- Tmp2_sb.Append(" SET DailyValue = ");
- Tmp2_sb.Append(Math.Round(SumDailyValue, 4, MidpointRounding.AwayFromZero));
- Tmp2_sb.Append(" WHERE ");
- query = Tmp2_sb.ToString();
- Tmp2_sb.Clear();
- break;
- case 3:
- if (NowMin == 00 && (Convert.ToDateTime(Hour).ToString("HH") == "00"))
- {
- string tmpDailyDate = Convert.ToDateTime(Hour).ToString("yyyy-MM-dd 00:00:00.000");
- Tmp3_sb.Append("INSERT INTO ");
- Tmp3_sb.Append(Table.Daily);
- Tmp3_sb.Append("(SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,DailyValue,MaxValue,MinValue)");
- Tmp3_sb.Append(" VALUES(" + TmpValue[2] + ", " + TmpValue[3] + ", " + TmpValue[4] + ", " + TmpValue[5] + ", '" + tmpDailyDate + "', " + Math.Round(TmpValue[12], 4, MidpointRounding.AwayFromZero) + ", " + Math.Round(TmpValue[12], 4, MidpointRounding.AwayFromZero) + ", " + Math.Round(TmpValue[12], 4, MidpointRounding.AwayFromZero) + ")");
- query = Tmp3_sb.ToString();
- Tmp3_sb.Clear();
- }
- break;
- case 4:
- Up_On_sb.Append("SELECT SUM(CurrentValue) FROM BemsMonitoringPointHistory15min ");
- Up_On_sb.Append("WHERE CreatedDateTime BETWEEN '" + Convert.ToDateTime(DailyDate).ToString("yyyy-MM-dd 00:00:00") + "'");
- Up_On_sb.Append("AND '" + Convert.ToDateTime(DailyDate).ToString("yyyy-MM-dd 23:45:00") + "' ");
- Up_On_sb.Append("AND SiteId = " + TmpValue[2] + " ");
- Up_On_sb.Append("AND FacilityTypeId = " + TmpValue[3] + " ");
- Up_On_sb.Append("AND FacilityCode = " + TmpValue[4] + " ");
- Up_On_sb.Append("AND PropertyId = " + TmpValue[5] + " ");
- returnquery = Up_On_sb.ToString();
- SqlCommand cmd3 = new SqlCommand(returnquery, conn);
- o3 = cmd3.ExecuteScalar();
- cmd3.Dispose();
- Up_On_sb.Clear();
- if (o3.GetType() != typeof(DBNull))
- {
- OnDailyValue = (float)Convert.ToDouble(o3);
- }
- else
- {
- return;
- }
- Tmp4_sb.Append("UPDATE ");
- Tmp4_sb.Append(Table.Daily);
- Tmp4_sb.Append(" SET DailyValue = ");
- Tmp4_sb.Append(Math.Round(OnDailyValue, 4, MidpointRounding.AwayFromZero));
- Tmp4_sb.Append(" WHERE ");
- query = Tmp4_sb.ToString();
- Tmp4_sb.Clear();
- break;
- case 5:
- Up_Sum2_sb.Append("SELECT SUM(CurrentValue) FROM BemsMonitoringPointHistory15min ");
- Up_Sum2_sb.Append("WHERE CreatedDateTime BETWEEN '" + Convert.ToDateTime(DailyDate).ToString("yyyy-MM-dd 00:00:00") + "'");
- Up_Sum2_sb.Append("AND '" + Convert.ToDateTime(DailyDate).ToString("yyyy-MM-dd 23:45:00") + "' ");
- Up_Sum2_sb.Append("AND SiteId = " + TmpValue[2] + " ");
- Up_Sum2_sb.Append("AND FacilityTypeId = " + TmpValue[3] + " ");
- Up_Sum2_sb.Append("AND FacilityCode = " + TmpValue[4] + " ");
- Up_Sum2_sb.Append("AND PropertyId = " + TmpValue[5] + " ");
- returnquery = Up_Sum2_sb.ToString();
- SqlCommand cmd4 = new SqlCommand(returnquery, conn);
- o4 = cmd4.ExecuteScalar();
- cmd4.Dispose();
- Up_Sum2_sb.Clear();
- if (o4.GetType() != typeof(DBNull))
- {
- Sum2DailyValue = (float)Convert.ToDouble(o4);
- }
- else
- {
- return;
- }
- Tmp5_sb.Append("UPDATE ");
- Tmp5_sb.Append(Table.Daily);
- Tmp5_sb.Append(" SET DailyValue = ");
- Tmp5_sb.Append(Math.Round(Sum2DailyValue, 4, MidpointRounding.AwayFromZero));
- Tmp5_sb.Append(" WHERE ");
- query = Tmp5_sb.ToString();
- Tmp5_sb.Clear();
- break;
- case 6:
- Tmp6_sb.Append("UPDATE ");
- Tmp6_sb.Append(Table.Daily);
- Tmp6_sb.Append(" SET DailyValue = ");
- Tmp6_sb.Append(Math.Round(InsertDailyValue, 4, MidpointRounding.AwayFromZero));
- Tmp6_sb.Append(" WHERE ");
- query = Tmp6_sb.ToString();
- Tmp6_sb.Clear();
- break;
- case 7:
- break;
- case 8:
- break;
- }
- if (TmpValue[7] != 7)
- {
- if (TmpValue[7] != 3)
- {
- Tmp7_sb.Append("SiteId = ");
- Tmp7_sb.Append(TmpValue[2]);
- Tmp7_sb.Append(" AND FacilityTypeId = ");
- Tmp7_sb.Append(TmpValue[3]);
- Tmp7_sb.Append(" AND FacilityCode = ");
- Tmp7_sb.Append(TmpValue[4]);
- Tmp7_sb.Append(" AND PropertyId = ");
- Tmp7_sb.Append(TmpValue[5]);
- Tmp7_sb.Append(" AND CreatedDateTime = '" + DailyDate + "'");
- query += Tmp7_sb.ToString();
- Tmp7_sb.Clear();
- }
- Tmp8_sb.Append(" UPDATE ");
- Tmp8_sb.Append(Table.Daily);
- Tmp8_sb.Append(" SET MaxValue = CASE WHEN MaxValue <= ");
- Tmp8_sb.Append(Math.Round(InsertDailyValue, 4, MidpointRounding.AwayFromZero));
- Tmp8_sb.Append(" THEN ");
- Tmp8_sb.Append(Math.Round(InsertDailyValue, 4, MidpointRounding.AwayFromZero));
- Tmp8_sb.Append(" WHEN MaxValue >= ");
- Tmp8_sb.Append(Math.Round(InsertDailyValue, 4, MidpointRounding.AwayFromZero));
- Tmp8_sb.Append(" THEN MaxValue END ");
- Tmp8_sb.Append("WHERE SiteId = ");
- Tmp8_sb.Append(TmpValue[2]);
- Tmp8_sb.Append(" AND FacilityTypeId = ");
- Tmp8_sb.Append(TmpValue[3]);
- Tmp8_sb.Append(" AND FacilityCode = ");
- Tmp8_sb.Append(TmpValue[4]);
- Tmp8_sb.Append(" AND PropertyId = ");
- Tmp8_sb.Append(TmpValue[5]);
- Tmp8_sb.Append(" AND CreatedDateTime = '");
- Tmp8_sb.Append(Max_DailyDate);
- Tmp8_sb.Append("'");
- query += Tmp8_sb.ToString();
- Tmp8_sb.Clear();
- Tmp9_sb.Append(" UPDATE ");
- Tmp9_sb.Append(Table.Daily);
- Tmp9_sb.Append(" SET MinValue = CASE WHEN MinValue >= ");
- Tmp9_sb.Append(Math.Round(InsertDailyValue, 4, MidpointRounding.AwayFromZero));
- Tmp9_sb.Append(" THEN ");
- Tmp9_sb.Append(Math.Round(InsertDailyValue, 4, MidpointRounding.AwayFromZero));
- Tmp9_sb.Append(" WHEN MinValue <= ");
- Tmp9_sb.Append(Math.Round(InsertDailyValue, 4, MidpointRounding.AwayFromZero));
- Tmp9_sb.Append(" THEN MinValue END ");
- Tmp9_sb.Append("WHERE SiteId = ");
- Tmp9_sb.Append(TmpValue[2]);
- Tmp9_sb.Append(" AND FacilityTypeId = ");
- Tmp9_sb.Append(TmpValue[3]);
- Tmp9_sb.Append(" AND FacilityCode = ");
- Tmp9_sb.Append(TmpValue[4]);
- Tmp9_sb.Append(" AND PropertyId = ");
- Tmp9_sb.Append(TmpValue[5]);
- Tmp9_sb.Append(" AND CreatedDateTime = '");
- Tmp9_sb.Append(Min_DailyDate);
- Tmp9_sb.Append("'");
- query += Tmp9_sb.ToString();
- SqlCommand cmd7 = new SqlCommand(query, conn);
- cmd7.ExecuteNonQuery();
- cmd7.Dispose();
- Tmp9_sb.Clear();
- }
- conn.Close();
- }
- conn.Dispose();
- }
- }
- private StringBuilder TmpEx1_sb = new StringBuilder();
- private StringBuilder TmpEx2_sb = new StringBuilder();
- public void Expression_Insert(IDictionary<string, float[]> MyDic, int NowMin)
- {
- string query = "";
- string InsertDate = DateTime.Now.ToString("yyyy-MM-dd HH:" + NowMin + ":00.000");
- string[] TmpStringValue = new string[8];
- float InsertValue = 0;
- string strcon = ConnDBInfo();
- using (SqlConnection conn = new SqlConnection(strcon))
- {
- foreach (KeyValuePair<string, float[]> kv in MyDic)
- {
- // Key값과 Value값을 담기 위한 TKey, TValue
- string TmpPoint = kv.Key;
- float[] TmpValue = kv.Value;
- conn.Open();
- switch (NowMin)
- {
- case 15:
- InsertValue = TmpValue[8];
- break;
- case 30:
- InsertValue = TmpValue[9];
- break;
- case 45:
- InsertValue = TmpValue[10];
- break;
- case 00:
- InsertValue = TmpValue[11];
- break;
- }
- {
- TmpEx1_sb.Append("INSERT INTO ");
- TmpEx1_sb.Append(Table.FiFt);
- TmpEx1_sb.Append("(SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,CurrentValue) ");
- TmpEx1_sb.Append("VALUES(");
- TmpEx1_sb.Append(TmpValue[2]);
- TmpEx1_sb.Append(",");
- TmpEx1_sb.Append(TmpValue[3]);
- TmpEx1_sb.Append(",");
- TmpEx1_sb.Append(TmpValue[4]);
- TmpEx1_sb.Append(",");
- TmpEx1_sb.Append(TmpValue[5]);
- TmpEx1_sb.Append(",'");
- TmpEx1_sb.Append(InsertDate);
- TmpEx1_sb.Append("',");
- TmpEx1_sb.Append(Math.Round(InsertValue, 4, MidpointRounding.AwayFromZero));
- TmpEx1_sb.Append(")");
- query = TmpEx1_sb.ToString();
- TmpEx1_sb.Clear();
- }
- if (InsertDate == DateTime.Now.ToString("yyyy-MM-dd 00:15:00.000"))
- {
- TmpEx2_sb.Append("INSERT INTO ");
- TmpEx2_sb.Append(Table.Daily);
- TmpEx2_sb.Append("(SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,DailyValue,MaxValue,MinValue) ");
- TmpEx2_sb.Append("VALUES(");
- TmpEx2_sb.Append(TmpValue[2]);
- TmpEx2_sb.Append(",");
- TmpEx2_sb.Append(TmpValue[3]);
- TmpEx2_sb.Append(",");
- TmpEx2_sb.Append(TmpValue[4]);
- TmpEx2_sb.Append(",");
- TmpEx2_sb.Append(TmpValue[5]);
- TmpEx2_sb.Append(",'");
- TmpEx2_sb.Append(Convert.ToDateTime(InsertDate).ToString("yyyy-MM-dd 00:00:00.000"));
- TmpEx2_sb.Append("',");
- TmpEx2_sb.Append(Math.Round(InsertValue, 4, MidpointRounding.AwayFromZero));
- TmpEx2_sb.Append(",");
- TmpEx2_sb.Append(Math.Round(InsertValue, 4, MidpointRounding.AwayFromZero));
- TmpEx2_sb.Append(",");
- TmpEx2_sb.Append(Math.Round(InsertValue, 4, MidpointRounding.AwayFromZero));
- TmpEx2_sb.Append(")");
- query += TmpEx2_sb.ToString();
- TmpEx2_sb.Clear();
- }
- SqlCommand cmd = new SqlCommand(query, conn);
- cmd.ExecuteNonQuery();
- cmd.Dispose();
- conn.Close();
- }
- conn.Dispose();
- }
- }
- }
- }
|