/**
* should be avoided; store value to increase performance for further requests
*/
function getSumTotalTimeSecondsFromScos($a_obj_id, $a_user_id, $a_write = false)
{
global $ilDB, $ilLog;
$scos = array();
$val_set = $ilDB->queryF('SELECT cp_node_id FROM cp_node
WHERE nodename = %s
AND cp_node.slm_id = %s', array('text', 'integer'), array('item', $a_obj_id));
while ($val_rec = $ilDB->fetchAssoc($val_set)) {
array_push($scos, $val_rec['cp_node_id']);
}
$time = 0;
foreach ($scos as $sco) {
include_once "./Modules/Scorm2004/classes/class.ilObjSCORM2004LearningModule.php";
$data_set = $ilDB->queryF('
SELECT total_time
FROM cmi_node
WHERE cp_node_id = %s
AND user_id = %s', array('integer', 'integer'), array($sco, $a_user_id));
while ($data_rec = $ilDB->fetchAssoc($data_set)) {
$sec = ilObjSCORM2004LearningModule::_ISODurationToCentisec($data_rec["total_time"]) / 100;
}
$time += (int) $sec;
$sec = 0;
//$ilLog->write("++".$time);
}
if ($a_write && $time > 0) {
$ilDB->queryF('UPDATE sahs_user SET sco_total_time_sec=%s WHERE obj_id = %s AND user_id = %s', array('integer', 'integer', 'integer'), array($time, $a_obj_id, $a_user_id));
}
return $time;
}
/**
* Synch read event table
*
* @param
* @return
*/
function _syncReadEvent($a_obj_id, $a_user_id, $a_type, $a_ref_id)
{
global $ilDB, $ilLog;
// get attempts
$val_set = $ilDB->queryF('
SELECT rvalue FROM cmi_custom
WHERE user_id = %s
AND sco_id = %s
AND lvalue = %s
AND obj_id = %s', array('integer', 'integer', 'text', 'integer'), array($a_user_id, 0, 'package_attempts', $a_obj_id));
$val_rec = $ilDB->fetchAssoc($val_set);
$val_rec["rvalue"] = str_replace("\r\n", "\n", $val_rec["rvalue"]);
if ($val_rec["rvalue"] == null) {
$val_rec["rvalue"] = "";
}
$attempts = $val_rec["rvalue"];
// time
$scos = array();
$val_set = $ilDB->queryF('SELECT cp_node_id FROM cp_node
WHERE nodename = %s
AND cp_node.slm_id = %s', array('text', 'integer'), array('item', $a_obj_id));
while ($val_rec = $ilDB->fetchAssoc($val_set)) {
array_push($scos, $val_rec['cp_node_id']);
}
$time = 0;
foreach ($scos as $sco) {
include_once "./Modules/Scorm2004/classes/class.ilObjSCORM2004LearningModule.php";
$data_set = $ilDB->queryF('
SELECT total_time
FROM cmi_node
WHERE cp_node_id = %s
AND user_id = %s', array('integer', 'integer'), array($sco, $a_user_id));
while ($data_rec = $ilDB->fetchAssoc($data_set)) {
// see bug report 7246
// $sec = ilObjSCORM2004LearningModule::_ISODurationToCentisec($data_rec["session_time"]) / 100;
$sec = ilObjSCORM2004LearningModule::_ISODurationToCentisec($data_rec["total_time"]) / 100;
}
$time += (int) $sec;
$sec = 0;
//$ilLog->write("++".$time);
}
include_once "./Services/Tracking/classes/class.ilChangeEvent.php";
ilChangeEvent::_recordReadEvent($a_type, $a_ref_id, $a_obj_id, $a_user_id, false, $attempts, $time);
}
function tracInteractionUser($a_user = array(), $a_sco = array(), $b_orderBySCO = false, $allowExportPrivacy = false)
{
global $ilDB, $lng;
$lng->loadLanguageModule("scormtrac");
$returnData = array();
$scoTitles = self::scoTitlesForExportSelected();
$scoProgress = self::markedLearningStatusForExportSelected($scoTitles);
$a_correct = array();
$a_incorrect = array();
$a_other = array();
$query = 'SELECT cmi_node.user_id, cmi_node.cp_node_id, count(*) as counter
FROM cmi_interaction, cmi_node
WHERE ' . $ilDB->in('cp_node_id', $a_sco, false, 'integer') . '
AND ' . $ilDB->in('cmi_node.user_id', $a_user, false, 'integer') . '
AND cmi_node.cmi_node_id = cmi_interaction.cmi_node_id
AND cmi_interaction.result = %s
GROUP BY cmi_node.user_id,cmi_node.cp_node_id';
$res = $ilDB->queryF($query, array('text'), array('correct'));
while ($row = $ilDB->fetchAssoc($res)) {
$a_correct[$row['user_id'] . ':' . $row['cp_node_id']] = $row['counter'];
}
$res = $ilDB->queryF($query, array('text'), array('incorrect'));
while ($row = $ilDB->fetchAssoc($res)) {
$a_incorrect[$row['user_id'] . ':' . $row['cp_node_id']] = $row['counter'];
}
$query = 'SELECT cmi_node.user_id, cmi_node.cp_node_id, count(*) as counter
FROM cmi_interaction, cmi_node
WHERE ' . $ilDB->in('cp_node_id', $a_sco, false, 'integer') . '
AND ' . $ilDB->in('cmi_node.user_id', $a_user, false, 'integer') . '
AND cmi_node.cmi_node_id = cmi_interaction.cmi_node_id
AND cmi_interaction.result <> %s AND cmi_interaction.result <> %s
GROUP BY cmi_node.user_id,cmi_node.cp_node_id';
$res = $ilDB->queryF($query, array('text', 'text'), array('correct', 'incorrect'));
while ($row = $ilDB->fetchAssoc($res)) {
$a_other[$row['user_id'] . ':' . $row['cp_node_id']] = $row['counter'];
}
$dbdata = array();
$query = 'SELECT user_id, cp_node_id, ' . 'audio_captioning, audio_level, completion_status, completion_threshold, credit, delivery_speed, ' . 'c_entry, c_exit, c_language, location as c_location, c_mode, progress_measure, c_max, c_min, c_raw, scaled, ' . 'scaled_passing_score, session_time, success_status, total_time, c_timestamp, suspend_data, launch_data ' . 'FROM cmi_node ' . 'WHERE ' . $ilDB->in('cp_node_id', $a_sco, false, 'integer') . ' ' . 'AND ' . $ilDB->in('user_id', $a_user, false, 'integer') . ' ' . 'ORDER BY ';
if ($b_orderBySCO) {
$query .= 'cp_node_id, user_id';
} else {
$query .= 'user_id, cp_node_id';
}
$res = $ilDB->query($query);
while ($row = $ilDB->fetchAssoc($res)) {
$dbdata[] = $row;
}
foreach ($dbdata as $data) {
$skey = $data["user_id"] . ':' . $data["cp_node_id"];
$correct = 0;
if ($a_correct[$skey] != null) {
$correct = $a_correct[$skey];
}
$incorrect = 0;
if ($a_incorrect[$skey] != null) {
$incorrect = $a_incorrect[$skey];
}
$other = 0;
if ($a_other[$skey] != null) {
$other = $a_other[$skey];
}
$all = $correct + $incorrect + $other;
$data["lm_id"] = $this->getObjId();
$data["lm_title"] = $this->lmTitle;
$data = array_merge($data, self::userDataArrayForExport($data["user_id"], $allowExportPrivacy));
$data["sco_marked_for_learning_progress"] = $scoProgress[$data["cp_node_id"]];
$data["sco_title"] = $scoTitles[$data["cp_node_id"]];
$data["counter_i_correct"] = $correct;
if ($all > 0) {
$data["counter_i_correct_percent"] = $correct * 100 / $all;
} else {
$data["counter_i_correct_percent"] = 0;
}
$data["counter_i_incorrect"] = $incorrect;
if ($all > 0) {
$data["counter_i_incorrect_percent"] = $incorrect * 100 / $all;
} else {
$data["counter_i_incorrect_percent"] = 0;
}
$data["counter_i_other"] = $other;
if ($all > 0) {
$data["counter_i_other_percent"] = $other * 100 / $all;
} else {
$data["counter_i_other_percent"] = 0;
}
$data["audio_captioning"] = "" . $data["audio_captioning"];
$data["audio_level"] = "" . $data["audio_level"];
$data["completion_status"] = "" . $data["completion_status"];
$data["completion_threshold"] = "" . $data["completion_threshold"];
$data["credit"] = "" . $data["credit"];
$data["delivery_speed"] = "" . $data["delivery_speed"];
$data["c_entry"] = "" . $data["c_entry"];
$data["c_exit"] = "" . $data["c_exit"];
$data["c_language"] = "" . $data["c_language"];
$data["c_location"] = "" . str_replace('"', '', $data["c_location"]);
$data["c_mode"] = "" . $data["c_mode"];
$data["progress_measure"] = "" . $data["progress_measure"];
$data["c_max"] = "" . $data["c_max"];
$data["c_min"] = "" . $data["c_min"];
$data["c_raw"] = "" . $data["c_raw"];
$data["scaled"] = "" . $data["scaled"];
//$data["scaled"]*100)
$data["scaled_passing_score"] = "" . $data["scaled_passing_score"];
$data["session_time"] = "" . $data["session_time"];
$data["session_time_seconds"] = "";
if ($data["session_time"] != "") {
$data["session_time_seconds"] = round(ilObjSCORM2004LearningModule::_ISODurationToCentisec($data["session_time"]) / 100);
}
$data["success_status"] = "" . $data["success_status"];
$data["total_time"] = "" . $data["total_time"];
$data["total_time_seconds"] = "";
if ($data["total_time"] != "") {
$data["total_time_seconds"] = round(ilObjSCORM2004LearningModule::_ISODurationToCentisec($data["total_time"]) / 100);
}
$data["c_timestamp"] = $data["c_timestamp"];
//ilDatePresentation::formatDate(new ilDateTime($data["c_timestamp"],IL_CAL_UNIX));
$data["suspend_data"] = "" . $data["suspend_data"];
$data["launch_data"] = "" . $data["launch_data"];
// if ($data["success_status"]!="" && $data["success_status"]!="unknown") {
// $status = $data["success_status"];
// } else {
// if ($data["completion_status"]=="") {
// $status="unknown";
// } else {
// $status = $data["completion_status"];
// }
// }
$returnData[] = $data;
}
return $returnData;
}
