using iBemsDataService.Model; using Quartz; using System; using System.Collections.Generic; using System.Linq; using System.Transactions; using System.Web; using iBemsDataService.Controllers.Bems.Common; using System.Collections; using System.Configuration; namespace iBemsDataService.Controllers.Fms.WorkManagement.ScheduledTask { public class AlarmPlanJob : IJob { private iBemsEntities db = new iBemsEntities(); public void Execute(IJobExecutionContext context) { // 알람 실행 함수 AlarmExecute(); // 전력 피크 실행 함수 PowerPeakExecute(); } // 알람 실행 함수 public void AlarmExecute() { // 현재시간 DateTime today = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); if (today.ToString("mm").ToString() == "16" || today.ToString("mm").ToString() == "31" || today.ToString("mm").ToString() == "46" || today.ToString("mm").ToString() == "01") { ArrayList ArrayList_AlarmSetting = new ArrayList(); using (var transaction = new TransactionScope()) { var query = from s in db.BemsAlarmSetting where s.UseAlarm == true // 알람을 사용하는것만 조회 select s; foreach (var s in query) { AlarmSetting _AlarmSetting = new AlarmSetting(); _AlarmSetting.SiteId = s.SiteId; _AlarmSetting.FacilityTypeId = s.FacilityTypeId; _AlarmSetting.FacilityCode = s.FacilityCode; _AlarmSetting.PropertyId = s.PropertyId; _AlarmSetting.FormulaId = s.FormulaId; _AlarmSetting.UseUpperLimit = s.UseUpperLimit; _AlarmSetting.UpperLimit = (double)s.UpperLimit; _AlarmSetting.UseLowerLimit = s.UseLowerLimit; _AlarmSetting.LowerLimit = (double)s.LowerLimit; _AlarmSetting.UseSMS = s.UseSMS; _AlarmSetting.UseEmail = s.UseEmail; _AlarmSetting.FacilityName = s.FacilityName; _AlarmSetting.PointName = s.PointName; _AlarmSetting.AlarmGroupName = s.AlarmGroupName; ArrayList_AlarmSetting.Add(_AlarmSetting); } transaction.Complete(); } // 아래와 같이 분리해서 구현한 이유는 _BemsFormulaController.GetBemsFormulaReal 함수는 using (var transaction = new TransactionScope()) 안에 들어가면 안된다 // 엔티티 프레임워크를 이용 할 경우에는 using (var transaction = new TransactionScope())를 무조건 사용해야 한다 for (int i = 0; i < ArrayList_AlarmSetting.Count; i++) { AlarmSetting _AlarmSetting = (AlarmSetting)ArrayList_AlarmSetting[i]; // 초 확인 string second = today.ToString("ss"); DateTime start = today.AddMinutes(-16); start = start.AddSeconds(-Int32.Parse(second)); DateTime end = today.AddMinutes(-2); end = end.AddSeconds(-Int32.Parse(second)); // 관제점 정보 if (_AlarmSetting.FormulaId == 0) { using (var transaction = new TransactionScope()) { var query = from s in db.BemsMonitoringPointHistory15min where s.SiteId == _AlarmSetting.SiteId && s.FacilityCode == _AlarmSetting.FacilityCode && s.PropertyId == _AlarmSetting.PropertyId && s.CreatedDateTime >= start && s.CreatedDateTime < end select s; foreach (var s in query) { _AlarmSetting.CreatedDateTime = s.CreatedDateTime; _AlarmSetting.CurrentValue = s.CurrentValue; ArrayList_AlarmSetting[i] = _AlarmSetting; } transaction.Complete(); } } else // 성능분석 정보 { List list = new List(); BemsFormulaController _BemsFormulaController = new BemsFormulaController(); list = _BemsFormulaController.GetBemsFormulaReal(_AlarmSetting.SiteId, _AlarmSetting.FacilityTypeId, _AlarmSetting.FacilityCode, _AlarmSetting.FormulaId, start, end, 1); _AlarmSetting.CreatedDateTime = list[0].DateTime; _AlarmSetting.CurrentValue = list[0].Value; ArrayList_AlarmSetting[i] = _AlarmSetting; } } // 아래와 같이 분리해서 구현한 이유는 _BemsFormulaController.GetBemsFormulaReal 함수는 using (var transaction = new TransactionScope()) 안에 들어가면 안된다 // 실질적으로 아래 함수에서 알람 로그가 저장된다 using (var transaction = new TransactionScope()) { for (int i = 0; i < ArrayList_AlarmSetting.Count; i++) { AlarmSetting _AlarmSetting = (AlarmSetting)ArrayList_AlarmSetting[i]; bool Exceed_Limit = false; // 상한값 비교 if (_AlarmSetting.UseUpperLimit == true) { if (_AlarmSetting.CurrentValue >= Convert.ToDouble(_AlarmSetting.UpperLimit)) { Exceed_Limit = true; } } // 하한값 비교 if (_AlarmSetting.UseLowerLimit == true) { if (_AlarmSetting.CurrentValue <= Convert.ToDouble(_AlarmSetting.LowerLimit)) { Exceed_Limit = true; } } // 기준을 초과했다 if (Exceed_Limit == true) { // BemsAlarmLog 테이블에 추가 var AlarmLog = new BemsAlarmLog { SiteId = _AlarmSetting.SiteId, FacilityTypeId = _AlarmSetting.FacilityTypeId, FacilityCode = _AlarmSetting.FacilityCode, PropertyId = _AlarmSetting.PropertyId, FormulaId = _AlarmSetting.FormulaId, CreatedDateTime = _AlarmSetting.CreatedDateTime, CurrentValue = _AlarmSetting.CurrentValue, SMSResult = "-", EmailResult = "-", Conform = false }; AlarmLog = db.BemsAlarmLog.Add(AlarmLog); db.SaveChanges(); // SMS 전송 string SMS_Result = "-"; if (_AlarmSetting.UseSMS == true) { int siteID = _AlarmSetting.SiteId; string usergroupname = _AlarmSetting.AlarmGroupName; using (var transaction1 = new TransactionScope()) { var query1 = from s1 in db.BemsAlarmGroup where s1.SiteId == siteID && s1.Name == usergroupname select s1; foreach (var s1 in query1) { int alarm_groupid = s1.AlarmGroupId; using (var transaction2 = new TransactionScope()) { var query2 = from s2 in db.BemsAlarmGroupUser where s2.SiteId == siteID && s2.AlarmGroupId == alarm_groupid select s2; foreach (var s2 in query2) { string userid = s2.UserId; using (var transaction3 = new TransactionScope()) { var query3 = from s3 in db.CmUser where s3.SiteId == siteID && s3.UserId == userid select s3; foreach (var s3 in query3) { string MobilePhoneNo = s3.MobilePhoneNo; if (MobilePhoneNo != "") { // "BEMS알람/07:30/공조기_AHU-101/공조기습도/44.09" AlarmConfigSMS _AlarmConfigSMS = new AlarmConfigSMS(); // SMS 정보 DB에서 가져오기 2019.04. var query4 = from s4 in db.CmAlarmAdmin where s4.SiteId == siteID select s4; foreach (var s4 in query4) { string smsUsercode_s = s4.UserCode; string smsDeptcode_s = s4.DeptCode; string smsPhoneNo_s = s4.PhoneNo; _AlarmConfigSMS.SetSMSInfo(smsUsercode_s, smsDeptcode_s, smsPhoneNo_s); // SMS 정보 가져오기 2019.04. string SMSResult = _AlarmConfigSMS.SendAlarmSMS(MobilePhoneNo, "BEMS알람/" + _AlarmSetting.CreatedDateTime.ToString("HH:mm") + "/" + _AlarmSetting.FacilityName + "/" + _AlarmSetting.PointName + "/" + _AlarmSetting.CurrentValue); if (SMSResult == "OK") // 성공 { if (SMS_Result == "실패") { SMS_Result = "실패"; } else { SMS_Result = "성공"; } } else // 실패 { SMS_Result = "실패"; } } } } transaction3.Complete(); } } transaction2.Complete(); } } transaction1.Complete(); } } // Email 전송 string Email_Result = "-"; if (_AlarmSetting.UseEmail == true) { int siteID = _AlarmSetting.SiteId; string usergroupname = _AlarmSetting.AlarmGroupName; using (var transaction1 = new TransactionScope()) { var query1 = from s1 in db.BemsAlarmGroup where s1.SiteId == siteID && s1.Name == usergroupname select s1; foreach (var s1 in query1) { int alarm_groupid = s1.AlarmGroupId; using (var transaction2 = new TransactionScope()) { var query2 = from s2 in db.BemsAlarmGroupUser where s2.SiteId == siteID && s2.AlarmGroupId == alarm_groupid select s2; foreach (var s2 in query2) { string userid = s2.UserId; using (var transaction3 = new TransactionScope()) { var query3 = from s3 in db.CmUser where s3.SiteId == siteID && s3.UserId == userid select s3; foreach (var s3 in query3) { string Email = s3.Email; if (Email != "") { AlarmConfigEmail _AlarmConfigEmail = new AlarmConfigEmail(); _AlarmConfigEmail.SetToAddress(Email); // Email 정보 설정 2019.04. var query4 = from s4 in db.CmAlarmAdmin where s4.SiteId == siteID select s4; foreach (var s4 in query4) { string emailaddress_s = s4.EmailAddress; string emailpassword_s = s4.EmailPassword; _AlarmConfigEmail.SetEmailInfo(emailaddress_s, emailpassword_s); // Email 정보 설정 2019.04. string temp = String.Empty; if (_AlarmSetting.UseUpperLimit == true) temp = temp + "/기준상한(이상) : " + _AlarmSetting.UpperLimit; if (_AlarmSetting.UseLowerLimit == true) temp = temp + "/기준하한(이하) : " + _AlarmSetting.LowerLimit; string EamilResult = _AlarmConfigEmail.SendEmail("BEMS알람", _AlarmSetting.CreatedDateTime + "/" + _AlarmSetting.FacilityName + "/" + _AlarmSetting.PointName + temp + "/" + "값 : " + _AlarmSetting.CurrentValue); if (EamilResult == "OK") // 성공 { if (Email_Result == "실패") { Email_Result = "실패"; } else { Email_Result = "성공"; } } else // 실패 { Email_Result = "실패"; } } } } transaction3.Complete(); } } transaction2.Complete(); } } transaction1.Complete(); } } // SMS 또는 Email을 사용중이다 if (_AlarmSetting.UseSMS == true || _AlarmSetting.UseEmail == true) { // BemsAlarmLog 테이블 업데이트 var update_BemsAlarmLog = (from d in db.BemsAlarmLog where d.SiteId == _AlarmSetting.SiteId && d.FacilityTypeId == _AlarmSetting.FacilityTypeId && d.FacilityCode == _AlarmSetting.FacilityCode && d.PropertyId == _AlarmSetting.PropertyId && d.FormulaId == _AlarmSetting.FormulaId && d.CreatedDateTime == _AlarmSetting.CreatedDateTime select d).Single(); update_BemsAlarmLog.SMSResult = SMS_Result; update_BemsAlarmLog.EmailResult = Email_Result; db.SaveChanges(); } } } transaction.Complete(); } } } // 전력 피크 실행 함수 public void PowerPeakExecute() { int siteid = 1; if (ConfigurationManager.ConnectionStrings["siteid"] != null) { siteid = Convert.ToInt32(ConfigurationManager.ConnectionStrings["siteid"].ConnectionString); } DateTime start = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd 00:00:00")); DateTime end = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd HH:mm:00")); using (var transaction = new TransactionScope()) { var Max = (from s in db.BemsPeakHistory where s.SiteId == siteid && s.CreatedDateTime >= start && s.CreatedDateTime <= end orderby s.CurrentValue descending select s).First(); var Min = (from s in db.BemsPeakHistory where s.SiteId == siteid && s.CreatedDateTime >= start && s.CreatedDateTime <= end orderby s.CurrentValue ascending select s).First(); var query = (from s in db.BemsPeakHistoryDaily where s.SiteId == siteid && s.CreatedDateTime == start select s); if (query.Any() == false) { var newBemsPeakHistoryDaily = new BemsPeakHistoryDaily { SiteId = siteid, CreatedDateTime = start, MaxPeakValue = Max.CurrentValue, MaxDateTime = Max.CreatedDateTime, MinPeakValue = Min.CurrentValue, MinDateTime = Min.CreatedDateTime }; db.BemsPeakHistoryDaily.Add(newBemsPeakHistoryDaily); db.SaveChanges(); } else { var tempmaxvalue = query.First().MaxPeakValue; DateTime tempmaxdate = Convert.ToDateTime(query.First().MaxDateTime); var tempminvalue = query.First().MinPeakValue; DateTime tempmindate = Convert.ToDateTime(query.First().MinDateTime); if (tempmaxvalue < Max.CurrentValue) { tempmaxvalue = Max.CurrentValue; tempmaxdate = Max.CreatedDateTime; } if (tempminvalue > Min.CurrentValue) { tempminvalue = Min.CurrentValue; tempmindate = Min.CreatedDateTime; } var newBemsPeakHistoryDaily = new BemsPeakHistoryDaily { SiteId = siteid, CreatedDateTime = start, MaxPeakValue = tempmaxvalue, MaxDateTime = tempmaxdate, MinPeakValue = tempminvalue, MinDateTime = tempmindate }; db.BemsPeakHistoryDaily.Remove(query.First()); db.BemsPeakHistoryDaily.Add(newBemsPeakHistoryDaily); db.SaveChanges(); } transaction.Complete(); } // 옛날 전력 피크 업데이트 용 /* for (var i = 0; i < 365; i++) { DateTime start = Convert.ToDateTime(DateTime.Now.ToString("2018-07-26 00:00:00")); DateTime end = Convert.ToDateTime(DateTime.Now.ToString("2018-07-26 23:59:00")); start = start.AddDays(i); end = end.AddDays(i); using (var transaction = new TransactionScope()) { var Max = (from s in db.BemsPeakHistory where s.SiteId == siteid && s.CreatedDateTime >= start && s.CreatedDateTime <= end orderby s.CurrentValue descending select s); var Min = (from s in db.BemsPeakHistory where s.SiteId == siteid && s.CreatedDateTime >= start && s.CreatedDateTime <= end orderby s.CurrentValue ascending select s); var query = (from s in db.BemsPeakHistoryDaily where s.SiteId == siteid && s.CreatedDateTime == start select s); if (Max.Any() != false && Min.Any() != false) { if (query.Any() == false) { var newBemsPeakHistoryDaily = new BemsPeakHistoryDaily { SiteId = siteid, CreatedDateTime = start, MaxPeakValue = Max.First().CurrentValue, MaxDateTime = Max.First().CreatedDateTime, MinPeakValue = Min.First().CurrentValue, MinDateTime = Min.First().CreatedDateTime }; db.BemsPeakHistoryDaily.Add(newBemsPeakHistoryDaily); db.SaveChanges(); } else { var tempmaxvalue = query.First().MaxPeakValue; DateTime tempmaxdate = Convert.ToDateTime(query.First().MaxDateTime); var tempminvalue = query.First().MinPeakValue; DateTime tempmindate = Convert.ToDateTime(query.First().MinDateTime); if (tempmaxvalue < Max.First().CurrentValue) { tempmaxvalue = Max.First().CurrentValue; tempmaxdate = Max.First().CreatedDateTime; } if (tempminvalue > Min.First().CurrentValue) { tempminvalue = Min.First().CurrentValue; tempmindate = Min.First().CreatedDateTime; } var newBemsPeakHistoryDaily = new BemsPeakHistoryDaily { SiteId = siteid, CreatedDateTime = start, MaxPeakValue = tempmaxvalue, MaxDateTime = tempmaxdate, MinPeakValue = tempminvalue, MinDateTime = tempmindate }; db.BemsPeakHistoryDaily.Remove(query.First()); db.BemsPeakHistoryDaily.Add(newBemsPeakHistoryDaily); db.SaveChanges(); } } transaction.Complete(); } }*/ } public class AlarmSetting { public int SiteId { get; set; } public int FacilityTypeId { get; set; } public int FacilityCode { get; set; } public int PropertyId { get; set; } public int FormulaId { get; set; } public bool UseUpperLimit { get; set; } public double UpperLimit { get; set; } public bool UseLowerLimit { get; set; } public double LowerLimit { get; set; } public bool UseSMS { get; set; } public bool UseEmail { get; set; } public DateTime CreatedDateTime { get; set; } public double CurrentValue { get; set; } public string FacilityName { get; set; } public string PointName { get; set; } public string AlarmGroupName { get; set; } }; //public class BemsPeakHistoryDaily //{ // public int SiteId { get; set; } // public DateTime CreatedDateTime { get; set; } // public double MaxPeakValue { get; set; } // public DateTime MaxDateTime { get; set; } // public double MinPeakValue { get; set; } // public DateTime MinDateTime { get; set; } //}; } }