-- Inspection scheduling: expose applicant name on list/calendar procs and improve SMS phone resolution.

IF OBJECT_ID('App.Pr_ApplicationsAwaitingInspectionsGet') IS NULL
    EXEC('CREATE PROCEDURE App.Pr_ApplicationsAwaitingInspectionsGet AS BEGIN SET NOCOUNT ON; END');
GO

ALTER PROCEDURE [App].[Pr_ApplicationsAwaitingInspectionsGet]
(
	@UserID BIGINT
)
AS
BEGIN
	DECLARE @EntityIDToCheck INT = 1000 /*Application*/
	DECLARE @ItemIDToCheck BIGINT = NULL
	DECLARE @Action VARCHAR(255) = 'Search'
	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	
	
	SELECT I.*
		, AD.ApplicationNumber AS ReferenceNumber
		, LTRIM(RTRIM(
			CASE WHEN ISNULL(AAD.IsApplicantIndividual, 0) = 1
				THEN ISNULL(AAD.FirstNames, '') + ' ' + ISNULL(AAD.SurnameOrCompanyName, '')
				ELSE ISNULL(AAD.SurnameOrCompanyName, '')
			END
		)) AS ApplicantName
	FROM App.ApplicationInspectionDetails I
		INNER JOIN App.ApplicationDetails AD ON AD.ApplicationDetailID = I.ApplicationDetailID
		LEFT JOIN App.ApplicationApplicantDetails AAD ON AAD.ApplicationDetailID = AD.ApplicationDetailID
		INNER JOIN Wf.WorkflowInstances W ON W.WorkflowInstanceID = AD.WorkflowInstanceID
		INNER JOIN Wf.WorkflowInstanceTasks WT ON WT.WorkflowInstanceID = W.WorkflowInstanceID
	WHERE AD.OfficeLocationID = @OfficeLocationID
	AND InspectionStatusID = 1 /*Awaiting inspection date*/
	AND InspectorID IS NULL
	AND WT.WorkflowInstanceTaskStatusID = 1
	AND WT.WorkflowTemplateStepID = 1010

END
GO

IF OBJECT_ID('App.Pr_InspectorInspectionsGet') IS NULL
    EXEC('CREATE PROCEDURE App.Pr_InspectorInspectionsGet AS BEGIN SET NOCOUNT ON; END');
GO

ALTER PROCEDURE [App].[Pr_InspectorInspectionsGet]
(
	@UserID BIGINT
)
AS
BEGIN
	DECLARE @EntityIDToCheck INT = NULL
	DECLARE @ItemIDToCheck BIGINT = NULL
	DECLARE @Action VARCHAR(255) = NULL
	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	
	
	SELECT I.InspectorID
		, I.Name
		, InspectionDateFrom AS startDate
		, InspectionDateTo AS endDate
		, InspectionAddress
		, A.ApplicationNumber AS ReferenceNumber
		, LTRIM(RTRIM(
			CASE WHEN ISNULL(AAD.IsApplicantIndividual, 0) = 1
				THEN ISNULL(AAD.FirstNames, '') + ' ' + ISNULL(AAD.SurnameOrCompanyName, '')
				ELSE ISNULL(AAD.SurnameOrCompanyName, '')
			END
		)) AS ApplicantName
	FROM [Md].[Inspectors] AS I
		INNER JOIN App.ApplicationInspectionDetails AS AID ON I.InspectorID = AID.InspectorID AND AID.InspectionStatusID = 2 /*Awaiting inspection outcome*/
		INNER JOIN App.ApplicationDetails A ON A.ApplicationDetailID = AID.ApplicationDetailID
		LEFT JOIN App.ApplicationApplicantDetails AAD ON AAD.ApplicationDetailID = A.ApplicationDetailID
		INNER JOIN Wf.WorkflowInstances W ON W.WorkflowInstanceID = A.WorkflowInstanceID
		INNER JOIN Wf.WorkflowInstanceTasks WT ON WT.WorkflowInstanceID = W.WorkflowInstanceID
	WHERE A.OfficeLocationID = @OfficeLocationID
	AND I.IsCancelled = 0
	AND InspectionStatusID = 2 /*Awaiting inspection outcome*/
	AND AID.InspectorID IS NOT NULL
	AND WT.WorkflowInstanceTaskStatusID = 1
	AND WT.WorkflowTemplateStepID = 1010
	
END
GO

IF OBJECT_ID('App.Pr_ApplicationInspectionDetailUpdate') IS NULL
    EXEC('CREATE PROCEDURE App.Pr_ApplicationInspectionDetailUpdate AS BEGIN SET NOCOUNT ON; END');
GO

ALTER PROCEDURE [App].[Pr_ApplicationInspectionDetailUpdate]
(
	@ApplicationDetailID BIGINT,
	@InspectionStatusID SMALLINT = NULL,
	@InspectorID BIGINT = NULL,
	@InspectionDateFrom DATETIME = NULL,
	@InspectionDateTo DATETIME = NULL,
	@UserID BIGINT
)
AS
BEGIN
	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	

	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](COALESCE(NULLIF(LTRIM(RTRIM(TelCell)), ''), NULLIF(LTRIM(RTRIM(TelPhone)), '')))
		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)
		
			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
		
	UPDATE App.ApplicationInspectionDetails
	SET 
		InspectionStatusID = COALESCE(@InspectionStatusID, InspectionStatusID),
		InspectorID        = COALESCE(@InspectorID,        InspectorID),
		InspectionDateFrom = COALESCE(@InspectionDateFrom, InspectionDateFrom),
		InspectionDateTo   = COALESCE(@InspectionDateTo,   InspectionDateTo)
	WHERE ApplicationDetailID = @ApplicationDetailID

END
GO
