def insert_user(user: dict):
cur = get_db().cursor()
# Check if user exists
cur.execute("SELECT * FROM users WHERE id = ?", (user["id"], ))
data = cur.fetchone()
if data:
if user["lastUpdate"] > data["last_update"]:
# update our user
current_app.logger.info(f"Updating user {user['name']}")
cur.execute(
"UPDATE users SET vip=?, name=?, transponder_code=? WHERE id=?",
(
user["vip"],
user["name"],
user["transponder"],
user["id"],
),
)
else:
current_app.logger.info(f"Inserting new user {user['name']}")
cur.execute(
"INSERT INTO users (vip, name, last_update, transponder_code) VALUES (?, ?,?,?)",
(
user["vip"],
user["name"],
user["lastUpdate"],
user["transponder"],
),
)
get_db().commit()
def delete_user(id: int):
"""Deletes a user
Removes the user from the database, however debts are kept.
"""
current_app.logger.warning(f"Deleting user with ID {id}")
cur = get_db().cursor()
cur.execute("DELETE FROM users WHERE id = ?", (id, ))
get_db().commit()
def sum_debt() -> int:
"""The sum of balances of users in debt"""
cur = get_db().cursor()
cur.execute(
"SELECT SUM(balance) from balances WHERE id in (SELECT id FROM users WHERE system = FALSE) AND balance < 0;"
)
return cur.fetchone()[0] or 0
def login():
if request.method == "POST":
username = request.form.get("username")
password = request.form.get("password")
next_url = request.form.get("next")
db = get_db()
error = None
user = db.execute("SELECT * FROM admins WHERE username = ?",
(username, )).fetchone()
if user is None:
error = "Incorrect username."
elif not check_password_hash(user["password"], password):
error = "Incorrect password."
if error is None:
current_app.logger.info(f"{username} logged in")
session.clear()
session["user_id"] = user["username"]
if next_url:
return redirect(escape(next_url))
return redirect(url_for(".admin"))
current_app.logger.warn(f"{error} ({username}:{password})")
flash(error)
return render_template("admin/login.html")
def total_intake() -> int:
"""The sum of money paid by non-system users"""
cur = get_db().cursor()
cur.execute(
"SELECT SUM(amount) from transactions WHERE user in (SELECT id FROM users WHERE system = FALSE) AND amount > 0;"
)
return cur.fetchone()[0] or 0
def vacuum_database():
"""Optimizes the Database file"""
with scheduler.app.app_context():
current_app.logger.info(f"Vacuuming database")
with get_db() as con:
con.execute("VACUUM")
current_app.logger.info(f"Finished vacuuming")
def load_logged_in_user():
user_id = session.get("user_id")
if user_id is None:
g.user = None
else:
g.user = (get_db().execute("SELECT * FROM admins WHERE username = ?",
(user_id, )).fetchone())
def undo_transaction(id: int):
"""Deletes the last transaction of a user."""
current_app.logger.warning(f"Deleting transaction with ID {id}")
with get_db() as cur:
cur.execute(
"DELETE FROM transactions WHERE user = ? ORDER BY timestamp DESC LIMIT 1;",
(id, ),
)
def get_transactions(limit=10) -> dict:
"""Return a list of transactions"""
current_app.logger.debug(f"Retrieving last {limit} transactions")
cur = get_db().cursor()
cur.execute(
"SELECT name, amount, description, timestamp FROM transactions LEFT JOIN users ON transactions.user = users.id ORDER BY timestamp DESC LIMIT ?;",
(limit, ),
)
return cur.fetchall()
def sum_transactions() -> int:
"""Sums all user transactions. Note that system users are excluded."""
start_time = perf_counter()
cur = get_db().cursor()
cur.execute(
"SELECT IFNULL(SUM(amount), 0) from transactions WHERE user in (SELECT id FROM users WHERE system = FALSE);"
)
current_app.logger.debug(
f"Summing all transactions took {perf_counter() - start_time} milliseconds"
)
return cur.fetchone()[0]
def get_user_from_name(name: str, sensitive=True) -> dict:
"""Like 'get_user()' but fetches by the name instead of ID"""
with get_db() as cur:
result = cur.execute(
"SELECT users.id AS userid, * FROM users LEFT JOIN balances ON users.id = balances.id WHERE users.name LIKE ?",
(name, ),
).fetchone()
if result is None:
raise ValueError(f"User with name '{name}' does not exist")
else:
return create_user(result, sensitive)
def get_user(id: int, sensitive=True) -> dict:
"""Fetch a user by their ID"""
with get_db() as cur:
result = cur.execute(
"SELECT users.id AS userid, * FROM users LEFT JOIN balances ON users.id = balances.id WHERE users.id = ?",
(id, ),
).fetchone()
if result is None:
raise ValueError(f"User with ID '{id}' does not exist")
else:
return create_user(result, sensitive)
def insert_transaction(transaction: dict):
"""Insert a transaction into the database"""
user = transaction["user"]
amount = transaction["amount"]
description = transaction["description"]
timestamp = transaction["timestamp"]
current_app.logger.info(f"Booking {amount / 100} towards user ID {user}")
cur = get_db().cursor()
# Insert transaction
cur.execute(
"INSERT INTO transactions VALUES (?,?,?,?)",
(
user,
amount,
description,
timestamp,
),
)
get_db().commit()
def save_admin_password():
"""Change the admin password"""
current_app.logger.warning("Changing administrator password")
password = request.form.get("password")
hashed = generate_password_hash(password)
username = session.get("user_id")
with get_db() as con:
con.cursor().execute(
"UPDATE admins SET password = ? WHERE username = ?",
(hashed, username),
)
flash("Passwort geändert.")
return redirect(url_for(".account"))
def backup_database():
"""Saves an optimized copy of the database to instance folder"""
backup_dir = current_app.config.get("BACKUP_DIR")
# Ensure the backup folder exists
os.makedirs(backup_dir, exist_ok=True)
backup_file = os.path.join(backup_dir,
strftime("BACKUP-%Y-%m-%d-%H%M%S.sqlite"))
current_app.logger.info(f"Backing up to {backup_file}")
with get_db() as db:
db.execute("VACUUM main INTO ?", (backup_file, ))
current_app.logger.info(f"Finished backing up")
prune_backups()
def get_users(sensitive=True) -> dict:
"""Return a list of non-system users
These can be directly converted to JSON for the client or used for further processing.
"""
current_app.logger.debug(
f"Retrieving users, include sensitive data: {sensitive}")
cur = get_db().cursor()
cur.execute(
"SELECT users.id AS userid, * FROM users LEFT JOIN balances ON users.id = balances.id WHERE users.system = FALSE;"
)
# Probably inefficient, as this has serial overhead and then everything is fork()'ed
results = [dict(row) for row in cur.fetchall()]
with Pool() as pool:
array = pool.starmap(create_user, product(results, [sensitive]))
# Sort by VIP Status, then activity
return sorted(array, key=lambda x: (-x["vip"], -x["lastUpdate"]))
def save_table():
user_id = request.form.get("id")
vipstatus = request.form.get("vip") == "on"
user_name = request.form.get("name")
action = request.form.get("action")
if action == "delete":
delete_user(user_id)
flash("Deleted user " + user_name)
return redirect(url_for(".users"))
if action == "undo":
undo_transaction(user_id)
flash(f"Letzte Transaktion von {user_name} gelöscht.")
return redirect(url_for(".users"))
# Assume we are getting one row
user = [{
"id": user_id,
"vip": vipstatus,
"name": user_name,
"lastUpdate": time.time(),
"transponder": request.form.get("transponder_code"),
}]
merge_users(user)
# Check if a deposit was made
if "payment" in request.form:
# TODO: ensure there are no float innaccuracies
payment = round(float(request.form.get("payment")) * 100)
with get_db() as db:
db.execute(
"INSERT INTO transactions (user, amount, description) VALUES (?,?,?)",
(user[0]["id"], payment, "Transaktion durch Adminbereich"),
)
flash("Updated user " + request.form.get("name"))
return redirect(url_for(".users"))
