
CREATE PROCEDURE DBO.FixIdentityFields AS
SET NOCOUNT ON
/*
    Created by Jacob Pedersen
    Last Updated:
    KJ 20080821: Changed TableName to DBTableName
    20060501 JP: fix for going from the old way to the new way
    20060426 JP: fix for ReservationIdentity per hotel in a chain
    20050713 KJ: added BillIdentity and InvoiceIdentity
    20050713 JP: changed to use new IdentityFields table
    Last Updated: 2502 2005  Anders  reservationroomlineidentity, if reservationroomline is empty then set tmp variable to 0
    Last Updated: 10/12-2003 Spider  Added ResturantReservation for ReservationIdentity + Prints
    15/04-2003 Jacob Pedersen

*/
--SET ANSI_WARNINGS OFF
DECLARE @tmp INT
DECLARE @tmp2 INT
DECLARE @HotelCustomerNumber INT
DECLARE @IdenField INT

PRINT 'ALLOTMENTIDENTITY'
SELECT @tmp=ISNULL(MAX(AllotmentNumber),0)+1 FROM Allotments WITH (NOLOCK)
UPDATE IdentityFields SET IdentityField=@tmp WHERE UPPER(DBTableName)='ALLOTMENTIDENTITY' AND HotelCustomerNumber=-1
IF @@rowcount=0
  INSERT INTO IdentityFields (DBTableName, HotelCustomerNumber, IdentityField) VALUES ('ALLOTMENTIDENTITY', -1, @tmp)

PRINT 'BILLIDENTITY'
DECLARE #db_Identity CURSOR FOR

  SELECT ISNULL(MAX(BillNumber),0)+1 AS IdenField, HotelCustomerNumber FROM Invoices WITH (nolock) GROUP BY HotelCustomerNumber

OPEN #db_Identity

FETCH NEXT FROM #db_Identity INTO @IdenField, @HotelCustomerNumber

WHILE @@fetch_status = 0
BEGIN
  UPDATE IdentityFields SET IdentityField=@IdenField 
  WHERE UPPER(DBTableName) = 'BILLIDENTITY' AND HotelCustomerNumber = @HotelCustomerNumber
  IF @@rowcount=0
    INSERT INTO IdentityFields (DBTableName, HotelCustomerNumber, IdentityField) VALUES ('BILLIDENTITY', @HotelCustomerNumber, @IdenField)
  
  FETCH NEXT FROM #db_Identity INTO @IdenField, @HotelCustomerNumber
END

CLOSE #db_Identity
DEALLOCATE #db_Identity

PRINT 'CONFERENCEROOMSIDENTITY'
SELECT @tmp=ISNULL(MAX(ConferenceRoomNumber),0)+1 FROM ConferenceRooms WITH (NOLOCK)
UPDATE IdentityFields SET IdentityField=@tmp WHERE UPPER(DBTableName)='CONFERENCEROOMSIDENTITY' AND HotelCustomerNumber=-1
IF @@rowcount=0
  INSERT INTO IdentityFields (DBTableName, HotelCustomerNumber, IdentityField) VALUES ('CONFERENCEROOMSIDENTITY', -1, @tmp)

PRINT 'CONFERENCEROOMTABLESIDENTITY'
SELECT @tmp=ISNULL(MAX(ConferenceRoomTableNumber),0)+1 FROM ConferenceRoomTables WITH (NOLOCK)
UPDATE IdentityFields SET IdentityField=@tmp WHERE UPPER(DBTableName)='CONFERENCEROOMTABLESIDENTITY' AND HotelCustomerNumber=-1
IF @@rowcount=0
  INSERT INTO IdentityFields (DBTableName, HotelCustomerNumber, IdentityField) VALUES ('CONFERENCEROOMTABLESIDENTITY', -1, @tmp)

PRINT 'CONFERENCEROOMTYPESIDENTITY'
SELECT @tmp=ISNULL(MAX(ConferenceRoomTypeNumber),0)+1 FROM ConferenceRoomTypes WITH (NOLOCK)
UPDATE IdentityFields SET IdentityField=@tmp WHERE UPPER(DBTableName)='CONFERENCEROOMTYPESIDENTITY' AND HotelCustomerNumber=-1
IF @@rowcount=0
  INSERT INTO IdentityFields (DBTableName, HotelCustomerNumber, IdentityField) VALUES ('CONFERENCEROOMTYPESIDENTITY', -1, @tmp)

PRINT 'CUSTOMERIDENTITY'
SELECT @tmp=ISNULL(MAX(CustomerNumber),0)+1 FROM Customer WITH (NOLOCK)
UPDATE IdentityFields SET IdentityField=@tmp WHERE UPPER(DBTableName)='CUSTOMERIDENTITY' AND HotelCustomerNumber=-1
IF @@rowcount=0
  INSERT INTO IdentityFields (DBTableName, HotelCustomerNumber, IdentityField) VALUES ('CUSTOMERIDENTITY', -1, @tmp)

PRINT 'FOLIOIDENTITY'
SELECT @tmp=ISNULL(MAX(FolioNumber),0)+1 FROM Folios WITH (NOLOCK)
UPDATE IdentityFields SET IdentityField=@tmp WHERE UPPER(DBTableName)='FOLIOIDENTITY' AND HotelCustomerNumber=-1
IF @@rowcount=0
  INSERT INTO IdentityFields (DBTableName, HotelCustomerNumber, IdentityField) VALUES ('FOLIOIDENTITY', -1, @tmp)

PRINT 'GLOBALTRANSACTIONIDENTITY'
SELECT @tmp=ISNULL(MAX(GlobalTransactionNumber),0)+1 FROM Transactions WITH (NOLOCK)
UPDATE IdentityFields SET IdentityField=@tmp WHERE UPPER(DBTableName)='GLOBALTRANSACTIONIDENTITY' AND HotelCustomerNumber=-1
IF @@rowcount=0
  INSERT INTO IdentityFields (DBTableName, HotelCustomerNumber, IdentityField) VALUES ('GLOBALTRANSACTIONIDENTITY', -1, @tmp)

PRINT 'INVOICEIDENTITY'
DECLARE #db_Identity CURSOR FOR
  SELECT ISNULL(MAX(InvoiceNumber),0)+1 AS IdenField, HotelCustomerNumber FROM Invoices WITH (nolock) GROUP BY HotelCustomerNumber
OPEN #db_Identity

FETCH NEXT FROM #db_Identity INTO @IdenField, @HotelCustomerNumber

WHILE @@fetch_status = 0
BEGIN
  UPDATE IdentityFields SET IdentityField=@IdenField 
  WHERE UPPER(DBTableName) = 'INVOICEIDENTITY' AND HotelCustomerNumber = @HotelCustomerNumber
  IF @@rowcount=0
    INSERT INTO IdentityFields (DBTableName, HotelCustomerNumber, IdentityField) VALUES ('INVOICEIDENTITY', @HotelCustomerNumber, @IdenField)
  
  FETCH NEXT FROM #db_Identity INTO @IdenField, @HotelCustomerNumber
END

CLOSE #db_Identity
DEALLOCATE #db_Identity

PRINT 'PACKETIDENTITY'
SELECT @tmp=ISNULL(MAX(PacketNumber),0)+1 FROM Packet WITH (NOLOCK)
UPDATE IdentityFields SET IdentityField=@tmp WHERE UPPER(DBTableName)='PACKETIDENTITY' AND HotelCustomerNumber=-1
IF @@rowcount=0
  INSERT INTO IdentityFields (DBTableName, HotelCustomerNumber, IdentityField) VALUES ('PACKETIDENTITY', -1, @tmp)

PRINT 'PACKETITEMIDENTITY'
SELECT @tmp=ISNULL(MAX(PacketItemNumber),0)+1 FROM PacketItem_NEW WITH (NOLOCK)
UPDATE IdentityFields SET IdentityField=@tmp WHERE UPPER(DBTableName)='PACKETITEMIDENTITY' AND HotelCustomerNumber=-1
IF @@rowcount=0
  INSERT INTO IdentityFields (DBTableName, HotelCustomerNumber, IdentityField) VALUES ('PACKETITEMIDENTITY', -1, @tmp)

PRINT 'PERSONELIDENTITY'
SELECT @tmp=ISNULL(MAX(UserIDNumber),0)+1 FROM Personel WITH (NOLOCK)
UPDATE IdentityFields SET IdentityField=@tmp WHERE UPPER(DBTableName)='PERSONELIDENTITY' AND HotelCustomerNumber=-1
IF @@rowcount=0
  INSERT INTO IdentityFields (DBTableName, HotelCustomerNumber, IdentityField) VALUES ('PERSONELIDENTITY', -1, @tmp)

PRINT 'PRODUCTSIDENTITY'
SELECT @tmp=ISNULL(MAX(ProductNumber),0)+1 FROM Products WITH (NOLOCK)
UPDATE IdentityFields SET IdentityField=@tmp WHERE UPPER(DBTableName)='PRODUCTSIDENTITY' AND HotelCustomerNumber=-1
IF @@rowcount=0
  INSERT INTO IdentityFields (DBTableName, HotelCustomerNumber, IdentityField) VALUES ('PRODUCTSIDENTITY', -1, @tmp)

PRINT 'PRODUCTWARELINKIDENTITY'
SELECT @tmp=ISNULL(MAX(ProductWareLinkNumber),0)+1 FROM Transactions WITH (NOLOCK)  -- NOTE! Is this correct???
UPDATE IdentityFields SET IdentityField=@tmp WHERE UPPER(DBTableName)='PRODUCTWARELINKIDENTITY' AND HotelCustomerNumber=-1
IF @@rowcount=0
  INSERT INTO IdentityFields (DBTableName, HotelCustomerNumber, IdentityField) VALUES ('PRODUCTWARELINKIDENTITY', -1, @tmp)

PRINT 'RESERVATIONADDRESSIDENTITY'
SELECT @tmp=ISNULL(MAX(AddressNumber),0)+1 FROM ReservationAddress WITH (NOLOCK)
SELECT @tmp2=ISNULL(MAX(AddressNumber),0)+1 FROM ReservationAddressXTab WITH (NOLOCK)
IF @tmp2 > @tmp 
  SET @tmp = @tmp2
UPDATE IdentityFields SET IdentityField=@tmp WHERE UPPER(DBTableName)='RESERVATIONADDRESSIDENTITY' AND HotelCustomerNumber=-1
IF @@rowcount=0
  INSERT INTO IdentityFields (DBTableName, HotelCustomerNumber, IdentityField) VALUES ('RESERVATIONADDRESSIDENTITY', -1, @tmp)

PRINT 'RESERVATIONIDENTITY'
DECLARE @CentralOfficeSeperateResSeries BIT
DECLARE @CentralBookingType TINYINT

SELECT @CentralOfficeSeperateResSeries=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='CentralOfficeSeperateResSeries'
IF IsNull (@CentralOfficeSeperateResSeries, 0) = 0
  SET @CentralOfficeSeperateResSeries = 0

SELECT @CentralBookingType=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='CentralBookingType'
IF IsNull (@CentralBookingType, 0) = 0
  SET @CentralBookingType = 0

IF (@CentralBookingType <> 7) OR (@CentralOfficeSeperateResSeries = 0) -- NOT MPMS or NOT Seperate series
BEGIN
  SELECT @tmp=ISNULL(MAX(ReferenceNumber),0)+1 FROM Reservations WITH (NOLOCK)
  SELECT @tmp2=ISNULL(MAX(ReferenceNumber),0)+1 FROM ReservationMain WITH (NOLOCK)
  IF @tmp2 > @tmp 
    SET @tmp = @tmp2
  SELECT @tmp2=ISNULL(MAX(RestaurantReservationNumber),0)+1 FROM RestaurantReservations WITH (NOLOCK)
  IF @tmp2 > @tmp 
    SET @tmp = @tmp2
  UPDATE IdentityFields SET IdentityField=@tmp WHERE UPPER(DBTableName)='RESERVATIONIDENTITY' AND HotelCustomerNumber=-1
  IF @@rowcount=0
    INSERT INTO IdentityFields (DBTableName, HotelCustomerNumber, IdentityField) VALUES ('RESERVATIONIDENTITY', -1, @tmp)
END
ELSE -- series per hotel
BEGIN
  PRINT'-ReservationNumber per hotel'

  DECLARE #Cursor CURSOR LOCAL FAST_FORWARD FOR

    SELECT HotelNumber FROM Customer WITH (NOLOCK) WHERE HotelNumber>0 OR LocalHotel=1

  OPEN #Cursor

  FETCH NEXT FROM #Cursor
  INTO @HotelCustomerNumber

  WHILE (@@FETCH_STATUS <> -1)
  BEGIN
    SELECT @tmp=HotelCustomerNumber FROM IdentityFields WITH (NOLOCK) WHERE HotelCustomerNumber=@HotelCustomerNumber AND DBTableName='RESERVATIONIDENTITY'
    IF @@rowcount = 0
      INSERT INTO IdentityFields (DBTableName, HotelCustomerNumber, IdentityField) VALUES ('RESERVATIONIDENTITY', @HotelCustomerNumber, 0)

    FETCH NEXT FROM #Cursor
    INTO @HotelCustomerNumber
  END

  CLOSE #Cursor
  DEALLOCATE #Cursor


  UPDATE ReservationMain SET HotelNumber=0 WHERE HotelNumber IS NULL

  DECLARE #Cursor CURSOR LOCAL FAST_FORWARD FOR

    SELECT HotelCustomerNumber FROM IdentityFields WITH (NOLOCK) WHERE DBTableName='RESERVATIONIDENTITY' AND HotelCustomerNumber>-1

  OPEN #Cursor

  FETCH NEXT FROM #Cursor
  INTO @HotelCustomerNumber

  WHILE (@@FETCH_STATUS <> -1)
  BEGIN
    SELECT @tmp=ISNULL(MAX(ReferenceNumber),0) FROM ReservationMain WITH (NOLOCK) WHERE HotelNumber=@HotelCustomerNumber AND ReferenceNumber<(@HotelCustomerNumber * 1000000) + 1000000  AND ReferenceNumber>= (@HotelCustomerNumber * 1000000) + 1

    -- Check if we are over the top. If so restart counting or below start (when first enabling the setting)
    IF (@tmp >= (@HotelCustomerNumber * 1000000) + 1000000) OR (@tmp < (@HotelCustomerNumber * 1000000) + 1)
      SET @tmp = (@HotelCustomerNumber * 1000000) + 1

    UPDATE IdentityFields SET IdentityField=@tmp WHERE DBTableName='RESERVATIONIDENTITY' AND HotelCustomerNumber=@HotelCustomerNumber

    FETCH NEXT FROM #Cursor
    INTO @HotelCustomerNumber
  END

  CLOSE #Cursor
  DEALLOCATE #Cursor

  DELETE FROM IdentityFields WHERE HotelCustomerNumber=-1 AND DBTableName='RESERVATIONIDENTITY'
END

PRINT 'RESERVATIONROOMLINEIDENTITY'
SELECT @tmp=ISNULL(MAX(LineNumber),0)+1 FROM ReservationRoomLine WITH (NOLOCK)
SELECT @tmp2=ISNULL(MAX(ReservationConfLineNumber),0)+1 FROM ReservationConferenceLine WITH (NOLOCK)
IF @tmp2 > @tmp 
  SET @tmp = @tmp2
UPDATE IdentityFields SET IdentityField=@tmp WHERE UPPER(DBTableName)='RESERVATIONROOMLINEIDENTITY' AND HotelCustomerNumber=-1
IF @@rowcount=0
  INSERT INTO IdentityFields (DBTableName, HotelCustomerNumber, IdentityField) VALUES ('RESERVATIONROOMLINEIDENTITY', -1, @tmp)

PRINT 'ROOMSIDENTITY'
SELECT @tmp=ISNULL(MAX(RoomNumber),0)+1 FROM Rooms WITH (NOLOCK)
UPDATE IdentityFields SET IdentityField=@tmp WHERE UPPER(DBTableName)='ROOMSIDENTITY' AND HotelCustomerNumber=-1
IF @@rowcount=0
  INSERT INTO IdentityFields (DBTableName, HotelCustomerNumber, IdentityField) VALUES ('ROOMSIDENTITY', -1, @tmp)

-- todo: SMSIMPORTIDENTITY

PRINT 'YIELDIDENTITY'
SELECT @tmp=ISNULL(MAX(YieldNumber),0)+1 FROM Yield WITH (NOLOCK)
UPDATE IdentityFields SET IdentityField=@tmp WHERE UPPER(DBTableName)='YIELDIDENTITY' AND HotelCustomerNumber=-1
IF @@rowcount=0
  INSERT INTO IdentityFields (DBTableName, HotelCustomerNumber, IdentityField) VALUES ('YIELDIDENTITY', -1, @tmp)

--SET ANSI_WARNINGS ON

