CMIS Export of room data into format for room booking system

[humbug1873]

Okay I've been fiddling around a little with the CMIS data (they should really offer an API for access).

My problems are that we have a two week timetable. The data itself is modeled quite strangely as it not uses database fileds like weekID or periodid.

Instead our week is 14 days long starting on Sunday and the periods are entered as fixed values in a string (d'oh).

Luckily the database is poorly designed so a lot of information is flying around (now I finally know why the data consistency in CMIS is so bad). This is what I've come up with so far. You put in a start-date and an end-date and you get a list of busy periods.

Of course this is highly specific to our school but I guess you should be able to modify.


-- In date range
-- Out busy periods
declare @startDate as datetime
declare @endDate as datetime
declare @setID as char(10)

set @startdate = '20091101'
set @enddate = '20091130'
set @setID= '2009/2010'

select cm.mapdate,tt.lecturerid, tg.groupcode, 'Lesson' =
CASE
WHEN tt.startTime = '08:45' THEN '0'
WHEN tt.startTime = '09:05' THEN '1'
WHEN tt.startTime = '09:15' THEN '1'
WHEN tt.startTime = '11:05' THEN '2'
WHEN tt.startTime = '13:25' THEN '3'
END,
tt.starttime, tt.roomid
from ccalmaps cm
inner join timetable tt on
tt.setid = cm.setid and tt.weekday=cm.dayposn
inner join teachinggroups tg on
tg.setid=cm.setid and tg.groupid=tt.groupid
where cm.setid=@setID
and cm.mapdate <= @endDate
and cm.mapdate >= @startDate
and tt.roomid is not null
-- and tt.roomid=@room
order by roomid,mapdate, lesson

.... now how time to put this into the mrbs_entry table ...

[michael2k6]

@Humbug1873

Thank you for offering assistance to Ranj. It's good to see everyone on the forum giving some input.

I don't have experience myself in the roombooking side of our software as i am in a different part of the operations team. I was merely suggesting that it might be worth looking into.

We do offer an API, but this is for use by our partners, not by individuals.
This could be achieved using a TSQL or could as was suggested by someone be a simple spreadsheet (obviously the disadvantage is you can't automate that). The advantage to it is that you can create the report in Facility and you don't need to work out the database.

Thanks again for your input, it's nice to see users helping each other out.
Mic @ Serco, outside of work hours.

My posts are always my own, and have nothing to do in any way whatsoever with my employer.

[ranj]

A year has gone by and now the time has come to reimport the new timetable from CMIS into our booking system but am having a few problems getting the correct data out of CMIS in the first place.

Last year from the help of others on this thread I managed to compile a SQL statement which gave me the information for me to extract data out of tables on CMIS (day, start and finish time of a lesson, the teachers code or lectureid, the room they taught in, the teaching group and year group).

select timetable.weekday, timetable.starttime, timetable.finishtime,
timetable.lecturerid, timetable.roomid, teachinggroups.groupcode,
timetable.courseyear
from timetable inner join teachinggroups
on timetable.groupid = teachinggroups.groupid
where timetable.setid='2009/2010';

I thought this should work for the new timetable 2010/2011 so I ran the same query its given me nearly 7000 records which I though GREAT but the information it has reported back doesnt correspond with the real information in CMIS. Seems like there has been a lost in translation with some of the data. All the tables seem to be correct until it gets to 'groupcode' and then produces conflicting information. e.g. it says for a particular member of staff who would normally teach Science is now teaching English.

My knowledge with databases is limited so was wondering if someone could have a look at my query and see what could be wrong. As far as I know the table names haven't changed and are unlikely to change in SQL and I think if they had changed then it wouldn't produce a report. When I try to run the statement without the 'innerjoin' it does seem to be giving correct information but that is useless to me because I need to know what teacher is teaching which group.

If a school is running CMIS if you could try to run that CMIS statement and see what it produces for you both in the current dataset and an old data set and see if the information it provides is as you expect it to be. Remember that you will need to change the timetable.setid to correspond with how your dataset naming is set on CMIS e.g. 2008 or 08 etc.

If anyone could advise on this as I have run out of things to try.

Thanks

[pauljonze]

Try:

select timetable.weekday, timetable.starttime, timetable.finishtime,
timetable.lecturerid, timetable.roomid, teachinggroups.groupcode,
timetable.courseyear
from timetable inner join teachinggroups
on timetable.groupid = teachinggroups.groupid and timetable.setid = teachinggroups.setid
where timetable.setid='2010/2011'
and timetable.weekday>0;

[robk]

My sql for this is

SELECT STUD_ADMIN.TIMETABLE.RoomId, STUD_ADMIN.TIMETABLE.WeekDay, STUD_ADMIN.TIMETABLE.StartTime, STUD_ADMIN.TIMETABLE.Duration, STUD_ADMIN.TIMETABLE.WeekId, STUD_ADMIN.TEACHINGGROUPS.GroupCode, STUD_ADMIN.LECTURER.Name, STUD_ADMIN.LECTURER.DispLectId FROM (STUD_ADMIN.TIMETABLE INNER JOIN STUD_ADMIN.TEACHINGGROUPS ON STUD_ADMIN.TIMETABLE.GroupId = STUD_ADMIN.TEACHINGGROUPS.GroupId) INNER JOIN STUD_ADMIN.LECTURER ON STUD_ADMIN.TEACHINGGROUPS.LecturerId = STUD_ADMIN.LECTURER.LecturerId WHERE (((STUD_ADMIN.TIMETABLE.RoomId)='$room') AND ((STUD_ADMIN.TIMETABLE.SetId)='$setid') AND ((STUD_ADMIN.TEACHINGGROUPS.SetId)='$setid') AND ((STUD_ADMIN.LECTURER.SetId)='$setid')) ORDER BY STUD_ADMIN.TIMETABLE.RoomId, STUD_ADMIN.TIMETABLE.WeekDay, STUD_ADMIN.TIMETABLE.StartTime;

Note the setid is on each table. If you don`t do that you will end up with one to many links as cmis reuses groupIDs etc.

Hope this helps.

[ranj]

Try:

select timetable.weekday, timetable.starttime, timetable.finishtime,
timetable.lecturerid, timetable.roomid, teachinggroups.groupcode,
timetable.courseyear
from timetable inner join teachinggroups
on timetable.groupid = teachinggroups.groupid and timetable.setid = teachinggroups.setid
where timetable.setid='2010/2011'
and timetable.weekday>0;

Thanks PaulJonze. Running that query has produced the results we required for our booking system. The extra good news also is now its noting producing records for other non timetabled groups. For example in the last query it would pick up things like department teaching time, personal tutoring time but now it is only showing records for a teacher teaching a particular subject at a particular time and this is far better for the room booking system as last time I remember a lot of the exporting information needed to be removed prior to the import.

My knowledge with database's isn't great. Can I ask what the 'setid' does?

Thanks again

[SkywOrca]

SetId in the database is the dataset identifier, which is used throughout Facility to split the data up into chunks of academic year data. Basically, if you're looking at data that should be recorded by dataset and joining tables together you'll almost always need to put links between the SetId fields in the ON clause. The ON clause is used to identify which items of data in each table should be congruent for the statement as a whole to return only rows that are linked.

[michael2k6]

As i said this time last year... good effort guys.