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

<!--- AZ-5634 Add SiteBackgroundStatic field to Themes table ---> 
<cftransaction>
    <cfquery name="addSiteBackgroundStatic" datasource="#application.config.dsn#">
        IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Themes' 
        AND COLUMN_NAME = 'SiteBackgroundStatic')
        BEGIN
            ALTER TABLE Themes
            ADD SiteBackgroundStatic BIT NOT NULL DEFAULT (0);
        END;
    </cfquery>
</cftransaction>

<!--- AZ-5545 Fix issue in spFormResponseData causing us to not be able to sort custom columns. --->
<!--- AZ-5620 Update spFormResponseData to handle sorting the Status column. --->
<cftransaction>
    <cfquery name="updateSpFormResponseData" datasource="#application.config.DSN#">
        ALTER PROCEDURE [dbo].[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(50), @FieldType NVARCHAR(10), @Value NVARCHAR(MAX), @Operator NVARCHAR(10), @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](50) NOT NULL,
                    FieldType [NVARCHAR](10) NOT NULL,
                    Value [NVARCHAR](MAX) NOT NULL,
                    Operator [NVARCHAR](10) 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
                    -- Handle special fields that require joins to other tables
                    IF @FieldName = 'ApprovalStatus'
                    BEGIN
                        -- Special case for ApprovalStatus (from Workflow 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
                                            LEFT JOIN Workflow w WITH (NOLOCK) ON fr.WorkflowSetIDInstance = w.WorkflowSetID 
                                                AND w.CurrentWorkflow = 1
                                WHERE    fr.FormID = ' + @sFormID + '
                                        AND w.ApprovalStatus ' + @Operator + ' ''' + @Value + '''
                            )'
                        EXEC (@DynamicSQL)
                    END
                    ELSE IF @FieldName = 'WorkflowSetIDInstance' AND (@Operator = 'IS NULL' OR (@Operator = 'IS' AND @Value = 'NULL'))
                    BEGIN
                        -- Special case for checking responses with no workflow - allow both formats of IS NULL
                        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.FormID = ' + @sFormID + '
                                        AND fr.WorkflowSetIDInstance IS NULL
                            )'
                        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
                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
                -- Remove both $ and , before casting to decimal for sorting
			    SELECT @OrderBy = 'CAST(REPLACE(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). AZ-5545 fixes issue with (NOLOCK)
                    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
                                    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
                                    WHEN @FieldName = 'ApprovalStatus'
                                        THEN '
                                            CASE 
                                                WHEN w.ApprovalStatus = ''r'' THEN 5
                                                WHEN w.ApprovalStatus = ''d'' THEN 4
                                                WHEN w.ApprovalStatus = ''p'' THEN 3
                                                WHEN w.ApprovalStatus = ''a'' THEN 2
                                                WHEN w.ApprovalStatus IS NULL THEN 1
                                                ELSE 0
                                            END' + @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 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
                            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 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
            
            -- 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    ' + 
                    CASE 
                        WHEN @FieldName = 'ApprovalStatus'
                            THEN 
                                CASE 
                                    WHEN @Descending = ' DESC' THEN '
                                        CASE 
                                            WHEN w.ApprovalStatus = ''r'' THEN 5
                                            WHEN w.ApprovalStatus = ''d'' THEN 4
                                            WHEN w.ApprovalStatus = ''p'' THEN 3
                                            WHEN w.ApprovalStatus = ''a'' THEN 2
                                            WHEN w.ApprovalStatus IS NULL THEN 1
                                            ELSE 0
                                        END' + @Descending
                                    ELSE '
                                        CASE 
                                            WHEN w.ApprovalStatus IS NULL THEN 1
                                            WHEN w.ApprovalStatus = ''a'' THEN 2
                                            WHEN w.ApprovalStatus = ''p'' THEN 3
                                            WHEN w.ApprovalStatus = ''d'' THEN 4
                                            WHEN w.ApprovalStatus = ''r'' THEN 5
                                            ELSE 6
                                        END' + @Descending
                                END
                        ELSE @OrderBy
                    END
            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    ' + 
                    CASE 
                        WHEN @OrderBy = 'ApprovalStatus' OR @OrderBy = 'ApprovalStatus DESC' OR @OrderBy = 'ApprovalStatus ASC'
                            THEN 
                                CASE 
                                    WHEN @Descending = ' DESC' THEN '
                                        CASE 
                                            WHEN ApprovalStatus = ''r'' THEN 5
                                            WHEN ApprovalStatus = ''d'' THEN 4
                                            WHEN ApprovalStatus = ''p'' THEN 3
                                            WHEN ApprovalStatus = ''a'' THEN 2
                                            WHEN ApprovalStatus IS NULL THEN 1
                                            ELSE 0
                                        END' + @Descending
                                    ELSE '
                                        CASE 
                                            WHEN ApprovalStatus IS NULL THEN 1
                                            WHEN ApprovalStatus = ''a'' THEN 2
                                            WHEN ApprovalStatus = ''p'' THEN 3
                                            WHEN ApprovalStatus = ''d'' THEN 4
                                            WHEN ApprovalStatus = ''r'' THEN 5
                                            ELSE 6
                                        END' + @Descending
                                END
                        ELSE @OrderBy
                    END
            END
        
            -- select response data based on dynamic sql
            EXEC (@DynamicSQL)

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


<!--- AZ-5620 Add in new columns for default sort settings for form response table. ---> 
<cftransaction>

    <!--- Add default sort field column (stores the field to sort by) --->
    <cfquery name="addDefaultSortField" datasource="#application.config.dsn#">
        IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Form' 
        AND COLUMN_NAME = 'DefaultSortField')
        BEGIN
            ALTER TABLE Form
            ADD DefaultSortField NVARCHAR(50) NULL;
        END;
    </cfquery>
    
    <!--- Add default sort direction column, stores 1=ascending/0=descending. 
            (for some reason that's just how we do it in Forms...) 
    --->
    <cfquery name="addDefaultSortDirection" datasource="#application.config.dsn#">
        IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Form' 
        AND COLUMN_NAME = 'DefaultSortDirection')
        BEGIN
            ALTER TABLE Form
            ADD DefaultSortDirection BIT NULL;
        END;
    </cfquery>
</cftransaction>

<!--- AZ-5619: Add new field for option to hide status column in form response table. --->
<cftransaction>
    <cfquery datasource="#application.config.DSN#" name="addHideFormStatusColumn">
        IF NOT EXISTS (SELECT * FROM AppSetting WHERE Name = 'GlobalHideFormStatusColumn')
        BEGIN
            INSERT INTO [dbo].[AppSetting] ([Name], [Value])
            VALUES ('GlobalHideFormStatusColumn', '0')
        END
    </cfquery>

    <cfquery name="addHideStatus" datasource="#application.config.dsn#">
        IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Form' 
        AND COLUMN_NAME = 'HideStatus')
        BEGIN
            ALTER TABLE Form
            ADD HideStatus BIT NOT NULL DEFAULT (0);
        END;
    </cfquery>
</cftransaction>



<!--- AZ-4798 Add the Product Health Lucee Scheduler Item.  --->
<cfscript>
    //check it exists firsts by looping over the taskList
    var taskList = "";
    schedule action="list" returnvariable="taskList";
    var bFoundScheduledItem = false;
    for (task in taskList) {
        if(StructKeyExists(task, 'name')){
            if (ListLast(task.name,"_") == "PRODUCTHEALTH") {
                bFoundScheduledItem = true;
            }
        }
    }
    //if it doesn't exist we create it
    if (bFoundScheduledItem == false) {
        var startTime = "12:45 AM";
        var sTaskName = application.config.appSettingsRepository.readSetting('COOKIENAME') & "_PRODUCTHEALTH";
        var sIntranetRoot = application.config.appSettingsRepository.readSetting('IntranetRoot');
        var sTaskUrl = sIntranetRoot & "/Scheduled/producthealth.cfm";
        var interval = 604800; // 7 days ... this only needs to run once a week.
        var port = cgi.server_port;
        schedule operation="HTTPRequest"
                action="update"
                task=sTaskname
                URL=sTaskUrl
                startDate=DateFormat(DateAdd("d",-1,now()),"M/d/YY")
                startTime=startTime
                interval=interval
                port=port
                resolveURL=true;
    }
</cfscript>


<!--- Feature AZ-4798 - Product Health --->
<!--- AZ-4798 Add the Process Cleanup Action Queue Lucee Scheduler Item.  --->
<cfscript>
    //check it exists firsts by looping over the taskList
    var taskList = "";
    schedule action="list" returnvariable="taskList";
    var bFoundScheduledItem = false;
    for (task in taskList) {
        if(StructKeyExists(task, 'name')){
            if (ListLast(task.name,"_") == "PROCESSCAQ") {
                bFoundScheduledItem = true;
            }
        }
    }
    //if it doesn't exist we create it
    if (bFoundScheduledItem == false) {
        var startTime = "9:00 PM";
        var sTaskName = application.config.appSettingsRepository.readSetting('COOKIENAME') & "_PROCESSCAQ";
        var sIntranetRoot = application.config.appSettingsRepository.readSetting('IntranetRoot');
        var sTaskUrl = sIntranetRoot & "/Scheduled/processcaq.cfm";
        var interval = 86400; // 24 hrs  ... this only needs to run once a day.
        var port = cgi.server_port;
        schedule operation="HTTPRequest"
                action="update"
                task=sTaskname
                URL=sTaskUrl
                startDate=DateFormat(DateAdd("d",-1,now()),"M/d/YY")
                startTime=startTime
                interval=interval
                port=port
                resolveURL=true;
    }
    
</cfscript>


<!--- Feature AZ-4798 - Product Health --->

    <!--- Create HealthNoViews table AZ-4798 --->
    <cfquery name="createHealthNoViews" datasource="#application.config.dsn#">
        IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'HealthNoViews')
        BEGIN
            CREATE TABLE HealthNoViews
            (
                ID INT IDENTITY(1,1) PRIMARY KEY,
                AppID INT NOT NULL,
                ItemID INT NOT NULL,
                AppTable VARCHAR(100) NOT NULL,
                UserID INT NULL,
                EditUserID INT NULL,
                DateEntered DATETIME NULL,
                DateModified DATETIME NULL,
                ItemLocation VARCHAR(2000) NULL,
                Title VARCHAR(1000) NULL,
                ActionQueued VARCHAR(2) NULL
            )
        END
    </cfquery>

<!--- Feature AZ-4798 - Product Health --->

    <!--- Create HealthDisabledUsers table AZ-4798 --->
    <cfquery name="createHealthDisabledUsers" datasource="#application.config.dsn#">
        IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'HealthDisabledUsers')
        BEGIN
            CREATE TABLE HealthDisabledUsers
            (
                ID INT IDENTITY(1,1) PRIMARY KEY,
                AppID INT NOT NULL,
                ItemID INT NOT NULL,
                UserID INT NOT NULL,
                Title VARCHAR(1000) NULL,
                AppTable VARCHAR(100) NULL,
                ItemLocation VARCHAR(2000) NULL,
                DateModified DATETIME NULL,
                DateEntered DATETIME NULL,
                EditUserID INT NULL,
                ActionQueued VARCHAR(2) NULL
            )
        END
    </cfquery>

    <!--- Create HealthMetrics table AZ-4798 --->
    <cfquery name="createHealthMetrics" datasource="#application.config.dsn#">
        IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'HealthMetrics')
        BEGIN
            CREATE TABLE HealthMetrics
            (
                ID INT PRIMARY KEY,
                Title VARCHAR(200) NOT NULL,
                Metric VARCHAR(100) NOT NULL,
                Description VARCHAR(1000) NULL,
                MetricValue INT NOT NULL DEFAULT 0,
                Status VARCHAR(20) NOT NULL,
                ButtonText VARCHAR(50) NULL,
                ButtonURL VARCHAR(100) NULL,
                ThresholdToBad INT NULL DEFAULT 0
            )
        END
    </cfquery>

    <!--- Check if table is empty or not --->
    <cfquery name="checkHealthMetrics" datasource="#application.config.dsn#">
        SELECT COUNT(*) AS rc FROM HealthMetrics
    </cfquery>

    <cfif checkHealthMetrics.rc EQ 0>
        <!--- If empty, insert initial values --->
        <!--- Insert initial values into HealthMetrics table --->
        <cfquery name="insertHealthMetrics" datasource="#application.config.dsn#">
            INSERT [HealthMetrics] ([ID], [Title], [Metric], [Description], [MetricValue], [Status], [ButtonText], [ButtonURL], [ThresholdToBad]) VALUES (1, N'Content with Inactive Owners', N'Items', N'Users who were disabled but still have content.', 0, N'No Data', N'Review & Clean', N'/siteadmin/health/inactive-owners',100);
            INSERT [HealthMetrics] ([ID], [Title], [Metric], [Description], [MetricValue], [Status], [ButtonText], [ButtonURL], [ThresholdToBad]) VALUES (2, N'Items with low views', N'Items', N'Items with low view counts', 0, N'No Data', N'Review & Clean', N'/siteadmin/health/low-views',100);
            INSERT [HealthMetrics] ([ID], [Title], [Metric], [Description], [MetricValue], [Status], [ButtonText], [ButtonURL], [ThresholdToBad]) VALUES (3, N'Aging Notifications', N'Notifications', N'Old notifications in the system that are no longer relevent', 0, N'No Data', N'Set Expiration Date', N'/siteadmin/health/aging-notifications',1000);
            INSERT [HealthMetrics] ([ID], [Title], [Metric], [Description], [MetricValue], [Status], [ButtonText], [ButtonURL], [ThresholdToBad]) VALUES (4, N'Large Files', N'Items', N'Large files in the system', 0, N'No Data', N'Review & Clean', N'/siteadmin/health/large-files',100);
            INSERT [HealthMetrics] ([ID], [Title], [Metric], [Description], [MetricValue], [Status], [ButtonText], [ButtonURL], [ThresholdToBad]) VALUES (5, N'Online Forms', N'Forms', N'From your Forms app', 0, N'No Data', N'Review & Clean', N'/siteadmin/health/form-responses',1000);
        </cfquery>
    </cfif>


    <!--- Create HealthLargeFiles table --->
    <cfquery name="createHealthLargeFiles" datasource="#application.config.dsn#">
        IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'HealthLargeFiles')
        BEGIN
            CREATE TABLE HealthLargeFiles
            (
                ID INT IDENTITY(1,1) PRIMARY KEY,
                AppID INT NOT NULL,
                ItemID INT NOT NULL,
                AppTable VARCHAR(100) NOT NULL,
                UserID INT NULL,
                EditUserID INT NULL,
                DateEntered DATETIME NULL,
                DateModified DATETIME NULL,
                ItemLocation VARCHAR(2000) NULL,
                Title VARCHAR(1000) NULL,
                ActionQueued VARCHAR(2) NULL
            )
        END
    </cfquery>

    <!--- Create HealthForms table for form statistics --->
    <cfquery name="createHealthForms" datasource="#application.config.dsn#">
        IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'HealthForms')
        BEGIN
            CREATE TABLE HealthForms
            (
                ID INT IDENTITY(1,1) PRIMARY KEY,
                FormID INT NOT NULL,
                FormTitle VARCHAR(1000) NULL,
                TotalResponses INT NULL DEFAULT 0,
                ResponsesOver10Years INT NULL DEFAULT 0,
                ResponsesOver7Years INT NULL DEFAULT 0,
                ResponsesOver3Years INT NULL DEFAULT 0,
                LastResponseDate DATETIME NULL,
                LastModifiedDate DATETIME NULL,
                ActionQueued VARCHAR(2) NULL
            )
        END
    </cfquery>

    <!--- Create HealthCleanupActionQueue table for background processing of actions --->
    <cfquery name="createHealthCleanupActionQueue" datasource="#application.config.dsn#">
        IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'HealthCleanupActionQueue')
        BEGIN
            CREATE TABLE HealthCleanupActionQueue
            (
                ID INT IDENTITY(1,1) PRIMARY KEY,
                UserID INT NOT NULL,
                Action VARCHAR(2) NOT NULL,
                ActionParam INT NULL,
                ItemId INT NOT NULL,
                Apptable VARCHAR(100) NOT NULL,
                Appid INT NOT NULL,
                Title VARCHAR(1000) NULL,
                Location VARCHAR(1000) NULL,
                DateAdded DATETIME NOT NULL DEFAULT GETDATE(),
                DateCompleted DATETIME NULL
            )
        END
    </cfquery>

<!--- Create HealthNoViews_View for better reporting AZ-4798 --->
    <cfquery name="dropHealthNoViewsView" datasource="#application.config.dsn#">
        IF EXISTS (SELECT * FROM sys.views WHERE name = 'HealthNoViews_View')
        BEGIN
            DROP VIEW HealthNoViews_View
        END
    </cfquery>
    
    <cfquery name="createHealthNoViewsView" datasource="#application.config.dsn#">
        CREATE VIEW HealthNoViews_View AS
        SELECT 
            hnv.ID,
            hnv.AppID,
            hnv.ItemID,
            hnv.AppTable,
            hnv.Title,
            CONCAT(p1.FirstName, ' ', p1.LastName) AS Owner,
            e1.EmployeeID AS OwnerID,
            hnv.ItemLocation As Location,
            hnv.DateEntered AS Date_Entered,
            hnv.DateModified AS Last_Modified_Date,
            CONCAT(p2.FirstName, ' ', p2.LastName) AS Last_Modifier,
            e2.EmployeeID AS ModifierID,
            hnv.ActionQueued,
            app.AppTypeID
        FROM 
            HealthNoViews hnv
            INNER JOIN UserProfile up1 ON hnv.userID = up1.UserID
            INNER JOIN Person p1 ON up1.PersonID = p1.PersonID
            LEFT JOIN Employee e1 ON p1.PersonID = e1.PersonID
            LEFT JOIN UserProfile up2 ON hnv.EditUserID = up2.UserID
            LEFT JOIN Person p2 ON up2.PersonID = p2.PersonID
            LEFT JOIN Employee e2 ON p2.PersonID = e2.PersonID
            LEFT JOIN Application app ON hnv.AppID = app.AppID
    </cfquery>

<!--- Create InactiveOwners_View for better reporting AZ-4798 --->
    <cfquery name="dropInactiveOwnersView" datasource="#application.config.dsn#">
        IF EXISTS (SELECT * FROM sys.views WHERE name = 'InactiveOwners_View')
        BEGIN
            DROP VIEW InactiveOwners_View
        END
    </cfquery>
    
    <cfquery name="createInactiveOwnersView" datasource="#application.config.dsn#">
        CREATE VIEW InactiveOwners_View AS
        SELECT 
            hdu.ID,
            hdu.AppID,
            hdu.ItemID,
            hdu.AppTable,
            hdu.Title,
            CONCAT(p1.FirstName, ' ', p1.LastName) AS Owner,
            e1.EmployeeID AS OwnerID,
            hdu.ItemLocation As Location,
            hdu.DateEntered AS Date_Entered,
            hdu.DateModified AS Last_Modified_Date,
            CONCAT(p2.FirstName, ' ', p2.LastName) AS Last_Modifier,
            e2.EmployeeID AS ModifierID,
            hdu.ActionQueued,
            app.AppTypeID
        FROM 
            HealthDisabledUsers hdu
            INNER JOIN UserProfile up1 ON hdu.userID = up1.UserID
            INNER JOIN Person p1 ON up1.PersonID = p1.PersonID
            LEFT JOIN Employee e1 ON p1.PersonID = e1.PersonID
            LEFT JOIN UserProfile up2 ON hdu.EditUserID = up2.UserID
            LEFT JOIN Person p2 ON up2.PersonID = p2.PersonID
            LEFT JOIN Employee e2 ON p2.PersonID = e2.PersonID
            LEFT JOIN Application app ON hdu.AppID = app.AppID
    </cfquery>

<!--- Create LargeFiles_View for better reporting AZ-4798 --->
    <cfquery name="dropLargeFilesView" datasource="#application.config.dsn#">
        IF EXISTS (SELECT * FROM sys.views WHERE name = 'LargeFiles_View')
        BEGIN
            DROP VIEW LargeFiles_View
        END
    </cfquery>
    
    <cfquery name="createLargeFilesView" datasource="#application.config.dsn#">
        CREATE VIEW LargeFiles_View AS
        SELECT 
            hlf.ID,
            hlf.AppID,
            hlf.ItemID,
            hlf.AppTable,
            hlf.Title,
            CONCAT(p1.FirstName, ' ', p1.LastName) AS Owner,
            e1.EmployeeID AS OwnerID,
            hlf.ItemLocation As Location,
            hlf.DateEntered AS Date_Entered,
            hlf.DateModified AS Last_Modified_Date,
            CONCAT(p2.FirstName, ' ', p2.LastName) AS Last_Modifier,
            e2.EmployeeID AS ModifierID,
            hlf.ActionQueued,
            app.AppTypeID
        FROM 
            HealthLargeFiles hlf
            INNER JOIN UserProfile up1 ON hlf.userID = up1.UserID
            INNER JOIN Person p1 ON up1.PersonID = p1.PersonID
            LEFT JOIN Employee e1 ON p1.PersonID = e1.PersonID
            LEFT JOIN UserProfile up2 ON hlf.EditUserID = up2.UserID
            LEFT JOIN Person p2 ON up2.PersonID = p2.PersonID
            LEFT JOIN Employee e2 ON p2.PersonID = e2.PersonID
            LEFT JOIN Application app ON hlf.AppID = app.AppID
    </cfquery>

<!--- Create HealthForms_View for better reporting --->
    <cfquery name="dropHealthFormsView" datasource="#application.config.dsn#">
        IF EXISTS (SELECT * FROM sys.views WHERE name = 'HealthForms_View')
        BEGIN
            DROP VIEW HealthForms_View
        END
    </cfquery>
    
    <cfquery name="createHealthFormsView" datasource="#application.config.dsn#">
        CREATE VIEW HealthForms_View AS
        SELECT ID, 
            FormID,
            FormTitle as Form_Title,
            TotalResponses as Total_Responses,
            ResponsesOver10Years as Over_10_YR,
            ResponsesOver7Years as Over_7_YR,
            ResponsesOver3Years as Over_3_YR,
            LastResponseDate as Last_Response,
            LastModifiedDate as Form_Modified,
            ActionQueued
        FROM HealthForms
        WHERE formID > 0
    </cfquery>

<!--- Case AZ-4654 - Add a setting for the throttle value. Higher the number the slower the background process
will do its work.  --->
    <cfquery datasource="#application.config.DSN#" name="addBackgroundTaskThrottle">
        IF NOT EXISTS(SELECT * FROM AppSetting WHERE Name = 'BACKGROUNDTASKTHROTTLE')
        BEGIN
            INSERT INTO [dbo].[AppSetting]
                ([Name]
                ,[Value])
            VALUES
                ('BACKGROUNDTASKTHROTTLE'
                ,'100')
        END
    </cfquery>
<!--- AZ-4911: Adjust automatic patch download functionality to point to Azure --->
<cftransaction>
    <cfquery datasource="#application.config.DSN#" name="addUpdateDownloadUrl">
        IF NOT EXISTS (SELECT * FROM AppSetting WHERE Name = 'UpdateDownloadUrl')
        BEGIN
            INSERT INTO [dbo].[AppSetting] ([Name], [Value])
            VALUES ('UpdateDownloadUrl', 'https://ic2files.intranetconnections.com/')
        END
    </cfquery>
</cftransaction>