def toggle_order_shipped():
""" toggle Shipped Date, to trigger balance adjustment """
""" also test join.
session.query(Customer).join(Invoice).filter(Invoice.amount == 8500).all()
"""
pre_cust = session.query(models.Customer).filter(models.Customer.Id == "ALFKI").one()
session.expunge(pre_cust)
print("")
test_order = session.query(models.Order).filter(models.Order.Id == 11011).join(models.Employee).one()
if test_order.ShippedDate is None or test_order.ShippedDate == "":
# with restored db, cust[ALFKI] has bal 960 & 3 unpaid orders, Order[11011) is 960, unshipped
test_order.ShippedDate = str(datetime.now())
print(prt("Shipping order - ShippedDate: ['' -> " + test_order.ShippedDate + "]" +
" for customer balance: " + str(pre_cust.Balance) +
", with UnpaidOrderCount: " + str(pre_cust.UnpaidOrderCount)))
else:
test_order.ShippedDate = None
print(prt("Returning order - ShippedDate: [ -> None]"))
insp = inspect(test_order)
# session.commit()
print("")
post_cust = session.query(models.Customer).filter(models.Customer.Id == "ALFKI").one()
logic_row = LogicRow(row=post_cust, old_row=pre_cust, ins_upd_dlt="*", nest_level=0, a_session=session, row_sets=None)
if abs(post_cust.Balance - pre_cust.Balance) == 960:
logic_row.log("Correct adjusted Customer Result")
assert True
else:
row_prt(post_cust, "\nERROR - incorrect adjusted Customer Result")
print("\n--> probable cause: Order customer update not written")
row_prt(pre_cust, "\npre_alfki")
assert False
def toggle_order_shipped():
"""
toggle Shipped Date, to trigger
* balance adjustment
* cascade to OrderDetails
* and Product adjustment
also test join
"""
pre_cust = session.query(
models.Customer).filter(models.Customer.Id == "ALFKI").one()
session.expunge(pre_cust)
print("")
test_order = session.query(models.Order).filter(
models.Order.Id == 11011).join(models.Employee).one()
if test_order.ShippedDate is None or test_order.ShippedDate == "":
test_order.ShippedDate = str(datetime.now())
print(
prt("Shipping order - ShippedDate: ['' -> " +
test_order.ShippedDate + "]"))
else:
test_order.ShippedDate = None
print(prt("Returning order - ShippedDate: [ -> None]"))
insp = inspect(test_order)
session.commit()
print("")
post_cust = session.query(
models.Customer).filter(models.Customer.Id == "ALFKI").one()
logic_row = LogicRow(row=post_cust,
old_row=pre_cust,
ins_upd_dlt="*",
nest_level=0,
a_session=session,
row_sets=None)
if abs(post_cust.Balance - pre_cust.Balance) == 960:
logic_row.log("Correct adjusted Customer Result")
assert True
else:
logic_row.log(post_cust, "ERROR - incorrect adjusted Customer Result")
assert False
if post_cust.Balance == 0:
pass
else:
logic_row.log("ERROR - balance should be 0")
assert False
if post_cust.UnpaidOrderCount == 2 and pre_cust.UnpaidOrderCount == 3:
pass
else:
logic_row.log("Error - UnpaidOrderCount should be 2")
assert False
import sqlalchemy_utils
import nw.nw_logic.models as models
from python_rules.exec_row_logic.logic_row import LogicRow
from python_rules.util import row_prt, prt
from nw.nw_logic import session # opens db, activates logic listener <--
cls = sqlalchemy_utils.functions.get_class_by_table(models.Base,
"Product",
data=None)
# Add Order - works
pre_cust = session.query(
models.Customer).filter(models.Customer.Id == "ALFKI").one()
session.expunge(pre_cust)
# First, try one to fail
bad_order = models.Order(AmountTotal=0,
CustomerId="ALFKI",
ShipCity="Richmond",
EmployeeId=6,
Freight=1)
session.add(bad_order)
# OrderDetails - https://docs.sqlalchemy.org/en/13/orm/backref.html
bad_item1 = models.OrderDetail(ProductId=1,
Amount=0,
Quantity=1,
UnitPrice=18,
Discount=0)
bad_order.OrderDetailList.append(bad_item1)
path_info = "Run Environment info...\n\n"\
+ "Current Working Directory: " + cwd + "\n\n"\
+ "sys.path: (Python imports)\n" + sys_path + "\n"\
+ "From: " + sys.argv[0] + "\n\n"\
+ "Using Python: " + sys.version + "\n\n"\
+ "At: " + str(datetime.now()) + "\n\n"
print("\n" + path_info + "\n\n")
import nw.nw_logic.models as models
from logic_engine.exec_row_logic.logic_row import LogicRow
from logic_engine.util import row_prt, prt
from nw import nw_logic
from nw.nw_logic import session # opens db, activates logic listener <--
""" toggle order's customer to ANATR, to verify no effect on Customer, OrderDetails """
pre_alfki = session.query(
models.Customer).filter(models.Customer.Id == "ALFKI").one()
pre_anatr = session.query(
models.Customer).filter(models.Customer.Id == "ANATR").one()
session.expunge(pre_alfki)
session.expunge(pre_anatr)
print("")
test_order = session.query(
models.Order).filter(models.Order.Id == 11011).one() # type : Order
amount_total = test_order.AmountTotal
if test_order.CustomerId == "ALFKI":
test_order.CustomerId = "ANATR"
else:
test_order.CustomerId = "ALFKI"
print(prt("Reparenting order - new CustomerId: " + test_order.CustomerId))
insp = inspect(test_order)
path_info = "Run Environment info...\n\n"\
+ "Current Working Directory: " + cwd + "\n\n"\
+ "sys.path: (Python imports)\n" + sys_path + "\n"\
+ "From: " + sys.argv[0] + "\n\n"\
+ "Using Python: " + sys.version + "\n\n"\
+ "At: " + str(datetime.now()) + "\n\n"
print("\n" + path_info + "\n\n")
import nw.nw_logic.models as models
from python_rules.exec_row_logic.logic_row import LogicRow
from python_rules.util import row_prt, prt
from nw import nw_logic
from nw.nw_logic import session # opens db, activates logic listener <--
""" toggle Due Date, to verify no effect on Customer, OrderDetails """
""" also test join.
session.query(Customer).join(Invoice).filter(Invoice.amount == 8500).all()
"""
pre_cust = session.query(
models.Customer).filter(models.Customer.Id == "ALFKI").one()
session.expunge(pre_cust)
print("")
test_order = session.query(models.Order).filter(models.Order.Id == 11011).join(
models.Employee).one()
if test_order.RequiredDate is None or test_order.RequiredDate == "":
test_order.RequiredDate = str(datetime.now())
print(
prt("Shipping order - RequiredDate: ['' -> " +
test_order.RequiredDate + "]"))
models.Product).filter(models.Product.Id == 58).one()
logic_row = LogicRow(row=post_adjusted_product,
old_row=pre_adjusted_product,
ins_upd_dlt="*",
nest_level=0,
a_session=session,
row_sets=None)
if post_adjusted_product.UnitsShipped == pre_adjusted_product.UnitsShipped + 40:
logic_row.log("Product adjusted properly on ship order")
else:
logic_row.log("Product adjusted improperly on ship order")
assert False
toggle_order_shipped()
pre_adjusted_product = session.query(
models.Product).filter(models.Product.Id == 58).one()
session.expunge(pre_adjusted_product)
print("\ndlt_order, shipped... now delete")
delete_cust = session.query(
models.Customer).filter(models.Customer.Id == "ALFKI").one()
session.delete(delete_cust)
session.commit()
post_adjusted_product = session.query(
models.Product).filter(models.Product.Id == 58).one()
logic_row = LogicRow(row=post_adjusted_product,
old_row=pre_adjusted_product,
ins_upd_dlt="*",
nest_level=0,
a_session=session,
row_sets=None)
import os
import sqlalchemy
import nw.nw_logic.models as models
from nw.nw_logic import session # opens db, activates logic listener <--
# first delete, so can add
delete_cust = session.query(
models.Customer).filter(models.Customer.Id == "$$New Cust").delete()
print("\nadd_cust, deleting: " + str(delete_cust) + "\n\n")
session.commit()
# Add a Customer - works
new_cust = models.Customer(Id="$$New Cust", )
session.add(new_cust)
session.commit()
verify_cust = session.query(
models.Customer).filter(models.Customer.Id == "$$New Cust").one()
print("\nadd_cust, completed: " + str(verify_cust) + "\n\n")
assert True