887a135a14e34b38e91668d1c7615013ba0c7e6e.svn-base 17 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Data.SqlClient;
  4. using System.Diagnostics;
  5. using System.Linq;
  6. using System.Text;
  7. namespace BEMSDataGateway
  8. {
  9. class OneMinute_Insert
  10. {
  11. LogFileCreate LFC = new LogFileCreate();
  12. My_DB_DML MDD = new My_DB_DML();
  13. string Standard_Time;
  14. public void OneMin_Insert(float SiteId, float CurrentValue)
  15. {
  16. Stopwatch SW = new Stopwatch();
  17. string sTemp1;
  18. SW.Reset();
  19. SW.Start();
  20. string query = "";
  21. string InsertDate = DateTime.Now.ToString("yyyy-MM-dd HH:" + DateTime.Now.Minute.ToString("mm") + ":00.000");
  22. string strcon = MDD.ConnDBInfo();
  23. LFC.Log("[OneMin_Insert.cs] BemsPeakHistory 삽입" + Environment.NewLine);
  24. using (SqlConnection conn = new SqlConnection(strcon))
  25. {
  26. conn.Open();
  27. query = "INSERT INTO BemsPeakHistory(SiteId,CreatedDateTime,CurrentValue) "
  28. + "VALUES(" + SiteId + ", '" + InsertDate + "'," + Math.Round(CurrentValue, 4, MidpointRounding.AwayFromZero) + ")";
  29. SqlCommand cmd = new SqlCommand(query, conn);
  30. cmd.ExecuteNonQuery();
  31. conn.Close();
  32. }
  33. LFC.Log("[OneMin_Insert.cs] BemsPeakHistory TOP 1 선택" + Environment.NewLine);
  34. using (SqlConnection conn = new SqlConnection(strcon))
  35. {
  36. conn.Open();
  37. SqlCommand cmd = new SqlCommand();
  38. cmd.Connection = conn;
  39. cmd.CommandText = "SELECT TOP 1 CreatedDateTime FROM BemsPeakHistory WHERE SiteId = " + SiteId + "";
  40. SqlDataReader rdr = cmd.ExecuteReader();
  41. // 다음 레코드 계속 가져와서 루핑
  42. while (rdr.Read())
  43. {
  44. string Standard_Time_Tmp = Convert.ToString(rdr.GetDateTime(0));
  45. Standard_Time = Convert.ToDateTime(Standard_Time_Tmp).ToString("yyyy-MM-dd HH:mm:00");
  46. }
  47. rdr.Close();
  48. conn.Close();
  49. }
  50. //기존 7일 후 삭제 => 현재 1년후 삭제
  51. LFC.Log("[OneMin_Insert.cs] BemsPeakHistory 1년 지난 데이터 삭제" + Environment.NewLine);
  52. TimeSpan ts = Convert.ToDateTime(InsertDate) - Convert.ToDateTime(Standard_Time);
  53. float MinSpan = (float)ts.TotalMinutes;
  54. if (MinSpan > 525600) // 1년 지나면 삭제
  55. {
  56. using (SqlConnection conn = new SqlConnection(strcon))
  57. {
  58. conn.Open();
  59. query = "DELETE FROM BemsPeakHistory WHERE SiteId = " + SiteId + " AND CreatedDateTime = '" + Standard_Time + "'";
  60. SqlCommand cmd = new SqlCommand(query, conn);
  61. cmd.ExecuteNonQuery();
  62. conn.Close();
  63. }
  64. }
  65. SW.Stop();
  66. sTemp1 = SW.Elapsed.ToString();
  67. LFC.Log("[OneMin_Insert.cs] 1분 데이터 시간 측정 : " + sTemp1 + " " + Environment.NewLine);
  68. }
  69. /// <summary>
  70. /// BemsPeakInfo에 테이블이 있는지 확인하기 위한 함수.
  71. /// </summary>
  72. private StringBuilder Peak_Info_In = new StringBuilder();
  73. public void Check_Exist_PeakInfo()
  74. {
  75. int Check_Num = 0;
  76. string TmpDML = "";
  77. string strcon = MDD.ConnDBInfo();
  78. string Insert_peakinfo = "";
  79. LFC.Log("[OneMin_Insert.cs] BemsPeakInfo 테이블 Count 조회 및 삽입");
  80. using (SqlConnection conn = new SqlConnection(strcon))
  81. {
  82. TmpDML = "SELECT COUNT(siteId)FROM BemsPeakInfo";
  83. SqlCommand cmd = new SqlCommand(TmpDML, conn);
  84. conn.Open();
  85. Check_Num = Convert.ToInt32(cmd.ExecuteScalar());
  86. conn.Close();
  87. }
  88. if (Check_Num == 0) // 조회할 데이터 없는 경우
  89. {
  90. Readini Ri = new Readini();
  91. Ri.ReadData();
  92. string Insert_Date = DateTime.Now.ToString("yyyy-MM-dd 00:00:00.000");
  93. using (SqlConnection conn = new SqlConnection(strcon))
  94. {
  95. Peak_Info_In.Append("INSERT INTO BemsPeakInfo(SiteId,MaxDateTIme,MaxPeakValue,MinDateTime,MinPeakValue,todayMaxPeakValue,todayMinPeakValue) VALUES(");
  96. Peak_Info_In.Append(Ri.SiteId);
  97. Peak_Info_In.Append(",'");
  98. Peak_Info_In.Append(Insert_Date);
  99. Peak_Info_In.Append("',0,'");
  100. Peak_Info_In.Append(Insert_Date);
  101. Peak_Info_In.Append("',500,0,500)");
  102. Insert_peakinfo = Peak_Info_In.ToString();
  103. try
  104. {
  105. conn.Open();
  106. SqlCommand cmd_p = new SqlCommand(Insert_peakinfo, conn);
  107. cmd_p.ExecuteNonQuery();
  108. }
  109. catch (Exception e)
  110. {
  111. LogFileCreate LFC = new LogFileCreate();
  112. LFC.Log("[OneMin_Insert.cs] BemsPeakInfo 테이블 Insert 오류 발생 : " + Insert_peakinfo + " = 오류 내역 = " + e + Environment.NewLine);
  113. }
  114. finally
  115. {
  116. conn.Close();
  117. }
  118. }
  119. }
  120. }
  121. public void Today_PeakMinMax() // 1일중에 최대, 최소값 산출해서 Read.ini에 세팅
  122. {
  123. object MaxValue;
  124. object MinValue;
  125. string query = "";
  126. string Date1 = DateTime.Now.ToString("yyyy-MM-dd 00:00:00.000");
  127. string Date2 = DateTime.Now.ToString("yyyy-MM-dd 23:59:59.000");
  128. string strcon = MDD.ConnDBInfo();
  129. Check_Exist_PeakInfo(); // BemsPeakInfo 테이블 Count 조회 및 삽입
  130. using (SqlConnection conn = new SqlConnection(strcon)) //하루 최대값
  131. {
  132. SqlCommand cmd_0 = new SqlCommand();
  133. cmd_0.Connection = conn;
  134. cmd_0.CommandText = "SELECT MAX(currentValue) FROM BemsPeakHistory WHERE CreatedDateTime between '" + Date1 + "' and '" + Date2 + "'";
  135. conn.Open();
  136. MaxValue = cmd_0.ExecuteScalar();
  137. if (MaxValue.GetType() == typeof(DBNull))
  138. {
  139. MaxValue = 0;
  140. }
  141. conn.Close();
  142. }
  143. using (SqlConnection conn = new SqlConnection(strcon)) //하루 최소값
  144. {
  145. SqlCommand cmd_1 = new SqlCommand();
  146. cmd_1.Connection = conn;
  147. cmd_1.CommandText = "SELECT MIN(currentValue) FROM BemsPeakHistory WHERE CreatedDateTime between '" + Date1 + "' and '" + Date2 + "'";
  148. conn.Open();
  149. MinValue = cmd_1.ExecuteScalar();
  150. if (MinValue.GetType() == typeof(DBNull))
  151. {
  152. MinValue = 500;
  153. }
  154. conn.Close();
  155. }
  156. Readini Ri = new Readini();
  157. Ri.SetIniValue("PeakInfo", "todayMaxPeakValue", Convert.ToString(MaxValue));
  158. Ri.SetIniValue("PeakInfo", "todayMinPeakValue", Convert.ToString(MinValue));
  159. using (SqlConnection conn = new SqlConnection(strcon))
  160. {
  161. conn.Open();
  162. query = "update BemsPeakInfo set todayMaxPeakValue = " + MaxValue + "";
  163. SqlCommand cmd_2 = new SqlCommand(query, conn);
  164. cmd_2.ExecuteNonQuery();
  165. conn.Close();
  166. }
  167. using (SqlConnection conn = new SqlConnection(strcon))
  168. {
  169. conn.Open();
  170. query = "update BemsPeakInfo set todayMinPeakValue = " + MinValue + "";
  171. SqlCommand cmd_3 = new SqlCommand(query, conn);
  172. cmd_3.ExecuteNonQuery();
  173. conn.Close();
  174. }
  175. LFC.Log("[OneMin_Insert.cs] Today_PeakMinMax 1일 최대 최소값 업데이트 ");
  176. }
  177. public void Year_PeakMinMax() // 1년중 최대, 최소값 산출해서 Read.ini에 세팅
  178. {
  179. object MaxPeakValue;
  180. object MinPeakValue;
  181. string strcon = MDD.ConnDBInfo();
  182. using (SqlConnection conn = new SqlConnection(strcon)) // 최대값 산출
  183. {
  184. SqlCommand cmd_4 = new SqlCommand();
  185. cmd_4.Connection = conn;
  186. cmd_4.CommandText = "SELECT MaxPeakValue FROM BemsPeakInfo";
  187. conn.Open();
  188. MaxPeakValue = cmd_4.ExecuteScalar();
  189. conn.Close();
  190. }
  191. using (SqlConnection conn = new SqlConnection(strcon)) //최소값 산출
  192. {
  193. SqlCommand cmd_5 = new SqlCommand();
  194. cmd_5.Connection = conn;
  195. cmd_5.CommandText = "SELECT MinPeakValue FROM BemsPeakInfo";
  196. conn.Open();
  197. MinPeakValue = cmd_5.ExecuteScalar();
  198. conn.Close();
  199. }
  200. Readini Ri = new Readini();
  201. Ri.SetIniValue("PeakInfo", "MaxPeakValue", Convert.ToString(MaxPeakValue));
  202. Ri.SetIniValue("PeakInfo", "MinPeakValue", Convert.ToString(MinPeakValue));
  203. LFC.Log("[OneMin_Insert.cs] BemsPeakInfo 테이블 1년 최대 최소값 선택 " + Environment.NewLine);
  204. }
  205. public void PeakCheck(float CurrentPeakValue)
  206. {
  207. string Standard_Time = DateTime.Now.ToString("yyyy-MM-dd HH:mm:00");
  208. string strcon = MDD.ConnDBInfo();
  209. string query_reset = "";
  210. Readini Ri = new Readini();
  211. Ri.ReadData();
  212. //하루가지나면..
  213. if (DateTime.Now.ToString("HH") == "00" && DateTime.Now.ToString("mm") == "00") // 1일 최대 최소 값 0으로 업데이트
  214. {
  215. using (SqlConnection conn = new SqlConnection(strcon))
  216. {
  217. conn.Open();
  218. query_reset = "UPDATE BemsPeakInfo SET todayMaxPeakValue = " + 0 + "";
  219. SqlCommand cmd1 = new SqlCommand(query_reset, conn);
  220. cmd1.ExecuteNonQuery();
  221. conn.Close();
  222. }
  223. using (SqlConnection conn = new SqlConnection(strcon))
  224. {
  225. conn.Open();
  226. query_reset = "UPDATE BemsPeakInfo SET todayMinPeakValue = " + 500 + "";
  227. SqlCommand cmd2 = new SqlCommand(query_reset, conn);
  228. cmd2.ExecuteNonQuery();
  229. conn.Close();
  230. }
  231. Ri.SetIniValue("PeakInfo", "todayMaxPeakValue", Convert.ToString(0));
  232. Ri.SetIniValue("PeakInfo", "todayMinPeakValue", Convert.ToString(500));
  233. }
  234. //일년이지나면...
  235. if (DateTime.Now.ToString("MM") == "01" && DateTime.Now.ToString("dd") == "01" && DateTime.Now.ToString("HH") == "00" && DateTime.Now.ToString("mm") == "00")
  236. {
  237. using (SqlConnection conn = new SqlConnection(strcon))
  238. {
  239. conn.Open();
  240. query_reset = "UPDATE BemsPeakInfo SET MaxPeakValue = " + 0 + ", MaxDateTime = '" + Standard_Time + "'";
  241. SqlCommand cmd3 = new SqlCommand(query_reset, conn);
  242. cmd3.ExecuteNonQuery();
  243. conn.Close();
  244. }
  245. using (SqlConnection conn = new SqlConnection(strcon))
  246. {
  247. conn.Open();
  248. query_reset = "UPDATE BemsPeakInfo SET MinPeakValue = " + 500 + ", MinDateTime = '" + Standard_Time + "'";
  249. SqlCommand cmd4 = new SqlCommand(query_reset, conn);
  250. cmd4.ExecuteNonQuery();
  251. conn.Close();
  252. }
  253. Ri.SetIniValue("PeakInfo", "MaxPeakValue", Convert.ToString(0));
  254. Ri.SetIniValue("PeakInfo", "MinPeakValue", Convert.ToString(500));
  255. }
  256. string query1 = "";
  257. string query2 = "";
  258. string query3 = "";
  259. string query4 = "";
  260. float MaxPeak = (float)Convert.ToDouble(Ri.MaxPeakValue);
  261. float MinPeak = (float)Convert.ToDouble(Ri.MinPeakValue);
  262. float todayMaxPeak = (float)Convert.ToDouble(Ri.todayMaxPeakValue);
  263. float todayMinPeak = (float)Convert.ToDouble(Ri.todayMinPeakValue);
  264. float[] result = new float[4];
  265. bool[] check = new bool[4];
  266. //일년치 최대
  267. if (CurrentPeakValue > MaxPeak)
  268. {
  269. result[0] = CurrentPeakValue;
  270. check[0] = true;
  271. }
  272. else
  273. {
  274. check[0] = false;
  275. }
  276. //일년치 최소
  277. if (CurrentPeakValue < MinPeak)
  278. {
  279. result[1] = CurrentPeakValue;
  280. check[1] = true;
  281. }
  282. else
  283. {
  284. check[1] = false;
  285. }
  286. //하루치 최대
  287. if (CurrentPeakValue > todayMaxPeak)
  288. {
  289. result[2] = CurrentPeakValue;
  290. check[2] = true;
  291. }
  292. else
  293. {
  294. check[2] = false;
  295. }
  296. //하루치 최소
  297. if (CurrentPeakValue < todayMinPeak)
  298. {
  299. result[3] = CurrentPeakValue;
  300. check[3] = true;
  301. }
  302. else
  303. {
  304. check[3] = false;
  305. }
  306. if (check[0] == true)
  307. {
  308. using (SqlConnection conn = new SqlConnection(strcon))
  309. {
  310. conn.Open();
  311. query1 = "UPDATE BemsPeakInfo SET MaxPeakValue = " + result[0] + ", MaxDateTime = '" + Standard_Time + "'";
  312. SqlCommand cmd5 = new SqlCommand(query1, conn);
  313. cmd5.ExecuteNonQuery();
  314. conn.Close();
  315. }
  316. Ri.SetIniValue("PeakInfo", "MaxPeakValue", Convert.ToString(result[0]));
  317. }
  318. if (check[1] == true)
  319. {
  320. using (SqlConnection conn = new SqlConnection(strcon))
  321. {
  322. conn.Open();
  323. query2 = "UPDATE BemsPeakInfo SET MinPeakValue = " + result[1] + ", MinDateTime = '" + Standard_Time + "'";
  324. SqlCommand cmd6 = new SqlCommand(query2, conn);
  325. cmd6.ExecuteNonQuery();
  326. conn.Close();
  327. }
  328. Ri.SetIniValue("PeakInfo", "MinPeakValue", Convert.ToString(result[1]));
  329. }
  330. if (check[2] == true)
  331. {
  332. using (SqlConnection conn = new SqlConnection(strcon))
  333. {
  334. conn.Open();
  335. query3 = "UPDATE BemsPeakInfo SET todayMaxPeakValue = " + result[2] + "";
  336. SqlCommand cmd7 = new SqlCommand(query3, conn);
  337. cmd7.ExecuteNonQuery();
  338. conn.Close();
  339. }
  340. Ri.SetIniValue("PeakInfo", "todayMaxPeakValue", Convert.ToString(result[2]));
  341. }
  342. if (check[3] == true)
  343. {
  344. using (SqlConnection conn = new SqlConnection(strcon))
  345. {
  346. conn.Open();
  347. query4 = "UPDATE BemsPeakInfo SET todayMinPeakValue = " + result[3] + "";
  348. SqlCommand cmd8 = new SqlCommand(query4, conn);
  349. cmd8.ExecuteNonQuery();
  350. conn.Close();
  351. }
  352. Ri.SetIniValue("PeakInfo", "todayMinPeakValue", Convert.ToString(result[3]));
  353. }
  354. }
  355. public void OneMin_Insert_COV(float SiteId, float FacilityTypeId, float FacilityCode, float PropertyId, float CurrentValue)
  356. {
  357. Stopwatch SW = new Stopwatch();
  358. string sTemp1;
  359. SW.Reset();
  360. SW.Start();
  361. string query = "";
  362. string InsertDate = DateTime.Now.ToString("yyyy-MM-dd HH:" + DateTime.Now.Minute.ToString("mm") + ":00.000");
  363. string strcon = MDD.ConnDBInfo();
  364. using (SqlConnection conn = new SqlConnection(strcon))
  365. {
  366. conn.Open();
  367. query = "INSERT INTO BemsCovHistory(SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,CurrentValue) "
  368. + "VALUES(" + SiteId + "," + FacilityTypeId + ", " + FacilityCode + ", " + PropertyId + ", '" + InsertDate + "'," + Math.Round(CurrentValue, 4, MidpointRounding.AwayFromZero) + ")";
  369. SqlCommand cmd = new SqlCommand(query, conn);
  370. cmd.ExecuteNonQuery();
  371. conn.Close();
  372. }
  373. SW.Stop();
  374. sTemp1 = SW.Elapsed.ToString();
  375. LFC.Log("[OneMin_Insert.cs] COV 1분데이터 Insert : " + sTemp1 + Environment.NewLine);
  376. }
  377. }
  378. }