﻿USE [AIADatabaseV5]
GO
/****** Object:  StoredProcedure [dbo].[GetSearchTerms]    Script Date: 2/1/2018 12:15:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- Create date: 19/5/2009
-- Description:	Fetch Editions for building level accounts for given Account Number and SiteId.
-- =============================================

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetSearchTerms]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetSearchTerms]
GO

CREATE PROCEDURE [dbo].[GetSearchTerms]
	
AS
BEGIN
	
	IF 1=0 BEGIN
		SET FMTONLY OFF
	END
	
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    Select * from (
	Select CASE WHEN trm.SearchText is not null THEN trm.SearchText ELSE phr.SearchText END as SearchText, 
		isnull(trm.TID,0) as VocabTermId, isnull(phr.PID,0) as PhraseId,
		(CASE WHEN trm.LID IS NOT NULL THEN trm.LID ELSE phr.LID END) as LexiconId 
	From 
			(Select vt.TermText + 'T' as SearchText,max(vtn.TermNumber) as TID, vt.VocabLexiconId as LID
			From dbo.VocabTerms vt 
				INNER JOIN dbo.VocabTermNumbers vtn on vt.TermNumber=vtn.TermNumber
				INNER JOIN dbo.VocabLexicon vl on vt.VocabLexiconId = vl.Id
			Where vtn.IsCurrent='Y' and vl.IsCurrent = 'Y'
			Group by vt.TermText + 'T', vt.VocabLexiconId
			) trm
		FULL OUTER JOIN
			(Select sp.Phrase + 'T' as SearchText, max(sp.Id) as PID, 1 as LID
			From ContentDescriptor cd 
				INNER JOIN ContentDescriptorToPhraseMap cdpm on cd.Id=cdpm.ContentDescriptorId 
				INNER JOIN dbo.SearchPhrase sp on cdpm.SearchPhraseId=sp.Id 
				Group by sp.Phrase + 'T'
			Union
			Select sp.Phrase + 'T' as SearchText, max(sp.Id) as PID, 2 as LID
			From ContentDescriptor cd 
				INNER JOIN ContentDescriptorToPhraseMap cdpm on cd.Id=cdpm.ContentDescriptorId 
				INNER JOIN dbo.SearchPhrase sp on cdpm.SearchPhraseId=sp.Id 
				Group by sp.Phrase + 'T') phr
		ON trm.SearchText = phr.SearchText and trm.LID = phr.LID) ads
	Order by LexiconId,SearchText

END

GO
