123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446 |
- using System;
- using System.Collections.Generic;
- using System.Data.SqlClient;
- using System.Diagnostics;
- using System.Linq;
- using System.Text;
- namespace BEMSDataGateway
- {
- class OneMinute_Insert
- {
- LogFileCreate LFC = new LogFileCreate();
- My_DB_DML MDD = new My_DB_DML();
- string Standard_Time;
- public void OneMin_Insert(float SiteId, float CurrentValue)
- {
- Stopwatch SW = new Stopwatch();
- string sTemp1;
- SW.Reset();
- SW.Start();
- string query = "";
- string InsertDate = DateTime.Now.ToString("yyyy-MM-dd HH:" + DateTime.Now.Minute.ToString("mm") + ":00.000");
- string strcon = MDD.ConnDBInfo();
- LFC.Log("[OneMin_Insert.cs] BemsPeakHistory 삽입" + Environment.NewLine);
- using (SqlConnection conn = new SqlConnection(strcon))
- {
- conn.Open();
- query = "INSERT INTO BemsPeakHistory(SiteId,CreatedDateTime,CurrentValue) "
- + "VALUES(" + SiteId + ", '" + InsertDate + "'," + Math.Round(CurrentValue, 4, MidpointRounding.AwayFromZero) + ")";
- SqlCommand cmd = new SqlCommand(query, conn);
- cmd.ExecuteNonQuery();
- conn.Close();
- }
- LFC.Log("[OneMin_Insert.cs] BemsPeakHistory TOP 1 선택" + Environment.NewLine);
- using (SqlConnection conn = new SqlConnection(strcon))
- {
- conn.Open();
- SqlCommand cmd = new SqlCommand();
- cmd.Connection = conn;
- cmd.CommandText = "SELECT TOP 1 CreatedDateTime FROM BemsPeakHistory WHERE SiteId = " + SiteId + "";
- SqlDataReader rdr = cmd.ExecuteReader();
- // 다음 레코드 계속 가져와서 루핑
- while (rdr.Read())
- {
- string Standard_Time_Tmp = Convert.ToString(rdr.GetDateTime(0));
- Standard_Time = Convert.ToDateTime(Standard_Time_Tmp).ToString("yyyy-MM-dd HH:mm:00");
- }
- rdr.Close();
- conn.Close();
- }
- //기존 7일 후 삭제 => 현재 1년후 삭제
- LFC.Log("[OneMin_Insert.cs] BemsPeakHistory 1년 지난 데이터 삭제" + Environment.NewLine);
- TimeSpan ts = Convert.ToDateTime(InsertDate) - Convert.ToDateTime(Standard_Time);
- float MinSpan = (float)ts.TotalMinutes;
- if (MinSpan > 525600) // 1년 지나면 삭제
- {
- using (SqlConnection conn = new SqlConnection(strcon))
- {
- conn.Open();
- query = "DELETE FROM BemsPeakHistory WHERE SiteId = " + SiteId + " AND CreatedDateTime = '" + Standard_Time + "'";
- SqlCommand cmd = new SqlCommand(query, conn);
- cmd.ExecuteNonQuery();
- conn.Close();
- }
- }
- SW.Stop();
- sTemp1 = SW.Elapsed.ToString();
- LFC.Log("[OneMin_Insert.cs] 1분 데이터 시간 측정 : " + sTemp1 + " " + Environment.NewLine);
- }
- /// <summary>
- /// BemsPeakInfo에 테이블이 있는지 확인하기 위한 함수.
- /// </summary>
- private StringBuilder Peak_Info_In = new StringBuilder();
- public void Check_Exist_PeakInfo()
- {
- int Check_Num = 0;
- string TmpDML = "";
- string strcon = MDD.ConnDBInfo();
- string Insert_peakinfo = "";
- LFC.Log("[OneMin_Insert.cs] BemsPeakInfo 테이블 Count 조회 및 삽입");
- using (SqlConnection conn = new SqlConnection(strcon))
- {
- TmpDML = "SELECT COUNT(siteId)FROM BemsPeakInfo";
- SqlCommand cmd = new SqlCommand(TmpDML, conn);
- conn.Open();
- Check_Num = Convert.ToInt32(cmd.ExecuteScalar());
- conn.Close();
- }
- if (Check_Num == 0) // 조회할 데이터 없는 경우
- {
- Readini Ri = new Readini();
- Ri.ReadData();
- string Insert_Date = DateTime.Now.ToString("yyyy-MM-dd 00:00:00.000");
- using (SqlConnection conn = new SqlConnection(strcon))
- {
- Peak_Info_In.Append("INSERT INTO BemsPeakInfo(SiteId,MaxDateTIme,MaxPeakValue,MinDateTime,MinPeakValue,todayMaxPeakValue,todayMinPeakValue) VALUES(");
- Peak_Info_In.Append(Ri.SiteId);
- Peak_Info_In.Append(",'");
- Peak_Info_In.Append(Insert_Date);
- Peak_Info_In.Append("',0,'");
- Peak_Info_In.Append(Insert_Date);
- Peak_Info_In.Append("',500,0,500)");
- Insert_peakinfo = Peak_Info_In.ToString();
- try
- {
- conn.Open();
- SqlCommand cmd_p = new SqlCommand(Insert_peakinfo, conn);
- cmd_p.ExecuteNonQuery();
- }
- catch (Exception e)
- {
- LogFileCreate LFC = new LogFileCreate();
- LFC.Log("[OneMin_Insert.cs] BemsPeakInfo 테이블 Insert 오류 발생 : " + Insert_peakinfo + " = 오류 내역 = " + e + Environment.NewLine);
- }
- finally
- {
- conn.Close();
- }
- }
- }
- }
- public void Today_PeakMinMax() // 1일중에 최대, 최소값 산출해서 Read.ini에 세팅
- {
- object MaxValue;
- object MinValue;
- string query = "";
- string Date1 = DateTime.Now.ToString("yyyy-MM-dd 00:00:00.000");
- string Date2 = DateTime.Now.ToString("yyyy-MM-dd 23:59:59.000");
- string strcon = MDD.ConnDBInfo();
- Check_Exist_PeakInfo(); // BemsPeakInfo 테이블 Count 조회 및 삽입
- using (SqlConnection conn = new SqlConnection(strcon)) //하루 최대값
- {
- SqlCommand cmd_0 = new SqlCommand();
- cmd_0.Connection = conn;
- cmd_0.CommandText = "SELECT MAX(currentValue) FROM BemsPeakHistory WHERE CreatedDateTime between '" + Date1 + "' and '" + Date2 + "'";
- conn.Open();
- MaxValue = cmd_0.ExecuteScalar();
- if (MaxValue.GetType() == typeof(DBNull))
- {
- MaxValue = 0;
- }
- conn.Close();
- }
- using (SqlConnection conn = new SqlConnection(strcon)) //하루 최소값
- {
- SqlCommand cmd_1 = new SqlCommand();
- cmd_1.Connection = conn;
- cmd_1.CommandText = "SELECT MIN(currentValue) FROM BemsPeakHistory WHERE CreatedDateTime between '" + Date1 + "' and '" + Date2 + "'";
- conn.Open();
- MinValue = cmd_1.ExecuteScalar();
- if (MinValue.GetType() == typeof(DBNull))
- {
- MinValue = 500;
- }
- conn.Close();
- }
- Readini Ri = new Readini();
- Ri.SetIniValue("PeakInfo", "todayMaxPeakValue", Convert.ToString(MaxValue));
- Ri.SetIniValue("PeakInfo", "todayMinPeakValue", Convert.ToString(MinValue));
- using (SqlConnection conn = new SqlConnection(strcon))
- {
- conn.Open();
- query = "update BemsPeakInfo set todayMaxPeakValue = " + MaxValue + "";
- SqlCommand cmd_2 = new SqlCommand(query, conn);
- cmd_2.ExecuteNonQuery();
- conn.Close();
- }
- using (SqlConnection conn = new SqlConnection(strcon))
- {
- conn.Open();
- query = "update BemsPeakInfo set todayMinPeakValue = " + MinValue + "";
- SqlCommand cmd_3 = new SqlCommand(query, conn);
- cmd_3.ExecuteNonQuery();
- conn.Close();
- }
- LFC.Log("[OneMin_Insert.cs] Today_PeakMinMax 1일 최대 최소값 업데이트 ");
- }
- public void Year_PeakMinMax() // 1년중 최대, 최소값 산출해서 Read.ini에 세팅
- {
- object MaxPeakValue;
- object MinPeakValue;
- string strcon = MDD.ConnDBInfo();
- using (SqlConnection conn = new SqlConnection(strcon)) // 최대값 산출
- {
- SqlCommand cmd_4 = new SqlCommand();
- cmd_4.Connection = conn;
- cmd_4.CommandText = "SELECT MaxPeakValue FROM BemsPeakInfo";
- conn.Open();
- MaxPeakValue = cmd_4.ExecuteScalar();
- conn.Close();
- }
- using (SqlConnection conn = new SqlConnection(strcon)) //최소값 산출
- {
- SqlCommand cmd_5 = new SqlCommand();
- cmd_5.Connection = conn;
- cmd_5.CommandText = "SELECT MinPeakValue FROM BemsPeakInfo";
- conn.Open();
- MinPeakValue = cmd_5.ExecuteScalar();
- conn.Close();
- }
- Readini Ri = new Readini();
- Ri.SetIniValue("PeakInfo", "MaxPeakValue", Convert.ToString(MaxPeakValue));
- Ri.SetIniValue("PeakInfo", "MinPeakValue", Convert.ToString(MinPeakValue));
- LFC.Log("[OneMin_Insert.cs] BemsPeakInfo 테이블 1년 최대 최소값 선택 " + Environment.NewLine);
- }
- public void PeakCheck(float CurrentPeakValue)
- {
- string Standard_Time = DateTime.Now.ToString("yyyy-MM-dd HH:mm:00");
- string strcon = MDD.ConnDBInfo();
- string query_reset = "";
- Readini Ri = new Readini();
- Ri.ReadData();
- //하루가지나면..
- if (DateTime.Now.ToString("HH") == "00" && DateTime.Now.ToString("mm") == "00") // 1일 최대 최소 값 0으로 업데이트
- {
- using (SqlConnection conn = new SqlConnection(strcon))
- {
- conn.Open();
- query_reset = "UPDATE BemsPeakInfo SET todayMaxPeakValue = " + 0 + "";
- SqlCommand cmd1 = new SqlCommand(query_reset, conn);
- cmd1.ExecuteNonQuery();
- conn.Close();
- }
- using (SqlConnection conn = new SqlConnection(strcon))
- {
- conn.Open();
- query_reset = "UPDATE BemsPeakInfo SET todayMinPeakValue = " + 500 + "";
- SqlCommand cmd2 = new SqlCommand(query_reset, conn);
- cmd2.ExecuteNonQuery();
- conn.Close();
- }
- Ri.SetIniValue("PeakInfo", "todayMaxPeakValue", Convert.ToString(0));
- Ri.SetIniValue("PeakInfo", "todayMinPeakValue", Convert.ToString(500));
- }
- //일년이지나면...
- if (DateTime.Now.ToString("MM") == "01" && DateTime.Now.ToString("dd") == "01" && DateTime.Now.ToString("HH") == "00" && DateTime.Now.ToString("mm") == "00")
- {
- using (SqlConnection conn = new SqlConnection(strcon))
- {
- conn.Open();
- query_reset = "UPDATE BemsPeakInfo SET MaxPeakValue = " + 0 + ", MaxDateTime = '" + Standard_Time + "'";
- SqlCommand cmd3 = new SqlCommand(query_reset, conn);
- cmd3.ExecuteNonQuery();
- conn.Close();
- }
- using (SqlConnection conn = new SqlConnection(strcon))
- {
- conn.Open();
- query_reset = "UPDATE BemsPeakInfo SET MinPeakValue = " + 500 + ", MinDateTime = '" + Standard_Time + "'";
- SqlCommand cmd4 = new SqlCommand(query_reset, conn);
- cmd4.ExecuteNonQuery();
- conn.Close();
- }
- Ri.SetIniValue("PeakInfo", "MaxPeakValue", Convert.ToString(0));
- Ri.SetIniValue("PeakInfo", "MinPeakValue", Convert.ToString(500));
- }
- string query1 = "";
- string query2 = "";
- string query3 = "";
- string query4 = "";
- float MaxPeak = (float)Convert.ToDouble(Ri.MaxPeakValue);
- float MinPeak = (float)Convert.ToDouble(Ri.MinPeakValue);
- float todayMaxPeak = (float)Convert.ToDouble(Ri.todayMaxPeakValue);
- float todayMinPeak = (float)Convert.ToDouble(Ri.todayMinPeakValue);
- float[] result = new float[4];
- bool[] check = new bool[4];
- //일년치 최대
- if (CurrentPeakValue > MaxPeak)
- {
- result[0] = CurrentPeakValue;
- check[0] = true;
- }
- else
- {
- check[0] = false;
- }
- //일년치 최소
- if (CurrentPeakValue < MinPeak)
- {
- result[1] = CurrentPeakValue;
- check[1] = true;
- }
- else
- {
- check[1] = false;
- }
- //하루치 최대
- if (CurrentPeakValue > todayMaxPeak)
- {
- result[2] = CurrentPeakValue;
- check[2] = true;
- }
- else
- {
- check[2] = false;
- }
- //하루치 최소
- if (CurrentPeakValue < todayMinPeak)
- {
- result[3] = CurrentPeakValue;
- check[3] = true;
- }
- else
- {
- check[3] = false;
- }
- if (check[0] == true)
- {
- using (SqlConnection conn = new SqlConnection(strcon))
- {
- conn.Open();
- query1 = "UPDATE BemsPeakInfo SET MaxPeakValue = " + result[0] + ", MaxDateTime = '" + Standard_Time + "'";
- SqlCommand cmd5 = new SqlCommand(query1, conn);
- cmd5.ExecuteNonQuery();
- conn.Close();
- }
- Ri.SetIniValue("PeakInfo", "MaxPeakValue", Convert.ToString(result[0]));
- }
- if (check[1] == true)
- {
- using (SqlConnection conn = new SqlConnection(strcon))
- {
- conn.Open();
- query2 = "UPDATE BemsPeakInfo SET MinPeakValue = " + result[1] + ", MinDateTime = '" + Standard_Time + "'";
- SqlCommand cmd6 = new SqlCommand(query2, conn);
- cmd6.ExecuteNonQuery();
- conn.Close();
- }
- Ri.SetIniValue("PeakInfo", "MinPeakValue", Convert.ToString(result[1]));
- }
- if (check[2] == true)
- {
- using (SqlConnection conn = new SqlConnection(strcon))
- {
- conn.Open();
- query3 = "UPDATE BemsPeakInfo SET todayMaxPeakValue = " + result[2] + "";
- SqlCommand cmd7 = new SqlCommand(query3, conn);
- cmd7.ExecuteNonQuery();
- conn.Close();
- }
- Ri.SetIniValue("PeakInfo", "todayMaxPeakValue", Convert.ToString(result[2]));
- }
- if (check[3] == true)
- {
- using (SqlConnection conn = new SqlConnection(strcon))
- {
- conn.Open();
- query4 = "UPDATE BemsPeakInfo SET todayMinPeakValue = " + result[3] + "";
- SqlCommand cmd8 = new SqlCommand(query4, conn);
- cmd8.ExecuteNonQuery();
- conn.Close();
- }
- Ri.SetIniValue("PeakInfo", "todayMinPeakValue", Convert.ToString(result[3]));
- }
- }
- public void OneMin_Insert_COV(float SiteId, float FacilityTypeId, float FacilityCode, float PropertyId, float CurrentValue)
- {
- Stopwatch SW = new Stopwatch();
- string sTemp1;
- SW.Reset();
- SW.Start();
- string query = "";
- string InsertDate = DateTime.Now.ToString("yyyy-MM-dd HH:" + DateTime.Now.Minute.ToString("mm") + ":00.000");
- string strcon = MDD.ConnDBInfo();
- using (SqlConnection conn = new SqlConnection(strcon))
- {
- conn.Open();
- query = "INSERT INTO BemsCovHistory(SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,CurrentValue) "
- + "VALUES(" + SiteId + "," + FacilityTypeId + ", " + FacilityCode + ", " + PropertyId + ", '" + InsertDate + "'," + Math.Round(CurrentValue, 4, MidpointRounding.AwayFromZero) + ")";
- SqlCommand cmd = new SqlCommand(query, conn);
- cmd.ExecuteNonQuery();
- conn.Close();
- }
- SW.Stop();
- sTemp1 = SW.Elapsed.ToString();
- LFC.Log("[OneMin_Insert.cs] COV 1분데이터 Insert : " + sTemp1 + Environment.NewLine);
- }
- }
- }
|