[stevensi1018]

Hi, we've been using Repoting Tools (in PHP with SQL queries to the database) since some months and I find it pretty hard to show the current task of a case.
The problem come from the parallel tasks mostly.

I want to show all the cases (draft, to_do, completed) with their current task. If there is a case that has more than 1 task opened, i want to show it too. If a case is paused, I want to show it too.

Is there any Processmaker Table that can show me what the current tasks are ? I know about APP_DELEGATION but the DEL_THREAD_STATUS='OPEN' doesn't return the paused tasks. I've got it working at 90% approximately but there are some errors and the code is really not good (I use multiple If with MAX_INDEX if APP_STATUS=COMPLETED, else if APP_STATUS='TO_DO' i use DEL_THREAD_STATUS='OPEN'

THanks

Edit: PMFTaskCase() seems like a perfect solution but since our reporting tool is not inside Processmaker, I can't use this function

Also,

Code: Select all

SELECT A.APP_NUMBER,C.CON_VALUE
FROM APPLICATION A, APP_DELEGATION D, CONTENT C
WHERE 1 = 1                  
AND A.APP_UID = D.APP_UID
AND D.DEL_THREAD_STATUS = 'OPEN' 
AND D.TAS_UID = C.CON_ID
AND C.CON_CATEGORY = 'TAS_TITLE' 
AND C.CON_LANG = 'en'
ORDER BY A.APP_NUMBER

doesn't return paused cases (I got this code from another forum thread but 6 years ago, things might have changed?

I Also looked at APP_THREAD but it seems like it doesn't have everything so it won't work correctly then

[amosbatto]

What version of PM are you using?
If you want the current tasks, then you should use this query:

Code: Select all

SELECT APP_NUMBER, APP_TASK_TITLE, DEL_INIT_DATE,  DEL_DUE_DATE 
FROM APP_CACHE_VIEW 
WHERE DEL_THREAD_STATUS='OPEN' AND DEL_FINISH_DATE IS NULL AND
APP_STATUS <> 'COMPLETED' AND APP_STATUS <> 'CANCELLED' 
ORDER BY APP_NUMBER

[stevensi1018]

What version of PM are you using?
If you want the current tasks, then you should use this query:

Code: Select all

SELECT APP_NUMBER, APP_TASK_TITLE, DEL_INIT_DATE,  DEL_DUE_DATE 
FROM APP_CACHE_VIEW 
WHERE DEL_THREAD_STATUS='OPEN' AND DEL_FINISH_DATE IS NULL AND
APP_STATUS <> 'COMPLETED' AND APP_STATUS <> 'CANCELLED' 
ORDER BY APP_NUMBER

I'm using Processmaker 3.1.3 and the 'APP_TASK_TITLE' column is not there. Also, my main problem was the fact that when a case is paused, it doesn't show since DEL_THREAD_STATUS <> 'OPEN' but mostly, if I have parallel tasks in a case, I can't jsut say 'SELECT MAX(DEL_INDEX)' since it won't show. Your code works for cases that are TO_DO and not paused but what about the cases that are paused ?

[amosbatto]

What version of PM are you using?
If you want the current tasks, then you should use this query:

Code: Select all

SELECT APP_NUMBER, APP_TASK_TITLE, DEL_INIT_DATE,  DEL_DUE_DATE 
FROM APP_CACHE_VIEW 
WHERE DEL_THREAD_STATUS='OPEN' AND DEL_FINISH_DATE IS NULL AND
APP_STATUS <> 'COMPLETED' AND APP_STATUS <> 'CANCELLED' 
ORDER BY APP_NUMBER

I'm using Processmaker 3.1.3 and the 'APP_TASK_TITLE' column is not there. Also, my main problem was the fact that when a case is paused, it doesn't show since DEL_THREAD_STATUS <> 'OPEN' but mostly, if I have parallel tasks in a case, I can't jsut say 'SELECT MAX(DEL_INDEX)' since it won't show. Your code works for cases that are TO_DO and not paused but what about the cases that are paused ?

Oh, sorry about that. It should be APP_TAS_TITLE, not APP_TASK_TITLE. Does this query do what you want?

Code: Select all

SELECT ACV.APP_NUMBER, ACV.APP_PRO_TITLE, ACV.APP_TAS_TITLE, 
ACV.DEL_INDEX, ACV.DEL_INIT_DATE,  ACV.DEL_TASK_DUE_DATE 
FROM APP_CACHE_VIEW ACV 
LEFT JOIN APP_DELAY AD ON ACV.APP_UID=AD.APP_UID AND ACV.DEL_INDEX=AD.APP_DEL_INDEX
WHERE ACV.DEL_THREAD_STATUS='OPEN' AND ACV.DEL_FINISH_DATE IS NULL AND
ACV.APP_STATUS <> 'COMPLETED' AND ACV.APP_STATUS <> 'CANCELLED' AND 
(AD.APP_UID IS NULL OR AD.APP_TYPE<>'PAUSE' OR (AD.APP_TYPE='PAUSE' AND 
AD.APP_AUTOMATIC_DISABLED_DATE IS NOT NULL)) 
ORDER BY ACV.APP_NUMBER;

[stevensi1018]

What version of PM are you using?
If you want the current tasks, then you should use this query:

Code: Select all

SELECT APP_NUMBER, APP_TASK_TITLE, DEL_INIT_DATE,  DEL_DUE_DATE 
FROM APP_CACHE_VIEW 
WHERE DEL_THREAD_STATUS='OPEN' AND DEL_FINISH_DATE IS NULL AND
APP_STATUS <> 'COMPLETED' AND APP_STATUS <> 'CANCELLED' 
ORDER BY APP_NUMBER

I'm using Processmaker 3.1.3 and the 'APP_TASK_TITLE' column is not there. Also, my main problem was the fact that when a case is paused, it doesn't show since DEL_THREAD_STATUS <> 'OPEN' but mostly, if I have parallel tasks in a case, I can't jsut say 'SELECT MAX(DEL_INDEX)' since it won't show. Your code works for cases that are TO_DO and not paused but what about the cases that are paused ?

Oh, sorry about that. It should be APP_TAS_TITLE, not APP_TASK_TITLE. Does this query do what you want?

Code: Select all

SELECT ACV.APP_NUMBER, ACV.APP_PRO_TITLE, ACV.APP_TAS_TITLE, 
ACV.DEL_INDEX, ACV.DEL_INIT_DATE,  ACV.DEL_TASK_DUE_DATE 
FROM APP_CACHE_VIEW ACV 
LEFT JOIN APP_DELAY AD ON ACV.APP_UID=AD.APP_UID AND ACV.DEL_INDEX=AD.APP_DEL_INDEX
WHERE ACV.DEL_THREAD_STATUS='OPEN' AND ACV.DEL_FINISH_DATE IS NULL AND
ACV.APP_STATUS <> 'COMPLETED' AND ACV.APP_STATUS <> 'CANCELLED' AND 
(AD.APP_UID IS NULL OR AD.APP_TYPE<>'PAUSE' OR (AD.APP_TYPE='PAUSE' AND 
AD.APP_AUTOMATIC_DISABLED_DATE IS NOT NULL)) 
ORDER BY ACV.APP_NUMBER;

It still doesn't show the paused cases unfortunately

[amosbatto]

I wrote the query to NOT show the paused cases. Try this if you want to also see paused cases:

Code: Select all

SELECT ACV.APP_NUMBER, ACV.APP_PRO_TITLE, ACV.APP_TAS_TITLE,
ACV.DEL_INDEX, ACV.DEL_INIT_DATE,  ACV.DEL_TASK_DUE_DATE
FROM APP_CACHE_VIEW ACV
LEFT JOIN APP_DELAY AD ON ACV.APP_UID=AD.APP_UID AND ACV.DEL_INDEX=AD.APP_DEL_INDEX
WHERE (ACV.DEL_THREAD_STATUS='OPEN' AND ACV.DEL_FINISH_DATE IS NULL AND
ACV.APP_STATUS <> 'COMPLETED' AND ACV.APP_STATUS <> 'CANCELLED') OR 
(ACV.APP_THREAD_STATUS='OPEN' AND AD.APP_TYPE='PAUSE' AND APP_AUTOMATIC_DISABLED_DATE IS NULL) 
ORDER BY ACV.APP_NUMBER;

[stevensi1018]

I wrote the query to NOT show the paused cases. Try this if you want to also see paused cases:

Code: Select all

SELECT ACV.APP_NUMBER, ACV.APP_PRO_TITLE, ACV.APP_TAS_TITLE,
ACV.DEL_INDEX, ACV.DEL_INIT_DATE,  ACV.DEL_TASK_DUE_DATE
FROM APP_CACHE_VIEW ACV
LEFT JOIN APP_DELAY AD ON ACV.APP_UID=AD.APP_UID AND ACV.DEL_INDEX=AD.APP_DEL_INDEX
WHERE (ACV.DEL_THREAD_STATUS='OPEN' AND ACV.DEL_FINISH_DATE IS NULL AND
ACV.APP_STATUS <> 'COMPLETED' AND ACV.APP_STATUS <> 'CANCELLED') OR 
(ACV.APP_THREAD_STATUS='OPEN' AND AD.APP_TYPE='PAUSE' AND APP_AUTOMATIC_DISABLED_DATE IS NULL) 
ORDER BY ACV.APP_NUMBER;

Thanks again but it shows if the case was paused, not if the case is currently paused. For example, if I paused the case 3 times, it will show 3 tasks. I'll try to find a solution but it seems way too complicated to show the current tasks (for example, it a case is completed, i would like to show the last task too)

I need to show this:
- All the current tasks of opened cases (including paused tasks)
- Last task of completed/cancelled cases

[stevensi1018]

I wrote the query to NOT show the paused cases. Try this if you want to also see paused cases:

Code: Select all

SELECT ACV.APP_NUMBER, ACV.APP_PRO_TITLE, ACV.APP_TAS_TITLE,
ACV.DEL_INDEX, ACV.DEL_INIT_DATE,  ACV.DEL_TASK_DUE_DATE
FROM APP_CACHE_VIEW ACV
LEFT JOIN APP_DELAY AD ON ACV.APP_UID=AD.APP_UID AND ACV.DEL_INDEX=AD.APP_DEL_INDEX
WHERE (ACV.DEL_THREAD_STATUS='OPEN' AND ACV.DEL_FINISH_DATE IS NULL AND
ACV.APP_STATUS <> 'COMPLETED' AND ACV.APP_STATUS <> 'CANCELLED') OR 
(ACV.APP_THREAD_STATUS='OPEN' AND AD.APP_TYPE='PAUSE' AND APP_AUTOMATIC_DISABLED_DATE IS NULL) 
ORDER BY ACV.APP_NUMBER;

I think I finally got it working (thanks to your code there was not much missing):

Code: Select all

SELECT ACV.APP_NUMBER,ACV.APP_TITLE,ACV.APP_PRO_TITLE, ACV.APP_TAS_TITLE, ACV.APP_STATUS, ACV.DEL_TASK_DUE_DATE,ACV.DEL_PRIORITY,ACV.APP_UPDATE_DATE,
        AD.APP_TYPE
        FROM APP_CACHE_VIEW ACV
        LEFT JOIN APP_DELAY AD ON ACV.APP_UID=AD.APP_UID AND ACV.DEL_INDEX=AD.APP_DEL_INDEX
        WHERE (ACV.DEL_THREAD_STATUS='OPEN' AND ACV.DEL_FINISH_DATE IS NULL AND
        ACV.APP_STATUS <> 'COMPLETED' AND ACV.APP_STATUS <> 'CANCELLED') OR 
        (ACV.APP_THREAD_STATUS='OPEN' AND AD.APP_TYPE='PAUSE' AND APP_AUTOMATIC_DISABLED_DATE IS NULL AND APP_DISABLE_ACTION_USER='0') 
        ORDER BY ACV.APP_NUMBER
        

I added 'AND APP_DISABLE_ACTION_USER='0' in the paused condition so it shows only the cases that were not unpaused (so the case is still open)