--****************************************************************************************************
--*
--*	This script will update Microsoft Project 2002 SQL Server tables to Microsoft Project 11 tables.
--*
--*	Privileges needed: member of db_owner or db_ddladmin
--*
--****************************************************************************************************

--****************************************************************************************************
--*		Note: Objects are only created if they don't exist - this is to support upgrading from 
--*         Project 2002 to Project 2003, or Project 2003 partial to Project 2003 Full
--****************************************************************************************************

--****************************************************************************************************
--*		MSP_RESOURCES
--****************************************************************************************************

IF NOT EXISTS (select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'MSP_RESOURCES' AND COLUMN_NAME = 'RES_BOOKING_TYPE')
BEGIN
	ALTER TABLE dbo.MSP_RESOURCES ADD
		RES_BOOKING_TYPE 							smallint 					NULL
END
GO


IF NOT EXISTS (select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'MSP_RESOURCES' AND COLUMN_NAME = 'RES_AD_GUID')
BEGIN
	ALTER TABLE dbo.MSP_RESOURCES ADD
		RES_AD_GUID 									uniqueidentifier 	NULL
END
GO


IF NOT EXISTS (select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'MSP_RESOURCES' AND COLUMN_NAME = 'RES_ACT_WORK_PROT')
BEGIN
	ALTER TABLE dbo.MSP_RESOURCES ADD
		RES_ACT_WORK_PROT 						decimal(25,6) 		NULL
END
GO


IF NOT EXISTS (select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'MSP_RESOURCES' AND COLUMN_NAME = 'RES_ACT_OVT_WORK_PROT')
BEGIN
	ALTER TABLE dbo.MSP_RESOURCES ADD
		RES_ACT_OVT_WORK_PROT 				decimal(25,6) 		NULL
END
GO


IF NOT EXISTS (select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'MSP_RESOURCES' AND COLUMN_NAME = 'RES_CREATION_DATE')
BEGIN
	ALTER TABLE dbo.MSP_RESOURCES ADD
		RES_CREATION_DATE							datetime					NULL
END
GO

--****************************************************************************************************
--*		MSP_ASSIGNMENTS
--****************************************************************************************************

IF NOT EXISTS (select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'MSP_ASSIGNMENTS' AND COLUMN_NAME = 'ASSN_BOOKING_TYPE')
BEGIN
	ALTER TABLE dbo.MSP_ASSIGNMENTS ADD
		ASSN_BOOKING_TYPE 						smallint 					NULL
END
GO


IF NOT EXISTS (select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'MSP_ASSIGNMENTS' AND COLUMN_NAME = 'ASSN_ACT_WORK_PROT')
BEGIN
	ALTER TABLE dbo.MSP_ASSIGNMENTS ADD
		ASSN_ACT_WORK_PROT 						decimal(25,6) 		NULL
END
GO


IF NOT EXISTS (select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'MSP_ASSIGNMENTS' AND COLUMN_NAME = 'ASSN_ACT_OVT_WORK_PROT')
BEGIN
	ALTER TABLE dbo.MSP_ASSIGNMENTS ADD
		ASSN_ACT_OVT_WORK_PROT 				decimal(25,6) 		NULL
END
GO


IF NOT EXISTS (select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'MSP_ASSIGNMENTS' AND COLUMN_NAME = 'ASSN_CREATION_DATE')
BEGIN
	ALTER TABLE dbo.MSP_ASSIGNMENTS ADD
		ASSN_CREATION_DATE						datetime					NULL
END
GO

--****************************************************************************************************
--*		MSP_ASSN_ENTERPRISE
--****************************************************************************************************

IF NOT EXISTS (select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'MSP_ASSN_ENTERPRISE' AND COLUMN_NAME = 'ASSN_BOOKING_TYPE')
BEGIN
	ALTER TABLE dbo.MSP_ASSN_ENTERPRISE ADD
		ASSN_BOOKING_TYPE 						smallint 					NULL
END
GO


IF NOT EXISTS (select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'MSP_ASSN_ENTERPRISE' AND COLUMN_NAME = 'ASSN_ACT_WORK_PROT')
BEGIN
	ALTER TABLE dbo.MSP_ASSN_ENTERPRISE ADD
		ASSN_ACT_WORK_PROT 						decimal(25,6) 		NULL
END
GO


IF NOT EXISTS (select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'MSP_ASSN_ENTERPRISE' AND COLUMN_NAME = 'ASSN_ACT_OVT_WORK_PROT')
BEGIN
	ALTER TABLE dbo.MSP_ASSN_ENTERPRISE ADD
		ASSN_ACT_OVT_WORK_PROT 				decimal(25,6) 		NULL
END
GO


IF NOT EXISTS (select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'MSP_ASSN_ENTERPRISE' AND COLUMN_NAME = 'ASSN_CREATION_DATE')
BEGIN
	ALTER TABLE dbo.MSP_ASSN_ENTERPRISE ADD
		ASSN_CREATION_DATE						datetime					NULL
END
GO


IF NOT EXISTS (select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'MSP_ASSN_ENTERPRISE' AND COLUMN_NAME = 'ASSN_DEFAULT_VERSION_PROJ_ID')
BEGIN
	ALTER TABLE dbo.MSP_ASSN_ENTERPRISE ADD
		ASSN_DEFAULT_VERSION_PROJ_ID    	int                  NULL
END
GO


IF NOT EXISTS (select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'MSP_ASSN_ENTERPRISE' AND COLUMN_NAME = 'ASSN_PROJ_VERSION_ID')
BEGIN
	ALTER TABLE dbo.MSP_ASSN_ENTERPRISE ADD
		ASSN_PROJ_VERSION_ID             	int                  NULL
END
GO


IF EXISTS (SELECT name FROM sysindexes WHERE name = 'I_MSP_ASSN_ENTERPRISE_ASSN_VERSION_ID_PROJ_PUBLISHED_ID')
  DROP INDEX dbo.MSP_ASSN_ENTERPRISE.I_MSP_ASSN_ENTERPRISE_ASSN_VERSION_ID_PROJ_PUBLISHED_ID
GO


CREATE INDEX I_MSP_ASSN_ENTERPRISE_ASSN_VERSION_ID_PROJ_PUBLISHED_ID
ON dbo.MSP_ASSN_ENTERPRISE (ASSN_PROJ_VERSION_ID, ASSN_DEFAULT_VERSION_PROJ_ID) WITH FILLFACTOR = 40
-- ON [PRIMARY]
GO


IF EXISTS (SELECT name FROM sysindexes WHERE name = 'I_MSP_ASSN_ENTERPRISE')
  DROP INDEX dbo.MSP_ASSN_ENTERPRISE.I_MSP_ASSN_ENTERPRISE
GO


CREATE CLUSTERED INDEX I_MSP_ASSN_ENTERPRISE
ON dbo.MSP_ASSN_ENTERPRISE (ASSN_ENTSUMPROJID, ASSN_UID) WITH FILLFACTOR = 40
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_PROJECTS
--****************************************************************************************************

IF NOT EXISTS (select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'MSP_PROJECTS' AND COLUMN_NAME = 'PROJ_CREATION_DATE_EX')
BEGIN
	ALTER TABLE dbo.MSP_PROJECTS ADD
		PROJ_CREATION_DATE_EX   		datetime        	NULL
END
GO


IF NOT EXISTS (select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'MSP_PROJECTS' AND COLUMN_NAME = 'PROJ_ACTUALS_SYNCH')
BEGIN
	ALTER TABLE dbo.MSP_PROJECTS ADD
		PROJ_ACTUALS_SYNCH					tinyint						NULL
END
GO


IF NOT EXISTS (select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'MSP_PROJECTS' AND COLUMN_NAME = 'PROJ_ADMINPROJECT')
BEGIN
	ALTER TABLE dbo.MSP_PROJECTS ADD
		PROJ_ADMINPROJECT						tinyint						NULL
END
GO


IF NOT EXISTS (select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'MSP_PROJECTS' AND COLUMN_NAME = 'PROJ_ENT_LIST_SEPARATOR')
BEGIN
	ALTER TABLE dbo.MSP_PROJECTS ADD
		PROJ_ENT_LIST_SEPARATOR     varchar(2)        NULL
END
GO


IF NOT EXISTS (select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'MSP_PROJECTS' AND COLUMN_NAME = 'PROJ_IGNORE_FRONT_END')
BEGIN
	ALTER TABLE dbo.MSP_PROJECTS ADD
		PROJ_IGNORE_FRONT_END       bit               NULL DEFAULT 0
END
GO

--****************************************************************************************************
-- When upgrading from 10-10.5, 10.5-11 or 10-11, trigger will be added.
--****************************************************************************************************

IF EXISTS (SELECT id FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.MSP_PROJECTS_AI_TRG')
AND OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER dbo.MSP_PROJECTS_AI_TRG
GO 


CREATE TRIGGER dbo.MSP_PROJECTS_AI_TRG
ON dbo.MSP_PROJECTS
FOR INSERT
AS 

	DECLARE @new_PROJ_ID int
	DECLARE @new_PROJ_TYPE int
	DECLARE @old_PROJ_ENT_LIST_SEPARATOR varchar(2)

	SELECT @new_PROJ_ID = PROJ_ID, @new_PROJ_TYPE = PROJ_TYPE  FROM inserted

	IF @new_PROJ_TYPE <> 102
	BEGIN 
		RETURN
	END
		  
	SELECT @old_PROJ_ENT_LIST_SEPARATOR = PROJ_ENT_LIST_SEPARATOR FROM MSP_PROJECTS WHERE PROJ_TYPE = 2
	UPDATE MSP_PROJECTS SET PROJ_ENT_LIST_SEPARATOR = @old_PROJ_ENT_LIST_SEPARATOR WHERE PROJ_ID = @new_PROJ_ID

GO
--**************************************************************************************************
--*		MSP_MV_FIELDS
--**************************************************************************************************

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_MV_FIELDS')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
	CREATE TABLE dbo.MSP_MV_FIELDS 
	( 
		RESERVED_DATA 				char(1)		NULL, 
		PROJ_ID 							int				NULL, 
		CODE_MV_UID 					int				NULL, 
		CODE_CATEGORY 				int				NULL, 
		CODE_REF_UID 					int				NULL, 
		CODE_FIELD_ID 				int				NULL, 
		CODE_UID 							int				NULL 
	) 
	-- ON [PRIMARY]
GO


IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'I_MSP_MV_FIELDS')
	CREATE CLUSTERED INDEX I_MSP_MV_FIELDS 
	ON dbo.MSP_MV_FIELDS (PROJ_ID, CODE_MV_UID) WITH FILLFACTOR = 40
	-- ON [PRIMARY]
GO

--**************************************************************************************************
--*		MSP_TASKS
--**************************************************************************************************

IF NOT EXISTS (select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'MSP_TASKS' AND COLUMN_NAME = 'TASK_ACT_WORK_PROT')
BEGIN
	ALTER TABLE dbo.MSP_TASKS ADD
		TASK_ACT_WORK_PROT 						decimal(25,6) 		NULL
END
GO


IF NOT EXISTS (select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'MSP_TASKS' AND COLUMN_NAME = 'TASK_ACT_OVT_WORK_PROT')
BEGIN
	ALTER TABLE dbo.MSP_TASKS ADD
		TASK_ACT_OVT_WORK_PROT 				decimal(25,6) 		NULL
END
GO

--**************************************************************************************************
--*		MSP_RESGLOBAL_SECURITY
--**************************************************************************************************

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_RESGLOBAL_SECURITY')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	CREATE TABLE dbo.MSP_RESGLOBAL_SECURITY
	( 
		SEC_RES_UID				int			NULL,
		SEC_CAL_UID				int			NULL
	) 
	-- ON [PRIMARY]
	INSERT INTO dbo.MSP_RESGLOBAL_SECURITY (SEC_RES_UID, SEC_CAL_UID) VALUES (0, 0)
END
GO

--**************************************************************************************************
--*		MSP_RES_GLOBAL_BINARY
--**************************************************************************************************

IF EXISTS (SELECT name FROM sysindexes WHERE name = 'I_MSP_RES_GLOBA')
	DROP INDEX dbo.MSP_RES_GLOBAL_BINARY.I_MSP_RES_GLOBA
GO

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'I_MSP_RES_GLOBAL_BINARY')
	CREATE CLUSTERED INDEX I_MSP_RES_GLOBAL_BINARY
	ON dbo.MSP_RES_GLOBAL_BINARY (GLB_REF_ENT_PROJ_ID, GLB_REF_UID) WITH FILLFACTOR = 40
	-- ON [PRIMARY]
GO


--**************************************************************************************************
--*		Temporary stored procedure to copy RBS
--**************************************************************************************************

IF EXISTS (SELECT id FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MSP_COPY_RBS]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.MSP_COPY_RBS
GO


CREATE PROCEDURE dbo.MSP_COPY_RBS
AS
BEGIN

	CREATE TABLE #MSP_TEMP_RBS 
	(
		PROJ_ID					int		NULL,
		CODE_CATEGORY		int		NULL,
		CODE_REF_UID		int		NULL,
		CODE_FIELD_ID		int		NULL,
		CODE_UID				int		NULL
	)

	INSERT INTO #MSP_TEMP_RBS (CODE_REF_UID, CODE_UID) 
		SELECT ENT_ResourceUniqueID, ResourceEnterpriseOutlineCode30ID 
		FROM dbo.MSP_VIEW_RES_ENT 
		WHERE ResourceEnterpriseOutlineCode30ID <> -1 
		AND ENT_ResourceUniqueID IN 
			(SELECT RES_UID 
			 FROM dbo.MSP_RESOURCES 
			 WHERE PROJ_ID = 1) 
			 AND ENT_ResourceUniqueID NOT IN
				(SELECT CODE_REF_UID 
	  		 FROM dbo.MSP_CODE_FIELDS
	  		 WHERE PROJ_ID = 1 
	  		 AND CODE_FIELD_ID = 205521540)
	 
	UPDATE #MSP_TEMP_RBS set PROJ_ID = 1,  CODE_CATEGORY=1, CODE_FIELD_ID = 205521540

	INSERT INTO dbo.MSP_CODE_FIELDS SELECT * FROM #MSP_TEMP_RBS

	UPDATE dbo.MSP_RESOURCES SET RES_ENTERPRISE_TIMESTAMP = RES_ENTERPRISE_TIMESTAMP + 1
	WHERE PROJ_ID = 1 AND RES_UID IN 
		(SELECT CODE_REF_UID FROM #MSP_TEMP_RBS)

	DROP TABLE #MSP_TEMP_RBS

END;
GO


EXEC dbo.MSP_COPY_RBS
GO
 

DROP PROCEDURE dbo.MSP_COPY_RBS
GO



-- End of script




