require_once '../../shared/code/tce_functions_form.php'; require_once '../../shared/code/tce_functions_tcecode.php'; require_once '../../shared/code/tce_functions_auth_sql.php'; if (isset($selectcategory)) { $changecategory = 1; } if (isset($testlog_id)) { $testlog_id = intval($testlog_id); } if (!isset($testlog_comment)) { $testlog_comment = ''; } if (isset($_REQUEST['test_id']) and $_REQUEST['test_id'] > 0) { $test_id = intval($_REQUEST['test_id']); // check user's authorization if (!F_isAuthorizedUser(K_TABLE_TESTS, 'test_id', $test_id, 'test_user_id')) { F_print_error('ERROR', $l['m_authorization_denied']); exit; } } else { $test_id = 0; } switch ($menu_mode) { case 'update': // Update if ($formstatus = F_check_form_fields()) { if (isset($testlog_score) and isset($max_score)) { // score cannot be greater than max_score $testlog_score = floatval($testlog_score); $max_score = floatval($max_score); if ($testlog_score > $max_score) {
/** * Add a new module if not exist. * @private */ private function addModule() { global $l, $db; require_once '../config/tce_config.php'; require_once '../code/tce_functions_auth_sql.php'; if (isset($this->level_data['module']['module_id']) and $this->level_data['module']['module_id'] > 0) { return; } // check if this module already exist $sql = 'SELECT module_id FROM ' . K_TABLE_MODULES . ' WHERE module_name=\'' . $this->level_data['module']['module_name'] . '\' LIMIT 1'; if ($r = F_db_query($sql, $db)) { if ($m = F_db_fetch_array($r)) { // get existing module ID if (!F_isAuthorizedUser(K_TABLE_MODULES, 'module_id', $m['module_id'], 'module_user_id')) { // unauthorized user $this->level_data['module']['module_id'] = false; } else { $this->level_data['module']['module_id'] = $m['module_id']; } } else { // insert new module $sql = 'INSERT INTO ' . K_TABLE_MODULES . ' ( module_name, module_enabled, module_user_id ) VALUES ( \'' . $this->level_data['module']['module_name'] . '\', \'' . $this->boolval[$this->level_data['module']['module_enabled']] . '\', \'' . $_SESSION['session_user_id'] . '\' )'; if (!($r = F_db_query($sql, $db))) { F_display_db_error(); } else { // get new module ID $this->level_data['module']['module_id'] = F_db_insert_id($db, K_TABLE_MODULES, 'module_id'); } } } else { F_display_db_error(); } }
} else { $subject_name = ''; } if (isset($_REQUEST['subject_description'])) { $subject_description = utrim($_REQUEST['subject_description']); } else { $subject_description = ''; } if ($subject_id > 0) { if ($changecategory == 0) { $sql = 'SELECT subject_module_id FROM ' . K_TABLE_SUBJECTS . ' WHERE subject_id=' . $subject_id . ' LIMIT 1'; if ($r = F_db_query($sql, $db)) { if ($m = F_db_fetch_array($r)) { $subject_module_id = $m['subject_module_id']; // check user's authorization for parent module if (!F_isAuthorizedUser(K_TABLE_MODULES, 'module_id', $subject_module_id, 'module_user_id') and !F_isAuthorizedUser(K_TABLE_SUBJECTS, 'subject_id', $subject_id, 'subject_user_id')) { F_print_error('ERROR', $l['m_authorization_denied']); exit; } } } else { F_display_db_error(); } } } else { $subject_id = 0; } switch ($menu_mode) { case 'delete': F_stripslashes_formfields(); // check if this record is used (test_log)
*/ require_once '../config/tce_config.php'; require_once '../../shared/code/tce_authorization.php'; require_once '../code/tce_functions_auth_sql.php'; require_once '../../shared/code/tce_functions_tcecode.php'; require_once '../../shared/config/tce_pdf.php'; require_once '../../shared/code/tcpdfex.php'; if (isset($_REQUEST['expmode']) and $_REQUEST['expmode'] > 0 and (isset($_REQUEST['module_id']) and $_REQUEST['module_id'] > 0) and (isset($_REQUEST['subject_id']) and $_REQUEST['subject_id'] > 0)) { $expmode = intval($_REQUEST['expmode']); $module_id = intval($_REQUEST['module_id']); $subject_id = intval($_REQUEST['subject_id']); } else { exit; } // check user's authorization for module if (!F_isAuthorizedUser(K_TABLE_MODULES, 'module_id', $module_id, 'module_user_id')) { exit; } $show_answers = true; if (isset($_REQUEST['hide_answers']) and $_REQUEST['hide_answers'] == 1) { $show_answers = false; } $doc_title = unhtmlentities($l['t_questions_list']); $doc_description = F_compact_string(unhtmlentities($l['hp_select_all_questions'])); $page_elements = 6; $qtype = array('S', 'M', 'T', '0'); // question types $qright = array(' ', '*'); // question types // --- create pdf document if ($l['a_meta_dir'] == 'rtl') {
/** * Copy selected question to another topic * @author Nicola Asuni * @since 2008-11-26 * @param $question_id (int) question ID * @param $new_subject_id (int) new subject ID */ function F_question_copy($question_id, $new_subject_id) { global $l, $db; require_once '../config/tce_config.php'; $question_id = intval($question_id); $new_subject_id = intval($new_subject_id); // check authorization $sql = 'SELECT subject_module_id FROM ' . K_TABLE_SUBJECTS . ' WHERE subject_id=' . $new_subject_id . ' LIMIT 1'; if ($r = F_db_query($sql, $db)) { if ($m = F_db_fetch_array($r)) { $subject_module_id = $m['subject_module_id']; // check user's authorization for parent module if (!F_isAuthorizedUser(K_TABLE_MODULES, 'module_id', $subject_module_id, 'module_user_id')) { return; } } } else { F_display_db_error(); return; } $q = F_question_get_data($question_id); if ($q !== false) { if (K_DATABASE_TYPE == 'ORACLE') { $chksql = 'dbms_lob.instr(question_description,\'' . F_escape_sql($db, $q['question_description']) . '\',1,1)>0'; } elseif (K_DATABASE_TYPE == 'MYSQL' and defined('K_MYSQL_QA_BIN_UNIQUITY') and K_MYSQL_QA_BIN_UNIQUITY) { $chksql = 'question_description=\'' . F_escape_sql($db, $q['question_description']) . '\' COLLATE utf8_bin'; } else { $chksql = 'question_description=\'' . F_escape_sql($db, $q['question_description']) . '\''; } if (F_check_unique(K_TABLE_QUESTIONS, $chksql . ' AND question_subject_id=' . $new_subject_id . '')) { $sql = 'START TRANSACTION'; if (!($r = F_db_query($sql, $db))) { F_display_db_error(false); break; } // adjust questions ordering if ($q['question_position'] > 0) { $sql = 'UPDATE ' . K_TABLE_QUESTIONS . ' SET question_position=question_position+1 WHERE question_subject_id=' . $new_subject_id . ' AND question_position>=' . $q['question_position'] . ''; if (!($r = F_db_query($sql, $db))) { F_display_db_error(false); F_db_query('ROLLBACK', $db); // rollback transaction } } $sql = 'INSERT INTO ' . K_TABLE_QUESTIONS . ' ( question_subject_id, question_description, question_explanation, question_type, question_difficulty, question_enabled, question_position, question_timer, question_fullscreen, question_inline_answers, question_auto_next ) VALUES ( ' . $new_subject_id . ', \'' . F_escape_sql($db, $q['question_description']) . '\', \'' . F_escape_sql($db, $q['question_explanation']) . '\', \'' . $q['question_type'] . '\', \'' . $q['question_difficulty'] . '\', \'' . $q['question_enabled'] . '\', ' . F_zero_to_null($q['question_position']) . ', \'' . $q['question_timer'] . '\', \'' . $q['question_fullscreen'] . '\', \'' . $q['question_inline_answers'] . '\', \'' . $q['question_auto_next'] . '\' )'; if (!($r = F_db_query($sql, $db))) { F_display_db_error(false); } else { $new_question_id = F_db_insert_id($db, K_TABLE_QUESTIONS, 'question_id'); } // copy associated answers $sql = 'SELECT * FROM ' . K_TABLE_ANSWERS . ' WHERE answer_question_id=' . $question_id . ''; if ($r = F_db_query($sql, $db)) { while ($m = F_db_fetch_array($r)) { $sqli = 'INSERT INTO ' . K_TABLE_ANSWERS . ' ( answer_question_id, answer_description, answer_explanation, answer_isright, answer_enabled, answer_position, answer_keyboard_key ) VALUES ( ' . $new_question_id . ', \'' . F_escape_sql($db, $m['answer_description']) . '\', \'' . F_escape_sql($db, $m['answer_explanation']) . '\', \'' . $m['answer_isright'] . '\', \'' . $m['answer_enabled'] . '\', ' . F_zero_to_null($m['answer_position']) . ', ' . F_empty_to_null($m['answer_keyboard_key']) . ' )'; if (!($ri = F_db_query($sqli, $db))) { F_display_db_error(false); F_db_query('ROLLBACK', $db); // rollback transaction } } } else { F_display_db_error(); } $sql = 'COMMIT'; if (!($r = F_db_query($sql, $db))) { F_display_db_error(false); break; } } } }
$question_explanation = ''; } $qtype = array('S', 'M', 'T', 'O'); // question types // check user's authorization if (isset($_REQUEST['question_id']) and $_REQUEST['question_id'] > 0) { $question_id = intval($_REQUEST['question_id']); $sql = 'SELECT subject_module_id,question_subject_id FROM ' . K_TABLE_SUBJECTS . ', ' . K_TABLE_QUESTIONS . ' WHERE subject_id=question_subject_id AND question_id=' . $question_id . ' LIMIT 1'; if ($r = F_db_query($sql, $db)) { if ($m = F_db_fetch_array($r)) { // check user's authorization for parent module if (!F_isAuthorizedUser(K_TABLE_MODULES, 'module_id', $m['subject_module_id'], 'module_user_id')) { F_print_error('ERROR', $l['m_authorization_denied']); exit; } } } else { F_display_db_error(); } } switch ($menu_mode) { case 'delete': F_stripslashes_formfields(); // check if this record is used (test_log) if (!F_check_unique(K_TABLE_TESTS_LOGS, 'testlog_question_id=' . $question_id . '')) { //this record will be only disabled and not deleted because it's used $sql = 'UPDATE ' . K_TABLE_QUESTIONS . ' SET
$answer_explanation = ''; } $qtype = array('S', 'M', 'T', 'O'); // question types // check user's authorization if ($answer_id > 0) { $sql = 'SELECT subject_module_id,question_subject_id,answer_question_id FROM ' . K_TABLE_SUBJECTS . ', ' . K_TABLE_QUESTIONS . ', ' . K_TABLE_ANSWERS . ' WHERE subject_id=question_subject_id AND question_id=answer_question_id AND answer_id=' . $answer_id . ' LIMIT 1'; if ($r = F_db_query($sql, $db)) { if ($m = F_db_fetch_array($r)) { // check user's authorization for parent module if (!F_isAuthorizedUser(K_TABLE_MODULES, 'module_id', $m['subject_module_id'], 'module_user_id') and !F_isAuthorizedUser(K_TABLE_SUBJECTS, 'subject_id', $m['question_subject_id'], 'subject_user_id')) { F_print_error('ERROR', $l['m_authorization_denied']); exit; } } } else { F_display_db_error(); } } switch ($menu_mode) { case 'delete': F_stripslashes_formfields(); // check if this record is used (test_log) if (!F_check_unique(K_TABLE_LOG_ANSWER, 'logansw_answer_id=' . $answer_id . '')) { //this record will be only disabled and not deleted because it's used $sql = 'UPDATE ' . K_TABLE_ANSWERS . ' SET
$ssl_enabled = F_getBoolean($ssl_enabled); } if (isset($ssl_name)) { $ssl_name = utrim($ssl_name); } else { $ssl_name = ''; } if (isset($ssl_user_id)) { $ssl_user_id = intval($ssl_user_id); } else { $ssl_user_id = intval($_SESSION['session_user_id']); } if (isset($_REQUEST['ssl_id']) and $_REQUEST['ssl_id'] > 0) { $ssl_id = intval($_REQUEST['ssl_id']); // check user's authorization for this certificate if (!F_isAuthorizedUser(K_TABLE_SSLCERTS, 'ssl_id', $ssl_id, 'ssl_user_id')) { F_print_error('ERROR', $l['m_authorization_denied']); exit; } } else { $ssl_id = 0; } // extact hash and end date from uploaded file $ssl_hash = ''; $ssl_end_date = ''; if (isset($_FILES['userfile']['name']) and !empty($_FILES['userfile']['name'])) { require_once '../code/tce_functions_upload.php'; // upload file $uploadedfile = F_upload_file('userfile', K_PATH_CACHE); if ($uploadedfile !== false) { $cert = file_get_contents(K_PATH_CACHE . $uploadedfile);
/** * Returns raw statistic array for the selected test. * @param $test_id (int) test ID. * @param $group_id (int) group ID - if greater than zero, filter stats for the specified user group. * @param $user_id (int) user ID - if greater than zero, filter stats for the specified user. * @param $startdate (int) start date ID - if greater than zero, filter stats for the specified starting date * @param $enddate (int) end date ID - if greater than zero, filter stats for the specified ending date * @param $testuser_id (int) test-user ID - if greater than zero, filter stats for the specified test-user. * @param $data (array) Array of existing data to be merged with the current one. * @param $pubmode (boolean) If true filter the results for the public interface. * return $data array containing test statistics. */ function F_getRawTestStat($test_id, $group_id = 0, $user_id = 0, $startdate = 0, $enddate = 0, $testuser_id = 0, $data = array(), $pubmode = false) { require_once '../config/tce_config.php'; require_once '../../shared/code/tce_functions_authorization.php'; require_once '../../shared/code/tce_functions_test.php'; global $db, $l; $test_id = intval($test_id); $group_id = intval($group_id); $user_id = intval($user_id); $testuser_id = intval($testuser_id); // query to calculate total number of questions $sqltot = K_TABLE_TEST_USER . ', ' . K_TABLE_TESTS_LOGS; $sqltb = K_TABLE_TEST_USER . ', ' . K_TABLE_TESTS_LOGS . ', ' . K_TABLE_ANSWERS . ', ' . K_TABLE_LOG_ANSWER; $sqlm = K_TABLE_TEST_USER . ', ' . K_TABLE_TESTS_LOGS . ', ' . K_TABLE_QUESTIONS . ', ' . K_TABLE_SUBJECTS . ', ' . K_TABLE_MODULES . ''; // apply filters $sqlw = 'WHERE testlog_testuser_id=testuser_id'; $sqlansw = 'WHERE logansw_answer_id=answer_id AND logansw_testlog_id=testlog_id AND testlog_testuser_id=testuser_id'; if ($test_id > 0) { $sqlw .= ' AND testuser_test_id=' . $test_id . ''; $sqlansw .= ' AND testuser_test_id=' . $test_id . ''; } elseif ($pubmode) { $test_ids_results = F_getTestIDResults($test_id, $user_id); $sqlw .= ' AND testuser_test_id IN (' . $test_ids_results . ')'; $sqlansw .= ' AND testuser_test_id IN (' . $test_ids_results . ')'; } if ($user_id > 0) { $sqltot .= ', ' . K_TABLE_USERS; $sqltb .= ', ' . K_TABLE_USERS; $sqlm .= ', ' . K_TABLE_USERS; $sqlw .= ' AND testuser_user_id=user_id AND user_id=' . $user_id . ''; $sqlansw .= ' AND testuser_user_id=user_id AND user_id=' . $user_id . ''; if ($testuser_id > 0) { $sqlw .= ' AND testuser_id=' . $testuser_id . ''; $sqlansw .= ' AND testuser_id=' . $testuser_id . ''; } } elseif ($group_id > 0) { $sqltot .= ', ' . K_TABLE_USERS . ', ' . K_TABLE_USERGROUP; $sqltb .= ', ' . K_TABLE_USERS . ', ' . K_TABLE_USERGROUP; $sqlm .= ', ' . K_TABLE_USERS . ', ' . K_TABLE_USERGROUP; $sqlw .= ' AND testuser_user_id=user_id AND usrgrp_user_id=user_id AND usrgrp_group_id=' . $group_id . ''; $sqlansw .= ' AND testuser_user_id=user_id AND usrgrp_user_id=user_id AND usrgrp_group_id=' . $group_id . ''; } if (!empty($startdate)) { $startdate_time = strtotime($startdate); $startdate = date(K_TIMESTAMP_FORMAT, $startdate_time); $sqlw .= ' AND testuser_creation_time>=\'' . $startdate . '\''; $sqlansw .= ' AND testuser_creation_time>=\'' . $startdate . '\''; } if (!empty($enddate)) { $enddate_time = strtotime($enddate); $enddate = date(K_TIMESTAMP_FORMAT, $enddate_time); $sqlw .= ' AND testuser_creation_time<=\'' . $enddate . '\''; $sqlansw .= ' AND testuser_creation_time<=\'' . $enddate . '\''; } // check if a specific test is selected or not if ($test_id == 0) { $test_ids = array(); $sqlt = 'SELECT testuser_test_id FROM ' . $sqltot . ' ' . $sqlw . ' GROUP BY testuser_test_id ORDER BY testuser_test_id'; if ($rt = F_db_query($sqlt, $db)) { while ($mt = F_db_fetch_assoc($rt)) { // check user's authorization if (F_isAuthorizedUser(K_TABLE_TESTS, 'test_id', $mt['testuser_test_id'], 'test_user_id')) { $test_ids[] = $mt['testuser_test_id']; } } } else { F_display_db_error(); } foreach ($test_ids as $tid) { // select test IDs $data = F_getRawTestStat($tid, $group_id, $user_id, $startdate, $enddate, $testuser_id, $data); } return $data; } $testdata = F_getTestData($test_id); // array to be returned if (!isset($data['qstats'])) { // total number of questions $data['qstats'] = array('recurrence' => 0, 'recurrence_perc' => 0, 'average_score' => 0, 'average_score_perc' => 0, 'average_time' => 0, 'right' => 0, 'right_perc' => 0, 'wrong' => 0, 'wrong_perc' => 0, 'unanswered' => 0, 'unanswered_perc' => 0, 'undisplayed' => 0, 'undisplayed_perc' => 0, 'unrated' => 0, 'unrated_perc' => 0, 'qnum' => 0, 'module' => array()); } $sql = 'SELECT module_id, subject_id, question_id, module_name, subject_name, subject_description, question_description,'; if ($user_id > 0 and $testuser_id > 0) { $sql .= ' testlog_score, testlog_user_ip, testlog_display_time, testlog_change_time, testlog_reaction_time, testlog_answer_text, question_type, question_explanation,'; } $sql .= ' COUNT(question_id) AS recurrence, AVG(testlog_score) AS average_score, AVG(testlog_change_time - testlog_display_time) AS average_time, MIN(question_type) AS question_type, MIN(question_difficulty) AS question_difficulty'; $sql .= ' FROM ' . $sqlm; $sql .= ' WHERE testlog_testuser_id=testuser_id AND question_id=testlog_question_id AND subject_id=question_subject_id AND module_id=subject_module_id'; if ($test_id > 0) { $sql .= ' AND testuser_test_id=' . $test_id . ''; } if ($testuser_id > 0) { $sql .= ' AND testuser_id=' . $testuser_id . ''; } if ($user_id > 0) { $sql .= ' AND testuser_user_id=user_id AND user_id=' . $user_id . ''; } elseif ($group_id > 0) { $sql .= ' AND testuser_user_id=user_id AND usrgrp_user_id=user_id AND usrgrp_group_id=' . $group_id . ''; } if (!empty($startdate)) { $sql .= ' AND testuser_creation_time>=\'' . $startdate . '\''; } if (!empty($enddate)) { $sql .= ' AND testuser_creation_time<=\'' . $enddate . '\''; } $sql .= ' GROUP BY module_id, subject_id, question_id, module_name, subject_name, subject_description, question_description'; if ($user_id > 0 and $testuser_id > 0) { $sql .= ', testlog_score, testlog_user_ip, testlog_display_time, testlog_change_time, testlog_reaction_time, testlog_answer_text, question_type, question_explanation'; } else { $sql .= ' ORDER BY module_name, subject_name, question_description'; } if ($r = F_db_query($sql, $db)) { while ($m = F_db_fetch_array($r)) { if (!isset($data['qstats']['module']['\'' . $m['module_id'] . '\''])) { $data['qstats']['module']['\'' . $m['module_id'] . '\''] = array('id' => $m['module_id'], 'name' => $m['module_name'], 'recurrence' => 0, 'recurrence_perc' => 0, 'average_score' => 0, 'average_score_perc' => 0, 'average_time' => 0, 'right' => 0, 'right_perc' => 0, 'wrong' => 0, 'wrong_perc' => 0, 'unanswered' => 0, 'unanswered_perc' => 0, 'undisplayed' => 0, 'undisplayed_perc' => 0, 'unrated' => 0, 'unrated_perc' => 0, 'qnum' => 0, 'subject' => array()); } if (!isset($data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\''])) { $data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\''] = array('id' => $m['subject_id'], 'name' => $m['subject_name'], 'description' => $m['subject_description'], 'recurrence' => 0, 'recurrence_perc' => 0, 'average_score' => 0, 'average_score_perc' => 0, 'average_time' => 0, 'right' => 0, 'right_perc' => 0, 'wrong' => 0, 'wrong_perc' => 0, 'unanswered' => 0, 'unanswered_perc' => 0, 'undisplayed' => 0, 'undisplayed_perc' => 0, 'unrated' => 0, 'unrated_perc' => 0, 'qnum' => 0, 'question' => array()); } $question_max_score = $testdata['test_score_right'] * $m['question_difficulty']; $question_half_score = $question_max_score / 2; $qright = F_count_rows($sqltot, $sqlw . ' AND testlog_question_id=' . $m['question_id'] . ' AND testlog_score>' . $question_half_score . ''); $qwrong = F_count_rows($sqltot, $sqlw . ' AND testlog_question_id=' . $m['question_id'] . ' AND testlog_score<=' . $question_half_score . ''); $qunanswered = F_count_rows($sqltot, $sqlw . ' AND testlog_question_id=' . $m['question_id'] . ' AND testlog_change_time IS NULL'); $qundisplayed = F_count_rows($sqltot, $sqlw . ' AND testlog_question_id=' . $m['question_id'] . ' AND testlog_display_time IS NULL'); $qunrated = F_count_rows($sqltot, $sqlw . ' AND testlog_question_id=' . $m['question_id'] . ' AND testlog_score IS NULL'); if (stripos($m['average_time'], ':') !== FALSE) { // PostgreSQL returns formatted time, while MySQL returns the number of seconds $m['average_time'] = strtotime($m['average_time']); } $num_all_answers = F_count_rows($sqltb, $sqlansw . ' AND testlog_question_id=' . $m['question_id']); if (!isset($data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['question']['\'' . $m['question_id'] . '\''])) { $data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['question']['\'' . $m['question_id'] . '\''] = array('id' => $m['question_id'], 'description' => $m['question_description'], 'type' => $m['question_type'], 'difficulty' => $m['question_difficulty'], 'recurrence' => 0, 'recurrence_perc' => 0, 'average_score' => 0, 'average_score_perc' => 0, 'average_time' => 0, 'right' => 0, 'right_perc' => 0, 'wrong' => 0, 'wrong_perc' => 0, 'unanswered' => 0, 'unanswered_perc' => 0, 'undisplayed' => 0, 'undisplayed_perc' => 0, 'unrated' => 0, 'unrated_perc' => 0, 'qnum' => 0, 'anum' => 0, 'answer' => array()); } // average score ratio if ($question_max_score > 0) { $average_score_perc = $m['average_score'] / $question_max_score; } else { $average_score_perc = 0; } // sum values for questions $data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['question']['\'' . $m['question_id'] . '\'']['qnum'] += 1; $data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['question']['\'' . $m['question_id'] . '\'']['recurrence'] += $m['recurrence']; $data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['question']['\'' . $m['question_id'] . '\'']['average_score'] += $m['average_score']; $data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['question']['\'' . $m['question_id'] . '\'']['average_score_perc'] += $average_score_perc; $data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['question']['\'' . $m['question_id'] . '\'']['average_time'] += $m['average_time']; $data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['question']['\'' . $m['question_id'] . '\'']['right'] += $qright; $data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['question']['\'' . $m['question_id'] . '\'']['wrong'] += $qwrong; $data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['question']['\'' . $m['question_id'] . '\'']['unanswered'] += $qunanswered; $data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['question']['\'' . $m['question_id'] . '\'']['undisplayed'] += $qundisplayed; $data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['question']['\'' . $m['question_id'] . '\'']['unrated'] += $qunrated; $data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['question']['\'' . $m['question_id'] . '\'']['anum'] += $num_all_answers; // sum values for subject $data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['qnum'] += 1; $data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['recurrence'] += $m['recurrence']; $data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['average_score'] += $m['average_score']; $data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['average_score_perc'] += $average_score_perc; $data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['average_time'] += $m['average_time']; $data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['right'] += $qright; $data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['wrong'] += $qwrong; $data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['unanswered'] += $qunanswered; $data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['undisplayed'] += $qundisplayed; $data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['unrated'] += $qunrated; // sum values for module $data['qstats']['module']['\'' . $m['module_id'] . '\'']['qnum'] += 1; $data['qstats']['module']['\'' . $m['module_id'] . '\'']['recurrence'] += $m['recurrence']; $data['qstats']['module']['\'' . $m['module_id'] . '\'']['average_score'] += $m['average_score']; $data['qstats']['module']['\'' . $m['module_id'] . '\'']['average_score_perc'] += $average_score_perc; $data['qstats']['module']['\'' . $m['module_id'] . '\'']['average_time'] += $m['average_time']; $data['qstats']['module']['\'' . $m['module_id'] . '\'']['right'] += $qright; $data['qstats']['module']['\'' . $m['module_id'] . '\'']['wrong'] += $qwrong; $data['qstats']['module']['\'' . $m['module_id'] . '\'']['unanswered'] += $qunanswered; $data['qstats']['module']['\'' . $m['module_id'] . '\'']['undisplayed'] += $qundisplayed; $data['qstats']['module']['\'' . $m['module_id'] . '\'']['unrated'] += $qunrated; // sum totals $data['qstats']['qnum'] += 1; $data['qstats']['recurrence'] += $m['recurrence']; $data['qstats']['average_score'] += $m['average_score']; $data['qstats']['average_score_perc'] += $average_score_perc; $data['qstats']['average_time'] += $m['average_time']; $data['qstats']['right'] += $qright; $data['qstats']['wrong'] += $qwrong; $data['qstats']['unanswered'] += $qunanswered; $data['qstats']['undisplayed'] += $qundisplayed; $data['qstats']['unrated'] += $qunrated; // get answer statistics $sqlaa = 'SELECT answer_id, answer_description, COUNT(answer_id) AS recurrence'; if ($user_id > 0 and $testuser_id > 0) { $sqlaa .= ', logansw_position, logansw_selected, answer_isright, answer_position, answer_explanation'; } $sqlaa .= ' FROM ' . $sqltb . ''; $sqlaw = ' WHERE testlog_testuser_id=testuser_id AND logansw_testlog_id=testlog_id AND answer_id=logansw_answer_id AND answer_question_id=' . $m['question_id'] . ''; if ($test_id > 0) { $sqlaw .= ' AND testuser_test_id=' . $test_id . ''; } if ($user_id > 0) { $sqlaw .= ' AND testuser_user_id=' . $user_id . ''; } if ($testuser_id > 0) { $sqlaw .= ' AND testuser_id=' . $testuser_id . ''; } if ($user_id > 0) { $sqlaw .= ' AND testuser_user_id=user_id AND user_id=' . $user_id . ''; } elseif ($group_id > 0) { $sqlaw .= ' AND testuser_user_id=user_id AND usrgrp_user_id=user_id AND usrgrp_group_id=' . $group_id . ''; } if (!empty($startdate)) { $sql .= ' AND testuser_creation_time>=\'' . $startdate . '\''; } if (!empty($enddate)) { $sql .= ' AND testuser_creation_time<=\'' . $enddate . '\''; } $sqlab = ' GROUP BY answer_id, answer_description'; if ($user_id > 0 and $testuser_id > 0) { $sqlab .= ', logansw_position, logansw_selected, answer_isright, answer_position, answer_explanation'; } $sqlab .= ' ORDER BY answer_description'; $sqla = $sqlaa . $sqlaw . $sqlab; if ($ra = F_db_query($sqla, $db)) { while ($ma = F_db_fetch_array($ra)) { $aright = F_count_rows($sqltb, $sqlaw . ' AND answer_id=' . $ma['answer_id'] . ' AND ((answer_isright=\'0\' AND logansw_selected=0) OR (answer_isright=\'1\' AND logansw_selected=1) OR (answer_position IS NOT NULL AND logansw_position IS NOT NULL AND answer_position=logansw_position))'); $awrong = F_count_rows($sqltb, $sqlaw . ' AND answer_id=' . $ma['answer_id'] . ' AND ((answer_isright=\'0\' AND logansw_selected=1) OR (answer_isright=\'1\' AND logansw_selected=0) OR (answer_position IS NOT NULL AND answer_position!=logansw_position))'); $aunanswered = F_count_rows($sqltb, $sqlaw . ' AND answer_id=' . $ma['answer_id'] . ' AND logansw_selected=-1'); if (!isset($data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['question']['\'' . $m['question_id'] . '\'']['answer']['\'' . $ma['answer_id'] . '\''])) { $data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['question']['\'' . $m['question_id'] . '\'']['answer']['\'' . $ma['answer_id'] . '\''] = array('id' => $ma['answer_id'], 'description' => $ma['answer_description'], 'recurrence' => 0, 'recurrence_perc' => 0, 'right' => 0, 'right_perc' => 0, 'wrong' => 0, 'wrong_perc' => 0, 'unanswered' => 0, 'unanswered_perc' => 0); } $data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['question']['\'' . $m['question_id'] . '\'']['answer']['\'' . $ma['answer_id'] . '\'']['recurrence'] += $ma['recurrence']; $data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['question']['\'' . $m['question_id'] . '\'']['answer']['\'' . $ma['answer_id'] . '\'']['right'] += $aright; $data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['question']['\'' . $m['question_id'] . '\'']['answer']['\'' . $ma['answer_id'] . '\'']['wrong'] += $awrong; $data['qstats']['module']['\'' . $m['module_id'] . '\'']['subject']['\'' . $m['subject_id'] . '\'']['question']['\'' . $m['question_id'] . '\'']['answer']['\'' . $ma['answer_id'] . '\'']['unanswered'] += $aunanswered; } } else { F_display_db_error(); } } } else { F_display_db_error(); } return $data; }
/** * Export all test results to CSV. * @author Nicola Asuni * @since 2006-03-30 * @param $test_id (int) Test ID * @param $group_id (int) Group ID * @param $order_field (string) ORDER BY portion of the SQL query * @return CSV data */ function F_csv_export_result_allusers($test_id, $group_id = 0, $order_field = "") { global $l, $db; require_once '../config/tce_config.php'; require_once '../../shared/code/tce_authorization.php'; require_once '../../shared/code/tce_functions_test_stats.php'; require_once 'tce_functions_user_select.php'; require_once '../code/tce_functions_statistics.php'; $test_id = intval($test_id); $group_id = intval($group_id); $order_field = F_escape_sql($order_field); // check user's authorization if (!F_isAuthorizedUser(K_TABLE_TESTS, 'test_id', $test_id, 'test_user_id')) { return ''; } if (!F_isAuthorizedEditorForGroup($group_id)) { return ''; } // statistical data $statsdata = array(); $statsdata['score'] = array(); $statsdata['right'] = array(); $statsdata['wrong'] = array(); $statsdata['unanswered'] = array(); $statsdata['undisplayed'] = array(); $statsdata['unrated'] = array(); $csv = ''; // CSV data to be returned // general data $csv .= 'TCExam Results Summary' . K_NEWLINE . K_NEWLINE; $csv .= 'version' . K_TAB . K_TCEXAM_VERSION . K_NEWLINE; $csv .= 'lang' . K_TAB . K_USER_LANG . K_NEWLINE; $csv .= 'date' . K_TAB . date(K_TIMESTAMP_FORMAT) . K_NEWLINE; $csv .= 'test_id' . K_TAB . $test_id . K_NEWLINE; $csv .= 'group_id' . K_TAB . $group_id . K_NEWLINE; $csv .= K_NEWLINE . K_NEWLINE; // separator // print column names $csv .= '#'; $csv .= K_TAB . $l['w_time_begin']; $csv .= K_TAB . $l['w_time_end']; $csv .= K_TAB . $l['w_time']; $csv .= K_TAB . $l['w_lastname']; $csv .= K_TAB . $l['w_firstname']; $csv .= K_TAB . $l['w_user']; $csv .= K_TAB . $l['w_passed']; $csv .= K_TAB . $l['w_score']; $csv .= K_TAB . $l['w_answers_right']; $csv .= K_TAB . $l['w_answers_wrong']; $csv .= K_TAB . $l['w_questions_unanswered']; $csv .= K_TAB . $l['w_questions_undisplayed']; $csv .= K_TAB . $l['w_questions_unrated']; $csv .= K_TAB . $l['w_comment']; $passed = 0; // output users stats $sqlr = 'SELECT testuser_id, testuser_creation_time, user_id, user_lastname, user_firstname, user_name, SUM(testlog_score) AS total_score, MAX(testlog_change_time) AS testuser_end_time FROM ' . K_TABLE_TESTS_LOGS . ', ' . K_TABLE_TEST_USER . ', ' . K_TABLE_USERS . ' WHERE testlog_testuser_id=testuser_id AND testuser_user_id=user_id AND testuser_test_id=' . $test_id . ''; if ($group_id > 0) { $sqlr .= ' AND testuser_user_id IN ( SELECT usrgrp_user_id FROM ' . K_TABLE_USERGROUP . ' WHERE usrgrp_group_id=' . $group_id . ' )'; } if ($_SESSION['session_user_level'] < K_AUTH_ADMINISTRATOR) { $sqlr .= ' AND (user_level<' . $_SESSION['session_user_level'] . ' OR user_id=' . $_SESSION['session_user_id'] . ')'; } $sqlr .= ' GROUP BY testuser_id, testuser_creation_time, user_id, user_lastname, user_firstname, user_name ORDER BY ' . $order_field . ''; if ($rr = F_db_query($sqlr, $db)) { $itemcount = 0; while ($mr = F_db_fetch_array($rr)) { $itemcount++; $csv .= K_NEWLINE . $itemcount; $csv .= K_TAB . $mr['testuser_creation_time']; $csv .= K_TAB . $mr['testuser_end_time']; $time_diff = strtotime($mr['testuser_end_time']) - strtotime($mr['testuser_creation_time']); //sec $time_diff = gmdate('H:i:s', $time_diff); $csv .= K_TAB . $time_diff; $csv .= K_TAB . $mr['user_lastname']; $csv .= K_TAB . $mr['user_firstname']; $csv .= K_TAB . $mr['user_name']; $usrtestdata = F_getUserTestStat($test_id, $mr['user_id']); $halfscore = $usrtestdata['max_score'] / 2; if ($usrtestdata['score_threshold'] > 0) { if ($usrtestdata['score'] >= $usrtestdata['score_threshold']) { $csv .= K_TAB . 'true'; $passed++; } else { $csv .= K_TAB . 'false'; } } else { $csv .= K_TAB; if ($usrtestdata['score'] > $halfscore) { $passed++; } } $csv .= K_TAB . $mr['total_score']; $csv .= K_TAB . $usrtestdata['right']; $csv .= K_TAB . $usrtestdata['wrong']; $csv .= K_TAB . $usrtestdata['unanswered']; $csv .= K_TAB . $usrtestdata['undisplayed']; $csv .= K_TAB . $usrtestdata['unrated']; $csv .= K_TAB . F_compact_string(htmlspecialchars($usrtestdata['comment'], ENT_NOQUOTES, $l['a_meta_charset'])); // collects data for descriptive statistics $statsdata['score'][] = $mr['total_score'] / $usrtestdata['max_score']; $statsdata['right'][] = $usrtestdata['right'] / $usrtestdata['all']; $statsdata['wrong'][] = $usrtestdata['wrong'] / $usrtestdata['all']; $statsdata['unanswered'][] = $usrtestdata['unanswered'] / $usrtestdata['all']; $statsdata['undisplayed'][] = $usrtestdata['undisplayed'] / $usrtestdata['all']; $statsdata['unrated'][] = $usrtestdata['unrated'] / $usrtestdata['all']; } } else { F_display_db_error(); } $csv .= K_NEWLINE; // separator // calculate statistics $stats = F_getArrayStatistics($statsdata); $excludestat = array('sum', 'variance'); $calcpercent = array('mean', 'median', 'mode', 'minimum', 'maximum', 'range', 'standard_deviation'); $csv .= K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . 'passed_total' . K_TAB . $passed . K_NEWLINE; $csv .= K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . 'passed_percent [%]' . K_TAB . round(100 * ($passed / $itemcount)) . K_NEWLINE; $csv .= K_NEWLINE; // separator $csv .= $l['w_statistics'] . K_NEWLINE; // separator // headers $csv .= K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . K_TAB; $csv .= $l['w_score'] . K_TAB; $csv .= $l['w_answers_right_th'] . K_TAB; $csv .= $l['w_answers_wrong_th'] . K_TAB; $csv .= $l['w_questions_unanswered_th'] . K_TAB; $csv .= $l['w_questions_undisplayed_th'] . K_TAB; $csv .= $l['w_questions_unrated'] . K_NEWLINE; foreach ($stats as $row => $columns) { if (!in_array($row, $excludestat)) { $csv .= K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . $l['w_' . $row] . K_TAB; $csv .= round($columns['score'], 3) . K_TAB; $csv .= round($columns['right'], 3) . K_TAB; $csv .= round($columns['wrong'], 3) . K_TAB; $csv .= round($columns['unanswered'], 3) . K_TAB; $csv .= round($columns['undisplayed'], 3) . K_TAB; $csv .= round($columns['unrated'], 3) . K_NEWLINE; if (in_array($row, $calcpercent)) { $csv .= K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . K_TAB . $row . ' [%]' . K_TAB; $csv .= round(100 * ($columns['score'] / $usrtestdata['max_score'])) . K_TAB; $csv .= round(100 * ($columns['right'] / $usrtestdata['all'])) . K_TAB; $csv .= round(100 * ($columns['wrong'] / $usrtestdata['all'])) . K_TAB; $csv .= round(100 * ($columns['unanswered'] / $usrtestdata['all'])) . K_TAB; $csv .= round(100 * ($columns['undisplayed'] / $usrtestdata['all'])) . K_TAB; $csv .= round(100 * ($columns['unrated'] / $usrtestdata['all'])) . K_NEWLINE; } } } return $csv; }
/** * Sends email test reports to users. * @author Nicola Asuni * @since 2005-02-24 * @param $test_id (int) TEST ID * @param $user_id (int) USER ID (0 means all users) * @param $group_id (int) GROUP ID (0 means all groups) * @param $mode (int) type of report to send: 0=detailed report; 1=summary report (without questions) */ function F_send_report_emails($test_id, $user_id = 0, $group_id = 0, $mode = 0) { global $l, $db; require_once '../config/tce_config.php'; require_once '../../shared/code/tce_functions_test.php'; require_once '../../shared/code/tce_functions_test_stats.php'; require_once '../../shared/code/tce_class_mailer.php'; require_once 'tce_functions_user_select.php'; $test_id = intval($test_id); $user_id = intval($user_id); $group_id = intval($group_id); $mode = intval($mode); if (!F_isAuthorizedUser(K_TABLE_TESTS, 'test_id', $test_id, 'test_user_id')) { return; } if (!F_isAuthorizedEditorForUser($user_id)) { return; } if (!F_isAuthorizedEditorForGroup($group_id)) { return; } // Instantiate C_mailer class $mail = new C_mailer(); //Load default values $mail->language = $l; $mail->Priority = $emailcfg['Priority']; $mail->ContentType = $emailcfg['ContentType']; $mail->Encoding = $emailcfg['Encoding']; $mail->WordWrap = $emailcfg['WordWrap']; $mail->Mailer = $emailcfg['Mailer']; $mail->Sendmail = $emailcfg['Sendmail']; $mail->UseMSMailHeaders = $emailcfg['UseMSMailHeaders']; $mail->Host = $emailcfg['Host']; $mail->Port = $emailcfg['Port']; $mail->Helo = $emailcfg['Helo']; $mail->SMTPAuth = $emailcfg['SMTPAuth']; $mail->SMTPSecure = $emailcfg['SMTPSecure']; $mail->Username = $emailcfg['Username']; $mail->Password = $emailcfg['Password']; $mail->Timeout = $emailcfg['Timeout']; $mail->SMTPDebug = $emailcfg['SMTPDebug']; $mail->PluginDir = $emailcfg['PluginDir']; $mail->Sender = $emailcfg['Sender']; $mail->From = $emailcfg['From']; $mail->FromName = $emailcfg['FromName']; if ($emailcfg['Reply']) { $mail->AddReplyTo($emailcfg['Reply'], $emailcfg['ReplyName']); } $mail->CharSet = $l['a_meta_charset']; if (!$mail->CharSet) { $mail->CharSet = $emailcfg['CharSet']; } $mail->Subject = $l['t_result_user']; $mail->IsHTML(TRUE); // Set message type to HTML. $email_num = 0; // count emails; if ($user_id == 0) { // for each user on selected test $sql = 'SELECT user_id, user_name, user_email, user_firstname, user_lastname, testuser_creation_time FROM ' . K_TABLE_TEST_USER . ', ' . K_TABLE_USERS . ' WHERE testuser_user_id=user_id AND testuser_test_id=' . $test_id . ' AND testuser_status>0'; if ($group_id > 0) { $sql .= ' AND testuser_user_id IN (SELECT usrgrp_user_id FROM ' . K_TABLE_USERGROUP . ' WHERE usrgrp_group_id=' . $group_id . ')'; } } else { // select only one test of one user $sql = 'SELECT user_id, user_name, user_email, user_firstname, user_lastname, testuser_creation_time FROM ' . K_TABLE_TEST_USER . ', ' . K_TABLE_USERS . ' WHERE testuser_user_id=user_id AND testuser_user_id=' . $user_id . ' AND testuser_test_id=' . $test_id . ' AND testuser_status>0 LIMIT 1'; } // get test data $testdata = F_getTestData($test_id); if ($r = F_db_query($sql, $db)) { while ($m = F_db_fetch_array($r)) { if (strlen($m['user_email']) > 3) { // get user's test stats $usrtestdata = F_getUserTestStat($test_id, $m['user_id']); // set HTML header $mail->Body = $emailcfg['MsgHeader']; // compose alternate TEXT message $mail->AltBody = '' . $l['t_result_user'] . ' [' . $m['testuser_creation_time'] . ']' . K_NEWLINE; $mail->AltBody .= $l['w_test'] . ': ' . $testdata['test_name'] . K_NEWLINE; $passmsg = ''; if ($testdata['test_score_threshold'] > 0) { $mail->AltBody .= $l['w_test_score_threshold'] . ': ' . $testdata['test_score_threshold']; if ($usrtestdata['score'] >= $testdata['test_score_threshold']) { $passmsg = ' - ' . $l['w_passed']; } else { $passmsg = ' - ' . $l['w_not_passed']; } $mail->AltBody .= K_NEWLINE; } $mail->AltBody .= $l['w_score'] . ': ' . $usrtestdata['score'] . ' (' . round(100 * $usrtestdata['score'] / $usrtestdata['max_score']) . '%)' . $passmsg . K_NEWLINE; $mail->AltBody .= $l['w_answers_right'] . ': ' . $usrtestdata['right'] . ' (' . round(100 * $usrtestdata['right'] / $usrtestdata['all']) . '%)' . K_NEWLINE; $mail->AltBody .= $l['w_answers_wrong'] . ': ' . $usrtestdata['wrong'] . ' (' . round(100 * $usrtestdata['wrong'] / $usrtestdata['all']) . '%)' . K_NEWLINE; $mail->AltBody .= $l['w_questions_unanswered'] . ': ' . $usrtestdata['unanswered'] . ' (' . round(100 * $usrtestdata['unanswered'] / $usrtestdata['all']) . '%)' . K_NEWLINE; $mail->AltBody .= $l['w_questions_undisplayed'] . ': ' . $usrtestdata['undisplayed'] . ' (' . round(100 * $usrtestdata['undisplayed'] / $usrtestdata['all']) . '%)' . K_NEWLINE; if ($mode == 0) { // create PDF doc $pdf_content = file_get_contents(K_PATH_HOST . K_PATH_TCEXAM . 'admin/code/tce_pdf_results.php?mode=3&testid=' . $test_id . '&groupid=0&userid=' . $m['user_id'] . '&email=' . md5(date('Y') . K_RANDOM_SECURITY . $test_id . $m['user_id'])); // attach doc $doc_name = 'test_' . date('Ymd', strtotime($m['testuser_creation_time'])) . '_' . $test_id . '_' . $m['user_id'] . '.pdf'; $mail->AddStringAttachment($pdf_content, $doc_name, $emailcfg['AttachmentsEncoding'], 'application/octet-stream'); $mail->AltBody .= K_NEWLINE . $l['w_attachment'] . ': ' . $doc_name . K_NEWLINE; } // convert alternate text to HTML $mail->Body .= str_replace(K_NEWLINE, '<br />' . K_NEWLINE, $mail->AltBody); // add HTML footer $mail->Body .= $emailcfg['MsgFooter']; //--- Elaborate user Templates --- $mail->Body = str_replace('#CHARSET#', $l['a_meta_charset'], $mail->Body); $mail->Body = str_replace('#LANG#', $l['a_meta_language'], $mail->Body); $mail->Body = str_replace('#LANGDIR#', $l['a_meta_dir'], $mail->Body); $mail->Body = str_replace('#EMAIL#', $m['user_email'], $mail->Body); $mail->Body = str_replace('#USERNAME#', htmlspecialchars($m['user_name'], ENT_NOQUOTES, $l['a_meta_charset']), $mail->Body); $mail->Body = str_replace('#USERFIRSTNAME#', htmlspecialchars($m['user_firstname'], ENT_NOQUOTES, $l['a_meta_charset']), $mail->Body); $mail->Body = str_replace('#USERLASTNAME#', htmlspecialchars($m['user_lastname'], ENT_NOQUOTES, $l['a_meta_charset']), $mail->Body); // add a "To" address $mail->AddAddress($m['user_email'], $m['user_name']); $email_num++; $progresslog = '' . $email_num . '. ' . $m['user_email'] . ' [' . $m['user_name'] . ']'; //output user data if (!$mail->Send()) { //send email to user $progresslog .= ' [' . $l['t_error'] . ']'; //display error message } $mail->ClearAddresses(); // Clear all addresses for next loop $mail->ClearAttachments(); // Clears all previously set filesystem, string, and binary attachments } else { $progresslog = '[' . $l['t_error'] . '] ' . $m['user_name'] . ': ' . $l['m_unknown_email'] . ''; //output user data } echo '' . $progresslog . '<br />' . K_NEWLINE; //output processed emails flush(); // force browser output } } else { F_display_db_error(false); } $mail->ClearAddresses(); // Clear all addresses for next loop $mail->ClearCustomHeaders(); // Clears all custom headers $mail->ClearAllRecipients(); // Clears all recipients assigned in the TO, CC and BCC $mail->ClearAttachments(); // Clears all previously set filesystem, string, and binary attachments $mail->ClearReplyTos(); // Clears all recipients assigned in the ReplyTo array return; }
/** * Import questions from TSV file (tab delimited text). * The format of TSV is the same obtained by exporting data from TCExam interface. * @param $tsvfile (string) TSV (tab delimited text) file name * @return boolean TRUE in case of success, FALSE otherwise */ function F_TSVQuestionImporter($tsvfile) { global $l, $db; require_once '../config/tce_config.php'; require_once '../../shared/code/tce_functions_auth_sql.php'; $qtype = array('S' => 1, 'M' => 2, 'T' => 3, 'O' => 4); // get file content as array $tsvrows = file($tsvfile, FILE_IGNORE_NEW_LINES); // array of TSV lines if ($tsvrows === FALSE) { return FALSE; } $current_module_id = 0; $current_subject_id = 0; $current_question_id = 0; $current_answer_id = 0; $questionhash = array(); // for each row while (list($item, $rowdata) = each($tsvrows)) { // get user data into array $qdata = explode("\t", $rowdata); switch ($qdata[0]) { case 'M': // MODULE $current_module_id = 0; if (!isset($qdata[2]) or empty($qdata[2])) { break; } $module_enabled = intval($qdata[1]); $module_name = F_escape_sql($db, F_tsv_to_text($qdata[2]), false); // check if this module already exist $sql = 'SELECT module_id FROM ' . K_TABLE_MODULES . ' WHERE module_name=\'' . $module_name . '\' LIMIT 1'; if ($r = F_db_query($sql, $db)) { if ($m = F_db_fetch_array($r)) { // get existing module ID if (!F_isAuthorizedUser(K_TABLE_MODULES, 'module_id', $m['module_id'], 'module_user_id')) { // unauthorized user $current_module_id = 0; } else { $current_module_id = $m['module_id']; } } else { // insert new module $sql = 'INSERT INTO ' . K_TABLE_MODULES . ' ( module_name, module_enabled, module_user_id ) VALUES ( \'' . $module_name . '\', \'' . $module_enabled . '\', \'' . $_SESSION['session_user_id'] . '\' )'; if (!($r = F_db_query($sql, $db))) { F_display_db_error(); } else { // get new module ID $current_module_id = F_db_insert_id($db, K_TABLE_MODULES, 'module_id'); } } } else { F_display_db_error(); } break; case 'S': // SUBJECT $current_subject_id = 0; if ($current_module_id == 0) { return; } if (!isset($qdata[2]) or empty($qdata[2])) { break; } $subject_enabled = intval($qdata[1]); $subject_name = F_escape_sql($db, F_tsv_to_text($qdata[2]), false); $subject_description = ''; if (isset($qdata[3])) { $subject_description = F_empty_to_null(F_tsv_to_text($qdata[3])); } // check if this subject already exist $sql = 'SELECT subject_id FROM ' . K_TABLE_SUBJECTS . ' WHERE subject_name=\'' . $subject_name . '\' AND subject_module_id=' . $current_module_id . ' LIMIT 1'; if ($r = F_db_query($sql, $db)) { if ($m = F_db_fetch_array($r)) { // get existing subject ID $current_subject_id = $m['subject_id']; } else { // insert new subject $sql = 'INSERT INTO ' . K_TABLE_SUBJECTS . ' ( subject_name, subject_description, subject_enabled, subject_user_id, subject_module_id ) VALUES ( \'' . $subject_name . '\', ' . $subject_description . ', \'' . $subject_enabled . '\', \'' . $_SESSION['session_user_id'] . '\', ' . $current_module_id . ' )'; if (!($r = F_db_query($sql, $db))) { F_display_db_error(); } else { // get new subject ID $current_subject_id = F_db_insert_id($db, K_TABLE_SUBJECTS, 'subject_id'); } } } else { F_display_db_error(); } break; case 'Q': // QUESTION $current_question_id = 0; if ($current_module_id == 0 or $current_subject_id == 0) { return; } if (!isset($qdata[5])) { break; } $question_enabled = intval($qdata[1]); $question_description = F_escape_sql($db, F_tsv_to_text($qdata[2]), false); $question_explanation = F_empty_to_null(F_tsv_to_text($qdata[3])); $question_type = $qtype[$qdata[4]]; $question_difficulty = intval($qdata[5]); if (isset($qdata[6])) { $question_position = F_zero_to_null($qdata[6]); } else { $question_position = F_zero_to_null(0); } if (isset($qdata[7])) { $question_timer = intval($qdata[7]); } else { $question_timer = 0; } if (isset($qdata[8])) { $question_fullscreen = intval($qdata[8]); } else { $question_fullscreen = 0; } if (isset($qdata[9])) { $question_inline_answers = intval($qdata[9]); } else { $question_inline_answers = 0; } if (isset($qdata[10])) { $question_auto_next = intval($qdata[10]); } else { $question_auto_next = 0; } // check if this question already exist $sql = 'SELECT question_id FROM ' . K_TABLE_QUESTIONS . ' WHERE '; if (K_DATABASE_TYPE == 'ORACLE') { $sql .= 'dbms_lob.instr(question_description,\'' . $question_description . '\',1,1)>0'; } elseif (K_DATABASE_TYPE == 'MYSQL' and K_MYSQL_QA_BIN_UNIQUITY) { $sql .= 'question_description=\'' . $question_description . '\' COLLATE utf8_bin'; } else { $sql .= 'question_description=\'' . $question_description . '\''; } $sql .= ' AND question_subject_id=' . $current_subject_id . ' LIMIT 1'; if ($r = F_db_query($sql, $db)) { if ($m = F_db_fetch_array($r)) { // get existing question ID $current_question_id = $m['question_id']; return; } } else { F_display_db_error(); } if (K_DATABASE_TYPE == 'MYSQL') { // this section is to avoid the problems on MySQL string comparison $maxkey = 240; $strkeylimit = min($maxkey, strlen($question_description)); $stop = $maxkey / 3; while (in_array(md5(strtolower(substr($current_subject_id . $question_description, 0, $strkeylimit))), $questionhash) and $stop > 0) { // a similar question was already imported, so we change it a little bit to avoid duplicate keys $question_description = '_' . $question_description; $strkeylimit = min($maxkey, $strkeylimit + 1); $stop--; // variable used to avoid infinite loop } if ($stop == 0) { F_print_error('ERROR', 'Unable to get unique question ID'); return; } } $sql = 'START TRANSACTION'; if (!($r = F_db_query($sql, $db))) { F_display_db_error(); } // insert question $sql = 'INSERT INTO ' . K_TABLE_QUESTIONS . ' ( question_subject_id, question_description, question_explanation, question_type, question_difficulty, question_enabled, question_position, question_timer, question_fullscreen, question_inline_answers, question_auto_next ) VALUES ( ' . $current_subject_id . ', \'' . $question_description . '\', ' . $question_explanation . ', \'' . $question_type . '\', \'' . $question_difficulty . '\', \'' . $question_enabled . '\', ' . $question_position . ', \'' . $question_timer . '\', \'' . $question_fullscreen . '\', \'' . $question_inline_answers . '\', \'' . $question_auto_next . '\' )'; if (!($r = F_db_query($sql, $db))) { F_display_db_error(false); } else { // get new question ID $current_question_id = F_db_insert_id($db, K_TABLE_QUESTIONS, 'question_id'); if (K_DATABASE_TYPE == 'MYSQL') { $questionhash[] = md5(strtolower(substr($current_subject_id . $question_description, 0, $strkeylimit))); } } $sql = 'COMMIT'; if (!($r = F_db_query($sql, $db))) { F_display_db_error(); } break; case 'A': // ANSWER $current_answer_id = 0; if ($current_module_id == 0 or $current_subject_id == 0 or $current_question_id == 0) { return; } if (!isset($qdata[4])) { break; } $answer_enabled = intval($qdata[1]); $answer_description = F_escape_sql($db, F_tsv_to_text($qdata[2]), false); $answer_explanation = F_empty_to_null(F_tsv_to_text($qdata[3])); $answer_isright = intval($qdata[4]); if (isset($qdata[5])) { $answer_position = F_zero_to_null($qdata[5]); } else { $answer_position = F_zero_to_null(0); } if (isset($qdata[6])) { $answer_keyboard_key = F_empty_to_null(F_tsv_to_text($qdata[6])); } else { $answer_keyboard_key = F_empty_to_null(''); } // check if this answer already exist $sql = 'SELECT answer_id FROM ' . K_TABLE_ANSWERS . ' WHERE '; if (K_DATABASE_TYPE == 'ORACLE') { $sql .= 'dbms_lob.instr(answer_description, \'' . $answer_description . '\',1,1)>0'; } elseif (K_DATABASE_TYPE == 'MYSQL' and K_MYSQL_QA_BIN_UNIQUITY) { $sql .= 'answer_description=\'' . $answer_description . '\' COLLATE utf8_bin'; } else { $sql .= 'answer_description=\'' . $answer_description . '\''; } $sql .= ' AND answer_question_id=' . $current_question_id . ' LIMIT 1'; if ($r = F_db_query($sql, $db)) { if ($m = F_db_fetch_array($r)) { // get existing subject ID $current_answer_id = $m['answer_id']; } else { $sql = 'START TRANSACTION'; if (!($r = F_db_query($sql, $db))) { F_display_db_error(); } $sql = 'INSERT INTO ' . K_TABLE_ANSWERS . ' ( answer_question_id, answer_description, answer_explanation, answer_isright, answer_enabled, answer_position, answer_keyboard_key ) VALUES ( ' . $current_question_id . ', \'' . $answer_description . '\', ' . $answer_explanation . ', \'' . $answer_isright . '\', \'' . $answer_enabled . '\', ' . $answer_position . ', ' . $answer_keyboard_key . ' )'; if (!($r = F_db_query($sql, $db))) { F_display_db_error(false); F_db_query('ROLLBACK', $db); } else { // get new answer ID $current_answer_id = F_db_insert_id($db, K_TABLE_ANSWERS, 'answer_id'); } $sql = 'COMMIT'; if (!($r = F_db_query($sql, $db))) { F_display_db_error(); } } } else { F_display_db_error(); } break; } // end of switch } // end of while return TRUE; }
/** * Sends email test reports to users. * @author Nicola Asuni * @since 2005-02-24 * @param $test_id (int) TEST ID * @param $user_id (int) USER ID (0 means all users) * @param $testuser_id (int) test-user ID - if greater than zero, filter stats for the specified test-user. * @param $group_id (int) GROUP ID (0 means all groups) * @param $startdate (int) start date ID - if greater than zero, filter stats for the specified starting date * @param $enddate (int) end date ID - if greater than zero, filter stats for the specified ending date * @param $mode (int) type of report to send: 0=detailed report; 1=summary report (without questions) * @param $display_mode display (int) mode: 0 = disabled; 1 = minimum; 2 = module; 3 = subject; 4 = question; 5 = answer. * @param $show_graph (boolean) If true display the score graph. */ function F_send_report_emails($test_id, $user_id = 0, $testuser_id = 0, $group_id = 0, $startdate = 0, $enddate = 0, $mode = 0, $display_mode = 1, $show_graph = false) { global $l, $db; require_once '../config/tce_config.php'; require_once '../../shared/code/tce_functions_test.php'; require_once '../../shared/code/tce_functions_test_stats.php'; require_once '../../shared/code/tce_class_mailer.php'; require_once 'tce_functions_user_select.php'; $mode = intval($mode); if ($test_id > 0) { $test_id = intval($test_id); if (!F_isAuthorizedUser(K_TABLE_TESTS, 'test_id', $test_id, 'test_user_id')) { return; } } else { $test_id = 0; } if ($user_id > 0) { $user_id = intval($user_id); } else { $user_id = 0; } if ($testuser_id > 0) { $testuser_id = intval($testuser_id); } else { $testuser_id = 0; } if ($group_id > 0) { $group_id = intval($group_id); } else { $group_id = 0; } if (!empty($startdate)) { $startdate_time = strtotime($startdate); $startdate = date(K_TIMESTAMP_FORMAT, $startdate_time); } else { $startdate = ''; } if (!empty($enddate)) { $enddate_time = strtotime($enddate); $enddate = date(K_TIMESTAMP_FORMAT, $enddate_time); } else { $enddate = ''; } // Instantiate C_mailer class $mail = new C_mailer(); //Load default values $mail->language = $l; $mail->Priority = $emailcfg['Priority']; $mail->ContentType = $emailcfg['ContentType']; $mail->Encoding = $emailcfg['Encoding']; $mail->WordWrap = $emailcfg['WordWrap']; $mail->Mailer = $emailcfg['Mailer']; $mail->Sendmail = $emailcfg['Sendmail']; $mail->UseMSMailHeaders = $emailcfg['UseMSMailHeaders']; $mail->Host = $emailcfg['Host']; $mail->Port = $emailcfg['Port']; $mail->Helo = $emailcfg['Helo']; $mail->SMTPAuth = $emailcfg['SMTPAuth']; $mail->SMTPSecure = $emailcfg['SMTPSecure']; $mail->Username = $emailcfg['Username']; $mail->Password = $emailcfg['Password']; $mail->Timeout = $emailcfg['Timeout']; $mail->SMTPDebug = $emailcfg['SMTPDebug']; $mail->PluginDir = $emailcfg['PluginDir']; $mail->Sender = $emailcfg['Sender']; $mail->From = $emailcfg['From']; $mail->FromName = $emailcfg['FromName']; if ($emailcfg['Reply']) { $mail->AddReplyTo($emailcfg['Reply'], $emailcfg['ReplyName']); } $mail->CharSet = $l['a_meta_charset']; if (!$mail->CharSet) { $mail->CharSet = $emailcfg['CharSet']; } $mail->Subject = $l['t_result_user']; $mail->IsHTML(TRUE); // Set message type to HTML. $email_num = 0; // count emails; // get all data $data = F_getAllUsersTestStat($test_id, $group_id, $user_id, $startdate, $enddate, 'total_score', false, $display_mode); foreach ($data['testuser'] as $tu) { if (strlen($tu['user_email']) > 3) { // set HTML header $mail->Body = $emailcfg['MsgHeader']; // compose alternate TEXT message $mail->AltBody = '' . $l['t_result_user'] . ' [' . $tu['testuser_creation_time'] . ']' . K_NEWLINE; $mail->AltBody .= $l['w_test'] . ': ' . $tu['test']['test_name'] . K_NEWLINE; $passmsg = ''; if ($tu['test']['test_score_threshold'] > 0) { $mail->AltBody .= $l['w_test_score_threshold'] . ': ' . $tu['test']['test_score_threshold']; if ($tu['total_score'] >= $tu['test']['test_score_threshold']) { $passmsg = ' - ' . $l['w_passed']; } else { $passmsg = ' - ' . $l['w_not_passed']; } $mail->AltBody .= K_NEWLINE; } $mail->AltBody .= $l['w_score'] . ': ' . F_formatFloat($tu['total_score']) . ' ' . F_formatPercentage($tu['total_score_perc'], false) . $passmsg . K_NEWLINE; if ($display_mode > 0) { $mail->AltBody .= $l['w_answers_right'] . ': ' . $tu['right'] . ' ' . F_formatPercentage($tu['right_perc'], false) . K_NEWLINE; $mail->AltBody .= $l['w_answers_wrong'] . ': ' . $tu['wrong'] . ' ' . F_formatPercentage($tu['wrong_perc'], false) . K_NEWLINE; $mail->AltBody .= $l['w_questions_unanswered'] . ': ' . $tu['unanswered'] . ' ' . F_formatPercentage($tu['unanswered_perc'], false) . K_NEWLINE; $mail->AltBody .= $l['w_questions_undisplayed'] . ': ' . $tu['undisplayed'] . ' ' . F_formatPercentage($tu['undisplayed_perc'], false) . K_NEWLINE; } if ($mode == 0) { $pdfkey = getPasswordHash(date('Y') . $tu['id'] . K_RANDOM_SECURITY . $tu['test']['test_id'] . date('m') . $tu['user_id']); // create PDF doc $mode = 3; $pdf_content = file_get_contents(K_PATH_HOST . K_PATH_TCEXAM . 'admin/code/tce_pdf_results.php?mode=' . $mode . '&diplay_mode=' . $display_mode . '&show_graph=' . $show_graph . '&test_id=' . $tu['test']['test_id'] . '&user_id=' . $tu['user_id'] . '&testuser_id=' . $tu['id'] . '&email=' . $pdfkey); // set PDF document file name $doc_name = 'tcexam_report'; $doc_name .= '_' . $mode; $doc_name .= '_0'; $doc_name .= '_' . $tu['test']['test_id']; $doc_name .= '_0'; $doc_name .= '_' . $tu['user_id']; $doc_name .= '_' . $tu['id']; $doc_name .= '.pdf'; // attach document $mail->AddStringAttachment($pdf_content, $doc_name, $emailcfg['AttachmentsEncoding'], 'application/octet-stream'); $mail->AltBody .= K_NEWLINE . $l['w_attachment'] . ': ' . $doc_name . K_NEWLINE; } // convert alternate text to HTML $mail->Body .= str_replace(K_NEWLINE, '<br />' . K_NEWLINE, $mail->AltBody); // add HTML footer $mail->Body .= $emailcfg['MsgFooter']; //--- Elaborate user Templates --- $mail->Body = str_replace('#CHARSET#', $l['a_meta_charset'], $mail->Body); $mail->Body = str_replace('#LANG#', $l['a_meta_language'], $mail->Body); $mail->Body = str_replace('#LANGDIR#', $l['a_meta_dir'], $mail->Body); $mail->Body = str_replace('#EMAIL#', $tu['user_email'], $mail->Body); $mail->Body = str_replace('#USERNAME#', htmlspecialchars($tu['user_name'], ENT_NOQUOTES, $l['a_meta_charset']), $mail->Body); $mail->Body = str_replace('#USERFIRSTNAME#', htmlspecialchars($tu['user_firstname'], ENT_NOQUOTES, $l['a_meta_charset']), $mail->Body); $mail->Body = str_replace('#USERLASTNAME#', htmlspecialchars($tu['user_lastname'], ENT_NOQUOTES, $l['a_meta_charset']), $mail->Body); // add a "To" address $mail->AddAddress($tu['user_email'], $tu['user_name']); $email_num++; $progresslog = '' . $email_num . '. ' . $tu['user_email'] . ' [' . $tu['user_name'] . ']'; //output user data if (!$mail->Send()) { //send email to user $progresslog .= ' [' . $l['t_error'] . ']'; //display error message } $mail->ClearAddresses(); // Clear all addresses for next loop $mail->ClearAttachments(); // Clears all previously set filesystem, string, and binary attachments } else { $progresslog = '[' . $l['t_error'] . '] ' . $tu['user_name'] . ': ' . $l['m_unknown_email'] . ''; //output user data } echo $progresslog . '<br />' . K_NEWLINE; //output processed emails flush(); // force browser output } $mail->ClearAddresses(); // Clear all addresses for next loop $mail->ClearCustomHeaders(); // Clears all custom headers $mail->ClearAllRecipients(); // Clears all recipients assigned in the TO, CC and BCC $mail->ClearAttachments(); // Clears all previously set filesystem, string, and binary attachments $mail->ClearReplyTos(); // Clears all recipients assigned in the ReplyTo array return; }