CREATE PROCEDURE [dbo].[GetMiceDataReservations] @ADateFrom char(8), @ADateTo char(8), @AHotelNumber int AS
--, @ARowCount int OUTPUT AS

/*
DECLARE
  @ADateFrom char(8),
  @ADateTo char(8),
  @AHotelNumber int,
  @ARowCount int

SET @ADateFrom = '20060601'
SET @ADateTo = '20060630'
SET @AHotelNumber = 0
*/

-- OF 20100827: ParticipantOverNigthing error fixed. Morningcount, AfternoonCount and EveningCount error fixed.
-- OF 20100826: Morningcount, AfternoonCount and EveningCount did not consider if guest where staying more than one day.
-- OF 20100811: Future reservation that are between the day it was made and the arrival date must be included according to explanation below.

--		En gang om måneden overføres data til Danmarks Statistik. Hvis indberetnings måneden eksempelvis har været maj skal følgende reservationer medtages:
--		•	Alle overnatnings-reservationer hvor afrejse datoen ligger i maj.
--		•	Alle møde-reservationer hvor sidste møde dag var i maj måned.
--		•	Alle fremtidige overnatnings-reservationer der ligger i systemet, og som senest er oprettet den sidste dag i maj.
--		•	Alle fremtidige møde-reservationer der ligger i systemet, og som senest er oprettet den sidste dag i maj.

-- OF 20100810: OverNightingCount always 0. Fixed.
-- OF 20100517: SubID altered to GuestNumber and Guestnumber passed to sp GetMiceReservationData because of to many data returned.
-- OF 20090430: Fix for bad order by in CurMiceReservations cursor. Errors in resolving room, guest and nationalities.
-- OF 20090226: Fix for determine if domino or not.
-- TL 20070117: Fix all the broken stuff (hopefully). Get correct room and guest count as well as nationalities.
-- BJ 20060929: Don't select cancelled reservations. 
-- BJ 20060920: Fixed select of conference lines. Exclude GuestNumber <> 0 and TableBooking <> 0
-- BJ 20060919: Ensure default values
-- BJ 2006xxxx: Implemented

SET NOCOUNT ON

-- Tmp variables
DECLARE
  @TmpRefNo int

SET @TmpRefNo = 0

-- CurMiceReservations
DECLARE
  @ResRefNo int,
  @ResGuestNo int,
  @ResArrivalDate char(8),
  @ResDepartureDate char(8),
  @ResTentative bit,
  @ResSegmentNo int,
  @ResDate char(8),
  @ResSegment int,
  @ResTotalPrice money,
  @ResCountryCode varchar(3)

-- CurMiceReservationLines
DECLARE
  @ResLineRoomCount int,
  @ResLineArrivalDate char(8),
  @ResLineArrivalTime char(5),
  @ResLineDepartureDate char(8),
  @ResLineDepartureTime char(5),
  @ResLineGuestCountRoom int,
  @ResLineGuestCountConference int,
  @ResLineOvernightingCount int,
  @ResLineRecType tinyint,
  @ResLineTurnover money,
  @ResLineMorningCount int,
  @ResLineAfternoonCount int,
  @ResLineEveningCount int,
  @ResLineRecCount int

DECLARE
  @ResGuestArrivalDate char(8),
  @ResGuestDepartureDate char(8),
  @ResGuestCountryCode varchar(3),
  @ResGuestCountryCodeCount int

CREATE TABLE #MiceReservations
(
  ReferenceNumber int,
  SubId int,
  ArrivalDate char(8) COLLATE DATABASE_DEFAULT NOT NULL,
  ArrivalTime char(5) COLLATE DATABASE_DEFAULT NOT NULL,
  DepartureDate char(8) COLLATE DATABASE_DEFAULT NOT NULL,
  DepartureTime char(5) COLLATE DATABASE_DEFAULT NOT NULL,
  ReservationDate char(8) COLLATE DATABASE_DEFAULT NOT NULL,
  SegmentNumber int DEFAULT 0, 
  RoomCount int DEFAULT 0,
  GuestCount int DEFAULT 0,
  RecType tinyint DEFAULT 0, -- 1 = Overnight reservation; 2 = meeting reservation; 3 = Guest information
  OvernightingCount int DEFAULT 0, -- Number of overnighting participants of meeting
  IsTentative bit DEFAULT 0,
  Turnover money DEFAULT 0,
  MorningCount int DEFAULT 0,
  AfternoonCount int DEFAULT 0,
  EveningCount int DEFAULT 0,
  GuestCountryCode varchar(3) DEFAULT '',
  GuestCountryCodeCount int DEFAULT 0
)

DECLARE CurMiceReservations CURSOR READ_ONLY FAST_FORWARD FOR
  SELECT 
    distinct(Reservations.ReferenceNumber),
    Reservations.GuestNumber,
    Reservations.ArrivalDate,
    Reservations.DepartureDate,
    IsNull(ReservationAddress.CountryCode, '') AS CountryCode,
    (CASE WHEN Reservations.Status = 4 THEN 1 ELSE 0 END) AS ReservationTentative,
    (CASE WHEN IsNull(Reservations.PurposeCode, 0) BETWEEN 1 AND 4 THEN Reservations.PurposeCode ELSE 5 END) AS ReservationSegment,
    Convert(char(8), Reservations.ReservationCreateDate, 112) AS ReservationDate,
    (CASE WHEN Reservations.ArrivalDate > @ADateTo THEN Reservations.TotalPrice * 0.8 ELSE 0 END) AS ResTurnover -- BJ 20060330: Can not get Reservation.TotalPrice excl. VAT for future res.
  FROM Reservations WITH (NOLOCK)
  INNER JOIN ReservationMain WITH (NOLOCK) ON
    Reservations.ReferenceNumber = ReservationMain.ReferenceNumber 
    AND ( (@AHotelNumber > -1 AND IsNull(ReservationMain.HotelNumber, 0) = @AHotelNumber)  OR @AHotelNumber = -1)
    --AND IsNull(ReservationMain.HotelNumber, -1) = @AHotelNumber
    AND Reservations.Status < 7 -- rsCancelled
	AND ((@ADateFrom between Convert(char(8), Reservations.ReservationCreateDate, 112) AND Reservations.ArrivalDate) or
		(@ADateTo between Convert(char(8), Reservations.ReservationCreateDate, 112) AND Reservations.DepartureDate))

   -- AND ((Reservations.DepartureDate BETWEEN @ADateFrom AND @ADateTo)
   --   OR ((Reservations.ArrivalDate > @ADateTo) AND (Convert(char(8), FollowUp.Date, 112) BETWEEN @ADateFrom AND @ADateTo)))

  INNER JOIN ReservationAddressXTab WITH (NOLOCK) ON
    ReservationAddressXTab.ReferenceNumber = Reservations.ReferenceNumber AND
    ReservationAddressXTab.GuestNumber = 0
  INNER JOIN ReservationAddress WITH (NOLOCK) ON
    ReservationAddressXTab.AddressNumber = ReservationAddress.AddressNumber AND
    IsNull(ReservationAddress.Link, 0) = 0
  ORDER BY 
    Reservations.ReferenceNumber,
    Reservations.GuestNumber,
    Reservations.DepartureDate, 
    Reservations.ArrivalDate

OPEN CurMiceReservations

FETCH NEXT FROM CurMiceReservations INTO 
  @ResRefNo, 
  @ResGuestNo, 
  @ResArrivalDate, 
  @ResDepartureDate, 
  @ResCountryCode,
  @ResTentative,
  @ResSegment,
  @ResDate,
  @ResTotalPrice

WHILE @@FETCH_STATUS <> -1
BEGIN
  IF @TmpRefNo <> @ResRefNo
  BEGIN
    SET @TmpRefNo = @ResRefNo
  END
    
  DECLARE CurMiceDataRoomLines CURSOR READ_ONLY FAST_FORWARD FOR
    SELECT ArrivalDate, DepartureDate, SUM(GuestCount) AS GuestCount, SUM(RoomCount) AS RoomCount FROM GetMiceReservationData (@ResRefNo, @ResGuestNo, @ADateFrom, @ADateTo)
    GROUP BY ArrivalDate, DepartureDate
  OPEN CurMiceDataRoomLines


  /***********************************************************
     ROOM LINES 
  ***********************************************************/

  FETCH NEXT FROM CurMiceDataRoomLines INTO
    @ResLineArrivalDate,
    @ResLineDepartureDate,
    @ResLineGuestCountRoom,
    @ResLineRoomCount

  WHILE @@FETCH_STATUS <> -1
  BEGIN

    INSERT INTO #MiceReservations (ReferenceNumber, SubId, ArrivalDate, ArrivalTime, DepartureDate, DepartureTime, ReservationDate, SegmentNumber, 
            RoomCount, GuestCount, RecType, IsTentative, Turnover, GuestCountryCode)
    VALUES (@ResRefNo, @ResGuestNo, @ResLineArrivalDate, '', @ResLineDepartureDate, '', @ResDate, @ResSegment, 
            @ResLineRoomCount, @ResLineGuestCountRoom, 1, @ResTentative, @ResTotalPrice, @ResCountryCode)
        
    DECLARE CurMiceDataGuests CURSOR READ_ONLY FAST_FORWARD FOR
      SELECT ArrivalDate, DepartureDate, SUM(GuestCount) AS GuestCount, CountryCode FROM GetMiceReservationData (@ResRefNo, @ResGuestNo, @ADateFrom, @ADateTo)
      GROUP BY ArrivalDate, DepartureDate, CountryCode
      HAVING ArrivalDate = @ResLineArrivalDate AND DepartureDate = @ResLineDepartureDate

    OPEN CurMiceDataGuests
    FETCH NEXT FROM CurMiceDataGuests INTO @ResGuestArrivalDate, @ResGuestDepartureDate, @ResGuestCountryCodeCount, @ResGuestCountryCode
    WHILE @@FETCH_STATUS <> -1 
    BEGIN
  
      IF @ResGuestCountryCodeCount <> 0
      BEGIN
        INSERT INTO #MiceReservations (ReferenceNumber, SubId, ArrivalDate, ArrivalTime, DepartureDate, DepartureTime, ReservationDate, RecType, GuestCountryCode, GuestCountryCodeCount)
        VALUES (@ResRefNo, @ResGuestNo, @ResLineArrivalDate, '', @ResLineDepartureDate, '', @ResDate, 3, @ResGuestCountryCode, @ResGuestCountryCodeCount)
      END

      FETCH NEXT FROM CurMiceDataGuests INTO @ResGuestArrivalDate, @ResGuestDepartureDate, @ResGuestCountryCodeCount, @ResGuestCountryCode
    END

    CLOSE CurMiceDataGuests
    DEALLOCATE CurMiceDataGuests

    FETCH NEXT FROM CurMiceDataRoomLines INTO
      @ResLineArrivalDate,
      @ResLineDepartureDate,
      @ResLineGuestCountRoom,
      @ResLineRoomCount
  END

  CLOSE CurMiceDataRoomLines
  DEALLOCATE CurMiceDataRoomLines


  /***********************************************************
     CONFERENCE LINES
  ***********************************************************/
  if @ResGuestNo = 0
  begin
  DECLARE CurMiceDataConfLines CURSOR READ_ONLY FAST_FORWARD FOR 
    SELECT
      ReservationConferenceLine.ArrivalDate,
      ReservationConferenceLine.ArrivalTime,
      ReservationConferenceLine.DepartureDate,
      ReservationConferenceLine.DepartureTime,
      Sum(IsNull(ReservationConferenceLine.Adults, 0) + IsNull(ReservationConferenceLine.Children1, 0) + IsNull(ReservationConferenceLine.Children2, 0)) AS GuestCount,
      Sum(ReservationConferenceLine.NumberOfRooms) AS NumberOfRooms,
      Sum((CASE WHEN ReservationConferenceLine.ArrivalTime < '12:00' THEN 1 ELSE 0 END) + DATEDIFF (day ,ReservationConferenceLine.ArrivalDate ,ReservationConferenceLine.DepartureDate)) AS MorningCount,
      Sum((CASE WHEN ReservationConferenceLine.ArrivalTime < '18:00' AND ReservationConferenceLine.DepartureTime > '12:00' THEN 1 ELSE 0 END) + DATEDIFF (day ,ReservationConferenceLine.ArrivalDate ,ReservationConferenceLine.DepartureDate)) AS AfternoonCount,
      Sum((CASE WHEN ReservationConferenceLine.ArrivalTime < '24:00' AND (ReservationConferenceLine.DepartureTime > '18:00' OR ReservationConferenceLine.DepartureTime < '06:00') THEN 1 ELSE 0 END) + DATEDIFF (day ,ReservationConferenceLine.ArrivalDate ,ReservationConferenceLine.DepartureDate)) AS EveningCount,
      0 AS OvernightingCount
    FROM Reservations WITH (NOLOCK)
    INNER JOIN ReservationConferenceLine WITH (NOLOCK) ON
      Reservations.ReferenceNumber = ReservationConferenceLine.ReferenceNumber
      AND IsNull(ReservationConferenceLine.TableBooking, 0) = 0 
      AND Reservations.ReferenceNumber = @ResRefNo
      AND Reservations.GuestNumber = 0
      AND Reservations.Status < 7 -- rsCancelled
      AND ((ReservationConferenceLine.DepartureDate BETWEEN @ADateFrom AND @ADateTo) OR (ReservationConferenceLine.ArrivalDate > @ADateTo))
    GROUP BY 
      ReservationConferenceLine.ReferenceNumber, 
      ReservationConferenceLine.ArrivalDate,
      ReservationConferenceLine.ArrivalTime,
      ReservationConferenceLine.DepartureDate,
      ReservationConferenceLine.DepartureTime

  OPEN CurMiceDataConfLines

  FETCH NEXT FROM CurMiceDataConfLines INTO
    @ResLineArrivalDate,
    @ResLineArrivalTime,
    @ResLineDepartureDate,
    @ResLineDepartureTime,
    @ResLineGuestCountConference,
    @ResLineRoomCount,
    @ResLineMorningCount,
    @ResLineAfternoonCount,
    @ResLineEveningCount,
    @ResLineOvernightingCount

  WHILE @@FETCH_STATUS <> -1
  BEGIN
	IF @ResLineEveningCount > 0
		SET @ResLineOvernightingCount = @ResLineGuestCountRoom

    INSERT INTO #MiceReservations (ReferenceNumber, SubId, ArrivalDate, ArrivalTime, DepartureDate, DepartureTime, ReservationDate, SegmentNumber, 
            RoomCount, GuestCount, RecType, OvernightingCount, IsTentative, Turnover, MorningCount, AfternoonCount, EveningCount, GuestCountryCode)
    VALUES (@ResRefNo, @ResGuestNo, @ResLineArrivalDate, @ResLineArrivalTime, @ResLineDepartureDate, @ResLineDepartureTime, @ResDate, @ResSegment, 
            @ResLineRoomCount, @ResLineGuestCountConference, 2, @ResLineOvernightingCount, @ResTentative, @ResTotalPrice, @ResLineMorningCount, @ResLineAfternoonCount, @ResLineEveningCount, @ResCountryCode)
    
    FETCH NEXT FROM CurMiceDataConfLines INTO
      @ResLineArrivalDate,
      @ResLineArrivalTime,
      @ResLineDepartureDate,
      @ResLineDepartureTime,
      @ResLineGuestCountConference,
      @ResLineRoomCount,
      @ResLineMorningCount,
      @ResLineAfternoonCount,
      @ResLineEveningCount,
      @ResLineOvernightingCount
  END

  CLOSE CurMiceDataConfLines
  DEALLOCATE CurMiceDataConfLines
  end
  /*
    CurMiceReservations
  */

  FETCH NEXT FROM CurMiceReservations INTO 
    @ResRefNo, 
    @ResGuestNo, 
    @ResArrivalDate, 
    @ResDepartureDate, 
    @ResCountryCode,
    @ResTentative,
    @ResSegment,
    @ResDate,
    @ResTotalPrice
END

CLOSE CurMiceReservations
DEALLOCATE CurMiceReservations

SELECT * FROM #MiceReservations WITH (NOLOCK)
order by ReferenceNumber, SubID
--- where rectype = 2

-- SET @ARowCount = @@ROWCOUNT

DROP TABLE #MiceReservations

SET NOCOUNT OFF


