CREATE PROCEDURE dbo.PostDeliveryReportAndNotification @AMessageID VARCHAR(255), @ADateStamp DATETIME, @ALogState INTEGER, @AMessage VARCHAR(2000), @AText VARCHAR(2000), @ANumberOfSMSSend INTEGER = -1 AS
/*
  Created by Arne Munch, March 2020
  Post a Delivery Report or Notification received by the Callback Service

  Changes:
  AMH 20230124: Create FollowUp with SubType = fustChanged
  AMH 20220914: Allow hyphen in MessageID and none additional followup text
  AMH 20201023: Add part number to message text for multi part reports
  AMH 20200707: Strip Sequence Number from MessageID
  AMH 20200420: Insert CallbackService in Personel if it's not already there
  AMH 20200320: Added retrieval of Identity Number for SMSLog

*/

SET NOCOUNT ON

DECLARE
@HotelNumber INT,
@UserIDNumber INT,
@CountryCode VARCHAR(10),
@PhoneNumber VARCHAR(40),
@ReferenceNumber INT,
@GuestNumber INT,
@NumberOfSMSSend INT,
@p INT = LEN(@AMessageID)

WHILE @p > 0
BEGIN
  -- SELECT values FROM SMSLog
  DECLARE #SMSLogCur CURSOR FOR
    SELECT HotelNumber, CountryCode, PhoneNumber, NumberOfSMSSend, FK_ReferenceNumber, FK_GuestNumber FROM dbo.SMSLog (NOLOCK)
    WHERE MessageID = LEFT(@AMessageID, @p) and State = 0

  OPEN #SMSLogCur
  
  FETCH NEXT FROM #SMSLogCur
    INTO @HotelNumber, @CountryCode, @PhoneNumber, @NumberOfSMSSend, @ReferenceNumber, @GuestNumber
  
  IF @@FETCH_STATUS <> 0
  BEGIN
    IF @p = LEN(@AMessageID)
    BEGIN
      -- Does the ID appear to have a Sequence Number in the end?
      WHILE (@p > 0) AND (CHARINDEX(SUBSTRING(@AMessageID, @p, 1), '0123456789-') > 0)
        SET @p -= 1

      -- If delimiter is present then strip the Sequence Number and try again
      IF (@p > 1) AND (SUBSTRING(@AMessageID, @p, 1) = '$')
      BEGIN
        DECLARE @MessagePart INT = CONVERT(INT, SUBSTRING(@AMessageID, @p+1, LEN(@AMessageID)-@p))+1
        IF (@MessagePart > 0) AND (LEFT(@AText, 12) = 'The message ')
          SET @AText = LEFT(@AText, 12) + 'part (' + CAST(@MessagePart AS VARCHAR) + '/' + CAST(@ANumberOfSMSSend AS VARCHAR) + ') ' + RIGHT(@AText, LEN(@AText)-12)
        SET @p -= 1
      END
      ELSE
        SET @p = -1
    END
    ELSE
      SET @p = -1
  END
  ELSE
    SET @p = 0

  CLOSE #SMSLogCur 
  DEALLOCATE #SMSLogCur
END

IF @p = -1
BEGIN
  SET @HotelNumber = 0
  SET @CountryCode = ''
  SET @PhoneNumber = ''
  SET @NumberOfSMSSend = 1
  SET @ReferenceNumber = 0
  SET @GuestNumber = 0
END
ELSE IF @ANumberOfSMSSend <> -1
  SET @NumberOfSMSSend = @ANumberOfSMSSend

DECLARE @UserIDCode VARCHAR(100) = 'CallbackService'

SELECT @UserIDNumber = UserIDNumber FROM dbo.Personel (NOLOCK) WHERE UserIDCode = @UserIDCode
IF @UserIDNumber IS NULL
BEGIN
  DECLARE @UserTypeNumber INT = 11 -- utCallbackService
  DECLARE @PassWd VARCHAR(100) = 'Picasso'

  EXEC GetNextNumber 'PERSONELIDENTITY', 1, @UserIDNumber OUTPUT

  INSERT INTO dbo.Personel (UserIDNumber, UserIDCode, FirstName, LastName, PasswordHash, UserTypeNumber) VALUES (@UserIdNumber, @UserIDCode, @UserIDCode, @UserIDCode, dbo.GetPasswordHash(@PassWd), @UserTypeNumber)
END

DECLARE @SMSLog INT
EXEC GetNextNumber 'TEXTMESSAGEIDENTITY', 1, @SMSLog OUTPUT

INSERT INTO dbo.SMSLog (HotelNumber, DateStamp, [State], UserIDNumber, CountryCode, PhoneNumber, Text, MessageID, Message, NumberOfSMSSend, PK_SMSLog, FK_ReferenceNumber, FK_GuestNumber)
VALUES (@HotelNumber, @ADateStamp, @ALogState, @UserIDNumber, @CountryCode, @PhoneNumber, @AText, @AMessageID, @AMessage, @NumberOfSMSSend, @SMSLog, @ReferenceNumber, @GuestNumber)

IF @ReferenceNumber > 0
BEGIN
  DECLARE @FollowupText VARCHAR(8000)
  IF @AText <> ''
    SET @FollowupText = @AMessage + ' - ' + @AText
  ELSE
    SET @FollowupText = @AMessage

  DECLARE @SystemDateTime DATETIME
  DECLARE @NewFollowUpNumber INT

  EXEC SystemDateTime '', @SystemDateTime OUTPUT

  INSERT INTO FollowUp ([Date], Type, Priority, State, Note, CreatedDate, CreatedBy, SubType, HotelNumber)
  VALUES (@SystemDateTime, 5, 12, 3, @FollowupText, @SystemDateTime, @UserIdNumber, 2, @HotelNumber)
  SELECT @NewFollowUpNumber = @@identity
  INSERT INTO ReservationFollowUpXTab (ReferenceNumber, GuestNumber, FollowUpNumber) VALUES (@ReferenceNumber, @GuestNumber, @NewFollowUpNumber)
END
