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