ALTER PROCEDURE [App].[Pr_AdvancedSearch]
	@SearchTerm VARCHAR(30)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @RootUrl VARCHAR(200) = System.Fn_GetSettingValue('SystemUrl')
   	DECLARE @SearchType VARCHAR (30)
	DECLARE @SearchNumber VARCHAR (30)
	DECLARE @SearchTermFull VARCHAR (30)
	DECLARE @IsReferenceNumberSearch BIT = 0

	IF UPPER(LEFT(@SearchTerm,3)) = 'APP'
	BEGIN
		SET @SearchTerm = 'APP' + System.Fn_PadLeftString('0', 7, RIGHT(@SearchTerm, LEN(@SearchTerm) - 3))
		SET @IsReferenceNumberSearch = 1
	END
	ELSE IF UPPER(LEFT(@SearchTerm,3)) = 'LIR' 
	BEGIN
		SET @SearchTerm = 'LIR' + System.Fn_PadLeftString('0', 7, RIGHT(@SearchTerm, LEN(@SearchTerm) - 3))
		SET @IsReferenceNumberSearch = 1
	END
	ELSE IF UPPER(LEFT(@SearchTerm,3)) = 'LER' 
	BEGIN
		SET @SearchTerm = 'LER' + System.Fn_PadLeftString('0', 7, RIGHT(@SearchTerm, LEN(@SearchTerm) - 3))
		SET @IsReferenceNumberSearch = 1
	END

	IF UPPER(LEFT(@SearchTerm,3)) = 'ADJ' 
	BEGIN
		DECLARE @AdjudicationNumber VARCHAR(20)

		SET @AdjudicationNumber = REPLACE(UPPER(@SearchTerm),'ADJ','')

		SELECT TOP 20 Ad.AdjudicationDetailID AS ID, 'Adjudication' + CAST(Ad.AdjudicationDetailID AS VARCHAR(20)) + '##Meeting Date ' + FORMAT(Ad.MeetingDate,'dd MMM yyyy') + '##Meeting Time' + Ad.MeetingTime + '##' + Ads.Title AS Description, @RootUrl + 'AdjudicationConsolePaneladjudicationDetailID=' + CAST(Ad.AdjudicationDetailID AS VARCHAR(20)) AS URL, '0' AS OpenInNewWindow
		FROM App.AdjudicationDetails Ad
			INNER JOIN App.AdjudicationDetailStatus Ads ON Ad.AdjudicationDetailStatusID = Ads.AdjudicationDetailStatusID
		WHERE (
			CASE 
				WHEN ISNULL(@AdjudicationNumber,'')  '' AND ad.AdjudicationDetailID  @AdjudicationNumber THEN 0
				ELSE 1
			END	
		) = 1
		ORDER BY MeetingDate DESC
	END
	ELSE IF UPPER(LEFT(@SearchTerm,3)) = 'NRR' 
	BEGIN
		SELECT TOP 20 Nrrd.NewRouteRequestDetailID AS ID, Wi.ReferenceNumber + '##' + LEFT(ISNULL(Nrrd.RouteDescription,''),100) + '##' + ISNULL(UPPER(Nrrd.NationalRouteNumber),'') + ' ' + ISNULL(UPPER(Nrrd.BoardRouteNumber),'') + '##' + '' AS Description, @RootUrl + 'NewRouteRequestConsoleworkflowid=1&itemid=' + CAST(Nrrd.NewRouteRequestDetailID AS VARCHAR(10)) AS URL, '0' AS OpenInNewWindow
		FROM App.NewRouteRequestDetails Nrrd						
			INNER JOIN Wf.WorkflowInstances Wi ON Nrrd.WorkflowInstanceID = Wi.WorkflowInstanceID
		WHERE 
			Nrrd.IsCancelled = 0
			AND
			(
				Wi.ReferenceNumber LIKE '%' + @SearchTerm + '%' 
			)
	END	
	ELSE IF @IsReferenceNumberSearch = 1
	BEGIN		
		SELECT TOP 20 Ard.ApplicationRequestDetailID AS ID, Wi.ReferenceNumber + '##' + ISNULL(Ard.Description,'') + '##' + ISNULL(UPPER(Aad.FirstNames),'') + ' ' + ISNULL(UPPER(Aad.SurnameOrCompanyName),'') + '##' + ISNULL(Aad.IDNumberOrBusinessRegistrationNo,'') AS Description, @RootUrl + 'ApplicationRequestConsoleworkflowid=1&itemid=' + CAST(Ard.ApplicationRequestDetailID AS VARCHAR(10)) AS URL, '0' AS OpenInNewWindow
		FROM App.ApplicationRequestDetails Ard
			INNER JOIN App.ApplicationApplicantDetails Aad ON Aad.ApplicationRequestDetailID = Ard.ApplicationRequestDetailID
			INNER JOIN App.ApplicationExistingLicences Ael ON Ael.ApplicationRequestDetailID = Ard.ApplicationRequestDetailID		
			INNER JOIN Wf.WorkflowInstances Wi ON Ard.WorkflowInstanceID = Wi.WorkflowInstanceID
		WHERE 
			Ard.IsCancelled = 0 
			AND	Wi.ReferenceNumber = @SearchTerm			
		UNION
		SELECT TOP 20 Lid.LicenceIssueDetailID AS ID, Wi2.ReferenceNumber + '##' + Wi.ReferenceNumber + ',' + ISNULL(Ard.Description,'') + '##' + ISNULL(UPPER(M.FirstNames),'') + ' ' + ISNULL(UPPER(M.SurnameOrCompanyName),'') + '##' + ISNULL(M.IDNumberOrBusinessRegistrationNo,'') + ',' + ISNULL(L.OperatingLicenceNumber,'') AS Description, @RootUrl + 'LicenceIssueRequestConsoleworkflowid=5&itemid=' + CAST(Lid.LicenceIssueDetailID AS VARCHAR(10)) AS URL, '0' AS OpenInNewWindow
		FROM App.LicenceIssueDetails Lid
			INNER JOIN Verified.Licences L ON Lid.VerifiedLicenceID = L.LicenceID
			INNER JOIN App.ApplicationRequestDetails Ard ON Lid.ApplicationRequestDetailID = Ard.ApplicationRequestDetailID
			INNER JOIN Verified.Members M ON L.MemberID = M.MemberID
			INNER JOIN App.ApplicationExistingLicences Ael ON Ael.ApplicationRequestDetailID = Ard.ApplicationRequestDetailID		
			INNER JOIN Wf.WorkflowInstances Wi ON Ard.WorkflowInstanceID = Wi.WorkflowInstanceID
			INNER JOIN Wf.WorkflowInstances Wi2 ON Lid.WorkflowInstanceID = Wi2.WorkflowInstanceID
		WHERE 
			Ard.IsCancelled = 0
			AND
			(
				Wi.ReferenceNumber = @SearchTerm 
				OR 
				Wi2.ReferenceNumber = @SearchTerm 
			)
		UNION 
		SELECT TOP 20 LuErd.LicenceUpliftmentExtensionRequestDetailID AS ID, Wi3.ReferenceNumber + '##' + Wi.ReferenceNumber + ',' + ISNULL(Ard.Description,'') + '##' + ISNULL(UPPER(M.FirstNames),'') + ' ' + ISNULL(UPPER(M.SurnameOrCompanyName),'') + '##' + ISNULL(M.IDNumberOrBusinessRegistrationNo,'') + ',' + ISNULL(L.OperatingLicenceNumber,'') AS Description, @RootUrl + 'LicenceUpliftmentExtensionRequestConsoleworkflowid=7&itemid=' + CAST(LuErd.LicenceUpliftmentExtensionRequestDetailID AS VARCHAR(10)) AS URL, '0' AS OpenInNewWindow
		FROM App.LicenceUpliftmentExtensionRequestDetails LuErd
			INNER JOIN Verified.Licences L ON LuErd.VerifiedLicenceID = L.LicenceID
			INNER JOIN App.ApplicationRequestDetails Ard ON L.ApplicationRequestDetailID = Ard.ApplicationRequestDetailID
			INNER JOIN Verified.Members M ON L.MemberID = M.MemberID
			INNER JOIN App.ApplicationExistingLicences Ael ON Ael.ApplicationRequestDetailID = Ard.ApplicationRequestDetailID		
			INNER JOIN Wf.WorkflowInstances Wi ON Ard.WorkflowInstanceID = Wi.WorkflowInstanceID
			INNER JOIN Wf.WorkflowInstances Wi3 ON LuErd.WorkflowInstanceID = Wi3.WorkflowInstanceID
		WHERE 
			Ard.IsCancelled = 0
			AND
			(
				Wi.ReferenceNumber = @SearchTerm 
				OR 
				Wi3.ReferenceNumber = @SearchTerm 
			)
		ORDER BY Ard.ApplicationRequestDetailID DESC
	END
	ELSE
	BEGIN
		SELECT TOP 20 Ard.ApplicationRequestDetailID AS ID, Wi.ReferenceNumber + '##' + ISNULL(Ard.Description,'') + '##' + ISNULL(UPPER(Aad.FirstNames),'') + ' ' + ISNULL(UPPER(Aad.SurnameOrCompanyName),'') + '##' + ISNULL(Aad.IDNumberOrBusinessRegistrationNo,'') AS Description, @RootUrl + 'ApplicationRequestConsoleworkflowid=1&itemid=' + CAST(Ard.ApplicationRequestDetailID AS VARCHAR(10)) AS URL, '0' AS OpenInNewWindow
		FROM App.ApplicationRequestDetails Ard
			INNER JOIN App.ApplicationApplicantDetails Aad ON Aad.ApplicationRequestDetailID = Ard.ApplicationRequestDetailID
			INNER JOIN App.ApplicationExistingLicences Ael ON Ael.ApplicationRequestDetailID = Ard.ApplicationRequestDetailID		
			INNER JOIN Wf.WorkflowInstances Wi ON Ard.WorkflowInstanceID = Wi.WorkflowInstanceID
		WHERE 
			Ard.IsCancelled = 0
			AND
			(
				Wi.ReferenceNumber LIKE '%' + @SearchTerm + '%' 
				OR
				Ard.Description LIKE '%' + @SearchTerm + '%'
				OR
				Aad.IDNumberOrBusinessRegistrationNo LIKE '%' + @SearchTerm + '%'
				OR 
				Ael.OperatingLicenceNumber LIKE '%' + @SearchTerm + '%'
			)
		UNION
		SELECT TOP 20 Lid.LicenceIssueDetailID AS ID, Wi2.ReferenceNumber + '##' + Wi.ReferenceNumber + ',' + ISNULL(Ard.Description,'') + '##' + ISNULL(UPPER(M.FirstNames),'') + ' ' + ISNULL(UPPER(M.SurnameOrCompanyName),'') + '##' + ISNULL(M.IDNumberOrBusinessRegistrationNo,'') + ',' + ISNULL(L.OperatingLicenceNumber,'') AS Description, @RootUrl + 'LicenceIssueRequestConsoleworkflowid=5&itemid=' + CAST(Lid.LicenceIssueDetailID AS VARCHAR(10)) AS URL, '0' AS OpenInNewWindow
		FROM App.LicenceIssueDetails Lid
			INNER JOIN Verified.Licences L ON Lid.VerifiedLicenceID = L.LicenceID
			INNER JOIN App.ApplicationRequestDetails Ard ON Lid.ApplicationRequestDetailID = Ard.ApplicationRequestDetailID
			INNER JOIN Verified.Members M ON L.MemberID = M.MemberID
			INNER JOIN App.ApplicationExistingLicences Ael ON Ael.ApplicationRequestDetailID = Ard.ApplicationRequestDetailID		
			INNER JOIN Wf.WorkflowInstances Wi ON Ard.WorkflowInstanceID = Wi.WorkflowInstanceID
			INNER JOIN Wf.WorkflowInstances Wi2 ON Lid.WorkflowInstanceID = Wi2.WorkflowInstanceID
		WHERE 
			Ard.IsCancelled = 0
			AND
			(
				Wi.ReferenceNumber LIKE '%' + @SearchTerm + '%' 
				OR 
				Wi2.ReferenceNumber LIKE '%' + @SearchTerm + '%' 
				OR
				Ard.Description LIKE '%' + @SearchTerm + '%'
				OR
				M.IDNumberOrBusinessRegistrationNo LIKE '%' + @SearchTerm + '%'			
				OR 
				L.OperatingLicenceNumber LIKE '%' + @SearchTerm + '%'
			)
		UNION 
		SELECT TOP 20 LuErd.LicenceUpliftmentExtensionRequestDetailID AS ID, Wi3.ReferenceNumber + '##' + Wi.ReferenceNumber + ',' + ISNULL(Ard.Description,'') + '##' + ISNULL(UPPER(M.FirstNames),'') + ' ' + ISNULL(UPPER(M.SurnameOrCompanyName),'') + '##' + ISNULL(M.IDNumberOrBusinessRegistrationNo,'') + ',' + ISNULL(L.OperatingLicenceNumber,'') AS Description, @RootUrl + 'LicenceUpliftmentExtensionRequestConsoleworkflowid=7&itemid=' + CAST(LuErd.LicenceUpliftmentExtensionRequestDetailID AS VARCHAR(10)) AS URL, '0' AS OpenInNewWindow
		FROM App.LicenceUpliftmentExtensionRequestDetails LuErd
			INNER JOIN Verified.Licences L ON LuErd.VerifiedLicenceID = L.LicenceID
			INNER JOIN App.ApplicationRequestDetails Ard ON L.ApplicationRequestDetailID = Ard.ApplicationRequestDetailID
			INNER JOIN Verified.Members M ON L.MemberID = M.MemberID
			INNER JOIN App.ApplicationExistingLicences Ael ON Ael.ApplicationRequestDetailID = Ard.ApplicationRequestDetailID		
			INNER JOIN Wf.WorkflowInstances Wi ON Ard.WorkflowInstanceID = Wi.WorkflowInstanceID
			INNER JOIN Wf.WorkflowInstances Wi3 ON LuErd.WorkflowInstanceID = Wi3.WorkflowInstanceID
		WHERE 
			Ard.IsCancelled = 0
			AND
			(
				Wi.ReferenceNumber LIKE '%' + @SearchTerm + '%' 
				OR 
				Wi3.ReferenceNumber LIKE '%' + @SearchTerm + '%' 
				OR
				Ard.Description LIKE '%' + @SearchTerm + '%'
				OR
				M.IDNumberOrBusinessRegistrationNo LIKE '%' + @SearchTerm + '%'			
				OR 
				L.OperatingLicenceNumber LIKE '%' + @SearchTerm + '%'
			)
		ORDER BY Ard.ApplicationRequestDetailID DESC
	END

END






