source: sandbox/workflow/trunk/inc/engine/src/ProcessMonitor/ProcessMonitor.php @ 2372

Revision 2372, 30.1 KB checked in by pedroerp, 14 years ago (diff)

Ticket #609 - Merged 2197:2356 /sandbox/workflow/branches/609/ em /sandbox/workflow/trunk.
  • Property svn:executable set to *
Line 
1<?php
2require_once(GALAXIA_LIBRARY.SEP.'src'.SEP.'common'.SEP.'Base.php');
3
4/**
5 * Provides methods for use in typical monitoring scripts, where the first part are methods for cleaning up instances and workitems associated with a process
6 * and the second, methods to obtains information about the actual state or histroy of the process
7 *
8 * @package Galaxia
9 * @license http://www.gnu.org/copyleft/gpl.html GPL
10 */
11class ProcessMonitor extends Base
12{
13  /**
14   * Constructor
15   *
16   * @param object &$db ADOdb
17   * @return object ProcessMonitor
18   * @access public
19   */
20  function ProcessMonitor()
21  {
22    $this->child_name = 'ProcessMonitor';
23    parent::Base();
24    // check the the actual user can really do this
25    if ( !(galaxia_user_can_monitor()))
26    {
27      unset($this);
28      galaxia_show_error('forbidden access to ProcessMonitor object');
29    }
30  }
31
32  /**
33   * Gets statistics about all processes handled by the engine
34   *
35   * @return array Resulting array has the following pairs of keys and values:
36   * [active_processes] => number, [processes] => number (total number of processes), [running_processes] => number,
37   * [active_instances] => number, [completed_instances] => number, [exception_instances] => number, [aborted_instances] => number
38   * @access public
39   */
40  function monitor_stats() {
41    $res = Array();
42    $res['active_processes'] = $this->getOne("select count(*) from `".GALAXIA_TABLE_PREFIX."processes` where `wf_is_active`=?",array('y'));
43    $res['processes'] = $this->getOne("select count(*) from `".GALAXIA_TABLE_PREFIX."processes`");
44    $result = $this->query("select distinct(`wf_p_id`) from `".GALAXIA_TABLE_PREFIX."instances` where `wf_status`=?",array('active'));
45    $res['running_processes'] = $result->numRows();
46    // get the number of instances per status
47    $query = "select wf_status, count(*) as num_instances from ".GALAXIA_TABLE_PREFIX."instances group by wf_status";
48    $result = $this->query($query);
49    $status = array();
50    while($info = $result->fetchRow()) {
51      $status[$info['wf_status']] = $info['num_instances'];
52    }
53    $res['active_instances'] = isset($status['active']) ? $status['active'] : 0;
54    $res['completed_instances'] = isset($status['completed']) ? $status['completed'] : 0;
55    $res['exception_instances'] = isset($status['exception']) ? $status['exception'] : 0;
56    $res['aborted_instances'] = isset($status['aborted']) ? $status['aborted'] : 0;
57    return $res;
58  }
59
60  /**
61   * @deprecated
62   */
63  function update_instance_status($iid,$status) {
64    return;
65    $query = "update `".GALAXIA_TABLE_PREFIX."instances` set `wf_status`=? where `wf_instance_id`=?";
66    $this->query($query,array($status,$iid));   
67  }
68 
69 
70  /**
71   * @deprecated
72   */
73  function update_instance_activity_status($iid,$activityId,$status) {
74    return;
75    $query = "update `".GALAXIA_TABLE_PREFIX."instance_activities` set `wf_status`=? where `wf_instance_id`=? and `wf_activity_id`=?";
76    $this->query($query,array($status,$iid,$activityId));
77  }
78
79  /**
80  * Removes instance along with its workitems (history), current activities and properties altogether
81  *
82  * @param int $iid Instance id
83  * @return bool
84  * @access public
85  */
86  function remove_instance($iid)
87  {
88    // start a transaction
89    $this->db->StartTrans();
90    $query = "delete from `".GALAXIA_TABLE_PREFIX."workitems` where `wf_instance_id`=?";
91    $this->query($query,array($iid));
92    $query = "delete from `".GALAXIA_TABLE_PREFIX."instance_activities` where `wf_instance_id`=?";
93    $this->query($query,array($iid));
94    $query = "delete from `".GALAXIA_TABLE_PREFIX."instances` where `wf_instance_id`=?";
95    $this->query($query,array($iid));
96    // perform commit (return true) or Rollback (return false)
97    return $this->db->CompleteTrans();
98
99  }
100 
101  /**
102  * Removes aborted instances along with workitems (history),
103  * you can limit this behaviour to a single process by specifying its process id
104  *
105  * @param int $process_id Providable process id to limit this function range to only one process
106  * @return bool
107  * @access public
108  */
109  function remove_aborted($pId=0)
110  {
111    // check the the actual user can really do this
112    if ( !((galaxia_user_can_clean_instances()) || (galaxia_user_can_clean_aborted_instances())) )
113    {
114      $this->error[] = tra('user is not authorized to delete aborted instances');
115      return false;
116    }
117    if (!($pId))
118    {
119      $whereand = '';
120      $bindvars = array('aborted');
121    }
122    else
123    {
124      $whereand = 'and wf_p_id = ?';
125      $bindvars = array('aborted', $pId);
126    }
127    $query="select `wf_instance_id` from `".GALAXIA_TABLE_PREFIX."instances` where `wf_status`=?".$whereand;
128    // start a transaction
129    $this->db->StartTrans();
130    $result = $this->query($query,$bindvars);
131    while($res = $result->fetchRow())
132    { 
133      $iid = $res['wf_instance_id'];
134      $query = "delete from `".GALAXIA_TABLE_PREFIX."instance_activities` where `wf_instance_id`=?";
135      $this->query($query,array($iid));
136      $query = "delete from `".GALAXIA_TABLE_PREFIX."workitems` where `wf_instance_id`=?";
137      $this->query($query,array($iid)); 
138    }
139    $query = "delete from `".GALAXIA_TABLE_PREFIX."instances` where `wf_status`=?".$whereand;
140    $this->query($query,$bindvars);
141    // perform commit (return true) or Rollback (return false)
142    return $this->db->CompleteTrans();
143  }
144
145  /**
146  * Provided a process, it removes instances, workitems (history) and activities current running associated with these instances
147  * 
148  * @param int $pId Process id whose contents will be erased
149  * @return bool
150  * @access public
151  */
152  function remove_all($pId) {
153    // check the the actual user can really do this
154    if ( !(galaxia_user_can_clean_instances()) )
155    {
156      $this->error[] = tra('user is not authorized to delete instances');
157      return false;
158    }
159    $query="select `wf_instance_id` from `".GALAXIA_TABLE_PREFIX."instances` where `wf_p_id`=?";
160    // start a transaction
161    $this->db->StartTrans();
162    $result = $this->query($query,array($pId));
163    while($res = $result->fetchRow()) {
164      $iid = $res['wf_instance_id'];
165      $query = "delete from `".GALAXIA_TABLE_PREFIX."instance_activities` where `wf_instance_id`=?";
166      $this->query($query,array($iid));
167      $query = "delete from `".GALAXIA_TABLE_PREFIX."workitems` where `wf_instance_id`=?";
168      $this->query($query,array($iid)); 
169    }
170    $query = "delete from `".GALAXIA_TABLE_PREFIX."instances` where `wf_p_id`=?";
171    $this->query($query,array($pId));
172    // perform commit (return true) or Rollback (return false)
173    return $this->db->CompleteTrans();
174  }
175
176  /**
177  * Lists all processes and return stats
178  *
179  * @param int $offset Starting row number to return
180  * @param int $maxRecords Max number of records to return
181  * @param string $sort_mode Sort order
182  * @param string $find Search for in process name or process description
183  * @param string $where Condition query, be carefull with this string, read the query before
184  * @param bool $add_stats True by default, by setting it to false you wont get the statistics associated with
185  * the processes, this could be helpfull for gui listing on selects, to avoid (a big number of) unnecessary queries
186  * @return array Associative, with the number of records for the 'cant' key and an array of process stats for the
187  * 'data' key. each row follows this form: key : process_id, value : an array of infos:
188  * keys are : wf_p_id, wf_name, wf_is_valid, wf_is_active, wf_version, wf_description, wf_last_modif,
189  * and wf_normalized_name for the 'classical part' and for the 'stats part' whe have: active_instances,
190  * exception_instances, completed_instances, aborted_instances, all_instances, activities
191  * @access public
192  */
193  function monitor_list_processes($offset,$maxRecords,$sort_mode,$find,$where='', $add_stats=true)
194  {
195 
196    $sort_mode = $this->convert_sortmode($sort_mode);
197    if($find) {
198      $findesc = '%'.$find.'%';
199      $mid=" where ((wf_name like ?) or (wf_description like ?))";
200      $bindvars = array($findesc,$findesc);
201    } else {
202      $mid="";
203      $bindvars = array();
204    }
205    if($where) {
206      if($mid) {
207        $mid.= " and ($where) ";
208      } else {
209        $mid.= " where ($where) ";
210      }
211    }
212    // get the requested processes
213    $query = "select * from ".GALAXIA_TABLE_PREFIX."processes $mid order by $sort_mode";
214    $query_cant = "select count(*) from ".GALAXIA_TABLE_PREFIX."processes $mid";
215    $result = $this->query($query,$bindvars,$maxRecords,$offset);
216    $cant = $this->getOne($query_cant,$bindvars);
217    $ret = Array();
218    while($res = $result->fetchRow()) {
219      $pId = $res['wf_p_id'];
220      // Number of active instances
221      $res['active_instances'] = 0;
222      // Number of exception instances
223      $res['exception_instances'] = 0;
224      // Number of completed instances
225      $res['completed_instances'] = 0;
226      // Number of aborted instances
227      $res['aborted_instances'] = 0;
228      $res['all_instances'] = 0;
229      // Number of activities
230      $res['activities'] = 0;
231      $ret[$pId] = $res;
232    }
233    if (count($ret) < 1) {
234      $retval = Array();
235      $retval["data"] = $ret;
236      $retval["cant"] = $cant;
237      return $retval;
238    }
239    if ($add_stats)
240    {
241      // get number of instances and timing statistics per process and status
242      $query = "select wf_p_id, wf_status, count(*) as num_instances,
243                min(wf_ended - wf_started) as min_time, avg(wf_ended - wf_started) as avg_time, max(wf_ended - wf_started) as max_time
244                from ".GALAXIA_TABLE_PREFIX."instances where wf_p_id in (" . join(', ', array_keys($ret)) . ") group by wf_p_id, wf_status";
245      $result = $this->query($query);
246      while($res = $result->fetchRow()) {
247        $pId = $res['wf_p_id'];
248        if (!isset($ret[$pId])) continue;
249        switch ($res['wf_status']) {
250          case 'active':
251            $ret[$pId]['active_instances'] = $res['num_instances'];
252            $ret[$pId]['all_instances'] += $res['num_instances'];
253            break;
254          case 'completed':
255            $ret[$pId]['completed_instances'] = $res['num_instances'];
256            $ret[$pId]['all_instances'] += $res['num_instances'];
257            $ret[$pId]['duration'] = array('min' => $res['min_time'], 'avg' => $res['avg_time'], 'max' => $res['max_time']);
258            break;
259          case 'exception':
260            $ret[$pId]['exception_instances'] = $res['num_instances'];
261            $ret[$pId]['all_instances'] += $res['num_instances'];
262            break;
263          case 'aborted':
264            $ret[$pId]['aborted_instances'] = $res['num_instances'];
265            $ret[$pId]['all_instances'] += $res['num_instances'];
266            break;
267        }
268      }
269      // get number of activities per process
270      $query = "select wf_p_id, count(*) as num_activities
271                from ".GALAXIA_TABLE_PREFIX."activities
272                where wf_p_id in (" . join(', ', array_keys($ret)) . ")
273                group by wf_p_id";
274      $result = $this->query($query);
275      while($res = $result->fetchRow()) {
276        $pId = $res['wf_p_id'];
277        if (!isset($ret[$pId])) continue;
278        $ret[$pId]['activities'] = $res['num_activities'];
279      }
280    }
281    $retval = Array();
282    $retval["data"] = $ret;
283    $retval["cant"] = $cant;
284    return $retval;
285  }
286
287  /**
288  * Lists all activities and return stats
289  *
290  * @param int $offset First row number to return
291  * @param int $maxRecords Maximum number of records to return
292  * @param string $sort_mode Sort order
293  * @param string $find Search for in activity name or activity description
294  * @param string $where Ads to the query, be carefull with this string, read the query before
295  * @param bool $add_stats True by default, by setting it to false you wont get the statistics associated with
296  * the activities, this could be helpfull for gui listing on selects, to avoid (a big number of) unnecessary queries
297  * @return array Associative, with the number of records for the 'cant' key and an array of process stats for the
298  * 'data' key. each row is of this form: key : activity_id, value : an array of infos: keys are : wf_procname, wf_version,
299  * wf_proc_normalized_name, wf_activity_id, wf_name, wf_normalized_name, wf_p_id, wf_type, wf_is_autorouted, wf_flow_num,
300  * wf_is_interactive, wf_last_modif, wf_description, wf_default_user and for the stats part: active_instances, completed_instances,
301  * aborted_instances, exception_instances, act_running_instances, act_completed_instances
302  * @access public
303  */
304  function monitor_list_activities($offset,$maxRecords,$sort_mode,$find,$where='', $add_stats=true)
305  {
306 
307    $sort_mode = $this->convert_sortmode($sort_mode);
308    if($find) {
309      $findesc = '%'.$find.'%';
310      $mid=" where ((ga.wf_name like ?) or (ga.wf_description like ?))";
311      $bindvars = array($findesc,$findesc);
312    } else {
313      $mid="";
314      $bindvars = array();
315    }
316    if($where) {
317      $where = preg_replace('/pId/', 'ga.wf_p_id', $where);
318      if($mid) {
319        $mid.= " and ($where) ";
320      } else {
321        $mid.= " where ($where) ";
322      }
323    }
324    $query = "select gp.`wf_name` as `wf_procname`, gp.`wf_version`, gp.wf_normalized_name as wf_proc_normalized_name, ga.*
325              from ".GALAXIA_TABLE_PREFIX."activities ga
326                left join ".GALAXIA_TABLE_PREFIX."processes gp on gp.wf_p_id=ga.wf_p_id
327              $mid order by $sort_mode";
328    $query_cant = "select count(*) from ".GALAXIA_TABLE_PREFIX."activities ga $mid";
329    $result = $this->query($query,$bindvars,$maxRecords,$offset);
330    $cant = $this->getOne($query_cant,$bindvars);
331    $ret = Array();
332    while($res = $result->fetchRow()) {
333      // Number of active instances
334      $aid = $res['wf_activity_id'];
335      if ($add_stats)
336      {
337        $res['active_instances']=$this->getOne("select count(gi.wf_instance_id) from ".GALAXIA_TABLE_PREFIX."instances gi,".GALAXIA_TABLE_PREFIX."instance_activities gia where gi.wf_instance_id=gia.wf_instance_id and gia.wf_activity_id=$aid and gi.wf_status='active' and wf_p_id=".$res['wf_p_id']);
338      // activities of completed instances are all removed from the instance_activities table for some reason, so we need to look at workitems
339        $res['completed_instances']=$this->getOne("select count(distinct gi.wf_instance_id) from ".GALAXIA_TABLE_PREFIX."instances gi,".GALAXIA_TABLE_PREFIX."workitems gw where gi.wf_instance_id=gw.wf_instance_id and gw.wf_activity_id=$aid and gi.wf_status='completed' and wf_p_id=".$res['wf_p_id']);
340      // activities of aborted instances are all removed from the instance_activities table for some reason, so we need to look at workitems
341        $res['aborted_instances']=$this->getOne("select count(distinct gi.wf_instance_id) from ".GALAXIA_TABLE_PREFIX."instances gi,".GALAXIA_TABLE_PREFIX."workitems gw where gi.wf_instance_id=gw.wf_instance_id and gw.wf_activity_id=$aid and gi.wf_status='aborted' and wf_p_id=".$res['wf_p_id']);
342        $res['exception_instances']=$this->getOne("select count(gi.wf_instance_id) from ".GALAXIA_TABLE_PREFIX."instances gi,".GALAXIA_TABLE_PREFIX."instance_activities gia where gi.wf_instance_id=gia.wf_instance_id and gia.wf_activity_id=$aid and gi.wf_status='exception' and wf_p_id=".$res['wf_p_id']);
343        $res['act_running_instances']=$this->getOne("select count(gi.wf_instance_id) from ".GALAXIA_TABLE_PREFIX."instances gi,".GALAXIA_TABLE_PREFIX."instance_activities gia where gi.wf_instance_id=gia.wf_instance_id and gia.wf_activity_id=$aid and gia.wf_status='running' and wf_p_id=".$res['wf_p_id']);     
344      // completed activities are removed from the instance_activities table unless they're part of a split for some reason, so this won't work
345      //  $res['act_completed_instances']=$this->getOne("select count(gi.wf_instance_id) from ".GALAXIA_TABLE_PREFIX."instances gi,".GALAXIA_TABLE_PREFIX."instance_activities gia where gi.wf_instance_id=gia.wf_instance_id and gia.activityId=$aid and gia.status='completed' and pId=".$res['pId']);     
346        $res['act_completed_instances'] = 0;
347      }
348      $ret[$aid] = $res;
349    }
350    if (count($ret) < 1) {
351      $retval = Array();
352      $retval["data"] = $ret;
353      $retval["cant"] = $cant;
354      return $retval;
355    }
356    if ($add_stats)
357    {
358      $query = "select wf_activity_id, count(distinct wf_instance_id) as num_instances, min(wf_ended - wf_started) as min_time, avg(wf_ended - wf_started) as avg_time, max(wf_ended - wf_started) as max_time
359                from ".GALAXIA_TABLE_PREFIX."workitems
360                where wf_activity_id in (" . join(', ', array_keys($ret)) . ")
361                group by wf_activity_id";
362      $result = $this->query($query);
363      while($res = $result->fetchRow()) {
364        // Number of active instances
365        $aid = $res['wf_activity_id'];
366        if (!isset($ret[$aid])) continue;
367        $ret[$aid]['act_completed_instances'] = $res['num_instances'] - $ret[$aid]['aborted_instances'];
368        $ret[$aid]['duration'] = array('min' => $res['min_time'], 'avg' => $res['avg_time'], 'max' => $res['max_time']);
369      }
370    }
371    $retval = Array();
372    $retval["data"] = $ret;
373    $retval["cant"] = $cant;
374    return $retval;
375  }
376
377 /**
378  * Lists all instances and return stats
379  *
380  * @param int $offset First row number to return
381  * @param int $maxRecords Maximum number of records to return
382  * @param string $sort_mode Sort order
383  * @param string $find Search for in instance name or instance description
384  * @param string $where Ads to the query, be carefull with this string, read the query before
385  * @return array Associative
386  * @access public
387  */
388  function monitor_list_instances($offset,$maxRecords,$sort_mode,$find,$where='')
389  {
390    $wherevars = array();
391    if($find) {
392      $findesc = $this->qstr('%'.$find.'%');
393      $mid=' where ((`wf_properties` like ?) or (gi.`wf_name` like ?)
394        or (ga.`wf_name` like ?) or (gp.`wf_name` like ?))';
395        $wherevars[] = $findesc;
396        $wherevars[] = $findesc;
397        $wherevars[] = $findesc;
398        $wherevars[] = $findesc;
399    } else {
400      $mid='';
401    }
402    if($where) {
403      if($mid) {
404        $mid.= " and ($where) ";
405      } else {
406        $mid.= " where ($where) ";
407      }
408    }
409
410    $query = 'select gp.wf_p_id, ga.wf_is_interactive,gp.wf_normalized_name as wf_proc_normalized_name, gi.wf_owner, gp.wf_name as wf_procname, gp.wf_version, ga.wf_type,';
411    $query.= ' ga.wf_activity_id, ga.wf_name as wf_activity_name, gi.wf_instance_id, gi.wf_name as wf_instance_name, gi.wf_priority, gi.wf_status, gia.wf_activity_id, gia.wf_user, gia.wf_started as wf_act_started, gi.wf_started, gi.wf_ended, gia.wf_status as wf_act_status ';
412    $query.= ' from `'.GALAXIA_TABLE_PREFIX.'instances` gi LEFT JOIN `'.GALAXIA_TABLE_PREFIX.'instance_activities` gia ON gi.`wf_instance_id`=gia.`wf_instance_id` ';
413    $query.= 'LEFT JOIN `'.GALAXIA_TABLE_PREFIX.'activities` ga ON gia.`wf_activity_id` = ga.`wf_activity_id` ';
414    $query.= 'LEFT JOIN `'.GALAXIA_TABLE_PREFIX."processes` gp ON gp.`wf_p_id`=gi.`wf_p_id` $mid";
415
416    $query_cant = 'select count(*) from `'.GALAXIA_TABLE_PREFIX.'instances` gi LEFT JOIN `'.GALAXIA_TABLE_PREFIX.'instance_activities` gia ON gi.`wf_instance_id`=gia.`wf_instance_id` ';
417    $query_cant.= 'LEFT JOIN `'.GALAXIA_TABLE_PREFIX.'activities` ga ON gia.`wf_activity_id` = ga.`wf_activity_id` LEFT JOIN `'.GALAXIA_TABLE_PREFIX."processes` gp ON gp.`wf_p_id`=gi.`wf_p_id` $mid";
418    $result = $this->query($query,$wherevars,$maxRecords,$offset,true,$this->convert_sortmode($sort_mode));
419        $cant = $this->getOne($query_cant,$wherevars);
420
421    $ret = Array();
422        while($res = $result->fetchRow())
423      $ret[] = $res;
424
425    $retval = Array();
426    $retval['data'] = $ret;
427    $retval['cant'] = $cant;
428    return $retval;
429  }
430
431 /**
432  * Lists completed instances and return stats
433  *
434  * @param int $offset First row number to return
435  * @param int $maxRecords Maximum number of records to return
436  * @param string $sort_mode Sort order
437  * @param string $find Search for in instance name or instance description
438  * @param string $where Ads to the query, be carefull with this string, read the query before
439  * @return array Associative
440  * @access public
441  */
442  function monitor_list_completed_instances($offset,$maxRecords,$sort_mode,$find,$where='')
443  {
444    $wherevars = array();
445    $mid = " where (gi.wf_status IN ('completed', 'aborted'))";
446    if($find)
447    {
448      $findesc = $this->qstr('%'.$find.'%');
449      $mid=' AND ((gi.`wf_name` LIKE ?)
450        OR (gp.`wf_name` LIKE ?))';
451      $wherevars[] = $findesc;
452      $wherevars[] = $findesc;
453      $wherevars[] = $findesc;
454    }
455
456    if($where)
457    {
458      if($mid)
459        $mid.= " and ($where) ";
460      else
461        $mid.= " where ($where) ";
462    }
463
464    $query = 'select gp.wf_p_id, gp.wf_normalized_name as wf_proc_normalized_name, gi.wf_owner, gp.wf_name as wf_procname, gp.wf_version,';
465    $query.= ' gi.wf_instance_id, gi.wf_name as wf_instance_name, gi.wf_priority, gi.wf_status, gi.wf_started, gi.wf_ended ';
466    $query.= ' from `'.GALAXIA_TABLE_PREFIX.'instances` gi LEFT JOIN `'.GALAXIA_TABLE_PREFIX."processes` gp ON gp.`wf_p_id`=gi.`wf_p_id` $mid";
467
468    $query_cant = 'select count(*) from `'.GALAXIA_TABLE_PREFIX.'instances` gi LEFT JOIN `'.GALAXIA_TABLE_PREFIX."processes` gp ON gp.`wf_p_id`=gi.`wf_p_id` $mid";
469    $result = $this->query($query,$wherevars,$maxRecords,$offset,true,$this->convert_sortmode($sort_mode));
470    $cant = $this->getOne($query_cant,$wherevars);
471
472    $ret = Array();
473    while($res = $result->fetchRow())
474      $ret[] = $res;
475
476    $retval = Array();
477    $retval['data'] = $ret;
478    $retval['cant'] = $cant;
479    return $retval;
480  }
481 
482 /**
483  * Lists all processes
484  *
485  * @param string $sort_mode Sort order
486  * @param string $where Ads to the query, be carefull with this string, read the query before
487  * @return array Associative
488  * @access public
489  */
490  function monitor_list_all_processes($sort_mode = 'wf_name_asc', $where = '') {
491    if (!empty($where)) {
492      $where = " where ($where) ";
493    }
494    $query = "select `wf_name`,`wf_version`,`wf_p_id` from `".GALAXIA_TABLE_PREFIX."processes` $where order by ".$this->convert_sortmode($sort_mode);
495    $result = $this->query($query);
496    $ret = Array();
497    while($res = $result->fetchRow()) {
498      $pId = $res['wf_p_id'];
499      $ret[$pId] = $res;
500    }
501    return $ret;
502  }
503 
504 /**
505  * Lists all activities
506  *
507  * @param string $sort_mode Sort order
508  * @param string $where Ads to the query, be carefull with this string, read the query before
509  * @return array Associative
510  * @access public
511  */
512  function monitor_list_all_activities($sort_mode = 'wf_name_asc', $where = '') {
513    if (!empty($where)) {
514      $where = " where ($where) ";
515    }
516    $query = "select `wf_name`,`wf_activity_id` from `".GALAXIA_TABLE_PREFIX."activities` $where order by ".$this->convert_sortmode($sort_mode);
517    $result = $this->query($query);
518    $ret = Array();
519    while($res = $result->fetchRow()) {
520      $aid = $res['wf_activity_id'];
521      $ret[$aid] = $res;
522    }
523    return $ret;
524  }
525 
526  /**
527   * Lists instances status types in the database
528   *
529   * @return array Associative
530   * @access public
531   */
532  function monitor_list_statuses() {
533    $query = "select distinct(`wf_status`) from `".GALAXIA_TABLE_PREFIX."instances`";
534    $result = $this->query($query);
535    $ret = Array();
536    while($res = $result->fetchRow()) {
537      $ret[] = $res['wf_status'];
538    }
539    return $ret;
540  }
541 
542  /**
543   * List users associated with instances available in the current database
544   *
545   * @return array Associative
546   * @access public
547   */ 
548  function monitor_list_users($where = null, $bindVars = null)
549  {
550    $query = "select distinct(`wf_user`) from `".GALAXIA_TABLE_PREFIX."instance_activities`";
551    if (!is_null($where))
552      $query .= ' WHERE ' . $where;
553    if (!is_null($bindVars) && is_array($bindVars))
554      $result = $this->query($query);
555    else
556      $result = $this->query($query, $bindVars);
557    $ret = Array();
558    while($res = $result->fetchRow()) {
559      $ret[] = $res['wf_user'];
560    }
561    return $ret;
562  }
563
564  /**
565   * List users associated with workitems available in the current database
566   *
567   * @return array Associative
568   * @access public
569   */ 
570  function monitor_list_wi_users() {
571    $query = "select distinct(`wf_user`) from `".GALAXIA_TABLE_PREFIX."workitems`";
572    $result = $this->query($query);
573    $ret = Array();
574    while($res = $result->fetchRow()) {
575      $ret[] = $res['wf_user'];
576    }
577    return $ret;
578  }
579
580  /**
581   * List instance owners available in the current database
582   *
583   * @return array Associative
584   * @access public
585   */   
586  function monitor_list_owners() {
587    $query = "select distinct(`wf_owner`) from `".GALAXIA_TABLE_PREFIX."instances`";
588    $result = $this->query($query);
589    $ret = Array();
590    while($res = $result->fetchRow()) {
591      $ret[] = $res['wf_owner'];
592    }
593    return $ret;
594  }
595 
596  /**
597   * List activity types available in the current database
598   *
599   * @return array Associative
600   * @access public
601   */
602  function monitor_list_activity_types() {
603    $query = "select distinct(`wf_type`) from `".GALAXIA_TABLE_PREFIX."activities`";
604    $result = $this->query($query);
605    $ret = Array();
606    while($res = $result->fetchRow()) {
607      $ret[] = $res['wf_type'];
608    }
609    return $ret; 
610  }
611 
612  /**
613   * Gets workitem information
614   *
615   * @param int $itemId Workitem id
616   * @return array Associative
617   * @access public
618   */ 
619  function monitor_get_workitem($itemId) {
620    $query = "select gw.`wf_order_id`,ga.`wf_name`,ga.`wf_type`,ga.`wf_is_interactive`,gp.`wf_name` as `wf_wf_procname`,gp.`wf_version`,";
621    $query.= "gw.`wf_item_id`,gw.`wf_properties`,gw.`wf_user`,`wf_started`,`wf_ended`-`wf_started` as wf_duration ";
622    $query.= "from `".GALAXIA_TABLE_PREFIX."workitems` gw,`".GALAXIA_TABLE_PREFIX."activities` ga,`".GALAXIA_TABLE_PREFIX."processes` gp where ga.`wf_activity_id`=gw.`wf_activity_id` and ga.`wf_p_id`=gp.`wf_p_id` and `wf_item_id`=?";
623    $result = $this->query($query, array($itemId));
624    $res = $result->fetchRow();
625    $res['wf_properties'] = unserialize($res['wf_properties']);
626    return $res;
627  }
628
629  /**
630  * Gets workitems per instance
631  *
632  * @param int $offset First row number to return
633  * @param int $maxRecords Maximum number of records to return
634  * @param string $sort_mode Sort order
635  * @param string $find Search for in instance name or instance description
636  * @param string $where Ads to the query, be carefull with this string, read the query before
637  * @param array $wherevars Set of vars used in where clause
638  * @access public
639  * @return array
640  */
641  function monitor_list_workitems($offset,$maxRecords,$sort_mode,$find,$where='',$wherevars=array()) {
642    $mid = '';
643    if ($where) {
644      $mid.= " and ($where) ";
645    }
646    if($find) {
647      $findesc = $this->qstr('%'.$find.'%');
648      $mid.=" and ((`wf_properties` like $findesc) or (gp.wf_name like $findesc) or (ga.wf_name like $findesc))";
649    }
650// TODO: retrieve instance status as well
651    $query = 'select wf_item_id,wf_ended-wf_started as wf_duration,ga.wf_is_interactive, ga.wf_type,gp.wf_name as wf_procname,gp.wf_version,gp.wf_normalized_name as wf_proc_normalized_name,ga.wf_name as wf_act_name,';
652    $query.= 'ga.wf_activity_id,wf_instance_id,wf_order_id,wf_properties,wf_started,wf_ended,wf_user';
653    $query.= ' from '.GALAXIA_TABLE_PREFIX.'workitems gw,'.GALAXIA_TABLE_PREFIX.'activities ga,'.GALAXIA_TABLE_PREFIX.'processes gp';
654    $query.= ' where gw.wf_activity_id=ga.wf_activity_id and ga.wf_p_id=gp.wf_p_id '.$mid.' order by '.$this->convert_sortmode($sort_mode);
655    $query_cant = "select count(*) from `".GALAXIA_TABLE_PREFIX."workitems` gw,`".GALAXIA_TABLE_PREFIX."activities` ga,`".GALAXIA_TABLE_PREFIX."processes` gp where gw.`wf_activity_id`=ga.`wf_activity_id` and ga.`wf_p_id`=gp.`wf_p_id` $mid";
656    $result = $this->query($query,$wherevars,$maxRecords,$offset);
657    $cant = $this->getOne($query_cant,$wherevars);
658    $ret = Array();
659    while($res = $result->fetchRow()) {
660      $itemId = $res['wf_item_id'];
661      $ret[$itemId] = $res;
662    }
663    $retval = Array();
664    $retval["data"] = $ret;
665    $retval["cant"] = $cant;
666    return $retval;
667  }
668
669/**
670  * Stats Instances per month
671  *
672  * @param integer $pid pid
673  * @param integer $months number of months
674  * @access public
675  * @return array
676  */
677  function stats_instances_per_month($pid, $months = 12)
678  {
679    list($year, $month) = explode('-', date('Y-m'));
680    $output = array();
681    for ($i = $months - 1; $i >= 0; $i--)
682      $output[date('Y-m', mktime(0, 0, 0, $month - $i, 1, $year))] = 0;
683    $query = "SELECT wf_started FROM " . GALAXIA_TABLE_PREFIX . "instances WHERE (wf_started > ?) AND (wf_p_id = ?)";
684    $result = $this->query($query, array(mktime(0, 0, 0, $month - ($months - 1), 1, $year), $pid));
685    while($res = $result->fetchRow())
686      $output[date('Y-m', $res['wf_started'])]++;
687
688    return $output;
689  }
690
691/**
692  * Stats activities instances
693  *
694  * @param integer $pid pid
695  * @access public
696  * @return array
697  */
698  function stats_instances_activities($pid)
699  {
700    $output = array();
701    $query = "SELECT ga.wf_name, COUNT(*) AS count FROM " . GALAXIA_TABLE_PREFIX . "instance_activities gia, " . GALAXIA_TABLE_PREFIX . "activities ga WHERE (gia.wf_activity_id = ga.wf_activity_id) AND (ga.wf_p_id = ?) GROUP BY ga.wf_name ORDER BY ga.wf_name";
702    $result = $this->query($query, array($pid));
703    while($res = $result->fetchRow())
704      $output[] = $res;
705
706    return $output;
707  }
708
709/**
710  * Stats instances per user
711  *
712  * @param integer $pid pid
713  * @param integer $users
714  * @access public
715  * @return array
716  */
717  function stats_instances_per_user($pid, $users = 10)
718  {
719    $output = array();
720    $query = "SELECT gia.wf_user, COUNT(*) AS count FROM egw_wf_instance_activities gia, egw_wf_activities ga WHERE (ga.wf_activity_id = gia.wf_activity_id) AND (gia.wf_user <> '*') AND (ga.wf_p_id = ?) GROUP BY gia.wf_user ORDER BY count DESC";
721    $result = $this->query($query, array($pid), $users);
722    while($res = $result->fetchRow())
723      $output[$res['wf_user']] = $res['count'];
724
725    return $output;
726  }
727/**
728  * Stats instances per status
729  *
730  * @param integer $pid pid
731  * @access public
732  * @return array
733  */
734  function stats_instances_per_status($pid)
735  {
736    $output = array();
737    $query = "SELECT wf_status, COUNT(*) AS count FROM egw_wf_instances WHERE (wf_p_id = ?) AND (wf_status <> 'completed') GROUP BY wf_status";
738    $result = $this->query($query, array($pid));
739    while($res = $result->fetchRow())
740      $output[$res['wf_status']] = $res['count'];
741
742    return $output;
743  }
744}
745?>
Note: See TracBrowser for help on using the repository browser.