public static List<SpecialReportRow> MonthReport(DateTime? startTime, DateTime? endTime, int detailId, string value, string line) { List<SpecialReportRow> result = new List<SpecialReportRow>(); List<EventRowWithAllColumns> rows = GetEventRowsByLine(startTime, endTime, detailId, value, line); DateTime r_starttime, r_endTime; r_starttime = (startTime.HasValue ? startTime.Value : rows.Min(o => o.EventStart).Value); //查询时结束日期是多一天的 r_endTime = (endTime.HasValue ? endTime.Value.AddDays(-1) : rows.Max(o => o.EventStart).Value); foreach (var item in DateTimeHelper.SplitMonths(r_starttime, r_endTime)) { var q = from o in rows where o.EventStart.Value.ToString(@"MM\/yyyy") == item.ToString(@"MM\/yyyy") select o; List<EventRowWithAllColumns> r = q.ToList(); SpecialReportRow srr = new SpecialReportRow(); ; srr.Title = item.ToString("MMM,yyyy", new System.Globalization.CultureInfo("en-US")); if (r != null) { srr.Minutes = r.Sum(o => o.Minutes); srr.Occurences = r.Count(); } else { srr.Minutes = 0; srr.Occurences = 0; } result.Add(srr); } int occ_total = result.Sum(o => o.Occurences); decimal? min_total = result.Sum(o => o.Minutes); foreach (SpecialReportRow item in result) { item.MinutesPercent = min_total == 0 ? 0 : item.Minutes.Value / min_total.Value * 100; item.OccurencesPercent = occ_total == 0 ? 0 : item.Occurences / occ_total * 100; } return result; }
public static List<SpecialReportRow> Hidden_YearReport(DateTime? startTime, DateTime? endTime, int detailId, string value, string line) { if (string.IsNullOrEmpty(line)) line = Filter_Line; List<SpecialReportRow> result = new List<SpecialReportRow>(); List<EventRowWithAllColumns> rows = Hidden_GetEventRows(startTime, endTime, detailId, value, line); DateTime r_starttime, r_endTime; r_starttime = (startTime.HasValue ? startTime.Value : rows.Min(o => o.EventStart).Value); //查询时结束日期是多一天的 r_endTime = (endTime.HasValue ? endTime.Value.AddDays(-1) : rows.Max(o => o.EventStart).Value); foreach (var item in DateTimeHelper.SplitYears(r_starttime, r_endTime)) { var q = from o in rows where o.EventStart.Value.ToString(@"yyyy") == item.ToString() select o; List<EventRowWithAllColumns> r = q.ToList(); SpecialReportRow srr = new SpecialReportRow(); ; srr.Title = item.ToString(); if (r != null) { srr.Minutes = r.Sum(o => o.Minutes); srr.Occurences = r.Count(); } else { srr.Minutes = 0; srr.Occurences = 0; } result.Add(srr); } int occ_total = result.Sum(o => o.Occurences); decimal? min_total = result.Sum(o => o.Minutes); foreach (SpecialReportRow item in result) { item.MinutesPercent = min_total == 0 ? 0 : item.Minutes.Value / min_total.Value * 100; item.OccurencesPercent = occ_total == 0 ? 0 : item.Occurences / occ_total * 100; } return result; }
/* public static List<SpecialReportRow> TopEventsGroupByLevel3(DateTime? startTime, DateTime? endTime, int detailId, string value, int takeCount, bool orderByMinutes, string line) { if (string.IsNullOrEmpty(line)) line = Filter_Line; using (DB db = new DB(DBHelper.GetConnectionString(Filter_Client))) { var q1 = from c in (from a in db.vw_DowntimeDetails where (!startTime.HasValue || a.EventStart >= startTime.Value) && (!endTime.HasValue || a.EventStart < endTime.Value) && (a.Client == Filter_Client || string.IsNullOrEmpty(Filter_Client)) && (a.Line == line || string.IsNullOrEmpty(line)) && a.Value == value join b in db.Details on a.DetailId equals b.Id where b.Id == detailId //&& !string.IsNullOrEmpty(b.Level2) //&& !string.IsNullOrEmpty(b.Level3) select new { a, b }) select new SpecialReportRow { Title = (!string.IsNullOrEmpty(g.Key.Level3) ? g.Key.Level3 : (!string.IsNullOrEmpty(g.Key.Level2) ? g.Key.Level2 : g.Key.Level1)), Minutes = g.Sum(o => o.a.Minutes), Occurences = g.Count(), appInfo = g.Min(o => o.b.ID) };//因为level3一定是唯一的,所以可以直接取一个MinId List<SpecialReportRow> result = q1.ToList(); result = (from o in result orderby (orderByMinutes ? o.Minutes : o.Occurences) descending, o.Title ascending select o).ToList(); //result = result.OrderByDescending(o => (orderByMinutes ? o.Minutes : o.Occurences)).ToList(); result = (takeCount > 0 ? result.Take(takeCount).ToList() : result); if (result != null) { int occ_total = result.Sum(o => o.Occurences); decimal? min_total = result.Sum(o => o.Minutes); foreach (var item in result) { item.OccurencesPercent = occ_total == 0 ? 0 : item.Occurences / Convert.ToDecimal(occ_total) * 100; item.MinutesPercent = min_total == 0 ? 0 : item.Minutes.Value / min_total.Value * 100; } result = result.OrderByDescending(o => (orderByMinutes ? o.Minutes : o.Occurences)).ToList(); } return result; } } */ public static List<SpecialReportRow> HoursReport(DateTime? startTime, DateTime? endTime, int detailId, string value, string line) { List<SpecialReportRow> result = new List<SpecialReportRow>(); List<EventRowWithAllColumns> rows = GetEventRowsByLine(startTime, endTime, detailId, value, line); if (rows != null) { for (var i = 0m; i < 24m; i += 0.5m) { var q = from o in rows where (o.EventStart.Value.Hour >= i && o.EventStart.Value.Hour < i + 0.5m) select o; List<EventRowWithAllColumns> r = q.ToList(); SpecialReportRow srr = new SpecialReportRow(); ; srr.Title = SecondToHoursString(Convert.ToInt32(i * 3600)); if (r != null) { srr.Minutes = r.Sum(o => o.Minutes); srr.Occurences = r.Count(); } else { srr.Minutes = 0; srr.Occurences = 0; } result.Add(srr); } } int occ_total = result.Sum(o => o.Occurences); decimal? min_total = result.Sum(o => o.Minutes); foreach (SpecialReportRow item in result) { item.MinutesPercent = min_total == 0 ? 0 : item.Minutes.Value / min_total.Value * 100; item.OccurencesPercent = occ_total == 0 ? 0 : item.Occurences / occ_total * 100; } return result; }
public static List<SpecialReportRow> DayReport(DateTime? startTime, DateTime? endTime, int detailId, string value, string line) { List<SpecialReportRow> result = new List<SpecialReportRow>(); List<EventRowWithAllColumns> rows = GetEventRowsByLine(startTime, endTime, detailId, value, line); if (rows != null) { DateTime r_starttime, r_endTime; r_starttime = (startTime.HasValue ? startTime.Value : rows.Min(o => o.EventStart).Value); //查询时结束日期是多一天的 r_endTime = (endTime.HasValue ? endTime.Value.AddDays(-1) : rows.Max(o => o.EventStart).Value); for (var i = 0; i <= r_endTime.Subtract(r_starttime).TotalDays; i++) { var q = from o in rows where o.EventStart.Value.ToString(@"MM\/dd\/yyyy") == r_starttime.AddDays(i).ToString(@"MM\/dd\/yyyy") select o; List<EventRowWithAllColumns> r = q.ToList(); SpecialReportRow srr = new SpecialReportRow(); ; srr.Title = r_starttime.AddDays(i).ToString(@"MM\/dd"); if (r != null) { srr.Minutes = r.Sum(o => o.Minutes); srr.Occurences = r.Count(); } else { srr.Minutes = 0; srr.Occurences = 0; } result.Add(srr); } } int occ_total = result.Sum(o => o.Occurences); decimal? min_total = result.Sum(o => o.Minutes); foreach (SpecialReportRow item in result) { item.MinutesPercent = min_total == 0 ? 0 : item.Minutes.Value / min_total.Value * 100; item.OccurencesPercent = occ_total == 0 ? 0 : item.Occurences / occ_total * 100; } return result; }