SqlScripts/01_SMART_UP_TOOLS/02_TASKFLOW/Updates/Added2MainScript/2.2.2.2.sql

UPDATE TBDD_MODULES SET DB_VERSION = '2.2.2.2' where NAME = 'Process-Manager'
GO

IF NOT EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N'FORMAT_STRING'
          AND Object_ID = Object_ID(N'dbo.TBPM_PROFILE_CONTROLS'))
BEGIN
    -- Column Exists
	ALTER TABLE TBPM_PROFILE_CONTROLS ADD FORMAT_STRING VARCHAR(200) NOT NULL DEFAULT ''
END
ELSE
	PRINT 'COLUMN FORMAT_STRING DOES EXIST'
GO
IF NOT EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N'BACKCOLOR_IF'
          AND Object_ID = Object_ID(N'dbo.TBPM_PROFILE_CONTROLS'))
BEGIN
    -- Column Exists
	ALTER TABLE TBPM_PROFILE_CONTROLS ADD BACKCOLOR_IF VARCHAR(500) NOT NULL DEFAULT ''
END
ELSE
	PRINT 'COLUMN BACKCOLOR_IF DOES EXIST'
GO
IF NOT EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N'SAVE_CHANGE_ON_ENABLED'
          AND Object_ID = Object_ID(N'dbo.TBPM_PROFILE_CONTROLS'))
BEGIN
    -- Column Exists
	ALTER TABLE TBPM_PROFILE_CONTROLS ADD SAVE_CHANGE_ON_ENABLED BIT NOT NULL DEFAULT 1
END
ELSE
	PRINT 'COLUMN SAVE_CHANGE_ON_ENABLED DOES EXIST'
GO
DECLARE @SQL NVARCHAR(max)
IF EXISTS (SELECT name FROM master.sys.databases WHERE name = N'IDB')
	BEGIN
		SET @SQL = 'CREATE OR ALTER VIEW [dbo].[VWPM_CONTROL_INDEX] AS
		SELECT TOP 100 PERCENT
			T1.GUID,
			T.GUID				PROFIL_ID,
			T.NAME				PROFIL_NAME,
			T.LOG_INDEX,
			T1.NAME				CTRL_NAME,
			T1.CTRL_TYPE,
			T1.CTRL_TEXT,
			T1.X_LOC,
			T1.Y_LOC,
			T1.FONT_COLOR,
			T1.FONT_FAMILY,
			T1.FONT_SIZE,
			T1.FONT_STYLE,
			T1.WIDTH,
			T1.HEIGHT,
			COALESCE(T1.INDEX_NAME,'''') INDEX_NAME,
			COALESCE((SELECT TYP_ID FROM IDB.dbo.TBIDB_ATTRIBUTE WHERE TITLE = COALESCE(T1.INDEX_NAME,'''')),''0'') AS IDB_TYP,
			T1.VALIDATION,
			T1.CHOICE_LIST,
			T1.TYP,
			T1.CONNECTION_ID,
			CASE  WHEN T1.[SQL_UEBERPRUEFUNG] IS NULL THEN '''' ELSE T1.[SQL_UEBERPRUEFUNG] END AS [SQL_UEBERPRUEFUNG],
			T1.[READ_ONLY],
			T1.LOAD_IDX_VALUE,
			T1.DEFAULT_VALUE,
			T1.REGEX_MATCH,
			T1.REGEX_MESSAGE_DE,
			T1.REGEX_MESSAGE_EN,
			T1.OVERWRITE_DATA,
			T1.SET_CONTROL_DATA,
			T1.SAVE_CHANGE_ON_ENABLED,
			T1.FORMAT_STRING			CTRL_FORMAT_STRING,
			T1.BACKCOLOR_IF				CTRL_BACKCOLOR_IF
		FROM 
			TBPM_PROFILE T,
			TBPM_PROFILE_CONTROLS T1
		WHERE
			T.GUID = T1.PROFIL_ID
			AND T1.CONTROL_ACTIVE = 1
		ORDER BY 
			T.GUID,
			T1.X_LOC,
			T1.Y_LOC'
	END
ELSE
	BEGIN
	SET @SQL = 'ALTER VIEW [dbo].[VWPM_CONTROL_INDEX] AS
	SELECT TOP 100 PERCENT
		T1.GUID,
		T.GUID				PROFIL_ID,
		T.NAME				PROFIL_NAME,
		T.LOG_INDEX,
		T1.NAME				CTRL_NAME,
		T1.CTRL_TYPE,
		T1.CTRL_TEXT,
		T1.X_LOC,
		T1.Y_LOC,
		T1.FONT_COLOR,
		T1.FONT_FAMILY,
		T1.FONT_SIZE,
		T1.FONT_STYLE,
		T1.WIDTH,
		T1.HEIGHT,
		COALESCE(T1.INDEX_NAME,'''') INDEX_NAME,
		--COALESCE((SELECT TYP_ID FROM IDB.dbo.TBIDB_ATTRIBUTE WHERE TITLE = COALESCE(T1.INDEX_NAME,'''')),''0'') AS IDB_TYP,
		T1.VALIDATION,
		T1.CHOICE_LIST,
		T1.TYP,
		T1.CONNECTION_ID,
		CASE  WHEN T1.[SQL_UEBERPRUEFUNG] IS NULL THEN '''' ELSE T1.[SQL_UEBERPRUEFUNG] END AS [SQL_UEBERPRUEFUNG],
		T1.[READ_ONLY],
		T1.LOAD_IDX_VALUE,
		T1.DEFAULT_VALUE,
		T1.REGEX_MATCH,
		T1.REGEX_MESSAGE_DE,
		T1.REGEX_MESSAGE_EN,
		T1.OVERWRITE_DATA,
		T1.SET_CONTROL_DATA,
		T1.SAVE_CHANGE_ON_ENABLED,
		T1.FORMAT_STRING			CTRL_FORMAT_STRING,
		T1.BACKCOLOR_IF				CTRL_BACKCOLOR_IF
	FROM 
		TBPM_PROFILE T,
		TBPM_PROFILE_CONTROLS T1
	WHERE
		T.GUID = T1.PROFIL_ID
		AND T1.CONTROL_ACTIVE = 1
	ORDER BY 
		T.GUID,
		T1.X_LOC,
		T1.Y_LOC'
END

EXEC (@SQL)

GO

ALTER VIEW [dbo].[VWPM_PROFILE_USER_UNION] AS
SELECT 
	'USER'				SCOPE,
	T.GUID				PROFIL_ID,
	T.NAME				PROFIL_NAME,
	T.TITLE,
	T.DESCRIPTION,
	T.WD_OBJECTTYPE,
	T.PRIORITY,
	T.WD_SEARCH,
	COALESCE(T.MOVE2FOLDER,'') as MOVE2Folder,
	T.ACTIVE,
	T.IN_WORK,
	T.NO_OF_DOCUMENTS,
	T.FINAL_PROFILE,
	T.FINAL_TEXT,
	T.LOG_INDEX,
	T.PM_VEKTOR_INDEX,
	T.DISPLAY_MODE,
	T2.GUID AS USER_ID,
	T2.PRENAME,
	T2.NAME,
	T2.USERNAME,
	T2.EMAIL,
	--T2.PM_RIGHT_FILE_DELETE as RIGHT_FILE_DELETE,
	[dbo].[FNPM_PROFILE_GROUP_COLOR] (T.GUID) AS 'PROFILE_GROUP_COLOR',
	[dbo].[FNPM_PROFILE_GROUP_TEXT] (T.GUID) AS 'PROFILE_GROUP_TEXT'
FROM 
	TBPM_PROFILE T,
	TBPM_PROFILE_USER T1,
	TBDD_USER T2
WHERE
	T.TYPE = 1 AND
	T.GUID = T1.PROFIL_ID AND
	T1.USER_ID = T2.GUID
UNION
SELECT 
	'GROUP'				SCOPE,
	T.GUID				PROFIL_ID,
	T.NAME				PROFIL_NAME,
	T.TITLE,
	T.DESCRIPTION,
	T.WD_OBJECTTYPE,
	T.PRIORITY,
	T.WD_SEARCH,
	COALESCE(T.MOVE2FOLDER,'') as MOVE2Folder,
	T.ACTIVE,
	T.IN_WORK,
	T.NO_OF_DOCUMENTS,
	T.FINAL_PROFILE,
	T.FINAL_TEXT,
	T.LOG_INDEX,
	T.PM_VEKTOR_INDEX,
	T.DISPLAY_MODE,
	T4.GUID AS USER_ID,
	T4.PRENAME,
	T4.NAME,
	T4.USERNAME,
	T4.EMAIL,
	--T4.PM_RIGHT_FILE_DELETE as RIGHT_FILE_DELETE,
	[dbo].[FNPM_PROFILE_GROUP_COLOR] (T.GUID) AS 'PROFILE_GROUP_COLOR',
	[dbo].[FNPM_PROFILE_GROUP_TEXT] (T.GUID) AS 'PROFILE_GROUP_TEXT'
FROM 
	TBPM_PROFILE T,
	TBPM_PROFILE_GROUP T1,
	TBDD_GROUPS T2,
	TBDD_GROUPS_USER T3,
	TBDD_USER T4
WHERE
	T.TYPE = 1 AND
	T.GUID = T1.PROFIL_ID AND
	T1.GROUP_ID = T2.GUID AND
	T2.GUID = T3.GROUP_ID AND
	T3.USER_ID = T4.GUID

GO