def details(req, userid): # The group from the regex is passed as a positional parameter
"""Create the details page for specific users."""
cur = req.db.cursor()
cur.execute("""
SELECT * FROM users
LEFT OUTER JOIN admin ON email = aEmail
LEFT OUTER JOIN student ON email = sEmail
LEFT OUTER JOIN club ON email = cEmail
WHERE email = %(email)s;
""", {'email': userid})
if cur.rowcount == 0:
raise HTTPError(404)
data = first(result2obj(cur, User))
clubs = None
if data.semail:
cur = req.execute("""SELECT * FROM memberof NATURAL JOIN clubusers
WHERE semail=%(u)s""",
u=userid)
clubs = list(result2obj(cur, User))
events=None
if data.cemail:
eventscur = req.execute("""SELECT * FROM event NATURAL JOIN runBy WHERE cEmail=%(user)s;""",user=userid)
events = result2obj(eventscur,struct)
return template(req, 'user', user=data, clubs=clubs,events=events) # user is a variable that the template references
def details(req, eid, rid):
"""Details page for a specific reservation."""
try:
eid = int(eid)
rid = int(rid)
except:
raise HTTPError(404)
cur = req.execute("SELECT * FROM reservation NATURAL JOIN room WHERE rid=%(r)i", r=rid)
if cur.rowcount == 0:
raise HTTPError(404)
resv = first(result2obj(cur, Reservation))
if resv.eid != eid:
raise HTTPError(404)
cur = req.execute("SELECT * FROM event WHERE eid=%(e)i", e=eid)
if cur.rowcount == 0:
raise HTTPError(404)
event = first(result2obj(cur, Event))
cur = req.execute("""SELECT *
FROM resconflicts NATURAL JOIN reservation NATURAL JOIN room
WHERE against=%(r)i
ORDER BY starttime""", r=rid)
confs = list(result2obj(cur, Reservation))
return template(req, 'reservation', reservation=resv, event=event, conflicts=confs)
def edit(req, eid, rid):
"""Edit a specific reservation."""
try:
eid = int(eid)
rid = int(rid)
except:
raise HTTPError(404)
cur = req.execute("SELECT * FROM reservation NATURAL JOIN room WHERE rid=%(r)i", r=rid)
if cur.rowcount == 0:
raise HTTPError(404)
resv = first(result2obj(cur, Reservation))
if resv.eid != eid:
raise HTTPError(404)
cur = req.execute("SELECT * FROM event WHERE eid=%(e)i", e=eid)
if cur.rowcount == 0:
raise HTTPError(404)
event = first(result2obj(cur, Event))
if not (req.user == resv.semail or req.issuper()):
raise ActionNotAllowed
post = req.post()
if post:
raise NotImplementedError
return template(req, 'reservation-edit', event=event, reservation=resv)
def create(req, eid):
"""Create a new reservation."""
try:
eid = int(eid)
except:
raise HTTPError(404)
if not (req.isstudent() or req.issuper()):
raise ActionNotAllowed
cur = req.execute("SELECT * FROM event WHERE eid=%(id)i", id=eid)
if cur.rowcount == 0:
raise HTTPError(404)
event = first(result2obj(cur, Event))
cur = req.execute(
"SELECT * FROM runBy NATURAL JOIN clubusers WHERE eid=%(id)i ORDER BY name",
id=eid)
clubs = list(result2obj(cur, User))
if not (req.inclub(c.cemail for c in clubs) or req.issuper()):
raise ActionNotAllowed
post = req.post()
if post:
if req.issuper():
semail = post['semail']
else:
semail = req.user
building = post['building']
roomnum = post['roomnum']
#FIXME: Parse datetimes
st = post['starttime']
et = post['endtime']
cur = req.execute("""INSERT INTO reservation
(eid, semail, timebooked, starttime, endtime, roomnum, building)
VALUES
(%(e)i, %(s)s, NOW(), %(st)s, %(et)s, %(rn)s, %(build)s)
RETURNING rid""", e=eid, s=semail, st=st, et=et, rn=roomnum,
build=building)
assert cur.rowcount
rid = first(itercursor(cur))[0]
req.status(303)
req.header('Location', req.fullurl('/event/%i/reservation/%i' % (eid, rid)))
query = req.query()
building = query.get('building', None)
roomnum = query.get('roomnum', None)
st = query.get('starttime', None)
et = query.get('endtime', None)
return template(req, 'reservation-create', event=event,
building=building, roomnum=roomnum, starttime=st, endtime=et)
def comment(req, eid):
"""Handle comment functionality such as displaying what comments reply to each other correctly."""
try:
eid = int(eid)
except:
raise HTTPError(404)
get = req.query()
post = req.post()
cur = req.db.cursor()
cur.execute("SELECT * FROM event WHERE eid=%(id)i", {'id': eid})
if cur.rowcount == 0:
raise HTTPError(404)
event = first(result2obj(cur, Event))
#manage how comments link to each other
if post:
replyto=None
if 'replyto' in post:
replyto = int(post['replyto'])
txt = post['txt'].replace('\r\n', '\n').replace('\r', '\n')
if replyto is None:
cur = req.execute("""
INSERT INTO comments (eid, madeat, email, txt)
VALUES (%(eid)i, NOW(), %(user)s, %(txt)s)""",
eid=eid, user=req.user, txt=txt)
else:
cur = req.execute("""
INSERT INTO comments (eid, madeat, email, txt, parent)
VALUES (%(eid)i, NOW(), %(user)s, %(txt)s, %(replyto)i)""",
eid=eid, user=req.user, txt=txt, replyto=replyto)
assert cur.rowcount
cid = cur.lastrowid
req.status(303)
req.header('Location', req.fullurl('/event/%i#comment%i' % (eid, cid)))
return
else:
quoted = ''
parent = None
if get is not None and 'replyto' in get:
try:
r2 = int(get['replyto'])
except: pass
else:
cur.execute(
"SELECT * FROM comments NATURAL JOIN users WHERE cid=%(id)i",
id=r2)
parent = first(result2obj(cur, Event))
quoted = '\n'.join('> '+l for l in parent.txt.split('\n')) + '\n'
return template(req, 'event-comment', event=event, parent=parent, quoted=quoted)
def approve(req, eid, rid):
"""Approve an event with conflict checking."""
try:
eid = int(eid)
rid = int(rid)
except:
raise HTTPError(404)
if not req.isadmin():
raise ActionNotAllowed
cur = req.execute("SELECT * FROM reservation NATURAL JOIN room WHERE rid=%(r)i", r=rid)
if cur.rowcount == 0:
raise HTTPError(404)
resv = first(result2obj(cur, Reservation))
if resv.eid != eid:
raise HTTPError(404)
cur = req.execute("SELECT * FROM event WHERE eid=%(e)i", e=eid)
if cur.rowcount == 0:
raise HTTPError(404)
event = first(result2obj(cur, Event))
cur = req.execute("""SELECT *
FROM resconflicts NATURAL JOIN reservation NATURAL JOIN room
WHERE against=%(r)i
ORDER BY starttime""", r=rid)
confs = list(result2obj(cur, Reservation))
post = req.post()
if post and not resv.aemail:
# in 2.5, we could just use any()/all()
canapprove = True
for c in confs:
if c.aemail:
canapprove = False
break
if 'yes' in post and canapprove:
cur = req.execute(
"UPDATE reservation SET aemail=%(a)s WHERE rid=%(r)i",
a=req.user, r=rid)
assert cur.rowcount
req.status(303)
req.header('Location', req.fullurl('/event/%i/reservation/%i'%(eid,rid)))
return
return template(req, 'reservation-approve', event=event, reservation=resv,
conflicts=confs)
def index(req):
"""Creates a link to all events, ordered by name."""
cur = req.db.cursor()
cur.execute("SELECT * FROM event ORDER BY name;")
data = list(result2obj(cur, Event))
return template(req, 'event-list', events=data)
def edit(req, building, room):
"""Edit a room page."""
# Handle occupancy, equipment
if not req.isadmin():
raise ActionNotAllowed
cur = req.execute(
"SELECT * FROM room WHERE building=%(b)s AND roomnum=%(r)s",
b=building,
r=room)
if not cur.rowcount:
raise HTTPError(404)
rdata = first(result2obj(cur, Room))
cur = req.execute(
"SELECT equipname FROM isin WHERE building=%(b)s AND roomnum=%(r)s",
b=building,
r=room)
equipment = [r[0] for r in itercursor(cur)]
post = req.post()
if post:
raise NotImplementedError
return template(req, 'room-edit', room=rdata, equipment=equipment)
def index(req):
"""Create the front page for room browsing."""
cur = req.db.cursor()
cur.execute("""SELECT * FROM room ORDER BY building, roomnum;""")
data = result2obj(cur, Room)
return template(req, 'room-list', rooms=data)
def index(req):
"""Create the front page for room browsing."""
cur = req.db.cursor()
cur.execute("""SELECT * FROM room ORDER BY building, roomnum;""")
data = result2obj(cur, Room)
return template(req, 'room-list', rooms=data)
def index(req):
"""Creates a link to all events, ordered by name."""
cur = req.db.cursor()
cur.execute("SELECT * FROM event ORDER BY name;")
data = list(result2obj(cur, Event))
return template(req, 'event-list', events=data)
def index(req):
"""Create the index page for users."""
cur = req.db.cursor()
cur.execute("""SELECT * FROM users ORDER BY name;""")
data = list(result2obj(cur, User))
return template(req, 'user-list', users=data)
def delete(req, eid, rid):
"""Delete a reservation from the database."""
try:
eid = int(eid)
rid = int(rid)
except:
raise HTTPError(404)
if not req.isadmin():
raise ActionNotAllowed
cur = req.execute("SELECT * FROM reservation NATURAL JOIN room WHERE rid=%(r)i", r=rid)
if cur.rowcount == 0:
raise HTTPError(404)
resv = first(result2obj(cur, Reservation))
if resv.eid != eid:
raise HTTPError(404)
cur = req.execute("SELECT * FROM event WHERE eid=%(e)i", e=eid)
if cur.rowcount == 0:
raise HTTPError(404)
event = first(result2obj(cur, Event))
cur = req.execute(
"SELECT COUNT(*) FROM runby WHERE eid=%(e)i AND cemail=%(c)s",
e=eid, c=req.user)
isclub = first(itercursor(cur))[0]
# running groups, booking user, admin
if not (isclub or req.user == resv.semail or req.isadmin()):
raise ActionNotAllowed
post = req.post()
if post:
if 'yes' in post:
cur = req.execute(
"DELETE reservation WHERE rid=%(r)i", r=rid)
assert cur.rowcount
req.status(303)
req.header('Location', req.fullurl('/event/%i'%eid))
else:
req.status(303)
req.header('Location', req.fullurl('/event/%i/reservation/%i'%(eid,rid)))
return
return template(req, 'reservation-delete', event=event, reservation=resv)
def details(req, eid):
"""Populates all the detail pages for specific events."""
from reservations import Reservation
try:
eid = int(eid)
except:
raise HTTPError(404)
cur = req.execute("SELECT * FROM event WHERE eid=%(id)i", id=eid)
if cur.rowcount == 0:
raise HTTPError(404)
event = first(result2obj(cur, Event))
#who runs
cur = req.execute(
"SELECT * FROM runBy NATURAL JOIN clubusers WHERE eid=%(id)i ORDER BY name",
id=eid)
clubs = list(result2obj(cur, User))
#find reservations, mark conflicts
cur = req.execute("""
SELECT * FROM reservation NATURAL LEFT OUTER JOIN (
SELECT COUNT(against) AS conflicts, rid
FROM resconflicts NATURAL JOIN reservation
WHERE EID=%(event)i
GROUP BY rid
) AS conflicting NATURAL LEFT OUTER JOIN room
WHERE reservation.eid = %(event)i
ORDER BY starttime""",
event=eid)
reservations = list(result2obj(cur, Reservation))
#comments ordered by time made
cur = req.execute(
"SELECT * FROM comments NATURAL JOIN users WHERE EID=%(id)i ORDER BY madeat",
id=eid)
comments = list(result2obj(cur, Comment))
#equipment present ordered by name
cur = req.execute(
"SELECT equipname FROM uses WHERE EID=%(id)i ORDER BY equipname",
id=eid)
equipment = [r[0] for r in itercursor(cur)]
return template(req,
'event',
event=event,
clubs=clubs,
equipment=equipment,
comments=comments,
reservations=reservations)
def adduser(req, userid):
"""Add a user to a club."""
cur = req.execute("SELECT * FROM clubusers WHERE email = %(email)s", email=userid)
user = first(result2obj(cur, User))
if not user.cemail:
raise HTTPError(404)
if not (req.user == userid or req.issuper()):
raise ActionNotAllowed
raise NotImplementedError
def building_index(req, building):
"""Create the index page of for buildings."""
cur = req.db.cursor()
cur.execute("""
SELECT * FROM room
WHERE building=%(building)s
ORDER BY roomnum
""", {'building': building})
data = result2obj(cur, Room)
return template(req, 'room-list-building', rooms=data, building=building)
def details(req, eid):
"""Populates all the detail pages for specific events."""
from reservations import Reservation
try:
eid = int(eid)
except:
raise HTTPError(404)
cur = req.execute("SELECT * FROM event WHERE eid=%(id)i", id=eid)
if cur.rowcount == 0:
raise HTTPError(404)
event = first(result2obj(cur, Event))
#who runs
cur = req.execute(
"SELECT * FROM runBy NATURAL JOIN clubusers WHERE eid=%(id)i ORDER BY name",
id=eid)
clubs = list(result2obj(cur, User))
#find reservations, mark conflicts
cur = req.execute("""
SELECT * FROM reservation NATURAL LEFT OUTER JOIN (
SELECT COUNT(against) AS conflicts, rid
FROM resconflicts NATURAL JOIN reservation
WHERE EID=%(event)i
GROUP BY rid
) AS conflicting NATURAL LEFT OUTER JOIN room
WHERE reservation.eid = %(event)i
ORDER BY starttime""", event=eid)
reservations = list(result2obj(cur, Reservation))
#comments ordered by time made
cur = req.execute(
"SELECT * FROM comments NATURAL JOIN users WHERE EID=%(id)i ORDER BY madeat",
id=eid)
comments = list(result2obj(cur, Comment))
#equipment present ordered by name
cur = req.execute(
"SELECT equipname FROM uses WHERE EID=%(id)i ORDER BY equipname",
id=eid)
equipment = [r[0] for r in itercursor(cur)]
return template(req, 'event',
event=event, clubs=clubs, equipment=equipment, comments=comments,
reservations=reservations)
def building_index(req, building):
"""Create the index page of for buildings."""
cur = req.db.cursor()
cur.execute(
"""
SELECT * FROM room
WHERE building=%(building)s
ORDER BY roomnum
""", {'building': building})
data = result2obj(cur, Room)
return template(req, 'room-list-building', rooms=data, building=building)
def index(req):
"""Create the index page for use statistics."""
#FIXME: Join against room so we can use Room
#most used rooms
roomscur = req.execute("""SELECT COUNT(*) AS c, building, roomnum
FROM room NATURAL JOIN reservation
GROUP BY building, roomnum
ORDER BY COUNT(*) DESC
LIMIT 10;""")
usedrooms = result2obj(roomscur, struct)
# FIXME: Join against users so we can use User
# students who run events
studentcur = req.execute("""SELECT COUNT(*) AS c, semail
FROM reservation NATURAL JOIN student
GROUP BY semail
ORDER BY COUNT(*) DESC
LIMIT 10;""")
studentsevents = result2obj(studentcur, struct)
#majors which run events
majorcur = req.execute("""
SELECT major, COUNT(rid) AS count
FROM
(
(SELECT rid, major1 AS major FROM reservation NATURAL JOIN student)
UNION
(SELECT rid, major2 AS major FROM reservation NATURAL JOIN student
WHERE major2 IS NOT NULL)
) AS counts
GROUP BY major
ORDER BY count DESC
LIMIT 10;""")
majorevents = result2obj(majorcur, struct)
return template(req,
'stats',
usedrooms=usedrooms,
studentsevents=studentsevents,
majorevents=majorevents)
def index(req):
"""Create the index page for use statistics."""
#FIXME: Join against room so we can use Room
#most used rooms
roomscur = req.execute("""SELECT COUNT(*) AS c, building, roomnum
FROM room NATURAL JOIN reservation
GROUP BY building, roomnum
ORDER BY COUNT(*) DESC
LIMIT 10;""")
usedrooms = result2obj(roomscur,struct)
# FIXME: Join against users so we can use User
# students who run events
studentcur =req.execute("""SELECT COUNT(*) AS c, semail
FROM reservation NATURAL JOIN student
GROUP BY semail
ORDER BY COUNT(*) DESC
LIMIT 10;""")
studentsevents = result2obj(studentcur,struct)
#majors which run events
majorcur=req.execute("""
SELECT major, COUNT(rid) AS count
FROM
(
(SELECT rid, major1 AS major FROM reservation NATURAL JOIN student)
UNION
(SELECT rid, major2 AS major FROM reservation NATURAL JOIN student
WHERE major2 IS NOT NULL)
) AS counts
GROUP BY major
ORDER BY count DESC
LIMIT 10;""")
majorevents = result2obj(majorcur,struct)
return template(req, 'stats',
usedrooms=usedrooms, studentsevents=studentsevents,
majorevents=majorevents)
def index(req, eid):
"""Format the reservation page."""
try:
eid = int(eid)
except:
raise HTTPError(404)
cur = req.execute("SELECT * FROM event WHERE eid=%(id)i", id=eid)
if cur.rowcount == 0:
raise HTTPError(404)
event = first(result2obj(cur, Event))
#find conflicts
cur = req.execute("""
SELECT * FROM reservation NATURAL LEFT OUTER JOIN (
SELECT COUNT(against) AS conflicts, rid
FROM resconflicts NATURAL JOIN reservation
WHERE EID=%(event)i
GROUP BY rid
) AS conflicting NATURAL LEFT OUTER JOIN room
WHERE reservation.eid = %(event)i
ORDER BY starttime""", event=eid)
reservations = list(result2obj(cur, Reservation))
return template(req, 'reservation-list', event=event, reservations=reservations)
def details(req, building, room):
"""Create the page for a specific room."""
cur = req.db.cursor()
cur.execute("""
SELECT * FROM room
WHERE roomnum=%(room)s AND building=%(building)s
""", {'room': room, 'building': building})
roomdata = first(result2obj(cur, Room))
cur.execute("""
SELECT equipname FROM isIn
WHERE roomnum=%(room)s AND building=%(building)s
ORDER BY equipname
""", {'room': room, 'building': building})
equipdata = [r[0] for r in itercursor(cur)]
return template(req, 'room', room=roomdata, equipment=equipdata)
def edit(req, building, room):
"""Edit a room page."""
# Handle occupancy, equipment
if not req.isadmin():
raise ActionNotAllowed
cur = req.execute("SELECT * FROM room WHERE building=%(b)s AND roomnum=%(r)s",
b=building, r=room)
if not cur.rowcount:
raise HTTPError(404)
rdata = first(result2obj(cur, Room))
cur = req.execute("SELECT equipname FROM isin WHERE building=%(b)s AND roomnum=%(r)s",
b=building, r=room)
equipment = [r[0] for r in itercursor(cur)]
post = req.post()
if post:
raise NotImplementedError
return template(req, 'room-edit', room=rdata, equipment=equipment)
def details(req, building, room):
"""Create the page for a specific room."""
cur = req.db.cursor()
cur.execute(
"""
SELECT * FROM room
WHERE roomnum=%(room)s AND building=%(building)s
""", {
'room': room,
'building': building
})
roomdata = first(result2obj(cur, Room))
cur.execute(
"""
SELECT equipname FROM isIn
WHERE roomnum=%(room)s AND building=%(building)s
ORDER BY equipname
""", {
'room': room,
'building': building
})
equipdata = [r[0] for r in itercursor(cur)]
return template(req, 'room', room=roomdata, equipment=equipdata)
def edit(req, eid):
"""Change information on the event."""
try:
eid = int(eid)
except:
raise HTTPError(404)
cur = req.execute("SELECT * FROM event WHERE eid=%(id)i", id=eid)
if cur.rowcount == 0:
raise HTTPError(404)
event = first(result2obj(cur, Event))
cur = req.execute(
"SELECT * FROM runBy NATURAL JOIN clubusers WHERE eid=%(id)i ORDER BY name",
id=eid)
clubs = list(result2obj(cur, User))
cur = req.execute(
"SELECT equipname FROM uses WHERE EID=%(id)i ORDER BY equipname",
id=eid)
equipment = [r[0] for r in itercursor(cur)]
if not (req.inclub(c.email for c in clubs) or req.issuper()):
raise ActionNotAllowed
post = req.post()
if post:
if 'basicinfo' in post:
size = None
if post['expectedsize']:
size = int(post['expectedsize'])
req.execute("""UPDATE event
SET name=%(name)s, description=%(desc)s, expectedsize=%(size)s
WHERE eid=%(eid)i""",
name=post['name'], desc=post['description'], size=size, eid=eid)
elif 'club-delete' in post and len(clubs) > 1:
# Broken?
if req.inclub(post['cemail']) or req.issuper():
req.execute("DELETE FROM runby WHERE eid=%(e)i AND cemail=%(c)s",
e=eid, c=post['cemail'])
elif 'club-add' in post:
if (req.isstudent() and req.inclub([post['cemail']])) \
or req.isclub() or req.issuper():
req.execute("INSERT INTO runby (eid, cemail) VALUES (%(e)i, %(c)s)",
e=eid, c=post['cemail'])
elif 'equip-delete' in post:
req.execute("DELETE FROM uses WHERE eid=%(e)i AND equipname=%(eq)s",
e=eid, eq=post['equipname'])
elif 'equip-add' in post:
req.execute("""INSERT INTO uses (eid, equipname) VALUES (%(e)i, %(eq)s)""",
e=eid, eq=post['equipname'])
req.status(303)
req.header('Location', req.fullurl('/event/%i/edit' % (eid)))
else:
userclubs = None
if req.isstudent():
cur = req.execute("""
SELECT * FROM memberof NATURAL JOIN clubusers WHERE semail=%(email)s ORDER BY name""",
email=req.user)
userclubs = list(result2obj(cur, User))
return template(req, 'event-edit',
event=event, clubs=clubs, equipment=equipment, userclubs=userclubs)
def index(req):
"""Creates an index page for reservations."""
cur = req.execute("""SELECT reservation.*, event.name FROM reservation NATURAL JOIN event WHERE aEmail IS NULL AND startTime > now() ORDER BY startTime;""")
reservations = list(result2obj(cur, Reservation))
return template(req, 'unapproved-reservations', reservations=reservations)
def create(req):
"""Handles the form for creating a new event and adding it to the database."""
if not (req.isstudent() or req.isclub() or req.issuper()):
raise ActionNotAllowed
clubs = None
if req.isstudent():
cur = req.execute(
"SELECT * FROM memberof NATURAL JOIN clubusers WHERE semail=%(u)s",
u=req.user)
clubs = list(result2obj(cur, User))
elif req.issuper():
cur = req.execute("SELECT * FROM clubusers")
clubs = list(result2obj(cur, User))
post = req.post()
if post:
name = post['name']
desc = post['description']
size = None
if post['expectedsize']:
size = int(post['expectedsize'])
if req.isclub():
clubs = [req.user]
else:
clubs = [v for n,v in req.postall() if n == 'cemail']
equipment = post['equipment'].split()
if len(clubs) and name and desc:
cur = req.db.cursor();
cur.execute("BEGIN")
try:
cur.execute("""INSERT INTO event (name, description, expectedsize)
VALUES (%(name)s, %(desc)s, %(size)s)
RETURNING eid""",
{'name': name, 'desc': desc, 'size': size})
assert cur.rowcount
eid = first(itercursor(cur))[0]
for c in clubs:
cur.execute(
"INSERT INTO runby (eid, cemail) VALUES (%(e)i, %(c)s)",
{'e': eid, 'c': c})
assert cur.rowcount
for e in equipment:
cur.execute(
"INSERT INTO uses (eid, equipname) VALUES (%(e)i, %(q)s)",
{'e': eid, 'q': e})
assert cur.rowcount
finally:
if sys.exc_info()[0] is None:
cur.execute("COMMIT")
else:
cur.execute("ROLLBACK")
req.status(303)
req.header("Location", req.fullurl("/event/%i" % eid))
return
return template(req, 'event-create', clubs=clubs)
def edit(req, eid):
"""Change information on the event."""
try:
eid = int(eid)
except:
raise HTTPError(404)
cur = req.execute("SELECT * FROM event WHERE eid=%(id)i", id=eid)
if cur.rowcount == 0:
raise HTTPError(404)
event = first(result2obj(cur, Event))
cur = req.execute(
"SELECT * FROM runBy NATURAL JOIN clubusers WHERE eid=%(id)i ORDER BY name",
id=eid)
clubs = list(result2obj(cur, User))
cur = req.execute(
"SELECT equipname FROM uses WHERE EID=%(id)i ORDER BY equipname",
id=eid)
equipment = [r[0] for r in itercursor(cur)]
if not (req.inclub(c.email for c in clubs) or req.issuper()):
raise ActionNotAllowed
post = req.post()
if post:
if 'basicinfo' in post:
size = None
if post['expectedsize']:
size = int(post['expectedsize'])
req.execute("""UPDATE event
SET name=%(name)s, description=%(desc)s, expectedsize=%(size)s
WHERE eid=%(eid)i""",
name=post['name'],
desc=post['description'],
size=size,
eid=eid)
elif 'club-delete' in post and len(clubs) > 1:
# Broken?
if req.inclub(post['cemail']) or req.issuper():
req.execute(
"DELETE FROM runby WHERE eid=%(e)i AND cemail=%(c)s",
e=eid,
c=post['cemail'])
elif 'club-add' in post:
if (req.isstudent() and req.inclub([post['cemail']])) \
or req.isclub() or req.issuper():
req.execute(
"INSERT INTO runby (eid, cemail) VALUES (%(e)i, %(c)s)",
e=eid,
c=post['cemail'])
elif 'equip-delete' in post:
req.execute(
"DELETE FROM uses WHERE eid=%(e)i AND equipname=%(eq)s",
e=eid,
eq=post['equipname'])
elif 'equip-add' in post:
req.execute(
"""INSERT INTO uses (eid, equipname) VALUES (%(e)i, %(eq)s)""",
e=eid,
eq=post['equipname'])
req.status(303)
req.header('Location', req.fullurl('/event/%i/edit' % (eid)))
else:
userclubs = None
if req.isstudent():
cur = req.execute("""
SELECT * FROM memberof NATURAL JOIN clubusers WHERE semail=%(email)s ORDER BY name""",
email=req.user)
userclubs = list(result2obj(cur, User))
return template(req,
'event-edit',
event=event,
clubs=clubs,
equipment=equipment,
userclubs=userclubs)
def create(req):
"""Handles the form for creating a new event and adding it to the database."""
if not (req.isstudent() or req.isclub() or req.issuper()):
raise ActionNotAllowed
clubs = None
if req.isstudent():
cur = req.execute(
"SELECT * FROM memberof NATURAL JOIN clubusers WHERE semail=%(u)s",
u=req.user)
clubs = list(result2obj(cur, User))
elif req.issuper():
cur = req.execute("SELECT * FROM clubusers")
clubs = list(result2obj(cur, User))
post = req.post()
if post:
name = post['name']
desc = post['description']
size = None
if post['expectedsize']:
size = int(post['expectedsize'])
if req.isclub():
clubs = [req.user]
else:
clubs = [v for n, v in req.postall() if n == 'cemail']
equipment = post['equipment'].split()
if len(clubs) and name and desc:
cur = req.db.cursor()
cur.execute("BEGIN")
try:
cur.execute(
"""INSERT INTO event (name, description, expectedsize)
VALUES (%(name)s, %(desc)s, %(size)s)
RETURNING eid""", {
'name': name,
'desc': desc,
'size': size
})
assert cur.rowcount
eid = first(itercursor(cur))[0]
for c in clubs:
cur.execute(
"INSERT INTO runby (eid, cemail) VALUES (%(e)i, %(c)s)",
{
'e': eid,
'c': c
})
assert cur.rowcount
for e in equipment:
cur.execute(
"INSERT INTO uses (eid, equipname) VALUES (%(e)i, %(q)s)",
{
'e': eid,
'q': e
})
assert cur.rowcount
finally:
if sys.exc_info()[0] is None:
cur.execute("COMMIT")
else:
cur.execute("ROLLBACK")
req.status(303)
req.header("Location", req.fullurl("/event/%i" % eid))
return
return template(req, 'event-create', clubs=clubs)
def comment(req, eid):
"""Handle comment functionality such as displaying what comments reply to each other correctly."""
try:
eid = int(eid)
except:
raise HTTPError(404)
get = req.query()
post = req.post()
cur = req.db.cursor()
cur.execute("SELECT * FROM event WHERE eid=%(id)i", {'id': eid})
if cur.rowcount == 0:
raise HTTPError(404)
event = first(result2obj(cur, Event))
#manage how comments link to each other
if post:
replyto = None
if 'replyto' in post:
replyto = int(post['replyto'])
txt = post['txt'].replace('\r\n', '\n').replace('\r', '\n')
if replyto is None:
cur = req.execute("""
INSERT INTO comments (eid, madeat, email, txt)
VALUES (%(eid)i, NOW(), %(user)s, %(txt)s)""",
eid=eid,
user=req.user,
txt=txt)
else:
cur = req.execute("""
INSERT INTO comments (eid, madeat, email, txt, parent)
VALUES (%(eid)i, NOW(), %(user)s, %(txt)s, %(replyto)i)""",
eid=eid,
user=req.user,
txt=txt,
replyto=replyto)
assert cur.rowcount
cid = cur.lastrowid
req.status(303)
req.header('Location', req.fullurl('/event/%i#comment%i' % (eid, cid)))
return
else:
quoted = ''
parent = None
if get is not None and 'replyto' in get:
try:
r2 = int(get['replyto'])
except:
pass
else:
cur.execute(
"SELECT * FROM comments NATURAL JOIN users WHERE cid=%(id)i",
id=r2)
parent = first(result2obj(cur, Event))
quoted = '\n'.join('> ' + l
for l in parent.txt.split('\n')) + '\n'
return template(req,
'event-comment',
event=event,
parent=parent,
quoted=quoted)
def user_edit(req, user):
"""Edit a user in the database."""
cur = req.db.cursor()
# Handles:
# * user/student/admin/club info
# * changing the type of user
# * making admins super
# * Adding club adminship
cur.execute("""
SELECT * FROM users
LEFT OUTER JOIN admin ON email = aEmail
LEFT OUTER JOIN student ON email = sEmail
LEFT OUTER JOIN club ON email = cEmail
WHERE email = %(email)s;
""", {'email': user})
userdata = first(result2obj(cur, User))
if cur.rowcount == 0:
raise HTTPError(404)
post = req.post()
clubs = None
if userdata.semail:
cur = req.execute("""SELECT * FROM memberof NATURAL JOIN clubusers
WHERE semail=%(u)s""",
u=user)
clubs = list(result2obj(cur, User))
if post is not None:
# Save
if 'club-remove' in post and userdata.semail: #TODO: Add permissions checking
cur = req.execute("DELETE FROM memberOf WHERE semail=%(email)s AND cemail=%(cemail)s",email=user,cemail=post['cemail'])
elif 'club-add' in post and userdata.semail:
cur = req.execute("INSERT INTO memberOf VALUES (%(semail)s, %(cemail)s)",semail=user,cemail=post['cemail'])
elif 'mkadmin' in post and req.issuper() and not userdata.aemail and not userdata.cemail:
cur = req.execute("INSERT INTO admin (aemail) VALUES (%(email)s)", email=user)
assert cur.rowcount
elif 'mkstudent' in post and not userdata.semail and not userdata.cemail:
cur = req.execute("INSERT INTO student (semail) VALUES (%(email)s)", email=user)
assert cur.rowcount
elif 'mkclub' in post and req.issuper() and not userdata.semail and not userdata.aemail and not userdata.cemail:
cur = req.execute("INSERT INTO club (cemail) VALUES (%(email)s)", email=user)
assert cur.rowcount
else:
cur.execute("BEGIN");
try:
password = None
print repr(post)
if post['oldpassword'] or (req.issuper() and post['password1']):
if post['password1'] != post['password2']:
return template(req, 'user-edit', user=userdata, msg='Mismatched passwords')
cur.execute("""
UPDATE users
SET password=md5(%(password)s)
WHERE email=%(email)s AND password=md5(%(old)s);
""",
{'email': user, 'old': post['oldpassword'], 'password': post['password1']}
)
assert cur.rowcount
cur.execute("""
UPDATE users
SET name=%(name)s
WHERE email=%(email)s;
""",
{'name': post['name'], 'email': user}
)
assert cur.rowcount
if userdata.aemail and 'aemail' in post:
title = None
if post['title']:
title = post['title']
if request.issuper():
cur.execute("""
UPDATE admin
SET title=%(title)s, super=%(super)s
WHERE aemail=%(email)s;
""",
{'title': title, 'super': 'super' in post, 'email': user}
)
else:
cur.execute("""
UPDATE admin
SET title=%(title)s
WHERE aemail=%(email)s;
""",
{'title': title, 'email': user}
)
assert cur.rowcount
if userdata.semail and 'semail' in post:
year = major1 = major2 = None
if post['year']: year = int(post['year'])
if post['major1']: major1 = post['major1']
if post['major2']: major2 = post['major2']
if major2 and not major1:
major1, major2 = major2, None
cur.execute("""
UPDATE student
SET year=%(year)i, major1=%(major1)s, major2=%(major2)s
WHERE semail=%(email)s;
""",
{'year': year, 'major1': major1, 'major2': major2, 'email': user}
)
assert cur.rowcount
if userdata.cemail and 'cemail' in post:
cls = desc = None
if post['class']: cls = int(post['class'])
if post['description']: desc = post['description']
cur.execute("""
UPDATE club
SET class=%(cls)i, description=%(desc)s
WHERE cemail=%(email)s;
""",
{'cls': cls, 'desc': desc, 'email': user}
)
assert cur.rowcount
finally:
if sys.exc_info()[0] is None:
cur.execute("COMMIT")
else:
cur.execute("ROLLBACK")
req.status(303)
req.header('Location', req.fullurl('/user/%s/edit' % user))
return
cur.execute("""
SELECT * FROM users
LEFT OUTER JOIN admin ON email = aEmail
LEFT OUTER JOIN student ON email = sEmail
LEFT OUTER JOIN club ON email = cEmail
WHERE email = %(email)s;
""", {'email': user})
userdata = first(result2obj(cur, User))
return template(req, 'user-edit', user=userdata, clubs=clubs)