ILIAS  eassessment Revision 61809
 All Data Structures Namespaces Files Functions Variables Groups Pages
class.ilChangeEvent.php
Go to the documentation of this file.
1 <?php
2 /* Copyright (c) 1998-2010 ILIAS open source, Extended GPL, see docs/LICENSE */
3 
4 
36 class ilChangeEvent
37 {
53  function _recordWriteEvent($obj_id, $usr_id, $action, $parent_obj_id = null)
54  {
55  global $ilDB;
56 
57  if ($parent_obj_id == null)
58  {
59  $pset = $ilDB->query('SELECT r2.obj_id par_obj_id FROM object_reference r1 '.
60  'JOIN tree t ON t.child = r1.ref_id '.
61  'JOIN object_reference r2 ON r2.ref_id = t.parent '.
62  'WHERE r1.obj_id = '.$ilDB->quote($obj_id,'integer'));
63 
64  while ($prec = $ilDB->fetchAssoc($pset))
65  {
66  $nid = $ilDB->nextId("write_event");
67  $query = sprintf('INSERT INTO write_event '.
68  '(write_id, obj_id, parent_obj_id, usr_id, action, ts) VALUES '.
69  '(%s, %s, %s, %s, %s, '.$ilDB->now().')',
70  $ilDB->quote($nid,'integer'),
71  $ilDB->quote($obj_id,'integer'),
72  $ilDB->quote($prec["par_obj_id"],'integer'),
73  $ilDB->quote($usr_id,'integer'),
74  $ilDB->quote($action,'text'));
75 
76  $aff = $ilDB->manipulate($query);
77  }
78  }
79  else
80  {
81  $nid = $ilDB->nextId("write_event");
82  $query = sprintf('INSERT INTO write_event '.
83  '(write_id, obj_id, parent_obj_id, usr_id, action, ts) '.
84  'VALUES (%s,%s,%s,%s,%s,'.$ilDB->now().')',
85  $ilDB->quote($nid,'integer'),
86  $ilDB->quote($obj_id,'integer'),
87  $ilDB->quote($parent_obj_id,'integer'),
88  $ilDB->quote($usr_id,'integer'),
89  $ilDB->quote($action,'text'));
90  $aff = $ilDB->manipulate($query);
91 
92  }
93  //error_log ('ilChangeEvent::_recordWriteEvent '.$q);
94  }
95 
104  function _recordReadEvent($a_type, $a_ref_id, $obj_id, $usr_id,
105  $isCatchupWriteEvents = true, $a_ext_rc = false, $a_ext_time = false)
106  {
107  global $ilDB, $tree;
108 
109  include_once('Services/Tracking/classes/class.ilObjUserTracking.php');
110  $validTimeSpan = ilObjUserTracking::_getValidTimeSpan();
111 
112  $query = sprintf('SELECT * FROM read_event '.
113  'WHERE obj_id = %s '.
114  'AND usr_id = %s ',
115  $ilDB->quote($obj_id,'integer'),
116  $ilDB->quote($usr_id,'integer'));
117  $res = $ilDB->query($query);
118  $row = $ilDB->fetchObject($res);
119 
120  // read counter
121  if ($a_ext_rc !== false)
122  {
123  $read_count = 'read_count = '.$ilDB->quote($a_ext_rc, "integer").", ";
124  $read_count_init = max(1, (int) $a_ext_rc);
125  $read_count_diff = max(1, (int) $a_ext_rc) - $row->read_count;
126  }
127  else
128  {
129  $read_count = 'read_count = read_count + 1, ';
130  $read_count_init = 1;
131  $read_count_diff = 1;
132  }
133 
134  if ($row)
135  {
136 
137  if ($a_ext_time !== false)
138  {
139  $time = (int) $a_ext_time;
140  }
141  else
142  {
143  $time = $ilDB->quote((time() - $row->last_access) <= $validTimeSpan
144  ? $row->spent_seconds + time() - $row->last_access
145  : $row->spent_seconds,'integer');
146  }
147  $time_diff = $time - (int) $row->spent_seconds;
148 
149  // Update
150  $query = sprintf('UPDATE read_event SET '.
151  $read_count.
152  'spent_seconds = %s, '.
153  'last_access = %s '.
154  'WHERE obj_id = %s '.
155  'AND usr_id = %s ',
156  $time,
157  $ilDB->quote(time(),'integer'),
158  $ilDB->quote($obj_id,'integer'),
159  $ilDB->quote($usr_id,'integer'));
160  $aff = $ilDB->manipulate($query);
161  }
162  else
163  {
164  if ($a_ext_time !== false)
165  {
166  $time = (int) $a_ext_time;
167  }
168  else
169  {
170  $time = 0;
171  }
172 
173  $time_diff = $time - (int) $row->spent_seconds;
174 
175  $query = sprintf('INSERT INTO read_event (obj_id,usr_id,last_access,read_count,spent_seconds,first_access) '.
176  'VALUES (%s,%s,%s,%s,%s,'.$ilDB->now().') ',
177  $ilDB->quote($obj_id,'integer'),
178  $ilDB->quote($usr_id,'integer'),
179  $ilDB->quote(time(),'integer'),
180  $ilDB->quote($read_count_init,'integer'),
181  $ilDB->quote($time,'integer'));
182 
183  $aff = $ilDB->manipulate($query);
184  }
185 
186  if ($isCatchupWriteEvents)
187  {
188  ilChangeEvent::_catchupWriteEvents($obj_id, $usr_id);
189  }
190 
191  // update parents (no categories or root)
192  if (!in_array($a_type, array("cat", "root", "crs")))
193  {
194  if ($tree->isInTree($a_ref_id))
195  {
196  $path = $tree->getPathId($a_ref_id);
197 
198  foreach ($path as $p)
199  {
200  $obj2_id = ilObject::_lookupObjId($p);
201  $obj2_type = ilObject::_lookupType($obj2_id);
202 //echo "<br>1-$obj2_type-$p-$obj2_id-";
203  if (($p != $a_ref_id) && (in_array($obj2_type, array("crs", "fold", "grp"))))
204  {
205  $query = sprintf('SELECT * FROM read_event '.
206  'WHERE obj_id = %s '.
207  'AND usr_id = %s ',
208  $ilDB->quote($obj2_id, 'integer'),
209  $ilDB->quote($usr_id, 'integer'));
210  $res2 = $ilDB->query($query);
211  if ($row2 = $ilDB->fetchAssoc($res2))
212  {
213 //echo "<br>2";
214  // update read count and spent seconds
215  $query = sprintf('UPDATE read_event SET '.
216  'childs_read_count = childs_read_count + %s ,'.
217  'childs_spent_seconds = childs_spent_seconds + %s '.
218  'WHERE obj_id = %s '.
219  'AND usr_id = %s ',
220  $ilDB->quote((int) $read_count_diff,'integer'),
221  $ilDB->quote((int) $time_diff,'integer'),
222  $ilDB->quote($obj2_id,'integer'),
223  $ilDB->quote($usr_id,'integer'));
224  $aff = $ilDB->manipulate($query);
225  }
226  else
227  {
228 //echo "<br>3";
229  $query = sprintf('INSERT INTO read_event (obj_id,usr_id,last_access,read_count,spent_seconds,first_access,'.
230  'childs_read_count, childs_spent_seconds) '.
231  'VALUES (%s,%s,%s,%s,%s,'.$ilDB->now().', %s, %s) ',
232  $ilDB->quote($obj2_id,'integer'),
233  $ilDB->quote($usr_id,'integer'),
234  $ilDB->quote(time(),'integer'),
235  $ilDB->quote(1,'integer'),
236  $ilDB->quote($time,'integer'),
237  $ilDB->quote((int) $read_count_diff,'integer'),
238  $ilDB->quote((int) $time_diff,'integer')
239  );
240  $aff = $ilDB->manipulate($query);
241  }
242  }
243  }
244  }
245  }
246 
247  // @todo:
248  // - calculate diff of spent_seconds and read_count
249  // - use ref id to get parents of types grp, crs, fold
250  // - add diffs to childs_spent_seconds and childs_read_count
251  }
252 
261  function _catchupWriteEvents($obj_id, $usr_id, $timestamp = null)
262  {
263  global $ilDB;
264 
265  $query = "SELECT obj_id FROM catch_write_events ".
266  "WHERE obj_id = ".$ilDB->quote($obj_id ,'integer')." ".
267  "AND usr_id = ".$ilDB->quote($usr_id ,'integer');
268  $res = $ilDB->query($query);
269  if($res->numRows())
270  {
271  $query = "UPDATE catch_write_events ".
272  "SET ts = ".($timestamp == null ? $ilDB->now() : $ilDB->quote($timestamp, 'timestamp'))." ".
273  "WHERE usr_id = ".$ilDB->quote($usr_id ,'integer')." ".
274  "AND obj_id = ".$ilDB->quote($obj_id ,'integer');
275  $res = $ilDB->manipulate($query);
276  }
277  else
278  {
279  $query = "INSERT INTO catch_write_events (ts,obj_id,usr_id) ".
280  "VALUES( ".
281  $ilDB->now().", ".
282  $ilDB->quote($obj_id,'integer').", ".
283  $ilDB->quote($usr_id,'integer')." ".
284  ")";
285  $res = $ilDB->manipulate($query);
286 
287  }
288 
289  /*
290  $q = "INSERT INTO catch_write_events ".
291  "(obj_id, usr_id, ts) ".
292  "VALUES (".
293  $ilDB->quote($obj_id,'integer').",".
294  $ilDB->quote($usr_id,'integer').",";
295  if ($timestamp == null)
296  {
297  $q .= "NOW()".
298  ") ON DUPLICATE KEY UPDATE ts=NOW()";
299  }
300  else {
301  $q .= $ilDB->quote($timestamp).
302  ") ON DUPLICATE KEY UPDATE ts=".$ilDB->quote($timestamp);
303  }
304  //error_log ('ilChangeEvent::_catchupWriteEvents '.$q);
305  $r = $ilDB->query($q);
306  */
307  }
355  public static function _lookupUncaughtWriteEvents($obj_id, $usr_id)
356  {
357  global $ilDB;
358 
359  $q = "SELECT ts ".
360  "FROM catch_write_events ".
361  "WHERE obj_id=".$ilDB->quote($obj_id ,'integer')." ".
362  "AND usr_id=".$ilDB->quote($usr_id ,'integer');
363  $r = $ilDB->query($q);
364  $catchup = null;
365  while ($row = $r->fetchRow(DB_FETCHMODE_ASSOC)) {
366  $catchup = $row['ts'];
367  }
368 
369  if($catchup == null)
370  {
371  $query = sprintf('SELECT * FROM write_event '.
372  'WHERE obj_id = %s '.
373  'AND usr_id <> %s '.
374  'ORDER BY ts DESC',
375  $ilDB->quote($obj_id,'integer'),
376  $ilDB->quote($usr_id,'integer'));
377  $res = $ilDB->query($query);
378  }
379  else
380  {
381  $query = sprintf('SELECT * FROM write_event '.
382  'WHERE obj_id = %s '.
383  'AND usr_id <> %s '.
384  'AND ts >= %s '.
385  'ORDER BY ts DESC',
386  $ilDB->quote($obj_id,'integer'),
387  $ilDB->quote($usr_id,'integer'),
388  $ilDB->quote($catchup,'timestamp'));
389  $res = $ilDB->query($query);
390  }
391  $events = array();
392  while($row = $ilDB->fetchAssoc($res))
393  {
394  $events[] = $row;
395  }
396  return $events;
397  }
408  public static function _lookupChangeState($obj_id, $usr_id)
409  {
410  global $ilDB;
411 
412  $q = "SELECT ts ".
413  "FROM catch_write_events ".
414  "WHERE obj_id=".$ilDB->quote($obj_id ,'integer')." ".
415  "AND usr_id=".$ilDB->quote($usr_id ,'integer');
416  $r = $ilDB->query($q);
417  $catchup = null;
418  while ($row = $r->fetchRow(DB_FETCHMODE_ASSOC)) {
419  $catchup = $row['ts'];
420  }
421 
422  if($catchup == null)
423  {
424  $ilDB->setLimit(1);
425  $query = sprintf('SELECT * FROM write_event '.
426  'WHERE obj_id = %s '.
427  'AND usr_id <> %s ',
428  $ilDB->quote($obj_id,'integer'),
429  $ilDB->quote($usr_id,'integer'));
430  $res = $ilDB->query($query);
431  }
432  else
433  {
434  $ilDB->setLimit(1);
435  $query = sprintf('SELECT * FROM write_event '.
436  'WHERE obj_id = %s '.
437  'AND usr_id <> %s '.
438  'AND ts > %s ',
439  $ilDB->quote($obj_id,'integer'),
440  $ilDB->quote($usr_id,'integer'),
441  $ilDB->quote($catchup,'timestamp'));
442  $res = $ilDB->query($query);
443  }
444 
445  $numRows = $res->numRows();
446  if ($numRows > 0)
447  {
448  $row = $ilDB->fetchAssoc($res);
449  // if we have write events, and user never catched one, report as new (1)
450  // if we have write events, and user catched an old write event, report as changed (2)
451  return ($catchup == null) ? 1 : 2;
452  }
453  else
454  {
455  return 0; // user catched all write events, report as unchanged (0)
456  }
457  }
473  public static function _lookupInsideChangeState($parent_obj_id, $usr_id)
474  {
475  global $ilDB;
476 
477  $q = "SELECT ts ".
478  "FROM catch_write_events ".
479  "WHERE obj_id=".$ilDB->quote($parent_obj_id)." ".
480  "AND usr_id=".$ilDB->quote($usr_id);
481  $r = $ilDB->query($q);
482  $catchup = null;
483  while ($row = $r->fetchRow(DB_FETCHMODE_ASSOC)) {
484  $catchup = $row['ts'];
485  }
486 
487  if($catchup == null)
488  {
489  $ilDB->setLimit(1);
490  $query = sprintf('SELECT * FROM write_event '.
491  'WHERE parent_obj_id = %s '.
492  'AND usr_id <> %s ',
493  $ilDB->quote($parent_obj_id,'integer'),
494  $ilDB->quote($usr_id,'integer'));
495  $res = $ilDB->query($query);
496  }
497  else
498  {
499  $ilDB->setLimit(1);
500  $query = sprintf('SELECT * FROM write_event '.
501  'WHERE parent_obj_id = %s '.
502  'AND usr_id <> %s '.
503  'AND ts > %s ',
504  $ilDB->quote($parent_obj_id,'integer'),
505  $ilDB->quote($usr_id,'integer'),
506  $ilDB->quote($catchup,'timestamp'));
507  $res = $ilDB->query($query);
508  }
509  $numRows = $res->numRows();
510  if ($numRows > 0)
511  {
512  $row = $ilDB->fetchAssoc($res);
513  // if we have write events, and user never catched one, report as new (1)
514  // if we have write events, and user catched an old write event, report as changed (2)
515  return ($catchup == null) ? 1 : 2;
516  }
517  else
518  {
519  return 0; // user catched all write events, report as unchanged (0)
520  }
521  }
566  public static function _lookupReadEvents($obj_id, $usr_id = null)
567  {
568  global $ilDB;
569 
570  if ($usr_id == null)
571  {
572  $query = sprintf('SELECT * FROM read_event '.
573  'WHERE obj_id = %s '.
574  'ORDER BY last_access DESC',
575  $ilDB->quote($obj_id,'integer'));
576  $res = $ilDB->query($query);
577  }
578  else
579  {
580  $query = sprintf('SELECT * FROM read_event '.
581  'WHERE obj_id = %s '.
582  'AND usr_id = %s '.
583  'ORDER BY last_access DESC',
584  $ilDB->quote($obj_id,'integer'),
585  $ilDB->quote($usr_id,'integer'));
586  $res = $ilDB->query($query);
587  }
588 
589  $counter = 0;
590  while ($row = $ilDB->fetchAssoc($res))
591  {
592  $events[$counter]['obj_id'] = $row['obj_id'];
593  $events[$counter]['usr_id'] = $row['usr_id'];
594  $events[$counter]['last_access'] = $row['last_access'];
595  $events[$counter]['read_count'] = $row['read_count'];
596  $events[$counter]['spent_seconds'] = $row['spent_seconds'];
597  $events[$counter]['first_access'] = $row['first_access'];
598 
599  $counter++;
600 
601  }
602  return $events ? $events : array();
603  }
604 
611  public static function lookupUsersInProgress($a_obj_id)
612  {
613  global $ilDB;
614 
615  $query = sprintf('SELECT DISTINCT(usr_id) usr FROM read_event '.
616  'WHERE obj_id = %s ',
617  $ilDB->quote($a_obj_id,'integer'));
618  $res = $ilDB->query($query);
619  while($row = $ilDB->fetchObject($res))
620  {
621  $users[] = $row->usr;
622  }
623  return $users ? $users : array();
624  }
625 
629  static function hasAccessed($a_obj_id, $a_usr_id)
630  {
631  global $ilDB;
632 
633  $set = $ilDB->query("SELECT usr_id FROM read_event WHERE ".
634  "obj_id = ".$ilDB->quote($a_obj_id, "integer")." AND ".
635  "usr_id = ".$ilDB->quote($a_usr_id, "integer")
636  );
637  if ($rec = $ilDB->fetchAssoc($set))
638  {
639  return true;
640  }
641  return false;
642  }
643 
649  public static function _activate() {
650  if (ilChangeEvent::_isActive())
651  {
652  return 'change event tracking is already active';
653  }
654  else
655  {
656  global $ilDB;
657 
658  // Insert initial data into table write_event
659  // We need to do this here, because we need
660  // to catch up write events that occured while the change event tracking was
661  // deactivated.
662 
663  // IGNORE isn't supported in oracle
664  $set = $ilDB->query(sprintf('SELECT r1.obj_id,r2.obj_id p,d.owner,%s,d.create_date '.
665  'FROM object_data d '.
666  'LEFT JOIN write_event w ON d.obj_id = w.obj_id '.
667  'JOIN object_reference r1 ON d.obj_id=r1.obj_id '.
668  'JOIN tree t ON t.child=r1.ref_id '.
669  'JOIN object_reference r2 on r2.ref_id=t.parent '.
670  'WHERE w.obj_id IS NULL',
671  $ilDB->quote('create','text')));
672  while ($rec = $ilDB->fetchAssoc($set))
673  {
674  $nid = $ilDB->nextId("write_event");
675  $query = 'INSERT INTO write_event '.
676  '(write_id, obj_id,parent_obj_id,usr_id,action,ts) VALUES ('.
677  $ilDB->quote($nid, "integer").",".
678  $ilDB->quote($rec["obj_id"], "integer").",".
679  $ilDB->quote($rec["p"], "integer").",".
680  $ilDB->quote($rec["owner"], "integer").",".
681  $ilDB->quote("create", "text").",".
682  $ilDB->quote($rec["create_date"], "timestamp").
683  ')';
684  $res = $ilDB->query($query);
685  }
686 
687  if ($ilDB->isError($res) || $ilDB->isError($res->result))
688  {
689  return 'couldn\'t insert initial data into table "write_event": '.
690  (($ilDB->isError($r->result)) ? $r->result->getMessage() : $r->getMessage());
691  }
692 
693 
694  global $ilias;
695  $ilias->setSetting('enable_change_event_tracking', '1');
696 
697  return $res;
698  }
699  }
700 
706  public static function _deactivate() {
707  global $ilias;
708  $ilias->setSetting('enable_change_event_tracking', '0');
709 
710  }
711 
717  public static function _isActive() {
718  global $ilias;
719  return $ilias->getSetting('enable_change_event_tracking', '0') == '1';
720 
721  }
722 
729  public static function _delete($a_obj_id)
730  {
731  global $ilDB;
732 
733  $query = sprintf('DELETE FROM write_event WHERE obj_id = %s ',
734  $ilDB->quote($a_obj_id,'integer'));
735  $aff = $ilDB->manipulate($query);
736 
737  $query = sprintf('DELETE FROM read_event WHERE obj_id = %s ',
738  $ilDB->quote($a_obj_id,'integer'));
739  $aff = $ilDB->manipulate($query);
740  return true;
741  }
742 }
743 ?>