Example #1
0
 def search_by_name(cls, search_term, party_type=None, query_limit=50):
     _filter_by_name = func.upper(cls.name).contains(func.upper(search_term))
     if party_type:
         return cls.query.filter(
             cls.party_type_code == party_type).filter(_filter_by_name).limit(query_limit)
     else:
         return cls.query.filter(_filter_by_name).limit(query_limit)
Example #2
0
def entity_search_state_orm(entity: Entity, value: str):
    """
    State criteria
    :param entity:    entity to search
    :parameter value: value for criteria
    """
    return func.upper(entity.orm_model.state) == func.upper(value)
Example #3
0
def answered_demo_fields():
    """ get certain field data from answered forms """
    # TODO: make dynamic
    last_demo_answers = Answers.query\
        .filter((func.upper(Answers.henkilotunnus) == func.upper(patpat(person_id=session['person_id'], attr='henkilotunnus')))\
            & (Answers.form_id == 8)\
            & (Answers.field_id.in_([2, 9, 10])))\
        .with_entities(Answers.field_id,\
            func.max(Answers.open_ts).label('last_ts'))\
        .group_by(Answers.field_id)\
        .subquery()

    demo_answers = Answers.query\
        .filter((func.upper(Answers.henkilotunnus) == func.upper(patpat(person_id=session['person_id'], attr='henkilotunnus')))\
            & (Answers.form_id == 8)\
            & (Answers.field_id.in_([2, 9, 10]))\
            & (Answers.field_id == last_demo_answers.c.field_id)\
            & (Answers.open_ts == last_demo_answers.c.last_ts))\
        .order_by(Answers.field_id)\
        .all()

    answer_fields = {
        'field_id': fields.String,
        'field_name': fields.String,
        'choice_name': fields.String,
        'open_ts': fields.DateTime
    }

    # if something is returned, then jsonify to front-end
    return jsonify(answers=marshal(demo_answers, answer_fields))
Example #4
0
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"
        q1 = session.query(Player.player_id, Player.nick, Player.stripped_nick, Player.create_dt, literal_column("0").label("is_alias")).\
                filter(func.upper(Player.stripped_nick).like('%'+nick.upper()+'%')).\
                filter(Player.player_id > 2)
        #filter(Player.active_ind == True).\
        q2 = session.query(PlayerNick.player_id, PlayerNick.nick, PlayerNick.stripped_nick, PlayerNick.create_dt, literal_column("1").label("is_alias")).\
                filter(func.upper(PlayerNick.stripped_nick).like('%'+nick.upper()+'%')).\
                filter(PlayerNick.player_id > 2)
        q = q1.union(q2)
        q = q.order_by(
            case([(func.upper(
                Player.stripped_nick).like(nick.upper() + '%'), "0")],
                 else_="1"))
Example #5
0
def getComplexesForGeneName(genename):
    #kdrew: do error checking
    error = ""
    complexes = []

    #kdrew: tests to see if genename is a valid genename
    #protein = db.session.query(cdb.Protein).filter((func.upper(cdb.Protein.genename) == func.upper(genename))).one()
    genes = db.session.query(cdb.Gene).filter(
        (func.upper(cdb.Gene.genename) == func.upper(genename))).all()

    if len(genes) == 0:
        #kdrew: input genename is not valid, flash message
        error = "Could not find given genename: %s" % genename
        return complexes, error

    for gene in genes:
        try:
            proteins = db.session.query(cdb.Protein).filter(
                (cdb.Protein.gene_id == gene.gene_id)).all()
        except NoResultFound:
            #kdrew: input genename is not valid, flash message
            error = error + "Could not find given genename: %s" % genename

        for protein in proteins:
            try:
                complexes = complexes + protein.complexes
            except NoResultFound:
                continue

    if len(complexes) == 0:
        error = "No complexes found for given genename: %s" % genename

    complexes = list(set(complexes))

    return complexes, error
Example #6
0
def find_album(album_item):
    """
    Находит альбом в базе данных по заданному альбому
    """
    session = connect_db()
    return session.query(Album).filter(
        func.upper(Album.album) == func.upper(album_item)).all()
Example #7
0
def message_tagging(
    config: SlackConversationConfiguration,
    user_id: str,
    user_email: str,
    channel_id: str,
    incident_id: int,
    event: EventEnvelope = None,
    db_session=None,
    slack_client=None,
):
    """Looks for incident tags in incident messages."""
    text = event.event.text
    incident = incident_service.get(db_session=db_session,
                                    incident_id=incident_id)
    tags = tag_service.get_all(db_session=db_session,
                               project_id=incident.project.id).all()
    tag_strings = [t.name.lower() for t in tags if t.discoverable]
    phrases = build_term_vocab(tag_strings)
    matcher = build_phrase_matcher("dispatch-tag", phrases)
    extracted_tags = list(set(extract_terms_from_text(text, matcher)))

    matched_tags = (db_session.query(Tag).filter(
        func.upper(Tag.name).in_([func.upper(t)
                                  for t in extracted_tags])).all())

    incident.tags.extend(matched_tags)
    db_session.commit()
Example #8
0
def find_sessions(search_str):
    """
    Used by the dashboard's search bar and so must work around fuzzy user
    input.
    """
    search_str = search_str.strip().upper()
    try:
        ident = scanid.parse(search_str)
    except:
        # Not a proper ID, try fuzzy search for name match
        query = Session.query.filter(
            func.upper(Session.name).contains(search_str))
    else:
        if ident.session:
            query = Session.query.filter(
                and_(
                    func.upper(Session.name) ==
                    ident.get_full_subjectid_with_timepoint(),
                    Session.num == ident.session))
            if not query.count():
                ident.session = None

        if not ident.session:
            query = Session.query.filter((func.upper(
                Session.name) == ident.get_full_subjectid_with_timepoint()))

    return query.all()
Example #9
0
def state_codes_by_abbr(abbr_):
    try:
        state_codes = State_Codes.query.filter(
            func.upper(State_Codes.Abbreviation) == func.upper(abbr_)).one()
        return jsonify(state_codes.serialize())
    except Exception as e:
        return (str(e)), 500
Example #10
0
 def validate_username(self, username):
     if username.data.upper() != self.original_username.upper():
         user = User.query.filter(
             func.upper(User.username) == func.upper(
                 self.username.data)).first()
         if user is not None:
             raise ValidationError('Please use a different username.')
Example #11
0
def editItem(item_catagory, item_name):
    # In case not logged in user accesses site using the url
    if 'username' not in login_session:
        return redirect(url_for('loginPage'))

    item_result = session.query(Item).filter_by(catagory=item_catagory,
                                                name=item_name).one()

    # Check if correct user is accessing file
    if login_session['email'] != item_result.user_email:
        return render_template('no-permission.html')

    if request.method == 'POST':
        if request.form['name']:
            item_result.name = request.form['name']
        if request.form['description']:
            item_result.description = request.form['description']
        if request.form['catagory']:
            # Add new catagory if the request.form['catagory'] not in database
            if isNotInCatagory(func.upper(request.form['catagory'])):
                addCatagory(func.upper(request.form['catagory']))
            item_result.catagory = func.upper(request.form['catagory'])
        session.add(item_result)
        session.commit()
        return redirect(url_for('showItemsMain'))
    else:
        return render_template('edit-item.html', item=item_result)
Example #12
0
    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")],
        )
Example #13
0
    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
Example #14
0
def generate_markers():
    """
    Creates new StockMarker entries for stocks in StockTransaction's table that
    do not have an existing StockMarker
    """
    try:
        from flaskr.model import StockTransaction, StockMarker
        from sqlalchemy import func
        stock_symbols = db.session.query(
            func.upper(StockTransaction.stock_symbol),
            func.upper(StockMarker.stock_symbol)
        ).outerjoin(StockMarker,
                    func.upper(StockTransaction.stock_symbol) == \
                    func.upper(StockMarker.stock_symbol)) \
            .filter(StockMarker.stock_symbol == None) \
            .distinct()
        db.session.bulk_save_objects([
            StockMarker(stock_symbol=r[0], exists=None) for r in stock_symbols
        ])
        db.session.commit()
    except Exception as e:
        logging.error(e)
        logging.error(traceback.format_exc())
        db.session.rollback()
        raise e
Example #15
0
def existing_venue_orm(name: str, address: str, city: str, state: str):
    """
    Check for existing venue
    :param name:    artist name
    :param address: artist address
    :param city:    artist city
    :param state:   artist state
    :return: existing venue id and name, or None
    """
    venue_id = None
    venue_name = None
    try:
        existing = Venue.query \
            .with_entities(Venue.id, Venue.name) \
            .filter(and_(func.lower(Venue.name) == func.lower(name),
                         func.lower(Venue.city) == func.lower(city),
                         func.lower(Venue.address) == func.lower(address),
                         func.upper(Venue.state) == func.upper(state))) \
            .first()
        if existing is not None:
            venue_id = existing.id
            venue_name = existing.name

    except:
        print_exc_info()

    return venue_id, venue_name
Example #16
0
  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)
Example #17
0
 def mute_cond(muted):
     # type: (Tuple[str, str]) -> Selectable
     stream_cond = column("recipient_id") == recipient_map[
         muted[0].lower()]
     topic_cond = func.upper(column("subject")) == func.upper(
         muted[1])
     return and_(stream_cond, topic_cond)
Example #18
0
    def get_sd_employee(user_id):

        try:
            session = SessionHandler().session_instance()
            sd_employee_count = session.query(SdEmployee).filter(
                SdEmployee.user_id == user_id).count()
            if sd_employee_count > 0:
                sd_employee = session.query(SdEmployee).filter(
                    SdEmployee.user_id == user_id).first()
            else:
                sd_user = session.query(SdUser).filter(
                    SdUser.user_id == user_id).first()
                set_role = session.query(SetRole).filter(
                    SetRole.user_name_real == sd_user.gis_user_real).one()
                sd_employee = session.query(SdEmployee). \
                    join(BsPerson, BsPerson.person_id == SdEmployee.person_id). \
                    filter(func.upper(BsPerson.person_register) == func.upper(set_role.user_register)).first()

            return sd_employee

        except exc.SQLAlchemyError, e:
            QMessageBox.information(
                None, QApplication.translate("LM2", "Database Query Error"),
                QApplication.translate("LM2", "Could not execute: {0}").format(
                    e.message))
Example #19
0
def upgrade():
    def add_col(name):
        op.add_column('midonet_tasks', sa.Column(name, sa.String(length=36)))

    def drop_table(name):
        fkey = {'type_id': 'midonet_tasks_ibfk_1',
                'data_type_id': 'midonet_tasks_ibfk_2'}
        table_name = {'type_id': 'midonet_task_types',
                      'data_type_id': 'midonet_data_types'}

        op.drop_constraint(fkey[name], 'midonet_tasks', type_='foreignkey')
        op.drop_column('midonet_tasks', name)
        op.drop_table(table_name[name])

    [add_col(name) for name in ['data_type', 'type']]

    type_sel = select([task_type.c.name]).\
        where(old_task.c.type_id == task_type.c.id).\
        as_scalar()
    op.execute(old_task.update().values(type=func.upper(type_sel)))

    dt_sel = select([data_type.c.name]).\
        where(old_task.c.data_type_id == data_type.c.id).\
        as_scalar()
    op.execute(old_task.update().values(data_type=func.upper(dt_sel)))

    [drop_table(name) for name in ['type_id', 'data_type_id']]
Example #20
0
def sync_document_terms(db_session=None):
    """Performs term extraction from known documents."""
    documents = get_all(db_session=db_session)

    for doc in documents:
        log.debug(f"Processing document. Name: {doc.name}")
        p = plugin_service.get_active(db_session=db_session,
                                      plugin_type="storage")

        try:
            if "sheet" in doc.resource_type:
                mime_type = "text/csv"
            else:
                mime_type = "text/plain"

            doc_text = p.instance.get(doc.resource_id, mime_type)
            extracted_terms = route_service.get_terms(db_session=db_session,
                                                      text=doc_text)

            matched_terms = (db_session.query(Term).filter(
                func.upper(Term.text).in_(
                    [func.upper(t) for t in extracted_terms])).all())

            log.debug(
                f"Extracted the following terms from {doc.weblink}. Terms: {extracted_terms}"
            )

            if matched_terms:
                doc.terms = matched_terms
                db_session.commit()

        except Exception as e:
            # even if one document fails we don't want them to all fail
            log.exception(e)
Example #21
0
def existing_artist_orm(name: str, city: str, state: str):
    """
    Check for existing artist
    :param name:    artist name
    :param city:    artist city
    :param state:   artist state
    :return: existing artist id and name, or None
    """
    artist_id = None
    artist_name = None
    try:
        existing = Artist.query \
            .with_entities(Artist.id, Artist.name) \
            .filter(and_(func.lower(Artist.name) == func.lower(name),
                         func.lower(Artist.city) == func.lower(city),
                         func.upper(Artist.state) == func.upper(state))) \
            .first()
        if existing is not None:
            artist_id = existing.id
            artist_name = existing.name

    except:
        print_exc_info()

    return artist_id, artist_name
Example #22
0
    def get_apriori_status_for_antenna(self, antenna, at_date='now'):
        """
        Get the "apriori" status of an antenna station (e.g. HH12) at a date.

        The status enum list may be found by module
        cm_partconnect.get_apriori_antenna_status_enum().

        Parameters
        ----------
        ant : str
            Antenna station designator (e.g. HH12, HA330) it is a single string
        at_date : str or int
            Date to look for. Anything intelligible by cm_utils.get_astropytime.

        Returns
        -------
        str
            The apriori antenna status as a string.  Returns None if not in table.

        """
        ant = antenna.upper()
        at_date = cm_utils.get_astropytime(at_date).gps
        cmapa = cm_partconnect.AprioriAntenna
        apa = self.session.query(cmapa).filter(
            or_(and_(func.upper(cmapa.antenna) == ant, cmapa.start_gpstime <= at_date,
                     cmapa.stop_gpstime.is_(None)),
                and_(func.upper(cmapa.antenna) == ant, cmapa.start_gpstime <= at_date,
                     cmapa.stop_gpstime > at_date))).first()
        if apa is not None:
            return apa.status
Example #23
0
    def is_in_database(self, station_name, db_name='geo_location'):
        """
        Check to see if a station_name is in the specified database table.

        Parameters
        ----------
        station_name :  str
            Name of station.
        db_name :  str
            Name of database table

        Returns
        -------
        bool
            True if station_name is present in specified table, False otherwise.

        """
        if db_name == 'geo_location':
            station = self.session.query(geo_location.GeoLocation).filter(
                func.upper(geo_location.GeoLocation.station_name) ==
                station_name.upper())
        elif db_name == 'connections':
            station = self.session.query(cm_partconnect.Connections).filter(
                func.upper(cm_partconnect.Connections.upstream_part) ==
                station_name.upper())
        else:
            raise ValueError('db not found.')
        if station.count() > 0:
            station_present = True
        else:
            station_present = False
        return station_present
Example #24
0
 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()
Example #25
0
def employees_by_company(company_):
    try:
        employees = Stem_Employee_Demographics.query.filter(
            func.upper(Stem_Employee_Demographics.Company) == func.upper(
                company_)).order_by(Stem_Employee_Demographics.Date).all()
        return jsonify([x.serialize() for x in employees])
    except Exception as e:
        return (str(e))
Example #26
0
def majors_by_major(major_):
    try:
        majors = Stem_Major_Demographics.query.filter(
            func.upper(Stem_Major_Demographics.Major) == func.upper(
                major_)).all()
        return jsonify([x.serialize() for x in majors])
    except Exception as e:
        return (str(e))
Example #27
0
def state_codes_by_name(state_name_):
    try:
        state_codes = State_Codes.query.filter(
            func.upper(State_Codes.StateName) == func.upper(
                state_name_)).one()
        return jsonify(state_codes.serialize())
    except Exception as e:
        return (str(e)), 500
Example #28
0
def employees_by_type(type_):
    try:
        employees = Stem_Employee_Demographics.query.filter(
            func.upper(Stem_Employee_Demographics.Type) == func.upper(
                type_)).all()
        return jsonify([x.serialize() for x in employees])
    except Exception as e:
        return (str(e))
Example #29
0
    def resolve_sets(self, info, **kwargs):
        locale = str(get_locale())
        filters = kwargs.get("filters")
        current_locale_translations = aliased(ModelSetTranslation)

        set_query = (db.session.query(ModelSet).join(
            ModelSet.set_translation.of_type(current_locale_translations)).
                     filter(current_locale_translations.locale == locale))

        level_sq = (db.session.query(
            ModelItem.set_id,
            func.max(ModelItem.level).label("level")).group_by(
                ModelItem.set_id).subquery())
        set_query = set_query.join(level_sq,
                                   ModelSet.uuid == level_sq.c.set_id)

        if filters:
            search = filters.search.strip()
            if filters.stats:
                set_query = set_query.join(ModelSetBonus)
                stat_names = set(map(lambda x: Stat(x).name, filters.stats))
                stat_sq = (db.session.query(
                    ModelSetBonus.set_id, ModelSetBonus.stat).group_by(
                        ModelSetBonus.set_id, ModelSetBonus.stat).filter(
                            ModelSetBonus.stat.in_(stat_names),
                            ModelSetBonus.value > 0)).subquery()
                bonus_sq = (db.session.query(
                    ModelSetBonus.set_id,
                    func.count(distinct(
                        stat_sq.c.stat)).label("num_stats_matched"),
                ).join(stat_sq,
                       ModelSetBonus.set_id == stat_sq.c.set_id).group_by(
                           ModelSetBonus.set_id).subquery())
                set_query = set_query.join(
                    bonus_sq, ModelSet.uuid == bonus_sq.c.set_id).filter(
                        bonus_sq.c.num_stats_matched == len(stat_names))
            if filters.max_level != None:
                set_query = set_query.filter(
                    level_sq.c.level <= filters.max_level)
            if filters.search:
                all_translations = aliased(ModelSetTranslation)
                set_query = (set_query.join(
                    ModelSet.set_translation.of_type(all_translations)).join(
                        ModelItem).join(ModelItemTranslation).filter(
                            func.upper(all_translations.name).contains(
                                func.upper(filters.search.strip()))
                            | func.upper(ModelItemTranslation.name).contains(
                                func.upper(filters.search.strip()))).group_by(
                                    ModelSet.uuid,
                                    level_sq.c.level,
                                    current_locale_translations.name,
                                ))

        return set_query.order_by(
            level_sq.c.level.desc(),
            current_locale_translations.name.asc()).all()

        return get_sets(locale, filters)
Example #30
0
def find(artist):
    """
    Находит все альбомы в базе данных по заданному артисту
    """
    session = connect_db()
    albums = session.query(Album).filter(
        func.upper(Album.artist) == func.upper(artist)).order_by(
            Album.year).all()
    return albums
Example #31
0
    def find_similar_org_by_name(cls, name, org_id=None, branch_name=None):
        """Find an Org instance that matches the provided name."""
        query = cls.query.filter(and_(
            func.upper(Org.name) == name.upper(),
            (func.upper(func.coalesce(Org.branch_name, '')) == ((branch_name or '').upper())))
        ).filter(Org.status_code != OrgStatusEnum.INACTIVE.value)

        if org_id:
            query = query.filter(Org.id != org_id)
        return query.all()
Example #32
0
    def searchSku(self, text):
        """ Search sku with the given text. Used only by the `skuinput` widget

        If text is 8-digits number, it will be interpreted first as item id.
        Then we will search for items with text as the exact model_no.
        If there is an exact match, that match will be returned.
        If not found, we will search items with text as starting model_no.
        If still not found, search for items whose model_no or item_name
        contains the given text.

        Note that inactive items will not be returned. """

        ret = set()
        query = self.sess.query(Item).filter(
            and_(Item.itemStatus != ITEM_STATUS.INACTIVE, Item.isSku == True))

        if len(text) < 3:
            raise RPCUserError('商品型号最少长度为3个字符。')

        # find an exact match for model. Could be multiple items
        items = query.filter(func.upper(Item.model) == text.upper()).all()

        # if nothing found yet, match starting string of model
        if not items:
            items = query.filter(
                func.upper(func.substr(Item.model, 1, len(text))) ==
                text.upper()).limit(11).all()

        if not items:
            items = query.filter(
                or_(
                    Item.itemName.op('ilike')('%%%s%%' % text),
                    Item.specification.op('ilike')('%%%s%%' % text),
                    Item.model.op('ilike')('%%%s%%' % text))).limit(11).all()

        if len(items) > 10:
            return []

        if items:
            for i in items:
                ret.add(i)

        if Item.IsItemNumber(text):
            item = self.sess.query(Item).get(text)
            if item:
                ret.add(item)

        return [{
            'itemId': i.itemId,
            'itemName': i.itemName,
            'model': i.model,
            'specification': i.specification,
            'unitId': i.unitId,
            'weight': i.weight
        } for i in ret]
Example #33
0
def reset_password_request():
    if current_user.is_authenticated:
        return redirect(url_for('main.index'))
    form = ResetPasswordRequestForm()
    if form.validate_on_submit():
        user = User.query.filter(func.upper(User.email) == func.upper(form.email.data)).first()
        if user:
            send_password_reset_email(user)
        flash('Check your email for the instructions to reset your password!')
        return redirect(url_for('auth.login'))
    return render_template('auth/reset_password_request.html', title = 'Reset Password', form=form)
Example #34
0
    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
Example #35
0
    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
Example #36
0
    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')
Example #37
0
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)
Example #38
0
    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))
Example #39
0
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)
Example #40
0
    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
Example #41
0
    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))
Example #42
0
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))
Example #43
0
File: run.py Project: dngl0/py-nhl
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
Example #44
0
 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'))
Example #45
0
    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
Example #46
0
 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
Example #47
0
    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
 def __eq__(self, other):
     return column('foo') == func.upper(other)
Example #51
0
 def mute_cond(muted):
     # type: (Tuple[str, str]) -> Selectable
     stream_cond = column("recipient_id") == recipient_map[muted[0].lower()]
     topic_cond = func.upper(column("subject")) == func.upper(muted[1])
     return and_(stream_cond, topic_cond)
Example #52
0
    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)
            # Since the regex has a `.*` in it, this will always match
            assert(m is not None)
            base_topic = m.group(1)

            # Additionally, MIT users expect the empty instance and
            # instance "personal" to be the same.
            if base_topic in ('', 'personal', '(instance "")'):
                cond = or_(
                    func.upper(column("subject")) == func.upper(literal("")),
                    func.upper(column("subject")) == func.upper(literal(".d")),
                    func.upper(column("subject")) == func.upper(literal(".d.d")),
                    func.upper(column("subject")) == func.upper(literal(".d.d.d")),
                    func.upper(column("subject")) == func.upper(literal(".d.d.d.d")),
                    func.upper(column("subject")) == func.upper(literal("personal")),
                    func.upper(column("subject")) == func.upper(literal("personal.d")),
                    func.upper(column("subject")) == func.upper(literal("personal.d.d")),
                    func.upper(column("subject")) == func.upper(literal("personal.d.d.d")),
                    func.upper(column("subject")) == func.upper(literal("personal.d.d.d.d")),
                    func.upper(column("subject")) == func.upper(literal('(instance "")')),
                    func.upper(column("subject")) == func.upper(literal('(instance "").d')),
                    func.upper(column("subject")) == func.upper(literal('(instance "").d.d')),
                    func.upper(column("subject")) == func.upper(literal('(instance "").d.d.d')),
                    func.upper(column("subject")) == func.upper(literal('(instance "").d.d.d.d')),
                )
            else:
                # We limit `.d` counts, since postgres has much better
                # query planning for this than they do for a regular
                # expression (which would sometimes table scan).
                cond = or_(
                    func.upper(column("subject")) == func.upper(literal(base_topic)),
                    func.upper(column("subject")) == func.upper(literal(base_topic + ".d")),
                    func.upper(column("subject")) == func.upper(literal(base_topic + ".d.d")),
                    func.upper(column("subject")) == func.upper(literal(base_topic + ".d.d.d")),
                    func.upper(column("subject")) == func.upper(literal(base_topic + ".d.d.d.d")),
                )
            return query.where(maybe_negate(cond))

        cond = func.upper(column("subject")) == func.upper(literal(operand))
        return query.where(maybe_negate(cond))
 def column_expression(self, col):
     return func.upper(col)
Example #54
0
 def mute_cond(muted):
     stream_cond = column("recipient_id") == recipient_map[muted[0].lower()]
     topic_cond = func.upper(column("subject")) == func.upper(muted[1])
     return and_(stream_cond, topic_cond)
Example #55
0
 def find_by_filename(cls, filename):
     session = Client().session()
     images = session.query(cls).filter(
         func.upper(cls.filename) == func.upper(filename))
     return images.first()
 def __eq__(self, other):
     if other is None:
         return self.expression == None
     else:
         return func.upper(self.expression) == func.upper(other)
Example #57
0
def getCurrentPlayer(username):

	return Player.query.filter(func.upper(Player.username) == username.upper()).first()
Example #58
0
def usernameAlreadyTaken(username):

	return Player.query.filter(func.upper(Player.username) == username.upper()).first()
Example #59
0
 def get_data_asset_id_in_list(self, list):
     return self.session.query(Test).filter(func.upper(Test.asset_id).in_(list)).all()