1<?php 2/* Copyright (c) 1998-2010 ILIAS open source, Extended GPL, see docs/LICENSE */ 3 4 5/** 6* Class ilChangeEvent tracks change events on repository objects. 7* 8* The following events are considered to be a 'write event': 9* - The creation of a new repository object 10* - A change of the data or meta-data of an object 11* - A move, link, copy, deletion or undeletion of the object 12* UI objects, which cause a 'write event', must call _recordWriteEvent(...) 13* In most cases, UI objects let the user catch up with write events on the 14* object, when doing this call. 15* 16* The following events are considered to be a 'read event': 17* - Opening a container object in the browser* 18* - Opening / downloading / reading an object 19* UI objects, which cause a 'read event', must call _recordReadEvent(...). 20* In most cases, UI objects let the user catch up with write events on the 21* object, when doing this call. 22* 23* *reading the content of a container using WebDAV is not counted, because WebDAV 24* clients can't see all objects in a container. 25* 26* A user can catch up with write events, by calling __catchupWriteEvents(...). 27* 28* A user can query, if an object has changed, since the last time he has caught 29* up with write events, by calling _lookupUncaughtWriteEvents(...). 30* 31* 32* @author Werner Randelshofer <werner.randelshofer@hslu.ch> 33* @version $Id: class.ilChangeEvent.php,v 1.02 2007/05/07 19:25:34 wrandels Exp $ 34* 35*/ 36class ilChangeEvent 37{ 38 private static $has_accessed = array(); 39 40 /** 41 * Records a write event. 42 * 43 * The parent object should be specified for the 'delete', 'undelete' and 44 * 'add' and 'remove' events. 45 * 46 * @param $obj_id int The object which was written to. 47 * @param $usr_id int The user who performed a write action. 48 * @param $action string The name of the write action. 49 * 'create', 'update', 'delete', 'add', 'remove', 'undelete'. 50 * @param $parent_obj_id int The object id of the parent object. 51 * If this is null, then the event is recorded for all parents 52 * of the object. If this is not null, then the event is only 53 * recorded for the specified parent. 54 */ 55 public static function _recordWriteEvent($obj_id, $usr_id, $action, $parent_obj_id = null) 56 { 57 global $DIC; 58 59 $ilDB = $DIC['ilDB']; 60 61 /* see _recordReadEvent 62 if (!ilChangeEvent::_isActive()) 63 { 64 return; 65 } 66 */ 67 68 if ($parent_obj_id == null) { 69 $pset = $ilDB->query('SELECT r2.obj_id par_obj_id FROM object_reference r1 ' . 70 'JOIN tree t ON t.child = r1.ref_id ' . 71 'JOIN object_reference r2 ON r2.ref_id = t.parent ' . 72 'WHERE r1.obj_id = ' . $ilDB->quote($obj_id, 'integer')); 73 74 while ($prec = $ilDB->fetchAssoc($pset)) { 75 $nid = $ilDB->nextId("write_event"); 76 $query = sprintf( 77 'INSERT INTO write_event ' . 78 '(write_id, obj_id, parent_obj_id, usr_id, action, ts) VALUES ' . 79 '(%s, %s, %s, %s, %s, ' . $ilDB->now() . ')', 80 $ilDB->quote($nid, 'integer'), 81 $ilDB->quote($obj_id, 'integer'), 82 $ilDB->quote($prec["par_obj_id"], 'integer'), 83 $ilDB->quote($usr_id, 'integer'), 84 $ilDB->quote($action, 'text') 85 ); 86 87 $aff = $ilDB->manipulate($query); 88 } 89 } else { 90 $nid = $ilDB->nextId("write_event"); 91 $query = sprintf( 92 'INSERT INTO write_event ' . 93 '(write_id, obj_id, parent_obj_id, usr_id, action, ts) ' . 94 'VALUES (%s,%s,%s,%s,%s,' . $ilDB->now() . ')', 95 $ilDB->quote($nid, 'integer'), 96 $ilDB->quote($obj_id, 'integer'), 97 $ilDB->quote($parent_obj_id, 'integer'), 98 $ilDB->quote($usr_id, 'integer'), 99 $ilDB->quote($action, 'text') 100 ); 101 $aff = $ilDB->manipulate($query); 102 } 103 } 104 105 /** 106 * Records a read event and catches up with write events. 107 * 108 * @param $obj_id int The object which was read. 109 * @param $usr_id int The user who performed a read action. 110 * @param $catchupWriteEvents boolean If true, this function catches up with 111 * write events. 112 */ 113 public static function _recordReadEvent( 114 $a_type, 115 $a_ref_id, 116 $obj_id, 117 $usr_id, 118 $isCatchupWriteEvents = true, 119 $a_ext_rc = false, 120 $a_ext_time = false 121 ) { 122 global $DIC; 123 124 $ilDB = $DIC['ilDB']; 125 $tree = $DIC['tree']; 126 127 /* read_event data is now used for several features, so we are always keeping track 128 if (!ilChangeEvent::_isActive()) 129 { 130 return; 131 } 132 */ 133 134 include_once('Services/Tracking/classes/class.ilObjUserTracking.php'); 135 $validTimeSpan = ilObjUserTracking::_getValidTimeSpan(); 136 137 $query = sprintf( 138 'SELECT * FROM read_event ' . 139 'WHERE obj_id = %s ' . 140 'AND usr_id = %s ', 141 $ilDB->quote($obj_id, 'integer'), 142 $ilDB->quote($usr_id, 'integer') 143 ); 144 $res = $ilDB->query($query); 145 $row = $ilDB->fetchObject($res); 146 147 // read counter 148 if ($a_ext_rc !== false) { 149 $read_count = 'read_count = ' . $ilDB->quote($a_ext_rc, "integer") . ", "; 150 $read_count_init = max(1, (int) $a_ext_rc); 151 $read_count_diff = max(1, (int) $a_ext_rc) - $row->read_count; 152 } else { 153 $read_count = 'read_count = read_count + 1, '; 154 $read_count_init = 1; 155 $read_count_diff = 1; 156 } 157 158 if ($row) { 159 if ($a_ext_time !== false) { 160 $time = (int) $a_ext_time; 161 } else { 162 $time = $ilDB->quote((time() - $row->last_access) <= $validTimeSpan 163 ? $row->spent_seconds + time() - $row->last_access 164 : $row->spent_seconds, 'integer'); 165 166 // if we are in the valid interval, we do not 167 // add anything to the read_count, since this is the 168 // same access for us 169 if ((time() - $row->last_access) <= $validTimeSpan) { 170 $read_count = ''; 171 $read_count_init = 1; 172 $read_count_diff = 0; 173 } 174 } 175 $time_diff = $time - (int) $row->spent_seconds; 176 177 // Update 178 $query = sprintf( 179 'UPDATE read_event SET ' . 180 $read_count . 181 'spent_seconds = %s, ' . 182 'last_access = %s ' . 183 'WHERE obj_id = %s ' . 184 'AND usr_id = %s ', 185 $time, 186 $ilDB->quote(time(), 'integer'), 187 $ilDB->quote($obj_id, 'integer'), 188 $ilDB->quote($usr_id, 'integer') 189 ); 190 $aff = $ilDB->manipulate($query); 191 192 self::_recordObjStats($obj_id, $time_diff, $read_count_diff); 193 } else { 194 if ($a_ext_time !== false) { 195 $time = (int) $a_ext_time; 196 } else { 197 $time = 0; 198 } 199 200 $time_diff = $time - (int) $row->spent_seconds; 201 202 /* 203 $query = sprintf('INSERT INTO read_event (obj_id,usr_id,last_access,read_count,spent_seconds,first_access) '. 204 'VALUES (%s,%s,%s,%s,%s,'.$ilDB->now().') ', 205 $ilDB->quote($obj_id,'integer'), 206 $ilDB->quote($usr_id,'integer'), 207 $ilDB->quote(time(),'integer'), 208 $ilDB->quote($read_count_init,'integer'), 209 $ilDB->quote($time,'integer')); 210 $ilDB->manipulate($query); 211 */ 212 213 // #10407 214 $ilDB->replace( 215 'read_event', 216 array( 217 'obj_id' => array('integer', $obj_id), 218 'usr_id' => array('integer', $usr_id) 219 ), 220 array( 221 'read_count' => array('integer', $read_count_init), 222 'spent_seconds' => array('integer', $time), 223 'first_access' => array('timestamp', date("Y-m-d H:i:s")), // was $ilDB->now() 224 'last_access' => array('integer', time()) 225 ) 226 ); 227 228 self::$has_accessed[$obj_id][$usr_id] = true; 229 230 self::_recordObjStats($obj_id, $time_diff, $read_count_diff); 231 } 232 233 if ($isCatchupWriteEvents) { 234 ilChangeEvent::_catchupWriteEvents($obj_id, $usr_id); 235 } 236 237 // update parents (no categories or root) 238 if (!in_array($a_type, array("cat", "root", "crs"))) { 239 if ($tree->isInTree($a_ref_id)) { 240 $path = $tree->getPathId($a_ref_id); 241 242 foreach ($path as $p) { 243 $obj2_id = ilObject::_lookupObjId($p); 244 $obj2_type = ilObject::_lookupType($obj2_id); 245 //echo "<br>1-$obj2_type-$p-$obj2_id-"; 246 if (($p != $a_ref_id) && (in_array($obj2_type, array("crs", "fold", "grp", "lso")))) { 247 $query = sprintf( 248 'SELECT * FROM read_event ' . 249 'WHERE obj_id = %s ' . 250 'AND usr_id = %s ', 251 $ilDB->quote($obj2_id, 'integer'), 252 $ilDB->quote($usr_id, 'integer') 253 ); 254 $res2 = $ilDB->query($query); 255 if ($row2 = $ilDB->fetchAssoc($res2)) { 256 //echo "<br>2"; 257 // update read count and spent seconds 258 $query = sprintf( 259 'UPDATE read_event SET ' . 260 'childs_read_count = childs_read_count + %s ,' . 261 'childs_spent_seconds = childs_spent_seconds + %s ' . 262 'WHERE obj_id = %s ' . 263 'AND usr_id = %s ', 264 $ilDB->quote((int) $read_count_diff, 'integer'), 265 $ilDB->quote((int) $time_diff, 'integer'), 266 $ilDB->quote($obj2_id, 'integer'), 267 $ilDB->quote($usr_id, 'integer') 268 ); 269 $aff = $ilDB->manipulate($query); 270 271 self::_recordObjStats($obj2_id, null, null, (int) $time_diff, (int) $read_count_diff); 272 } else { 273 //echo "<br>3"; 274 //$ilLog->write("insert read event for obj_id -".$obj2_id."-".$usr_id."-"); 275 /* 276 $query = sprintf('INSERT INTO read_event (obj_id,usr_id,last_access,read_count,spent_seconds,first_access,'. 277 'childs_read_count, childs_spent_seconds) '. 278 'VALUES (%s,%s,%s,%s,%s,'.$ilDB->now().', %s, %s) ', 279 $ilDB->quote($obj2_id,'integer'), 280 $ilDB->quote($usr_id,'integer'), 281 $ilDB->quote(time(),'integer'), 282 $ilDB->quote(1,'integer'), 283 $ilDB->quote($time,'integer'), 284 $ilDB->quote((int) $read_count_diff,'integer'), 285 $ilDB->quote((int) $time_diff,'integer') 286 ); 287 $aff = $ilDB->manipulate($query); 288 */ 289 290 // #10407 291 $ilDB->replace( 292 'read_event', 293 array( 294 'obj_id' => array('integer', $obj2_id), 295 'usr_id' => array('integer', $usr_id) 296 ), 297 array( 298 'read_count' => array('integer', 1), 299 'spent_seconds' => array('integer', $time), 300 'first_access' => array('timestamp', date("Y-m-d H:i:s")), // was $ilDB->now() 301 'last_access' => array('integer', time()), 302 'childs_read_count' => array('integer', (int) $read_count_diff), 303 'childs_spent_seconds' => array('integer', (int) $time_diff) 304 ) 305 ); 306 307 self::$has_accessed[$obj2_id][$usr_id] = true; 308 309 self::_recordObjStats($obj2_id, $time, 1, (int) $time_diff, (int) $read_count_diff); 310 } 311 } 312 } 313 } 314 } 315 316 // @todo: 317 // - calculate diff of spent_seconds and read_count 318 // - use ref id to get parents of types grp, crs, fold 319 // - add diffs to childs_spent_seconds and childs_read_count 320 } 321 322 public static function _recordObjStats($a_obj_id, $a_spent_seconds, $a_read_count, $a_childs_spent_seconds = null, $a_child_read_count = null) 323 { 324 global $DIC; 325 326 $ilDB = $DIC['ilDB']; 327 328 if (!ilObjUserTracking::_enabledObjectStatistics() || 329 (int) $a_obj_id <= 0) { // #12706 330 return; 331 } 332 333 $now = time(); 334 335 $fields = array(); 336 $fields['log_id'] = array("integer", $ilDB->nextId('obj_stat_log')); 337 $fields["obj_id"] = array("integer", $a_obj_id); 338 $fields["obj_type"] = array("text", ilObject::_lookupType($a_obj_id)); 339 $fields["tstamp"] = array("timestamp", $now); 340 $fields["yyyy"] = array("integer", date("Y")); 341 $fields["mm"] = array("integer", date("m")); 342 $fields["dd"] = array("integer", date("d")); 343 $fields["hh"] = array("integer", date("H")); 344 if ($a_spent_seconds > 0) { 345 $fields["spent_seconds"] = array("integer", $a_spent_seconds); 346 } 347 if ($a_read_count > 0) { 348 $fields["read_count"] = array("integer", $a_read_count); 349 } 350 if ($a_childs_spent_seconds > 0) { 351 $fields["childs_spent_seconds"] = array("integer", $a_childs_spent_seconds); 352 } 353 if ($a_child_read_count > 0) { 354 $fields["childs_read_count"] = array("integer", $a_child_read_count); 355 } 356 $ilDB->insert("obj_stat_log", $fields); 357 358 // 0.01% probability 359 if (mt_rand(1, 100) == 1) { 360 self::_syncObjectStats($now); 361 } 362 } 363 364 /** 365 * Process object statistics log data 366 * 367 * @param integer $a_now 368 * @param integer $a_minimum 369 */ 370 public static function _syncObjectStats($a_now = null, $a_minimum = 20000) 371 { 372 global $DIC; 373 374 $ilDB = $DIC['ilDB']; 375 376 if (!$a_now) { 377 $a_now = time(); 378 } 379 380 set_time_limit(0); 381 382 // has source table enough entries? 383 $set = $ilDB->query("SELECT COUNT(*) AS counter FROM obj_stat_log"); 384 $row = $ilDB->fetchAssoc($set); 385 if ($row["counter"] >= $a_minimum) { 386 $ilAtomQuery = $ilDB->buildAtomQuery(); 387 $ilAtomQuery->addTableLock('obj_stat_log'); 388 $ilAtomQuery->addTableLock('obj_stat_tmp'); 389 390 $ilAtomQuery->addQueryCallable(function (ilDBInterface $ilDB) use ($a_now, $a_minimum, &$ret) { 391 392 // if other process was transferring, we had to wait for the lock and 393 // the source table should now have less than minimum/needed entries 394 $set = $ilDB->query("SELECT COUNT(*) AS counter FROM obj_stat_log"); 395 $row = $ilDB->fetchAssoc($set); 396 if ($row["counter"] >= $a_minimum) { 397 // use only "full" seconds to have a clear cut 398 $ilDB->query("INSERT INTO obj_stat_tmp" . 399 " SELECT * FROM obj_stat_log" . 400 " WHERE tstamp < " . $ilDB->quote($a_now, "timestamp")); 401 402 // remove transferred entries from source table 403 $ilDB->query("DELETE FROM obj_stat_log" . 404 " WHERE tstamp < " . $ilDB->quote($a_now, "timestamp")); 405 406 $ret = true; 407 } else { 408 $ret = false; 409 } 410 }); 411 412 $ilAtomQuery->run(); 413 414 //continue only if obj_stat_log counter >= $a_minimum 415 if ($ret) { 416 $ilAtomQuery = $ilDB->buildAtomQuery(); 417 $ilAtomQuery->addTableLock('obj_stat_tmp'); 418 $ilAtomQuery->addTableLock('obj_stat'); 419 420 $ilAtomQuery->addQueryCallable(function (ilDBInterface $ilDB) use ($a_now, $a_minimum) { 421 422 // process log data (timestamp is not needed anymore) 423 $sql = "SELECT obj_id, obj_type, yyyy, mm, dd, hh, SUM(read_count) AS read_count," . 424 " SUM(childs_read_count) AS childs_read_count, SUM(spent_seconds) AS spent_seconds," . 425 " SUM(childs_spent_seconds) AS childs_spent_seconds" . 426 " FROM obj_stat_tmp" . 427 " GROUP BY obj_id, obj_type, yyyy, mm, dd, hh"; 428 $set = $ilDB->query($sql); 429 while ($row = $ilDB->fetchAssoc($set)) { 430 // "primary key" 431 $where = array("obj_id" => array("integer", $row["obj_id"]), 432 "obj_type" => array("text", $row["obj_type"]), 433 "yyyy" => array("integer", $row["yyyy"]), 434 "mm" => array("integer", $row["mm"]), 435 "dd" => array("integer", $row["dd"]), 436 "hh" => array("integer", $row["hh"])); 437 438 $where_sql = array(); 439 foreach ($where as $field => $def) { 440 $where_sql[] = $field . " = " . $ilDB->quote($def[1], $def[0]); 441 } 442 $where_sql = implode(" AND ", $where_sql); 443 444 // existing entry? 445 $check = $ilDB->query("SELECT read_count, childs_read_count, spent_seconds," . 446 "childs_spent_seconds" . 447 " FROM obj_stat" . 448 " WHERE " . $where_sql); 449 if ($ilDB->numRows($check)) { 450 $old = $ilDB->fetchAssoc($check); 451 452 // add existing values 453 $fields = array("read_count" => array("integer", $old["read_count"] + $row["read_count"]), 454 "childs_read_count" => array("integer", $old["childs_read_count"] + $row["childs_read_count"]), 455 "spent_seconds" => array("integer", $old["spent_seconds"] + $row["spent_seconds"]), 456 "childs_spent_seconds" => array("integer", $old["childs_spent_seconds"] + $row["childs_spent_seconds"])); 457 458 $ilDB->update("obj_stat", $fields, $where); 459 } else { 460 // new entry 461 $fields = $where; 462 $fields["read_count"] = array("integer", $row["read_count"]); 463 $fields["childs_read_count"] = array("integer", $row["childs_read_count"]); 464 $fields["spent_seconds"] = array("integer", $row["spent_seconds"]); 465 $fields["childs_spent_seconds"] = array("integer", $row["childs_spent_seconds"]); 466 467 $ilDB->insert("obj_stat", $fields); 468 } 469 } 470 471 // clean up transfer table 472 $ilDB->query("DELETE FROM obj_stat_tmp"); 473 }); 474 475 $ilAtomQuery->run(); 476 } 477 } 478 } 479 480 /** 481 * Catches up with all write events which occured before the specified 482 * timestamp. 483 * 484 * @param $obj_id int The object. 485 * @param $usr_id int The user. 486 * @param $timestamp SQL timestamp. 487 */ 488 public static function _catchupWriteEvents($obj_id, $usr_id, $timestamp = null) 489 { 490 global $DIC; 491 492 $ilDB = $DIC['ilDB']; 493 494 $query = "SELECT obj_id FROM catch_write_events " . 495 "WHERE obj_id = " . $ilDB->quote($obj_id, 'integer') . " " . 496 "AND usr_id = " . $ilDB->quote($usr_id, 'integer'); 497 $res = $ilDB->query($query); 498 if ($res->numRows()) { 499 $ts = ($timestamp == null) 500 ? ilUtil::now() 501 : $timestamp; 502 /* $query = "UPDATE catch_write_events ". 503 "SET ts = ".($timestamp == null ? $ilDB->now() : $ilDB->quote($timestamp, 'timestamp'))." ". 504 "WHERE usr_id = ".$ilDB->quote($usr_id ,'integer')." ". 505 "AND obj_id = ".$ilDB->quote($obj_id ,'integer'); 506 $res = $ilDB->manipulate($query);*/ 507 } else { 508 $ts = ilUtil::now(); 509 /* $query = "INSERT INTO catch_write_events (ts,obj_id,usr_id) ". 510 "VALUES( ". 511 $ilDB->now().", ". 512 $ilDB->quote($obj_id,'integer').", ". 513 $ilDB->quote($usr_id,'integer')." ". 514 ")"; 515 $res = $ilDB->manipulate($query);*/ 516 } 517 518 // alex, use replace due to bug #10406 519 $ilDB->replace( 520 "catch_write_events", 521 array( 522 "obj_id" => array("integer", $obj_id), 523 "usr_id" => array("integer", $usr_id) 524 ), 525 array( 526 "ts" => array("timestamp", $ts)) 527 ); 528 } 529 530 /** 531 * Catches up with all write events which occured before the specified 532 * timestamp. 533 * 534 * THIS FUNCTION IS CURRENTLY NOT IN USE. BEFORE IT CAN BE USED, THE TABLE 535 * catch_read_events MUST BE CREATED. 536 * 537 * 538 * 539 * @param $obj_id int The object. 540 * @param $usr_id int The user. 541 * @param $timestamp SQL timestamp. 542 * / 543 function _catchupReadEvents($obj_id, $usr_id, $timestamp = null) 544 { 545 global $DIC; 546 547 $ilDB = $DIC['ilDB']; 548 549 550 $q = "INSERT INTO catch_read_events ". 551 "(obj_id, usr_id, action, ts) ". 552 "VALUES (". 553 $ilDB->quote($obj_id).",". 554 $ilDB->quote($usr_id).",". 555 $ilDB->quote('read').","; 556 if ($timestamp == null) 557 { 558 $q .= "NOW()". 559 ") ON DUPLICATE KEY UPDATE ts=NOW()"; 560 } 561 else { 562 $q .= $ilDB->quote($timestamp). 563 ") ON DUPLICATE KEY UPDATE ts=".$ilDB->quote($timestamp); 564 } 565 566 $r = $ilDB->query($q); 567 } 568 */ 569 570 571 /** 572 * Reads all write events which occured on the object 573 * which happened after the last time the user caught up with them. 574 * 575 * @param $obj_id int The object 576 * @param $usr_id int The user who is interested into these events. 577 * @return array with rows from table write_event 578 */ 579 public static function _lookupUncaughtWriteEvents($obj_id, $usr_id) 580 { 581 global $DIC; 582 583 $ilDB = $DIC['ilDB']; 584 585 $q = "SELECT ts " . 586 "FROM catch_write_events " . 587 "WHERE obj_id=" . $ilDB->quote($obj_id, 'integer') . " " . 588 "AND usr_id=" . $ilDB->quote($usr_id, 'integer'); 589 $r = $ilDB->query($q); 590 $catchup = null; 591 while ($row = $r->fetchRow(ilDBConstants::FETCHMODE_ASSOC)) { 592 $catchup = $row['ts']; 593 } 594 595 if ($catchup == null) { 596 $query = sprintf( 597 'SELECT * FROM write_event ' . 598 'WHERE obj_id = %s ' . 599 'AND usr_id <> %s ' . 600 'ORDER BY ts DESC', 601 $ilDB->quote($obj_id, 'integer'), 602 $ilDB->quote($usr_id, 'integer') 603 ); 604 $res = $ilDB->query($query); 605 } else { 606 $query = sprintf( 607 'SELECT * FROM write_event ' . 608 'WHERE obj_id = %s ' . 609 'AND usr_id <> %s ' . 610 'AND ts >= %s ' . 611 'ORDER BY ts DESC', 612 $ilDB->quote($obj_id, 'integer'), 613 $ilDB->quote($usr_id, 'integer'), 614 $ilDB->quote($catchup, 'timestamp') 615 ); 616 $res = $ilDB->query($query); 617 } 618 $events = array(); 619 while ($row = $ilDB->fetchAssoc($res)) { 620 $events[] = $row; 621 } 622 return $events; 623 } 624 /** 625 * Returns the change state of the object for the specified user. 626 * which happened after the last time the user caught up with them. 627 * 628 * @param $obj_id int The object 629 * @param $usr_id int The user who is interested into these events. 630 * @return 0 = object is unchanged, 631 * 1 = object is new, 632 * 2 = object has changed 633 */ 634 public static function _lookupChangeState($obj_id, $usr_id) 635 { 636 global $DIC; 637 638 $ilDB = $DIC['ilDB']; 639 640 $q = "SELECT ts " . 641 "FROM catch_write_events " . 642 "WHERE obj_id=" . $ilDB->quote($obj_id, 'integer') . " " . 643 "AND usr_id=" . $ilDB->quote($usr_id, 'integer'); 644 $r = $ilDB->query($q); 645 $catchup = null; 646 while ($row = $r->fetchRow(ilDBConstants::FETCHMODE_ASSOC)) { 647 $catchup = $row['ts']; 648 } 649 650 if ($catchup == null) { 651 $ilDB->setLimit(1); 652 $query = sprintf( 653 'SELECT * FROM write_event ' . 654 'WHERE obj_id = %s ' . 655 'AND usr_id <> %s ', 656 $ilDB->quote($obj_id, 'integer'), 657 $ilDB->quote($usr_id, 'integer') 658 ); 659 $res = $ilDB->query($query); 660 } else { 661 $ilDB->setLimit(1); 662 $query = sprintf( 663 'SELECT * FROM write_event ' . 664 'WHERE obj_id = %s ' . 665 'AND usr_id <> %s ' . 666 'AND ts > %s ', 667 $ilDB->quote($obj_id, 'integer'), 668 $ilDB->quote($usr_id, 'integer'), 669 $ilDB->quote($catchup, 'timestamp') 670 ); 671 $res = $ilDB->query($query); 672 } 673 674 $numRows = $res->numRows(); 675 if ($numRows > 0) { 676 $row = $ilDB->fetchAssoc($res); 677 // if we have write events, and user never catched one, report as new (1) 678 // if we have write events, and user catched an old write event, report as changed (2) 679 return ($catchup == null) ? 1 : 2; 680 } else { 681 return 0; // user catched all write events, report as unchanged (0) 682 } 683 } 684 685 /** 686 * Reads all read events which occured on the object 687 * which happened after the last time the user caught up with them. 688 * 689 * NOTE: THIS FUNCTION NEEDS TO BE REWRITTEN. READ EVENTS ARE OF INTEREST 690 * AT REF_ID's OF OBJECTS. 691 * 692 * @param $obj_id int The object 693 * @param $usr_id int The user who is interested into these events. 694 * / 695 public static function _lookupUncaughtReadEvents($obj_id, $usr_id) 696 { 697 global $DIC; 698 699 $ilDB = $DIC['ilDB']; 700 701 $q = "SELECT ts ". 702 "FROM catch_read_events ". 703 "WHERE obj_id=".$ilDB->quote($obj_id)." ". 704 "AND usr_id=".$ilDB->quote($usr_id); 705 $r = $ilDB->query($q); 706 $catchup = null; 707 while ($row = $r->fetchRow(ilDBConstants::FETCHMODE_ASSOC)) { 708 $catchup = $row['ts']; 709 } 710 711 $q = "SELECT * ". 712 "FROM read_event ". 713 "WHERE obj_id=".$ilDB->quote($obj_id)." ". 714 ($catchup == null ? "" : "AND last_access > ".$ilDB->quote($catchup))." ". 715 ($catchup == null ? "" : "AND last_access > ".$ilDB->quote($catchup))." ". 716 "ORDER BY last_access DESC"; 717 $r = $ilDB->query($q); 718 $events = array(); 719 while ($row = $r->fetchRow(ilDBConstants::FETCHMODE_ASSOC)) 720 { 721 $events[] = $row; 722 } 723 return $events; 724 }*/ 725 /** 726 * Reads all read events which occured on the object. 727 * 728 * @param $obj_id int The object 729 * @param $usr_id int Optional, the user who performed these events. 730 */ 731 public static function _lookupReadEvents($obj_id, $usr_id = null) 732 { 733 global $DIC; 734 735 $ilDB = $DIC['ilDB']; 736 737 if ($usr_id == null) { 738 $query = sprintf( 739 'SELECT * FROM read_event ' . 740 'WHERE obj_id = %s ' . 741 'ORDER BY last_access DESC', 742 $ilDB->quote($obj_id, 'integer') 743 ); 744 $res = $ilDB->query($query); 745 } else { 746 $query = sprintf( 747 'SELECT * FROM read_event ' . 748 'WHERE obj_id = %s ' . 749 'AND usr_id = %s ' . 750 'ORDER BY last_access DESC', 751 $ilDB->quote($obj_id, 'integer'), 752 $ilDB->quote($usr_id, 'integer') 753 ); 754 $res = $ilDB->query($query); 755 } 756 757 $counter = 0; 758 while ($row = $ilDB->fetchAssoc($res)) { 759 $events[$counter]['obj_id'] = $row['obj_id']; 760 $events[$counter]['usr_id'] = $row['usr_id']; 761 $events[$counter]['last_access'] = $row['last_access']; 762 $events[$counter]['read_count'] = $row['read_count']; 763 $events[$counter]['spent_seconds'] = $row['spent_seconds']; 764 $events[$counter]['first_access'] = $row['first_access']; 765 766 $counter++; 767 } 768 return $events ? $events : array(); 769 } 770 771 /** 772 * Lookup users in progress 773 * 774 * @return 775 * @static 776 */ 777 public static function lookupUsersInProgress($a_obj_id) 778 { 779 global $DIC; 780 781 $ilDB = $DIC['ilDB']; 782 783 $query = sprintf( 784 'SELECT DISTINCT(usr_id) usr FROM read_event ' . 785 'WHERE obj_id = %s ', 786 $ilDB->quote($a_obj_id, 'integer') 787 ); 788 $res = $ilDB->query($query); 789 while ($row = $ilDB->fetchObject($res)) { 790 $users[] = $row->usr; 791 } 792 return $users ? $users : array(); 793 } 794 795 /** 796 * Has accessed 797 */ 798 public static function hasAccessed($a_obj_id, $a_usr_id) 799 { 800 global $DIC; 801 802 $ilDB = $DIC['ilDB']; 803 804 if (isset(self::$has_accessed[$a_obj_id][$a_usr_id])) { 805 return self::$has_accessed[$a_obj_id][$a_usr_id]; 806 } 807 808 $set = $ilDB->query( 809 "SELECT usr_id FROM read_event WHERE " . 810 "obj_id = " . $ilDB->quote($a_obj_id, "integer") . " AND " . 811 "usr_id = " . $ilDB->quote($a_usr_id, "integer") 812 ); 813 if ($rec = $ilDB->fetchAssoc($set)) { 814 return self::$has_accessed[$a_obj_id][$a_usr_id] = true; 815 } 816 return self::$has_accessed[$a_obj_id][$a_usr_id] = false; 817 } 818 819 /** 820 * Activates change event tracking. 821 * 822 * @return mixed true on success, a string with an error message on failure. 823 */ 824 public static function _activate() 825 { 826 if (ilChangeEvent::_isActive()) { 827 return 'change event tracking is already active'; 828 } else { 829 global $DIC; 830 831 $ilDB = $DIC['ilDB']; 832 833 // Insert initial data into table write_event 834 // We need to do this here, because we need 835 // to catch up write events that occured while the change event tracking was 836 // deactivated. 837 838 // IGNORE isn't supported in oracle 839 $set = $ilDB->query(sprintf( 840 'SELECT r1.obj_id,r2.obj_id p,d.owner,%s,d.create_date ' . 841 'FROM object_data d ' . 842 'LEFT JOIN write_event w ON d.obj_id = w.obj_id ' . 843 'JOIN object_reference r1 ON d.obj_id=r1.obj_id ' . 844 'JOIN tree t ON t.child=r1.ref_id ' . 845 'JOIN object_reference r2 on r2.ref_id=t.parent ' . 846 'WHERE w.obj_id IS NULL', 847 $ilDB->quote('create', 'text') 848 )); 849 while ($rec = $ilDB->fetchAssoc($set)) { 850 $nid = $ilDB->nextId("write_event"); 851 $query = 'INSERT INTO write_event ' . 852 '(write_id, obj_id,parent_obj_id,usr_id,action,ts) VALUES (' . 853 $ilDB->quote($nid, "integer") . "," . 854 $ilDB->quote($rec["obj_id"], "integer") . "," . 855 $ilDB->quote($rec["p"], "integer") . "," . 856 $ilDB->quote($rec["owner"], "integer") . "," . 857 $ilDB->quote("create", "text") . "," . 858 $ilDB->quote($rec["create_date"], "timestamp") . 859 ')'; 860 $res = $ilDB->query($query); 861 } 862 863 global $DIC; 864 865 $ilSetting = $DIC['ilSetting']; 866 $ilSetting->set('enable_change_event_tracking', '1'); 867 868 return $res; 869 } 870 } 871 872 /** 873 * Deactivates change event tracking. 874 * 875 * @return mixed true on success, a string with an error message on failure. 876 */ 877 public static function _deactivate() 878 { 879 global $DIC; 880 881 $ilSetting = $DIC['ilSetting']; 882 $ilSetting->set('enable_change_event_tracking', '0'); 883 } 884 885 /** 886 * Returns true, if change event tracking is active. 887 * 888 * @return mixed true on success, a string with an error message on failure. 889 */ 890 public static function _isActive() 891 { 892 global $DIC; 893 894 $ilSetting = $DIC['ilSetting']; 895 return $ilSetting->get('enable_change_event_tracking', '0') == '1'; 896 } 897 898 /** 899 * Delete object entries 900 * 901 * @return 902 * @static 903 */ 904 public static function _delete($a_obj_id) 905 { 906 global $DIC; 907 908 $ilDB = $DIC['ilDB']; 909 910 $query = sprintf( 911 'DELETE FROM write_event WHERE obj_id = %s ', 912 $ilDB->quote($a_obj_id, 'integer') 913 ); 914 $aff = $ilDB->manipulate($query); 915 916 $query = sprintf( 917 'DELETE FROM read_event WHERE obj_id = %s ', 918 $ilDB->quote($a_obj_id, 'integer') 919 ); 920 $aff = $ilDB->manipulate($query); 921 return true; 922 } 923 924 public static function _deleteReadEvents($a_obj_id) 925 { 926 global $DIC; 927 928 $ilDB = $DIC['ilDB']; 929 930 $ilDB->manipulate("DELETE FROM read_event" . 931 " WHERE obj_id = " . $ilDB->quote($a_obj_id, "integer")); 932 } 933 934 public static function _deleteReadEventsForUsers($a_obj_id, array $a_user_ids) 935 { 936 global $DIC; 937 938 $ilDB = $DIC['ilDB']; 939 940 $ilDB->manipulate("DELETE FROM read_event" . 941 " WHERE obj_id = " . $ilDB->quote($a_obj_id, "integer") . 942 " AND " . $ilDB->in("usr_id", $a_user_ids, "", "integer")); 943 } 944 945 public static function _getAllUserIds($a_obj_id) 946 { 947 global $DIC; 948 949 $ilDB = $DIC['ilDB']; 950 951 $res = array(); 952 953 $set = $ilDB->query("SELECT usr_id FROM read_event" . 954 " WHERE obj_id = " . $ilDB->quote($a_obj_id, "integer")); 955 while ($row = $ilDB->fetchAssoc($set)) { 956 $res[] = $row["usr_id"]; 957 } 958 959 return $res; 960 } 961 962 /** 963 * _updateAccessForScormOfflinePlayer 964 * needed to synchronize last_access and first_access when learning modul is used offline 965 * called in ./Modules/ScormAicc/classes/class.ilSCORMOfflineMode.php 966 * @return true 967 */ 968 public static function _updateAccessForScormOfflinePlayer($obj_id, $usr_id, $i_last_access, $t_first_access) 969 { 970 global $DIC; 971 972 $ilDB = $DIC['ilDB']; 973 $res = $ilDB->queryF( 974 'UPDATE read_event SET first_access=%s, last_access = %s WHERE obj_id=%s AND usr_id=%s', 975 array('timestamp','integer','integer','integer'), 976 array($t_first_access,$i_last_access,$obj_id,$usr_id) 977 ); 978 return $res; 979 } 980} 981