+ Post New Thread
Results 1 to 5 of 5
Scripts Thread, MRBS Insert Period Script in Coding and Web Development; I need a SQL script to insert a period in the middle without effecting existing reservations. When MRBS is set ...
  1. #1

    Join Date
    Oct 2012
    Location
    St Paul
    Posts
    5
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    MRBS Insert Period Script

    I need a SQL script to insert a period in the middle without effecting existing reservations.

    When MRBS is set to use Periods instead of times it uses minutes after 12:00 to hold the period number. For example:
    Period 1 ->12:00
    Period 2 ->12:01
    Period 3 ->12:02
    Period 4 ->12:03
    Period 5 ->12:04
    Period 6 ->12:05
    Period 7 ->12:06

    Now if I change the period structure by adding a new period at the top of the list I can just run

    Code:
    UPDATE mrbs_entry SET start_time=start_time+60, end_time=end_time+60;
    UPDATE mrbs_repeat SET start_time=start_time+60, end_time=end_time+60, end_date=end_date+60;
    This will add 60 seconds to every entry so I can add a period at the top. My problem is how do I adjust the script so I can insert a period in the middle:

    Period 1 ->12:00
    Period 2 ->12:01
    Period 3 ->12:02
    Period 4 ->12:03
    Advisory ->12:04
    Period 5 ->12:05
    Period 6 ->12:06
    Period 7 ->12:07

    I need to add 60 seconds to any entry 12:04 or later in the day. The problem is that the date/time is stored as an integer. I have used SQL for years but I am not a coder. I think I need to use a CAST to convert from integer to date/time and then parse it to find the ones with an hour of 12 and minutes between 0 and 59 (MRBS only supports 60 periods). Then use the above UPDATE commands to add 60 seconds to any minute greater than a specified value. This would probably have to be done for each of the 5 values above one at a time.

    Most of our sites are using MRBS 1.2.1 so we don't currently have a mix of Periods and Times but we have a few test sites using MRBS 1.4.8 where you can have some reservations that are Period based and others that are Time based. Is there a Field in the new structure that determines if it is a Period or Time reservation? We might need to have that as an option as well.

    Thanks for any help you can give.

  2. #2

    Join Date
    Oct 2012
    Location
    St Paul
    Posts
    5
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Update, I forgot to mention this is running on MySQL.

    I also found the commands to read the Hour and Minute as integers
    HOUR(FROM_UNIXTIME(start_time))
    MINUTE(FROM_UNIXTIME(start_time))

  3. #3

    Join Date
    Oct 2012
    Location
    St Paul
    Posts
    5
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Ok, I think I have this worked out. It could probably use some cleanup.

    Code:
    UPDATE mrbs_entry 
     SET start_time = IF(MINUTE(FROM_UNIXTIME(start_time)) > 3, start_time+60, start_time),
     end_time = IF(MINUTE(FROM_UNIXTIME(end_time)) > 4, end_time+60, end_time)
    WHERE (HOUR(FROM_UNIXTIME(start_time)) = 12 AND HOUR(FROM_UNIXTIME(end_time)) = 12);
    
    UPDATE mrbs_repeat 
     SET start_time = IF(MINUTE(FROM_UNIXTIME(start_time)) > 3, start_time+60, start_time),
     end_time = IF(MINUTE(FROM_UNIXTIME(end_time)) > 4, end_time+60, end_time), 
     end_date = IF(MINUTE(FROM_UNIXTIME(end_time)) > 4, end_date+60, end_date)
    WHERE (HOUR(FROM_UNIXTIME(start_time)) = 12 AND HOUR(FROM_UNIXTIME(end_time)) = 12)
    Any way to set a variable and then call it to replace the 3 and 3+1=4?

  4. #4

    Join Date
    Oct 2012
    Location
    St Paul
    Posts
    5
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Here is my final code. Maybe this can be copied into the MRBS program as part of a GUI for managing Periods (then again with all the different databases it supports this might be hard).

    Code:
    /*
    This script is for adjusting existing reservations when a new period is being added in the middle of the period list.
    Break should be set to the last period before the new one is inserted.  Note that periods are numbered from 0 not 1.
    If Break is set to 4 then the 5th period will remain unchanged and all later periods will have one period added to them.
    If a reservation is more than one period long and spans the new period then it will be expanded to include the new period.
    If this is not desired then it will have to be manually adjusted.
    */
    SET @break = 4;
    UPDATE mrbs_entry
     SET start_time = IF(MINUTE(FROM_UNIXTIME(start_time)) > @break, start_time+60, start_time),
     end_time = IF(MINUTE(FROM_UNIXTIME(end_time)) >  @break+1), end_time+60, end_time)
    WHERE (HOUR(FROM_UNIXTIME(start_time)) = 12 AND HOUR(FROM_UNIXTIME(end_time)) = 12);
    UPDATE mrbs_repeat 
     SET start_time = IF(MINUTE(FROM_UNIXTIME(start_time)) > @break, start_time+60, start_time),
     end_time = IF(MINUTE(FROM_UNIXTIME(end_time)) >  @break+1), end_time+60, end_time), 
     end_date = IF(MINUTE(FROM_UNIXTIME(end_time)) >  @break+1), end_date+60, end_date)
    WHERE (HOUR(FROM_UNIXTIME(start_time)) = 12 AND HOUR(FROM_UNIXTIME(end_time)) = 12)

  5. #5

    Join Date
    Oct 2012
    Location
    St Paul
    Posts
    5
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    I also figured out what to do in MRBS 1.4.8 if you have a mixed environment using period and time based schedules.

    Code:
    --Note if site has both Period and Time based schedules in different Areas then you must filter by Room ID.
    --Look in the mrbs_room table and determine which rooms are in the Peroid based Area and note the IDs.
    --Add the line below to each WHERE statement with the appropriate room numbers.
    AND room_id IN (4,5)



SHARE:
+ Post New Thread

Similar Threads

  1. SIMS to MRBS: Script Available
    By McTedo in forum Scripts
    Replies: 105
    Last Post: 21st March 2014, 01:16 PM
  2. Setting MRBs to use periods
    By BKGarry in forum Web Development
    Replies: 4
    Last Post: 14th May 2013, 08:27 AM
  3. MRBS Producing extra Periods
    By Rhys in forum Educational Software
    Replies: 4
    Last Post: 30th August 2011, 04:34 PM
  4. MRBS: Can't get my periods
    By chrisjako in forum Network and Classroom Management
    Replies: 18
    Last Post: 24th March 2011, 09:59 AM
  5. MRBS Scripts
    By walkden-high in forum Windows Server 2000/2003
    Replies: 2
    Last Post: 7th June 2009, 10:47 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
  •