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!"