CMIS Export of room data into format for room booking system

[ranj]

Hi

 

I am currently making an enquiry to establish whether the following is possible in CMIS.

 

Enquiring with a number of companies about a resource booking system built using web technologies (so can be access via a web browser in school or out of school), the system we are looking for is quite bespoke, for this to happen I need to pull out certain information from our MIS system CMIS and the information I need is

 

Every room in the school that could be booked (classrooms, lecture rooms, IT rooms, hall) along with the teacher who is teaching for that period (if its a classroom) and the lesson being taught in there (e.g. biology, maths etc).

 

I then need this information in either a comma separated CSV or text format, XML would be great also.

 

I know in CMIS there is a way to do this but you can only print this information out, not been able to find a way to do this via an export.

 

Has anyone else done something along the lines to this.

 

If anyone could help it would be greatly appreciated.

 

Thanks

[budgester]

Do it directly from the database using SQL.

 

But good luck with that because the database is not very well designed.

[robk]

If you run a report, you can right click and select export to give a tab delimited text file, or leave data on the clipboard that can be pasted into excel.

 

You then just need a report that lists all the timetable events.

 

You could have a look at the cmis database directly, but Serco would not support this!

 

Robk

[ranj]

 

You could have a look at the cmis database directly, but Serco would not support this!

 

Robk

 

Yeh I know, want to avoid this as much as possible. Serco do not like you touching the data using SQL. Would prefer not to do this also because if they have changed the data structure it means code needing to be rewritten again.

 

Easiest option is to use a report in CMIS.

[michael2k6]

Hi,

 

What has been said is correct. The easiest way to do this is report. What i can't understand is why if you're using it for room bookings you need a list of which rooms are free, not which rooms are in use and by whom? I might of read it wrong or got the wrong idea - apologies in advance if i have.

 

We have our own room booking system in ePortal which does just that. Phone sales and ask them about it. I negates the need for importing and exporting etc.

If it does not meet your needs, and you choose to use a different system, then one which runs completely stand alone to the database and just works using exports is going to ruin your timetable. If you want to find a member of staff and you're using two softwares like that, you'd first need to lookup where they should be, then lookup on the other software whether theyve moved?

 

What you need is something that can read and write. We wouldnt support any software to do this unless they are one of our partners. I'm not sure whether or not we have partner software that can do this, but if that's a route you'd like to go to PM me your details and i'll have someone contact you and let you know (else i'll find out and contact you myself).

 

@Robk - If he had a list of events it would not include free rooms. Now i've re read the post i think what he's after is a report of all rooms and what's in them right now, so he can see which are free and what's in the others - a list of the events occuring now would only produce the rooms with peoples in.

 

Mic @ Serco

[robk]

I have been working on a simalar system, though I only needed to know which rooms were timetabled so could book the others. I am only dealing with eight rooms though.

 

Robk

[ranj]

This is more of a question for birmingham schools. I remember somewhere on the bgfl site there were some user guides for CMIS. I am struggling to find them now. In particular I am looking for one to do with reporting. I want to see if there is anything in their guides to advise me on how I could export the information I need into a file.

[Guest monkeyx]

Or you could just try running:-

 

select * from rooms where setid='2009/2010';

 

where setid is your current acadamic year set name.

 

From the Options/Adavnced/Execute SQL Menu

 

The resulting output can the be copied into excel, or maybe just excute the code from within your app Serco are not as restrictive as Capita about doing this sort of thing.

 

Tim

[ranj]

Or you could just try running:-

 

select * from rooms where setid='2009/2010';

 

where setid is your current acadamic year set name.

 

From the Options/Adavnced/Execute SQL Menu

 

The resulting output can the be copied into excel, or maybe just excute the code from within your app Serco are not as restrictive as Capita about doing this sort of thing.

 

Thanks for this. Is there anyway I can get a list of timetable events so I can adjust the query as I need it to give me a bit more information such as whos in what room on a particular period for a particular day as that has just given me the room around the whole school.

 

If not do you know of possible table names which could possibly give me this sort of information such as events or timetables table?

 

thanks

[greatone]

What you need is something like this:

 

SELECT weekday, starttime, finishtime, lecturerid, roomid, courseyear from STUD_ADMIN.TIMETABLE where setid='2009/2010'

 

This is assuming that you have your database set up in the way that Serco advise with the tables owned by STUD_ADMIN

[ranj]

What you need is something like this:

 

SELECT weekday, starttime, finishtime, lecturerid, roomid, courseyear from STUD_ADMIN.TIMETABLE where setid='2009/2010'

 

This is assuming that you have your database set up in the way that Serco advise with the tables owned by STUD_ADMIN

 

Thats great, that statement is more in the line of what I need. the only information I am missing is the subject information or teaching group. I thought the table name may be subjectid but that didnt work.

 

Does anyone know what the table name is for subject or teaching group?

 

thanks

[greatone]

Try adding 'ClassGroupID' to the query

 

This may contain the info you are looking for

[humbug1873]

Mic,

 

now this gave me something to laugh about. The room booking as it is in ePortal has quite a lot of flaws:

 

* I get exceptions all the time trying to book a room.

* I can't even see who is in there.

* access rights management!? (i.e. who can actually cancel a booking etc) .. I have to admit I haven't been that far yet as even the 'simple' things won't work.

* Last but not least ...it's really complicated to use.

 

Maybe in a far far future in a funny/happy place eportal room booking will actually work, Right now it's a waste of money and time.

 

-- Rene

 

We have our own room booking system in ePortal which does just that. Phone sales and ask them about it. I negates the need for importing and exporting etc.

If it does not meet your needs, and you choose to use a different system, then one which runs completely stand alone to the database and just works using exports is going to ruin your timetable. If you want to find a member of staff and you're using two softwares like that, you'd first need to lookup where they should be, then lookup on the other software whether theyve moved?

Mic @ Serco

[humbug1873]

What you need is something like this:

 

SELECT weekday, starttime, finishtime, lecturerid, roomid, courseyear from STUD_ADMIN.TIMETABLE where setid='2009/2010'

 

This is assuming that you have your database set up in the way that Serco advise with the tables owned by STUD_ADMIN

 

That is a 'nice' beginning. But now comes the hard part translating the WeekDay ID into a 'date'.

 

At least if you are using a two week timetable - as my school is doing.

 

Suddenly life becomes complicated especially as the week structure in the database is a bit of a mystery to me.

 

There a table 'weekstructure' that has a week number and a start date. Unfortunately in mu case the start date of a week is always 'Tuesday' even though Facility thinks different.

 

Anybody now if there is some mapping going on elsewhere I am intensely curious about that.

 

My ultimate goal is to have an importer script that copies the current terms timetable into MRBS. Any other booking can then be done in mrbs.

[greatone]

That is exactly what I do except that I am not using MRBS for our booking system.

 

In the CMIS table 'Timetable' there is a column 'WeekID'. As we use a one week timetable my column is all set to '1' . My guess is that yours will have both '1' and '2'

 

I would ignore the CMIS week mappings and as you know when week1 and week2 is create a script to populate the mrbs_entry table based on your start date.

 

There are several threads on here regarding importing timetables into MRBS but none of them seem to have come to a positive result

[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.