DBinsert.cs 36 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Text;
  5. using System.Data.SqlClient;
  6. using System.Data;
  7. using System.IO;
  8. using System.Collections.Concurrent;
  9. using System.Diagnostics;
  10. namespace BEMSDataGateway
  11. {
  12. static class Table
  13. {
  14. public const string RawT = "BemsMonitoringPointHistory15minRawData";
  15. public const string FiFt = "BemsMonitoringPointHistory15min";
  16. public const string Hourly = "BemsMonitoringPointHistoryHourly";
  17. public const string Daily = "BemsMonitoringPointHistoryDaily";
  18. }
  19. class My_DB_DML
  20. {
  21. public string ConnDBInfo() // DB접속 정보를 읽는 함수
  22. {
  23. Readini Ri = new Readini();
  24. Ri.ReadData();
  25. string strconInfo = @"Data Source = " + Ri.UserIP + "," + Ri.UserPort + "; "
  26. + "Initial Catalog =" + Ri.UserDB + "; "
  27. + "Persist Security Info=True; "
  28. + "User ID =" + Ri.UserID + "; "
  29. + "Password =" + Ri.UserPW + "";
  30. return strconInfo;
  31. }
  32. private StringBuilder sb_delete = new StringBuilder();
  33. public void DB_Min_Delete(IDictionary<string, float[]> MyDic, int NowMin)
  34. {
  35. LogFileCreate LFC = new LogFileCreate();
  36. int tmpNowMin = 0;
  37. string InsertDate1 = "";
  38. if (NowMin == 00)
  39. {
  40. tmpNowMin = 45;
  41. InsertDate1 = DateTime.Now.AddHours(-1).ToString("yyyy-MM-dd HH:" + tmpNowMin + ":00.000");
  42. }
  43. else
  44. {
  45. tmpNowMin = NowMin - 15;
  46. InsertDate1 = DateTime.Now.ToString("yyyy-MM-dd HH:" + tmpNowMin + ":00.000");
  47. }
  48. string query = "";
  49. string[] TmpStringValue = new string[8];
  50. string strcon = ConnDBInfo();
  51. using (SqlConnection conn = new SqlConnection(strcon))
  52. {
  53. conn.Open();
  54. foreach (KeyValuePair<string, float[]> kv in MyDic)
  55. {
  56. /// Key값과 Value값을 담기 위한 TKey, TValue
  57. string TmpPoint = kv.Key;
  58. float[] TmpValue = kv.Value;
  59. if (TmpValue[7] != 7 || TmpValue[7] != 3) // 현재값과 RAW 아니면 삭제해야함
  60. {
  61. sb_delete.Append("DELETE FROM ");
  62. sb_delete.Append(Table.FiFt);
  63. sb_delete.Append(" WHERE SiteId = ");
  64. sb_delete.Append(TmpValue[2]);
  65. sb_delete.Append(" AND FacilityTypeId = ");
  66. sb_delete.Append(TmpValue[3]);
  67. sb_delete.Append(" AND FacilityCode = ");
  68. sb_delete.Append(TmpValue[4]);
  69. sb_delete.Append(" AND PropertyId = ");
  70. sb_delete.Append(TmpValue[5]);
  71. sb_delete.Append(" AND CreatedDateTime = '" + InsertDate1 + "'");
  72. query = sb_delete.ToString();
  73. SqlCommand cmd = new SqlCommand(query, conn);
  74. cmd.ExecuteNonQuery();
  75. cmd.Dispose();
  76. }
  77. }
  78. conn.Close();
  79. conn.Dispose();
  80. }
  81. sb_delete.Clear();
  82. }
  83. public void DB_Min_Insert(IDictionary<string, float[]> MyDic, int NowMin)
  84. {
  85. LogFileCreate LFC = new LogFileCreate();
  86. int tmpNowMin = 0;
  87. string InsertDate1 = "";
  88. if (NowMin == 00)
  89. {
  90. tmpNowMin = 45;
  91. InsertDate1 = DateTime.Now.AddHours(-1).ToString("yyyy-MM-dd HH:" + tmpNowMin + ":00.000");
  92. }
  93. else
  94. {
  95. tmpNowMin = NowMin - 15;
  96. InsertDate1 = DateTime.Now.ToString("yyyy-MM-dd HH:" + tmpNowMin + ":00.000");
  97. }
  98. string query = "";
  99. string InsertDate2 = DateTime.Now.ToString("yyyy-MM-dd HH:" + NowMin + ":00.000");
  100. string[] TmpStringValue = new string[8];
  101. float InsertValue = 0;
  102. string strcon = ConnDBInfo();
  103. try
  104. {
  105. using (SqlConnection conn = new SqlConnection(strcon))
  106. {
  107. foreach (KeyValuePair<string, float[]> kv in MyDic)
  108. {
  109. /// Key값과 Value값을 담기 위한 TKey, TValue
  110. string TmpPoint = kv.Key;
  111. float[] TmpValue = kv.Value;
  112. if (TmpValue[7] != 7)
  113. {
  114. conn.Open();
  115. switch (NowMin)
  116. {
  117. case 15:
  118. InsertValue = TmpValue[8];
  119. break;
  120. case 30:
  121. InsertValue = TmpValue[9];
  122. break;
  123. case 45:
  124. InsertValue = TmpValue[10];
  125. break;
  126. case 00:
  127. InsertValue = TmpValue[11];
  128. break;
  129. }
  130. if (TmpValue[7] == 2 && InsertValue < 0) //적산값인데 0보다 작으면...
  131. {
  132. InsertValue = 0;
  133. }
  134. if (TmpValue[7] == 3) // 현재값
  135. {
  136. query = "INSERT INTO " + Table.FiFt + "(SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,CurrentValue) "
  137. + "VALUES(" + TmpValue[2] + "," + TmpValue[3] + "," + TmpValue[4] + "," + TmpValue[5] + ",'" + InsertDate2 + "'," + Math.Round(InsertValue, 4, MidpointRounding.AwayFromZero) + ")";
  138. }
  139. else
  140. {
  141. query = "INSERT INTO " + Table.FiFt + "(SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,CurrentValue) "
  142. + "VALUES(" + TmpValue[2] + "," + TmpValue[3] + "," + TmpValue[4] + "," + TmpValue[5] + ",'" + InsertDate1 + "'," + Math.Round(InsertValue, 4, MidpointRounding.AwayFromZero) + ")";
  143. }
  144. if (InsertDate2 == DateTime.Now.ToString("yyyy-MM-dd 00:15:00.000") && TmpValue[7] != 3)
  145. {
  146. query += "INSERT INTO " + Table.Daily + "(SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,DailyValue,MaxValue,MinValue) "
  147. + "VALUES(" + TmpValue[2] + "," + TmpValue[3] + "," + TmpValue[4] + "," + TmpValue[5] + ",'" + Convert.ToDateTime(InsertDate2).ToString("yyyy-MM-dd 00:00:00.000") + "'," + Math.Round(InsertValue, 4, MidpointRounding.AwayFromZero) + "," + Math.Round(InsertValue, 4, MidpointRounding.AwayFromZero) + "," + Math.Round(InsertValue, 4, MidpointRounding.AwayFromZero) + ")";
  148. }
  149. using (SqlCommand cmd = new SqlCommand(query, conn))
  150. {
  151. cmd.ExecuteNonQuery();
  152. cmd.Dispose();
  153. conn.Close();
  154. }
  155. }
  156. }
  157. conn.Dispose();
  158. }
  159. }
  160. catch (SqlException e)
  161. {
  162. if (Form1.Enable_EnglishMode)//영어
  163. {
  164. LFC.Log("[DBinsert.cs] DB_Min_Insert Error : " + e + Environment.NewLine + Environment.NewLine);
  165. LFC.Log("[DBinsert.cs] DB_Min_Insert Error query: " + query);
  166. }
  167. else//한글
  168. {
  169. LFC.Log("[DBinsert.cs] DB_Min_Insert 에러 : " + e + Environment.NewLine + Environment.NewLine);
  170. LFC.Log("[DBinsert.cs] DB_Min_Insert 에러 쿼리문: " + query);
  171. }
  172. }
  173. }
  174. public void DB_Min_Insert_Now(IDictionary<string, float[]> MyDic, int NowMin)
  175. {
  176. LogFileCreate LFC = new LogFileCreate();
  177. string query = "";
  178. string InsertDate2 = DateTime.Now.ToString("yyyy-MM-dd HH:" + NowMin + ":00.000");
  179. string[] TmpStringValue = new string[8];
  180. string strcon = ConnDBInfo();
  181. float InsertValue = 0;
  182. using (SqlConnection conn = new SqlConnection(strcon))
  183. {
  184. foreach (KeyValuePair<string, float[]> kv in MyDic)
  185. {
  186. /// Key값과 Value값을 담기 위한 TKey, TValue
  187. string TmpPoint = kv.Key;
  188. float[] TmpValue = kv.Value;
  189. if (TmpValue[7] != 7)
  190. {
  191. if (TmpValue[7] == 2 || TmpValue[7] == 5) //적산이거나 적산2인 경우.
  192. {
  193. InsertValue = 0;
  194. }
  195. else if (TmpValue[7] == 6) // 현재값2인 경우
  196. {
  197. if (TmpValue[13] > 0)
  198. {
  199. InsertValue = 1;
  200. }
  201. else
  202. InsertValue = 0;
  203. }
  204. else
  205. {
  206. InsertValue = TmpValue[13];
  207. }
  208. conn.Open();
  209. query = "INSERT INTO " + Table.FiFt + "(SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,CurrentValue) "
  210. + "VALUES(" + TmpValue[2] + "," + TmpValue[3] + "," + TmpValue[4] + "," + TmpValue[5] + ",'" + InsertDate2 + "'," + Math.Round(InsertValue, 4, MidpointRounding.AwayFromZero) + ")";
  211. SqlCommand cmd = new SqlCommand(query, conn);
  212. cmd.ExecuteNonQuery();
  213. cmd.Dispose();
  214. conn.Close();
  215. }
  216. }
  217. conn.Dispose();
  218. }
  219. }
  220. public void DB_Hour_Update(IDictionary<string, float[]> MyDic, int NowMin, string Hour)
  221. {
  222. string query = "";
  223. string ConnInfo = "";
  224. string strcon = ConnDBInfo();
  225. string HourDate = "";
  226. string NowHourDate = "";
  227. if (NowMin == 0) //XX시 00분일때는 Hourly의 값이 XX-1에 들어가야함.
  228. {
  229. HourDate = Convert.ToDateTime(Hour).AddHours(-1).ToString("yyyy-MM-dd HH:00:00.000");
  230. }
  231. else
  232. {
  233. HourDate = Hour; // 그렇지 않다면 그냥 HOur
  234. }
  235. My_DB_DML MDD = new My_DB_DML();
  236. ReturnValue RV = new ReturnValue();
  237. ConnInfo = MDD.ConnDBInfo(); // DB접속정보를 담고 있음.
  238. using (SqlConnection conn = new SqlConnection(strcon))
  239. {
  240. foreach (KeyValuePair<string, float[]> kv in MyDic)
  241. {
  242. /// Key값과 Value값을 담기 위한 TKey, TValue
  243. string TmpPoint = kv.Key;
  244. float[] TmpValue = kv.Value;
  245. if (TmpValue[7] != 7) // 7이란 값 변경에 대한것을 의미..
  246. {
  247. conn.Open(); // 접속
  248. if (TmpValue[7] == 2 && TmpValue[12] < 0) //적산값인데 0보다 작으면...
  249. {
  250. TmpValue[12] = 0;
  251. }
  252. if (NowMin == 15) // XX시 15분이라면. 무조건 Insert.
  253. {
  254. query = "INSERT INTO " + Table.Hourly + "(SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,CurrentValue)"
  255. + " VALUES(" + TmpValue[2] + ", "
  256. + "" + TmpValue[3] + ", "
  257. + "" + TmpValue[4] + ", "
  258. + "" + TmpValue[5] + ", "
  259. + "'" + HourDate + "', "
  260. + "" + Math.Round(TmpValue[12], 4, MidpointRounding.AwayFromZero) + ")";
  261. }
  262. else // 그렇지 않다면 Update
  263. {
  264. if (TmpValue[7] == 3 && NowMin == 0) //현재값이라면..
  265. {
  266. NowHourDate = Convert.ToDateTime(Hour).ToString("yyyy-MM-dd HH:00:00.000");
  267. query = "INSERT INTO " + Table.Hourly + "(SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,CurrentValue)"
  268. + " VALUES(" + TmpValue[2] + ", "
  269. + "" + TmpValue[3] + ", "
  270. + "" + TmpValue[4] + ", "
  271. + "" + TmpValue[5] + ", "
  272. + "'" + NowHourDate + "', "
  273. + "" + Math.Round(TmpValue[12], 4, MidpointRounding.AwayFromZero) + ")";
  274. }
  275. else
  276. {
  277. query = "UPDATE " + Table.Hourly + " SET CurrentValue = " + Math.Round(TmpValue[12], 4, MidpointRounding.AwayFromZero) + " WHERE "
  278. + "SiteId = " + TmpValue[2] + " "
  279. + "AND FacilityTypeId = " + TmpValue[3] + " "
  280. + "AND FacilityCode = " + TmpValue[4] + " "
  281. + "AND PropertyId = " + TmpValue[5] + " "
  282. + "AND CreatedDateTime = '" + HourDate + "'";
  283. }
  284. }
  285. SqlCommand cmd = new SqlCommand(query, conn);
  286. cmd.ExecuteNonQuery();
  287. cmd.Dispose();
  288. conn.Close();
  289. }
  290. }
  291. conn.Dispose();
  292. }
  293. }
  294. #region StringBuilder
  295. private StringBuilder Up_Avg_sb = new StringBuilder();
  296. private StringBuilder Tmp1_sb = new StringBuilder();
  297. private StringBuilder Up_Sum_sb = new StringBuilder();
  298. private StringBuilder Tmp2_sb = new StringBuilder();
  299. private StringBuilder Up_Sum2_sb = new StringBuilder();
  300. private StringBuilder Tmp3_sb = new StringBuilder();
  301. private StringBuilder Up_On_sb = new StringBuilder();
  302. private StringBuilder Tmp4_sb = new StringBuilder();
  303. private StringBuilder Tmp5_sb = new StringBuilder();
  304. private StringBuilder Tmp6_sb = new StringBuilder();
  305. private StringBuilder Tmp7_sb = new StringBuilder();
  306. private StringBuilder Tmp8_sb = new StringBuilder();
  307. private StringBuilder Tmp9_sb = new StringBuilder();
  308. private StringBuilder Tmp10_sb = new StringBuilder();
  309. #endregion
  310. public void DB_Day_Update(IDictionary<string, float[]> MyDic, int NowMin, string Hour)
  311. {
  312. string ConnInfo = "";
  313. string strcon = ConnDBInfo();
  314. string DailyDate = "";
  315. string Max_DailyDate = "";
  316. string Min_DailyDate = "";
  317. float[] SelectValue = new float[2];
  318. if (NowMin == 00 && (Convert.ToDateTime(Hour).ToString("HH") == "00"))
  319. {
  320. DailyDate = Convert.ToDateTime(Hour).AddDays(-1).ToString("yyyy-MM-dd 00:00:00.000");
  321. LogFileCreate LFC = new LogFileCreate();
  322. if (Form1.Enable_EnglishMode)//영어
  323. {
  324. LFC.Log("[DBinsert.cs] DailyDate at 00:00= " + DailyDate + Environment.NewLine);
  325. }
  326. else//한글
  327. {
  328. LFC.Log("[DBinsert.cs] 00시00분 일때의 DailyDate = " + DailyDate + Environment.NewLine);
  329. }
  330. Max_DailyDate = Convert.ToDateTime(Hour).AddDays(-1).ToString("yyyy-MM-dd 00:00:00.000");
  331. Min_DailyDate = Convert.ToDateTime(Hour).AddDays(-1).ToString("yyyy-MM-dd 00:00:00.000");
  332. }
  333. else
  334. {
  335. DailyDate = Convert.ToDateTime(Hour).ToString("yyyy-MM-dd 00:00:00.000");
  336. Max_DailyDate = Convert.ToDateTime(Hour).ToString("yyyy-MM-dd 00:00:00.000");
  337. Min_DailyDate = Convert.ToDateTime(Hour).ToString("yyyy-MM-dd 00:00:00.000");
  338. }
  339. My_DB_DML MDD = new My_DB_DML();
  340. ReturnValue RV = new ReturnValue();
  341. ConnInfo = MDD.ConnDBInfo(); // DB접속정보를 담고 있음.
  342. using (SqlConnection conn = new SqlConnection(strcon))
  343. {
  344. foreach (KeyValuePair<string, float[]> kv in MyDic)
  345. {
  346. object o = 0;
  347. object o2 = 0;
  348. object o3 = 0;
  349. object o4 = 0;
  350. string query = "";
  351. string returnquery = "";
  352. float InsertDailyValue = 0;
  353. float AvgDailyValue = 0;
  354. float SumDailyValue = 0;
  355. float Sum2DailyValue = 0;
  356. float OnDailyValue = 0;
  357. /// Key값과 Value값을 담기 위한 TKey, TValue
  358. string TmpPoint = kv.Key;
  359. float[] TmpValue = kv.Value;
  360. if (TmpValue[7] != 3)
  361. {
  362. switch (NowMin)
  363. {
  364. case 15:
  365. InsertDailyValue = (TmpValue[8]);
  366. break;
  367. case 30:
  368. InsertDailyValue = (TmpValue[9]);
  369. break;
  370. case 45:
  371. InsertDailyValue = (TmpValue[10]);
  372. break;
  373. case 00:
  374. InsertDailyValue = (TmpValue[11]);
  375. break;
  376. }
  377. }
  378. else // 3일때.
  379. {
  380. InsertDailyValue = TmpValue[12];
  381. }
  382. if (TmpValue[7] == 2 && InsertDailyValue < 0) //적산값인데 0보다 작으면...
  383. {
  384. InsertDailyValue = 0;
  385. }
  386. conn.Open();
  387. switch (Convert.ToInt32(TmpValue[7]))
  388. {
  389. case 0:
  390. return;
  391. case 1:
  392. Up_Avg_sb.Append("SELECT AVG(CurrentValue) FROM BemsMonitoringPointHistory15min ");
  393. Up_Avg_sb.Append("WHERE CreatedDateTime BETWEEN '" + Convert.ToDateTime(DailyDate).ToString("yyyy-MM-dd 00:00:00") + "'");
  394. Up_Avg_sb.Append("AND '" + Convert.ToDateTime(DailyDate).ToString("yyyy-MM-dd 23:45:00") + "' ");
  395. Up_Avg_sb.Append("AND SiteId = " + TmpValue[2] + " ");
  396. Up_Avg_sb.Append("AND FacilityTypeId = " + TmpValue[3] + " ");
  397. Up_Avg_sb.Append("AND FacilityCode = " + TmpValue[4] + " ");
  398. Up_Avg_sb.Append("AND PropertyId = " + TmpValue[5] + " ");
  399. returnquery = Up_Avg_sb.ToString();
  400. SqlCommand cmd = new SqlCommand(returnquery, conn);
  401. o = cmd.ExecuteScalar();
  402. cmd.Dispose();
  403. Up_Avg_sb.Clear();
  404. if (o.GetType() != typeof(DBNull))
  405. {
  406. AvgDailyValue = (float)Convert.ToDouble(o);
  407. }
  408. else
  409. {
  410. return;
  411. }
  412. Tmp1_sb.Append("UPDATE ");
  413. Tmp1_sb.Append(Table.Daily);
  414. Tmp1_sb.Append(" SET DailyValue = ");
  415. Tmp1_sb.Append(Math.Round(AvgDailyValue, 4, MidpointRounding.AwayFromZero));
  416. Tmp1_sb.Append(" WHERE ");
  417. query = Tmp1_sb.ToString();
  418. Tmp1_sb.Clear();
  419. break;
  420. case 2:
  421. Up_Sum_sb.Append("SELECT SUM(CurrentValue) FROM BemsMonitoringPointHistory15min ");
  422. Up_Sum_sb.Append("WHERE CreatedDateTime BETWEEN '" + Convert.ToDateTime(DailyDate).ToString("yyyy-MM-dd 00:00:00") + "'");
  423. Up_Sum_sb.Append("AND '" + Convert.ToDateTime(DailyDate).ToString("yyyy-MM-dd 23:45:00") + "' ");
  424. Up_Sum_sb.Append("AND SiteId = " + TmpValue[2] + " ");
  425. Up_Sum_sb.Append("AND FacilityTypeId = " + TmpValue[3] + " ");
  426. Up_Sum_sb.Append("AND FacilityCode = " + TmpValue[4] + " ");
  427. Up_Sum_sb.Append("AND PropertyId = " + TmpValue[5] + " ");
  428. returnquery = Up_Sum_sb.ToString();
  429. SqlCommand cmd2 = new SqlCommand(returnquery, conn);
  430. o2 = cmd2.ExecuteScalar();
  431. cmd2.Dispose();
  432. Up_Sum_sb.Clear();
  433. if (o2.GetType() != typeof(DBNull))
  434. {
  435. SumDailyValue = (float)Convert.ToDouble(o2);
  436. }
  437. else
  438. {
  439. return;
  440. }
  441. Tmp2_sb.Append("UPDATE ");
  442. Tmp2_sb.Append(Table.Daily);
  443. Tmp2_sb.Append(" SET DailyValue = ");
  444. Tmp2_sb.Append(Math.Round(SumDailyValue, 4, MidpointRounding.AwayFromZero));
  445. Tmp2_sb.Append(" WHERE ");
  446. query = Tmp2_sb.ToString();
  447. Tmp2_sb.Clear();
  448. break;
  449. case 3:
  450. if (NowMin == 00 && (Convert.ToDateTime(Hour).ToString("HH") == "00"))
  451. {
  452. string tmpDailyDate = Convert.ToDateTime(Hour).ToString("yyyy-MM-dd 00:00:00.000");
  453. Tmp3_sb.Append("INSERT INTO ");
  454. Tmp3_sb.Append(Table.Daily);
  455. Tmp3_sb.Append("(SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,DailyValue,MaxValue,MinValue)");
  456. Tmp3_sb.Append(" VALUES(" + TmpValue[2] + ", " + TmpValue[3] + ", " + TmpValue[4] + ", " + TmpValue[5] + ", '" + tmpDailyDate + "', " + Math.Round(TmpValue[12], 4, MidpointRounding.AwayFromZero) + ", " + Math.Round(TmpValue[12], 4, MidpointRounding.AwayFromZero) + ", " + Math.Round(TmpValue[12], 4, MidpointRounding.AwayFromZero) + ")");
  457. query = Tmp3_sb.ToString();
  458. Tmp3_sb.Clear();
  459. }
  460. break;
  461. case 4:
  462. Up_On_sb.Append("SELECT SUM(CurrentValue) FROM BemsMonitoringPointHistory15min ");
  463. Up_On_sb.Append("WHERE CreatedDateTime BETWEEN '" + Convert.ToDateTime(DailyDate).ToString("yyyy-MM-dd 00:00:00") + "'");
  464. Up_On_sb.Append("AND '" + Convert.ToDateTime(DailyDate).ToString("yyyy-MM-dd 23:45:00") + "' ");
  465. Up_On_sb.Append("AND SiteId = " + TmpValue[2] + " ");
  466. Up_On_sb.Append("AND FacilityTypeId = " + TmpValue[3] + " ");
  467. Up_On_sb.Append("AND FacilityCode = " + TmpValue[4] + " ");
  468. Up_On_sb.Append("AND PropertyId = " + TmpValue[5] + " ");
  469. returnquery = Up_On_sb.ToString();
  470. SqlCommand cmd3 = new SqlCommand(returnquery, conn);
  471. o3 = cmd3.ExecuteScalar();
  472. cmd3.Dispose();
  473. Up_On_sb.Clear();
  474. if (o3.GetType() != typeof(DBNull))
  475. {
  476. OnDailyValue = (float)Convert.ToDouble(o3);
  477. }
  478. else
  479. {
  480. return;
  481. }
  482. Tmp4_sb.Append("UPDATE ");
  483. Tmp4_sb.Append(Table.Daily);
  484. Tmp4_sb.Append(" SET DailyValue = ");
  485. Tmp4_sb.Append(Math.Round(OnDailyValue, 4, MidpointRounding.AwayFromZero));
  486. Tmp4_sb.Append(" WHERE ");
  487. query = Tmp4_sb.ToString();
  488. Tmp4_sb.Clear();
  489. break;
  490. case 5:
  491. Up_Sum2_sb.Append("SELECT SUM(CurrentValue) FROM BemsMonitoringPointHistory15min ");
  492. Up_Sum2_sb.Append("WHERE CreatedDateTime BETWEEN '" + Convert.ToDateTime(DailyDate).ToString("yyyy-MM-dd 00:00:00") + "'");
  493. Up_Sum2_sb.Append("AND '" + Convert.ToDateTime(DailyDate).ToString("yyyy-MM-dd 23:45:00") + "' ");
  494. Up_Sum2_sb.Append("AND SiteId = " + TmpValue[2] + " ");
  495. Up_Sum2_sb.Append("AND FacilityTypeId = " + TmpValue[3] + " ");
  496. Up_Sum2_sb.Append("AND FacilityCode = " + TmpValue[4] + " ");
  497. Up_Sum2_sb.Append("AND PropertyId = " + TmpValue[5] + " ");
  498. returnquery = Up_Sum2_sb.ToString();
  499. SqlCommand cmd4 = new SqlCommand(returnquery, conn);
  500. o4 = cmd4.ExecuteScalar();
  501. cmd4.Dispose();
  502. Up_Sum2_sb.Clear();
  503. if (o4.GetType() != typeof(DBNull))
  504. {
  505. Sum2DailyValue = (float)Convert.ToDouble(o4);
  506. }
  507. else
  508. {
  509. return;
  510. }
  511. Tmp5_sb.Append("UPDATE ");
  512. Tmp5_sb.Append(Table.Daily);
  513. Tmp5_sb.Append(" SET DailyValue = ");
  514. Tmp5_sb.Append(Math.Round(Sum2DailyValue, 4, MidpointRounding.AwayFromZero));
  515. Tmp5_sb.Append(" WHERE ");
  516. query = Tmp5_sb.ToString();
  517. Tmp5_sb.Clear();
  518. break;
  519. case 6:
  520. Tmp6_sb.Append("UPDATE ");
  521. Tmp6_sb.Append(Table.Daily);
  522. Tmp6_sb.Append(" SET DailyValue = ");
  523. Tmp6_sb.Append(Math.Round(InsertDailyValue, 4, MidpointRounding.AwayFromZero));
  524. Tmp6_sb.Append(" WHERE ");
  525. query = Tmp6_sb.ToString();
  526. Tmp6_sb.Clear();
  527. break;
  528. case 7:
  529. break;
  530. case 8:
  531. break;
  532. }
  533. if (TmpValue[7] != 7)
  534. {
  535. if (TmpValue[7] != 3)
  536. {
  537. Tmp7_sb.Append("SiteId = ");
  538. Tmp7_sb.Append(TmpValue[2]);
  539. Tmp7_sb.Append(" AND FacilityTypeId = ");
  540. Tmp7_sb.Append(TmpValue[3]);
  541. Tmp7_sb.Append(" AND FacilityCode = ");
  542. Tmp7_sb.Append(TmpValue[4]);
  543. Tmp7_sb.Append(" AND PropertyId = ");
  544. Tmp7_sb.Append(TmpValue[5]);
  545. Tmp7_sb.Append(" AND CreatedDateTime = '" + DailyDate + "'");
  546. query += Tmp7_sb.ToString();
  547. Tmp7_sb.Clear();
  548. }
  549. Tmp8_sb.Append(" UPDATE ");
  550. Tmp8_sb.Append(Table.Daily);
  551. Tmp8_sb.Append(" SET MaxValue = CASE WHEN MaxValue <= ");
  552. Tmp8_sb.Append(Math.Round(InsertDailyValue, 4, MidpointRounding.AwayFromZero));
  553. Tmp8_sb.Append(" THEN ");
  554. Tmp8_sb.Append(Math.Round(InsertDailyValue, 4, MidpointRounding.AwayFromZero));
  555. Tmp8_sb.Append(" WHEN MaxValue >= ");
  556. Tmp8_sb.Append(Math.Round(InsertDailyValue, 4, MidpointRounding.AwayFromZero));
  557. Tmp8_sb.Append(" THEN MaxValue END ");
  558. Tmp8_sb.Append("WHERE SiteId = ");
  559. Tmp8_sb.Append(TmpValue[2]);
  560. Tmp8_sb.Append(" AND FacilityTypeId = ");
  561. Tmp8_sb.Append(TmpValue[3]);
  562. Tmp8_sb.Append(" AND FacilityCode = ");
  563. Tmp8_sb.Append(TmpValue[4]);
  564. Tmp8_sb.Append(" AND PropertyId = ");
  565. Tmp8_sb.Append(TmpValue[5]);
  566. Tmp8_sb.Append(" AND CreatedDateTime = '");
  567. Tmp8_sb.Append(Max_DailyDate);
  568. Tmp8_sb.Append("'");
  569. query += Tmp8_sb.ToString();
  570. Tmp8_sb.Clear();
  571. Tmp9_sb.Append(" UPDATE ");
  572. Tmp9_sb.Append(Table.Daily);
  573. Tmp9_sb.Append(" SET MinValue = CASE WHEN MinValue >= ");
  574. Tmp9_sb.Append(Math.Round(InsertDailyValue, 4, MidpointRounding.AwayFromZero));
  575. Tmp9_sb.Append(" THEN ");
  576. Tmp9_sb.Append(Math.Round(InsertDailyValue, 4, MidpointRounding.AwayFromZero));
  577. Tmp9_sb.Append(" WHEN MinValue <= ");
  578. Tmp9_sb.Append(Math.Round(InsertDailyValue, 4, MidpointRounding.AwayFromZero));
  579. Tmp9_sb.Append(" THEN MinValue END ");
  580. Tmp9_sb.Append("WHERE SiteId = ");
  581. Tmp9_sb.Append(TmpValue[2]);
  582. Tmp9_sb.Append(" AND FacilityTypeId = ");
  583. Tmp9_sb.Append(TmpValue[3]);
  584. Tmp9_sb.Append(" AND FacilityCode = ");
  585. Tmp9_sb.Append(TmpValue[4]);
  586. Tmp9_sb.Append(" AND PropertyId = ");
  587. Tmp9_sb.Append(TmpValue[5]);
  588. Tmp9_sb.Append(" AND CreatedDateTime = '");
  589. Tmp9_sb.Append(Min_DailyDate);
  590. Tmp9_sb.Append("'");
  591. query += Tmp9_sb.ToString();
  592. SqlCommand cmd7 = new SqlCommand(query, conn);
  593. cmd7.ExecuteNonQuery();
  594. cmd7.Dispose();
  595. Tmp9_sb.Clear();
  596. }
  597. conn.Close();
  598. }
  599. conn.Dispose();
  600. }
  601. }
  602. private StringBuilder TmpEx1_sb = new StringBuilder();
  603. private StringBuilder TmpEx2_sb = new StringBuilder();
  604. public void Expression_Insert(IDictionary<string, float[]> MyDic, int NowMin)
  605. {
  606. string query = "";
  607. string InsertDate = DateTime.Now.ToString("yyyy-MM-dd HH:" + NowMin + ":00.000");
  608. string[] TmpStringValue = new string[8];
  609. float InsertValue = 0;
  610. string strcon = ConnDBInfo();
  611. using (SqlConnection conn = new SqlConnection(strcon))
  612. {
  613. foreach (KeyValuePair<string, float[]> kv in MyDic)
  614. {
  615. // Key값과 Value값을 담기 위한 TKey, TValue
  616. string TmpPoint = kv.Key;
  617. float[] TmpValue = kv.Value;
  618. conn.Open();
  619. switch (NowMin)
  620. {
  621. case 15:
  622. InsertValue = TmpValue[8];
  623. break;
  624. case 30:
  625. InsertValue = TmpValue[9];
  626. break;
  627. case 45:
  628. InsertValue = TmpValue[10];
  629. break;
  630. case 00:
  631. InsertValue = TmpValue[11];
  632. break;
  633. }
  634. {
  635. TmpEx1_sb.Append("INSERT INTO ");
  636. TmpEx1_sb.Append(Table.FiFt);
  637. TmpEx1_sb.Append("(SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,CurrentValue) ");
  638. TmpEx1_sb.Append("VALUES(");
  639. TmpEx1_sb.Append(TmpValue[2]);
  640. TmpEx1_sb.Append(",");
  641. TmpEx1_sb.Append(TmpValue[3]);
  642. TmpEx1_sb.Append(",");
  643. TmpEx1_sb.Append(TmpValue[4]);
  644. TmpEx1_sb.Append(",");
  645. TmpEx1_sb.Append(TmpValue[5]);
  646. TmpEx1_sb.Append(",'");
  647. TmpEx1_sb.Append(InsertDate);
  648. TmpEx1_sb.Append("',");
  649. TmpEx1_sb.Append(Math.Round(InsertValue, 4, MidpointRounding.AwayFromZero));
  650. TmpEx1_sb.Append(")");
  651. query = TmpEx1_sb.ToString();
  652. TmpEx1_sb.Clear();
  653. }
  654. if (InsertDate == DateTime.Now.ToString("yyyy-MM-dd 00:15:00.000"))
  655. {
  656. TmpEx2_sb.Append("INSERT INTO ");
  657. TmpEx2_sb.Append(Table.Daily);
  658. TmpEx2_sb.Append("(SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,DailyValue,MaxValue,MinValue) ");
  659. TmpEx2_sb.Append("VALUES(");
  660. TmpEx2_sb.Append(TmpValue[2]);
  661. TmpEx2_sb.Append(",");
  662. TmpEx2_sb.Append(TmpValue[3]);
  663. TmpEx2_sb.Append(",");
  664. TmpEx2_sb.Append(TmpValue[4]);
  665. TmpEx2_sb.Append(",");
  666. TmpEx2_sb.Append(TmpValue[5]);
  667. TmpEx2_sb.Append(",'");
  668. TmpEx2_sb.Append(Convert.ToDateTime(InsertDate).ToString("yyyy-MM-dd 00:00:00.000"));
  669. TmpEx2_sb.Append("',");
  670. TmpEx2_sb.Append(Math.Round(InsertValue, 4, MidpointRounding.AwayFromZero));
  671. TmpEx2_sb.Append(",");
  672. TmpEx2_sb.Append(Math.Round(InsertValue, 4, MidpointRounding.AwayFromZero));
  673. TmpEx2_sb.Append(",");
  674. TmpEx2_sb.Append(Math.Round(InsertValue, 4, MidpointRounding.AwayFromZero));
  675. TmpEx2_sb.Append(")");
  676. query += TmpEx2_sb.ToString();
  677. TmpEx2_sb.Clear();
  678. }
  679. SqlCommand cmd = new SqlCommand(query, conn);
  680. cmd.ExecuteNonQuery();
  681. cmd.Dispose();
  682. conn.Close();
  683. }
  684. conn.Dispose();
  685. }
  686. }
  687. }
  688. }