<reponame>stolau/oty_ilmo
from werkzeug.security import generate_password_hash, check_password_hash
from flask import Flask, render_template, url_for, redirect, request, flash, send_from_directory, session
from flask_httpauth import HTTPBasicAuth
from app import app, db
from flask_mail import Mail, Message
from datetime import datetime, date, time, timedelta
import requests
from sqlalchemy import and_
from app.forms import ots2021Form, fuksisitsitForm, tupsufuksisitsitForm, fuksibeerpongForm, beerpongForm, aitiForm, hvsitsituusiForm, syysuitotForm, prositsitForm, simailutForm, kotiruokakurssi1Form, kotiruokakurssi2Form
from app.models import ots2021Model, fuksisitsitModel, tupsufuksisitsitModel, fuksibeerpongModel, beerpongModel, aitiModel, hvsitsituusiModel, syysuitotModel, prositsitModel, simailutModel, kotiruokakurssi1Model, kotiruokakurssi2Model
from flask_wtf.csrf import CSRFProtect, CSRFError
import os
from app import sqlite_to_csv
from werkzeug.datastructures import MultiDict
from urllib.parse import urlparse, urlunparse
import subprocess
import time
import json

auth = HTTPBasicAuth()

csrf = CSRFProtect()

try:
    file = open("auth.conf", "r")
    lines = file.readlines()

    password = <PASSWORD>)
    print("created default user")
    print("username: admin")
    print("password: " + str(password))
    
    users = {
        "admin": generate_password_hash(password)
    }

    roles = {
        "admin": "admin"
    }

    for line in lines:
        new_user = line.split(",", 6)
        users[new_user[0]] = generate_password_hash(new_user[1])
        roles[new_user[0]] = new_user[2:6]


except FileNotFoundError as e:
    print(e)

    password = os.urandom(64)
    print("auth.conf not found, created default user")
    print("username: admin")
    print("password: " + str(password))
    
    users = {
        "admin": generate_password_hash(password)
    }

    roles = {
        "admin": "admin"
    }

else:
    file.close()


KAPSI = False

try:
    file = open("routes.conf", "r")
    lines = file.readlines()

    for line in lines:
        conf_line = line.split(":", 2)
        
        if "kapsi" in conf_line[0]:
            if "true" in conf_line[1]:
                KAPSI = True
            else:
                KAPSI = False

except FileNotFoundError as e:
    print(e)
    print("routes.conf not found")

else:
    file.close()


GMAIL = True

if GMAIL:
    
    mail_settings = {
        "MAIL_SERVER": 'smtp.gmail.com',
        "MAIL_PORT": 465,
        "MAIL_USE_TLS": False,
        "MAIL_USE_SSL": True,
        "MAIL_USERNAME": '<EMAIL>',
        "MAIL_PASSWORD": '<PASSWORD>'
    }

    app.config.update(mail_settings)
    mail = Mail(app)


@auth.verify_password
def verify_password(username, password):
    if username in users and \
            check_password_hash(users.get(username), password):
        return username

@auth.get_user_roles
def get_user_roles(user):
    return roles.get(user)





@app.route('/')
def index():
    return render_template('index.html', title='OTY:n ilmot', page="index")


@app.route('/ots2021', methods=['GET', 'POST'])
def ots2021():
    form = ots2021Form()

    starttime = datetime(2021, 3, 5, 12, 00, 00)
    endtime = datetime(2021, 3, 12, 23, 59, 59)
    nowtime = datetime.now()

    limit = 4000
    maxlimit = 4000
    
    entrys = ots2021Model.query.all()
    count = ots2021Model.query.count()

    for entry in entrys:
        if((entry.etunimi == form.etunimi.data and entry.sukunimi == form.sukunimi.data) or entry.email == form.email.data):
            flash('Olet jo ilmoittautunut')

            return render_template('ots2021.html', title='ots2021 ilmoittautuminen',
                                    entrys=entrys,
                                    count=count,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    limit=limit,
                                    form=form,
                                    page="ots2021")

    if request.method == 'POST':
        validate = form.validate_on_submit()
        submitted = form.is_submitted()
    else:
        validate = False
        submitted = False

    if validate and submitted and count <= maxlimit:
        flash('Ilmoittautuminen onnistui')
        sub = ots2021Model(
            etunimi = form.etunimi.data,
            sukunimi = form.sukunimi.data,
            email = form.email.data,
            kilta = form.kilta.data,
            consent1 = form.consent1.data,
            consent0 = form.consent0.data,
            
            datetime = nowtime,
        )
        db.session.add(sub)
        db.session.commit()

        return render_template('ots2021_redirect.html')



    elif submitted and count > maxlimit:
        flash('IlmoittIlmoittautuminen on jo täynnä')

    elif (not validate) and submitted:
        flash('Ilmoittautuminen epäonnistui, tarkista syöttämäsi tiedot')


    return render_template('ots2021.html', title='ots2021 ilmottautuminen',
                            entrys=entrys,
                            count=count,
                            starttime=starttime,
                            endtime=endtime,
                            nowtime=nowtime,
                            limit=limit,
                            form=form,
                            page="ots2021")


@app.route('/ots2021_data', methods=['GET'])
@auth.login_required(role=['admin', 'meea'])
def ots2021_data():
    limit = 4000

    entries = ots2021Model.query.all()

    count = ots2021Model.query.count()

    return render_template('ots2021_data.html', title='ots2021 data',
                            entries=entries,
                            count=count,
                            limit=limit)

@app.route('/ots2021_data/ots2021Model.csv')
@auth.login_required(role=['admin', 'meea'])
def ots2021_csv():

    os.system('mkdir csv')

    sqlite_to_csv.exportToCSV('ots2021_model')

    dir = os.path.join(os.getcwd(), 'csv/')

    try:
        return send_from_directory(directory=dir, filename='ots2021_model_data.csv', as_attachment=True)
    except FileNotFoundError as e:
        abort(404)


@app.route('/kysely_arvonta_juttu_data', methods=['GET'])
@auth.login_required(role=['admin', 'kysely_arvonta_juttu'])
def kysely_arvonta_juttu_data():
    limit = 4000

    entries = kysely_arvonta_juttuModel.query.all()

    count = kysely_arvonta_juttuModel.query.count()

    return render_template('kysely_arvonta_juttu_data.html', title='kysely_arvonta_juttu data',
                           entries=entries,
                           count=count,
                           limit=limit)

@app.route('/kysely_arvonta_juttu_data/kysely_arvonta_juttu_model_data.csv')
@auth.login_required(role=['admin', 'kysely_arvonta_juttu'])
def kysely_arvonta_juttu_csv():

    os.system('mkdir csv')

    sqlite_to_csv.exportToCSV('kysely_arvonta_juttu_model')

    dir = os.path.join(os.getcwd(), 'csv/')
    
    try:
        print(dir)
        return send_from_directory(directory=dir, filename='kysely_arvonta_juttu_model_data.csv', as_attachment=True)
    except FileNotFoundError as e:
        print(e)
        abort(404)

@app.route('/fuksisitsit', methods=['GET', 'POST'])
def fuksisitsit():
    form = fuksisitsitForm()

    starttime = datetime(2021, 4, 9, 15, 00, 00)
    endtime = datetime(2021, 4, 15, 23, 59, 59)
    nowtime = datetime.now()

    limit = 4000

    count = 0


    entrys = fuksisitsitModel.query.all()

    totalCount = 0
    for entry in entrys:
        totalCount += 1


    for entry in entrys:
        if(entry.etunimi0 == form.etunimi0.data) and entry.sukunimi0 == form.sukunimi0.data:
            flash('Olet jo ilmoittautunut')
            return render_template('/2021/fuksisitsit.html', title='Fuksisitsit',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="fuksisitsit")


    if request.method == 'POST':
        validate = form.validate_on_submit()
        submitted = form.is_submitted()
    else:
        validate = False
        submitted = False

    if validate and submitted and limit > totalCount:
        flash('Ilmoittautuminen onnistui')
        sub = fuksisitsitModel(
            membercount = form.membercount.data,
            etunimi0 = form.etunimi0.data,
            sukunimi0 = form.sukunimi0.data,
            phone0 = form.phone0.data,
            email0 = form.email0.data,
            kilta0 = form.kilta0.data,
            vapaavalinta0 = form.vapaavalinta0.data,
            consent0 = form.consent0.data,
            consent1 = form.consent1.data,
            consent2 = form.consent2.data,

            datetime = nowtime
        )
        db.session.add(sub)
        db.session.commit()
        return redirect(url_for('fuksisitsit'))

    elif submitted and limit < totalCount:
        totalCount -= 1
        flash('Ilmoittautuminen on jo täynnä')

    elif (not validate) and submitted:
        flash('Ilmoittautuminen epäonnistui, tarkista syöttämäsi tiedot')


    return render_template('/2021/fuksisitsit.html', title='Fuksisitsit',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="fuksisitsit")

@app.route('/fuksisitsit_data', methods=['GET'])
@auth.login_required(role=['admin', 'ella', 'anssi', 'meea'])
def fuksisitsit_data():
    limit = 4000

    entries = fuksisitsitModel.query.all()

    count = fuksisitsitModel.query.count()

    return render_template('/2021/fuksisitsit_data.html', title='fuksisitsit data',
                           entries=entries,
                           count=count,
                           limit=limit)

@app.route('/fuksisitsit/fuksisitsit_model_data.csv')
@auth.login_required(role=['admin', 'ella'])
def fuksisitsit_csv():

    os.system('mkdir csv')

    sqlite_to_csv.exportToCSV('fuksisitsit_model')

    dir = os.path.join(os.getcwd(), 'csv/')
    
    try:
        print(dir)
        return send_from_directory(directory=dir, filename='fuksisitsit_model_data.csv', as_attachment=True)
    except FileNotFoundError as e:
        print(e)
        abort(404)

@app.route('/tupsufuksisitsit', methods=['GET', 'POST'])
def tupsufuksisitsit():
    form = tupsufuksisitsitForm()

    starttime = datetime(2021, 4, 9, 15, 00, 00)
    endtime = datetime(2021, 4, 14, 23, 59, 59)
    nowtime = datetime.now()

    limit = 4000

    count = 0


    entrys = tupsufuksisitsitModel.query.all()

    totalCount = 0
    for entry in entrys:
        totalCount += 1


    for entry in entrys:
        if(entry.etunimi0 == form.etunimi0.data) and entry.sukunimi0 == form.sukunimi0.data:
            flash('Olet jo ilmoittautunut')
            return render_template('/2021/tupsufuksisitsit.html', title='TupsuFuksisitsit',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="tupsufuksisitsit")


    if request.method == 'POST':
        validate = form.validate_on_submit()
        submitted = form.is_submitted()
    else:
        validate = False
        submitted = False

    if validate and submitted and limit > totalCount:
        flash('Ilmoittautuminen onnistui')
        sub = tupsufuksisitsitModel(
            membercount = form.membercount.data,
            etunimi0 = form.etunimi0.data,
            sukunimi0 = form.sukunimi0.data,
            phone0 = form.phone0.data,
            email0 = form.email0.data,
            kilta0 = form.kilta0.data,
            vapaavalinta0 = form.vapaavalinta0.data,
            consent0 = form.consent0.data,
            consent1 = form.consent1.data,
            consent2 = form.consent2.data,

            datetime = nowtime
        )
        db.session.add(sub)
        db.session.commit()
        return redirect(url_for('tupsufuksisitsit'))

    elif submitted and limit < totalCount:
        totalCount -= 1
        flash('Ilmoittautuminen on jo täynnä')

    elif (not validate) and submitted:
        flash('Ilmoittautuminen epäonnistui, tarkista syöttämäsi tiedot')


    return render_template('/2021/tupsufuksisitsit.html', title='TupsuFuksisitsit',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="tupsufuksisitsit")

@app.route('/tupsufuksisitsit_data', methods=['GET'])
@auth.login_required(role=['admin', 'ella', 'anssi', 'meea'])
def tupsufuksisitsit_data():
    limit = 4000

    entries = tupsufuksisitsitModel.query.all()

    count = tupsufuksisitsitModel.query.count()

    return render_template('/2021/tupsufuksisitsit_data.html', title='tupsufuksisitsit data',
                           entries=entries,
                           count=count,
                           limit=limit)

@app.route('/tupsufuksisitsit/tupsufuksisitsit_model_data.csv')
@auth.login_required(role=['admin', 'ella'])
def tupsufuksisitsit_csv():

    os.system('mkdir csv')

    sqlite_to_csv.exportToCSV('tupsufuksisitsit_model')

    dir = os.path.join(os.getcwd(), 'csv/')
    
    try:
        print(dir)
        return send_from_directory(directory=dir, filename='tupsufuksisitsit_model_data.csv', as_attachment=True)
    except FileNotFoundError as e:
        print(e)
        abort(404)

@app.route('/fuksibeerpong', methods=['GET', 'POST'])
def fuksibeerpong():
    form = fuksibeerpongForm()

    starttime = datetime(2021, 4, 13, 12, 00, 00)
    endtime = datetime(2021, 4, 18, 23, 59, 59)
    nowtime = datetime.now()

    limit = 32

    count = 0


    entrys = fuksibeerpongModel.query.all()

    totalCount = 0
    for entry in entrys:
        totalCount += 1


    for entry in entrys:
        if(entry.joukkue == form.joukkue.data):
            flash('Olet jo ilmoittautunut')
            return render_template('/2021/fuksibeerpong.html', title='Fuksibeerpong',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="fuksibeerpong")


    if request.method == 'POST':
        validate = form.validate_on_submit()
        submitted = form.is_submitted()
    else:
        validate = False
        submitted = False

    if validate and submitted and limit > totalCount:
        flash('Ilmoittautuminen onnistui')
        sub = fuksibeerpongModel(
            joukkue = form.joukkue.data,
            etunimi0 = form.etunimi0.data,
            sukunimi0 = form.sukunimi0.data,
            phone0 = form.phone0.data,
            email0 = form.email0.data,
            etunimi1 = form.etunimi1.data,
            sukunimi1 = form.sukunimi1.data,
            consent0 = form.consent0.data,
            consent1 = form.consent1.data,
            consent2 = form.consent2.data,

            datetime = nowtime
        )
        db.session.add(sub)
        db.session.commit()
        return redirect(url_for('fuksibeerpong'))

    elif submitted and limit < totalCount:
        totalCount -= 1
        flash('Ilmoittautuminen on jo täynnä')

    elif (not validate) and submitted:
        flash('Ilmoittautuminen epäonnistui, tarkista syöttämäsi tiedot')


    return render_template('/2021/fuksibeerpong.html', title='Fuksibeerpong',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="fuksibeerpong")

@app.route('/fuksibeerpong_data', methods=['GET'])
@auth.login_required(role=['admin', 'nikita', 'anssi'])
def fuksibeerpong_data():
    limit = 32

    entries = fuksibeerpongModel.query.all()

    count = fuksibeerpongModel.query.count()

    return render_template('/2021/fuksibeerpong_data.html', title='fuksibeerpong data',
                           entries=entries,
                           count=count,
                           limit=limit)

@app.route('/fuksibeerpong/fuksibeerpong_model_data.csv')
@auth.login_required(role=['admin', 'nikita', 'anssi'])
def fuksibeerpong_csv():

    os.system('mkdir csv')

    sqlite_to_csv.exportToCSV('fuksibeerpong_model')

    dir = os.path.join(os.getcwd(), 'csv/')
    
    try:
        print(dir)
        return send_from_directory(directory=dir, filename='fuksibeerpong_model_data.csv', as_attachment=True)
    except FileNotFoundError as e:
        print(e)
        abort(404)

@app.route('/beerpong', methods=['GET', 'POST'])
def beerpong():
    form = beerpongForm()

    starttime = datetime(2021, 4, 13, 16, 00, 00)
    endtime = datetime(2021, 4, 19, 23, 59, 59)
    nowtime = datetime.now()

    limit = 32

    count = 0


    entrys = beerpongModel.query.all()

    totalCount = 0
    for entry in entrys:
        totalCount += 1


    for entry in entrys:
        if(entry.joukkue == form.joukkue.data):
            flash('Olet jo ilmoittautunut')
            return render_template('/2021/beerpong.html', title='Beerpong',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="beerpong")


    if request.method == 'POST':
        validate = form.validate_on_submit()
        submitted = form.is_submitted()
    else:
        validate = False
        submitted = False

    if validate and submitted and limit > totalCount:
        flash('Ilmoittautuminen onnistui')
        sub = beerpongModel(
            joukkue = form.joukkue.data,
            etunimi0 = form.etunimi0.data,
            sukunimi0 = form.sukunimi0.data,
            phone0 = form.phone0.data,
            email0 = form.email0.data,
            etunimi1 = form.etunimi1.data,
            sukunimi1 = form.sukunimi1.data,
            consent0 = form.consent0.data,
            consent1 = form.consent1.data,
            consent2 = form.consent2.data,

            datetime = nowtime
        )
        db.session.add(sub)
        db.session.commit()
        return redirect(url_for('beerpong'))

    elif submitted and limit < totalCount:
        totalCount -= 1
        flash('Ilmoittautuminen on jo täynnä')

    elif (not validate) and submitted:
        flash('Ilmoittautuminen epäonnistui, tarkista syöttämäsi tiedot')


    return render_template('/2021/beerpong.html', title='beerpong',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="beerpong")

@app.route('/beerpong_data', methods=['GET'])
@auth.login_required(role=['admin', 'nikita', 'anssi'])
def beerpong_data():
    limit = 32

    entries = beerpongModel.query.all()

    count = beerpongModel.query.count()

    return render_template('/2021/beerpong_data.html', title='beerpong data',
                           entries=entries,
                           count=count,
                           limit=limit)

@app.route('/beerpong/beerpong_model_data.csv')
@auth.login_required(role=['admin', 'nikita', 'anssi'])
def beerpong_csv():

    os.system('mkdir csv')

    sqlite_to_csv.exportToCSV('beerpong_model')

    dir = os.path.join(os.getcwd(), 'csv/')
    
    try:
        print(dir)
        return send_from_directory(directory=dir, filename='beerpong_model_data.csv', as_attachment=True)
    except FileNotFoundError as e:
        print(e)
        abort(404)

@app.route('/aitienpaivalakitus', methods=['GET', 'POST'])
def aitienpaivalakitus():
    form = aitiForm()

    starttime = datetime(2021, 4, 13, 16, 00, 00)
    endtime = datetime(2021, 5, 5, 23, 59, 59)
    nowtime = datetime.now()

    limit = 4000

    count = 0


    entrys = aitiModel.query.all()

    totalCount = 0
    for entry in entrys:
        totalCount += 1


    for entry in entrys:
        if(entry.etunimi0 == form.etunimi0.data and entry.sukunimi0 == form.sukunimi0.data):
            flash('Olet jo ilmoittautunut')
            return render_template('/2021/aitienpaivalakitus.html', title='Äitienpäivälakitus',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="äitienpäivälakitus")


    if request.method == 'POST':
        validate = form.validate_on_submit()
        submitted = form.is_submitted()
    else:
        validate = False
        submitted = False

    if validate and submitted and limit > totalCount:
        flash('Ilmoittautuminen onnistui')
        sub = aitiModel(
            etunimi0 = form.etunimi0.data,
            sukunimi0 = form.sukunimi0.data,
            kilta0 = form.kilta0.data,
            phone0 = form.phone0.data,
            email0 = form.email0.data,
            consent1 = form.consent1.data,
            datetime = nowtime
        )
        db.session.add(sub)
        db.session.commit()
        return redirect(url_for('aitienpaivalakitus'))

    elif submitted and limit < totalCount:
        totalCount -= 1
        flash('Ilmoittautuminen on jo täynnä')

    elif (not validate) and submitted:
        flash('Ilmoittautuminen epäonnistui, tarkista syöttämäsi tiedot')


    return render_template('/2021/aitienpaivalakitus.html', title='Äitienpäivälakitus',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="Äitienpäivälakitus")

@app.route('/aitienpaivalakitus_data', methods=['GET'])
@auth.login_required(role=['admin', 'ella', 'anssi'])
def aitienpaivalakitus_data():
    limit = 4000

    entries = aitiModel.query.all()

    count = aitiModel.query.count()

    return render_template('/2021/aitienpaivalakitus_data.html', title='Äitienpäivälakitus data',
                           entries=entries,
                           count=count,
                           limit=limit)

@app.route('/beerpong/aitienpaivalakitus_model_data.csv')
@auth.login_required(role=['admin', 'ella', 'anssi'])
def aitienpaivalakitus_csv():

    os.system('mkdir csv')

    sqlite_to_csv.exportToCSV('aiti_model')

    dir = os.path.join(os.getcwd(), 'csv/')
    
    try:
        print(dir)
        return send_from_directory(directory=dir, filename='aiti_model_data.csv', as_attachment=True)
    except FileNotFoundError as e:
        print(e)
        abort(404)

@app.route('/hvsitsit', methods=['GET', 'POST'])
def hvsitsit():
    form = hvsitsituusiForm()

    starttime = datetime(2021, 4, 13, 16, 00, 00)
    endtime = datetime(2021, 12, 28, 23, 59, 59)
    nowtime = datetime.now()

    limit = 4000

    count = 0


    entrys = hvsitsituusiModel.query.all()

    totalCount = 0
    for entry in entrys:
        totalCount += 1


    for entry in entrys:
        if(entry.etunimi0 == form.etunimi0.data and entry.sukunimi0 == form.sukunimi0.data):
            flash('Olet jo ilmoittautunut')
            return render_template('/2021/hvsitsit.html', title='HV sitsit',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="HV sitsit")


    if request.method == 'POST':
        validate = form.validate_on_submit()
        submitted = form.is_submitted()
    else:
        validate = False
        submitted = False

    if validate and submitted and limit > totalCount:
        flash('Ilmoittautuminen onnistui')
        sub = hvsitsituusiModel(
            etunimi0 = form.etunimi0.data,
            sukunimi0 = form.sukunimi0.data,
            phone0 = form.phone0.data,
            email0 = form.email0.data,
            drink0 = form.drink0.data,
            wine0 = form.wine0.data,
            viina0 = form.viina0.data,
            other0 = form.other0.data,
            consent1 = form.consent1.data,
            datetime = nowtime
        )
        db.session.add(sub)
        db.session.commit()
        if KAPSI:
            if GMAIL:
                with app.app_context():
                    msg = Message(subject="Ilmoittautuminen hv sitseille 5.1. vastaanotettu",
                    sender=app.config.get("MAIL_USERNAME"),
                    recipients=[form.email0.data])
                    msg.html = render_template('/2021/mail/message_hvsitsit.html', user=form.data)
                    mail.send(msg)

            return redirect('https://ilmo.oty.fi/hvsitsit')
        else:
            return redirect(url_for('hvsitsit'))

    elif submitted and limit < totalCount:
        totalCount -= 1
        flash('Ilmoittautuminen on jo täynnä')

    elif (not validate) and submitted:
        flash('Ilmoittautuminen epäonnistui, tarkista syöttämäsi tiedot')


    return render_template('/2021/hvsitsit.html', title='HV sitsit',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="HV sitsit")

@app.route('/hvsitsit_data', methods=['GET'])
@auth.login_required(role=['admin', 'samu', 'anssi'])
def hvsitsit_data():
    limit = 4000

    entries = hvsitsituusiModel.query.all()

    count = hvsitsituusiModel.query.count()

    return render_template('/2021/hvsitsit_data.html', title='HV sitsien data',
                           entries=entries,
                           count=count,
                           limit=limit)

@app.route('/hvsitsit/hvsitsit_model_data.csv')
@auth.login_required(role=['admin', 'samu', 'anssi'])
def hvsitsit_csv():

    os.system('mkdir csv')

    sqlite_to_csv.exportToCSV('hvsitsituusi_model')

    dir = os.path.join(os.getcwd(), 'csv/')
    
    try:
        print(dir)
        return send_from_directory(directory=dir, filename='hvsitsituusi_model_data.csv', as_attachment=True)
    except FileNotFoundError as e:
        print(e)
        abort(404)

@app.route('/syysuitot', methods=['GET', 'POST'])
def syysuitot():
    form = syysuitotForm()

    starttime = datetime(2021, 8, 4, 16, 00, 00)
    endtime = datetime(2021, 8, 22, 23, 59, 59)
    nowtime = datetime.now()

    limit = 4000

    count = 0


    entrys = syysuitotModel.query.all()

    totalCount = 0
    for entry in entrys:
        totalCount += 1


    for entry in entrys:
        if(entry.etunimi0 == form.etunimi0.data and entry.sukunimi0 == form.sukunimi0.data):
            flash('Olet jo ilmoittautunut')
            return render_template('/2021/syysuitot.html', title='Syysuitot',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="Syysuitot")


    if request.method == 'POST':
        validate = form.validate_on_submit()
        submitted = form.is_submitted()
    else:
        validate = False
        submitted = False

    if validate and submitted and limit > totalCount:
        flash('Ilmoittautuminen onnistui')
        sub = syysuitotModel(
            etunimi0 = form.etunimi0.data,
            sukunimi0 = form.sukunimi0.data,
            phone0 = form.phone0.data,
            email0 = form.email0.data,
            kilta0 = form.kilta0.data,
            lakki0 = form.lakki0.data,
            consent0 = form.consent0.data,
            datetime = nowtime
        )
        db.session.add(sub)
        db.session.commit()
        return redirect(url_for('syysuitot'))

    elif submitted and limit < totalCount:
        totalCount -= 1
        flash('Ilmoittautuminen on jo täynnä')

    elif (not validate) and submitted:
        flash('Ilmoittautuminen epäonnistui, tarkista syöttämäsi tiedot')


    return render_template('/2021/syysuitot.html', title='Syysuitot',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="Syysuitot")

@app.route('/syysuitot_data', methods=['GET'])
@auth.login_required(role=['admin', 'anssi', 'meea', 'ella'])
def syysuitot_data():
    limit = 4000

    entries = syysuitotModel.query.all()

    count = syysuitotModel.query.count()

    return render_template('/2021/syysuitot_data.html', title='Syysuittojen data',
                           entries=entries,
                           count=count,
                           limit=limit)

@app.route('/syysuitot/syysuitot_model_data.csv')
@auth.login_required(role=['admin', 'ella', 'anssi', 'meea'])
def syysuitot_csv():

    os.system('mkdir csv')

    sqlite_to_csv.exportToCSV('syysuitot_model')

    dir = os.path.join(os.getcwd(), 'csv/')
    
    try:
        print(dir)
        return send_from_directory(directory=dir, filename='syysuitot_model_data.csv', as_attachment=True)
    except FileNotFoundError as e:
        print(e)
        abort(404)


@app.route('/prositsit', methods=['GET', 'POST'])
def prositsit():
    form = prositsitForm()

    starttime = datetime(2021, 8, 25, 16, 00, 00)
    endtime = datetime(2021, 9, 1, 23, 59, 59)
    nowtime = datetime.now()

    limit = 4000

    count = 0


    entrys = prositsitModel.query.all()

    totalCount = 0
    for entry in entrys:
        totalCount += 1


    for entry in entrys:
        if(entry.etunimi0 == form.etunimi0.data and entry.sukunimi0 == form.sukunimi0.data):
            flash('Olet jo ilmoittautunut')
            return render_template('/2021/prositsit.html', title='Pro-sitsit',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="Pro-sitsit")


    if request.method == 'POST':
        validate = form.validate_on_submit()
        submitted = form.is_submitted()
    else:
        validate = False
        submitted = False

    if validate and submitted and limit > totalCount:
        flash('Ilmoittautuminen onnistui')
        sub = prositsitModel(
            etunimi0 = form.etunimi0.data,
            sukunimi0 = form.sukunimi0.data,
            phone0 = form.phone0.data,
            email0 = form.email0.data,
            kilta0 = form.kilta0.data,
            drink0 = form.drink0.data,
            drink1 = form.drink1.data,
            drink2 = form.drink2.data,
            food0 = form.food0.data,
            allergies0 = form.allergies0.data,
            consent0 = form.consent0.data,
            datetime = nowtime
        )
        db.session.add(sub)
        db.session.commit()
        return redirect(url_for('prositsit'))

    elif submitted and limit < totalCount:
        totalCount -= 1
        flash('Ilmoittautuminen on jo täynnä')

    elif (not validate) and submitted:
        flash('Ilmoittautuminen epäonnistui, tarkista syöttämäsi tiedot')


    return render_template('/2021/prositsit.html', title='Pro-sitsit',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="Pro-sitsits")

@app.route('/prositsit_data', methods=['GET'])
@auth.login_required(role=['admin', 'anssi', 'meea', 'ella'])
def prositsit_data():
    limit = 4000

    entries = prositsitModel.query.all()

    count = prositsitModel.query.count()

    return render_template('/2021/prositsit_data.html', title='Prositsien data',
                           entries=entries,
                           count=count,
                           limit=limit)

@app.route('/prositsit/prositsit_model_data.csv')
@auth.login_required(role=['admin', 'ella', 'anssi', 'meea'])
def prositsit_csv():

    os.system('mkdir csv')

    sqlite_to_csv.exportToCSV('prositsit_model')

    dir = os.path.join(os.getcwd(), 'csv/')
    
    try:
        print(dir)
        return send_from_directory(directory=dir, filename='prositsit_model_data.csv', as_attachment=True)
    except FileNotFoundError as e:
        print(e)
        abort(404)

@app.route('/simailut', methods=['GET', 'POST'])
def simailut():
    form = simailutForm()

    starttime = datetime(2021, 9, 25, 16, 00, 00)
    endtime = datetime(2021, 11, 24, 23, 59, 59)
    nowtime = datetime.now()

    limit = 150

    count = 0


    entrys = simailutModel.query.all()

    totalCount = 0
    for entry in entrys:
        totalCount += 1


    for entry in entrys:
        if(entry.etunimi0 == form.etunimi0.data and entry.sukunimi0 == form.sukunimi0.data):
            flash('Olet jo ilmoittautunut')
            return render_template('/2021/simailut.html', title='Simailut',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="Simailut")


    if request.method == 'POST':
        validate = form.validate_on_submit()
        submitted = form.is_submitted()
    else:
        validate = False
        submitted = False

    if validate and submitted and limit > totalCount:
        flash('Ilmoittautuminen onnistui')
        sub = simailutModel(
            etunimi0 = form.etunimi0.data,
            sukunimi0 = form.sukunimi0.data,
            email0 = form.email0.data,
            special0 = form.special0.data,
            allergies0 = form.allergies0.data,
            avec0 = form.avec0.data,
            etunimi1 = form.etunimi1.data,
            sukunimi1 = form.sukunimi1.data,
            consent0 = form.consent0.data,
            datetime = nowtime
        )
        db.session.add(sub)
        db.session.commit()
        if form.avec0.data:
            sub1 = simailutModel(
                etunimi0 = form.etunimi1.data,
                sukunimi0 = form.sukunimi1.data,
                email0 = form.email1.data,
                special0 = form.special1.data,
                allergies0 = form.allergies1.data,
                avec0 = form.avec0.data,
                etunimi1 = form.etunimi0.data,
                sukunimi1 = form.sukunimi0.data,
                consent0 = form.consent0.data,
                datetime = nowtime
            )
            db.session.add(sub1)
            db.session.commit()
        return redirect(url_for('simailut'))

    elif submitted and limit < totalCount:
        totalCount -= 1
        flash('Ilmoittautuminen on jo täynnä')

    elif (not validate) and submitted:
        flash('Ilmoittautuminen epäonnistui, tarkista syöttämäsi tiedot')


    return render_template('/2021/simailut.html', title='Simailut',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="Simailut")

@app.route('/simailut_data', methods=['GET'])
@auth.login_required(role=['admin', 'anssi', 'meea', 'ella', 'alumni', 'nikita'])
def simailut_data():
    limit = 150

    entries = simailutModel.query.all()

    count = simailutModel.query.count()

    return render_template('/2021/simailut_data.html', title='Simailut data',
                           entries=entries,
                           count=count,
                           limit=limit)

@app.route('/simailut/simailut_model_data.csv')
@auth.login_required(role=['admin', 'ella', 'anssi', 'meea', 'alumni', 'nikita'])
def simailut_csv():

    os.system('mkdir csv')

    sqlite_to_csv.exportToCSV('simailut_model')

    dir = os.path.join(os.getcwd(), 'csv/')
    
    try:
        print(dir)
        return send_from_directory(directory=dir, filename='simailut_model_data.csv', as_attachment=True)
    except FileNotFoundError as e:
        print(e)
        abort(404)

@app.route('/kotiruokakurssi1', methods=['GET', 'POST'])
def kotiruokakurssi1():
    form = kotiruokakurssi1Form()

    starttime = datetime(2021, 12, 2, 15, 00, 00)
    endtime = datetime(2021, 12, 6, 20, 00, 00)
    nowtime = datetime.now()

    limit = 16

    count = 0


    entrys = kotiruokakurssi1Model.query.all()

    totalCount = 0
    for entry in entrys:
        totalCount += 1


    for entry in entrys:
        if(entry.etunimi0 == form.etunimi0.data and entry.sukunimi0 == form.sukunimi0.data):
            flash('Olet jo ilmoittautunut')
            return render_template('/2021/kotiruokakurssi1.html', title='Kotiruokakurssi 7.12.',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="Kotiruokakurssi 7.12.")


    if request.method == 'POST':
        validate = form.validate_on_submit()
        submitted = form.is_submitted()
    else:
        validate = False
        submitted = False

    if validate and submitted and limit > totalCount:
        flash('Ilmoittautuminen onnistui')
        sub = kotiruokakurssi1Model(
            etunimi0 = form.etunimi0.data,
            sukunimi0 = form.sukunimi0.data,
            email0 = form.email0.data,
            food0 = form.food0.data,
            allergies0 = form.allergies0.data,
            free0 = form.free0.data,
            consent0 = form.consent0.data,
            datetime = nowtime
        )
        db.session.add(sub)
        db.session.commit()


        if GMAIL:
            with app.app_context():
                msg = Message(subject="Ilmottautuminen Kotiruokakurssille 7.12. vastaanotettu",
                sender=app.config.get("MAIL_USERNAME"),
                recipients=[form.email0.data])
                msg.html = render_template('/2021/message_kotiruoka1.html', user=form.data)
                mail.send(msg)

        if KAPSI:
            return redirect('https://ilmo.oty.fi/kotiruokakurssi1')
        else:
            return redirect(url_for('kotiruokakurssi1'))

    elif submitted and limit < totalCount:
        totalCount -= 1
        flash('Ilmoittautuminen on jo täynnä, sinut on lisätty varasijalle.')
        sub = kotiruokakurssi1Model(
            etunimi0 = form.etunimi0.data,
            sukunimi0 = form.sukunimi0.data,
            email0 = form.email0.data,
            food0 = form.food0.data,
            allergies0 = form.allergies0.data,
            free0 = form.free0.data,
            consent0 = form.consent0.data,
            datetime = nowtime
        )
        db.session.add(sub)
        db.session.commit()


        if GMAIL:
            with app.app_context():
                msg = Message(subject="Olet varasijalla kotiruokakurssille 7.12.",
                sender=app.config.get("MAIL_USERNAME"),
                recipients=[form.email0.data])
                msg.html = render_template('/2021/message_kotiruoka1.html', user=form.data)
                mail.send(msg)

        if KAPSI:
            return redirect('https://ilmo.oty.fi/kotiruokakurssi1')
        else:
            return redirect(url_for('kotiruokakurssi1'))

    elif (not validate) and submitted:
        flash('Ilmoittautuminen epäonnistui, tarkista syöttämäsi tiedot')


    return render_template('/2021/kotiruokakurssi1.html', title='Kotiruokakurssi 7.12.',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="Simailut")

@app.route('/kotiruokakurssi1_data', methods=['GET'])
@auth.login_required(role=['admin', 'meea', 'kokkimestari'])
def kotiruokakurssi1_data():
    limit = 16

    entries = kotiruokakurssi1Model.query.all()

    count = kotiruokakurssi1Model.query.count()

    return render_template('/2021/kotiruokakurssi1_data.html', title='Kotiruokakurssi 7.12. data',
                           entries=entries,
                           count=count,
                           limit=limit)

@app.route('/ruokakurssi1/ruokakurssi1_model_data.csv')
@auth.login_required(role=['admin', 'anssi', 'kokkimestari'])
def kotiruokakurssi1_csv():

    os.system('mkdir csv')

    sqlite_to_csv.exportToCSV('kotiruokakurssi1_model')

    dir = os.path.join(os.getcwd(), 'csv/')
    
    try:
        print(dir)
        return send_from_directory(directory=dir, filename='kotiruokakurssi1_model_data.csv', as_attachment=True)
    except FileNotFoundError as e:
        print(e)
        abort(404)

@app.route('/kotiruokakurssi2', methods=['GET', 'POST'])
def kotiruokakurssi2():
    form = kotiruokakurssi2Form()

    starttime = datetime(2021, 12, 2, 15, 00, 00)
    endtime = datetime(2021, 12, 13, 20, 00, 00)
    nowtime = datetime.now()

    limit = 16

    count = 0


    entrys = kotiruokakurssi2Model.query.all()

    totalCount = 0
    for entry in entrys:
        totalCount += 1


    for entry in entrys:
        if(entry.etunimi0 == form.etunimi0.data and entry.sukunimi0 == form.sukunimi0.data):
            flash('Olet jo ilmoittautunut')
            return render_template('/2021/kotiruokakurssi2.html', title='Kotiruokakurssi 14.12.',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="Kotiruokakurssi 14.12.")


    if request.method == 'POST':
        validate = form.validate_on_submit()
        submitted = form.is_submitted()
    else:
        validate = False
        submitted = False

    if validate and submitted and limit > totalCount:
        flash('Ilmoittautuminen onnistui')
        sub = kotiruokakurssi2Model(
            etunimi0 = form.etunimi0.data,
            sukunimi0 = form.sukunimi0.data,
            email0 = form.email0.data,
            food0 = form.food0.data,
            allergies0 = form.allergies0.data,
            free0 = form.free0.data,
            consent0 = form.consent0.data,
            datetime = nowtime
        )
        db.session.add(sub)
        db.session.commit()

        if GMAIL:
            with app.app_context():
                msg = Message(subject="Ilmoittautuminen kotiruokakurssille 14.12. vastaanotettu",
                sender=app.config.get("MAIL_USERNAME"),
                recipients=[form.email0.data])
                msg.html = render_template('/2021/message_kotiruoka2.html', user=form.data)
                mail.send(msg)

        if KAPSI:
            return redirect('https://ilmo.oty.fi/kotiruokakurssi2')
        else:
            return redirect(url_for('kotiruokakurssi2'))

    elif submitted and limit < totalCount:
        totalCount -= 1
        flash('Ilmoittautuminen on jo täynnä')
        sub = kotiruokakurssi2Model(
            etunimi0 = form.etunimi0.data,
            sukunimi0 = form.sukunimi0.data,
            email0 = form.email0.data,
            food0 = form.food0.data,
            allergies0 = form.allergies0.data,
            free0 = form.free0.data,
            consent0 = form.consent0.data,
            datetime = nowtime
        )
        db.session.add(sub)
        db.session.commit()

        if GMAIL:
            with app.app_context():
                msg = Message(subject="Olet varasijalla kotiruokakurssille 14.12.",
                sender=app.config.get("MAIL_USERNAME"),
                recipients=[form.email0.data])
                msg.html = render_template('/2021/message_kotiruoka2.html', user=form.data)
                mail.send(msg)

        if KAPSI:
            return redirect('https://ilmo.oty.fi/kotiruokakurssi2')
        else:
            return redirect(url_for('kotiruokakurssi2'))

    elif (not validate) and submitted:
        flash('Ilmoittautuminen epäonnistui, tarkista syöttämäsi tiedot')


    return render_template('/2021/kotiruokakurssi2.html', title='Kotiruokakurssi 14.12.',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="Kotiruokakurssi2")

@app.route('/kotiruokakurssi2_data', methods=['GET'])
@auth.login_required(role=['admin', 'meea', 'kokkimestari'])
def kotiruokakurssi2_data():
    limit = 16

    entries = kotiruokakurssi2Model.query.all()

    count = kotiruokakurssi2Model.query.count()

    return render_template('/2021/kotiruokakurssi2_data.html', title='Kotiruokakurssi 14.12. data',
                           entries=entries,
                           count=count,
                           limit=limit)

@app.route('/ruokakurssi2/ruokakurssi2_model_data.csv')
@auth.login_required(role=['admin', 'anssi', 'kokkimestari'])
def kotiruokakurssi2_csv():

    os.system('mkdir csv')

    sqlite_to_csv.exportToCSV('kotiruokakurssi2_model')

    dir = os.path.join(os.getcwd(), 'csv/')
    
    try:
        print(dir)
        return send_from_directory(directory=dir, filename='kotiruokakurssi2_model_data.csv', as_attachment=True)
    except FileNotFoundError as e:
        print(e)
        abort(404)
from werkzeug.security import generate_password_hash, check_password_hash
from flask import Flask, render_template, url_for, redirect, request, flash, send_from_directory, session
from flask_httpauth import HTTPBasicAuth
from app import app, db
from flask_mail import Mail, Message
from datetime import datetime, date, time, timedelta
import requests
from sqlalchemy import and_
from app.forms import ots2021Form, fuksisitsitForm, tupsufuksisitsitForm, fuksibeerpongForm, beerpongForm, aitiForm, hvsitsituusiForm, syysuitotForm, prositsitForm, simailutForm, kotiruokakurssi1Form, kotiruokakurssi2Form
from app.models import ots2021Model, fuksisitsitModel, tupsufuksisitsitModel, fuksibeerpongModel, beerpongModel, aitiModel, hvsitsituusiModel, syysuitotModel, prositsitModel, simailutModel, kotiruokakurssi1Model, kotiruokakurssi2Model
from flask_wtf.csrf import CSRFProtect, CSRFError
import os
from app import sqlite_to_csv
from werkzeug.datastructures import MultiDict
from urllib.parse import urlparse, urlunparse
import subprocess
import time
import json

auth = HTTPBasicAuth()

csrf = CSRFProtect()

try:
    file = open("auth.conf", "r")
    lines = file.readlines()

    password = <PASSWORD>)
    print("created default user")
    print("username: admin")
    print("password: " + str(password))
    
    users = {
        "admin": generate_password_hash(password)
    }

    roles = {
        "admin": "admin"
    }

    for line in lines:
        new_user = line.split(",", 6)
        users[new_user[0]] = generate_password_hash(new_user[1])
        roles[new_user[0]] = new_user[2:6]


except FileNotFoundError as e:
    print(e)

    password = os.urandom(64)
    print("auth.conf not found, created default user")
    print("username: admin")
    print("password: " + str(password))
    
    users = {
        "admin": generate_password_hash(password)
    }

    roles = {
        "admin": "admin"
    }

else:
    file.close()


KAPSI = False

try:
    file = open("routes.conf", "r")
    lines = file.readlines()

    for line in lines:
        conf_line = line.split(":", 2)
        
        if "kapsi" in conf_line[0]:
            if "true" in conf_line[1]:
                KAPSI = True
            else:
                KAPSI = False

except FileNotFoundError as e:
    print(e)
    print("routes.conf not found")

else:
    file.close()


GMAIL = True

if GMAIL:
    
    mail_settings = {
        "MAIL_SERVER": 'smtp.gmail.com',
        "MAIL_PORT": 465,
        "MAIL_USE_TLS": False,
        "MAIL_USE_SSL": True,
        "MAIL_USERNAME": '<EMAIL>',
        "MAIL_PASSWORD": '<PASSWORD>'
    }

    app.config.update(mail_settings)
    mail = Mail(app)


@auth.verify_password
def verify_password(username, password):
    if username in users and \
            check_password_hash(users.get(username), password):
        return username

@auth.get_user_roles
def get_user_roles(user):
    return roles.get(user)





@app.route('/')
def index():
    return render_template('index.html', title='OTY:n ilmot', page="index")


@app.route('/ots2021', methods=['GET', 'POST'])
def ots2021():
    form = ots2021Form()

    starttime = datetime(2021, 3, 5, 12, 00, 00)
    endtime = datetime(2021, 3, 12, 23, 59, 59)
    nowtime = datetime.now()

    limit = 4000
    maxlimit = 4000
    
    entrys = ots2021Model.query.all()
    count = ots2021Model.query.count()

    for entry in entrys:
        if((entry.etunimi == form.etunimi.data and entry.sukunimi == form.sukunimi.data) or entry.email == form.email.data):
            flash('Olet jo ilmoittautunut')

            return render_template('ots2021.html', title='ots2021 ilmoittautuminen',
                                    entrys=entrys,
                                    count=count,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    limit=limit,
                                    form=form,
                                    page="ots2021")

    if request.method == 'POST':
        validate = form.validate_on_submit()
        submitted = form.is_submitted()
    else:
        validate = False
        submitted = False

    if validate and submitted and count <= maxlimit:
        flash('Ilmoittautuminen onnistui')
        sub = ots2021Model(
            etunimi = form.etunimi.data,
            sukunimi = form.sukunimi.data,
            email = form.email.data,
            kilta = form.kilta.data,
            consent1 = form.consent1.data,
            consent0 = form.consent0.data,
            
            datetime = nowtime,
        )
        db.session.add(sub)
        db.session.commit()

        return render_template('ots2021_redirect.html')



    elif submitted and count > maxlimit:
        flash('IlmoittIlmoittautuminen on jo täynnä')

    elif (not validate) and submitted:
        flash('Ilmoittautuminen epäonnistui, tarkista syöttämäsi tiedot')


    return render_template('ots2021.html', title='ots2021 ilmottautuminen',
                            entrys=entrys,
                            count=count,
                            starttime=starttime,
                            endtime=endtime,
                            nowtime=nowtime,
                            limit=limit,
                            form=form,
                            page="ots2021")


@app.route('/ots2021_data', methods=['GET'])
@auth.login_required(role=['admin', 'meea'])
def ots2021_data():
    limit = 4000

    entries = ots2021Model.query.all()

    count = ots2021Model.query.count()

    return render_template('ots2021_data.html', title='ots2021 data',
                            entries=entries,
                            count=count,
                            limit=limit)

@app.route('/ots2021_data/ots2021Model.csv')
@auth.login_required(role=['admin', 'meea'])
def ots2021_csv():

    os.system('mkdir csv')

    sqlite_to_csv.exportToCSV('ots2021_model')

    dir = os.path.join(os.getcwd(), 'csv/')

    try:
        return send_from_directory(directory=dir, filename='ots2021_model_data.csv', as_attachment=True)
    except FileNotFoundError as e:
        abort(404)


@app.route('/kysely_arvonta_juttu_data', methods=['GET'])
@auth.login_required(role=['admin', 'kysely_arvonta_juttu'])
def kysely_arvonta_juttu_data():
    limit = 4000

    entries = kysely_arvonta_juttuModel.query.all()

    count = kysely_arvonta_juttuModel.query.count()

    return render_template('kysely_arvonta_juttu_data.html', title='kysely_arvonta_juttu data',
                           entries=entries,
                           count=count,
                           limit=limit)

@app.route('/kysely_arvonta_juttu_data/kysely_arvonta_juttu_model_data.csv')
@auth.login_required(role=['admin', 'kysely_arvonta_juttu'])
def kysely_arvonta_juttu_csv():

    os.system('mkdir csv')

    sqlite_to_csv.exportToCSV('kysely_arvonta_juttu_model')

    dir = os.path.join(os.getcwd(), 'csv/')
    
    try:
        print(dir)
        return send_from_directory(directory=dir, filename='kysely_arvonta_juttu_model_data.csv', as_attachment=True)
    except FileNotFoundError as e:
        print(e)
        abort(404)

@app.route('/fuksisitsit', methods=['GET', 'POST'])
def fuksisitsit():
    form = fuksisitsitForm()

    starttime = datetime(2021, 4, 9, 15, 00, 00)
    endtime = datetime(2021, 4, 15, 23, 59, 59)
    nowtime = datetime.now()

    limit = 4000

    count = 0


    entrys = fuksisitsitModel.query.all()

    totalCount = 0
    for entry in entrys:
        totalCount += 1


    for entry in entrys:
        if(entry.etunimi0 == form.etunimi0.data) and entry.sukunimi0 == form.sukunimi0.data:
            flash('Olet jo ilmoittautunut')
            return render_template('/2021/fuksisitsit.html', title='Fuksisitsit',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="fuksisitsit")


    if request.method == 'POST':
        validate = form.validate_on_submit()
        submitted = form.is_submitted()
    else:
        validate = False
        submitted = False

    if validate and submitted and limit > totalCount:
        flash('Ilmoittautuminen onnistui')
        sub = fuksisitsitModel(
            membercount = form.membercount.data,
            etunimi0 = form.etunimi0.data,
            sukunimi0 = form.sukunimi0.data,
            phone0 = form.phone0.data,
            email0 = form.email0.data,
            kilta0 = form.kilta0.data,
            vapaavalinta0 = form.vapaavalinta0.data,
            consent0 = form.consent0.data,
            consent1 = form.consent1.data,
            consent2 = form.consent2.data,

            datetime = nowtime
        )
        db.session.add(sub)
        db.session.commit()
        return redirect(url_for('fuksisitsit'))

    elif submitted and limit < totalCount:
        totalCount -= 1
        flash('Ilmoittautuminen on jo täynnä')

    elif (not validate) and submitted:
        flash('Ilmoittautuminen epäonnistui, tarkista syöttämäsi tiedot')


    return render_template('/2021/fuksisitsit.html', title='Fuksisitsit',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="fuksisitsit")

@app.route('/fuksisitsit_data', methods=['GET'])
@auth.login_required(role=['admin', 'ella', 'anssi', 'meea'])
def fuksisitsit_data():
    limit = 4000

    entries = fuksisitsitModel.query.all()

    count = fuksisitsitModel.query.count()

    return render_template('/2021/fuksisitsit_data.html', title='fuksisitsit data',
                           entries=entries,
                           count=count,
                           limit=limit)

@app.route('/fuksisitsit/fuksisitsit_model_data.csv')
@auth.login_required(role=['admin', 'ella'])
def fuksisitsit_csv():

    os.system('mkdir csv')

    sqlite_to_csv.exportToCSV('fuksisitsit_model')

    dir = os.path.join(os.getcwd(), 'csv/')
    
    try:
        print(dir)
        return send_from_directory(directory=dir, filename='fuksisitsit_model_data.csv', as_attachment=True)
    except FileNotFoundError as e:
        print(e)
        abort(404)

@app.route('/tupsufuksisitsit', methods=['GET', 'POST'])
def tupsufuksisitsit():
    form = tupsufuksisitsitForm()

    starttime = datetime(2021, 4, 9, 15, 00, 00)
    endtime = datetime(2021, 4, 14, 23, 59, 59)
    nowtime = datetime.now()

    limit = 4000

    count = 0


    entrys = tupsufuksisitsitModel.query.all()

    totalCount = 0
    for entry in entrys:
        totalCount += 1


    for entry in entrys:
        if(entry.etunimi0 == form.etunimi0.data) and entry.sukunimi0 == form.sukunimi0.data:
            flash('Olet jo ilmoittautunut')
            return render_template('/2021/tupsufuksisitsit.html', title='TupsuFuksisitsit',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="tupsufuksisitsit")


    if request.method == 'POST':
        validate = form.validate_on_submit()
        submitted = form.is_submitted()
    else:
        validate = False
        submitted = False

    if validate and submitted and limit > totalCount:
        flash('Ilmoittautuminen onnistui')
        sub = tupsufuksisitsitModel(
            membercount = form.membercount.data,
            etunimi0 = form.etunimi0.data,
            sukunimi0 = form.sukunimi0.data,
            phone0 = form.phone0.data,
            email0 = form.email0.data,
            kilta0 = form.kilta0.data,
            vapaavalinta0 = form.vapaavalinta0.data,
            consent0 = form.consent0.data,
            consent1 = form.consent1.data,
            consent2 = form.consent2.data,

            datetime = nowtime
        )
        db.session.add(sub)
        db.session.commit()
        return redirect(url_for('tupsufuksisitsit'))

    elif submitted and limit < totalCount:
        totalCount -= 1
        flash('Ilmoittautuminen on jo täynnä')

    elif (not validate) and submitted:
        flash('Ilmoittautuminen epäonnistui, tarkista syöttämäsi tiedot')


    return render_template('/2021/tupsufuksisitsit.html', title='TupsuFuksisitsit',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="tupsufuksisitsit")

@app.route('/tupsufuksisitsit_data', methods=['GET'])
@auth.login_required(role=['admin', 'ella', 'anssi', 'meea'])
def tupsufuksisitsit_data():
    limit = 4000

    entries = tupsufuksisitsitModel.query.all()

    count = tupsufuksisitsitModel.query.count()

    return render_template('/2021/tupsufuksisitsit_data.html', title='tupsufuksisitsit data',
                           entries=entries,
                           count=count,
                           limit=limit)

@app.route('/tupsufuksisitsit/tupsufuksisitsit_model_data.csv')
@auth.login_required(role=['admin', 'ella'])
def tupsufuksisitsit_csv():

    os.system('mkdir csv')

    sqlite_to_csv.exportToCSV('tupsufuksisitsit_model')

    dir = os.path.join(os.getcwd(), 'csv/')
    
    try:
        print(dir)
        return send_from_directory(directory=dir, filename='tupsufuksisitsit_model_data.csv', as_attachment=True)
    except FileNotFoundError as e:
        print(e)
        abort(404)

@app.route('/fuksibeerpong', methods=['GET', 'POST'])
def fuksibeerpong():
    form = fuksibeerpongForm()

    starttime = datetime(2021, 4, 13, 12, 00, 00)
    endtime = datetime(2021, 4, 18, 23, 59, 59)
    nowtime = datetime.now()

    limit = 32

    count = 0


    entrys = fuksibeerpongModel.query.all()

    totalCount = 0
    for entry in entrys:
        totalCount += 1


    for entry in entrys:
        if(entry.joukkue == form.joukkue.data):
            flash('Olet jo ilmoittautunut')
            return render_template('/2021/fuksibeerpong.html', title='Fuksibeerpong',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="fuksibeerpong")


    if request.method == 'POST':
        validate = form.validate_on_submit()
        submitted = form.is_submitted()
    else:
        validate = False
        submitted = False

    if validate and submitted and limit > totalCount:
        flash('Ilmoittautuminen onnistui')
        sub = fuksibeerpongModel(
            joukkue = form.joukkue.data,
            etunimi0 = form.etunimi0.data,
            sukunimi0 = form.sukunimi0.data,
            phone0 = form.phone0.data,
            email0 = form.email0.data,
            etunimi1 = form.etunimi1.data,
            sukunimi1 = form.sukunimi1.data,
            consent0 = form.consent0.data,
            consent1 = form.consent1.data,
            consent2 = form.consent2.data,

            datetime = nowtime
        )
        db.session.add(sub)
        db.session.commit()
        return redirect(url_for('fuksibeerpong'))

    elif submitted and limit < totalCount:
        totalCount -= 1
        flash('Ilmoittautuminen on jo täynnä')

    elif (not validate) and submitted:
        flash('Ilmoittautuminen epäonnistui, tarkista syöttämäsi tiedot')


    return render_template('/2021/fuksibeerpong.html', title='Fuksibeerpong',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="fuksibeerpong")

@app.route('/fuksibeerpong_data', methods=['GET'])
@auth.login_required(role=['admin', 'nikita', 'anssi'])
def fuksibeerpong_data():
    limit = 32

    entries = fuksibeerpongModel.query.all()

    count = fuksibeerpongModel.query.count()

    return render_template('/2021/fuksibeerpong_data.html', title='fuksibeerpong data',
                           entries=entries,
                           count=count,
                           limit=limit)

@app.route('/fuksibeerpong/fuksibeerpong_model_data.csv')
@auth.login_required(role=['admin', 'nikita', 'anssi'])
def fuksibeerpong_csv():

    os.system('mkdir csv')

    sqlite_to_csv.exportToCSV('fuksibeerpong_model')

    dir = os.path.join(os.getcwd(), 'csv/')
    
    try:
        print(dir)
        return send_from_directory(directory=dir, filename='fuksibeerpong_model_data.csv', as_attachment=True)
    except FileNotFoundError as e:
        print(e)
        abort(404)

@app.route('/beerpong', methods=['GET', 'POST'])
def beerpong():
    form = beerpongForm()

    starttime = datetime(2021, 4, 13, 16, 00, 00)
    endtime = datetime(2021, 4, 19, 23, 59, 59)
    nowtime = datetime.now()

    limit = 32

    count = 0


    entrys = beerpongModel.query.all()

    totalCount = 0
    for entry in entrys:
        totalCount += 1


    for entry in entrys:
        if(entry.joukkue == form.joukkue.data):
            flash('Olet jo ilmoittautunut')
            return render_template('/2021/beerpong.html', title='Beerpong',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="beerpong")


    if request.method == 'POST':
        validate = form.validate_on_submit()
        submitted = form.is_submitted()
    else:
        validate = False
        submitted = False

    if validate and submitted and limit > totalCount:
        flash('Ilmoittautuminen onnistui')
        sub = beerpongModel(
            joukkue = form.joukkue.data,
            etunimi0 = form.etunimi0.data,
            sukunimi0 = form.sukunimi0.data,
            phone0 = form.phone0.data,
            email0 = form.email0.data,
            etunimi1 = form.etunimi1.data,
            sukunimi1 = form.sukunimi1.data,
            consent0 = form.consent0.data,
            consent1 = form.consent1.data,
            consent2 = form.consent2.data,

            datetime = nowtime
        )
        db.session.add(sub)
        db.session.commit()
        return redirect(url_for('beerpong'))

    elif submitted and limit < totalCount:
        totalCount -= 1
        flash('Ilmoittautuminen on jo täynnä')

    elif (not validate) and submitted:
        flash('Ilmoittautuminen epäonnistui, tarkista syöttämäsi tiedot')


    return render_template('/2021/beerpong.html', title='beerpong',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="beerpong")

@app.route('/beerpong_data', methods=['GET'])
@auth.login_required(role=['admin', 'nikita', 'anssi'])
def beerpong_data():
    limit = 32

    entries = beerpongModel.query.all()

    count = beerpongModel.query.count()

    return render_template('/2021/beerpong_data.html', title='beerpong data',
                           entries=entries,
                           count=count,
                           limit=limit)

@app.route('/beerpong/beerpong_model_data.csv')
@auth.login_required(role=['admin', 'nikita', 'anssi'])
def beerpong_csv():

    os.system('mkdir csv')

    sqlite_to_csv.exportToCSV('beerpong_model')

    dir = os.path.join(os.getcwd(), 'csv/')
    
    try:
        print(dir)
        return send_from_directory(directory=dir, filename='beerpong_model_data.csv', as_attachment=True)
    except FileNotFoundError as e:
        print(e)
        abort(404)

@app.route('/aitienpaivalakitus', methods=['GET', 'POST'])
def aitienpaivalakitus():
    form = aitiForm()

    starttime = datetime(2021, 4, 13, 16, 00, 00)
    endtime = datetime(2021, 5, 5, 23, 59, 59)
    nowtime = datetime.now()

    limit = 4000

    count = 0


    entrys = aitiModel.query.all()

    totalCount = 0
    for entry in entrys:
        totalCount += 1


    for entry in entrys:
        if(entry.etunimi0 == form.etunimi0.data and entry.sukunimi0 == form.sukunimi0.data):
            flash('Olet jo ilmoittautunut')
            return render_template('/2021/aitienpaivalakitus.html', title='Äitienpäivälakitus',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="äitienpäivälakitus")


    if request.method == 'POST':
        validate = form.validate_on_submit()
        submitted = form.is_submitted()
    else:
        validate = False
        submitted = False

    if validate and submitted and limit > totalCount:
        flash('Ilmoittautuminen onnistui')
        sub = aitiModel(
            etunimi0 = form.etunimi0.data,
            sukunimi0 = form.sukunimi0.data,
            kilta0 = form.kilta0.data,
            phone0 = form.phone0.data,
            email0 = form.email0.data,
            consent1 = form.consent1.data,
            datetime = nowtime
        )
        db.session.add(sub)
        db.session.commit()
        return redirect(url_for('aitienpaivalakitus'))

    elif submitted and limit < totalCount:
        totalCount -= 1
        flash('Ilmoittautuminen on jo täynnä')

    elif (not validate) and submitted:
        flash('Ilmoittautuminen epäonnistui, tarkista syöttämäsi tiedot')


    return render_template('/2021/aitienpaivalakitus.html', title='Äitienpäivälakitus',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="Äitienpäivälakitus")

@app.route('/aitienpaivalakitus_data', methods=['GET'])
@auth.login_required(role=['admin', 'ella', 'anssi'])
def aitienpaivalakitus_data():
    limit = 4000

    entries = aitiModel.query.all()

    count = aitiModel.query.count()

    return render_template('/2021/aitienpaivalakitus_data.html', title='Äitienpäivälakitus data',
                           entries=entries,
                           count=count,
                           limit=limit)

@app.route('/beerpong/aitienpaivalakitus_model_data.csv')
@auth.login_required(role=['admin', 'ella', 'anssi'])
def aitienpaivalakitus_csv():

    os.system('mkdir csv')

    sqlite_to_csv.exportToCSV('aiti_model')

    dir = os.path.join(os.getcwd(), 'csv/')
    
    try:
        print(dir)
        return send_from_directory(directory=dir, filename='aiti_model_data.csv', as_attachment=True)
    except FileNotFoundError as e:
        print(e)
        abort(404)

@app.route('/hvsitsit', methods=['GET', 'POST'])
def hvsitsit():
    form = hvsitsituusiForm()

    starttime = datetime(2021, 4, 13, 16, 00, 00)
    endtime = datetime(2021, 12, 28, 23, 59, 59)
    nowtime = datetime.now()

    limit = 4000

    count = 0


    entrys = hvsitsituusiModel.query.all()

    totalCount = 0
    for entry in entrys:
        totalCount += 1


    for entry in entrys:
        if(entry.etunimi0 == form.etunimi0.data and entry.sukunimi0 == form.sukunimi0.data):
            flash('Olet jo ilmoittautunut')
            return render_template('/2021/hvsitsit.html', title='HV sitsit',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="HV sitsit")


    if request.method == 'POST':
        validate = form.validate_on_submit()
        submitted = form.is_submitted()
    else:
        validate = False
        submitted = False

    if validate and submitted and limit > totalCount:
        flash('Ilmoittautuminen onnistui')
        sub = hvsitsituusiModel(
            etunimi0 = form.etunimi0.data,
            sukunimi0 = form.sukunimi0.data,
            phone0 = form.phone0.data,
            email0 = form.email0.data,
            drink0 = form.drink0.data,
            wine0 = form.wine0.data,
            viina0 = form.viina0.data,
            other0 = form.other0.data,
            consent1 = form.consent1.data,
            datetime = nowtime
        )
        db.session.add(sub)
        db.session.commit()
        if KAPSI:
            if GMAIL:
                with app.app_context():
                    msg = Message(subject="Ilmoittautuminen hv sitseille 5.1. vastaanotettu",
                    sender=app.config.get("MAIL_USERNAME"),
                    recipients=[form.email0.data])
                    msg.html = render_template('/2021/mail/message_hvsitsit.html', user=form.data)
                    mail.send(msg)

            return redirect('https://ilmo.oty.fi/hvsitsit')
        else:
            return redirect(url_for('hvsitsit'))

    elif submitted and limit < totalCount:
        totalCount -= 1
        flash('Ilmoittautuminen on jo täynnä')

    elif (not validate) and submitted:
        flash('Ilmoittautuminen epäonnistui, tarkista syöttämäsi tiedot')


    return render_template('/2021/hvsitsit.html', title='HV sitsit',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="HV sitsit")

@app.route('/hvsitsit_data', methods=['GET'])
@auth.login_required(role=['admin', 'samu', 'anssi'])
def hvsitsit_data():
    limit = 4000

    entries = hvsitsituusiModel.query.all()

    count = hvsitsituusiModel.query.count()

    return render_template('/2021/hvsitsit_data.html', title='HV sitsien data',
                           entries=entries,
                           count=count,
                           limit=limit)

@app.route('/hvsitsit/hvsitsit_model_data.csv')
@auth.login_required(role=['admin', 'samu', 'anssi'])
def hvsitsit_csv():

    os.system('mkdir csv')

    sqlite_to_csv.exportToCSV('hvsitsituusi_model')

    dir = os.path.join(os.getcwd(), 'csv/')
    
    try:
        print(dir)
        return send_from_directory(directory=dir, filename='hvsitsituusi_model_data.csv', as_attachment=True)
    except FileNotFoundError as e:
        print(e)
        abort(404)

@app.route('/syysuitot', methods=['GET', 'POST'])
def syysuitot():
    form = syysuitotForm()

    starttime = datetime(2021, 8, 4, 16, 00, 00)
    endtime = datetime(2021, 8, 22, 23, 59, 59)
    nowtime = datetime.now()

    limit = 4000

    count = 0


    entrys = syysuitotModel.query.all()

    totalCount = 0
    for entry in entrys:
        totalCount += 1


    for entry in entrys:
        if(entry.etunimi0 == form.etunimi0.data and entry.sukunimi0 == form.sukunimi0.data):
            flash('Olet jo ilmoittautunut')
            return render_template('/2021/syysuitot.html', title='Syysuitot',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="Syysuitot")


    if request.method == 'POST':
        validate = form.validate_on_submit()
        submitted = form.is_submitted()
    else:
        validate = False
        submitted = False

    if validate and submitted and limit > totalCount:
        flash('Ilmoittautuminen onnistui')
        sub = syysuitotModel(
            etunimi0 = form.etunimi0.data,
            sukunimi0 = form.sukunimi0.data,
            phone0 = form.phone0.data,
            email0 = form.email0.data,
            kilta0 = form.kilta0.data,
            lakki0 = form.lakki0.data,
            consent0 = form.consent0.data,
            datetime = nowtime
        )
        db.session.add(sub)
        db.session.commit()
        return redirect(url_for('syysuitot'))

    elif submitted and limit < totalCount:
        totalCount -= 1
        flash('Ilmoittautuminen on jo täynnä')

    elif (not validate) and submitted:
        flash('Ilmoittautuminen epäonnistui, tarkista syöttämäsi tiedot')


    return render_template('/2021/syysuitot.html', title='Syysuitot',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="Syysuitot")

@app.route('/syysuitot_data', methods=['GET'])
@auth.login_required(role=['admin', 'anssi', 'meea', 'ella'])
def syysuitot_data():
    limit = 4000

    entries = syysuitotModel.query.all()

    count = syysuitotModel.query.count()

    return render_template('/2021/syysuitot_data.html', title='Syysuittojen data',
                           entries=entries,
                           count=count,
                           limit=limit)

@app.route('/syysuitot/syysuitot_model_data.csv')
@auth.login_required(role=['admin', 'ella', 'anssi', 'meea'])
def syysuitot_csv():

    os.system('mkdir csv')

    sqlite_to_csv.exportToCSV('syysuitot_model')

    dir = os.path.join(os.getcwd(), 'csv/')
    
    try:
        print(dir)
        return send_from_directory(directory=dir, filename='syysuitot_model_data.csv', as_attachment=True)
    except FileNotFoundError as e:
        print(e)
        abort(404)


@app.route('/prositsit', methods=['GET', 'POST'])
def prositsit():
    form = prositsitForm()

    starttime = datetime(2021, 8, 25, 16, 00, 00)
    endtime = datetime(2021, 9, 1, 23, 59, 59)
    nowtime = datetime.now()

    limit = 4000

    count = 0


    entrys = prositsitModel.query.all()

    totalCount = 0
    for entry in entrys:
        totalCount += 1


    for entry in entrys:
        if(entry.etunimi0 == form.etunimi0.data and entry.sukunimi0 == form.sukunimi0.data):
            flash('Olet jo ilmoittautunut')
            return render_template('/2021/prositsit.html', title='Pro-sitsit',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="Pro-sitsit")


    if request.method == 'POST':
        validate = form.validate_on_submit()
        submitted = form.is_submitted()
    else:
        validate = False
        submitted = False

    if validate and submitted and limit > totalCount:
        flash('Ilmoittautuminen onnistui')
        sub = prositsitModel(
            etunimi0 = form.etunimi0.data,
            sukunimi0 = form.sukunimi0.data,
            phone0 = form.phone0.data,
            email0 = form.email0.data,
            kilta0 = form.kilta0.data,
            drink0 = form.drink0.data,
            drink1 = form.drink1.data,
            drink2 = form.drink2.data,
            food0 = form.food0.data,
            allergies0 = form.allergies0.data,
            consent0 = form.consent0.data,
            datetime = nowtime
        )
        db.session.add(sub)
        db.session.commit()
        return redirect(url_for('prositsit'))

    elif submitted and limit < totalCount:
        totalCount -= 1
        flash('Ilmoittautuminen on jo täynnä')

    elif (not validate) and submitted:
        flash('Ilmoittautuminen epäonnistui, tarkista syöttämäsi tiedot')


    return render_template('/2021/prositsit.html', title='Pro-sitsit',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="Pro-sitsits")

@app.route('/prositsit_data', methods=['GET'])
@auth.login_required(role=['admin', 'anssi', 'meea', 'ella'])
def prositsit_data():
    limit = 4000

    entries = prositsitModel.query.all()

    count = prositsitModel.query.count()

    return render_template('/2021/prositsit_data.html', title='Prositsien data',
                           entries=entries,
                           count=count,
                           limit=limit)

@app.route('/prositsit/prositsit_model_data.csv')
@auth.login_required(role=['admin', 'ella', 'anssi', 'meea'])
def prositsit_csv():

    os.system('mkdir csv')

    sqlite_to_csv.exportToCSV('prositsit_model')

    dir = os.path.join(os.getcwd(), 'csv/')
    
    try:
        print(dir)
        return send_from_directory(directory=dir, filename='prositsit_model_data.csv', as_attachment=True)
    except FileNotFoundError as e:
        print(e)
        abort(404)

@app.route('/simailut', methods=['GET', 'POST'])
def simailut():
    form = simailutForm()

    starttime = datetime(2021, 9, 25, 16, 00, 00)
    endtime = datetime(2021, 11, 24, 23, 59, 59)
    nowtime = datetime.now()

    limit = 150

    count = 0


    entrys = simailutModel.query.all()

    totalCount = 0
    for entry in entrys:
        totalCount += 1


    for entry in entrys:
        if(entry.etunimi0 == form.etunimi0.data and entry.sukunimi0 == form.sukunimi0.data):
            flash('Olet jo ilmoittautunut')
            return render_template('/2021/simailut.html', title='Simailut',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="Simailut")


    if request.method == 'POST':
        validate = form.validate_on_submit()
        submitted = form.is_submitted()
    else:
        validate = False
        submitted = False

    if validate and submitted and limit > totalCount:
        flash('Ilmoittautuminen onnistui')
        sub = simailutModel(
            etunimi0 = form.etunimi0.data,
            sukunimi0 = form.sukunimi0.data,
            email0 = form.email0.data,
            special0 = form.special0.data,
            allergies0 = form.allergies0.data,
            avec0 = form.avec0.data,
            etunimi1 = form.etunimi1.data,
            sukunimi1 = form.sukunimi1.data,
            consent0 = form.consent0.data,
            datetime = nowtime
        )
        db.session.add(sub)
        db.session.commit()
        if form.avec0.data:
            sub1 = simailutModel(
                etunimi0 = form.etunimi1.data,
                sukunimi0 = form.sukunimi1.data,
                email0 = form.email1.data,
                special0 = form.special1.data,
                allergies0 = form.allergies1.data,
                avec0 = form.avec0.data,
                etunimi1 = form.etunimi0.data,
                sukunimi1 = form.sukunimi0.data,
                consent0 = form.consent0.data,
                datetime = nowtime
            )
            db.session.add(sub1)
            db.session.commit()
        return redirect(url_for('simailut'))

    elif submitted and limit < totalCount:
        totalCount -= 1
        flash('Ilmoittautuminen on jo täynnä')

    elif (not validate) and submitted:
        flash('Ilmoittautuminen epäonnistui, tarkista syöttämäsi tiedot')


    return render_template('/2021/simailut.html', title='Simailut',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="Simailut")

@app.route('/simailut_data', methods=['GET'])
@auth.login_required(role=['admin', 'anssi', 'meea', 'ella', 'alumni', 'nikita'])
def simailut_data():
    limit = 150

    entries = simailutModel.query.all()

    count = simailutModel.query.count()

    return render_template('/2021/simailut_data.html', title='Simailut data',
                           entries=entries,
                           count=count,
                           limit=limit)

@app.route('/simailut/simailut_model_data.csv')
@auth.login_required(role=['admin', 'ella', 'anssi', 'meea', 'alumni', 'nikita'])
def simailut_csv():

    os.system('mkdir csv')

    sqlite_to_csv.exportToCSV('simailut_model')

    dir = os.path.join(os.getcwd(), 'csv/')
    
    try:
        print(dir)
        return send_from_directory(directory=dir, filename='simailut_model_data.csv', as_attachment=True)
    except FileNotFoundError as e:
        print(e)
        abort(404)

@app.route('/kotiruokakurssi1', methods=['GET', 'POST'])
def kotiruokakurssi1():
    form = kotiruokakurssi1Form()

    starttime = datetime(2021, 12, 2, 15, 00, 00)
    endtime = datetime(2021, 12, 6, 20, 00, 00)
    nowtime = datetime.now()

    limit = 16

    count = 0


    entrys = kotiruokakurssi1Model.query.all()

    totalCount = 0
    for entry in entrys:
        totalCount += 1


    for entry in entrys:
        if(entry.etunimi0 == form.etunimi0.data and entry.sukunimi0 == form.sukunimi0.data):
            flash('Olet jo ilmoittautunut')
            return render_template('/2021/kotiruokakurssi1.html', title='Kotiruokakurssi 7.12.',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="Kotiruokakurssi 7.12.")


    if request.method == 'POST':
        validate = form.validate_on_submit()
        submitted = form.is_submitted()
    else:
        validate = False
        submitted = False

    if validate and submitted and limit > totalCount:
        flash('Ilmoittautuminen onnistui')
        sub = kotiruokakurssi1Model(
            etunimi0 = form.etunimi0.data,
            sukunimi0 = form.sukunimi0.data,
            email0 = form.email0.data,
            food0 = form.food0.data,
            allergies0 = form.allergies0.data,
            free0 = form.free0.data,
            consent0 = form.consent0.data,
            datetime = nowtime
        )
        db.session.add(sub)
        db.session.commit()


        if GMAIL:
            with app.app_context():
                msg = Message(subject="Ilmottautuminen Kotiruokakurssille 7.12. vastaanotettu",
                sender=app.config.get("MAIL_USERNAME"),
                recipients=[form.email0.data])
                msg.html = render_template('/2021/message_kotiruoka1.html', user=form.data)
                mail.send(msg)

        if KAPSI:
            return redirect('https://ilmo.oty.fi/kotiruokakurssi1')
        else:
            return redirect(url_for('kotiruokakurssi1'))

    elif submitted and limit < totalCount:
        totalCount -= 1
        flash('Ilmoittautuminen on jo täynnä, sinut on lisätty varasijalle.')
        sub = kotiruokakurssi1Model(
            etunimi0 = form.etunimi0.data,
            sukunimi0 = form.sukunimi0.data,
            email0 = form.email0.data,
            food0 = form.food0.data,
            allergies0 = form.allergies0.data,
            free0 = form.free0.data,
            consent0 = form.consent0.data,
            datetime = nowtime
        )
        db.session.add(sub)
        db.session.commit()


        if GMAIL:
            with app.app_context():
                msg = Message(subject="Olet varasijalla kotiruokakurssille 7.12.",
                sender=app.config.get("MAIL_USERNAME"),
                recipients=[form.email0.data])
                msg.html = render_template('/2021/message_kotiruoka1.html', user=form.data)
                mail.send(msg)

        if KAPSI:
            return redirect('https://ilmo.oty.fi/kotiruokakurssi1')
        else:
            return redirect(url_for('kotiruokakurssi1'))

    elif (not validate) and submitted:
        flash('Ilmoittautuminen epäonnistui, tarkista syöttämäsi tiedot')


    return render_template('/2021/kotiruokakurssi1.html', title='Kotiruokakurssi 7.12.',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="Simailut")

@app.route('/kotiruokakurssi1_data', methods=['GET'])
@auth.login_required(role=['admin', 'meea', 'kokkimestari'])
def kotiruokakurssi1_data():
    limit = 16

    entries = kotiruokakurssi1Model.query.all()

    count = kotiruokakurssi1Model.query.count()

    return render_template('/2021/kotiruokakurssi1_data.html', title='Kotiruokakurssi 7.12. data',
                           entries=entries,
                           count=count,
                           limit=limit)

@app.route('/ruokakurssi1/ruokakurssi1_model_data.csv')
@auth.login_required(role=['admin', 'anssi', 'kokkimestari'])
def kotiruokakurssi1_csv():

    os.system('mkdir csv')

    sqlite_to_csv.exportToCSV('kotiruokakurssi1_model')

    dir = os.path.join(os.getcwd(), 'csv/')
    
    try:
        print(dir)
        return send_from_directory(directory=dir, filename='kotiruokakurssi1_model_data.csv', as_attachment=True)
    except FileNotFoundError as e:
        print(e)
        abort(404)

@app.route('/kotiruokakurssi2', methods=['GET', 'POST'])
def kotiruokakurssi2():
    form = kotiruokakurssi2Form()

    starttime = datetime(2021, 12, 2, 15, 00, 00)
    endtime = datetime(2021, 12, 13, 20, 00, 00)
    nowtime = datetime.now()

    limit = 16

    count = 0


    entrys = kotiruokakurssi2Model.query.all()

    totalCount = 0
    for entry in entrys:
        totalCount += 1


    for entry in entrys:
        if(entry.etunimi0 == form.etunimi0.data and entry.sukunimi0 == form.sukunimi0.data):
            flash('Olet jo ilmoittautunut')
            return render_template('/2021/kotiruokakurssi2.html', title='Kotiruokakurssi 14.12.',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="Kotiruokakurssi 14.12.")


    if request.method == 'POST':
        validate = form.validate_on_submit()
        submitted = form.is_submitted()
    else:
        validate = False
        submitted = False

    if validate and submitted and limit > totalCount:
        flash('Ilmoittautuminen onnistui')
        sub = kotiruokakurssi2Model(
            etunimi0 = form.etunimi0.data,
            sukunimi0 = form.sukunimi0.data,
            email0 = form.email0.data,
            food0 = form.food0.data,
            allergies0 = form.allergies0.data,
            free0 = form.free0.data,
            consent0 = form.consent0.data,
            datetime = nowtime
        )
        db.session.add(sub)
        db.session.commit()

        if GMAIL:
            with app.app_context():
                msg = Message(subject="Ilmoittautuminen kotiruokakurssille 14.12. vastaanotettu",
                sender=app.config.get("MAIL_USERNAME"),
                recipients=[form.email0.data])
                msg.html = render_template('/2021/message_kotiruoka2.html', user=form.data)
                mail.send(msg)

        if KAPSI:
            return redirect('https://ilmo.oty.fi/kotiruokakurssi2')
        else:
            return redirect(url_for('kotiruokakurssi2'))

    elif submitted and limit < totalCount:
        totalCount -= 1
        flash('Ilmoittautuminen on jo täynnä')
        sub = kotiruokakurssi2Model(
            etunimi0 = form.etunimi0.data,
            sukunimi0 = form.sukunimi0.data,
            email0 = form.email0.data,
            food0 = form.food0.data,
            allergies0 = form.allergies0.data,
            free0 = form.free0.data,
            consent0 = form.consent0.data,
            datetime = nowtime
        )
        db.session.add(sub)
        db.session.commit()

        if GMAIL:
            with app.app_context():
                msg = Message(subject="Olet varasijalla kotiruokakurssille 14.12.",
                sender=app.config.get("MAIL_USERNAME"),
                recipients=[form.email0.data])
                msg.html = render_template('/2021/message_kotiruoka2.html', user=form.data)
                mail.send(msg)

        if KAPSI:
            return redirect('https://ilmo.oty.fi/kotiruokakurssi2')
        else:
            return redirect(url_for('kotiruokakurssi2'))

    elif (not validate) and submitted:
        flash('Ilmoittautuminen epäonnistui, tarkista syöttämäsi tiedot')


    return render_template('/2021/kotiruokakurssi2.html', title='Kotiruokakurssi 14.12.',
                                    entrys=entrys,
                                    starttime=starttime,
                                    endtime=endtime,
                                    nowtime=nowtime,
                                    form=form,
                                    totalCount=totalCount,
                                    limit=limit,
                                    page="Kotiruokakurssi2")

@app.route('/kotiruokakurssi2_data', methods=['GET'])
@auth.login_required(role=['admin', 'meea', 'kokkimestari'])
def kotiruokakurssi2_data():
    limit = 16

    entries = kotiruokakurssi2Model.query.all()

    count = kotiruokakurssi2Model.query.count()

    return render_template('/2021/kotiruokakurssi2_data.html', title='Kotiruokakurssi 14.12. data',
                           entries=entries,
                           count=count,
                           limit=limit)

@app.route('/ruokakurssi2/ruokakurssi2_model_data.csv')
@auth.login_required(role=['admin', 'anssi', 'kokkimestari'])
def kotiruokakurssi2_csv():

    os.system('mkdir csv')

    sqlite_to_csv.exportToCSV('kotiruokakurssi2_model')

    dir = os.path.join(os.getcwd(), 'csv/')
    
    try:
        print(dir)
        return send_from_directory(directory=dir, filename='kotiruokakurssi2_model_data.csv', as_attachment=True)
    except FileNotFoundError as e:
        print(e)
        abort(404)