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

headless_lms_models/
page_visit_datum_summary_by_courses.rs
