Решение найдет свое применение в тех случаях, когда есть необходимость отменять зависшие экземпляры процессов.
Вся основная логика лежит на хранимых процедурах: tsp_DeleteSysProcessDataById, tsp_DeleteSysProcessDataByStartDate, tsp_UpdateSysProcessLogById, tsp_UpdateSysProcessLogByStartDate
В общих словах про процедуры
tsp_DeleteSysProcessDataById – удаляет информацию по процессу из таблиц SysActivityPrcEl, SysEntityCommonPrcEl, SysProcessIntermediateEvent, SysProcessData
tsp_DeleteSysProcessDataByStartDate – читает процессы, в логе фильтрует по дате, и запускает DeleteSysProcessDataById
tsp_UpdateSysProcessLogById – обновляет информацию по процессу из таблиц SysProcessLog, SysProcessElementLog,
tsp_UpdateSysProcessLogByStartDate – читает процессы, в логе фильтрует по дате, и запускает UpdateSysProcessLogById
Бизнес-процесс который позволяет выбрать процесс, указать период, отменить процессы и выдать результат (к-ство отменных процессов)
Ниже отдельно SQL скрипты самих процедур
Скрипт на удаление:
SET QUOTED_IDENTIFIER ON
GO
IF NOT OBJECT_ID('[dbo].[tsp_DeleteSysProcessDataById]') IS NULL
BEGIN
DROP PROCEDURE [dbo].[tsp_DeleteSysProcessDataById]
END
GO
CREATE PROCEDURE [dbo].[tsp_DeleteSysProcessDataById]
@SysProcessDataId uniqueidentifier
AS
BEGIN
SET NOCOUNT ON
DECLARE @sysProcessDataRecordsToDelete TABLE (Id uniqueidentifier)
DECLARE @sysProcessElementDataRecordsToDelete TABLE (Id uniqueidentifier )
INSERT INTO @sysProcessDataRecordsToDelete ([Id])
SELECT Id from SysProcessData
WHERE ParentId = @SysProcessDataId
OPTION (OPTIMIZE FOR (@SysProcessDataId UNKNOWN))
DECLARE @COUNT bigint = (SELECT COUNT_BIG(*) FROM @sysProcessDataRecordsToDelete)
WHILE (@COUNT) != 0
BEGIN
DECLARE @parentId uniqueidentifier = (SELECT TOP 1 Id from @sysProcessDataRecordsToDelete)
DELETE FROM @sysProcessDataRecordsToDelete WHERE Id = @parentId;
EXEC [dbo].[tsp_DeleteSysProcessDataById] @parentId
SET @COUNT = @COUNT - 1
END
INSERT INTO @sysProcessElementDataRecordsToDelete ([Id])
SELECT Id FROM SysProcessElementData
WHERE SysProcessId = @SysProcessDataId
SET @COUNT = (SELECT COUNT_BIG(*) FROM @sysProcessElementDataRecordsToDelete)
DELETE FROM SysActivityPrcEl WHERE ProcessElementId IN (SELECT Id FROM @sysProcessElementDataRecordsToDelete)
DELETE FROM SysEntityCommonPrcEl WHERE ProcessElementId IN (SELECT Id FROM @sysProcessElementDataRecordsToDelete)
DELETE FROM SysProcessIntermediateEvent WHERE SysProcessElementId IN (SELECT Id FROM @sysProcessElementDataRecordsToDelete)
DELETE FROM SysProcessData WHERE Id = @SysProcessDataId
OPTION (OPTIMIZE FOR (@SysProcessDataId UNKNOWN))
END
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT OBJECT_ID('[dbo].[tsp_DeleteSysProcessDataByStartDate]') IS NULL
BEGIN
DROP PROCEDURE [dbo].[tsp_DeleteSysProcessDataByStartDate]
END
GO
CREATE PROCEDURE [dbo].[tsp_DeleteSysProcessDataByStartDate]
@ProcessName nvarchar(MAX),
@StartDate datetime,
@EndDate datetime
AS
BEGIN
SET NOCOUNT ON
IF NOT OBJECT_ID('#SysProcessDataId') IS NULL
BEGIN
DROP TABLE #SysProcessDataId
END
CREATE TABLE #SysProcessDataId (Id uniqueidentifier)
INSERT INTO #SysProcessDataId ([Id])
SELECT DISTINCT SysProcessData.Id FROM SysProcessLog
JOIN SysProcessData on SysProcessLog.SysSchemaId = SysProcessData.SysSchemaId
WHERE SysProcessData.ParentId IS NULL
AND CONVERT(date, SysProcessLog.StartDate) >= CONVERT(date, @StartDate) and CONVERT(date, SysProcessLog.StartDate) <= CONVERT(date, @EndDate)
AND SysProcessLog.Name = @ProcessName
AND SysProcessLog.StatusId = 'ED2AE277-B6E2-DF11-971B-001D60E938C6'
DECLARE SysProcessDataIdCursor CURSOR STATIC LOCAL FOR
SELECT
Id
FROM
#SysProcessDataId
DECLARE @COUNT int = (SELECT COUNT_BIG(*) FROM #SysProcessDataId)
IF (@COUNT > 50000)
SET @COUNT = 50000
DECLARE @processDataId uniqueidentifier
OPEN SysProcessDataIdCursor
WHILE (@COUNT > 0)
BEGIN
FETCH NEXT FROM SysProcessDataIdCursor INTO @processDataId
IF @@FETCH_STATUS = -1 BREAK
IF @@FETCH_STATUS = -2 CONTINUE
EXEC [dbo].[tsp_DeleteSysProcessDataById] @processDataId
SET @COUNT = @COUNT - 1
END
CLOSE SysProcessDataIdCursor
DEALLOCATE SysProcessDataIdCursor
IF NOT OBJECT_ID('#SysProcessDataId') IS NULL
BEGIN
DROP TABLE #SysProcessDataId
END
END
GO
Скрипт на обновление:
SET QUOTED_IDENTIFIER ON
GO
IF NOT OBJECT_ID('[dbo].[tsp_UpdateSysProcessLogById]') IS NULL
BEGIN
DROP PROCEDURE [dbo].[tsp_UpdateSysProcessLogById]
END
GO
CREATE PROCEDURE [dbo].[tsp_UpdateSysProcessLogById]
@SysProcessLogId uniqueidentifier
AS
BEGIN
SET NOCOUNT ON
DECLARE @sysProcessLogRecordsToDelete TABLE (Id uniqueidentifier)
INSERT INTO @sysProcessLogRecordsToDelete ([Id])
SELECT Id FROM SysProcessLog
WHERE ParentId = @SysProcessLogId
OPTION (OPTIMIZE FOR (@SysProcessLogId UNKNOWN))
DECLARE @COUNT int = (SELECT COUNT(*) FROM @sysProcessLogRecordsToDelete)
WHILE (@COUNT) != 0
BEGIN
DECLARE @parentId uniqueidentifier = (SELECT TOP 1 Id from @sysProcessLogRecordsToDelete)
Update SysProcessLog
Set StatusId=( select id from SysProcessStatus where id = '1BE78F3E-234D-4D6A-869A-DC07253FD2F3' )
Where id =@parentId
EXEC [dbo].[tsp_UpdateSysProcessLogById] @parentId
SET @COUNT = @COUNT - 1
END
Update SysProcessLog
Set CompleteDate = GETUTCDATE(), StatusId=( select id from SysProcessStatus where id = '1BE78F3E-234D-4D6A-869A-DC07253FD2F3' )
Where id =@SysProcessLogId AND StatusId = 'ED2AE277-B6E2-DF11-971B-001D60E938C6'
UPDATE SysProcessElementLog Set StatusId = (select Id from SysProcessStatus where Id = '1BE78F3E-234D-4D6A-869A-DC07253FD2F3'), CompleteDate = GETUTCDATE() where SysProcessId = @SysProcessLogId
and StatusId= 'ED2AE277-B6E2-DF11-971B-001D60E938C6'
END
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT OBJECT_ID('[dbo].[tsp_UpdateSysProcessLogByStartDate]') IS NULL
BEGIN
DROP PROCEDURE [dbo].[tsp_UpdateSysProcessLogByStartDate]
END
GO
CREATE PROCEDURE [dbo].[tsp_UpdateSysProcessLogByStartDate]
@ProcessName nvarchar(MAX),
@StartDate datetime,
@EndDate datetime
AS
BEGIN
SET NOCOUNT ON
IF NOT OBJECT_ID('#SysProcessLogId') IS NULL
BEGIN
DROP TABLE #SysProcessLogId
END
CREATE TABLE #SysProcessLogId (Id uniqueidentifier)
INSERT INTO #SysProcessLogId ([Id])
SELECT Top 100000 SysProcessLog.Id FROM SysProcessLog
WHERE SysProcessLog.ParentId IS NULL
AND CONVERT(date, SysProcessLog.StartDate) >= CONVERT(date, @StartDate)
AND CONVERT(date, SysProcessLog.StartDate) <= CONVERT(date, @EndDate)
AND SysProcessLog.Name = @ProcessName
CREATE TABLE #SysProcessExec (Id uniqueidentifier)
INSERT INTO #SysProcessExec ([Id])
SELECT Top 100000 SysProcessLog.Id FROM SysProcessLog
WHERE SysProcessLog.ParentId IS NULL
AND CONVERT(date, SysProcessLog.StartDate) >= CONVERT(date, @StartDate)
AND CONVERT(date, SysProcessLog.StartDate) <= CONVERT(date, @EndDate)
AND SysProcessLog.Name = @ProcessName
AND SysProcessLog.StatusId = 'ED2AE277-B6E2-DF11-971B-001D60E938C6'
DECLARE SysProcessLogIdCursor CURSOR STATIC LOCAL FOR
SELECT
Id
FROM
#SysProcessLogId
DECLARE @COUNT int = (SELECT COUNT_BIG(*) FROM #SysProcessLogId)
DECLARE @processLogId uniqueidentifier
OPEN SysProcessLogIdCursor
WHILE (@COUNT > 0)
BEGIN
FETCH NEXT FROM SysProcessLogIdCursor INTO @processLogId
IF @@FETCH_STATUS = -1 BREAK
IF @@FETCH_STATUS = -2 CONTINUE
EXEC [dbo].[tsp_UpdateSysProcessLogById] @processLogId
SET @COUNT = @COUNT - 1
END
CLOSE SysProcessLogIdCursor
DEALLOCATE SysProcessLogIdCursor
INSERT INTO MassCancellationProcessesLog (StartDate, EndDate, DisableProcessCount, Process) VALUES(CONVERT(date, @StartDate), CONVERT(date, @EndDate), (SELECT COUNT(*) FROM #SysProcessExec), @ProcessName)
IF NOT OBJECT_ID('#SysProcessLogId') IS NULL
BEGIN
DROP TABLE #SysProcessLogId
END
IF NOT OBJECT_ID('#SysProcessExec') IS NULL
BEGIN
DROP TABLE #SysProcessExec
END
END
GO
