BAM-Queries & Task-Log

[camunda]

Hi guys.

For the simulation I currently write a Command with some HSQL Queries to get historical data (to propose it as simulation input). Basically I have 2 queries, which look like:

 <query name="Simulation.calculateAverageTimeForStates">
 <![CDATA[
 select
 nl.node.id,
 nl.node.name,
 count(nl.leave),
 avg(nl.duration),
 min(nl.duration),
 max(nl.duration),
 stddev(nl.duration)
 from org.jbpm.graph.log.NodeLog nl
 where nl.node.processDefinition = :processDefinition
 and nl.node.class = org.jbpm.graph.node.State
 and time(nl.enter) > time(:fromTime) and time(nl.enter) < time(:tillTime)
 group by nl.node
 ]]>
 </query>

 <query name="Simulation.calculateAverageTimeForTaskInstances">
 <![CDATA[
 select
 assignLog.taskInstance.task.id,
 assignLog.taskInstance.task.name,
 assignLog.taskInstance.task.taskNode.id,
 assignLog.taskInstance.task.taskNode.name,
 count(assignLog.date),
 avg(endLog.date - assignLog.date),
 min(endLog.date - assignLog.date),
 max(endLog.date - assignLog.date),
 stddev(endLog.date - assignLog.date)
 from org.jbpm.taskmgmt.log.TaskEndLog as endLog,
 org.jbpm.taskmgmt.log.TaskAssignLog as assignLog
 where
 endLog.taskInstance = assignLog.taskInstance
 and assignLog.taskInstance.task.taskNode.processDefinition = :processDefinition
 and time(assignLog.date) > time(:fromTime) and time(assignLog.date) < time(:tillTime)
 group by assignLog.taskInstance.task
 ]]>
 </query>

They do their work. I figured out one problem, which is a bit ugly, but I am not sure how to really solve it. Maybe it makes sense to think about it more in PVM? I just wanted to write it down here...

I don't get any information which outgoing transition the user selected while ending tasks. I only get the outgoing transition for the TaskNode. But these are two different pieces of information.

For the states I use this HSQL. I now use it for the TaskInstances too, but this is not really correct (but I ignore this for the moment)...

 <query name="Simulation.calculateLeavingTransitionProbability">
 <![CDATA[
 select
 tl.transition.id,
 tl.transition.name,
 tl.destinationNode.id,
 tl.destinationNode.name,
 count(tl)
 from org.jbpm.graph.log.TransitionLog tl
 where tl.sourceNode.id = :nodeId
 group by tl.transition
 ]]>
 </query>

Cheers
Bernd