static function download()
{
global $wpdb, $user_ID;
// only do this when the URL contains watupro_download_file=$file_id
if (empty($_GET['watupro_download_file']) or empty($_GET['id']) or !is_numeric($_GET['id'])) {
return true;
}
if (!is_user_logged_in()) {
wp_die(__('Only logged in users can download uploaded files.', 'watupro'));
}
// select the uploaded file
$file = $wpdb->get_row($wpdb->prepare("SELECT ID, user_id, user_answer_id, filename, filesize, filetype \n\t\t\tFROM " . WATUPRO_USER_FILES . " WHERE ID=%d", $_GET['id']));
if (empty($file->ID)) {
wp_die(__('The file has been deleted.', 'watupro'));
}
// check access
if ($file->user_id != $user_ID) {
if (!current_user_can(WATUPRO_MANAGE_CAPS)) {
wp_die(__('You can only download your own files.', 'watupro'));
}
// manager. Let's see if he's allowed to see this file
$multiuser_access = 'all';
if (watupro_intel()) {
$multiuser_access = WatuPROIMultiUser::check_access('exams_access');
}
if ($multiuser_access == 'own') {
$exam_id = $wpdb->get_var($wpdb->prepare("SELECT exam_id FROM " . WATUPRO_STUDENT_ANSWERS . " WHERE\n\t\t\t\t\tID=%d", $file->user_answer_id));
$editor_id = $wpdb->get_var($wpdb->prepare("SELECT editor_id FROM \n\t\t\t\t\t" . WATUPRO_EXAMS . " WHERE ID=%d", $exam_id));
if ($editor_id != $user_ID) {
wp_die(__('You can download only files of your own students.', 'watupro'));
}
}
}
// all good, let's download
$content = $wpdb->get_var($wpdb->prepare("SELECT BINARY filecontents \n\t\t\tFROM " . WATUPRO_USER_FILES . " WHERE ID=%d", $file->ID));
header("Content-Length: " . strlen($content));
header("Content-Description: File Transfer");
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=\"" . $file->filename . "\"");
header("Content-Transfer-Encoding: binary");
echo $content;
exit;
}
function watupro_question_cats()
{
global $wpdb, $user_ID;
$multiuser_access = 'all';
if (watupro_intel()) {
$multiuser_access = WatuPROIMultiUser::check_access('qcats_access');
}
$error = false;
if (!empty($_POST['add'])) {
if (!WTPCategory::add($_POST['name'], $_POST['description'])) {
$error = __('Another category with this name already exists.', 'watupro');
}
}
if (!empty($_POST['save'])) {
if ($multiuser_access == 'own') {
$cat = $wpdb->get_row($wpdb->prepare("SELECT * FROM " . WATUPRO_QCATS . " WHERE ID=%d", $_POST['id']));
if ($cat->editor_id != $user_ID) {
wp_die(__('You can manage only your own categories', 'watupro'));
}
}
if (!WTPCategory::save($_POST['name'], $_POST['id'], $_POST['description'])) {
$error = __('Another category with this name already exists.', 'watupro');
}
}
if (!empty($_POST['del'])) {
if ($multiuser_access == 'own') {
$cat = $wpdb->get_row($wpdb->prepare("SELECT * FROM " . WATUPRO_QCATS . " WHERE ID=%d", $_POST['id']));
if ($cat->editor_id != $user_ID) {
wp_die(__('You can manage only your own categories', 'watupro'));
}
}
WTPCategory::delete($_POST['id']);
}
// select all question categories
$own_sql = $multiuser_access == 'own' ? $wpdb->prepare(" WHERE editor_id = %d ", $user_ID) : "";
$cats = $wpdb->get_results("SELECT * FROM " . WATUPRO_QCATS . " {$own_sql} ORDER BY ID");
if (@file_exists(get_stylesheet_directory() . '/watupro/question_cats.php')) {
require get_stylesheet_directory() . '/watupro/question_cats.php';
} else {
require WATUPRO_PATH . "/views/question_cats.php";
}
}
function watupro_default_grades()
{
global $wpdb, $user_ID;
$in_default_grades = true;
$percentage_based = intval(@$_GET['percentage_based']);
$exam = (object) array("ID" => 0, "name" => "", "grades_by_percent" => $percentage_based);
// check access
$multiuser_access = 'all';
$userid_sql = '';
if (watupro_intel()) {
$multiuser_access = WatuPROIMultiUser::check_access('exams_access');
}
// change the common gradecat design
if (!empty($_POST['save_design']) and $multiuser_access == 'all') {
update_option('watupro_gradecat_design', $_POST['gradecat_design']);
}
// prepare the default gradecat design
$gradecat_design = get_option('watupro_gradecat_design');
if (empty($gradecat_design)) {
$gradecat_design = "<p>" . __('For category <strong>%%CATEGORY%%</strong> you got grade <strong>%%GTITLE%%</strong>.', 'watupro') . "</p>\n\t\t\t<p>%%GDESC%%</p><hr>";
update_option('watupro_gradecat_design', $gradecat_design);
}
// select question categories
$cats = $wpdb->get_results("SELECT * FROM " . WATUPRO_QCATS . " WHERE name!='' ORDER BY name");
if (!empty($_POST['add'])) {
$wpdb->query($wpdb->prepare("INSERT INTO " . WATUPRO_GRADES . " SET\n\t\t\texam_id=0, gtitle=%s, gdescription=%s, gfrom=%s, gto=%s, certificate_id=%d, \n\t\t\tcat_id=%d, editor_id=%d, percentage_based=%d", $_POST['gtitle'], $_POST['gdescription'], $_POST['gfrom'], $_POST['gto'], @$_POST['certificate_id'], $_POST['cat_id'], $user_ID, $percentage_based));
}
if (!empty($_POST['del'])) {
if ($multiuser_access == 'own') {
$userid_sql = $wpdb->prepare(" AND editor_id=%d ", $user_ID);
}
$wpdb->query($wpdb->prepare("DELETE FROM " . WATUPRO_GRADES . " WHERE ID=%d {$userid_sql}", $_POST['id']));
}
if (!empty($_POST['save'])) {
if ($multiuser_access == 'own') {
$userid_sql = $wpdb->prepare(" AND editor_id=%d ", $user_ID);
}
$wpdb->query($wpdb->prepare("UPDATE " . WATUPRO_GRADES . " SET\n\t\t\tgtitle=%s, gdescription=%s, gfrom=%s, gto=%s, certificate_id=%d\n\t\t\tWHERE ID=%d {$userid_sql}", $_POST['gtitle'], $_POST['gdescription' . $_POST['id']], $_POST['gfrom'], $_POST['gto'], @$_POST['certificate_id'], $_POST['id']));
}
$cat_id = empty($_POST['cat_id']) ? 0 : $_POST['cat_id'];
// select all grades of the selected category
$grades = $wpdb->get_results($wpdb->prepare("SELECT * FROM " . WATUPRO_GRADES . " \n\t\tWHERE exam_id=0 AND cat_id=%d AND percentage_based=%d", $cat_id, $percentage_based));
// for the moment certificates will be used only on non-category grades
if (!$cat_id) {
// select certificates if any
$certificates = $wpdb->get_results("SELECT * FROM " . WATUPRO_CERTIFICATES . " ORDER BY title");
$cnt_certificates = sizeof($certificates);
}
if (@file_exists(get_stylesheet_directory() . '/watupro/grades.php')) {
require get_stylesheet_directory() . '/watupro/grades.php';
} else {
require WATUPRO_PATH . "/views/grades.php";
}
}
function watupro_user_certificates()
{
global $wpdb, $user_ID;
$certificate = $wpdb->get_row($wpdb->prepare("SELECT * FROM " . WATUPRO_CERTIFICATES . " WHERE ID=%d", $_GET['id']));
// check access
$multiuser_access = 'all';
if (watupro_intel()) {
$multiuser_access = WatuPROIMultiUser::check_access('certificates_access');
}
if ($multiuser_access == 'own') {
if ($certificate->editor_id != $user_ID) {
wp_die(__('You can manage only your own certificates', 'watupro'));
}
}
if (!empty($_GET['approve'])) {
$wpdb->query($wpdb->prepare("UPDATE " . WATUPRO_USER_CERTIFICATES . " SET pending_approval = 0 WHERE ID=%d", $_GET['user_certificate_id']));
// send email to user?
if ($certificate->approval_notify_user) {
WatuPROCertificate::approval_notify($certificate, $_GET['user_certificate_id']);
}
watupro_redirect("admin.php?page=watupro_user_certificates&id=" . $_GET['id']);
}
if (!empty($_GET['delete'])) {
$wpdb->query($wpdb->prepare("DELETE FROM " . WATUPRO_USER_CERTIFICATES . " WHERE ID=%d", $_GET['user_certificate_id']));
}
// select users
$users = $wpdb->get_results($wpdb->prepare("SELECT tUC.ID as user_certificate_id, tU.user_nicename as user_nicename, tU.user_email as user_email, \n\ttE.name as exam_name, tUC.pending_approval as pending_approval, tT.ID as taking_id, tT.date as taking_date, tT.result as taking_result,\n\ttE.ID as exam_id\n\tFROM " . WATUPRO_USER_CERTIFICATES . " tUC \n\tJOIN {$wpdb->users} tU ON tUC.user_id = tU.ID \n\tJOIN " . WATUPRO_TAKEN_EXAMS . " tT ON tT.user_id = tU.ID AND tT.ID = tUC.taking_id\n\tJOIN " . WATUPRO_EXAMS . " tE ON tE.ID = tT.exam_id AND tE.ID = tUC.exam_id\n\tWHERE tUC.certificate_id=%d\n\tORDER BY tT.ID DESC", $certificate->ID));
$dateformat = get_option('date_format');
$is_admin = true;
wp_enqueue_script('thickbox', null, array('jquery'));
wp_enqueue_style('thickbox.css', '/' . WPINC . '/js/thickbox/thickbox.css', null, '1.0');
if (@file_exists(get_stylesheet_directory() . '/watupro/users-earned-certificate.html.php')) {
require get_stylesheet_directory() . '/watupro/users-earned-certificate.html.php';
} else {
require WATUPRO_PATH . "/views/users-earned-certificate.html.php";
}
}
function watupro_add_menu_links()
{
global $wp_version, $_registered_pages;
$page = 'tools.php';
$student_caps = current_user_can(WATUPRO_MANAGE_CAPS) ? WATUPRO_MANAGE_CAPS : 'read';
// used to be watupro_exams
// multiuser settings - let's first default all to WATUPRO_MANAGE_CAPS in case of no Intelligence module
$exam_caps = $certificate_caps = $cat_caps = $ugroup_caps = $qcat_caps = $setting_caps = WATUPRO_MANAGE_CAPS;
if (watupro_intel() and !current_user_can('administrator')) {
if (!WatuPROIMultiUser::check_access('exams_access', true)) {
$exam_caps = 'administrator';
}
if (!WatuPROIMultiUser::check_access('certificates_access', true)) {
$certificate_caps = 'administrator';
}
if (!WatuPROIMultiUser::check_access('cats_access', true)) {
$cat_caps = 'administrator';
}
if (!WatuPROIMultiUser::check_access('usergroups_access', true)) {
$ugroup_caps = 'administrator';
}
if (!WatuPROIMultiUser::check_access('qcats_access', true)) {
$qcat_caps = 'administrator';
}
if (!WatuPROIMultiUser::check_access('settings_access', true)) {
$setting_caps = 'administrator';
}
}
// students part
if (!get_option('watupro_nodisplay_myquizzes')) {
add_menu_page(sprintf(__('My %s', 'watupro'), __('Quizzes', 'watupro')), sprintf(__('My %s', 'watupro'), __('Quizzes', 'watupro')), $student_caps, "my_watupro_exams", 'watupro_my_exams');
} else {
add_submenu_page(null, sprintf(__('My %s', 'watupro'), __('Quizzes', 'watupro')), sprintf(__('My %s', 'watupro'), __('Quizzes', 'watupro')), $exam_caps, "my_watupro_exams", 'watupro_my_exams');
}
if (!get_option('watupro_nodisplay_mycertificates')) {
add_submenu_page('my_watupro_exams', __("My Certificates", 'watupro'), __("My Certificates", 'watupro'), $student_caps, 'watupro_my_certificates', 'watupro_my_certificates');
} else {
add_submenu_page(null, __("My Certificates", 'watupro'), __("My Certificates", 'watupro'), $exam_caps, 'watupro_my_certificates', 'watupro_my_certificates');
}
do_action('watupro_user_menu');
if (!get_option('watupro_nodisplay_mysettings')) {
add_submenu_page('my_watupro_exams', sprintf(__("%s Settings", 'watupro'), __('Quiz', 'watupro')), sprintf(__("%s Settings", 'watupro'), __('Quiz', 'watupro')), $student_caps, 'watupro_my_options', 'watupro_my_options');
}
// admin menus
// "watupro_exams" menu is always accessible to WATUPRO_MANAGE_CAPS because it's the main menu item
add_menu_page(__('Watu PRO', 'watupro'), __('Watu PRO', 'watupro'), WATUPRO_MANAGE_CAPS, "watupro_exams", 'watupro_exams');
add_submenu_page('watupro_exams', __('Quizzes', 'watupro'), __('Quizzes', 'watupro'), WATUPRO_MANAGE_CAPS, "watupro_exams", 'watupro_exams');
add_submenu_page('watupro_exams', __("Watu PRO Certificates", 'watupro'), __("Certificates", 'watupro'), $certificate_caps, 'watupro_certificates', 'watupro_certificates');
add_submenu_page('watupro_exams', __('Quiz Categories', 'watupro'), __('Quiz Categories', 'watupro'), $cat_caps, "watupro_cats", "watupro_cats");
add_submenu_page('watupro_exams', __('User Groups', 'watupro'), __('User Groups', 'watupro'), $ugroup_caps, "watupro_groups", "watupro_groups");
add_submenu_page('watupro_exams', __('Question Categories', 'watupro'), __('Question Categories', 'watupro'), $qcat_caps, "watupro_question_cats", "watupro_question_cats");
add_submenu_page('watupro_exams', __('Default Grades', 'watupro'), __('Default Grades', 'watupro'), $exam_caps, "watupro_default_grades", "watupro_default_grades");
// accessible only to superadmin
add_submenu_page('watupro_exams', __('Modules', 'watupro'), __('Modules', 'watupro'), 'manage_options', "watupro_modules", "watupro_modules");
add_submenu_page('watupro_exams', __('Settings', 'watupro'), __('Settings', 'watupro'), $setting_caps, "watupro_options", "watupro_options");
do_action('watupro_admin_menu');
// always accessible to WATUPRO_MANAGE_CAPS
add_submenu_page('watupro_exams', __('Help', 'watupro'), __('Help', 'watupro'), WATUPRO_MANAGE_CAPS, "watupro_help", "watupro_help");
// not visible in menu - add/edit exam
add_submenu_page(NULL, __('Add/Edit Exam', 'watupro'), __('Add/Edit Exam', 'watupro'), $exam_caps, "watupro_exam", "watupro_exam");
add_submenu_page(NULL, __('Add/Edit Question', 'watupro'), __('Add/Edit Question', 'watupro'), $exam_caps, "watupro_question", "watupro_question");
// add/edit question
add_submenu_page(NULL, __('Manage Questions', 'watupro'), __('Manage Questions', 'watupro'), $exam_caps, "watupro_questions", "watupro_questions");
// manage questions
add_submenu_page(NULL, __('Taken Exam Data', 'watupro'), __('Taken Exam Data', 'watupro'), $exam_caps, "watupro_takings", "watupro_takings");
// view takings
add_submenu_page(NULL, __('Manage Grades', 'watupro'), __('Manage Grades', 'watupro'), $exam_caps, "watupro_grades", "watupro_grades");
// manage grades
add_submenu_page(NULL, __('Copy Exam', 'watupro'), __('Copy Exam', 'watupro'), $exam_caps, "watupro_copy_exam", "watupro_copy_exam");
// copy exam
add_submenu_page(NULL, __('Users Who Earned Certificate', 'watupro'), __('Users Who Earned Certificate', 'watupro'), $certificate_caps, "watupro_user_certificates", "watupro_user_certificates");
// view/approve user certificates
add_submenu_page(NULL, __('Editing an answer to question', 'watupro'), __('Editing an answer to question', 'watupro'), $exam_caps, "watupro_edit_choice", "watupro_edit_choice");
add_submenu_page(NULL, __('Advanced questions import', 'watupro'), __('Advanced questions import', 'watupro'), $exam_caps, "watupro_advanced_import", array('WatuPROImport', 'dispatch'));
}
function watupro_copy_exam()
{
global $wpdb, $user_ID;
$multiuser_access = 'all';
if (watupro_intel()) {
$multiuser_access = WatuPROIMultiUser::check_access('exams_access');
}
$own_sql = $multiuser_access == 'own' ? $wpdb->prepare(" AND editor_id=%d ", $user_ID) : "";
$exam = $wpdb->get_row($wpdb->prepare("SELECT * FROM " . WATUPRO_EXAMS . " WHERE ID=%d", $_GET['id']));
$grades = $wpdb->get_results($wpdb->prepare("SELECT * FROM " . WATUPRO_GRADES . " WHERE exam_id=%d order by ID ", $exam->ID));
$questions = $wpdb->get_results($wpdb->prepare("SELECT cat_id, question, ID FROM " . WATUPRO_QUESTIONS . " WHERE exam_id=%d ORDER BY sort_order, ID", $exam->ID));
$cids = array(0);
foreach ($questions as $question) {
if (!in_array($question->cat_id, $cids)) {
$cids[] = $question->cat_id;
}
}
$cidsql = implode(", ", $cids);
// select question categories to group questions by cats
$qcats = $wpdb->get_results("SELECT * FROM " . WATUPRO_QCATS . " WHERE ID IN ({$cidsql}) ORDER BY name");
// add Uncategorized
$qcats[] = (object) array("ID" => 0, "name" => __('Uncategorized', 'watupro'));
$other_exams = $wpdb->get_results("SELECT * FROM " . WATUPRO_EXAMS . " WHERE ID!='" . $exam->ID . "' {$own_sql} ORDER BY name");
if (!empty($_POST['copy_exam'])) {
try {
$copy_to = $_POST['copy_option'] == 'new' ? 0 : $_POST['copy_to'];
WTPExam::copy($exam->ID, $copy_to);
$_SESSION['flash'] = __("The exam was successfully copied!", 'watupro');
watupro_redirect("admin.php?page=watupro_exams");
} catch (Exception $e) {
$error = $e->getMessage();
}
}
if (@file_exists(get_stylesheet_directory() . '/watupro/copy-exam-form.html.php')) {
require get_stylesheet_directory() . '/watupro/copy-exam-form.html.php';
} else {
require WATUPRO_PATH . "/views/copy-exam-form.html.php";
}
}
function watupro_cats()
{
global $wpdb, $wp_roles, $user_ID;
$groups_table = WATUPRO_GROUPS;
$multiuser_access = 'all';
if (watupro_intel()) {
$multiuser_access = WatuPROIMultiUser::check_access('cats_access');
}
// are we using WP Roles or Watupro groups
$use_wp_roles = get_option('watupro_use_wp_roles');
// select all groups
if (!$use_wp_roles) {
$groups = $wpdb->get_results("SELECT * FROM " . WATUPRO_GROUPS . " ORDER BY name");
} else {
$roles = $wp_roles->roles;
}
switch (@$_GET['do']) {
case 'add':
if (!empty($_POST['ok'])) {
$wpdb->query($wpdb->prepare("INSERT INTO " . WATUPRO_CATS . " (name, ugroups, editor_id)\n\t\t\t\t\tVALUES (%s, %s, %d)", $_POST['name'], "|" . @implode("|", $_POST['ugroups']) . "|", $user_ID));
echo "<meta http-equiv='refresh' content='0;url=admin.php?page=watupro_cats' />";
exit;
}
if (@file_exists(get_stylesheet_directory() . '/watupro/cat.php')) {
require get_stylesheet_directory() . '/watupro/cat.php';
} else {
require WATUPRO_PATH . "/views/cat.php";
}
break;
case 'edit':
if ($multiuser_access == 'own') {
$cat = $wpdb->get_row($wpdb->prepare("SELECT * FROM " . WATUPRO_CATS . " WHERE ID=%d", $_GET['id']));
if ($cat->editor_id != $user_ID) {
wp_die(__('You can manage only your own categories', 'watupro'));
}
}
if (!empty($_POST['del'])) {
$wpdb->query($wpdb->prepare("DELETE FROM " . WATUPRO_CATS . " WHERE ID=%d", $_GET['id']));
// set cat_id=0 to all exams that were in this cat
$wpdb->query($wpdb->prepare("UPDATE " . WATUPRO_EXAMS . " SET cat_id=0 WHERE cat_id=%d", $_GET['id']));
echo "<meta http-equiv='refresh' content='0;url=admin.php?page=watupro_cats' />";
exit;
}
if (!empty($_POST['ok'])) {
$wpdb->query($wpdb->prepare("UPDATE " . WATUPRO_CATS . " SET\n\t\t\t\t\tname=%s, ugroups=%s WHERE ID=%d", $_POST['name'], "|" . @implode("|", $_POST['ugroups']) . "|", $_GET['id']));
echo "<meta http-equiv='refresh' content='0;url=admin.php?page=watupro_cats' />";
exit;
}
$cat = $wpdb->get_row($wpdb->prepare("SELECT * FROM " . WATUPRO_CATS . " WHERE ID=%d", $_GET['id']));
if (@file_exists(get_stylesheet_directory() . '/watupro/cat.php')) {
require get_stylesheet_directory() . '/watupro/cat.php';
} else {
require WATUPRO_PATH . "/views/cat.php";
}
break;
default:
// select my cats
$own_sql = $multiuser_access == 'own' ? $wpdb->prepare(" WHERE editor_id = %d ", $user_ID) : "";
$cats = $wpdb->get_results("SELECT * FROM " . WATUPRO_CATS . " {$own_sql} ORDER BY name");
if (@file_exists(get_stylesheet_directory() . '/watupro/cats.php')) {
require get_stylesheet_directory() . '/watupro/cats.php';
} else {
require WATUPRO_PATH . "/views/cats.php";
}
break;
}
}
function watupro_takings()
{
global $wpdb, $wp_roles, $user_ID;
$roles = $wp_roles->roles;
$multiuser_access = 'all';
if (watupro_intel()) {
$multiuser_access = WatuPROIMultiUser::check_access('exams_access');
}
// select user groups
$groups = $wpdb->get_results("SELECT * FROM " . WATUPRO_GROUPS . " ORDER BY name");
// shows data for a taken exam
$ob = empty($_GET['ob']) ? "id" : $_GET['ob'];
$dir = !empty($_GET['dir']) ? $_GET['dir'] : "DESC";
$odir = $dir == 'ASC' ? 'DESC' : 'ASC';
$offset = empty($_GET['offset']) ? 0 : $_GET['offset'];
// select exam
$exam = $wpdb->get_row($wpdb->prepare("SELECT * FROM " . WATUPRO_EXAMS . " WHERE ID=%d", $_GET['exam_id']));
if (!empty($_POST['cleanup']) or !empty($_POST['blankout'])) {
if ($multiuser_access == 'own' and $exam->editor_id != $user_ID) {
wp_die(__('You can manage only the results on exams created by you.', 'watupro'));
}
if (!empty($_POST['cleanup'])) {
// now cleanup
$wpdb->query($wpdb->prepare("DELETE FROM " . WATUPRO_TAKEN_EXAMS . " WHERE exam_id=%d", $exam->ID));
$wpdb->query($wpdb->prepare("DELETE FROM " . WATUPRO_STUDENT_ANSWERS . " WHERE exam_id=%d", $exam->ID));
}
if (!empty($_POST['blankout'])) {
$wpdb->query($wpdb->prepare("UPDATE " . WATUPRO_TAKEN_EXAMS . " \n\t\t\t\tSET details='data removed', catgrades='data removed' WHERE exam_id=%d", $exam->ID));
$wpdb->query($wpdb->prepare("UPDATE " . WATUPRO_STUDENT_ANSWERS . " \n\t\t\t\tSET question_text='data removed', snapshot='data removed' WHERE exam_id=%d", $exam->ID));
}
}
// check access
if ($multiuser_access == 'own') {
if ($exam->editor_id != $user_ID) {
wp_die(__('You can only view results on your own quizzes.', 'watupro'));
}
}
// search/filter
$filters = array();
$joins = array();
$filter_sql = $left_join_sql = $role_join_sql = $group_join_sql = $left_join = "";
$join_sql = "LEFT JOIN {$wpdb->users} tU ON tU.ID=tT.user_id";
// add filters and joins
// display name
if (!empty($_GET['dn'])) {
switch ($_GET['dnf']) {
case 'contains':
$like = "%{$_GET['dn']}%";
break;
case 'starts':
$like = "{$_GET['dn']}%";
break;
case 'ends':
$like = "%{$_GET['dn']}";
break;
case 'equals':
default:
$like = $_GET['dn'];
break;
}
$joins[] = $wpdb->prepare(" display_name LIKE %s ", $like);
}
// email
if (!empty($_GET['email'])) {
switch ($_GET['emailf']) {
case 'contains':
$like = "%{$_GET['email']}%";
break;
case 'starts':
$like = "{$_GET['email']}%";
break;
case 'ends':
$like = "%{$_GET['email']}";
break;
case 'equals':
default:
$like = $_GET['email'];
break;
}
$joins[] = $wpdb->prepare(" user_email LIKE %s ", $like);
$filters[] = $wpdb->prepare(" ((user_id=0 AND email LIKE %s) OR (user_id!=0 AND user_email LIKE %s)) ", $like, $like);
$left_join = 'LEFT';
// when email is selected, do left join because it might be without logged user
}
// WP user role - when selected role the join always becomes right join
if (!empty($_GET['role'])) {
$left_join = '';
$blog_prefix = $wpdb->get_blog_prefix();
$role_join_sql = "JOIN {$wpdb->usermeta} tUM ON tUM.user_id = tU.id \n\t\t\tAND tUM.meta_key = '{$blog_prefix}capabilities' AND tUM.meta_value LIKE '%:" . '"' . $_GET['role'] . '"' . ";%'";
}
// Watupro user group
if (!empty($_GET['ugroup'])) {
$left_join = '';
$group_join_sql = "JOIN {$wpdb->usermeta} tUM2 ON tUM2.user_id = tU.id \n\t\t\tAND tUM2.meta_key = 'watupro_groups' AND tUM2.meta_value LIKE '%:" . '"' . $_GET['ugroup'] . '"' . ";%'";
} else {
$group_join_sql = "LEFT JOIN {$wpdb->usermeta} tUM2 ON tUM2.user_id = tU.id \n\t\t\tAND tUM2.meta_key = 'watupro_groups' ";
}
// IP
if (!empty($_GET['ip'])) {
switch ($_GET['ipf']) {
case 'contains':
$like = "%{$_GET['ip']}%";
break;
case 'starts':
$like = "{$_GET['ip']}%";
break;
case 'ends':
$like = "%{$_GET['ip']}";
break;
case 'equals':
default:
$like = $_GET['ip'];
break;
}
$filters[] = $wpdb->prepare(" ip LIKE %s ", $like);
}
// Date
if (!empty($_GET['date'])) {
switch ($_GET['datef']) {
case 'after':
$filters[] = $wpdb->prepare(" date>%s ", $_GET['date']);
break;
case 'before':
$filters[] = $wpdb->prepare(" date<%s ", $_GET['date']);
break;
case 'equals':
default:
$filters[] = $wpdb->prepare(" date=%s ", $_GET['date']);
break;
}
}
// Points
if (!empty($_GET['points'])) {
switch ($_GET['pointsf']) {
case 'less':
$filters[] = $wpdb->prepare(" points<%d ", $_GET['points']);
break;
case 'more':
$filters[] = $wpdb->prepare(" points>%d ", $_GET['points']);
break;
case 'equals':
default:
$filters[] = $wpdb->prepare(" points=%d ", $_GET['points']);
break;
}
}
// % correct
if (!empty($_GET['percent_correct'])) {
switch ($_GET['percentf']) {
case 'less':
$filters[] = $wpdb->prepare(" percent_correct < %d ", $_GET['percent_correct']);
break;
case 'more':
$filters[] = $wpdb->prepare(" percent_correct > %d ", $_GET['percent_correct']);
break;
case 'equals':
default:
$filters[] = $wpdb->prepare(" percent_correct = %d ", $_GET['percent_correct']);
break;
}
}
// passed taking ID from the manage user-certificates page
if (!empty($_GET['taking_id'])) {
$filters[] = $wpdb->prepare(" tT.ID=%d ", $_GET['taking_id']);
}
// Grade
if (!empty($_GET['grade'])) {
$filters[] = $wpdb->prepare(" grade_id=%d ", $_GET['grade']);
}
// construct filter & join SQLs
if (sizeof($filters)) {
$filter_sql = " AND " . implode(" AND ", $filters);
}
if (sizeof($joins)) {
$join_sql = " {$left_join} JOIN {$wpdb->users} tU ON tU.ID=tT.user_id AND " . implode(" AND ", $joins);
}
$limit_sql = "LIMIT {$offset},10";
if (!empty($_GET['export'])) {
$limit_sql = "";
}
// select takings
$in_progress = empty($_GET['in_progress']) ? 0 : 1;
// completed or "in progress" takings
$q = "SELECT SQL_CALC_FOUND_ROWS tT.*, tU.display_name as display_name, tU.user_email as user_email,\n\ttUM2.meta_value as user_groups\n\tFROM " . WATUPRO_TAKEN_EXAMS . " tT \n\t{$join_sql} {$role_join_sql} {$group_join_sql}\n\tWHERE tT.exam_id={$exam->ID} AND tT.in_progress={$in_progress} {$filter_sql}\n\tORDER BY {$ob} {$dir} {$limit_sql}";
// echo $q;
$takings = $wpdb->get_results($q);
$count = $wpdb->get_var("SELECT FOUND_ROWS()");
// fill user groups
foreach ($takings as $cnt => $taking) {
if (empty($taking->user_groups)) {
continue;
}
$ugroups = unserialize($taking->user_groups);
$ugroup_names = array();
foreach ($groups as $group) {
if (in_array($group->ID, $ugroups)) {
$ugroup_names[] = $group->name;
}
}
$takings[$cnt]->user_groups = implode(', ', $ugroup_names);
}
// end filling user groups info
// select number of in_progress takings unless we are showing them now
if (!$in_progress) {
$num_unfinished = $wpdb->get_var($wpdb->prepare("SELECT COUNT(ID) FROM " . WATUPRO_TAKEN_EXAMS . "\n\t\t\tWHERE exam_id = %d AND in_progress=1", $exam->ID));
}
if (!empty($_GET['export'])) {
$_record = new WTPRecord();
$_record->export($takings, $exam);
}
// grades for the dropdown
$grades = WTPGrade::get_grades($exam);
// this var will be added to links at the view
$filters_url = "dn=" . @$_GET['dn'] . "&dnf=" . @$_GET['dnf'] . "&email=" . @$_GET['email'] . "&emailf=" . @$_GET['emailf'] . "&ip=" . @$_GET['ip'] . "&ipf=" . @$_GET['ipf'] . "&date=" . @$_GET['date'] . "&datef=" . @$_GET['datef'] . "&points=" . @$_GET['points'] . "&pointsf=" . @$_GET['pointsf'] . "&grade=" . @$_GET['grade'] . "&role=" . @$_GET['role'] . "&ugroup=" . @$_GET['ugroup'] . "&percent_correct=" . @$_GET['percent_correct'] . "&percentf=" . @$_GET['percentf'];
$display_filters = (!sizeof($filters) and !sizeof($joins) and empty($role_join_sql) and empty($_GET['ugroup'])) ? false : true;
wp_enqueue_script('thickbox', null, array('jquery'));
wp_enqueue_style('thickbox.css', '/' . WPINC . '/js/thickbox/thickbox.css', null, '1.0');
if (@file_exists(get_stylesheet_directory() . '/watupro/takings.php')) {
require get_stylesheet_directory() . '/watupro/takings.php';
} else {
require WATUPRO_PATH . "/views/takings.php";
}
}