CREATE PROCEDURE DBO.CalcPacketPriceTotal @ArrivalDate DATETIME, @DepartureDate DATETIME, @ProductNumber INT, @ProductNumber2 INT,  @ProductNumber3 INT,  @ProductNumber4 INT, @Day2 INT, @Day3 INT, @Day4 INT, 
  @RoomTypeNumber INT, @HotelNumber INT, @Adults INT, @Children1 INT, @Children2 INT, @ResultPackagePrice MONEY OUTPUT AS
SET NOCOUNT ON

/*
  Created by Kent Johansen 06. september 2006

  Calc the package price for a specific period

  Changes:
	MLJ 2022-10-06. Optimized. 
    KJ 20210113: Package value not calculated correct Case #CR203839
    KJ 20131014: ProductPrice Rewrite.
    KJ 20080206: Bug: Fix for "Package calculated wrong"
    KJ 20070308: Fix for New arrival + departure when new product for +products.
    KJ 20061211: Fix for "Error converting @ResultPackagePrice" 
*/

DECLARE @ShowPrint BIT
SET @ShowPrint = 0

DECLARE @CurrentDate DATETIME
DECLARE @PackageNumber INT
DECLARE @CleaningWareNumber INT
DECLARE @PriceOverwrite MONEY
DECLARE @PackagePrice MONEY
DECLARE @VatByPacketValue MONEY
DECLARE @sql NVARCHAR(2000)

DECLARE @PlusProductNumber INT
DECLARE @PlusLastDay INT

DECLARE @NewArrivalDate DATETIME
DECLARE @NewDepartureDate DATETIME

DECLARE @PacketType INT
 
SET @PlusLastDay = 0

SET @ResultPackagePrice = 0
SET @PackagePrice = 0

IF ISNULL(@Day2, 0) = 0 SET @Day2 = 0
IF ISNULL(@Day3, 0) = 0 SET @Day3 = 0
IF ISNULL(@Day4, 0) = 0 SET @Day4 = 0
IF ISNULL(@Adults, 0) = 0 SET @Adults = 0
IF ISNULL(@Children1, 0) = 0 SET @Children1 = 0
IF ISNULL(@Children2, 0) = 0 SET @Children2 = 0

SET @CurrentDate = @ArrivalDate

DECLARE @PackageViews TABLE
(
	View_FK_ProductNumber int,
    View_PacketType int ,
    View_DateStamp datetime
)

IF ISNULL(@Day2, 0) > 0
BEGIN
  IF @ShowPrint = 1
  BEGIN 
    PRINT '+Product'
    PRINT '@ArrivalDate'
    PRINT CONVERT(CHAR(8), @ArrivalDate, 112)
    PRINT '@Day2'
    PRINT @Day2
    PRINT '@Day3'
    PRINT @Day3
    PRINT '@Day4'
    PRINT @Day4
  END 

  WHILE @CurrentDate <= @DepartureDate
  BEGIN
	IF @ShowPrint = 1
	BEGIN
		PRINT '@CurrentDate'
		PRINT CONVERT(CHAR(8), @CurrentDate, 112)
	END

	IF (@CurrentDate+1 >= CONVERT(DATETIME, @ArrivalDate)) AND (@CurrentDate+1 < CONVERT(DATETIME, @ArrivalDate)+ @Day2)
	BEGIN
		SET @PlusProductNumber = @ProductNumber
		SET @NewArrivalDate = @ArrivalDate
		SET @NewDepartureDate = @DepartureDate   
	END
	ELSE IF ((@CurrentDate+1 >= CONVERT(DATETIME, @ArrivalDate)+@Day2) AND (@CurrentDate+1 <= CONVERT(DATETIME, @ArrivalDate)+@Day3)) OR (@Day3=0)
	BEGIN
		SET @PlusProductNumber = @ProductNumber2
		IF @PlusLastDay <> 2
		BEGIN
		SET @PlusLastDay = 2
		SET @NewArrivalDate = @CurrentDate
		SET @NewDepartureDate = @ArrivalDate + @Day2   
		END
	END
	ELSE IF ((@CurrentDate+1 >= CONVERT(DATETIME, @ArrivalDate)+@Day3) AND (@CurrentDate+1 <= CONVERT(DATETIME, @ArrivalDate)+@Day4)) OR (@Day4=0)
	BEGIN
		SET @PlusProductNumber = @ProductNumber3
		IF @PlusLastDay <> 3
		BEGIN
		SET @PlusLastDay = 3
		SET @NewArrivalDate = @CurrentDate
		SET @NewDepartureDate = @ArrivalDate + @Day3   
		END
	END
	ELSE IF (@CurrentDate+1 >= CONVERT(DATETIME, @ArrivalDate)+@Day4) AND (@CurrentDate+1 <= CONVERT(DATETIME, @DepartureDate))
	BEGIN
		SET @PlusProductNumber = @ProductNumber4
		IF @PlusLastDay <> 4
		BEGIN
		SET @PlusLastDay = 4
		SET @NewArrivalDate = @CurrentDate
		SET @NewDepartureDate = @ArrivalDate + @Day4   
		END
	END

	IF @ShowPrint = 1
	BEGIN
		PRINT '@PlusProductNumber'
		PRINT @PlusProductNumber
		PRINT '@NewArrivalDate'
		PRINT @NewArrivalDate
		PRINT '@NewDepartureDate'
		PRINT @NewDepartureDate
		PRINT '@DepartureDate'
		PRINT @DepartureDate
		PRINT '@ArrivalDate'
		PRINT @ArrivalDate
		PRINT '@CurrentDate'
		PRINT @CurrentDate
	END

	IF @PlusProductNumber > 0
	BEGIN    
		SET @PackageNumber = 0
		IF @CurrentDate = @NewArrivalDate -- Arrival
		SET @PacketType = 0
		ELSE IF @CurrentDate = @NewDepartureDate -- Departure
		SET @PacketType = 2
		ELSE IF @CurrentDate > @NewArrivalDate AND @CurrentDate < @NewDepartureDate -- Stay
		SET @PacketType = 1

		INSERT INTO @PackageViews VALUES (@PlusProductNumber,@PacketType,@CurrentDate)
    END    

    SET @CurrentDate = @CurrentDate + 1
  END
END
ELSE
BEGIN
  WHILE @CurrentDate <= @DepartureDate
  BEGIN
    IF @ShowPrint = 1
    BEGIN
      PRINT '@CurrentDate'
      PRINT CONVERT(CHAR(8), @CurrentDate, 112)
      PRINT '@ArrivalDate'
      PRINT @ArrivalDate
      PRINT '@DepartureDate'
      PRINT @DepartureDate
    END

    IF @ProductNumber > 0
    BEGIN    
		SET @PackageNumber = 0
		IF CONVERT(CHAR(8), @CurrentDate, 112) = @ArrivalDate -- Arrival
		SET @PacketType = 0
		ELSE IF CONVERT(CHAR(8), @CurrentDate, 112) = @DepartureDate -- Departure
		BEGIN
		SET @PacketType = 2
		END
		ELSE 
		SET @PacketType = 1
          
		IF @ShowPrint = 1
		BEGIN
		PRINT '@PacketType'
		PRINT @PacketType
		END

		INSERT INTO @PackageViews VALUES (@ProductNumber,@PacketType,@CurrentDate)
    END    

    SET @CurrentDate = @CurrentDate + 1
  END
END

DECLARE PackageViewsCursor CURSOR FOR
SELECT FK_PacketNumber
FROM @PackageViews PV
CROSS APPLY (
	SELECT TOP 1 ProductPricePacket.FK_PacketNumber
	FROM ProductPricePacket WITH (NOLOCK) 
	WHERE ProductPricePacket.FK_ProductNumber=PV.View_FK_ProductNumber 
	AND ProductPricePacket.PacketType=PV.View_PacketType 
	AND PV.View_DateStamp BETWEEN ProductPricePacket.StartDate AND ProductPricePacket.EndDate
	AND ISNULL(ProductPricePacket.FK_PacketNumber,0) > 0
	ORDER BY ProductPricePacket.Id DESC
) PPP

OPEN PackageViewsCursor
FETCH NEXT FROM PackageViewsCursor INTO @PackageNumber
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N'EXEC CalcPacketPrice 1,' + CONVERT(VARCHAR(10), @RoomTypeNumber) + ', ' + CONVERT(VARCHAR(8), @CurrentDate, 112) + ', ' + CONVERT(VARCHAR(10), @PackageNumber) + ', ' + CONVERT(VARCHAR(10), @ProductNumber) + ', ' + 
    CONVERT(VARCHAR(10), @Adults) + ', ' + CONVERT(VARCHAR(10), @Children1) + ', ' + CONVERT(VARCHAR(10), @Children2) + ', @PackagePrice OUTPUT, @VatByPacketValue OUTPUT' + ', -1, 0'
    IF @ShowPrint = 1
	    PRINT @sql
    EXEC sp_executesql @sql, N'@PackagePrice MONEY OUTPUT, @VatByPacketValue MONEY OUTPUT', @PackagePrice OUTPUT, @VatByPacketValue OUTPUT
    SET @ResultPackagePrice = @ResultPackagePrice + @PackagePrice

	FETCH NEXT FROM PackageViewsCursor INTO @PackageNumber
END
CLOSE PackageViewsCursor
DEALLOCATE PackageViewsCursor

IF @ShowPrint = 1
BEGIN
  PRINT '@ResultPackagePrice'
  PRINT CONVERT(VARCHAR(10), @ResultPackagePrice)
END