From 00c89d915b7fd72d47831d69e076e1ace1a7e78f Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Tue, 26 Feb 2013 09:12:03 -0500 Subject: [PATCH] Bug 9021 - Add SMS via email as an alternative to SMS services via SMS::Send drivers Nearly all cellular providers allow a person to send an text message to a cellular phone by sending an email to phonenumber@provider. We can leverage this capability to add the ability for Koha to send sms messages to patrons without the need to subscribe to an sms gateway server. Basic plan: 1. Add a table sms_providers to the db to tell Koha what service providers are available, and what domain emails should be sent to. 2. Add borrowers.sms_provider_id to tell Koha which mobile service the patron subscribes to for the number given in smsalertnumber 3. Modify Koha to send an email rather than using SMS::Send if the driver is set to 'Email' Test plan: 0) Get a mobile phone 1) Apply the patch 2) Run updatedatabase.pl 3) Set the value of SMSSendDriver to 'Email' 4) Go to the admin page, the "Additional parameters" area should now have the link "SMS cellular providers" 5) On this page, add some providers. Make sure to add the provider for your own cellular phone service. Here are some examples: Sprint phonenumber@messaging.sprintpcs.com Verizon phonenumber@vtext.com T-Mobile phonenumber@tmomail.net AT&T phonenumber@txt.att.net Only add the domain part in the 'domain' field. So for Verizon, that would be 'vtext.com' 6) Create an account for yourself, add your SMS number, and select your provider from the dropdown box directly below it. 7) Enable SMS messaging for Item check-in and Item checkout 8) Check out an item to yourself 9) Run process_message_queue.pl 10) Wait! You should receive a text message shortly, when I tested it, I received my sms message within the minute. Signed-off-by: Mark Tompsett Signed-off-by: Mark Tompsett Signed-off-by: Jonathan Druart Signed-off-by: Kyle M Hall --- C4/Letters.pm | 12 +- C4/Members.pm | 13 +- Koha/SMS/Provider.pm | 157 ++++++++++++++++++ admin/admin-home.pl | 4 + admin/sms_providers.pl | 59 +++++++ .../data/mysql/atomicupdate/bug_9021.sql | 12 ++ installer/data/mysql/kohastructure.sql | 16 ++ .../prog/en/modules/admin/admin-home.tt | 4 + .../prog/en/modules/admin/sms_providers.tt | 102 ++++++++++++ .../prog/en/modules/members/memberentrygen.tt | 13 ++ .../bootstrap/en/modules/opac-messaging.tt | 27 ++- members/memberentry.pl | 6 + opac/opac-messaging.pl | 12 +- 13 files changed, 427 insertions(+), 10 deletions(-) create mode 100644 Koha/SMS/Provider.pm create mode 100755 admin/sms_providers.pl create mode 100644 installer/data/mysql/atomicupdate/bug_9021.sql create mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/admin/sms_providers.tt diff --git a/C4/Letters.pm b/C4/Letters.pm index dc9574f39b..99a39d447a 100644 --- a/C4/Letters.pm +++ b/C4/Letters.pm @@ -31,6 +31,8 @@ use C4::Log; use C4::SMS; use C4::Debug; use Koha::DateUtils; +use Koha::SMS::Provider; + use Date::Calc qw( Add_Delta_Days ); use Encode; use Carp; @@ -983,7 +985,14 @@ sub SendQueuedMessages { _send_message_by_email( $message, $params->{'username'}, $params->{'password'}, $params->{'method'} ); } elsif ( lc( $message->{'message_transport_type'} ) eq 'sms' ) { - _send_message_by_sms( $message ); + if ( C4::Context->preference('SMSSendDriver') eq 'Email' ) { + my $member = C4::Members::GetMember( 'borrowernumber' => $message->{'borrowernumber'} ); + my $sms_provider = Koha::SMS::Provider->find( $member->{'sms_provider_id'} ); + $message->{to_address} .= '@' . $sms_provider->domain(); + _send_message_by_email( $message, $params->{'username'}, $params->{'password'}, $params->{'method'} ); + } else { + _send_message_by_sms( $message ); + } } } return scalar( @$unsent_messages ); @@ -1222,6 +1231,7 @@ sub _send_message_by_email { } _update_message_to_address($message->{'message_id'},$to_address) unless $message->{to_address}; #if initial message address was empty, coming here means that a to address was found and queue should be updated + if ( sendmail( %sendmail_params ) ) { _set_message_status( { message_id => $message->{'message_id'}, status => 'sent' } ); diff --git a/C4/Members.pm b/C4/Members.pm index 4ba1f1934c..9f835f68b9 100644 --- a/C4/Members.pm +++ b/C4/Members.pm @@ -645,6 +645,7 @@ sub ModMember { $data{password} = hash_password($data{password}); } } + my $old_categorycode = GetBorrowerCategorycode( $data{borrowernumber} ); # get only the columns of a borrower @@ -653,15 +654,19 @@ sub ModMember { my $new_borrower = { map { join(' ', @columns) =~ /$_/ ? ( $_ => $data{$_} ) : () } keys(%data) }; delete $new_borrower->{flags}; - $new_borrower->{dateofbirth} ||= undef if exists $new_borrower->{dateofbirth}; - $new_borrower->{dateenrolled} ||= undef if exists $new_borrower->{dateenrolled}; - $new_borrower->{dateexpiry} ||= undef if exists $new_borrower->{dateexpiry}; - $new_borrower->{debarred} ||= undef if exists $new_borrower->{debarred}; + $new_borrower->{dateofbirth} ||= undef if exists $new_borrower->{dateofbirth}; + $new_borrower->{dateenrolled} ||= undef if exists $new_borrower->{dateenrolled}; + $new_borrower->{dateexpiry} ||= undef if exists $new_borrower->{dateexpiry}; + $new_borrower->{debarred} ||= undef if exists $new_borrower->{debarred}; + $new_borrower->{sms_provider_id} ||= undef if exists $new_borrower->{sms_provider_id}; + my $rs = $schema->resultset('Borrower')->search({ borrowernumber => $new_borrower->{borrowernumber}, }); + my $execute_success = $rs->update($new_borrower); if ($execute_success ne '0E0') { # only proceed if the update was a success + # ok if its an adult (type) it may have borrowers that depend on it as a guarantor # so when we update information for an adult we should check for guarantees and update the relevant part # of their records, ie addresses and phone numbers diff --git a/Koha/SMS/Provider.pm b/Koha/SMS/Provider.pm new file mode 100644 index 0000000000..6331088eaf --- /dev/null +++ b/Koha/SMS/Provider.pm @@ -0,0 +1,157 @@ +package Koha::SMS::Provider; + +# Copyright 2012 ByWater Solutions +# +# This file is part of Koha. +# +# Koha is free software; you can redistribute it and/or modify it under the +# terms of the GNU General Public License as published by the Free Software +# Foundation; either version 3 of the License, or (at your option) any later +# version. +# +# Koha is distributed in the hope that it will be useful, but WITHOUT ANY +# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +# A PARTICULAR PURPOSE. See the GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License along +# with Koha; if not, write to the Free Software Foundation, Inc., +# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. + +=head1 NAME + +Koha::SMS::Provider - class to manage sms providers + +=head1 SYNOPSIS + +Object-oriented class that encapsulates sms providers in Koha. + +=head1 DESCRIPTION + +SMS::Provider data. + +=cut + +use Modern::Perl; + +use C4::Context; + +use base qw(Class::Accessor); + +__PACKAGE__->mk_accessors(qw( id name domain )); + +=head2 new + + my $provider = Koha::SMS::Provider->new($data); + +Create a new Koha::SMS::Provider object based on the provided record. + +=cut + +sub new { + my $class = shift; + my $data = shift; + + my $self = $class->SUPER::new($data); + + bless $self, $class; + return $self; +} + +=head2 store + + Creates or updates the object in the database + +=cut + +sub store { + my $self = shift; + + if ( $self->id ) { + return C4::Context->dbh->do( "UPDATE sms_providers SET name = ?, domain = ? WHERE id = ?", undef, ( $self->name, $self->domain, $self->id ) ); + } else { + return C4::Context->dbh->do( "INSERT INTO sms_providers ( name, domain ) VALUES ( ?, ? )", undef, ( $self->name, $self->domain ) ); + } +} + +=head2 delete + +=cut + +sub delete { + my $self = shift; + + return C4::Context->dbh->do( "DELETE FROM sms_providers WHERE id = ?", undef, ( $self->id ) ); +} + +=head2 all + + my $providers = Koha::SMS::Provider->all(); + +=cut + +sub all { + my $class = shift; + + my $query = "SELECT * FROM sms_providers ORDER BY name"; + my $sth = C4::Context->dbh->prepare($query); + $sth->execute(); + + my @providers; + while ( my $row = $sth->fetchrow_hashref() ) { + my $p = Koha::SMS::Provider->new($row); + push( @providers, $p ); + } + + return @providers; +} + +=head2 find + + my $provider = Koha::SMS::Provider->find( $id ); + +=cut + +sub find { + my $class = shift; + my $id = shift; + + my $query = "SELECT * FROM sms_providers WHERE ID = ?"; + my $sth = C4::Context->dbh->prepare($query); + $sth->execute($id); + + my $row = $sth->fetchrow_hashref(); + my $p = Koha::SMS::Provider->new($row); + + return $p; +} + +=head2 search + + my @providers = Koha::SMS::Provider->search({ [name => $name], [domain => $domain] }); + +=cut + +sub search { + my $class = shift; + my $params = shift; + + my $query = "SELECT * FROM sms_providers WHERE "; + + my @params = map( $params->{$_}, keys %$params ); + $query .= join( " AND ", map( "$_ = ?", keys %$params ) ); + + $query .= " ORDER BY name"; + + my $sth = C4::Context->dbh->prepare($query); + $sth->execute(@params); + + my @providers; + while ( my $row = $sth->fetchrow_hashref() ) { + my $p = Koha::SMS::Provider->new($row); + push( @providers, $p ); + } + + return @providers; +} + +1; diff --git a/admin/admin-home.pl b/admin/admin-home.pl index b72d93df42..327d24dedc 100755 --- a/admin/admin-home.pl +++ b/admin/admin-home.pl @@ -34,4 +34,8 @@ my ($template, $loggedinuser, $cookie) debug => 1, }); +$template->param( + SMSSendDriver => C4::Context->preference('SMSSendDriver'), +); + output_html_with_http_headers $query, $cookie, $template->output; diff --git a/admin/sms_providers.pl b/admin/sms_providers.pl new file mode 100755 index 0000000000..4daea4a91e --- /dev/null +++ b/admin/sms_providers.pl @@ -0,0 +1,59 @@ +#!/usr/bin/perl + +# Copyright 2012 ByWater Solutions +# +# This file is part of Koha. +# +# Koha is free software; you can redistribute it and/or modify it under the +# terms of the GNU General Public License as published by the Free Software +# Foundation; either version 3 of the License, or (at your option) any later +# version. +# +# Koha is distributed in the hope that it will be useful, but WITHOUT ANY +# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +# A PARTICULAR PURPOSE. See the GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License along +# with Koha; if not, write to the Free Software Foundation, Inc., +# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. + +use strict; +use warnings; +use CGI; + +use C4::Context; +use C4::Auth; +use C4::Output; + +use Koha::SMS::Provider; + +my $cgi = new CGI; + +my ( $template, $loggedinuser, $cookie ) = get_template_and_user( + { template_name => "admin/sms_providers.tt", + query => $cgi, + type => "intranet", + authnotrequired => 0, + flagsrequired => { parameters => 'parameters_remaining_permissions' }, + debug => 1, + } +); + +my $op = $cgi->param('op'); +my $id = $cgi->param('id'); +my $name = $cgi->param('name'); +my $domain = $cgi->param('domain'); + +if ( $op eq 'add_update' ) { + if ( $name && $domain ) { + Koha::SMS::Provider->new( { id => $id, name => $name, domain => $domain } )->store(); + } +} elsif ( $op eq 'delete' ) { + Koha::SMS::Provider->find($id)->delete(); +} + +my @providers = Koha::SMS::Provider->all(); + +$template->param( providers => \@providers ); + +output_html_with_http_headers $cgi, $cookie, $template->output; diff --git a/installer/data/mysql/atomicupdate/bug_9021.sql b/installer/data/mysql/atomicupdate/bug_9021.sql new file mode 100644 index 0000000000..f729d262e9 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_9021.sql @@ -0,0 +1,12 @@ +CREATE TABLE sms_providers ( + id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY , + name VARCHAR( 255 ) NOT NULL , + domain VARCHAR( 255 ) NOT NULL , + UNIQUE ( + name + ) +) ENGINE = INNODB CHARACTER SET utf8; + +ALTER TABLE borrowers ADD sms_provider_id INT( 11 ) NULL DEFAULT NULL AFTER smsalertnumber, ADD INDEX ( sms_provider_id ); + +ALTER TABLE borrowers ADD FOREIGN KEY ( sms_provider_id ) REFERENCES sms_providers ( id ); diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index b98b017b34..48e2f18904 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -263,6 +263,7 @@ CREATE TABLE `borrowers` ( -- this table includes information about your patrons `altcontactcountry` text default NULL, -- the country for the alternate contact for the patron/borrower `altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower `smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SNS turned on) + `sms_provider_id` int(11) DEFAULT NULL, -- the provider of the mobile phone number defined in smsalertnumber `privacy` integer(11) DEFAULT '1' NOT NULL, -- patron/borrower's privacy settings related to their reading history `privacy_guarantor_checkouts` tinyint(1) NOT NULL DEFAULT '0', -- controls if relatives can see this patron's checkouts UNIQUE KEY `cardnumber` (`cardnumber`), @@ -274,8 +275,10 @@ CREATE TABLE `borrowers` ( -- this table includes information about your patrons KEY `surname_idx` (`surname`(255)), KEY `firstname_idx` (`firstname`(255)), KEY `othernames_idx` (`othernames`(255)), + KEY `sms_provider_id` (`sms_provider_id`), CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`), CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) + CONSTRAINT `borrowers_ibfk_3` FOREIGN KEY (`sms_provider_id`) REFERENCES `sms_providers` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- @@ -1996,6 +1999,19 @@ CREATE TABLE sessions ( PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; +-- +-- Table structure for table `sms_providers` +-- + +DROP TABLE IF EXISTS sms_providers; +CREATE TABLE `sms_providers` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `name` varchar(255) NOT NULL, + `domain` varchar(255) NOT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `name` (`name`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; + -- -- Table structure for table `special_holidays` -- diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/admin-home.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/admin-home.tt index 633b3ccb64..d3285c7567 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/admin-home.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/admin-home.tt @@ -110,6 +110,10 @@
Hide or show columns for tables.
Audio alerts
Define which events trigger which sounds
+ [% IF SMSSendDriver == 'Email' %] +
SMS cellular providers
+
Define a list of cellular providers for sending SMS messages via email.
+ [% END %] diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/sms_providers.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/sms_providers.tt new file mode 100644 index 0000000000..6bae201017 --- /dev/null +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/sms_providers.tt @@ -0,0 +1,102 @@ +[% INCLUDE 'doc-head-open.inc' %] +Koha › Administration › SMS cellular providers +[% INCLUDE 'doc-head-close.inc' %] + + + + +[% INCLUDE 'header.inc' %] + + + +
+
+
+
+

SMS cellular providers

+ + + + + + + + + + + + + [% FOREACH p IN providers %] + + + + + + + [% END %] + + + + + + + + + + + + + + +
NameDomain  
[% p.name %][% p.domain %]EditDelete
+ + + Cancel
+
+
+
+ [% INCLUDE 'admin-menu.inc' %] +
+
+
+[% INCLUDE 'intranet-bottom.inc' %] diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/members/memberentrygen.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/members/memberentrygen.tt index 8a8d3d48d5..616c2c629c 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/members/memberentrygen.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/members/memberentrygen.tt @@ -1115,6 +1115,19 @@

+

+ + +

[% END %] [% END %] [% END %] diff --git a/koha-tmpl/opac-tmpl/bootstrap/en/modules/opac-messaging.tt b/koha-tmpl/opac-tmpl/bootstrap/en/modules/opac-messaging.tt index 1f30489989..d1d30fac2d 100644 --- a/koha-tmpl/opac-tmpl/bootstrap/en/modules/opac-messaging.tt +++ b/koha-tmpl/opac-tmpl/bootstrap/en/modules/opac-messaging.tt @@ -119,9 +119,30 @@
- [% IF ( SMSSendDriver ) %] -
- [% END %] + [% IF ( SMSSendDriver ) %] +
  1. Some charges for text messages may be incurred when using this service. Please check with your mobile service provider if you have questions.
+
  1. + + Please enter numbers only. (123) 456-7890 would be entered as 1234567890. +
+ [% END %] + + [% IF ( SMSSendDriver == 'Email' ) %] +
  1. + + + Please contact a library staff member if you are unsure of your mobile service provider, or you do not see your provider in this list. +
+ [% END %]
diff --git a/members/memberentry.pl b/members/memberentry.pl index 843cbeb36f..48ea65ee8a 100755 --- a/members/memberentry.pl +++ b/members/memberentry.pl @@ -46,6 +46,7 @@ use Module::Load; if ( C4::Context->preference('NorwegianPatronDBEnable') && C4::Context->preference('NorwegianPatronDBEnable') == 1 ) { load Koha::NorwegianPatronDB, qw( NLGetSyncDataFromBorrowernumber ); } +use Koha::SMS::Provider; use vars qw($debug); @@ -68,6 +69,11 @@ my ($template, $loggedinuser, $cookie) debug => ($debug) ? 1 : 0, }); +if ( C4::Context->preference('SMSSendDriver') eq 'Email' ) { + my @providers = Koha::SMS::Provider->all(); + $template->param( sms_providers => \@providers ); +} + my $guarantorid = $input->param('guarantorid'); my $borrowernumber = $input->param('borrowernumber'); my $actionType = $input->param('actionType') || ''; diff --git a/opac/opac-messaging.pl b/opac/opac-messaging.pl index 8ccc5006a3..6fe18a7c5f 100755 --- a/opac/opac-messaging.pl +++ b/opac/opac-messaging.pl @@ -50,8 +50,11 @@ my $messaging_options = C4::Members::Messaging::GetMessagingOptions(); if ( defined $query->param('modify') && $query->param('modify') eq 'yes' ) { my $sms = $query->param('SMSnumber'); if ( defined $sms && ( $borrower->{'smsalertnumber'} // '' ) ne $sms ) { - ModMember( borrowernumber => $borrowernumber, - smsalertnumber => $sms ); + ModMember( + borrowernumber => $borrowernumber, + smsalertnumber => $sms, + sms_provider_id => $query->param('sms_provider_id'), + ); $borrower = C4::Members::GetMember( borrowernumber => $borrowernumber ); } @@ -66,4 +69,9 @@ $template->param( BORROWER_INFO => $borrower, SMSSendDriver => C4::Context->preference("SMSSendDriver"), TalkingTechItivaPhone => C4::Context->preference("TalkingTechItivaPhoneNotification") ); +if ( C4::Context->preference("SMSSendDriver") eq 'Email' ) { + my @providers = Koha::SMS::Provider->all(); + $template->param( sms_providers => \@providers, sms_provider_id => $borrower->{'sms_provider_id'} ); +} + output_html_with_http_headers $query, $cookie, $template->output, undef, { force_no_caching => 1 }; -- 2.39.5