headless_lms_models/
page_visit_datum_summary_by_courses.rs

1use chrono::NaiveDate;
2use utoipa::ToSchema;
3
4use crate::prelude::*;
5
6#[derive(Debug, Serialize, Deserialize, PartialEq, Clone, ToSchema)]
7
8pub struct PageVisitDatumSummaryByCourse {
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 course_id: Option<Uuid>,
14    pub exam_id: Option<Uuid>,
15    pub referrer: Option<String>,
16    pub utm_source: Option<String>,
17    pub utm_medium: Option<String>,
18    pub utm_campaign: Option<String>,
19    pub utm_term: Option<String>,
20    pub utm_content: Option<String>,
21    pub num_visitors: i32,
22    pub visit_date: NaiveDate,
23}
24
25/// Calculates the statistics for a single day.
26pub async fn calculate_and_update_for_date(
27    conn: &mut PgConnection,
28    date: NaiveDate,
29) -> ModelResult<Vec<PageVisitDatumSummaryByCourse>> {
30    let res = sqlx::query_as!(
31        PageVisitDatumSummaryByCourse,
32        r#"
33INSERT INTO page_visit_datum_summary_by_courses (
34    course_id,
35    exam_id,
36    referrer,
37    utm_source,
38    utm_medium,
39    utm_campaign,
40    utm_term,
41    utm_content,
42    num_visitors,
43    visit_date
44  )
45SELECT
46  course_id,
47  exam_id,
48  referrer,
49  utm_source,
50  utm_medium,
51  utm_campaign,
52  utm_term,
53  utm_content,
54  COUNT(DISTINCT anonymous_identifier) AS num_visitors,
55  $1 AS visit_date
56FROM page_visit_datum
57WHERE deleted_at IS NULL
58  AND created_at >= ($1::date::timestamp AT TIME ZONE 'UTC')
59  AND created_at < (($1::date + 1)::timestamp AT TIME ZONE 'UTC')
60  AND is_bot = FALSE
61GROUP BY course_id,
62  exam_id,
63  referrer,
64  utm_source,
65  utm_medium,
66  utm_campaign,
67  utm_term,
68  utm_content
69  ON CONFLICT (
70    course_id,
71    exam_id,
72    referrer,
73    visit_date,
74    utm_source,
75    utm_medium,
76    utm_campaign,
77    utm_term,
78    utm_content,
79    deleted_at
80  ) DO
81UPDATE
82SET num_visitors = EXCLUDED.num_visitors
83RETURNING *
84"#,
85        date
86    )
87    .fetch_all(conn)
88    .await?;
89
90    Ok(res)
91}
92
93/// Gets the latest date for which the statistics have been calculated.
94pub async fn get_latest_date(conn: &mut PgConnection) -> ModelResult<Option<NaiveDate>> {
95    let res = sqlx::query!(
96        r#"
97SELECT MAX(visit_date) AS latest_date
98FROM page_visit_datum_summary_by_courses
99WHERE deleted_at IS NULL
100"#,
101    )
102    .fetch_optional(conn)
103    .await?;
104
105    Ok(res.and_then(|r| r.latest_date))
106}
107
108pub async fn get_all_for_course(
109    conn: &mut PgConnection,
110    course_id: Uuid,
111) -> ModelResult<Vec<PageVisitDatumSummaryByCourse>> {
112    let res = sqlx::query_as!(
113        PageVisitDatumSummaryByCourse,
114        r#"
115SELECT *
116FROM page_visit_datum_summary_by_courses
117WHERE course_id = $1
118AND deleted_at IS NULL
119"#,
120        course_id
121    )
122    .fetch_all(conn)
123    .await?;
124
125    Ok(res)
126}
headless_lms_models/page_visit_datum_summary_by_courses.rs

headless_lms_models/
page_visit_datum_summary_by_courses.rs
