def count_percentile(import_id):
birth_days_grouped_by_town = db.session.query(Citizen.town, func.array_agg(Citizen.birth_date)) \
.filter_by(import_id=import_id) \
.group_by(Citizen.town).all()
db.session.close()
if not birth_days_grouped_by_town:
error = json.dumps({'error': 'This import_id does not exist yet'})
return json_response(error, 404)
test_dict = dict(birth_days_grouped_by_town)
response = {'data': []}
for key, value in test_dict.items():
arr = calculate_age_arr(value)
p50 = round(percentile(arr, 50, interpolation='linear'), 2)
p75 = round(percentile(arr, 75, interpolation='linear'), 2)
p99 = round(percentile(arr, 99, interpolation='linear'), 2)
response['data'].append({
'town': f'{key}',
'p50': p50,
'p75': p75,
'p99': p99
})
return json_response(json.dumps(response), 200)
def pdb_atom_names(self):
"""
"""
session = Session()
query = session.query(
chem_comp_fragment_atoms.c.hit,
func.array_agg(chem_comp_fragment_atoms.c.pdb_name))
query = query.filter(chem_comp_fragment_atoms.c.chem_comp_fragment_id
== self.chem_comp_fragment_id)
query = query.group_by(chem_comp_fragment_atoms.c.hit)
return query.all()
def get_unique_answer_weights():
question_answer = db.session.query(Answer.question_id, Answer.answer, Answer.answer_weight) \
.distinct(Answer.answer, Answer.question_id) \
.join(Question) \
.filter((Question.question_type == QuestionType.demographic) | (Question.question_type == QuestionType.university)) \
.add_column(Question.question) \
.add_column(Question.id) \
.from_self(Question.id, Question.question, func.array_agg(Answer.answer), func.array_agg(Answer.answer_weight)) \
.group_by(Question.id, Question.question)
results = question_answer.all()
transformed = []
for result in results:
t = [result[0], result[1]]
weights = [{
"name": k,
"weight": v
} for k, v in zip(result[2], result[3])]
t.append(weights)
transformed.append(t)
return transformed
def windows(session, experiment_id, union=True):
"""Retrieve all windows of an experiment
Overlapping windows are optionally unioned and the properties of the
individual windows aggregated
Args:
session: sqlalchemy database session
experiment_id (int): Database id of an experiment record
union (bool): Whether to union (see postgis' ST_Union) the overlapping
windows
Return:
dict: A feature collection of the windows belonging to that experiment
"""
if union:
sq = session.query(Trainwindow.geom.ST_Union().ST_Dump().geom.label('geom'))\
.filter(Trainwindow.experiment_id==experiment_id).subquery()
q = session.query(sq.c.geom.label('geom'),
func.array_agg(Trainwindow.id).label('ids'),
func.bool_and(Trainwindow.complete).label('all_complete'))\
.filter(Trainwindow.experiment_id==experiment_id)\
.join(sq, sq.c.geom.ST_Intersects(Trainwindow.geom.ST_Centroid()))\
.group_by(sq.c.geom).all()
fc = [{
'geometry': mapping(to_shape(item.geom)),
'properties': {
'ids': item.ids,
'all_complete': item.all_complete
}
} for item in q]
else:
q = session.query(Trainwindow).filter_by(experiment_id=experiment_id)
fc = [{
'geometry': mapping(to_shape(item.geom)),
'properties': {
'ids': [item.id],
'all_complete': item.complete
}
} for item in q]
return {'type': 'FeatureCollection', 'features': fc}
def trial_query(self):
studies = self.studies()
conditions = self.conditions()
interventions = self.interventions()
facilities = self.facilities()
facility_contacts = self.facility_contacts()
links = self.links()
description = self.detailed_descriptions()
keywords = self.keywords()
eligibilities = self.eligibilities()
nct_id = bindparam('nct_id')
cond_query = select([
conditions.c.nct_id,
func.array_agg(distinct(conditions.c.name)).label('conditions')
]).where(conditions.c.nct_id == nct_id).group_by(
conditions.c.nct_id).alias('cond_query')
loc_query = select([
facilities.c.nct_id,
func.array_agg(
distinct(
case([(facilities.c.country
== 'United States', facilities.c.state)],
else_=facilities.c.country))).label('locations')
]).where(facilities.c.nct_id == nct_id).group_by(
facilities.c.nct_id).alias('loc_query')
email_phone_query = select([
facility_contacts.c.nct_id,
func.array_agg(distinct(
facility_contacts.c.phone)).label('contact_phones'),
func.array_agg(distinct(
facility_contacts.c.email)).label('contact_emails')
]).where(
and_(
facility_contacts.c.nct_id == nct_id,
or_(facility_contacts.c.phone.isnot(None),
facility_contacts.c.email.isnot(None)))).group_by(
facility_contacts.c.nct_id).alias('email_phone_query')
link_query = select([
links.c.nct_id,
func.array_agg(distinct(links.c.url)).label('urls')
]).where(and_(links.c.url.isnot(None),
links.c.nct_id == nct_id)).group_by(
links.c.nct_id).alias('link_query')
drug_query = select([
interventions.c.nct_id,
func.array_agg(distinct(interventions.c.name)).label('drug_names')
]).where(
and_(
interventions.c.intervention_type.in_(
['Drug', 'Device', 'Biological']),
interventions.c.nct_id == nct_id)).group_by(
interventions.c.nct_id).alias('drug_query')
return select([
studies.c.nct_id,
studies.c.study_first_posted_date.label('date_trial_added'),
studies.c.brief_title,
studies.c.official_title,
studies.c.overall_status.label('program_status'),
studies.c.phase.label('eligible_cancer_phase'),
eligibilities.c.minimum_age.label('minimum_age'),
eligibilities.c.maximum_age.label('maximum_age'),
eligibilities.c.gender.label('gender_criteria'),
func.trim(
func.regexp_replace(
eligibilities.c.criteria,
# capture from the start of the inclusion section up until the first exclusion section
# Note - the (.*?) is separated from the "inclusion" capture because otherwise the postgres
# engine (9.5) did not handle the non-greedy operator properly
'.*(Inclusion|Major inclusion|INCLUSION|Eligibility Criteria)(.*?)'
'(Exclusion|Major exclusion|EXCLUSION).*',
'\\1\\2')).label('inclusion_criteria'),
func.trim(
func.regexp_replace(
eligibilities.c.criteria,
# capture from the first exclusion section to the end
'.*?((?:Exclusion|Major exclusion|EXCLUSION).*)',
'\\1')).label('exclusion_criteria'),
studies.c.last_update_posted_date.label('last_changed_date'),
loc_query.c.locations,
email_phone_query.c.contact_phones,
email_phone_query.c.contact_emails,
cond_query.c.conditions,
link_query.c.urls,
description.c.description.label('trial_description'),
drug_query.c.drug_names,
]).select_from(
studies.outerjoin(
loc_query, loc_query.c.nct_id == studies.c.nct_id).outerjoin(
email_phone_query,
email_phone_query.c.nct_id == studies.c.nct_id).outerjoin(
link_query,
link_query.c.nct_id == studies.c.nct_id).outerjoin(
eligibilities,
eligibilities.c.nct_id == studies.c.nct_id).
outerjoin(description,
description.c.nct_id == studies.c.nct_id).outerjoin(
drug_query,
drug_query.c.nct_id == studies.c.nct_id).outerjoin(
cond_query,
cond_query.c.nct_id == studies.c.nct_id)).where(
studies.c.nct_id == nct_id)
def user_show_groups_json(user_id, group_filter="all"):
memberships = Membership.q.filter(Membership.user_id == user_id)
if group_filter == "active":
memberships = memberships.filter(
# it is important to use == here, "is" does NOT work
or_(Membership.begins_at == None,
Membership.begins_at <= session.utcnow())).filter(
# it is important to use == here, "is" does NOT work
or_(Membership.ends_at == None,
Membership.ends_at > session.utcnow()))
group = aliased(PropertyGroup)
p_granted = aliased(Property)
p_denied = aliased(Property)
memberships = (memberships.join(group).outerjoin(
p_granted,
and_(p_granted.property_group_id == group.id,
p_granted.granted == True)).add_column(
func.array_agg(distinct(
p_granted.name)).label('granted')).outerjoin(
p_denied,
and_(
p_denied.property_group_id == group.id,
p_denied.granted == False)).add_column(
func.array_agg(distinct(
p_denied.name)).label('denied')).group_by(
Membership.id))
return jsonify(items=[{
'group_name':
membership.group.name,
'begins_at':
datetime_format(membership.begins_at, default=''),
'ends_at':
datetime_format(membership.ends_at, default=''),
'grants':
granted,
'denies':
denied,
'active':
membership.active(),
'actions': [{
'href':
url_for(".edit_membership",
user_id=user_id,
membership_id=membership.id),
'title':
'Bearbeiten',
'icon':
'glyphicon-edit'
}, {
'href':
url_for(".end_membership",
user_id=user_id,
membership_id=membership.id),
'title':
"Beenden",
'icon':
'glyphicon-off'
} if membership.active() else {}],
} for membership, granted, denied in memberships.all()])