PHP Database::get_statistic_table方法代码示例

    /**
     * Returns users whose last login is prior from $ceiling
     * 
     * @param int|string $ceiling last login date
     * @param bool $active_only if true returns only active users. Otherwise returns all users.
     * @return ResultSet
     */
    static function list_zombies($ceiling, $active_only = true)
    {
        $ceiling = is_numeric($ceiling) ? (int) $ceiling : strtotime($ceiling);
        $ceiling = date('Y-m-d H:i:s', $ceiling);

        $user_table = Database::get_main_table(TABLE_MAIN_USER);
        $login_table = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_LOGIN);

        $sql = 'SELECT 
                    user.user_id, 
                    user.firstname, 
                    user.lastname, 
                    user.username, 
                    user.auth_source, 
                    user.email, 
                    user.status, 
                    user.registration_date, 
                    user.active, 
                    access.login_date';

        global $_configuration;
        if ($_configuration['multiple_access_urls']) {

            $access_url_rel_user_table = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
            $current_url_id = api_get_current_access_url_id();

            $sql .= " FROM $user_table as user, $login_table as access, $access_url_rel_user_table as url
                      WHERE 
                        access.login_date = (SELECT MAX(a.login_date) 
                                             FROM $login_table as a 
                                             WHERE a.login_user_id = user.user_id
                                             ) AND
                        access.login_date <= '$ceiling' AND
                        user.user_id = access.login_user_id AND 
                        url.user_id = user.user_id AND url.access_url_id=$current_url_id";
        } else {
            $sql .= " FROM $user_table as user, $login_table as access
                      WHERE 
                        access.login_date = (SELECT MAX(a.login_date) 
                                             FROM $login_table as a 
                                             WHERE a.login_user_id = user.user_id
                                             ) AND
                        access.login_date <= '$ceiling' AND
                        user.user_id = access.login_user_id";
        }
        if($active_only)
        {
            $sql .= ' AND user.active = 1';
        }

        return ResultSet::create($sql);
    }

function get_number_students_finish_exercise($exercise_id, $course_code, $session_id)
{
    $track_exercises = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
    $track_attempt = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
    $exercise_id = intval($exercise_id);
    $course_code = Database::escape_string($course_code);
    $session_id = intval($session_id);
    $sql = "SELECT DISTINCT exe_user_id\n    \t\tFROM {$track_exercises} e INNER JOIN {$track_attempt} a ON (a.exe_id = e.exe_id)\n    \t\tWHERE \texe_exo_id \t\t= {$exercise_id} AND\n    \t\t\t\tcourse_code \t= '{$course_code}' AND\n    \t\t\t\te.session_id \t= {$session_id} AND\n    \t\t\t\tstatus = ''";
    $result = Database::query($sql);
    $return = 0;
    if ($result) {
        $return = Database::num_rows($result);
    }
    return $return;
}

$courseCode = $_GET['coursecode'];
$questionId = $_GET['questionId'];
$coordinates = $_GET['coord'];
$objExcercise = $_SESSION['objExercise'];
$exerciseId = $objExcercise->selectId();
// Save clicking order
$answerOrderId = count($_SESSION['exerciseResult'][$questionId]['ids']) + 1;
if ($_GET['answerId'] == "0") {
    $hit = 0;
    $answerId = NULL;
} else {
    $hit = 1;
    $answerId = api_substr($_GET['answerId'], 22, 2);
    // Save into session
    $_SESSION['exerciseResult'][$questionId][$answerId] = $hit;
}
//round-up the coordinates
$coords = explode('/', $coordinates);
$coordinates = '';
foreach ($coords as $coord) {
    list($x, $y) = explode(';', $coord);
    $coordinates .= round($x) . ';' . round($y) . '/';
}
$coordinates = substr($coordinates, 0, -1);
$TBL_TRACK_E_HOTSPOT = Database::get_statistic_table(STATISTIC_TRACK_E_HOTSPOTS);
// Save into db
$sql = "INSERT INTO {$TBL_TRACK_E_HOTSPOT} (user_id , course_id , quiz_id , question_id , answer_id , correct , coordinate ) VALUES (\n\t\t\t" . intval($_user['user_id']) . ",\n\t\t\t'" . Database::escape_string($courseCode) . "',\n\t\t\t" . intval($exerciseId) . ",\n\t\t\t" . intval($questionId) . ",\n\t\t\t" . intval($answerId) . ",\n\t\t\t" . intval($hit) . "',\n\t\t\t'" . Database::escape_string($coordinates) . "')";
$result = Database::query($sql);
// Save insert id into session if users changes answer.
$insert_id = Database::insert_id();
$_SESSION['exerciseResult'][$questionId]['ids'][$answerOrderId] = $insert_id;

    require_once '../newscorm/learnpathItem.class.php';
    require_once '../newscorm/scorm.class.php';
    require_once '../newscorm/scormItem.class.php';
    require_once '../newscorm/aicc.class.php';
    require_once '../newscorm/aiccItem.class.php';
}
require_once '../inc/global.inc.php';
$courseInfo = api_get_course_info();
$_user = api_get_user_info();
$this_section = SECTION_COURSES;
require_once api_get_path(LIBRARY_PATH) . 'fileManage.lib.php';
$documentPath = api_get_path(SYS_COURSE_PATH) . $courseInfo['path'] . "/document";
$test = $_REQUEST['test'];
$full_file_path = $documentPath . $test;
my_delete($full_file_path . $_user['user_id'] . ".t.html");
$TABLETRACK_HOTPOTATOES = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_HOTPOTATOES);
$TABLE_LP_ITEM_VIEW = Database::get_course_table(TABLE_LP_ITEM_VIEW);
$score = $_REQUEST['score'];
$origin = $_REQUEST['origin'];
$learnpath_item_id = intval($_REQUEST['learnpath_item_id']);
$lpViewId = isset($_REQUEST['lp_view_id']) ? intval($_REQUEST['lp_view_id']) : null;
$course_id = $courseInfo['real_id'];
$_cid = api_get_course_id();
$jscript2run = '';
/**
 * Save the score for a HP quiz. Can be used by the learnpath tool as well
 * for HotPotatoes quizzes. When coming from the learning path, we
 * use the session variables telling us which item of the learning path has to
 * be updated (score-wise)
 * @param	string	File is the exercise name (the file name for a HP)
 * @param	integer	Score to save inside the tracking tables (HP and learnpath)

 /**
  * Gets the results of all students (or just one student if access is limited)
  * @param	string		The document path (for HotPotatoes retrieval)
  * @param	integer		User ID. Optional. If no user ID is provided, we take all the results. Defauts to null
  */
 public function getExercisesReporting($document_path, $hotpotato_name)
 {
     $return = array();
     $TBL_USER = Database::get_main_table(TABLE_MAIN_USER);
     $TBL_TRACK_HOTPOTATOES = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_HOTPOTATOES);
     $cid = api_get_course_id();
     $course_id = api_get_course_int_id();
     //$user_id         = intval($user_id);
     $user_id = null;
     $session_id_and = ' AND te.session_id = ' . api_get_session_id() . ' ';
     $hotpotato_name = Database::escape_string($hotpotato_name);
     if (!empty($exercise_id)) {
         $session_id_and .= " AND exe_exo_id = {$exercise_id} ";
     }
     if (empty($user_id)) {
         $sql = "SELECT firstname as userpart1, lastname as userpart2 ,\n                    email,\n                    tth.exe_name,\n                    tth.exe_result,\n                    tth.exe_weighting,\n                    tth.exe_date\n                    FROM {$TBL_TRACK_HOTPOTATOES} tth, {$TBL_USER} tu\n                    WHERE   tu.user_id=tth.exe_user_id AND\n                            tth.exe_cours_id = '" . Database::escape_string($cid) . "' AND\n                            tth.exe_name = '{$hotpotato_name}'\n                    ORDER BY tth.exe_cours_id ASC, tth.exe_date ASC";
     } else {
         $user_id_and = ' AND te.exe_user_id = ' . api_get_user_id() . ' ';
         // get only this user's results
         $sql = "SELECT '', exe_name, exe_result , exe_weighting, exe_date\n                    FROM {$TBL_TRACK_HOTPOTATOES}\n                    WHERE\n                        exe_user_id = '" . $user_id . "' AND\n                        exe_cours_id = '" . Database::escape_string($cid) . "' AND\n                        tth.exe_name = '{$hotpotato_name}'\n                    ORDER BY exe_cours_id ASC, exe_date ASC";
     }
     $results = array();
     $resx = Database::query($sql);
     while ($rowx = Database::fetch_array($resx, 'ASSOC')) {
         $results[] = $rowx;
     }
     $hpresults = array();
     $resx = Database::query($sql);
     while ($rowx = Database::fetch_array($resx, 'ASSOC')) {
         $hpresults[] = $rowx;
     }
     /*if ($filter) {
           switch ($filter) {
               case 1 :
                   $filter_by_not_revised = true;
                   break;
               case 2 :
                   $filter_by_revised = true;
                   break;
               default :
                   null;
           }
       }*/
     // Print the Result of Hotpotatoes Tests
     if (is_array($hpresults)) {
         for ($i = 0; $i < sizeof($hpresults); $i++) {
             $return[$i] = array();
             $title = GetQuizName($hpresults[$i]['exe_name'], $document_path);
             if ($title == '') {
                 $title = basename($hpresults[$i]['exe_name']);
             }
             if (empty($user_id)) {
                 $return[$i]['email'] = $hpresults[$i]['email'];
                 $return[$i]['first_name'] = $hpresults[$i]['userpart1'];
                 $return[$i]['last_name'] = $hpresults[$i]['userpart2'];
             }
             $return[$i]['title'] = $title;
             $return[$i]['exe_date'] = $hpresults[$i]['exe_date'];
             $return[$i]['result'] = $hpresults[$i]['exe_result'];
             $return[$i]['max'] = $hpresults[$i]['exe_weighting'];
         }
     }
     $this->results = $return;
     return true;
 }

//.........这里部分代码省略.........
             //Deleting session info
             if (api_get_session_id()) {
                 Session::erase('id_session');
                 Session::erase('session_name');
             }
         }
     } else {
         // Continue with the previous values
         if (empty($_SESSION['_course']) or empty($_SESSION['_cid'])) {
             //no previous values...
             $_cid = -1;
             //set default values that will be caracteristic of being unset
             $_course = -1;
         } else {
             $_cid = $_SESSION['_cid'];
             $_course = $_SESSION['_course'];
             // these lines are usefull for tracking. Indeed we can have lost the id_session and not the cid.
             // Moreover, if we want to track a course with another session it can be usefull
             if (!empty($_GET['id_session'])) {
                 $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
                 $sql = 'SELECT name FROM ' . $tbl_session . ' WHERE id="' . intval($_SESSION['id_session']) . '"';
                 $rs = Database::query($sql);
                 list($_SESSION['session_name']) = Database::fetch_array($rs);
                 $_SESSION['id_session'] = intval($_GET['id_session']);
             }
             if (!isset($_SESSION['login_as'])) {
                 $save_course_access = true;
                 //The value  $_dont_save_user_course_access should be added before the call of global.inc.php see the main/inc/chat.ajax.php file
                 //Disables the updates in the TRACK_E_COURSE_ACCESS table
                 if (isset($_dont_save_user_course_access) && $_dont_save_user_course_access == true) {
                     $save_course_access = false;
                 }
                 if ($save_course_access) {
                     $course_tracking_table = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
                     /*
                      * When $_configuration['session_lifetime'] is too big 100 hours (in order to let users take exercises with no problems)
                      * the function Tracking::get_time_spent_on_the_course() returns big values (200h) due the condition:
                      * login_course_date > now() - INTERVAL $session_lifetime SECOND
                      *
                      */
                     /*
                                               if (isset($_configuration['session_lifetime'])) {
                                               $session_lifetime    = $_configuration['session_lifetime'];
                                               } else {
                                               $session_lifetime    = 3600; // 1 hour
                                               } */
                     $session_lifetime = 3600;
                     // 1 hour
                     $course_code = $_course['sysCode'];
                     $time = api_get_utc_datetime();
                     if (isset($_user['user_id']) && !empty($_user['user_id'])) {
                         //We select the last record for the current course in the course tracking table
                         //But only if the login date is < than now + max_life_time
                         $sql = "SELECT course_access_id FROM {$course_tracking_table}\n                            WHERE   user_id     = " . intval($_user['user_id']) . " AND\n                                    course_code = '{$course_code}' AND\n                                    session_id  = " . api_get_session_id() . " AND\n                                    login_course_date > now() - INTERVAL {$session_lifetime} SECOND\n                        ORDER BY login_course_date DESC LIMIT 0,1";
                         $result = Database::query($sql);
                         if (Database::num_rows($result) > 0) {
                             $i_course_access_id = Database::result($result, 0, 0);
                             //We update the course tracking table
                             $sql = "UPDATE {$course_tracking_table}  SET logout_course_date = '{$time}', counter = counter+1\n                                WHERE course_access_id = " . intval($i_course_access_id) . " AND session_id = " . api_get_session_id();
                             //error_log($sql);
                             Database::query($sql);
                         } else {
                             $sql = "INSERT INTO {$course_tracking_table} (course_code, user_id, login_course_date, logout_course_date, counter, session_id)" . "VALUES('" . $course_code . "', '" . $_user['user_id'] . "', '{$time}', '{$time}', '1','" . api_get_session_id() . "')";
                             //error_log($sql);
                             Database::query($sql);
                         }

 /**
  * Writes the current data to the database
  * @return	boolean	Query result
  */
 function write_to_db()
 {
     if ($this->debug > 0) {
         error_log('New LP - In learnpathItem::write_to_db()', 0);
     }
     $mode = $this->get_lesson_mode();
     $credit = $this->get_credit();
     $my_verified_status = $this->get_status(false);
     $item_view_table = Database::get_course_table(TABLE_LP_ITEM_VIEW);
     $sql_verified = 'SELECT status FROM ' . $item_view_table . ' WHERE lp_item_id="' . $this->db_id . '" AND lp_view_id="' . $this->view_id . '" AND view_count="' . $this->attempt_id . '" ;';
     $rs_verified = Database::query($sql_verified, __FILE__, __LINE__);
     $row_verified = Database::fetch_array($rs_verified);
     $my_case_completed = array('completed', 'passed', 'browsed', 'failed');
     //added by isaac flores
     if (in_array($sql_verified['status'], $my_case_completed)) {
         $save = false;
     } else {
         $save = true;
     }
     if ($save === false && $this->type == 'sco' || $this->type == 'sco' && ($credit == 'no-credit' or $mode == 'review' or $mode == 'browse')) {
         //this info shouldn't be saved as the credit or lesson mode info prevent it
         if ($this->debug > 1) {
             error_log('New LP - In learnpathItem::write_to_db() - credit(' . $credit . ') or lesson_mode(' . $mode . ') prevent recording!', 0);
         }
     } else {
         //check the row exists
         $inserted = false;
         // this a special case for multiple attempts and Dokeos exercises
         if ($this->type == 'quiz' && $this->get_prevent_reinit() == 0 && $this->get_status() == 'completed') {
             // we force the item to be restarted
             $this->restart();
             $sql = "INSERT INTO {$item_view_table} " . "(total_time, " . "start_time, " . "score, " . "status, " . "max_score, " . "lp_item_id, " . "lp_view_id, " . "view_count, " . "suspend_data, " . "lesson_location)" . "VALUES" . "(" . $this->get_total_time() . "," . "" . $this->current_start_time . "," . "" . $this->get_score() . "," . "'" . $this->get_status(false) . "'," . "'" . $this->get_max() . "'," . "" . $this->db_id . "," . "" . $this->view_id . "," . "" . $this->get_attempt_id() . "," . "'" . Database::escape_string($this->current_data) . "'," . "'" . $this->lesson_location . "')";
             if ($this->debug > 2) {
                 error_log('New LP - In learnpathItem::write_to_db() - Inserting into item_view forced: ' . $sql, 0);
             }
             $res = Database::query($sql, __FILE__, __LINE__);
             $this->db_item_view_id = Database::insert_id();
             $inserted = true;
         }
         $item_view_table = Database::get_course_table(TABLE_LP_ITEM_VIEW);
         $check = "SELECT * FROM {$item_view_table} " . "WHERE lp_item_id = " . $this->db_id . " " . "AND   lp_view_id = " . $this->view_id;
         if ($this->debug > 2) {
             error_log('New LP - In learnpathItem::write_to_db() - Querying item_view: ' . $check, 0);
         }
         $check_res = Database::query($check);
         //depending on what we want (really), we'll update or insert a new row
         //now save into DB
         $res = 0;
         if ($inserted == false && Database::num_rows($check_res) < 1) {
             /*$my_status = '';
             		if ($this->type!=TOOL_QUIZ) {
             				$my_status = $this->get_status(false);
             		}*/
             $sql = "INSERT INTO {$item_view_table} " . "(total_time, " . "start_time, " . "score, " . "status, " . "max_score, " . "lp_item_id, " . "lp_view_id, " . "view_count, " . "suspend_data, " . "lesson_location)" . "VALUES" . "(" . $this->get_total_time() . "," . "" . $this->current_start_time . "," . "" . $this->get_score() . "," . "'" . $this->get_status(false) . "'," . "'" . $this->get_max() . "'," . "" . $this->db_id . "," . "" . $this->view_id . "," . "" . $this->get_attempt_id() . "," . "'" . Database::escape_string($this->current_data) . "'," . "'" . $this->lesson_location . "')";
             if ($this->debug > 2) {
                 error_log('New LP - In learnpathItem::write_to_db() - Inserting into item_view: ' . $sql, 0);
             }
             $res = Database::query($sql, __FILE__, __LINE__);
             $this->db_item_view_id = Database::insert_id();
         } else {
             $sql = '';
             if ($this->type == 'hotpotatoes') {
                 //make an exception for HotPotatoes, don't update the score
                 //because it has been saved outside of this tool
                 $sql = "UPDATE {$item_view_table} " . "SET total_time = " . $this->get_total_time() . ", " . " start_time = " . $this->get_current_start_time() . ", " . " score = " . $this->get_score() . ", " . " status = '" . $this->get_status(false) . "'," . " max_score = '" . $this->get_max() . "'," . " suspend_data = '" . Database::escape_string($this->current_data) . "'," . " lesson_location = '" . $this->lesson_location . "' " . "WHERE lp_item_id = " . $this->db_id . " " . "AND lp_view_id = " . $this->view_id;
             } else {
                 //for all other content types...
                 if ($this->type == 'quiz') {
                     $my_status = ' ';
                     $total_time = ' ';
                     if (!empty($_REQUEST['exeId'])) {
                         $TBL_TRACK_EXERCICES = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
                         $safe_exe_id = Database::escape_string($_REQUEST['exeId']);
                         $sql = 'SELECT start_date,exe_date FROM ' . $TBL_TRACK_EXERCICES . ' WHERE exe_id = ' . (int) $safe_exe_id;
                         $res = Database::query($sql, __FILE__, __LINE__);
                         $row_dates = Database::fetch_array($res);
                         $time_start_date = convert_mysql_date($row_dates['start_date']);
                         $time_exe_date = convert_mysql_date($row_dates['exe_date']);
                         $mytime = (int) $time_exe_date - (int) $time_start_date;
                         $total_time = " total_time = " . $mytime . ", ";
                     }
                 } else {
                     $my_type_lp = learnpath::get_type_static($this->lp_id);
                     // this is a array containing values finished
                     $case_completed = array('completed', 'passed', 'browsed');
                     //is not multiple attempts
                     if ($this->get_prevent_reinit() == 1) {
                         // process of status verified into data base
                         $sql_verified = 'SELECT status FROM ' . $item_view_table . ' WHERE lp_item_id="' . $this->db_id . '" AND lp_view_id="' . $this->view_id . '" AND view_count="' . $this->attempt_id . '" ;';
                         $rs_verified = Database::query($sql_verified, __FILE__, __LINE__);
                         $row_verified = Database::fetch_array($rs_verified);
                         //get type lp: 1=lp dokeos and  2=scorm
                         // if not is completed or passed or browsed and learning path is scorm
                         if (!in_array($this->get_status(false), $case_completed) && $my_type_lp == 2) {
                             //&& $this->type!='dir'
                             $total_time = " total_time = total_time +" . $this->get_total_time() . ", ";
//.........这里部分代码省略.........

    /**
     * Displays the documents downloaded for a specific user in a specific course.
     * @param     string    kind of view inside tracking info
     * @param    int        User id
     * @param    string    Course code
     * @param    int        Session id (optional, default = 0)
     * @return     void
     */
    public function display_document_tracking_info($view, $user_id, $course_id, $session_id = 0)
    {
    	// protect data
    	$user_id     = intval($user_id);
    	$course_id     = Database::escape_string($course_id);
    	$session_id = intval($session_id);

    	$downloads_table = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_DOWNLOADS);

    	if (substr($view,4,1) == '1') {
    		$new_view = substr_replace($view,'0',4,1);
    		$title[1]= get_lang('DocumentsDetails');

    		$sql = "SELECT down_doc_path
                        FROM $downloads_table
                        WHERE down_cours_id = '$course_id'
                            AND down_user_id = '$user_id'
                            AND down_session_id = '$session_id'
                        GROUP BY down_doc_path";

    		$results = getManyResults1Col($sql);
    		$title_line = get_lang('DocumentsTitleDocumentColumn')."\n";
            $line = null;
    		if (is_array($results)) {
    			for ($j = 0 ; $j < count($results) ; $j++) {
    				$line .= $results[$j]."\n";
    			}
    		} else {
    			$line = get_lang('NoResult');
    		}
    	} else {
    		$new_view = substr_replace($view,'1',4,1);
    	}
    	return array($title_line, $line);
    }

     if ($result) {
         echo Display::display_confirmation_message(get_lang('MessageHasBeenSent'));
     } else {
         echo Display::display_error_message(get_lang('ErrorSendingMessage'));
     }
     break;
 case 'send_invitation':
     $subject = isset($_REQUEST['subject']) ? $_REQUEST['subject'] : null;
     SocialManager::send_invitation_friend_user($_REQUEST['user_id'], $subject, $_REQUEST['content']);
     break;
 case 'find_users':
     if (api_is_anonymous()) {
         echo '';
         break;
     }
     $track_online_table = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ONLINE);
     $tbl_my_user = Database::get_main_table(TABLE_MAIN_USER);
     $tbl_my_user_friend = Database::get_main_table(TABLE_MAIN_USER_REL_USER);
     $tbl_user = Database::get_main_table(TABLE_MAIN_USER);
     $tbl_access_url_rel_user = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
     $search = Database::escape_string($_REQUEST['tag']);
     $access_url_id = api_get_multiple_access_url() == 'true' ? api_get_current_access_url_id() : 1;
     $user_id = api_get_user_id();
     $is_western_name_order = api_is_western_name_order();
     $likeCondition = " AND (firstname LIKE '%{$search}%' OR lastname LIKE '%{$search}%' OR email LIKE '%{$search}%') ";
     if (api_get_setting('allow_social_tool') == 'true' && api_get_setting('allow_message_tool') == 'true') {
         // All users
         if (api_get_setting('allow_send_message_to_all_platform_users') == 'true' || api_is_platform_admin()) {
             if ($access_url_id != 0) {
                 $sql = "SELECT DISTINCT u.user_id as id, u.firstname, u.lastname, u.email\n                            FROM {$tbl_user} u LEFT JOIN {$tbl_access_url_rel_user} r ON u.user_id = r.user_id\n                            WHERE\n                                u.status <> 6  AND\n                                u.user_id <> {$user_id} AND\n                                r.access_url_id = {$access_url_id}\n                                {$likeCondition} ";
             } else {

 /**
  * Print the number of users that didn't login for a certain period of time
  */
 static function print_users_not_logged_in_stats()
 {
     $total_logins = array();
     $table = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_LOGIN);
     $access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
     $current_url_id = api_get_current_access_url_id();
     $total = self::count_users();
     if (api_is_multiple_url_enabled()) {
         $table_url = ", {$access_url_rel_user_table}";
         $where_url = " AND login_user_id=user_id AND access_url_id='" . $current_url_id . "'";
     } else {
         $table_url = '';
         $where_url = '';
     }
     $sql[get_lang('Thisday')] = "SELECT count(distinct(login_user_id)) AS number " . " FROM {$table} {$table_url} " . " WHERE DATE_ADD(login_date, INTERVAL 1 DAY) >= NOW() {$where_url}";
     $sql[get_lang('Last7days')] = "SELECT count(distinct(login_user_id)) AS number " . " FROM {$table} {$table_url} " . " WHERE DATE_ADD(login_date, INTERVAL 7 DAY) >= NOW() {$where_url}";
     $sql[get_lang('Last31days')] = "SELECT count(distinct(login_user_id)) AS number " . " FROM {$table} {$table_url} " . " WHERE DATE_ADD(login_date, INTERVAL 31 DAY) >= NOW() {$where_url}";
     $sql[sprintf(get_lang('LastXMonths'), 6)] = "SELECT count(distinct(login_user_id)) AS number " . " FROM {$table} {$table_url} " . " WHERE DATE_ADD(login_date, INTERVAL 6 MONTH) >= NOW() {$where_url}";
     $sql[get_lang('NeverConnected')] = "SELECT count(distinct(login_user_id)) AS number " . " FROM {$table} {$table_url} WHERE 1=1 {$where_url}";
     foreach ($sql as $index => $query) {
         $res = Database::query($query);
         $obj = Database::fetch_object($res);
         $r = $total - $obj->number;
         $total_logins[$index] = $r < 0 ? 0 : $r;
     }
     Statistics::print_stats(get_lang('StatsUsersDidNotLoginInLastPeriods'), $total_logins, false);
 }

/**
 * Deletes an attempt from TABLE_STATISTIC_TRACK_E_HOTPOTATOES
 * @param int $id
 */
function deleteAttempt($id)
{
    $table = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_HOTPOTATOES);
    $id = intval($id);
    $sql = "DELETE FROM {$table} WHERE id = {$id}";
    Database::query($sql);
}

// So, we have to reassign this variable again in order to keep its value right.
$charset = $charset_initial_value;
// The global variable $text_dir has been defined in the language file trad4all.inc.php.
// For determing text direction correspondent to the current language we use now information from the internationalization library.
$text_dir = api_get_text_direction();
// ===== "who is logged in?" module section =====
// check and modify the date of user in the track.e.online table
if (!($x = strpos($_SERVER['PHP_SELF'], 'whoisonline.php'))) {
    preventMultipleLogin($_user["user_id"]);
    LoginCheck(isset($_user['user_id']) ? $_user['user_id'] : '');
}
// ===== end "who is logged in?" module section =====
//Update of the logout_date field in the table track_e_login (needed for the calculation of the total connection time)
if (!isset($_SESSION['login_as']) && isset($_user)) {
    // if $_SESSION['login_as'] is set, then the user is an admin logged as the user
    $tbl_track_login = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_LOGIN);
    $sql_last_connection = "SELECT login_id, login_date FROM {$tbl_track_login} WHERE login_user_id='" . $_user["user_id"] . "' ORDER BY login_date DESC LIMIT 0,1";
    $q_last_connection = Database::query($sql_last_connection);
    if (Database::num_rows($q_last_connection) > 0) {
        $i_id_last_connection = Database::result($q_last_connection, 0, 'login_id');
        // is the latest logout_date still relevant?
        $sql_logout_date = "SELECT logout_date FROM {$tbl_track_login} WHERE login_id={$i_id_last_connection}";
        $q_logout_date = Database::query($sql_logout_date);
        $res_logout_date = convert_sql_date(Database::result($q_logout_date, 0, 'logout_date'));
        if ($res_logout_date < time() - $_configuration['session_lifetime']) {
            // it isn't, we should create a fresh entry
            event_login();
            // now that it's created, we can get its ID and carry on
            $q_last_connection = Database::query($sql_last_connection);
            $i_id_last_connection = Database::result($q_last_connection, 0, 'login_id');
        }

    /**
     * TODO: Not used, to b deleted?
     * Enter description here...
     * @param int $user_id
     * @param string $course_code
     * @param date $year
     * @param date $month
     * @param date $day
     * @return unknown
     */
    static function get_connections_to_course_by_time($user_id, $course_code, $year = '', $month = '', $day = '') {
        // Database table definitions
        $tbl_track_course = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);

        $sql = 'SELECT login_course_date, logout_course_date FROM ' . $tbl_track_course . '
                        WHERE user_id = ' . intval($user_id) . '
                        AND course_code="' . Database::escape_string($course_code) . '"
                        ORDER BY login_course_date DESC';

        $rs = Database::query($sql);
        $connections = array();
        while ($row = Database::fetch_array($rs)) {
            $login_date = $row['login_course_date'];
            $logout_date = $row['logout_course_date'];
            $timestamp_login_date = strtotime($login_date);
            $timestamp_logout_date = strtotime($logout_date);
            $connections[] = array('login' => $timestamp_login_date, 'logout' => $timestamp_logout_date);
        }
        return $connections;
    }

/**
 * User logs in for the first time to a course
 * @param string $course_code
 * @param int $user_id
 * @param int $session_id
 */
function event_course_login($course_code, $user_id, $session_id)
{
    $course_tracking_table = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
    //@todo use api_get_utc_datetime
    $time = api_get_utc_datetime();
    $course_code = Database::escape_string($course_code);
    $user_id = intval($user_id);
    $session_id = intval($session_id);
    $session_lifetime = 3600;
    //We select the last record for the current course in the course tracking table
    $sql = "SELECT course_access_id\n            FROM {$course_tracking_table}\n            WHERE\n                user_id     = {$user_id} AND\n                course_code = '{$course_code}' AND\n                session_id  = {$session_id} AND\n                login_course_date > '{$time}' - INTERVAL {$session_lifetime} SECOND\n            ORDER BY login_course_date DESC LIMIT 0,1";
    $result = Database::query($sql);
    if (Database::num_rows($result) > 0) {
        $i_course_access_id = Database::result($result, 0, 0);
        //We update the course tracking table
        $sql = "UPDATE {$course_tracking_table}  SET logout_course_date = '{$time}', counter = counter+1\n            WHERE course_access_id = " . intval($i_course_access_id) . " AND session_id = " . $session_id;
        Database::query($sql);
    } else {
        $sql = "INSERT INTO {$course_tracking_table} (course_code, user_id, login_course_date, logout_course_date, counter, session_id)" . "VALUES('" . $course_code . "', '" . $user_id . "', '{$time}', '{$time}', '1','" . $session_id . "')";
        Database::query($sql);
    }
    // Course catalog stats modifications see #4191
    CourseManager::update_course_ranking(null, null, null, null, true, false);
}

 /**
  *
  * @param int $courseId
  * @param int $sessionId
  * @param array $quizId
  * @return array exercises
  */
 public function getExerciseAndResult($courseId, $sessionId, $quizId = array())
 {
     if (empty($quizId)) {
         return array();
     }
     $sessionId = intval($sessionId);
     $ids = is_array($quizId) ? $quizId : array($quizId);
     $ids = array_map('intval', $ids);
     $ids = implode(',', $ids);
     $track_exercises = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
     if ($sessionId != 0) {
         $sql = "SELECT * FROM {$track_exercises} te " . "INNER JOIN c_quiz cq ON cq.id = te.exe_exo_id " . "INNER JOIN course c ON te.exe_cours_id = c.code AND c.id = cq.c_id " . "WHERE " . "c.id = %s AND " . "te.session_id = %s AND " . "cq.id IN (%s) " . "ORDER BY cq.id ";
         $sql = sprintf($sql, $courseId, $sessionId, $ids);
     } else {
         $sql = "SELECT * FROM {$track_exercises} te " . "INNER JOIN c_quiz cq ON cq.id = te.exe_exo_id " . "INNER JOIN course c ON te.exe_cours_id = c.code AND c.id = cq.c_id " . "WHERE " . "c.id = %s AND " . "cq.id IN (%s) " . "ORDER BY cq.id ";
         $sql = sprintf($sql, $courseId, $ids);
     }
     $result = Database::query($sql);
     $rows = array();
     while ($row = Database::fetch_array($result, 'ASSOC')) {
         $rows[] = $row;
     }
     return $rows;
 }