<!--- Bug 6673 Add indices on the FormResponse table to improve query execution --->
<cfquery datasource="#application.config.DSN#">
-- Create New Index IX_FormResponseField_FormResponseIDFormFieldID
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='IX_FormResponseField_FormResponseIDFormFieldID' AND object_id = OBJECT_ID('FormResponseField'))
BEGIN
CREATE NONCLUSTERED INDEX [IX_FormResponseField_FormResponseIDFormFieldID] ON [FormResponseField]
(
	[FormResponseID] ASC,
	[FormFieldID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
END

-- Create New Index IX_FormResponse_UserID
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='IX_FormResponse_UserID' AND object_id = OBJECT_ID('FormResponse'))
BEGIN
CREATE NONCLUSTERED INDEX [IX_FormResponse_UserID] ON [FormResponse]
(
    [UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
END

-- Create New Index IX_FormResponse_WorkflowSetIDInstance
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='IX_FormResponse_WorkflowSetIDInstance' AND object_id = OBJECT_ID('FormResponse'))
BEGIN
CREATE NONCLUSTERED INDEX [IX_FormResponse_WorkflowSetIDInstance] ON [FormResponse]
(
    [WorkflowSetIDInstance] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
END

-- Create New Index IX_Workflow_CurrentWorkflowApprovalStatus
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='IX_Workflow_CurrentWorkflowApprovalStatus' AND object_id = OBJECT_ID('Workflow'))
BEGIN
	CREATE NONCLUSTERED INDEX [IX_Workflow_CurrentWorkflowApprovalStatus] ON [Workflow]
	(
		[CurrentWorkflow] ASC,
		[ApprovalStatus] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
END

-- Create New Index  IX_WorkflowStage_CurrentStageWorflowStageTypeID
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='IX_WorkflowStage_CurrentStageWorflowStageTypeID' AND object_id = OBJECT_ID('WorkflowStage'))
BEGIN
	CREATE NONCLUSTERED INDEX [IX_WorkflowStage_CurrentStageWorflowStageTypeID] ON [WorkflowStage]
	(
		[CurrentStage] ASC
	)
	INCLUDE ( 	[WorkflowStageTypeID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
END

-- Create New Index  IX_WorkflowStageUser_UserIDViewOnlyConsult
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='IX_WorkflowStageUser_UserIDViewOnlyConsult' AND object_id = OBJECT_ID('WorkflowStageUser'))
BEGIN
	CREATE NONCLUSTERED INDEX [IX_WorkflowStageUser_UserIDViewOnlyConsult] ON [WorkflowStageUser]
	(
		[UserID] ASC,
		[ViewOnlyConsult] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
END

-- Change Primary Key on FormField to be a clustered index
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'PK_FormField' and  object_id = OBJECT_ID('FormField') and type_desc = 'CLUSTERED')
BEGIN
	-- Drop Foreign Key Contraints so we can delete PK
	IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME = 'FK_FormTrigger_FormField' AND TABLE_NAME = 'FormTrigger')
	BEGIN
		ALTER TABLE [FormTrigger] DROP CONSTRAINT [FK_FormTrigger_FormField]
	END

	IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME = 'FK_FormTrigger_FormField1' AND TABLE_NAME = 'FormTrigger')
	BEGIN
		ALTER TABLE [FormTrigger] DROP CONSTRAINT [FK_FormTrigger_FormField1]
	END

	IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME = 'FK_FormFieldOption_FormField' AND TABLE_NAME = 'FormFieldOption')
	BEGIN
		ALTER TABLE [FormFieldOption] DROP CONSTRAINT [FK_FormFieldOption_FormField]
	END

	IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME = 'FK_FormResponseField_FormField' AND TABLE_NAME = 'FormResponseField')
	BEGIN
		ALTER TABLE [FormResponseField] DROP CONSTRAINT [FK_FormResponseField_FormField]
	END

	-- Delete Primary Key
	IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'PK_FormField' and  object_id = OBJECT_ID('FormField'))
	BEGIN
		ALTER TABLE [FormField] DROP CONSTRAINT [PK_FormField]
	END
	
	-- Add new clustered primary key
	ALTER TABLE [FormField] ADD  CONSTRAINT [PK_FormField] PRIMARY KEY CLUSTERED 
	(
		[FormID] ASC,
		[FormFieldID] ASC
	) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
	

	-- Re-add constraints now PK has been added
	ALTER TABLE [FormTrigger]  WITH CHECK ADD  CONSTRAINT [FK_FormTrigger_FormField] FOREIGN KEY([FormID], [FormFieldID])
	REFERENCES [FormField] ([FormID], [FormFieldID])
	ALTER TABLE [FormTrigger] CHECK CONSTRAINT [FK_FormTrigger_FormField]

	ALTER TABLE [FormTrigger]  WITH CHECK ADD  CONSTRAINT [FK_FormTrigger_FormField1] FOREIGN KEY([FormID], [TriggerFieldID])
	REFERENCES [FormField] ([FormID], [FormFieldID])
	ALTER TABLE [FormTrigger] CHECK CONSTRAINT [FK_FormTrigger_FormField1]

	ALTER TABLE [FormFieldOption]  WITH CHECK ADD  CONSTRAINT [FK_FormFieldOption_FormField] FOREIGN KEY([FormID], [FormFieldID])
	REFERENCES [FormField] ([FormID], [FormFieldID])
	ALTER TABLE [FormFieldOption] CHECK CONSTRAINT [FK_FormFieldOption_FormField]

	ALTER TABLE [FormResponseField]  WITH CHECK ADD  CONSTRAINT [FK_FormResponseField_FormField] FOREIGN KEY([FormID], [FormFieldID])
	REFERENCES [FormField] ([FormID], [FormFieldID])
	ALTER TABLE [FormResponseField] CHECK CONSTRAINT [FK_FormResponseField_FormField]
END
</cfquery>

<!--- Case 6672: The stored procedure needed to default the form landing page to show the content instead of the approvals tab. --->
<cfquery datasource="#application.config.DSN#">
	IF OBJECT_ID(N'spUserHasPendingFormItems', N'P') IS NOT NULL
	DROP PROC spUserHasPendingFormItems
</cfquery>
<cfquery datasource="#application.config.DSN#">
CREATE PROCEDURE [spUserHasPendingFormItems] 
	@UserID INT,
	@IsAdmin BIT
AS
BEGIN
	DECLARE @HasPending BIT
	SET @HasPending =  0

	IF (@HasPending = 0 AND @IsAdmin = 1)
	BEGIN
		--SELECT 'FormResponse, User as Admin: Check for any pending for approvals'
		SELECT	TOP 1 @HasPending = 1			
		FROM		FormResponse AS fr 
						INNER JOIN Form	AS f ON fr.FormID = f.FormID
						INNER JOIN Workflow AS w ON fr.WorkflowSetIDInstance = w.WorkflowSetID AND w.CurrentWorkflow = 1						
		WHERE		w.ApprovalStatus = 'p'
					AND fr.Deleted = 0
					AND f.Deleted = 0
					AND w.WorkflowID IN (SELECT ws.WorkflowID FROM WorkflowStage ws WHERE ws.WorkflowID = w.WorkflowID)
	END

	IF (@HasPending = 0)
	BEGIN
		--SELECT 'FormResponse, User as Approval Manager: Check for pending approval action'
		SELECT	TOP 1 @HasPending = 1	
		FROM
			FormResponse response
			JOIN WorkflowSet workflowSet on response.WorkflowSetIDInstance = workflowSet.WorkflowSetID
			JOIN Workflow workflow on response.WorkflowSetIDInstance = workflow.WorkflowSetID
			JOIN WorkflowStage stage on workflow.WorkflowID = stage.WorkflowID AND workflow.WorkflowSetID = stage.WorkflowSetID
			JOIN WorkflowStageUser stageUser on stage.WorkflowID = stageUser.WorkFlowID AND stage.WorkflowSetID = stageUser.WorkflowSetID AND stage.StageID = stageUser.StageID
		WHERE
			response.Deleted = 0				-- not deleted
			AND response.Draft = 0				-- not draft
			AND workflow.ApprovalStatus = 'p'	-- workflow still pending approval
			AND workflow.CurrentWorkflow = 1	-- current workflow
			AND stage.CurrentStage = 1			-- current stage
			AND stage.WorkflowStageTypeID IN (2,3,4,5)  -- not notifications (1), but all approval stages
			AND stageUser.ViewOnlyConsult = 0	-- not just view consulting
			AND stageUser.UserID = @UserID		-- current user 
			AND stageUser.WorkflowStageUserID	-- not already approved current stage 
			NOT IN 
				(
					SELECT	e.WorkflowStageUserID
					FROM
						WorkflowStageUser d 
						INNER JOIN WorkflowActivity e ON d.WorkflowStageUserID = e.WorkflowStageUserID
					WHERE		
						d.UserID = @UserID
						AND e.ActivityType = 'a'
				)
	END

	IF (@HasPending = 0)
	BEGIN

		--SELECT 'FormResponse, User as Form Submitter: Check for user submitted responses pending approval, declined, or reset'
		SELECT	TOP 1 @HasPending = 1	
		FROM
			FormResponse AS fr 
			INNER JOIN Form	AS f ON fr.FormID = f.FormID
			INNER JOIN Workflow AS w ON fr.WorkflowSetIDInstance = w.WorkflowSetID AND w.CurrentWorkflow = 1
		WHERE
			fr.UserID = @UserID
			AND fr.Deleted = 0
			AND f.Deleted = 0
			AND (w.ApprovalStatus = 'p' OR w.ApprovalStatus = 'd' OR w.ApprovalStatus = 't')
	END

	IF (@HasPending = 0)
	BEGIN	
		-- SELECT 'FormResponse, User as Form Owner: Check for forms this user owns that are pending'
		SELECT	TOP 1 @HasPending = 1	
		FROM
			FormResponse AS fr 
			INNER JOIN Form	AS f ON fr.FormID = f.FormID
			INNER JOIN Workflow AS w ON fr.WorkflowSetIDInstance = w.WorkflowSetID AND w.CurrentWorkflow = 1
		WHERE
			w.ApprovalStatus = 'p'
			AND fr.Deleted = 0
			AND f.Deleted = 0
			AND	
			(
				fr.FormID IN
				(
					SELECT		FormID
					FROM		FormResponsePermission
					WHERE		UserID = @UserID
																
					OR
														
					GroupID IN 
					(
						SELECT		GroupID
						FROM		UserGroups
						WHERE		UserID = @UserID
					)
				)
				OR
				f.UserID = @UserID
			)
	END

	IF (@HasPending = 0)
	BEGIN
		-- SELECT 'Form, User as Admin or Owner for Content Approval: Check for all forms in content approval'
		-- Check first to see if admin is an app owner

		DECLARE @IsAppOwner BIT
		SET @IsAppOwner = 0

		-- if admin, also app owner
		IF (@IsAdmin = 1)
		BEGIN
			SET @IsAppOwner = 1
		END
		ELSE
		-- if not admin, check for app owner
		BEGIN
			SELECT	TOP 1 @IsAppOwner = 1	
			FROM		UserPermissions
			WHERE		MenuID = 7
						AND CategoryID IS NULL
						AND AppOwner = 'yes'
						AND
						(
							UserID = @UserID
							OR GroupID IN
							(
								SELECT		GroupID
								FROM		UserGroups
								WHERE		UserID = @UserID
							)
						)
		END
	
		-- only if app owner, search for all content approval pending
		IF (@IsAppOwner = 1)
		BEGIN
			SELECT	TOP 1 @HasPending = 1	
			FROM 
				Form f
				INNER JOIN Workflow w ON f.WorkflowSetIDInstance = w.WorkflowSetID AND w.CurrentWorkflow = 1
			WHERE		
				w.ApprovalStatus = 'p'
				AND f.Deleted = 0
		END						
	END

	IF (@HasPending = 0)
	BEGIN
		-- SELECT 'Form, User as Content Approver: Check for added forms requring user approval'	
		SELECT	TOP 1 @HasPending = 1	
						
		FROM
			Workflow a 
			INNER JOIN WorkflowStage b ON a.WorkflowSetID = b.WorkflowSetID AND a.WorkflowID = b.WorkflowID AND b.CurrentStage = 1 AND a.CurrentWorkflow = 1 AND a.ApprovalStatus = 'p'
			INNER JOIN WorkflowStageUser c ON b.WorkflowSetID = c.WorkflowSetID AND b.WorkflowID = c.WorkflowID AND b.StageID = c.StageID
			INNER JOIN WorkflowSet d ON a.WorkflowSetID = d.WorkflowSetID AND a.CurrentWorkflow = 1
			LEFT JOIN (
				SELECT		COUNT(e.WorkflowActivityID) AS CntWorkflowActivity, e.WorkflowStageUserID
				FROM
					WorkflowStageUser d 
					INNER JOIN WorkflowActivity e ON d.WorkflowSetID = e.WorkflowSetID AND d.WorkflowID = e.WorkflowID AND d.StageID = e.StageID AND d.WorkflowStageUserID = e.WorkflowStageUserID
				WHERE
					d.UserID = @UserID
					AND e.ActivityType = 'a'
					GROUP BY	e.WorkflowStageUserID
			) AS wa ON c.WorkflowStageUserID = wa.WorkflowStageUserID 
			INNER JOIN Form f ON a.WorkflowSetID = f.WorkflowSetIDInstance
		WHERE
			c.UserID = @UserID
			AND f.Deleted = 0
			AND c.ViewOnlyConsult = 0
			AND wa.CntWorkflowActivity IS NULL
	END


	IF (@HasPending = 0)
	BEGIN
		-- SELECT 'Form, User as Content Approval Submitter: Check for forms user changed still pending/declined/reset'
		SELECT	TOP 1 @HasPending = 1	
		FROM
			Form f
			INNER JOIN Workflow AS w ON f.WorkflowSetIDInstance = w.WorkflowSetID AND w.CurrentWorkflow = 1
		WHERE
			f.UserID = @UserID
			AND f.Deleted = 0
			AND (w.ApprovalStatus = 'p' OR w.ApprovalStatus = 'd' OR w.ApprovalStatus = 't')
	END

	SELECT @HasPending As hasPending
END
</cfquery>


<!--- 6812: Stored Procedure Needs to include UserID in column to export filter form responces--->
<cfquery datasource="#application.config.DSN#">
	IF OBJECT_ID(N'spFormResponseData', N'P') IS NOT NULL
	DROP PROC spFormResponseData
</cfquery>
<cfquery datasource="#application.config.DSN#">
CREATE PROCEDURE [spFormResponseData] 
		@FormID INT, 
		@Export BIT, 
		@OrderBy NVARCHAR(100) = 'ResponseNumber DESC', 
		@ColumnList NVARCHAR(MAX) = NULL,
		@Pagination BIT = NULL, 
		@ItemsPerPage TINYINT = 5, 
		@PageIdx INT = 1
	AS
	BEGIN
		-- declare variables needed for procedure
		DECLARE @DynamicSQL NVARCHAR(MAX), @Fields NVARCHAR(MAX), @PivotFields NVARCHAR(MAX), @sFormID NVARCHAR(12)
		DECLARE @FieldName NVARCHAR(MAX), @FieldType NVARCHAR(10), @Value NVARCHAR(MAX), @Operator NVARCHAR(5), @Descending NVARCHAR(5)
		DECLARE @FormFieldID INT, @ConditionID INT, @PageRowStart INT, @PageRowEnd INT, @TotalResponses INT
		DECLARE @Scoring BIT, @Approval BIT
		SELECT @sFormID = CAST(@FormID AS NVARCHAR(12))
		
		-- create temp tables if they don't exist (used for response filtering)
		IF OBJECT_ID('tempdb..##tblFormResponseID') IS NULL
		BEGIN
			CREATE TABLE ##tblFormResponseID (FormID INT, FormResponseID INT)
			INSERT ##tblFormResponseID SELECT DISTINCT FormID, FormResponseID FROM FormResponse WHERE FormID = @FormID
		END
		IF OBJECT_ID('tempdb..##tblConditions') IS NULL
		BEGIN
			CREATE TABLE ##tblConditions (ConditionID INT IDENTITY(1,1) NOT NULL,
				FieldName [NVARCHAR](20) NOT NULL,
				FieldType [NVARCHAR](10) NOT NULL,
				Value [NVARCHAR](MAX) NOT NULL,
				Operator [NVARCHAR](5) NOT NULL
			)
		END
		
		-- check if form has scoring and if response has approval workflow
		SELECT	@Scoring = ft.Scoring
		FROM	Form f
					INNER JOIN FormType ft ON f.FormTypeID = ft.FormTypeID
		WHERE	f.FormID = @FormID
		SELECT	@Approval = CASE WHEN s.StageID IS NULL THEN 0 ELSE 1 END
		FROM	Form f
					LEFT JOIN WorkflowSet ws ON f.WorkflowSetIDTemplate = ws.WorkflowSetID
					LEFT JOIN Workflow w ON ws.WorkflowSetID = w.WorkflowSetID AND w.ParentID IS NULL
					LEFT JOIN WorkflowStage s ON w.WorkflowID = s.WorkflowID AND s.WorkflowStageTypeID IN (2,3,4)
		WHERE	f.FormID = @FormID
		
		-- response filtering (used in search of responses)
		SELECT	@ConditionID = MIN(ConditionID)
		FROM	##tblConditions
		WHILE	@ConditionID IS NOT NULL
		BEGIN
			SELECT	@FieldName = FieldName, @FieldType = FieldType, @Value = Value, @Operator = Operator
			FROM	##tblConditions
			WHERE	ConditionID = @ConditionID
			IF CHARINDEX('field', @FieldName) > 0
			BEGIN
				-- dynamic field (data stored in FormResponseField sub table)
				SELECT @DynamicSQL = '
					DELETE FROM ##tblFormResponseID
					WHERE FormResponseID NOT IN (
						SELECT	fr.FormResponseID
						FROM	FormResponse fr
									INNER JOIN ##tblFormResponseID tr ON fr.FormID = tr.FormID
										AND fr.FormResponseID = tr.FormResponseID
									INNER JOIN FormResponseField' + @FieldType + ' v ON tr.FormID = v.FormID
										AND tr.FormResponseID = v.FormResponseID
										AND v.FormFieldID = ' + REPLACE(@FieldName, 'field', '') + '
										AND v.FormID = ' + @sFormID + '
								
						WHERE	fr.FormID = ' + @sFormID + '
								AND v.Value IS NOT NULL AND v.Value ' +
								CASE @FieldType
									WHEN 'Date' THEN
										@Operator + ' CAST(''' + @Value + ''' AS datetime)'
									WHEN 'Number' THEN
										@Operator + ' ' + @Value
									WHEN 'String' THEN
										@Operator + ' ''' + @Value + ''''
								END + '
					)'
				EXEC (@DynamicSQL)			
			END
			ELSE
			BEGIN
				-- system field (filter using base FormResponse table)
				SELECT @DynamicSQL = '
					DELETE FROM ##tblFormResponseID
					WHERE FormResponseID NOT IN (
						SELECT	fr.FormResponseID
						FROM	FormResponse fr
									INNER JOIN ##tblFormResponseID tr ON fr.FormID = tr.FormID
										AND fr.FormResponseID = tr.FormResponseID
						WHERE	fr.' + @FieldName + ' ' +
								CASE @FieldType
									WHEN 'Date' THEN
										@Operator + ' CAST(''' + @Value + ''' AS datetime) AND ' + @FieldName + ' IS NOT NULL'
									WHEN 'Number' THEN
										@Operator + ' ' + @Value + ' AND ' + @FieldName + ' IS NOT NULL'
									WHEN 'String' THEN
										@Operator + ' ''' + @Value + ''' AND ' + @FieldName + ' IS NOT NULL'
									WHEN 'IntList' THEN
										' IN (' + @Value + ')'
								END + '
					)'
				EXEC (@DynamicSQL)
			END
					
			SELECT	@ConditionID = MIN(ConditionID)
			FROM	##tblConditions
			WHERE	ConditionID > @ConditionID
		END

		-- buidling sort expression
		SELECT @Descending = CASE WHEN CHARINDEX(' DESC', @OrderBy) > 0 THEN N' DESC' ELSE N'' END
		SELECT @FieldName = REPLACE(@OrderBy, ' DESC', '')
		SELECT @FieldType = t.ResponseTable
		FROM	FormField f
					INNER JOIN FormFieldType t ON f.FieldTypeID = t.FieldTypeID
		WHERE	f.FieldName = @FieldName
		IF @FieldType = 'Number'
		BEGIN
			SELECT @OrderBy = 'CAST(REPLACE(' + @FieldName + ', ''$'', '''') AS DECIMAL(19, 6))' + @Descending
		END
				
		-- pagination
		IF @Pagination = 1
		BEGIN
			SELECT @PageRowEnd = @PageIdx * @ItemsPerPage
			SELECT @PageRowStart = @PageRowEnd - @ItemsPerPage + 1
			SELECT @TotalResponses = COUNT(1) FROM ##tblFormResponseID
			SELECT @Fields = CAST(@TotalResponses AS NVARCHAR(12)) + ' AS TotalResults, '
			
			IF CHARINDEX('field', @OrderBy) > 0
			BEGIN
				-- dynamic field (pivot field and paginate)		
				SELECT @DynamicSQL = '
					WITH Responses AS (
						SELECT	tr.FormResponseID, ROW_NUMBER() OVER (ORDER BY Value ' + @Descending + ') AS RowNo
						FROM	##tblFormResponseID tr
									INNER JOIN FormResponseField' + @FieldType + ' frf ON tr.FormID = frf.FormID
										AND tr.FormResponseID = frf.FormResponseID
										AND frf.FormFieldID = ' + REPLACE(@FieldName, 'field', '') + '
										AND frf.FormID = ' + @sFormID + '
					)
					DELETE FROM	##tblFormResponseID
					WHERE	FormResponseID NOT IN (
								SELECT	FormResponseID
								FROM	Responses
								WHERE	RowNo BETWEEN ' + CAST(@PageRowStart AS NVARCHAR(12)) + ' AND ' + CAST(@PageRowEnd AS NVARCHAR(12)) + '
							)
					'
				EXEC (@DynamicSQL)
			END
			ELSE
			BEGIN
				-- system field (paginate on base response table)
				SELECT @DynamicSQL = '
					WITH Responses AS (
						SELECT	fr.FormResponseID, ROW_NUMBER() OVER (ORDER BY ' + 
							CASE WHEN @FieldName = 'SubmittedBy' 
								THEN 'p.FirstName' + @Descending + ', p.LastName' + @Descending
								ELSE @OrderBy END + ') AS RowNo
						FROM	FormResponse fr
									INNER JOIN ##tblFormResponseID tr ON fr.FormID = tr.FormID
										AND fr.FormResponseID = tr.FormResponseID
									LEFT JOIN UserProfile u ON fr.UserID = u.UserID
										LEFT JOIN Person p ON u.PersonID = p.PersonID
						WHERE	fr.FormID = ' + @sFormID + '
					)
					DELETE FROM	##tblFormResponseID
					WHERE	FormResponseID NOT IN (
								SELECT	FormResponseID
								FROM	Responses
								WHERE	RowNo BETWEEN ' + CAST(@PageRowStart AS NVARCHAR(12)) + ' AND ' + CAST(@PageRowEnd AS NVARCHAR(12)) + '
							)
					'
				EXEC (@DynamicSQL)
			END		
		END
			
		-- determine column values & labels based on export flag
		IF @Fields IS NULL
			SELECT @Fields = ''
		IF @Export = 1
		BEGIN
			SELECT	@Fields = @Fields + 'fr.FormResponseID, fr.ResponseNumber, fr.UserID,'
			SELECT	@Fields = @Fields + 'CONVERT(VARCHAR(max), fr.SubmissionDate, 120) AS SubmissionDate, '
			SELECT	@Fields = @Fields + 'CASE WHEN p.FirstName IS NULL THEN '''' ELSE p.FirstName + '' '' + p.LastName END AS SubmittedBy, '
			SELECT	@Fields = @Fields + 'CASE WHEN p2.FirstName IS NULL THEN '''' ELSE p2.FirstName + '' '' + p2.LastName END AS LastEditedBy, '
			SELECT	@Fields = @Fields + 'CONVERT(VARCHAR(max), fr.LastEditedOn, 120) AS LastEditedOn'
			IF @Scoring = 1
			BEGIN
				SELECT	@Fields = @Fields + ', fr.PossibleScore, fr.Score'
			END
			IF @Approval = 1
			BEGIN
				SELECT	@Fields = @Fields + ', CASE 
												WHEN w.ApprovalStatus = ''p'' THEN ''Pending''
												WHEN w.ApprovalStatus = ''a'' THEN ''Approved'' 
												WHEN w.ApprovalStatus = ''d'' THEN ''Declined''
												WHEN w.ApprovalStatus = ''r'' THEN ''Rejected''
											END AS ApprovalStatus'
				SELECT	@Fields = @Fields + ', CONVERT(VARCHAR(max), w.FinalApprovalDate, 120) AS FinalApprovalDate'
			END
			SELECT	@PivotFields = 'ff.FieldName AS _Name_, 
						CASE
							WHEN ft.FieldTypeID = 8 
								THEN REPLACE(REPLACE(v.Value, ''{ts '''''', ''''), ''''''}'', '''')
							ELSE v.Value 
						END AS _Value_'
		END
		ELSE
		BEGIN
			SELECT	@Fields = @Fields + 'fr.ResponseNumber, fr.FormID, fr.FormResponseID, fr.UserID, fr.EditUserID, fr.WorkflowSetIDInstance, fr.Draft, '
			SELECT	@Fields = @Fields + 'fr.PossibleScore, fr.Score, fr.CreatedDate, fr.SubmissionDate, fr.Deleted, fr.DeletedDate, '
			SELECT	@Fields = @Fields + 'CASE WHEN p.FirstName IS NULL THEN '''' ELSE p.FirstName + '' '' + p.LastName END AS SubmittedBy, '
			SELECT	@Fields = @Fields + 'CASE WHEN p2.FirstName IS NULL THEN '''' ELSE p2.FirstName + '' '' + p2.LastName END AS LastEditedBy, fr.LastEditedOn, '
			SELECT	@Fields = @Fields + 'w.ApprovalStatus, w.FinalApprovalDate'
			SELECT	@PivotFields = 'ff.FieldName AS _Name_, v.Value AS _Value_'
		END
		
		-- build a column list of fields to pivot for the given form response(s) (if not passed in)
		IF @ColumnList IS NULL
		BEGIN
			SELECT	@ColumnList = ''
			SELECT	@ColumnList = @ColumnList + ', [' + FieldName + ']'
			FROM	FormField ff
			WHERE	ff.FormID = @FormID
					AND ff.FieldName IN (
						SELECT		DISTINCT(ff2.FieldName)
						FROM		FormField ff2
										INNER JOIN FormResponseField frf ON ff2.FormFieldID = frf.FormFieldID
										INNER JOIN ##tblFormResponseID tr ON frf.FormID = tr.FormID
											AND tr.FormResponseID = frf.FormResponseID
						WHERE		ff2.FormID = @FormID
										AND ff2.InSpreadsheet = 0
										AND ff2.Deleted = 0
					)
			SET @ColumnList = SUBSTRING(@ColumnList, 3, LEN(@ColumnList))
		END

		
	
		-- build dynamic sql to get response data
		IF @ColumnList = ''
		BEGIN
			-- no dynamic response fields, simple query
			SELECT @DynamicSQL = '
				SELECT		' + @Fields + '
				FROM		FormResponse fr
								INNER JOIN ##tblFormResponseID tr ON fr.FormID = tr.FormID
									AND fr.FormResponseID = tr.FormResponseID
								LEFT JOIN Workflow w ON fr.WorkflowSetIDInstance = w.WorkflowSetID
									AND w.CurrentWorkflow = 1
								LEFT JOIN UserProfile u ON fr.UserID = u.UserID
								LEFT JOIN Person p ON u.PersonID = p.PersonID 
								LEFT JOIN UserProfile u2 ON fr.EditUserID = u2.UserID
								LEFT JOIN Person p2 ON u2.PersonID = p2.PersonID 
				WHERE		fr.FormID = ' + @sFormID + '
				ORDER BY	' + @OrderBy
		END
		ELSE
		BEGIN
			-- perform pivot of response data
			SELECT @DynamicSQL = '
				SELECT	* 
				FROM	(
							SELECT	' + @Fields + ', ' + @PivotFields + '
							FROM	FormResponse fr
										INNER JOIN ##tblFormResponseID tr ON fr.FormID = tr.FormID
											AND fr.FormResponseID = tr.FormResponseID
										INNER JOIN FormResponseField frf ON tr.FormID = frf.FormID
											AND tr.FormResponseID = frf.FormResponseID
										INNER JOIN FormResponseFieldValue v ON frf.FormID = v.FormID 
											AND frf.FormResponseID = v.FormResponseID
											AND frf.FormFieldID = v.FormFieldID
										INNER JOIN FormField ff ON frf.FormID = ff.FormID
											AND frf.FormFieldID = ff.FormFieldID
										INNER JOIN FormFieldType ft ON ff.FieldTypeID = ft.FieldTypeID
										LEFT JOIN Workflow w ON fr.WorkflowSetIDInstance = w.WorkflowSetID
											AND w.CurrentWorkflow = 1
										LEFT JOIN UserProfile u ON fr.UserID = u.UserID
										LEFT JOIN Person p ON u.PersonID = p.PersonID
										LEFT JOIN UserProfile u2 ON fr.EditUserID = u2.UserID
										LEFT JOIN Person p2 ON u2.PersonID = p2.PersonID
							WHERE	fr.FormID = ' + @sFormID + '
									AND ff.Deleted = 0
									AND v.RowNo = 0
						) AS SourceTable
						PIVOT (MAX(SourceTable._Value_) FOR SourceTable._Name_ IN (' + @ColumnList + ')) AS PivotTable
				ORDER BY	' + @OrderBy
		END
	
		-- select response data based on dynamic sql
		EXEC (@DynamicSQL)

		-- drop temporary tables
		DROP TABLE ##tblFormResponseID
		DROP TABLE ##tblConditions
	END
</cfquery>

<!--- Bug 6771 - Enable Generic Error Page --->
<cfquery datasource="#application.config.DSN#">
IF NOT EXISTS(SELECT Value FROM AppSetting WHERE Name = 'GENERICERRORSENABLED')
BEGIN
INSERT INTO AppSetting
           (Name
           ,Value)
     VALUES
           ('GENERICERRORSENABLED'
           ,'0');
END
</cfquery>

<!--- Bug 6589 - Delete all user alerts that are currently set to deleted. --->
<cfquery datasource="#application.config.DSN#">
DELETE FROM UserEventAlerts WHERE Deleted = 1;
</cfquery>