TEC-23 – Ajout des champs du demandeur et de l’utilisateur final sur toutes les tâches et validations

Version(s)

ECALDIMA 2.0 et supérieure

Description

Lors d’une validation ou d’une tâche il s’avère pratique de voir apparaitre les données du demandeur et de l’utilisateur final d’une demande de service. C’est champs peuvent être ajoutés manuellement depuis un click de droite sur la validation ou la tâche dans l’arbre de processus du Concepteur. Si l’on souhaite faire apparaitre ces champs systématiquement, le script ci-dessous permettra de le faire en une seule action.

Si l’on souhaite faire apparaitre ces champs systématiquement, le script ci-dessous permettra de le faire en une seule action.

Installation

Les valeurs des variables au début du script peuvent être modifiées afin de déterminer les champs qui doivent apparaitre. Par défaut ce sont les champs Société, Service et Emplacement du demandeur et de l’utilisateur final qui seront associés à toutes les validations et tâches. Indiquer la valeur 0 (zéro) pour ne pas utiliser un champ. Le script peut être exécuté plusieurs fois. La modification sera visible sur les nouvelles demandes.

Utilisation

USE ECALDIMA
GO
--Parameters
--Set the parameter to 1 to associate the field to the tasks and validations. Otherwise set it to 0 (zero)
Declare @AddRequesterCompany int = 1
Declare @AddRequesterDepartment int = 1
Declare @AddRequesterLocation int = 1
Declare @AddEndUserCompany int = 1
Declare @AddEndUserDepartment int = 1
Declare @AddEndUserLocation int = 1

--End of parameters


Declare @ServiceId int
Declare @ProcessId int
Declare @HasEndUserField int
Declare @ProcessNodeId int

Declare c_cur CURSOR FOR
SELECT Id, ProcessId FROM [Service]
JOIN Service_ProcessAssociation ON [Service].Id = Service_ProcessAssociation.ServiceId



SELECT @HasEndUserField = 0
OPEN c_cur
FETCH NEXT FROM c_cur INTO @ServiceId,@ProcessId
WHILE @@FETCH_STATUS = 0
BEGIN
       SELECT @HasEndUserField = 0
       SELECT @HasEndUserField = COUNT(*) FROM Service_FieldAssociation WHERE ServiceId = @ServiceId AND FieldId = -7

       Declare obj_cur CURSOR FOR SELECT Id FROM dbo.fn_GetProcessTreeByRootId(@ProcessId) WHERE NodeType IN ('VALIDATION','TASK')
       OPEN obj_cur
       FETCH NEXT FROM obj_cur INTO @ProcessNodeId
       WHILE @@FETCH_STATUS = 0
       BEGIN
              IF NOT EXISTS(SELECT 1 FROM ProcessFieldVisibility WHERE ProcessId = @ProcessNodeId AND FieldId = -3) INSERT ProcessFieldVisibility([ProcessId],[FieldId],[Visible],[Sort]) VALUES (@ProcessNodeId,-3,1,-8)
              IF @AddRequesterCompany = 1 AND NOT EXISTS(SELECT 1 FROM ProcessFieldVisibility WHERE ProcessId = @ProcessNodeId AND FieldId = -12) INSERT ProcessFieldVisibility([ProcessId],[FieldId],[Visible],[Sort]) VALUES (@ProcessNodeId,-12,1,-7)
              IF @AddRequesterDepartment = 1 AND NOT EXISTS(SELECT 1 FROM ProcessFieldVisibility WHERE ProcessId = @ProcessNodeId AND FieldId = -9) INSERT ProcessFieldVisibility([ProcessId],[FieldId],[Visible],[Sort]) VALUES (@ProcessNodeId,-9,1,-5)
              IF @AddRequesterLocation = 1 AND NOT EXISTS(SELECT 1 FROM ProcessFieldVisibility WHERE ProcessId = @ProcessNodeId AND FieldId = -10) INSERT ProcessFieldVisibility([ProcessId],[FieldId],[Visible],[Sort]) VALUES (@ProcessNodeId,-10,1,-6)
              
              If @HasEndUserField = 1
              BEGIN
                     IF NOT EXISTS(SELECT 1 FROM ProcessFieldVisibility WHERE ProcessId = @ProcessNodeId AND FieldId = -7) INSERT ProcessFieldVisibility([ProcessId],[FieldId],[Visible],[Sort]) VALUES (@ProcessNodeId,-7,1,-4)
                     IF @AddEndUserCompany = 1 AND NOT EXISTS(SELECT 1 FROM ProcessFieldVisibility WHERE ProcessId = @ProcessNodeId AND FieldId = -16) INSERT ProcessFieldVisibility([ProcessId],[FieldId],[Visible],[Sort]) VALUES (@ProcessNodeId,-16,1,-3)
                     IF @AddEndUserDepartment = 1 AND NOT EXISTS(SELECT 1 FROM ProcessFieldVisibility WHERE ProcessId = @ProcessNodeId AND FieldId = -13) INSERT ProcessFieldVisibility([ProcessId],[FieldId],[Visible],[Sort]) VALUES (@ProcessNodeId,-13,1,-1)
                     IF @AddEndUserLocation = 1 AND NOT EXISTS(SELECT 1 FROM ProcessFieldVisibility WHERE ProcessId = @ProcessNodeId AND FieldId = -14) INSERT ProcessFieldVisibility([ProcessId],[FieldId],[Visible],[Sort]) VALUES (@ProcessNodeId,-14,1,-2)
              END
              FETCH NEXT FROM obj_cur INTO @ProcessNodeId
       END
       CLOSE obj_cur
       DEALLOCATE obj_cur

       
       FETCH NEXT FROM c_cur INTO @ServiceId,@ProcessId
END
CLOSE c_cur
DEALLOCATE c_cur

Declare @ArticleId int
Declare c_cur CURSOR FOR
SELECT ServiceId, Article_ProcessAssociation.ArticleId, ProcessId 
FROM Article_ProcessAssociation
JOIN Service_ArticleAssociation ON Service_ArticleAssociation.ArticleId = Article_ProcessAssociation.ArticleId

SELECT @HasEndUserField = 0
OPEN c_cur
FETCH NEXT FROM c_cur INTO @ServiceId,@ArticleId,@ProcessId
WHILE @@FETCH_STATUS = 0
BEGIN
       SELECT @HasEndUserField = 0
       SELECT @HasEndUserField = COUNT(*) FROM Service_FieldAssociation WHERE ServiceId = @ServiceId AND FieldId = -7
       Declare obj_cur CURSOR FOR SELECT Id FROM dbo.fn_GetProcessTreeByRootId(@ProcessId) WHERE NodeType IN ('VALIDATION','TASK')
       OPEN obj_cur
       FETCH NEXT FROM obj_cur INTO @ProcessNodeId
       WHILE @@FETCH_STATUS = 0
       BEGIN
              IF NOT EXISTS(SELECT 1 FROM ProcessFieldVisibility WHERE ProcessId = @ProcessNodeId AND FieldId = -3) INSERT ProcessFieldVisibility([ProcessId],[FieldId],[Visible],[Sort]) VALUES (@ProcessNodeId,-3,1,-8)
              IF @AddRequesterCompany = 1 AND NOT EXISTS(SELECT 1 FROM ProcessFieldVisibility WHERE ProcessId = @ProcessNodeId AND FieldId = -12) INSERT ProcessFieldVisibility([ProcessId],[FieldId],[Visible],[Sort]) VALUES (@ProcessNodeId,-12,1,-7)
              IF @AddRequesterDepartment = 1 AND NOT EXISTS(SELECT 1 FROM ProcessFieldVisibility WHERE ProcessId = @ProcessNodeId AND FieldId = -9) INSERT ProcessFieldVisibility([ProcessId],[FieldId],[Visible],[Sort]) VALUES (@ProcessNodeId,-9,1,-5)
              IF @AddRequesterLocation = 1 AND NOT EXISTS(SELECT 1 FROM ProcessFieldVisibility WHERE ProcessId = @ProcessNodeId AND FieldId = -10) INSERT ProcessFieldVisibility([ProcessId],[FieldId],[Visible],[Sort]) VALUES (@ProcessNodeId,-10,1,-6)
              If @HasEndUserField = 1
              BEGIN
                     IF NOT EXISTS(SELECT 1 FROM ProcessFieldVisibility WHERE ProcessId = @ProcessNodeId AND FieldId = -7) INSERT ProcessFieldVisibility([ProcessId],[FieldId],[Visible],[Sort]) VALUES (@ProcessNodeId,-7,1,-4)
                     IF @AddEndUserCompany = 1 AND NOT EXISTS(SELECT 1 FROM ProcessFieldVisibility WHERE ProcessId = @ProcessNodeId AND FieldId = -16) INSERT ProcessFieldVisibility([ProcessId],[FieldId],[Visible],[Sort]) VALUES (@ProcessNodeId,-16,1,-3)
                     IF @AddEndUserDepartment = 1 AND NOT EXISTS(SELECT 1 FROM ProcessFieldVisibility WHERE ProcessId = @ProcessNodeId AND FieldId = -13) INSERT ProcessFieldVisibility([ProcessId],[FieldId],[Visible],[Sort]) VALUES (@ProcessNodeId,-13,1,-1)
                     IF @AddEndUserLocation = 1 AND NOT EXISTS(SELECT 1 FROM ProcessFieldVisibility WHERE ProcessId = @ProcessNodeId AND FieldId = -14) INSERT ProcessFieldVisibility([ProcessId],[FieldId],[Visible],[Sort]) VALUES (@ProcessNodeId,-14,1,-2)
              END
              FETCH NEXT FROM obj_cur INTO @ProcessNodeId
       END
       CLOSE obj_cur
       DEALLOCATE obj_cur
       FETCH NEXT FROM c_cur INTO @ServiceId,@ArticleId,@ProcessId
END
CLOSE c_cur
DEALLOCATE c_cur