headless_lms_models/library/
students_view.rs

1//! Contains helper functions needed for student view
2use crate::chapters::{
3    self, ChapterAvailability, CourseUserInfo, DatabaseChapter, UserChapterProgress,
4};
5use crate::prelude::*;
6use crate::user_chapter_locking_statuses::UserChapterLockingStatus;
7use crate::user_details::UserDetail;
8use crate::user_exercise_states::UserExerciseState;
9use chrono::{DateTime, Utc};
10use utoipa::ToSchema;
11
12#[derive(Clone, PartialEq, Deserialize, Serialize, ToSchema)]
13
14pub struct ProgressOverview {
15    pub chapter_locking_enabled: bool,
16    pub user_details: Vec<UserDetail>,
17    pub chapters: Vec<DatabaseChapter>,
18    pub user_exercise_states: Vec<UserExerciseState>,
19    pub chapter_availability: Vec<ChapterAvailability>,
20    pub user_chapter_progress: Vec<UserChapterProgress>,
21    pub user_chapter_locking_statuses: Vec<UserChapterLockingStatus>,
22}
23
24pub async fn get_progress(
25    conn: &mut PgConnection,
26    course_id: Uuid,
27) -> ModelResult<ProgressOverview> {
28    let course = crate::courses::get_course(conn, course_id).await?;
29    let user_details = crate::user_details::get_users_by_course_id(conn, course_id).await?;
30    let chapters = crate::chapters::course_chapters(conn, course_id).await?;
31    let user_exercise_states =
32        crate::user_exercise_states::get_all_for_course(conn, course_id).await?;
33    let chapter_availability = chapters::fetch_chapter_availability(conn, course_id).await?;
34    let user_chapter_progress = chapters::fetch_user_chapter_progress(conn, course_id).await?;
35    let user_chapter_locking_statuses =
36        crate::user_chapter_locking_statuses::get_all_for_course(conn, &course).await?;
37
38    Ok(ProgressOverview {
39        chapter_locking_enabled: course.chapter_locking_enabled,
40        user_details,
41        chapters,
42        user_exercise_states,
43        chapter_availability,
44        user_chapter_progress,
45        user_chapter_locking_statuses,
46    })
47}
48
49pub async fn get_course_users(
50    conn: &mut PgConnection,
51    course_id: Uuid,
52) -> ModelResult<Vec<CourseUserInfo>> {
53    let rows = chapters::fetch_course_users(conn, course_id).await?;
54    Ok(rows)
55}
56
57#[derive(Clone, PartialEq, Deserialize, Serialize, sqlx::FromRow, ToSchema)]
58
59pub struct CompletionGridRow {
60    pub student: String,
61    pub module: Option<String>, // empty/default row can be None
62    pub grade: String,          // "-", "Passed", "Failed", or number as text
63    pub status: String,         // "Registered" | "-"
64    pub needs_to_be_reviewed: bool,
65}
66
67/// Returns student × module grid with latest completion per (user,module),
68/// formatted for the Completions tab.
69pub async fn get_completions_grid_by_course_id(
70    conn: &mut PgConnection,
71    course_id: Uuid,
72) -> ModelResult<Vec<CompletionGridRow>> {
73    let rows_raw = sqlx::query!(
74        r#"
75WITH modules AS (
76  SELECT id AS module_id, name AS module_name, order_number
77  FROM course_modules
78  WHERE course_id = $1
79    AND deleted_at IS NULL
80),
81enrolled AS (
82  SELECT DISTINCT user_id
83  FROM course_instance_enrollments
84  WHERE course_id = $1
85    AND deleted_at IS NULL
86),
87latest_cmc AS (
88  SELECT DISTINCT ON (cmc.user_id, cmc.course_module_id)
89    cmc.id,
90    cmc.user_id,
91    cmc.course_module_id,
92    cmc.grade,
93    cmc.passed,
94    cmc.completion_date,
95    cmc.needs_to_be_reviewed
96  FROM course_module_completions cmc
97  WHERE cmc.course_id = $1
98    AND cmc.deleted_at IS NULL
99  ORDER BY cmc.user_id, cmc.course_module_id, cmc.completion_date DESC
100),
101cmcr AS (
102  SELECT course_module_completion_id
103  FROM course_module_completion_registered_to_study_registries
104  WHERE course_id = $1
105    AND deleted_at IS NULL
106)
107SELECT
108  ud.first_name AS "first_name?",
109  ud.last_name AS "last_name?",
110  m.module_name AS "module_name?",
111  r.grade AS "grade?",
112  r.passed AS "passed?",
113  r.needs_to_be_reviewed AS "needs_to_be_reviewed?",
114  (r.id IS NOT NULL AND r.id IN (SELECT course_module_completion_id FROM cmcr)) AS "is_registered?"
115FROM modules m
116CROSS JOIN enrolled e
117JOIN users u ON u.id = e.user_id
118LEFT JOIN user_details ud ON ud.user_id = u.id
119LEFT JOIN latest_cmc r
120  ON r.user_id = e.user_id
121 AND r.course_module_id = m.module_id
122"#,
123        course_id
124    )
125    .fetch_all(conn)
126    .await?;
127
128    let rows = rows_raw
129        .into_iter()
130        .map(|r| {
131            let first = r.first_name.unwrap_or_default().trim().to_string();
132            let last = r.last_name.unwrap_or_default().trim().to_string();
133
134            let student = if first.is_empty() && last.is_empty() {
135                "(Missing name)".to_string()
136            } else {
137                format!("{first} {last}").trim().to_string()
138            };
139
140            let grade = match (r.grade, r.passed) {
141                (Some(g), _) => g.to_string(),
142                (None, Some(true)) => "Passed".to_string(),
143                (None, Some(false)) => "Failed".to_string(),
144                (None, None) => "-".to_string(),
145            };
146
147            let status = if r.is_registered.unwrap_or(false) {
148                "Registered".to_string()
149            } else {
150                "-".to_string()
151            };
152
153            CompletionGridRow {
154                student,
155                module: r.module_name,
156                grade,
157                status,
158                needs_to_be_reviewed: r.needs_to_be_reviewed.unwrap_or(false),
159            }
160        })
161        .collect();
162
163    Ok(rows)
164}
165
166#[derive(Clone, PartialEq, Deserialize, Serialize, sqlx::FromRow, ToSchema)]
167
168pub struct CertificateGridRow {
169    pub student: String,
170    pub certificate: String,
171    pub date_issued: Option<DateTime<Utc>>,
172    pub verification_id: Option<String>,
173    pub certificate_id: Option<Uuid>,
174    pub name_on_certificate: Option<String>,
175}
176
177/// Returns one row per enrolled student with their overall course certificate info.
178pub async fn get_certificates_grid_by_course_id(
179    conn: &mut PgConnection,
180    course_id: Uuid,
181) -> ModelResult<Vec<CertificateGridRow>> {
182    let rows = sqlx::query_as!(
183        CertificateGridRow,
184        r#"
185WITH enrolled AS (
186  SELECT DISTINCT user_id
187  FROM course_instance_enrollments
188  WHERE course_id = $1
189    AND deleted_at IS NULL
190),
191user_certs AS (
192  -- one latest certificate per user for this course
193  SELECT DISTINCT ON (gc.user_id)
194    gc.user_id,
195    gc.id,
196    gc.created_at AS latest_issued_at,
197    gc.verification_id,
198    gc.name_on_certificate
199  FROM generated_certificates gc
200  JOIN certificate_configuration_to_requirements cctr
201    ON gc.certificate_configuration_id = cctr.certificate_configuration_id
202   AND cctr.deleted_at IS NULL
203  JOIN course_modules cm
204    ON cm.id = cctr.course_module_id
205   AND cm.deleted_at IS NULL
206  WHERE cm.course_id = $1
207    AND gc.deleted_at IS NULL
208  ORDER BY gc.user_id, gc.created_at DESC
209)
210SELECT
211  /* non-null */
212  CASE
213    WHEN ud.first_name IS NULL OR TRIM(ud.first_name) = ''
214      OR ud.last_name  IS NULL OR TRIM(ud.last_name)  = ''
215    THEN '(Missing name)'
216    ELSE TRIM(ud.first_name) || ' ' || TRIM(ud.last_name)
217  END AS "student!",
218
219  /* non-null */
220  CASE
221    WHEN uc.user_id IS NOT NULL THEN 'Course Certificate'
222    ELSE 'No Certificate'
223  END AS "certificate!",
224
225  /* nullable */
226  uc.latest_issued_at AS "date_issued?",
227  uc.verification_id  AS "verification_id?",
228  uc.id               AS "certificate_id?",
229  uc.name_on_certificate AS "name_on_certificate?"
230
231FROM enrolled e
232JOIN users u ON u.id = e.user_id
233LEFT JOIN user_details ud ON ud.user_id = u.id
234LEFT JOIN user_certs uc ON uc.user_id = e.user_id
235ORDER BY
236  COALESCE(NULLIF(LOWER(TRIM(ud.last_name)),  ''), 'zzzzzz'),
237  COALESCE(NULLIF(LOWER(TRIM(ud.first_name)), ''), 'zzzzzz')
238        "#,
239        course_id
240    )
241    .fetch_all(conn)
242    .await?;
243
244    Ok(rows)
245}
headless_lms_models/library/students_view.rs

headless_lms_models/library/
students_view.rs
