view release on metacpan or  search on metacpan

lib/App/Dochazka/REST/Model/Schedule.pm  view on Meta::CPAN

# ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
# POSSIBILITY OF SUCH DAMAGE.
# ************************************************************************* 

package App::Dochazka::REST::Model::Schedule;

use 5.012;
use strict;
use warnings;
use App::CELL qw( $CELL $log $meta $site );
use App::Dochazka::REST::Model::Shared qw( cud decode_schedule_json load load_multiple select_single );
use Data::Dumper;
use JSON;
use Params::Validate qw( :all );
use Try::Tiny;

# we get 'spawn', 'reset', and accessors from parent
use parent 'App::Dochazka::Common::Model::Schedule';




=head1 NAME

App::Dochazka::REST::Model::Schedule - schedule functions




=head1 SYNOPSIS

    use App::Dochazka::REST::Model::Schedule;

    ...



=head1 DESCRIPTION

A description of the schedule data model follows.



=head2 Schedules in the database


=head3 Table

Schedules are stored the C<schedules> table. For any given schedule, there is
always only one record in the table -- i.e., individual schedules can be used
for multiple employees. (For example, an organization might have hundreds of
employees on a single, unified schedule.) 

      CREATE TABLE IF NOT EXISTS schedules (
        sid        serial PRIMARY KEY,
        schedule   text UNIQUE NOT NULL,
        disabled   boolean,
        remark     text
      );

The value of the 'schedule' field is a JSON array which looks something like this:

    [
        { low_dow:"MON", low_time:"08:00", high_dow:"MON", high_time:"12:00" },  
        { low_dow:"MON", low_time:"12:30", high_dow:"MON", high_time:"16:30" },  
        { low_dow:"TUE", low_time:"08:00", high_dow:"TUE", high_time:"12:00" },  
        { low_dow:"TUE", low_time:"12:30", high_dow:"TUE", high_time:"16:30" },
        ...
    ]   

Or, to give an example of a more convoluted schedule:

    [   
        { low_dow:"WED", low_time:"22:15", high_dow:"THU", high_time:"03:25" }, 
        { low_dow:"THU", low_time:"05:25", high_dow:"THU", high_time:"09:55" },
        { low_dow:"SAT", low_time:"19:05", high_dow:"SUN", high_time:"24:00" } 
    ] 

The intervals in the JSON string must be sorted and the whitespace, etc.
must be consistent in order for the UNIQUE constraint in the 'schedule'
table to work properly. However, these precautions will no longer be
necessary after PostgreSQL 9.4 comes out and the field type is changed to
'jsonb'.

The 'disabled' field is intended go be used to control which schedules get
offered in, e.g., front-end dialogs when administrators choose which schedule
to assign to a new employee, and the like. For example, there may be schedules
in the database that were used in the past, but it is no longer desirable to 
offer these schedules in the front-end dialog, so the administrator can "remove"
them from the dialog by setting this field to 'true'.


=head3 Process for creating new schedules

It is important to understand how the JSON string introduced in the previous
section is assembled -- or, more generally, how a schedule is created. Essentially,
the schedule is first created in a C<schedintvls> table, with a record for each
time interval in the schedule. This table has triggers and a C<gist> index that 
enforce schedule data integrity so that only a valid schedule can be inserted.
Once the schedule has been successfully built up in C<schedintvls>, it is 
"translated" (using a stored procedure) into a single JSON string, which is
stored in the C<schedules> table. This process is described in more detail below:  

First, if the schedule already exists in the C<schedules> table, nothing
more need be done -- we can skip to L<Schedhistory>

If the schedule we need is not yet in the database, we will have to create it.
This is a three-step process: (1) build up the schedule in the C<schedintvls>
table (sometimes referred to as the "scratch schedule" table because it is used
to store an intermediate product with only a short lifespan); (2) translate the
schedule to form the schedule's JSON representation; (3) insert the JSON string
into the C<schedules> table.

The C<schedintvls>, or "scratch schedule", table:

      CREATE SEQUENCE scratch_sid_seq;

      CREATE TABLE IF NOT EXISTS schedintvls (
        int_id  serial PRIMARY KEY,
        ssid    integer NOT NULL,
        intvl   tsrange NOT NULL,