class.ilObjSCORMLearningModule.php

Go to the documentation of this file.

<?php

/* Copyright (c) 1998-2010 ILIAS open source, Extended GPL, see docs/LICENSE */

require_once "./Services/Object/classes/class.ilObject.php";
require_once "./Modules/ScormAicc/classes/class.ilObjSCORMValidator.php";
require_once "./Modules/ScormAicc/classes/class.ilObjSAHSLearningModule.php";

class ilObjSCORMLearningModule extends ilObjSAHSLearningModule
{
        var $validator;

        function ilObjSCORMLearningModule($a_id = 0, $a_call_by_reference = true)
        {
                $this->type = "sahs";
                parent::ilObject($a_id,$a_call_by_reference);
        }


        function validate($directory)
        {
                $this->validator = new ilObjSCORMValidator($directory);
                $returnValue = $this->validator->validate();
                return $returnValue;
        }

        function getValidationSummary()
        {
                if(is_object($this->validator))
                {
                        return $this->validator->getSummary();
                }
                return "";
        }

        function getTrackingItems()
        {
                return ilObjSCORMLearningModule::_getTrackingItems($this->getId());
        }


        function _getTrackingItems($a_obj_id)
        {
                include_once("./Modules/ScormAicc/classes/SCORM/class.ilSCORMTree.php");
                $tree = new ilSCORMTree($a_obj_id);
                $root_id = $tree->readRootId();

                $items = array();
                $childs = $tree->getSubTree($tree->getNodeData($root_id));

                foreach($childs as $child)
                {
                        if($child["c_type"] == "sit")
                        {
                                include_once("./Modules/ScormAicc/classes/SCORM/class.ilSCORMItem.php");
                                $sc_item =& new ilSCORMItem($child["obj_id"]);
                                if ($sc_item->getIdentifierRef() != "")
                                {
                                        $items[count($items)] =& $sc_item;
                                }
                        }
                }

                return $items;
        }

        function readObject()
        {
                global $ilErr;
                
                $needs_convert = false;

                // convert imsmanifest.xml file in iso to utf8 if needed

                $manifest_file = $this->getDataDirectory()."/imsmanifest.xml";

                // check if manifestfile exists and space left on device...
                $check_for_manifest_file = is_file($manifest_file);

                // if no manifestfile
                if (!$check_for_manifest_file)
                {
                        $this->ilias->raiseError($this->lng->txt("Manifestfile $manifest_file not found!"), $this->ilias->error_obj->MESSAGE);
                        return;
                }

                if ($check_for_manifest_file)
                {
                        $manifest_file_array = file($manifest_file);
                        foreach($manifest_file_array as $mfa)
                        {
                                // if (seems_not_utf8($mfa))
                                if (@iconv('UTF-8', 'UTF-8', $mfa) != $mfa) 
                                {
                                        $needs_convert = true;
                                        break;
                                }
                        }

                        // to copy the file we need some extraspace, counted in bytes *2 ... we need 2 copies....
                        $estimated_manifest_filesize = filesize($manifest_file) * 2;
                        
                        // i deactivated this, because it seems to fail on some windows systems (see bug #1795)
                        //$check_disc_free = disk_free_space($this->getDataDirectory()) - $estimated_manifest_filesize;
                        $check_disc_free = 2;
                }

                // if $manifest_file needs to be converted to UTF8
                if ($needs_convert)
                {
                        // if file exists and enough space left on device
                        if ($check_for_manifest_file && ($check_disc_free > 1))
                        {

                                // create backup from original
                                if (!copy($manifest_file, $manifest_file.".old"))
                                {
                                        echo "Failed to copy $manifest_file...<br>\n";
                                }

                                // read backupfile, convert each line to utf8, write line to new file
                                // php < 4.3 style
                                $f_write_handler = fopen($manifest_file.".new", "w");
                                $f_read_handler = fopen($manifest_file.".old", "r");
                                while (!feof($f_read_handler))
                                {
                                        $zeile = fgets($f_read_handler);
                                        //echo mb_detect_encoding($zeile);
                                        fputs($f_write_handler, utf8_encode($zeile));
                                }
                                fclose($f_read_handler);
                                fclose($f_write_handler);

                                // copy new utf8-file to imsmanifest.xml
                                if (!copy($manifest_file.".new", $manifest_file))
                                {
                                        echo "Failed to copy $manifest_file...<br>\n";
                                }

                                if (!@is_file($manifest_file))
                                {
                                        $this->ilias->raiseError($this->lng->txt("cont_no_manifest"),
                                        $this->ilias->error_obj->WARNING);
                                }
                        }
                        else
                        {
                                // gives out the specific error

                                if (!($check_disc_free > 1))
                                        $this->ilias->raiseError($this->lng->txt("Not enough space left on device!"),$this->ilias->error_obj->MESSAGE);
                                        return;
                        }

                }
                else
                {
                        // check whether file starts with BOM (that confuses some sax parsers, see bug #1795)
                        $hmani = fopen($manifest_file, "r");
                        $start = fread($hmani, 3);
                        if (strtolower(bin2hex($start)) == "efbbbf")
                        {
                                $f_write_handler = fopen($manifest_file.".new", "w");
                                while (!feof($hmani))
                                {
                                        $n = fread($hmani, 900);
                                        fputs($f_write_handler, $n);
                                }
                                fclose($f_write_handler);
                                fclose($hmani);

                                // copy new utf8-file to imsmanifest.xml
                                if (!copy($manifest_file.".new", $manifest_file))
                                {
                                        echo "Failed to copy $manifest_file...<br>\n";
                                }
                        }
                        else
                        {
                                fclose($hmani);
                        }
                }

                //validate the XML-Files in the SCORM-Package
                if ($_POST["validate"] == "y")
                {
                        if (!$this->validate($this->getDataDirectory()))
                        {
                                $ilErr->raiseError("<b>Validation Error(s):</b><br>".$this->getValidationSummary(),$ilErr->MESSAGE);
                        }
                }

                // start SCORM package parser
                include_once ("./Modules/ScormAicc/classes/SCORM/class.ilSCORMPackageParser.php");
                // todo determine imsmanifest.xml path here...
                $slmParser = new ilSCORMPackageParser($this, $manifest_file);
                $slmParser->startParsing();
                return $slmParser->getPackageTitle();
        }

        function setLearningProgressSettingsAtUpload()
        {
                global $ilSetting;
                //condition 1
                $lm_set = new ilSetting("lm");
                if ($lm_set->get('scorm_lp_auto_activate') != 1) return;
                //condition 2
                include_once("./Services/Tracking/classes/class.ilObjUserTracking.php");
                if (ilObjUserTracking::_enabledLearningProgress() == false) return; 
                
                //set Learning Progress to Automatic by Collection of SCORM Items
                include_once("./Services/Tracking/classes/class.ilLPObjSettings.php");
                $lm_set = new ilLPObjSettings($this->getId());
                $lm_set->setMode(ilLPObjSettings::LP_MODE_SCORM);
                $lm_set->insert();
                
                //select all SCOs as relevant for Learning Progress
                include_once("Services/Tracking/classes/collection/class.ilLPCollectionOfSCOs.php");
                $collection = new ilLPCollectionOfSCOs($this->getId(), ilLPObjSettings::LP_MODE_SCORM);
                $scos = array();
                foreach($collection->getPossibleItems() as $sco_id => $item)
                {
                        $scos[] = $sco_id;
                }
                $collection->activateEntries($scos);
        }
        function getTrackedItems()
        {
                global $ilDB, $ilUser;
                
                $sco_set = $ilDB->queryF('
                SELECT DISTINCT sco_id FROM scorm_tracking WHERE obj_id = %s', 
                array('integer'),array($this->getId()));

                $items = array();
                while($sco_rec = $ilDB->fetchAssoc($sco_set))
                {
                        include_once("./Modules/ScormAicc/classes/SCORM/class.ilSCORMItem.php");
                        $sc_item =& new ilSCORMItem($sco_rec["sco_id"]);
                        if ($sc_item->getIdentifierRef() != "")
                        {
                                $items[count($items)] =& $sc_item;
                        }
                }

                return $items;
        }
        
        public static function _lookupLastAccess($a_obj_id, $a_usr_id)
        {
                global $ilDB;

                $result = $ilDB->queryF('
                SELECT last_access FROM sahs_user 
                WHERE  obj_id = %s
                AND user_id = %s',
                array('integer','integer'), array($a_obj_id,$a_usr_id));
                
                if ($ilDB->numRows($result))
                {
                        $row = $ilDB->fetchAssoc($result);
                        return $row["last_access"];
                }
                return "";
        }

        function getTrackedUsers($a_search)
        {
                global $ilDB, $ilUser;
//TODO: UK last_access is not correct if no Commit or last_visited_sco
//              $query = 'SELECT user_id,MAX(c_timestamp) last_access, lastname, firstname FROM scorm_tracking st ' .
                $query = 'SELECT user_id, last_access, lastname, firstname FROM sahs_user st ' .
                        'JOIN usr_data ud ON st.user_id = ud.usr_id ' .
                        'WHERE obj_id = ' . $ilDB->quote($this->getId(), 'integer');
                if($a_search) {
//                      $query .= ' AND (' . $ilDB->like('lastname', 'text', '%' . $a_search . '%') . ' OR ' . $ilDB->like('firstname', 'text', '%' . $a_search . '%') .')';
                        $query .= ' AND ' . $ilDB->like('lastname', 'text', '%' . $a_search . '%');
                }
                $query .= ' GROUP BY user_id, lastname, firstname, last_access';
                $sco_set = $ilDB->query($query);

                $items = array();
                while($sco_rec = $ilDB->fetchAssoc($sco_set))
                {
                        $items[] = $sco_rec;
                }
                return $items;
        }


        public function getAttemptsForUsers()
        {
                global $ilDB;
                $query = 'SELECT user_id, package_attempts FROM sahs_user WHERE obj_id = ' . $ilDB->quote($this->getId(), 'integer') . ' ';
                $res = $ilDB->query($query);

                $attempts = array();
                while($row = $res->fetchRow(DB_FETCHMODE_ASSOC))
                {
                        $attempts[$row['user_id']] = (int) $row['package_attempts'];
                }
                return $attempts;
        }


        function getAttemptsForUser($a_user_id){
                global $ilDB;
                $val_set = $ilDB->queryF('SELECT package_attempts FROM sahs_user WHERE obj_id = %s AND user_id = %s',
                array('integer','integer'),
                array($this->getId(),$a_user_id,0));

                $val_rec = $ilDB->fetchAssoc($val_set);
                
                if ($val_rec["package_attempts"] == null) {
                        $val_rec["package_attempts"]="";
                }
                return $val_rec["package_attempts"];
        }


        public function getModuleVersionForUsers()
        {
                global $ilDB;
                $query = 'SELECT user_id, module_version FROM sahs_user WHERE obj_id = ' . $ilDB->quote($this->getId(), 'integer') . ' ';
                $res = $ilDB->query($query);

                $versions = array();
                while($row = $res->fetchRow(DB_FETCHMODE_ASSOC))
                {
                        $versions[$row['user_id']] = (int) $row['module_version'];
                }
                return $versions;
        }


        function getModuleVersionForUser($a_user_id){
                global $ilDB;
                $val_set = $ilDB->queryF('SELECT module_version FROM sahs_user WHERE obj_id = %s AND user_id = %s',
                array('integer','integer'),
                array($this->getId(),$a_user_id,0));

                $val_rec = $ilDB->fetchAssoc($val_set);
                
                if ($val_rec["module_version"] == null) {
                        $val_rec["module_version"]="";
                }
                return $val_rec["module_version"];
        }

        function getTrackingDataPerUser($a_sco_id, $a_user_id)
        {
                global $ilDB;

                $data_set = $ilDB->queryF('
                SELECT * FROM scorm_tracking 
                WHERE user_id = %s
                AND sco_id = %s
                AND obj_id = %s
                ORDER BY lvalue',
                array('integer','integer','integer'),
                array($a_user_id,$a_sco_id,$this->getId()));
                        
                $data = array();
                while($data_rec = $ilDB->fetchAssoc($data_set)) {
                        $data[] = $data_rec;
                }

                return $data;
        }

        function getTrackingDataAgg($a_user_id)
        {
                global $ilDB;

                // get all users with any tracking data
                $sco_set = $ilDB->queryF('
                SELECT DISTINCT sco_id FROM scorm_tracking 
                WHERE obj_id = %s
                AND user_id = %s
                AND sco_id <> %s',
                array('integer','integer','integer'),
                array($this->getId(),$a_user_id,0));

                $data = array();
                while($sco_rec = $ilDB->fetchAssoc($sco_set))           
                {
                        $data_set = $ilDB->queryF('
                        SELECT * FROM scorm_tracking 
                        WHERE  obj_id = %s
                        AND sco_id = %s
                        AND user_id = %s 
                        AND lvalue <> %s
                        AND (lvalue = %s
                                OR lvalue = %s
                                OR lvalue = %s)',
                        array('integer','integer','integer','text','text','text','text'),
                        array($this->getId(),
                                $sco_rec["sco_id"],
                                $a_user_id,
                                "package_attempts",
                                "cmi.core.lesson_status",
                                "cmi.core.total_time",
                                "cmi.core.score.raw")
                        );
                        
                        $score = $time = $status = "";
                        
                        while($data_rec = $ilDB->fetchAssoc($data_set))
                        {
                                switch($data_rec["lvalue"])
                                {
                                        case "cmi.core.lesson_status":
                                                $status = $data_rec["rvalue"];
                                                break;

                                        case "cmi.core.total_time":
                                                $time = $data_rec["rvalue"];
                                                break;

                                        case "cmi.core.score.raw":
                                                $score = $data_rec["rvalue"];
                                                break;
                                }
                        }
                        //create sco_object
                        include_once './Modules/ScormAicc/classes/SCORM/class.ilSCORMItem.php';
                        $sc_item =& new ilSCORMItem($sco_rec["sco_id"]);
                        $data[] = array("sco_id"=>$sco_rec["sco_id"], "title" => $sc_item->getTitle(),
                        "score" => $score, "time" => $time, "status" => $status);
                                
                }
                return (array) $data;
        }

        function getTrackingDataAggSco($a_sco_id)
            {
                global $ilDB;

                // get all users with any tracking data
                $user_set = $ilDB->queryF('
                SELECT DISTINCT user_id FROM scorm_tracking 
                WHERE obj_id = %s
                AND sco_id = %s',
                array('integer','integer'),
                array($this->getId(),$a_sco_id));

                $data = array();
                while($user_rec = $ilDB->fetchAssoc($user_set))
                {

                    $data_set = $ilDB->queryF('
                    SELECT * FROM scorm_tracking 
                    WHERE obj_id = %s
                    AND sco_id = %s
                    AND user_id = %s
                    AND (lvalue = %s
                    OR lvalue = %s
                    OR lvalue = %s)',
                                array('integer','integer','integer','text','text','text'),
                                array($this->getId(),
                                        $a_sco_id,
                                        $user_rec["user_id"],
                                        "cmi.core.lesson_status",
                                        "cmi.core.total_time",
                                        "cmi.core.score.raw")
                                );
                                
                        $score = $time = $status = "";
                        
                    while($data_rec = $ilDB->fetchAssoc($data_set))
                    {
                        switch($data_rec["lvalue"])
                        {
                            case "cmi.core.lesson_status":
                                $status = $data_rec["rvalue"];
                                break;

                            case "cmi.core.total_time":
                                $time = $data_rec["rvalue"];
                                break;

                            case "cmi.core.score.raw":
                                $score = $data_rec["rvalue"];
                                break;
                        }
                    }

                    $data[] = array("user_id" => $user_rec["user_id"],
                        "score" => $score, "time" => $time, "status" => $status);
                }

                return $data;
            }   
        


        public function exportSelected($a_all, $a_users = array())
        {
                global $ilDB, $ilUser;
                include_once('./Modules/ScormAicc/classes/class.ilSCORMTrackingItems.php');
                include_once("./Services/Tracking/classes/class.ilLearningProgressBaseGUI.php");
                include_once('./Services/PrivacySecurity/classes/class.ilPrivacySettings.php');
                $privacy = ilPrivacySettings::_getInstance();
                $allowExportPrivacy = $privacy->enabledExportSCORM();

                $csv = "";
                $query = 'SELECT * FROM sahs_user WHERE obj_id = %s';
                if (count($a_users) >0) $query .= ' AND '.$ilDB->in('user_id', $a_users, false, 'integer');
                $res = $ilDB->queryF(
                        $query,
                        array('integer'),
                        array($this->getId())
                );
                while ($data = $ilDB->fetchAssoc($res)) {
                        $csv = $csv. $data["obj_id"]
                                . ";\"" . $this->getTitle() ."\""
                                . ";" . $data["module_version"]
                                . ";\"" . implode("\";\"",ilSCORMTrackingItems::userDataArrayForExport($data["user_id"], $allowExportPrivacy)) ."\""
                                . ";\"" . $data["last_access"] ."\""
                                . ";\"" . ilLearningProgressBaseGUI::__readStatus($data["obj_id"],$data["user_id"]) ."\"" //not $data["status"] because modifications to learning progress could have made before export
                                . ";" . $data["package_attempts"]
                                . ";" . $data["percentage_completed"]
                                . ";" . $data["sco_total_time_sec"]
//                              . ";\"" . $certificateDate ."\""
                                . "\n";
                }
                $udh=ilSCORMTrackingItems::userDataHeaderForExport();
                $header = "LearningModuleId;LearningModuleTitle;LearningModuleVersion;".str_replace(',',';',$udh["cols"]).";"
                                . "LastAccess;Status;Attempts;percentageCompletedSCOs;SumTotal_timeSeconds\n";

                $this->sendExportFile($header, $csv);
        }
        
        
        function importTrackingData($a_file)
        {
                global $ilDB, $ilUser;
                
                $error = 0;
                //echo file_get_contents($a_file);
                $method = null;
                
                //lets import
                $fhandle = fopen($a_file, "r");
                
                //the top line is the field names
                $fields = fgetcsv($fhandle, pow(2, 16), ';');
                //lets check the import method
                fclose($fhandle);
           
                switch($fields[0])
                {
                        case "Scoid": 
                        case "SCO-Identifier":
                                $error = $this->importRaw($a_file);
                                break;
                        case "Department":
                        case "LearningModuleId":
                                $error = $this->importSuccess($a_file);
                                break;
                        default:
                                return -1;
                                break;
                }
                return $error;
        }

        function importSuccess($a_file) {
                
                global $ilDB, $ilUser;
                include_once("./Services/Tracking/classes/class.ilLPStatus.php");
                $scos = array();
                //get all SCO's of this object ONLY RELEVANT!
                include_once './Services/Object/classes/class.ilObjectLP.php';
                $olp = ilObjectLP::getInstance($this->getId());
                $collection = $olp->getCollectionInstance();
                if($collection)
                {               
                        $scos = $collection->getItems();
                }
                
                $fhandle = fopen($a_file, "r");

                $obj_id = $this->getID();
                $fields = fgetcsv($fhandle, pow(2, 16), ';');
                $users = array();
                $usersToDelete = array();
                while(($csv_rows = fgetcsv($fhandle, pow(2, 16), ";")) !== FALSE)
                {
                        $data = array_combine($fields, $csv_rows);
                        //no check the format - sufficient to import users
                        if ($data["Login"]) $user_id = $this->get_user_id($data["Login"]);
                        if ($data["login"]) $user_id = $this->get_user_id($data["login"]);
                        //add mail in future
                        if ($data["user"] && is_int($data["user"])) $user_id = $data["user"];
                        if ($user_id>0) {
                                        
                                $last_access = ilUtil::now();
                                if ($data['Date']) {
                                        $date_ex = explode('.', $data['Date']);
                                        $last_access = implode('-', array($date_ex[2], $date_ex[1], $date_ex[0]));
                                }
                                if ($data['LastAccess']) {
                                        $last_access = $data['LastAccess'];
                                }
                                
                                $status = ilLPStatus::LP_STATUS_COMPLETED_NUM;
                                
                                if ($data["Status"]) {
                                        if (is_int($data["Status"])) $status = $data["Status"];
                                        else if ($data["Status"] == "0" || $data["Status"] == "1" || $data["Status"] == "2" || $data["Status"] == "3") $status = (int) $data["Status"];
                                        else if ($data["Status"] == ilLPStatus::LP_STATUS_NOT_ATTEMPTED) $status = ilLPStatus::LP_STATUS_NOT_ATTEMPTED_NUM;
                                        else if ($data["Status"] == ilLPStatus::LP_STATUS_IN_PROGRESS) $status = ilLPStatus::LP_STATUS_IN_PROGRESS_NUM;
                                        else if ($data["Status"] == ilLPStatus::LP_STATUS_FAILED) $status = ilLPStatus::LP_STATUS_FAILED_NUM;
                                }

                                $attempts = null;
                                if($data["Attempts"]) $attempts = $data["Attempts"];
                                
                                $percentage_completed = 0;
                                if ($status == ilLPStatus::LP_STATUS_COMPLETED_NUM) $percentage_completed = 100;
                                if ($data['percentageCompletedSCOs']) $percentage_completed = $data['percentageCompletedSCOs'];

                                $sco_total_time_sec = null;
                                if ($data['SumTotal_timeSeconds']) $sco_total_time_sec = $data['SumTotal_timeSeconds'];
                                
                                if ($status == ilLPStatus::LP_STATUS_NOT_ATTEMPTED) {
                                        $usersToDelete[] = $user_id;
                                } else {
                                        $this->importSuccessForSahsUser($user_id, $last_access, $status, $attempts, $percentage_completed, $sco_total_time_sec);
                                        $users[] = $user_id;
                                }
                                
                                if ($status == ilLPStatus::LP_STATUS_COMPLETED_NUM) {
                                        foreach ($scos as $sco_id) 
                                        {
                                                $statement = $ilDB->queryF('
                                                        SELECT * FROM scorm_tracking 
                                                        WHERE user_id = %s
                                                        AND sco_id = %s 
                                                        AND lvalue = %s
                                                        AND obj_id = %s',
                                                        array('integer','integer','text','integer'),
                                                        array($user_id, $sco_id, 'cmi.core.lesson_status',$obj_id)
                                                );
                                                if($ilDB->numRows($statement) > 0)
                                                {
                                                        $ilDB->update('scorm_tracking',
                                                                array(
                                                                        'rvalue'                => array('clob', 'completed'),
                                                                        'c_timestamp'   => array('timestamp', $last_access)
                                                                ),
                                                                array(
                                                                        'user_id'               => array('integer', $user_id),
                                                                        'sco_id'                => array('integer', $sco_id),
                                                                        'lvalue'                => array('text', 'cmi.core.lesson_status'),
                                                                        'obj_id'                => array('integer', $obj_id)
                                                                )
                                                        );
                                                }
                                                else
                                                {
                                                        $ilDB->insert('scorm_tracking', array(
                                                                'obj_id'                => array('integer', $obj_id),
                                                                'user_id'               => array('integer', $user_id),
                                                                'sco_id'                => array('integer', $sco_id),
                                                                'lvalue'                => array('text', 'cmi.core.lesson_status'),
                                                                'rvalue'                => array('clob', 'completed'),
                                                                'c_timestamp'   => array('timestamp', $last_access)
                                                        ));
                                                }                                                       
                                        }
                                }
                        } else {
                                //echo "Warning! User $csv_rows[0] does not exist in ILIAS. Data for this user was skipped.\n";
                        }
                }
                if (count($usersToDelete)>0) {
                        // include_once("./Services/Tracking/classes/class.ilLPMarks.php");
                        // ilLPMarks::_deleteForUsers($this->getId(), $usersToDelete);
                        $this->deleteTrackingDataOfUsers($usersToDelete);
                }
                include_once("./Services/Tracking/classes/class.ilLPStatusWrapper.php");
                ilLPStatusWrapper::_refreshStatus($this->getId(),$users);
                return 0;
        }

        function importSuccessForSahsUser($user_id, $last_access, $status, $attempts=null, $percentage_completed=null, $sco_total_time_sec=null){
                global $ilDB;
                $statement = $ilDB->queryF('SELECT * FROM sahs_user WHERE obj_id = %s AND user_id = %s',
                                                        array('integer','integer'),
                                                        array($this->getID(),$user_id)
                                                );
                if($ilDB->numRows($statement) > 0)
                {
                        $ilDB->update('sahs_user',
                                array(
                                        'last_access'   => array('timestamp', $last_access),
                                        'status'        => array('integer', $status),
                                        'package_attempts'      => array('integer', $attempts),
                                        'percentage_completed'  => array('integer', $percentage_completed),
                                        'sco_total_time_sec'    => array('integer', $sco_total_time_sec)
                                ),
                                array(
                                        'obj_id'                => array('integer', $this->getID()),
                                        'user_id'               => array('integer', $user_id)
                                )
                        );
                }
                else
                {
                        $ilDB->insert('sahs_user', array(
                                'obj_id'                => array('integer', $this->getID()),
                                'user_id'               => array('integer', $user_id),
                                'last_access'   => array('timestamp', $last_access),
                                'status'        => array('integer', $status),
                                'package_attempts'      => array('integer', $attempts),
                                'percentage_completed'  => array('integer', $percentage_completed),
                                'sco_total_time_sec'    => array('integer', $sco_total_time_sec)
                        ));
                }                                                       
        }

        private function parseUserId($il_id)
        {
                global $ilSetting;

                $parts = explode('_', $il_id);

                if(!count((array) $parts))
                {
                        return 0;
                }
                if(!isset($parts[2]) or !isset($parts[3]))
                {
                        return 0;
                }
                if($parts[2] != $ilSetting->get('inst_id',$parts[2]))
                {
                        return 0;
                }
                return $parts[3];
        }

        private function importRaw($a_file)
        {
                global $ilDB, $ilUser,$lng;
                $lng->loadLanguageModule("scormtrac");

                $fhandle = fopen($a_file, "r");

                $fields = fgetcsv($fhandle, pow(2, 16), ';');
                $users = array();
                $a_last_access = array();
                $a_time = array();
                $a_package_attempts = array();
                $a_module_version = array();
                while(($csv_rows = fgetcsv($fhandle, pow(2, 16), ";")) !== FALSE)
                {
                        $data = array_combine($fields, $csv_rows);
                        if ($data['Userid']) {
                                $user_id = $this->parseUserId($data['Userid']);
                        } 
                        else if ($data[$lng->txt("user")]) 
                        {
                                if (is_int($data[$lng->txt("user")])) $user_id = $data[$lng->txt("user")];
                        }
                        if ($data[$lng->txt("login")])
                        {
                                $user_id = $this->get_user_id($data[$lng->txt("login")]);
                        }
                        if(!$user_id)
                        {
                                continue;
                        }

                        if ($data['Scoid'])
                        {
                                $il_sco_id = $this->lookupSCOId($data['Scoid']);
                        }
                        if ($data[$lng->txt("identifierref")])
                        {
                                $il_sco_id = $this->lookupSCOId($data[$lng->txt("identifierref")]);
                        }
                        if(!$il_sco_id)
                        {
                                continue;
                        }

                        $c_timestamp="";
                        if ($data['Timestamp'])
                        {
                                $c_timestamp = $data['Timestamp'];
                        }
                        if ($data[$lng->txt("c_timestamp")])
                        {
                                $c_timestamp = $data[$lng->txt("c_timestamp")];
                        }
                        if ($c_timestamp == "")
                        {
                                $date = new DateTime();
                                $c_timestamp = $date->getTimestamp();
                        } else {
                                if($a_last_access[$user_id]) {
                                        if ($a_last_access[$user_id] < $c_timestamp) $a_last_access[$user_id] = $c_timestamp;
                                } else {
                                        $a_last_access[$user_id] = $c_timestamp;
                                }
                        }
                        
                        if(!$data['Key'])
                        {
                                continue;
                        }
                        if(!$data['Value'])
                        {
                                $data['Value'] = "";
                        }

                        if($data['Key'] == "cmi.core.total_time" && $data['Value'] != "") {
                                $tarr = explode(":", $data['Value']);
                                $sec = (int) $tarr[2] + (int) $tarr[1] * 60 +
                                        (int) substr($tarr[0], strlen($tarr[0]) - 3) * 60 * 60;
                                if ($a_time[$user_id]) $a_time[$user_id] += $sec;
                                else $a_time[$user_id] = $sec;
                        }
                        //do the actual import
                        if($il_sco_id > 0)
                        {
                                $statement = $ilDB->queryF('
                                        SELECT * FROM scorm_tracking 
                                        WHERE user_id = %s
                                        AND sco_id = %s 
                                        AND lvalue = %s
                                        AND obj_id = %s',
                                                array('integer', 'integer', 'text', 'integer'),
                                                array($user_id, $il_sco_id, $data['Key'], $this->getID())
                                );
                                if($ilDB->numRows($statement) > 0)
                                {
                                        $ilDB->update('scorm_tracking',
                                                array(
                                                        'rvalue' => array('clob', $data['Value']),
                                                        'c_timestamp' => array('timestamp', $c_timestamp)
                                                ),
                                                array(
                                                        'user_id' => array('integer', $user_id),
                                                        'sco_id' => array('integer', $il_sco_id),
                                                        'lvalue' => array('text', $data['Key']),
                                                        'obj_id' => array('integer', $this->getId())
                                                )
                                        );
                                }
                                else
                                {
                                        $ilDB->insert('scorm_tracking', array(
                                                'obj_id' => array('integer', $this->getId()),
                                                'user_id' => array('integer', $user_id),
                                                'sco_id' => array('integer', $il_sco_id),
                                                'lvalue' => array('text', $data['Key']),
                                                'rvalue' => array('clob', $data['Value']),
                                                'c_timestamp' => array('timestamp', $data['Timestamp'])
                                        ));
                                }
                        }
                        // $package_attempts = 1;
                        if($il_sco_id == 0)
                        {
                                if ($data['Key'] == "package_attempts") $a_package_attempts[$user_id] = $data['Value'];
                                // if ($data['Key'] == "module_version") $a_module_version[$user_id] = $data['Value'];
                        }
                        if (!in_array($user_id,$users)) $users[] = $user_id;
                }
                fclose($fhandle);

                //UK determineStatus, percentage_completed and syncGlobalStatus
                include_once './Services/Tracking/classes/class.ilLPStatusWrapper.php';
                ilLPStatusWrapper::_refreshStatus($this->getId(),$users);
                
                // include_once './Services/Tracking/classes/status/class.ilLPStatusSCORM.php';
                include_once './Services/Tracking/classes/class.ilLPStatus.php';
                foreach ($users as $user_id){
                        $attempts = 1;
                        if ($a_package_attempts[$user_id]) $attempts = $a_package_attempts[$user_id];
                        // $module_version = 1;
                        // if ($a_module_version[$user_id]) $module_version = $a_module_version[$user_id];
                        $sco_total_time_sec = null;
                        if ($a_time[$user_id]) $sco_total_time_sec = $a_time[$user_id];
                        $last_access = null;
                        if ($a_last_access[$user_id]) $last_access = $a_last_access[$user_id];
                        // $status = ilLPStatusWrapper::_determineStatus($this->getId(),$user_id);
                        $status = ilLPStatus::_lookupStatus($this->getId(),$user_id);
                        // $percentage_completed = ilLPStatusSCORM::determinePercentage($this->getId(),$user_id);
                        $percentage_completed = ilLPStatus::_lookupPercentage($this->getId(),$user_id);

                        $this->importSuccessForSahsUser($user_id, $last_access, $status, $attempts, $percentage_completed, $sco_total_time_sec);
                        
                }

                return 0;
        }


        function decreaseAttemptsForUser($a_user_id) {
                global $ilDB;
                
                foreach ($a_user_id as $user)
                {
                        //first check if there is a package_attempts entry
                        $val_set = $ilDB->queryF('SELECT package_attempts FROM sahs_user WHERE user_id = %s AND obj_id = %s',
                        array('integer','integer'),
                        array($user,$this->getID()));
                        
                        $val_rec = $ilDB->fetchAssoc($val_set);
                        
                        if ($val_rec["package_attempts"] != null && $val_rec["package_attempts"] != 0) 
                        {
                                $new_rec = 0;
                                //decrease attempt by 1
                                if ((int)$val_rec["package_attempts"] > 0) $new_rec = (int)$val_rec["package_attempts"]-1;
                                $ilDB->manipulateF('UPDATE sahs_user SET package_attempts = %s WHERE user_id = %s AND obj_id = %s',
                                        array('integer','integer','integer'),
                                        array($new_rec,$user,$this->getID()));

                                //following 2 lines were before 4.4 only for SCORM 1.2
                                include_once("./Services/Tracking/classes/class.ilLPStatusWrapper.php");
                                ilLPStatusWrapper::_updateStatus($this->getId(), $user);
                        }
                }
        }

        
        //helper function
        function get_user_id($a_login) {
                global $ilDB, $ilUser;
                
                $val_set = $ilDB->queryF('SELECT * FROM usr_data WHERE(login=%s)',
                array('text'),array($a_login));
                $val_rec = $ilDB->fetchAssoc($val_set);
                
                if (count($val_rec)>0) {
                        return $val_rec['usr_id'];
                } else {
                        return null;
                }
        }
        
        
        private function lookupSCOId($a_referrer){
                global $ilDB, $ilUser;
                
                //non specific SCO entries
                if ($a_referrer=="0") {
                        return 0;
                }

                $val_set = $ilDB->queryF('
                SELECT obj_id FROM sc_item,scorm_tree 
                WHERE (obj_id = child 
                AND identifierref = %s 
                AND slm_id = %s)',
                array('text','integer'), array($a_referrer,$this->getID()));
                $val_rec = $ilDB->fetchAssoc($val_set);
                
                return $val_rec["obj_id"];
        }
        
        function getUserIdEmail($a_mail)
        {
                global $ilDB, $ilUser;
                
                $val_set = $ilDB->queryF('SELECT usr_id FROM usr_data WHERE(email=%s)',
                array('text'),array($a_mail));
                $val_rec = $ilDB->fetchAssoc($val_set);
                                
                
                return $val_rec["usr_id"];
        }
        
        
        function sendExportFile($a_header,$a_content)
        {
                $timestamp = time();
                $refid = $this->getRefId();
                $filename = "scorm_tracking_".$refid."_".$timestamp.".csv";
                //Header
                header("Expires: 0");
                header("Cache-control: private");
                header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
                header("Content-Description: File Transfer");
                header("Content-Type: application/octet-stream");
                header("Content-disposition: attachment; filename=$filename");
                echo $a_header.$a_content;
                exit;   
        }
        
        public static function _getAllScoIds($a_id)
        {
                global $ilDB;
                
                $scos = array();

                $val_set = $ilDB->queryF('
                SELECT scorm_object.obj_id,
                                scorm_object.title,
                                scorm_object.c_type,
                                scorm_object.slm_id,
                                scorm_object.obj_id scoid 
                FROM scorm_object,sc_item,sc_resource 
                WHERE(scorm_object.slm_id = %s
                AND scorm_object.obj_id = sc_item.obj_id 
                AND sc_item.identifierref = sc_resource.import_id 
                AND sc_resource.scormtype = %s)
                GROUP BY scorm_object.obj_id,
                                scorm_object.title,
                                scorm_object.c_type,
                                scorm_object.slm_id,
                                scorm_object.obj_id ',
                array('integer', 'text'),
                array($a_id,'sco'));

                while ($val_rec = $ilDB->fetchAssoc($val_set)) 
                {
                        array_push($scos,$val_rec['scoid']);
                }
                return $scos;
        }
        
        public static function _getStatusForUser($a_id, $a_user,$a_allScoIds,$a_numerical=false)
        {
                global $ilDB, $lng;
                
                $scos = $a_allScoIds;
                //check if all SCO's are completed
                $scos_c = implode(',',$scos);

                $val_set = $ilDB->queryF('
                SELECT * FROM scorm_tracking 
                WHERE (user_id = %s
                AND obj_id = %s
                AND '.$ilDB->in('sco_id', $scos, false, 'integer').'
                AND ((lvalue = %s AND '.$ilDB->like('rvalue', 'clob', 'completed').') 
                        OR (lvalue = %s AND '.$ilDB->like('rvalue', 'clob', 'passed').')))',
                array('integer','integer','text','text'),
                array($a_user,$a_id,'cmi.core.lesson_status', 'cmi.core.lesson_status'));       
                while ($val_rec = $ilDB->fetchAssoc($val_set))
                {
                        $key = array_search($val_rec['sco_id'], $scos); 
                        unset ($scos[$key]);
                }
                //check for completion
                if (count($scos) == 0) {
                        $completion = ($a_numerical===true)  ? true: $lng->txt("cont_complete");
                }       
                if (count($scos) > 0) {
                        $completion = ($a_numerical===true)  ? false: $lng->txt("cont_incomplete");
                }
                return $completion;
        }

        public static function _getCourseCompletionForUser($a_id, $a_user) 
        {
                return ilObjSCORMLearningModule::_getStatusForUser($a_id, $a_user, ilObjSCORMLearningModule::_getAllScoIds($a_id), true);
        }

        function getAllScoIds(){
                global $ilDB;
                
                $scos = array();
                //get all SCO's of this object
                $val_set = $ilDB->queryF('
                SELECT scorm_object.obj_id,
                                scorm_object.title,
                                scorm_object.c_type,
                                scorm_object.slm_id,
                                scorm_object.obj_id scoid 
                FROM scorm_object, sc_item,sc_resource 
                WHERE(scorm_object.slm_id = %s 
                        AND scorm_object.obj_id = sc_item.obj_id 
                        AND sc_item.identifierref = sc_resource.import_id 
                        AND sc_resource.scormtype = %s )
                GROUP BY scorm_object.obj_id,
                scorm_object.title,
                scorm_object.c_type,
                scorm_object.slm_id,
                scorm_object.obj_id',
                array('integer','text'), 
                array($this->getId(),'sco'));

                while ($val_rec = $ilDB->fetchAssoc($val_set))
                {
                        array_push($scos,$val_rec['scoid']);
                }
                return $scos;
        }
        
        function getStatusForUser($a_user,$a_allScoIds,$a_numerical=false){
                global $ilDB;
                $scos = $a_allScoIds;
                //loook up status
                //check if all SCO's are completed
                $scos_c = implode(',',$scos); 

                $val_set = $ilDB->queryF('
                SELECT sco_id FROM scorm_tracking 
                WHERE (user_id = %s
                        AND obj_id = %s
                        AND '.$ilDB->in('sco_id', $scos, false, 'integer').'
                 AND ((lvalue = %s AND '.$ilDB->like('rvalue', 'clob', 'completed').') OR (lvalue =  %s AND '.$ilDB->like('rvalue', 'clob', 'passed').') ) )',
                array('integer','integer','text','text',), 
                array($a_user,$this->getID(),'cmi.core.lesson_status','cmi.core.lesson_status'));       
                while ($val_rec = $ilDB->fetchAssoc($val_set))  
                {
                        $key = array_search($val_rec['sco_id'], $scos); 
                        unset ($scos[$key]);
                }               
                //check for completion
                if (count($scos) == 0) {
                        $completion = ($a_numerical===true)  ? true: $this->lng->txt("cont_complete");
                }       
                if (count($scos) > 0) {
                        $completion = ($a_numerical===true)  ? false: $this->lng->txt("cont_incomplete");
                }
                return $completion;
        }
        
        function getCourseCompletionForUser($a_user) {
                return $this->getStatusForUser($a_user,$this->getAllScoIds,true);
        }
        
        //to be called from IlObjUser
        public static function _removeTrackingDataForUser($user_id) {
                global $ilDB;
                //gobjective
                $ilDB->manipulateF(
                        'DELETE FROM scorm_tracking WHERE user_id = %s',
                        array('integer'),
                        array($user_id)
                );
                $ilDB->manipulateF(
                        'DELETE FROM sahs_user WHERE user_id = %s',
                        array('integer'),
                        array($user_id)
                );
        }
        
        function _getScoresForUser($a_item_id, $a_user_id)
        {
                global $ilDB;

                $retAr = array("raw" => null, "max" => null, "scaled" => null);
                $val_set = $ilDB->queryF("
                        SELECT lvalue, rvalue FROM scorm_tracking 
                        WHERE sco_id = %s 
                        AND user_id =  %s
                        AND (lvalue = 'cmi.core.score.raw' OR lvalue = 'cmi.core.score.max')",
                        array('integer', 'integer'),
                        array($a_item_id, $a_user_id)
                );
                while ($val_rec = $ilDB->fetchAssoc($val_set))
                {
                        if ($val_rec['lvalue'] == "cmi.core.score.raw") $retAr["raw"] = $val_rec["rvalue"];
                        if ($val_rec['lvalue'] == "cmi.core.score.max") $retAr["max"] = $val_rec["rvalue"];
                }
                if ($retAr["raw"] != null && $retAr["max"] != null) $retAr["scaled"] = ($retAr["raw"] / $retAr["max"]);

                return $retAr;
        }


        public function getLastVisited($user_id)
        {
                global $ilDB;
                $val_set = $ilDB->queryF('SELECT last_visited FROM sahs_user WHERE obj_id = %s AND user_id = %s',
                        array('integer','integer'), 
                        array($this->getID(),$user_id)
                );
                while ($val_rec = $ilDB->fetchAssoc($val_set))
                {
                        if ($val_rec["last_visited"] != null) return "".$val_rec["last_visited"];
                }
                return '0';
        }

        function deleteTrackingDataOfUsers($a_users)
        {
                global $ilDB;
                include_once("./Services/Tracking/classes/class.ilChangeEvent.php");
                include_once("./Services/Tracking/classes/class.ilLPStatusWrapper.php");

                ilChangeEvent::_deleteReadEventsForUsers($this->getId(), $a_users);

                foreach($a_users as $user)
                {
                        $ilDB->manipulateF('
                                DELETE FROM scorm_tracking
                                WHERE user_id = %s
                                AND obj_id = %s',
                                array('integer', 'integer'),
                                array($user, $this->getID()));

                        $ilDB->manipulateF('
                                DELETE FROM sahs_user
                                WHERE user_id = %s
                                AND obj_id = %s',
                                array('integer', 'integer'),
                                array($user, $this->getID()));

                        ilLPStatusWrapper::_updateStatus($this->getId(), $user);
                }
        }

}
?>