public ActionResult Index() { dtoRawInterfaceOverallSummary dto = new dtoRawInterfaceOverallSummary(); dto = dal.getSummaryData(); return(View(dto)); }
public dtoRawInterfaceOverallSummary getSummaryData() { dtoRawInterfaceOverallSummary dto = new dtoRawInterfaceOverallSummary(); string sql = @"DECLARE @dt DATETIME SET @dt = DATEADD(day,-8,getDate()) SELECT B.Cat, Action as Act, iDay as Span, count(B.[Id]) as Tot FROM ( SELECT TOP 10000 CASE WHEN CHARINDEX('Communication', ServiceID) > 0 THEN 'Communication' WHEN CHARINDEX('Customer', ServiceID) > 0 THEN 'Customer' WHEN CHARINDEX('Partner', ServiceID) > 0 THEN 'Partner' WHEN CHARINDEX('Material', ServiceID) > 0 THEN 'Material' WHEN CHARINDEX('CMIR', ServiceID) > 0 THEN 'CMIR' ELSE 'Unknown' END as Cat, abs(DateDiff(day,getDate(),J.Created)) as iDay, Action, ID FROM [jbsmstr].[scoMaster].[RawInterfaceData] J (NOLOCK) WHERE J.Created >= @dt ) B GROUP BY B.Cat, B.[Action], B.iDay ORDER BY B.iDay, B.Cat ASC"; List <dtoCatSummary> rData = db.Database.SqlQuery <dtoCatSummary>(sql).ToList(); dto.errorcount = rData.Where(i => i.act == "ERROR").Sum(i => i.tot); dto.insertcount = rData.Where(i => i.act == "INSERT").Sum(i => i.tot); dto.updatecount = rData.Where(i => i.act == "UPDATE").Sum(i => i.tot); dto.insertmax = 0; dto.updatemax = 0; dto.errormax = 0; for (int i = 0; i < 10; i++) { dtoGraphStat g = new dtoGraphStat(); DateTime d = DateTime.Now.AddDays(-i); g.x = d.DayOfWeek.ToString().Substring(0, 1).ToUpper().ToString(); int c = rData.Where(j => j.act == "INSERT").Where(j => j.span == i).Sum(j => j.tot); if (c > dto.insertmax) { dto.insertmax = c; } g.y = c.ToString(); g.desc = d.ToShortDateString(); dto.graphInserts.Add(g); } for (int i = 0; i < 10; i++) { dtoGraphStat g = new dtoGraphStat(); DateTime d = DateTime.Now.AddDays(-i); g.x = d.DayOfWeek.ToString().Substring(0, 1).ToUpper().ToString(); int c = rData.Where(j => j.act == "UPDATE").Where(j => j.span == i).Sum(j => j.tot); if (c > dto.updatemax) { dto.updatemax = c; } g.y = c.ToString(); g.desc = d.ToShortDateString(); dto.graphUpdates.Add(g); } for (int i = 0; i < 10; i++) { dtoGraphStat g = new dtoGraphStat(); DateTime d = DateTime.Now.AddDays(-i); g.x = d.DayOfWeek.ToString().Substring(0, 1).ToUpper().ToString(); int c = rData.Where(j => j.act == "ERROR").Where(j => j.span == i).Sum(j => j.tot); if (c > dto.errormax) { dto.errormax = c; } g.y = c.ToString(); g.desc = d.ToShortDateString(); dto.graphErrors.Add(g); } if ((dto.updatemax + dto.updatemax) <= 10) { dto.updatemax = 10; } if ((dto.insertmax + dto.insertmax) <= 10) { dto.insertmax = 10; } if ((dto.errormax + dto.errormax) <= 10) { dto.errormax = 10; } var gUpdate = rData.Where(o => o.act == "UPDATE").GroupBy(o => o.cat) .Select(g => new { cat = g.Key, tot = g.Sum(i => i.tot) }).ToList(); foreach (var r in gUpdate.OrderByDescending(v => v.tot)) { dtoCatSummary c = new dtoCatSummary(); c.cat = r.cat; c.tot = r.tot; dto.updatesummary.Add(c); } var gInsert = rData.Where(o => o.act == "INSERT").GroupBy(o => o.cat) .Select(g => new { cat = g.Key, tot = g.Sum(i => i.tot) }).ToList(); foreach (var r in gInsert.OrderByDescending(v => v.tot)) { dtoCatSummary c = new dtoCatSummary(); c.cat = r.cat; c.tot = r.tot; dto.insertsummary.Add(c); } var gError = rData.Where(o => o.act == "ERROR").GroupBy(o => o.cat) .Select(g => new { cat = g.Key, tot = g.Sum(i => i.tot) }).ToList(); foreach (var r in gError.OrderByDescending(v => v.tot)) { dtoCatSummary c = new dtoCatSummary(); c.cat = r.cat; c.tot = r.tot; dto.errorsummary.Add(c); } return(dto); }