00001 <?php
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011
00012
00013
00014
00015
00016
00017
00018
00019
00020
00021
00022
00023
00030 class ilObjSCORMTracking
00031 {
00036 function ilObjSCORMTracking()
00037 {
00038 global $ilias;
00039
00040 }
00041
00042 function extractData()
00043 {
00044 $this->insert = array();
00045 if (is_array($_GET["iL"]))
00046 {
00047 foreach($_GET["iL"] as $key => $value)
00048 {
00049 $this->insert[] = array("left" => $value, "right" => $_GET["iR"][$key]);
00050 }
00051 }
00052 if (is_array($_POST["iL"]))
00053 {
00054 foreach($_POST["iL"] as $key => $value)
00055 {
00056 $this->insert[] = array("left" => $value, "right" => $_POST["iR"][$key]);
00057 }
00058 }
00059
00060 $this->update = array();
00061 if (is_array($_GET["uL"]))
00062 {
00063 foreach($_GET["uL"] as $key => $value)
00064 {
00065 $this->update[] = array("left" => $value, "right" => $_GET["uR"][$key]);
00066 }
00067 }
00068 if (is_array($_POST["uL"]))
00069 {
00070 foreach($_POST["uL"] as $key => $value)
00071 {
00072 $this->update[] = array("left" => $value, "right" => $_POST["uR"][$key]);
00073 }
00074 }
00075 }
00076
00077 function store($obj_id=0, $sahs_id=0, $extractData=1)
00078 {
00079 global $ilDB, $ilUser;
00080
00081 if (empty($obj_id))
00082 {
00083 $obj_id = ilObject::_lookupObjId($_GET["ref_id"]);
00084 }
00085
00086 if (empty($sahs_id))
00087 $sahs_id = ($_GET["sahs_id"] != "") ? $_GET["sahs_id"] : $_POST["sahs_id"];
00088
00089 if ($extractData==1)
00090 $this->extractData();
00091
00092 if (is_object($ilUser))
00093 {
00094 $user_id = $ilUser->getId();
00095 }
00096
00097
00098 $f = fopen("content/scorm.log", "a");
00099 fwrite($f, "\nCALLING SCORM store()\n");
00100 if ($obj_id <= 1)
00101 {
00102 fwrite($f, "Error: No obj_id given.\n");
00103 }
00104 else
00105 {
00106 foreach($this->insert as $insert)
00107 {
00108 $q = "SELECT * FROM scorm_tracking WHERE ".
00109 " user_id = ".$ilDB->quote($user_id).
00110 " AND sco_id = ".$ilDB->quote($sahs_id).
00111 " AND lvalue = ".$ilDB->quote($insert["left"]);
00112 $set = $ilDB->query($q);
00113 if ($rec = $set->fetchRow(DB_FETCHMODE_ASSOC))
00114 {
00115 fwrite($f, "Error Insert, left value already exists. L:".$insert["left"].",R:".
00116 $insert["right"].",sahs_id:".$sahs_id.",user_id:".$user_id."\n");
00117 }
00118 else
00119 {
00120 $q = "INSERT INTO scorm_tracking (user_id, sco_id, obj_id, lvalue, rvalue) VALUES ".
00121 "(".$ilDB->quote($user_id).",".$ilDB->quote($sahs_id).",".
00122 $ilDB->quote($obj_id).",".
00123 $ilDB->quote($insert["left"]).",".$ilDB->quote($insert["right"]).")";
00124 $ilDB->query($q);
00125 fwrite($f, "Insert - L:".$insert["left"].",R:".
00126 $insert["right"].",sahs_id:".$sahs_id.",user_id:".$user_id."\n");
00127 }
00128 }
00129 foreach($this->update as $update)
00130 {
00131 $q = "SELECT * FROM scorm_tracking WHERE ".
00132 " user_id = ".$ilDB->quote($user_id).
00133 " AND sco_id = ".$ilDB->quote($sahs_id).
00134 " AND lvalue = ".$ilDB->quote($update["left"]);
00135 $set = $ilDB->query($q);
00136 if ($rec = $set->fetchRow(DB_FETCHMODE_ASSOC))
00137 {
00138 $q = "REPLACE INTO scorm_tracking (user_id, sco_id, obj_id, lvalue, rvalue) VALUES ".
00139 "(".$ilDB->quote($user_id).",".$ilDB->quote($sahs_id).",".
00140 $ilDB->quote($obj_id).",".
00141 $ilDB->quote($update["left"]).",".$ilDB->quote($update["right"]).")";
00142 $ilDB->query($q);
00143 fwrite($f, "Update - L:".$update["left"].",R:".
00144 $update["right"].",sahs_id:".$sahs_id.",user_id:".$user_id."\n");
00145 }
00146 else
00147 {
00148 fwrite($f, "ERROR Update, left value does not exist. L:".$update["left"].",R:".
00149 $update["right"].",sahs_id:".$sahs_id.",user_id:".$user_id."\n");
00150 }
00151 }
00152 }
00153 fclose($f);
00154 }
00155
00156 function _insertTrackData($a_sahs_id, $a_lval, $a_rval, $a_obj_id)
00157 {
00158 global $ilDB, $ilUser;
00159
00160 $q = "INSERT INTO scorm_tracking (user_id, sco_id, lvalue, rvalue, obj_id) ".
00161 " VALUES (".$ilDB->quote($ilUser->getId()).",".$ilDB->quote($a_sahs_id).
00162 ",".$ilDB->quote($a_lval).",".$ilDB->quote($a_rval).
00163 ",".$ilDB->quote($a_obj_id).")";
00164 $ilDB->query($q);
00165
00166 }
00167
00168
00169 function _getInProgress($scorm_item_id,$a_obj_id)
00170 {
00171 global $ilDB;
00172
00173 if(is_array($scorm_item_id))
00174 {
00175 $where = "WHERE sco_id IN('";
00176 $where .= implode("','",$scorm_item_id);
00177 $where .= "') ";
00178 $where .= ("AND obj_id = '".$a_obj_id."' ");
00179
00180 }
00181 else
00182 {
00183 $where = "WHERE sco_id = '".$scorm_item_id."' ";
00184 $where .= ("AND obj_id = '".$a_obj_id."' ");
00185 }
00186
00187
00188 $query = "SELECT user_id,sco_id FROM scorm_tracking ".
00189 $where.
00190 "GROUP BY user_id, sco_id";
00191
00192
00193 $res = $ilDB->query($query);
00194 while($row = $res->fetchRow(DB_FETCHMODE_OBJECT))
00195 {
00196 $in_progress[$row->sco_id][] = $row->user_id;
00197 }
00198 return is_array($in_progress) ? $in_progress : array();
00199 }
00200
00201 function _getCompleted($scorm_item_id,$a_obj_id)
00202 {
00203 global $ilDB;
00204
00205 if(is_array($scorm_item_id))
00206 {
00207 $where = "WHERE sco_id IN('".implode("','",$scorm_item_id)."') ";
00208 }
00209 else
00210 {
00211 $where = "sco_id = '".$scorm_item_id."' ";
00212 }
00213
00214 $query = "SELECT DISTINCT(user_id) FROM scorm_tracking ".
00215 $where.
00216 "AND obj_id = '".$a_obj_id."' ".
00217 "AND lvalue = 'cmi.core.lesson_status' ".
00218 "AND ( rvalue = 'completed' ".
00219 "OR rvalue = 'passed')";
00220 $res = $ilDB->query($query);
00221 while($row = $res->fetchRow(DB_FETCHMODE_OBJECT))
00222 {
00223 $user_ids[] = $row->user_id;
00224 }
00225 return $user_ids ? $user_ids : array();
00226 }
00227
00228 function _getFailed($scorm_item_id,$a_obj_id)
00229 {
00230 global $ilDB;
00231
00232 if(is_array($scorm_item_id))
00233 {
00234 $where = "WHERE sco_id IN('".implode("','",$scorm_item_id)."') ";
00235 }
00236 else
00237 {
00238 $where = "sco_id = '".$scorm_item_id."' ";
00239 }
00240
00241 $query = "SELECT DISTINCT(user_id) FROM scorm_tracking ".
00242 $where.
00243 "AND obj_id = '".$a_obj_id."' ".
00244 "AND lvalue = 'cmi.core.lesson_status' ".
00245 "AND rvalue = 'failed'";
00246 $res = $ilDB->query($query);
00247 while($row = $res->fetchRow(DB_FETCHMODE_OBJECT))
00248 {
00249 $user_ids[] = $row->user_id;
00250 }
00251 return $user_ids ? $user_ids : array();
00252 }
00253
00254 function _getCountCompletedPerUser($a_scorm_item_ids,$a_obj_id)
00255 {
00256 global $ilDB;
00257
00258 $where = "WHERE sco_id IN('";
00259 $where .= implode("','",$a_scorm_item_ids);
00260 $where .= "') ";
00261 $where .= ("AND obj_id = '".$a_obj_id."'");
00262
00263
00264 $query = "SELECT user_id, COUNT(user_id) as completed FROM scorm_tracking ".
00265 $where.
00266 "AND lvalue = 'cmi.core.lesson_status' ".
00267 "AND (rvalue = 'completed' OR ".
00268 "rvalue = 'passed') ".
00269 "GROUP BY user_id";
00270
00271 $res = $ilDB->query($query);
00272 while($row = $res->fetchRow(DB_FETCHMODE_OBJECT))
00273 {
00274 $users[$row->user_id] = $row->completed;
00275 }
00276
00277 return $users ? $users : array();
00278 }
00279
00280 function _getProgressInfo($sco_item_ids,$a_obj_id)
00281 {
00282 global $ilDB;
00283
00284 $query = "SELECT * FROM scorm_tracking ".
00285 "WHERE sco_id IN('".implode("','",$sco_item_ids)."') ".
00286 "AND obj_id = '".$a_obj_id."' ".
00287 "AND lvalue = 'cmi.core.lesson_status'";
00288
00289 $res = $ilDB->query($query);
00290
00291 $info['completed'] = array();
00292 $info['failed'] = array();
00293 while($row = $res->fetchRow(DB_FETCHMODE_OBJECT))
00294 {
00295 switch($row->rvalue)
00296 {
00297 case 'completed':
00298 case 'passed':
00299 $info['completed'][$row->sco_id][] = $row->user_id;
00300 break;
00301
00302 case 'failed':
00303 $info['failed'][$row->sco_id][] = $row->user_id;
00304 break;
00305 }
00306 }
00307 $info['in_progress'] = ilObjSCORMTracking::_getInProgress($sco_item_ids,$a_obj_id);
00308
00309 return $info;
00310 }
00311
00312
00313 }
00314 ?>
