def update_member(id):
db_connection = connect_to_database()
#display table row
display_row = 'SELECT * FROM `band members` WHERE `band member id` = %s' % (id)
row_result = execute_query(db_connection, display_row).fetchall()
#display existing data
if request.method == 'GET':
member_query = 'SELECT * FROM `band members` WHERE `band member id` = %s' % (id)
member_result = execute_query(db_connection, member_query).fetchone()
return render_template('members_update.html', row = row_result, member = member_result)
#display update form and process any updates using the same function, then redirect back to band members page which displays the updated band members table
elif request.method == 'POST':
db_connection = connect_to_database()
member_id = request.form['member_id']
member_name = request.form['member_name']
instrument = request.form['instrument']
birthdate = request.form['birthdate']
data = (member_name, instrument, birthdate, member_id)
query = "UPDATE `band members` SET `name` = %s, `instrument` = %s, `birthdate` = %s WHERE `band member id` = %s"
result = execute_query(db_connection, query, data)
return redirect('/band_members')
def update_album(id):
db_connection = connect_to_database()
#display table row
display_row = 'SELECT * FROM `album` WHERE `album id` = %s' % (id)
row_result = execute_query(db_connection, display_row).fetchall()
#display existing data
if request.method == 'GET':
album_query = 'SELECT * FROM `album` WHERE `album id` = %s' % (id)
album_result = execute_query(db_connection, album_query).fetchone()
return render_template('album_update.html', row = row_result, album = album_result)
#display update form and process any updates using the same function, then redirect back to album page which displays the updated album table
elif request.method == 'POST':
db_connection = connect_to_database()
album_id = request.form['album_id']
album_name = request.form['album_name']
release_date = request.form['release_date']
data = (album_name, release_date, album_id)
query = "UPDATE `album` SET `album name` = %s, `release date` = %s WHERE `album id` = %s"
result = execute_query(db_connection, query, data)
return redirect('/album')
def engineers():
if request.method == 'GET':
db_connection = connect_to_database()
query = "SELECT engineerID, firstName, lastName, available FROM Service_Engineers;"
result = execute_query(db_connection, query).fetchall()
print(f"All Engineers in DB: {result}")
return render_template('engineers.html', rows=result)
if request.method == 'POST' and 'firstName' in request.form:
firstName = request.form['firstName']
lastName = request.form['lastName']
available = request.form['available']
db_connection = connect_to_database()
query = "INSERT INTO Service_Engineers (firstName, lastName, available) VALUES (%s, %s, %s)"
data = firstName, lastName, available
execute_query(db_connection, query, data)
return redirect(url_for('engineers'))
if request.method == 'POST':
lastName = request.form['lastName']
db_connection = connect_to_database()
query = "SELECT engineerID, firstName, lastName, available FROM Service_Engineers WHERE lastName = '%s'" % (
lastName)
searchresult = execute_query(db_connection, query).fetchall()
print(f"Search Result: {searchresult}")
query = "SELECT engineerID, firstName, lastName, available FROM Service_Engineers;"
result = execute_query(db_connection, query).fetchall()
print(f"All Engineers in DB: {result}")
return render_template('engineers.html',
searchrows=searchresult,
rows=result)
else:
return render_template('engineers.html')
def update_tracks(id):
db_connection = connect_to_database()
#display table row
display_row = 'SELECT * FROM `tracks` WHERE `track id` = %s' % (id)
row_result = execute_query(db_connection, display_row).fetchall()
#display existing data
if request.method == 'GET':
track_query = 'SELECT * FROM `tracks` WHERE `track id` = %s' % (id)
track_result = execute_query(db_connection, track_query).fetchone()
album_list = 'SELECT `album id` FROM `album`';
album_result = execute_query(db_connection, album_list).fetchall();
return render_template('track_update.html', track = track_result, row = row_result, albums = album_result)
#display update form and process any updates using the same function, then redirect back to track page which displays the updated tracks table
elif request.method == 'POST':
db_connection = connect_to_database()
track_id = request.form['track_id']
track_name = request.form['track_name']
track_length = request.form['track_length']
album_id = request.form['album_id']
data = (track_name, track_length, album_id, track_id)
query = "UPDATE `tracks` SET `track name` = %s, `track length` = %s, `album id` = %s WHERE `track id` = %s"
result = execute_query(db_connection, query, data)
return redirect('/track')
def wh_inv():
print("Warehouse Inventory page rendering")
cur = mysql.connection.cursor()
# OLD QUERY: query = "SELECT location_ID, product_serial_number FROM warehouse_inventory;"
# NEW QUERY WITH JOIN
query = """SELECT warehouse_inventory.location_ID, warehouse_inventory.product_serial_number, warehouse_locations.location_city,
product_inventory.product_brand, product_inventory.product_model, warehouse_inventory.inventory_ID
FROM `warehouse_inventory` LEFT JOIN warehouse_locations ON warehouse_inventory.location_ID = warehouse_locations.location_ID
LEFT JOIN product_inventory ON warehouse_inventory.product_serial_number = product_inventory.product_serial_number;"""
cur.execute(query)
result = cur.fetchall()
print(result)
print("warehouse Inventory query pull success")
#query for locations dropdown menu
query_locs = "SELECT location_ID, location_city FROM warehouse_locations"
db_connection = connect_to_database()
result_locs = execute_query(db_connection, query_locs).fetchall()
print("Location results")
print(result_locs)
#query for serial number dropdown menu
query_serNos = "SELECT product_serial_number, product_brand, product_model FROM product_inventory"
db_connection = connect_to_database()
result_serNos = execute_query(db_connection, query_serNos).fetchall()
print("Product results")
print(result_serNos)
return render_template('warehouse_inventory.html', wh_inv = result, locs = result_locs, prods = result_serNos)
def update_set_list(id):
db_connection = connect_to_database()
#display table row
display_row = 'SELECT `set list`.`set list id`, `set list`.`line up id`, `shows`.`city`, `set list`.`track id`, `tracks`.`track name` FROM `set list` JOIN `shows` ON `set list`.`line up id` = `shows`.`line up id` JOIN `tracks` ON `set list`.`track id` = `tracks`.`track id` WHERE `set list id` = %s' % (id)
row_result = execute_query(db_connection, display_row).fetchall()
#display existing data
if request.method == 'GET':
sl_query = 'SELECT * FROM `set list` WHERE `set list id` = %s' % (id)
sl_result = execute_query(db_connection, sl_query).fetchone()
query1 = 'SELECT `track id`, `track name` FROM `tracks`';
result_track = execute_query(db_connection, query1).fetchall();
return render_template('set_list_update.html', row = row_result, setlist = sl_result, track_id = result_track)
#display update form and process any updates using the same function, then redirect back to set list page which displays the updated set list table
elif request.method == 'POST':
db_connection = connect_to_database()
setlist_id = request.form['setlist_id']
track_id = request.form['track_id']
data = (track_id, setlist_id)
query = query = "UPDATE `set list` SET `track id` = %s WHERE `set list id` = %s;"
result = execute_query(db_connection, query, data)
return redirect('/set_list')
def engineer_pods():
if request.method == 'GET':
db_connection = connect_to_database()
query = "SELECT engineerID, podID FROM Engineer_Pods;"
podquery = "SELECT * FROM Transport_Pods;"
engquery = "SELECT * FROM Service_Engineers;"
joinquery = "SELECT DISTINCT Engineer_Pods.engineerID, Service_Engineers.firstName, Service_Engineers.lastName, Engineer_Pods.podID FROM Service_Engineers INNER JOIN Engineer_Pods on Service_Engineers.engineerID = Engineer_Pods.engineerID ORDER BY intersectionID"
result = execute_query(db_connection, query).fetchall()
print(f"All Engineer_Pods in DB: {result}")
podresult = execute_query(db_connection, podquery).fetchall()
print(f"All Pods in DB: {podresult}")
engresult = execute_query(db_connection, engquery).fetchall()
print(f"All Engineers in DB: {engresult}")
joinresult = execute_query(db_connection, joinquery).fetchall()
return render_template('engineer_pods.html',
rows=result,
podresults=podresult,
engresults=engresult,
joinresults=joinresult)
if request.method == 'POST':
engineer = request.form['engineerID']
engID = ""
i = 0
while i < len(
engineer) and engineer[i] != ":": # get eng ID from engineer
engID += engineer[i]
i += 1
print('eng id', engID)
podID = request.form['podID']
db_connection = connect_to_database()
availquery = "SELECT available FROM Service_Engineers WHERE engineerID = %s" % (
engID) #ensure engineer is available
available = execute_query(db_connection, availquery).fetchone()
if available == (1, ):
dupquery = "SELECT * FROM Engineer_Pods WHERE engineerID = %s AND podID = %s" % (
engID, podID) # ensure won't result in duplicate assignemnt
duplicate = available = execute_query(db_connection,
dupquery).fetchone()
if duplicate is None:
print('Creating engineer/pod assignment')
query = "INSERT INTO Engineer_Pods (engineerID, podID) VALUES (%s, %s)"
data = engID, podID
execute_query(db_connection, query, data)
return redirect(url_for('engineer_pods'))
def authorLookUp():
db_connection = connect_to_database()
# Initial visit to page
if request.method == 'GET':
query = 'SELECT * FROM authors'
result = execute_query(db_connection, query)
return render_template("/author/lookUp.html", rows=result)
# Look up author
elif request.method == 'POST':
fname = request.form['fname']
lname = request.form['lname']
query = 'SELECT * FROM authors WHERE first_name = %s AND last_name = %s'
data = (fname, lname)
result = execute_query(db_connection, query, data).fetchall()
# If no such author exists, show message
if result == ():
query = 'SELECT * FROM authors'
result = execute_query(db_connection, query).fetchall()
message = "No such author found."
return render_template("/author/lookUp.html",
rows=result,
query_message=message)
# Otherwise, display result
return render_template("/author/lookUp.html", rows=result)
def memberAdd():
print("Fetching and rendering add member page.")
db_connection = connect_to_database()
# Initial visit to page
if request.method == 'GET':
query = 'SELECT member_ID, first_name, last_name, address, email from members'
result = execute_query(db_connection, query).fetchall()
print(result)
return render_template("/member/add.html", rows=result)
# Adding member
elif request.method == 'POST':
fname = request.form['fname']
lname = request.form['lname']
address = request.form['address']
email = request.form['email']
# If no email is provided, change email to NULL (None in Python)
if email == '':
email = None
query = 'INSERT INTO members (first_name, last_name, address, email) VALUES (%s, %s, %s, %s)'
data = (fname, lname, address, email)
execute_query(db_connection, query, data).fetchall()
# Display results
query = 'SELECT member_ID, first_name, last_name, address, email from members'
add_result = execute_query(db_connection, query).fetchall()
message = "Member added."
return render_template("/member/add.html",
rows=add_result,
query_message=message)
def memberLookup():
print("Fetching and rendering look up member page.")
db_connection = connect_to_database()
# Initial visit to page
if request.method == 'GET':
query = 'SELECT member_ID, first_name, last_name, address, email from members'
result = execute_query(db_connection, query).fetchall()
print(result)
return render_template("/member/lookUp.html", rows=result)
# Searching for member
elif request.method == 'POST':
fname = request.form['fname']
lname = request.form['lname']
query = 'SELECT member_ID, first_name, last_name, address, email from members where first_name = %s AND last_name = %s'
data = (fname, lname)
search_result = execute_query(db_connection, query, data).fetchall()
# If no such member exists, show message
if search_result == ():
query = 'SELECT member_ID, first_name, last_name, address, email from members'
result = execute_query(db_connection, query).fetchall()
message = "No such member found."
return render_template("/member/lookUp.html",
rows=result,
query_message=message)
# Otherwise, display member
return render_template("/member/lookUp.html", rows=search_result)
def transactionLookUp():
print("Fetching and rendering look up transaction page.")
db_connection = connect_to_database()
# Initial visit to page
if request.method == 'GET':
query = 'SELECT checked_out_books.member_ID, members.first_name, members.last_name, checked_out_books.ISBN, books.title, checked_out_books.check_out_date, checked_out_books.due_date, checked_out_books.return_date, checked_out_books.employee_ID FROM checked_out_books INNER JOIN members ON checked_out_books.member_ID = members.member_ID INNER JOIN books ON checked_out_books.ISBN = books.ISBN'
result = execute_query(db_connection, query).fetchall()
print(result)
return render_template("/transaction/lookUp.html", rows=result)
# Searching for transaction
elif request.method == 'POST':
fname = request.form['fname']
lname = request.form['lname']
# Find transactions by member's first and last name
query = 'SELECT checked_out_books.member_ID, members.first_name, members.last_name, checked_out_books.ISBN, books.title, checked_out_books.check_out_date, checked_out_books.due_date, checked_out_books.return_date, checked_out_books.employee_ID FROM checked_out_books INNER JOIN members ON checked_out_books.member_ID = members.member_ID INNER JOIN books ON checked_out_books.ISBN = books.ISBN WHERE members.first_name = %s AND members.last_name = %s'
data = (fname, lname)
search_result = execute_query(db_connection, query, data).fetchall()
# If no such transaction exists, show message
if search_result == ():
query = 'SELECT checked_out_books.member_ID, members.first_name, members.last_name, checked_out_books.ISBN, books.title, checked_out_books.check_out_date, checked_out_books.due_date, checked_out_books.return_date, checked_out_books.employee_ID FROM checked_out_books INNER JOIN members ON checked_out_books.member_ID = members.member_ID INNER JOIN books ON checked_out_books.ISBN = books.ISBN'
result = execute_query(db_connection, query).fetchall()
message = "No such transaction found."
return render_template("/transaction/lookUp.html",
rows=result,
query_message=message)
return render_template("/transaction/lookUp.html", rows=search_result)
def create_campaign():
"""
Serves the Create Campaign page to Dungeon Masters
"""
if not logged_in():
return redirect(location='/login', code=302)
elif session['player_type'] != 'DM':
print(
f"Error: User: {session['user_id']} accessed the create-campaign handler and is not a DM. Logging out."
)
logout_user()
return redirect(location='/login', code=302)
# get the DM's campaign data
db_connection = db.connect_to_database()
query_open_campaigns = queries.get_campaigns_by_dm(session['user_id'],
status='Open')
query_closed_campaigns = queries.get_campaigns_by_dm(session['user_id'],
status='Closed')
result_open_campaigns = db.execute_query(db_connection,
query_open_campaigns)
result_closed_campaigns = db.execute_query(db_connection,
query_closed_campaigns)
return render_template("create-campaign.html", \
open_campaigns=result_open_campaigns.fetchall(),\
closed_campaigns=result_closed_campaigns.fetchall())
def credential_check():
"""
Returns values denoting if the username and/or email arguments are already in-use
"""
username = request.args['username']
email = request.args['email']
db_connection = db.connect_to_database()
username_query = queries.get_user_with_username(username)
username_result = db.execute_query(db_connection, username_query)
email_query = queries.get_user_with_email(email)
email_result = db.execute_query(db_connection, email_query)
# return logic based on if the email/username are or are not unique
if email_result.rowcount == 0 and username_result.rowcount == 0:
return "1" # must be 1. tells the JS GET handler that the Username+Email are available for use
elif email_result.rowcount == 0:
return "username"
elif username_result.rowcount == 0:
return "email"
else:
return "username and email"
# this statement should never be hit, had it here for testing purposes, keeping it around for now.
return "1"
def books():
books_form = BooksForm()
# Week 7: Learn using Python and Flask Framework - Inserting Data Using Flask Video
if request.method == 'POST':
title = request.form['title']
author = request.form['author']
bookGenreID = request.form['genre']
try:
# CREATE functionality
db_connection = connect_to_database()
query = '''
INSERT INTO Books (title, author, bookGenreID)
VALUES (%s, %s, %s);
'''
data = (title,author,bookGenreID)
execute_query(db_connection, query, data)
flash('Successfully added {} by {}!'.format(title, author), 'success')
except:
flash('The book already exists. Please add a new book.', 'danger')
genres_list = get_genres()
books_form.genre.choices = genres_list
all_books = get_all_books() # READ functionality
return render_template('books.html', form=books_form, active={'books':True}, books = all_books)
def attendees():
club_names_list = get_club_names()
formSelectClub = SelectClub()
formSelectClub.clubName.choices = club_names_list
club_meetings = []
select_club = False
if request.method == 'POST' and formSelectClub.validate():
club = formSelectClub.clubName.data
club_meetings = get_club_meetings(club)
select_club = True
if request.method == 'DELETE':
meetingID = request.args['meetingID']
memberID = request.args['memberID']
# print('meetingID', meetingID, 'memberID', memberID)
db_connection = connect_to_database()
query = '''
DELETE FROM meetings_members
WHERE meetingID = %s AND memberID = %s
'''
data = (meetingID, memberID)
execute_query(db_connection, query, data)
select_query = '''
SELECT * FROM meetings_members
WHERE meetingID = %s AND memberID = %s
'''
row = execute_query(db_connection, select_query, data).fetchall()
if len(row) == 0:
print('attendee succussfully deleted')
return 'OK', 200
return render_template('attendees.html',
formSelectClub=formSelectClub,
active={'meetings':True, 'attendees':True},
club_meetings=club_meetings,
select_club=select_club)
def meeting_signup_member(meetingID, memberID, email):
'''
Tries signing up a member to specified meeting.
If successful, inserts into meetings_members, and flashes success message,
otherwise flashes error message.
'''
try:
db_connection = connect_to_database()
query = '''
INSERT INTO meetings_members (meetingID, memberID)
VALUES (%s, %s)
'''
data = (meetingID, memberID)
execute_query(db_connection, query, data)
except MySQLdb.Error as err:
print(err)
if err.args[0] == 1062:
flash('''
Member with email {} is already signed up
for that meeting.
'''.format(email), 'danger')
else:
flash('Error: {}'.format(err), 'danger')
return False
return True
def index():
db_connection = connect_to_database()
all_locations = 'SELECT * FROM Locations;'
all_locations_results = execute_query(db_connection,
all_locations).fetchall()
print(f"All Locations in DB: {all_locations_results}")
return render_template('index.html', index_locations=all_locations_results)
def checkPlayerPin(player_id, pin):
db_connection = connect_to_database()
query = "select 1 from players where id = %s and pin = %s;"
data = (player_id, pin)
result = execute_query(db_connection, query, data)
rl = list(result.fetchall())
return len(rl)
def update_customer(id):
db_connection = connect_to_database()
print("Update customer start")
if request.method == "GET":
print("Getting customer record")
cust_q = "SELECT customer_ID, first_name, last_name, email, phone_number FROM customers WHERE customer_ID = %s" % (id)
cust_result = execute_query(db_connection, cust_q).fetchone()
if cust_result == None:
return "Could not find customer"
return render_template('customer_update.html', customer = cust_result)
elif request.method == "POST":
print("Updating customer")
# Get data from the user edit on page
cust_id = id
fname = request.form['first_name']
lname = request.form['last_name']
email = request.form['email']
phone = request.form['phone_number']
print(request.form)
query = "UPDATE customers SET first_name = %s, last_name = %s, email = %s, phone_number = %s WHERE customer_id = %s"
data = (fname, lname, email, phone, cust_id)
result = execute_query(db_connection, query, data)
print("Customer updated")
return redirect(url_for('customers'))
def employeeLookUp():
print("Fetching and rendering look up employee page.")
db_connection = connect_to_database()
# Initial visit to page
if request.method == 'GET':
query = 'SELECT employee_ID, first_name, last_name from employees'
result = execute_query(db_connection, query).fetchall()
print(result)
return render_template("/employee/lookUp.html", rows=result)
# Finding employee
elif request.method == 'POST':
fname = request.form['fname']
lname = request.form['lname']
query = 'SELECT employee_ID, first_name, last_name from employees where first_name = %s AND last_name = %s'
data = (fname, lname)
search_result = execute_query(db_connection, query, data).fetchall()
# If no such employee exists, show message
if search_result == ():
query = 'SELECT employee_ID, first_name, last_name from employees'
result = execute_query(db_connection, query).fetchall()
message = "No such employee found."
return render_template("/employee/lookUp.html",
rows=result,
query_message=message)
# Otherwise, display result
return render_template("/employee/lookUp.html", rows=search_result)
def employeeAdd():
print("Fetching and rendering add employee page.")
db_connection = connect_to_database()
# Initial visit to page
if request.method == 'GET':
query = 'SELECT employee_ID, first_name, last_name from employees'
result = execute_query(db_connection, query).fetchall()
print(result)
return render_template("/employee/add.html", rows=result)
# Adding employee
elif request.method == 'POST':
fname = request.form['fname']
lname = request.form['lname']
query = 'INSERT INTO employees (first_name, last_name) VALUES (%s, %s)'
data = (fname, lname)
execute_query(db_connection, query, data).fetchall()
# Display result
query = 'SELECT employee_ID, first_name, last_name from employees'
add_result = execute_query(db_connection, query).fetchall()
message = "Employee added."
return render_template("/employee/add.html",
rows=add_result,
query_message=message)
def join_or_leave_campaign():
"""
adds or removes a user to/from a campaign
"""
if not logged_in():
return redirect(location='/login', code=302)
db_connection = db.connect_to_database()
# execute query (join or leave)
campaign = request.get_json()
if campaign['action'] == 'Join':
#TODO: update JS and this handler to handle the character_id if a user chooses.
query = queries.join_campaign(session['user_id'],
campaign['id'],
character_id='Null')
elif campaign['action'] == 'Leave':
query = queries.leave_campaign(session['user_id'], campaign['id'])
else:
return json.dumps({"error":
"no action or incorrect action specified"}), 500
db.execute_query(db_connection, query)
# JS will automatically reload the page on a success response
return "1"
def CUD_characters():
"""
Create, Update, and Delete Characters
Action determined by POST request
"""
# get Action, connect to db
character = request.get_json()
print(character)
action = character['action']
db_connection = db.connect_to_database()
# get query depending on requested Action
if action == 'create':
query = queries.create_character(session['user_id'], \
character['name'], \
character['class_'], \
character['traits'])
elif action == 'update':
query = queries.update_character(character['id'], \
character['name'], \
character['class_'], \
character['traits'])
elif action == 'delete':
query = queries.delete_character(character['id'])
else:
return "error: incorrect action in POST request", 400
print("Executing character query:")
db.execute_query(db_connection, query)
return "1"
def account_update():
"""
Update a user's account info
"""
if not logged_in():
return redirect(location='/login', code=302)
# update account info
account = request.get_json()
db_connection = db.connect_to_database()
print(account)
query = queries.update_account(session['user_id'], \
account['name'], \
account['email'], \
account['playstyle'], \
account['campaign_history'])
db.execute_query(db_connection, query)
# if applicable, update password
if account['pw'] != '':
query = queries.update_account_pw(session['user_id'], account['pw'])
db.execute_query(db_connection, query)
logout_user()
return "1"
def logged_in():
# returns True if the user is logged in, false otherwise
# logged in =
# 1. Client has a Session cookie (user_id, username, email, player_type)
# 2. Their session_cookie user_id is in the database
try:
db_connection = db.connect_to_database()
user_id = session['user_id']
username = session['username']
query = f"SELECT user_id FROM users WHERE user_id = {user_id} AND username = '******';"
result = db.execute_query(db_connection, query)
result.fetchall()[0][
'user_id'] # if user id does not exist, move to exception clause
except:
print("User is not logged in")
logout_user()
return False
try:
if session['user_id'] and session['username'] and session[
'email'] and session['player_type']:
return True
except:
return False
return False
def add_customer():
print("Adding a customer")
f_name = request.form["first_name"]
l_name = request.form["last_name"]
email = request.form["email"]
phone = request.form["phone_number"]
emp_ID = request.form["employee_id"]
print("First name ", f_name)
db_connection = connect_to_database()
#check if an employee_ID was entered
if emp_ID == "NULL":
query = "INSERT INTO customers (first_name, last_name, email, phone_number) VALUES (%s, %s, %s, %s)"
data = (f_name, l_name, email, phone)
result = execute_query(db_connection, query, data)
print("Insert result, null emp - ", result)
else:
query = "INSERT INTO customers (first_name, last_name, email, phone_number, employee_ID) VALUES (%s, %s, %s, %s, %s)"
data = (f_name, l_name, email, phone, emp_ID)
result = execute_query(db_connection, query, data)
print("Insert result, NOT null emp - ", result)
print("Customer added")
return redirect(url_for('customers'))
def update_availability():
"""
Updates a user's availability
and removes them from any Open campaigns where they are now unavailable to play on that day
POST request accepts a JSON object with the user's updated availability:
ex: {monday:1, tuesday:0, wednesday:1, ...}
"""
print("hit the @/'availability' POST handler")
if not logged_in():
return redirect(location='/login', code=302)
user_availability = request.get_json()
print('user availability = ')
print(user_availability)
# execute queries
db_connection = db.connect_to_database()
query_update_availability = \
queries.update_my_availability(session['user_id'], user_availability)
query_del_campaign_participation = \
queries.delete_user_from_campaign_if_availability_removed(session['user_id'])
db.execute_query(db_connection, query_update_availability)
db.execute_query(db_connection, query_del_campaign_participation)
return "1"
def memberUpdate():
print("Fetching and rendering select member to update page.")
db_connection = connect_to_database()
# Initial visit to page
if request.method == 'GET':
query = 'SELECT member_ID, first_name, last_name, address, email from members'
result = execute_query(db_connection, query).fetchall()
print(result)
return render_template("/member/update.html", rows=result)
# Finding member to be updated
elif request.method == 'POST':
memberID = request.form['memberID']
query = 'SELECT member_ID, first_name, last_name, address, email from members WHERE member_ID = %s'
data = ([memberID])
member_result = execute_query(db_connection, query, data).fetchone()
print(member_result)
# If no such member exists, show message
if member_result == None:
query = 'SELECT member_ID, first_name, last_name, address, email from members'
result = execute_query(db_connection, query).fetchall()
message = "No such member found."
return render_template("/member/update.html",
rows=result,
query_message=message)
# Otherwise, render pre-populated upate form
return render_template("/member/updateForm.html", member=member_result)
def authenticate():
"""
Authenticates user w/ username + pwd, logs them in if valid, returns '0' otherwise
"""
# capture credentials
credentials = request.get_json()
username = credentials['username']
pwd = credentials['pwd']
# TODO: Cleanse the query params.
# test credentials
db_connection = db.connect_to_database()
query = f"SELECT * FROM users WHERE username = '******' AND password = '******'"
result = db.execute_query(db_connection, query)
# create session
try:
result = result.fetchall()
session['user_id'] = result[0]['user_id']
session['username'] = result[0]['username']
session['email'] = result[0]['email']
session['player_type'] = result[0]['player_type']
except:
return "0"
return redirect(location='/login', code=302)
def updateForm():
print("Fetching and rendering update member results page.")
db_connection = connect_to_database()
# Get updates values from form
if request.method == 'POST':
memberID = request.form['memberID']
fname = request.form['fname']
lname = request.form['lname']
address = request.form['address']
email = request.form['email']
# If no email is provided, change email to NULL (None in Python)
if email == '':
email = None
# Update member
query = 'UPDATE members SET first_name = %s, last_name = %s, address = %s, email = %s WHERE member_ID = %s'
data = (fname, lname, address, email, [memberID])
update_result = execute_query(db_connection, query, data)
# Display updated result
query = 'SELECT member_ID, first_name, last_name, address, email from members WHERE member_ID = %s'
data = ([memberID])
updated_member = execute_query(db_connection, query, data)
# Display all records
query = 'SELECT member_ID, first_name, last_name, address, email from members'
result = execute_query(db_connection, query).fetchall()
return render_template("/member/updateResult.html",
updatedRows=updated_member,
allRows=result)