1use super::TimeGranularity;
2use crate::prelude::*;
3
4#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
5#[cfg_attr(feature = "ts_rs", derive(TS))]
6pub struct GlobalStatEntry {
7 pub course_name: String,
8 pub course_id: Uuid,
9 pub organization_id: Uuid,
10 pub organization_name: String,
11 pub year: i32,
12 pub month: Option<i32>, pub value: i64,
14}
15
16#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
17#[cfg_attr(feature = "ts_rs", derive(TS))]
18pub struct GlobalCourseModuleStatEntry {
19 pub course_name: String,
20 pub course_id: Uuid,
21 pub course_module_id: Uuid,
22 pub course_module_name: Option<String>,
23 pub organization_id: Uuid,
24 pub organization_name: String,
25 pub year: String,
26 pub value: i64,
27 pub course_module_ects_credits: Option<f32>,
28}
29
30#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
31#[cfg_attr(feature = "ts_rs", derive(TS))]
32pub struct DomainCompletionStats {
33 pub email_domain: String,
34 pub total_completions: i64,
35 pub unique_users: i64,
36 pub registered_completion_percentage: Option<f64>,
37 pub registered_completions: i64,
38 pub not_registered_completions: i64,
39 pub users_with_some_registered_completions: i64,
40 pub users_with_some_unregistered_completions: i64,
41 pub registered_ects_credits: f32,
42 pub not_registered_ects_credits: f32,
43}
44
45#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
46#[cfg_attr(feature = "ts_rs", derive(TS))]
47pub struct CourseCompletionStats {
48 pub course_id: Uuid,
49 pub course_name: String,
50 pub total_completions: i64,
51 pub unique_users: i64,
52 pub registered_completion_percentage: Option<f64>,
53 pub registered_completions: i64,
54 pub not_registered_completions: i64,
55 pub users_with_some_registered_completions: i64,
56 pub users_with_some_unregistered_completions: i64,
57 pub registered_ects_credits: f32,
58 pub not_registered_ects_credits: f32,
59}
60
61pub async fn get_number_of_people_completed_a_course(
62 conn: &mut PgConnection,
63 granularity: TimeGranularity,
64) -> ModelResult<Vec<GlobalStatEntry>> {
65 let res = sqlx::query_as!(
66 GlobalStatEntry,
67 r#"
68SELECT c.name AS course_name,
69 EXTRACT('year' FROM completion_date)::int as "year!",
70 CASE WHEN $1 = 'Month' THEN EXTRACT('month' FROM completion_date)::int ELSE NULL END as "month",
71 COUNT(DISTINCT user_id) as "value!",
72 c.id as "course_id!",
73 o.id as "organization_id",
74 o.name as "organization_name"
75FROM course_module_completions cmc
76JOIN courses c ON cmc.course_id = c.id
77JOIN organizations o ON c.organization_id = o.id
78WHERE cmc.deleted_at IS NULL
79 AND c.is_draft = FALSE
80 AND c.deleted_at IS NULL
81 AND c.is_test_mode = FALSE
82GROUP BY c.name, c.id, o.id, o.name, "year!", "month"
83ORDER BY c.id, "year!", "month"
84"#,
85 granularity.to_string()
86 )
87 .fetch_all(conn)
88 .await?;
89 Ok(res)
90}
91
92pub async fn get_number_of_people_registered_completion_to_study_registry(
93 conn: &mut PgConnection,
94 granularity: TimeGranularity,
95) -> ModelResult<Vec<GlobalStatEntry>> {
96 let res = sqlx::query_as!(
97 GlobalStatEntry,
98 r#"
99SELECT c.name AS course_name,
100 EXTRACT('year' FROM cms.completion_date)::int as "year!",
101 CASE WHEN $1 = 'Month' THEN EXTRACT('month' FROM cms.completion_date)::int ELSE NULL END as "month",
102 COUNT(DISTINCT cmcrtsr.user_id) as "value!",
103 c.id as "course_id!",
104 o.id as "organization_id",
105 o.name as "organization_name"
106FROM course_module_completion_registered_to_study_registries cmcrtsr
107JOIN course_module_completions cms ON cmcrtsr.course_module_completion_id = cms.id
108JOIN courses c ON cmcrtsr.course_id = c.id
109JOIN organizations o ON c.organization_id = o.id
110WHERE cmcrtsr.deleted_at IS NULL
111 AND c.is_draft = FALSE
112 AND c.deleted_at IS NULL
113 AND c.is_test_mode = FALSE
114GROUP BY c.name, c.id, o.id, o.name, "year!", "month"
115ORDER BY c.id, "year!", "month"
116"#,
117 granularity.to_string()
118 )
119 .fetch_all(conn)
120 .await?;
121 Ok(res)
122}
123
124pub async fn get_number_of_people_done_at_least_one_exercise(
125 conn: &mut PgConnection,
126 granularity: TimeGranularity,
127) -> ModelResult<Vec<GlobalStatEntry>> {
128 dbg!(&granularity);
129 let res = sqlx::query_as!(
130 GlobalStatEntry,
131 r#"
132SELECT c.name AS course_name,
133 EXTRACT('year' FROM ess.created_at)::int as "year!",
134 CASE WHEN $1 = 'Month' THEN EXTRACT('month' FROM ess.created_at)::int ELSE NULL END as "month",
135 COUNT(DISTINCT ess.user_id) as "value!",
136 c.id as "course_id!",
137 o.id as "organization_id",
138 o.name as "organization_name"
139FROM exercise_slide_submissions ess
140JOIN courses c ON ess.course_id = c.id
141JOIN organizations o ON c.organization_id = o.id
142WHERE ess.deleted_at IS NULL
143 AND c.is_draft = FALSE
144 AND c.deleted_at IS NULL
145 AND c.is_test_mode = FALSE
146GROUP BY c.name, c.id, o.id, o.name, "year!", "month"
147ORDER BY c.id, "year!", "month"
148"#,
149 granularity.to_string()
150 )
151 .fetch_all(conn)
152 .await?;
153 dbg!(&res);
154 Ok(res)
155}
156
157pub async fn get_number_of_people_started_course(
158 conn: &mut PgConnection,
159 granularity: TimeGranularity,
160) -> ModelResult<Vec<GlobalStatEntry>> {
161 let res = sqlx::query_as!(
162 GlobalStatEntry,
163 r#"
164SELECT c.name AS course_name,
165 EXTRACT('year' FROM cie.created_at)::int as "year!",
166 CASE WHEN $1 = 'Month' THEN EXTRACT('month' FROM cie.created_at)::int ELSE NULL END as "month",
167 COUNT(DISTINCT cie.user_id) as "value!",
168 c.id as "course_id!",
169 o.id as "organization_id",
170 o.name as "organization_name"
171FROM course_instance_enrollments cie
172JOIN courses c ON cie.course_id = c.id
173JOIN organizations o ON c.organization_id = o.id
174WHERE cie.deleted_at IS NULL
175 AND c.is_draft = FALSE
176 AND c.deleted_at IS NULL
177 AND c.is_test_mode = FALSE
178GROUP BY c.name, c.id, o.id, o.name, "year!", "month"
179ORDER BY c.id, "year!", "month"
180"#,
181 granularity.to_string()
182 )
183 .fetch_all(conn)
184 .await?;
185 Ok(res)
186}
187
188pub async fn get_course_module_stats_by_completions_registered_to_study_registry(
189 conn: &mut PgConnection,
190 granularity: TimeGranularity,
191) -> ModelResult<Vec<GlobalCourseModuleStatEntry>> {
192 let res = sqlx::query_as!(
193 GlobalCourseModuleStatEntry,
194 r#"
195SELECT c.name as course_name,
196 q.year as "year!",
197 q.value as "value!",
198 q.course_module_id as "course_module_id!",
199 c.id as "course_id",
200 cm.name as "course_module_name",
201 cm.ects_credits as "course_module_ects_credits",
202 o.id as "organization_id",
203 o.name as "organization_name"
204FROM (
205 SELECT cmcrtsr.course_module_id,
206 CASE WHEN $1 = 'Month' THEN
207 EXTRACT('year' FROM cms.completion_date)::VARCHAR || '-' || LPAD(EXTRACT('month' FROM cms.completion_date)::VARCHAR, 2, '0')
208 ELSE
209 EXTRACT('year' FROM cms.completion_date)::VARCHAR
210 END as year,
211 COUNT(DISTINCT cmcrtsr.user_id) as value
212 FROM course_module_completion_registered_to_study_registries cmcrtsr
213 JOIN course_module_completions cms ON cmcrtsr.course_module_completion_id = cms.id
214 WHERE cmcrtsr.deleted_at IS NULL
215 GROUP BY cmcrtsr.course_module_id,
216 year
217 ORDER BY cmcrtsr.course_module_id,
218 year
219 ) q
220 JOIN course_modules cm ON q.course_module_id = cm.id
221 JOIN courses c ON cm.course_id = c.id
222 JOIN organizations o ON c.organization_id = o.id
223WHERE c.is_draft = FALSE
224 AND c.deleted_at IS NULL
225 AND c.is_test_mode = FALSE
226"#,
227 granularity.to_string()
228 )
229 .fetch_all(conn)
230 .await?;
231 Ok(res)
232}
233
234pub async fn get_completion_stats_by_email_domain(
256 conn: &mut PgConnection,
257 year: Option<i32>,
258) -> ModelResult<Vec<DomainCompletionStats>> {
259 let res = sqlx::query_as!(
260 DomainCompletionStats,
261 r#"
262WITH deduped_completions AS (
263SELECT *
264FROM (
265 SELECT cmc.*,
266 CASE
267 WHEN cmr.course_module_completion_id IS NOT NULL THEN 1
268 ELSE 0
269 END AS is_registered,
270 ROW_NUMBER() OVER (
271 PARTITION BY cmc.user_id,
272 cmc.course_module_id
273 ORDER BY CASE
274 WHEN cmr.course_module_completion_id IS NOT NULL THEN 1
275 ELSE 0
276 END DESC,
277 cmc.created_at DESC
278 ) AS rn
279 FROM course_module_completions cmc
280 LEFT JOIN course_module_completion_registered_to_study_registries cmr ON cmc.id = cmr.course_module_completion_id
281 AND cmr.deleted_at IS NULL
282 WHERE cmc.deleted_at IS NULL
283 AND (
284 $1::int IS NULL
285 OR EXTRACT(
286 YEAR
287 FROM cmc.completion_date
288 ) = $1
289 )
290 ) sub
291WHERE rn = 1
292),
293unique_registrations AS (
294SELECT DISTINCT course_module_completion_id
295FROM course_module_completion_registered_to_study_registries cmr
296WHERE cmr.deleted_at IS NULL
297)
298SELECT u.email_domain AS "email_domain!",
299COUNT(DISTINCT d.id) AS "total_completions!",
300COUNT(DISTINCT d.user_id) AS "unique_users!",
301ROUND(
302 (
303 SUM(
304 CASE
305 WHEN ur.course_module_completion_id IS NOT NULL THEN 1
306 ELSE 0
307 END
308 ) * 100.0
309 ) / NULLIF(COUNT(DISTINCT d.id), 0),
310 2
311)::float8 AS "registered_completion_percentage",
312SUM(
313 CASE
314 WHEN ur.course_module_completion_id IS NOT NULL THEN 1
315 ELSE 0
316 END
317) AS "registered_completions!",
318SUM(
319 CASE
320 WHEN ur.course_module_completion_id IS NULL THEN 1
321 ELSE 0
322 END
323) AS "not_registered_completions!",
324COUNT(
325 DISTINCT CASE
326 WHEN ur.course_module_completion_id IS NOT NULL THEN d.user_id
327 END
328) AS "users_with_some_registered_completions!",
329COUNT(
330 DISTINCT CASE
331 WHEN ur.course_module_completion_id IS NULL THEN d.user_id
332 END
333) AS "users_with_some_unregistered_completions!",
334COALESCE(
335 SUM(
336 CASE
337 WHEN ur.course_module_completion_id IS NOT NULL THEN cm.ects_credits
338 ELSE 0
339 END
340 ),
341 0
342) AS "registered_ects_credits!",
343COALESCE(
344 SUM(
345 CASE
346 WHEN ur.course_module_completion_id IS NULL THEN cm.ects_credits
347 ELSE 0
348 END
349 ),
350 0
351) AS "not_registered_ects_credits!"
352FROM deduped_completions d
353JOIN users u ON d.user_id = u.id
354AND u.deleted_at IS NULL
355LEFT JOIN unique_registrations ur ON d.id = ur.course_module_completion_id
356JOIN courses c ON d.course_id = c.id
357AND c.deleted_at IS NULL
358JOIN course_modules cm ON d.course_module_id = cm.id
359AND cm.deleted_at IS NULL
360WHERE d.prerequisite_modules_completed = TRUE
361AND c.is_draft = FALSE
362AND c.is_test_mode = FALSE
363AND cm.enable_registering_completion_to_uh_open_university = TRUE
364AND cm.ects_credits IS NOT NULL
365AND cm.ects_credits > 0
366GROUP BY u.email_domain
367ORDER BY "total_completions!" DESC,
368email_domain
369 "#,
370 year
371 )
372 .fetch_all(conn)
373 .await?;
374 Ok(res)
375}
376
377pub async fn get_course_completion_stats_for_email_domain(
387 conn: &mut PgConnection,
388 email_domain: String,
389 year: Option<i32>,
390) -> ModelResult<Vec<CourseCompletionStats>> {
391 let res = sqlx::query_as!(
392 CourseCompletionStats,
393 r#"
394WITH deduped_completions AS (
395 SELECT *
396 FROM (
397 SELECT cmc.*,
398 CASE
399 WHEN cmr.course_module_completion_id IS NOT NULL THEN 1
400 ELSE 0
401 END AS is_registered,
402 ROW_NUMBER() OVER (
403 PARTITION BY cmc.user_id,
404 cmc.course_module_id
405 ORDER BY CASE
406 WHEN cmr.course_module_completion_id IS NOT NULL THEN 1
407 ELSE 0
408 END DESC,
409 cmc.created_at DESC
410 ) AS rn
411 FROM course_module_completions cmc
412 LEFT JOIN course_module_completion_registered_to_study_registries cmr ON cmc.id = cmr.course_module_completion_id
413 AND cmr.deleted_at IS NULL
414 WHERE cmc.deleted_at IS NULL
415 AND (
416 $2::int IS NULL
417 OR EXTRACT(
418 YEAR
419 FROM cmc.completion_date
420 ) = $2
421 )
422 ) sub
423 WHERE rn = 1
424),
425unique_registrations AS (
426 SELECT DISTINCT course_module_completion_id
427 FROM course_module_completion_registered_to_study_registries cmr
428 WHERE cmr.deleted_at IS NULL
429)
430SELECT c.id AS "course_id!",
431 c.name AS "course_name!",
432 COUNT(DISTINCT d.id) AS "total_completions!",
433 COUNT(DISTINCT d.user_id) AS "unique_users!",
434 ROUND(
435 (
436 SUM(
437 CASE
438 WHEN ur.course_module_completion_id IS NOT NULL THEN 1
439 ELSE 0
440 END
441 ) * 100.0
442 ) / NULLIF(COUNT(DISTINCT d.id), 0),
443 2
444 )::float8 AS "registered_completion_percentage",
445 SUM(
446 CASE
447 WHEN ur.course_module_completion_id IS NOT NULL THEN 1
448 ELSE 0
449 END
450 ) AS "registered_completions!",
451 SUM(
452 CASE
453 WHEN ur.course_module_completion_id IS NULL THEN 1
454 ELSE 0
455 END
456 ) AS "not_registered_completions!",
457 COUNT(
458 DISTINCT CASE
459 WHEN ur.course_module_completion_id IS NOT NULL THEN d.user_id
460 END
461 ) AS "users_with_some_registered_completions!",
462 COUNT(
463 DISTINCT CASE
464 WHEN ur.course_module_completion_id IS NULL THEN d.user_id
465 END
466 ) AS "users_with_some_unregistered_completions!",
467 COALESCE(
468 SUM(
469 CASE
470 WHEN ur.course_module_completion_id IS NOT NULL THEN cm.ects_credits
471 ELSE 0
472 END
473 ),
474 0
475 ) AS "registered_ects_credits!",
476 COALESCE(
477 SUM(
478 CASE
479 WHEN ur.course_module_completion_id IS NULL THEN cm.ects_credits
480 ELSE 0
481 END
482 ),
483 0
484 ) AS "not_registered_ects_credits!"
485FROM deduped_completions d
486 JOIN users u ON d.user_id = u.id
487 AND u.deleted_at IS NULL
488 LEFT JOIN unique_registrations ur ON d.id = ur.course_module_completion_id
489 JOIN courses c ON d.course_id = c.id
490 AND c.deleted_at IS NULL
491 JOIN course_modules cm ON d.course_module_id = cm.id
492 AND cm.deleted_at IS NULL
493WHERE d.prerequisite_modules_completed = TRUE
494 AND c.is_draft = FALSE
495 AND c.is_test_mode = FALSE
496 AND cm.enable_registering_completion_to_uh_open_university = TRUE
497 AND cm.ects_credits IS NOT NULL
498 AND cm.ects_credits > 0
499 AND u.email_domain = $1
500GROUP BY c.id,
501 c.name
502ORDER BY "total_completions!" DESC,
503 c.id
504 "#,
505 email_domain,
506 year
507 )
508 .fetch_all(conn)
509 .await?;
510 Ok(res)
511}