CREATE PROCEDURE dbo.RecurringPatternCreateArrangement @AReferenceNumber INT AS

--Created AV 2008 0526 Used for Feriebureau Bornholm
--Last modified 2009 0106 EXT: added new rule: Multiplywithpax
--Last modified 2008 1216 EXT: Extra person price: Children1 is always free. No price for them.....
--Last modified 2008 1107 bug in added support for linking wares to roomtypes instead of just room numbers
--Last modified 2008 1106 added support for linking wares to roomtypes instead of just room numbers
--Last modified 2008 0923 dont include the optional wares
--Last modified 2008 0723 Fix for extra person problem again again...
--Last modified 2008 0707 Fix for extra person problem again
--Last modified 2008 0703 Fix for extra person problem
--Last modified 2008 0602 Readonly field added
--Last modified 2008 0603 Validate on Adulst Children1 and children 2
--Last modified 2008 0609 RecurringRule ordinal numbers changed
--Last modified 2008 0609 Price Addons implemented
--Last modified 2008 0609 CollectArrival and CollectDeparture implemented
--Last modified 2008 0610 Fix for addons: Only addon for the persons which is over the limit

/*
(rtUnknown : 0
rtPricePerTime : 1
rtRequiredPicasso : 2
rtRequiredOnline : 3
rtNumberForSale: 4:
rtArrival : 5
rtStay : 6
rtDeparture : 7
rtCollectArrival : 8
rtCollectDeparture : 9
rtAdult : 10
rtChildren1 : 11
rtChildren2 : 12
rtPax : 13
rtAddonPerAdult : 14
rtAddonPerChildren1 : 15
rtAddonPerChildren2 : 16
rtOptional : 17
*/

  DECLARE @MainResArrival DATETIME
  DECLARE @MainResDeparture DATETIME
  DECLARE @RoomArrivalDateTime DATETIME
  DECLARE @RoomDepartureDateTime DATETIME
  DECLARE @RoomAdults INT
  DECLARE @RoomChildren1 INT
  DECLARE @RoomChildren2 INT
  DECLARE @CurrentDate DATETIME

  DECLARE @RoomNumber INT 
  DECLARE @RoomTypeNumber INT
  DECLARE @Identity INT
  DECLARE @ScreenRoomNumber VARCHAR (3)
  DECLARE @WareNumber INT
  DECLARE @Pieces INT
  DECLARE @Time VARCHAR(5)
  DECLARE @PK_Department INT
  DECLARE @ExDepartment  INT
  DECLARE @WareText VARCHAR (80)
  DECLARE @Price MONEY
  DECLARE @NewPrice MONEY
  DECLARE @TotalPrice MONEY
  DECLARE @AddonPriceAdults MONEY
  DECLARE @AddonPriceChildren1 MONEY
  DECLARE @AddonPriceChildren2 MONEY
  DECLARE @CollectArrival BIT
  DECLARE @CollectDeparture BIT
  DECLARE @NumberOfDays INT
  DECLARE @Pax INT
  DECLARE @DoContinue BIT


  DECLARE @RecurringPatternID INT
  DECLARE @Required BIT

  SELECT @EXDepartment = PK_Department FROM Departments WITH (NOLOCK) WHERE AutoCharge = 1

  SELECT @RoomNumber = ISNULL(RoomNumber, 0), @RoomTypeNumber = ISNULL(BookedTypeNumber, 0), @RoomAdults = ISNULL(Adults, 0), @RoomChildren1 = ISNULL(Children1, 0), @RoomChildren2 = ISNULL(Children2, 0),  @RoomArrivalDateTime = CONVERT(DateTime, ArrivalDate, 112) + CONVERT(DateTime, ArrivalTime, 108), @RoomDepartureDateTime = CONVERT(DateTime, DepartureDate, 112) + CONVERT(DateTime, DepartureTime, 108)
  FROM ReservationRoomLine WITH (NOLOCK) INNER JOIN ReservationRoomLineXTab WITH (NOLOCK) ON ReservationRoomLine.LineNumber = ReservationRoomLineXTab.LineNumber
  AND ReservationRoomLineXTab.ReferenceNumber = @AReferenceNumber AND  ReservationRoomLineXTab.GuestNumber = 0

  SELECT @ScreenRoomNumber = CONVERT(VARCHAR(3), Number) FROM Rooms WITH (NOLOCK) WHERE RoomNumber = @RoomNumber

  SELECT @MainResArrival = CONVERT(DATETIME, ArrivalDate, 112), @MainResDeparture = CONVERT(DATETIME, DepartureDate, 112)  FROM Reservations WITH (NOLOCK) WHERE ReferenceNumber = @AReferenceNumber AND GuestNumber = 0

  SET @CurrentDate = @MainResArrival

  WHILE @CurrentDate <=@MainResDeparture
  BEGIN
      DECLARE UpdateCur CURSOR LOCAL FAST_FORWARD FOR
      SELECT DISTINCT RecurringPatternWareXTab.FK_RecurringPatternID, RecurringPatternWareXTab.FK_WareNumber FROM RecurringPatternSpecified WITH (NOLOCK) 
      INNER JOIN RecurringPatternWareXTab WITH (NOLOCK) ON RecurringPatternWareXTab.FK_RecurringPatternID = RecurringPatternSpecified.FK_RecurringPatternID
      INNER JOIN RecurringRule WITH (NOLOCK) ON RecurringPatternSpecified.FK_RecurringPatternID = RecurringRule.FK_RecurringPatternID
      LEFT OUTER JOIN WareRoomXTab WITH (NOLOCK) ON WareRoomXTab.FK_WareNumber = RecurringPatternWareXTab.FK_WareNumber
      LEFT OUTER JOIN WareRoomTypeXTab WITH (NOLOCK) ON WareRoomTypeXTab.FK_WareNumber = RecurringPatternWareXTab.FK_WareNumber
      WHERE
      (
       (@RoomNumber > 0 AND (ISNULL(WareRoomXTab.FK_WareNumber,0) = 0 OR  WareRoomXTab.FK_RoomNumber  = @RoomNumber))
       OR (@RoomNumber = 0 AND (ISNULL(WareRoomTypeXTab.FK_WareNumber,0) = 0 OR WareRoomTypeXTab.FK_RoomTypeNumber = @RoomTypeNumber))
       )
     AND
     (
       (@CurrentDate = @MainResArrival AND RecurringPatternSpecified.StartDateTime <= @MainResArrival AND RecurringPatternSpecified.EndDateTime >= @MainResArrival AND RecurringRule.RuleType = 5)     --Arrival Day
       OR (@CurrentDate = @MainResDeparture AND RecurringPatternSpecified.StartDateTime <= @MainResDeparture AND RecurringPatternSpecified.EndDateTime >= @MainResDeparture AND RecurringRule.RuleType = 7) -- Departure Day
       OR (RecurringPatternSpecified.StartDateTime <= @CurrentDate AND RecurringPatternSpecified.EndDateTime >= @CurrentDate AND RecurringRule.RuleType = 6) --Stay Day
     )

    OPEN UpdateCur
  
    FETCH NEXT FROM UpdateCur INTO @RecurringPatternID,@WareNumber
 
    WHILE(@@Fetch_status=0)
    BEGIN
      IF @CurrentDate = @MainResArrival
        SET @Time = CONVERT(VARCHAR(5), @RoomArrivalDateTime, 108)        
      ELSE IF @CurrentDate = @MainResDeparture
        SET @Time = CONVERT(VARCHAR(5), @RoomDepartureDateTime, 108)
     ELSE
      SET @Time = '12:00' 
 
      SET @AddonPriceAdults = 0
      SET @AddonPriceChildren1 = 0
      SET @AddonPriceChildren2 = 0
      SET @NewPrice = 0
      SET @Pax = 0
      SET @CollectArrival = 0
      SET @CollectDeparture = 0
      SET @DoContinue = 1
      
      IF EXISTS(SELECT * FROM RecurringRule WITH (NOLOCK) WHERE FK_RecurringPatternID = @RecurringPatternID AND RuleType = 8)
        SET @CollectArrival = 1
      ELSE
        SET @CollectArrival = 0

      IF EXISTS(SELECT * FROM RecurringRule WITH (NOLOCK) WHERE FK_RecurringPatternID = @RecurringPatternID AND RuleType = 9)
        SET @CollectDeparture = 1
      ELSE
        SET @CollectDeparture = 0

      IF NOT EXISTS(SELECT WareNumber FROM TArrangementLinesView WITH (NOLOCK) WHERE ReferenceNumber = @AReferenceNumber AND TArrangementLinesView.Date  = CONVERT(VARCHAR(8),@CurrentDate, 112) AND WareNumber = @WareNumber) 
     AND DBO.RecurringRuleValidate(@RecurringPatternID, 10, @RoomAdults) = 1 AND  DBO.RecurringRuleValidate(@RecurringPatternID, 11, @RoomChildren1) = 1 AND  DBO.RecurringRuleValidate(@RecurringPatternID, 12, @RoomChildren2) = 1
--        AND  DBO.RecurringRuleValidate(@RecurringPatternID, 13, @RoomAdults + @RoomChildren1 + @RoomChildren2) = 1
      AND (@CollectArrival = 0 OR  (@CollectArrival = 1 AND @CurrentDate = @MainResArrival))
      AND (@CollectDeparture = 0 OR  (@CollectDeparture = 1 AND @CurrentDate = @MainResDeparture))
      BEGIN
        SELECT @WareText = WareName, @Price = ISNULL(Price, 0) FROM Ware WITH (NOLOCK) WHERE WareNumber = @WareNumber

        SELECT @NewPrice = CONVERT(MONEY, RuleValue) FROM RecurringRule WITH (NOLOCK) WHERE FK_RecurringPatternID = @RecurringPatternID AND RuleType = 1
        SET @Pieces = 1

        IF ISNULL(@NewPrice, 0) > 0
          SET @Price = @NewPrice

        -- The Addons...
        SELECT @Pax = ISNULL(RuleValue, 0) FROM RecurringRule WITH (NOLOCK) WHERE FK_RecurringPatternID = @RecurringPatternID AND RuleType = 13  AND RuleCompareType = 1
        IF @Pax > 0 
        BEGIN
            IF @Pax < (@RoomAdults /*+ @RoomChildren1*/ + @RoomChildren2) -- Children1 does not have to pay....
            BEGIN
              SELECT @AddonPriceAdults = ISNULL(CONVERT(MONEY, RuleValue), 0) FROM RecurringRule WITH (NOLOCK) WHERE FK_RecurringPatternID = @RecurringPatternID AND RuleType = 14
              --SET @AddonPriceAdults = @AddonPriceAdults * (@RoomAdults + @RoomChildren1 + @RoomChildren2 - @Pax)
              IF @AddonPriceAdults > 0
              BEGIN 
                SET @Pieces =  @RoomAdults + /*@RoomChildren1 +*/ @RoomChildren2 - @Pax --Children1 does not have to pay....
                SET @Price = @AddonPriceAdults
              END   
            END
            ELSE
              SET @DoContinue = 0
             
        END

        IF EXISTS(SELECT * FROM RecurringRule WITH (NOLOCK) WHERE FK_RecurringPatternID = @RecurringPatternID AND RuleType = 17) -- Dont take the optional wares
          SET @DoContinue = 0

        IF @DoContinue = 1
        BEGIN
          IF EXISTS(SELECT * FROM RecurringRule WITH (NOLOCK) WHERE FK_RecurringPatternID = @RecurringPatternID AND RuleType = 2)
            SET @Required = 1
          ELSE
            SET @Required = 0

          IF EXISTS(SELECT * FROM RecurringRule WITH (NOLOCK) WHERE FK_RecurringPatternID = @RecurringPatternID AND RuleType = 6) AND ((@CollectArrival = 1 AND @CurrentDate = @MainResArrival) OR (@CollectDeparture = 1 AND @CurrentDate = @MainResDeparture) )
          BEGIN
            SELECT @NumberOfDays = DATEDIFF(DAY, @MainResArrival, @MainResDeparture) + 1
            SET @Price = @Price * @NumberOfDays
          END

          IF EXISTS(SELECT * FROM RecurringRule WITH (NOLOCK) WHERE FK_RecurringPatternID = @RecurringPatternID AND RuleType = 20) -- multiply with pax  AV 2009 0106
            SET @Pieces = @RoomAdults + @RoomChildren1 + @RoomChildren2
    
          SET @TotalPrice = @Price * @Pieces
          INSERT INTO ArrangementLines (Date,Time, Price, /*TotalPrice,*/ Pieces, WareText, WareNumber, PlaceText, FK_ExDepartment, ReadOnly) VALUES  (CONVERT(CHAR(8),@CurrentDate, 112 ),@Time, @Price, /*@TotalPrice,*/ @Pieces,@WareText, @WareNumber, @ScreenRoomNumber, @EXDepartment, @Required)  
          SELECT @Identity = @@identity  FROM ArrangementLines WITH (NOLOCK)
          INSERT INTO ArrangementReservationsXtab VALUES (@Identity,@AReferencenumber, 0, 0)

          DECLARE DepCur Cursor Forward_Only For
          SELECT DISTINCT FK_Department FROM WareDepartmentsXTab  WITH (NOLOCK)
          WHERE WareNumber = @WareNumber AND Type = 2
          OPEN DepCur
          FETCH NEXT FROM DepCur INTO @PK_Department
          WHILE  (@@FETCH_STATUS <> -1)
          BEGIN
             IF NOT EXISTS (SELECT * FROM ArrangementLineDepartmentXTab WITH (NOLOCK) WHERE FK_ArrangementLineNumber = @Identity AND FK_DepartmentNumber = @PK_Department)
               INSERT INTO ArrangementLineDepartmentXTab (FK_ArrangementLineNumber, FK_DepartmentNumber) VALUES (@Identity, @PK_Department)
             FETCH NEXT FROM DepCur INTO @PK_Department
          END
          CLOSE DepCur
          DEALLOCATE DepCur
        END

      END 

      FETCH NEXT FROM UpdateCur INTO @RecurringPatternID, @WareNumber
    END

    SET @CurrentDate = @CurrentDate + 1 
    CLOSE UpdateCur

    DEALLOCATE UpdateCur
  END

