3afaea1f1d43d8795de05bb813f24af99a69b219.svn-base 17 KB

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