
CREATE PROCEDURE [dbo].[OnlineFindReservationCheckin] @Referencenumber INT, @UsePaymentAtLogin BIT=0 AS
SET NOCOUNT ON
/*
    Created by Troels Buch Jan 2007
    
    Changes: 
	SG 2021-04-15: Only formatting and comment
    KS20131009: Added Where clause: RESERVATIONS.Status < 6
    TB20121114: Added check on Voucher and altress / Added @UsePaymentAtLogin

	
    NOTES: 	Used for Terminal (stander) to load reservation
	        "usePaymentAtLogin": True: Search also using AltResNo and VOUCHER
*/

IF @UsePaymentAtLogin = 0
BEGIN 

SELECT distinct Reservations.guestnumber, ReservationAddressXtab.Addressnumber,  Reservations.status, Reservations.price, Reservations.totalprice, AddresstypeNumber, Lastname, Firstname, Address1, Countrycode
, zipcode, Attentionname, Reservations.arrivaldate, Reservations.Departuredate, Reservations.adults, Reservations.children1, Reservations.children2
, Reservations.productnumber, ReservationRoomLine.RoomNumber, Rooms.Number 
FROM Reservations (NOLOCK)
INNER JOIN ReservationAddressXtab (NOLOCK) ON ReservationAddressXtab.Referencenumber = Reservations.Referencenumber
                                           AND ReservationAddressXtab.GuestNumber = Reservations.GuestNumber 
										   AND ReservationAddressXtab.AddressTypeNumber = 1
INNER JOIN ReservationAddress (NOLOCK) ON ReservationAddress.AddressNumber = ReservationAddressXtab.Addressnumber
INNER JOIN ReservationRoomlineXtab (NOLOCK) ON Reservations.ReferenceNumber = ReservationRoomlineXTab.ReferenceNumber
AND ReservationRoomlineXtab.GuestNumber = Reservations.GuestNumber
INNER JOIN ReservationRoomLine (NOLOCK) ON ReservationRoomLine.LineNumber = ReservationRoomlineXTab.LineNumber
LEFT OUTER JOIN Rooms (NOLOCK) ON ReservationRoomLine.RoomNumber = Rooms.RoomNumber
WHERE Reservations.Referencenumber = @Referencenumber 
      AND RESERVATIONS.Status < 6
order by ReservationRoomLine.RoomNumber, Reservations.guestnumber, ReservationAddressXtab.Addressnumber
--print '1'
END
ELSE
BEGIN

SELECT distinct top 1 Reservations.guestnumber, ReservationAddressXtab.Addressnumber,  Reservations.status, Reservations.price, Reservations.totalprice, AddresstypeNumber, Lastname, Firstname, Address1, Countrycode
, zipcode, Attentionname, Reservations.arrivaldate, Reservations.Departuredate, Reservations.adults, Reservations.children1, Reservations.children2
, Reservations.productnumber, ReservationRoomLine.RoomNumber, Rooms.Number, Reservations.Altresno, Reservations.VOUCHER, Reservations.Referencenumber 
FROM Reservations (NOLOCK)
INNER JOIN ReservationAddressXtab (NOLOCK) ON ReservationAddressXtab.Referencenumber = Reservations.Referencenumber
                                           AND ReservationAddressXtab.GuestNumber = Reservations.GuestNumber 
										   AND ReservationAddressXtab.AddressTypeNumber = 1
INNER JOIN ReservationAddress (NOLOCK) ON ReservationAddress.AddressNumber = ReservationAddressXtab.Addressnumber
INNER JOIN ReservationRoomlineXtab (NOLOCK) ON Reservations.ReferenceNumber = ReservationRoomlineXTab.ReferenceNumber
           AND ReservationRoomlineXtab.GuestNumber = Reservations.GuestNumber
INNER JOIN ReservationRoomLine (NOLOCK) ON ReservationRoomLine.LineNumber = ReservationRoomlineXTab.LineNumber
LEFT OUTER JOIN Rooms (NOLOCK) ON ReservationRoomLine.RoomNumber = Rooms.RoomNumber
WHERE (Reservations.Referencenumber = @Referencenumber 
       OR Reservations.Altresno = CONVERT(VARCHAR(50), @Referencenumber) 
	   OR Reservations.VOUCHER = CONVERT(VARCHAR(50), @Referencenumber))
      AND RESERVATIONS.Status < 6
order by Reservations.Referencenumber DESC, ReservationRoomLine.RoomNumber, Reservations.guestnumber, ReservationAddressXtab.Addressnumber 
--print '2'
END



GO

