def get(self): # first check the auth token auth_header = request.headers.get('Authorization') if not auth_header: return "No authorization token", 403 T = Token() identity = T.check(auth_header) if not identity: return "Wrong token", 403 if identity['role'] != 1: return "Not customer", 403 # select all purchased items left outer join ratings sql_1 = """ SELECT purchases.item_id, customer_rating.rating FROM (SELECT item_id FROM order_item, orders WHERE order_item.ord_id = orders.ord_id and user_id = ?) AS purchases LEFT OUTER JOIN customer_rating ON purchases.item_id = customer_rating.item_id AND customer_rating.user_id = ? """ param_1 = (identity['user_id'], identity['user_id']) try: with sqlite3.connect(os.environ.get("DB_FILE")) as conn: conn.row_factory = lambda C, R: { c[0]: R[i] for i, c in enumerate(C.description) } cur = conn.cursor() cur.execute(sql_1, param_1) result = cur.fetchall() if len(result) == 0: return "No purchase records yet", 204 else: # add the item name and photo into it for d in result: sql_2 = "SELECT name FROM item WHERE item_id = {}".format( d['item_id']) sql_3 = "SELECT photo FROM photo WHERE item_id = {} LIMIT 1".format( d['item_id']) cur.execute(sql_2) d['name'] = cur.fetchone()['name'] cur.execute(sql_3) d['photo'] = cur.fetchone()['photo'] return result, 200 except Exception as e: print(e) return "Internal server error", 500
def post(self): # first check the auth token auth_header = request.headers.get('Authorization') if not auth_header: return "No authorization token", 403 T = Token() identity = T.check(auth_header) if not identity: return "Wrong token", 403 # unpack the address data = request.json if not data: return "Malformed request", 400 success, result = unpack( data, "unit_number", "street_number", "street_name", "suburb", "postcode", "state", required=True ) if not success: return "Missing parameter in address", 400 unitnumber, streetnumber, streetname, suburb, postcode, state = result # check all validity success, msg = check_address( unitnumber, streetnumber, streetname, suburb, postcode, state ) if not success: return msg, 400 sql = """INSERT INTO customer_address(user_id, unit_number, street_number, street_name, suburb, state, postcode) VALUES(?, ?, ?, ?, ?, ?, ?) """ values = (identity['user_id'], unitnumber, streetnumber, streetname, suburb, state, postcode) try: with sqlite3.connect(os.environ.get("DB_FILE")) as conn: conn.row_factory = lambda C, R: {c[0]: R[i] for i, c in enumerate(C.description)} cur = conn.cursor() cur.execute(sql, values) new_address_id = cur.lastrowid return {"address_id": new_address_id}, 200 except Exception as e: print(e) return "Internal server error", 500
def get(self): auth_header = request.headers.get("Authorization") if not auth_header: return "No authorization token", 403 T = Token() identity = T.check(auth_header) if not identity: return "Wrong token", 403 result = get_user_profile(identity['user_id']) return result
def check_admin_token(header): if not header: abort(400, "No authorization token") T = Token() identity = T.check(header) if not identity: abort(403, "Wrong token") if identity['role'] != 0: abort(403, "Only admin can access") return identity
def check_admin_identity(): # check token auth = request.headers.get("Authorization") if not auth: return None, "No authorization token", 403 T = Token() identity = T.check(auth) if not identity: return None, "Wrong token", 403 if identity['role'] != 0: return None, "Only admin can edit", 403 return identity, None, None
def get(self): # check the token first # first check the auth token auth_header = request.headers.get('Authorization') if not auth_header: return "No authorization token", 403 T = Token() identity = T.check(auth_header) if not identity: return "Wrong token", 403 try: with sqlite3.connect(os.environ.get("DB_FILE")) as conn: conn.row_factory = lambda C, R: { c[0]: R[i] for i, c in enumerate(C.description) } cur = conn.cursor() sql_1 = """ SELECT orders.ord_id FROM user, orders WHERE user.user_id = orders.user_id AND user.user_id = ? ORDER BY unix_time DESC """ sql_1_param = (identity["user_id"], ) cur.execute(sql_1, sql_1_param) sql_1_result = cur.fetchall() order_id_list = [e['ord_id'] for e in sql_1_result] if not order_id_list: return "The customer has not made any orders yet", 204 result = [] for ord_id in order_id_list: result.append(get_this_order_history(ord_id)) return result, 200 except Exception as e: print(e) return "Internal server error", 500
def post(self): # check the token first # first check the auth token auth_header = request.headers.get('Authorization') if not auth_header: return "No authorization token", 403 T = Token() identity = T.check(auth_header) if not identity: return "Wrong token", 403 # check the payload data = request.json if not data: return "Malformed request", 400 # submit cart code, response = submit_order(data, identity) return response, code
def get(self): header = request.headers.get("Authorization") if not header: return "No authorization token", 403 T = Token() identity = T.check(header) if not identity: return "Wrong Token", 403 sql = """ SELECT DISTINCT view_history.item_id FROM view_history LEFT OUTER JOIN item ON view_history.item_id = item.item_id WHERE user_id= ? AND item.status = 1 ORDER BY time DESC LIMIT 8 """ parameter = (identity["user_id"],) try: with sqlite3.connect(os.environ.get("DB_FILE")) as conn: conn.row_factory = lambda C, R: {c[0]: R[i] for i, c in enumerate(C.description)} cur = conn.cursor() r = cur.execute(sql, parameter) result = r.fetchall() if len(result) == 0: return "No content", 204 else: final = get_all_profiles(list(result)) return final, 200 except Exception as e: print(e) return "Internal server error", 500
def put(self): auth=request.headers.get("Authorization") if not auth: return "No authorization token",403 T=Token() identity=T.check(auth) if not identity: return"Wrong token",403 ids = identity['user_id'] data=request.json if not data: return "Malformed request", 400 is_unpack_ok, modified_data = unpack( data, "first_name","last_name","email","mobile","password", required=False ) if not is_unpack_ok: return "Malformed request", 400 f_name, l_name, email, mobile, password = modified_data # also prepare the sql for the relevant modified data # one data => one sql => one tuple in the sql_param_list sql_list = [] sql_param_list = [] if f_name: ok, msg = check_name(f_name) if not ok: return msg, 400 sql_list.append("UPDATE user SET first_name = ? WHERE user_id = ?") sql_param_list.append((f_name, ids)) if l_name: ok, msg = check_name(l_name) if not ok: return msg, 400 sql_list.append("UPDATE user SET last_name = ? WHERE user_id = ?") sql_param_list.append((l_name, ids)) if email: ok, msg = check_email(email) if not ok: return msg, 400 sql_list.append("UPDATE user SET email = ? WHERE user_id = ?") sql_param_list.append((email, ids)) if mobile: ok, msg = check_mobile(mobile); if not ok: return msg, 400 sql_list.append("UPDATE user SET mobile = ? WHERE user_id = ?") sql_param_list.append((mobile, ids)) if password: ok, msg = check_password(password) if not ok: return msg, 400 sql_list.append("UPDATE user SET password = ? WHERE user_id = ?") sql_param_list.append((password, ids)) # if nothing update, this is a malformed request if len(sql_list) == 0: return "Malformed request", 400 try: with sqlite3.connect(os.environ.get("DB_FILE")) as conn: conn.row_factory = lambda C, R: {c[0]: R[i] for i, c in enumerate(C.description)} cur = conn.cursor() for i in range(len(sql_list)): cur.execute(sql_list[i], sql_param_list[i]) return "OK", 200 except Exception as e: print(e) return "Internal server error", 500
def delete(self): # first check the auth token auth_header = request.headers.get('Authorization') if not auth_header: return "No authorization token", 403 T = Token() identity = T.check(auth_header) if not identity: return "Wrong token", 403 # require the address_id if not request.args.get("address_id"): return "Missing address_id", 400 address_id = None try: address_id = int(request.args.get("address_id")) except ValueError: return "Address_id should be integer", 400 if address_id and address_id <= 0: return "Address_id should be positive", 400 # several things: # 1. Check whether this address belongs to the user or not # 2. The address must be active (i.e. state = 1) # 3. The address should not be the last set of address of this user try: with sqlite3.connect(os.environ.get("DB_FILE")) as conn: conn.row_factory = lambda C, R: {c[0]: R[i] for i, c in enumerate(C.description)} cur = conn.cursor() sql_1 = "SELECT status FROM customer_address WHERE user_id = ? AND address_id = ?" sql_1_param = (identity['user_id'], address_id) cur.execute(sql_1, sql_1_param) result_1 = cur.fetchone() if not result_1: return "Invalid address_id", 401 if result_1['status'] != 1: return "address_id is deleted already", 401 sql_2 = "SELECT count(*) AS num FROM customer_address WHERE user_id = ? AND status = 1" sql_2_param = (identity['user_id'],) cur.execute(sql_2, sql_2_param) result_2 = cur.fetchone() if int(result_2['num']) == 1: return "This is the last address set of this user", 402 # now change the status to 0 sql_3 = "UPDATE customer_address SET status = 0 WHERE address_id = ?" sql_3_param = (address_id, ) cur.execute(sql_3, sql_3_param) return "OK", 200 except Exception as e: print(e) return "Internal server error", 500
def put(self): # first check the auth token auth_header = request.headers.get('Authorization') if not auth_header: return "No authorization token", 403 T = Token() identity = T.check(auth_header) if not identity: return "Wrong token", 403 # require the address_id if not request.args.get("address_id"): return "Missing address_id", 400 address_id = None try: address_id = int(request.args.get("address_id")) except ValueError: return "Address_id should be integer", 400 if address_id and address_id <= 0: return "Address_id should be positive", 400 # check if the token user has the address or not sql_1 = """ SELECT * FROM customer_address WHERE user_id = ? and address_id = ? """ sql_1_param = (identity['user_id'], address_id) try: with sqlite3.connect(os.environ.get("DB_FILE")) as conn: conn.row_factory = lambda C, R: {c[0]: R[i] for i, c in enumerate(C.description)} cur = conn.cursor() result = cur.execute(sql_1, sql_1_param) if not result: return "Invalid address_id", 401 except Exception as e: print(e) return "Internal server error", 500 # nwo unpack the address data = request.json if not data: return "Malformed request", 400 success, result = unpack( data, "unit_number", "street_number", "street_name", "suburb", "postcode", "state", required=True ) if not success: return "Missing parameter in address", 400 unitnumber, streetnumber, streetname, suburb, postcode, state = result # check all validity success, msg = check_address( unitnumber, streetnumber, streetname, suburb, postcode, state ) if not success: return msg, 400 # sql sql_2 = """ UPDATE customer_address SET unit_number = ?, street_number = ?, street_name = ?, suburb = ?, postcode = ?, state = ? WHERE user_id = ? AND address_id = ? """ sql_2_param = ( unitnumber, streetnumber, streetname, suburb, postcode, state, identity['user_id'], address_id ) try: with sqlite3.connect(os.environ.get("DB_FILE")) as conn: conn.row_factory = lambda C, R: {c[0]: R[i] for i, c in enumerate(C.description)} cur = conn.cursor() cur.execute(sql_2, sql_2_param) return "OK", 200 except Exception as e: print(e) return "Internal server error", 500
def get(self): auth_header = request.headers.get("Authorization") if not auth_header: return "No authorization", 403 T = Token() identity = T.check(auth_header) if (not identity) or (identity['role'] != 0): return "Wrong token", 403 # check the start and end time, both need to exist start_str = request.args.get("start", None) end_str = request.args.get("end", None) if (not start_str) or (not end_str): return "Require both start and end", 400 start_range, end_range = filter_start_and_end(start_str, end_str) # check the start and end date FIRST_DAY, _ = filter_start_and_end("2021-01-01", "2021-01-01") END_DAY = datetime.now() if start_range > end_range or start_range < FIRST_DAY or end_range > END_DAY: return "Invalid time range", 400 # now convert the end time to one day after end_range += timedelta(days=1) + timedelta(microseconds=1) # check the type, default is "day" graph_type = request.args.get("type", "day") typelist = ['day', 'week', 'month'] if graph_type not in typelist: return "Invalid parameter type", 400 sql_1 = """ SELECT COUNT(ord_id) AS count, SUM(total_price) as value FROM orders WHERE unix_time >= ? AND unix_time <= ? """ sql_2 = """ SELECT orders.user_id,ROUND(SUM(orders.total_price), 2) AS total_price, user.first_name || ' ' || user.last_name AS name FROM orders, user WHERE unix_time >= ? AND unix_time <= ? AND orders.user_id = user.user_id GROUP BY orders.user_id ORDER BY sum(total_price) DESC """ sql_3 = """ SELECT order_item.item_id, SUM(order_item.quantity) AS amount, item.name FROM order_item, orders, item WHERE order_item.ord_id = orders.ord_id AND orders.unix_time >= ? AND orders.unix_time <= ? AND order_item.item_id = item.item_id GROUP BY order_item.item_id ORDER BY amount DESC """ values = (start_range.timestamp(), end_range.timestamp()) result = {} try: with sqlite3.connect(os.environ.get("DB_FILE")) as conn: conn.row_factory = lambda C, R: { c[0]: R[i] for i, c in enumerate(C.description) } cur = conn.cursor() # all orders and each total price cur.execute(sql_1, values) result_1 = cur.fetchone() if not result_1: return "No Records", 204 # simple attribute result["orders"] = result_1['count'] turnover = 0 if result['orders'] != 0: turnover = round(result_1['value'], 2) result["turnover"] = round(turnover, 2) result['gst'] = round(turnover * (1 - 1 / 1.1), 2) result['revenue'] = round(turnover * 0.2, 2) # customer id list, with sum of order prices # first graph, plot customer shopping record bar chart cur.execute(sql_2, values) result_2 = cur.fetchall() result['graphs'] = {} result['graphs']['customers'] = result_2 # second graph, plot of items sale amount cur.execute(sql_3, values) result_3 = cur.fetchall() result['graphs']['items'] = result_3 # the third graph: sale orders & order count VS time in types orders_vs_time_list = [] # within the given time range, there are many periods # deepcopy the start # the end time is the start time minus 1 seconds result['type'] = graph_type p_start = deepcopy(start_range) p_end = deepcopy(start_range) - timedelta(microseconds=1) is_first_period = True while True: p_summary = { "x": p_start.strftime("%Y-%m-%d"), "value": None, "count": None } if is_first_period: # first period, edit the date to the real start of range if graph_type == "week": print(p_start.weekday()) p_start -= timedelta(days=p_start.weekday()) elif graph_type == "month": print(p_start.day) p_start -= timedelta(days=(p_start.day - 1)) is_first_period = False # for the end time p_end = deepcopy(p_start) + move_forward(graph_type) # search sum of orders, and values within this period param = (p_start.timestamp(), p_end.timestamp()) cur.execute(sql_1, param) p_result = cur.fetchone() if p_result['count'] == 0: p_summary['value'] = 0 p_summary['count'] = 0 else: p_summary['value'] = round(p_result['value'], 2) p_summary['count'] = p_result['count'] # insert orders_vs_time_list.append(p_summary) if p_end > end_range: break else: # move forward p_start p_start += move_forward(graph_type) # results for last graph result['graphs']['orders'] = orders_vs_time_list return result, 200 except Exception as e: print(e) return "Internal server error", 500
def put(self, item_id): # first check the auth token auth_header = request.headers.get('Authorization') if not auth_header: return "No authorization token", 403 T = Token() identity = T.check(auth_header) if not identity: return "Wrong token", 403 # request the item_id if not item_id: return "Missing item_id", 400 try: item_id = int(item_id) except ValueError: return "item_id should be integer", 401 if item_id <= 0: return "item_id should be positive", 401 # check if the token user bought the item or not sql_1 = """ SELECT * FROM ( SELECT * FROM orders,order_item WHERE orders.ord_id = order_item.ord_id ) WHERE user_id = ? and item_id = ? """ sql_param = (identity['user_id'], item_id) try: with sqlite3.connect(os.environ.get("DB_FILE")) as conn: conn.row_factory = lambda C, R: { c[0]: R[i] for i, c in enumerate(C.description) } cur = conn.cursor() cur.execute(sql_1, sql_param) result = cur.fetchall() if not result: return "You need to purchase before rating. ", 404 except Exception as e: print(e) return "Internal server error", 500 #get the new rating print(request.json) new_rating = request.json.get( "Rating") # get the new rating from the json straight away if not new_rating: return "Malformed request", 400 #check validity: check integer first, and then check the range between 1 to 5 if not isinstance(new_rating, int): return "Rating must be an integer" if new_rating < 1 or new_rating > 5: return "Rating should be between 1 and 5", 401 #check whether user wants to submit new ranking or update old rating sql_2 = """ SELECT * FROM customer_rating WHERE user_id = ? and item_id = ? """ #submit new ranking sql_3 = """ INSERT INTO customer_rating VALUES (?, ?, ?) """ sql_param_2 = (identity['user_id'], item_id, new_rating) #update old rating sql_4 = """ UPDATE customer_rating SET rating = ? WHERE user_id = ? AND item_id = ? """ sql_param_3 = (new_rating, identity['user_id'], item_id) try: with sqlite3.connect(os.environ.get("DB_FILE")) as conn: conn.row_factory = lambda C, R: { c[0]: R[i] for i, c in enumerate(C.description) } cur = conn.cursor() # first check if the customer has rated before cur.execute(sql_2, sql_param) result = cur.fetchall() # if not rated, then insert if not result: cur.execute(sql_3, sql_param_2) return "OK", 200 else: # if rated already, then update cur.execute(sql_4, sql_param_3) return "OK", 200 except Exception as e: print(e) return "Internal server error", 500
def get(self, page_id): # deal with the page_id, page_size first page_id = filter_page_id(page_id) # deal with all values page_size = filter_page_size(request.args.get("page_size"), 18) order_method = "view" if request.args.get("order_method") in [ "view", "name", "price", "relevancy" ]: order_method = request.args.get("order_method") order = "asc" if request.args.get("order") in ["asc", "desc"]: order = request.args.get("order") price_min = filter_price(request.args.get("price_min"), 0) price_max = filter_price(request.args.get("price_max"), 10000) if price_max < price_min: abort(400, "Price max should > price min") # variable to store all conditions conds = [] conds.append("(item.price >= {} AND item.price <= {})".format( price_min, price_max)) # keyword search, may be empty # when it is the keyword search, default is order_method = relevancy and order = desc keyword = request.args.get("keyword") # multi-valued attributes cpu = filter_param(request.args.getlist("cpu"), ["0", "1"]) storage = filter_param(request.args.getlist("storage"), ["0", "1", "2", "3"]) memory = filter_param(request.args.getlist("memory"), ["0", "1", "2"]) graphic = filter_param(request.args.getlist("graphic"), ["0", "1", "2"]) screen = filter_param(request.args.getlist("screen"), ["0", "1", "2", "3"]) cpu_conds = [ "lower(laptop.cpu_prod) LIKE '%intel%'", "lower(laptop.cpu_prod) LIKE '%amd%'", ] storage_conds = [ "CAST(laptop.primary_storage_cap AS INTEGER) <= 256", "(CAST(laptop.primary_storage_cap AS INTEGER) > 256 AND CAST(laptop.primary_storage_cap AS INTEGER) <= 512)", "(CAST(laptop.primary_storage_cap AS INTEGER) > 512 AND CAST(laptop.primary_storage_cap AS INTEGER) <= 1024)", "CAST(laptop.primary_storage_cap AS INTEGER) > 1024", ] memory_conds = [ "CAST(laptop.memory_size AS INTEGER) <= 8", "(CAST(laptop.memory_size AS INTEGER) > 8 AND CAST(laptop.memory_size AS INTEGER) <= 16)", "CAST(laptop.memory_size AS INTEGER) > 16", ] graphic_conds = [ "laptop.gpu_model LIKE '%GTX 1%'", "laptop.gpu_model LIKE '%RTX 2%'", "laptop.gpu_model LIKE '%RTX 3%'", ] screen_conds = [ "CAST(laptop.display_size AS REAL) <= 13.3", "(CAST(laptop.display_size AS REAL) > 13.3 AND CAST(laptop.display_size AS REAL) <= 15.6)", "CAST(laptop.display_size AS REAL) > 15.6", ] # for each variable list, if one condition, use AND to join, if multiple condition # bracket them, and inside use OR to join conds.append(configure_conds(cpu, cpu_conds)) conds.append(configure_conds(storage, storage_conds)) conds.append(configure_conds(memory, memory_conds)) conds.append(configure_conds(graphic, graphic_conds)) conds.append(configure_conds(screen, screen_conds)) # at last, check the status = 0 / 1 / 2 # default to on sell items status = 1 if (request.args.get("status")): auth_header = request.headers.get("Authorization") if not auth_header: return "No authorization token exist when you try to access parameter 'status'", 403 T = Token() identity = T.check(auth_header) if (not identity) or (identity['role'] != 0): return "Wrong token when you try to access parameter 'status'", 403 status_list = ["0", "1", "2"] if request.args.get("status") not in status_list: return "Wrong status parameter", 400 status = int(request.args.get("status")) # add condition for status if status == 0: conds.append("(item.status = 0)") elif status == 1: conds.append("(item.status = 1)") else: # all items conds.append("(status = 0 OR status = 1)") # remove all None conds = [cond for cond in conds if cond is not None] try: with sqlite3.connect(os.environ.get("DB_FILE")) as conn: conn.row_factory = lambda C, R: { c[0]: R[i] for i, c in enumerate(C.description) } cur = conn.cursor() # get both item_id and name into the list sql = """SELECT item.item_id, item.name FROM item LEFT OUTER JOIN laptop ON item.item_id = laptop.item_id """ for cond in conds: if "WHERE" in sql: sql += "AND {} \n".format(cond) else: sql += "WHERE {} \n".format(cond) if order_method != "relevancy": sql += "ORDER BY {} {}".format(order_method, order) cur.execute(sql) item_id_name_list = cur.fetchall() # if no result, or the id list does not reach this page id # here cannot use abort, it will be caught in the exception if (not item_id_name_list) or (len(item_id_name_list) < page_id * page_size): return (404, "No more pages") # if there is a keyword in the request, then we fetch all item names and compare # the keyword will not have %20 inside result_id_list = item_id_name_list if keyword: keyword = keyword.lower() for item in item_id_name_list: name = item['name'].lower() item[ 'similarity'] = jaro_winkler.normalized_similarity( keyword, name) # if the keyword search asks for order by similarity # use descending order by default if order_method == "relevancy": item_id_name_list = sorted( item_id_name_list, key=lambda d: d['similarity'], reverse=True) # threshold = 0.65 THRESHOLD = 0.65 result_id_list = [ d for d in item_id_name_list if d['similarity'] > THRESHOLD ] # again, check if no results if (not result_id_list) or (len(result_id_list) < page_id * page_size): return (404, "No more pages") # pack the result result = { 'current_page': page_id, 'page_count': get_page_count(len(result_id_list), page_size), 'data': get_all_profiles( result_id_list[page_id * page_size:(page_id + 1) * page_size]) } return result, 200 except Exception as e: print(e) abort(500, "Internal server error")
def get(self, item_id): if not item_id: return "No item_id provided", 400 try: item_id = int(item_id) except ValueError: return "Item_id must be an integer", 400 if item_id <= 0: return "Item_id must be a positive integer", 400 # check the existence, if yes, then query both tables try: with sqlite3.connect(os.environ.get("DB_FILE")) as conn: conn.row_factory = lambda C, R: { c[0]: R[i] for i, c in enumerate(C.description) } cur = conn.cursor() sql_1 = "SELECT * FROM item WHERE item_id = ?" sql_2 = "SELECT * FROM laptop WHERE item_id = ?" sql_3 = "SELECT * FROM photo WHERE item_id = ?" sql_4 = "Update item SET view = view + 1 WHERE item_id = ?" sql_param = (item_id, ) cur.execute(sql_1, sql_param) simple_profile = cur.fetchone() if not simple_profile: return "Not found", 404 cur.execute(sql_2, sql_param) detail_profile = cur.fetchone() cur.execute(sql_3, sql_param) raw_photos = cur.fetchall() cur.execute(sql_4, sql_param) photos = [] for each in raw_photos: photos.append(each['photo']) result = { 'simple': simple_profile, 'detail': detail_profile, 'photos': photos } # before return, check the token header header = request.headers.get("Authorization") # if exist, check the role if header: T = Token() identity = T.check(header) if identity['role'] == 1: # for logged in user, store in the view history sql_4 = "INSERT INTO view_history(user_id, item_id, time) VALUES (?, ?, ?)" sql_4_param = (identity['user_id'], item_id, time.time()) cur.execute(sql_4, sql_4_param) # return result return result, 200 except Exception as e: print(e) return "Internal server error", 500
def get(self): """Recommend items based on user view history (need token)""" auth_header = request.headers.get("Authorization") if not auth_header: return "No authorization token", 403 T = Token() identity = T.check(auth_header) if not identity: return "Wrong token", 403 sql = """ SELECT view_history.* FROM view_history, item WHERE view_history.item_id = item.item_id AND item.status = 1 """ sql2 = """ SELECT item.* FROM item WHERE item.status = 1 """ sql3 = """ SELECT laptop.* FROM laptop, item WHERE laptop.item_id = item.item_id AND item.status = 1 """ u_id = identity['user_id'] #####item-feature fea = [ 'item_id', 'price', 'cpu_lithography', 'cpu_cache', 'cpu_base_speed', 'cpu_boost_speed', 'cpu_cores', 'cpu_tdp', 'cpu_rating', 'cpu_integrated_video_id', 'display_size', 'display_horizontal_resolution', 'display_vertical_resolution', 'display_sRGB', 'memory_size', 'memory_speed', 'primary_storage_cap', 'primary_storage_read_speed', 'gpu_lithography', 'gpu_shaders', 'gpu_base_speed', 'gpu_boost_speed', 'gpu_shader_speed', 'gpu_memory_speed', 'gpu_memory_bandwidth', 'gpu_memory_size', 'gpu_rating', 'wireless_card_speed', 'chassis_height_cm', 'chassis_height_inch', 'chassis_depth_cm', 'chassis_depth_inch', 'chassis_width_cm', 'chassis_width_inch', 'chassis_weight_kg', 'chassis_weight_lb', 'battery_capacity', 'config_score', 'battery_life_raw', 'total_storage_capacity' ] try: with sqlite3.connect(os.environ.get("DB_FILE")) as conn: conn.row_factory = lambda C, R: { c[0]: R[i] for i, c in enumerate(C.description) } cur = conn.cursor() cu1 = conn.cursor() cu2 = conn.cursor() r = cur.execute(sql) r2 = cu1.execute(sql2) r3 = cu2.execute(sql3) result, result1, result2 = r.fetchall(), r2.fetchall( ), r3.fetchall() view_history, item, laptop = pd.DataFrame( result), pd.DataFrame(result1), pd.DataFrame(result2) most_view = pd.DataFrame( view_history.groupby('user_id')['item_id'].agg( lambda x: stats.mode(x)[0])).reset_index() # if this user does not have any view history # then return 204 if not most_view[most_view['user_id'] == u_id]['item_id'].any(): return "New user, no view history", 204 # for users with view history i_id = most_view[most_view['user_id'] == u_id]['item_id'].values[0] all_lap = item.merge(laptop, on='item_id', how='left') item_fea = all_lap[fea].set_index('item_id') new_item = MinMaxScaler().fit_transform(item_fea) new_df = pd.DataFrame(new_item, columns=fea[1:]) df = pd.DataFrame(all_lap['item_id']) nor_item = pd.concat((new_df, df), axis=1).set_index("item_id") def build_xy(item_id1, item_id2): bool_array = nor_item.loc[item_id1].notnull( ) & nor_item.loc[item_id2].notnull() return nor_item.loc[item_id1, bool_array], nor_item.loc[item_id2, bool_array] ###pearsonr to calculate similarity of pair item def pearson(item_id1, item_id2): x, y = build_xy(item_id1, item_id2) mean1, mean2 = x.mean(), y.mean() denominator = (sum((x - mean1)**2) * sum( (y - mean2)**2))**0.5 try: value = sum((x - mean1) * (y - mean2)) / denominator except ZeroDivisionError: value = 0 return value #####find out nearest item def computeNearestNeighbor(item_id, k): return nor_item.drop(item_id).index.to_series().apply( pearson, args=(item_id, )).nlargest(k) KNN_item = computeNearestNeighbor(i_id, 5).index.tolist() p_all = [] for i in KNN_item: profile = {} profile['item_id'] = int(i) p_all.append(profile) r = get_all_profiles(p_all) return r, 200 except Exception as e: print(e) return "Internal server error", 500
def get(self): """Recommend items based on popularity and user similarity (need token)""" auth_header = request.headers.get("Authorization") if not auth_header: return "No authorization token", 403 T = Token() identity = T.check(auth_header) if not identity: return "Wrong token", 403 sql = """SELECT * FROM customer_rating""" u_id = identity['user_id'] try: with sqlite3.connect(os.environ.get("DB_FILE")) as conn: conn.row_factory = lambda C, R: { c[0]: R[i] for i, c in enumerate(C.description) } cur = conn.cursor() r1 = cur.execute(sql) result = r1.fetchall() # user-rating table user_rating = pd.DataFrame(result) #if user has not purchased anything before if u_id not in user_rating.user_id.values: return "user has not purchased anything", 204 # mean ratint rated by each user ratings_mean_count = pd.DataFrame( user_rating.groupby('item_id') ['rating'].mean().sort_values(ascending=False)) ratings_mean_count['rating_counts'] = pd.DataFrame( user_rating.groupby('item_id')['rating'].count()) popular = ratings_mean_count.sort_values( ['rating_counts', 'rating'], ascending=False).reset_index() pop_item = popular['item_id'].values.tolist() df = user_rating.pivot_table(index='user_id', columns='item_id', values='rating') # common part item rated by user pair def build_xy(user_id1, user_id2): bool_array = df.loc[user_id1].notnull( ) & df.loc[user_id2].notnull() return df.loc[user_id1, bool_array], df.loc[user_id2, bool_array] # pearsonr to count similarity of user pair def pearson(user_id1, user_id2): x, y = build_xy(user_id1, user_id2) mean1, mean2 = x.mean(), y.mean() denominator = (sum((x - mean1)**2) * sum( (y - mean2)**2))**0.5 try: value = sum((x - mean1) * (y - mean2)) / denominator except ZeroDivisionError: value = 0 return value # find nearest user def computeNearestNeighbor(user_id, k=8): # the customer may not have purchased before # so need to check, if no purchase, return none # print("1111",df.drop(user_id).index.to_series().apply(pearson, args=(user_id,)).nlargest(k)) return df.drop(user_id).index.to_series().apply( pearson, args=(user_id, )).nlargest(k) ####CF user-based rec def recommend(user_id): # find nearest user_id result = 0 nearest_user_id_list = computeNearestNeighbor( user_id).index.tolist() #find out the item that is rated by neighbour but not user self #in case nearest user has the same rated item as user self. for nearest_user_id in nearest_user_id_list: k_near = df.loc[nearest_user_id, df.loc[user_id].isnull() & df.loc[nearest_user_id].notnull()] if len(k_near.values) != 0: result = k_near.sort_values(ascending=False) # print("result:",result[:5].index) break ###get top 5 return result[:5].index # if no purchase record, return 204 rec_result = recommend(u_id) # print(rec_result) # for customer with purchase record all_result = [] for i in rec_result: all_result.append(i) #recommend to users: top_k top_k = 8 for i in pop_item: if len(all_result) < top_k and i not in all_result: all_result.append(i) elif len(all_result) >= 5: break p_all = [] for i in all_result: profile = {} profile['item_id'] = int(i) p_all.append(profile) r = get_all_profiles(p_all) return r, 200 except Exception as e: print(e) return "Internal server error", 500
def get(self): # first check the auth token auth_header = request.headers.get('Authorization') if not auth_header: return "No authorization token", 403 T = Token() identity = T.check(auth_header) if not identity: return "Wrong token", 403 # check the address_id in query string, but this is optional address_id = None if request.args.get("address_id"): try: address_id = int(request.args.get("address_id")) except ValueError: return "Address_id should be integer", 400 if address_id and address_id <= 0: return "Address_id should be positive", 400 # sql sql = None values = None # if address_id exist if address_id: sql = """SELECT address_id, unit_number, street_number, street_name, suburb, state, postcode FROM customer_address WHERE user_id = ? and address_id = ? """ values = (identity['user_id'],address_id) else: # get all address for this user sql = """SELECT address_id, unit_number, street_number, street_name, suburb, state, postcode FROM customer_address WHERE user_id = ? AND status == 1 """ values = (identity['user_id'],) try: with sqlite3.connect(os.environ.get("DB_FILE")) as conn: conn.row_factory = lambda C, R: {c[0]: R[i] for i, c in enumerate(C.description)} cur = conn.cursor() cur.execute(sql, values) result = cur.fetchall() # check if no result if not result: return "Invalid address_id", 404 else: return result, 200 except Exception as e: print(e) return "Internal server error", 500