CREATE PROCEDURE dbo.OwnerYearStatement @AStartDate CHAR(8), @AEndDate CHAR(8), @ARoomNumberCSV VARCHAR(8000), @AHotelNumber INT, @ACustomerNumberCSV VARCHAR(8000), @AGroupByWare BIT,
  @AWareGroupCSV VARCHAR(8000), @AWareCSV VARCHAR(8000), @AExcludeOwnerWare BIT AS

/*
  Created by Kent Johansen August 2010
  Returns list of transactions on owner folios

  Changes:
    KJ 20130107: Bug: Fix for "Can not convert the value...."
    KJ 20101222: Ext: Added @AExcludeOwnerWare to exclude all owner wares in specified list
  
*/
SET NOCOUNT ON

DECLARE @KeyValue VARCHAR(2000)
DECLARE @OldKeyValue VARCHAR(2000)

CREATE TABLE #OwnerWare (
  WareNumber INT 
)

DECLARE db_curx CURSOR FOR
  SELECT KeyValue, OldKeyValue FROM HotelSetup(NOLOCK) 
  LEFT OUTER JOIN HotelSetupLog(NOLOCK) ON HotelSetupLog.HotelSetupNumber = HotelSetup.HotelSetupNumber
  WHERE (SearchKey = 'ElectricityWareName' OR SearchKey = 'CleaningWareName' OR SearchKey = 'AdditionalWareName')
OPEN db_curx

FETCH NEXT FROM db_curx INTO @KeyValue, @OldKeyValue

WHILE @@FETCH_STATUS = 0
BEGIN
  IF dbo.IsReallyNumeric (@KeyValue) = 1
    INSERT INTO #OwnerWare VALUES (@KeyValue)
  IF dbo.IsReallyNumeric (@OldKeyValue) = 1
    INSERT INTO #OwnerWare VALUES (@OldKeyValue)
  FETCH NEXT FROM db_curx INTO @KeyValue, @OldKeyValue
END

CLOSE db_curx
DEALLOCATE db_curx

DECLARE db_cur1 CURSOR FOR
  SELECT WareNumber
  FROM Ware(NOLOCK)
  WHERE Type IN (7,8,9)
OPEN db_cur1

FETCH NEXT FROM db_cur1 INTO @KeyValue

WHILE @@FETCH_STATUS = 0
BEGIN
  INSERT INTO #OwnerWare VALUES (@KeyValue)
  FETCH NEXT FROM db_cur1 INTO @KeyValue
END

CLOSE db_cur1
DEALLOCATE db_cur1

DECLARE @StartDateTime DATETIME
DECLARE @EndDateTime DATETIME

SET @StartDateTime = CONVERT(DATETIME, @AStartDate + ' 00:00:00:000') 
SET @EndDateTime = CONVERT(DATETIME, @AEndDate + ' 23:59:00:997') 

IF @AGroupByWare = 1
BEGIN
  SELECT t.WareNumber, w.WareName, c.CustomerNumber, r.RoomNumber, 
  SUM(t.PriceOverwrite * (CASE t.Debit WHEN 1 THEN 1 ELSE -1 END)*CASE WHEN a.AccountType IN (4,5,6) THEN 1 ELSE 1+(VatValue/100) END)*-1 AS PriceOverwrite
  FROM Transactions t WITH (NOLOCK)
  INNER JOIN Ware w WITH (NOLOCK) ON w.WareNumber = t.WareNumber
    AND ((@AWareCSV = '')  OR (w.WareNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AWareCSV))))
  INNER JOIN WareGroups wg WITH (NOLOCK) ON wg.WareGroupNumber = w.WareGroupNumber
    AND ((@AWareGroupCSV = '')  OR (wg.WareGroupNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AWareGroupCSV))))
  INNER JOIN Accounts a WITH (NOLOCK) ON a.AccountNumber = t.AccountNumber
  INNER JOIN Folios f WITH (NOLOCK) ON f.FolioNumber = t.FolioNumber
  INNER JOIN ReservationAddress ra WITH (NOLOCK) ON ra.AddressNumber = f.ReservationAddressNumber
    AND ((@ACustomerNumberCSV = '')  OR (ra.Link IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ACustomerNumberCSV))))
  INNER JOIN Rooms r WITH (NOLOCK) ON r.RoomNumber = ra.FK_RoomNumber
    AND r.HotelNumber = @AHotelNumber 
    AND ((@ARoomNumberCSV = '')  OR (r.Number IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ARoomNumberCSV))))
  INNER JOIN Customer c WITH (NOLOCK) ON c.CustomerNumber = ra.Link
  LEFT OUTER JOIN Countries WITH (NOLOCK) ON Countries.CountryNumber = c.CountryNumber
  WHERE t.DateStamp BETWEEN @StartDateTime AND @EndDateTime
  AND t.ShowOnBillFlag = 1
  AND t.MovedDeleted = 0
  AND t.Payment NOT IN (1, 14)
  GROUP BY t.WareNumber, w.WareName, c.CustomerNumber, r.RoomNumber
END
ELSE
BEGIN
  SELECT c.FirstName, c.LastName, c.Address1, c.Address2, c.Address3, Countries.CountryCode, c.ZipCodeOverwrite, c.CityOverWrite, c.CustomerNumber,
    t.DateStamp, t.TextOverwrite, t.WareNumber, 
    t.PriceOverwrite * (CASE t.Debit WHEN 1 THEN 1 ELSE -1 END)*CASE WHEN a.AccountType IN (4,5,6) THEN 1 ELSE 1+(VatValue/100) END*-1 AS PriceOverwrite,--t.PriceOverwrite, 
    r.RoomNumber, r.Number, r.HotelNumber--,   *
  FROM Transactions t WITH (NOLOCK)
  INNER JOIN Ware w WITH (NOLOCK) ON w.WareNumber = t.WareNumber
    AND ((@AWareCSV = '')  OR (w.WareNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AWareCSV))))
    AND ((ISNULL(@AExcludeOwnerWare, 0) = 0) OR (w.WareNumber NOT IN (SELECT WareNumber FROM #OwnerWare)))
  INNER JOIN WareGroups wg WITH (NOLOCK) ON wg.WareGroupNumber = w.WareGroupNumber
    AND ((@AWareGroupCSV = '')  OR (wg.WareGroupNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AWareGroupCSV))))
  INNER JOIN Accounts a WITH (NOLOCK) ON a.AccountNumber = t.AccountNumber
  INNER JOIN Folios f WITH (NOLOCK) ON f.FolioNumber = t.FolioNumber
  INNER JOIN ReservationAddress ra WITH (NOLOCK) ON ra.AddressNumber = f.ReservationAddressNumber
    AND ((@ACustomerNumberCSV = '')  OR (ra.Link IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ACustomerNumberCSV))))
  INNER JOIN Rooms r WITH (NOLOCK) ON r.RoomNumber = ra.FK_RoomNumber
    AND r.HotelNumber = @AHotelNumber 
    AND ((@ARoomNumberCSV = '')  OR (r.Number IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ARoomNumberCSV))))
  INNER JOIN Customer c WITH (NOLOCK) ON c.CustomerNumber = ra.Link
  LEFT OUTER JOIN Countries WITH (NOLOCK) ON Countries.CountryNumber = c.CountryNumber
  WHERE t.DateStamp BETWEEN @StartDateTime AND @EndDateTime
  AND t.ShowOnBillFlag = 1
  AND t.MovedDeleted = 0
  AND t.Payment NOT IN (1, 14)
  ORDER BY r.Number, ra.Link, t.DateStamp
END

DROP TABLE #OwnerWare