52 lines
2.4 KiB
Transact-SQL
52 lines
2.4 KiB
Transact-SQL
--Das Overview-SQL muss um folgenden LEFT JOIN erweitert werden
|
|
--#############################################################################################
|
|
--LEFT JOIN TBTF_PROFILE_FILES_WORK PFW ON T.DOC_ID = PFW.DocID AND T.PROFIL_ID = PFW.ProfileID
|
|
--#############################################################################################
|
|
--und einen where-Teil:
|
|
--#############################################################################################
|
|
--(PFW.DocID IS NULL OR (PFW.InWork = 0 AND PFW.Worked = 0))
|
|
|
|
CREATE TABLE TBTF_PROFILE_FILES_WORK
|
|
(
|
|
GUID BIGINT NOT NULL IDENTITY(1,1),
|
|
ProfileID INTEGER NOT NULL,
|
|
DocID BIGINT NOT NULL,
|
|
InWork BIT NOT NULL DEFAULT 0,
|
|
Worked BIT NOT NULL DEFAULT 0,
|
|
Action_When DATETIME,
|
|
Action_UserID INTEGER,
|
|
ChangedWho_ID INTEGER,
|
|
CHANGED_WHEN DATETIME,
|
|
CONSTRAINT PK_TBTF_PROFILE_FILES_WORK PRIMARY KEY (GUID),
|
|
CONSTRAINT UQ_TBTF_PROFILE_FILES_WORK UNIQUE(ProfileID,DocID)
|
|
)
|
|
GO
|
|
|
|
CREATE PROCEDURE PRTF_PROFILE_FILES_WORK @pDocID BIGINT,@pProfilID INTEGER,@pUserID INTEGER, @pMode VARCHAR(100)
|
|
AS
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT GUID FROM TBTF_PROFILE_FILES_WORK WHERE ProfileID = @pProfilID AND DocID = @pDocID)
|
|
BEGIN
|
|
INSERT INTO TBTF_PROFILE_FILES_WORK (ProfileID,DocID,Action_UserID,Action_When) VALUES (@pProfilID,@pDocID,@pUserID,GETDATE());
|
|
END
|
|
IF @pMode = 'FreeFile'
|
|
BEGIN
|
|
PRINT 'FreeFile ...'
|
|
UPDATE TBTF_PROFILE_FILES_WORK SET InWork = 0, Action_UserID = @pUserID
|
|
WHERE DocID = @pDocID AND ProfileID = @pProfilID AND InWork = 1;
|
|
END
|
|
ELSE IF @pMode = 'InWork'
|
|
UPDATE TBTF_PROFILE_FILES_WORK SET InWork = 1, Action_When = GETDATE(), Action_UserID = @pUserID
|
|
WHERE DocID = @pDocID AND ProfileID = @pProfilID AND InWork = 0;
|
|
ELSE IF @pMode = 'Worked'
|
|
UPDATE TBTF_PROFILE_FILES_WORK SET InWork = 0,Worked = 1, Action_When = GETDATE(), Action_UserID = @pUserID
|
|
WHERE DocID = @pDocID AND ProfileID = @pProfilID AND Worked = 0;
|
|
END
|
|
GO
|
|
UPDATE TBDD_MODULES SET DB_VERSION = '2.4.2.0' where NAME = 'Process-Manager';
|
|
--#############################################################################################
|
|
IF NOT EXISTS(SELECT GUID FROM [DD_ECM].[dbo].[TBPM_KONFIGURATION] where [SQL_PROFILE_MAIN_VIEW] like '%(PFW.DocID IS NULL OR (PFW.InWork = 0 AND PFW.Worked = 0))%')
|
|
PRINT 'ACHTUNG: Bitte erste Anweisung in Skript beachten: Es fehlt eine Erweiterung im Overview SQL!!!'
|
|
GO
|
|
|