73d63029af665e55a57399c253a8947ade8e2973.svn-base 13 KB

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