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(); 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(); } 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 객체를 리턴 SqlDataReader rdr = cmd.ExecuteReader(); // 다음 레코드 계속 가져와서 루핑 while (rdr.Read()) { LFC.Log(Convert.ToString(rdr.GetDateTime(0))); string Standard_Time_Tmp = Convert.ToString(rdr.GetDateTime(0)); LFC.Log(Convert.ToString(Standard_Time_Tmp)); Standard_Time = Convert.ToDateTime(Standard_Time_Tmp).ToString("yyyy-MM-dd HH:mm:00"); LFC.Log(Standard_Time); } // 사용후 닫음 rdr.Close(); //conn.Close(); } // TimeSpan ts = Convert.ToDateTime(InsertDate) - Convert.ToDateTime(Standard_Time); float MinSpan = (float)ts.TotalMinutes; if (MinSpan > 10080) { 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(sTemp1 + "1분데이터"); /////////////////////////////////////////////// } /// /// BemsPeakInfo에 테이블이 있는지 확인하기 위한 함수. /// private StringBuilder Peak_Info_In = new StringBuilder(); public void Check_Exist_PeakInfo() { int Check_Num = 0; string TmpDML = ""; // TmpDMl 초기화 string strcon = MDD.ConnDBInfo(); string Insert_peakinfo = ""; //조회 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)) { //INsert쿼리문 조합 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("INSERT 오류 발생 = " + Insert_peakinfo + " = 오류 내역 = " + e); } finally { conn.Close(); } } } } public void Today_PeakMinMax() { 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(); // PeakInfo Table에 Record가 있는지 확인. Check_Exist_PeakInfo(); ///////////////////////////////////////////// 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(); } using (SqlConnection conn = new SqlConnection(strcon)) { conn.Open(); query = "update BemsPeakInfo set todayMinPeakValue = " + MinValue + ""; SqlCommand cmd_3 = new SqlCommand(query, conn); cmd_3.ExecuteNonQuery(); } } public void Year_PeakMinMax() { //bool CheckOk_Max = false; //bool CheckOk_Min = false; //object History_MaxPeakValue; //object History_MinPeakValue; 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)); } 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" ) { using (SqlConnection conn = new SqlConnection(strcon)) { conn.Open(); query_reset = "UPDATE BemsPeakInfo SET todayMaxPeakValue = " + 0 + ""; SqlCommand cmd1 = new SqlCommand(query_reset, conn); cmd1.ExecuteNonQuery(); } using (SqlConnection conn = new SqlConnection(strcon)) { conn.Open(); query_reset = "UPDATE BemsPeakInfo SET todayMinPeakValue = " + 500 + ""; SqlCommand cmd2 = new SqlCommand(query_reset, conn); cmd2.ExecuteNonQuery(); } 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(); } 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(); } 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(); } 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(); } 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(); } 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(); } 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(sTemp1 + "= COV 1분데이터 Insert"); /////////////////////////////////////////////// } } }