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

Revision 3060, 30.2 KB checked in by viani, 14 years ago (diff)

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