def from_web(item, cities):
if not item['data0']:
return None
obj = Submission()
obj.name = PersonName({
'last_name': item['data0'],
'first_name': item['data1'],
'middle_name': item['data2'],
'name_suffix': item['data3']
})
try:
obj.address = Address({
'address': item['data4'],
'city': item['data5'],
'zipcode': item['data6']
})
except Exception as ex:
if str(ex).startswith('Unable to parse address'):
obj.address = Address({
'address': "",
'city': item['data5'],
'zipcode': item['data6']
})
if obj.address.city not in cities:
for city in cities:
if city['zipcode'] == obj.address.zipcode:
obj.address.city = city['name']
break
return obj
def __str__(self):
return PersonName.display_name({
'last': self.last_name,
'first': self.first_name,
'middle': self.middle_name,
'suffix': self.name_suffix
})
def lookup(dao, params):
pn = PersonName(params)
if 'address' in params and params['address']:
addr = Address(params)
matches = Voter.voters_by_name_and_address(dao, addr, pn)
if matches:
return matches
return Voter.__voters_by_name(dao, pn)
def __init__(self, d=None):
self.id = None
self.name = None
self.birth_year = None
self.gender = ''
self.info = None
self.address = None
self.voter_id = None
self.reg_date = ''
self.precinct_id = None
if d:
for attr in self.__dict__:
if attr in d:
setattr(self, attr, d[attr])
self.name = PersonName(d)
self.address = Address(d)
self.info = ContactInfo(d)
def test_get_best_voter(self):
addr = Address({'address': '3000 Newcastle Rd'})
pn = PersonName({'last_name': 'weinblatt', 'first_name': 'howard'})
contact = Contact()
contact.name = pn
contact.address = addr
voter = Contact.get_best_voter_rec(self.dao, contact)
pass
def __init__(self, d=None):
self.id = None
self.name = None
self.address = None
self.birth_year = None
self.gender = None
self.voter_id = None
self.precinct_id = None
self.reg_date = None
self.perm_abs = None
self.status = None
self.uocava = None
self.ballot = None
if d:
for attr in self.__dict__:
if attr in d:
setattr(self, attr, d[attr])
self.name = PersonName(d)
self.address = Address(d)
def email_dups():
dao = Dao(stateful=True)
# cities = Turf.get_city_names(dao)
dups = Contact.get_email_dups(dao)
dao.close()
for dup in dups:
dup['name'] = str(PersonName(dup))
dup['address'] = str(Address(dup))
return jsonify(dups)
def contact_matches():
contact = Contact(json.loads(request.form['params']))
dao = Dao(stateful=True)
try:
matches = contact.get_matches(dao)
for match in matches:
match['name'] = str(PersonName(match))
match['address'] = str(Address(match))
return jsonify(matches=matches)
except Exception as ex:
return jsonify(error=str(ex))
def test_init(self):
d = {
'last_name': 'Marx',
'first_name': 'Julius',
'nickname': 'Groucho',
'name_suffix': 'JR',
'alias': 'Capt. Spaulding, ESQ'
}
pn = PersonName(d)
self.assertEqual('MARX, JULIUS, JR', str(pn))
self.assertEqual('GROUCHO', pn.nickname)
self.assertEqual('Capt. Spaulding, ESQ', pn.alias)
self.assertEqual('MRKS', pn.name_metaphone)
def name_duplicates():
if request.method == 'GET':
dao = Dao(stateful=True)
cities = turf_dao.get_city_names(dao)
dups = con_dao.get_name_dups(dao)
dao.close()
for dup in dups:
dup['name'] = str(PersonName(dup))
dup['address'] = str(Address(dup))
return render_template('contacts/dups.html',
title='Name Duplicates',
dups=dups,
cities=cities)
def from_csv(row, cities, with_contact):
obj = Submission()
if with_contact:
obj.name = PersonName({
'last_name': row[6],
'first_name': row[7],
'middle_name': row[8],
'name_suffix': row[9]
})
obj.address = Address({
'address': row[3],
'city': row[4],
'zip': row[5]
})
obj.contact = ContactInfo({
'email': row[0],
'phone1': row[1],
'phone2': row[2]
})
else:
obj.name = PersonName({
'last_name': row[0],
'first_name': row[1],
'middle_name': row[2],
'name_suffix': row[3]
})
obj.address = Address({
'address': row[4],
'city': row[5],
'zip': row[6]
})
if obj.address.city not in cities:
obj.set_city_by_zip(cities)
return obj
def get_voter_rex(c):
sql = ("SELECT last_name, first_name, middle_name "
"FROM voters "
"WHERE last_name_meta=? "
"AND last_name LIKE ? "
"AND first_name_meta LIKE ? "
"AND first_name LIKE ?;")
vals = (
c.name.last_meta,
c.name.last[0] + '%',
c.name.first_meta + '%',
c.name.first[0] + '%'
)
rex = dao.execute(sql, vals)
return [PersonName(rec) for rec in rex] if rex else []
def __init__(self, d=None):
self.id = None
self.name = None
self.birth_year = None
self.gender = ''
self.info = None
self.address = None
self.voter_id = None
self.reg_date = ''
self.bst_id = None
if d:
for attr in self.__dict__:
if attr in d:
setattr(self, attr, d[attr])
self.name = PersonName(d)
self.address = Address(d)
self.info = ContactInfo(d)
def __init__(self, d=None):
self.id = None
self.name = None
self.address = None
self.birth_year = None
self.gender = None
self.voter_id = None
self.reg_date = None
self.perm_abs = None
self.status = None
self.uocava = None
self.ballot = None
if d:
for attr in self.__dict__:
if attr in d:
setattr(self, attr, d[attr])
self.name = PersonName(d)
self.address = Address(d)
def fuzzy_lookup(params):
from models.person_name import PersonName
from models.address import Address
pn = PersonName(params)
if 'address' in params and params['address'] != '':
addr = Address(params)
matches = Voter.by_fuzzy_name_and_address(pn, addr)
if matches:
return matches
candidates = Voter.by_fuzzy_name(pn)
candidates_by_name = {
str(candidate): candidate
for candidate in candidates
}
names = [str(candidate) for candidate in candidates]
matches = MatchLib.get_best_partials(str(pn), names, 75)
return [candidates_by_name[match] for match in matches]
class Contact(object):
def __init__(self, d=None):
self.id = None
self.name = None
self.birth_year = None
self.gender = ''
self.info = None
self.address = None
self.voter_id = None
self.reg_date = ''
self.bst_id = None
if d:
for attr in self.__dict__:
if attr in d:
setattr(self, attr, d[attr])
self.name = PersonName(d)
self.address = Address(d)
self.info = ContactInfo(d)
def __str__(self):
return str(self.name)
def serialize(self):
return {
'name': self.name.serialize(),
'whole_name': str(self.name),
'birth_year': self.birth_year,
'gender': self.gender,
'contact': self.info.serialize(),
'address': self.address.serialize(),
'voter_id': self.voter_id,
'reg_date': self.reg_date,
'id': self.id,
'bst_id': self.bst_id
}
def get_values(self):
return (
self.name.last,
self.name.first,
self.name.middle,
self.name.suffix,
self.name.nickname,
self.name.last_meta,
self.name.first_meta,
self.name.nickname_meta,
self.birth_year,
self.gender,
self.info.email,
self.info.phone1,
self.info.phone2,
self.address.house_number,
self.address.pre_direction,
self.address.street_name,
self.address.street_type,
self.address.suf_direction,
self.address.unit,
self.address.metaphone,
self.address.city,
self.address.zipcode,
self.address.precinct_id,
self.voter_id,
self.reg_date,
self.bst_id
)
def copy_voter(self, voter):
if not self.name.nickname:
self.name.nickname = self.name.first
self.name.last = voter.name.last
self.name.first = voter.name.first
self.name.middle = voter.name.middle
self.name.suffix = voter.name.suffix
self.address.house_number = voter.address.house_number
self.address.pre_direction = voter.address.pre_direction
self.address.street_name = voter.address.street_name
self.address.street_type = voter.address.street_type
self.address.suf_direction = voter.address.suf_direction
self.address.unit = voter.address.unit
self.address.city = voter.address.city
self.address.zipcode = voter.address.zipcode
self.address.precinct_id = voter.address.precinct_id
self.gender = voter.gender
self.birth_year = voter.birth_year
self.reg_date = voter.reg_date
self.voter_id = voter.voter_id
def get_name_only_matches(self, dao):
rex = PersonName.person_by_name_only(dao, 'contacts', self.name)
return [Contact[rec] for rec in rex
if rec['id'] != self.id] if rex else []
class Voter(object):
def __init__(self, d=None):
self.id = None
self.name = None
self.address = None
self.birth_year = None
self.gender = None
self.voter_id = None
self.reg_date = None
self.perm_abs = None
self.status = None
self.uocava = None
self.ballot = None
if d:
for attr in self.__dict__:
if attr in d:
setattr(self, attr, d[attr])
self.name = PersonName(d)
self.address = Address(d)
def __str__(self):
return str(self.name)
def serialize(self):
d = {
'voter_id': self.voter_id,
'name': self.name.serialize(),
'address': self.address.serialize(),
'precinct_id': self.address.precinct_id,
'birth_year': self.birth_year,
'gender': self.gender,
'reg_date': self.reg_date,
'perm_abs': self.perm_abs if self.perm_abs else 'N',
'status': self.status,
'uocava': self.uocava
}
if 'score' in self.__dict__:
d['score'] = self.__dict__['score']
return d
def get_values(self):
return (
self.name.last,
self.name.first,
self.name.middle,
self.name.suffix,
self.name.last_meta,
self.name.first_meta,
self.birth_year,
self.gender,
self.address.house_number,
self.address.pre_direction,
self.address.street_name,
self.address.street_type,
self.address.suf_direction,
self.address.unit,
self.address.metaphone,
self.address.city,
self.address.zipcode,
self.address.precinct_id,
self.voter_id,
self.reg_date,
self.perm_abs,
self.status,
self.uocava
)
class Contact(object):
db_cols = [
'last_name', 'first_name', 'middle_name', 'name_suffix', 'nickname',
'last_name_meta', 'first_name_meta', 'nickname_meta', 'birth_year',
'gender', 'email', 'phone1', 'phone2', 'house_number', 'pre_direction',
'street_name', 'street_type', 'suf_direction', 'unit',
'street_name_meta', 'city', 'zipcode', 'precinct_id', 'voter_id',
'reg_date'
]
def __init__(self, d=None):
self.id = None
self.name = None
self.birth_year = None
self.gender = ''
self.info = None
self.address = None
self.voter_id = None
self.reg_date = ''
self.precinct_id = None
if d:
for attr in self.__dict__:
if attr in d:
setattr(self, attr, d[attr])
self.name = PersonName(d)
self.address = Address(d)
self.info = ContactInfo(d)
def __str__(self):
return str(self.name)
def serialize(self):
return {
'name': self.name.serialize(),
'whole_name': str(self.name),
'birth_year': self.birth_year,
'gender': self.gender,
'contact': self.info.serialize(),
'address': self.address.serialize(),
'voter_id': self.voter_id,
'reg_date': self.reg_date,
'id': self.id,
}
def get_values(self):
return (self.name.last, self.name.first, self.name.middle,
self.name.suffix, self.name.nickname, self.name.last_meta,
self.name.first_meta, self.name.nickname_meta, self.birth_year,
self.gender, self.info.email, self.info.phone1,
self.info.phone2, self.address.house_number,
self.address.pre_direction, self.address.street_name,
self.address.street_type, self.address.suf_direction,
self.address.unit, self.address.metaphone, self.address.city,
self.address.zipcode, self.address.precinct_id, self.voter_id,
self.reg_date)
@staticmethod
@get_dao
def get_all(dao):
sql = ("SELECT * FROM contacts "
"ORDER BY last_name, first_name, middle_name")
rex = dao.execute(sql)
return [Contact(rec) for rec in rex]
def get_matches(self, dao=None):
if not dao:
dao = Dao(stateful=True)
if self.info and (self.info.phone1 or self.info.phone2):
matches = self.get_phone_matches(dao)
if matches:
return matches
if self.info and self.info.email:
matches = self.get_email_matches(dao)
if matches:
return matches
if self.address:
matches = self.get_name_addr_matches(dao)
if matches:
return matches
return self.get_name_only_matches(dao)
def get_email_matches(self, dao):
sql = ("SELECT * FROM contacts " "WHERE email LIKE ? " "AND id<>?;")
vals = (self.info.email[0], self.id)
rex = dao.execute(sql, vals)
if not rex:
return []
candidates = {rec['id']: rec['email'] for rec in rex}
choices = self.choose_email(candidates.values())
if not choices:
return []
result = []
for choice in choices:
for rec in rex:
if rec['email'] == choice:
result.append(Contact(rec))
return result
def choose_email(self, candidates):
target = self.info.email.split('@')
emails = [c.split('@') for c in candidates]
best_domain = MatchLib.get_best_match(
target[1], set([email[1] for email in emails]))[0]
usernames = [
email[0] for email in emails
if email[1] in [target[1], best_domain]
]
matches = MatchLib.get_best_matches(target[0], usernames, 80)
return [match[0] + '@' + best_domain
for match in matches] if matches else []
def get_phone_matches(self, dao):
if not self.info:
return []
if not self.info.phone1 and not self.info.phone2:
return []
rex = []
sql = ("SELECT * FROM contacts "
"WHERE (phone1=? OR phone2=?) "
"AND id<>?;")
if self.info.phone1:
vals = (self.info.phone1, self.info.phone1, self.id)
rex = dao.execute(sql, vals)
if self.info.phone2:
vals = (self.info.phone2, self.info.phone2, self.id)
rex += dao.execute(sql, vals)
d = {}
for rec in rex:
if rec['id'] in d:
continue
d[rec['id']] = rec
return list(d.values())
def get_name_addr_matches(self, dao):
rex = PersonName.person_by_name_and_address(dao, 'contacts',
self.address, self.name)
return [Contact[rec] for rec in rex
if rec['id'] != self.id] if rex else []
def get_name_only_matches(self, dao):
rex = PersonName.person_by_name_only(dao, 'contacts', self.name)
return [Contact[rec] for rec in rex
if rec['id'] != self.id] if rex else []
def add(self, dao):
sql = ("INSERT INTO contacts (%s) VALUES (%s)") % (','.join(
self.db_cols), dao.get_param_str(self.db_cols))
vals = self.get_values()
return dao.execute(sql, vals)
def update(self, dao):
sql = "UPDATE contacts SET %s WHERE id=?;" % (
'=?,'.join(self.db_cols) + '=?', )
vals = self.get_values() + (self.id, )
dao.execute(sql, vals)
@staticmethod
def add_many(data, dao=None):
contacts = [Contact(d) for d in data]
values = [contact.get_values() for contact in contacts]
if not dao:
dao = Dao()
dao.add_many('contacts', Contact.db_cols, values)
@staticmethod
@get_dao
def update_many(dao, contacts):
values = [
contact.get_values() + (contact.id, ) for contact in contacts
]
dao.update_many('contacts', Contact.db_cols, values)
@staticmethod
@get_dao
def drop_many(dao, ids):
dao.drop_many('contacts', ids)
@staticmethod
def get_best_voter_rec(dao, contact):
from models.voter import Voter
candidates = Voter.voters_by_name_and_address(dao, contact.address,
contact.name)
if not candidates:
return None
streets = set(
[candidate.address.street_name for candidate in candidates])
best_street = MatchLib.get_best_match(contact.address.street_name,
streets, 85)
if not best_street:
return None
candidates = [
candidate for candidate in candidates
if candidate.address.street_name == best_street
]
best_name = MatchLib.get_best_match(
str(contact), [str(candidate.name) for candidate in candidates],
85)
if not best_name:
return None
return [
candidate for candidate in candidates
if str(candidate.name) == best_name
][0]
@staticmethod
def get_best_turf(dao, contact):
turfs = Address.get_turf(dao, contact.address)
if not turfs:
return None
if len(set([turf['precinct_id'] for turf in turfs])) == 1:
return turfs[0]
d = {(t['pre_direction'] + ' ' + t['street_name'] + ' ' +
t['street_type'] + ' ' + t['suf_direction']).strip(): t
for t in turfs}
match = MatchLib.get_best_match(str(contact.address), d.keys(), 85)
return d[match] if match else None
@staticmethod
def synchronize(dao):
# from models.dao import Dao
from models.voter import Voter
# dao = Dao(stateful=True)
contacts = Contact.get_all(dao)
problems = {'reg': [], 'name': [], 'vrec': [], 'addr': [], 'pct': []}
for contact in contacts:
if not contact.voter_id:
problems['reg'].append(contact)
continue
voter = Voter.get_one(dao, contact.voter_id)
if not voter:
problems['vrec'].append([
contact.id,
str(contact.name),
str(contact.address), contact.address.city,
contact.address.zipcode, contact.address.precinct_id, None
])
continue
if str(contact.name) != str(voter.name):
problems['name'].append((str(contact.name), str(voter.name)))
continue
if str(contact.address) != str(voter.address):
problems['addr'].append(contact)
continue
if contact.address.precinct_id != voter.address.precinct_id:
problems['pct'].append(contact)
# contact.copy_voter(voter)
# contact.update(dao)
return problems
def copy_voter(self, voter):
if not self.name.nickname:
self.name.nickname = self.name.first
self.name.last = voter.name.last
self.name.first = voter.name.first
self.name.middle = voter.name.middle
self.name.suffix = voter.name.suffix
self.address.house_number = voter.address.house_number
self.address.pre_direction = voter.address.pre_direction
self.address.street_name = voter.address.street_name
self.address.street_type = voter.address.street_type
self.address.suf_direction = voter.address.suf_direction
self.address.unit = voter.address.unit
self.address.city = voter.address.city
self.address.zipcode = voter.address.zipcode
self.address.precinct_id = voter.address.precinct_id
self.gender = voter.gender
self.birth_year = voter.birth_year
self.reg_date = voter.reg_date
self.voter_id = voter.voter_id
@staticmethod
def assign_precinct(contacts, dao=None):
if not dao:
dao = Dao(stateful=True)
updates = []
for contact in contacts:
# get voter rex by name + address
voter = Contact.get_best_voter_rec(dao, contact)
if voter:
nn = (contact.name.nickname, contact.name.nickname_meta)
contact.name = voter.name
contact.nickname = nn[0]
contact.nickname_meta = nn[1]
contact.address = voter.address
contact.birth_year = voter.birth_year
contact.gender = voter.gender
contact.voter_id = voter.voter_id
contact.reg_date = voter.reg_date
updates.append(contact)
continue
turf = Contact.get_best_turf(dao, contact)
if turf:
contact.address.pre_direction = turf['pre_direction']
contact.address.street_name = turf['street_name']
contact.address.street_type = turf['street_type']
contact.address.suf_direction = turf['suf_direction']
contact.address.precinct_id = turf['precinct_id']
contact.address.city = turf['city']
contact.address.zipcode = turf['zipcode']
updates.append(contact)
continue
# get voter rex by name only
# Contact.update_many(dao, updates)
dao.close()
return len(updates)
@staticmethod
def get_with_missing_precinct(dao=None):
if not dao:
dao = Dao()
sql = ("SELECT * FROM contacts "
"WHERE precinct_id is null "
"ORDER BY last_name, first_name, middle_name;")
rex = dao.execute(sql)
return rex
# return [Contact(rec) for rec in rex] if rex else []
@staticmethod
@get_dao
def get_email_dups(dao):
sql = ("SELECT * "
"FROM contacts "
"WHERE email IN "
"(SELECT email "
"FROM contacts "
"WHERE email <> '' "
"GROUP BY email HAVING COUNT(email) > 1) "
"ORDER BY last_name, first_name, middle_name;")
return dao.execute(sql)
@staticmethod
@get_dao
def get_phone_dups(dao):
p_clause_1 = "(SELECT phone1 FROM contacts " \
"WHERE phone1 <> '' " \
"GROUP BY phone1 HAVING COUNT(phone1) > 1) "
p_clause_2 = "(SELECT phone2 FROM contacts " \
"WHERE phone2 <> '' " \
"GROUP BY phone2 HAVING COUNT(phone2) > 1) "
sql = ("SELECT * FROM contacts "
"WHERE phone1 IN %s "
"OR phone1 IN %s "
"OR phone2 IN %s "
"OR phone2 IN %s "
"ORDER BY last_name, first_name, middle_name;") % (
p_clause_1, p_clause_2, p_clause_1, p_clause_2)
return dao.execute(sql)
@staticmethod
@get_dao
def get_name_addr_dups(dao):
sql = ("SELECT * FROM contacts "
"INNER JOIN "
"(SELECT last_name_meta, street_name_meta FROM contacts "
"GROUP BY (last_name_meta || ':' || street_name_meta) "
"HAVING count(id) > 1) dup "
"ON contacts.last_name_meta=dup.last_name_meta "
"WHERE contacts.street_name_meta <> '' "
"ORDER BY last_name, first_name, middle_name;")
return dao.execute(sql)
@staticmethod
@get_dao
def get_name_dups(dao):
sql = (
"SELECT contacts.* FROM contacts "
"JOIN "
"(SELECT last_name_meta, last_name, first_name_meta FROM contacts "
"GROUP BY (last_name_meta || ':' || first_name_meta) "
"HAVING count(id) > 1) dup "
"ON contacts.last_name_meta=dup.last_name_meta "
"AND substr(contacts.last_name,1,1)=substr(dup.last_name,1,1) "
"AND contacts.first_name_meta LIKE (dup.first_name_meta || '%') "
"ORDER BY last_name, first_name, middle_name;")
return dao.execute(sql)
@staticmethod
@get_dao
def get_activists(dao):
sql = "SELECT * FROM contacts WHERE active=1 ORDER BY last_name, first_name, middle_name;"
rex = dao.execute(sql)
return [Contact(rec) for rec in rex] if rex else []
@staticmethod
@get_dao
def get_by_precinct(dao, precinct_id):
sql = ("SELECT * FROM contacts "
"WHERE precinct_id=? "
"AND active=1 "
"ORDER BY last_name, first_name, middle_name;")
vals = (precinct_id, )
rex = dao.execute(sql, vals)
return [Contact(rec) for rec in rex] if rex else []
@staticmethod
@get_dao
def get_by_precinct_list(dao, precinct_list):
sql = ("SELECT * FROM contacts "
"WHERE precinct_id IN (%s) "
"AND active=1 "
"ORDER BY last_name, first_name, middle_name;") % (
dao.get_param_str(precinct_list))
rex = dao.execute(sql, precinct_list)
return [Contact(rec) for rec in rex] if rex else []
@staticmethod
def get_by_group(dao, group_id):
sql = ("SELECT * FROM contacts "
"WHERE id IN "
"(SELECT contact_id "
"FROM group_members "
"WHERE group_id=?)")
rex = dao.execute(sql, (group_id, ))
return [Contact(rec) for rec in rex] if rex else []
class Voter(object):
# attrs = [
# 'id', 'name', 'address', 'turf',
# 'birth_year', 'gender'
# ]
colnames = [
'Last Name', 'first Name', 'Middle', 'Suffix', 'Address', 'City',
'Zip', 'Gender', 'Birth Year', 'Party', 'Voter ID', 'Reg Date',
'Perm Abs', 'Status', 'UOCAVA'
]
def __init__(self, d=None):
self.id = None
self.name = None
self.address = None
self.birth_year = None
self.gender = None
self.voter_id = None
self.precinct_id = None
self.reg_date = None
self.perm_abs = None
self.status = None
self.uocava = None
self.ballot = None
if d:
for attr in self.__dict__:
if attr in d:
setattr(self, attr, d[attr])
self.name = PersonName(d)
self.address = Address(d)
def __str__(self):
return str(self.name)
def serialize(self):
d = {
'voter_id': self.voter_id,
'name': self.name.serialize(),
'address': self.address.serialize(),
'birth_year': self.birth_year,
'gender': self.gender,
'reg_date': self.reg_date,
'perm_abs': self.perm_abs if self.perm_abs else 'N',
'status': self.status,
'uocava': self.uocava
}
if 'score' in self.__dict__:
d['score'] = self.__dict__['score']
return d
@staticmethod
def voters_by_name_and_address(dao, addr, pn):
sql = ("SELECT * FROM voters "
"WHERE street_name_meta = ? "
"AND street_name LIKE ? "
"AND house_number BETWEEN ? AND ? "
"AND last_name_meta = ? "
"AND last_name LIKE ?;")
vals = (addr.metaphone, addr.street_name[0] + '%', addr.block[0],
addr.block[1], pn.last_meta, pn.last[0] + '%')
rex = dao.execute(sql, vals)
return [Voter(rec) for rec in rex] if rex else []
@staticmethod
def lookup(dao, params):
pn = PersonName(params)
if 'address' in params and params['address']:
addr = Address(params)
matches = Voter.voters_by_name_and_address(dao, addr, pn)
if matches:
return matches
return Voter.__voters_by_name(dao, pn)
@staticmethod
def __voters_by_location(dao, addr, letter):
sql = ("SELECT * FROM voters "
"WHERE street_name_meta LIKE %s "
"AND street_name LIKE %s "
"AND house_number BETWEEN %s AND %s "
"AND last_name LIKE %s;")
vals = (addr.metaphone + '%', addr.street_name[0] + '%', addr.block[0],
addr.block[1], letter + '%')
rex = dao.execute(sql, vals)
return [Voter(rec) for rec in rex] if rex else []
@staticmethod
def __voters_by_name(dao, pn):
candidates = Voter.__get_candidates_by_name(dao, pn)
candidates_by_name = {
str(candidate.name): candidate
for candidate in candidates
}
names = [str(candidate.name) for candidate in candidates]
matches = MatchLib.get_best_partials(str(pn), names, 75)
return [candidates_by_name[match] for match in matches]
@staticmethod
def __get_candidates_by_name(dao, pn):
sql = ("SELECT * "
"FROM voters "
"WHERE last_name_meta=? "
"AND last_name LIKE ? "
"AND first_name LIKE ?")
vals = [pn.last_meta, pn.last[0] + '%', pn.first[0] + '%']
rex = dao.execute(sql, vals)
return [Voter(rec) for rec in rex] if rex else []
@staticmethod
def get_one(dao, voter_id):
sql = "SELECT * FROM voters WHERE voter_id=?;"
vals = (voter_id, )
rex = dao.execute(sql, vals)
if not rex:
return None
return Voter(rex[0])
@staticmethod
def get_by_name(dao, pn):
sql = ("SELECT * FROM voters "
"WHERE last_name=? "
"AND first_name=? ")
vals = [pn.last, pn.first]
if pn.middle:
sql += "AND middle_name=? "
vals.append(pn.middle)
if pn.suffix:
sql += "AND name_suffix=?"
vals.append(pn.suffix)
rex = dao.execute(sql, vals)
return [Voter(rec) for rec in rex]
@staticmethod
def get_voter(dao, voter_id):
from models.election import Election
from models.voter_history import VoterHistory
sql = "SELECT * FROM voters WHERE voter_id=?;"
vals = (voter_id, )
rex = dao.execute(sql, vals)
if not rex:
return None
voter_rec = rex[0]
elections = Election.get(dao)
election_codes = [election['code'] for election in elections]
voter_elections = VoterHistory.get_for_voter(dao,
voter_rec['voter_id'],
election_codes)
for election in elections:
code = 'N'
if election['code'] in voter_elections:
code = voter_elections[election['code']]['ballot'] \
if voter_elections[election['code']]['ballot'] else 'Y'
if voter_elections[election['code']]['absentee_flag'] == 'Y':
code += 'A'
voter_rec[election['date']] = code
return voter_rec
@staticmethod
def batch_lookup(submissions):
from models.street_index import StreetIndex
from dao.dao import Dao
dao = Dao(stateful=True)
for submission in submissions:
# print(str(submission.name))
street = '%s %s' % (submission.address.street_name,
submission.address.street_type)
if submission.address.metaphone:
voters = Voter.__get_by_location(dao, submission)
if voters:
streets = set(
[voter.address.street_name for voter in voters])
matches = MatchLib.get_best_partials(street, streets, 85)
voters = [
voter for voter in voters
if voter.address.street_name in matches
]
voters_by_name = {
str(voter.name): voter
for voter in voters
}
names = [str(voter.name) for voter in voters]
matches = MatchLib.get_best_partials(
str(submission.name), names, 65)
if matches:
submission.matches = [
voters_by_name[match] for match in matches
]
top_score = 0
for match in submission.matches:
Voter.__set_match_score(submission, match)
if match.score > top_score:
top_score = match.score
if top_score < 65:
voters = []
else:
continue
voters = Voter.__get_by_name(dao, submission)
if voters:
if submission.address.street_name:
streets = set(
[voter.address.street_name for voter in voters])
matches = MatchLib.get_best_partials(street, streets, 85)
submission.matches = [
voter for voter in voters
if voter.address.street_name in matches
]
else:
submission.matches = voters
if not submission.matches:
if StreetIndex.is_valid_address(submission.address):
submission.matches = Voter.__get_household(dao, submission)
for match in submission.matches:
Voter.__set_match_score(submission, match)
dao.close()
@staticmethod
def __get_by_location(dao, submission):
sql = ("SELECT * FROM voters "
"WHERE street_name_meta LIKE ? "
"AND street_name LIKE ? "
"AND house_number BETWEEN ? AND ? "
"AND last_name LIKE ?;")
vals = (submission.address.metaphone + '%',
submission.address.street_name[0] + '%',
submission.address.block[0], submission.address.block[1],
submission.name.last[0] + '%')
rex = dao.execute(sql, vals)
return [Voter(rec) for rec in rex] if rex else []
@staticmethod
def __get_by_name(dao, submission):
candidates = Voter.__get_candidates_name_only(dao, submission)
candidates_by_name = {
str(candidate.name): candidate
for candidate in candidates
}
names = [str(candidate.name) for candidate in candidates]
matches = MatchLib.get_best_partials(str(submission.name), names, 75)
return [candidates_by_name[match] for match in matches]
@staticmethod
def __get_candidates_name_only(dao, submission):
pn = submission.name
addr = submission.address
sql = ("SELECT * "
"FROM voters "
"WHERE last_name_meta=? "
"AND last_name LIKE ? "
"AND first_name_meta=? "
"AND first_name LIKE ?")
vals = [
MatchLib.get_single(pn.last), pn.last[0] + '%',
MatchLib.get_single(pn.first), pn.first[0] + '%'
]
if addr.zipcode:
sql += " AND zipcode LIKE ?"
vals.append(addr.zipcode[0:4] + '%')
elif addr.city:
sql += " AND city=?"
vals.append(addr.city)
rex = dao.execute(sql, vals)
return [Voter(rec) for rec in rex] if rex else []
@staticmethod
def __get_household(dao, submission):
if not submission.address.metaphone:
return []
sql = ("SELECT * "
"FROM voters "
"WHERE street_name_meta LIKE ? "
"AND street_name LIKE ? "
"AND house_number=? "
"ORDER BY last_name, first_name, middle_name;")
vals = [
submission.address.metaphone + '%',
submission.address.street[0] + '%', submission.address.house_number
]
rex = dao.execute(sql, vals)
return [Voter(rec) for rec in rex] if rex else []
@staticmethod
def __set_match_score(submission, match):
from fuzzywuzzy import fuzz
possible = 100
score = fuzz.ratio(str(submission.name), str(match.name))
if score >= 50:
if submission.address.street_name:
possible += 100
score += fuzz.ratio(str(submission.address),
str(match.address))
if submission.address.city:
possible += 100
score += fuzz.ratio(submission.address.city,
match.address.city)
if submission.address.zipcode:
possible += 100
score += fuzz.ratio(submission.address.zipcode,
match.address.zipcode)
match.score = int(score / possible * 100)
@staticmethod
def get_by_block(dao, block, elections=None):
sql = ("SELECT * "
"FROM voters "
"WHERE precinct_id=? "
"AND street_name=? "
"AND street_type=? ")
vals = [
block['precinct_id'], block['street_name'], block['street_type']
]
if block['low_addr']:
sql += "AND house_number BETWEEN ? AND ? "
vals += [block['low_addr'], block['high_addr']]
if block['odd_even']:
if block['odd_even'] == 'O':
sql += "AND (house_number % 2)=1 "
elif block['odd_even'] == 'E':
sql += "AND (house_number % 2)=0 "
sql += "ORDER BY house_number;"
voters = dao.execute(sql, vals)
if not voters:
return []
if elections:
from models.voter_history import VoterHistory
election_codes = [election['code'] for election in elections]
for voter in voters:
voter_elections = VoterHistory.get_for_voter(
dao, voter['voter_id'], election_codes)
for election in elections:
code = 'N'
if election['code'] in voter_elections:
code = voter_elections[election['code']]['ballot'] \
if voter_elections[election['code']]['ballot'] else 'Y'
if voter_elections[
election['code']]['absentee_flag'] == 'Y':
code += 'A'
voter[election['date']] = code
return voters
@staticmethod
def get_by_precinct(precinct_id):
flds = Voter.__fldnames + Voter.__hx_fldnames
sql = ("SELECT flds FROM voters AS v "
"JOIN voter_history AS h "
"ON v.voter_id=h.voter_id "
"WHERE v.precinct_id=%s "
"ORDER BY street_name, street_type, house_number;") % (
','.join(flds))
vals = (precinct_id, )
dao = MySqlDao()
return dao.execute(sql, vals)
__fldnames = [
'v.id AS id', 'v.last_name AS last_name', 'v.first_name AS first_name',
'v.middle_name AS middle_name', 'v.name_suffix AS name_suffix',
'v.birth_year AS birth_year', 'v.gender AS gender',
'v.house_number AS house_number', 'v.pre_direction AS pre_direction',
'v.street_name AS street_name', 'v.street_type AS street_type',
'v.suf_direction AS suf_direction', 'v.unit AS unit', 'v.city AS city',
'v.zip AS zip', 'v.voter_id AS voter_id', 'v.reg_date AS reg_date',
'v.permanent_absentee AS permanent_absentee', 'v.status AS status',
'v.uocava AS uocava'
]
__hx_fldnames = [
'h.election_code AS election_code', 'h.absentee_flag AS absentee_flag',
'h.ballot AS ballot'
]