Changes between Version 14 and Version 15 of WikiStart/AdvancedReports

Timestamp:

01/29/24 17:14:49 (12 months ago)

Author:

121232

Comment:

--

WikiStart/AdvancedReports

@@ -252 +252 @@
 
 {{{
-DECLARE correct_student_id INT;
-SET correct_student_id = 11111;
-
-SELECT 
- ce.calendar_event_id,
- ce.event_name,
- ce.event_type,
- ce.event_description,
- ce.event_start_time,
- ce.event_end_time
+
+ DECLARE correct_student_id INT;
+SET correct_student_id = 11111;
+DECLARE c_term_type VARCHAR(20);
+SET c_term_type = 'Zimski';
+DECLARE c_starting_year INT;
+SET c_starting_year = 2023;
+DECLARE c_finishing_year INT;
+SET c_finishing_year = 2024;
+
+SELECT 
+    ce.event_name,
+    sat.subject_name,
+    ce.event_type,
+    ce.event_description
 FROM
- calendar_event ce
+    subject_at_term AS sat
 JOIN
- term t ON ce.starting_year = t.starting_year
- AND ce.finishing_year = t.finishing_year
- AND ce.term_type = t.term_type
-WHERE t.student_id = correct_student_id
-  AND t.starting_year = 2023
-  AND t.finishing_year = 2024
-  AND t.term_type = 'Zimski';
+    subject_event AS se
+    ON sat.student_id = se.student_id
+    AND sat.term_subject_id = se.term_subject_id
+    AND sat.term_type = se.term_type
+    AND sat.starting_year = se.starting_year
+    AND sat.finishing_year = se.finishing_year
+JOIN
+    calendar_event AS ce
+    ON se.calendar_event_id = ce.calendar_event_id
+WHERE 
+    se.student_id = correct_student_id
+    AND se.starting_year = c_starting_year
+    AND se.finishing_year = c_finishing_year
+    AND se.term_type = c_term_type;
 }}}
 
@@ -281 +293 @@
 DECLARE correct_student_id INT;
 SET correct_student_id = 11111;
+DECLARE c_term_type VARCHAR(20);
+SET c_term_type = 'Zimski';
+DECLARE c_starting_year INT;
+SET c_starting_year = 2023;
+DECLARE c_finishing_year INT;
+SET c_finishing_year = 2024;
+DECLARE c_term_subject_id INT;
+SET c_term_subject_id = 87654;
 
 SELECT
@@ -296 +316 @@
 FROM
     subject_event se
-    JOIN calendar_event ce ON se.calendar_event_id = ce.calendar_event_id
+JOIN
+    calendar_event ce ON se.calendar_event_id = ce.calendar_event_id
 WHERE
-    se.term_subject_id = 170569
+    se.term_subject_id = c_term_subject_id
+    AND se.term_type = c_term_type
+    AND se.starting_year = c_starting_year
+    AND se.finishing_year = c_finishing_year
+    AND se.student_id = correct_student_id
+
 UNION
+
 SELECT
     NULL AS calendar_event_id,
@@ -315 +342 @@
     creates_task ct
 JOIN 
-    task t ON ct.task_id = t.task_id
+    task AS t ON ct.task_id = t.task_id
 WHERE
-    ct.term_subject_id = 170569;
-
-
-
+    ct.term_subject_id = c_term_subject_id
+    AND ct.term_type = c_term_type
+    AND ct.starting_year = c_starting_year
+    AND ct.finishing_year = c_finishing_year
+    AND ct.student_id = correct_student_id;
 }}}