/* 
	RunUpgrades.sql
	Script to run upgrades _and(or)_ finish load of new machine 
	Assumes that the DB already exists and *is the 'current' database* (in db_name()).

	NOTES: 
	1. To add a new version update, make changes at the places marked by '!!!'
	2. There might be a cleaner way to do this--the text of 
		the PRINT, EXEC and SELECT statements for each 'updating' 
		is identical, maybe it can be pulled into a single common place?
	3. We try to run all the Update scripts if @origPartNumber is Null, but no
		guarantees that this will make for a happy schema! Be sure to
		run ConfigGUI's AnalyzeDBSchema if this happens.
*/

use control

/* variables: */
declare @PartNumber varchar(30)	
declare @origPartNumber varchar(30)	
declare @updateStr varchar(30)	/* e.g. 100to130 */

/* consts: */
declare @highestPartNumber varchar(30)
declare @secondHighestPartNumber varchar(30)
declare @scriptsPath varchar(128)
declare @execStrStartStart varchar(128)
declare @execStrStart varchar(255)
declare @execStrMid varchar(128)
declare @execStrEnd varchar(128)

/* !!! put highest and second-highest partnumber here */
set @highestPartNumber = 'SV0238-08F0'							/* 8.2 w/ coherent changes */
set @secondHighestPartNumber = 'SV0238-08E0'					/* 8.2 */

set @scriptsPath = 'C:\nvision\envy\install\'

/* --------------------------------
	(this might be obsolete:)
	we use these 3 consts to get 
	"master.dbo.xp_cmdshell'osql -E -n -d [the db name] -i C:\nvision\envy\install\ControlSchemaUpdate_[update].sql'"
*/
set @execStrStartStart ='master.dbo.xp_cmdshell ''osql -E -n -d ' + db_name() + ' -i ' + @scriptsPath
set @execStrStart = @execStrStartStart + 'ControlSchemaUpdate_'
set @execStrMid	= '.sql'
set @execStrEnd	= ''''
/* ------------------------------- */
SELECT @origPartNumber =Version_SVNumber FROM Version WHERE (Select MAX(Version_ID) FROM Version)= Version_ID
SET @PartNumber = @origPartNumber

IF (@origPartNumber IS NULL)
BEGIN
	PRINT 'Starting with NO Version information in database.'
	PRINT 'We recommend that you run the ConfigGUI''s Analyze DB Schema' 
	PRINT '    for insurance after the upgrade is complete.'
END
PRINT 'Current Part Number # ' + db_Name() + ' ' + @PartNumber
	
IF (@origPartNumber IS NULL OR 
	(@PartNumber='SV0113-010200' OR @PartNumber='SV0113-010000'))  /* at version 1.2, 1.0, or none */
BEGIN 
	set @updateStr = '100to130'
	PRINT 'Executing update from ' + @updateStr
	EXEC(@execStrStart + @updateStr + @execStrMid + @execStrEnd)
	SELECT @PartNumber = Version_SVNumber FROM Version WHERE (Select MAX(Version_ID) FROM Version)= Version_ID
	PRINT 'Now Part Number # ' + db_Name() + ' ' + @PartNumber
END


IF(@origPartNumber IS NULL OR @PartNumber='SV0113-010300') /* at version 1.3*/
BEGIN 
	set @updateStr = '130to200'
	PRINT 'Executing update from ' + @updateStr
	EXEC(@execStrStart + @updateStr + @execStrMid + @execStrEnd)
	SELECT @PartNumber = Version_SVNumber FROM Version WHERE (Select MAX(Version_ID) FROM Version)= Version_ID
	PRINT 'Now Part Number # ' + db_Name() + ' ' + @PartNumber
END


IF(@origPartNumber IS NULL OR @PartNumber='1147476A0') /* at version 2.0 */
BEGIN
	set @updateStr = '200to210'
	PRINT 'Executing update from ' + @updateStr
	EXEC(@execStrStart + @updateStr + @execStrMid + @execStrEnd)
	SELECT @PartNumber = Version_SVNumber FROM Version WHERE (Select MAX(Version_ID) FROM Version)= Version_ID
	PRINT 'Now Part Number # ' + db_Name() + ' ' + @PartNumber
END

IF(@origPartNumber IS NULL OR @PartNumber='1192168') /* at version 2.1*/
BEGIN
	set @updateStr = '210to220'
	PRINT 'Executing update from ' + @updateStr
	EXEC(@execStrStart + @updateStr + @execStrMid + @execStrEnd)
	SELECT @PartNumber = Version_SVNumber FROM Version WHERE (Select MAX(Version_ID) FROM Version)= Version_ID
	PRINT 'Now Part Number # ' + db_Name() + ' ' + @PartNumber

END

IF(@origPartNumber IS NULL OR @PartNumber='1213686') /* at version 2.2*/
BEGIN
	set @updateStr = '220to230'
	PRINT 'Executing update from ' + @updateStr
	EXEC(@execStrStart + @updateStr + @execStrMid + @execStrEnd)
	SELECT @PartNumber = Version_SVNumber FROM Version WHERE (Select MAX(Version_ID) FROM Version)= Version_ID
	PRINT 'Now Part Number # ' + db_Name() + ' ' + @PartNumber
END

IF(@origPartNumber IS NULL OR @PartNumber='SV0238-02') /* at version 2.3*/
BEGIN
	set @updateStr = '230to300'
	PRINT 'Executing update from ' + @updateStr
	PRINT @execStrStart + @updateStr + @execStrMid + @execStrEnd
	EXEC(@execStrStart + @updateStr + @execStrMid + @execStrEnd)
	SELECT @PartNumber = Version_SVNumber FROM Version WHERE (Select MAX(Version_ID) FROM Version)= Version_ID
	PRINT 'Now Part Number # ' + db_Name() + ' ' + @PartNumber
END

IF(@origPartNumber IS NULL OR @PartNumber='SV0238-03') /* perhaps at version 3.0 */
BEGIN
	set @updateStr = '300to310'
	PRINT 'Executing update from ' + @updateStr
	PRINT @execStrStart + @updateStr + @execStrMid + @execStrEnd
	EXEC(@execStrStart + @updateStr + @execStrMid + @execStrEnd)
	SELECT @PartNumber = Version_SVNumber FROM Version WHERE (Select MAX(Version_ID) FROM Version)= Version_ID
	PRINT 'Now Part Number # ' + db_Name() + ' ' + @PartNumber
END

IF(@origPartNumber IS NULL OR @PartNumber='SV0238-03') /* perhaps at version 3.1 */
BEGIN
	set @updateStr = '310to350'
	PRINT 'Executing update from ' + @updateStr
	PRINT @execStrStart + @updateStr + @execStrMid + @execStrEnd
	EXEC(@execStrStart + @updateStr + @execStrMid + @execStrEnd)
	SELECT @PartNumber = Version_SVNumber FROM Version WHERE (Select MAX(Version_ID) FROM Version)= Version_ID
	PRINT 'Now Part Number # ' + db_Name() + ' ' + @PartNumber
END

IF(@origPartNumber IS NULL OR @PartNumber='SV0238-03') /* perhaps at version 3.5 */
BEGIN
	set @updateStr = '350to360'
	PRINT 'Executing update from ' + @updateStr
	PRINT @execStrStart + @updateStr + @execStrMid + @execStrEnd
	EXEC(@execStrStart + @updateStr + @execStrMid + @execStrEnd)
	SELECT @PartNumber = Version_SVNumber FROM Version WHERE (Select MAX(Version_ID) FROM Version)= Version_ID
	PRINT 'Now Part Number # ' + db_Name() + ' ' + @PartNumber
END

IF(@origPartNumber IS NULL OR @PartNumber='SV0238-03H0') /* should be at version 3.6 */
BEGIN
	set @updateStr = '360to500'
	PRINT 'Executing update from ' + @updateStr
	PRINT @execStrStart + @updateStr + @execStrMid + @execStrEnd
	EXEC(@execStrStart + @updateStr + @execStrMid + @execStrEnd)
	SELECT @PartNumber = Version_SVNumber FROM Version WHERE (Select MAX(Version_ID) FROM Version)= Version_ID
	PRINT 'Now Part Number # ' + db_Name() + ' ' + @PartNumber
END

IF(@origPartNumber IS NULL OR @PartNumber='SV0238-05A0' OR @PartNumber='SV0238-05B0') /* We're at version 5.0 */
BEGIN
	set @updateStr = '500to600'
	PRINT 'Executing update from ' + @updateStr
	PRINT @execStrStart + @updateStr + @execStrMid + @execStrEnd
	EXEC(@execStrStart + @updateStr + @execStrMid + @execStrEnd)
	SELECT @PartNumber = Version_SVNumber FROM Version WHERE (Select MAX(Version_ID) FROM Version)= Version_ID
	PRINT 'Now Part Number # ' + db_Name() + ' ' + @PartNumber
END

IF(@origPartNumber IS NULL OR @PartNumber='SV0238-06A0') /* We're at 6.0 */
BEGIN
	set @updateStr = '600to601'
	PRINT 'Executing update from ' + @updateStr
	PRINT @execStrStart + @updateStr + @execStrMid + @execStrEnd
	EXEC(@execStrStart + @updateStr + @execStrMid + @execStrEnd)
	SELECT @PartNumber = Version_SVNumber FROM Version WHERE (Select MAX(Version_ID) FROM Version)= Version_ID
	PRINT 'Now Part Number # ' + db_Name() + ' ' + @PartNumber
END

IF(@origPartNumber IS NULL OR @PartNumber='SV0238-06B0' ) /* We're at 6.0.1 */
BEGIN
	set @updateStr = '601to609'
	PRINT 'Executing update from ' + @updateStr
	PRINT @execStrStart + @updateStr + @execStrMid + @execStrEnd
	EXEC(@execStrStart + @updateStr + @execStrMid + @execStrEnd)
	SELECT @PartNumber = Version_SVNumber FROM Version WHERE (Select MAX(Version_ID) FROM Version)= Version_ID
	PRINT 'Now Part Number # ' + db_Name() + ' ' + @PartNumber
END

/*We're at 6.0.9 or 8.0.0 and adding GvNode fields and tables */
IF(@origPartNumber IS NULL OR @PartNumber ='SV0238-06D0' OR @PartNumber ='SV0238-06E0' or @PartNumber = 'SV0238-08A0' or @PartNumber = 'SV0238-08B0')
BEGIN
	set @updateStr = '609to800'
	PRINT 'Executing update from ' + @updateStr
	PRINT @execStrStart + @updateStr + @execStrMid + @execStrEnd
	EXEC(@execStrStart + @updateStr + @execStrMid + @execStrEnd)
	SELECT @PartNumber = Version_SVNumber FROM Version WHERE (Select MAX(Version_ID) FROM Version)= Version_ID
	PRINT 'Now Part Number # ' + db_Name() + ' ' + @PartNumber
END

/* More GvNode stuff going from 8.0.0 to 8.1.0 */
IF(@origPartNumber IS NULL OR @PartNumber = 'SV0238-08C0')
BEGIN
	set @updateStr = '800to810'
	PRINT 'Executing update from ' + @updateStr
	PRINT @execStrStart + @updateStr + @execStrMid + @execStrEnd
	EXEC(@execStrStart + @updateStr + @execStrMid + @execStrEnd)
	SELECT @PartNumber = Version_SVNumber FROM Version WHERE (Select MAX(Version_ID) FROM Version)= Version_ID
	PRINT 'Now Part Number # ' + db_Name() + ' ' + @PartNumber
END

/* These stored procedures are needed for the 810 to 820 upgrade */
SET @updateStr = 'ControlProceduresSys'
PRINT 'Executing ' + @updateStr + ' script'
EXEC( @execStrStartStart + @updateStr + @execStrMid + @execStrEnd)
	
/* Tieline changes going from 8.1.0 to 8.2.0 */
/* Coherent schema changes were also added to 8.2 after the tieline changes, SV0238-08E0 is 8.2 and SV0238-08F0 is 8.2 with the coherent changes. */
IF(@origPartNumber IS NULL OR @PartNumber = 'SV0238-08D0' or @PartNumber = 'SV0238-08E0')
BEGIN
	set @updateStr = '810to820'
	PRINT 'Executing update from ' + @updateStr
	PRINT @execStrStart + @updateStr + @execStrMid + @execStrEnd
	EXEC(@execStrStart + @updateStr + @execStrMid + @execStrEnd)
	SELECT @PartNumber = Version_SVNumber FROM Version WHERE (Select MAX(Version_ID) FROM Version)= Version_ID
	PRINT 'Now Part Number # ' + db_Name() + ' ' + @PartNumber
END

PRINT 'Now Part Number # ' + db_Name() + ' ' + @PartNumber

/***    put new updates right above this   *** */
/* ------------------------------------------- */

IF (@origPartNumber IS NULL OR @PartNumber=@highestPartNumber) /* at highest version */
BEGIN
	set @updateStr = 'ControlProcedures'
	PRINT 'Executing ' + @updateStr + ' script'
	EXEC( @execStrStartStart + @updateStr + @execStrMid + @execStrEnd)

	set @updateStr = 'BaselineData'
	PRINT 'Executing ' + @updateStr + ' script'
	EXEC( @execStrStartStart + @updateStr + @execStrMid + @execStrEnd)
	PRINT 'Now Part Number # ' + db_Name() + ' ' + @PartNumber
END
ELSE
BEGIN
	PRINT 'Error, never got updated to the current highest PartNumber.'
END

