CREATE PROCEDURE DBO.PostMissingPackageTransaction @FolioNumber INT, @ARecordFixed BIT OUTPUT AS
SET NOCOUNT ON

/*
  Changes:
    KJ 20180302: Bug: MovedDeleted wrong.
    KJ 20170220: Ext: Added @TotalPackageAmount to prevent unvanted transactions when package dep account is in level
    KJ 20161219: Bug Fix for "Can not insert the value NULL into FKInvoice"
    KJ 20160916: Ext: Added output param 
    KJ 20160916: Bug: Fix for "InvoiceBillNumber not set on new transactions"
    KJ 20131017: Bug: Fix for "wrong debit credit on posted transactions"
    KJ 20111123: Bug: Fix for "Also use package amount to determine debit and kredit" Case #CR036690
    KJ 20100602: Bug: Added OriginalGlobalTransactionNumber
    KJ 20090126: Some bugfixes.
    KJ 20081021: Ext: Department rewrite.
*/

DECLARE @ShowPrint BIT
SET @ShowPrint = 0

IF @ShowPrint = 1
  PRINT 'Post missing Packages script'

SET @ARecordFixed = 0

DECLARE @TotalPackageAmount MONEY

SELECT
  @TotalPackageAmount = SUM(Transactions.PriceOverwrite * (CASE Transactions.Debit WHEN 1 THEN 1 ELSE -1 END)*CASE WHEN Accounts.AccountType IN (4,5,6) THEN 1 ELSE 1+(VatValue/100) END)
FROM Accounts WITH (NOLOCK)
FULL OUTER JOIN Transactions WITH (NOLOCK) ON Accounts.AccountNumber = Transactions.AccountNumber
WHERE FolioNumber = @FolioNumber
AND Accounts.AccountType = 6

IF ISNULL(@TotalPackageAmount, 0) <> 0
BEGIN
  DECLARE @PacketAccountNumber INT

  DECLARE @Pieces INT
  DECLARE @PackageNumber INT
  DECLARE @PackageAmount FLOAT
  DECLARE @WareNumber INT
  DECLARE @ProductNumber INT
  DECLARE @Adult INT
  DECLARE @Children1 INT
  DECLARE @Children2 INT
  DECLARE @CurrencyNumber INT
  DECLARE @TextOverwrite VARCHAR(150)
  DECLARE @ResultAccountNumber INT
  DECLARE @BalanceAccountNumber INT
  DECLARE @VATAccountNumber INT
  DECLARE @VATValueNumber INT
  DECLARE @FK_DepartmentNumber INT
  DECLARE @GuestNumber INT
  DECLARE @ProductWareLinkNumber INT
  DECLARE @HotelCustomerNumber INT
  DECLARE @InvoiceBillNumber INT
  DECLARE @FKInvoice INT

  DECLARE @VatValueWare FLOAT
  DECLARE @WarePriceWithOutVAT FLOAT
  DECLARE @VATOfPacketItem FLOAT

  DECLARE @GlobalTransactionNumber INT
  DECLARE @RealDateTimeForTransaction DATETIME

  DECLARE @NegPackageAmount BIT

  DECLARE @MovedDeleted BIT

  EXEC SystemDateTime '', @RealDateTimeForTransaction OUTPUT

  IF @ShowPrint = 1
  BEGIN
    PRINT '@RealDateTimeForTransaction'
    PRINT CONVERT(VARCHAR, @RealDateTimeForTransaction)
  END

  DECLARE db_cur CURSOR FOR
    SELECT Folios.FolioNumber, Transactions.Pieces, Transactions.PackageNumber, Transactions.WareNumber, Transactions.ProductNumber, PaxAdult, PaxChild1, PaxChild2,
      CurrencyNumber, TextOverwrite, Ware.ResultAccountNumber, Ware.BalanceAccountNumber, AccountsVAT.VATAccountNumber, AccountsVAT.VatValueNumber, FK_DepartmentNumber,
      Transactions.GuestNumber, ProductWareLinkNumber, Transactions.HotelCustomerNumber, Transactions.InvoiceBillNumber,
      (
        SELECT TOP 1 FKInvoice FROM InvoiceTransactionXTab WITH (NOLOCK)
        INNER JOIN Invoice WITH (NOLOCK) ON Invoice.PkInvoice = InvoiceTransactionXTab.FkInvoice
        WHERE InvoiceTransactionXTab.FkTransaction = Transactions.TransactionNumber
          AND Invoice.CancelDate IS NULL
      ),
      SUM(Transactions.PriceOverwrite * (CASE Transactions.Debit WHEN 1 THEN 1 ELSE -1 END)*CASE WHEN Accounts.AccountType IN (4,5,6) THEN 1 ELSE 1+(VatValue/100) END) AS Price,
      Transactions.MovedDeleted, Transactions.AccountNumber
    FROM Accounts WITH (NOLOCK)
    FULL OUTER JOIN Transactions WITH (NOLOCK) ON Accounts.AccountNumber = Transactions.AccountNumber
    INNER JOIN FOLIOS WITH (NOLOCK) ON Transactions.FolioNumber = FOLIOS.FolioNumber AND Folios.FolioNumber = @FolioNumber
    INNER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = Transactions.WareNumber
    INNER JOIN Accounts AccountsVAT WITH (NOLOCK) ON  Ware.ResultAccountNumber = AccountsVAT.AccountNumber
    WHERE Accounts.AccountType = 6
    --AND ISNULL(MovedDeleted, 0) = 0
    GROUP BY Folios.FolioNumber, Transactions.Pieces, Transactions.PackageNumber, Transactions.WareNumber, Transactions.ProductNumber, PaxAdult, PaxChild1, PaxChild2,
      CurrencyNumber, TextOverwrite, Ware.ResultAccountNumber, Ware.BalanceAccountNumber, AccountsVAT.VATAccountNumber, AccountsVAT.VatValueNumber, FK_DepartmentNumber,
      Transactions.GuestNumber, ProductWareLinkNumber, Transactions.HotelCustomerNumber, Transactions.InvoiceBillNumber, Transactions.TransactionNumber, Transactions.MovedDeleted,
      Transactions.AccountNumber
    HAVING SUM(Transactions.PriceOverwrite * (CASE Transactions.Debit WHEN 1 THEN 1 ELSE -1 END)*
     CASE WHEN Accounts.AccountType IN (4,5,6) THEN 1 ELSE 1+(VatValue/100) END) NOT BETWEEN -1 AND 1
  OPEN db_cur

  FETCH NEXT FROM db_cur INTO @FolioNumber, @Pieces, @PackageNumber, @WareNumber, @ProductNumber, @Adult, @Children1, @Children2, 
    @CurrencyNumber, @TextOverwrite, @ResultAccountNumber, @BalanceAccountNumber, @VATAccountNumber, @VATValueNumber, @FK_DepartmentNumber,
    @GuestNumber, @ProductWareLinkNumber, @HotelCustomerNumber, @InvoiceBillNumber, @FKInvoice,
    @PackageAmount, @MovedDeleted, @PacketAccountNumber

  WHILE (@@fetch_status = 0)
  BEGIN
    SET @ARecordFixed = 1
    IF @ShowPrint = 1
    BEGIN
      PRINT '------------------------'
      PRINT '@FolioNumber'
      PRINT @FolioNumber
      PRINT '@Pieces'
      PRINT @Pieces
      PRINT '@PackageNumber'
      PRINT @PackageNumber
      PRINT '@PackageAmount'
      PRINT CONVERT(VARCHAR, @PackageAmount)
      PRINT '@WareNumber'
      PRINT @WareNumber
      PRINT '@BalanceAccountNumber'
      PRINT @BalanceAccountNumber
      PRINT '@VATAccountNumber'
      PRINT @VATAccountNumber
      PRINT '@VATValueNumber'
      PRINT @VATValueNumber 
    END

    IF @PackageAmount < 0
      SET @NegPackageAmount = 1 
    ELSE
      SET @NegPackageAmount = 0
  
    SET @PackageAmount = ABS(@PackageAmount)

    EXEC GetVATValueFromVATValueNumber @VATValueNumber, @VatValueWare OUTPUT

    IF (@PackageAmount<>0)
    BEGIN
     IF @Pieces>0
       SET @VATOfPacketItem = @PackageAmount*(1.0-(1.0/CONVERT(FLOAT,((@VatValueWare/100.0)+1.0))))
     ELSE
       SET @VATOfPacketItem = ABS(-@PackageAmount*(1.0-(1.0/CONVERT(FLOAT,((@VatValueWare/100.0)+1.0)))))
    END

    IF @ShowPrint = 1
    BEGIN
      PRINT '@VATOfPacketItem'
      PRINT CONVERT(VARCHAR, @VATOfPacketItem)
      print '@PackageAmount - @VATOfPacketItem'
      print CONVERT(VARCHAR, @PackageAmount - @VATOfPacketItem)
    END

    EXEC GetNextNumber 'GlobalTransactionIdentity', 1, @GlobalTransactionNumber OUTPUT

    IF /*(@Pieces<0) OR*/ @NegPackageAmount = 1
    BEGIN
      -- Krediter vare på omsætning excl moms
      INSERT INTO Transactions (GlobalTransactionNumber, RealDateTimeForTransaction, ProductNumber, PackageNumber, PAXadult, PAXChild1, PAXChild2, DateStamp, UserIDNumber, FolioNumber, Pieces, PriceOverwrite, CurrencyNumber, ShowOnBillFlag, Debit, MovedDeleted, WareNumber, TextOverwrite, Payment, AccountNumber, VATValue, FK_DepartmentNumber, OriginalDate, ExchangeRateOverwrite, GuestNumber,ProductWareLinkNumber, HotelCustomerNumber, OriginalGlobalTransactionNumber, InvoiceBillNumber)
      VALUES(@GlobalTransactionNumber, @RealDateTimeForTransaction, @ProductNumber, @PackageNumber, @Adult, @Children1, @Children2, @RealDateTimeForTransaction, 1, @FolioNumber, @Pieces, @PackageAmount - @VATOfPacketItem, @CurrencyNumber, 0, 0, @MovedDeleted, @WareNumber, @TextOverwrite, 0, @ResultAccountNumber, @VatValueWare, @FK_DepartmentNumber, @RealDateTimeForTransaction, 1, @GuestNumber, @ProductWareLinkNumber, @HotelCustomerNumber, @GlobalTransactionNumber, @InvoiceBillNumber)
      IF ISNULL(@FKInvoice, 0) > 0
        INSERT INTO InvoiceTransactionXTab (FKInvoice, FKTransaction) VALUES (@FKInvoice, @@IDENTITY)

      -- Debiter vare incl moms på balancekonto
      INSERT INTO Transactions (GlobalTransactionNumber, RealDateTimeForTransaction, ProductNumber, PackageNumber, PAXadult, PAXChild1, PAXChild2, DateStamp, UserIDNumber, FolioNumber, Pieces, PriceOverwrite, CurrencyNumber, ShowOnBillFlag, Debit, MovedDeleted, WareNumber, TextOverwrite, Payment, AccountNumber, VATValue, FK_DepartmentNumber, OriginalDate,ExchangeRateOverwrite, GuestNumber,ProductWareLinkNumber, HotelCustomerNumber, OriginalGlobalTransactionNumber, InvoiceBillNumber)
      VALUES(@GlobalTransactionNumber, @RealDateTimeForTransaction, @ProductNumber, @PackageNumber, @Adult, @Children1, @Children2, @RealDateTimeForTransaction, 1, @FolioNumber, @Pieces, @PackageAmount, @CurrencyNumber, 0, 1, @MovedDeleted, @WareNumber, @TextOverwrite, 0, @PacketAccountNumber, @VatValueWare, @FK_DepartmentNumber, @RealDateTimeForTransaction, 1, @GuestNumber, @ProductWareLinkNumber, @HotelCustomerNumber, @GlobalTransactionNumber, @InvoiceBillNumber)
      IF ISNULL(@FKInvoice, 0) > 0
        INSERT INTO InvoiceTransactionXTab (FKInvoice, FKTransaction) VALUES (@FKInvoice, @@IDENTITY)

      -- Krediter momsen for varen på balancen (moms)
      INSERT INTO Transactions (GlobalTransactionNumber, RealDateTimeForTransaction, ProductNumber, PackageNumber, PAXadult, PAXChild1, PAXChild2, DateStamp, UserIDNumber, FolioNumber, Pieces, PriceOverwrite, CurrencyNumber, ShowOnBillFlag, Debit, MovedDeleted, WareNumber, TextOverwrite, Payment, AccountNumber, VATVAlue, FK_DepartmentNumber, OriginalDate,ExchangeRateOverwrite, GuestNumber,ProductWareLinkNumber, HotelCustomerNumber, OriginalGlobalTransactionNumber, InvoiceBillNumber)
      VALUES(@GlobalTransactionNumber, @RealDateTimeForTransaction, @ProductNumber, @PackageNumber, @Adult, @Children1, @Children2, @RealDateTimeForTransaction, 1, @FolioNumber, @Pieces, @VATOfPacketItem, @CurrencyNumber, 0, 0, @MovedDeleted, @WareNumber, @TextOverwrite, 0, @VATAccountNumber, @VatValueWare, @FK_DepartmentNumber, @RealDateTimeForTransaction, 1, @GuestNumber, @ProductWareLinkNumber, @HotelCustomerNumber, @GlobalTransactionNumber, @InvoiceBillNumber)
      IF ISNULL(@FKInvoice, 0) > 0
        INSERT INTO InvoiceTransactionXTab (FKInvoice, FKTransaction) VALUES (@FKInvoice, @@IDENTITY)
    END
    ELSE
    BEGIN
      -- Debiter vare på omsætning på resultat excl moms
      INSERT INTO Transactions (GlobalTransactionNumber, RealDateTimeForTransaction, ProductNumber, PackageNumber, PAXadult, PAXChild1, PAXChild2, DateStamp, UserIDNumber, FolioNumber, Pieces, PriceOverwrite, CurrencyNumber, ShowOnBillFlag, Debit, MovedDeleted, WareNumber, TextOverwrite, Payment, AccountNumber, VATValue, FK_DepartmentNumber, OriginalDate, ExchangeRateOverwrite, GuestNumber,ProductWareLinkNumber, HotelCustomerNumber, OriginalGlobalTransactionNumber, InvoiceBillNumber)
      VALUES(@GlobalTransactionNumber, @RealDateTimeForTransaction, @ProductNumber, @PackageNumber, @Adult, @Children1, @Children2, @RealDateTimeForTransaction, 1, @FolioNumber, @Pieces, @PackageAmount - @VATOfPacketItem, @CurrencyNumber, 0, 1, @MovedDeleted, @WareNumber, @TextOverwrite , 0, @ResultAccountNumber, @VatValueWare, @FK_DepartmentNumber, @RealDateTimeForTransaction, 1, @GuestNumber, @ProductWareLinkNumber, @HotelCustomerNumber, @GlobalTransactionNumber, @InvoiceBillNumber)
      IF ISNULL(@FKInvoice, 0) > 0
        INSERT INTO InvoiceTransactionXTab (FKInvoice, FKTransaction) VALUES (@FKInvoice, @@IDENTITY)

      -- Krediter vare incl moms på balancekonto
      INSERT INTO Transactions (GlobalTransactionNumber, RealDateTimeForTransaction, ProductNumber, PackageNumber, PAXadult, PAXChild1, PAXChild2, DateStamp, UserIDNumber, FolioNumber, Pieces, PriceOverwrite, CurrencyNumber, ShowOnBillFlag, Debit, MovedDeleted, WareNumber, TextOverwrite, Payment, AccountNumber, VATValue, FK_DepartmentNumber, OriginalDate, ExchangeRateOverwrite, GuestNumber,ProductWareLinkNumber, HotelCustomerNumber, OriginalGlobalTransactionNumber, InvoiceBillNumber)
      VALUES(@GlobalTransactionNumber, @RealDateTimeForTransaction, @ProductNumber, @PackageNumber, @Adult, @Children1, @Children2, @RealDateTimeForTransaction, 1, @FolioNumber, @Pieces, @PackageAmount, @CurrencyNumber, 0, 0, @MovedDeleted, @WareNumber, @TextOverwrite, 0, @PacketAccountNumber, @VatValueWare, @FK_DepartmentNumber, @RealDateTimeForTransaction, 1, @GuestNumber, @ProductWareLinkNumber, @HotelCustomerNumber, @GlobalTransactionNumber, @InvoiceBillNumber)
      IF ISNULL(@FKInvoice, 0) > 0
        INSERT INTO InvoiceTransactionXTab (FKInvoice, FKTransaction) VALUES (@FKInvoice, @@IDENTITY)

      -- Debiter momsen for varen på balancen (moms)
      INSERT INTO Transactions (GlobalTransactionNumber, RealDateTimeForTransaction, ProductNumber, PackageNumber, PAXadult, PAXChild1, PAXChild2, DateStamp, UserIDNumber, FolioNumber, Pieces, PriceOverwrite, CurrencyNumber, ShowOnBillFlag, Debit, MovedDeleted, WareNumber, TextOverwrite, Payment, AccountNumber, VATVAlue, FK_DepartmentNumber, OriginalDate, ExchangeRateOverwrite, GuestNumber,ProductWareLinkNumber, HotelCustomerNumber, OriginalGlobalTransactionNumber, InvoiceBillNumber)
      VALUES(@GlobalTransactionNumber, @RealDateTimeForTransaction, @ProductNumber, @PackageNumber, @Adult, @Children1, @Children2, @RealDateTimeForTransaction, 1, @FolioNumber, @Pieces, @VATOfPacketItem, @CurrencyNumber, 0, 1, @MovedDeleted, @WareNumber, @TextOverwrite, 0, @VATAccountNumber, @VatValueWare, @FK_DepartmentNumber, @RealDateTimeForTransaction, 1, @GuestNumber, @ProductWareLinkNumber, @HotelCustomerNumber, @GlobalTransactionNumber, @InvoiceBillNumber)
      IF ISNULL(@FKInvoice, 0) > 0
        INSERT INTO InvoiceTransactionXTab (FKInvoice, FKTransaction) VALUES (@FKInvoice, @@IDENTITY)

    END

    FETCH NEXT FROM db_cur INTO @FolioNumber, @Pieces, @PackageNumber, @WareNumber, @ProductNumber, @Adult, @Children1, @Children2, 
      @CurrencyNumber, @TextOverwrite, @ResultAccountNumber, @BalanceAccountNumber, @VATAccountNumber, @VATValueNumber, @FK_DepartmentNumber,
      @GuestNumber, @ProductWareLinkNumber, @HotelCustomerNumber, @InvoiceBillNumber, @FKInvoice,
      @PackageAmount, @MovedDeleted, @PacketAccountNumber
  END

  CLOSE db_cur
  DEALLOCATE db_cur
END