+ Post New Thread
Page 2 of 2 FirstFirst 12
Results 16 to 23 of 23
MIS Systems Thread, CMIS Export of room data into format for room booking system in Technical; Okay I've been fiddling around a little with the CMIS data (they should really offer an API for access). My ...
  1. #16

    Join Date
    Apr 2009
    Posts
    6
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    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 ...

  2. #17

    Join Date
    Jun 2007
    Location
    Wakefield, West Yorkshire
    Posts
    625
    Thank Post
    96
    Thanked 130 Times in 101 Posts
    Rep Power
    67
    @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.

  3. #18
    ranj's Avatar
    Join Date
    Feb 2006
    Location
    Birmingham
    Posts
    730
    Thank Post
    98
    Thanked 42 Times in 32 Posts
    Rep Power
    25
    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

  4. #19
    pauljonze's Avatar
    Join Date
    May 2008
    Location
    Staffs
    Posts
    86
    Thank Post
    17
    Thanked 22 Times in 15 Posts
    Rep Power
    22
    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;

  5. Thanks to pauljonze from:

    ranj (17th August 2010)

  6. #20
    robk's Avatar
    Join Date
    Nov 2005
    Location
    Ashbourne
    Posts
    693
    Thank Post
    172
    Thanked 128 Times in 107 Posts
    Blog Entries
    1
    Rep Power
    47
    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.

  7. #21
    ranj's Avatar
    Join Date
    Feb 2006
    Location
    Birmingham
    Posts
    730
    Thank Post
    98
    Thanked 42 Times in 32 Posts
    Rep Power
    25
    Quote Originally Posted by pauljonze View Post
    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

  8. #22
    SkywOrca's Avatar
    Join Date
    Sep 2008
    Posts
    88
    Thank Post
    1
    Thanked 18 Times in 17 Posts
    Rep Power
    47
    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.

  9. #23

    Join Date
    Jun 2007
    Location
    Wakefield, West Yorkshire
    Posts
    625
    Thank Post
    96
    Thanked 130 Times in 101 Posts
    Rep Power
    67
    As i said this time last year... good effort guys.

SHARE:
+ Post New Thread
Page 2 of 2 FirstFirst 12

Similar Threads

  1. Room booking system on Moodle
    By BatchFile in forum Virtual Learning Platforms
    Replies: 11
    Last Post: 22nd July 2009, 07:38 PM
  2. In/Out board & room booking system for outlook?
    By cokecan72 in forum Windows
    Replies: 1
    Last Post: 16th April 2009, 02:07 PM
  3. Meeting Room Booking System - MRBS
    By pmassingham in forum How do you do....it?
    Replies: 4
    Last Post: 12th February 2009, 11:03 AM
  4. Replies: 0
    Last Post: 31st October 2007, 01:25 PM
  5. Meeting Room Booking System - examples config inside!
    By db260179 in forum Network and Classroom Management
    Replies: 7
    Last Post: 25th July 2007, 07:07 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •