USE [LegitimateNational]
GO
/****** Object:  StoredProcedure [App].[Pr_InspectorInspectionsGet]    Script Date: 2026/05/22 16:04:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [App].[Pr_InspectorInspectionsGet]
(
	@UserID BIGINT
)
AS
BEGIN

	/********************************************************************************************
	Get user details and perform security checks
	*********************************************************************************************/	
	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	
	
	/********************************************************************************************
	Get inspections
	*********************************************************************************************/
	SELECT I.InspectorID
		,I.Name
		,InspectionDateFrom AS startDate
		, InspectionDateTo AS endDate
		, InspectionAddress
		, A.ApplicationNumber  AS ReferenceNumber
	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
		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
