class.ilSCORM2004TrackingItems.php

Go to the documentation of this file.

<?php
/* Copyright (c) 1998-2013 ILIAS open source, Extended GPL, see docs/LICENSE */
 
include_once './Modules/ScormAicc/classes/class.ilSCORMTrackingItems.php';
include_once './Modules/Scorm2004/classes/class.ilObjSCORM2004LearningModule.php';
class ilSCORM2004TrackingItems extends ilSCORMTrackingItems
{
    protected $db;
 
    protected $lng;
 
 
    public function __construct()
    {
        global $DIC;
 
        $this->db = $DIC->database();
        $this->lng = $DIC->language();
    }
 
 
    public function scoTitlesForExportSelected($obj_id)
    {
        $ilDB = $this->db;
        $scoTitles = array();
        $query = 'SELECT cp_item.cp_node_id, cp_item.title '
            . 'FROM cp_item, cmi_node, cp_node '
            . 'WHERE cp_node.slm_id = %s '
            . 'AND cp_item.cp_node_id = cmi_node.cp_node_id '
            . 'AND cp_node.cp_node_id = cmi_node.cp_node_id '
            . 'GROUP BY cp_item.cp_node_id, cp_item.title';
        $res = $ilDB->queryF(
            $query,
            array('integer'),
            array($obj_id)
        );
        while ($row = $ilDB->fetchAssoc($res)) {
            $scoTitles[$row['cp_node_id']] = $row['title'];
        }
        return $scoTitles;
    }
 
    public static function exportSelectedCoreColumns($b_orderBySCO, $b_allowExportPrivacy)
    {
        global $DIC;
 
        $lng = $DIC->language();
        $lng->loadLanguageModule("scormtrac");
        // default fields
        $cols = array();
        $udh = self::userDataHeaderForExport();
        $a_cols = explode(
            ',',
            'lm_id,lm_title,cp_node_id,sco_marked_for_learning_progress,sco_title,' . $udh["cols"]
            . ',audio_captioning,audio_level,completion_status,completion_threshold,credit,delivery_speed'
            . ',c_entry,c_exit,c_language,c_location,c_mode,progress_measure,c_max,c_min,c_raw,scaled'
            . ',scaled_passing_score,session_time,session_time_seconds,success_status,total_time,total_time_seconds,c_timestamp,suspend_data,launch_data'
        );
        $a_true = explode(',', $udh["default"] . ",sco_title,success_status,completion_status");
        for ($i = 0;$i < count($a_cols);$i++) {
            $cols[$a_cols[$i]] = array("txt" => $lng->txt($a_cols[$i]),"default" => false);
        }
        for ($i = 0;$i < count($a_true);$i++) {
            $cols[$a_true[$i]]["default"] = true;
        }
        return $cols;
    }
 
    public function exportSelectedCore($a_user = array(), $a_sco = array(), $b_orderBySCO = false, $allowExportPrivacy = false, $obj_id, $lmTitle)
    {
        $ilDB = $this->db;
        $lng = $this->lng;
        $lng->loadLanguageModule("scormtrac");
 
        $returnData = array();
 
        $scoTitles = self::scoTitlesForExportSelected($obj_id);
 
        $scoProgress = self::markedLearningStatusForExportSelected($scoTitles, $obj_id);
 
 
        $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) {
            $data["lm_id"] = $obj_id;
            $data["lm_title"] = $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["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;
    }
    
    public static function exportSelectedInteractionsColumns()
    {
        global $DIC;
 
        $lng = $DIC->language();
        $lng->loadLanguageModule("scormtrac");
        $cols = array();
        $udh = self::userDataHeaderForExport();
        $a_cols = explode(
            ',',
            'lm_id,lm_title,cp_node_id,sco_marked_for_learning_progress,sco_title,' . $udh["cols"]
            . ',id,description,weighting,c_type,result,latency,latency_seconds,c_timestamp,learner_response'
        );
        $a_true = explode(',', $udh["default"] . ",sco_title,id,result,learner_response");//note for trunk: id instead of description
        for ($i = 0;$i < count($a_cols);$i++) {
            $cols[$a_cols[$i]] = array("txt" => $lng->txt($a_cols[$i]),"default" => false);
        }
        for ($i = 0;$i < count($a_true);$i++) {
            $cols[$a_true[$i]]["default"] = true;
        }
        return $cols;
    }
 
    public function exportSelectedInteractions($a_user = array(), $a_sco = array(), $b_orderBySCO = false, $allowExportPrivacy = false, $obj_id, $lmTitle)
    {
        $ilDB = $this->db;
        $lng = $this->lng;
        $lng->loadLanguageModule("scormtrac");
 
        $returnData = array();
 
        $scoTitles = self::scoTitlesForExportSelected($obj_id);
 
        $scoProgress = self::markedLearningStatusForExportSelected($scoTitles, $obj_id);
 
        $dbdata = array();
        $query = 'SELECT cmi_node.user_id, cmi_node.cp_node_id,
                                cmi_interaction.cmi_interaction_id,
                                cmi_interaction.id, 
                                cmi_interaction.description, 
                                cmi_interaction.weighting, 
                                cmi_interaction.c_type, 
                                cmi_interaction.result, 
                                cmi_interaction.latency, 
                                cmi_interaction.c_timestamp, 
                                cmi_interaction.learner_response, 
                                cmi_interaction.cmi_interaction_id, 
                                cmi_interaction.cmi_node_id 
                                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
                                ORDER BY ';
        if ($b_orderBySCO) {
            $query .= 'cmi_node.cp_node_id, cmi_node.user_id';
        } else {
            $query .= 'cmi_node.user_id, cmi_node.cp_node_id';
        }
        $query .= ', cmi_interaction.cmi_interaction_id, cmi_interaction.cmi_node_id';
        $res = $ilDB->query($query);
        while ($row = $ilDB->fetchAssoc($res)) {
            $dbdata[] = $row;
        }
        foreach ($dbdata as $data) {
            $data["lm_id"] = $obj_id;
            $data["lm_title"] = $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["description"] = "" . $data["description"];
            $data["weighting"] = "" . $data["weighting"];
            $data["c_type"] = "" . $data["c_type"];
            $data["result"] = "" . $data["result"];
            $data["latency"] = "" . $data["latency"];
            $data["latency_seconds"] = "";
            if ($data["latency"] != "") {
                $data["latency_seconds"] = round(ilObjSCORM2004LearningModule::_ISODurationToCentisec($data["latency"]) / 100);
            }
            $data["c_timestamp"] = "" . $data["c_timestamp"];
            $data["learner_response"] = "" . str_replace('"', '', $data["learner_response"]);
            $returnData[] = $data;
        }
        //              var_dump($returnData);
        return $returnData;
    }
 
    public static function exportSelectedObjectivesColumns()
    {
        global $DIC;
 
        $lng = $DIC->language();
        $lng->loadLanguageModule("scormtrac");
        $cols = array();
        $udh = self::userDataHeaderForExport();
        $a_cols = explode(
            ',',
            'lm_id,lm_title,cp_node_id,sco_marked_for_learning_progress,sco_title,' . $udh["cols"]
            . ',id,description,completion_status,progress_measure,success_status,scaled,c_max,c_min,c_raw,scope'
        );
        $a_true = explode(',', $udh["default"] . ",sco_title,id,completion_status,success_status");
        for ($i = 0;$i < count($a_cols);$i++) {
            $cols[$a_cols[$i]] = array("txt" => $lng->txt($a_cols[$i]),"default" => false);
        }
        for ($i = 0;$i < count($a_true);$i++) {
            $cols[$a_true[$i]]["default"] = true;
        }
        return $cols;
    }
 
    public function exportSelectedObjectives($a_user = array(), $a_sco = array(), $b_orderBySCO = false, $allowExportPrivacy = false, $obj_id, $lmTitle)
    {
        $ilDB = $this->db;
        $lng = $this->lng;
        $lng->loadLanguageModule("scormtrac");
 
        $returnData = array();
 
        $scoTitles = self::scoTitlesForExportSelected($obj_id);
 
        $scoProgress = self::markedLearningStatusForExportSelected($scoTitles, $obj_id);
 
        $dbdata = array();
        $query = 'SELECT cmi_node.user_id, cmi_node.cp_node_id,
                                cmi_objective.cmi_objective_id,
                                cmi_objective.id, 
                                cmi_objective.description, 
                                cmi_objective.completion_status,
                                cmi_objective.progress_measure,
                                cmi_objective.success_status,
                                cmi_objective.scaled,
                                cmi_objective.c_max,
                                cmi_objective.c_min,
                                cmi_objective.c_raw,
                                cmi_objective.scope 
                                FROM cmi_objective, 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_objective.cmi_node_id 
                                AND cmi_interaction_id is null 
                                ORDER BY ';
        if ($b_orderBySCO) {
            $query .= 'cmi_node.cp_node_id, cmi_node.user_id';
        } else {
            $query .= 'cmi_node.user_id, cmi_node.cp_node_id';
        }
        $query .= ', cmi_objective.cmi_node_id';
        $res = $ilDB->query($query);
        while ($row = $ilDB->fetchAssoc($res)) {
            $dbdata[] = $row;
        }
        foreach ($dbdata as $data) {
            $data["lm_id"] = $obj_id;
            $data["lm_title"] = $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["description"] = "" . $data["description"];
            $data["completion_status"] = "" . $data["completion_status"];
            $data["progress_measure"] = "" . $data["progress_measure"];
            $data["success_status"] = "" . $data["success_status"];
            $data["scaled"] = "" . $data["scaled"];
            $data["c_max"] = "" . $data["c_max"];
            $data["c_min"] = "" . $data["c_min"];
            $data["c_raw"] = "" . $data["c_raw"];
            $data["scope"] = "" . $data["scope"];
            $returnData[] = $data;
        }
        //              var_dump($returnData);
        return $returnData;
    }
 
    public static function exportObjGlobalToSystemColumns()
    {
        global $DIC;
 
        $lng = $DIC->language();
        $lng->loadLanguageModule("scormtrac");
        $cols = array();
        $udh = self::userDataHeaderForExport();
        $a_cols = explode(
            ',',
            'lm_id,lm_title,' . $udh["cols"]
            . ',Status,satisfied,measure,c_raw,c_min,c_max,completion_status,progress_measure'
        );
        $a_true = explode(',', $udh["default"] . ",lm_title,Status,satisfied,completion_status");
        for ($i = 0;$i < count($a_cols);$i++) {
            $cols[$a_cols[$i]] = array("txt" => $lng->txt($a_cols[$i]),"default" => false);
        }
        for ($i = 0;$i < count($a_true);$i++) {
            $cols[$a_true[$i]]["default"] = true;
        }
        return $cols;
    }
 
    public function exportObjGlobalToSystem($a_user = array(), $allowExportPrivacy = false, $obj_id, $lmTitle)
    {
        $ilDB = $this->db;
        $lng = $this->lng;
        $lng->loadLanguageModule("scormtrac");
        $returnData = array();
        $dbdata = array();
        $query = 'SELECT user_id, scope_id,
                                status,
                                satisfied,
                                measure,
                                score_raw as c_raw,
                                score_min as c_min,
                                score_max as c_max,
                                completion_status,
                                progress_measure
                                FROM cmi_gobjective 
                                WHERE scope_id = %s  
                                AND ' . $ilDB->in('user_id', $a_user, false, 'integer') . '
                                ORDER BY user_id, scope_id';
        $res = $ilDB->queryF($query, array('integer'), array($obj_id));
        while ($row = $ilDB->fetchAssoc($res)) {
            $dbdata[] = $row;
        }
        foreach ($dbdata as $data) {
            $data["lm_id"] = $data["scope_id"];
            $data["lm_title"] = $lmTitle;
            $data = array_merge($data, self::userDataArrayForExport($data["user_id"], $allowExportPrivacy));
            $data["Status"] = "" . $data["status"];
            $data["satisfied"] = "" . $data["satisfied"];
            $data["measure"] = "" . $data["measure"];
            $data["c_raw"] = "" . $data["c_raw"];
            $data["c_min"] = "" . $data["c_min"];
            $data["c_max"] = "" . $data["c_max"];
            $data["completion_status"] = "" . $data["completion_status"];
            $data["progress_measure"] = "" . $data["progress_measure"];
            $returnData[] = $data;
        }
        //              var_dump($returnData);
        return $returnData;
    }
 
    
    public static function tracInteractionItemColumns($b_orderBySCO, $b_allowExportPrivacy)
    {
        global $DIC;
 
        $lng = $DIC->language();
        $lng->loadLanguageModule("scormtrac");
        $cols = array();
        $a_cols = explode(
            ',',
            'lm_id,lm_title,cp_node_id,sco_marked_for_learning_progress,sco_title'
            . ',id,description,counter_all'
            . ',counter_correct,counter_correct_percent'
            . ',counter_incorrect,counter_incorrect_percent'
            . ',counter_other,counter_other_percent'
        );
        $a_true = explode(',', "sco_title,description,counter_correct,counter_incorrect");
        for ($i = 0;$i < count($a_cols);$i++) {
            $cols[$a_cols[$i]] = array("txt" => $lng->txt($a_cols[$i]),"default" => false);
        }
        for ($i = 0;$i < count($a_true);$i++) {
            $cols[$a_true[$i]]["default"] = true;
        }
        return $cols;
    }
 
    public function tracInteractionItem($a_user = array(), $a_sco = array(), $b_orderBySCO = false, $allowExportPrivacy = false, $obj_id, $lmTitle)
    {
        $ilDB = $this->db;
        $lng = $this->lng;
        $lng->loadLanguageModule("scormtrac");
 
        $returnData = array();
 
        $scoTitles = self::scoTitlesForExportSelected($obj_id);
 
        $scoProgress = self::markedLearningStatusForExportSelected($scoTitles, $obj_id);
 
        $a_correct = array();
        $a_incorrect = array();
        $query = 'SELECT cmi_node.cp_node_id, cmi_interaction.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.cp_node_id,cmi_interaction.id';
 
        $res = $ilDB->queryF($query, array('text'), array('correct'));
        while ($row = $ilDB->fetchAssoc($res)) {
            $a_correct[$row['cp_node_id'] . ':' . $row['id']] = $row['counter'];
        }
 
        $res = $ilDB->queryF($query, array('text'), array('incorrect'));
        while ($row = $ilDB->fetchAssoc($res)) {
            $a_incorrect[$row['cp_node_id'] . ':' . $row['id']] = $row['counter'];
        }
 
        $dbdata = array();
        $query = 'SELECT cmi_node.cp_node_id, cmi_interaction.id, cmi_interaction.description, count(*) as counter_all
                                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
                                GROUP BY cmi_node.cp_node_id,cmi_interaction.id,cmi_interaction.description';
        $res = $ilDB->query($query);
        while ($row = $ilDB->fetchAssoc($res)) {
            $dbdata[] = $row;
        }
        foreach ($dbdata as $data) {
            $skey = $data["cp_node_id"] . ':' . $data["id"];
            $all = $data["counter_all"];
            $correct = 0;
            if ($a_correct[$skey] != null) {
                $correct = $a_correct[$skey];
            }
            $incorrect = 0;
            if ($a_incorrect[$skey] != null) {
                $incorrect = $a_incorrect[$skey];
            }
            $other = $all - ($correct + $incorrect);
            $data["lm_id"] = $obj_id;
            $data["lm_title"] = $lmTitle;
            $data["sco_marked_for_learning_progress"] = $scoProgress[$data["cp_node_id"]];
            $data["sco_title"] = $scoTitles[$data["cp_node_id"]];
            //                  $data["id"] = "".$data["id"];
            $data["description"] = "" . $data["description"];
            //                  $data["counter_all"] = $data["counter"];
            $data["counter_correct"] = $correct;
            $data["counter_correct_percent"] = $correct * 100 / $all;
            $data["counter_incorrect"] = $incorrect;
            $data["counter_incorrect_percent"] = $incorrect * 100 / $all;
            $data["counter_other"] = $other;
            $data["counter_other_percent"] = $other * 100 / $all;
            $returnData[] = $data;
        }
        return $returnData;
    }
    
    public static function tracInteractionUserColumns($b_orderBySCO, $b_allowExportPrivacy)
    {
        global $DIC;
 
        $lng = $DIC->language();
        $lng->loadLanguageModule("scormtrac");
        // default fields
        $cols = array();
        $udh = self::userDataHeaderForExport();
        $a_cols = explode(
            ',',
            'lm_id,lm_title,cp_node_id,sco_marked_for_learning_progress,sco_title,' . $udh["cols"]
            . ',counter_i_correct,counter_i_correct_percent'
            . ',counter_i_incorrect,counter_i_incorrect_percent'
            . ',counter_i_other,counter_i_other_percent'
            . ',audio_captioning,audio_level,completion_status,completion_threshold,credit,delivery_speed'
            . ',c_entry,c_exit,c_language,c_location,c_mode,progress_measure,c_max,c_min,c_raw,scaled'
            . ',scaled_passing_score,session_time,session_time_seconds,success_status,total_time,total_time_seconds,c_timestamp,suspend_data,launch_data'
        );
        $a_true = explode(',', $udh["default"] . ',sco_title'
            . ',counter_i_correct,counter_i_correct_percent'
            . ',counter_i_incorrect,counter_i_incorrect_percent'
            . ',counter_i_other,counter_i_other_percent'
            . ',c_raw,scaled');
        for ($i = 0;$i < count($a_cols);$i++) {
            $cols[$a_cols[$i]] = array("txt" => $lng->txt($a_cols[$i]),"default" => false);
        }
        for ($i = 0;$i < count($a_true);$i++) {
            $cols[$a_true[$i]]["default"] = true;
        }
        return $cols;
    }
 
    public function tracInteractionUser($a_user = array(), $a_sco = array(), $b_orderBySCO = false, $allowExportPrivacy = false, $obj_id, $lmTitle)
    {
        $ilDB = $this->db;
        $lng = $this->lng;
        $lng->loadLanguageModule("scormtrac");
 
        $returnData = array();
 
        $scoTitles = self::scoTitlesForExportSelected($obj_id);
 
        $scoProgress = self::markedLearningStatusForExportSelected($scoTitles, $obj_id);
 
        $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"] = $obj_id;
            $data["lm_title"] = $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;
    }
 
 
    public static function tracInteractionUserAnswersColumns($a_user = array(), $a_sco = array(), $b_orderBySCO, $b_allowExportPrivacy)
    {
        global $DIC;
 
        $lng = $DIC->language();
        $ilDB = $DIC->database();
        $lng->loadLanguageModule("scormtrac");
        // default fields
        $cols = array();
        $a_interaction = array();
        $a_interactionDescription = array();
        $dbdata = array();
        $query = 'SELECT cmi_node.cp_node_id,
                                cmi_interaction.cmi_interaction_id, 
                                cmi_interaction.id, 
                                cmi_interaction.description 
                                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
                                ORDER BY ';
        if ($b_orderBySCO) {
            $query .= 'cmi_node.cp_node_id, cmi_node.user_id';
        } else {
            $query .= 'cmi_node.user_id, cmi_node.cp_node_id';
        }
        $query .= ', cmi_interaction.cmi_interaction_id, cmi_interaction.id';
 
        $res = $ilDB->query($query);
        while ($row = $ilDB->fetchAssoc($res)) {
            $dbdata[] = $row;
        }
        foreach ($dbdata as $data) {
            $key = $data["cp_node_id"] . ':' . $data["id"];
            $exist = false;
            for ($i = 0;$i < count($a_interaction);$i++) {
                if ($a_interaction[$i] == $key) {
                    $exist = true;
                }
            }
            if ($exist == false) {
                $a_interaction[] = $key;
            }
            if ($a_interactionDescription[$key] == null) {
                $a_interactionDescription[$key] = "" . $data["description"];
            }
        }
        $udh = self::userDataHeaderForExport();
        $a_cols = explode(',', 'lm_id,lm_title,cp_node_id,sco_marked_for_learning_progress,sco_title,' . $udh["cols"]);
        $a_true = explode(',', $udh["default"] . ",sco_title");
        for ($i = 0;$i < count($a_cols);$i++) {
            $cols[$a_cols[$i]] = array("txt" => $lng->txt($a_cols[$i]),"default" => false);
        }
        for ($i = 0;$i < count($a_true);$i++) {
            $cols[$a_true[$i]]["default"] = true;
        }
        for ($i = 0;$i < count($a_interaction);$i++) {
            //                  $cols["interaction_id".$i] = array("txt" => $lng->txt("interaction_id").' '.$i,"default" => false);
            //                  if ($a_interactionDescription[$a_interaction[$i]] != "") {
            //                          $cols["interaction_description".$i] = array("txt" => $lng->txt("interaction_description").' '.$i,"default" => false);
            //                  }
            //                  $cols["interaction_value".$i] = array("txt" => $lng->txt("interaction_value").' '.$i,"default" => true);//$a_interactionDescription[$a_interaction[$i]]
            $cols["interaction_value" . $i . " " . $a_interactionDescription[$a_interaction[$i]]] = array("txt" => sprintf($lng->txt("interaction_value"), $i) . " " . $a_interactionDescription[$a_interaction[$i]],"default" => true);
        }
        return $cols;
    }
 
    public function tracInteractionUserAnswers($a_user = array(), $a_sco = array(), $b_orderBySCO = false, $allowExportPrivacy = false, $obj_id, $lmTitle)
    {
        $ilDB = $this->db;
        $lng = $this->lng;
        $lng->loadLanguageModule("scormtrac");
 
        $returnData = array();
 
        $scoTitles = self::scoTitlesForExportSelected($obj_id);
 
        $scoProgress = self::markedLearningStatusForExportSelected($scoTitles, $obj_id);
 
        $a_interaction = array();
        $a_interactionId = array();
        $a_interactionDescription = array();
        $a_interactionUser = array();
        $dbdata = array();
        $query = 'SELECT cmi_node.user_id, cmi_node.cp_node_id,
                                cmi_interaction.cmi_interaction_id, 
                                cmi_interaction.id, 
                                cmi_interaction.result, 
                                cmi_interaction.description 
                                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
                                ORDER BY ';
        if ($b_orderBySCO) {
            $query .= 'cmi_node.cp_node_id, cmi_node.user_id';
        } else {
            $query .= 'cmi_node.user_id, cmi_node.cp_node_id';
        }
        $query .= ', cmi_interaction.cmi_interaction_id, cmi_interaction.id';
        $res = $ilDB->query($query);
        while ($row = $ilDB->fetchAssoc($res)) {
            $dbdata[] = $row;
        }
        foreach ($dbdata as $data) {
            $key = $data["cp_node_id"] . ':' . $data["id"];
            $exist = false;
            for ($i = 0;$i < count($a_interaction);$i++) {
                if ($a_interaction[$i] == $key) {
                    $exist = true;
                }
            }
            // if ($exist==false) $a_interaction[] = $key;
            // if ($a_interactionId[$key]==null) $a_interactionId[$key] = "".$data["id"];
            // if ($a_interactionDescription[$key]==null) $a_interactionDescription[$key] = "".$data["description"];
            if ($exist == false) {
                $a_interaction[] = $key;
                $a_interactionId[$key] = "" . $data["id"];
                $a_interactionDescription[$key] = "" . $data["description"];
            }
            $key .= ':' . $data["user_id"];
            $a_interactionUser[$key] = "" . $data["result"];
        }
        //              var_dump($a_interactionUser);
 
        $dbdata = array();
        $query = 'SELECT user_id, cp_node_id '
            . 'FROM cmi_node '
            . 'WHERE ' . $ilDB->in('cp_node_id', $a_sco, false, 'integer') . ' '
            . 'AND ' . $ilDB->in('user_id', $a_user, false, 'integer') . ' '
            // . 'GROUP BY user_id '
            . '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) {
            for ($i = 0;$i < count($a_interaction);$i++) {
                // $data["interaction_id".$i] = $a_interactionId[$a_interaction[$i]];
                // $data["interaction_description".$i] = $a_interactionDescription[$a_interaction[$i]];
                // $data["interaction_value".$i] = "";
                // $ukey=$a_interaction[$i].':'.$data["user_id"];
                // if ($a_interactionUser[$ukey] != null) $data["interaction_value".$i] = $a_interactionUser[$ukey];
                $intdesc = "interaction_value" . $i . " " . $a_interactionDescription[$a_interaction[$i]];
                $data[$intdesc] = "";
                $ukey = $a_interaction[$i] . ':' . $data["user_id"];
                if ($a_interactionUser[$ukey] != null) {
                    $data[$intdesc] = $a_interactionUser[$ukey];
                }
            }
            $data["lm_id"] = $obj_id;
            $data["lm_title"] = $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"]];
            $returnData[] = $data;
        }
        //              var_dump($returnData);
        return $returnData;
    }
    
 
    public function exportSelectedSuccess($a_user = array(), $allowExportPrivacy = false, $obj_id, $lmTitle)
    {
        $ilDB = $this->db;
 
        $scoCounter = 0;
        $query = 'SELECT count(distinct(cp_node.cp_node_id)) counter '
            . 'FROM cp_node, cp_resource, cp_item '
            . 'WHERE cp_item.cp_node_id = cp_node.cp_node_id '
            . 'AND cp_item.resourceid = cp_resource.id AND scormtype = %s '
            . 'AND nodename = %s AND cp_node.slm_id = %s';
        $res = $ilDB->queryF(
            $query,
            array('text', 'text', 'integer'),
            array('sco', 'item', $obj_id)
        );
        while ($row = $ilDB->fetchAssoc($res)) {
            $scoCounter = $row['counter'];
        }
 
        $u_startedSCO = array();
        $u_completedSCO = array();
        $u_passedSCO = array();
        for ($i = 0; $i < count($a_user); $i++) {
            $u_startedSCO[$a_user[$i]] = 0;
            $u_completedSCO[$a_user[$i]] = 0;
            $u_passedSCO[$a_user[$i]] = 0;
        }
 
        $query = 'SELECT user_id, count(*) counter '
            . 'FROM cmi_node, cp_node '
            . 'WHERE cmi_node.cp_node_id = cp_node.cp_node_id '
            . 'AND cp_node.slm_id = %s '
            . 'AND ' . $ilDB->in('user_id', $a_user, false, 'integer') . ' '
            . 'GROUP BY user_id';
        $res = $ilDB->queryF(
            $query,
            array('integer'),
            array($obj_id)
        );
        while ($data = $ilDB->fetchAssoc($res)) {
            $u_startedSCO[$data['user_id']] = $data['counter'];
        }
 
        $query = 'SELECT user_id, count(*) counter '
            . 'FROM cmi_node, cp_node '
            . 'WHERE cmi_node.cp_node_id = cp_node.cp_node_id '
            . 'AND cp_node.slm_id = %s '
            . "AND cmi_node.completion_status = 'completed' "
            . 'AND ' . $ilDB->in('user_id', $a_user, false, 'integer') . ' '
            . 'GROUP BY user_id';
        $res = $ilDB->queryF(
            $query,
            array('integer'),
            array($obj_id)
        );
        while ($data = $ilDB->fetchAssoc($res)) {
            $u_completedSCO[$data['user_id']] = $data['counter'];
        }
 
        $query = 'SELECT user_id, count(*) counter '
            . 'FROM cmi_node, cp_node '
            . 'WHERE cmi_node.cp_node_id = cp_node.cp_node_id '
            . 'AND cp_node.slm_id = %s '
            . "AND cmi_node.success_status = 'passed' "
            . 'AND ' . $ilDB->in('user_id', $a_user, false, 'integer') . ' '
            . 'GROUP BY user_id';
        $res = $ilDB->queryF(
            $query,
            array('integer'),
            array($obj_id)
        );
        while ($data = $ilDB->fetchAssoc($res)) {
            $u_passedSCO[$data['user_id']] = $data['counter'];
        }
 
        $dbdata = array();
 
        $query = 'SELECT * FROM sahs_user WHERE obj_id = ' . $ilDB->quote($obj_id, 'integer')
            . ' AND ' . $ilDB->in('user_id', $a_user, false, 'integer')
            . ' ORDER BY user_id';
        $res = $ilDB->query($query);
        while ($row = $ilDB->fetchAssoc($res)) {
            $dbdata[] = $row;
        }
        
        return self::exportSelectedSuccessRows($a_user, $allowExportPrivacy, $dbdata, $scoCounter, $u_startedSCO, $u_completedSCO, $u_passedSCO, $obj_id, $lmTitle);
        //CertificateDate?
    }
}