CREATE PROCEDURE dbo.BalanceEconomyExt @AHotelCustomerNumber INT AS
SET NOCOUNT ON

/*
  Changes:
    KJ 20201006: Bug: Fix for "Wrong handle for AHotelCustomerNumber = -1"
    KJ 20190704: Ext: Added Bonus payment
    KJ 20171117: Removed use of getbestaddresstypenumber
    20151005: Ext: Added handle for AHotelCustomerNumber = -1
*/

CREATE TABLE #tmp
(
  FolioNumber INT PRIMARY KEY,
  AllAmount MONEY,
  DepositAmount MONEY,
  InhouseAmount MONEY
)

DECLARE @FolioNumber INT
DECLARE @tmpSaldo MONEY
DECLARE @AccountNumber INT

/*
  Debtor all saldo
*/

DECLARE @DummyReference INT
DECLARE @DummyGuest INT
DECLARE @DummyFullName VARCHAR(500)
DECLARE @DummyArrivalDate DATETIME
DECLARE @DummyDepartureDate DATETIME
DECLARE @DummyComp VARCHAR(100)
DECLARE @DummyAgency VARCHAR(100)
DECLARE @DummyCheckRef INT
DECLARE @DummyStatus INT
DECLARE @DummyLineType INT
DECLARE @DummyLink INT
DECLARE @DummyPhone VARCHAR(100)
DECLARE @DummyAddressType INT 
DECLARE @DummyWareName VARCHAR(100)

DECLARE db_cur CURSOR FOR
  SELECT DISTINCT (SELECT SUM(Transactions.PriceOverwrite*(CASE WHEN (Accounts.AccountType IN (4, 9)) THEN -1 ELSE 1 END)*(CASE WHEN (Transactions.Debit = 0) THEN -1 ELSE 1 END))
  FROM Transactions WITH (nolock) 
  INNER JOIN FOLIOS F1 WITH (nolock) ON Transactions.FolioNumber = F1.FolioNumber 
    AND Transactions.FolioNumber = F.FolioNumber
  INNER JOIN Accounts WITH (nolock) ON Transactions.AccountNumber = Accounts.AccountNumber
  WHERE Transactions.showonbillflag=1
  AND (@AHotelCustomerNumber = -11 OR Transactions.HotelCustomerNumber = @AHotelCustomerNumber)
  AND Transactions.Payment IN (0,1,2,3,4,5,6,7,8,9,12,13,14,15,16,17,21)
  AND (Transactions.moveddeleted IS NULL or Transactions.MovedDeleted=0) 
  and F1.referencenumber = F.ReferenceNumber and F1.guestnumber=F.GuestNumber)
  , TFindReservationView.ReferenceNumber
  , TFindReservationView.GuestNumber
  , (TFindReservationView.LastName+CASE WHEN TFindReservationView.FirstName <> '' THEN ', '+TFindReservationView.FirstName ELSE '' END)
  , CONVERT(Char(12), CAST(TFindReservationView.ArrivalDate AS DateTime), 112)
  , CONVERT(Char(12), CAST(TFindReservationView.DepartureDate AS DateTime), 112)
  , TFindReservationView.CompanyName
  , TFindReservationView.AgencyName
  , 0
  , TFindReservationView.Status
  , 1
/*
  , (SELECT Top 1 FolioNumber From Folios WITH (nolock)
      WHERE Folios.ReferenceNumber = TFindReservationView.ReferenceNumber and Folios.GuestNumber = TFindReservationView.GuestNumber)
*/
  , F.FolioNumber
  , RESERVATIONADDRESS.LINK
  , RESERVATIONADDRESS.PHONE
  , RESERVATIONADDRESS.ADDRESSTYPE
  , Ware.WareName
  FROM TFindReservationView WITH (NOLOCK) 
  INNER JOIN ReservationaddressXtab WITH (NOLOCK) ON ReservationaddressXtab.Referencenumber = TFindReservationView.ReferenceNumber 
    AND ReservationaddressXtab.Guestnumber = TFindReservationView.GuestNumber 
  INNER JOIN ReservationAddress WITH (nolock) on ReservationAddress.AddressType=--dbo.getbestaddresstypenumber(TFindReservationView.ReferenceNumber, TFindReservationView.guestNumber) 
    (
      select top 1 AddressType 
      from reservationaddress WITH (nolock) 
      where addressnumber in (select addressnumber from reservationaddressxtab WITH (nolock) where referencenumber = TFindReservationView.ReferenceNumber and guestnumber = TFindReservationView.GuestNumber)
      order by Case 
        WHEN Addresstype = 2 then 1
        WHEN Addresstype = 3 then 2 
        WHEN Addresstype = 5 then 3 
        WHEN Addresstype = 1 then 4 
        WHEN Addresstype = 4 then 5 
        ELSE 6
      END  
    )   

    AND ReservationAddress.AddressNumber=ReservationaddressXtab.AddressNumber 
  LEFT OUTER JOIN FOLIOS WITH (nolock) ON RESERVATIONADDRESS.AddressNumber = FOLIOS.ReservationAddressNumber 
    AND dbo.RESERVATIONADDRESS.AddressNumber = dbo.FOLIOS.ReservationAddressNumber
  LEFT OUTER JOIN Ware WITH (NOLOCK) ON RESERVATIONADDRESS.PaymentTypeWareNumber = Ware.WareNumber
  LEFT OUTER JOIN Folios F WITH (NOLOCK) ON F.ReferenceNumber = TFindReservationView.ReferenceNumber
    AND F.GuestNumber = TFindReservationView.GuestNumber
  WHERE 1=2
  OR (TFindReservationView.GuestNumber=0
  AND (TFindReservationView.SpecnumberofRooms IS NULL OR TFindReservationView.SpecnumberofRooms = 0)
  AND TFindReservationView.ReferenceNumber NOT IN (SELECT  ReferenceNumber FROM RESERVATIONCONFERENCELINE WITH (NOLOCK) where ReferenceNumber>0)
  AND (1=2 OR TFindReservationView.status IN (0,1,2,3,4,5,6,7,8)))
  OR (TFindReservationView.ReferenceNumber > 0
  AND TFindReservationView.SpecnumberofRooms > 0
  AND (1=2 OR TFindReservationView.status IN (0,1,2,3,4,5,6,7,8)))
  OR (TFindReservationView.ReferenceNumber IN (SELECT  ReferenceNumber FROM  RESERVATIONCONFERENCELINE WITH (NOLOCK))
  AND TFindReservationView.GuestNumber = 0
  AND (1=2 OR TFindReservationView.status IN (0,1,2,3,4,5,6,7,8)))
  GROUP BY
    TFindReservationView.ReferenceNumber, TFindReservationView.GuestNumber,
    (TFindReservationView.LastName+CASE WHEN TFindReservationView.FirstName <> '' THEN ', '+TFindReservationView.FirstName ELSE '' END),
    CONVERT(Char(12), CAST(TFindReservationView.ArrivalDate AS DateTime), 112),
    CONVERT(Char(12), CAST(TFindReservationView.DepartureDate AS DateTime), 112),
    TFindReservationView.CompanyName,
    TFindReservationView.AgencyName,
    TFindReservationView.Status, F.FolioNumber,
    RESERVATIONADDRESS.LINK,
    RESERVATIONADDRESS.PHONE,
    RESERVATIONADDRESS.ADDRESSTYPE,
    Ware.WareName, F.ReferenceNumber, F.GuestNumber
  HAVING
    ROUND((SELECT SUM(Transactions.PriceOverwrite * (CASE WHEN (Accounts.AccountType IN (4,9)) THEN -1 ELSE 1 END)* (CASE WHEN (Transactions.Debit = 0) THEN -1 ELSE 1 END))
    FROM Transactions WITH (nolock) 
    INNER JOIN FOLIOS F1 WITH (nolock) ON Transactions.FolioNumber = F1.FolioNumber 
    INNER JOIN Accounts WITH (nolock) ON Transactions.AccountNumber = Accounts.AccountNumber
    WHERE Transactions.showonbillflag=1
    AND (@AHotelCustomerNumber = -1 OR Transactions.HotelCustomerNumber = @AHotelCustomerNumber)
    AND Transactions.Payment IN (0,1,2,3,4,5,6,7,8,9,12,13,14,15,16,17,21)
    AND (Transactions.moveddeleted IS NULL or Transactions.MovedDeleted=0) and
    F1.referencenumber = TFindReservationView.ReferenceNumber and F1.guestnumber=TFindReservationView.GuestNumber), 3) <> 0

  UNION
  
  SELECT DISTINCT
    (SELECT SUM(Transactions.PriceOverwrite*(CASE WHEN (Accounts.AccountType IN (4, 9)) THEN -1 ELSE 1 END)*(CASE WHEN (Transactions.Debit = 0) THEN -1 ELSE 1 END))
    FROM Transactions WITH (nolock) 
    INNER JOIN FOLIOS F1 WITH (nolock) ON Transactions.FolioNumber = F1.FolioNumber 
    INNER JOIN Accounts WITH (nolock) ON Transactions.AccountNumber = Accounts.AccountNumber
    WHERE showonbillflag=1
    AND (@AHotelCustomerNumber = -1 OR Transactions.HotelCustomerNumber = @AHotelCustomerNumber)
    AND Transactions.Payment IN (0,1,2,3,4,5,6,7,8,9,12,13,14,15,16,17,21)
    and (Transactions.moveddeleted IS NULL or Transactions.MovedDeleted=0) and Transactions.FolioNumber= Folios.FolioNumber)
    , Folios.ReferenceNumber, Folios.GuestNumber
    , (RESERVATIONADDRESS.LASTNAME+CASE WHEN RESERVATIONADDRESS.FirstName <> '' THEN ', '+RESERVATIONADDRESS.FirstName ELSE '' END)
    , '', '', '', '', '', Folios.OpenFolio, 0, Folios.FolioNumber, RESERVATIONADDRESS.LINK, RESERVATIONADDRESS.PHONE, RESERVATIONADDRESS.ADDRESSTYPE, Ware.WareName
  FROM FOLIOS WITH (NOLOCK) 
  INNER JOIN RESERVATIONADDRESS WITH (NOLOCK) ON FOLIOS.ReservationAddressNumber = RESERVATIONADDRESS.AddressNumber 
  LEFT OUTER JOIN Ware WITH (NOLOCK) ON RESERVATIONADDRESS.PaymentTypeWareNumber = Ware.WareNumber
  WHERE (Folios.ReferenceNumber IS NULL)
  AND ((Folios.GuestNumber = 0) OR (Folios.GuestNumber is null)) 
  AND (FOLIOS.ReservationAddressNumber > 0)
  AND (FOLIOS.FolioNumber NOT IN (SELECT FolioNUmber FROM FolioRestResTableChairsXTab WITH (NOLOCK)))
  GROUP BY
    Folios.ReferenceNumber,
    Folios.GuestNumber,
    (RESERVATIONADDRESS.LASTNAME+CASE WHEN RESERVATIONADDRESS.FirstName <> '' THEN ', '+RESERVATIONADDRESS.FirstName ELSE '' END),
    Folios.FolioNumber,
    FOLIOS.OpenFolio,
    RESERVATIONADDRESS.LINK,
    RESERVATIONADDRESS.PHONE,
    RESERVATIONADDRESS.ADDRESSTYPE,
    Ware.WareName
  HAVING
    ROUND((SELECT SUM(Transactions.PriceOverwrite*(CASE WHEN (Accounts.AccountType IN (4, 9)) THEN -1 ELSE 1 END)*(CASE WHEN (Transactions.Debit = 0) THEN -1 ELSE 1 END))
    FROM Transactions WITH (nolock) 
    INNER JOIN FOLIOS F1 WITH (nolock) ON Transactions.FolioNumber = F1.FolioNumber 
    INNER JOIN Accounts WITH (nolock) ON Transactions.AccountNumber = Accounts.AccountNumber
    WHERE showonbillflag=1
      AND (@AHotelCustomerNumber = -1 OR Transactions.HotelCustomerNumber = @AHotelCustomerNumber)
      AND Transactions.Payment IN (0,1,2,3,4,5,6,7,8,9,12,13,14,15,16,17,21)
      and (Transactions.moveddeleted IS NULL or Transactions.MovedDeleted=0) and Transactions.FolioNumber= Folios.FolioNumber), 3) <> 0

  UNION

  SELECT DISTINCT (SELECT SUM(Transactions.PriceOverwrite*(CASE WHEN (Accounts.AccountType IN (4, 9)) THEN -1 ELSE 1 END)*(CASE WHEN (Transactions.Debit = 0) THEN -1 ELSE 1 END))
    FROM Transactions WITH (nolock) 
    INNER JOIN FOLIOS F1 WITH (nolock) ON Transactions.FolioNumber = F1.FolioNumber 
    INNER JOIN Accounts WITH (nolock) ON Transactions.AccountNumber = Accounts.AccountNumber
    WHERE showonbillflag=1
    AND (@AHotelCustomerNumber = -1 OR Transactions.HotelCustomerNumber = @AHotelCustomerNumber)
    AND Transactions.Payment IN (0,1,2,3,4,5,6,7,8,9,12,13,14,15,16,17,21)
    and (Transactions.moveddeleted IS NULL or Transactions.MovedDeleted=0) and Transactions.FolioNumber= Folios.FolioNumber)
  , Folios.ReferenceNumber, Folios.GuestNumber, '', '', '', '', '', Folios.FolioNumber, Folios.OpenFolio, 0, Folios.FolioNumber, '', '', '', ''
  FROM
  FOLIOS WITH (NOLOCK)
   WHERE (FOLIOS.ReservationAddressNumber is null) AND (Folios.GuestNumber = 0 ) AND (FOLIOS.ReferenceNumber is null)
  AND (FOLIOS.FolioNumber NOT IN (SELECT FolioNUmber  FROM FolioRestResTableChairsXTab WITH (NOLOCK)))
  GROUP BY
    Folios.ReferenceNumber,
    Folios.GuestNumber,
    Folios.FolioNumber,
    Folios.OpenFolio,
    Folios.FolioNumber
  HAVING ROUND((SELECT SUM(Transactions.PriceOverwrite*(CASE WHEN (Accounts.AccountType IN (4, 9)) THEN -1 ELSE 1 END)*(CASE WHEN (Transactions.Debit = 0) THEN -1 ELSE 1 END))
    FROM Transactions WITH (nolock) INNER JOIN
      FOLIOS F1 WITH (nolock) ON Transactions.FolioNumber = F1.FolioNumber INNER JOIN
      Accounts WITH (nolock) ON Transactions.AccountNumber = Accounts.AccountNumber
    WHERE showonbillflag=1
    AND (@AHotelCustomerNumber = -1 OR Transactions.HotelCustomerNumber = @AHotelCustomerNumber)
    AND Transactions.Payment IN (0,1,2,3,4,5,6,7,8,9,12,13,14,15,16,17,21)
    and (Transactions.moveddeleted IS NULL or Transactions.MovedDeleted=0) and Transactions.FolioNumber= Folios.FolioNumber), 3) <> 0

  UNION

  SELECT
    (SELECT SUM(Transactions.PriceOverwrite *
    (CASE WHEN (Accounts.AccountType IN (4, 9)) THEN -1 ELSE 1 END)*
    (CASE WHEN (Transactions.Debit = 0) THEN -1 ELSE 1 END))
    FROM Transactions WITH (nolock) INNER JOIN
      FOLIOS F1 WITH (nolock) ON Transactions.FolioNumber = F1.FolioNumber INNER JOIN
      Accounts WITH (nolock) ON Transactions.AccountNumber = Accounts.AccountNumber
    WHERE showonbillflag=1
    AND (@AHotelCustomerNumber = -1 OR Transactions.HotelCustomerNumber = @AHotelCustomerNumber)
    AND Transactions.Payment IN (0,1,2,3,4,5,6,7,8,9,12,13,14,15,16,17,21)
      and (Transactions.moveddeleted IS NULL or Transactions.MovedDeleted=0) and Transactions.FolioNumber= Folios.FolioNumber) AS Total
  , FolioRestResTableChairsXTab.RestaurantReservationNumber AS Reference, Folios.GuestNumber As Guest
  , '' As FullName, Convert(Char(12), RestaurantReservations.StartDateTime, 112) AS ArrivalDate
  , Convert(Char(12), RestaurantReservations.EndDateTime, 112) AS DepartureDate
  , '' AS CompanyName, '' AS AgencyName, FOLIOS.FolioNumber AS CheckReference
  , RestaurantReservations.Status, 5 AS LineType, Folios.FolioNumber
  , '' AS Link, '' AS PHONE, '' AS ADDRESSTYPE, '' AS WareName
  FROM FOLIOS WITH (NOLOCK) Inner join
  FolioRestResTableChairsXTab WITH (NOLOCK) ON FolioRestResTableChairsXTab.FolioNumber = FOLIOS.FolioNumber inner join
  RestaurantReservations WITH (NOLOCK) ON RestaurantReservations.RestaurantReservationNumber = FolioRestResTableChairsXTab.RestaurantReservationNumber
  WHERE ISNULL(Folios.GuestNumber, 0) = 0
  AND (1=2
  OR RestaurantReservations.status IN (0,1,2,3,4,5,6,7,8)
  )
  GROUP BY
    FolioRestResTableChairsXTab.RestaurantReservationNumber,
    Folios.GuestNumber,
    Convert(Char(12), RestaurantReservations.StartDateTime, 112),
    Convert(Char(12), RestaurantReservations.EndDateTime, 112),
    FOLIOS.FolioNumber,
    RestaurantReservations.Status,
    Folios.FolioNumber
  HAVING ROUND((SELECT SUM(Transactions.PriceOverwrite*(CASE WHEN (Accounts.AccountType IN (4, 9)) THEN -1 ELSE 1 END)*(CASE WHEN (Transactions.Debit = 0) THEN -1 ELSE 1 END))
    FROM Transactions WITH (nolock) INNER JOIN
      FOLIOS F1 WITH (nolock) ON Transactions.FolioNumber = F1.FolioNumber INNER JOIN
      Accounts WITH (nolock) ON Transactions.AccountNumber = Accounts.AccountNumber
    WHERE showonbillflag=1
    AND (@AHotelCustomerNumber = -1 OR Transactions.HotelCustomerNumber = @AHotelCustomerNumber)
    AND Transactions.Payment IN (0,1,2,3,4,5,6,7,8,9,12,13,14,15,16,17,21)
    and (Transactions.moveddeleted IS NULL or Transactions.MovedDeleted=0) and Transactions.FolioNumber= Folios.FolioNumber), 3) <> 0

  UNION

  SELECT (SELECT SUM(Transactions.PriceOverwrite*(CASE WHEN (Accounts.AccountType IN (4, 9)) THEN -1 ELSE 1 END)*(CASE WHEN (Transactions.Debit = 0) THEN -1 ELSE 1 END))
    FROM Transactions WITH (nolock) INNER JOIN
      FOLIOS F1 WITH (nolock) ON Transactions.FolioNumber = F1.FolioNumber INNER JOIN
      Accounts WITH (nolock) ON Transactions.AccountNumber = Accounts.AccountNumber
    WHERE showonbillflag=1
    AND (@AHotelCustomerNumber = -1 OR Transactions.HotelCustomerNumber = @AHotelCustomerNumber)
    AND Transactions.Payment IN (0,1,2,3,4,5,6,7,8,9,12,13,14,15,16,17,21)
    and (Transactions.moveddeleted IS NULL or Transactions.MovedDeleted=0) and Transactions.FolioNumber= Folios.FolioNumber) AS Total
  , RESERVATIONCONFERENCELINE.ReferenceNumber AS Reference, ISNULL(RESERVATIONCONFERENCELINE.GuestNo,0) As Guest
  , (RESERVATIONADDRESS.LASTNAME+CASE WHEN RESERVATIONADDRESS.FirstName <> '' THEN ', '+RESERVATIONADDRESS.FirstName ELSE '' END) AS FullName
  , RESERVATIONCONFERENCELINE.ArrivalDate AS ArrivalDate
  , RESERVATIONCONFERENCELINE.DepartureDate AS DepartureDate
  , '' AS CompanyName, '' AS AgencyName, FOLIOS.FolioNumber AS CheckReference
  , RESERVATIONCONFERENCELINE.TableStatus, 5 AS LineType, Folios.FolioNumber
  , '' AS Link, '' AS PHONE, RESERVATIONADDRESS.ADDRESSTYPE AS ADDRESSTYPE, '' AS WareName
  FROM FOLIOS WITH (NOLOCK) Inner join
  FolioRestResTableChairsXTab WITH (NOLOCK) ON FolioRestResTableChairsXTab.FolioNumber = FOLIOS.FolioNumber inner join
  RESERVATIONCONFERENCELINE WITH (NOLOCK) ON RESERVATIONCONFERENCELINE.ReservationConfLineNumber = FolioRestResTableChairsXTab.RestaurantReservationNumber AND 
  RESERVATIONCONFERENCELINE.TableBooking = 1
  INNER JOIN RESERVATIONS WITH (NOLOCK) ON RESERVATIONCONFERENCELINE.ReferenceNumber = RESERVATIONS.ReferenCeNumber AND ISNULL(RESERVATIONCONFERENCELINE.GuestNo,0) = 
  RESERVATIONS.GuestNumber
  INNER JOIN RESERVATIONADDRESSXTAB WITH (NOLOCK) ON RESERVATIONADDRESSXTAB.ReferenceNumber = RESERVATIONS.ReferenCeNumber AND RESERVATIONADDRESSXTAB.GuestNumber = 
  RESERVATIONS.GuestNumber
  INNER JOIN RESERVATIONADDRESS WITH (NOLOCK) ON RESERVATIONADDRESSXTAB.AddressNumber = RESERVATIONADDRESS.AddressNumber AND RESERVATIONADDRESS.ADDRESSTYPE = 1
  Where (FOLIOS.FolioNumber IN (SELECT FolioNumber FROM  FolioRestResTableChairsXTab WITH (NOLOCK)))
  AND (1=2
  OR RESERVATIONCONFERENCELINE.TableStatus IN (0,1,2,3,4,5,6,7,8)
  )
  GROUP BY
    RESERVATIONCONFERENCELINE.ReferenceNumber,
    RESERVATIONCONFERENCELINE.GuestNo,
    (RESERVATIONADDRESS.LASTNAME+CASE WHEN RESERVATIONADDRESS.FirstName <> '' THEN ', '+RESERVATIONADDRESS.FirstName ELSE '' END),
    RESERVATIONCONFERENCELINE.ArrivalDate,
    RESERVATIONCONFERENCELINE.DepartureDate,
    FOLIOS.FolioNumber,
    RESERVATIONCONFERENCELINE.TableStatus,
    Folios.FolioNumber,
    RESERVATIONADDRESS.ADDRESSTYPE
  HAVING ROUND((SELECT SUM(Transactions.PriceOverwrite*(CASE WHEN (Accounts.AccountType IN (4, 9)) THEN -1 ELSE 1 END)*(CASE WHEN (Transactions.Debit = 0) THEN -1 ELSE 1 END))
    FROM Transactions WITH (nolock) INNER JOIN
      FOLIOS F1 WITH (nolock) ON Transactions.FolioNumber = F1.FolioNumber INNER JOIN
      Accounts WITH (nolock) ON Transactions.AccountNumber = Accounts.AccountNumber
    WHERE showonbillflag=1
    AND (@AHotelCustomerNumber = -1 OR Transactions.HotelCustomerNumber = @AHotelCustomerNumber)
    AND Transactions.Payment IN (0,1,2,3,4,5,6,7,8,9,12,13,14,15,16,17,21)
    and (Transactions.moveddeleted IS NULL or Transactions.MovedDeleted=0) and Transactions.FolioNumber= Folios.FolioNumber), 3) <> 0
OPEN db_cur

FETCH NEXT FROM db_cur INTO @tmpSaldo, @DummyReference, @DummyGuest, @DummyFullName, @DummyArrivalDate, @DummyDepartureDate, @DummyComp, @DummyAgency, @DummyCheckRef, @DummyStatus, @DummyLineType,
  @FolioNumber, @DummyLink, @DummyPhone, @DummyAddressType, @DummyWareName

WHILE @@FETCH_STATUS = 0
BEGIN
  INSERT INTO #tmp (FolioNumber, AllAmount) VALUES (@FolioNumber, @tmpSaldo)

  FETCH NEXT FROM db_cur INTO @tmpSaldo, @DummyReference, @DummyGuest, @DummyFullName, @DummyArrivalDate, @DummyDepartureDate, @DummyComp, @DummyAgency, @DummyCheckRef, @DummyStatus, @DummyLineType,
    @FolioNumber, @DummyLink, @DummyPhone, @DummyAddressType, @DummyWareName
END

CLOSE db_cur
DEALLOCATE db_cur

/*
  Debtor Deposit saldo
*/

DECLARE db_cur CURSOR FOR
  SELECT DISTINCT (SELECT SUM(TR.PriceOverwrite*(CASE WHEN (Accounts.AccountType IN (4, 9)) THEN -1 ELSE 1 END)*(CASE WHEN (TR.Debit = 0) THEN -1 ELSE 1 END))
  FROM Transactions TR WITH(NOLOCK) 
  INNER JOIN FOLIOS F1 WITH(NOLOCK) ON TR.FolioNumber = F1.FolioNumber 
    AND TR.FolioNumber = F.FolioNumber
  INNER JOIN Accounts WITH(NOLOCK) ON TR.AccountNumber = Accounts.AccountNumber
  WHERE TR.showonbillflag=1
    AND (@AHotelCustomerNumber = -1 OR TR.HotelCustomerNumber = @AHotelCustomerNumber)
    AND TR.Payment in (12)
    AND (TR.moveddeleted IS NULL or TR.MovedDeleted=0) and
    F1.referencenumber = TFRV.ReferenceNumber and F1.guestnumber = TFRV.GuestNumber) AS Total
  , TFRV.ReferenceNumber As Reference
  , TFRV.GuestNumber As Guest
  , (TFRV.LastName+CASE WHEN TFRV.FirstName <> '' THEN ', '+ TFRV.FirstName ELSE '' END) AS FullName
  , CONVERT(Char(12), CAST(TFRV.ArrivalDate AS DateTime), 112) AS ArrivalDate
  , CONVERT(Char(12), CAST(TFRV.DepartureDate AS DateTime), 112) AS DepartureDate
  , TFRV.CompanyName
  , TFRV.AgencyName
  , 0 AS CheckReference
  , TFRV.Status
  , 1 AS LineType
  , F.FolioNumber
  , RESERVATIONADDRESS.LINK
  , RESERVATIONADDRESS.PHONE
  , RESERVATIONADDRESS.ADDRESSTYPE
  , ISNULL(Ware.WareName, '') AS WareName
  FROM TFindReservationView TFRV WITH(NOLOCK) 
  INNER JOIN ReservationaddressXtab WITH(NOLOCK) ON ReservationaddressXtab.Referencenumber = TFRV.ReferenceNumber 
    AND ReservationaddressXtab.Guestnumber = TFRV.GuestNumber 
  INNER JOIN ReservationAddress WITH(NOLOCK) on ReservationAddress.AddressType=dbo.getbestaddresstypenumber(TFRV.ReferenceNumber, TFRV.guestNumber) 
    AND ReservationAddress.AddressNumber=ReservationaddressXtab.AddressNumber 
  LEFT OUTER JOIN FOLIOS WITH(NOLOCK) ON RESERVATIONADDRESS.AddressNumber = FOLIOS.ReservationAddressNumber 
    AND dbo.RESERVATIONADDRESS.AddressNumber = dbo.FOLIOS.ReservationAddressNumber
  LEFT JOIN Reservations R WITH(NOLOCK) ON R.ReferenceNumber = TFRV.ReferenceNumber 
    AND R.GuestNumber = TFRV.GuestNumber
  LEFT JOIN Ware WITH(NOLOCK) ON R.MethodOfPayment = Ware.WareNumber
  LEFT OUTER JOIN Folios F WITH (NOLOCK) ON F.ReferenceNumber = TFRV.ReferenceNumber
    AND F.GuestNumber = TFRV.GuestNumber
  WHERE 1=2 OR (TFRV.GuestNumber=0
  AND (TFRV.SpecnumberofRooms IS NULL OR TFRV.SpecnumberofRooms = 0)
  AND TFRV.ReferenceNumber NOT IN (SELECT  ReferenceNumber FROM RESERVATIONCONFERENCELINE WITH(NOLOCK) where ReferenceNumber>0)
  AND (1=2 OR TFRV.status IN (0,1,2,3,4)
  ))
  OR (TFRV.ReferenceNumber > 0
  AND TFRV.SpecnumberofRooms > 0
  AND (1=2 OR TFRV.status IN (0,1,2,3,4)
  ))
  OR (TFRV.ReferenceNumber IN (SELECT  ReferenceNumber FROM  RESERVATIONCONFERENCELINE WITH(NOLOCK))
  AND TFRV.GuestNumber = 0
  AND (1=2 OR TFRV.status IN (0,1,2,3,4)
  ))
  GROUP BY
    TFRV.ReferenceNumber, TFRV.GuestNumber, (TFRV.LastName+CASE WHEN TFRV.FirstName <> '' THEN ', '+ TFRV.FirstName ELSE '' END),
    CONVERT(Char(12), CAST(TFRV.ArrivalDate AS DateTime), 112),
    CONVERT(Char(12), CAST(TFRV.DepartureDate AS DateTime), 112),
    TFRV.CompanyName, TFRV.AgencyName, TFRV.Status, F.FolioNumber, RESERVATIONADDRESS.LINK, RESERVATIONADDRESS.PHONE, RESERVATIONADDRESS.ADDRESSTYPE, Ware.WareName
  HAVING
    ROUND((SELECT ABS(SUM(Transactions.PriceOverwrite * (CASE WHEN (Accounts.AccountType IN (4,9)) THEN -1 ELSE 1 END)* (CASE WHEN (Transactions.Debit = 0) THEN -1 ELSE 1 END)))
    FROM Transactions WITH(NOLOCK) 
    INNER JOIN FOLIOS F1 WITH(NOLOCK) ON Transactions.FolioNumber = F1.FolioNumber 
    INNER JOIN Accounts WITH(NOLOCK) ON Transactions.AccountNumber = Accounts.AccountNumber
    WHERE Transactions.showonbillflag=1
    AND (@AHotelCustomerNumber = -1 OR Transactions.HotelCustomerNumber = @AHotelCustomerNumber)
    AND Transactions.Payment in (12)
    AND (Transactions.moveddeleted IS NULL or Transactions.MovedDeleted=0) 
    and F1.referencenumber = TFRV.ReferenceNumber and F1.guestnumber = TFRV.GuestNumber), 3) <> 0
    
  UNION

  SELECT (SELECT SUM(Transactions.PriceOverwrite*(CASE WHEN (Accounts.AccountType IN (4, 9)) THEN -1 ELSE 1 END)*(CASE WHEN (Transactions.Debit = 0) THEN -1 ELSE 1 END))
  FROM Transactions WITH(NOLOCK) 
  INNER JOIN FOLIOS F1 WITH(NOLOCK) ON Transactions.FolioNumber = F1.FolioNumber 
  INNER JOIN Accounts WITH(NOLOCK) ON Transactions.AccountNumber = Accounts.AccountNumber
  WHERE showonbillflag=1
  AND (@AHotelCustomerNumber = -1 OR Transactions.HotelCustomerNumber = @AHotelCustomerNumber)
  AND Transactions.Payment in (12)
  and (Transactions.moveddeleted IS NULL or Transactions.MovedDeleted=0) and Transactions.FolioNumber= Folios.FolioNumber) AS Total
  , FolioRestResTableChairsXTab.RestaurantReservationNumber AS Reference, Folios.GuestNumber As Guest
  , '' As FullName, Convert(Char(12), RestaurantReservations.StartDateTime, 112) AS ArrivalDate
  , Convert(Char(12), RestaurantReservations.EndDateTime, 112) AS DepartureDate
  , '' AS CompanyName, '' AS AgencyName, FOLIOS.FolioNumber AS CheckReference
  , RestaurantReservations.Status, 5 AS LineType, Folios.FolioNumber
  , '' AS Link, '' AS PHONE, '' AS ADDRESSTYPE, '' AS WareName
  FROM FOLIOS WITH(NOLOCK) 
  Inner join FolioRestResTableChairsXTab WITH(NOLOCK) ON FolioRestResTableChairsXTab.FolioNumber = FOLIOS.FolioNumber 
  inner join RestaurantReservations WITH(NOLOCK) ON RestaurantReservations.RestaurantReservationNumber = FolioRestResTableChairsXTab.RestaurantReservationNumber
  WHERE ISNULL(Folios.GuestNumber, 0) = 0
  AND (1=2 OR RestaurantReservations.status IN (0,1,2,3,4))
  GROUP BY
    FolioRestResTableChairsXTab.RestaurantReservationNumber,
    Folios.GuestNumber,
    Convert(Char(12), RestaurantReservations.StartDateTime, 112),
    Convert(Char(12), RestaurantReservations.EndDateTime, 112),
    FOLIOS.FolioNumber,
    RestaurantReservations.Status,
    Folios.FolioNumber
  HAVING
    ROUND((SELECT SUM(Transactions.PriceOverwrite *(CASE WHEN (Accounts.AccountType IN (4, 9)) THEN -1 ELSE 1 END)*(CASE WHEN (Transactions.Debit = 0) THEN -1 ELSE 1 END))
    FROM Transactions WITH(NOLOCK) 
    INNER JOIN FOLIOS F1 WITH(NOLOCK) ON Transactions.FolioNumber = F1.FolioNumber 
    INNER JOIN Accounts WITH(NOLOCK) ON Transactions.AccountNumber = Accounts.AccountNumber
    WHERE showonbillflag=1
    AND (@AHotelCustomerNumber = -1 OR Transactions.HotelCustomerNumber = @AHotelCustomerNumber)
    AND Transactions.Payment in (12)
    and (Transactions.moveddeleted IS NULL or Transactions.MovedDeleted=0) and Transactions.FolioNumber= Folios.FolioNumber), 3) <> 0
    
  UNION

  SELECT (SELECT SUM(Transactions.PriceOverwrite*(CASE WHEN (Accounts.AccountType IN (4, 9)) THEN -1 ELSE 1 END)*(CASE WHEN (Transactions.Debit = 0) THEN -1 ELSE 1 END))
  FROM Transactions WITH(NOLOCK) 
  INNER JOIN FOLIOS F1 WITH(NOLOCK) ON Transactions.FolioNumber = F1.FolioNumber 
  INNER JOIN Accounts WITH(NOLOCK) ON Transactions.AccountNumber = Accounts.AccountNumber
  WHERE showonbillflag=1
  AND (@AHotelCustomerNumber = -1 OR Transactions.HotelCustomerNumber = @AHotelCustomerNumber)
  AND Transactions.Payment in (12)
  and (Transactions.moveddeleted IS NULL or Transactions.MovedDeleted=0) and Transactions.FolioNumber= Folios.FolioNumber) AS Total
  , RESERVATIONCONFERENCELINE.ReferenceNumber AS Reference, ISNULL(RESERVATIONCONFERENCELINE.GuestNo,0) As Guest
  , (RESERVATIONADDRESS.LASTNAME+CASE WHEN RESERVATIONADDRESS.FirstName <> '' THEN ', '+RESERVATIONADDRESS.FirstName ELSE '' END) AS FullName
  , RESERVATIONCONFERENCELINE.ArrivalDate AS ArrivalDate
  , RESERVATIONCONFERENCELINE.DepartureDate AS DepartureDate
  , '' AS CompanyName, '' AS AgencyName, FOLIOS.FolioNumber AS CheckReference
  , RESERVATIONCONFERENCELINE.TableStatus, 5 AS LineType, Folios.FolioNumber
  , '' AS Link, '' AS PHONE, RESERVATIONADDRESS.ADDRESSTYPE AS ADDRESSTYPE, '' AS WareName
  FROM FOLIOS WITH(NOLOCK) 
  Inner join FolioRestResTableChairsXTab WITH(NOLOCK) ON FolioRestResTableChairsXTab.FolioNumber = FOLIOS.FolioNumber 
  inner join RESERVATIONCONFERENCELINE WITH(NOLOCK) ON RESERVATIONCONFERENCELINE.ReservationConfLineNumber = FolioRestResTableChairsXTab.RestaurantReservationNumber 
    AND RESERVATIONCONFERENCELINE.TableBooking = 1
  INNER JOIN RESERVATIONS WITH(NOLOCK) ON RESERVATIONCONFERENCELINE.ReferenceNumber = RESERVATIONS.ReferenCeNumber 
    AND ISNULL(RESERVATIONCONFERENCELINE.GuestNo,0) = RESERVATIONS.GuestNumber
  INNER JOIN RESERVATIONADDRESSXTAB WITH(NOLOCK) ON RESERVATIONADDRESSXTAB.ReferenceNumber = RESERVATIONS.ReferenCeNumber 
    AND RESERVATIONADDRESSXTAB.GuestNumber = RESERVATIONS.GuestNumber
  INNER JOIN RESERVATIONADDRESS WITH(NOLOCK) ON RESERVATIONADDRESSXTAB.AddressNumber = RESERVATIONADDRESS.AddressNumber 
    AND RESERVATIONADDRESS.ADDRESSTYPE = 1
  Where (FOLIOS.FolioNumber IN (SELECT FolioNumber FROM  FolioRestResTableChairsXTab WITH(NOLOCK)))
  AND (1=2 OR RESERVATIONCONFERENCELINE.TableStatus IN (0,1,2,3,4))
  GROUP BY
    RESERVATIONCONFERENCELINE.ReferenceNumber, RESERVATIONCONFERENCELINE.GuestNo,
    (RESERVATIONADDRESS.LASTNAME+CASE WHEN RESERVATIONADDRESS.FirstName <> '' THEN ', '+RESERVATIONADDRESS.FirstName ELSE '' END),
    RESERVATIONCONFERENCELINE.ArrivalDate,
    RESERVATIONCONFERENCELINE.DepartureDate,
    FOLIOS.FolioNumber,
    RESERVATIONCONFERENCELINE.TableStatus,
    Folios.FolioNumber,
    RESERVATIONADDRESS.ADDRESSTYPE
  HAVING
    ROUND((SELECT SUM(Transactions.PriceOverwrite *
    (CASE WHEN (Accounts.AccountType IN (4, 9)) THEN -1 ELSE 1 END)*(CASE WHEN (Transactions.Debit = 0) THEN -1 ELSE 1 END))  
    FROM Transactions WITH(NOLOCK) 
    INNER JOIN FOLIOS F1 WITH(NOLOCK) ON Transactions.FolioNumber = F1.FolioNumber 
    INNER JOIN Accounts WITH(NOLOCK) ON Transactions.AccountNumber = Accounts.AccountNumber
    WHERE showonbillflag=1
    AND (@AHotelCustomerNumber = -1 OR Transactions.HotelCustomerNumber = @AHotelCustomerNumber)
    AND Transactions.Payment in (12)
    and (Transactions.moveddeleted IS NULL or Transactions.MovedDeleted=0) and Transactions.FolioNumber= Folios.FolioNumber), 3) <> 0
OPEN db_cur

FETCH NEXT FROM db_cur INTO @tmpSaldo, @DummyReference, @DummyGuest, @DummyFullName, @DummyArrivalDate, @DummyDepartureDate, @DummyComp, @DummyAgency, @DummyCheckRef, @DummyStatus, @DummyLineType,
  @FolioNumber, @DummyLink, @DummyPhone, @DummyAddressType, @DummyWareName

WHILE @@FETCH_STATUS = 0
BEGIN
  UPDATE #tmp SET DepositAmount = @tmpSaldo WHERE FolioNumber = @FolioNumber
  IF @@ROWCOUNT = 0
    INSERT INTO #tmp (FolioNumber, DepositAmount) VALUES (@FolioNumber, @tmpSaldo)

  FETCH NEXT FROM db_cur INTO @tmpSaldo, @DummyReference, @DummyGuest, @DummyFullName, @DummyArrivalDate, @DummyDepartureDate, @DummyComp, @DummyAgency, @DummyCheckRef, @DummyStatus, @DummyLineType,
    @FolioNumber, @DummyLink, @DummyPhone, @DummyAddressType, @DummyWareName
END

CLOSE db_cur
DEALLOCATE db_cur

DECLARE db_cur CURSOR FOR
  SELECT Transactions.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)
  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
  WHERE Accounts.AccountType = 5
  AND (@AHotelCustomerNumber = -1 OR Transactions.HotelCustomerNumber = @AHotelCustomerNumber)
  GROUP BY Transactions.FolioNumber
  HAVING ROUND((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)), 3) <> 0
OPEN db_cur

FETCH NEXT FROM db_cur INTO @FolioNumber, @tmpSaldo
WHILE @@FETCH_STATUS = 0
BEGIN
  UPDATE #tmp SET InhouseAmount = @tmpSaldo WHERE FolioNumber = @FolioNumber
  IF @@ROWCOUNT = 0
    INSERT INTO #tmp (FolioNumber, InhouseAmount) VALUES (@FolioNumber, @tmpSaldo)
  FETCH NEXT FROM db_cur INTO @FolioNumber, @tmpSaldo
END

CLOSE db_cur
DEALLOCATE db_cur

SELECT * FROM #tmp
DROP TABLE #tmp