Block full day but allow unblock of specific times

[jberanek]

Hello,

I have an interesting problem that I am trying to solve with MRBS. I would like to be able to easily bulk update all the rooms and areas defined as BLOCKED, and then only open up certain timeslots so that users can then see what they have available left to book.

So essentially, there is a football field that is booked for the whole day, but then I know that the field is available from 4-5pm for a practice and then all different coaches in the community would be able to see that the field is available and request to run their practice there.

As I have about 30 fields to manage, there is no apparent way to achieve this.

I thought of doing the reverse which was only booking spots as AVAILABLE, but then those are blocked from a user being able to use the workflow to reserve that spot as it is already blocked.

Any suggestions or ideas???

Reported by: *anonymous

Original Ticket: mrbs/support-requests/500

[jberanek]

I probably haven't understood the problem. Can you not simply block book all the rooms in an area as booked, by selecting all the rooms in the edit_entry.php form and making a daily recurring booking, and then go and edit individual days (using Edit Entry on the view_entry.php page) as necessary?

Campbell

Original comment by: campbell-m

[jberanek]

Hello Campbell,

Let me explain a little further and maybe it will become more apparent as to what I am trying to achieve.

Fields can be booked for an hour, but they can start at 00, 15, 30, 45. This means that I have set the resolution to 15 minutes. The times that most fields are available are between 7am - 12:00am inclusive 7 days a week. The purpose of the booking system that I am putting together is to assist coaches in finding available fields for practices and then to use the existing workflow in MRBS so that only a field admin can approve the field request or not. The issue is that in most cases, the day is already booked with assigned practices and with games or other events, leaving very few slots left for coaches to utilize. For example, on a Monday, 1 field may have availability only from 9-10pm for the whole week. Then the following week it may have Wednesday from 7-9pm as available. This "adhoc" nature of fields available would apply to about 10 different areas, with about 3 fields in each area.

Essentially, I will be given a list of available times and fields that I would then need to add to MBRS. And by "add", as you described, would mean editing a full day appointment into 2 blocked time settings. For Field 1 on Monday, I would have to edit the full day appointment and effectively block from 7am-9pm and then add a second blocked entry from 10pm-12am. This second additional entry cannot be done from the same page. If the list I get during the beginning of the week is very small (less than 10), I would only need to spend about 1 hour or so to go through all of the "cleanup". But this becomes impossible to manage if the slots increase to 50+.

Using this method (I will name it the BLOCK-ALL process) also has other undesired effects such as the inability for users/coaches to search for available spots to then request them. Under the reports, there is no way to specify finding all available slots from multiple areas within a certain date range. The concept of available spots does not really exist in MRBS. For example, the ability to search by date range, for a specific field, that has a 90 minute slot available. This type of query is not possible.

So, then I thought, rather than trying to block and then unblock constantly, why not just "PRE-BOOK" the slots when the list is given to me and just name the brief description as "AVAILABLE". This then allows the search fields under reports to give the user quite a bit of options to find an available field. They can also see the fields in the MONTH calendar view. I could even use the color coding from the type (Internal/External) to help with this. This would also seem to be much easier for me to code a simple PHP page based on the testdata.php script, that is provided in MRBS, for me to "import" the list that is given to me. Unfortunately, using this method has other undesired effects. Mainly that users/coaches will look at fields and since it will look mostly blank/available, I can foresee them constantly requesting fields that are not available. Moreover, there is no way for them to try and reserve/request a field that I just imported as AVAILABLE. Clicking the slot that I added as available does not allow them to do anything but edit/delete/copy the existing available slot to kick off the "approval" process and workflow that I get from the "BLOCK-ALL" process.

Pros of "BLOCK-ALL" process: 1-Workflow Process for approval 2-Empty slots show up as white and people understand that it is "available"

Cons of "BLOCK-ALL" process: 1-Lots of work by the admin 2-Prone to errors 3-While editing of days, users can start booking available slots while you are trying to "edit" them 4-Reporting with search criteria for coaches/users is almost impossible to find available time slots 5-Constantly blocking future timeslots all the time adding extra admin work and room for errors 6-Difficult to automate

Pros of "PRE-BOOKING" process: 1-Reporting with search criteria becomes very easy to find available slots 2-Much less admin work to pre-book list of fields and timeslots 3-No need for excessive editing of days or to continuously block slots in the future 4-Use of Internal/External colors make it even easier for users to see which pre-booked slots are not available anymore or are confirmed. 5-Easier to automate

Cons of "PRE-BOOKING" process: 1-Users/coaches may request "white-space" in MRBS as it was designed creating excess confusion and additional workflow emails and junk on a regular basis 2-Users/coaches that want to "reserve" an available spot based on what was uploaded is not possible since they cannot kick-off the workflow for approval of a timeslot.

Based on the Pros/Cons, one would think that the "PRE-BOOKING" process would be best, however, MRBS would be total overkill IMHO, since ultimately, I could just create a page that has an excel sheet that is updated once a week when I have the timeslots available and then the users/coaches could always just download it and send me an email requesting the slot. Sure this isn't going to address the FIFO queueing that MRBS has with the ability to handle multiple users trying to book time as the DB locks the tables. But of course, since the "PRE-BOOKING" process cannot take advantage of this since this does not exist in the "PRE-BOOKING" process.

So here I am and unsure of how to proceed. Ultimately, an improvement to the "PRE-BOOKING" process would be to have the ability for a booking to have 3 states.

BOOKED-AVAILABLE BOOKED-TENTATIVE BOOKED-CONFIRMED

Then, when an admin puts up a BOOKED-AVAILABLE slot, users/coaches can move it from BOOKED-AVAILABLE to BOOKED-TENTATIVE and it would kick off the existing workflows of MBRS. The admin could then approve it to BOOKED-CONFIRMED, or reject it back to BOOKED-AVAILABLE, or request more information and keep it as BOOKED-TENTATIVE.

If the config file does not use the workflow, then the user/coach would change the state from BOOKED-AVAILABLE to BOOKED-CONFIRMED.

Furthermore, since the config file also has the concept of allowing only an admin to create an entry, enabling this option could mean that "PRE-BOOKING" con #1 would be addressed since I would expect that a user/coach changing the state of an existing BOOKED-AVAILABLE still to work since it is not a "NEW" entry but a modification to an existing entry. This would then allow for the admin to setup a bunch of BOOKED-AVAILABLE slots, prevent users/coaches from requesting slots that are not booked the admin thus making the white space "read-only", and give them the ability to use the workflow of changing the state from BOOKED-AVAILABLE to BOOKED-TENTATIVE and eventually to BOOKED-CONFIRMED.

Sorry for the lengthy resonse, but I wanted to ensure that it was as clear as possible as to what I was trying to accomplish. If you know of ways that I can already address any of the "BLOCK-ALL" or "PRE-BOOKING" pros or cons, please let me know.

Best regards, Nick

Original comment by: *anonymous

[jberanek]

Thanks for the explanation, but I think I'm still missing something!

I can't work out why you would need to pre-book something as available? Why not just show it as an empty slot? Are you aware of the booking approval process in MRBS, which I think would solve some of the problems you describe? This allows users to book a room subject to approval by an admin. When a user books a room an email is sent to the admin asking them to approve or reject the booking. (This is independent of the confirmed/tentative status which is designed to allow users to show that they're thinking of booking a room but are not 100% sure yet.)

I agree a "find a room" feature would be useful in MRBS, but my sense is that that's only half the issue here.

Incidentally the other thing you may not be aware of is the ability to adjust a booking by dragging its edge.

Apologies if you're already aware of all these things, but I'm still a bit puzzled!

Campbell

Original comment by: campbell-m

[jberanek]

Hello Campbell,

Cool!! Dragging the edge is definitely a time saver, however, how do we address the issue of users booking a time while I am editing the full day appointment to be able to open up timeslots? Sure I can keep rejecting the requests, but that is frustrating for users and me as the admin.

I guess I could always edit the config file to put a minimum and maximum date on MBRS and then create repeat appointments until that date.

$max_booking_date_enabled = TRUE; $max_booking_date = "2014-07-23"; // Must be a string in the format "yyyy-mm-dd"

This would effectively mean I do it once and then "forget" about it. But I still need to find an easy way to "insert" the available timeslots. Maybe setting 3 repeating blocks would work. I would set 7am-12pm, 12pm-7pm, 7pm-12am as blocked for all fields between max and min booking date for the season. This would then allow me to easily click and drag to make slots available as I get the list of available fields and times.

Secondly, yes, I am aware of the whole workflow for approving a reservation which is why I don't want to defer to the "PRE-BOOKING" process I described. This is a major CON for that method. I would prefer to make the "BLOCK-ALL" process work out.

One thing that I noticed is that the whole "Skip conflict" checkbox does not work. I actually need to keep it unselected, click on save, have it tell me there are conflicts, and then hit the new button that shows up "Skip and Book". If I select the checkbox and hit save, then I don't get a choice to "skip and book" in the subsequent screen.

With respect to finding room/field availability, clearly, the "PRE-BOOKING" process I describe has huge advantages because the only thing returned will be slots that coaches can choose from. Alternatively, an Availability report (find a room) would be very useful as mentioned by myself and you.

Any ideas on what an SQL query would look like to find open time during a day for one field? If I can at least understand the query, I could probably create a basic form that the user would enter startdate, enddate, field name, hit submit and get available timeslots.

Or I could create a table called mrbs_available that would have the list of fields and times that they could query, but I don't think this would be the best solution. A proper availability query/report based on some criterias would be best.

Thanks, Nick

Original comment by: *anonymous

[jberanek]

Good, glad you've now got dragging the edge of an existing booking. Another time saver that you may not be aware of is the ability to select a block of empty cells on the day and week views which will then take you into the edit_entry form with the times and rooms pre-selected. Just hold the mouse down while you drag the cursor to select a rectangle of cells.

The "Skip conflicts" checkbox is working the way that it's designed to, though I appreciate that it may not be the way you want it to. The idea is that if you check Skip Conflicts on the booking form it'll just bypass the next screen which will warn you about conflicts. You might want to do this because you already know about the conflicts, possibly from hovering over the little red cross, and decided you don't mind about them.

On the main issue though, I still don't understand the problem properly (or even at all!). I'm probably being a bit thick, but what I don't understand is why you can't just start with everything available, then you put in the bookings you know about and then let the users book any remaining free slots in the normal way? You can always shut out ordinary users until you've got all your bookings in by temporarily setting $auth['only_admin_can_book'] = TRUE; in the config file.

As for the SQL query, I'm not sure why you would need that? In the example you give, you can see which slots are free for any given field by looking at the Week or Month views.

Campbell

Original comment by: campbell-m

[jberanek]

Hi Campbell,

Click and drag, I was aware of for creation. I just did not know about the click and drag for edits.

On the main issue though, I still don't understand the problem properly (or even at all!). I'm probably being a bit thick, but what I don't understand is why you can't just start with everything available, then you put in the bookings you know about and then let the users book any remaining free slots in the normal way?

Because the list I get on a weekly basis is a list of fields that are FREE. By default, all fields are fully occupied. I don't control that. I am only given the remaining timeslots that are free. Hence I need to show that the fields are all blocked except for the fields that I am told are now available. This then allows the coaches to find available fields for friendly matches or additional practices.

You can always shut out ordinary users until you've got all your bookings in by temporarily setting $auth['only_admin_can_book'] = TRUE; in the config file.

The above would work as a workaround, but I think the three timeslots method that I explained would work pretty nicely as well.

As for the SQL query, I'm not sure why you would need that? In the example you give, you can see which slots are free for any given field by looking at the Week or Month views.

Day and Week show the "whitespace" nicely. The Month View does not. The month view would be useless actually because you cannot tell what is available. In the end, I think that we could live with the Day/Week views to have users find available slots. The one disadvantage is that the query would span all areas and all fields, while the week and month views are restricted to one field only. Even if I put all fields into 1 area, I don't think this would be good visually, plus the same limitations would exist.

As for the skip conflicts, I will have to take another look at it. The response screen seemed to indicate that the added fields were successful. Probably because the resulting screen only shows failures and not conflicts. So conflicts are skipped, but if there are some conditions that are not in conflict, then the submission continues. This method means that from the UI, you should never end up with two bookings at the same time or overlapping.

Regards, Nick

Original comment by: *anonymous

[jberanek]

To be clearer,

When the skip conflicts box is selected, the resulting screen only shows "errors". There is no indication that anything was added to the schedule. Maybe listing the successes and conflicts would make it clearer?

Thanks, Nick

Original comment by: *anonymous

[jberanek]

Because the list I get on a weekly basis is a list of fields that are FREE

Ah, OK! Now I understand the problem. Let me have a think about it.

Original comment by: campbell-m

[jberanek]

:-)

Thanks Campbell. I know it took some time to explain it, but I am glad that it now makes more sense.

Best Regards, Nick

Original comment by: *anonymous

[jberanek]

A patch to search for free slots have been posted on the list some time ago, IRC...

NdK

Original comment by: *anonymous

[jberanek]

As far as I understand the issues are

(1) that you want to be able to release slots when you are given the list of free slots by the third party, but you have a host of eager users ready to pounce on free slots the moment they see them appear. Breaking up a one day reservation at the moment involves temporarily creating a free slot, which isn't really free - it's just that you haven't had time to put in place the replacement.

(2) you would like some means of searching for free slots.

As far as (1) goes, I can think of the following solutions:

(a) book your placeholder reservations as 15 minute (ie the length of the resolution) slots instead of all day slots, but still keeping them as daily repeating series. Then when you get handed the list of free slots the ones you delete will be genuinely free. However the deletion process will be a bit cumbersome because you'll have lots of deletions to make.

(b) book your placeholder reservations as three bookings each day, as you suggested. This will involve fewer deletions, but may run the risk of users creeping in with new bookings before you are ready.

(c) preventing users making bookings while you are entering the free slots, either by setting $auth['only_admin_can_book'] or by taking the website offline

(d) if you get handed the list of free slots at the same time every week, eg Friday evening, then you could set a policy that new bookings for the coming week can't be made until after some fixed time, eg 0700 on Saturday morning. This would be a relatively simple piece of code to add.

On (2) the best thing to do is to look at how day_table_innerhtml() in functions_table.inc works. The day view is essentially finding the free slots for a fixed period, a day in this case, and presenting them graphically. The important thing to realise is that MRBS doesn't operate by having a table of slots in the database and then inserting bookings. Instead it operates by having a table of bookings. For this reason there's no simple SQL query that will give you the free slots. Instead the query has to find all the bookings that start before the end of the period and end after the start. Then you have to go through those bookings in PHP to build up the map of free slots.

Hope that helps and that I've understood the problem correctly.

Campbell

Original comment by: campbell-m

[jberanek]

A combination of 1c and 1b is what I will most likely use. I will start with 1b and if I see that users are sneaking in, then I will first perform 1c, and then 1b.

For (2), he idea is that there would be a little search page, or somehow add some fields to the Reports page that includes the start and end date of the search, and potentially specify area and/or rooms. The result would just be a table of available times based on the search criteria.

I will take a look at day_table_innerhtml in functions_table.inc looks...

Nick

Original comment by: *anonymous

[jberanek]

A previous poster mentioned that there was some code floating around? Any ideas on where I can get my hands on it? I took a look at the day_table_innerhtml and at this point, the sql is beyond my understanding. Start and end times for the search would be based on the way it is defined in the config file as well.

So I think what needs to be done is get a list of all blocks of starttime and endtime per day, per room between the start of the day and the end of the day (my days start from 7am-12am).

Then based on the results, look for "gaps" in time and list those out. The startdate should equal today and should not go past the defined end date...maybe a 2 week maximum.

As I think more and more about this, its getting more and more interesting. :-)

Nick

Original comment by: *anonymous

[jberanek]

A previous poster mentioned that there was some code floating around?

You'd have to search through the trackers for that. I suspect it's probably a little out of date by now though.

Campbell

Original comment by: campbell-m