claroline学员成绩统计代码

１、练习的成绩统计：
   \claroline\exercise\track_exercises.php中：
　　
总体情况：
     $sql = "SELECT  MIN(TEX.`result`) AS `minimum`,
                MAX(TEX.`result`) AS `maximum`,
                AVG(TEX.`result`) AS `average`,
                MAX(TEX.`weighting`) AS `weighting` ,
                COUNT(DISTINCT TEX.`user_id`) AS `users`,
                COUNT(TEX.`user_id`) AS `tusers`,
                AVG(`TEX`.`time`) AS `avgTime`
        FROM `".$tbl_qwz_tracking."` AS TEX
        WHERE TEX.`exo_id` = ". (int)$exercise->getId()."
                AND TEX.`user_id` IS NOT NULL";

每个学员的详细得分：
 //-- display details : USERS VIEW
    $sql = "SELECT `U`.`nom`, `U`.`prenom`, `U`.`user_id`,
            MIN(TE.`result`) AS `minimum`,
            MAX(TE.`result`) AS `maximum`,
            AVG(TE.`result`) AS `average`,
            COUNT(TE.`result`) AS `attempts`,
            AVG(TE.`time`) AS `avgTime`
    FROM (`".$tbl_user."` AS `U`, `".$tbl_rel_course_user."` AS `CU`, `".$tbl_qwz_exercise."` AS `QT`)
    LEFT JOIN `".$tbl_qwz_tracking."` AS `TE`
          ON `CU`.`user_id` = `TE`.`user_id`
          AND `QT`.`id` = `TE`.`exo_id`
    WHERE `CU`.`user_id` = `U`.`user_id`
      AND `CU`.`code_cours` = '" . claro_sql_escape(claro_get_current_course_id()) . "'
      AND (
            `TE`.`exo_id` = ". (int)$exercise->getId()."
            OR
            `TE`.`exo_id` IS NULL
          )
    GROUP BY `U`.`user_id`
    ORDER BY `U`.`nom` ASC, `U`.`prenom` ASC";


２、学习路径的统计

    \claroline\inc\lib\learnPath.lib.inc.php

function get_learnPath_progress($lpid, $lpUid)
{
    $tbl_cdb_names = claro_sql_get_course_tbl();
    $tbl_lp_learnPath            = $tbl_cdb_names['lp_learnPath'];
    $tbl_lp_rel_learnPath_module = $tbl_cdb_names['lp_rel_learnPath_module'];
    $tbl_lp_user_module_progress = $tbl_cdb_names['lp_user_module_progress'];
    $tbl_lp_module               = $tbl_cdb_names['lp_module'];

    // find progression for this user in each module of the path

    $sql = "SELECT UMP.`raw` AS R, UMP.`scoreMax` AS SMax, M.`contentType` AS CTYPE, UMP.`lesson_status` AS STATUS
             FROM `" . $tbl_lp_learnPath . "` AS LP,
                  `" . $tbl_lp_rel_learnPath_module . "` AS LPM,
                  `" . $tbl_lp_user_module_progress . "` AS UMP,
                  `" . $tbl_lp_module . "` AS M
            WHERE LP.`learnPath_id` = LPM.`learnPath_id`
              AND LPM.`learnPath_module_id` = UMP.`learnPath_module_id`
              AND UMP.`user_id` = " . (int) $lpUid . "
              AND LP.`learnPath_id` = " . (int) $lpid . "
              AND LPM.`visibility` = 'SHOW'
              AND M.`module_id` = LPM.`module_id`
              AND M.`contentType` != '" . CTLABEL_ . "'";

    $modules = claro_sql_query_fetch_all($sql);

    $progress = 0;
    if( !is_array($modules) || empty($modules) )
    {
        $progression = 0;
    }
    else
    {
        // progression is calculated in pourcents
        foreach( $modules as $module )
        {
            if( $module['SMax'] <= 0 )
            {
                $modProgress = 0 ;
            }
            else
            {
                $raw = min($module['R'],$module['SMax']);
                $modProgress = @round($raw/$module['SMax']*100);
            }

            // in case of scorm module, progression depends on the lesson status value
            if (($module['CTYPE']=="SCORM") && ($module['SMax'] <= 0) && (( $module['STATUS'] == 'COMPLETED') || ($module['STATUS'] == 'PASSED')))
            {
                $modProgress = 100;
            }

            if ($modProgress >= 0)
            {
                $progress += $modProgress;
            }
        }
        // find number of visible modules in this path
        $sqlnum = "SELECT COUNT(M.`module_id`)
                    FROM `" . $tbl_lp_rel_learnPath_module . "` AS LPM,
                          `". $tbl_lp_module . "` AS M
                    WHERE LPM.`learnPath_id` = " . (int) $lpid . "
                    AND LPM.`visibility` = 'SHOW'
                    AND M.`contentType` != '" . CTLABEL_ . "'
                    AND M.`module_id` = LPM.`module_id`
                    ";
        $nbrOfVisibleModules = claro_sql_query_get_single_value($sqlnum);

        if( is_numeric($nbrOfVisibleModules) && $nbrOfVisibleModules > 0)
              $progression = @round($progress/$nbrOfVisibleModules);
        else
            $progression = 0;

    }
    return $progression;
}