------------------------------------------------------------------
--                                                              --
--  File:   TradeUpdate.SQL                                     --
--          Microsoft TPC-E Benchmark Kit Ver. 1.14.0-1031      --
--          Copyright Microsoft, 2008-2015                  --
--                                                              --
------------------------------------------------------------------
USE tpce
GO
SET QUOTED_IDENTIFIER ON
GO

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

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

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

--
-- Trade Update Frame 1 is responsible for retrieving
-- information about the specified max of 20 trade IDs.
-- It also may modify up to max_updates rows of TRADE.
--
CREATE  PROCEDURE   TradeUpdateFrame1
                        @max_trades    int, 
                        @max_updates   int,        
                        @trade_id1     bigint,      
                        @trade_id2     bigint,     
                        @trade_id3     bigint,     
                        @trade_id4     bigint,
                        @trade_id5     bigint,
                        @trade_id6     bigint,
                        @trade_id7     bigint,
                        @trade_id8     bigint,
                        @trade_id9     bigint,
                        @trade_id10    bigint,
                        @trade_id11    bigint,
                        @trade_id12    bigint,
                        @trade_id13    bigint,
                        @trade_id14    bigint,
                        @trade_id15    bigint,
                        @trade_id16    bigint,
                        @trade_id17    bigint,
                        @trade_id18    bigint,
                        @trade_id19    bigint,
                        @trade_id20    bigint
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

    BEGIN TRANSACTION

    DECLARE @trade_id           bigint,
            @t_exec_name        varchar(49),
            @t_bid_price        numeric(8,2),
            @t_trade_price      numeric(8,2),
            @t_is_cash          tinyint,
            @t_is_market        tinyint,
            @se_cash_type       varchar(40),
            @se_cash_due_date   date,
            @se_amount          numeric(10,2),
            @ct_amount          numeric(10,2),
            @ct_name            varchar(100),
            @ct_dts             datetime,
            @num_found          int,
            @num_updated        int

    DECLARE @trades TABLE   (SEQ    tinyint     NOT NULL,
                             T_ID   bigint      NULL,
                             PRIMARY KEY (SEQ))

    INSERT INTO @trades 
            SELECT 1, @trade_id1
            UNION ALL
            SELECT 2, @trade_id2
            UNION ALL
            SELECT 3, @trade_id3
            UNION ALL
            SELECT 4, @trade_id4
            UNION ALL
            SELECT 5, @trade_id5
            UNION ALL
            SELECT 6, @trade_id6
            UNION ALL
            SELECT 7, @trade_id7
            UNION ALL
            SELECT 8, @trade_id8
            UNION ALL
            SELECT 9, @trade_id9
            UNION ALL
            SELECT 10, @trade_id10
            UNION ALL
            SELECT 11, @trade_id11
            UNION ALL
            SELECT 12, @trade_id12
            UNION ALL
            SELECT 13, @trade_id13
            UNION ALL
            SELECT 14, @trade_id14
            UNION ALL
            SELECT 15, @trade_id15
            UNION ALL
            SELECT 16, @trade_id16
            UNION ALL
            SELECT 17, @trade_id17
            UNION ALL
            SELECT 18, @trade_id18
            UNION ALL
            SELECT 19, @trade_id19
            UNION ALL
            SELECT 20, @trade_id20

    SELECT  @num_found    = 0,   
            @num_updated  = 0

    DECLARE Trade_List CURSOR LOCAL FORWARD_ONLY SCROLL_LOCKS
        FOR SELECT  T_ID         /* should be the same as maximum posssible max_trades */
            FROM    @trades
            WHERE   SEQ <= @max_trades
            ORDER   BY SEQ

    OPEN    Trade_List 
	
    -- initial fetch before the loop
    FETCH FROM Trade_List INTO @trade_id 

    WHILE (@@FETCH_STATUS = 0 AND @max_trades > 0)      -- max_trades may be 1-20 and thus less than the 20 inputs
    BEGIN
        IF (@num_updated < @max_updates)                -- max_updates <= max_trades
        BEGIN
            UPDATE  TRADE
            SET     T_EXEC_NAME = CASE WHEN T_EXEC_NAME LIKE '% X %' THEN REPLACE (RTRIM(T_EXEC_NAME), ' X ', ' ')
                                       WHEN T_EXEC_NAME LIKE '% %'   THEN REPLACE (RTRIM(T_EXEC_NAME), ' ', ' X ')
                                       ELSE T_EXEC_NAME
                                  END
            WHERE   T_ID        = @trade_id

            SET @num_updated    = @num_updated + @@ROWCOUNT
        END

        -- set cash transaction local variables to NULL
        SELECT  @ct_amount  = NULL,
                @ct_name    = NULL,
                @ct_dts     = NULL,
                @num_found  = @num_found+1,
                @max_trades = @max_trades-1

        SELECT  @t_exec_name        = T_EXEC_NAME,
                @t_bid_price        = T_BID_PRICE,
                @t_trade_price      = T_TRADE_PRICE,
                @t_is_cash          = T_IS_CASH,
                @t_is_market        = TT_IS_MRKT
        FROM    TRADE,
                TRADE_TYPE
        WHERE   T_ID                = @trade_id AND
                T_TT_ID             = TT_ID

        SELECT  @se_cash_type       = SE_CASH_TYPE,
                @se_cash_due_date   = SE_CASH_DUE_DATE,
                @se_amount          = SE_AMT
        FROM    SETTLEMENT
        WHERE   SE_T_ID             = @trade_id

        -- if this is a cash transaction,
        -- get information from CASH_TRANSACTION
        IF (@t_is_cash <> 0)
            SELECT  @ct_amount  = CT_AMT,
                    @ct_name    = CT_NAME,
                    @ct_dts     = CT_DTS
            FROM    CASH_TRANSACTION
            WHERE   CT_T_ID     = @trade_id

        -- return as a rowset
        SELECT  @t_bid_price,
                @ct_amount,
                @ct_dts,
                @ct_name,
                @t_exec_name,
                @t_is_cash,
                @t_is_market,
                @se_amount,
                @se_cash_due_date,
                @se_cash_type,
                @t_trade_price
			
        -- get the trade history 2 or 3 rows for the trade
        SELECT  TOP 3
                TH_DTS,
                TH_ST_ID
        FROM    TRADE_HISTORY
        WHERE   TH_T_ID = @trade_id
        ORDER   BY TH_DTS

        FETCH FROM Trade_List INTO @trade_id

    END -- WHILE

    SELECT  @num_found,
            @num_updated
        
    CLOSE       Trade_List 
    DEALLOCATE  Trade_List

    COMMIT TRANSACTION
END
GO

--
-- Trade Update Frame 2 is responsible for retrieving information
-- for up to max_trades trades executed for the specified account 
-- at or after the specified time.
-- It may also modify up to max_updates rows of SETTLEMENT.
--
CREATE  PROCEDURE   TradeUpdateFrame2
                        @acct_id                bigint,
                        @max_trades             int,
                        @max_updates            int,
                        @start_trade_dts        datetime,
                        @end_trade_dts          datetime
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

    BEGIN TRANSACTION

    DECLARE @trade_id           bigint,
            @t_exec_name        varchar(49),
            @t_bid_price        numeric(8,2),
            @t_trade_price      numeric(8,2),
            @t_is_cash          tinyint,
            @se_cash_type       varchar(40),
            @se_cash_due_date   date,
            @se_amount          numeric(10,2),
            @ct_amount          numeric(10,2),
            @ct_name            varchar(100),
            @ct_dts             datetime,
            @num_found          int,
            @num_updated        int

    SELECT  @num_found      = 0,
            @num_updated    = 0

    DECLARE Trade_List CURSOR LOCAL FORWARD_ONLY SCROLL_LOCKS
        FOR SELECT  TOP 20         /* should be the same as maximum posssible max_trades */
                    T_ID,
                    T_EXEC_NAME,
                    T_BID_PRICE,
                    T_TRADE_PRICE,
                    T_IS_CASH
            FROM    TRADE
            WHERE   T_CA_ID     =  @acct_id AND
                    T_DTS       BETWEEN @start_trade_dts AND @end_trade_dts
            ORDER   BY T_DTS

    OPEN    Trade_List 

    -- initial fetch before the loop
    FETCH   FROM    Trade_List
            INTO    @trade_id,
                    @t_exec_name,
                    @t_bid_price,
                    @t_trade_price,
                    @t_is_cash

    WHILE (@@FETCH_STATUS = 0 AND @max_trades > 0)
    BEGIN
        -- set cash transaction local variables to NULL
        SELECT  @ct_amount  = NULL,
                @ct_name    = NULL,
                @ct_dts     = NULL,
                @num_found  = @num_found + 1,
                @max_trades = @max_trades - 1

        IF (@num_updated < @max_updates)          -- max_updates is <= max_trades
        BEGIN
            UPDATE  SETTLEMENT
            SET     SE_CASH_TYPE    = CASE  WHEN SE_CASH_TYPE = 'Cash Account'   THEN 'Cash'
                                            WHEN SE_CASH_TYPE = 'Cash'           THEN 'Cash Account'
                                            WHEN SE_CASH_TYPE = 'Margin Account' THEN 'Margin'
                                            WHEN SE_CASH_TYPE = 'Margin'         THEN 'Margin Account'
                                            ELSE SE_CASH_TYPE
                                      END
            WHERE   SE_T_ID         = @trade_id

            SET     @num_updated    = @num_updated + @@ROWCOUNT
        END

        SELECT  @se_cash_type       = SE_CASH_TYPE,
                @se_cash_due_date   = SE_CASH_DUE_DATE,
                @se_amount          = SE_AMT
        FROM    SETTLEMENT
        WHERE   SE_T_ID             = @trade_id

        -- if this is a cash transaction,
        -- get information from CASH_TRANSACTION
        IF (@t_is_cash <> 0)
            SELECT  @ct_amount  = CT_AMT,
                    @ct_name    = CT_NAME,
                    @ct_dts     = CT_DTS
            FROM    CASH_TRANSACTION
            WHERE   CT_T_ID     = @trade_id

        -- return as a rowset
        SELECT  @t_bid_price,
                @ct_amount,
                @ct_dts,
                @ct_name,
                @t_exec_name,
                @t_is_cash,
                @se_amount,
                @se_cash_due_date,
                @se_cash_type,                
                @trade_id,
                @t_trade_price
  	
        -- get the trade history of 2-3 rows for this trade
        SELECT  TOP 3
                TH_DTS,
                TH_ST_ID
        FROM    TRADE_HISTORY
        WHERE   TH_T_ID = @trade_id
        ORDER   BY TH_DTS

        FETCH   FROM    Trade_List
                INTO    @trade_id,
                        @t_exec_name,
                        @t_bid_price,
                        @t_trade_price,
                        @t_is_cash

    END -- WHILE (@@FETCH_STATUS = 0 AND @max_trades > 0)

    SELECT  @num_found,
            @num_updated

    CLOSE       Trade_List 
    DEALLOCATE  Trade_List

    IF  (@num_found = 0)
    BEGIN   --  Show that earlier initial trade exists for this account
        INSERT  INTO TL_TU_WARNINGS
        SELECT  TOP 1
                getdate(), 'TU2', 
                @max_trades, @acct_id, NULL, @start_trade_dts, @end_trade_dts,
                T_ID, T_CA_ID, T_S_SYMB, T_DTS
        FROM    TRADE
        WHERE   T_CA_ID = @acct_id AND
                T_DTS   < @start_trade_dts
        ORDER   BY T_DTS DESC

    END

    COMMIT TRANSACTION
END
GO

--
-- Trade Update Frame 3 returns up to max_trades trades for a given
-- security symbol on or after a specified point in time.
-- It may also modify up to max_updates rows of CASH_TRANSACTION.
--
CREATE  PROCEDURE   TradeUpdateFrame3
                        @max_trades         int,
                        @max_updates        int,
                        @symbol             varchar(15),
                        @start_trade_dts    datetime,
                        @end_trade_dts      datetime,
                        @max_acct_id        bigint
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

    BEGIN TRANSACTION

    DECLARE @trade_id           bigint,
            @t_ca_id            bigint,
            @t_exec_name        varchar(49),
            @t_is_cash          tinyint,
            @t_trade_price      numeric(8,2),
            @t_qty              numeric(6),
            @t_dts              datetime,
            @t_tt_id            char(3),
            @se_cash_type       varchar(40),
            @se_cash_due_date   date,
            @se_amount          numeric(10,2),
            @ct_amount          numeric(10,2),
            @ct_name            varchar(100),
            @ct_dts             datetime,
            @num_found          int,
            @num_updated        int,
            @ct_string          varchar(20),
            @s_name             varchar(70),
            @type_name          char(12)

    DECLARE Trade_List CURSOR LOCAL FORWARD_ONLY SCROLL_LOCKS
                FOR SELECT  TOP 20
                            T_ID,
                            T_CA_ID,
                            T_EXEC_NAME,
                            T_IS_CASH,
                            T_TRADE_PRICE,
                            T_QTY,
                            T_DTS,
                            T_TT_ID
                    FROM    TRADE
                    WHERE   T_S_SYMB    =  @symbol AND
                            T_DTS       BETWEEN @start_trade_dts AND @end_trade_dts
                    ORDER   BY T_DTS

    SELECT  @num_found      = 0,
            @num_updated    = 0

    SELECT  @s_name = S_NAME
    FROM    SECURITY
    WHERE   S_SYMB  = @symbol

    OPEN    Trade_List
    
    FETCH FROM  Trade_List
          INTO  @trade_id,
                @t_ca_id,
                @t_exec_name,
                @t_is_cash,
                @t_trade_price,
                @t_qty,
                @t_dts,
                @t_tt_id

    WHILE (@@FETCH_STATUS = 0 AND @max_trades > 0)
    BEGIN
        -- set cash transaction local variables to NULL
        SELECT  @ct_amount  = NULL,
                @ct_name    = NULL,
                @ct_dts     = NULL,
                @num_found  = @num_found + 1,
                @max_trades = @max_trades - 1,
                @type_name  = TT_NAME
        FROM    TRADE_TYPE
        WHERE   TT_ID       = @t_tt_id

        SELECT  @se_cash_type       = SE_CASH_TYPE,
                @se_cash_due_date   = SE_CASH_DUE_DATE,
                @se_amount          = SE_AMT
        FROM    SETTLEMENT
        WHERE   SE_T_ID             = @trade_id

        -- if this is a cash transaction
        -- get information from CASH_TRANSACTION
        IF (@t_is_cash <> 0)
        BEGIN
            IF (@num_updated < @max_updates)
            BEGIN
                SELECT  @ct_string  = RTRIM(@type_name) + ' ' + RTRIM(CAST(@t_qty AS char(7)))
        
                UPDATE  CASH_TRANSACTION
                SET     CT_NAME = CASE  WHEN CT_NAME LIKE '% Shares of %' THEN @ct_string + ' shares of ' + RTRIM(@s_name)
                                        WHEN CT_NAME LIKE '% shares of %' THEN @ct_string + ' Shares of ' + RTRIM(@s_name)
                                        ELSE CT_NAME
                                  END
                WHERE   CT_T_ID = @trade_id
                
                SET     @num_updated    = @num_updated + @@ROWCOUNT
            END

            SELECT  @ct_amount  = CT_AMT,
                    @ct_name    = CT_NAME,
                    @ct_dts     = CT_DTS
            FROM    CASH_TRANSACTION
            WHERE   CT_T_ID     = @trade_id
        END

        -- return as a rowset
        SELECT  @t_ca_id,
                @ct_amount,
                @ct_dts,
                @ct_name,
                @t_exec_name,
                @t_is_cash,
                @t_trade_price,
                @t_qty,
                @s_name,
                @se_amount,
                @se_cash_due_date,
                @se_cash_type,
                @t_dts,
                @trade_id,
                @t_tt_id,
                @type_name

        -- get the trade history of 2-3 rows for this trade
        SELECT  TOP 3
                TH_DTS,
                TH_ST_ID
        FROM    TRADE_HISTORY
        WHERE   TH_T_ID = @trade_id
        ORDER   BY TH_DTS

        IF (@max_trades > 0)
            FETCH FROM  Trade_List
                  INTO  @trade_id,
                        @t_ca_id,
                        @t_exec_name,
                        @t_is_cash,
                        @t_trade_price,
                        @t_qty,
                        @t_dts,
                        @t_tt_id

    END

    SELECT  @num_found,
            @num_updated

    CLOSE       Trade_List
    DEALLOCATE  Trade_List

    IF  (@num_found = 0)
    BEGIN   --  Show that earlier initial trade exists for this symbol
        INSERT  INTO TL_TU_WARNINGS
        SELECT  TOP 1
                getdate(), 'TU3', 
                @max_trades, NULL, @symbol, @start_trade_dts, @end_trade_dts,
                T_ID, T_CA_ID, T_S_SYMB, T_DTS
        FROM    TRADE
        WHERE   T_S_SYMB = @symbol AND
                T_DTS    < @start_trade_dts
        ORDER   BY T_DTS DESC

    END

    COMMIT TRANSACTION
END
GO
