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); } /// /// BemsPeakInfo에 테이블이 있는지 확인하기 위한 함수. /// 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); } } }