chamilo-lms2/main/reports/modules/scorm.php

<?php

$reports_modules['scorm'] = array();


function reports_modules_scorm_init()
{
}

function reports_modules_scorm_init_forEachCourses($course_code, $course_id, $course_db)
{
    global $reports_modules;

    $reports_modules_scorm_toolid = reports_getToolId(TOOL_LEARNPATH);

    // package level
    array_push($reports_modules['scorm'],
        array(
            'keys_query' =>
                'select '.$course_id.' as course_id, '.
                $reports_modules_scorm_toolid.' as tool_id, '.
                'lp.id as child_id, lp.name as child_name, '.
                "'".$course_db."'".' as course_db from '.
                Database::get_course_table(TABLE_LP_MAIN).' lp',
            'values_query_function' => 'reports_modules_scorm_packageVal'
        ));

    // sco level
    array_push($reports_modules['scorm'],
        array(
            'keys_query' =>
                'select '.$course_id.' as course_id, '.
                $reports_modules_scorm_toolid.' as tool_id, '.
                'lp.id as child_id, lp.name as child_name, '.
                'lpi.id as subchild_id, lpi.title as subchild_name, '.
                "'".$course_db."'".' as course_db from '.
                Database::get_course_table(TABLE_LP_MAIN, $course_db).
                ' lp,'.
                Database::get_course_table(TABLE_LP_ITEM, $course_db).
                ' lpi where lp.id = lpi.lp_id',
            'values_query_function' => 'reports_modules_scorm_scoVal'
        ));

    // objectives level
    array_push($reports_modules['scorm'],
        array(
            'keys_query' =>
                'select distinct '.$course_id.' as course_id, '.
                $reports_modules_scorm_toolid.' as tool_id, '.
                'lp.id as child_id, lp.name as child_name, '.
                'lpi.id as subchild_id, '.
                'lpi.title as subchild_name, '.
                'null as subsubchild_id, '.
                'lpivo.objective_id as subsubchild_name, '.
                "'".$course_db."'".' as course_db from '.
                Database::get_course_table(TABLE_LP_MAIN, $course_db).
                ' lp,'.
                Database::get_course_table(TABLE_LP_ITEM, $course_db).
                ' lpi, '.
                Database::get_course_table(TABLE_LP_ITEM_VIEW, $course_db).
                ' lpiv, '.
                Database::get_course_table(TABLE_LP_IV_OBJECTIVE, $course_db).
                ' lpivo '.
                'where lp.id = lpi.lp_id '.
                'and lpi.id = lpiv.lp_item_id '.
                'and lpiv.id = lpivo.lp_iv_id ',
            'values_query_function' => 'reports_modules_scorm_objVal'
        ));
}

function reports_modules_scorm_packageVal($scorm, $key_id)
{
    return array(
        'type' => 'sql',
        'sql' =>
            'select '.$key_id.', user_id as uid, '.
            'session_id, view_count as attempt, null as score, '.
            'progress as progress, '.
            'null as time, null as ts from '.
            Database::get_course_table(TABLE_LP_VIEW, $scorm['course_db']).
            ' where lp_id = '.$scorm['child_id']
    );
}

function reports_modules_scorm_scoVal($scorm, $key_id)
{
    return array(
        'type' => 'sql',
        'sql' =>
            'select '.$key_id.', lpv.user_id as uid, '.
            'lpv.session_id, lpiv.view_count as attempt, '.
            'lpiv.score as score, '.
            '(case lpiv.status '.
            'when "incomplete" then 0 '.
            'when "completed" then 1 '.
            'when "passed" then 2 '.
            'when "failed" then 3 '.
            'when "browsed" then 4 '.
            'when "not attempted" then 5 '.
            'else 6 '.
            'end) as progress, '.
            'lpiv.total_time as time, null as ts from '.
            Database::get_course_table(TABLE_LP_VIEW, $scorm['course_db']).
            ' lpv, '.
            Database::get_course_table(TABLE_LP_ITEM_VIEW, $scorm['course_db']).
            ' lpiv '.
            ' where lpv.lp_id = '.$scorm['child_id'].
            ' and lpiv.lp_item_id = '.$scorm['subchild_id'].
            ' and lpiv.lp_view_id = lpv.id'
    );
}

function reports_modules_scorm_objVal($scorm, $key_id)
{
    return array(
        'type' => 'sql',
        'sql' =>
            'select '.$key_id.', lpv.user_id as uid, '.
            'lpv.session_id, lpiv.view_count as attempt, '.
            'lpivo.score_raw as score, '.
            '(case lpivo.status '.
            'when "incomplete" then 0 '.
            'when "completed" then 1 '.
            'when "passed" then 2 '.
            'when "failed" then 3 '.
            'when "browsed" then 4 '.
            'when "not attempted" then 5 '.
            'else 6 '.
            'end) as progress, '.
            'null as time, null as ts from '.
            Database::get_course_table(TABLE_LP_VIEW, $scorm['course_db']).
            ' lpv, '.
            Database::get_course_table(TABLE_LP_ITEM_VIEW, $scorm['course_db']).
            ' lpiv, '.
            Database::get_course_table(TABLE_LP_IV_OBJECTIVE, $scorm['course_db']).
            ' lpivo '.
            ' where lpv.lp_id = '.$scorm['child_id'].
            ' and lpiv.lp_item_id = '.$scorm['subchild_id'].
            ' and lpivo.objective_id = "'.$scorm['subsubchild_name'].'" '.
            ' and lpiv.lp_view_id = lpv.id'.
            ' and lpivo.lp_iv_id=lpiv.id'
    );
}