Skip to content

Sample Project Banking

Jump to bottom
valhuber edited this page Jan 1, 2021 · 6 revisions

Banking

This illustrates complex logic, including multiple sums, integrated with a Python event.

cd examples/banking/tests
python transfer_funds.py

Data Model

Customers have Checking and Savings accounts, with _Trans details for deposits and withdrawals.

Logic

The logic rolls up the transaction / accounts into the customer, and verifies the balance.

It also provides for TRANSFERFUND, explored below.

def activate_basic_rules():

    def transfer_funds(row: TRANSFERFUND, old_row: TRANSFERFUND, logic_row: LogicRow):
        """
        Command Pattern: insert (and save) transfer_funds row

        Rules then adjust Checking and Customer rollups, and validate credit
        """
        if logic_row.ins_upd_dlt == "ins" or True:  # logic engine fills parents for insert
            logic_row.log("Transfer from source to target")
            fromCustNum = row.FromCustNum
            toCustNum = row.ToCustNum
            acctNum = row.FromAcct
            trans_date = datetime.datetime(2020, 10, 1)
            transferAmt = row.TransferAmt
            transID = row.TransId
            # need to lookup the From Acct to see if it is checking or savings - that way we can reverse the flow
            deposit = models.SAVINGSTRANS(TransId=transID, CustNum=toCustNum, AcctNum=acctNum, DepositAmt=transferAmt, WithdrawlAmt=0,
                                          TransDate=trans_date)
            logic_row.insert("Deposit to savings", deposit)
            withdrawal = models.CHECKINGTRANS(TransId=transID, CustNum=fromCustNum, AcctNum=acctNum,
                                             DepositAmt=0, WithdrawlAmt=transferAmt, TransDate=trans_date)
            logic_row.insert("Withdraw from CHECKINGTRANS", withdrawal)

    Rule.sum(derive=CHECKING.Deposits, as_sum_of=CHECKINGTRANS.DepositAmt)
    Rule.sum(derive=CHECKING.Withdrawls, as_sum_of=CHECKINGTRANS.WithdrawlAmt)
    Rule.formula(derive=CHECKING.AvailableBalance, as_expression=lambda  row: row.Deposits - row.Withdrawls)
    Rule.count(derive=CHECKING.ItemCount, as_count_of=CHECKINGTRANS)

    Rule.sum(derive=CUSTOMER.CheckingAcctBal, as_sum_of=CHECKING.AvailableBalance)
    Rule.sum(derive=CUSTOMER.SavingsAcctBal, as_sum_of=SAVING.AvailableBalance)
    Rule.formula(derive=CUSTOMER.TotalBalance, as_expression=lambda row: row.CheckingAcctBal + row.SavingsAcctBal)
    Rule.constraint(validate=CUSTOMER,
                    as_condition=lambda row: row.CheckingAcctBal >= 0,
                    error_msg="Your Checking balance of ({row.CheckingAcctBal}) is less than 0)")
    Rule.constraint(validate=CUSTOMER,
                    as_condition=lambda row: row.SavingsAcctBal >= 0,
                    error_msg="Your Savings balance of ({row.SavingsAcctBal}) is less than 0)")

    Rule.sum(derive=SAVING.Withdrawls, as_sum_of=SAVINGSTRANS.WithdrawlAmt)
    Rule.sum(derive=SAVING.Deposits, as_sum_of=SAVINGSTRANS.DepositAmt)
    Rule.formula(derive=SAVING.AvailableBalance, as_expression=lambda row: row.Deposits - row.Withdrawls)
    Rule.count(derive=SAVING.ItemCount, as_count_of=SAVINGSTRANS)

    Rule.formula(derive=CHECKINGTRANS.Total, as_expression=lambda row: row.DepositAmt - row.WithdrawlAmt)
    Rule.formula(derive=SAVINGSTRANS.Total, as_expression=lambda row: row.DepositAmt - row.WithdrawlAmt)

    Rule.commit_row_event(on_class=TRANSFERFUND, calling=transfer_funds)

Sample Transactions in transfer_funds.py

Let's explore examples/banking/tests/transfer_funds.py

Step 1 - Deposit To Checking - inserts a CHECKINGTRANS 

trans_date = datetime(2020, 10, 1)
deposit = models.CHECKINGTRANS(TransId=1, CustNum=1, AcctNum=1,
                               DepositAmt=100, WithdrawlAmt=0, TransDate=trans_date)
print("\n\nCustomer Account Deposit Checking Setup - Deposit $100 to CHECKINGTRANS ")
session.add(deposit)
session.commit()

Which:

  1. Adjusts derive=CHECKING.Deposits, as_sum_of=CHECKINGTRANS.DepositAmt
  2. Adjusts derive=CHECKING.ItemCount, as_count_of=CHECKINGTRANS
  3. Which recomputes derive=CHECKING.AvailableBalance, as_expression=lambda row: row.Deposits - row.Withdrawls
  4. Which adjusts derive=CUSTOMER.CheckingAcctBal, as_sum_of=CHECKING.AvailableBalance

Visible in the log:

Customer Account Deposit Checking Setup - Deposit $100 to CHECKINGTRANS 

Logic Phase:		BEFORE COMMIT          						 - 2020-10-06 08:01:37,290 - logic_logger - DEBUG
Logic Phase:		ROW LOGIC (sqlalchemy before_flush)			 - 2020-10-06 08:01:37,290 - logic_logger - DEBUG
/Users/val/python/pycharm/Logic-Bank/venv/lib/python3.8/site-packages/sqlalchemy/sql/sqltypes.py:661: SAWarning: Dialect sqlite+pysqlite does *not* support Decimal objects natively, and SQLAlchemy must convert from floating point - rounding errors and other issues may occur. Please consider storing Decimal numbers as strings or integers on this platform for lossless storage.
  util.warn(
..CHECKINGTRANS[1] {Insert - client} AcctNum: 1, ChkNo: None, CustNum: 1, DepositAmt: 100, ImageURL: None, Total: None, TransDate: 2020-10-01 00:00:00, TransId: 1, WithdrawlAmt: 0  row@: 0x10ee03850 - 2020-10-06 08:01:37,292 - logic_logger - DEBUG
..CHECKINGTRANS[1] {Formula Total} AcctNum: 1, ChkNo: None, CustNum: 1, DepositAmt: 100, ImageURL: None, Total: 100, TransDate: 2020-10-01 00:00:00, TransId: 1, WithdrawlAmt: 0  row@: 0x10ee03850 - 2020-10-06 08:01:37,321 - logic_logger - DEBUG
....CHECKING[1 | 1] {Update - Adjusting CHECKING} AcctNum: 1, AcctType: C, AvailableBalance: 0.00, CreditCode: 1, CreditLimit: 1000.00, CurrentBalance: 0.00, CustNum: 1, Deposits:  [0.00-->] 100.00, ItemCount:  [0-->] 1, Withdrawls: 0.00  row@: 0x10f876310 - 2020-10-06 08:01:37,324 - logic_logger - DEBUG
....CHECKING[1 | 1] {Formula AvailableBalance} AcctNum: 1, AcctType: C, AvailableBalance:  [0.00-->] 100.00, CreditCode: 1, CreditLimit: 1000.00, CurrentBalance: 0.00, CustNum: 1, Deposits:  [0.00-->] 100.00, ItemCount:  [0-->] 1, Withdrawls: 0.00  row@: 0x10f876310 - 2020-10-06 08:01:37,325 - logic_logger - DEBUG
......CUSTOMER[1] {Update - Adjusting CUSTOMER} CheckingAcctBal:  [0.00-->] 100.00, City: Daytona Beach, CustNum: 1, Name: Tyler Bank, Phone: 5556078094, SavingsAcctBal: 0.00, State: FL, Street: 1234 N BEACH ST, TotalBalance: 0.00, ZIP: 32174, emailAddress: [email protected]  row@: 0x10f876250 - 2020-10-06 08:01:37,335 - logic_logger - DEBUG
......CUSTOMER[1] {Formula TotalBalance} CheckingAcctBal:  [0.00-->] 100.00, City: Daytona Beach, CustNum: 1, Name: Tyler Bank, Phone: 5556078094, SavingsAcctBal: 0.00, State: FL, Street: 1234 N BEACH ST, TotalBalance:  [0.00-->] 100.00, ZIP: 32174, emailAddress: [email protected]  row@: 0x10f876250 - 2020-10-06 08:01:37,336 - logic_logger - DEBUG
Logic Phase:		COMMIT   									 - 2020-10-06 08:01:37,340 - logic_logger - DEBUG
Logic Phase:		FLUSH   (sqlalchemy flush processing       	 - 2020-10-06 08:01:37,340 - logic_logger - DEBUG

..CUSTOMER[1] {Customer Account Deposit Checking Setup OK - balance is 100} CheckingAcctBal:  [0.00-->] 100.00, City: Daytona Beach, CustNum: 1, Name: Tyler Bank, Phone: 5556078094, SavingsAcctBal: 0.00, State: FL, Street: 1234 N BEACH ST, TotalBalance:  [0.00-->] 100.00, ZIP: 32174, emailAddress: [email protected]  row@: 0x10f876250 - 2020-10-06 08:01:37,358 - logic_logger - DEBUG

Step 2 - Transfer Funds: $10 from checking to savings

Operates as follows:

  1. Client inserts a TRANSFER_FUNDS row
    • Per Command Pattern, this runs transfer_funds() in the Commit Logic Phase
    • It inserts Savings and Checking rows
  2. Sum rules adjust Savings.Deposits
  3. Which chains to adjust Customer.CheckingAcctBal 
"""
    ********* Transfer 10 from checking to savings (main test) *********
"""
transfer = models.TRANSFERFUND(TransId=2, FromCustNum=1, FromAcct=1, ToCustNum=1, ToAcct=1, TransferAmt=10, TransDate=trans_date)
print("\n\nTransfer 10 from checking to savings (main test) ")
session.add(transfer)
session.commit()

The portion of the log from the main test:

Transfer 10 from checking to savings (main test) 

Logic Phase:		BEFORE COMMIT          						 - 2020-10-06 07:57:35,962 - logic_logger - DEBUG
Logic Phase:		ROW LOGIC (sqlalchemy before_flush)			 - 2020-10-06 07:57:35,962 - logic_logger - DEBUG
..TRANSFERFUND[2] {Insert - client} FromAcct: 1, FromCustNum: 1, ToAcct: 1, ToCustNum: 1, TransDate: 2020-10-01 00:00:00, TransId: 2, TransferAmt: 10  row@: 0x10c634280 - 2020-10-06 07:57:35,963 - logic_logger - DEBUG
Logic Phase:		COMMIT   									 - 2020-10-06 07:57:35,975 - logic_logger - DEBUG
..TRANSFERFUND[2] {Commit Event} FromAcct: 1, FromCustNum: 1, ToAcct: 1, ToCustNum: 1, TransDate: 2020-10-01 00:00:00, TransId: 2, TransferAmt: 10  row@: 0x10c634280 - 2020-10-06 07:57:35,976 - logic_logger - DEBUG
..TRANSFERFUND[2] {Transfer from source to target} FromAcct: 1, FromCustNum: 1, ToAcct: 1, ToCustNum: 1, TransDate: 2020-10-01 00:00:00, TransId: 2, TransferAmt: 10  row@: 0x10c634280 - 2020-10-06 07:57:35,977 - logic_logger - DEBUG
....SAVINGSTRANS[2] {Insert - Deposit to savings} AcctNum: 1, CustNum: 1, DepositAmt: 10, Total: None, TransDate: 2020-10-01 00:00:00, TransId: 2, WithdrawlAmt: 0  row@: 0x10c677a00 - 2020-10-06 07:57:35,979 - logic_logger - DEBUG
....SAVINGSTRANS[2] {Formula Total} AcctNum: 1, CustNum: 1, DepositAmt: 10, Total:  [None-->] 10, TransDate: 2020-10-01 00:00:00, TransId: 2, WithdrawlAmt: 0  row@: 0x10c677a00 - 2020-10-06 07:57:35,988 - logic_logger - DEBUG
......SAVING[1 | 1] {Update - Adjusting SAVING} AcctNum: 1, AcctType: S, AvailableBalance: 0.00, CurrentBalance: 0.00, CustNum: 1, Deposits:  [0.00-->] 10.00, ItemCount:  [0-->] 1, Withdrawls: 0.00  row@: 0x10c6888b0 - 2020-10-06 07:57:35,990 - logic_logger - DEBUG
......SAVING[1 | 1] {Formula AvailableBalance} AcctNum: 1, AcctType: S, AvailableBalance:  [0.00-->] 10.00, CurrentBalance: 0.00, CustNum: 1, Deposits:  [0.00-->] 10.00, ItemCount:  [0-->] 1, Withdrawls: 0.00  row@: 0x10c6888b0 - 2020-10-06 07:57:35,990 - logic_logger - DEBUG
........CUSTOMER[1] {Update - Adjusting CUSTOMER} CheckingAcctBal: 100.00, City: Daytona Beach, CustNum: 1, Name: Tyler Bank, Phone: 5556078094, SavingsAcctBal:  [0.00-->] 10.00, State: FL, Street: 1234 N BEACH ST, TotalBalance: 100.00, ZIP: 32174, emailAddress: tyler.bank@acme.com  row@: 0x10c6775b0 - 2020-10-06 07:57:35,993 - logic_logger - DEBUG
........CUSTOMER[1] {Formula TotalBalance} CheckingAcctBal: 100.00, City: Daytona Beach, CustNum: 1, Name: Tyler Bank, Phone: 5556078094, SavingsAcctBal:  [0.00-->] 10.00, State: FL, Street: 1234 N BEACH ST, TotalBalance:  [100.00-->] 110.00, ZIP: 32174, emailAddress: tyler.bank@acme.com  row@: 0x10c6775b0 - 2020-10-06 07:57:35,993 - logic_logger - DEBUG
....CHECKINGTRANS[2] {Insert - Withdraw from CHECKINGTRANS} AcctNum: 1, ChkNo: None, CustNum: 1, DepositAmt: 0, ImageURL: None, Total: None, TransDate: 2020-10-01 00:00:00, TransId: 2, WithdrawlAmt: 10  row@: 0x10c677280 - 2020-10-06 07:57:35,996 - logic_logger - DEBUG
....CHECKINGTRANS[2] {Formula Total} AcctNum: 1, ChkNo: None, CustNum: 1, DepositAmt: 0, ImageURL: None, Total:  [None-->] -10, TransDate: 2020-10-01 00:00:00, TransId: 2, WithdrawlAmt: 10  row@: 0x10c677280 - 2020-10-06 07:57:36,000 - logic_logger - DEBUG
......CHECKING[1 | 1] {Update - Adjusting CHECKING} AcctNum: 1, AcctType: C, AvailableBalance: 100.00, CreditCode: 1, CreditLimit: 1000.00, CurrentBalance: 0.00, CustNum: 1, Deposits: 100.00, ItemCount:  [1-->] 2, Withdrawls:  [0.00-->] 10.00  row@: 0x10c634310 - 2020-10-06 07:57:36,001 - logic_logger - DEBUG
......CHECKING[1 | 1] {Formula AvailableBalance} AcctNum: 1, AcctType: C, AvailableBalance:  [100.00-->] 90.00, CreditCode: 1, CreditLimit: 1000.00, CurrentBalance: 0.00, CustNum: 1, Deposits: 100.00, ItemCount:  [1-->] 2, Withdrawls:  [0.00-->] 10.00  row@: 0x10c634310 - 2020-10-06 07:57:36,002 - logic_logger - DEBUG
........CUSTOMER[1] {Update - Adjusting CUSTOMER} CheckingAcctBal:  [100.00-->] 90.00, City: Daytona Beach, CustNum: 1, Name: Tyler Bank, Phone: 5556078094, SavingsAcctBal: 10.00, State: FL, Street: 1234 N BEACH ST, TotalBalance: 110.00, ZIP: 32174, emailAddress: tyler.bank@acme.com  row@: 0x10c6775b0 - 2020-10-06 07:57:36,003 - logic_logger - DEBUG
........CUSTOMER[1] {Formula TotalBalance} CheckingAcctBal:  [100.00-->] 90.00, City: Daytona Beach, CustNum: 1, Name: Tyler Bank, Phone: 5556078094, SavingsAcctBal: 10.00, State: FL, Street: 1234 N BEACH ST, TotalBalance:  [110.00-->] 100.00, ZIP: 32174, emailAddress: tyler.bank@acme.com  row@: 0x10c6775b0 - 2020-10-06 07:57:36,004 - logic_logger - DEBUG
Logic Phase:		FLUSH   (sqlalchemy flush processing       	 - 2020-10-06 07:57:36,005 - logic_logger - DEBUG
Developed with PyCharm

Summary, Revision History

Tutorial

Overview

Rule Engines

User Project Operations

Install

Flask AppBuilder Integration

Managing Rules

Rule Summary

Declarative

Extensibility

Referential Integrity

Denormalization

Manual Code Comparison

Examples

Check Credit

Sufficient Raise

Ship Order

Orders for Commissioned Employees Only

Allocation

Exceptions

Banking

Logic Bank Internals

Logic Walk-through

Install and DevOps

Clone this wiki locally