headless_lms_models/
page_visit_datum_summary_by_pages.rs

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

headless_lms_models/
page_visit_datum_summary_by_pages.rs
