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

headless_lms_models/library/
students_view.rs
