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 id,
152 created_at,
153 updated_at,
154 deleted_at,
155 exercise_slide_id,
156 course_id,
157 exam_id,
158 exercise_id,
159 user_id,
160 user_points_update_strategy AS "user_points_update_strategy: _",
161 flag_count
162 "#,
163 exercise_slide_submission.exercise_slide_id,
164 exercise_slide_submission.course_id,
165 exercise_slide_submission.exam_id,
166 exercise_slide_submission.exercise_id,
167 exercise_slide_submission.user_id,
168 exercise_slide_submission.user_points_update_strategy as UserPointsUpdateStrategy,
169 )
170 .fetch_one(conn)
171 .await?;
172 Ok(res)
173}
174
175pub async fn insert_exercise_slide_submission_with_id(
176 conn: &mut PgConnection,
177 id: Uuid,
178 exercise_slide_submission: &NewExerciseSlideSubmission,
179) -> ModelResult<ExerciseSlideSubmission> {
180 let res = sqlx::query_as!(
181 ExerciseSlideSubmission,
182 r#"
183INSERT INTO exercise_slide_submissions (
184 id,
185 exercise_slide_id,
186 course_id,
187 exam_id,
188 exercise_id,
189 user_id,
190 user_points_update_strategy
191 )
192VALUES ($1, $2, $3, $4, $5, $6, $7)
193RETURNING id,
194 created_at,
195 updated_at,
196 deleted_at,
197 exercise_slide_id,
198 course_id,
199 exam_id,
200 exercise_id,
201 user_id,
202 user_points_update_strategy AS "user_points_update_strategy: _",
203 flag_count
204 "#,
205 id,
206 exercise_slide_submission.exercise_slide_id,
207 exercise_slide_submission.course_id,
208 exercise_slide_submission.exam_id,
209 exercise_slide_submission.exercise_id,
210 exercise_slide_submission.user_id,
211 exercise_slide_submission.user_points_update_strategy as UserPointsUpdateStrategy,
212 )
213 .fetch_one(conn)
214 .await?;
215 Ok(res)
216}
217
218pub async fn get_by_id(conn: &mut PgConnection, id: Uuid) -> ModelResult<ExerciseSlideSubmission> {
219 let exercise_slide_submission = sqlx::query_as!(
220 ExerciseSlideSubmission,
221 r#"
222SELECT id,
223created_at,
224updated_at,
225deleted_at,
226exercise_slide_id,
227course_id,
228exam_id,
229exercise_id,
230user_id,
231user_points_update_strategy AS "user_points_update_strategy: _",
232flag_count
233FROM exercise_slide_submissions
234WHERE id = $1
235 AND deleted_at IS NULL;
236 "#,
237 id
238 )
239 .fetch_one(conn)
240 .await?;
241 Ok(exercise_slide_submission)
242}
243
244pub async fn get_by_course_id_and_user_ids_and_exercise_ids(
245 conn: &mut PgConnection,
246 course_id: Uuid,
247 user_ids: &[Uuid],
248 exercise_ids: &[Uuid],
249) -> ModelResult<Vec<ExerciseSlideSubmission>> {
250 let submissions = sqlx::query_as!(
251 ExerciseSlideSubmission,
252 r#"
253SELECT ess.id,
254 ess.created_at,
255 ess.updated_at,
256 ess.deleted_at,
257 ess.exercise_slide_id,
258 ess.course_id,
259 ess.exam_id,
260 ess.exercise_id,
261 ess.user_id,
262 ess.user_points_update_strategy AS "user_points_update_strategy: _",
263 ess.flag_count
264FROM exercise_slide_submissions ess
265 JOIN exercises e ON e.id = ess.exercise_id
266WHERE ess.course_id = $1
267 AND e.course_id = $1
268 AND ess.user_id = ANY($2)
269 AND ess.exercise_id = ANY($3)
270 AND ess.deleted_at IS NULL
271 AND e.deleted_at IS NULL
272 "#,
273 course_id,
274 user_ids,
275 exercise_ids
276 )
277 .fetch_all(conn)
278 .await?;
279 Ok(submissions)
280}
281
282pub async fn get_user_ids_by_submission_ids(
284 conn: &mut PgConnection,
285 submission_ids: &[Uuid],
286) -> ModelResult<HashMap<Uuid, Uuid>> {
287 if submission_ids.is_empty() {
288 return Ok(HashMap::new());
289 }
290 let rows = sqlx::query!(
291 r#"
292SELECT id,
293 user_id
294FROM exercise_slide_submissions
295WHERE id = ANY($1)
296 AND deleted_at IS NULL
297 "#,
298 submission_ids
299 )
300 .fetch_all(conn)
301 .await?;
302 Ok(rows.into_iter().map(|r| (r.id, r.user_id)).collect())
303}
304
305pub async fn try_to_get_random_filtered_by_user_and_submissions(
309 conn: &mut PgConnection,
310 exercise_id: Uuid,
311 excluded_user_id: Uuid,
312 excluded_ids: &[Uuid],
313) -> ModelResult<Option<ExerciseSlideSubmission>> {
314 let mut res = sqlx::query_as!(
315 ExerciseSlideSubmission,
316 r#"
317SELECT DISTINCT ON (user_id)
318 ess.id,
319 ess.created_at,
320 ess.updated_at,
321 ess.deleted_at,
322 ess.exercise_slide_id,
323 ess.course_id,
324 ess.exam_id,
325 ess.exercise_id,
326 ess.user_id,
327 ess.user_points_update_strategy AS "user_points_update_strategy: _",
328 ess.flag_count
329FROM exercise_slide_submissions AS ess
330JOIN courses AS c
331 ON ess.course_id = c.id
332WHERE ess.exercise_id = $1
333 AND ess.id <> ALL($2)
334 AND ess.user_id <> $3
335 AND ess.deleted_at IS NULL
336 AND ess.flag_count < c.flagged_answers_threshold
337ORDER BY ess.user_id, ess.created_at DESC
338 "#,
339 exercise_id,
340 excluded_ids,
341 excluded_user_id,
342 )
343 .fetch_all(conn)
344 .await?;
345 let mut rng = rand::rng();
347 res.shuffle(&mut rng);
348 Ok(res.into_iter().next())
349}
350
351pub async fn get_by_exercise_id(
352 conn: &mut PgConnection,
353 exercise_id: Uuid,
354 pagination: Pagination,
355) -> ModelResult<Vec<ExerciseSlideSubmission>> {
356 let submissions = sqlx::query_as!(
357 ExerciseSlideSubmission,
358 r#"
359SELECT id,
360 created_at,
361 updated_at,
362 deleted_at,
363 exercise_slide_id,
364 course_id,
365 exam_id,
366 exercise_id,
367 user_id,
368 user_points_update_strategy AS "user_points_update_strategy: _",
369 flag_count
370FROM exercise_slide_submissions
371WHERE exercise_id = $1
372 AND deleted_at IS NULL
373LIMIT $2 OFFSET $3;
374 "#,
375 exercise_id,
376 pagination.limit(),
377 pagination.offset(),
378 )
379 .fetch_all(conn)
380 .await?;
381 Ok(submissions)
382}
383
384pub async fn get_users_all_submissions_for_course_or_exam(
385 conn: &mut PgConnection,
386 user_id: Uuid,
387 course_or_exam_id: CourseOrExamId,
388) -> ModelResult<Vec<ExerciseSlideSubmission>> {
389 let (course_id, exam_id) = course_or_exam_id.to_course_and_exam_ids();
390 let submissions = sqlx::query_as!(
391 ExerciseSlideSubmission,
392 r#"
393SELECT id,
394 created_at,
395 updated_at,
396 deleted_at,
397 exercise_slide_id,
398 course_id,
399 exam_id,
400 exercise_id,
401 user_id,
402 user_points_update_strategy AS "user_points_update_strategy: _",
403 flag_count
404FROM exercise_slide_submissions
405WHERE user_id = $1
406 AND (course_id = $2 OR exam_id = $3)
407 AND deleted_at IS NULL
408 "#,
409 user_id,
410 course_id,
411 exam_id,
412 )
413 .fetch_all(conn)
414 .await?;
415 Ok(submissions)
416}
417
418pub async fn get_users_submissions_for_exercise(
419 conn: &mut PgConnection,
420 user_id: Uuid,
421 exercise_id: Uuid,
422) -> ModelResult<Vec<ExerciseSlideSubmission>> {
423 let submissions = sqlx::query_as!(
424 ExerciseSlideSubmission,
425 r#"
426SELECT id,
427 created_at,
428 updated_at,
429 deleted_at,
430 exercise_slide_id,
431 course_id,
432 exam_id,
433 exercise_id,
434 user_id,
435 user_points_update_strategy AS "user_points_update_strategy: _",
436 flag_count
437FROM exercise_slide_submissions
438WHERE user_id = $1
439 AND exercise_id = $2
440 AND deleted_at IS NULL
441ORDER BY created_at DESC
442 "#,
443 user_id,
444 exercise_id,
445 )
446 .fetch_all(conn)
447 .await?;
448 Ok(submissions)
449}
450
451pub async fn get_users_latest_exercise_slide_submission(
452 conn: &mut PgConnection,
453 exercise_slide_id: Uuid,
454 user_id: Uuid,
455) -> ModelResult<ExerciseSlideSubmission> {
456 let res = sqlx::query_as!(
457 ExerciseSlideSubmission,
458 r#"
459SELECT id,
460 created_at,
461 updated_at,
462 deleted_at,
463 exercise_slide_id,
464 course_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_slide_id = $1
472 AND user_id = $2
473 AND deleted_at IS NULL
474ORDER BY created_at DESC
475LIMIT 1
476 "#,
477 exercise_slide_id,
478 user_id
479 )
480 .fetch_one(conn)
481 .await?;
482 Ok(res)
483}
484
485pub async fn try_to_get_users_latest_exercise_slide_submission(
486 conn: &mut PgConnection,
487 exercise_slide_id: Uuid,
488 user_id: Uuid,
489) -> ModelResult<Option<ExerciseSlideSubmission>> {
490 get_users_latest_exercise_slide_submission(conn, exercise_slide_id, user_id)
491 .await
492 .optional()
493}
494
495pub async fn get_course_and_exam_id(
496 conn: &mut PgConnection,
497 id: Uuid,
498) -> ModelResult<CourseOrExamId> {
499 let res = sqlx::query!(
500 "
501SELECT course_id,
502 exam_id
503FROM exercise_slide_submissions
504WHERE id = $1
505 AND deleted_at IS NULL
506 ",
507 id
508 )
509 .fetch_one(conn)
510 .await?;
511 CourseOrExamId::from_course_and_exam_ids(res.course_id, res.exam_id)
512}
513
514pub async fn exercise_slide_submission_count(
515 conn: &mut PgConnection,
516 exercise_id: Uuid,
517) -> ModelResult<u32> {
518 let count = sqlx::query!(
519 "
520SELECT COUNT(*) as count
521FROM exercise_slide_submissions
522WHERE exercise_id = $1
523AND deleted_at IS NULL
524",
525 exercise_id,
526 )
527 .fetch_one(conn)
528 .await?;
529 Ok(count.count.unwrap_or(0).try_into()?)
530}
531
532pub async fn exercise_slide_submissions(
533 conn: &mut PgConnection,
534 exercise_id: Uuid,
535 pagination: Pagination,
536) -> ModelResult<Vec<ExerciseSlideSubmission>> {
537 let submissions = sqlx::query_as!(
538 ExerciseSlideSubmission,
539 r#"
540SELECT id,
541 created_at,
542 updated_at,
543 deleted_at,
544 exercise_slide_id,
545 course_id,
546 exam_id,
547 exercise_id,
548 user_id,
549 user_points_update_strategy AS "user_points_update_strategy: _",
550 flag_count
551FROM exercise_slide_submissions
552WHERE exercise_id = $1
553 AND deleted_at IS NULL
554ORDER BY created_at DESC
555LIMIT $2 OFFSET $3
556 "#,
557 exercise_id,
558 pagination.limit(),
559 pagination.offset(),
560 )
561 .fetch_all(conn)
562 .await?;
563 Ok(submissions)
564}
565
566pub async fn exercise_slide_submission_count_with_exam_id(
567 conn: &mut PgConnection,
568 exam_id: Uuid,
569) -> ModelResult<u32> {
570 let count = sqlx::query!(
571 "
572SELECT COUNT(*) as count
573FROM exercise_slide_submissions
574WHERE exam_id = $1
575AND deleted_at IS NULL
576",
577 exam_id,
578 )
579 .fetch_one(conn)
580 .await?;
581 Ok(count.count.unwrap_or(0).try_into()?)
582}
583
584pub async fn exercise_slide_submission_count_with_exercise_id(
585 conn: &mut PgConnection,
586 exercise_id: Uuid,
587) -> ModelResult<u32> {
588 let count = sqlx::query!(
589 "
590SELECT COUNT(*) as count
591FROM exercise_slide_submissions
592WHERE exercise_id = $1
593AND deleted_at IS NULL
594",
595 exercise_id,
596 )
597 .fetch_one(conn)
598 .await?;
599 Ok(count.count.unwrap_or(0).try_into()?)
600}
601
602pub async fn get_latest_exercise_slide_submissions_and_user_exercise_state_list_with_exercise_id(
603 conn: &mut PgConnection,
604 exercise_id: Uuid,
605 pagination: Pagination,
606) -> ModelResult<Vec<ExerciseSlideSubmissionAndUserExerciseState>> {
607 let submissions = sqlx::query_as!(
608 ExerciseSlideSubmission,
609 r#"
610 SELECT DISTINCT ON (user_id)
611 id,
612 created_at,
613 updated_at,
614 deleted_at,
615 exercise_slide_id,
616 course_id,
617 exam_id,
618 exercise_id,
619 user_id,
620 user_points_update_strategy AS "user_points_update_strategy: _",
621 flag_count
622FROM exercise_slide_submissions
623WHERE exercise_id = $1
624 AND deleted_at IS NULL
625ORDER BY user_id, created_at DESC
626LIMIT $2 OFFSET $3
627 "#,
628 exercise_id,
629 pagination.limit(),
630 pagination.offset(),
631 )
632 .fetch_all(&mut *conn)
633 .await?;
634
635 let user_ids = submissions
636 .iter()
637 .map(|sub| sub.user_id)
638 .collect::<Vec<_>>();
639
640 let exercise = exercises::get_by_id(conn, exercise_id).await?;
641 let exam_id = exercise.exam_id;
642 let course_id = exercise.course_id;
643
644 let user_exercise_states_list =
645 user_exercise_states::get_or_create_user_exercise_state_for_users(
646 conn,
647 &user_ids,
648 exercise_id,
649 course_id,
650 exam_id,
651 )
652 .await?;
653
654 let mut user_exercise_state_id_list: Vec<Uuid> = Vec::new();
655
656 for (_key, value) in user_exercise_states_list.clone().into_iter() {
657 user_exercise_state_id_list.push(value.id);
658 }
659
660 let exercise = exercises::get_by_id(conn, exercise_id).await?;
661 let exam_id = exercise
662 .exam_id
663 .ok_or_else(|| ModelError::new(ModelErrorType::Generic, "No exam id found", None))?;
664
665 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?;
666
667 let user_exam_enrollments_list =
668 exams::get_exam_enrollments_for_users(conn, exam_id, &user_ids).await?;
669
670 let mut list: Vec<ExerciseSlideSubmissionAndUserExerciseState> = Vec::new();
671 for sub in submissions {
672 let user_exercise_state = user_exercise_states_list
673 .get(&sub.user_id)
674 .ok_or_else(|| ModelError::new(ModelErrorType::Generic, "No user found", None))?;
675
676 let teacher_grading_decision = teacher_grading_decisions_list.get(&user_exercise_state.id);
677 let user_exam_enrollment =
678 user_exam_enrollments_list
679 .get(&sub.user_id)
680 .ok_or_else(|| {
681 ModelError::new(
682 ModelErrorType::Generic,
683 "No users exam_enrollment found",
684 None,
685 )
686 })?;
687
688 if user_exam_enrollment.ended_at.is_some() {
690 let data = ExerciseSlideSubmissionAndUserExerciseState {
691 exercise: exercise.clone(),
692 exercise_slide_submission: sub,
693 user_exercise_state: user_exercise_state.clone(),
694 teacher_grading_decision: teacher_grading_decision.cloned(),
695 user_exam_enrollment: user_exam_enrollment.clone(),
696 };
697 list.push(data);
698 }
699 }
700
701 Ok(list)
702}
703
704pub async fn get_course_daily_slide_submission_counts(
705 conn: &mut PgConnection,
706 course: &Course,
707) -> ModelResult<Vec<ExerciseSlideSubmissionCount>> {
708 let res = sqlx::query_as!(
709 ExerciseSlideSubmissionCount,
710 r#"
711SELECT DATE(created_at) date, count(*)::integer
712FROM exercise_slide_submissions
713WHERE course_id = $1
714AND deleted_at IS NULL
715GROUP BY date
716ORDER BY date;
717 "#,
718 course.id
719 )
720 .fetch_all(conn)
721 .await?;
722 Ok(res)
723}
724
725pub async fn get_course_daily_user_counts_with_submissions(
726 conn: &mut PgConnection,
727 course: &Course,
728) -> ModelResult<Vec<ExerciseSlideSubmissionCount>> {
729 let res = sqlx::query_as!(
730 ExerciseSlideSubmissionCount,
731 r#"
732SELECT DATE(created_at) date, count(DISTINCT user_id)::integer
733FROM exercise_slide_submissions
734WHERE course_id = $1
735AND deleted_at IS NULL
736GROUP BY date
737ORDER BY date;
738 "#,
739 course.id
740 )
741 .fetch_all(conn)
742 .await?;
743 Ok(res)
744}
745
746pub async fn answer_requiring_attention_count(
747 conn: &mut PgConnection,
748 exercise_id: Uuid,
749) -> ModelResult<u32> {
750 let count = sqlx::query!(
751 r#"
752 SELECT
753 COUNT(DISTINCT us_state.user_id) as count
754 FROM user_exercise_states AS us_state
755 JOIN exercise_task_submissions AS t_submission
756 ON us_state.selected_exercise_slide_id =
757 t_submission.exercise_slide_id
758 JOIN exercise_slide_submissions AS s_submission
759 ON t_submission.exercise_slide_submission_id =
760 s_submission.id
761 WHERE us_state.selected_exercise_slide_id =
762 t_submission.exercise_slide_id
763 AND us_state.user_id = s_submission.user_id
764 AND us_state.exercise_id = $1
765 AND us_state.reviewing_stage = 'waiting_for_manual_grading'
766 AND us_state.deleted_at IS NULL
767 AND s_submission.deleted_at IS NULL
768 AND t_submission.deleted_at IS NULL"#,
769 exercise_id,
770 )
771 .fetch_one(conn)
772 .await?;
773 Ok(count.count.unwrap_or(0).try_into()?)
774}
775
776pub async fn get_count_of_answers_requiring_attention_in_exercise_by_course_id(
777 conn: &mut PgConnection,
778 course_id: Uuid,
779) -> ModelResult<Vec<ExerciseAnswersInCourseRequiringAttentionCount>> {
780 let count_list = sqlx::query_as!(
781 ExerciseAnswersInCourseRequiringAttentionCount,
782 r#"
783SELECT exercises.id,
784 (
785 SELECT COUNT(DISTINCT us_state.user_id)::integer AS COUNT
786 FROM exercises AS exercises2
787 LEFT JOIN user_exercise_states AS us_state ON us_state.exercise_id = exercises2.id
788 LEFT JOIN exercise_slide_submissions AS s_submission ON us_state.selected_exercise_slide_id = s_submission.exercise_slide_id
789 LEFT JOIN exercise_task_submissions AS t_submission ON s_submission.id = t_submission.exercise_slide_submission_id
790 WHERE us_state.selected_exercise_slide_id = t_submission.exercise_slide_id
791 AND us_state.user_id = s_submission.user_id
792 AND us_state.reviewing_stage = 'waiting_for_manual_grading'
793 AND us_state.deleted_at IS NULL
794 AND s_submission.deleted_at IS NULL
795 AND t_submission.deleted_at IS NULL
796 AND exercises2.course_id = $1
797 AND exercises.id = exercises2.id
798 GROUP BY exercises2.id
799 ),
800 exercises.order_number,
801 exercises.name,
802 exercises.page_id,
803 exercises.chapter_id
804FROM exercises
805WHERE exercises.course_id = $1
806 AND exercises.deleted_at IS NULL
807GROUP BY exercises.id;
808"#,
809 course_id,
810 )
811 .fetch_all(conn)
812 .await?;
813 Ok(count_list)
814}
815
816pub async fn exercise_slide_submissions_for_answers_requiring_attention(
817 conn: &mut PgConnection,
818 exercise_id: Uuid,
819 pagination: Pagination,
820) -> ModelResult<Vec<ExerciseSlideSubmission>> {
821 let submissions = sqlx::query_as!(
822 ExerciseSlideSubmission,
823 r#"
824SELECT id,
825 created_at,
826 updated_at,
827 deleted_at,
828 exercise_slide_id,
829 course_id,
830 exam_id,
831 exercise_id,
832 user_id,
833 user_points_update_strategy AS "user_points_update_strategy: _",
834 flag_count
835FROM exercise_slide_submissions
836WHERE exercise_id = $1
837 AND deleted_at IS NULL
838LIMIT $2 OFFSET $3
839 "#,
840 exercise_id,
841 pagination.limit(),
842 pagination.offset(),
843 )
844 .fetch_all(conn)
845 .await?;
846 Ok(submissions)
847}
848
849pub async fn get_all_answers_requiring_attention(
850 conn: &mut PgConnection,
851 exercise_id: Uuid,
852 pagination: Pagination,
853) -> ModelResult<Vec<AnswerRequiringAttention>> {
854 let submissions = sqlx::query_as!(
855 AnswerRequiringAttention,
856 r#"
857 SELECT DISTINCT ON (us_state.user_id)
858 us_state.id,
859 us_state.user_id,
860 us_state.exercise_id,
861 us_state.score_given,
862 us_state.grading_progress as "grading_progress: _",
863 t_submission.data_json,
864 s_submission.created_at,
865 s_submission.updated_at,
866 s_submission.deleted_at,
867 s_submission.id AS submission_id
868 FROM user_exercise_states AS us_state
869 JOIN exercise_task_submissions AS t_submission
870 ON us_state.selected_exercise_slide_id =
871 t_submission.exercise_slide_id
872 JOIN exercise_slide_submissions AS s_submission
873 ON t_submission.exercise_slide_submission_id =
874 s_submission.id
875 WHERE us_state.selected_exercise_slide_id =
876 t_submission.exercise_slide_id
877 AND us_state.user_id = s_submission.user_id
878 AND us_state.exercise_id = $1
879 AND us_state.reviewing_stage = 'waiting_for_manual_grading'
880 AND us_state.deleted_at IS NULL
881 AND s_submission.deleted_at IS NULL
882 AND t_submission.deleted_at IS NULL
883 ORDER BY us_state.user_id, s_submission.created_at DESC
884 LIMIT $2 OFFSET $3;"#,
885 exercise_id,
886 pagination.limit(),
887 pagination.offset(),
888 )
889 .fetch_all(conn)
890 .await?;
891 Ok(submissions)
892}
893
894pub async fn get_course_exercise_slide_submission_counts_by_weekday_and_hour(
895 conn: &mut PgConnection,
896 course: &Course,
897) -> ModelResult<Vec<ExerciseSlideSubmissionCountByWeekAndHour>> {
898 let res = sqlx::query_as!(
899 ExerciseSlideSubmissionCountByWeekAndHour,
900 r#"
901SELECT date_part('isodow', created_at)::integer isodow,
902 date_part('hour', created_at)::integer "hour",
903 count(*)::integer
904FROM exercise_slide_submissions
905WHERE course_id = $1
906AND deleted_at IS NULL
907GROUP BY isodow,
908 "hour"
909ORDER BY isodow,
910 hour;
911 "#,
912 course.id
913 )
914 .fetch_all(conn)
915 .await?;
916 Ok(res)
917}
918
919pub async fn get_course_exercise_slide_submission_counts_by_exercise(
920 conn: &mut PgConnection,
921 course: &Course,
922) -> ModelResult<Vec<ExerciseSlideSubmissionCountByExercise>> {
923 let res = sqlx::query_as!(
924 ExerciseSlideSubmissionCountByExercise,
925 r#"
926SELECT counts.*, exercises.name exercise_name
927 FROM (
928 SELECT exercise_id, count(*)::integer count
929 FROM exercise_slide_submissions
930 WHERE course_id = $1
931 AND deleted_at IS NULL
932 GROUP BY exercise_id
933 ) counts
934 JOIN exercises ON (counts.exercise_id = exercises.id);
935 "#,
936 course.id
937 )
938 .fetch_all(conn)
939 .await?;
940 Ok(res)
941}
942
943pub async fn get_exercise_slide_submission_counts_for_exercise_user(
944 conn: &mut PgConnection,
945 exercise_id: Uuid,
946 course_id_or_exam_id: CourseOrExamId,
947 user_id: Uuid,
948) -> ModelResult<HashMap<Uuid, i64>> {
949 let ci_id_or_e_id = match course_id_or_exam_id {
950 CourseOrExamId::Course(id) => id,
951 CourseOrExamId::Exam(id) => id,
952 };
953 let res = sqlx::query!(
954 r#"
955SELECT exercise_slide_id,
956 COUNT(*) as count
957FROM exercise_slide_submissions
958WHERE exercise_id = $1
959 AND (course_id = $2 OR exam_id = $2)
960 AND user_id = $3
961 AND deleted_at IS NULL
962GROUP BY exercise_slide_id;
963 "#,
964 exercise_id,
965 ci_id_or_e_id,
966 user_id
967 )
968 .fetch_all(conn)
969 .await?
970 .iter()
971 .map(|row| (row.exercise_slide_id, row.count.unwrap_or(0)))
972 .collect::<HashMap<Uuid, i64>>();
973
974 Ok(res)
975}
976
977pub async fn get_exercise_slide_submission_info(
978 conn: &mut PgConnection,
979 exercise_slide_submission_id: Uuid,
980 user_id: Uuid,
981 fetch_service_info: impl Fn(Url) -> BoxFuture<'static, ModelResult<ExerciseServiceInfoApi>>,
982 include_deleted_tasks: bool,
983) -> ModelResult<ExerciseSlideSubmissionInfo> {
984 let exercise_slide_submission = get_by_id(&mut *conn, exercise_slide_submission_id).await?;
985 let exercise =
986 crate::exercises::get_by_id(&mut *conn, exercise_slide_submission.exercise_id).await?;
987 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?;
988 let user_exercise_state = crate::user_exercise_states::get_user_exercise_state_if_exists(
989 &mut *conn,
990 user_id,
991 exercise_slide_submission.exercise_id,
992 CourseOrExamId::from_course_and_exam_ids(
993 exercise_slide_submission.course_id,
994 exercise_slide_submission.exam_id,
995 )?,
996 )
997 .await?;
998
999 Ok(ExerciseSlideSubmissionInfo {
1000 exercise,
1001 tasks,
1002 exercise_slide_submission,
1003 user_exercise_state,
1004 })
1005}
1006
1007pub async fn delete_exercise_submissions_with_exam_id_and_user_id(
1008 conn: &mut PgConnection,
1009 exam_id: Uuid,
1010 user_id: Uuid,
1011) -> ModelResult<()> {
1012 sqlx::query!(
1013 "
1014UPDATE exercise_slide_submissions
1015SET deleted_at = now()
1016WHERE exam_id = $1 AND user_id = $2
1017AND deleted_at IS NULL
1018 ",
1019 exam_id,
1020 user_id,
1021 )
1022 .execute(&mut *conn)
1023 .await?;
1024 Ok(())
1025}