Room Balancing


In order to help our children's workers not be overrun by too many kids ending up in one class we (in our old system) would keep a running tally of how many kids are in each room and then tell parents what room to go to during check-in. With the new system we want this automated. We have currently hard-coded our local source to always balance rooms until we code in a "proper" way to turn it off and on.
What we are proposing is this. A new occurrence type attribute of "Balance Room" that would behave as follows. First, instead of only taking the first matching occurrence while filtering occurrences, instead all matching occurrences will be accepted during filtering. Once that is done each "time slot" will be walked through to determine which specific occurrence a person should be placed in with the following logic:
a) If all matching occurrences have the Balance option turned ON, then the occurrence with the fewest people in it's room is selected. If two or more occurrences share the same location then the first match with the lowest number of occupants is selected.
b) If all matching occurrences have the Balance option turned OFF, then the first match is used (current functionality).
c) If some matching occurrences have the Balance option turned on and some have it turned off, then the first non-balancing match is used. This provides the ability to have a "primary" location that is first filled to capacity and then secondary locations can be used that will balance attendance so as to not overwhelm a single teacher.
If there are no concerns with this logic I will work on this patch and post for review.


nairdo wrote Aug 26, 2009 at 3:49 PM

I'd like to review this with Jason too (he's currently OOO) but option c) implies capacity limits. Is this something that you are also going to code since the module doesn't currently use the room's 'max people' and if so are you just going to factor this in or were you planning on performing 'auto close' on the room when capacity is reached?

cabal95 wrote Aug 27, 2009 at 5:58 PM

That is a good point. I'll either post a forum question or e-mail arena directly and ask them how this (room limits) is handled in their check-in module. My initial thought/concern would be if a room has a limit of 20, and has 15 kids currently; and 10 kiosks all sitting on the last screen just before the final database update occurs and they all hit "next" at the same time, do we let the room overfill by 5 or throw an error for the last 5? My gut feeling is that would be a pretty rare thing to not worry, but I would still like to make sure we do it the same way as Arena for consistency sake if possible.

I think the room balancing code could technically be a separate patch from the room limits code, though. We "just" need to add another filter function that checks the room limits (max limit and ratio limit) that gets called like the age/grade, attributes, etc. filter functions. Once that patch is in place the room(occurrence) would never even make it down to the balancing section of code.

nairdo wrote Sep 22, 2009 at 9:23 PM

Research Note: Since the Arena Location classes CurrentCount property is time constrained (it's based on the current time) we are going to create a LocationExtension that will be able to calculate a location's attendance count based on a given time.

cabal95 wrote Sep 22, 2009 at 10:53 PM

Does it make sense to have the extension (and possibly related stored procedure) take as parameters the "service time" to be checked rather than a specific start and end time? e.g. (very pseudo code):

myLocation.AttendanceForService(DateTime.Parse('2009-09-20 11:00:00'));

function AttendanceForService(DateTime serviceTime)
return Run_Sql_Stored_Procedure('cust_cccev_checkin_sp_realAttendanceCount', this.location_id, serviceTime);

stored proc cust_cccev_checkin_sp_realAttendanceCount(@location_id, @serviceTime)
select count(*)
from core_occurrence_attendance as coa
left join core_occurrence as co on co.occurrence_id = coa.occurrence_id
where co.location_id = @location_id
  and co.occurrence_start_time = @serviceTime

Then we can call, in the room balancing code, myLocation.AttendanceForService(myOccurrence.occurrence_start_time)
instead of having to "guess" when the correct start and stop times are. (occurrences that have different check-in start/stop times, etc.)

nairdo wrote Oct 14, 2009 at 5:30 PM

Yes, that sounds right. The extension method (GetHeadCountByDate) takes the occurrence start time (and the instance location's id) and uses a new sp called "cust_cccev_ckin_sp_get_location_head_count_by_date" to determine the count as follows:
SELECT COUNT(distinct oa.person_id)
FROM core_occurrence O
INNER JOIN core_occurrence_attendance OA ON O.occurrence_id = OA.occurrence_id
WHERE O.location_id = @LocationID
AND (O.occurrence_start_time <= @StartDate OR check_in_start <= @StartDate)
AND (O.occurrence_end_time >= @StartDate OR check_in_end >= @StartDate)
AND OA.attended = 1
AND OA.check_in_time IS NOT NULL
AND OA.check_in_time <> '1/1/1900'
AND OA.check_out_time = '1/1/1900'
This new sp will need to be included in the upgrade/install sql script. Which reminds me... we need to create a spot in the repo for including all the sql artifacts. I'll open a brief discussion on that topic now.

nairdo wrote Oct 14, 2009 at 6:17 PM

Also, one more point of clarification for those reading this topic. The way we all agreed "Room Balancing" would work differs slightly from the original description on this thread. It now works like this:

An Occurrence Type (aka Attendance Type) has an attribute (via our custom OccurrenceTypeAttribute) called IsRoomBalancing. Any location/room that are added to the OccurrencType (via the Attendance Type details edit screen) will partake in the room balancing operation.

wrote Oct 14, 2009 at 6:26 PM

wrote Oct 14, 2009 at 6:27 PM

wrote Nov 16, 2009 at 10:24 PM

wrote Feb 1, 2013 at 3:23 AM

wrote May 14, 2013 at 1:43 AM

wrote May 14, 2013 at 1:43 AM

wrote Jun 12, 2013 at 12:10 AM