| Note |
|---|
Remember to take a back backup first, and test of with Test before you touch Prod! |
...
The script takes the stage assignee from the current asset status “owner”. If no “owner” is specified, then the task is left unassigned.
The title of the tasks is assigned to the English title for the asset. If the asset doesn’t have a title, then no task will be created.
The script is safe to run multiple times since it will skip assets that already have a task for the specified business workflow.
If something fails when the script runs, all changes are undone, and the system is left as it was before the script was run.
| Note |
|---|
Remember to take a back backup first, and test of with Test before you touch Prod! |
| Code Block | ||
|---|---|---|
| ||
declare @statusMapping table(statusValue nvarchar(max), stageUniqueId nvarchar(max), retired bit);
----- Set these parameters ------
declare @instanceOwnerId int = 10000;
declare @businessWorkflowId int = 2;
insert into @statusMapping(statusValue, stageUniqueId, retired)
values ('#54a0ff;creative', 'creative', 0),
('#1dd1a1;approved', 'approved', 0),
('#ee5253;expired', 'expired', 1);
----- Script, do not touch ------
declare @assetStatusLabelId int;
select @assetStatusLabelId = iml.item_metafield_labelid from item_metafield_label iml
join item_metafield im on iml.item_metafieldid = im.item_metafieldid
join item_item_metafield iim on im.item_metafieldid = iim.item_metafieldid
join item i on i.itemid = iim.itemid
where ItemGuid = '6fe35f10-c810-497b-af9c-e52c3d583593'
and iml.languageid = 3
declare @assetTitleLabelId int;
select @assetTitleLabelId = iml.item_metafield_labelid from item_metafield_label iml
join item_metafield im on iml.item_metafieldid = im.item_metafieldid
join item_item_metafield iim on im.item_metafieldid = iim.item_metafieldid
join item i on i.itemid = iim.itemid
where ItemGuid = '5eb3eefc-a043-410f-89b0-29ed3ef37078'
and iml.languageid = 3
declare @assetStatusOwnerLabelId int;
select @assetStatusOwnerLabelId = iml.item_metafield_labelid from item_metafield_label iml
join item_metafield im on iml.item_metafieldid = im.item_metafieldid
join item_item_metafield iim on im.item_metafieldid = iim.item_metafieldid
join item i on i.itemid = iim.itemid
where ItemGuid = 'fc948200-c103-41fb-8dae-bd1c5c6ce281'
and iml.languageid = 3
declare @workflowVersionId int;
select @workflowVersionId = versions.Id from BusinessWorkflowService_WorkflowDefinitionVersions versions
where versions.WorkflowDefinitionId = @businessWorkflowId;
declare @existingTasks table(itemId int INDEX IX1 CLUSTERED);
insert into @existingTasks(itemId)
select items.ItemId from BusinessWorkflowService_WorkflowInstanceItems items
join BusinessWorkflowService_WorkflowProjectInstances instances on instances.Id = items.WorkflowInstanceId
where instances.WorkflowDefinitionVersionId = @workflowVersionId;
declare @assets table (assetId int, itemId int, uniqueStageId nvarchar(max), title nvarchar(max), retired bit, createdAt datetime, statusUpdatedAt datetime, stageOwnerId int)
insert into @assets(assetId, itemId, uniqueStageId, title, retired, createdAt, statusUpdatedAt, stageOwnerId)
select a.assetid, ia.itemid, map.stageUniqueId, imv_title.value, map.retired, a.upload_date, imv_status.DateModified, im.memberid from asset a
join item_asset ia on a.assetid = ia.assetid
join item i on ia.itemid = i.itemid
join item_metafield_value imv_status on i.itemid = imv_status.itemid
join item_combo_value icv on imv_status.item_metafield_labelid = icv.item_metafield_labelid and imv_status.item_combo_valueid = icv.item_combo_valueid
join @statusMapping map on map.statusValue = icv.optionvalue
join item_metafield_value imv_title on i.itemid = imv_title.itemid
left join item_metafield_value imv_owner on i.itemid = imv_owner.itemid and imv_owner.item_metafield_labelid = @assetStatusOwnerLabelId
left join item_member im on im.itemid = imv_owner.ref_itemid
where imv_status.item_metafield_labelid = @assetStatusLabelId
and imv_title.item_metafield_labelid = @assetTitleLabelId
and not exists(select t.itemid from @existingTasks t where t.itemId = ia.itemid)
begin transaction;
declare @createdOwners table(memberId int, ownerId int);
insert into BusinessWorkflowService_Owners(Discriminator, MemberOwner_MemberId)
output inserted.MemberOwner_MemberId, inserted.Id into @createdOwners
select distinct 'MemberOwner', a.stageOwnerId from @assets a
where a.stageOwnerId is not null
insert into BusinessWorkflowService_Owners(Discriminator, MemberOwner_MemberId)
values ('MemberOwner', @instanceOwnerId)
declare @instanceOwnerOwnerId int = scope_identity();
declare @createdTasks table(assetId int, itemId int, taskId int);
merge BusinessWorkflowService_WorkflowProjectInstances as target
using @assets as src
left join @createdOwners owner on src.stageOwnerId = owner.memberId
on (1 = 0)
when not matched then
insert (WorkflowDefinitionVersionId, CurrentStageName, CreatedAt, Guid, InstanceOwnerId, LastModified, StageOwnerId, Retired, Name, CurrentStageEnteredAt)
values (@workflowVersionId, src.uniqueStageId, src.createdAt, newid(), @instanceOwnerOwnerId, src.statusUpdatedAt, owner.ownerId, src.retired, src.title, src.statusUpdatedAt)
output src.assetId, src.itemId, inserted.Id into @createdTasks(assetId, itemId, taskId);
insert into BusinessWorkflowService_WorkflowInstanceItems(WorkflowInstanceId, ItemType, ItemBaseId, ItemId)
select t.taskId, 'asset', t.assetId, t.itemId from @createdTasks t;
commit transaction;
|
| Note |
|---|
Remember to take a back backup first, and test of with Test before you touch Prod! |