def query(self):
     pq = qualstat_getstatdata(column("eval_type") == "f")
     base = alias(pq)
     query = (select([
         func.array_agg(column("queryid")).label("queryids"),
         "qualid",
         cast(column("quals"), JSONB).label('quals'),
         "occurences",
         "execution_count",
         func.array_agg(column("query")).label("queries"),
         "avg_filter",
         "filter_ratio"
     ]).select_from(
         join(base, powa_databases,
              onclause=(
                  powa_databases.c.oid == literal_column("dbid"))))
         .where(powa_databases.c.datname == bindparam("database"))
         .where(column("avg_filter") > 1000)
         .where(column("filter_ratio") > 0.3)
         .group_by(column("qualid"), column("execution_count"),
                   column("occurences"),
                   cast(column("quals"), JSONB),
                  column("avg_filter"), column("filter_ratio"))
         .order_by(column("occurences").desc())
         .limit(200))
     return query

def fetch_legs(db, where):
    legs = db.metadata.tables["leg_modes"]
    legends0 = db.metadata.tables["leg_ends"]
    legends1 = legends0.alias("legends1")
    places0 = db.metadata.tables["places"]
    places1 = places0.alias("places1")

    s = select(
        [   legs.c.id,
            legs.c.time_start,
            legs.c.time_end,
            legs.c.activity,
            legs.c.line_type,
            legs.c.line_name,
            legs.c.km,
            legs.c.trip,
            func.coalesce(places0.c.label, cast(
                places0.c.id, String)).label("startplace"),
            func.coalesce(places1.c.label, cast(
                places1.c.id, String)).label("endplace")],
        where,
        legs.outerjoin(legends0, legs.c.cluster_start == legends0.c.id) \
            .outerjoin(legends1, legs.c.cluster_end == legends1.c.id) \
            .outerjoin(places0, legends0.c.place == places0.c.id) \
            .outerjoin(places1, legends1.c.place == places1.c.id),
        order_by=legs.c.time_start)

    return db.engine.execute(s)

def aspect_range(stmt, parent_uri, nodes):
    """
    Find all image nodes having an aspect ratio between two values

    Query parameters:

        value_min - Aspect ratio (float)
        value_max - Aspect ratio (float)
    """


    value_min = float(nodes.pop(0))
    value_max = float(nodes.pop(0))

    subq = select([node_meta_table.c.md5])
    subq = subq.where(
        cast(node_meta_table.c.metadata.op('->')('aspect_ratio'),
            Numeric(7,3)) >= value_min)
    subq = subq.where(
        cast(node_meta_table.c.metadata.op('->')('aspect_ratio'),
            Numeric(7,3)) <= value_max)

    stmt = stmt.filter(Node.md5.in_(subq))

    return stmt

 def __table_args__(cls):
     return (
         db.Index("ix_reservations_start_dt_date", cast(cls.start_dt, Date)),
         db.Index("ix_reservations_end_dt_date", cast(cls.end_dt, Date)),
         db.Index("ix_reservations_start_dt_time", cast(cls.start_dt, Time)),
         db.Index("ix_reservations_end_dt_time", cast(cls.end_dt, Time)),
         {"schema": "roombooking"},
     )

def _build_notification_before_days_filter(notification_before_days):
    days_until_occurrence = cast(ReservationOccurrence.start_dt, Date) - cast(func.now(), Date)
    notification_before_days = func.coalesce(Room.notification_before_days, notification_before_days)
    if datetime.now().hour >= settings.get('notification_hour', 6):
        # Notify of today and delayed occurrences (happening in N or less days)
        return days_until_occurrence <= notification_before_days
    else:
        # Notify only of delayed occurrences (happening in less than N days)
        return days_until_occurrence < notification_before_days

 def find_incall_id(self, extension_id):
     return (self.session.query(Incall.id)
             .join(Extension,
                   and_(Extension.type == 'incall',
                        cast(Extension.typeval, Integer) == Incall.id))
             .join(Dialaction,
                   and_(Dialaction.category == 'incall',
                        cast(Dialaction.categoryval, Integer) == Incall.id))
             .filter(Extension.id == extension_id)
             .scalar())

def find_all_by_member_id(session, member_id):
    query = (session.query(Callfiltermember.id,
                           sql.cast(Callfiltermember.typeval, Integer).label('member_id'),
                           Callfiltermember.bstype.label('role'))
             .filter(Callfiltermember.type == 'user')
             .filter(Callfiltermember.bstype.in_(['boss', 'secretary']))
             .filter(sql.cast(Callfiltermember.typeval, Integer) == member_id)
             )

    return [FilterMember(id=row.id, member_id=row.member_id, role=row.role)
            for row in query]

 def make_filter(cls, field, ftype, value):
     filter = None
     if ftype == 'IN':
         filter = field.in_([v for v in value if v])
     elif ftype == 'date_gt':
         filter = field >  value
     elif ftype == 'date_gte':
         filter = field >= value
     elif ftype == 'date_gt_now_less':
         qty, granularity = value.split(" ")
         filter = field > func.date_trunc(granularity, func.now() - cast(value, Interval()))
     elif ftype == 'date_lt_now_less':
         qty, granularity = value.split(" ")
         filter = field < func.date_trunc(granularity, func.now() - cast(value, Interval()))
     elif ftype == 'date_x_last_n':
         qty, granularity, count_current_period = value.split(" ")
         filter = (field > func.date_trunc(granularity, func.now() - cast("%s %s" % (qty, granularity), Interval())), field < func.date_trunc(granularity, func.now() - cast('0', Interval())),)
         if count_current_period == 'on':
             filter = filter[0]
     elif ftype == 'date_month_ne':
         filter = extract('month', field) != value
     elif ftype == 'date_month_gt':
         filter = extract('month', field) > value
     elif ftype == 'date_month_lt':
         filter = extract('month', field) < value
     elif ftype == 'date_month_eq':
         filter = extract('month', field) == value
     elif ftype == 'date_hour_ne':
         filter = extract('hour', field) != value
     elif ftype == 'date_hour_gt':
         filter = extract('hour', field) > value
     elif ftype == 'date_hour_lt':
         filter = extract('hour', field) < value
     elif ftype == 'date_hour_eq':
         filter = extract('hour', field) == value
     elif ftype == 'date_lt':
         filter = field <  value
     elif ftype == 'date_lte':
         filter = field <= value
     elif ftype == '=':
         filter = field == value
     elif ftype == '!=':
         filter = field != value
     elif ftype == '>':
         filter = field >  value
     elif ftype == '>=':
         filter = field >= value
     elif ftype == '<':
         filter = field <  value
     elif ftype == '<=':
         filter = field <= value
     elif ftype == 'like':
         filter = field.ilike(value)
     return filter

 def is_in_notification_window(self, exclude_first_day=False):
     from indico.modules.rb import settings as rb_settings
     from indico.modules.rb.models.rooms import Room
     in_the_past = cast(self.start_dt, Date) < cast(func.now(), Date)
     days_until_occurrence = cast(self.start_dt, Date) - cast(func.now(), Date)
     notification_window = func.coalesce(Room.notification_before_days,
                                         rb_settings.get('notification_before_days', 1))
     if exclude_first_day:
         return (days_until_occurrence < notification_window) & ~in_the_past
     else:
         return (days_until_occurrence <= notification_window) & ~in_the_past

 def __getitem__(self, key):
     bind = self.column.table.bind
     values = list(
         bind.execute(
             select(
                 [
                     self.function(cast(key, types.Date)).label("id"),
                     self.label_expression(cast(key, types.Date)).label("label"),
                 ]
             )
         )
     )[0]
     return Member(self, values.id, values.label)

def _export_reservations(hook, limit_per_room, include_rooms, extra_filters=None):
    """Exports reservations.

    :param hook: The HTTPAPIHook instance
    :param limit_per_room: Should the limit/offset be applied per room
    :param include_rooms: Should reservations include room information
    """
    filters = list(extra_filters) if extra_filters else []
    if hook._fromDT and hook._toDT:
        filters.append(cast(Reservation.start_dt, Date) <= hook._toDT.date())
        filters.append(cast(Reservation.end_dt, Date) >= hook._fromDT.date())
        filters.append(cast(Reservation.start_dt, Time) <= hook._toDT.time())
        filters.append(cast(Reservation.end_dt, Time) >= hook._fromDT.time())
    elif hook._toDT:
        filters.append(cast(Reservation.end_dt, Date) <= hook._toDT.date())
        filters.append(cast(Reservation.end_dt, Time) <= hook._toDT.time())
    elif hook._fromDT:
        filters.append(cast(Reservation.start_dt, Date) >= hook._fromDT.date())
        filters.append(cast(Reservation.start_dt, Time) >= hook._fromDT.time())
    filters += _get_reservation_state_filter(hook._queryParams)
    data = ['vc_equipment']
    if hook._occurrences:
        data.append('occurrences')
    order = {
        'start': Reservation.start_dt,
        'end': Reservation.end_dt
    }.get(hook._orderBy, Reservation.start_dt)
    if hook._descending:
        order = order.desc()
    reservations_data = Reservation.get_with_data(*data, filters=filters, limit=hook._limit, offset=hook._offset,
                                                  order=order, limit_per_room=limit_per_room)
    for result in reservations_data:
        yield result['reservation'].room_id, _serializable_reservation(result, include_rooms)

    def search_added(self, year, month=None, day=None, types='*', limit=None):
        ''' Search by added date '''
        date_trunc = 'day' if day else 'month' if month else 'year'
        month, day = month or 1, day or 1

        search_date = date(year, month, day)
        search_for = orm.with_polymorphic(Content, types)
        search_query = self.dbsession.query(search_for)

        filters = sql.and_(
            search_for.filter_published(),
            sql.func.date_trunc(
                date_trunc,
                sql.cast(search_for.added, Date)
            ) == sql.func.date_trunc(
                date_trunc, search_date
            )
        )

        if types != '*':
            ids = polymorphic_ids(search_for, types)
            filters.append(search_for.content_type_id.in_(ids))

        search_query = search_query.filter(filters)
        count = search_query.count()

        search_query = search_query.order_by(search_for.added.desc())

        if limit:
            search_query = search_query.limit(limit)

        return search_result(search_query, count)

    def filter_modified(cls, part, col=None):
        if col is None:
            col = cls.last_update

        return sql.and_(col >= sql.func.date_trunc(part, sql.func.now()),
                        col < sql.func.date_trunc(part, sql.func.now())\
                              + sql.cast('1 %s' % part, Interval))

def tree_stats(request, treedef, tree, parentid):
    tree_table = datamodel.get_table(tree)
    parentid = None if parentid == 'null' else int(parentid)

    node = getattr(models, tree_table.name)
    descendant = aliased(node)
    node_id = getattr(node, node._id)
    descendant_id = getattr(descendant, node._id)
    treedef_col = tree_table.name + "TreeDefID"

    same_tree_p = getattr(descendant, treedef_col) == int(treedef)
    is_descendant_p = sql.and_(
        sql.between(descendant.nodeNumber, node.nodeNumber, node.highestChildNodeNumber),
        same_tree_p)

    target, make_joins = getattr(StatsQuerySpecialization, tree)()
    target_id = getattr(target, target._id)

    direct_count = sql.cast(
        sql.func.sum(sql.case([(sql.and_(target_id != None, descendant_id == node_id), 1)], else_=0)),
        types.Integer)

    all_count = sql.func.count(target_id)

    with models.session_context() as session:
        query = session.query(node_id, direct_count, all_count) \
                            .join(descendant, is_descendant_p) \
                            .filter(node.ParentID == parentid) \
                            .group_by(node_id)

        query = make_joins(request.specify_collection, query, descendant_id)
        results = list(query)

    return HttpResponse(toJson(results), content_type='application/json')

 def __call__(self, column_clause, cuboid=None):
     if cuboid and cuboid.fact_count_column is not None:
         count = func.sum(cuboid.fact_count_column)
         return case([(count == 0, 0)], else_=(
             func.sum(column_clause * cuboid.fact_count_column) /
             cast(count,
                  types.Numeric)))
     return func.avg(column_clause)

 def __init__(self, level, id, label, metadata=None):
     self.level = level
     self.id = id
     self.label = label
     self.label_expression = cast(_literal_as_binds(self.label),
                                  types.Unicode)
     self.id_expr = _literal_as_binds(self.id)
     self.metadata = metadata or MetaData()

 def __init__(self, itemtype, itemcount):
     self.itemtype = itemtype
     self.itemcount = itemcount
     self.query = select(
         [cast(func.sum(Item.itemcount), Integer)],
         and_(Item.roleid == bindparam("roleid", type_=Integer), Item.itemtype == itemtype),
     )
     self.what = intern(":".join([self.__class__.__name__, str(itemtype), str(itemcount)]))

 def col(name, table):
     try:
         return colnamemaps[table][name]
     except KeyError:
         if cast_nulls:
             return sql.cast(sql.null(), types[name]).label(name)
         else:
             return sql.type_coerce(sql.null(), types[name]).label(name)

def allTransactionMonths():
  session = requireSession()
  parts = "year", "month"
  query = session \
      .query(*[cast(date_part(p, M.Transaction.date), INT)
               .label(p) for p in parts]) \
      .order_by(*[asc(p) for p in parts]) \
      .distinct()
  return list(query)

    def filter_effective(cls, today=None, timez='UTC'):
        """Select items for which the effective date has been reached"""
        if not today:
            today = datetime.today()

        return sql.func.coalesce(
            cls.effective,
            sql.cast('-infinity', DateTime(timezone=timez))
        ) <= today