<!--- Bug 6377 - drop the TinyURL CategoryID column if exists --->
<cfquery name="trainingApps" datasource="#application.config.DSN#">
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TinyURL' AND COLUMN_NAME = 'CategoryID')
BEGIN
	ALTER TABLE TinyURL DROP COLUMN CategoryID;
END
</cfquery>

<!--- Bug 6527 - create a store procedure to get the information for the summary view --->
<cfquery datasource="#application.config.DSN#">
	IF OBJECT_ID(N'spFormResponseDataSummary', N'P') IS NOT NULL
	DROP PROC spFormResponseDataSummary
</cfquery>
<cfquery datasource="#application.config.DSN#">
	CREATE PROCEDURE [spFormResponseDataSummary] 
		@FormID INT, 
		@OrderBy NVARCHAR(100) = 'ResponseNumber DESC', 
		@ColumnList NVARCHAR(MAX) = NULL
	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 @ConditionID INT
		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
		
		
		
		-- 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
				
		-- determine column values & labels based on export flag
		IF @Fields IS NULL
			SELECT @Fields = ''
		
		
		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_, 
					CASE
						WHEN ft.Options = 1 
							THEN ''{
								"options": "'' + frf.SelectedOptions + ''", 
								"value": "'' + REPLACE(REPLACE(v.Value, CHAR(34), ''&quot;''), CHAR(92), ''\\'') + ''"
							}''
						ELSE v.Value 
					END AS _Value_'
		
		
		-- build a column list of fields to pivot for the given form response(s) (if not passed in)
		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))
		
		
	
		-- 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 6543 - there was a mistake on the sproc for single response that caused an error if the form didn't have any fields other than spreadsheet --->
<cfquery datasource="#application.config.DSN#">
	IF OBJECT_ID(N'spFormSingleResponseData', N'P') IS NOT NULL
	DROP PROC spFormSingleResponseData
</cfquery>
<cfquery datasource="#application.config.DSN#">
CREATE 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 '
		
		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 '''''', ''''), ''''''}'', '''')
							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 
				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
							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>

<!--- Bug 6551 - recreate the sproc to change LEFT JOIN to INNER JOIN on the filtering of responses based on the filter criteria entered by the user when there is pagination on the responses --->
<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, '
			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>