USE [uf]
GO
/****** Object:  StoredProcedure [dbo].[UF2_sp_Record_Paging]    Script Date: 04/17/2014 18:53:20 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[UF2_sp_Record_Paging] (
	@strFields VARCHAR(8000) ,
	@strPK VARCHAR(100),
	@strTables VARCHAR(8000),	
	@strFilter VARCHAR(8000) = NULL,
	@strSort VARCHAR(8000) = NULL,
	@strGroup VARCHAR(8000) = NULL,
	@intPageNo INT = 1,
	@intPageSize INT = NULL,
	@blnGetRecordCount BIT = 0,
	@strCountTables VARCHAR(4000)
)

/*
	PURPOSE: executes a select statement as defined by the parameters and returns a particular page of data (or all rows) efficiently
	
*/

 AS
	DECLARE @blnBringAllRecords BIT
	DECLARE @strPageNo VARCHAR(50)
	DECLARE @strPageSize VARCHAR(50)
	DECLARE @strSkippedRows VARCHAR(50)

	DECLARE @strFilterCriteria VARCHAR(8000)
	DECLARE @strSimpleFilter VARCHAR(8000)
	DECLARE @strSortCriteria VARCHAR(8000)
	DECLARE @strGroupCriteria VARCHAR(8000)

	DECLARE @intRecordcount INT
	DECLARE @intPagecount INT	


--******** NORMALIZE THE PAGING CRITERIA
	--if no meaningful inputs are provided, we can avoid paging and execute a more efficient query, so we will set a flag that will help with that (blnBringAllRecords)

	IF @intPageNo < 1
		SET @intPageNo = 1

	SET @strPageNo = CONVERT(VARCHAR(50), @intPageNo)
	
	IF @intPageSize IS NULL OR @intPageSize < 1 -- BRING ALL RECORDS, DON'T DO PAGING
		SET @blnBringAllRecords = 1
	ELSE
		BEGIN
			SET @blnBringAllRecords = 0
			SET @strPageSize = CONVERT(VARCHAR(50), @intPageSize)
			SET @strPageNo =  CONVERT(VARCHAR(50), @intPageNo)
			SET @strSkippedRows = CONVERT(VARCHAR(50), @intPageSize * (@intPageNo - 1))
		END





--******** NORMALIZE THE FILTER AND SORTING CRITERIA
	--if they are empty, we will avoid filtering and sorting, respectively, executing more efficient queries

	IF @strFilter IS NOT NULL AND @strFilter != '' 
		BEGIN
			SET @strFilterCriteria = ' WHERE ' + @strFilter + ' '
			SET @strSimpleFilter = ' AND ' + @strFilter + ' '
		END
	ELSE
		BEGIN
			SET @strSimpleFilter = ''
			SET @strFilterCriteria = ''
		END

	IF @strSort IS NOT NULL AND @strSort != '' 
		SET @strSortCriteria = ' ORDER BY ' + @strSort + ' '
	ELSE
		SET @strSortCriteria = ''

	IF @strGroup IS NOT NULL AND @strGroup != '' 
		SET @strGroupCriteria = ' GROUP BY ' + @strGroup + ' '
	ELSE
		SET @strGroupCriteria = ''



--************************** NOW START DOING THE REAL WORK
--!NOTE: for potentially improved performance, use sp_executesql instead of EXEC

IF @blnBringAllRecords = 1 --ignore paging and run a simple select
BEGIN

	EXEC (
		'SELECT ' + @strFields + ' FROM ' + @strTables + @strFilterCriteria + @strGroupCriteria + @strSortCriteria
	)

END-- WE HAD TO BRING ALL RECORDS
ELSE		--BRING ONLY A PARTICULAR PAGE
BEGIN
	IF @intPageNo = 1 --in this case we can execute a more efficient query, with no subqueries
		EXEC (
			'SELECT TOP ' + @strPageSize + ' ' + @strFields + ' FROM ' + @strTables + @strFilterCriteria + @strGroupCriteria + @strSortCriteria
		)
	ELSE --execute a structure of subqueries that brings the correct page
		EXEC (
			'SELECT ' + @strFields + ' FROM ' + @strTables + ' WHERE ' + @strPK + ' IN ' + '
				(SELECT TOP ' + @strPageSize + ' ' + @strPK + ' FROM ' + @strTables + 
					' WHERE ' + @strPK + ' NOT IN ' + '
						(SELECT TOP ' + @strSkippedRows + ' ' + @strPK + ' FROM ' + @strTables + @strFilterCriteria + @strGroupCriteria + @strSortCriteria + ') ' +
					@strSimpleFilter +
					@strGroupCriteria +
					@strSortCriteria + ') ' +
				@strSimpleFilter +
				@strGroupCriteria +
				@strSortCriteria + ' OPTION (RECOMPILE)'
		)

END --WE HAD TO BRING A PARTICULAR PAGE


--IF WE NEED TO RETURN THE RECORDCOUNT
IF @blnGetRecordCount = 1
	IF @strGroupCriteria != ''
		EXEC (
			'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM ' + @strCountTables + @strFilterCriteria + @strGroupCriteria + ') AS tbl (id)'
		)
	ELSE
		EXEC (
			'SELECT COUNT(*) AS RECORDCOUNT FROM ' + @strCountTables + @strFilterCriteria + @strGroupCriteria
		)

DECLARE @rowsPerPage int,
    @pageNum int,
 @startRow int,
 @endRow int

SET @rowsPerPage = 10
SET @pageNum = 3

SET @startRow = ((@pageNum- 1) * @rowsPerPage)+1
SET @endRow = @startRow + @rowsPerPage -1

SELECT * FROM (
 SELECT row_number() OVER (ORDER BY id) as resultNum, id
 FROM regTrackingHistory
) as numberResults
WHERE resultNum  BETWEEN @startRow AND @endRow

exec Paging '
SELECT
"UF2_rt_Images.image_id AS image_id,
UF2_rt_Images.user_id AS user_id
UF2_rt_Images.camera_id AS camera_id,
UF2_rt_Images.Lens_id AS Lens_id,
UF2_rt_Images.image_camera_legacy AS camera_legacy,
UF2_rt_Images.image_title AS image_title,
UF2_rt_Images.editors_choice AS editors_choice,
UF2_rt_Images.staff_choice AS staff_choice,
UF2_rt_Images.staff_choicebit AS staff_choicebit,
UF2_rt_Images.featured_comments AS featured_comment,
UF2_rt_Images.image_rateable AS rateable,
UF2_rt_Images.image_adult AS adult,
UF2_Image_View_Counts.view_count AS view_count,
UF2_rt_Images.Photo_Of_Day AS Photo_Of_Day,
UF2_rt_Images.Photo_Of_Day_Awarded AS Photo_Of_Day_Awarded,
UF2_rt_Images.Photog_Of_Day AS Photog_Of_Day,
UF2_rt_Images.Photog_Of_Day_Awarded AS Photog_Of_Day_Awarded,
UF2_rt_Images.Donor_Of_Day AS Donor_Of_Day,
UF2_rt_Images.Donor_Of_Day_Awarded AS Donor_Of_Day_Awarded,
UF2_rt_Images.Image_Project_Spotlight AS Image_Project_Spotlight,
UF2_rt_Images.Project_ID AS Project_ID,
UF2_rt_Images.comment_count AS comment_count,
UF2_rt_Images.rating_count AS rating_count,
UF2_rt_Images.dt_Image_Rating_Count,
UF2_rt_Images.favorite_count AS favorite_count,
UF2_rt_Images.image_Rating_Override AS image_Rating_Override,
UF2_rt_Images.dt_image_Rating_Count AS dt_Rating_Count,
UF2_rt_Images.dt_image_Rating_Avg AS dt_Rating_Avg,
UF2_rt_Images.rating_total AS rating_total,
UF2_rt_cameras.camera_model AS camera_model,
UF2_rt_users.user_donor AS donor,
UF2_rt_users.user_Gender AS user_gender,
UF2_rt_users.user_Allowed_Top_Photo AS user_Allowed_Top_Photo,
UF2_rt_cameras.manufacturer_id AS camera_mfg_id,
UF2_rt_users.user_last_name AS Last_Name,
UF2_rt_users.user_first_name AS First_Name,
UF2_rt_manufacturers.manufacturer_name AS Camera_Mfg, 
UF2_Critique_Corner.CC_Active AS CC_Active ','
UF2_rt_Images.image_ID
','
FROM
(((((((UF2_rt_Images 
INNER JOIN UF2_rt_users ON (UF2_rt_users.user_id=UF2_rt_Images.user_id)) 
LEFT JOIN UF2_rt_cameras ON (UF2_rt_cameras.camera_id=UF2_rt_Images.camera_id)) 
LEFT JOIN UF2_rt_Lenses ON (UF2_rt_Lenses.Lens_ID=UF2_rt_Images.Lens_id)) 
LEFT JOIN UF2_rt_film ON (UF2_rt_film.film_id=UF2_rt_Images.film_id)) 
LEFT JOIN UF2_rt_manufacturers ON (UF2_rt_manufacturers.manufacturer_id=UF2_rt_cameras.manufacturer_id)) 
LEFT JOIN UF2_Image_view_Counts ON (UF2_image_view_counts.image_id=UF2_rt_images.image_id)) 
LEFT JOIN UF2_Critique_Corner ON (UF2_Critique_Corner.CC_Image_ID = UF2_rt_Images.Image_ID)) 
','
WHERE
((UF2_rt_Images.Image_Active = 1) AND (UF2_rt_Users.User_Level = 1) AND (UF2_rt_Users.User_Active = 1) AND (UF2_rt_Images.Image_Type = 1) AND (UF2_rt_Images.Image_Allowed_Top_Rated = 1) AND (UF2_rt_Users.User_Allowed_Top_Photo = 1)) 
','
ORDER BY
UF2_rt_Images.Favorite_Count, UF2_rt_Images.Image_ID DESC
','