CREATE PROCEDURE [dbo].[ReCreateArrangement] @refno int, @AHotelNumber int = -1
AS
  SET NOCOUNT ON

  --sidst rettet 2016 0518 Genskab altid arrangementlinier for alle hoteller på en reservation
  --sidst rettet 2010 0315 DELETE fra ArrangementReservationsXTab og ArrangementRoomXTab flyttet.
  --sidst rettet 2007 0828 added new param: AHotelnumber
  --sidst retttet 2006 0313 HotelNumber bruges nu.
  --Sidst rettet 2005 0831 Brug af curser og kun sletning igennem nøgle felterne i arrangementtabeller: For at være sikker på at undgå tablescans / locktimout
  --sidste rettet 01022005 parametre til child2
  --sidste rettet 110603 delete from arrangementroomxtab.... og hvis en hall linie slutter før døgnet startet, så træk 1 fra departuredate..
  --sidst rettet 220503 Rutinen virker 100 % også med plusprodukt

  --If routine is called with -10  for parameter @refno, all reservations in system with statuses < Inhouse will be affected. Else only the given referencenumber will be affected

  DECLARE @ProductNumber int
  DECLARE @PacketNumber int
  DECLARE @WhichDay int
  DECLARE @ArrivalDate char(8)
  DECLARE @DepartureDate char(8)
  DECLARE @ArrDate datetime
  DECLARE @Depdate datetime
  DECLARE @ArrTime char(5)
  DECLARE @DepTime char(5)
  DECLARE @Adults int
  DECLARE @Children1 int
  DECLARE @Children2 int
  DECLARE @NumberOfRooms int

  DECLARE @RoomTypeNumber int
  DECLARE @RoomNumber int
  DECLARE @GuestNumber int

  DECLARE @LineNumber int
  DECLARE @NameListCreated bit

  DECLARE @FromProduct bit

  DECLARE @Day2 int
  DECLARE @Day3 int
  DECLARE @Day4 int

  DECLARE @ProductNumber2 int
  DECLARE @ProductNumber3 int
  DECLARE @ProductNumber4 int

  DECLARE @Loopcounter int
  DECLARE @DoContinue bit

  DECLARE @ArrLineNumber int
  DECLARE @ArrRoomLineNumber int
  DECLARE @ArrPlusProductLineNumber int
  DECLARE @ArrPacketItemNumber int
  DECLARE @ArrReferenceNumber int
  DECLARE @ArrGuestNumber int
  DECLARE @Arr24HoursStarts char(5)
  DECLARE @HotelNumber int
  DECLARE @DominoEnabled bit

  SELECT
    @Arr24HoursStarts = KeyValue
  FROM HotelSetUp WITH (NOLOCK)
  WHERE SearchKey = 'Arr24HoursStarts'

  SELECT
    @DominoEnabled = CONVERT(smallint, KeyValue)
  FROM HotelSetup WITH (NOLOCK)
  WHERE SearchKey = 'CentralBookingType'
  IF ISNULL(@DominoEnabled, 0) = 0
    SET @DominoEnabled = 0


  DECLARE @Counter int
  SET @Counter = 0

  IF @Refno = -10
  BEGIN
    IF @AHotelNumber > -1
      AND @DominoEnabled = 1
    BEGIN
      DECLARE ResCur CURSOR FORWARD_ONLY FOR
      SELECT DISTINCT
        reservations.referencenumber
      FROM reservations WITH (NOLOCK)
      INNER JOIN resevationmain WITH (NOLOCK)
        ON reservationmain.referencenumber = reservations.referencenumber
      WHERE reservations.guestnumber = 0
      AND reservations.Status < 5
      AND reservations.ReferenceNumber > 0
      AND reservationmain.hotelnumber = @AHotelNumber
    END
    ELSE
    BEGIN
      DECLARE ResCur CURSOR FORWARD_ONLY FOR
      SELECT DISTINCT
        referencenumber
      FROM reservations WITH (NOLOCK)
      WHERE guestnumber = 0
      AND Status < 5
      AND ReferenceNumber > 0
    END
  END
  ELSE
  BEGIN
    DECLARE ResCur CURSOR FORWARD_ONLY FOR
    SELECT DISTINCT
      referencenumber
    FROM reservations WITH (NOLOCK)
    WHERE guestnumber = 0
    AND referencenumber = @refno
  END

  OPEN ResCur
  FETCH NEXT FROM ResCur INTO @Refno
  WHILE (@@FETCH_STATUS <> -1)
  BEGIN
    SET @HotelNumber = -1
	/* 20160518/RN
    IF @DominoEnabled = 1
      SELECT
        @HotelNumber = HotelNumber
      FROM ReservationMain WITH (NOLOCK)
      WHERE ReferenceNumber = @RefNo
    ELSE
      SET @HotelNumber = -1
	*/

    PRINT 'nu kommer ref no: ' + CONVERT(char, @refno)
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    DECLARE ArrCur CURSOR FORWARD_ONLY FOR
    SELECT
      ArrangementLineNumber
    FROM Arrangementlines WITH (NOLOCK)
    WHERE Arrangementlinenumber IN (SELECT
      ArrangementLineNumber
    FROM ArrangementreservationsXtab WITH (NOLOCK)
    WHERE ReferenceNumber = @Refno)
    AND ArrangementLinenumber IN (SELECT
      ArrangementLineNumber
    FROM ArrangementRoomXtab WITH (NOLOCK))
    OPEN ArrCur
    FETCH NEXT FROM ArrCur INTO @ArrLineNumber
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
      DELETE FROM ArrangementReservationsXTab
      WHERE ArrangementLineNumber = @ArrLineNumber
      DELETE FROM ArrangementRoomXTab
      WHERE ArrangementLineNumber = @ArrLineNumber
      DELETE FROM ArrangementLines
      WHERE arrangementLineNumber = @ArrLineNumber
      FETCH NEXT FROM ArrCur INTO @ArrLineNumber
    END

    CLOSE ArrCur
    DEALLOCATE ArrCur

    /* 20100315/RN: CR013012, Removed:
    Declare ArrCur Cursor Forward_Only For
    SELECT ArrangementLineNumber, ReferenceNumber, GuestNumber, PlusProductLineNumber FROM ArrangementReservationsXtab (NOLOCK) where referencenumber=@Refno and arrangementlinenumber in (select arrangementLineNumber from ArrangementRoomXtab(nolock))
    Open ArrCur
    Fetch Next From ArrCur Into @ArrLineNumber, @ArrReferenceNumber, @ArrGuestNumber, @ArrPlusProductLineNumber
    While  (@@FETCH_STATUS <> -1)
    Begin
      Delete from ArrangementReservationsXtab where arrangementLineNumber = @ArrLineNumber and ReferenceNumber = @ArrReferencenumber and GuestNumber = @ArrGuestNumber 
      and PlusProductLineNumber = @ArrPlusProductLineNumber
   
     Fetch Next From ArrCur Into @ArrLineNumber, @ArrReferenceNumber, @ArrGuestNumber, @ArrPlusProductLineNumber
    end
    CLOSE ArrCur
    DEALLOCATE ArrCur
  
    Declare ArrCur Cursor Forward_Only For
    SELECT ArrangementLineNumber, LineNumber, PlusProductLineNumber, PacketItemNumber  FROM ArrangementRoomXtab (NOLOCK) where arrangementlinenumber in (select arrangementlinenumber from arrangementreservationsxtab(nolock) where referencenumber=@refno)
    Open ArrCur
    Fetch Next From ArrCur Into @ArrLineNumber, @ArrRoomLineNumber, @ArrPlusProductLineNumber, @ArrPacketItemNumber
    While  (@@FETCH_STATUS <> -1)
    Begin
      Delete from ArrangementRoomXtab where arrangementLineNumber = @ArrLineNumber and LineNumber = @ArrRoomLineNumber and PlusProductLineNumber = @ArrPlusProductLineNumber and PacketItemNumber = @ArrPacketItemNumber 
     Fetch Next From ArrCur Into @ArrLineNumber, @ArrRoomLineNumber, @ArrPlusProductLineNumber, @ArrPacketItemNumber
    end
    CLOSE ArrCur
    DEALLOCATE ArrCur
    */


    SET @counter = @counter + 1

    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    DECLARE ConflineCur CURSOR FORWARD_ONLY FOR
    SELECT DISTINCT
      ReservationConfLinenumber
    FROM ReservationConferenceline WITH (NOLOCK)
    WHERE referencenumber = @refno
    OPEN ConflineCur
    FETCH NEXT FROM ConflineCur INTO @linenumber
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN

      DECLARE ArrCur CURSOR FORWARD_ONLY FOR
      SELECT
        ArrangementLineNumber,
        LineNumber,
        PlusProductLineNumber,
        PacketItemNumber
      FROM ArrangementRoomXtab WITH (NOLOCK)
      WHERE linenumber = @LineNumber
      OPEN ArrCur
      FETCH NEXT FROM ArrCur INTO @ArrLineNumber, @ArrRoomLineNumber, @ArrPlusProductLineNumber, @ArrPacketItemNumber
      WHILE (@@FETCH_STATUS <> -1)
      BEGIN
        DELETE FROM ArrangementRoomXtab
        WHERE arrangementLineNumber = @ArrLineNumber
          AND LineNumber = @ArrRoomLineNumber
          AND PlusProductLineNumber = @ArrPlusProductLineNumber
          AND PacketItemNumber = @ArrPacketItemNumber
        FETCH NEXT FROM ArrCur INTO @ArrLineNumber, @ArrRoomLineNumber, @ArrPlusProductLineNumber, @ArrPacketItemNumber
      END
      CLOSE ArrCur
      DEALLOCATE ArrCur

      SELECT DISTINCT
        @ProductNumber = ProductNumber,
        @PacketNumber = PackageNumber,
        @arrivaldate = arrivaldate,
        @arrtime = arrivaltime,
        @departuredate = departuredate,
        @deptime = departuretime,
        @Adults = Adults,
        @Children1 = Children1,
        @Children2 = Children2,
        @NumberOfRooms = NumberOfRooms
      FROM reservationConferenceline WITH (NOLOCK)
      WHERE ReservationConflineNumber = @Linenumber
      IF ISNULL(@ProductNumber, 0) = 0 -- is null
        SET @productNumber = 0
      IF @ProductNumber = 0
        SET @FromProduct = 0
      ELSE
        SET @FromProduct = 1
      IF ISNULL(@NumberOfRooms, 0) = 0 -- is null
        SET @NumberOfRooms = 0
      IF ISNULL(@Adults, 0) = 0 -- is null
        SET @Adults = 0
      IF ISNULL(@Children1, 0) = 0 -- is null
        SET @Children1 = 0
      IF ISNULL(@Children2, 0) = 0 -- is null
        SET @Children2 = 0

      IF @Arrtime < @Arr24HoursStarts
        SET @arrdate = CONVERT(datetime, @Arrivaldate) - 1
      ELSE
        SET @arrdate = CONVERT(datetime, @Arrivaldate)

      IF @Deptime < @Arr24HoursStarts
        SET @depdate = CONVERT(datetime, @DepartureDate) - 1
      ELSE
        SET @depdate = CONVERT(datetime, @DepartureDate)

      EXEC GenerateArrangement -3,
                               @ProductNumber,
                               -3,
                               @PacketNumber,
                               @ArrDate,
                               @Arrdate,
                               @DepDate,
                               @DepDate,
                               @DepDate,
                               @Depdate,
                               @Refno,
                               0,
                               @Adults,
                               @Adults,
                               @Children1,
                               @Children1,
                               @Children2,
                               @Children2,
                               0,
                               @FromProduct,
                               @LineNumber,
                               @NumberofRooms,
                               @NumberofRooms,
                               0,
                               @HotelNumber

      FETCH NEXT FROM ConflineCur INTO @linenumber
    END
    CLOSE ConfLineCur
    DEALLOCATE ConfLineCur

    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    DECLARE RoomLineCur CURSOR FORWARD_ONLY FOR
    SELECT DISTINCT
      Linenumber
    FROM ReservationRoomLineXtab WITH (NOLOCK)
    WHERE referencenumber = @refno
    OPEN RoomLineCur
    FETCH NEXT FROM RoomLineCur INTO @linenumber
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN

      DECLARE ArrCur CURSOR FORWARD_ONLY FOR
      SELECT
        ArrangementLineNumber,
        LineNumber,
        PlusProductLineNumber,
        PacketItemNumber
      FROM ArrangementRoomXtab WITH (NOLOCK)
      WHERE linenumber = @LineNumber
      OPEN ArrCur
      FETCH NEXT FROM ArrCur INTO @ArrLineNumber, @ArrRoomLineNumber, @ArrPlusProductLineNumber, @ArrPacketItemNumber
      WHILE (@@FETCH_STATUS <> -1)
      BEGIN
        DELETE FROM ArrangementRoomXtab
        WHERE arrangementLineNumber = @ArrLineNumber
          AND LineNumber = @ArrRoomLineNumber
          AND PlusProductLineNumber = @ArrPlusProductLineNumber
          AND PacketItemNumber = @ArrPacketItemNumber
        FETCH NEXT FROM ArrCur INTO @ArrLineNumber, @ArrRoomLineNumber, @ArrPlusProductLineNumber, @ArrPacketItemNumber
      END
      CLOSE ArrCur
      DEALLOCATE ArrCur

      IF EXISTS (SELECT
          *
        FROM reservationroomlinextab WITH (NOLOCK)
        WHERE linenumber = @LineNumber
        AND guestnumber = 0)
        SET @NameListCreated = 0
      ELSE
        SET @NameListCreated = 1
      SELECT
        @NumberOfRooms = Numberofrooms,
        @roomNumber = RoomNumber,
        @RoomTypeNumber = BookedTypeNumber
      FROM reservationroomline WITH (NOLOCK)
      WHERE linenumber = @LineNumber
      IF ISNULL(@RoomNumber, 0) > 0 -- is not null
        SELECT
          @RoomTypeNumber = RoomTypeNumber
        FROM rooms WITH (NOLOCK)
        WHERE roomnumber = @roomNumber --Mabye we have allocated another roomtype than the specified
      IF ISNULL(@RoomTypeNumber, 0) = 0 -- is null
        PRINT 'Houston we got a problem: RoomTypeNumber not found'

      IF @NameListCreated = 1
      BEGIN
        SET @NumberOfRooms = 1
        DECLARE RoomLineCur1 CURSOR FORWARD_ONLY FOR
        SELECT DISTINCT
          GuestNumber,
          ProductNumber,
          Productnumber2,
          productnumber3,
          productnumber4,
          day2,
          day3,
          day4,
          arrivaldate,
          departuredate,
          Adults,
          Children1,
          Children2
        FROM reservations WITH (NOLOCK)
        WHERE referencenumber = @Refno
        AND referencenumber > 0
        AND guestnumber IN (SELECT
          guestnumber
        FROM reservationroomlinextab
        WHERE referencenumber = @refno
        AND linenumber = @LineNumber)
        AND status < 7
        OPEN RoomLineCur1
        FETCH NEXT FROM RoomLineCur1 INTO @GuestNumber, @ProductNumber, @productnumber2, @productnumber3, @productnumber4, @day2, @day3, @day4, @arrivaldate, @departuredate, @Adults, @Children1, @Children2
        WHILE (@@FETCH_STATUS <> -1)
        BEGIN
          PRINT ' Guest: ' + CONVERT(char, @GuestNumber)
          SET @LoopCounter = 0
          SET @DoContinue = 1
          WHILE @LoopCounter <= 4
            AND @DoContinue = 1
          BEGIN
            SET @Loopcounter = @LoopCounter + 1

            IF @LoopCounter = 1
            BEGIN
              IF @day2 = 0
                OR ISNULL(@day2, 0) = 0 -- is null --happens if no plusproduct
              BEGIN
                SET @arrdate = CONVERT(datetime, @Arrivaldate)
                SET @depdate = CONVERT(datetime, @departuredate)
                SET @DoContinue = 0
              END
              ELSE
              BEGIN
                SET @arrdate = CONVERT(datetime, @Arrivaldate)
                SET @depdate = CONVERT(datetime, @Arrivaldate) + @day2 - 1
              END
            END
            ELSE
            IF @LoopCounter = 2
            BEGIN
              SET @productnumber = @productnumber2
              IF @day3 = 0
                OR ISNULL(@day3, 0) = 0 -- is null
              BEGIN
                SET @arrdate = CONVERT(datetime, @Arrivaldate) + @day2 - 1
                SET @depdate = CONVERT(datetime, @departuredate)
                SET @DoContinue = 0
              END
              ELSE
              BEGIN
                SET @arrdate = CONVERT(datetime, @Arrivaldate) + @day2 - 1
                SET @depdate = CONVERT(datetime, @Arrivaldate) + @day3 - 1
              END
            END
            ELSE
            IF @LoopCounter = 3
            BEGIN
              SET @productnumber = @productnumber3
              IF @day4 = 0
                OR ISNULL(@day4, 0) = 0 -- is null
              BEGIN
                SET @arrdate = CONVERT(datetime, @Arrivaldate) + @day3 - 1
                SET @depdate = CONVERT(datetime, @departuredate)
                SET @DoContinue = 0
              END
              ELSE
              BEGIN
                SET @arrdate = CONVERT(datetime, @Arrivaldate) + @day3 - 1
                SET @depdate = CONVERT(datetime, @Arrivaldate) + @day4 - 1
              END
            END
            ELSE
            IF @LoopCounter = 4
            BEGIN
              SET @productnumber = @productnumber4
              SET @arrdate = CONVERT(datetime, @Arrivaldate) + @day4 - 1
              SET @depdate = CONVERT(datetime, @Departuredate)
              SET @DoContinue = 0
            END

            IF ISNULL(@Adults, 0) = 0 -- is null
              SET @Adults = 0
            IF ISNULL(@Children1, 0) = 0 -- is null
              SET @Children1 = 0
            IF ISNULL(@Children2, 0) = 0 -- is null
              SET @Children2 = 0
            IF ISNULL(@ProductNumber, 0) = 0 -- is null
              SET @productNumber = 0
            EXEC GenerateArrangementFromNameList -3,
                                                 @ProductNumber,
                                                 @Loopcounter,
                                                 @arrDate,
                                                 @ArrDate,
                                                 @DepDate,
                                                 @Depdate,
                                                 @depdate,
                                                 @Depdate,
                                                 @Refno,
                                                 @GuestNumber,
                                                 @adults,
                                                 @adults,
                                                 @Children1,
                                                 @Children1,
                                                 @Children2,
                                                 @Children2,
                                                 @NumberOfRooms,
                                                 @NumberOfRooms,
                                                 0,
                                                 0,
                                                 1,
                                                 @Linenumber,
                                                 @RoomTypeNumber,
                                                 0,
                                                 @HotelNumber


          END



          FETCH NEXT FROM RoomLineCur1 INTO @GuestNumber, @ProductNumber, @productnumber2, @productnumber3, @productnumber4, @day2, @day3, @day4, @arrivaldate, @departuredate, @Adults, @Children1, @Children2
        END
        CLOSE RoomLineCur1
        DEALLOCATE RoomLineCur1
      END
      ELSE
      BEGIN

        SELECT DISTINCT
          @ProductNumber = ProductNumber,
          @ProductNumber2 = ProductNumber2,
          @ProductNumber3 = ProductNumber3,
          @ProductNumber4 = ProductNumber4,
          @day2 = day2,
          @day3 = day3,
          @day4 = day4,
          @arrivaldate = arrivaldate,
          @departuredate = departuredate,
          @Adults = Adults,
          @Children1 = Children1,
          @Children2 = Children2
        FROM reservationroomline WITH (NOLOCK)
        WHERE lineNumber = @Linenumber

        SET @LoopCounter = 0
        SET @DoContinue = 1

        WHILE @LoopCounter <= 4
          AND @DoContinue = 1
        BEGIN
          SET @Loopcounter = @LoopCounter + 1
          IF @LoopCounter = 1
          BEGIN
            IF @day2 = 0
              OR ISNULL(@day2, 0) = 0 -- is null --happens if no plusproduct
            BEGIN
              SET @arrdate = CONVERT(datetime, @Arrivaldate)
              SET @depdate = CONVERT(datetime, @departuredate)
              SET @DoContinue = 0
            END
            ELSE
            BEGIN
              SET @arrdate = CONVERT(datetime, @Arrivaldate)
              SET @depdate = CONVERT(datetime, @Arrivaldate) + @day2 - 1
            END
          END
          ELSE
          IF @LoopCounter = 2
          BEGIN
            SET @productnumber = @productnumber2
            IF @day3 = 0
              OR ISNULL(@day3, 0) = 0 -- is null
            BEGIN
              SET @arrdate = CONVERT(datetime, @Arrivaldate) + @day2 - 1
              SET @depdate = CONVERT(datetime, @departuredate)
              SET @DoContinue = 0
            END
            ELSE
            BEGIN
              SET @arrdate = CONVERT(datetime, @Arrivaldate) + @day2 - 1
              SET @depdate = CONVERT(datetime, @Arrivaldate) + @day3 - 1
            END
          END
          ELSE
          IF @LoopCounter = 3
          BEGIN
            SET @productnumber = @productnumber3
            IF @day4 = 0
              OR ISNULL(@day4, 0) = 0 -- is null
            BEGIN
              SET @arrdate = CONVERT(datetime, @Arrivaldate) + @day3 - 1
              SET @depdate = CONVERT(datetime, @departuredate)
              SET @DoContinue = 0
            END

            ELSE
            BEGIN
              SET @arrdate = CONVERT(datetime, @Arrivaldate) + @day3 - 1
              SET @depdate = CONVERT(datetime, @Arrivaldate) + @day4 - 1
            END
          END
          ELSE
          IF @LoopCounter = 4
          BEGIN
            SET @productnumber = @productnumber4
            SET @arrdate = CONVERT(datetime, @Arrivaldate) + @day4 - 1
            SET @depdate = CONVERT(datetime, @Departuredate)
            SET @DoContinue = 0
          END

          SET @GuestNumber = 0

          IF ISNULL(@Adults, 0) = 0 -- is null
            SET @Adults = 0
          IF ISNULL(@Children1, 0) = 0 -- is null
            SET @Children1 = 0
          IF ISNULL(@Children2, 0) = 0 -- is null
            SET @Children2 = 0
          IF ISNULL(@ProductNumber, 0) = 0 -- is null
            SET @productNumber = 0

          EXEC GenerateArrangementFromNameList -3,
                                               @ProductNumber,
                                               @LoopCounter,
                                               @arrDate,
                                               @ArrDate,
                                               @DepDate,
                                               @Depdate,
                                               @depdate,
                                               @Depdate,
                                               @Refno,
                                               @GuestNumber,
                                               @adults,
                                               @adults,
                                               @Children1,
                                               @Children1,
                                               @Children2,
                                               @Children2,
                                               @NumberOfRooms,
                                               @NumberOfRooms,
                                               0,
                                               0,
                                               0,
                                               @Linenumber,
                                               @RoomTypeNumber,
                                               0,
                                               @HotelNumber
        END

      END

      FETCH NEXT FROM RoomLineCur INTO @LineNumber
    END

    CLOSE roomlinecur
    DEALLOCATE roomlinecur

    FETCH NEXT FROM ResCur INTO @Refno
  END

  CLOSE ResCur
  DEALLOCATE Rescur
  PRINT 'Numbers of Affected referencenumbers is: '
  PRINT @counter