CREATE PROCEDURE dbo.OwnerQuarterStatement @AStartDate CHAR(8), @AEndDate CHAR(8), @ARoomNumberCSV VARCHAR(8000), @AHotelNumber INT AS

/*
  Created by Kent Johansen December 2024
  Returns data for dac7 data for the Danish IRS

  Changes:
  
*/
SET NOCOUNT ON

DECLARE @StartDateTime DATETIME
DECLARE @EndDateTime DATETIME

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

SELECT WareNumber
INTO #OwnerWare
FROM Ware WITH (NOLOCK)
WHERE Type IN (5, 7, 8, 9) -- 5: wtOwner 7: wtOwnerElectricity 8: wtOwnerCleaning 9: wtOwnerAdditional

SELECT Customer.FirstName, Customer.LastName, Customer.Address1, Customer.Address2, Customer.Address3, Countries.CountryCode, Customer.ZipCodeOverwrite, Customer.CityOverWrite, Customer.CustomerNumber, Ware.Type AS WareType, Ware.WareName, 
  Rooms.RoomNumber, Rooms.Number, Rooms.HotelNumber, Customer.MemberCardNumber, 
  (SELECT dbo.GetReferenceNumberFromTransactionNote(Transactions.Note)) AS Ref, (SELECT dbo.GetGuestNumberFromTransactionNote(Transactions.Note)) AS Guest,
  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*-1) AS PriceOverwrite,
  CASE WHEN Ware.Type = 5 THEN COUNT(DISTINCT Transactions.Note) ELSE 0 END AS NumberOfBookings, 
  DATEPART(QUARTER, Transactions.DateStamp) AS NumQuarter, 0 AS NumberOfNights
INTO #tmp
FROM Transactions WITH (NOLOCK)
INNER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = Transactions.WareNumber
INNER JOIN Accounts WITH (NOLOCK) ON Accounts.AccountNumber = Transactions.AccountNumber
INNER JOIN Folios WITH (NOLOCK) ON Folios.FolioNumber = Transactions.FolioNumber
INNER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddress.AddressNumber = Folios.ReservationAddressNumber
INNER JOIN Rooms WITH (NOLOCK) ON Rooms.RoomNumber = ReservationAddress.FK_RoomNumber
  AND Rooms.HotelNumber = @AHotelNumber 
  AND ((@ARoomNumberCSV = '')  OR (Rooms.Number IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ARoomNumberCSV))))
INNER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = ReservationAddress.Link
INNER JOIN #OwnerWare ON #OwnerWare.WareNumber = Transactions.WareNumber
LEFT OUTER JOIN Countries WITH (NOLOCK) ON Countries.CountryNumber = Customer.CountryNumber
WHERE Transactions.DateStamp BETWEEN @StartDateTime AND @EndDateTime
AND Transactions.ShowOnBillFlag = 1
AND Transactions.MovedDeleted = 0
AND Transactions.Payment NOT IN (1, 14)
GROUP BY Customer.FirstName, Customer.LastName, Customer.Address1, Customer.Address2, Customer.Address3, Countries.CountryCode, Customer.ZipCodeOverwrite, Customer.CityOverWrite, Customer.CustomerNumber, Ware.Type, Ware.WareName,
  Rooms.RoomNumber, Rooms.Number, Rooms.HotelNumber, Customer.MemberCardNumber, Transactions.Note,
  DATEPART(QUARTER, Transactions.DateStamp)
ORDER BY Rooms.Number, DATEPART(QUARTER, Transactions.DateStamp)

UPDATE #tmp
SET NumberOfNights = ISNULL((SELECT dbo.OverNightDaysForOwnerReport(Ref, Guest)), 0)
WHERE WareType = 5

SELECT FirstName, LastName, Address1, Address2, Address3, CountryCode, ZipCodeOverwrite, CityOverWrite, CustomerNumber, WareType, WareName,
  RoomNumber, Number, HotelNumber, MemberCardNumber,
  ROUND(SUM(PriceOverwrite), 0) AS PriceOverwrite,
  SUM(NumberOfBookings) AS NumberOfBookings, 
  NumQuarter,
  SUM(NumberOfNights) AS NumberOfNights
FROM #tmp
GROUP BY FirstName, LastName, Address1, Address2, Address3, CountryCode, ZipCodeOverwrite, CityOverWrite, CustomerNumber, WareType, WareName,
  RoomNumber, Number, HotelNumber, MemberCardNumber,
  NumQuarter
ORDER BY Number, NumQuarter

DROP TABLE #OwnerWare
DROP TABLE #tmp