d8b9b7ae74de9c878605bca26ea6d2b91d868322.svn-base 30 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553
  1. using iBemsDataService.Model;
  2. using Quartz;
  3. using System;
  4. using System.Collections.Generic;
  5. using System.Linq;
  6. using System.Transactions;
  7. using System.Web;
  8. using iBemsDataService.Controllers.Bems.Common;
  9. using System.Collections;
  10. using System.Configuration;
  11. namespace iBemsDataService.Controllers.Fms.WorkManagement.ScheduledTask
  12. {
  13. public class AlarmPlanJob : IJob
  14. {
  15. private iBemsEntities db = new iBemsEntities();
  16. public void Execute(IJobExecutionContext context)
  17. {
  18. // 알람 실행 함수
  19. AlarmExecute();
  20. // 전력 피크 실행 함수
  21. PowerPeakExecute();
  22. }
  23. // 알람 실행 함수
  24. public void AlarmExecute()
  25. {
  26. // 현재시간
  27. DateTime today = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
  28. if (today.ToString("mm").ToString() == "16" || today.ToString("mm").ToString() == "31" ||
  29. today.ToString("mm").ToString() == "46" || today.ToString("mm").ToString() == "01")
  30. {
  31. ArrayList ArrayList_AlarmSetting = new ArrayList();
  32. using (var transaction = new TransactionScope())
  33. {
  34. var query =
  35. from s in db.BemsAlarmSetting
  36. where s.UseAlarm == true // 알람을 사용하는것만 조회
  37. select s;
  38. foreach (var s in query)
  39. {
  40. AlarmSetting _AlarmSetting = new AlarmSetting();
  41. _AlarmSetting.SiteId = s.SiteId;
  42. _AlarmSetting.FacilityTypeId = s.FacilityTypeId;
  43. _AlarmSetting.FacilityCode = s.FacilityCode;
  44. _AlarmSetting.PropertyId = s.PropertyId;
  45. _AlarmSetting.FormulaId = s.FormulaId;
  46. _AlarmSetting.UseUpperLimit = s.UseUpperLimit;
  47. _AlarmSetting.UpperLimit = (double)s.UpperLimit;
  48. _AlarmSetting.UseLowerLimit = s.UseLowerLimit;
  49. _AlarmSetting.LowerLimit = (double)s.LowerLimit;
  50. _AlarmSetting.UseSMS = s.UseSMS;
  51. _AlarmSetting.UseEmail = s.UseEmail;
  52. _AlarmSetting.FacilityName = s.FacilityName;
  53. _AlarmSetting.PointName = s.PointName;
  54. _AlarmSetting.AlarmGroupName = s.AlarmGroupName;
  55. ArrayList_AlarmSetting.Add(_AlarmSetting);
  56. }
  57. transaction.Complete();
  58. }
  59. // 아래와 같이 분리해서 구현한 이유는 _BemsFormulaController.GetBemsFormulaReal 함수는 using (var transaction = new TransactionScope()) 안에 들어가면 안된다
  60. // 엔티티 프레임워크를 이용 할 경우에는 using (var transaction = new TransactionScope())를 무조건 사용해야 한다
  61. for (int i = 0; i < ArrayList_AlarmSetting.Count; i++)
  62. {
  63. AlarmSetting _AlarmSetting = (AlarmSetting)ArrayList_AlarmSetting[i];
  64. // 초 확인
  65. string second = today.ToString("ss");
  66. DateTime start = today.AddMinutes(-16);
  67. start = start.AddSeconds(-Int32.Parse(second));
  68. DateTime end = today.AddMinutes(-2);
  69. end = end.AddSeconds(-Int32.Parse(second));
  70. // 관제점 정보
  71. if (_AlarmSetting.FormulaId == 0)
  72. {
  73. using (var transaction = new TransactionScope())
  74. {
  75. var query = from s in db.BemsMonitoringPointHistory15min
  76. where s.SiteId == _AlarmSetting.SiteId &&
  77. s.FacilityCode == _AlarmSetting.FacilityCode &&
  78. s.PropertyId == _AlarmSetting.PropertyId &&
  79. s.CreatedDateTime >= start &&
  80. s.CreatedDateTime < end
  81. select s;
  82. foreach (var s in query)
  83. {
  84. _AlarmSetting.CreatedDateTime = s.CreatedDateTime;
  85. _AlarmSetting.CurrentValue = s.CurrentValue;
  86. ArrayList_AlarmSetting[i] = _AlarmSetting;
  87. }
  88. transaction.Complete();
  89. }
  90. }
  91. else // 성능분석 정보
  92. {
  93. List<CalculationResult> list = new List<CalculationResult>();
  94. BemsFormulaController _BemsFormulaController = new BemsFormulaController();
  95. list = _BemsFormulaController.GetBemsFormulaReal(_AlarmSetting.SiteId, _AlarmSetting.FacilityTypeId, _AlarmSetting.FacilityCode, _AlarmSetting.FormulaId, start, end, 1);
  96. _AlarmSetting.CreatedDateTime = list[0].DateTime;
  97. _AlarmSetting.CurrentValue = list[0].Value;
  98. ArrayList_AlarmSetting[i] = _AlarmSetting;
  99. }
  100. }
  101. // 아래와 같이 분리해서 구현한 이유는 _BemsFormulaController.GetBemsFormulaReal 함수는 using (var transaction = new TransactionScope()) 안에 들어가면 안된다
  102. // 실질적으로 아래 함수에서 알람 로그가 저장된다
  103. using (var transaction = new TransactionScope())
  104. {
  105. for (int i = 0; i < ArrayList_AlarmSetting.Count; i++)
  106. {
  107. AlarmSetting _AlarmSetting = (AlarmSetting)ArrayList_AlarmSetting[i];
  108. bool Exceed_Limit = false;
  109. // 상한값 비교
  110. if (_AlarmSetting.UseUpperLimit == true)
  111. {
  112. if (_AlarmSetting.CurrentValue >= Convert.ToDouble(_AlarmSetting.UpperLimit))
  113. {
  114. Exceed_Limit = true;
  115. }
  116. }
  117. // 하한값 비교
  118. if (_AlarmSetting.UseLowerLimit == true)
  119. {
  120. if (_AlarmSetting.CurrentValue <= Convert.ToDouble(_AlarmSetting.LowerLimit))
  121. {
  122. Exceed_Limit = true;
  123. }
  124. }
  125. // 기준을 초과했다
  126. if (Exceed_Limit == true)
  127. {
  128. // BemsAlarmLog 테이블에 추가
  129. var AlarmLog = new BemsAlarmLog
  130. {
  131. SiteId = _AlarmSetting.SiteId,
  132. FacilityTypeId = _AlarmSetting.FacilityTypeId,
  133. FacilityCode = _AlarmSetting.FacilityCode,
  134. PropertyId = _AlarmSetting.PropertyId,
  135. FormulaId = _AlarmSetting.FormulaId,
  136. CreatedDateTime = _AlarmSetting.CreatedDateTime,
  137. CurrentValue = _AlarmSetting.CurrentValue,
  138. SMSResult = "-",
  139. EmailResult = "-",
  140. Conform = false
  141. };
  142. AlarmLog = db.BemsAlarmLog.Add(AlarmLog);
  143. db.SaveChanges();
  144. // SMS 전송
  145. string SMS_Result = "-";
  146. if (_AlarmSetting.UseSMS == true)
  147. {
  148. int siteID = _AlarmSetting.SiteId;
  149. string usergroupname = _AlarmSetting.AlarmGroupName;
  150. using (var transaction1 = new TransactionScope())
  151. {
  152. var query1 = from s1 in db.BemsAlarmGroup
  153. where s1.SiteId == siteID &&
  154. s1.Name == usergroupname
  155. select s1;
  156. foreach (var s1 in query1)
  157. {
  158. int alarm_groupid = s1.AlarmGroupId;
  159. using (var transaction2 = new TransactionScope())
  160. {
  161. var query2 = from s2 in db.BemsAlarmGroupUser
  162. where s2.SiteId == siteID &&
  163. s2.AlarmGroupId == alarm_groupid
  164. select s2;
  165. foreach (var s2 in query2)
  166. {
  167. string userid = s2.UserId;
  168. using (var transaction3 = new TransactionScope())
  169. {
  170. var query3 = from s3 in db.CmUser
  171. where s3.SiteId == siteID &&
  172. s3.UserId == userid
  173. select s3;
  174. foreach (var s3 in query3)
  175. {
  176. string MobilePhoneNo = s3.MobilePhoneNo;
  177. if (MobilePhoneNo != "")
  178. {
  179. // "BEMS알람/07:30/공조기_AHU-101/공조기습도/44.09"
  180. AlarmConfigSMS _AlarmConfigSMS = new AlarmConfigSMS();
  181. // SMS 정보 DB에서 가져오기 2019.04.
  182. var query4 = from s4 in db.CmAlarmAdmin
  183. where s4.SiteId == siteID
  184. select s4;
  185. foreach (var s4 in query4)
  186. {
  187. string smsUsercode_s = s4.UserCode;
  188. string smsDeptcode_s = s4.DeptCode;
  189. string smsPhoneNo_s = s4.PhoneNo;
  190. _AlarmConfigSMS.SetSMSInfo(smsUsercode_s, smsDeptcode_s, smsPhoneNo_s);
  191. // SMS 정보 가져오기 2019.04.
  192. string SMSResult = _AlarmConfigSMS.SendAlarmSMS(MobilePhoneNo, "BEMS알람/" + _AlarmSetting.CreatedDateTime.ToString("HH:mm") + "/" +
  193. _AlarmSetting.FacilityName + "/" + _AlarmSetting.PointName + "/" + _AlarmSetting.CurrentValue);
  194. if (SMSResult == "OK") // 성공
  195. {
  196. if (SMS_Result == "실패")
  197. {
  198. SMS_Result = "실패";
  199. }
  200. else
  201. {
  202. SMS_Result = "성공";
  203. }
  204. }
  205. else // 실패
  206. {
  207. SMS_Result = "실패";
  208. }
  209. }
  210. }
  211. }
  212. transaction3.Complete();
  213. }
  214. }
  215. transaction2.Complete();
  216. }
  217. }
  218. transaction1.Complete();
  219. }
  220. }
  221. // Email 전송
  222. string Email_Result = "-";
  223. if (_AlarmSetting.UseEmail == true)
  224. {
  225. int siteID = _AlarmSetting.SiteId;
  226. string usergroupname = _AlarmSetting.AlarmGroupName;
  227. using (var transaction1 = new TransactionScope())
  228. {
  229. var query1 = from s1 in db.BemsAlarmGroup
  230. where s1.SiteId == siteID &&
  231. s1.Name == usergroupname
  232. select s1;
  233. foreach (var s1 in query1)
  234. {
  235. int alarm_groupid = s1.AlarmGroupId;
  236. using (var transaction2 = new TransactionScope())
  237. {
  238. var query2 = from s2 in db.BemsAlarmGroupUser
  239. where s2.SiteId == siteID &&
  240. s2.AlarmGroupId == alarm_groupid
  241. select s2;
  242. foreach (var s2 in query2)
  243. {
  244. string userid = s2.UserId;
  245. using (var transaction3 = new TransactionScope())
  246. {
  247. var query3 = from s3 in db.CmUser
  248. where s3.SiteId == siteID &&
  249. s3.UserId == userid
  250. select s3;
  251. foreach (var s3 in query3)
  252. {
  253. string Email = s3.Email;
  254. if (Email != "")
  255. {
  256. AlarmConfigEmail _AlarmConfigEmail = new AlarmConfigEmail();
  257. _AlarmConfigEmail.SetToAddress(Email);
  258. // Email 정보 설정 2019.04.
  259. var query4 = from s4 in db.CmAlarmAdmin
  260. where s4.SiteId == siteID
  261. select s4;
  262. foreach (var s4 in query4)
  263. {
  264. string emailaddress_s = s4.EmailAddress;
  265. string emailpassword_s = s4.EmailPassword;
  266. _AlarmConfigEmail.SetEmailInfo(emailaddress_s, emailpassword_s);
  267. // Email 정보 설정 2019.04.
  268. string temp = String.Empty;
  269. if (_AlarmSetting.UseUpperLimit == true) temp = temp + "/기준상한(이상) : " + _AlarmSetting.UpperLimit;
  270. if (_AlarmSetting.UseLowerLimit == true) temp = temp + "/기준하한(이하) : " + _AlarmSetting.LowerLimit;
  271. string EamilResult = _AlarmConfigEmail.SendEmail("BEMS알람",
  272. _AlarmSetting.CreatedDateTime + "/" + _AlarmSetting.FacilityName + "/" + _AlarmSetting.PointName +
  273. temp + "/" + "값 : " + _AlarmSetting.CurrentValue);
  274. if (EamilResult == "OK") // 성공
  275. {
  276. if (Email_Result == "실패")
  277. {
  278. Email_Result = "실패";
  279. }
  280. else
  281. {
  282. Email_Result = "성공";
  283. }
  284. }
  285. else // 실패
  286. {
  287. Email_Result = "실패";
  288. }
  289. }
  290. }
  291. }
  292. transaction3.Complete();
  293. }
  294. }
  295. transaction2.Complete();
  296. }
  297. }
  298. transaction1.Complete();
  299. }
  300. }
  301. // SMS 또는 Email을 사용중이다
  302. if (_AlarmSetting.UseSMS == true || _AlarmSetting.UseEmail == true)
  303. {
  304. // BemsAlarmLog 테이블 업데이트
  305. var update_BemsAlarmLog = (from d in db.BemsAlarmLog
  306. where d.SiteId == _AlarmSetting.SiteId &&
  307. d.FacilityTypeId == _AlarmSetting.FacilityTypeId &&
  308. d.FacilityCode == _AlarmSetting.FacilityCode &&
  309. d.PropertyId == _AlarmSetting.PropertyId &&
  310. d.FormulaId == _AlarmSetting.FormulaId &&
  311. d.CreatedDateTime == _AlarmSetting.CreatedDateTime
  312. select d).Single();
  313. update_BemsAlarmLog.SMSResult = SMS_Result;
  314. update_BemsAlarmLog.EmailResult = Email_Result;
  315. db.SaveChanges();
  316. }
  317. }
  318. }
  319. transaction.Complete();
  320. }
  321. }
  322. }
  323. // 전력 피크 실행 함수
  324. public void PowerPeakExecute()
  325. {
  326. int siteid = 1;
  327. if (ConfigurationManager.ConnectionStrings["siteid"] != null)
  328. {
  329. siteid = Convert.ToInt32(ConfigurationManager.ConnectionStrings["siteid"].ConnectionString);
  330. }
  331. DateTime start = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd 00:00:00"));
  332. DateTime end = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd HH:mm:00"));
  333. using (var transaction = new TransactionScope())
  334. {
  335. var Max =
  336. (from s in db.BemsPeakHistory
  337. where s.SiteId == siteid &&
  338. s.CreatedDateTime >= start &&
  339. s.CreatedDateTime <= end
  340. orderby s.CurrentValue descending
  341. select s).First();
  342. var Min =
  343. (from s in db.BemsPeakHistory
  344. where s.SiteId == siteid &&
  345. s.CreatedDateTime >= start &&
  346. s.CreatedDateTime <= end
  347. orderby s.CurrentValue ascending
  348. select s).First();
  349. var query =
  350. (from s in db.BemsPeakHistoryDaily
  351. where s.SiteId == siteid &&
  352. s.CreatedDateTime == start
  353. select s);
  354. if (query.Any() == false)
  355. {
  356. var newBemsPeakHistoryDaily = new BemsPeakHistoryDaily
  357. {
  358. SiteId = siteid,
  359. CreatedDateTime = start,
  360. MaxPeakValue = Max.CurrentValue,
  361. MaxDateTime = Max.CreatedDateTime,
  362. MinPeakValue = Min.CurrentValue,
  363. MinDateTime = Min.CreatedDateTime
  364. };
  365. db.BemsPeakHistoryDaily.Add(newBemsPeakHistoryDaily);
  366. db.SaveChanges();
  367. }
  368. else
  369. {
  370. var tempmaxvalue = query.First().MaxPeakValue;
  371. DateTime tempmaxdate = Convert.ToDateTime(query.First().MaxDateTime);
  372. var tempminvalue = query.First().MinPeakValue;
  373. DateTime tempmindate = Convert.ToDateTime(query.First().MinDateTime);
  374. if (tempmaxvalue < Max.CurrentValue)
  375. {
  376. tempmaxvalue = Max.CurrentValue;
  377. tempmaxdate = Max.CreatedDateTime;
  378. }
  379. if (tempminvalue > Min.CurrentValue)
  380. {
  381. tempminvalue = Min.CurrentValue;
  382. tempmindate = Min.CreatedDateTime;
  383. }
  384. var newBemsPeakHistoryDaily = new BemsPeakHistoryDaily
  385. {
  386. SiteId = siteid,
  387. CreatedDateTime = start,
  388. MaxPeakValue = tempmaxvalue,
  389. MaxDateTime = tempmaxdate,
  390. MinPeakValue = tempminvalue,
  391. MinDateTime = tempmindate
  392. };
  393. db.BemsPeakHistoryDaily.Remove(query.First());
  394. db.BemsPeakHistoryDaily.Add(newBemsPeakHistoryDaily);
  395. db.SaveChanges();
  396. }
  397. transaction.Complete();
  398. }
  399. // 옛날 전력 피크 업데이트 용
  400. /*
  401. for (var i = 0; i < 365; i++)
  402. {
  403. DateTime start = Convert.ToDateTime(DateTime.Now.ToString("2018-07-26 00:00:00"));
  404. DateTime end = Convert.ToDateTime(DateTime.Now.ToString("2018-07-26 23:59:00"));
  405. start = start.AddDays(i);
  406. end = end.AddDays(i);
  407. using (var transaction = new TransactionScope())
  408. {
  409. var Max =
  410. (from s in db.BemsPeakHistory
  411. where s.SiteId == siteid &&
  412. s.CreatedDateTime >= start &&
  413. s.CreatedDateTime <= end
  414. orderby s.CurrentValue descending
  415. select s);
  416. var Min =
  417. (from s in db.BemsPeakHistory
  418. where s.SiteId == siteid &&
  419. s.CreatedDateTime >= start &&
  420. s.CreatedDateTime <= end
  421. orderby s.CurrentValue ascending
  422. select s);
  423. var query =
  424. (from s in db.BemsPeakHistoryDaily
  425. where s.SiteId == siteid &&
  426. s.CreatedDateTime == start
  427. select s);
  428. if (Max.Any() != false && Min.Any() != false)
  429. {
  430. if (query.Any() == false)
  431. {
  432. var newBemsPeakHistoryDaily = new BemsPeakHistoryDaily
  433. {
  434. SiteId = siteid,
  435. CreatedDateTime = start,
  436. MaxPeakValue = Max.First().CurrentValue,
  437. MaxDateTime = Max.First().CreatedDateTime,
  438. MinPeakValue = Min.First().CurrentValue,
  439. MinDateTime = Min.First().CreatedDateTime
  440. };
  441. db.BemsPeakHistoryDaily.Add(newBemsPeakHistoryDaily);
  442. db.SaveChanges();
  443. }
  444. else
  445. {
  446. var tempmaxvalue = query.First().MaxPeakValue;
  447. DateTime tempmaxdate = Convert.ToDateTime(query.First().MaxDateTime);
  448. var tempminvalue = query.First().MinPeakValue;
  449. DateTime tempmindate = Convert.ToDateTime(query.First().MinDateTime);
  450. if (tempmaxvalue < Max.First().CurrentValue)
  451. {
  452. tempmaxvalue = Max.First().CurrentValue;
  453. tempmaxdate = Max.First().CreatedDateTime;
  454. }
  455. if (tempminvalue > Min.First().CurrentValue)
  456. {
  457. tempminvalue = Min.First().CurrentValue;
  458. tempmindate = Min.First().CreatedDateTime;
  459. }
  460. var newBemsPeakHistoryDaily = new BemsPeakHistoryDaily
  461. {
  462. SiteId = siteid,
  463. CreatedDateTime = start,
  464. MaxPeakValue = tempmaxvalue,
  465. MaxDateTime = tempmaxdate,
  466. MinPeakValue = tempminvalue,
  467. MinDateTime = tempmindate
  468. };
  469. db.BemsPeakHistoryDaily.Remove(query.First());
  470. db.BemsPeakHistoryDaily.Add(newBemsPeakHistoryDaily);
  471. db.SaveChanges();
  472. }
  473. }
  474. transaction.Complete();
  475. }
  476. }*/
  477. }
  478. public class AlarmSetting
  479. {
  480. public int SiteId { get; set; }
  481. public int FacilityTypeId { get; set; }
  482. public int FacilityCode { get; set; }
  483. public int PropertyId { get; set; }
  484. public int FormulaId { get; set; }
  485. public bool UseUpperLimit { get; set; }
  486. public double UpperLimit { get; set; }
  487. public bool UseLowerLimit { get; set; }
  488. public double LowerLimit { get; set; }
  489. public bool UseSMS { get; set; }
  490. public bool UseEmail { get; set; }
  491. public DateTime CreatedDateTime { get; set; }
  492. public double CurrentValue { get; set; }
  493. public string FacilityName { get; set; }
  494. public string PointName { get; set; }
  495. public string AlarmGroupName { get; set; }
  496. };
  497. //public class BemsPeakHistoryDaily
  498. //{
  499. // public int SiteId { get; set; }
  500. // public DateTime CreatedDateTime { get; set; }
  501. // public double MaxPeakValue { get; set; }
  502. // public DateTime MaxDateTime { get; set; }
  503. // public double MinPeakValue { get; set; }
  504. // public DateTime MinDateTime { get; set; }
  505. //};
  506. }
  507. }