CREATE PROCEDURE dbo.CreateReceipt @PkInvoice INT OUTPUT, @FkFolio INT, @FkUser INT = NULL, @FkDepartment INT = NULL, @IdentityKind INT = 1 AS
SET NOCOUNT ON
/*
nb20240626 New  
Created by Nenad Blagojevic juni 2024
Comments:
@FkFolio INT, --Required
@FkUser INT,   --Optional
@FkDepartment INT --Optional
@IdentityKind INT --Optional
0-Prebill-not supported
1-Receipt-hardcoded
2-Invoice-not supported
(ReceiptNumber from number series controlled by @IdentityKind:
0-Proforma number serie value
1-Bill number serie value
2-Invoice number serie value)
@PkInvoice > 0 --Ordinary value
@PkInvoice = 0 --not created
@PkInvoice = -1 --error code: folio not balanced
@PkInvoice = -2 --error code: turnover accounts (behind) not balanced
*/
DECLARE @DebugLevel TINYINT
SET @DebugLevel = 0
IF @DebugLevel >= 1 
BEGIN
  PRINT '---- CreateReceipt Pre ---'
END

DECLARE
  @DoContinue BIT,
  @Hcn INT,
  @Department INT,
  @User INT,
  @Layout INT,
  @Country_Localization INT
--Pre--------------------------
SET @PkInvoice = 0
SET @DoContinue = 1
IF NOT EXISTS(
  SELECT TransactionNumber FROM Transactions WITH (NOLOCK) 
  WHERE FolioNumber = @FkFolio AND ShowOnBillFlag = 1 and MovedDeleted = 0
  EXCEPT
  SELECT TR.TransactionNumber FROM Transactions TR WITH (NOLOCK)
    INNER JOIN InvoiceTransactionXtab ITX WITH (NOLOCK) ON TR.TransactionNumber = ITX.FkTransaction
    INNER JOIN Invoice I WITH (NOLOCK) ON ITX.FkInvoice = I.PkInvoice
  WHERE TR.FolioNumber = @FkFolio and I.CancelDate IS NULL)
  SET @DoContinue = 0
IF @DoContinue = 1
BEGIN
  SELECT @Hcn = HotelCustomerNumber, @Layout = BillTypeNumber FROM Folios WHERE FolioNumber = @FkFolio --Get HotelCustomerNumber
  IF ISNULL(@Hcn, 0) <= 0
  BEGIN
    SET @DoContinue = 0
    RAISERROR('Error in storedprocedure <<CreateReceipt>>: @Hcn not valid.', 15, 1)
  END
  ELSE
  BEGIN
    SET @Layout = ISNULL(@Layout, 0)
	SELECT @Country_Localization = CountryNumber FROM Customer WHERE CustomerNumber = @Hcn
	SET @Country_Localization = ISNULL(@Country_Localization, 0)
  END
END
SET @Department = @FkDepartment --Get Department
IF @DoContinue = 1 AND ISNULL(@Department, 0) <= 0
BEGIN
  SELECT @Department = dbo.GetHotelSetupKeyValue('PicassoOnlinePosteringDepartment', @Hcn)
  IF ISNULL(@Department, 0) <= 0
  BEGIN
    SET @DoContinue = 0
    RAISERROR('Error in storedprocedure <<CreateReceipt>>: @FkDepartment not valid.', 15, 1)
  END
END
SET @User = @FkUser --Get UserIdNumber
IF @DoContinue = 1 AND ISNULL(@User, 0) <= 0
BEGIN
  SELECT @User = Keyvalue FROM HotelSetup WITH (NOLOCK) WHERE Searchkey = 'PicassoOnlinePosteringUserIdNumber'
  IF @User <= 0
    SELECT TOP 1 @User = UserIDNumber FROM Personel WITH (NOLOCK)
  IF @User <= 0
  BEGIN
    SET @DoContinue = 0
    RAISERROR('Error in storedprocedure <<CreateReceipt>>: @FkUser not valid.', 15, 1)
  END
END
IF @DoContinue = 1 AND NOT EXISTS ( --unprinted transactions exists
  SELECT TransactionNumber FROM Transactions WITH (NOLOCK) 
  WHERE FolioNumber = @FkFolio AND ShowOnBillFlag = 1 and MovedDeleted = 0
  EXCEPT
  SELECT TR.TransactionNumber FROM Transactions TR WITH (NOLOCK)
    INNER JOIN InvoiceTransactionXtab ITX WITH (NOLOCK) ON TR.TransactionNumber = ITX.FkTransaction
    INNER JOIN Invoice I WITH (NOLOCK) ON ITX.FkInvoice = I.PkInvoice
  WHERE TR.FolioNumber = @FkFolio and I.CancelDate IS NULL) 
  SET @DoContinue = 0
IF @DoContinue = 1 --in balance check
BEGIN
  DECLARE @Balance MONEY, @Result MONEY, @Turnover MONEY
  SELECT @Balance = dbo.GetFolioBalance(@FkFolio)
  IF @Balance = 0
  BEGIN
    SELECT @Turnover = ISNULL(Price, 0) + ISNULL(Tax, 0), @Result = dbo.GetTotalFrontLegFolio(@FkFolio, 0) FROM GetBalanceReceipt(@FkFolio, 0)
    IF @Turnover <> @Result 
      SET @PkInvoice = -2 --Turnover accounts (behind) not in balance 
  END
  ELSE 
    SET @PkInvoice = -1 --Not in balance
  IF @PkInvoice < 0 --Use it for now as error kode, negative values are error codes
    SET @DoContinue = 0
END
------------Exec  
IF @DebugLevel >= 1 
BEGIN
  PRINT '@DoContinue = ' + CONVERT(NVARCHAR, ISNULL(@DoContinue, 0))
  PRINT '@Balance = ' + CONVERT(NVARCHAR, ISNULL(@Balance, 0))
  PRINT '@Turnover = ' + CONVERT(NVARCHAR, ISNULL(@Turnover, 0))
  PRINT '@PkInvoice = ' + CONVERT(NVARCHAR, ISNULL(@PkInvoice, 0))
END

IF @DoContinue = 1 AND @IdentityKind = 1 --only receipts supported, todo invoices and proforma
BEGIN
  DECLARE --local var 
  @SystemDate DATETIME,
  @ReceiptId INT
  EXEC SystemDateTime '', @SystemDate OUTPUT --Get SystemDate   
  --Insert new Bill
  IF @IdentityKind = 1
    EXEC GetNextNumber 'BILLIDENTITY', 1, @ReceiptId OUTPUT, @Hcn
  INSERT INTO Invoice (BillNumber, FkStaff, CreatedDate, PrintDate, FkCustomerHotel, FkDepartment, ReceiptType, ReceiptNumber, FkBillLayout, FkCountry) 
  VALUES (@ReceiptId, @User, @SystemDate, @SystemDate, @Hcn, @Department, @IdentityKind, @ReceiptId, @Layout, @Country_Localization)
  SET @PkInvoice = @@IDENTITY
  --Insert transaction relation
  INSERT INTO InvoiceTransactionXTab (FkInvoice, FkTransaction) 
    SELECT @PkInvoice, TransactionNumber FROM Transactions WITH (NOLOCK) 
	WHERE FolioNumber = @FkFolio AND ShowOnBillFlag = 1 and MovedDeleted = 0
	EXCEPT
    SELECT @PkInvoice, TR.TransactionNumber FROM Transactions TR WITH (NOLOCK)
    INNER JOIN InvoiceTransactionXtab ITX WITH (NOLOCK) ON TR.TransactionNumber = ITX.FkTransaction
    INNER JOIN Invoice I WITH (NOLOCK) ON ITX.FkInvoice = I.PkInvoice
    WHERE TR.FolioNumber = @FkFolio and I.CancelDate IS NULL
END  
------------Post

