-- Delete duplicate rows
DELETE FROM
@TT_Matches
WHERE
NOT EXISTS
(
SELECT 
TT_SR.PageID FROM @TT_SearchResults AS TT_SR WHERE TT_SR.PageID = @TT_Matches.PageID
)

CREATE PROCEDURE sppb_Search_Public_WithSpider

@SearchKeywords	VARCHAR(1000),
@SearchTerm		VARCHAR(100),
@PageSize		INT,
@CurrentPage		INT

/*********************************************************************************
10 Feburary 2005 - Created - Peter Bridger
Do a search of the website, public version

Search Page table and PCL against pages.
Only return pages that are 
* Live
* Searchable
* Directly accessible

***********************************************************************************
16 August 2005 - Modified - Peter Bridger 
Procedure upgraded to also search 
* Information Centre
* Newsroom 
* Documents
* Local pages

PLUS paging has been added

DataTypeID >
1: Gandalf page
**** News ****
2: Newsroom
3: Newsroom (Local pages)
**** Local structure system ****
4: District
5: Sector
6: Beat
*** Documents ***
7: Document

***********************************************************************************
15 February 2006 - Modified - Peter Bridger 

*** Persons database ***
8: Person

Added the ability to search the persons database. Primarly to make
it easier for the public to find a police officer, their beat officer for example.

***********************************************************************************
8 March 2006 - Modified - Peter Bridger

*** Information Centre  ***
9: Info Centre

We've *finally* got the Force-Internet middleware database server in
place and working. So now a syncronised copy of the Information
Centre database is held on HQIDB01, meaning it can now be included
in the website search.

***********************************************************************************
5 September 2006 - Modified - Peter Bridger

Added a very basic logging system, so that we have a record of 
what people search for and how many results get returned

***********************************************************************************
10: Search Spider (Page)

Wrote a basic search spider, this is also searched and the results
are combined with the database searches.
*********************************************************************************/

AS

SET NOCOUNT ON

-- Create a less strict version of the search term
DECLARE
@SearchTerm_LessStrict	VARCHAR(100)

SELECT @SearchTerm_LessStrict = REPLACE( @SearchTerm, ' AND ', ' OR ' )

-- Create table to hold results
DECLARE
@TT_SearchResults  TABLE
(
SearchID		INT IDENTITY(1,1),
Rank			INT,
PageFileName		VARCHAR(100),
DataID			INT,
DataTypeID		TINYINT,
PageTitle		VARCHAR(150),
Static_Lineage		VARCHAR(1000),
DescriptionPublic	VARCHAR(1000),
Friendly_Lineage	VARCHAR(1000)
)

-- Search Page table, against Title, PublicDescription and Keywords
INSERT @TT_SearchResults
(
Rank,
PageFileName,
DataID,
DataTypeID,
PageTitle,
Static_Lineage,
DescriptionPublic
)
SELECT
dbo.GandalfRank( CT.Rank, P.PageID, S.IndexPageID ),
P.PageFileName,
P.PageID,
1, -- Gandalf page
P.Title,
S.Static_Lineage,
DescriptionPublic
from 
CONTAINSTABLE( Pages, *, @SearchTerm ) AS CT
JOIN Pages AS P ON
P.PageID = CT.[KEY]
INNER JOIN
Sections AS S ON
S.SectionID = P.SectionID
WHERE
P.IsSearchable = 1 AND
P.IsLive = 1 AND
P.IsDirectlyAccessible = 1 AND
dbo.IsPageLineageLive( P.SectionID ) = 1 -- Check lineage of page is all live
ORDER BY Rank DESC

-- Search against PCL
INSERT @TT_SearchResults
(
Rank,
PageFileName,
DataID,
DataTypeID,
PageTitle,
Static_Lineage,
DescriptionPublic
)
SELECT
CT.Rank,
P.PageFileName,
P.PageID,
1, -- Gandalf page
P.Title,
S.Static_Lineage,
P.DescriptionPublic
FROM
CONTAINSTABLE( Lookup_Categories, *, @SearchTerm ) AS CT
JOIN Lookup_Categories AS LC ON
LC.CategoryID  = CT.[KEY]
INNER JOIN
PageCategorys AS PC ON
PC.CategoryID = LC.CategoryID
INNER JOIN
Pages AS P ON
P.PageID = PC.PageID
INNER JOIN
Sections AS S ON
S.SectionID = P.SectionID
LEFT JOIN -- Where PageID doesn't already exist in table
@TT_SearchResults AS TT_SR ON
TT_SR.DataID = P.PageID AND
TT_SR.DataTypeID = 1
WHERE
P.IsSearchable = 1 AND
P.IsLive = 1 AND
P.IsDirectlyAccessible = 1 AND
dbo.IsPageLineageLive( P.SectionID ) = 1 AND -- Check lineage of page is all live 
TT_SR.DataID IS NULL

-- Add friendly lineage data
UPDATE
@TT_SearchResults
SET
Friendly_Lineage = dbo.GetFriendlyLineage( Static_Lineage )


-- Search newsroom - News stories
INSERT @TT_SearchResults
(
Rank,
PageFileName,
DataID,
DataTypeID,
PageTitle,
Static_Lineage,

DescriptionPublic,
Friendly_Lineage
)
SELECT
CT.Rank,
' ',
NS.NewsStoryID,
dbo.NewsType( NS.IsLocalNews ), 
NS.Title + ' (' + NS.Location + ')',
' ',
CravenSystemV2.dbo.ReturnFirstParagraph( NS.StoryText ),
' '
from 
CONTAINSTABLE( CravenSystemV2.dbo.NewsStories, *, @SearchTerm ) AS CT
JOIN CravenSystemV2.dbo.NewsStories AS NS ON
NS.NewsStoryID = CT.[KEY]
WHERE
NS.IsLive = 1 AND
NS.IsDeleted = 0
ORDER BY Rank DESC

-- Search newsroom - News story updates
INSERT @TT_SearchResults
(
Rank,
PageFileName,
DataID,
DataTypeID,
PageTitle,
Static_Lineage,
DescriptionPublic,
Friendly_Lineage
)
SELECT
CT.Rank,
' ',
NS.NewsStoryID,
dbo.NewsType( NS.IsLocalNews ),
NS.Title + ' (' + NS.Location + ')',
' ',
CravenSystemV2.dbo.ReturnFirstParagraph( NS.StoryText ),
' '
FROM
CONTAINSTABLE( CravenSystemV2.dbo.NewsStoriesUpdates, *, @SearchTerm ) AS CT
JOIN CravenSystemV2.dbo.NewsStoriesUpdates AS NSU ON
NSU.NewsStoryUpdateID = CT.[KEY]
JOIN CravenSystemV2.dbo.NewsStories AS NS ON
NS.NewsStoryID = NSU.NewsStoryID
LEFT JOIN -- Where DataID doesn't already exist in table
@TT_SearchResults AS TT_SR ON
TT_SR.DataID = NS.NewsStoryID AND
( TT_SR.DataTypeID = 2 OR TT_SR.DataTypeID = 3 )
WHERE
NS.IsLive = 1 AND
NS.IsDeleted = 0 AND
NSU.IsLive = 1 AND
NSU.IsDeleted = 0 AND
TT_SR.DataID IS NULL -- Story doesn't already exist
ORDER BY CT.Rank DESC

-- Search Local Structure System - Districts
INSERT @TT_SearchResults
(
Rank,
PageFileName,
DataID,
DataTypeID,
PageTitle,
Static_Lineage,
DescriptionPublic,
Friendly_Lineage
)
SELECT
CT.Rank + 20,
' ',
D.DistrictID,
4, -- LSS, district
D.DistrictName,
' ',
DS.DescriptionShort,
' '
FROM
CONTAINSTABLE( LocalStructureSystem.dbo.Districts, *, @SearchTerm ) AS CT
JOIN LocalStructureSystem.dbo.Districts AS D ON
D.DistrictID = CT.[KEY]
INNER JOIN
LocalPagesV3.dbo.DistrictsSettings AS DS ON
DS.DistrictID = D.DistrictID
ORDER BY CT.Rank DESC

-- Search Local Structure System - Sectors
INSERT @TT_SearchResults
(
Rank,
PageFileName,
DataID,
DataTypeID,
PageTitle,
Static_Lineage,
DescriptionPublic,
Friendly_Lineage
)
SELECT
CT.Rank + 20,
' ',
S.SectorID,
5, -- LSS, sector
S.SectorName,
' ',
SS.DescriptionShort,
' '
FROM
CONTAINSTABLE( LocalStructureSystem.dbo.Sectors, *, @SearchTerm ) AS CT
JOIN LocalStructureSystem.dbo.Sectors AS S ON
S.SectorID = CT.[KEY]
INNER JOIN
LocalPagesV3.dbo.SectorsSettings AS SS ON
SS.SectorID = S.SectorID
ORDER BY CT.Rank DESC

-- Search Local Structure System - Beats
INSERT @TT_SearchResults
(
Rank,
PageFileName,
DataID,
DataTypeID,
PageTitle,
Static_Lineage,
DescriptionPublic,
Friendly_Lineage
)
SELECT
CT.Rank + 20,
' ',
B.BeatID,
6, -- LSS, beat
B.BeatName,
' ',
BS.DescriptionShort,
' '
FROM
CONTAINSTABLE( LocalStructureSystem.dbo.Beats, *, @SearchTerm ) AS CT
JOIN LocalStructureSystem.dbo.Beats AS B ON
B.BeatID = CT.[KEY]
INNER JOIN
LocalPagesV3.dbo.BeatsSettings AS BS ON
BS.BeatID = B.BeatID
ORDER BY CT.Rank DESC

-- Search Documents
INSERT @TT_SearchResults
(
Rank,
PageFileName,
DataID,
DataTypeID,
PageTitle,
Static_Lineage,
DescriptionPublic,
Friendly_Lineage
)
SELECT
CT.Rank,
SS.SectionID,
D.DocumentID,
7, -- Document
D.Title,
' ',
D.Summary,
' '
FROM
CONTAINSTABLE( DocumentsSystemV2.dbo.Documents, *, @SearchTerm ) AS CT
JOIN DocumentsSystemV2.dbo.Documents AS D ON
D.DocumentID = CT.[KEY]
INNER JOIN
DocumentsSystemV2.dbo.SubSections AS SS ON
SS.SubSectionID = D.SubSectionID 

-- Search Persons database
INSERT @TT_SearchResults
(
Rank,
PageFileName,
DataID,
DataTypeID,
PageTitle,
Static_Lineage,
DescriptionPublic,
Friendly_Lineage
)
SELECT
CT.Rank,
' ',
P.PersonID,
8, -- Person
P.FirstName + ' ' + P.LastName,
' ',
' ',
' '
FROM
CONTAINSTABLE( PersonsDatabase.dbo.Persons, *, @SearchTerm_LessStrict )  AS CT
JOIN PersonsDatabase.dbo.Persons AS P ON
P.PersonID= CT.[KEY]
WHERE
P.IsLive = 1


-- Search Information Centre
INSERT @TT_SearchResults
(
Rank,
PageFileName,
DataID,
DataTypeID,
PageTitle,
Static_Lineage,
DescriptionPublic,
Friendly_Lineage
)
SELECT
CT.Rank,
' ',
I.ItemID,
9, -- Info Centre
I.Title,
' ',
dbo.ReturnFirstParagraph( I.PublicText ),
' '
FROM
CONTAINSTABLE( YodaSystemSynced.dbo.Items, *, @SearchTerm )  AS CT
JOIN YodaSystemSynced.dbo.Items AS I ON
I.SyncItemID = CT.[KEY]
WHERE
I.IsLive = 1 AND
I.IsSyncData = 0

-- Search using SearchSpider (Daedalus)
DECLARE
@TT_Matches	TABLE
(
Frequency	SMALLINT,
PageID		INT
)

-- Search for keywords
INSERT INTO
@TT_Matches
SELECT
SUM( PK.Frequency ) 'TotalMatches',
PK.PageID
FROM
SearchSpider.dbo.Keywords AS K
INNER JOIN
SearchSpider.dbo.PageKeywords AS PK ON
PK.KeywordID = K.KeywordID
INNER JOIN
dbo.Split( @SearchKeywords, ',' ) KW ON
K.Keyword = KW.Data
GROUP BY
PK.PageID


-- Delete duplicate rows
DELETE FROM
@TT_SearchResults
WHERE
EXISTS
( SELECT TT_M.PageID FROM @TT_Matches AS TT_M WHERE TTM.PageID = @TT_SearchResults.PageID )


-- Add results to table
INSERT @TT_SearchResults
(
Rank,
PageFileName,
DataID,
DataTypeID,
PageTitle,
Static_Lineage,
DescriptionPublic,
Friendly_Lineage
)
SELECT
TT_M.Frequency * 10,
P.PageFileName,
' ',
10, -- Search spider
P.Title,
S.Static_Lineage,
P.DescriptionPublic,
dbo.GetFriendlyLineage( Static_Lineage )
FROM
@TT_Matches AS TT_M
INNER JOIN
Pages AS P ON
P.PageID = TT_M.PageID AND
P.IsSearchable = 1 -- Only return searchable pages
INNER JOIN 
Sections AS S ON
S.SectionID = P.SectionID

-- Create temp table to order returned results
DECLARE
@TT_RankResults TABLE
(
OrderID		INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
SearchID	INT,
Rank		INT
)

INSERT INTO
@TT_RankResults
( SearchID, Rank )
SELECT
SearchID, Rank
FROM
@TT_SearchResults
ORDER BY
Rank DESC

-- Return results, in ranked order
SELECT DISTINCT
TT_RR.OrderID,
TT_SR.Rank,
TT_SR.PageFileName,
TT_SR.DataID,
TT_SR.DataTypeID,
TT_SR.PageTitle,
TT_SR.Static_Lineage,
TT_SR.Friendly_Lineage,
TT_SR.DescriptionPublic
FROM
@TT_SearchResults AS TT_SR
INNER JOIN 
@TT_RankResults AS TT_RR ON
TT_RR.SearchID = TT_SR.SearchID
WHERE
TT_RR.OrderID > @PageSize * @CurrentPage AND
TT_RR.OrderID <= @PageSize * (@CurrentPage + 1 )
ORDER BY
TT_RR.OrderID ASC

-- Return total paging size
DECLARE 
@PagingSizeTotal 	INT

SELECT 
@PagingSizeTotal = COUNT(*) 
FROM
@TT_SearchResults AS TT_SR

-- 5 September 2006 - Modified - Peter Bridger
-- Quick and dirty logging
INSERT INTO
SearchQuickLog
( SearchTerm, Matches )
VALUES
( @SearchTerm, @PagingSizeTotal )

RETURN @PagingSizeTotal

/* EOF */
GO

DELETE	m
FROM	@TT_Matches m
WHERE	NOT EXISTS	(
				SELECT	sr.PageID
				FROM	@TT_SearchResults sr WHERE sr.PageID = m.PageID
			)