USE [AIADatabaseV5]
GO
/****** Object:  StoredProcedure [dbo].[GetEncyclopediaSearch]    Script Date: 2/1/2018 12:15:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================================
-- Author:		Magic
-- Create date: 28-May-09
-- Description:	Get the result based on the keyword search
-- =============================================================

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

CREATE PROCEDURE [dbo].[GetEncyclopediaSearch]
	-- Add the parameters for the stored procedure here
	@iLexiconId int, @sSearchKeyword nvarchar(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT COUNT(DISTINCT EncyclopediaWeightage.Keyword) AS hitcount, 
    SUM(EncyclopediaWeightage.Relevancy) AS score, 
    EncyclopediaMetadata.Title, EncyclopediaMetadata.ProjectTypeId, EncyclopediaMetadata.GenContentId, 
    EncyclopediaMetadata.SubContent
	FROM EncyclopediaMetadata
	INNER JOIN EncyclopediaWeightage 
	ON EncyclopediaMetadata.Id = EncyclopediaWeightage.MetadataId
	WHERE EncyclopediaMetadata.LexiconId = @iLexiconId
	AND EncyclopediaWeightage.Keyword = @sSearchKeyword
	GROUP BY EncyclopediaWeightage.Keyword, EncyclopediaWeightage.Relevancy, EncyclopediaMetadata.Title, 
	EncyclopediaMetadata.ProjectTypeId, EncyclopediaMetadata.GenContentId,EncyclopediaMetadata.SubContent
	UNION
	SELECT 1 AS hitcount, 1 AS score, EncyclopediaMetadata.Title, EncyclopediaMetadata.ProjectTypeId, 
	EncyclopediaMetadata.GenContentId, EncyclopediaMetadata.SubContent 
    FROM EncyclopediaMetadata WHERE (' ' + EncyclopediaMetadata.Title+ ' ') LIKE '%'+ @sSearchKeyword +'%'
	AND EncyclopediaMetadata.Id NOT IN 
	(SELECT InDepthWeightage.MetadataId FROM InDepthWeightage WHERE InDepthWeightage.Keyword = @sSearchKeyword)
    ORDER BY hitcount DESC, score DESC
	
	
END

GO