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)
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]
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)])