def add_new():
if request.method == 'POST':
event_name = request.form['event_name']
venue = request.form['venue']
budget = request.form['budget']
description = request.form['desc']
cursor = db.get_db().cursor()
# Gets the ID to increment
cursor.execute("SELECT MAX(Id) FROM Events;")
fetch = cursor.fetchall()[0][0]
event_id = fetch + 1 if fetch else 1
# First, create the event.
cursor.execute('INSERT INTO Events (Id, Event_Name, Venue, Budget, Event_Desc) VALUES(%s, %s, %s, %s, %s)', (
event_id,
event_name,
venue,
budget,
description,
))
# Then, insert the user who created it to have LEVEL 3 CLEARANCE
cursor.execute("INSERT INTO Clearance (Member_Id, Clearance_Level, Event_Id) VALUES (%s, %s, %s)", (
session['member_id'],
"3",
event_id,
))
# Commit data
db.get_db().commit()
return redirect(url_for('event.description', id=str(event_id)))
return render_template('new.html')
def del_members(id):
# Deletes the entry based on the ID
try:
db_obj = db.get_db()
# Delete from database
cursor = db_obj.cursor()
# Delete the event committee
# Don't forget to delete the clearance too.
if request.form["ActiveTable"] == "0" or request.form["ActiveTable"] == "1":
cursor.execute("DELETE FROM Event_Committee WHERE Member_Id=%s AND Event_Id=%s;", (
request.form["ID"],
id,
))
cursor.execute("DELETE FROM Clearance WHERE Member_Id=%s AND Event_Id=%s;", (
request.form["ID"],
id,
))
# Delete the guest
elif request.form["ActiveTable"] == "2":
cursor.execute("DELETE FROM Guests WHERE Id=%s AND Event_Id=%s;", (
request.form["ID"],
id,
))
db_obj.commit()
return "1"
except Exception as e:
print(str(e))
return "0"
def description(id):
cursor = db.get_db().cursor()
cursor.execute('SELECT Event_Name, Event_Desc FROM Events WHERE Id=%s', (id,))
fetch = cursor.fetchall()[0]
return render_template("description.html", event_name=fetch[0], description_text=fetch[1])
def upd_all_members():
try:
if request.form["activeTable"] == "0" and session['isadmin']:
db_obj = db.get_db()
cursor = db_obj.cursor()
# Update the members table first,
cursor.execute('UPDATE Members SET Full_Name=%s, Position=%s WHERE Id=%s;', (
request.form['Name'],
request.form['Position'],
request.form['Id'],
))
# Then update the login cred
cursor.execute('UPDATE Login_Cred SET Email=%s, IsAdmin=%s WHERE Member_Id=%s;', (
request.form['Mail'],
request.form['isAdmin'] == "True",
request.form['Id'],
))
# Commit
db_obj.commit()
return "1"
except Exception as e:
print(str(e))
pass
return "0"
def admin():
# Check whether the user is an admin.
if session['isadmin']:
# Delete the event, and everything that is related to it in the database.
# Gets all the sponsors
cursor = db.get_db().cursor()
cursor.execute("SELECT Id, Sponsor_Name, Contact_Name, Sponsor_Address, Phone_Number, Sponsor_Type FROM Sponsor;")
sponsor_list = []
for x in cursor.fetchall():
sponsor_list.append({
"id": x[0],
"name": x[1],
"address": x[3],
"phone": x[4],
"type": x[5],
"cname": x[2]
})
return render_template("admin.html",
event_dict=get_event_list(),
sponsor_dict=sponsor_list
)
else:
# If user is not an admin then punish.
return render_template("nobueno.html")
def add_inventory(id):
try:
db_obj = db.get_db()
cursor = db_obj.cursor()
# Get the sponsor ID, if available
sponsor_id = None
if request.form['sponsor'] != "0":
sponsor_id = request.form['sponsor']
# Update datbase
cursor.execute('INSERT INTO Inventory (Item_Name, Item_Quantity, Sponsor_Id, Event_Id) VALUES (%s, %s, %s, %s);', (
request.form['name'],
request.form['amount'],
sponsor_id,
id,
))
# Commit
db_obj.commit()
return "1"
except Exception as e:
print(str(e))
return "0"
def new_admin():
try:
if request.form["ActiveTable"] == "0" and session['isadmin']:
db_obj = db.get_db()
cursor = db_obj.cursor()
# Insert the sponsors table
cursor.execute('INSERT INTO Sponsor (Sponsor_Name, Contact_Name, Sponsor_Address, Phone_Number, Sponsor_Type) VALUES (%s, %s, %s, %s, %s);', (
request.form['Name'],
request.form['Contact'],
request.form['Mail'],
request.form['Phone'],
request.form['Type'],
))
# Commit
db_obj.commit()
return "1"
except Exception as e:
print(str(e))
pass
return "0"
def upd_admin():
try:
if request.form["activeTable"] == "0" and session['isadmin']:
db_obj = db.get_db()
cursor = db_obj.cursor()
# Update the sponsor
cursor.execute('UPDATE Sponsor SET Sponsor_Name=%s, Contact_Name=%s, Sponsor_Address=%s, Phone_Number=%s, Sponsor_Type=%s WHERE Id=%s;', (
request.form['Name'],
request.form['Contact'],
request.form['Address'],
request.form['Phone'],
request.form['Type'],
request.form['Id'],
))
# Commit
db_obj.commit()
return "1"
except Exception as e:
print(str(e))
pass
return "0"
def del_finance(id):
# Deletes the entry based on the ID
try:
db_obj = db.get_db()
# Delete from database
cursor = db_obj.cursor()
# Delete the event committee
if request.form["ActiveTable"] == "0":
cursor.execute("DELETE FROM Income WHERE Id=%s;", (
request.form["ID"],
))
# Delete the guest
elif request.form["ActiveTable"] == "1":
cursor.execute("DELETE FROM Expenses WHERE Id=%s;", (
request.form["ID"],
))
db_obj.commit()
return "1"
except Exception as e:
print(str(e))
return "0"
def del_all_members():
try:
# Do an additional check here so the user cant delete itself.
if request.form["ActiveTable"] == "0" and session['isadmin'] and session['member_id'] != request.form['Id']:
db_obj = db.get_db()
cursor = db_obj.cursor()
# Delete login cred table
cursor.execute('DELETE FROM Login_Cred WHERE Member_Id=%s;', (
request.form['Id'],
))
# Then delete all reference regarding member_id
cursor.execute('DELETE FROM Clearance WHERE Member_Id=%s;', (request.form['Id'],))
cursor.execute('DELETE FROM Event_Committee WHERE Member_Id=%s;', (request.form['Id'],))
# Delete the members table last.
cursor.execute('DELETE FROM Members WHERE Id=%s;', (
request.form['Id'],
))
# Commit
db_obj.commit()
return "1"
except Exception as e:
print(str(e))
pass
return "0"
def delete(id):
# Check whether the user is an admin.
if session['isadmin']:
# Delete the event, and everything that is related to it in the database.
db_ref = db.get_db()
cursor = db_ref.cursor()
# Delete everything that is related.
cursor.execute("DELETE FROM Clearance WHERE Event_Id=%s;", (id,))
cursor.execute("DELETE FROM Event_Committee WHERE Event_Id=%s;", (id,))
cursor.execute("DELETE FROM Feedback WHERE Event_Id=%s;", (id,))
cursor.execute("DELETE FROM Guests WHERE Event_Id=%s;", (id,))
cursor.execute("DELETE FROM Expenses WHERE Event_Id=%s;", (id,))
cursor.execute("DELETE FROM Inventory WHERE Event_Id=%s;", (id,))
cursor.execute("DELETE FROM Income WHERE Event_Id=%s;", (id,))
# Finally, delete the events
cursor.execute("DELETE FROM Events WHERE Id=%s;", (id,))
# Commit the changes.
db_ref.commit()
return redirect(url_for("index.index"))
else:
# If user is not an admin then punish.
return render_template("nobueno.html")
def finance(id):
# Queries the database for necessary data to pass
cursor = db.get_db().cursor()
# First, we get the income
cursor.execute(
"SELECT Income_Date, Item_Name, Amount, Income_Type, Sponsor_Name, i.Id FROM Income i LEFT JOIN Sponsor s ON i.Sponsor_Id=s.Id WHERE Event_Id=%s;",
(id,)
)
# List of income
income_list = []
for data in cursor.fetchall():
income_list.append({
"id": data[5],
"date": data[0],
"name": data[1],
"amount": format_idr(data[2]),
"type": data[3],
"sponsor_name": data[4]
})
# Get the expense
cursor.execute(
"SELECT e.Id, e.Item_Name, Expense_Type, Amount, Expense_Date, e.Id FROM Expenses e JOIN Events ev ON ev.Id=e.Event_Id WHERE Event_Id=%s;",
(id,)
)
# And put to expense list
expense_list = []
for data in cursor.fetchall():
expense_list.append({
"id": data[5],
"date": data[4],
"name": data[1],
"amount": format_idr(data[3]),
"type": data[2]
})
# Get the sponsors
cursor.execute(
"SELECT Id, Sponsor_Name FROM Sponsor;"
)
sponsor_list = []
for data in cursor.fetchall():
sponsor_list.append({
"value": data[0],
"name": data[1]
})
return render_template("finance.html",
income_dict=income_list,
expense_dict=expense_list,
sponsor_dict=sponsor_list,
editPrivilege=session['clearance'].get(int(id), 1)=="3",
addPrivilege=session['clearance'].get(int(id), 1)=="3"
)
def members(id):
# Queries the database for necessary data to pass
cursor = db.get_db().cursor()
# First, we get the committee list
cursor.execute(
"SELECT Id, Full_Name, Member_Role, Clearance_Level, IsVolunteer FROM Members m LEFT JOIN Clearance c ON m.Id=c.Member_Id JOIN Event_Committee ec ON ec.Member_Id=m.Id WHERE c.Event_Id=%s AND ec.Event_id=%s;",
(id, id,)
)
# Committee list already includes volunteers, from the database.
committee_list = []
volunteer_list = []
for data in cursor.fetchall():
# Checks whether the member is a volunteer.
if not data[4]:
committee_list.append({
"id": data[0],
"name": data[1],
"position": data[2],
"clearance": data[3]
})
else:
volunteer_list.append({
"id": data[0],
"name": data[1],
"position": data[2],
"clearance": data[3]
})
# Get the guests
cursor.execute(
"SELECT Id, Full_Name, Category, Phone_Number, Email FROM Guests WHERE Event_Id=%s;",
(id,)
)
guest_list = []
for data in cursor.fetchall():
guest_list.append({
"id": data[0],
"name": data[1],
"category": data[2],
"Phone_Number": data[3],
"Email": data[4]
})
return render_template("members.html",
committee_dict=committee_list,
volunteer_dict=volunteer_list,
guest_dict=guest_list,
editPrivilege=session['clearance'].get(int(id), 1)=="3",
addPrivilege=session['clearance'].get(int(id), 1)=="3"
)
def event_data(id):
# Gets the total of the members
cursor = db.get_db().cursor()
cursor.execute(
"SELECT (SELECT COUNT(*) FROM Event_Committee WHERE Event_Id=%s) + (SELECT COUNT(*) FROM Guests WHERE Event_Id=%s) as total;", (
id,
id,
)
)
return render_template("event_data.html", total_member=cursor.fetchall()[0][0])
def update_privilege():
cursor = db.get_db().cursor()
cursor.execute("SELECT Event_Id, Clearance_Level FROM Clearance WHERE Member_Id=%s", (
session['member_id'],
))
# Gets the clearances
clearances = cursor.fetchall()
# And inserts it into the session variable
for c in clearances:
# The key is the id of the event, and the value is the clearance
session['clearance'][c[0]] = c[1]
def update_description(id):
""" POST method for updating the description """
try:
db_obj = db.get_db()
# Update datbase
cursor = db_obj.cursor()
cursor.execute('UPDATE Events SET Event_Desc=%s WHERE Id=%s;', (request.form['description'], id,))
# Commit
db_obj.commit()
return "1"
except Exception:
return "0"
def get_event_list():
# Gets the list of events the user have clearance to.
cursor = db.get_db().cursor()
cursor.execute("SELECT e.Id, Event_Name, Clearance_Level FROM Clearance c JOIN Events e ON c.Event_Id=e.Id WHERE c.Member_Id=%s;", (
session["member_id"],
))
events = []
# Gets all the events available for the user
for ev in cursor.fetchall():
# If the clearance is not 1, then show the event
if ev[2] != 1:
events.append({
"event_id": ev[0],
"event_name": ev[1]
})
return events
def upd_members(id):
# Updates the entry.
try:
db_obj = db.get_db()
# Update from database
cursor = db_obj.cursor()
# Updates the members
if request.form["activeTable"] == "0" or request.form["activeTable"] == "1":
# Committees
# Update the position first
cursor.execute("UPDATE Event_Committee SET Member_Role=%s WHERE Member_Id=%s AND Event_Id=%s;", (
request.form["Position"],
request.form["Id"],
id,
))
# Then, update the clearance
cursor.execute("UPDATE Clearance SET Clearance_Level=%s WHERE Member_Id=%s AND Event_Id=%s;", (
request.form["Clearance"],
request.form["Id"],
id,
))
elif request.form["activeTable"] == "2":
# Update the guests
print(request.form["Phone"])
cursor.execute("UPDATE Guests SET Full_Name=%s, Category=%s, Phone_Number=%s, Email=%s WHERE Id=%s AND Event_Id=%s;", (
request.form["Name"],
request.form["Position"],
request.form["Phone"],
request.form["Mail"],
request.form["Id"],
id,
))
db_obj.commit()
return "1"
except Exception as e:
print(str(e))
return "0"
def feedback(id):
# Queries the database for necessary data to pass
cursor = db.get_db().cursor()
# First, we get the committee list
cursor.execute(
"SELECT * FROM Feedback WHERE Event_ID=%s;",
(id,)
)
f_list = []
for data in cursor.fetchall():
f_list.append({
"rating": data[1],
"feedback_text": data[2]
})
return render_template("feedback.html", parent_list=f_list)
def profile_page(id):
# TODO: Add /user/<id>/profile
cursor = db.get_db().cursor()
cursor.execute(
"SELECT Full_Name, Position, Username, Email FROM Members m LEFT JOIN Login_Cred l ON l.Member_Id=m.Id WHERE m.Id=%s;",
(id, ))
fetch = cursor.fetchall()
# Check whether the member exists
if not fetch:
return render_template("404.html")
cursor.execute(
"""
SELECT c.Member_Id, c.Event_Id, c.Clearance_Level, IsVolunteer, Member_Role, e.Event_name, e.Venue, e.Event_Desc
FROM Event_Committee ec
RIGHT JOIN Clearance c
ON ec.Member_Id=c.Member_Id AND ec.Event_Id=c.Event_Id
JOIN Events e ON c.Event_Id=e.Id
WHERE c.Member_Id=%s;
""", (id, ))
event_dict = []
# Get all the data necessary
for e in cursor.fetchall():
event_dict.append({
"event_name": e[5],
"event_description": e[7],
"event_id": e[1],
"clearance": e[2],
"role": e[4],
"venue": e[6]
})
return render_template("profile.html",
fullname=fetch[0][0],
position=fetch[0][1],
email=fetch[0][3],
username=fetch[0][2],
event_dict=event_dict)
def add_finance(id):
""" POST method for updating the description """
try:
db_obj = db.get_db()
# Update datbase
cursor = db_obj.cursor()
# Depending on the table selected (Income / Expense), insert the data.
if request.form['ActiveTable'] == "0":
# Get the sponsor ID from the database
sponsor_id = None
if request.form['Sponsor'] != "0":
sponsor_id = request.form['Sponsor']
# Then, we can insert into income
cursor.execute('Insert INTO Income (Income_Type, Item_Name, Amount, Income_Date, Event_Id, Sponsor_Id) VALUES (%s, %s, %s, %s, %s, %s);', (
request.form['Type'],
request.form['Name'],
request.form['Cost'],
request.form['Date'],
id,
sponsor_id,
))
elif request.form['ActiveTable'] == "1":
cursor.execute('Insert INTO Expenses (Expense_Type, Item_Name, Amount, Expense_Date, Event_Id) VALUES (%s, %s, %s, %s, %s);', (
request.form['Type'],
request.form['Name'],
request.form['Cost'],
request.form['Date'],
id,
))
# Commit
db_obj.commit()
return "1"
except Exception as e:
print(str(e))
return "0"
def login():
msg = ''
# indicate the desired action to be performed for a given resource.
if request.method == 'POST':
email = request.form['mail']
password = request.form['pwd']
cursor = db.get_db().cursor()
# Gets the email and password pair from the database.
cursor.execute('SELECT * FROM Login_Cred WHERE Email = %s ', (email, ))
#method returns a single record or None if no more rows are available.
users = cursor.fetchall()
if users:
# Gets the first row
users = users[0]
# Sets the internal session variables
session['loggedin'] = True
session['id'] = users[0]
session['email'] = users[2]
session['user_name'] = users[3]
session['isadmin'] = users[4]
session['member_id'] = users[5]
# Sets an empty dictionary of clearances
session['clearance'] = {}
msg = 'Logged in successfully !'
pass_hash = users[1]
if check_password_hash(pass_hash,
password) and request.form['mail'] != "":
return redirect(url_for('index.index'))
else:
return redirect(url_for('user.login'))
else:
return redirect(url_for('user.login'))
else:
return render_template('login.html')
def forms():
""" This route is a REST API that can be integrated to receive form data.
The integration on Google App Script allows all the feedback form for events
To be sent and received here. """
# Get the POSTed data into a variable
raw_data = request.get_data().decode("utf-8")
# If the data posted begins with the prefix, then we can continue with decryption
# because it's not garbage data.
prefix = current_app.config.get("G_FORM_PREFIX")
if raw_data.startswith(prefix):
try:
# Try to decrypt the data
decrypted = aes.decrypt(
raw_data[len(prefix):],
current_app.config.get("G_FORM_SECRET").encode())
# Parse the json data
json_data = json.loads(decrypted)
# Get the data, and put it into the database
cursor = db.get_db().cursor()
cursor.execute(
"INSERT INTO Feedback (Event_Id, Rating, Comments) VALUES (%s, %s, %s)",
(
json_data["event_id"],
json_data["rating"],
json_data["comment"],
))
return "1"
except (AssertionError, json.JSONDecodeError, KeyError,
mysql.connector.Error) as e:
# Error loading the data or inserting
pass
# Return 0 if data cannot be decrypted and inserted
return "0"
def new_all_members():
try:
if request.form["ActiveTable"] == "0" and session['isadmin']:
db_obj = db.get_db()
cursor = db_obj.cursor()
# Insert the members table first,
cursor.execute('INSERT INTO Members (Full_Name, Position) VALUES (%s, %s);', (
request.form['Name'],
request.form['Position'],
))
# Commit
db_obj.commit()
return "1"
except Exception as e:
print(str(e))
pass
return "0"
def all_members():
# Gets all the members with the login cred.
cursor = db.get_db().cursor()
cursor.execute("SELECT m.Id, Full_Name, Position, Email, IsAdmin FROM Members m LEFT JOIN Login_Cred l ON m.Id=l.Member_Id;")
all_member = []
for x in cursor.fetchall():
all_member.append({
"id": x[0],
"name": x[1],
"position": x[2],
"mail": x[3],
"admin": "True" if x[4] == 1 else "False"
})
return render_template("all_member.html",
editPrivilege=session['isadmin'],
addPrivilege=session['isadmin'],
event_dict=get_event_list(),
all_member_dict=all_member
)
def upd_finance(id):
# Updates the entry.
try:
db_obj = db.get_db()
# Update from database
cursor = db_obj.cursor()
# Updates the income or expense
if request.form["activeTable"] == "0":
# Gets the sponsor
sponsor_id = request.form["Sponsor"] if request.form["Sponsor"] != "0" else None
cursor.execute("UPDATE Income SET Income_Date=%s, Item_Name=%s, Amount=%s, Sponsor_Id=%s, Income_Type=%s WHERE Id=%s;", (
request.form["Date"],
request.form["Name"],
request.form["Cost"],
sponsor_id,
request.form["Type"],
request.form["Id"],
))
elif request.form["activeTable"] == "1":
cursor.execute("UPDATE Expenses SET Expense_Date=%s, Item_Name=%s, Amount=%s, Expense_Type=%s WHERE Id=%s;", (
request.form["Date"],
request.form["Name"],
request.form["Cost"],
request.form["Type"],
request.form["Id"],
))
db_obj.commit()
return "1"
except Exception as e:
print(str(e))
return "0"
def wrapped(id):
# First, we check if the string is a number first. If it's not, don't bother checking.
if not id.isnumeric():
return render_template("404.html")
# Second, we check whether the event exists in the database.
cursor = db.get_db().cursor()
cursor.execute('SELECT Id, Event_Name FROM Events WHERE Id=%s', (id,))
fetch = cursor.fetchall()
if fetch:
# If the event is found,
# Check whether the user can view the event.
# If the user does not have any clearance in the database,
# then the default is clearance 1
if session['clearance'].get(int(id), 1) != "1":
g.event_name = fetch[0][1]
g.event_id = fetch[0][0]
return func(id)
# If none of the conditions is met, then return a 404
return render_template("404.html")
def del_admin():
try:
if request.form["ActiveTable"] == "0" and session['isadmin']:
db_obj = db.get_db()
cursor = db_obj.cursor()
# Delete all the referencing sponsor ID
cursor.execute('DELETE FROM Inventory WHERE Sponsor_Id=%s;', (request.form['Id'],))
cursor.execute('DELETE FROM Income WHERE Sponsor_Id=%s;', (request.form['Id'],))
# Finally, delete the sponsor.
cursor.execute('DELETE FROM Sponsor WHERE Id=%s;', (request.form['Id'],))
# Commit
db_obj.commit()
return "1"
except Exception as e:
print(str(e))
pass
return "0"
def inventory(id):
# Queries the database for necessary data to pass
cursor = db.get_db().cursor()
# First, we get the committee list
cursor.execute(
"SELECT Inventory_Id, Item_Name, Item_Quantity, Sponsor_Name FROM Inventory i LEFT JOIN Sponsor s ON i.Sponsor_Id=s.Id WHERE i.Event_ID=%s;",
(id,)
)
in_list = []
for data in cursor.fetchall():
in_list.append({
"id": data[0],
"name": data[1],
"amount": data[2],
"sponsor": data[3]
})
# Get the sponsors
cursor.execute(
"SELECT Id, Sponsor_Name FROM Sponsor;"
)
sponsor_list = []
for data in cursor.fetchall():
sponsor_list.append({
"value": data[0],
"name": data[1]
})
return render_template("inventory.html",
inventory_dict=in_list,
sponsor_dict=sponsor_list,
editPrivilege=session['clearance'].get(int(id), 1)=="3",
addPrivilege=session['clearance'].get(int(id), 1)=="3"
)
def upd_inventory(id):
# Updates the entry.
try:
db_obj = db.get_db()
# Update from database
cursor = db_obj.cursor()
# Updates the inventory
if request.form["activeTable"] == "0":
cursor.execute("UPDATE Inventory SET Item_Name=%s, Item_Quantity=%s, Sponsor_Id=%s WHERE Inventory_Id=%s;", (
request.form["Name"],
request.form["Amount"],
request.form["Sponsor"],
request.form["Id"],
))
db_obj.commit()
return "1"
except Exception as e:
print(str(e))
return "0"
