



CREATE PROCEDURE DBO.FindDublicateAddresses @LogOnly BIT = 0 AS
SET NOCOUNT ON
/*
     Jacob 211201

     Last Update: 
	20050420 JP: huge rewrite
	27062002 Jacob Pedersen


find the sinners!

select count (*), referencenumber, guestnumber, addresstype from reservationaddressxtab (nolock) inner join reservationaddress (nolock) on reservationaddress.addressnumber=reservationaddressxtab.addressnumber
group by referencenumber, guestnumber, addresstype
having count (*)> 1

*/

IF @LogOnly = 1
  PRINT 'LOG ONLY MODE!--------------------------------------------------------------------'

DECLARE
@Referencenumber INT,
@GuestNumber INT,
@AddressType INT,
@AddressNumber INT,
@FolioNumber INT,

@FoliosExist INT,
@TransCount INT,

@OldReferenceNumber INT,
@OldGuestNumber INT,
@OldAddressType INT,
@OldAddressNumber INT

SET @OldReferencenumber = 0
SET @OldGuestNumber = 0
SET @OldAddressType = 0
SET @OldAddressNumber = 0

print 'Update ReservationAddressXTAB with AddressType''''s from AddressType'
UPDATE ReservationAddressXTAB SET AddressTypeNumber=
  (SELECT AddressType FROM ReservationAddress WHERE ReservationAddress.AddressNumber=ReservationAddressXTAB.AddressNumber)

SELECT @AddressNumber=Count(*) FROM ReservationAddressXTab WITH (NOLOCK) WHERE NOT AddressNumber IN (SELECT AddressNumber FROM ReservationAddress WITH (NOLOCK))  -- Delete orphant records in XTab!
IF @AddressNumber > 0
begin
  print 'Delete orphant records in XTab!'
  DELETE FROM ReservationAddressXTab WHERE NOT AddressNumber IN (SELECT AddressNumber FROM ReservationAddress WITH (NOLOCK))
end;

DECLARE #AddressCur Cursor Forward_Only FOR

SELECT ReservationAddressXTab.ReferenceNumber, ReservationAddressXTab.GuestNumber, ReservationAddressXTab.AddressTypeNumber, ReservationAddressXTab.AddressNumber
FROM ReservationAddressXTab WITH (NOLOCK) INNER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddressXTab.AddressNumber = ReservationAddress.AddressNumber
ORDER BY ReferenceNumber, GuestNumber, AddressTypeNumber

/*
SELECT ReservationAddressXTab.ReferenceNumber, ReservationAddressXTab.GuestNumber, ReservationAddress.AddressType, ReservationAddressXTab.AddressNumber
FROM ReservationAddressXTab (NOLOCK) INNER JOIN ReservationAddress (NOLOCK) ON ReservationAddressXTab.AddressNumber = ReservationAddress.AddressNumber
ORDER BY ReferenceNumber, GuestNumber, AddressTypeNumber
*/
OPEN #AddressCur

FETCH NEXT FROM #AddressCur
INTO @Referencenumber, @GuestNumber, @AddressType, @AddressNumber

WHILE (@@FETCH_STATUS <> -1)
BEGIN
  IF @OldReferencenumber = @ReferenceNumber and @OldGuestNumber = @GuestNumber and @OldAddressType = @AddressType
  BEGIN
    SELECT @FoliosExist = COUNT(*) FROM Folios WITH (NOLOCK) WHERE ReservationAddressNumber=@AddressNumber

    IF @FoliosExist = 0
    BEGIN
      print 'Deleted1:'

      IF @LogOnly = 0
      BEGIN
        DELETE FROM Folios WHERE ReservationAddressNumber=@AddressNumber
        DELETE FROM ReservationAddressXTab WHERE AddressNumber=@AddressNumber
        DELETE FROM ReservationAddress WHERE AddressNumber=@AddressNumber
      END
    END
    ELSE
    BEGIN
      SELECT @FolioNumber = FolioNumber FROM Folios WITH (NOLOCK) WHERE ReservationAddressNumber=@AddressNumber

      SELECT @TransCount = COUNT(*) FROM Transactions WITH (NOLOCK) WHERE FolioNumber=@FolioNumber

      IF @TransCount > 0 -- Try the old address and see if any transactions there
      BEGIN
        PRINT 'Transactions found on folio: '+Convert (VarChar (20), @FolioNumber)

        SELECT @FolioNumber = FolioNumber FROM Folios WITH (NOLOCK) WHERE ReservationAddressNumber=@OldAddressNumber

        PRINT 'Trying folio: '+Convert (VarChar (20), @FolioNumber)+' instead'
        PRINT 'with old addressnumber: '+Convert (VarChar (20), @OldAddressNumber)

        SELECT @TransCount = COUNT(*) FROM Transactions WITH (NOLOCK) WHERE FolioNumber=@FolioNumber
      END

      IF @TransCount > 0
        print 'ERROR: Unable to delete because transactions exist:'
      ELSE
      BEGIN
        print 'Deleted2:'

        IF @LogOnly = 0
        BEGIN
          DELETE FROM Folios WHERE ReservationAddressNumber=@AddressNumber
          DELETE FROM ReservationAddressXTab WHERE AddressNumber=@AddressNumber
          DELETE FROM ReservationAddress WHERE AddressNumber=@AddressNumber
        END
      END
    END

    print 'RefNo:'+Convert (VarChar (20), @referencenumber)
    print 'GuestNo:'+Convert (VarChar (20), @guestnumber)
    print 'AddressType:'+Convert (VarChar (20), @addresstype)
    print 'AddressNo:'+Convert (VarChar (20), @addressNumber)
    print 'FolioNo:'+Convert (VarChar (20), @FolioNumber)
    print ''
  END

  SET @OldReferencenumber = @Referencenumber
  SET @OldGuestNumber = @GuestNumber
  SET @OldAddressType = @AddressType
  SET @OldAddressNumber = @AddressNumber

  Fetch Next From #AddressCur
  Into @Referencenumber, @GuestNumber, @AddressType, @AddressNumber
END

CLOSE #AddressCur
DEALLOCATE #AddressCur

