Complete Timetable Output query

Administration
1

What’s the output expected with the Complete Timetable Output query? I have created the timetables for the current academic year, and placed classes in periods, as evidenced in Home > Timetable > View Master Timetable.

But when I run the query there is no output.

Thanks

Tieku

2

Hi Tieku,

This query should return a row for every teacher involved in every lesson of every class taught in all timetables in the current year:

However, on closer inspection I’ve realised that it is not returning rows for lessons without a facility set. I’ve now fixed this by changing JOIN gibbonSpace to LEFT JOIN gibbonSpace. You can resync your queries to enjoy this change.

Let me know if this helps!

Ross

3

Thanks Ross,

That would mean the same change should be made to query Complete Student Timetable Output.

Can you modify Complete Timetable Output so that it also includes classes with no teacher assigned? This result will help rebuild all timetables for the new year since there is no Copy All To Next Year feature for timetables.

Thanks

4

Hi Tieku,

Any of the built in queries can be duplicated so that you can modify them for your specific needs, likely by changing the JOINS to LEFT JOINS.

5

Hi Sandra,

Yes I know I can make copies of the queries. I was asking which line to modify to include classes with no teacher assigned. Thanks

6

Hi Tieku,

Something like the following should work:

`SELECT 
    gibbonCourse.name AS course,
    gibbonCourse.nameShort AS courseShort, 
    gibbonCourseClass.nameShort AS classShort,
    gibbonTTDay.name AS day,
    gibbonTTColumnRow.name AS period,
    substr(gibbonTTColumnRow.timeStart, 1, 5) AS timeStart,
    substr(gibbonTTColumnRow.timeEnd, 1, 5) AS timeEnd,
    gibbonSpace.name AS facility,
    gibbonPerson.surname,
    gibbonPerson.preferredName
 FROM 
    gibbonCourseClass
    JOIN gibbonCourse ON (gibbonCourseClass.gibbonCourseID=gibbonCourse.gibbonCourseID) 
    JOIN gibbonTTDayRowClass ON (gibbonTTDayRowClass.gibbonCourseClassID=gibbonCourseClass.gibbonCourseClassID)
    JOIN gibbonTTDay ON (gibbonTTDayRowClass.gibbonTTDayID=gibbonTTDay.gibbonTTDayID)
    JOIN gibbonTTColumnRow ON (gibbonTTDayRowClass.gibbonTTColumnRowID=gibbonTTColumnRow.gibbonTTColumnRowID)
    JOIN gibbonTTColumn ON (gibbonTTColumnRow.gibbonTTColumnID=gibbonTTColumn.gibbonTTColumnID)
    LEFT JOIN gibbonSpace ON (gibbonTTDayRowClass.gibbonSpaceID=gibbonSpace.gibbonSpaceID)
    LEFT JOIN gibbonCourseClassPerson ON (gibbonCourseClassPerson.gibbonCourseClassID=gibbonCourseClass.gibbonCourseClassID)
    LEFT JOIN gibbonPerson ON (gibbonCourseClassPerson.gibbonPersonID=gibbonPerson.gibbonPersonID AND gibbonCourseClassPerson.role='Teacher')
    LEFT JOIN gibbonTTDayRowClassException ON (gibbonTTDayRowClassException.gibbonTTDayRowClassID=gibbonTTDayRowClass.gibbonTTDayRowClassID)
WHERE 
    gibbonCourse.gibbonSchoolYearID=:schoolYear
    AND gibbonTTDayRowClassExceptionID IS NULL
GROUP BY gibbonTTDayRowClass.gibbonTTDayRowClassID
ORDER BY day, period, courseShort, classShort ;`
```