[pawansaxena]

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.

[pawansaxena]

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;