CREATE PROCEDURE [dbo].[OnlineFindProductPrices] @AStartDate DATETIME, @AEndDate DATETIME, @CountryNumber INT,
  @ProductNumber INT, @RoomTypeNumber INT AS
SET NOCOUNT ON

/*
Created by Troels Buch September 2015

Changes:
TBU20160108: Added Productname and description
TBU20150929: Added @OnlineRoomTypeNumber
TBU20151001: Added @ParentRoomTypeNumber
 
*/

DECLARE @DoRecalcPrices BIT
DECLARE @CalcTotalPrice MONEY
DECLARE @CalcArrivalPrice MONEY
DECLARE @Addon money, @BillTextProduct VARCHAR(50), @BillTextAddOn VARCHAR(50), @CalcPriceDay money, @PacketNumber int, @EnvironmentFee smallmoney, @FixedPrice bit, @VATProduct bit, @EnvironmentFeeType Float, @AIsDayProduct Bit, @SpecifiedDepartureAddOn Bit, @NamelistSetting int, @OnePersonPaysForAll Bit
DECLARE @DateNow Datetime, @UseZeroPrice BIT
DECLARE @DateStr Char(8), @ChepPriceTotal MONEY
DECLARE @PaxText VARCHAR(200), @PaxName VARCHAR(200)
DECLARE @Pax INT, @OnlineRoomTypeNumber INT, @ParentRoomTypeNumber INT
DECLARE @Productname VARCHAR(250), @ProductDescription VARCHAR(250)

/* Temp table used for product prices per product per roomtype */
CREATE TABLE #ProductPrices (
[RoomTypeNumber] [int] NOT NULL ,
[ProductNumber] [int] NOT NULL,
[Description] [VARCHAR] (255) NULL,
[RoomPriceFirstDay] [Money] NULL,
[RoomPrice] [money] NULL,
[RoomTypeName] [VARCHAR] (255) NULL,
[RoomPricePerDay] [Money] NULL,
[RoomPriceMinTotal] [Money] NULL,
[Pax] [INT] NULL,
[OnlineRoomTypeNumber] [INT] NULL,
[ParentRoomTypeNumber] [INT] NULL,
[ProductName] [VARCHAR] (255) NULL,
[ProductDescription] [VARCHAR] (255) NULL,
[UseZeroPrice] [Bit] NULL
)
ON [PRIMARY]

SET @Pax=1

WHILE @Pax < 10
BEGIN

EXEC ProductPrices @ProductNumber, @AStartDate, @AEndDate, @RoomTypeNumber, 1, @Pax, 0, 0, @CalcTotalPrice OUTPUT, @CalcArrivalPrice OUTPUT
SET @DoRecalcPrices = 1

SET @DateNow = @AStartDate
SET @ChepPriceTotal = -1
        	
WHILE (@DateNow < @AEndDate)
BEGIN
	exec [dbo].[ProductPricesDay] @ProductNumber, @AStartDate, @AEndDate, @DateNow, @RoomTypeNumber, 1, @Pax, 0, 0, @Addon OUTPUT, @BillTextProduct OUTPUT, @BillTextAddOn OUTPUT, @CalcPriceDay OUTPUT, @PacketNumber OUTPUT, @EnvironmentFee OUTPUT, @FixedPrice OUTPUT, @VATProduct OUTPUT,  @SpecifiedDepartureAddOn OUTPUT, @NamelistSetting = -1, @OnePersonPaysForAll = 0, @EnvironmentFeeType = 0 , @AIsDayProduct = 0 

	IF @ChepPriceTotal = -1
	BEGIN 
		SET @ChepPriceTotal = @CalcPriceDay+@Addon
		END
		ELSE
		BEGIN
			IF @CalcPriceDay < @ChepPriceTotal
				BEGIN
					SET @ChepPriceTotal = @CalcPriceDay+@Addon
				END
		END        	
	  SET @DateNow = DATEADD(DAY, 1, @DateNow)
	END

	INSERT INTO #ProductPrices VALUES (@RoomTypeNumber, @ProductNumber, /*@Description*/'', @CalcArrivalPrice, @CalcTotalPrice, /*@ProductName*/'', @CalcPriceDay, @ChepPriceTotal, @Pax, 0, 0, '', '', 1)

	SET @PaxText = ''

	select @PaxText=Description, @PaxName=OnlineRoomtypesTranslated.Text, @OnlineRoomTypeNumber=roomtypes.onlineRoomtypenumber, 
	@ParentRoomTypeNumber=roomtypes.ParentRoomTypeNumber  
	FROM OnlineRoomtypesTranslated (NOLOCK) 
	inner join roomtypes (NOLOCK) ON OnlineRoomtypesTranslated.onlineroomtypenumber = roomtypes.onlineroomtypenumber
	WHERE roomtypes.roomtypenumber= @RoomTypeNumber
	AND countryNumber=@CountryNumber
	AND pax = @Pax

	/*select @PaxText=Description, @PaxName=Text from OnlineRoomtypesTranslated (NOLOCK) 
	WHERE OnlineroomtypeNumber= @RoomTypeNumber 
	AND countryNumber=@CountryNumber
	AND pax = @Pax*/

	IF @PaxText = ''
	BEGIN
	  select @PaxText=Description, @PaxName=OnlineRoomtypesTranslated.Text, @OnlineRoomTypeNumber=roomtypes.onlineRoomtypenumber, 
	  @ParentRoomTypeNumber=roomtypes.ParentRoomTypeNumber 
	  FROM OnlineRoomtypesTranslated (NOLOCK) 
	  inner join roomtypes (NOLOCK) ON OnlineRoomtypesTranslated.onlineroomtypenumber = roomtypes.onlineroomtypenumber
	  WHERE roomtypes.roomtypenumber= @RoomTypeNumber
	  AND countryNumber=@CountryNumber
	  AND pax = 0 

	  /*select @PaxText=Description, @PaxName=Text from OnlineRoomtypesTranslated (NOLOCK) 
	  WHERE OnlineroomtypeNumber= @RoomTypeNumber 
	  AND countryNumber=@CountryNumber
	  AND pax = 0*/
	END

	UPDATE #ProductPrices SET RoomTypeName=@PaxName, Description=@PaxText, OnlineRoomTypeNumber=@OnlineRoomTypeNumber, ParentRoomTypeNumber=@ParentRoomTypeNumber 
	WHERE Pax=@Pax
	  SET @Pax = @Pax+1	

	SELECT @Productname=Name, @ProductDescription=Description1 
	FROM Translation WITH (NOLOCK) 
	INNER JOIN Products p WITH (NOLOCK) on ProductNumber=Translation.ObjectNumber 
	LEFT OUTER JOIN ProductDescription PD WITH (NOLOCK) on p.ProductNumber=PD.FK_ProductNumber and PD.Type=0 
	LEFT OUTER JOIN ProductsImagesXTab i on p.ProductNumber=i.ProductNumber 
	WHERE p.ProductNumber=@ProductNumber AND CountryNumber=@CountryNumber AND Translation.Type=2
	 	
	UPDATE #ProductPrices SET ProductName=@Productname, ProductDescription=@ProductDescription WHERE ProductNumber=@ProductNumber
END

SELECT * FROM #ProductPrices 

DROP TABLE #ProductPrices



