headless_lms_models/
study_registry_student_number_conflicts.rs

1//! Student numbers a registrar reported that could not become a link because another stood in the way.
2
3use crate::prelude::*;
4use crate::verified_student_numbers::StudentNumberVerificationMethod;
5
6/// A conflict that still holds: the account does not hold the reported number.
7#[derive(Debug, Clone)]
8pub struct UnresolvedStudyRegistryConflict {
9    pub id: Uuid,
10    pub created_at: DateTime<Utc>,
11    pub user_id: Uuid,
12    pub user_email: Option<String>,
13    pub first_name: Option<String>,
14    pub last_name: Option<String>,
15    pub reported_student_number: DbSecret,
16    pub course_id: Uuid,
17    pub course_name: String,
18    pub conflicting_link_user_id: Uuid,
19    pub conflicting_link_user_email: Option<String>,
20    pub conflicting_link_student_number: DbSecret,
21    pub conflicting_link_verified_via: StudentNumberVerificationMethod,
22}
23
24/// How many conflicts still hold.
25pub async fn count_unresolved(conn: &mut PgConnection) -> ModelResult<i64> {
26    let count = sqlx::query_scalar!(
27        r#"
28SELECT COUNT(*) AS "count!"
29FROM study_registry_student_number_conflicts c
30WHERE c.deleted_at IS NULL
31  AND NOT EXISTS (
32    SELECT 1
33    FROM verified_student_numbers vsn
34    WHERE vsn.user_id = c.user_id
35      AND vsn.student_number = c.student_number
36      AND vsn.deleted_at IS NULL
37  )
38        "#
39    )
40    .fetch_one(conn)
41    .await?;
42    Ok(count)
43}
44
45/// Conflicts that still hold, newest first.
46pub async fn get_unresolved(
47    conn: &mut PgConnection,
48    limit: i64,
49) -> ModelResult<Vec<UnresolvedStudyRegistryConflict>> {
50    let rows = sqlx::query_as!(
51        UnresolvedStudyRegistryConflict,
52        r#"
53SELECT c.id,
54  c.created_at,
55  c.user_id,
56  ud.email AS "user_email?",
57  ud.first_name AS "first_name?",
58  ud.last_name AS "last_name?",
59  c.student_number AS reported_student_number,
60  r.course_id,
61  co.name AS course_name,
62  vsn.user_id AS conflicting_link_user_id,
63  holder.email AS "conflicting_link_user_email?",
64  vsn.student_number AS conflicting_link_student_number,
65  vsn.verified_via AS conflicting_link_verified_via
66FROM study_registry_student_number_conflicts c
67  JOIN course_module_completion_registered_to_study_registries r ON r.id = c.registered_completion_id
68  JOIN courses co ON co.id = r.course_id
69  JOIN verified_student_numbers vsn ON vsn.id = c.conflicting_verified_student_number_id
70  LEFT JOIN user_details ud ON ud.user_id = c.user_id
71  LEFT JOIN user_details holder ON holder.user_id = vsn.user_id
72WHERE c.deleted_at IS NULL
73  AND NOT EXISTS (
74    SELECT 1
75    FROM verified_student_numbers held
76    WHERE held.user_id = c.user_id
77      AND held.student_number = c.student_number
78      AND held.deleted_at IS NULL
79  )
80ORDER BY c.created_at DESC,
81  c.id
82LIMIT $1
83        "#,
84        limit,
85    )
86    .fetch_all(conn)
87    .await?;
88    Ok(rows)
89}
90
91/// Records that a registrar-reported link could not take its Sisu person because `blocking_link_id`,
92/// another account's live link, already holds that person. Nothing is recorded unless `link_id`'s
93/// number is still the latest one a registrar reported for its account.
94pub async fn record_person_conflict(
95    conn: &mut PgConnection,
96    link_id: Uuid,
97    blocking_link_id: Uuid,
98) -> ModelResult<()> {
99    sqlx::query!(
100        r#"
101INSERT INTO study_registry_student_number_conflicts (
102    user_id,
103    student_number,
104    registered_completion_id,
105    conflicting_verified_student_number_id
106  )
107SELECT reported.user_id,
108  reported.student_number,
109  reported.registered_completion_id,
110  $2
111FROM verified_student_numbers link
112  JOIN study_registry_reported_student_numbers reported ON reported.user_id = link.user_id
113  AND reported.student_number = link.student_number
114WHERE link.id = $1
115ON CONFLICT DO NOTHING
116        "#,
117        link_id,
118        blocking_link_id,
119    )
120    .execute(conn)
121    .await?;
122    Ok(())
123}
headless_lms_models/study_registry_student_number_conflicts.rs

headless_lms_models/
study_registry_student_number_conflicts.rs
