def test_password_change_form(self):
        from nuorisovaalitadmin.models import PasswordReset
        from nuorisovaalitadmin.models import School
        from nuorisovaalitadmin.models import User

        view = self._makeView()
        session = DBSession()
        populate_testing_db()

        school = session.query(School).first()
        self.assertTrue(school is not None)

        user = User(u'john.doe', u'secret', u'Jöhn Döe', u'*****@*****.**', school_or_id=school)
        session.add(user)
        session.flush()

        reset = PasswordReset(user.id, datetime.now() + timedelta(days=7), u'uniquetoken')
        session.add(reset)

        self.assertEquals(1, session.query(PasswordReset).filter_by(token=reset.token).count())
        self.assertEquals(u'john.doe', session.query(User).get(user.id).username)

        view.request.matchdict['token'] = reset.token
        self.assertEquals(view.password_change_form(), {
            'username': u'john.doe',
            'action_url': 'http://example.com/reset-password/process',
            'token': u'uniquetoken',
            'title': u'Vaihda salasana'})
    def test_login__form_submission__invalid_password(self):
        from nuorisovaalitadmin.models import School
        from nuorisovaalitadmin.models import User
        from nuorisovaalitadmin.views.login import login

        session = DBSession()
        populate_testing_db()
        school = session.query(School).first()
        self.assertTrue(school is not None)
        session.add(User(u'john.doe', u'secret', u'Jöhn Döe', u'*****@*****.**', school_or_id=school))
        session.flush()
        self.assertEquals(
            session.query(User).filter_by(username=u'john.doe').first().email,
            u'*****@*****.**')

        request = testing.DummyRequest()
        token = request.session.new_csrf_token()
        request.POST = {
            'form.submitted': u'1',
            'username': u'john.doe',
            'password': u'thisiswrong',
            'csrf_token': token,
        }

        options = login(request)
        self.assertEquals(options, {
            'title': u'Kirjaudu sisään',
            'action_url': 'http://example.com/login',
            'username': u'john.doe',
            'reset_url': 'http://example.com/reset-password',
            'csrf_token': token})
    def test_send_confirmation_message(self, send_mail):
        from email.message import Message
        from nuorisovaalitadmin.models import PasswordReset
        from nuorisovaalitadmin.models import School
        from nuorisovaalitadmin.models import User

        self.config.add_settings(DUMMY_SETTINGS)
        session = DBSession()
        populate_testing_db()
        school = session.query(School).first()
        self.assertTrue(school is not None)

        user = User(u'john.doe', u'secret', u'Jöhn Döe', u'*****@*****.**', school_or_id=school)
        session.add(user)
        session.flush()
        userid = user.id

        view = self._makeView(post={'username': u'john.doe'})
        response = view.send_confirmation_message()

        self.assertEquals(1, session.query(PasswordReset).filter_by(user_id=userid).count())
        self.assertEquals(response.location, 'http://example.com')
        self.assertEquals(view.request.session.pop_flash(),
            [u'Ohjeet salasanan vaihtamiseen on lähetetty sähköpostissa.'])
        self.assertEquals(send_mail.call_args[0][0], u'*****@*****.**')
        self.assertEquals(send_mail.call_args[0][1], [u'*****@*****.**'])
        self.failUnless(isinstance(send_mail.call_args[0][2], Message))
Exemplo n.º 4
0
def populate_voting_results():
    """Reads the paper ballot submissions and populates the database with new
    :py:class:`nuorisovaalit.models.Vote` records based on them.
    """
    session = DBSession()

    records = failures = 0
    for submission in session.query(CSVSubmission).filter_by(kind=CSVSubmission.RESULT).all():
        print 'Processing', submission.school.name.encode('utf-8')
        # Find all the candidates that belong to the district associated with the school.
        candidates = dict((c.number, c) for c in submission.school.district.candidates)
        for record in submission.csv:
            number = int(record['number'])
            votes = int(record['votes'])
            if number in candidates:
                session.add(Vote(candidates[number], submission.school, Vote.PAPER, votes))
                records += 1
            else:
                failures += 1
                print >> sys.stderr, 'Unknown candidate number {0} in district {1}'.format(number, submission.school.district.name.encode('utf-8'))

    if failures == 0:
        session.flush()
        transaction.commit()
        print 'Populated {0} voting records.'.format(records)
    else:
        print >> sys.stderr, 'Aborting due to {0} failure(s).'.format(failures)
        transaction.abort()
    def test_create_message(self):
        from nuorisovaalitadmin.models import PasswordReset
        from nuorisovaalitadmin.models import School
        from nuorisovaalitadmin.models import User

        session = DBSession()
        populate_testing_db()
        self.config.add_settings(DUMMY_SETTINGS)

        school = session.query(School).first()
        self.assertTrue(school is not None)
        user = User(u'john.doe', u'secret', u'Jöhn Döe', u'*****@*****.**', school_or_id=school)
        session.add(user)
        session.flush()
        reset = PasswordReset(user.id, datetime(2010, 11, 15, 17, 20), u'uniquetoken')
        view = self._makeView()

        message = view.create_message(user, reset)
        self.assertEquals(unicode(message['Subject']), u'john.doe')
        self.assertEquals(unicode(message['From']), u'*****@*****.**')
        self.assertEquals(unicode(message['To']), u'john.doe <*****@*****.**>')

        message_str = message.as_string()
        # Check that the relevant bits of information are included in the message
        self.failUnless('john.doe' in message_str)
        self.failUnless('15.11.2010 17:20' in message_str)
        self.failUnless('http://example.com/reset-password/uniquetoken' in message_str)
    def test_voter_submission_stats__submissions(self):
        from nuorisovaalit.models import School
        from nuorisovaalitadmin.models import CSVSubmission
        from nuorisovaalitadmin.models import User
        from nuorisovaalitadmin.views.allianssi import voter_submission_stats

        session = DBSession()
        populate_testing_db()

        eschools = session.query(School)\
                 .join(User)\
                 .filter(User.eparticipation == True)\
                 .all()
        self.assertTrue(len(eschools) > 0)
        school = eschools[0]

        # Check that there is no submission to begin with.
        self.assertEquals(0, session.query(CSVSubmission).count())

        # Add a submission for the school.
        session.add(CSVSubmission({}, school, CSVSubmission.VOTER))
        session.flush()

        school_count = len(eschools)
        school_names_not_submitted = [s.name for s in eschools if s.name != school.name]

        options = voter_submission_stats(DummyRequest())
        options_not_submitted = [s.name for s in options['schools_not_submitted']]
        self.assertEquals(school_count - 1, len(options_not_submitted))
        self.assertEquals(school_names_not_submitted, options_not_submitted)
    def test_results_total_xls__no_votes(self):
        from nuorisovaalit.models import District
        from nuorisovaalit.models import Vote
        from nuorisovaalitadmin.views.allianssi import results_total_xls

        session = DBSession()
        populate_testing_db()

        # Add a district with code 0.
        self.assertEquals(0, session.query(District).filter_by(code=0).count())
        session.add(District(u'Tyhjä piiri åäö', 0))
        session.flush()

        self.assertEquals(0, session.query(Vote).count())

        response = results_total_xls(DummyRequest())
        self.assertTrue(isinstance(response, Response))
        self.assertEquals('application/vnd.ms-excel', response.headers['content-type'])
        self.assertEquals('attachment; filename=nuorisovaalit2011-valtakunnalliset-tulokset.xls',
                          response.headers['content-disposition'])

        wb = xlrd.open_workbook(file_contents=response.body)

        district_names = [n[0] for n in session.query(District.name)]
        self.assertTrue(len(district_names) > 1)
        district_names.remove(u'Tyhjä piiri åäö')

        # Cut long names.
        district_names = sorted(d[:31] for d in district_names)

        # Assert that there is a sheet in the Xls file for every
        # district except the one with code 0.
        sheet_names = sorted(wb.sheet_by_index(i).name for i in xrange(len(district_names)))
        self.assertEquals(district_names, sheet_names)
Exemplo n.º 8
0
def populate_testing_db():
    session = DBSession()
    populate_db()
    school = session.query(School).first()
    session.add(User(u'keijo',
                     u'passwd',
                     u'Keijo Käyttäjä',
                     '*****@*****.**',
                     True,
                     school))
    session.flush()
    def test_groupfinder__with_groups(self):
        from nuorisovaalitadmin.models import Group
        from nuorisovaalitadmin.models import User
        from nuorisovaalitadmin.views.login import groupfinder

        session = DBSession()
        user = User(u'dokai', u'secret', u'Kai', '*****@*****.**', school_or_id=1)
        session.add(user)
        session.flush()
        user.groups.append(Group(u'coolios', u'Coolios'))
        user.groups.append(Group(u'admins', u'Administrators'))

        self.assertEquals(groupfinder(u'dokai', testing.DummyRequest()), [
            'group:coolios', 'group:admins'])
def populate_db():
    from nuorisovaalit.models import District
    from nuorisovaalit.models import School
    from nuorisovaalitadmin.models import Group
    from nuorisovaalitadmin.models import User

    session = DBSession()
    district = District(u'Ylöjärven vaalipiiri', 1)
    session.add(district)
    session.flush()

    school = School(u'Äältö-yliopisto', district)
    session.add(school)
    session.flush()

    grp_admin = Group('admin', u'Admins')
    grp_school = Group('school', u'Schools')
    grp_allianssi = Group('allianssi', u'Allianssi')

    usr_admin = User('usr_admin', 'testi', u'Admin user', '*****@*****.**', True, school, grp_admin)
    usr_school = User('usr_school', 'testi', u'School user', '*****@*****.**', True, school, grp_school)
    usr_allianssi = User('usr_allianssi', 'testi', u'Allianssi user', '*****@*****.**', True, school, grp_allianssi)

    session.add(usr_admin)
    session.add(usr_school)
    session.add(usr_allianssi)
    session.flush()

    usr_admin.groups.append(grp_admin)
    usr_school.groups.append(grp_school)
    usr_allianssi.groups.append(grp_allianssi)
    session.flush()
Exemplo n.º 11
0
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.")
Exemplo n.º 12
0
    def test_change_password(self, remember):
        from nuorisovaalitadmin.models import PasswordReset
        from nuorisovaalitadmin.models import School
        from nuorisovaalitadmin.models import User

        session = DBSession()
        populate_testing_db()

        school = session.query(School).first()
        self.assertTrue(school is not None)

        remember.return_value = [('X-Login', 'john.doe')]
        user = User(u'john.doe', u'secret', u'Jöhn Döe', u'*****@*****.**', school_or_id=school)
        session.add(user)
        session.flush()

        reset = PasswordReset(user.id, datetime.now() + timedelta(days=7), u'uniquetoken')
        session.add(reset)
        session.flush()

        self.assertEquals(1, session.query(PasswordReset)\
            .filter(PasswordReset.user_id == user.id)\
            .filter(PasswordReset.token == u'uniquetoken')\
            .filter(PasswordReset.expires >= datetime.now())\
            .count())

        view = self._makeView(post={
            'token': 'uniquetoken',
            'password': '******',
            'confirm_password': '******'})
        response = view.change_password()
        self.assertEquals(dict(response.headers), {
            'Content-Length': '0',
            'X-Login': '******',
            'Content-Type': 'text/html; charset=UTF-8',
            'Location': 'http://example.com'})
        # Check that the password was changed
        self.failUnless(session.query(User).filter_by(id=user.id).first().check_password('abc123'))
        # Check that the reset request was deleted
        self.assertEquals(0, session.query(PasswordReset).count())
    def test_voter_submission_stats__submission_with_wrong_kind(self):
        from nuorisovaalit.models import School
        from nuorisovaalitadmin.models import CSVSubmission
        from nuorisovaalitadmin.models import User
        from nuorisovaalitadmin.views.allianssi import voter_submission_stats

        session = DBSession()
        populate_testing_db()

        eschools = session.query(School)\
                 .join(User)\
                 .filter(User.eparticipation == True)\
                 .all()
        self.assertTrue(len(eschools) > 0)
        school = eschools[0]

        # Add a submission for the school.
        session.add(CSVSubmission({}, school, CSVSubmission.RESULT))
        session.flush()

        options = voter_submission_stats(DummyRequest())
        self.assertEquals(len(eschools), len(list(options['schools_not_submitted'])))
    def test_result_submission_stats__with_submission(self):
        from nuorisovaalit.models import School
        from nuorisovaalitadmin.models import CSVSubmission
        from nuorisovaalitadmin.views.allianssi import result_submission_stats

        session = DBSession()
        populate_testing_db()
        school_count = session.query(School).count()

        school = session.query(School).first()
        self.assertTrue(school is not None)
        session.add(CSVSubmission({}, school, CSVSubmission.RESULT))
        session.flush()

        options = result_submission_stats(DummyRequest())
        self.assertEquals(u'Tuloslista-info', options['title'])
        self.assertEquals(school_count, options['school_count'])
        self.assertEquals(1, options['school_count_submitted'])
        self.assertEquals(school_count - 1, options['school_count_not_submitted'])
        self.assertEquals('{0:.2f}'.format(100 * 1 / float(school_count)),
                          options['submitted'])
        self.assertEquals('{0:.2f}'.format(100 * (school_count - 1) / float(school_count)),
                          options['not_submitted'])
Exemplo n.º 15
0
    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.'
    def test_voter_list_template__with_voters(self):
        from nuorisovaalit.models import District
        from nuorisovaalit.models import School
        from nuorisovaalit.models import Voter
        from nuorisovaalit.models import VotingLog

        populate_db()
        login_as(self.testapp, u'usr_school')

        # Initial conditions.
        session = DBSession()
        self.assertEquals(0, session.query(Voter).count())
        self.assertEquals(0, session.query(VotingLog).count())

        # Add voters.
        school = session.query(School).one()
        matti = Voter(u'http://matti.meikalainen.example.com',
                      u'Mätti',
                      u'Meikäläinen',
                      datetime(1995, 1, 25),
                      None,
                      None,
                      None,
                      school)
        maija = Voter(u'http://maija.mehilainen.example.com',
                      u'Mäijä',
                      u'Mehiläinen',
                      datetime(1996, 2, 26),
                      None,
                      None,
                      None,
                      school)
        session.add(matti)
        session.add(maija)
        session.flush()

        # Add voting logs.
        timestamp = time.mktime(datetime(2011, 2, 28, 23, 59, 58).timetuple())
        session.add(VotingLog(maija, timestamp))
        session.flush()

        # Add extra school and a voter to it to check that they do not
        # appear in the wrong place.
        district = session.query(District).one()
        school_other = School(u'Väärä koulu', district)
        session.add(school_other)
        session.flush()
        vaara = Voter(u'http://vaara.aanestaja.example.com',
                      u'Väärä',
                      u'Äänestäjä',
                      datetime(1994, 1, 21),
                      None,
                      None,
                      None,
                      school_other)
        session.add(vaara)
        session.add(Voter(u'http://kiero.oykkari.example.com',
                          u'Kierö',
                          u'Öykkäri',
                          datetime(1993, 3, 22),
                          None,
                          None,
                          None,
                          school_other))
        session.flush()
        timestamp_vaara = time.mktime(datetime(2010, 3, 30, 20, 58, 59).timetuple())
        session.add(VotingLog(vaara, timestamp_vaara))
        session.flush()

        res = self.testapp.get('/voter-list.xls')
        self.assertTrue(isinstance(res, Response))
        self.assertEquals('application/vnd.ms-excel',
                          res.headers['content-type'])
        self.assertEquals('attachment; filename=nuorisovaalit2011-aanestajalista.xls',
                          res.headers['content-disposition'])

        # Assert the Excel file contents so that it contains the
        # correct info and only the voters that are in the user's
        # school.
        self.assertXlsEquals(u'Nuorisovaalit 2011', [
            (u'Sukunimi', u'Etunimi', u'Syntymäaika', u'Äänestänyt'),
            (u'Mehiläinen', u'Mäijä', u'26.02.1996', u'28.02.2011 23:59:58'),
            (u'Meikäläinen', u'Mätti', u'25.01.1995', u''),
        ], res.body, skip_header=False, col_widths=(7000, 7000, 3500, 7000))
Exemplo n.º 17
0
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 test_result_submission_stats__with_different_kinds_of_schools(self):
        from nuorisovaalit.models import District
        from nuorisovaalit.models import School
        from nuorisovaalitadmin.models import CSVSubmission
        from nuorisovaalitadmin.views.allianssi import result_submission_stats

        session = DBSession()

        # Initial conditions.
        self.assertEquals(0, session.query(District).count())
        self.assertEquals(0, session.query(School).count())
        self.assertEquals(0, session.query(CSVSubmission).count())

        # Populate db.
        district1 = District(u'district 1', 1)
        district2 = District(u'district 2', 2)
        district3 = District(u'district 3', 3)
        session.add(district1)
        session.add(district2)
        session.add(district3)
        session.flush()

        school1 = School(u'schööl 1', district1)
        school2 = School(u'schööl 2', district1)
        school3 = School(u'schööl 3', district2)
        school4 = School(u'schööl 4', district2)
        school5 = School(u'schööl 5', district3)
        school6 = School(u'schööl 6', district3)
        school7 = School(u'schööl 7', district3)
        session.add(school1)
        session.add(school2)
        session.add(school3)
        session.add(school4)
        session.add(school5)
        session.add(school6)
        session.add(school7)
        session.flush()

        session.add(CSVSubmission({}, school3, CSVSubmission.VOTER))
        session.add(CSVSubmission({}, school3, CSVSubmission.RESULT))
        session.add(CSVSubmission({}, school4, CSVSubmission.RESULT))
        session.add(CSVSubmission({}, school5, CSVSubmission.VOTER))
        session.add(CSVSubmission({}, school6, CSVSubmission.RESULT))
        session.add(CSVSubmission({}, school7, CSVSubmission.VOTER))
        session.add(CSVSubmission({}, school7, CSVSubmission.RESULT))
        session.flush()

        options = result_submission_stats(DummyRequest())
        schools_not_submitted = options.pop('schools_not_submitted')

        self.assertEquals([
            u'schööl 1',
            u'schööl 2',
            u'schööl 5',
        ], sorted(s.name for s in schools_not_submitted))

        self.assertEquals({
            'title': u'Tuloslista-info',
            'school_count': 7,
            'school_count_submitted': 4,
            'school_count_not_submitted': 3,
            'submitted': '57.14',
            'not_submitted': '42.86',
        }, options)