File Coverage

blib/lib/App/Dochazka/REST/Model/Schedule.pm
Criterion Covered Total %
statement 37 89 41.5
branch 0 22 0.0
condition 0 12 0.0
subroutine 13 19 68.4
pod 6 6 100.0
total 56 148 37.8


line stmt bran cond sub pod time code
1             # *************************************************************************
2             # Copyright (c) 2014-2017, SUSE LLC
3             #
4             # All rights reserved.
5             #
6             # Redistribution and use in source and binary forms, with or without
7             # modification, are permitted provided that the following conditions are met:
8             #
9             # 1. Redistributions of source code must retain the above copyright notice,
10             # this list of conditions and the following disclaimer.
11             #
12             # 2. Redistributions in binary form must reproduce the above copyright
13             # notice, this list of conditions and the following disclaimer in the
14             # documentation and/or other materials provided with the distribution.
15             #
16             # 3. Neither the name of SUSE LLC nor the names of its contributors may be
17             # used to endorse or promote products derived from this software without
18             # specific prior written permission.
19             #
20             # THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
21             # AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
22             # IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
23             # ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE
24             # LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
25             # CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
26             # SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
27             # INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
28             # CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
29             # ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
30             # POSSIBILITY OF SUCH DAMAGE.
31             # *************************************************************************
32              
33             package App::Dochazka::REST::Model::Schedule;
34              
35 41     41   3399 use 5.012;
  41         175  
36 41     41   205 use strict;
  41         76  
  41         766  
37 41     41   181 use warnings;
  41         74  
  41         1030  
38 41     41   200 use App::CELL qw( $CELL $log $meta $site );
  41         84  
  41         3862  
39 41     41   1394 use App::Dochazka::REST::Model::Shared qw( cud decode_schedule_json load load_multiple select_single );
  41         84  
  41         2145  
40 41     41   224 use Data::Dumper;
  41         94  
  41         1333  
41 41     41   199 use JSON;
  41         77  
  41         215  
42 41     41   3661 use Params::Validate qw( :all );
  41         91  
  41         5183  
43 41     41   266 use Try::Tiny;
  41         95  
  41         1770  
44              
45             # we get 'spawn', 'reset', and accessors from parent
46 41     41   221 use parent 'App::Dochazka::Common::Model::Schedule';
  41         141  
  41         218  
47              
48              
49              
50              
51             =head1 NAME
52              
53             App::Dochazka::REST::Model::Schedule - schedule functions
54              
55              
56              
57              
58             =head1 SYNOPSIS
59              
60             use App::Dochazka::REST::Model::Schedule;
61              
62             ...
63              
64              
65              
66             =head1 DESCRIPTION
67              
68             A description of the schedule data model follows.
69              
70              
71              
72             =head2 Schedules in the database
73              
74              
75             =head3 Table
76              
77             Schedules are stored the C<schedules> table. For any given schedule, there is
78             always only one record in the table -- i.e., individual schedules can be used
79             for multiple employees. (For example, an organization might have hundreds of
80             employees on a single, unified schedule.)
81              
82             CREATE TABLE IF NOT EXISTS schedules (
83             sid serial PRIMARY KEY,
84             schedule text UNIQUE NOT NULL,
85             disabled boolean,
86             remark text
87             );
88              
89             The value of the 'schedule' field is a JSON array which looks something like this:
90              
91             [
92             { low_dow:"MON", low_time:"08:00", high_dow:"MON", high_time:"12:00" },
93             { low_dow:"MON", low_time:"12:30", high_dow:"MON", high_time:"16:30" },
94             { low_dow:"TUE", low_time:"08:00", high_dow:"TUE", high_time:"12:00" },
95             { low_dow:"TUE", low_time:"12:30", high_dow:"TUE", high_time:"16:30" },
96             ...
97             ]
98              
99             Or, to give an example of a more convoluted schedule:
100              
101             [
102             { low_dow:"WED", low_time:"22:15", high_dow:"THU", high_time:"03:25" },
103             { low_dow:"THU", low_time:"05:25", high_dow:"THU", high_time:"09:55" },
104             { low_dow:"SAT", low_time:"19:05", high_dow:"SUN", high_time:"24:00" }
105             ]
106              
107             The intervals in the JSON string must be sorted and the whitespace, etc.
108             must be consistent in order for the UNIQUE constraint in the 'schedule'
109             table to work properly. However, these precautions will no longer be
110             necessary after PostgreSQL 9.4 comes out and the field type is changed to
111             'jsonb'.
112              
113             The 'disabled' field is intended go be used to control which schedules get
114             offered in, e.g., front-end dialogs when administrators choose which schedule
115             to assign to a new employee, and the like. For example, there may be schedules
116             in the database that were used in the past, but it is no longer desirable to
117             offer these schedules in the front-end dialog, so the administrator can "remove"
118             them from the dialog by setting this field to 'true'.
119              
120              
121             =head3 Process for creating new schedules
122              
123             It is important to understand how the JSON string introduced in the previous
124             section is assembled -- or, more generally, how a schedule is created. Essentially,
125             the schedule is first created in a C<schedintvls> table, with a record for each
126             time interval in the schedule. This table has triggers and a C<gist> index that
127             enforce schedule data integrity so that only a valid schedule can be inserted.
128             Once the schedule has been successfully built up in C<schedintvls>, it is
129             "translated" (using a stored procedure) into a single JSON string, which is
130             stored in the C<schedules> table. This process is described in more detail below:
131              
132             First, if the schedule already exists in the C<schedules> table, nothing
133             more need be done -- we can skip to L<Schedhistory>
134              
135             If the schedule we need is not yet in the database, we will have to create it.
136             This is a three-step process: (1) build up the schedule in the C<schedintvls>
137             table (sometimes referred to as the "scratch schedule" table because it is used
138             to store an intermediate product with only a short lifespan); (2) translate the
139             schedule to form the schedule's JSON representation; (3) insert the JSON string
140             into the C<schedules> table.
141              
142             The C<schedintvls>, or "scratch schedule", table:
143              
144             CREATE SEQUENCE scratch_sid_seq;
145              
146             CREATE TABLE IF NOT EXISTS schedintvls (
147             int_id serial PRIMARY KEY,
148             ssid integer NOT NULL,
149             intvl tsrange NOT NULL,
150             EXCLUDE USING gist (ssid WITH =, intvl WITH &&)
151             )/,
152              
153             As stated above, before the C<schedule> table is touched, a "scratch schedule"
154             must first be created in the C<schedintvls> table. Although this operation
155             changes the database, it should be seen as a "dry run". The C<gist> index and
156             a trigger assure that:
157              
158             =over
159              
160             =item * no overlapping entries are entered
161              
162             =item * all the entries fall within a single 168-hour period
163              
164             =item * all the times are evenly divisible by five minutes
165              
166             =back
167              
168             #
169             # FIXME: expand the trigger to check for "closed-open" C<< [ ..., ... ) >> tsrange
170             #
171              
172             If the schedule is successfully inserted into C<schedintvls>, the next step is
173             to "translate", or convert, the individual intervals (expressed as tsrange
174             values) into the four-key hashes described in L<Schedules in the database>,
175             assemble the JSON string, and insert a new row in C<schedules>.
176              
177             To facilitate this conversion, a stored procedure C<translate_schedintvl> was
178             developed.
179              
180             Successful insertion into C<schedules> will generate a Schedule ID (SID) for
181             the schedule, enabling it to be used to make Schedhistory objects.
182              
183             At this point, the scratch schedule is deleted from the C<schedintvls> table.
184              
185              
186             =head2 Schedules in the Perl API
187              
188              
189             =head3 L<Schedintvls> class
190              
191             =over
192              
193             =item * constructor (L<spawn>)
194              
195             =item * L<reset> method (recycles an existing object)
196              
197             =item * basic accessor (L<ssid>)
198              
199             =item * L<intvls> accessor (arrayref containing all tsrange intervals in schedule)
200              
201             =item * L<schedule> accessor (arrayref containing "translated" intervals)
202              
203             =item * L<load> method (load the object from the database and translate the tsrange intervals)
204              
205             =item * L<insert> method (insert all the tsrange elements in one go)
206              
207             =item * L<delete> method (delete all the tsrange elements when we're done with them)
208              
209             =item * L<json> method (generate JSON string from the translated intervals)
210              
211             =back
212              
213             For basic workflow, see C<t/model/schedule.t>.
214              
215              
216             =head3 C<Schedule> class
217              
218             =over
219              
220             =item * constructor (L<spawn>)
221              
222             =item * L<reset> method (recycles an existing object)
223              
224             =item * basic accessors (L<sid>, L<schedule>, L<remark>)
225              
226             =item * L<insert> method (inserts the schedule if it isn't in the database already)
227              
228             =item * L<delete> method
229              
230             =item * L<load> method (not implemented yet)
231              
232             #=item * L<get_schedule_json> function (get JSON string associated with a given SID)
233             #
234             =back
235              
236             For basic workflow, see C<t/model/schedule.t>.
237              
238              
239              
240              
241             =head1 EXPORTS
242              
243             This module provides the following exports:
244              
245             =over
246              
247             #=item * C<get_schedule_json>
248             #
249             =item * C<get_all_schedules>
250              
251             =item * C<sid_exists> (boolean)
252              
253             =back
254              
255             =cut
256              
257 41     41   60632 use Exporter qw( import );
  41         97  
  41         27476  
258             our @EXPORT_OK = qw(
259             get_all_schedules
260             sid_exists
261             );
262              
263              
264              
265             =head1 METHODS
266              
267              
268             =head2 insert
269              
270             Instance method. Attempts to INSERT a record into the 'schedules' table.
271             Field values are taken from the object. Returns a status object.
272              
273             If the "schedule" field of the schedule to be inserted matches an existing
274             schedule, no new record is inserted. Instead, the existing schedule record
275             is returned. In such a case, the "scode", "remark", and "disabled" fields
276             are ignored - except when they are NULL in the existing record.
277              
278             =cut
279              
280             sub insert {
281 0     0 1   my $self = shift;
282 0           my ( $context ) = validate_pos( @_, { type => HASHREF } );
283              
284             # if the exact same schedule is already in the database, we
285             # don't insert it again
286             my $status = select_single(
287             conn => $context->{'dbix_conn'},
288             sql => $site->SQL_SCHEDULES_SELECT_BY_SCHEDULE,
289 0           keys => [ $self->{schedule} ],
290             );
291 0           $log->info( "select_single returned: " . Dumper $status );
292 0 0         if ( $status->level eq 'OK' ) {
    0          
293 0           my $found_sched = App::Dochazka::REST::Model::Schedule->spawn(
294             sid => $status->payload->[0],
295             scode => $status->payload->[1],
296             schedule => $status->payload->[2],
297             remark => $status->payload->[3],
298             disabled => $status->payload->[4],
299             );
300 0           $self->{'sid'} = $found_sched->sid;
301             {
302             #
303             # the exact schedule exists, but if any of { scode, remark, disabled }
304             # are NULL and we have a value, update the record to reflect the value
305             # (in other words, do not prefer NULLs over real values)
306             #
307 0           my $do_update = 0;
  0            
308 0 0 0       if ( ! defined( $found_sched->scode ) and defined( $self->scode ) ) {
309 0           $found_sched->scode( $self->scode );
310 0           $do_update = 1;
311             }
312 0 0 0       if ( ! defined( $found_sched->remark ) and defined( $self->remark ) ) {
313 0           $found_sched->remark( $self->remark );
314 0           $do_update = 1;
315             }
316 0 0 0       if ( ! defined( $found_sched->disabled ) and defined( $self->disabled ) ) {
317 0           $found_sched->disabled( $self->disabled );
318 0           $do_update = 1;
319             }
320 0 0         if ( $do_update ) {
321 0           $status = $found_sched->update( $context );
322 0 0 0       if ( $status->level eq 'OK' and $status->code eq 'DOCHAZKA_CUD_OK' ) {
323 0           $status->code( 'DOCHAZKA_SCHEDULE_UPDATE_OK' );
324             }
325 0           return $status;
326             }
327 0           return $CELL->status_ok( 'DOCHAZKA_SCHEDULE_EXISTS', args => [ $self->{sid} ],
328             payload => $found_sched );
329             }
330             } elsif( $status->level ne 'NOTICE' ) {
331 0           $log->info( "select_single status was neither OK nor NOTICE; returning it as-is" );
332 0           return $status;
333             }
334              
335             # no exact match found, insert a new record
336 0           $log->debug( __PACKAGE__ . "::insert calling cud to insert new schedule" );
337             $status = cud(
338             conn => $context->{'dbix_conn'},
339 0           eid => $context->{'current'}->{'eid'},
340             object => $self,
341             sql => $site->SQL_SCHEDULE_INSERT,
342             attrs => [ 'scode', 'schedule', 'remark' ],
343             );
344              
345 0 0         if ( $status->ok ) {
346 0           $status->code( 'DOCHAZKA_SCHEDULE_INSERT_OK' );
347 0           $log->info( "Inserted new schedule with SID " . $self->{sid} );
348             }
349 0           return $status;
350             }
351              
352              
353             =head2 update
354              
355             Although we do not allow the 'sid' or 'schedule' fields to be updated, schedule
356             records have 'scode', 'remark' and 'disabled' fields that can be updated via this
357             method.
358              
359             =cut
360              
361             sub update {
362 0     0 1   my $self = shift;
363 0           my ( $context ) = validate_pos( @_, { type => HASHREF } );
364              
365 0 0         return $CELL->status_err( 'DOCHAZKA_MALFORMED_400' ) unless $self->{'sid'};
366              
367             my $status = cud(
368             conn => $context->{'dbix_conn'},
369 0           eid => $context->{'current'}->{'eid'},
370             object => $self,
371             sql => $site->SQL_SCHEDULE_UPDATE,
372             attrs => [ 'scode', 'remark', 'disabled', 'sid' ],
373             );
374              
375 0           return $status;
376             }
377              
378              
379             =head2 delete
380              
381             Instance method. Attempts to DELETE a schedule record. This may succeed
382             if no other records in the database refer to this schedule.
383              
384             =cut
385              
386             sub delete {
387 0     0 1   my $self = shift;
388 0           my ( $context ) = validate_pos( @_, { type => HASHREF } );
389              
390             my $status = cud(
391             conn => $context->{'dbix_conn'},
392 0           eid => $context->{'current'}->{'eid'},
393             object => $self,
394             sql => $site->SQL_SCHEDULE_DELETE,
395             attrs => [ 'sid' ],
396             );
397 0 0         $self->reset( sid => $self->{sid} ) if $status->ok;
398              
399 0           $log->debug( "Entering " . __PACKAGE__ . "::delete with status " . Dumper( $status ) );
400 0           return $status;
401             }
402              
403              
404             =head2 load_by_scode
405              
406             Analogous function to L<App::Dochazka::REST::Model::Activity/"load_by_aid">.
407              
408             =cut
409              
410             sub load_by_scode {
411 0     0 1   my $self = shift;
412 0           my ( $conn, $scode ) = validate_pos( @_,
413             { isa => 'DBIx::Connector' },
414             { type => SCALAR },
415             );
416              
417 0           return load(
418             conn => $conn,
419             class => __PACKAGE__,
420             sql => $site->SQL_SCHEDULE_SELECT_BY_SCODE,
421             keys => [ $scode ],
422             );
423             }
424              
425              
426              
427             =head2 load_by_sid
428              
429             Analogous function to L<App::Dochazka::REST::Model::Activity/"load_by_aid">.
430              
431             =cut
432              
433             sub load_by_sid {
434 0     0 1   my $self = shift;
435 0           my ( $conn, $sid ) = validate_pos( @_,
436             { isa => 'DBIx::Connector' },
437             { type => SCALAR },
438             );
439              
440 0           return load(
441             conn => $conn,
442             class => __PACKAGE__,
443             sql => $site->SQL_SCHEDULE_SELECT_BY_SID,
444             keys => [ $sid ],
445             );
446             }
447              
448              
449              
450             =head1 FUNCTIONS
451              
452              
453             =head2 sid_exists
454              
455             Boolean function
456              
457             =cut
458              
459             BEGIN {
460 41     41   272 no strict 'refs';
  41         92  
  41         1441  
461 41     41   199 *{'sid_exists'} = App::Dochazka::REST::Model::Shared::make_test_exists( 'sid' );
  41         4427  
462             }
463              
464              
465             =head2 get_all_schedules
466              
467             Returns a list of all schedule objects, ordered by sid. Takes one
468             argument - a paramhash that can contain only one key, 'disabled',
469             which can be either true or false (defaults to true).
470              
471             =cut
472              
473             sub get_all_schedules {
474 0     0 1   my %PH = validate( @_, {
475             conn => { isa => 'DBIx::Connector' },
476             disabled => { type => SCALAR, default => 0 }
477             } );
478            
479             my $sql = $PH{disabled}
480 0 0         ? $site->SQL_SCHEDULES_SELECT_ALL_INCLUDING_DISABLED
481             : $site->SQL_SCHEDULES_SELECT_ALL_EXCEPT_DISABLED;
482              
483             # run the query and gather the results
484              
485             return load_multiple(
486 0           conn => $PH{'conn'},
487             class => __PACKAGE__,
488             sql => $sql,
489             keys => [],
490             );
491             }
492              
493              
494             #=head2 get_schedule_json
495             #
496             #Given a SID, queries the database for the JSON string associated with the SID.
497             #Returns undef if not found.
498             #
499             #=cut
500             #
501             #sub get_schedule_json {
502             # my ( $sid ) = @_;
503             # die "Problem with arguments in get_schedule_json" if not defined $sid;
504             #
505             # my $json;
506             # try {
507             # $conn->do( fixup => sub {
508             # ( $json ) = $_->selectrow_array( $site->SQL_SCHEDULES_SELECT_SCHEDULE,
509             # undef,
510             # $sid );
511             # } );
512             # }
513             #
514             # if ( $json ) {
515             # $log->debug( __PACKAGE__ . "::get_schedule_json got schedule from database: $json" );
516             # return decode_schedule_json( $json );
517             # }
518             # return;
519             #}
520              
521              
522              
523             =head1 AUTHOR
524              
525             Nathan Cutler, C<< <presnypreklad@gmail.com> >>
526              
527             =cut
528              
529             1;
530