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)
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)
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))
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"))
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
def find_album(album_item):
"""
Находит альбом в базе данных по заданному альбому
"""
session = connect_db()
return session.query(Album).filter(
func.upper(Album.album) == func.upper(album_item)).all()
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()
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()
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
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.')
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)
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 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
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
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 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)
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))
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']]
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)
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
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
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
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 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))
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))
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
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))
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)
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
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()
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('商品型号最少长度为３个字符。')
# 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]
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)
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 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 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 __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 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 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
def __eq__(self, other):
return column('foo') == func.upper(other)
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)
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)
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)
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)
def getCurrentPlayer(username): return Player.query.filter(func.upper(Player.username) == username.upper()).first()
def usernameAlreadyTaken(username): return Player.query.filter(func.upper(Player.username) == username.upper()).first()
def get_data_asset_id_in_list(self, list):
return self.session.query(Test).filter(func.upper(Test.asset_id).in_(list)).all()
