CREATE PROCEDURE dbo.MonaStatistic @AHotelCustomerNumberCSV VARCHAR(100), @AResTypeCSV VARCHAR(100), @AFromDate DATETIME, @AToDate DATETIME, @ASMSTypeCSV VARCHAR(100) AS
/*
  Created by Kent Johansen, november 2021
  Changes
  KJ 20220503: Ext: Added SMS template type
  20220307: Added AResTypeCSV

*/

SELECT COUNT(*) AS cnt, Personel.UserTypeNumber, Followup.SubType2, ReservationMain.FkCustomerHotel AS HotelCustomerNumber, NULL AS SMSType
FROM FollowUp WITH (NOLOCK)
INNER JOIN Personel WITH (NOLOCK) ON Personel.UserIdNumber = Followup.CreatedBy
INNER JOIN ReservationFollowupXTab WITH (NOLOCK) ON ReservationFollowupXTab.FollowUpNumber = Followup.FollowupNumber
INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = ReservationFollowupXTab.ReferenceNumber
  AND (ISNULL(@AHotelCustomerNumberCSV, '') = '' OR ReservationMain.FkCustomerHotel IN (SELECT Number FROM dbo.iter_intlist_to_table(@AHotelCustomerNumberCSV)))
  AND (ISNULL(@AResTypeCSV, '') = '' OR ReservationMain.ReservationType IN (SELECT Number FROM dbo.iter_intlist_to_table(@AResTypeCSV)))
WHERE ISNULL(Followup.SubType2, 0) > 0
AND FollowUp.SubType2 <> 9 -- fst2SMSSend
AND Followup.CreatedDate BETWEEN @AFromDate AND @AToDate
GROUP BY Personel.UserTypeNumber, Followup.SubType2, ReservationMain.FkCustomerHotel

UNION

SELECT COUNT(*), Personel.UserTypeNumber, 9, ReservationMain.FkCustomerHotel, SMSLog.SMSType
FROM SMSLog WITH (NOLOCK)
INNER JOIN Personel WITH (NOLOCK) ON Personel.UserIdNumber = SMSLog.UserIDNumber
INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = SMSLog.FK_ReferenceNumber
  AND (ISNULL(@AHotelCustomerNumberCSV, '') = '' OR ReservationMain.FkCustomerHotel IN (SELECT Number FROM dbo.iter_intlist_to_table(@AHotelCustomerNumberCSV)))
  AND (ISNULL(@AResTypeCSV, '') = '' OR ReservationMain.ReservationType IN (SELECT Number FROM dbo.iter_intlist_to_table(@AResTypeCSV)))
  AND SMSLog.FK_ReferenceNumber > 0
WHERE SMSLog.DateStamp BETWEEN @AFromDate AND @AToDate
AND ISNULL(SMSLog.SMSType, -1) >= 0
AND (ISNULL(@ASMSTypeCSV, '') = '' OR SMSLog.SMSType IN (SELECT Number FROM dbo.iter_intlist_to_table(@ASMSTypeCSV)))
GROUP BY Personel.UserTypeNumber, ReservationMain.FkCustomerHotel, SMSLog.SMSType

UNION

SELECT COUNT(*), Personel.UserTypeNumber, 1, Transactions.HotelCustomerNumber, NULL
FROM Transactions WITH (NOLOCK)
INNER JOIN Personel WITH (NOLOCK) ON Personel.UserIdNumber = Transactions.UserIdNumber
  AND Personel.UserTypeNumber IN (5, 8, 9, 10, 11)
INNER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = Transactions.WareNumber
INNER JOIN WareGroups WITH (NOLOCK) ON WareGroups.WareGroupNumber = Ware.WareGroupNumber
  AND Waregroups.Payment = 1 -- Payment
INNER JOIN Folios WITH (NOLOCK) ON Folios.FolioNumber = Transactions.FolioNumber
INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = Folios.ReferenceNumber
  AND (ISNULL(@AResTypeCSV, '') = '' OR ReservationMain.ReservationType IN (SELECT Number FROM dbo.iter_intlist_to_table(@AResTypeCSV)))
WHERE Transactions.ShowOnBillFlag = 1
AND Transactions.MovedDeleted = 0
AND Transactions.DateStamp BETWEEN @AFromDate AND @AToDate
AND (ISNULL(@AHotelCustomerNumberCSV, '') = '' OR Transactions.HotelCustomerNumber IN (SELECT Number FROM dbo.iter_intlist_to_table(@AHotelCustomerNumberCSV)))
GROUP BY Personel.UserTypeNumber, Transactions.HotelCustomerNumber
ORDER BY SubType2, HotelCustomerNumber