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