Ticket-Server/app.py

from sqlite3 import connect, OperationalError

import bcrypt
from datetime import datetime
from flask import Flask, request, jsonify, make_response, render_template
from flask_httpauth import HTTPBasicAuth
import flask_excel as excel
from dateutil import tz


def utc_to_local(utc_dt):
    return utc_dt.replace(tzinfo=tz.tzutc()).astimezone(tz.gettz("Europe/Amsterdam"))


app = Flask(__name__, static_url_path='')
auth = HTTPBasicAuth()
db = connect("database.db", check_same_thread=False)
excel.init_excel(app)

try:
    cursor = db.cursor()
    cursor.execute('''create table tickets (
      username text not null, number text, date timestamp default current_timestamp, inside boolean default 0
    );''')
    cursor.execute('''create table users (
      username text not null, password text not null
    );''')
    cursor.close()
    db.commit()
except OperationalError:
    pass


@auth.verify_password
def verpass(username, password):
    print(username)
    if username in (None, '') or password in (None, ''):
        return False
    # return True
    cur = db.cursor()
    ret = cur.execute('''select password from users where username = ?''', (username,)).fetchone()
    if ret is None:
        salt = bcrypt.gensalt()
        cur.execute('''insert into users (username, password) values (?,?);''',
                    (username, bcrypt.hashpw(password.encode('utf-8'), salt),))
        cur.close()
        db.commit()
        return True
    else:
        (pw) = ret[0]
        try:
            red = bcrypt.checkpw(password.encode('utf-8'), pw)
        except ValueError:
            return False
        return red


def checknum(username, number, checked=0):
    cur = db.cursor()
    if checked != -1:
        res = cur.execute('''select * from tickets where username = ? and number = ? and inside = ?;''',
                          (username, number, checked,)).fetchone()
    else:
        res = cur.execute('''select * from tickets where username = ? and number = ?;''',
                          (username, number,)).fetchone()
    if res is None:
        return False
    return len(res) >= 1


@app.route("/ticket", methods=["POST"])
@auth.login_required
def addticket():
    print(request.json)
    if not request.json or "number" not in request.json or "function" not in request.json:
        return make_response(jsonify({"msg": "missing/faulty request body?"}), 400)
    fun = request.json["function"]
    if fun not in ("add", "check", "uncheck", "del"):
        return make_response(jsonify({"msg": "invalid function"}), 501)
    num = str(request.json["number"])
    if fun == "add":
        if checknum(auth.username(), num):
            return make_response(jsonify({"msg": "Ticket already payed!"}), 409)
        cur = db.cursor()
        cur.execute("insert into tickets (username,number) values (?,?);", (auth.username(), num,))
        cur.close()
        db.commit()
        return make_response(jsonify({"msg": "Added user"}), 200)
    elif fun == "check":
        cr = db.cursor()
        cr.execute("select number from tickets where number = ?", (num,))
        lis = cr.fetchall()
        if len(lis) < 1:
            return make_response(jsonify({"msg": "Ticket not payed!"}), 409)
        if checknum(auth.username(), num, 1):
            return make_response(jsonify({"msg": "Ticket already inside!"}), 417)
        cur = db.cursor()
        cur.execute("update tickets set inside = 1 where username = ? and number = ?;", (auth.username(), num))
        cur.close()
        db.commit()
        return make_response(jsonify({"msg": "Payed!"}), 200)
    elif fun == "uncheck":
        if checknum(auth.username(), num, 0):
            return make_response(jsonify({"msg": "Ticket hasn't entered yet!"}), 409)
        cur = db.cursor()
        cur.execute("update tickets set inside = 0 where username = ? and number = ?;", (auth.username(), num))
        cur.close()
        db.commit()
        return make_response(jsonify({"msg": "Unpayed!!"}), 200)
    elif fun == "del":
        if not checknum(auth.username(), num, -1):
            return make_response(jsonify({"msg": "Ticket does not exist!"}), 409)
        cur = db.cursor()
        cur.execute("delete from tickets where username = ? and number = ?", (auth.username(), num,))
        cur.close()
        db.commit()
        return make_response(jsonify({"msg": "Removed!"}), 200)


@app.route("/control", methods=["POST"])
@auth.login_required
def control():
    if not request.json or "function" not in request.json:
        return make_response(jsonify({"msg": "missing/faulty request body?"}), 400)
    fun = request.json["function"]
    if fun not in ("remove_all",):
        return make_response(jsonify({"msg": "invalid function"}), 405)
    if fun == "remove_all":
        cur = db.cursor()
        cur.execute("delete from tickets where username = ?", (auth.username(),))
        cur.close()
        db.commit()
        return make_response(jsonify({"msg": "Removed!"}), 200)


@app.route("/scan")
@auth.login_required
def scanlink():
    return render_template("scan.html", username=auth.username(), func="add", mode="Toevoegen")


@app.route("/check")
@auth.login_required
def checklink():
    return render_template("scan.html", username=auth.username(), func="check", mode="Controleren")


# @app.route("/list")
# @auth.login_required
# def tes():
#     cur = db.cursor()
#     tickets = cur.execute('''select * from tickets where username = ?;''', (auth.username(),)).fetchall()
#     cur.close()
#     print(tickets)
#     lst = list()
#     for (ln, num, dt, inside) in tickets:
#         lst.append(
#             {"number": num,
#              "date": datetime.strptime(dt, "%Y-%m-%d %H:%M:%S").astimezone(tz.gettz("Europe/Amsterdam")).strftime(
#                  "%H:%M:%S %d-%m-%Y"), "inside": inside == 1})
#     return jsonify(lst)


@app.route("/excel")
@auth.login_required
def exc():
    cur = db.cursor()
    tickets = cur.execute('''select * from tickets where username = ?;''', (auth.username(),)).fetchall()
    cur.close()
    print(tickets)
    lst = list()
    for (ln, num, dt, inside) in tickets:
        lst.append({"number": num, "date": datetime.strptime(dt, "%Y-%m-%d %H:%M:%S").astimezone(
            tz.gettz("Europe/Amsterdam")).strftime(
            "%H:%M:%S %d-%m-%Y"), "inside": inside == 1})

    return excel.make_response_from_records(lst, 'xlsx', file_name="tickets - " + auth.username())


@app.route("/list")
@auth.login_required
def check():
    cur = db.cursor()
    num1 = cur.execute('''select * from tickets where username = ?;''', (auth.username(),)).fetchall()
    num2 = cur.execute('''select * from tickets where username = ? and inside = 1;''', (auth.username(),)).fetchall()
    cur.close()
    return "%d/%d" % (len(num2), len(num1))


if __name__ == '__main__':
    app.run("0.0.0.0", 5000)