Post date: Mar 26, 2015 3:15:3 PM
Early version of the ObjectState table included RoleRequired, enabling a power user to determine which role was required to effect a state transition. This has been deprecated in favor of PermissionRequired, enabling a power use to grant a permissions to a specific transition to multiple roles.
To migrate from one to the other, run the query below. It will:
Create SystemFunctions based on {ImportFormTemplate}{StatusFrom}To{StatusTo}
Grant the appropriate Role permission to the function
Set the ObjectState.PermissionRequired column to the new value
DECLARE @T TABLE (ID int, RoleRequired nvarchar(50), Permission nvarchar(50))
INSERT INTO @T
SELECT
ObjectStateID,
RoleRequired,
REPLACE(
REPLACE(
REPLACE(LEFT(ImportFormTEmplate, 50-LEN(ISNULL(LEFT(StatusFrom,15), 'None'))-LEN(ISNULL(LEFT(StatusTo,15), 'None'))) + ISNULL(LEFT(StatusFrom, 15), 'None') + 'To' + ISNULL(LEFT(StatusTo,15), 'None'), ' ', ''),
'/', ''
),
'-', ''
)
FROM ObjectState
INNER JOIN ImportFormTemplate
ON ImportFormTemplateID = ObjectState.TemplateID
WHERE RoleREquired IS NOT NULL
AND PermissionRequired IS NULL
SELECT * FROM @T
INSERT INTO SystemFunction (SystemFunction)
SELECT Permission FROM @T AS T WHERE NOT EXISTS (
SELECT 1 FROM SystemFunction WHERE SystemFunction = Permission
)
--
-- INSERT INTO SystemPermission (SystemRoleID, SystemFunctionID, UpdatedPersonID, UpdatedDate)
SELECT SystemRole.SystemRoleID,
SystemFunction.SystemFunctionID,
1,
GETDATE()
FROM @T AS T
INNER JOIN SystemRole
ON SystemRole.SystemRole = T.RoleREquired
LEFT OUTER JOIN SystemFunction
ON SystemFunction.SystemFunction = T.Permission
WHERE NOT EXISTS (
SELECT 1 FROM SystemPermission
WHERE SystemRoleID = SystemRole.SystemRoleID
AND SystemFunctionID = SystemFunction.SystemFunctionID
)
UPDATE ObjectState
SET PermissionRequired = Permission
FROM @T AS T
INNER JOIN OBjectState
ON ObjectSTateID= T.ID