8dc7510a476a0cb68795bb8f64e7d8c503f00572.svn-base 12 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Text;
  5. using System.Data.SqlClient;
  6. using System.IO;
  7. using System.Data;
  8. using System.Windows.Forms;
  9. namespace BEMSDataGateway
  10. {
  11. class FirstCheck
  12. {
  13. LogFileCreate LFC = new LogFileCreate();
  14. My_DB_DML MDD = new My_DB_DML();
  15. public float[] UpdateDay(string date, int[] Array) // 15분 데이터 하루치 최대, 최소, 특정값 산출
  16. {
  17. float[] updatevalue = new float[3];
  18. string strcon = MDD.ConnDBInfo();
  19. string query = "";
  20. float[] SelectValue = new float[2];
  21. string SearchDate = "";
  22. object o = 0;
  23. DateTime tmpDate = Convert.ToDateTime(date);
  24. SearchDate = tmpDate.ToString("yyyy-MM-dd 00:00:00");
  25. using (SqlConnection conn = new SqlConnection(strcon))
  26. {
  27. // [0] [1] [2] [3]
  28. string[] Check = { "SUM", "COUNT", "MIN", "MAX" };
  29. foreach (string ch in Check) //15분 데이터 하루치 합, 갯수, 최소, 최대 저장
  30. {
  31. query = "SELECT " + ch + "(CurrentValue) FROM BemsMonitoringPointHistory15min "
  32. + "WHERE CreatedDateTime BETWEEN '" + Convert.ToDateTime(SearchDate).ToString("yyyy-MM-dd 00:00:00") + "' "
  33. + "AND '" + Convert.ToDateTime(SearchDate).ToString("yyyy-MM-dd 23:45:00") + "' "
  34. + "AND SiteId = " + Array[2] + " "
  35. + "AND FacilityTypeId = " + Array[3] + " "
  36. + "AND FacilityCode = " + Array[4] + " "
  37. + "AND PropertyId = " + Array[5] + " ";
  38. SqlCommand cmd = new SqlCommand(query, conn);
  39. conn.Open();
  40. o = cmd.ExecuteScalar(); // 1,100,1,4
  41. if (o.GetType() != typeof(DBNull))
  42. {
  43. switch (ch)
  44. {
  45. case "SUM":
  46. SelectValue[0] = (float)Convert.ToDouble(o);
  47. break;
  48. case "COUNT":
  49. SelectValue[1] = (float)Convert.ToDouble(o);
  50. break;
  51. case "MIN":
  52. updatevalue[1] = (float)Convert.ToDouble(o);
  53. break;
  54. case "MAX":
  55. updatevalue[2] = (float)Convert.ToDouble(o);
  56. break;
  57. }
  58. }
  59. conn.Close();
  60. }
  61. switch (Array[7])
  62. {
  63. case 0: // Raw
  64. break;
  65. case 1: // 평균
  66. if (SelectValue[1] == 0) // 데이터 갯수가 0이면 1로 나눔
  67. {
  68. SelectValue[1] = 1;
  69. }
  70. updatevalue[0] = SelectValue[0] / SelectValue[1];
  71. break;
  72. case 2: // 적산 : 합하는 수치
  73. updatevalue[0] = SelectValue[0];
  74. break;
  75. case 3: // 현재
  76. break;
  77. case 4: // 가동시간
  78. updatevalue[0] = SelectValue[0];
  79. break;
  80. case 5: // 적산값
  81. updatevalue[0] = SelectValue[0];
  82. break;
  83. case 6: // 현재2
  84. if (SelectValue[0] > 0)
  85. {
  86. updatevalue[0] = 1;
  87. }
  88. else
  89. {
  90. updatevalue[0] = 0;
  91. }
  92. break;
  93. }
  94. }
  95. return updatevalue;
  96. }
  97. // DB접속 정보를 읽는 메서드
  98. public string ConnDBInfo()
  99. {
  100. Readini Ri = new Readini();
  101. Ri.ReadData();
  102. string strconInfo = @"Data Source = " + Ri.UserIP + "," + Ri.UserPort + "; "
  103. + "Initial Catalog =" + Ri.UserDB + "; "
  104. + "Persist Security Info=True; "
  105. + "User ID =" + Ri.UserID + "; "
  106. + "Password =" + Ri.UserPW + "";
  107. return strconInfo;
  108. }
  109. public void Check_DailyData()
  110. {
  111. FirstCheck_Progressbar FP = new FirstCheck_Progressbar();
  112. My_DB_DML MDD = new My_DB_DML();
  113. ReturnValue RV = new ReturnValue();
  114. string ConnInfo = "";
  115. string HourlyDate = "";
  116. int result = 0;
  117. string[] temp;
  118. string TmpDML = "";
  119. DateTime HourDate = DateTime.Now;
  120. int Min = int.Parse(HourDate.Minute.ToString());
  121. string Hour = (HourDate).ToString("yyyy-MM-dd HH:00:00.000");
  122. string Day = (HourDate).ToString("yyyy-MM-dd 00:00:00.000");
  123. string strcon = MDD.ConnDBInfo();
  124. FP.Start_progressBar();
  125. StreamReader sr = new StreamReader("SettingFile.csv", Encoding.GetEncoding("euc-kr")); // 파일 읽기.
  126. while (!sr.EndOfStream) // Stream 끝까지 읽음
  127. {
  128. string s = sr.ReadLine(); // 한줄 읽기
  129. if (!(s.Length > 0))
  130. {
  131. continue;
  132. }
  133. temp = s.Split(','); // ,구분하여 잘라냄
  134. float[] Return_Value;
  135. temp[1] = temp[1].Trim();
  136. string[] PointDivid = temp[0].Split('.');
  137. int[] values = new int[8];
  138. values[2] = int.Parse(PointDivid[0]); // SiteId
  139. values[3] = int.Parse(PointDivid[1]); // FacilityTypeId
  140. values[4] = int.Parse(PointDivid[2]); // FacilityCode
  141. values[5] = int.Parse(PointDivid[3]); // PropertyId
  142. values[7] = Convert.ToInt32(temp[2]); // 데이터 종류(현재값, 적산값 등...)
  143. if (values[7] == 7)
  144. {
  145. continue;
  146. }
  147. ConnInfo = MDD.ConnDBInfo(); // DB 접속 정보
  148. HourlyDate = Convert.ToDateTime(Hour).ToString("yyyy-MM-dd HH:00:00.000");
  149. result = RV.ExistValue(HourlyDate, ConnInfo, values); // 데이터 존재 여부 판단, result[0] = Hourly의 값, result[1]에는 Daily값
  150. Return_Value = UpdateDay(HourlyDate, values); // 15분 데이터 하루치 최대, 최소, 특정값 산출
  151. string TmpWhere = "WHERE SiteId = " + values[2] + " "
  152. + "AND FacilityTypeId = " + values[3] + " "
  153. + "AND FacilityCode = " + values[4] + " "
  154. + "AND PropertyId = " + values[5] + " ";
  155. //Daily 테이블 데일리값, 최대, 최소 값 업데이트
  156. string TmpDMLDayUP = " UPDATE " + Table.Daily + " SET DailyValue = " + Math.Round(Return_Value[0], 4, MidpointRounding.AwayFromZero) + ", MaxValue = " + Math.Round(Return_Value[2], 4, MidpointRounding.AwayFromZero) + ", MinValue = " + Math.Round(Return_Value[1], 4, MidpointRounding.AwayFromZero) + " " + TmpWhere + "AND CreatedDateTime = '" + Day + "'"; ;
  157. string TmpDMLHourIn = "";
  158. ////// INSERT 구문 //////
  159. if ((15 <= Min) && (Min < 60))
  160. {
  161. TmpDMLHourIn = " INSERT INTO " + Table.Hourly + "(SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,CurrentValue)"
  162. + " VALUES(" + values[2] + "," + values[3] + "," + values[4] + "," + values[5] + ",'" + Hour + "'," + 0 + ") ";
  163. }
  164. string TmpDMLDayIn = " INSERT INTO " + Table.Daily + "(SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,DailyValue,MaxValue,MinValue)"
  165. + " VALUES(" + values[2] + "," + values[3] + "," + values[4] + "," + values[5] + ",'" + Day + "'," + Math.Round(Return_Value[0], 4, MidpointRounding.AwayFromZero) + "," + Math.Round(Return_Value[2], 4, MidpointRounding.AwayFromZero) + "," + Math.Round(Return_Value[1], 4, MidpointRounding.AwayFromZero) + ") ";
  166. //쿼리를 조합해주는 구문
  167. switch (result)
  168. {
  169. case 1: // 시간, 데일리 테이블 삽입
  170. TmpDML = TmpDMLHourIn + TmpDMLDayIn;
  171. break;
  172. case 2: // 시간 테이블 삽입, 데일리 테이블 업데이트
  173. TmpDML = TmpDMLHourIn + TmpDMLDayUP;
  174. break;
  175. case 3: // 데일리 테이블 삽입
  176. TmpDML = TmpDMLDayIn;
  177. break;
  178. case 4: // 데일리 테이블 업데이트
  179. TmpDML = TmpDMLDayUP;
  180. break;
  181. }
  182. //LFC.Log("[FirstCheck.cs] Hour, Daily 테이블 삽입 및 업데이트" + Environment.NewLine);
  183. using (SqlConnection conn = new SqlConnection(strcon))
  184. {
  185. try
  186. {
  187. conn.Open();
  188. SqlCommand cmd = new SqlCommand(TmpDML, conn);
  189. cmd.ExecuteNonQuery();
  190. conn.Close();
  191. }
  192. catch (Exception e)
  193. {
  194. LFC.Log("[FirstCheck.cs] Intsert에러 발생 : " + e + Environment.NewLine);
  195. }
  196. }
  197. }
  198. FP.Stop_progressBar();
  199. }
  200. }
  201. /// <summary>
  202. /// 값의 존재유무를 확인하기 위한 클래스
  203. /// </summary>
  204. class ReturnValue
  205. {
  206. int[] reader = new int[2]; // Hourly, Daily의 존부 여부 결과를 담기 위한 변수
  207. public int ExistValue(string Date, string strcon, int[] Arr)
  208. {
  209. int result = 0;
  210. using (SqlConnection conn = new SqlConnection(strcon))
  211. {
  212. string TmpDML = "";
  213. for (int Check = 0; Check < 2; Check++) // 값 존재 여부 파악 쿼리문
  214. {
  215. switch (Check)
  216. {
  217. case 0: // Hourly 유무
  218. TmpDML = "SELECT COUNT(CurrentValue) FROM " + Table.Hourly + " "
  219. + "WHERE CreatedDateTime = '" + Date + "'";
  220. break;
  221. case 1: // Daily 유무
  222. TmpDML = "SELECT Count(DailyValue) FROM " + Table.Daily + " "
  223. + "WHERE CreatedDateTime = '" + Convert.ToDateTime(Date).ToString("yyyy-MM-dd 00:00:00.000") + "'";
  224. break;
  225. }
  226. TmpDML += "AND SiteId = " + Arr[2] + " "
  227. + "AND FacilityTypeId = " + Arr[3] + " "
  228. + "AND FacilityCode = " + Arr[4] + " "
  229. + "AND PropertyId = " + Arr[5] + "";
  230. SqlCommand cmd = new SqlCommand(TmpDML, conn);
  231. conn.Open();
  232. reader[Check] = Convert.ToInt32(cmd.ExecuteScalar());
  233. conn.Close();
  234. result = ResultDataArr(reader);
  235. }
  236. return result;
  237. }
  238. }
  239. //결과를 상수로 넘겨 주기 위한 메서드
  240. public int ResultDataArr(int[] arr)
  241. {
  242. int ResultInt = 0;
  243. if (arr[0] == 0 && arr[1] == 0)
  244. {
  245. ResultInt = 1; // Daily, Hourly 둘다 없는 경우
  246. }
  247. else if (arr[0] == 0 && arr[1] == 1)
  248. {
  249. ResultInt = 2; // Daily만 있는 경우
  250. }
  251. else if (arr[0] == 1 && arr[1] == 0)
  252. {
  253. ResultInt = 3; // Hourly만 있는 경우
  254. }
  255. else if (arr[0] == 1 && arr[1] == 1)
  256. {
  257. ResultInt = 4; // Daily, Hourly 둘다 있는 경우
  258. }
  259. return ResultInt;
  260. }
  261. }
  262. }