def post(self, request, **kwargs):
"""This method will return users results in JSON format
:param request:
:return JSON response
"""
user_to_search = request.POST.get('term', False)
query = User.query.filter(
User.customerId == request.user.customer_id, User.isActive == 1,
functions.concat(User.firstName, ' ',
User.LastName).startswith(user_to_search))
order_by_clause = functions.concat(User.firstName, ' ', User.LastName)
query = query.order_by(order_by_clause).with_entities(
User.userId, User.firstName, User.LastName).limit(10)
users = query.all()
result = []
for user in users:
result.append({
'id': user.userId,
'name': user.firstName + ' ' + user.LastName,
'avatar': '',
'type': 'user'
})
return JsonResponse(dict(users=result))
def retrieve_flights():
airport_origin = aliased(Airport)
airport_destination = aliased(Airport)
flights = db.session.query(Flight.id,functions.concat(airport_origin.city,", ",airport_origin.country," (",airport_origin.airport_code,")").label("origin") \
,functions.concat(airport_destination.city,", ",airport_destination.country," (",airport_destination.airport_code,")").label("destination") \
,Flight.duration).filter(Flight.origin_id==airport_origin.id).filter(Flight.destination_id==airport_destination.id).all()
return flights
def find_nice_twins(self, max_check=1000):
session = mv_model.Session()
open_twins = list(
session.query(mv_model.TwinDeck).filter(
mv_model.TwinDeck.standard_key == None))
random.shuffle(open_twins)
print(f"Analyzing {len(open_twins)} twin decks")
for potential_twin in open_twins[:max_check]:
potential_twin_deck = potential_twin.evil_deck
house_decks = session.query(
mv_model.Deck).filter(mv_model.Deck.expansion == 496)
house_decks = house_decks.filter(
concat(potential_twin_deck.name).like(
concat('%', mv_model.Deck.name, '%')))
for d in house_decks:
if d.name == potential_twin_deck.name: continue
print("found!", potential_twin_deck.name, '-', d.name)
pt_card_names = sorted(
[c.name for c in potential_twin_deck.cards])
t_card_names = sorted([c.name for c in d.cards])
if pt_card_names == t_card_names:
print(" - matching cards")
potential_twin.standard_key = d.key
session.add(potential_twin)
session.commit()
#self.alert(f'Found twin: {potential_twin.standard_key}, {potential_twin.evil_key}', ['discord'])
break
time.sleep(0.5)
def get(self, request, **kwargs):
"""This method will return users results in JSON format
:param request:
:return JSON response
"""
user_to_search = request.GET.get('term', '')
page = int(request.GET.get('page', 1))
page_limit = int(request.GET.get('page_limit', 25))
query = User.query.filter(
User.customerId == request.user.customer_id, User.isActive == 1,
functions.concat(User.firstName, ' ',
User.LastName).startswith(user_to_search))
order_by_clause = functions.concat(User.firstName, ' ', User.LastName)
total_users = query.with_entities(func.count('*')).scalar()
offset = (page - 1) * page_limit
query = query.order_by(order_by_clause).with_entities(
User.userId, User.firstName,
User.LastName).offset(offset).limit(page_limit)
users = query.all()
users = [{
'id': user_find.userId,
'text': user_find.firstName + ' ' + user_find.LastName
} for user_find in users]
return JsonResponse(dict(users=users, total=total_users))
def flight(flight_id):
airport_origin = aliased(Airport)
airport_destination = aliased(Airport)
flight_info = Flight.query.get(flight_id)
flight = db.session.query(Flight.id
,functions.concat(airport_origin.city," (",airport_origin.airport_code,")").label('origin')
,functions.concat(airport_destination.city," (",airport_destination.airport_code,")").label('destination')
,functions.concat(Flight.duration, " minutes").label('duration'))\
.filter(Flight.origin_id==airport_origin.id).filter(Flight.destination_id==airport_destination.id).filter(Flight.id==flight_id).all()
return render_template("flight.html",
flight=flight[0],
passengers=flight_info.passengers)
def records_from_full_name(ukrdc3: Session, names: Iterable[str]):
"""Finds Ids from full name"""
conditions = []
for name in names:
query_term: str = _convert_query_to_pg_like(name).upper()
conditions.append(concat(Name.given, " ", Name.family).like(query_term))
conditions.append(concat(Name.family, " ", Name.given).like(query_term))
conditions.append(Name.given.like(query_term))
conditions.append(Name.family.like(query_term))
return ukrdc3.query(PatientRecord).join(Patient).join(Name).filter(or_(*conditions))
def get_team_briefs(self, current_user_id, domain):
"""Returns summary of live and closed briefs submitted by the user's team."""
team_ids = db.session.query(User.id).filter(
User.id != current_user_id,
User.email_address.endswith(concat('@', domain)))
team_brief_ids = db.session.query(BriefUser.brief_id).filter(
BriefUser.user_id.in_(team_ids))
results = (db.session.query(
Brief.id, Brief.data['title'].astext.label('name'),
Brief.closed_at, Brief.status, Framework.slug.label('framework'),
Lot.slug.label('lot'), User.name.label('author'),
func.count(BriefResponse.id).label('applications')).join(
BriefUser, Framework, Lot, User).filter(
Brief.id.in_(team_brief_ids),
or_(Brief.status == 'live',
Brief.status == 'closed')).outerjoin(
BriefResponse,
Brief.id == BriefResponse.brief_id).group_by(
Brief.id, Framework.slug, Lot.slug,
User.name).order_by(
sql_case([(Brief.status == 'live', 1),
(Brief.status == 'closed', 2)]),
Brief.closed_at.desc().nullslast(),
Brief.id.desc()).all())
return [r._asdict() for r in results]
def get_sequence_descriptions_from_pfam_without_join_table(pfam, with_pdb):
subquery = get_pfam_acc_from_pfam(pfam)
subquery = subquery.distinct().subquery()
query = db.session.query(
concat(Pfamseq.pfamseq_id, '/',
cast(PfamARegFullSignificant.seq_start, types.Unicode), '-',
cast(PfamARegFullSignificant.seq_end, types.Unicode)))
query = query.join(
PfamARegFullSignificant,
Pfamseq.pfamseq_acc == PfamARegFullSignificant.pfamseq_acc)
query = query.filter(
PfamARegFullSignificant.pfamA_acc == subquery.c.pfamA_acc)
if with_pdb:
subquery2 = db.session.query(PdbPfamAReg)
subquery2 = subquery2.filter(PdbPfamAReg.pfamA_acc == subquery.c.
pfamA_acc).distinct().subquery()
query = query.filter(
PfamARegFullSignificant.pfamseq_acc == subquery2.c.pfamseq_acc)
query = query.filter(PfamARegFullSignificant.in_full)
query = query.options(
Load(Pfamseq).load_only('pfamseq_id'),
Load(PfamARegFullSignificant).load_only("seq_start", "seq_end"))
query = query.order_by(Pfamseq.pfamseq_id.asc()).distinct()
results = query.all()
return [r[0] for r in results]
def get_descriptions(self, code, with_pdb):
#icode = "%{:}%".format(code)
subquery = scoped_db.query(PfamA.pfamA_acc)
subquery = subquery.filter(
or_(PfamA.pfamA_acc == code.upper(),
PfamA.pfamA_id.ilike(code))).distinct().subquery()
query = scoped_db.query(
concat(Pfamseq.pfamseq_id, '/',
cast(PfamARegFullSignificant.seq_start, types.Unicode), '-',
cast(PfamARegFullSignificant.seq_end, types.Unicode)))
query = query.outerjoin(
PfamARegFullSignificant,
and_(Pfamseq.pfamseq_acc == PfamARegFullSignificant.pfamseq_acc,
PfamARegFullSignificant.in_full == 1))
query = query.filter(
PfamARegFullSignificant.pfamA_acc == subquery.c.pfamA_acc)
if with_pdb:
subquery2 = scoped_db.query(PdbPfamAReg)
subquery2 = subquery2.filter(PdbPfamAReg.pfamA_acc == subquery.c.
pfamA_acc).distinct().subquery()
query = query.filter(
PfamARegFullSignificant.pfamseq_acc == subquery2.c.pfamseq_acc)
query = query.options(
Load(Pfamseq).load_only('pfamseq_id'),
Load(PfamARegFullSignificant).load_only("seq_start", "seq_end"))
query = query.order_by(Pfamseq.pfamseq_id.asc())
return query.distinct().all()
def get_latest_updates(branch: str = "Stable") -> result:
"""
SELECT CONCAT(devices.name, ' ', devices.region) as name, latest.*
FROM devices,
(
SELECT all_latest.*
FROM (SELECT codename, version, branch, method, size, md5, link, changelog, date
from updates
WHERE updates.branch = "Stable"
AND updates.type = "Full"
ORDER BY updates.date DESC
LIMIT 99999) as all_latest
GROUP BY all_latest.codename, all_latest.method) as latest
WHERE latest.codename = devices.codename
AND devices.miui_code != ""
AND LENGTH(devices.miui_code) = 4
ORDER BY date desc
"""
all_latest = session.query(
Update.codename, Update.version, Update.android, Update.branch,
Update.method, Update.size, Update.md5, Update.link, Update.changelog,
Update.date).filter(Update.branch == branch).filter(
Update.type == "Full").order_by(
Update.date.desc()).limit(99999).subquery()
latest = session.query(all_latest).group_by(
all_latest.c.codename).group_by(all_latest.c.method).subquery()
updates = session.query(
Device.name,
concat(Device.name, ' ', Device.region).label('fullname'),
latest).filter(latest.c.codename == Device.codename).filter(
Device.miui_code != "").filter(
func.length(Device.miui_code) == 4).order_by(
latest.c.date.desc()).all()
return updates
def get_descriptions(self, code, with_pdb):
subquery = scoped_db.query(PfamA)
subquery = subquery.filter(
or_(PfamA.pfamA_acc == code.upper(),
PfamA.pfamA_id.ilike(code))).distinct().subquery()
query1 = scoped_db.query(PfamARegFullSignificant.pfamseq_acc,
PfamARegFullSignificant.seq_start,
PfamARegFullSignificant.seq_end)
query1 = query1.filter(
PfamARegFullSignificant.pfamA_acc == subquery.c.pfamA_acc,
PfamARegFullSignificant.in_full)
query1 = query1.options(
Load(PfamARegFullSignificant).load_only("seq_start", "seq_end"))
query1 = query1.distinct().subquery()
# query2 = scoped_db.query(Pfamseq.pfamseq_id)
# query2 = query2.filter(Pfamseq.pfamA_acc == subquery.c.pfamA_acc).distinct().subquery()
query = scoped_db.query(
concat(Pfamseq.pfamseq_id, '/',
cast(query1.c.seq_start, types.Unicode), '-',
cast(query1.c.seq_end, types.Unicode)))
query = query.filter(Pfamseq.pfamseq_acc == query1.c.pfamseq_acc)
if with_pdb:
subquery2 = scoped_db.query(PdbPfamAReg)
subquery2 = subquery2.filter(PdbPfamAReg.pfamA_acc == subquery.c.
pfamA_acc).distinct().subquery()
query = query.filter(
PfamARegFullSignificant.pfamseq_acc == subquery2.c.pfamseq_acc)
query = query.order_by(Pfamseq.pfamseq_id.asc())
return query.distinct().all()
def get_device_roms(codename) -> result:
"""
SELECT CONCAT(devices.name, ' ', devices.region) as name, all_updates.*
FROM devices,
(
SELECT codename, version, android, branch, method, size, md5, link, changelog, date
from updates
WHERE codename like 'whyred%'
AND (updates.branch like "Stable%" OR updates.branch = "Weekly")
AND updates.type = "Full"
ORDER BY updates.date DESC
LIMIT 99999) as all_updates
WHERE devices.codename = all_updates.codename
AND LENGTH(devices.miui_code) = 4
"""
all_updates = session.query(
Update.codename, Update.version, Update.android, Update.branch,
Update.method, Update.size, Update.md5, Update.link, Update.changelog,
Update.date).filter(Update.codename.startswith(codename)).filter(
or_(Update.branch.startswith("Stable"),
Update.branch == "Weekly")).filter(
Update.type == "Full").order_by(
Update.date.desc()).limit(99999).subquery()
updates = session.query(
concat(Device.name, ' ', Device.region).label('name'),
all_updates).filter(Device.codename == all_updates.c.codename).filter(
func.length(Device.miui_code) == 4).all()
return updates
def venues():
data = []
upcoming_shows = 0
# Get unique cities first to fill the data array in the correct way
city_records = Venue.query.distinct(concat(Venue.city, Venue.state)).all()
for c_record in city_records:
venues_data = []
# Get all the venues in each city
venue_records = Venue.query.filter(Venue.city.like(c_record.city)) \
.filter(Venue.state.like(c_record.state)).all()
for v_record in venue_records:
# Get upcoming show
upcoming_shows = 0
shows = db.session.query(showTable)\
.join(Venue, Venue.id == showTable.c.venue_id)\
.join(Artist, Artist.id == showTable.columns.artist_id)\
.filter(Venue.id == v_record.id).all()
for show in shows:
if str_to_datetime(show.start_time) > datetime.utcnow():
upcoming_shows += 1
# Append each venue data to the venues list
venues_data.append({
'id': v_record.id,
'name': v_record.name,
'num_upcomig_shows': upcoming_shows
})
# Append each city to the data list
data.append({
'city': c_record.city,
'state': c_record.state,
'venues': venues_data
})
return render_template('pages/venues.html', areas=data)
def make_case(query, caseNode):
field_exprs = []
for node in caseNode.findall('field'):
query, expr = make_expr(query, node)
field_exprs.append(expr)
expr = concat(*field_exprs) if len(field_exprs) > 1 else field_exprs[0]
return query, caseNode.attrib.get('value', None), expr
def make_case(query, caseNode):
field_exprs = []
for node in caseNode.findall('field'):
query, expr = make_expr(query, node)
field_exprs.append(expr)
expr = concat(
*field_exprs) if len(field_exprs) > 1 else field_exprs[0]
return query, caseNode.attrib.get('value', None), expr
def getQuarterPeriodList(self, session=None):
dbconnector = DBConnector()
if (session is None):
session = dbconnector.getNewSession()
query = session.query(QuarterPeriod)\
.with_entities(QuarterPeriod.OID, functions.concat(QuarterPeriod.year, '-' ,QuarterPeriod.quarter))\
.order_by(QuarterPeriod.year.desc(), QuarterPeriod.quarter.desc())
objectResult = query.all()
return objectResult
def pseudo_sprintf(self, format, expr):
"""Handle format attribute of fields in data object formatter definitions.
expr - the expression giving the field to be formatted.
format - an sprintf style format string. In Specify 6 this is
given to the java.util.Formatter.format method with the value
of expr as the sole argument. So, it seems the format sting
should have only one substitution directive. Only going to
handle '%s' and '%d' for now.
"""
if '%s' in format:
before, after = format.split('%s')
return concat(before, expr, after)
elif '%d' in format:
before, after = format.split('%d')
return concat(before, expr, after)
else:
return format
def match_gui_query(self, cep_col, upload_col, match_desc, id_suffix):
# noinspection PyCallingNonCallable
session = mysql_session_maker()
return session.query(
self.match_table.c.ID.label("ID_1"),
functions.concat(expression.cast(self.match_table.c.ID, String),
literal_column("'{}'".format(id_suffix))).label("ID_2"),
cep_col.label("CEP_Value"),
upload_col.label("Uploaded_Value"),
literal_column("'{}:'".format(match_desc), String(length=50)).label("Match_Type"))
def pseudo_sprintf(self, format, expr):
"""Handle format attribute of fields in data object formatter definitions.
expr - the expression giving the field to be formatted.
format - an sprintf style format string. In Specify 6 this is
given to the java.util.Formatter.format method with the value
of expr as the sole argument. So, it seems the format sting
should have only one substitution directive. Only going to
handle '%s' and '%d' for now.
"""
if '%s' in format:
before, after = format.split('%s')
return concat(before, expr, after)
elif '%d' in format:
before, after = format.split('%d')
return concat(before, expr, after)
else:
return format
def parse_sqla_operator(raw_key, *operands):
key = raw_key.lower().strip()
if not operands:
raise APIError('Missing arguments for \'%s\'.' % (key))
if key in ['and']:
query = and_(*operands)
return query
elif key in ['or']:
query = or_(*operands)
return query
elif key in ['not']:
x = operands[0]
return parse_condition(x)._not()
else:
if len(operands) != 2:
raise APIError(
'Wrong number of arguments for \'%s\'. Expected: 2 Got: %s' %
(key, len(operands)))
x, y = operands
if key in ['equals', '=']:
return x == y
if key in ['greater', '>']:
return x > y
if key in ['lower', '<']:
return x < y
if key in ['notequal', '<>', '!=']:
return x != y
if key in ['notgreater', '<=']:
return x <= y
if key in ['notlower', '>=']:
return x >= y
if key in ['add', '+']:
return x + y
if key in ['substract', '-']:
return x - y
if key in ['multiply', '*']:
return x * y
if key in ['divide', '/']:
return x / y
if key in ['concatenate', '||']:
return fun.concat(x, y)
if key in ['is not']:
return x.isnot(y)
if key in ['<->']:
return x.distance_centroid(y)
if key in ['getitem']:
if isinstance(y, Slice):
return x[parse_single(y.start, int):parse_single(y.stop, int)]
else:
return x[read_pgid(y)]
if key in ['in']:
return x.in_(y)
raise APIError("Operator '%s' not supported" % key)
def keyword_filter(query, keyword, columns):
keyword = keyword.replace(' ', ' ')
clauses = []
for column in columns:
clause = concat(column).ilike('%{}%'.format(keyword))
clauses.append(clause)
query = query.filter(or_(*clauses))
return query
def keyword_filter(query, keyword, columns):
keyword = keyword.replace(' ', ' ')
clauses = []
for column in columns:
clause = concat(column).ilike('%{}%'.format(keyword))
clauses.append(clause)
query = query.filter(or_(*clauses))
return query
def make_expr(query, fieldNode):
path = fieldNode.text.split('.')
query, table, model, specify_field = build_join(query, specify_model, orm_table, path, join_cache)
if specify_field.is_relationship:
formatter_name = fieldNode.attrib.get('formatter', None)
query, expr = self.objformat(query, table, formatter_name, join_cache)
else:
expr = self._fieldformat(specify_field, getattr(table, specify_field.name))
if 'sep' in fieldNode.attrib:
expr = concat(fieldNode.attrib['sep'], expr)
return query, coalesce(expr, '')
class TreeItemViews(AbstractViews): # type: ignore
"""The admin tree item view."""
_list_fields = [
_list_field("id"),
_list_field("name"),
_list_field("description"),
]
_extra_list_fields_no_parents = [
_list_field(
"metadatas",
renderer=lambda treeitem: ", ".join(
[f"{m.name}: {m.value}" or "" for m in treeitem.metadatas]),
filter_column=concat(Metadata.name, ": ",
Metadata.value).label("metadata"),
)
]
_extra_list_fields = [
_list_field(
"parents_relation",
renderer=lambda layer_wms: ", ".join([
p.treegroup.name or ""
for p in sorted(layer_wms.parents_relation,
key=lambda p: p.treegroup.name or "")
]),
)
] + _extra_list_fields_no_parents
@view_config(route_name="c2cgeoform_item",
request_method="POST",
renderer="../templates/edit.jinja2")
def save(self):
response = super().save()
# correctly handles the validation error as if there is a validation error, cstruct is empty
has_to_be_registered_in_parent = (hasattr(self, "_appstruct")
and self._appstruct is not None
and self._appstruct.get("parent_id"))
if has_to_be_registered_in_parent:
parent = self._request.dbsession.query(TreeGroup).get(
has_to_be_registered_in_parent)
rel = LayergroupTreeitem(parent, self._obj, 100)
self._request.dbsession.add(rel)
return response
def _base_query( # pylint: disable=arguments-differ
self,
query: sqlalchemy.orm.query.Query) -> sqlalchemy.orm.query.Query:
return (query.outerjoin("metadatas").options(
subqueryload("parents_relation").joinedload("treegroup")).options(
subqueryload("metadatas")))
def find_all_with_last_appt_by_query_params_in_store(cls, store_id, **kwargs):
from application.models.appointment import Appointment
relationship_query = db.session.query(func.max(Appointment.id).label('last_id'),
Appointment.customer_id).filter(
and_(Appointment.remark != None, Appointment.remark != u'', Appointment.store_id == store_id)).group_by(
Appointment.customer_id).subquery()
query = db.session.query(cls, Appointment.remark).options(joinedload(cls.sales)).outerjoin(
relationship_query,
cls.id == relationship_query.columns.customer_id).outerjoin(Appointment,
relationship_query.columns.last_id ==
Appointment.id).filter(
and_(cls.store_id == store_id, cls.status != 'cancelled', cls.status != 'duplicated'))
# hack to support paginate
query.__class__ = BaseQuery
if kwargs.get('intent_level'):
query = query.filter(cls._intent_level == kwargs.get('intent_level'))
if kwargs.get('intent_car_ids'):
query = query.filter(
functions.concat(',', cls.intent_car_ids).like('%,' + kwargs.get('intent_car_ids') + '%'))
if kwargs.get('last_instore', None):
if kwargs.get('last_instore') == 'none' or kwargs.get('last_instore') == '-1':
query = query.filter(cls.last_reception_date == None)
else:
try:
days = int(kwargs.get('last_instore'))
last_instore_date = date.today() - timedelta(days=days)
query = query.filter(db.func.date(cls.last_reception_date) >= last_instore_date)
except:
pass
if kwargs.get('status'):
query = query.filter(cls.status == kwargs.get('status'))
if kwargs.get('keywords'):
keywords = '%' + kwargs.get('keywords') + '%'
query = query.filter(or_(cls.name.like(keywords), cls.mobile.like(keywords)))
if kwargs.get('sales_id'):
query = query.filter(cls.sales_id == int(kwargs.get('sales_id')))
page = kwargs.get('page', DEFAULT_PAGE_START)
per_page = kwargs.get('per_page', DEFAULT_PAGE_SIZE)
sortable_fields = ('sales_id', 'status', '_intent_level', 'last_reception_date')
query_order_fixed = SortMixin.add_order_query(query, cls, sortable_fields, kwargs)
return query_order_fixed.paginate(page, per_page)
def load_nodes():
session = Session()
rs = session.query(NodeStatus.ip, Node.lat, Node.lon,
concat(Node.country, ", ",
Node.city)).join(Node,
Node.ip == NodeStatus.ip).all()
ip = [x[3] for x in rs]
lat = [x[1] for x in rs]
lng = [x[2] for x in rs]
session.close()
return ip, lat, lng
def get_team_members(self, current_user_id, email_domain):
"""Returns a list of the user's team members."""
team_ids = db.session.query(User.id).filter(
User.id != current_user_id,
User.email_address.endswith(concat('@', email_domain)))
results = (db.session.query(User.name,
User.email_address.label('email')).filter(
User.id != current_user_id,
User.id.in_(team_ids),
User.active.is_(True)).order_by(
func.lower(User.name)))
return [r._asdict() for r in results]
def get_devices() -> result:
"""
SELECT codename, CONCAT(name, ' ', region) as name, miui_name
from devices
WHERE miui_code != ""
AND LENGTH(miui_code) = 4
GROUP BY codename
ORDER BY codename
"""
return session.query(
Device.codename,
concat(Device.name, ' ', Device.region).label('name'),
Device.miui_name).filter(Device.miui_code != "").filter(
func.length(Device.miui_code) == 4).order_by(
Device.codename).all()
def get_all_updates() -> result:
"""
SELECT CONCAT(d.name, ' ', d.region) as name, firmware.codename, version,
android, branch, filename, size, md5, date
from firmware
JOIN devices d on firmware.codename = d.codename
GROUP BY md5
:return: list of firmware results
"""
return session.query(
concat(Device.name, ' ', Device.region).label('name'), Update.codename,
Update.version, Update.android, Update.branch, Update.filename,
Update.size, Update.md5, Update.date).join(
Device,
Update.codename == Device.codename).group_by(Update.md5).all()
def make_expr(query, fieldNode):
path = fieldNode.text.split('.')
query, table, model, specify_field = build_join(
query, specify_model, orm_table, path, join_cache)
if specify_field.is_relationship:
formatter_name = fieldNode.attrib.get('formatter', None)
query, expr = self.objformat(query, table, formatter_name,
join_cache)
else:
expr = self._fieldformat(specify_field,
getattr(table, specify_field.name))
if 'sep' in fieldNode.attrib:
expr = concat(fieldNode.attrib['sep'], expr)
return query, coalesce(expr, '')
def make_expr(query, fieldNode):
path = fieldNode.text.split('.')
query, table, model, specify_field = query.build_join(specify_model, orm_table, path)
if specify_field.is_relationship:
formatter_name = fieldNode.attrib.get('formatter', None)
query, expr = self.objformat(query, table, formatter_name)
else:
expr = self._fieldformat(specify_field, getattr(table, specify_field.name))
if 'format' in fieldNode.attrib:
expr = self.pseudo_sprintf(fieldNode.attrib['format'], expr)
if 'sep' in fieldNode.attrib:
expr = concat(fieldNode.attrib['sep'], expr)
return query, blank_nulls(expr)
class TreeItemViews(AbstractViews):
_list_fields = [
_list_field('id'),
_list_field('name'),
_list_field('metadata_url'),
_list_field('description')
]
_extra_list_fields_no_parents = [
_list_field('metadatas',
renderer=lambda layers_group: ', '.join([
'{}: {}'.format(m.name, m.value) or ''
for m in layers_group.metadatas
]),
filter_column=concat(Metadata.name, ': ',
Metadata.value).label('metadata'))
]
_extra_list_fields = [
_list_field('parents_relation',
renderer=lambda layer_wms: ', '.join([
p.treegroup.name or ''
for p in sorted(layer_wms.parents_relation,
key=lambda p: p.treegroup.name or '')
]))
] + _extra_list_fields_no_parents
@view_config(route_name='c2cgeoform_item',
request_method='POST',
renderer='../templates/edit.jinja2')
def save(self):
response = super().save()
# correctly handles the validation error as if there is a validation error, cstruct is empty
has_to_be_registred_in_parent = (hasattr(self, '_appstruct')
and self._appstruct is not None
and self._appstruct.get('parent_id'))
if has_to_be_registred_in_parent:
parent = self._request.dbsession.query(TreeGroup).get(
has_to_be_registred_in_parent)
rel = LayergroupTreeitem(parent, self._obj, 100)
self._request.dbsession.add(rel)
return response
def _base_query(self, query):
return query. \
outerjoin('metadatas').\
options(subqueryload('parents_relation').joinedload('treegroup')). \
options(subqueryload('metadatas'))
def _build_response_query(self, session, p_id, org_id):
query = session.query(concat('Patient/P', PatientStatus.participantId).label('subject'),
HPO.name.label('awardee'),
Organization.externalId.label('organization'),
Site.googleGroup.label('site'),
PatientStatus.patientStatus.label('patient_status'),
PatientStatus.comment.label('comment'),
PatientStatus.created.label('created'),
PatientStatus.modified.label('modified'),
PatientStatus.user.label('user'),
PatientStatus.authored.label('authored')).\
filter_by(participantId=p_id, organizationId=org_id). \
join(Site, Site.siteId == PatientStatus.siteId). \
join(HPO, HPO.hpoId == Site.hpoId). \
join(Organization, Organization.organizationId == Site.organizationId)
# self.print_query(query)
return query
def find_last_by_location_within_1hour(self, zipcode, country_code):
sub_time = datetime.utcnow() + timedelta(hours=1, minutes=0)
query = self
query = query.filter(
functions.concat(Temperature.date, ' ',
Temperature.time).__ge__('2019-12-03 22:33:00'))
if (country_code):
query = query.filter_by(country_code=country_code)
if (zipcode):
query = query.filter_by(zipcode=zipcode)
return query.order_by(text('date desc')).order_by(
text('time desc')).limit(1).first()
def make_expr(query, fieldNode):
path = fieldNode.text.split('.')
query, table, model, specify_field = query.build_join(
specify_model, orm_table, path)
if specify_field.is_relationship:
formatter_name = fieldNode.attrib.get('formatter', None)
query, expr = self.objformat(query, table, formatter_name)
else:
expr = self._fieldformat(specify_field,
getattr(table, specify_field.name))
if 'format' in fieldNode.attrib:
expr = self.pseudo_sprintf(fieldNode.attrib['format'], expr)
if 'sep' in fieldNode.attrib:
expr = concat(fieldNode.attrib['sep'], expr)
return query, blank_nulls(expr)
def get_team_briefs(self, current_user_id, domain):
"""Returns summary of live and closed briefs submitted by the user's team."""
team_ids = db.session.query(User.id).filter(User.id != current_user_id,
User.email_address.endswith(concat('@', domain)))
team_brief_ids = db.session.query(BriefUser.brief_id).filter(BriefUser.user_id.in_(team_ids))
results = (db.session.query(Brief.id, Brief.data['title'].astext.label('name'), Brief.closed_at, Brief.status,
Framework.slug.label('framework'), Lot.slug.label('lot'), User.name.label('author'),
func.count(BriefResponse.id).label('applications'))
.join(BriefUser, Framework, Lot, User)
.filter(Brief.id.in_(team_brief_ids), or_(Brief.status == 'live', Brief.status == 'closed'))
.outerjoin(BriefResponse, Brief.id == BriefResponse.brief_id)
.group_by(Brief.id, Framework.slug, Lot.slug, User.name)
.order_by(sql_case([
(Brief.status == 'live', 1),
(Brief.status == 'closed', 2)]), Brief.closed_at.desc().nullslast(), Brief.id.desc())
.all())
return [r._asdict() for r in results]
def build_seach_equipment_query(items_model, search_value):
# Probably it is better to make filters like in Django: date_analyse__icontains="value"
return db.session\
.query(items_model)\
.outerjoin(items_model.test_reason)\
.outerjoin(items_model.test_type)\
.outerjoin(items_model.test_status)\
.outerjoin(items_model.equipment)\
.outerjoin(items_model.campaign)\
.outerjoin(Campaign.created_by)\
.filter(or_(
cast(TestResult.date_analyse, String).ilike("%{}%".format(search_value)),
cast(TestResult.id, String).ilike("%{}%".format(search_value)),
concat(cast(TestResult.id, String),
func.substring(User.name, r'^([a-zA-Z]{1})'),
func.substring(User.name, r'\s([a-zA-Z]){1}'))
.ilike("%{}%".format(search_value)),
TestReason.name.ilike("%{}%".format(search_value)),
TestType.name.ilike("%{}%".format(search_value)),
TestStatus.name.ilike("%{}%".format(search_value)),
Equipment.serial.ilike("%{}%".format(search_value)),
Equipment.equipment_number.ilike("%{}%".format(search_value)),
))\
.all()
def title_content(cls):
# return literal_column('title || " " || content')
return concat(cls.title, cls.content)
from c2cgeoportal_admin.views.treeitems import TreeItemViews
_list_field = partial(ListField, Theme)
base_schema = GeoFormSchemaNode(Theme)
base_schema.add(children_schema_node(only_groups=True))
base_schema.add(
colander.SequenceSchema(
GeoFormManyToManySchemaNode(Functionality),
name='functionalities',
widget=RelationCheckBoxListWidget(
select([
Functionality.id,
concat(Functionality.name, '=', Functionality.value).label('label')
]).alias('functionnality_labels'),
'id',
'label',
order_by='label'
),
validator=manytomany_validator
)
)
base_schema.add(
colander.SequenceSchema(
GeoFormManyToManySchemaNode(Role),
name='restricted_roles',
widget=RelationCheckBoxListWidget(
Role,
