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);
}
