MRBS Insert Period Script

Printable View

12th October 2012, 03:03 PM
CygnusX-1

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.
12th October 2012, 05:33 PM
CygnusX-1

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))
12th October 2012, 08:54 PM
CygnusX-1

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?
15th October 2012, 07:21 PM
CygnusX-1

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)
17th October 2012, 05:00 PM
CygnusX-1

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)