def voter_submission_counts():
"""Lists the schools that have submitted voters and the number of voters
for each school.
"""
engine = engine_from_config(get_config(), 'sqlalchemy.')
initialize_sql(engine)
session = DBSession()
query = session.query(School.name, CSVSubmission.csv)\
.join(CSVSubmission)\
.filter(CSVSubmission.kind == CSVSubmission.VOTER)\
.order_by(School.name)
stats = defaultdict(int)
for school, submission in query.all():
if school == u'Dummy school':
continue
print school, len(submission)
stats['total'] += len(submission)
for entry in submission:
if entry['gsm'].strip():
stats['gsm'] += 1
elif entry['email'].strip():
stats['email'] += 1
else:
stats['letter'] += 1
print
print 'Total voters: {total}'.format(**stats)
print 'SMS: {gsm}, Email: {email}, Letter: {letter}'.format(**stats)
def populate_voters():
engine = engine_from_config(get_config(), 'sqlalchemy.')
initialize_sql(engine)
engine.echo = False
startdate = None
if len(sys.argv) >= 2:
try:
startdate = datetime.strptime(sys.argv[2].strip(), '%Y-%m-%dT%H:%M')
print "Reading submissions received after", startdate.strftime('%Y-%m-%d %H:%M')
except ValueError:
print "Invalid timestamp:", sys.argv[2], ". Excepted format is %Y-%m-%dT%H:%M"
sys.exit(1)
CreateVoters().run(startdate)
def populate_demo():
engine = engine_from_config(get_config(), 'sqlalchemy.')
initialize_sql(engine)
session = DBSession()
engine.echo = False
school1 = session.query(School).get(1)
school2 = session.query(School).get(2)
school3 = session.query(School).get(3)
grp_admin = Group('admin', u'Administrators')
grp_allianssi = Group('xxxx', u'xxxx')
grp_school = Group('school', u'Schools')
grp_school_limited = Group('school_limited', u'Schools (results only)')
session.add(grp_admin)
session.add(grp_allianssi)
session.add(grp_school)
session.add(grp_school_limited)
admin = User('admin', 'testi', u'Admin user', '*****@*****.**', True, school1)
allianssi = User('xxxx', 'testi', u'xxxx', '*****@*****.**', True, school1)
school_user1 = User('school1', 'testi', u'School user', '*****@*****.**', True, school1)
school_user2 = User('school2', 'testi', u'School user', '*****@*****.**', True, school2)
school_user3 = User('school3', 'testi', u'School user', '*****@*****.**', True, school3)
admin.groups.append(grp_admin)
allianssi.groups.append(grp_allianssi)
school_user1.groups.append(grp_school)
school_user2.groups.append(grp_school)
school_user3.groups.append(grp_school_limited)
session.add(admin)
session.add(allianssi)
session.add(school_user1)
session.add(school_user2)
session.add(school_user3)
session.flush()
transaction.commit()
print("Generated demo accounts.")
def populate_voting_results_cli():
"""Command line interface for populate_voting_results()."""
engine = engine_from_config(get_config(), 'sqlalchemy.')
initialize_sql(engine)
populate_voting_results()
def populate_school_accounts():
"""Creates the school representative accounts.
This scripts assumes that the database is already populated with the
voting district information.
Based on the information received we create the following type of objects:
* nuorisovaalit.models.School
* nuorisovaalitadmin.models.User
.. warning:: Running this function multiple times on the same data will
result in redundant accounts to be created. You should only
run it once per dataset.
"""
engine = engine_from_config(get_config(), 'sqlalchemy.')
initialize_sql(engine)
session = DBSession()
print('Generating school representative accounts.')
# Generate user groups if necessary
groups = [
('admin', u'Administrators'),
('xxxx', u'xxxx'),
('school', u'Schools'),
('school_limited', u'Schools (results only)')]
for gname, gtitle in groups:
if session.query(Group).filter(Group.name == gname).count() == 0:
print(' > Created group: {0}'.format(gname))
session.add(Group(gname, gtitle))
session.flush()
# Create a dummy school to satisfy constraints.
district = session.query(District).first()
if session.query(School).filter_by(name=u'Dummy school').count() == 0:
dummy_school = School(u'Dummy school', district)
session.add(dummy_school)
session.flush()
# Create an admin account if necessary
if session.query(User).filter_by(username='******').count() == 0:
print(' > Creating an admin user.')
admin_grp = session.query(Group).filter_by(name='admin').one()
admin = User('admin', 'xxxx', u'Administrator', u'*****@*****.**', False, dummy_school, admin_grp)
session.add(admin)
# Create the xxxx account if necessary
if session.query(User).filter_by(username='******').count() == 0:
print(' > Creating the xxxx user.')
allianssi_grp = session.query(Group).filter_by(name='xxxx').one()
dummy_school = session.query(School).filter_by(name=u'Dummy school').first()
allianssi = User('xxxx', 'yyyy', u'xxxx', u'*****@*****.**', False, dummy_school, allianssi_grp)
session.add(allianssi)
school_grp = session.query(Group).filter_by(name='school').one()
school_limited_grp = session.query(Group).filter_by(name='school_limited').one()
# Create a test account that has normal school user access.
if session.query(User).filter_by(username='******').count() == 0:
print(' > Creating a dummy school user.')
dummy_school = session.query(School).filter_by(name=u'Dummy school').first()
schooltest = User('schooltest', 'xxxx', u'School test account', u'*****@*****.**', False, dummy_school, school_grp)
session.add(schooltest)
def genpasswd(length=8, chars='abcdefhkmnprstuvwxyz23456789'):
return u''.join(random.choice(chars) for i in xrange(length))
users = set([username
for result in session.query(User.username).all()
for username in result])
def genusername(name):
base = candidate = unicode(unidecode(u'.'.join(name.strip().lower().split())))
suffix = count(2)
while candidate in users:
candidate = base + unicode(suffix.next())
users.add(candidate)
return candidate
if len(sys.argv) > 2:
filename = os.path.join(os.getcwd(), sys.argv[2].strip())
reader = csv.reader(open(filename, 'rb'))
# Skip the first row.
reader.next()
else:
print('No CSV file was provided, omitting school account creation!')
reader = tuple()
# Generate the users
for row in reader:
school_name, fullname, email, district_name, participation = \
[f.decode('utf-8').strip() for f in row[:5]]
# Find the corresponding district
distcode = int(district_name.strip().split()[0])
district = session.query(District).filter_by(code=distcode).one()
# Create the school object.
school = School(school_name, district)
session.add(school)
session.flush()
password = genpasswd()
username = genusername(fullname)
participates = participation.strip() == '1'
# Choose the user group based on the participation to the electronic election.
group = school_grp if participates else school_limited_grp
session.add(User(username, password, fullname, email, participates, school, group))
print(u'{0}|{1}|{2}|{3}'.format(username, password, email, school_name))
session.flush()
transaction.commit()
def verify_voters():
"""Verifies the generated Voter instances against the OpenID account
list.
"""
engine = engine_from_config(get_config(), 'sqlalchemy.')
initialize_sql(engine)
session = DBSession()
if len(sys.argv) < 6:
print "Usage: {0} <config> <openidlist> <itellalist> <labyrinttilist> <emaillist>".format(sys.argv[0])
sys.exit(1)
openids = set([oid for result in session.query(Voter.openid).all() for oid in result])
failed = 0
passwords = dict(line.strip().split('|', 1) for line in open(sys.argv[2]).read().splitlines())
print "Checking Voter instances for OpenID consistency.."
for username, password in passwords.iteritems():
openid = 'http://{0}.did.fi'.format(username)
if openid not in openids:
print "Unknown OpenID account:", openid
failed += 1
print "Checked", len(passwords), "OpenID accounts against", len(openids), "voters."
if failed == 0:
print "Voter instances OK"
else:
print "Failures", failed
print "Data inconsistency in Voter instances, please investigate!"
print
print "Checking Itella listing for password consistency.."
failed = 0
wb = xlrd.open_workbook(filename=os.path.join(os.getcwd(), sys.argv[3]))
ws = wb.sheet_by_index(0)
for row in xrange(1, ws.nrows):
username = ws.cell_value(row, 0)
password = ws.cell_value(row, 1)
if username not in passwords:
print "Unknown username:"******"Password mismatch for username:"******"Checked", ws.nrows, "Excel rows."
if failed == 0:
print "Itella records OK"
else:
print "Failures", failed
print "Data inconsistency in Itella records, please investigate!"
print
print "Checking Labyrintti listing for password consistency.."
failed = 0
RE_CREDS = re.compile(u'.*tunnus: ([a-z0-9.-]+), salasana: ([a-z0-9]+)')
for row in csv.reader(open(sys.argv[4]), delimiter=',', quotechar='"'):
m = RE_CREDS.match(row[1])
if m is not None:
username = m.group(1)
password = m.group(2)
if username not in passwords:
print "Unknown username:"******"Password mismatch for username:"******"Invalid message:", row[1]
print "Checked", len(open(sys.argv[4]).readlines()), "CSV rows."
if failed == 0:
print "Labyrintti records OK"
else:
print "Failures", failed
print "Data inconsistency in Labyrintti records, please investigate!"
print
print "Checking email listing for password consistency.."
failed = 0
lines = open(sys.argv[5]).read().splitlines()
for line in lines:
username, password, email = line.split('|', 2)
if username not in passwords:
print "Unknown username:"******"Password mismatch for username:"******"Checked", len(lines), "email records."
if failed == 0:
print "Email records OK"
else:
print "Failures", failed
print "Data inconsistency in email records, please investigate!"