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