def getProductsWithSameCategory(data):
ret_list = []
product = None
conn = connectToPostgreSQL()
cur = conn.cursor()
#search for the input product in the table and save its row of data to variable product, so it can then be compared to the other rows in the table.
cur.execute("SELECT * FROM product")
row = cur.fetchone()
while row is not None:
if row[0] == data.get("productId"):
product = row
break
else:
row = cur.fetchone()
#properties is iterated again, now to compare the input product with the other products
cur.execute("SELECT * FROM product")
row = cur.fetchone()
while row is not None:
if row[4] == product[4] and row[0] != data.get("productId") and (
row[5] == product[5] or row[5] == 'unisex'):
ret_list.append(row[0])
row = cur.fetchone()
cur.close()
conn.close()
return ret_list
def loadIntoDB():
conn = connectToPostgreSQL()
cur = conn.cursor()
start_datum = '2018-01-01'
end_datum = '2019-01-02'
getBuidInThisTimeInterval(conn, cur, start_datum, end_datum)
cur.close()
conn.close()
def getMostPopularThisWeek():
conn = connectToPostgreSQL()
cur = conn.cursor()
top_per_week = getTopPerWeek(cur)
cur.close()
conn.close()
ret_list = getProductsFromList(top_per_week)
return ret_list
def getProductsBasedOnBrand(data):
visitor_id = data.get("visitorId")
visitor_profile = getProfile(visitor_id)
visitor_ordered = []
ret_list = []
product_list = []
counter_list = []
product = None
#prepare a list of items the visitor has ordered before so it can be more easily checked later
for x in range(len(visitor_profile["orders"])):
for y in range(len(visitor_profile["orders"][x])):
visitor_ordered.append(visitor_profile["orders"][x][y])
for x in range(len(visitor_ordered)):
if visitor_ordered[x] not in product_list:
product_list.append(visitor_ordered[x])
counter_list.append(1)
else:
counter_list[product_list.index(visitor_ordered[x])] += 1
most_purchased_product = product_list[counter_list.index(
max(counter_list))]
# search for the input product in the table and save its row of data to variable product, so it can then be compared to the other rows in the table.
conn = connectToPostgreSQL()
cur = conn.cursor()
cur.execute("SELECT * FROM product")
row = cur.fetchone()
while row is not None:
if row[0] == most_purchased_product:
product = row
break
else:
row = cur.fetchone()
cur.execute("SELECT * FROM product")
row = cur.fetchone()
while row is not None:
if row[1] == product[1] and row[0] != data.get("productId") and (
row[5] == product[5] or row[5] == 'unisex'):
ret_list.append(row[0])
row = cur.fetchone()
cur.close()
conn.close()
cleanUpColorDuplicates(ret_list)
random.shuffle(ret_list)
ret_list = ret_list[0:min(5, len(ret_list))]
ret_list = getProductsFromList(ret_list)
return ret_list
def getMostSimilarVisitors_order_based(visitor, visitor_previously_ordered):
ret_list = []
buid_similar_list = []
buid_list = []
buid_counter_list = []
conn = connectToPostgreSQL()
cur = conn.cursor()
cur.execute("SELECT * FROM orders")
row = cur.fetchone()
while row is not None:
if row[0] not in visitor["buids"] and row[
1] in visitor_previously_ordered and row[1] != "None":
if row[0] not in buid_list:
buid_list.append(row[0])
buid_counter_list.append(1)
else:
buid_counter_list[buid_list.index(row[0])] += 1
row = cur.fetchone()
x = 0
while x < 5:
most_similar_buid = buid_list[buid_counter_list.index(
max(buid_counter_list))]
most_similar_buid_count = max(buid_counter_list)
buid_similar_list.append(most_similar_buid)
buid_counter_list.remove(most_similar_buid_count)
buid_list.remove(most_similar_buid)
x += 1
cur.execute("SELECT * FROM visitors_buid")
row = cur.fetchone()
while row is not None:
if row[1] in buid_similar_list:
ret_list.append(row[0])
row = cur.fetchone()
cur.close()
conn.close()
return ret_list
def getMostPurchasedProducts():
ret_list = []
conn = connectToPostgreSQL()
cur = conn.cursor()
cur.execute("SELECT * FROM populair")
row = cur.fetchone()
while len(ret_list) < 10:
ret_list.append(row[0])
row = cur.fetchone()
cur.close()
conn.close()
ret_list = getProductsFromList(ret_list)
return ret_list
def loadMostPurchasedProductsIntoDatabase():
conn = connectToPostgreSQL()
cur = conn.cursor()
product_list = []
counter_list = []
sorted_product_list = []
sorted_product_list_counter = []
cur.execute("SELECT * FROM orders")
row = cur.fetchone()
while row is not None:
if row[1] not in product_list:
product_list.append(row[1])
counter_list.append(1)
else:
counter_list[product_list.index(row[1])] += 1
row = cur.fetchone()
x = len(product_list)
while x:
most_sold_id = product_list[counter_list.index(max(counter_list))]
most_sold_count = max(counter_list)
sorted_product_list.append(most_sold_id)
sorted_product_list_counter.append(most_sold_count)
counter_list.remove(most_sold_count)
product_list.remove(most_sold_id)
x -= 1
for index in range(len(sorted_product_list)):
values = '\'' + sorted_product_list[index] + '\'' + ',' + str(
sorted_product_list_counter[index])
insertPostgres(cur, values, 'populair', conn)
cur.close()
conn.close()
def getProductsWithSameProperties(data, weight=0.3):
ret_list = []
product = None
target_points = 0
conn = connectToPostgreSQL()
cur = conn.cursor()
#search for the input product in the table and save its row of data to variable product, so it can then be compared to the other rows in the table.
cur.execute("SELECT * FROM properties")
row = cur.fetchone()
while row is not None:
if row[0] == data.get("productId"):
product = row
break
else:
row = cur.fetchone()
for x in range(2, len(product)):
if product[x] != 'None':
target_points += 1
#properties is iterated again, now to compare the input product with the other products
cur.execute("SELECT * FROM properties")
row = cur.fetchone()
while row is not None:
points = 0
for x in range(2, len(row)):
if row[x] == product[x] and row[x] != 'None':
points += 1
if points >= (target_points * weight):
ret_list.append(row[0])
row = cur.fetchone()
cur.close()
conn.close()
return ret_list