1use std::collections::HashMap;
2
3use chrono::NaiveDate;
4use futures::future::BoxFuture;
5use rand::prelude::SliceRandom;
6use url::Url;
7
8use crate::{
9 CourseOrExamId,
10 courses::Course,
11 exams::{self, ExamEnrollment},
12 exercise_service_info::ExerciseServiceInfoApi,
13 exercise_task_gradings::UserPointsUpdateStrategy,
14 exercise_tasks::CourseMaterialExerciseTask,
15 exercises::{self, Exercise, GradingProgress},
16 prelude::*,
17 teacher_grading_decisions::{self, TeacherGradingDecision},
18 user_exercise_states::{self, UserExerciseState},
19};
20
21#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
22#[cfg_attr(feature = "ts_rs", derive(TS))]
23pub struct AnswerRequiringAttention {
24 pub id: Uuid,
25 pub user_id: Uuid,
26 pub created_at: DateTime<Utc>,
27 pub updated_at: DateTime<Utc>,
28 pub deleted_at: Option<DateTime<Utc>>,
29 pub data_json: Option<serde_json::Value>,
30 pub grading_progress: GradingProgress,
31 pub score_given: Option<f32>,
32 pub submission_id: Uuid,
33 pub exercise_id: Uuid,
34}
35#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
36#[cfg_attr(feature = "ts_rs", derive(TS))]
37pub struct NewExerciseSlideSubmission {
38 pub exercise_slide_id: Uuid,
39 pub course_id: Option<Uuid>,
40 pub exam_id: Option<Uuid>,
41 pub user_id: Uuid,
42 pub exercise_id: Uuid,
43 pub user_points_update_strategy: UserPointsUpdateStrategy,
44}
45
46#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
47#[cfg_attr(feature = "ts_rs", derive(TS))]
48pub struct ExerciseSlideSubmission {
49 pub id: Uuid,
50 pub created_at: DateTime<Utc>,
51 pub updated_at: DateTime<Utc>,
52 pub deleted_at: Option<DateTime<Utc>>,
53 pub exercise_slide_id: Uuid,
54 pub course_id: Option<Uuid>,
55 pub exam_id: Option<Uuid>,
56 pub exercise_id: Uuid,
57 pub user_id: Uuid,
58 pub user_points_update_strategy: UserPointsUpdateStrategy,
59 pub flag_count: Option<i32>,
60}
61
62impl ExerciseSlideSubmission {
63 pub fn get_course_id(&self) -> ModelResult<Uuid> {
64 self.course_id.ok_or_else(|| {
65 ModelError::new(
66 ModelErrorType::Generic,
67 "Submission is not related to a course.".to_string(),
68 None,
69 )
70 })
71 }
72}
73
74#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
75#[cfg_attr(feature = "ts_rs", derive(TS))]
76pub struct ExerciseAnswersInCourseRequiringAttentionCount {
77 pub id: Uuid,
78 pub name: String,
79 pub page_id: Uuid,
80 pub chapter_id: Option<Uuid>,
81 pub order_number: i32,
82 pub count: Option<i32>,
83}
84
85#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
86#[cfg_attr(feature = "ts_rs", derive(TS))]
87pub struct ExerciseSlideSubmissionCount {
88 pub date: Option<NaiveDate>,
89 pub count: Option<i32>,
90}
91
92#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
93#[cfg_attr(feature = "ts_rs", derive(TS))]
94pub struct ExerciseSlideSubmissionCountByExercise {
95 pub exercise_id: Uuid,
96 pub count: Option<i32>,
97 pub exercise_name: String,
98}
99
100#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
101#[cfg_attr(feature = "ts_rs", derive(TS))]
102pub struct ExerciseSlideSubmissionCountByWeekAndHour {
103 pub isodow: Option<i32>,
104 pub hour: Option<i32>,
105 pub count: Option<i32>,
106}
107
108#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
109#[cfg_attr(feature = "ts_rs", derive(TS))]
110pub struct ExerciseSlideSubmissionInfo {
111 pub tasks: Vec<CourseMaterialExerciseTask>,
112 pub exercise: Exercise,
113 pub exercise_slide_submission: ExerciseSlideSubmission,
114 pub user_exercise_state: Option<UserExerciseState>,
115}
116
117#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
118#[cfg_attr(feature = "ts_rs", derive(TS))]
119pub struct ExerciseSlideSubmissionAndUserExerciseState {
120 pub exercise: Exercise,
121 pub exercise_slide_submission: ExerciseSlideSubmission,
122 pub user_exercise_state: UserExerciseState,
123 pub teacher_grading_decision: Option<TeacherGradingDecision>,
124 pub user_exam_enrollment: ExamEnrollment,
125}
126
127#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
128#[cfg_attr(feature = "ts_rs", derive(TS))]
129pub struct ExerciseSlideSubmissionAndUserExerciseStateList {
130 pub data: Vec<ExerciseSlideSubmissionAndUserExerciseState>,
131 pub total_pages: u32,
132}
133
134pub async fn insert_exercise_slide_submission(
135 conn: &mut PgConnection,
136 exercise_slide_submission: NewExerciseSlideSubmission,
137) -> ModelResult<ExerciseSlideSubmission> {
138 let res = sqlx::query_as!(
139 ExerciseSlideSubmission,
140 r#"
141INSERT INTO exercise_slide_submissions (
142 exercise_slide_id,
143 course_id,
144 exam_id,
145 exercise_id,
146 user_id,
147 user_points_update_strategy
148 )
149VALUES ($1, $2, $3, $4, $5, $6)
150RETURNING id,
151 created_at,
152 updated_at,
153 deleted_at,
154 exercise_slide_id,
155 course_id,
156 exam_id,
157 exercise_id,
158 user_id,
159 user_points_update_strategy AS "user_points_update_strategy: _",
160 flag_count
161 "#,
162 exercise_slide_submission.exercise_slide_id,
163 exercise_slide_submission.course_id,
164 exercise_slide_submission.exam_id,
165 exercise_slide_submission.exercise_id,
166 exercise_slide_submission.user_id,
167 exercise_slide_submission.user_points_update_strategy as UserPointsUpdateStrategy,
168 )
169 .fetch_one(conn)
170 .await?;
171 Ok(res)
172}
173
174pub async fn insert_exercise_slide_submission_with_id(
175 conn: &mut PgConnection,
176 id: Uuid,
177 exercise_slide_submission: &NewExerciseSlideSubmission,
178) -> ModelResult<ExerciseSlideSubmission> {
179 let res = sqlx::query_as!(
180 ExerciseSlideSubmission,
181 r#"
182INSERT INTO exercise_slide_submissions (
183 id,
184 exercise_slide_id,
185 course_id,
186 exam_id,
187 exercise_id,
188 user_id,
189 user_points_update_strategy
190 )
191VALUES ($1, $2, $3, $4, $5, $6, $7)
192RETURNING id,
193 created_at,
194 updated_at,
195 deleted_at,
196 exercise_slide_id,
197 course_id,
198 exam_id,
199 exercise_id,
200 user_id,
201 user_points_update_strategy AS "user_points_update_strategy: _",
202 flag_count
203 "#,
204 id,
205 exercise_slide_submission.exercise_slide_id,
206 exercise_slide_submission.course_id,
207 exercise_slide_submission.exam_id,
208 exercise_slide_submission.exercise_id,
209 exercise_slide_submission.user_id,
210 exercise_slide_submission.user_points_update_strategy as UserPointsUpdateStrategy,
211 )
212 .fetch_one(conn)
213 .await?;
214 Ok(res)
215}
216
217pub async fn get_by_id(conn: &mut PgConnection, id: Uuid) -> ModelResult<ExerciseSlideSubmission> {
218 let exercise_slide_submission = sqlx::query_as!(
219 ExerciseSlideSubmission,
220 r#"
221SELECT id,
222created_at,
223updated_at,
224deleted_at,
225exercise_slide_id,
226course_id,
227exam_id,
228exercise_id,
229user_id,
230user_points_update_strategy AS "user_points_update_strategy: _",
231flag_count
232FROM exercise_slide_submissions
233WHERE id = $1
234 AND deleted_at IS NULL;
235 "#,
236 id
237 )
238 .fetch_one(conn)
239 .await?;
240 Ok(exercise_slide_submission)
241}
242
243pub async fn try_to_get_random_filtered_by_user_and_submissions(
247 conn: &mut PgConnection,
248 exercise_id: Uuid,
249 excluded_user_id: Uuid,
250 excluded_ids: &[Uuid],
251) -> ModelResult<Option<ExerciseSlideSubmission>> {
252 let mut res = sqlx::query_as!(
253 ExerciseSlideSubmission,
254 r#"
255SELECT DISTINCT ON (user_id)
256 ess.id,
257 ess.created_at,
258 ess.updated_at,
259 ess.deleted_at,
260 ess.exercise_slide_id,
261 ess.course_id,
262 ess.exam_id,
263 ess.exercise_id,
264 ess.user_id,
265 ess.user_points_update_strategy AS "user_points_update_strategy: _",
266 ess.flag_count
267FROM exercise_slide_submissions AS ess
268JOIN courses AS c
269 ON ess.course_id = c.id
270WHERE ess.exercise_id = $1
271 AND ess.id <> ALL($2)
272 AND ess.user_id <> $3
273 AND ess.deleted_at IS NULL
274 AND ess.flag_count < c.flagged_answers_threshold
275ORDER BY ess.user_id, ess.created_at DESC
276 "#,
277 exercise_id,
278 excluded_ids,
279 excluded_user_id,
280 )
281 .fetch_all(conn)
282 .await?;
283 let mut rng = rand::rng();
285 res.shuffle(&mut rng);
286 Ok(res.into_iter().next())
287}
288
289pub async fn get_by_exercise_id(
290 conn: &mut PgConnection,
291 exercise_id: Uuid,
292 pagination: Pagination,
293) -> ModelResult<Vec<ExerciseSlideSubmission>> {
294 let submissions = sqlx::query_as!(
295 ExerciseSlideSubmission,
296 r#"
297SELECT id,
298 created_at,
299 updated_at,
300 deleted_at,
301 exercise_slide_id,
302 course_id,
303 exam_id,
304 exercise_id,
305 user_id,
306 user_points_update_strategy AS "user_points_update_strategy: _",
307 flag_count
308FROM exercise_slide_submissions
309WHERE exercise_id = $1
310 AND deleted_at IS NULL
311LIMIT $2 OFFSET $3;
312 "#,
313 exercise_id,
314 pagination.limit(),
315 pagination.offset(),
316 )
317 .fetch_all(conn)
318 .await?;
319 Ok(submissions)
320}
321
322pub async fn get_users_all_submissions_for_course_or_exam(
323 conn: &mut PgConnection,
324 user_id: Uuid,
325 course_or_exam_id: CourseOrExamId,
326) -> ModelResult<Vec<ExerciseSlideSubmission>> {
327 let (course_id, exam_id) = course_or_exam_id.to_course_and_exam_ids();
328 let submissions = sqlx::query_as!(
329 ExerciseSlideSubmission,
330 r#"
331SELECT id,
332 created_at,
333 updated_at,
334 deleted_at,
335 exercise_slide_id,
336 course_id,
337 exam_id,
338 exercise_id,
339 user_id,
340 user_points_update_strategy AS "user_points_update_strategy: _",
341 flag_count
342FROM exercise_slide_submissions
343WHERE user_id = $1
344 AND (course_id = $2 OR exam_id = $3)
345 AND deleted_at IS NULL
346 "#,
347 user_id,
348 course_id,
349 exam_id,
350 )
351 .fetch_all(conn)
352 .await?;
353 Ok(submissions)
354}
355
356pub async fn get_users_submissions_for_exercise(
357 conn: &mut PgConnection,
358 user_id: Uuid,
359 exercise_id: Uuid,
360) -> ModelResult<Vec<ExerciseSlideSubmission>> {
361 let submissions = sqlx::query_as!(
362 ExerciseSlideSubmission,
363 r#"
364SELECT id,
365 created_at,
366 updated_at,
367 deleted_at,
368 exercise_slide_id,
369 course_id,
370 exam_id,
371 exercise_id,
372 user_id,
373 user_points_update_strategy AS "user_points_update_strategy: _",
374 flag_count
375FROM exercise_slide_submissions
376WHERE user_id = $1
377 AND exercise_id = $2
378 AND deleted_at IS NULL
379ORDER BY created_at DESC
380 "#,
381 user_id,
382 exercise_id,
383 )
384 .fetch_all(conn)
385 .await?;
386 Ok(submissions)
387}
388
389pub async fn get_users_latest_exercise_slide_submission(
390 conn: &mut PgConnection,
391 exercise_slide_id: Uuid,
392 user_id: Uuid,
393) -> ModelResult<ExerciseSlideSubmission> {
394 let res = sqlx::query_as!(
395 ExerciseSlideSubmission,
396 r#"
397SELECT id,
398 created_at,
399 updated_at,
400 deleted_at,
401 exercise_slide_id,
402 course_id,
403 exam_id,
404 exercise_id,
405 user_id,
406 user_points_update_strategy AS "user_points_update_strategy: _",
407 flag_count
408FROM exercise_slide_submissions
409WHERE exercise_slide_id = $1
410 AND user_id = $2
411 AND deleted_at IS NULL
412ORDER BY created_at DESC
413LIMIT 1
414 "#,
415 exercise_slide_id,
416 user_id
417 )
418 .fetch_one(conn)
419 .await?;
420 Ok(res)
421}
422
423pub async fn try_to_get_users_latest_exercise_slide_submission(
424 conn: &mut PgConnection,
425 exercise_slide_id: Uuid,
426 user_id: Uuid,
427) -> ModelResult<Option<ExerciseSlideSubmission>> {
428 get_users_latest_exercise_slide_submission(conn, exercise_slide_id, user_id)
429 .await
430 .optional()
431}
432
433pub async fn get_course_and_exam_id(
434 conn: &mut PgConnection,
435 id: Uuid,
436) -> ModelResult<CourseOrExamId> {
437 let res = sqlx::query!(
438 "
439SELECT course_id,
440 exam_id
441FROM exercise_slide_submissions
442WHERE id = $1
443 AND deleted_at IS NULL
444 ",
445 id
446 )
447 .fetch_one(conn)
448 .await?;
449 CourseOrExamId::from_course_and_exam_ids(res.course_id, res.exam_id)
450}
451
452pub async fn exercise_slide_submission_count(
453 conn: &mut PgConnection,
454 exercise_id: Uuid,
455) -> ModelResult<u32> {
456 let count = sqlx::query!(
457 "
458SELECT COUNT(*) as count
459FROM exercise_slide_submissions
460WHERE exercise_id = $1
461AND deleted_at IS NULL
462",
463 exercise_id,
464 )
465 .fetch_one(conn)
466 .await?;
467 Ok(count.count.unwrap_or(0).try_into()?)
468}
469
470pub async fn exercise_slide_submissions(
471 conn: &mut PgConnection,
472 exercise_id: Uuid,
473 pagination: Pagination,
474) -> ModelResult<Vec<ExerciseSlideSubmission>> {
475 let submissions = sqlx::query_as!(
476 ExerciseSlideSubmission,
477 r#"
478SELECT id,
479 created_at,
480 updated_at,
481 deleted_at,
482 exercise_slide_id,
483 course_id,
484 exam_id,
485 exercise_id,
486 user_id,
487 user_points_update_strategy AS "user_points_update_strategy: _",
488 flag_count
489FROM exercise_slide_submissions
490WHERE exercise_id = $1
491 AND deleted_at IS NULL
492ORDER BY created_at DESC
493LIMIT $2 OFFSET $3
494 "#,
495 exercise_id,
496 pagination.limit(),
497 pagination.offset(),
498 )
499 .fetch_all(conn)
500 .await?;
501 Ok(submissions)
502}
503
504pub async fn exercise_slide_submission_count_with_exam_id(
505 conn: &mut PgConnection,
506 exam_id: Uuid,
507) -> ModelResult<u32> {
508 let count = sqlx::query!(
509 "
510SELECT COUNT(*) as count
511FROM exercise_slide_submissions
512WHERE exam_id = $1
513AND deleted_at IS NULL
514",
515 exam_id,
516 )
517 .fetch_one(conn)
518 .await?;
519 Ok(count.count.unwrap_or(0).try_into()?)
520}
521
522pub async fn exercise_slide_submission_count_with_exercise_id(
523 conn: &mut PgConnection,
524 exercise_id: Uuid,
525) -> ModelResult<u32> {
526 let count = sqlx::query!(
527 "
528SELECT COUNT(*) as count
529FROM exercise_slide_submissions
530WHERE exercise_id = $1
531AND deleted_at IS NULL
532",
533 exercise_id,
534 )
535 .fetch_one(conn)
536 .await?;
537 Ok(count.count.unwrap_or(0).try_into()?)
538}
539
540pub async fn get_latest_exercise_slide_submissions_and_user_exercise_state_list_with_exercise_id(
541 conn: &mut PgConnection,
542 exercise_id: Uuid,
543 pagination: Pagination,
544) -> ModelResult<Vec<ExerciseSlideSubmissionAndUserExerciseState>> {
545 let submissions = sqlx::query_as!(
546 ExerciseSlideSubmission,
547 r#"
548 SELECT DISTINCT ON (user_id)
549 id,
550 created_at,
551 updated_at,
552 deleted_at,
553 exercise_slide_id,
554 course_id,
555 exam_id,
556 exercise_id,
557 user_id,
558 user_points_update_strategy AS "user_points_update_strategy: _",
559 flag_count
560FROM exercise_slide_submissions
561WHERE exercise_id = $1
562 AND deleted_at IS NULL
563ORDER BY user_id, created_at DESC
564LIMIT $2 OFFSET $3
565 "#,
566 exercise_id,
567 pagination.limit(),
568 pagination.offset(),
569 )
570 .fetch_all(&mut *conn)
571 .await?;
572
573 let user_ids = submissions
574 .iter()
575 .map(|sub| sub.user_id)
576 .collect::<Vec<_>>();
577
578 let exercise = exercises::get_by_id(conn, exercise_id).await?;
579 let exam_id = exercise.exam_id;
580 let course_id = exercise.course_id;
581
582 let user_exercise_states_list =
583 user_exercise_states::get_or_create_user_exercise_state_for_users(
584 conn,
585 &user_ids,
586 exercise_id,
587 course_id,
588 exam_id,
589 )
590 .await?;
591
592 let mut user_exercise_state_id_list: Vec<Uuid> = Vec::new();
593
594 for (_key, value) in user_exercise_states_list.clone().into_iter() {
595 user_exercise_state_id_list.push(value.id);
596 }
597
598 let exercise = exercises::get_by_id(conn, exercise_id).await?;
599 let exam_id = exercise
600 .exam_id
601 .ok_or_else(|| ModelError::new(ModelErrorType::Generic, "No exam id found", None))?;
602
603 let teacher_grading_decisions_list = teacher_grading_decisions::try_to_get_latest_grading_decision_by_user_exercise_state_id_for_users(conn, &user_exercise_state_id_list).await?;
604
605 let user_exam_enrollments_list =
606 exams::get_exam_enrollments_for_users(conn, exam_id, &user_ids).await?;
607
608 let mut list: Vec<ExerciseSlideSubmissionAndUserExerciseState> = Vec::new();
609 for sub in submissions {
610 let user_exercise_state = user_exercise_states_list
611 .get(&sub.user_id)
612 .ok_or_else(|| ModelError::new(ModelErrorType::Generic, "No user found", None))?;
613
614 let teacher_grading_decision = teacher_grading_decisions_list.get(&user_exercise_state.id);
615 let user_exam_enrollment =
616 user_exam_enrollments_list
617 .get(&sub.user_id)
618 .ok_or_else(|| {
619 ModelError::new(
620 ModelErrorType::Generic,
621 "No users exam_enrollment found",
622 None,
623 )
624 })?;
625
626 if user_exam_enrollment.ended_at.is_some() {
628 let data = ExerciseSlideSubmissionAndUserExerciseState {
629 exercise: exercise.clone(),
630 exercise_slide_submission: sub,
631 user_exercise_state: user_exercise_state.clone(),
632 teacher_grading_decision: teacher_grading_decision.cloned(),
633 user_exam_enrollment: user_exam_enrollment.clone(),
634 };
635 list.push(data);
636 }
637 }
638
639 Ok(list)
640}
641
642pub async fn get_course_daily_slide_submission_counts(
643 conn: &mut PgConnection,
644 course: &Course,
645) -> ModelResult<Vec<ExerciseSlideSubmissionCount>> {
646 let res = sqlx::query_as!(
647 ExerciseSlideSubmissionCount,
648 r#"
649SELECT DATE(created_at) date, count(*)::integer
650FROM exercise_slide_submissions
651WHERE course_id = $1
652AND deleted_at IS NULL
653GROUP BY date
654ORDER BY date;
655 "#,
656 course.id
657 )
658 .fetch_all(conn)
659 .await?;
660 Ok(res)
661}
662
663pub async fn get_course_daily_user_counts_with_submissions(
664 conn: &mut PgConnection,
665 course: &Course,
666) -> ModelResult<Vec<ExerciseSlideSubmissionCount>> {
667 let res = sqlx::query_as!(
668 ExerciseSlideSubmissionCount,
669 r#"
670SELECT DATE(created_at) date, count(DISTINCT user_id)::integer
671FROM exercise_slide_submissions
672WHERE course_id = $1
673AND deleted_at IS NULL
674GROUP BY date
675ORDER BY date;
676 "#,
677 course.id
678 )
679 .fetch_all(conn)
680 .await?;
681 Ok(res)
682}
683
684pub async fn answer_requiring_attention_count(
685 conn: &mut PgConnection,
686 exercise_id: Uuid,
687) -> ModelResult<u32> {
688 let count = sqlx::query!(
689 r#"
690 SELECT
691 COUNT(*) as count
692 FROM user_exercise_states AS us_state
693 JOIN exercise_task_submissions AS t_submission
694 ON us_state.selected_exercise_slide_id =
695 t_submission.exercise_slide_id
696 JOIN exercise_slide_submissions AS s_submission
697 ON t_submission.exercise_slide_submission_id =
698 s_submission.id
699 WHERE us_state.selected_exercise_slide_id =
700 t_submission.exercise_slide_id
701 AND us_state.user_id = s_submission.user_id
702 AND us_state.exercise_id = $1
703 AND us_state.reviewing_stage = 'waiting_for_manual_grading'
704 AND us_state.deleted_at IS NULL
705 AND s_submission.deleted_at IS NULL
706 AND t_submission.deleted_at IS NULL"#,
707 exercise_id,
708 )
709 .fetch_one(conn)
710 .await?;
711 Ok(count.count.unwrap_or(0).try_into()?)
712}
713
714pub async fn get_count_of_answers_requiring_attention_in_exercise_by_course_id(
715 conn: &mut PgConnection,
716 course_id: Uuid,
717) -> ModelResult<Vec<ExerciseAnswersInCourseRequiringAttentionCount>> {
718 let count_list = sqlx::query_as!(
719 ExerciseAnswersInCourseRequiringAttentionCount,
720 r#"
721SELECT exercises.id,
722 (
723 SELECT COUNT(us_state.id)::integer AS COUNT
724 FROM exercises AS exercises2
725 LEFT JOIN user_exercise_states AS us_state ON us_state.exercise_id = exercises2.id
726 LEFT JOIN exercise_slide_submissions AS s_submission ON us_state.selected_exercise_slide_id = s_submission.exercise_slide_id
727 LEFT JOIN exercise_task_submissions AS t_submission ON s_submission.id = t_submission.exercise_slide_submission_id
728 WHERE us_state.selected_exercise_slide_id = t_submission.exercise_slide_id
729 AND us_state.user_id = s_submission.user_id
730 AND us_state.reviewing_stage = 'waiting_for_manual_grading'
731 AND us_state.deleted_at IS NULL
732 AND exercises2.course_id = $1
733 AND exercises.id = exercises2.id
734 GROUP BY exercises2.id
735 ),
736 exercises.order_number,
737 exercises.name,
738 exercises.page_id,
739 exercises.chapter_id
740FROM exercises
741WHERE exercises.course_id = $1
742 AND exercises.deleted_at IS NULL
743GROUP BY exercises.id;
744"#,
745 course_id,
746 )
747 .fetch_all(conn)
748 .await?;
749 Ok(count_list)
750}
751
752pub async fn exercise_slide_submissions_for_answers_requiring_attention(
753 conn: &mut PgConnection,
754 exercise_id: Uuid,
755 pagination: Pagination,
756) -> ModelResult<Vec<ExerciseSlideSubmission>> {
757 let submissions = sqlx::query_as!(
758 ExerciseSlideSubmission,
759 r#"
760SELECT id,
761 created_at,
762 updated_at,
763 deleted_at,
764 exercise_slide_id,
765 course_id,
766 exam_id,
767 exercise_id,
768 user_id,
769 user_points_update_strategy AS "user_points_update_strategy: _",
770 flag_count
771FROM exercise_slide_submissions
772WHERE exercise_id = $1
773 AND deleted_at IS NULL
774LIMIT $2 OFFSET $3
775 "#,
776 exercise_id,
777 pagination.limit(),
778 pagination.offset(),
779 )
780 .fetch_all(conn)
781 .await?;
782 Ok(submissions)
783}
784
785pub async fn get_all_answers_requiring_attention(
786 conn: &mut PgConnection,
787 exercise_id: Uuid,
788 pagination: Pagination,
789) -> ModelResult<Vec<AnswerRequiringAttention>> {
790 let submissions = sqlx::query_as!(
791 AnswerRequiringAttention,
792 r#"
793 SELECT
794 us_state.id,
795 us_state.user_id,
796 us_state.exercise_id,
797 us_state.score_given,
798 us_state.grading_progress as "grading_progress: _",
799 t_submission.data_json,
800 s_submission.created_at,
801 s_submission.updated_at,
802 s_submission.deleted_at,
803 s_submission.id AS submission_id
804 FROM user_exercise_states AS us_state
805 JOIN exercise_task_submissions AS t_submission
806 ON us_state.selected_exercise_slide_id =
807 t_submission.exercise_slide_id
808 JOIN exercise_slide_submissions AS s_submission
809 ON t_submission.exercise_slide_submission_id =
810 s_submission.id
811 WHERE us_state.selected_exercise_slide_id =
812 t_submission.exercise_slide_id
813 AND us_state.user_id = s_submission.user_id
814 AND us_state.exercise_id = $1
815 AND us_state.reviewing_stage = 'waiting_for_manual_grading'
816 AND us_state.deleted_at IS NULL
817 AND us_state.deleted_at IS NULL
818 AND s_submission.deleted_at IS NULL
819 AND t_submission.deleted_at IS NULL
820 ORDER BY t_submission.updated_at
821 LIMIT $2 OFFSET $3;"#,
822 exercise_id,
823 pagination.limit(),
824 pagination.offset(),
825 )
826 .fetch_all(conn)
827 .await?;
828 Ok(submissions)
829}
830
831pub async fn get_course_exercise_slide_submission_counts_by_weekday_and_hour(
832 conn: &mut PgConnection,
833 course: &Course,
834) -> ModelResult<Vec<ExerciseSlideSubmissionCountByWeekAndHour>> {
835 let res = sqlx::query_as!(
836 ExerciseSlideSubmissionCountByWeekAndHour,
837 r#"
838SELECT date_part('isodow', created_at)::integer isodow,
839 date_part('hour', created_at)::integer "hour",
840 count(*)::integer
841FROM exercise_slide_submissions
842WHERE course_id = $1
843AND deleted_at IS NULL
844GROUP BY isodow,
845 "hour"
846ORDER BY isodow,
847 hour;
848 "#,
849 course.id
850 )
851 .fetch_all(conn)
852 .await?;
853 Ok(res)
854}
855
856pub async fn get_course_exercise_slide_submission_counts_by_exercise(
857 conn: &mut PgConnection,
858 course: &Course,
859) -> ModelResult<Vec<ExerciseSlideSubmissionCountByExercise>> {
860 let res = sqlx::query_as!(
861 ExerciseSlideSubmissionCountByExercise,
862 r#"
863SELECT counts.*, exercises.name exercise_name
864 FROM (
865 SELECT exercise_id, count(*)::integer count
866 FROM exercise_slide_submissions
867 WHERE course_id = $1
868 AND deleted_at IS NULL
869 GROUP BY exercise_id
870 ) counts
871 JOIN exercises ON (counts.exercise_id = exercises.id);
872 "#,
873 course.id
874 )
875 .fetch_all(conn)
876 .await?;
877 Ok(res)
878}
879
880pub async fn get_exercise_slide_submission_counts_for_exercise_user(
881 conn: &mut PgConnection,
882 exercise_id: Uuid,
883 course_id_or_exam_id: CourseOrExamId,
884 user_id: Uuid,
885) -> ModelResult<HashMap<Uuid, i64>> {
886 let ci_id_or_e_id = match course_id_or_exam_id {
887 CourseOrExamId::Course(id) => id,
888 CourseOrExamId::Exam(id) => id,
889 };
890 let res = sqlx::query!(
891 r#"
892SELECT exercise_slide_id,
893 COUNT(*) as count
894FROM exercise_slide_submissions
895WHERE exercise_id = $1
896 AND (course_id = $2 OR exam_id = $2)
897 AND user_id = $3
898 AND deleted_at IS NULL
899GROUP BY exercise_slide_id;
900 "#,
901 exercise_id,
902 ci_id_or_e_id,
903 user_id
904 )
905 .fetch_all(conn)
906 .await?
907 .iter()
908 .map(|row| (row.exercise_slide_id, row.count.unwrap_or(0)))
909 .collect::<HashMap<Uuid, i64>>();
910
911 Ok(res)
912}
913
914pub async fn get_exercise_slide_submission_info(
915 conn: &mut PgConnection,
916 exercise_slide_submission_id: Uuid,
917 user_id: Uuid,
918 fetch_service_info: impl Fn(Url) -> BoxFuture<'static, ModelResult<ExerciseServiceInfoApi>>,
919 include_deleted_tasks: bool,
920) -> ModelResult<ExerciseSlideSubmissionInfo> {
921 let exercise_slide_submission = get_by_id(&mut *conn, exercise_slide_submission_id).await?;
922 let exercise =
923 crate::exercises::get_by_id(&mut *conn, exercise_slide_submission.exercise_id).await?;
924 let tasks = crate::exercise_task_submissions::get_exercise_task_submission_info_by_exercise_slide_submission_id(&mut *conn, exercise_slide_submission_id, user_id, fetch_service_info, include_deleted_tasks).await?;
925 let user_exercise_state = crate::user_exercise_states::get_user_exercise_state_if_exists(
926 &mut *conn,
927 user_id,
928 exercise_slide_submission.exercise_id,
929 CourseOrExamId::from_course_and_exam_ids(
930 exercise_slide_submission.course_id,
931 exercise_slide_submission.exam_id,
932 )?,
933 )
934 .await?;
935
936 Ok(ExerciseSlideSubmissionInfo {
937 exercise,
938 tasks,
939 exercise_slide_submission,
940 user_exercise_state,
941 })
942}
943
944pub async fn delete_exercise_submissions_with_exam_id_and_user_id(
945 conn: &mut PgConnection,
946 exam_id: Uuid,
947 user_id: Uuid,
948) -> ModelResult<()> {
949 sqlx::query!(
950 "
951UPDATE exercise_slide_submissions
952SET deleted_at = now()
953WHERE exam_id = $1 AND user_id = $2
954 ",
955 exam_id,
956 user_id,
957 )
958 .execute(&mut *conn)
959 .await?;
960 Ok(())
961}