/* 
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
CREATEC:\Program Files\Microsoft SQL Server 28th June 2005
*/
CREATE  PROCEDURE [dbo].[usp_SEARCHDocuments_Paging1]
(
   @clientID INT 
,  @accountNumber VARCHAR(15) = NULL
,  @invoiceNumber VARCHAR(20) = NULL
,  @documentDate SMALLDATETIME = NULL
,  @handsetNumber VARCHAR(12) = NULL
,  @recipientName VARCHAR(45) = NULL
,  @limit SMALLINT = 20
,  @pageNumber SMALLINT = 1
)
AS
SET NOCOUNT ON
SET ROWCOUNT 0
DECLARE @lastID INT

SET @lastID = (@limit * (@pageNumber-1))
SET @limit = (@limit * @pageNumber)

SET @handsetNumber = REPLACE(@handsetNumber, ' ', '')
/*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)
		
DECLARE @docSetLimit TABLE
(
  TableID INT IDENTITY PRIMARY KEY CLUSTERED
, DocSetID BIGINT 
, DocumentDate SMALLDATETIME
, AccountNumber VARCHAR(15)
)

SET ROWCOUNT @limit
INSERT INTO @docSetLimit (DocSetID, DocumentDate, AccountNumber)
SELECT DS.DocumentSetID, D.DocumentDate, A.AccountNumber
FROM Client C 
	INNER JOIN Account A 
			 	ON (
					(C.ClientID = A.ClientID) 
					AND (A.ClientID = @clientID)
					AND (AccountNumber = @accountNumber OR @accountNumber IS NULL)
				    )
	INNER JOIN DocumentRecipient DR 
				ON (
					(A.AccountID = DR.AccountID) 
			     		AND (@recipientName IS NULL OR DocumentRecipientName LIKE ('%' + @recipientName + '%'))
				   )
	INNER JOIN Document D 
				ON (
				 	(DR.DocumentRecipientID = D.DocumentRecipientID) 
				 	AND (DocumentInvoiceNumber = @invoiceNumber OR @invoiceNumber IS NULL)
				 	AND (DocumentDate = @documentDate OR @documentDate IS NULL)
				   )
	INNER JOIN DocumentSet DS ON DS.DocumentSetID = D.DocumentSetID
	INNER JOIN [File] F ON F.FileID = DS.FileID
	INNER JOIN Status S ON (S.StatusID = F.StatusID AND StatusName = 'Accessible')

	GROUP BY DS.DocumentSetID, DocumentDate, AccountNumber

	ORDER BY  D.DocumentDate desc
		, A.AccountNumber
		, DS.DocumentSetID

SET ROWCOUNT 0

SELECT    DocumentRecipientName
	, DocumentRecipientPostcode
	, DocumentInvoiceNumber
	, T1.DocumentDate
	, DocumentPageCount	
	, D.DocumentID
	, HandsetNumber
	, T1.AccountNumber
	, DocumentSequenceNumber
	, DocumentSetID
	FROM Client C INNER JOIN Account A ON C.ClientID = A.ClientID
	INNER JOIN DocumentRecipient DR ON A.AccountID = DR.AccountID
	INNER 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
	WHERE (TableID > @lastID)

	ORDER BY  T1.AccountNumber
		, DocumentInvoiceNumber
		, DocumentSetID
		, DocumentSequenceNumber
		, T1.DocumentDate

First call to the stored procedure (2 results per page...for simplicity)
@limit = 2 --passed in by application
@pageNumber = 1
@limit = @limit * @pageNumber --which equals 2

@lastID = (@limit * (@pagenumber-1)) --which equals(2 * (1-1))= 0
SET ROWCOUNT @limit --which equals 2

--Results of @docSetLimit 

TableID	DocSetID DocumentDate  	AccountNumber
1	1243	1/11/05	         12345
2	34	4/11/05	         23456

2nd query to select all the documents relating to the document sets in @docSetLimit 
SELECT ....... 
INNER JOIN @docSetLimit as T1 ON .....
WHERE T1.TableID > @lastID --where TableID > 0

Results sent to application:
DocumentID	DocSetID .............
1	        1243	 .............
2	        1243	 ............. 
3	        1243	 .............    
4	        34  	 ............. 	
5	        34  	 ............. 

Second call to the stored procedure (2 results per page...for simplicity)
@pageNumber = 2
@limit = @limit * @pageNumber --which equal 2

@lastID = (@limit * (@pagenumber-1)) --which equals (2 * (2-1))= 2

Results of @docSetLimit 
[code]
TableID	DocSetID DocumentDate  	AccountNumber
1	1243	1/11/05	         12345
2	34	4/11/05	         23456
3	256	14/11/05	 123
4	790	1/12/05	         12345

2nd query  
SELECT ....... 
INNER JOIN @docSetLimit ON .....
WHERE TableID > @lastID --where TableID > 2

Results sent to application:
DocumentID	DocSetID .............
6	        256	 .............
7	        256	 ............. 
8	        256	 .............    
9	        790  	 ............. 	
10	        790  	 ............. 
11	        790  	 ............. 

and so on so forth