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