[heemalkumar]

Hi Team
I would like to close all cases assigned to a person from a particular date. How do I go about doing that with SQL?

[amosbatto]

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().

[heemalkumar]

Hi Amos
I have ran the SQL, but the case remains in Inbox. Is there something I missed out on?

[amosbatto]

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.

[rachmadiniutami]

Hi amos,

i create a trigger in a seperate process to cancell spesific case like below.
this trigger, will read an PM_table name PMT_CANCELL_WELCOME_CALL
this table contain list of case information (Case number, Cancell date, status). and when cancell date is today, the case will be cancell.
i want, the system run the trigger everyday so i dont have to run the task anymore?
is that possible? how to make it happened?

$fetched_queued = "SELECT * FROM PMT_CANCELL_WELCOME_CALL WHERE STATUS = 'QUEUE'";
$fetched_queued = executeQuery($fetched_queued);
foreach($fetched_queued as $queued_key=>$queued_value){
$Caseno=$queued_value['CASENO'];
$CekStillLive=executeQuery("SELECT TAS_UID FROM APP_CACHE_VIEW WHERE APP_NUMBER=$Caseno AND (APP_STATUS='TO_DO' OR APP_STATUS='DRAFT') AND DEL_THREAD_STATUS='OPEN'");
if (!is_array($CekStillLive)) {
executeQuery("Update PMT_CANCELL_WELCOME_CALL set STATUS='COMP' where CASENO=$Caseno");
}else{
$taskuid=$CekStillLive[1]['TAS_UID'];
if ($taskuid!=='4304253695e437cf0d8b586016424364'){

if(strtotime($queued_value['SEND_DATE'])==strtotime(date('Y-m-d'))){
if (PMFCancelCase($CekStillLive['APP_UID'], $CekStillLive['DEL_INDEX'], $CekStillLive['CURRENT_USER_UID'])){
executeQuery("UPDATE PMT_CANCELL_WELCOME_CALL SET STATUS = 'CANCEL' WHERE CASENO = '$Caseno'");

}else{
echo "dont cancell <br>";
}
}else{
echo "dont cancell <br>";
}
}
}
}