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

-- DataMaintenanceFrame1 is responsible for periodlically 
-- updating data tables that are used for reference by the
-- other transactions. 
CREATE PROCEDURE    DataMaintenanceFrame1
                        @vol_incr       smallint    = 0,    /* IN volume increment for DAILY_MARKET update */
                        @c_id           bigint     = 0,     /* IN customer ID */
                        @acct_id        bigint     = 0,     /* IN customer account ID */
                        @co_id          bigint     = 0,     /* IN company ID */
                        @day_of_month   tinyint     = 0,    /* IN random day for DAILY_MARKET update */
                        @symbol         varchar(15) = NULL, /* IN security symbol */
                        @tx_id          char(4)     = NULL, /* IN tax ID */
                        @table_name     char(18)            /* IN table name for update */
AS		
BEGIN
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED

	BEGIN TRANSACTION

    DECLARE -- local variables
            @row_count      int,
            @acl            char(4),
            @ad_line2       varchar(80),
            @ad_id          bigint,
            @sp_rate        char(4),
            @len_target     int,
            @c_email_2      varchar(50),
            @len_db_column  int,
            @tx_name        char(50),
            @pos            int,
            @wl_id          bigint,
            @old_symbol     varchar(15),
            @new_symbol     varchar(15),
            @old_tax_rate   char(3),
            @new_tax_rate   char(3),
            @tax_num        int,
			@useralias		varchar(50),
			@isp			varchar(50)

    --- insert a row into the DM_Audit_Table
    INSERT INTO DM_Audit_Table VALUES ( @table_name,@c_id,@acct_id,@co_id,@day_of_month,@symbol,@tx_id,@vol_incr,GETDATE())

------------------------------
-- ACCOUNT_PERMISSION TABLE --
------------------------------
    IF (@table_name = 'ACCOUNT_PERMISSION')
        BEGIN
            -- ACCOUNT_PERMISSION Table
            -- Update the AP_ACL to "1111" or "0011" in
            -- rows for a customer account of c_id			
            SELECT  TOP 1
                    @acl        = AP_ACL
            FROM    ACCOUNT_PERMISSION
            WHERE   AP_CA_ID    = @acct_id
            ORDER   BY AP_ACL DESC
	
            IF (@acl <> '1111')
                UPDATE  ACCOUNT_PERMISSION
                SET     AP_ACL      = '1111'
                WHERE   AP_CA_ID    = @acct_id AND
                        AP_ACL      = @acl
            ELSE
                UPDATE  ACCOUNT_PERMISSION
                SET     AP_ACL      = '0011'
                WHERE   AP_CA_ID    = @acct_id AND
                        AP_ACL      = @acl
        END

------------------------------
--     ADDRESS TABLE        --
------------------------------
    IF (@table_name = 'ADDRESS')
        BEGIN
            IF ( @c_id <> 0 )
                -- ADDRESS Table
                -- Change the AD_LINE2 in the ADDRESS table
                -- for the Customer with C_ID of @c_id
                SELECT  @ad_line2   = AD_LINE2,
                        @ad_id      = AD_ID
                FROM    ADDRESS,
                        CUSTOMER
                WHERE   AD_ID   = C_AD_ID AND
                        C_ID    = @c_id
            ELSE
                -- ADDRESS Table
                -- Change the AD_LINE2 in the ADDRESS table
                -- for the Company with CO_ID of @co_id
                SELECT  @ad_line2   = AD_LINE2,
                        @ad_id      = AD_ID
                FROM    ADDRESS,
                        COMPANY
                WHERE   AD_ID   = CO_AD_ID AND
                        CO_ID    = @co_id


            IF (@ad_line2 <> 'Apt. 10C')
                UPDATE  ADDRESS
                SET     AD_LINE2    = 'Apt. 10C'
                WHERE   AD_ID       = @ad_id
            ELSE
                UPDATE  ADDRESS
                SET     AD_LINE2    = 'Apt. 22'
                WHERE   AD_ID       = @ad_id
        END

------------------------------
--     COMPANY TABLE        --
------------------------------
    IF (@table_name = 'COMPANY')
        BEGIN
            -- COMPANY Table
            -- Update a row in the COMPANY table identified
            -- by @co_id, set the company's Standard and Poors
            -- credit rating to 'ABA' or to 'AAA'
            SELECT  @sp_rate    = CO_SP_RATE
            FROM    COMPANY
            WHERE   CO_ID       = @co_id

            IF (@sp_rate <> 'ABA')
                UPDATE  COMPANY
                SET     CO_SP_RATE  = 'ABA'
                WHERE   CO_ID       = @co_id
            ELSE
                UPDATE  COMPANY
                SET     CO_SP_RATE  = 'AAA'
                WHERE   CO_ID       = @co_id
        END

------------------------------
--     CUSTOMER TABLE       --
------------------------------	
    IF (@table_name = 'CUSTOMER')
        BEGIN
            -- CUSTOMER Table
            -- Update the second e-mail address fo a Customer
            -- identified by @c_id.  Set the ISP part of the 
            -- second e-mail address to '@mindspring.com' or
            -- '@earthlink.com'.
            SELECT  @useralias  = SUBSTRING(C_EMAIL_2,1,CHARINDEX('@',C_EMAIL_2,1)),
                    @isp        = SUBSTRING(C_EMAIL_2,(CHARINDEX('@',C_EMAIL_2,1) + 1),8000)
            FROM    CUSTOMER
            WHERE   C_ID        = @c_id

            If (@isp = 'mindspring.com')
            BEGIN
                UPDATE  CUSTOMER
                SET     C_EMAIL_2   = @useralias + 'earthlink.com'
                WHERE   C_ID        = @c_id
            END
            ELSE
            BEGIN
                UPDATE  CUSTOMER
                SET     C_EMAIL_2   = @useralias + 'mindspring.com'
                WHERE   C_ID        = @c_id
            END
        END

------------------------------
--  CUSTOMER_TAXRATE TABLE  --
------------------------------
    IF (@table_name = 'CUSTOMER_TAXRATE')
        BEGIN
            -- CUSTOMER_TAXRATE Table
            -- Find the current country tax rate code for the customer
            -- identified by @c_id.  Calculate cyclically the next tax rate code
            -- for the customer's country.  
            -- Update to the next country tax rate code.

            SELECT  @old_tax_rate   = CX_TX_ID
            FROM    CUSTOMER_TAXRATE
            WHERE   CX_C_ID         = @c_id AND
                    (CX_TX_ID like 'US%' OR CX_TX_ID like 'CN%')   -- U.S. or Canada tax rate code

            IF (LEFT(@old_tax_rate,2) = 'US')
                BEGIN
                IF (@old_tax_rate = 'US5')
                    set @new_tax_rate = 'US1'
                ELSE	-- Change string US<n> to US<n+1> for n=1,2,3,4
                    BEGIN
                        set @tax_num = ASCII(RIGHT(@old_tax_rate,1)) - ASCII('0') + 1
                        set @new_tax_rate = 'US' + CHAR(@tax_num + ASCII('0'))
                    END
                END
            ELSE    -- not U.S., so must be Canada 
               BEGIN
                IF (@old_tax_rate = 'CN4')
                    set @new_tax_rate = 'CN1'
                ELSE	-- Change string CN<n> to CN<n+1> for n=1,2,3
                    BEGIN
                        set @tax_num = ASCII(RIGHT(@old_tax_rate,1)) - ASCII('0') + 1
                        set @new_tax_rate = 'CN' + CHAR(@tax_num + ASCII('0'))
                    END
                END

            UPDATE  -- establish new country tax rate code
                CUSTOMER_TAXRATE
            SET 
                CX_TX_ID    = @new_tax_rate
            WHERE   
                CX_C_ID     = @c_id AND
                CX_TX_ID    = @old_tax_rate 
        END

------------------------------
--    DAILY_MARKET TABLE    --
------------------------------
    IF (@table_name = 'DAILY_MARKET')
        BEGIN
            -- DAILY_MARKET Table
            -- A security symbol, a day of the month, and a 
            -- random number to update DM_VOL column.
            -- The rows to be updated are those for the security 
            -- symbol passed in, and for that
            -- day in the month that was passed in.

            UPDATE  DAILY_MARKET
            SET     DM_VOL                  = DM_VOL + @vol_incr
            WHERE   DM_S_SYMB               = @symbol AND
                    DATEPART(day,DM_DATE)   = @day_of_month
        END

------------------------------
--     EXCHANGE TABLE       --
------------------------------
    IF (@table_name = 'EXCHANGE')
        BEGIN
            -- EXCHANGE Table
            -- No additional parameters are passed when the table is 
            -- the EXCHANGE table.  Since there are only 4 rows in the table,
            -- each row will have its EX_DESC column updated.  If the EX_DESC
            -- columns does not already end in 'LAST UPDATED ' and timestamp, that	
            -- string will be appended to EX_DESC.  If it already there, we will update
            -- the timestamp with the current data and time
            SELECT  @row_count  = COUNT(*)
            FROM    EXCHANGE
            WHERE   EX_DESC LIKE '%LAST UPDATED%'
			
            IF (@row_count = 0)
                UPDATE  EXCHANGE
                SET     EX_DESC	= SUBSTRING(EX_DESC,1,LEN(EX_DESC)) + ' LAST UPDATED ' + CONVERT(CHAR(23), GETDATE(), 21)
            ELSE
                UPDATE  EXCHANGE
                SET     EX_DESC	= SUBSTRING(EX_DESC,1,(LEN(EX_DESC)-23)) + CONVERT(CHAR(23), GETDATE(), 21)
        END

------------------------------
--     FINANCIAL TABLE      --
------------------------------
    IF (@table_name = 'FINANCIAL')
        BEGIN
            -- FINANCIAL Table
            -- Update the FINANCIAL table for a company identified by
            -- @co_id.  That company's FI_QTR_START_DATEs will be 
            -- updated to the second of the month or to the first of the month is the dates
            -- are already the second of the month
            SELECT  @row_count  = COUNT(*)
            FROM    FINANCIAL
            WHERE   FI_CO_ID    = @co_id AND
                    DATEPART(DAY,FI_QTR_START_DATE) = 1

            IF (@row_count > 0)
                UPDATE  FINANCIAL
                SET     FI_QTR_START_DATE   = DATEADD(DAY,1,FI_QTR_START_DATE)
                WHERE   FI_CO_ID            = @co_id
            ELSE
                UPDATE  FINANCIAL
                SET     FI_QTR_START_DATE   = DATEADD(DAY,-1,FI_QTR_START_DATE)
                WHERE   FI_CO_ID            = @co_id
        END

------------------------------
--     NEWS_ITEM TABLE      --
------------------------------
    IF (@table_name = 'NEWS_ITEM')
    BEGIN
        -- NEWS_ITEM Table
        -- Update the NEWS_ITEM table for a company identified by
        -- @co_id.  Update the NI_DTS by adding 1 day to the top
        -- news item
        UPDATE  NEWS_ITEM
        SET     NI_DTS  = DATEADD(DAY,1,NI_DTS)
        WHERE   NI_ID   IN (SELECT   NX_NI_ID
                           FROM     NEWS_XREF
                           WHERE    NX_CO_ID = @co_id)
     END

------------------------------
--      SECURITY TABLE      --
------------------------------
    IF (@table_name = 'SECURITY')
    BEGIN
        -- SECURITY Table
        -- Update the SECURITY table for a security identified by
        -- @symbol.  Increment the S_EXCH_DATE by 1 day
        UPDATE  SECURITY
        SET     S_EXCH_DATE = DATEADD(DAY,1,S_EXCH_DATE)
        WHERE   S_SYMB      = @symbol
     END

------------------------------
--      TAXRATE TABLE       --
------------------------------
    IF (@table_name = 'TAXRATE')
        BEGIN
            -- TAXRATE Table
            -- Update a tax rate identified by @tx_id.  The tax rate's
            -- TX_NAME will have a substring mdified from 'Tax' to 'tax"
            -- or or from 'tax' to 'Tax', depending on the current value. 

            SELECT  @tx_name    = TX_NAME
            FROM    TAXRATE
            WHERE   TX_ID       = @tx_id

            IF (@tx_name like '% Tax %')
                SELECT @tx_name = REPLACE(@tx_name, ' Tax ', ' tax ')
            ELSE
                SELECT @tx_name = REPLACE(@tx_name, ' tax ', ' Tax ')

            UPDATE  TAXRATE
            SET     TX_NAME = @tx_name
            WHERE   TX_ID   = @tx_id
        END

------------------------------
--     WATCH_ITEM TABLE     --
------------------------------
    IF (@table_name = 'WATCH_ITEM')
        BEGIN
            -- WATCH_ITEM Table
            -- Find the "middle" symbol in the current watch list.
            -- Find the "next" symbol in the SECURITY table that is "after" 
            -- the "middle" watch list symbol and not already in the watch list. 
            -- Update the "middle" watch list symbol with the "next" symbol.

            SELECT  @row_count = COUNT(*)           -- Number of rows is [50..150]
            FROM    WATCH_ITEM,
                    WATCH_LIST
            WHERE   WL_C_ID     = @c_id AND
                    WI_WL_ID    = WL_ID

            SET     @row_count  = (@row_count+1)/2  -- Calculate "middle" symbol index

            SELECT  TOP (@row_count)
                    @old_symbol = WI_S_SYMB         -- Find "current" middle symbol
            FROM    WATCH_ITEM,
                    WATCH_LIST
            WHERE   WL_C_ID     = @c_id AND
                    WI_WL_ID    = WL_ID
            ORDER BY
                    WI_S_SYMB ASC

            SELECT  TOP 1
                    @new_symbol = S_SYMB            -- Find "new" symbol for replacement
            FROM    SECURITY
            WHERE   S_SYMB > @old_symbol AND
                    S_SYMB NOT IN 
                           (SELECT  WI_S_SYMB
                            FROM    WATCH_ITEM,
                                    WATCH_LIST
                            WHERE   WL_C_ID     = @c_id AND
                                    WI_WL_ID    = WL_ID
                           )
            ORDER BY
                    S_SYMB ASC

            UPDATE  WATCH_ITEM
            SET     WI_S_SYMB   = @new_symbol       -- Update old symbol with new symbol
            FROM    WATCH_LIST
            WHERE   WL_C_ID     = @c_id AND
                    WI_WL_ID    = WL_ID AND
                    WI_S_SYMB   = @old_symbol

        END

    COMMIT TRANSACTION
END
