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()