CREATE PROCEDURE [dbo].[GetIdentityInvoice] @TableName VARCHAR (30), @Increase INT, @Identity INT OUTPUT, @HotelCustomerNumber INT = -1, @PkDepartment INT = -1 AS
SET NOCOUNT ON
/*
  NB 20151207 Fix for Error checkout Bill Numer = -1
  NB 20150622 new CashIdentity type 
  KJ 20150528 Fix for "Error checkout. Bill number = -1"
  KJ 20150226 Fix for "Error checkout no bill number if running domino and not invoiceseries per hotel". Case #CR084242
  NB20150223 SepEcoByStaffDep single hotel in domino and on -1 rec Department fixed. 
  NB20141120 Rewritten and secured CR079559
  NB 20121030: Created

  Note: 
*/
DECLARE @RowCount INT, @PropertyCount INT
IF @TableName = 'CASHIDENTITY'
BEGIN
  UPDATE dbo.IdentityInvoice SET @Identity = IdentityField = IdentityField + @Increase 
  WHERE DBTableName=@TableName AND HotelCustomerNumber=@HotelCustomerNumber AND FkDepartment= @PkDepartment
  SET @RowCount = @@ROWCOUNT
  IF @RowCount = 0
  BEGIN
    SET @Identity = 1
    INSERT INTO dbo.IdentityInvoice (DBTableName, HotelCustomerNumber, IdentityField, FkDepartment) 
	VALUES (@TableName, @HotelCustomerNumber, @Identity, @PkDepartment)
  END
END
ELSE
BEGIN
  DECLARE @SepEcoByStaffDep BIT, @InvoiceSeries BIT, @EnterInvoiceSeriesLogic BIT

  IF (@TableName = 'BILLIDENTITY' OR @TableName = 'INVOICEIDENTITY')
    SET @EnterInvoiceSeriesLogic = 1
  ELSE
    SET @EnterInvoiceSeriesLogic = 0
  
  SELECT @SepEcoByStaffDep = isnull(KeyValue, '0') FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'SeparateeconomyBystaffDep'
  IF @EnterInvoiceSeriesLogic = 0 OR @SepEcoByStaffDep = 1 and @PkDepartment = -1 
    SET @SepEcoByStaffDep = 0
  SET @SepEcoByStaffDep = ISNULL(@SepEcoByStaffDep, 0)

  IF @EnterInvoiceSeriesLogic = 1
    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')
  ELSE
    SET @InvoiceSeries = 0
  SET @InvoiceSeries = ISNULL(@InvoiceSeries, 0)

  SELECT @PropertyCount = Count(*) FROM dbo.Customer WITH (NOLOCK) WHERE CustomerTypeNumber = 7 AND (LocalHotel=1 OR HotelNumber >= 0) AND ISNULL(MarkAsDeleted, 0) = 0
  SET @PropertyCount = ISNULL(@PropertyCount, 1)

  SET @Identity = -1

-- - - - - - Separate bill and invoice by department series - - - - - -
  IF (@SepEcoByStaffDep = 1) 
  BEGIN
    UPDATE dbo.IdentityInvoice SET @Identity = IdentityField = IdentityField + @Increase 
    WHERE DBTableName=@TableName AND HotelCustomerNumber=@HotelCustomerNumber AND FkDepartment= @PkDepartment
    SET @RowCount = @@ROWCOUNT
  END
  ELSE
    SET @RowCount= 0
  IF @RowCount = 0
  BEGIN
    DECLARE @Hcn1 INT, @Hcn2 INT
	IF @InvoiceSeries = 1
	BEGIN
	  SET @Hcn1 = @HotelCustomerNumber
	  SET @Hcn2 = -1
	END
	ELSE
	BEGIN
	  SET @Hcn1 = -1 
	  SET @Hcn2 = @HotelCustomerNumber
	END
    UPDATE dbo.IdentityInvoice SET @Identity = IdentityField = IdentityField + @Increase 
    WHERE DBTableName=@TableName AND HotelCustomerNumber = @Hcn1 
    SET @RowCount = @@ROWCOUNT
    IF @RowCount = 0 AND @Hcn1 <> @Hcn2
    BEGIN
      UPDATE dbo.IdentityInvoice SET @Identity = IdentityField = IdentityField + @Increase 
      WHERE DBTableName=@TableName AND HotelCustomerNumber = @Hcn2 
      SET @RowCount = @@ROWCOUNT
	END
  END
--ON Invoiceseries enabled Fix for HotelCustomerNumber = -1 on InvoiceSeries and identity records for each hotel exist then probability for localhotel is the one to be used most likely 
  IF @InvoiceSeries = 1 AND @RowCount=0
    IF (SELECT COUNT(*) FROM dbo.IdentityInvoice WITH (NOLOCK) WHERE DBTableName = @TableName AND HotelCustomerNumber > -1) = @PropertyCount 
    BEGIN
      UPDATE dbo.IdentityInvoice SET @Identity = IdentityField = IdentityField + @Increase 
      WHERE DBTableName=@TableName AND HotelCustomerNumber IN (
        SELECT TOP 1 II.HotelCustomerNumber FROM dbo.IdentityInvoice II WITH (NOLOCK) 
          INNER JOIN dbo.Customer C WITH (NOLOCK) ON II.HotelCustomerNumber = C.CustomerNumber
        WHERE DBTableName=@TableName AND C.LocalHotel=1 ORDER BY II.IdentityField DESC)
      SET @RowCount = @@ROWCOUNT
    END

  IF @RowCount>0
    SET @Identity = @Identity - @Increase + 1
/*
PRINT '@HCN: '+ CONVERT(VARCHAR, @HotelCustomerNumber)
PRINT '@HCN1: '+ CONVERT(VARCHAR, @Hcn1)
PRINT '@HCN2: '+ CONVERT(VARCHAR, @Hcn2)
PRINT '@EnterInvoiceSeriesLogic: '+ CONVERT(VARCHAR, @EnterInvoiceSeriesLogic)
PRINT '@InvoiceSeries: '+ CONVERT(VARCHAR, @InvoiceSeries)
PRINT '@PropertyCount: '+ CONVERT(VARCHAR, @PropertyCount)
PRINT '@SepEcoByStaffDep: '+ CONVERT(VARCHAR, @SepEcoByStaffDep)
PRINT ': '+ CONVERT(VARCHAR, @RowCount)
*/
END