def test_multi_qualification(self):
        JuniorEngineer, Manager, Engineer = (
            self.classes.JuniorEngineer,
            self.classes.Manager,
            self.classes.Engineer,
        )

        session, m1, e1, e2 = self._fixture_one()

        ealias = aliased(Engineer)
        eq_(session.query(Manager, ealias).all(), [(m1, e1), (m1, e2)])

        eq_(session.query(Manager.name).all(), [("Tom",)])

        eq_(
            session.query(Manager.name, ealias.name).all(),
            [("Tom", "Kurt"), ("Tom", "Ed")],
        )

        eq_(
            session.query(
                func.upper(Manager.name), func.upper(ealias.name)
            ).all(),
            [("TOM", "KURT"), ("TOM", "ED")],
        )

        eq_(
            session.query(Manager).add_entity(ealias).all(),
            [(m1, e1), (m1, e2)],
        )

        eq_(
            session.query(Manager.name).add_column(ealias.name).all(),
            [("Tom", "Kurt"), ("Tom", "Ed")],
        )

    def get_bounded_mandates(self, request):
        query = (
            db.session.query(MpGroupMembership, MpGroup, Person)
            .join(MpGroupMembership.mp_group)
            .join(MpGroupMembership.mandate)
            .join(Mandate.person)
            .filter(Mandate.year == 2012)
            .order_by(func.lower(MpGroupMembership.interval), Person.first_name, Person.last_name)
        )

        if request == "late_start":
            query = query.filter(func.lower(MpGroupMembership.interval) == func.lower(Mandate.interval)).filter(
                func.lower(Mandate.interval) > date(2012, 12, 19)
            )

        elif request == "early_end":
            query = query.filter(func.upper(MpGroupMembership.interval) == func.upper(Mandate.interval)).filter(
                func.upper(Mandate.interval) < date.today()
            )

        else:
            raise RuntimeError("Unknown request %r" % request)

        for (membership, group, person) in query:
            yv = {
                "name": person.name_first_last,
                "group": group.name,
                "start": membership.interval.lower,
                "end": membership.interval.upper,
            }

            yield yv

  def applyFilter(self, txt):
    ''' Show only items that match the filter.
    '''
    # Find all elements that match the filter in either name or description.
    # Stupid SQL wildcard requires me to try four different patterns.
    session = self.session_parent.session
    c = self.model_class
    txt = txt.upper()
    fltr1 = '%s'%txt
    fltr2 = '%s%%'%txt
    fltr3 = '%%%s'%txt
    fltr4 = '%%%s%%'%txt

    n = func.upper(c.Name)
    d = func.upper(c.Description)
    all = session.query(c).filter(or_(n.like(fltr1),
                                      n.like(fltr2),
                                      n.like(fltr3),
                                      n.like(fltr4),
                                      d.like(fltr1),
                                      d.like(fltr2),
                                      d.like(fltr3),
                                      d.like(fltr4))).all()
    # Make items to show these records, without the hierarchy
    self.clear()
    self.detail_items = {}
    for rec in all:
      item = createTreeItem(rec)
      self.detail_items[rec.Id] = item
      self.addTopLevelItem(item)

 def getByContentType(cls,idContentType,lang='en',active=1):
     
     return DBSession.query(cls).join(cls.language).\
         filter( func.upper(  FixLanguage.code) ==  func.upper(lang )  ).\
         filter(cls.active == str(active).decode('utf-8'), cls.id_content_type == str(idContentType ).decode('utf-8'))\
         .union( DBSession.query(cls).join(cls.language).\
         filter( func.upper(  FixLanguage.code) ==  func.upper('en' )  ).\
         filter(cls.active == str(active).decode('utf-8'), cls.id_content_type == str(idContentType ).decode('utf-8'))).first()

    def get_seats(self):
        by_party = dict(
            db.session.query(
                MpGroup.short_name,
                func.count(MpGroupMembership.id),
            )
            .join(MpGroupMembership.mp_group)
            .filter(MpGroup.year == 2016)
            .filter(func.upper(MpGroupMembership.interval) == 'infinity')
            .group_by(MpGroup.short_name)
            .all()
        )

        offset = 0
        rv = []

        for short_name in PARTY_ORDER:
            count = by_party.get(short_name, 0)
            rv.append({
                'party': short_name,
                'count': count,
                'offset': offset,
            })
            offset += count

        return rv

    def get_bounded_mandates(self, request):
        query = (
            db.session.query(
                MpGroupMembership,
                MpGroup,
                Person,
            )
            .join(MpGroupMembership.mp_group)
            .join(MpGroupMembership.mandate)
            .join(Mandate.person)
            .filter(Mandate.year == 2016)
            .order_by(
                func.lower(MpGroupMembership.interval),
                Person.first_name,
                Person.last_name,
            )
        )

        if request == 'late_start':
            query = (
                query
                .filter(func.lower(MpGroupMembership.interval) ==
                        func.lower(Mandate.interval))
                .filter(func.lower(Mandate.interval) > date(2016, 12, 21))
            )

        elif request == 'early_end':
            query = (
                query
                .filter(func.upper(MpGroupMembership.interval) ==
                        func.upper(Mandate.interval)
                )
                .filter(func.upper(Mandate.interval) < date.today())
            )

        else:
            raise RuntimeError("Unknown request %r" % request)

        for (membership, group, person) in query:
            yv = {
                'name': person.name_first_last,
                'group': group.name,
                'start': membership.interval.lower,
                'end': membership.interval.upper,
            }

            yield yv

    def test_query_expr(self):
        query = self.session.query(
            Construct({'a_id': self.a_cls.id,
                       'a_name': func.upper(self.a_cls.name)}),
        )

        s1, s2 = query.order_by(self.a_cls.name.asc()).all()

        self.assertEqual(s1.a_name, 'A1')
        self.assertEqual(s2.a_name, 'A2')

def index():
    # call helper functions load_blog_info() and load_posts() to load info
    # maybe schedule this later, for now we can call it from here
    helpers.load_blog_info()
    helpers.load_posts()

    posts = Post.query.order_by(func.upper(Post.title)).all()

    bloginfo = Blog.query.get(1)

    return render_template("index.html", posts=posts, bloginfo=bloginfo)

    def by_topic(self, query, operand, maybe_negate):
        if self.user_profile.realm.domain == "mit.edu":
            # MIT users expect narrowing to topic "foo" to also show messages to /^foo(.d)*$/
            # (foo, foo.d, foo.d.d, etc)
            m = re.search(r'^(.*?)(?:\.d)*$', operand, re.IGNORECASE)
            if m:
                base_topic = m.group(1)
            else:
                base_topic = operand

            # Additionally, MIT users expect the empty instance and
            # instance "personal" to be the same.
            if base_topic in ('', 'personal', '(instance "")'):
                regex = r'^(|personal|\(instance ""\))(\.d)*$'
            else:
                regex = r'^%s(\.d)*$' % (self._pg_re_escape(base_topic),)

            cond = column("subject").op("~*")(regex)
            return query.where(maybe_negate(cond))

        cond = func.upper(column("subject")) == func.upper(literal(operand))
        return query.where(maybe_negate(cond))

def search_q(nick=None, server_name=None, map_name=None, create_dt=None,
        gametypes=[]):
    session     = DBSession()
    result_type = None
    q           = None

    # player-only searches
    if nick and not server_name and not map_name and not create_dt \
        and len(gametypes) < 1:
        result_type = "player"
        q = session.query(Player)
        if nick:
            q = q.filter(
                    func.upper(Player.stripped_nick).like('%'+nick.upper()+'%')).\
                    filter(Player.player_id > 2).\
                    filter(Player.active_ind == True).\
                    order_by(Player.player_id)

    # server-only searches
    elif server_name and not nick and not map_name and not create_dt and len(gametypes) < 1:
        result_type = "server"
        q = session.query(Server)
        if server_name:
            q = q.filter(func.upper(Server.name).like('%'+server_name.upper()+'%'))\
                .filter(Server.active_ind)\
                .order_by(Server.server_id)

    # map-only searches
    elif map_name and not nick and not server_name and not create_dt \
        and len(gametypes) < 1:
        result_type = "map"
        q = session.query(Map)
        if map_name:
            q = q.filter(func.upper(Map.name).\
                    like('%'+map_name.upper()+'%')).\
                    order_by(Map.map_id)

    # game searches (all else)
    else:
        result_type = "game"
        q = session.query(Game, Server, Map).\
                filter(Game.server_id == Server.server_id).\
                filter(Server.active_ind).\
                filter(Game.map_id == Map.map_id).\
                order_by(Game.game_id.desc())
        if len(gametypes) > 0:
            q = q.filter(Game.game_type_cd.in_(gametypes))
        if nick:
            q = q.filter(func.upper(PlayerGameStat.stripped_nick).\
                    like('%'+nick.upper()+'%')).\
                filter(PlayerGameStat.game_id == Game.game_id)
        if map_name:
            q = q.filter(func.upper(Map.name).\
                    like('%'+map_name.upper()+'%'))
        if server_name:
            q = q.filter(func.upper(Server.name).\
                    like('%'+server_name.upper()+'%'))

    return (result_type, q)

    def guess_model(self):
        from skylines.model import Flight, AircraftModel

        # first try to find the reg number in the database
        if self.registration is not None:
            glider_reg = self.registration

            result = DBSession.query(Flight) \
                .filter(func.upper(Flight.registration) == func.upper(glider_reg)) \
                .order_by(desc(Flight.id)) \
                .first()

            if result and result.model_id:
                return result.model_id

        # try to find another flight with the same logger and use it's aircraft type
        if (self.logger_id is not None
                and self.logger_manufacturer_id is not None):
            logger_id = self.logger_id
            logger_manufacturer_id = self.logger_manufacturer_id

            result = DBSession.query(Flight).outerjoin(IGCFile) \
                .filter(func.upper(IGCFile.logger_manufacturer_id) == func.upper(logger_manufacturer_id)) \
                .filter(func.upper(IGCFile.logger_id) == func.upper(logger_id)) \
                .filter(Flight.model_id == None) \
                .order_by(desc(Flight.id))

            if self.logger_manufacturer_id.startswith('X'):
                result = result.filter(Flight.pilot == self.owner)

            result = result.first()

            if result and result.model_id:
                return result.model_id

        if self.model is not None:
            glider_type = self.model.lower()

            # otherwise, try to guess the glider model by the glider type igc header
            text_fragments = ['%{}%'.format(v) for v in re.sub(r'[^a-z]', ' ', glider_type).split()]
            digit_fragments = ['%{}%'.format(v) for v in re.sub(r'[^0-9]', ' ', glider_type).split()]

            if not text_fragments and not digit_fragments:
                return None

            glider_type_clean = re.sub(r'[^a-z0-9]', '', glider_type)

            result = DBSession \
                .query(AircraftModel) \
                .filter(and_(
                    func.regexp_replace(func.lower(AircraftModel.name), '[^a-z]', ' ').like(func.any(text_fragments)),
                    func.regexp_replace(func.lower(AircraftModel.name), '[^0-9]', ' ').like(func.all(digit_fragments)))) \
                .order_by(func.levenshtein(func.regexp_replace(func.lower(AircraftModel.name), '[^a-z0-9]', ''), glider_type_clean))

            if result.first():
                return result.first().id

        # nothing found
        return None

def people():
    """A person generator.

    Returns an iterator of person dictionaries.
    """
    sql = select([persons.c.MIT_ID, persons.c.KRB_NAME_UPPERCASE,
                  persons.c.FIRST_NAME, persons.c.MIDDLE_NAME,
                  persons.c.LAST_NAME, persons.c.EMAIL_ADDRESS,
                  persons.c.ORIGINAL_HIRE_DATE, dlcs.c.DLC_NAME,
                  persons.c.PERSONNEL_SUBAREA_CODE, orcids.c.ORCID]) \
        .select_from(persons.outerjoin(orcids).join(dlcs)) \
        .where(persons.c.EMAIL_ADDRESS != None) \
        .where(persons.c.LAST_NAME != None) \
        .where(persons.c.KRB_NAME_UPPERCASE != None) \
        .where(persons.c.KRB_NAME_UPPERCASE != 'UNKNOWN') \
        .where(persons.c.MIT_ID != None) \
        .where(persons.c.APPOINTMENT_END_DATE >= datetime(2009, 1, 1)) \
        .where(func.upper(dlcs.c.ORG_HIER_SCHOOL_AREA_NAME).in_(AREAS)) \
        .where(persons.c.PERSONNEL_SUBAREA_CODE.in_(PS_CODES)) \
        .where(func.upper(persons.c.JOB_TITLE).in_(TITLES))
    with closing(engine().connect()) as conn:
        for row in conn.execute(sql):
            yield dict(zip(row.keys(), row))

def get_player_id(player_name):
    player_name = player_name.upper().strip()

    if player_name in Playermap:
        return Playermap[player_name]

    p = Classes.players
    pn = Classes.players_names

    query = session.query(p).outerjoin(pn, p.player_id == pn.player_id)
    orclause = or_ \
        ( \
            func.upper(p.player_name).like('%' + player_name + '%'), \
            func.upper(pn.player_name).like('%' + player_name + '%') \
        )
    res = query.filter(orclause).distinct(p.player_id)
    
    if res.count() == 0:
        return None

    player_id = res[0].player_id
    Playermap[player_name] = player_id
    return player_id

    def by_topic(self, query, operand, maybe_negate):
        # type: (Query, str, ConditionTransform) -> Query
        if self.user_profile.realm.is_zephyr_mirror_realm:
            # MIT users expect narrowing to topic "foo" to also show messages to /^foo(.d)*$/
            # (foo, foo.d, foo.d.d, etc)
            m = re.search(r"^(.*?)(?:\.d)*$", operand, re.IGNORECASE)
            if m:
                base_topic = m.group(1)
            else:
                base_topic = operand

            # Additionally, MIT users expect the empty instance and
            # instance "personal" to be the same.
            if base_topic in ("", "personal", '(instance "")'):
                regex = r'^(|personal|\(instance ""\))(\.d)*$'
            else:
                regex = r"^%s(\.d)*$" % (self._pg_re_escape(base_topic),)

            cond = column("subject").op("~*")(regex)
            return query.where(maybe_negate(cond))

        cond = func.upper(column("subject")) == func.upper(literal(operand))
        return query.where(maybe_negate(cond))

    def guess_registration(self):
        from skylines.model.flight import Flight

        # try to find another flight with the same logger and use it's aircraft registration
        if self.logger_id is not None \
            and self.logger_manufacturer_id is not None:
            logger_id = self.logger_id
            logger_manufacturer_id = self.logger_manufacturer_id

            result = DBSession.query(Flight).outerjoin(IGCFile) \
                .filter(func.upper(IGCFile.logger_manufacturer_id) == func.upper(logger_manufacturer_id)) \
                .filter(func.upper(IGCFile.logger_id) == func.upper(logger_id)) \
                .filter(Flight.registration != None) \
                .order_by(desc(Flight.id))

            if self.logger_manufacturer_id.startswith('X'):
                result = result.filter(Flight.pilot == self.owner)

            result = result.first()

            if result and result.registration:
                return result.registration

        return None

 def __init__(self, username_or_email):
     
     #logger = logging.getLogger('ForgotPassword.__init__')
     
     self.u = dbs.query(SapnsUser).\
             filter(or_(func.upper(SapnsUser.user_name) == func.upper(username_or_email),
                        func.upper(SapnsUser.email_address) == func.upper(username_or_email),
                        )).\
             first()
             
     if not self.u:
         raise EUserDoesNotExist
         
     # generate a random password
     random.seed()
     s1 = hl.sha1('%6.6d' % random.randint(0, 999999))
     
     self.new_password = ''
     for c in s1.hexdigest()[:random.randint(10, 15)]:
         if random.randint(0, 1):
             self.new_password += c.upper()
             
         else:
             self.new_password += c
     
     self.u.password = self.new_password
     dbs.add(self.u)
     dbs.flush()
     
     self.dst = [(self.u.email_address.encode('utf-8'), self.u.user_name.encode('utf-8'),)]
         
     # e-mail settings
     self.remitente = (config.get('mail.e_mail'), config.get('mail.name'),)
     
     # get e-mail templates
     self.env = Environment(loader=PackageLoader('sapns', 'templates'))

 def getRatingList(self, nicks):
   """
   Returns a rating list of players
   currently in the lobby by nick
   because the client can't link
   JID to nick conveniently.
   """
   ratinglist = {}
   players = db.query(Player.jid, Player.rating).filter(func.upper(Player.jid).in_([ str(JID).upper() for JID in list(nicks) ]))
   for player in players:
       rating = str(player.rating) if player.rating != -1 else ''
       for JID in list(nicks):
           if JID.upper() == player.jid.upper():
               ratinglist[nicks[JID]] = {'name': nicks[JID], 'rating': rating}
               break
   return ratinglist

    def get_seats(self):
        by_party = dict(
            db.session.query(MpGroup.short_name, func.count(MpGroupMembership.id))
            .join(MpGroupMembership.mp_group)
            .filter(func.upper(MpGroupMembership.interval) == "infinity")
            .group_by(MpGroup.short_name)
            .all()
        )

        offset = 0
        rv = []

        for short_name in PARTY_ORDER:
            count = by_party.get(short_name, 0)
            rv.append({"party": short_name, "count": count, "offset": offset})
            offset += count

        return rv

def getNetlocPrefixes(netloc, length=2):
	# print("Netloc prefixes")
	# SELECT DISTINCT(substring(title for {len})) FROM book_items WHERE lower(title) LIKE %s AND src=%s;

	# So... the .distinct() operator on a query apparently cannot take a function
	# as a parameter, because stupid or something. As such, we hack the query we
	# want together as a return entity
	substr_chunk  = func.substring(func.upper(database.WebPages.title), 0, length)
	# distinct_hack = func.distinct(substr_chunk)

	query = database.get_session().query(database.WebPages)                 \
		.filter(database.WebPages.is_text == True)                    \
		.filter(database.WebPages.file == None)                       \
		.filter(database.WebPages.state == 'complete')                \
		.filter(database.WebPages.netloc == netloc)                   \
		.group_by(substr_chunk)                                       \
		.order_by(substr_chunk)                                       \
		.with_entities(substr_chunk, func.min(database.WebPages.id), func.min(database.WebPages.netloc))

	vals = query.all()
	return vals

    def get_payments_with_details(self, payment_type):
        query = (
            db.session
            .query(Payment.name, sql_func.sum(Payment.cost))
            .join(Payment.transaction)
            .filter(Payment.payment_type_id == payment_type.id)
            .group_by(sql_func.upper(Payment.name))
        )
        query = self.filterize_query(query)
        payment_type_name = payment_type.name.upper()

        payments = [
            Money(u'{}: {}'.format(payment_type_name, name), cost)
            for name, cost in query.all()
        ]

        if len(payments) == 0:
            return 0, [], []
        total = sum((Decimal(obj.value) for obj in payments), Decimal('0.00'))
        rows = payments + [Money(payment_type_name + u': SUMA', total)]

        return total, rows, payments