[ashkufaraz]

I with this query can get amout time that a case stay in inbox

Code: Select all

select TIMESTAMPDIFF(MINUTE,d.DEL_DELEGATE_DATE, IFNULL(d.DEL_FINISH_DATE, NOW()))/60 AS timeJob
from app_delegation d
GROUP BY d.USR_UID

but i want get amount time that a case stay in inbox base on users calender

I write this query

Code: Select all

SELECT cd.CALENDAR_WORK_DAYS, cb.CALENDAR_BUSINESS_START,cb.CALENDAR_BUSINESS_END, CONCAT(HOUR(d.DEL_DELEGATE_DATE),":", MINUTE(d.DEL_DELEGATE_DATE)) as startTime, CONCAT(HOUR(IFNULL(d.DEL_FINISH_DATE, NOW())),":", MINUTE(IFNULL(d.DEL_FINISH_DATE, NOW()))) as endTime, d.DEL_DELEGATE_DATE, IFNULL(d.DEL_FINISH_DATE, NOW()) AS DEL_FINISH_DATE, DAYOFWEEK(d.DEL_DELEGATE_DATE) AS dayFrom, DAYOFWEEK(IFNULL(d.DEL_FINISH_DATE, NOW())) AS dayTo,ca.CALENDAR_UID,d.USR_UID
FROM app_delegation d
LEFT JOIN users u ON d.USR_UID=u.USR_UID
LEFT JOIN calendar_assignments ca ON ca.OBJECT_UID=d.USR_UID
LEFT JOIN calendar_business_hours cb ON cb.CALENDAR_UID=ca.CALENDAR_UID
LEFT JOIN calendar_definition cd ON cd.CALENDAR_UID=ca.CALENDAR_UID
ORDER BY d.USR_UID

but i does not know how can check DEL_FINISH_DATE and DEL_FINISH_DATE is in rang CALENDAR_BUSINESS_START and CALENDAR_BUSINESS_END and CALENDAR_WORK_DAYS of users

[amosbatto]

This is too difficult to do with a MySQL function, because ProcessMaker has some special rules for calculating task duration.
If you want to calculate task duration the way ProcessMaker does it then use this trigger code:

Code: Select all

//calculate task duration for users:
G::LoadClass('calendar');
$oCal = new calendar();
@=taskTimes = array();

$sql = "SELECT USR_UID, PRO_UID, TAS_UID, DEL_DELEGATE_DATE, DEL_FINISH_DATE 
   FROM APP_DELEGATION GROUP BY USR_UID";
$aTasks = executeQuery($sql);

foreach ($aTasks as $aTask) {
   $oCal->getCalendar($aTask['USR_UID'], $aTask['PRO_UID'], $aTask['TAS_UID']);
   $oCal->getCalendarData();
   $taskEnd = empty($aTask['DEL_FINISH_DATE']) ? date("Y-m-d H:i:s") : $aTask['DEL_FINISH_DATE']; 
   $seconds = $oCal->calculateDuration($aTask['DEL_DELEGATE_DATE'], $taskEnd);
   @=taskTimes[] = array(
       'userId' => $aTask['USR_UID'],
       'taskId' => $aTask['TAS_UID'],
       'taskDuration' => $seconds
   );
}

You can find the definition of the calendar class functions in workflow/engine/classes/class.calendar.php