------------------------------------------------------------------
--                                                              --
--  File:   MarketFeed.SQL                                      --
--          Microsoft TPC-E Benchmark Kit Ver. 1.12.0-1025      --
--          Copyright Microsoft  2010, 2011, 2012               --
--                                                              --
------------------------------------------------------------------
USE tpce
GO
SET QUOTED_IDENTIFIER ON
GO

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

-- MarketFeedFrame1 is responsible for recording 
-- the new values of the @symbols coming from the 
-- market. 
CREATE PROCEDURE    MarketFeedFrame1	
                        @st_submitted_id    char(4),
                        @type_limit_buy     char(3),
                        @type_limit_sell    char(3),
                        @type_stop_loss     char(3),

                        @qty1     int, @price1    numeric(8,2), @symbol1     varchar(15),
                        @qty2     int, @price2    numeric(8,2), @symbol2     varchar(15),
                        @qty3     int, @price3    numeric(8,2), @symbol3     varchar(15),
                        @qty4     int, @price4    numeric(8,2), @symbol4     varchar(15),
                        @qty5     int, @price5    numeric(8,2), @symbol5     varchar(15),
                        @qty6     int, @price6    numeric(8,2), @symbol6     varchar(15),
                        @qty7     int, @price7    numeric(8,2), @symbol7     varchar(15),
                        @qty8     int, @price8    numeric(8,2), @symbol8     varchar(15),
                        @qty9     int, @price9    numeric(8,2), @symbol9     varchar(15),
                        @qty10    int, @price10   numeric(8,2), @symbol10    varchar(15),

                        @qty11    int, @price11   numeric(8,2), @symbol11    varchar(15),
                        @qty12    int, @price12   numeric(8,2), @symbol12    varchar(15),
                        @qty13    int, @price13   numeric(8,2), @symbol13    varchar(15),
                        @qty14    int, @price14   numeric(8,2), @symbol14    varchar(15),
                        @qty15    int, @price15   numeric(8,2), @symbol15    varchar(15),
                        @qty16    int, @price16   numeric(8,2), @symbol16    varchar(15),
                        @qty17    int, @price17   numeric(8,2), @symbol17    varchar(15),
                        @qty18    int, @price18   numeric(8,2), @symbol18    varchar(15),
                        @qty19    int, @price19   numeric(8,2), @symbol19    varchar(15),
                        @qty20    int, @price20   numeric(8,2), @symbol20    varchar(15)
                        
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

	BEGIN TRANSACTION

    DECLARE -- output variables
            @bid_price          numeric(8,2),
            @trade_id           bigint,
            @trade_qty          numeric(6),
            @symbol             varchar(15),             -- local variables	
            @trade_type         char(3),
            @trade_dts          datetime,
            @num_unique         int,
            @num_symbols        int

    DECLARE @ticker TABLE   (SEQ    tinyint         not NULL,
                             QTY    numeric(6)      not NULL,
                             PRICE  numeric(8,2)    not NULL,
                             -- use the current database collation instead of tempdb collation
                             -- otherwise comparison between current database tables and
                             -- @ticker will result in collation conflict error
                             SYMBOL char(15)        COLLATE database_default NOT NULL,
                             PRIMARY KEY (SYMBOL, SEQ))

    DECLARE @trigger TABLE  (SYMB   varchar(15)     COLLATE database_default PRIMARY KEY NOT NULL,
                             MINP   numeric(8,2)    not NULL,
                             MAXP   numeric(8,2)    not NULL,
                             SUMQ   numeric(6)      not NULL,
                             MAXS   tinyint         not NULL)

    -- Populate the @ticker table from the procedure parameters.

    INSERT INTO @ticker
		VALUES  ( 1,  @qty1,  @price1,  @symbol1),
                ( 2,  @qty2,  @price2,  @symbol2),
		        ( 3,  @qty3,  @price3,  @symbol3),
		        ( 4,  @qty4,  @price4,  @symbol4),
		        ( 5,  @qty5,  @price5,  @symbol5),
		        ( 6,  @qty6,  @price6,  @symbol6),
		        ( 7,  @qty7,  @price7,  @symbol7),
		        ( 8,  @qty8,  @price8,  @symbol8),
		        ( 9,  @qty9,  @price9,  @symbol9),
		        (10, @qty10, @price10, @symbol10),
		        (11, @qty11, @price11, @symbol11),
		        (12, @qty12, @price12, @symbol12),
		        (13, @qty13, @price13, @symbol13),
		        (14, @qty14, @price14, @symbol14),
		        (15, @qty15, @price15, @symbol15),
		        (16, @qty16, @price16, @symbol16),
		        (17, @qty17, @price17, @symbol17),
		        (18, @qty18, @price18, @symbol18),
		        (19, @qty19, @price19, @symbol19),
		        (20, @qty20, @price20, @symbol20)

    SELECT  @num_symbols    = COUNT(*)
    FROM    @ticker

    -- populate the @trigger table from the @ticker table
    INSERT INTO @trigger
            SELECT  SYMBOL,
                    MIN(PRICE),
                    MAX(PRICE),
                    SUM(QTY),
                    MAX(SEQ)
            FROM    @ticker
            GROUP   BY SYMBOL
            ORDER   BY SYMBOL
            
	SET		@num_unique	 = @@ROWCOUNT
    
    DECLARE Request_List CURSOR LOCAL FORWARD_ONLY SCROLL_LOCKS
		FOR SELECT  TR_T_ID, 
                    TR_BID_PRICE, 
                    TR_TT_ID,				
                    TR_QTY,
                    TR_S_SYMB
            FROM    TRADE_REQUEST WITH (UPDLOCK),
                    @trigger AS t
            WHERE   TR_S_SYMB       = t.SYMB AND 
                    ((TR_TT_ID      = @type_stop_loss AND 
                      TR_BID_PRICE  >= t.MINP) OR
                     (TR_TT_ID      = @type_limit_sell AND 
                      TR_BID_PRICE  <= t.MAXP) OR 
                     (TR_TT_ID      = @type_limit_buy AND 
                      TR_BID_PRICE  >= t.MINP) )
            ORDER BY TR_S_SYMB, TR_T_ID
            OPTION  (KEEPFIXED PLAN)

    OPEN    Request_List 
	
    -- initial fetch before the loop
    FETCH FROM Request_List INTO    @trade_id, 
                                    @bid_price, 
                                    @trade_type, 				
                                    @trade_qty,
                                    @symbol

    WHILE(@@FETCH_STATUS = 0)
        BEGIN
            -- return as a single-row rowset
            SELECT  @trade_id,
                    @bid_price,
                    @trade_qty,
                    @symbol,
                    @trade_type
   
            SET     @trade_dts = getdate()

            UPDATE  TRADE 
            SET     T_DTS   = @trade_dts, 
                    T_ST_ID = @st_submitted_id 
            WHERE   T_ID    = @trade_id

            DELETE
            FROM    TRADE_REQUEST
            WHERE   CURRENT OF Request_List

            INSERT INTO TRADE_HISTORY   (TH_T_ID, TH_DTS, TH_ST_ID)
                        VALUES          (@trade_id, @trade_dts, @st_submitted_id)	

            FETCH FROM Request_List INTO    @trade_id, 
                                            @bid_price, 
                                            @trade_type, 				
                                            @trade_qty,
                                            @symbol
                                            
        END -- WHILE
        
    CLOSE       Request_List 
    DEALLOCATE  Request_List
    
    SET     @trade_dts		= getdate()

    UPDATE  LAST_TRADE 
    SET     LT_PRICE    = s.PRICE,
            LT_VOL      = LT_VOL + s.SUMQ,
            LT_DTS      = @trade_dts
    FROM   (SELECT	TOP 100
                    SYMBOL,
                    PRICE,
                    SUMQ
            FROM    @ticker, 
                    @trigger
            WHERE   SYMBOL = SYMB AND
                    SEQ    = MAXS
            ORDER BY SYMBOL) AS s
            WHERE   LT_S_SYMB = s.SYMBOL

	SELECT  @@ROWCOUNT
	

    COMMIT TRANSACTION				
END
GO
