



CREATE PROCEDURE DBO.GetRoomLineProductInformation @Date datetime, @linenumber int, @productnumber int OUTPUT, @productname varchar(100) output, @Price money OUTPUT AS
SET NOCOUNT ON
declare @day int
declare @dayproductnumber int
declare @arrivaldate datetime
declare @departuredate datetime
declare @productnumber1 int
declare @productnumber2 int
declare @productnumber3 int
declare @productnumber4 int
declare @productday2 int
declare @productday3 int
declare @productday4 int
declare @adults int
declare @children1 int
declare @children2 int
declare @roomtypenumber int
declare @numberofrooms int
declare @dayproductname varchar(100)
declare @dayproductprice money
select @roomtypenumber = BookedTypeNumber, @numberofrooms = numberofrooms, @adults = adults, @children1 = children1, @children2 = children2, @arrivaldate = convert(datetime, departuredate, 112), @arrivaldate = convert(datetime, departuredate, 112), @productnumber1 = productnumber, @productnumber2 = productnumber2, @productnumber3 = productnumber3, @productnumber4 = productnumber4, @productday2 = day2, @productday3 = day3, @productday4 = day4 from reservationroomline WITH (nolock) where linenumber = @linenumber
set @day = convert(int, @date) - convert(int, @arrivaldate)
set @dayproductnumber = @productnumber1
if @productday2 >= @day set @dayproductnumber = @productnumber2
if @productday3 >= @day set @dayproductnumber = @productnumber3
if @productday4 >= @day set @dayproductnumber = @productnumber4
select @dayproductname = productname from products WITH (nolock) where productnumber = @dayproductnumber
declare @adatestr varchar(8)
declare @ddatestr varchar(8)
declare @datestr varchar(8)
set @adatestr = convert(varchar(8), @date-1, 112)
set @ddatestr = convert(varchar(8), @date+1, 112)
set @datestr = convert(varchar(8), @date, 112)

-- ******************************************************************************************
-- Generates SQL to get price on - BEGIN
-- ******************************************************************************************
declare @sql varchar(8000)
declare @result money
declare @datestamp datetime
declare @adultfield varchar(25)
declare @children1field varchar(25)
declare @children2field varchar(25)
declare @allfields varchar(800)
declare @addonadultsfields varchar(50)
declare @addonchildren1fields varchar(50)
declare @addonchildren2fields varchar(50)

set @allfields = ''
set @addonadultsfields = ''
set @addonchildren1fields = ''
set @addonchildren2fields = ''
set @datestamp = @date
if @productnumber = 0 select @productnumber = productnumber from products WITH (nolock) where productname = @productname
set @result = 0
if @adults > 9 set @adults = 9
if @children1 > 9 set @children1 = 9
if @children2 > 9 set @children2 = 9
if @datestamp = @arrivaldate 
begin
  if @adults > 1 set @addonadultsfields = 'AdultArrivalAddon' + Convert(VarChar(2), @adults) 
  else set @addonadultsfields = 'AdultArrivalAddon'
  if @children1 > 1 set @addonchildren1fields = 'Children1ArrivalAddon' + Convert(VarChar(2), @children1) 
  else set @addonchildren1fields = 'Children1ArrivalAddon'
  if @children2 > 1 set @addonchildren2fields = 'Children2ArrivalAddon' + Convert(VarChar(2), @children2) 
  else set @addonchildren2fields = 'Children2ArrivalAddon'
end
else
  if @datestamp = @departuredate 
  begin
    if @adults > 1 set @addonadultsfields = 'AdultDepartureAddon' + Convert(VarChar(2), @adults) 
    else set @addonadultsfields = 'AdultDepartureAddon'
    if @children1 > 1 set @addonchildren1fields = 'Children1DepartureAddon' + Convert(VarChar(2), @children1) 
    else set @addonchildren1fields = 'Children1DepartureAddon'
    if @children2 > 1 set @addonchildren2fields = 'Children2DepartureAddon' + Convert(VarChar(2), @children2) 
    else set @addonchildren2fields = 'Children2DepartureAddon'
  end
  else
    begin
      if @adults > 1 set @addonadultsfields = 'AdultStayAddon' + Convert(VarChar(2), @adults) else set @addonadultsfields = 'AdultStayAddon'
      if @children1 > 1 set @addonchildren1fields = 'Children1StayAddon' + Convert(VarChar(2), @children1) else set @addonchildren1fields = 'Children1StayAddon'
      if @children2 > 1 set @addonchildren2fields = 'Children2StayAddon' + Convert(VarChar(2), @children2) else set @addonchildren2fields = 'Children2StayAddon'
    end
set @adultfield     = 'AdultPrice' +  + Convert(VarChar(2), @adults)
set @children1field = 'Children1Price' +  + Convert(VarChar(2), @children1)
set @children2field = 'Children2Price' +  + Convert(VarChar(2), @children2)
if @adults > 0    set @allfields = @allfields + @adultfield else set @allfields = '0'
if @children1 > 0 set @allfields = @allfields + ' + ' + @children1field 
if @children2 > 0 set @allfields = @allfields + ' + ' + @children2field
if @adults > 0    set @allfields = @allfields + ' + ' + @addonadultsfields 
if @children1 > 0 set @allfields = @allfields + ' + ' + @addonchildren1fields 
if @children2 > 0 set @allfields = @allfields + ' + ' + @addonchildren2fields
set @sql = 'select ' + @allfields + ' as result from ProductPrice WITH (nolock) where productnumber = ' + convert(varchar(10), @productnumber) + ' and datestamp = ' + convert(varchar, @date, 112) + ' and Roomtypenumber = ' + convert(varchar(10), @roomtypenumber)
-- ******************************************************************************************
-- Generates SQL to get price on - END
-- ******************************************************************************************


--CREATE TABLE #pprice ( price MONEY )
--  INSERT INTO #pprice EXEC sp_sqlexec @sql
--  select @price = price from #pprice (nolock)
--DROP TABLE #pprice 

--print @date
--print @linenumber
--print @productnumber
--print @productname
--set @price = isnull(@price, 0)
--print convert(varchar, @price)

