code

USE [SudBiH]
GO
/****** Object:  StoredProcedure [dbo].[SearchCourtPractices]    Script Date: 6/14/2021 3:40:25 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[SearchCourtPractices]
	-- Add the parameters for the stored procedure here
	@searchterm [nvarchar](max),	
	@casenumber [nvarchar](max),
	@casetitle [nvarchar](max),
	@department int = 0,
	@crimecategory int = 0,
	@verdictlevel nvarchar(max),
	@verdictleveltitle nvarchar(max),
	@placeofcommittment [nvarchar](max),
	@datefrom [nvarchar](max),
	@dateto [nvarchar](max),
    @tag [nvarchar](2)

AS
BEGIN
	DECLARE @exec_sql  nvarchar(max)
    SET NOCOUNT ON;

	IF (@searchterm = N' ')
	BEGIN
		SET @searchterm = NULL
	END


	SET @exec_sql = '
    SELECT 
	null AS Title, 
	null AS Subtitle, 
	null AS Category, 
	null AS Url
    WHERE 1 = 2
	'

	IF(@searchterm IS NOT NULL AND LEN(@searchterm) > 0)
	BEGIN
		SET @searchterm = N'"*' + REPLACE(TRIM(@searchterm), N' ', N'*" AND "*') + N'*"'
		SET @exec_sql += '
		UNION

		SELECT 
		Title, 
		Subtitle, 
		Category, 
		Url
		FROM (

		SELECT
			mr.CaseTitle AS Title,
			m.CaseNumber AS Subtitle,
			1 AS Category,
			N''/Court/Case/'' + CAST(m.Id AS nvarchar(max)) AS Url
		FROM Matrix m
		INNER JOIN 
			MatrixResources mr on m.Id = mr.MatrixId
		WHERE 
			mr.LT = N''' + @tag + '''AND
			(CONTAINS(mr.CaseTitle, N''' + @searchterm + ''') OR 
			(CONTAINS(m.CaseNumber, N''' + @searchterm + ''') OR m.CaseNumber LIKE N''%' + @searchterm + '%'') OR
			CONTAINS(m.PlaintiffNumber, N''' + @searchterm + ''') OR 
			CONTAINS(mr.IndictmentSummary, N''' + @searchterm + ''') OR 
			CONTAINS(mr.CourseOfProceedings, N''' + @searchterm + ''') OR
			CONTAINS(mr.Notes, N''' + @searchterm + ''') OR
			CONTAINS(mr.CaseSubtitle,  N''' + @searchterm + ''') OR
			CONTAINS(mr.CaseInformation,  N''' + @searchterm + ''')
			)

		
		UNION
		SELECT
			mr.CaseTitle AS Title,
			m.CaseNumber AS Subtitle,
			1 AS Category,
			N''/Court/Case/'' + CAST(m.Id AS nvarchar(max)) AS Url
		FROM Matrix m
		INNER JOIN 
			MatrixResources mr on m.Id = mr.MatrixId
		INNER JOIN 
			Defendant d on m.Id = d.MatrixId
		WHERE
			d.LT = N''' + @tag + ''' AND mr.LT = N''' + @tag + ''' AND
			(CONTAINS(d.FullName, N''' + @searchterm + ''') OR
			CONTAINS(d.Punishment, N''' + @searchterm + ''') OR
			CONTAINS(d.Payments, N''' + @searchterm + ''') OR
			CONTAINS(d.PropertyClaim, N''' + @searchterm + ''') OR
			CONTAINS(d.EducationLevel, N''' + @searchterm + '''))
		
		UNION
		SELECT
			mr.CaseTitle AS Title,
			m.CaseNumber AS Subtitle,
			1 AS Category,
			N''/Court/Case/'' + CAST(m.Id AS nvarchar(max)) AS Url
		FROM Matrix m
		INNER JOIN 
			MatrixResources mr on m.Id = mr.MatrixId
		INNER JOIN
			SafetyMeasure sm on m.Id = sm.MatrixId
		WHERE 
			sm.LT = N''' + @tag + ''' AND mr.LT = N''' + @tag + ''' AND
			(CONTAINS(sm.Defendant, N''' + @searchterm + ''') OR
			CONTAINS(sm.PMZJ, N''' + @searchterm + ''') OR
			CONTAINS(sm.Amount, N''' + @searchterm + ''') OR
			CONTAINS(sm.Period, N''' + @searchterm + '''))
		
		UNION
		SELECT
			mr.CaseTitle AS Title,
			m.CaseNumber AS Subtitle,
			1 AS Category,
			N''/Court/Case/'' + CAST(m.Id AS nvarchar(max)) AS Url
		FROM Matrix m
		INNER JOIN 
			MatrixResources mr on m.Id = mr.MatrixId
		INNER JOIN
			CountOfIndictment coi on m.Id = coi.MatrixId
		WHERE
			coi.LT = N''' + @tag + ''' AND mr.LT = N''' + @tag + ''' AND
			(CONTAINS(coi.Defendant, N''' + @searchterm + ''') OR
			CONTAINS(coi.Offense, N''' + @searchterm + ''') OR
			CONTAINS(coi.PlaceOfCommittment, N''' + @searchterm + '''))
		
		UNION
		SELECT
			mr.CaseTitle AS Title,
			m.CaseNumber AS Subtitle,
			1 AS Category,
			N''/Court/Case/'' + CAST(m.Id AS nvarchar(max)) AS Url
		FROM Matrix m
		INNER JOIN 
			MatrixResources mr on m.Id = mr.MatrixId
		INNER JOIN
			Verdict v on m.Id = v.MatrixId
		WHERE
			v.LT = N''' + @tag + ''' AND mr.LT = N''' + @tag + ''' AND
			(CONTAINS(v.Summary, N''' + @searchterm + ''') OR
			CONTAINS(v.Offense, N''' + @searchterm + ''') OR
			CONTAINS(v.PrimarySanction, N''' + @searchterm + ''') OR
			CONTAINS(v.SecondarySancion, N''' + @searchterm + ''') OR
			CONTAINS(v.Indictment, N''' + @searchterm + ''') OR
			CONTAINS(v.SuspendedSentence, N''' + @searchterm + '''))
			
		
		UNION
		SELECT
			mr.CaseTitle AS Title,
			m.CaseNumber AS Subtitle,
			1 AS Category,
			N''/Court/Case/'' + CAST(m.Id AS nvarchar(max)) AS Url
		FROM Matrix m
		INNER JOIN 
			MatrixResources mr on m.Id = mr.MatrixId
		INNER JOIN
			Verdict v on m.Id = v.MatrixId
		WHERE
			v.LT = N''' + @tag + ''' AND mr.LT = N''' + @tag + ''' AND
			CONTAINS(v.Content, N''' + @searchterm + ''')

		UNION
		SELECT
			m.CaseNumber + N'' '' + mr.CaseTitle AS Title,
			Cast(v.VerdictLevelId AS nvarchar(max)) AS Subtitle,
			2 AS Category,
			N''/Court/Open/'' + CAST(v.Id AS nvarchar(max)) + N''?n='' + v.FileName AS Url
		FROM Matrix m
		INNER JOIN 
			MatrixResources mr on m.Id = mr.MatrixId
		INNER JOIN
			Verdict v on m.Id = v.MatrixId
		WHERE
			v.LT = N''' + @tag + ''' AND mr.LT = N''' + @tag + ''' AND
			(CONTAINS(mr.CaseTitle, N''' + @searchterm + ''') OR 
			(CONTAINS(m.CaseNumber, N''' + @searchterm + ''') OR m.CaseNumber LIKE N''%' + @searchterm + '%'') OR
			CONTAINS(m.PlaintiffNumber, N''' + @searchterm + ''') OR 
			CONTAINS(mr.IndictmentSummary, N''' + @searchterm + ''') OR 
			CONTAINS(mr.CourseOfProceedings, N''' + @searchterm + ''') OR
			CONTAINS(mr.Notes, N''' + @searchterm + ''') OR
			CONTAINS(mr.CaseSubtitle,  N''' + @searchterm + ''') OR
			CONTAINS(mr.CaseInformation,  N''' + @searchterm + '''))

		
		UNION
		SELECT
			m.CaseNumber + N'' '' + mr.CaseTitle AS Title,
			Cast(v.VerdictLevelId AS nvarchar(max)) AS Subtitle,
			2 AS Category,
			N''/Court/Open/'' + CAST(v.Id AS nvarchar(max)) + N''?n='' + v.FileName AS Url
		FROM Matrix m
		INNER JOIN 
			MatrixResources mr on m.Id = mr.MatrixId
		INNER JOIN
			Verdict v on m.Id = v.MatrixId
		INNER JOIN 
			Defendant d on m.Id = d.MatrixId
		WHERE
			v.LT = N''' + @tag + ''' AND mr.LT = N''' + @tag + ''' AND
			d.LT = N''' + @tag + ''' AND
			(CONTAINS(d.FullName, N''' + @searchterm + ''') OR
			CONTAINS(d.Punishment, N''' + @searchterm + ''') OR
			CONTAINS(d.Payments, N''' + @searchterm + ''') OR
			CONTAINS(d.PropertyClaim, N''' + @searchterm + ''') OR
			CONTAINS(d.EducationLevel, N''' + @searchterm + '''))

		UNION
		SELECT
			m.CaseNumber + N'' '' + mr.CaseTitle AS Title,
			Cast(v.VerdictLevelId AS nvarchar(max)) AS Subtitle,
			2 AS Category,
			N''/Court/Open/'' + CAST(v.Id AS nvarchar(max)) + N''?n='' + v.FileName AS Url
		FROM Matrix m
		INNER JOIN 
			MatrixResources mr on m.Id = mr.MatrixId
		INNER JOIN
			Verdict v on m.Id = v.MatrixId
		INNER JOIN
			CountOfIndictment coi on m.Id = coi.MatrixId
		INNER JOIN
			SafetyMeasure sm on m.Id = sm.MatrixId
		WHERE
			v.LT = N''' + @tag + ''' AND mr.LT = N''' + @tag + ''' AND
			coi.LT = N''' + @tag + ''' AND
			(CONTAINS(coi.Defendant, N''' + @searchterm + ''') OR
			CONTAINS(coi.Offense, N''' + @searchterm + ''') OR
			CONTAINS(coi.PlaceOfCommittment, N''' + @searchterm + ''') OR
			CONTAINS(coi.DamageDoneTo, N''' + @searchterm + '''))

		UNION
		SELECT
			m.CaseNumber + N'' '' + mr.CaseTitle AS Title,
			Cast(v.VerdictLevelId AS nvarchar(max)) AS Subtitle,
			2 AS Category,
			N''/Court/Open/'' + CAST(v.Id AS nvarchar(max)) + N''?n='' + v.FileName AS Url
		FROM Matrix m
		INNER JOIN 
			MatrixResources mr on m.Id = mr.MatrixId
		INNER JOIN
			Verdict v on m.Id = v.MatrixId
		INNER JOIN
			SafetyMeasure sm on m.Id = sm.MatrixId
		WHERE
			v.LT = N''' + @tag + ''' AND mr.LT = N''' + @tag + ''' AND
			sm.LT = N''' + @tag + ''' AND
			(CONTAINS(sm.Defendant, N''' + @searchterm + ''') OR
			CONTAINS(sm.PMZJ, N''' + @searchterm + ''') OR
			CONTAINS(sm.Amount, N''' + @searchterm + ''') OR
			CONTAINS(sm.Period, N''' + @searchterm + '''))

		UNION
		SELECT
			m.CaseNumber + N'' '' + mr.CaseTitle AS Title,
			Cast(v.VerdictLevelId AS nvarchar(max)) AS Subtitle,
			2 AS Category,
			N''/Court/Open/'' + CAST(v.Id AS nvarchar(max)) + N''?n='' + v.FileName AS Url
		FROM Matrix m
		INNER JOIN 
			MatrixResources mr on m.Id = mr.MatrixId
		INNER JOIN
			Verdict v on m.Id = v.MatrixId
		WHERE
			v.LT = N''' + @tag + ''' AND mr.LT = N''' + @tag + ''' AND
			(CONTAINS(v.Content, N''' + @searchterm + ''') OR
			CONTAINS(v.Summary, N''' + @searchterm + ''') OR
			CONTAINS(v.Offense, N''' + @searchterm + ''') OR
			CONTAINS(v.PrimarySanction, N''' + @searchterm + ''') OR
			CONTAINS(v.SecondarySancion, N''' + @searchterm + ''') OR
			CONTAINS(v.Indictment, N''' + @searchterm + ''') OR
			CONTAINS(v.SuspendedSentence, N''' + @searchterm + '''))
		
		UNION
		SELECT
			pr.Title AS Title,
			pr.Subtitle AS Subtitle,
			3 AS Category,
			N''/Post/Read/'' + p.Link AS Url
		FROM Post p
		INNER JOIN 
			PostResource pr on p.Id = pr.PostId
		INNER JOIN 
			Matrix m on m.Id = p.MatrixId
		INNER JOIN
			MatrixResources mr on m.Id = mr.MatrixId
		WHERE
			mr.LT = N''' + @tag + ''' AND pr.LT = N''' + @tag + ''' AND
			(CONTAINS(pr.Title, N''' + @searchterm + ''') OR
			CONTAINS(pr.Subtitle, N''' + @searchterm + ''') OR
			CONTAINS(pr.HtmlContent, N''' + @searchterm + '''))

		UNION
		SELECT
			pr.Title AS Title,
			pr.Subtitle AS Subtitle,
			3 AS Category,
			N''/Post/Read/'' + p.Link AS Url
		FROM Post p
		INNER JOIN 
			PostResource pr on p.Id = pr.PostId
		INNER JOIN 
			Matrix m on m.Id = p.MatrixId
		INNER JOIN
			MatrixResources mr on m.Id = mr.MatrixId
		WHERE
			mr.LT = N''' + @tag + ''' AND pr.LT = N''' + @tag + ''' AND
			(CONTAINS(mr.CaseTitle, N''' + @searchterm + ''') OR 
			CONTAINS(mr.IndictmentSummary, N''' + @searchterm + ''') OR 
			CONTAINS(mr.CourseOfProceedings, N''' + @searchterm + ''') OR
			CONTAINS(mr.CaseSubtitle,  N''' + @searchterm + ''') OR
			CONTAINS(mr.CaseInformation,  N''' + @searchterm + '''))

		UNION
		SELECT
			pr.Title AS Title,
			pr.Subtitle AS Subtitle,
			3 AS Category,
			N''/Post/Read/'' + p.Link AS Url
		FROM Post p
		INNER JOIN 
			PostResource pr on p.Id = pr.PostId
		INNER JOIN 
			Matrix m on m.Id = p.MatrixId
		INNER JOIN
			MatrixResources mr on m.Id = mr.MatrixId
		INNER JOIN 
			Defendant d on m.Id = d.MatrixId
		WHERE
			mr.LT = N''' + @tag + ''' AND pr.LT = N''' + @tag + ''' AND d.LT = N''' + @tag + ''' AND 
			(CONTAINS(d.Punishment, N''' + @searchterm + ''') OR
			CONTAINS(d.Payments, N''' + @searchterm + ''') OR
			CONTAINS(d.PropertyClaim, N''' + @searchterm + '''))

		) AS SEARCHTERM 
		'
	END

	IF (@casenumber IS NOT NULL AND LEN(@casenumber) > 0)
	BEGIN
		SET @exec_sql += CASE 
			WHEN (@searchterm IS NOT NULL AND LEN(@searchterm) > 0) THEN 'INTERSECT' 
			ELSE 'UNION' 
			END

		SET @exec_sql += '

		SELECT 
		Title, 
		Subtitle, 
		Category, 
		Url
		FROM (

		SELECT 
			mr.CaseTitle AS Title,
			m.CaseNumber AS Subtitle,
			1 AS Category,
			N''/Court/Case/'' + CAST(m.Id AS nvarchar(max)) AS Url
		FROM Matrix m
		INNER JOIN
				MatrixResources mr on m.Id = mr.MatrixId
		WHERE
			mr.LT = N''' + @tag + ''' AND
			(CONTAINS(m.CaseNumber, N''' + N'"*' + REPLACE(TRIM(@casenumber), N' ', N'*" AND "*') + N'*"' + ''') OR m.CaseNumber LIKE N''%' + @casenumber + '%'')
		
		UNION
		SELECT
			m.CaseNumber + N'' '' + mr.CaseTitle AS Title,
			Cast(v.VerdictLevelId AS nvarchar(max)) AS Subtitle,
			2 AS Category,
			N''/Court/Open/'' + CAST(v.Id AS nvarchar(max)) + N''?n='' + v.FileName AS Url
		FROM Matrix m
		INNER JOIN 
			MatrixResources mr on m.Id = mr.MatrixId
		INNER JOIN
			Verdict v on m.Id = v.MatrixId
		WHERE
			v.LT = N''' + @tag + ''' AND mr.LT = N''' + @tag + ''' AND
			(CONTAINS(m.CaseNumber, N''' + N'"*' + REPLACE(TRIM(@casenumber), N' ', N'*" AND "*') + N'*"' + ''') OR m.CaseNumber LIKE N''%' + @casenumber + '%'')

		UNION
		SELECT
			m.CaseNumber + N'' '' + mr.CaseTitle AS Title,
			Cast(v.VerdictLevelId AS nvarchar(max)) AS Subtitle,
			2 AS Category,
			N''/Court/Open/'' + CAST(v.Id AS nvarchar(max)) + N''?n='' + v.FileName AS Url
		FROM Matrix m
		INNER JOIN 
			MatrixResources mr on m.Id = mr.MatrixId
		INNER JOIN
			Verdict v on m.Id = v.MatrixId
		WHERE
			v.LT = N''' + @tag + ''' AND mr.LT = N''' + @tag + ''' AND
			CONTAINS(v.Content, N''' + N'"*' + REPLACE(TRIM(@casenumber), N' ', N'*" AND "*') + N'*"' + ''')
		
		UNION
		SELECT
			pr.Title AS Title,
			pr.Subtitle AS Subtitle,
			3 AS Category,
			N''/Post/Read/'' + p.Link AS Url
		FROM Post p
		INNER JOIN 
			PostResource pr on p.Id = pr.PostId
		INNER JOIN 
			Matrix m on m.Id = p.MatrixId
		INNER JOIN
			MatrixResources mr on m.Id = mr.MatrixId
		WHERE
			mr.LT = N''' + @tag + ''' AND pr.LT = N''' + @tag + ''' AND
			(CONTAINS(m.CaseNumber, N''' + N'"*' + REPLACE(TRIM(@casenumber), N' ', N'*" AND "*') + N'*"' + ''') OR m.CaseNumber LIKE N''%' + @casenumber + '%'')
		) AS CASENUMBER
		'
	END

	IF (@casetitle IS NOT NULL AND LEN(@casetitle) > 0)
	BEGIN

		SET @exec_sql += CASE 
			WHEN (@searchterm IS NOT NULL AND LEN(@searchterm) > 0) THEN 'INTERSECT' 
			WHEN (@casenumber IS NOT NULL AND LEN(@casenumber) > 0) THEN 'INTERSECT'  
			ELSE 'UNION' 
			END

		SET @exec_sql += '
		
		SELECT 
		Title, 
		Subtitle, 
		Category, 
		Url
		FROM (

		SELECT 
			mr.CaseTitle AS Title,
			m.CaseNumber AS Subtitle,
			1 AS Category,
			N''/Court/Case/'' + CAST(m.Id AS nvarchar(max)) AS Url
		FROM Matrix m
		INNER JOIN 
			MatrixResources mr on m.Id = mr.MatrixId
		WHERE 
			mr.LT = N''' + @tag + ''' AND
			(CONTAINS(mr.CaseTitle, N''' + N'"*' + REPLACE(TRIM(@casetitle), N' ', N'*" AND "*') + N'*"' + ''') OR mr.CaseTitle LIKE N''%' + @casetitle + '%'')
		
		UNION 
		SELECT
			m.CaseNumber + N'' '' + mr.CaseTitle AS Title,
			Cast(v.VerdictLevelId AS nvarchar(max)) AS Subtitle,
			2 AS Category,
			N''/Court/Open/'' + CAST(v.Id AS nvarchar(max)) + N''?n='' + v.FileName AS Url
		FROM Matrix m
		INNER JOIN 
			MatrixResources mr on m.Id = mr.MatrixId
		INNER JOIN
			Verdict v on m.Id = v.MatrixId
		WHERE
			v.LT = N''' + @tag + ''' AND mr.LT = N''' + @tag + ''' AND
			(CONTAINS(m.CaseNumber, N''' + N'"*' + REPLACE(TRIM(@casetitle), N' ', N'*" AND "*') + N'*"' + ''') OR m.CaseNumber LIKE N''%' + @casetitle + '%'') OR
			CONTAINS(v.Content, N''' + N'"*' + REPLACE(TRIM(@casetitle), N' ', N'*" AND "*') + N'*"' + ''')
		
		UNION
		SELECT
			pr.Title AS Title,
			pr.Subtitle AS Subtitle,
			3 AS Category,
			N''/Post/Read/'' + p.Link AS Url
		FROM Post p
		INNER JOIN 
			PostResource pr on p.Id = pr.PostId
		INNER JOIN 
			Matrix m on m.Id = p.MatrixId
		INNER JOIN
			MatrixResources mr on m.Id = mr.MatrixId
		WHERE
			mr.LT = N''' + @tag + ''' AND pr.LT = N''' + @tag + ''' AND
			(CONTAINS(mr.CaseTitle, N''' + N'"*' + REPLACE(TRIM(@casetitle), N' ', N'*" AND "*') + N'*"' + ''') OR mr.CaseTitle LIKE N''%' + @casetitle + '%'') OR
			(CONTAINS(pr.Title, N''' + N'"*' + REPLACE(TRIM(@casetitle), N' ', N'*" AND "*') + N'*"' + ''') OR
			CONTAINS(pr.Subtitle, N''' + N'"*' + REPLACE(TRIM(@casetitle), N' ', N'*" AND "*') + N'*"' + ''') OR
			CONTAINS(pr.HtmlContent, N''' + N'"*' + REPLACE(TRIM(@casetitle), N' ', N'*" AND "*') + N'*"' + '''))
		) AS CASETITLE 
		'

	END

	IF (@department IS NOT NULL AND @department <> 0)
	BEGIN
	
		SET @exec_sql += CASE 
			WHEN (@searchterm IS NOT NULL AND LEN(@searchterm) > 0) THEN 'INTERSECT' 
			WHEN (@casenumber IS NOT NULL AND LEN(@casenumber) > 0) THEN 'INTERSECT'  
			WHEN (@casetitle IS NOT NULL AND LEN(@casetitle) > 0) THEN 'INTERSECT'  
			ELSE 'UNION' 
			END

		SET @exec_sql += '
		
		SELECT 
		Title, 
		Subtitle, 
		Category, 
		Url
		FROM (

		SELECT 
			mr.CaseTitle AS Title,
			m.CaseNumber AS Subtitle,
			1 AS Category,
			N''/Court/Case/'' + CAST(m.Id AS nvarchar(max)) AS Url
		FROM Matrix m
		INNER JOIN 
			MatrixResources mr on m.Id = mr.MatrixId
		WHERE 
			mr.LT = N''' + @tag + ''' AND
			m.DepartmentId = ' + CAST(@department AS nvarchar(max)) + '
		
		UNION 
		SELECT
			m.CaseNumber + N'' '' + mr.CaseTitle AS Title,
			Cast(v.VerdictLevelId AS nvarchar(max)) AS Subtitle,
			2 AS Category,
			N''/Court/Open/'' + CAST(v.Id AS nvarchar(max)) + N''?n='' + v.FileName AS Url
		FROM Matrix m
		INNER JOIN 
			MatrixResources mr on m.Id = mr.MatrixId
		INNER JOIN
			Verdict v on m.Id = v.MatrixId
		WHERE
			v.LT = N''' + @tag + ''' AND mr.LT = N''' + @tag + ''' AND
			m.DepartmentId = ' + CAST(@department AS nvarchar(max)) + '
		
		UNION
		SELECT
			pr.Title AS Title,
			pr.Subtitle AS Subtitle,
			3 AS Category,
			N''/Post/Read/'' + p.Link AS Url
		FROM Post p
		INNER JOIN 
			PostResource pr on p.Id = pr.PostId
		INNER JOIN 
			Matrix m on m.Id = p.MatrixId
		INNER JOIN
			MatrixResources mr on m.Id = mr.MatrixId
		WHERE
			mr.LT = N''' + @tag + ''' AND pr.LT = N''' + @tag + ''' AND
			m.DepartmentId = ' + CAST(@department AS nvarchar(max)) + '
		) AS DEPARTMENT 
		'
	END

	IF (@crimecategory IS NOT NULL AND @crimecategory <> 0)
	BEGIN
	
		SET @exec_sql += CASE 
			WHEN (@searchterm IS NOT NULL AND LEN(@searchterm) > 0) THEN 'INTERSECT' 
			WHEN (@casenumber IS NOT NULL AND LEN(@casenumber) > 0) THEN 'INTERSECT'  
			WHEN (@casetitle IS NOT NULL AND LEN(@casetitle) > 0) THEN 'INTERSECT'  
			WHEN (@department IS NOT NULL AND @department <> 0) THEN 'INTERSECT'  
			ELSE 'UNION' 
			END

		SET @exec_sql += '
		
		SELECT 
		Title, 
		Subtitle, 
		Category, 
		Url
		FROM (

		SELECT 
			mr.CaseTitle AS Title,
			m.CaseNumber AS Subtitle,
			1 AS Category,
			N''/Court/Case/'' + CAST(m.Id AS nvarchar(max)) AS Url
		FROM Matrix m
		INNER JOIN 
			MatrixResources mr on m.Id = mr.MatrixId
		WHERE 
			mr.LT = N''' + @tag + ''' AND
			m.CrimeCategoryId = ' + CAST(@crimecategory AS nvarchar(max)) + '
		
		UNION 
		SELECT
			m.CaseNumber + N'' '' + mr.CaseTitle AS Title,
			Cast(v.VerdictLevelId AS nvarchar(max)) AS Subtitle,
			2 AS Category,
			N''/Court/Open/'' + CAST(v.Id AS nvarchar(max)) + N''?n='' + v.FileName AS Url
		FROM Matrix m
		INNER JOIN 
			MatrixResources mr on m.Id = mr.MatrixId
		INNER JOIN
			Verdict v on m.Id = v.MatrixId
		WHERE
			v.LT = N''' + @tag + ''' AND mr.LT = N''' + @tag + ''' AND
			m.CrimeCategoryId = ' + CAST(@crimecategory AS nvarchar(max)) + '
		
		UNION
		SELECT
			pr.Title AS Title,
			pr.Subtitle AS Subtitle,
			3 AS Category,
			N''/Post/Read/'' + p.Link AS Url
		FROM Post p
		INNER JOIN 
			PostResource pr on p.Id = pr.PostId
		INNER JOIN 
			Matrix m on m.Id = p.MatrixId
		INNER JOIN
			MatrixResources mr on m.Id = mr.MatrixId
		WHERE
			mr.LT = N''' + @tag + ''' AND pr.LT = N''' + @tag + ''' AND
			m.CrimeCategoryId = ' + CAST(@crimecategory AS nvarchar(max)) + '
		) AS CATEGORY 
		'
	END

	IF((@verdictlevel IS NOT NULL AND LEN(@verdictlevel) > 0) AND (@verdictleveltitle IS NOT NULL AND LEN(@verdictleveltitle) > 0))
	BEGIN

		SET @exec_sql += CASE 
			WHEN (@searchterm IS NOT NULL AND LEN(@searchterm) > 0) THEN 'INTERSECT' 
			WHEN (@casenumber IS NOT NULL AND LEN(@casenumber) > 0) THEN 'INTERSECT'  
			WHEN (@casetitle IS NOT NULL AND LEN(@casetitle) > 0) THEN 'INTERSECT'  
			WHEN (@department IS NOT NULL AND @department <> 0) THEN 'INTERSECT'  
			WHEN (@crimecategory IS NOT NULL AND @crimecategory <> 0) THEN 'INTERSECT'  
			ELSE 'UNION' 
			END

		SET @exec_sql += '
		
		SELECT 
		Title, 
		Subtitle, 
		Category, 
		Url
		FROM (

		SELECT 
			mr.CaseTitle AS Title,
			m.CaseNumber AS Subtitle,
			1 AS Category,
			N''/Court/Case/'' + CAST(m.Id AS nvarchar(max)) AS Url
		FROM Matrix m
		INNER JOIN 
			MatrixResources mr on m.Id = mr.MatrixId
		INNER JOIN
			Verdict v on m.Id = v.MatrixId
		WHERE 
			v.LT = N''' + @tag + ''' AND mr.LT = N''' + @tag + ''' AND
			v.VerdictLevelId IN (' + @verdictlevel + ')
		
		UNION 
		SELECT
			m.CaseNumber + N'' '' + mr.CaseTitle AS Title,
			Cast(v.VerdictLevelId AS nvarchar(max)) AS Subtitle,
			2 AS Category,
			N''/Court/Open/'' + CAST(v.Id AS nvarchar(max)) + N''?n='' + v.FileName AS Url
		FROM Matrix m
		INNER JOIN 
			MatrixResources mr on m.Id = mr.MatrixId
		INNER JOIN
			Verdict v on m.Id = v.MatrixId
		INNER JOIN 
			VerdictLevelResource vr on v.VerdictLevelId = vr.VerdictLevelId
		WHERE
			v.LT = N''' + @tag + ''' AND mr.LT = N''' + @tag + ''' AND vr.LT = N''' + @tag + ''' AND
			v.VerdictLevelId IN (' + @verdictlevel + ')
		
		UNION
		SELECT
			pr.Title AS Title,
			pr.Subtitle AS Subtitle,
			3 AS Category,
			N''/Post/Read/'' + p.Link AS Url
		FROM Post p
		INNER JOIN 
			PostResource pr on p.Id = pr.PostId
		INNER JOIN 
			Matrix m on m.Id = p.MatrixId
		INNER JOIN
			MatrixResources mr on m.Id = mr.MatrixId
		INNER JOIN
			Verdict v on m.Id = v.MatrixId
		WHERE
			v.LT = N''' + @tag + ''' AND mr.LT = N''' + @tag + ''' AND pr.LT = N''' + @tag + ''' AND
			v.VerdictLevelId IN (' + @verdictlevel + ') AND
			(CONTAINS(v.Content, N''' + @verdictleveltitle + ''') OR
			CONTAINS(pr.Title, N''' + @verdictleveltitle + ''') OR
			CONTAINS(pr.Subtitle, N''' + @verdictleveltitle + ''') OR
			CONTAINS(pr.HtmlContent, N''' + @verdictleveltitle + '''))
		) AS VERDICT 
		'
	END

	IF(@placeofcommittment IS NOT NULL AND LEN(@placeofcommittment) > 0)
	BEGIN

		SET @exec_sql += CASE 
			WHEN (@searchterm IS NOT NULL AND LEN(@searchterm) > 0) THEN 'INTERSECT' 
			WHEN (@casenumber IS NOT NULL AND LEN(@casenumber) > 0) THEN 'INTERSECT'  
			WHEN (@casetitle IS NOT NULL AND LEN(@casetitle) > 0) THEN 'INTERSECT'  
			WHEN (@department IS NOT NULL AND @department <> 0) THEN 'INTERSECT'  
			WHEN (@crimecategory IS NOT NULL AND @crimecategory <> 0) THEN 'INTERSECT'  
			WHEN ((@verdictlevel IS NOT NULL AND LEN(@verdictlevel) > 0) AND (@verdictleveltitle IS NOT NULL AND LEN(@verdictleveltitle) > 0)) THEN 'INTERSECT'  
			ELSE 'UNION' 
			END

		SET @exec_sql += '
		
		SELECT 
		Title, 
		Subtitle, 
		Category, 
		Url
		FROM (

		SELECT 
			mr.CaseTitle AS Title,
			m.CaseNumber AS Subtitle,
			1 AS Category,
			N''/Court/Case/'' + CAST(m.Id AS nvarchar(max)) AS Url
		FROM Matrix m
		INNER JOIN 
			MatrixResources mr on m.Id = mr.MatrixId
		INNER JOIN 
			CountOfIndictment coi on m.Id = coi.MatrixId
		WHERE 
			coi.LT = N''' + @tag + ''' AND mr.LT = N''' + @tag + ''' AND
			CONTAINS(coi.PlaceOfCommittment, N''' + N'"*' + REPLACE(TRIM(@placeofcommittment), N' ', N'*" AND "*') + N'*"' + ''')
		
		UNION 
		SELECT 
			m.CaseNumber + N'' '' + mr.CaseTitle AS Title,
			Cast(v.VerdictLevelId AS nvarchar(max)) AS Subtitle,
			2 AS Category,
			N''/Court/Open/'' + CAST(v.Id AS nvarchar(max)) + N''?n='' + v.FileName AS Url
		FROM Matrix m
		INNER JOIN 
			MatrixResources mr on m.Id = mr.MatrixId
		INNER JOIN
			Verdict v on m.Id = v.MatrixId
		INNER JOIN 
			CountOfIndictment coi on m.Id = coi.MatrixId
		INNER JOIN
			VerdictLevelResource vr on v.VerdictLevelId = vr.VerdictLevelId
		WHERE 
			v.LT = N''' + @tag + ''' AND mr.LT = N''' + @tag + ''' AND vr.LT = N''' + @tag + ''' AND
			(CONTAINS(coi.PlaceOfCommittment, N''' + N'"*' + REPLACE(TRIM(@placeofcommittment), N' ', N'*" AND "*') + N'*"' + ''') OR
			CONTAINS(v.Content, N''' + N'"*' + REPLACE(TRIM(@placeofcommittment), N' ', N'*" AND "*') + N'*"' + '''))
		
		UNION
		SELECT
			pr.Title AS Title,
			pr.Subtitle AS Subtitle,
			3 AS Category,
			N''/Post/Read/'' + p.Link AS Url
		FROM Post p
		INNER JOIN 
			PostResource pr on p.Id = pr.PostId
		INNER JOIN 
			Matrix m on m.Id = p.MatrixId
		INNER JOIN
			MatrixResources mr on m.Id = mr.MatrixId
		INNER JOIN 
			CountOfIndictment coi on m.Id = coi.MatrixId
		WHERE
			coi.LT = N''' + @tag + ''' AND mr.LT = N''' + @tag + ''' AND pr.LT = N''' + @tag + ''' AND
			(CONTAINS(coi.PlaceOfCommittment, N''' + N'"*' + REPLACE(TRIM(@placeofcommittment), N' ', N'*" AND "*') + N'*"' + ''') OR
			CONTAINS(pr.Title, N''' + N'"*' + REPLACE(TRIM(@placeofcommittment), N' ', N'*" AND "*') + N'*"' + ''') OR
			CONTAINS(pr.Subtitle, N''' + N'"*' + REPLACE(TRIM(@placeofcommittment), N' ', N'*" AND "*') + N'*"' + ''') OR
			CONTAINS(pr.HtmlContent, N''' + N'"*' + REPLACE(TRIM(@placeofcommittment), N' ', N'*" AND "*') + N'*"' + '''))
		) AS PLACE '
	END

	IF((@dateto IS NULL AND LEN(@dateto) = 0) AND (@datefrom IS NOT NULL AND LEN(@datefrom) > 0))
	BEGIN
		SET @exec_sql += CASE 
				WHEN (@searchterm IS NOT NULL AND LEN(@searchterm) > 0) THEN 'INTERSECT' 
				WHEN (@casenumber IS NOT NULL AND LEN(@casenumber) > 0) THEN 'INTERSECT'  
				WHEN (@casetitle IS NOT NULL AND LEN(@casetitle) > 0) THEN 'INTERSECT'  
				WHEN (@crimecategory IS NOT NULL AND @crimecategory <> 0) THEN 'INTERSECT'  
				WHEN ((@verdictlevel IS NOT NULL AND LEN(@verdictlevel) > 0) AND (@verdictleveltitle IS NOT NULL AND LEN(@verdictleveltitle) > 0)) THEN 'INTERSECT' 
				WHEN (@placeofcommittment IS NOT NULL AND LEN(@placeofcommittment) > 0) THEN 'INTERSECT'
				ELSE 'UNION' 
				END

			SET @exec_sql += '
		
			SELECT 
			Title, 
			Subtitle, 
			Category, 
			Url
			FROM (

			SELECT 
				mr.CaseTitle AS Title,
				m.CaseNumber AS Subtitle,
				1 AS Category,
				N''/Court/Case/'' + CAST(m.Id AS nvarchar(max)) AS Url
			FROM Matrix m
			INNER JOIN 
				MatrixResources mr on m.Id = mr.MatrixId
			INNER JOIN
				Verdict v on m.Id = v.MatrixId
			WHERE 
				mr.LT = N''' + @tag + ''' AND
				v.Date >= N''' + @datefrom + '''
		
			UNION 
			SELECT 
				m.CaseNumber + N'' '' + mr.CaseTitle AS Title,
				Cast(v.VerdictLevelId AS nvarchar(max)) AS Subtitle,
				2 AS Category,
				N''/Court/Open/'' + CAST(v.Id AS nvarchar(max)) + N''?n='' + v.FileName AS Url
			FROM Matrix m
			INNER JOIN 
				MatrixResources mr on m.Id = mr.MatrixId
			INNER JOIN 
				CountOfIndictment coi on m.Id = coi.MatrixId
			INNER JOIN
				Verdict v on m.Id = v.MatrixId
			WHERE 
				v.LT = N''' + @tag + ''' AND coi.LT = N''' + @tag + ''' AND mr.LT = N''' + @tag + ''' AND
				v.Date >= N''' + @datefrom + '''
		
			UNION
			SELECT
				pr.Title AS Title,
				pr.Subtitle AS Subtitle,
				3 AS Category,
				N''/Post/Read/'' + p.Link AS Url
			FROM Post p
			INNER JOIN 
				PostResource pr on p.Id = pr.PostId
			INNER JOIN 
				Matrix m on m.Id = p.MatrixId
			INNER JOIN
				MatrixResources mr on m.Id = mr.MatrixId
			INNER JOIN
				Verdict v on m.Id = v.MatrixId
			WHERE
				v.LT = N''' + @tag + ''' AND mr.LT = N''' + @tag + ''' AND pr.LT = N''' + @tag + ''' AND
				p.Date >= N''' + @datefrom + '''
			) AS DATEFROM '
		END

		IF((@dateto IS NOT NULL AND LEN(@dateto) > 0) AND (@datefrom IS NULL AND LEN(@datefrom) = 0))
		BEGIN
		SET @exec_sql += CASE 
				WHEN (@searchterm IS NOT NULL AND LEN(@searchterm) > 0) THEN 'INTERSECT' 
				WHEN (@casenumber IS NOT NULL AND LEN(@casenumber) > 0) THEN 'INTERSECT'  
				WHEN (@casetitle IS NOT NULL AND LEN(@casetitle) > 0) THEN 'INTERSECT'  
				WHEN (@crimecategory IS NOT NULL AND @crimecategory <> 0) THEN 'INTERSECT'  
				WHEN ((@verdictlevel IS NOT NULL AND LEN(@verdictlevel) > 0) AND (@verdictleveltitle IS NOT NULL AND LEN(@verdictleveltitle) > 0)) THEN 'INTERSECT' 
				WHEN (@placeofcommittment IS NOT NULL AND LEN(@placeofcommittment) > 0) THEN 'INTERSECT'
				ELSE 'UNION' 
				END

			SET @exec_sql += '
		
			SELECT 
			Title, 
			Subtitle, 
			Category, 
			Url
			FROM (

			SELECT 
				mr.CaseTitle AS Title,
				m.CaseNumber AS Subtitle,
				1 AS Category,
				N''/Court/Case/'' + CAST(m.Id AS nvarchar(max)) AS Url
			FROM Matrix m
			INNER JOIN 
				MatrixResources mr on m.Id = mr.MatrixId
			INNER JOIN
				Verdict v on m.Id = v.MatrixId
			WHERE 
				mr.LT = N''' + @tag + ''' AND
				v.Date <= N''' + @dateto + '''
		
			UNION 
			SELECT 
				m.CaseNumber + N'' '' + mr.CaseTitle AS Title,
				Cast(v.VerdictLevelId AS nvarchar(max)) AS Subtitle,
				2 AS Category,
				N''/Court/Open/'' + CAST(v.Id AS nvarchar(max)) + N''?n='' + v.FileName AS Url
			FROM Matrix m
			INNER JOIN 
				MatrixResources mr on m.Id = mr.MatrixId
			INNER JOIN 
				CountOfIndictment coi on m.Id = coi.MatrixId
			INNER JOIN
				Verdict v on m.Id = v.MatrixId
			WHERE 
				v.LT = N''' + @tag + ''' AND coi.LT = N''' + @tag + ''' AND mr.LT = N''' + @tag + ''' AND
				v.Date <= N''' + @dateto + '''
		
			UNION
			SELECT
				pr.Title AS Title,
				pr.Subtitle AS Subtitle,
				3 AS Category,
				N''/Post/Read/'' + p.Link AS Url
			FROM Post p
			INNER JOIN 
				PostResource pr on p.Id = pr.PostId
			INNER JOIN 
				Matrix m on m.Id = p.MatrixId
			INNER JOIN
				MatrixResources mr on m.Id = mr.MatrixId
			INNER JOIN
				Verdict v on m.Id = v.MatrixId
			WHERE
				v.LT = N''' + @tag + ''' AND mr.LT = N''' + @tag + ''' AND pr.LT = N''' + @tag + ''' AND
				p.Date <= N''' + @dateto + '''
			) AS DATETO '
		END


	IF((@dateto IS NOT NULL AND LEN(@dateto) > 0) AND (@datefrom IS NOT NULL AND LEN(@datefrom) > 0))
	BEGIN
		SET @exec_sql += CASE 
				WHEN (@searchterm IS NOT NULL AND LEN(@searchterm) > 0) THEN 'INTERSECT' 
				WHEN (@casenumber IS NOT NULL AND LEN(@casenumber) > 0) THEN 'INTERSECT'  
				WHEN (@casetitle IS NOT NULL AND LEN(@casetitle) > 0) THEN 'INTERSECT'  
				WHEN (@crimecategory IS NOT NULL AND @crimecategory <> 0) THEN 'INTERSECT'  
				WHEN ((@verdictlevel IS NOT NULL AND LEN(@verdictlevel) > 0) AND (@verdictleveltitle IS NOT NULL AND LEN(@verdictleveltitle) > 0)) THEN 'INTERSECT' 
				WHEN (@placeofcommittment IS NOT NULL AND LEN(@placeofcommittment) > 0) THEN 'INTERSECT'
				ELSE 'UNION' 
				END

		SET @exec_sql += '
		
			SELECT 
			Title, 
			Subtitle, 
			Category, 
			Url
			FROM (

			SELECT 
				mr.CaseTitle AS Title,
				m.CaseNumber AS Subtitle,
				1 AS Category,
				N''/Court/Case/'' + CAST(m.Id AS nvarchar(max)) AS Url
			FROM Matrix m
			INNER JOIN 
				MatrixResources mr on m.Id = mr.MatrixId
			INNER JOIN
				Verdict v on m.Id = v.MatrixId
			WHERE 
				v.LT = N''' + @tag + ''' AND mr.LT = N''' + @tag + ''' AND
				(v.Date >= N''' + @datefrom + ''' AND
				v.Date <= N''' + @dateto + ''')
		
			UNION 
			SELECT 
				m.CaseNumber + N'' '' + mr.CaseTitle AS Title,
				Cast(v.VerdictLevelId AS nvarchar(max)) AS Subtitle,
				2 AS Category,
				N''/Court/Open/'' + CAST(v.Id AS nvarchar(max)) + N''?n='' + v.FileName AS Url
			FROM Matrix m
			INNER JOIN 
				MatrixResources mr on m.Id = mr.MatrixId
			INNER JOIN
				Verdict v on m.Id = v.MatrixId
			WHERE 
				v.LT = N''' + @tag + ''' AND mr.LT = N''' + @tag + ''' AND
				(v.Date >= N''' + @datefrom + ''' AND
				v.Date <= N''' + @dateto + ''')
		
			UNION
			SELECT
				pr.Title AS Title,
				pr.Subtitle AS Subtitle,
				3 AS Category,
				N''/Post/Read/'' + p.Link AS Url
			FROM Post p
			INNER JOIN 
				PostResource pr on p.Id = pr.PostId
			INNER JOIN 
				Matrix m on m.Id = p.MatrixId
			INNER JOIN
				MatrixResources mr on m.Id = mr.MatrixId
			INNER JOIN
				Verdict v on m.Id = v.MatrixId
			WHERE
				v.LT = N''' + @tag + ''' AND mr.LT = N''' + @tag + ''' AND pr.LT = N''' + @tag + ''' AND
				((p.Date >= N''' + @datefrom + ''' AND
				p.Date <= N''' + @dateto + ''') OR
				(v.Date >= N''' + @datefrom + ''' AND
				v.Date <= N''' + @dateto + '''))
			) AS DATERANGE '
	END

	PRINT(@exec_sql)
	EXEC(@exec_sql)
END