Changes between Version 3 and Version 4 of WikiStart/AdvancedReports


Ignore:
Timestamp:
01/29/24 13:11:13 (21 months ago)
Author:
121232
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • WikiStart/AdvancedReports

    v3 v4  
    22
    33== **[[span(style=color: #BF1B1B, 1. Листање(приказ) на денешни events, count на денешни часови, испити и tasks:)]]**
     4
     5{{{
     6DECLARE correct_student_id INT;
     7SET correct_student_id = 11111;
     8
     9WITH EventData AS (
     10    SELECT
     11        ce.calendar_event_id,
     12        ce.event_name,
     13        ce.event_type,
     14        ce.event_start_time,
     15        ce.event_end_time,
     16        nre.event_date
     17    FROM
     18        student_event AS se
     19    JOIN
     20        calendar_event AS ce ON se.calendar_event_id = ce.calendar_event_id
     21    JOIN
     22        non_repeating_events AS nre ON ce.calendar_event_id = nre.calendar_event_id
     23    WHERE
     24        nre.event_date = CURRENT_DATE
     25        AND se.student_id = correct_student_id
     26
     27    UNION
     28
     29    SELECT
     30        ce.calendar_event_id,
     31        ce.event_name,
     32        ce.event_type,
     33        ce.event_start_time,
     34        ce.event_end_time,
     35        CASE
     36            WHEN re.event_week_day = 'MONDAY' THEN date_trunc('week', CURRENT_DATE)::DATE
     37            WHEN re.event_week_day = 'TUESDAY' THEN date_trunc('week', CURRENT_DATE) + interval '1 day'::DATE
     38            WHEN re.event_week_day = 'WEDNESDAY' THEN date_trunc('week', CURRENT_DATE) + interval '2 days'::DATE
     39            WHEN re.event_week_day = 'THURSDAY' THEN date_trunc('week', CURRENT_DATE) + interval '3 days'::DATE
     40            WHEN re.event_week_day = 'FRIDAY' THEN date_trunc('week', CURRENT_DATE) + interval '4 days'::DATE
     41            WHEN re.event_week_day = 'SATURDAY' THEN date_trunc('week', CURRENT_DATE) + interval '5 days'::DATE
     42            WHEN re.event_week_day = 'SUNDAY' THEN date_trunc('week', CURRENT_DATE) + interval '6 days'::DATE
     43        END AS event_date
     44    FROM
     45        student_event AS se
     46    JOIN
     47        calendar_event AS ce ON se.calendar_event_id = ce.calendar_event_id
     48    JOIN
     49        repeating_events AS re ON ce.calendar_event_id = re.calendar_event_id
     50    WHERE
     51        re.starting_date <= (
     52            CASE
     53                WHEN re.event_week_day = 'MONDAY' THEN date_trunc('week', CURRENT_DATE)::DATE
     54                WHEN re.event_week_day = 'TUESDAY' THEN date_trunc('week', CURRENT_DATE) + interval '1 day'::DATE
     55                WHEN re.event_week_day = 'WEDNESDAY' THEN date_trunc('week', CURRENT_DATE) + interval '2 days'::DATE
     56                WHEN re.event_week_day = 'THURSDAY' THEN date_trunc('week', CURRENT_DATE) + interval '3 days'::DATE
     57                WHEN re.event_week_day = 'FRIDAY' THEN date_trunc('week', CURRENT_DATE) + interval '4 days'::DATE
     58                WHEN re.event_week_day = 'SATURDAY' THEN date_trunc('week', CURRENT_DATE) + interval '5 days'::DATE
     59                WHEN re.event_week_day = 'SUNDAY' THEN date_trunc('week', CURRENT_DATE) + interval '6 days'::DATE
     60            END
     61        )
     62        AND re.ending_date >= (
     63            CASE
     64                WHEN re.event_week_day = 'MONDAY' THEN date_trunc('week', CURRENT_DATE)::DATE
     65                WHEN re.event_week_day = 'TUESDAY' THEN date_trunc('week', CURRENT_DATE) + interval '1 day'::DATE
     66                WHEN re.event_week_day = 'WEDNESDAY' THEN date_trunc('week', CURRENT_DATE) + interval '2 days'::DATE
     67                WHEN re.event_week_day = 'THURSDAY' THEN date_trunc('week', CURRENT_DATE) + interval '3 days'::DATE
     68                WHEN re.event_week_day = 'FRIDAY' THEN date_trunc('week', CURRENT_DATE) + interval '4 days'::DATE
     69                WHEN re.event_week_day = 'SATURDAY' THEN date_trunc('week', CURRENT_DATE) + interval '5 days'::DATE
     70                WHEN re.event_week_day = 'SUNDAY' THEN date_trunc('week', CURRENT_DATE) + interval '6 days'::DATE
     71            END
     72        )
     73        AND (EXTRACT(day FROM date_trunc('week', CURRENT_DATE) - date_trunc('week', (
     74            CASE
     75                WHEN re.event_week_day = 'MONDAY' THEN date_trunc('week', CURRENT_DATE)::DATE
     76                WHEN re.event_week_day = 'TUESDAY' THEN date_trunc('week', CURRENT_DATE) + interval '1 day'::DATE
     77                WHEN re.event_week_day = 'WEDNESDAY' THEN date_trunc('week', CURRENT_DATE) + interval '2 days'::DATE
     78                WHEN re.event_week_day = 'THURSDAY' THEN date_trunc('week', CURRENT_DATE) + interval '3 days'::DATE
     79                WHEN re.event_week_day = 'FRIDAY' THEN date_trunc('week', CURRENT_DATE) + interval '4 days'::DATE
     80                WHEN re.event_week_day = 'SATURDAY' THEN date_trunc('week', CURRENT_DATE) + interval '5 days'::DATE
     81                WHEN re.event_week_day = 'SUNDAY' THEN date_trunc('week', CURRENT_DATE) + interval '6 days'::DATE
     82            END
     83        ))) % (7 * re.repeats_every_n_weeks)) = 0
     84        AND se.student_id = correct_student_id
     85) AS EventResult
     86
     87SELECT
     88    COUNT(CASE WHEN event_type = 'Exam' THEN 1 END) AS exam_count,
     89    COUNT(CASE WHEN event_type <> 'Exam' THEN 1 END) AS non_exam_count,
     90    COUNT(task.task_id) AS task_count
     91FROM EventResult
     92LEFT JOIN task ON EventResult.event_date = task.task_date;
     93
     94}}}
     95
    496
    597== **[[span(style=color: #BF1B1B, 2. Листање(приказ) на таскови за наредните три дена:)]]**