------------------------------------------------------------------
--                                                              --
--  File:   Isolation3_S1.SQL                                   --
--          Microsoft TPC-E Benchmark Kit Ver. 1.12.0-1025      --
--          Copyright Microsoft  2010, 2011, 2012               --
--                                                              --
--                                                              --
--  Implements TPC-E Isolation test #3                          --
--  P1 Test in Read-Write                                       --
--                                                              --
------------------------------------------------------------------
USE tpce
GO

BEGIN TRANSACTION

DECLARE @num_custs      bigint,
        @num_companies  bigint,
        @acct_id        bigint,
        @cust_id        bigint,
        @company_id     bigint,
        @symbol_1       varchar(15),
        @symbol_2       varchar(15),
        @trade_id_1     bigint,
        @trade_id_2     bigint,
        @ca_bal         numeric(10,2),
        @cnt            bigint,
        @val            bigint,
        @Dup_Sec_Flag   tinyint

--- get the number of customers in the database
SELECT  @num_custs      = COUNT(*) FROM CUSTOMER

--- select a random customer for use in the test
SET     @cust_id        = CONVERT(bigint, RAND() * @num_custs) + 4300000001

--- select an associated customer account id for this customer
SELECT  TOP 1
        @acct_id        = CA_ID
FROM    CUSTOMER_ACCOUNT
WHERE   CA_C_ID         = @cust_id
ORDER   BY 1 DESC
                                               
--- run the Customer Position transaction
EXEC CustomerPosition_Iso3    @cust_id, ' ', @acct_id

--- get the CA_BAL for the account selected so we can display it later
SELECT  @ca_bal = CA_BAL
FROM    CUSTOMER_ACCOUNT
WHERE   CA_ID   = @acct_id

--- select a security symbol for use in the trade order below
SELECT  @num_companies  = COUNT_BIG(*)
FROM    COMPANY

--- select a random company id to use
SET     @company_id = CONVERT(BIGINT, RAND() * @num_companies) + 4300000001

--- get the number of rows in the security table for this company
SELECT  @cnt        = COUNT_BIG(*) 
FROM    SECURITY
WHERE   S_CO_ID = @company_id
				
SELECT  @val    = (@cnt + 1) / 2    

SET     rowcount @val

--- now get the symbol
SELECT  TOP 1
        @symbol_1   = S_SYMB
FROM    SECURITY
WHERE   S_CO_ID = @company_id    

SET     rowcount 0

--- to facilitate the rest of the test, we need to enter a trade order for this customer account
EXEC TradeOrder_Iso3  @acct_id, @symbol_1

--- select another random security, but not the one used above
SET     @Dup_Sec_Flag   = 1
WHILE   @Dup_Sec_Flag   = 1
BEGIN
    --- select a random company id to use
    SET     @company_id = CONVERT(BIGINT, RAND() * @num_companies) + 4300000001

    --- get the number of rows in the security table for this company
    SELECT  @cnt        = COUNT_BIG(*) 
    FROM    SECURITY
    WHERE   S_CO_ID = @company_id
				
    SELECT  @val    = (@cnt + 1) / 2    

    SET     rowcount @val

    --- now get the symbol
    SELECT  TOP 1
            @symbol_2   = S_SYMB
    FROM    SECURITY
    WHERE   S_CO_ID = @company_id    

    SET     rowcount 0
    
    IF @symbol_1 <> @symbol_2
        SET     @Dup_Sec_Flag = 0
    ELSE
        SET     @Dup_Sec_Flag = 1
END

--- to facilitate the rest of the test, we need to enter a trade order for this customer account
--- with a different security symbol
EXEC TradeOrder_Iso3  @acct_id, @symbol_2

SELECT @trade_id_1  = T_ID
FROM    TRADE
WHERE   T_S_SYMB    = @symbol_1 AND
        T_CA_ID     = @acct_id

SELECT @trade_id_2  = T_ID
FROM    TRADE
WHERE   T_S_SYMB    = @symbol_2 AND
        T_CA_ID     = @acct_id

PRINT   ' '
PRINT   '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
PRINT   'USE THESE VALUES IN ISOLATION3_S2!'
PRINT   '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
SELECT  'Isolation 3 - S1 Output',
        @cust_id    As 'Customer ID Used',
        @acct_id    As 'Customer Account ID Used',
        @ca_bal     As 'Customer Account Balance',
        @symbol_1   As 'Symbol Used',
        @trade_id_1 As 'Trade ID Returned'

PRINT   ' '
PRINT   '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
PRINT   'USE THESE VALUES IN ISOLATION3_S3!'
PRINT   '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
SELECT  'Isolation 3 - S1 Output',
        @cust_id    As 'Customer ID Used',
        @acct_id    As 'Customer Account ID Used',
        @ca_bal     As 'Customer Account Balance',
        @symbol_2   As 'Symbol Used',
        @trade_id_2 As 'Trade ID Returned'

COMMIT
GO
