CREATE PROCEDURE DBO.PostMissingPackageTransaction @FolioNumber INT AS
SET NOCOUNT ON

/*
  Changes: 
    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'

DECLARE @PacketAccountNumber INT
SELECT @PacketAccountNumber = AccountNumber FROM Accounts WITH (NOLOCK) WHERE AccountType = 6 AND ISNULL(MarkAsDeleted, '0') = '0'

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(100)
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 @VatValueWare FLOAT
DECLARE @WarePriceWithOutVAT FLOAT
DECLARE @VATOfPacketItem FLOAT

DECLARE @GlobalTransactionNumber INT
DECLARE @ServerDateTime DATETIME
DECLARE @RealDateTimeForTransaction DATETIME

DECLARE @NegPackageAmount BIT

SET @ServerDateTime = CURRENT_TIMESTAMP
SET @RealDateTimeForTransaction = CONVERT(DATETIME, (SELECT SystemDate FROM System WITH (NOLOCK) WHERE Number=1) + ' ' + CONVERT(CHAR(8), @ServerDateTime, 108))

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, InvoiceBillNumber,
  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
  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.AccountNumber = @PacketAccountNumber
  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
  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,
  @PackageAmount

WHILE (@@fetch_status = 0)
BEGIN
  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, InvoiceBillNumber, OriginalGlobalTransactionNumber)
    VALUES(@GlobalTransactionNumber, @RealDateTimeForTransaction, @ProductNumber, @PackageNumber, @Adult, @Children1, @Children2, @RealDateTimeForTransaction, 1, @FolioNumber, @Pieces, @PackageAmount - @VATOfPacketItem, @CurrencyNumber, 0, 0, 0, @WareNumber, @TextOverwrite, 0, @ResultAccountNumber, @VatValueWare, @FK_DepartmentNumber, @RealDateTimeForTransaction, 1, @GuestNumber, @ProductWareLinkNumber, @HotelCustomerNumber, @InvoiceBillNumber, @GlobalTransactionNumber)
    -- 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, InvoiceBillNumber, OriginalGlobalTransactionNumber)
    VALUES(@GlobalTransactionNumber, @RealDateTimeForTransaction, @ProductNumber, @PackageNumber, @Adult, @Children1, @Children2, @RealDateTimeForTransaction, 1, @FolioNumber, @Pieces, @PackageAmount, @CurrencyNumber, 0, 1, 0, @WareNumber, @TextOverwrite, 0, @PacketAccountNumber, @VatValueWare, @FK_DepartmentNumber, @RealDateTimeForTransaction, 1, @GuestNumber, @ProductWareLinkNumber, @HotelCustomerNumber, @InvoiceBillNumber, @GlobalTransactionNumber)
    -- 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, InvoiceBillNumber, OriginalGlobalTransactionNumber)
    VALUES(@GlobalTransactionNumber, @RealDateTimeForTransaction, @ProductNumber, @PackageNumber, @Adult, @Children1, @Children2, @RealDateTimeForTransaction, 1, @FolioNumber, @Pieces, @VATOfPacketItem, @CurrencyNumber, 0, 0, 0, @WareNumber, @TextOverwrite, 0, @VATAccountNumber, @VatValueWare, @FK_DepartmentNumber, @RealDateTimeForTransaction, 1, @GuestNumber, @ProductWareLinkNumber, @HotelCustomerNumber, @InvoiceBillNumber, @GlobalTransactionNumber)
  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, InvoiceBillNumber, OriginalGlobalTransactionNumber)
    VALUES(@GlobalTransactionNumber, @RealDateTimeForTransaction, @ProductNumber, @PackageNumber, @Adult, @Children1, @Children2, @RealDateTimeForTransaction, 1, @FolioNumber, @Pieces, @PackageAmount - @VATOfPacketItem, @CurrencyNumber, 0, 1, 0, @WareNumber, @TextOverwrite , 0, @ResultAccountNumber, @VatValueWare, @FK_DepartmentNumber, @RealDateTimeForTransaction, 1, @GuestNumber, @ProductWareLinkNumber, @HotelCustomerNumber, @InvoiceBillNumber, @GlobalTransactionNumber)
    -- 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, InvoiceBillNumber, OriginalGlobalTransactionNumber)
    VALUES(@GlobalTransactionNumber, @RealDateTimeForTransaction, @ProductNumber, @PackageNumber, @Adult, @Children1, @Children2, @RealDateTimeForTransaction, 1, @FolioNumber, @Pieces, @PackageAmount, @CurrencyNumber, 0, 0, 0, @WareNumber, @TextOverwrite, 0, @PacketAccountNumber, @VatValueWare, @FK_DepartmentNumber, @RealDateTimeForTransaction, 1, @GuestNumber, @ProductWareLinkNumber, @HotelCustomerNumber, @InvoiceBillNumber, @GlobalTransactionNumber)
    -- 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, InvoiceBillNumber, OriginalGlobalTransactionNumber)
    VALUES(@GlobalTransactionNumber, @RealDateTimeForTransaction, @ProductNumber, @PackageNumber, @Adult, @Children1, @Children2, @RealDateTimeForTransaction, 1, @FolioNumber, @Pieces, @VATOfPacketItem, @CurrencyNumber, 0, 1, 0, @WareNumber, @TextOverwrite, 0, @VATAccountNumber, @VatValueWare, @FK_DepartmentNumber, @RealDateTimeForTransaction, 1, @GuestNumber, @ProductWareLinkNumber, @HotelCustomerNumber, @InvoiceBillNumber, @GlobalTransactionNumber)
  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,
    @PackageAmount
END

CLOSE db_cur
DEALLOCATE db_cur