def party_counts(self): # if the bill cosponsorships have party_ids, then use it. party_counts = db_session.query(func.count(distinct(Cosponsorship.person_id)))\ .join(Cosponsorship.bill)\ .filter(Bill.id == self.id)\ .outerjoin(Cosponsorship.party)\ .add_columns(Party.name, Party.color)\ .group_by(Party.id) # Otherwise, use the most recent party affiliation of candidacy info. if any(party is None for _, party, _ in party_counts): party_counts = db_session.query(Party.name, func.count(distinct(Person.id)), Party.color)\ .join(Candidacy)\ .join(Election)\ .filter(Election.assembly_id == self.assembly_id)\ .join(Person)\ .join(Cosponsorship)\ .join(Bill)\ .filter(Bill.id == self.id)\ .group_by(Party.id) else: party_counts = ((party, count, color) for count, party, color in party_counts) return list(party_counts)
def person_main(): sort = request.args.get('sort', 'name') election_type = request.args.get('election_type', 'assembly') assembly_id = int(request.args.get('assembly_id', current_parliament_id(election_type)) or 0) if sort == 'cosponsorship': bill_t = Bill.__table__ cosponsorship_count = db_session.query( cosponsorship.c.person_id, func.count(cosponsorship.c.id).label('cosponsorship_count') )\ .outerjoin(bill_t)\ .filter(bill_t.c.assembly_id == assembly_id)\ .group_by(cosponsorship.c.person_id)\ .subquery('cosponsorship_count') officials = Person.query.order_by( desc(func.coalesce(func.sum(cosponsorship_count.c.cosponsorship_count), 0)) )\ .join(Candidacy)\ .outerjoin(cosponsorship_count)\ .filter(and_(Candidacy.type == election_type, Candidacy.assembly_id == assembly_id, Candidacy.is_elected == True))\ .group_by(Person.id) else: officials = Person.query.order_by(Person.name)\ .join(Candidacy)\ .filter(and_(Candidacy.type == election_type, Candidacy.assembly_id == assembly_id, Candidacy.is_elected == True)) return render_template('people.html', officials=officials, assembly_id=assembly_id)
def meeting_main(): year = request.args.get('year', date.today().year) date_ = request.args.get('date') # meetings of the day (optional) meetings_of_the_day = None if date_: if not date_re.match(date_): abort(404) date_ = date(*map(int, date_.split('-'))) meetings_of_the_day = Meeting.query.filter_by(date=date_) # meetings of the year meetings_of_the_year =\ db_session.query(Meeting.date)\ .filter(Meeting.year == year)\ .group_by(Meeting.date) meetings_of_the_year = ( { 'date': meeting_date, 'url': url_for('meeting_main', date=format_date(meeting_date, 'yyyy-MM-dd')) } for (meeting_date,) in meetings_of_the_year ) return render_template('meetings.html', year=int(year), date=date_, meetings_of_the_year=meetings_of_the_year, meetings_of_the_day=meetings_of_the_day, )
def all_person_names(): name_tuples = (list(i) for i in db_session.query(Person.name, Person.name_en)) all_names = "" try: all_names = list(set(reduce(operator.add, name_tuples))) except TypeError as e: pass return all_names
def officials_grouped_by_assembly_id(cls, region_id): officials_ = db_session.query(Person.id, Candidacy.assembly_id)\ .filter(Candidacy.person_id == Person.id)\ .filter(Candidacy.district_id.any(region_id))\ .filter(Candidacy.is_elected == True)\ .group_by(Person.id, Candidacy.election_id) res = defaultdict(list) for person_id, assembly_id in officials_: res[assembly_id].append(person_id) return res
def all_person_names(): name_tuples = (list(i) for i in db_session.query( Person.name, Person.name_en, )) all_names = '' try: all_names = list(set(reduce(operator.add, name_tuples))) except TypeError as e: pass return all_names
def meeting_main(): year = request.args.get('year') date_ = request.args.get('date') view_type = request.args.get('type', 'list') if view_type == 'list': return render_template('meetings-list.html') # find the right calendar if not year: if date_: year = format_date(date(*map(int, date_.split('-'))), 'yyyy') else: year = date.today().year if not date_: # find the latest meeting date for the selected year d = latest_meeting_date(year) if d: return redirect( url_for('meeting_main', type='calendar', date=format_date(d, 'yyyy-MM-dd'))) # meetings of the day (optional) meetings_of_the_day = None if date_: if not date_re.match(date_): abort(404) date_ = date(*map(int, date_.split('-'))) meetings_of_the_day = Meeting.query.filter_by(date=date_) # meetings of the year meetings_of_the_year =\ db_session.query(Meeting.date)\ .filter(Meeting.year == year)\ .group_by(Meeting.date) meetings_of_the_year = ({ 'date': meeting_date, 'url': url_for('meeting_main', type='calendar', date=format_date(meeting_date, 'yyyy-MM-dd')) } for (meeting_date, ) in meetings_of_the_year) return render_template( 'meetings-calendar.html', year=int(year), date=date_, meetings_of_the_year=meetings_of_the_year, meetings_of_the_day=meetings_of_the_day, )
def party_counts(self): party_counts = db_session.query(Party.name, func.count(distinct(Person.id)))\ .join(Candidacy)\ .join(Election)\ .filter(Election.assembly_id == self.assembly_id)\ .join(Person)\ .join(cosponsorship)\ .join(Bill)\ .filter(Bill.id == self.id)\ .group_by(Party.id) return [(party, int(count)) for party, count in party_counts]
def meeting_main(): year = request.args.get('year') date_ = request.args.get('date') view_type = request.args.get('type', 'list') if view_type=='list': return render_template('meetings-list.html') # find the right calendar if not year: if date_: year = format_date(date(*map(int, date_.split('-'))), 'yyyy') else: year = date.today().year if not date_: # find the latest meeting date for the selected year d = latest_meeting_date(year) if d: return redirect(url_for('meeting_main', type='calendar', date=format_date(d, 'yyyy-MM-dd'))) # meetings of the day (optional) meetings_of_the_day = None if date_: if not date_re.match(date_): abort(404) date_ = date(*map(int, date_.split('-'))) meetings_of_the_day = Meeting.query.filter_by(date=date_) # meetings of the year meetings_of_the_year =\ db_session.query(Meeting.date)\ .filter(Meeting.year == year)\ .group_by(Meeting.date) meetings_of_the_year = ( { 'date': meeting_date, 'url': url_for('meeting_main', type='calendar', date=format_date(meeting_date, 'yyyy-MM-dd')) } for (meeting_date,) in meetings_of_the_year ) return render_template('meetings-calendar.html', year=int(year), date=date_, meetings_of_the_year=meetings_of_the_year, meetings_of_the_day=meetings_of_the_day, )
def person_main(): sort = request.args.get("sort", "name") election_type = request.args.get("election_type", "assembly") assembly_id = int(request.args.get("assembly_id", current_parliament_id(election_type)) or 0) if sort == "cosponsorship": bill_t = Bill.__table__ cosponsorship_count = ( db_session.query(cosponsorship.c.person_id, func.count(cosponsorship.c.id).label("cosponsorship_count")) .outerjoin(bill_t) .filter(bill_t.c.assembly_id == assembly_id) .group_by(cosponsorship.c.person_id) .subquery("cosponsorship_count") ) officials = ( Person.query.order_by(desc(func.coalesce(func.sum(cosponsorship_count.c.cosponsorship_count), 0))) .join(Candidacy) .outerjoin(cosponsorship_count) .filter( and_( Candidacy.type == election_type, Candidacy.assembly_id == assembly_id, Candidacy.is_elected == True, ) ) .group_by(Person.id) ) else: officials = ( Person.query.order_by(Person.name) .join(Candidacy) .filter( and_( Candidacy.type == election_type, Candidacy.assembly_id == assembly_id, Candidacy.is_elected == True, ) ) ) # party list party_count = defaultdict(int) for official in officials: party_count[official.cur_party] += 1 party_list = [p[0] for p in sorted(party_count.items(), key=lambda x: x[1], reverse=True)] return render_template("people.html", officials=officials, assembly_id=assembly_id, party_list=party_list)
def allies(cls, person, assembly_id=None, threshold=0.5): bills = cls.bills_of(person, assembly_id) sponsored_bills = (bill for bill in bills if person.id in (p.id for p in bill.representative_people)) counter = Counter() num_sponsored_bills = 0 for bill in bills: followers = db_session.query(Person.id)\ .join(cosponsorship)\ .filter(cosponsorship.c.bill_id == bill.id) counter = counter + Counter(follower.id for follower in followers if follower.id != person.id) num_sponsored_bills += 1 return OrderedDict( (key, (value + .0) / num_sponsored_bills) for key, value in counter.most_common(10)\ if (value + .0) / num_sponsored_bills > threshold )
def allies(cls, person, assembly_id=None, threshold=0.5): bills = cls.bills_of(person, assembly_id) sponsored_bills = (bill for bill in bills if person.id in ( p.id for p in bill.representative_people)) counter = Counter() num_sponsored_bills = 0 for bill in bills: followers = db_session.query(Person.id)\ .join(cosponsorship)\ .filter(cosponsorship.c.bill_id == bill.id) counter = counter + Counter(follower.id for follower in followers if follower.id != person.id) num_sponsored_bills += 1 return OrderedDict( (key, (value + .0) / num_sponsored_bills) for key, value in counter.most_common(10)\ if (value + .0) / num_sponsored_bills > threshold )
def person_main(): sort = request.args.get('sort', 'name') election_type = request.args.get('election_type', 'assembly') assembly_id = int(request.args.get('assembly_id', current_parliament_id(election_type)) or 0) if sort == 'cosponsorship': bill_t = Bill.__table__ cosponsorship_count = db_session.query( cosponsorship.c.person_id, func.count(cosponsorship.c.id).label('cosponsorship_count') )\ .outerjoin(bill_t)\ .filter(bill_t.c.assembly_id == assembly_id)\ .group_by(cosponsorship.c.person_id)\ .subquery('cosponsorship_count') officials = Person.query.order_by( desc(func.coalesce(func.sum(cosponsorship_count.c.cosponsorship_count), 0)) )\ .join(Candidacy)\ .outerjoin(cosponsorship_count)\ .filter(and_(Candidacy.type == election_type, Candidacy.assembly_id == assembly_id, Candidacy.is_elected == True))\ .group_by(Person.id) else: officials = Person.query.order_by(Person.name)\ .join(Candidacy)\ .filter(and_(Candidacy.type == election_type, Candidacy.assembly_id == assembly_id, Candidacy.is_elected == True)) # party list party_count = defaultdict(int) for official in officials: party_count[official.cur_party] += 1 party_list = [p[0] for p in sorted(party_count.items(), key=lambda x: x[1], reverse=True)] return render_template('people.html', officials=officials, assembly_id=assembly_id, party_list=party_list)
def counts(): nbills = db_session.query(Bill).count() nmeetings = db_session.query(Meeting).count() npeople = db_session.query(Person).count() return nbills, nmeetings, npeople
def latest_meeting_date(year): date_ = db_session.query(Meeting.date)\ .filter(Meeting.year == year)\ .order_by(Meeting.date.desc())\ .first() return date_[0] if date_ else None
def status_count(status, assembly_id): return db_session.query(Bill).filter(\ Bill.status_id==status.id, Bill.assembly_id==assembly_id ).count()