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