



CREATE PROCEDURE DBO.PrintFutureReservations AS
SET NOCOUNT ON

DECLARE @ReferenceNumber int
DECLARE @ArrivalDate varchar (8)
DECLARE @DepartureDate varchar (8)
DECLARE @AttentionName varchar(50)
DECLARE @FullString VARCHAR (250)

PRINT 'FILEFORMAT : RefNumber + Firstname + LastName, Arrivaldate,departuredate,Address1,Address2,ZipCode,City,CountryCode,AttentionName'

DECLARE @AddressTypeNumber INT


 
  DECLARE  AddressCur Cursor For
  SELECT ReferenceNumber,ArrivalDate,Departuredate FROM Reservations WITH (NOLOCK) WHERE GuestNumber = 0 AND Status IN (0,1,2)
--  AND ArrivalDate > = '20051007' AND ArrivalDate <= '20051231'
  AND ReferenceNumber NOT IN (SELECT ReferenceNumber FROM ReservationAddressXtab WITH (NOLOCK) WHERE AddressTypeNumber > 2 AND GuestNumber = 0)
  OPEN AddressCur
  FETCH NEXT FROM AddressCur INTO @ReferenceNumber,@Arrivaldate,@departuredate
 
  WHILE  (@@FETCH_STATUS = 0)
  BEGIN
     SET @AddressTypeNumber = NULL
     SET @FullString = NULL
     SELECT @AddressTypeNumber = AddressTypeNumber FROM ReservationAddressXtab WITH (NOLOCK) WHERE AddressTypeNumber = 2 AND ReferenceNumber = @ReferenceNumber AND GuestNumber = 0
     AND AddressTypeNumber = 2
   
     IF @AddressTypeNumber IS NOT NULL 
       SELECT @AttentionName = AttentionName FROM ReservationAddress WITH (NOLOCK) WHERE AddressNumber IN (SELECT AddressNumber FROM ReservationAddressXtab WITH (NOLOCK) WHERE
       ReferenceNumber = @ReferenceNumber AND GuestNumber = 0 AND AddressTypeNumber = 2)

  
    SET @AddressTypeNumber = 1


     SELECT @FullString =  LTRIM(CONVERT(VARCHAR(6),@ReferenceNumber)) + ',' +  LTRIM(ISNULL(FirstName,'') + ' ' + ISNULL(LastName,''))  
     + ',' + ISNULL(@ArrivalDate,'') + ',' + ISNULL(@DepartureDate,'')
     +',' + ISNULL(Address1,'') + ',' + ISNULL(Address2,'') + ','  + ISNULL(ZipCode,'') + ','  + ISNULL(City,'') + ',' + ISNULL(CountryCode,'') + ',' + ISNULL(@AttentionName,'')  FROM ReservationAddress WITH (NOLOCK) WHERE AddressNumber IN (SELECT AddressNumber FROM ReservationAddressXtab WITH (NOLOCK) WHERE

     ReferenceNumber = @ReferenceNumber AND GuestNumber = 0 AND AddressTypeNumber = @AddressTypeNumber)

     IF @FullString IS NOT NULL         
       PRINT @FullString
     FETCH NEXT FROM AddressCur INTO @ReferenceNumber,@arrivaldate,@Departuredate

  END
  CLOSE AddressCur
  DEALLOCATE AddressCur

