headless_lms_models/
course_module_completion_registered_to_study_registries.rs

1use crate::{course_module_completions, error::missing_model_error, prelude::*};
2
3#[derive(Clone, PartialEq, Deserialize, Serialize)]
4pub struct CourseModuleCompletionRegisteredToStudyRegistry {
5    pub id: Uuid,
6    pub created_at: DateTime<Utc>,
7    pub updated_at: DateTime<Utc>,
8    pub deleted_at: Option<DateTime<Utc>>,
9    pub course_id: Uuid,
10    pub course_module_completion_id: Uuid,
11    pub course_module_id: Uuid,
12    /// Null when this platform registered the attainment itself instead of a third-party registrar.
13    pub study_registry_registrar_id: Option<Uuid>,
14    pub user_id: Uuid,
15    pub real_student_number: String,
16}
17
18#[derive(Clone, PartialEq, Deserialize, Serialize)]
19pub struct NewCourseModuleCompletionRegisteredToStudyRegistry {
20    pub course_id: Uuid,
21    pub course_module_completion_id: Uuid,
22    pub course_module_id: Uuid,
23    pub study_registry_registrar_id: Uuid,
24    pub user_id: Uuid,
25    pub real_student_number: String,
26}
27
28pub async fn insert(
29    conn: &mut PgConnection,
30    pkey_policy: PKeyPolicy<Uuid>,
31    new_completion_registration: &NewCourseModuleCompletionRegisteredToStudyRegistry,
32) -> ModelResult<Uuid> {
33    let res = sqlx::query!(
34        "
35INSERT INTO course_module_completion_registered_to_study_registries (
36    id,
37    course_id,
38    course_module_completion_id,
39    course_module_id,
40    study_registry_registrar_id,
41    user_id,
42    real_student_number
43  )
44VALUES (
45    $1,
46    $2,
47    $3,
48    $4,
49    $5,
50    $6,
51    $7
52)
53RETURNING *
54        ",
55        pkey_policy.into_uuid(),
56        new_completion_registration.course_id,
57        new_completion_registration.course_module_completion_id,
58        new_completion_registration.course_module_id,
59        new_completion_registration.study_registry_registrar_id,
60        new_completion_registration.user_id,
61        new_completion_registration.real_student_number,
62    )
63    .fetch_one(conn)
64    .await?;
65    Ok(res.id)
66}
67
68/// Like [insert], but a pre-existing registration for the same completion is left untouched
69/// instead of erroring, for seeding scripts that may rerun against already-seeded data.
70pub async fn insert_or_ignore(
71    conn: &mut PgConnection,
72    new_completion_registration: &NewCourseModuleCompletionRegisteredToStudyRegistry,
73) -> ModelResult<()> {
74    sqlx::query!(
75        "
76INSERT INTO course_module_completion_registered_to_study_registries (
77    course_id,
78    course_module_completion_id,
79    course_module_id,
80    study_registry_registrar_id,
81    user_id,
82    real_student_number
83  )
84VALUES ($1, $2, $3, $4, $5, $6)
85ON CONFLICT DO NOTHING
86        ",
87        new_completion_registration.course_id,
88        new_completion_registration.course_module_completion_id,
89        new_completion_registration.course_module_id,
90        new_completion_registration.study_registry_registrar_id,
91        new_completion_registration.user_id,
92        new_completion_registration.real_student_number,
93    )
94    .execute(conn)
95    .await?;
96    Ok(())
97}
98
99pub async fn insert_bulk(
100    conn: &mut PgConnection,
101    new_completion_registrations: Vec<NewCourseModuleCompletionRegisteredToStudyRegistry>,
102) -> ModelResult<Vec<Uuid>> {
103    if new_completion_registrations.is_empty() {
104        return Ok(vec![]);
105    }
106
107    let ids: Vec<Uuid> = (0..new_completion_registrations.len())
108        .map(|_| Uuid::new_v4())
109        .collect();
110    let course_ids: Vec<Uuid> = new_completion_registrations
111        .iter()
112        .map(|r| r.course_id)
113        .collect();
114    let completion_ids: Vec<Uuid> = new_completion_registrations
115        .iter()
116        .map(|r| r.course_module_completion_id)
117        .collect();
118    let module_ids: Vec<Uuid> = new_completion_registrations
119        .iter()
120        .map(|r| r.course_module_id)
121        .collect();
122    let registrar_ids: Vec<Uuid> = new_completion_registrations
123        .iter()
124        .map(|r| r.study_registry_registrar_id)
125        .collect();
126    let user_ids: Vec<Uuid> = new_completion_registrations
127        .iter()
128        .map(|r| r.user_id)
129        .collect();
130    let student_numbers: Vec<String> = new_completion_registrations
131        .iter()
132        .map(|r| r.real_student_number.clone())
133        .collect();
134
135    let res = sqlx::query!(
136        r#"
137INSERT INTO course_module_completion_registered_to_study_registries (
138    id,
139    course_id,
140    course_module_completion_id,
141    course_module_id,
142    study_registry_registrar_id,
143    user_id,
144    real_student_number
145)
146SELECT * FROM UNNEST(
147    $1::uuid[],
148    $2::uuid[],
149    $3::uuid[],
150    $4::uuid[],
151    $5::uuid[],
152    $6::uuid[],
153    $7::text[]
154)
155-- Matches cmc_registered_to_study_registries_completion_registrar_idx, so a registrar's repeated
156-- POST for a completion it already registered stays idempotent instead of erroring.
157ON CONFLICT (course_module_completion_id, study_registry_registrar_id) WHERE deleted_at IS NULL DO NOTHING
158RETURNING *
159        "#,
160        &ids[..],
161        &course_ids[..],
162        &completion_ids[..],
163        &module_ids[..],
164        &registrar_ids[..],
165        &user_ids[..],
166        &student_numbers[..],
167    )
168    .fetch_all(conn)
169    .await?;
170
171    Ok(res.into_iter().map(|r| r.id).collect())
172}
173
174#[derive(Clone, PartialEq, Eq, Deserialize, Serialize, Debug)]
175/// An object representing that a completion has been registered to a study registry.
176pub struct RegisteredCompletion {
177    /// Id of the completion that was registered to the study registry.
178    pub completion_id: Uuid,
179    /// The student number the completion was registed to.
180    pub student_number: String,
181    /// The registration date that is visible in the study registry for the user.
182    pub registration_date: DateTime<Utc>,
183}
184
185pub async fn mark_completions_as_registered_to_study_registry(
186    conn: &mut PgConnection,
187    completions: Vec<RegisteredCompletion>,
188    study_registry_registrar_id: Uuid,
189) -> ModelResult<()> {
190    if completions.is_empty() {
191        return Ok(());
192    }
193
194    let ids: Vec<Uuid> = completions.iter().map(|x| x.completion_id).collect();
195    let completions_by_id = course_module_completions::get_by_ids_as_map(conn, &ids).await?;
196
197    let mut new_registrations = Vec::with_capacity(completions.len());
198    for completion in completions {
199        let module_completion = completions_by_id
200            .get(&completion.completion_id)
201            .ok_or_else(missing_model_error(
202                ModelErrorType::PreconditionFailed,
203                format!(
204                    "Cannot find completion with id: {}. This completion does not exist in the database.",
205                    completion.completion_id
206                ),
207            ))?;
208        new_registrations.push(NewCourseModuleCompletionRegisteredToStudyRegistry {
209            course_id: module_completion.course_id,
210            course_module_completion_id: completion.completion_id,
211            course_module_id: module_completion.course_module_id,
212            study_registry_registrar_id,
213            user_id: module_completion.user_id,
214            real_student_number: completion.student_number,
215        });
216    }
217
218    let user_ids: Vec<Uuid> = new_registrations.iter().map(|r| r.user_id).collect();
219    let mut tx = conn.begin().await?;
220    insert_bulk(&mut tx, new_registrations).await?;
221    // A savepoint, so a linking failure is rolled back alone and the registrar's POST still lands.
222    let mut savepoint = tx.begin().await?;
223    match crate::verified_student_numbers::link_numbers_reported_by_study_registry(
224        &mut savepoint,
225        &user_ids,
226    )
227    .await
228    {
229        Ok(_) => savepoint.commit().await?,
230        Err(error) => {
231            warn!("Could not link student numbers the study registry reported: {error}");
232            savepoint.rollback().await?;
233        }
234    }
235    tx.commit().await?;
236
237    Ok(())
238}
239
240pub async fn get_by_id(
241    conn: &mut PgConnection,
242    id: Uuid,
243) -> ModelResult<CourseModuleCompletionRegisteredToStudyRegistry> {
244    let res = sqlx::query_as!(
245        CourseModuleCompletionRegisteredToStudyRegistry,
246        "
247SELECT *
248FROM course_module_completion_registered_to_study_registries
249WHERE id = $1
250  AND deleted_at IS NULL
251        ",
252        id,
253    )
254    .fetch_one(conn)
255    .await?;
256    Ok(res)
257}
258
259/// The row this platform registered itself for a completion, as opposed to one filed by a
260/// third-party registrar: a null `study_registry_registrar_id` is what marks a row as ours.
261pub async fn get_platform_registered_row_for_completion(
262    conn: &mut PgConnection,
263    completion_id: Uuid,
264) -> ModelResult<Option<CourseModuleCompletionRegisteredToStudyRegistry>> {
265    let res = sqlx::query_as!(
266        CourseModuleCompletionRegisteredToStudyRegistry,
267        "
268SELECT *
269FROM course_module_completion_registered_to_study_registries
270WHERE course_module_completion_id = $1
271  AND study_registry_registrar_id IS NULL
272  AND deleted_at IS NULL
273        ",
274        completion_id,
275    )
276    .fetch_optional(conn)
277    .await?;
278    Ok(res)
279}
280
281pub async fn delete(conn: &mut PgConnection, id: Uuid) -> ModelResult<()> {
282    sqlx::query!(
283        "
284UPDATE course_module_completion_registered_to_study_registries
285SET deleted_at = now()
286WHERE id = $1
287AND deleted_at IS NULL
288        ",
289        id
290    )
291    .execute(conn)
292    .await?;
293    Ok(())
294}
295
296/// Get the number of students that have completed the course
297pub async fn get_count_of_distinct_users_with_registrations_by_course_id(
298    conn: &mut PgConnection,
299    course_id: Uuid,
300) -> ModelResult<i64> {
301    let res = sqlx::query!(
302        "
303SELECT COUNT(DISTINCT user_id) as count
304FROM course_module_completion_registered_to_study_registries
305WHERE course_id = $1
306  AND deleted_at IS NULL
307",
308        course_id,
309    )
310    .fetch_one(conn)
311    .await?;
312    Ok(res.count.unwrap_or(0))
313}
314
315pub async fn get_by_completion_id_and_registrar_id(
316    conn: &mut PgConnection,
317    completion_id: Uuid,
318    study_registry_registrar_id: Uuid,
319) -> ModelResult<Vec<CourseModuleCompletionRegisteredToStudyRegistry>> {
320    let registrations = sqlx::query_as!(
321        CourseModuleCompletionRegisteredToStudyRegistry,
322        r#"
323        SELECT *
324        FROM course_module_completion_registered_to_study_registries
325        WHERE course_module_completion_id = $1 AND study_registry_registrar_id = $2
326        AND deleted_at IS NULL
327        "#,
328        completion_id,
329        study_registry_registrar_id
330    )
331    .fetch_all(conn)
332    .await?;
333
334    Ok(registrations)
335}
336
337/// Returns non-deleted registrations for a registrar scoped to the given completion ids.
338pub async fn get_by_registrar_id_and_completion_ids(
339    conn: &mut PgConnection,
340    study_registry_registrar_id: Uuid,
341    completion_ids: &[Uuid],
342) -> ModelResult<Vec<CourseModuleCompletionRegisteredToStudyRegistry>> {
343    let registrations = sqlx::query_as!(
344        CourseModuleCompletionRegisteredToStudyRegistry,
345        r#"
346SELECT *
347FROM course_module_completion_registered_to_study_registries
348WHERE study_registry_registrar_id = $1
349  AND course_module_completion_id = ANY($2)
350  AND deleted_at IS NULL
351        "#,
352        study_registry_registrar_id,
353        completion_ids
354    )
355    .fetch_all(conn)
356    .await?;
357
358    Ok(registrations)
359}
360
361#[derive(Clone, PartialEq, Deserialize, Serialize)]
362/// A registration's state without the registrar-supplied student number, for surfaces that must
363/// never expose it (see `real_student_number` on [CourseModuleCompletionRegisteredToStudyRegistry]).
364pub struct CourseModuleCompletionRegistrationState {
365    pub course_module_completion_id: Uuid,
366    pub course_module_id: Uuid,
367    pub study_registry_registrar_id: Option<Uuid>,
368    pub created_at: DateTime<Utc>,
369}
370
371/// Registration state for the given completions, across all registrars. Selects no student
372/// number, unlike [get_by_registrar_id_and_completion_ids].
373pub async fn get_registrations_by_completion_ids(
374    conn: &mut PgConnection,
375    completion_ids: &[Uuid],
376) -> ModelResult<Vec<CourseModuleCompletionRegistrationState>> {
377    let registrations = sqlx::query_as!(
378        CourseModuleCompletionRegistrationState,
379        r#"
380SELECT course_module_completion_id,
381  course_module_id,
382  study_registry_registrar_id,
383  created_at
384FROM course_module_completion_registered_to_study_registries
385WHERE course_module_completion_id = ANY($1)
386  AND deleted_at IS NULL
387        "#,
388        completion_ids
389    )
390    .fetch_all(conn)
391    .await?;
392
393    Ok(registrations)
394}
395
396/// Of the given completions, the ones some registrar has already registered.
397///
398/// Rows this platform registered itself carry no registrar and are not counted, so a grade
399/// improvement's second submission stays allowed.
400pub async fn completion_ids_registered_by_a_registrar(
401    conn: &mut PgConnection,
402    completion_ids: &[Uuid],
403) -> ModelResult<Vec<Uuid>> {
404    let ids = sqlx::query_scalar!(
405        r#"
406SELECT DISTINCT course_module_completion_id
407FROM course_module_completion_registered_to_study_registries
408WHERE course_module_completion_id = ANY($1::uuid [])
409  AND study_registry_registrar_id IS NOT NULL
410  AND deleted_at IS NULL
411        "#,
412        completion_ids,
413    )
414    .fetch_all(conn)
415    .await?;
416    Ok(ids)
417}
418
419#[cfg(test)]
420mod test {
421    use super::*;
422    use crate::{course_module_completions::CourseModuleCompletionGranter, test_helper::*};
423
424    #[tokio::test]
425    async fn bulk_insert_works() {
426        insert_data!(:tx, :user, :org, :course, instance: _instance, :course_module);
427
428        let registrar_id = crate::study_registry_registrars::insert(
429            tx.as_mut(),
430            PKeyPolicy::Generate,
431            "Test Registrar",
432            "test_123131231231231231231231231231238971283718927389172893718923712893129837189273891278317892378193971289",
433        )
434        .await
435        .unwrap();
436
437        let completion_1 = crate::course_module_completions::insert(
438            tx.as_mut(),
439            PKeyPolicy::Generate,
440            &crate::course_module_completions::NewCourseModuleCompletion {
441                course_id: course,
442                course_module_id: course_module.id,
443                user_id: user,
444                completion_date: Utc::now(),
445                completion_registration_attempt_date: None,
446                completion_language: "en-US".to_string(),
447                eligible_for_ects: true,
448                email: "test@example.com".to_string(),
449                grade: Some(10),
450                passed: true,
451            },
452            CourseModuleCompletionGranter::User(user),
453        )
454        .await
455        .unwrap();
456        let completion_2 = crate::course_module_completions::insert(
457            tx.as_mut(),
458            PKeyPolicy::Generate,
459            &crate::course_module_completions::NewCourseModuleCompletion {
460                course_id: course,
461                course_module_id: course_module.id,
462                user_id: user,
463                completion_date: Utc::now(),
464                completion_registration_attempt_date: None,
465                completion_language: "en-US".to_string(),
466                eligible_for_ects: true,
467                email: "test2@example.com".to_string(),
468                grade: Some(9),
469                passed: true,
470            },
471            CourseModuleCompletionGranter::User(user),
472        )
473        .await
474        .unwrap();
475
476        let registrations = vec![
477            NewCourseModuleCompletionRegisteredToStudyRegistry {
478                course_id: course,
479                course_module_completion_id: completion_1.id,
480                course_module_id: course_module.id,
481                study_registry_registrar_id: registrar_id,
482                user_id: user,
483                real_student_number: "12345".to_string(),
484            },
485            NewCourseModuleCompletionRegisteredToStudyRegistry {
486                course_id: course,
487                course_module_completion_id: completion_2.id,
488                course_module_id: course_module.id,
489                study_registry_registrar_id: registrar_id,
490                user_id: user,
491                real_student_number: "67890".to_string(),
492            },
493        ];
494
495        let inserted_ids = insert_bulk(tx.as_mut(), registrations).await.unwrap();
496        assert_eq!(inserted_ids.len(), 2);
497
498        let mut registered_completion_ids = Vec::new();
499        for id in inserted_ids {
500            let registration = get_by_id(tx.as_mut(), id).await.unwrap();
501            assert_eq!(registration.course_id, course);
502            assert_eq!(registration.course_module_id, course_module.id);
503            assert_eq!(registration.study_registry_registrar_id, Some(registrar_id));
504            assert_eq!(registration.user_id, user);
505            registered_completion_ids.push(registration.course_module_completion_id);
506        }
507        registered_completion_ids.sort();
508        let mut expected = vec![completion_1.id, completion_2.id];
509        expected.sort();
510        assert_eq!(registered_completion_ids, expected);
511    }
512
513    #[tokio::test]
514    async fn bulk_insert_empty_vec_works() {
515        insert_data!(:tx);
516
517        let empty_vec = vec![];
518        let result = insert_bulk(tx.as_mut(), empty_vec).await.unwrap();
519        assert!(result.is_empty());
520    }
521
522    #[tokio::test]
523    async fn insert_completions_works() {
524        insert_data!(:tx, :user, :org, :course, instance: _instance, :course_module);
525
526        let registrar_id = crate::study_registry_registrars::insert(
527            tx.as_mut(),
528            PKeyPolicy::Generate,
529            "Test Registrar",
530            "test_123131231231231231231231231231238971283718927389172893718923712893129837189273891278317892378193971289",
531        )
532        .await
533        .unwrap();
534
535        let completion = crate::course_module_completions::insert(
536            tx.as_mut(),
537            PKeyPolicy::Generate,
538            &crate::course_module_completions::NewCourseModuleCompletion {
539                course_id: course,
540                course_module_id: course_module.id,
541                user_id: user,
542                completion_date: Utc::now(),
543                completion_registration_attempt_date: None,
544                completion_language: "en-US".to_string(),
545                eligible_for_ects: true,
546                email: "test@example.com".to_string(),
547                grade: Some(5),
548                passed: true,
549            },
550            CourseModuleCompletionGranter::User(user),
551        )
552        .await
553        .unwrap();
554
555        let registered_completions = vec![RegisteredCompletion {
556            completion_id: completion.id,
557            student_number: "12345".to_string(),
558            registration_date: Utc::now(),
559        }];
560
561        mark_completions_as_registered_to_study_registry(
562            tx.as_mut(),
563            registered_completions,
564            registrar_id,
565        )
566        .await
567        .unwrap();
568
569        let registrations =
570            get_by_completion_id_and_registrar_id(tx.as_mut(), completion.id, registrar_id)
571                .await
572                .unwrap();
573
574        assert_eq!(registrations.len(), 1);
575        assert_eq!(registrations[0].course_id, course);
576        assert_eq!(registrations[0].course_module_id, course_module.id);
577        assert_eq!(registrations[0].user_id, user);
578        assert_eq!(registrations[0].real_student_number, "12345");
579    }
580
581    #[tokio::test]
582    async fn insert_completions_with_invalid_completion_id_fails() {
583        insert_data!(:tx);
584
585        let registrar_id = crate::study_registry_registrars::insert(
586            tx.as_mut(),
587            PKeyPolicy::Generate,
588            "Test Registrar",
589            "test_123131231231231231231231231231238971283718927389172893718923712893129837189273891278317892378193971289",
590        )
591        .await
592        .unwrap();
593
594        let invalid_uuid = Uuid::new_v4(); // This UUID doesn't correspond to any completion
595        let registered_completions = vec![RegisteredCompletion {
596            completion_id: invalid_uuid,
597            student_number: "12345".to_string(),
598            registration_date: Utc::now(),
599        }];
600
601        // Attempt to insert the completions should fail
602        let result = mark_completions_as_registered_to_study_registry(
603            tx.as_mut(),
604            registered_completions,
605            registrar_id,
606        )
607        .await;
608
609        assert!(result.is_err());
610        let error = result.unwrap_err();
611        assert_eq!(*error.error_type(), ModelErrorType::PreconditionFailed);
612        assert!(error.message().contains("Cannot find completion with id"));
613        assert!(error.message().contains(&invalid_uuid.to_string()));
614    }
615}
headless_lms_models/course_module_completion_registered_to_study_registries.rs

headless_lms_models/
course_module_completion_registered_to_study_registries.rs
