Close workflow tickets via query

From SMT-X
Jump to: navigation, search

CREATE TABLE [ProcessInstanceToClose] ( [Id] int NOT NULL, CONSTRAINT PK_ProcessInstanceToClose PRIMARY KEY ([Id]) );

INSERT INTO [ProcessInstanceToClose] ([Id]) VALUES (1);

GO

DECLARE @ProcessInstanceId int, @ProcessInstanceLogId int, @DefaultLanguageId int, @LogText nvarchar(255);

DECLARE ticketcursor CURSOR FOR SELECT [Id] FROM [ProcessInstance] WHERE [Id] IN (SELECT [Id] FROM [ProcessInstanceToClose]) AND [DateEnded] IS NULL;

SELECT @DefaultLanguageId = [Id] FROM [Language] where [IsDefault]=1; SET @LogText = 'Auto closed by system';

OPEN ticketcursor FETCH NEXT FROM ticketcursor INTO @ProcessInstanceId

WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO [ProcessInstanceLog] ([ProcessInstanceId],[DateAdded],[Type]) VALUES (@ProcessInstanceId,GETDATE(),'ClosedBySystem'); INSERT INTO [ProcessInstanceLogLanguage] ([ProcessInstanceLogId], [LanguageId], [LogText]) VALUES (SCOPE_IDENTITY(), @DefaultLanguageId, @LogText);


FETCH NEXT FROM ticketcursor INTO @ProcessInstanceId END

CLOSE ticketcursor DEALLOCATE ticketcursor

GO

UPDATE [ProcessISInstanceActor] SET [DateEnded] = GETDATE(), [AutoEnded] = 1 WHERE [ProcessInstanceStepInstanceId] IN (SELECT [Id] FROM [ProcessInstanceStepInstance] WHERE [ProcessInstanceStepId] IN (SELECT [Id] FROM [ProcessInstanceStep] WHERE [ProcessInstanceId] IN (SELECT [Id] FROM [ProcessInstanceToClose]))) AND [DateEnded] IS NULL;

UPDATE [ProcessInstanceStepInstance] SET [DateEnded] = GETDATE() WHERE [ProcessInstanceStepId] IN (SELECT [Id] FROM [ProcessInstanceStep] WHERE [ProcessInstanceId] IN (SELECT [Id] FROM [ProcessInstanceToClose])) AND [DateEnded] IS NULL;

UPDATE [ProcessInstance] SET [DateEnded] = GETDATE() WHERE [Id] IN (SELECT [Id] FROM [ProcessInstanceToClose]) AND [DateEnded] IS NULL;

GO

DROP TABLE [ProcessInstanceToClose];