ILIAS  release_4-3 Revision
 All Data Structures Namespaces Files Functions Variables Groups Pages
Public Member Functions | Static Public Member Functions | Static Protected Member Functions
ilTrQuery Class Reference

Tracking query class. More...

+ Collaboration diagram for ilTrQuery:

Public Member Functions

 getObjectsStatusForUser ($a_user_id, array $obj_refs)
 getObjectivesStatusForUser ($a_user_id, array $a_objective_ids)
 getSCOsStatusForUser ($a_user_id, $a_parent_obj_id, array $a_sco_ids)
 getObjectTypeStatistics ()
 getObjectTypeStatisticsPerMonth ($a_aggregation, $a_year=null)

Static Public Member Functions

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.
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.
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.
static getParticipantsForObject ($a_ref_id)
 Get participant ids for given object.
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 executeQueries (array $queries, $a_order_field="", $a_order_dir="", $a_offset=0, $a_limit=9999)
 Execute given queries, including count query.
static getUserObjectMatrix ($a_parent_ref_id, $a_obj_ids, $a_user_filter=NULL)
 Get status matrix for users on objects.
static getUserObjectiveMatrix ($a_parent_obj_id, $a_users)
static getObjectAccessStatistics (array $a_ref_ids, $a_year, $a_month=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.
static getObjectLPStatistics (array $a_obj_ids, $a_year, $a_month=null, $a_group_by_day=false)

Static Protected Member Functions

static getSessionData ($a_user_id, array $obj_ids)
 Get session data for given objects and user.
static getSummaryDataForObject ($a_ref_id, array $fields, array $a_filters=NULL)
 Get all aggregated tracking data for object.
static getSummaryPercentages ($field, $base_query, $alias=NULL)
 Get aggregated data for field.
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 getSubTree ($a_parent_ref_id, array &$a_object_ids, array &$a_ref_ids)
 Get complete branch of tree (recursively)
static refreshObjectsStatus (array $a_obj_ids, $a_users=null)
 check whether status (for all relevant users) exists

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

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 1027 of file class.ilTrQuery.php.

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

{
if(sizeof($a_additional_fields))
{
$udf = NULL;
foreach($a_additional_fields as $field)
{
if(substr($field, 0, 4) != "udf_")
{
$function = NULL;
if($a_aggregate)
{
$pos = strrpos($field, "_");
if($pos === false)
{
continue;
}
$function = strtoupper(substr($field, $pos+1));
$field = substr($field, 0, $pos);
if(!in_array($function, array("MIN", "MAX", "SUM", "AVG", "COUNT")))
{
continue;
}
}
switch($field)
{
case "language":
if($function)
{
$a_fields[] = $function."(value) AS ".$field."_".strtolower($function);
}
else
{
$a_fields[] = "value AS ".$field;
}
break;
case "read_count":
case "spent_seconds":
if(!$function)
{
$a_fields[] = "(".$field."+childs_".$field.") AS ".$field;
}
else
{
if($function == "AVG")
{
$a_fields[] = "ROUND(AVG(".$field."+childs_".$field."), 2) AS ".$field."_".strtolower($function);
}
else
{
$a_fields[] = $function."(".$field."+childs_".$field.") AS ".$field."_".strtolower($function);
}
}
break;
case "read_count_spent_seconds":
if($function == "AVG")
{
$a_fields[] = "ROUND(AVG((spent_seconds+childs_spent_seconds)/(read_count+childs_read_count)), 2) AS ".$field."_".strtolower($function);
}
break;
default:
if($function)
{
if($function == "AVG")
{
$a_fields[] = "ROUND(AVG(".$field."), 2) AS ".$field."_".strtolower($function);
}
else
{
$a_fields[] = $function."(".$field.") AS ".$field."_".strtolower($function);
}
}
else
{
$a_fields[] = $field;
}
break;
}
}
else
{
$udf[] = substr($field, 4);
}
}
// clean-up
$a_fields = array_unique($a_fields);
if(is_array($udf))
{
$udf = array_unique($udf);
}
return $udf;
}
}

+ Here is the caller graph for this function:

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 832 of file class.ilTrQuery.php.

References IL_CAL_DATETIME, IL_CAL_UNIX, and LP_STATUS_NOT_ATTEMPTED_NUM.

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

{
global $ilDB;
$having = array();
if(sizeof($a_filters))
{
foreach($a_filters as $id => $value)
{
switch($id)
{
case "login":
case "firstname":
case "lastname":
case "institution":
case "department":
case "street":
case "email":
case "matriculation":
case "country":
case "city":
case "title":
$where[] = $ilDB->like("usr_data.".$id, "text", "%".$value."%");
break;
case "gender":
case "zipcode":
case "sel_country":
$where[] = "usr_data.".$id." = ".$ilDB->quote($value ,"text");
break;
case "u_comment":
$where[] = $ilDB->like("ut_lp_marks.".$id, "text", "%".$value."%");
break;
case "status":
if($value == LP_STATUS_NOT_ATTEMPTED_NUM)
{
// #10645 - not_attempted is default
$where[] = "(ut_lp_marks.status = ".$ilDB->quote(LP_STATUS_NOT_ATTEMPTED_NUM ,"text").
" OR ut_lp_marks.status IS NULL)";
break;
}
// fallthrough
case "mark":
$where[] = "ut_lp_marks.".$id." = ".$ilDB->quote($value ,"text");
break;
case "percentage":
if(!$a_aggregate)
{
if($value["from"])
{
$where[] = "ut_lp_marks.".$id." >= ".$ilDB->quote($value["from"] ,"integer");
}
if($value["to"])
{
$where[] = "ut_lp_marks.".$id." <= ".$ilDB->quote($value["to"] ,"integer");
}
}
else
{
if($value["from"])
{
$having[] = "ROUND(AVG(ut_lp_marks.".$id.")) >= ".$ilDB->quote($value["from"] ,"integer");
}
if($value["to"])
{
$having[] = "ROUND(AVG(ut_lp_marks.".$id.")) <= ".$ilDB->quote($value["to"] ,"integer");
}
}
break;
case "language":
$where[] = "usr_pref.value = ".$ilDB->quote($value ,"text");
break;
// timestamp
case "last_access":
if($value["from"])
{
$value["from"] = new ilDateTime($value["from"], IL_CAL_DATETIME);
$value["from"] = $value["from"]->get(IL_CAL_UNIX);
}
if($value["to"])
{
$value["to"] = new ilDateTime($value["to"], IL_CAL_DATETIME);
$value["to"] = $value["to"]->get(IL_CAL_UNIX);
}
// fallthrough
case 'status_changed':
// fallthrough
case "registration":
if($id == "registration")
{
$id = "create_date";
}
// fallthrough
case "create_date":
case "first_access":
case "birthday":
if($value["from"])
{
$where[] = $id." >= ".$ilDB->quote($value["from"] ,"date");
}
if($value["to"])
{
$where[] = $id." <= ".$ilDB->quote($value["to"] ,"date");
}
break;
case "read_count":
if(!$a_aggregate)
{
if($value["from"])
{
$where[] = "(read_event.".$id."+read_event.childs_".$id.") >= ".$ilDB->quote($value["from"] ,"integer");
}
if($value["to"])
{
$where[] = "(read_event.".$id."+read_event.childs_".$id.") <= ".$ilDB->quote($value["to"] ,"integer");
}
}
else
{
if($value["from"])
{
$having[] = "SUM(read_event.".$id."+read_event.childs_".$id.") >= ".$ilDB->quote($value["from"] ,"integer");
}
if($value["to"])
{
$having[] = "SUM(read_event.".$id."+read_event.childs_".$id.") <= ".$ilDB->quote($value["to"] ,"integer");
}
}
break;
case "spent_seconds":
if(!$a_aggregate)
{
if($value["from"])
{
$where[] = "(read_event.".$id."+read_event.childs_".$id.") >= ".$ilDB->quote($value["from"] ,"integer");
}
if($value["to"])
{
$where[] = "(read_event.".$id."+read_event.childs_".$id.") <= ".$ilDB->quote($value["to"] ,"integer");
}
}
else
{
if($value["from"])
{
$having[] = "ROUND(AVG(read_event.".$id."+read_event.childs_".$id.")) >= ".$ilDB->quote($value["from"] ,"integer");
}
if($value["to"])
{
$having[] = "ROUND(AVG(read_event.".$id."+read_event.childs_".$id.")) <= ".$ilDB->quote($value["to"] ,"integer");
}
}
break;
default:
// var_dump("unknown: ".$id);
break;
}
}
}
$sql = "";
if(sizeof($where))
{
$sql .= " WHERE ".implode(" AND ", $where);
}
if(sizeof($having))
{
// ugly "having" hack because of summary view
$sql .= " [[--HAVING ".implode(" AND ", $having)."HAVING--]]";
}
return $sql;
}

+ Here is the caller graph for this function:

static ilTrQuery::deleteObjectStatistics ( array  $a_months)
static

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

Referenced by ilLPObjectStatisticsGUI\deleteData().

{
global $ilDB;
// no combined column, have to concat
$date_compare = $ilDB->in($ilDB->concat(array(array("yyyy", ""),
array($ilDB->quote("-", "text"), ""),
array("mm", ""))), $a_months, "", "text");
$sql = "DELETE FROM obj_stat".
" WHERE ".$date_compare;
$ilDB->manipulate($sql);
// fulldate == YYYYMMDD
$tables = array("obj_lp_stat", "obj_type_stat", "obj_user_stat");
foreach($a_months as $month)
{
$year = substr($month, 0, 4);
$month = substr($month, 5);
$from = $year.str_pad($month, 2, "0", STR_PAD_LEFT)."01";
$to = $year.str_pad($month, 2, "0", STR_PAD_LEFT)."31";
foreach($tables as $table)
{
$sql = "DELETE FROM ".$table.
" WHERE fulldate >= ".$ilDB->quote($from, "integer").
" AND fulldate <= ".$ilDB->quote($to, "integer");
$ilDB->manipulate($sql);
}
}
}

+ Here is the caller graph for this function:

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 1258 of file class.ilTrQuery.php.

References $query, and $result.

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

{
global $ilDB;
$cnt = 0;
$subqueries = array();
foreach($queries as $item)
{
// ugly "having" hack because of summary view
$item = str_replace("[[--HAVING", "HAVING", $item);
$item = str_replace("HAVING--]]", "", $item);
if(!isset($item["count"]))
{
$count_field = $item["fields"];
$count_field = array_shift($count_field);
}
else
{
$count_field = $item["count"];
}
$count_query = "SELECT COUNT(".$count_field.") AS cnt".$item["query"];
$set = $ilDB->query($count_query);
if ($rec = $ilDB->fetchAssoc($set))
{
$cnt += $rec["cnt"];
}
$subqueries[] = "SELECT ".implode(",", $item["fields"]).$item["query"];
}
// set query
$result = array();
if($cnt > 0)
{
if(sizeof($subqueries) > 1)
{
$base = array_shift($subqueries);
$query = $base." UNION (".implode(") UNION (", $subqueries).")";
}
else
{
$query = $subqueries[0];
}
if ($a_order_dir != "asc" && $a_order_dir != "desc")
{
$a_order_dir = "asc";
}
if($a_order_field)
{
$query.= " ORDER BY ".$a_order_field." ".strtoupper($a_order_dir);
}
$offset = (int) $a_offset;
$limit = (int) $a_limit;
$ilDB->setLimit($limit, $offset);
$set = $ilDB->query($query);
while($rec = $ilDB->fetchAssoc($set))
{
$result[] = $rec;
}
}
return array("cnt" => $cnt, "set" => $result);
}

+ Here is the caller graph for this function:

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

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

References $res, and $row.

Referenced by ilLPObjectStatisticsTableGUI\getItems().

{
global $ilDB;
$obj_ids = array_keys($a_ref_ids);
if($a_month)
{
$column = "dd";
}
else
{
$column = "mm";
}
$res = array();
$sql = "SELECT obj_id,".$column.",SUM(read_count) read_count,SUM(childs_read_count) childs_read_count,".
"SUM(spent_seconds) spent_seconds,SUM(childs_spent_seconds) childs_spent_seconds".
" FROM obj_stat".
" WHERE ".$ilDB->in("obj_id", $obj_ids, "", "integer").
" AND yyyy = ".$ilDB->quote($a_year, "integer");
if($a_month)
{
$sql .= " AND mm = ".$ilDB->quote($a_month, "integer");
}
$sql .= " GROUP BY obj_id,".$column;
$set = $ilDB->query($sql);
while($row = $ilDB->fetchAssoc($set))
{
$row["read_count"] += $row["childs_read_count"];
$row["spent_seconds"] += $row["childs_spent_seconds"];
$res[$row["obj_id"]][$row[$column]]["read_count"] += $row["read_count"];
$res[$row["obj_id"]][$row[$column]]["spent_seconds"] += $row["spent_seconds"];
}
// add user data
$sql = "SELECT obj_id,".$column.",SUM(counter) counter".
" FROM obj_user_stat".
" WHERE ".$ilDB->in("obj_id", $obj_ids, "", "integer").
" AND yyyy = ".$ilDB->quote($a_year, "integer");
if($a_month)
{
$sql .= " AND mm = ".$ilDB->quote($a_month, "integer");
}
$sql .= " GROUP BY obj_id,".$column;
$set = $ilDB->query($sql);
while($row = $ilDB->fetchAssoc($set))
{
$res[$row["obj_id"]][$row[$column]]["users"] += $row["counter"];
}
return $res;
}

+ Here is the caller graph for this function:

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

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

References $res, and $row.

Referenced by ilLPObjectStatisticsDailyTableGUI\getItems().

{
global $ilDB;
$obj_ids = array_keys($a_ref_ids);
$res = array();
$sql = "SELECT obj_id,hh,SUM(read_count) read_count,SUM(childs_read_count) childs_read_count,".
"SUM(spent_seconds) spent_seconds,SUM(childs_spent_seconds) childs_spent_seconds".
" FROM obj_stat".
" WHERE ".$ilDB->in("obj_id", $obj_ids, "", "integer").
" AND yyyy = ".$ilDB->quote($a_year, "integer");
if($a_month)
{
$sql .= " AND mm = ".$ilDB->quote($a_month, "integer");
}
$sql .= " GROUP BY obj_id,hh";
$set = $ilDB->query($sql);
while($row = $ilDB->fetchAssoc($set))
{
$row["read_count"] += $row["childs_read_count"];
$row["spent_seconds"] += $row["childs_spent_seconds"];
$res[$row["obj_id"]][(int)$row["hh"]]["read_count"] += $row["read_count"];
$res[$row["obj_id"]][(int)$row["hh"]]["spent_seconds"] += $row["spent_seconds"];
}
return $res;
}

+ Here is the caller graph for this function:

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 1137 of file class.ilTrQuery.php.

References ilLPCollectionCache\_getItems(), ilLPObjSettings\_lookupMode(), ilObject\_lookupObjId(), ilObject\_lookupType(), getSubTree(), LP_MODE_OBJECTIVES, LP_MODE_SCORM, and refreshObjectsStatus().

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

{
include_once "Services/Tracking/classes/class.ilLPObjSettings.php";
$object_ids = array($a_parent_obj_id);
$ref_ids = array($a_parent_obj_id => $a_parent_ref_id);
$objectives_parent_id = $scorm = false;
$mode = ilLPObjSettings::_lookupMode($a_parent_obj_id);
switch($mode)
{
// what about LP_MODE_SCORM_PACKAGE ?
case LP_MODE_SCORM:
include_once "Services/Tracking/classes/class.ilLPStatusSCORM.php";
$status_scorm = new ilLPStatusSCORM($a_parent_obj_id);
$scorm = $status_scorm->_getStatusInfo($a_parent_obj_id);
break;
case LP_MODE_OBJECTIVES:
if(ilObject::_lookupType($a_parent_obj_id) == "crs")
{
$objectives_parent_id = $a_parent_obj_id;
}
break;
default:
// lp collection
if($use_collection)
{
include_once 'Services/Tracking/classes/class.ilLPCollectionCache.php';
foreach(ilLPCollectionCache::_getItems($a_parent_obj_id) as $child_ref_id)
{
$child_id = ilObject::_lookupObjId($child_ref_id);
$object_ids[] = $child_id;
$ref_ids[$child_id] = $child_ref_id;
}
}
// all objects in branch
else
{
self::getSubTree($a_parent_ref_id, $object_ids, $ref_ids);
$object_ids = array_unique($object_ids);
}
foreach($object_ids as $idx => $object_id)
{
if(!$object_id)
{
unset($object_ids[$idx]);
}
}
break;
}
if($a_refresh_status)
{
self::refreshObjectsStatus($object_ids, $a_user_ids);
}
return array("object_ids" => $object_ids,
"ref_ids" => $ref_ids,
"objectives_parent_id" => $objectives_parent_id,
"scorm" => $scorm);
}

+ Here is the call graph for this function:

+ Here is the caller graph for this function:

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

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

References $query, $result, and LP_STATUS_COMPLETED_NUM.

Referenced by ilLPProgressTableGUI\getItems().

{
global $ilDB;
$query = "SELECT crs_id, crs_objectives.objective_id AS obj_id, title, status, ".$ilDB->quote("lobj", "text")." AS type".
" FROM crs_objectives".
" LEFT JOIN crs_objective_status ON (crs_objectives.objective_id = crs_objective_status.objective_id AND user_id = ".$a_user_id.")".
" WHERE ".$ilDB->in("crs_objectives.objective_id", $a_objective_ids, false, "integer").
" ORDER BY position";
$set = $ilDB->query($query);
$result = array();
while($rec = $ilDB->fetchAssoc($set))
{
if($rec["status"])
{
$rec["status"] = LP_STATUS_COMPLETED_NUM;
}
$result[] = $rec;
}
return $result;
}

+ Here is the caller graph for this function:

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

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

References $res, and $row.

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

{
global $ilDB;
if($a_group_by_day)
{
$column = "dd";
}
else
{
$column = "mm,yyyy";
}
$res = array();
$sql = "SELECT obj_id,".$column.",".
"MIN(mem_cnt) mem_cnt_min,AVG(mem_cnt) mem_cnt_avg, MAX(mem_cnt) mem_cnt_max,".
"MIN(in_progress) in_progress_min,AVG(in_progress) in_progress_avg,MAX(in_progress) in_progress_max,".
"MIN(completed) completed_min,AVG(completed) completed_avg,MAX(completed) completed_max,".
"MIN(failed) failed_min,AVG(failed) failed_avg,MAX(failed) failed_max,".
"MIN(not_attempted) not_attempted_min,AVG(not_attempted) not_attempted_avg,MAX(not_attempted) not_attempted_max".
" FROM obj_lp_stat".
" WHERE ".$ilDB->in("obj_id", $a_obj_ids, "", "integer").
" AND yyyy = ".$ilDB->quote($a_year, "integer");
if($a_month)
{
$sql .= " AND mm = ".$ilDB->quote($a_month, "integer");
}
$sql .= " GROUP BY obj_id,".$column;
$set = $ilDB->query($sql);
while($row = $ilDB->fetchAssoc($set))
{
$res[] = $row;
}
return $res;
}

+ Here is the caller graph for this function:

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 349 of file class.ilTrQuery.php.

References $query, $result, $row, ilObjSAHSLearningModule\_lookupSubType(), buildColumns(), buildFilters(), executeQueries(), getObjectIds(), getSessionData(), IL_CAL_DATETIME, IL_CAL_UNIX, LP_STATUS_COMPLETED_NUM, LP_STATUS_FAILED_NUM, LP_STATUS_IN_PROGRESS_NUM, and LP_STATUS_NOT_ATTEMPTED_NUM.

Referenced by ilTrUserObjectsPropsTableGUI\getItems().

{
global $ilDB;
$fields = array("object_data.obj_id", "title", "type");
self::buildColumns($fields, $a_additional_fields);
$objects = self::getObjectIds($a_parent_obj_id, $a_parent_ref_id, $use_collection, true, array($a_user_id));
$query = " FROM object_data LEFT JOIN read_event ON (object_data.obj_id = read_event.obj_id AND".
" read_event.usr_id = ".$ilDB->quote($a_user_id, "integer").")".
" LEFT JOIN ut_lp_marks ON (ut_lp_marks.usr_id = ".$ilDB->quote($a_user_id, "integer")." AND".
" ut_lp_marks.obj_id = object_data.obj_id)".
" WHERE ".$ilDB->in("object_data.obj_id", $objects["object_ids"], false, "integer").
self::buildFilters(array(), $a_filters);
$queries = array();
$queries[] = array("fields"=>$fields, "query"=>$query);
// objectives data
if($objects["objectives_parent_id"])
{
$objective_fields = array("crs_objectives.objective_id AS obj_id", "title",
$ilDB->quote("lobj", "text")." as type");
if (is_array($a_additional_fields))
{
foreach($a_additional_fields as $field)
{
if($field != "status")
{
$objective_fields[] = "NULL AS ".$field;
}
else
{
include_once("Services/Tracking/classes/class.ilLPStatus.php");
$objective_fields[] = "CASE WHEN status IS NOT NULL THEN ".LP_STATUS_COMPLETED_NUM." ELSE NULL END AS status";
}
}
}
$where = array();
$where[] = "crs_objectives.crs_id = ".$ilDB->quote($objects["objectives_parent_id"], "integer");
$objectives_query = " FROM crs_objectives".
" LEFT JOIN crs_objective_status ON (crs_objectives.objective_id = crs_objective_status.objective_id".
" AND crs_objective_status.user_id = ".$ilDB->quote($a_user_id, "integer").")".
self::buildFilters($where, $a_filters);
$queries[] = array("fields"=>$objective_fields, "query"=>$objectives_query, "count"=>"crs_objectives.objective_id");
}
if(!in_array($a_order_field, $fields))
{
$a_order_field = "title";
}
$result = self::executeQueries($queries, $a_order_field, $a_order_dir, $a_offset, $a_limit);
if($result["cnt"])
{
// session data
$sessions = self::getSessionData($a_user_id, $objects["object_ids"]);
foreach($result["set"] as $idx => $item)
{
if($item["type"] == "sess")
{
$session = $sessions[$item["obj_id"]];
$result["set"][$idx]["title"] = $session["title"];
$result["set"][$idx]["sort_title"] = $session["e_start"];
// $result["set"][$idx]["status"] = (int)$session["status"];
}
$result["set"][$idx]["ref_id"] = $objects["ref_ids"][$item["obj_id"]];
}
// scos data (:TODO: will not be part of offset/limit)
if($objects["scorm"])
{
include_once("./Modules/ScormAicc/classes/class.ilObjSAHSLearningModule.php");
$subtype = ilObjSAHSLearningModule::_lookupSubType($a_parent_obj_id);
if($subtype == "scorm2004")
{
include_once("./Modules/Scorm2004/classes/class.ilObjSCORM2004LearningModule.php");
$sobj = new ilObjSCORM2004LearningModule($a_parent_ref_id, true);
$scos_tracking = $sobj->getTrackingDataAgg($a_user_id, true);
}
else
{
include_once("./Modules/ScormAicc/classes/class.ilObjSCORMLearningModule.php");
$sobj = new ilObjSCORMLearningModule($a_parent_ref_id, true);
$scos_tracking = array();
foreach($sobj->getTrackingDataAgg($a_user_id) as $item)
{
// format: hhhh:mm:ss ?!
if($item["time"])
{
$time = explode(":", $item["time"]);
$item["time"] = $time[0]*60*60+$time[1]*60+$time[2];
}
$scos_tracking[$item["sco_id"]] = array("session_time"=>$item["time"]);
}
}
foreach($objects["scorm"]["scos"] as $sco)
{
$row = array("title" => $objects["scorm"]["scos_title"][$sco],
"type" => "sco");
$status = LP_STATUS_NOT_ATTEMPTED_NUM;
if(in_array($a_user_id, $objects["scorm"]["completed"][$sco]))
{
$status = LP_STATUS_COMPLETED_NUM;
}
else if(in_array($a_user_id, $objects["scorm"]["failed"][$sco]))
{
$status = LP_STATUS_FAILED_NUM;
}
else if(in_array($a_user_id, $objects["scorm"]["in_progress"][$sco]))
{
$status = LP_STATUS_IN_PROGRESS_NUM;
}
$row["status"] = $status;
// add available tracking data
if(isset($scos_tracking[$sco]))
{
if(isset($scos_tracking[$sco]["last_access"]))
{
$date = new ilDateTime($scos_tracking[$sco]["last_access"], IL_CAL_DATETIME);
$row["last_access"] = $date->get(IL_CAL_UNIX);
}
$row["spent_seconds"] = $scos_tracking[$sco]["session_time"];
}
$result["set"][] = $row;
$result["cnt"]++;
}
}
}
return $result;
}

+ Here is the call graph for this function:

+ Here is the caller graph for this function:

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

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

References $query, $result, ilLPObjSettings\__getDefaultMode(), getSessionData(), IL_CRS_VIEW_OBJECTIVE, LP_MODE_OBJECTIVES, and refreshObjectsStatus().

Referenced by ilLPProgressTableGUI\getItems().

{
global $ilDB;
if(sizeof($obj_refs))
{
$obj_ids = array_keys($obj_refs);
self::refreshObjectsStatus($obj_ids, array($a_user_id));
include_once "Services/Tracking/classes/class.ilLPObjSettings.php";
include_once "Services/Tracking/classes/class.ilLPStatus.php";
// prepare object view modes
include_once 'Modules/Course/classes/class.ilObjCourse.php';
$view_modes = array();
$query = "SELECT obj_id, view_mode FROM crs_settings".
" WHERE ".$ilDB->in("obj_id", $obj_ids , false, "integer");
$set = $ilDB->query($query);
while($rec = $ilDB->fetchAssoc($set))
{
$view_modes[(int)$rec["obj_id"]] = (int)$rec["view_mode"];
}
$sessions = self::getSessionData($a_user_id, $obj_ids);
$query = "SELECT object_data.obj_id, title, CASE WHEN status IS NULL THEN ".LP_STATUS_NOT_ATTEMPTED_NUM." ELSE status END AS status,".
" status_changed, percentage, read_count+childs_read_count AS read_count, spent_seconds+childs_spent_seconds AS spent_seconds,".
" u_mode, type, visits, mark, u_comment".
" FROM object_data".
" LEFT JOIN ut_lp_settings ON (ut_lp_settings.obj_id = object_data.obj_id)".
" LEFT JOIN read_event ON (read_event.obj_id = object_data.obj_id AND read_event.usr_id = ".$ilDB->quote($a_user_id, "integer").")".
" 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").")".
// " WHERE (u_mode IS NULL OR u_mode <> ".$ilDB->quote(LP_MODE_DEACTIVATED, "integer").")".
" WHERE ".$ilDB->in("object_data.obj_id", $obj_ids, false, "integer").
" ORDER BY title";
$set = $ilDB->query($query);
$result = array();
while($rec = $ilDB->fetchAssoc($set))
{
$rec["comment"] = $rec["u_comment"];
unset($rec["u_comment"]);
$rec["ref_ids"] = $obj_refs[(int)$rec["obj_id"]];
$rec["status"] = (int)$rec["status"];
$rec["percentage"] = (int)$rec["percentage"];
$rec["read_count"] = (int)$rec["read_count"];
$rec["spent_seconds"] = (int)$rec["spent_seconds"];
$rec["u_mode"] = (int)$rec["u_mode"];
if($rec["type"] == "sess")
{
$session = $sessions[$rec["obj_id"]];
$rec["title"] = $session["title"];
// $rec["status"] = (int)$session["status"];
}
// lp mode might not match object/course view mode
if($rec["type"] == "crs" && $view_modes[$rec["obj_id"]] == IL_CRS_VIEW_OBJECTIVE)
{
$rec["u_mode"] = LP_MODE_OBJECTIVES;
}
else if(!$rec["u_mode"])
{
$rec["u_mode"] = ilLPObjSettings::__getDefaultMode($rec["obj_id"], $rec["type"]);
}
// can be default mode
if(/*$rec["u_mode"] != LP_MODE_DEACTIVATE*/ true)
{
$result[] = $rec;
}
}
return $result;
}
}

+ Here is the call graph for this function:

+ Here is the caller graph for this function:

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 550 of file class.ilTrQuery.php.

References $ref_id, $result, buildColumns(), getObjectIds(), and getSummaryDataForObject().

Referenced by ilTrSummaryTableGUI\getItems().

{
global $ilDB;
$fields = array();
self::buildColumns($fields, $a_additional_fields, true);
$objects = array();
if($a_preselected_obj_ids === NULL)
{
$objects = self::getObjectIds($a_parent_obj_id, $a_parent_ref_id, false, false);
}
else
{
foreach($a_preselected_obj_ids as $obj_id => $ref_ids)
{
$objects["object_ids"][] = $obj_id;
$objects["ref_ids"][$obj_id] = array_pop($ref_ids);
}
}
$result = array();
if($objects)
{
// object data
$set = $ilDB->query("SELECT obj_id,title,type FROM object_data".
" WHERE ".$ilDB->in("obj_id", $objects["object_ids"], false, "integer"));
while($rec = $ilDB->fetchAssoc($set))
{
$object_data[$rec["obj_id"]] = $rec;
if($a_preselected_obj_ids)
{
$object_data[$rec["obj_id"]]["ref_ids"] = $a_preselected_obj_ids[$rec["obj_id"]];
}
}
foreach($objects["ref_ids"] as $object_id => $ref_id)
{
$object_result = self::getSummaryDataForObject($ref_id, $fields, $a_filters);
if(sizeof($object_result))
{
if($object_data[$object_id])
{
$result[] = array_merge($object_data[$object_id], $object_result);
}
}
}
// :TODO: objectives
if($objects["objectives_parent_id"])
{
}
}
return array("cnt"=>sizeof($result), "set"=>$result);
}

+ Here is the call graph for this function:

+ Here is the caller graph for this function:

static ilTrQuery::getObjectStatisticsLogInfo ( )
static

Get last update info for object statistics.

Returns
array

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

Referenced by ilLPObjectStatisticsGUI\showAggregationInfo().

{
global $ilDB;
$set = $ilDB->query("SELECT COUNT(*) counter, MIN(tstamp) tstamp".
" FROM obj_stat_log");
return $ilDB->fetchAssoc($set);
}

+ Here is the caller graph for this function:

static ilTrQuery::getObjectStatisticsMonthlySummary ( )
static

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

References $res, and $row.

Referenced by ilLPObjectStatisticsAdminTableGUI\getItems().

{
global $ilDB;
$set = $ilDB->query("SELECT COUNT(*) AS COUNTER,yyyy,mm".
" FROM obj_stat".
" GROUP BY yyyy, mm".
" ORDER BY yyyy DESC, mm DESC");
$res = array();
while($row = $ilDB->fetchAssoc($set))
{
$res[] = array("month"=>$row["yyyy"]."-".$row["mm"],
"count"=>$row["counter"]);
}
return $res;
}

+ Here is the caller graph for this function:

ilTrQuery::getObjectTypeStatistics ( )

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

References $res, $row, and ilObjectDefinition\MODE_REPOSITORY.

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

{
global $ilDB, $objDefinition;
// re-use add new item selection (folder is not that important)
$types = array_keys($objDefinition->getCreatableSubObjects("root", ilObjectDefinition::MODE_REPOSITORY));
include_once "Services/Tree/classes/class.ilTree.php";
$tree = new ilTree(1);
$sql = "SELECT ".$tree->table_obj_data.".obj_id,".$tree->table_obj_data.".type,".
$tree->table_tree.".".$tree->tree_pk.",".$tree->table_obj_reference.".ref_id".
" FROM ".$tree->table_tree.
" ".$tree->buildJoin().
" WHERE ".$ilDB->in($tree->table_obj_data.".type", $types, "", "text");
$set = $ilDB->query($sql);
$res = array();
while($row = $ilDB->fetchAssoc($set))
{
$res[$row["type"]]["type"] = $row["type"];
$res[$row["type"]]["references"]++;
$res[$row["type"]]["objects"][] = $row["obj_id"];
if($row[$tree->tree_pk] < 0)
{
$res[$row["type"]]["deleted"]++;
}
}
foreach($res as $type => $values)
{
$res[$type]["objects"] = sizeof(array_unique($values["objects"]));
}
return $res;
}

+ Here is the caller graph for this function:

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

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

References $res, and $row.

Referenced by ilLPObjectStatisticsTypesTableGUI\getItems().

{
global $ilDB;
if(!$a_year)
{
$a_year = date("Y");
}
$agg = strtoupper($a_aggregation);
$res = array();
$sql = "SELECT type,yyyy,mm,".$agg."(cnt_objects) cnt_objects,".$agg."(cnt_references) cnt_references,".
"".$agg."(cnt_deleted) cnt_deleted FROM obj_type_stat".
" WHERE yyyy = ".$ilDB->quote($a_year, "integer").
" GROUP BY type,yyyy,mm";
$set = $ilDB->query($sql);
while($row = $ilDB->fetchAssoc($set))
{
$row["mm"] = str_pad($row["mm"], 2, "0", STR_PAD_LEFT);
$res[$row["type"]][$row["yyyy"]."-".$row["mm"]] = array(
"objects" => (int)$row["cnt_objects"],
"references" => (int)$row["cnt_references"],
"deleted" => (int)$row["cnt_deleted"]
);
}
return $res;
}

+ Here is the caller graph for this function:

static ilTrQuery::getParticipantsForObject (   $a_ref_id)
static

Get participant ids for given object.

Parameters
int$a_ref_id
Returns
array

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

References $path, ilGroupParticipants\_getInstanceByObjId(), ilCourseParticipants\_getInstanceByObjId(), ilObjSCORMTracking\_getTrackedUsers(), ilObject\_lookupObjectId(), ilObjSAHSLearningModule\_lookupSubType(), ilObject\_lookupType(), and ilLPStatusTestFinished\getParticipants().

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

{
global $tree;
$obj_id = ilObject::_lookupObjectId($a_ref_id);
$obj_type = ilObject::_lookupType($obj_id);
// try to get participants from (parent) course/group
switch($obj_type)
{
case "crs":
include_once "Modules/Course/classes/class.ilCourseParticipants.php";
$member_obj = ilCourseParticipants::_getInstanceByObjId($obj_id);
return $member_obj->getMembers();
case "grp":
include_once "Modules/Group/classes/class.ilGroupParticipants.php";
$member_obj = ilGroupParticipants::_getInstanceByObjId($obj_id);
return $member_obj->getMembers();
default:
// walk path to find course or group object and use members of that object
$path = $tree->getPathId($a_ref_id);
array_pop($path);
foreach(array_reverse($path) as $path_ref_id)
{
$type = ilObject::_lookupType($path_ref_id, true);
if($type == "crs" || $type == "grp")
{
return self::getParticipantsForObject($path_ref_id);
}
}
break;
}
$a_users = null;
// no participants possible: use tracking/object data where possible
switch($obj_type)
{
case "sahs":
include_once("./Modules/ScormAicc/classes/class.ilObjSAHSLearningModule.php");
$subtype = ilObjSAHSLearningModule::_lookupSubType($obj_id);
if ($subtype == "scorm2004")
{
// based on cmi_node/cp_node, used for scorm tracking data views
include_once("./Modules/Scorm2004/classes/class.ilObjSCORM2004LearningModule.php");
$mod = new ilObjSCORM2004LearningModule($obj_id, false);
$all = $mod->getTrackedUsers("");
if($all)
{
$a_users = array();
foreach($all as $item)
{
$a_users[] = $item["user_id"];
}
}
}
else
{
include_once("./Modules/ScormAicc/classes/SCORM/class.ilObjSCORMTracking.php");
$a_users = ilObjSCORMTracking::_getTrackedUsers($obj_id);
}
break;
case "exc":
include_once("./Modules/Exercise/classes/class.ilExerciseMembers.php");
include_once("./Modules/Exercise/classes/class.ilObjExercise.php");
$exc = new ilObjExercise($obj_id, false);
$members = new ilExerciseMembers($exc);
$a_users = $members->getMembers();
break;
case "tst":
include_once("./Services/Tracking/classes/class.ilLPStatusTestFinished.php");
$a_users = ilLPStatusTestFinished::getParticipants($obj_id);
break;
default:
// no sensible data: return null
break;
}
return $a_users;
}

+ Here is the call graph for this function:

+ Here is the caller graph for this function:

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

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

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

Referenced by ilLPProgressTableGUI\getItems().

{
self::refreshObjectsStatus(array($a_parent_obj_id), array($a_user_id));
// import score from tracking data
$scores_raw = $scores = array();
include_once './Modules/ScormAicc/classes/class.ilObjSAHSLearningModule.php';
$subtype = ilObjSAHSLearningModule::_lookupSubType($a_parent_obj_id);
switch($subtype)
{
case 'hacp':
case 'aicc':
case 'scorm':
include_once './Modules/ScormAicc/classes/class.ilObjSCORMLearningModule.php';
$module = new ilObjSCORMLearningModule($a_parent_obj_id, false);
$scores_raw = $module->getTrackingDataAgg($a_user_id);
break;
case 'scorm2004':
include_once './Modules/Scorm2004/classes/class.ilObjSCORM2004LearningModule.php';
$module = new ilObjSCORM2004LearningModule($a_parent_obj_id, false);
$scores_raw = $module->getTrackingDataAgg($a_user_id);
break;
}
if($scores_raw)
{
foreach($scores_raw as $item)
{
$scores[$item["sco_id"]] = $item["score"];
}
unset($module);
unset($scores_raw);
}
include_once 'Services/Tracking/classes/class.ilLPStatusWrapper.php';
$status_info = ilLPStatusWrapper::_getStatusInfo($a_parent_obj_id);
$items = array();
foreach($a_sco_ids as $sco_id)
{
// #9719 - can have in_progress AND failed/completed
if(in_array($a_user_id, $status_info["failed"][$sco_id]))
{
$status = LP_STATUS_FAILED;
}
elseif(in_array($a_user_id, $status_info["completed"][$sco_id]))
{
$status = LP_STATUS_COMPLETED;
}
elseif(in_array($a_user_id, $status_info["in_progress"][$sco_id]))
{
$status = LP_STATUS_IN_PROGRESS;
}
else
{
$status = LP_STATUS_NOT_ATTEMPTED;
}
$items[$sco_id] = array(
"title" => $status_info["scos_title"][$sco_id],
"status" => $status,
"type" => "sahs",
"score" => (int)$scores[$sco_id]
);
}
return $items;
}

+ Here is the call graph for this function:

+ Here is the caller graph for this function:

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 500 of file class.ilTrQuery.php.

References $query, ilDatePresentation\formatPeriod(), and IL_CAL_DATETIME.

Referenced by getObjectsDataForUser(), and getObjectsStatusForUser().

{
global $ilDB;
$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,".
" mark, e_comment".
" FROM event".
" JOIN event_appointment ON (event.obj_id = event_appointment.event_id)".
" LEFT JOIN event_participants ON (event_participants.event_id = event.obj_id AND usr_id = ".$ilDB->quote($a_user_id, "integer").")".
" WHERE ".$ilDB->in("obj_id", $obj_ids , false, "integer");
$set = $ilDB->query($query);
$sessions = array();
while($rec = $ilDB->fetchAssoc($set))
{
$rec["comment"] = $rec["e_comment"];
unset($rec["e_comment"]);
$date = ilDatePresentation::formatPeriod(
new ilDateTime($rec["e_start"], IL_CAL_DATETIME),
new ilDateTime($rec["e_end"], IL_CAL_DATETIME));
if($rec["title"])
{
$rec["title"] = $date.': '.$rec["title"];
}
else
{
$rec["title"] = $date;
}
$sessions[$rec["obj_id"]] = $rec;
}
return $sessions;
}

+ Here is the call graph for this function:

+ Here is the caller graph for this function:

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 1209 of file class.ilTrQuery.php.

References ilLPObjSettings\_lookupMode(), ilRepositoryObjectPluginSlot\isTypePluginWithLP(), LP_MODE_PLUGIN, and LP_MODE_UNDEFINED.

Referenced by getObjectIds().

{
global $tree;
$children = $tree->getChilds($a_parent_ref_id);
if($children)
{
foreach($children as $child)
{
if($child["type"] == "adm" || $child["type"] == "rolf")
{
continue;
}
// as there can be deactivated items in the collection
// we should allow them here too
$cmode = ilLPObjSettings::_lookupMode($child["obj_id"]);
if($cmode == LP_MODE_PLUGIN)
{
// #11368
include_once "Services/Repository/classes/class.ilRepositoryObjectPluginSlot.php";
if(ilRepositoryObjectPluginSlot::isTypePluginWithLP($child["type"], false))
{
$a_object_ids[] = $child["obj_id"];
$a_ref_ids[$child["obj_id"]] = $child["ref_id"];
}
}
else if(/* $cmode != LP_MODE_DEACTIVATED && */ $cmode != LP_MODE_UNDEFINED)
{
$a_object_ids[] = $child["obj_id"];
$a_ref_ids[$child["obj_id"]] = $child["ref_id"];
}
self::getSubTree($child["ref_id"], $a_object_ids, $a_ref_ids);
}
}
}

+ Here is the call graph for this function:

+ Here is the caller graph for this function:

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 617 of file class.ilTrQuery.php.

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

Referenced by getObjectsSummaryForObject().

{
global $ilDB;
$where = array();
$where[] = "usr_data.usr_id <> ".$ilDB->quote(ANONYMOUS_USER_ID, "integer");
// users
$a_users = self::getParticipantsForObject($a_ref_id);
$left = "";
if (is_array($a_users)) // #14840
{
$left = "LEFT";
$where[] = $ilDB->in("usr_data.usr_id", $a_users, false, "integer");
}
$obj_id = ilObject::_lookupObjectId($a_ref_id);
self::refreshObjectsStatus(array($obj_id), $a_users);
$query = " FROM usr_data ".$left." JOIN read_event ON (read_event.usr_id = usr_data.usr_id".
" AND obj_id = ".$ilDB->quote($obj_id, "integer").")".
" LEFT JOIN ut_lp_marks ON (ut_lp_marks.usr_id = usr_data.usr_id ".
" AND ut_lp_marks.obj_id = ".$ilDB->quote($obj_id, "integer").")".
" LEFT JOIN usr_pref ON (usr_pref.usr_id = usr_data.usr_id AND keyword = ".$ilDB->quote("language", "text").")".
self::buildFilters($where, $a_filters, true);
$fields[] = 'COUNT(usr_data.usr_id) AS user_count';
$queries = array();
$queries[] = array("fields"=>$fields, "query"=>$query, "count"=>"*");
$result = self::executeQueries($queries);
$result = $result["set"][0];
$users_no = $result["user_count"];
$valid = true;
if(!$users_no)
{
$valid = false;
}
else if(isset($a_filters["user_total"]))
{
if($a_filters["user_total"]["from"] && $users_no < $a_filters["user_total"]["from"])
{
$valid = false;
}
else if($a_filters["user_total"]["to"] && $users_no > $a_filters["user_total"]["to"])
{
$valid = false;
}
}
if($valid)
{
$result["country"] = self::getSummaryPercentages("country", $query);
$result["sel_country"] = self::getSummaryPercentages("sel_country", $query);
$result["city"] = self::getSummaryPercentages("city", $query);
$result["gender"] = self::getSummaryPercentages("gender", $query);
$result["language"] = self::getSummaryPercentages("usr_pref.value", $query, "language");
$result["status"] = self::getSummaryPercentages("status", $query);
$result["mark"] = self::getSummaryPercentages("mark", $query);
}
else
{
$result = array();
}
if($result)
{
$result["user_total"] = $users_no;
}
return $result;
}

+ Here is the call graph for this function:

+ Here is the caller graph for this function:

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 700 of file class.ilTrQuery.php.

References $query, and $result.

Referenced by getSummaryDataForObject().

{
global $ilDB;
if(!$alias)
{
$field_alias = $field;
}
else
{
$field_alias = $alias;
$alias = " AS ".$alias;
}
// move having BEHIND group by
$having = "";
if(preg_match("/".preg_quote(" [[--HAVING")."(.+)".preg_quote("HAVING--]]")."/", $base_query, $hits))
{
$having = " HAVING ".$hits[1];
$base_query = str_replace($hits[0], "", $base_query);
}
$query = "SELECT COUNT(*) AS counter, ".$field.$alias." ".$base_query. " GROUP BY ".$field.$having." ORDER BY counter DESC";
$set = $ilDB->query($query);
$result = array();
while($rec = $ilDB->fetchAssoc($set))
{
$result[$rec[$field_alias]] = (int)$rec["counter"];
}
return $result;
}

+ Here is the caller graph for this function:

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 196 of file class.ilTrQuery.php.

References $query, $result, $row, ilObject\_lookupObjectId(), buildColumns(), buildFilters(), executeQueries(), getParticipantsForObject(), ilMemberAgreement\lookupAcceptedAgreements(), refreshObjectsStatus(), and ilUtil\stableSortArray().

Referenced by ilTrObjectUsersPropsTableGUI\getItems().

{
global $ilDB;
$fields = array("usr_data.usr_id", "login", "active");
$udf = self::buildColumns($fields, $a_additional_fields);
$where = array();
$where[] = "usr_data.usr_id <> ".$ilDB->quote(ANONYMOUS_USER_ID, "integer");
// users
$left = "";
$a_users = self::getParticipantsForObject($a_ref_id);
$obj_id = ilObject::_lookupObjectId($a_ref_id);
self::refreshObjectsStatus(array($obj_id), $a_users);
if (is_array($a_users))
{
$left = "LEFT";
$where[] = $ilDB->in("usr_data.usr_id", $a_users, false, "integer");
}
$query = " FROM usr_data ".$left." JOIN read_event ON (read_event.usr_id = usr_data.usr_id".
" AND read_event.obj_id = ".$ilDB->quote($obj_id, "integer").")".
" LEFT JOIN ut_lp_marks ON (ut_lp_marks.usr_id = usr_data.usr_id ".
" AND ut_lp_marks.obj_id = ".$ilDB->quote($obj_id, "integer").")".
" LEFT JOIN usr_pref ON (usr_pref.usr_id = usr_data.usr_id AND keyword = ".$ilDB->quote("language", "text").")".
self::buildFilters($where, $a_filters);
$queries = array(array("fields"=>$fields, "query"=>$query));
// #9598 - if language is not in fields alias is missing
if($a_order_field == "language")
{
$a_order_field = "usr_pref.value";
}
// udf data is added later on, not in this query
$udf_order = null;
if(!$a_order_field)
{
$a_order_field = "login";
}
else if(substr($a_order_field, 0, 4) == "udf_")
{
$udf_order = $a_order_field;
$a_order_field = null;
}
$result = self::executeQueries($queries, $a_order_field, $a_order_dir, $a_offset, $a_limit);
if($result["cnt"])
{
if(sizeof($udf))
{
$query = "SELECT usr_id, field_id, value FROM udf_text WHERE ".$ilDB->in("field_id", $udf, false, "integer");
$set = $ilDB->query($query);
$udf = array();
while($row = $ilDB->fetchAssoc($set))
{
$udf[$row["usr_id"]]["udf_".$row["field_id"]] = $row["value"];
}
}
// (course) user agreement
if($check_agreement)
{
// admins/tutors (write-access) will never have agreement ?!
include_once "Services/Membership/classes/class.ilMemberAgreement.php";
$agreements = ilMemberAgreement::lookupAcceptedAgreements($check_agreement);
// public information for users
$query = "SELECT usr_id FROM usr_pref WHERE keyword = ".$ilDB->quote("public_profile", "text").
" AND value = ".$ilDB->quote("y", "text")." OR value = ".$ilDB->quote("g", "text");
$set = $ilDB->query($query);
$all_public = array();
while($row = $ilDB->fetchAssoc($set))
{
$all_public[] = $row["usr_id"];
}
$query = "SELECT usr_id,keyword FROM usr_pref WHERE ".$ilDB->like("keyword", "text", "public_%", false).
" AND value = ".$ilDB->quote("y", "text")." AND ".$ilDB->in("usr_id", $all_public, "", "integer");
$set = $ilDB->query($query);
$public = array();
while($row = $ilDB->fetchAssoc($set))
{
$public[$row["usr_id"]][] = substr($row["keyword"], 7);
}
unset($all_public);
}
foreach($result["set"] as $idx => $row)
{
// add udf data
if(isset($udf[$row["usr_id"]]))
{
$result["set"][$idx] = $row = array_merge($row, $udf[$row["usr_id"]]);
}
// remove all private data - if active agreement and agreement not given by user
if(sizeof($privacy_fields) && $check_agreement && !in_array($row["usr_id"], $agreements))
{
foreach($privacy_fields as $field)
{
// check against public profile
if(isset($row[$field]) && (!isset($public[$row["usr_id"]]) ||
!in_array($field, $public[$row["usr_id"]])))
{
// remove complete entry - offending field was filtered
if(isset($a_filters[$field]))
{
unset($result["set"][$idx]);
break;
}
// remove offending field
else
{
$result["set"][$idx][$field] = false;
}
}
}
}
}
// as we cannot do this in the query, sort by custom field here
// this will not work with pagination!
if($udf_order)
{
include_once "Services/Utilities/classes/class.ilStr.php";
$result["set"] = ilUtil::stableSortArray($result["set"],
$udf_order, $a_order_dir);
}
}
return $result;
}

+ Here is the call graph for this function:

+ Here is the caller graph for this function:

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

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

References $query, buildFilters(), and executeQueries().

Referenced by ilTrMatrixTableGUI\getItems().

{
global $ilDB;
if($a_parent_obj_id && $a_users)
{
include_once("Services/Tracking/classes/class.ilLPStatus.php");
$fields = array("crs_objectives.objective_id AS obj_id", "crs_objective_status.user_id AS usr_id", "title");
$fields[] = "CASE WHEN status IS NOT NULL THEN ".LP_STATUS_COMPLETED_NUM." ELSE NULL END AS status";
$where = array();
$where[] = "crs_objectives.crs_id = ".$ilDB->quote($a_parent_obj_id, "integer");
$query = " FROM crs_objectives".
" LEFT JOIN crs_objective_status ON (crs_objectives.objective_id = crs_objective_status.objective_id".
" AND ".$ilDB->in("crs_objective_status.user_id", $a_users, "", "integer").")".
self::buildFilters($where);
return self::executeQueries(array(array("fields"=>$fields, "query"=>$query, "count"=>"crs_objectives.objective_id")));
}
}

+ Here is the call graph for this function:

+ Here is the caller graph for this function:

static ilTrQuery::getUserObjectMatrix (   $a_parent_ref_id,
  $a_obj_ids,
  $a_user_filter = NULL 
)
static

Get status matrix for users on objects.

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

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

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

Referenced by ilTrMatrixTableGUI\getItems().

{
global $ilDB;
$result = array("cnt"=>0, "set"=>NULL);
if(sizeof($a_obj_ids))
{
$where = array();
$where[] = "usr_data.usr_id <> ".$ilDB->quote(ANONYMOUS_USER_ID, "integer");
if($a_user_filter)
{
$where[] = $ilDB->like("usr_data.login", "text", "%".$a_user_filter."%");
}
// users
$left = "";
$a_users = self::getParticipantsForObject($a_parent_ref_id);
if (is_array($a_users))
{
$left = "LEFT";
$where[] = $ilDB->in("usr_data.usr_id", $a_users, false, "integer");
}
include_once("./Services/Tracking/classes/class.ilLPStatus.php");
$fields = array("usr_data.usr_id", "login", "active", "status",
"status_changed", "percentage", "last_access",
"spent_seconds+childs_spent_seconds as spent_seconds");
$parent_obj_id = ilObject::_lookupObjectId($a_parent_ref_id);
self::refreshObjectsStatus(array($parent_obj_id), $a_users);
self::refreshObjectsStatus($a_obj_ids, $a_users);
$raw = array();
foreach($a_obj_ids as $obj_id)
{
// one request for each object
$query = " FROM usr_data ".$left." JOIN read_event ON (read_event.usr_id = usr_data.usr_id".
" AND read_event.obj_id = ".$ilDB->quote($obj_id, "integer").")".
" LEFT JOIN ut_lp_marks ON (ut_lp_marks.usr_id = usr_data.usr_id ".
" AND ut_lp_marks.obj_id = ".$ilDB->quote($obj_id, "integer").")".
self::buildFilters($where);
$raw = self::executeQueries(array(array("fields"=>$fields, "query"=>$query)), "login");
if($raw["cnt"])
{
// convert to final structure
foreach($raw["set"] as $row)
{
$result["set"][$row["usr_id"]]["login"] = $row["login"];
$result["set"][$row["usr_id"]]["usr_id"] = $row["usr_id"];
$result["set"][$row["usr_id"]]["active"] = $row["active"]; // #11377
$result["set"][$row["usr_id"]]["objects"][$obj_id] = array("status"=>$row["status"],
"percentage"=>$row["percentage"]);
if($obj_id == $parent_obj_id)
{
$result["set"][$row["usr_id"]]["status_changed"] = $row["status_changed"];
$result["set"][$row["usr_id"]]["last_access"] = $row["last_access"];
$result["set"][$row["usr_id"]]["spent_seconds"] = $row["spent_seconds"];
}
}
}
}
$result["cnt"] = sizeof($result["set"]);
$result["users"] = $a_users;
}
return $result;
}

+ Here is the call graph for this function:

+ Here is the caller graph for this function:

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 1663 of file class.ilTrQuery.php.

References ilLPStatus\checkStatusForObject().

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

{
include_once("./Services/Tracking/classes/class.ilLPStatus.php");
foreach($a_obj_ids as $obj_id)
{
ilLPStatus::checkStatusForObject($obj_id, $a_users);
}
}

+ Here is the call graph for this function:

+ Here is the caller graph for this function:

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

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

References $ref_id, $res, $row, and ilObject\_getAllReferences().

{
global $ilDB, $tree;
if($a_type == "lres")
{
$a_type = array('lm','sahs','htlm','dbk');
}
$sql = "SELECT r.ref_id,r.obj_id".
" FROM object_data o".
" JOIN object_reference r ON (o.obj_id = r.obj_id)".
" JOIN tree t ON (t.child = r.ref_id)".
" WHERE t.tree = ".$ilDB->quote(1, "integer");
if(!is_array($a_type))
{
$sql .= " AND o.type = ".$ilDB->quote($a_type, "text");
}
else
{
$sql .= " AND ".$ilDB->in("o.type", $a_type, "", "text");
}
if($a_title)
{
$sql .= " AND (".$ilDB->like("o.title", "text", "%".$a_title."%").
" OR ".$ilDB->like("o.description", "text", "%".$a_title."%").")";
}
if(is_array($a_hidden))
{
$sql .= " AND ".$ilDB->in("o.obj_id", $a_hidden, true, "integer");
}
if(is_array($a_preset_obj_ids))
{
$sql .= " AND ".$ilDB->in("o.obj_id", $a_preset_obj_ids, false, "integer");
}
$set = $ilDB->query($sql);
$res = array();
while($row = $ilDB->fetchAssoc($set))
{
if($a_root && $a_root != ROOT_FOLDER_ID)
{
foreach(ilObject::_getAllReferences($row['obj_id']) as $ref_id)
{
if($tree->isGrandChild($a_root, $ref_id))
{
$res[$row["obj_id"]][] = $row["ref_id"];
continue;
}
}
}
else
{
$res[$row["obj_id"]][] = $row["ref_id"];
}
}
return $res;
}

+ Here is the call graph for this function:

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