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