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()