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

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 22 hours ago CouponModel.php 22 hours ago CourseModel.php 22 hours ago EnrollmentModel.php 22 hours 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 22 hours ago QuizModel.php 22 hours ago UserModel.php 1 year ago WithdrawModel.php 22 hours ago

QuizModel.php

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