[sims] Timetables from SIMS

[TimH]

As a newcomer to SIMS I've been looking at getting from it a few things that we used to have on our old (in-house) system.

 

Current task is to get from SIMS some form of timetable which I can use to populate our room booking system (MRBS). With our old system I could check if the timetable had changed and update MRBS automatically every night.

 

So, I need to be able to extract from SIMS (ideally programmatically - commandlinereporter ?) some form of timetable that I can then parse, ideally room centric, though by member of staff and student would ultimately be handy (we used to email students their timetables automatically).

 

Any ideas, tips, pointers welcome, tia,

 

tim

[CAM]

Your timetable should be stored on Nova-T4 or Nova-T6. Which of those programs does your timetabler use? (Ideally T6 as T4 is due to be phased out).

 

Also, reading again, why do you need to check daily if the timetable has changed? Won't it be easier for the timetabler to EMail changes to you?

Edited July 3, 2009 by CAM

[dhicks]

So, I need to be able to extract from SIMS (ideally programmatically - commandlinereporter?) some form of timetable that I can then parse

 

Can you write a SIMS report that produces a timetable? Can it be run by CommandReporter?

 

--

David Hicks

[penfold_99]

As a newcomer to SIMS I've been looking at getting from it a few things that we used to have on our old (in-house) system.

 

Current task is to get from SIMS some form of timetable which I can use to populate our room booking system (MRBS). With our old system I could check if the timetable had changed and update MRBS automatically every night.

 

So, I need to be able to extract from SIMS (ideally programmatically - commandlinereporter ?) some form of timetable that I can then parse, ideally room centric, though by member of staff and student would ultimately be handy (we used to email students their timetables automatically).

 

Any ideas, tips, pointers welcome, tia,

 

tim

 

This is something i ahev been talking with a colleague about. You can export any report in sims as a csv file.

 

It would just be automating the import into mbrs.

[TimH]

Your timetable should be stored on Nova-T4 or Nova-T6. Which of those programs does your timetabler use? (Ideally T6 as T4 is due to be phased out).

 

T6

 

Also, reading again, why do you need to check daily if the timetable has changed? Won't it be easier for the timetabler to EMail changes to you?

 

Because the process is automated, a nightly script checks the timetable and if necessary repopulates mrbs.

[TimH]

Can you write a SIMS report that produces a timetable? Can it be run by CommandReporter?

 

This is what I haven't (yet) worked out how to do !

[TimH]

This is something i ahev been talking with a colleague about. You can export any report in sims as a csv file.

 

What I can't see is how to do this with a timetable.

 

It would just be automating the import into mbrs.

 

Have had that since we started with mrbs a few years ago. I decided the table structure too fiddly to alter directly so use a Python script to parse the timetable then fetch the urls as if a person were making the bookings (logged in as a special user).

 

tim

[penfold_99]

What I can't see is how to do this with a timetable.

tim

 

You could create a report that exports the timetable as csv.

[dhicks]

What I can't see is how to do this with a timetable.

 

We don't have the timetabling module in SIMS, so I don't know how to either. If you figure it out, try and run the report with the CommandReporter tool and see what XML you get. If you have a moment spare you could maybe post it here so we can all have a look.

 

--

David Hicks

[TimH]

You could create a report that exports the timetable as csv.

 

That's what I can't/couldn't figure out how to do but I think it's my fault for trying too soon, the timetable model only went in this week and we don't yet have the full timetable available. I can see a teacher's timetable on the "Staff Timetable" page but can't get a meaningful report of this.

 

Maybe I should just sit and wait a bit longer !

 

tim

[howartp]

Current task is to get from SIMS some form of timetable which I can use to populate our room booking system (MRBS).

I tried this last summer and got nowhere, but I'm back on it again today for hopefully linking into Exchange 2007 calendars.

 

We do use MRBS for our computer rooms though, so it'd be great if we could get this going - I love the idea of generating URL calls to do the donkey work for us - that would be fantastic!

 

Peter

[howartp]

I think I've got somewhere with the Timetable from SIMS into CSV bit...

 

If you do a report based on 'staff' then add the following columns:

 

Staff: Forename, Surname

Lessons: Room, ClassName, Period

 

(You can use whatever staff identifiers you want to pull out - I've used forename and surname here, although we have a UDF for student usernames, so I probably need to populate that for staff as well)

 

In the right-hand column of the Fields page, click on 'Lessons' then click the little red book icon that appears in the right-hand 'toolbar' column, entitled 'define settings for this subreport'. In here, create filter for the RoomNames so it only pulls out the rooms you want, and untick 'Include records with no such lessons'.

 

Fill in the rest of the report design, setting it to Text -> CSV output. And bob's your uncle, so to speak.

 

It comes out as:

 

ForeName, Surname, Room, ClassName, Period

Joe,Bloggs,Room1,7xIT1,BWed:2

,,Room1,9yIT3,AThur:5

,,Room3,12gAi2,BFri:3

Mary,Smith,Room38,7yEn3,BWed:3

,,Room38,7xEn4,BWed:3

 

etc - in other words, you'd need to assume the teacher is the same person until a new value is found.

 

Now to get it into MRBS and Exchange - can I leave you to play with the MRBS solution? I'm subscribed to this thread so I'll see any updates or questions.

 

Peter

[howartp]

Oh, I forgot the CommandReporter bit...

 

As long as you have no parameters being prompted for at runtime then this is easy. If you are being prompted for parameters, either do without them then use your scripting method to filter them, or read the SDK for how to pass in the parameters. I've previously done parameterised reports as well - it isn't hard, just needs the destructions to be followed.

 

The following line will then work:

 

CommandReporter.exe /user:me /password:pswd /report:"CSVTimetables" /output:"c:\CSV_Rooms.csv"

 

Obviously substituting a username and password of someone who has ThirdPartyReporting rights, and your report name and output location.

 

The beauty of the commandreporter that I've just discovered is it populates the staff name on every record, not just next-staff records, contrary to my previous post.

 

Peter

[Gatt]

What I can't see is how to do this with a timetable.

 

 

 

Have had that since we started with mrbs a few years ago. I decided the table structure too fiddly to alter directly so use a Python script to parse the timetable then fetch the urls as if a person were making the bookings (logged in as a special user).

 

tim

 

Any chance I can get a peak at that Python script?

[TimH]

That's what I can't/couldn't figure out how to do but I think it's my fault for trying too soon, the timetable model only went in this week

 

No, not my fault, as we are new to SIMS I was using future effective dates to try this. A supportnet ticket has led to KB89878 - you can't produce future reports for periods. So when they fix this/it becomes next year it will do as expected.

[TimH]

Oh, I forgot the CommandReporter bit...

 

The beauty of the commandreporter that I've just discovered is it populates the staff name on every record, not just next-staff records, contrary to my previous post.

 

 

Isn't that related to "suppress duplicates" in the report definition ?

 

 

tim

[TimH]

Now to get it into MRBS and Exchange - can I leave you to play with the MRBS solution? I'm subscribed to this thread so I'll see any updates or questions.

 

ATM, because I can't test on report data, I've had to do it with a timetable export from NovaT6 but it wasn't too difficult to tweak my old version which read from our old in-house timetable.

 

What I might try it to convert the NovaT export so that it resembles what could be produced via a report and tweak the script to read that.

 

 

tim

Edited July 20, 2009 by vikpaw
quote tag missing

[MattMitchell]

Peter - how do you get hold of the sdk?

[howartp]

Tim,

 

I've just uploaded a sample report output from mine - it's a .txt file to upload, but rename it to .csv and it should work?

 

Peter

Rooms.txt

[howartp]

Peter - how do you get hold of the sdk?

The SDK exists on a default install, it's just the .chm help file you need.

 

It isn't really an SDK, it's an executable (CommandReporter.exe) which takes certain parameters to run reports on demand.

 

I've just zipped it and attached to this post.

 

Once unzipped you'll probably need to right-click on it, go to properties and tick/untick the box which is to do with unblocking content from a remote location - otherwise you'll get a load of blank pages when you open the .chm

 

Peter

SIMS_SDK.zip

[TimH]

Tim,

 

I've just uploaded a sample report output from mine - it's a .txt file to upload, but rename it to .csv and it should work?

 

Thanks, I was just looking at the possibilities.

 

I see you have AMon and BMon, does that indicate a two week timetable ?

 

That should be manageable, I book each timetabled class individually for 8 weeks ahead rather than using repeat bookings (which I originally did) - there was a reason,...errm maybe to allow booking managers to hand remove a single booking from a set.

 

When mrbs is updated I send a clear all timetabled bookings command then refill, it takes 20 mins or so but as it runs by schedule at night that doesn't matter.

 

tim

[howartp]

Thanks, I was just looking at the possibilities.

 

I see you have AMon and BMon, does that indicate a two week timetable ?

 

That should be manageable, I book each timetabled class individually for 8 weeks ahead rather than using repeat bookings (which I originally did) - there was a reason,...errm maybe to allow booking managers to hand remove a single booking from a set.

 

When mrbs is updated I send a clear all timetabled bookings command then refill, it takes 20 mins or so but as it runs by schedule at night that doesn't matter.

 

tim

Hi Tim,

 

Yes, we run a two week timetable.

 

The 'weeks' skip over the holidays, so if it is week A in last week of term, week B would be the first week of the next term whether it is a one-week or two-week holiday. This breaks the repeating feature, but if you manually create entries 'by hand' in the script, that should be tweakable to skip the holidays.

 

What do you do about staff making bookings; if you wipe the timetabled entries and regenerate them all, does it delete any manual bookings that clash with new timetabled entries?

 

Peter

[TimH]

What do you do about staff making bookings; if you wipe the timetabled entries and regenerate them all, does it delete any manual bookings that clash with new timetabled entries?

 

Sorry for not getting back sooner, have got lots of little jobs like this on the go ATM, will post script for booking later - I've ripped apart and simplified my old version.

 

To clear timetable entries I added a script to our mrbs (only a few lines, will include with booking script when I get there) that says

 

delete from $tbl_entry where create_by=\"timetable\"

 

The automated bookings are made as a fake user "timetable", so any that have been hand adjusted won't be touched as they will have been created by a real person !

 

Likewise if an entry has replaced a timetabled one and the automatic booker tries to replace with timetable, it fails silently on that period.

 

tim

[howartp]

Hi Tim,

 

I'm on holiday for a fortnight now - I'll come back to this thread when I return.

 

Peter

[howartp]

I've been playing with this today, and got it all working.

 

Unfortunately if you have any support staff assisting in lessons (ie two teachers per lesson) then you'll end up with clashed bookings.

 

I thought I'd go back into SIMS and play with it, but my report now tells me there is no data for my report. Hopefully it is related to the issue where you can't run period reports for future dates - I guess you can't run them for past dates either? It's a shame if so.

 

Peter