def editPost(post_id): if (not session.get('logged_in')): return redirect(url_for('main')) cursor = conn.cursor() query = 'SELECT * FROM content WHERE id = %s' cursor.execute(query, (post_id)) data = cursor.fetchall() cursor.close() #checks if there is a post with the given post_id, spit out error otherwise cursor = conn.cursor() editCountQuery = 'SELECT COUNT(*) FROM content WHERE id = %s' cursor.execute(editCountQuery, (post_id)) countData = cursor.fetchone() cursor.close() if (countData['COUNT(*)'] > 0): return render_template("content_edit.html", post_id=post_id, data=data, countData=countData) else: editError = "Post does not exist. Please edit a valid post." return render_template("content_edit.html", post_id=post_id, data=data, editError=editError)
def replyPost(post_id): if (not session.get('logged_in')): return redirect(url_for('main')) query = 'SELECT * \ FROM content \ WHERE id =%s' cursor = conn.cursor() cursor.execute(query, (post_id)) data = cursor.fetchall() cursor.close() #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): return render_template("reply_post.html", post_id=post_id, countData=countData, data=data) else: error = "Post does not exist. Please comment on a valid post." return render_template("reply_post.html", post_id=post_id, error=error, data=data)
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 getData(query): cursor = conn.cursor() cursor.execute(query) data = cursor.fetchall() conn.commit() cursor.close() return (data)
def loginAuth(): #grabs information from the forms username = request.form['username'] password = request.form['password'] #usrtype = request.form['usrtype'] #cursor used to send queries cursor = conn.cursor() #executes query query = 'SELECT * FROM tbl_user WHERE `user_username` = %s and `user_password` = md5(%s)' cursor.execute(query, (username, password)) #stores the results in a variable data = cursor.fetchone() #use fetchall() if you are expecting more than 1 data row cursor.close() error = None if (data): #creates a session for the the user #session is a built in session['username'] = username return redirect(url_for('index')) # if usrtype == 'staff': # return redirect(url_for('staffHome')) # elif usrtype == 'customer': # return redirect(url_for('customerHome')) # else: # return redirect(url_for('agentHome')) else: #returns an error message to the html page error = 'Invalid login or username' return render_template('login.html', error=error)
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 getFriend(): userList = [] # query for getting the members of the group of # which the username is creator of creatorQuery = "SELECT username, group_name \ FROM member \ WHERE username_creator = %s;" cursor = conn.cursor() cursor.execute(creatorQuery, (session['username'])) userList.extend(cursor.fetchall()) cursor.close() # query for getting the members of the group of # which the username is a member of cursor = conn.cursor() memberQuery = "SELECT username, group_name \ FROM member \ WHERE group_name in \ (SELECT group_name \ FROM member \ WHERE username = %s) \ HAVING username != %s;" cursor.execute(memberQuery, (session['username'], session['username'])) userList.extend(cursor.fetchall()) cursor.close() # query for getting the creators of the group of # which the user is a member of friendCreatorQuery = "SELECT username_creator, group_name \ FROM member \ WHERE group_name in \ (SELECT group_name \ FROM member \ WHERE username = %s) \ GROUP BY group_name;" cursor = conn.cursor() cursor.execute(friendCreatorQuery, (session['username'])) userList.extend(cursor.fetchall()) cursor.close() return userList
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 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 sharePosts(): if (not session.get('logged_in')): return redirect(url_for('main')) query = "SELECT * FROM content WHERE username=%s" cursor = conn.cursor() cursor.execute(query, (session['username'])) data = cursor.fetchall() cursor.close() return render_template('sharePosts.html', data=data)
def addGroups(groupList): friendGroup = "SELECT group_name, description \ FROM friendgroup \ WHERE username = %s" cursor = conn.cursor() cursor.execute(friendGroup, (session['username'])) groupList.extend(cursor.fetchall()) cursor.close()
def _genTix(): cursor = conn.cursor() cand = random.randint(1, 2147483647) query = 'SELECT ticket_id FROM ticket' cursor.execute(query) allTix = cursor.fetchall() cursor.close() while cand in allTix: cand = random.randint(1, 2147483647) return cand
def addFriends(): if (not session.get('logged_in')): return redirect(url_for('main')) cursor = conn.cursor() groupQuery = 'SELECT * FROM `friendgroup` WHERE username = %s' cursor.execute(groupQuery, session['username']) group = cursor.fetchall() cursor.close() return render_template('addFriends.html', data=group)
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 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 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 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 tags(): if (not session.get('logged_in')): return redirect(url_for('main')) # making an sql query to obtain tag requests that the user has yet # to approve (when status = 0) cursor = conn.cursor() query = 'SELECT * FROM tag WHERE username_taggee = %s and status = 0' cursor.execute(query, (session['username'])) dataTwo = cursor.fetchall() cursor.close() request_id = {} for item in dataTwo: post_id = int(item['id']) cursor = conn.cursor() query = 'SELECT file_path FROM content WHERE id = %s' cursor.execute(query, (post_id)) data = cursor.fetchall() cursor.close() request_id[post_id] = "" request_id[post_id] = data[0]['file_path'] return render_template("tags.html", data=dataTwo, request_id=request_id)
def customerHome(): username = session['username'] cursor = conn.cursor() query = 'SELECT purchases.ticket_id, ticket.airline_name, ticket.flight_num, departure_airport, departure_time, arrival_airport, arrival_time \ FROM purchases, ticket, flight \ WHERE purchases.ticket_id = ticket.ticket_id \ AND ticket.airline_name = flight.airline_name \ AND ticket.flight_num = flight.flight_num \ AND customer_email = %s AND departure_time > curdate()' cursor.execute(query, (username)) data = cursor.fetchall() cursor.close() return render_template('customer.html', username=username, posts=data)
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 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 agentHome(): username = session['username'] cursor = conn.cursor(); query = 'SELECT * \ FROM purchases, ticket, flight, booking_agent \ WHERE purchases.ticket_id = ticket.ticket_id \ AND ticket.airline_name = flight.airline_name \ AND ticket.flight_num = flight.flight_num \ AND booking_agent.email = %s AND booking_agent.booking_agent_id = purchases.booking_agent_id \ AND departure_time > curdate() \ ORDER BY customer_email' cursor.execute(query, (username)) data = cursor.fetchall() # Get booking_agent_id queryGetID = 'SELECT booking_agent_id FROM booking_agent WHERE email=%s' cursor.execute(queryGetID, username) agentID = cursor.fetchone() # Get total commsion in the past 30 days queryGetCommission = 'SELECT sum(price)*.10 as totalComm FROM purchases, ticket, flight \ WHERE purchases.ticket_id = ticket.ticket_id \ AND ticket.airline_name = flight.airline_name AND ticket.flight_num = flight.flight_num \ AND purchases.purchase_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND CURDATE() \ AND purchases.booking_agent_id = %s' cursor.execute(queryGetCommission, agentID['booking_agent_id']) totalComm = cursor.fetchone() totalCommVal = 0 if totalComm['totalComm'] != None: totalCommVal = totalComm['totalComm'] # print totalComm # Get total tickets in the past 30 days queryGetTicketCount = 'SELECT count(*) as ticketCount FROM purchases, ticket, flight \ WHERE purchases.ticket_id = ticket.ticket_id \ AND ticket.airline_name = flight.airline_name AND ticket.flight_num = flight.flight_num \ AND purchases.purchase_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND CURDATE() \ AND purchases.booking_agent_id = %s' cursor.execute(queryGetTicketCount, agentID['booking_agent_id']) ticketCount = cursor.fetchone() ticketCountVal = ticketCount['ticketCount'] avgComm = 0 # print ticketCount, totalCommVal if ticketCountVal != 0: avgComm = totalCommVal/ticketCountVal cursor.close() return render_template('agent.html', username=username, posts=data, totalComm=totalCommVal, avgComm=avgComm, ticketCount=ticketCountVal)
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 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 searchPurchaseAgent(): cursor = conn.cursor() fromcity = request.form['fromcity'] fromairport = request.form['fromairport'] fromdate = request.form['fromdate'] tocity = request.form['tocity'] toairport = request.form['toairport'] todate = request.form['todate'] query = 'SELECT distinct f.airline_name, f.flight_num, departure_airport, departure_time, arrival_airport, arrival_time, price, airplane_id \ FROM flight as f, airport \ WHERE airport.airport_name=f.departure_airport \ AND airport.airport_city = %s \ AND airport.airport_name = %s \ AND %s BETWEEN DATE_SUB(f.departure_time, INTERVAL 2 DAY) AND DATE_ADD(f.departure_time, INTERVAL 2 DAY)\ AND %s BETWEEN DATE_SUB(f.arrival_time, INTERVAL 2 DAY) AND DATE_ADD(f.arrival_time, INTERVAL 2 DAY)\ AND (f.airline_name, f.flight_num) in \ (SELECT flight.airline_name, flight.flight_num FROM flight, airport \ WHERE airport.airport_name=flight.arrival_airport \ AND airport.airport_city = %s \ AND airport.airport_name = %s) \ AND (SELECT DISTINCT seats \ FROM flight, airplane \ WHERE flight.airplane_id = airplane.airplane_id AND flight.airline_name = airplane.airline_name \ AND flight.airline_name = f.airline_name AND flight.flight_num = f.flight_num) \ >= (SELECT COUNT(*) \ FROM ticket \ WHERE ticket.airline_name = f.airline_name AND ticket.flight_num = f.flight_num)' cursor.execute(query, (fromcity, fromairport, fromdate, todate, tocity, toairport)) # print cursor._executed data = cursor.fetchall() cursor.close() error = None if(data): print(data) return render_template('purchaseAgent.html', results=data) else: #returns an error message to the html page error = 'No results found' return render_template('purchaseAgent.html', searchError=error)
def searchAgent(): username = session['username'] cursor = conn.cursor() fromcity = request.form['fromcity'] fromairport = request.form['fromairport'] fromdate = request.form['fromdate'] tocity = request.form['tocity'] toairport = request.form['toairport'] todate = request.form['todate'] # Get booking_agent_id queryGetID = 'SELECT booking_agent_id FROM booking_agent WHERE email=%s' cursor.execute(queryGetID, username) agentID = cursor.fetchone()['booking_agent_id'] # Main query query = 'SELECT * FROM flight, airport, purchases, ticket \ WHERE airport.airport_name=flight.departure_airport \ AND flight.flight_num = ticket.flight_num AND flight.airline_name = ticket.airline_name\ AND ticket.ticket_id = purchases.ticket_id\ AND purchases.booking_agent_id = %s\ AND airport.airport_city = %s \ AND airport.airport_name = %s \ -- AND flight.status = "Upcoming"\ AND %s BETWEEN DATE_SUB(flight.departure_time, INTERVAL 2 DAY) AND DATE_ADD(flight.departure_time, INTERVAL 2 DAY) \ AND %s BETWEEN DATE_SUB(flight.arrival_time, INTERVAL 2 DAY) AND DATE_ADD(flight.arrival_time, INTERVAL 2 DAY) \ AND (flight.airline_name, flight.flight_num) in \ (SELECT flight.airline_name, flight.flight_num FROM flight, airport \ WHERE airport.airport_name=flight.arrival_airport \ AND airport.airport_city = %s \ AND airport.airport_name = %s)' cursor.execute(query, (agentID, fromcity, fromairport, fromdate, todate, tocity, toairport)) data = cursor.fetchall() cursor.close() error = None if(data): return render_template('searchAgent.html', results=data) else: #returns an error message to the html page error = 'No results found' return render_template('searchAgent.html', error=error)
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 commission(): username = session['username'] cursor = conn.cursor() fromdate = request.form['fromdate'] todate = request.form['todate'] print(fromdate, todate) # Get booking_agent_id queryGetID = 'SELECT booking_agent_id FROM booking_agent WHERE email=%s' cursor.execute(queryGetID, username) agentID = cursor.fetchone() # print('~~~DEBUG: ', agentID) # Get total commsion in the past 30 days queryGetCommission = 'SELECT sum(price)*.10 as totalComm FROM purchases, ticket, flight \ WHERE purchases.ticket_id = ticket.ticket_id \ AND ticket.airline_name = flight.airline_name AND ticket.flight_num = flight.flight_num \ AND purchases.purchase_date BETWEEN CAST(%s AS DATE) AND CAST(%s AS DATE) \ AND purchases.booking_agent_id = %s' cursor.execute(queryGetCommission, (fromdate, todate, agentID['booking_agent_id'])) totalComm = cursor.fetchone() totalCommVal = 0 if totalComm['totalComm'] != None: totalCommVal = totalComm['totalComm'] # print('~~~DEBUG:: ', totalComm) # Get total tickets in the past 30 days queryGetTicketCount = 'SELECT count(*) as ticketCount FROM purchases, ticket, flight \ WHERE purchases.ticket_id = ticket.ticket_id \ AND ticket.airline_name = flight.airline_name AND ticket.flight_num = flight.flight_num \ AND purchases.purchase_date BETWEEN CAST(%s AS DATE) AND CAST(%s AS DATE) \ AND purchases.booking_agent_id = %s' cursor.execute(queryGetTicketCount, (fromdate, todate, agentID['booking_agent_id'])) ticketCount = cursor.fetchone() ticketCountVal = ticketCount['ticketCount'] # print('~~~DEBUG: ', ticketCount) # avgComm = totalComm/ticketCount cursor.close() return render_template('commission.html', fromdate=fromdate, todate=todate, totalComm=totalCommVal, ticketCount=ticketCountVal)