headless_lms_models/credit_registrations/
admin_view.rs

1//! The ledger as the admin explorer and the reconciliation detectors read it, across courses.
2
3use super::registration::{CreditRegistration, is_waiting_for_enrolment};
4use super::state::{CreditRegistrationErrorCode, CreditRegistrationState, ResubmissionFacts};
5use super::teacher_view::search_pattern_of;
6use crate::library::credit_registration::{CreditRegistrationPendingReason, PendingPreconditions};
7use crate::prelude::*;
8use crate::verified_student_numbers::StudentNumberVerificationMethod;
9
10/// One ledger row as an admin sees it: every identifier support needs to answer "what happened to
11/// this student", across courses.
12///
13/// Not the study registry's own error text: it is written for an integrator, may name a person and
14/// is untranslated. The error code and the scrubbed call bodies stand in for it.
15#[derive(Debug, Clone)]
16pub struct AdminCreditRegistration {
17    pub id: Uuid,
18    pub created_at: DateTime<Utc>,
19    pub user_id: Uuid,
20    pub first_name: Option<String>,
21    pub last_name: Option<String>,
22    /// In full: the admin view exists to resolve support cases, which starts from the address.
23    pub email: Option<String>,
24    pub course_id: Uuid,
25    pub course_name: String,
26    pub course_module_id: Uuid,
27    pub course_module_name: Option<String>,
28    pub course_instance_id: Uuid,
29    pub course_module_completion_id: Uuid,
30    pub completion_date: DateTime<Utc>,
31    pub state: CreditRegistrationState,
32    pub state_entered_at: DateTime<Utc>,
33    pub error_code: Option<CreditRegistrationErrorCode>,
34    pub needs_admin_attention: bool,
35    pub next_attempt_at: DateTime<Utc>,
36    pub last_attempt_at: Option<DateTime<Utc>>,
37    pub submitted_at: Option<DateTime<Utc>>,
38    pub registered_at: Option<DateTime<Utc>>,
39    pub terminal_at: Option<DateTime<Utc>>,
40    /// Frozen on the row when it left `checking_enrolment`, so it is what we actually sent.
41    pub student_number: Option<DbSecret>,
42    pub sisu_person_id: Option<DbSecret>,
43    pub uh_course_code: Option<String>,
44    pub selected_enrolment_id: Option<String>,
45    pub grade_scale_id: Option<String>,
46    pub grade_id: Option<String>,
47    pub credits: Option<f32>,
48    pub submitted_attainment_id: Option<String>,
49    pub sisu_attainment_id: Option<String>,
50    pub submit_retry_count: i32,
51    pub verify_attempt_count: i32,
52    pub attempt_number: i32,
53    pub superseded_by_id: Option<Uuid>,
54    /// The account's live link now, which may differ from the number frozen on the row.
55    pub verified_student_number: Option<DbSecret>,
56    pub verified_student_number_at: Option<DateTime<Utc>>,
57    pub verified_student_number_via: Option<StudentNumberVerificationMethod>,
58    pub resubmit_not_before: Option<DateTime<Utc>>,
59    pub partially_registered_at: Option<DateTime<Utc>>,
60    pub not_registered_reimport_count: i32,
61    pub no_usable_enrolment_since: Option<DateTime<Utc>>,
62    pub enrolment_checked_at: Option<DateTime<Utc>>,
63    pub enrolment_check_anchor_at: Option<DateTime<Utc>>,
64    pub enrolment_check_due_at: Option<DateTime<Utc>>,
65    pub enrolment_checks_stopped_at: Option<DateTime<Utc>>,
66    pub completion_eligible: bool,
67    pub has_verified_student_number: bool,
68    pub course_code_allowed: bool,
69    /// The page's total row count, so a caller can read it off the first row instead of a second
70    /// query.
71    pub total_count: i64,
72}
73
74impl AdminCreditRegistration {
75    /// What decides whether a human may move this row; see [`ResubmissionFacts`].
76    pub fn resubmission_facts(&self) -> ResubmissionFacts {
77        ResubmissionFacts {
78            state: self.state,
79            is_superseded: self.superseded_by_id.is_some(),
80            error_code: self.error_code,
81            resubmit_not_before: self.resubmit_not_before,
82            submitted_at: self.submitted_at,
83        }
84    }
85
86    /// See [`is_waiting_for_enrolment`].
87    pub fn is_waiting_for_enrolment(&self) -> bool {
88        is_waiting_for_enrolment(
89            self.state,
90            self.enrolment_check_anchor_at,
91            self.no_usable_enrolment_since,
92        )
93    }
94
95    /// What this row is waiting on, or `None` where it is not waiting at all: outside `pending` the
96    /// preconditions say nothing about why the row is where it is.
97    pub fn pending_reason(&self) -> Option<CreditRegistrationPendingReason> {
98        (self.state == CreditRegistrationState::Pending)
99            .then(|| {
100                PendingPreconditions {
101                    completion_eligible: self.completion_eligible,
102                    has_verified_student_number: self.has_verified_student_number,
103                    course_code_allowed: self.course_code_allowed,
104                }
105                .reason()
106            })
107            .flatten()
108    }
109}
110
111/// How the explorer orders a page. Descending only: an ops table is read newest-worst first.
112#[derive(Debug, Clone, Copy, PartialEq, Eq, Default)]
113pub enum AdminCreditRegistrationSort {
114    #[default]
115    LastActivity,
116    Created,
117    TimeInState,
118    Attempts,
119}
120
121impl AdminCreditRegistrationSort {
122    /// Bound into the query's `ORDER BY` as a `text` parameter.
123    fn as_str(self) -> &'static str {
124        match self {
125            Self::LastActivity => "last_activity",
126            Self::Created => "created",
127            Self::TimeInState => "time_in_state",
128            Self::Attempts => "attempts",
129        }
130    }
131}
132
133/// The narrowings the admin explorer applies, all of them in SQL.
134#[derive(Debug, Clone, Default)]
135pub struct AdminCreditRegistrationFilters<'a> {
136    pub states: Option<&'a [CreditRegistrationState]>,
137    pub error_codes: Option<&'a [CreditRegistrationErrorCode]>,
138    pub course_id: Option<Uuid>,
139    pub course_module_id: Option<Uuid>,
140    pub user_id: Option<Uuid>,
141    pub student_number: Option<&'a str>,
142    pub needs_admin_attention: bool,
143    pub submitted_after: Option<DateTime<Utc>>,
144    pub submitted_before: Option<DateTime<Utc>>,
145    /// Matched against the student's name and email, either student number, the attainment ids and
146    /// the stored error text. Searching that text is not rendering it.
147    pub search: Option<&'a str>,
148    /// A uuid typed into the search box: a registration, a user or a completion id. Ambiguous by
149    /// design, for a human's paste. A caller that already knows which single field it means should
150    /// use `id` or `course_module_completion_id` instead, not this plus a Rust-side filter.
151    pub search_id: Option<Uuid>,
152    /// Exactly one registration.
153    pub id: Option<Uuid>,
154    /// Every attempt against one completion.
155    pub course_module_completion_id: Option<Uuid>,
156    /// An exact set of rows, for a caller that already knows which ones it wants.
157    pub credit_registration_ids: Option<&'a [Uuid]>,
158    /// Off by default, or a course that regrades shows two rows per student.
159    pub include_superseded: bool,
160}
161
162/// The query behind [`get_admin_facing`]. `total_count` is computed before the limit, so every row
163/// of a page carries the count of the whole filtered ledger.
164async fn admin_facing_page(
165    conn: &mut PgConnection,
166    filters: &AdminCreditRegistrationFilters<'_>,
167    sort: AdminCreditRegistrationSort,
168    limit: i64,
169    offset: i64,
170) -> ModelResult<Vec<AdminCreditRegistration>> {
171    let search_pattern = filters.search.map(search_pattern_of);
172    let res = sqlx::query_as!(
173        AdminCreditRegistration,
174        r#"
175SELECT cr.id,
176  cr.created_at,
177  cr.user_id,
178  ud.first_name AS "first_name?",
179  ud.last_name AS "last_name?",
180  ud.email AS "email?",
181  cr.course_id,
182  c.name AS course_name,
183  cr.course_module_id,
184  cm.name AS course_module_name,
185  cr.course_instance_id,
186  cr.course_module_completion_id,
187  cmc.completion_date,
188  cr.state,
189  cr.state_entered_at,
190  cr.error_code AS "error_code?",
191  cr.needs_admin_attention,
192  cr.next_attempt_at,
193  cr.last_attempt_at,
194  cr.submitted_at,
195  cr.registered_at,
196  cr.terminal_at,
197  cr.student_number,
198  cr.sisu_person_id,
199  cr.uh_course_code,
200  cr.selected_enrolment_id,
201  cr.grade_scale_id,
202  cr.grade_id,
203  cr.credits,
204  cr.submitted_attainment_id,
205  cr.sisu_attainment_id,
206  cr.submit_retry_count,
207  cr.verify_attempt_count,
208  cr.attempt_number,
209  cr.superseded_by_id,
210  vsn.student_number AS "verified_student_number?",
211  vsn.verified_at AS "verified_student_number_at?",
212  vsn.verified_via AS "verified_student_number_via?",
213  cr.resubmit_not_before,
214  cr.partially_registered_at,
215  cr.not_registered_reimport_count,
216  cr.no_usable_enrolment_since,
217  cr.enrolment_checked_at,
218  cr.enrolment_check_anchor_at,
219  cr.enrolment_check_due_at,
220  cr.enrolment_checks_stopped_at,
221  p.completion_eligible AS "completion_eligible!",
222  p.has_verified_student_number AS "has_verified_student_number!",
223  p.course_code_allowed AS "course_code_allowed!",
224  COUNT(*) OVER () AS "total_count!"
225FROM credit_registrations cr
226  JOIN courses c ON c.id = cr.course_id
227  JOIN course_modules cm ON cm.id = cr.course_module_id
228  JOIN course_module_completions cmc ON cmc.id = cr.course_module_completion_id
229  JOIN credit_registration_preconditions p ON p.credit_registration_id = cr.id
230  LEFT JOIN user_details ud ON ud.user_id = cr.user_id
231  LEFT JOIN verified_student_numbers vsn ON vsn.user_id = cr.user_id
232  AND vsn.deleted_at IS NULL
233WHERE cr.deleted_at IS NULL
234  AND ($1::bool OR cr.superseded_by_id IS NULL)
235  AND (
236    $2::credit_registration_state [] IS NULL
237    OR cr.state = ANY($2)
238  )
239  AND (
240    $3::credit_registration_error_code [] IS NULL
241    OR cr.error_code = ANY($3)
242  )
243  AND ($4::uuid IS NULL OR cr.course_id = $4)
244  AND ($5::uuid IS NULL OR cr.course_module_id = $5)
245  AND ($6::uuid IS NULL OR cr.user_id = $6)
246  AND (
247    $7::text IS NULL
248    OR cr.student_number = $7
249    OR vsn.student_number = $7
250  )
251  AND (NOT $8::bool OR cr.needs_admin_attention)
252  AND ($9::timestamptz IS NULL OR cr.submitted_at >= $9)
253  AND ($10::timestamptz IS NULL OR cr.submitted_at <= $10)
254  AND (
255    $11::text IS NULL
256    OR ud.name_search_helper LIKE '%' || $11 || '%' ESCAPE '\'
257    OR ud.email_search_helper LIKE '%' || $11 || '%' ESCAPE '\'
258    OR LOWER(cr.student_number) LIKE '%' || $11 || '%' ESCAPE '\'
259    OR LOWER(vsn.student_number) LIKE '%' || $11 || '%' ESCAPE '\'
260    OR LOWER(cr.submitted_attainment_id) LIKE '%' || $11 || '%' ESCAPE '\'
261    OR LOWER(cr.sisu_attainment_id) LIKE '%' || $11 || '%' ESCAPE '\'
262    OR LOWER(cr.error_message) LIKE '%' || $11 || '%' ESCAPE '\'
263  )
264  AND (
265    $12::uuid IS NULL
266    OR cr.id = $12
267    OR cr.user_id = $12
268    OR cr.course_module_completion_id = $12
269  )
270  AND (
271    $13::uuid [] IS NULL
272    OR cr.id = ANY($13)
273  )
274  AND ($17::uuid IS NULL OR cr.id = $17)
275  AND (
276    $18::uuid IS NULL
277    OR cr.course_module_completion_id = $18
278  )
279ORDER BY CASE
280    WHEN $14::text = 'attempts' THEN cr.submit_retry_count + cr.verify_attempt_count
281  END DESC NULLS LAST,
282  CASE $14::text
283    WHEN 'created' THEN cr.created_at
284    WHEN 'time_in_state' THEN cr.state_entered_at
285    ELSE COALESCE(cr.last_attempt_at, cr.state_entered_at)
286  END DESC,
287  cr.id
288LIMIT $15 OFFSET $16
289        "#,
290        filters.include_superseded,
291        filters.states as Option<&[CreditRegistrationState]>,
292        filters.error_codes as Option<&[CreditRegistrationErrorCode]>,
293        filters.course_id,
294        filters.course_module_id,
295        filters.user_id,
296        filters.student_number,
297        filters.needs_admin_attention,
298        filters.submitted_after,
299        filters.submitted_before,
300        search_pattern.as_deref(),
301        filters.search_id,
302        filters.credit_registration_ids as Option<&[Uuid]>,
303        sort.as_str(),
304        limit,
305        offset,
306        filters.id,
307        filters.course_module_completion_id,
308    )
309    .fetch_all(conn)
310    .await?;
311    Ok(res)
312}
313
314/// A page of the ledger for the admin explorer, cross-course.
315pub async fn get_admin_facing(
316    conn: &mut PgConnection,
317    filters: &AdminCreditRegistrationFilters<'_>,
318    sort: AdminCreditRegistrationSort,
319    limit: i64,
320    offset: i64,
321) -> ModelResult<Vec<AdminCreditRegistration>> {
322    admin_facing_page(conn, filters, sort, limit, offset).await
323}
324
325/// Live rows in each of the given states, newest activity first within each state, for the
326/// Reconciliation lists. `limit_per_state` caps every state independently, via `ROW_NUMBER`, so one
327/// state with many rows cannot crowd another out of a shared `LIMIT`.
328pub async fn get_live_by_states(
329    conn: &mut PgConnection,
330    states: &[CreditRegistrationState],
331    limit_per_state: i64,
332) -> ModelResult<Vec<CreditRegistration>> {
333    let res = sqlx::query_as!(
334        CreditRegistration,
335        r#"
336SELECT cr.*
337FROM credit_registrations cr
338  JOIN (
339    SELECT id,
340      ROW_NUMBER() OVER (
341        PARTITION BY state
342        ORDER BY state_entered_at DESC
343      ) AS rn
344    FROM credit_registrations
345    WHERE state = ANY($1)
346      AND superseded_by_id IS NULL
347      AND deleted_at IS NULL
348  ) ranked ON ranked.id = cr.id
349WHERE ranked.rn <= $2
350ORDER BY cr.state_entered_at DESC
351        "#,
352        states as &[CreditRegistrationState],
353        limit_per_state,
354    )
355    .fetch_all(conn)
356    .await?;
357    Ok(res)
358}
headless_lms_models/credit_registrations/admin_view.rs

headless_lms_models/credit_registrations/
admin_view.rs
