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

headless_lms_models/
page_visit_datum_summary_by_pages.rs
