def committees(): committee_table = Committee.__table__ candidates_table = Candidate.__table__ raw_query_params = request.args.copy() limit = request.args.get('limit', 500) offset = request.args.get('offset', 0) order_by = request.args.get('order_by', 'status_date') sort_order = request.args.get('sort_order', 'desc') if limit > 500: limit = 500 valid_query, query_clauses, resp, status_code = make_query(committee_table, raw_query_params) if valid_query: committee_cols = [c.label('committee_%s' % c.name) for c in committee_table.columns] candidate_cols = [c.label('candidate_%s' % c.name) for c in candidates_table.columns] all_columns = committee_cols + candidate_cols base_query = db_session.query(*all_columns)\ .join(candidate_committees, candidate_committees.c.committee_id == committee_table.c.id)\ .join(candidates_table, candidate_committees.c.candidate_id == candidates_table.c.id) for clause in query_clauses: base_query = base_query.filter(clause) order_by_col = getattr(committee_table.c, order_by) base_query = base_query.order_by(getattr(order_by_col, sort_order)()) base_query = base_query.limit(limit) objs = [] committee_fields = committee_table.columns.keys() candidate_fields = candidates_table.columns.keys() rows = sorted(list(base_query.all()), key=attrgetter('committee_id')) for committee, grouping in groupby(rows, attrgetter('committee_id')): rows = list(grouping) committee_values = rows[0][:len(committee_fields)] committee_info = OrderedDict(zip(committee_fields, committee_values)) candidates = [] for row in rows: candidate_values = row[len(committee_fields):] candidate_info = OrderedDict(zip(candidate_fields, candidate_values)) candidates.append(candidate_info) committee_info['candidates'] = candidates objs.append(committee_info) resp['objects'] = objs resp['meta']['query'].update({ 'limit': limit, 'offset': offset, 'sort_order': sort_order, 'order_by': order_by, }) response = make_response(json.dumps(resp, default=dthandler, sort_keys=False)) response.headers['Content-Type'] = 'application/json' return response
def contribution(receipt_id): try: receipt_id = int(receipt_id) except ValueError: return abort(404) receipt = db_session.query(Receipt).get(receipt_id) if not receipt: return abort(404) if not receipt.committee: return abort(404) return render_template('contribution-detail.html', receipt=receipt)
def expense(expense_id): try: expense_id = int(expense_id) except ValueError: return abort(404) expense = db_session.query(Expenditure).get(expense_id) if not expense: return abort(404) if not expense.committee: return abort(404) return render_template('expense-detail.html', expense=expense)
def getReceiptsTotal(committee_id, doc_name, last_period_end, last_receipt_date=None): total_receipts = db_session.query(func.sum(Receipt.amount))\ .join(FiledDoc, Receipt.filed_doc_id==FiledDoc.id)\ .filter(Receipt.committee_id==committee_id)\ .filter(FiledDoc.doc_name==doc_name)\ .filter(FiledDoc.reporting_period_begin >= last_period_end) if last_receipt_date: total_receipts = total_receipts.filter(FiledDoc.received_datetime <= last_receipt_date) total_receipts = total_receipts.scalar() return total_receipts if total_receipts is not None else 0.0
def get_candidate_funds(candidate_id): try: candidate_id = int(candidate_id) except ValueError: return abort(404) candidate = db_session.query(Candidate).get(candidate_id) if not candidate: return abort(404) candidate_name = candidate.first_name + " " + candidate.last_name params= {'candidate_name': candidate_name} d2_part = '9B' params= {'d2_part': d2_part} support_min_date = datetime(2016, 3, 16, 0, 0) params = {'support_min_date': support_min_date} expended_date = support_min_date params = {'expended_date': expended_date} supporting_funds_sql = ''' SELECT COALESCE(SUM(amount), 0) AS amount FROM condensed_expenditures WHERE candidate_name = :candidate_name AND d2_part = :d2_part AND expended_date > :expended_date AND supporting = 'true' ''' supporting_funds = g.engine.execute(sa.text(supporting_funds_sql), candidate_name=candidate_name, d2_part=d2_part, expended_date=expended_date).first().amount opposing_funds_sql = ''' SELECT COALESCE(SUM(amount), 0) AS amount FROM condensed_expenditures WHERE candidate_name = :candidate_name AND d2_part = :d2_part AND expended_date > :expended_date AND opposing = 'true' ''' opposing_funds = g.engine.execute(sa.text(opposing_funds_sql), candidate_name=candidate_name, d2_part=d2_part, expended_date=expended_date).first().amount return supporting_funds, opposing_funds
def candidate(candidate_id): candidate_id = candidate_id.rsplit('-', 1)[-1] try: candidate_id = int(candidate_id) except ValueError: return abort(404) candidate = db_session.query(Candidate).get(candidate_id) if not candidate: return abort(404) supporting = [c for c in candidate.committees] return render_template('candidate-detail.html', candidate=candidate, supporting=supporting)
def getFundsRaisedTotal(committee_id, quarterly_start_date, next_quarterly_start_date, receipt_end_date): pre_primary_total_raised = db_session.query(func.sum(D2Report.total_receipts))\ .join(FiledDoc, D2Report.filed_doc_id==FiledDoc.id)\ .filter(D2Report.committee_id==committee_id)\ .filter(FiledDoc.doc_name=="Quarterly")\ .filter(FiledDoc.reporting_period_begin >= quarterly_start_date)\ .filter(FiledDoc.reporting_period_end < next_quarterly_start_date)\ .scalar() # If the Primary Quarterly report hasn't been filed yet, then return None. if pre_primary_total_raised is None: return None # Add contributions since last quarterly report. quarterly_end_date = parse(next_quarterly_start_date) - timedelta(days=1) contributions = getReceiptsTotal(committee_id, "A-1", quarterly_end_date, receipt_end_date) return pre_primary_total_raised + contributions
def committees(): committee_table = Committee.__table__ candidates_table = Candidate.__table__ raw_query_params = request.args.copy() limit = request.args.get("limit", 500) offset = request.args.get("offset", 0) order_by = request.args.get("order_by", "status_date") sort_order = request.args.get("sort_order", "desc") if limit > 500: limit = 500 valid_query, query_clauses, resp, status_code = make_query(committee_table, raw_query_params) if valid_query: committee_cols = [c.label("committee_%s" % c.name) for c in committee_table.columns] candidate_cols = [c.label("candidate_%s" % c.name) for c in candidates_table.columns] all_columns = committee_cols + candidate_cols base_query = db_session.query(*all_columns).join(candidate_committees).join(candidates_table) for clause in query_clauses: base_query = base_query.filter(clause) order_by_col = getattr(committee_table.c, order_by) base_query = base_query.order_by(getattr(order_by_col, sort_order)()) base_query = base_query.limit(limit) objs = [] committee_fields = committee_table.columns.keys() candidate_fields = candidates_table.columns.keys() rows = sorted(list(base_query.all()), key=attrgetter("committee_id")) for committee, grouping in groupby(rows, attrgetter("committee_id")): rows = list(grouping) committee_values = rows[0][: len(committee_fields)] committee_info = OrderedDict(zip(committee_fields, committee_values)) candidates = [] for row in rows: candidate_values = row[len(committee_fields) :] candidate_info = OrderedDict(zip(candidate_fields, candidate_values)) candidates.append(candidate_info) committee_info["candidates"] = candidates objs.append(committee_info) resp["objects"] = objs resp["meta"]["query"].update({"limit": limit, "offset": offset, "sort_order": sort_order, "order_by": order_by}) response = make_response(json.dumps(resp, default=dthandler, sort_keys=False)) response.headers["Content-Type"] = "application/json" return response
def get_committee_details(committee_id): committee_id = committee_id.rsplit('-', 1)[-1] try: committee_id = int(committee_id) except ValueError: return abort(404) committee = db_session.query(Committee).get(committee_id) if not committee: return abort(404) latest_filing = ''' SELECT * FROM most_recent_filings WHERE committee_id = :committee_id ORDER BY received_datetime DESC LIMIT 1 ''' latest_filing = g.engine.execute(sa.text(latest_filing), committee_id=committee_id).first() params = {'committee_id': committee_id} if latest_filing.end_funds_available \ or latest_filing.end_funds_available == 0: recent_receipts = ''' SELECT COALESCE(SUM(receipts.amount), 0) AS amount FROM condensed_receipts AS receipts JOIN filed_docs AS filed ON receipts.filed_doc_id = filed.id WHERE receipts.committee_id = :committee_id AND receipts.received_date > :end_date ''' controlled_amount = latest_filing.end_funds_available params['end_date'] = latest_filing.reporting_period_end end_date = latest_filing.reporting_period_end else: recent_receipts = ''' SELECT COALESCE(SUM(receipts.amount), 0) AS amount FROM condensed_receipts JOIN filed_docs AS filed ON receipts.filed_doc_id = filed.id WHERE receipts.committee_id = :committee_id ''' controlled_amount = 0 recent_total = g.engine.execute(sa.text(recent_receipts),**params).first().amount controlled_amount += recent_total params = {'committee_id': committee_id} quarterlies = ''' SELECT DISTINCT ON (f.doc_name, f.reporting_period_end) r.end_funds_available, r.total_investments, r.total_receipts, (r.debts_itemized * -1) as debts_itemized, (r.debts_non_itemized * -1) as debts_non_itemized, (r.total_expenditures * -1) as total_expenditures, f.reporting_period_end FROM d2_reports AS r JOIN filed_docs AS f ON r.filed_doc_id = f.id WHERE r.committee_id = :committee_id AND f.reporting_period_end IS NOT NULL AND f.doc_name = 'Quarterly' ORDER BY f.reporting_period_end ASC ''' quarterlies = list(g.engine.execute(sa.text(quarterlies), committee_id=committee_id)) ending_funds = [[r.end_funds_available, r.reporting_period_end.year, r.reporting_period_end.month, r.reporting_period_end.day] for r in quarterlies] investments = [[r.total_investments, r.reporting_period_end.year, r.reporting_period_end.month, r.reporting_period_end.day] for r in quarterlies] debts = [[(r.debts_itemized + r.debts_non_itemized), r.reporting_period_end.year, r.reporting_period_end.month, r.reporting_period_end.day] for r in quarterlies] expenditures = [[r.total_expenditures, r.reporting_period_end.year, r.reporting_period_end.month, r.reporting_period_end.day] for r in quarterlies] #accomodate for independent expenditures past last filing date total_expenditures = sum([r.total_expenditures for r in quarterlies]) return committee, recent_receipts, recent_total, latest_filing, controlled_amount, ending_funds, investments, debts, expenditures, total_expenditures
def independent_expenditures(candidate_id, stance): candidate_id = candidate_id.rsplit('-', 1)[-1] try: candidate_id = int(candidate_id) except ValueError: return abort(404) candidate = db_session.query(Candidate).get(candidate_id) if not candidate: return abort(404) cname = [candidate.first_name, candidate.last_name] candidate_name = " ".join(cname) independent_expenditures_type = 'Supporting' independent_expenditures_title = "Supporting Independent Expenditures" independent_expenditures_description = "Independent expenditures in support of " + candidate_name + " since March 16, 2016" type_arg = 'supporting' if stance == 'opposing': independent_expenditures_type = 'Opposing' independent_expenditures_title = "Opposing Independent Expenditures" independent_expenditures_description = "Independent expenditures in opposition to " + candidate_name + " since March 16, 2016" all_names = [] all_names.append(candidate_name) #get all possible names for candidate alternate_names_sql = ''' SELECT alternate_names FROM contested_races WHERE candidate_id = :candidate_id ''' alternate_names = g.engine.execute(sa.text(alternate_names_sql), candidate_id=candidate_id).first() if alternate_names: alt_names = alternate_names.alternate_names.split(';') for name in alt_names: all_names.append(name.strip()) d2_part = '9B' expended_date = datetime(2016, 3, 16, 0, 0) independent_expenditures =[] master_names = set(all_names) for candidate_name in master_names: if independent_expenditures_type == "Supporting": ind_expenditures_sql = ''' SELECT committee_id, amount AS amount, expended_date AS date FROM condensed_expenditures WHERE candidate_name = :candidate_name AND d2_part = :d2_part AND expended_date > :expended_date AND supporting = 'true' ''' else: ind_expenditures_sql = ''' SELECT committee_id, amount AS amount, expended_date AS date FROM condensed_expenditures WHERE candidate_name = :candidate_name AND d2_part = :d2_part AND expended_date > :expended_date AND opposing = 'true' ''' ind_expends = list(g.engine.execute(sa.text(ind_expenditures_sql), candidate_name=candidate_name, d2_part=d2_part, expended_date=expended_date)) for ie in ind_expends: ie_dict = dict(ie.items()) ie_dict['committee_name'] = db_session.query(Committee).get(ie_dict['committee_id']).name independent_expenditures.append(ie_dict) newlist = sorted(independent_expenditures, key=lambda k: k['date']) candidate_name = " ".join(cname) return render_template('independent-expenditures.html', independent_expenditures_type=independent_expenditures_type, independent_expenditures_title=independent_expenditures_title, independent_expenditures=newlist, independent_expenditures_description=independent_expenditures_description, candidate_name=candidate_name, candidate_id=candidate_id)
def expenditures(): raw_query_params = request.args.copy() limit = request.args.get("limit", 1000) offset = request.args.get("offset", 0) order_by = request.args.get("order_by", "expended_date") sort_order = request.args.get("sort_order", "desc") datatype = request.args.get("datatype") expenditures_table = sa.Table("condensed_expenditures", sa.MetaData(), autoload=True, autoload_with=db_session.bind) valid_query, query_clauses, resp, status_code = make_query(expenditures_table, raw_query_params) if not raw_query_params.get("committee_id"): resp = {"status": "error", "message": "A committee ID is required"} status_code = 400 valid_query = False if valid_query: committees_table = Committee.__table__ committee_cols = [c.label("committee_%s" % c.name) for c in committees_table.columns] expenditure_cols = [c.label("expenditure_%s" % c.name) for c in expenditures_table.columns] all_columns = committee_cols + expenditure_cols base_query = db_session.query(*all_columns).join( expenditures_table, expenditures_table.c.committee_id == committees_table.c.id ) for clause in query_clauses: base_query = base_query.filter(clause) order_by_col = getattr(expenditures_table.c, order_by) base_query = base_query.order_by(getattr(order_by_col, sort_order)()) limit_query = base_query.limit(int(limit)) limit_query = limit_query.offset(int(offset)) objs = [] committee_fields = committees_table.columns.keys() expenditure_fields = expenditures_table.columns.keys() rows = sorted(list(limit_query.all()), key=attrgetter("committee_id")) committee_info = {"expenditures": [], "name": ""} for committee, grouping in groupby(rows, attrgetter("committee_id")): rows = list(grouping) committee_values = rows[0][: len(committee_fields)] committee_info = OrderedDict(zip(committee_fields, committee_values)) expenditures = [] for row in rows: expenditure_values = row[len(committee_fields) :] expenditure_info = OrderedDict(zip(expenditure_fields, expenditure_values)) expenditures.append(expenditure_info) committee_info["expenditures"] = expenditures objs.append(committee_info) if datatype == "csv": outp = StringIO() writer = csv.writer(outp) records = committee_info["expenditures"] if records: writer.writerow(list(records[0].keys())) writer.writerows([list(r.values()) for r in records]) response = make_response(outp.getvalue(), 200) filedate = datetime.now().strftime("%Y-%m-%d") response.headers["Content-Type"] = "text/csv" fname = "Illinois_Sunshine_Committee_Expenditures_%s_%s.csv" % ( "_".join(committee_info["name"].split(" ")), filedate, ) response.headers["Content-Disposition"] = "attachment; filename=%s" % (fname) return response total_rows = base_query.count() resp["objects"] = objs resp["meta"]["query"].update({"limit": limit, "offset": offset, "sort_order": sort_order, "order_by": order_by}) resp["meta"]["total_rows"] = total_rows response = make_response(json.dumps(resp, default=dthandler, sort_keys=False)) response.headers["Content-Type"] = "application/json" return response
def committee(committee_id): committee_id = committee_id.rsplit('-', 1)[-1] try: committee_id = int(committee_id) except ValueError: return abort(404) committee = db_session.query(Committee).get(committee_id) if not committee: return abort(404) latest_filing = ''' SELECT * FROM most_recent_filings WHERE committee_id = :committee_id ORDER BY received_datetime DESC LIMIT 1 ''' latest_filing = g.engine.execute(sa.text(latest_filing), committee_id=committee_id).first() params = {'committee_id': committee_id} if latest_filing.end_funds_available \ or latest_filing.end_funds_available == 0: recent_receipts = ''' SELECT COALESCE(SUM(receipts.amount), 0) AS amount FROM condensed_receipts AS receipts JOIN filed_docs AS filed ON receipts.filed_doc_id = filed.id WHERE receipts.committee_id = :committee_id AND receipts.received_date > :end_date ''' controlled_amount = latest_filing.end_funds_available params['end_date'] = latest_filing.reporting_period_end else: recent_receipts = ''' SELECT COALESCE(SUM(receipts.amount), 0) AS amount FROM condensed_receipts JOIN filed_docs AS filed ON receipts.filed_doc_id = filed.id WHERE receipts.committee_id = :committee_id ''' controlled_amount = 0 recent_total = g.engine.execute(sa.text(recent_receipts),**params).first().amount controlled_amount += recent_total candidate_ids = tuple(c.id for c in committee.candidates) related_committees = ''' SELECT name, id, type, active, money, reason FROM ( (SELECT o.name, o.committee_id AS id, o.type, o.active, o.money, 'Officers with the same name' AS reason FROM ( SELECT cm.name, cm.active, oc.committee_id, o.first_name, o.last_name, cm.type, m.total AS money FROM committees AS cm LEFT JOIN officer_committees AS oc ON cm.id = oc.committee_id JOIN officers AS o ON oc.officer_id = o.id LEFT JOIN committee_money AS m ON oc.committee_id = m.committee_id ) AS o JOIN ( SELECT cm.name, oc.committee_id, o.first_name, o.last_name, cm.type, m.total AS money FROM committees AS cm LEFT JOIN officer_committees AS oc ON cm.id = oc.committee_id JOIN officers AS o ON oc.officer_id = o.id LEFT JOIN committee_money AS m ON oc.committee_id = m.committee_id ) AS o2 ON TRIM(BOTH ' ' FROM REPLACE(o.first_name, '.', '')) = TRIM(BOTH ' ' FROM REPLACE(o2.first_name, '.', '')) AND TRIM(BOTH ' ' FROM REPLACE(o.last_name, '.', '')) = TRIM(BOTH ' ' FROM REPLACE(o2.last_name, '.', '')) WHERE o.committee_id != o2.committee_id AND (o.committee_id = :committee_id OR o2.committee_id = :committee_id)) ''' params = {'committee_id': committee_id} if candidate_ids: unions = ''' UNION (SELECT cm.name, cm.id, cm.type, cm.active, m.total AS money, 'Supported candidates in common' AS reason FROM committees AS cm LEFT JOIN candidate_committees AS cc ON cm.id = cc.committee_id LEFT JOIN candidates AS cd ON cc.candidate_id = cd.id LEFT JOIN committee_money AS m ON cm.id = m.committee_id WHERE cd.id IN :candidate_ids) UNION (SELECT c.name, c.id, c.type, c.active, m.total AS money, 'Officer with same name as supported candidate' AS reason FROM candidates AS cd LEFT JOIN officers AS o ON TRIM(BOTH ' ' FROM REPLACE(cd.first_name, '.', '')) = TRIM(BOTH ' ' FROM REPLACE(o.first_name, '.', '')) AND TRIM(BOTH ' ' FROM REPLACE(cd.last_name, '.', '')) = TRIM(BOTH ' ' FROM REPLACE(o.last_name, '.', '')) LEFT JOIN officer_committees AS oc ON o.id = oc.officer_id JOIN committees AS c ON oc.committee_id = c.id LEFT JOIN committee_money AS m ON oc.committee_id = m.committee_id WHERE cd.id IN :candidate_ids ) ''' related_committees += unions params['candidate_ids'] = candidate_ids related_committees += ''' ) AS s WHERE id != :committee_id AND active = TRUE ''' related_committees = list(g.engine.execute(sa.text(related_committees),**params)) supported_candidates = [] opposed_candidates = [] related_candidates_sql = ''' SELECT candidate_name, office, opposing, supporting, supporting_amount, opposing_amount FROM expenditures_by_candidate WHERE committee_id = :committee_id ORDER BY supporting_amount DESC, opposing_amount DESC ''' related_candidates = list(g.engine.execute(sa.text(related_candidates_sql), committee_id=committee.id)) for c in related_candidates: if c.supporting: added = False for sc in supported_candidates: if sc['candidate_name'] == c.candidate_name: added = True sc['office'] = sc['office'] + ", " + c.office if not added: supported_candidates.append(dict(c.items())) if c.opposing: added = False for sc in opposed_candidates: if sc['candidate_name'] == c.candidate_name: added = True sc['office'] = sc['office'] + ", " + c.office if not added: opposed_candidates.append(dict(c.items())) current_officers = [officer for officer in committee.officers if officer.current] quarterlies = ''' SELECT DISTINCT ON (f.doc_name, f.reporting_period_end) r.end_funds_available, r.total_investments, r.total_receipts, (r.debts_itemized * -1) as debts_itemized, (r.debts_non_itemized * -1) as debts_non_itemized, (r.total_expenditures * -1) as total_expenditures, f.reporting_period_end FROM d2_reports AS r JOIN filed_docs AS f ON r.filed_doc_id = f.id WHERE r.committee_id = :committee_id AND f.reporting_period_end IS NOT NULL AND f.doc_name = 'Quarterly' ORDER BY f.reporting_period_end ASC ''' quarterlies = list(g.engine.execute(sa.text(quarterlies), committee_id=committee_id)) expended_date = latest_filing.reporting_period_end recent_expenditures_sql = ''' SELECT (amount * -1) AS amount, expended_date FROM condensed_expenditures WHERE expended_date > :expended_date AND committee_id = :committee_id ORDER BY expended_date DESC ''' recent_expenditures = list(g.engine.execute(sa.text(recent_expenditures_sql), expended_date=expended_date, committee_id=committee_id)) ending_funds = [[r.end_funds_available, r.reporting_period_end.year, r.reporting_period_end.month, r.reporting_period_end.day] for r in quarterlies] investments = [[r.total_investments, r.reporting_period_end.year, r.reporting_period_end.month, r.reporting_period_end.day] for r in quarterlies] debts = [[(r.debts_itemized + r.debts_non_itemized), r.reporting_period_end.year, r.reporting_period_end.month, r.reporting_period_end.day] for r in quarterlies] donations = [[r.total_receipts, r.reporting_period_end.year, r.reporting_period_end.month, r.reporting_period_end.day] for r in quarterlies] expenditures = [] for r in recent_expenditures: expenditures.append([r.amount, r.expended_date.year, r.expended_date.month, r.expended_date.day]) for r in quarterlies: expenditures.append([r.total_expenditures, r.reporting_period_end.year, r.reporting_period_end.month, r.reporting_period_end.day]) total_donations = sum([r.total_receipts for r in quarterlies]) total_expenditures = sum([r.total_expenditures for r in quarterlies]) + sum([r.amount for r in recent_expenditures]) return render_template('committee-detail.html', committee=committee, supported_candidates=supported_candidates, opposed_candidates=opposed_candidates, current_officers=current_officers, related_committees=related_committees, recent_receipts=recent_receipts, recent_total=recent_total, latest_filing=latest_filing, controlled_amount=controlled_amount, ending_funds=ending_funds, investments=investments, debts=debts, donations=donations, expenditures=expenditures, total_donations=total_donations, total_expenditures=total_expenditures)
def committee(committee_id): committee_id = committee_id.rsplit('-', 1)[-1] try: committee_id = int(committee_id) except ValueError: return abort(404) committee = db_session.query(Committee).get(committee_id) if not committee: return abort(404) latest_filing = ''' SELECT * FROM most_recent_filings WHERE committee_id = :committee_id ORDER BY received_datetime DESC LIMIT 1 ''' latest_filing = g.engine.execute(sa.text(latest_filing), committee_id=committee_id).first() params = {'committee_id': committee_id} if latest_filing.end_funds_available \ or latest_filing.end_funds_available == 0: recent_receipts = ''' SELECT COALESCE(SUM(receipts.amount), 0) AS amount FROM receipts JOIN filed_docs AS filed ON receipts.filed_doc_id = filed.id WHERE receipts.committee_id = :committee_id AND receipts.received_date > :end_date ''' controlled_amount = latest_filing.end_funds_available + \ latest_filing.total_investments - \ latest_filing.total_debts params['end_date'] = latest_filing.reporting_period_end else: recent_receipts = ''' SELECT COALESCE(SUM(receipts.amount), 0) AS amount FROM receipts JOIN filed_docs AS filed ON receipts.filed_doc_id = filed.id WHERE receipts.committee_id = :committee_id ''' controlled_amount = 0 recent_total = g.engine.execute(sa.text(recent_receipts),**params).first().amount controlled_amount += recent_total candidate_ids = tuple(c.id for c in committee.candidates) related_committees = ''' SELECT name, id, type, active, money, reason FROM ( (SELECT o.name, o.committee_id AS id, o.type, o.active, o.money, 'Officers with the same name' AS reason FROM ( SELECT cm.name, cm.active, o.committee_id, o.first_name, o.last_name, cm.type, m.total AS money FROM committees AS cm JOIN officers AS o ON cm.id = o.committee_id LEFT JOIN committee_money AS m ON cm.id = m.committee_id ) AS o JOIN ( SELECT cm.name, o.committee_id, o.first_name, o.last_name, cm.type, m.total AS money FROM committees AS cm JOIN officers AS o ON cm.id = o.committee_id LEFT JOIN committee_money AS m ON cm.id = m.committee_id ) AS o2 ON o.first_name = o2.first_name AND o.last_name = o2.last_name WHERE o.committee_id != o2.committee_id AND (o.committee_id = :committee_id OR o2.committee_id = :committee_id)) ''' params = {'committee_id': committee_id} if candidate_ids: unions = ''' UNION (SELECT cm.name, cm.id, cm.type, cm.active, m.total AS money, 'Supported candidates in common' AS reason FROM committees AS cm LEFT JOIN candidate_committees AS cc ON cm.id = cc.committee_id LEFT JOIN candidates AS cd ON cc.candidate_id = cd.id LEFT JOIN committee_money AS m ON cm.id = m.committee_id WHERE cd.id IN :candidate_ids) UNION (SELECT oc.name, oc.id, oc.type, oc.active, m.total AS money, 'Officer with same name as supported candidate' AS reason FROM candidates AS cd LEFT JOIN officers AS o ON cd.first_name = o.first_name AND cd.last_name = o.last_name LEFT JOIN committees AS oc ON o.committee_id = oc.id LEFT JOIN committee_money AS m ON oc.id = m.committee_id WHERE cd.id IN :candidate_ids ) ''' related_committees += unions params['candidate_ids'] = candidate_ids related_committees += ''' ) AS s WHERE id != :committee_id AND active = TRUE ''' related_committees = list(g.engine.execute(sa.text(related_committees),**params)) supported_candidates = [] opposed_candidates = [] related_candidates_sql = ''' SELECT candidate_name, office, opposing, supporting, supporting_amount, opposing_amount FROM expenditures_by_candidate WHERE committee_id = :committee_id ORDER BY supporting_amount DESC, opposing_amount DESC ''' related_candidates = list(g.engine.execute(sa.text(related_candidates_sql), committee_id=committee.id)) for c in related_candidates: if c.supporting: added = False for sc in supported_candidates: if sc['candidate_name'] == c.candidate_name: added = True sc['office'] = sc['office'] + ", " + c.office if not added: supported_candidates.append(dict(c.items())) if c.opposing: added = False for sc in opposed_candidates: if sc['candidate_name'] == c.candidate_name: added = True sc['office'] = sc['office'] + ", " + c.office if not added: opposed_candidates.append(dict(c.items())) current_officers = [officer for officer in committee.officers if officer.current] quarterlies = ''' SELECT DISTINCT ON (f.doc_name, f.reporting_period_end) r.end_funds_available, r.total_investments, r.total_receipts, (r.debts_itemized * -1) as debts_itemized, (r.debts_non_itemized * -1) as debts_non_itemized, (r.total_expenditures * -1) as total_expenditures, f.reporting_period_end FROM d2_reports AS r JOIN filed_docs AS f ON r.filed_doc_id = f.id WHERE r.committee_id = :committee_id AND f.reporting_period_end IS NOT NULL ORDER BY f.reporting_period_end ASC ''' quarterlies = list(g.engine.execute(sa.text(quarterlies), committee_id=committee_id)) ending_funds = [[r.end_funds_available, r.reporting_period_end.year, r.reporting_period_end.month, r.reporting_period_end.day] for r in quarterlies] investments = [[r.total_investments, r.reporting_period_end.year, r.reporting_period_end.month, r.reporting_period_end.day] for r in quarterlies] debts = [[(r.debts_itemized + r.debts_non_itemized), r.reporting_period_end.year, r.reporting_period_end.month, r.reporting_period_end.day] for r in quarterlies] donations = [[r.total_receipts, r.reporting_period_end.year, r.reporting_period_end.month, r.reporting_period_end.day] for r in quarterlies] expenditures = [[r.total_expenditures, r.reporting_period_end.year, r.reporting_period_end.month, r.reporting_period_end.day] for r in quarterlies] total_donations = sum([r.total_receipts for r in quarterlies]) total_expenditures = sum([r.total_expenditures for r in quarterlies]) return render_template('committee-detail.html', committee=committee, supported_candidates=supported_candidates, opposed_candidates=opposed_candidates, current_officers=current_officers, related_committees=related_committees, recent_receipts=recent_receipts, recent_total=recent_total, latest_filing=latest_filing, controlled_amount=controlled_amount, ending_funds=ending_funds, investments=investments, debts=debts, donations=donations, expenditures=expenditures, total_donations=total_donations, total_expenditures=total_expenditures)
def expenditures(): raw_query_params = request.args.copy() limit = request.args.get('limit', 1000) offset = request.args.get('offset', 0) order_by = request.args.get('order_by', 'expended_date') sort_order = request.args.get('sort_order', 'desc') datatype = request.args.get('datatype') expenditures_table = sa.Table('condensed_expenditures', sa.MetaData(), autoload=True, autoload_with=db_session.bind) valid_query, query_clauses, resp, status_code = make_query(expenditures_table, raw_query_params) if not raw_query_params.get('committee_id'): resp = { 'status' : 'error', 'message' : 'A committee ID is required', } status_code = 400 valid_query = False if valid_query: committees_table = Committee.__table__ committee_cols = [c.label('committee_%s' % c.name) for c in committees_table.columns] expenditure_cols = [c.label('expenditure_%s' % c.name) for c in expenditures_table.columns] all_columns = committee_cols + expenditure_cols base_query = db_session.query(*all_columns)\ .join(expenditures_table, expenditures_table.c.committee_id == committees_table.c.id) for clause in query_clauses: base_query = base_query.filter(clause) order_by_col = getattr(expenditures_table.c, order_by) base_query = base_query.order_by(getattr(order_by_col, sort_order)()) limit_query = base_query.limit(int(limit)) limit_query = limit_query.offset(int(offset)) objs = [] committee_fields = committees_table.columns.keys() expenditure_fields = expenditures_table.columns.keys() rows = sorted(list(limit_query.all()), key=attrgetter('committee_id')) committee_info = {'expenditures': [], 'name': ''} for committee, grouping in groupby(rows, attrgetter('committee_id')): rows = list(grouping) committee_values = rows[0][:len(committee_fields)] committee_info = OrderedDict(zip(committee_fields, committee_values)) expenditures = [] for row in rows: expenditure_values = row[len(committee_fields):] expenditure_info = OrderedDict(zip(expenditure_fields, expenditure_values)) expenditures.append(expenditure_info) committee_info['expenditures'] = expenditures objs.append(committee_info) if datatype == 'csv': outp = StringIO() writer = csv.writer(outp) records = committee_info['expenditures'] if records: writer.writerow(list(records[0].keys())) writer.writerows([list(r.values()) for r in records]) response = make_response(outp.getvalue(), 200) filedate = datetime.now().strftime('%Y-%m-%d') response.headers['Content-Type'] = 'text/csv' fname = 'Illinois_Sunshine_Committee_Expenditures_%s_%s.csv' % ('_'.join(committee_info['name'].split(' ')), filedate) response.headers['Content-Disposition'] = 'attachment; filename="%s"' % (fname) return response total_rows = base_query.count() resp['objects'] = objs resp['meta']['query'].update({ 'limit': limit, 'offset': offset, 'sort_order': sort_order, 'order_by': order_by, }) resp['meta']['total_rows'] = total_rows response = make_response(json.dumps(resp, default=dthandler, sort_keys=False)) response.headers['Content-Type'] = 'application/json' return response
def expenditures(): raw_query_params = request.args.copy() limit = request.args.get('limit', 1000) offset = request.args.get('offset', 0) order_by = request.args.get('order_by', 'expended_date') sort_order = request.args.get('sort_order', 'desc') datatype = request.args.get('datatype') expenditures_table = sa.Table('condensed_expenditures', sa.MetaData(), autoload=True, autoload_with=db_session.bind) valid_query, query_clauses, resp, status_code = make_query( expenditures_table, raw_query_params) if not raw_query_params.get('committee_id'): resp = { 'status': 'error', 'message': 'A committee ID is required', } status_code = 400 valid_query = False if valid_query: committees_table = Committee.__table__ committee_cols = [ c.label('committee_%s' % c.name) for c in committees_table.columns ] expenditure_cols = [ c.label('expenditure_%s' % c.name) for c in expenditures_table.columns ] all_columns = committee_cols + expenditure_cols base_query = db_session.query(*all_columns)\ .join(expenditures_table, expenditures_table.c.committee_id == committees_table.c.id) for clause in query_clauses: base_query = base_query.filter(clause) order_by_col = getattr(expenditures_table.c, order_by) base_query = base_query.order_by(getattr(order_by_col, sort_order)()) limit_query = base_query.limit(int(limit)) limit_query = limit_query.offset(int(offset)) objs = [] committee_fields = committees_table.columns.keys() expenditure_fields = expenditures_table.columns.keys() rows = sorted(list(limit_query.all()), key=attrgetter('committee_id')) committee_info = {'expenditures': [], 'name': ''} for committee, grouping in groupby(rows, attrgetter('committee_id')): rows = list(grouping) committee_values = rows[0][:len(committee_fields)] committee_info = OrderedDict( zip(committee_fields, committee_values)) expenditures = [] for row in rows: expenditure_values = row[len(committee_fields):] expenditure_info = OrderedDict( zip(expenditure_fields, expenditure_values)) expenditures.append(expenditure_info) committee_info['expenditures'] = expenditures objs.append(committee_info) if datatype == 'csv': outp = StringIO() writer = csv.writer(outp) records = committee_info['expenditures'] if records: writer.writerow(list(records[0].keys())) writer.writerows([list(r.values()) for r in records]) response = make_response(outp.getvalue(), 200) filedate = datetime.now().strftime('%Y-%m-%d') response.headers['Content-Type'] = 'text/csv' fname = 'Illinois_Sunshine_Committee_Expenditures_%s_%s.csv' % ( '_'.join(committee_info['name'].split(' ')), filedate) response.headers[ 'Content-Disposition'] = 'attachment; filename="%s"' % (fname) return response total_rows = base_query.count() resp['objects'] = objs resp['meta']['query'].update({ 'limit': limit, 'offset': offset, 'sort_order': sort_order, 'order_by': order_by, }) resp['meta']['total_rows'] = total_rows response = make_response( json.dumps(resp, default=dthandler, sort_keys=False)) response.headers['Content-Type'] = 'application/json' return response
def committees(): committee_table = Committee.__table__ candidates_table = Candidate.__table__ raw_query_params = request.args.copy() limit = request.args.get('limit', 500) offset = request.args.get('offset', 0) order_by = request.args.get('order_by', 'status_date') sort_order = request.args.get('sort_order', 'desc') if limit > 500: limit = 500 valid_query, query_clauses, resp, status_code = make_query( committee_table, raw_query_params) if valid_query: committee_cols = [ c.label('committee_%s' % c.name) for c in committee_table.columns ] candidate_cols = [ c.label('candidate_%s' % c.name) for c in candidates_table.columns ] all_columns = committee_cols + candidate_cols base_query = db_session.query(*all_columns)\ .join(candidate_committees, candidate_committees.c.committee_id == committee_table.c.id)\ .join(candidates_table, candidate_committees.c.candidate_id == candidates_table.c.id) for clause in query_clauses: base_query = base_query.filter(clause) order_by_col = getattr(committee_table.c, order_by) base_query = base_query.order_by(getattr(order_by_col, sort_order)()) base_query = base_query.limit(limit) objs = [] committee_fields = committee_table.columns.keys() candidate_fields = candidates_table.columns.keys() rows = sorted(list(base_query.all()), key=attrgetter('committee_id')) for committee, grouping in groupby(rows, attrgetter('committee_id')): rows = list(grouping) committee_values = rows[0][:len(committee_fields)] committee_info = OrderedDict( zip(committee_fields, committee_values)) candidates = [] for row in rows: candidate_values = row[len(committee_fields):] candidate_info = OrderedDict( zip(candidate_fields, candidate_values)) candidates.append(candidate_info) committee_info['candidates'] = candidates objs.append(committee_info) resp['objects'] = objs resp['meta']['query'].update({ 'limit': limit, 'offset': offset, 'sort_order': sort_order, 'order_by': order_by, }) response = make_response( json.dumps(resp, default=dthandler, sort_keys=False)) response.headers['Content-Type'] = 'application/json' return response
def expenditures(): raw_query_params = request.args.copy() limit = request.args.get('limit', 500) offset = request.args.get('offset', 0) order_by = request.args.get('order_by', 'expended_date') sort_order = request.args.get('sort_order', 'desc') if int(limit) > 500: limit = 500 expenditures_table = sa.Table('condensed_expenditures', sa.MetaData(), autoload=True, autoload_with=db_session.bind) valid_query, query_clauses, resp, status_code = make_query(expenditures_table, raw_query_params) if valid_query: committees_table = Committee.__table__ committee_cols = [c.label('committee_%s' % c.name) for c in committees_table.columns] expenditure_cols = [c.label('expenditure_%s' % c.name) for c in expenditures_table.columns] all_columns = committee_cols + expenditure_cols base_query = db_session.query(*all_columns)\ .join(expenditures_table, expenditures_table.c.committee_id == committees_table.c.id) for clause in query_clauses: base_query = base_query.filter(clause) order_by_col = getattr(expenditures_table.c, order_by) base_query = base_query.order_by(getattr(order_by_col, sort_order)()) limit_query = base_query.limit(int(limit)) limit_query = limit_query.offset(int(offset)) objs = [] committee_fields = committees_table.columns.keys() expenditure_fields = expenditures_table.columns.keys() rows = sorted(list(limit_query.all()), key=attrgetter('committee_id')) for committee, grouping in groupby(rows, attrgetter('committee_id')): rows = list(grouping) committee_values = rows[0][:len(committee_fields)] committee_info = OrderedDict(zip(committee_fields, committee_values)) expenditures = [] for row in rows: expenditure_values = row[len(committee_fields):] expenditure_info = OrderedDict(zip(expenditure_fields, expenditure_values)) expenditures.append(expenditure_info) committee_info['expenditures'] = expenditures objs.append(committee_info) total_rows = base_query.count() resp['objects'] = objs resp['meta']['query'].update({ 'limit': limit, 'offset': offset, 'sort_order': sort_order, 'order_by': order_by, 'total_rows': total_rows, }) response = make_response(json.dumps(resp, default=dthandler, sort_keys=False)) response.headers['Content-Type'] = 'application/json' return response
def getCommitteeFundsData(committee_id, pre_primary_start, primary_start, post_primary_start): current_date = datetime.now().strftime("%Y-%m-%d") table_display_data = [] total_funds_raised = 0.0 primary_funds_raised = None pre_primary_quarterlies = db_session.query(D2Report)\ .join(FiledDoc, D2Report.filed_doc_id==FiledDoc.id)\ .filter(D2Report.archived == False)\ .filter(FiledDoc.archived == False)\ .filter(D2Report.committee_id==committee_id)\ .filter(FiledDoc.doc_name=="Quarterly")\ .filter(FiledDoc.reporting_period_begin >= pre_primary_start)\ .filter(FiledDoc.reporting_period_end <= primary_start)\ .order_by(FiledDoc.reporting_period_begin)\ .all() primary_quarterlies = db_session.query(D2Report)\ .join(FiledDoc, D2Report.filed_doc_id==FiledDoc.id)\ .filter(D2Report.archived == False)\ .filter(FiledDoc.archived == False)\ .filter(D2Report.committee_id==committee_id)\ .filter(FiledDoc.doc_name=="Quarterly")\ .filter(FiledDoc.reporting_period_begin >= primary_start)\ .filter(FiledDoc.reporting_period_end <= current_date)\ .order_by(FiledDoc.reporting_period_begin)\ .all() if current_date >= post_primary_start: if primary_quarterlies: # Add rows for each primary quarterly report. for i, rpt in enumerate(primary_quarterlies): rpt_label = "Funds Raised {dtstart:%b} {dtstart.day}, {dtstart.year} to {dtend:%b} {dtend.day}, {dtend.year}".format( dtstart = rpt.filed_doc.reporting_period_begin, dtend = rpt.filed_doc.reporting_period_end ) rpt_total = rpt.total_receipts # For the first quarterly (the primary quarterly) use the end_funds_available. if i == 0: rpt_label = "Funds Available After Primary" rpt_total = rpt.end_funds_available table_display_data.append([rpt_label, rpt_total]) last_quarterly_date = rpt.filed_doc.reporting_period_end total_funds_raised += rpt_total # Add a row for funds raised since the last primary quarterly report (pulled from Receipt). total_receipts = getReceiptsTotal(committee_id, "A-1", last_quarterly_date, current_date) receipts_label = "Funds Raised Since {dt:%b} {dt.day}, {dt.year}".format(dt = last_quarterly_date) table_display_data.append([receipts_label, total_receipts]) total_funds_raised += total_receipts # Add a row for the total funds raised. table_display_data.append(["Total Funds Raised", total_funds_raised]) else: if pre_primary_quarterlies: # Add the funds available from the last pre-primary quarterly report. pre_primary_end_date = "{dt:%b} {dt.day}, {dt.year}".format(dt = pre_primary_quarterlies[-1].filed_doc.reporting_period_end) table_display_data.append(["Funds Available on " + pre_primary_end_date + " Quarterly Report", pre_primary_quarterlies[-1].end_funds_available]) total_funds_raised = pre_primary_quarterlies[-1].end_funds_available last_quarterly_date = pre_primary_quarterlies[-1].filed_doc.reporting_period_end else: total_funds_raised = 0.0 last_quarterly_date = parse(pre_primary_start) # Add contributions since last quaterly report. total_receipts = getReceiptsTotal(committee_id, "A-1", last_quarterly_date, current_date) receipts_label = "Contributions Since {dt:%b} {dt.day}, {dt.year}".format(dt = last_quarterly_date) table_display_data.append([receipts_label, total_receipts]) total_funds_raised += total_receipts # Add a row for the total funds. table_display_data.append(["Total Funds", total_funds_raised]) else: # Default the last quarterly date to the day before the pre-primary starts. last_quarterly_date = parse(pre_primary_start) - timedelta(days=1) pre_pre_primary_quarterly = db_session.query(D2Report)\ .join(FiledDoc, D2Report.filed_doc_id==FiledDoc.id)\ .filter(D2Report.archived == False)\ .filter(FiledDoc.archived == False)\ .filter(D2Report.committee_id==committee_id)\ .filter(FiledDoc.doc_name=="Quarterly")\ .filter(FiledDoc.reporting_period_end <= last_quarterly_date)\ .order_by(FiledDoc.reporting_period_end.desc())\ .first() # Add the funds available from the last quarterly report from before the pre-primary start date. pre_pre_primary_end_date = "{dt:%b} {dt.day}, {dt.year}".format(dt = last_quarterly_date) pre_pre_primary_funds = 0 if not pre_pre_primary_quarterly else pre_pre_primary_quarterly.end_funds_available table_display_data.append(["Funds Available on " + pre_pre_primary_end_date + " Quarterly Report", pre_pre_primary_funds]) total_funds_raised += pre_pre_primary_funds # Add rows for each pre-primary quarterly report. for rpt in pre_primary_quarterlies: rpt_label = "Funds Raised {dtstart:%b} {dtstart.day}, {dtstart.year} to {dtend:%b} {dtend.day}, {dtend.year}".format( dtstart = rpt.filed_doc.reporting_period_begin, dtend = rpt.filed_doc.reporting_period_end ) table_display_data.append([rpt_label, rpt.total_receipts]) last_quarterly_date = rpt.filed_doc.reporting_period_end total_funds_raised += rpt.total_receipts # Add funds raised since last quaterly report. total_receipts = getReceiptsTotal(committee_id, "A-1", last_quarterly_date, current_date) receipts_label = "Funds Raised Since {dt:%b} {dt.day}, {dt.year}".format(dt = last_quarterly_date) table_display_data.append([receipts_label, total_receipts]) total_funds_raised += total_receipts # Add a row for the total funds raise. table_display_data.append(["Total Funds Raised", total_funds_raised]) return table_display_data