USE [AIADatabaseV5]
GO
/****** Object:  StoredProcedure [dbo].[GetSearchDetails]    Script Date: 02/06/2018 10:49:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- Create date: 21/5/2009
-- Description:	Fetch Content Descriptor Ids for Different Resources.
-- =============================================
CREATE PROCEDURE [dbo].[GetSearchDetails]
	
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 distinct TermNumber, ContentTypeId,Id 
	From (
		Select ds.TermNumber, cd.ContentTypeId, cd.Id
		From DissectibleStructures ds 
				INNER JOIN DissectibleStructureGroup  dsg on ds.Dissectiblestructuregroupid = dsg.id
				INNER JOIN DissectibleBodyView dbv on dsg.Dissectiblebodyviewid = dbv.id
				INNER JOIN DissectibleContent dc on dsg.id = dc.Dissectiblestructuregroupid
				INNER JOIN ContentDescriptor cd on dc.contentdescriptorid = cd.id
		Where cd.IsActive = 'Y'and ds.TermNumber is not null
		Union
		-- Query which will use to get Atlas anatomy data.
		Select distinct ap.VocabTermNumbers as TermNumber,cd.ContentTypeId,ic.ContentdescriptorId
		From ImageContent ic 
			INNER JOIN AnnotationPin ap on ic.Id = ap.ImageContentId
			INNER JOIN ContentDescriptor cd on cd.Id = ic.ContentDescriptorId
		Where ap.VocabTermNumbers is not null
		Union
		----Query for CI by term ID
		Select ca.TermNumber,cd.ContentTypeId,cd.Id
		From ContentAttribute ca
			INNER JOIN ContentDescriptorToAttributeMap cdm on ca.Id = cdm.ContentAttributeId
			INNER JOIN ContentDescriptor cd on cdm.ContentDescriptorId = cd.Id
		Where cd.IsActive='Y' and ca.TermNumber is not null
		Union
		Select tdt.TermNumberId as TermNumber,cd.ContentTypeId,cd.Id 
		From ContentDescriptor cd 
			INNER JOIN ImageContent ic on cd.Id = ic.ContentDescriptorId
			INNER JOIN ThreeDTermsToHighLight tdt on ic.Id = tdt.IcId
		Where cd.IsActive='Y'
		Union
		Select distinct cdpm.SearchPhraseId as TermNumber, cd.ContentTypeId, cd.Id  from ContentDescriptor cd 
			INNER JOIN ContentDescriptorToPhraseMap cdpm on cd.id =  cdpm.ContentDescriptorId
			Where cd.IsActive='Y' 
		) a
	Order by TermNumber, ContentTypeId, Id
END
GO