IF NOT EXISTS (SELECT TOP 1 1 FROM [System].[Settings] WHERE SettingName = 'SenderEmailAddress')
BEGIN
	INSERT INTO [System].[Settings](SettingName,SettingValue,IsCancelled,SettingDescription)
	VALUES('SenderEmailAddress','info@neoafrica.com',0,NULL)
END
GO
SELECT * FROM [System].[Settings] WHERE SettingName = 'SenderEmailAddress'
GO

CREATE OR ALTER PROCEDURE [Crm].[Pr_MessageCreate]
(
	@EntityID INT,
	@ItemID BIGINT,
	@MessageTypeID TINYINT,
	@Recipients VARCHAR(MAX),
	@CC VARCHAR(MAX),
	@BCC VARCHAR(MAX),
	@Subject VARCHAR(1000),
	@Message VARCHAR(MAX),
	@IsHtml BIT,
	@MessageByUserName VARCHAR(200)
)
AS
BEGIN
	DECLARE 
		@ReturnID BIGINT
		,@UserID BIGINT
		,@Sender VARCHAR(255)
		
	SELECT
		@ReturnID = -1
		,@UserID = -1

	IF EXISTS (SELECT * FROM System.Entities WHERE EntityID = @EntityID)
	BEGIN	
		SELECT @UserID = Security.Fn_GetUserID(@MessageByUserName)
				
		--SELECT @Sender = Email
		--FROM Security.Users 
		--WHERE UserID = 1 /*System Admin - info@legiti-mate.net*/

		SELECT TOP 1 @Sender = SettingValue FROM [System].[Settings] WHERE SettingName = 'SenderEmailAddress'
	
		IF ISNULL(@UserID, 0) <> 0
		BEGIN
			INSERT INTO Crm.Messages(MessageTypeID, EntityID, ItemID, Sender, Recipients, CC, BCC, Subject, Message, IsHtml, CreatedByID, CreatedDate, UpdatedByID, UpdatedDate)
			SELECT @MessageTypeID, @EntityID, @ItemID, @Sender, @Recipients, @CC, @BCC, @Subject, @Message, @IsHtml, @UserID, GETDATE(), @UserID, GETDATE()
			
			SELECT @ReturnID = SCOPE_IDENTITY()
		END
		ELSE
		BEGIN
			EXEC System.Pr_SysErrorThrow 'The user provided does not exist', 11
		END
	END
	ELSE
	BEGIN
		EXEC System.Pr_SysErrorThrow 'The entity provided does not exist', 11
	END

	SELECT @ReturnID, @UserID
END
GO

CREATE OR ALTER PROCEDURE [App].[Pr_AdjudicationRemoveApplication]
	@ApplicationDetailID BIGINT,
	@AdjudicationDetailID BIGINT,
	@Reason VARCHAR(1000),
	@UserID BIGINT
AS
BEGIN	
	SET NOCOUNT ON;

	/********************************************************************************************
	Get user details and perform security checks
	*********************************************************************************************/	
	DECLARE @EntityIDToCheck INT = 1007 /*AdjudicationDetails*/
	DECLARE @ItemIDToCheck BIGINT = @AdjudicationDetailID
	DECLARE @Action VARCHAR(255) = 'Update'
	DECLARE @OfficeLocationID BIGINT, @DivisionTypeID SMALLINT, @ProvinceID SMALLINT, @RegionID SMALLINT, @HasAccess BIT
	
	EXEC App.Pr_GetUserLocationAndSecurity @UserID, @EntityIDToCheck, @ItemIDToCheck, @Action, @OfficeLocationID OUTPUT, @DivisionTypeID OUTPUT, @ProvinceID OUTPUT, @RegionID OUTPUT, @HasAccess OUTPUT

	IF @HasAccess = 0
	BEGIN
		RAISERROR('User location and security error', 16, 1)
		RETURN
	END	
	
	/********************************************************************************************
	Remove application
	*********************************************************************************************/	
	DECLARE @ApplicationNumber VARCHAR(20) = NULL
	DECLARE @WorkflowInstanceID BIGINT = NULL
	DECLARE @WorkflowInstanceTaskID BIGINT = NULL
	DECLARE @ErrorMessage VARCHAR(2000) = NULL
	DECLARE @UserName VARCHAR(100) = Security.Fn_GetUserNameByID(@UserID)

	SELECT @WorkflowInstanceID = WorkflowInstanceID, @ApplicationNumber = ApplicationNumber
	FROM App.ApplicationDetails
	WHERE ApplicationDetailID = @ApplicationDetailID

	/*
	Make sure action is allowed
	*/
	IF NOT EXISTS(
		SELECT * 
		FROM App.ApplicationDetails Ard
			INNER JOIN Wf.WorkflowInstances Wi ON Wi.WorkflowInstanceID = Ard.WorkflowInstanceID
		WHERE 
			ApplicationDetailID = @ApplicationDetailID 
			AND ApplicationStatusID = 6 /*Adjudication*/ 
			AND ApplicationSubStatusID IN (9 /*Awaiting Adjudication Agenda Finalisation*/, 10 /*Awaiting Adjudication Outcome*/)
			AND Wi.WorkflowTemplateStepID = 1013 /*Agenda Officer Finalises Adjudication Meeting Agenda*/
		)
	BEGIN
		RAISERROR('The application is in the wrong status. ',16,1)
		RETURN
	END	

	/*
	reset any old ApplicationAdjudicationDecisions to inactive
	*/
	UPDATE App.ApplicationAdjudicationDecisions
	SET 
		IsActiveAdjudication = 0, 
		AdjudicationDecisionID=NULL
	FROM App.ApplicationAdjudicationDecisions Aad
		INNER JOIN app.ApplicationAdjudications Aa ON Aa.ApplicationAdjudicationID = Aad.ApplicationAdjudicationID
	WHERE ApplicationDetailID = @ApplicationDetailID
		AND AdjudicationDetailID = @AdjudicationDetailID
		AND AdjudicationDecisionID IS NULL

	/*
	reset the application request record
	*/
	UPDATE App.ApplicationDetails
	SET AdjudicationDetailID = NULL,
		IsUpliftmentRequired=0,
		IsAdjustmentRequired=0,
		IsCancellationRequired=0
	WHERE ApplicationDetailID = @ApplicationDetailID

	/*
	set the current decisions to postponed
	*/
	DELETE FROM App.ApplicationAdjudications 
	WHERE 
		ApplicationDetailID = @ApplicationDetailID			
		AND AdjudicationDetailID = @AdjudicationDetailID

	/*
	Send SMS
	*/
	DECLARE @ApplicantTellNumber VARCHAR(100) = ''
	DECLARE @MeetingDate DATETIME = ''
	DECLARE @Sender VARCHAR(255)

	SET @ApplicantTellNumber = (SELECT Ad.TelCell
								FROM App.ApplicationDetails Ard
								INNER JOIN app.ApplicationApplicantDetails Ad ON Ard.ApplicationDetailID = Ad.ApplicationDetailID
								where Ard.ApplicationDetailID = @ApplicationDetailID)

	IF ISNULL(@ApplicantTellNumber, '') <> ''
	BEGIN
		SET @ApplicantTellNumber = System.Fn_GetFormattedCellNumber (@ApplicantTellNumber)

		SET @MeetingDate = (SELECT CAST(MeetingDate AS DATETIME)
							FROM app.AdjudicationDetails
							where AdjudicationDetailID = @AdjudicationDetailID)

		SELECT TOP 1 @Sender = SettingValue FROM [System].[Settings] WHERE SettingName = 'SenderEmailAddress'
		
		INSERT INTO [Crm].[Messages] (MessageTypeID, EntityID, ItemID, Sender, Recipients, CC, BCC, Subject, Message, IsHtml, SentDate, Retries, IsCancelled, CreatedByID, CreatedDate, UpdatedByID, UpdatedDate)
		VALUES (2, 1007, @ApplicationDetailID, @Sender, @ApplicantTellNumber, '', '', 'Application received SMS', @ApplicationNumber+' refers. The application will not be considered on ' + CONVERT(varchar, @MeetingDate, 106) + '. You will receive an updated message to reschedule the hearing.', 0, null, 0, 0, 1, GETDATE(), 1, GETDATE())
	END
		
	/*
	Recalculate the steps and then send the workflow back to the scheduling step
	*/
	EXEC Wf.Pr_WorkflowInstanceRequiredStepsCalculate @WorkflowInstanceID
				
	DECLARE @CurrentlyAssignedToTaskID BIGINT

	SELECT @WorkflowInstanceTaskID = Wit.WorkflowInstanceTaskID, @CurrentlyAssignedToTaskID = Wit.AssignedToID
	FROM App.ApplicationDetails Ard
		INNER JOIN Wf.WorkflowInstances Wi ON Wi.WorkflowInstanceID = Ard.WorkflowInstanceID
		INNER JOIN Wf.WorkflowInstanceTasks Wit ON Wit.WorkflowInstanceID = Wi.WorkflowInstanceID
	WHERE Ard.ApplicationDetailID = @ApplicationDetailID
		AND Wit.WorkflowInstanceTaskStatusID = 1 /*in progress*/
	
	IF @UserID <> @CurrentlyAssignedToTaskID
	BEGIN
		UPDATE Wf.WorkflowInstanceTasks
		SET AssignedToID = @UserID
		WHERE WorkflowInstanceTaskID = @WorkflowInstanceTaskID
	END

	EXEC Wf.Pr_WorkflowInstanceTaskAction @WorkflowInstanceTaskID, 5 /*Requested Correction*/, @UserName, 1012 /*Adjudication Queue (Add application to adjudication)*/, @Reason, @ErrorMessage OUT
	
	IF ISNULL(@ErrorMessage, '') <> ''
	BEGIN
		RAISERROR(@ErrorMessage, 16, 1)
	END		
    
END
GO

CREATE OR ALTER PROCEDURE [App].[Pr_ApplicationInspectionDetailUpdate]
(
	@ApplicationDetailID BIGINT,
	@InspectionStatusID SMALLINT,
	@InspectorID BIGINT,
	@InspectionDateFrom DATETIME,
	@InspectionDateTo DATETIME,
	@UserID BIGINT
)
AS
BEGIN
	/********************************************************************************************
	Get user details and perform security checks
	*********************************************************************************************/	
	DECLARE @EntityIDToCheck INT = 1000 /*Application*/
	DECLARE @ItemIDToCheck BIGINT = @ApplicationDetailID
	DECLARE @Action VARCHAR(255) = 'Update'
	DECLARE @OfficeLocationID BIGINT, @DivisionTypeID SMALLINT, @ProvinceID SMALLINT, @RegionID SMALLINT, @HasAccess BIT
	
	EXEC App.Pr_GetUserLocationAndSecurity @UserID, @EntityIDToCheck, @ItemIDToCheck, @Action, @OfficeLocationID OUTPUT, @DivisionTypeID OUTPUT, @ProvinceID OUTPUT, @RegionID OUTPUT, @HasAccess OUTPUT

	IF @HasAccess = 0
	BEGIN
		RAISERROR('User location and security error', 16, 1)
		RETURN
	END	
	
	/********************************************************************************************
	Update inspection details
	*********************************************************************************************/	
	IF EXISTS(SELECT * FROM App.ApplicationInspectionDetails WHERE ApplicationDetailID = @ApplicationDetailID AND InspectionStatusID = @InspectionStatusID)
	BEGIN
		RETURN
	END

	IF @InspectionStatusID = 2 /*Awaiting inspection outcome*/
	BEGIN
		DECLARE @ApplicationNumber VARCHAR(20)
		DECLARE @CellNumber VARCHAR(20)
		DECLARE @Sender VARCHAR(255)

		SELECT @ApplicationDetailID = ApplicationDetailID, @ApplicationNumber = ApplicationNumber, @CellNumber = [System].[Fn_GetFormattedCellNumber](TelCell)	
		FROM App.Vw_ApplicationDetails AD
		WHERE AD.ApplicationDetailID = @ApplicationDetailID
		
		IF ISNULL(@CellNumber,'') <> ''
		BEGIN
			DECLARE @Message VARCHAR(165) 
			DECLARE @SentByUserID BIGINT = CAST(System.Fn_GetSettingValue('SystemUserID') AS BIGINT)
			DECLARE @MessageTemplateID INT
		
			SET @Message = 'Notification from the National Public Transport Regulator. ' + @ApplicationNumber + ' update: We will be inspecting your premises on ' + System.Fn_FormatDate(@InspectionDateFrom, 'dd MMM yyyy') + '. Thank you.'

			SELECT TOP 1 @Sender = SettingValue FROM [System].[Settings] WHERE SettingName = 'SenderEmailAddress'

			INSERT INTO Crm.Messages(MessageTypeID, EntityID, ItemID, Sender, Recipients, CC, BCC, Subject, Message, IsHtml, CreatedByID, CreatedDate, UpdatedByID, UpdatedDate)
			SELECT 2 /*sms*/, 1000 /*Application request details*/, @ApplicationDetailID, @Sender, @CellNumber, '', '', 'Inspection Scheduled SMS', @Message, 0, @SentByUserID, GETDATE(), @SentByUserID, GETDATE()	
		END
	END
		
	--todo status history and auditing
	UPDATE App.ApplicationInspectionDetails
	SET 
		InspectionStatusID = @InspectionStatusID,
		InspectorID = @InspectorID,
		InspectionDateFrom = @InspectionDateFrom,
		InspectionDateTo = @InspectionDateTo
	WHERE ApplicationDetailID = @ApplicationDetailID
	
END
GO

CREATE OR ALTER PROCEDURE [App].[Pr_ApplicationStartedSmsSender]
	@WorkflowInstanceID BIGINT
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @ApplicationNumber VARCHAR(20)
	DECLARE @ApplicationDetailID BIGINT
	DECLARE @CellNumber VARCHAR(20)
	DECLARE @EntityID INT
	DECLARE @ItemID BIGINT
	DECLARE @DivisionTypeID SMALLINT
	DECLARE @DivisionDescription VARCHAR(150)
	DECLARE @ProvinceID SMALLINT

	SELECT @ApplicationDetailID = ApplicationDetailID, @ApplicationNumber = ApplicationNumber, @CellNumber = [System].[Fn_GetFormattedCellNumber](TelCell)	
	FROM App.Vw_ApplicationDetails AD
	WHERE AD.WorkflowInstanceID = @WorkflowInstanceID

	SELECT @DivisionTypeID = DivisionTypeID, @ProvinceID = ProvinceID, @EntityID = WI.EntityID, @ItemID = WI.ItemID
	FROM App.Vw_ApplicationDetails_Light AD	
	INNER JOIN Wf.WorkflowInstances WI ON WI.WorkflowInstanceID = AD.WorkflowInstanceID
	WHERE AD.WorkflowInstanceID = @WorkflowInstanceID

	IF (@DivisionTypeID = 3)
	BEGIN
		SELECT @CellNumber = TatRequestByTelCell
		FROM App.ApplicationTATDetails
		WHERE ApplicationDetailID = @ItemID
	END

	SET @DivisionDescription = (CASE WHEN @DivisionTypeID = 1 THEN 'Provincial Regulatory Entity' WHEN @DivisionTypeID = 2 THEN 'National Public Transport Regulator' ELSE 'TAT' END)

	IF ISNULL(@CellNumber,'') <> ''
	BEGIN
		DECLARE @Message VARCHAR(165) 
		DECLARE @SentByUserID BIGINT = CAST(System.Fn_GetSettingValue('SystemUserID') AS BIGINT)
		DECLARE @MessageTemplateID INT
		DECLARE @Sender VARCHAR(255)

		IF EXISTS(SELECT * FROM Md.MessageMappings WHERE MessageMappingTypeID = 1 /*Application Received/Started SMS*/ AND DivisionTypeID = @DivisionTypeID AND ISNULL(ProvinceID,-1) = @ProvinceID)
		BEGIN
			SET @MessageTemplateID = (SELECT MessageTemplateID FROM Md.MessageMappings WHERE MessageMappingTypeID = 1 /*Application Received/Started SMS*/ AND DivisionTypeID = @DivisionTypeID AND ProvinceID = @ProvinceID)
			SET @Message = (SELECT TOP 1 Message FROM Crm.MessageTemplates WHERE MessageTemplateID = @MessageTemplateID)
		END
		ELSE BEGIN
			SET @MessageTemplateID = (SELECT MessageTemplateID FROM Md.MessageMappings WHERE MessageMappingTypeID = 1 /*Application Received/Started SMS*/ AND DivisionTypeID = @DivisionTypeID AND ProvinceID IS NULL)
			SET @Message = (SELECT TOP 1 Message FROM Crm.MessageTemplates WHERE MessageTemplateID = @MessageTemplateID)
		END
		SET @Message = System.GetFormattedEntity(@EntityID, @ItemID, @Message)
		SET @Message = REPLACE(@Message, '[[divisiontype]]', @DivisionDescription)
		--SET @Message = 'Notification from: Provincial Regulatory Entity. Your application has been received. Your application number is: ' + ISNULL(@ReferenceNumber,'') + '. Thank you.'

		SELECT TOP 1 @Sender = SettingValue FROM [System].[Settings] WHERE SettingName = 'SenderEmailAddress'

		INSERT INTO Crm.Messages(MessageTypeID, EntityID, ItemID, Sender, Recipients, CC, BCC, Subject, Message, IsHtml, CreatedByID, CreatedDate, UpdatedByID, UpdatedDate)
		SELECT 2 /*sms*/, 1000 /*Application request details*/, @ApplicationDetailID, @Sender, @CellNumber, '', '', 'Application received SMS', @Message, 0, @SentByUserID, GETDATE(), @SentByUserID, GETDATE()	
	END
END
GO

CREATE OR ALTER PROCEDURE [App].[Pr_GazetteDetailStatusUpdate]
(
	@UserID BIGINT
	,@Comments VARCHAR(1000)
	,@GazetteDetailID BIGINT
	,@GazetteDetailStatusID SMALLINT
	,@IsTransactionRequired BIT = 1
)	
AS
BEGIN
	SET NOCOUNT ON;
	
	/********************************************************************************************
	Get user details and perform security checks
	*********************************************************************************************/	
	DECLARE @EntityIDToCheck INT = 1006 /*GazetteDetails*/
	DECLARE @ItemIDToCheck BIGINT = @GazetteDetailID
	DECLARE @Action VARCHAR(255) = 'Status Change'
	DECLARE @OfficeLocationID BIGINT, @DivisionTypeID SMALLINT, @ProvinceID SMALLINT, @RegionID SMALLINT, @HasAccess BIT
	
	EXEC App.Pr_GetUserLocationAndSecurity @UserID, @EntityIDToCheck, @ItemIDToCheck, @Action, @OfficeLocationID OUTPUT, @DivisionTypeID OUTPUT, @ProvinceID OUTPUT, @RegionID OUTPUT, @HasAccess OUTPUT

	IF @HasAccess = 0
	BEGIN
		RAISERROR('User location and security error', 16, 1)
		RETURN
	END	
	
	/********************************************************************************************
	Update gazette status
	*********************************************************************************************/	
	IF EXISTS(SELECT * FROM [App].GazetteDetails WHERE GazetteDetailID = @GazetteDetailID AND GazetteDetailStatusID = @GazetteDetailStatusID AND DivisionTypeID = @DivisionTypeID AND ProvinceID = @ProvinceID)
	BEGIN		
		RAISERROR('The gazette is already set to this status.', 16, 1)
		RETURN
	END

	IF @GazetteDetailStatusID <> 1 /*Awaiting Gazette Finalisation*/
	BEGIN		
		IF NOT EXISTS(SELECT * FROM App.ApplicationDetails WHERE GazetteDetailID = @GazetteDetailID)
		BEGIN
			RAISERROR('No applications have been linked to the gazette', 16, 1)
			RETURN
		END
	END

	IF @IsTransactionRequired = 1
	BEGIN
		BEGIN TRANSACTION
	END

	BEGIN TRY		
		IF @GazetteDetailStatusID = 2 --Awaiting Notice Period Expiry 
		BEGIN
			DECLARE @GazettePeriodDays INT = CAST(System.Fn_GetSettingValue('GazettePeriodDays') AS INT)
			DECLARE @SentByUserID BIGINT = CAST(System.Fn_GetSettingValue('SystemUserID') AS BIGINT)
			DECLARE @Sender VARCHAR(255)

			UPDATE App.GazetteDetails
			SET 
				GazettePrintDate = GETDATE(),
				GazetteNoticeExpiryDate = CAST((System.Fn_FormatDate(System.Fn_AddWorkingDaysToDate(GETDATE(), @GazettePeriodDays),'dd MMM yyyy') + ' 23:59') AS DATETIME)
			WHERE GazetteDetailID = @GazetteDetailID

			--insert SMS messages for the members notifying them that the application has been printed in the gazette
			DECLARE @GazetteNumber VARCHAR(20)

			SELECT @GazetteNumber = GazetteNumber
			FROM App.GazetteDetails
			WHERE GazetteDetailID = @GazetteDetailID
	
			SELECT TOP 1 @Sender = SettingValue FROM [System].[Settings] WHERE SettingName = 'SenderEmailAddress'

			INSERT INTO Crm.Messages(MessageTypeID, EntityID, ItemID, Sender, Recipients, CC, BCC, Subject, Message, IsHtml, CreatedByID, CreatedDate, UpdatedByID, UpdatedDate)
			SELECT 2 /*sms*/, 1000 /*Application request details*/, Ard.ApplicationDetailID, @Sender, System.Fn_GetFormattedCellNumber(TelCell), '', '', 'Application added to gazette SMS', 
			'Notification from the National Public Transport Regulator. ' + ISNULL(ApplicationNumber,'') + ' has been added to a gazette ' + ISNULL(@GazetteNumber,'') + ' for publication. Please wait for further notification.'
			, 0, @SentByUserID, GETDATE(), @SentByUserID, GETDATE()	
			FROM App.Vw_ApplicationDetails Ard
			WHERE GazetteDetailID = @GazetteDetailID
		END

		DECLARE @AuditMessage VARCHAR(255)

		SELECT @AuditMessage = 'Gazette status changed from ' + Prev.Title + ' to ' + New.Title
		FROM [App].GazetteDetailStatus New, [App].GazetteDetails U
			INNER JOIN [App].GazetteDetailStatus Prev ON U.GazetteDetailStatusID = Prev.GazetteDetailStatusID
		WHERE U.GazetteDetailID = @GazetteDetailID
		AND New.GazetteDetailStatusID = @GazetteDetailStatusID

		IF LEN(ISNULL(@Comments, '')) <> 0
		BEGIN
			SET @AuditMessage = @AuditMessage + ' Comments: ' + @Comments
		END
							
		UPDATE [App].GazetteDetails SET GazetteDetailStatusID = @GazetteDetailStatusID WHERE GazetteDetailID = @GazetteDetailID
				
		INSERT INTO [App].GazetteDetailStatusHistory(GazetteDetailID, GazetteDetailStatusID, UpdatedByID )
		VALUES( @GazetteDetailID, @GazetteDetailStatusID, @UserID )
	
		DECLARE 
			@AuditSource VARCHAR(50)
			,@AuditAction VARCHAR(50) 
			,@Entity VARCHAR(50) 

		SELECT
			@AuditSource = 'application' 
			,@AuditAction = 'status changed'  
			,@Entity = 'Gazette'
				
		EXEC Audit.Pr_WriteEntry @UserID, @AuditSource, @AuditAction, @AuditMessage, 0, 0, @Entity, @GazetteDetailID

		--log status change to all related applications
		INSERT INTO Audit.History(SourceID, ActionID, Description, IsCritical, IsException, EntityID, ItemID, CreatedByID, CreatedDate)
		SELECT 2, 2, @AuditMessage, 0, 0, 6, Ard.WorkflowInstanceID, @UserID, GETDATE() --2=Application, 2=Updated, 6=workflowinstance
		FROM App.ApplicationDetails ARD
		WHERE ARD.GazetteDetailID = @GazetteDetailID

		--if gazette was moved to a status of 4=Awaiting gazette print date then auto-create the next gazette
		IF @GazetteDetailStatusID = 4
		BEGIN
			DECLARE @GazetteTypeID SMALLINT = (SELECT TOP 1 GazetteTypeID FROM App.GazetteDetails WHERE GazetteDetailID = @GazetteDetailID)
			DECLARE @NewGazetteNumber VARCHAR(20)
			DECLARE @GazetteDays BIGINT
			DECLARE @GazetteExpiryDate DATETIME = System.Fn_AddWorkingDaysToDate(GETDATE(),@GazetteDays)

			SET @NewGazetteNumber = [App].[Fn_GetGazetteNumber](@GazetteTypeID, @OfficeLocationID)
			SET @GazetteDays = CAST(System.Fn_GetSettingValue('GazettePeriodDays') AS BIGINT)

			DECLARE @GazetteProvinceID SMALLINT
			DECLARE @GazetteDivisionTypeID SMALLINT

			SELECT @GazetteProvinceID = ProvinceID, @GazetteDivisionTypeID = DivisionTypeID
			FROM App.GazetteDetails
			WHERE GazetteDetailID = @GazetteDetailID

			INSERT INTO [App].[GazetteDetails](GazetteDetailStatusID, GazetteNumber, GazettePrintDate, GazetteNoticeExpiryDate, DivisionTypeID, ProvinceID, CreatedByID, CreatedDate, UpdatedByID, UpdatedDate, GazetteTypeID)
			SELECT 1, @NewGazetteNumber, NULL, NULL, @GazetteDivisionTypeID, @GazetteProvinceID, @UserID, GETDATE(), @UserID, GETDATE(), @GazetteTypeID
		END

		/*Update application status for applications linked to this gazette*/
		EXEC App.Pr_GazetteDetailApplicationStatusUpdate @GazetteDetailID, @UserID

		IF @IsTransactionRequired = 1
		BEGIN
			COMMIT TRANSACTION 
		END	
	END TRY
	BEGIN CATCH
		-- Roll back any active or uncommittable transactions 
		-- before rethrowing the error    
		IF XACT_STATE() <> 0 AND @IsTransactionRequired = 1
		BEGIN
			ROLLBACK TRANSACTION;
		END
	
		EXEC System.Pr_SysErrorRethrow
	END CATCH
END
GO

CREATE OR ALTER PROCEDURE [App].[Pr_LicenceUpliftmentExtensionDecision]
(
    @LicenceID BIGINT,
    @UserID BIGINT
)
AS
BEGIN    

	/********************************************************************************************
	Get user details and perform security checks
	*********************************************************************************************/	
	DECLARE @EntityIDToCheck INT = 1011 /*OperatingLicences*/
	DECLARE @ItemIDToCheck BIGINT = @LicenceID
	DECLARE @Action VARCHAR(255) = 'Save'
	DECLARE @OfficeLocationID BIGINT, @DivisionTypeID SMALLINT, @ProvinceID SMALLINT, @RegionID SMALLINT, @HasAccess BIT
	
	EXEC App.Pr_GetUserLocationAndSecurity @UserID, @EntityIDToCheck, @ItemIDToCheck, @Action, @OfficeLocationID OUTPUT, @DivisionTypeID OUTPUT, @ProvinceID OUTPUT, @RegionID OUTPUT, @HasAccess OUTPUT

	IF @HasAccess = 0
	BEGIN
		RAISERROR('User location and security error', 16, 1)
		RETURN
	END	
	
	/********************************************************************************************
	Save licence extension decision
	*********************************************************************************************/
	DECLARE @isUplifted BIT = 0
	DECLARE @CellNo VARCHAR (15)
	DECLARE @Message VARCHAR(max)
	DECLARE @OperatingLicenceNumber VARCHAR(20)
	DECLARE @NinetyDaysDate VARCHAR(20)
	DECLARE @DateApproved VARCHAR(20)

	--SET FMTONLY off;
    SELECT @CellNo =  M.TelCell, 
	@OperatingLicenceNumber = App.Fn_StripVersionNumber(L.OperatingLicenceNumber,'/'), 
	@NinetyDaysDate = CONVERT(varchar,Lit.IssuingExpiryDate_90Days,101),
	@DateApproved = CONVERT(varchar,L.DateOfIssue,101), @isUplifted =
	CASE
        WHEN ApproverOneApproved = 1 AND ApproverTwoApproved = 1 then 1
		ELSE 0
    END
	FROM App.LicenceUpliftmentExtensionRequestDetails Luerd
	INNER JOIN Verified.Licences L ON L.LicenceID = Luerd.VerifiedLicenceID
	INNER JOIN Verified.Members M ON M.MemberID = L.MemberID
	INNER JOIN Verified.LicenceIssueTracking Lit ON Lit.LicenceID = Luerd.VerifiedLicenceID
	INNER JOIN App.Vw_ApplicationDetails Ard ON Ard.ApplicationDetailID = L.ApplicationDetailID
	WHERE Luerd.VerifiedLicenceID = @LicenceID

	IF @isUplifted = 1
	BEGIN
		EXEC Verified.Pr_LicenceIssueTrackingExtend @LicenceID , 1

		IF @CellNo IS NOT NULL AND @CellNo != -1
		BEGIN
			DECLARE @Sender VARCHAR(255)

			SELECT TOP 1 @Sender = SettingValue FROM [System].[Settings] WHERE SettingName = 'SenderEmailAddress'

			SET @Message = 'Your operating license, ' +@OperatingLicenceNumber+' that was approved on the '+ @DateApproved +',  has not been uplifted within the given 60 days. We grant a further grace period of 30 days, which will expire on '+ @NinetyDaysDate + '.'
		
			INSERT INTO Crm.Messages(MessageTypeID, EntityID, ItemID, Sender, Recipients, CC, BCC, Subject, Message, IsHtml, CreatedByID, CreatedDate, UpdatedByID, UpdatedDate)
			SELECT 2, 1019, @licenceID, @Sender, @CellNo, '', '', 'Licence Upliftment received SMS', @Message, 0, 1, GETDATE(), 1, GETDATE()
		END
	END
END
GO

CREATE OR ALTER PROCEDURE [App].[Pr_Svc_ExpiredNotifications]
AS
BEGIN
	DECLARE @SentByUserID BIGINT = CAST(System.Fn_GetSettingValue('SystemUserID') AS BIGINT)
	DECLARE @LincenceIDs TABLE 
	(
		ID BIGINT
	)

    INSERT  INTO @LincenceIDs
    SELECT  LicenceID
    FROM    Verified.Licences
    WHERE   DATEDIFF(DAY, GETDATE(), DateOfExpiry) <= 0                   
        AND ExpiredNotificationSmsSentDate IS NULL
		AND OperatingLicenceNumber NOT LIKE 'LT%'
		AND OperatingLicenceNumber NOT IN
		(
			SELECT ISNULL(OperatingLicenceNumber,'')
			FROM App.ApplicationLicences
				INNER JOIN App.ApplicationDetails ON	ApplicationDetails.ApplicationDetailID = ApplicationLicences.ApplicationDetailID
			WHERE ApplicationRequestTypeID = 8 --renewal
				AND App.ApplicationLicences.SplitIntoNewApplication = 0 
				AND App.ApplicationLicences.IsCancelled = 0
				AND App.ApplicationDetails.IsCancelled = 0
		) --renewal application already in progres
		AND LicenceID NOT IN (SELECT LicenceID FROM Verified.LicenceExceptions)

	UPDATE Verified.Licences SET ExpiredNotificationSmsSentDate = GETDATE()	
	WHERE LicenceID IN (SELECT ID FROM @LincenceIDs)

	DECLARE @Sender VARCHAR(255)

	SELECT TOP 1 @Sender = SettingValue FROM [System].[Settings] WHERE SettingName = 'SenderEmailAddress'

	INSERT INTO Crm.Messages(MessageTypeID, EntityID, ItemID, Sender, Recipients, CC, BCC, Subject, Message, IsHtml, CreatedByID, CreatedDate, UpdatedByID, UpdatedDate)
	SELECT 2 /*sms*/, 1011 /*OperatingLicences*/, LicenceID, @Sender, System.Fn_GetFormattedCellNumber(TelCell), '', '', 'OL Expiring SMS', 
	'Notification from: Provincial Regulatory Entity. Operating license ' + ISNULL(OperatingLicenceNumber,'') + ' has expired and is no longer valid.'
	, 0, @SentByUserID, GETDATE(), @SentByUserID, GETDATE()	
	FROM Verified.Licences
		INNER JOIN Verified.Members ON Members.MemberID = Licences.MemberID
	WHERE LicenceID IN (SELECT ID FROM @LincenceIDs)

END
GO

CREATE OR ALTER PROCEDURE [App].[Pr_Svc_SendActivityEmail]
	@PeriodDescription VARCHAR(10),
	@SendToEmail VARCHAR(100)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @SummaryEmailTemplate VARCHAR(MAX) = App.Fn_GetActivityEmailTemplate()
	
	DECLARE @ThisPeriodEndDate DATETIME = DATEADD(d, -1, GETDATE())
	DECLARE @ThisPeriodStartDate DATETIME 
	DECLARE @LastPeriodStartDate DATETIME 
	DECLARE @LastPeriodEndDate DATETIME 

	SET @ThisPeriodEndDate = CAST(System.Fn_FormatDate(@ThisPeriodEndDate,'dd MMM yyyy 23:59') AS DATETIME)

	IF @PeriodDescription = 'month'
	BEGIN
		SET @ThisPeriodStartDate = CAST(System.Fn_FormatDate(DATEADD(m, -1, @ThisPeriodEndDate) ,'dd MMM yyyy 00:00') AS DATETIME)
		SET @LastPeriodStartDate = CAST(System.Fn_FormatDate(DATEADD(m, -2, @ThisPeriodEndDate) ,'dd MMM yyyy 00:00') AS DATETIME) 
		SET @LastPeriodEndDate =   DATEADD(d,-1 ,DATEADD(m,-1, @ThisPeriodEndDate))
	END
	ELSE IF @PeriodDescription = 'week'
	BEGIN
		SET @ThisPeriodStartDate = CAST(System.Fn_FormatDate(DATEADD(d, -7, @ThisPeriodEndDate) ,'dd MMM yyyy 00:00') AS DATETIME)
		SET @LastPeriodStartDate = CAST(System.Fn_FormatDate(DATEADD(d, -14, @ThisPeriodEndDate) ,'dd MMM yyyy 00:00') AS DATETIME) 
		SET @LastPeriodEndDate =   DATEADD(d,-1 ,DATEADD(d,-7, @ThisPeriodEndDate))
	END

	IF @PeriodDescription = 'year'
	BEGIN		
		SET @LastPeriodStartDate = CAST('01 Jan ' + CAST(YEAR(GETDATE())-1 AS VARCHAR(4)) + ' 00:00' AS DATETIME) 
		SET @LastPeriodEndDate =   CAST('31 Dec ' + CAST(YEAR(GETDATE())-1 AS VARCHAR(4)) + ' 23:59' AS DATETIME) 
		SET @ThisPeriodStartDate = CAST('01 Jan ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) + ' 00:00' AS DATETIME) 
		SET @ThisPeriodEndDate = CAST('31 Dec ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) + ' 23:59' AS DATETIME) 
	END

	DECLARE @ThisPeriodApplications TABLE(WorkflowInstanceID BIGINT, ApplicationDetailID BIGINT)
	DECLARE @LastPeriodApplications TABLE(WorkflowInstanceID BIGINT, ApplicationDetailID BIGINT)

	DECLARE @ThisPeriodLicencesCreated TABLE(WorkflowInstanceID BIGINT, LicenceIssueDetailID BIGINT)
	DECLARE @LastPeriodLicencesCreated TABLE(WorkflowInstanceID BIGINT, LicenceIssueDetailID BIGINT)

	DECLARE @ThisPeriodLicencesCompleted TABLE(WorkflowInstanceID BIGINT, LicenceIssueDetailID BIGINT)
	DECLARE @LastPeriodLicencesCompleted TABLE(WorkflowInstanceID BIGINT, LicenceIssueDetailID BIGINT)

	DECLARE @OverallApplicationsInProgress TABLE(WorkflowInstanceID BIGINT, ApplicationDetailID BIGINT)
	DECLARE @OverallApplicationsInProgressLastPeriod TABLE(WorkflowInstanceID BIGINT, ApplicationDetailID BIGINT)

	DECLARE @OverallLicenceIssuesInProgress TABLE(WorkflowInstanceID BIGINT, LicenceIssueDetailID BIGINT)
	DECLARE @OverallLicenceIssuesInProgressLastPeriod TABLE(WorkflowInstanceID BIGINT, LicenceIssueDetailID BIGINT)


	/******************************************************************************************
	Overall number of applications in progress
	******************************************************************************************/
	DECLARE @OverallApplicationsInProgressCount BIGINT
	DECLARE @OverallApplicationsInProgressLastPeriodCount BIGINT

	INSERT INTO @OverallApplicationsInProgress(WorkflowInstanceID, ApplicationDetailID) 
	SELECT WorkflowInstanceID, ItemID FROM Wf.WorkflowInstances Wi WHERE WorkflowTemplateID = 1 AND WorkflowInstanceStatusID = 1

	INSERT INTO @OverallApplicationsInProgressLastPeriod(WorkflowInstanceID, ApplicationDetailID) 
	SELECT WorkflowInstanceID, ItemID FROM Wf.WorkflowInstances Wi 
	WHERE WorkflowTemplateID = 1 AND 
		(
			WorkflowInstanceStatusID = 1 AND Wi.StartDate < @LastPeriodEndDate	
			OR 
			(WorkflowInstanceStatusID <> 1 AND Wi.StartDate < @LastPeriodEndDate AND EndDate > @ThisPeriodStartDate)
		)

	SELECT @OverallApplicationsInProgressCount = COUNT(*) FROM @OverallApplicationsInProgress
	SELECT @OverallApplicationsInProgressLastPeriodCount = COUNT(*) FROM @OverallApplicationsInProgressLastPeriod
	
	SET @SummaryEmailTemplate = REPLACE(@SummaryEmailTemplate, '[[overall application count]]', CAST(@OverallApplicationsInProgressCount AS VARCHAR(20)))
	SET @SummaryEmailTemplate = REPLACE(@SummaryEmailTemplate, '[[overall application difference]]', App.Fn_GetActivityEmailPreviousPeriodDifferenceDescription(@OverallApplicationsInProgressCount, @OverallApplicationsInProgressLastPeriodCount, @PeriodDescription, 1, 0))


	/******************************************************************************************
	Overall number of licence issues in progress
	******************************************************************************************/
	DECLARE @OverallLicenceIssuesInProgressCount BIGINT
	DECLARE @OverallLicenceIssuesInProgressLastPeriodCount BIGINT

	INSERT INTO @OverallLicenceIssuesInProgress(WorkflowInstanceID, LicenceIssueDetailID) 
	SELECT WorkflowInstanceID, ItemID FROM Wf.WorkflowInstances Wi WHERE WorkflowTemplateID = 7 AND WorkflowInstanceStatusID = 1

	INSERT INTO @OverallLicenceIssuesInProgressLastPeriod(WorkflowInstanceID, LicenceIssueDetailID) 
	SELECT WorkflowInstanceID, ItemID FROM Wf.WorkflowInstances Wi 
	WHERE WorkflowTemplateID = 7 AND 
		(
			WorkflowInstanceStatusID = 1 AND Wi.StartDate < @LastPeriodEndDate		
			OR 
			(WorkflowInstanceStatusID <> 1 AND Wi.StartDate < @LastPeriodEndDate AND EndDate > @ThisPeriodStartDate)
		)

	SELECT @OverallLicenceIssuesInProgressCount = COUNT(*) FROM @OverallLicenceIssuesInProgress
	SELECT @OverallLicenceIssuesInProgressLastPeriodCount = COUNT(*) FROM @OverallLicenceIssuesInProgressLastPeriod

	SET @SummaryEmailTemplate = REPLACE(@SummaryEmailTemplate, '[[overall licence issues count]]', CAST(@OverallLicenceIssuesInProgressCount AS VARCHAR(20)))
	SET @SummaryEmailTemplate = REPLACE(@SummaryEmailTemplate, '[[overall licence issues difference]]', App.Fn_GetActivityEmailPreviousPeriodDifferenceDescription(@OverallLicenceIssuesInProgressCount, @OverallLicenceIssuesInProgressLastPeriodCount, @PeriodDescription, 1, 0))


	/******************************************************************************************
	New applications received
	******************************************************************************************/
	DECLARE @ApplicationsReceivedThisPeriod BIGINT
	DECLARE @ApplicationsReceivedLastPeriod BIGINT

	INSERT INTO @ThisPeriodApplications(WorkflowInstanceID, ApplicationDetailID) 
	SELECT WorkflowInstanceID, ItemID FROM Wf.WorkflowInstances Wi WHERE Wi.CreatedDate BETWEEN @ThisPeriodStartDate AND @ThisPeriodEndDate AND WorkflowTemplateID = 1
	
	INSERT INTO @LastPeriodApplications(WorkflowInstanceID, ApplicationDetailID) 
	SELECT WorkflowInstanceID, ItemID FROM Wf.WorkflowInstances Wi WHERE Wi.CreatedDate BETWEEN @LastPeriodStartDate AND @LastPeriodEndDate AND WorkflowTemplateID = 1

	SELECT @ApplicationsReceivedThisPeriod = COUNT(*) FROM @ThisPeriodApplications
	SELECT @ApplicationsReceivedLastPeriod = COUNT(*) FROM @LastPeriodApplications


	SET @SummaryEmailTemplate = REPLACE(@SummaryEmailTemplate, '[[new application count]]', CAST(@ApplicationsReceivedThisPeriod AS VARCHAR(20)))
	SET @SummaryEmailTemplate = REPLACE(@SummaryEmailTemplate, '[[new application difference]]', App.Fn_GetActivityEmailPreviousPeriodDifferenceDescription(@ApplicationsReceivedThisPeriod, @ApplicationsReceivedLastPeriod, @PeriodDescription, 0, 0))


	/******************************************************************************************
	Applications moved to issuing
	******************************************************************************************/
	DECLARE @LicenceIssuesCreatedThisPeriod BIGINT
	DECLARE @LicenceIssuesCreatedLastPeriod BIGINT

	INSERT INTO @ThisPeriodLicencesCreated(WorkflowInstanceID, LicenceIssueDetailID) 
	SELECT WorkflowInstanceID, ItemID FROM Wf.WorkflowInstances Wi WHERE Wi.CreatedDate BETWEEN @ThisPeriodStartDate AND @ThisPeriodEndDate AND WorkflowTemplateID = 7
	
	INSERT INTO @LastPeriodLicencesCreated(WorkflowInstanceID, LicenceIssueDetailID) 
	SELECT WorkflowInstanceID, ItemID FROM Wf.WorkflowInstances Wi WHERE Wi.CreatedDate BETWEEN @LastPeriodStartDate AND @LastPeriodEndDate AND WorkflowTemplateID = 7

	SELECT @LicenceIssuesCreatedThisPeriod = COUNT(*) FROM @ThisPeriodLicencesCreated
	SELECT @LicenceIssuesCreatedLastPeriod = COUNT(*) FROM @LastPeriodLicencesCreated
	
	SET @SummaryEmailTemplate = REPLACE(@SummaryEmailTemplate, '[[moved to issuing count]]', CAST(@LicenceIssuesCreatedThisPeriod AS VARCHAR(20)))
	SET @SummaryEmailTemplate = REPLACE(@SummaryEmailTemplate, '[[moved to issuing difference]]', App.Fn_GetActivityEmailPreviousPeriodDifferenceDescription(@LicenceIssuesCreatedThisPeriod, @LicenceIssuesCreatedLastPeriod, @PeriodDescription, 1, 1))


	/******************************************************************************************
	Issued to Operators
	******************************************************************************************/
	DECLARE @LicenceIssuesCompletedThisPeriod BIGINT
	DECLARE @LicenceIssuesCompletedLastPeriod BIGINT

	INSERT INTO @ThisPeriodLicencesCompleted(WorkflowInstanceID, LicenceIssueDetailID) 
	SELECT WorkflowInstanceID, ItemID FROM Wf.WorkflowInstances Wi WHERE Wi.EndDate BETWEEN @ThisPeriodStartDate AND @ThisPeriodEndDate AND WorkflowTemplateID = 7
	
	INSERT INTO @LastPeriodLicencesCompleted(WorkflowInstanceID, LicenceIssueDetailID) 
	SELECT WorkflowInstanceID, ItemID FROM Wf.WorkflowInstances Wi WHERE Wi.EndDate BETWEEN @LastPeriodStartDate AND @LastPeriodEndDate AND WorkflowTemplateID = 7


	SELECT @LicenceIssuesCompletedThisPeriod = COUNT(*) FROM @ThisPeriodLicencesCompleted		
	SELECT @LicenceIssuesCompletedLastPeriod = COUNT(*) FROM @LastPeriodLicencesCompleted
	
	SET @SummaryEmailTemplate = REPLACE(@SummaryEmailTemplate, '[[issued count]]', CAST(@LicenceIssuesCompletedThisPeriod AS VARCHAR(20)))
	SET @SummaryEmailTemplate = REPLACE(@SummaryEmailTemplate, '[[issued difference]]', App.Fn_GetActivityEmailPreviousPeriodDifferenceDescription(@LicenceIssuesCompletedThisPeriod, @LicenceIssuesCompletedLastPeriod, @PeriodDescription, 1, 1))



	/******************************************************************************************
	Overall number of applications in progress details
	******************************************************************************************/
	DECLARE @OverallApplicationsInProgressDetails TABLE(StepTitle VARCHAR(100), NumberOfApplicationsThisPeriod DECIMAL(9,2), NumberOfApplicationsLastPeriod DECIMAL(9,2), PercentageOfOverAll DECIMAL(9,2))

	INSERT INTO @OverallApplicationsInProgressDetails(StepTitle, NumberOfApplicationsThisPeriod)
	SELECT 
		CASE 
			WHEN Wts.StepTitle = 'Agenda Officer' THEN 'waiting for an adjudication hearing to be scheduled'
			WHEN Wts.StepTitle = 'Agenda Officer  Finalises Adjudication Meeting  Agenda' OR Wts.StepTitle = 'Adjudication Panel Captures Adjudication Meeting Decision' THEN 'waiting for an adjudication hearing to happen'
			WHEN Wts.StepTitle = 'Awaiting Gazette Notice Period Expiry (auto completion expiry)' THEN 'waiting for gazette expiry'
			WHEN Wts.StepTitle = 'Exception Handling' THEN 'waiting for exceptions to be resolved'
			WHEN Wts.StepTitle = 'Initiator creates request' THEN 'waiting for the application to be submitted by the data capturer'
			WHEN Wts.StepTitle = 'Manual Verification' THEN 'waiting for document verification to be finalised'
			WHEN Wts.StepTitle = 'Payment/Scanning' THEN 'waiting for payment/document scanning to be completed'
			ELSE Wts.StepTitle
		END, 
		COUNT(*)
	FROM @OverallApplicationsInProgress Oip
		INNER JOIN App.ApplicationDetails Ard ON Oip.ApplicationDetailID = Ard.ApplicationDetailID
		INNER JOIN Wf.WorkflowInstances Wi ON Oip.WorkflowInstanceID = Wi.WorkflowInstanceID 
		INNER JOIN Wf.WorkflowTemplateSteps Wts ON Wi.WorkflowTemplateStepID = Wts.WorkflowTemplateStepID
	GROUP BY Wts.StepTitle

	DECLARE @OverallApplicationsInProgressDetailsCount DECIMAL(9,2)

	SELECT @OverallApplicationsInProgressDetailsCount = SUM(NumberOfApplicationsThisPeriod)
	FROM @OverallApplicationsInProgressDetails

	UPDATE @OverallApplicationsInProgressDetails
	SET PercentageOfOverAll = (NumberOfApplicationsThisPeriod / @OverallApplicationsInProgressDetailsCount) * 100

	DECLARE @OverallApplicationsInProgressDetailsText VARCHAR(MAX)

	SELECT @OverallApplicationsInProgressDetailsText = COALESCE(@OverallApplicationsInProgressDetailsText + '  ', '  ') + '<li><b>' + CAST(PercentageOfOverAll AS VARCHAR(20)) + '%</b> is ' + StepTitle + '</li>'
	FROM @OverallApplicationsInProgressDetails
	ORDER BY PercentageOfOverAll DESC

	SET @SummaryEmailTemplate = REPLACE(@SummaryEmailTemplate, '[[applications in progress breakdown]]', ISNULL(@OverallApplicationsInProgressDetailsText,''))



	/******************************************************************************************
	Overall number of licence issues in progress details
	******************************************************************************************/
	DECLARE @OverallLicenceIssuesInProgressDetails TABLE(StepTitle VARCHAR(100), NumberOfLicencesThisPeriod DECIMAL(9,2), NumberOfLicencesLastPeriod DECIMAL(9,2), PercentageOfOverAll DECIMAL(9,2))

	INSERT INTO @OverallLicenceIssuesInProgressDetails(StepTitle, NumberOfLicencesThisPeriod)
	SELECT 
		CASE 
			WHEN Wts.StepTitle = 'Document Upload' THEN 'waiting for the final documentation to be uploaded'
			WHEN Wts.StepTitle = 'Final verification and print'  THEN 'waiting for final verification and printing'		
			ELSE 'waiting for ' + Wts.StepTitle
		END, 
		COUNT(*)
	FROM @OverallLicenceIssuesInProgress Lip
		INNER JOIN App.LicenceIssueDetails Lid ON Lip.LicenceIssueDetailID = Lid.LicenceIssueDetailID
		INNER JOIN Wf.WorkflowInstances Wi ON Lip.WorkflowInstanceID = Wi.WorkflowInstanceID 
		INNER JOIN Wf.WorkflowTemplateSteps Wts ON Wi.WorkflowTemplateStepID = Wts.WorkflowTemplateStepID
	GROUP BY Wts.StepTitle

	DECLARE @OverallLicenceIssuesInProgressDetailsCount DECIMAL(9,2)

	SELECT @OverallLicenceIssuesInProgressDetailsCount = SUM(NumberOfLicencesThisPeriod)
	FROM @OverallLicenceIssuesInProgressDetails

	UPDATE @OverallLicenceIssuesInProgressDetails
	SET PercentageOfOverAll = (NumberOfLicencesThisPeriod / @OverallLicenceIssuesInProgressDetailsCount) * 100

	DECLARE @OverallLicencesInProgressDetailsText VARCHAR(MAX)

	SELECT @OverallLicencesInProgressDetailsText = COALESCE(@OverallLicencesInProgressDetailsText + '  ', '  ') + '<li><b>' + CAST(PercentageOfOverAll AS VARCHAR(20)) + '% </b> is ' + StepTitle + '</li>'
	FROM @OverallLicenceIssuesInProgressDetails
	ORDER BY PercentageOfOverAll DESC

	SET @SummaryEmailTemplate = REPLACE(@SummaryEmailTemplate, '[[licence issues in progress breakdown]]', ISNULL(@OverallLicencesInProgressDetailsText,''))



	/******************************************************************************************
	New applications received summary
	******************************************************************************************/
	DECLARE @ApplicationTypeSummary TABLE(ApplicationRequestTypeID SMALLINT, Description VARCHAR(100), CreatedLastPeriod DECIMAL(9,2), CreatedThisPeriod DECIMAL(9,2), CreatedDifference DECIMAL(9,2), CreatedThisPeriodPercentageOfOverall DECIMAL(9,2), CreatedLastPeriodPercentageOfOverall DECIMAL(9,2))
	INSERT INTO @ApplicationTypeSummary(ApplicationRequestTypeID, Description, CreatedLastPeriod, CreatedThisPeriod)
	SELECT 
		Art.ApplicationRequestTypeID,
		Art.Title, 
		(
			SELECT COUNT(*) 
			FROM @ThisPeriodApplications Tpa
				INNER JOIN App.ApplicationDetails Ard ON Tpa.ApplicationDetailID = Ard.ApplicationDetailID
			WHERE Ard.ApplicationRequestTypeID = Art.ApplicationRequestTypeID
		) AS CreatedLastPeriod,
		(
			SELECT COUNT(*) 
			FROM @LastPeriodApplications Tpa
				INNER JOIN App.ApplicationDetails Ard ON Tpa.ApplicationDetailID = Ard.ApplicationDetailID
			WHERE Ard.ApplicationRequestTypeID = Art.ApplicationRequestTypeID
		) AS CreatedPreviousPeriod
	FROM Md.ApplicationRequestTypes Art

	DECLARE @OverAllTotalThisPeriod BIGINT
	DECLARE @OverAllTotalLastPeriod BIGINT

	SELECT 
		@OverAllTotalThisPeriod = SUM(CreatedThisPeriod),
		@OverAllTotalLastPeriod = SUM(CreatedLastPeriod) 
	FROM @ApplicationTypeSummary


	UPDATE @ApplicationTypeSummary
	SET 
		CreatedDifference = CreatedThisPeriod - CreatedLastPeriod,
		CreatedThisPeriodPercentageOfOverall = CASE WHEN @OverAllTotalThisPeriod > 0 THEN (CreatedThisPeriod / @OverAllTotalThisPeriod) * 100 ELSE 0 END,
		CreatedLastPeriodPercentageOfOverall = CASE WHEN @OverAllTotalLastPeriod > 0 THEN (CreatedLastPeriod / @OverAllTotalLastPeriod) * 100 ELSE 0 END

	DECLARE @ApplicationTypeSummaryText VARCHAR(MAX)

	SELECT @ApplicationTypeSummaryText = COALESCE(@ApplicationTypeSummaryText + '  ', '  ') + 
		'<li><b>' +
			CAST(ABS(CreatedThisPeriodPercentageOfOverall) AS VARCHAR(20)) + '%</b> were ' + LOWER(Description) + ' requests.</li>'
	FROM @ApplicationTypeSummary 
	WHERE CreatedThisPeriodPercentageOfOverall <> 0
	ORDER BY CreatedThisPeriodPercentageOfOverall DESC

	SET @SummaryEmailTemplate = REPLACE(@SummaryEmailTemplate, '[[new applications breakdown]]', ISNULL(@ApplicationTypeSummaryText,''))


	/******************************************************************************************
	Applications moved to issuing summary
	******************************************************************************************/

	DECLARE @LicencesIssuesCreatedText VARCHAR(MAX)

	SELECT 
		@LicencesIssuesCreatedText = COALESCE(@LicencesIssuesCreatedText + '  ', '  ') + '<li><b>' + CAST(COUNT(*) AS VARCHAR(20)) +  '</b>' +
			CASE 
				WHEN (LOWER(Ls.Title) = 'archived') THEN ' has been archived'
				WHEN (LOWER(Ls.Title) = 'awaiting issuing') THEN ' is still awaiting issuing'
				WHEN (LOWER(Ls.Title) = 'issued') THEN ' has been issued to the operator'
				WHEN (LOWER(Ls.Title) = 'expiring within 60 days') THEN ' will be expiring within the next 60 days'			
				ELSE Ls.Title
			END		
			+ '</li>'
	FROM @ThisPeriodLicencesCreated Tplc
		INNER JOIN App.LicenceIssueDetails Lid ON Tplc.LicenceIssueDetailID = Lid.LicenceIssueDetailID
		INNER JOIN Wf.WorkflowInstances Wi ON Tplc.WorkflowInstanceID = Wi.WorkflowInstanceID
		INNER JOIN App.ApplicationDetails Ard ON Lid.ApplicationDetailID = Ard.ApplicationDetailID
		INNER JOIN Verified.Licences L ON Lid.VerifiedLicenceID = L.LicenceID
		INNER JOIN Verified.LicenceStatus Ls ON L.LicenceStatusID = Ls.LicenceStatusID
	GROUP BY Ls.Title
	ORDER BY COUNT(*) DESC

	SET @SummaryEmailTemplate = REPLACE(@SummaryEmailTemplate, '[[moved to issuing breakdown]]', ISNULL(@LicencesIssuesCreatedText,''))

	/******************************************************************************************
	Replace common placeholders
	******************************************************************************************/
	SET @SummaryEmailTemplate = REPLACE(@SummaryEmailTemplate, '[[period description]]', @PeriodDescription)
	SET @SummaryEmailTemplate = REPLACE(@SummaryEmailTemplate, '[[period date range]]', System.Fn_FormatDate(@ThisPeriodStartDate, 'dd MMM yyyy') + ' to ' + System.Fn_FormatDate(@ThisPeriodEndDate, 'dd MMM yyyy'))

	
	/******************************************************************************************
	Create email message
	******************************************************************************************/
	DECLARE @Sender VARCHAR(255)

	SELECT TOP 1 @Sender = SettingValue FROM [System].[Settings] WHERE SettingName = 'SenderEmailAddress'

	INSERT INTO Crm.Messages(MessageTypeID, EntityID, ItemID, Sender, Recipients, CC, BCC, Subject, Message, IsHtml, SentDate, Retries, IsCancelled, CreatedByID, CreatedDate, UpdatedByID, UpdatedDate)
	VALUES(1 /*email*/, NULL, NULL, @Sender, @SendToEmail, NULL, NULL, 'Legiti-mate status update', @SummaryEmailTemplate, 1, NULL, 0, 0, 1, GETDATE(), 1, GETDATE())

END
GO

CREATE OR ALTER PROCEDURE [App].[Pr_Svc_SendUpliftmentNotifications]
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @SentByUserID BIGINT = CAST(System.Fn_GetSettingValue('SystemUserID') AS BIGINT)
	DECLARE @Days BIGINT = CAST(System.Fn_GetSettingValue('UpliftmentNotificationDays') AS BIGINT)
	DECLARE @LincenceIDs TABLE 
	(
		ID BIGINT
	)

    INSERT  INTO @LincenceIDs
    SELECT  LicenceID
    FROM    Verified.Licences
    WHERE   UpliftmentReminderSmsSentDate IS NULL
		AND DATEDIFF(DAY, DateOfIssue, GETDATE()) >= @Days                   
		AND OperatingLicenceNumber NOT LIKE 'LTOKZN%'
		AND LicenceStatusID = 1 --awaiting upliftment
		AND LicenceID NOT IN (SELECT LicenceID FROM Verified.LicenceExceptions)

	UPDATE Verified.Licences SET UpliftmentReminderSmsSentDate = GETDATE()	
	WHERE LicenceID IN (SELECT ID FROM @LincenceIDs)

	DECLARE @Sender VARCHAR(255)

	SELECT TOP 1 @Sender = SettingValue FROM [System].[Settings] WHERE SettingName = 'SenderEmailAddress'

	INSERT INTO Crm.Messages(MessageTypeID, EntityID, ItemID, Sender, Recipients, CC, BCC, Subject, Message, IsHtml, CreatedByID, CreatedDate, UpdatedByID, UpdatedDate)
	SELECT 2 /*sms*/, 1011 /*OperatingLicences*/, LicenceID, @Sender, System.Fn_GetFormattedCellNumber(M.TelCell), '', '', 'OL not uplifted SMS', 
	'Notification from: Provincial Regulatory Entity. ' + ISNULL(Ard.ApplicationNumber,'') + ' has been waiting for upliftment for more than 30 days. Call on your nearest PRE office to uplift.'
	, 0, @SentByUserID, GETDATE(), @SentByUserID, GETDATE()	
	FROM Verified.Licences L
		INNER JOIN App.Vw_ApplicationDetails Ard ON L.ApplicationDetailID = Ard.ApplicationDetailID
		INNER JOIN Verified.Members M ON M.MemberID = L.MemberID
	WHERE LicenceID IN (SELECT ID FROM @LincenceIDs)

END
GO

CREATE OR ALTER PROCEDURE [App].[Pr_Svc_UpcomingExpiryNotifications]	
AS
BEGIN	
	DECLARE @RenewalGracePeriod INT
	DECLARE @SentByUserID BIGINT = CAST(System.Fn_GetSettingValue('SystemUserID') AS BIGINT)

	SELECT @RenewalGracePeriod = CAST(SettingValue AS INT) FROM System.Settings WHERE SettingName = 'RenewalGracePeriod'

	DECLARE @LicenceIDs TABLE 
	(
		ID BIGINT
	)

	INSERT INTO @LicenceIDs(Id)
	SELECT LicenceID 
	FROM Verified.Licences
	WHERE DATEDIFF(DAY,GETDATE(),DateOfExpiry) <= @RenewalGracePeriod 
		AND UpcomingExpiryNotificationSmsSentDate IS NULL
		AND ExpiredNotificationSmsSentDate IS NULL
		AND OperatingLicenceNumber NOT LIKE 'LT%'
		AND OperatingLicenceNumber NOT IN
		(
			SELECT OperatingLicenceNumber
			FROM App.ApplicationLicences
				INNER JOIN App.ApplicationDetails ON	ApplicationDetails.ApplicationDetailID = ApplicationLicences.ApplicationDetailID
			WHERE ApplicationRequestTypeID = 8 --renewal
				AND App.ApplicationLicences.IsCancelled = 0
				AND App.ApplicationLicences.SplitIntoNewApplication = 0
				AND App.ApplicationDetails.IsCancelled = 0
		) --renewal application already in progres
		AND LicenceID NOT IN (SELECT LicenceID FROM Verified.LicenceExceptions)

	UPDATE Verified.Licences 
	SET 
		UpcomingExpiryNotificationSmsSentDate = GETDATE()	
	WHERE LicenceID IN (SELECT ID FROM @LicenceIDs)

	DECLARE @Sender VARCHAR(255)

	SELECT TOP 1 @Sender = SettingValue FROM [System].[Settings] WHERE SettingName = 'SenderEmailAddress'

	INSERT INTO Crm.Messages(MessageTypeID, EntityID, ItemID, Sender, Recipients, CC, BCC, Subject, Message, IsHtml, CreatedByID, CreatedDate, UpdatedByID, UpdatedDate)
	SELECT 2 /*sms*/, 1011 /*OperatingLicences*/, LicenceID, @Sender, System.Fn_GetFormattedCellNumber(TelCell), '', '', 'OL Expiring SMS', 
	'Notification from: Provincial Regulatory Entity. Operating license ' + ISNULL(OperatingLicenceNumber,'') + ' is expiring on ' + [System].[Fn_ToYYYYMMDD](DateOfExpiry) + '. Please apply for renewal ASAP. Thank you.'
	, 0, @SentByUserID, GETDATE(), @SentByUserID, GETDATE()	
	FROM Verified.Licences
		INNER JOIN Verified.Members ON Members.MemberID = Licences.MemberID
	WHERE LicenceID IN (SELECT ID FROM @LicenceIDs)

END
GO

CREATE OR ALTER PROCEDURE [Verified].[Pr_Svc_LicenceIssueTrackingSMSSender]
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @RequiredCorrespondence TABLE(LicenceID BIGINT, NumberDaysMessageRequired VARCHAR(2), OperatingLicenceNumber VARCHAR(20), CellNumber VARCHAR(20))
 
    INSERT INTO @RequiredCorrespondence(LicenceID, NumberDaysMessageRequired, OperatingLicenceNumber, CellNumber)
    SELECT Lit.LicenceID, '30', L.OperatingLicenceNumber, System.Fn_GetFormattedCellNumber(M.TelCell)
    FROM Verified.LicenceIssueTracking Lit
		INNER JOIN Verified.Licences L ON L.LicenceID = Lit.LicenceID
		INNER JOIN Verified.Members M ON L.MemberID = M.MemberID
	WHERE 
		L.LicenceStatusID = 1 /*Awaiting issuing*/
		AND GETDATE() > IssuingExpiryDate_30Days
		AND IssuingExpiryDate_30DaysIsSmsSent = 0
		AND ISNULL(M.TelCell,'') <> ''

    INSERT INTO @RequiredCorrespondence(LicenceID, NumberDaysMessageRequired, OperatingLicenceNumber, CellNumber)
    SELECT Lit.LicenceID, '60', L.OperatingLicenceNumber, System.Fn_GetFormattedCellNumber(M.TelCell)
    FROM Verified.LicenceIssueTracking Lit
		INNER JOIN Verified.Licences L ON L.LicenceID = Lit.LicenceID
		INNER JOIN Verified.Members M ON L.MemberID = M.MemberID
	WHERE 
		L.LicenceStatusID = 1 /*Awaiting issuing*/
		AND GETDATE() > IssuingExpiryDate_60Days
		AND IssuingExpiryDate_30DaysIsSmsSent = 1
		AND IssuingExpiryDate_60DaysIsSmsSent = 0
		AND IssuingExpiryDateIsExtended = 0
		AND ISNULL(M.TelCell,'') <> ''

    INSERT INTO @RequiredCorrespondence(LicenceID, NumberDaysMessageRequired, OperatingLicenceNumber, CellNumber)
    SELECT Lit.LicenceID, '90', L.OperatingLicenceNumber, System.Fn_GetFormattedCellNumber(M.TelCell)
    FROM Verified.LicenceIssueTracking Lit
		INNER JOIN Verified.Licences L ON L.LicenceID = Lit.LicenceID
		INNER JOIN Verified.Members M ON L.MemberID = M.MemberID
	WHERE 
		L.LicenceStatusID = 1 /*Awaiting issuing*/
		AND GETDATE() > IssuingExpiryDate_90Days
		AND IssuingExpiryDate_30DaysIsSmsSent = 1
		AND IssuingExpiryDate_90DaysIsSmsSent = 0
		AND IssuingExpiryDateIsExtended = 1
		AND ISNULL(M.TelCell,'') <> ''
      
    IF EXISTS(SELECT * FROM @RequiredCorrespondence) 
    BEGIN
        DECLARE @SystemEmail VARCHAR(100)
        DECLARE @SystemUserID BIGINT
        
		DECLARE @LicenceID BIGINT
		DECLARE @NumberDaysMessageRequired VARCHAR(2)
		DECLARE @CellNumber VARCHAR(20)
		DECLARE @OperatingLicenceNumber VARCHAR(20)
		DECLARE @Sender VARCHAR(255)

        SET @SystemEmail = System.Fn_GetSettingValue('SystemEmail')
        SET @SystemUserID = CAST(System.Fn_GetSettingValue('SystemUserID') AS BIGINT)
        SELECT TOP 1 @Sender = SettingValue FROM [System].[Settings] WHERE SettingName = 'SenderEmailAddress'

        DECLARE correspondenceCursor CURSOR 
        FOR    
            SELECT LicenceID, NumberDaysMessageRequired, OperatingLicenceNumber, CellNumber
            FROM @RequiredCorrespondence
		OPEN correspondenceCursor
 
			FETCH NEXT FROM correspondenceCursor
			INTO @LicenceID, @NumberDaysMessageRequired, @OperatingLicenceNumber, @CellNumber
    
			WHILE @@FETCH_STATUS = 0
			BEGIN                               
			   IF @NumberDaysMessageRequired = '30'
			   BEGIN
					INSERT INTO [Crm].[Messages] (MessageTypeID, EntityID, ItemID, Sender, Recipients, CC, BCC, Subject, Message, IsHtml, SentDate, Retries, IsCancelled, CreatedByID, CreatedDate, UpdatedByID, UpdatedDate)
					VALUES (2, 1011, @LicenceID, @Sender, @CellNumber, '', '', '30 Day SMS','Notification from PRE: Your Operating Licence '+@OperatingLicenceNumber+' was approved 30 days ago.You have not uplifted it. We hereby concede a grace period of a further 30 days. Failure to uplift will result in CANCELLATION of your Licence. In Need you may, in writing, request a further extension of 30 days', 0, null, 0, 0, 1, GETDATE(), 1, GETDATE())

					UPDATE Verified.LicenceIssueTracking
					SET 
						IssuingExpiryDate_30DaysIsSmsSent = 1, 
						IssuingExpiryDate_30DaysIsSmsSentDate = GETDATE()
					WHERE LicenceID = @LicenceID
			   END
			   ELSE IF @NumberDaysMessageRequired = '60'
			   BEGIN
					INSERT INTO [Crm].[Messages] (MessageTypeID, EntityID, ItemID, Sender, Recipients, CC, BCC, Subject, Message, IsHtml, SentDate, Retries, IsCancelled, CreatedByID, CreatedDate, UpdatedByID, UpdatedDate)
					VALUES (2, 1011, @LicenceID, @Sender, @CellNumber, '', '', '60 Day SMS','Notification from PRE: Your Operating Licence '+@OperatingLicenceNumber+' was approved 60 days ago.You have not uplifted it and the licence has now been CANCELLED.', 0, null, 0, 0, 1, GETDATE(), 1, GETDATE())

					UPDATE Verified.LicenceIssueTracking
					SET 
						IssuingExpiryDate_60DaysIsSmsSent = 1, 
						IssuingExpiryDate_60DaysIsSmsSentDate = GETDATE()
					WHERE LicenceID = @LicenceID

			   END
			   ELSE IF @NumberDaysMessageRequired = '90'
			   BEGIN
					INSERT INTO [Crm].[Messages] (MessageTypeID, EntityID, ItemID, Sender, Recipients, CC, BCC, Subject, Message, IsHtml, SentDate, Retries, IsCancelled, CreatedByID, CreatedDate, UpdatedByID, UpdatedDate)
					VALUES (2, 1011, @LicenceID, @Sender, @CellNumber, '', '', '90 Day SMS','Notification from PRE: Your Operating Licence '+@OperatingLicenceNumber+' was approved 90 days ago.You have not uplifted it and the licence has now been CANCELLED.', 0, null, 0, 0, 1, GETDATE(), 1, GETDATE())

					UPDATE Verified.LicenceIssueTracking
					SET 
						IssuingExpiryDate_90DaysIsSmsSent = 1, 
						IssuingExpiryDate_90DaysIsSmsSentDate = GETDATE()
					WHERE LicenceID = @LicenceID
			   END
                                
			   FETCH NEXT FROM correspondenceCursor 
			   INTO @LicenceID, @NumberDaysMessageRequired, @OperatingLicenceNumber, @CellNumber;
			END
        CLOSE correspondenceCursor;
        DEALLOCATE correspondenceCursor;    
    END
END
GO







