<cfif form.Type EQ "Patch">
	<cfinclude template="patch_15.0.09.cfm">
</cfif>

<!--- Case RR-2122 - Adding "user list" field option to online forms --->
<cftransaction>
<cfquery datasource="#application.config.DSN#" name="updateFormFieldType">
UPDATE FormFieldType
SET DisplayOrder = DisplayOrder+1
WHERE DisplayOrder >= 7;

IF NOT EXISTS (SELECT * FROM FormFieldType WHERE FieldTypeID = 19)
BEGIN
	INSERT INTO FormFieldType
           (FieldTypeID
           ,FieldName
           ,FieldDesc
           ,DisplayOrder
           ,Options
           ,Scorable
           ,ResponseTable)
    VALUES
           (19
           ,'all staff'
           ,'A drop down list of all active intranet users'
           ,7
           ,1
           ,0
           ,'String');
           
    INSERT INTO FormSectionFieldType
    (FormTypeID, SectionTypeID, FieldTypeID)
    VALUES
    (1,1,19);

    INSERT INTO FormSectionFieldType
    (FormTypeID, SectionTypeID, FieldTypeID)
    VALUES
    (1,2,19);

    INSERT INTO FormSectionFieldType
    (FormTypeID, SectionTypeID, FieldTypeID)
    VALUES
    (2,1,19);
END

</cfquery>

<!--- Case RR-2141 - Adding a field to FormResponse Table to control deleted responses --->
<cfquery datasource="#application.config.DSN#" name="addFormResponseFieldType">
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'FormResponse' AND COLUMN_NAME = 'DeletedBy')
	BEGIN
		ALTER TABLE FormResponse ADD
			DeletedBy int NULL
	END
</cfquery>

<cfquery datasource="#application.config.DSN#" name="alterSpFormSingleResponseData">
ALTER PROCEDURE [spFormSingleResponseData] 
		@FormID INT, 
		@FormResponseID INT,
		@Export BIT
	AS
	BEGIN
		-- declare variables needed for procedure
		DECLARE @DynamicSQL NVARCHAR(MAX), @Fields NVARCHAR(MAX), @PivotFields NVARCHAR(MAX), @sFormID NVARCHAR(12), @sFormResponseID NVARCHAR(12)
		DECLARE @ColumnList NVARCHAR(MAX)
		DECLARE @Scoring BIT, @Approval BIT
		SELECT @sFormID = CAST(@FormID AS NVARCHAR(12))
		SELECT @sFormResponseID = CAST(@FormResponseID AS NVARCHAR(12))
		SELECT @Fields = ''
		
		-- 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
		
		SELECT	@Fields = @Fields + 'fr.ResponseNumber, fr.FormID, fr.FormResponseID, fr.UserID, fr.EditUserID, fr.WorkflowSetIDInstance, fr.Draft, '
		SELECT	@Fields = @Fields + '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 + 'CASE WHEN p3.FirstName IS NULL THEN '''' ELSE p3.FirstName + '' '' + p3.LastName END AS DeletedByName, fr.DeletedBy '
		
		IF @Export = 0
		BEGIN
			SELECT	@Fields = @Fields + ', fr.PossibleScore, fr.Score, w.ApprovalStatus, w.FinalApprovalDate'
			SELECT	@PivotFields = 'ff.FieldName AS _Name_, CASE 
							WHEN ft.Options = 1 
								THEN ''{
									"options": "'' + frf.SelectedOptions + ''", 
									"value": "'' + REPLACE(REPLACE(v.Value, CHAR(34), ''&quot;''), CHAR(92), ''\\'') + ''"
								}''
							WHEN ft.FieldTypeID = 16 
								THEN ''{
									"locationA": "'' + REPLACE(frf.LocationA, CHAR(34), ''&quot;'') + ''",
									"locationAbbrevA": "'' + REPLACE(frf.LocationAbbrevA, CHAR(34), ''&quot;'') + ''",
									"locationB": "'' + REPLACE(frf.LocationB, CHAR(34), ''&quot;'') + ''",
									"locationAbbrevB": "'' + REPLACE(frf.LocationAbbrevB, CHAR(34), ''&quot;'') + ''", 
									"roundTrip": '' + CASE WHEN frf.RoundTrip = 0 THEN ''0'' ELSE ''1'' END + '', 
									"value": "'' + v.Value + ''"
								}''
							ELSE v.Value 
						END AS _Value_'
		END


		ELSE
		BEGIN
			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 '''''', ''''), ''''''}'', '''')
							WHEN ft.FieldTypeID = 19 
								THEN REPLACE(v.Value,v.Value, (SELECT TOP 1 p.FirstName + '' '' + p.LastName + '' ['' + up.Username + '']'' FROM Person p INNER JOIN UserProfile up on p.PersonID = up.PersonID WHERE up.UserID = v.Value))
							ELSE v.Value
						END AS _Value_'
		END

		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 FormResponse tr ON frf.FormID = tr.FormID
										AND tr.FormResponseID = frf.FormResponseID
					WHERE		ff2.FormID = @FormID
									AND tr.FormResponseID = @FormResponseID
									AND ff2.InSpreadsheet = 0
									AND ff2.Deleted = 0
				)
		SET @ColumnList = SUBSTRING(@ColumnList, 3, LEN(@ColumnList))
		
		-- build dynamic sql to get response data
		IF @ColumnList = ''
		BEGIN
			-- no dynamic response fields, simple query
			SELECT @DynamicSQL = '
				SELECT		' + @Fields + '
				FROM		FormResponse fr
								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 
								LEFT JOIN UserProfile u3 ON fr.DeletedBy = u3.UserID
								LEFT JOIN Person p3 ON u3.PersonID = p3.PersonID 
				WHERE		fr.FormID = ' + @sFormID + '
								AND fr.FormResponseID = ' + @sFormResponseID
		END
		ELSE
		BEGIN
			-- perform pivot of response data
			SELECT @DynamicSQL = '
				SELECT	* 
				FROM	(
							SELECT	' + @Fields + ', ' + @PivotFields + '
							FROM	FormResponse fr
										INNER JOIN FormResponse 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
										LEFT JOIN UserProfile u3 ON fr.DeletedBy = u3.UserID
										LEFT JOIN Person p3 ON u3.PersonID = p3.PersonID
							WHERE	fr.FormID = ' + @sFormID + '
										AND tr.FormResponseID = ' + @sFormResponseID + '
									AND ff.Deleted = 0
									AND v.RowNo = 0
						) AS SourceTable
						PIVOT (MAX(SourceTable._Value_) FOR SourceTable._Name_ IN (' + @ColumnList + ')) AS PivotTable
				ORDER BY	1'
		END			
		
	
		-- select response data based on dynamic sql
		EXEC (@DynamicSQL)
		
	END
</cfquery>

<cfquery datasource="#application.config.DSN#" name="alterSpFormResponseSpreadsheetData">
ALTER PROCEDURE [spFormResponseSpreadsheetData] @FormID INT, @ResponseID INT, @SectionID INT, @Export BIT
AS
BEGIN
	-- declare variables needed for procedure
	DECLARE @DynamicSQL NVARCHAR(MAX), @Fields NVARCHAR(MAX), @PivotFields NVARCHAR(MAX), @ColumnList NVARCHAR(MAX), @sFormID VARCHAR(12)
	DECLARE @FormFieldID INT, @MaxRowNo SMALLINT, @RowNo SMALLINT
	SELECT @sFormID = CAST(@FormID AS VARCHAR(12))
				
	-- combine form ID with response ID and Row Nos in a temporary table to perform join on values
	IF OBJECT_ID('tempdb..##tblFormResponseRows') IS NULL
	BEGIN
		CREATE TABLE ##tblFormResponseRows (FormID INT, FormResponseID INT, RowNo SMALLINT)
		SELECT	@MaxRowNo = MAX(frf.RowNo)
		FROM	FormResponseField frf
					INNER JOIN FormField f ON frf.FormFieldID = f.FormFieldID AND f.FormSectionID = @SectionID
		WHERE	frf.FormID = @FormID
				AND frf.FormResponseID = @ResponseID
		SELECT	@RowNo = 0 
		WHILE	@RowNo < @MaxRowNo
		BEGIN
			SELECT	@RowNo = @RowNo + 1
			INSERT	##tblFormResponseRows SELECT @FormID, @ResponseID, @RowNo
		END
	END
				
	-- determine column values & labels based on export flag
	IF @Export = 1
	BEGIN
		SELECT	@Fields = 'tr.RowNo AS Row'
		SELECT	@PivotFields = 'ff.FieldName AS _Name_, 
					CASE
						WHEN ft.FieldTypeID = 8 
							THEN REPLACE(REPLACE(v.Value, ''{ts '''''', ''''), ''''''}'', '''')
						WHEN ft.FieldTypeID = 19 
							THEN REPLACE(v.Value,v.Value, (SELECT TOP 1 p.FirstName + '' '' + p.LastName + '' ['' + up.Username + '']'' FROM Person p INNER JOIN UserProfile up on p.PersonID = up.PersonID WHERE up.UserID = v.Value))
						ELSE v.Value 
					END AS _Value_'
	END
	ELSE
	BEGIN
		SELECT	@Fields = 'tr.RowNo'
		SELECT	@PivotFields = 'ff.FieldName AS _Name_, CASE 
					WHEN ft.Options = 1 
						THEN ''{
							"options": "'' + frf.SelectedOptions + ''", 
							"value": "'' + REPLACE(REPLACE(v.Value, CHAR(34), ''&quot;''), CHAR(92), ''\\'') + ''"
						}''
					WHEN ft.FieldTypeID = 16 
						THEN ''{
							"locationA": "'' + REPLACE(frf.LocationA, CHAR(34), ''&quot;'') + ''",
							"locationAbbrevA": "'' + REPLACE(frf.LocationAbbrevA, CHAR(34), ''&quot;'') + ''",
							"locationB": "'' + REPLACE(frf.LocationB, CHAR(34), ''&quot;'') + ''",
							"locationAbbrevB": "'' + REPLACE(frf.LocationAbbrevB, CHAR(34), ''&quot;'') + ''", 
							"roundTrip": '' + CASE WHEN frf.RoundTrip = 0 THEN ''0'' ELSE ''1'' END + '', 
							"value": "'' + v.Value + ''"
						}''
					ELSE v.Value 
				END AS _Value_'
	END
				
	-- build a column list of spreadsheet fields for the given form
	SELECT	@ColumnList = ''
	SELECT	@ColumnList = @ColumnList + ', [' + FieldName + ']'
	FROM	FormField ff
	WHERE	ff.FieldName IN (
				SELECT		DISTINCT(ff2.FieldName)
				FROM		FormField ff2
								INNER JOIN FormResponseField frf ON ff2.FormFieldID = frf.FormFieldID
									AND frf.FormID = @FormID
								INNER JOIN ##tblFormResponseRows tr ON frf.FormID = tr.FormID
									AND tr.FormResponseID = frf.FormResponseID
				WHERE		ff2.FormID = @FormID
								AND ff2.FormSectionID = @SectionID
								AND ff2.Deleted = 0
			)
	SET @ColumnList = SUBSTRING(@ColumnList, 3, LEN(@ColumnList))
			
	-- build dynamic sql to get response data
	IF @ColumnList = ''
	BEGIN
		-- no response fields, simple query
		SELECT @DynamicSQL = '
			SELECT	' + @Fields + '
			FROM	Form f
						INNER JOIN FormResponse fr ON f.FormID = fr.FormID
							AND fr.FormID = ' + @sFormID + '
						INNER JOIN ##tblFormResponseRows tr ON fr.FormID = tr.FormID
							AND fr.FormResponseID = tr.FormResponseID'
			+ ' ORDER BY	1'
	END
	ELSE
	BEGIN
		-- perform pivot of response data
		SELECT @DynamicSQL = '
			SELECT	* 
			FROM	(
						SELECT	' + @Fields + ', ' + @PivotFields + '
						FROM	Form f
									INNER JOIN FormResponse fr ON f.FormID = fr.FormID
										AND fr.FormID = ' + @sFormID + '
									INNER JOIN FormField ff ON f.FormID = ff.FormID
										AND ff.FormID = ' + @sFormID + '
									INNER JOIN FormFieldType ft ON ff.FieldTypeID = ft.FieldTypeID
									INNER JOIN FormResponseField frf ON f.FormID = frf.FormID
										AND fr.FormResponseID = frf.FormResponseID
										AND ff.FormFieldID = frf.FormFieldID
										AND frf.FormID = ' + @sFormID + '
									INNER JOIN FormResponseFieldValue v ON fr.FormID = v.FormID 
										AND fr.FormResponseID = v.FormResponseID
										AND ff.FormFieldID = v.FormFieldID
										AND frf.RowNo = v.RowNo
										AND v.FormID = ' + @sFormID + '
									INNER JOIN ##tblFormResponseRows tr ON v.FormID = tr.FormID
										AND v.FormResponseID = tr.FormResponseID
										AND v.RowNo = tr.RowNo
										AND tr.FormID = ' + @sFormID + '
						WHERE	ff.Deleted = 0
								AND v.RowNo <> 0
					) AS SourceTable
					PIVOT (MAX(SourceTable._Value_) FOR SourceTable._Name_ IN (' + @ColumnList + ')) AS PivotTable'
			+ ' ORDER BY	1'
	END
			
	-- select response data based on dynamic sql
	EXEC (@DynamicSQL)
				
	-- drop temporary table
	DROP TABLE ##tblFormResponseRows
END
</cfquery>

<cfquery datasource="#application.config.DSN#" name="alterSpFormResponseData">
ALTER 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

		-- building 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		
				
		-- 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 + 'CASE WHEN p3.FirstName IS NULL THEN '''' ELSE p3.FirstName + '' '' + p3.LastName END AS DeletedBy, '
			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 '''''', ''''), ''''''}'', '''')
							WHEN ft.FieldTypeID = 19
								THEN REPLACE(v.Value,v.Value, (SELECT TOP 1 p.FirstName + '' '' + p.LastName + '' ['' + up.Username + '']'' FROM Person p INNER JOIN UserProfile up on p.PersonID = up.PersonID WHERE up.UserID = v.Value))
							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 + 'CASE WHEN p3.FirstName IS NULL THEN '''' ELSE p3.FirstName + '' '' + p3.LastName END AS DeletedByName, fr.DeletedBy, '
			SELECT	@Fields = @Fields + 'w.ApprovalStatus, w.FinalApprovalDate'
			SELECT	@PivotFields = 'ff.FieldName AS _Name_, 
						CASE
							WHEN ft.FieldTypeID = 19
								THEN REPLACE(v.Value,v.Value, (SELECT p.FirstName + '' '' + p.LastName + '' ['' + up.Username + '']'' FROM Person p INNER JOIN UserProfile up on p.PersonID = up.PersonID WHERE up.UserID = v.Value))
							ELSE v.Value 
						END 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 
								LEFT JOIN UserProfile u3 ON fr.DeletedBy = u3.UserID
								LEFT JOIN Person p3 ON u3.PersonID = p3.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
										LEFT JOIN UserProfile u3 ON fr.DeletedBy = u3.UserID
										LEFT JOIN Person p3 ON u3.PersonID = p3.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>
</cftransaction>