/* 
PURPOSE: 	Procedure to search for document sets across clients
FUNCTIONALITY: The database is searched and returns all the documents that match 
		any of the variables provided. This can either be a single document
		set or multiple, depending on the level of the search.
NOTE: FOR INTERNAL USE ONLY
CREATOR: Rebecca Starr
CREATED: 28th June 2005
*/
CREATE      PROCEDURE [dbo].[usp_SEARCHDocuments]
(
   @clientID INT = NULL
,  @accountNumber VARCHAR(15) = NULL
,  @invoiceNumber VARCHAR(20) = NULL
,  @documentDate SMALLDATETIME = NULL
,  @handsetNumber VARCHAR(12) = NULL
,  @accountName VARCHAR(35) = NULL --HAS TO BE REMOVED
,  @recipientName VARCHAR(45) = NULL
,  @limit SMALLINT = NULL
)
AS
SET NOCOUNT ON
--Obtains the accountID from the account table based on ClientID and AccountNumber
DECLARE @accountID INT
SET @accountID = (SELECT AccountID FROM Account 
			WHERE AccountNumber = @accountNumber
			AND ClientID = @clientID
			)
/*Selects the information based on the parameters passed. If no variables are passed, 
then all the data in the database is returned.*/
--An invoice number, handset number, etc can identify a document set or a single document
--In order to return all the documents of a document set, a nested select is performed
--1) First Part: Applies a limit of how many document sets are to be returned using ROWCOUNT
--2) Second Part: Creates a table variable and all the document sets that match the search
--   criteria are inserted into the variable @docSetLimit
--3) Third Part: Selects all the documents that belong to the document sets contained in the
--   table variable (inner join @docSetLimit)
IF @limit IS NULL SET @limit = 0
SET ROWCOUNT @limit
DECLARE @docSetLimit TABLE
(
DocSetID BIGINT
)
INSERT INTO @docSetLimit (DocSetID)
SELECT DS.DocumentSetID
FROM Client C 
	LEFT OUTER JOIN Account A ON C.ClientID = A.ClientID
	LEFT OUTER JOIN DocumentRecipient DR ON A.AccountID = DR.AccountID
	LEFT OUTER JOIN Document D ON DR.DocumentRecipientID = D.DocumentRecipientID
	LEFT OUTER JOIN Handset H ON D.DocumentID = H.DocumentID
	INNER JOIN DocumentSet DS ON DS.DocumentSetID = D.DocumentSetID
	INNER JOIN [File] F ON F.FileID = DS.FileID
	WHERE (@clientID IS NULL OR A.ClientID = @clientID)
	AND (@accountNumber IS NULL OR AccountNumber = @accountNumber)
	AND (@invoiceNumber IS NULL OR DocumentInvoiceNumber = @invoiceNumber)
	AND (@documentDate IS NULL OR CONVERT(VARCHAR,DocumentDate,106) = @documentDate)
	AND (@handsetNumber IS NULL OR HandsetNumber = REPLACE(@handsetNumber, ' ', ''))
	AND (@accountName IS NULL OR DocumentRecipientName LIKE ('%' + @accountName + '%'))--THIS IS TO BE REMOVED
	AND (@recipientName IS NULL OR DocumentRecipientName LIKE ('%' + @recipientName + '%'))
	AND (F.StatusID = (SELECT StatusID FROM Status WHERE StatusName = 'Accessible'))
	GROUP BY DS.DocumentSetID
SET ROWCOUNT 0
SELECT    DocumentRecipientName
	, DocumentRecipientPostcode
	, DocumentInvoiceNumber
	, DocumentDate
	, DocumentPageCount	
	, D.DocumentID
	, HandsetNumber
	, AccountNumber
	, DocumentSequenceNumber
	, DocumentSetID
	FROM Client C LEFT OUTER JOIN Account A ON C.ClientID = A.ClientID
		LEFT OUTER JOIN DocumentRecipient DR ON A.AccountID = DR.AccountID
			LEFT OUTER JOIN Document D ON DR.DocumentRecipientID = D.DocumentRecipientID
				LEFT OUTER JOIN Handset H ON D.DocumentID = H.DocumentID
					INNER JOIN @docSetLimit T1 ON T1.DocSetID = D.DocumentSetID
	ORDER BY AccountNumber, DocumentInvoiceNumber, DocumentSetID, DocumentSequenceNumber, DocumentDate


GO

IF @clientID IS NOT NULL
BEGIN
    INSERT INTO @docSetLimit (DocSetID)
    SELECT DS.DocumentSetID
    FROM Client C 
    WHERE ClientID = @clientID
END
ELSE
IF @invoiceNumber IS NOT NULL
BEGIN
    INSERT INTO @docSetLimit (DocSetID)
    SELECT DS.DocumentSetID
    FROM Client C 
	JOIN Account A ON C.ClientID = A.ClientID
	JOIN DocumentRecipient DR ON A.AccountID = DR.AccountID
	JOIN Document D ON DR.DocumentRecipientID = D.DocumentRecipientID
    WHERE DocumentInvoiceNumber = @invoiceNumber
END
ELSE
...

...
LEFT OUTER JOIN Handset H ON D.DocumentID = H.DocumentID
...
WHERE ...
	AND (@handsetNumber IS NULL OR HandsetNumber = REPLACE(@handsetNumber, ' ', ''))

...
LEFT OUTER JOIN Handset H 
	 ON D.DocumentID = H.DocumentID
	AND (@handsetNumber IS NULL OR HandsetNumber = REPLACE(@handsetNumber, ' ', ''))
...
WHERE ...