719013d2f1d16c24034aaf5772ddbb17624b5068.svn-base 36 KB

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