def handle_item(i):
nonlocal total_amount
# for i in range(num_items):
# Step 5a
s_quantity = utils.single_select(
session,
'SELECT S_QUANTITY FROM stock WHERE S_W_ID = %s AND S_I_ID = %s',
(supplier_warehouse[i], item_number[i]))
# Step 5b
adjusted_qty[i] = s_quantity - quantity[i]
# Step 5c
if adjusted_qty[i] < 10:
adjusted_qty[i] += 100
# Step 5d
utils.do_query(
session,
'UPDATE stock SET S_QUANTITY = %s WHERE S_W_ID = %s AND S_I_ID = %s',
(adjusted_qty[i], supplier_warehouse[i], item_number[i]))
utils.do_query(
session, '''
UPDATE stock_counters
SET S_YTD_CHANGE = S_YTD_CHANGE + %s,
S_ORDER_CNT_CHANGE = S_ORDER_CNT_CHANGE + 1
WHERE S_W_ID = %s AND S_I_ID = %s
''', (quantity[i], supplier_warehouse[i], item_number[i]))
if supplier_warehouse[i] != w_id:
utils.do_query(
session, '''
UPDATE stock_counters SET S_REMOTE_CNT_CHANGE = S_REMOTE_CNT_CHANGE + 1
WHERE S_W_ID = %s AND S_I_ID = %s
''', (supplier_warehouse[i], item_number[i]))
# Step 5e
i_price = utils.single_select(
session, 'SELECT I_PRICE FROM item WHERE I_ID = %s',
(item_number[i], ))
item_amount[i] = quantity[i] * i_price
# Step 5f
total_amount += item_amount[i]
# Step 5g
dist_name = 'S_DIST_' + str(d_id)
dist_info = utils.single_select(
session,
'SELECT {} FROM stock WHERE S_W_ID = {} AND S_I_ID = {}'.format(
dist_name, supplier_warehouse[i], item_number[i]))
utils.do_query(
session, '''
INSERT INTO order_line (OL_O_ID, OL_D_ID, OL_W_ID, OL_NUMBER, OL_I_ID, OL_SUPPLY_W_ID, OL_QUANTITY, OL_AMOUNT, OL_DIST_INFO)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
''', (n, d_id, w_id, i, item_number[i], supplier_warehouse[i],
quantity[i], item_amount[i], dist_info))
# Populate the item_orders table for each item-order pair
utils.do_query(session,
cql_insert_item_orders,
(w_id, item_number[i], n, d_id, c_id),
query_type='write')
def handle_district(district_no):
rows = utils.do_query(
session, '''
SELECT MIN(O_ID), O_C_ID FROM orders WHERE O_W_ID = %s AND O_D_ID = %s AND O_CARRIER_ID = 'null' ALLOW FILTERING
''', (w_id, district_no))
n = None
c = None
for row in rows:
n = row[0]
c = row[1]
break
if n == None: # no undelivered orders in this district
return
utils.do_query(
session, '''
UPDATE orders SET O_CARRIER_ID = '%s'
WHERE O_W_ID = %s AND O_D_ID = %s AND O_ID = %s
''', (carrier_id, w_id, district_no, n))
current_datetime = datetime.now()
current_datetime_str = current_datetime.strftime(
'%Y-%m-%d %H:%M:%S.%f')
ol_cnt = utils.single_select(
session,
'SELECT O_OL_CNT FROM orders WHERE O_W_ID = %s AND O_D_ID = %s AND O_ID = %s',
(w_id, district_no, n))
for i in range(1, ol_cnt + 1):
utils.do_query(
session, '''
UPDATE order_line
SET OL_DELIVERY_D = %s
WHERE OL_W_ID = %s AND OL_D_ID = %s AND OL_O_ID = %s AND OL_NUMBER = %s
''', (current_datetime_str, w_id, district_no, n, i))
sum_ol_amount = utils.single_select(
session, '''
SELECT SUM(OL_AMOUNT) FROM order_line
WHERE OL_W_ID = %s AND OL_D_ID = %s AND OL_O_ID = %s
''', (w_id, district_no, n))
utils.do_query(
session, '''
UPDATE customer_counters
SET C_BALANCE_CHANGE = C_BALANCE_CHANGE + %s,
C_DELIVERY_CNT_CHANGE = C_DELIVERY_CNT_CHANGE + 1
WHERE C_W_ID = %s AND C_D_ID = %s AND C_ID = %s
''', (int(sum_ol_amount * 100), w_id, district_no, c))
balance = utils.single_select(
session,
'SELECT C_BALANCE FROM customer_initial WHERE C_W_ID = %s AND C_D_ID = %s AND C_ID = %s',
(w_id, district_no, c))
balance += Decimal(
utils.single_select(
session,
'SELECT C_BALANCE_CHANGE FROM customer_counters WHERE C_W_ID = %s AND C_D_ID = %s AND C_ID = %s',
(w_id, district_no, c))) / Decimal(100)
utils.do_query(
session,
'UPDATE customer SET C_BALANCE = %s WHERE C_W_ID = %s AND C_D_ID = %s AND C_ID = %s',
(balance, w_id, district_no, c))
def get_customers_from_warehouse(session, cql_get, cql_insert, w_id, d_id,
c_id, other_w, item_number):
customers = utils.do_query(session,
cql_get, (other_w, item_number),
query_type='read')
relevant_customers = [c for c in customers]
counter = Counter([(c.c_id, c.o_id, c.d_id) for c in relevant_customers])
related_customers = [c for c in counter if counter[c] > 1]
for rc in related_customers:
utils.do_query(session, cql_insert,
(w_id, d_id, c_id, other_w, rc[1], rc[0]), 'write')
utils.do_query(session, cql_insert,
(other_w, rc[1], rc[0], w_id, d_id, c_id), 'write')
def order_status(session, c_w_id, c_d_id, c_id):
output = {}
rows = utils.do_query(
session,
'SELECT C_FIRST, C_MIDDLE, C_LAST, C_BALANCE FROM customer WHERE C_W_ID = %s AND C_D_ID = %s AND C_ID = %s',
(c_w_id, c_d_id, c_id))
for row in rows:
output['c_first'] = row.c_first
output['c_middle'] = row.c_middle
output['c_last'] = row.c_last
output['c_balance'] = row.c_balance
break
rows = utils.do_query(
session,
'SELECT O_ID, O_ENTRY_D, O_CARRIER_ID FROM customer_orders WHERE O_W_ID = %s AND O_D_ID = %s AND O_C_ID = %s LIMIT 1',
(c_w_id, c_d_id, c_id)) # max O_ID
for row in rows:
output['o_id'] = row[0]
output['o_entry_d'] = row.o_entry_d
output['o_carrier_id'] = row.o_carrier_id
break
o_id = output['o_id']
rows = utils.do_query(
session, '''
SELECT OL_I_ID, OL_SUPPLY_W_ID, OL_QUANTITY, OL_AMOUNT, OL_DELIVERY_D
FROM order_line
WHERE OL_W_ID = %s AND OL_D_ID = %s AND OL_O_ID = %s
''', (c_w_id, c_d_id, o_id))
output['order_lines'] = []
for row in rows:
d = {
'ol_i_id': row[0],
'ol_supply_w_id': row[1],
'ol_quantity': row[2],
'ol_amount': row[3],
'ol_delivery_d': row[4]
}
output['order_lines'].append(d)
return output
def populate_related_customers(session, w_id, d_id, c_id, item_number):
global all_warehouse
if len(all_warehouse) == 0:
warehouses = utils.do_query(
session, 'SELECT W_ID FROM warehouse ALLOW FILTERING')
all_warehouse = [w.w_id for w in warehouses]
cql_get = session.prepare(
"SELECT C_ID, D_ID, O_ID FROM item_orders WHERE W_ID = ? AND I_ID IN ?"
)
cql_insert = session.prepare(
"INSERT INTO related_customers (C_W_ID, C_D_ID, C_ID, R_W_ID, R_D_ID, R_ID) VALUES (?, ?, ?, ?, ?, ?)"
)
for w in all_warehouse:
if w == w_id:
continue
get_customers_from_warehouse(session, cql_get, cql_insert, w_id, d_id,
c_id, w, item_number)
def related_customer(session, w, d, c):
cql_get_customers = 'SELECT C_W_ID, C_D_ID, C_ID, R_W_ID, R_D_ID, R_ID FROM related_customers ' \
'WHERE C_W_ID = {w} AND C_D_ID = {d} AND C_ID = {c}'
customers = do_query(session, cql_get_customers.format(w=w, d=d, c=c))
return (w, d, c), [(c.r_w_id, c.r_d_id, c.r_id) for c in customers]
def payment(session, c_w_id, c_d_id, c_id, amount):
cents = int(amount * 100)
utils.do_query(
session,
'UPDATE warehouse_counters SET W_YTD_CHANGE = W_YTD_CHANGE + %s WHERE W_ID = %s',
(cents, c_w_id))
utils.do_query(
session,
'UPDATE district_counters SET D_YTD_CHANGE = D_YTD_CHANGE + %s WHERE D_W_ID = %s AND D_ID = %s',
(cents, c_w_id, c_d_id))
utils.do_query(
session, '''
UPDATE customer_counters
SET C_BALANCE_CHANGE = C_BALANCE_CHANGE - %s,
C_YTD_PAYMENT_CHANGE = C_YTD_PAYMENT_CHANGE + %s,
C_PAYMENT_CNT_CHANGE = C_PAYMENT_CNT_CHANGE + 1
WHERE C_W_ID = %s AND C_D_ID = %s AND C_ID = %s
''', (cents, cents, c_w_id, c_d_id, c_id))
balance = utils.single_select(
session,
'SELECT C_BALANCE FROM customer_initial WHERE C_W_ID = %s AND C_D_ID = %s AND C_ID = %s',
(c_w_id, c_d_id, c_id))
balance += Decimal(
utils.single_select(
session,
'SELECT C_BALANCE_CHANGE FROM customer_counters WHERE C_W_ID = %s AND C_D_ID = %s AND C_ID = %s',
(c_w_id, c_d_id, c_id))) / Decimal(100)
utils.do_query(
session,
'UPDATE customer SET C_BALANCE = %s WHERE C_W_ID = %s AND C_D_ID = %s AND C_ID = %s',
(balance, c_w_id, c_d_id, c_id))
output = {}
rows = utils.do_query(
session, '''
SELECT C_W_ID, C_D_ID, C_ID, C_FIRST, C_MIDDLE, C_LAST, C_STREET_1, C_STREET_2,
C_CITY, C_STATE, C_ZIP, C_PHONE, C_SINCE, C_CREDIT, C_CREDIT_LIM, C_DISCOUNT, C_BALANCE
FROM customer
WHERE C_W_ID = %s AND C_D_ID = %s AND C_ID = %s
''', (c_w_id, c_d_id, c_id))
for row in rows:
c = row._asdict()
c['c_balance'] -= amount
output['customer_info'] = c
break
rows = utils.do_query(
session,
'SELECT W_STREET_1, W_STREET_2, W_CITY, W_STATE, W_ZIP FROM warehouse WHERE W_ID = %s',
(c_w_id, ))
for row in rows:
output['warehouse_info'] = row._asdict()
break
rows = utils.do_query(
session,
'SELECT D_STREET_1, D_STREET_2, D_CITY, D_STATE, D_ZIP FROM district WHERE D_W_ID = %s AND D_ID = %s',
(c_w_id, c_d_id))
for row in rows:
output['district_info'] = row._asdict()
break
return output
def new_order(session, w_id, d_id, c_id, num_items, item_number,
supplier_warehouse, quantity):
# Step 1
n = 0
n = utils.single_select(
session,
'SELECT D_O_ID_OFST from district WHERE D_W_ID = %s AND D_ID = %s',
(w_id, d_id))
n += utils.single_select(
session,
'SELECT D_O_COUNTER from district_counters WHERE D_W_ID = %s AND D_ID = %s',
(w_id, d_id))
# Step 2
utils.do_query(
session,
'UPDATE district_counters SET D_O_COUNTER = D_O_COUNTER + 1 WHERE D_W_ID = %s AND D_ID = %s',
(w_id, d_id))
# Step 3
all_local = 1
for i in range(num_items):
if supplier_warehouse[i] != w_id:
all_local = 0
break
current_datetime = datetime.now()
# Step 4 & 5
total_amount = 0
item_amount = [0] * num_items
adjusted_qty = [0] * num_items
cql_insert_item_orders = session.prepare(
"INSERT INTO item_orders (W_ID, I_ID, O_ID, D_ID, C_ID) VALUES (?, ?, ?, ?, ?)"
)
def handle_item(i):
nonlocal total_amount
# for i in range(num_items):
# Step 5a
s_quantity = utils.single_select(
session,
'SELECT S_QUANTITY FROM stock WHERE S_W_ID = %s AND S_I_ID = %s',
(supplier_warehouse[i], item_number[i]))
# Step 5b
adjusted_qty[i] = s_quantity - quantity[i]
# Step 5c
if adjusted_qty[i] < 10:
adjusted_qty[i] += 100
# Step 5d
utils.do_query(
session,
'UPDATE stock SET S_QUANTITY = %s WHERE S_W_ID = %s AND S_I_ID = %s',
(adjusted_qty[i], supplier_warehouse[i], item_number[i]))
utils.do_query(
session, '''
UPDATE stock_counters
SET S_YTD_CHANGE = S_YTD_CHANGE + %s,
S_ORDER_CNT_CHANGE = S_ORDER_CNT_CHANGE + 1
WHERE S_W_ID = %s AND S_I_ID = %s
''', (quantity[i], supplier_warehouse[i], item_number[i]))
if supplier_warehouse[i] != w_id:
utils.do_query(
session, '''
UPDATE stock_counters SET S_REMOTE_CNT_CHANGE = S_REMOTE_CNT_CHANGE + 1
WHERE S_W_ID = %s AND S_I_ID = %s
''', (supplier_warehouse[i], item_number[i]))
# Step 5e
i_price = utils.single_select(
session, 'SELECT I_PRICE FROM item WHERE I_ID = %s',
(item_number[i], ))
item_amount[i] = quantity[i] * i_price
# Step 5f
total_amount += item_amount[i]
# Step 5g
dist_name = 'S_DIST_' + str(d_id)
dist_info = utils.single_select(
session,
'SELECT {} FROM stock WHERE S_W_ID = {} AND S_I_ID = {}'.format(
dist_name, supplier_warehouse[i], item_number[i]))
utils.do_query(
session, '''
INSERT INTO order_line (OL_O_ID, OL_D_ID, OL_W_ID, OL_NUMBER, OL_I_ID, OL_SUPPLY_W_ID, OL_QUANTITY, OL_AMOUNT, OL_DIST_INFO)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
''', (n, d_id, w_id, i, item_number[i], supplier_warehouse[i],
quantity[i], item_amount[i], dist_info))
# Populate the item_orders table for each item-order pair
utils.do_query(session,
cql_insert_item_orders,
(w_id, item_number[i], n, d_id, c_id),
query_type='write')
pool = ThreadPool(8)
pool.map(handle_item, range(num_items))
pool.close()
# Create order after creating all order-lines, so that when querying for popular items there will be no error
utils.do_query(
session, '''
INSERT INTO orders (O_ID, O_D_ID, O_W_ID, O_C_ID, O_ENTRY_D, O_OL_CNT, O_ALL_LOCAL)
VALUES (%s, %s, %s, %s, %s, %s, %s)
''', (n, d_id, w_id, c_id, datetime.now(), num_items, all_local))
# Step 6
w_tax = utils.single_select(session,
'SELECT W_TAX FROM warehouse WHERE W_ID = %s',
(w_id, ))
d_tax = utils.single_select(
session, 'SELECT D_TAX FROM district WHERE D_W_ID = %s AND D_ID = %s',
(w_id, d_id))
c_discount = utils.single_select(
session,
'SELECT C_DISCOUNT FROM customer WHERE C_W_ID = %s AND C_D_ID = %s AND C_ID = %s',
(w_id, d_id, c_id))
total_amount *= (1 + d_tax + w_tax) * (1 - c_discount)
# Output
output = {}
output['w_id'] = w_id
output['d_id'] = d_id
output['c_id'] = c_id
rows = utils.do_query(
session,
'SELECT C_LAST, C_CREDIT, C_DISCOUNT FROM customer WHERE C_W_ID = %s AND C_D_ID = %s AND C_ID = %s',
(w_id, d_id, c_id))
for row in rows:
output['c_last'] = row.c_last
output['c_credit'] = row.c_credit
output['c_discount'] = row.c_discount
break
output['w_tax'] = w_tax
output['d_tax'] = d_tax
output['o_id'] = n
output['o_entry_d'] = current_datetime
output['num_items'] = num_items
output['total_amount'] = total_amount
output['item_infos'] = []
for i in range(num_items):
i_name = utils.single_select(
session, 'SELECT I_NAME FROM item WHERE I_ID = %s',
(item_number[i], ))
output['item_infos'].append(
(item_number[i], i_name, supplier_warehouse[i], quantity[i],
item_amount[i], adjusted_qty[i]))
return output