1use std::collections::HashSet;
2
3use futures::{Stream, future::BoxFuture};
4use serde_json::Value;
5use url::Url;
6use utoipa::ToSchema;
7
8use crate::{
9 CourseOrExamId,
10 exercise_service_info::{self, ExerciseServiceInfoApi},
11 exercise_services, exercise_slide_submissions,
12 exercise_tasks::{CourseMaterialExerciseTask, ExerciseTask},
13 library::custom_view_exercises::{CustomViewExerciseTaskSubmission, CustomViewExerciseTasks},
14 peer_or_self_review_question_submissions::PeerOrSelfReviewQuestionSubmission,
15 peer_or_self_review_questions::PeerOrSelfReviewQuestion,
16 peer_or_self_review_submissions::PeerOrSelfReviewSubmission,
17 prelude::*,
18};
19
20#[derive(Debug, Serialize, Deserialize, PartialEq, Clone, ToSchema)]
21
22pub struct ExerciseTaskSubmission {
23 pub id: Uuid,
24 pub created_at: DateTime<Utc>,
25 pub updated_at: DateTime<Utc>,
26 pub deleted_at: Option<DateTime<Utc>>,
27 pub exercise_slide_submission_id: Uuid,
28 pub exercise_task_id: Uuid,
29 pub exercise_slide_id: Uuid,
30 pub data_json: Option<serde_json::Value>,
31 pub exercise_task_grading_id: Option<Uuid>,
32 pub metadata: Option<serde_json::Value>,
33}
34
35#[derive(Debug, Serialize, Deserialize, PartialEq, Clone, ToSchema)]
36
37pub struct PeerOrSelfReviewsReceived {
38 pub peer_or_self_review_questions: Vec<PeerOrSelfReviewQuestion>,
39 pub peer_or_self_review_question_submissions: Vec<PeerOrSelfReviewQuestionSubmission>,
40 pub peer_or_self_review_submissions: Vec<PeerOrSelfReviewSubmission>,
41}
42
43#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
44
45pub struct SubmissionData {
46 pub exercise_id: Uuid,
47 pub course_id: Uuid,
48 pub exercise_slide_submission_id: Uuid,
49 pub exercise_slide_id: Uuid,
50 pub exercise_task_id: Uuid,
51 pub user_id: Uuid,
52 pub course_instance_id: Uuid,
53 pub data_json: Value,
54 pub id: Uuid,
55}
56
57#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
58
59pub struct ExportedSubmission {
60 pub id: Uuid,
61 pub user_id: Uuid,
62 pub created_at: DateTime<Utc>,
63 pub exercise_id: Uuid,
64 pub exercise_task_id: Uuid,
65 pub score_given: Option<f32>,
66 pub data_json: Option<serde_json::Value>,
67}
68
69#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
70
71pub struct ExportedCourseSubmission {
72 pub exercise_slide_submission_id: Uuid,
73 pub id: Uuid,
74 pub user_id: Uuid,
75 pub created_at: DateTime<Utc>,
76 pub course_id: Option<Uuid>,
77 pub exercise_id: Uuid,
78 pub exercise_task_id: Uuid,
79 pub score_given: Option<f32>,
80 pub data_json: Option<serde_json::Value>,
81}
82
83#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
85pub struct ExerciseTaskSubmissionCsvExportData {
86 pub exercise_slide_submission_id: Uuid,
87 pub exercise_task_submission_id: Uuid,
88 pub exercise_task_id: Uuid,
89 pub exercise_id: Uuid,
90 pub user_id: Uuid,
91 pub submitted_at: DateTime<Utc>,
92 pub answer: Option<serde_json::Value>,
93}
94
95pub async fn get_submission(
96 conn: &mut PgConnection,
97 submission_id: Uuid,
98) -> ModelResult<ExerciseTaskSubmission> {
99 let res = sqlx::query_as!(
100 ExerciseTaskSubmission,
101 "
102SELECT *
103FROM exercise_task_submissions
104WHERE id = $1
105",
106 submission_id
107 )
108 .fetch_one(conn)
109 .await?;
110 Ok(res)
111}
112
113pub async fn insert_with_id(
115 conn: &mut PgConnection,
116 submission_data: &SubmissionData,
117) -> ModelResult<Uuid> {
118 let res = sqlx::query!(
119 "
120INSERT INTO exercise_task_submissions (
121 id,
122 exercise_slide_submission_id,
123 exercise_slide_id,
124 exercise_task_id,
125 data_json
126 )
127VALUES ($1, $2, $3, $4, $5)
128RETURNING id
129 ",
130 submission_data.id,
131 submission_data.exercise_slide_submission_id,
132 submission_data.exercise_slide_id,
133 submission_data.exercise_task_id,
134 submission_data.data_json,
135 )
136 .fetch_one(conn)
137 .await?;
138 Ok(res.id)
139}
140
141pub async fn insert(
142 conn: &mut PgConnection,
143 pkey_policy: PKeyPolicy<Uuid>,
144 exercise_slide_submission_id: Uuid,
145 exercise_slide_id: Uuid,
146 exercise_task_id: Uuid,
147 data_json: &Value,
148) -> ModelResult<Uuid> {
149 let res = sqlx::query!(
150 "
151INSERT INTO exercise_task_submissions (
152 id,
153 exercise_slide_submission_id,
154 exercise_slide_id,
155 exercise_task_id,
156 data_json
157 )
158 VALUES ($1, $2, $3, $4, $5)
159 RETURNING id
160 ",
161 pkey_policy.into_uuid(),
162 exercise_slide_submission_id,
163 exercise_slide_id,
164 exercise_task_id,
165 data_json,
166 )
167 .fetch_one(conn)
168 .await?;
169 Ok(res.id)
170}
171
172pub async fn get_by_id(conn: &mut PgConnection, id: Uuid) -> ModelResult<ExerciseTaskSubmission> {
173 let submission = sqlx::query_as!(
174 ExerciseTaskSubmission,
175 "
176SELECT *
177FROM exercise_task_submissions
178WHERE id = $1
179",
180 id
181 )
182 .fetch_one(conn)
183 .await?;
184 Ok(submission)
185}
186
187pub async fn get_by_exercise_slide_submission_id(
188 conn: &mut PgConnection,
189 exercise_slide_submission_id: Uuid,
190) -> ModelResult<Vec<ExerciseTaskSubmission>> {
191 let submissions = sqlx::query_as!(
192 ExerciseTaskSubmission,
193 "
194SELECT *
195FROM exercise_task_submissions
196WHERE exercise_slide_submission_id = $1
197 ",
198 exercise_slide_submission_id
199 )
200 .fetch_all(conn)
201 .await?;
202 Ok(submissions)
203}
204
205pub async fn get_csv_export_data_by_exercise_and_task(
207 conn: &mut PgConnection,
208 exercise_id: Uuid,
209 exercise_task_id: Uuid,
210) -> ModelResult<Vec<ExerciseTaskSubmissionCsvExportData>> {
211 let submissions = sqlx::query_as!(
212 ExerciseTaskSubmissionCsvExportData,
213 r#"
214SELECT ets.exercise_slide_submission_id,
215 ets.id AS exercise_task_submission_id,
216 ets.exercise_task_id,
217 ess.exercise_id,
218 ess.user_id,
219 ets.created_at AS submitted_at,
220 ets.data_json AS answer
221FROM exercise_task_submissions ets
222 JOIN exercise_slide_submissions ess ON ets.exercise_slide_submission_id = ess.id
223WHERE ess.exercise_id = $1
224 AND ets.exercise_task_id = $2
225 AND ess.deleted_at IS NULL
226 AND ets.deleted_at IS NULL
227ORDER BY ets.created_at ASC
228 "#,
229 exercise_id,
230 exercise_task_id
231 )
232 .fetch_all(conn)
233 .await?;
234 Ok(submissions)
235}
236
237pub async fn get_csv_export_data_by_exercise_and_task_latest_per_user(
239 conn: &mut PgConnection,
240 exercise_id: Uuid,
241 exercise_task_id: Uuid,
242) -> ModelResult<Vec<ExerciseTaskSubmissionCsvExportData>> {
243 let submissions = sqlx::query_as!(
244 ExerciseTaskSubmissionCsvExportData,
245 r#"
246WITH latest AS (
247 SELECT DISTINCT ON (ess.user_id) ets.id AS exercise_task_submission_id
248 FROM exercise_task_submissions ets
249 JOIN exercise_slide_submissions ess ON ets.exercise_slide_submission_id = ess.id
250 WHERE ess.exercise_id = $1
251 AND ets.exercise_task_id = $2
252 AND ess.deleted_at IS NULL
253 AND ets.deleted_at IS NULL
254 ORDER BY ess.user_id, ets.created_at DESC
255)
256SELECT ets.exercise_slide_submission_id,
257 ets.id AS exercise_task_submission_id,
258 ets.exercise_task_id,
259 ess.exercise_id,
260 ess.user_id,
261 ets.created_at AS submitted_at,
262 ets.data_json AS answer
263FROM exercise_task_submissions ets
264JOIN exercise_slide_submissions ess ON ets.exercise_slide_submission_id = ess.id
265JOIN latest ON latest.exercise_task_submission_id = ets.id
266WHERE ess.exercise_id = $1
267 AND ets.exercise_task_id = $2
268 AND ess.deleted_at IS NULL
269 AND ets.deleted_at IS NULL
270ORDER BY ets.created_at ASC
271 "#,
272 exercise_id,
273 exercise_task_id
274 )
275 .fetch_all(conn)
276 .await?;
277 Ok(submissions)
278}
279
280pub async fn get_users_latest_exercise_task_submissions_for_exercise_slide(
281 conn: &mut PgConnection,
282 exercise_slide_id: Uuid,
283 user_id: Uuid,
284) -> ModelResult<Option<Vec<ExerciseTaskSubmission>>> {
285 let exercise_slide_submission =
286 exercise_slide_submissions::try_to_get_users_latest_exercise_slide_submission(
287 conn,
288 exercise_slide_id,
289 user_id,
290 )
291 .await?;
292 if let Some(exercise_slide_submission) = exercise_slide_submission {
293 let task_submissions = sqlx::query_as!(
294 ExerciseTaskSubmission,
295 "
296SELECT *
297FROM exercise_task_submissions
298WHERE exercise_slide_submission_id = $1
299 AND deleted_at IS NULL
300 ",
301 exercise_slide_submission.id
302 )
303 .fetch_all(conn)
304 .await?;
305 Ok(Some(task_submissions))
306 } else {
307 Ok(None)
308 }
309}
310
311pub async fn get_course_and_exam_id(
312 conn: &mut PgConnection,
313 id: Uuid,
314) -> ModelResult<CourseOrExamId> {
315 let res = sqlx::query!(
316 "
317SELECT ess.course_id,
318 ess.exam_id
319FROM exercise_task_submissions ets
320 JOIN exercise_slide_submissions ess ON ets.exercise_slide_submission_id = ess.id
321WHERE ets.id = $1
322 AND ets.deleted_at IS NULL
323 AND ess.deleted_at IS NULL
324 ",
325 id
326 )
327 .fetch_one(conn)
328 .await?;
329 CourseOrExamId::from_course_and_exam_ids(res.course_id, res.exam_id)
330}
331
332pub async fn get_peer_reviews_received(
333 conn: &mut PgConnection,
334 exercise_id: Uuid,
335 exercise_slide_submission_id: Uuid,
336 user_id: Uuid,
337) -> ModelResult<PeerOrSelfReviewsReceived> {
338 let exercise = crate::exercises::get_by_id(&mut *conn, exercise_id).await?;
339 let peer_or_self_review_config =
340 crate::peer_or_self_review_configs::get_by_exercise_or_course_id(
341 &mut *conn,
342 &exercise,
343 exercise.course_id.ok_or_else(|| {
344 ModelError::new(
345 ModelErrorType::InvalidRequest,
346 "Peer reviews work only on courses (and not, for example, on exams)"
347 .to_string(),
348 None,
349 )
350 })?,
351 )
352 .await?;
353 let peer_or_self_review_questions =
354 crate::peer_or_self_review_questions::get_by_peer_or_self_review_configs_id(
355 &mut *conn,
356 peer_or_self_review_config.id,
357 )
358 .await?;
359
360 let peer_or_self_review_question_ids = peer_or_self_review_questions
361 .iter()
362 .map(|x| x.id)
363 .collect::<Vec<_>>();
364
365 let peer_or_self_review_submissions =
366 crate::peer_or_self_review_submissions::get_received_peer_or_self_review_submissions_for_user_by_peer_or_self_review_config_id_and_exercise_slide_submission(
367 &mut *conn,
368 user_id,
369 exercise_slide_submission_id,
370 peer_or_self_review_config.id,
371 )
372 .await?;
373
374 let peer_or_self_review_question_submissions =
375 crate::peer_or_self_review_question_submissions::get_by_peer_reviews_question_ids(
376 &mut *conn,
377 &peer_or_self_review_question_ids,
378 user_id,
379 exercise_slide_submission_id,
380 )
381 .await?;
382
383 Ok(PeerOrSelfReviewsReceived {
384 peer_or_self_review_questions,
385 peer_or_self_review_question_submissions,
386 peer_or_self_review_submissions,
387 })
388}
389
390pub async fn set_grading_id(
391 conn: &mut PgConnection,
392 grading_id: Uuid,
393 submission_id: Uuid,
394) -> ModelResult<ExerciseTaskSubmission> {
395 let res = sqlx::query_as!(
396 ExerciseTaskSubmission,
397 "
398UPDATE exercise_task_submissions
399SET exercise_task_grading_id = $1
400WHERE id = $2
401RETURNING *
402",
403 grading_id,
404 submission_id
405 )
406 .fetch_one(conn)
407 .await?;
408 Ok(res)
409}
410
411pub fn stream_exam_submissions(
412 conn: &mut PgConnection,
413 exam_id: Uuid,
414) -> impl Stream<Item = sqlx::Result<ExportedSubmission>> + '_ {
415 sqlx::query_as!(
416 ExportedSubmission,
417 "
418SELECT exercise_task_submissions.id,
419 user_id,
420 exercise_task_submissions.created_at,
421 exercise_slide_submissions.exercise_id,
422 exercise_task_submissions.exercise_task_id,
423 exercise_task_gradings.score_given,
424 exercise_task_submissions.data_json
425FROM exercise_task_submissions
426 JOIN exercise_slide_submissions ON exercise_task_submissions.exercise_slide_submission_id = exercise_slide_submissions.id
427 JOIN exercise_task_gradings on exercise_task_submissions.exercise_task_grading_id = exercise_task_gradings.id
428 JOIN exercises on exercise_slide_submissions.exercise_id = exercises.id
429WHERE exercise_slide_submissions.exam_id = $1
430 AND exercise_task_submissions.deleted_at IS NULL
431 AND exercise_task_gradings.deleted_at IS NULL
432 AND exercises.deleted_at IS NULL;
433 ",
434 exam_id
435 )
436 .fetch(conn)
437}
438
439pub fn stream_course_submissions(
440 conn: &mut PgConnection,
441 course_id: Uuid,
442) -> impl Stream<Item = sqlx::Result<ExportedCourseSubmission>> + '_ {
443 sqlx::query_as!(
444 ExportedCourseSubmission,
445 "
446SELECT exercise_task_submissions.exercise_slide_submission_id,
447 exercise_task_submissions.id,
448 user_id,
449 exercise_task_submissions.created_at,
450 exercise_slide_submissions.course_id,
451 exercise_slide_submissions.exercise_id,
452 exercise_task_submissions.exercise_task_id,
453 exercise_task_gradings.score_given,
454 exercise_task_submissions.data_json
455FROM exercise_task_submissions
456 JOIN exercise_slide_submissions ON exercise_task_submissions.exercise_slide_submission_id = exercise_slide_submissions.id
457 JOIN exercise_task_gradings ON exercise_task_submissions.exercise_task_grading_id = exercise_task_gradings.id
458 JOIN exercises ON exercise_slide_submissions.exercise_id = exercises.id
459WHERE exercise_slide_submissions.course_id = $1
460 AND exercise_slide_submissions.deleted_at IS NULL
461 AND exercise_task_submissions.deleted_at IS NULL
462 AND exercise_task_gradings.deleted_at IS NULL
463 AND exercises.deleted_at IS NULL;
464 ",
465 course_id
466 )
467 .fetch(conn)
468}
469
470pub async fn get_exercise_task_submission_info_by_exercise_slide_submission_id(
472 conn: &mut PgConnection,
473 exercise_slide_submission_id: Uuid,
474 viewer_user_id: Uuid,
475 fetch_service_info: impl Fn(Url) -> BoxFuture<'static, ModelResult<ExerciseServiceInfoApi>>,
476 include_deleted_tasks: bool,
477) -> ModelResult<Vec<CourseMaterialExerciseTask>> {
478 let task_submisssions = crate::exercise_task_submissions::get_by_exercise_slide_submission_id(
479 &mut *conn,
480 exercise_slide_submission_id,
481 )
482 .await?;
483 let exercise_task_gradings =
484 crate::exercise_task_gradings::get_all_gradings_by_exercise_slide_submission_id(
485 &mut *conn,
486 exercise_slide_submission_id,
487 )
488 .await?;
489
490 let exercise_tasks = if include_deleted_tasks {
491 crate::exercise_tasks::get_exercise_tasks_by_exercise_slide_id_including_deleted::<
492 Vec<ExerciseTask>,
493 >(&mut *conn, &task_submisssions[0].exercise_slide_id)
494 .await?
495 } else {
496 crate::exercise_tasks::get_exercise_tasks_by_exercise_slide_id::<Vec<ExerciseTask>>(
497 &mut *conn,
498 &task_submisssions[0].exercise_slide_id,
499 )
500 .await?
501 };
502
503 let mut res = Vec::with_capacity(task_submisssions.len());
504
505 let unique_exercise_service_slugs = exercise_tasks
506 .iter()
507 .cloned()
508 .map(|et| et.exercise_type)
509 .collect::<HashSet<_>>()
510 .into_iter()
511 .collect::<Vec<_>>();
512 let exercise_service_slug_to_service_and_info =
513 exercise_service_info::get_selected_exercise_services_by_type(
514 &mut *conn,
515 &unique_exercise_service_slugs,
516 fetch_service_info,
517 )
518 .await?;
519
520 for ts in task_submisssions {
521 let grading = exercise_task_gradings
522 .iter()
523 .find(|g| Some(g.id) == ts.exercise_task_grading_id)
524 .ok_or_else(|| {
525 ModelError::new(
526 ModelErrorType::NotFound,
527 "Grading not found".to_string(),
528 None,
529 )
530 })?;
531 let task = exercise_tasks
532 .iter()
533 .find(|t| t.id == ts.exercise_task_id)
534 .ok_or_else(|| {
535 ModelError::new(
536 ModelErrorType::NotFound,
537 "Exercise task not found".to_string(),
538 None,
539 )
540 })?;
541 let (exercise_service, service_info) = exercise_service_slug_to_service_and_info
542 .get(&task.exercise_type)
543 .ok_or_else(|| {
544 ModelError::new(
545 ModelErrorType::InvalidRequest,
546 "Exercise service not found".to_string(),
547 None,
548 )
549 })?;
550 let mut exercise_iframe_url =
551 exercise_services::get_exercise_service_externally_preferred_baseurl(exercise_service)?;
552 exercise_iframe_url.set_path(&service_info.user_interface_iframe_path);
553 let course_material_exercise_task = CourseMaterialExerciseTask {
554 id: task.id,
555 exercise_service_slug: task.exercise_type.clone(),
556 exercise_slide_id: task.exercise_slide_id,
557 exercise_iframe_url: Some(exercise_iframe_url.to_string()),
558 pseudonumous_user_id: Some(Uuid::new_v5(
559 &service_info.exercise_service_id,
560 viewer_user_id.as_bytes(),
561 )),
562 assignment: task.assignment.clone(),
563 public_spec: task.public_spec.clone(),
564 model_solution_spec: task.model_solution_spec.clone(),
565 previous_submission: Some(ts),
566 previous_submission_grading: Some(grading.clone()),
567 order_number: task.order_number,
568 deleted_at: task.deleted_at,
569 };
570 res.push(course_material_exercise_task);
571 }
572 Ok(res)
573}
574
575pub async fn get_user_custom_view_exercise_tasks_by_module_and_exercise_type(
576 conn: &mut PgConnection,
577 exercise_type: &str,
578 course_module_id: Uuid,
579 user_id: Uuid,
580 course_id: Uuid,
581) -> ModelResult<CustomViewExerciseTasks> {
582 let task_submissions =
583 crate::exercise_task_submissions::get_user_latest_exercise_task_submissions_by_course_module_and_exercise_type(
584 &mut *conn,
585 user_id,
586 exercise_type,
587 course_module_id,
588 course_id,
589 )
590 .await?;
591 let task_gradings =
592 crate::exercise_task_gradings::get_user_exercise_task_gradings_by_module_and_exercise_type(
593 &mut *conn,
594 user_id,
595 exercise_type,
596 course_module_id,
597 course_id,
598 )
599 .await?;
600
601 let exercise_tasks = crate::exercise_tasks::get_all_exercise_tasks_by_module_and_exercise_type(
602 &mut *conn,
603 exercise_type,
604 course_module_id,
605 )
606 .await?;
607 let res: CustomViewExerciseTasks = CustomViewExerciseTasks {
608 exercise_tasks,
609 task_submissions,
610 task_gradings,
611 };
612 Ok(res)
613}
614
615pub async fn get_user_latest_exercise_task_submissions_by_course_module_and_exercise_type(
617 conn: &mut PgConnection,
618 user_id: Uuid,
619 exercise_type: &str,
620 module_id: Uuid,
621 course_id: Uuid,
622) -> ModelResult<Vec<CustomViewExerciseTaskSubmission>> {
623 let res: Vec<CustomViewExerciseTaskSubmission> = sqlx::query_as!(
624 CustomViewExerciseTaskSubmission,
625 r#"
626 SELECT DISTINCT ON (g.exercise_task_id)
627 g.id,
628 g.created_at,
629 g.exercise_slide_submission_id,
630 g.exercise_slide_id,
631 g.exercise_task_id,
632 g.exercise_task_grading_id,
633 g.data_json
634 FROM exercise_task_submissions g
635 JOIN exercise_tasks et ON et.id = g.exercise_task_id
636 JOIN exercise_slide_submissions ess ON ess.id = g.exercise_slide_submission_id
637 JOIN exercises e ON e.id = ess.exercise_id
638 JOIN chapters c ON c.id = e.chapter_id
639 WHERE ess.user_id = $1
640 AND ess.course_id = $2
641 AND et.exercise_type = $3
642 AND c.course_module_id = $4
643 AND g.deleted_at IS NULL
644 AND et.deleted_at IS NULL
645 AND ess.deleted_at IS NULL
646 AND e.deleted_at IS NULL
647 AND c.deleted_at IS NULL
648 ORDER BY g.exercise_task_id, g.created_at DESC
649 "#,
650 user_id,
651 course_id,
652 exercise_type,
653 module_id
654 )
655 .fetch_all(conn)
656 .await?;
657 Ok(res)
658}
659
660pub async fn get_ids_by_exercise_id(
661 conn: &mut PgConnection,
662 exercise_id: Uuid,
663) -> ModelResult<Vec<Uuid>> {
664 let res = sqlx::query!(
665 "
666SELECT id
667FROM exercise_task_submissions
668WHERE exercise_slide_submission_id IN (
669 SELECT id
670 FROM exercise_slide_submissions
671 WHERE exercise_id = $1
672)
673AND deleted_at IS NULL
674",
675 &exercise_id
676 )
677 .fetch_all(conn)
678 .await?;
679 Ok(res.iter().map(|x| x.id).collect())
680}
681
682pub async fn get_latest_submission_ids_by_exercise_id(
684 conn: &mut PgConnection,
685 exercise_id: Uuid,
686) -> ModelResult<Vec<Uuid>> {
687 let res = sqlx::query!(
688 "
689SELECT id
690FROM exercise_task_submissions
691WHERE exercise_slide_submission_id IN (SELECT id
692 FROM (SELECT DISTINCT ON (user_id, exercise_id) *
693 FROM exercise_slide_submissions
694 WHERE exercise_id = $1
695 AND deleted_at IS NULL
696 ORDER BY user_id, exercise_id, created_at DESC) a )
697 AND deleted_at IS NULL
698",
699 &exercise_id
700 )
701 .fetch_all(conn)
702 .await?;
703 Ok(res.iter().map(|x| x.id).collect())
704}