CREATE PROCEDURE DBO.PostDepositDiffAll AS
/*
  Created by Kent Johansen. November 2020

*/
SET NOCOUNT ON

DECLARE @Saldo MONEY
DECLARE @FolioNumber INT
DECLARE @ResStatus INT

SET @ResStatus = 6

DECLARE db_cur CURSOR LOCAL READ_ONLY FAST_FORWARD FOR
  SELECT Folios.FolioNumber,
  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 (NOLOCK)
  FULL OUTER JOIN Transactions (NOLOCK) ON Accounts.AccountNumber = Transactions.AccountNumber
  INNER JOIN FOLIOS (NOLOCK) ON Transactions.FolioNumber = FOLIOS.FolioNumber
  INNER JOIN Reservations (NOLOCK) ON Reservations.ReferenceNumber = Folios.ReferenceNumber AND Reservations.GuestNumber = Folios.GuestNumber
  AND Accounts.AccountType IN (8,9)
  AND Reservations.Status >= @ResStatus
  GROUP BY Folios.FolioNumber
  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
  ORDER BY Folios.FolioNumber DESC
OPEN db_cur

FETCH NEXT FROM db_cur INTO @FolioNumber,  @Saldo

WHILE @@FETCH_STATUS = 0
BEGIN
  IF @Saldo <> 0
    EXEC PostDepositDiff @FolioNumber

  FETCH NEXT FROM db_cur INTO @FolioNumber,  @Saldo
END

CLOSE db_cur
DEALLOCATE db_cur
