Changes between Version 2 and Version 3 of WikiStart/AdvancedReports


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

--

Legend:

Unmodified
Added
Removed
Modified

  • WikiStart/AdvancedReports

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