CREATE PROCEDURE DBO.InhouseAfterNation @AFromDate VARCHAR(8), @AToDate VARCHAR(8), @AStatusString VARCHAR(100), @ATypeString VARCHAR(100), @AHotelNumbers VARCHAR(50) AS

/*
  SP moved from InhouseAfterNation.pas

  Changes:
    KJ 20191213: Bug: Fix for "Wrong country code". Case #CR173756
    KJ 20181105: Bug: Fix for "Mixed resources not implemented". Case #CR155584
    KJ 20180529: Ext: Added countrygroup
    KJ 20120301: Ext: Added Owner Reservations. Req #1169
    KJ 20110714: Ext: Added Adults, Children1, Children2. Req# 620
    KJ 20080718: Bug: Fix for "CountryCode not correct"
    KJ 20080422: Bug: Fix for "CountryCode not correct"
    KJ 20080129: Bug: Fix for "String or binary data would be truncated."
    KJ 20070717: Fix for "Subquery returned more than 1 value. "
    KJ 20060510: Fix for Count not correct when not running Domino

*/

SET NOCOUNT ON

DECLARE @hotelnumber INT
DECLARE @referencenumber INT
DECLARE @guestnumber INT
DECLARE @linenumber INT
DECLARE @arrivaldate VARCHAR(8)
DECLARE @departuredate VARCHAR(8)
DECLARE @pax INT
DECLARE @Adults INT
DECLARE @Children1 INT
DECLARE @Children2 INT
DECLARE @GroupName VARCHAR(10)
DECLARE @GuestGroupName VARCHAR(10)
DECLARE @ix INT
DECLARE @Link INT
DECLARE @CustomerNumber INT
DECLARE @GuestGroupNumber INT

DECLARE @ex_arrival VARCHAR(8)
set @ex_arrival = @AFromDate

DECLARE @ex_departure VARCHAR(8)
set @ex_departure = @AToDate
set @ex_departure = CONVERT(VARCHAR(8), CONVERT(datetime, CONVERT(INT, CONVERT(datetime, @ex_departure, 112)) + 1), 112)

DECLARE res_cur CURSOR FOR
  SELECT ISNULL(RESERVATIONMAIN.HOTELNUMBER, 0), RESERVATIONS.ReferenceNumber, RESERVATIONS.GuestNumber, ReservationRoomline.LineNumber,
  CASE WHEN Reservations.GuestNumber = 0 THEN ReservationRoomline.Arrivaldate ELSE Reservations.ArrivalDate END AS Arrivaldate,
  CASE WHEN Reservations.GuestNumber = 0 THEN ReservationRoomline.Departuredate ELSE reservations.Departuredate END AS Departuredate,
  CASE WHEN Reservations.GuestNumber = 0 THEN (ISNULL(ReservationRoomline.Adults,0)+ISNULL(ReservationRoomline.Children1,0)+ISNULL(ReservationRoomline.Children2,0)) * ReservationRoomline.NumberOfRooms ELSE (ISNULL(Reservations.Adults,0)+ISNULL(Reservations.Children1,0)+ISNULL(Reservations.Children2,0)) * ReservationRoomline.NumberOfRooms END AS Pax,
  CASE WHEN Reservations.GuestNumber = 0 THEN ISNULL(ReservationRoomline.Adults,0) * ReservationRoomline.NumberOfRooms ELSE ISNULL(Reservations.Adults,0) * ReservationRoomline.NumberOfRooms END AS Adults,
  CASE WHEN Reservations.GuestNumber = 0 THEN ISNULL(ReservationRoomline.Children1,0) * ReservationRoomline.NumberOfRooms ELSE ISNULL(Reservations.Children1,0) * ReservationRoomline.NumberOfRooms END AS Children1,
  CASE WHEN Reservations.GuestNumber = 0 THEN ISNULL(ReservationRoomline.Children2,0) * ReservationRoomline.NumberOfRooms ELSE ISNULL(Reservations.Children2,0) * ReservationRoomline.NumberOfRooms END AS Children2,
  (SELECT TOP 1 GroupCode FROM CountryGroups WITH (NOLOCK) WHERE GroupNumber =
  (
    CASE WHEN
      ISNULL((SELECT TOP 1 CountryGroupNumber FROM Countries WITH (NOLOCK) WHERE CountryCode = ((SELECT TOP 1 CountryCode FROM ReservationAddress WITH (NOLOCK) WHERE (AddressNumber IN
        (SELECT AddressNumber FROM ReservationAddressXTab WITH (NOLOCK) WHERE ReferenceNumber = Reservations.ReferenceNumber AND GuestNumber = Reservations.GuestNumber)) AND AddressType = 1))), 0) = 0
    THEN
      (SELECT TOP 1 CountryGroupNumber FROM Countries WITH (NOLOCK) where CountryNumber = ((SELECT TOP 1 CountryNumber FROM ReservationAddress WITH (NOLOCK) WHERE (AddressNumber IN
          (SELECT AddressNumber FROM ReservationAddressXTab WITH (NOLOCK) WHERE ReferenceNumber = Reservations.ReferenceNumber AND GuestNumber = Reservations.GuestNumber)) AND AddressType = 1)))
    ELSE
      (SELECT TOP 1 CountryGroupNumber FROM Countries WITH (NOLOCK) where CountryCode = ((SELECT TOP 1 CountryCode FROM ReservationAddress WITH (NOLOCK) WHERE (AddressNumber IN
          (SELECT AddressNumber FROM ReservationAddressXTab WITH (NOLOCK) WHERE ReferenceNumber = Reservations.ReferenceNumber AND GuestNumber = Reservations.GuestNumber)) AND AddressType = 1)))
    END
  )) AS GuestGroupName,

  ra.Link, Rooms.CustomerNumber,
  (SELECT TOP 1 GroupNumber FROM CountryGroups WITH (NOLOCK)
    INNER JOIN Countries WITH (NOLOCK) ON Countries.CountryGroupNumber = Countrygroups.GroupNumber WHERE Countrynumber = (SELECT TOP 1 CountryNumber FROM ReservationAddressXtab WITH (NOLOCK)
    INNER JOIN ReservationAddress WITH (NOLOCK) on ReservationAddress.AddressNumber = ReservationaddressXtab.AddressNumber WHERE ReservationAddress.Addresstype = 1 AND ReferenceNumber = Reservations.Referencenumber
  AND GuestNumber = Reservations.GuestNumber)) AS GuestGroupNumber
  FROM RESERVATIONS WITH (NOLOCK)
  INNER JOIN RESERVATIONMAIN WITH (NOLOCK) ON RESERVATIONS.ReferenceNumber = RESERVATIONMAIN.REFERENCENUMBER
  INNER JOIN RESERVATIONROOMLINEXTAB WITH (NOLOCK) ON RESERVATIONS.ReferenceNumber = RESERVATIONROOMLINEXTAB.ReferenceNumber AND RESERVATIONS.GuestNumber = RESERVATIONROOMLINEXTAB.GuestNumber
  INNER JOIN RESERVATIONROOMLINE WITH (NOLOCK) ON RESERVATIONROOMLINEXTAB.LINENUMBER = RESERVATIONROOMLINE.LINENUMBER
  INNER JOIN ReservationAddressXTab rax WITH (NOLOCK) ON Reservations.ReferenceNumber = rax.ReferenceNumber 
    AND Reservations.GuestNumber = rax.GuestNumber
  LEFT OUTER JOIN ReservationAddress ra WITH (NOLOCK) ON ra.AddressNumber = rax.AddressNumber 
  LEFT OUTER JOIN Rooms WITH (NOLOCK) ON Rooms.RoomNumber = ReservationRoomLine.RoomNumber
  LEFT OUTER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = ReservationRoomLine.BookedTypeNumber
  WHERE Reservations.Referencenumber > 0
  AND (((Reservations.GuestNumber = 0) AND (ReservationRoomline.ArrivalDate <= @ex_departure and reservationroomline.departuredate > @ex_arrival))
    OR ((Reservations.GuestNumber > 0) AND (Reservations.ArrivalDate <= @ex_departure and reservations.departuredate > @ex_arrival)))
  AND Reservations.Status IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AStatusString))
  AND ReservationMain.ReservationType in  (SELECT DISTINCT Number FROM iter_intlist_to_table(@ATypeString))
  AND ISNULL(RoomTypes.HotelNumber, -1) IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumbers))
  AND ra.AddressType = 1
OPEN res_cur

FETCH NEXT FROM res_cur INTO @HOTELNUMBER, @ReferenceNumber, @GuestNumber, @linenumber, @Arrivaldate, @Departuredate, @Pax, @Adults, @Children1, @Children2, 
  @GuestGroupName, @Link, @CustomerNumber, @GuestGroupNumber

DECLARE @thisday INT
DECLARE @arrival INT
DECLARE @departure INT

IF @@fetch_status = 0
BEGIN
  CREATE TABLE #temp_reservations (
    HOTELNUMBER INT,
    ReferenceNumber INT,
    GuestNumber INT,
    linenumber INT,
    aDate VARCHAR(8) COLLATE DATABASE_DEFAULT,
    Pax INT,
    Adults INT,
    Children1 INT,
    Children2 INT,
    GroupName VARCHAR(10) COLLATE DATABASE_DEFAULT,
    Link INT NULL, 
    CustomerNumber INT NULL,
    GuestGroupNumber INT NULL)

  while @@fetch_status = 0
  begin
    if not (@guestgroupname is null)   
      set @GroupName = @guestgroupname
    if (@groupname is null) 
      set @groupname = 'halllllooooooooooooooo'  --Nice going Spider

    set @thisday = CONVERT(INT, CONVERT(datetime, @arrivaldate, 112))

    while @thisday < CONVERT(INT, CONVERT(datetime, @departuredate, 112))
    begin
      if (@thisday < CONVERT(INT, CONVERT(datetime, @departuredate, 112))) AND (@thisday >= CONVERT(INT, CONVERT(datetime, @ex_arrival, 112))) AND (@thisday < CONVERT(INT, CONVERT(datetime, @ex_departure, 112)))
        insert into #temp_reservations (HOTELNUMBER,ReferenceNumber,GuestNumber, linenumber, aDate, Pax, Adults, Children1, Children2, GroupName, Link, CustomerNumber, GuestGroupNumber) values 
          (@hotelnumber, @referencenumber, @guestnumber, @linenumber, CONVERT(VARCHAR(8), CONVERT(datetime, @thisday, 112), 112), @pax, @Adults, @Children1, @Children2, isNull(@groupname, ' '), @Link, @CustomerNumber, @GuestGroupNumber) 
      set @thisday = @thisday + 1 
    end 
    FETCH NEXT FROM res_cur INTO @HOTELNUMBER, @ReferenceNumber, @GuestNumber, @linenumber, @Arrivaldate, @Departuredate, @Pax, @Adults, @Children1, @Children2, @GuestGroupName, 
      @Link, @CustomerNumber, @GuestGroupNumber
  end 

  SET @arrival = CONVERT(INT, CONVERT(datetime, @ex_arrival, 112)) 
  SET @departure = CONVERT(INT, CONVERT(datetime, @ex_departure, 112)) 
  SET @thisday = @arrival 

  WHILE @thisday < @departure 
  BEGIN 
    INSERT INTO #temp_reservations (HOTELNUMBER,ReferenceNumber,GuestNumber, linenumber, aDate, Pax, Adults, Children1, Children2, GroupName, Link, CustomerNumber, GuestGroupNumber) values 
      (0, 0, 0, 0, CONVERT(VARCHAR(8), CONVERT(datetime, @thisday, 112), 112), 0, 0, 0, 0, @groupname, NULL, NULL, NULL) 
    SET @thisday = @thisday + 1 
  END 

  SELECT aDate, GroupName, (SELECT TOP 1 GroupNumber FROM CountryGroups WITH (NOLOCK) WHERE CountryGroups.GroupCode = #temp_reservations.GroupName) AS GroupNumber, 
    (SELECT TOP 1 GroupSortNumber FROM CountryGroups WITH (NOLOCK) WHERE CountryGroups.GroupCode = #temp_reservations.GroupName) AS GroupSortNumber, 
    SUM(pax) AS pax, SUM(Adults) AS Adults, SUM(Children1) AS Children1, SUM(Children2) AS Children2, Link, CustomerNumber, GuestGroupNumber
  FROM #temp_reservations WITH (NOLOCK) 
  GROUP by aDate, GroupName,Link, CustomerNumber, GuestGroupNumber ORDER BY aDate
  
  DROP TABLE #temp_reservations 
END 
ELSE 
  SELECT '' WHERE 1=2

CLOSE res_cur
DEALLOCATE res_cur