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:
    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 = 1

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
 
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

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
      BEGIN
        SELECT @PackageNumber = ArrivalPacketNumber
        FROM ProductPrice WITH (NOLOCK) 
        WHERE ProductNumber = @PlusProductNumber
        AND DateStamp = CONVERT(CHAR(8), @CurrentDate, 112)
      END
      ELSE IF @CurrentDate = @NewDepartureDate -- Departure
      BEGIN
        SELECT @PackageNumber = DeparturePacketNumber
        FROM ProductPrice WITH (NOLOCK) 
        WHERE ProductNumber = @PlusProductNumber
        AND DateStamp = CONVERT(CHAR(8), @CurrentDate, 112)
      END
      ELSE IF @CurrentDate > @NewArrivalDate AND @CurrentDate < @NewDepartureDate-- Stay
      BEGIN
        SELECT @PackageNumber = StayPacketNumber
        FROM ProductPrice WITH (NOLOCK) 
        WHERE ProductNumber = @PlusProductNumber
        AND DateStamp = CONVERT(CHAR(8), @CurrentDate, 112)
      END
    END    

    IF @ShowPrint = 1
    BEGIN
      PRINT '@PackageNumber'
      PRINT @PackageNumber
    END

    IF ISNULL(@PackageNumber, 0) > 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

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

      SET @ResultPackagePrice = @ResultPackagePrice + @PackagePrice
    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)
    END

    IF @ProductNumber > 0
    BEGIN    
      IF @CurrentDate = @ArrivalDate -- Arrival
      BEGIN
        SELECT @PackageNumber = ArrivalPacketNumber
        FROM ProductPrice WITH (NOLOCK) 
        WHERE ProductNumber = @ProductNumber
        AND DateStamp = CONVERT(CHAR(8), @CurrentDate, 112)
      END
      ELSE IF @CurrentDate = @DepartureDate -- Departure
      BEGIN
        SELECT @PackageNumber = DeparturePacketNumber
        FROM ProductPrice WITH (NOLOCK) 
        WHERE ProductNumber = @ProductNumber
        AND DateStamp = CONVERT(CHAR(8), @CurrentDate, 112)
      END
      ELSE -- Stay
      BEGIN
        SELECT @PackageNumber = StayPacketNumber
        FROM ProductPrice WITH (NOLOCK) 
        WHERE ProductNumber = @ProductNumber
        AND DateStamp = CONVERT(CHAR(8), @CurrentDate, 112)
      END
    END    
    IF ISNULL(@PackageNumber, 0) > 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
    END

    SET @CurrentDate = @CurrentDate +1
  END
END

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