CREATE FUNCTION [dbo].[GetFolioAmountByWareType] (@ReferenceNumber INT, @GuestNumber INT, @FolioNumber INT, @AWareType INT,  @StartDate CHAR(8), @EndDate CHAR(8))
  RETURNS @tbl TABLE (
    Amount MONEY NOT NULL,
    Pieces INT NOT NULL) AS
/*
  Created By Kent Johansen june 2013
  WareType from WareTL
  Changes:

*/


BEGIN
  DECLARE @Total FLOAT
  DECLARE @TotalPieces INT

  SET @Total = 0
  SET @TotalPieces = 0
  
  DECLARE @PriceOverwrite FLOAT
  DECLARE @Pieces INT
  DECLARE @Debit BIT

  DECLARE db_cur CURSOR FOR
    SELECT SUM(PriceOverwrite), SUM(Pieces), Debit
    FROM Transactions (NOLOCK)
    INNER JOIN FOLIOS (NOLOCK) ON Transactions.FolioNumber = FOLIOS.FolioNumber 
      AND Folios.ReferenceNumber = @ReferenceNumber
      AND Transactions.GuestNumber = @GuestNumber 
      AND ((@FolioNumber = -1) OR (Transactions.FolioNumber = @FolioNumber))
    INNER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = Transactions.WareNumber
      AND Ware.Type = @AWareType
    WHERE ShowOnBillFlag = 1
    AND CONVERT(CHAR(8), DateStamp, 112) BETWEEN @StartDate AND @EndDate
    GROUP BY Debit
    ORDER BY Debit DESC
  OPEN db_cur

  FETCH NEXT FROM db_cur INTO @PriceOverwrite, @Pieces, @Debit

  WHILE @@FETCH_STATUS <> -1
  BEGIN
    IF @Debit = 1
    BEGIN
      SET @Total = @Total + @PriceOverwrite
      SET @TotalPieces = @TotalPieces + @Pieces
    END
    ELSE
    BEGIN
      SET @Total = @Total - @PriceOverwrite
      SET @TotalPieces = @TotalPieces + @Pieces
    END

    FETCH NEXT FROM db_cur INTO @PriceOverwrite, @Pieces, @Debit
  END

  CLOSE db_cur
  DEALLOCATE db_cur

  INSERT INTO @tbl VALUES (@Total, @TotalPieces)

  RETURN
END