def get_with_data(*args, **kwargs): from indico.modules.rb.models.locations import Location only_active = kwargs.pop('only_active', True) filters = kwargs.pop('filters', None) order = kwargs.pop('order', [ Location.name, Room.building, Room.floor, Room.number, Room.verbose_name ]) if kwargs: raise ValueError('Unexpected kwargs: {}'.format(kwargs)) query = Room.query entities = [Room] if 'equipment' in args: entities.append(static_array.array_agg(EquipmentType.name)) query = query.outerjoin(RoomEquipmentAssociation).outerjoin( EquipmentType) query = (query.with_entities(*entities).outerjoin( Location, Location.id == Room.location_id).group_by(Location.name, Room.id)) if only_active: query = query.filter(~Room.is_deleted) if filters: # pragma: no cover query = query.filter(*filters) if order: # pragma: no cover query = query.order_by(*order) keys = ('room', ) + tuple(args) return (dict(zip(keys, row if args else [row])) for row in query)
def get_with_data(*args, **kwargs): from indico.modules.rb.models.locations import Location only_active = kwargs.pop('only_active', True) filters = kwargs.pop('filters', None) order = kwargs.pop('order', [Location.name, Room.building, Room.floor, Room.number, Room.verbose_name]) if kwargs: raise ValueError('Unexpected kwargs: {}'.format(kwargs)) query = Room.query entities = [Room] if 'equipment' in args: entities.append(static_array.array_agg(EquipmentType.name)) query = query.outerjoin(RoomEquipmentAssociation).outerjoin(EquipmentType) query = (query.with_entities(*entities) .outerjoin(Location, Location.id == Room.location_id) .group_by(Location.name, Room.id)) if only_active: query = query.filter(Room.is_active) if filters: # pragma: no cover query = query.filter(*filters) if order: # pragma: no cover query = query.order_by(*order) keys = ('room',) + tuple(args) return (dict(zip(keys, row if args else [row])) for row in query)
def get_with_data(*args, **kwargs): filters = kwargs.pop('filters', None) limit = kwargs.pop('limit', None) offset = kwargs.pop('offset', 0) order = kwargs.pop('order', Reservation.start_dt) limit_per_room = kwargs.pop('limit_per_room', False) occurs_on = kwargs.pop('occurs_on') if kwargs: raise ValueError('Unexpected kwargs: {}'.format(kwargs)) query = Reservation.query.options(joinedload(Reservation.room)) if filters: query = query.filter(*filters) if occurs_on: query = query.filter( Reservation.id.in_(db.session.query(ReservationOccurrence.reservation_id) .filter(ReservationOccurrence.date.in_(occurs_on), ReservationOccurrence.is_valid)) ) if limit_per_room and (limit or offset): query = limit_groups(query, Reservation, Reservation.room_id, order, limit, offset) query = query.order_by(order, Reservation.created_dt) if not limit_per_room: if limit: query = query.limit(limit) if offset: query = query.offset(offset) result = OrderedDict((r.id, {'reservation': r}) for r in query) if 'vc_equipment' in args: vc_id_subquery = db.session.query(EquipmentType.id) \ .correlate(Reservation) \ .filter_by(name='Video conference') \ .join(RoomEquipmentAssociation) \ .filter(RoomEquipmentAssociation.c.room_id == Reservation.room_id) \ .as_scalar() # noinspection PyTypeChecker vc_equipment_data = dict(db.session.query(Reservation.id, static_array.array_agg(EquipmentType.name)) .join(ReservationEquipmentAssociation, EquipmentType) .filter(Reservation.id.in_(result.iterkeys())) .filter(EquipmentType.parent_id == vc_id_subquery) .group_by(Reservation.id)) for id_, data in result.iteritems(): data['vc_equipment'] = vc_equipment_data.get(id_, ()) if 'occurrences' in args: occurrence_data = OrderedMultiDict(db.session.query(ReservationOccurrence.reservation_id, ReservationOccurrence) .filter(ReservationOccurrence.reservation_id.in_(result.iterkeys())) .order_by(ReservationOccurrence.start_dt)) for id_, data in result.iteritems(): data['occurrences'] = occurrence_data.getlist(id_) return result.values()
def get_with_data(*args, **kwargs): from indico.modules.rb.models.locations import Location only_active = kwargs.pop('only_active', True) filters = kwargs.pop('filters', None) order = kwargs.pop('order', [Location.name, Room.building, Room.floor, Room.number, Room.name]) if kwargs: raise ValueError('Unexpected kwargs: {}'.format(kwargs)) query = Room.query entities = [Room] if 'equipment' in args: entities.append(static_array.array_agg(EquipmentType.name)) query = query.outerjoin(RoomEquipmentAssociation).outerjoin(EquipmentType) if 'vc_equipment' in args or 'non_vc_equipment' in args: vc_id_subquery = db.session.query(EquipmentType.id) \ .correlate(Room) \ .filter_by(name='Video conference') \ .join(RoomEquipmentAssociation) \ .filter(RoomEquipmentAssociation.c.room_id == Room.id) \ .as_scalar() if 'vc_equipment' in args: # noinspection PyTypeChecker entities.append(static_array.array( db.session.query(EquipmentType.name) .join(RoomEquipmentAssociation) .filter( RoomEquipmentAssociation.c.room_id == Room.id, EquipmentType.parent_id == vc_id_subquery ) .order_by(EquipmentType.name) .as_scalar() )) if 'non_vc_equipment' in args: # noinspection PyTypeChecker entities.append(static_array.array( db.session.query(EquipmentType.name) .join(RoomEquipmentAssociation) .filter( RoomEquipmentAssociation.c.room_id == Room.id, (EquipmentType.parent_id == None) | (EquipmentType.parent_id != vc_id_subquery) ) .order_by(EquipmentType.name) .as_scalar() )) query = (query.with_entities(*entities) .outerjoin(Location, Location.id == Room.location_id) .group_by(Location.name, Room.id)) if only_active: query = query.filter(Room.is_active) if filters: # pragma: no cover query = query.filter(*filters) if order: # pragma: no cover query = query.order_by(*order) keys = ('room',) + tuple(args) return (dict(zip(keys, row if args else [row])) for row in query)
def get_with_data(*args, **kwargs): filters = kwargs.pop('filters', None) limit = kwargs.pop('limit', None) offset = kwargs.pop('offset', 0) order = kwargs.pop('order', Reservation.start_dt) limit_per_room = kwargs.pop('limit_per_room', False) occurs_on = kwargs.pop('occurs_on') if kwargs: raise ValueError('Unexpected kwargs: {}'.format(kwargs)) query = Reservation.query.options(joinedload(Reservation.room)) if filters: query = query.filter(*filters) if occurs_on: query = query.filter( Reservation.id.in_( db.session.query( ReservationOccurrence.reservation_id).filter( ReservationOccurrence.date.in_(occurs_on), ReservationOccurrence.is_valid))) if limit_per_room and (limit or offset): query = limit_groups(query, Reservation, Reservation.room_id, order, limit, offset) query = query.order_by(order, Reservation.created_dt) if not limit_per_room: if limit: query = query.limit(limit) if offset: query = query.offset(offset) result = OrderedDict((r.id, {'reservation': r}) for r in query) if 'vc_equipment' in args: vc_id_subquery = db.session.query(EquipmentType.id) \ .correlate(Reservation) \ .filter_by(name='Video conference') \ .join(RoomEquipmentAssociation) \ .filter(RoomEquipmentAssociation.c.room_id == Reservation.room_id) \ .as_scalar() # noinspection PyTypeChecker vc_equipment_data = dict( db.session.query( Reservation.id, static_array.array_agg(EquipmentType.name)).join( ReservationEquipmentAssociation, EquipmentType).filter( Reservation.id.in_(result.iterkeys())).filter( EquipmentType.parent_id == vc_id_subquery).group_by(Reservation.id)) for id_, data in result.iteritems(): data['vc_equipment'] = vc_equipment_data.get(id_, ()) if 'occurrences' in args: occurrence_data = OrderedMultiDict( db.session.query(ReservationOccurrence.reservation_id, ReservationOccurrence).filter( ReservationOccurrence.reservation_id.in_( result.iterkeys())).order_by( ReservationOccurrence.start_dt)) for id_, data in result.iteritems(): data['occurrences'] = occurrence_data.getlist(id_) return result.values()