def purchaseCustomer():
username = session['username']
cursor = conn.cursor()
airline_name = request.form['airline_name']
flight_num = request.form['flight_num']
# Find the number of tickets to generate the next ticket_id
queryCount = 'SELECT COUNT(*) as count FROM ticket \
WHERE ticket.airline_name = %s AND ticket.flight_num = %s'
cursor.execute(queryCount, (airline_name, flight_num))
ticketCount = cursor.fetchone()
ticketCountVal = 0
if ticketCount != None:
ticketCountVal = ticketCount['count']
# ticket_id = _genTix(ticketCountVal, airline_name.strip().replace(' ', ''), flight_num)
ticket_id = _genTix()
# print("WHAT F*****G NUMBER: ", ticket_id)
# Create the new ticket
queryNewTicket = 'INSERT INTO ticket VALUES(%s, %s, %s)'
cursor.execute(queryNewTicket, (ticket_id, airline_name, flight_num))
# Finalize the purchase
queryPurchase = 'INSERT INTO purchases VALUES(%s, %s, %s, CURDATE())'
cursor.execute(queryPurchase, (ticket_id, username, None))
data = cursor.fetchone()
conn.commit()
cursor.close()
return render_template('purchaseCustomer.html')
def getData(query, item):
cursor = conn.cursor()
cursor.execute(query, (item))
data = cursor.fetchone()
conn.commit()
cursor.close()
return data
def getData(query):
cursor = conn.cursor()
cursor.execute(query)
data = cursor.fetchall()
conn.commit()
cursor.close()
return (data)
def getInfo(username):
query = "SELECT * FROM profile WHERE username=%s"
cursor = conn.cursor()
cursor.execute(query, (username))
data = cursor.fetchone()
conn.commit()
cursor.close()
return(data)
def dislikePost(post_id):
if (not session.get('logged_in')):
return redirect(url_for('main'))
cursor = conn.cursor()
dislikePostQuery = 'DELETE FROM likes WHERE username_liker="' + session[
'username'] + '" AND id=' + post_id
cursor.execute(dislikePostQuery)
conn.commit()
cursor.close()
return redirect(url_for('main'))
def likePost(post_id):
if (not session.get('logged_in')):
return redirect(url_for('main'))
cursor = conn.cursor()
likePostQuery = 'INSERT INTO likes (id, username_liker) VALUES (' + post_id + ', "' + session[
'username'] + '")'
cursor.execute(likePostQuery)
conn.commit()
cursor.close()
return redirect(url_for('main'))
def registerProcessing():
# server-side validation
username = request.form['username']
if username in session['users'].keys():
errormsg = "Username already taken."
return render_template('register.html', error=errormsg)
if len(username) < 4:
errormsg = "Username is too short. Must be more than 3 characters."
return render_template('register.html', error=errormsg)
elif len(username) > 50:
errormsg = "Username and/or other fields are too long. 50 characters max."
return render_template('register.html', error=errormsg)
password = request.form['password']
if len(password) < 4:
errormsg = "Password is too short (needs to be greater than 3 characters)."
return render_template('register.html', error=errormsg)
elif len(password) > 50:
errormsg = "Password is too long. 50 characters max."
return render_template('register.html', error=errormsg)
retype = request.form['retype']
if retype != password:
errormsg = "Passwords do not match."
return render_template('register.html', error=errormsg)
# Add salt and hash password prior to inserting to DB.
salt = generate_random_salt(
) # base64 encoded random bytes, default len=64
password_hash = generate_password_hash(password, salt)
firstname = request.form['firstname']
lastname = request.form['lastname']
cursor = conn.cursor()
query = 'INSERT INTO person (username, password, salt, first_name, last_name) VALUES (%s, %s, %s, %s, %s)'
cursor.execute(query, (username, password_hash, salt, firstname, lastname))
conn.commit()
cursor.close()
query = "INSERT INTO profile (username, bio, file_path) VALUES (%s, '', '')"
cursor = conn.cursor()
cursor.execute(query, (username))
conn.commit()
cursor.close()
session['logged_in'] = True
session['username'] = username
session['users'][username] = {}
session['users'][username]['groups'] = []
session['users'][username]['first_name'] = firstname
session['users'][username]['last_name'] = lastname
return redirect(url_for('main', username=session['username']))
def editProfileProcessed(username):
if (not session.get('logged_in')):
return redirect(url_for('main'))
#Cannot edit another user's profile. Checks for that error.
if (session['username'] != username):
error = 'Cannot edit another user profile.'
return render_template('editProfile.html',
username=username,
error=error)
biography = request.form['bio']
img_filepath = '/static/posts_pic/'
data = getInfo(username)
if (allowed_file(request.files['photo'].filename) == False):
error = 'Please attach image files only.'
return render_template('editProfile.html',
username=username,
error=error,
data=data)
if len(biography) > 50:
error = 'Bio is too long. 50 characters max.'
return render_template('editProfile.html',
username=username,
error=error,
data=data)
if request.method == 'POST' and 'photo' in request.files:
filename = photos.save(request.files['photo'])
img_filepath = img_filepath + filename
# conducts queries to update post
cursor = conn.cursor()
updateQuery = 'UPDATE profile \
SET \
bio = %s, \
file_path = %s \
WHERE profile.username = %s'
cursor.execute(updateQuery, (biography, img_filepath, username))
conn.commit()
cursor.close()
return redirect(url_for('profile', username=username))
def registerProcessing():
username = request.form['username']
if username in session['users'].keys():
errormsg = "Username already taken."
return render_template('register.html', error = errormsg)
if len(username) < 4:
errormsg = "Username is too short. Must be more than 3 characters."
return render_template('register.html', error = errormsg)
elif len(username) > 50:
errormsg = "Username and/or other fields are too long. 50 characters max."
return render_template('register.html', error = errormsg)
password = request.form['password']
if len(password) < 4:
errormsg = "Password is too short (needs to be greater than 3 characters)."
return render_template('register.html', error = errormsg)
elif len(password) > 50:
errormsg = "Password is too long. 50 characters max."
return render_template('register.html', error = errormsg)
retype = request.form['retype']
if retype != password:
errormsg = "Passwords do not match."
return render_template('register.html', error = errormsg)
firstname = request.form['firstname']
lastname = request.form['lastname']
cursor = conn.cursor()
query = 'INSERT INTO person (username, password, first_name, last_name) VALUES (%s, md5(%s), %s, %s)'
cursor.execute(query, (username, password, firstname, lastname))
conn.commit()
cursor.close()
query = "INSERT INTO profile (username, bio, file_path) VALUES (%s, '', '')"
cursor = conn.cursor()
cursor.execute(query, (username))
conn.commit()
cursor.close()
session['logged_in'] = True
session['username'] = username
session['users'][username] = {}
session['users'][username]['groups'] = []
session['users'][username]['first_name'] = firstname
session['users'][username]['last_name'] = lastname
return redirect(url_for('main', username = session['username']))
def registerAuthAgent():
email = request.form['email']
password = request.form['password']
booking_agent_id = request.form['booking_agent_id']
cursor = conn.cursor()
query = 'SELECT * FROM booking_agent WHERE email = %s'
cursor.execute(query, (email))
data = cursor.fetchone()
error = None
if (data):
error = "This user already exists"
return render_template('registerAgent.html', error=error)
else:
ins = 'INSERT INTO booking_agent VALUES(%s, md5(%s), %s)'
cursor.execute(ins, (email, password, booking_agent_id))
conn.commit()
cursor.close()
return render_template('index.html')
def deletePost(post_id):
if (not session.get('logged_in')):
return redirect(url_for('main'))
userQuery = 'SELECT username FROM content WHERE id = %s'
user = getData(userQuery, post_id)
if (user['username'] != session['username']):
#return render_template('result.html', data=user['username'])
error = "This is not your post to delete!"
return redirect(url_for('main'))
else:
# check if post is in table
shareQuery = 'SELECT * FROM share WHERE id = %s'
data = getData(shareQuery, post_id)
if (data is not None):
delete = 'DELETE FROM share WHERE id = %s'
cursor = conn.cursor()
cursor.execute(delete, (post_id))
conn.commit() #commit the change to DB
cursor.close()
cursor = conn.cursor()
#two delete queries; must delete tag because foreign key constraint
delete = 'DELETE FROM tag WHERE tag.id=%s'
cursor.execute(delete, (post_id))
conn.commit() #commit the change to DB
delete = 'DELETE FROM likes WHERE likes.id=%s'
cursor.execute(delete, (post_id))
conn.commit() #commit the change to DB
delete = 'DELETE FROM comment WHERE comment.id=%s'
cursor.execute(delete, (post_id))
conn.commit() #commit the change to DB
delete = 'DELETE FROM content WHERE content.id=%s'
cursor.execute(delete, (post_id))
conn.commit() #commit the change to DB
cursor.close()
return redirect(url_for('main'))
def editPostProcessed(post_id):
if (not session.get('logged_in')):
return redirect(url_for('main'))
postContent = request.form['content']
pubOrPriv = request.form['publicity']
img_filepath = '/static/posts_pic/'
if not allowed_file(request.files['photo'].filename):
error = 'Please attach image files only.'
return render_template('content_edit.html',
post_id=post_id,
error=error)
if len(postContent) > 50:
error = 'Description is too long. 50 characters max.'
return render_template('content_edit.html',
post_id=post_id,
error=error)
if request.method == 'POST' and 'photo' in request.files:
filename = photos.save(request.files['photo'])
img_filepath = img_filepath + filename
# conducts queries to update post
cursor = conn.cursor()
updateQuery = 'UPDATE content \
SET \
file_path = %s, \
content_name = %s, \
public = %s, \
timest = CURRENT_TIMESTAMP \
WHERE content.id = %s'
cursor.execute(updateQuery,
(img_filepath, postContent, pubOrPriv, post_id))
conn.commit()
cursor.close()
return redirect(url_for('main'))
def creatingFriends():
if (not session.get('logged_in')):
return redirect(url_for('main'))
# get all the info from the form
groupName = request.form['name']
description = request.form["description"]
data = request.form
#check if group name too long
if len(groupName) > 50:
error = "Group name too long. 50 characters max."
return render_template('createFriend.html', error=error)
# check if group name exists
query = "SELECT COUNT(group_name) FROM friendgroup WHERE group_name = %s"
allGroups = getData(query, groupName)
if (allGroups[0]['COUNT(group_name)'] == 1):
error = "The group name already exists. Please enter another one."
return render_template('createFriend.html', error=error)
else:
cursor = conn.cursor()
command = "INSERT INTO friendgroup (group_name, username, description) VALUES (%s, %s, %s)"
cursor.execute(command, (groupName, session['username'], description))
conn.commit()
cursor.close()
# create a query for each member
cursor = conn.cursor()
stuff = []
exclude = ["name", "description"]
for member in data:
if (member not in exclude):
query = "INSERT INTO member (username, group_name, username_creator) VALUES (%s, %s, %s)"
cursor.execute(query, (member, groupName, session['username']))
conn.commit()
cursor.close()
return redirect(url_for('friends'))
def registerAuthStaff():
username = request.form['username']
password = request.form['password']
first_name = request.form['first_name']
last_name = request.form['last_name']
date_of_birth = request.form['date_of_birth']
airline_name = request.form['airline_name']
cursor = conn.cursor()
query = 'SELECT * FROM airline_staff WHERE username = %s'
cursor.execute(query, (username))
data = cursor.fetchone()
error = None
if (data):
error = "This user already exists"
return render_template('registerStaff.html', error=error)
else:
ins = 'INSERT INTO airline_staff VALUES(%s, md5(%s), %s, %s, %s, %s)'
cursor.execute(ins, (username, password, first_name, last_name,
date_of_birth, airline_name))
conn.commit()
cursor.close()
return render_template('index.html')
def registerAuthCustomer():
#grabs information from the forms
email = request.form['email']
name = request.form['name']
password = request.form['password']
building_number = request.form['building_number']
street = request.form['street']
city = request.form['city']
state = request.form['state']
phone_number = request.form['phone_number']
passport_number = request.form['passport_number']
passport_expiration = request.form['passport_expiration']
passport_country = request.form['passport_country']
date_of_birth = request.form['date_of_birth']
#cursor used to send queries
cursor = conn.cursor()
#executes query
query = 'SELECT * FROM customer WHERE email = %s'
cursor.execute(query, (email))
#stores the results in a variable
data = cursor.fetchone()
#use fetchall() if you are expecting more than 1 data row
error = None
if (data):
#If the previous query returns data, then user exists
error = "This user already exists"
return render_template('registerCustomer.html', error=error)
else:
ins = 'INSERT INTO customer VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
cursor.execute(ins,
(email, name, password, building_number, street, city,
state, phone_number, passport_number,
passport_expiration, passport_country, date_of_birth))
conn.commit()
cursor.close()
return render_template('index.html')
def purchaseAgent():
username = session['username']
customer_email = request.form['customer_email']
cursor = conn.cursor()
airline_name = request.form['airline_name']
flight_num = request.form['flight_num']
# Find the number of tickets to generate the next ticket_id
queryCount = 'SELECT COUNT(*) as count FROM ticket \
WHERE ticket.airline_name = %s AND ticket.flight_num = %s'
cursor.execute(queryCount, (airline_name, flight_num))
ticketCount = cursor.fetchone()
ticketCountVal = 0
if ticketCount != None:
ticketCountVal = ticketCount['count']
# ticket_id = _genTix(ticketCountVal, airline_name.strip().replace(' ', ''), flight_num)
ticket_id = _genTix()
# Create the new ticket
queryNewTicket = 'INSERT INTO ticket VALUES(%s, %s, %s)'
cursor.execute(queryNewTicket, (ticket_id, airline_name, flight_num))
# Get booking_agent_id
queryGetID = 'SELECT booking_agent_id FROM booking_agent WHERE email=%s'
cursor.execute(queryGetID, username)
agentID = cursor.fetchone() # returns a dict
# Finalize the purchase
queryPurchase = 'INSERT INTO purchases VALUES(%s, %s, %s, CURDATE())'
cursor.execute(queryPurchase, (ticket_id, customer_email, agentID['booking_agent_id']))
data = cursor.fetchone()
conn.commit()
cursor.close()
error = None
if(data):
return render_template('agent.html', results=data)
else:
#returns an error message to the html page
error = 'Cannot complete purchase'
return render_template('purchaseAgent.html', error=error)
def replyingPost(post_id):
if (not session.get('logged_in')):
return redirect(url_for('main'))
#check if post_id even exists, otherwise return error
cursor = conn.cursor()
query = 'SELECT COUNT(*) FROM content WHERE id = %s'
cursor.execute(query, (post_id))
countData = cursor.fetchone()
cursor.close()
if (countData['COUNT(*)'] <= 0):
error = "Post does not exist. Please comment on a valid post."
return render_template("reply_post.html", post_id=post_id, error=error)
content = request.form['description']
time = datetime.datetime.now().strftime('%y-%m-%d %H:%M:%S')
query = 'INSERT INTO comment (id, username, timest, comment_text) VALUES (%s, %s, %s, %s)'
cursor = conn.cursor()
cursor.execute(query, (post_id, session['username'], time, content))
data = conn.commit()
cursor.close()
return redirect(url_for('main'))
def execute(query, param):
cursor = conn.cursor()
cursor.execute(query, (param))
conn.commit()
cursor.close()
return;
def makePostProcessed():
if (not session.get('logged_in')):
return redirect(url_for('main'))
content_name = request.form['content_name']
public = request.form['public']
friendgroup = False
if (request.form['friend_group_name']):
friendgroup = request.form['friend_group_name']
img_filepath = '/static/posts_pic/'
if not allowed_file(request.files['photo'].filename):
error = 'Please attach image files only.'
return render_template('makePost.html', error=error)
if request.method == 'POST' and 'photo' in request.files:
filename = photos.save(request.files['photo'])
img_filepath = img_filepath + filename
if len(content_name) > 50:
error = 'Description is too long. 50 characters max.'
return render_template('makePost.html', error=error)
#checks if friendgroup form field goes over the defined size
if (friendgroup):
if (len(friendgroup) > 50):
error = 'Friendgroup is too long. 50 characters max.'
return render_template('makePost.html', error=error)
# checks if group exists
query = 'SELECT group_name FROM friendgroup'
groups = getData(query)
present = False
for group in groups:
if (group['group_name'] == request.form['friend_group_name']):
present = True
if (present == False and public == '0'):
error = "Group does not exist."
return render_template('makePost.html', error=error)
username = session['username']
cursor = conn.cursor()
timest = datetime.datetime.now().strftime('%y-%m-%d %H:%M:%S')
query = 'SELECT max(id) as postID FROM Content' #to get the id of this post
cursor.execute(query)
postID = cursor.fetchone()['postID'] # + 1
if (postID is None):
postID = 1
else:
postID += 1
#If the content item is private, PriCoSha gives the user a way to designate
#FriendGroups (that the user owns) with which the Photo is shared.
if (public == '1'):
query = 'INSERT into Content (id, username, timest, file_path, content_name, public) values (%s, %s, %s, %s, %s, %s)'
cursor.execute(query, (postID, username, timest, img_filepath, content_name, public))
if (public == '0'): #need to know which friendgroup to share it with if not public
group_name = request.form['friend_group_name']
#this is for if the poster is attempting to share a post to a group they are not in
query = '(SELECT username FROM member WHERE group_name = %s) UNION\
(SELECT username FROM friendgroup WHERE group_name = %s)'
cursor.execute(query, (group_name, group_name))
listPeople = cursor.fetchall() #list of people who can see that post
flag = False
for mem in listPeople:
if mem['username'] == username:
flag = True
if flag == False:
error = "You cannot post to this group."
return render_template('makePost.html', error=error)
query = 'INSERT into Content (id, username, timest, file_path, content_name, public) values (%s, %s, %s, %s, %s, %s)'
cursor.execute(query, (postID, username, timest, img_filepath, content_name, public))
query = 'INSERT into share (id, group_name, username) values (%s, %s, %s)'
cursor.execute(query, (postID, group_name, username))
conn.commit()
cursor.close()
return redirect(url_for('main'))
def tagUserProcessed(post_id):
username_taggee = request.form['username_taggee']
if (username_taggee):
if (len(username_taggee) > 50):
error = 'Name is too long. 50 characters max.'
return render_template('tagUser.html', post_id=post_id, error=error)
query = "SELECT username \
FROM person \
WHERE username = %s"
cursor = conn.cursor()
cursor.execute(query, (username_taggee))
data = cursor.fetchall()
if (len(data) < 1):
error = "User not found."
return render_template('tagUser.html', post_id=post_id, error=error) #check
username_tagger = session['username']
#gets all the ids of the visible posts to the taggee
query = 'SELECT content.id\
FROM content\
WHERE content.public = 1\
OR content.username= %s\
OR id in\
(SELECT share.id\
FROM share\
WHERE %s in\
(SELECT member.username\
FROM member\
WHERE share.group_name = member.group_name)\
OR %s in (SELECT username\
FROM friendgroup\
WHERE share.group_name = friendgroup.group_name))'
cursor.execute(query, (username_taggee, username_taggee, username_taggee))
visiblePosts = cursor.fetchall() #posts shared to the groups this person is in
flag = False
for mem in visiblePosts:
if mem['id'] == int(post_id):
flag = True
if not flag:
errormsg = "Cannot tag: post is not visible to this person or s/he doesn't exist!"
return render_template('tagUser.html', post_id=post_id, error=errormsg)
#checks if tag is a duplicate
queryDuplicate = 'SELECT * FROM tag WHERE id = %s AND username_taggee = %s'
cursor.execute(queryDuplicate, (post_id, username_taggee))
duplicate = cursor.fetchone()
#return render_template('result.html', data=duplicate)
if duplicate:
error = "Cannot tag this person: this tag already exists or is pending."
return render_template('tagUser.html', post_id=post_id, error=error)
timest = datetime.datetime.now().strftime('%y-%m-%d %H:%M:%S')
query = 'INSERT into tag (id, username_tagger, username_taggee, timest, status) values (%s, %s, %s, %s, %s)'
#if user is tagging themselves
if username_taggee == username_tagger:
cursor.execute(query, (post_id, username_tagger, username_taggee, timest, 1))
elif username_taggee != username_tagger:
cursor.execute(query, (post_id, username_tagger, username_taggee, timest, 0))
conn.commit()
cursor.close()
return redirect(url_for('main'))
def addingFriends():
if (not session.get('logged_in')):
return redirect(url_for('main'))
groupQuery = 'SELECT group_name FROM `friendgroup` WHERE username = %s'
group = getData(groupQuery, session['username'])
#checks if user owns any groups, else error
countGroupQuery = 'SELECT COUNT(*) FROM friendgroup WHERE username = %s'
countGroupData = getData(countGroupQuery, session['username'])
if (countGroupData[0]['COUNT(*)'] < 1):
error = "Did not select a friendgroup first, or incorrect name."
return render_template("addFriends.html", data=group, error=error)
if (len(group) == 0):
error = "Please include a group name or a user's name"
return render_template('addFriends.html', error=error, data=group)
# creating variables from the form
formGroup = request.form['group']
fullname = request.form['name']
first_name = ""
last_name = ""
# checks if username field is filled
# username field is filled only if there
# are two people with the same first and last name
username = request.form.get('username', None)
# if user entered a proper first name and last name
if len(fullname.split()) == 2:
first_name = fullname.split()[0]
last_name = fullname.split()[1]
else:
error = "Please enter a first name and a last name."
return render_template('addFriends.html', error=error, data=group)
# if the username parameter is not filled, check for the username
# with the person's first and last name
if (username is None):
# finding username with the entered first and last name
cursor = conn.cursor()
query = "SELECT username \
FROM person \
WHERE first_name = %s \
AND last_name = %s"
cursor.execute(query, (first_name, last_name))
userData = cursor.fetchall()
cursor.close()
# if there are multiple users with the same first and last name
if (len(userData) > 1):
error = "Please include a username."
return render_template('addFriends.html', error=error, data=group)
# if the user cannot be found, send an error message
elif (len(userData) < 1):
error = "User not found."
return render_template('addFriends.html', error=error, data=group)
isOwner = False
cursor = conn.cursor()
query = "SELECT username \
FROM friendgroup \
WHERE username = %s "
cursor.execute(query, (userData[0]['username']))
ownerData = cursor.fetchall()
cursor.close()
for data in ownerData:
if data['username'] == userData[0]['username']:
isOwner = True
isMember = False
cursor = conn.cursor()
query = "SELECT username \
FROM member \
WHERE username = %s "
cursor.execute(query, (userData[0]['username']))
memberData = cursor.fetchall()
cursor.close()
for data in memberData:
if data['username'] == userData[0]['username']:
isMember = True
#if user already in the group or is group owner, send error message
if (isOwner):
error = "Already the group owner or a group member."
return render_template('addFriends.html', error=error, data=group)
elif (isMember):
error = "Already a group member or the group owner."
return render_template('addFriends.html', error=error, data=group)
else:
query = "INSERT INTO member (username, group_name, username_creator) VALUES (%s, %s, %s)"
cursor = conn.cursor()
cursor.execute(
query,
(userData[0]['username'], formGroup, session['username']))
conn.commit()
cursor.close()
return redirect(url_for('friends'))
else:
cursor = conn.cursor()
query = "SELECT username \
FROM person \
WHERE username = %s"
cursor.execute(query, (username))
data = cursor.fetchone()
cursor.close()
# if the username is collected
if (data):
data = group
for mem in group:
if (mem['group_name'] == formGroup):
error = "This person is already in the group!"
return render_template('addFriends.html',
error=error,
data=group)
query = "INSERT INTO member (username, group_name, username_creator) VALUES (%s, %s, %s)"
cursor = conn.cursor()
cursor.execute(query,
(data['username'], formGroup, session['username']))
conn.commit()
cursor.close()
return redirect(url_for('friends'))
else:
error = "Username was not found. Please enter a valid one."
return render_template('addFriends.html', error=error, data=group)
return render_template('addFriends.html')
def incoming_sms():
resp = MessagingResponse()
resp_message = ""
if request.method == 'POST':
message_body = request.values.get('Body', None)
message_body = message_body.encode('utf8')
if message_body.upper() == 'IDEA':
resp_message = "Welcome! We're excited bout your idea! Let's get started. Please type your EID and begin with 'EID: '"
elif "EID: " in message_body.upper():
message = message_body.split()
employee_id = str(message[1])
query = "SELECT * FROM Employee WHERE EMPLID='" + employee_id + "'"
cursor = conn.cursor()
cursor.execute(query)
user = cursor.fetchall()
cursor.close()
C["cookie_emplid"] = employee_id
if (len(user) == 1):
resp_message = "Thanks! From 1-5, how urgent is your idea? 1 being the least urgent."
else:
resp_message = "Please enter a valid Blue Apron ID."
elif message_body in urgency:
C["cookie_urgency"] = int(message_body)
resp_message = "Got it. In a sentence, please describe your idea. 'My idea is...'"
elif "MY IDEA IS" in message_body.upper():
C["cookie_idea"] = message_body
resp_message = "Last part: How did you come up with your idea and why is it worth pursuing? 'I came up with this idea because...'"
elif "I CAME UP WITH THIS IDEA BECAUSE" in message_body.upper():
C["cookie_why"] = message_body
resp_message = "Thank you for using Matter Bot. Please send 'Done' when you're finished and have a great day!"
else:
# Find department of the employee
cursor_one = conn.cursor()
dept_query = "SELECT department FROM Employee WHERE EMPLID ='" + C[
"cookie_emplid"].value + "'"
cursor_one.execute(dept_query)
dept = cursor_one.fetchone()
cursor_one.close()
# Find current ticket reviewer
cursor_three = conn.cursor()
reviewer_query = "SELECT EMPLID FROM Employee WHERE department ='" + dept[
'department'].encode('utf8') + "' AND title = 'MANAGER'"
cursor_three.execute(reviewer_query)
reviewer = cursor_three.fetchone()
cursor_three.close()
# storing the ticket into the DB
cursor_two = conn.cursor()
query = 'INSERT INTO ticket (idea, why, urgency, date_created, person_in_charge) VALUES (%s, %s, %s, %s, %s);'
time = datetime.now()
cursor_two.execute(
query,
(C["cookie_idea"].value, C["cookie_why"].value,
int(C["cookie_urgency"].value), time, reviewer['EMPLID']))
conn.commit()
cursor_two.close()
# getting the tracking id to insert to google sheets later
cursor_four = conn.cursor()
trackingNo_query = "SELECT ticket_id FROM ticket WHERE IDEA ='" + C[
"cookie_idea"].value + "'"
cursor_four.execute(trackingNo_query)
trackingNo = cursor_four.fetchone()
cursor_four.close()
# insert into google sheets
insert(C["cookie_emplid"].value, C["cookie_urgency"].value,
C["cookie_idea"].value, C["cookie_why"].value,
trackingNo['ticket_id'])
# slack integration
channel_id = findDept(dept["department"])
# find person name
cursor_five = conn.cursor()
emp_query = "SELECT employee_name FROM Employee WHERE EMPLID ='" + C[
"cookie_emplid"].value + "'"
cursor_five.execute(emp_query)
emp_name = cursor_five.fetchone()
cursor_five.close()
# sending message to slack
send_message(channel_id, "Department " + channel_id,
C["cookie_emplid"].value,
int(C["cookie_urgency"].value),
emp_name["employee_name"], C["cookie_idea"].value,
C["cookie_why"].value)
resp.message(resp_message)
return str(resp)
PRIMARY KEY(EMPLID) \
);"
ursor_two.execute(query)
conn.commit()
ursor_two.close()
ursor_two = conn.cursor()
query = "CREATE TABLE Ticket ( \
ticket_id INT NOT NULL AUTO_INCREMENT, \
idea TEXT, \
why TEXT, \
urgency INT, \
resolution VARCHAR(255), \
person_in_charge INT DEFAULT 0, \
date_created DATETIME, \
PRIMARY KEY (ticket_id), \
FOREIGN KEY (person_in_charge) REFERENCES Employee(EMPLID) \
);"
ursor_two.execute(query)
conn.commit()
ursor_two.close()
"""
ursor_two = conn.cursor()
query = "INSERT INTO Employee (EMPLID, title, employee_name, department, groupname) VALUES ('12346', 'SPECIALIST', 'PEANUT BUTTER', 'FSQA', '1'), ('12347', 'SPECIALIST', 'SOUP DUMPLING', 'FSQA', '1'), ('12348', 'SPECIALIST', 'FRENCH FRIES', 'FSQA', '1'), ('12349', 'MANAGER', 'Spring Onion', 'FSQA', '1'), ('23456', 'RUNNER', 'BLUE CHEESE', 'KITCHEN', '2'), ('23457', 'RUNNER', 'BEEF JERKY', 'KITCHEN', '2'), ('23458', 'RUNNER', 'BITTER GOURD', 'KITCHEN', '2'), ('23459', 'RUNNER', 'BITTER MELON', 'KITCHEN', '2'), ('23450', 'MANAGER', 'BOK CHOY', 'KITCHEN', '2'), ('34567', 'CUSTODIAN', 'BUBBLE TEA', 'PACKING', '3'), ('34568', 'CUSTODIAN', 'POPCORN CHICKEN', 'PACKING', '3'), ('34569', 'CUSTODIAN', 'MANGO SLUSH', 'PACKING', '3'), ('34560', 'CUSTODIAN', 'APPLE PIE', 'PACKING', '3'), ('34561', 'MANAGER', 'CREAM CHEESE', 'PACKING', '3'), ('45678', 'QA ASSOCIATE', 'BANANA SPLIT', 'SANITATION', '4'), ('45679', 'QA ASSOCIATE', 'FRIED COKE', 'SANITATION', '4'), ('45670', 'QA ASSOCIATE', 'ROAST PORK', 'SANITATION', '4'), ('45671', 'QA ASSOCIATE', 'PASTA PRIMAVERA', 'SANITATION', '4'), ('45672', 'MANAGER', 'RIDGE GOURD', 'SANITATION', '4'), ('56789', 'TECH', 'APPLE CRISP', 'SHIPPING', '5'), ('56780', 'TECH', 'SPICY SHRIMP', 'SHIPPING', '5'), ('56781', 'TECH', 'BEEF BURGER', 'SHIPPING', '5'), ('56782', 'TECH', 'MISO-HONEY SALMON', 'SHIPPING', '5'), ('56783', 'MANAGER', 'TAHINI CHICKEN', 'SHIPPING', '5');"
ursor_two.execute(query)
conn.commit()
ursor_two.close()
