CREATE PROCEDURE dbo.InhouseListHall @AHotelNumber INT, @AArrivalDate CHAR(8), @ADepartureDate CHAR(8), @AGroupNumberCSV VARCHAR(100) = '' AS
/*
  Craeted by Kent Johansen, January 2015.
  Purpose: Returns hall info for reservations arrival for specified hotel and period (Monet)
  Changes:
    SG 20221207: Added MonetExtraHallname
	SG 20200713: Added agency
    KJ 20160414: Added Group
    KJ 20160303: Include res without company.

*/
SET NOCOUNT ON

DECLARE @AArrivalDateAddOne CHAR(8)
SET @AArrivalDateAddOne = CONVERT(CHAR(8), DATEADD(DAY, 1, CONVERT(DATETIME, @AArrivalDate, 112)), 112)

DECLARE @Arr24 CHAR(5)
SELECT @Arr24 = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'Arr24HoursStarts'
IF ISNULL(@Arr24, '') = ''
  SET @Arr24 = '07:00'

SELECT ReservationConferenceLine.ReferenceNumber, ISNULL(ReservationConferenceLine.GuestNo, 0) AS GuestNumber, Reservations.Status,
CASE WHEN ISNULL(raFirm.LastName, '') <> '' THEN
  raFirm.LastName
ELSE
  CASE WHEN ISNULL(raGuest.FirstName, '') <> '' THEN raGuest.FirstName + ' ' + raGuest.LastName ELSE raGuest.LastName END
END AS FullName,
raFirm.LastName AS CompanyLastName,
raAgency.LastName AS AgencyName,
raGuest.LastName AS GuestLastName, raGuest.FirstName AS GuestFirstName,
ConferenceRooms.Number, ConferenceRooms.Name,
IsNull(ReservationConferenceLine.MonetExtraHallname, '') AS MonetExtraHallname,
ReservationConferenceLine.ArrivalDate, ReservationConferenceLine.ArrivalTime, ReservationConferenceLine.DepartureDate,
ReservationConferenceLine.DepartureTime, ConferenceRoomMonetXTab.MonetGroupNumber AS GroupNumber, ConferenceRoomMonetXTab.Filename
FROM Reservations WITH (NOLOCK)
INNER JOIN ReservationConferenceLine WITH (NOLOCK) ON ReservationConferenceLine.ReferenceNumber = Reservations.ReferenceNumber
  AND ISNULL(ReservationConferenceLine.GuestNo, 0) = Reservations.GuestNumber
INNER JOIN ConferenceRooms WITH (NOLOCK) ON ConferenceRooms.ConferenceRoomNumber = ReservationConferenceLine.ConferenceRoomNumber
  AND ConferenceRooms.HotelNumber = @AHotelNumber
INNER JOIN ConferenceRoomMonetXTab WITH (NOLOCK) ON CONFERENCEROOMS.ConferenceRoomNumber = ConferenceRoomMonetXTab.ConferenceRoomNumber
INNER JOIN ReservationAddressXTab rax1 WITH (NOLOCK) ON rax1.ReferenceNumber = Reservations.ReferenceNumber
  AND rax1.GuestNumber = Reservations.GuestNumber
  AND rax1.AddressTypeNumber = 1
INNER JOIN ReservationAddress raGuest WITH (NOLOCK) ON raGuest.AddressNumber = rax1.AddressNumber
LEFT OUTER JOIN ReservationAddressXTab rax2 WITH (NOLOCK) ON rax2.ReferenceNumber = Reservations.ReferenceNumber
  AND rax2.GuestNumber = Reservations.GuestNumber
  AND rax2.AddressTypeNumber = 2  
LEFT OUTER JOIN ReservationAddress raFirm WITH (NOLOCK) ON raFirm.AddressNumber = rax2.AddressNumber
--Agency
LEFT OUTER JOIN ReservationAddressXTab rax3 WITH (NOLOCK) ON rax3.ReferenceNumber = Reservations.ReferenceNumber
  AND rax3.GuestNumber = Reservations.GuestNumber
  AND rax3.AddressTypeNumber = 3  
LEFT OUTER JOIN ReservationAddress raAgency WITH (NOLOCK) ON raAgency.AddressNumber = rax3.AddressNumber

WHERE (((ReservationConferenceLine.ArrivalDate = @AArrivalDate AND ReservationConferenceLine.ArrivalTime >= @Arr24)
  OR (ReservationConferenceLine.ArrivalDate=@AArrivalDateAddOne AND ReservationConferenceLine.ArrivalTime<@Arr24))
OR (ReservationConferenceLine.ArrivalDate>@AArrivalDate AND ReservationConferenceLine.ArrivalDate<=@ADepartureDate))
AND ((ISNULL(ReservationConferenceLine.SchemePattern, 0) = 0) or not (ReservationConferenceLine.LinkLineNumber is null))
AND Reservations.ReferenceNumber > 0
AND Reservations.Status <= 6
AND ISNULL(ReservationConferenceLine.TableBooking, 0) = 0
AND ((@AGroupNumberCSV = '') OR (@AGroupNumberCSV <> '' AND ISNULL(ConferenceRooms.GroupNumber, 0) IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AGroupNumberCSV))))