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}
65
66/// Returns student × module grid with latest completion per (user,module),
67/// formatted for the Completions tab.
68pub async fn get_completions_grid_by_course_id(
69    conn: &mut PgConnection,
70    course_id: Uuid,
71) -> ModelResult<Vec<CompletionGridRow>> {
72    let rows_raw = sqlx::query!(
73        r#"
74WITH modules AS (
75  SELECT id AS module_id, name AS module_name, order_number
76  FROM course_modules
77  WHERE course_id = $1
78    AND deleted_at IS NULL
79),
80enrolled AS (
81  SELECT DISTINCT user_id
82  FROM course_instance_enrollments
83  WHERE course_id = $1
84    AND deleted_at IS NULL
85),
86latest_cmc AS (
87  SELECT DISTINCT ON (cmc.user_id, cmc.course_module_id)
88    cmc.id,
89    cmc.user_id,
90    cmc.course_module_id,
91    cmc.grade,
92    cmc.passed,
93    cmc.completion_date
94  FROM course_module_completions cmc
95  WHERE cmc.course_id = $1
96    AND cmc.deleted_at IS NULL
97  ORDER BY cmc.user_id, cmc.course_module_id, cmc.completion_date DESC
98),
99cmcr AS (
100  SELECT course_module_completion_id
101  FROM course_module_completion_registered_to_study_registries
102  WHERE course_id = $1
103    AND deleted_at IS NULL
104)
105SELECT
106  ud.first_name AS "first_name?",
107  ud.last_name AS "last_name?",
108  m.module_name AS "module_name?",
109  r.grade AS "grade?",
110  r.passed AS "passed?",
111  (r.id IS NOT NULL AND r.id IN (SELECT course_module_completion_id FROM cmcr)) AS "is_registered?"
112FROM modules m
113CROSS JOIN enrolled e
114JOIN users u ON u.id = e.user_id
115LEFT JOIN user_details ud ON ud.user_id = u.id
116LEFT JOIN latest_cmc r
117  ON r.user_id = e.user_id
118 AND r.course_module_id = m.module_id
119"#,
120        course_id
121    )
122    .fetch_all(conn)
123    .await?;
124
125    let rows = rows_raw
126        .into_iter()
127        .map(|r| {
128            let first = r.first_name.unwrap_or_default().trim().to_string();
129            let last = r.last_name.unwrap_or_default().trim().to_string();
130
131            let student = if first.is_empty() && last.is_empty() {
132                "(Missing name)".to_string()
133            } else {
134                format!("{first} {last}").trim().to_string()
135            };
136
137            let grade = match (r.grade, r.passed) {
138                (Some(g), _) => g.to_string(),
139                (None, Some(true)) => "Passed".to_string(),
140                (None, Some(false)) => "Failed".to_string(),
141                (None, None) => "-".to_string(),
142            };
143
144            let status = if r.is_registered.unwrap_or(false) {
145                "Registered".to_string()
146            } else {
147                "-".to_string()
148            };
149
150            CompletionGridRow {
151                student,
152                module: r.module_name,
153                grade,
154                status,
155            }
156        })
157        .collect();
158
159    Ok(rows)
160}
161
162#[derive(Clone, PartialEq, Deserialize, Serialize, sqlx::FromRow, ToSchema)]
163
164pub struct CertificateGridRow {
165    pub student: String,
166    pub certificate: String,
167    pub date_issued: Option<DateTime<Utc>>,
168    pub verification_id: Option<String>,
169    pub certificate_id: Option<Uuid>,
170    pub name_on_certificate: Option<String>,
171}
172
173/// Returns one row per enrolled student with their overall course certificate info.
174pub async fn get_certificates_grid_by_course_id(
175    conn: &mut PgConnection,
176    course_id: Uuid,
177) -> ModelResult<Vec<CertificateGridRow>> {
178    let rows = sqlx::query_as!(
179        CertificateGridRow,
180        r#"
181WITH enrolled AS (
182  SELECT DISTINCT user_id
183  FROM course_instance_enrollments
184  WHERE course_id = $1
185    AND deleted_at IS NULL
186),
187user_certs AS (
188  -- one latest certificate per user for this course
189  SELECT DISTINCT ON (gc.user_id)
190    gc.user_id,
191    gc.id,
192    gc.created_at AS latest_issued_at,
193    gc.verification_id,
194    gc.name_on_certificate
195  FROM generated_certificates gc
196  JOIN certificate_configuration_to_requirements cctr
197    ON gc.certificate_configuration_id = cctr.certificate_configuration_id
198   AND cctr.deleted_at IS NULL
199  JOIN course_modules cm
200    ON cm.id = cctr.course_module_id
201   AND cm.deleted_at IS NULL
202  WHERE cm.course_id = $1
203    AND gc.deleted_at IS NULL
204  ORDER BY gc.user_id, gc.created_at DESC
205)
206SELECT
207  /* non-null */
208  CASE
209    WHEN ud.first_name IS NULL OR TRIM(ud.first_name) = ''
210      OR ud.last_name  IS NULL OR TRIM(ud.last_name)  = ''
211    THEN '(Missing name)'
212    ELSE TRIM(ud.first_name) || ' ' || TRIM(ud.last_name)
213  END AS "student!",
214
215  /* non-null */
216  CASE
217    WHEN uc.user_id IS NOT NULL THEN 'Course Certificate'
218    ELSE 'No Certificate'
219  END AS "certificate!",
220
221  /* nullable */
222  uc.latest_issued_at AS "date_issued?",
223  uc.verification_id  AS "verification_id?",
224  uc.id               AS "certificate_id?",
225  uc.name_on_certificate AS "name_on_certificate?"
226
227FROM enrolled e
228JOIN users u ON u.id = e.user_id
229LEFT JOIN user_details ud ON ud.user_id = u.id
230LEFT JOIN user_certs uc ON uc.user_id = e.user_id
231ORDER BY
232  COALESCE(NULLIF(LOWER(TRIM(ud.last_name)),  ''), 'zzzzzz'),
233  COALESCE(NULLIF(LOWER(TRIM(ud.first_name)), ''), 'zzzzzz')
234        "#,
235        course_id
236    )
237    .fetch_all(conn)
238    .await?;
239
240    Ok(rows)
241}
headless_lms_models/library/students_view.rs

headless_lms_models/library/
students_view.rs
