CREATE PROCEDURE dbo.GDPRReport @AHotelCustomerNumberCSV VARCHAR(100) AS
/*
  Created by Kent Johansen, October 2021
  Return count for anonymized data for report

  Changes:
*/

SET NOCOUNT ON

SELECT 1 AS CountType, COUNT(*) AS cnt, ISNULL(CustomerAnonymized, 0) AS IsAnonymized, CustomerTypeNumber
FROM Customer WITH (NOLOCK)
WHERE CustomerTypeNumber IN (1,8)
GROUP BY ISNULL(CustomerAnonymized, 0), CustomerTypeNumber

UNION

SELECT 2, COUNT(*) AS cnt, ISNULL(ReservationAddress.AddressAnonymized, 0), ReservationAddress.AddressType
FROM ReservationAddress WITH (NOLOCK)
INNER JOIN ReservationAddressXTab WITH (NOLOCK) ON ReservationAddressXTab.AddressNumber = ReservationAddress.AddressNumber
INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = ReservationAddressXTab.ReferenceNumber
  AND Reservations.GuestNumber = ReservationAddressXTab.GuestNumber
INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = Reservations.ReferenceNumber
  AND FkCustomerHotel IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelCustomerNumberCSV))
WHERE ReservationAddress.AddressType = 1
GROUP BY ISNULL(ReservationAddress.AddressAnonymized, 0), ReservationAddress.AddressType

UNION

SELECT 3, COUNT(*) AS cnt, ISNULL(ReservationAddress.AddressAnonymized, 0), ReservationAddress.AddressType
FROM ReservationAddress WITH (NOLOCK)
INNER JOIN Folios WITH (NOLOCK) ON Folios.ReservationAddressNumber = ReservationAddress.AddressNumber
  AND ISNULL(Folios.ReferenceNumber, 0) = 0 
  AND ISNULL(Folios.ReservationAddressNumber, 0) > 0
  AND Folios.HotelCustomerNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelCustomerNumberCSV))
INNER JOIN Transactions WITH (NOLOCK) ON Transactions.FolioNumber = Folios.FolioNumber
WHERE ReservationAddress.AddressType = 1
GROUP BY ISNULL(ReservationAddress.AddressAnonymized, 0), ReservationAddress.AddressType
ORDER BY CountType, cnt, IsAnonymized, CustomerTypeNumber