CREATE PROCEDURE dbo.GetNextNumber @TableName VARCHAR (30), @Increase INT, @Identity INT OUTPUT, @HotelCustomerNumber INT = -1, @PkDepartment INT = -1, @AChangeDate CHAR(8) = '' AS
SET NOCOUNT ON
/*
--nb20150622 CASHIDENTITY
--nb20150224 PREBILLIDENTITY
  KJ 20130125: Ext: GIFTCERTIFICATEIDENTITY per hotel
  RN 20121109: Ext: Added IdentityReservationRoomLineNumber table.
  NB 20121106: Ext: Added IdentityInvoice table 
  KJ 20121029: Ext: Added IdentityGlobalTransactionNumber + IdentityProductWareLinkNumber tables.
  KJ 20120216: Bug: Fix for "Cannot insert duplicate key for CourseCertificateIdentity".
  KJ 20110310: Bug: Fix for "CourseCertificateIdentity is per year and not per day".
  KJ 20110223: Ext: CourseCertificateIdentity added. req# 800
  KJ 20101206: Bug: Fix for "Subquery returned more than 1 value"
  KJ 20100301: Added Changedate. Used for audit log for which date a cashbox report has been printed
  BA/20100208: CR012331/@Identity using checking on the condition = NULL  
  KJ 20080821: Changed TableName to DBTableName
  nb20070910 Bug - handling of invoices in domino with none invoice series (neither by hotel or department) 
  nb20070824 Ext -- - - - - - Separate bill and invoice by department series - - - - - -
  nb20061019 Preventing Lock time outs Ex. can deal with 5 simultinuos calls of 2sek/call if the 6. call done (same time) the 6. call will fail with Lock Request timed out. Not likely to happend on existing systems 
  nb20060814 Invice series dont work on swedish hotels running separate invoice series only on invoice identity table numbers
  JP 20060627: OPTIMIZE: prefixed tables with dbo.
  JP 20060614: added L+RTRIM
  nb/kj 20060223: IdentityFields sikkerhed mht use invoice series på regninger. Fejl tvinges hvis forkert hotelnumber opgivet. 
  JP 20060103: EXT: CancellationNumber series. Much like ReservationNumber series
  JP 20051214: BUG: fix for disabling MPMS or CentralOfficeSeperateResSeries
  JP 20051124: BUG: if not found in table and existing reservations we get a wrong start identity
  JP 20051123: Rewrote RESERVATIONIDENTITY logic to give hotels 1 million referencenumbers each instead of sharing 2 million
  JP 20051115: 1000000->200000 res numbers
  JP 20050530: fixed bill- and invoice identities
  JP 20050513: Deleted 17 Identity tables from database
  JP 20050429: Seperate Reservation number series when MPMS
  NB20050314: Ext moved at last in parameters and a default value added
  JP 20030320:  SET @Identity = @Identity - @Increase+1 !!!!!
  JP 20030128: Created

  Note: When RESERVATIONIDENTITY then @HotelCustomerNumber is the HotelNumber and not CustomerNumber!
*/
DECLARE @LockTimeOut INT
SET @LockTimeOut = @@LOCK_TIMEOUT
SET LOCK_TIMEOUT 10000 
DECLARE @RowCount INT
DECLARE @cnt INT

declare @EnterInvoiceSeriesLogic bit
-- - - - - - D O M I N O - - - - - -
IF (@HotelCustomerNumber > -1) AND (@TableName='RESERVATIONIDENTITY' OR @TableName='CANCELLATIONIDENTITY' OR @TableName = 'GIFTCERTIFICATEIDENTITY') 
  SET @EnterInvoiceSeriesLogic = 1
ELSE
  SET @EnterInvoiceSeriesLogic = 0
  
IF @TableName = 'BILLIDENTITY' OR @TableName = 'INVOICEIDENTITY' OR @TableName = 'FOLIOIDENTITY' OR @TableName = 'PREBILLIDENTITY' OR @TableName = 'CASHIDENTITY'
  exec dbo.GetIdentityInvoice @TableName, @Increase, @Identity OUTPUT, @HotelCustomerNumber, @PkDepartment
ELSE IF @EnterInvoiceSeriesLogic = 1
BEGIN
  DECLARE @CentralBookingType TINYINT

  IF @TableName='RESERVATIONIDENTITY' -- safety: make sure none of the taken referencenumbers are actually used
  BEGIN
    DECLARE @CentralOfficeSeperateResSeries BIT

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

    IF @CentralOfficeSeperateResSeries = 1
    BEGIN
      SELECT @CentralBookingType=KeyValue FROM dbo.HotelSetup WITH (NOLOCK) WHERE Upper (SearchKey)=Upper('CentralBookingType')
      IF IsNull (@CentralBookingType, 0) = 0
        SET @CentralBookingType = 0
    END
    ELSE
      SET @CentralBookingType = 0

    IF @CentralBookingType <> 7 OR @CentralOfficeSeperateResSeries = 0 -- NOT MPMS or NOT Seperate series
      SET @HotelCustomerNumber = -1
    ELSE
      DELETE FROM dbo.IdentityFields WHERE DBTableName=@TableName AND HotelCustomerNumber=-1
  END
  ELSE IF @TableName='CANCELLATIONIDENTITY'
  BEGIN
    DECLARE @CancellationNumberSeriesPerHotel BIT

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

    IF @CancellationNumberSeriesPerHotel = 1
    BEGIN
      SELECT @CentralBookingType=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE Upper (SearchKey)=Upper('CentralBookingType')
      IF IsNull (@CentralBookingType, 0) = 0
        SET @CentralBookingType = 0
    END
    ELSE
      SET @CentralBookingType = 0

    IF @CentralBookingType <> 7 OR @CancellationNumberSeriesPerHotel = 0 -- NOT MPMS or NOT Seperate series
      SET @HotelCustomerNumber = -1
    ELSE
      DELETE FROM dbo.IdentityFields WHERE DBTableName=@TableName AND HotelCustomerNumber=-1
  END
  ELSE IF @TableName='GIFTCERTIFICATEIDENTITY'
  BEGIN
    DECLARE @GiftCertificateNumberPerHotelPerHotel BIT

    SELECT @GiftCertificateNumberPerHotelPerHotel = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'GiftCertificateNumberPerHotel'
    SET @GiftCertificateNumberPerHotelPerHotel = ISNULL(@GiftCertificateNumberPerHotelPerHotel, 0)

    IF @GiftCertificateNumberPerHotelPerHotel = 0 -- NOT MPMS or NOT Seperate series
      SET @HotelCustomerNumber = -1
    ELSE
      DELETE FROM dbo.IdentityFields WHERE DBTableName=@TableName AND HotelCustomerNumber=-1
  END

  UPDATE dbo.IdentityFields SET @Identity = IdentityField = IdentityField + @Increase, ChangeDate = @AChangeDate 
  WHERE DBTableName=@TableName AND HotelCustomerNumber = @HotelCustomerNumber AND FkDepartment=-1

  set @RowCount = @@ROWCOUNT
  IF @RowCount = 0
  BEGIN
    IF @TableName='RESERVATIONIDENTITY' -- Only RESERVATIONIDENTITY because number series handling is not the same
    BEGIN
      IF @HotelCustomerNumber > -1
        SELECT @Identity = MAX(ReferenceNumber) FROM dbo.ReservationMain WITH (NOLOCK) WHERE HotelNumber=@HotelCustomerNumber
      ELSE
        SELECT @Identity = MAX(ReferenceNumber) FROM dbo.ReservationMain WITH (NOLOCK) 
    END
    ELSE IF @TableName='CANCELLATIONIDENTITY' -- Only CANCELLATIONIDENTITY because number series handling is not the same
    BEGIN
      IF @HotelCustomerNumber > -1
        SELECT @Identity = MAX(CancellationNumber) FROM dbo.ReservationMain WITH (NOLOCK) WHERE HotelNumber=@HotelCustomerNumber
      ELSE
        SELECT @Identity = MAX(CancellationNumber) FROM dbo.ReservationMain WITH (NOLOCK) 
    END

    IF (@TableName='RESERVATIONIDENTITY') or (@TableName='CANCELLATIONIDENTITY')
    BEGIN
      IF @Identity is NULL -- BA/20100208: CR012331/Changed from '= null' to 'is null'
        IF @HotelCustomerNumber > -1
          SET @Identity = 1 + (@HotelCustomerNumber * 1000000)
        ELSE
          SET @Identity = 1

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

      IF (SELECT COUNT(*) FROM IdentityFields WITH (NOLOCK) WHERE DBTableName = @TableName and HotelCustomerNumber = @HotelCustomerNumber) = 0 --nb20120213 Safety: Do not create duplicates!!!
        INSERT INTO dbo.IdentityFields (DBTableName, HotelCustomerNumber, IdentityField, ChangeDate) VALUES (@TableName, @HotelCustomerNumber, @Identity, @AChangeDate)
    END
  END

  DECLARE @ReservationCount INT
  IF @TableName='RESERVATIONIDENTITY' -- safety: make sure none of the taken referencenumbers are actually used
     SELECT @ReservationCount = COUNT(*) FROM dbo.ReservationMain WITH (NOLOCK) WHERE ReferenceNumber BETWEEN @Identity - @Increase + 1 AND @Identity
  ELSE IF @TableName='CANCELLATIONIDENTITY' -- safety: make sure none of the taken cancellationnumbers are actually used
     SELECT @ReservationCount = COUNT(*) FROM dbo.ReservationMain WITH (NOLOCK) WHERE CancellationNumber BETWEEN @Identity - @Increase + 1 AND @Identity

  IF @ReservationCount > 0  -- safety: make sure none of the taken numbers are actually used - recursivly called until hole is found
    EXEC dbo.GetNextNumber @TableName, @Increase, @Identity OUTPUT, @HotelCustomerNumber 
END
ELSE  -- - - - - - N O N   D O M I N O - - - - - -
BEGIN
  set @RowCount= 0
  IF @TableName = 'COURSECERTIFICATEIDENTITY'
  BEGIN 
    DECLARE @AChangeDateDT DATETIME
    SET @AChangeDateDT = CONVERT(DATETIME, @AChangeDate, 112)
    
    UPDATE dbo.IdentityFields SET @Identity = IdentityField = IdentityField + @Increase WHERE DBTableName = @TableName AND HotelCustomerNumber = @HotelCustomerNumber AND YEAR(ChangeDate) = YEAR(@AChangeDateDT)
    set @RowCount = @@rowcount
    
    IF @RowCount <= 0
      INSERT INTO dbo.IdentityFields (DBTableName, HotelCustomerNumber, IdentityField, FKDepartment, ChangeDate) VALUES ('COURSECERTIFICATEIDENTITY', -1, 0, -1, @AChangeDate)
  END
  ELSE IF @TableName = 'GLOBALTRANSACTIONIDENTITY'
  BEGIN
    INSERT INTO IdentityGlobalTransactionNumber (Field1) VALUES (@Increase)
    SET @Identity = @@IDENTITY
    IF @Increase > 1
    BEGIN
      SET @cnt = 1
      WHILE @cnt < @Increase
      BEGIN
        INSERT INTO IdentityGlobalTransactionNumber (Field1) VALUES (@Increase)
        SET @cnt = @cnt+1
      END
    END
  END
  ELSE IF @TableName = 'PRODUCTWARELINKIDENTITY'
  BEGIN
    INSERT INTO IdentityProductWareLinkNumber (Field1) VALUES (@Increase)
    SET @Identity = @@IDENTITY
    IF @Increase > 1
    BEGIN
      SET @cnt = 1
      WHILE @cnt < @Increase
      BEGIN
        INSERT INTO IdentityProductWareLinkNumber (Field1) VALUES (@Increase)
        SET @cnt = @cnt+1
      END
    END
  END
  ELSE IF @TableName = 'RESERVATIONROOMLINEIDENTITY'
  BEGIN
    INSERT INTO IdentityReservationRoomLineNumber (Field1) VALUES (@Increase)
    SET @Identity = @@IDENTITY
    IF @Increase > 1
    BEGIN
      SET @cnt = 1
      WHILE @cnt < @Increase
      BEGIN
        INSERT INTO IdentityReservationRoomLineNumber (Field1) VALUES (@Increase)
        SET @cnt = @cnt+1
      END
    END    
  END
  ELSE IF @TableName = 'RESERVATIONADDRESSIDENTITY'
  BEGIN
    INSERT INTO IdentityReservationAddressNumber (Field1) VALUES (@Increase)
    SET @Identity = @@IDENTITY
    IF @Increase > 1
    BEGIN
      SET @cnt = 1
      WHILE @cnt < @Increase
      BEGIN
        INSERT INTO IdentityReservationAddressNumber (Field1) VALUES (@Increase)
        SET @cnt = @cnt+1
      END
    END    
  END
  
  if @RowCount <= 0 AND @TableName <> 'GLOBALTRANSACTIONIDENTITY' AND @TableName <> 'PRODUCTWARELINKIDENTITY' AND @TableName <> 'RESERVATIONROOMLINEIDENTITY' AND @TableName <> 'RESERVATIONADDRESSIDENTITY'
  BEGIN
    IF @TableName = 'COURSECERTIFICATEIDENTITY'
      UPDATE dbo.IdentityFields SET @Identity = IdentityField = IdentityField + @Increase WHERE DBTableName = @TableName AND HotelCustomerNumber = @HotelCustomerNumber AND YEAR(ChangeDate) = YEAR(@AChangeDateDT)
    ELSE
      UPDATE dbo.IdentityFields SET @Identity = IdentityField = IdentityField + @Increase, ChangeDate = @AChangeDate WHERE DBTableName=@TableName
  END    
END

IF @TableName <> 'GLOBALTRANSACTIONIDENTITY' AND @TableName <> 'PRODUCTWARELINKIDENTITY' AND @TableName <> 'RESERVATIONROOMLINEIDENTITY'
  SET @Identity = @Identity - @Increase + 1

DECLARE @SqlStr VARCHAR(25)
SET @SqlStr = 'SET LOCK_TIMEOUT '+ CONVERT(VARCHAR, @LockTimeOut)
EXEC (@SqlStr)
