headless_lms_models/
page_visit_datum_summary_by_courses_countries.rs

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
use chrono::NaiveDate;

use crate::prelude::*;

#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
#[cfg_attr(feature = "ts_rs", derive(TS))]
pub struct PageVisitDatumSummaryByCoursesCountries {
    pub id: Uuid,
    pub created_at: DateTime<Utc>,
    pub updated_at: DateTime<Utc>,
    pub deleted_at: Option<DateTime<Utc>>,
    pub country: Option<String>,
    pub course_id: Option<Uuid>,
    pub exam_id: Option<Uuid>,
    pub num_visitors: i32,
    pub visit_date: NaiveDate,
}

/// Calculates the statistics for a single day.
pub async fn calculate_and_update_for_date(
    conn: &mut PgConnection,
    date: NaiveDate,
) -> ModelResult<Vec<PageVisitDatumSummaryByCoursesCountries>> {
    let res = sqlx::query_as!(
        PageVisitDatumSummaryByCoursesCountries,
        r#"
INSERT INTO page_visit_datum_summary_by_courses_countries (
    course_id,
    exam_id,
    country,
    num_visitors,
    visit_date
  )
SELECT course_id,
  exam_id,
  country,
  COUNT(DISTINCT anonymous_identifier) AS num_visitors,
  $1 AS visit_date
FROM page_visit_datum
WHERE deleted_at IS NULL
  AND created_at::date = $1
  AND is_bot = FALSE
GROUP BY course_id,
  exam_id,
  country ON CONFLICT (
    course_id,
    exam_id,
    visit_date,
    country,
    deleted_at
  ) DO
UPDATE
SET num_visitors = EXCLUDED.num_visitors
RETURNING *;
"#,
        date
    )
    .fetch_all(conn)
    .await?;

    Ok(res)
}

pub async fn get_all_for_course(
    conn: &mut PgConnection,
    course_id: Uuid,
) -> ModelResult<Vec<PageVisitDatumSummaryByCoursesCountries>> {
    let res = sqlx::query_as!(
        PageVisitDatumSummaryByCoursesCountries,
        r#"
SELECT *
FROM page_visit_datum_summary_by_courses_countries
WHERE course_id = $1
  AND deleted_at IS NULL
"#,
        course_id
    )
    .fetch_all(conn)
    .await?;

    Ok(res)
}