headless_lms_models/
page_visit_datum_summary_by_courses_countries.rs

1use chrono::NaiveDate;
2use utoipa::ToSchema;
3
4use crate::prelude::*;
5
6#[derive(Debug, Serialize, Deserialize, PartialEq, Clone, ToSchema)]
7
8pub struct PageVisitDatumSummaryByCoursesCountries {
9    pub id: Uuid,
10    pub created_at: DateTime<Utc>,
11    pub updated_at: DateTime<Utc>,
12    pub deleted_at: Option<DateTime<Utc>>,
13    pub country: Option<String>,
14    pub course_id: Option<Uuid>,
15    pub exam_id: Option<Uuid>,
16    pub num_visitors: i32,
17    pub visit_date: NaiveDate,
18}
19
20/// Calculates the statistics for a single day.
21pub async fn calculate_and_update_for_date(
22    conn: &mut PgConnection,
23    date: NaiveDate,
24) -> ModelResult<Vec<PageVisitDatumSummaryByCoursesCountries>> {
25    let res = sqlx::query_as!(
26        PageVisitDatumSummaryByCoursesCountries,
27        r#"
28INSERT INTO page_visit_datum_summary_by_courses_countries (
29    course_id,
30    exam_id,
31    country,
32    num_visitors,
33    visit_date
34  )
35SELECT course_id,
36  exam_id,
37  country,
38  COUNT(DISTINCT anonymous_identifier) AS num_visitors,
39  $1 AS visit_date
40FROM page_visit_datum
41WHERE deleted_at IS NULL
42  AND created_at >= ($1::date::timestamp AT TIME ZONE 'UTC')
43  AND created_at < (($1::date + 1)::timestamp AT TIME ZONE 'UTC')
44  AND is_bot = FALSE
45GROUP BY course_id,
46  exam_id,
47  country ON CONFLICT (
48    course_id,
49    exam_id,
50    visit_date,
51    country,
52    deleted_at
53  ) DO
54UPDATE
55SET num_visitors = EXCLUDED.num_visitors
56RETURNING *;
57"#,
58        date
59    )
60    .fetch_all(conn)
61    .await?;
62
63    Ok(res)
64}
65
66pub async fn get_all_for_course(
67    conn: &mut PgConnection,
68    course_id: Uuid,
69) -> ModelResult<Vec<PageVisitDatumSummaryByCoursesCountries>> {
70    let res = sqlx::query_as!(
71        PageVisitDatumSummaryByCoursesCountries,
72        r#"
73SELECT *
74FROM page_visit_datum_summary_by_courses_countries
75WHERE course_id = $1
76  AND deleted_at IS NULL
77"#,
78        course_id
79    )
80    .fetch_all(conn)
81    .await?;
82
83    Ok(res)
84}
headless_lms_models/page_visit_datum_summary_by_courses_countries.rs

headless_lms_models/
page_visit_datum_summary_by_courses_countries.rs
