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

DECLARE     @dts            datetime,
            @start_tid      bigint,
            @tr_cnt         int,
            @sbmt           int,
            @old_cncl       int,
            @new_cncl       int,
            @new_trades     int,
            @new_history    int,
            @new_market     int,
            @new_limit      int,
            @error_cnt      int

PRINT   'Trade Cleanup Start'
SELECT  CONVERT(char(30), GETDATE(), 21)
PRINT   ' '
PRINT   'Cleaning up trade information from previous runs...'
PRINT   ' '

-- get the largest initial T_ID
SELECT  @start_tid  = MAX_T_ID
FROM    TPCE_INFO

-- count the runtime cancelled trades before this cleanup event
SELECT  @old_cncl   = COUNT(*)
FROM    TRADE
WHERE   T_ID        > @start_tid AND
        T_ST_ID     = 'CNCL'

-- count the pending limit orders before this cleanup event
SELECT  @tr_cnt     = COUNT(*)
FROM    TRADE_REQUEST

SELECT  @tr_cnt AS TR_Rows

IF (@tr_cnt > 0)
BEGIN
    -- check for non-Pending trades in TRADE_REQUEST, which SHOULD never occur, but does!
    SELECT  @error_cnt  = COUNT(*)
    FROM    TRADE_REQUEST JOIN TRADE ON TR_T_ID = T_ID
    WHERE   T_ST_ID     <> 'PNDG'

    IF (@error_cnt > 0)
    BEGIN
        PRINT 'ERROR!!! -- Trades in TRADE_REQUEST with Status not PNDG'

        SELECT  T_ST_ID,
                COUNT(*) AS TR_Trades
        FROM    TRADE_REQUEST JOIN TRADE ON TR_T_ID = T_ID
        WHERE   T_ST_ID <> 'PNDG'
        GROUP   BY T_ST_ID
    END

    SET     @dts    = GETDATE()

    INSERT INTO TRADE_HISTORY
        SELECT  TR_T_ID,
                @dts,
                'SBMT'
        FROM    TRADE_REQUEST

    SET     @dts = GETDATE()

    INSERT INTO TRADE_HISTORY
        SELECT  TR_T_ID,
                @dts,
                'CNCL'
        FROM    TRADE_REQUEST

    UPDATE  TRADE
    SET     T_ST_ID = 'CNCL',
            T_DTS   = @dts
    FROM    TRADE_REQUEST
    WHERE   T_ID    = TR_T_ID

    TRUNCATE TABLE TRADE_REQUEST
END

SELECT  T_ID AS ID
INTO    #T
FROM    TRADE
WHERE   T_ID    > @start_tid AND
        T_ST_ID = 'SBMT'
      
SET     @sbmt   = @@rowcount

-- submitted (but not completed) runtime trades before cleanup event
SELECT  @sbmt AS SBMT_Rows

IF (@sbmt > 0)
BEGIN
    SET     @dts    = GETDATE()
    
    INSERT INTO TRADE_HISTORY
        SELECT  ID,
                @dts,
                'CNCL'
        FROM    #T

    UPDATE  TRADE
    SET     T_ST_ID = 'CNCL',
            T_DTS   = @dts
    FROM    #T
    WHERE   T_ID    = ID
END

DROP TABLE #T

-- cancelled runtime trades after cleanup
SELECT  @new_cncl   = COUNT(*)				
FROM    TRADE
WHERE   T_ID        > @start_tid AND
        T_ST_ID     = 'CNCL'

SELECT  @old_cncl AS Prior_Canceled,
        @tr_cnt AS Pending,
        @sbmt AS Submitted,
        @new_cncl AS New_Cancelled

IF (@new_cncl = (@old_cncl + @tr_cnt + @sbmt))
    PRINT   'Counts Match'
ELSE
BEGIN
    PRINT   'ERROR:  Counts do NOT match!!!'

    SELECT  @new_cncl AS Actual_New_Cancelled,
            (@old_cncl+@tr_cnt+@sbmt) AS Expected_New_Cancelled
END

PRINT ' '

SELECT  @new_trades = COUNT(*),
        @new_market = SUM(CASE WHEN TT_IS_MRKT = 1
                                THEN 1
                                ELSE 0
                          END),
        @new_limit  = SUM(CASE WHEN TT_IS_MRKT = 0
                                THEN 1
                                ELSE 0
                          END)
FROM    TRADE JOIN TRADE_TYPE ON T_TT_ID = TT_ID
WHERE   T_ID    > @start_tid

SELECT  @new_history    = COUNT(*) 
FROM    TRADE_HISTORY
WHERE   TH_T_ID         > @start_tid

SELECT  @new_trades AS Runtime_Trades, 
        @new_market AS Runtime_Market,
        CONVERT(FLOAT,@new_market) * 100.00 / @new_trades AS Percent_Market,
        @new_limit AS Runtime_Limit,
        CONVERT(FLOAT,@new_limit) * 100.00 / @new_trades AS Percent_Limit

SELECT  @new_history AS Runtime_History,
        @new_trades AS Runtime_Trades,
        CONVERT(FLOAT,@new_history) / @new_trades AS Runtime_Ratio

IF (@new_history = (2 * @new_market) + (3 * @new_limit))
    PRINT   'Correct number of runtime TRADE_HISTORY rows'
ELSE
BEGIN
    PRINT   'ERROR: Incorrect number of runtime TRADE_HISTORY rows!!!'
    
    SELECT  @new_history AS Actual_New_History, 
            ((2 * @new_market) + (3 * @new_limit)) AS Expected_New_History
END

PRINT   'Trade Cleanup End'
SELECT  CONVERT(char(30), GETDATE(), 21)
PRINT   ' '
GO

CHECKPOINT
GO
