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