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