3 SELECT evergreen.upgrade_deps_block_check('0666', :eg_version);
5 -- 950.data.seed-values.sql
6 INSERT INTO config.settings_group (name, label) VALUES
18 INSERT INTO config.org_unit_setting_type (name, grp, label, description, datatype) VALUES
24 'Enable features that send SMS text messages.',
30 'Current features that use SMS include hold-ready-for-pickup notifications and a "Send Text" action for call numbers in the OPAC. If this setting is not enabled, the SMS options will not be offered to the user. Unless you are carefully silo-ing patrons and their use of the OPAC, the context org for this setting should be the top org in the org hierarchy, otherwise patrons can trample their user settings when jumping between orgs.',
37 'sms.disable_authentication_requirement.callnumbers',
40 'sms.disable_authentication_requirement.callnumbers',
41 'Disable auth requirement for texting call numbers.',
46 'sms.disable_authentication_requirement.callnumbers',
47 'Disable authentication requirement for sending call number information via SMS from the OPAC.',
55 -- 002.schema.config.sql
56 CREATE TABLE config.sms_carrier (
57 id SERIAL PRIMARY KEY,
61 active BOOLEAN DEFAULT TRUE
64 -- 090.schema.action.sql
65 ALTER TABLE action.hold_request ADD COLUMN sms_notify TEXT;
66 ALTER TABLE action.hold_request ADD COLUMN sms_carrier INT REFERENCES config.sms_carrier (id);
67 ALTER TABLE action.hold_request ADD CONSTRAINT sms_check CHECK (
69 OR sms_carrier IS NOT NULL -- and implied sms_notify IS NOT NULL
72 -- 950.data.seed-values.sql
73 INSERT INTO config.usr_setting_type (name,grp,opac_visible,label,description,datatype,fm_class) VALUES (
74 'opac.default_sms_carrier',
78 'opac.default_sms_carrier',
79 'Default SMS/Text Carrier',
84 'opac.default_sms_carrier',
85 'Default SMS/Text Carrier',
93 INSERT INTO config.usr_setting_type (name,grp,opac_visible,label,description,datatype) VALUES (
94 'opac.default_sms_notify',
98 'opac.default_sms_notify',
99 'Default SMS/Text Number',
104 'opac.default_sms_notify',
105 'Default SMS/Text Number',
112 INSERT INTO config.usr_setting_type (name,grp,opac_visible,label,description,datatype) VALUES (
113 'opac.default_phone',
117 'opac.default_phone',
118 'Default Phone Number',
123 'opac.default_phone',
124 'Default Phone Number',
131 SELECT setval( 'config.sms_carrier_id_seq', 1000 );
132 INSERT INTO config.sms_carrier VALUES
149 'opensrf+$number@localhost',
168 '$number@pcs.rogers.com',
181 'Rogers Wireless (Alternate)',
185 '1$number@mms.rogers.com',
202 '$number@msg.telus.com',
221 '$number@msg.telus.com',
251 'Bell Mobility & Solo Mobile',
255 '$number@txt.bell.ca',
268 'Bell Mobility & Solo Mobile (Alternate)',
272 '$number@txt.bellmobility.ca',
289 '$number@sms.wirefree.informe.ca',
306 '$number@mobiletxt.ca',
323 '$number@sms.sasktel.com',
340 '$number@text.mtsmobility.com',
357 '$number@vmobile.ca',
376 '$number@msg.iridium.com',
393 '$number@msg.globalstarusa.com',
410 '$number@bulletinmessenger.net', -- International Formatted number
427 '$number@api.panaceamobile.com',
446 '$number@cbeyond.sprintpcs.com',
459 'General Communications, Inc.',
463 '$number@mobile.gci.net',
476 'Golden State Cellular',
480 '$number@gscsms.com',
487 'Cincinnati, Ohio, USA',
510 'Hawaiian Telcom Wireless',
514 '$number@hawaii.sprintpcs.com',
527 'i wireless (T-Mobile)',
531 '$number.iws@iwspcs.net',
544 'i-wireless (Sprint PCS)',
548 '$number@iwirelesshometext.com',
565 '$number@mymetropcs.com',
582 '$number@mobile.kajeet.net',
599 '$number@SMS.elementmobile.net',
616 '$number@echoemail.net',
633 '$number@myboostmobile.com',
650 '$number@bellsouth.com',
663 'Bluegrass Cellular',
667 '$number@sms.bluecell.com',
680 'AT&T Enterprise Paging',
684 '$number@page.att.net',
697 'AT&T Mobility/Wireless',
701 '$number@txt.att.net',
714 'AT&T Global Smart Messaging Suite',
718 '$number@sms.smartmessagingsuite.com',
731 'Alltel (Allied Wireless)',
735 '$number@sms.alltelwireless.com',
748 'Alaska Communications',
752 '$number@msg.acsalaska.com',
769 '$number@paging.acswireless.com',
782 'Cingular (GoPhone prepaid)',
786 '$number@cingulartext.com',
799 'Cingular (Postpaid)',
803 '$number@cingular.com',
816 'Cellular One (Dobson) / O2 / Orange',
820 '$number@mobile.celloneusa.com',
837 '$number@csouth1.com',
854 '$number@cellcom.quiktxt.com',
867 'Chariton Valley Wireless',
871 '$number@sms.cvalley.net',
888 '$number@sms.mycricket.com',
901 'Cleartalk Wireless',
905 '$number@sms.cleartalk.us',
922 '$number@sms.edgewireless.com',
939 '$number@rinasms.com',
956 '$number@tmomail.net',
969 'Straight Talk / PagePlus Cellular',
986 'South Central Communications',
990 '$number@rinasms.com',
1007 '$number@smtext.com',
1024 '$number@messaging.sprintpcs.com',
1041 '$number@messaging.nextel.com',
1058 '$number@zsend.com', -- nine digit number
1075 '$number@qwestmp.com',
1092 '$number@email.uscc.net',
1109 '$number@utext.com',
1126 '$number@teleflip.com',
1143 '$number@vmobl.com',
1160 '$number@vtext.com',
1177 '$number@usamobility.net',
1194 '$number@viaerosms.com',
1211 '$number@mmst5.tracfone.com',
1224 'Centennial Wireless',
1228 '$number@cwemail.com',
1232 -- South Korea and USA
1237 'South Korea and USA',
1247 '$number@myhelio.com',
1252 INSERT INTO permission.perm_list ( id, code, description ) VALUES
1255 'ADMIN_SMS_CARRIER',
1258 'Allows a user to add/create/delete SMS Carrier entries.',
1265 INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable)
1267 pgt.id, perm.id, aout.depth, TRUE
1269 permission.grp_tree pgt,
1270 permission.perm_list perm,
1271 actor.org_unit_type aout
1273 pgt.name = 'Global Administrator' AND
1274 aout.name = 'Consortium' AND
1275 perm.code = 'ADMIN_SMS_CARRIER';
1277 INSERT INTO action_trigger.reactor (
1282 'Send an SMS text message based on a user-defined template'
1285 INSERT INTO action_trigger.event_definition (
1300 'Hold Ready for Pickup SMS Notification',
1304 'CreateHoldNotification',
1309 [%- user = target.0.usr -%]
1310 From: [%- params.sender_email || default_sender %]
1311 To: [%- params.recipient_email || helpers.get_sms_gateway_email(target.0.sms_carrier,target.0.sms_notify) %]
1312 Subject: [% target.size %] hold(s) ready
1314 [% FOR hold IN target %][%-
1315 bibxml = helpers.xml_doc( hold.current_copy.call_number.record.marc );
1317 FOR part IN bibxml.findnodes(''//*[@tag="245"]/*[@code="a"]'');
1318 title = title _ part.textContent;
1320 author = bibxml.findnodes(''//*[@tag="100"]/*[@code="a"]'').textContent;
1321 %][% hold.usr.first_given_name %]:[% title %] @ [% hold.pickup_lib.name %]
1326 INSERT INTO action_trigger.environment (
1330 currval('action_trigger.event_definition_id_seq'),
1331 'current_copy.call_number.record.simple_record'
1333 currval('action_trigger.event_definition_id_seq'),
1336 currval('action_trigger.event_definition_id_seq'),
1337 'pickup_lib.billing_address'
1340 INSERT INTO action_trigger.hook(
1346 'acn.format.sms_text',
1349 'acn.format.sms_text',
1350 'A text message has been requested for a call number.',
1357 INSERT INTO action_trigger.event_definition (
1369 'acn.format.sms_text',
1373 From: [%- params.sender_email || default_sender %]
1374 To: [%- params.recipient_email || helpers.get_sms_gateway_email(user_data.sms_carrier,user_data.sms_notify) %]
1375 Subject: Call Number
1378 bibxml = helpers.xml_doc( target.record.marc );
1380 FOR part IN bibxml.findnodes(''//*[@tag="245"]/*[@code="a" or @code="b"]'');
1381 title = title _ part.textContent;
1383 author = bibxml.findnodes(''//*[@tag="100"]/*[@code="a"]'').textContent;
1385 Call Number: [% target.label %]
1386 Location: [% helpers.get_most_populous_location( target.id ).name %]
1387 Library: [% target.owning_lib.name %]
1392 Author: [% author %]
1397 INSERT INTO action_trigger.environment (
1401 currval('action_trigger.event_definition_id_seq'),
1402 'record.simple_record'
1404 currval('action_trigger.event_definition_id_seq'),
1405 'owning_lib.billing_address'
1409 -- DELETE FROM actor.usr_setting WHERE name = 'opac.default_phone' OR name in ( SELECT name FROM config.usr_setting_type WHERE grp = 'sms' ); DELETE FROM config.usr_setting_type WHERE name = 'opac.default_phone' OR grp = 'sms'; DELETE FROM actor.org_unit_setting WHERE name in ( SELECT name FROM config.org_unit_setting_type WHERE grp = 'sms' ); DELETE FROM config.org_unit_setting_type_log WHERE field_name in ( SELECT name FROM config.org_unit_setting_type WHERE grp = 'sms' ); DELETE FROM config.org_unit_setting_type WHERE grp = 'sms'; DELETE FROM config.settings_group WHERE name = 'sms'; DELETE FROM permission.grp_perm_map WHERE perm = 519; DELETE FROM permission.perm_list WHERE id = 519; ALTER TABLE action.hold_request DROP CONSTRAINT sms_check; ALTER TABLE action.hold_request DROP COLUMN sms_notify; ALTER TABLE action.hold_request DROP COLUMN sms_carrier; DROP TABLE config.sms_carrier; DELETE FROM action_trigger.event WHERE event_def = ( SELECT id FROM action_trigger.event_definition WHERE name = 'Hold Ready for Pickup SMS Notification' ); DELETE FROM action_trigger.environment WHERE event_def = ( SELECT id FROM action_trigger.event_definition WHERE name = 'Hold Ready for Pickup SMS Notification' ); DELETE FROM action_trigger.event_definition WHERE name = 'Hold Ready for Pickup SMS Notification'; DELETE FROM action_trigger.event WHERE event_def IN ( SELECT id FROM action_trigger.event_definition WHERE hook = 'acn.format.sms_text' ); DELETE FROM action_trigger.environment WHERE event_def IN ( SELECT id FROM action_trigger.event_definition WHERE hook = 'acn.format.sms_text' ); DELETE FROM action_trigger.event_definition WHERE hook = 'acn.format.sms_text'; DELETE FROM action_trigger.hook WHERE key = 'acn.format.sms_text'; DELETE FROM action_trigger.reactor WHERE module = 'SendSMS'; DELETE FROM config.upgrade_log WHERE version = 'XXXX';