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)