/home/nbcgowuy/tnclms.com/wp-content/plugins/tutor/models/QuizModel.php
<?php
/**
* Quiz Model
*
* @package Tutor\Models
* @author Themeum <support@themeum.com>
* @link https://themeum.com
* @since 2.0.10
*/
namespace Tutor\Models;
use Tutor\Cache\TutorCache;
use Tutor\Helpers\QueryHelper;
use TUTOR\Quiz;
/**
* Class QuizModel
*
* @since 2.0.10
*/
class QuizModel {
const ATTEMPT_STARTED = 'attempt_started';
const ATTEMPT_ENDED = 'attempt_ended';
const REVIEW_REQUIRED = 'review_required';
const ATTEMPT_TIMEOUT = 'attempt_timeout';
const RESULT_PASS = 'pass';
const RESULT_FAIL = 'fail';
const RESULT_PENDING = 'pending';
/**
* Get quiz table name
*
* @since 2.1.0
*
* @return string
*/
public function get_table(): string {
global $wpdb;
return $wpdb->prefix . 'tutor_quiz_attempts';
}
/**
* Get manual review types.
*
* @since 3.8.0
*
* @return array
*/
public static function get_manual_review_types() {
return array( 'open_ended', 'short_answer' );
}
/**
* Get total number of quiz
*
* @since 2.0.2
*
* @since 3.7.1 Course ids param added
*
* @param array $course_ids Array of course ids.
*
* @return int
*/
public static function get_total_quiz( array $course_ids = array() ) {
global $wpdb;
$course_in_clause = '';
if ( count( $course_ids ) ) {
$prepare_ids = QueryHelper::prepare_in_clause( $course_ids );
$course_in_clause = "AND course.ID IN ({$prepare_ids})";
}
$sql = "SELECT COUNT(DISTINCT quiz.ID)
FROM {$wpdb->posts} quiz
INNER JOIN {$wpdb->posts} topic ON quiz.post_parent=topic.ID
INNER JOIN {$wpdb->posts} course ON topic.post_parent=course.ID
WHERE 1 = 1
{$course_in_clause}
AND course.post_type=%s
AND course.post_status = 'publish'
AND quiz.post_type='tutor_quiz'";
//phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
return $wpdb->get_var( $wpdb->prepare( $sql, tutor()->course_post_type ) );
}
/**
* Get Attempt row by grade method settings
*
* @since 1.4.2
*
* @param int $quiz_id quiz id.
* @param int $user_id user id.
*
* @return array|bool|null|object
*/
public function get_quiz_attempt( $quiz_id = 0, $user_id = 0 ) {
global $wpdb;
$quiz_id = tutils()->get_post_id( $quiz_id );
$user_id = tutils()->get_user_id( $user_id );
$attempt = false;
$quiz_grade_method = get_tutor_option( 'quiz_grade_method', 'highest_grade' );
$from_string = "FROM {$wpdb->tutor_quiz_attempts} WHERE quiz_id = %d AND user_id = %d AND attempt_status != 'attempt_started' ";
//phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
if ( 'highest_grade' === $quiz_grade_method ) {
$attempt = $wpdb->get_row( $wpdb->prepare( "SELECT * {$from_string} ORDER BY earned_marks DESC LIMIT 1; ", $quiz_id, $user_id ) );
} elseif ( 'average_grade' === $quiz_grade_method ) {
$attempt = $wpdb->get_row(
$wpdb->prepare(
"SELECT {$wpdb->tutor_quiz_attempts}.*,
COUNT(attempt_id) AS attempt_count,
AVG(total_marks) AS total_marks,
AVG(earned_marks) AS earned_marks {$from_string}
",
$quiz_id,
$user_id
)
);
} elseif ( 'first_attempt' === $quiz_grade_method ) {
$attempt = $wpdb->get_row( $wpdb->prepare( "SELECT * {$from_string} ORDER BY attempt_id ASC LIMIT 1; ", $quiz_id, $user_id ) );
} elseif ( 'last_attempt' === $quiz_grade_method ) {
$attempt = $wpdb->get_row( $wpdb->prepare( "SELECT * {$from_string} ORDER BY attempt_id DESC LIMIT 1; ", $quiz_id, $user_id ) );
}
//phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
return $attempt;
}
/**
* Get all of the attempts by an user of a quiz
*
* @since 1.0.0
*
* @param int $quiz_id quiz ID.
* @param int $user_id user ID.
*
* @return array|bool|null|object
*/
public function quiz_attempts( $quiz_id = 0, $user_id = 0 ) {
global $wpdb;
$quiz_id = tutor_utils()->get_post_id( $quiz_id );
$user_id = tutor_utils()->get_user_id( $user_id );
$cache_key = "tutor_quiz_attempts_for_{$user_id}_{$quiz_id}";
$attempts = TutorCache::get( $cache_key );
if ( false === $attempts ) {
$attempts = $wpdb->get_results(
$wpdb->prepare(
"SELECT *
FROM {$wpdb->prefix}tutor_quiz_attempts
WHERE quiz_id = %d
AND user_id = %d
ORDER BY attempt_id DESC
",
$quiz_id,
$user_id
)
);
TutorCache::set( $cache_key, $attempts );
}
if ( is_array( $attempts ) && count( $attempts ) ) {
return $attempts;
}
return false;
}
/**
* Get Quiz question by question id
*
* @since 1.0.0
*
* @param int $question_id question ID.
*
* @return array|bool|object|void|null
*/
public static function get_quiz_question_by_id( $question_id = 0 ) {
global $wpdb;
if ( $question_id ) {
$question = $wpdb->get_row(
$wpdb->prepare(
"SELECT *
FROM {$wpdb->prefix}tutor_quiz_questions
WHERE question_id = %d
LIMIT 0, 1;
",
$question_id
)
);
return $question;
}
return false;
}
/**
* Get all ended attempts by an user of a quiz
*
* @since 1.4.1
*
* @param int $quiz_id quiz ID.
* @param int $user_id user ID.
*
* @return array|bool|null|object
*/
public function quiz_ended_attempts( $quiz_id = 0, $user_id = 0 ) {
global $wpdb;
$quiz_id = tutor_utils()->get_post_id( $quiz_id );
$user_id = tutor_utils()->get_user_id( $user_id );
$attempts = $wpdb->get_results(
$wpdb->prepare(
"SELECT *
FROM {$wpdb->prefix}tutor_quiz_attempts
WHERE quiz_id = %d
AND user_id = %d
AND attempt_status != %s
",
$quiz_id,
$user_id,
'attempt_started'
)
);
if ( is_array( $attempts ) && count( $attempts ) ) {
return $attempts;
}
return false;
}
/**
* Get the next question order ID
*
* @since 1.0.0
*
* @param integer $quiz_id quiz ID.
*
* @return int
*/
public static function quiz_next_question_order_id( $quiz_id ) {
global $wpdb;
$last_order = (int) $wpdb->get_var(
$wpdb->prepare(
"SELECT MAX(question_order)
FROM {$wpdb->prefix}tutor_quiz_questions
WHERE quiz_id = %d ;
",
$quiz_id
)
);
return $last_order + 1;
}
/**
* Get next quiz question ID
*
* @since 1.0.0
*
* @return int
*/
public static function quiz_next_question_id() {
global $wpdb;
$last_order = (int) $wpdb->get_var( "SELECT MAX(question_id) FROM {$wpdb->prefix}tutor_quiz_questions;" );
return $last_order + 1;
}
/**
* Total number of quiz attempts
*
* @since 1.0.0
*
* @param string $search_term search term.
* @param integer $course_id course ID.
* @param string $tab tab.
* @param string $date_filter date filter.
*
* @return int
*/
public static function get_total_quiz_attempts( $search_term = '', int $course_id = 0, string $tab = '', $date_filter = '' ) {
global $wpdb;
// Prepare search term.
$search_term = empty( $search_term ) ? '' : '%' . $wpdb->esc_like( $search_term ) . '%';
// Prepare course filter.
$course_filter = $course_id ? $wpdb->prepare( ' AND quiz_attempts.course_id = %d', $course_id ) : '';
// Prepare date filter.
$date_filter = empty( $date_filter ) ? '' : tutor_get_formated_date( 'Y-m-d', $date_filter );
$date_filter = empty( $date_filter ) ? '' : $wpdb->prepare( ' AND DATE(quiz_attempts.attempt_started_at) = %s ', $date_filter );
// Prepare user join and clause.
$user_join = '';
$user_clause = '';
if ( '' !== $search_term ) {
$user_join = "INNER JOIN {$wpdb->users} ON quiz_attempts.user_id = {$wpdb->users}.ID";
$user_clause = $wpdb->prepare(
" AND ( {$wpdb->users}.user_email LIKE %s OR {$wpdb->users}.display_name LIKE %s )",
$search_term,
$search_term
);
}
// Prepare tab filter.
$tab_clause = '';
if ( in_array( $tab, array( self::RESULT_PASS, self::RESULT_FAIL, self::RESULT_PENDING ), true ) ) {
$tab_clause = $wpdb->prepare( ' AND quiz_attempts.result = %s', $tab );
}
//phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
$count = $wpdb->get_var(
$wpdb->prepare(
"SELECT COUNT(DISTINCT quiz_attempts.attempt_id) AS total
FROM {$wpdb->prefix}tutor_quiz_attempts quiz_attempts
INNER JOIN {$wpdb->posts} quiz ON quiz_attempts.quiz_id = quiz.ID
INNER JOIN {$wpdb->posts} AS course ON course.ID = quiz_attempts.course_id
{$user_join}
WHERE attempt_status != %s AND quiz_attempts.result IS NOT NULL
{$user_clause}
{$course_filter}
{$tab_clause}
{$date_filter}",
'attempt_started'
)
);
//phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
return (int) $count;
}
/**
* Get the all quiz attempts
*
* @since 1.0.0
* @since 1.9.5 sorting params added.
* @since 3.8.0 refactor and query optimize.
*
* @param integer $start start.
* @param integer $limit limit.
* @param string $search_filter search filter.
* @param string $course_filter course filter.
* @param string $date_filter date filter.
* @param string $order_filter order filter.
* @param mixed $result_state result state.
* @param boolean $count_only count only or not.
* @param boolean $instructor_id_check need instructor id check or not.
*
* @return mixed
*/
public static function get_quiz_attempts( $start = 0, $limit = 10, $search_filter = '', $course_filter = array(), $date_filter = '', $order_filter = 'DESC', $result_state = null, $count_only = false, $instructor_id_check = false ) {
global $wpdb;
$start = (int) $start;
$limit = (int) $limit;
$search_filter = sanitize_text_field( $search_filter );
$course_filter = sanitize_text_field( $course_filter );
$date_filter = sanitize_text_field( $date_filter );
$order_filter = sanitize_sql_orderby( $order_filter );
$search_term_raw = $search_filter;
$search_filter = '%' . $wpdb->esc_like( $search_filter ) . '%';
// Filter by course.
if ( '' !== $course_filter ) {
if ( ! is_array( $course_filter ) ) {
$course_filter = array( $course_filter );
}
$course_ids = QueryHelper::prepare_in_clause( array_map( 'intval', $course_filter ) );
$course_filter = " AND quiz_attempts.course_id IN ($course_ids) ";
}
// Filter by date.
$date_filter = '' !== $date_filter ? tutor_get_formated_date( 'Y-m-d', $date_filter ) : '';
$date_filter = '' !== $date_filter ? $wpdb->prepare( ' AND DATE(quiz_attempts.attempt_started_at) = %s ', $date_filter ) : '';
$result_clause = '';
$select_columns = $count_only ? 'COUNT(DISTINCT quiz_attempts.attempt_id)' : 'DISTINCT quiz_attempts.*, quiz.post_title, users.user_email, users.user_login, users.display_name';
$limit_offset = $count_only ? '' : $wpdb->prepare( ' LIMIT %d OFFSET %d', $limit, $start );
// Get attempts by instructor ID.
$instructor_clause = '';
if ( $instructor_id_check ) {
$current_user_id = get_current_user_id();
$instructor_id = tutor_utils()->has_user_role( 'administrator', $current_user_id ) ? null : $current_user_id;
if ( $instructor_id ) {
$instructor_clause = " INNER JOIN {$wpdb->usermeta} AS instructor_meta ON course.ID = instructor_meta.meta_value AND (instructor_meta.meta_key='_tutor_instructor_course_id' AND instructor_meta.user_id=$instructor_id) ";
}
}
$result_clause = '';
if ( in_array( $result_state, array( self::RESULT_PASS, self::RESULT_FAIL, self::RESULT_PENDING ), true ) ) {
$result_clause = $wpdb->prepare( ' AND quiz_attempts.result = %s', $result_state );
}
//phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
$query = $wpdb->prepare(
"SELECT {$select_columns}
FROM {$wpdb->prefix}tutor_quiz_attempts quiz_attempts
INNER JOIN {$wpdb->posts} quiz ON quiz_attempts.quiz_id = quiz.ID
INNER JOIN {$wpdb->users} AS users ON quiz_attempts.user_id = users.ID
INNER JOIN {$wpdb->posts} AS course ON course.ID = quiz_attempts.course_id
{$instructor_clause}
WHERE quiz_attempts.attempt_ended_at IS NOT NULL
AND (
users.user_email = %s
OR users.display_name LIKE %s
OR quiz.post_title LIKE %s
OR course.post_title LIKE %s
)
AND quiz_attempts.attempt_ended_at IS NOT NULL
{$result_clause}
{$course_filter}
{$date_filter}
ORDER BY quiz_attempts.attempt_ended_at {$order_filter} {$limit_offset}",
$search_term_raw,
$search_filter,
$search_filter,
$search_filter
);
//phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
//phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
return $count_only ? $wpdb->get_var( $query ) : $wpdb->get_results( $query );
}
/**
* Delete quizattempt for user
*
* @since 1.9.5
*
* @param mixed $attempt_ids attempt ids.
*
* @return void
*/
public static function delete_quiz_attempt( $attempt_ids ) {
global $wpdb;
// Singlular to array.
! is_array( $attempt_ids ) ? $attempt_ids = array( $attempt_ids ) : 0;
if ( count( $attempt_ids ) ) {
$attempt_ids = implode( ',', $attempt_ids );
//phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
// Deleting attempt (comment), child attempt and attempt meta (comment meta).
$wpdb->query( "DELETE FROM {$wpdb->prefix}tutor_quiz_attempts WHERE attempt_id IN($attempt_ids)" );
$wpdb->query( "DELETE FROM {$wpdb->prefix}tutor_quiz_attempt_answers WHERE quiz_attempt_id IN($attempt_ids)" );
//phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
do_action( 'tutor_quiz/attempt_deleted', $attempt_ids );
}
}
/**
* Sorting params added on quiz attempt
*
* @since 1.9.5
*
* @param integer $start start.
* @param integer $limit limit.
* @param array $course_ids course ids.
* @param string $search_filter search filter.
* @param string $course_filter course filter.
* @param string $date_filter date filter.
* @param string $order_filter order filter.
* @param mixed $user_id user id.
* @param boolean $count_only is only count or not.
* @param boolean $all_attempt need all atempt or not.
*
* @return mixed
*/
public static function get_quiz_attempts_by_course_ids( $start = 0, $limit = 10, $course_ids = array(), $search_filter = '', $course_filter = '', $date_filter = '', $order_filter = '', $user_id = null, $count_only = false, $all_attempt = false ) {
global $wpdb;
$search_filter = sanitize_text_field( $search_filter );
$course_filter = (int) sanitize_text_field( $course_filter );
$date_filter = sanitize_text_field( $date_filter );
$order_filter = sanitize_sql_orderby( $order_filter );
$course_ids = array_map(
function ( $id ) {
return "'" . esc_sql( $id ) . "'";
},
$course_ids
);
$course_ids_in = count( $course_ids ) ? ' AND quiz_attempts.course_id IN (' . implode( ', ', $course_ids ) . ') ' : '';
$search_filter = $search_filter ? '%' . $wpdb->esc_like( $search_filter ) . '%' : '';
$search_term_raw = $search_filter;
$search_filter = $search_filter ? "AND ( users.user_email = '{$search_term_raw}' OR users.display_name LIKE {$search_filter} OR quiz.post_title LIKE {$search_filter} OR course.post_title LIKE {$search_filter} )" : '';
$course_filter = 0 !== $course_filter ? " AND quiz_attempts.course_id = $course_filter " : '';
$date_filter = '' != $date_filter ? tutor_get_formated_date( 'Y-m-d', $date_filter ) : '';
$date_filter = '' != $date_filter ? " AND DATE(quiz_attempts.attempt_started_at) = '$date_filter' " : '';
$user_filter = $user_id ? ' AND user_id=\'' . esc_sql( $user_id ) . '\' ' : '';
$limit_offset = $count_only ? '' : " LIMIT {$start}, {$limit} ";
$select_col = $count_only ? ' COUNT(DISTINCT quiz_attempts.attempt_id) ' : ' quiz_attempts.*, users.*, quiz.* ';
$attempt_type = $all_attempt ? '' : " AND quiz_attempts.attempt_status != 'attempt_started' ";
$query = "SELECT {$select_col}
FROM {$wpdb->prefix}tutor_quiz_attempts AS quiz_attempts
INNER JOIN {$wpdb->posts} AS quiz
ON quiz_attempts.quiz_id = quiz.ID
INNER JOIN {$wpdb->users} AS users
ON quiz_attempts.user_id = users.ID
INNER JOIN {$wpdb->posts} AS course
ON course.ID = quiz_attempts.course_id
WHERE 1=1
{$attempt_type}
{$course_ids_in}
{$search_filter}
{$course_filter}
{$date_filter}
{$user_filter}
ORDER BY quiz_attempts.attempt_id {$order_filter} {$limit_offset};";
//phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
return $count_only ? $wpdb->get_var( $query ) : $wpdb->get_results( $query );
}
/**
* Get answers list by quiz question
*
* @since 1.0.0
*
* @param int $question_id question ID.
* @param bool $rand rand.
*
* @return array|bool|null|object
*/
public static function get_answers_by_quiz_question( $question_id, $rand = false ) {
global $wpdb;
$question = $wpdb->get_row(
$wpdb->prepare(
"SELECT *
FROM {$wpdb->prefix}tutor_quiz_questions
WHERE question_id = %d;
",
$question_id
)
);
if ( ! $question ) {
return false;
}
$order = ' answer_order ASC ';
if ( $rand ) {
$order = ' RAND() ';
}
//phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
$answers = $wpdb->get_results(
$wpdb->prepare(
"SELECT *
FROM {$wpdb->prefix}tutor_quiz_question_answers
WHERE belongs_question_id = %d
AND belongs_question_type = %s
ORDER BY {$order}
",
$question_id,
$question->question_type
)
);
//phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
return $answers;
}
/**
* Get quiz answers by attempt id
*
* @since 1.0.0
*
* @param mixed $attempt_id attempt ID.
* @param bool $add_index need index or not.
*
* @return array|null|object
*/
public static function get_quiz_answers_by_attempt_id( $attempt_id, $add_index = false ) {
global $wpdb;
$ids = is_array( $attempt_id ) ? $attempt_id : array( $attempt_id );
$ids_in = implode( ',', $ids );
if ( empty( $ids_in ) ) {
// Prevent empty.
return array();
}
//phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
$results = $wpdb->get_results(
"SELECT answers.*,
question.*
FROM {$wpdb->prefix}tutor_quiz_attempt_answers answers
LEFT JOIN {$wpdb->prefix}tutor_quiz_questions question
ON answers.question_id = question.question_id
WHERE answers.quiz_attempt_id IN ({$ids_in})
ORDER BY attempt_answer_id ASC;"
);
//phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
if ( $add_index ) {
$new_array = array();
foreach ( $results as $result ) {
! isset( $new_array[ $result->quiz_attempt_id ] ) ? $new_array[ $result->quiz_attempt_id ] = array() : 0;
$new_array[ $result->quiz_attempt_id ][] = $result;
}
return $new_array;
}
return $results;
}
/**
* Get single answer by answer_id
*
* @since 1.0.0
*
* @param array|init $answer_id answer id.
*
* @return array|null|object
*/
public static function get_answer_by_id( $answer_id ) {
global $wpdb;
! is_array( $answer_id ) ? $answer_id = array( $answer_id ) : 0;
$answer_id = array_map(
function ( $id ) {
return "'" . esc_sql( $id ) . "'";
},
$answer_id
);
$in_ids_string = implode( ', ', $answer_id );
//phpcs:disable WordPress.DB.PreparedSQL.NotPrepared
$answer = $wpdb->get_results(
$wpdb->prepare(
"SELECT answer.*,
question.question_title,
question.question_type
FROM {$wpdb->prefix}tutor_quiz_question_answers answer
LEFT JOIN {$wpdb->prefix}tutor_quiz_questions question
ON answer.belongs_question_id = question.question_id
WHERE answer.answer_id IN (" . $in_ids_string . ')
AND 1 = %d;
',
1
)
);
//phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
return $answer;
}
/**
* Get quiz attempt timing
*
* @since 1.0.0
*
* @param mixed $attempt_data attempt data.
* @return array
*/
public static function get_quiz_attempt_timing( $attempt_data ) {
$attempt_duration = '';
$attempt_duration_taken = '';
$attempt_info = @unserialize( $attempt_data->attempt_info );
if ( is_array( $attempt_info ) ) {
// Allowed duration.
if ( isset( $attempt_info['time_limit'] ) ) {
//phpcs:ignore WordPress.WP.I18n.NonSingularStringLiteralText
$time_type = __( ucwords( tutor_utils()->array_get( 'time_limit.time_type', $attempt_info, 'minutes' ) ), 'tutor' );
$time_value = tutor_utils()->array_get( 'time_limit.time_value', $attempt_info, 0 );
$attempt_duration = $time_value . ' ' . $time_type;
}
// Taken duration.
$seconds = strtotime( $attempt_data->attempt_ended_at ) - strtotime( $attempt_data->attempt_started_at );
$attempt_duration_taken = tutor_utils()->seconds_to_time( $seconds );
}
return compact( 'attempt_duration', 'attempt_duration_taken' );
}
/**
* Check student is passed in a quiz or not.
* Quiz retry mode: student required at least one quiz passed in attempts
*
* @since 2.1.0
*
* @param int $quiz_id quiz ID.
* @param int $user_id user ID.
*
* @return boolean
*/
public static function is_quiz_passed( $quiz_id, $user_id = 0 ) {
global $wpdb;
$user_id = tutor_utils()->get_user_id( $user_id );
$attempts = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}tutor_quiz_attempts WHERE user_id=%d AND quiz_id=%d", $user_id, $quiz_id ) );
$required_percentage = tutor_utils()->get_quiz_option( $quiz_id, 'passing_grade', 0 );
foreach ( $attempts as $attempt ) {
$earned_percentage = self::calculate_attempt_earned_percentage( $attempt );
if ( $earned_percentage >= $required_percentage ) {
return true;
}
}
return false;
}
/**
* Get all question type for a quiz
*
* @since 2.1.0
*
* @param integer $quiz_id quiz ID.
*
* @return array
*/
public static function get_quiz_question_types( int $quiz_id ) {
global $wpdb;
$types = $wpdb->get_col(
$wpdb->prepare( "SELECT DISTINCT question_type FROM {$wpdb->prefix}tutor_quiz_questions WHERE quiz_id=%d", $quiz_id )
);
return $types;
}
/**
* Check a quiz attempt need manual review or not
*
* @since 2.1.0
*
* @param int $quiz_id quiz ID.
*
* @return boolean
*/
public static function is_manual_review_required( $quiz_id ) {
$required = false;
$review_question_types = self::get_manual_review_types();
$question_types = self::get_quiz_question_types( $quiz_id );
foreach ( $review_question_types as $type ) {
if ( in_array( $type, $question_types, true ) ) {
$required = true;
break;
}
}
return $required;
}
/**
* Get last or first quiz attempt
*
* @since 2.1.0
* @since 2.1.3 user_id param added.
*
* @param integer $quiz_id quiz id to get attempt of.
* @param integer $user_id user ID who attempt the quiz.
* @param string $order ASC or DESC, default is DESC
* pass ASC to get first attempt.
*
* @return mixed object on success, null on failure
*/
public function get_first_or_last_attempt( int $quiz_id, int $user_id = 0, string $order = 'DESC' ) {
$attempt = QueryHelper::get_row(
$this->get_table(),
array(
'quiz_id' => $quiz_id,
'user_id' => tutor_utils()->get_user_id( $user_id ),
),
'attempt_id',
$order
);
return $attempt;
}
/**
* Get total number of quizzes by course id
*
* @since 2.2.0
*
* @param int|array $course_id Course id or array of course ids.
*
* @return int
*/
public static function get_quiz_count_by_course( $course_id ) {
global $wpdb;
$and_clause = is_array( $course_id ) && count( $course_id ) ? ' AND post_parent IN (' . QueryHelper::prepare_in_clause( $course_id ) . ')' : "AND post_parent = $course_id";
//phpcs:disable
$count = $wpdb->get_var(
$wpdb->prepare(
"SELECT
COUNT(ID)
FROM {$wpdb->posts}
WHERE post_parent IN
(SELECT
ID
FROM {$wpdb->posts}
WHERE post_type = %s
{$and_clause}
AND post_status = %s
)
AND post_type = %s
AND post_status = %s",
'topics',
'publish',
'tutor_quiz',
'publish'
)
);
//phpcs:enable
return $count ? $count : 0;
}
/**
* Get final quiz result depending on all attempts.
*
* @since 2.4.0
*
* @param int $quiz_id quiz id.
* @param int $user_id user id.
*
* @return string pass, fail, pending
*/
public static function get_quiz_result( $quiz_id, $user_id = 0 ) {
global $wpdb;
$all_pending = true;
$result = self::RESULT_PENDING;
$user_id = tutor_utils()->get_user_id( $user_id );
$attempt_list = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}tutor_quiz_attempts WHERE user_id=%d AND quiz_id=%d", $user_id, $quiz_id ) );
$total_pending_attempt = (int) $wpdb->get_var(
$wpdb->prepare(
"SELECT COUNT(attempt_id) total
FROM {$wpdb->prefix}tutor_quiz_attempts
WHERE result=%s AND quiz_id = %d AND user_id = %d
",
self::RESULT_PENDING,
$quiz_id,
$user_id
)
);
if ( count( $attempt_list ) !== $total_pending_attempt ) {
$all_pending = false;
}
if ( false === $all_pending ) {
$required_percentage = tutor_utils()->get_quiz_option( $quiz_id, 'passing_grade', 0 );
foreach ( $attempt_list as $attempt ) {
$earned_percentage = self::calculate_attempt_earned_percentage( $attempt );
if ( $earned_percentage >= $required_percentage ) {
// If at least one attempt passed then quiz passed.
$result = self::RESULT_PASS;
break;
} else {
$result = self::RESULT_FAIL;
}
}
}
return $result;
}
/**
* Get quiz attempt details
*
* @since 2.6.1
*
* @param integer $attempt_id attempt id.
*
* @return mixed
*/
public static function quiz_attempt_details( int $attempt_id ) {
global $wpdb;
$table_quiz_attempt_answers = $wpdb->prefix . 'tutor_quiz_attempt_answers';
$table_quiz_questions = $wpdb->prefix . 'tutor_quiz_questions';
$table_quiz_attempts = $wpdb->prefix . 'tutor_quiz_attempts';
$table_quiz_question_answers = $wpdb->prefix . 'tutor_quiz_question_answers';
$query = "SELECT
ques.question_id,
ques.question_title,
ques.question_type,
(
SELECT
GROUP_CONCAT(answer_title)
FROM
{$table_quiz_question_answers}
WHERE
belongs_question_id = ques.question_id
AND is_correct = 1
) AS correct_answers,
(
SELECT
CASE
WHEN CHAR_LENGTH(att_ans.given_answer) = 1 AND att_ans.given_answer REGEXP '^[0-9]$' THEN
-- If given_answer is a single digit integer
(
SELECT
answer_title
FROM
{$table_quiz_question_answers}
WHERE
answer_id = CAST(att_ans.given_answer AS UNSIGNED)
)
WHEN CHAR_LENGTH(att_ans.given_answer) > 1 AND SUBSTRING(att_ans.given_answer, 1, 2) = 'a:' THEN
-- If given_answer is serialized array
(
att_ans.given_answer
)
ELSE
-- If given_answer is a serialized string
att_ans.given_answer
END
) AS given_answer,
att_ans.question_mark,
att_ans.achieved_mark,
att_ans.is_correct,
(
SELECT
attempt_info
FROM {$table_quiz_attempts}
WHERE attempt_id = {$attempt_id}
LIMIT 1
) AS attempt_info
FROM
{$table_quiz_attempt_answers} AS att_ans
JOIN {$table_quiz_questions} AS ques ON ques.question_id = att_ans.question_id
JOIN {$table_quiz_question_answers} AS ans ON ans.answer_id = att_ans.attempt_answer_id
WHERE
quiz_attempt_id = %d
LIMIT
50
";
//phpcs:ignore
$result = $wpdb->get_results( $wpdb->prepare( $query, $attempt_id ) );
// If array and count result then loop with each result and prepare given answer.
if ( is_array( $result ) && count( $result ) ) {
foreach ( $result as $key => $value ) {
// Check if given answer is a serialized string.
if ( is_serialized( $value->given_answer ) ) {
$given_answers = tutor_utils()->get_answer_by_id( maybe_unserialize( $value->given_answer ) );
$result[ $key ]->given_answer = array_column( $given_answers, 'answer_title' );
} elseif ( is_numeric( $value->given_answer ) ) {
$given_answers = tutor_utils()->get_answer_by_id( maybe_unserialize( $value->given_answer ) );
$result[ $key ]->given_answer = array_column( $given_answers, 'answer_title' );
}
}
}
return $result;
}
/**
* Get a question record.
*
* @since 3.0.0
*
* @param int $question_id quiz question id.
*
* @return array|object|null|void
*/
public static function get_question( $question_id ) {
global $wpdb;
return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}tutor_quiz_questions WHERE question_id = %d", $question_id ) );
}
/**
* Get all answer's of a quiz question.
*
* @since 3.0.0
*
* @param int $question_id question id.
* @param string $question_type question type.
*
* @return array
*/
public static function get_question_answers( $question_id, $question_type = null ) {
global $wpdb;
$query = "SELECT * FROM {$wpdb->prefix}tutor_quiz_question_answers WHERE belongs_question_id = %d";
if ( $question_type ) {
$query .= ' AND belongs_question_type = %s ORDER BY answer_order ASC';
//phpcs:ignore
$answers = $wpdb->get_results( $wpdb->prepare( $query, $question_id, $question_type ) );
} else {
$query .= ' ORDER BY answer_order ASC';
//phpcs:ignore
$answers = $wpdb->get_results( $wpdb->prepare( $query, $question_id ) );
}
foreach ( $answers as $answer ) {
$answer->answer_title = stripslashes( $answer->answer_title );
if ( $answer->image_id ) {
$answer->image_url = wp_get_attachment_url( $answer->image_id );
}
}
return $answers;
}
/**
* Get next answer order SL no
*
* @since 3.0.0
*
* @param int $question_id question id.
* @param int $question_type question type.
*
* @return int
*/
public static function get_next_answer_order( $question_id, $question_type ) {
global $wpdb;
$max_id = (int) $wpdb->get_var(
$wpdb->prepare(
"SELECT MAX(answer_order) FROM {$wpdb->prefix}tutor_quiz_question_answers WHERE belongs_question_id = %d AND belongs_question_type = %s",//phpcs:ignore
$question_id,
$question_type
)
);
return $max_id + 1;
}
/**
* Get quiz details by quiz id.
*
* @since 3.0.0
*
* @param int $quiz_id quiz id.
*
* @return object
*/
public static function get_quiz_details( $quiz_id ) {
$quiz = get_post( $quiz_id );
$quiz->quiz_option = get_post_meta( $quiz_id, Quiz::META_QUIZ_OPTION, true );
$quiz->questions = tutor_utils()->get_questions_by_quiz( $quiz_id );
if ( ! is_array( $quiz->questions ) ) {
$quiz->questions = array();
}
foreach ( $quiz->questions as $question ) {
$question->question_answers = self::get_question_answers( $question->question_id, $question->question_type );
if ( isset( $question->question_settings ) ) {
$question->question_settings = maybe_unserialize( $question->question_settings );
}
}
return $quiz;
}
/**
* Calculate attempt earned percentage
*
* @since 3.7.1
*
* @param int|object $attempt attempt id or attempt object.
*
* @return float
*/
public static function calculate_attempt_earned_percentage( $attempt ) {
if ( is_numeric( $attempt ) ) {
$attempt = tutor_utils()->get_attempt( $attempt );
}
if ( ! $attempt ) {
return 0;
}
$total_marks = (float) $attempt->total_marks;
$earned_marks = (float) $attempt->earned_marks;
$earned_percentage = ( $earned_marks > 0 && $total_marks > 0 )
? number_format( ( $earned_marks * 100 ) / $total_marks )
: 0;
return $earned_percentage;
}
/**
* Prepare attempt result from attempt data.
*
* @since 3.7.1
*
* @param int|object $attempt attempt id or object.
*
* @return string pass, fail, pending
*/
public static function prepare_attempt_result( $attempt ) {
if ( is_numeric( $attempt ) ) {
$attempt = tutor_utils()->get_attempt( $attempt );
}
if ( ! $attempt ) {
return false;
}
$attempt_info = maybe_unserialize( $attempt->attempt_info );
$answers_map = self::get_quiz_answers_by_attempt_id( $attempt->attempt_id, true );
$earned_percentage = self::calculate_attempt_earned_percentage( $attempt );
$passing_grade = (int) $attempt_info['passing_grade'] ?? 0;
$answers = $answers_map[ $attempt->attempt_id ] ?? array();
$answers = is_array( $answers ) ? $answers : array();
$has_pending = (bool) count(
array_filter( $answers, fn( $answer ) => null === $answer->is_correct )
);
if ( $has_pending ) {
$result = self::RESULT_PENDING;
} else {
$result = ( $earned_percentage >= $passing_grade ) ? self::RESULT_PASS : self::RESULT_FAIL;
}
return $result;
}
/**
* Get a quiz attempt result
*
* @since 3.7.1
*
* @param int $attempt_id attempt id.
*
* @return string pass, fail, pending
*/
public static function get_attempt_result( $attempt_id ) {
$attempt = tutor_utils()->get_attempt( $attempt_id );
if ( ! $attempt ) {
return false;
}
/**
* If result is processed then return it
*
* @since 3.7.1
*/
if ( isset( $attempt->result ) && ! empty( $attempt->result ) ) {
return $attempt->result;
}
/**
* Backward compatibility for getting a attempt result
*
* @since 3.7.1
*/
$result = self::prepare_attempt_result( $attempt );
return $result;
}
/**
* Update attempt result.
*
* @param int $attempt_id attempt id.
*
* @return bool
*/
public static function update_attempt_result( $attempt_id ) {
$attempt_result = self::prepare_attempt_result( $attempt_id );
if ( $attempt_result ) {
QueryHelper::update(
QueryHelper::prepare_table_name( 'tutor_quiz_attempts' ),
array( 'result' => $attempt_result ),
array( 'attempt_id' => $attempt_id )
);
return true;
}
return false;
}
}