CREATE PROCEDURE dbo.GetPricePackageAdjusted @APacketNumber INT, @APieces INT = 1, @AAdultPax INT, @AChild1Pax INT, @AChild2Pax INT,  @AArrivalDatetime DATETIME, @ADepartureDatetime DATETIME, @AFirstDayPrice MONEY OUTPUT, @ATotalPrice MONEY OUTPUT
AS
BEGIN
  DECLARE @PacketType INT
  DECLARE @PacketNumber INT
  DECLARE @CurrentDate DATETIME
  DECLARE @Price MONEY
  DECLARE @TotalPrice MONEY
  DECLARE @DebType INT

  DECLARE @PacketTime CHAR(5)
  DECLARE @ItemPrice MONEY
  DECLARE @IsMandatory BIT
  DECLARE @CurrentDateAndTime DATETIME
  DECLARE @NumberOfDays INT
  DECLARE @Arr24HoursStart TIME
  DECLARE @ArrTime TIME
  DECLARE @DepTime TIME
  DECLARE @ArrDate DATE
  DECLARE @DepDate DATE
  DECLARE @ArrivalDatetime DATETIME
  DECLARE @DepartureDatetime DATETIME

  SELECT @Arr24HoursStart = CAST(KeyValue AS TIME) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'Arr24HoursStarts'
  SELECT @ArrTime = CAST(@AArrivalDatetime AS TIME)
  SELECT @ArrDate = CAST(@AArrivalDatetime AS DATE)
  SELECT @DepTime = CAST(@ADepartureDatetime AS TIME)
  SELECT @DepDate = CAST(@ADepartureDatetime AS DATE)
  SET @ArrivalDatetime = @AArrivalDatetime
  SET @DepartureDatetime = @ADepartureDatetime
  
  SET @AFirstDayPrice = 0
  SET @ATotalPrice = 0
  SET @TotalPrice = 0
  SET @NumberOfDays = DATEDIFF(day, @AArrivalDatetime, @ADepartureDatetime) + 1
  
  SELECT *
  INTO #PacketItem_NEW
  FROM PacketItem_NEW WITH (NOLOCK)
  WHERE PacketItem_NEW.PacketNumber = @APacketNumber


  -- If arrivaltime or departuretime is less than Arr24HoursStart,
  -- we need to fetch the right package items and 
  -- alter the arrival/departure dates for later processing
  IF (@ArrTime < @Arr24HoursStart) OR (@DepTime < @Arr24HoursStart)
  BEGIN
    IF (@ArrTime < @Arr24HoursStart)
	BEGIN
      DECLARE PackageCur CURSOR FOR
        SELECT Time, PriceOverwrite, IsMandatory, DebType
	    FROM #PacketItem_NEW WITH (NOLOCK)
	    WHERE (Time >= @ArrTime)
		  AND (Time < @Arr24HoursStart)
	    ORDER BY Time ASC
	  OPEN PackageCur
	  FETCH NEXT FROM PackageCur INTO @PacketTime, @ItemPrice, @IsMandatory, @DebType
	  WHILE (@@FETCH_STATUS <> -1)
	  BEGIN
	    -- Calculate itemprice with regards to debitor type on the packetitem

		IF @DebType = 1 --Fixed Price Per room /Hall (adult)		
		BEGIN
		  SELECT @TotalPrice = @ItemPrice * @APieces
		END
		ELSE IF @DebType = 2 --Percent og roomprice (Adult)
		BEGIN
		  SELECT @TotalPrice = @TotalPrice + 0
		END
		ELSE IF @DebType = 3 -- Price * Adult-pax
		BEGIN
		  SELECT @TotalPrice = @TotalPrice + (@ItemPrice * @AAdultPax) 
		END
		ELSE IF @DebType = 4 -- Fixed price per room (Children1)
		BEGIN
		  SELECT @TotalPrice = @TotalPrice + (@ItemPrice * @APieces)
		END
		ELSE IF @DebType = 5 -- Price * Children1-pax
		BEGIN
		  SELECT @TotalPrice = @TotalPrice + (@ItemPrice *  @AChild1Pax)
		END
		ELSE IF @DebType = 6 -- Percent of roomprice (Children1)
		BEGIN
		  SELECT @TotalPrice = @TotalPrice + 0
		END
		ELSE IF @DebType = 7 -- Fixed price per room (Children2)
		BEGIN
		  SELECT @TotalPrice = @TotalPrice + (@ItemPrice * @APieces)
		END
		ELSE IF @DebType = 8 -- Price * Children2-pax
		BEGIN
		  SELECT @TotalPrice = @TotalPrice + (@ItemPrice * @AChild2Pax)
		END
		ELSE IF @DebType = 9 -- Percent of roomprice (Children2)
		BEGIN
		  SELECT @TotalPrice = @TotalPrice + 0
		END
  	    FETCH NEXT FROM PackageCur INTO @PacketTime, @ItemPrice, @IsMandatory, @DebType
	  END
	  CLOSE PackageCur
	  DEALLOCATE PackageCur

	  SET @ArrDate = CAST(DATEADD(day, 1, CAST(@ArrDate AS DATETIME)) AS DATE)
	  SET @ArrivalDatetime =  CAST(@ArrDate AS DATETIME) + CAST(@Arr24HoursStart AS DATETIME)
	END

	IF (@DepTime < @Arr24HoursStart)
	BEGIN
      DECLARE PackageCur CURSOR FOR
        SELECT Time, PriceOverwrite, IsMandatory, DebType
	    FROM #PacketItem_NEW WITH (NOLOCK)
	    WHERE (Time <= @DepTime)
	    ORDER BY Time ASC
	  OPEN PackageCur
	  FETCH NEXT FROM PackageCur INTO @PacketTime, @ItemPrice, @IsMandatory, @DebType
	  WHILE (@@FETCH_STATUS <> -1)
	  BEGIN
	    -- Calculate itemprice with regards to debitor type on the packetitem

		IF @DebType = 1 --Fixed Price Per room /Hall (adult)		
		BEGIN
		  SELECT @TotalPrice = @ItemPrice * @APieces
		END
		ELSE IF @DebType = 2 --Percent og roomprice (Adult)
		BEGIN
		  SELECT @TotalPrice = @TotalPrice + 0
		END
		ELSE IF @DebType = 3 -- Price * Adult-pax
		BEGIN
		  SELECT @TotalPrice = @TotalPrice + (@ItemPrice * @AAdultPax) 
		END
		ELSE IF @DebType = 4 -- Fixed price per room (Children1)
		BEGIN
		  SELECT @TotalPrice = @TotalPrice + (@ItemPrice * @APieces)
		END
		ELSE IF @DebType = 5 -- Price * Children1-pax
		BEGIN
		  SELECT @TotalPrice = @TotalPrice + (@ItemPrice *  @AChild1Pax)
		END
		ELSE IF @DebType = 6 -- Percent of roomprice (Children1)
		BEGIN
		  SELECT @TotalPrice = @TotalPrice + 0
		END
		ELSE IF @DebType = 7 -- Fixed price per room (Children2)
		BEGIN
		  SELECT @TotalPrice = @TotalPrice + (@ItemPrice * @APieces)
		END
		ELSE IF @DebType = 8 -- Price * Children2-pax
		BEGIN
		  SELECT @TotalPrice = @TotalPrice + (@ItemPrice * @AChild2Pax)
		END
		ELSE IF @DebType = 9 -- Percent of roomprice (Children2)
		BEGIN
		  SELECT @TotalPrice = @TotalPrice + 0
		END
  	    FETCH NEXT FROM PackageCur INTO @PacketTime, @ItemPrice, @IsMandatory, @DebType
	  END
	  CLOSE PackageCur
	  DEALLOCATE PackageCur

	  SET @DepDate = CAST(DATEADD(day, -1, CAST(@DepDate AS DATETIME)) AS DATE)
	  SET @DepartureDatetime = CAST(@DepDate AS DATETIME) + CAST('23:59:59' AS DATETIME)
	END

    SET @NumberOfDays = DATEDIFF(day, @ArrivalDatetime, @DepartureDatetime) + 1
  END

  SET @CurrentDate = @ArrivalDatetime
    
  WHILE @CurrentDate <= @DepartureDatetime
  BEGIN
    DECLARE PackageCur CURSOR FOR
	  SELECT Time, PriceOverwrite, IsMandatory, DebType
	  FROM #PacketItem_NEW WITH (NOLOCK)
	  ORDER BY TIME ASC
    OPEN PackageCur
	FETCH NEXT FROM PackageCur INTO @PacketTime, @ItemPrice, @IsMandatory, @DebType
	WHILE (@@FETCH_STATUS <> -1)
	BEGIN
	  SET @CurrentDateAndTime = CONVERT(datetime, CONVERT(CHAR(10),@CurrentDate, 112) + ' ' + @PacketTime, 112)
	  IF ((@CurrentDateAndTime >= @ArrivalDatetime) AND (@CurrentDateAndTime <= @DepartureDatetime)) OR (@IsMandatory = 1)
	  BEGIN
	    -- Calculate itemprice with regards to debitor type on the packetitem

		IF @DebType = 1 --Fixed Price Per room /Hall (adult)		
		BEGIN
		  SELECT @TotalPrice = @ItemPrice * @APieces
		END
		ELSE IF @DebType = 2 --Percent og roomprice (Adult)
		BEGIN
		  SELECT @TotalPrice = @TotalPrice + 0
		END
		ELSE IF @DebType = 3 -- Price * Adult-pax
		BEGIN
		  SELECT @TotalPrice = @TotalPrice + (@ItemPrice * @AAdultPax) 
		END
		ELSE IF @DebType = 4 -- Fixed price per room (Children1)
		BEGIN
		  SELECT @TotalPrice = @TotalPrice + (@ItemPrice * @APieces)
		END
		ELSE IF @DebType = 5 -- Price * Children1-pax
		BEGIN
		  SELECT @TotalPrice = @TotalPrice + (@ItemPrice *  @AChild1Pax)
		END
		ELSE IF @DebType = 6 -- Percent of roomprice (Children1)
		BEGIN
		  SELECT @TotalPrice = @TotalPrice + 0
		END
		ELSE IF @DebType = 7 -- Fixed price per room (Children2)
		BEGIN
		  SELECT @TotalPrice = @TotalPrice + (@ItemPrice * @APieces)
		END
		ELSE IF @DebType = 8 -- Price * Children2-pax
		BEGIN
		  SELECT @TotalPrice = @TotalPrice + (@ItemPrice * @AChild2Pax)
		END
		ELSE IF @DebType = 9 -- Percent of roomprice (Children2)
		BEGIN
		  SELECT @TotalPrice = @TotalPrice + 0
		END
	  END
  	  FETCH NEXT FROM PackageCur INTO @PacketTime, @ItemPrice, @IsMandatory, @DebType
	END
	CLOSE PackageCur
	DEALLOCATE PackageCur
    SET @CurrentDate = @CurrentDate + 1
  END
  
  DROP TABLE #PacketItem_NEW

  SELECT @ATotalPrice = @TotalPrice

  IF @TotalPrice > 0
    SELECT @Price = ((@TotalPrice / (@AAdultPax + @AChild1Pax + @AChild2Pax)) / @NumberOfDays)
  ELSE
    SELECT @Price = 0
  SELECT @AFirstDayPrice = @Price
END