1 #endregion 2 3 #region 告警统计 4 /// <summary> 5 /// 获取天、周、月、年起始时间 6 /// </summary> 7 public List<DateTime> GetListDate() 8 { 9 try 10 { 11 //天 12 DateTime day = Convert.ToDateTime(DateTime.Now.Date.ToString()); //当天开始时间 13 //周 14 DateTime week = Convert.ToDateTime(DateTime.Now.Date.AddDays(-(int)(DateTime.Now.DayOfWeek)).ToString()); //本周开始时间 +1 15 //if (week > DateTime.Now) //now(2017/5/7是周日 week首天应为2017/5/1) 16 // week = week.AddDays(-7); 17 //月 18 DateTime month = Convert.ToDateTime(new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).ToString()); //本月开始时间 19 //年 20 DateTime year = Convert.ToDateTime(new DateTime(DateTime.Now.Date.Year, 1, 1).ToString()); //本年开始时间 21 return new List<DateTime>() { day, week, month, year }; 22 } 23 catch (Exception ex) 24 { 25 LogHelper.GetLog("Server").Error(ExceptionHelper.GetExceptionDesc(ex)); 26 return null; 27 } 28 } 29 /// <summary> 30 /// 获取子系统的告警根据当日、周、月、年 31 /// </summary> 32 public List<List<HisAlarm>> GetSubsysDateAlarmList(int subsysId) 33 { 34 try 35 { 36 List<List<HisAlarm>> subAlarm = new List<List<HisAlarm>>(); 37 foreach (var date in listDate) 38 { 39 String hql = String.Format("from HisAlarm as alm where alm.AlarmTime>'{0}'AND alm.SubsysId={1}", date.ToString("yyyy-MM-dd HH:mm:ss"),subsysId.ToString()); 40 List<HisAlarm> hqlResult = (List<HisAlarm>)alarmDao.GetSession().CreateQuery(hql).List<HisAlarm>(); 41 subAlarm.Add(hqlResult); 42 } 43 return subAlarm; 44 } 45 catch (Exception ex) 46 { 47 LogHelper.GetLog("Server").Error(ExceptionHelper.GetExceptionDesc(ex)); 48 return null; 49 } 50 } 51 52 /// <summary> 53 /// 获取设备的告警根据当日、周、月、年 54 /// </summary> 55 public List<List<HisAlarm>> GetDeviceDateAlarmList(int deviceId) 56 { 57 try 58 { 59 List<List<HisAlarm>> devAlarm = new List<List<HisAlarm>>(); 60 foreach (var date in listDate) 61 { 62 string hql = String.Format("from HisAlarm as alm where alm.AlarmTime>'{0}'AND alm.AlarmObjectid={1}", date.ToString("yyyy-MM-dd HH:mm:ss"), deviceId.ToString()); 63 List<HisAlarm> hqlResult = (List<HisAlarm>)alarmDao.GetSession().CreateQuery(hql).List<HisAlarm>(); 64 devAlarm.Add(hqlResult); 65 } 66 return devAlarm; 67 } 68 catch (Exception ex) 69 { 70 LogHelper.GetLog("Server").Error(ExceptionHelper.GetExceptionDesc(ex)); 71 return null; 72 } 73 } 74 //子系统告警趋势(子系统告警统计) 75 public Dictionary<int, List<int>> GetSubThrendAlarmDic(List<HisAlarm> list, int timeFlag) 76 { 77 try 78 { 79 Dictionary<int, List<int>> dic = new Dictionary<int, List<int>>(); 80 if(list==null ||list.Count==0) 81 return dic; 82 var query = list.GroupBy(it => it.SubsysId, it => it); 83 foreach (var info in query) 84 { 85 var temList = info.ToList<HisAlarm>(); 86 List<int> intList = new List<int>(); 87 //day 88 if (timeFlag == 0) 89 { 90 DateTime day = listDate[0];//当天开始时间 91 for (int i = 0; i <= DateTime.Now.Hour; i++) //DateTime.Now.Hour当天的小时数 92 { 93 var hour1 = day.AddHours(i); 94 var hour2 = day.AddHours(i + 1); 95 var dayAlarmList = temList.FindAll(item => hour1 <= item.AlarmTime && item.AlarmTime < hour2); 96 intList.Add((dayAlarmList != null) ? dayAlarmList.Count : 0); 97 intList.Add((dayAlarmList != null) ? dayAlarmList.Count(item => item.Status != (int)AlarmStatus_e.AlarmFinish) : 0); 98 intList.Add((dayAlarmList != null) ? dayAlarmList.Count(item => item.Status == (int)AlarmStatus_e.AlarmFinish) : 0); 99 } 100 } 101 //week 102 if (timeFlag == 1) 103 { 104 DateTime week = listDate[1]; //本周开始时间 105 int dayshu = DateTime.Now.Day- week.Day + 1; //本周的第几天 106 for (int i = 0; i < dayshu; i++) 107 { 108 var day1 = week.AddDays(i); 109 var day2 = week.AddDays(i + 1); 110 var weekAlarmList = temList.FindAll(item => day1 <= item.AlarmTime && item.AlarmTime < day2); 111 intList.Add((weekAlarmList != null) ? weekAlarmList.Count : 0); 112 intList.Add((weekAlarmList != null) ? weekAlarmList.Count(item => item.Status != (int)AlarmStatus_e.AlarmFinish) : 0); 113 intList.Add((weekAlarmList != null) ? weekAlarmList.Count(item => item.Status == (int)AlarmStatus_e.AlarmFinish) : 0); 114 } 115 } 116 //month 117 if (timeFlag == 2) 118 { 119 DateTime month = listDate[2]; //本月开始时间 120 for (int i = 0; i < DateTime.Now.Day; i++) //DateTime.Now.Day表示该月中的第几天 121 { 122 var day1 = month.AddDays(i); 123 var day2 = month.AddDays(i + 1); 124 var monthAlarmList = temList.FindAll(item => day1 <= item.AlarmTime && item.AlarmTime < day2); 125 intList.Add((monthAlarmList != null) ? monthAlarmList.Count : 0); 126 intList.Add((monthAlarmList != null) ? monthAlarmList.Count(item => item.Status != (int)AlarmStatus_e.AlarmFinish) : 0); 127 intList.Add((monthAlarmList != null) ? monthAlarmList.Count(item => item.Status == (int)AlarmStatus_e.AlarmFinish) : 0); 128 } 129 } 130 //year 131 if (timeFlag == 3) 132 { 133 DateTime year = listDate[3]; //本年开始时间 134 for (int i = 0; i < DateTime.Now.Month; i++) //DateTime.Now.Month表示该年中的第几个月 135 { 136 var year1 = year.AddMonths(i); 137 var year2 = year.AddMonths(i + 1); 138 var yearAlarmList = temList.FindAll(item => year1 <= item.AlarmTime && item.AlarmTime < year2); 139 intList.Add((yearAlarmList != null) ? yearAlarmList.Count : 0); 140 intList.Add((yearAlarmList != null) ? yearAlarmList.Count(item => item.Status != (int)AlarmStatus_e.AlarmFinish) : 0); 141 intList.Add((yearAlarmList != null) ? yearAlarmList.Count(item => item.Status == (int)AlarmStatus_e.AlarmFinish) : 0); 142 } 143 } 144 dic.Add(info.Key, intList); 145 } 146 return dic; 147 } 148 catch (Exception ex) 149 { 150 LogHelper.GetLog("Server").Error(ExceptionHelper.GetExceptionDesc(ex)); 151 return null; 152 } 153 } 154 //告警类型趋势(设备告警统计) 155 public Dictionary<int, List<int>> GetAlarmTypeThrendAlarmDic(List<HisAlarm> list, int timeFlag) 156 { 157 try 158 { 159 Dictionary<int, List<int>> dic = new Dictionary<int, List<int>>(); 160 if (list == null || list.Count == 0) 161 return dic; 162 var query = list.GroupBy(it => it.AlarmCode, it => it); 163 foreach (var info in query) 164 { 165 var temList = info.ToList<HisAlarm>(); 166 List<int> intList = new List<int>(); 167 //day 168 if (timeFlag == 0) 169 { 170 DateTime day = listDate[0]; //当天开始时间 171 for (int i = 0; i <= DateTime.Now.Hour; i++) //DateTime.Now.Hour当天的小时数 172 { 173 var hour1 = day.AddHours(i); 174 var hour2 = day.AddHours(i + 1); 175 //var dayAlarmList = temList.FindAll(item => hour1 <= item.AlarmTime && item.AlarmTime < hour2); 176 //intList.Add((dayAlarmList != null) ? dayAlarmList.Count : 0); 177 intList.Add(temList.Count(item => hour1 <= item.AlarmTime && item.AlarmTime < hour2)); 178 } 179 } 180 //week 181 if (timeFlag == 1) 182 { 183 DateTime week = listDate[1]; ; //本周开始时间 184 int dayshu = DateTime.Now.Day - week.Day +1; //本周第几天 185 for (int i = 0; i < dayshu ; i++) //DateTime.Now.Day%7本周第几天 186 { 187 var day1 = week.AddDays(i); 188 var day2 = week.AddDays(i + 1); 189 //var weekAlarmList = temList.FindAll(item => day1 <= item.AlarmTime && item.AlarmTime < day2); 190 //intList.Add((weekAlarmList != null) ? weekAlarmList.Count : 0); 191 intList.Add(temList.Count(item => day1 <= item.AlarmTime && item.AlarmTime < day2)); 192 } 193 } 194 //month 195 if (timeFlag == 2) 196 { 197 DateTime month = listDate[2]; //本月开始时间 198 for (int i = 0; i < DateTime.Now.Day; i++) //DateTime.Now.Day表示该月中的第几天 199 { 200 var day1 = month.AddDays(i); 201 var day2 = month.AddDays(i + 1); 202 //var monthAlarmList = temList.FindAll(item => day1 <= item.AlarmTime && item.AlarmTime < day2); 203 //intList.Add((monthAlarmList != null) ? monthAlarmList.Count : 0); 204 intList.Add(temList.Count(item => day1 <= item.AlarmTime && item.AlarmTime < day2)); 205 } 206 } 207 //year 208 if (timeFlag == 3) 209 { 210 DateTime year = listDate[3]; //本年开始时间 211 for (int i = 0; i < DateTime.Now.Month; i++) //DateTime.Now.Month表示该年中的第几个月 212 { 213 var year1 = year.AddMonths(i); 214 var year2 = year.AddMonths(i + 1); 215 //var yearAlarmList = temList.FindAll(item => year1 <= item.AlarmTime && item.AlarmTime < year2); 216 //intList.Add((yearAlarmList != null) ? yearAlarmList.Count : 0); 217 intList.Add(temList.Count(item => year1 <= item.AlarmTime && item.AlarmTime < year2)); 218 } 219 } 220 dic.Add(info.Key, intList); 221 } 222 return dic; 223 } 224 catch (Exception ex) 225 { 226 LogHelper.GetLog("Server").Error(ExceptionHelper.GetExceptionDesc(ex)); 227 return null; 228 } 229 } 230 //设备对象告警趋势(设备告警统计) 231 public Dictionary<int, List<int>> GetDeviceObjectThrendAlarmDic(List<HisAlarm> list, int timeFlag) 232 { 233 try 234 { 235 Dictionary<int, List<int>> dic = new Dictionary<int, List<int>>(); 236 if (list == null || list.Count == 0) 237 return dic; 238 var query = list.GroupBy(it => it.AlarmObjectid, it => it); 239 foreach (var info in query) 240 { 241 var temList = info.ToList<HisAlarm>(); 242 List<int> intList = new List<int>(); 243 //day 244 if (timeFlag == 0) 245 { 246 DateTime day = listDate[0]; //当天开始时间 247 for (int i = 0; i <= DateTime.Now.Hour; i++) //DateTime.Now.Hour当天的小时数 248 { 249 var hour1 = day.AddHours(i); 250 var hour2 = day.AddHours(i + 1); 251 var dayAlarmList = temList.FindAll(item => hour1 <= item.AlarmTime && item.AlarmTime < hour2); 252 intList.Add((dayAlarmList != null) ? dayAlarmList.Count : 0); 253 intList.Add((dayAlarmList != null) ? dayAlarmList.Count(item => item.Status != (int)AlarmStatus_e.AlarmFinish) : 0); 254 intList.Add((dayAlarmList != null) ? dayAlarmList.Count(item => item.Status == (int)AlarmStatus_e.AlarmFinish) : 0); 255 } 256 } 257 //week 258 if (timeFlag == 1) 259 { 260 DateTime week = listDate[1]; //本周开始时间 261 int dayshu = DateTime.Now.Day - week.Day+1; //本周第几天 262 if (dayshu == 0) 263 dayshu = 7; 264 for (int i = 0; i < dayshu ; i++) 265 { 266 var day1 = week.AddDays(i); 267 var day2 = week.AddDays(i + 1); 268 var weekAlarmList = temList.FindAll(item => day1 <= item.AlarmTime && item.AlarmTime < day2); 269 intList.Add((weekAlarmList != null) ? weekAlarmList.Count : 0); 270 intList.Add((weekAlarmList != null) ? weekAlarmList.Count(item => item.Status != (int)AlarmStatus_e.AlarmFinish) : 0); 271 intList.Add((weekAlarmList != null) ? weekAlarmList.Count(item => item.Status == (int)AlarmStatus_e.AlarmFinish) : 0); 272 } 273 } 274 //month 275 if (timeFlag == 2) 276 { 277 DateTime month = listDate[2]; //本月开始时间 278 for (int i = 0; i < DateTime.Now.Day; i++) //DateTime.Now.Day表示该月中的第几天 279 { 280 var day1 = month.AddDays(i); 281 var day2 = month.AddDays(i + 1); 282 var monthAlarmList = temList.FindAll(item => day1 <= item.AlarmTime && item.AlarmTime < day2); 283 intList.Add((monthAlarmList != null) ? monthAlarmList.Count : 0); 284 intList.Add((monthAlarmList != null) ? monthAlarmList.Count(item => item.Status != (int)AlarmStatus_e.AlarmFinish) : 0); 285 intList.Add((monthAlarmList != null) ? monthAlarmList.Count(item => item.Status == (int)AlarmStatus_e.AlarmFinish) : 0); 286 } 287 } 288 //year 289 if (timeFlag == 3) 290 { 291 DateTime year = listDate[3]; //本年开始时间 292 for (int i = 0; i < DateTime.Now.Month; i++) //DateTime.Now.Month表示该年中的第几个月 293 { 294 var year1 = year.AddMonths(i); 295 var year2 = year.AddMonths(i + 1); 296 var yearAlarmList = temList.FindAll(item => year1 <= item.AlarmTime && item.AlarmTime < year2); 297 intList.Add((yearAlarmList != null) ? yearAlarmList.Count : 0); 298 intList.Add((yearAlarmList != null) ? yearAlarmList.Count(item => item.Status != (int)AlarmStatus_e.AlarmFinish) : 0); 299 intList.Add((yearAlarmList != null) ? yearAlarmList.Count(item => item.Status == (int)AlarmStatus_e.AlarmFinish) : 0); 300 } 301 } 302 dic.Add(info.Key, intList); 303 } 304 return dic; 305 } 306 catch (Exception ex) 307 { 308 LogHelper.GetLog("Server").Error(ExceptionHelper.GetExceptionDesc(ex)); 309 return null; 310 } 311 } 312 313 /// <summary> 314 /// 整体告警统计 315 /// </summary> 316 /// <returns></returns> 317 public AllAlarmCountDTO GetAllAlarmCount() 318 { 319 try 320 { 321 AllAlarmCountDTO allAlarmCount = new AllAlarmCountDTO() 322 { 323 levelAlarm = new ItemAlarmDTO() { dayAlarm = new Dictionary<int, List<int>>(), monthAlarm = new Dictionary<int, List<int>>(), weekAlarm = new Dictionary<int, List<int>>(), yearAlarm = new Dictionary<int, List<int>>() }, 324 deviceTypeAlarm = new ItemAlarmDTO() { dayAlarm = new Dictionary<int, List<int>>(), monthAlarm = new Dictionary<int, List<int>>(), weekAlarm = new Dictionary<int, List<int>>(), yearAlarm = new Dictionary<int, List<int>>() }, 325 moduleAlarm = new ItemAlarmDTO() { dayAlarm = new Dictionary<int, List<int>>(), monthAlarm = new Dictionary<int, List<int>>(), weekAlarm = new Dictionary<int, List<int>>(), yearAlarm = new Dictionary<int, List<int>>() }, 326 subAlarm = new ItemAlarmDTO() { dayAlarm = new Dictionary<int, List<int>>(), monthAlarm = new Dictionary<int, List<int>>(), weekAlarm = new Dictionary<int, List<int>>(), yearAlarm = new Dictionary<int, List<int>>() }, 327 typeAlarm = new ItemAlarmDTO() { dayAlarm = new Dictionary<int, List<int>>(), monthAlarm = new Dictionary<int, List<int>>(), weekAlarm = new Dictionary<int, List<int>>(), yearAlarm = new Dictionary<int, List<int>>() } 328 }; 329 //告警等级 330 foreach (var date in listDate) 331 { 332 string sql = String.Format("SELECT t3.a2,SUM(t3.b2)as r FROM(SELECT*FROM(SELECT alarm_code AS a1,alarm_level AS a2 FROM def_type_alarm) AS t1,(SELECT alarm_code AS b1,COUNT (*) AS b2 FROM his_alarm WHERE alarm_time > '{0}' GROUP BY alarm_code) AS t2 WHERE t1.a1 = t2.b1) AS t3 GROUP BY t3.a2", date.ToString("yyyy-MM-dd HH:mm:ss")); 333 var listRelut = alarmDao.GetSession().CreateSQLQuery(sql).AddScalar("a2", NHibernateUtil.Int32).AddScalar("r", NHibernateUtil.Int32).List(); 334 if (listRelut.Count == 0) 335 { 336 if (date == listDate[0]) //天 337 allAlarmCount.levelAlarm.dayAlarm = new Dictionary<int, List<int>>(); 338 if (date == listDate[1]) //周 339 allAlarmCount.levelAlarm.weekAlarm = new Dictionary<int, List<int>>(); 340 if (date == listDate[2]) //月 341 allAlarmCount.levelAlarm.monthAlarm = new Dictionary<int, List<int>>(); 342 if (date == listDate[3]) //年 343 allAlarmCount.levelAlarm.yearAlarm = new Dictionary<int, List<int>>(); 344 } 345 else 346 { 347 foreach (var it in listRelut) 348 { 349 Object[] obj = (Object[])it; 350 if (date == listDate[0]) //天 351 allAlarmCount.levelAlarm.dayAlarm.Add((int)obj[0], new List<int>() { (int)obj[1]}); //告警等级，数量 352 if (date == listDate[1]) //周 353 allAlarmCount.levelAlarm.weekAlarm.Add((int)obj[0], new List<int>() { (int)obj[1] }); //告警等级，数量 354 if (date == listDate[2]) //月 355 allAlarmCount.levelAlarm.monthAlarm.Add((int)obj[0], new List<int>() { (int)obj[1] }); //告警等级，数量 356 if (date == listDate[3]) //年 357 allAlarmCount.levelAlarm.yearAlarm.Add((int)obj[0], new List<int>() { (int)obj[1] }); //告警等级，数量 358 } 359 } 360 } 361 //告警类型top10 362 foreach (var date in listDate) //遍历天、周、月、年 363 { 364 String hqlAlarmCode = String.Format("select alm.AlarmCode,count(AlarmId) from HisAlarm as alm where alm.AlarmTime>'{0}' group by alm.AlarmCode order by count(AlarmId) desc", date.ToString("yyyy-MM-dd HH:mm:ss")); 365 var alarmCodeTop = alarmDao.GetSession().CreateQuery(hqlAlarmCode).SetMaxResults(10).List(); 366 if (alarmCodeTop.Count == 0) 367 { 368 if (date == listDate[0]) //天 369 allAlarmCount.typeAlarm.dayAlarm = new Dictionary<int, List<int>>(); 370 if (date == listDate[1]) //周 371 allAlarmCount.typeAlarm.weekAlarm = new Dictionary<int, List<int>>(); 372 if (date == listDate[2]) //月 373 allAlarmCount.typeAlarm.monthAlarm = new Dictionary<int, List<int>>(); 374 if (date == listDate[3]) //年 375 allAlarmCount.typeAlarm.yearAlarm = new Dictionary<int, List<int>>(); 376 } 377 else 378 { 379 foreach (var it in alarmCodeTop) 380 { 381 Object[] obj = (Object[])it; 382 if (date == listDate[0]) //天 383 allAlarmCount.typeAlarm.dayAlarm.Add(Convert.ToInt32(obj[0]), new List<int>() { Convert.ToInt32(obj[1]) }); 384 if (date == listDate[1]) //周 385 allAlarmCount.typeAlarm.weekAlarm.Add(Convert.ToInt32(obj[0]), new List<int>() { Convert.ToInt32(obj[1]) }); 386 if (date == listDate[2]) //月 387 allAlarmCount.typeAlarm.monthAlarm.Add(Convert.ToInt32(obj[0]), new List<int>() { Convert.ToInt32(obj[1]) }); 388 if (date == listDate[3]) //年 389 allAlarmCount.typeAlarm.yearAlarm.Add(Convert.ToInt32(obj[0]), new List<int>() { Convert.ToInt32(obj[1]) }); 390 } 391 } 392 } 393 //设备类型top10 394 foreach (var date in listDate) //遍历天、周、月、年 395 { 396 String sql = String.Format("SELECT t2.b2,sum(a2) as r FROM(SELECT alarm_objectid AS a1,COUNT (*) AS a2 FROM his_alarm WHERE his_alarm.alarm_time > '{0}'GROUP BY alarm_objectid) AS t1,(SELECT object_id AS b1,object_type AS b2 FROM cfg_object_device) AS t2 WHERE t1.a1 = t2.b1 GROUP BY t2.b2 ORDER BY r DESC", date.ToString("yyyy-MM-dd HH:mm:ss")); 397 var alarmDeviceTop = alarmDao.GetSession().CreateSQLQuery(sql).AddScalar("b2", NHibernateUtil.Int32).AddScalar("r", NHibernateUtil.Int32).SetMaxResults(10).List(); 398 if (alarmDeviceTop.Count == 0) 399 { 400 if (date == listDate[0]) //天 401 allAlarmCount.deviceTypeAlarm.dayAlarm = new Dictionary<int, List<int>>(); 402 if (date == listDate[1]) //周 403 allAlarmCount.deviceTypeAlarm.weekAlarm = new Dictionary<int, List<int>>(); 404 if (date == listDate[2]) //月 405 allAlarmCount.deviceTypeAlarm.monthAlarm = new Dictionary<int, List<int>>(); 406 if (date == listDate[3]) //年 407 allAlarmCount.deviceTypeAlarm.yearAlarm = new Dictionary<int, List<int>>(); 408 } 409 else 410 { 411 foreach (var it in alarmDeviceTop) 412 { 413 Object[] obj = (Object[])it; 414 if (date == listDate[0]) //天 415 allAlarmCount.deviceTypeAlarm.dayAlarm.Add(Convert.ToInt32(obj[0]), new List<int>() { Convert.ToInt32(obj[1]) }); 416 if (date == listDate[1]) //周 417 allAlarmCount.deviceTypeAlarm.weekAlarm.Add(Convert.ToInt32(obj[0]), new List<int>() { Convert.ToInt32(obj[1]) }); 418 if (date == listDate[2]) //月 419 allAlarmCount.deviceTypeAlarm.monthAlarm.Add(Convert.ToInt32(obj[0]), new List<int>() { Convert.ToInt32(obj[1]) }); 420 if (date == listDate[3]) //年 421 allAlarmCount.deviceTypeAlarm.yearAlarm.Add(Convert.ToInt32(obj[0]), new List<int>() { Convert.ToInt32(obj[1]) }); 422 } 423 } 424 } 425 //模块 426 System.Diagnostics.Stopwatch sw3 = new System.Diagnostics.Stopwatch(); 427 sw3.Start(); 428 foreach (var date in listDate) //遍历天、周、月、年 429 { 430 String sql = String.Format("SELECT t1.ModuId,t1.r FROM(SELECT t.ModuId,SUM(t.result)as r FROM(SELECT alarm_objectid AS objId,(CASE WHEN alarm_objectid in (SELECT cfg_object_device.object_id from cfg_object_device) THEN(SELECT cfg_object_device.parent_id FROM cfg_object_device WHERE cfg_object_device.object_id = alarm_objectid)ELSE alarm_objectid END) AS ModuId,COUNT (*) AS result FROM his_alarm where alarm_time>'{0}' GROUP BY alarm_objectid)as t GROUP BY t.ModuId ) as t1,cfg_object_module where t1.ModuId=cfg_object_module.object_id", date.ToString("yyyy-MM-dd HH:mm:ss")); 431 var alarmModule = alarmDao.GetSession().CreateSQLQuery(sql).AddScalar("ModuId", NHibernateUtil.Int32).AddScalar("r", NHibernateUtil.Int32).List(); 432 if (alarmModule.Count == 0) 433 { 434 if (date == listDate[0]) //天 435 allAlarmCount.moduleAlarm.dayAlarm = new Dictionary<int, List<int>>(); 436 if (date == listDate[1]) //周 437 allAlarmCount.moduleAlarm.weekAlarm = new Dictionary<int, List<int>>(); 438 if (date == listDate[2]) //月 439 allAlarmCount.moduleAlarm.monthAlarm = new Dictionary<int, List<int>>(); 440 if (date == listDate[3]) //年 441 allAlarmCount.moduleAlarm.yearAlarm = new Dictionary<int, List<int>>(); 442 } 443 else 444 { 445 foreach (var it in alarmModule) 446 { 447 Object[] obj = (Object[])it; 448 if (date == listDate[0]) //天 449 allAlarmCount.moduleAlarm.dayAlarm.Add((int)obj[0], new List<int>() { (int)obj[1] }); 450 if (date == listDate[1]) //周 451 allAlarmCount.moduleAlarm.weekAlarm.Add((int)obj[0], new List<int>() { (int)obj[1] }); 452 if (date == listDate[2]) //月 453 allAlarmCount.moduleAlarm.monthAlarm.Add((int)obj[0], new List<int>() { (int)obj[1] }); 454 if (date == listDate[3]) //年 455 allAlarmCount.moduleAlarm.yearAlarm.Add((int)obj[0], new List<int>() { (int)obj[1] }); 456 } 457 } 458 } 459 //子系统 460 System.Diagnostics.Stopwatch sw4 = new System.Diagnostics.Stopwatch(); 461 sw4.Start(); 462 foreach (var date in listDate) 463 { 464 string sql = String.Format("SELECT subsys_id as SId,COUNT (*) as CAll,COUNT(CASE WHEN status!=3 THEN 1 END) as C1,COUNT(CASE WHEN status=3 THEN 1 END) as C2 FROM his_alarm WHERE alarm_time>'{0}' GROUP BY subsys_id", date.ToString("yyyy-MM-dd HH:mm:ss")); 465 var listResult = alarmDao.GetSession().CreateSQLQuery(sql).AddScalar("SId", NHibernateUtil.Int32).AddScalar("CAll", NHibernateUtil.Int32).AddScalar("C1", NHibernateUtil.Int32).AddScalar("C2", NHibernateUtil.Int32).List(); 466 if (listResult.Count == 0) 467 { 468 if (date == listDate[0])//天 469 allAlarmCount.subAlarm.dayAlarm = new Dictionary<int, List<int>>(); 470 if (date == listDate[1])//周 471 allAlarmCount.subAlarm.weekAlarm = new Dictionary<int, List<int>>(); 472 if (date == listDate[2])//月 473 allAlarmCount.subAlarm.monthAlarm = new Dictionary<int, List<int>>(); 474 if (date == listDate[3])//年 475 allAlarmCount.subAlarm.yearAlarm = new Dictionary<int, List<int>>(); 476 } 477 else 478 { 479 foreach (var it in listResult) 480 { 481 Object[] obj = (Object[])it; 482 if (date == listDate[0])//天 483 allAlarmCount.subAlarm.dayAlarm.Add((int)obj[0], new List<int>() { (int)obj[1], (int)obj[2], (int)obj[3] }); 484 if (date == listDate[1])//周 485 allAlarmCount.subAlarm.weekAlarm.Add((int)obj[0], new List<int>() { (int)obj[1], (int)obj[2], (int)obj[3] }); 486 if (date == listDate[2])//月 487 allAlarmCount.subAlarm.monthAlarm.Add((int)obj[0], new List<int>() { (int)obj[1], (int)obj[2], (int)obj[3] }); 488 if (date == listDate[3])//年 489 allAlarmCount.subAlarm.yearAlarm.Add((int)obj[0], new List<int>() { (int)obj[1], (int)obj[2], (int)obj[3] }); 490 } 491 } 492 } 493 return allAlarmCount; 494 } 495 catch (Exception ex) 496 { 497 LogHelper.GetLog("Server").Error(ExceptionHelper.GetExceptionDesc(ex)); 498 return null; 499 } 500 } 501 502 /// <summary> 503 /// 子系统告警统计 504 /// </summary> 505 /// <param name="subsysId"></param> 506 /// <returns></returns> 507 public SubAlarmCountDTO GetSubsysAlarmCount(int subsysId) 508 { 509 try 510 { 511 SubAlarmCountDTO subAlarmCount = new SubAlarmCountDTO() 512 { 513 levelAlarm = new ItemAlarmDTO() { dayAlarm = new Dictionary<int, List<int>>(), monthAlarm = new Dictionary<int, List<int>>(), weekAlarm = new Dictionary<int, List<int>>(), yearAlarm = new Dictionary<int, List<int>>() }, 514 deviceTypeAlarm = new ItemAlarmDTO() { dayAlarm = new Dictionary<int, List<int>>(), monthAlarm = new Dictionary<int, List<int>>(), weekAlarm = new Dictionary<int, List<int>>(), yearAlarm = new Dictionary<int, List<int>>() }, 515 deviceObjectAlarm = new ItemAlarmDTO() { dayAlarm = new Dictionary<int, List<int>>(), monthAlarm = new Dictionary<int, List<int>>(), weekAlarm = new Dictionary<int, List<int>>(), yearAlarm = new Dictionary<int, List<int>>() }, 516 subAlarm = new ItemAlarmDTO() { dayAlarm = new Dictionary<int, List<int>>(), monthAlarm = new Dictionary<int, List<int>>(), weekAlarm = new Dictionary<int, List<int>>(), yearAlarm = new Dictionary<int, List<int>>() }, 517 typeAlarm = new ItemAlarmDTO() { dayAlarm = new Dictionary<int, List<int>>(), monthAlarm = new Dictionary<int, List<int>>(), weekAlarm = new Dictionary<int, List<int>>(), yearAlarm = new Dictionary<int, List<int>>() } 518 }; 519 var dateList = GetSubsysDateAlarmList(subsysId); 520 //告警等级 521 foreach (var date in listDate) 522 { 523 string sql = String.Format("SELECT t3.a2,SUM(t3.b2)as r FROM(SELECT*FROM(SELECT alarm_code AS a1,alarm_level AS a2 FROM def_type_alarm) AS t1,(SELECT alarm_code AS b1,COUNT (*) AS b2 FROM his_alarm WHERE alarm_time > '{0}' And his_alarm.subsys_id ='{1}' GROUP BY alarm_code) AS t2 WHERE t1.a1 = t2.b1) AS t3 GROUP BY t3.a2", date.ToString("yyyy-MM-dd HH:mm:ss"), subsysId.ToString()); 524 var listRelut = alarmDao.GetSession().CreateSQLQuery(sql).AddScalar("a2", NHibernateUtil.Int32).AddScalar("r", NHibernateUtil.Int32).List(); 525 if (listRelut.Count == 0) 526 { 527 if (date == listDate[0]) //天 528 subAlarmCount.levelAlarm.dayAlarm = new Dictionary<int, List<int>>(); 529 if (date == listDate[1]) //周 530 subAlarmCount.levelAlarm.weekAlarm = new Dictionary<int, List<int>>(); 531 if (date == listDate[2]) //月 532 subAlarmCount.levelAlarm.monthAlarm = new Dictionary<int, List<int>>(); 533 if (date == listDate[3]) //年 534 subAlarmCount.levelAlarm.yearAlarm = new Dictionary<int, List<int>>(); 535 } 536 else 537 { 538 foreach (var it in listRelut) 539 { 540 Object[] obj = (Object[])it; 541 if (date == listDate[0]) //天 542 subAlarmCount.levelAlarm.dayAlarm.Add((int)obj[0], new List<int>() { (int)obj[1] }); //告警等级，数量 543 if (date == listDate[1]) //周 544 subAlarmCount.levelAlarm.weekAlarm.Add((int)obj[0], new List<int>() { (int)obj[1] }); //告警等级，数量 545 if (date == listDate[2]) //月 546 subAlarmCount.levelAlarm.monthAlarm.Add((int)obj[0], new List<int>() { (int)obj[1] }); //告警等级，数量 547 if (date == listDate[3]) //年 548 subAlarmCount.levelAlarm.yearAlarm.Add((int)obj[0], new List<int>() { (int)obj[1] }); //告警等级，数量 549 } 550 } 551 } 552 //告警类型top10 553 foreach (var date in listDate) //遍历天、周、月、年 554 { 555 String hqlAlarmCode = String.Format("select alm.AlarmCode,count(AlarmId) from HisAlarm as alm where alm.AlarmTime>'{0}' And alm.SubsysId ='{1}' group by alm.AlarmCode order by count(AlarmId) desc", date.ToString("yyyy-MM-dd HH:mm:ss"), subsysId.ToString()); 556 var alarmCodeTop = alarmDao.GetSession().CreateQuery(hqlAlarmCode).SetMaxResults(10).List(); 557 if (alarmCodeTop.Count == 0) 558 { 559 if (date == listDate[0]) //天 560 subAlarmCount.typeAlarm.dayAlarm = new Dictionary<int, List<int>>(); 561 if (date == listDate[1]) //周 562 subAlarmCount.typeAlarm.weekAlarm = new Dictionary<int, List<int>>(); 563 if (date == listDate[2]) //月 564 subAlarmCount.typeAlarm.monthAlarm = new Dictionary<int, List<int>>(); 565 if (date == listDate[3]) //年 566 subAlarmCount.typeAlarm.yearAlarm = new Dictionary<int, List<int>>(); 567 } 568 else 569 { 570 foreach (var it in alarmCodeTop) 571 { 572 Object[] obj = (Object[])it; 573 if (date == listDate[0]) //天 574 subAlarmCount.typeAlarm.dayAlarm.Add(Convert.ToInt32(obj[0]), new List<int>() { Convert.ToInt32(obj[1]) }); 575 if (date == listDate[1]) //周 576 subAlarmCount.typeAlarm.weekAlarm.Add(Convert.ToInt32(obj[0]), new List<int>() { Convert.ToInt32(obj[1]) }); 577 if (date == listDate[2]) //月 578 subAlarmCount.typeAlarm.monthAlarm.Add(Convert.ToInt32(obj[0]), new List<int>() { Convert.ToInt32(obj[1]) }); 579 if (date == listDate[3]) //年 580 subAlarmCount.typeAlarm.yearAlarm.Add(Convert.ToInt32(obj[0]), new List<int>() { Convert.ToInt32(obj[1]) }); 581 } 582 } 583 } 584 //设备对象 top10 585 foreach (var date in listDate) //遍历天、周、月、年 586 { 587 String sql = String.Format("SELECT t1.a1,sum(t1.a2) as r FROM(SELECT alarm_objectid AS a1,COUNT (*) AS a2 FROM his_alarm WHERE his_alarm.alarm_time > '{0}' And his_alarm.subsys_id ='{1}' GROUP BY alarm_objectid) AS t1,(SELECT object_id AS b1 FROM cfg_object_device) AS t2 WHERE t1.a1 = t2.b1 GROUP BY t1.a1 ORDER BY r DESC", date.ToString("yyyy-MM-dd HH:mm:ss"), subsysId.ToString()); 588 var alarmDeviceTop = alarmDao.GetSession().CreateSQLQuery(sql).AddScalar("a1", NHibernateUtil.Int32).AddScalar("r", NHibernateUtil.Int32).SetMaxResults(10).List(); 589 if (alarmDeviceTop.Count == 0) 590 { 591 if (date == listDate[0]) //天 592 subAlarmCount.deviceObjectAlarm.dayAlarm = new Dictionary<int, List<int>>(); 593 if (date == listDate[1]) //周 594 subAlarmCount.deviceObjectAlarm.weekAlarm = new Dictionary<int, List<int>>(); 595 if (date == listDate[2]) //月 596 subAlarmCount.deviceObjectAlarm.monthAlarm = new Dictionary<int, List<int>>(); 597 if (date == listDate[3]) //年 598 subAlarmCount.deviceObjectAlarm.yearAlarm = new Dictionary<int, List<int>>(); 599 } 600 else 601 { 602 foreach (var it in alarmDeviceTop) 603 { 604 Object[] obj = (Object[])it; 605 if (date == listDate[0]) //天 606 subAlarmCount.deviceObjectAlarm.dayAlarm.Add(Convert.ToInt32(obj[0]), new List<int>() { Convert.ToInt32(obj[1]) }); 607 if (date == listDate[1]) //周 608 subAlarmCount.deviceObjectAlarm.weekAlarm.Add(Convert.ToInt32(obj[0]), new List<int>() { Convert.ToInt32(obj[1]) }); 609 if (date == listDate[2]) //月 610 subAlarmCount.deviceObjectAlarm.monthAlarm.Add(Convert.ToInt32(obj[0]), new List<int>() { Convert.ToInt32(obj[1]) }); 611 if (date == listDate[3]) //年 612 subAlarmCount.deviceObjectAlarm.yearAlarm.Add(Convert.ToInt32(obj[0]), new List<int>() { Convert.ToInt32(obj[1]) }); 613 } 614 } 615 } 616 //设备类型 top10 617 foreach (var date in listDate) //遍历天、周、月、年 618 { 619 String sql = String.Format("SELECT t2.b2,sum(a2) as r FROM(SELECT alarm_objectid AS a1,COUNT (*) AS a2 FROM his_alarm WHERE his_alarm.alarm_time > '{0}' And his_alarm.subsys_id ='{1}' GROUP BY alarm_objectid) AS t1,(SELECT object_id AS b1,object_type AS b2 FROM cfg_object_device) AS t2 WHERE t1.a1 = t2.b1 GROUP BY t2.b2 ORDER BY r DESC", date.ToString("yyyy-MM-dd HH:mm:ss"), subsysId.ToString()); 620 var alarmDeviceTop = alarmDao.GetSession().CreateSQLQuery(sql).AddScalar("b2", NHibernateUtil.Int32).AddScalar("r", NHibernateUtil.Int32).SetMaxResults(10).List(); 621 if (alarmDeviceTop.Count == 0) 622 { 623 if (date == listDate[0]) //天 624 subAlarmCount.deviceTypeAlarm.dayAlarm = new Dictionary<int, List<int>>(); 625 if (date == listDate[1]) //周 626 subAlarmCount.deviceTypeAlarm.weekAlarm = new Dictionary<int, List<int>>(); 627 if (date == listDate[2]) //月 628 subAlarmCount.deviceTypeAlarm.monthAlarm = new Dictionary<int, List<int>>(); 629 if (date == listDate[3]) //年 630 subAlarmCount.deviceTypeAlarm.yearAlarm = new Dictionary<int, List<int>>(); 631 } 632 else 633 { 634 foreach (var it in alarmDeviceTop) 635 { 636 Object[] obj = (Object[])it; 637 if (date == listDate[0]) //天 638 subAlarmCount.deviceTypeAlarm.dayAlarm.Add(Convert.ToInt32(obj[0]), new List<int>() { Convert.ToInt32(obj[1]) }); 639 if (date == listDate[1]) //周 640 subAlarmCount.deviceTypeAlarm.weekAlarm.Add(Convert.ToInt32(obj[0]), new List<int>() { Convert.ToInt32(obj[1]) }); 641 if (date == listDate[2]) //月 642 subAlarmCount.deviceTypeAlarm.monthAlarm.Add(Convert.ToInt32(obj[0]), new List<int>() { Convert.ToInt32(obj[1]) }); 643 if (date == listDate[3]) //年 644 subAlarmCount.deviceTypeAlarm.yearAlarm.Add(Convert.ToInt32(obj[0]), new List<int>() { Convert.ToInt32(obj[1]) }); 645 } 646 } 647 } 648 //子系统 649 subAlarmCount.subAlarm.dayAlarm = GetSubThrendAlarmDic(dateList[0], 0); 650 subAlarmCount.subAlarm.weekAlarm = GetSubThrendAlarmDic(dateList[1], 1); 651 subAlarmCount.subAlarm.monthAlarm = GetSubThrendAlarmDic(dateList[2], 2); 652 subAlarmCount.subAlarm.yearAlarm = GetSubThrendAlarmDic(dateList[3], 3); 653 return subAlarmCount; 654 } 655 catch (Exception ex) 656 { 657 LogHelper.GetLog("Server").Error(ExceptionHelper.GetExceptionDesc(ex)); 658 return null; 659 } 660 } 661 662 /// <summary> 663 /// 设备告警统计 664 /// </summary> 665 /// <param name="deviceId"></param> 666 /// <returns></returns> 667 public DeviceAlarmCountDTO GetDeviceAlarmCount(int deviceId) 668 { 669 try 670 { 671 DeviceAlarmCountDTO deviceAlarmCount = new DeviceAlarmCountDTO() 672 { 673 deviceObjectAlarm = new ItemAlarmDTO() { dayAlarm = new Dictionary<int, List<int>>(), monthAlarm = new Dictionary<int, List<int>>(), weekAlarm = new Dictionary<int, List<int>>(), yearAlarm = new Dictionary<int, List<int>>() }, 674 levelAlarm = new ItemAlarmDTO() { dayAlarm = new Dictionary<int, List<int>>(), monthAlarm = new Dictionary<int, List<int>>(), weekAlarm = new Dictionary<int, List<int>>(), yearAlarm = new Dictionary<int, List<int>>() }, 675 typeAlarm = new ItemAlarmDTO() { dayAlarm = new Dictionary<int, List<int>>(), monthAlarm = new Dictionary<int, List<int>>(), weekAlarm = new Dictionary<int, List<int>>(), yearAlarm = new Dictionary<int, List<int>>() } 676 }; 677 var dateList = GetDeviceDateAlarmList(deviceId); 678 //告警等级 679 foreach (var date in listDate) 680 { 681 string sql = String.Format("SELECT t3.a2,SUM(t3.b2)as r FROM(SELECT*FROM(SELECT alarm_code AS a1,alarm_level AS a2 FROM def_type_alarm) AS t1,(SELECT alarm_code AS b1,COUNT (*) AS b2 FROM his_alarm WHERE alarm_time > '{0}' And his_alarm.alarm_objectid ='{1}' GROUP BY alarm_code) AS t2 WHERE t1.a1 = t2.b1) AS t3 GROUP BY t3.a2", date.ToString("yyyy-MM-dd HH:mm:ss"), deviceId.ToString()); 682 var listRelut = alarmDao.GetSession().CreateSQLQuery(sql).AddScalar("a2", NHibernateUtil.Int32).AddScalar("r", NHibernateUtil.Int32).List(); 683 if (listRelut.Count == 0) 684 { 685 if (date == listDate[0]) //天 686 deviceAlarmCount.levelAlarm.dayAlarm = new Dictionary<int, List<int>>(); 687 if (date == listDate[1]) //周 688 deviceAlarmCount.levelAlarm.weekAlarm = new Dictionary<int, List<int>>(); 689 if (date == listDate[2]) //月 690 deviceAlarmCount.levelAlarm.monthAlarm = new Dictionary<int, List<int>>(); 691 if (date == listDate[3]) //年 692 deviceAlarmCount.levelAlarm.yearAlarm = new Dictionary<int, List<int>>(); 693 } 694 else 695 { 696 foreach (var it in listRelut) 697 { 698 Object[] obj = (Object[])it; 699 if (date == listDate[0]) //天 700 deviceAlarmCount.levelAlarm.dayAlarm.Add((int)obj[0], new List<int>() { (int)obj[1] }); //告警等级，数量 701 if (date == listDate[1]) //周 702 deviceAlarmCount.levelAlarm.weekAlarm.Add((int)obj[0], new List<int>() { (int)obj[1] }); //告警等级，数量 703 if (date == listDate[2]) //月 704 deviceAlarmCount.levelAlarm.monthAlarm.Add((int)obj[0], new List<int>() { (int)obj[1] }); //告警等级，数量 705 if (date == listDate[3]) //年 706 deviceAlarmCount.levelAlarm.yearAlarm.Add((int)obj[0], new List<int>() { (int)obj[1] }); //告警等级，数量 707 } 708 } 709 } 710 //告警类型趋势 711 deviceAlarmCount.typeAlarm.dayAlarm = GetAlarmTypeThrendAlarmDic(dateList[0], 0); 712 deviceAlarmCount.typeAlarm.weekAlarm = GetAlarmTypeThrendAlarmDic(dateList[1], 1); 713 deviceAlarmCount.typeAlarm.monthAlarm = GetAlarmTypeThrendAlarmDic(dateList[2], 2); 714 deviceAlarmCount.typeAlarm.yearAlarm = GetAlarmTypeThrendAlarmDic(dateList[3], 3); 715 //设备对象告警趋势 716 deviceAlarmCount.deviceObjectAlarm.dayAlarm = GetDeviceObjectThrendAlarmDic(dateList[0], 0); 717 deviceAlarmCount.deviceObjectAlarm.weekAlarm = GetDeviceObjectThrendAlarmDic(dateList[1], 1); 718 deviceAlarmCount.deviceObjectAlarm.monthAlarm = GetDeviceObjectThrendAlarmDic(dateList[2], 2); 719 deviceAlarmCount.deviceObjectAlarm.yearAlarm = GetDeviceObjectThrendAlarmDic(dateList[3], 3); 720 return deviceAlarmCount; 721 } 722 catch (Exception ex) 723 { 724 LogHelper.GetLog("Server").Error(ExceptionHelper.GetExceptionDesc(ex)); 725 return null; 726 } 727 } 728 729 /// <summary> 730 /// 多个设备的告警统计 731 /// </summary> 732 /// <param name="deviceId"></param> 733 /// <returns></returns> 734 public DevMultiAlarmCountDTO GetDevMultiAlarmCount(List<int> deviceIdList) 735 { 736 try 737 { 738 DevMultiAlarmCountDTO devObjAlarmCount = new DevMultiAlarmCountDTO() 739 { 740 dev1ObjAlarm = new ItemAlarmDTO() { dayAlarm = new Dictionary<int, List<int>>(), monthAlarm = new Dictionary<int, List<int>>(), weekAlarm = new Dictionary<int, List<int>>(), yearAlarm = new Dictionary<int, List<int>>() }, 741 //dev2ObjAlarm = new ItemAlarmDTO() { dayAlarm = new Dictionary<int, List<int>>(), monthAlarm = new Dictionary<int, List<int>>(), weekAlarm = new Dictionary<int, List<int>>(), yearAlarm = new Dictionary<int, List<int>>() }, 742 }; 743 if (deviceIdList.Count == 0) 744 return devObjAlarmCount; 745 if (deviceIdList.Count == 1) 746 { 747 var date1List = GetDeviceDateAlarmList(deviceIdList[0]); 748 //设备对象告警趋势 749 devObjAlarmCount.dev1ObjAlarm.dayAlarm = GetDeviceObjectThrendAlarmDic(date1List[0], 0); 750 devObjAlarmCount.dev1ObjAlarm.weekAlarm = GetDeviceObjectThrendAlarmDic(date1List[1], 1); 751 devObjAlarmCount.dev1ObjAlarm.monthAlarm = GetDeviceObjectThrendAlarmDic(date1List[2], 2); 752 devObjAlarmCount.dev1ObjAlarm.yearAlarm = GetDeviceObjectThrendAlarmDic(date1List[3], 3); 753 return devObjAlarmCount; 754 } 755 //if (deviceIdList.Count == 2) 756 //{ 757 // var date1List = GetDeviceDateAlarmList(deviceIdList[0]); 758 // //设备对象告警趋势 759 // devObjAlarmCount.dev1ObjAlarm.dayAlarm = GetDeviceObjectThrendAlarmDic(date1List[0], 0); 760 // devObjAlarmCount.dev1ObjAlarm.weekAlarm = GetDeviceObjectThrendAlarmDic(date1List[1], 1); 761 // devObjAlarmCount.dev1ObjAlarm.monthAlarm = GetDeviceObjectThrendAlarmDic(date1List[2], 2); 762 // devObjAlarmCount.dev1ObjAlarm.yearAlarm = GetDeviceObjectThrendAlarmDic(date1List[3], 3); 763 // var date2List = GetDeviceDateAlarmList(deviceIdList[1]); 764 // //设备对象告警趋势 765 // devObjAlarmCount.dev1ObjAlarm.dayAlarm = GetDeviceObjectThrendAlarmDic(date2List[0], 0); 766 // devObjAlarmCount.dev1ObjAlarm.weekAlarm = GetDeviceObjectThrendAlarmDic(date2List[1], 1); 767 // devObjAlarmCount.dev1ObjAlarm.monthAlarm = GetDeviceObjectThrendAlarmDic(date2List[2], 2); 768 // devObjAlarmCount.dev1ObjAlarm.yearAlarm = GetDeviceObjectThrendAlarmDic(date2List[3], 3); 769 // return devObjAlarmCount; 770 //} 771 return devObjAlarmCount; 772 } 773 catch (Exception ex) 774 { 775 LogHelper.GetLog("Server").Error(ExceptionHelper.GetExceptionDesc(ex)); 776 return null; 777 } 778 } 779 #endregion 780 781 #region 整体告警统计 782 /// <summary> 783 /// 1整体告警等级统计 784 /// </summary> 785 /// <param name="timeType"></param> 786 /// <returns></returns> 787 public Dictionary<int, int> AllAlarmLevel(int timeType) 788 { 789 try 790 { 791 Dictionary<int, int> dic = new Dictionary<int,int>(); 792 string sql = String.Format("SELECT t3.a2 AS x,SUM(t3.b2)AS y FROM(SELECT*FROM(SELECT alarm_code AS a1,alarm_level AS a2 FROM def_type_alarm) AS t1,(SELECT alarm_code AS b1,COUNT (*) AS b2 FROM his_alarm WHERE alarm_time > '{0}' GROUP BY alarm_code) AS t2 WHERE t1.a1 = t2.b1) AS t3 GROUP BY t3.a2", listDate[timeType].ToString("yyyy-MM-dd HH:mm:ss")); 793 var listResult = alarmDao.GetSession().CreateSQLQuery(sql).AddScalar("x", NHibernateUtil.Int32).AddScalar("y", NHibernateUtil.Int32).List(); 794 if (listResult.Count == 0) 795 return dic; 796 foreach (var item in listResult) //遍历查询到的结果 797 { 798 Object[] obj = (Object[])item; 799 dic.Add((int)obj[0], (int)obj[1]); 800 } 801 //保证dic中有三个元素 802 for (int i = 1; i <= 3; i++) 803 { 804 if (!dic.Keys.Contains(i)) 805 dic.Add(i, 0); 806 } 807 return dic; 808 } 809 catch (Exception ex) 810 { 811 LogHelper.GetLog("Server").Error(ExceptionHelper.GetExceptionDesc(ex)); 812 return null; 813 } 814 } 815 /// <summary> 816 /// 2整体告警子系统统计 817 /// </summary> 818 /// <param name="timeType"></param> 819 /// <returns></returns> 820 public Dictionary<int, int> AllAlarmSubCount(int timeType) 821 { 822 try 823 { 824 Dictionary<int, int> dic = new Dictionary<int, int>(); 825 string sql = String.Format("SELECT cfg_object_subsystem.object_id AS x,ISNULL(a2, 0)AS y FROM cfg_object_subsystem LEFT JOIN (SELECT subsys_id AS a1,COUNT(subsys_id) AS a2 FROM his_alarm WHERE alarm_time>'{0}' GROUP BY subsys_id)AS t1 ON t1.a1=cfg_object_subsystem.object_id", listDate[timeType].ToString("yyyy-MM-dd HH:mm:ss")); 826 var listResult = alarmDao.GetSession().CreateSQLQuery(sql).AddScalar("x", NHibernateUtil.Int32).AddScalar("y", NHibernateUtil.Int32).List(); 827 if (listResult.Count == 0) 828 return dic; 829 foreach (var item in listResult) //遍历查询到的结果 830 { 831 Object[] obj = (Object[])item; 832 dic.Add((int)obj[0], (int)obj[1]); 833 } 834 //dic集合中有不为0的元素则返回集合 835 foreach (var item in dic.Keys) 836 { 837 if (!(dic[item] == 0)) 838 return dic; 839 } 840 return null; 841 } 842 catch (Exception ex) 843 { 844 LogHelper.GetLog("Server").Error(ExceptionHelper.GetExceptionDesc(ex)); 845 return null; 846 } 847 } 848 /// <summary> 849 /// 3整体告警告警类型top10 850 /// </summary> 851 /// <param name="timeType"></param> 852 /// <returns></returns> 853 public Dictionary<int, int> AllAlarmTypeTop10(int timeType) 854 { 855 try 856 { 857 Dictionary<int, int> dic = new Dictionary<int, int>(); 858 String sql = String.Format("SELECT his_alarm.alarm_code AS x,COUNT(his_alarm.alarm_code)AS y FROM his_alarm WHERE his_alarm.alarm_time>'{0}' GROUP BY alarm_code ORDER BY COUNT(his_alarm.alarm_code) DESC", listDate[timeType].ToString("yyyy-MM-dd HH:mm:ss")); 859 var listResult = alarmDao.GetSession().CreateSQLQuery(sql).AddScalar("x", NHibernateUtil.Int32).AddScalar("y", NHibernateUtil.Int32).SetMaxResults(10).List(); 860 if (listResult.Count == 0) 861 return dic; 862 foreach (var item in listResult) //遍历查询到的结果 863 { 864 Object[] obj = (Object[])item; 865 dic.Add((int)obj[0], (int)obj[1]); 866 } 867 //dic集合中有不为0的元素则返回集合 868 foreach (var item in dic.Keys) 869 { 870 if (!(dic[item] == 0)) 871 return dic; 872 } 873 return null; 874 } 875 catch (Exception ex) 876 { 877 LogHelper.GetLog("Server").Error(ExceptionHelper.GetExceptionDesc(ex)); 878 return null; 879 } 880 } 881 /// <summary> 882 /// 4整体告警设备类型top10 883 /// </summary> 884 /// <param name="timeType"></param> 885 /// <returns></returns> 886 public Dictionary<int, int> AllAlarmDeviceTop10(int timeType) 887 { 888 try 889 { 890 Dictionary<int, int> dic = new Dictionary<int, int>(); 891 String sql = String.Format("SELECT t2.b2 AS x,sum(a2) AS y FROM(SELECT alarm_objectid AS a1,COUNT (*) AS a2 FROM his_alarm WHERE his_alarm.alarm_time > '{0}'GROUP BY alarm_objectid) AS t1,(SELECT object_id AS b1,object_type AS b2 FROM cfg_object_device) AS t2 WHERE t1.a1 = t2.b1 GROUP BY t2.b2 ORDER BY y DESC", listDate[timeType].ToString("yyyy-MM-dd HH:mm:ss")); 892 var listResult = alarmDao.GetSession().CreateSQLQuery(sql).AddScalar("x", NHibernateUtil.Int32).AddScalar("y", NHibernateUtil.Int32).SetMaxResults(10).List(); 893 if (listResult.Count == 0) 894 return dic; 895 foreach (var item in listResult) //遍历查询到的结果 896 { 897 Object[] obj = (Object[])item; 898 dic.Add((int)obj[0], (int)obj[1]); 899 } 900 //dic集合中有不为0的元素则返回集合 901 foreach (var item in dic.Keys) 902 { 903 if (!(dic[item] == 0)) 904 return dic; 905 } 906 return null; 907 } 908 catch (Exception ex) 909 { 910 LogHelper.GetLog("Server").Error(ExceptionHelper.GetExceptionDesc(ex)); 911 return null; 912 } 913 } 914 /// <summary> 915 /// 5整体告警模块统计 916 /// </summary> 917 /// <param name="timeType"></param> 918 /// <returns></returns> 919 public Dictionary<int, int> AllAlarmModuleCount(int timeType) 920 { 921 try 922 { 923 Dictionary<int, int> dic = new Dictionary<int, int>(); 924 String sql = String.Format("SELECT cfg_object_module.object_id AS x,ISNULL(t2.r, 0)AS y FROM cfg_object_module LEFT JOIN(SELECT t1.a2,SUM(t1.a3) AS r FROM(SELECT alarm_objectid AS a1,(CASE WHEN alarm_objectid IN (SELECT cfg_object_device.object_id FROM cfg_object_device) THEN (SELECT cfg_object_device.parent_id FROM cfg_object_device WHERE cfg_object_device.object_id = alarm_objectid)ELSE alarm_objectid END) AS a2,COUNT (*) AS a3 FROM his_alarm WHERE alarm_time > '{0}' GROUP BY alarm_objectid) AS t1 GROUP BY t1.a2)AS t2 ON t2.a2=cfg_object_module.object_id", listDate[timeType].ToString("yyyy-MM-dd HH:mm:ss")); 925 var listResult = alarmDao.GetSession().CreateSQLQuery(sql).AddScalar("x", NHibernateUtil.Int32).AddScalar("y", NHibernateUtil.Int32).List(); 926 if (listResult.Count == 0) 927 return dic; 928 foreach (var item in listResult) //遍历查询到的结果 929 { 930 Object[] obj = (Object[])item; 931 dic.Add((int)obj[0], (int)obj[1]); 932 } 933 //dic集合中有不为0的元素则返回集合 934 foreach (var item in dic.Keys) 935 { 936 if (!(dic[item] == 0)) 937 return dic; 938 } 939 return null; 940 } 941 catch (Exception ex) 942 { 943 LogHelper.GetLog("Server").Error(ExceptionHelper.GetExceptionDesc(ex)); 944 return null; 945 } 946 } 947 /// <summary> 948 /// 6整体告警子系统状态统计 949 /// </summary> 950 /// <param name="timeType"></param> 951 /// <returns></returns> 952 public Dictionary<int, List<int>> AllAlarmSubStatus(int timeType) 953 { 954 try 955 { 956 Dictionary<int, List<int>> dic = new Dictionary<int, List<int>>(); 957 string sql = String.Format("SELECT cfg_object_subsystem.object_id AS x,ISNULL(t1.a2, 0)AS y1,ISNULL(t1.a3, 0)AS y2,ISNULL(t1.a4, 0)AS y3 FROM cfg_object_subsystem LEFT JOIN(SELECT subsys_id AS a1,COUNT (*) AS a2,COUNT (CASE WHEN status != 3 THEN 1 END) AS a3,COUNT (CASE WHEN status = 3 THEN 1 END) AS a4 FROM his_alarm WHERE alarm_time > '{0}' GROUP BY subsys_id)AS t1 ON t1.a1=cfg_object_subsystem.object_id", listDate[timeType].ToString("yyyy-MM-dd HH:mm:ss")); 958 var listResult = alarmDao.GetSession().CreateSQLQuery(sql).AddScalar("x", NHibernateUtil.Int32).AddScalar("y1", NHibernateUtil.Int32).AddScalar("y2", NHibernateUtil.Int32).AddScalar("y3", NHibernateUtil.Int32).List(); 959 if (listResult.Count == 0) 960 return dic; 961 foreach (var item in listResult) //遍历查询到的结果 962 { 963 Object[] obj = (Object[])item; 964 dic.Add((int)obj[0], new List<int>(){(int)obj[1], (int)obj[2], (int)obj[3]}); 965 } 966 //dic集合中有不为0的元素即返回集合 967 foreach (var item in dic.Keys) 968 { 969 if (!(dic[item][0] == 0)) 970 return dic; 971 } 972 return null; 973 } 974 catch (Exception ex) 975 { 976 LogHelper.GetLog("Server").Error(ExceptionHelper.GetExceptionDesc(ex)); 977 return null; 978 } 979 } 980 #endregion 981 982 #region 子系统告警统计 983 /// <summary> 984 /// 1子系统告警等级统计 985 /// </summary> 986 /// <param name="timeType"></param> 987 /// <returns></returns> 988 public Dictionary<int, int> SubAlarmLevel(int timeType, int subsysId) 989 { 990 try 991 { 992 Dictionary<int, int> dic = new Dictionary<int, int>(); 993 string sql = String.Format("SELECT t3.a2 AS x,SUM(t3.b2)AS y FROM(SELECT*FROM(SELECT alarm_code AS a1,alarm_level AS a2 FROM def_type_alarm) AS t1,(SELECT alarm_code AS b1,COUNT (*) AS b2 FROM his_alarm WHERE alarm_time > '{0}' And his_alarm.subsys_id ='{1}' GROUP BY alarm_code) AS t2 WHERE t1.a1 = t2.b1) AS t3 GROUP BY t3.a2", listDate[timeType].ToString("yyyy-MM-dd HH:mm:ss"), subsysId.ToString()); 994 var listResult = alarmDao.GetSession().CreateSQLQuery(sql).AddScalar("x", NHibernateUtil.Int32).AddScalar("y", NHibernateUtil.Int32).List(); 995 if (listResult.Count == 0) 996 return dic; 997 foreach (var item in listResult) //遍历查询到的结果 998 { 999 Object[] obj = (Object[])item; 1000 dic.Add((int)obj[0], (int)obj[1]); 1001 } 1002 //保证dic中有三个元素 1003 for (int i = 1; i <= 3; i++) 1004 { 1005 if (!dic.Keys.Contains(i)) 1006 dic.Add(i, 0); 1007 } 1008 return dic; 1009 } 1010 catch (Exception ex) 1011 { 1012 LogHelper.GetLog("Server").Error(ExceptionHelper.GetExceptionDesc(ex)); 1013 return null; 1014 } 1015 } 1016 /// <summary> 1017 /// 2子系统告警趋势 1018 /// </summary> 1019 /// <returns></returns> 1020 public List<int> SubAlarmTrend(int timeType, int subsysId) 1021 { 1022 try 1023 { 1024 List<int> listResult = new List<int>(); 1025 String sql = String.Format("SELECT his_alarm.alarm_time as dt FROM his_alarm WHERE alarm_time>'{0}' AND subsys_id='{1}'", listDate[timeType].ToString("yyyy-MM-dd HH:mm:ss"), subsysId.ToString()); 1026 var listReturn = alarmDao.GetSession().CreateSQLQuery(sql).AddScalar("dt", NHibernateUtil.DateTime).List<DateTime>(); 1027 var list = (List<DateTime>)listReturn; 1028 if (list.Count == 0) 1029 return listResult; 1030 switch (timeType) 1031 { 1032 case 0: 1033 DateTime day = listDate[0];//当天开始时间 1034 for (int i = 0; i <= DateTime.Now.Hour; i++) //DateTime.Now.Hour当天的小时数 1035 { 1036 var hour1 = day.AddHours(i); 1037 var hour2 = day.AddHours(i + 1); 1038 listResult.Add(list.Count(it => hour1 < it && it <= hour2)); 1039 } 1040 break; 1041 case 1: 1042 DateTime week = listDate[1]; //本周开始时间 1043 int dayshu = DateTime.Now.Day - week.Day + 1; //本周的第几天 1044 for (int i = 0; i < dayshu; i++) 1045 { 1046 var day1 = week.AddDays(i); 1047 var day2 = week.AddDays(i + 1); 1048 listResult.Add(list.Count(it => day1 < it && it <= day2)); 1049 } 1050 break; 1051 case 2: 1052 DateTime month = listDate[2]; //本月开始时间 1053 for (int i = 0; i < DateTime.Now.Day; i++) //DateTime.Now.Day表示该月中的第几天 1054 { 1055 var day1 = month.AddDays(i); 1056 var day2 = month.AddDays(i + 1); 1057 listResult.Add(list.Count(it => day1 < it && it <= day2)); 1058 } 1059 break; 1060 case 3: 1061 DateTime year = listDate[3]; //本年开始时间 1062 for (int i = 0; i < DateTime.Now.Month; i++) //DateTime.Now.Month表示该年中的第几个月 1063 { 1064 var month1 = year.AddMonths(i); 1065 var month2 = year.AddMonths(i + 1); 1066 listResult.Add(list.Count(it => month1 < it && it <= month2)); 1067 } 1068 break; 1069 } 1070 //集合中有不为0的元素则返回集合 1071 foreach (var item in listResult) 1072 { 1073 if (!(item == 0)) 1074 return listResult; 1075 } 1076 return null; 1077 } 1078 catch (Exception ex) 1079 { 1080 LogHelper.GetLog("Server").Error(ExceptionHelper.GetExceptionDesc(ex)); 1081 return null; 1082 } 1083 } 1084 /// <summary> 1085 /// 3子系统告警告警类型top10 1086 /// </summary> 1087 /// <param name="timeType"></param> 1088 /// <param name="subsysId"></param> 1089 /// <returns></returns> 1090 public Dictionary<int, int> SubAlarmType10(int timeType, int subsysId) 1091 { 1092 try 1093 { 1094 Dictionary<int, int> dic = new Dictionary<int, int>(); 1095 String sql = String.Format("SELECT his_alarm.alarm_code AS x,COUNT(his_alarm.alarm_code)AS y FROM his_alarm WHERE his_alarm.alarm_time>'{0}' AND his_alarm.subsys_id = '{1}' GROUP BY alarm_code ORDER BY COUNT(his_alarm.alarm_code) DESC", listDate[timeType].ToString("yyyy-MM-dd HH:mm:ss"), subsysId.ToString()); 1096 var listResult = alarmDao.GetSession().CreateSQLQuery(sql).AddScalar("x", NHibernateUtil.Int32).AddScalar("y", NHibernateUtil.Int32).SetMaxResults(10).List(); 1097 if (listResult.Count == 0) 1098 return dic; 1099 foreach (var item in listResult) //遍历查询到的结果 1100 { 1101 Object[] obj = (Object[])item; 1102 dic.Add(Convert.ToInt32(obj[0]), Convert.ToInt32(obj[1])); 1103 } 1104 foreach (var item in dic.Keys) 1105 { 1106 if (!(dic[item] == 0)) 1107 return dic; 1108 } 1109 return null; 1110 } 1111 catch (Exception ex) 1112 { 1113 LogHelper.GetLog("Server").Error(ExceptionHelper.GetExceptionDesc(ex)); 1114 return null; 1115 } 1116 } 1117 /// <summary> 1118 /// 4子系统告警设备对象top10 1119 /// </summary> 1120 /// <param name="timeType"></param> 1121 /// <param name="subsysId"></param> 1122 /// <returns></returns> 1123 public Dictionary<int, int> SubAlarmDeviceObject10(int timeType, int subsysId) 1124 { 1125 try 1126 { 1127 Dictionary<int, int> dic = new Dictionary<int, int>(); 1128 String sql = String.Format("SELECT t1.a1 AS x,sum(t1.a2) AS y FROM(SELECT alarm_objectid AS a1,COUNT (*) AS a2 FROM his_alarm WHERE his_alarm.alarm_time > '{0}' And his_alarm.subsys_id ='{1}' GROUP BY alarm_objectid) AS t1,(SELECT object_id AS b1 FROM cfg_object_device) AS t2 WHERE t1.a1 = t2.b1 GROUP BY t1.a1 ORDER BY y DESC", listDate[timeType].ToString("yyyy-MM-dd HH:mm:ss"), subsysId.ToString()); 1129 var listResult = alarmDao.GetSession().CreateSQLQuery(sql).AddScalar("x", NHibernateUtil.Int32).AddScalar("y", NHibernateUtil.Int32).SetMaxResults(10).List(); 1130 if (listResult.Count == 0) 1131 return dic; 1132 foreach (var item in listResult) //遍历查询到的结果 1133 { 1134 Object[] obj = (Object[])item; 1135 dic.Add(Convert.ToInt32(obj[0]), Convert.ToInt32(obj[1])); 1136 } 1137 foreach (var item in dic.Keys) 1138 { 1139 if (!(dic[item] == 0)) 1140 return dic; 1141 } 1142 return null; 1143 } 1144 catch (Exception ex) 1145 { 1146 LogHelper.GetLog("Server").Error(ExceptionHelper.GetExceptionDesc(ex)); 1147 return null; 1148 } 1149 } 1150 /// <summary> 1151 /// 5子系统告警设备类型top10 1152 /// </summary> 1153 /// <param name="timeType"></param> 1154 /// <param name="subsysId"></param> 1155 /// <returns></returns> 1156 public Dictionary<int, int> SubAlarmDeviceType10(int timeType, int subsysId) 1157 { 1158 try 1159 { 1160 Dictionary<int, int> dic = new Dictionary<int, int>(); 1161 String sql = String.Format("SELECT t2.b2 AS x,sum(a2) AS y FROM(SELECT alarm_objectid AS a1,COUNT (*) AS a2 FROM his_alarm WHERE his_alarm.alarm_time > '{0}' And his_alarm.subsys_id ='{1}' GROUP BY alarm_objectid) AS t1,(SELECT object_id AS b1,object_type AS b2 FROM cfg_object_device) AS t2 WHERE t1.a1 = t2.b1 GROUP BY t2.b2 ORDER BY y DESC", listDate[timeType].ToString("yyyy-MM-dd HH:mm:ss"), subsysId.ToString()); 1162 var listResult = alarmDao.GetSession().CreateSQLQuery(sql).AddScalar("x", NHibernateUtil.Int32).AddScalar("y", NHibernateUtil.Int32).SetMaxResults(10).List(); 1163 if (listResult.Count == 0) 1164 return dic; 1165 foreach (var item in listResult) //遍历查询到的结果 1166 { 1167 Object[] obj = (Object[])item; 1168 dic.Add(Convert.ToInt32(obj[0]), Convert.ToInt32(obj[1])); 1169 } 1170 foreach (var item in dic.Keys) 1171 { 1172 if (!(dic[item] == 0)) 1173 return dic; 1174 } 1175 return null; 1176 } 1177 catch (Exception ex) 1178 { 1179 LogHelper.GetLog("Server").Error(ExceptionHelper.GetExceptionDesc(ex)); 1180 return null; 1181 } 1182 } 1183 /// <summary> 1184 /// 6子系统告警状态趋势 1185 /// </summary> 1186 /// <returns></returns> 1187 public List<int> SubAlarmStatusTrend(int timeType, int subsysId) 1188 { 1189 try 1190 { 1191 List<int> listResult = new List<int>(); 1192 String sql = String.Format("SELECT his_alarm.alarm_time as dt FROM his_alarm WHERE alarm_time>'{0}' AND subsys_id='{1}'", listDate[timeType].ToString("yyyy-MM-dd HH:mm:ss"), subsysId.ToString()); 1193 var listAll = alarmDao.GetSession().CreateSQLQuery(sql).AddScalar("dt", NHibernateUtil.DateTime).List<DateTime>(); 1194 var list = (List<DateTime>)listAll; 1195 if (listAll.Count == 0) 1196 return listResult; 1197 String sql2 = String.Format("SELECT his_alarm.alarm_time as dt FROM his_alarm WHERE alarm_time>'{0}' AND subsys_id='{1}' AND his_alarm.status!='3'", listDate[timeType].ToString("yyyy-MM-dd HH:mm:ss"), subsysId.ToString()); 1198 var listAll2 = alarmDao.GetSession().CreateSQLQuery(sql2).AddScalar("dt", NHibernateUtil.DateTime).List<DateTime>(); 1199 var list2 = (List<DateTime>)listAll2; 1200 switch (timeType) 1201 { 1202 case 0: 1203 DateTime day = listDate[0];//当天开始时间 1204 for (int i = 0; i <= DateTime.Now.Hour; i++) //DateTime.Now.Hour当天的小时数 1205 { 1206 var hour1 = day.AddHours(i); 1207 var hour2 = day.AddHours(i + 1); 1208 var count1 = list.Count(it => hour1 < it && it <= hour2); 1209 var count2 = list2.Count(it => hour1 < it && it <= hour2); 1210 listResult.Add(count1); 1211 listResult.Add(count2); 1212 listResult.Add(count1-count2); 1213 } 1214 break; 1215 case 1: 1216 DateTime week = listDate[1]; //本周开始时间 1217 int dayshu = DateTime.Now.Day - week.Day + 1; //本周的第几天 1218 for (int i = 0; i < dayshu; i++) 1219 { 1220 var day1 = week.AddDays(i); 1221 var day2 = week.AddDays(i + 1); 1222 var count1 = list.Count(it => day1 < it && it <= day2); 1223 var count2 = list2.Count(it => day1 < it && it <= day2); 1224 listResult.Add(count1); 1225 listResult.Add(count2); 1226 listResult.Add(count1 - count2); 1227 } 1228 break; 1229 case 2: 1230 DateTime month = listDate[2]; //本月开始时间 1231 for (int i = 0; i < DateTime.Now.Day; i++) //DateTime.Now.Day表示该月中的第几天 1232 { 1233 var day1 = month.AddDays(i); 1234 var day2 = month.AddDays(i + 1); 1235 var count1 = list.Count(it => day1 < it && it <= day2); 1236 var count2 = list2.Count(it => day1 < it && it <= day2); 1237 listResult.Add(count1); 1238 listResult.Add(count2); 1239 listResult.Add(count1 - count2); 1240 } 1241 break; 1242 case 3: 1243 DateTime year = listDate[3]; //本年开始时间 1244 for (int i = 0; i < DateTime.Now.Month; i++) //DateTime.Now.Month表示该年中的第几个月 1245 { 1246 var month1 = year.AddMonths(i); 1247 var month2 = year.AddMonths(i + 1); 1248 var count1 = list.Count(it => month1 < it && it <= month2); 1249 var count2 = list2.Count(it => month1 < it && it <= month2); 1250 listResult.Add(count1); 1251 listResult.Add(count2); 1252 listResult.Add(count1 - count2); 1253 } 1254 break; 1255 } 1256 foreach (var item in listResult) 1257 { 1258 if (!(item == 0)) 1259 return listResult; 1260 } 1261 return null; 1262 } 1263 catch (Exception ex) 1264 { 1265 LogHelper.GetLog("Server").Error(ExceptionHelper.GetExceptionDesc(ex)); 1266 return null; 1267 } 1268 } 1269 #endregion 1270 1271 #region 设备告警统计 1272 /// <summary> 1273 /// 1设备告警等级统计 1274 /// </summary> 1275 /// <param name="timeType"></param> 1276 /// <returns></returns> 1277 public Dictionary<int, int> DevAlarmLevel(int timeType, int deviceId) 1278 { 1279 try 1280 { 1281 Dictionary<int, int> dic = new Dictionary<int, int>(); 1282 string sql = String.Format("SELECT t3.a2 AS x,SUM(t3.b2)AS y FROM(SELECT*FROM(SELECT alarm_code AS a1,alarm_level AS a2 FROM def_type_alarm) AS t1,(SELECT alarm_code AS b1,COUNT (*) AS b2 FROM his_alarm WHERE alarm_time > '{0}' And his_alarm.alarm_objectid ='{1}' GROUP BY alarm_code) AS t2 WHERE t1.a1 = t2.b1) AS t3 GROUP BY t3.a2", listDate[timeType].ToString("yyyy-MM-dd HH:mm:ss"), deviceId.ToString()); 1283 var listRelut = alarmDao.GetSession().CreateSQLQuery(sql).AddScalar("x", NHibernateUtil.Int32).AddScalar("y", NHibernateUtil.Int32).List(); 1284 if (listRelut.Count == 0) 1285 return dic; 1286 foreach (var item in listRelut) //遍历查询到的结果 1287 { 1288 Object[] obj = (Object[])item; 1289 dic.Add((int)obj[0], (int)obj[1]); 1290 } 1291 //保证dic中有三个元素 1292 for (int i = 1; i <= 3; i++) 1293 { 1294 if (!dic.Keys.Contains(i)) 1295 dic.Add(i, 0); 1296 } 1297 return dic; 1298 } 1299 catch (Exception ex) 1300 { 1301 LogHelper.GetLog("Server").Error(ExceptionHelper.GetExceptionDesc(ex)); 1302 return null; 1303 } 1304 } 1305 /// <summary> 1306 /// 2,4设备告警趋势 1307 /// </summary> 1308 /// <returns></returns> 1309 public List<int> DevAlarmTrend(int timeType, int deviceId) 1310 { 1311 try 1312 { 1313 List<int> listResult = new List<int>(); 1314 String sql = String.Format("SELECT his_alarm.alarm_time AS dt FROM his_alarm WHERE alarm_time>'{0}' AND alarm_objectid='{1}'", listDate[timeType].ToString("yyyy-MM-dd HH:mm:ss"), deviceId.ToString()); 1315 var listRelut = alarmDao.GetSession().CreateSQLQuery(sql).AddScalar("dt", NHibernateUtil.DateTime).List<DateTime>(); 1316 var list = (List<DateTime>)listRelut; 1317 if (list.Count == 0) 1318 return listResult; 1319 switch (timeType) 1320 { 1321 case 0: 1322 DateTime day = listDate[0];//当天开始时间 1323 for (int i = 0; i <= DateTime.Now.Hour; i++) //DateTime.Now.Hour当天的小时数 1324 { 1325 var hour1 = day.AddHours(i); 1326 var hour2 = day.AddHours(i + 1); 1327 listResult.Add(list.Count(it => hour1 < it && it <= hour2)); 1328 } 1329 break; 1330 case 1: 1331 DateTime week = listDate[1]; //本周开始时间 1332 int dayshu = DateTime.Now.Day - week.Day + 1; //本周的第几天 1333 for (int i = 0; i < dayshu; i++) 1334 { 1335 var day1 = week.AddDays(i); 1336 var day2 = week.AddDays(i + 1); 1337 listResult.Add(list.Count(it => day1 < it && it <= day2)); 1338 } 1339 break; 1340 case 2: 1341 DateTime month = listDate[2]; //本月开始时间 1342 for (int i = 0; i < DateTime.Now.Day; i++) //DateTime.Now.Day表示该月中的第几天 1343 { 1344 var day1 = month.AddDays(i); 1345 var day2 = month.AddDays(i + 1); 1346 listResult.Add(list.Count(it => day1 < it && it <= day2)); 1347 } 1348 break; 1349 case 3: 1350 DateTime year = listDate[3]; //本年开始时间 1351 for (int i = 0; i < DateTime.Now.Month; i++) //DateTime.Now.Month表示该年中的第几个月 1352 { 1353 var month1 = year.AddMonths(i); 1354 var month2 = year.AddMonths(i + 1); 1355 listResult.Add(list.Count(it => month1 < it && it <= month2)); 1356 } 1357 break; 1358 } 1359 foreach (var item in listResult) 1360 { 1361 if (!(item == 0)) 1362 return listResult; 1363 } 1364 return null; 1365 } 1366 catch (Exception ex) 1367 { 1368 LogHelper.GetLog("Server").Error(ExceptionHelper.GetExceptionDesc(ex)); 1369 return null; 1370 } 1371 } 1372 /// <summary> 1373 /// 3设备告警类型趋势 1374 /// </summary> 1375 /// <param name="timeType"></param> 1376 /// <param name="deviceId"></param> 1377 /// <param name="alarmCode"></param> 1378 /// <returns></returns> 1379 public List<int> DevAlarmTypeTrend(int timeType, int deviceId, int alarmCode) 1380 { 1381 try 1382 { 1383 List<int> listResult = new List<int>(); 1384 String sql = String.Format("SELECT his_alarm.alarm_time AS dt FROM his_alarm WHERE alarm_time>'{0}' AND alarm_objectid='{1}' AND alarm_code='{2}'", listDate[timeType].ToString("yyyy-MM-dd HH:mm:ss"), deviceId.ToString(), alarmCode.ToString()); 1385 var listRelut = alarmDao.GetSession().CreateSQLQuery(sql).AddScalar("dt", NHibernateUtil.DateTime).List<DateTime>(); 1386 var list = (List<DateTime>)listRelut; 1387 if (list.Count == 0) 1388 return listResult; 1389 switch (timeType) 1390 { 1391 case 0: 1392 DateTime day = listDate[0];//当天开始时间 1393 for (int i = 0; i <= DateTime.Now.Hour; i++) //DateTime.Now.Hour当天的小时数 1394 { 1395 var hour1 = day.AddHours(i); 1396 var hour2 = day.AddHours(i + 1); 1397 listResult.Add(list.Count(it => hour1 < it && it <= hour2)); 1398 } 1399 break; 1400 case 1: 1401 DateTime week = listDate[1]; //本周开始时间 1402 int dayshu = DateTime.Now.Day - week.Day + 1; //本周的第几天 1403 for (int i = 0; i < dayshu; i++) 1404 { 1405 var day1 = week.AddDays(i); 1406 var day2 = week.AddDays(i + 1); 1407 listResult.Add(list.Count(it => day1 < it && it <= day2)); 1408 } 1409 break; 1410 case 2: 1411 DateTime month = listDate[2]; //本月开始时间 1412 for (int i = 0; i < DateTime.Now.Day; i++) //DateTime.Now.Day表示该月中的第几天 1413 { 1414 var day1 = month.AddDays(i); 1415 var day2 = month.AddDays(i + 1); 1416 listResult.Add(list.Count(it => day1 < it && it <= day2)); 1417 } 1418 break; 1419 case 3: 1420 DateTime year = listDate[3]; //本年开始时间 1421 for (int i = 0; i < DateTime.Now.Month; i++) //DateTime.Now.Month表示该年中的第几个月 1422 { 1423 var month1 = year.AddMonths(i); 1424 var month2 = year.AddMonths(i + 1); 1425 listResult.Add(list.Count(it => month1 < it && it <= month2)); 1426 } 1427 break; 1428 } 1429 foreach (var item in listResult) 1430 { 1431 if (!(item == 0)) 1432 return listResult; 1433 } 1434 return null; 1435 } 1436 catch (Exception ex) 1437 { 1438 LogHelper.GetLog("Server").Error(ExceptionHelper.GetExceptionDesc(ex)); 1439 return null; 1440 } 1441 } 1442 /// <summary> 1443 /// 5设备告警状态趋势 1444 /// </summary> 1445 /// <returns></returns> 1446 public List<int> DevAlarmStatusTrend(int timeType, int deviceId) 1447 { 1448 try 1449 { 1450 List<int> listResult = new List<int>(); 1451 String sql = String.Format("SELECT his_alarm.alarm_time as dt FROM his_alarm WHERE alarm_time>'{0}' AND alarm_objectid='{1}'", listDate[timeType].ToString("yyyy-MM-dd HH:mm:ss"), deviceId.ToString()); 1452 var listAll = alarmDao.GetSession().CreateSQLQuery(sql).AddScalar("dt", NHibernateUtil.DateTime).List<DateTime>(); 1453 var list = (List<DateTime>)listAll; 1454 if (listAll.Count == 0) 1455 return listResult; 1456 String sql2 = String.Format("SELECT his_alarm.alarm_time as dt FROM his_alarm WHERE alarm_time>'{0}' AND alarm_objectid='{1}' AND his_alarm.status!='3'", listDate[timeType].ToString("yyyy-MM-dd HH:mm:ss"), deviceId.ToString()); 1457 var listAll2 = alarmDao.GetSession().CreateSQLQuery(sql2).AddScalar("dt", NHibernateUtil.DateTime).List<DateTime>(); 1458 var list2 = (List<DateTime>)listAll2; 1459 switch (timeType) 1460 { 1461 case 0: 1462 DateTime day = listDate[0];//当天开始时间 1463 for (int i = 0; i <= DateTime.Now.Hour; i++) //DateTime.Now.Hour当天的小时数 1464 { 1465 var hour1 = day.AddHours(i); 1466 var hour2 = day.AddHours(i + 1); 1467 var count1 = list.Count(it => hour1 < it && it <= hour2); 1468 var count2 = list2.Count(it => hour1 < it && it <= hour2); 1469 listResult.Add(count1); //总告警数 1470 listResult.Add(count2); //新生成 1471 listResult.Add(count1 - count2); //已完成 1472 } 1473 break; 1474 case 1: 1475 DateTime week = listDate[1]; //本周开始时间 1476 int dayshu = DateTime.Now.Day - week.Day + 1; //本周的第几天 1477 for (int i = 0; i < dayshu; i++) 1478 { 1479 var day1 = week.AddDays(i); 1480 var day2 = week.AddDays(i + 1); 1481 var count1 = list.Count(it => day1 < it && it <= day2); 1482 var count2 = list2.Count(it => day1 < it && it <= day2); 1483 listResult.Add(count1); 1484 listResult.Add(count2); 1485 listResult.Add(count1 - count2); 1486 } 1487 break; 1488 case 2: 1489 DateTime month = listDate[2]; //本月开始时间 1490 for (int i = 0; i < DateTime.Now.Day; i++) //DateTime.Now.Day表示该月中的第几天 1491 { 1492 var day1 = month.AddDays(i); 1493 var day2 = month.AddDays(i + 1); 1494 var count1 = list.Count(it => day1 < it && it <= day2); 1495 var count2 = list2.Count(it => day1 < it && it <= day2); 1496 listResult.Add(count1); 1497 listResult.Add(count2); 1498 listResult.Add(count1 - count2); 1499 } 1500 break; 1501 case 3: 1502 DateTime year = listDate[3]; //本年开始时间 1503 for (int i = 0; i < DateTime.Now.Month; i++) //DateTime.Now.Month表示该年中的第几个月 1504 { 1505 var month1 = year.AddMonths(i); 1506 var month2 = year.AddMonths(i + 1); 1507 var count1 = list.Count(it => month1 < it && it <= month2); 1508 var count2 = list2.Count(it => month1 < it && it <= month2); 1509 listResult.Add(count1); 1510 listResult.Add(count2); 1511 listResult.Add(count1 - count2); 1512 } 1513 break; 1514 } 1515 foreach (var item in listResult) 1516 { 1517 if (!(item == 0)) 1518 return listResult; 1519 } 1520 return null; 1521 } 1522 catch (Exception ex) 1523 { 1524 LogHelper.GetLog("Server").Error(ExceptionHelper.GetExceptionDesc(ex)); 1525 return null; 1526 } 1527 } 1528 #endregion 1529 #endregion