<!--- Bug 6833: Send System Alert throws FK error 
	Note this error did not present itself in dev environments because the 
	FK constraints on ChatHistory and ChatMessages tables were disabled.
--->
<cfquery datasource="#application.config.DSN#">
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME = 'fk_ChatMessages_UserID' AND TABLE_NAME = 'ChatMessages')
BEGIN
	ALTER TABLE [ChatMessages] DROP CONSTRAINT [fk_ChatMessages_UserID]
END

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

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME = 'fk_ChatHistory_UserID' AND TABLE_NAME = 'ChatHistory')
BEGIN
	ALTER TABLE [ChatHistory] DROP CONSTRAINT [fk_ChatHistory_UserID]
END
</cfquery>
<!--- Bug 6924 updated stored procedure spFormResponseSpreadsheetData --->
<cfquery datasource="#application.config.DSN#">
IF OBJECT_ID(N'spFormResponseSpreadsheetData', N'P') IS NOT NULL
DROP PROC [spFormResponseSpreadsheetData];
</cfquery>

<cfquery datasource="#application.config.DSN#">
CREATE PROCEDURE [spFormResponseSpreadsheetData] @FormID INT, @ResponseID INT, @SectionID INT, @Export BIT
AS
BEGIN

	-- declare variables needed for procedure
	DECLARE @DynamicSQL NVARCHAR(MAX), @Fields NVARCHAR(MAX), @PivotFields NVARCHAR(MAX), @ColumnList NVARCHAR(MAX), @sFormID VARCHAR(12), @sResponseID VARCHAR(12)
	DECLARE @ParmDefinition NVARCHAR(500);
	DECLARE @FormFieldID INT, @MaxRowNo SMALLINT, @RowNo SMALLINT
				
	-- combine form ID with response ID and Row Nos in a temporary table to perform join on values
	IF OBJECT_ID('tempdb..##tblFormResponseRows') IS NULL
	BEGIN
		CREATE TABLE ##tblFormResponseRows (FormID INT, FormResponseID INT, RowNo SMALLINT)
		SELECT	@MaxRowNo = MAX(frf.RowNo)
		FROM	FormResponseField frf
					INNER JOIN FormField f ON frf.FormFieldID = f.FormFieldID AND f.FormSectionID = @SectionID
		WHERE	frf.FormID = @FormID
				AND frf.FormResponseID = @ResponseID
		SELECT	@RowNo = 0 
		WHILE	@RowNo < @MaxRowNo
		BEGIN
			SELECT	@RowNo = @RowNo + 1
			INSERT	##tblFormResponseRows SELECT @FormID, @ResponseID, @RowNo
		END
	END
		
	-- determine column values & labels based on export flag
	IF @Export = 1
	BEGIN
		SELECT	@Fields = 'tr.RowNo AS Row'
		SELECT	@PivotFields = 'ff.FieldName AS _Name_, 
					CASE
						WHEN ft.FieldTypeID = 8 
							THEN REPLACE(REPLACE(v.Value, ''{ts '''''', ''''), ''''''}'', '''')
						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(v.Value, CHAR(34), ''&quot;'') + ''"
						}''
					WHEN ft.FieldTypeID = 16 
						THEN ''{
							"locationA": "'' + REPLACE(frf.LocationA, CHAR(34), ''&quot;'') + ''",
							"locationAbbrevA": "'' + REPLACE(frf.LocationAbbrevA, CHAR(34), ''&quot;'') + ''",
							"locationB": "'' + REPLACE(frf.LocationB, CHAR(34), ''&quot;'') + ''",
							"locationAbbrevB": "'' + REPLACE(frf.LocationAbbrevB, CHAR(34), ''&quot;'') + ''", 
							"roundTrip": '' + CASE WHEN frf.RoundTrip = 0 THEN ''0'' ELSE ''1'' END + '', 
							"value": "'' + v.Value + ''"
						}''
					ELSE v.Value 
				END AS _Value_'
	END
			

	-- build a column list of spreadsheet fields for the given form
	SELECT	@ColumnList = ''
	SELECT	@ColumnList = @ColumnList + ', [' + FieldName + ']'
	FROM	FormField ff
	WHERE	ff.FormFieldID IN (
				SELECT		DISTINCT(ff2.FormFieldID)
				FROM		FormField ff2
								INNER JOIN FormResponseField frf ON ff2.FormFieldID = frf.FormFieldID
									AND frf.FormID = @FormID
				WHERE		ff2.FormID = @FormID
								AND ff2.FormSectionID = @SectionID
								AND ff2.Deleted = 0
								AND frf.FormResponseID = @ResponseID
			)
	SET @ColumnList = SUBSTRING(@ColumnList, 3, LEN(@ColumnList))
			
	-- build dynamic sql to get response data
	IF @ColumnList = ''
	BEGIN
		-- no response fields, simple query
		SELECT @DynamicSQL = '
			SELECT	' + @Fields + '
			FROM	Form f
						INNER JOIN FormResponse fr ON f.FormID = fr.FormID
							AND fr.FormID = @pFormID
						INNER JOIN ##tblFormResponseRows tr ON fr.FormID = tr.FormID
							AND fr.FormResponseID = tr.FormResponseID'
			+ ' ORDER BY	1'
		SET @ParmDefinition = N'@pFormID INT'
		EXECUTE sp_executesql @DynamicSQL, @ParmDefinition, @pFormID = @FormID 
	END
	ELSE
	BEGIN
		-- perform pivot of response data
		SELECT @DynamicSQL = '
			SELECT	* 
			FROM	(
						SELECT	' + @Fields + ', ' + @PivotFields + '
						FROM	Form f
									INNER JOIN FormResponse fr ON f.FormID = fr.FormID
										AND fr.FormID = @pFormID 
									INNER JOIN FormField ff ON f.FormID = ff.FormID
										AND ff.FormID = @pFormID 
									INNER JOIN FormFieldType ft ON ff.FieldTypeID = ft.FieldTypeID
									INNER JOIN FormResponseField frf ON f.FormID = frf.FormID
										AND fr.FormResponseID = frf.FormResponseID
										AND ff.FormFieldID = frf.FormFieldID
										AND frf.FormID = @pFormID
									INNER JOIN FormResponseFieldValue v ON fr.FormID = v.FormID 
										AND fr.FormResponseID = v.FormResponseID
										AND ff.FormFieldID = v.FormFieldID
										AND frf.RowNo = v.RowNo
										AND v.FormID = @pFormID 
									INNER JOIN ##tblFormResponseRows tr ON v.FormID = tr.FormID
										AND v.FormResponseID = tr.FormResponseID
										AND v.RowNo = tr.RowNo
										AND tr.FormID = @pFormID
						WHERE	ff.Deleted = 0
								AND v.RowNo <> 0
								AND fr.FormResponseID =  @pResponseID
					) AS SourceTable
					PIVOT (MAX(SourceTable._Value_) FOR SourceTable._Name_ IN (' + @ColumnList + ')) AS PivotTable'
			+ ' ORDER BY	1'

			SET @ParmDefinition = N'@pFormID INT, @pResponseID INT'
			EXECUTE sp_executesql @DynamicSQL, @ParmDefinition, @pFormID = @FormID, @pResponseID = @ResponseID 
	END
				
	-- drop temporary table
	DROP TABLE ##tblFormResponseRows
END
</cfquery>
<!--- Bug 6924 Create New Index IX_FormField_FormSectionID --->
<cfquery datasource="#application.config.DSN#">
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='IX_FormField_FormSectionID' AND object_id = OBJECT_ID('FormField'))
BEGIN
	CREATE NONCLUSTERED INDEX [IX_FormField_FormSectionID] ON [FormField]
	(
		[FormSectionID] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
END
</cfquery>
<!--- Bug 7008 Don't Populate URL Field in Link Table for Internal Links --->
<cfquery datasource="#application.config.DSN#">
BEGIN
	UPDATE 	Link
	SET 	URL = ''
	WHERE 	TinyURLID IS NOT NULL;
END
</cfquery>