Questions and discussion about developing processes and programming in PHP, JavaScript, web services & REST API.

Moderators: ArturoRobles, ArturoRobles

Forum rules: Please search to see if a question has already asked before creating a new topic. Please don't post the same question in multiple forums.
#828286
I have to support reports in processmaker 3.4. A supervisor or admin can define certain SLA's. According to those SLA's i have to identify the performance of any given user ( or all users ).
My goal is :

1. Find out process completion time in days/minutes.
2. Find out task completion time in days minutes.
3. Number of process instances that were completed within the SLA
Number of process instances that were NOT completed within the SLA
4. Number of process instances that were completed within the Due date/time
Number of process instances that were NOT completed within the Due date/time


I also want to filter these results based on DATE RANGE (week, day, month, custom range) , ASSIGNEE, OWNER.

Any help would be highly appreciated.
NOTE : i don't want to use any plugin or third party tool. I need to do it via mysql queries.
Thank you.
#828302
This is a long, lengthy and cumbersome process.

To get started i will give some queries :

1. To get the number of all processes which got completed ( irrespective of whether they got completed within due date or not we can use the following query ) :

SELECT A.APP_UID as CASE_ID, A.APP_STATUS as CASE_STATUS, A.PRO_UID, A.APP_CREATE_DATE, A.APP_FINISH_DATE, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(APP_FINISH_DATE , APP_CREATE_DATE)))) AS TIME_TAKEN, AD.USR_UID, U.USR_USERNAME, P.PRO_TITLE AS APP_PRO_TITLE FROM APPLICATION A LEFT JOIN APP_DELEGATION AD ON (AD.APP_NUMBER = A.APP_NUMBER) LEFT JOIN USERS U ON (AD.USR_ID = U.USR_ID) LEFT JOIN PROCESS P ON (AD.PRO_ID = P.PRO_ID) WHERE A.APP_STATUS = "COMPLETED" GROUP BY A.APP_UID;



2. To get the number and info of cases which are not completed yet ( they are in TO_DO, DRAFT status ).
This will list all the processes even if any one of their tasks is not completed by any user.

SELECT A.APP_UID as CASE_ID, A.APP_STATUS as CASE_STATUS, A.PRO_UID, A.APP_CREATE_DATE, A.APP_FINISH_DATE, AD.USR_UID, U.USR_USERNAME, P.PRO_TITLE AS APP_PRO_TITLE FROM APPLICATION A LEFT JOIN APP_DELEGATION AD ON (AD.APP_NUMBER = A.APP_NUMBER) LEFT JOIN USERS U ON (AD.USR_ID = U.USR_ID) LEFT JOIN PROCESS P ON (AD.PRO_ID = P.PRO_ID) WHERE A.APP_STATUS = "DRAFT" OR A.APP_STATUS="TO_DO" AND AD.DEL_THREAD_STATUS != 'CLOSED' GROUP BY A.APP_UID;



3. To get the count of cases completed within due date (on time) :

SELECT COUNT(*) FROM ( SELECT APP_UID, TAS_UID, DEL_FINISH_DATE, DEL_TASK_DUE_DATE, MIN(CASE WHEN (DATEDIFF(DEL_TASK_DUE_DATE,DEL_FINISH_DATE) >= 0) THEN 'TRUE' ELSE 'FALSE' END) COMPLETED_ON_TIME FROM ( SELECT APP_UID,TAS_UID, DEL_FINISH_DATE, DEL_TASK_DUE_DATE FROM APP_DELEGATION WHERE APP_UID IN ( SELECT A.APP_UID FROM APPLICATION A LEFT JOIN APP_DELEGATION AD ON (AD.APP_NUMBER = A.APP_NUMBER) LEFT JOIN USERS U ON (AD.USR_ID = U.USR_ID) LEFT JOIN PROCESS P ON (AD.PRO_ID = P.PRO_ID) WHERE A.APP_STATUS = "COMPLETED" GROUP BY A.APP_UID ) ) AS T GROUP BY APP_UID ) AS CASE_COMPLETION WHERE COMPLETED_ON_TIME = 'TRUE';



4. To get all cases alongwith info of their timely completion we can run :

SELECT APP_UID, TAS_UID, DEL_FINISH_DATE, DEL_TASK_DUE_DATE, MIN(CASE WHEN (DATEDIFF(DEL_TASK_DUE_DATE,DEL_FINISH_DATE) >= 0) THEN 'TRUE' ELSE 'FALSE' END) COMPLETED_ON_TIME FROM ( SELECT APP_UID,TAS_UID, DEL_FINISH_DATE, DEL_TASK_DUE_DATE FROM APP_DELEGATION WHERE APP_UID IN ( SELECT A.APP_UID FROM APPLICATION A LEFT JOIN APP_DELEGATION AD ON (AD.APP_NUMBER = A.APP_NUMBER) LEFT JOIN USERS U ON (AD.USR_ID = U.USR_ID) LEFT JOIN PROCESS P ON (AD.PRO_ID = P.PRO_ID) WHERE A.APP_STATUS = "COMPLETED" GROUP BY A.APP_UID ) ) AS T GROUP BY APP_UID;
Unable to create workspace

I have the same issue. Any solution? Thanks alex[…]

Install in ubuntu

Dear All, In our dev environment we did an upgrad[…]

Get data from oracle database.

How to display all data in a table from oracle DB […]

Hello everyone, I have the same route case issue, […]