This script is used to repair quick search not populating entries on an active Manitou System. Run Query against Manitou database.
OK to run while Manitou is running.
USE MANITOU;
GO
IF OBJECT_ID(N'QUICKSEARCH',N'TF') IS NOT NULL DROP FUNCTION QUICKSEARCH;
GO
CREATE FUNCTION dbo.QUICKSEARCH (@SEARCH NVARCHAR(50),
@CONTTYPE INT,
@SERIALNO INT,
@INLIST NVARCHAR(MAX),
@PAGE INT,
@ROWS INT)
RETURNS @SEARCHRESULTS TABLE (SEARCHKEY INT NOT NULL,
SERIALNO INT NOT NULL,
RESULT1 NVARCHAR(50) NULL,
RESULT2 NVARCHAR(50) NULL,
TOTAL INT NOT NULL)
WITH ENCRYPTION AS
BEGIN
DECLARE @CNT AS INT;
DECLARE @ALL TABLE (SEARCHKEY INT NOT NULL,
SERIALNO INT NOT NULL,
VAL1 NVARCHAR(50) NULL,
VAL2 NVARCHAR(50) NULL);
SET @SEARCH=N'%'+@SEARCH+N'%';
INSERT INTO @ALL (SEARCHKEY,SERIALNO)
SELECT 0x1,SERIALNO
FROM CONTACT
WHERE CONTTYPE=1 AND FULLNAME LIKE @SEARCH;
INSERT INTO @ALL (SEARCHKEY,SERIALNO)
SELECT 0x2,SERIALNO
FROM [ADDRESS]
WHERE CONTTYPE=1 AND ADDR1 LIKE @SEARCH;
INSERT INTO @ALL (SEARCHKEY,SERIALNO,VAL2)
SELECT 0x4,SERIALNO,PASSWD
FROM CUSTPW
WHERE PASSWD LIKE @SEARCH;
INSERT INTO @ALL (SEARCHKEY,SERIALNO,VAL2)
SELECT 0x8,SERIALNO,CONTPOINT
FROM CONTPOINT
WHERE CONTTYPE=1 AND FILEAS LIKE @SEARCH;
INSERT INTO @ALL (SEARCHKEY,SERIALNO,VAL1,VAL2)
SELECT 0x10,L.SERIALNO,ISNULL(T.FULLNAME,N''),L.PASSWD
FROM CONTLIST L
JOIN CONTACT T ON L.REFCONTTYPE=T.CONTTYPE AND L.REFSERIALNO=T.SERIALNO
WHERE L.CONTTYPE=1 AND L.REFCONTTYPE=100 AND PASSWD LIKE @SEARCH;
INSERT INTO @ALL (SEARCHKEY,SERIALNO,VAL1,VAL2)
SELECT 0x20,L.SERIALNO,ISNULL(T.FULLNAME,N''),P.CONTPOINT
FROM CONTLIST L
JOIN CONTPOINT P ON L.REFCONTTYPE=100 AND L.REFCONTTYPE=P.CONTTYPE AND L.REFSERIALNO=P.SERIALNO
JOIN CONTACT T ON L.REFCONTTYPE=T.CONTTYPE AND L.REFSERIALNO=T.SERIALNO
WHERE L.CONTTYPE = 1 AND P.FILEAS LIKE @SEARCH;
INSERT INTO @ALL (SEARCHKEY,SERIALNO,VAL1)
SELECT 0x40,L.SERIALNO,T.FULLNAME
FROM CONTLIST L
JOIN CONTACT T ON L.REFCONTTYPE=100 AND L.REFCONTTYPE=T.CONTTYPE AND L.REFSERIALNO=T.SERIALNO
WHERE L.CONTTYPE = 1 AND T.FULLNAME LIKE @SEARCH;
INSERT INTO @ALL (SEARCHKEY,SERIALNO)
SELECT 0x80,SERIALNO
FROM CUSTOMER
WHERE CONTID LIKE @SEARCH;
IF @CONTTYPE = 1
DELETE FROM @ALL WHERE [@ALL].SERIALNO <> @SERIALNO;
ELSE
BEGIN
IF @CONTTYPE = 2
BEGIN
IF LEN(ISNULL(@INLIST, N'')) = 0
DELETE @ALL FROM @ALL JOIN CUSTOMER ON CUSTOMER.SERIALNO = [@ALL].SERIALNO WHERE CUSTOMER.INSTNO <> @SERIALNO;
ELSE
BEGIN
DECLARE @IN TABLE (SERIALNO INT NOT NULL);
DECLARE @I INT;
DECLARE @NO INT;
AGAIN:
SET @I = CHARINDEX(N',', @INLIST);
IF @I > 0
BEGIN
SET @NO = CAST(LEFT(@INLIST, @I-1) AS INT);
SET @INLIST = RIGHT(@INLIST, LEN(@INLIST)-@I);
INSERT INTO @IN VALUES (@NO);
GOTO AGAIN;
END;
SET @NO = CAST(@INLIST AS INT);
INSERT INTO @IN VALUES (@NO);
DELETE @ALL FROM @ALL JOIN CUSTOMER ON CUSTOMER.SERIALNO = [@ALL].SERIALNO WHERE CUSTOMER.INSTNO NOT IN ( SELECT [@IN].SERIALNO FROM @IN );
END;
END;
ELSE
BEGIN
IF @CONTTYPE = 3
DELETE @ALL FROM @ALL JOIN CUSTOMER ON CUSTOMER.SERIALNO = [@ALL].SERIALNO WHERE CUSTOMER.BRANCHNO <> @SERIALNO;
ELSE
BEGIN
IF @CONTTYPE = 4
DELETE FROM @ALL WHERE NOT EXISTS (SELECT * FROM CONTLIST WHERE CONTLIST.CONTTYPE = 1 AND CONTLIST.SERIALNO = [@ALL].SERIALNO);
END;
END;
END;
SET @CNT = (SELECT COUNT (*) FROM @ALL);
INSERT INTO @SEARCHRESULTS (SEARCHKEY,SERIALNO,RESULT1,RESULT2,TOTAL)
SELECT SEARCHKEY,SERIALNO,VAL1,VAL2,@CNT
FROM (SELECT ROW_NUMBER () OVER (ORDER BY SEARCHKEY, VAL1, VAL2) AS ROWNUM,* FROM @ALL) AS O
WHERE ROWNUM BETWEEN (@PAGE*@ROWS)+1 AND (@PAGE*@ROWS)+@ROWS;
RETURN;
END;
GO
IF EXISTS (SELECT OPTINT FROM MOPTIONS WHERE OPTIONNO = 121)
BEGIN
UPDATE MOPTIONS SET LASTUPD = CURRENT_TIMESTAMP, OPTIONTXT = 'QUICKSEARCHVER', DATATYPE = 1, OPTINT = 2 WHERE OPTIONNO = 121;
END;
ELSE
BEGIN
INSERT INTO MOPTIONS (LASTUPD, CREDATE, OPTIONNO, OPTIONTXT, DATATYPE, OPTBOOL, OPTINT, OPTCHAR, OPTDATE)
VALUES(CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 121, 'QUICKSEARCHVER', 1, 0, 2, NULL, NULL);
END;
GO
