def results_by_district(self):
""" Returns the results aggregated by the distict. """
counted = func.coalesce(func.bool_and(BallotResult.counted), False)
yeas = func.sum(BallotResult.yeas)
nays = func.sum(BallotResult.nays)
yeas_percentage = 100 * yeas / (
cast(func.coalesce(func.nullif(yeas + nays, 0), 1), Float)
)
nays_percentage = 100 - yeas_percentage
accepted = case({True: yeas > nays}, counted)
results = self.results.with_entities(
BallotResult.district.label('name'),
counted.label('counted'),
accepted.label('accepted'),
yeas.label('yeas'),
nays.label('nays'),
yeas_percentage.label('yeas_percentage'),
nays_percentage.label('nays_percentage'),
func.sum(BallotResult.empty).label('empty'),
func.sum(BallotResult.invalid).label('invalid'),
func.sum(BallotResult.eligible_voters).label('eligible_voters'),
func.array_agg(BallotResult.entity_id).label('entity_ids')
)
results = results.group_by(BallotResult.district)
results = results.order_by(None).order_by(BallotResult.district)
return results
def standardize_scores(query, grouping_cols):
""" Recalibrate scores to be calibrated against others' answers.
Args:
query (flask_sqlalchemy.BaseQuery):
grouping_cols (list): List of columns to group by
Returns:
"""
query = query.add_columns(
func.sum(Alignment.raw_score).over(
partition_by=[Dimension.name, Respondent.id]).label('raw_sum'),
func.avg(Alignment.binary_score).over(
partition_by=[Dimension.name, Option.question_id]).label('mean'))
std = func.nullif(func.sqrt(column('mean') * (1 - column('mean'))), 0)
query = query.from_self('dimension', 'raw_score', 'binary_score',
'question_id', 'option_id',
((column('binary_score') - column('mean')) /
std).label('adjusted_score'), *grouping_cols)
query = query.from_self(*grouping_cols) \
.add_columns(_sum_case_when('Chaotic vs. Lawful'),
_sum_case_when('Evil vs. Good')) \
.group_by(*grouping_cols)
return query
def invalid_query(self, session, **kw):
return session.query(
func.regexp_replace(Config.key, u'\D', '', u'g').label('id'),
func.nullif(Config.value, u'__gone__').label('target'))\
.filter(Config.key.like(u'__Source_%%__'))\
.filter(
session.query(orm.aliased(Config))
.filter_by(key=func.format(u'__Source_%s__', Config.value)).exists())\
.order_by('id', 'target')
def invalid_query(self, session, **kw):
cte = session.query(Languoid.pk, Languoid.father_pk, Languoid.level)\
.filter(Languoid.father_pk != None).cte(recursive=True) # noqa
parent = orm.aliased(Languoid)
cte = cte.union_all(session.query(cte.c.pk, parent.father_pk, cte.c.level)\
.join(parent, cte.c.father_pk == parent.pk)\
.filter(parent.father_pk != None)) # noqa
return session.query(Languoid)\
.outerjoin(cte, Languoid.pk == cte.c.father_pk)\
.group_by(Language.pk, Languoid.pk)\
.having(or_(
func.coalesce(Languoid.child_family_count, -1) !=
func.count(func.nullif(cte.c.level != LanguoidLevel.family, True)),
func.coalesce(Languoid.child_language_count, -1) !=
func.count(func.nullif(cte.c.level != LanguoidLevel.language, True)),
func.coalesce(Languoid.child_dialect_count, -1) !=
func.count(func.nullif(cte.c.level != LanguoidLevel.dialect, True))))\
.order_by((Languoid.id))
def invalid_query(self, session, **kw):
return session.query(
func.regexp_replace(Config.key, u'\D', '', u'g').label('id'),
func.nullif(Config.value, u'__gone__').label('target'))\
.filter(Config.key.like(u'__Source_%%__'))\
.filter(
session.query(orm.aliased(Config))
.filter_by(key=func.format(u'__Source_%s__', Config.value)).exists())\
.order_by('id', 'target')
def invalid_query(self, session, **kw):
cte = session.query(Languoid.pk, Languoid.father_pk, Languoid.level)\
.filter(Languoid.father_pk != None).cte(recursive=True) # noqa
parent = orm.aliased(Languoid)
cte = cte.union_all(session.query(cte.c.pk, parent.father_pk, cte.c.level)\
.join(parent, cte.c.father_pk == parent.pk)\
.filter(parent.father_pk != None)) # noqa
return session.query(Languoid)\
.outerjoin(cte, Languoid.pk == cte.c.father_pk)\
.group_by(Language.pk, Languoid.pk)\
.having(or_(
func.coalesce(Languoid.child_family_count, -1) !=
func.count(func.nullif(cte.c.level != LanguoidLevel.family, True)),
func.coalesce(Languoid.child_language_count, -1) !=
func.count(func.nullif(cte.c.level != LanguoidLevel.language, True)),
func.coalesce(Languoid.child_dialect_count, -1) !=
func.count(func.nullif(cte.c.level != LanguoidLevel.dialect, True))))\
.order_by((Languoid.id))
def yeas_percentage(self):
# coalesce will pick the first non-null result
# nullif will return null if division by zero
# => when all yeas and nays are zero the yeas percentage is 0%
return 100 * (
self.yeas / (
func.coalesce(
func.nullif(self.yeas + self.nays, 0), 1
)
)
)
def allocated_mandates(self):
""" Number of already allocated mandates/elected candidates. """
election_ids = [election.id for election in self.elections]
session = object_session(self)
mandates = session.query(
func.count(func.nullif(Candidate.elected, False))
)
mandates = mandates.filter(Candidate.election_id.in_(election_ids))
mandates = mandates.first()
return mandates[0] if mandates else 0
def allocated_mandates(self):
""" Number of already allocated mandates/elected candidates. """
results = object_session(self).query(
func.count(
func.nullif(Candidate.elected, False)
)
)
results = results.filter(Candidate.election_id == self.id)
mandates = results.first()
return mandates and mandates[0] or 0
def new(site_id=None):
form = survey_form()
form.site.choices = [(s.id, s.name) for s in site.query.order_by(
cast(func.nullif(func.regexp_replace(site.name, "\\D", "", "g"), ""),
DECIMAL)).all()]
if form.validate_on_submit():
new_survey = survey(site_id=form.site.data,
date=form.date.data,
crew=form.crew.data,
time_in=form.time_in.data,
time_out=form.time_out.data,
surveyed=form.precentage_surveyed.data,
method=form.method.data,
ac1=form.ac1.data,
ac2=form.ac2.data,
ac3=form.ac3.data,
egg1=form.egg1.data,
egg2=form.egg2.data,
egg3=form.egg3.data,
chick02=form.chick02.data,
chick39=form.chick39.data,
chick1017=form.chick1017.data,
fledgling=form.fledgling.data,
ef1=form.ef1.data,
ef2=form.ef2.data,
ef3=form.ef3.data,
ef4=form.ef4.data,
scrape=form.scrape.data,
pveg=form.primary_vegitation.data,
perc_pveg=form.precentage_primary_vegitation.data,
size_type=form.size_type.data,
cwdn1=form.cwdn1.data,
cwdn2=form.cwdn2.data,
cwdn3=form.cwdn3.data,
cwdlog=form.cwdlog.data,
w_temp=form.water_temp.data,
a_temp=form.ambient_temp.data,
perc_dio=form.precentage_disolved_oxygen.data,
sal=form.salinity.data,
sp_condu=form.specific_conductance.data,
condu=form.conductivity.data,
comment=form.comment.data)
new_survey.save()
return redirect(url_for('surveys.view', id=new_survey.id))
if site_id:
form.site.data = int(site_id)
return render_template('surveys/new.jinja2', form=form)
def get_mandate_activity(self, year):
proposal_cte = (
db.session.query(
Mandate.id,
func.count(Proposal.id).label("total"),
func.count(func.nullif(Proposal.status, "inprogress")).label("_inprogress"),
func.count(func.nullif(Proposal.status, "approved")).label("_approved"),
func.count(func.nullif(Proposal.status, "rejected")).label("_rejected"),
)
.join(Sponsorship.mandate)
.join(Sponsorship.proposal)
.filter(Proposal.date >= date(year, 1, 1))
.filter(Proposal.date <= date(year, 12, 31))
.group_by(Mandate.id)
.cte()
)
transcript_cte = (
db.session.query(Mandate.id, func.count(Transcript.id).label("transcripts"))
.join(Mandate.transcripts)
.join(Transcript.chapter)
.filter(TranscriptChapter.date >= date(year, 1, 1))
.filter(TranscriptChapter.date <= date(year, 12, 31))
.group_by(Mandate.id)
.cte()
)
questions_cte = (
db.session.query(
Mandate.id, func.count("*").label("questions"), func.count(func.nullif(Match.score, 0)).label("qlocal")
)
.join(Mandate.asked)
.join(Ask.question)
.filter(Question.date >= date(year, 1, 1))
.filter(Question.date <= date(year, 12, 31))
.join(Ask.match_row)
.group_by(Mandate.id)
.cte()
)
query = (
db.session.query(
Person,
proposal_cte.c.total,
proposal_cte.c._inprogress,
proposal_cte.c._approved,
proposal_cte.c._rejected,
transcript_cte.c.transcripts,
questions_cte.c.questions,
questions_cte.c.qlocal,
)
.join(Mandate.person)
.filter(Mandate.year == 2012)
.outerjoin(proposal_cte, Mandate.id == proposal_cte.c.id)
.outerjoin(transcript_cte, Mandate.id == transcript_cte.c.id)
.outerjoin(questions_cte, Mandate.id == questions_cte.c.id)
.order_by(Person.first_name, Person.last_name, Person.id)
)
for (person, total, _inprogress, _approved, _rejected, transcripts, questions, qlocal) in query:
yield {
"name": person.name_first_last,
"proposals_total": (total or 0),
"proposals_inprogress": (total or 0) - (_inprogress or 0),
"proposals_approved": (total or 0) - (_approved or 0),
"proposals_rejected": (total or 0) - (_rejected or 0),
"transcripts": transcripts or 0,
"questions": questions or 0,
"questions-local": qlocal or 0,
}
def _region(cls, state, city):
return func.coalesce(func.nullif(state, ""), func.nullif(city, ""))
def edit(id):
form = survey_form()
form.site.choices = [(s.id, s.name) for s in site.query.order_by(
cast(func.nullif(func.regexp_replace(site.name, "\\D", "", "g"), ""),
DECIMAL)).all()]
data = survey.query.filter(survey.id == id).first()
if form.validate_on_submit():
data.site_id = form.site.data
data.date = form.date.data
data.crew = form.crew.data
data.time_in = form.time_in.data
data.time_out = form.time_out.data
data.surveyed = form.precentage_surveyed.data
data.method = form.method.data
data.ac1 = form.ac1.data
data.ac2 = form.ac2.data
data.ac3 = form.ac3.data
data.egg1 = form.egg1.data
data.egg2 = form.egg2.data
data.egg3 = form.egg3.data
data.scrape = form.scrape.data
data.chick02 = form.chick02.data
data.chick39 = form.chick39.data
data.chick1017 = form.chick1017.data
data.fledgling = form.fledgling.data
data.ef1 = form.ef1.data
data.ef2 = form.ef2.data
data.ef3 = form.ef3.data
data.ef4 = form.ef4.data
data.pveg = form.primary_vegitation.data
data.perc_pveg = form.precentage_primary_vegitation.data
data.size_type = form.size_type.data
data.cwdn1 = form.cwdn1.data
data.cwdn2 = form.cwdn2.data
data.cwdn3 = form.cwdn3.data
data.cwdlog = form.cwdlog.data
data.w_temp = form.water_temp.data
data.a_temp = form.ambient_temp.data
data.perc_dio = form.precentage_disolved_oxygen.data
data.sal = form.salinity.data
data.sp_condu = form.specific_conductance.data
data.condu = form.conductivity.data
data.comment = form.comment.data
data.save()
return redirect(url_for('surveys.view', id=id))
form.site.data = data.site_id
form.date.data = data.date
form.crew.data = data.crew
form.time_in.data = data.time_in
form.time_out.data = data.time_out
form.precentage_surveyed.data = data.surveyed
form.method.data = data.method
form.ac1.data = data.ac1
form.ac2.data = data.ac2
form.ac3.data = data.ac3
form.egg1.data = data.egg1
form.egg2.data = data.egg2
form.egg3.data = data.egg3
form.chick02.data = data.chick02
form.chick39.data = data.chick39
form.chick1017.data = data.chick1017
form.fledgling.data = data.fledgling
form.ef1.data = data.ef1
form.ef2.data = data.ef2
form.ef3.data = data.ef3
form.ef4.data = data.ef4
form.scrape.data = data.scrape
form.primary_vegitation.data = data.pveg
form.precentage_primary_vegitation.data = data.perc_pveg
form.size_type.data = data.size_type
form.cwdn1.data = data.cwdn1
form.cwdn2.data = data.cwdn2
form.cwdn3.data = data.cwdn3
form.cwdlog.data = data.cwdlog
form.water_temp.data = data.w_temp
form.ambient_temp.data = data.a_temp
form.precentage_disolved_oxygen.data = data.perc_dio
form.salinity.data = data.sal
form.specific_conductance.data = data.sp_condu
form.conductivity.data = data.condu
form.comment.data = data.comment
return render_template('surveys/edit.jinja2', form=form)
