<cfif form.Type EQ "Patch">
	<cfinclude template="patch_16.5.01.cfm">
</cfif>

<!--- AZ-4251 Add UserNameMapping Field to ADConnection --->
<cftransaction>
    <cfquery name="UpdateDatabaseSchema" datasource="#application.config.dsn#">
        IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ADConnection' 
        AND COLUMN_NAME = 'UserNameMapping')
        BEGIN
            ALTER TABLE ADConnection
            ADD UserNameMapping VARCHAR(20) NULL DEFAULT ('defaultUsername') WITH VALUES;
        END;
    </cfquery>
</cftransaction> 
    
<!--- Case AZ-4095 - Block ability to uplaod .csv by default to avoid CSV Injection vulnerability --->
<cftransaction>
    <cfquery datasource="#application.config.DSN#" name="blockedExtensions">
        IF NOT EXISTS (SELECT * FROM AppSetting WHERE Name = 'BLOCKEDEXTFORFILEUPLOAD')
        BEGIN
            INSERT INTO [dbo].[AppSetting] ([Name], [Value])
            VALUES ('BLOCKEDEXTFORFILEUPLOAD', 'asp,aspx,cfc,cfm,cfml,do,htm,html,jsp,jspx,php,csv,exe,bat,ps1,psm1,psd1,com,cmd,sh,bash,class,jar,msi,msp,gadget,dll,ocx,zsh,ksh,rb,erb,py,pyc,pyo,pyw.htaccess,env,config')
        END
        ELSE
        BEGIN
            UPDATE [dbo].[AppSetting]
            SET [Value] = 'asp,aspx,cfc,cfm,cfml,do,htm,html,jsp,jspx,php,csv,exe,bat,ps1,psm1,psd1,com,cmd,sh,bash,class,jar,msi,msp,gadget,dll,ocx,zsh,ksh,rb,erb,py,pyc,pyo,pyw.htaccess,env,config'
            WHERE [Name] = 'BLOCKEDEXTFORFILEUPLOAD'
        END

    </cfquery>
</cftransaction>

<!--- AZ-4252 Add WITH (NOLOCK) to spFormResponseData --->
<cftransaction>
    <cfquery datasource="#application.config.DSN#">
        IF OBJECT_ID(N'spFormResponseData', N'P') IS NOT NULL
        DROP PROC [spFormResponseData];
    </cfquery>
</cftransaction>


<cftransaction>

    <cfquery name="modifySpFormResponseData" 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
            SET NOCOUNT ON;

            -- 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 WITH (NOLOCK) 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 WITH (NOLOCK) 
                        INNER JOIN FormType ft WITH (NOLOCK) 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 WITH (NOLOCK) 
                        LEFT JOIN WorkflowSet ws WITH (NOLOCK) ON f.WorkflowSetIDTemplate = ws.WorkflowSetID
                        LEFT JOIN Workflow w WITH (NOLOCK) ON ws.WorkflowSetID = w.WorkflowSetID AND w.ParentID IS NULL
                        LEFT JOIN WorkflowStage s WITH (NOLOCK) 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 WITH (NOLOCK) 
                                        INNER JOIN ##tblFormResponseID tr ON fr.FormID = tr.FormID
                                            AND fr.FormResponseID = tr.FormResponseID
                                        INNER JOIN FormResponseField' + @FieldType + ' v WITH (NOLOCK) 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 WITH (NOLOCK) 
                                        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 WITH (NOLOCK) 
                        INNER JOIN FormFieldType t WITH (NOLOCK) 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 WITH (NOLOCK) 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 WITH 
                                    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 WITH (NOLOCK) 
                                        INNER JOIN ##tblFormResponseID tr WITH (NOLOCK) ON fr.FormID = tr.FormID
                                            AND fr.FormResponseID = tr.FormResponseID
                                        LEFT JOIN UserProfile u WITH (NOLOCK) ON fr.UserID = u.UserID
                                            LEFT JOIN Person p WITH (NOLOCK) 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 WITH (NOLOCK) INNER JOIN UserProfile up WITH (NOLOCK) 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 WITH (NOLOCK) INNER JOIN UserProfile up WITH (NOLOCK) 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 WITH (NOLOCK) 
                WHERE	ff.FormID = @FormID
                        AND ff.FieldName IN (
                            SELECT		DISTINCT(ff2.FieldName)
                            FROM		FormField ff2 WITH (NOLOCK) 
                                            INNER JOIN FormResponseField frf WITH (NOLOCK) 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 WITH (NOLOCK) 
                                    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 WITH (NOLOCK) ON fr.UserID = u.UserID
                                    LEFT JOIN Person p WITH (NOLOCK) ON u.PersonID = p.PersonID 
                                    LEFT JOIN UserProfile u2 WITH (NOLOCK) ON fr.EditUserID = u2.UserID
                                    LEFT JOIN Person p2 WITH (NOLOCK) ON u2.PersonID = p2.PersonID 
                                    LEFT JOIN UserProfile u3 WITH (NOLOCK) ON fr.DeletedBy = u3.UserID
                                    LEFT JOIN Person p3 WITH (NOLOCK) 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 WITH (NOLOCK) 
                                            INNER JOIN ##tblFormResponseID tr ON fr.FormID = tr.FormID
                                                AND fr.FormResponseID = tr.FormResponseID
                                            INNER JOIN FormResponseField frf WITH (NOLOCK) ON tr.FormID = frf.FormID
                                                AND tr.FormResponseID = frf.FormResponseID
                                            INNER JOIN FormResponseFieldValue v WITH (NOLOCK) ON frf.FormID = v.FormID 
                                                AND frf.FormResponseID = v.FormResponseID
                                                AND frf.FormFieldID = v.FormFieldID
                                            INNER JOIN FormField ff WITH (NOLOCK) ON frf.FormID = ff.FormID
                                                AND frf.FormFieldID = ff.FormFieldID
                                            INNER JOIN FormFieldType ft WITH (NOLOCK) ON ff.FieldTypeID = ft.FieldTypeID
                                            LEFT JOIN Workflow w WITH (NOLOCK) ON fr.WorkflowSetIDInstance = w.WorkflowSetID
                                                AND w.CurrentWorkflow = 1
                                            LEFT JOIN UserProfile u WITH (NOLOCK) ON fr.UserID = u.UserID
                                            LEFT JOIN Person p WITH (NOLOCK) ON u.PersonID = p.PersonID
                                            LEFT JOIN UserProfile u2 WITH (NOLOCK) ON fr.EditUserID = u2.UserID
                                            LEFT JOIN Person p2 WITH (NOLOCK) ON u2.PersonID = p2.PersonID
                                            LEFT JOIN UserProfile u3 WITH (NOLOCK) ON fr.DeletedBy = u3.UserID
                                            LEFT JOIN Person p3 WITH (NOLOCK) 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> 

<!--- AZ-4252 Add WITH (NOLOCK) to spFormResponseSpreadsheetData --->
<cfquery datasource="#application.config.DSN#">
    IF OBJECT_ID(N'spFormResponseSpreadsheetData', N'P') IS NOT NULL
    DROP PROC [spFormResponseSpreadsheetData];
</cfquery>

<cftransaction>
    <cfquery name="modifySpFormResponseSpreadsheetData" datasource="#application.config.dsn#">
        CREATE PROCEDURE spFormResponseSpreadsheetData
            @FormID INT, @ResponseID INT, @SectionID INT, @Export BIT
        AS
        BEGIN
            SET NOCOUNT ON;

            -- 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 WITH (NOLOCK)
                            INNER JOIN FormField f WITH (NOLOCK) 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 WITH (NOLOCK) INNER JOIN UserProfile up WITH (NOLOCK) 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 WITH (NOLOCK)
            WHERE	ff.FieldName IN (
                        SELECT		DISTINCT(ff2.FieldName)
                        FROM		FormField ff2 WITH (NOLOCK)
                                        INNER JOIN FormResponseField frf WITH (NOLOCK) ON ff2.FormFieldID = frf.FormFieldID
                                            AND frf.FormID = @FormID
                                        INNER JOIN ##tblFormResponseRows tr WITH (NOLOCK) 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 WITH (NOLOCK)
                                INNER JOIN FormResponse fr WITH (NOLOCK) ON f.FormID = fr.FormID
                                    AND fr.FormID = ' + @sFormID + '
                                INNER JOIN ##tblFormResponseRows tr WITH (NOLOCK) 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 WITH (NOLOCK)
                                            INNER JOIN FormResponse fr WITH (NOLOCK) ON f.FormID = fr.FormID
                                                AND fr.FormID = ' + @sFormID + '
                                            INNER JOIN FormField ff WITH (NOLOCK) ON f.FormID = ff.FormID
                                                AND ff.FormID = ' + @sFormID + '
                                            INNER JOIN FormFieldType ft WITH (NOLOCK) ON ff.FieldTypeID = ft.FieldTypeID
                                            INNER JOIN FormResponseField frf WITH (NOLOCK) ON f.FormID = frf.FormID
                                                AND fr.FormResponseID = frf.FormResponseID
                                                AND ff.FormFieldID = frf.FormFieldID
                                                AND frf.FormID = ' + @sFormID + '
                                            INNER JOIN FormResponseFieldValue v WITH (NOLOCK) 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 WITH (NOLOCK) 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> 
</cftransaction> 

<!--- AZ-4336: Allow custom fields to be enabled on support desk emails if chosen by the user. 
                This will get the list of current support desk apps within the application and add the new DisplayOnEmail field for each SDFormFields table. --->
<cftransaction> 
    <cfquery name="retrieveFormFieldTables" datasource="#application.config.dsn#"> 
        SELECT TableName4
        FROM   APPLICATION 
        WHERE  AppTypeID = 27
    </cfquery> 

    <cfloop index="ffIndex" from="1" to="#retrieveFormFieldTables.RecordCount#"> 
        <cfquery name="addNewFFColumn" datasource="#application.config.dsn#"> 
            IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '#retrieveFormFieldTables["TableName4"][ffIndex]#' AND COLUMN_NAME = 'DisplayOnEmail')
            BEGIN
                ALTER TABLE #retrieveFormFieldTables["TableName4"][ffIndex]#
                ADD DisplayOnEmail BIT DEFAULT 0; 
            END
        </cfquery>

        <!--- As a fun quirk of SQL the default doesn't play nice setting existing records DisplayOnEmail to be 0 so we still need to update it.--->
        <cfquery name="setDisplayOnEmailValues" datasource="#application.config.dsn#"> 
            UPDATE #retrieveFormFieldTables["TableName4"][ffIndex]#
            SET DisplayOnEmail = 0
            WHERE DisplayOnEmail IS NULL;
        </cfquery>
    </cfloop>
</cftransaction>

<!--- AZ-4517: Add in table valued parameter to workaround SQLs 2100 parameter limit in large queries and improve performance. --->
<cftransaction> 
    <cfquery name="createUserIDListTVP" datasource="#application.config.dsn#"> 
        IF NOT EXISTS (SELECT * FROM sys.types WHERE name = 'UserIDList' AND is_table_type = 1)
        BEGIN
            CREATE TYPE dbo.UserIDList AS TABLE (UserID INT);
        END
    </cfquery>
</cftransaction> 