Hello everybody.
I am working with a SCORM package of moodle, I am trying to make the sum of all the cmi.core.total_time variables. At the moment the cmi.core.total_time variable contains the time that a student has been using in a specific page of a specific course, I need the overall time that the student has been surfing in all pages of the all courses.
Thank you a lot.
Hi Juan,
Actually for detailled statistics on SCORM content and usage I use SQL to query directly the db.
This query can give you the data your are looking for
SELECT mu.id, mu.firstname, mu.lastname, SEC_TO_TIME(SUM(TIME_TO_SEC(msst.value)))
FROM mdl_user mu LEFT JOIN mdl_scorm_scoes_track msst ON mu.id= msst.userid
WHERE
msst.element = "cmi.core.total_time"
GROUP BY mu.id
Hope this helps
Ciao
Marco
Thank you Marco!!, it is perfect, now im trying to get the time that the student has been on a specific course for display on the calification screen:
http://img186.imageshack.us/my.php?image=dibujobc0.jpg
I trying with SQL, but dont find the correct attribute which specifies the course.
Do you think that would be possible?
http://img186.imageshack.us/my.php?image=dibujobc0.jpg
I trying with SQL, but dont find the correct attribute which specifies the course.
Do you think that would be possible?
Hi Juan Carlos,
Yes I think it's possible. Try with this:
SELECT dt.fina, dt.lana, dt.toti, ms.name, ms.course
FROM(
SELECT msst.scormid as courseid, mu.id, mu.firstname as fina, mu.lastname as lana, SEC_TO_TIME(SUM(TIME_TO_SEC(msst.value))) as toti
FROM mdl_user mu LEFT JOIN mdl_scorm_scoes_track msst ON mu.id= msst.userid
WHERE
msst.element = "cmi.core.total_time"
GROUP BY
msst.scormid, mu.id
) as dt LEFT JOIN mdl_scorm ms ON dt.courseid = ms.id
Is what you was lookin for?
Ciao
Marco
Thank you again Marco.
I finally managed to get access to the database; thus, it was easier for me. This was the sql query that i needed:
SELECT u.username Usuario,
u.firstname Nombre,
u.lastname Apellidos,
c.fullname Curso,
SEC_TO_TIME(SUM( TIME_TO_SEC( t.value ) ) ) Tiempo_total
FROM mdl_course c, mdl_scorm s, mdl_scorm_scoes_track t, mdl_user u
WHERE u.id = t.userid
AND t.scormid = s.id
AND s.course = c.id
AND t.element = 'cmi.core.total_time'
AND c.id = '".$course->id."'
GROUP BY u.username
Show all users of a given course. In my system one course is formed by several scorms.
If someone needs it, I also modify the export to excell, Including this new information.
Ciao!!
I finally managed to get access to the database; thus, it was easier for me. This was the sql query that i needed:
SELECT u.username Usuario,
u.firstname Nombre,
u.lastname Apellidos,
c.fullname Curso,
SEC_TO_TIME(SUM( TIME_TO_SEC( t.value ) ) ) Tiempo_total
FROM mdl_course c, mdl_scorm s, mdl_scorm_scoes_track t, mdl_user u
WHERE u.id = t.userid
AND t.scormid = s.id
AND s.course = c.id
AND t.element = 'cmi.core.total_time'
AND c.id = '".$course->id."'
GROUP BY u.username
Show all users of a given course. In my system one course is formed by several scorms.
If someone needs it, I also modify the export to excell, Including this new information.
Ciao!!
Hola Juan,
That's great! if you find the way to obtain what you was lookin for... ;)
Reading your query it looks similar the one I've posted over, I think they give the same records, only two different way to write the same thing in SQL.
Can you share your version of the export to Excell function? What Moodle version are you using? 1.8.3 or 1.9?
ciao
Marco
Hey Marco, is similar but dont give the same records. My function in Excel:
Edit: /grade/lib.php
In functions zone:
////////////Autor: Juan Carlos Ariza//////////
function getTotalTime($user, $course) {
$conexion=mysql_connect("localhost", "USER", "PASS")
or die("Error al conectar con el servidor");
mysql_select_db('moodle', $conexion)
or die("Error al abrir la base de datos");
$consulta = mysql_query("SELECT SEC_TO_TIME(SUM( TIME_TO_SEC( t.value ) ) ) Tiempo_total FROM mdl_course c, mdl_scorm s, mdl_scorm_scoes_track t, mdl_user u WHERE u.id = t.userid AND t.scormid = s.id AND s.course = c.id AND t.element = 'cmi.core.total_time' AND u.id ='".$user."' AND c.id = '".$course."'")
or die("No se puede acceder a la tabla");
$nfilas=mysql_num_rows($consulta);
for($i=0; $i<$nfilas; $i++) {
$fila=mysql_fetch_array($consulta);
$tiempo = $fila['Tiempo_total'];
}
return $tiempo;
}
///////////////////////////////////////////////////
In code zone:
(...)
foreach ($studentgrades as $grade) {
if (is_numeric($grade)) {
$myxls->write_number($i,$j++,strip_tags($grade));
}
else {
$myxls->write_string($i,$j++,strip_tags($grade));
}
}
////////////Autor: Juan Carlos Ariza//////////
$tiempoT = getTotalTime($student->id, $course->id);
if (empty($tiempoT)) {
$tiempoT = "00:00:00";
}
$myxls->write_number($i,$j,$totals[$student->id]);
$myxls->write_string($i,$j+1,$tiempoT);
///////////////////////////////////////////
}
(...)
Big up !!
Edit: /grade/lib.php
In functions zone:
////////////Autor: Juan Carlos Ariza//////////
function getTotalTime($user, $course) {
$conexion=mysql_connect("localhost", "USER", "PASS")
or die("Error al conectar con el servidor");
mysql_select_db('moodle', $conexion)
or die("Error al abrir la base de datos");
$consulta = mysql_query("SELECT SEC_TO_TIME(SUM( TIME_TO_SEC( t.value ) ) ) Tiempo_total FROM mdl_course c, mdl_scorm s, mdl_scorm_scoes_track t, mdl_user u WHERE u.id = t.userid AND t.scormid = s.id AND s.course = c.id AND t.element = 'cmi.core.total_time' AND u.id ='".$user."' AND c.id = '".$course."'")
or die("No se puede acceder a la tabla");
$nfilas=mysql_num_rows($consulta);
for($i=0; $i<$nfilas; $i++) {
$fila=mysql_fetch_array($consulta);
$tiempo = $fila['Tiempo_total'];
}
return $tiempo;
}
///////////////////////////////////////////////////
In code zone:
(...)
foreach ($studentgrades as $grade) {
if (is_numeric($grade)) {
$myxls->write_number($i,$j++,strip_tags($grade));
}
else {
$myxls->write_string($i,$j++,strip_tags($grade));
}
}
////////////Autor: Juan Carlos Ariza//////////
$tiempoT = getTotalTime($student->id, $course->id);
if (empty($tiempoT)) {
$tiempoT = "00:00:00";
}
$myxls->write_number($i,$j,$totals[$student->id]);
$myxls->write_string($i,$j+1,$tiempoT);
///////////////////////////////////////////
}
(...)
Big up !!