CREATE PROCEDURE dbo.SDHRelationManager @AReferenceNumber INT, @AGuestNumber INT AS
/*
  Created by Kent Johansen february 2014
  Changes:
    KJ 20160425: Ext: Added multiple new fields. Req #2780
    KJ 20150326: Bug: Fix for "Sp does not return correct address info" Case #CR085433
    KJ 20150206: Ext: Added info from Room/Hall and address info
    KJ 20140604: Added some fields for hotels not part of SDH, Req #2315
*/

DECLARE @HotelCustomerNumber INT
DECLARE @AddressType INT
DECLARE @RoomHallInfo INT

SELECT @HotelCustomerNumber =  CustomerNumber
FROM Customer WITH (NOLOCK)
INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.HotelNumber = Customer.HotelNumber
  AND ReservationMain.ReferenceNumber = @AReferenceNumber
WHERE Customer.HotelNumber>=0
AND Customer.CustomerTypeNumber=7

SELECT @AddressType = dbo.GetHotelSetupKeyValue ('RelationManagerAddressInfoType', @HotelCustomerNumber)
SET @AddressType = ISNULL(@AddressType, 1)

SELECT @RoomHallInfo = dbo.GetHotelSetupKeyValue ('RelationManagerRoomHallInfo', @HotelCustomerNumber)
SET @RoomHallInfo = ISNULL(@RoomHallInfo, 0)

IF @RoomHallInfo = 0
BEGIN
  SELECT ra.FirstName, ra.LastName, ra.Email, Countries.CountryName, ra.Link, ra.Phone, ra.Cell, ra.ZipCode,
  ra.Address1, ra.Address2, ra.Address3,
  rrl.ArrivalDate, rrl.DepartureDate, CONVERT(CHAR(8), f.CreatedDate, 112) AS CreatedDate, 
  cc.Text AS CampaignCodeText, p.ProductName, ISNULL(rrl.Adults, 0) + ISNULL(rrl.Children1, 0) + ISNULL(rrl.Children2, 0) AS Pax,
  rt.Type AS RoomType, gt.Text AS GuestType,
  (SELECT ISNULL(
    (CASE WHEN (ra.CITY IS NULL OR LEN(LTRIM(ra.CITY)) = 0) THEN 
      (SELECT TOP 1 CityName FROM Zipcodes WITH (NOLOCK)
      INNER JOIN Countries WITH (NOLOCK) ON Countries.CountryNumber = Zipcodes.CountryNumber
      WHERE ra.CountryCode = Countries.CountryCode
	  AND ra.Zipcode = Zipcodes.Zipcode 
	  AND LEN(LTRIM(ra.Zipcode)) > 0)
  END), ra.CITY)) AS CityOverwrite,
  ra1.LastName AS CompanyName, Rooms.Number AS VisualRoomNumber, pc.Text AS PurposeCodeName, s.Text AS SegmentName,
  ResNotes.Text AS ReceptionNote, rm.ReservationType, pos.Text AS POSName,
  (SELECT TOP 1 LastName FROM ReservationAddress WITH (NOLOCK) WHERE (AddressNumber IN 
    (SELECT AddressNumber FROM ReservationAddressXTab WITH (NOLOCK) WHERE ReferenceNumber = r.ReferenceNumber AND GuestNumber = 0)) AND AddressType = 2) AS GuestCompanyName
  FROM Reservations r WITH (NOLOCK) 
  INNER JOIN ReservationMain rm WITH (NOLOCK) ON rm.ReferenceNumber = r.ReferenceNumber
  INNER JOIN ReservationRoomLineXtab rrlx with (NOLOCK) ON rrlx.ReferenceNumber = r.ReferenceNumber
    AND rrlx.GuestNumber = r.GuestNumber
  INNER JOIN ReservationRoomLine rrl WITH (NOLOCK) ON rrl.LineNumber = rrlx.LineNumber
  INNER JOIN ReservationAddressXtab rax WITH (NOLOCK) ON rax.ReferenceNumber = r.ReferenceNumber
    AND rax.GuestNumber = r.GuestNumber
    AND rax.AddressTypeNumber = @AddressType
  INNER JOIN ReservationAddress ra WITH (NOLOCK) ON ra.AddressNumber = rax.AddressNumber
  LEFT OUTER JOIN Countries WITH (NOLOCK) ON Countries.CountryNumber = ra.CountryNumber
  INNER JOIN ReservationFollowUpXTab rfxt WITH (NOLOCK) ON rfxt.ReferenceNumber = r.ReferenceNumber
    AND rfxt.GuestNumber = r.GuestNumber
  INNER JOIN Followup f WITH (NOLOCK) ON f.FollowupNumber = rfxt.FollowupNumber
    AND f.Type = 5 
    AND f.SubType = 1
  LEFT OUTER JOIN CampaignCodes cc WITH (NOLOCK) ON cc.PK_CampaignCode = r.FK_CampaignCode
  LEFT OUTER JOIN Products p WITH (NOLOCK) ON p.ProductNumber = rrl.ProductNumber
  LEFT OUTER JOIN Rooms WITH (NOLOCK) ON Rooms.RoomNumber = rrl.roomNumber
  LEFT OUTER JOIN RoomTypes rt WITH (NOLOCK) ON rt.RoomTypeNumber = Rooms.RoomTypeNumber
  LEFT OUTER JOIN GuestTypes gt WITH (NOLOCK) ON gt.TypeNumber = r.GuestType
  LEFT OUTER JOIN ReservationAddressXtab rax1 WITH (NOLOCK) ON rax1.ReferenceNumber = r.ReferenceNumber
    AND rax1.GuestNumber = r.GuestNumber
    AND rax1.AddressTypeNumber = 2
  LEFT OUTER JOIN ReservationAddress ra1 WITH (NOLOCK) ON ra1.AddressNumber = rax1.AddressNumber
  LEFT OUTER JOIN PurposeCodes pc WITH (NOLOCK) ON pc.PurposeCodeNumber = r.PurposeCode
  LEFT OUTER JOIN Segments s WITH (NOLOCK) ON s.Number = r.MarketSegment
  LEFT OUTER JOIN ReservationNotes ResNotes WITH (NOLOCK) ON r.ReferenceNumber = ResNotes.ReferenceNumber 
    AND r.GuestNumber = ResNotes.GuestNumber 
    AND ResNotes.NotePage = 1
  LEFT OUTER JOIN PointOfSales pos WITH (NOLOCK) ON pos.Number = r.PurposeCode
  WHERE r.ReferenceNumber = @AReferenceNumber
  AND r.GuestNumber = @AGuestNumber
END
ELSE
BEGIN
  SELECT TOP 1 ra.AttentionName AS FirstName, ra.AttentionName AS LastName, ra.Email, Countries.CountryName, ra.Link, ra.Phone, ra.Cell, ra.ZipCode,
  ra.Address1, ra.Address2, ra.Address3,
  rcl.ArrivalDate, rcl.DepartureDate, CONVERT(CHAR(8), f.CreatedDate, 112) AS CreatedDate, 
  cc.Text AS CampaignCodeText, p.ProductName, ISNULL(rcl.Adults, 0) + ISNULL(rcl.Children1, 0) + ISNULL(rcl.Children2, 0) AS Pax,
  crt.Type AS RoomType, gt.Text AS GuestType,
  (SELECT ISNULL(
    (CASE WHEN (ra.CITY IS NULL OR LEN(LTRIM(ra.CITY)) = 0) THEN 
      (SELECT TOP 1 CityName FROM Zipcodes WITH (NOLOCK)
      INNER JOIN Countries WITH (NOLOCK) ON Countries.CountryNumber = Zipcodes.CountryNumber
      WHERE ra.CountryCode = Countries.CountryCode
	  AND ra.Zipcode = Zipcodes.Zipcode 
	  AND LEN(LTRIM(ra.Zipcode)) > 0)
  END), ra.CITY)) AS CityOverwrite,
  ra1.LastName AS CompanyName, cr.Number AS VisualRoomNumber, pc.Text AS PurposeCodeName, s.Text AS SegmentName,
  ResNotes.Text AS ReceptionNote, rm.ReservationType, pos.Text AS POSName,
  (SELECT TOP 1 LastName FROM ReservationAddress WITH (NOLOCK) WHERE (AddressNumber IN 
    (SELECT AddressNumber FROM ReservationAddressXTab WITH (NOLOCK) WHERE ReferenceNumber = r.ReferenceNumber AND GuestNumber = 0)) AND AddressType = 2) AS GuestCompanyName
  FROM Reservations r WITH (NOLOCK) 
  INNER JOIN ReservationMain rm WITH (NOLOCK) ON rm.ReferenceNumber = r.ReferenceNumber
  INNER JOIN ReservationConferenceLine rcl with (NOLOCK) ON rcl.ReferenceNumber = r.ReferenceNumber
    AND ISNULL(rcl.GuestNo, 0) = r.GuestNumber
	AND ISNULL(rcl.TableBooking, 0) = 0
  INNER JOIN ReservationAddressXtab rax WITH (NOLOCK) ON rax.ReferenceNumber = r.ReferenceNumber
    AND rax.GuestNumber = r.GuestNumber
    AND rax.AddressTypeNumber = @AddressType
  INNER JOIN ReservationAddress ra WITH (NOLOCK) ON ra.AddressNumber = rax.AddressNumber
  LEFT OUTER JOIN Countries WITH (NOLOCK) ON Countries.CountryNumber = ra.CountryNumber
  INNER JOIN ReservationFollowUpXTab rfxt WITH (NOLOCK) ON rfxt.ReferenceNumber = r.ReferenceNumber
    AND rfxt.GuestNumber = r.GuestNumber
  INNER JOIN Followup f WITH (NOLOCK) ON f.FollowupNumber = rfxt.FollowupNumber
    AND f.Type = 5 
    AND f.SubType = 1
  LEFT OUTER JOIN CampaignCodes cc WITH (NOLOCK) ON cc.PK_CampaignCode = r.FK_CampaignCode
  LEFT OUTER JOIN Products p WITH (NOLOCK) ON p.ProductNumber = rcl.ProductNumber
  LEFT OUTER JOIN ConferenceRooms cr WITH (NOLOCK) ON cr.ConferenceRoomNumber = rcl.ConferenceRoomNumber
  LEFT OUTER JOIN ConferenceRoomTypes crt WITH (NOLOCK) ON crt.ConferenceRoomTypeNumber = cr.ConferenceRoomTypeNumber
  LEFT OUTER JOIN GuestTypes gt WITH (NOLOCK) ON gt.TypeNumber = r.GuestType
  LEFT OUTER JOIN ReservationAddressXtab rax1 WITH (NOLOCK) ON rax1.ReferenceNumber = r.ReferenceNumber
    AND rax1.GuestNumber = r.GuestNumber
    AND rax1.AddressTypeNumber = 2
  LEFT OUTER JOIN ReservationAddress ra1 WITH (NOLOCK) ON ra1.AddressNumber = rax1.AddressNumber
  LEFT OUTER JOIN PurposeCodes pc WITH (NOLOCK) ON pc.PurposeCodeNumber = r.PurposeCode
  LEFT OUTER JOIN Segments s WITH (NOLOCK) ON s.Number = r.MarketSegment
  LEFT OUTER JOIN ReservationNotes ResNotes WITH (NOLOCK) ON r.ReferenceNumber = ResNotes.ReferenceNumber 
    AND r.GuestNumber = ResNotes.GuestNumber 
    AND ResNotes.NotePage = 1
  LEFT OUTER JOIN PointOfSales pos WITH (NOLOCK) ON pos.Number = r.PurposeCode
  WHERE r.ReferenceNumber = @AReferenceNumber
  AND r.GuestNumber = @AGuestNumber
  ORDER BY rcl.ScreenLine
END