SIMS to MRBS: Script Available

[walkden-high]

Hi J17 Sparky

 

I never managed to get the script to work for me neither I do have a method of block booking rooms for up to 8 weeks if you are interested, I have attached 2 spreadsheets that uses a formula to put the correct time stamp in the periods once you have selected the date eg 05/01/10 the first day of term in Jan.

 

You will have edit the formula in B-4 to match your periods it is currently setup to find the time stamp for our 5 periods then all you do is fill fill in the fields that are in yellow.

 

our periods below

 

before school

period 1

period 2

break

period 3

period 4

lunch

period 5

after school

 

once I have my spreadsheet filled in correctly I then save it as a tab delimited txt file then import in to mrbs_entry with php myadmin straight in to the database.

 

unix time stamp finder

 

UNIX Timestamp To Standard Time Calculator

 

Hope this can help, let me know if you have any problems

 

Justin

Room Booking Template 8 Weeks-Summertime.xls

Room Booking Template 8 Weeks-Winter.xls

[Guest Guest]

Cheers for that.

 

Ive put the entries in until feb half term so will have to wait till then to try your spreedsheet.

 

Cheers

[RobFuller]

Anyone come across this error before?

 

Windows IIS7 Application Event Log -

 

php[4364]

PHP Fatal error: require_once() [function.require]: Failed opening required 'HTTP/Request.php' (include_path='.;C:\php5\pear') in D:\Websites\IRBS\bulkYearMake.php on line 170

 

php[4364]

PHP Notice: Undefined offset: 26 in D:\Websites\IRBS\bulkYearMake.php on line 143

 

php[4364]

PHP Notice: Undefined offset: 2 in D:\Websites\IRBS\bulkYearMake.php on line 129

 

php[4364]

PHP Notice: Undefined offset: 2 in D:\Websites\IRBS\bulkYearMake.php on line 127

[Minkus]

Dear all,

 

Revisited this script for the coming academic year (it was originally written by a former pupil at my school) and realised that many people were struggling to get it to work.

 

I have now rewritten the script so that it no longer depends on the PEAR HTTP_Request module, which seemed to be causing most of the problems. It now uses iframes to accomplish the same goal instead, which also has the advantage that it will probably work on almost any copy of MRBS, so long as you can get into it from a web browser.

 

I've also made a few other fixes and alterations to make the script work with the latest version of MRBS (1.4.7) and easier to use.

 

To install:

1. Extract bulkYearMake.php and copy it to your main MRBS folder
   
2. Extract Room Timetables.RptDef and import it into SIMS.Net
   

Edit bulkYearMake.php according to your own school's needs:

• Default entry type for booking, and an alternative type for certain subjects if you want - see first few lines of the script.
  
• MRBS period setup - search for 'PERIOD EXPLANATION' inside the source for more details. You need to tell it how the periods in the SIMS export file match up to the periods in MRBS. If you're not sure, you could just run it against your MRBS as-is, but make a back up first!
  
• (You don't need to map room names to numbers in the script any more - see below).

 

To use:

1. Run the 'Room Timetables' report under SIMS.Net, select a single room, and save the file it generates
   
2. EDIT the file that SIMS.Net generated - replace the name of the room (e.g. "Z") with the internal MRBS 'room' ID (e.g. "1"). To find the MRBS 'room' ID, open a room in MRBS, and look in the address bar for 'room=#'. It should look something like this when you are done:
   

   "Room","Day name","Period","Class","Initials","Subject code","Students"
   "3","Fri","Fri:1","9cr/Gr2","RS  ","Gr",22
   "3","Fri","Fri:10","8C/Ma","HO  ","Ma",32
   "3","Fri","Fri:2","9cr/Gr2","RS  ","Gr",22
   ...

   
   

3. Navigate to bulkYearMake.php in your Web browser, change the 'Start of Term' and 'End of Term' fields if you want to, select the file you edited earlier, and click 'Go'. (You only need to change the edit_entry_handler.php field if you put bulkYearMake.php into a different area of the website to your main MRBS install).
   
4. The script should load & create a series of frames within itself, each of which represents an attempt to book a period into MRBS. It may take some time to load - be patient! If it all works OK, you should see each of these displaying the MRBS calendar, and the necessary entries created. If not, you may see an MRBS error message (which you need to resolve yourself!) or a Scheduling Conflict (which means you need to clear out the room bookings before you start).
   
5. Make sure you get rid of bulkYearMake.php from your MRBS directory when you're done. It's not insecure in the way it accesses the MRBS database, but it was hacked together & is probably full of XSS vulnerabilities.

I hope a few more people manage to get this one to work. I have tested it pretty extensively here and it seems to work fine.

 

I can't offer a huge amount of support for people wanting to get it to fit their timetable - you'll need to experiment yourself - and if it doesn't work in other MRBS auth schemes (we use IIS and 'nt') then I probably can't help there either. However any other problems drop a message in this post and I'll see what I can do to help.

 

It's released under the GPL as before, so if you do make any changes that others might find helpful (one idea would be to modify the script so that it can handle more than one room at once), please post them back here so that we can all benefit.

SIMStoMRBS1.1.zip

Edited September 16, 2011 by Minkus

[mikeglover]

I'm having issues working out what to change the periods to in the bulkYearMake.php. We dont have a Reg in the morning so currently when i import the timetable Mon:1 is appearring in the Lesson 2 slot on MRBS. This is the period script I have setup already:

if(substr($period[$item],4,3) == "RgA"){ // If this is an AM registration period

$start_secs = 12*60*60;

} elseif(substr($period[$item],4,3) == "RgP"){ // If this is a PM registration period

$start_secs = 12*60*60 + (60 * 9);

} else { // If this is an ordinary period

$this_period = (int)substr($period[$item],4,2); // Get the period number into an integer

if($this_period == 5 or $this_period == 6){$this_period += 1;} // If we are Periods 5 and 6, add one (to skip break)

elseif($this_period >= 7){$this_period += 3;} // If we are Period 7 or beyond, add three (to skip break, lunch and registration PM)

$start_secs = 12*60*60 + (60 * $this_period); // Convert the period number into seconds for MRBS

}

 

And the periods that appear in the SIMS csv are Mon:1, Mon:2, Mon3, Mon4, Mon5, Mon6. The script doesnt need to skip any breaks or lunches.

Can someone help me by giving me the correct script I need. Thanks

[Minkus]

Hi,

 

Try changing the last few lines to the following:

 

$this_period = (int)substr($period[$item],4,2); // Get the period number into an integer

$start_secs = (60 * $this_period); // Convert the period number into seconds for MRBS

 

This should mean the first period goes into Slot 1, and there are no breaks.

[mikeglover]

Hi,

 

Try changing the last few lines to the following:

 

 

 

This should mean the first period goes into Slot 1, and there are no breaks.

 

Hi, thanks for your reply but I'm afriad that doesn't work. So my code now look like this now:

 

if(substr($period[$item],4,3) == "RgA"){ // If this is an AM registration period

$start_secs = 12*60*60;

} elseif(substr($period[$item],4,3) == "RgP"){ // If this is a PM registration period

$start_secs = 12*60*60 + (60 * 9);

} else { // If this is an ordinary period

$this_period = (int)substr($period[$item],4,2); // Get the period number into an integer

$start_secs = (60 * $this_period); // Convert the period number into seconds for MRBS

}

 

When i run the script there are no errors but nothing is added into the timetable now. I double checked the Room ID.

[Minkus]

Sorry. Not thinking.

 

Try:

 

$this_period = (int)substr($period[$item],4,2); // Get the period number into an integer

$start_secs = 12*60*60 + (60 * ($this_period - 1)); // Convert the period number into seconds for MRBS

 

EDIT - Changed the formula - make sure you use this one!

[mikeglover]

Sorry. Not thinking.

 

Try:

 

$this_period = (int)substr($period[$item],4,2); // Get the period number into an integer

$start_secs = 12*60*60 + (60 * ($this_period - 1)); // Convert the period number into seconds for MRBS

 

EDIT - Changed the formula - make sure you use this one!

 

Thanks Chris this works a treat now

One step closer to the finish line

[glennda]

Does anybody have the Sims2MRBS report which includes registration periods?

[Minkus]

Hi,

 

The one included in the script should export registration periods - so long as they are available in SIMS.Net?

 

Maybe check with your timetabler to make sure this is the case?

[glennda]

Possibly they are done differently here! I will investigate more!

[howartp]

Fantastic script!

 

I've just modified ours for a two-week timetable.

 

Firstly around line 233 change:

$postData[12][1] = 2;

to

$postData[12][1] = 6;

Then change

$postData[16][1] = "";

to

$postData[16][1] = 2;

(This sets the repeat_type to "N-weekly" and the "N" to 2, so in other words all entries repeat bi-weekly.)

 

Then you need to split your CSV into each half of the schedule. We have a week 'A' and week 'B', so our days show as 'AMon:1' and 'BMon:1' etc. Split the CSV into two files, one with all of the 'A' week bookings and another for the 'B' weeks.

 

Now you need to modify the code around line 64 from:

if(strlen($entry[2]) == 5){ $entry[2] = substr($entry[2],0,4)."0".substr($entry[2],4,1); }

to

if(strlen($entry[2]) == 6){ $entry[2] = substr($entry[2],1,4)."0".substr($entry[2],5,1); }

This ignores the 'A' or 'B' on the front of the session. The result of AMon:1 should now be Mon:01, etc.

 

Now you just run the routine as it's designed, except when it asks you for the start of term, make sure you add 7 days to it when you're doing week 'B'. (So if term starts on 3rd September with week A, run this normally with your week A csv, then do the same from 10th September with your week B csv)

 

(The code line numbers might be slightly out because I messed about a bit before getting it right, but look for the code snippets)

 

Hope this helps someone.

 

Peter

[Minkus]

Hi Peter,

 

Great post, and thanks for sharing your changes with the rest of the forum!

 

Kind regards,

[PICNIC]

I wonder if you could help me with a problem - I think I have totally FUBARED the script trying to get it round our timetable. I can get it to run, but it ignores the term start date and tries to enter from the day I run the script and it also tries to input all the classes into the 'Before School' Period, we have 9 slots a day, Before School, 1, 2, break, 3, 4, lunch, 5 and 6.

 

I have applied howartp changes for a 2 week timetable and altered the periods how I thought they should be.

 

If you have a chance to look at it that would be great, if not no worries I like a challenge

 

import.zip

[howartp]

I wonder if you could help me with a problem - I think I have totally FUBARED the script trying to get it round our timetable. I can get it to run, but it ignores the term start date and tries to enter from the day I run the script and it also tries to input all the classes into the 'Before School' Period, we have 9 slots a day, Before School, 1, 2, break, 3, 4, lunch, 5 and 6.

 

I have applied howartp changes for a 2 week timetable and altered the periods how I thought they should be.

 

If you have a chance to look at it that would be great, if not no worries I like a challenge

Interesting, at a glance (using my Text Compare software) I can't see that anything you've changed should cause this behaviour.

 

You have only changed the lines that I would expect you to change, and they look 'sensible' whether they are correct or not.

 

Can you attach a copy of the SIMS export as it comes out of SIMS, before you strip it down?

 

Which of your periods are Registrations, and/or are they extra? (I see you've changed some RgA and RgP entries to 0, which I need to understand why so I can get my head around it for you)

 

For example, our timetable is Session1(Reg_AM), Session2, Break, Session3, Session4, Lunch, Session5, Session6. All Session5 marks are automagically entered into Reg_PM, but it is physically a seperate session in SIMS. In the morning however, session 1 IS the Reg session. (Session 1 is 20 minutes, as Reg would be, whereas session 5 is full hour taught lesson)

 

Peter

[PICNIC]

Thanks Peter

 

The above attached csv is exactly as sims exports, but i have stripped off the week 2 entries (bi weekly timetable) as I thought these would need to go in separately?. Sims does not contain any reg periods, just periods 1-6, maybe this is where I am misunderstanding?

 

The MRBS setup contains

 

$periods[] = "Before School";
$periods[] = "1 - 09:00 - 09:50";
$periods[] = "2 - 09:50 - 10:40";
$periods[] = "Morning Break";
$periods[] = "3 - 10:55 - 11:45";
$periods[] = "4 - 11:45 - 12:35";
$periods[] = "Lunch";
$periods[] = "5 - 13:25 - 14:15";
$periods[] = "6 - 14:15 - 15:05";

 

Messy but does the job

Edited July 27, 2012 by PICNIC

[howartp]

Thanks Peter

 

The above attached csv is exactly as sims exports, but i have stripped off the week 2 entries (bi weekly timetable) as I thought these would need to go in separately?. Sims does not contain any reg periods, just periods 1-6, maybe this is where I am misunderstanding?

 

The MRBS setup contains

 

 

Messy but does the job

Ah, right.

 

Firstly then, line 93 you've got periods 3 and 6 - this should be periods 3 (after break) and 5 (after lunch). This is checking the input from the SIMS file, so we use the period numbers as exported (ie 1-6)

 

ACTUALLY, IGNORE THE FOLLOWING BLOCK - I'VE JUST REALISED MRBS STARTS AT ZERO SO THIS ENTIRE BLOCK IS WRONG FOR YOUR PERIODS - I'LL LEAVE IT AS IT MAY HELP OTHERS WITH DIFFERENT PERIOD COMBINATIONS:

Secondly, around lines 192-200 (after the 'PERIOD EXPLANATION' comment section) the else section needs a minor modification. You are currently reading the period from SIMS, then if it's after break you are adding ONE to make it match MRBS's periods - but you have a period before school which you need to account for. So the lines need changing as follows:

 

if(substr($period[$item],4,3) == "0"){ // If this is an AM registration period
$start_secs = 12*60*60;
} elseif(substr($period[$item],4,3) == "RgP"){ // If this is a PM registration period
$start_secs = 12*60*60 + (60 * 9);
} else { // If this is an ordinary period
$this_period = (int)substr($period[$item],4,2); // Get the period number into an integer
if($this_period == 3 or $this_period == 4)[color="#FF0000"][b]{$this_period += 1;}[/b][/color] // If we are Periods 3 and 4, add [b][color="#FF0000"]one (to skip break)[/color][/b]
elseif($this_period >= 5)[b][color="#FF0000"]{$this_period += 2;}[/color][/b] // If we are Period 5 or beyond, add [color="#FF0000"][b]two (to skip break and lunch)[/b][/color]
$start_secs = 12*60*60 + (60 * $this_period); // Convert the period number into seconds for MRBS
}

 

if(substr($period[$item],4,3) == "0"){ // If this is an AM registration period
$start_secs = 12*60*60;
} elseif(substr($period[$item],4,3) == "RgP"){ // If this is a PM registration period
$start_secs = 12*60*60 + (60 * 9);
} else { // If this is an ordinary period
$this_period = (int)substr($period[$item],4,2); // Get the period number into an integer
if($this_period == 3 or $this_period == 4)[b][color="#FF0000"]{$this_period += 2;}[/color][/b] // If we are Periods 3 and 4, add [b][color="#FF0000"]two (to skip pre-school and break)[/color][/b]
elseif($this_period >= 5)[b][color="#FF0000"]{$this_period += 3;}[/color][/b] // If we are Period 5 or beyond, add [b][color="#FF0000"]three (to skip pre-school, break and lunch)[/color][/b]
$start_secs = 12*60*60 + (60 * $this_period); // Convert the period number into seconds for MRBS
}

 

Finally, can you check that your MRBS periods are in consecutive order - for example, you haven't added 'Before School' as an afterthought; if you have then its ID will be out of sync and the script won't work. To check, go to the booking page (eg Day.php) and hover over one of the free booking slots - the URL will show ....period=#..... - make sure the periods are consecutive starting from zero.

 

Peter

[PICNIC]

Thanks Peter, no time to look at this today, but I will report back once I do.

[Minkus]

Also make sure you've got a recent version of MRBS installed. The most recent version of the script is designed to work with MRBS 1.4.7 (not quite the latest), and they did change the booking form a few versions ago, which might account for the problems you're having.

 

If you're already using 1.4.7+, then that's probably not the problem!

[howartp]

Hi everyone,

 

Just noticed a bug with the script.

 

If your CSV has the same class on two consecutive rows and there isn't a break/lunch between them, the script assumes it is a double-period and extends the duration accordingly.

 

Unfortunately it doesn't check the DAY - so you could have the following situation, where the Thursday session is doubled and Tuesday's session doesn't get entered:

Room,Day name,Period,Class,Staff Code,Subject code,Students

17,BFri,BFri:4,10V2/Eg1,KS ,Eg,14

17,BFri,BFri:6,11D/Eg2,MDA ,Eg,21

17,BMon,BMon:3,11D/Eg1,AJB ,Eg,22

17,BThu,BThu:3,8x/Te5,CH ,Te,19

17,BThu,BThu:3,8x/Te5,KS ,Te,19

17,BThu,BThu:5,10B/Eg1,STH ,Eg,23

17,BTue,BTue:4,10B/Eg1,STH ,Eg,23

17,BWed,BWed:3,10B/Eg1,STH ,Eg,23

17,BWed,BWed:5,8x/Te5,CH ,Te,19

17,BWed,BWed:5,8x/Te5,KS ,Te,19

 

To resolve it, go to somewhere around line 94 where you have your version of the following:

 

if($duration[$listof[$i]] != 0){

while($class[$listof[$i]] == $class[$listof[$i+$j]] && substr($listof[$i+$j],4,3) != "RgA" && substr($listof[$i+$j],4,3) != "RgP" && substr($listof[$i+$j],4,2) != "03" && substr($listof[$i+$j],4,2) != "05"){

$duration[$listof[$i+$j]] = 0; // Set the following class to have a duration of zero - will be ignored

$duration[$listof[$i]]++; // Increment the length of this class

$j++; // Now check the next class after that

 

This says 'while the current class is the same as the next class, and....(checks your own break/lunch periods)'.

 

Add in the following:

&& $day[$listof[$i]] == $day[$listof[$i+$j]]

 

so it now says 'while the current class is the same as the next class, and the day is the same, and....(checks your own break/lunch periods)'.

if($duration[$listof[$i]] != 0){

while($class[$listof[$i]] == $class[$listof[$i+$j]] && $day[$listof[$i]] == $day[$listof[$i+$j]] && substr($listof[$i+$j],4,3) != "RgA" && substr($listof[$i+$j],4,3) != "RgP" && substr($listof[$i+$j],4,2) != "03" && substr($listof[$i+$j],4,2) != "05"){

$duration[$listof[$i+$j]] = 0; // Set the following class to have a duration of zero - will be ignored

$duration[$listof[$i]]++; // Increment the length of this class

$j++; // Now check the next class after that

 

Hope this helps.

 

Peter

[Minkus]

Thanks for the bug report Peter. When we do our import this year I'll make the change to our copy and upload a new version.

 

Kind regards,

Chris

[wesleyw]

When would this be uploaded?

 

Wes

[wesleyw]

How would I change this to cope with two week timetables?

[Minkus]

Dear all,

 

Version 1.2 of the script is now available, with a minor bug fix spotted by howartp - thank you very much!

 

This is the only change to the script - if version 1.1 worked OK for you, just wait until next year before updating.

 

To install:

 

1. Extract bulkYearMake.php and copy it to your main MRBS folder
   
2. Extract Room Timetables.RptDef and import it into SIMS.Net
   

 

Edit bulkYearMake.php according to your own school's needs:

 

• Default entry type for booking, and an alternative type for certain subjects if you want - see first few lines of the script.
  
• MRBS period setup - search for 'PERIOD EXPLANATION' inside the source for more details. You need to tell it how the periods in the SIMS export file match up to the periods in MRBS. If you're not sure, you could just run it against your MRBS as-is, but make a back up first!
  
• (You don't need to map room names to numbers in the script any more - see below).
  

 

To use:

 

1. Run the 'Room Timetables' report under SIMS.Net, select a single room, and save the file it generates
   
2. EDIT the file that SIMS.Net generated - replace the name of the room (e.g. "Z") with the internal MRBS 'room' ID (e.g. "1"). To find the MRBS 'room' ID, open a room in MRBS, and look in the address bar for 'room=#'. It should look something like this when you are done:
   

   "Room","Day name","Period","Class","Initials","Subject code","Students"
   "3","Fri","Fri:1","9cr/Gr2","RS  ","Gr",22
   "3","Fri","Fri:10","8C/Ma","HO  ","Ma",32
   "3","Fri","Fri:2","9cr/Gr2","RS  ","Gr",22
   ...

   
   

3. Navigate to bulkYearMake.php in your Web browser, change the 'Start of Term' and 'End of Term' fields if you want to, select the file you edited earlier, and click 'Go'. (You only need to change the edit_entry_handler.php field if you put bulkYearMake.php into a different area of the website to your main MRBS install).
   
4. The script should load & create a series of frames within itself, each of which represents an attempt to book a period into MRBS. It may take some time to load - be patient! If it all works OK, you should see each of these displaying the MRBS calendar, and the necessary entries created. If not, you may see an MRBS error message or a Scheduling Conflict. If you get an error message, try importing again, just in case this helps. If you get a scheduling conflict, and you are running MRBS 1.4.8 or above, you should be able to tell it to ignore the conflict if you want to.
   
5. Make sure you get rid of bulkYearMake.php from your MRBS directory when you're done. It's not insecure in the way it accesses the MRBS database, but it was hacked together & is probably full of XSS vulnerabilities.
   

 

The script was tested with the latest version of MRBS (1.4.8), and let me encourage you to upgrade to this if you haven't done so already, as it supports the ability to ignore scheduling conflicts for repeat entries and create as many entries as it can, which is great if you already have bookings in your database.

 

It's released under the GPL as before, so if you do make any changes that others might find helpful (one idea would be to modify the script so that it can handle more than one room at once), please post them back here so that we can all benefit.

SIMStoMRBS1.2.zip