rosario_bulletins.sql

SELECT s.STUDENT_ID as id,
       CONCAT(s.FIRST_NAME, coalesce(NULLIF(CONCAT(' ', s.MIDDLE_NAME, ' '), '  '), ' '), s.LAST_NAME) AS nom_complet,
       s.LAST_NAME as nom,
       s.FIRST_NAME as prenom,
       smperiod.title AS période,
       sgl.title AS classe,
       SUBSTRING_INDEX(
           SUBSTRING_INDEX(courses.COURSE_TITLE, 'fr_FR.utf8:', -1),
           '|',
           1
       ) AS cours,
       -- courses.TITLE AS COURSE_PERIOD_TITLE,
       -- courses.STAFF_ID,
       CONCAT(ssm2.FIRST_NAME, coalesce(NULLIF(CONCAT(' ', ssm2.MIDDLE_NAME, ' '), '  '), ' '), ssm2.LAST_NAME) AS enseignant,
       assignments.title AS devoir,
       assignments.PARTIAL_POINTS as points,
       assignments.PARTIAL_TOTAL as total_points,
       assignments.PARTIAL_WEIGHT as coeff
FROM students s
JOIN student_enrollment ssmenr ON (ssmenr.STUDENT_ID = s.STUDENT_ID)
INNER JOIN school_gradelevels sgl ON (sgl.id = ssmenr.GRADE_ID)
LEFT JOIN (
    SELECT s.syear, s.STUDENT_ID, c.TITLE AS COURSE_TITLE,cp.TITLE,cp.COURSE_PERIOD_ID,cp.COURSE_ID,cp.TEACHER_ID AS STAFF_ID
    FROM schedule s,course_periods cp,courses c
    WHERE cp.COURSE_PERIOD_ID=s.COURSE_PERIOD_ID
      AND s.MARKING_PERIOD_ID IN ('1','2','4')
      AND '2026-01-30'>=s.START_DATE
      AND ((s.END_DATE IS NULL OR '2026-01-30'<=s.END_DATE)
        OR EXISTS(SELECT 1 FROM gradebook_grades gg
                  WHERE gg.STUDENT_ID=s.STUDENT_ID
                    AND gg.COURSE_PERIOD_ID=cp.COURSE_PERIOD_ID
                  LIMIT 1))
      AND cp.GRADE_SCALE_ID IS NOT NULL
      AND c.COURSE_ID=cp.COURSE_ID
    ORDER BY cp.SHORT_NAME, cp.TITLE
) courses ON (courses.STUDENT_ID = s.STUDENT_ID)
LEFT JOIN (
    SELECT s.STUDENT_ID,
           ga.marking_period_id,
           ssm.SYEAR,
           ssm.SCHOOL_ID,
           cp.course_period_id,
           gt.ASSIGNMENT_TYPE_ID,
           ga.title,
           sum(CASE WHEN gg.POINTS < 0 THEN '0' ELSE gg.POINTS END)                           AS PARTIAL_POINTS,
           sum(CASE WHEN gg.POINTS < 0 THEN '0' ELSE ga.POINTS END)                           AS PARTIAL_TOTAL,
           gt.FINAL_GRADE_PERCENT,
           sum(CASE
                   WHEN gg.POINTS < 0 THEN '0'
                   ELSE
                       (CASE WHEN ga.WEIGHT IS NULL THEN '0' ELSE ga.WEIGHT END) END)         AS PARTIAL_WEIGHT,
           sum(CASE WHEN gg.POINTS < 0 THEN '0' ELSE (gg.POINTS / ga.POINTS) * ga.WEIGHT END) AS PARTIAL_WEIGHTED_GRADE
    FROM students s
         JOIN student_enrollment ssm ON (ssm.STUDENT_ID = s.STUDENT_ID)
         JOIN schedule ss
          ON (ss.STUDENT_ID = s.STUDENT_ID AND ss.SYEAR = ssm.SYEAR AND ss.MARKING_PERIOD_ID IN ('1', '2', '4')
              AND ('2026-01-30' >= ss.START_DATE
                  AND ('2026-01-30' <= ss.END_DATE
                      OR ss.END_DATE IS NULL)))
     JOIN course_periods cp ON (cp.COURSE_PERIOD_ID = ss.COURSE_PERIOD_ID)
     JOIN gradebook_assignments ga ON
        (((ga.COURSE_PERIOD_ID = cp.COURSE_PERIOD_ID
            OR ga.COURSE_ID = cp.COURSE_ID)
            AND ga.STAFF_ID = cp.TEACHER_ID)
            AND ga.MARKING_PERIOD_ID IN ('4', '5'))
             LEFT OUTER JOIN gradebook_grades gg ON
        (gg.STUDENT_ID = s.STUDENT_ID
            AND gg.ASSIGNMENT_ID = ga.ASSIGNMENT_ID
            AND gg.COURSE_PERIOD_ID = cp.COURSE_PERIOD_ID),
         gradebook_assignment_types gt
    WHERE gt.ASSIGNMENT_TYPE_ID = ga.ASSIGNMENT_TYPE_ID
      AND gt.COURSE_ID = cp.COURSE_ID
      AND (gg.POINTS IS NOT NULL
        OR (ga.ASSIGNED_DATE IS NULL OR CURRENT_DATE >= ga.ASSIGNED_DATE)
               AND (ga.DUE_DATE IS NULL OR CURRENT_DATE >= ga.DUE_DATE)
        OR CURRENT_DATE > (SELECT END_DATE
                           FROM school_marking_periods
                           WHERE MARKING_PERIOD_ID = ga.MARKING_PERIOD_ID))
      AND (gg.POINTS IS NOT NULL
        OR ga.DUE_DATE IS NULL
        OR ((ga.DUE_DATE >= ss.START_DATE
            AND (ss.END_DATE IS NULL OR ga.DUE_DATE <= ss.END_DATE))
            AND (ga.DUE_DATE >= ssm.START_DATE
                AND (ssm.END_DATE IS NULL OR ga.DUE_DATE <= ssm.END_DATE))))
      AND ga.POINTS > 0
    GROUP BY ga.marking_period_id, ssm.SYEAR, ssm.SCHOOL_ID, cp.course_period_id, gt.ASSIGNMENT_TYPE_ID, ga.title, gt.FINAL_GRADE_PERCENT, s.STUDENT_ID
) assignments ON (assignments.syear = ssmenr.syear and assignments.school_id = ssmenr.school_id
                      and assignments.STUDENT_ID = s.STUDENT_ID
                        and assignments.COURSE_PERIOD_ID = courses.COURSE_PERIOD_ID)
JOIN school_marking_periods smperiod ON (smperiod.marking_period_id = assignments.marking_period_id)
LEFT JOIN staff ssm2 ON (ssm2.STAFF_ID = courses.STAFF_ID AND ssm2.SYEAR = courses.syear)
WHERE ssmenr.SCHOOL_ID = 1
AND ssmenr.SYEAR = '2025'
  AND ('2026-01-30' >= ssmenr.START_DATE
    AND (ssmenr.END_DATE IS NULL
        OR '2026-01-30' <= ssmenr.END_DATE))
AND s.student_id = 385
ORDER BY nom_complet, période, classe, cours
;

SELECT s.STUDENT_ID,gt.ASSIGNMENT_TYPE_ID,
       sum(CASE WHEN gg.POINTS<0 THEN '0' ELSE gg.POINTS END) AS PARTIAL_POINTS,
       sum(CASE WHEN gg.POINTS<0 THEN '0' ELSE ga.POINTS END) AS PARTIAL_TOTAL,
       gt.FINAL_GRADE_PERCENT,sum(CASE WHEN gg.POINTS IS NULL AND ga.POINTS>0 THEN 1 ELSE 0 END) AS UNGRADED
FROM students s
         JOIN schedule ss ON (ss.STUDENT_ID=s.STUDENT_ID AND ss.SYEAR='2025' AND ss.MARKING_PERIOD_ID IN ('1','2','4') AND CURRENT_DATE>=ss.START_DATE) JOIN course_periods cp ON (cp.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID AND cp.COURSE_PERIOD_ID='40')
         JOIN student_enrollment ssm ON (ssm.STUDENT_ID=s.STUDENT_ID AND ssm.SYEAR=ss.SYEAR AND ssm.SCHOOL_ID='1' AND (CURRENT_DATE>=ssm.START_DATE AND (ssm.END_DATE IS NULL OR CURRENT_DATE<=ssm.END_DATE))) JOIN gradebook_assignments ga ON (((ga.COURSE_PERIOD_ID=cp.COURSE_PERIOD_ID OR ga.COURSE_ID=cp.COURSE_ID) AND ga.STAFF_ID=cp.TEACHER_ID) AND ga.MARKING_PERIOD_ID='4') LEFT OUTER JOIN gradebook_grades gg ON (gg.STUDENT_ID=s.STUDENT_ID AND gg.ASSIGNMENT_ID=ga.ASSIGNMENT_ID AND gg.COURSE_PERIOD_ID=cp.COURSE_PERIOD_ID),gradebook_assignment_types gt WHERE gt.ASSIGNMENT_TYPE_ID=ga.ASSIGNMENT_TYPE_ID AND gt.COURSE_ID=cp.COURSE_ID
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            AND (gg.POINTS IS NOT NULL
        OR (ga.ASSIGNED_DATE IS NULL OR CURRENT_DATE>=ga.ASSIGNED_DATE)
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     AND (ga.DUE_DATE IS NULL OR CURRENT_DATE>=(ga.DUE_DATE + INTERVAL 0 DAY))
        OR CURRENT_DATE>(SELECT END_DATE FROM school_marking_periods WHERE MARKING_PERIOD_ID=ga.MARKING_PERIOD_ID)) AND (gg.POINTS IS NOT NULL OR ga.DUE_DATE IS NULL OR ((ga.DUE_DATE>=ss.START_DATE AND (ss.END_DATE IS NULL OR ga.DUE_DATE<=ss.END_DATE)) AND (ga.DUE_DATE>=ssm.START_DATE AND (ssm.END_DATE IS NULL OR ga.DUE_DATE<=ssm.END_DATE)))) AND s.STUDENT_ID='385' GROUP BY gt.ASSIGNMENT_TYPE_ID,gt.FINAL_GRADE_PERCENT,s.STUDENT_ID
;

SELECT s.STUDENT_ID,
       gt.ASSIGNMENT_TYPE_ID,
       ga.title,
       sum(CASE WHEN gg.POINTS < 0 THEN '0' ELSE gg.POINTS END)                           AS PARTIAL_POINTS,
       sum(CASE WHEN gg.POINTS < 0 THEN '0' ELSE ga.POINTS END)                           AS PARTIAL_TOTAL,
       gt.FINAL_GRADE_PERCENT,
       sum(CASE
               WHEN gg.POINTS < 0 THEN '0'
               ELSE
                   (CASE WHEN ga.WEIGHT IS NULL THEN '0' ELSE ga.WEIGHT END) END)         AS PARTIAL_WEIGHT,
       sum(CASE WHEN gg.POINTS < 0 THEN '0' ELSE (gg.POINTS / ga.POINTS) * ga.WEIGHT END) AS PARTIAL_WEIGHTED_GRADE
FROM students s
         JOIN schedule ss
              ON (ss.STUDENT_ID = s.STUDENT_ID AND ss.SYEAR = '2025' AND ss.MARKING_PERIOD_ID IN ('1', '2', '4')
                  AND ('2026-01-30' >= ss.START_DATE
                      AND ('2026-01-30' <= ss.END_DATE
                          OR ss.END_DATE IS NULL)))
         JOIN course_periods cp ON (cp.COURSE_PERIOD_ID = ss.COURSE_PERIOD_ID AND cp.COURSE_PERIOD_ID = '40')
         JOIN student_enrollment ssm ON (ssm.STUDENT_ID = s.STUDENT_ID
    AND ssm.SYEAR = ss.SYEAR
    AND ssm.SCHOOL_ID = '1' AND ('2026-01-30' >= ssm.START_DATE
        AND (ssm.END_DATE IS NULL OR '2026-01-30' <= ssm.END_DATE)))
         JOIN gradebook_assignments ga ON
    (((ga.COURSE_PERIOD_ID = cp.COURSE_PERIOD_ID
        OR ga.COURSE_ID = cp.COURSE_ID)
        AND ga.STAFF_ID = cp.TEACHER_ID)
        AND ga.MARKING_PERIOD_ID = '4')
         LEFT OUTER JOIN gradebook_grades gg ON
    (gg.STUDENT_ID = s.STUDENT_ID
        AND gg.ASSIGNMENT_ID = ga.ASSIGNMENT_ID
        AND gg.COURSE_PERIOD_ID = cp.COURSE_PERIOD_ID),
     gradebook_assignment_types gt
WHERE TRUE
  AND gt.ASSIGNMENT_TYPE_ID = ga.ASSIGNMENT_TYPE_ID
  AND gt.COURSE_ID = cp.COURSE_ID
  AND (gg.POINTS IS NOT NULL
    OR (ga.ASSIGNED_DATE IS NULL OR CURRENT_DATE >= ga.ASSIGNED_DATE)
           AND (ga.DUE_DATE IS NULL OR CURRENT_DATE >= ga.DUE_DATE)
    OR CURRENT_DATE > (SELECT END_DATE
                       FROM school_marking_periods
                       WHERE MARKING_PERIOD_ID = ga.MARKING_PERIOD_ID))
  AND (gg.POINTS IS NOT NULL
    OR ga.DUE_DATE IS NULL
    OR ((ga.DUE_DATE >= ss.START_DATE
        AND (ss.END_DATE IS NULL OR ga.DUE_DATE <= ss.END_DATE))
        AND (ga.DUE_DATE >= ssm.START_DATE
            AND (ssm.END_DATE IS NULL OR ga.DUE_DATE <= ssm.END_DATE))))
  AND ga.POINTS > 0
and s.student_id = 385
GROUP BY gt.ASSIGNMENT_TYPE_ID, ga.title, gt.FINAL_GRADE_PERCENT, s.STUDENT_ID;