public async Task <ActionVisitsDay> AggDTMVisitsByDay(DateTime?start, DateTime?end, string campaign_action = "all") { var DTMContext = new DTMVisitContext(); campaign_action = campaign_action.ToUpper(); if (!campaign_action.StartsWith("{")) { campaign_action = "{" + campaign_action; } if (!campaign_action.EndsWith("{")) { campaign_action = campaign_action + "}"; } if (start == null) { start = startDate; } if (end == null) { end = endDate; } var visitsDTMActionbyday = new List <DailyStatAction>(); if (campaign_action == "all") { visitsDTMActionbyday = await DTMContext.dtmVisits.Aggregate() .Match(x => x.DATA_HORA_INICI > start && x.DATA_HORA_INICI <= end) .Group(r => new { groupedYear = r.DATA_HORA_INICI.Year, groupedMonth = r.DATA_HORA_INICI.Month, groupedDay = r.DATA_HORA_INICI.Day, r.CAMPAIGN_ACTION_ID }, g => new { Key = g.Key, total = g.Count() }) .Project(r => new DailyStatAction() { Day = r.Key.groupedDay, Month = r.Key.groupedMonth, Year = r.Key.groupedYear, Action = r.Key.CAMPAIGN_ACTION_ID, Total = r.total }) .SortBy(x => x.Day).SortBy(x => x.Month).SortBy(x => x.Year) .ToListAsync(); } else { visitsDTMActionbyday = await DTMContext.dtmVisits.Aggregate() .Match(x => x.DATA_HORA_INICI > start && x.DATA_HORA_INICI <= end && x.CAMPAIGN_ACTION_ID == campaign_action) .Group(r => new { groupedYear = r.DATA_HORA_INICI.Year, groupedMonth = r.DATA_HORA_INICI.Month, groupedDay = r.DATA_HORA_INICI.Day, r.CAMPAIGN_ACTION_ID }, g => new { Key = g.Key, total = g.Count() }) .Project(r => new DailyStatAction() { Day = r.Key.groupedDay, Month = r.Key.groupedMonth, Year = r.Key.groupedYear, Action = r.Key.CAMPAIGN_ACTION_ID, Total = r.total }) .SortBy(x => x.Day).SortBy(x => x.Month).SortBy(x => x.Year) .ToListAsync(); } if (visitsDTMActionbyday.Count == 0) { return(new ActionVisitsDay()); } DateTime aux = new DateTime(visitsDTMActionbyday[0].Year, visitsDTMActionbyday[0].Month, visitsDTMActionbyday[0].Day); if (aux < start) { visitsDTMActionbyday.RemoveAt(0); aux = new DateTime(visitsDTMActionbyday[0].Year, visitsDTMActionbyday[0].Month, visitsDTMActionbyday[0].Day); } //List<ProjectionKeyDateValue> visitsDate = NormalizeDayList(visitsDTMbyday, aux); List <ProjectionKeyDateActionValue> visitsDate = new List <ProjectionKeyDateActionValue>(); foreach (var item in visitsDTMActionbyday) { DateTime dt = new DateTime(item.Year, item.Month, item.Day); if (dt == aux) { ProjectionKeyDateActionValue pkdv = new ProjectionKeyDateActionValue() { day = dt, campaign_action = item.Action, count = item.Total }; visitsDate.Add(pkdv); aux = dt.AddDays(1); } else { int sparedays = dt.Subtract(aux).Days; for (int i = 0; i < sparedays; i++) { DateTime newday = aux.AddDays(1); aux = newday; if (newday == dt) { ProjectionKeyDateActionValue pkdv = new ProjectionKeyDateActionValue() { day = dt, campaign_action = item.Action, count = item.Total }; visitsDate.Add(pkdv); } else { ProjectionKeyDateActionValue pkdv = new ProjectionKeyDateActionValue() { day = newday, campaign_action = item.Action, count = 0 }; visitsDate.Add(pkdv); } } } } ActionVisitsDay pd = new ActionVisitsDay() { Total = visitsDate.Sum(x => x.count), visitsDay = visitsDate.OrderBy(x => x.day) }; return(pd); }
public async Task <VisitsDay> GetDTMCampaignVisitsByDayAction(DateTime?start, DateTime?end, string action) { var DTMContext = new DTMVisitContext(); if (start == null) { start = startDate; } if (end == null) { end = endDate; } if (!action.StartsWith("{")) { action = "{" + action + "}"; } var visitsDTMbyday = await DTMContext.dtmVisits.Aggregate() .Match(x => x.DATA_HORA_INICI > start && x.DATA_HORA_INICI <= end && (x.CAMPAIGN_ACTION_ID == action.ToUpper() || x.CAMPAIGN_ACTION_ID == action.ToLower())) .Group(r => new { groupedYear = r.DATA_HORA_INICI.Year, groupedMonth = r.DATA_HORA_INICI.Month, groupedDay = r.DATA_HORA_INICI.Day }, g => new { Key = g.Key, total = g.Count() }) .Project(r => new DailyStat() { Day = r.Key.groupedDay, Month = r.Key.groupedMonth, Year = r.Key.groupedYear, Total = r.total }) .SortBy(x => x.Day).SortBy(x => x.Month).SortBy(x => x.Year) .ToListAsync(); if (visitsDTMbyday.Count == 0) { return(new VisitsDay()); } DateTime aux = new DateTime(visitsDTMbyday[0].Year, visitsDTMbyday[0].Month, visitsDTMbyday[0].Day); if (aux < start) { visitsDTMbyday.RemoveAt(0); aux = new DateTime(visitsDTMbyday[0].Year, visitsDTMbyday[0].Month, visitsDTMbyday[0].Day); } List <ProjectionKeyDateValue> visitsDate = NormalizeDayList(visitsDTMbyday, aux); /*List<ProjectionKeyDateValue> visitsDate = new List<ProjectionKeyDateValue>(); * foreach (var item in visitsDTMbyday) * { * DateTime dt = new DateTime(item.Year, item.Month, item.Day); * if (dt == aux) * { * ProjectionKeyDateValue pkdv = new ProjectionKeyDateValue() * { * Data = dt, * Count = item.Total * }; * visitsDate.Add(pkdv); * aux = dt.AddDays(1); * } * else * { * int sparedays = dt.Subtract(aux).Days; * for (int i = 0; i < sparedays; i++) * { * DateTime newday = aux.AddDays(1); * aux = newday; * if (newday == dt) * { * ProjectionKeyDateValue pkdv = new ProjectionKeyDateValue() * { * Data = dt, * Count = item.Total * }; * visitsDate.Add(pkdv); * } * else * { * ProjectionKeyDateValue pkdv = new ProjectionKeyDateValue() * { * Data = newday, * Count = 0 * }; * visitsDate.Add(pkdv); * } * } * } * * }*/ VisitsDay pd = new VisitsDay() { Total = visitsDate.Sum(x => x.Count), visitsDay = visitsDate.OrderBy(x => x.Data) }; return(pd); }