installer/data/mysql/db_revs/220600022.pl

   1 use Modern::Perl;
   2
   3 return {
   4     bug_number  => "30650",
   5     description => "Add Curbside pickup feature",
   6     up          => sub {
   7         my ($args) = @_;
   8         my ( $dbh, $out ) = @$args{qw(dbh out)};
   9         unless ( TableExists('curbside_pickup_policy') ) {
  10             $dbh->do(
  11                 q{
  12             CREATE TABLE `curbside_pickup_policy` (
  13               `id` int(11) NOT NULL auto_increment,
  14               `branchcode` varchar(10) NOT NULL,
  15               `enabled` TINYINT(1) NOT NULL DEFAULT 0,
  16               `pickup_interval` INT(2) NOT NULL DEFAULT 0,
  17               `patrons_per_interval` INT(2) NOT NULL DEFAULT 0,
  18               `patron_scheduled_pickup` TINYINT(1) NOT NULL DEFAULT 0,
  19               `sunday_start_hour` INT(2) NULL DEFAULT NULL,
  20               `sunday_start_minute` INT(2) NULL DEFAULT NULL,
  21               `sunday_end_hour` INT(2) NULL DEFAULT NULL,
  22               `sunday_end_minute` INT(2) NULL DEFAULT NULL,
  23               `monday_start_hour` INT(2) NULL DEFAULT NULL,
  24               `monday_start_minute` INT(2) NULL DEFAULT NULL,
  25               `monday_end_hour` INT(2) NULL DEFAULT NULL,
  26               `monday_end_minute` INT(2) NULL DEFAULT NULL,
  27               `tuesday_start_hour` INT(2) NULL DEFAULT NULL,
  28               `tuesday_start_minute` INT(2) NULL DEFAULT NULL,
  29               `tuesday_end_hour` INT(2) NULL DEFAULT NULL,
  30               `tuesday_end_minute` INT(2) NULL DEFAULT NULL,
  31               `wednesday_start_hour` INT(2) NULL DEFAULT NULL,
  32               `wednesday_start_minute` INT(2) NULL DEFAULT NULL,
  33               `wednesday_end_hour` INT(2) NULL DEFAULT NULL,
  34               `wednesday_end_minute` INT(2) NULL DEFAULT NULL,
  35               `thursday_start_hour` INT(2) NULL DEFAULT NULL,
  36               `thursday_start_minute` INT(2) NULL DEFAULT NULL,
  37               `thursday_end_hour` INT(2) NULL DEFAULT NULL,
  38               `thursday_end_minute` INT(2) NULL DEFAULT NULL,
  39               `friday_start_hour` INT(2) NULL DEFAULT NULL,
  40               `friday_start_minute` INT(2) NULL DEFAULT NULL,
  41               `friday_end_hour` INT(2) NULL DEFAULT NULL,
  42               `friday_end_minute` INT(2) NULL DEFAULT NULL,
  43               `saturday_start_hour` INT(2) NULL DEFAULT NULL,
  44               `saturday_start_minute` INT(2) NULL DEFAULT NULL,
  45               `saturday_end_hour` INT(2) NULL DEFAULT NULL,
  46               `saturday_end_minute` INT(2) NULL DEFAULT NULL,
  47               PRIMARY KEY (`id`),
  48               UNIQUE KEY (`branchcode`),
  49               FOREIGN KEY (branchcode) REFERENCES branches(branchcode) ON DELETE CASCADE ON UPDATE CASCADE
  50             ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  51         }
  52             );
  53
  54             say $out "Added new table 'curbside_pickup_policy'";
  55         }
  56
  57         unless ( TableExists('curbside_pickup_opening_slots') ) {
  58             $dbh->do(q{
  59                 CREATE TABLE `curbside_pickup_opening_slots` (
  60                     `id` INT(11) NOT NULL AUTO_INCREMENT,
  61                     `curbside_pickup_policy_id` INT(11) NOT NULL,
  62                     `day` TINYINT(1) NOT NULL,
  63                     `start_hour` INT(2) NOT NULL,
  64                     `start_minute` INT(2) NOT NULL,
  65                     `end_hour` INT(2) NOT NULL,
  66                     `end_minute` INT(2) NOT NULL,
  67                     PRIMARY KEY (`id`),
  68                     FOREIGN KEY (curbside_pickup_policy_id) REFERENCES curbside_pickup_policy(id) ON DELETE CASCADE ON UPDATE CASCADE
  69                 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  70             });
  71
  72             say $out "Added new table 'curbside_pickup_opening_slots'";
  73
  74             my $existing_slots = $dbh->selectall_arrayref(q{SELECT * FROM curbside_pickup_policy}, { Slice => {} });
  75             my $insert_sth = $dbh->prepare(q{INSERT INTO curbside_pickup_opening_slots ( curbside_pickup_policy_id, day, start_hour, start_minute, end_hour, end_minute ) VALUES (?, ?, ?, ?, ?, ?)});
  76             for my $slot ( @$existing_slots ) {
  77                 my $day_i = 0;
  78                 for my $day ( qw( sunday monday tuesday wednesday thursday friday saturday ) ) {
  79                     my $start_hour = $slot->{$day . '_start_hour'};
  80                     my $start_minute = $slot->{$day . '_start_minute'};
  81                     my $end_hour = $slot->{$day . '_end_hour'};
  82                     my $end_minute = $slot->{$day . '_end_minute'};
  83                     next unless $start_hour && $start_minute && $end_hour && $end_minute;
  84                     $insert_sth->execute($slot->{id}, $day_i, $start_hour, $start_minute, $end_hour, $end_minute);
  85                     $day_i++;
  86                 }
  87             }
  88             $dbh->do(q{
  89                 ALTER TABLE curbside_pickup_policy
  90                 DROP COLUMN sunday_start_hour,
  91                 DROP COLUMN sunday_start_minute,
  92                 DROP COLUMN sunday_end_hour,
  93                 DROP COLUMN sunday_end_minute,
  94
  95                 DROP COLUMN monday_start_hour,
  96                 DROP COLUMN monday_start_minute,
  97                 DROP COLUMN monday_end_hour,
  98                 DROP COLUMN monday_end_minute,
  99
 100                 DROP COLUMN tuesday_start_hour,
 101                 DROP COLUMN tuesday_start_minute,
 102                 DROP COLUMN tuesday_end_hour,
 103                 DROP COLUMN tuesday_end_minute,
 104
 105                 DROP COLUMN wednesday_start_hour,
 106                 DROP COLUMN wednesday_start_minute,
 107                 DROP COLUMN wednesday_end_hour,
 108                 DROP COLUMN wednesday_end_minute,
 109
 110                 DROP COLUMN thursday_start_hour,
 111                 DROP COLUMN thursday_start_minute,
 112                 DROP COLUMN thursday_end_hour,
 113                 DROP COLUMN thursday_end_minute,
 114
 115                 DROP COLUMN friday_start_hour,
 116                 DROP COLUMN friday_start_minute,
 117                 DROP COLUMN friday_end_hour,
 118                 DROP COLUMN friday_end_minute,
 119
 120                 DROP COLUMN saturday_start_hour,
 121                 DROP COLUMN saturday_start_minute,
 122                 DROP COLUMN saturday_end_hour,
 123                 DROP COLUMN saturday_end_minute
 124             });
 125         }
 126
 127         unless ( TableExists('curbside_pickups') ) {
 128
 129             $dbh->do(
 130                 q{
 131             CREATE TABLE `curbside_pickups` (
 132               `id` int(11) NOT NULL auto_increment,
 133               `borrowernumber` int(11) NOT NULL,
 134               `branchcode` varchar(10) NOT NULL,
 135               `scheduled_pickup_datetime` datetime NOT NULL,
 136               `staged_datetime` datetime NULL DEFAULT NULL,
 137               `staged_by` int(11) NULL DEFAULT NULL,
 138               `arrival_datetime` datetime NULL DEFAULT NULL,
 139               `delivered_datetime` datetime NULL DEFAULT NULL,
 140               `delivered_by` int(11) NULL DEFAULT NULL,
 141               `notes` text NULL DEFAULT NULL,
 142               PRIMARY KEY (`id`),
 143               FOREIGN KEY (branchcode) REFERENCES branches(branchcode) ON DELETE CASCADE ON UPDATE CASCADE,
 144               FOREIGN KEY (borrowernumber) REFERENCES borrowers(borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE,
 145               FOREIGN KEY (staged_by) REFERENCES borrowers(borrowernumber) ON DELETE SET NULL ON UPDATE CASCADE
 146             ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 147             }
 148             );
 149
 150             say $out "Added new table 'curbside_pickups'";
 151         }
 152         unless ( TableExists('curbside_pickup_issues') ) {
 153             $dbh->do(
 154                 q{
 155             CREATE TABLE `curbside_pickup_issues` (
 156               `id` int(11) NOT NULL auto_increment,
 157               `curbside_pickup_id` int(11) NOT NULL,
 158               `issue_id` int(11) NOT NULL,
 159               `reserve_id` int(11) NOT NULL,
 160               PRIMARY KEY (`id`),
 161               FOREIGN KEY (curbside_pickup_id) REFERENCES curbside_pickups(id) ON DELETE CASCADE ON UPDATE CASCADE
 162             ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 163                 }
 164             );
 165
 166             say $out "Added new table 'curbside_pickup_issues'";
 167         }
 168         $dbh->do(
 169             q{
 170                 INSERT IGNORE INTO `letter` (`module`, `code`, `branchcode`, `name`, `is_html`, `title`, `content`, `message_transport_type`, `lang`) VALUES ('reserves','NEW_CURBSIDE_PICKUP','','New curbside pickup',0,"You have scheduled a curbside pickup for [% branch.branchname %]","[%- USE KohaDates -%]\n[%- SET cp = curbside_pickup -%]\n\nYou have a curbside pickup scheduled for [% cp.scheduled_pickup_datetime | $KohaDates with_hours => 1 %] at [% cp.library.branchname %].\n\nAny holds waiting for you at the pickup time will be included in this pickup. At this time, that list includes:\n[%- FOREACH h IN cp.patron.holds %]\n    [%- IF h.branchcode == cp.branchcode && h.found == 'W' %]\n* [% h.biblio.title %], [% h.biblio.author %] ([% h.item.barcode %])\n    [%- END %]\n[%- END %]\n\nOnce you have arrived, please call your library or log into your account and click the \"Alert staff of your arrival\" button to let them know you are there.",'email','default');
 171             }
 172         );
 173
 174         say $out "Added new letter 'NEW_CURBSIDE_PICKUP' (email)";
 175
 176         $dbh->do(q{
 177             INSERT IGNORE INTO systempreferences (`variable`, `value`, `options`, `explanation`, `type` )
 178             VALUES
 179             ('CurbsidePickup', '0', NULL, 'Enable curbside pickup', 'YesNo')
 180         });
 181
 182         say $out "Added new system preference 'CurbsidePickup'";
 183
 184         $dbh->do(qq{
 185             INSERT IGNORE permissions (module_bit, code, description)
 186             VALUES
 187             (1, 'manage_curbside_pickups', 'Manage curbside pickups (circulation)')
 188         });
 189
 190         say $out "Added new permission 'manage_curbside_pickups' (circulation)";
 191
 192         $dbh->do(qq{
 193             INSERT IGNORE permissions (module_bit, code, description)
 194             VALUES
 195             (3, 'manage_curbside_pickups', 'Manage curbside pickups (admin)')
 196         });
 197
 198         say $out "Added new permission 'manage_curbside_pickups' (admin)";
 199
 200         unless ( column_exists('curbside_pickup_policy', 'enable_waiting_holds_only') ) {
 201             $dbh->do(q{
 202                 ALTER table curbside_pickup_policy
 203                 ADD COLUMN enable_waiting_holds_only TINYINT(1) NOT NULL DEFAULT 0 AFTER enabled
 204             });
 205
 206             say $out "Added column 'curbside_pickup_policy.enable_waiting_holds_only'";
 207         }
 208     }
 209   }