123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535 |
- using System;
- using System.Collections.Generic;
- using System.Data.SqlClient;
- using System.Diagnostics;
- using System.Linq;
- using System.Text;
- namespace DataGateWayProject
- {
- 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분데이터");
- ///////////////////////////////////////////////
- }
- /// <summary>
- /// BemsPeakInfo에 테이블이 있는지 확인하기 위한 함수.
- /// </summary>
- 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 = 5000;
- }
- // 사용후 닫음
- 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 = " + 5000 + ""; // 최소값을 0으로 해버리면 그것 자체가 하루의 최소값이 되니까..
- SqlCommand cmd2 = new SqlCommand(query_reset, conn);
- cmd2.ExecuteNonQuery();
- }
- Ri.SetIniValue("PeakInfo", "todayMaxPeakValue", Convert.ToString(0));
- Ri.SetIniValue("PeakInfo", "todayMinPeakValue", Convert.ToString(5000));
- }
- //일년이지나면...
- 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 = " + 5000 + ", MinDateTime = '" + Standard_Time + "'"; // 최소값을 0으로 해버리면 그것 자체가 일년의 최소값이 되니까..
- SqlCommand cmd4 = new SqlCommand(query_reset, conn);
- cmd4.ExecuteNonQuery();
- }
- Ri.SetIniValue("PeakInfo", "MaxPeakValue", Convert.ToString(0));
- Ri.SetIniValue("PeakInfo", "MinPeakValue", Convert.ToString(5000));
- }
- 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");
- ///////////////////////////////////////////////
- }
- }
- }
|