USE [EuroInvestorStockDB]
GO
/****** Object:  StoredProcedure [dbo].[ParseXMLClosePrice ]    Script Date: 2/22/2017 1:43:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [dbo].[ParseXMLClosePrice ]
   @XML XML
   WITH RECOMPILE
AS
BEGIN
	SET NOCOUNT ON;
	declare @sTime	 datetime
		set @stime  = getdate()

DECLARE @TempStock TABLE
(
Date datetime,
	ExchangeId integer,
	InstrumentID	int,
	OpenPrice decimal(11,4),
	High decimal(11,4),
	Low decimal(11,4),
	ClosePrice decimal(11,4),
	TotVol bigint,
	Multiplier integer,
    CombinedSymbol NVARCHAR(100),
	Currency NVARCHAR(3),
	Symbol  VarChar(20),
	PrevClose decimal(11,4),
	CloseDate datetime,
	change decimal(11,4)
);



	INSERT INTO
	@TempStock
	(Date,OpenPrice,High,Low,CombinedSymbol,ClosePrice,Currency,TotVol,CloseDate,change,PrevClose)
  SELECT
  Stock.value('Date[1]','datetime') AS Date,
  	Stock.value('Open[1]','decimal(11,4)') AS OpenPrice,
	Stock.value('High[1]','decimal(11,4)') AS High,
	Stock.value('Low[1]','decimal(11,4)') AS Low,
    Stock.value('Security[1]/Symbol[1]','NVARCHAR(100)') AS CombinedSymbol,
    Stock.value('Last[1]','decimal(11,4)') AS ClosePrice,
	Stock.value('Currency[1]','NVARCHAR(3)') AS Currency,
	Stock.value('Volume[1]','bigint') AS TotVol,
	Stock.value('EndOfDayPriceDate[1]','datetime') AS CloseDate,
	Stock.value('ChangeFromLastClose[1]','decimal(11,4)') AS change,
	Stock.value('LastClose[1]','decimal(11,4)') AS PrevClose
    FROM @XML.nodes('EndOfDayQuote')Catalog(Stock)

	print 'time to finish XML read -->' + convert( varchar(50) ,(cast (getdate()-@stime as float )*24*60*60 ))

--Get the Symbol and Exchange
update @Tempstock
		SET
		-- Go from the beginning to the last '.'
			Symbol = substring(CombinedSymbol, 1, LEN(combinedSymbol)- charindex('.', reverse(CombinedSymbol))),
			ExchangeId = ( select [ExchangeID]
			 FROM  [EuroInvestorStockDB].[dbo].[MicExchange]  with(nolock)
			 -- Go from the last '.' till the end
			 where [Mic]= substring(combinedSymbol, LEN(combinedSymbol)- charindex('.', reverse(combinedSymbol))+2, LEN(combinedSymbol)))

	print 'time to finish Reading the symbol -->' + convert( varchar(50) ,(cast (getdate()-@stime as float )*24*60*60 ))
--take care of E: case
update @Tempstock
		SET Symbol = ( IIF (EXISTS (select Top 1 [Symbol] from Instrument  where Symbol = CONCAT('E:',SI.Symbol) and ExchangeId=SI.ExchangeId ),CONCAT('E:',SI.Symbol),SI.Symbol))
		from @TempStock AS SI
-- Take care of conversion
print 'time to finish the E: case -->' + convert( varchar(50) ,(cast (getdate()-@stime as float )*24*60*60 ))



 UPDATE @Tempstock
            SET
			SI.Change = RAN.Change,
			SI.InstrumentID = RAN.ID,
			SI.Multiplier = IIF(RAN.Currency = 'GBX',100,1)
			from @TempStock AS SI
			INNER JOIN  Instrument  AS RAN with(nolock)
			ON  SI.Symbol=RAN.Symbol and SI.Exchangeid=RAN.Exchangeid

print 'time to finish the GBX conversion  -->' + convert( varchar(50) ,(cast (getdate()-@stime as float )*24*60*60 ))


-- Insert into Closeprice

INSERT INTO
	Close_Price (Date, ExchangeID, InstrumentID, OpenPrice,High,Low,ClosePrice,TotVol)
	  SELECT
			IP.Date  AS Date,
  			IP.ExchangeID AS ExchangeID,
			IP.InstrumentID as InstrumentID,
			IP.OpenPrice* IP.Multiplier as OpenPrice,
			IP.High * IP.Multiplier  as High,
			IP.Low * IP.Multiplier  as Low,
			Ip.ClosePrice * IP.Multiplier  as ClosePrice,
			IIF ( IP.TotVol > 2147483647, 2147483647, CONVERT(INT,IP.TotVol)) as TotVol
		FROM @TempStock AS IP
		  IF @@RowCount = 0
            BEGIN
                print 'Did not update Close_Price Table '
            END

print 'time to finish  insertion to CLosePRICE  -->' + convert( varchar(50) ,(cast (getdate()-@stime as float )*24*60*60 ))

-- Update the instrument table upon market closing
  UPDATE SI
            SET
                Timestamp = GETDATE(),
                SI.PrevClose = ISNULL(RAN.PrevClose*RAN.Multiplier, SI.PrevClose),
				-- Upon closing, the last price is close price
				SI.Last = RAN.ClosePrice*RAN.Multiplier,
				-- Setting yesterday volume
				SI.YesterdayVol = IIF ( RAN.TotVol > 2147483647, 2147483647, CONVERT(INT,RAN.TotVol))
			from Instrument AS SI
			INNER JOIN @TempStock  AS RAN
			ON  SI.Id=RAN.InstrumentID
            IF @@RowCount = 0
            BEGIN
                print 'Did not update Instrument Table '
            END
			Select * from @TempStock
			print 'time to finish -->' + convert( varchar(50) ,(cast (getdate()-@stime as float )*24*60*60 ))

RETURN;
END;