<!---
/*========================================================================================================================================
 
Name script: RR515_widget_weather_deprecation.sql
Jira: https://sqboxsolutions.atlassian.net/browse/RR-515
 
Description: This script will delete all weather widget and ajust the layout after the version 12.0.
 
Table: 	[dbo].[widget]
		[dbo].[widgetbox]
		[dbo].[WidgetColumn]
 
========================================================================================================================================*/
--->

<cfif form.Type EQ "Patch">
        <cfinclude template="patch_14.5.02.cfm">
</cfif>


<cfquery datasource="#application.config.DSN#">

begin

	declare	@version			varchar(6), 
			@versionUpdate		varchar(6), 
			@widgetTypeId		varchar(2), 
			@widgetID			int,		
			@widgetInterfaceId	int,
			@widgetColumnId		int,
			@widgetBoxId		int,
			@columnRow			int,
			@boxColumnCount		int,
			@countWidgetColumn	int
		
	set @versionUpdate = '13.0'

	select @version = value 
	from AppSetting
	where name = 'version'

	if @versionUpdate <= @version

		Begin

			select @widgetTypeId = widgetTypeId from WidgetType
			where WidgetTypeTitle = 'Weather'

			DECLARE cursor_results CURSOR FOR
  
			SELECT widgetID, widgetInterfaceId, widgetColumnId, widgetBoxId
			FROM widget 
			where WidgetTypeID = @widgetTypeId;
 
			OPEN cursor_results
			FETCH NEXT FROM cursor_results into @widgetID, @widgetInterfaceId, @widgetColumnId, @widgetBoxId
			WHILE @@FETCH_STATUS = 0
				BEGIN 
					
					if Exists (select * from widget where widgetID = @widgetID)
						Begin
							
							select @columnRow = columnRow, @boxColumnCount = boxColumnCount
							from widgetbox
							where WidgetInterfaceID = @widgetInterfaceId
							and WidgetColumnID = @widgetColumnId
							and WidgetBoxID = @widgetBoxId

							delete from widget where widgetID = @widgetID
							
							Print 'Delete: ' + CONVERT(varchar(10), @widgetID) 
							
							if @boxColumnCount > 1
								Begin
									
									update widgetbox set BoxColumnCount = @boxColumnCount-1
									where WidgetInterfaceID = @widgetInterfaceId
									and WidgetColumnID = @widgetColumnId
									and WidgetBoxID = @widgetBoxId
									and ColumnRow = @columnRow
									
									Print 'Update:[' + CONVERT(varchar(10), @boxColumnCount) + ' to ' + CONVERT(varchar(10), @boxColumnCount-1) + '] WidgetInterfaceID(' + CONVERT(varchar(10), @widgetInterfaceId) + ') || WidgetColumnID('+ CONVERT(varchar(10), @widgetColumnId) +') || WidgetBoxID('+ CONVERT(varchar(10), @widgetBoxId) +') || ColumnRow('+ CONVERT(varchar(10), @columnRow) +')'
									
								end
							else
								Begin
									
									delete widgetbox
									where WidgetInterfaceID = @widgetInterfaceId
									and WidgetColumnID = @widgetColumnId
									and WidgetBoxID = @widgetBoxId
									and ColumnRow = @columnRow
									
									Print 'Delete: WidgetInterfaceID(' + CONVERT(varchar(10), @widgetInterfaceId) + ') || WidgetColumnID('+ CONVERT(varchar(10), @widgetColumnId) +') || WidgetBoxID('+ CONVERT(varchar(10), @widgetBoxId) +') || ColumnRow('+ CONVERT(varchar(10), @columnRow) +')' 
									
									select @countWidgetColumn = count(*) from widgetbox
									where WidgetInterfaceID = @widgetInterfaceId
									and WidgetColumnID = @widgetColumnId 

									Print 'CountWidgetColumn: ' + CONVERT(varchar(10), @countWidgetColumn)
									
									if @countWidgetColumn = 0
										Begin
										
											delete WidgetColumn
											where WidgetInterfaceID = @widgetInterfaceId
											and WidgetColumnID = @widgetColumnId
										
											Print 'Delete: WidgetInterfaceID(' + CONVERT(varchar(10), @widgetInterfaceId) + ') || WidgetColumnID('+ CONVERT(varchar(10), @widgetColumnId) +')'
										
										End
								end
						END
					
					FETCH NEXT FROM cursor_results into @widgetID, @widgetInterfaceId, @widgetColumnId, @widgetBoxId
				END
			
		CLOSE cursor_results;
		DEALLOCATE cursor_results;
		END
	Else
		Begin
			PRINT @version + ' not supported';
		End
END

</cfquery>