ILIAS  release_5-4 Revision v5.4.26-12-gabc799a52e6
ilTrQuery Class Reference

Tracking query class. More...

+ Collaboration diagram for ilTrQuery:

Static Public Member Functions

static getObjectsStatusForUser ($a_user_id, array $obj_refs)
 
static getObjectivesStatusForUser ($a_user_id, $a_obj_id, array $a_objective_ids)
 
static getSCOsStatusForUser ($a_user_id, $a_parent_obj_id, array $a_sco_ids)
 
static getSubItemsStatusForUser ($a_user_id, $a_parent_obj_id, array $a_item_ids)
 Get subitems status. More...
 
static getUserDataForObject ( $a_ref_id, $a_order_field="", $a_order_dir="", $a_offset=0, $a_limit=9999, array $a_filters=null, array $a_additional_fields=null, $check_agreement=false, $privacy_fields=null)
 Get all user-based tracking data for object. More...
 
static getObjectsDataForUser ( $a_user_id, $a_parent_obj_id, $a_parent_ref_id, $a_order_field="", $a_order_dir="", $a_offset=0, $a_limit=9999, array $a_filters=null, array $a_additional_fields=null, $use_collection=true)
 Get all object-based tracking data for user and parent object. More...
 
static getSubItemType ($a_parent_obj_id)
 Get sub-item object type for parent. More...
 
static getObjectsSummaryForObject ( $a_parent_obj_id, $a_parent_ref_id, $a_order_field="", $a_order_dir="", $a_offset=0, $a_limit=9999, array $a_filters=null, array $a_additional_fields=null, $a_preselected_obj_ids=null)
 Get all aggregated tracking data for parent object. More...
 
static getParticipantsForObject ($a_ref_id)
 Get participant ids for given object. More...
 
static getObjectIds ($a_parent_obj_id, $a_parent_ref_id=false, $use_collection=true, $a_refresh_status=true, $a_user_ids=null)
 Get (sub)objects for given object, also handles learning objectives (course only) More...
 
static executeQueries (array $queries, $a_order_field="", $a_order_dir="", $a_offset=0, $a_limit=9999)
 Execute given queries, including count query. More...
 
static getUserObjectMatrix ( $a_parent_ref_id, $a_obj_ids, $a_user_filter=null, array $a_additional_fields=null, array $a_privacy_fields=null, $a_check_agreement=null)
 Get status matrix for users on objects. More...
 
static getUserObjectiveMatrix ($a_parent_obj_id, $a_users)
 
static getObjectAccessStatistics (array $a_ref_ids, $a_year, $a_month=null)
 
static getObjectTypeStatistics ()
 
static getWorkspaceBlogs ($a_title=null)
 
static getPortfolios ($a_title=null)
 
static getObjectDailyStatistics (array $a_ref_ids, $a_year, $a_month=null)
 
static getObjectStatisticsMonthlySummary ()
 
static deleteObjectStatistics (array $a_months)
 
static searchObjects ($a_type, $a_title=null, $a_root=null, $a_hidden=null, $a_preset_obj_ids=null)
 
static getObjectStatisticsLogInfo ()
 Get last update info for object statistics. More...
 
static getObjectLPStatistics (array $a_obj_ids, $a_year, $a_month=null, $a_group_by_day=false)
 
static getObjectTypeStatisticsPerMonth ($a_aggregation, $a_year=null)
 

Static Protected Member Functions

static getUDFAndHandlePrivacy (array &$a_result, array $a_udf=null, $a_check_agreement=null, array $a_privacy_fields=null, array $a_filters=null)
 Handle privacy and add udf data to (user) result data. More...
 
static getSessionData ($a_user_id, array $obj_ids)
 Get session data for given objects and user. More...
 
static getSummaryDataForObject ($a_ref_id, array $fields, array $a_filters=null)
 Get all aggregated tracking data for object. More...
 
static getSummaryPercentages ($field, $base_query, $alias=null)
 Get aggregated data for field. More...
 
static buildFilters (array $where, array $a_filters=null, $a_aggregate=false)
 Build sql from filter definition. More...
 
static buildColumns (array &$a_fields, array $a_additional_fields=null, $a_aggregate=false)
 Build sql from field definition. More...
 
static getSubTree ($a_parent_ref_id, array &$a_object_ids, array &$a_ref_ids)
 Get complete branch of tree (recursively) More...
 
static refreshObjectsStatus (array $a_obj_ids, $a_users=null)
 check whether status (for all relevant users) exists More...
 

Detailed Description

Tracking query class.

Put any complex queries into this class. Keep tracking class small.

Author
Jörg Lützenkirchen luetz.nosp@m.enki.nosp@m.rchen.nosp@m.@lei.nosp@m.fos.c.nosp@m.om
Version
$Id$

Definition at line 12 of file class.ilTrQuery.php.

Member Function Documentation

◆ buildColumns()

static ilTrQuery::buildColumns ( array &  $a_fields,
array  $a_additional_fields = null,
  $a_aggregate = false 
)
staticprotected

Build sql from field definition.

Parameters
array&$a_fields
array$a_additional_fields
bool$a_aggregate
Returns
array

Definition at line 1111 of file class.ilTrQuery.php.

1112 {
1113 if (sizeof($a_additional_fields)) {
1114 $udf = null;
1115 foreach ($a_additional_fields as $field) {
1116 if (substr($field, 0, 4) != "udf_") {
1117 $function = null;
1118 if ($a_aggregate) {
1119 $pos = strrpos($field, "_");
1120 if ($pos === false) {
1121 continue;
1122 }
1123 $function = strtoupper(substr($field, $pos + 1));
1124 $field = substr($field, 0, $pos);
1125 if (!in_array($function, array("MIN", "MAX", "SUM", "AVG", "COUNT"))) {
1126 continue;
1127 }
1128 }
1129
1130 switch ($field) {
1131 case 'org_units':
1132 break;
1133
1134 case "language":
1135 if ($function) {
1136 $a_fields[] = $function . "(value) " . $field . "_" . strtolower($function);
1137 } else {
1138 $a_fields[] = "value as " . $field;
1139 }
1140 break;
1141
1142 case "read_count":
1143 case "spent_seconds":
1144 if (!$function) {
1145 $a_fields[] = "(" . $field . "+childs_" . $field . ") " . $field;
1146 } else {
1147 if ($function == "AVG") {
1148 $a_fields[] = "ROUND(AVG(" . $field . "+childs_" . $field . "), 2) " . $field . "_" . strtolower($function);
1149 } else {
1150 $a_fields[] = $function . "(COALESCE(" . $field . ", 0) + COALESCE(childs_" . $field . ", 0)) " . $field . "_" . strtolower($function);
1151 }
1152 }
1153 break;
1154
1155 case "read_count_spent_seconds":
1156 if ($function == "AVG") {
1157 $a_fields[] = "ROUND(AVG((spent_seconds+childs_spent_seconds)/(read_count+childs_read_count)), 2) " . $field . "_" . strtolower($function);
1158 }
1159 break;
1160
1161 default:
1162 if ($function) {
1163 if ($function == "AVG") {
1164 $a_fields[] = "ROUND(AVG(" . $field . "), 2) " . $field . "_" . strtolower($function);
1165 } else {
1166 $a_fields[] = $function . "(" . $field . ") " . $field . "_" . strtolower($function);
1167 }
1168 } else {
1169 $a_fields[] = $field;
1170 }
1171 break;
1172 }
1173 } else {
1174 $udf[] = substr($field, 4);
1175 }
1176 }
1177
1178 // clean-up
1179 $a_fields = array_unique($a_fields);
1180 if (is_array($udf)) {
1181 $udf = array_unique($udf);
1182 }
1183
1184 return $udf;
1185 }
1186 }

Referenced by getObjectsDataForUser(), getObjectsSummaryForObject(), getUserDataForObject(), and getUserObjectMatrix().

+ Here is the caller graph for this function:

◆ buildFilters()

static ilTrQuery::buildFilters ( array  $where,
array  $a_filters = null,
  $a_aggregate = false 
)
staticprotected

Build sql from filter definition.

Parameters
array$where
array$a_filters
bool$a_aggregate
Returns
string

Definition at line 933 of file class.ilTrQuery.php.

934 {
935 global $DIC;
936
937 $ilDB = $DIC['ilDB'];
938
939 $having = array();
940
941 if (is_array($a_filters) && sizeof($a_filters) > 0) {
942 foreach ($a_filters as $id => $value) {
943 switch ($id) {
944 case "login":
945 case "firstname":
946 case "lastname":
947 case "institution":
948 case "department":
949 case "street":
950 case "email":
951 case "matriculation":
952 case "country":
953 case "city":
954 case "title":
955 $where[] = $ilDB->like("usr_data." . $id, "text", "%" . $value . "%");
956 break;
957
958 case "gender":
959 case "zipcode":
960 case "sel_country":
961 $where[] = "usr_data." . $id . " = " . $ilDB->quote($value, "text");
962 break;
963
964 case "u_comment":
965 $where[] = $ilDB->like("ut_lp_marks." . $id, "text", "%" . $value . "%");
966 break;
967
968 case "status":
970 // #10645 - not_attempted is default
971 $where[] = "(ut_lp_marks.status = " . $ilDB->quote(ilLPStatus::LP_STATUS_NOT_ATTEMPTED_NUM, "text") .
972 " OR ut_lp_marks.status IS NULL)";
973 break;
974 }
975 // fallthrough
976
977 // no break
978 case "mark":
979 $where[] = "ut_lp_marks." . $id . " = " . $ilDB->quote($value, "text");
980 break;
981
982 case "percentage":
983 if (!$a_aggregate) {
984 if ($value["from"]) {
985 $where[] = "ut_lp_marks." . $id . " >= " . $ilDB->quote($value["from"], "integer");
986 }
987 if ($value["to"]) {
988 $where[] = "(ut_lp_marks." . $id . " <= " . $ilDB->quote($value["to"], "integer") .
989 " OR ut_lp_marks." . $id . " IS NULL)";
990 }
991 } else {
992 if ($value["from"]) {
993 $having[] = "ROUND(AVG(ut_lp_marks." . $id . ")) >= " . $ilDB->quote($value["from"], "integer");
994 }
995 if ($value["to"]) {
996 $having[] = "ROUND(AVG(ut_lp_marks." . $id . ")) <= " . $ilDB->quote($value["to"], "integer");
997 }
998 }
999 break;
1000
1001 case "language":
1002 $where[] = "usr_pref.value = " . $ilDB->quote($value, "text");
1003 break;
1004
1005 // timestamp
1006 case "last_access":
1007 if ($value["from"]) {
1008 $value["from"] = substr($value["from"], 0, -2) . "00";
1009 $value["from"] = new ilDateTime($value["from"], IL_CAL_DATETIME);
1010 $value["from"] = $value["from"]->get(IL_CAL_UNIX);
1011 }
1012 if ($value["to"]) {
1013 if (strlen($value["to"]) == 19) {
1014 $value["to"] = substr($value["to"], 0, -2) . "59"; // #14858
1015 }
1016 $value["to"] = new ilDateTime($value["to"], IL_CAL_DATETIME);
1017 $value["to"] = $value["to"]->get(IL_CAL_UNIX);
1018 }
1019 // fallthrough
1020
1021 // no break
1022 case 'status_changed':
1023 // fallthrough
1024
1025 case "registration":
1026 if ($id == "registration") {
1027 $id = "create_date";
1028 }
1029 // fallthrough
1030
1031 // no break
1032 case "create_date":
1033 case "first_access":
1034 case "birthday":
1035 if ($value["from"]) {
1036 $where[] = $id . " >= " . $ilDB->quote($value["from"], "date");
1037 }
1038 if ($value["to"]) {
1039 if (strlen($value["to"]) == 19) {
1040 $value["to"] = substr($value["to"], 0, -2) . "59"; // #14858
1041 }
1042 $where[] = $id . " <= " . $ilDB->quote($value["to"], "date");
1043 }
1044 break;
1045
1046 case "read_count":
1047 if (!$a_aggregate) {
1048 if ($value["from"]) {
1049 $where[] = "(read_event." . $id . "+read_event.childs_" . $id . ") >= " . $ilDB->quote($value["from"], "integer");
1050 }
1051 if ($value["to"]) {
1052 $where[] = "((read_event." . $id . "+read_event.childs_" . $id . ") <= " . $ilDB->quote($value["to"], "integer") .
1053 " OR (read_event." . $id . "+read_event.childs_" . $id . ") IS NULL)";
1054 }
1055 } else {
1056 if ($value["from"]) {
1057 $having[] = "SUM(read_event." . $id . "+read_event.childs_" . $id . ") >= " . $ilDB->quote($value["from"], "integer");
1058 }
1059 if ($value["to"]) {
1060 $having[] = "SUM(read_event." . $id . "+read_event.childs_" . $id . ") <= " . $ilDB->quote($value["to"], "integer");
1061 }
1062 }
1063 break;
1064
1065 case "spent_seconds":
1066 if (!$a_aggregate) {
1067 if ($value["from"]) {
1068 $where[] = "(read_event." . $id . "+read_event.childs_" . $id . ") >= " . $ilDB->quote($value["from"], "integer");
1069 }
1070 if ($value["to"]) {
1071 $where[] = "((read_event." . $id . "+read_event.childs_" . $id . ") <= " . $ilDB->quote($value["to"], "integer") .
1072 " OR (read_event." . $id . "+read_event.childs_" . $id . ") IS NULL)";
1073 }
1074 } else {
1075 if ($value["from"]) {
1076 $having[] = "ROUND(AVG(read_event." . $id . "+read_event.childs_" . $id . ")) >= " . $ilDB->quote($value["from"], "integer");
1077 }
1078 if ($value["to"]) {
1079 $having[] = "ROUND(AVG(read_event." . $id . "+read_event.childs_" . $id . ")) <= " . $ilDB->quote($value["to"], "integer");
1080 }
1081 }
1082 break;
1083
1084 default:
1085 // var_dump("unknown: ".$id);
1086 break;
1087 }
1088 }
1089 }
1090
1091 $sql = "";
1092 if (sizeof($where)) {
1093 $sql .= " WHERE " . implode(" AND ", $where);
1094 }
1095 if (sizeof($having)) {
1096 // ugly "having" hack because of summary view
1097 $sql .= " [[--HAVING " . implode(" AND ", $having) . "HAVING--]]";
1098 }
1099
1100 return $sql;
1101 }
const IL_CAL_UNIX
const IL_CAL_DATETIME
@classDescription Date and time handling
const LP_STATUS_NOT_ATTEMPTED_NUM
if(!array_key_exists('StateId', $_REQUEST)) $id
global $DIC
Definition: saml.php:7
global $ilDB

References $DIC, $id, $ilDB, IL_CAL_DATETIME, IL_CAL_UNIX, and ilLPStatus\LP_STATUS_NOT_ATTEMPTED_NUM.

Referenced by getObjectsDataForUser(), getSummaryDataForObject(), getUserDataForObject(), and getUserObjectMatrix().

+ Here is the caller graph for this function:

◆ deleteObjectStatistics()

static ilTrQuery::deleteObjectStatistics ( array  $a_months)
static

Definition at line 1741 of file class.ilTrQuery.php.

1742 {
1743 global $DIC;
1744
1745 $ilDB = $DIC['ilDB'];
1746
1747 // no combined column, have to concat
1748 $date_compare = $ilDB->in($ilDB->concat(array(array("yyyy", ""),
1749 array($ilDB->quote("-", "text"), ""),
1750 array("mm", ""))), $a_months, "", "text");
1751 $sql = "DELETE FROM obj_stat" .
1752 " WHERE " . $date_compare;
1753 $ilDB->manipulate($sql);
1754
1755 // fulldate == YYYYMMDD
1756 $tables = array("obj_lp_stat", "obj_type_stat", "obj_user_stat");
1757 foreach ($a_months as $month) {
1758 $year = substr($month, 0, 4);
1759 $month = substr($month, 5);
1760 $from = $year . str_pad($month, 2, "0", STR_PAD_LEFT) . "01";
1761 $to = $year . str_pad($month, 2, "0", STR_PAD_LEFT) . "31";
1762
1763 foreach ($tables as $table) {
1764 $sql = "DELETE FROM " . $table .
1765 " WHERE fulldate >= " . $ilDB->quote($from, "integer") .
1766 " AND fulldate <= " . $ilDB->quote($to, "integer");
1767 $ilDB->manipulate($sql);
1768 }
1769 }
1770 }
if(empty($password)) $table
Definition: pwgen.php:24
$from

References $DIC, $from, $ilDB, and $table.

Referenced by ilLPObjectStatisticsGUI\deleteData().

+ Here is the caller graph for this function:

◆ executeQueries()

static ilTrQuery::executeQueries ( array  $queries,
  $a_order_field = "",
  $a_order_dir = "",
  $a_offset = 0,
  $a_limit = 9999 
)
static

Execute given queries, including count query.

Parameters
array$queriesfields, query, count
string$a_order_field
string$a_order_dir
int$a_offset
int$a_limit
Returns
array cnt, set

Definition at line 1326 of file class.ilTrQuery.php.

1327 {
1328 global $DIC;
1329
1330 $ilDB = $DIC['ilDB'];
1331
1332 $cnt = 0;
1333 $subqueries = array();
1334 foreach ($queries as $item) {
1335 // ugly "having" hack because of summary view
1336 $item = str_replace("[[--HAVING", "HAVING", $item);
1337 $item = str_replace("HAVING--]]", "", $item);
1338
1339 if (!isset($item["count"])) {
1340 $count_field = $item["fields"];
1341 $count_field = array_shift($count_field);
1342 } else {
1343 $count_field = $item["count"];
1344 }
1345 $count_query = "SELECT COUNT(" . $count_field . ") AS cnt" . $item["query"];
1346 $set = $ilDB->query($count_query);
1347 if ($rec = $ilDB->fetchAssoc($set)) {
1348 $cnt += $rec["cnt"];
1349 }
1350
1351 $subqueries[] = "SELECT " . implode(",", $item["fields"]) . $item["query"];
1352 }
1353
1354 // set query
1355 $result = array();
1356 if ($cnt > 0) {
1357 if (sizeof($subqueries) > 1) {
1358 $base = array_shift($subqueries);
1359 $query = $base . " UNION (" . implode(") UNION (", $subqueries) . ")";
1360 } else {
1361 $query = $subqueries[0];
1362 }
1363
1364 if ($a_order_dir != "asc" && $a_order_dir != "desc") {
1365 $a_order_dir = "asc";
1366 }
1367 if ($a_order_field) {
1368 $query .= " ORDER BY " . $a_order_field . " " . strtoupper($a_order_dir);
1369 }
1370
1371 $offset = (int) $a_offset;
1372 $limit = (int) $a_limit;
1373 $ilDB->setLimit($limit, $offset);
1374
1375 $set = $ilDB->query($query);
1376 while ($rec = $ilDB->fetchAssoc($set)) {
1377 $result[] = $rec;
1378 }
1379 }
1380
1381 return array("cnt" => $cnt, "set" => $result);
1382 }
$result
$base
Definition: index.php:4
$query

References $base, $DIC, $ilDB, $query, and $result.

Referenced by getObjectsDataForUser(), getSummaryDataForObject(), getUserDataForObject(), and getUserObjectMatrix().

+ Here is the caller graph for this function:

◆ getObjectAccessStatistics()

static ilTrQuery::getObjectAccessStatistics ( array  $a_ref_ids,
  $a_year,
  $a_month = null 
)
static

Definition at line 1544 of file class.ilTrQuery.php.

1545 {
1546 global $DIC;
1547
1548 $ilDB = $DIC['ilDB'];
1549
1550 $obj_ids = array_keys($a_ref_ids);
1551
1552 if ($a_month) {
1553 $column = "dd";
1554 } else {
1555 $column = "mm";
1556 }
1557
1558 $res = array();
1559 $sql = "SELECT obj_id," . $column . ",SUM(read_count) read_count,SUM(childs_read_count) childs_read_count," .
1560 "SUM(spent_seconds) spent_seconds,SUM(childs_spent_seconds) childs_spent_seconds" .
1561 " FROM obj_stat" .
1562 " WHERE " . $ilDB->in("obj_id", $obj_ids, "", "integer") .
1563 " AND yyyy = " . $ilDB->quote($a_year, "integer");
1564 if ($a_month) {
1565 $sql .= " AND mm = " . $ilDB->quote($a_month, "integer");
1566 }
1567 $sql .= " GROUP BY obj_id," . $column;
1568 $set = $ilDB->query($sql);
1569 while ($row = $ilDB->fetchAssoc($set)) {
1570 $row["read_count"] += $row["childs_read_count"];
1571 $row["spent_seconds"] += $row["childs_spent_seconds"];
1572 $res[$row["obj_id"]][$row[$column]]["read_count"] += $row["read_count"];
1573 $res[$row["obj_id"]][$row[$column]]["spent_seconds"] += $row["spent_seconds"];
1574 }
1575
1576
1577 // add user data
1578
1579 $sql = "SELECT obj_id," . $column . ",SUM(counter) counter" .
1580 " FROM obj_user_stat" .
1581 " WHERE " . $ilDB->in("obj_id", $obj_ids, "", "integer") .
1582 " AND yyyy = " . $ilDB->quote($a_year, "integer");
1583 if ($a_month) {
1584 $sql .= " AND mm = " . $ilDB->quote($a_month, "integer");
1585 }
1586 $sql .= " GROUP BY obj_id," . $column;
1587 $set = $ilDB->query($sql);
1588 while ($row = $ilDB->fetchAssoc($set)) {
1589 $res[$row["obj_id"]][$row[$column]]["users"] += $row["counter"];
1590 }
1591
1592 return $res;
1593 }
$row
foreach($_POST as $key=> $value) $res

References $DIC, $ilDB, $res, and $row.

Referenced by ilLPObjectStatisticsTableGUI\getItems().

+ Here is the caller graph for this function:

◆ getObjectDailyStatistics()

static ilTrQuery::getObjectDailyStatistics ( array  $a_ref_ids,
  $a_year,
  $a_month = null 
)
static

Definition at line 1695 of file class.ilTrQuery.php.

1696 {
1697 global $DIC;
1698
1699 $ilDB = $DIC['ilDB'];
1700
1701 $obj_ids = array_keys($a_ref_ids);
1702
1703 $res = array();
1704 $sql = "SELECT obj_id,hh,SUM(read_count) read_count,SUM(childs_read_count) childs_read_count," .
1705 "SUM(spent_seconds) spent_seconds,SUM(childs_spent_seconds) childs_spent_seconds" .
1706 " FROM obj_stat" .
1707 " WHERE " . $ilDB->in("obj_id", $obj_ids, "", "integer") .
1708 " AND yyyy = " . $ilDB->quote($a_year, "integer");
1709 if ($a_month) {
1710 $sql .= " AND mm = " . $ilDB->quote($a_month, "integer");
1711 }
1712 $sql .= " GROUP BY obj_id,hh";
1713 $set = $ilDB->query($sql);
1714 while ($row = $ilDB->fetchAssoc($set)) {
1715 $row["read_count"] += $row["childs_read_count"];
1716 $row["spent_seconds"] += $row["childs_spent_seconds"];
1717 $res[$row["obj_id"]][(int) $row["hh"]]["read_count"] += $row["read_count"];
1718 $res[$row["obj_id"]][(int) $row["hh"]]["spent_seconds"] += $row["spent_seconds"];
1719 }
1720 return $res;
1721 }

References $DIC, $ilDB, $res, and $row.

Referenced by ilLPObjectStatisticsDailyTableGUI\getItems().

+ Here is the caller graph for this function:

◆ getObjectIds()

static ilTrQuery::getObjectIds (   $a_parent_obj_id,
  $a_parent_ref_id = false,
  $use_collection = true,
  $a_refresh_status = true,
  $a_user_ids = null 
)
static

Get (sub)objects for given object, also handles learning objectives (course only)

Parameters
int$a_parent_obj_id
int$a_parent_ref_id
int$use_collection
bool$a_refresh_status
array$a_user_ids
Returns
array object_ids, objectives_parent_id

Definition at line 1198 of file class.ilTrQuery.php.

1199 {
1200 include_once "Services/Object/classes/class.ilObjectLP.php";
1201
1202 $object_ids = array($a_parent_obj_id);
1203 $ref_ids = array($a_parent_obj_id => $a_parent_ref_id);
1204 $objectives_parent_id = $scorm = $subitems = false;
1205
1206 $olp = ilObjectLP::getInstance($a_parent_obj_id);
1207 $mode = $olp->getCurrentMode();
1208 switch ($mode) {
1209 // what about LP_MODE_SCORM_PACKAGE ?
1211 include_once "Services/Tracking/classes/class.ilLPStatusFactory.php";
1212 $status_scorm = get_class(ilLPStatusFactory::_getInstance($a_parent_obj_id, ilLPObjSettings::LP_MODE_SCORM));
1213 $scorm = $status_scorm::_getStatusInfo($a_parent_obj_id);
1214 break;
1215
1217 if (ilObject::_lookupType($a_parent_obj_id) == "crs") {
1218 $objectives_parent_id = $a_parent_obj_id;
1219 }
1220 break;
1221
1225 include_once "Services/Tracking/classes/class.ilLPStatusFactory.php";
1226 $status_coll_tlt = get_class(ilLPStatusFactory::_getInstance($a_parent_obj_id, $mode));
1227 $subitems = $status_coll_tlt::_getStatusInfo($a_parent_obj_id);
1228 break;
1229
1230 default:
1231 // lp collection
1232 if ($use_collection) {
1233 $collection = $olp->getCollectionInstance();
1234 if ($collection) {
1235 foreach ($collection->getItems() as $child_ref_id) {
1236 $child_id = ilObject::_lookupObjId($child_ref_id);
1237 $object_ids[] = $child_id;
1238 $ref_ids[$child_id] = $child_ref_id;
1239 }
1240 }
1241 }
1242 // all objects in branch
1243 else {
1244 self::getSubTree($a_parent_ref_id, $object_ids, $ref_ids);
1245 $object_ids = array_unique($object_ids);
1246 }
1247
1248 foreach ($object_ids as $idx => $object_id) {
1249 if (!$object_id) {
1250 unset($object_ids[$idx]);
1251 }
1252 }
1253 break;
1254 }
1255
1256 if ($a_refresh_status) {
1257 self::refreshObjectsStatus($object_ids, $a_user_ids);
1258 }
1259
1260 return array("object_ids" => $object_ids,
1261 "ref_ids" => $ref_ids,
1262 "objectives_parent_id" => $objectives_parent_id,
1263 "scorm" => $scorm,
1264 "subitems" => $subitems);
1265 }
static _getInstance($a_obj_id, $a_mode=null)
static getInstance($a_obj_id)
static _lookupObjId($a_id)
static _lookupType($a_id, $a_reference=false)
lookup object type
static refreshObjectsStatus(array $a_obj_ids, $a_users=null)
check whether status (for all relevant users) exists
static getSubTree($a_parent_ref_id, array &$a_object_ids, array &$a_ref_ids)
Get complete branch of tree (recursively)

References ilLPStatusFactory\_getInstance(), ilObject\_lookupObjId(), ilObject\_lookupType(), ilObjectLP\getInstance(), getSubTree(), ilLPObjSettings\LP_MODE_COLLECTION_MANUAL, ilLPObjSettings\LP_MODE_COLLECTION_MOBS, ilLPObjSettings\LP_MODE_COLLECTION_TLT, ilLPObjSettings\LP_MODE_OBJECTIVES, ilLPObjSettings\LP_MODE_SCORM, and refreshObjectsStatus().

Referenced by ilTrMatrixTableGUI\getItems(), getObjectsDataForUser(), and getObjectsSummaryForObject().

+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ getObjectivesStatusForUser()

static ilTrQuery::getObjectivesStatusForUser (   $a_user_id,
  $a_obj_id,
array  $a_objective_ids 
)
static

Definition at line 85 of file class.ilTrQuery.php.

86 {
87 global $DIC;
88
89 $ilDB = $DIC['ilDB'];
90
91 include_once "Modules/Course/classes/Objectives/class.ilLOUserResults.php";
92 $lo_lp_status = ilLOUserResults::getObjectiveStatusForLP($a_user_id, $a_obj_id, $a_objective_ids);
93
94 $query = "SELECT crs_id, crs_objectives.objective_id AS obj_id, title," . $ilDB->quote("lobj", "text") . " AS type" .
95 " FROM crs_objectives" .
96 " WHERE " . $ilDB->in("crs_objectives.objective_id", $a_objective_ids, false, "integer") .
97 " AND active = " . $ilDB->quote(1, "integer") .
98 " ORDER BY position";
99 $set = $ilDB->query($query);
100 $result = array();
101 while ($rec = $ilDB->fetchAssoc($set)) {
102 if (array_key_exists($rec["obj_id"], $lo_lp_status)) {
103 $rec["status"] = $lo_lp_status[$rec["obj_id"]];
104 } else {
106 }
107 $result[] = $rec;
108 }
109
110 return $result;
111 }
static getObjectiveStatusForLP($a_user_id, $a_obj_id, array $a_objective_ids)

References $DIC, $ilDB, $query, $result, ilLOUserResults\getObjectiveStatusForLP(), and ilLPStatus\LP_STATUS_NOT_ATTEMPTED_NUM.

Referenced by ilLPProgressTableGUI\getItems().

+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ getObjectLPStatistics()

static ilTrQuery::getObjectLPStatistics ( array  $a_obj_ids,
  $a_year,
  $a_month = null,
  $a_group_by_day = false 
)
static

Definition at line 1855 of file class.ilTrQuery.php.

1856 {
1857 global $DIC;
1858
1859 $ilDB = $DIC['ilDB'];
1860
1861 if ($a_group_by_day) {
1862 $column = "dd";
1863 } else {
1864 $column = "mm,yyyy";
1865 }
1866
1867 $res = array();
1868 $sql = "SELECT obj_id," . $column . "," .
1869 "MIN(mem_cnt) mem_cnt_min,AVG(mem_cnt) mem_cnt_avg, MAX(mem_cnt) mem_cnt_max," .
1870 "MIN(in_progress) in_progress_min,AVG(in_progress) in_progress_avg,MAX(in_progress) in_progress_max," .
1871 "MIN(completed) completed_min,AVG(completed) completed_avg,MAX(completed) completed_max," .
1872 "MIN(failed) failed_min,AVG(failed) failed_avg,MAX(failed) failed_max," .
1873 "MIN(not_attempted) not_attempted_min,AVG(not_attempted) not_attempted_avg,MAX(not_attempted) not_attempted_max" .
1874 " FROM obj_lp_stat" .
1875 " WHERE " . $ilDB->in("obj_id", $a_obj_ids, "", "integer") .
1876 " AND yyyy = " . $ilDB->quote($a_year, "integer");
1877 if ($a_month) {
1878 $sql .= " AND mm = " . $ilDB->quote($a_month, "integer");
1879 }
1880 $sql .= " GROUP BY obj_id," . $column;
1881 $set = $ilDB->query($sql);
1882 while ($row = $ilDB->fetchAssoc($set)) {
1883 $res[] = $row;
1884 }
1885
1886 return $res;
1887 }

References $DIC, $ilDB, $res, and $row.

Referenced by ilLPObjectStatisticsLPTableGUI\getDetailItems(), and ilLPObjectStatisticsLPTableGUI\getItems().

+ Here is the caller graph for this function:

◆ getObjectsDataForUser()

static ilTrQuery::getObjectsDataForUser (   $a_user_id,
  $a_parent_obj_id,
  $a_parent_ref_id,
  $a_order_field = "",
  $a_order_dir = "",
  $a_offset = 0,
  $a_limit = 9999,
array  $a_filters = null,
array  $a_additional_fields = null,
  $use_collection = true 
)
static

Get all object-based tracking data for user and parent object.

Parameters
int$a_user_id
int$a_parent_obj_id
int$a_parent_ref_id
string$a_order_field
string$a_order_dir
int$a_offset
int$a_limit
array$a_filters
array$a_additional_fields
bool$use_collection
Returns
array cnt, set

Definition at line 420 of file class.ilTrQuery.php.

431 {
432 global $DIC;
433
434 $ilDB = $DIC['ilDB'];
435
436 $fields = array("object_data.obj_id", "title", "type");
437 self::buildColumns($fields, $a_additional_fields);
438
439 $objects = self::getObjectIds($a_parent_obj_id, $a_parent_ref_id, $use_collection, true, array($a_user_id));
440
441 $query = " FROM object_data LEFT JOIN read_event ON (object_data.obj_id = read_event.obj_id AND" .
442 " read_event.usr_id = " . $ilDB->quote($a_user_id, "integer") . ")" .
443 " LEFT JOIN ut_lp_marks ON (ut_lp_marks.usr_id = " . $ilDB->quote($a_user_id, "integer") . " AND" .
444 " ut_lp_marks.obj_id = object_data.obj_id)" .
445 " WHERE " . $ilDB->in("object_data.obj_id", $objects["object_ids"], false, "integer") .
446 self::buildFilters(array(), $a_filters);
447
448 $queries = array();
449 $queries[] = array("fields" => $fields, "query" => $query);
450
451 if (!in_array($a_order_field, $fields)) {
452 $a_order_field = "title";
453 }
454
455 $result = self::executeQueries($queries, $a_order_field, $a_order_dir, $a_offset, $a_limit);
456 if ($result["cnt"]) {
457 // session data
458 $sessions = self::getSessionData($a_user_id, $objects["object_ids"]);
459
460 foreach ($result["set"] as $idx => $item) {
461 if ($item["type"] == "sess") {
462 $session = $sessions[$item["obj_id"]];
463 $result["set"][$idx]["title"] = $session["title"];
464 $result["set"][$idx]["sort_title"] = $session["e_start"];
465 // $result["set"][$idx]["status"] = (int)$session["status"];
466 }
467
468 $result["set"][$idx]["ref_id"] = $objects["ref_ids"][$item["obj_id"]];
469 }
470
471 // scos data (:TODO: will not be part of offset/limit)
472 if ($objects["scorm"]) {
473 include_once("./Modules/ScormAicc/classes/class.ilObjSAHSLearningModule.php");
474 $subtype = ilObjSAHSLearningModule::_lookupSubType($a_parent_obj_id);
475 if ($subtype == "scorm2004") {
476 include_once("./Modules/Scorm2004/classes/class.ilObjSCORM2004LearningModule.php");
477 $sobj = new ilObjSCORM2004LearningModule($a_parent_ref_id, true);
478 $scos_tracking = $sobj->getTrackingDataAgg($a_user_id, true);
479 } else {
480 include_once("./Modules/ScormAicc/classes/class.ilObjSCORMLearningModule.php");
481 $sobj = new ilObjSCORMLearningModule($a_parent_ref_id, true);
482 $scos_tracking = array();
483 foreach ($sobj->getTrackingDataAgg($a_user_id) as $item) {
484 // format: hhhh:mm:ss ?!
485 if ($item["time"]) {
486 $time = explode(":", $item["time"]);
487 $item["time"] = $time[0] * 60 * 60 + $time[1] * 60 + $time[2];
488 }
489 $scos_tracking[$item["sco_id"]] = array("session_time" => $item["time"]);
490 }
491 }
492
493 foreach ($objects["scorm"]["scos"] as $sco) {
494 $row = array("title" => $objects["scorm"]["scos_title"][$sco],
495 "type" => "sco");
496
498 if (in_array($a_user_id, $objects["scorm"]["completed"][$sco])) {
500 } elseif (in_array($a_user_id, $objects["scorm"]["failed"][$sco])) {
502 } elseif (in_array($a_user_id, $objects["scorm"]["in_progress"][$sco])) {
504 }
505 $row["status"] = $status;
506
507 // add available tracking data
508 if (isset($scos_tracking[$sco])) {
509 if (isset($scos_tracking[$sco]["last_access"])) {
510 $date = new ilDateTime($scos_tracking[$sco]["last_access"], IL_CAL_DATETIME);
511 $row["last_access"] = $date->get(IL_CAL_UNIX);
512 }
513 $row["spent_seconds"] = $scos_tracking[$sco]["session_time"];
514 }
515
516 $result["set"][] = $row;
517 $result["cnt"]++;
518 }
519 }
520
521 // #15379 - objectives data
522 if ($objects["objectives_parent_id"]) {
523 include_once "Modules/Course/classes/class.ilCourseObjective.php";
524 include_once "Modules/Course/classes/Objectives/class.ilLOUserResults.php";
525 $objtv_ids = ilCourseObjective::_getObjectiveIds($objects["objectives_parent_id"], true);
526 foreach (self::getObjectivesStatusForUser($a_user_id, $objects["objectives_parent_id"], $objtv_ids) as $item) {
527 $result["set"][] = $item;
528 $result["cnt"]++;
529 }
530 }
531
532 // subitem data
533 if ($objects["subitems"]) {
534 $sub_type = self::getSubItemType($a_parent_obj_id);
535 foreach ($objects["subitems"]["items"] as $item_id) {
536 $row = array("title" => $objects["subitems"]["item_titles"][$item_id],
537 "type" => $sub_type);
538
540 if (in_array($a_user_id, $objects["subitems"]["completed"][$item_id])) {
542 }
543 $row["status"] = $status;
544
545 $result["set"][] = $row;
546 $result["cnt"]++;
547 }
548 }
549 }
550 return $result;
551 }
static _getObjectiveIds($course_id, $a_activated_only=false)
const LP_STATUS_COMPLETED_NUM
const LP_STATUS_IN_PROGRESS_NUM
const LP_STATUS_FAILED_NUM
static _lookupSubType($a_obj_id)
lookup subtype id (scorm, )
Class ilObjSCORM2004LearningModule.
Class ilObjSCORMLearningModule.
static getObjectIds($a_parent_obj_id, $a_parent_ref_id=false, $use_collection=true, $a_refresh_status=true, $a_user_ids=null)
Get (sub)objects for given object, also handles learning objectives (course only)
static getSessionData($a_user_id, array $obj_ids)
Get session data for given objects and user.
static buildFilters(array $where, array $a_filters=null, $a_aggregate=false)
Build sql from filter definition.
static buildColumns(array &$a_fields, array $a_additional_fields=null, $a_aggregate=false)
Build sql from field definition.
static getSubItemType($a_parent_obj_id)
Get sub-item object type for parent.
static executeQueries(array $queries, $a_order_field="", $a_order_dir="", $a_offset=0, $a_limit=9999)
Execute given queries, including count query.
$time
Definition: cron.php:21
$session

References $DIC, $ilDB, $query, $result, $row, $session, $time, ilCourseObjective\_getObjectiveIds(), ilObjSAHSLearningModule\_lookupSubType(), buildColumns(), buildFilters(), executeQueries(), getObjectIds(), getSessionData(), getSubItemType(), IL_CAL_DATETIME, IL_CAL_UNIX, ilLPStatus\LP_STATUS_COMPLETED_NUM, ilLPStatus\LP_STATUS_FAILED_NUM, ilLPStatus\LP_STATUS_IN_PROGRESS_NUM, and ilLPStatus\LP_STATUS_NOT_ATTEMPTED_NUM.

Referenced by ilTrUserObjectsPropsTableGUI\getItems().

+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ getObjectsStatusForUser()

static ilTrQuery::getObjectsStatusForUser (   $a_user_id,
array  $obj_refs 
)
static

Definition at line 14 of file class.ilTrQuery.php.

15 {
16 global $DIC;
17
18 $ilDB = $DIC['ilDB'];
19
20 if (sizeof($obj_refs)) {
21 $obj_ids = array_keys($obj_refs);
22 self::refreshObjectsStatus($obj_ids, array($a_user_id));
23
24 include_once "Services/Object/classes/class.ilObjectLP.php";
25 include_once "Services/Tracking/classes/class.ilLPStatus.php";
26
27 // prepare object view modes
28 include_once 'Modules/Course/classes/class.ilObjCourse.php';
29 $view_modes = array();
30 $query = "SELECT obj_id, view_mode FROM crs_settings" .
31 " WHERE " . $ilDB->in("obj_id", $obj_ids, false, "integer");
32 $set = $ilDB->query($query);
33 while ($rec = $ilDB->fetchAssoc($set)) {
34 $view_modes[(int) $rec["obj_id"]] = (int) $rec["view_mode"];
35 }
36
37 $sessions = self::getSessionData($a_user_id, $obj_ids);
38
39 $query = "SELECT object_data.obj_id, title, CASE WHEN status IS NULL THEN " . ilLPStatus::LP_STATUS_NOT_ATTEMPTED_NUM . " ELSE status END AS status," .
40 " status_changed, percentage, read_count+childs_read_count AS read_count, spent_seconds+childs_spent_seconds AS spent_seconds," .
41 " u_mode, type, visits, mark, u_comment" .
42 " FROM object_data" .
43 " LEFT JOIN ut_lp_settings ON (ut_lp_settings.obj_id = object_data.obj_id)" .
44 " LEFT JOIN read_event ON (read_event.obj_id = object_data.obj_id AND read_event.usr_id = " . $ilDB->quote($a_user_id, "integer") . ")" .
45 " LEFT JOIN ut_lp_marks ON (ut_lp_marks.obj_id = object_data.obj_id AND ut_lp_marks.usr_id = " . $ilDB->quote($a_user_id, "integer") . ")" .
46 // " WHERE (u_mode IS NULL OR u_mode <> ".$ilDB->quote(ilLPObjSettings::LP_MODE_DEACTIVATED, "integer").")".
47 " WHERE " . $ilDB->in("object_data.obj_id", $obj_ids, false, "integer") .
48 " ORDER BY title";
49 $set = $ilDB->query($query);
50 $result = array();
51 while ($rec = $ilDB->fetchAssoc($set)) {
52 $rec["comment"] = $rec["u_comment"];
53 unset($rec["u_comment"]);
54
55 $rec["ref_ids"] = $obj_refs[(int) $rec["obj_id"]];
56 $rec["status"] = (int) $rec["status"];
57 $rec["percentage"] = (int) $rec["percentage"];
58 $rec["read_count"] = (int) $rec["read_count"];
59 $rec["spent_seconds"] = (int) $rec["spent_seconds"];
60 $rec["u_mode"] = (int) $rec["u_mode"];
61
62 if ($rec["type"] == "sess") {
63 $session = $sessions[$rec["obj_id"]];
64 $rec["title"] = $session["title"];
65 // $rec["status"] = (int)$session["status"];
66 }
67
68 // lp mode might not match object/course view mode
69 if ($rec["type"] == "crs" && $view_modes[$rec["obj_id"]] == IL_CRS_VIEW_OBJECTIVE) {
71 } elseif (!$rec["u_mode"]) {
72 $olp = ilObjectLP::getInstance($rec["obj_id"]);
73 $rec["u_mode"] = $olp->getCurrentMode();
74 }
75
76 // can be default mode
77 if (/*$rec["u_mode"] != ilLPObjSettings::LP_MODE_DEACTIVATE*/ true) {
78 $result[] = $rec;
79 }
80 }
81 return $result;
82 }
83 }
const IL_CRS_VIEW_OBJECTIVE

References $DIC, $ilDB, $query, $result, $session, ilObjectLP\getInstance(), getSessionData(), IL_CRS_VIEW_OBJECTIVE, ilLPObjSettings\LP_MODE_OBJECTIVES, ilLPStatus\LP_STATUS_NOT_ATTEMPTED_NUM, and refreshObjectsStatus().

Referenced by ilPortfolioPageGUI\getCoursesOfUser(), and ilLPProgressTableGUI\getItems().

+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ getObjectsSummaryForObject()

static ilTrQuery::getObjectsSummaryForObject (   $a_parent_obj_id,
  $a_parent_ref_id,
  $a_order_field = "",
  $a_order_dir = "",
  $a_offset = 0,
  $a_limit = 9999,
array  $a_filters = null,
array  $a_additional_fields = null,
  $a_preselected_obj_ids = null 
)
static

Get all aggregated tracking data for parent object.

:TODO: sorting, offset, limit, objectives, collection/all

Parameters
int$a_parent_obj_id
int$a_parent_ref_id
string$a_order_field
string$a_order_dir
int$a_offset
int$a_limit
array$a_filter
array$a_additional_fields
array$a_preselected_obj_ids
Returns
array cnt, set

Definition at line 625 of file class.ilTrQuery.php.

635 {
636 global $DIC;
637
638 $ilDB = $DIC['ilDB'];
639
640 $fields = array();
641 self::buildColumns($fields, $a_additional_fields, true);
642
643 $objects = array();
644 if ($a_preselected_obj_ids === null) {
645 $objects = self::getObjectIds($a_parent_obj_id, $a_parent_ref_id, false, false);
646 } else {
647 foreach ($a_preselected_obj_ids as $obj_id => $ref_ids) {
648 $objects["object_ids"][] = $obj_id;
649 $objects["ref_ids"][$obj_id] = array_pop($ref_ids);
650 }
651 }
652
653 $result = array();
654 if ($objects) {
655 // object data
656 $set = $ilDB->query("SELECT obj_id,title,type FROM object_data" .
657 " WHERE " . $ilDB->in("obj_id", $objects["object_ids"], false, "integer"));
658 while ($rec = $ilDB->fetchAssoc($set)) {
659 $object_data[$rec["obj_id"]] = $rec;
660 if ($a_preselected_obj_ids) {
661 $object_data[$rec["obj_id"]]["ref_ids"] = $a_preselected_obj_ids[$rec["obj_id"]];
662 } else {
663 $object_data[$rec["obj_id"]]["ref_ids"] = array($objects["ref_ids"][$rec["obj_id"]]);
664 }
665 }
666
667 foreach ($objects["ref_ids"] as $object_id => $ref_id) {
668 $object_result = self::getSummaryDataForObject($ref_id, $fields, $a_filters);
669 if (sizeof($object_result)) {
670 if ($object_data[$object_id]) {
671 $result[] = array_merge($object_data[$object_id], $object_result);
672 }
673 }
674 }
675
676 // :TODO: objectives
677 if ($objects["objectives_parent_id"]) {
678 }
679 }
680
681 return array("cnt" => sizeof($result), "set" => $result);
682 }
static getSummaryDataForObject($a_ref_id, array $fields, array $a_filters=null)
Get all aggregated tracking data for object.

References $DIC, $ilDB, $result, buildColumns(), getObjectIds(), and getSummaryDataForObject().

Referenced by ilTrSummaryTableGUI\getItems().

+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ getObjectStatisticsLogInfo()

static ilTrQuery::getObjectStatisticsLogInfo ( )
static

Get last update info for object statistics.

Returns
array

Definition at line 1844 of file class.ilTrQuery.php.

1845 {
1846 global $DIC;
1847
1848 $ilDB = $DIC['ilDB'];
1849
1850 $set = $ilDB->query("SELECT COUNT(*) counter, MIN(tstamp) tstamp" .
1851 " FROM obj_stat_log");
1852 return $ilDB->fetchAssoc($set);
1853 }

References $DIC, and $ilDB.

Referenced by ilLPObjectStatisticsGUI\showAggregationInfo().

+ Here is the caller graph for this function:

◆ getObjectStatisticsMonthlySummary()

static ilTrQuery::getObjectStatisticsMonthlySummary ( )
static

Definition at line 1723 of file class.ilTrQuery.php.

1724 {
1725 global $DIC;
1726
1727 $ilDB = $DIC['ilDB'];
1728
1729 $set = $ilDB->query("SELECT COUNT(*) AS COUNTER,yyyy,mm" .
1730 " FROM obj_stat" .
1731 " GROUP BY yyyy, mm" .
1732 " ORDER BY yyyy DESC, mm DESC");
1733 $res = array();
1734 while ($row = $ilDB->fetchAssoc($set)) {
1735 $res[] = array("month" => $row["yyyy"] . "-" . $row["mm"],
1736 "count" => $row["counter"]);
1737 }
1738 return $res;
1739 }

References $DIC, $ilDB, $res, and $row.

Referenced by ilLPObjectStatisticsAdminTableGUI\getItems().

+ Here is the caller graph for this function:

◆ getObjectTypeStatistics()

static ilTrQuery::getObjectTypeStatistics ( )
static

Definition at line 1595 of file class.ilTrQuery.php.

1596 {
1597 global $DIC;
1598
1599 $ilDB = $DIC['ilDB'];
1600 $objDefinition = $DIC['objDefinition'];
1601
1602 // re-use add new item selection (folder is not that important)
1603 $types = array_keys($objDefinition->getCreatableSubObjects("root", ilObjectDefinition::MODE_REPOSITORY));
1604
1605 // repository
1606 include_once "Services/Tree/classes/class.ilTree.php";
1607 $tree = new ilTree(1);
1608 $sql = "SELECT " . $tree->table_obj_data . ".obj_id," . $tree->table_obj_data . ".type," .
1609 $tree->table_tree . "." . $tree->tree_pk . "," . $tree->table_obj_reference . ".ref_id" .
1610 " FROM " . $tree->table_tree .
1611 " " . $tree->buildJoin() .
1612 " WHERE " . $ilDB->in($tree->table_obj_data . ".type", $types, "", "text");
1613 $set = $ilDB->query($sql);
1614 $res = array();
1615 while ($row = $ilDB->fetchAssoc($set)) {
1616 $res[$row["type"]]["type"] = $row["type"];
1617 $res[$row["type"]]["references"]++;
1618 $res[$row["type"]]["objects"][] = $row["obj_id"];
1619 if ($row[$tree->tree_pk] < 0) {
1620 $res[$row["type"]]["deleted"]++;
1621 }
1622 }
1623
1624 foreach ($res as $type => $values) {
1625 $res[$type]["objects"] = sizeof(array_unique($values["objects"]));
1626 }
1627
1628 // portfolios (not part of repository)
1629 foreach (self::getPortfolios() as $obj_id) {
1630 $res["prtf"]["type"] = "prtf";
1631 $res["prtf"]["references"]++;
1632 $res["prtf"]["objects"]++;
1633 }
1634
1635 foreach (self::getWorkspaceBlogs() as $obj_id) {
1636 $res["blog"]["type"] = "blog";
1637 $res["blog"]["references"]++;
1638 $res["blog"]["objects"]++;
1639 }
1640
1641 return $res;
1642 }
Tree class data representation in hierachical trees using the Nested Set Model with Gaps by Joe Celco...
$type
$values

References $DIC, $ilDB, $res, $row, $tree, $type, $values, and ilObjectDefinition\MODE_REPOSITORY.

Referenced by ilLPCronObjectStatistics\gatherTypesData(), and ilLPObjectStatisticsTypesTableGUI\getItems().

+ Here is the caller graph for this function:

◆ getObjectTypeStatisticsPerMonth()

static ilTrQuery::getObjectTypeStatisticsPerMonth (   $a_aggregation,
  $a_year = null 
)
static

Definition at line 1889 of file class.ilTrQuery.php.

1890 {
1891 global $DIC;
1892
1893 $ilDB = $DIC['ilDB'];
1894
1895 if (!$a_year) {
1896 $a_year = date("Y");
1897 }
1898
1899 $agg = strtoupper($a_aggregation);
1900
1901 $res = array();
1902 $sql = "SELECT type,yyyy,mm," . $agg . "(cnt_objects) cnt_objects," . $agg . "(cnt_references) cnt_references," .
1903 "" . $agg . "(cnt_deleted) cnt_deleted FROM obj_type_stat" .
1904 " WHERE yyyy = " . $ilDB->quote($a_year, "integer") .
1905 " GROUP BY type,yyyy,mm";
1906 $set = $ilDB->query($sql);
1907 while ($row = $ilDB->fetchAssoc($set)) {
1908 $row["mm"] = str_pad($row["mm"], 2, "0", STR_PAD_LEFT);
1909 $res[$row["type"]][$row["yyyy"] . "-" . $row["mm"]] = array(
1910 "objects" => (int) $row["cnt_objects"],
1911 "references" => (int) $row["cnt_references"],
1912 "deleted" => (int) $row["cnt_deleted"]
1913 );
1914 }
1915
1916 return $res;
1917 }

References $DIC, $ilDB, $res, and $row.

Referenced by ilLPObjectStatisticsTypesTableGUI\getItems().

+ Here is the caller graph for this function:

◆ getParticipantsForObject()

static ilTrQuery::getParticipantsForObject (   $a_ref_id)
static

Get participant ids for given object.

Parameters
int$a_ref_id
Returns
mixed array or null if no users can bedetermined for object.

Definition at line 802 of file class.ilTrQuery.php.

803 {
804 global $DIC;
805
806 $tree = $DIC['tree'];
807
808 $obj_id = ilObject::_lookupObjectId($a_ref_id);
809 $obj_type = ilObject::_lookupType($obj_id);
810
811 $members = [];
812
813 // try to get participants from (parent) course/group
814 $members_read = false;
815 switch ($obj_type) {
816 case 'crs':
817 case 'grp':
818 $members_read = true;
819 $member_obj = ilParticipants::getInstance($a_ref_id);
820 $members = $member_obj->getMembers();
821 break;
822
823
824 /* Mantis 19296: Individual Assessment can be subtype of crs.
825 * But for LP view only his own members should be displayed.
826 * We need to return the members without checking the parent path. */
827 case "iass":
828 $members_read = true;
829 include_once("Modules/IndividualAssessment/classes/class.ilObjIndividualAssessment.php");
830 $iass = new ilObjIndividualAssessment($obj_id, false);
831 $members = $iass->loadMembers()->membersIds();
832 break;
833
834 default:
835 // walk path to find course or group object and use members of that object
836 $path = $tree->getPathId($a_ref_id);
837 array_pop($path);
838 foreach (array_reverse($path) as $path_ref_id) {
839 $type = ilObject::_lookupType($path_ref_id, true);
840 if ($type == "crs" || $type == "grp") {
841 $members_read = true;
842 $members = self::getParticipantsForObject($path_ref_id);
843 }
844 }
845 break;
846 }
847
848 // begin-patch ouf
849 if ($members_read) {
850 return $GLOBALS['DIC']->access()->filterUserIdsByRbacOrPositionOfCurrentUser(
851 'read_learning_progress',
852 'read_learning_progress',
853 $a_ref_id,
854 $members
855 );
856 }
857
858 $a_users = null;
859
860 // no participants possible: use tracking/object data where possible
861 switch ($obj_type) {
862 case "sahs":
863 include_once("./Modules/ScormAicc/classes/class.ilObjSAHSLearningModule.php");
864 $subtype = ilObjSAHSLearningModule::_lookupSubType($obj_id);
865 if ($subtype == "scorm2004") {
866 // based on cmi_node/cp_node, used for scorm tracking data views
867 include_once("./Modules/Scorm2004/classes/class.ilObjSCORM2004LearningModule.php");
868 $mod = new ilObjSCORM2004LearningModule($obj_id, false);
869 $all = $mod->getTrackedUsers("");
870 if ($all) {
871 $a_users = array();
872 foreach ($all as $item) {
873 $a_users[] = $item["user_id"];
874 }
875 }
876 } else {
877 include_once("./Modules/ScormAicc/classes/SCORM/class.ilObjSCORMTracking.php");
878 $a_users = ilObjSCORMTracking::_getTrackedUsers($obj_id);
879 }
880 break;
881
882 case "exc":
883 include_once("./Modules/Exercise/classes/class.ilExerciseMembers.php");
884 include_once("./Modules/Exercise/classes/class.ilObjExercise.php");
885 $exc = new ilObjExercise($obj_id, false);
886 $members = new ilExerciseMembers($exc);
887 $a_users = $members->getMembers();
888 break;
889
890 case "tst":
891 include_once "Services/Tracking/classes/class.ilLPStatusFactory.php";
893 $a_users = $class::getParticipants($obj_id);
894 break;
895
896 case "svy":
897 include_once "Services/Tracking/classes/class.ilLPStatusFactory.php";
899 $a_users = $class::getParticipants($obj_id);
900 break;
901
902 case "prg":
903 include_once("Modules/StudyProgramme/classes/class.ilObjStudyProgramme.php");
904 $prg = new ilObjStudyProgramme($obj_id, false);
905 $a_users = $prg->getIdsOfUsersWithRelevantProgress();
906 break;
907 default:
908 // keep null
909 break;
910 }
911
912 if (is_null($a_users)) {
913 return $a_users;
914 }
915
916 // begin-patch ouf
917 return $GLOBALS['DIC']->access()->filterUserIdsByRbacOrPositionOfCurrentUser(
918 'read_learning_progress',
919 'read_learning_progress',
920 $a_ref_id,
921 $a_users
922 );
923 }
$path
Definition: aliased.php:25
Class ilExerciseMembers.
static _getClassById($a_obj_id, $a_mode=null)
Class ilObjExercise.
For the purpose of streamlining the grading and learning-process status definition outside of tests,...
static _getTrackedUsers($a_obj_id)
Get all tracked users.
Class ilObjStudyProgramme.
static _lookupObjectId($a_ref_id)
lookup object id
static getInstance($a_ref_id)
Get instance by ref_id.
static getParticipantsForObject($a_ref_id)
Get participant ids for given object.
$GLOBALS['JPEG_Segment_Names']
Global Variable: XMP_tag_captions.

References $DIC, $GLOBALS, $path, $tree, $type, ilLPStatusFactory\_getClassById(), ilObjSCORMTracking\_getTrackedUsers(), ilObject\_lookupObjectId(), ilObjSAHSLearningModule\_lookupSubType(), ilObject\_lookupType(), ilParticipants\getInstance(), getParticipantsForObject(), ilLPObjSettings\LP_MODE_SURVEY_FINISHED, and ilLPObjSettings\LP_MODE_TEST_FINISHED.

Referenced by getParticipantsForObject(), getSummaryDataForObject(), getUserDataForObject(), getUserObjectMatrix(), ilSCORM2004TrackingItemsPerUserFilterGUI\parse(), ilSCORMTrackingItemsPerUserFilterGUI\parse(), ilObjSCORM2004LearningModuleGUI\showTrackingItems(), ilObjSCORMLearningModuleGUI\showTrackingItems(), ilObjSCORM2004LearningModuleGUI\showTrackingItemsBySco(), and ilObjSCORMLearningModuleGUI\showTrackingItemsBySco().

+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ getPortfolios()

static ilTrQuery::getPortfolios (   $a_title = null)
static

Definition at line 1671 of file class.ilTrQuery.php.

1672 {
1673 global $DIC;
1674
1675 $ilDB = $DIC['ilDB'];
1676
1677 $res = array();
1678
1679 $sql = "SELECT od.obj_id" .
1680 " FROM usr_portfolio prtf" .
1681 " JOIN object_data od ON (od.obj_id = prtf.id)";
1682
1683 if ($a_title) {
1684 $sql .= " WHERE " . $ilDB->like("od.title", "text", "%" . $a_title . "%");
1685 }
1686
1687 $set = $ilDB->query($sql);
1688 while ($row = $ilDB->fetchAssoc($set)) {
1689 $res[] = $row["obj_id"];
1690 }
1691
1692 return $res;
1693 }

References $DIC, $ilDB, $res, and $row.

Referenced by ilLPObjectStatisticsDailyTableGUI\getItems(), and ilLPObjectStatisticsTableGUI\getItems().

+ Here is the caller graph for this function:

◆ getSCOsStatusForUser()

static ilTrQuery::getSCOsStatusForUser (   $a_user_id,
  $a_parent_obj_id,
array  $a_sco_ids 
)
static

Definition at line 113 of file class.ilTrQuery.php.

114 {
115 self::refreshObjectsStatus(array($a_parent_obj_id), array($a_user_id));
116
117 // import score from tracking data
118 $scores_raw = $scores = array();
119 include_once './Modules/ScormAicc/classes/class.ilObjSAHSLearningModule.php';
120 $subtype = ilObjSAHSLearningModule::_lookupSubType($a_parent_obj_id);
121 switch ($subtype) {
122 case 'hacp':
123 case 'aicc':
124 case 'scorm':
125 include_once './Modules/ScormAicc/classes/class.ilObjSCORMLearningModule.php';
126 $module = new ilObjSCORMLearningModule($a_parent_obj_id, false);
127 $scores_raw = $module->getTrackingDataAgg($a_user_id);
128 break;
129
130 case 'scorm2004':
131 include_once './Modules/Scorm2004/classes/class.ilObjSCORM2004LearningModule.php';
132 $module = new ilObjSCORM2004LearningModule($a_parent_obj_id, false);
133 $scores_raw = $module->getTrackingDataAgg($a_user_id);
134 break;
135 }
136 if ($scores_raw) {
137 foreach ($scores_raw as $item) {
138 $scores[$item["sco_id"]] = $item["score"];
139 }
140 unset($module);
141 unset($scores_raw);
142 }
143
144 include_once 'Services/Tracking/classes/class.ilLPStatusWrapper.php';
145 $status_info = ilLPStatusWrapper::_getStatusInfo($a_parent_obj_id);
146
147 $items = array();
148 foreach ($a_sco_ids as $sco_id) {
149 // #9719 - can have in_progress AND failed/completed
150 if (in_array($a_user_id, $status_info["failed"][$sco_id])) {
152 } elseif (in_array($a_user_id, $status_info["completed"][$sco_id])) {
154 } elseif (in_array($a_user_id, $status_info["in_progress"][$sco_id])) {
156 } else {
158 }
159
160 $items[$sco_id] = array(
161 "title" => $status_info["scos_title"][$sco_id],
162 "status" => $status,
163 "type" => "sahs",
164 "score" => (int) $scores[$sco_id]
165 );
166 }
167
168 return $items;
169 }
static _getStatusInfo($a_obj_id)
Reads informations about the object e.g test results, tlt, number of visits.
const LP_STATUS_COMPLETED
const LP_STATUS_FAILED
const LP_STATUS_NOT_ATTEMPTED
const LP_STATUS_IN_PROGRESS
if($modEnd===false) $module
Definition: module.php:59

References $module, ilLPStatusWrapper\_getStatusInfo(), ilObjSAHSLearningModule\_lookupSubType(), ilLPStatus\LP_STATUS_COMPLETED, ilLPStatus\LP_STATUS_FAILED, ilLPStatus\LP_STATUS_IN_PROGRESS, ilLPStatus\LP_STATUS_NOT_ATTEMPTED, and refreshObjectsStatus().

Referenced by ilLPProgressTableGUI\getItems().

+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ getSessionData()

static ilTrQuery::getSessionData (   $a_user_id,
array  $obj_ids 
)
staticprotected

Get session data for given objects and user.

Parameters
int$a_user_id
array$obj_ids
Returns
array

Definition at line 576 of file class.ilTrQuery.php.

577 {
578 global $DIC;
579
580 $ilDB = $DIC['ilDB'];
581
582 $query = "SELECT obj_id, title, e_start, e_end, CASE WHEN participated = 1 THEN 2 WHEN registered = 1 THEN 1 ELSE NULL END AS status," .
583 " mark, e_comment" .
584 " FROM event" .
585 " JOIN event_appointment ON (event.obj_id = event_appointment.event_id)" .
586 " LEFT JOIN event_participants ON (event_participants.event_id = event.obj_id AND usr_id = " . $ilDB->quote($a_user_id, "integer") . ")" .
587 " WHERE " . $ilDB->in("obj_id", $obj_ids, false, "integer");
588 $set = $ilDB->query($query);
589 $sessions = array();
590 while ($rec = $ilDB->fetchAssoc($set)) {
591 $rec["comment"] = $rec["e_comment"];
592 unset($rec["e_comment"]);
593
595 new ilDateTime($rec["e_start"], IL_CAL_DATETIME, ilTimeZone::UTC),
596 new ilDateTime($rec["e_end"], IL_CAL_DATETIME, ilTimeZone::UTC)
597 );
598
599 if ($rec["title"]) {
600 $rec["title"] = $date . ': ' . $rec["title"];
601 } else {
602 $rec["title"] = $date;
603 }
604 $sessions[$rec["obj_id"]] = $rec;
605 }
606 return $sessions;
607 }
static formatPeriod(ilDateTime $start, ilDateTime $end, $a_skip_starting_day=false)
Format a period of two date Shows: 14.

References $DIC, $ilDB, $query, ilDatePresentation\formatPeriod(), IL_CAL_DATETIME, and ilTimeZone\UTC.

Referenced by getObjectsDataForUser(), and getObjectsStatusForUser().

+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ getSubItemsStatusForUser()

static ilTrQuery::getSubItemsStatusForUser (   $a_user_id,
  $a_parent_obj_id,
array  $a_item_ids 
)
static

Get subitems status.

Parameters
type$a_user_id
type$a_parent_obj_id
array$a_item_ids
Returns
type

Definition at line 178 of file class.ilTrQuery.php.

179 {
180 self::refreshObjectsStatus(array($a_parent_obj_id), array($a_user_id));
181
182 switch (ilObject::_lookupType($a_parent_obj_id)) {
183 case "lm":
184 case "mcst":
185 include_once './Services/Object/classes/class.ilObjectLP.php';
186 $olp = ilObjectLP::getInstance($a_parent_obj_id);
187 $collection = $olp->getCollectionInstance();
188 if ($collection) {
189 $ref_ids = ilObject::_getAllReferences($a_parent_obj_id);
190 $ref_id = end($ref_ids);
191 $item_data = $collection->getPossibleItems($ref_id);
192 }
193 break;
194
195 default:
196 return array();
197 }
198
199 include_once 'Services/Tracking/classes/class.ilLPStatusWrapper.php';
200 $status_info = ilLPStatusWrapper::_getStatusInfo($a_parent_obj_id);
201
202 $items = array();
203 foreach ($a_item_ids as $item_id) {
204 if (!isset($item_data[$item_id])) {
205 continue;
206 }
207
208 if (in_array($a_user_id, $status_info["completed"][$item_id])) {
210 } elseif (in_array($a_user_id, $status_info["in_progress"][$item_id])) {
212 } else {
214 }
215
216 $items[$item_id] = array(
217 "title" => $item_data[$item_id]["title"],
218 "status" => $status,
219 "type" => self::getSubItemType($a_parent_obj_id)
220 );
221 }
222
223 return $items;
224 }
static _getAllReferences($a_id)
get all reference ids of object

References ilObject\_getAllReferences(), ilLPStatusWrapper\_getStatusInfo(), ilObject\_lookupType(), ilObjectLP\getInstance(), ilLPStatus\LP_STATUS_COMPLETED, ilLPStatus\LP_STATUS_IN_PROGRESS, ilLPStatus\LP_STATUS_NOT_ATTEMPTED, and refreshObjectsStatus().

Referenced by ilLPProgressTableGUI\getItems().

+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ getSubItemType()

static ilTrQuery::getSubItemType (   $a_parent_obj_id)
static

Get sub-item object type for parent.

Parameters
int$a_parent_obj_id
Returns
string

Definition at line 558 of file class.ilTrQuery.php.

559 {
560 switch (ilObject::_lookupType($a_parent_obj_id)) {
561 case "lm":
562 return "st";
563
564 case "mcst":
565 return "mob";
566 }
567 }

References ilObject\_lookupType().

Referenced by getObjectsDataForUser(), and ilTrMatrixTableGUI\getSelectableColumns().

+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ getSubTree()

static ilTrQuery::getSubTree (   $a_parent_ref_id,
array &  $a_object_ids,
array &  $a_ref_ids 
)
staticprotected

Get complete branch of tree (recursively)

Parameters
int$a_parent_ref_id
array$a_object_ids
array$a_ref_ids

Definition at line 1274 of file class.ilTrQuery.php.

1275 {
1276 global $DIC;
1277
1278 $tree = $DIC['tree'];
1279
1280 $children = $tree->getChilds($a_parent_ref_id);
1281 if ($children) {
1282 foreach ($children as $child) {
1283 if ($child["type"] == "adm" || $child["type"] == "rolf") {
1284 continue;
1285 }
1286
1287 // as there can be deactivated items in the collection
1288 // we should allow them here too
1289
1290 $olp = ilObjectLP::getInstance($child["obj_id"]);
1291 $cmode = $olp->getCurrentMode();
1292
1293 /* see ilPluginLP
1294 if($cmode == ilLPObjSettings::LP_MODE_PLUGIN)
1295 {
1296 // #11368
1297 include_once "Services/Repository/classes/class.ilRepositoryObjectPluginSlot.php";
1298 if(ilRepositoryObjectPluginSlot::isTypePluginWithLP($child["type"], false))
1299 {
1300 $a_object_ids[] = $child["obj_id"];
1301 $a_ref_ids[$child["obj_id"]] = $child["ref_id"];
1302 }
1303 }
1304 */
1305
1306 if (/* $cmode != ilLPObjSettings::LP_MODE_DEACTIVATED && */ $cmode != ilLPObjSettings::LP_MODE_UNDEFINED) {
1307 $a_object_ids[] = $child["obj_id"];
1308 $a_ref_ids[$child["obj_id"]] = $child["ref_id"];
1309 }
1310
1311 self::getSubTree($child["ref_id"], $a_object_ids, $a_ref_ids);
1312 }
1313 }
1314 }

References $DIC, $tree, ilObjectLP\getInstance(), getSubTree(), and ilLPObjSettings\LP_MODE_UNDEFINED.

Referenced by getObjectIds(), and getSubTree().

+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ getSummaryDataForObject()

static ilTrQuery::getSummaryDataForObject (   $a_ref_id,
array  $fields,
array  $a_filters = null 
)
staticprotected

Get all aggregated tracking data for object.

Parameters
int$a_ref_id
array$fields
array$a_filters
Returns
array

Definition at line 692 of file class.ilTrQuery.php.

693 {
694 global $DIC;
695
696 $ilDB = $DIC['ilDB'];
697
698 $where = array();
699 $where[] = "usr_data.usr_id <> " . $ilDB->quote(ANONYMOUS_USER_ID, "integer");
700
701 // users
702 $a_users = self::getParticipantsForObject($a_ref_id);
703
704 $left = "";
705 if (is_array($a_users)) { // #14840
706 $left = "LEFT";
707 $where[] = $ilDB->in("usr_data.usr_id", $a_users, false, "integer");
708 }
709
710 $obj_id = ilObject::_lookupObjectId($a_ref_id);
711 self::refreshObjectsStatus(array($obj_id), $a_users);
712
713 $query = " FROM usr_data " . $left . " JOIN read_event ON (read_event.usr_id = usr_data.usr_id" .
714 " AND obj_id = " . $ilDB->quote($obj_id, "integer") . ")" .
715 " LEFT JOIN ut_lp_marks ON (ut_lp_marks.usr_id = usr_data.usr_id " .
716 " AND ut_lp_marks.obj_id = " . $ilDB->quote($obj_id, "integer") . ")" .
717 " LEFT JOIN usr_pref ON (usr_pref.usr_id = usr_data.usr_id AND keyword = " . $ilDB->quote("language", "text") . ")" .
718 self::buildFilters($where, $a_filters, true);
719
720 $fields[] = 'COUNT(usr_data.usr_id) AS user_count';
721
722 $queries = array();
723 $queries[] = array("fields" => $fields, "query" => $query, "count" => "*");
724
725 $result = self::executeQueries($queries);
726 $result = $result["set"][0];
727 $users_no = $result["user_count"];
728
729 $valid = true;
730 if (!$users_no) {
731 $valid = false;
732 } elseif (isset($a_filters["user_total"])) {
733 if ($a_filters["user_total"]["from"] && $users_no < $a_filters["user_total"]["from"]) {
734 $valid = false;
735 } elseif ($a_filters["user_total"]["to"] && $users_no > $a_filters["user_total"]["to"]) {
736 $valid = false;
737 }
738 }
739
740 if ($valid) {
741 $result["country"] = self::getSummaryPercentages("country", $query);
742 $result["sel_country"] = self::getSummaryPercentages("sel_country", $query);
743 $result["city"] = self::getSummaryPercentages("city", $query);
744 $result["gender"] = self::getSummaryPercentages("gender", $query);
745 $result["language"] = self::getSummaryPercentages("usr_pref.value", $query, "language");
746 $result["status"] = self::getSummaryPercentages("status", $query);
747 $result["mark"] = self::getSummaryPercentages("mark", $query);
748 } else {
749 $result = array();
750 }
751
752 if ($result) {
753 $result["user_total"] = $users_no;
754 }
755
756 return $result;
757 }
static getSummaryPercentages($field, $base_query, $alias=null)
Get aggregated data for field.
$valid

References $DIC, $ilDB, $query, $result, $valid, ilObject\_lookupObjectId(), buildFilters(), executeQueries(), getParticipantsForObject(), getSummaryPercentages(), and refreshObjectsStatus().

Referenced by getObjectsSummaryForObject().

+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ getSummaryPercentages()

static ilTrQuery::getSummaryPercentages (   $field,
  $base_query,
  $alias = null 
)
staticprotected

Get aggregated data for field.

Parameters
string$field
string$base_query
string$alias
Returns
array

Definition at line 767 of file class.ilTrQuery.php.

768 {
769 global $DIC;
770
771 $ilDB = $DIC['ilDB'];
772
773 if (!$alias) {
774 $field_alias = $field;
775 } else {
776 $field_alias = $alias;
777 $alias = " AS " . $alias;
778 }
779
780 // move having BEHIND group by
781 $having = "";
782 if (preg_match("/" . preg_quote(" [[--HAVING") . "(.+)" . preg_quote("HAVING--]]") . "/", $base_query, $hits)) {
783 $having = " HAVING " . $hits[1];
784 $base_query = str_replace($hits[0], "", $base_query);
785 }
786
787 $query = "SELECT COUNT(*) AS counter, " . $field . $alias . " " . $base_query . " GROUP BY " . $field . $having . " ORDER BY counter DESC";
788 $set = $ilDB->query($query);
789 $result = array();
790 while ($rec = $ilDB->fetchAssoc($set)) {
791 $result[$rec[$field_alias]] = (int) $rec["counter"];
792 }
793 return $result;
794 }

References $DIC, $ilDB, $query, and $result.

Referenced by getSummaryDataForObject().

+ Here is the caller graph for this function:

◆ getUDFAndHandlePrivacy()

static ilTrQuery::getUDFAndHandlePrivacy ( array &  $a_result,
array  $a_udf = null,
  $a_check_agreement = null,
array  $a_privacy_fields = null,
array  $a_filters = null 
)
staticprotected

Handle privacy and add udf data to (user) result data.

Parameters
array$a_result
array$a_udf
int$a_check_agreement
array$a_privacy_fields
array$a_filters

Definition at line 323 of file class.ilTrQuery.php.

329 {
330 global $DIC;
331
332 $ilDB = $DIC['ilDB'];
333
334 if (!$a_result["cnt"]) {
335 return;
336 }
337
338 if (is_array($a_udf) && count($a_udf) > 0) {
339 $query = "SELECT usr_id, field_id, value FROM udf_text WHERE " . $ilDB->in("field_id", $a_udf, false, "integer");
340 $set = $ilDB->query($query);
341 $udf = array();
342 while ($row = $ilDB->fetchAssoc($set)) {
343 $udf[$row["usr_id"]]["udf_" . $row["field_id"]] = $row["value"];
344 }
345 }
346
347 // (course/group) user agreement
348 if ($a_check_agreement) {
349 // admins/tutors (write-access) will never have agreement ?!
350 include_once "Services/Membership/classes/class.ilMemberAgreement.php";
351 $agreements = ilMemberAgreement::lookupAcceptedAgreements($a_check_agreement);
352
353 // public information for users
354 $query = "SELECT usr_id FROM usr_pref WHERE keyword = " . $ilDB->quote("public_profile", "text") .
355 " AND value = " . $ilDB->quote("y", "text") . " OR value = " . $ilDB->quote("g", "text");
356 $set = $ilDB->query($query);
357 $all_public = array();
358 while ($row = $ilDB->fetchAssoc($set)) {
359 $all_public[] = $row["usr_id"];
360 }
361 $query = "SELECT usr_id,keyword FROM usr_pref WHERE " . $ilDB->like("keyword", "text", "public_%", false) .
362 " AND value = " . $ilDB->quote("y", "text") . " AND " . $ilDB->in("usr_id", $all_public, "", "integer");
363 $set = $ilDB->query($query);
364 $public = array();
365 while ($row = $ilDB->fetchAssoc($set)) {
366 $public[$row["usr_id"]][] = substr($row["keyword"], 7);
367 }
368 unset($all_public);
369 }
370
371 foreach ($a_result["set"] as $idx => $row) {
372 // add udf data
373 if (isset($udf[$row["usr_id"]])) {
374 $a_result["set"][$idx] = $row = array_merge($row, $udf[$row["usr_id"]]);
375 }
376
377 // remove all private data - if active agreement and agreement not given by user
378 if (sizeof($a_privacy_fields) && $a_check_agreement && !in_array($row["usr_id"], $agreements)) {
379 foreach ($a_privacy_fields as $field) {
380 // check against public profile
381 if (isset($row[$field]) && (!isset($public[$row["usr_id"]]) ||
382 !in_array($field, $public[$row["usr_id"]]))) {
383 // remove complete entry - offending field was filtered
384 if (isset($a_filters[$field])) {
385 // we cannot remove row because of pagination!
386 foreach (array_keys($row) as $col_id) {
387 $a_result["set"][$idx][$col_id] = null;
388 }
389 $a_result["set"][$idx]["privacy_conflict"] = true;
390 // unset($a_result["set"][$idx]);
391 break;
392 }
393 // remove offending field
394 else {
395 $a_result["set"][$idx][$field] = false;
396 }
397 }
398 }
399 }
400 }
401
402 // $a_result["cnt"] = sizeof($a_result["set"]);
403 }
static lookupAcceptedAgreements($a_obj_id)
Lookup users who have accepted the agreement.

References $DIC, $ilDB, $query, $row, and ilMemberAgreement\lookupAcceptedAgreements().

Referenced by getUserDataForObject(), and getUserObjectMatrix().

+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ getUserDataForObject()

static ilTrQuery::getUserDataForObject (   $a_ref_id,
  $a_order_field = "",
  $a_order_dir = "",
  $a_offset = 0,
  $a_limit = 9999,
array  $a_filters = null,
array  $a_additional_fields = null,
  $check_agreement = false,
  $privacy_fields = null 
)
static

Get all user-based tracking data for object.

Parameters
int$a_ref_id
string$a_order_field
string$a_order_dir
int$a_offset
int$a_limit
array$a_filters
array$a_additional_fields
int$check_agreement(obj id of parent course)
arry$privacy_fields
Returns
array cnt, set

Definition at line 240 of file class.ilTrQuery.php.

250 {
251 global $DIC;
252
253 $ilDB = $DIC['ilDB'];
254
255 $fields = array("usr_data.usr_id", "login", "active");
256 $udf = self::buildColumns($fields, $a_additional_fields);
257
258 $where = array();
259 $where[] = "usr_data.usr_id <> " . $ilDB->quote(ANONYMOUS_USER_ID, "integer");
260
261 // users
262 $left = "";
263 $a_users = self::getParticipantsForObject($a_ref_id);
264
265 $obj_id = ilObject::_lookupObjectId($a_ref_id);
266 self::refreshObjectsStatus(array($obj_id), $a_users);
267
268 if (is_array($a_users)) {
269 $left = "LEFT";
270 $where[] = $ilDB->in("usr_data.usr_id", $a_users, false, "integer");
271 }
272
273 $query = " FROM usr_data " . $left . " JOIN read_event ON (read_event.usr_id = usr_data.usr_id" .
274 " AND read_event.obj_id = " . $ilDB->quote($obj_id, "integer") . ")" .
275 " LEFT JOIN ut_lp_marks ON (ut_lp_marks.usr_id = usr_data.usr_id " .
276 " AND ut_lp_marks.obj_id = " . $ilDB->quote($obj_id, "integer") . ")" .
277 " LEFT JOIN usr_pref ON (usr_pref.usr_id = usr_data.usr_id AND keyword = " . $ilDB->quote("language", "text") . ")" .
278 self::buildFilters($where, $a_filters);
279
280 $queries = array(array("fields" => $fields, "query" => $query));
281
282 // #9598 - if language is not in fields alias is missing
283 if ($a_order_field == "language") {
284 $a_order_field = "usr_pref.value";
285 }
286
287 // udf data is added later on, not in this query
288 $udf_order = null;
289 if (!$a_order_field) {
290 $a_order_field = "login";
291 } elseif (substr($a_order_field, 0, 4) == "udf_") {
292 $udf_order = $a_order_field;
293 $a_order_field = null;
294 }
295
296 $result = self::executeQueries($queries, $a_order_field, $a_order_dir, $a_offset, $a_limit);
297
298 self::getUDFAndHandlePrivacy($result, $udf, $check_agreement, $privacy_fields, $a_filters);
299
300 // as we cannot do this in the query, sort by custom field here
301 // this will not work with pagination!
302 if ($udf_order) {
303 include_once "Services/Utilities/classes/class.ilStr.php";
305 $result["set"],
306 $udf_order,
307 $a_order_dir
308 );
309 }
310
311 return $result;
312 }
static getUDFAndHandlePrivacy(array &$a_result, array $a_udf=null, $a_check_agreement=null, array $a_privacy_fields=null, array $a_filters=null)
Handle privacy and add udf data to (user) result data.
static stableSortArray($array, $a_array_sortby, $a_array_sortorder=0, $a_numeric=false)
Sort an aray using a stable sort algorithm, which preveserves the sequence of array elements which ha...

References $DIC, $ilDB, $query, $result, ilObject\_lookupObjectId(), buildColumns(), buildFilters(), executeQueries(), getParticipantsForObject(), getUDFAndHandlePrivacy(), refreshObjectsStatus(), and ilUtil\stableSortArray().

Referenced by ilTrObjectUsersPropsTableGUI\getItems().

+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ getUserObjectiveMatrix()

static ilTrQuery::getUserObjectiveMatrix (   $a_parent_obj_id,
  $a_users 
)
static

Definition at line 1490 of file class.ilTrQuery.php.

1491 {
1492 global $DIC;
1493
1494 $ilDB = $DIC['ilDB'];
1495
1496 if ($a_parent_obj_id && $a_users) {
1497 $res = array();
1498
1499 include_once "Services/Tracking/classes/class.ilLPStatus.php";
1500 include_once "Modules/Course/classes/Objectives/class.ilLOUserResults.php";
1501 include_once "Modules/Course/classes/class.ilCourseObjective.php";
1502 $objective_ids = ilCourseObjective::_getObjectiveIds($a_parent_obj_id, true);
1503
1504 // #17402 - are initital test(s) qualifying?
1505 include_once "Modules/Course/classes/Objectives/class.ilLOSettings.php";
1506 $lo_set = ilLOSettings::getInstanceByObjId($a_parent_obj_id);
1507 $initial_qualifying = $lo_set->isInitialTestQualifying();
1508
1509 // there may be missing entries for any user / objective combination
1510 foreach ($objective_ids as $objective_id) {
1511 foreach ($a_users as $user_id) {
1512 $res[$user_id][$objective_id] = ilLPStatus::LP_STATUS_NOT_ATTEMPTED_NUM;
1513 }
1514 }
1515
1516 $query = "SELECT * FROM loc_user_results" .
1517 " WHERE " . $ilDB->in("objective_id", $objective_ids, "", "integer") .
1518 " AND " . $ilDB->in("user_id", $a_users, "", "integer");
1519 if (!(bool) $initial_qualifying) {
1520 $query .= " AND type = " . $ilDB->quote(ilLOUserResults::TYPE_QUALIFIED, "integer");
1521 }
1522 $query .= " ORDER BY type"; // qualified must come last!
1523 $set = $ilDB->query($query);
1524 while ($row = $ilDB->fetchAssoc($set)) {
1525 $objective_id = $row["objective_id"];
1526 $user_id = $row["user_id"];
1527
1528 // if both initial and qualified, qualified will overwrite initial
1529
1530 // #15873 - see ilLOUserResults::getObjectiveStatusForLP()
1531 if ($row["status"] == ilLOUserResults::STATUS_COMPLETED) {
1532 $res[$user_id][$objective_id] = ilLPStatus::LP_STATUS_COMPLETED_NUM;
1533 } elseif ($row["status"] == ilLOUserResults::STATUS_FAILED) {
1534 $res[$user_id][$objective_id] = $row["is_final"]
1537 }
1538 }
1539
1540 return $res;
1541 }
1542 }
static getInstanceByObjId($a_obj_id)
get singleton instance

References $DIC, $ilDB, $query, $res, $row, ilCourseObjective\_getObjectiveIds(), ilLOSettings\getInstanceByObjId(), ilLPStatus\LP_STATUS_COMPLETED_NUM, ilLPStatus\LP_STATUS_FAILED_NUM, ilLPStatus\LP_STATUS_IN_PROGRESS_NUM, ilLPStatus\LP_STATUS_NOT_ATTEMPTED_NUM, ilLOUserResults\STATUS_COMPLETED, ilLOUserResults\STATUS_FAILED, and ilLOUserResults\TYPE_QUALIFIED.

Referenced by ilTrMatrixTableGUI\getItems().

+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ getUserObjectMatrix()

static ilTrQuery::getUserObjectMatrix (   $a_parent_ref_id,
  $a_obj_ids,
  $a_user_filter = null,
array  $a_additional_fields = null,
array  $a_privacy_fields = null,
  $a_check_agreement = null 
)
static

Get status matrix for users on objects.

Parameters
int$a_parent_ref_id
array$a_obj_ids
string$a_user_filter
array$a_additional_fields
array$a_privacy_fields
int$a_check_agreement
Returns
array cnt, set

Definition at line 1395 of file class.ilTrQuery.php.

1402 {
1403 global $DIC;
1404
1405 $ilDB = $DIC['ilDB'];
1406
1407 $result = array("cnt" => 0, "set" => null);
1408 if (sizeof($a_obj_ids)) {
1409 $where = array();
1410 $where[] = "usr_data.usr_id <> " . $ilDB->quote(ANONYMOUS_USER_ID, "integer");
1411 if ($a_user_filter) {
1412 $where[] = $ilDB->like("usr_data.login", "text", "%" . $a_user_filter . "%");
1413 }
1414
1415 // users
1416 $left = "";
1417 $a_users = self::getParticipantsForObject($a_parent_ref_id);
1418 if (is_array($a_users)) {
1419 $left = "LEFT";
1420 $where[] = $ilDB->in("usr_data.usr_id", $a_users, false, "integer");
1421 }
1422
1423 $parent_obj_id = ilObject::_lookupObjectId($a_parent_ref_id);
1424 self::refreshObjectsStatus($a_obj_ids, $a_users);
1425
1426 $fields = array("usr_data.usr_id", "login", "active");
1427 $udf = self::buildColumns($fields, $a_additional_fields);
1428
1429 include_once("./Services/Tracking/classes/class.ilLPStatus.php");
1430
1431 // #18673 - if parent supports percentage does not matter for "sub-items"
1432 $fields[] = "percentage";
1433
1434 $raw = array();
1435 foreach ($a_obj_ids as $obj_id) {
1436 // one request for each object
1437 $query = " FROM usr_data " . $left . " JOIN read_event ON (read_event.usr_id = usr_data.usr_id" .
1438 " AND read_event.obj_id = " . $ilDB->quote($obj_id, "integer") . ")" .
1439 " LEFT JOIN ut_lp_marks ON (ut_lp_marks.usr_id = usr_data.usr_id " .
1440 " AND ut_lp_marks.obj_id = " . $ilDB->quote($obj_id, "integer") . ")" .
1441 " LEFT JOIN usr_pref ON (usr_pref.usr_id = usr_data.usr_id AND keyword = " . $ilDB->quote("language", "text") . ")" .
1442 self::buildFilters($where);
1443
1444 $raw = self::executeQueries(array(array("fields" => $fields, "query" => $query)), "login");
1445 if ($raw["cnt"]) {
1446 // convert to final structure
1447 foreach ($raw["set"] as $row) {
1448 $result["set"][$row["usr_id"]]["login"] = $row["login"];
1449 $result["set"][$row["usr_id"]]["usr_id"] = $row["usr_id"];
1450
1451 // #14953
1452 $result["set"][$row["usr_id"]]["obj_" . $obj_id] = $row["status"];
1453 $result["set"][$row["usr_id"]]["obj_" . $obj_id . "_perc"] = $row["percentage"];
1454 if ($obj_id == $parent_obj_id) {
1455 $result["set"][$row["usr_id"]]["status_changed"] = $row["status_changed"];
1456 $result["set"][$row["usr_id"]]["last_access"] = $row["last_access"];
1457 $result["set"][$row["usr_id"]]["spent_seconds"] = $row["spent_seconds"];
1458 $result["set"][$row["usr_id"]]["read_count"] = $row["read_count"];
1459 }
1460
1461 foreach ($fields as $field) {
1462 // #14957 - value [as] language
1463 if (stristr($field, "language")) {
1464 $field = "language";
1465 }
1466
1467 if (isset($row[$field])) {
1468 // #14955
1469 if ($obj_id == $parent_obj_id ||
1470 !in_array($field, array("mark", "u_comment"))) {
1471 $result["set"][$row["usr_id"]][$field] = $row[$field];
1472 }
1473 }
1474 }
1475 }
1476 }
1477 }
1478
1479 $result["cnt"] = 0;
1480 if (is_array($result["set"])) {
1481 $result["cnt"] = count($result["set"]);
1482 }
1483 $result["users"] = $a_users;
1484
1485 self::getUDFAndHandlePrivacy($result, $udf, $a_check_agreement, $a_privacy_fields, $a_additional_fields);
1486 }
1487 return $result;
1488 }

References $DIC, $ilDB, $query, $result, $row, ilObject\_lookupObjectId(), buildColumns(), buildFilters(), executeQueries(), getParticipantsForObject(), getUDFAndHandlePrivacy(), and refreshObjectsStatus().

Referenced by ilTrMatrixTableGUI\getItems().

+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ getWorkspaceBlogs()

static ilTrQuery::getWorkspaceBlogs (   $a_title = null)
static

Definition at line 1644 of file class.ilTrQuery.php.

1645 {
1646 global $DIC;
1647
1648 $ilDB = $DIC['ilDB'];
1649
1650 $res = array();
1651
1652 // blogs in workspace?
1653 $sql = "SELECT od.obj_id,oref.wsp_id,od.type" .
1654 " FROM tree_workspace wst" .
1655 " JOIN object_reference_ws oref ON (oref.wsp_id = wst.child)" .
1656 " JOIN object_data od ON (oref.obj_id = od.obj_id)" .
1657 " WHERE od.type = " . $ilDB->quote("blog", "text");
1658
1659 if ($a_title) {
1660 $sql .= " AND " . $ilDB->like("od.title", "text", "%" . $a_title . "%");
1661 }
1662
1663 $set = $ilDB->query($sql);
1664 while ($row = $ilDB->fetchAssoc($set)) {
1665 $res[] = $row["obj_id"];
1666 }
1667
1668 return $res;
1669 }

References $DIC, $ilDB, $res, and $row.

Referenced by ilLPObjectStatisticsDailyTableGUI\getItems(), and ilLPObjectStatisticsTableGUI\getItems().

+ Here is the caller graph for this function:

◆ refreshObjectsStatus()

static ilTrQuery::refreshObjectsStatus ( array  $a_obj_ids,
  $a_users = null 
)
staticprotected

check whether status (for all relevant users) exists

Parameters
array$a_obj_ids
array$a_users

Definition at line 1831 of file class.ilTrQuery.php.

1832 {
1833 include_once("./Services/Tracking/classes/class.ilLPStatus.php");
1834 foreach ($a_obj_ids as $obj_id) {
1835 ilLPStatus::checkStatusForObject($obj_id, $a_users);
1836 }
1837 }
static checkStatusForObject($a_obj_id, $a_users=false)
This function checks whether the status for a given number of users is dirty and must be recalculated...

References ilLPStatus\checkStatusForObject().

Referenced by getObjectIds(), getObjectsStatusForUser(), getSCOsStatusForUser(), getSubItemsStatusForUser(), getSummaryDataForObject(), getUserDataForObject(), and getUserObjectMatrix().

+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ searchObjects()

static ilTrQuery::searchObjects (   $a_type,
  $a_title = null,
  $a_root = null,
  $a_hidden = null,
  $a_preset_obj_ids = null 
)
static

Definition at line 1772 of file class.ilTrQuery.php.

1773 {
1774 global $DIC;
1775
1776 $ilDB = $DIC['ilDB'];
1777 $tree = $DIC['tree'];
1778
1779 if ($a_type == "lres") {
1780 $a_type = array('lm','sahs','htlm');
1781 }
1782
1783 $sql = "SELECT r.ref_id,r.obj_id" .
1784 " FROM object_data o" .
1785 " JOIN object_reference r ON (o.obj_id = r.obj_id)" .
1786 " JOIN tree t ON (t.child = r.ref_id)" .
1787 " WHERE t.tree = " . $ilDB->quote(1, "integer");
1788
1789 if (!is_array($a_type)) {
1790 $sql .= " AND o.type = " . $ilDB->quote($a_type, "text");
1791 } else {
1792 $sql .= " AND " . $ilDB->in("o.type", $a_type, "", "text");
1793 }
1794
1795 if ($a_title) {
1796 $sql .= " AND (" . $ilDB->like("o.title", "text", "%" . $a_title . "%") .
1797 " OR " . $ilDB->like("o.description", "text", "%" . $a_title . "%") . ")";
1798 }
1799
1800 if (is_array($a_hidden)) {
1801 $sql .= " AND " . $ilDB->in("o.obj_id", $a_hidden, true, "integer");
1802 }
1803
1804 if (is_array($a_preset_obj_ids)) {
1805 $sql .= " AND " . $ilDB->in("o.obj_id", $a_preset_obj_ids, false, "integer");
1806 }
1807
1808 $set = $ilDB->query($sql);
1809 $res = array();
1810 while ($row = $ilDB->fetchAssoc($set)) {
1811 if ($a_root && $a_root != ROOT_FOLDER_ID) {
1812 foreach (ilObject::_getAllReferences($row['obj_id']) as $ref_id) {
1813 if ($tree->isGrandChild($a_root, $ref_id)) {
1814 $res[$row["obj_id"]][] = $row["ref_id"];
1815 continue;
1816 }
1817 }
1818 } else {
1819 $res[$row["obj_id"]][] = $row["ref_id"];
1820 }
1821 }
1822 return $res;
1823 }
$a_type
Definition: workflow.php:92

References $a_type, $DIC, $ilDB, $res, $row, $tree, and ilObject\_getAllReferences().

+ Here is the call graph for this function:

The documentation for this class was generated from the following file: