Questions and discussion about using ProcessMaker: user interface, running cases & functionality

Moderator: amosbatto

#822881
It would be better to do it inside a trigger or plugin, because you can use PMFCancelCase(). Your trigger can query the APP_CACHE_VIEW table to find the cases to cancel and then call PMFCancelCase() for each case in a for loop. You NEED to use PMFCancelCase() if you are not using the Community Edition.

Let's say that you create a separate process to cancel cases. You could have a loop so you never close the case. The one task in the task has a dropdown box to select the user with the variable "selectUser" which uses the SQL query:
SELECT USR_UID, USR_USERNAME FROM USERS
Then the following trigger following that Dynaform would cancel all the cases assigned to that user.
Code: Select all
if (!empty(@@selectUser)) {
   $userId = @@selectUser;
   $fromDate = '2019-01-25 00:00:00';
   //another way to get a date:
   //$fromDate = date("Y-m-d H:i:s", strtotime("-1 month"));

   $sql ="SELECT * FROM APP_CACHE_VIEW WHERE USR_UID='$userId' AND DEL_THREAD_STATUS='OPEN' AND 
      (APP_STATUS='TO_DO' OR APP_STATUS='DRAFT') AND DEL_INIT_DATE > '$fromDate'";
   $aCases = executeQuery($sql);

   $g = new G();
   $g->sessionVarSave();
  
   foreach ($aCases as $aCase) {
        PMFCancelCase($aCase['APP_UID'], $aCase['DEL_INDEX'], $userId);
   }
   $g->sessionVarRestore();
}
If you have to do it with MYSQL in Community Edition:
Code: Select all
UPDATE APPLICATION AS A  
INNER JOIN APP_DELEGATION AS AD ON A.APP_UID=AD.APP_UID 
INNER JOIN USERS AS U ON AD.USR_UID=U.USR_UID 
SET A.APP_STATUS='CANCELLED', A.APP_UPDATE_DATE=NOW() 
WHERE U.USR_USERNAME='admin' AND AD.DEL_THREAD_STATUS='OPEN' AND 
(A.APP_STATUS='TO_DO' OR A.APP_STATUS='DRAFT') AND AD.DEL_INIT_DATE > '2019-01-25 00:00:00' ;

UPDATE APP_DELEGATION AS AD 
INNER JOIN USERS AS U ON AD.USR_UID=U.USR_UID 
INNER JOIN APPLICATION AS A ON AD.APP_UID=A.APP_UID 
SET AD.DEL_FINISH_DATE=NOW(), AD.DEL_THREAD_STATUS='CLOSED' 
WHERE U.USR_USERNAME='admin' AND AD.DEL_THREAD_STATUS='OPEN' AND 
(A.APP_STATUS='TO_DO' OR A.APP_STATUS='DRAFT') AND AD.DEL_INIT_DATE > '2019-01-25 00:00:00' ;
This method of writing directly to database tables doesn't create new rows APP_DELAY for each case which is cancelled, which is important if you ever want to call PMFUnCancelCase().
#822896
heemalkumar wrote:I have ran the SQL, but the case remains in Inbox. Is there something I missed out on?
The SQL commands worked on my installation of PM 3.2.3 Community. What version do you have?

Try going to Admin > Cache List Cache Builder and click on the "Build Cache" button.

If that doesn't work, then you need to use PMFCancelCase() in a trigger.
attach file

Hi all. i upload input document to the process. Bu[…]

Hi Amos, Thanks again. The next task is a "Va[…]

Hi Amos, problem fixed. The field variable and th[…]

Hi Amosbatto. I apologise, I have been away for w[…]