def get_payments(self):
db = hackhub.get_db()
cur = db.cursor()
cur.execute(
"select year, month, recorded, admin from payment where username=? order by year desc, month desc",
(self.username, ))
return cur.fetchall()
def use_code(code):
db = get_db()
cur = db.cursor()
cur.execute("""UPDATE door_codes SET used = ? WHERE code == ? """,
(now(), code))
db.commit()
cur.close()
def all_member():
db = hackhub.get_db()
cur = db.cursor()
cur.execute('select username from member')
usernames = cur.fetchall()
members = [Member(username[0]) for username in usernames]
return members
def all_member():
db = hackhub.get_db()
cur = db.cursor()
cur.execute('select username from member')
usernames = cur.fetchall()
members = [Member(username[0]) for username in usernames]
return members
def admin_behind():
attrs = [int(time.strftime("%m")), int(time.strftime("%Y")),
int(time.strftime("%m")) - 1, int(time.strftime("%Y")),
int(time.strftime("%m")) - 2, int(time.strftime("%Y")),
int(time.strftime("%m")) - 3, int(time.strftime("%Y"))]
#handle months being last year
# ugh, should really have this as a function...
if attrs[2] <= 0:
attrs[2] = 12 + attrs[2]
attrs[3] = attrs[3] - 1
if attrs[4] <= 0:
attrs[4] = 12 + attrs[4]
attrs[5] = attrs[5] - 1
if attrs[6] <= 0:
attrs[6] = 12 + attrs[6]
attrs[7] = attrs[7] - 1
attrs = tuple(attrs)
db = get_db()
cur = db.cursor()
cur.execute("SELECT username FROM member WHERE username NOT IN (SELECT username FROM payment WHERE (month == ? AND year == ?) OR (month == ? AND year == ?) OR (month == ? AND year == ?) OR (month == ? AND year == ?)) AND username NOT IN (SELECT user FROM dismembered)", attrs)
users = cur.fetchall()
behind = []
errors = ""
for user in users:
m = Member(user[0])
try:
last_payment = m.get_payments()[0]
year, month = last_payment[0], last_payment[1]
except Exception as e:
year, month = None, None
errors += str(e) + "\n"
behind.append((m.username, m.profile["realname"], m.profile["email"], year, month))
return render_template('admin_behind.html', behind=behind, errors=errors)
def use_code(code):
db = get_db()
cur = db.cursor()
cur.execute("""UPDATE door_codes SET used = ? WHERE code == ? """,
(now(), code))
db.commit()
cur.close()
def refresh(self):
db = hackhub.get_db()
curr = db.cursor()
curr.execute(
"SELECT username FROM payment WHERE month = ? and year = ?",
(self.month, self.year))
self.payments = curr.fetchall()
def is_active(self):
db = hackhub.get_db()
cur = db.cursor()
cur.execute("select * from dismembered where user=? and date_reset is null", (self.username,))
if cur.fetchone() == None:
return True
return False
def new_event(username, event_type, extra=''):
db = get_db()
cur = db.cursor()
cur.execute("""INSERT INTO events (name, type, timestamp, extra) VALUES (?, ?, ?, ?)""",
(username, event_type, calendar.timegm(time.gmtime()), extra))
db.commit()
cur.close()
def new_status(state, message, username):
db = hackhub.get_db()
cur = db.cursor()
cur.execute("INSERT INTO status VALUES (null, ?, ?, ?, datetime('now'))",
(state,
message,
username));
db.commit()
def profile_emails():
m = Member(session['username'])
uprefs = m.get_email_prefs()
db = get_db()
cur = db.cursor()
cur.execute("SELECT code, description FROM email_events")
events = cur.fetchall()
return render_template("email_prefs.html", uprefs=uprefs, events=events)
def profile_emails():
m = Member(session['username'])
uprefs = m.get_email_prefs()
db = get_db()
cur = db.cursor()
cur.execute("SELECT code, description FROM email_events")
events = cur.fetchall()
return render_template("email_prefs.html", uprefs=uprefs, events=events)
def new_event(username, event_type, extra=''):
db = get_db()
cur = db.cursor()
cur.execute(
"""INSERT INTO events (name, type, timestamp, extra) VALUES (?, ?, ?, ?)""",
(username, event_type, calendar.timegm(time.gmtime()), extra))
db.commit()
cur.close()
def is_active(self):
db = hackhub.get_db()
cur = db.cursor()
cur.execute("select * from dismembered where user=?",
(self.username, ))
if cur.fetchone() == None:
return True
return False
def __init__(self, start = 0):
db = hackhub.get_db()
cur = db.cursor()
cur.execute("select * from status order by thetime desc limit ?,10", (start,))
self.status = cur.fetchall()
def new_code(username):
code = " ".join([word(), word()])
db = get_db()
cur = db.cursor()
cur.execute("""INSERT INTO door_codes (user, code, created) VALUES (?, ?, ?)""",
(username, code, now()))
db.commit()
cur.close()
return code
def user_url_code(username):
db = get_db()
cur = db.cursor()
cur.execute("""SELECT code FROM url_codes WHERE username = ?""", (username,))
res = cur.fetchall()
if res:
return res[0][0]
else:
return ""
def __init__(self, start=0):
db = hackhub.get_db()
cur = db.cursor()
cur.execute("select * from status order by thetime desc limit ?,10",
(start, ))
self.status = cur.fetchall()
def update_profile(self, details):
for key in details.keys():
if key in Member.schema:
db = hackhub.get_db()
cur = db.cursor()
cur.execute("update member set %s=? where username=?" % (key,),
(details[key], self.username))
db.commit()
self.refresh_profile()
def new_code(username):
code = " ".join([word(), word()])
db = get_db()
cur = db.cursor()
cur.execute("""INSERT INTO door_codes (user, code, created) VALUES (?, ?, ?)""",
(username, code, now()))
db.commit()
cur.close()
return code
def admin_applications():
if request.method == 'GET':
db = get_db()
cur = db.cursor()
cur.execute(
"""SELECT id, username, realname, nickname, email, address, received
FROM application WHERE accepted=0 AND ignored=0 ORDER BY id DESC"""
)
applications = cur.fetchall()
cur.close()
print applications
return render_template('admin_applications.html',
applications=applications)
else:
id = int(request.form['id'])
db = get_db()
cur = db.cursor()
cur.execute(
"""SELECT username, realname, nickname, email, address
FROM application WHERE id=? AND accepted=0""", (id, ))
row = cur.fetchone()
if not row:
cur.close()
return redirect('/hub/admin/applications')
details = dict(zip(row.keys(), row))
username = details['username']
details['key'] = 0
details['adm'] = 0
new_member(username, details)
cur.execute('UPDATE application SET accepted=1 WHERE id=?', (id, ))
db.commit()
cur.close()
l = ldap.initialize(settings.LDAP_URI)
l.simple_bind_s(settings.LDAP_BIND_DN, settings.LDAP_PASSWORD)
l.modify_s(settings.LDAP_MEMBERS_GROUP_DN, [
(ldap.MOD_ADD, "member",
"uid=" + username.encode("ascii", "ignore") + "," +
settings.LDAP_USERS_DN),
])
return redirect('/hub/admin')
def undismember(username, reason):
db = hackhub.get_db()
cur = db.cursor()
cur.execute("select * from dismembered where user=?", (username,))
if cur.fetchone() == None:
raise ValueError("No such member to undismember")
t = int(time.time())
cur.execute("UPDATE dismembered SET date_reset=?, reason_reset=? where user=?", (t, reason, username))
db.commit()
cur.close()
def update_profile(self, details):
for key in details.keys():
if key in Member.schema:
db = hackhub.get_db()
cur = db.cursor()
cur.execute(
"update member set %s=? where username=?" % (key, ),
(details[key], self.username))
db.commit()
self.refresh_profile()
def add_url_code(username):
code = ''.join([random.choice(string.ascii_lowercase+string.digits) for n in xrange(64)])
db = get_db()
cur = db.cursor()
cur.execute("""SELECT code, username FROM url_codes WHERE username = ?""", (username,))
if cur.fetchall():
cur.execute("""DELETE FROM url_codes WHERE username = ?""", (username,))
cur.execute("INSERT INTO url_codes (code, username) VALUES (?, ?)""", (code, username))
db.commit()
cur.close()
def add_payment(self, month, year, admin=None):
db = hackhub.get_db()
cur = db.cursor()
if admin == None:
cur.execute("insert into payment (username, month, year) values (?, ?, ?)",
(self.username, month, year))
else:
cur.execute("insert into payment (username, month, year, recorded, admin) values (?, ?, ?, ?, ?)",
(self.username, month, year, int(time.time()), admin))
db.commit()
db.close()
def member_list():
month = datetime.utcnow()
members = {}
curr = hackhub.get_db().cursor()
for i in xrange(2):
for user in Payments(month.month, month.year).payments:
curr.execute("SELECT username, email FROM member WHERE username = ?", user)
res = curr.fetchone()
members[res[1]] = res[0]
month += monthdelta(-1)
return members
def refresh_profile(self):
db = hackhub.get_db()
cur = db.cursor()
cur.execute("select * from member where username=?", (self.username, ))
profile = cur.fetchone()
self.profile = {}
if profile == None:
return False
for i in range(0, len(profile)):
self.profile[Member.schema[i]] = profile[i]
return True
def is_paid(self, delta=0):
attrs = [self.username, int(time.strftime("%m")), int(time.strftime("%Y"))]
attrs[1] += delta
if attrs[1] <= 0:
attrs[1] = 12 - attrs[1]
attrs[2] -= 1
db = hackhub.get_db()
cur = db.cursor()
cur.execute("select * from payment where username=? and month=? and year=?", attrs)
if cur.fetchone() == None:
return False
return True
def member_list():
month = datetime.utcnow()
members = {}
curr = hackhub.get_db().cursor()
for i in xrange(2):
for user in Payments(month.month, month.year).payments:
curr.execute(
"SELECT username, email FROM member WHERE username = ?", user)
res = curr.fetchone()
members[res[1]] = res[0]
month += monthdelta(-1)
return members
def add_payment(self, month, year, admin=None):
db = hackhub.get_db()
cur = db.cursor()
if admin == None:
cur.execute(
"insert into payment (username, month, year) values (?, ?, ?)",
(self.username, month, year))
else:
cur.execute(
"insert into payment (username, month, year, recorded, admin) values (?, ?, ?, ?, ?)",
(self.username, month, year, int(time.time()), admin))
db.commit()
db.close()
def refresh_profile(self):
db = hackhub.get_db()
cur = db.cursor()
cur.execute("select * from member where username=?", (
self.username,
))
profile = cur.fetchone()
self.profile = {}
if profile == None:
return False
for i in range(0, len(profile)):
self.profile[Member.schema[i]] = profile[i]
return True
def admin_applications():
if request.method == 'GET':
db = get_db()
cur = db.cursor()
cur.execute("""SELECT id, username, realname, nickname, email, address, received
FROM application WHERE accepted=0 AND ignored=0 ORDER BY id DESC""")
applications = cur.fetchall()
cur.close()
print applications
return render_template('admin_applications.html', applications=applications)
else:
id = int(request.form['id'])
db = get_db()
cur = db.cursor()
cur.execute("""SELECT username, realname, nickname, email, address
FROM application WHERE id=? AND accepted=0""", (id,))
row = cur.fetchone()
if not row:
cur.close()
return redirect('/hub/admin/applications')
details = dict(zip(row.keys(), row))
username = details['username']
details['key'] = 0
details['adm'] = 0
new_member(username, details)
cur.execute('UPDATE application SET accepted=1 WHERE id=?', (id,))
db.commit()
cur.close()
l = ldap.initialize(settings.LDAP_URI)
l.simple_bind_s(settings.LDAP_BIND_DN, settings.LDAP_PASSWORD)
l.modify_s(settings.LDAP_MEMBERS_GROUP_DN, [
(ldap.MOD_ADD, "member", "uid="+username.encode("ascii", "ignore")+","+settings.LDAP_USERS_DN),
])
return redirect('/hub/admin')
def get_email_prefs(self):
db = hackhub.get_db()
cur = db.cursor()
cur.execute("SELECT code, description, def FROM email_events")
em_e = cur.fetchall()
prefs = {}
for ev in em_e:
cur.execute("SELECT pref FROM email_prefs WHERE user = ? AND code = ?",
(self.username, ev[0]))
p = cur.fetchall()
if len(p) == 0:
prefs[ev[0]] = ev[2]
else:
prefs[ev[0]] = p[0][0]
return prefs
def get_email_prefs(self):
db = hackhub.get_db()
cur = db.cursor()
cur.execute("SELECT code, description, def FROM email_events")
em_e = cur.fetchall()
prefs = {}
for ev in em_e:
cur.execute(
"SELECT pref FROM email_prefs WHERE user = ? AND code = ?",
(self.username, ev[0]))
p = cur.fetchall()
if len(p) == 0:
prefs[ev[0]] = ev[2]
else:
prefs[ev[0]] = p[0][0]
return prefs
def __init__(self, id=-1):
db = hackhub.get_db()
cur = db.cursor()
if id == -1:
cur.execute("select state, message, username, strftime('%s', thetime) from status order by thetime desc limit 1")
status = cur.fetchone()
else:
cur.execute("select state, message, username, strftime('%s', thetime) where id=?", (id,))
status = cur.fetchone()
self.status = {'open': (status[0] == 1),
'message': status[1],
'trigger_person': status[2],
'lastchange': int(status[3])}
def is_paid(self, delta=0):
attrs = [
self.username,
int(time.strftime("%m")),
int(time.strftime("%Y"))
]
attrs[1] += delta
if attrs[1] <= 0:
attrs[1] = 12 - attrs[1]
attrs[2] -= 1
db = hackhub.get_db()
cur = db.cursor()
cur.execute(
"select * from payment where username=? and month=? and year=?",
attrs)
if cur.fetchone() == None:
return False
return True
def hub_apply_for_membership():
if not session.get('application_username'):
return redirect('/hub/')
try:
m = Member(session['application_username'])
except:
pass
else:
session['username'] = session['application_username']
del session['application_username']
return redirect('/hub/')
db = get_db()
cur = db.cursor()
cur.execute('SELECT 1 FROM application WHERE username=?',
(session['application_username'], ))
if cur.fetchone():
cur.close()
return render_template('membership_apply_thanks.html',
status=Status().status)
cur.close()
if request.method == 'GET':
return render_template('membership_apply.html', status=Status().status)
else:
try:
username = session['application_username']
realname = request.form['realname']
nickname = request.form['nickname']
email = request.form['email']
address = request.form['address']
except KeyError:
return redirect('/hub/apply-for-membership')
cur = db.cursor()
cur.execute(
"""insert into application (username, realname,
nickname, email, address, received) values (?, ?, ?, ?, ?, strftime('%s', 'now'))""",
(username, realname, nickname, email, address))
db.commit()
cur.close()
return render_template('membership_apply_thanks.html',
status=Status().status)
def admin_behind():
attrs = [
int(time.strftime("%m")),
int(time.strftime("%Y")),
int(time.strftime("%m")) - 1,
int(time.strftime("%Y")),
int(time.strftime("%m")) - 2,
int(time.strftime("%Y")),
int(time.strftime("%m")) - 3,
int(time.strftime("%Y"))
]
#handle months being last year
# ugh, should really have this as a function...
if attrs[2] <= 0:
attrs[2] = 12 + attrs[2]
attrs[3] = attrs[3] - 1
if attrs[4] <= 0:
attrs[4] = 12 + attrs[4]
attrs[5] = attrs[5] - 1
if attrs[6] <= 0:
attrs[6] = 12 + attrs[6]
attrs[7] = attrs[7] - 1
attrs = tuple(attrs)
db = get_db()
cur = db.cursor()
cur.execute(
"SELECT username FROM member WHERE username NOT IN (SELECT username FROM payment WHERE (month == ? AND year == ?) OR (month == ? AND year == ?) OR (month == ? AND year == ?) OR (month == ? AND year == ?)) AND username NOT IN (SELECT user FROM dismembered)",
attrs)
users = cur.fetchall()
behind = []
errors = ""
for user in users:
m = Member(user[0])
try:
last_payment = m.get_payments()[0]
year, month = last_payment[0], last_payment[1]
except Exception as e:
year, month = None, None
errors += str(e) + "\n"
behind.append((m.username, m.profile["realname"], m.profile["email"],
year, month))
return render_template('admin_behind.html', behind=behind, errors=errors)
def set_email_pref(self, code, pref):
db = hackhub.get_db()
cur = db.cursor()
cur.execute("SELECT def FROM email_events WHERE code = ?", (code,))
default = cur.fetchone()
if not default:
raise Exception("No event found with code %s", code)
if pref == default[0]:
cur.execute("DELETE FROM email_prefs WHERE user = ? and code = ?",
(self.username, code))
else:
cur.execute("SELECT * FROM email_prefs WHERE user = ? and code = ?",
(self.username, code))
if cur.fetchone():
cur.execute("UPDATE email_prefs SET pref = ? WHERE user = ? AND code = ?",
(pref, self.username, code))
else:
cur.execute("INSERT INTO email_prefs (user, code, pref) VALUES (?, ?, ?)",
(self.username, code, pref))
db.commit()
def hub_apply_for_membership():
if not session.get('application_username'):
return redirect('/hub/')
try:
m = Member(session['application_username'])
except:
pass
else:
session['username'] = session['application_username']
del session['application_username']
return redirect('/hub/')
db = get_db()
cur = db.cursor()
cur.execute('SELECT 1 FROM application WHERE username=?',
(session['application_username'],))
if cur.fetchone():
cur.close()
return render_template('membership_apply_thanks.html', status=Status().status)
cur.close()
if request.method == 'GET':
return render_template('membership_apply.html', status=Status().status)
else:
try:
username = session['application_username']
realname = request.form['realname']
nickname = request.form['nickname']
email = request.form['email']
address = request.form['address']
except KeyError:
return redirect('/hub/apply-for-membership')
cur = db.cursor()
cur.execute("""insert into application (username, realname,
nickname, email, address, received) values (?, ?, ?, ?, ?, strftime('%s', 'now'))""",
(username, realname, nickname, email, address))
db.commit()
cur.close()
return render_template('membership_apply_thanks.html', status=Status().status)
def __init__(self, id=-1):
db = hackhub.get_db()
cur = db.cursor()
if id == -1:
cur.execute(
"select state, message, username, strftime('%s', thetime) from status order by thetime desc limit 1"
)
status = cur.fetchone()
else:
cur.execute(
"select state, message, username, strftime('%s', thetime) where id=?",
(id, ))
status = cur.fetchone()
self.status = {
'open': (status[0] == 1),
'message': status[1],
'trigger_person': status[2],
'lastchange': int(status[3])
}
def set_email_pref(self, code, pref):
db = hackhub.get_db()
cur = db.cursor()
cur.execute("SELECT def FROM email_events WHERE code = ?", (code, ))
default = cur.fetchone()
if not default:
raise Exception("No event found with code %s", code)
if pref == default[0]:
cur.execute("DELETE FROM email_prefs WHERE user = ? and code = ?",
(self.username, code))
else:
cur.execute(
"SELECT * FROM email_prefs WHERE user = ? and code = ?",
(self.username, code))
if cur.fetchone():
cur.execute(
"UPDATE email_prefs SET pref = ? WHERE user = ? AND code = ?",
(pref, self.username, code))
else:
cur.execute(
"INSERT INTO email_prefs (user, code, pref) VALUES (?, ?, ?)",
(self.username, code, pref))
db.commit()
def new_status(state, message, username):
db = hackhub.get_db()
cur = db.cursor()
cur.execute("INSERT INTO status VALUES (null, ?, ?, ?, datetime('now'))",
(state, message, username))
db.commit()
def payment_list():
db = get_db()
cur = db.cursor()
cur.execute("SELECT * FROM payment ORDER BY id;")
payments = cur.fetchall()
return render_template('admin_payments.html', payments=payments)
(pref, self.username, code))
else:
cur.execute(
"INSERT INTO email_prefs (user, code, pref) VALUES (?, ?, ?)",
(self.username, code, pref))
db.commit()
def new_member(username, details):
try:
if Member(username).refresh_profile(): # Does the user exist?
return False
except Exception, e:
if not "No such member" in str(e):
raise e
db = hackhub.get_db()
cur = db.cursor()
cur.execute(
"""insert into member (username, realname,
nickname, email, address, key, adm) values (?, ?, ?, ?, ?, ?, ?)""",
(username, details['realname'], details['nickname'], details['email'],
details['address'], details['key'], details['adm']))
db.commit()
cur.close()
try:
m = Member(username)
except Exception, e:
if not "No such member" in str(e):
raise e
m.update_profile(details)
def url_codes():
db = get_db()
cur = db.cursor()
cur.execute("""SELECT code, username FROM url_codes""")
return cur.fetchall()
def get_payments(self):
db = hackhub.get_db()
cur = db.cursor()
cur.execute("select year, month, recorded, admin from payment where username=? order by year desc, month desc", (self.username,))
return cur.fetchall()
def active_codes():
db = get_db()
cur = db.cursor()
cur.execute("""SELECT code, created, user FROM door_codes WHERE used IS NULL""")
return cur.fetchall()
def active_codes():
db = get_db()
cur = db.cursor()
cur.execute("""SELECT code, created, user FROM door_codes WHERE used IS NULL""")
return cur.fetchall()
def refresh(self):
db = hackhub.get_db()
curr = db.cursor()
curr.execute("SELECT username FROM payment WHERE month = ? and year = ?",
(self.month, self.year))
self.payments = curr.fetchall()
def payment_list():
db = get_db()
cur = db.cursor()
cur.execute("SELECT * FROM payment ORDER BY id;")
payments = cur.fetchall()
return render_template('admin_payments.html', payments=payments)
def recent_events(num=10):
db = get_db()
cur = db.cursor()
cur.execute("""SELECT * FROM events ORDER BY timestamp DESC LIMIT ?""",
(num, ))
return cur.fetchall()
cur.execute("UPDATE email_prefs SET pref = ? WHERE user = ? AND code = ?",
(pref, self.username, code))
else:
cur.execute("INSERT INTO email_prefs (user, code, pref) VALUES (?, ?, ?)",
(self.username, code, pref))
db.commit()
def new_member(username, details):
try:
if Member(username).refresh_profile(): # Does the user exist?
return False
except Exception, e:
if not "No such member" in str(e):
raise e
db = hackhub.get_db()
cur = db.cursor()
cur.execute("""insert into member (username, realname,
nickname, email, address, key, adm) values (?, ?, ?, ?, ?, ?, ?)""",
(username, details['realname'], details['nickname'], details['email'],
details['address'], details['key'], details['adm']))
db.commit()
cur.close()
try:
m = Member(username)
except Exception, e:
if not "No such member" in str(e):
raise e
m.update_profile(details)
return True
def recent_events(num=10):
db = get_db()
cur = db.cursor()
cur.execute("""SELECT * FROM events ORDER BY timestamp DESC LIMIT ?""",
(num,))
return cur.fetchall()
