def GET(self, table, what):
try:
table = table_map[table]
except KeyError:
raise web.notfound
if not r_safeproperty.match(what): raise web.notfound
#if `what` is not there in the `table` (provide available options rather than 404???)
try:
maxnum = float(db.select(table,
what='max(%s) as m' % what,
vars=locals())[0].m)
except:
raise web.notfound
items = db.select(table,
what="*, 100*(%s/$maxnum) as pct" % what,
order='%s desc' % what,
where='%s is not null' % what,
vars=locals()).list()
for item in items:
if table == 'district':
item.id = 'd' + item.name
item.path = '/us/' + item.name.lower()
elif table == 'politician':
item.name = '%s %s (%s-%s)' % (item.firstname, item.lastname,
item.party[0], item.district.split('-')[0])
item.path = '/p/' + item.id
return render.dproperty(items, what)
def zip2dist(zip5, scale_column='population'):
## ARRRG, The census provides the congressional districts down to the tract
# level, but not to the block level. The ZCTA are provided at the block
# level, but NOT at the tract level.
# This would be ok if tracts didn't overlap ZCTAs, but they do. Not sure
# how to solve this problem.
if scale_column=='zip4':
return zip2dist_by_zip4(zip5)
pop_zip = db.select('census_population', what='sum('+scale_column+')',
where="sumlev='ZCTA' and zip_id=$zip5",
vars=locals()).list()
if pop_zip and len(pop_zip)==1:
pop_zip = pop_zip[0].sum
else: print "oops"; return None
# Limit our search to known intersecting districts
dists = db.select('zip4', what='district_id',
where="zip=$zip5", group='district_id',
vars=locals())
intersect_pops = db.query("select a.district_id, b.state_id, SUM(b."+scale_column+") from (SELECT * FROM census_population WHERE sumlev='TRACT' AND district_id != '') as a INNER JOIN (SELECT * FROM census_population WHERE sumlev='BLOCK' AND zip_id=$zip5) as b ON (a.state_id=b.state_id AND a.county_id=b.county_id AND a.tract_id=b.tract_id) group by a.district_id, b.state_id", vars=locals()).list()
# NOTE: This is not the correct behavior, but for now just adjust this to
# give us something that sums to 1.0.
pop_zip2 = sum(map(lambda x: x.sum if x.sum else 0.0, intersect_pops))
print >>sys.stderr, "Pop Zip:",pop_zip, pop_zip2
pop_zip = pop_zip2
ret = {}
for ip in intersect_pops:
print >>sys.stderr, ip.sum, pop_zip
ret['%s-%s' % (ip.state_id, ip.district_id)] = Decimal(ip.sum) / pop_zip if pop_zip else 0.0
return ret
def GET(self, format=None):
i = web.input(address=None)
pzip5 = re.compile(r'\d{5}')
pzip4 = re.compile(r'\d{5}-\d{4}')
pdist = re.compile(r'[a-zA-Z]{2}\-\d{2}')
dists = None
if not i.get('q'):
i.q = i.get('zip')
if i.q:
if pzip4.match(i.q):
zip, plus4 = i.q.split('-')
dists = [x.district_id for x in
db.select('zip4', where='zip=$zip and plus4=$plus4', vars=locals())]
elif pzip5.match(i.q):
try:
dists = zip2rep.zip2dist(i.q, i.address)
except zip2rep.BadAddress:
return render.find_badaddr(i.q, i.address)
if dists:
d_dists = list(schema.District.select(where=web.sqlors('name=', dists)))
out = apipublish.publish(d_dists, format)
if out: return out
if len(dists) == 1:
raise web.seeother('/us/%s' % dists[0].lower())
elif len(dists) == 0:
return render.find_none(i.q)
else:
return render.find_multi(i.q, d_dists)
if pdist.match(i.q):
raise web.seeother('/us/%s' % i.q)
results = se.query(i.q)
reps = schema.Politician.select(where=web.sqlors('id=', results))
if len(reps) > 1:
return render.find_multi_reps(reps, congress_ranges)
else:
try:
rep = reps[0]
web.seeother('/p/%s' % rep.id)
except IndexError:
raise web.notfound()
else:
index = list(schema.District.select(order='name asc'))
for i in index:
i.politician = list(db.select('curr_politician', where='district_id = $i.name', vars=locals()))
out = apipublish.publish(index, format)
if out: return out
return render.districtlist(index)
def GET_signatories(self, pid):
user_email = helpers.get_loggedin_email()
ptitle = db.select('petition', what='title', where='id=$pid', vars=locals())[0].title
signs = db.select(['signatory', 'users'],
what='users.name, users.email, '
'signatory.share_with, signatory.comment',
where='petition_id=$pid AND user_id=users.id',
order='signtime desc',
vars=locals()).list()
return render.signature_list(pid, ptitle, signs, is_author(user_email, pid))
def is_author(email, pid):
if not email: return False
try:
user_id = db.select('users', where='email=$email', what='id', vars=locals())[0].id
owner_id = db.select('petition', where='id=$pid', what='owner_id', vars=locals())[0].owner_id
except:
return False
else:
return user_id == owner_id
def create_or_update(polid, dist):
if not db.select('district', where='name=$dist', vars=locals()):
db.insert('district', seqname=False, name=dist, state_id=dist[:2])
if db.select('politician', where='id=$polid', vars=locals()):
db.update('politician', where='id=$polid', district_id=dist, last_elected_year='2008', vars=locals())
else:
first, last = id.split('_', 1)
first, last = first.title(), last.title()
db.insert('politician', seqname=False, id=polid, firstname=first, lastname=last, last_elected_year='2008', district_id=dist)
def check_user(userName, passwd):
myvar = dict(uName=userName, upwd=passwd)
results = db.select("users", myvar, where="userName = $uName and passwd = $upwd")
if results is None or len(results) == 0:
results = db.select("users", myvar, where="mail = $uName and passwd = $upwd")
if len(results) > 0:
temp = results[0]
videoList = videos.get_videoList(temp["userId"])
return bigUser.BigUser(temp, videoList)
else:
return None
def zip2dist_by_zip4(zip5):
if db.select('state', where='code=$zip5',vars=locals()):
return {zip5:1.0}
dists = db.select('zip4',
what='COUNT(plus4), district_id',
where='zip=$zip5',
group='district_id',
vars=locals()).list()
all_zip4 = sum(map(lambda d: d.count, dists))
ret = {}
for d in dists:
ret[d.district_id] = float(d.count) / float(all_zip4)
return ret
def GET(self, polid, format=None):
limit = 50
page = int(web.input(page=0).page)
#c = schema.lob_contribution.select(where='politician_id=$polid', limit=limit, offset=page*limit, order='amount desc', vars=locals())
a = db.select(['lob_filing', 'lob_contribution'],
what='SUM(amount)',
where="politician_id = $polid AND lob_filing.id = filing_id",
vars=locals())[0].sum
c = db.select(['lob_organization', 'lob_filing', 'lob_contribution', 'lob_person'],
where="politician_id = $polid and lob_filing.id = filing_id and lob_organization.id = org_id and lob_person.id = lobbyist_id",
order='amount desc', limit=limit, offset=page*limit,
vars=locals())
return render.politician_lobby(c, a, limit)
def insert_pac(pac):
pac_id[0] += 1
pa = {'id':pac_id[0]} #@@ stable ids
for z, val in pac.items():
if z in lob_pac: pa[lob_pac[z]] = val
db_pac = db.select('lob_pac', where='LOWER(name)='+web.sqlquote(pa['name'].lower()))
if not db_pac:
db_pac = db.select('lob_pac', where='name ilike '+web.sqlquote('%'+cleanPacName(pa['name'])+'%') )
if not db_pac:
db.insert('lob_pac', seqname=False, **pa)
else:
pa = db_pac[0]
db.insert('lob_pac_filings',seqname=False, pac_id=pa['id'], filing_id=fil['id'])
def load_votesmart():
# Candidates from votesmart
for district, cands in votesmart.candidates():
district=tools.fix_district_name(district)
for pol in cands:
vs_id=pol['candidateId']
wd = get_wd_id(vs_id)
if not wd: continue
polid = wd['watchdog_id']
pol_cand = filter_dict(cand_mapping, pol)
if not db.select('politician',
where='id=$polid', vars=locals()):
db.insert('politician',
seqname=False,
id=polid,
**unidecode(filter_dict(schema.Politician.columns.keys(),
pol_cand)))
else:
# @@ Should probably check that we really want to do this, but
# it apears as though the data has two entries for current
# members (the second having more info filled out).
db.update('politician', where='id=$polid', vars=locals(),
**unidecode(filter_dict(schema.Politician.columns.keys(),
pol_cand)))
if not db.select('congress',
where="politician_id=$polid AND congress_num='-1'",
vars=locals()):
db.insert('congress', seqname=False, congress_num='-1',
politician_id=polid, district_id=district,
party=pol_cand['party'])
# Bios from votesmart
for vs_id, p in votesmart.bios():
pol = p['candidate']
if pol['gender']:
pol['gender']=pol['gender'][0]
if pol['education']:
pol['education'] = pol['education'].replace('\r\n', '\n')
wd = get_wd_id(vs_id)
if not wd: continue
polid = wd['watchdog_id']
pol_people = filter_dict(schema.Politician.columns.keys(),
filter_dict(bios_mapping, pol))
if db.select('politician', where='votesmartid=$vs_id',vars=locals()):
db.update('politician', where='votesmartid=$vs_id',
vars=locals(), **unidecode(pol_people))
def GET(self, n):
res = db.select('pastes', where='id=$n', vars=locals())
db.update('pastes', where='id=$n',vars=locals(), lastview=now_time())
if res:
return render.view(res[0],n)
else:
return web.notfound()
def POST_delete(self, pid):
with db.transaction():
title = db.select('petition', what='title', where='id=$pid', vars=locals())[0].title
db.delete('signatory', where='petition_id=$pid', vars=locals())
db.delete('petition', where='id=$pid', vars=locals())
helpers.set_msg('Petition "%s" deleted' % (title))
raise web.seeother('/')
def sendmail_to_signatory(user, pid):
p = db.select('petition', where='id=$pid', vars=locals())[0]
p.url = 'http//watchdog.net/c/%s' % (pid)
token = auth.get_secret_token(user.email)
msg = render_plain.signatory_mailer(user, p, token)
#@@@ shouldn't this web.utf8 stuff taken care by in web.py?
web.sendmail(web.utf8(config.from_address), web.utf8(user.email), web.utf8(msg.subject.strip()), web.utf8(msg))
def get_contacts(user, by='id'):
if by == 'email':
where = 'uemail=$user'
else:
where = 'user_id=$user'
contacts = db.select('contacts',
what='cname as name, cemail as email, provider',
where=where,
vars=locals()).list()
if by == 'id':
#remove repeated emails due to multiple providers; prefer the one which has name
cdict = {}
for c in contacts:
if c.email not in cdict.keys():
cdict[c.email] = c
elif c.name:
cdict[c.email] = c
contacts = cdict.values()
for c in contacts:
c.name = c.name or c.email.split('@')[0]
contacts.sort(key=lambda x: x.name.lower())
return contacts
def GET(self, district, format=None):
try:
district = district.upper()
d = db.select(['district', 'state', 'politician'],
what=('district.*, '
'state.name as state_name, '
'politician.firstname as pol_firstname, '
'politician.lastname as pol_lastname, '
'politician.id as pol_id, '
'politician.photo_path as pol_photo_path'),
where=('district.name = $district AND '
'district.state = state.code AND '
'politician.district = district.name'),
vars=locals())[0]
except IndexError:
raise web.notfound
out = apipublish.publish({
'uri': 'http://watchdog.net/us/' + district.lower(),
'type': 'District',
'state': apipublish.URI('http://watchdog.net/us/' + d.state.lower()),
'wikipedia almanac': apipublish.URI,
'name voting area_sqmi cook_index poverty_pct median_income '
'est_population est_population_year outline center_lat '
'center_lng zoom_level': apipublish.identity,
}, [d], format)
if out is not False:
return out
if d.district == 0:
d.districtth = 'at-large'
else:
d.districtth = web.nthstr(d.district)
return render.district(d)
def getcontact(pol):
r = db.select('pol_contacts', what='contact, contacttype', where='politician_id=$pol', vars=locals())
if r:
r = r[0]
return r.contact, r.contacttype
else:
return None, None
def pol2dist(pol):
try:
return db.select("curr_politician", what="district_id", where="curr_politician.id=$pol", vars=locals())[
0
].district_id
except KeyError:
return
def load_election_results(d, distname):
(year, votes, vote_pct) = (0,'0','0')
if 'name' not in d:
print "No name for the congress person for: ",distname
return
pname = d['name'].lower()
if 'electionresults' not in d:
print "No election results for %s repsenting %s." % (d['name'],distname)
return
for e in d['electionresults']:
if 'candidate' in e and 'primary' not in e['election'] and \
pname.replace(' ','') in e['candidate'].lower().replace(' ',''):
if int(e['election'][0:4]) > year:
(year,votes) = (int(e['election'][0:4]), e['totalvotes'])
if 'percent' in e: vote_pct = e['percent']
#print year, votes, vote_pct, d['name'], distname
if year:
pol=db.select('politician', what='id',
where="district_id='"+distname+"' AND "+web.sqlors('lastname ilike ',pname.split(' ')),
vars=locals()).list()
if pol and len(pol)==1:
polid=pol[0].id
db.update('politician', where='id=$polid',
n_vote_received=votes.replace(',','').replace('Unopposed','0'),
pct_vote_received=vote_pct.replace('%',''),
last_elected_year=year, vars=locals());
else: print "Couldn't find an id for %s representing %s." % (d['name'], distname)
else: print "Didn't find a recent election for %s representing %s." %(d['name'], distname) #, pformat(d['electionresults'])
def interest_group_ratings(polid):
"Returns the interest group ratings for a politician."
return list(db.select(['interest_group_rating', 'interest_group'],
what='year, interest_group.groupname, rating, longname',
where=('politician_id = $polid '
'AND interest_group.id = interest_group_rating.group_id'),
vars=locals()))
def polname_by_id(pol_id):
try:
p = db.select('politician', what='firstname, middlename, lastname', where='id=$pol_id', vars=locals())[0]
except:
return None
else:
return "%s %s %s" %(p.firstname or '', p.middlename or '', p.lastname or '')
def dist2pols(dist):
if not dist: return []
where = 'curr_politician.district_id=$dist or curr_politician.district_id=$dist[:2]'
try:
return [p.id for p in db.select('curr_politician', what='id', where=where, vars=locals())]
except KeyError:
return []
def fill_user_details(form, fillings=["email", "name", "contact"]):
details = {}
email = helpers.get_loggedin_email() or helpers.get_unverified_email()
if email:
if "email" in fillings:
details["email"] = email
user = db.select("users", where="email=$email", vars=locals())
if user:
user = user[0]
if "name" in fillings:
details["userid"] = user.id
details["prefix"] = user.prefix
details["fname"] = user.fname
details["lname"] = user.lname
if "contact" in fillings:
details["addr1"] = user.addr1
details["addr2"] = user.addr2
details["city"] = user.city
details["zipcode"] = user.zip5
details["zip4"] = user.zip4
details["phone"] = user.phone
details["state"] = user.state
form.fill(**details)
def findPol(raw_name):
name = cleanName(raw_name).replace(',','').split(' ')
name = map(string.lower,filter(lambda x: x, name))
p = db.select('politician', where=web.sqlors('LOWER(lastname)=',name) + ' AND (' + web.sqlors('LOWER(firstname)=',name)+' OR '+web.sqlors('LOWER(nickname)=',name)+')').list()
#print raw_name, "-->", name
if p and len(p) == 1:
return p[0].id
def past_votes(self):
return db.select(
"past_elections",
where="district_id=$self.district_id and politician_id = $self.id",
order="year desc",
vars=locals(),
).list()
def save_signature(i, pid, uid):
where = "petition_id=$pid AND user_id=$uid"
signed = db.select("signatory", where=where, vars=locals())
share_with = (i.get("share_with", "off") == "on" and "N") or "A"
update_user_details(i)
if not signed:
referrer = get_referrer(pid, uid)
signid = db.insert(
"signatory",
user_id=uid,
share_with=share_with,
petition_id=pid,
comment=i.get("comment"),
referrer=referrer,
)
helpers.set_msg("Thanks for your signing! Why don't you tell your friends about it now?")
return signid
else:
db.update(
"signatory",
where="user_id=$uid and petition_id=$pid",
comment=i.get("comment"),
deleted=None,
vars=locals(),
)
helpers.set_msg("Your signature has been changed. Why don't you tell your friends about it now?")
return "old_%s" % signed[0].id
def get_video(videoId):
if videoId is None:
return None
myvar = dict(videoId = videoId)
results = db.select('videos', myvar, where="videoId = $videoId")
if len(results)==0:
return None
return bigVideo.BigVideo(results[0])
def index(self):
def format(name):
names = name.lower().split(', ')
if len(names) > 1:
return '_'.join(names[1].split() + [names[0]])
return urllib.quote(name)
return ('/contrib/%s/%s' % (c.zip, format(c.name)) \
for c in db.select('contribution', what='zip, name'))
def compose_msg(polid, msg):
#@@ compose msg here
p = db.select('politician', where='id=$polid',
what='firstname, middlename, lastname, district_id', vars=locals())[0]
pol_name = "%s %s %s" % (p.firstname or '', p.middlename or '', p.lastname or '')
rep_or_sen = 'Sen.' if len(p.district_id) == 2 else 'Rep.'
full_msg = 'Dear %s %s,\n%s' % (rep_or_sen, pol_name, msg)
return full_msg
def calculate_per_capita():
""" """
print "Pre getting all populations per district..."
pop_dist = {}
for d in schema.District.select(where='est_population is not null'):
pop_dist[d.name] = d.est_population
print "Calculate the per-capita impact of each earmark..."
pc = {}
for e in db.select('earmark', what='final_amt, id', order='id asc'):
done_states = set()
amount = float(e.final_amt or 0)
pop = 0
sponsors = db.query("select district_id, state_id, id from politician, district, earmark_sponsor where politician.district_id = district.name and earmark_sponsor.politician_id = politician.id and earmark_id=$e.id",vars=locals()).list()
if not sponsors: continue
# Get the population for each district sponsoring
for p in sponsors:
if p.district_id != p.state_id:
done_states.add(p.state_id)
pop += pop_dist.get(p.district_id, 0)
# Get the population for state sponsoring unless a district has from
# within state also sponsors.
for p in sponsors:
if p.district_id == p.state_id:
if p.state_id in done_states: continue
done_states.add(p.state_id)
pop += pop_dist.get(p.district_id, 0)
if not pop:
pc[e.id] = 0.0
else:
pc[e.id] = amount / pop
#print e.id, pc[e.id], amount, pop
print "Aggregating per-capita impact to districts..."
for d in schema.District.select():
if d.name == d.state_id: continue # Don't set for states.
congress_people = set()
senators = db.select('curr_politician', where='district_id = $d.state.code', vars=locals())
if senators:
congress_people.update(p.id for p in senators)
politician = db.select('curr_politician', where='district_id = $d.name', vars=locals())
if politician:
congress_people.update(p.id for p in politician)
ems = db.select('earmark_sponsor', what='distinct(earmark_id)', where=web.sqlors('politician_id=',congress_people))
empc = sum(map(lambda x: pc.get(x, 0.0), set(e.earmark_id for e in ems)))
#print d.name, empc
db.update('district',where='name=$d.name',earmark_per_capita=empc, vars=locals())
def getcontact(pol):
p = db.select('pol_contacts', where='politician_id=$pol', vars=locals())
return p[0] if p else {}
def get_user_by_email(email):
try:
return db.select('users', where='email=$email', vars=locals())[0]
except IndexError:
return
def _fill():
for pol in db.select('politician', what='id, govtrackid, opensecretsid'):
_govtrackcache[pol.govtrackid] = str(pol.id)
_opensecretscache[pol.opensecretsid] = str(pol.id)
def is_verified(email):
verified = db.select(
'users',
where='email=$email and (verified=True or password is not null)',
vars=locals())
return bool(verified)
def get_user_by_id(uid):
try:
return db.select('users', where='id=$uid', vars=locals())[0]
except IndexError:
return
