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