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.

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

[QUOTE=monkeyx;402059]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