CREATE PROCEDURE DBO.CalcOwnerCleaningPrice @ArrivalDate DATETIME, @DepartureDate DATETIME, @ProductNumber INT, @ProductNumber2 INT,  @ProductNumber3 INT,  @ProductNumber4 INT, @Day2 INT, @Day3 INT, @Day4 INT,  @RoomTypeNumber INT,  @HotelNumber INT, @CleaningPrice MONEY OUTPUT AS
SET NOCOUNT ON

/*
  Created by Kent Johansen 27. august 2006

  Calc the cleaning price based on package

  Todo: Fix for +product

  Changes:
    KJ 20131014: ProductPrice Rewrite.
    KJ 20081113: Bug Fix for "@PriceOverwrite not set"
    KJ 20070410: Department Rewrite

*/

DECLARE @ShowPrint BIT
DECLARE @CurrentDate DATETIME
DECLARE @PackageNumber INT
DECLARE @CleaningWareNumber INT
DECLARE @PriceOverwrite MONEY
DECLARE @PacketType INT

SET @ShowPrint = 1
SET @CleaningPrice = 0

SELECT @CleaningWareNumber = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'CleaningWareName'
IF @CleaningWareNumber > 0
BEGIN
  IF ISNULL(@Day2, 0) = 0 SET @Day2 = 0
  IF ISNULL(@Day3, 0) = 0 SET @Day3 = 0
  IF ISNULL(@Day4, 0) = 0 SET @Day4 = 0

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

      SET @PackageNumber = 0
      IF @ProductNumber > 0
      BEGIN    
        IF @CurrentDate = @ArrivalDate -- Arrival
          SET @PacketType = 0
        ELSE IF @CurrentDate = @DepartureDate -- Departure
          SET @PacketType = 1
        ELSE -- Stay
          SET @PacketType = 2
          
        SELECT TOP 1 @PackageNumber = FK_PacketNumber 
        FROM ProductPricePacket WITH (NOLOCK) 
        WHERE FK_ProductNumber = @ProductNumber 
        AND PacketType = @PacketType
        AND StartDate >= @CurrentDate
        AND EndDate <= @CurrentDate
        ORDER BY Id DESC
      END    

      IF ISNULL(@PackageNumber, 0) > 0
      BEGIN
        SELECT @PriceOverwrite = PacketItem_New.PriceOverwrite
        FROM Ware WITH (NOLOCK)
        INNER JOIN ACCOUNTS WITH (NOLOCK) ON  Ware.ResultAccountNumber = Accounts.AccountNumber
        INNER JOIN PacketItem_New WITH (NOLOCK) ON  Ware.WareNumber = PacketItem_New.WareNumber
        LEFT OUTER JOIN PacketItem_NewRoomTypeXtab WITH (NOLOCK) ON PacketItem_NewRoomTypeXtab.PacketItemNumber = PacketItem_New.PacketItemNumber  
        INNER JOIN PacketItem_NewDepartmentXTab pd WITH (NOLOCK) ON pd.PacketItemNumber = PacketItem_New.PacketItemNumber 
          AND pd.Type = 2
        INNER JOIN Departments d WITH (NOLOCK) ON d.PK_Department = pd.FK_Department
        WHERE PacketItem_New.PacketNumber=@PackageNumber
        AND ((@HotelNumber = -1) OR (d.HotelNumber = @HotelNumber))
        AND PacketItem_NewRoomTypeXtab.RoomTypeNumber = @RoomTypeNumber
        AND Ware.WareNumber = @CleaningWareNumber
     
        SET @CleaningPrice = @CleaningPrice + @PriceOverwrite
      END

      SET @CurrentDate = @CurrentDate +1
    END
  END

  IF @ShowPrint = 1
  BEGIN
    PRINT '@CleaningPrice'
    PRINT CONVERT(CHAR(8), @CleaningPrice, 112)
  END
END

