headless_lms_models/
page_visit_datum.rs

1use chrono::NaiveDate;
2
3use crate::prelude::*;
4
5pub struct NewPageVisitDatum {
6    pub course_id: Option<Uuid>,
7    pub exam_id: Option<Uuid>,
8    pub page_id: Uuid,
9    pub country: Option<String>,
10    pub browser: Option<String>,
11    pub browser_version: Option<String>,
12    pub operating_system: Option<String>,
13    pub operating_system_version: Option<String>,
14    pub device_type: Option<String>,
15    pub referrer: Option<String>,
16    pub is_bot: bool,
17    pub utm_source: Option<String>,
18    pub utm_medium: Option<String>,
19    pub utm_campaign: Option<String>,
20    pub utm_term: Option<String>,
21    pub utm_content: Option<String>,
22    pub anonymous_identifier: String,
23}
24
25pub async fn insert(
26    conn: &mut PgConnection,
27    new_page_visit_datum: NewPageVisitDatum,
28) -> ModelResult<Uuid> {
29    let res = sqlx::query!(
30        "
31INSERT INTO page_visit_datum (
32    course_id,
33    exam_id,
34    page_id,
35    country,
36    browser,
37    browser_version,
38    operating_system,
39    operating_system_version,
40    device_type,
41    referrer,
42    is_bot,
43    utm_source,
44    utm_medium,
45    utm_campaign,
46    utm_term,
47    utm_content,
48    anonymous_identifier
49  )
50VALUES (
51    $1,
52    $2,
53    $3,
54    $4,
55    $5,
56    $6,
57    $7,
58    $8,
59    $9,
60    $10,
61    $11,
62    $12,
63    $13,
64    $14,
65    $15,
66    $16,
67    $17
68  )
69RETURNING id
70",
71        new_page_visit_datum.course_id,
72        new_page_visit_datum.exam_id,
73        new_page_visit_datum.page_id,
74        new_page_visit_datum.country,
75        unknown_is_none(new_page_visit_datum.browser),
76        unknown_is_none(new_page_visit_datum.browser_version),
77        unknown_is_none(new_page_visit_datum.operating_system),
78        unknown_is_none(new_page_visit_datum.operating_system_version),
79        unknown_is_none(new_page_visit_datum.device_type),
80        new_page_visit_datum.referrer,
81        new_page_visit_datum.is_bot,
82        new_page_visit_datum.utm_source,
83        new_page_visit_datum.utm_medium,
84        new_page_visit_datum.utm_campaign,
85        new_page_visit_datum.utm_term,
86        new_page_visit_datum.utm_content,
87        new_page_visit_datum.anonymous_identifier
88    )
89    .fetch_one(conn)
90    .await?;
91    Ok(res.id)
92}
93
94/// Woothee uses UNKNOWN instead of None, this fixes that
95fn unknown_is_none(value: Option<String>) -> Option<String> {
96    value.filter(|v| v != "UNKNOWN")
97}
98
99// Gets the oldest date there are some stastics for.
100pub async fn get_oldest_date(conn: &mut PgConnection) -> ModelResult<Option<NaiveDate>> {
101    let res = sqlx::query!(
102        r#"
103SELECT MIN(created_at) AS oldest_date
104FROM page_visit_datum
105WHERE deleted_at IS NULL
106"#,
107    )
108    .fetch_optional(conn)
109    .await?;
110
111    Ok(res.and_then(|res| res.oldest_date.map(|d| d.date_naive())))
112}