def calculate_rooms_booked_time(rooms, start_date=None, end_date=None):
if end_date is None:
end_date = date.today() - relativedelta(days=1)
if start_date is None:
start_date = end_date - relativedelta(days=29)
# Reservations on working days
reservations = Reservation.find(
Reservation.room_id.in_(r.id for r in rooms),
db.extract('dow', ReservationOccurrence.start_dt).between(1, 5),
db.cast(ReservationOccurrence.start_dt, db.Date) >= start_date,
db.cast(ReservationOccurrence.end_dt, db.Date) <= end_date,
ReservationOccurrence.is_valid,
_join=ReservationOccurrence)
rsv_start = db.cast(ReservationOccurrence.start_dt, db.TIME)
rsv_end = db.cast(ReservationOccurrence.end_dt, db.TIME)
slots = ((db.cast(start, db.TIME), db.cast(end, db.TIME))
for start, end in Location.working_time_periods)
# this basically handles all possible ways an occurrence overlaps with each one of the working time slots
overlaps = sum(
db.case([((rsv_start < start) & (rsv_end > end),
db.extract('epoch', end - start)),
((rsv_start < start) & (rsv_end > start) & (rsv_end <= end),
db.extract('epoch', rsv_end - start)),
((rsv_start >= start) & (rsv_start < end) & (rsv_end > end),
db.extract('epoch', end - rsv_start)),
((rsv_start >= start) & (rsv_end <= end),
db.extract('epoch', rsv_end - rsv_start))],
else_=0) for start, end in slots)
return reservations.with_entities(db.func.sum(overlaps)).scalar() or 0
def calculate_rooms_booked_time(rooms, start_date=None, end_date=None):
if end_date is None:
end_date = date.today() - relativedelta(days=1)
if start_date is None:
start_date = end_date - relativedelta(days=29)
# Reservations on working days
reservations = Reservation.find(Reservation.room_id.in_(r.id for r in rooms),
db.extract('dow', ReservationOccurrence.start_dt).between(1, 5),
db.cast(ReservationOccurrence.start_dt, db.Date) >= start_date,
db.cast(ReservationOccurrence.end_dt, db.Date) <= end_date,
ReservationOccurrence.is_valid,
_join=ReservationOccurrence)
rsv_start = db.cast(ReservationOccurrence.start_dt, db.TIME)
rsv_end = db.cast(ReservationOccurrence.end_dt, db.TIME)
slots = ((db.cast(start, db.TIME), db.cast(end, db.TIME)) for start, end in WORKING_TIME_PERIODS)
# this basically handles all possible ways an occurrence overlaps with each one of the working time slots
overlaps = sum(db.case([
((rsv_start < start) & (rsv_end > end), db.extract('epoch', end - start)),
((rsv_start < start) & (rsv_end > start) & (rsv_end <= end), db.extract('epoch', rsv_end - start)),
((rsv_start >= start) & (rsv_start < end) & (rsv_end > end), db.extract('epoch', end - rsv_start)),
((rsv_start >= start) & (rsv_end <= end), db.extract('epoch', rsv_end - rsv_start))
], else_=0) for start, end in slots)
return reservations.with_entities(db.func.sum(overlaps)).scalar() or 0
def get_events_by_year(category_id=None):
"""Get the number of events for each year.
:param category_id: The category ID to get statistics for. Events
from subcategories are also included.
:return: A dictionary mapping years to event counts.
"""
category_filter = Event.category_chain_overlaps(
category_id) if category_id else True
query = (db.session.query(
db.cast(db.extract('year', Event.start_dt),
db.Integer).label('year'), db.func.count()).filter(
~Event.is_deleted,
category_filter).order_by('year').group_by('year'))
return dict(query)
def get_events_by_year(category_id=None):
"""Get the number of events for each year.
:param category_id: The category ID to get statistics for. Events
from subcategories are also included.
:return: An `OrderedDict` mapping years to event counts.
"""
category_filter = Event.category_chain_overlaps(category_id) if category_id else True
query = (db.session
.query(db.cast(db.extract('year', Event.start_dt), db.Integer).label('year'),
db.func.count())
.filter(~Event.is_deleted,
category_filter)
.order_by('year')
.group_by('year'))
return OrderedDict(query)
def get_contribs_by_year(category_id=None):
"""Get the number of contributions for each year.
:param category_id: The category ID to get statistics for.
Contributions from subcategories are also
included.
:return: A dictionary mapping years to contribution counts.
"""
category_filter = Event.category_chain_overlaps(
category_id) if category_id else True
query = (db.session.query(
db.cast(db.extract('year',
TimetableEntry.start_dt), db.Integer).label('year'),
db.func.count()).join(TimetableEntry.event).filter(
TimetableEntry.type == TimetableEntryType.CONTRIBUTION,
~Event.is_deleted,
category_filter).order_by('year').group_by('year'))
return dict(query)
def get_contribs_by_year(category_id=None):
"""Get the number of contributions for each year.
:param category_id: The category ID to get statistics for.
Contributions from subcategories are also
included.
:return: An `OrderedDict` mapping years to contribution counts.
"""
category_filter = Event.category_chain_overlaps(category_id) if category_id else True
query = (db.session
.query(db.cast(db.extract('year', TimetableEntry.start_dt), db.Integer).label('year'),
db.func.count())
.join(TimetableEntry.event)
.filter(TimetableEntry.type == TimetableEntryType.CONTRIBUTION,
~Event.is_deleted,
category_filter)
.order_by('year')
.group_by('year'))
return OrderedDict(query)
