public IEnumerable <DoorStats> GetDoorStatsBy(DoorStatsBy by, DateTimeOffset date) { /* générer une liste de dates en SQL * WITH RECURSIVE dates(date) AS ( * VALUES('2017-11-01') * UNION ALL * SELECT date(date, '+1 day') as dateref * FROM dates * WHERE date < '2017-11-30' * ) * SELECT date FROM dates; * */ //using "datetime(eventopentime, 'localtime') to get num day in paris time rather than UTC //note: 0:38 in paris gives 22h38 in UTC (so the day before ;-)) string sql = ""; Func <object[], IEnumerable <DoorStats> > materializeFun = null; IEnumerable <int> DayOrWeekRange = null; switch (by) { case DoorStatsBy.ByWeek: var week = DateHelper.GetIso8601WeekOfYear(date.Date); sql = string.Format(@"select count(num) openingtimes,strftime('%d',datetime(eventopentime, 'localtime')) as day, strftime('%W', datetime(eventopentime, 'localtime')) as week from doorevent where week='{0:D2}' group by day order by eventopentime", week); materializeFun = MaterializeStats; DayOrWeekRange = DateHelper.WeekDays(date.Year, week); break; case DoorStatsBy.ByMonth: sql = string.Format(@"select count(num) openingtimes,strftime('%d',datetime(eventopentime, 'localtime')) as day, strftime('%m',datetime(eventopentime, 'localtime')) as month, strftime('%Y',datetime(eventopentime, 'localtime')) as year from doorevent where month='{0:D2}' and year='{1}' group by day order by eventopentime", date.Month, date.Year); materializeFun = MaterializeStats; DayOrWeekRange = DateHelper.MonthDays(date); break; case DoorStatsBy.ByYear: var year = date.Year; sql = string.Format(@"select count(num) openingtimes, strftime('%W',datetime(eventopentime, 'localtime')) as week,strftime('%Y',datetime(eventopentime, 'localtime')) as year from doorevent where year='{0}' group by week order by eventopentime", date.Year); materializeFun = MaterializeStats; DayOrWeekRange = DateHelper.YearWeeks(date); break; } //avec le leftouterjoin, on ajoute les jours ou semaine à 0, pour avoir toujours un graph complet return(DayOrWeekRange.Select(x => x.ToString("D2")) .LeftOuterJoin(GenericSelect(sql, materializeFun), x => x, y => y.Label, (l, r) => r != null ? r : new DoorStats { Label = l, OpeningsCount = 0, OpeningTime = 0 } )); }
public IEnumerable <DoorStats> GetDoorStatsBy(DoorStatsBy by, DateTimeOffset date) { return(this._db.GetDoorStatsBy(by, date)); }