SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



ALTER  PROCEDURE dbo.From2To3
AS BEGIN
    SET NOCOUNT ON 
    DECLARE @Ret int, @LocalError int

	IF (SELECT UpgradeStatus FROM dbo.Upgrade) = 1
BEGIN
	RETURN 0
END

	-- Do nothing if the upgrade has been already completed
	IF (SELECT UpgradeStatus FROM dbo.Upgrade) = 2
	BEGIN
		PRINT '.\scripts\procs.sql' + '(' + CONVERT(varchar(30), 590) + '): ' + 'Database already upgraded'
        RETURN 0
	END

	-- If there is no sibling database SOPHOS2 then there is nothing to upgrade
	-- Change the upgrade status to complete
	IF NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'SOPHOS2') 
	BEGIN
		PRINT '.\scripts\procs.sql' + '(' + CONVERT(varchar(30), 598) + '): ' + 'No SOPHOS2 database to upgrade'
		UPDATE Upgrade SET UpgradeStatus = 2
		SET @LocalError = @@ERROR
		IF @LocalError <> 0
		BEGIN
			PRINT '.\scripts\procs.sql' + '(' + CONVERT(varchar(30), 603) + '): ' + 'Failed to update upgrade status'
			RETURN 2
		END
        RETURN 0
	END

    -- Remove any existing data
    EXEC @Ret=dbo.ReinitialiseDatabase
    IF @RET <> 0
    BEGIN
        PRINT '.\scripts\procs.sql' + '(' + CONVERT(varchar(30), 613) + '): ' + 'Failed to reinitialise the database'
        RETURN 55
    END

    ---
    -- Transfer data from SOPHOS2
    ---
	--[ReporterParameters]
    DELETE FROM [dbo].[ReporterParameters] 
    SET @LocalError = @@ERROR
    IF @LocalError <> 0
    BEGIN
        PRINT '.\scripts\procs.sql' + '(' + CONVERT(varchar(30), 626) + '): ' + 'Failed to delete [ReporterParameters]'
        RETURN 55
    END
    INSERT INTO [dbo].[ReporterParameters] 
        SELECT CompanyName,AutoPurge,AutoPurgeMaximum,AutoPurgeOldestMsg,StatisticsLengthInDays,newid()
        FROM [SOPHOS2].[dbo].[ReporterParameters]   
    SET @LocalError = @@ERROR
    IF @LocalError <> 0
    BEGIN
        PRINT '.\scripts\procs.sql' + '(' + CONVERT(varchar(30), 635) + '): ' + 'Failed to transfer [ReporterParameters]'
        RETURN 55
    END

	--[ComputerStatistics20]
    SET IDENTITY_INSERT dbo.ComputerStatistics20 ON
    INSERT dbo.ComputerStatistics20 (ID,CollectedAtGMT,TotalNumber,Assigned,Managed,WithAUInstalled,WithSAVInstalled,WithSAVOnAccess,WithSCFInstalled,WithSCFStarted,
                                  WithVirusAlerts,WithPUAAlerts,WithSCFAlerts,WithSAVErrors,WithSCFErrors,WithUpdateErrors,WithInstallErrors,OutOfDate,
                                  WithDifferentSAVpolicy,WithDifferentAUpolicy,WithDifferentSCFpolicy,VirusCount,PUACount,SCFCount)
		SELECT ID,CollectedAtGMT,TotalNumber,Assigned,Managed,WithAUInstalled,WithSAVInstalled,WithSAVOnAccess,WithSCFInstalled,
		       WithSCFStarted,WithVirusAlerts,WithPUAAlerts,WithSCFAlerts,WithSAVErrors,WithSCFErrors,WithUpdateErrors,WithInstallErrors,OutOfDate,
		       WithDifferentSAVpolicy,WithDifferentAUpolicy,WithDifferentSCFpolicy,VirusCount,PUACount,SCFCount
		    FROM [SOPHOS2].[dbo].ComputerStatistics
    SET @LocalError = @@ERROR
    SET IDENTITY_INSERT [dbo].ComputerStatistics20 OFF
    IF @LocalError <> 0
    BEGIN
        PRINT '.\scripts\procs.sql' + '(' + CONVERT(varchar(30), 652) + '): ' + 'Failed to transfer ComputerStatistics20'
        RETURN 55
    END

	--[IDELists]
    SET IDENTITY_INSERT [dbo].IDElists ON
    INSERT dbo.IDElists (ID,MD5,IDEList,IDENumber)
		SELECT * FROM [SOPHOS2].[dbo].IDElists WHERE ID > 199
    SET @LocalError = @@ERROR
    SET IDENTITY_INSERT [dbo].IDElists OFF
    IF @LocalError <> 0
    BEGIN
        PRINT '.\scripts\procs.sql' + '(' + CONVERT(varchar(30), 664) + '): ' + 'Failed to transfer IDElists'
        RETURN 55
    END

	--[ComputersAndDeletedComputers]
    SET IDENTITY_INSERT dbo.ComputersAndDeletedComputers ON
    INSERT dbo.ComputersAndDeletedComputers (ID,Name,DomainName,OperatingSystem,Description,Managed,Connected,SAVOnAccess,PackageID,
                                             LastScanDateTime,InstallPending,InstallInProgress,IPAddress,ServicePack,QuarantineCount,OutOfDate,
                                             PrimaryCIDLoc,SecondaryCIDLoc,IDEListID,LastLoggedOnUser,InstallFailureReason,LastScanName,InstallTime,
                                             MessageSystemAddress,LastMessageTime,Deleted,
                                             LastSAVErrorAlert,LastAUErrorAlert,LastSCFErrorAlert,
                                             SCFOutOfDate,SCFStarted,SCFVersion,SCFMode,DNSName)
		SELECT ID,Name,DomainName,OperatingSystem,Description,Managed,Connected,SAVOnAccess,PackageID,
		       LastScanDateTime,InstallPending,InstallInProgress,IPAddress,ServicePack,QuarantineCount,OutOfDate,
		       PrimaryCIDLoc,SecondaryCIDLoc,IDEListID,LastLoggedOnUser,InstallFailureReason,LastScanName,InstallTime,
		       MessageSystemAddress,LastMessageTime,Deleted,
		       LastSAVErrorAlert,LastAUErrorAlert,LastSCFErrorAlert,SCFOutOfDate,
		       SCFStarted,SCFVersion,SCFMode,DNSName 
		    FROM [SOPHOS2].[dbo].ComputersAndDeletedComputers
    SET @LocalError = @@ERROR
    SET IDENTITY_INSERT [dbo].ComputersAndDeletedComputers OFF
    IF @LocalError <> 0
    BEGIN
        PRINT '.\scripts\procs.sql' + '(' + CONVERT(varchar(30), 687) + '): ' + 'Failed to transfer ComputersAndDeletedComputers'
        RETURN 55
    END

	--[Packages]
    SET IDENTITY_INSERT dbo.Packages ON
    INSERT dbo.Packages (ID,ProductID,SAVVersion,EngineVersion,VirusDataVersion,IDEChecksum,RolloutNumber,ExpiryTime,NotificationTime,Expired)
		SELECT ID,ProductID,SAVVersion,EngineVersion,VirusDataVersion,IDEChecksum,RolloutNumber,ExpiryTime,NotificationTime,Expired
		    FROM [SOPHOS2].[dbo].Packages
    SET @LocalError = @@ERROR
    SET IDENTITY_INSERT [dbo].Packages OFF
    IF @LocalError <> 0
    BEGIN
        PRINT '.\scripts\procs.sql' + '(' + CONVERT(varchar(30), 700) + '): ' + 'Failed to transfer Packages'
        RETURN 55
    END

	--[DeployedPackages]
    SET IDENTITY_INSERT dbo.DeployedPackages ON
    INSERT dbo.DeployedPackages (ID,PackageID,ComputerID)
		SELECT ID,PackageID,ComputerID
		    FROM [SOPHOS2].[dbo].DeployedPackages
    SET @LocalError = @@ERROR
    SET IDENTITY_INSERT [dbo].DeployedPackages OFF
    IF @LocalError <> 0
    BEGIN
        PRINT '.\scripts\procs.sql' + '(' + CONVERT(varchar(30), 713) + '): ' + 'Failed to transfer DeployedPackages'
        RETURN 55
    END

	--[EMLibraryServers]
    INSERT dbo.EMLibraryServers (ComputerID,NotificationTime,StatusXML)
        SELECT e.ComputerID,ISNULL(pn.LatestNotificationTime,getdate()),e.StatusXML
        FROM [SOPHOS2].[dbo].EMLibraryServers e LEFT OUTER JOIN 
             (SELECT  ComputerID,  max(ISNULL(p.NotificationTime,0)) AS LatestNotificationTime
              FROM [SOPHOS2].dbo.DeployedPackages dp  LEFT OUTER JOIN 
		           [SOPHOS2].dbo.Packages p ON dp.PackageID = p.ID
              GROUP BY ComputerID
             ) pn
        ON e.ComputerID=pn.ComputerID		
    SET @LocalError = @@ERROR
    IF @LocalError <> 0
    BEGIN
        PRINT '.\scripts\procs.sql' + '(' + CONVERT(varchar(30), 730) + '): ' + 'Failed to transfer EMLibraryServers'
        RETURN 55
    END

	--[Errors]
    SET IDENTITY_INSERT dbo.Errors ON
    INSERT dbo.Errors (ID,ComputerID,UserName,AlertTime,Outstanding,Source,EntityInfo,Number,MessageCode,Insert1,Insert2,Insert3,Insert4,Insert5)
		SELECT ID,ComputerID,UserName,AlertTime,Outstanding,Source,EntityInfo,Number,MessageCode,Insert1,Insert2,Insert3,Insert4,Insert5
		    FROM [SOPHOS2].[dbo].Errors
    SET @LocalError = @@ERROR
    SET IDENTITY_INSERT [dbo].Errors OFF
    IF @LocalError <> 0
    BEGIN
        PRINT '.\scripts\procs.sql' + '(' + CONVERT(varchar(30), 743) + '): ' + 'Failed to transfer Errors'
        RETURN 55
    END
    UPDATE dbo.ComputersAndDeletedComputers SET 
		LastSAVErrorAlert = dbo.ComputerLastOutstandingAlertGetFunction( ID, 15 ),
		LastAUErrorAlert = dbo.ComputerLastOutstandingAlertGetFunction( ID, 10 ),		
		LastSCFErrorAlert = dbo.ComputerLastOutstandingAlertGetFunction( ID, 12 )		
    WHERE ID IN ( SELECT ComputerID FROM dbo.Errors WHERE Outstanding=1)

    --[ErrorAlertFilters]
    DELETE dbo.ErrorAlertFilters
    IF @@ERROR <> 0
    BEGIN
        PRINT '.\scripts\procs.sql' + '(' + CONVERT(varchar(30), 758) + '): ' + 'Error deleting ErrorAlertFilters'
        RETURN 55
    END
    INSERT dbo.[ErrorAlertFilters] (Source, Number)
		SELECT Source, Number
		    FROM [SOPHOS2].[dbo].ErrorAlertFilters
    SET @LocalError = @@ERROR
    IF @LocalError <> 0
    BEGIN
        PRINT '.\scripts\procs.sql' + '(' + CONVERT(varchar(30), 767) + '): ' + 'Failed to transfer ErrorAlertFilters'
        RETURN 55
    END

	--[Groups]
    SET IDENTITY_INSERT dbo.Groups ON
    INSERT dbo.Groups (ID,Name,ParentID)
		SELECT ID,Name,ParentID
		    FROM [SOPHOS2].[dbo].Groups
    SET @LocalError = @@ERROR
    SET IDENTITY_INSERT [dbo].Groups OFF
    IF @LocalError <> 0
    BEGIN
        PRINT '.\scripts\procs.sql' + '(' + CONVERT(varchar(30), 780) + '): ' + 'Failed to transfer Groups'
        RETURN 55
    END

	--[ComputerGroupMapping]
    INSERT dbo.ComputerGroupMapping (ComputerID,GroupID)
		SELECT ComputerID,GroupID
		    FROM [SOPHOS2].[dbo].ComputerGroupMapping
    SET @LocalError = @@ERROR
    IF @LocalError <> 0
    BEGIN
        PRINT '.\scripts\procs.sql' + '(' + CONVERT(varchar(30), 791) + '): ' + 'Failed to transfer ComputerGroupMapping'
        RETURN 55
    END

	--[ComputerPolicyStates]
    INSERT dbo.ComputerPolicyStates (ComputerID, Type, CorrelationID, State)
		SELECT ComputerID, Type, CorrelationID, State
		    FROM [SOPHOS2].[dbo].ComputerPolicyStates
    SET @LocalError = @@ERROR
    IF @LocalError <> 0
    BEGIN
        PRINT '.\scripts\procs.sql' + '(' + CONVERT(varchar(30), 802) + '): ' + 'Failed to transfer ComputerPolicyStates'
        RETURN 55
    END

	-- Set the upgrade status to indicate that this upgrade phase has been completed
	UPDATE dbo.Upgrade SET UpgradeStatus = 1
	SET @LocalError = @@ERROR
	IF @LocalError <> 0
	BEGIN
		PRINT '.\scripts\procs.sql' + '(' + CONVERT(varchar(30), 811) + '): ' + 'Failed to update upgrade status'
		RETURN 2
	END
    RETURN 0
END 

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO