CREATE PROCEDURE [dbo].[OnlineHallRebuildReservation] @AReferenceNumber INT, @AGuestNumber INT
, @AOnlineBookTypeNumber INT,  @AHotelNumber INT = -1, @langInt Int = 0 AS 

/*
     SG 20171106: Added company addr1 and 2
	 SG 20171019: Added purpose code, payment type, contact person adr1 and 2 
     SG 20170928: Use translated name
     LJE 20110426: Added: RESERVATIONCONFERENCELINE.Children1, . Children2.
	TB20090316: Added LEFT OUTER JOIN
	TB20090316: Added LEFT OUTER JOIN
*/

SELECT RESERVATIONCONFERENCELINE.ReservationConfLineNumber
, IsNull(t.Name, h.Name) as Name
, h.Number AS HallNumber
, RESERVATIONCONFERENCELINE.NumberOfRooms
, RESERVATIONCONFERENCELINE.ArrivalDate
, RESERVATIONCONFERENCELINE.ArrivalTime
, RESERVATIONCONFERENCELINE.DepartureDate
, RESERVATIONCONFERENCELINE.DepartureTime
, RESERVATIONCONFERENCELINE.ProductNumber
, RESERVATIONCONFERENCELINE.Price
, RESERVATIONCONFERENCELINE.TotalPrice
, RESERVATIONCONFERENCELINE.Adults
, RESERVATIONCONFERENCELINE.Children1
, RESERVATIONCONFERENCELINE.Children2
, RESERVATIONCONFERENCELINE.ConferenceRoomNumber
, CONFERENCEROOMTYPES.Type
, CONFERENCEROOMTYPES.ConferenceRoomTypeNumber
, IsNull(r.PURPOSECODE, 0) as PURPOSECODE
, IsNull(pc.Text, '') as PurposeText
, IsNull(pc.Description, '') as PurposeDescription
, IsNull(r.METHODOFPAYMENT, 0) as METHODOFPAYMENT
, IsNull(w_pay.WareName, '') as PaymentTypeText
, IsNull(Addr1.Address1, '') as ContactAddr1
, IsNull(Addr1.Address2, '') as ContactAddr2
, IsNull(Addr2.Address1, '') as CompanyAddr1
, IsNull(Addr2.Address2, '') as CompanyAddr2
FROM RESERVATIONCONFERENCELINE (NOLOCK) 
INNER JOIN Reservations r with (nolock) ON RESERVATIONCONFERENCELINE.ReferenceNumber = r.ReferenceNumber
LEFT OUTER JOIN CONFERENCEROOMS h (NOLOCK) ON RESERVATIONCONFERENCELINE.ConferenceRoomNumber = h.ConferenceRoomNumber 
LEFT OUTER JOIN CONFERENCEROOMTYPES (NOLOCK) ON h.ConferenceRoomTypeNumber = CONFERENCEROOMTYPES.ConferenceRoomTypeNumber 
LEFT OUTER JOIN Translation t on h.ConferenceRoomNumber = t.ObjectNumber and t.Type=4 and t.CountryNumber = @langInt
LEFT OUTER JOIN PurposeCodes pc with (nolock) ON r.PURPOSECODE = pc.PurposeCodeNumber
LEFT OUTER JOIN Ware w_pay with (nolock) ON r.METHODOFPAYMENT = w_pay.WareNumber
LEFT OUTER JOIN ReservationAddressXTab Xtab1 WITH (NOLOCK) ON ISNULL(r.GuestNumber, 0) = XTab1.GuestNumber 
                    AND XTab1.ReferenceNumber = r.ReferenceNumber 
                    AND XTab1.AddressTypeNumber = 1 
LEFT OUTER JOIN ReservationAddress Addr1 WITH (NOLOCK) ON Addr1.AddressNumber = XTab1.AddressNumber
LEFT OUTER JOIN ReservationAddressXTab Xtab2 WITH (NOLOCK) ON ISNULL(r.GuestNumber, 0) = XTab2.GuestNumber 
                    AND XTab2.ReferenceNumber = r.ReferenceNumber 
                    AND XTab2.AddressTypeNumber = 2 
LEFT OUTER JOIN ReservationAddress Addr2 WITH (NOLOCK) ON Addr2.AddressNumber = XTab2.AddressNumber --Company
WHERE RESERVATIONCONFERENCELINE.ReferenceNumber = @AReferenceNumber 
AND isNull(RESERVATIONCONFERENCELINE.GuestNo, 0) = @AGuestNumber
