IF EXISTS (
  SELECT DISTINCT SysColumns.Name 
  FROM SysColumns WITH (NOLOCK) 
  INNER JOIN SysObjects WITH (NOLOCK) ON SysColumns.ID = SysObjects.ID 
  WHERE SysObjects.Name = 'IdentityFields' AND SysColumns.Name = 'TableName'
)
EXEC sp_rename 'IdentityFields.TableName', 'DBTableName', 'COLUMN'
GO

ALTER PROCEDURE dbo.GetNextNumber @TableName VARCHAR (30), @Increase INT, @Identity INT OUTPUT, @HotelCustomerNumber INT = -1, @PkDepartment INT = -1 AS
SET NOCOUNT ON
/*
  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

--SET @TableName = UPPER(LTRIM(RTRIM(@TableName)))
--If ((@HotelCustomerNumber > -1) AND (@TableName = 'BILLIDENTIT' OR @TableName = 'INVOICEIDENTITY' OR @TableName='RESERVATIONIDENTITY' OR @TableName='CANCELLATIONIDENTITY')) -- - - - - - D O M I N O - - - - - -
--nb20070824 Ext -- - - - - - Separate bill and invoice by department series - - - - - -
declare @SepEcoByStaffDep bit,
  @EnterInvoiceSeriesLogic bit,
  @InvoiceSeries bit
select @SepEcoByStaffDep = isnull(KeyValue, '0') from HotelSetup WITH (nolock) where SearchKey = 'SeparateeconomyBystaffDep'
if @SepEcoByStaffDep = 1 and @PkDepartment = -1 
  SET @SepEcoByStaffDep = 0
if (@HotelCustomerNumber > -1) AND (@TableName = 'BILLIDENTITY' OR @TableName = 'INVOICEIDENTITY' OR @TableName='RESERVATIONIDENTITY' OR @TableName='CANCELLATIONIDENTITY') -- - - - - - D O M I N O - - - - - -
  set @EnterInvoiceSeriesLogic = 1
else if (@SepEcoByStaffDep = 1) AND (@TableName = 'BILLIDENTITY' OR @TableName = 'INVOICEIDENTITY') -- - - - - - Separate department series - - - - - -
  set @EnterInvoiceSeriesLogic = 1
else
  set @EnterInvoiceSeriesLogic = 0
if (@TableName = 'BILLIDENTITY' or @TableName = 'INVOICEIDENTITY') 
select @InvoiceSeries = isnull(isnull(v.KeyValue, h.KeyValue), 0) from dbo.HotelSetup h WITH (nolock) 
  left join dbo.HotelSetupValueXTab vx WITH (nolock) on vx.HotelSetupNumber = h.HotelSetupNumber
  left join dbo.HotelSetupValue v WITH (nolock) on vx.fk_ValueNumber = v.Pk_ValueNumber
where upper (h.SearchKey)=upper('UseInvoiceSeries')
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

--  IF NOT (@TableName = 'BILLIDENTITY' OR @TableName = 'INVOICEIDENTITY') OR (SELECT ISNULL(KeyValue, 0) FROM HotelSetup WITH (NOLOCK) WHERE Upper (SearchKey)=Upper('UseInvoiceSeries') )= 1
--    begin
--      UPDATE IdentityFields SET @Identity = IdentityField = IdentityField + @Increase WHERE TableName=@TableName AND HotelCustomerNumber=@HotelCustomerNumber
--    SET @RowCount = @@ROWCOUNT
--nb20070824 Ext -- - - - - - Separate bill and invoice by department series - - - - - -
  if @SepEcoByStaffDep = 1 or NOT (@TableName = 'BILLIDENTITY' OR @TableName = 'INVOICEIDENTITY') OR @InvoiceSeries = 1
  begin
    if (@SepEcoByStaffDep = 1) 
    begin
      update  IdentityFields SET @Identity = IdentityField = IdentityField + @Increase WHERE DBTableName=@TableName AND HotelCustomerNumber=@HotelCustomerNumber AND FkDepartment= @PkDepartment
      set @RowCount = @@ROWCOUNT
    end
    else
      set @RowCount= 0
    if @SepEcoByStaffDep = 0 or @RowCount = 0
    begin
      update dbo.IdentityFields SET @Identity = IdentityField = IdentityField + @Increase WHERE DBTableName=@TableName  AND HotelCustomerNumber = @HotelCustomerNumber AND FkDepartment=-1
      set @RowCount = @@ROWCOUNT
    end

    IF  (@TableName = 'BILLIDENTITY' OR @TableName = 'INVOICEIDENTITY') AND @RowCount=0
      IF (SELECT COUNT(*) FROM IdentityFields WITH (NOLOCK) WHERE DBTableName = @TableName) = 1 AND (SELECT HotelCustomerNumber FROM IdentityFields WITH (NOLOCK) WHERE DBTableName = @TableName) = -1
      BEGIN
        UPDATE IdentityFields SET @Identity = IdentityField = IdentityField + @Increase WHERE DBTableName=@TableName
        SET @RowCount = @@ROWCOUNT
      END

  END
  ELSE IF (@TableName = 'BILLIDENTITY' OR @TableName = 'INVOICEIDENTITY') 
  BEGIN
    UPDATE IdentityFields SET @Identity = IdentityField = IdentityField + @Increase WHERE DBTableName=@TableName  AND HotelCustomerNumber IN (
      SELECT TOP 1 HotelCustomerNumber FROM IdentityFields WITH (NOLOCK) WHERE DBTableName=@TableName ORDER BY IdentityField DESC)
    SET @RowCount = @@ROWCOUNT
  END

  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 = 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

      INSERT INTO dbo.IdentityFields (DBTableName, HotelCustomerNumber, IdentityField) VALUES (@TableName, @HotelCustomerNumber, @Identity)
    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
  --IF (@TableName = 'BILLIDENTITY' OR @TableName = 'INVOICEIDENTITY') AND @InvoiceSeries = 1
  --  UPDATE dbo.IdentityFields SET @Identity = IdentityField = IdentityField + @Increase WHERE TableName=@TableName  AND HotelCustomerNumber = @HotelCustomerNumber
--nb20070824 Ext -- - - - - - Separate bill and invoice by department series - - - - - -
--nb20070910 Bug - handling of invoices in domino with none invoice series (neither by hotel or department) 
  set @RowCount= 0
  if (@TableName = 'BILLIDENTITY' OR @TableName = 'INVOICEIDENTITY') 
  begin
    if @SepEcoByStaffDep = 1
    begin
      update dbo.IdentityFields SET @Identity = IdentityField = IdentityField + @Increase WHERE DBTableName=@TableName  AND HotelCustomerNumber = @HotelCustomerNumber AND FkDepartment= @PkDepartment
      set @RowCount = @@rowcount
    end
    if @SepEcoByStaffDep = 0 or @RowCount <= 0
    begin
        update dbo.IdentityFields SET @Identity = IdentityField = IdentityField + @Increase WHERE DBTableName=@TableName  AND HotelCustomerNumber = @HotelCustomerNumber
        set @RowCount = @@rowcount
    end
  end
  if @RowCount <= 0
    UPDATE dbo.IdentityFields SET @Identity = IdentityField = IdentityField + @Increase WHERE DBTableName=@TableName
END
SET @Identity = @Identity - @Increase + 1
--nb20061019 Preventing Lock time outs
DECLARE @SqlStr VARCHAR(25)
SET @SqlStr = 'SET LOCK_TIMEOUT '+ CONVERT(VARCHAR, @LockTimeOut)
EXEC (@SqlStr)
GO
IF NOT EXISTS   INSERT INTO ScriptsOnce VALUES ('IdentityFields.TableName', GETDATE())
