------------------------------------------------------------------
--                                                              --
--  File:   TradeOrder_Iso1_2.SQL                               --
--          Microsoft TPC-E Benchmark Kit Ver. 1.12.0-1025      --
--          Copyright Microsoft  2010, 2011, 2012               --
--                                                              --
--  Implements TPC-E Isolation test #1                          --
--  P3 Test in Read-Write                                       --
--                                                              --
------------------------------------------------------------------
USE tpce
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS(  SELECT  name
            FROM    sysobjects
            WHERE   name    = 'TradeOrder_Iso1_2' AND
                    type    = 'P')
DROP PROCEDURE  TradeOrder_Iso1_2
GO

--
-- Trade Order Frame1 is responsible for retrieving 
-- basic information on the customer, it's 
-- account and it's broker.
--
CREATE PROCEDURE    TradeOrder_Iso1_2
                        @acct_id        bigint,
                        @symbol         varchar(15)
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

    BEGIN TRANSACTION
    
    DECLARE 
            --- Frame 2/3/4 Variables ---
            @exec_f_name        varchar(20),
            @exec_l_name        varchar(25),
            @exec_tax_id        varchar(20),
            --- Frame 3/4 Variables ---
            @cust_id            bigint,
            @cust_tier          tinyint,
            @is_lifo            tinyint,
            @issue              char(8),
            @st_pending_id      char(4),
            @st_submitted_id    char(4),
            @tax_status         tinyint,
            @trade_qty          numeric(6),
            @trade_type_id      char(3),
            @type_is_margin     tinyint,
            @co_name            varchar(60),
            @requested_price    numeric(8,2),
            @comm_rate          numeric(10,2),
            @charge_amount      numeric(10,2),
            @tax_amount         numeric(10,2),
            @acct_assets        numeric(12,2),
            @status_id          char(4),
            @type_is_market     tinyint,
            @type_is_sell       tinyint,
            @buy_value          numeric(10,2),
            @market_price       numeric(8,2),
            @s_name             varchar(70),
            @sell_value         numeric(10,2),
            @co_id              bigint,
            @exch_id            char(6),
            @hold_price         numeric(8,2),
            @hold_qty           numeric(6),
            @needed_qty         numeric(6),
            @hs_qty             numeric(12),
            @tax_rates          numeric(6,5),
            @acct_bal           numeric(12,2),
            @hold_assets        numeric(12,2),
            --- Frame 4 Variables ---
            @broker_id          bigint,
            @exec_name          varchar(64),
            @is_cash            tinyint,
            @comm_amount        numeric(10,2),
            @trade_dts          datetime,
            @t_id               bigint,
            --- ACID Variables ---
            @found_row_flag     tinyint,
            @num_companies      bigint,
            @rand               bigint,
            @company_id         bigint

    --- ACID CODE BEGIN ---
    PRINT   'Begin Trade Order Frame 1 '
    SELECT  CONVERT(char(30), GETDATE(), 21)
    PRINT   ' '
    PRINT   'Variables used:'
    SELECT  @acct_id     AS 'Customer Account ID'
    PRINT   ' '
    --- reset the found row flag
    SELECT  @issue          = S_ISSUE,
            @co_name        = CO_NAME
    FROM    SECURITY JOIN
            COMPANY ON S_CO_ID = CO_ID
    WHERE   S_SYMB          = @symbol
    ---ACID CODE END ---


    SELECT  CA_B_ID,
            CA_C_ID,
            CA_NAME,
            CA_TAX_ST,
            C_L_NAME,
            C_F_NAME,
            C_TAX_ID,
            C_TIER,
            B_NAME
    FROM    CUSTOMER_ACCOUNT WITH (UPDLOCK) JOIN
            CUSTOMER ON CA_C_ID = C_ID JOIN
            BROKER ON CA_B_ID = B_ID
    WHERE   CA_ID   = @acct_id
    

    --- ACID CODE BEGIN ---
    PRINT   'End of Trade Order Frame 1 '
    SELECT  CONVERT(char(30), GETDATE(), 21)
    PRINT   ' '
    --- get the customer's name to use as the executor
    SELECT  @exec_f_name    = C_F_NAME,
            @exec_l_name    = C_L_NAME,
            @exec_tax_id    = C_TAX_ID,
            @cust_id        = C_ID,
            @cust_tier      = C_TIER,
            @tax_status     = CA_TAX_ST,
            @broker_id      = CA_B_ID
    FROM    CUSTOMER_ACCOUNT JOIN
            CUSTOMER ON CA_C_ID = C_ID
    WHERE   CA_ID           = @acct_id
    --- ACID CODE END ---

    --
    -- Trade Order Frame2 is responsible for verifying 
    -- that the executor has the necessary permissions 
    -- to submit the trade.
    --
    --- ACID CODE BEGIN ---
    PRINT   'Begin Trade Order Frame 2 '
    SELECT  CONVERT(char(30), GETDATE(), 21)
    PRINT   ' '
    PRINT   'Variables used:'
    SELECT  @acct_id     AS 'Customer Account ID',
            @exec_f_name As 'Executor First Name',
            @exec_l_name As 'Executor Last Name',
            @exec_tax_id As 'Executor Tax ID'
    PRINT   ' '
    --- ACID CODE END ---

    SELECT  AP_ACL 
    FROM    ACCOUNT_PERMISSION 
    WHERE   AP_CA_ID    = @acct_id AND
            AP_L_NAME   = @exec_l_name AND
            AP_F_NAME   = @exec_f_name AND
            AP_TAX_ID   = @exec_tax_id

    --- ACID CODE BEGIN ---
    PRINT   'End of Trade Order Frame 2 '
    SELECT  CONVERT(char(30), GETDATE(), 21)
    PRINT   ' '
    SELECT  @is_lifo            = 0,
            @st_pending_id      = 'PNDG',
            @st_submitted_id    = 'SBMT',
            @trade_qty          = 100,
            @trade_type_id      = 'TMB',
            @type_is_margin     = 0,
            @requested_price    = 99.99

    SELECT  @co_name            = CO_NAME
    FROM    SECURITY JOIN COMPANY ON S_CO_ID = CO_ID
    WHERE   S_SYMB              = @symbol
    --- ACID CODE END ---

    --
    -- Trade Order Frame3 is responsible for retrieving 
    -- and computing all financial data, including 
    -- cash calance, holdings, commission, charges 
    -- and taxes.
    --
    --- ACID CODE BEGIN ---
    PRINT   'Begin Trade Order Frame 3 '
    SELECT  CONVERT(char(30), GETDATE(), 21)
    PRINT   ' '
    PRINT   'Variables used:'
    SELECT  @acct_id            AS 'Customer Account ID',
            @cust_id            AS 'Customer ID',
            @cust_tier          AS 'Customer Tier',
            @is_lifo            AS 'Is LIFO Flag',
            @st_pending_id      AS 'Status Pending ID',
            @st_submitted_id    AS 'Status Submitted ID',
            @tax_status         AS 'Tax Staus Code',
            @trade_qty          AS 'Quantity of Trade',
            @trade_type_id      AS 'Trade Type ID',
            @type_is_margin     AS 'Type is Margin Code',
            @co_name            AS 'Company Name',
            @requested_price    AS 'Requested Price',
            @symbol             AS 'Security Symbol'
    PRINT   ' '
    --- ACID CODE END ---

    -- Initialize variables
    SELECT  @buy_value = 0,
            @sell_value = 0,
            @needed_qty = @trade_qty

    IF (@symbol IS NULL)
        BEGIN
            SELECT  @co_id  = CO_ID
            FROM    COMPANY
            WHERE   CO_NAME = @co_name
		
            SELECT  @exch_id    = S_EX_ID,
                    @s_name     = S_NAME,
                    @symbol     = S_SYMB
            FROM    SECURITY
            WHERE   S_CO_ID     = @co_id AND
                    S_ISSUE     = @issue
        END
    ELSE
        BEGIN
            SELECT  @co_id      = S_CO_ID,
                    @exch_id    = S_EX_ID,
                    @s_name     = S_NAME
            FROM    SECURITY
            WHERE   S_SYMB      = @symbol

            SELECT  @co_name    = CO_NAME
            FROM    COMPANY
            WHERE   CO_ID       = @co_id
        END
	
    -- get current price for the security
    SELECT  @market_price   = LT_PRICE
    FROM    LAST_TRADE
    WHERE   LT_S_SYMB       = @symbol

    -- set trade characteristics based on trade type
    SELECT  @type_is_market = TT_IS_MRKT,
            @type_is_sell   = TT_IS_SELL
    FROM    TRADE_TYPE
    WHERE   TT_ID           = @trade_type_id

    IF (@type_is_market <> 0)
        SET @requested_price    = @market_price

    SELECT  @hs_qty     = HS_QTY
    FROM    HOLDING_SUMMARY
    WHERE   HS_CA_ID    = @acct_id AND
            HS_S_SYMB   = @symbol

    IF (@hs_qty is NULL)
	    SET  @hs_qty = 0

    IF (@type_is_sell <> 0)
        BEGIN
        IF (@hs_qty > 0)
            BEGIN
            IF (@is_lifo <> 0)
                DECLARE Holding_List CURSOR LOCAL FAST_FORWARD
                        FOR SELECT  H_QTY,
                                    H_PRICE
                            FROM    HOLDING
                            WHERE   H_CA_ID     = @acct_id AND
                                    H_S_SYMB    = @symbol
                            ORDER   BY H_DTS DESC
            ELSE
                DECLARE Holding_List CURSOR LOCAL FAST_FORWARD
                        FOR SELECT  H_QTY,
                                    H_PRICE
                            FROM    HOLDING
                            WHERE   H_CA_ID     = @acct_id AND
                                    H_S_SYMB    = @symbol
                            ORDER   BY H_DTS ASC

            OPEN Holding_List

            FETCH Holding_List INTO @hold_qty, @hold_price

            WHILE (@@FETCH_STATUS = 0 AND @needed_qty > 0)
            BEGIN
                IF (@hold_qty > @needed_qty)
                    SELECT  @buy_value  = @buy_value + (@needed_qty * @hold_price),
                            @sell_value = @sell_value + (@needed_qty * @requested_price),
                            @needed_qty = 0
                ELSE
                    SELECT  @buy_value  = @buy_value + (@hold_qty * @hold_price),
                            @sell_value = @sell_value + (@hold_qty * @requested_price),
                            @needed_qty = @needed_qty - @hold_qty

                FETCH Holding_List INTO @hold_qty, @hold_price
            END
            CLOSE       Holding_List
            DEALLOCATE  Holding_List
            END
        END
    ELSE
        BEGIN
        IF (@hs_qty < 0)
            BEGIN
            IF (@is_lifo <> 0)
                DECLARE Holding_List CURSOR LOCAL FAST_FORWARD
                        FOR SELECT  H_QTY,
                                    H_PRICE
                            FROM    HOLDING
                            WHERE   H_CA_ID     = @acct_id AND
                                    H_S_SYMB    = @symbol
                            ORDER   BY H_DTS DESC
            ELSE
                DECLARE Holding_List CURSOR LOCAL FAST_FORWARD
                        FOR SELECT  H_QTY,
                                    H_PRICE
                            FROM    HOLDING
                            WHERE   H_CA_ID     = @acct_id AND
                                    H_S_SYMB    = @symbol
                            ORDER   BY H_DTS ASC

            OPEN Holding_List

            FETCH Holding_List INTO @hold_qty, @hold_price

            WHILE (@@FETCH_STATUS = 0 AND @needed_qty > 0)
                BEGIN
                   IF (@hold_qty + @needed_qty < 0)
                        SELECT  @sell_value = @sell_value + (@needed_qty * @hold_price),
                                @buy_value  = @buy_value + (@needed_qty * @requested_price),
                                @needed_qty = 0
                    ELSE
                        BEGIN
                            SET     @hold_qty   = @hold_qty * -1
                            SELECT  @sell_value = @sell_value + (@hold_qty * @hold_price),
                                    @buy_value  = @buy_value + (@hold_qty * @requested_price),
                                    @needed_qty = @needed_qty - @hold_qty
                        END

                    FETCH Holding_List INTO @hold_qty, @hold_price
                END
            CLOSE       Holding_List
            DEALLOCATE  Holding_List
            END
        END
    IF ((@sell_value > @buy_value) AND (@tax_status = 1 OR @tax_status = 2))
    BEGIN
        SELECT  @tax_rates  = SUM(TX_RATE)
        FROM    TAXRATE
        WHERE   TX_ID IN (  SELECT  CX_TX_ID
                            FROM    CUSTOMER_TAXRATE
                            WHERE   CX_C_ID = @cust_id)

        SET     @tax_amount = (@sell_value - @buy_value) * @tax_rates
    END
    ELSE
        SET     @tax_amount = 0

    SELECT  @comm_rate  = CR_RATE
    FROM    COMMISSION_RATE
    WHERE   CR_C_TIER   =  @cust_tier AND
            CR_TT_ID    =  @trade_type_id AND
            CR_EX_ID    =  @exch_id AND
            CR_FROM_QTY <= @trade_qty AND
            CR_TO_QTY   >= @trade_qty

    SELECT  @charge_amount  = CH_CHRG
    FROM    CHARGE
    WHERE   CH_C_TIER       = @cust_tier AND
            CH_TT_ID        = @trade_type_id

    IF (@type_is_margin <> 0)
    BEGIN
        SELECT  @hold_assets    = SUM(HS_QTY * LT_PRICE)
        FROM    HOLDING_SUMMARY,
                LAST_TRADE
        WHERE   HS_CA_ID        = @acct_id AND
                LT_S_SYMB       = HS_S_SYMB

        SELECT  @acct_bal   = CA_BAL
        FROM    CUSTOMER_ACCOUNT
        WHERE   CA_ID       = @acct_id

        IF (@hold_assets is NULL)
            SET @acct_assets    = @acct_bal
        ELSE
            SET @acct_assets    = @hold_assets + @acct_bal
    END
    ELSE
        SET     @acct_assets    = 0
        
    IF (@type_is_market <> 0)
        SET @status_id  = @st_submitted_id
    ELSE
        SET @status_id  = @st_pending_id
	
    SELECT  @co_name, @requested_price, @symbol, @buy_value,
            @charge_amount, @comm_rate, @acct_assets, @market_price,
            @s_name, @sell_value,  @status_id, @tax_amount,
            @type_is_market, @type_is_sell

    --- ACID CODE BEGIN ---
    PRINT   'End Trade Order Frame 3 '
    SELECT  CONVERT(char(30), GETDATE(), 21)
    PRINT   ' '
    PRINT   'Returned values:'
    SELECT  @co_name            AS 'Company Name',
            @symbol             AS 'Security Symbol',
            @comm_rate          AS 'Commission Rate',
            @charge_amount      AS 'Charge Amount',
            @tax_amount         AS 'Tax Amount',
            @requested_price    AS 'Requested Price',
            @acct_assets        AS 'Account Assets',
            @status_id          AS 'Status ID',
            @type_is_market     AS 'Market Type Flag',
            @type_is_sell       AS 'Sell Type Flag'
    PRINT   ' '
    SELECT  @comm_amount    = (@comm_rate / 100) * 100 * 99.99,
            @exec_name      = @exec_f_name + ' ' + @exec_l_name
    --- ACID CODE END ---

    --
    -- Trade Order Frame4 is responsible for retrieving 
    -- and computing all financial data, including 
    -- cash calance, holdings, commission, charges 
    -- and taxes.
    --
    --- ACID CODE BEGIN ---
    SELECT  @is_cash        = 0,
            @is_lifo        = 0
    PRINT   'Begin Trade Order Frame 4 '
    SELECT  CONVERT(char(30), GETDATE(), 21)
    PRINT   ' '
    PRINT   'Variables used:'
    SELECT  @acct_id            AS 'Customer Account ID',
            @broker_id          AS 'Broker ID',
            @charge_amount      AS 'Charge Amount',
            @comm_amount        AS 'Commission Amount',
            @exec_name          AS 'Executor Name',
            @is_cash            AS 'Is Cash Flag',
            @is_lifo            AS 'Is LIFO Flag',
            @requested_price    AS 'Requested Price',
            @status_id          AS 'Status ID',
            @symbol             AS 'Security Symbol',
            @trade_qty          AS 'Quantity of Trade',
            @trade_type_id      AS 'Trade Type ID',
            @type_is_market     AS 'Type is Market Code'
    PRINT   ' '
    --- ACID CODE END ---

    SET     @trade_dts    = GETDATE()     -- set the current date and time for the trade date
    SET		@symbol		  = RTRIM(@symbol)

    -- call Get_Next_T_ID to acquire the trade id for this trade
    EXECUTE Get_Next_T_ID   @t_id   OUTPUT

    -- record the trade information in the TRADE table
    --- ACID CODE BEGIN ---
    PRINT   'Timestamp before insert into TRADE table '
    SELECT  CONVERT(char(30), GETDATE(), 21)
    PRINT   ' '
    --- ACID CODE END ---
    INSERT INTO TRADE   (T_ID, T_DTS, T_ST_ID, T_TT_ID,
                         T_IS_CASH, T_S_SYMB, T_QTY, T_BID_PRICE,
                         T_CA_ID, T_EXEC_NAME, T_TRADE_PRICE, T_CHRG,
                         T_COMM, T_TAX, T_LIFO)
                VALUES  (@t_id, @trade_dts, @status_id, @trade_type_id,
                         @is_cash, @symbol, @trade_qty, @requested_price,
                         @acct_id, rtrim(@exec_name), NULL, @charge_amount,
                         @comm_amount, 0, @is_lifo)
    --- ACID CODE BEGIN ---
    PRINT   'Timestamp after insert into TRADE table '
    SELECT  CONVERT(char(30), GETDATE(), 21)
    PRINT   ' '
    --- ACID CODE END ---
    -- for limit orders only, record the trade information INSERT the TRADE_REQUEST table
    IF ( @type_is_market = 0 )
        INSERT INTO TRADE_REQUEST   (TR_T_ID, TR_TT_ID, TR_S_SYMB, TR_QTY,
                                     TR_BID_PRICE, TR_B_ID)
                VALUES              (@t_id, @trade_type_id, @symbol, @trade_qty,
                                     @requested_price, @broker_id)

    -- record the trade information INSERT the TRADE_HISTORY table
    --- ACID CODE BEGIN ---
    PRINT   'Timestamp before insert into TRADE_HISTORY table '
    SELECT  CONVERT(char(30), GETDATE(), 21)
    PRINT   ' '
    --- ACID CODE END ---
    INSERT INTO TRADE_HISTORY   (TH_T_ID, TH_DTS, TH_ST_ID)
                VALUES          (@t_id, @trade_dts, @status_id)
    --- ACID CODE BEGIN ---
    PRINT   'Timestamp after insert into TRADE_HISTORY table '
    SELECT  CONVERT(char(30), GETDATE(), 21)
    PRINT   ' '
    SELECT  CONVERT(char(30), GETDATE(), 21)
    PRINT   ' '
    PRINT   'Timestamp before commit of TradeOrderFrame4 '
    SELECT  CONVERT(char(30), GETDATE(), 21)
    PRINT   ' '
    COMMIT 
    PRINT   'Timestamp after commit of TradeOrderFrame4 '
    SELECT  CONVERT(char(30), GETDATE(), 21)
    PRINT   ' '
    --- ACID CODE END ---
    
    -- return the trade id
    SELECT  @t_id
    
    --- ACID CODE BEGIN ---
    --- verify the proper rows were inserted
    SELECT  'Isolation 1 - S2 Output',
            @acct_id As 'Customer Account Used',
            @symbol As 'Symbol Used',
            T_ID As 'Trade ID Returned'
    FROM    TRADE
    WHERE   T_ID = @t_id
    --- ACID CODE END ---

END
GO
