예제 #1
0
def room_assistance_emails():
    inner = (Request.query.filter(
        Request.type == 'room-assistance',
        Request.state == RequestState.accepted).add_columns(
            func.jsonb_array_elements_text(
                Request.data['occurrences']).label('requested_at')).subquery())

    aliased_event = aliased(Event, name='event')
    requests = (db.session.query(inner, aliased_event).join(
        aliased_event, aliased_event.id == inner.c.event_id).filter(
            aliased_event.own_room_id.isnot(None)).filter(
                db.cast(inner.c.requested_at, db.Date) == db.cast(
                    now_utc(), db.Date)).all())

    requests = [req._asdict() for req in requests]
    template = get_plugin_template_module('emails/room_assistance_emails.html',
                                          requests=requests,
                                          parse_dt=dateutil.parser.parse)
    recipients = RoomAssistancePlugin.settings.get(
        'room_assistance_recipients')
    if recipients:
        email = make_email(from_address=config.NO_REPLY_EMAIL,
                           to_list=recipients,
                           template=template,
                           html=True)
        send_email(email)
예제 #2
0
def _find_requests(from_dt=None, to_dt=None):
    inner = (Request.query
             .filter(Request.type == 'room-assistance',
                     Request.state == RequestState.accepted)
             .add_columns(func.jsonb_array_elements_text(Request.data['occurrences']).label('requested_at'))
             .subquery())

    aliased_event = aliased(Event, name='event')
    query = db.session.query(inner, aliased_event).join(aliased_event, aliased_event.id == inner.c.event_id)
    if from_dt:
        query = query.filter(db.cast(inner.c.requested_at, db.DateTime) >= from_dt)
    if to_dt:
        query = query.filter(db.cast(inner.c.requested_at, db.DateTime) <= to_dt)
    return [req._asdict() for req in query]
예제 #3
0
    def test_aggregate_scalar_over_table_valued(self, metadata, connection):
        test = Table("test", metadata, Column("id", Integer),
                     Column("data", JSONB))
        test.create(connection)

        connection.execute(
            test.insert(),
            [
                {
                    "id": 1,
                    "data": {
                        "key": [23.7, 108.17, 55.98]
                    }
                },
                {
                    "id": 2,
                    "data": {
                        "key": [2.320, 9.55]
                    }
                },
                {
                    "id": 3,
                    "data": {
                        "key": [10.5, 6]
                    }
                },
            ],
        )

        elem = (func.jsonb_array_elements_text(
            test.c.data["key"]).table_valued("value").alias("elem"))

        maxdepth = select(func.max(cast(elem.c.value,
                                        Float))).label("maxdepth")

        stmt = select(test.c.id.label("test_id"),
                      maxdepth).order_by("maxdepth")

        eq_(
            connection.execute(stmt).all(), [(2, 9.55), (3, 10.5),
                                             (1, 108.17)])