QuizModel.php — Tutor LMS – eLearning and online course solution 4.0.1

tutor / models / QuizModel.php

tutor / models Last commit date

BaseModel.php 10 months ago BillingModel.php 1 year ago CartItemModel.php 10 months ago CartModel.php 4 days ago CouponModel.php 4 days ago CourseModel.php 4 days ago EnrollmentModel.php 4 days ago LessonModel.php 9 months ago OrderActivitiesModel.php 1 year ago OrderItemMetaModel.php 10 months ago OrderItemModel.php 10 months ago OrderMetaModel.php 1 year ago OrderModel.php 4 days ago QuizModel.php 1 day ago UserModel.php 1 year ago WithdrawModel.php 4 days ago

QuizModel.php

1730 lines

1	<?php
2	/**
3	* Quiz Model
4	*
5	* @package Tutor\Models
6	* @author Themeum <support@themeum.com>
7	* @link https://themeum.com
8	* @since 2.0.10
9	*/
10
11	namespace Tutor\Models;
12
13	use Tutor\Cache\TutorCache;
14	use Tutor\Helpers\DateTimeHelper;
15	use Tutor\Helpers\QueryHelper;
16	use TUTOR\Quiz;
17
18	/**
19	* Class QuizModel
20	*
21	* @since 2.0.10
22	*/
23	class QuizModel {
24
25	const ATTEMPT_STARTED = 'attempt_started';
26	const ATTEMPT_ENDED = 'attempt_ended';
27	const REVIEW_REQUIRED = 'review_required';
28	const ATTEMPT_TIMEOUT = 'attempt_timeout';
29
30	const RESULT_PASS = 'pass';
31	const RESULT_FAIL = 'fail';
32	const RESULT_PENDING = 'pending';
33
34	const ATTEMPTS_TABLE = 'tutor_quiz_attempts';
35
36	/**
37	* Question type constants
38	*
39	* @since 4.0.0
40	*
41	* @var string
42	*/
43	const QUESTION_TYPE_TRUE_FALSE = 'true_false';
44	const QUESTION_TYPE_SINGLE_CHOICE = 'single_choice';
45	const QUESTION_TYPE_MULTIPLE_CHOICE = 'multiple_choice';
46	const QUESTION_TYPE_OPEN_ENDED = 'open_ended';
47	const QUESTION_TYPE_FILL_IN_THE_BLANK = 'fill_in_the_blank';
48	const QUESTION_TYPE_SHORT_ANSWER = 'short_answer';
49	const QUESTION_TYPE_MATCHING = 'matching';
50	const QUESTION_TYPE_IMAGE_MATCHING = 'image_matching';
51	const QUESTION_TYPE_IMAGE_ANSWERING = 'image_answering';
52	const QUESTION_TYPE_ORDERING = 'ordering';
53
54	/**
55	* Newly added question types.
56	* These questions types are not supported in legacy learning mode.
57	*
58	* @since 4.0.0
59	*/
60	const QUESTION_TYPE_DRAW_IMAGE = 'draw_image';
61	const QUESTION_TYPE_SCALE = 'scale';
62	const QUESTION_TYPE_PIN_IMAGE = 'pin_image';
63	const QUESTION_TYPE_COORDINATES = 'coordinates';
64	const QUESTION_TYPE_PUZZLE = 'puzzle';
65
66	/**
67	* Get quiz table name
68	*
69	* @since 2.1.0
70	*
71	* @return string
72	*/
73	public function get_table(): string {
74	return QueryHelper::prepare_table_name( self::ATTEMPTS_TABLE );
75	}
76
77
78	/**
79	* Get all quiz types which are only available in modern learning mode.
80	*
81	* @since 4.0.0
82	*
83	* @return array
84	*/
85	public static function get_modern_mode_quiz_types() {
86	return array(
87	self::QUESTION_TYPE_DRAW_IMAGE => array(
88	'name' => __( 'Image Marking', 'tutor' ),
89	'icon' => '<span class="tooltip-btn"><i class="tutor-quiz-type-icon tutor-quiz-type-draw-image tutor-icon-image"></i></span>',
90	'is_pro' => true,
91	),
92	self::QUESTION_TYPE_SCALE => array(
93	'name' => __( 'Range', 'tutor' ),
94	'icon' => '<span class="tooltip-btn"><i class="tutor-quiz-type-icon tutor-quiz-type-scale tutor-icon-slider-horizontal"></i></span>',
95	'is_pro' => true,
96	),
97	self::QUESTION_TYPE_PIN_IMAGE => array(
98	'name' => __( 'Pin', 'tutor' ),
99	'icon' => '<span class="tooltip-btn"><i class="tutor-quiz-type-icon tutor-quiz-type-pin-image tutor-icon-image"></i></span>',
100	'is_pro' => true,
101	),
102	self::QUESTION_TYPE_COORDINATES => array(
103	'name' => __( 'Graph', 'tutor' ),
104	'icon' => '<span class="tooltip-btn"><i class="tutor-quiz-type-icon tutor-quiz-type-coordinates tutor-icon-grid"></i></span>',
105	'is_pro' => true,
106	),
107	self::QUESTION_TYPE_PUZZLE => array(
108	'name' => __( 'Puzzle', 'tutor' ),
109	'icon' => '<span class="tooltip-btn"><i class="tutor-quiz-type-icon tutor-quiz-type-puzzle tutor-icon-images"></i></span>',
110	'is_pro' => true,
111	),
112	);
113	}
114
115	/**
116	* Get all question types
117	*
118	* @since 1.0.0
119	*
120	* @param mixed $type type.
121	*
122	* @return array\|mixed
123	*/
124	public static function get_question_types( $type = '' ) {
125	$types = array(
126	self::QUESTION_TYPE_TRUE_FALSE => array(
127	'name' => __( 'True/False', 'tutor' ),
128	'icon' => '<span class="tooltip-btn" ><i class="tutor-quiz-type-icon tutor-quiz-type-boolean tutor-icon-circle-half"></i></span>',
129	'is_pro' => false,
130	),
131	self::QUESTION_TYPE_SINGLE_CHOICE => array(
132	'name' => __( 'Single Choice', 'tutor' ),
133	'icon' => '<span class="tooltip-btn"><i class="tutor-quiz-type-icon tutor-quiz-type-single-choice tutor-icon-mark"></i></span>',
134	'is_pro' => false,
135	),
136	self::QUESTION_TYPE_MULTIPLE_CHOICE => array(
137	'name' => __( 'Multiple Choice', 'tutor' ),
138	'icon' => '<span class="tooltip-btn"><i class="tutor-quiz-type-icon tutor-quiz-type-multiple-choices tutor-icon-double-mark"></i></span>',
139	'is_pro' => false,
140	),
141	self::QUESTION_TYPE_OPEN_ENDED => array(
142	'name' => __( 'Open Ended', 'tutor' ),
143	'icon' => '<span class="tooltip-btn"><i class="tutor-quiz-type-icon tutor-quiz-type-open-ended tutor-icon-text-width"></i></span>',
144	'is_pro' => false,
145	),
146	self::QUESTION_TYPE_FILL_IN_THE_BLANK => array(
147	'name' => __( 'Fill In The Blanks', 'tutor' ),
148	'icon' => '<span class="tooltip-btn" ><i class="tutor-quiz-type-icon tutor-quiz-type-fill-blanks tutor-icon-hourglass"></i></span>',
149	'is_pro' => false,
150	),
151	self::QUESTION_TYPE_SHORT_ANSWER => array(
152	'name' => __( 'Short Answer', 'tutor' ),
153	'icon' => '<span class="tooltip-btn"><i class="tutor-quiz-type-icon tutor-quiz-type-short-answer tutor-icon-minimize"></i></span>',
154	'is_pro' => true,
155	),
156	self::QUESTION_TYPE_MATCHING => array(
157	'name' => __( 'Matching', 'tutor' ),
158	'icon' => '<span class="tooltip-btn"><i class="tutor-quiz-type-icon tutor-quiz-type-matching tutor-icon-arrow-right-left"></i></span>',
159	'is_pro' => true,
160	),
161	self::QUESTION_TYPE_IMAGE_MATCHING => array(
162	'name' => __( 'Image Matching', 'tutor' ),
163	'icon' => '<span class="tooltip-btn"><i class="tutor-quiz-type-icon tutor-quiz-type-image-matching tutor-icon-images"></i></span>',
164	'is_pro' => true,
165	),
166	self::QUESTION_TYPE_IMAGE_ANSWERING => array(
167	'name' => __( 'Image Answering', 'tutor' ),
168	'icon' => '<span class="tooltip-btn"><i class="tutor-quiz-type-icon tutor-quiz-type-image-answering tutor-icon-camera"></i></span>',
169	'is_pro' => true,
170	),
171	self::QUESTION_TYPE_ORDERING => array(
172	'name' => __( 'Ordering', 'tutor' ),
173	'icon' => '<span class="tooltip-btn"><i class="tutor-quiz-type-icon tutor-quiz-type-ordering tutor-icon-ordering-z-a"></i></span>',
174	'is_pro' => true,
175	),
176	);
177
178	$types = $types + self::get_modern_mode_quiz_types();
179	$types = apply_filters( 'tutor_get_question_types', $types );
180
181	if ( isset( $types[ $type ] ) ) {
182	return $types[ $type ];
183	}
184
185	return $types;
186	}
187
188	/**
189	* Get manual review types.
190	*
191	* @since 3.8.0
192	*
193	* @return array
194	*/
195	public static function get_manual_review_types() {
196	return array(
197	self::QUESTION_TYPE_OPEN_ENDED,
198	self::QUESTION_TYPE_SHORT_ANSWER,
199	self::QUESTION_TYPE_IMAGE_ANSWERING,
200	);
201	}
202
203
204	/**
205	* Format the quiz attempts result obtained from query.
206	*
207	* @since 4.0.0
208	*
209	* @param array $quiz_attempts the quiz_attempts result obtained from query.
210	* @param string $result attempt result type.
211	*
212	* @return array
213	*/
214	public static function format_quiz_attempts( array $quiz_attempts, string $result = '' ): array {
215	$formatted_attempts = array();
216
217	if ( ! count( $quiz_attempts ) ) {
218	return $quiz_attempts;
219	}
220
221	foreach ( $quiz_attempts as $quiz_attempt ) {
222	$course_title = $quiz_attempt->course_title ?? '';
223	$quiz_title = $quiz_attempt->post_title ?? '';
224
225	$quiz_attempt_result = $quiz_attempt->result ?? 'fail';
226	$result_types = array( self::RESULT_FAIL, self::RESULT_PASS, self::RESULT_PENDING );
227
228	if ( ! empty( $result ) && in_array( $result, $result_types, true ) && $quiz_attempt_result !== $result ) {
229	continue;
230	}
231
232	// Common formatting logic.
233	$start_time = DateTimeHelper::create( $quiz_attempt->attempt_started_at ?? '' )
234	->format( get_option( 'date_format' ) . ', ' . get_option( 'time_format' ) );
235
236	$attempt_time = strtotime( $quiz_attempt->attempt_ended_at ?? '' ) - strtotime( $quiz_attempt->attempt_started_at ?? '' );
237	$attempt_time = tutor_utils()->playtime_string( $attempt_time );
238
239	$earned_percent = self::calculate_attempt_earned_percentage( $quiz_attempt );
240
241	$correct_answers = 0;
242	$incorrect_answers = 0;
243
244	$answers = self::get_quiz_answers_by_attempt_id( $quiz_attempt->attempt_id );
245
246	if ( tutor_utils()->count( $answers ) ) {
247	foreach ( $answers as $answer ) {
248	$is_correct = (int) $answer->is_correct ?? 0;
249	if ( $is_correct ) {
250	++$correct_answers;
251	} else {
252	++$incorrect_answers;
253	}
254	}
255	}
256
257	$formatted_attempt = array(
258	'attempt_id' => $quiz_attempt->attempt_id ?? 0,
259	'result' => $quiz_attempt_result,
260	'marks_percent' => $earned_percent ?? 0,
261	'correct_answers' => $correct_answers,
262	'incorrect_answers' => $incorrect_answers,
263	'time_taken' => $attempt_time ?? '',
264	'date' => $start_time ?? '',
265	'quiz_title' => $quiz_title,
266	'course_title' => $course_title,
267	'quiz_id' => $quiz_attempt->quiz_id ?? 0,
268	'course_id' => $quiz_attempt->course_id ?? 0,
269	'student' => $quiz_attempt->display_name ?? '',
270	'attempt_info' => maybe_unserialize( $quiz_attempt->attempt_info ) ?? array(),
271	);
272
273	$formatted_attempts[] = $formatted_attempt;
274	}
275
276	return $formatted_attempts;
277	}
278
279	/**
280	* Get formatted quiz attempt list by quiz data.
281	*
282	* @since 4.0.0
283	*
284	* @param array $quizzes list of quiz data.
285	* @param string $result attempt result type.
286	*
287	* @return array
288	*/
289	public function get_formatted_quiz_attempt_list_by_quiz_id( $quizzes, $result = '' ) {
290	$quiz_attempts_list = array();
291
292	if ( ! tutor_utils()->count( $quizzes ) ) {
293	return $quiz_attempts_list;
294	}
295
296	foreach ( $quizzes as $quiz_info ) {
297	$quiz_id = $quiz_info['quiz_id'] ?? 0;
298	$course_id = $quiz_info['course_id'] ?? 0;
299
300	$quiz_attempts = $this->quiz_attempts( $quiz_id, get_current_user_id() );
301	$formatted_quiz_attempts = self::format_quiz_attempts( $quiz_attempts, $result );
302
303	if ( ! tutor_utils()->count( $formatted_quiz_attempts ) ) {
304	continue;
305	}
306
307	$quiz_attempts_list[ $quiz_id ] = array(
308	'quiz_id' => $quiz_id,
309	'quiz_title' => get_the_title( $quiz_id ),
310	'course_title' => get_the_title( $course_id ),
311	'course_id' => $course_id,
312	'attempts' => array(),
313	);
314
315	foreach ( $formatted_quiz_attempts as $attempt ) {
316	$quiz_attempts_list[ $quiz_id ]['attempts'][] = $attempt;
317	}
318	}
319
320	return $quiz_attempts_list;
321	}
322
323	/**
324	* Get total number of quiz
325	*
326	* @since 2.0.2
327	*
328	* @since 3.7.1 Course ids param added
329	*
330	* @param array $course_ids Array of course ids.
331	*
332	* @return int
333	*/
334	public static function get_total_quiz( array $course_ids = array() ) {
335	global $wpdb;
336
337	$course_in_clause = '';
338	if ( count( $course_ids ) ) {
339	$prepare_ids = QueryHelper::prepare_in_clause( $course_ids );
340	$course_in_clause = "AND course.ID IN ({$prepare_ids})";
341	}
342
343	$sql = "SELECT COUNT(DISTINCT quiz.ID)
344	FROM {$wpdb->posts} quiz
345	INNER JOIN {$wpdb->posts} topic ON quiz.post_parent=topic.ID
346	INNER JOIN {$wpdb->posts} course ON topic.post_parent=course.ID
347	WHERE 1 = 1
348	{$course_in_clause}
349	AND course.post_type=%s
350	AND course.post_status = 'publish'
351	AND quiz.post_type='tutor_quiz'";
352
353	//phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
354	return $wpdb->get_var( $wpdb->prepare( $sql, tutor()->course_post_type ) );
355	}
356
357	/**
358	* Get Attempt row by grade method settings
359	*
360	* @since 1.4.2
361	*
362	* @param int $quiz_id quiz id.
363	* @param int $user_id user id.
364	*
365	* @return array\|bool\|null\|object
366	*/
367	public function get_quiz_attempt( $quiz_id = 0, $user_id = 0 ) {
368	global $wpdb;
369
370	$quiz_id = tutils()->get_post_id( $quiz_id );
371	$user_id = tutils()->get_user_id( $user_id );
372
373	$attempt = false;
374
375	$quiz_grade_method = get_tutor_option( 'quiz_grade_method', 'highest_grade' );
376	$from_string = "FROM {$wpdb->tutor_quiz_attempts} WHERE quiz_id = %d AND user_id = %d AND attempt_status != 'attempt_started' ";
377
378	//phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
379	if ( 'highest_grade' === $quiz_grade_method ) {
380	$attempt = $wpdb->get_row( $wpdb->prepare( "SELECT * {$from_string} ORDER BY earned_marks DESC LIMIT 1; ", $quiz_id, $user_id ) );
381	} elseif ( 'average_grade' === $quiz_grade_method ) {
382
383	$attempt = $wpdb->get_row(
384	$wpdb->prepare(
385	"SELECT {$wpdb->tutor_quiz_attempts}.*,
386	COUNT(attempt_id) AS attempt_count,
387	AVG(total_marks) AS total_marks,
388	AVG(earned_marks) AS earned_marks {$from_string}
389	",
390	$quiz_id,
391	$user_id
392	)
393	);
394	} elseif ( 'first_attempt' === $quiz_grade_method ) {
395
396	$attempt = $wpdb->get_row( $wpdb->prepare( "SELECT * {$from_string} ORDER BY attempt_id ASC LIMIT 1; ", $quiz_id, $user_id ) );
397	} elseif ( 'last_attempt' === $quiz_grade_method ) {
398
399	$attempt = $wpdb->get_row( $wpdb->prepare( "SELECT * {$from_string} ORDER BY attempt_id DESC LIMIT 1; ", $quiz_id, $user_id ) );
400	}
401	//phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
402
403	return $attempt;
404	}
405
406	/**
407	* Get all of the attempts by an user of a quiz
408	*
409	* @since 1.0.0
410	*
411	* @param int $quiz_id quiz ID.
412	* @param int $user_id user ID.
413	*
414	* @return array\|bool\|null\|object
415	*/
416	public function quiz_attempts( $quiz_id = 0, $user_id = 0 ) {
417	global $wpdb;
418
419	$quiz_id = tutor_utils()->get_post_id( $quiz_id );
420	$user_id = tutor_utils()->get_user_id( $user_id );
421
422	$cache_key = "tutor_quiz_attempts_for_{$user_id}_{$quiz_id}";
423	$attempts = TutorCache::get( $cache_key );
424
425	if ( false === $attempts ) {
426	$attempts = $wpdb->get_results(
427	$wpdb->prepare(
428	"SELECT *
429	FROM {$wpdb->prefix}tutor_quiz_attempts
430	WHERE quiz_id = %d
431	AND user_id = %d
432	ORDER BY attempt_id DESC
433	",
434	$quiz_id,
435	$user_id
436	)
437	);
438	TutorCache::set( $cache_key, $attempts );
439	}
440
441	if ( is_array( $attempts ) && count( $attempts ) ) {
442	return $attempts;
443	}
444
445	return false;
446	}
447
448	/**
449	* Get Quiz question by question id
450	*
451	* @since 1.0.0
452	*
453	* @param int $question_id question ID.
454	*
455	* @return array\|bool\|object\|void\|null
456	*/
457	public static function get_quiz_question_by_id( $question_id = 0 ) {
458	global $wpdb;
459
460	if ( $question_id ) {
461	$question = $wpdb->get_row(
462	$wpdb->prepare(
463	"SELECT *
464	FROM {$wpdb->prefix}tutor_quiz_questions
465	WHERE question_id = %d
466	LIMIT 0, 1;
467	",
468	$question_id
469	)
470	);
471
472	return $question;
473	}
474
475	return false;
476	}
477
478	/**
479	* Get all ended attempts by an user of a quiz
480	*
481	* @since 1.4.1
482	*
483	* @param int $quiz_id quiz ID.
484	* @param int $user_id user ID.
485	*
486	* @return array\|bool\|null\|object
487	*/
488	public function quiz_ended_attempts( $quiz_id = 0, $user_id = 0 ) {
489	global $wpdb;
490
491	$quiz_id = tutor_utils()->get_post_id( $quiz_id );
492	$user_id = tutor_utils()->get_user_id( $user_id );
493
494	$attempts = $wpdb->get_results(
495	$wpdb->prepare(
496	"SELECT *
497	FROM {$wpdb->prefix}tutor_quiz_attempts
498	WHERE quiz_id = %d
499	AND user_id = %d
500	AND attempt_status != %s
501	",
502	$quiz_id,
503	$user_id,
504	'attempt_started'
505	)
506	);
507
508	if ( is_array( $attempts ) && count( $attempts ) ) {
509	return $attempts;
510	}
511
512	return false;
513	}
514
515	/**
516	* Get the next question order ID
517	*
518	* @since 1.0.0
519	*
520	* @param integer $quiz_id quiz ID.
521	*
522	* @return int
523	*/
524	public static function quiz_next_question_order_id( $quiz_id ) {
525	global $wpdb;
526
527	$last_order = (int) $wpdb->get_var(
528	$wpdb->prepare(
529	"SELECT MAX(question_order)
530	FROM {$wpdb->prefix}tutor_quiz_questions
531	WHERE quiz_id = %d ;
532	",
533	$quiz_id
534	)
535	);
536
537	return $last_order + 1;
538	}
539
540	/**
541	* Get next quiz question ID
542	*
543	* @since 1.0.0
544	*
545	* @return int
546	*/
547	public static function quiz_next_question_id() {
548	global $wpdb;
549
550	$last_order = (int) $wpdb->get_var( "SELECT MAX(question_id) FROM {$wpdb->prefix}tutor_quiz_questions;" );
551	return $last_order + 1;
552	}
553
554	/**
555	* Total number of quiz attempts
556	*
557	* @since 1.0.0
558	*
559	* @param string $search_term search term.
560	* @param integer $course_id course ID.
561	* @param string $tab tab.
562	* @param string $date_filter date filter.
563	*
564	* @return int
565	*/
566	public static function get_total_quiz_attempts( $search_term = '', int $course_id = 0, string $tab = '', $date_filter = '' ) {
567	global $wpdb;
568
569	// Prepare search term.
570	$search_term = empty( $search_term ) ? '' : '%' . $wpdb->esc_like( $search_term ) . '%';
571
572	// Prepare course filter.
573	$course_filter = $course_id ? $wpdb->prepare( ' AND quiz_attempts.course_id = %d', $course_id ) : '';
574
575	// Prepare date filter.
576	$date_filter = empty( $date_filter ) ? '' : tutor_get_formated_date( 'Y-m-d', $date_filter );
577	$date_filter = empty( $date_filter ) ? '' : $wpdb->prepare( ' AND DATE(quiz_attempts.attempt_started_at) = %s ', $date_filter );
578
579	// Prepare user join and clause.
580	$user_join = '';
581	$user_clause = '';
582	if ( '' !== $search_term ) {
583	$user_join = "INNER JOIN {$wpdb->users} ON quiz_attempts.user_id = {$wpdb->users}.ID";
584	$user_clause = $wpdb->prepare(
585	" AND ( {$wpdb->users}.user_email LIKE %s OR {$wpdb->users}.display_name LIKE %s )",
586	$search_term,
587	$search_term
588	);
589	}
590
591	// Prepare tab filter.
592	$tab_clause = '';
593	if ( in_array( $tab, array( self::RESULT_PASS, self::RESULT_FAIL, self::RESULT_PENDING ), true ) ) {
594	$tab_clause = $wpdb->prepare( ' AND quiz_attempts.result = %s', $tab );
595	}
596
597	//phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
598	$count = $wpdb->get_var(
599	$wpdb->prepare(
600	"SELECT COUNT(DISTINCT quiz_attempts.attempt_id) AS total
601	FROM {$wpdb->prefix}tutor_quiz_attempts quiz_attempts
602	INNER JOIN {$wpdb->posts} quiz ON quiz_attempts.quiz_id = quiz.ID
603	INNER JOIN {$wpdb->posts} AS course ON course.ID = quiz_attempts.course_id
604	{$user_join}
605	WHERE attempt_status != %s AND quiz_attempts.result IS NOT NULL
606	{$user_clause}
607	{$course_filter}
608	{$tab_clause}
609	{$date_filter}",
610	'attempt_started'
611	)
612	);
613	//phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
614
615	return (int) $count;
616	}
617
618	/**
619	* Get the all quiz attempts
620	*
621	* @since 1.0.0
622	* @since 1.9.5 sorting params added.
623	* @since 3.8.0 refactor and query optimize.
624	* @since 4.0.0 date-range filtering added via $start_date / $end_date.
625	*
626	* @param integer $start Query offset.
627	* @param integer $limit Number of rows to return (0 = no limit).
628	* @param string $search_filter Search keyword matched against user email, display name, quiz title, and course title.
629	* @param string $course_filter Course ID (or array of IDs) to restrict results to.
630	* @param string $start_date Range start date (Y-m-d).
631	* @param string $end_date Range end date (Y-m-d).
632	* @param string $order_filter SQL ORDER BY direction – 'ASC' or 'DESC'.
633	* @param mixed $result_state Attempt result to filter by (pass\|fail\|pending). Null returns all results.
634	* @param boolean $count_only When true, returns an integer count instead of rows.
635	* @param boolean $instructor_id_check When true, restricts results to courses the current user instructs.
636	*
637	* @return mixed Integer count when $count_only is true, array of row objects otherwise.
638	*/
639	public static function get_quiz_attempts( $start = 0, $limit = 10, $search_filter = '', $course_filter = array(), $start_date = '', $end_date = '', $order_filter = 'DESC', $result_state = null, $count_only = false, $instructor_id_check = false ) {
640	global $wpdb;
641
642	$start = (int) $start;
643	$limit = (int) $limit;
644	$search_filter = sanitize_text_field( $search_filter );
645	$course_filter = sanitize_text_field( $course_filter );
646	$order_filter = sanitize_sql_orderby( $order_filter );
647
648	$search_term_raw = $search_filter;
649	$search_filter = '%' . $wpdb->esc_like( $search_filter ) . '%';
650
651	// Filter by course.
652	if ( '' !== $course_filter ) {
653	if ( ! is_array( $course_filter ) ) {
654	$course_filter = array( $course_filter );
655	}
656
657	$course_ids = QueryHelper::prepare_in_clause( array_map( 'intval', $course_filter ) );
658	$course_filter = " AND quiz_attempts.course_id IN ($course_ids) ";
659	}
660
661	// Filter by date (single) or date range.
662	$date_filter = '';
663	if ( '' !== $start_date && '' !== $end_date ) {
664	$start_date = tutor_get_formated_date( 'Y-m-d', $start_date );
665	$end_date = tutor_get_formated_date( 'Y-m-d', $end_date );
666	$date_filter = $wpdb->prepare( ' AND DATE(quiz_attempts.attempt_started_at) BETWEEN %s AND %s ', $start_date, $end_date );
667	}
668
669	$result_clause = '';
670	$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, course.post_title as course_title';
671	$limit_offset = $count_only \|\| ( 0 === $limit && 0 === $start ) ? '' : $wpdb->prepare( ' LIMIT %d OFFSET %d', $limit, $start );
672
673	// Get attempts by instructor ID.
674	$instructor_clause = '';
675	if ( $instructor_id_check ) {
676	$current_user_id = get_current_user_id();
677	$instructor_id = tutor_utils()->has_user_role( 'administrator', $current_user_id ) ? null : $current_user_id;
678
679	if ( $instructor_id ) {
680	$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) ";
681	}
682	}
683
684	$result_clause = '';
685	if ( in_array( $result_state, array( self::RESULT_PASS, self::RESULT_FAIL, self::RESULT_PENDING ), true ) ) {
686	$result_clause = $wpdb->prepare( ' AND quiz_attempts.result = %s', $result_state );
687	}
688
689	//phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
690	$query = $wpdb->prepare(
691	"SELECT {$select_columns}
692	FROM {$wpdb->prefix}tutor_quiz_attempts quiz_attempts
693	INNER JOIN {$wpdb->posts} quiz ON quiz_attempts.quiz_id = quiz.ID
694	INNER JOIN {$wpdb->users} AS users ON quiz_attempts.user_id = users.ID
695	INNER JOIN {$wpdb->posts} AS course ON course.ID = quiz_attempts.course_id
696	{$instructor_clause}
697	WHERE quiz_attempts.attempt_ended_at IS NOT NULL
698	AND (
699	users.user_email = %s
700	OR users.display_name LIKE %s
701	OR quiz.post_title LIKE %s
702	OR course.post_title LIKE %s
703	)
704	AND quiz_attempts.attempt_ended_at IS NOT NULL
705	{$result_clause}
706	{$course_filter}
707	{$date_filter}
708	ORDER BY quiz_attempts.attempt_ended_at {$order_filter} {$limit_offset}",
709	$search_term_raw,
710	$search_filter,
711	$search_filter,
712	$search_filter
713	);
714	//phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
715
716	//phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
717	return $count_only ? $wpdb->get_var( $query ) : $wpdb->get_results( $query );
718	}
719
720	/**
721	* Delete quiz attempt for user
722	*
723	* @since 1.9.5
724	*
725	* @param mixed $attempt_ids attempt ids.
726	*
727	* @return void
728	*/
729	public static function delete_quiz_attempt( $attempt_ids ) {
730	// Singlular to array.
731	global $wpdb;
732
733	// Singular to array.
734	! is_array( $attempt_ids ) ? $attempt_ids = array( $attempt_ids ) : 0;
735	$attempt_ids = array_map( 'absint', array_filter( $attempt_ids ) );
736
737	if ( count( $attempt_ids ) ) {
738	// Collect file paths from all question types that store files (e.g. draw_image). Files deleted after DB for safety.
739	$attempt_file_paths = apply_filters( 'tutor_quiz/attempt_file_paths_for_deletion', array(), $attempt_ids );
740	$attempt_file_paths = is_array( $attempt_file_paths ) ? array_values( array_filter( array_unique( $attempt_file_paths ) ) ) : array();
741
742	$attempt_ids = QueryHelper::prepare_in_clause( $attempt_ids );
743
744	// Deleting attempt (comment), child attempt and attempt meta (comment meta).
745	$wpdb->query( $wpdb->prepare( "DELETE FROM {$wpdb->prefix}tutor_quiz_attempts WHERE attempt_id IN({$attempt_ids})" ) ); //phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
746	$wpdb->query( $wpdb->prepare( "DELETE FROM {$wpdb->prefix}tutor_quiz_attempt_answers WHERE quiz_attempt_id IN({$attempt_ids})" ) ); //phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
747
748	self::delete_files_by_paths( $attempt_file_paths );
749
750	do_action( 'tutor_quiz/attempt_deleted', $attempt_ids );
751	}
752	}
753
754	/**
755	* Delete files by absolute path (e.g. after DB rows have been removed).
756	*
757	* @since 4.0.0
758	*
759	* @param string[] $paths Array of absolute file paths.
760	*
761	* @return void
762	*/
763	public static function delete_files_by_paths( array $paths ) {
764	foreach ( $paths as $path ) {
765	if ( is_string( $path ) && '' !== $path && is_file( $path ) && is_readable( $path ) ) {
766	wp_delete_file( $path );
767	}
768	}
769	}
770
771	/**
772	* Sorting params added on quiz attempt
773	*
774	* @since 1.9.5
775	*
776	* @param integer $start start.
777	* @param integer $limit limit.
778	* @param array $course_ids course ids.
779	* @param string $search_filter search filter.
780	* @param string $course_filter course filter.
781	* @param string $date_filter date filter.
782	* @param string $order_filter order filter.
783	* @param mixed $user_id user id.
784	* @param boolean $count_only is only count or not.
785	* @param boolean $all_attempt need all atempt or not.
786	*
787	* @return mixed
788	*/
789	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 ) {
790	global $wpdb;
791	$search_filter = sanitize_text_field( $search_filter );
792	$course_filter = (int) sanitize_text_field( $course_filter );
793	$date_filter = sanitize_text_field( $date_filter );
794	$order_filter = sanitize_sql_orderby( $order_filter );
795
796	$course_ids = array_map(
797	function ( $id ) {
798	return "'" . esc_sql( $id ) . "'";
799	},
800	$course_ids
801	);
802
803	$course_ids_in = count( $course_ids ) ? ' AND quiz_attempts.course_id IN (' . implode( ', ', $course_ids ) . ') ' : '';
804
805	$search_filter = $search_filter ? '%' . $wpdb->esc_like( $search_filter ) . '%' : '';
806	$search_term_raw = $search_filter;
807	$search_filter = $search_filter ? $wpdb->prepare( 'AND ( users.user_email = %s OR users.display_name LIKE %s OR quiz.post_title LIKE %s OR course.post_title LIKE %s )', $search_term_raw, $search_filter, $search_filter, $search_filter ) : '';
808
809	$course_filter = 0 !== $course_filter ? " AND quiz_attempts.course_id = $course_filter " : '';
810	$date_filter = '' != $date_filter ? tutor_get_formated_date( 'Y-m-d', $date_filter ) : '';
811	$date_filter = '' != $date_filter ? " AND DATE(quiz_attempts.attempt_started_at) = '$date_filter' " : '';
812	$user_filter = $user_id ? ' AND user_id=\'' . esc_sql( $user_id ) . '\' ' : '';
813
814	$limit_offset = $count_only \|\| ( 0 === $limit && 0 === $start ) ? '' : " LIMIT {$start}, {$limit} ";
815	$select_col = $count_only ? ' COUNT(DISTINCT quiz_attempts.attempt_id) ' : ' quiz_attempts., quiz. ';
816
817	$attempt_type = $all_attempt ? '' : " AND quiz_attempts.attempt_status != 'attempt_started' ";
818
819	$query = "SELECT {$select_col}
820	FROM {$wpdb->prefix}tutor_quiz_attempts AS quiz_attempts
821	INNER JOIN {$wpdb->posts} AS quiz
822	ON quiz_attempts.quiz_id = quiz.ID
823	INNER JOIN {$wpdb->users} AS users
824	ON quiz_attempts.user_id = users.ID
825	INNER JOIN {$wpdb->posts} AS course
826	ON course.ID = quiz_attempts.course_id
827	WHERE 1=1
828	{$attempt_type}
829	{$course_ids_in}
830	{$search_filter}
831	{$course_filter}
832	{$date_filter}
833	{$user_filter}
834	ORDER BY quiz_attempts.attempt_id {$order_filter} {$limit_offset};";
835
836	//phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
837	return $count_only ? $wpdb->get_var( $query ) : $wpdb->get_results( $query );
838	}
839
840	/**
841	* Obtain quiz data based on student quiz attempts.
842	*
843	* @since 4.0.0
844	*
845	* @param integer $user_id the user id.
846	* @param integer $course_id the course id.
847	* @param integer $start the query offset.
848	* @param integer $limit the query limit.
849	* @param string $order_filter filter for ASC or DESC order.
850	* @param array $args list of filters to apply.
851	*
852	* @return array
853	*/
854	public static function get_attempted_quizzes( $user_id = 0, $course_id = 0, $start = 0, $limit = 0, $order_filter = 'DESC', $args = array() ) {
855	$quiz_table = 'posts as quiz';
856	$user_table = 'users as users';
857	$courses_table = 'posts as course';
858
859	$join_conditions = array(
860	array(
861	'type' => 'INNER',
862	'table' => $quiz_table,
863	'on' => 'quiz_attempts.quiz_id = quiz.ID',
864	),
865	array(
866	'type' => 'INNER',
867	'table' => $user_table,
868	'on' => 'quiz_attempts.user_id = users.ID',
869	),
870	array(
871	'type' => 'INNER',
872	'table' => $courses_table,
873	'on' => 'course.ID = quiz_attempts.course_id',
874	),
875	);
876
877	$where_conditions = array(
878	'quiz_attempts.attempt_status' => array( '!=', 'attempt_started' ),
879	'user_id' => $user_id,
880	);
881
882	if ( $course_id ) {
883	$where_conditions['quiz_attempts.course_id'] = $course_id;
884	}
885
886	if ( isset( $args['status'] ) && ! empty( $args['status'] ) ) {
887	$where_conditions['quiz_attempts.result'] = $args['status'];
888	}
889
890	$quiz_ids = QueryHelper::get_joined_data(
891	self::ATTEMPTS_TABLE . ' as quiz_attempts',
892	$join_conditions,
893	array( 'DISTINCT quiz_attempts.quiz_id,quiz_attempts.course_id' ),
894	$where_conditions,
895	array(),
896	'quiz_attempts.quiz_id',
897	$limit,
898	$start,
899	$order_filter,
900	'ARRAY_A',
901	);
902
903	return $quiz_ids;
904	}
905
906	/**
907	* Get answers list by quiz question
908	*
909	* @since 1.0.0
910	*
911	* @param int $question_id question ID.
912	* @param bool $rand rand.
913	*
914	* @return array\|bool\|null\|object
915	*/
916	public static function get_answers_by_quiz_question( $question_id, $rand = false ) {
917	global $wpdb;
918
919	$question = $wpdb->get_row(
920	$wpdb->prepare(
921	"SELECT *
922	FROM {$wpdb->prefix}tutor_quiz_questions
923	WHERE question_id = %d;
924	",
925	$question_id
926	)
927	);
928
929	if ( ! $question ) {
930	return false;
931	}
932
933	$order = ' answer_order ASC ';
934
935	if ( $rand ) {
936	$order = ' RAND() ';
937	}
938
939	//phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
940	$answers = $wpdb->get_results(
941	$wpdb->prepare(
942	"SELECT *
943	FROM {$wpdb->prefix}tutor_quiz_question_answers
944	WHERE belongs_question_id = %d
945	AND belongs_question_type = %s
946	ORDER BY {$order}
947	",
948	$question_id,
949	$question->question_type
950	)
951	);
952	//phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
953
954	return $answers;
955	}
956
957	/**
958	* Get quiz answers by attempt id
959	*
960	* @since 1.0.0
961	*
962	* @param mixed $attempt_id attempt ID.
963	* @param bool $add_index need index or not.
964	*
965	* @return array\|null\|object
966	*/
967	public static function get_quiz_answers_by_attempt_id( $attempt_id, $add_index = false ) {
968	global $wpdb;
969
970	$ids = is_array( $attempt_id ) ? $attempt_id : array( $attempt_id );
971	$ids_in = implode( ',', $ids );
972
973	if ( empty( $ids_in ) ) {
974	// Prevent empty.
975	return array();
976	}
977
978	//phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
979	$results = $wpdb->get_results(
980	"SELECT answers.*,
981	question.*
982	FROM {$wpdb->prefix}tutor_quiz_attempt_answers answers
983	LEFT JOIN {$wpdb->prefix}tutor_quiz_questions question
984	ON answers.question_id = question.question_id
985	WHERE answers.quiz_attempt_id IN ({$ids_in})
986	ORDER BY attempt_answer_id ASC;"
987	);
988	//phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
989
990	if ( $add_index ) {
991	$new_array = array();
992
993	foreach ( $results as $result ) {
994	! isset( $new_array[ $result->quiz_attempt_id ] ) ? $new_array[ $result->quiz_attempt_id ] = array() : 0;
995	$new_array[ $result->quiz_attempt_id ][] = $result;
996	}
997
998	return $new_array;
999	}
1000
1001	return $results;
1002	}
1003
1004	/**
1005	* Check whether an attempt answer should be treated as skipped.
1006	*
1007	* @since 4.0.0
1008	*
1009	* @param object\|null $attempt_answer Attempt answer object.
1010	*
1011	* @return bool
1012	*/
1013	public static function is_attempt_answer_skipped( $attempt_answer ): bool {
1014	if ( ! is_object( $attempt_answer ) ) {
1015	return true;
1016	}
1017
1018	$given_answer = maybe_unserialize( $attempt_answer->given_answer ?? '' );
1019
1020	if ( is_array( $given_answer ) ) {
1021	$given_answer = array_filter(
1022	array_map(
1023	static function ( $item ) {
1024	return trim( wp_strip_all_tags( (string) $item ) );
1025	},
1026	$given_answer
1027	),
1028	static function ( string $item ) {
1029	return '' !== $item;
1030	}
1031	);
1032
1033	return empty( $given_answer );
1034	}
1035
1036	return '' === trim( wp_strip_all_tags( (string) $given_answer ) );
1037	}
1038
1039	/**
1040	* Filter attempt answers for attempt-details views.
1041	*
1042	* @since 4.0.0
1043	*
1044	* @param array $attempt_answers Attempt answer rows.
1045	* @param bool $is_instructor_review Whether the current view is instructor review.
1046	*
1047	* @return array
1048	*/
1049	public static function filter_attempt_answers_for_details( $attempt_answers, bool $is_instructor_review = false ): array {
1050	if ( ! is_array( $attempt_answers ) ) {
1051	return array();
1052	}
1053
1054	return array_values(
1055	array_filter(
1056	$attempt_answers,
1057	static function ( $attempt_answer ) use ( $is_instructor_review ) {
1058	if ( ! is_object( $attempt_answer ) ) {
1059	return false;
1060	}
1061
1062	if ( $is_instructor_review ) {
1063	return true;
1064	}
1065
1066	return ! self::is_attempt_answer_skipped( $attempt_answer );
1067	}
1068	)
1069	);
1070	}
1071
1072	/**
1073	* Get normalized attempt-answer status.
1074	*
1075	* Status rules follow legacy attempt-details logic:
1076	* - correct: is_correct is truthy.
1077	* - pending: is_correct is null for manually reviewed question types.
1078	* - incorrect: all other cases.
1079	*
1080	* @since 4.0.0
1081	*
1082	* @param object $attempt_answer Attempt answer object.
1083	*
1084	* @return string One of: correct, pending, wrong.
1085	*/
1086	public static function get_attempt_answer_status( $attempt_answer ): string {
1087	$question_type = (string) ( $attempt_answer->question_type ?? '' );
1088
1089	if ( 'image_matching' === $question_type ) {
1090	$question_type = 'matching';
1091	}
1092
1093	if ( 'single_choice' === $question_type ) {
1094	$question_type = 'multiple_choice';
1095	}
1096
1097	if ( (bool) ( $attempt_answer->is_correct ?? false ) ) {
1098	return 'correct';
1099	}
1100
1101	if (
1102	null === ( $attempt_answer->is_correct ?? null ) &&
1103	in_array( $question_type, array( 'open_ended', 'short_answer', 'image_answering' ), true )
1104	) {
1105	return 'pending';
1106	}
1107
1108	return 'incorrect';
1109	}
1110
1111	/**
1112	* Get single answer by answer_id
1113	*
1114	* @since 1.0.0
1115	*
1116	* @param array\|init $answer_id answer id.
1117	*
1118	* @return array\|null\|object
1119	*/
1120	public static function get_answer_by_id( $answer_id ) {
1121	global $wpdb;
1122
1123	! is_array( $answer_id ) ? $answer_id = array( $answer_id ) : 0;
1124
1125	$answer_id = array_map(
1126	function ( $id ) {
1127	return "'" . esc_sql( $id ) . "'";
1128	},
1129	$answer_id
1130	);
1131
1132	$in_ids_string = implode( ', ', $answer_id );
1133
1134	//phpcs:disable WordPress.DB.PreparedSQL.NotPrepared
1135	$answer = $wpdb->get_results(
1136	$wpdb->prepare(
1137	"SELECT answer.*,
1138	question.question_title,
1139	question.question_type
1140	FROM {$wpdb->prefix}tutor_quiz_question_answers answer
1141	LEFT JOIN {$wpdb->prefix}tutor_quiz_questions question
1142	ON answer.belongs_question_id = question.question_id
1143	WHERE answer.answer_id IN (" . $in_ids_string . ')
1144	AND 1 = %d;
1145	',
1146	1
1147	)
1148	);
1149	//phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
1150
1151	return $answer;
1152	}
1153
1154	/**
1155	* Get quiz attempt timing
1156	*
1157	* @since 1.0.0
1158	*
1159	* @param mixed $attempt_data attempt data.
1160	* @return array
1161	*/
1162	public static function get_quiz_attempt_timing( $attempt_data ) {
1163	$attempt_duration = '';
1164	$attempt_duration_taken = '';
1165	$attempt_info = @unserialize( $attempt_data->attempt_info );
1166	if ( is_array( $attempt_info ) ) {
1167	// Allowed duration.
1168	if ( isset( $attempt_info['time_limit'] ) ) {
1169	//phpcs:ignore WordPress.WP.I18n.NonSingularStringLiteralText
1170	$time_type = __( ucwords( tutor_utils()->array_get( 'time_limit.time_type', $attempt_info, 'minutes' ) ), 'tutor' );
1171	$time_value = tutor_utils()->array_get( 'time_limit.time_value', $attempt_info, 0 );
1172	$attempt_duration = $time_value . ' ' . $time_type;
1173	}
1174
1175	// Taken duration.
1176	$seconds = strtotime( $attempt_data->attempt_ended_at ) - strtotime( $attempt_data->attempt_started_at );
1177	$attempt_duration_taken = tutor_utils()->seconds_to_time( $seconds );
1178	}
1179
1180	return compact( 'attempt_duration', 'attempt_duration_taken' );
1181	}
1182
1183	/**
1184	* Check student is passed in a quiz or not.
1185	* Quiz retry mode: student required at least one quiz passed in attempts
1186	*
1187	* @since 2.1.0
1188	*
1189	* @param int $quiz_id quiz ID.
1190	* @param int $user_id user ID.
1191	*
1192	* @return boolean
1193	*/
1194	public static function is_quiz_passed( $quiz_id, $user_id = 0 ) {
1195	global $wpdb;
1196
1197	$user_id = tutor_utils()->get_user_id( $user_id );
1198	$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 ) );
1199	$required_percentage = tutor_utils()->get_quiz_option( $quiz_id, 'passing_grade', 0 );
1200
1201	foreach ( $attempts as $attempt ) {
1202	$earned_percentage = self::calculate_attempt_earned_percentage( $attempt );
1203	if ( $earned_percentage >= $required_percentage ) {
1204	return true;
1205	}
1206	}
1207
1208	return false;
1209	}
1210
1211	/**
1212	* Get all question type for a quiz
1213	*
1214	* @since 2.1.0
1215	*
1216	* @param integer $quiz_id quiz ID.
1217	*
1218	* @return array
1219	*/
1220	public static function get_quiz_question_types( int $quiz_id ) {
1221	global $wpdb;
1222	$types = $wpdb->get_col(
1223	$wpdb->prepare( "SELECT DISTINCT question_type FROM {$wpdb->prefix}tutor_quiz_questions WHERE quiz_id=%d", $quiz_id )
1224	);
1225
1226	return $types;
1227	}
1228
1229	/**
1230	* Check a quiz attempt need manual review or not
1231	*
1232	* @since 2.1.0
1233	*
1234	* @param int $quiz_id quiz ID.
1235	*
1236	* @return boolean
1237	*/
1238	public static function is_manual_review_required( $quiz_id ) {
1239	$required = false;
1240	$review_question_types = self::get_manual_review_types();
1241	$question_types = self::get_quiz_question_types( $quiz_id );
1242
1243	foreach ( $review_question_types as $type ) {
1244	if ( in_array( $type, $question_types, true ) ) {
1245	$required = true;
1246	break;
1247	}
1248	}
1249
1250	return $required;
1251	}
1252
1253	/**
1254	* Get last or first quiz attempt
1255	*
1256	* @since 2.1.0
1257	* @since 2.1.3 user_id param added.
1258	*
1259	* @param integer $quiz_id quiz id to get attempt of.
1260	* @param integer $user_id user ID who attempt the quiz.
1261	* @param string $order ASC or DESC, default is DESC
1262	* pass ASC to get first attempt.
1263	*
1264	* @return mixed object on success, null on failure
1265	*/
1266	public function get_first_or_last_attempt( int $quiz_id, int $user_id = 0, string $order = 'DESC' ) {
1267	$attempt = QueryHelper::get_row(
1268	$this->get_table(),
1269	array(
1270	'quiz_id' => $quiz_id,
1271	'user_id' => tutor_utils()->get_user_id( $user_id ),
1272	),
1273	'attempt_id',
1274	$order
1275	);
1276	return $attempt;
1277	}
1278
1279	/**
1280	* Get total number of quizzes by course id
1281	*
1282	* @since 2.2.0
1283	*
1284	* @param int\|array $course_id Course id or array of course ids.
1285	*
1286	* @return int
1287	*/
1288	public static function get_quiz_count_by_course( $course_id ) {
1289	global $wpdb;
1290
1291	$and_clause = is_array( $course_id ) && count( $course_id ) ? ' AND post_parent IN (' . QueryHelper::prepare_in_clause( $course_id ) . ')' : "AND post_parent = $course_id";
1292
1293	//phpcs:disable
1294	$count = $wpdb->get_var(
1295	$wpdb->prepare(
1296	"SELECT
1297	COUNT(ID)
1298	FROM {$wpdb->posts}
1299	WHERE post_parent IN
1300	(SELECT
1301	ID
1302	FROM {$wpdb->posts}
1303	WHERE post_type = %s
1304	{$and_clause}
1305	AND post_status = %s
1306	)
1307	AND post_type = %s
1308	AND post_status = %s",
1309	'topics',
1310	'publish',
1311	'tutor_quiz',
1312	'publish'
1313	)
1314	);
1315	//phpcs:enable
1316	return $count ? $count : 0;
1317	}
1318
1319	/**
1320	* Get final quiz result depending on all attempts.
1321	*
1322	* @since 2.4.0
1323	*
1324	* @param int $quiz_id quiz id.
1325	* @param int $user_id user id.
1326	*
1327	* @return string pass, fail, pending
1328	*/
1329	public static function get_quiz_result( $quiz_id, $user_id = 0 ) {
1330	global $wpdb;
1331
1332	$all_pending = true;
1333	$result = self::RESULT_PENDING;
1334
1335	$user_id = tutor_utils()->get_user_id( $user_id );
1336	$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 ) );
1337
1338	$total_pending_attempt = (int) $wpdb->get_var(
1339	$wpdb->prepare(
1340	"SELECT COUNT(attempt_id) total
1341	FROM {$wpdb->prefix}tutor_quiz_attempts
1342	WHERE result=%s AND quiz_id = %d AND user_id = %d
1343	",
1344	self::RESULT_PENDING,
1345	$quiz_id,
1346	$user_id
1347	)
1348	);
1349
1350	if ( count( $attempt_list ) !== $total_pending_attempt ) {
1351	$all_pending = false;
1352	}
1353
1354	if ( false === $all_pending ) {
1355	$required_percentage = tutor_utils()->get_quiz_option( $quiz_id, 'passing_grade', 0 );
1356	foreach ( $attempt_list as $attempt ) {
1357	$earned_percentage = self::calculate_attempt_earned_percentage( $attempt );
1358	if ( $earned_percentage >= $required_percentage ) {
1359	// If at least one attempt passed then quiz passed.
1360	$result = self::RESULT_PASS;
1361	break;
1362	} else {
1363	$result = self::RESULT_FAIL;
1364	}
1365	}
1366	}
1367
1368	return $result;
1369	}
1370
1371	/**
1372	* Get quiz attempt details
1373	*
1374	* @since 2.6.1
1375	*
1376	* @param integer $attempt_id attempt id.
1377	*
1378	* @return mixed
1379	*/
1380	public static function quiz_attempt_details( int $attempt_id ) {
1381	global $wpdb;
1382
1383	$table_quiz_attempt_answers = $wpdb->prefix . 'tutor_quiz_attempt_answers';
1384	$table_quiz_questions = $wpdb->prefix . 'tutor_quiz_questions';
1385	$table_quiz_attempts = $wpdb->prefix . 'tutor_quiz_attempts';
1386	$table_quiz_question_answers = $wpdb->prefix . 'tutor_quiz_question_answers';
1387
1388	$query = "SELECT
1389	ques.question_id,
1390	ques.question_title,
1391	ques.question_type,
1392	(
1393	SELECT
1394	GROUP_CONCAT(answer_title)
1395	FROM
1396	{$table_quiz_question_answers}
1397	WHERE
1398	belongs_question_id = ques.question_id
1399	AND is_correct = 1
1400	) AS correct_answers,
1401
1402	(
1403
1404	SELECT
1405
1406	CASE
1407	WHEN CHAR_LENGTH(att_ans.given_answer) = 1 AND att_ans.given_answer REGEXP '^[0-9]$' THEN
1408	-- If given_answer is a single digit integer
1409	(
1410	SELECT
1411	answer_title
1412	FROM
1413	{$table_quiz_question_answers}
1414	WHERE
1415	answer_id = CAST(att_ans.given_answer AS UNSIGNED)
1416	)
1417	WHEN CHAR_LENGTH(att_ans.given_answer) > 1 AND SUBSTRING(att_ans.given_answer, 1, 2) = 'a:' THEN
1418	-- If given_answer is serialized array
1419	(
1420	att_ans.given_answer
1421	)
1422	ELSE
1423	-- If given_answer is a serialized string
1424	att_ans.given_answer
1425	END
1426	) AS given_answer,
1427	att_ans.question_mark,
1428	att_ans.achieved_mark,
1429	att_ans.is_correct,
1430	(
1431	SELECT
1432	attempt_info
1433	FROM {$table_quiz_attempts}
1434	WHERE attempt_id = {$attempt_id}
1435	LIMIT 1
1436	) AS attempt_info
1437	FROM
1438	{$table_quiz_attempt_answers} AS att_ans
1439	JOIN {$table_quiz_questions} AS ques ON ques.question_id = att_ans.question_id
1440	JOIN {$table_quiz_question_answers} AS ans ON ans.answer_id = att_ans.attempt_answer_id
1441	WHERE
1442	quiz_attempt_id = %d
1443	LIMIT
1444	50
1445	";
1446
1447	//phpcs:ignore
1448	$result = $wpdb->get_results( $wpdb->prepare( $query, $attempt_id ) );
1449
1450	// If array and count result then loop with each result and prepare given answer.
1451	if ( is_array( $result ) && count( $result ) ) {
1452	foreach ( $result as $key => $value ) {
1453	// Check if given answer is a serialized string.
1454	if ( is_serialized( $value->given_answer ) ) {
1455	$given_answers = tutor_utils()->get_answer_by_id( maybe_unserialize( $value->given_answer ) );
1456	$result[ $key ]->given_answer = array_column( $given_answers, 'answer_title' );
1457	} elseif ( is_numeric( $value->given_answer ) ) {
1458	$given_answers = tutor_utils()->get_answer_by_id( maybe_unserialize( $value->given_answer ) );
1459	$result[ $key ]->given_answer = array_column( $given_answers, 'answer_title' );
1460	}
1461	}
1462	}
1463
1464	return $result;
1465	}
1466
1467	/**
1468	* Get a question record.
1469	*
1470	* @since 3.0.0
1471	*
1472	* @param int $question_id quiz question id.
1473	*
1474	* @return array\|object\|null\|void
1475	*/
1476	public static function get_question( $question_id ) {
1477	global $wpdb;
1478	return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}tutor_quiz_questions WHERE question_id = %d", $question_id ) );
1479	}
1480
1481	/**
1482	* Get all answer's of a quiz question.
1483	*
1484	* @since 3.0.0
1485	*
1486	* @param int $question_id question id.
1487	* @param string $question_type question type.
1488	*
1489	* @return array
1490	*/
1491	public static function get_question_answers( $question_id, $question_type = null ) {
1492	global $wpdb;
1493
1494	$query = "SELECT * FROM {$wpdb->prefix}tutor_quiz_question_answers WHERE belongs_question_id = %d";
1495
1496	if ( $question_type ) {
1497	$query .= ' AND belongs_question_type = %s ORDER BY answer_order ASC';
1498	//phpcs:ignore
1499	$answers = $wpdb->get_results( $wpdb->prepare( $query, $question_id, $question_type ) );
1500	} else {
1501	$query .= ' ORDER BY answer_order ASC';
1502	//phpcs:ignore
1503	$answers = $wpdb->get_results( $wpdb->prepare( $query, $question_id ) );
1504	}
1505
1506	foreach ( $answers as $answer ) {
1507	$answer->answer_title = stripslashes( $answer->answer_title );
1508	if ( $answer->image_id ) {
1509	$answer->image_url = wp_get_attachment_url( $answer->image_id );
1510	}
1511	}
1512
1513	/**
1514	* Filter question answers after loading (e.g. expand stored mask paths for REST/API).
1515	*
1516	* @param array $answers Answer rows.
1517	* @param int $question_id Question id.
1518	* @param string\|null $question_type Question type when known.
1519	*/
1520	return apply_filters( 'tutor_quiz_question_answers', $answers, $question_id, $question_type );
1521	}
1522
1523	/**
1524	* Get full image URL for a quiz answer.
1525	*
1526	* Uses attachment ID if present; falls back to stored image URL.
1527	*
1528	* @since 4.0.0
1529	*
1530	* @param object $answer Quiz answer object.
1531	* @param string $size Image size to retrieve. Default full.
1532	*
1533	* @return string
1534	*/
1535	public static function get_answer_image_url( $answer, $size = 'full' ) {
1536	if ( empty( $answer ) ) {
1537	return '';
1538	}
1539
1540	if ( ! empty( $answer->image_id ) ) {
1541	$url = wp_get_attachment_image_url( $answer->image_id, $size );
1542	if ( $url ) {
1543	return $url;
1544	}
1545	}
1546
1547	if ( ! empty( $answer->image_url ) ) {
1548	return $answer->image_url;
1549	}
1550
1551	return '';
1552	}
1553
1554	/**
1555	* Get next answer order SL no
1556	*
1557	* @since 3.0.0
1558	*
1559	* @param int $question_id question id.
1560	* @param int $question_type question type.
1561	*
1562	* @return int
1563	*/
1564	public static function get_next_answer_order( $question_id, $question_type ) {
1565	global $wpdb;
1566	$max_id = (int) $wpdb->get_var(
1567	$wpdb->prepare(
1568	"SELECT MAX(answer_order) FROM {$wpdb->prefix}tutor_quiz_question_answers WHERE belongs_question_id = %d AND belongs_question_type = %s",//phpcs:ignore
1569	$question_id,
1570	$question_type
1571	)
1572	);
1573
1574	return $max_id + 1;
1575	}
1576
1577	/**
1578	* Get quiz details by quiz id.
1579	*
1580	* @since 3.0.0
1581	*
1582	* @param int $quiz_id quiz id.
1583	*
1584	* @return object
1585	*/
1586	public static function get_quiz_details( $quiz_id ) {
1587	$quiz = get_post( $quiz_id );
1588	$quiz->quiz_option = get_post_meta( $quiz_id, Quiz::META_QUIZ_OPTION, true );
1589	$quiz->questions = tutor_utils()->get_questions_by_quiz( $quiz_id );
1590
1591	if ( ! is_array( $quiz->questions ) ) {
1592	$quiz->questions = array();
1593	}
1594
1595	foreach ( $quiz->questions as $question ) {
1596	$question->question_answers = self::get_question_answers( $question->question_id, $question->question_type );
1597	if ( isset( $question->question_settings ) ) {
1598	$question->question_settings = maybe_unserialize( $question->question_settings );
1599	}
1600	}
1601
1602	return $quiz;
1603	}
1604
1605	/**
1606	* Calculate attempt earned percentage
1607	*
1608	* @since 3.7.1
1609	*
1610	* @param int\|object $attempt attempt id or attempt object.
1611	*
1612	* @return float
1613	*/
1614	public static function calculate_attempt_earned_percentage( $attempt ) {
1615	if ( is_numeric( $attempt ) ) {
1616	$attempt = tutor_utils()->get_attempt( $attempt );
1617	}
1618
1619	if ( ! $attempt ) {
1620	return 0;
1621	}
1622
1623	$total_marks = (float) $attempt->total_marks;
1624	$earned_marks = (float) $attempt->earned_marks;
1625
1626	$earned_percentage = ( $earned_marks > 0 && $total_marks > 0 )
1627	? number_format( ( $earned_marks * 100 ) / $total_marks )
1628	: 0;
1629
1630	return $earned_percentage;
1631	}
1632
1633
1634	/**
1635	* Prepare attempt result from attempt data.
1636	*
1637	* @since 3.7.1
1638	*
1639	* @param int\|object $attempt attempt id or object.
1640	*
1641	* @return string pass, fail, pending
1642	*/
1643	public static function prepare_attempt_result( $attempt ) {
1644	if ( is_numeric( $attempt ) ) {
1645	$attempt = tutor_utils()->get_attempt( $attempt );
1646	}
1647
1648	if ( ! $attempt ) {
1649	return false;
1650	}
1651
1652	$attempt_info = maybe_unserialize( $attempt->attempt_info );
1653	$answers_map = self::get_quiz_answers_by_attempt_id( $attempt->attempt_id, true );
1654
1655	$earned_percentage = self::calculate_attempt_earned_percentage( $attempt );
1656	$passing_grade = (int) $attempt_info['passing_grade'] ?? 0;
1657
1658	$answers = $answers_map[ $attempt->attempt_id ] ?? array();
1659	$answers = is_array( $answers ) ? $answers : array();
1660
1661	$has_pending = (bool) count(
1662	array_filter( $answers, fn( $answer ) => null === $answer->is_correct )
1663	);
1664
1665	if ( $has_pending ) {
1666	$result = self::RESULT_PENDING;
1667	} else {
1668	$result = ( $earned_percentage >= $passing_grade ) ? self::RESULT_PASS : self::RESULT_FAIL;
1669	}
1670
1671	return $result;
1672	}
1673
1674	/**
1675	* Get a quiz attempt result
1676	*
1677	* @since 3.7.1
1678	*
1679	* @param int $attempt_id attempt id.
1680	*
1681	* @return string pass, fail, pending
1682	*/
1683	public static function get_attempt_result( $attempt_id ) {
1684	$attempt = tutor_utils()->get_attempt( $attempt_id );
1685	if ( ! $attempt ) {
1686	return false;
1687	}
1688
1689	/**
1690	* If result is processed then return it
1691	*
1692	* @since 3.7.1
1693	*/
1694	if ( isset( $attempt->result ) && ! empty( $attempt->result ) ) {
1695	return $attempt->result;
1696	}
1697
1698	/**
1699	* Backward compatibility for getting a attempt result
1700	*
1701	* @since 3.7.1
1702	*/
1703	$result = self::prepare_attempt_result( $attempt );
1704
1705	return $result;
1706	}
1707
1708	/**
1709	* Update attempt result.
1710	*
1711	* @param int $attempt_id attempt id.
1712	*
1713	* @return bool
1714	*/
1715	public static function update_attempt_result( $attempt_id ) {
1716	$attempt_result = self::prepare_attempt_result( $attempt_id );
1717	if ( $attempt_result ) {
1718	QueryHelper::update(
1719	QueryHelper::prepare_table_name( 'tutor_quiz_attempts' ),
1720	array( 'result' => $attempt_result ),
1721	array( 'attempt_id' => $attempt_id )
1722	);
1723
1724	return true;
1725	}
1726
1727	return false;
1728	}
1729	}
1730