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

-- SecurityDetailFrame1 is responsible for retrieving 
-- all available details about a security
CREATE PROCEDURE    SecurityDetailFrame1
                        @symbol             varchar(15),
                        @dm_date            date,
                        @max_rows           tinyint,
                        @access_lob_flag    tinyint
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED

	BEGIN TRANSACTION

    DECLARE @52_wk_high         numeric(8,2),   /* OUT 52 weeks high */ 
            @52_wk_high_date    date    ,       /* OUT 52 weeks high date */ 
            @52_wk_low          numeric(8,2),   /* OUT OUT 52 weeks low */ 
            @52_wk_low_date     date    ,       /* OUT 52 weeks low date */ 		
            @pe_ratio           numeric(5,2),   /* OUT price to earning */ 
            @num_out            numeric(12),    /* OUT outstanding shares */ 
            @s_name             varchar(70),    /* OUT security name */ 
            @start_date         date,           /* OUT date trading started */
            @exch_date          date,           /* OUT date security listed */
            @dividend           numeric(5,2),   /* OUT dividend */ 
            @yield              numeric(5,2),   /* OUT yield */ 		
            @co_id              bigint,         /* OUT company ID */
            @co_ad_ctry         varchar(80),    /* OUT company country */ 
            @co_zc_div          varchar(80),    /* OUT company county/state */ 
            @co_ad_line1        varchar(80),    /* OUT company address L1 */ 
            @co_ad_line2        varchar(80),    /* OUT company address L2 */ 
            @co_zc_town         varchar(80),    /* OUT company town */ 
            @co_ad_zip          char(12),       /* OUT company zip code */ 
            @co_ceo             char(46),       /* OUT name of CEO */ 
            @co_desc            char(150),      /* OUT company description */ 
            @co_name            varchar(60),    /* OUT company name */ 
            @co_st_id           char(4),        /* OUT status ID */ 
            @co_sp_rate         char(4),        /* OUT S&P rate */
            @co_open_date       date,           /* OUT date company opened */ 
            @ex_ad_ctry         varchar(80),    /* OUT exchange country */ 
            @ex_zc_div          varchar(80),    /* OUT exchange county/state */ 
            @ex_ad_line1        varchar(80),    /* OUT exchange address L1 */ 
            @ex_ad_line2        varchar(80),    /* OUT exchange address L2 */ 
            @ex_zc_town         varchar(80),    /* OUT exchange town */ 
            @ex_ad_zip          varchar(12),    /* OUT exchange zip code */ 		 
            @ex_date            datetime,       /* OUT date listed on exchange */ 
            @ex_desc            char(150),      /* OUT exchange description */ 
            @ex_name            char(100),      /* OUT name of exchange */ 
            @ex_num_symb        numeric(6),     /* OUT number of shares listed */ 
            @ex_open            int,	        /* OUT last open */ 
            @ex_close           int             /* OUT last close */


    -- Need to assign all this data to variables
    -- because co_id is used further.
    -- Mixing variable assigment with simple column
    -- select doesn't work.
    SELECT  @s_name             = S_NAME, 
            @co_id              = CO_ID, 
            @co_name            = CO_NAME, 
            @co_sp_rate         = CO_SP_RATE,
            @co_ceo             = CO_CEO, 
            @co_desc            = CO_DESC, 
            @co_open_date       = CO_OPEN_DATE, 
            @co_st_id           = CO_ST_ID, 			
            @co_ad_line1        = CA.AD_LINE1, 
            @co_ad_line2        = CA.AD_LINE2, 
            @co_zc_town         = ZCA.ZC_TOWN, 
            @co_zc_div          = ZCA.ZC_DIV, 
            @co_ad_zip          = CA.AD_ZC_CODE, 
            @co_ad_ctry         = CA.AD_CTRY, 
            @num_out            = S_NUM_OUT, 
            @start_date         = S_START_DATE, 
            @exch_date          = S_EXCH_DATE, 
            @pe_ratio           = S_PE, 
            @52_wk_high         = S_52WK_HIGH, 
            @52_wk_high_date    = S_52WK_HIGH_DATE, 
            @52_wk_low          = S_52WK_LOW, 
            @52_wk_low_date     = S_52WK_LOW_DATE, 
            @dividend           = S_DIVIDEND,
            @yield              = S_YIELD, 
            @ex_ad_line1        = EA.AD_LINE1, 
            @ex_ad_line2        = EA.AD_LINE2, 
            @ex_zc_town         = ZEA.ZC_TOWN, 
            @ex_zc_div          = ZEA.ZC_DIV, 
            @ex_ad_ctry         = EA.AD_CTRY,
            @ex_ad_zip          = EA.AD_ZC_CODE, 				
            @ex_desc            = EX_DESC, 
            @ex_name            = EX_NAME, 
            @ex_num_symb        = EX_NUM_SYMB, 
            @ex_open            = EX_OPEN,
            @ex_close           = EX_CLOSE
    FROM    SECURITY, 
            COMPANY, 
            ADDRESS CA, 
            ADDRESS EA,
            ZIP_CODE ZCA,
            ZIP_CODE ZEA,
            EXCHANGE 
    WHERE   S_SYMB          = @symbol AND
            CO_ID           = S_CO_ID AND
            CA.AD_ID        = CO_AD_ID AND
            EA.AD_ID        = EX_AD_ID AND
            EX_ID           = S_EX_ID AND
            CA.AD_ZC_CODE   = ZCA.ZC_CODE AND
            EA.AD_ZC_CODE   = ZEA.ZC_CODE
		
    -- return as a rowset
    SELECT  @s_name, @co_name, @co_sp_rate, @co_ceo,
            @co_desc, @co_open_date, @co_st_id, @co_ad_line1,
            @co_ad_line2, @co_zc_town, @co_zc_div, @co_ad_zip,
            @co_ad_ctry, @num_out, @start_date, @exch_date,
            @pe_ratio, @52_wk_high, @52_wk_high_date, @52_wk_low,
            @52_wk_low_date, @dividend, @yield, @ex_ad_line1,
            @ex_ad_line2, @ex_zc_town, @ex_zc_div, @ex_ad_zip,
            @ex_ad_ctry, @ex_open, @ex_close, @ex_name,
            @ex_num_symb, @ex_desc,
            -- while we are sending data back, go ahead and 
            -- retrieve last trade information
            LT_PRICE,
            LT_OPEN_PRICE,
            LT_VOL
    FROM    LAST_TRADE
    WHERE   LT_S_SYMB   = @symbol
			
    -- retrieve financial data					
    SELECT  TOP 20
            FI_YEAR, 
            FI_QTR, 
            FI_QTR_START_DATE, 
            FI_REVENUE, 
            FI_NET_EARN, 
            FI_BASIC_EPS, 
            FI_DILUT_EPS, 
            FI_MARGIN, 
            FI_INVENTORY, 
            FI_ASSETS, 
            FI_LIABILITY, 
            FI_OUT_BASIC, 
            FI_OUT_DILUT 
    FROM    FINANCIAL 
    WHERE   FI_CO_ID    = @co_id 
    ORDER   BY FI_YEAR ASC, 
            FI_QTR 

    -- retrieve daily information
    SET     ROWCOUNT @max_rows
		
    SELECT  DM_DATE, 
            DM_CLOSE, 
            DM_HIGH, 
            DM_LOW, 
            DM_VOL 
    FROM    DAILY_MARKET 
    WHERE   DM_S_SYMB   = @symbol AND
            DM_DATE     >= @dm_date
    ORDER   BY DM_DATE ASC 

    SET     ROWCOUNT 0

    IF ( @access_lob_flag = 1 )
        -- retrieve news information
        SELECT  TOP 2
                NI_ITEM, 
                NI_DTS, 
                NI_SOURCE, 
                NI_AUTHOR 
        FROM    NEWS_XREF, 
                NEWS_ITEM 
        WHERE   NI_ID       = NX_NI_ID AND 
                NX_CO_ID    = @co_id
    ELSE
        SELECT  TOP 2
                NI_HEADLINE,
                NI_SUMMARY,
                NI_DTS,
                NI_SOURCE,
                NI_AUTHOR
        FROM    NEWS_XREF,
                NEWS_ITEM
        WHERE   NI_ID       = NX_NI_ID AND
                NX_CO_ID    = @co_id

    SELECT  CO_NAME,
            IN_NAME
    FROM    COMPANY_COMPETITOR,
            COMPANY,
            INDUSTRY
    WHERE   CP_CO_ID = @co_id AND
            CO_ID = CP_COMP_CO_ID AND
            IN_ID = CP_IN_ID

    COMMIT TRANSACTION
END
