CREATE PROCEDURE [dbo].[OfficeHotelManagerReservation] @AStartDate CHAR(8), @AEndDate CHAR(8), @AHotelNumberStr VARCHAR(100), @AGroupBySegment BIT, @ASegmentCSV VARCHAR(500) AS
BEGIN
/*
  Create by Kent Johansen December 2008
  Changes:
    KJ 20190118: Ext: Segment added
    KJ 20100728: Bug: Fix for wrong pax and pieces

*/

  SET NOCOUNT ON

  DECLARE @ArrivalDate VARCHAR(8)
  DECLARE @DepartureDate VARCHAR(8)
  DECLARE @NumberOfRooms INT
  DECLARE @Pax INT
  DECLARE @rclLineNumber INT
  DECLARE @ConferenceRoomNumber INT

  DECLARE @LastrclLineNumber INT
  DECLARE @CurMonthNumber INT

  DECLARE @ArrivalDateTime DATETIME
  DECLARE @DepartureDateTime DATETIME

  DECLARE @Idx INT

  DECLARE @SegmentNumber INT

  SET @LastrclLineNumber = -1

  CREATE TABLE #OfficeManagerReservations
  (
    rclLineNumber INT,
    MonthNumber INT,
    SegmentNumber INT,
    Pax INT,
    Pieces INT,
    ConferenceRoomNumber INT
  )
  
  DECLARE db_cur CURSOR FOR
    SELECT rcl.ArrivalDate, rcl.DepartureDate, rcl.NumberOfRooms, ISNULL(rcl.Adults, 0)+ISNULL(rcl.Children1,0)+ISNULL(rcl.Children2, 0),
      rcl.ReservationConfLineNumber, rcl.ConferenceRoomNumber, Reservations.MarketSegment
    FROM ReservationConferenceLine rcl WITH (NOLOCK)
    INNER JOIN Reservations WITH (NOLOCK) ON rcl.ReferenceNumber = Reservations.ReferenceNumber
      AND ISNULL(rcl.GuestNo, 0) = Reservations.GuestNumber
    LEFT OUTER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = rcl.ReferenceNumber
    WHERE ISNULL(Tablebooking, 0) = 0
    AND Reservations.Status < 7
    AND Reservations.ReferenceNumber > 0
    AND ((@AHotelNumberStr = '') OR (HotelNumber IN (SELECT Value FROM DBO.SplitNumberList(@AHotelNumberStr))))
    AND rcl.ArrivalDate <= @AEndDate
    AND rcl.DepartureDate >= @AStartDate
    AND (@ASegmentCSV = '' OR Reservations.MarketSegment IN (SELECT Value FROM DBO.SplitNumberList(@ASegmentCSV)))
    ORDER BY rcl.ReservationConfLineNumber
  OPEN db_cur

  FETCH NEXT FROM db_cur INTO @ArrivalDate, @DepartureDate, @NumberOfRooms, @Pax, @rclLineNumber, @ConferenceRoomNumber, @SegmentNumber

  WHILE @@FETCH_STATUS = 0
  BEGIN
    IF @NumberOfRooms > 0 OR @Pax > 0
    BEGIN
      SET @Idx = 0
      SET @ArrivalDateTime = CONVERT(DATETIME, @ArrivalDate, 112)
      SET @DepartureDateTime = CONVERT(DATETIME, @DepartureDate, 112)

      WHILE @ArrivalDateTime + @Idx <= @DepartureDateTime 
        AND (@ArrivalDateTime+@Idx >= CONVERT(DATETIME, @AStartDate, 112))
        AND (@ArrivalDateTime+@Idx <= CONVERT(DATETIME, @AEndDate, 112))
      BEGIN
        SET @CurMonthNumber = MONTH(@ArrivalDateTime+@Idx)
        INSERT INTO #OfficeManagerReservations VALUES (@rclLineNumber, @CurMonthNumber, @SegmentNumber, @Pax, @NumberOfRooms, @ConferenceRoomNumber)
        SET @Idx = @Idx + 1
      END
      FETCH NEXT FROM db_cur INTO @ArrivalDate, @DepartureDate, @NumberOfRooms, @Pax, @rclLineNumber, @ConferenceRoomNumber, @SegmentNumber
    END
  END

  CLOSE db_cur
  DEALLOCATE db_cur

  IF @AGroupBySegment = 1
  BEGIN
    SELECT MonthNumber, ConferenceRoomNumber, SUM(Pax) AS Pax, SUM(Pieces) AS Pieces, SegmentNumber
    FROM #OfficeManagerReservations 
    GROUP BY MonthNumber, ConferenceRoomNumber, SegmentNumber
    ORDER BY MonthNumber
  END
  ELSE
  BEGIN
    SELECT MonthNumber, ConferenceRoomNumber, SUM(Pax) AS Pax, SUM(Pieces) AS Pieces, -1 AS SegmentNumber
    FROM #OfficeManagerReservations 
    GROUP BY MonthNumber, ConferenceRoomNumber
    ORDER BY MonthNumber
  END

  DROP TABLE #OfficeManagerReservations
END