def handleSearch(): searchFilters = json.loads(request.data) username = searchFilters['username'] cursor.execute( f"select * from Shipment where Username != '{username}' and \ FromLocation = '{searchFilters['from']['label']}' \ and ToLocation = '{searchFilters['to']['label']}'\ and RequestedDeliveryDate <= '{searchFilters['date'].split('T')[0]}'") shipments = cursor.fetchall() # items items = getItemsOfShipments(shipments, cursor) pics = matchPicsOfItems(shipments) # trips cursor.execute( f"select * from Trip where Username != '{username}' and FromLocation = '{searchFilters['from']['label']}' \ and ToLocation = '{searchFilters['to']['label']}' \ and Date <= '{searchFilters['date'].split('T')[0]}' ") trips = cursor.fetchall() midpoints, unWantedCategories = getMidpointsAndunWantedCats(trips, cursor) return jsonify({ 'trips': zipTableAttrs('trip', trips), 'shipments': zipTableAttrs('shipment', shipments), 'midpoints': midpoints, 'unWantedCategories': unWantedCategories, 'items': items, "itemPics": pics })
def acceptRequest(): data = json.loads(request.data) reqId = data['reqId'] status = data['status'] cursor.execute( f"UPDATE Request SET status = '{status}' WHERE ID = '{reqId}';") return 'SUCCESS'
def validateUser(): """ Validating the existance of a user in the database. """ data = json.loads(request.data) usernameOrEmail = data['usernameOrEmail'].strip() password = data['password'] if validate_email(usernameOrEmail): condition = 'Email' else: condition = 'Username' cursor.execute(f"select * from User where {condition}='{usernameOrEmail}'") fetched_data = cursor.fetchall() if not len(fetched_data) == 0: fetched_zipped_data = zipTableAttrs('user', fetched_data)[0] if usernameOrEmail == fetched_zipped_data[ condition] and password == fetched_zipped_data['Password']: token = jwt.encode( { 'user': usernameOrEmail, 'exp': datetime.utcnow() + timedelta(minutes=30) }, app.config['SECRET_KEY']) return jsonify({ 'exists': True, 'token': token.decode('utf-8'), 'username': fetched_zipped_data['Username'] }) return jsonify({'exists': False, 'username': ''})
def completedDeals(): data = json.loads(request.data) periodIsOver = data['periodIsOver'] if periodIsOver: reqId = data['reqId'] status = data['status'] cursor.execute( f"UPDATE Request SET status = '{status}' WHERE ID = '{reqId}'") return 'SUCCESS'
def otherPersonsInfo(): data = json.loads(request.data) username = data['username'] cursor.execute(f"select * from User where Username='******'") info = zipTableAttrs('user', cursor.fetchall())[0] return jsonify({ 'Email': info['Email'], 'PhoneNumber': info['PhoneNumber'] })
def addReview(): data = json.loads(request.data) reqId = data['reqId'] who = data['who'] cursor.execute( f"insert into Review(tripID, ShipmentID, reviewerUsername, revieweeUsername, NumOfStars, Text) values('{data['TripID']}', '{data['ShipmentID']}', '{data['reviewerUsername']}', '{data['revieweeUsername']}', '{data['NumOfStars']}', '{data['Text']}')" ) cursor.execute( f"UPDATE Request SET {who} = '{cursor.lastrowid}' WHERE ID = '{reqId}'" ) return 'SUCCESS'
def registerRequest(): data = json.loads(request.data) trip = data['trip'] shipment = data['shipment'] if data['sender'] == 'shipment': cursor.execute( f"insert into Request(TripID, ShipmentID, senderUsername, receiverUsername, status) values ('{trip['Id']}', '{shipment['Id']}', '{shipment['username']}', '{trip['Username']}', '{0}')" ) else: cursor.execute( f"insert into Request(TripID, ShipmentID, senderUsername, receiverUsername, status) values ('{trip['Id']}', '{shipment['Id']}', '{trip['username']}', '{shipment['Username']}', '{0}')" ) return jsonify({'registered': True})
def addShipment(): shipment = json.loads(request.data) cursor.execute( f"insert shipment(Username, FromLocation, ToLocation, RequestedDeliveryDate, Title, Description_, OfferedReward) values('{shipment['username']}', '{shipment['from']['label']}', '{shipment['to']['label']}','{shipment['date'].split('T')[0]}', '{shipment['title']}', '{shipment['description']}', '{shipment['OfferedReward']}')" ) id = cursor.lastrowid items = shipment['itemArray'] for item in items: ItemCat = item['ItemCategory'] cursor.execute( f"insert item (shipmentID, Category, Weight, Name, Quantity, ItemLink) values ('{id}', '{shipment['lookupTable'][ItemCat]}', '{item['ItemWeight']}', '{item['ItemTitle']}', '{item['ItemQuantity']}', '{item['ItemLink']}')" ) # this works better I think. Double check with asem because this will cause problems. cursor.execute("SELECT max(id) FROM shipment") testID = cursor.fetchone()[0] return jsonify({'created': True, 'Id': testID})
def addTrip(): trip = json.loads(request.data) cursor.execute( f"insert Trip(Username, FromLocation, ToLocation, Date, Time, AvailableWeight, Description) values('{trip['username']}', '{trip['from']['label']}', '{trip['to']['label']}','{trip['date'].split('T')[0]}', '{trip['time'].split('T')[1][:-5]}', '{trip['AvWeight']}', '{trip['description']}')" ) id = cursor.lastrowid for unwantedcat in trip["cats"]: cursor.execute( f"insert unwantedcategory(tripID, Category) values('{id}','{unwantedcat}')" ) for point in trip["midPoints"]: cursor.execute( f"insert midpoint(District, TripID) values('{point['label']}','{id}')" ) return jsonify({'created': True, 'Id': cursor.lastrowid})
def registerUser(): """ Registering a user, inserting it the User table if it is not in use and valid. """ data = json.loads(request.data) name = data['name'].strip() username = data['username'].strip() email = data['email'].strip() password = data['password'] country = data['country']['label'] phoneNum = data['phone'] birthday = data['birthday'].split('T')[0] # check if the email is available emailAvailable = False emailValid = False cursor.execute(f'select * from User where Email="{email}"') if len(cursor.fetchall()) == 0: emailAvailable = True if validate_email(email): emailValid = True # check if username is available usernameAvailable = False cursor.execute(f'select * from User where Username="******"') if len(cursor.fetchall()) == 0: usernameAvailable = True # assign a deafult picture if emailAvailable and usernameAvailable and emailValid: cursor.execute( f"insert User(Name, Username, Email, Password, Country, PhoneNumber, Birthday) values('{name}', '{username}', '{email}', '{password}','{country}', '{phoneNum}', '{birthday}')" ) return jsonify({ 'registered': True, 'emailValid': True, 'emailAvailable': True, 'usernameAvailable': True }) else: return jsonify({ 'registered': False, 'emailValid': emailValid, 'emailAvailable': emailAvailable, 'usernameAvailable': usernameAvailable })
def updateUserInfo(): updatedInfo = json.loads(request.data) cursor.execute( f"UPDATE User set Name='{updatedInfo['name']}', Email='{updatedInfo['email']}', Country='{updatedInfo['location']}' where Username='******'username']}'" ) return {"UpdateStatus": True}
def suggestedResults(username): ### Trips #### cursor.execute(f""" select FromLocation from trip as t, User as u where t.Username='******' and u.Username='******' UNION select ToLocation from trip as t, User as u where t.Username='******' and u.Username='******' UNION select FromLocation from trip as t, User as u where u.Username='******' and FromLocation=u.Country UNION select ToLocation from trip as t, User as u where u.Username='******' and ToLocation=u.Country """) # countries the user is interested in countries = [x[0] for x in cursor.fetchall()] trips = [] for country in countries: cursor.execute(f""" select * from trip where Username != '{username}' and (ToLocation = '{country}' or FromLocation = '{country}') """) trips.extend(cursor.fetchall()) trips = [t for t in {k[0]: k for k in trips}.values()] # unique trips midpoints, unWantedCategories = getMidpointsAndunWantedCats(trips, cursor) ### Shipments #### cursor.execute(f""" select FromLocation from shipment as t, User as u where t.Username='******' and u.Username='******' UNION select ToLocation from shipment as t, User as u where t.Username='******' and u.Username='******' UNION select FromLocation from shipment as t, User as u where u.Username='******' and FromLocation=u.Country UNION select ToLocation from shipment as t, User as u where u.Username='******' and ToLocation=u.Country """) # countries the user is interested in countries = [x[0] for x in cursor.fetchall()] shipments = [] for country in countries: cursor.execute(f""" select * from shipment where Username != '{username}' and (ToLocation = '{country}' or FromLocation = '{country}') """) shipments.extend(cursor.fetchall()) # unique shipments shipments = [t for t in {k[0]: k for k in shipments}.values()] # items items = getItemsOfShipments(shipments, cursor) # Pics pics = matchPicsOfItems(shipments) return jsonify({ 'trips': zipTableAttrs('trip', trips), 'midpoints': midpoints, 'unWantedCategories': unWantedCategories, 'shipments': zipTableAttrs('shipment', shipments), 'items': items, "itemPics": pics })
def cancelRequestAccept(): data = json.loads(request.data) reqId = data['reqId'] cursor.execute(f"UPDATE Request SET status = '{0}' WHERE ID = '{reqId}';") return 'SUCCESS'
def cancelRequest(): data = json.loads(request.data) reqId = data['reqId'] cursor.execute(f"Delete From Request WHERE ID = '{reqId}';") return 'SUCCESS'
def getUserInfo(username): """ - A dynamic url containing id of a user that is passed to the function. - A query will get the user info with that Id. - Reviews are queryed with the profile's id """ cursor.execute(f"select * from User where Username='******'") res = cursor.fetchall() if len(res) == 0: return jsonify('NOT FOUND') info = zipTableAttrs('user', res)[0] # trips cursor.execute(f"select * from Trip where Username='******'") trips = cursor.fetchall() midpoints, unWantedCategories = getMidpointsAndunWantedCats(trips, cursor) # shipments cursor.execute(f"select * from Shipment where Username='******'") shipments = cursor.fetchall() items_final = getItemsOfShipments(shipments, cursor) pics = matchPicsOfItems(shipments) # reviews cursor.execute(f"select * from Review where revieweeUsername='******'") reviews = zipTableAttrs('review', cursor.fetchall()) # user's rating ratings = map(lambda rev: rev['NumOfStars'], reviews) freq = collections.Counter(ratings) summation = sum(int(k) * v for k, v in freq.items()) allrates = sum([i[1] for i in freq.items() if int(i[0]) != 0]) if allrates > 0: userRating = int(round(summation / allrates)) else: userRating = 0 # requests cursor.execute( f"select * from Request where senderUsername='******'Username']}' or receiverUsername='******'Username']}'" ) requests = zipTableAttrs('request', cursor.fetchall()) # req_trip_ship => {req: {ship:'', trip:''}} req_shipTrip = {} items = [] # of a shipment for req in requests: tripId = req['TripID'] shipmentId = req['ShipmentID'] cursor.execute(f"select * from Shipment where ID='{shipmentId}'") # shipment related to the request shipment = zipTableAttrs('shipment', cursor.fetchall()) cursor.execute(f"select * from Trip where Id='{tripId}'") # trip related to the request trip = zipTableAttrs('trip', cursor.fetchall()) # midpoints cursor.execute( f"select (District) from trip as t, Midpoint as m where t.Id = m.TripID and t.Id='{tripId}'" ) midpoint_districts = [i[0] for i in cursor.fetchall()] # unwanted categories cursor.execute( f"select (Category) from trip as t, UnwantedCategory as u where t.Id = u.tripID and t.Id ='{tripId}'" ) unwanted_categories = [i[0] for i in cursor.fetchall()] # Items cursor.execute( f"select * from Item where shipmentID = '{shipmentId}'") items = zipTableAttrs('item', cursor.fetchall()) req_shipTrip[req['Id']] = { 'shipment': shipment[0], 'trip': trip[0], 'unwantedCategories': unwanted_categories, 'midpoints': midpoint_districts, 'items': items } info['userRating'] = userRating info_reviews = { 'info': info, 'reviews': reviews, 'trips': zipTableAttrs('trip', trips), 'shipments': zipTableAttrs('shipment', shipments), 'midpoints': midpoints, 'unWantedCategories': unWantedCategories, 'items': items, 'requests': requests, 'reqShipTrip': req_shipTrip, 'items': items_final, 'itemsPics': pics } return jsonify(info_reviews)