CREATE PROCEDURE dbo.PosteringTransferAsWare @GlobalTransactionNumber INT, @RealDateTimeForTransaction DATETIME, @PaxAdult INT, @PaxChild1 INT, @PaxChild2 INT, @UserIDNumber INT, @FolioNumber INT, @Pieces INT, @WarePrice MONEY, @MyCountryNumber INT, @WareNumber INT, @WareText VARCHAR(100), @BalanceAccountNumber INT, 
  @FK_DepartmentNumber INT, @Note VARCHAR(50), @PostForDatetime DATETIME, @ExchangeRate FLOAT, @ProductWareLinkNumber INT, @GuestNumber INT, @HotelCustomerNumber INT, @ChargedWithCard BIT, @AccountType TINYINT, @OriginalWareText VARCHAR(100), @TransferFromRoomNumber INT, @TransferFromTableNumber INT, @ReferenceNumber INT,
  @TransferedFromLastName VARCHAR(50), @RoomNumber INT, @TransferToFolioNumber INT, @TransferToGuestNumber INT, @PaymentBalanceAccountNumber INT, @TransferToHotelCustomerNumber INT, @NegativeTransfer BIT = 0, @ACashDesk INT = -1 AS
SET NOCOUNT ON

/*

  Created by Kent Johansen, November 2006

  Changes:
    KJ 20181115: Bug: Fix for "Post using wrong GlobalTransactionNumber". Case #CR156101
    KJ 20180927: Bug: Fix for "Relation not saved"
    KJ 20180904: Ext: Added relation between transactions.
    KJ 20170911: Bug: Fix for "Wrong VAT when multiple prebills has been made". Case #CR132172
    KJ 20170508: Bug: Fix for "Error transfer when prebill/statement has been made".
    KJ 20140611: Ext: CashDesk per hotel. Case #CR073269
    KJ 20110512: Bug: Fix for "Payment is still not correct".
    KJ 20091208: Ext: Added CashDesk
    KJ 20090904: Bug: Fix for "Wrong warenumber on showonbill transaction on to folio"
    KJ 20090519: Bug: Post wares and vat with original warenumber and Text
    KJ 20081222: Ext: Added OriginalGlobalTransactionNumber
    KJ 20081128: Bug: Fix for "Transter post never posted"
    KJ 20081127: Bug: Fix for "don't clear payment - use the one from the original transaction"
    KJ 20081008: Bug: Fix for "Wrong post when transfer amount is negative"
    KJ 20080926: Bug: Fix for "Wrong post difference"
    KJ 20080825: Bug: Fix for "Wrong post difference"
    KJ 20071019: Fix for "Vat vrong"-again
    KJ 20070417: Bug: Fix for "Handle transfer amount different of amount of transactions"
    KJ 20070410: Department rewrite
    KJ 20070221: Fix for "Vat vrong"
    KJ 20070123: Handle negative transfer
    KJ 20070111: Changed accountnumber for ware-vat on to-folio 

*/

DECLARE @ShowPrint BIT
SET @ShowPrint = 0

IF @ShowPrint = 1
BEGIN
  PRINT @GlobalTransactionNumber
  PRINT @RealDateTimeForTransaction
  PRINT @PaxAdult
  PRINT @PaxChild1
  PRINT @PaxChild2
  PRINT @UserIDNumber
  PRINT @FolioNumber
  PRINT @Pieces
  PRINT CONVERT(VARCHAR(10), @WarePrice)
  PRINT @MyCountryNumber
  PRINT @WareNumber 
  PRINT @WareText
  PRINT @BalanceAccountNumber
  PRINT @FK_DepartmentNumber
  PRINT @Note
  PRINT @PostForDatetime
  PRINT @ExchangeRate
  PRINT @ProductWareLinkNumber
  PRINT @GuestNumber
  PRINT @HotelCustomerNumber
  PRINT @ChargedWithCard
  PRINT @AccountType
  PRINT @OriginalWareText
  PRINT @TransferFromRoomNumber
  PRINT @TransferFromTableNumber
  PRINT @ReferenceNumber
  PRINT @TransferedFromLastName
  PRINT @RoomNumber
  PRINT @TransferToFolioNumber
  PRINT @TransferToGuestNumber
  PRINT @PaymentBalanceAccountNumber
  PRINT @TransferToHotelCustomerNumber
END

DECLARE @TransferPriceOverwrite FLOAT
DECLARE @TotalTransferPriceOverwrite FLOAT
DECLARE @TransferAccountNumber INT
DECLARE @TransferVatValue MONEY
DECLARE @TransferAccountType INT
DECLARE @Debit BIT
DECLARE @Payment INT
DECLARE @IsDebit BIT
DECLARE @TransferWareNumber INT
DECLARE @TransNumber INT
DECLARE @TransferPieces INT
DECLARE @s VARCHAR(500)

DECLARE @TransactionNumberFromFolio INT
DECLARE @TransactionNumberToFolio INT

SET @TransactionNumberFromFolio = NULL
SET @TransactionNumberToFolio = NULL

SET @TotalTransferPriceOverwrite = 0

DECLARE @UseCashDesk BIT
DECLARE @LastTransactionNumber INT

--SELECT @UseCashDesk = CONVERT(BIT, KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE Searchkey = 'FiscalPrinterSweden'
SELECT @UseCashDesk = dbo.GetHotelSetupKeyValue ('FiscalPrinterSweden', @HotelCustomerNumber)
SET @UseCashDesk = ISNULL(@UseCashDesk,0)

-- From folio
-- credit post
IF @NegativeTransfer = 1
  SET @IsDebit = 1
ELSE
  SET @IsDebit = 0
INSERT INTO Transactions (ProductNumber, PackageNumber, GlobalTransactionNumber, OriginalGlobalTransactionNumber, RealDateTimeForTransaction, PAXadult, PAXChild1, PAXChild2, DateStamp, UserIDNumber, FolioNumber, Pieces, PriceOverwrite, CurrencyNumber, ShowOnBillFlag, Debit, MovedDeleted, WareNumber, TextOverwrite, Payment, AccountNumber, VATValue, FK_DepartmentNumber, Note, OriginalDate, ExchangeRateOverwrite, ProductWareLinkNumber, GuestNumber, AutoReceivedPayment, HotelCustomerNumber, ChargedWithCard)
VALUES(0, 0, @GlobalTransactionNumber, @GlobalTransactionNumber, @RealDateTimeForTransaction, @PaxAdult, @PaxChild1, @PaxChild2, @RealDateTimeForTransaction, @UserIDNumber, @FolioNumber, @Pieces, @WarePrice, @MyCountryNumber, 1, @IsDebit, 0, @WareNumber, @WareText, 0, @BalanceAccountNumber, 0, @FK_DepartmentNumber, @Note, @PostForDatetime, @ExchangeRate, @ProductWareLinkNumber, @GuestNumber, 0, @HotelCustomerNumber, @ChargedWithCard)

SELECT @LastTransactionNumber = @@Identity
IF @UseCashDesk = 1
  EXEC TransactionsRelationInsert @LastTransactionNumber, @ACashDesk, @UserIDNumber
SET @TransactionNumberFromFolio = @LastTransactionNumber

-- vat transactions
DECLARE TransferVatTrans1 CURSOR READ_ONLY SCROLL FOR
  SELECT DISTINCT Transactions.TransactionNumber, Transactions.PriceOverwrite, Transactions.AccountNumber, Transactions.VatValue, Accounts.AccountType, Debit, Transactions.Payment, 
  Transactions.TextOverwrite, Transactions.WareNumber, Transactions.Pieces
  FROM Transactions WITH (NOLOCK)
  INNER JOIN Accounts WITH (NOLOCK) ON Accounts.AccountNumber = Transactions.AccountNumber AND Accounts.AccountType IN (2,3,4)
  LEFT JOIN dbo.InvoiceTransactionXtab ITX WITH (NOLOCK) ON ITX.FkTransaction = Transactions.TransactionNumber
  LEFT JOIN dbo.Invoice I WITH (NOLOCK) ON I.PkInvoice = ITX.FkInvoice 
  WHERE Transactions.FolioNumber = @FolioNumber
  AND Transactions.MovedDeleted = 0
  AND (I.PkInvoice IS NULL OR NOT I.CancelDate IS NULL OR I.ReceiptType = 0)
  AND Transactions.PriceOverwrite > 0
OPEN TransferVatTrans1

FETCH NEXT FROM TransferVatTrans1 INTO @TransNumber, @TransferPriceOverwrite, @TransferAccountNumber, @TransferVatValue, @TransferAccountType, @Debit, @Payment, @WareText, @TransferWareNumber, @TransferPieces

WHILE @@FETCH_STATUS = 0
BEGIN
  IF @Debit = 1
  BEGIN
    SET @IsDebit = 0
    SET @TotalTransferPriceOverwrite = @TotalTransferPriceOverwrite - @TransferPriceOverwrite
  END
  ELSE
  BEGIN
    SET @IsDebit = 1
    SET @TotalTransferPriceOverwrite = @TotalTransferPriceOverwrite + @TransferPriceOverwrite
  END

  -- Debit vatvalue
  IF @AccountType = 2
  BEGIN 
    -- Debit VAT
    INSERT INTO Transactions (ProductNumber, PackageNumber, GlobalTransactionNumber, OriginalGlobalTransactionNumber, RealDateTimeForTransaction, PAXadult, PAXChild1, PAXChild2, DateStamp, UserIDNumber, FolioNumber, Pieces, PriceOverwrite, CurrencyNumber, ShowOnBillFlag, Debit, MovedDeleted, WareNumber, TextOverwrite, Payment, AccountNumber, VATValue, /*DepartmentOverwrite*/ FK_DepartmentNumber, Note, OriginalDate,ExchangeRateOverwrite,ProductWareLinkNumber,GuestNumber, AutoReceivedPayment, HotelCustomerNumber, ChargedWithCard)
    VALUES(0, 0, @GlobalTransactionNumber, @GlobalTransactionNumber, @RealDateTimeForTransaction, @PaxAdult, @PaxChild1, @PaxChild2, @RealDateTimeForTransaction, @UserIDNumber, @FolioNumber, @TransferPieces, @TransferPriceOverwrite, @MyCountryNumber, 0, @IsDebit, 0, @TransferWareNumber, @WareText, @Payment, @TransferAccountNumber, @TransferVatValue, /*@Department*/ @FK_DepartmentNumber, @Note, @PostForDatetime, @ExchangeRate, @ProductWareLinkNumber, @GuestNumber, 0, @HotelCustomerNumber, @ChargedWithCard)
    IF @UseCashDesk = 1
    BEGIN
      SELECT @LastTransactionNumber = @@Identity
      EXEC TransactionsRelationInsert @LastTransactionNumber, @ACashDesk, @UserIDNumber
    END
  END
  ELSE
  BEGIN
    -- Debit ware-vat
    INSERT INTO Transactions (ProductNumber, PackageNumber, GlobalTransactionNumber, OriginalGlobalTransactionNumber, RealDateTimeForTransaction, PAXadult, PAXChild1, PAXChild2, DateStamp, UserIDNumber, FolioNumber, Pieces, PriceOverwrite, CurrencyNumber, ShowOnBillFlag, Debit, MovedDeleted, WareNumber, TextOverwrite, Payment, AccountNumber, VATValue, FK_DepartmentNumber, Note, OriginalDate,ExchangeRateOverwrite,ProductWareLinkNumber,GuestNumber, AutoReceivedPayment, HotelCustomerNumber, ChargedWithCard)
    VALUES(0, 0, @GlobalTransactionNumber, @GlobalTransactionNumber, @RealDateTimeForTransaction, @PaxAdult, @PaxChild1, @PaxChild2, @RealDateTimeForTransaction, @UserIDNumber, @FolioNumber, @TransferPieces, @TransferPriceOverwrite, @MyCountryNumber, 0, @IsDebit, 0, @TransferWareNumber, @WareText, @Payment, @TransferAccountNumber, @TransferVatValue, @FK_DepartmentNumber, @Note, @PostForDatetime, @ExchangeRate, @ProductWareLinkNumber, @GuestNumber, 0, @HotelCustomerNumber, @ChargedWithCard)
    IF @UseCashDesk = 1
    BEGIN
      SELECT @LastTransactionNumber = @@Identity
      EXEC TransactionsRelationInsert @LastTransactionNumber, @ACashDesk, @UserIDNumber
    END
  END 

  FETCH NEXT FROM TransferVatTrans1 INTO @TransNumber, @TransferPriceOverwrite, @TransferAccountNumber, @TransferVatValue, @TransferAccountType, @Debit, @Payment, @WareText, @TransferWareNumber, @TransferPieces
END

IF @TotalTransferPriceOverwrite < 0 and @WarePrice > 0
  SET @TotalTransferPriceOverwrite = @TotalTransferPriceOverwrite * -1

IF CAST(@WarePrice AS MONEY) <> CAST(@TotalTransferPriceOverwrite AS MONEY)
BEGIN 
  -- Transfer amount is not the same as total amount on transactions
  IF @ShowPrint = 1
  BEGIN
    PRINT '@TotalTransferPriceOverwrite'
    PRINT @TotalTransferPriceOverwrite
  END 

  SET @s = 'Error in storedprocedure <<PosteringTransferAsWare>>: Ware amount and post amount diff. WarePrice: ' + CAST(@WarePrice AS VARCHAR) + ' @TotalTransferPriceOverwrite=' + CAST(@TotalTransferPriceOverwrite AS VARCHAR)
  RAISERROR(@s,15,1)
END

SET @WareText = @OriginalWareText
if (@TransferFromRoomNumber>0) or (@TransferFromTableNumber>0)
begin
  if (@TransferFromRoomNumber>0)
    SET @WareText = @WareText + ' from room ' + CONVERT(VARCHAR, @TransferFromRoomNumber)
  else if (@TransferFromTableNumber>0)
    SET @WareText = @WareText + ' table ' + CONVERT(VARCHAR, @TransferFromTableNumber) + ' (' + CONVERT(VARCHAR, @FolioNumber) + ')'
end
else if @ReferenceNumber>0
begin
  if @RoomNumber>0
    SET @WareText = @WareText + ' to room ' + CONVERT(VARCHAR, @RoomNumber)
  else
    SET @WareText = @WareText + ' to ref. ' + CONVERT(VARCHAR, @ReferenceNumber)+ '/ ' + CONVERT(VARCHAR, @GuestNumber)
  if @TransferedFromLastName<>''
    SET @WareText = @WareText +  ', ' + @TransferedFromLastName
end
else
if ((@FolioNumber>0) AND (@ReferenceNumber=0 ))
begin
  SET @WareText = @WareText + ' from folio ' + CONVERT(VARCHAR, @FolioNumber)
  if @TransferedFromLastName<>''
    SET @WareText = @WareText +  ', '  + @TransferedFromLastName
end

if @TransferToFolioNumber<=0  SET @TransferToFolioNumber = @FolioNumber
-- Debiter vare incl moms på balancekonto
--SET @GlobalTransactionNumber=@GlobalTransactionNumber+1           
  EXEC GetNextNumber 'GLOBALTRANSACTIONIDENTITY', 1, @GlobalTransactionNumber OUTPUT

-- To folio
--Debit post
IF @NegativeTransfer = 1
  SET @IsDebit = 0
ELSE
  SET @IsDebit = 1
INSERT INTO Transactions (ProductNumber, PackageNumber, GlobalTransactionNumber, OriginalGlobalTransactionNumber, RealDateTimeForTransaction, PAXadult, PAXChild1, PAXChild2, DateStamp, UserIDNumber, FolioNumber, Pieces, PriceOverwrite, CurrencyNumber, ShowOnBillFlag, Debit, MovedDeleted, WareNumber, TextOverwrite, Payment, AccountNumber, VATValue, FK_DepartmentNumber, Note, OriginalDate,ExchangeRateOverwrite,ProductWareLinkNumber,GuestNumber, AutoReceivedPayment, HotelCustomerNumber, ChargedWithCard)
VALUES(0, 0, @GlobalTransactionNumber, @GlobalTransactionNumber, @RealDateTimeForTransaction, @PaxAdult, @PaxChild1, @PaxChild2, @RealDateTimeForTransaction, @UserIDNumber, @TransferToFolioNumber, @Pieces, @WarePrice, @MyCountryNumber, 1, @IsDebit, 0,@WareNumber, @WareText, 0, @BalanceAccountNumber, 0, @FK_DepartmentNumber, @Note, @PostForDatetime, @ExchangeRate, @ProductWareLinkNumber, @TransferToGuestNumber, 0, @TransferToHotelCustomerNumber, @ChargedWithCard)

SELECT @LastTransactionNumber = @@Identity
IF @UseCashDesk = 1
  EXEC TransactionsRelationInsert @LastTransactionNumber, @ACashDesk, @UserIDNumber
SET @TransactionNumberToFolio = @LastTransactionNumber

FETCH FIRST FROM TransferVatTrans1 INTO @TransNumber, @TransferPriceOverwrite, @TransferAccountNumber, @TransferVatValue, @TransferAccountType, @Debit, @Payment, @WareText, @TransferWareNumber, @TransferPieces

WHILE @@FETCH_STATUS = 0
BEGIN
  IF @TransferAccountType = 2
  BEGIN
    -- Credit vat
    INSERT INTO Transactions (ProductNumber, PackageNumber, GlobalTransactionNumber, OriginalGlobalTransactionNumber, RealDateTimeForTransaction, PAXadult, PAXChild1, PAXChild2, DateStamp, UserIDNumber, FolioNumber, Pieces, PriceOverwrite, CurrencyNumber, ShowOnBillFlag, Debit, MovedDeleted, WareNumber, TextOverwrite, Payment, AccountNumber, VATValue, FK_DepartmentNumber, Note, OriginalDate,ExchangeRateOverwrite,ProductWareLinkNumber,GuestNumber, AutoReceivedPayment, HotelCustomerNumber, ChargedWithCard)
    VALUES(0, 0, @GlobalTransactionNumber, @GlobalTransactionNumber, @RealDateTimeForTransaction, @PaxAdult, @PaxChild1, @PaxChild2, @RealDateTimeForTransaction, @UserIDNumber, @TransferToFolioNumber, @TransferPieces, @TransferPriceOverwrite, @MyCountryNumber, 0, @Debit, 0,@TransferWareNumber, @WareText, @Payment, @TransferAccountNumber, @TransferVatValue, @FK_DepartmentNumber, @Note, @PostForDatetime, @ExchangeRate, @ProductWareLinkNumber, @TransferToGuestNumber, 0, @TransferToHotelCustomerNumber, @ChargedWithCard)
    IF @UseCashDesk = 1
    BEGIN
      SELECT @LastTransactionNumber = @@Identity
      EXEC TransactionsRelationInsert @LastTransactionNumber, @ACashDesk, @UserIDNumber
    END
  END
  ELSE
  BEGIN 
    --Credit ware-vat
    INSERT INTO Transactions (ProductNumber, PackageNumber, GlobalTransactionNumber, OriginalGlobalTransactionNumber, RealDateTimeForTransaction, PAXadult, PAXChild1, PAXChild2, DateStamp, UserIDNumber, FolioNumber, Pieces, PriceOverwrite, CurrencyNumber, ShowOnBillFlag, Debit, MovedDeleted, WareNumber, TextOverwrite, Payment, AccountNumber, VATValue, FK_DepartmentNumber, Note, OriginalDate,ExchangeRateOverwrite,ProductWareLinkNumber,GuestNumber, AutoReceivedPayment, HotelCustomerNumber, ChargedWithCard)
    VALUES(0, 0, @GlobalTransactionNumber, @GlobalTransactionNumber, @RealDateTimeForTransaction, @PaxAdult, @PaxChild1, @PaxChild2, @RealDateTimeForTransaction, @UserIDNumber, @TransferToFolioNumber, @TransferPieces, @TransferPriceOverwrite, @MyCountryNumber, 0, @Debit, 0,@TransferWareNumber, @WareText, @Payment, @TransferAccountNumber, @TransferVatValue,@FK_DepartmentNumber, @Note, @PostForDatetime, @ExchangeRate, @ProductWareLinkNumber, @TransferToGuestNumber, 0, @TransferToHotelCustomerNumber, @ChargedWithCard)
    IF @UseCashDesk = 1
    BEGIN
      SELECT @LastTransactionNumber = @@Identity
      EXEC TransactionsRelationInsert @LastTransactionNumber, @ACashDesk, @UserIDNumber
    END
  END
  FETCH NEXT FROM TransferVatTrans1 INTO @TransNumber, @TransferPriceOverwrite, @TransferAccountNumber, @TransferVatValue, @TransferAccountType, @Debit, @Payment, @WareText, @TransferWareNumber, @TransferPieces
END

CLOSE TransferVatTrans1
DEALLOCATE TransferVatTrans1

IF ((@WarePrice * -1) - @TotalTransferPriceOverwrite <> 0) and @WarePrice <> @TotalTransferPriceOverwrite
BEGIN 
  -- Transfer amount is not the same as total amount on transactions
  IF @ShowPrint = 1
  BEGIN
    PRINT '@TotalTransferPriceOverwrite - print difference'
    PRINT @TotalTransferPriceOverwrite
  END 

  IF @WarePrice > @TotalTransferPriceOverwrite
  BEGIN
    INSERT INTO Transactions (ProductNumber, PackageNumber, GlobalTransactionNumber, OriginalGlobalTransactionNumber, RealDateTimeForTransaction, PAXadult, PAXChild1, PAXChild2, DateStamp, UserIDNumber, FolioNumber, Pieces, PriceOverwrite, CurrencyNumber, ShowOnBillFlag, Debit, MovedDeleted, WareNumber, TextOverwrite, Payment, AccountNumber, VATValue, /*DepartmentOverwrite*/ FK_DepartmentNumber, Note, OriginalDate,ExchangeRateOverwrite,ProductWareLinkNumber,GuestNumber, AutoReceivedPayment, HotelCustomerNumber, ChargedWithCard)
    VALUES(0, 0, @GlobalTransactionNumber, @GlobalTransactionNumber, @RealDateTimeForTransaction, @PaxAdult, @PaxChild1, @PaxChild2, @RealDateTimeForTransaction, @UserIDNumber, @TransferToFolioNumber, @Pieces, @WarePrice - @TotalTransferPriceOverwrite, @MyCountryNumber, 0, 0, 0, @WareNumber, @WareText, 0, @TransferAccountNumber, 0, @FK_DepartmentNumber, @Note, @PostForDatetime, @ExchangeRate, @ProductWareLinkNumber, @TransferToGuestNumber, 0, @TransferToHotelCustomerNumber, @ChargedWithCard)
    IF @UseCashDesk = 1
    BEGIN
      SELECT @LastTransactionNumber = @@Identity
      EXEC TransactionsRelationInsert @LastTransactionNumber, @ACashDesk, @UserIDNumber
    END
  END
  ELSE IF @WarePrice < @TotalTransferPriceOverwrite
  BEGIN
    INSERT INTO Transactions (ProductNumber, PackageNumber, GlobalTransactionNumber, OriginalGlobalTransactionNumber, RealDateTimeForTransaction, PAXadult, PAXChild1, PAXChild2, DateStamp, UserIDNumber, FolioNumber, Pieces, PriceOverwrite, CurrencyNumber, ShowOnBillFlag, Debit, MovedDeleted, WareNumber, TextOverwrite, Payment, AccountNumber, VATValue, /*DepartmentOverwrite*/ FK_DepartmentNumber, Note, OriginalDate,ExchangeRateOverwrite,ProductWareLinkNumber,GuestNumber, AutoReceivedPayment, HotelCustomerNumber, ChargedWithCard)
    VALUES(0, 0, @GlobalTransactionNumber, @GlobalTransactionNumber, @RealDateTimeForTransaction, @PaxAdult, @PaxChild1, @PaxChild2, @RealDateTimeForTransaction, @UserIDNumber, @TransferToFolioNumber, @Pieces, @TotalTransferPriceOverwrite - @WarePrice, @MyCountryNumber, 0, 1, 0, @WareNumber, @WareText, 0, @TransferAccountNumber, 0, @FK_DepartmentNumber, @Note, @PostForDatetime, @ExchangeRate, @ProductWareLinkNumber, @TransferToGuestNumber, 0, @TransferToHotelCustomerNumber, @ChargedWithCard)
    IF @UseCashDesk = 1
    BEGIN
      SELECT @LastTransactionNumber = @@Identity
      EXEC TransactionsRelationInsert @LastTransactionNumber, @ACashDesk, @UserIDNumber
    END
  END
END
IF ISNULL(@TransactionNumberFromFolio, 0) > 0 AND ISNULL(@TransactionNumberToFolio, 0) > 0
  INSERT INTO TransactionTransactionXTab (FK_TransactionNumber1, FK_TransactionNumber2, RelationType) VALUES (@TransactionNumberFromFolio, @TransactionNumberToFolio, 2)