def get_icon_data_cte(cls): cat_alias = db.aliased(cls) cte_query = (select([ cat_alias.id, cat_alias.id.label('source_id'), cat_alias.icon_metadata ]).where(cat_alias.parent_id.is_(None)).cte(recursive=True)) rec_query = (select([ cat_alias.id, db.case({'null': cte_query.c.source_id}, else_=cat_alias.id, value=db.func.json_typeof(cat_alias.icon_metadata)), db.case({'null': cte_query.c.icon_metadata}, else_=cat_alias.icon_metadata, value=db.func.json_typeof(cat_alias.icon_metadata)) ]).where(cat_alias.parent_id == cte_query.c.id)) return cte_query.union_all(rec_query)
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), ReservationOccurrence.start_dt >= start_date, ReservationOccurrence.end_dt <= 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 get_protection_cte(cls): cat_alias = db.aliased(cls) cte_query = (select([cat_alias.id, cat_alias.protection_mode]).where( cat_alias.parent_id.is_(None)).cte(recursive=True)) rec_query = (select([ cat_alias.id, db.case( {ProtectionMode.inheriting.value: cte_query.c.protection_mode}, else_=cat_alias.protection_mode, value=cat_alias.protection_mode) ]).where(cat_alias.parent_id == cte_query.c.id)) return cte_query.union_all(rec_query)
def visibility_horizon_query(self): """Get a query object that returns the highest category this one is visible from.""" cte_query = (select([ Category.id, Category.parent_id, db.case([(Category.visibility.is_(None), None)], else_=(Category.visibility - 1)).label('n'), literal(0).label('level') ]).where(Category.id == self.id).cte('visibility_horizon', recursive=True)) parent_query = (select([ Category.id, Category.parent_id, db.case([ (Category.visibility.is_(None) & cte_query.c.n.is_(None), None) ], else_=db.func.least(Category.visibility, cte_query.c.n) - 1), cte_query.c.level + 1 ]).where( db.and_(Category.id == cte_query.c.parent_id, (cte_query.c.n > 0) | cte_query.c.n.is_(None)))) cte_query = cte_query.union_all(parent_query) return db.session.query(cte_query.c.id, cte_query.c.n).order_by( cte_query.c.level.desc()).limit(1)
def _make_occurrence_date_filter(): _default = rb_settings.get('notification_before_days') _default_weekly = rb_settings.get('notification_before_days_weekly') _default_monthly = rb_settings.get('notification_before_days_monthly') notification_before_days_room = db.case( { RepeatFrequency.WEEK.value: Room.notification_before_days_weekly, RepeatFrequency.MONTH.value: Room.notification_before_days_monthly }, else_=Room.notification_before_days, value=Reservation.repeat_frequency) notification_before_days_default = db.case( { RepeatFrequency.WEEK.value: _default_weekly, RepeatFrequency.MONTH.value: _default_monthly }, else_=_default, value=Reservation.repeat_frequency) notification_before_days = db.func.coalesce( notification_before_days_room, notification_before_days_default) days_until_occurrence = db.cast(ReservationOccurrence.start_dt, db.Date) - date.today() return days_until_occurrence == notification_before_days
def get_track_reviewer_abstract_counts(event, user): """Get the numbers of abstracts per track for a specific user. Note that this does not take into account if the user is a reviewer for a track; it just checks whether the user has reviewed an abstract in a track or not. :return: A dict mapping tracks to dicts containing the counts. """ # COUNT() does not count NULL values so we pass NULL in case an # abstract is not in the submitted state. That way we still get # the track - filtering using WHERE would only include tracks # that have some abstract in the submitted state. count_total = db.func.count(Abstract.id) count_reviewable = db.func.count( db.case({AbstractState.submitted.value: Abstract.id}, value=Abstract.state)) count_reviewable_reviewed = db.func.count( db.case({AbstractState.submitted.value: AbstractReview.id}, value=Abstract.state)) count_total_reviewed = db.func.count(AbstractReview.id) query = (Track.query.with_parent(event).with_entities( Track, count_total, count_total_reviewed, count_reviewable - count_reviewable_reviewed).outerjoin( Track.abstracts_reviewed).outerjoin( AbstractReview, db.and_(AbstractReview.abstract_id == Abstract.id, AbstractReview.user_id == user.id)).group_by(Track.id)) return { track: { 'total': total, 'reviewed': reviewed, 'unreviewed': unreviewed } for track, total, reviewed, unreviewed in query }
def get_protection_parent_cte(self): cte_query = (select([ Category.id, db.cast(literal(None), db.Integer).label('protection_parent') ]).where(Category.id == self.id).cte(recursive=True)) rec_query = (select([ Category.id, db.case( { ProtectionMode.inheriting.value: func.coalesce(cte_query.c.protection_parent, self.id) }, else_=Category.id, value=Category.protection_mode) ]).where(Category.parent_id == cte_query.c.id)) return cte_query.union_all(rec_query)
def duration(cls): from fossir.modules.events.contributions import Contribution from fossir.modules.events.sessions.models.blocks import SessionBlock from fossir.modules.events.timetable.models.breaks import Break return db.case( { TimetableEntryType.SESSION_BLOCK.value: db.select([SessionBlock.duration]).where( SessionBlock.id == cls.session_block_id).correlate_except( SessionBlock).as_scalar(), TimetableEntryType.CONTRIBUTION.value: db.select([Contribution.duration]).where( Contribution.id == cls.contribution_id).correlate_except( Contribution).as_scalar(), TimetableEntryType.BREAK.value: db.select([Break.duration]).where(Break.id == cls.break_id). correlate_except(Break).as_scalar(), }, value=cls.type)