CREATE PROCEDURE [dbo].[SMSCountReportMisc] @AFromDate SMALLDATETIME, @AToDate SMALLDATETIME, @ASMSProviderName VARCHAR(250) AS
/*
  Created by Kent Johansen, September 2023
  Changes

*/

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
  GROUP BY CustomerNumber, LastName, HotelNumber
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 SUBSTRING(CountryCode, 1, 1)  <> '+' THEN '+' + CountryCode ELSE CountryCode END AS CountryCode, SUM(NumberOfSMSSend)
    FROM SMSlog WITH (NOLOCK) 
    WHERE State = 0 
    AND Hotelnumber = @HotelNumber 
    AND DateStamp BETWEEN @AFromDate AND @AToDate
    GROUP BY CountryCode

    INSERT INTO #SMSCount (HotelName, PartnerId, CountryCode, Cnt) SELECT @LastName, @PartnerId, 'OTHER', 0
  END

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

INSERT INTO #SMSCount (HotelName, PartnerId, CountryCode, Cnt)
SELECT 'ĊċĊċĊċĊċSum', @PartnerId, CountryCode, SUM(cnt)
FROM #SMSCount
GROUP BY #SMSCount.CountryCode

CLOSE db_cur
DEALLOCATE db_cur

SELECT DB_NAME() AS DBName, *
FROM #SMSCount