def test_results_index__with_votes(self):
        from nuorisovaalit.models import Candidate
        from nuorisovaalit.models import School
        from nuorisovaalit.models import Vote
        from nuorisovaalitadmin.views.allianssi import results_index

        self.config.add_route('results_total_xls', '/results-total.xls')
        session = DBSession()
        populate_testing_db()

        candidate = session.query(Candidate).first()
        school = session.query(School)\
                 .filter_by(district_id=candidate.district_id)\
                 .first()
        self.assertTrue(candidate is not None)
        self.assertTrue(school is not None)

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

        session.add(Vote(candidate, school, Vote.PAPER, 15))
        session.add(Vote(candidate, school, Vote.ELECTRONIC))
        session.add(Vote(candidate, school, Vote.ELECTRONIC))

        options = results_index(DummyRequest())
        options.pop('voted')
        options.pop('not_voted')

        self.assertEquals({
            'title': u'Tulokset',
            'vote_count_total': 17,
            'vote_count_electronic': 2,
            'results_total_xls': 'http://example.com/results-total.xls',
        }, options)
    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__success_with_full_identity(self, remember):
        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))
        self.assertEquals(
            session.query(User).filter_by(username=u'john.doe').first().email,
            u'*****@*****.**')

        remember.return_value = [('X-Login', 'john.doe')]
        request = testing.DummyRequest()
        token = request.session.new_csrf_token()
        request.POST = {
            'form.submitted': u'1',
            'username': u'john.doe',
            'password': u'secret',
            'csrf_token': token,
        }

        response = login(request)
        self.assertEquals(dict(response.headers), {
            'Content-Length': '0',
            'Content-Type': 'text/html; charset=UTF-8',
            'Location': 'http://example.com',
            'X-Login': u'john.doe'})
        self.assertEquals(request.session.pop_flash(), [u'Olet kirjautunut sisään.'])
    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))
    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 result_submission_stats(request):
    """Renders statistics about the schools who have or have not submitted
    paper ballot results.
    """
    session = DBSession()
    # Total number of schools participating in the voting.
    school_count = session.query(School).count()

    schools_not_submitted = session.query(School)\
              .join(District)\
              .filter(~School.csvsubmission.any(CSVSubmission.kind == CSVSubmission.RESULT))\
              .order_by(District.name, School.name)\
              .all()

    school_count_not_submitted = len(schools_not_submitted)
    school_count_submitted = school_count - school_count_not_submitted

    submitted = '0.00'
    not_submitted = '100.00'
    if school_count > 0:
        percentage = 100 * school_count_submitted / float(school_count)
        submitted = '{0:.2f}'.format(percentage)
        not_submitted = '{0:.2f}'.format(100 - percentage)

    request.add_response_callback(disable_caching)

    return {
        'title': u'Tuloslista-info',
        'school_count': school_count,
        'school_count_submitted': school_count_submitted,
        'school_count_not_submitted': school_count_not_submitted,
        'submitted': submitted,
        'not_submitted': not_submitted,
        'schools_not_submitted': schools_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)
    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_prune_expired__empty(self):
        from nuorisovaalitadmin.models import PasswordReset

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

        self.assertEquals(0, session.query(PasswordReset).count())
        view.prune_expired()
        self.assertEquals(0, session.query(PasswordReset).count())
    def test_prune_expired(self):
        from nuorisovaalitadmin.models import PasswordReset

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

        session.add(PasswordReset(1, datetime.now() - timedelta(days=30)))
        session.add(PasswordReset(2, datetime.now() + timedelta(days=30)))

        self.assertEquals(2, session.query(PasswordReset).count())
        view.prune_expired()
        self.assertEquals(1, session.query(PasswordReset).count())
        self.assertEquals(2, session.query(PasswordReset).first().user_id)
    def test_password_change_form__invalid_user(self):
        from nuorisovaalitadmin.models import PasswordReset
        from nuorisovaalitadmin.models import User

        view = self._makeView()
        reset = PasswordReset(1, datetime.now() + timedelta(days=7))
        session = DBSession()
        session.add(reset)

        self.assertEquals(1, session.query(PasswordReset).filter_by(token=reset.token).count())
        self.assertEquals(None, session.query(User).get(1))

        view.request.matchdict['token'] = reset.token
        self.assertRaises(NotFound, view.password_change_form)
def login(request):
    """Renders a login form and logs in a user if given the correct
    credentials.
    """
    session = DBSession()
    username = u''
    log = logging.getLogger('nuorisovaalitadmin')

    if 'form.submitted' in request.POST:
        username = request.POST['username']

        if request.session.get_csrf_token() != request.POST.get('csrf_token'):
            log.warn('CSRF attempt at {0}.'.format(request.url))
            raise Forbidden(u'CSRF attempt detected.')
        else:
            user = session.query(User).filter_by(username=username).first()
            password = request.POST['password']

            if user is not None and user.check_password(password):
                headers = remember(request, user.username)
                request.session.flash(u'Olet kirjautunut sisään.')
                log.info('Successful login for "{0}".'.format(user.username))
                return HTTPFound(location=request.application_url, headers=headers)

            log.warn('Failed login attempt for {0}'.format(request.POST.get('username').encode('utf-8')))
            request.session.flash(u'Kirjautuminen epäonnistui')

    request.add_response_callback(disable_caching)
    return {
        'title': u'Kirjaudu sisään',
        'action_url': route_url('login', request),
        'username': username,
        'reset_url': route_url('reset_password', request),
        'csrf_token': request.session.get_csrf_token(),
    }
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 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 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 results_template_xls(request):
    """Downloads an Excel sheet template for paper ballot results submission.

    The Excel template contains the names and numbers of the candidates
    associated with the school's voting district.
    """
    rows = [
        (u'Numero', u'Nimi', u'Äänimäärä'),
    ]

    user = authenticated_user(request)
    if user is None:
        raise Forbidden

    session = DBSession()

    # Add all the candidates and their numbers into the CSV template.
    candidates = session.query(Candidate)\
                 .filter(Candidate.district == user.school.district)\
                 .order_by(Candidate.number)
    rows.extend((c.number, c.fullname(), u'') for c in candidates)

    request.add_response_callback(disable_caching_explorer)
    return xls_response(rows,
                        filename='nuorisovaalit2011-uurnatulokset.xls',
                        num_formats=('@', '@', '@'),
                        col_widths=(None, 8000, 3000))
    def validate(self, submission):
        """Validates the submitted data and returns a list of error messages
        corresponding to validation failures.

        Each error message is a dictionary containing the line number where
        the error occurred and a message describing the error, e.g.::

            { 'lineno': 13, 'msg': u'Etunimi puuttuu.' }

        :param submission: Iterable of dictionary containing the submission
            data.
        :type submission: iterable

        :rtype: list
        """
        errors = []

        def missing(row, field):
            return row.get(field) is None or len(row[field].strip()) == 0

        def error(lineno, msg):
            return dict(lineno=lineno, msg=msg)

        def is_num(n):
            return n is not None and n.strip().isdigit()

        # Start line numbering from two since the header row was
        # consumed while parsing.
        for lineno, row in enumerate(submission, start=2):
            if not any(v.strip() for v in row.values()):
                # Skip empty rows.
                continue

            if missing(row, 'name'):
                errors.append(error(lineno, u'Ehdokkaan nimi puuttuu.'))

            if missing(row, 'votes'):
                errors.append(error(lineno, u'Ehdokkaan äänimäärä puuttuu.'))
            elif not is_num(row.get('votes')):
                errors.append(error(lineno, u'Äänimäärä on väärää muotoa. Tarkista, että äänimäärä on positiivinen kokonaisluku.'))

            if missing(row, 'number'):
                errors.append(error(lineno, u'Ehdokasnumero puuttuu.'))
            elif not is_num(row.get('number')):
                errors.append(error(lineno, u'Ehdokasnumero on väärää muotoa. Tarkista, että ehdokasnumero on positiivinen kokonaisluku.'))
            else:
                # Check that there exists exactly one candidate with
                # the given number who is in the same district as the
                # user.
                num = int(row.get('number').strip())
                session = DBSession()
                num_candidates = session.query(Candidate)\
                                 .filter(Candidate.number == num)\
                                 .filter(Candidate.district == self.user.school.district)\
                                 .count()
                if num_candidates != 1:
                    errors.append(error(lineno, u'Ehdokasnumero {0} ei vastaa yhtään vaalipiirin ehdokasta.'.format(num)))

        return errors
    def test_index__authenticated(self):
        from nuorisovaalitadmin.views.login import index
        from nuorisovaalitadmin.models import User

        session = DBSession()
        populate_testing_db()
        user = session.query(User).first()
        self.config.testing_securitypolicy(userid=user.username)

        self.assertEquals({}, index(DummyRequest()))
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_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_result_submission_stats__no_submissions(self):
        from nuorisovaalit.models import School
        from nuorisovaalitadmin.models import CSVSubmission
        from nuorisovaalitadmin.views.allianssi import result_submission_stats

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

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

        options = result_submission_stats(DummyRequest())

        self.assertEquals(u'Tuloslista-info', options['title'])
        self.assertEquals(school_count, options['school_count'])
        self.assertEquals(0, options['school_count_submitted'])
        self.assertEquals(school_count, options['school_count_not_submitted'])
        self.assertEquals('0.00', options['submitted'])
        self.assertEquals('100.00', options['not_submitted'])
def voter_submission_stats(request):
    """Renders statistics about the schools who have or have not submitted
    voter information.
    """
    session = DBSession()
    # Total number of schools participating in the electronic voting.
    school_count = session.query(School)\
                    .join(User)\
                    .filter(User.eparticipation == True)\
                    .count()

    # Query the schools that have yet to submit voter information.
    schools_not_submitted = session.query(School)\
                .join(District)\
                .join(User)\
                .filter(User.eparticipation == True)\
                .filter(~School.csvsubmission.any(CSVSubmission.kind == CSVSubmission.VOTER))\
                .order_by(District.name, School.name)\
                .all()

    school_count_not_submitted = len(schools_not_submitted)
    school_count_submitted = school_count - school_count_not_submitted

    submitted = '0.00'
    not_submitted = '100.00'
    if school_count > 0:
        percentage = 100 * school_count_submitted / float(school_count)
        submitted = '{0:.2f}'.format(percentage)
        not_submitted = '{0:.2f}'.format(100 - percentage)

    request.add_response_callback(disable_caching)

    return {
        'title': u'Äänestäjälista-info',
        'school_count': school_count,
        'school_count_submitted': school_count_submitted,
        'school_count_not_submitted': school_count_not_submitted,
        'submitted': submitted,
        'not_submitted': not_submitted,
        'schools_not_submitted': schools_not_submitted,
    }
    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_list_template__empty(self):
        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())

        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'])

        self.assertXlsEquals(u'Nuorisovaalit 2011', [
            (u'Sukunimi', u'Etunimi', u'Syntymäaika', u'Äänestänyt'),
        ], res.body, skip_header=False, col_widths=(7000, 7000, 3500, 7000))
    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'])
    def test_voter_submission_stats__no_schools(self):
        from nuorisovaalit.models import School
        from nuorisovaalitadmin.views.allianssi import voter_submission_stats

        session = DBSession()
        # Check that there is no schools.
        self.assertEquals(0, session.query(School).count())

        options = voter_submission_stats(DummyRequest())
        self.assertEquals(u'Äänestäjälista-info', options['title'])
        self.assertEquals(0, options['school_count'])
        self.assertEquals(0, options['school_count_submitted'])
        self.assertEquals('0.00', options['submitted'])
        self.assertEquals([], list(options['schools_not_submitted']))
def results_index(request):
    """Index page for the xxxx specific results.

    The results will include:

        * Total results
        * TODO: xxxx needs to provide information what type of results
            they need.
    """
    session = DBSession()

    vote_count = session.query(func.sum(Vote.count)).scalar()
    vote_count_electronic = session.query(func.sum(Vote.count))\
                            .filter(Vote.kind == Vote.ELECTRONIC).scalar()
    if vote_count is None:
        vote_count = 0
    if vote_count_electronic is None:
        vote_count_electronic = 0

    request.add_response_callback(disable_caching)

    voted = '0.00'
    not_voted = '100.00'
    voter_count = session.query(Voter).count()
    if voter_count > 0:
        percentage = 100 * float(vote_count_electronic) / voter_count
        voted = '{0:.2f}'.format(percentage)
        not_voted = '{0:.2f}'.format(100 - percentage)

    return {
        'title': u'Tulokset',
        'vote_count_total': vote_count,
        'vote_count_electronic': vote_count_electronic,
        'voted': voted,
        'not_voted': not_voted,
        'results_total_xls': route_url('results_total_xls', request),
    }
    def test_change_password__invalid_user(self):
        from nuorisovaalitadmin.models import PasswordReset

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

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

        view = self._makeView(post={
            'token': 'uniquetoken',
            'password': '******',
            'confirm_password': '******'})
        self.assertRaises(NotFound, view.change_password)
    def test_results_index__no_votes(self):
        from nuorisovaalit.models import Vote
        from nuorisovaalitadmin.views.allianssi import results_index

        self.config.add_route('results_total_xls', '/results-total.xls')
        session = DBSession()
        populate_testing_db()

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

        self.assertEquals({
            'title': u'Tulokset',
            'vote_count_total': 0,
            'vote_count_electronic': 0,
            'voted': '0.00',
            'not_voted': '100.00',
            'results_total_xls': 'http://example.com/results-total.xls',
        }, results_index(DummyRequest()))
def voter_list_xls(request):
    """Downloads the school specific list of voters as an Excel sheet.

    The list contains all voters registered for the school and voters who have
    already cast their vote will have a timestamp noting the time the vote
    was cast. The timestamp (or lack thereof) can be used to either grant or
    deny access to the paper ballot.

    The Excel sheet contains the following information about each voter:

        * Last name
        * First name
        * Date of birth (dd.mm.yyyy)
        * Voting timestamp
    """
    user = authenticated_user(request)
    if user is None:
        raise Forbidden

    rows = [
        (u'Sukunimi', u'Etunimi', u'Syntymäaika', u'Äänestänyt'),
    ]
    session = DBSession()

    # Query all the voters and the voting timestamp if they have voted.
    voters = session.query(Voter.lastname, Voter.firstname, Voter.dob, VotingLog.timestamp.label('timestamp'))\
             .outerjoin(VotingLog)\
             .filter(Voter.school == user.school)\
             .order_by(Voter.lastname, Voter.firstname)

    for voter in voters:
        timestamp = ''
        if voter.timestamp is not None:
            timestamp = datetime.fromtimestamp(voter.timestamp).strftime('%d.%m.%Y %H:%M:%S')
        rows.append((voter.lastname,
                     voter.firstname,
                     unicode(voter.dob.strftime('%d.%m.%Y')),
                     unicode(timestamp)))

    request.add_response_callback(disable_caching_explorer)
    return xls_response(rows, filename='nuorisovaalit2011-aanestajalista.xls',
                        num_formats=('@', '@', '@', '@'),
                        col_widths=(7000, 7000, 3500, 7000))