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 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)