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 allif (!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 allUPDATE 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().