def invite():
if request.method == "POST":
if 'cancel' in request.form:
return redirect(url_front())
email_addresses = request.form['email']
for email_address in email_addresses.splitlines():
key = generate_key()
url = config.URL + url_for("usermanager.new", key=key)
text = invite_mail.format(url=url)
data.execute("UPDATE User_creation_keys SET email = ? WHERE key = ?", email_address, key)
mail.send(email_address, "Invitation til GRIS", text)
flash("Invitationer sendt")
message = mail.invitation_send_adminmail.format(email=request.form['email'])
mail.admin("User Invited", message)
return redirect(url_for("usermanager.overview"))
else:
w = html.WebBuilder()
w.form()
w.formtable()
w.textarea("email", "Emails (1 pr. linje)")
form = w.create()
return render_template("form.html", form=form)
def add_news():
if request.method == 'POST':
if 'cancel' in request.form:
return redirect(url_front())
creator = session['username']
created = now()
title = request.form['title']
get_flashed_messages()
if title == "":
flash("Please enter a title")
return html.back()
text = request.form['text']
data.execute("INSERT INTO News(creator, created, title, text) VALUES(?,?,?,?)", creator, created, title, text)
return redirect(url_front())
else:
w = html.WebBuilder()
w.form()
w.formtable()
w.textfield("title", "Overskrift")
w.textarea("text", "Tekst")
form = w.create()
return render_template("form.html", form=form)
def login():
error = None
if request.method == 'POST':
username = request.form['username']
raw_password = request.form['password']
if 'forgot' in request.form:
try:
forgot_password(username)
except Exception as e:
if str(e) != "No such user/No valid email":
raise
flash("Kunne ikke sende en mail til denne bruger")
return redirect(url_for('usermanager.login'))
return render_template("usermanager/forgot.html", username=username)
user = data.execute('SELECT password, deleted FROM Users WHERE username = ?', username)
if empty(user) or not password.check(raw_password, user[0]['password']):
flash('Invalid username or password')
elif user[0]["deleted"]:
flash('Sorry, your user has been deleted')
else:
session['logged_in'] = True
session['username'] = username
groups = data.execute('SELECT groupname FROM Group_users WHERE username = ?', username)
groups = [group['groupname'] for group in groups]
session['groups'] = groups
update_password(username, raw_password)
flash("Login succesful")
return redirect(session.pop('login_origin', url_front()))
return render_template("usermanager/login.html", error=error)
def delete_user():
# return render_template("admin/delete_user.html")
if request.method == "POST":
if 'cancel' in request.form:
flash(escape("Ændringer anulleret"))
return redirect(url_for('admin.overview'))
b = data.Bucket(request.form)
b.deleted = True
b >> ("UPDATE Users SET $ WHERE username = ?", request.form["user"])
data.execute("DELETE FROM Group_users WHERE username = ?", request.form["user"])
flash("Bruger slettet")
return redirect(url_for('admin.delete_user'))
else:
users = data.execute("SELECT * FROM Users WHERE deleted = ?", False)
users = [(user['username'], "{0}: {1}".format(user['username'], user['name'])) for user in users]
w = html.WebBuilder()
w.form()
w.formtable()
w.select("user", "Brugere:", users)
form = w.create()
return render_template("admin/delete_user.html", form=form)
def delete(t_id):
if request.method == "POST":
if 'delete' in request.form:
try:
data.execute("DELETE FROM Tours WHERE t_id = ?", t_id)
except:
flash("Could not delete tour, there are people/items associated with it")
return redirect(url_for('rustours.rustour', t_id=t_id))
return redirect(url_for('rustours.overview'))
else:
flash(escape("Nothing deleted"))
return redirect(url_for('rustours.rustour', t_id=t_id))
else:
tours = data.execute("SELECT * FROM Tours WHERE t_id = ?", t_id)
if len(tours) != 1:
flash(escape("Den tur findes ikke"))
return redirect(url_for("rustours.overview"))
tour = tours[0]
w = html.WebBuilder()
w.form()
w.formtable()
w.html("Vil du slette rusturen?")
w.html('<button type="submit" name="delete" value="delete">Slet rustur</button>', "Slet rustur?")
form = w.create()
return render_template("form.html", form=form)
def delete(m_id):
if request.method == "POST":
if 'delete' in request.form:
try:
data.execute("DELETE FROM Mentorteams WHERE m_id = ?", m_id)
except:
flash("Could not delete team, there are people/items associated with it")
return redirect(url_for('mentorteams.mentorteam', m_id=m_id))
return redirect(url_for('mentorteams.overview'))
else:
flash(escape("Nothing deleted"))
return redirect(url_for('mentorteams.mentorteam', m_id=m_id))
else:
teams = data.execute("SELECT * FROM Mentorteams WHERE m_id = ?", m_id)
if len(teams) != 1:
flash(escape("Det hold findes ikke"))
return redirect(url_for("mentorteams.overview"))
team = teams[0]
w = html.WebBuilder()
w.form()
w.formtable()
w.html("Vil du slette holdet?")
w.html('<button type="submit" name="delete" value="delete">Slet</button>', "Slet mentorhold?")
form = w.create()
return render_template("form.html", form=form)
def delete(t_id):
if request.method == "POST":
if 'delete' in request.form:
try:
data.execute("DELETE FROM Tours WHERE t_id = ?", t_id)
except:
flash(
"Could not delete tour, there are people/items associated with it"
)
return redirect(url_for('rustours.rustour', t_id=t_id))
return redirect(url_for('rustours.overview'))
else:
flash(escape("Nothing deleted"))
return redirect(url_for('rustours.rustour', t_id=t_id))
else:
tours = data.execute("SELECT * FROM Tours WHERE t_id = ?", t_id)
if len(tours) != 1:
flash(escape("Den tur findes ikke"))
return redirect(url_for("rustours.overview"))
tour = tours[0]
w = html.WebBuilder()
w.form()
w.formtable()
w.html("Vil du slette rusturen?")
w.html(
'<button type="submit" name="delete" value="delete">Slet rustur</button>',
"Slet rustur?")
form = w.create()
return render_template("form.html", form=form)
def dutyteams(t_id):
if request.method == "POST":
if 'cancel' in request.form:
return redirect(url_for('rustours.rustour', t_id=t_id))
if request.form['new'] != "":
b = data.Bucket()
b.name = request.form['new']
b.t_id = t_id
b >= "Dutyteams"
dutyteams = data.execute("SELECT d_id FROM Dutyteams WHERE t_id = ?", t_id)
dutyteams = set(str(dutyteam['d_id']) for dutyteam in dutyteams)
for d_id in request.form.keys():
if d_id in dutyteams:
b = data.Bucket()
b.name = request.form[d_id]
b >> ("UPDATE Dutyteams $ WHERE t_id = ? AND d_id = ?", t_id, d_id)
return redirect(url_for("rustours.rustour", t_id=t_id))
else:
dutyteams = data.execute("SELECT * FROM Dutyteams WHERE t_id = ? ORDER BY d_id ASC", t_id)
w = html.WebBuilder()
w.form()
w.formtable()
for dutyteam in dutyteams:
w.textfield(dutyteam['d_id'], "Omdøb:", value=dutyteam['name'])
w.textfield("new", "Nyt tjansehold:")
form = w.create()
return render_template("form.html", form=form)
def delete_user():
# return render_template("admin/delete_user.html")
if request.method == "POST":
if 'cancel' in request.form:
flash(escape("Ændringer anulleret"))
return redirect(url_for('admin.overview'))
b = data.Bucket(request.form)
b.deleted = True
b >> ("UPDATE Users SET $ WHERE username = ?", request.form["user"])
data.execute("DELETE FROM Group_users WHERE username = ?",
request.form["user"])
flash("Bruger slettet")
return redirect(url_for('admin.delete_user'))
else:
users = data.execute("SELECT * FROM Users WHERE deleted = ?", False)
users = [(user['username'], "{0}: {1}".format(user['username'],
user['name']))
for user in users]
w = html.WebBuilder()
w.form()
w.formtable()
w.select("user", "Brugere:", users)
form = w.create()
return render_template("admin/delete_user.html", form=form)
def group(groupname):
if request.method == "POST":
if 'cancel' in request.form:
flash("Ændringer anulleret")
return redirect(url_for('admin.groups_overview'))
users = data.execute('SELECT username FROM Users WHERE deleted = ?', False)
for user in users:
username = user['username']
try:
if username in request.form:
usermanager.group_add_user(groupname, username)
else:
usermanager.group_remove_user(groupname, username)
except psycopg2.IntegrityError as e:
pass
return redirect(url_for('admin.groups_overview'))
else:
users = data.execute('SELECT username, name FROM Users WHERE deleted = ? ORDER BY name ASC', False)
group = data.execute('SELECT username FROM Group_users WHERE groupname = ?', groupname)
group = set(user['username'] for user in group)
usernames = (user['username'] for user in users)
kv = {user:(user in group) for user in usernames}
w = html.WebBuilder()
w.form()
w.formtable()
for user in users:
w.checkbox(user['username'], '"{0}" {1}'.format(user['username'], user['name']))
form = w.create(kv)
return render_template("form.html", form=form)
def invite():
if request.method == "POST":
if 'cancel' in request.form:
return redirect(url_front())
email_addresses = request.form['email']
for email_address in email_addresses.splitlines():
key = generate_key()
url = config.URL + url_for("usermanager.new", key=key)
text = invite_mail.format(url=url)
data.execute(
"UPDATE User_creation_keys SET email = ? WHERE key = ?",
email_address, key)
mail.send(email_address, "Invitation til GRIS", text)
flash("Invitationer sendt")
message = mail.invitation_send_adminmail.format(
email=request.form['email'])
mail.admin("User Invited", message)
return redirect(url_for("usermanager.overview"))
else:
w = html.WebBuilder()
w.form()
w.formtable()
w.textarea("email", "Emails (1 pr. linje)")
form = w.create()
return render_template("form.html", form=form)
def user(username):
user = data.execute("SELECT * FROM Users WHERE username = ?", username)
user = user[0]
user = unnonify(user)
tours = data.execute("SELECT t_id, tour_name, year, type FROM Tours INNER JOIN Tours_tutors USING (t_id) WHERE username = ? ORDER BY YEAR DESC", username)
mentorteams = data.execute("SELECT m_id, mentor_names, year FROM Mentorteams INNER JOIN Mentors USING (m_id) WHERE username = ? ORDER BY YEAR DESC", username)
return render_template("usermanager/user.html", user=user, tours=tours, mentorteams=mentorteams)
def book(b_id):
# TODO: The books are technically correct, but we need to fix 2 things.
# 1. You shouldn't see what you owe to yourself
# 2. When you owe money to someone who also owes to you this should be substracted from each other
book = data.execute("SELECT * FROM Books WHERE b_id = ?", b_id)[0]
#raw_entries = data.execute("SELECT * FROM Entries WHERE b_id = ? ORDER BY date ASC", b_id)
user = session['username']
# TODO: convert internal representation to øre
# TODO: decide on floating vs integer for currency
raw_entries = data.execute( 'SELECT *, ((amount*1)/share_total*share) AS owes FROM (SELECT * FROM Entries where b_id = ?) LEFT OUTER JOIN (SELECT e_id, SUM(share) AS share_total FROM Debts GROUP BY e_id) USING (e_id) LEFT OUTER JOIN (SELECT e_id, share FROM Debts WHERE debtor = ?) USING(e_id);', b_id, user)
# TODO: substract reverse debts
local_totals = data.execute( 'SELECT * FROM (SELECT creditor, SUM(owes) AS total FROM (SELECT *, ((amount*1)/share_total*share) AS owes FROM (SELECT * FROM Entries WHERE b_id = ?) LEFT OUTER JOIN (SELECT e_id, SUM(share) AS share_total FROM Debts GROUP BY e_id) USING (e_id) LEFT OUTER JOIN (SELECT e_id, share FROM Debts WHERE debtor = ?) USING(e_id)) GROUP BY creditor) WHERE total is not Null', b_id, user)
global_totals = data.execute('SELECT * FROM (SELECT creditor, SUM(owes) AS total FROM (SELECT *, ((amount*1)/share_total*share) AS owes FROM Entries LEFT OUTER JOIN (SELECT e_id, SUM(share) AS share_total FROM Debts GROUP BY e_id) USING (e_id) LEFT OUTER JOIN (SELECT e_id, share FROM Debts WHERE debtor = ?) USING(e_id)) GROUP BY creditor) WHERE total is not Null', user)
raw_breakdown = data.execute('SELECT *, (COALESCE(credit, 0)-COALESCE(debt,0)) AS balance FROM (SELECT * FROM (SELECT creditor AS user FROM Entries WHERE b_id = ?) UNION SELECT debtor AS user FROM Debts LEFT OUTER JOIN Entries USING(e_id) WHERE b_id = ? UNION SELECT participant AS user FROM Book_participants WHERE b_id = ?) LEFT OUTER JOIN (SELECT creditor AS user, SUM(amount) AS credit FROM Entries WHERE b_id = ? GROUP BY creditor) USING (user) LEFT OUTER JOIN (SELECT debtor AS user, SUM(debt) AS debt FROM (SELECT *, ((amount*1)/share_total*share) AS debt FROM Debts LEFT OUTER JOIN Entries USING(e_id) LEFT OUTER JOIN (SELECT e_id, SUM(share) AS share_total FROM Debts GROUP BY e_id) USING(e_id) WHERE b_id = ?) GROUP BY debtor) USING(user)', b_id, b_id, b_id, b_id, b_id)
entries = []
for entry in raw_entries:
d = {}
d.update(entry)
#amount = money(entry['amount'])
#d['amount'] = amount
share_total = entry['share_total']
if share_total == None:
share_total = 0
share = entry['share']
if share == None:
share = 0
final_share = "{0}/{1}".format(share, share_total)
owes = entry['owes']
if owes == None:
owes = 0
#owes = "{0}kr.".format(owes)
if share == 0:
final_share = ""
owes = ""
d.update({"final_share":final_share, "owes":owes})
entries += [d]
breakdown = []
for row in raw_breakdown:
res = {}
for c in row.keys():
if row[c] == None:
res[c] = ""
else:
res[c] = row[c]
breakdown.append(res)
return render_template("bookkeeper/book.html", book=book, entries=entries, breakdown=breakdown, local_totals=local_totals, global_totals=global_totals)
def delete_old_keys():
"""Delete all old keys"""
# EXPLANATION: weed out old creation keys
overtime = now() - datetime.timedelta(days=30)
data.execute("DELETE FROM User_creation_keys WHERE created <= ?", overtime)
# EXPLANATION: weed out old password keys
overtime = now() - datetime.timedelta(minutes=20)
data.execute("DELETE FROM User_forgotten_password_keys WHERE created <= ?", overtime)
def delete_old_keys():
"""Delete all old keys"""
# EXPLANATION: weed out old creation keys
overtime = now() - datetime.timedelta(days=30)
data.execute("DELETE FROM User_creation_keys WHERE created <= ?", overtime)
# EXPLANATION: weed out old password keys
overtime = now() - datetime.timedelta(minutes=20)
data.execute("DELETE FROM User_forgotten_password_keys WHERE created <= ?",
overtime)
def friends():
friends = data.execute("SELECT r_id1, name1, r_id2, name AS name2 FROM (SELECT r_id1, name AS name1, r_id2 FROM (SELECT * FROM Friends UNION (SELECT r_id2 AS r_id1, r_id1 AS r_id2 FROM Friends)) AS a INNER JOIN Russer ON r_id1 = r_id) AS b INNER JOIN Russer ON r_id2 = r_id ORDER BY name1")
friends = itertools.groupby(friends, key=get('name1'))
friends = [(x[0], list(x[1])) for x in friends]
friends = [({'name1':name, 'r_id1':l[0]['r_id1']}, l) for name, l in friends]
user_friends = data.execute("SELECT username, Users.name as users_name, r_id, Russer.name as russer_name FROM Friends_of_us INNER JOIN Users USING (username) INNER JOIN Russer Using (r_id) ORDER BY Russer.name")
user_friends = itertools.groupby(user_friends, key=get('russer_name'))
user_friends = [(x[0], list(x[1])) for x in user_friends]
user_friends = [({'russer_name':name, 'r_id':l[0]['r_id']}, l) for name, l in user_friends]
return render_template("rusmanager/friends.html", friends=friends, user_friends=user_friends)
def new(key):
time.sleep(random.randint(2, 6))
# EXPLANATION: weed out old creation keys
delete_old_keys()
# EXPLANATION: Check if key exists/is valid
result = data.execute(
"SELECT key, email FROM User_creation_keys WHERE key = ?", key)
if empty(result):
time.sleep(random.randint(5, 21))
# TODO: Send to errorpage?
return redirect(url_front())
if request.method == "POST":
if 'cancel' in request.form:
data.execute("DELETE FROM User_creation_keys WHERE key = ?", key)
flash("Oprettelse anulleret")
return redirect(url_front())
b = data.Bucket(request.form)
if not sanitize_username(b.username):
flash("Ugyldigt brugernavn")
return html.back()
if b.password1 != b.password2:
flash("Du gav to forskellige løsener, prøv igen")
return html.back()
if b.password1 == "":
flash("Du skal vælge et løsen")
return html.back()
data.execute("DELETE FROM User_creation_keys WHERE key = ?", key)
create_user(b.username, b.password1, b.name, b.email)
flash("Ny bruger oprettet")
session['logged_in'] = True
session['username'] = b.username
return redirect(url_for("usermanager.settings"))
else:
wb = html.WebBuilder()
wb.form()
wb.formtable()
wb.textfield("username", "Brugernavn (Hvad du bliver kaldt på DIKU):")
wb.textfield("name", "Fulde navn:")
wb.textfield("email", "Email:", value=result[0]["email"])
wb.password("password1", "Løsen")
wb.password("password2", "Gentag løsen")
form = wb.create()
return render_template("form.html", form=form)
def new(key):
time.sleep(random.randint(2,6))
# EXPLANATION: weed out old creation keys
delete_old_keys()
# EXPLANATION: Check if key exists/is valid
result = data.execute("SELECT key, email FROM User_creation_keys WHERE key = ?", key)
if empty(result):
time.sleep(random.randint(5,21))
# TODO: Send to errorpage?
return redirect(url_front())
if request.method == "POST":
if 'cancel' in request.form:
data.execute("DELETE FROM User_creation_keys WHERE key = ?", key)
flash("Oprettelse anulleret")
return redirect(url_front())
b = data.Bucket(request.form)
if not sanitize_username(b.username):
flash("Ugyldigt brugernavn")
return html.back()
if b.password1 != b.password2:
flash("Du gav to forskellige løsener, prøv igen")
return html.back()
if b.password1 == "":
flash("Du skal vælge et løsen")
return html.back()
data.execute("DELETE FROM User_creation_keys WHERE key = ?", key)
create_user(b.username, b.password1, b.name, b.email)
flash("Ny bruger oprettet")
session['logged_in'] = True
session['username'] = b.username
return redirect(url_for("usermanager.settings"))
else:
wb = html.WebBuilder()
wb.form()
wb.formtable()
wb.textfield("username", "Brugernavn (Hvad du bliver kaldt på DIKU):")
wb.textfield("name", "Fulde navn:")
wb.textfield("email", "Email:", value=result[0]["email"])
wb.password("password1", "Løsen")
wb.password("password2", "Gentag løsen")
form = wb.create()
return render_template("form.html", form=form)
def admin(subject, text, type="plain", mail_admins=None):
if mail_admins == None:
mail_admins = config.MAIL_ADMINS
if mail_admins:
admins = data.execute("SELECT email FROM Group_users INNER JOIN Users USING (username) WHERE groupname = ? and email IS NOT NULL", "admin_mail_log")
if len(admins) > 0:
send(admins, subject, text, type)
def modify_news(id):
news = data.execute("SELECT * FROM News WHERE n_id = ?", id)
if empty(news) or session['username'] != news[0]['creator']:
flash("You are not permitted to edit this newsitem")
return redirect(url_front())
news = news[0]
if request.method == 'POST':
if 'cancel' in request.form:
return redirect(url_front())
b = data.Bucket(request.form)
if 'delete' in request.form:
b >> ("DELETE FROM News WHERE n_id = ?", id)
if b.title == "":
flash("Please enter a title")
return html.back()
b.text
b >> ("UPDATE News $ WHERE n_id = ?", id)
return redirect(url_front())
else:
w = html.WebBuilder()
w.form()
w.formtable()
w.textfield("title", "Overskrift")
w.textarea("text", "Tekst")
w.html('<button type="submit" name="delete" value="delete">Slet nyhed</button>', "")
form = w.create(news)
return render_template("form.html", form=form)
def new():
if request.method == "POST":
if 'cancel' in request.form:
flash("Oprettelse annulleret")
return redirect(url_for('schedule.overview'))
s_id = data.execute(
"INSERT INTO Schedule(title, description, created, closes) VALUES(?,?,?,?) RETURNING s_id",
request.form['title'],
request.form['description'],
str(datetime.datetime.now()),
request.form['deadline'])[0][0]
choices = [(s_id, x, 0) for x in request.form.getlist('choices') if x]
flash(str(choices))
data.executemany(
"INSERT INTO Schedule_cols(s_id, label, type, parent) VALUES (?,?,?, NULL)",
choices)
flash("Oprettelse gennemført")
return redirect(url_for('schedule.overview'))
else:
deadline_calendar = html.calendar('schedule.deadline', 'yyyyMMdd', 'arrow', True, 24, False, 'future')
time_calendar = html.calendar('schedule.time', 'yyyyMMdd', 'arrow', True, 24, False, 'future')
return render_template("schedule/new.html", deadline_calendar=deadline_calendar, time_calendar=time_calendar)
def new():
if request.method == "POST":
if 'cancel' in request.form:
flash("Oprettelse annulleret")
return redirect(url_for('schedule.overview'))
s_id = data.execute(
"INSERT INTO Schedule(title, description, created, closes) VALUES(?,?,?,?) RETURNING s_id",
request.form['title'], request.form['description'],
str(datetime.datetime.now()), request.form['deadline'])[0][0]
choices = [(s_id, x, 0) for x in request.form.getlist('choices') if x]
flash(str(choices))
data.executemany(
"INSERT INTO Schedule_cols(s_id, label, type, parent) VALUES (?,?,?, NULL)",
choices)
flash("Oprettelse gennemført")
return redirect(url_for('schedule.overview'))
else:
deadline_calendar = html.calendar('schedule.deadline', 'yyyyMMdd',
'arrow', True, 24, False, 'future')
time_calendar = html.calendar('schedule.time', 'yyyyMMdd', 'arrow',
True, 24, False, 'future')
return render_template("schedule/new.html",
deadline_calendar=deadline_calendar,
time_calendar=time_calendar)
def user(username):
user = data.execute("SELECT * FROM Users WHERE username = ?", username)
user = user[0]
user = unnonify(user)
tours = data.execute(
"SELECT t_id, tour_name, year, type FROM Tours INNER JOIN Tours_tutors USING (t_id) WHERE username = ? ORDER BY YEAR DESC",
username)
mentorteams = data.execute(
"SELECT m_id, mentor_names, year FROM Mentorteams INNER JOIN Mentors USING (m_id) WHERE username = ? ORDER BY YEAR DESC",
username)
return render_template("usermanager/user.html",
user=user,
tours=tours,
mentorteams=mentorteams)
def change_password():
if request.method == "POST":
if 'cancel' in request.form:
flash(escape("Ændringer annulleret"))
return redirect(url_for('usermanager.settings'))
username = session["username"]
current_password = data.execute("SELECT password FROM Users WHERE username = ?", username)[0]['password']
b = data.Bucket(request.form)
if not password.check(b.current, current_password):
return logout()
if b.new1 != b.new2:
flash("De to løsner er ikke ens")
return redirect(url_for('usermanager.change_password'))
if b.new1 == "":
flash("Du specificerede ikke et nyt løsen")
return redirect(url_for('usermanager.change_password'))
update_password(username, b.new1)
return redirect(url_for('usermanager.settings'))
else:
w = html.WebBuilder()
w.form()
w.formtable()
w.password("current", "Nuværende løsen")
w.password("new1", "Nyt løsen")
w.password("new2", "Gentag nyt løsen")
form = w.create()
return render_template("form.html", form=form)
def groups_overview():
groups = data.execute('SELECT * FROM Group_users INNER JOIN Users USING (username) ORDER BY groupname, username')
groups = itertools.groupby(groups, key=get('groupname'))
groups = [(x[0], list(x[1])) for x in groups]
all_groups = data.execute("SELECT groupname FROM Groups ORDER BY groupname")
all_groups = [group['groupname'] for group in all_groups]
result = []
for group in all_groups:
if len(groups) > 0 and groups[0][0] == group:
result.append(groups.pop(0))
else:
result.append((group, []))
groups = result
return render_template("admin/group_overview.html", groups=groups)
def admin(subject, text, type="plain", mail_admins=None):
if mail_admins == None:
mail_admins = config.MAIL_ADMINS
if mail_admins:
admins = data.execute(
"SELECT email FROM Group_users INNER JOIN Users USING (username) WHERE groupname = ? and email IS NOT NULL",
"admin_mail_log")
if len(admins) > 0:
send(admins, subject, text, type)
def settings(m_id):
if request.method == "POST":
if 'cancel' in request.form:
return redirect(url_front())
b = data.Bucket(request.form)
if b.mentor_names == "":
b.mentor_names = "Unavngivet mentorhold"
if b.year.isdecimal():
b.year = int(b.year)
else:
flash("Please enter a valid year")
return html.back()
b >> ("UPDATE Mentorteams $ WHERE m_id = ?", m_id)
mentors = request.form['mentors']
mentors = mentors.replace('"', '')
mentors = mentors.replace('"', '')
mentors = [name.split()[0] for name in re.split(';\s', mentors) if name != ""]
old = data.execute("SELECT username FROM Mentors WHERE m_id = ?", m_id)
old = [mentor['username'] for mentor in old]
for mentor in set(old) - set(mentors):
data.execute("DELETE FROM Mentors WHERE m_id = ? and username = ?", m_id, mentor)
for mentor in sorted(set(mentors) - set(old)):
data.execute("INSERT INTO Mentors(m_id, username) VALUES (?, ?)", m_id, mentor)
return redirect(url_for('mentorteams.mentorteam', m_id=m_id))
else:
teams = data.execute("SELECT * FROM Mentorteams WHERE m_id = ?", m_id)
if len(teams) != 1:
flash(escape("Det hold findes ikke"))
return redirect(url_for("mentorteams.overview"))
team = teams[0]
all_mentors = data.execute("SELECT * FROM Users WHERE username IN (Select username from Group_users where groupname = 'mentor')")
all_mentors = ['\\"{0}\\" {1}'.format(mentor['username'], mentor['name']) for mentor in all_mentors]
all_mentors.sort()
actual_mentors = data.execute("SELECT * FROM Mentors INNER JOIN Users USING(username) WHERE m_id = ?", m_id)
actual_mentors = ['"{0}" {1}; '.format(mentor['username'], mentor['name']) for mentor in actual_mentors]
actual_mentors.sort()
actual_mentors ="".join(actual_mentors)
w = html.WebBuilder()
w.form()
w.formtable()
w.textfield("mentor_names", "Navn")
w.textfield("year", "År")
w.html(html.autocomplete_multiple(all_mentors, "mentors", default=actual_mentors), description="Mentorer", value="abekat")
form = w.create(team)
return render_template("mentorteams/settings.html", form=form)
def modify_book(b_id):
if request.method == "POST":
if 'cancel' in request.form:
flash(escape("Ændringer annulleret"))
return redirect(url_for("bookkeeper.book", b_id=b_id))
# TOPIC: insert descriptors
b = data.Bucket(request.form)
b.title
b.description
b >> ("UPDATE Books $ WHERE b_id = ?", b_id)
# TOPIC: fetch new participants
usernames = request.form['users']
usernames = usernames.replace('"', '')
usernames = usernames.replace('"', '')
usernames = [name.split()[0] for name in re.split(';\s', usernames) if name != ""]
#usernames = sorted(set(usernames))
old = data.execute("SELECT participant FROM Book_participants where b_id = ?", b_id)
old = [u[0] for u in old]
# TOPIC: update list of participants in database, deleting missing and inserting new
for user in set(old) - set(usernames):
data.execute("DELETE FROM Book_participants where b_id = ? AND participant = ?", b_id, user)
for user in sorted(set(usernames) - set(old)):
data.execute("INSERT INTO Book_participants(b_id, participant) VALUES (?, ?)", b_id, user)
# TODO: maybe we should ensure no one with debts/outstandings is removed?
return redirect(url_for("bookkeeper.book", b_id=b_id))
else:
book = data.execute("SELECT * FROM Books where b_id = ?", b_id)[0]
raw_users = data.execute("SELECT username, name FROM Users")
users = ['\\"{0}\\" {1}'.format(user['username'], user['name']) for user in raw_users]
users.sort()
# TODO: fill with current
participants = data.execute("SELECT * FROM Book_participants as B INNER JOIN Users as U ON B.participant = U.username WHERE b_id = ?", b_id)
participants = ['"{0}" {1}; '.format(p['username'], p['name']) for p in participants]
participants.sort()
participants = "".join(participants)
print(participants)
w = html.WebBuilder()
w.form()
w.formtable()
w.textfield("title", "Overskrift")
w.textarea("description", "beskrivelse")
w.html(html.autocomplete_multiple(users, "users", default=participants), description="Deltagere", value="abekat")
form = w.create(book)
return render_template("form.html", form=form)
def groups_overview():
groups = data.execute(
'SELECT * FROM Group_users INNER JOIN Users USING (username) ORDER BY groupname, username'
)
groups = itertools.groupby(groups, key=get('groupname'))
groups = [(x[0], list(x[1])) for x in groups]
all_groups = data.execute(
"SELECT groupname FROM Groups ORDER BY groupname")
all_groups = [group['groupname'] for group in all_groups]
result = []
for group in all_groups:
if len(groups) > 0 and groups[0][0] == group:
result.append(groups.pop(0))
else:
result.append((group, []))
groups = result
return render_template("admin/group_overview.html", groups=groups)
def renew_password(key):
# EXPLANATION: weed out old password keys
delete_old_keys()
result = data.execute(
"SELECT * FROM User_forgotten_password_keys WHERE key = ?", key)
if len(result) != 1:
flash("Linket du fulgte er desvære udløbet, prøv igen")
return redirect(url_front())
result = result[0]
if request.method == "POST":
b = data.Bucket(request.form)
if b.new1 != b.new2:
flash("De to løsner er ikke ens")
return redirect(url_for('usermanager.renew_password', key=key))
if b.new1 == "":
flash("Du specificerede ikke et nyt løsen")
return redirect(url_for('usermanager.renew_password', key=key))
data.execute("DELETE FROM User_forgotten_password_keys WHERE key = ?",
key)
update_password(result['username'], b.new1)
session['logged_in'] = True
session['username'] = result['username']
return redirect(url_front())
else:
w = html.WebBuilder()
w.form()
w.formtable()
w.password("new1", "Nyt løsen")
w.password("new2", "Gentag nyt løsen")
form = w.create()
return render_template("form.html", form=form)
def login():
error = None
if request.method == 'POST':
username = request.form['username']
raw_password = request.form['password']
if 'forgot' in request.form:
try:
forgot_password(username)
except Exception as e:
if str(e) != "No such user/No valid email":
raise
flash("Kunne ikke sende en mail til denne bruger")
return redirect(url_for('usermanager.login'))
return render_template("usermanager/forgot.html",
username=username)
user = data.execute(
'SELECT password, deleted FROM Users WHERE username = ?', username)
if empty(user) or not password.check(raw_password,
user[0]['password']):
flash('Invalid username or password')
elif user[0]["deleted"]:
flash('Sorry, your user has been deleted')
else:
session['logged_in'] = True
session['username'] = username
groups = data.execute(
'SELECT groupname FROM Group_users WHERE username = ?',
username)
groups = [group['groupname'] for group in groups]
session['groups'] = groups
update_password(username, raw_password)
flash("Login succesful")
return redirect(session.pop('login_origin', url_front()))
return render_template("usermanager/login.html", error=error)
def settings():
if request.method == "POST":
if 'cancel' in request.form:
flash(escape("Ændringer annulleret"))
return redirect(url_for('usermanager.overview'))
username = session["username"]
b = data.Bucket(request.form)
b.name
b.email
b.phone
b.address
b.zipcode
b.city
b.birthday = nonify(b.birthday)
b.driverslicence = "driverslicence" in request.form
b.diku_age
b.about_me # = request.form["about_me"]
b >> ("UPDATE Users $ WHERE username = ?", username)
return redirect(url_for('usermanager.user', username=username))
else:
user = data.execute("SELECT * FROM Users WHERE username = ?",
session["username"])
user = user[0]
user = unnonify(user)
birthday = user["birthday"]
if birthday == None:
birthday = ""
elif isinstance(birthday, datetime.date):
birthday = birthday.isoformat()
w = html.WebBuilder()
w.form()
w.formtable()
w.textfield("name", "Fulde navn")
w.textfield("email", "Email")
w.textfield("phone", "Telefonnummer")
w.textfield("address", "Adresse")
w.textfield("zipcode", "Postnummer")
w.textfield("city", "By")
w.calendar("birthday", "Fødselsdag")
w.checkbox("driverslicence", "Har du kørekort?")
w.textfield("diku_age", "Hvornår startede du på DIKU?")
w.textarea("about_me", "Lidt om mig")
form = w.create(user)
return render_template("form.html", form=form)
def renew_password(key):
# EXPLANATION: weed out old password keys
delete_old_keys()
result = data.execute("SELECT * FROM User_forgotten_password_keys WHERE key = ?", key)
if len(result) != 1:
flash("Linket du fulgte er desvære udløbet, prøv igen")
return redirect(url_front())
result = result[0]
if request.method == "POST":
b = data.Bucket(request.form)
if b.new1 != b.new2:
flash("De to løsner er ikke ens")
return redirect(url_for('usermanager.renew_password', key=key))
if b.new1 == "":
flash("Du specificerede ikke et nyt løsen")
return redirect(url_for('usermanager.renew_password', key=key))
data.execute("DELETE FROM User_forgotten_password_keys WHERE key = ?", key)
update_password(result['username'], b.new1)
session['logged_in'] = True
session['username'] = result['username']
return redirect(url_front())
else:
w = html.WebBuilder()
w.form()
w.formtable()
w.password("new1", "Nyt løsen")
w.password("new2", "Gentag nyt løsen")
form = w.create()
return render_template("form.html", form=form)
def group(groupname):
if request.method == "POST":
if 'cancel' in request.form:
flash("Ændringer anulleret")
return redirect(url_for('admin.groups_overview'))
users = data.execute('SELECT username FROM Users WHERE deleted = ?',
False)
for user in users:
username = user['username']
try:
if username in request.form:
usermanager.group_add_user(groupname, username)
else:
usermanager.group_remove_user(groupname, username)
except psycopg2.IntegrityError as e:
pass
return redirect(url_for('admin.groups_overview'))
else:
users = data.execute(
'SELECT username, name FROM Users WHERE deleted = ? ORDER BY name ASC',
False)
group = data.execute(
'SELECT username FROM Group_users WHERE groupname = ?', groupname)
group = set(user['username'] for user in group)
usernames = (user['username'] for user in users)
kv = {user: (user in group) for user in usernames}
w = html.WebBuilder()
w.form()
w.formtable()
for user in users:
w.checkbox(user['username'],
'"{0}" {1}'.format(user['username'], user['name']))
form = w.create(kv)
return render_template("form.html", form=form)
def settings():
if request.method == "POST":
if 'cancel' in request.form:
flash(escape("Ændringer annulleret"))
return redirect(url_for('usermanager.overview'))
username = session["username"]
b = data.Bucket(request.form)
b.name
b.email
b.phone
b.address
b.zipcode
b.city
b.birthday = nonify(b.birthday)
b.driverslicence = "driverslicence" in request.form
b.diku_age
b.about_me# = request.form["about_me"]
b >> ("UPDATE Users $ WHERE username = ?", username)
return redirect(url_for('usermanager.user', username=username))
else:
user = data.execute("SELECT * FROM Users WHERE username = ?", session["username"])
user = user[0]
user = unnonify(user)
birthday = user["birthday"]
if birthday == None:
birthday = ""
elif isinstance(birthday, datetime.date):
birthday = birthday.isoformat()
w = html.WebBuilder()
w.form()
w.formtable()
w.textfield("name", "Fulde navn")
w.textfield("email", "Email")
w.textfield("phone", "Telefonnummer")
w.textfield("address", "Adresse")
w.textfield("zipcode", "Postnummer")
w.textfield("city", "By")
w.calendar("birthday", "Fødselsdag")
w.checkbox("driverslicence", "Har du kørekort?")
w.textfield("diku_age", "Hvornår startede du på DIKU?")
w.textarea("about_me", "Lidt om mig")
form = w.create(user)
return render_template("form.html", form=form)
def dutyteams(t_id):
if request.method == "POST":
if 'cancel' in request.form:
return redirect(url_for('rustours.rustour', t_id=t_id))
if request.form['new'] != "":
b = data.Bucket()
b.name = request.form['new']
b.t_id = t_id
b >= "Dutyteams"
dutyteams = data.execute("SELECT d_id FROM Dutyteams WHERE t_id = ?",
t_id)
dutyteams = set(str(dutyteam['d_id']) for dutyteam in dutyteams)
for d_id in request.form.keys():
if d_id in dutyteams:
b = data.Bucket()
b.name = request.form[d_id]
b >> ("UPDATE Dutyteams $ WHERE t_id = ? AND d_id = ?", t_id,
d_id)
return redirect(url_for("rustours.rustour", t_id=t_id))
else:
dutyteams = data.execute(
"SELECT * FROM Dutyteams WHERE t_id = ? ORDER BY d_id ASC", t_id)
w = html.WebBuilder()
w.form()
w.formtable()
for dutyteam in dutyteams:
w.textfield(dutyteam['d_id'], "Omdøb:", value=dutyteam['name'])
w.textfield("new", "Nyt tjansehold:")
form = w.create()
return render_template("form.html", form=form)
def random_greeting():
result = random.choice(
[ "GRIS"
, "Bacon"
, "Velkommen"
, "Nu med ekstra procenter!"
, "made in Emacs"
, "GTs inside"
, "Der er <i>n</i> dage til rusturen"
, "git push -f"
, lambda: "8"+("="*random.randint(1,17))+"D"
, lambda: ("_-‾-"*random.randint(1,10))+"=:>"
, ":(){ :|:& };:"
, "public static void main(String[] args) {"
, "Søren lavede denne side"
, "Caro har også hjulpet"
, "Formanden er dum!"
, "Er du bange for tyngdekraften?"
, "[email protected]:RusKursusGruppen/GRIS.git"
, "Drevet af Flask, GT flask..."
, "IT-Kalifen er en slacker!"
, "Robert'); DROP TABLE Students;--"
, "[]"
, "<a href=\"http://en.wikipedia.org/wiki/Special:Random\">Learn more:</a>"
, "++++++++++[>+++++++>++++++++++>+++>+<<<<-]>++.>+.+++++++..+++.>++.<<+++++++++++++++.>.+++.------.--------.>+.>."
, lambda: "Der er {0} russer i databasen".format(data.execute("SELECT COALESCE(COUNT(r_id),0) FROM Russer")[0][0])
, lambda: "<i>Latest commit message:</i> " + subprocess.check_output(['git', 'log', '-1', '--pretty=%B']).decode('utf-8').rstrip('\n')
, lambda: "<i>Latest committer:</i> " + subprocess.check_output(['git', 'log', '-1', '--pretty=%an']).decode('utf-8').rstrip('\n')
, lambda: "<i>Latest commit date:</i> " + subprocess.check_output(['git', 'log', '-1', '--pretty=%ad']).decode('utf-8').rstrip('\n')
, "Emacs, den objektivt bedste editor"
, "O(n²)"
, "λf.(λx.f (x x)) (λx.f (x x))"
, "Kodet med knytnæver!"
, "Søren har udviklet RKG-OS i skyen der kan ALT!"
, "Søren snakker om kommunister og skinke!"
, "Lund er nizzle i haven. NB og Munksgaard er nice og pooler"
, "Nu gør RKG som vi plejer og kører en ligegyldig kommentar op til en laaaaang debat hvor alle skal sige det samme som de andre men blot med et lille twist (not)!"
, "lalalalalala..."
, "Vi har mange programmer til ølregnskab... alle er i BETA"
, "*****@*****.**"
, "qsort (p:xs) = qsort [x | x<-xs, x<p] ++ [p] ++ qsort [x | x<-xs, x>=p]"
, "Er der ild i piben?"
, "Fedt med mad"
, "Tukanen er det mest retarderede menneske nogensinde"
, "3% kode, 79% slam"])
if callable(result):
return result()
return result
def add_to_rustour(m_id):
if request.method == "POST":
if 'cancel' in request.form:
flash(escape("Ingen ændringer"))
return redirect(url_for('mentorteams.mentorteam', m_id=m_id))
b = data.Bucket(request.form)
russer = data.execute("SELECT r_id FROM Russer WHERE mentor = ?", m_id)
russer = [(b.tour_name, rus['r_id']) for rus in russer]
data.executemany("UPDATE Russer SET rustour = ? WHERE r_id = ?", russer)
flash("Alle russer på mentorholdet er blevet sat på rustur".format(b.tour_name))
return redirect(url_for("mentorteams.mentorteam", m_id=m_id))
else:
rustours = data.execute("SELECT * FROM Tours WHERE year = ?", rkgyear())
rustours = [(tour['t_id'], tour['tour_name']) for tour in rustours]
wb = html.WebBuilder()
wb.form()
wb.formtable()
wb.select("tour_name", "Tildel rustur", rustours)
form = wb.create()
return render_template("form.html", form=form)
def new():
if request.method == "POST":
if 'cancel' in request.form:
flash(escape("Rus IKKE tilføjet"))
return redirect(url_for('rusmanager.overview'))
name = " ".join([x.capitalize() for x in request.form['name'].split()])
r_id = data.execute("INSERT INTO Russer(name) VALUES(?) RETURNING r_id", name)[0][0]
flash("Rus oprettet")
return redirect(url_for('rusmanager.rus', r_id=r_id))
else:
w = html.WebBuilder()
w.form()
w.formtable()
w.textfield("name", "Navn")
form = w.create()
return render_template("form.html", form=form)
def status():
russer = data.execute("SELECT count(*) from Russer")[0]['count']
russer_attending_campus = data.execute("SELECT count(*) from Russer where attending_campus = ?", True)[0]['count']
russer_attending_rustour = data.execute("SELECT count(*) from Russer where attending_rustour = ?", True)[0]['count']
# rustour_attending_campus = data.execute("SELECT * FROM (SELECT t_id, COUNT(t_id) FROM Tours LEFT JOIN Russer ON (rustour = t_id) WHERE year = ? AND attending_campus = ? GROUP BY t_id) AS b INNER JOIN Tours USING (t_id) ORDER BY tour_name ASC", rkgyear(), True)
# rustour_attending_rustour = data.execute("SELECT * FROM (SELECT t_id, COUNT(t_id) FROM Tours LEFT JOIN Russer ON (rustour = t_id) WHERE year = ? AND attending_rustour = ? GROUP BY t_id) AS b INNER JOIN Tours USING (t_id) ORDER BY tour_name ASC", rkgyear(), True)
rustour_count = data.execute("SELECT * FROM Tours LEFT JOIN (SELECT rustour AS t_id, COUNT(*) FROM Russer GROUP BY rustour) AS a using (t_id) WHERE year = ? ORDER BY tour_name ASC", 2014)
rustour_attending_campus = data.execute("SELECT * FROM Tours LEFT JOIN (SELECT rustour AS t_id, COUNT(*) FROM Russer where attending_campus = ? GROUP BY rustour) AS a using (t_id) WHERE year = ? ORDER BY tour_name ASC", True, 2014)
rustour_attending_rustour = data.execute("SELECT * FROM Tours LEFT JOIN (SELECT rustour AS t_id, COUNT(*) FROM Russer where attending_rustour = ? GROUP BY rustour) AS a using (t_id) WHERE year = ? ORDER BY tour_name ASC", True, 2014)
rustours = zip(rustour_count, rustour_attending_campus, rustour_attending_rustour)
rustours = [{'t_id':x['t_id'], 'tour_name':x['tour_name'], 'count':x['count'], 'attending_campus':c['count'], 'attending_rustour':r['count']} for x,c,r in rustours]
# mentorteam_attending_campus = data.execute("SELECT * FROM (SELECT m_id, COUNT(m_id) FROM Mentorteams LEFT JOIN Russer ON (mentor = m_id) WHERE year = ? AND attending_campus = ? GROUP BY m_id) AS b INNER JOIN Mentorteams USING (m_id) ORDER BY mentor_names ASC", rkgyear(), True)
# mentorteam_attending_rustour = data.execute("SELECT * FROM (SELECT m_id, COUNT(m_id) FROM Mentorteams LEFT JOIN Russer ON (mentor = m_id) WHERE year = ? AND attending_rustour = ? GROUP BY m_id) AS b INNER JOIN Mentorteams USING (m_id) ORDER BY mentor_names ASC", rkgyear(), True)
mentorteam_count = data.execute("SELECT * FROM Mentorteams LEFT JOIN (SELECT mentor AS m_id, COUNT(*) FROM Russer GROUP BY mentor) AS a using (m_id) WHERE year = ? ORDER BY mentor_names ASC", 2014)
mentorteam_attending_campus = data.execute("SELECT * FROM Mentorteams LEFT JOIN (SELECT mentor AS m_id, COUNT(*) FROM Russer where attending_campus = ? GROUP BY mentor) AS a using (m_id) WHERE year = ? ORDER BY mentor_names ASC", True, 2014)
mentorteam_attending_rustour = data.execute("SELECT * FROM Mentorteams LEFT JOIN (SELECT mentor AS m_id, COUNT(*) FROM Russer where attending_rustour = ? GROUP BY mentor) AS a using (m_id) WHERE year = ? ORDER BY mentor_names ASC", True, 2014)
mentorteams = zip(mentorteam_count, mentorteam_attending_campus, mentorteam_attending_rustour)
mentorteams = [{'m_id':x['m_id'], 'mentor_names':x['mentor_names'], 'count':x['count'], 'attending_campus':c['count'], 'attending_rustour':r['count']} for x,c,r in mentorteams]
# return ""
return render_template("rusmanager/status.html", russer=russer, russer_attending_campus=russer_attending_campus, russer_attending_rustour=russer_attending_rustour, rustours=rustours, mentorteams=mentorteams)
def forgot_password(username):
user = data.execute("SELECT name, email from Users WHERE username = ?",
username)
if len(user) != 1:
raise Exception("No such user/No valid email")
min = config.USER_CREATION_KEY_MIN_LENGTH
max = config.USER_CREATION_KEY_MAX_LENGTH
length = random.randrange(min, max)
alphabet = string.ascii_letters + string.digits
finished = False
while not finished:
key = ''.join(random.choice(alphabet) for x in range(length))
try:
b = data.Bucket()
b.username = username
b.key = key
b.created = now()
b >= "User_forgotten_password_keys"
finished = True
break
except psycopg2.IntegrityError as e:
if e.pgerror.startswith(
'ERROR: duplicate key value violates unique constraint "user_forgotten_password_keys_pkey"'
):
finished = False
continue
else:
raise
user = user[0]
email = user['email']
if email == None or email == '':
raise Exception("No such user/No valid email")
url = config.URL + url_for("usermanager.renew_password", key=key)
text = forgot_password_mail.format(name=user['name'], url=url)
mail.send(email, "Glemt løsen", text)
def forgot_password(username):
user = data.execute("SELECT name, email from Users WHERE username = ?", username)
if len(user) != 1:
raise Exception("No such user/No valid email")
min = config.USER_CREATION_KEY_MIN_LENGTH
max = config.USER_CREATION_KEY_MAX_LENGTH
length = random.randrange(min, max)
alphabet = string.ascii_letters + string.digits
finished = False
while not finished:
key = ''.join(random.choice(alphabet) for x in range(length))
try:
b = data.Bucket()
b.username = username
b.key = key
b.created = now()
b >= "User_forgotten_password_keys"
finished = True
break
except psycopg2.IntegrityError as e:
if e.pgerror.startswith('ERROR: duplicate key value violates unique constraint "user_forgotten_password_keys_pkey"'):
finished = False
continue
else:
raise
user = user[0]
email = user['email']
if email == None or email == '':
raise Exception("No such user/No valid email")
url = config.URL + url_for("usermanager.renew_password", key=key)
text = forgot_password_mail.format(name=user['name'], url=url)
mail.send(email, "Glemt løsen", text)
def change_password():
if request.method == "POST":
if 'cancel' in request.form:
flash(escape("Ændringer annulleret"))
return redirect(url_for('usermanager.settings'))
username = session["username"]
current_password = data.execute(
"SELECT password FROM Users WHERE username = ?",
username)[0]['password']
b = data.Bucket(request.form)
if not password.check(b.current, current_password):
return logout()
if b.new1 != b.new2:
flash("De to løsner er ikke ens")
return redirect(url_for('usermanager.change_password'))
if b.new1 == "":
flash("Du specificerede ikke et nyt løsen")
return redirect(url_for('usermanager.change_password'))
update_password(username, b.new1)
return redirect(url_for('usermanager.settings'))
else:
w = html.WebBuilder()
w.form()
w.formtable()
w.password("current", "Nuværende løsen")
w.password("new1", "Nyt løsen")
w.password("new2", "Gentag nyt løsen")
form = w.create()
return render_template("form.html", form=form)
def rustour(t_id):
tour = data.execute("SELECT * FROM Tours WHERE t_id = ?", t_id)[0]
russer = data.execute("SELECT * FROM Russer WHERE rustour = ? ORDER BY name ASC", t_id)
tutors = data.execute("SELECT * FROM tours_tutors WHERE t_id = ? ORDER BY username ASC", t_id)
dutyteams = data.execute("SELECT Russer.r_id, Russer.name, Dutyteams.name as dutyteam FROM Russer FULL OUTER JOIN Dutyteams ON Russer.dutyteam = Dutyteams.d_id WHERE Russer.rustour = ? and Russer.dutyteam IS NOT NULL ORDER BY Dutyteams.d_id ASC", t_id)
dutyteams = itertools.groupby(dutyteams, key=get("dutyteam"))
dutyteams = [(x[0], list(x[1])) for x in dutyteams]
all_teams = data.execute("SELECT name FROM Dutyteams WHERE t_id = ? ORDER BY d_id ASC", t_id)
all_teams = [x['name'] for x in all_teams]
result = []
for team in all_teams:
if len(dutyteams) > 0 and dutyteams[0][0] == team:
result.append(dutyteams.pop(0))
else:
result.append((team, []))
dutyteams = result
unassigned = data.execute("SELECT r_id, name FROM Russer WHERE rustour = ? AND dutyteam IS NULL ORDER BY name DESC", t_id)
return render_template("rustours/rustour.html", tour=tour, russer=russer, tutors=tutors, dutyteams=dutyteams, unassigned=unassigned)
def rustour(t_id):
tour = data.execute("SELECT * FROM Tours WHERE t_id = ?", t_id)[0]
russer = data.execute(
"SELECT * FROM Russer WHERE rustour = ? ORDER BY name ASC", t_id)
tutors = data.execute(
"SELECT * FROM tours_tutors WHERE t_id = ? ORDER BY username ASC",
t_id)
dutyteams = data.execute(
"SELECT Russer.r_id, Russer.name, Dutyteams.name as dutyteam FROM Russer FULL OUTER JOIN Dutyteams ON Russer.dutyteam = Dutyteams.d_id WHERE Russer.rustour = ? and Russer.dutyteam IS NOT NULL ORDER BY Dutyteams.d_id ASC",
t_id)
dutyteams = itertools.groupby(dutyteams, key=get("dutyteam"))
dutyteams = [(x[0], list(x[1])) for x in dutyteams]
all_teams = data.execute(
"SELECT name FROM Dutyteams WHERE t_id = ? ORDER BY d_id ASC", t_id)
all_teams = [x['name'] for x in all_teams]
result = []
for team in all_teams:
if len(dutyteams) > 0 and dutyteams[0][0] == team:
result.append(dutyteams.pop(0))
else:
result.append((team, []))
dutyteams = result
unassigned = data.execute(
"SELECT r_id, name FROM Russer WHERE rustour = ? AND dutyteam IS NULL ORDER BY name DESC",
t_id)
return render_template("rustours/rustour.html",
tour=tour,
russer=russer,
tutors=tutors,
dutyteams=dutyteams,
unassigned=unassigned)
def update_password(username, raw_password):
passwd = password.encode(raw_password)
data.execute("UPDATE Users SET password = ? WHERE username = ?", passwd,
username)
def group_add_user(groupname, username):
data.execute("INSERT INTO Group_users(groupname, username) VALUES(?,?)",
groupname, username)
def overview():
users = data.execute(
"select username, name from Users where deleted = ? order by name",
False)
return render_template("usermanager/overview.html", users=users)
def deleted_users():
users = data.execute(
"select username, name from Users where deleted = ? order by name",
True)
return render_template("usermanager/deleted_users.html", users=users)
def set_user_groups(username, groups):
data.execute("DELETE FROM Group_users WHERE username = ?", username)
for group in groups:
group_add_user(group, username)
def overview():
users = data.execute("select username, name from Users where deleted = ? order by name", False)
return render_template("usermanager/overview.html", users=users)
def group_remove_user(groupname, username):
data.execute(
"DELETE FROM Group_users WHERE groupname = ? AND username = ?",
groupname, username)
def deleted_users():
users = data.execute("select username, name from Users where deleted = ? order by name", True)
return render_template("usermanager/deleted_users.html", users=users)
