def get(self, other_email):
if other_email is None:
return 'Missing other_email', 400
# lookup userid for requested user
c = db.cursor(cursor_factory=RealDictCursor)
c.execute("SELECT id FROM users WHERE cppemail = %s", (other_email + '@cpp.edu',))
# check if we got a result
row = c.fetchone()
if row is None:
return 'User does not exist', 404
# get id
other_id = row['id']
# lookup message history
c.execute("SELECT * FROM messages WHERE receive_userid = %s and send_userid = %s or receive_userid = %s and send_userid = %s ORDER BY timestamp ASC", (other_id, request.id, request.id, other_id))
messages = c.fetchall()
for m in messages:
m['outgoing'] = m['send_userid'] == request.id
# mark as read in db
c.execute("UPDATE messages SET read = true WHERE receive_userid = %s and send_userid = %s", (request.id, other_id))
db.commit()
return jsonify(results=messages)
def post(self):
altemail = get_form('altemail')
addressline1 = get_form('addressline1')
addressline2 = get_form('addressline2') # not required
city = get_form('city')
zipcode = get_form('zip')
schedule = get_form('schedule')
drivingpref = get_form('drivingpref')
maxdist = get_form('maxdist')
# make sure required fields are not missing
if addressline1 is None or city is None or zipcode is None or schedule is None or drivingpref is None or maxdist is None:
return 'Missing fields', 400
# make sure the required fields are not empty
if len(addressline1) == 0 or len(city) == 0 or len(zipcode) == 0 or len(schedule) == 0 or len(drivingpref) == 0 or len(maxdist) == 0:
return 'Empty fields', 400
# write to db
c = db.cursor(cursor_factory=RealDictCursor)
c.execute("UPDATE users SET altemail = %s, addressline1 = %s, city = %s, zip = %s, drivingpref = %s, maxdist = %s, profilecomplete = true WHERE id = %s", (altemail, addressline1, city, zipcode, drivingpref, maxdist, request.id))
schedule = json.loads(schedule)
# write schedule to db
for i in range(len(schedule)):
day = schedule[i]
c.execute("UPDATE schedule SET arrive = %s, depart = %s WHERE userid = %s AND dayofweek = %s", (day['arrive'], day['depart'], request.id, i,))
db.commit()
return "OK", 200
def add(cls,
topic,
winner_olid,
candidate_olids,
username,
description=""):
"""
params:
:topic (api.Topic):
:winner_golid: ANY OL ID (e.g. OL123M or OL234W)
"""
topic = Topic.upsert(topic)
winner = Book.upsert_by_olid(Book.clean_olid(winner_olid))
candidates = []
candidates.append(winner)
for olid in candidate_olids:
olid = Book.clean_olid(olid) # xxx
candidates.append(Book.upsert_by_olid(olid))
r = cls(topic_id=topic.id,
book_id=winner.id,
description=description,
username=username,
candidates=candidates).create()
db.commit()
return r
def post(self):
# if no file
if 'picture' not in request.files:
return 'Missing file', 400
# get filename
picture = request.files['picture']
# check if the file is one of the allowed filetypes / extensions or empty
if picture is None or not allowed_file(picture.filename):
return 'Bad file', 400
image = Image.open(picture.stream)
croppedpicture = resizeimage.resize_cover(image, [256, 256])
imgByteArr = io.BytesIO()
croppedpicture.save(imgByteArr, format='JPEG')
imgByteArr = bytearray(imgByteArr.getvalue())
# write to db
c = db.cursor(cursor_factory=RealDictCursor)
c.execute("UPDATE users SET picture = %s WHERE id = %s", (imgByteArr, request.id))
db.commit()
return 'OK', 200
def put(self, current_user, todo_id):
todo = Todo.query.filter_by(id=todo_id,
user_id=current_user.public_id).first()
if not todo:
return {"message": "Todo item not found."}
todo.complete = True
db.commit()
return {"message": f"Todo item: '{todo.title}' completed"}
def post(self):
# make sure user is not reviewing themself
reviewee_email = get_form('email')
if reviewee_email == request.email:
return 'You can not review yourself', 400
# lookup reviewer id
c = db.cursor(cursor_factory=RealDictCursor)
c.execute("SELECT id FROM users WHERE cppemail = %s",
(request.email, ))
# check if we got a result
row = c.fetchone()
if row is None:
return 'You do not exist', 404
# get id
reviewer_id = dict(row)['id']
# lookup reviewee id
c.execute("SELECT id FROM users WHERE cppemail = %s",
(reviewee_email, ))
# check if we got a result
row = c.fetchone()
if row is None:
return 'User does not exist', 404
# get id
reviewee_id = dict(row)['id']
# make sure user has not already reviewed this person
c.execute(
"SELECT id FROM reviews WHERE reviewee_userid = %s and reviewer_userid = %s",
(reviewee_id, reviewer_id))
# check if we got a result
row = c.fetchone()
if row is not None:
return 'You can not review more than once', 400
# get # of stars
stars_unparsed = get_form('stars')
if stars_unparsed is None:
return 'Missing field: stars', 400
stars = int(stars_unparsed)
if stars < 1 or stars > 5:
return 'Stars out of range', 400
content = get_form('content')
if content is None or len(content) == 0:
return 'Missing field: content', 400
# insert new record into reviews table
c.execute(
"INSERT INTO reviews (reviewer_userid, reviewee_userid, stars, content) VALUES (%s, %s, %s, %s)",
(reviewer_id, reviewee_id, stars, content))
db.commit()
return 'OK', 201
def create(username):
user_id = users.get_user_id(username)
expires = datetime.datetime.now() + datetime.timedelta(hours=2)
cursor = db.cursor()
cursor.execute('INSERT INTO Sessions (user_id, expires) VALUES (%s, %s);',
(user_id, expires))
db.commit()
cursor.execute('SELECT LAST_INSERT_ID();')
session_id = cursor.fetchone()[0]
cursor.close()
return session_id
def post(self):
to_userid = get_form('to_userid')
text = get_form('message')
# make sure required fields are not empyty
if to_userid is None or text is None:
return 'Missing fields', 400
# write to db
c = db.cursor(cursor_factory=RealDictCursor)
c.execute("INSERT INTO messages (send_userid, receive_userid, message, timestamp) VALUES (%s, %s, %s, CURRENT_TIMESTAMP)", (request.id, to_userid, text))
db.commit()
return 'OK', 200
def insert_draws(draws: list) -> bool:
sql = "INSERT INTO draws (draw_id, numbers, stars, date, prize, has_winner) VALUES "
vars = []
for i, draw in enumerate(draws):
if i == 0:
sql += "(%s, %s, %s, %s, %s, %s)"
else:
sql += ", (%s, %s, %s, %s, %s, %s)"
vars.extend(draw)
db.getCursor().execute(sql, vars)
db.commit()
return True
def main():
if len(sys.argv) == 1:
csvdir = os.path.join(os.path.dirname(os.path.dirname(
os.path.dirname(os.path.realpath(__file__)))), '../', 'assets')
else:
csvdir = sys.argv[1]
db.session.query(api.Meter).delete()
db.session.query(api.Garage).delete()
db.session.commit()
with open(os.path.join(csvdir, 'on-street-metered-parking.csv'), 'rU') as f:
for row in csv.reader(f):
(street, address, side, street_from, street_to, id, mech_id,
model, door, vault, issues) = row
meter = api.Meter()
meter.street = street.rstrip('E')
meter.address = address
meter.side = side
meter.street_from = street_from
meter.street_to = street_to
meter.id = id
meter.mech_id = int(mech_id)
meter.door = door
meter.vault = vault
meter.issues = issues
db.session.add(meter)
with open(os.path.join(csvdir, 'parking-garages.csv'), 'rU') as f:
for row in csv.reader(f):
(location_number, facility, street, address, lat, lon, spaces,
cost) = row
garage = api.Garage()
garage.id = int(location_number)
garage.facility = facility
garage.street = street
garage.address = address
garage.lat = float(lat)
garage.lon = float(lon)
garage.spaces = int(spaces)
garage.cost = int(cost[1:])
db.session.add(garage)
db.commit()
def update_item(todo):
''' PUT an update the todo item with id <item_id> or DELETE it '''
if request.method == 'PUT':
if not request.json:
return jsonify({"message": "no json data supplied"})
if 'text' not in request.json:
return jsonify({"message": "no text supplied"})
todo.text = request.json.get('text')
db.session.add(todo)
db.commit()
return jsonify({"message": "success"})
if request.method == 'DELETE':
db.session.delete(todo)
db.session.commit()
return jsonify({"message": "success"})
def delete(self, param):
reviewid = int(param)
# lookup review id and enforce reviewer_userid
c = db.cursor(cursor_factory=RealDictCursor)
c.execute(
"SELECT id FROM reviews WHERE reviewer_userid = %s and id = %s",
(request.id, reviewid))
# check if we got a result
row = c.fetchone()
if row is None:
return 'Review does not exist', 404
# get all reviews for user, join with users table
c.execute("DELETE FROM reviews WHERE id = %s", (reviewid, ))
db.commit()
return 'OK', 202
def create(post_id, commentor_id, content):
cursor = db.cursor()
cursor.execute(
"INSERT INTO Comment (post_id, commentor_id, content) VALUES (%s, %s, %s)",
(post_id, commentor_id, content['content']))
last_id = cursor.lastrowid
db.commit()
cursor.execute("SELECT * FROM Comment WHERE id=%s", (last_id, ))
comment_info = cursor.fetchone()
cursor.close()
comment = {
"id": comment_info[0],
"post_id": comment_info[1],
"commentor_id": comment_info[2],
"content": comment_info[3],
"created": comment_info[4],
"updated": comment_info[5]
}
return comment
def post(self):
oldpassword = get_form('oldpassword')
newpassword = get_form('newpassword')
# make sure required fields are not empty
if oldpassword is None or newpassword is None:
return 'Missing fields', 400
# salt password
c = db.cursor(cursor_factory=RealDictCursor)
c.execute("SELECT * FROM users WHERE cppemail = %s", (request.email,))
salt = os.urandom(32).encode('hex')
passhash = hashlib.sha256(newpassword + salt).hexdigest()
# write to db
c.execute("UPDATE users SET salt = %s, passhash = %s WHERE id = %s", (salt, passhash, request.id))
db.commit()
return 'OK', 200
def create(post_dictionary):
post = {}
cursor = db.cursor()
if not post_dictionary['title'] or not post_dictionary['content']:
return
cursor.execute("INSERT INTO Posts (author_id, title, content) Values(%s, %s, %s)",
(1, post_dictionary['title'], post_dictionary['content']))
db.commit()
last_id = cursor.lastrowid
cursor.execute("SELECT * FROM Posts WHERE id=%s", (last_id,))
post_info = cursor.fetchone()
post = {
"id": post_info[0],
"author_id": post_info[1],
"title": post_info[2],
"content": post_info[3],
"created": post_info[4],
"updated": post_info[5]
}
cursor.close()
return post
def create(account_dictionary):
cursor = db.cursor()
cursor.execute(
"INSERT INTO Users (username, password) VALUES (%s, %s);",
(account_dictionary['username'], account_dictionary['password']))
last_id = cursor.lastrowid
db.commit()
cursor.execute("SELECT * FROM Users WHERE id=%s", (last_id, ))
account_info = cursor.fetchone()
cursor.close()
account = {
"id": account_info[0],
"username": account_info[1],
"password": bcrypt.hashpw(account_info[2], bcrypt.gensalt()),
"nickname": account_info[3],
"picture": account_info[4],
"user_level": account_info[5],
"deleted": account_info[6],
"created": account_info[7],
"updated": account_info[8]
}
return account
def post(self):
email = get_form('email')
alt = get_form('altemail')
password = get_form('password')
fullname = get_form('fullname')
# make sure required fields are not empyty
if email is None or password is None or fullname is None or len(fullname) == 0:
return 'Missing fields', 400
# use regex to enforce .cpp email
if re.match(r'^[a-zA-Z]+@cpp\.edu$', email) is None:
return 'cpp email', 400
# make sure user doesn't already exist
c = db.cursor()
c.execute("SELECT id FROM users WHERE cppemail = %s", (email,))
if c.fetchone() is not None:
return 'account exists', 400
# salt and hash password
salt = os.urandom(32).encode('hex')
passhash = hashlib.sha256(password + salt).hexdigest()
verified = True # TODO: implement email verification
# write to db
c.execute("INSERT INTO users (cppemail, fullname, altemail, salt, passhash, verified, timestamp) VALUES (%s, %s, %s, %s, %s, %s, CURRENT_TIMESTAMP) RETURNING id", (email, fullname, alt, salt, passhash, verified))
userid = c.fetchone()[0]
values = []
for day in range(7):
values.append(userid)
values.append(day)
# write empty schedule to db
c.execute("INSERT INTO schedule (userid, dayofweek) VALUES (%s, %s), (%s, %s), (%s, %s), (%s, %s), (%s, %s), (%s, %s), (%s, %s)", tuple(values))
db.commit()
return 'OK', 200
def add_value(self, v):
self.schema['values'].append(v)
flag_modified(self, "schema")
db.add(self)
db.commit()
def delete(comment_id):
cursor = db.cursor()
cursor.execute("DELETE FROM Comment WHERE id=%s", (comment_id, ))
db.commit()
cursor.close()
return
def delete(self, session):
db.session.delete(session)
db.commit()
return {"result": True}