From 62a0894c931b05f5b591b9a9e72b17c8cbdb03ec Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Tue, 19 May 2020 15:08:32 -0400 Subject: [PATCH] LP#1879983: new table and seed data for curbside This patch adds a new database table, action.curbside, for tracking appointments to pick up on-shelf hold requests: id - ID patron - patron that the appoint is for org - pickup library that the appointment is for slot - date and time of appointment staged - whethers for appointment have been staged stage_staff - staff member responsible for staging the items arrival - whether patron has arrived to pick up the items delivered - whether items have been checked out to patron delivery_staff - staff member responsible for checking out the items notes - notes about the appointment, e.g., the color of the patron's vehicle It also adds four new library settings: * circ.curbside: whether to enable curbside appointments for picking up available hold requests. This default to off. * circ.curbside.granularity: interval between appointment slots. This defaults to 15 minutes. * circ.curbside.max_concurrent: how many appointments to permit per time slot. This defaults to 10. * circ.curbside.disable_patron_input: if turned on, display scheduled and pending appointments in My Account in the public catalog but do not give the patron the ability to change them from My Account. This defaults to false, i.e., allowing patrons to modify appointments from My Account. It also adds two Action Trigger hooks: * hold.offer_curbside: to trigger notifications offering a patron the opportunity to set an appointment time; this is invoked if the CurbsideSlot A/T reactor is used to create appointment slots when holds become available. * hold.confim_curbside: fired when a curbside pickup appointment is created or updated. It also adds seed data for the Curbside A/T validator and CurbsideSlot A/T reactor. Finally, it adds sample A/T event definitions: * Curbside offer Email notification * Curbside offer SMS notification * Curbside confirmation Email notification * Curbside confirmation SMS notification These event definitions are disabled by default. In addition to Mike Rylander, significant contributions to this patch were made by Jason Boyer and Galen Charlton. Sponsored-by: PaILS Signed-off-by: Galen Charlton Signed-off-by: Mike Rylander Signed-off-by: Jason Boyer Signed-off-by: Michele Morgan --- Open-ILS/examples/fm_IDL.xml | 20 ++ .../sql/Pg/upgrade/XXXX.schema.curbside.sql | 326 ++++++++++++++++++ 2 files changed, 346 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.curbside.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 5ec1ff4c79..e47f7228b3 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -4853,6 +4853,26 @@ SELECT usr, + + + + + + + + + + + + + + + + + + + + diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.curbside.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.curbside.sql new file mode 100644 index 0000000000..7a6896cf35 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.curbside.sql @@ -0,0 +1,326 @@ +BEGIN; + +CREATE TABLE action.curbside ( + id SERIAL PRIMARY KEY, + patron INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + org INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + slot TIMESTAMPTZ, + staged TIMESTAMPTZ, + stage_staff INT REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + arrival TIMESTAMPTZ, + delivered TIMESTAMPTZ, + delivery_staff INT REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + notes TEXT +); + +INSERT INTO config.org_unit_setting_type (name, label, grp, description, datatype) +VALUES ( + 'circ.curbside', + 'Enable curbside pickup functionality at library.', + 'circ', + 'When set to TRUE, enable staff and public interfaces to schedule curbside pickup of holds that become available for pickup.', + 'bool' +); + +INSERT INTO config.org_unit_setting_type (name, label, grp, description, datatype) +VALUES ( + 'circ.curbside.granularity', + 'Time interval between curbside appointments', + 'circ', + 'Time interval between curbside appointments', + 'interval' +); + +INSERT INTO config.org_unit_setting_type (name, label, grp, description, datatype) +VALUES ( + 'circ.curbside.max_concurrent', + 'Maximum number of patrons that may select a particular curbside pickup time', + 'circ', + 'Maximum number of patrons that may select a particular curbside pickup time', + 'integer' +); + +INSERT INTO config.org_unit_setting_type (name, label, grp, description, datatype) +VALUES ( + 'circ.curbside.disable_patron_input', + 'Disable patron modification of curbside appointments in public catalog', + 'circ', + 'When set to TRUE, patrons cannot use the My Account interface to select curbside pickup times', + 'bool' +); + +INSERT INTO actor.org_unit_setting (org_unit, name, value) + SELECT id, 'circ.curbside', 'false' FROM actor.org_unit WHERE parent_ou IS NULL + UNION + SELECT id, 'circ.curbside.max_concurrent', '10' FROM actor.org_unit WHERE parent_ou IS NULL + UNION + SELECT id, 'circ.curbside.granularity', '"15 minutes"' FROM actor.org_unit WHERE parent_ou IS NULL +; + +INSERT INTO action_trigger.hook (key, core_type, description, passive) +VALUES ( + 'hold.offer_curbside', + 'ahr', + oils_i18n_gettext( + 'hold.offer_curbside', + 'Hook used to trigger the notification of an offer of curbside pickup', + 'ath', + 'description' + ), + FALSE +); + +INSERT INTO action_trigger.hook (key, core_type, description, passive) +VALUES ( + 'hold.confirm_curbside', + 'acsp', + oils_i18n_gettext( + 'hold.confirm_curbside', + 'Hook used to trigger the notification of the creation or update of a curbside pickup appointment with an arrival URL', + 'ath', + 'description' + ), + FALSE +); + +INSERT INTO action_trigger.reactor (module, description) VALUES ( + 'CurbsideSlot', 'Create a curbside pickup appointment slot when necessary' +); + +INSERT INTO action_trigger.validator (module, description) VALUES ( + 'Curbside', 'Confirm that curbside pickup is enabled for the hold pickup library' +); + +------------------- Disabled example A/T defintions ------------------------------ + +-- Create a "dummy" slot when applicable, and trigger the "offer curbside" events +INSERT INTO action_trigger.event_definition ( + active, + owner, + name, + hook, + validator, + reactor, + delay +) VALUES ( + 'f', + 1, + 'Trigger curbside offer events and create a placeholder for the patron, where applicable', + 'hold.available', + 'Curbside', + 'CurbsideSlot', + '00:30:00' +); + +-- Email offer +INSERT INTO action_trigger.event_definition ( + active, + owner, + name, + hook, + validator, + reactor, + delay, + delay_field, + group_field, + template +) VALUES ( + 'f', + 1, + 'Curbside offer Email notification, triggered by CurbsideSlot reactor on a definition attached to the hold.available hook', + 'hold.offer_curbside', + 'Curbside', + 'SendEmail', + '00:00:00', + 'shelf_time', + 'usr', +$$ +[%- USE date -%] +[%- user = target.0.usr -%] +To: [%- params.recipient_email || user.email %] +From: [%- params.sender_email || default_sender %] +Date: [%- date.format(date.now, '%a, %d %b %Y %T -0000', gmt => 1) %] +Subject: Curbside Pickup +Auto-Submitted: auto-generated + +[% target.0.pickup_lib.name %] is now offering curbside delivery +service. Please call [% target.0.pickup_lib.phone %] or visit the +link below to schedule a pickup time. + +https://example.org/eg/opac/myopac/holds_curbside + +Stay safe! Wash your hands! +$$); + +INSERT INTO action_trigger.environment ( + event_def, + path +) VALUES ( + currval('action_trigger.event_definition_id_seq'), + 'pickup_lib' +), ( + currval('action_trigger.event_definition_id_seq'), + 'usr' +); + +INSERT INTO action_trigger.event_params (event_def, param, value) + VALUES (currval('action_trigger.event_definition_id_seq'), 'check_email_notify', 1); + +-- SMS offer +INSERT INTO action_trigger.event_definition ( + active, + owner, + name, + hook, + validator, + reactor, + delay, + delay_field, + group_field, + template +) VALUES ( + false, + 1, + 'Curbside offer SMS notification, triggered by CurbsideSlot reactor on a definition attached to the hold.available hook', + 'hold.offer_curbside', + 'Curbside', + 'SendSMS', + '00:00:00', + 'shelf_time', + 'sms_notify', + $$[%- USE date -%] +[%- user = target.0.usr -%] +From: [%- params.sender_email || default_sender %] +Date: [%- date.format(date.now, '%a, %d %b %Y %T -0000', gmt => 1) %] +To: [%- params.recipient_email || helpers.get_sms_gateway_email(target.0.sms_carrier,target.0.sms_notify) %] +Subject: Curbside Pickup +Auto-Submitted: auto-generated + +[% target.0.pickup_lib.name %] offers curbside pickup. +Call [% target.0.pickup_lib.phone %] or visit https://example.org/eg/opac/myopac/holds_curbside +$$ +); + +INSERT INTO action_trigger.environment ( + event_def, + path +) VALUES ( + currval('action_trigger.event_definition_id_seq'), + 'pickup_lib' +), ( + currval('action_trigger.event_definition_id_seq'), + 'usr' +); + +INSERT INTO action_trigger.event_params (event_def, param, value) + VALUES (currval('action_trigger.event_definition_id_seq'), 'check_sms_notify', 1); + +-- Email confirmation +INSERT INTO action_trigger.event_definition ( + active, + owner, + name, + hook, + validator, + reactor, + delay, + template +) VALUES ( + 'f', + 1, + 'Curbside confirmation Email notification', + 'hold.confirm_curbside', + 'Curbside', + 'SendEmail', + '00:00:00', +$$ +[%- USE date -%] +[%- user = target.patron -%] +To: [%- params.recipient_email || user.email %] +From: [%- params.sender_email || default_sender %] +Date: [%- date.format(date.now, '%a, %d %b %Y %T -0000', gmt => 1) %] +Subject: Curbside Pickup Confirmed +Auto-Submitted: auto-generated + +This email is to confirm that you have scheduled a curbside item +pickup at [% target.org.name %] for [% date.format(helpers.format_date(target.slot), '%a, %d %b %Y %T') %]. + +You can cancel or change to your appointment, add vehicle description +notes, and alert staff to your arrival by going to the link below. + +When you arrive, please call [% target.org.phone %] or visit the +link below to let us know you are here. + +https://example.org/eg/opac/myopac/holds_curbside + +Stay safe! Wash your hands! +$$); + +INSERT INTO action_trigger.environment ( + event_def, + path +) VALUES ( + currval('action_trigger.event_definition_id_seq'), + 'org' +), ( + currval('action_trigger.event_definition_id_seq'), + 'patron' +); + +-- We do /not/ add this by default, treating curbside request as implicit opt-in +/* +INSERT INTO action_trigger.event_params (event_def, param, value) + VALUES (currval('action_trigger.event_definition_id_seq'), 'check_email_notify', 1); +*/ + +-- SMS confirmation +INSERT INTO action_trigger.event_definition ( + active, + owner, + name, + hook, + validator, + reactor, + delay, + template +) VALUES ( + false, + 1, + 'Curbside confirmation SMS notification', + 'hold.confirm_curbside', + 'Curbside', + 'SendSMS', + '00:00:00', + $$[%- USE date -%] +[%- user = target.patron -%] +From: [%- params.sender_email || default_sender %] +Date: [%- date.format(date.now, '%a, %d %b %Y %T -0000', gmt => 1) %] +To: [%- params.recipient_email || helpers.get_sms_gateway_email(helpers.get_user_setting(user.id, 'opac.default_sms_carrier'), helpers.get_user_setting(user.id, 'opac.default_sms_notify')) %] +Subject: Curbside Pickup Confirmed +Auto-Submitted: auto-generated + +Location: [% target.org.name %] +Time: [% date.format(helpers.format_date(target.slot), '%a, %d %b %Y %T') %] +Make changes at https://example.org/eg/opac/myopac/holds_curbside +$$ +); + +INSERT INTO action_trigger.environment ( + event_def, + path +) VALUES ( + currval('action_trigger.event_definition_id_seq'), + 'org' +), ( + currval('action_trigger.event_definition_id_seq'), + 'patron' +); + +-- We do /not/ add this by default, treating curbside request as implicit opt-in +/* +INSERT INTO action_trigger.event_params (event_def, param, value) + VALUES (currval('action_trigger.event_definition_id_seq'), 'check_sms_notify', 1); +*/ + + +COMMIT; -- 2.43.2