headless_lms_models/
page_visit_datum_summary_by_courses_countries.rs

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

headless_lms_models/
page_visit_datum_summary_by_courses_countries.rs
