def run(self):
session = DBSession()
session.bind.echo = False
# Join the repoze.filesafe manager in the transaction so that files will
# be written only when a transaction commits successfully.
filesafe = FileSafeDataManager()
transaction.get().join(filesafe)
# Query the currently existing usernames to avoid UNIQUE violations.
self.usernames.update(username for cols in session.query(Voter.username).all() for username in cols)
fh_itella = filesafe.createFile(self.output, 'w')
# Excel worksheet for Itella
wb_itella = xlwt.Workbook(encoding='utf-8')
ws_itella = wb_itella.add_sheet('Hexagon IT')
text_formatting = xlwt.easyxf(num_format_str='@')
for col, header in enumerate([u'Tunnus', u'Salasana', u'Nimi', u'Osoite', u'Postinumero', u'Postitoimipaikka']):
ws_itella.write(0, col, header, text_formatting)
rows_itella = count(1)
voter_count = 0
self.header('Starting to process {}'.format(self.source_file))
src_sheet = xlrd.open_workbook(self.source_file).sheet_by_index(0)
pbar = progressbar.ProgressBar(widgets=[progressbar.Percentage(), progressbar.Bar()], maxval=src_sheet.nrows).start()
for row in xrange(1, src_sheet.nrows):
token, lastname, names, address, zipcode, city = [c.value for c in src_sheet.row_slice(row, 0, 6)]
names = names.split()
firstname = names[0]
username = self.genusername(names, lastname)
password = self.genpasswd()
# Create the voter instance.
session.add(Voter(username, password, firstname, lastname, token))
# Write the Itella information
row = rows_itella.next()
for col, item in enumerate([username, password, u'{} {}'.format(u' '.join(names), lastname), address, zipcode, city]):
ws_itella.write(row, col, item, text_formatting)
voter_count += 1
pbar.update(voter_count)
wb_itella.save(fh_itella)
fh_itella.close()
session.flush()
transaction.commit()
pbar.finish()
self.header('Finished processing')
print 'Processed', voter_count, 'voters.'
def run(self, startdate=None):
session = DBSession()
if startdate is None:
startdate = datetime(1900, 1, 1)
# Join the repoze.filesafe manager in the transaction so that files will
# be written only when a transaction commits successfully.
filesafe = FileSafeDataManager()
transaction.get().join(filesafe)
# Query the currently existing usernames to avoid UNIQUE violations.
# The usernames are stored as OpenID identifiers so we need to extract
# the usernames from the URLs.
self.usernames.update([
urlparse(openid).netloc.rsplit('.', 2)[0]
for result in session.query(Voter.openid).all()
for openid in result])
# Query the voter submission and associated schools.
submissions = session.query(School, CSVSubmission)\
.join(CSVSubmission)\
.filter(CSVSubmission.kind == CSVSubmission.VOTER)\
.filter(CSVSubmission.timestamp > startdate)\
.order_by(School.name)
fh_openid = filesafe.createFile(self.filename('voters-openid_accounts-{id}.txt'), 'w')
fh_email = filesafe.createFile(self.filename('voters-email-{id}.txt'), 'w')
fh_labyrintti = filesafe.createFile(self.filename('voters-labyrintti-{id}.csv'), 'w')
fh_itella = filesafe.createFile(self.filename('voters-itella-{id}.xls'), 'w')
# Excel worksheet for Itella
wb_itella = xlwt.Workbook(encoding='utf-8')
ws_itella = wb_itella.add_sheet('xxxx')
text_formatting = xlwt.easyxf(num_format_str='@')
for col, header in enumerate([u'Tunnus', u'Salasana', u'Nimi', u'Osoite', u'Postinumero', u'Postitoimipaikka', u'Todennäköinen kunta']):
ws_itella.write(0, col, header, text_formatting)
rows_itella = count(1)
school_count = voter_count = address_parse_errors = 0
self.header('Starting to process submissions')
for school, submission in submissions.all():
self.header('Processing school: {0}'.format(school.name.encode('utf-8')))
for voter in submission.csv:
username = self.genusername(voter['firstname'], voter['lastname'])
password = self.genpasswd()
# Create the voter instance.
openid = u'http://{0}.did.fi'.format(username)
dob = date(*reversed([int(v.strip()) for v in voter['dob'].split('.', 2)]))
session.add(Voter(openid, voter['firstname'], voter['lastname'], dob, voter['gsm'], voter['email'], voter['address'], school))
# Write the OpenID account information.
fh_openid.write(u'{0}|{1}\n'.format(username, password).encode('utf-8'))
has_gsm, has_email = bool(voter['gsm'].strip()), bool(voter['email'].strip())
if has_gsm or has_email:
if has_gsm:
# Write the Labyrintti information only if a GSM number is available.
message = self.SMS_TMPL.format(username=username, password=password).encode('utf-8')
if len(message) > 160:
transaction.abort()
raise ValueError('SMS message too long: {0}'.format(message))
fh_labyrintti.write('"{0}","{1}"\n'.format(
u''.join(voter['gsm'].split()).encode('utf-8'),
message))
if has_email:
# Write the email information for everybody with an address.
fh_email.write(u'{0}|{1}|{2}\n'.format(username, password, voter['email']).encode('utf-8'))
else:
# Write the Itella information for those that only have an
# address. We rely on the validation to ensure that it is
# available.
match = RE_ADDRESS.match(voter['address'])
if match is not None:
street = match.group(1).strip().strip(u',').strip()
zipcode = match.group(2).strip().strip(u',').strip()
city = match.group(3).strip().strip(u',').strip()
row = rows_itella.next()
for col, item in enumerate([username, password, u'{0} {1}'.format(voter['firstname'].split()[0], voter['lastname']), street, zipcode, city]):
ws_itella.write(row, col, item, text_formatting)
else:
print 'Failed to parse address for {0}: {1}.'.format(username, voter['address'].encode('utf-8'))
address_parse_errors += 1
row = rows_itella.next()
for col, item in enumerate([username, password, u'{0} {1}'.format(voter['firstname'].split()[0], voter['lastname']), voter['address'], u'', u'', school.name]):
ws_itella.write(row, col, item, text_formatting)
print username
voter_count += 1
school_count += 1
wb_itella.save(fh_itella)
fh_openid.close()
fh_email.close()
fh_labyrintti.close()
fh_itella.close()
session.flush()
transaction.commit()
self.header('Finished processing')
print 'Processed', school_count, 'schools and', voter_count, 'voters.'
print 'Address parsing failed for', address_parse_errors, 'users.'
