SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[files_users_WhoDownloadedFile]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[files_users_WhoDownloadedFile]
GO

CREATE PROCEDURE [dbo].files_users_WhoDownloadedFile
(
	@PageIndex int,
	@PageSize int,
	@PostID int,
	@ReturnRecordCount bit = 0,
	@SettingsID int,
	@StartDate datetime,
	@EndDate datetime
)
AS
BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int
DECLARE @TotalUsers int

-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1

-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn

-- Create a temp table to store the select results
CREATE TABLE #PageIndexForUsers 
(
	IndexID int IDENTITY (1, 1) NOT NULL,
	LatestDownloadDate datetime,
	TotalDownloads int,
	UserID int
)	

-- Do we need to return a record count?
-- *************************************
IF (@ReturnRecordCount = 1)
    SET @TotalUsers = (SELECT count(distinct U.cs_UserId) FROM cs_vw_Users_FullUser U, files_Downloads D WHERE D.UserId = U.cs_UserId and D.PostID = @PostID and U.SettingsID = @SettingsID and D.DownloadDate between @StartDate and @EndDate)

-- Special case depending on what the user wants and how they want it ordered by
-- *************************************

INSERT INTO #PageIndexForUsers (UserID, LatestDownloadDate, TotalDownloads)
SELECT U.cs_UserID, Max(D.DownloadDate), count(*)
FROM cs_vw_Users_FullUser U, files_Downloads D 
WHERE D.UserId = U.cs_UserId 
	and D.PostID = @PostID 
	and U.SettingsID = @SettingsID 
	and D.DownloadDate between @StartDate and @EndDate
GROUP BY U.cs_UserID
ORDER BY Max(D.DownloadDate)

-- Get the user details
SELECT
	*, @PostID as PostID
FROM 
	cs_vw_Users_FullUser U (nolock),
	#PageIndexForUsers
WHERE 
	U.cs_UserID = #PageIndexForUsers.UserID AND
	#PageIndexForUsers.IndexID > @PageLowerBound AND
	#PageIndexForUsers.IndexID < @PageUpperBound  and U.SettingsID = @SettingsID 
ORDER BY
	#PageIndexForUsers.IndexID
END

-- Return the record count if necessary

IF (@ReturnRecordCount = 1)
  SELECT @TotalUsers

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].files_users_WhoDownloadedFile  to public
go