CREATE PROCEDURE dbo.SMSCountReport @AFromDate SMALLDATETIME, @AToDate SMALLDATETIME, @ASMSProviderName VARCHAR(250) AS
/*
  Created by Kent Johansen, March 2022
  Changes
    KJ 20220426: Added sms provider name
    KJ 20220428: Added PartnerId
*/

CREATE TABLE #SMSCount
(
  HotelName VARCHAR(100),
  PartnerId VARCHAR(500),
  CountryCode VARCHAR(10),
  Cnt INT
)

DECLARE @HCN INT
DECLARE @LastName VARCHAR(100)
DECLARE @HotelNumber INT

DECLARE @Enabled BIT
DECLARE @ProviderBool BIT
DECLARE @PartnerId VARCHAR(500)

DECLARE db_cur CURSOR LOCAL FAST_FORWARD FOR 
  SELECT CustomerNumber, LastName, HotelNumber
  FROM Customer (NOLOCK)
  WHERE CustomerTypeNumber = 7
  AND ISNULL(HotelNumber, -1) >= 0
OPEN db_cur
FETCH NEXT FROM db_cur INTO @HCN, @LastName, @HotelNumber

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @Enabled = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'ShortMessageServicesEnabled'
  IF ISNULL(@Enabled, 0) = 0
    SELECT @Enabled = dbo.GetHotelSetupKeyValue ('ShortMessageServicesEnabled', @HCN)

  IF @ASMSProviderName <> ''
  BEGIN
    IF dbo.GetHotelSetupKeyValue ('Interface_ShortMessageServices_ProviderName', @HCN) = @ASMSProviderName 
      SET @ProviderBool = 1
    ELSE
      SET @ProviderBool = 0    
  END
  ELSE
    SET @ProviderBool = 1
    
  IF ISNULL(@Enabled, 0) = 1 AND @ProviderBool = 1
  BEGIN
    SELECT @PartnerId = dbo.GetHotelSetupKeyValue ('Interface_ShortMessageServices_PartnerId', @HCN)
    
    INSERT INTO #SMSCount (HotelName, PartnerId, CountryCode, Cnt)
    SELECT @LastName, @PartnerId, 
      CASE 
        WHEN CountryCode IN ('+45', '45') THEN 'DK'
        WHEN CountryCode IN ('+46', '46') THEN 'SE'
        WHEN CountryCode IN ('+47', '47') THEN 'NO'
        WHEN CountryCode IN ('+353', '353') THEN 'IE'
        WHEN CountryCode IN ('+354', '354') THEN 'IS'
        ELSE 'OTHER'
      END, 
    SUM(NumberOfSMSSend)
    FROM SMSlog WITH (NOLOCK) 
    WHERE State = 0 
    AND Hotelnumber = @HotelNumber 
    AND DateStamp BETWEEN @AFromDate AND @AToDate
    GROUP BY CountryCode

    IF @@ROWCOUNT = 0
    BEGIN
      INSERT INTO #SMSCount (HotelName, PartnerId, CountryCode, Cnt) SELECT @LastName, @PartnerId, 'DK', 0
      INSERT INTO #SMSCount (HotelName, PartnerId, CountryCode, Cnt) SELECT @LastName, @PartnerId, 'SE', 0
      INSERT INTO #SMSCount (HotelName, PartnerId, CountryCode, Cnt) SELECT @LastName, @PartnerId, 'NO', 0
      INSERT INTO #SMSCount (HotelName, PartnerId, CountryCode, Cnt) SELECT @LastName, @PartnerId, 'IE', 0
      INSERT INTO #SMSCount (HotelName, PartnerId, CountryCode, Cnt) SELECT @LastName, @PartnerId, 'IS', 0
      INSERT INTO #SMSCount (HotelName, PartnerId, CountryCode, Cnt) SELECT @LastName, @PartnerId, 'OTHER', 0
    END
  END

  FETCH NEXT FROM db_cur INTO @HCN, @LastName, @HotelNumber
END

CLOSE db_cur
DEALLOCATE db_cur

SELECT DB_NAME() AS DBName, *
FROM #SMSCount
PIVOT
(
  SUM(Cnt) FOR CountryCode IN ([DK], [SE], [NO], [IE], [IS], [Other]) 
) AS pvt
DROP TABLE #SMSCount