aa54c0ebf512e56548c15b53f0676d6a58dfa071.svn-base 35 KB

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