BEGIN

	DECLARE @SQL1 NVARCHAR(MAX)
	DECLARE @SQL_SELECT NVARCHAR(MAX)
	DECLARE @SQL_WHERE NVARCHAR(MAX)
	DECLARE @SQL_PAGING NVARCHAR(MAX)
	DECLARE @Status INT
	DECLARE @OrderBy VARCHAR(255)
	
	DECLARE @AdminUser INT 
	DECLARE @UserCusts INT 
	DECLARE @UserProts INT

	SELECT @AdminUser = CAST(AllCusts AS INT)
	FROM UserSites
	WHERE UserSites.UserID = @WebUserID
		AND UserSites.ApplicationID = @ApplicationID
		AND UserSites.SiteID = @SiteID
		AND UserSites.AllCusts = 1

IF(SELECT COUNT(*) FROM OrderHistory
	INNER JOIN UserCusts
		ON OrderHistory.SiteID = UserCusts.SiteID
		AND OrderHistory.CustNo = UserCusts.CustomerID
	INNER JOIN UserSites
		ON UserSites.UserID = UserCusts.UserID
		AND UserSites.ApplicationID = UserCusts.ApplicationID
		AND UserSites.SiteID = UserCusts.SiteID
	WHERE OrderHistory.Protocol = @Protocol
		AND UserCusts.ApplicationID = @ApplicationID
		AND UserCusts.SiteID = @SiteID
		AND UserCusts.UserID = @WebUserID
		AND UserSites.AllCusts = 0
		AND UserCusts.AllProts = 1 ) > 0
	BEGIN
		SELECT @UserCusts = 1
	END
	ELSE
	BEGIN
		SELECT @UserCusts = 0
	END

IF(SELECT COUNT(*) FROM OrderHistory
	INNER JOIN UserProts 
		ON OrderHistory.SiteID = UserProts.SiteID
		AND OrderHistory.CustNo = UserProts.CustomerID
		AND OrderHistory.Protocol = UserProts.Protocol
	INNER JOIN UserCusts
		ON UserCusts.UserID = UserProts.UserID
		AND UserCusts.ApplicationID = UserProts.ApplicationID
		AND UserCusts.SiteID = UserProts.SiteID
		AND UserCusts.CustomerID = UserProts.CustomerID
	INNER JOIN UserSites
		ON UserSites.UserID = UserCusts.UserID
		AND UserSites.ApplicationID = UserCusts.ApplicationID
		AND UserSites.SiteID = UserCusts.SiteID
	WHERE OrderHistory.Protocol = @Protocol
		AND UserProts.ApplicationID = @ApplicationID
		AND UserProts.SiteID = @SiteID
		AND UserProts.UserID = @WebUserID
		AND UserSites.AllCusts = 0
		AND UserCusts.AllProts = 0) > 0
	BEGIN
		SELECT @UserProts = 1
	END
	ELSE
	BEGIN
		SELECT @UserProts = 0
	END
		
	select @AdminUser, @UserCusts, @UserProts

	SET @OrderBy = 'CreatedByDate DESC, OH.Protocol'
	
	SET @SQL_SELECT = '
		SELECT ROW_NUMBER() OVER(ORDER BY ' + @OrderBy + ') AS RowNumber
			,OrderHistoryID
			,ST.StatusID
			,LEFT(ST.StatusDescription
			,CHARINDEX('' '', ST.StatusDescription) - 1) AS Status
			,OH.StudyID AS StudyID
			,OH.Protocol AS Protocol
--			,C.custname AS Customer
			,A.Study_Site
			,CreatedBy
			,CreatedByDate
			,CustOrderNumber AS CustControlNum
			,Order_Num AS FCSOrderNumber
			,ShipByDate 
			,Customer
		FROM OrderHistory OH (NOLOCK)
			LEFT JOIN CDSWeb..Customer C (NOLOCK) ON C.custno = OH.CustNo collate SQL_Latin1_General_CP1_CI_AS
				AND C.Site = OH.SiteID
			INNER JOIN StatusesML ST (NOLOCK) on ST.StatusID = OH.StatusID
			INNER JOIN Addresses A (NOLOCK) ON A.Address_ID = OH.AddressID
		WHERE (WebUserID = ' + CAST(@WebUserID AS VARCHAR) + ' OR @AdminUser = 1 OR @UserCusts = 1 OR @UserProts = 1 )'
		
	IF @StatusID <> '-1' --All Status Codes
		BEGIN
			--SET @SQL_WHERE = @SQL_WHERE + ' AND (ST.StatusID = CAST(@StatusID As VARCHAR))'	
			SET @SQL_SELECT = @SQL_SELECT + ' AND (ST.StatusID = ' + @StatusID + ')'
		END			
	
	IF @Protocol <> '-1' --All Protocols
		BEGIN
			SET @SQL_SELECT = @SQL_SELECT + ' AND (OH.Protocol = ''' + @Protocol + ''')'	
		END	
		
	SET @SQL1 = @SQL_SELECT
		
	PRINT @SQL1
	
	EXEC(@SQL1)		
		
END

WHERE (WebUserID = ' + CAST(@WebUserID AS VARCHAR) + ' OR @AdminUser = 1 OR

WHERE (WebUserID = ' + CAST(@WebUserID AS VARCHAR) + ' OR ' + CAST(@AdminUser AS INT) + '= 1  OR ' + CAST(@UserCusts AS INT) + '= 1 OR ' + CAST(@UserProts AS INT) + '= 1 )'

EXEC sp_executesql @SQL1, '@AdminUser INT ,@UserCusts INT ,@UserProts INT ' , @AdminUser  ,@UserCusts  ,@UserProts