----------------------------------------------------------------
--                                                              
--  File:   TradeOrder.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    = 'TradeOrderFrame1' AND
                    type    = 'P')
DROP PROCEDURE  TradeOrderFrame1
GO

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

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

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

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

    BEGIN TRANSACTION

    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
    
    SELECT  @@ROWCOUNT	
END
GO

--
-- Trade Order Frame2 is responsible for verifying 
-- that the executor has the necessary permissions 
-- to submit the trade.
--
CREATE PROCEDURE    TradeOrderFrame2
                        @acct_id            bigint,	
                        @exec_f_name        varchar(25),
                        @exec_l_name        varchar(20),
                        @exec_tax_id        varchar(20)	
AS
BEGIN

    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

END
GO

--
-- Trade Order Frame3 is responsible for retrieving 
-- and computing all financial data, including 
-- cash calance, holdings, commission, charges 
-- and taxes.
--
CREATE PROCEDURE    TradeOrderFrame3
                        @acct_id            bigint,
                        @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),
                        @symbol             varchar(15)	
AS
BEGIN
    DECLARE	-- output parameters
            @buy_value      numeric(12,2),
            @charge_amount  numeric(10,2),
            @comm_rate      numeric(10,2),
            @acct_assets    numeric(12,2),
            @market_price   numeric(8,2),
            @s_name         varchar(70),
            @sell_value     numeric(12,2),
            @status_id      char(4),
            @tax_amount     numeric(10,2),
            @type_is_market tinyint,
            @type_is_sell   tinyint,
            @co_id          bigint,
            @exch_id        char(6),
            @hold_price     numeric(8,2),
            @hold_qty       numeric(6),
            @needed_qty     numeric(6),
            @hs_qty         numeric(6),
            @tax_rates      numeric(6,5),
            @acct_bal       numeric(12,2),
            @hold_assets    numeric(12,2)

    -- 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
END
GO

--
-- Trade Order Frame4 is responsible for retrieving 
-- and computing all financial data, including 
-- cash calance, holdings, commission, charges 
-- and taxes.
--
CREATE PROCEDURE    TradeOrderFrame4
                        @acct_id            bigint,         /* IN account ID */
                        @broker_id          bigint,         /* IN broker ID */
                        @charge_amount      numeric(10,2),  /* IN charge amount */
                        @comm_amount        numeric(10,2),  /* IN commission amount */
                        @exec_name          varchar(49),    /* IN executor name */
                        @is_cash            tinyint,        /* IN is cash flag */
                        @is_lifo            tinyint,        /* IN is lifo flag */
                        @requested_price    numeric(8,2),   /* IN price requested */
                        @status_id          char(4),        /* IN status fo the order */
                        @symbol             varchar(15),    /* IN security symbol */
                        @trade_qty          numeric(6),     /* IN trade quantity */
                        @trade_type_id      char(3),        /* IN trade type ID */
                        @type_is_market     tinyint         /* IN market order flag */
AS
BEGIN

    DECLARE @trade_dts      datetime,
            @t_id           bigint  

    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
    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)

    -- 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
    INSERT INTO TRADE_HISTORY   (TH_T_ID, TH_DTS, TH_ST_ID)
                VALUES          (@t_id, @trade_dts, @status_id)

    -- return the trade id
    SELECT  @t_id

END
GO
