def get(self, member_id):
parser = get_default_parser()
args = parser.parse_args()
connection = get_db_connection()
if not is_token_valid(args['authentication-token'], connection):
connection.close()
abort(403)
select_member_query = "SELECT first_name, last_name, balance, last_membership_payment, school, email, phone, " \
"is_former_staff " \
"FROM members " \
"WHERE member_id = %(id)s"
args = {"id": member_id}
with connection.cursor() as cursor:
cursor.execute(select_member_query, args)
if cursor.rowcount < 1:
connection.close()
abort(404)
row = cursor.fetchone()
row['last_membership_payment'] = str(
row['last_membership_payment'])
if row['is_former_staff'] == b'\x01':
row['is_former_staff'] = True
else:
row['is_former_staff'] = False
row['id'] = member_id
connection.close()
return row, 201
def get(self, party_id):
parser = get_default_parser()
args = parser.parse_args()
connection = get_db_connection()
if not is_token_valid(args['authentication-token'], connection):
connection.close()
abort(403)
select_party_query = "SELECT name, date, normal_beer_price, special_beer_price " \
"FROM parties " \
"WHERE party_id = %(id)s"
select_served_beers_query = "SELECT s.product_id AS id, name, category, price " \
"FROM served_beers_at_party s " \
"INNER JOIN products p ON s.product_id = p.product_id " \
"WHERE s.party_id = %(id)s " \
"ORDER BY name ASC, category ASC"
party = {"id": party_id}
with connection.cursor() as cursor:
cursor.execute(select_party_query, party)
if cursor.rowcount < 1:
connection.close()
abort(404)
row = cursor.fetchone()
party['date'] = str(row['date'])
party['name'] = row['name']
party['normal_beer_price'] = row['normal_beer_price']
party['special_beer_price'] = row['special_beer_price']
party['served_beers'] = []
cursor.execute(select_served_beers_query, party)
for row in cursor.fetchall():
party['served_beers'].append(row)
connection.close()
return party, 201
def get(self):
parser = get_default_parser()
args = parser.parse_args()
connection = get_db_connection()
if not is_token_valid(args['authentication-token'], connection):
connection.close()
abort(403)
query = "SELECT party_id, name, date, number_of_attendees, balance " \
"FROM party_list"
parties = []
with connection.cursor() as cursor:
cursor.execute(query)
for row in cursor.fetchall():
number_of_attendees = row['number_of_attendees']
if number_of_attendees is None:
number_of_attendees = 0
balance = row['balance']
if balance is None:
balance = 0
parties.append({
'id': row['party_id'],
'name': row['name'],
'date': str(row['date']),
'number_of_attendees': number_of_attendees,
'balance': balance
})
connection.close()
return parties, 201
def put(self, party_id):
parser = get_default_parser()
args = parser.parse_args()
connection = get_db_connection()
if not is_token_valid(args['authentication-token'], connection):
connection.close()
abort(403)
if request.json is None:
abort(400, description="request should be json")
for key in [
'name', 'date', 'normal_beer_price', 'special_beer_price',
'served_beers'
]:
if key not in request.json:
abort(400, description="missing " + key + " key in JSON body")
check_party_exist_query = "SELECT party_id FROM parties WHERE party_id = %(id)s"
update_party_query = "UPDATE parties " \
"SET name = %(name)s, date = %(date)s, normal_beer_price = %(normal_beer_price)s, " \
"special_beer_price = %(special_beer_price)s " \
"WHERE party_id = %(id)s"
delete_beer_query = "DELETE FROM served_beers_at_party WHERE party_id = %(id)s"
insert_beer_query = "INSERT INTO served_beers_at_party(party_id, product_id, category) " \
"VALUES(%(party_id)s, %(product_id)s, %(category)s)"
party = {
'id': party_id,
'name': request.json['name'],
'date': request.json['date'],
'normal_beer_price': request.json['normal_beer_price'],
'special_beer_price': request.json['special_beer_price']
}
with connection.cursor() as cursor:
if not cursor.execute(check_party_exist_query, party):
connection.close()
abort(404)
cursor.execute(update_party_query, party)
cursor.execute(delete_beer_query, party)
for beer in request.json['served_beers']:
for key in ['id', 'category']:
if key not in beer:
connection.rollback()
connection.close()
abort(400,
description="missing " + key +
" key in served_beers item in JSON body")
row = {
'party_id': party_id,
'product_id': beer['id'],
'category': beer['category']
}
cursor.execute(insert_beer_query, row)
connection.commit()
connection.close()
return 201
def delete(self, member_id):
parser = get_default_parser()
args = parser.parse_args()
connection = get_db_connection()
if not is_token_valid(args['authentication-token'], connection):
connection.close()
abort(403)
update_query = "UPDATE members SET is_deleted = 1 WHERE member_id = %s"
with connection.cursor() as cursor:
if cursor.execute(update_query, member_id) < 1:
connection.close()
abort(404)
connection.commit()
connection.close()
return 201
def delete(self, transaction_id):
parser = get_default_parser()
args = parser.parse_args()
connection = get_db_connection()
if not is_token_valid(args['authentication-token'], connection):
connection.close()
abort(403)
delete_query = "DELETE FROM transactions WHERE transaction_id = %s"
with connection.cursor() as cursor:
if cursor.execute(delete_query, transaction_id) < 1:
connection.close()
abort(404)
connection.commit()
connection.close()
return 201
def get(self):
parser = get_default_parser()
args = parser.parse_args()
connection = get_db_connection()
if not is_token_valid(args['authentication-token'], connection):
connection.close()
abort(403)
query = "SELECT balance_too_low_threshold FROM parameters WHERE parameters_id = 1"
with connection.cursor() as cursor:
cursor.execute(query)
if cursor.rowcount < 1:
connection.close()
abort(404)
row = cursor.fetchone()
connection.close()
return row, 201
def post(self):
parser = get_default_parser()
args = parser.parse_args()
connection = get_db_connection()
if not is_token_valid(args['authentication-token'], connection):
connection.close()
abort(403)
if request.json is None:
abort(400, description="request should be json")
for key in [
'name', 'date', 'normal_beer_price', 'special_beer_price',
'served_beers'
]:
if key not in request.json:
abort(400, description="missing " + key + " key in JSON body")
insert_party_query = "INSERT INTO parties(name, date, normal_beer_price, special_beer_price) " \
"VALUES(%(name)s, %(date)s, %(normal_beer_price)s, %(special_beer_price)s)"
insert_beer_query = "INSERT INTO served_beers_at_party(party_id, product_id, category) " \
"VALUES(%(party_id)s, %(product_id)s, %(category)s)"
with connection.cursor() as cursor:
party = {
"name": request.json["name"],
"date": request.json["date"],
"normal_beer_price": request.json["normal_beer_price"],
"special_beer_price": request.json["special_beer_price"]
}
cursor.execute(insert_party_query, party)
party_id = connection.insert_id()
for beer in request.json['served_beers']:
for key in ['id', 'category']:
if key not in beer:
connection.rollback()
connection.close()
abort(400,
description="missing " + key +
" key in served_beers item in JSON body")
row = {
'party_id': party_id,
'product_id': beer['id'],
'category': beer['category']
}
cursor.execute(insert_beer_query, row)
connection.commit()
connection.close()
return {'id': party_id}, 201
def get(self, product):
parser = get_default_parser()
args = parser.parse_args()
connection = get_db_connection()
if not is_token_valid(args['authentication-token'], connection):
connection.close()
abort(403)
if product not in ["normal_beer", "special_beer"]:
connection.close()
abort(404)
query = "SELECT default_" + product + "_price AS default_product_price FROM parameters WHERE parameters_id = 1"
with connection.cursor() as cursor:
cursor.execute(query)
if cursor.rowcount < 1:
connection.close()
abort(404)
row = cursor.fetchone()
connection.close()
return row, 200
def put(self, member_id):
parser = get_default_parser()
args = parser.parse_args()
connection = get_db_connection()
if not is_token_valid(args['authentication-token'], connection):
connection.close()
abort(403)
update_query = "UPDATE members SET last_membership_payment = %(date)s WHERE member_id = %(id)s"
args = {
"date": datetime.date.today().strftime("%Y-%m-%d"),
"id": member_id
}
with connection.cursor() as cursor:
if cursor.execute(update_query, args) < 1:
connection.close()
abort(404)
connection.commit()
connection.close()
return 201
def put(self):
parser = get_default_parser()
args = parser.parse_args()
connection = get_db_connection()
if not is_token_valid(args['authentication-token'], connection):
connection.close()
abort(403)
if request.json is None:
abort(400, description="request should be json")
if 'balance_too_low_threshold' not in request.json:
abort(400, description="missing balance_too_low_threshold key in JSON body")
update_threshold_query = "UPDATE parameters " \
"SET balance_too_low_threshold = %(balance_too_low_threshold)s " \
"WHERE parameters_id = 1"
with connection.cursor() as cursor:
cursor.execute(update_threshold_query, request.json)
connection.commit()
connection.close()
return 201
def get(self, party_id):
parser = get_default_parser()
args = parser.parse_args()
connection = get_db_connection()
if not is_token_valid(args['authentication-token'], connection):
connection.close()
abort(403)
query = "SELECT transaction_id AS id, t.member_id AS member_id, first_name, last_name, amount, label, " \
"timestamp " \
"FROM transactions t INNER JOIN members m ON t.member_id = m.member_id " \
"WHERE party_id = %s " \
"ORDER BY timestamp DESC"
transactions = []
with connection.cursor() as cursor:
cursor.execute(query, party_id)
for row in cursor.fetchall():
row['timestamp'] = str(row['timestamp'])
transactions.append(row)
connection.close()
return transactions, 201
def post(self):
parser = get_default_parser()
args = parser.parse_args()
connection = get_db_connection()
if not is_token_valid(args['authentication-token'], connection):
connection.close()
abort(403)
if request.json is None:
abort(400, description="request should be json")
for key in ['first_name', 'last_name', 'school', 'phone', 'email']:
if key not in request.json:
abort(400, description="missing " + key + " key in JSON body")
insert_member_query = "INSERT INTO members(first_name, last_name, school, phone, email) " \
"VALUES(%(first_name)s, %(last_name)s, %(school)s, %(phone)s, %(email)s)"
with connection.cursor() as cursor:
cursor.execute(insert_member_query, request.json)
member_id = connection.insert_id()
connection.commit()
connection.close()
return {'id': member_id}, 201
def post(self):
parser = get_default_parser()
args = parser.parse_args()
connection = get_db_connection()
if not is_token_valid(args['authentication-token'], connection):
connection.close()
abort(403)
if request.json is None:
abort(400, description="request should be json")
for key in ['name', 'price', 'type']:
if key not in request.json:
abort(400, description="missing " + key + " key in JSON body")
insert_product_query = "INSERT INTO products(name, price, type) " \
"VALUES(%(name)s, %(price)s, %(type)s)"
with connection.cursor() as cursor:
cursor.execute(insert_product_query, request.json)
product_id = connection.insert_id()
connection.commit()
connection.close()
return {'id': product_id}, 201
def get(self):
parser = get_default_parser()
args = parser.parse_args()
connection = get_db_connection()
if not is_token_valid(args['authentication-token'], connection):
connection.close()
abort(403)
query = "SELECT member_id AS id, first_name, last_name, balance, last_membership_payment, is_former_staff " \
"FROM member_list"
members = []
with connection.cursor() as cursor:
cursor.execute(query)
for row in cursor.fetchall():
row['last_membership_payment'] = str(row['last_membership_payment'])
if row['is_former_staff'] == b'\x01':
row['is_former_staff'] = True
else:
row['is_former_staff'] = False
members.append(row)
connection.close()
return members, 201
def post(self):
parser = get_default_parser()
args = parser.parse_args()
connection = get_db_connection()
if not is_token_valid(args['authentication-token'], connection):
connection.close()
abort(403)
if request.json is None:
abort(400, description="request should be json")
for key in ['member_id', 'amount', 'label']:
if key not in request.json:
abort(400, description="missing " + key + " key in JSON body")
if 'party_id' not in request.json:
request.json['party_id'] = None
insert_transaction_query = "INSERT INTO transactions(member_id, party_id, label, amount) " \
"VALUES(%(member_id)s, %(party_id)s, %(label)s, %(amount)s)"
with connection.cursor() as cursor:
cursor.execute(insert_transaction_query, request.json)
transaction_id = connection.insert_id()
connection.commit()
connection.close()
return {'id': transaction_id}, 201
def put(self, product):
parser = get_default_parser()
args = parser.parse_args()
connection = get_db_connection()
if not is_token_valid(args['authentication-token'], connection):
connection.close()
abort(403)
if request.json is None:
abort(400, description="request should be json")
if 'default_product_price' not in request.json:
abort(400,
description="missing default_product_price key in JSON body")
if product not in ["normal_beer", "special_beer"]:
connection.close()
abort(404)
update_price_query = "UPDATE parameters " \
"SET default_" + product + "_price = %(default_product_price)s " \
"WHERE parameters_id = 1"
with connection.cursor() as cursor:
request.json['product'] = product
cursor.execute(update_price_query, request.json)
connection.commit()
connection.close()
return 200
def get(self):
parser = get_default_parser()
parser.add_argument('type', required=False, type=str)
args = parser.parse_args()
connection = get_db_connection()
if not is_token_valid(args['authentication-token'], connection):
connection.close()
abort(403)
query = "SELECT product_id, name, price, type FROM products"
if args['type'] is not None:
query += " WHERE type = %(type)s"
query += " ORDER BY name ASC, type ASC"
products = []
with connection.cursor() as cursor:
cursor.execute(query, args)
for row in cursor.fetchall():
products.append({
'id': row['product_id'],
'name': row['name'],
'price': row['price'],
'type': row['type']
})
connection.close()
return products, 201