class.ilBasicSkill.php

Go to the documentation of this file.

<?php
 
/* Copyright (c) 1998-2013 ILIAS open source, Extended GPL, see docs/LICENSE */
 
include_once("./Services/Skill/classes/class.ilSkillTreeNode.php");
include_once("./Services/Skill/interfaces/interface.ilSkillUsageInfo.php");
 
class ilBasicSkill extends ilSkillTreeNode implements ilSkillUsageInfo
{
        const ACHIEVED = 1;
        const NOT_ACHIEVED = 0;
 
        const EVAL_BY_OTHERS_= 0;
        const EVAL_BY_SELF = 1;
        const EVAL_BY_ALL = 2;
 
        var $id;
 
        function __construct($a_id = 0)
        {
                parent::__construct($a_id);
                $this->setType("skll");
        }
 
        function read()
        {
                parent::read();
        }
 
        function create()
        {
                parent::create();
        }
 
        function delete()
        {
                global $ilDB;
 
                $ilDB->manipulate("DELETE FROM skl_level WHERE "
                        ." skill_id = ".$ilDB->quote($this->getId(), "integer")
                        );
 
                $ilDB->manipulate("DELETE FROM skl_user_has_level WHERE "
                        ." skill_id = ".$ilDB->quote($this->getId(), "integer")
                        );
 
                parent::delete();
        }
 
        function copy()
        {
                $skill = new ilBasicSkill();
                $skill->setTitle($this->getTitle());
                $skill->setType($this->getType());
                $skill->setSelfEvaluation($this->getSelfEvaluation());
                $skill->setOrderNr($this->getOrderNr());
                $skill->create();
 
                $levels = $this->getLevelData();
                if (sizeof($levels))
                {
                        foreach($levels as $item)
                        {
                                $skill->addLevel($item["title"], $item["description"]);
                        }
                }
                $skill->update();
                
                return $skill;
        }
 
        //
        //
        // Skill level related methods
        //
        //
 
        function addLevel($a_title, $a_description, $a_import_id = "")
        {
                global $ilDB;
 
                $nr = $this->getMaxLevelNr();
                $nid = $ilDB->nextId("skl_level");
                $ilDB->insert("skl_level", array(
                                "id" => array("integer", $nid),
                                "skill_id" => array("integer", $this->getId()),
                                "nr" => array("integer", $nr+1),
                                "title" => array("text", $a_title),
                                "description" => array("clob", $a_description),
                                "import_id" => array("text", $a_import_id),
                                "creation_date" => array("timestamp", ilUtil::now())
                        ));
 
        }
 
        function getMaxLevelNr()
        {
                global $ilDB;
 
                $set = $ilDB->query("SELECT MAX(nr) mnr FROM skl_level WHERE ".
                        " skill_id = ".$ilDB->quote($this->getId(), "integer")
                        );
                $rec = $ilDB->fetchAssoc($set);
                return (int) $rec["mnr"];
        }
 
        function getLevelData($a_id = 0)
        {
                global $ilDB;
 
                if ($a_id > 0)
                {
                        $and = " AND id = ".$ilDB->quote($a_id, "integer");
                }
 
                $set = $ilDB->query("SELECT * FROM skl_level WHERE ".
                        " skill_id = ".$ilDB->quote($this->getId(), "integer").
                        $and.
                        " ORDER BY nr"
                        );
                $levels = array();
                while ($rec = $ilDB->fetchAssoc($set))
                {
                        if ($a_id > 0)
                        {
                                return $rec;
                        }
                        $levels[] = $rec;
                }
                return $levels;
        }
 
        protected static function lookupLevelProperty($a_id, $a_prop)
        {
                global $ilDB;
 
                $set = $ilDB->query("SELECT $a_prop FROM skl_level WHERE ".
                        " id = ".$ilDB->quote($a_id, "integer")
                );
                $rec = $ilDB->fetchAssoc($set);
                return $rec[$a_prop];
        }
 
        static function lookupLevelTitle($a_id)
        {
                return ilBasicSkill::lookupLevelProperty($a_id, "title");
        }
 
        static function lookupLevelDescription($a_id)
        {
                return ilBasicSkill::lookupLevelProperty($a_id, "description");
        }
 
        static function lookupLevelSkillId($a_id)
        {
                return ilBasicSkill::lookupLevelProperty($a_id, "skill_id");
        }
 
        static protected function writeLevelProperty($a_id, $a_prop, $a_value, $a_type)
        {
                global $ilDB;
 
                $ilDB->update("skl_level", array(
                        $a_prop => array($a_type, $a_value),
                        ), array(
                        "id" => array("integer", $a_id),
                ));
        }
 
        static function writeLevelTitle($a_id, $a_title)
        {
                ilBasicSkill::writeLevelProperty($a_id, "title", $a_title, "text");
        }
 
        static function writeLevelDescription($a_id, $a_description)
        {
                ilBasicSkill::writeLevelProperty($a_id, "description", $a_description, "clob");
        }
 
        function updateLevelOrder($order)
        {
                global $ilDB;
 
                asort($order);
 
                $cnt = 1;
                foreach ($order as $id => $o)
                {
                        $ilDB->manipulate("UPDATE skl_level SET ".
                                " nr = ".$ilDB->quote($cnt, "integer").
                                " WHERE id = ".$ilDB->quote($id, "integer")
                                );
                        $cnt++;
                }
        }
 
        function deleteLevel($a_id)
        {
                global $ilDB;
 
                $ilDB->manipulate("DELETE FROM skl_level WHERE "
                        ." id = ".$ilDB->quote($a_id, "integer")
                        );
 
        }
 
        function fixLevelNumbering()
        {
                global $ilDB;
 
                $set = $ilDB->query("SELECT id, nr FROM skl_level WHERE ".
                        " skill_id = ".$ilDB->quote($this->getId(), "integer").
                        " ORDER BY nr ASC"
                );
                $cnt = 1;
                while ($rec = $ilDB->fetchAssoc($set))
                {
                        $ilDB->manipulate("UPDATE skl_level SET ".
                                " nr = ".$ilDB->quote($cnt, "integer").
                                " WHERE id = ".$ilDB->quote($rec["id"], "integer")
                                );
                        $cnt++;
                }
        }
 
        function getSkillForLevelId($a_level_id)
        {
                global $ilDB;
 
                $set = $ilDB->query("SELECT * FROM skl_level WHERE ".
                        " id = ".$ilDB->quote($a_level_id, "integer")
                        );
                $skill = null;
                if ($rec = $ilDB->fetchAssoc($set))
                {
                        if (ilSkillTreeNode::isInTree($rec["skill_id"]))
                        {
                                $skill = new ilBasicSkill($rec["skill_id"]);
                        }
                }
                return $skill;
        }
 
        //
        //
        // User skill (level) related methods
        //
        //
 
 
        static function resetUserSkillLevelStatus($a_user_id, $a_skill_id, $a_tref_id = 0, $a_trigger_ref_id = 0, $a_self_eval = false)
        {
                global $DIC;
 
                $db = $DIC->database();
 
                if (!$a_self_eval)
                {
                        include_once("./Services/Skill/exceptions/class.ilSkillException.php");
                        throw new ilSkillException("resetUserSkillLevelStatus currently only provided for self evaluations.");
                }
 
                $trigger_obj_id = ($a_trigger_ref_id > 0)
                        ? ilObject::_lookupObjId($a_trigger_ref_id)
                        : 0;
 
                $update = false;
                $status_date = self::hasRecentSelfEvaluation($a_user_id, $a_skill_id, $a_tref_id, $a_trigger_ref_id);
                if ($status_date != "")
                {
                        $update = true;
                }
 
                if ($update)
                {
                        // this will only be set in self eval case, means this will always have a $rec
                        $now = ilUtil::now();
                        $db->manipulate("UPDATE skl_user_skill_level SET ".
                                " level_id = ".$db->quote(0, "integer").",".
                                " status_date = ".$db->quote($now, "timestamp").
                                " WHERE user_id = ".$db->quote($a_user_id, "integer").
                                " AND status_date = ".$db->quote($status_date, "timestamp").
                                " AND skill_id = ".$db->quote($a_skill_id, "integer").
                                " AND status = ".$db->quote(self::ACHIEVED, "integer").
                                " AND trigger_obj_id = ".$db->quote($trigger_obj_id, "integer").
                                " AND tref_id = ".$db->quote((int) $a_tref_id, "integer").
                                " AND self_eval = ".$db->quote($a_self_eval, "integer")
                        );
                }
                else
                {
                        $now = ilUtil::now();
                        $db->manipulate("INSERT INTO skl_user_skill_level ".
                                "(level_id, user_id, tref_id, status_date, skill_id, status, valid, trigger_ref_id,".
                                "trigger_obj_id, trigger_obj_type, trigger_title, self_eval, unique_identifier) VALUES (".
                                $db->quote(0, "integer").",".
                                $db->quote($a_user_id, "integer").",".
                                $db->quote((int) $a_tref_id, "integer").",".
                                $db->quote($now, "timestamp").",".
                                $db->quote($a_skill_id, "integer").",".
                                $db->quote(self::ACHIEVED, "integer").",".
                                $db->quote(1, "integer").",".
                                $db->quote($a_trigger_ref_id, "integer").",".
                                $db->quote($trigger_obj_id, "integer").",".
                                $db->quote("", "text").",".
                                $db->quote("", "text").",".
                                $db->quote($a_self_eval, "integer").",".
                                $db->quote("", "text").
                                ")");
                }
 
                $db->manipulate("DELETE FROM skl_user_has_level WHERE "
                        ." user_id = ".$db->quote($a_user_id, "integer")
                        ." AND skill_id = ".$db->quote($a_skill_id, "integer")
                        ." AND tref_id = ".$db->quote((int) $a_tref_id, "integer")
                        ." AND trigger_obj_id = ".$db->quote($trigger_obj_id, "integer")
                        ." AND self_eval = ".$db->quote($a_self_eval, "integer")
                );
 
        }
 
        protected static function hasRecentSelfEvaluation($a_user_id, $a_skill_id, $a_tref_id = 0, $a_trigger_ref_id = 0)
        {
                global $DIC;
 
                $db = $DIC->database();
 
                $trigger_obj_id = ($a_trigger_ref_id > 0)
                        ? ilObject::_lookupObjId($a_trigger_ref_id)
                        : 0;
 
                $recent = "";
 
                $db->setLimit(1);
                $set = $db->query("SELECT * FROM skl_user_skill_level WHERE ".
                        "skill_id = ".$db->quote($a_skill_id, "integer")." AND ".
                        "user_id = ".$db->quote($a_user_id, "integer")." AND ".
                        "tref_id = ".$db->quote((int) $a_tref_id, "integer")." AND ".
                        "trigger_obj_id = ".$db->quote($trigger_obj_id, "integer")." AND ".
                        "self_eval = ".$db->quote(1, "integer").
                        " ORDER BY status_date DESC"
                );
                $rec = $db->fetchAssoc($set);
                $status_day = substr($rec["status_date"], 0, 10);
                $today = substr(ilUtil::now(), 0, 10);
                if ($rec["valid"] && $rec["status"] == ilBasicSkill::ACHIEVED && $status_day == $today)
                {
                        $recent = $rec["status_date"];
                }
 
                return $recent;
        }
        
 
        static function writeUserSkillLevelStatus($a_level_id, $a_user_id,
                $a_trigger_ref_id, $a_tref_id = 0, $a_status = ilBasicSkill::ACHIEVED, $a_force = false,
                $a_self_eval = false, $a_unique_identifier = "")
        {
                global $ilDB;
 
                $skill_id = ilBasicSkill::lookupLevelSkillId($a_level_id);
                $trigger_ref_id = $a_trigger_ref_id;
                $trigger_obj_id = ilObject::_lookupObjId($trigger_ref_id);
                $trigger_title = ilObject::_lookupTitle($trigger_obj_id);
                $trigger_type = ilObject::_lookupType($trigger_obj_id);
 
                $update = false;
 
                // self evaluations will update, if the last self evaluation is on the same day
                if ($a_self_eval && self::hasRecentSelfEvaluation($a_user_id, $skill_id, $a_tref_id, $trigger_ref_id))
                {
                        $status_date = self::hasRecentSelfEvaluation($a_user_id, $skill_id, $a_tref_id, $trigger_ref_id);
                        if ($status_date != "")
                        {
                                $update = true;
                        }
                }
 
                if ($update)
                {
                        // this will only be set in self eval case, means this will always have a $rec
                        $now = ilUtil::now();
                        $ilDB->manipulate("UPDATE skl_user_skill_level SET ".
                                " level_id = ".$ilDB->quote($a_level_id, "integer").",".
                                " status_date = ".$ilDB->quote($now, "timestamp").
                                " WHERE user_id = ".$ilDB->quote($a_user_id, "integer").
                                " AND status_date = ".$ilDB->quote($status_date, "timestamp").
                                " AND skill_id = ".$ilDB->quote($skill_id, "integer").
                                " AND status = ".$ilDB->quote($a_status, "integer").
                                " AND trigger_obj_id = ".$ilDB->quote($trigger_obj_id, "integer").
                                " AND tref_id = ".$ilDB->quote((int) $a_tref_id, "integer").
                                " AND self_eval = ".$ilDB->quote($a_self_eval, "integer")
                                );
                }
                else
                {
                        if ($a_unique_identifier != "")
                        {
                                $ilDB->manipulate("DELETE FROM skl_user_skill_level WHERE ".
                                        " user_id = ".$ilDB->quote($a_user_id, "integer").
                                        " AND tref_id = ".$ilDB->quote($a_tref_id, "integer").
                                        " AND skill_id = ".$ilDB->quote($skill_id, "integer").
                                        " AND trigger_ref_id = ".$ilDB->quote($trigger_ref_id, "integer").
                                        " AND trigger_obj_id = ".$ilDB->quote($trigger_obj_id, "integer").
                                        " AND self_eval = ".$ilDB->quote($a_self_eval, "integer").
                                        " AND unique_identifier = ".$ilDB->quote($a_unique_identifier, "text")
                                );
                        }
 
                        $now = ilUtil::now();
                        $ilDB->manipulate("INSERT INTO skl_user_skill_level ".
                                "(level_id, user_id, tref_id, status_date, skill_id, status, valid, trigger_ref_id,".
                                "trigger_obj_id, trigger_obj_type, trigger_title, self_eval, unique_identifier) VALUES (".
                                $ilDB->quote($a_level_id, "integer").",".
                                $ilDB->quote($a_user_id, "integer").",".
                                $ilDB->quote((int) $a_tref_id, "integer").",".
                                $ilDB->quote($now, "timestamp").",".
                                $ilDB->quote($skill_id, "integer").",".
                                $ilDB->quote($a_status, "integer").",".
                                $ilDB->quote(1, "integer").",".
                                $ilDB->quote($trigger_ref_id, "integer").",".
                                $ilDB->quote($trigger_obj_id, "integer").",".
                                $ilDB->quote($trigger_type, "text").",".
                                $ilDB->quote($trigger_title, "text").",".
                                $ilDB->quote($a_self_eval, "integer").",".
                                $ilDB->quote($a_unique_identifier, "text").
                                ")");
                }
 
                // fix (removed level_id and added skill id, since table should hold only
                // one entry per skill)
                $ilDB->manipulate("DELETE FROM skl_user_has_level WHERE "
                        ." user_id = ".$ilDB->quote($a_user_id, "integer")
                        ." AND skill_id = ".$ilDB->quote($skill_id, "integer")
                        ." AND tref_id = ".$ilDB->quote((int) $a_tref_id, "integer")
                        ." AND trigger_obj_id = ".$ilDB->quote($trigger_obj_id, "integer")
                        ." AND self_eval = ".$ilDB->quote($a_self_eval, "integer")
                );
 
                if ($a_status == ilBasicSkill::ACHIEVED)
                {
                        $ilDB->manipulate("INSERT INTO skl_user_has_level ".
                        "(level_id, user_id, tref_id, status_date, skill_id, trigger_ref_id, trigger_obj_id, trigger_obj_type, trigger_title, self_eval) VALUES (".
                        $ilDB->quote($a_level_id, "integer").",".
                        $ilDB->quote($a_user_id, "integer").",".
                        $ilDB->quote($a_tref_id, "integer").",".
                        $ilDB->quote($now, "timestamp").",".
                        $ilDB->quote($skill_id, "integer").",".
                        $ilDB->quote($trigger_ref_id, "integer").",".
                        $ilDB->quote($trigger_obj_id, "integer").",".
                        $ilDB->quote($trigger_type, "text").",".
                        $ilDB->quote($trigger_title, "text").",".
                        $ilDB->quote($a_self_eval, "integer").
                        ")");
                }
        }
 
        function getMaxLevelPerType($a_tref_id, $a_type, $a_user_id = 0, $a_self_eval = 0)
        {
                global $ilDB, $ilUser;
                
                if ($a_user_id == 0)
                {
                        $a_user_id = $ilUser->getId();
                }
                
                $set = $ilDB->query($q = "SELECT level_id FROM skl_user_has_level ".
                        " WHERE trigger_obj_type = ".$ilDB->quote($a_type, "text").
                        " AND skill_id = ".$ilDB->quote($this->getId(), "integer").
                        " AND tref_id = ".$ilDB->quote((int) $a_tref_id, "integer").
                        " AND user_id = ".$ilDB->quote($a_user_id, "integer").
                        " AND self_eval = ".$ilDB->quote($a_self_eval, "integer")
                        );
 
                $has_level = array();
                while ($rec = $ilDB->fetchAssoc($set))
                {
                        $has_level[$rec["level_id"]] = true;
                }
                $max_level = 0;
                foreach ($this->getLevelData() as $l)
                {
                        if (isset($has_level[$l["id"]]))
                        {
                                $max_level = $l["id"];
                        }
                }
                return $max_level;
        }
 
        function getAllLevelEntriesOfUser($a_tref_id, $a_user_id = 0, $a_self_eval = 0)
        {
                global $ilDB, $ilUser;
                
                if ($a_user_id == 0)
                {
                        $a_user_id = $ilUser->getId();
                }
                
                $set = $ilDB->query($q = "SELECT * FROM skl_user_has_level ".
                        " WHERE skill_id = ".$ilDB->quote($this->getId(), "integer").
                        " AND tref_id = ".$ilDB->quote((int) $a_tref_id, "integer").
                        " AND user_id = ".$ilDB->quote($a_user_id, "integer").
                        " AND self_eval = ".$ilDB->quote($a_self_eval, "integer").
                        " ORDER BY status_date DESC"
                        );
 
                $levels = array();
                while ($rec = $ilDB->fetchAssoc($set))
                {
                        $levels[] = $rec;
                }
                return $levels;
        }
 
        function getAllHistoricLevelEntriesOfUser($a_tref_id, $a_user_id = 0, $a_eval_by = 0)
        {
                global $ilDB, $ilUser;
 
                if ($a_user_id == 0)
                {
                        $a_user_id = $ilUser->getId();
                }
 
                $by = ($a_eval_by != self::EVAL_BY_ALL)
                        ? " AND self_eval = ".$ilDB->quote($a_self_eval, "integer")
                        : "";
 
                $set = $ilDB->query($q = "SELECT * FROM skl_user_skill_level ".
                                " WHERE skill_id = ".$ilDB->quote($this->getId(), "integer").
                                " AND tref_id = ".$ilDB->quote((int) $a_tref_id, "integer").
                                " AND user_id = ".$ilDB->quote($a_user_id, "integer").
                                $by.
                                " ORDER BY status_date DESC"
                );
                $levels = array();
                while ($rec = $ilDB->fetchAssoc($set))
                {
                        $levels[] = $rec;
                }
                return $levels;
        }
 
 
        function getMaxLevelPerObject($a_tref_id, $a_object_id, $a_user_id = 0, $a_self_eval = 0)
        {
                global $ilDB, $ilUser;
 
                if ($a_user_id == 0)
                {
                        $a_user_id = $ilUser->getId();
                }
 
                $set = $ilDB->query($q = "SELECT level_id FROM skl_user_has_level ".
                                " WHERE trigger_obj_id = ".$ilDB->quote($a_object_id, "integer").
                                " AND skill_id = ".$ilDB->quote($this->getId(), "integer").
                                " AND tref_id = ".$ilDB->quote((int) $a_tref_id, "integer").
                                " AND user_id = ".$ilDB->quote($a_user_id, "integer").
                                " AND self_eval = ".$ilDB->quote($a_self_eval, "integer")
                );
 
                $has_level = array();
                while ($rec = $ilDB->fetchAssoc($set))
                {
                        $has_level[$rec["level_id"]] = true;
                }
                $max_level = 0;
                foreach ($this->getLevelData() as $l)
                {
                        if (isset($has_level[$l["id"]]))
                        {
                                $max_level = $l["id"];
                        }
                }
                return $max_level;
        }
 
        function getLastLevelPerObject($a_tref_id, $a_object_id, $a_user_id = 0, $a_self_eval = 0)
        {
                global $ilDB, $ilUser;
 
                if ($a_user_id == 0)
                {
                        $a_user_id = $ilUser->getId();
                }
 
                $ilDB->setLimit(1);
                $set = $ilDB->query($q = "SELECT level_id FROM skl_user_has_level ".
                                " WHERE trigger_obj_id = ".$ilDB->quote($a_object_id, "integer").
                                " AND skill_id = ".$ilDB->quote($this->getId(), "integer").
                                " AND tref_id = ".$ilDB->quote((int) $a_tref_id, "integer").
                                " AND user_id = ".$ilDB->quote($a_user_id, "integer").
                                " AND self_eval = ".$ilDB->quote($a_self_eval, "integer").
                                " ORDER BY status_date DESC"
                );
 
                $rec = $ilDB->fetchAssoc($set);
 
                return $rec["level_id"];
        }
 
        function getLastUpdatePerObject($a_tref_id, $a_object_id, $a_user_id = 0, $a_self_eval = 0)
        {
                global $ilDB, $ilUser;
 
                if ($a_user_id == 0)
                {
                        $a_user_id = $ilUser->getId();
                }
 
                $ilDB->setLimit(1);
                $set = $ilDB->query($q = "SELECT status_date FROM skl_user_has_level ".
                                " WHERE trigger_obj_id = ".$ilDB->quote($a_object_id, "integer").
                                " AND skill_id = ".$ilDB->quote($this->getId(), "integer").
                                " AND tref_id = ".$ilDB->quote((int) $a_tref_id, "integer").
                                " AND user_id = ".$ilDB->quote($a_user_id, "integer").
                                " AND self_eval = ".$ilDB->quote($a_self_eval, "integer").
                                " ORDER BY status_date DESC"
                );
 
                $rec = $ilDB->fetchAssoc($set);
 
                return $rec["status_date"];
        }
 
        //
        //
        // Certificate related methods
        //
        //
 
        function getTitleForCertificate()
        {
                return $this->getTitle();
        }
 
        function getShortTitleForCertificate()
        {
                return "Skill";
        }
 
        public static function _lookupCertificate($a_skill_id, $a_skill_level_id)
        {
                $certificatefile = CLIENT_WEB_DIR."/certificates/skill/".
                        ((int)$a_skill_id)."/".((int) $a_skill_level_id)."/certificate.xml";
                if (@file_exists($certificatefile))
                {
                        return true;
                }
                else
                {
                        return false;
                }
        }
 
        static public function getUsageInfo($a_cskill_ids, &$a_usages)
        {
                global $ilDB;
                
                include_once("./Services/Skill/classes/class.ilSkillUsage.php");
                ilSkillUsage::getUsageInfoGeneric($a_cskill_ids, $a_usages, ilSkillUsage::USER_ASSIGNED,
                                "skl_user_skill_level", "user_id");
        }
 
        static function getCommonSkillIdForImportId($a_source_inst_id, $a_skill_import_id, $a_tref_import_id = 0)
        {
                global $ilDB;
 
                include_once("./Services/Skill/classes/class.ilSkillTree.php");
                include_once("./Services/Skill/classes/class.ilSkillTemplateReference.php");
                $tree = new ilSkillTree();
 
                if ($a_source_inst_id == 0)
                {
                        return array();
                }
 
                $template_ids = array();
                if ($a_tref_import_id > 0)
                {
                        $skill_node_type = "sktp";
 
                        // get all matching tref nodes
                        $set = $ilDB->query("SELECT * FROM skl_tree_node n JOIN skl_tree t ON (n.obj_id = t.child) ".
                                        " WHERE n.import_id = ".$ilDB->quote("il_".((int)$a_source_inst_id)."_sktr_".$a_tref_import_id, "text").
                                        " ORDER BY n.creation_date DESC ");
                        while ($rec = $ilDB->fetchAssoc($set))
                        {
                                if (($t = ilSkillTemplateReference::_lookupTemplateId($rec["obj_id"])) > 0)
                                {
                                        $template_ids[$t] = $rec["obj_id"];
                                }
                        }
                }
                else
                {
                        $skill_node_type = "skll";
                }
                $set = $ilDB->query("SELECT * FROM skl_tree_node n JOIN skl_tree t ON (n.obj_id = t.child) ".
                        " WHERE n.import_id = ".$ilDB->quote("il_".((int)$a_source_inst_id)."_".$skill_node_type."_".$a_skill_import_id, "text").
                        " ORDER BY n.creation_date DESC ");
                $results = array();
                while ($rec = $ilDB->fetchAssoc($set))
                {
                        $matching_trefs = array();
                        if ($a_tref_import_id > 0)
                        {
                                $skill_template_id = $tree->getTopParentNodeId($rec["obj_id"]);
 
                                // check of skill is in template
                                foreach ($template_ids as $templ => $tref)
                                {
                                        if ($skill_template_id == $templ)
                                        {
                                                $matching_trefs[] = $tref;
                                        }
                                }
                        }
                        else
                        {
                                $matching_trefs = array(0);
                        }
 
                        foreach ($matching_trefs as $t)
                        {
                                $results[] = array("skill_id" => $rec["obj_id"], "tref_id" => $t, "creation_date" => $rec["creation_date"]);
                        }
                }
                return $results;
        }
 
        static function getLevelIdForImportId($a_source_inst_id, $a_level_import_id)
        {
                global $ilDB;
 
                $set = $ilDB->query("SELECT * FROM skl_level l JOIN skl_tree t ON (l.skill_id = t.child) " .
                                " WHERE l.import_id = " . $ilDB->quote("il_" . ((int)$a_source_inst_id) . "_sklv_" . $a_level_import_id, "text") .
                                " ORDER BY l.creation_date DESC ");
                $results = array();
                while ($rec = $ilDB->fetchAssoc($set))
                {
                        $results[] = array("level_id" => $rec["id"], "creation_date" => $rec["creation_date"]);
                }
                return $results;
        }
 
        static function getLevelIdForImportIdMatchSkill($a_source_inst_id, $a_level_import_id, $a_skill_import_id, $a_tref_import_id = 0)
        {
                $level_id_data = self::getLevelIdForImportId($a_source_inst_id, $a_level_import_id);
                $skill_data = self::getCommonSkillIdForImportId($a_source_inst_id, $a_skill_import_id, $a_tref_import_id);
                $matches = array();
                foreach($level_id_data as $l)
                {
                        reset($skill_data);
                        foreach ($skill_data as $s)
                        {
                                if (ilBasicSkill::lookupLevelSkillId($l["level_id"]) == $s["skill_id"])
                                {
                                        $matches[] = array(
                                                        "level_id" => $l["level_id"],
                                                        "creation_date" => $l["creation_date"],
                                                        "skill_id" => $s["skill_id"],
                                                        "tref_id" => $s["tref_id"]
                                        );
                                }
                        }
                }
                return $matches;
        }
 
}
?>