def start_loading(world_socket, order_id, truck_id):
print("Entered start_loading()")
command = world.ACommands()
load = command.load.add()
seqnum = generate_seqnum_and_add_to_waiting_ack_set()
load.seqnum = seqnum
try:
conn, cursor = connect_db()
query = f"""
SELECT warehouse_id
FROM amazon_frontend_order
WHERE id={order_id}
"""
cursor.execute(query)
load.whnum = cursor.fetchone()[0]
load.truckid = truck_id
load.shipid = order_id
cursor.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
update_order_status(order_id, order_status.LOADING)
resend_to_world_until_ack_received(command, seqnum, world_socket)
print("Exited start_loading()")
def handle_truck_arrived(world_socket, truck_arrived):
print("Entered handle_truck_arrived()")
try:
conn, cursor = connect_db()
query = f"""
UPDATE amazon_frontend_order
SET truck_id={truck_arrived.truck_id}
WHERE id={truck_arrived.package_id}
"""
execute_and_commit(query, conn, cursor)
query = f"""
SELECT status
FROM amazon_frontend_order
WHERE id={truck_arrived.package_id}
"""
cursor.execute(query)
row = cursor.fetchone()
if row[0] == order_status.PACKED:
start_loading(world_socket, truck_arrived.package_id,
truck_arrived.truck_id)
cursor.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
def handle_purchased(world_socket, ups_socket, purchased):
print("Entered handle_purchased()")
# Reply ACK to world
reply_ack_to_world_with_lock(purchased, world_socket)
try:
conn, cursor = connect_db()
query = ""
for thing in purchased.things:
query += f"""
UPDATE amazon_frontend_warehousestock
SET num_product=num_product+{thing.count}
WHERE warehouse_id={purchased.whnum} AND product_id={thing.id};
"""
execute_and_commit(query, conn, cursor)
query = f"""
SELECT id
FROM amazon_frontend_order
WHERE status='{order_status.PURCHASING}'
"""
cursor.execute(query)
orders = cursor.fetchall()
for order in orders:
order_id = order[0]
if check_warehouse_inventory(order_id, purchased.whnum) == True:
start_packing(world_socket, order_id)
start_getting_truck(ups_socket, order_id)
cursor.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
def handle_packed(world_socket, packed):
print("Entered handle_packed()")
# Reply ACK to world
reply_ack_to_world_with_lock(packed, world_socket)
try:
conn, cursor = connect_db()
query = f"""
UPDATE amazon_frontend_order
SET status='{order_status.PACKED}', time_packed='{get_current_time()}'
WHERE id={packed.shipid}
"""
execute_and_commit(query, conn, cursor)
query = f"""
SELECT truck_id
FROM amazon_frontend_order
WHERE id={packed.shipid}
"""
cursor.execute(query)
truck_id = cursor.fetchone()[0]
if truck_id is not None:
start_loading(world_socket, packed.shipid, truck_id)
cursor.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
print("Exited handle_packed()")
def start_getting_truck(ups_socket, order_id):
''' Start requesting a truck for order: orderid '''
print("Entered start_getting_truck()")
Ucommand = ups.AUCommands()
get_truck = Ucommand.get_truck
try:
conn, cursor = connect_db()
query = f"""
SELECT ups_account, warehouse_id, location_x, location_y, destination_x, destination_y
FROM amazon_frontend_order, amazon_frontend_warehouse
WHERE amazon_frontend_order.warehouse_id=amazon_frontend_warehouse.id AND amazon_frontend_order.id={order_id}
"""
cursor.execute(query)
row = cursor.fetchone()
get_truck.order_id = order_id
get_truck.ups_account = row[0]
get_truck.warehouse_id = row[1]
get_truck.location_x = row[2]
get_truck.location_y = row[3]
get_truck.destination_x = row[4]
get_truck.destination_y = row[5]
cursor.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
send_to_ups_with_lock(Ucommand, ups_socket)
print("Exited start_getting_truck()")
def check_warehouse_inventory(order_id, warehouse_id):
try:
conn, cursor = connect_db()
query = f"""
SELECT product_id, num_product
FROM amazon_frontend_orderproducttuple
WHERE order_id={order_id}
"""
cursor.execute(query)
rows = cursor.fetchall()
for row in rows:
product_id, num_product = row
query = f"""
SELECT num_product
FROM amazon_frontend_warehousestock
WHERE warehouse_id={warehouse_id} AND product_id={product_id}
"""
cursor.execute(query)
num_in_stock = cursor.fetchone()[0]
if num_in_stock < num_product:
return False
cursor.close()
return True
except (Exception, psycopg2.DatabaseError) as error:
print(error)
def connect_world_command(world_id):
conn, cursor = connect_db()
query = f"""
SELECT id, location_x, location_y
FROM amazon_frontend_warehouse
"""
cursor.execute(query)
rows = cursor.fetchall()
cursor.close()
initWarehouses = []
for row in rows:
initWarehouse = world.AInitWarehouse()
initWarehouse.id = row[0]
initWarehouse.x = row[1]
initWarehouse.y = row[2]
initWarehouses.append(initWarehouse)
command = world.AConnect()
command.worldid = world_id
command.isAmazon = True
for initWarehouse in initWarehouses:
command.initwh.add().CopyFrom(initWarehouse)
return command
def update_order_status(order_id, new_status):
conn, cursor = connect_db()
query = f"""
UPDATE amazon_frontend_order
SET status='{new_status}'
WHERE id={order_id}
"""
execute_and_commit(query, conn, cursor)
cursor.close()
def check_canceled(order_id):
conn, cursor = connect_db()
query = f"""
SELECT status
FROM amazon_frontend_order
WHERE id={order_id}
"""
cursor.execute(query)
status = cursor.fetchone()[0]
cursor.close()
return status == order_status.CANCELED
def collect_stock_day(pools: [str], db_type: str, update=False):
pro = _init_api(TOKEN)
conn = connect_db(db_type)
cursor = conn.cursor()
for i, code in enumerate(pools):
try:
start = "20000101"
if update:
cursor.execute("select date from {0} where code='{1}'".format(
STOCK_DAY[TABLE], code))
rs = cursor.fetchall()
if len(rs) > 0:
start = sorted(rs, reverse=True)[0][0]
cursor.execute(
("delete from {0} where code='{1}' "
"and date='{2}'").format(STOCK_DAY[TABLE], code,
start))
start = str(start).replace("-", "")
print("start:", start)
daily = pro.daily(ts_code=code, start_date=start)
adj_factor = pro.adj_factor(ts_code=code)
if update:
adj_factor = adj_factor[adj_factor["trade_date"] >= start]
print("adj:", adj_factor.shape)
df = natural_outer_join(daily, adj_factor)
# 打印进度
print('Seq: ' + str(i + 1) + ' of ' + str(len(pools)) +
' Code: ' + str(code))
df = unify_df_col_nm(df)
# print(df.columns)
except Exception as err:
print(err)
print('No DATA Code: ' + str(i))
continue
for _, row in df.iterrows():
try:
row["date"] = (datetime.datetime.strptime(
row["date"], "%Y%m%d")).strftime('%Y-%m-%d')
cursor.execute(
_sql_insert(db_type, STOCK_DAY[TABLE], STOCK_DAY[COLUMNS]),
tuple(row[list(STOCK_DAY[COLUMNS])]))
conn.commit()
except Exception as err:
print("error:", err)
continue
close_db(conn)
def gen_data(pred_period=20, lower_bound="2011-01-01", start="2014-01-01"):
db_type = "sqlite3"
conn = dbop.connect_db(db_type)
cursor = conn.cursor()
df_all, cols_future = prepare_data(cursor,
pred_period=pred_period,
start=lower_bound)
data_period = (df_all.index >= start)
df_all = df_all[data_period]
df_all = df_all[df_all["amt"] != 0]
return df_all, cols_future
def init_table(table_name, db_type):
conn = connect_db(db_type)
cursor = conn.cursor()
configs = _parse_config(path="database\\config\\{}".format(table_name))
sql_drop = "drop table {}".format(table_name)
sql_create = configs["create"]
print(sql_create)
try:
cursor.execute(sql_drop)
except Exception as e:
pass
cursor.execute(sql_create)
close_db(conn)
def handle_destination_changed(destination_changed):
print("Entered handle_destination_changed()")
try:
conn, cursor = connect_db()
query = f"""
UPDATE amazon_frontend_order
SET destination_x={destination_changed.new_destination_x}, destination_y={destination_changed.new_destination_y}
WHERE id={destination_changed.package_id}
"""
execute_and_commit(query, conn, cursor)
cursor.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
def handle_delivered(delivered):
print("Entered handle_delivered()")
try:
conn, cursor = connect_db()
query = f"""
UPDATE amazon_frontend_order
SET status='{order_status.DELIVERED}', time_delivered='{get_current_time()}'
WHERE id={delivered.package_id}
"""
execute_and_commit(query, conn, cursor)
cursor.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
def start_packing(world_socket, order_id):
print("Entered start_packing()")
command = world.ACommands()
pack = command.topack.add()
seqnum = generate_seqnum_and_add_to_waiting_ack_set()
pack.seqnum = seqnum
try:
conn, cursor = connect_db()
query = f"""
SELECT product_id, description, num_product
FROM amazon_frontend_orderproducttuple, amazon_frontend_product
WHERE amazon_frontend_orderproducttuple.product_id=amazon_frontend_product.id AND amazon_frontend_orderproducttuple.order_id={order_id}
"""
cursor.execute(query)
rows = cursor.fetchall()
for row in rows:
thing = pack.things.add()
thing.id = row[0]
thing.description = row[1]
thing.count = row[2]
query = f"""
SELECT warehouse_id
FROM amazon_frontend_order
WHERE id={order_id}
"""
cursor.execute(query)
pack.whnum = cursor.fetchone()[0]
pack.shipid = order_id
# Update warehouse stock
query = ""
for thing in pack.things:
query += f"""
UPDATE amazon_frontend_warehousestock
SET num_product=num_product-{thing.count}
WHERE warehouse_id={pack.whnum} AND product_id={thing.id};
"""
execute_and_commit(query, conn, cursor)
cursor.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
update_order_status(order_id, order_status.PACKING)
resend_to_world_until_ack_received(command, seqnum, world_socket)
def collect_index_day(pools: [str], db_type: str, update=False):
conn = connect_db(db_type)
cursor = conn.cursor()
for i, code in enumerate(pools):
try:
start = "2000-01-01"
if update:
cursor.execute("select date from {0} where code='{1}'".format(
INDEX_DAY[TABLE], code))
rs = cursor.fetchall()
# print(rs)
if len(rs) > 0:
start = sorted(rs, reverse=True)[0][0]
cursor.execute(
("delete from {0} where code='{1}' "
"and date='{2}'").format(INDEX_DAY[TABLE], code,
start))
print("start:", start)
df = ts.get_k_data(code=code, start=start)
# 打印进度
print('Seq: ' + str(i + 1) + ' of ' + str(len(pools)) +
' Code: ' + str(code))
df.columns = unify_col_names(df.columns)
print(df.shape)
except Exception as err:
print(err)
print('No DATA Code: ' + str(i))
continue
for _, row in df.iterrows():
try:
cursor.execute(
_sql_insert(db_type, INDEX_DAY[TABLE], INDEX_DAY[COLUMNS]),
tuple(row[list(INDEX_DAY[COLUMNS])]))
conn.commit()
except Exception as err:
print(err)
continue
close_db(conn)
def handle_loaded(world_socket, ups_socket, loaded):
print("Entered handle_loaded()")
# Reply ACK to world
reply_ack_to_world_with_lock(loaded, world_socket)
try:
conn, cursor = connect_db()
query = f"""
UPDATE amazon_frontend_order
SET status='{order_status.LOADED}', time_loaded='{get_current_time()}'
WHERE id={loaded.shipid}
"""
execute_and_commit(query, conn, cursor)
start_delivering(ups_socket, loaded.shipid)
except (Exception, psycopg2.DatabaseError) as error:
print(error)
print("Exited handle_loaded()")
def start_delivering(ups_socket, order_id):
print("Entered start_delivering()")
try:
conn, cursor = connect_db()
query = f"""
UPDATE amazon_frontend_order
SET status='{order_status.DELIVERING}'
WHERE id={order_id}
"""
execute_and_commit(query, conn, cursor)
cursor.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
deliver = ups.request_init_delivery()
deliver.package_id = order_id
command = ups.AUCommands()
command.init_delivery.CopyFrom(deliver)
send_to_ups_with_lock(command, ups_socket)
print("Exiting start_delivering")
def handle_cancel(cancel):
print("Entered handle_cancel()")
try:
conn, cursor = connect_db()
query = f"""
SELECT status
FROM amazon_frontend_order
WHERE id={cancel.order_id}
"""
cursor.execute(query)
status = cursor.fetchone()[0]
if status == order_status.PURCHASING:
query = f"""
UPDATE amazon_frontend_order
SET status='{order_status.CANCELED}'
WHERE id={cancel.order_id}
"""
execute_and_commit(query, conn, cursor)
cursor.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
#References:
#https://medium.com/ai-society/a-concise-recommender-systems-tutorial-fa40d5a9c0fa
#https://cambridgespark.com/content/tutorials/implementing-your-own-recommender-systems-in-Python/index.html
#https://hackernoon.com/introduction-to-recommender-system-part-1-collaborative-filtering-singular-value-decomposition-44c9659c5e75
from pearson_similarity import similarity
from recommend import recommend
from db_operations import connect_db, get_reviews, insert_recommendations, clear_recommendations
conn = connect_db()
records = get_reviews(conn)
num_users = len(records)
to_insert = []
for i in range(num_users):
user_id = records[i][0]
recommendations = recommend(records, i, 10, similarity)
as_tuples = [(user_id, int(item), recommendations[item])
for item in recommendations]
to_insert.extend(as_tuples)
print('Done fetching reviews!')
clear_recommendations(conn)
insert_recommendations(conn, to_insert)
conn.commit()
conn.close()
def insert_to_db(row, db_type: str, table_name, columns):
conn = connect_db(db_type)
cursor = conn.cursor()
cursor.execute(_sql_insert(db_type, table_name, columns),
list(row[columns]))
conn.commit()
def handle_buy(world_socket, ups_socket, buy):
print("Entered handle_buy()")
Acommand = world.ACommands()
purchase = Acommand.buy.add()
seqnum = generate_seqnum_and_add_to_waiting_ack_set()
purchase.seqnum = seqnum
# Ucommand = ups.AUCommands()
# get_truck = Ucommand.get_truck
try:
conn, cursor = connect_db()
# Acommand
# retrieve things in an order, be careful with the columns queried
query = f"""
SELECT product_id, description, num_product
FROM amazon_frontend_orderproducttuple, amazon_frontend_product
WHERE amazon_frontend_orderproducttuple.product_id=amazon_frontend_product.id AND amazon_frontend_orderproducttuple.order_id={buy.order_id}
"""
cursor.execute(query)
rows = cursor.fetchall()
for row in rows:
thing = purchase.things.add()
thing.id = row[0]
thing.description = row[1]
thing.count = row[2]
query = f"""
SELECT COUNT(*), MIN(id)
FROM amazon_frontend_warehouse;
"""
cursor.execute(query)
num_warehouse, min_warehouse_id = cursor.fetchone()
purchase.whnum = buy.order_id % int(num_warehouse) + int(
min_warehouse_id)
query = f"""
UPDATE amazon_frontend_order
SET warehouse_id={purchase.whnum}
WHERE id={buy.order_id}
"""
execute_and_commit(query, conn, cursor)
# # Ucommand
# query = f"""
# SELECT ups_account, warehouse_id, location_x, location_y, destination_x, destination_y
# FROM amazon_frontend_order, amazon_frontend_warehouse
# WHERE amazon_frontend_order.warehouse_id=amazon_frontend_warehouse.id AND amazon_frontend_order.id={buy.order_id}
# """
# cursor.execute(query)
# row = cursor.fetchone()
# get_truck.order_id = buy.order_id
# get_truck.ups_account = row[0]
# get_truck.warehouse_id = row[1]
# get_truck.location_x = row[2]
# get_truck.location_y = row[3]
# get_truck.destination_x = row[4]
# get_truck.destination_y = row[5]
cursor.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
# send_to_ups_with_lock(Ucommand, ups_socket)
resend_to_world_until_ack_received(Acommand, seqnum, world_socket)
