5 description => "Curbside pickup tables",
8 my ( $dbh, $out ) = @$args{qw(dbh out)};
9 unless ( TableExists('curbside_pickup_policy') ) {
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,
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;
55 unless ( TableExists('curbside_pickup_opening_slots') ) {
57 CREATE TABLE `curbside_pickup_opening_slots` (
58 `id` INT(11) NOT NULL AUTO_INCREMENT,
59 `curbside_pickup_policy_id` INT(11) NOT NULL,
60 `day` INT(1) NOT NULL,
61 `start_hour` INT(2) NOT NULL,
62 `start_minute` INT(2) NOT NULL,
63 `end_hour` INT(2) NOT NULL,
64 `end_minute` INT(2) NOT NULL,
66 FOREIGN KEY (curbside_pickup_policy_id) REFERENCES curbside_pickup_policy(id) ON DELETE CASCADE ON UPDATE CASCADE
67 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
69 my $existing_slots = $dbh->selectall_arrayref(q{SELECT * FROM curbside_pickup_policy}, { Slice => {} });
70 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 (?, ?, ?, ?, ?, ?)});
71 for my $slot ( @$existing_slots ) {
73 for my $day ( qw( sunday monday tuesday wednesday thursday friday saturday ) ) {
74 my $start_hour = $slot->{$day . '_start_hour'};
75 my $start_minute = $slot->{$day . '_start_minute'};
76 my $end_hour = $slot->{$day . '_end_hour'};
77 my $end_minute = $slot->{$day . '_end_minute'};
78 next unless $start_hour && $start_minute && $end_hour && $end_minute;
79 $insert_sth->execute($slot->{id}, $day_i, $start_hour, $start_minute, $end_hour, $end_minute);
84 ALTER TABLE curbside_pickup_policy
85 DROP COLUMN sunday_start_hour,
86 DROP COLUMN sunday_start_minute,
87 DROP COLUMN sunday_end_hour,
88 DROP COLUMN sunday_end_minute,
90 DROP COLUMN monday_start_hour,
91 DROP COLUMN monday_start_minute,
92 DROP COLUMN monday_end_hour,
93 DROP COLUMN monday_end_minute,
95 DROP COLUMN tuesday_start_hour,
96 DROP COLUMN tuesday_start_minute,
97 DROP COLUMN tuesday_end_hour,
98 DROP COLUMN tuesday_end_minute,
100 DROP COLUMN wednesday_start_hour,
101 DROP COLUMN wednesday_start_minute,
102 DROP COLUMN wednesday_end_hour,
103 DROP COLUMN wednesday_end_minute,
105 DROP COLUMN thursday_start_hour,
106 DROP COLUMN thursday_start_minute,
107 DROP COLUMN thursday_end_hour,
108 DROP COLUMN thursday_end_minute,
110 DROP COLUMN friday_start_hour,
111 DROP COLUMN friday_start_minute,
112 DROP COLUMN friday_end_hour,
113 DROP COLUMN friday_end_minute,
115 DROP COLUMN saturday_start_hour,
116 DROP COLUMN saturday_start_minute,
117 DROP COLUMN saturday_end_hour,
118 DROP COLUMN saturday_end_minute
122 unless ( TableExists('curbside_pickups') ) {
126 CREATE TABLE `curbside_pickups` (
127 `id` int(11) NOT NULL auto_increment,
128 `borrowernumber` int(11) NOT NULL,
129 `branchcode` varchar(10) NOT NULL,
130 `scheduled_pickup_datetime` datetime NOT NULL,
131 `staged_datetime` datetime NULL DEFAULT NULL,
132 `staged_by` int(11) NULL DEFAULT NULL,
133 `arrival_datetime` datetime NULL DEFAULT NULL,
134 `delivered_datetime` datetime NULL DEFAULT NULL,
135 `delivered_by` int(11) NULL DEFAULT NULL,
136 `notes` text NULL DEFAULT NULL,
138 FOREIGN KEY (branchcode) REFERENCES branches(branchcode) ON DELETE CASCADE ON UPDATE CASCADE,
139 FOREIGN KEY (borrowernumber) REFERENCES borrowers(borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE,
140 FOREIGN KEY (staged_by) REFERENCES borrowers(borrowernumber) ON DELETE SET NULL ON UPDATE CASCADE
141 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
145 unless ( TableExists('curbside_pickup_issues') ) {
148 CREATE TABLE `curbside_pickup_issues` (
149 `id` int(11) NOT NULL auto_increment,
150 `curbside_pickup_id` int(11) NOT NULL,
151 `issue_id` int(11) NOT NULL,
152 `reserve_id` int(11) NOT NULL,
154 FOREIGN KEY (curbside_pickup_id) REFERENCES curbside_pickups(id) ON DELETE CASCADE ON UPDATE CASCADE
155 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
161 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');
166 INSERT IGNORE INTO systempreferences (`variable`, `value`, `options`, `explanation`, `type` )
168 ('CurbsidePickup', '0', NULL, 'Enable curbside pickup', 'YesNo')
172 INSERT IGNORE permissions (module_bit, code, description)
174 (1, 'manage_curbside_pickups', 'Manage curbside pickups (circulation)')
177 INSERT IGNORE permissions (module_bit, code, description)
179 (3, 'manage_curbside_pickups', 'Manage curbside pickups (admin)')
182 unless ( column_exists('curbside_pickup_policy', 'enable_waiting_holds_only') ) {
184 ALTER table curbside_pickup_policy
185 ADD COLUMN enable_waiting_holds_only INT(1) NOT NULL DEFAULT 0 AFTER enabled