From 715a55a704acf5e422c51fc1dadcab29f0656ad0 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. --- C4/Letters.pm | 12 ++- C4/Members.pm | 7 +- Koha/SMS/Provider.pm | 157 ++++++++++++++++++++ admin/admin-home.pl | 4 + admin/sms_providers.pl | 59 ++++++++ installer/data/mysql/kohastructure.sql | 18 ++- installer/data/mysql/updatedatabase.pl | 19 +++ .../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 | 9 +- 13 files changed, 430 insertions(+), 7 deletions(-) create mode 100644 Koha/SMS/Provider.pm create mode 100755 admin/sms_providers.pl 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 a11c862..a7c0886 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; @@ -927,7 +929,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 ); @@ -1166,6 +1175,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 d88ea45..5c55cb6 100644 --- a/C4/Members.pm +++ b/C4/Members.pm @@ -797,8 +797,13 @@ sub ModMember { $data{password} = hash_password($data{password}); } } + my $old_categorycode = GetBorrowerCategorycode( $data{borrowernumber} ); - my $execute_success=UpdateInTable("borrowers",\%data); + + $data{'sms_provider_id'} = undef unless ( $data{'sms_provider_id'} ); + + my $execute_success = UpdateInTable( "borrowers", \%data ); + if ($execute_success) { # 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 diff --git a/Koha/SMS/Provider.pm b/Koha/SMS/Provider.pm new file mode 100644 index 0000000..6331088 --- /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 b431b49..16aefa0 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 0000000..4daea4a --- /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/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 4ffbbf2..ada242a 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -264,6 +264,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 UNIQUE KEY `cardnumber` (`cardnumber`), PRIMARY KEY `borrowernumber` (`borrowernumber`), @@ -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_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; -- @@ -1983,6 +1986,19 @@ CREATE TABLE sessions ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- +-- 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; + +-- -- Table structure for table `special_holidays` -- diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index cf17e0b..2203f13 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -9585,6 +9585,25 @@ if ( CheckVersion($DBversion) ) { SetVersion ($DBversion); } +$DBversion = "3.19.00.XXX"; +if (C4::Context->preference("Version") < TransformToNum($DBversion)) { + $dbh->do(" + 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; + "); + $dbh->do("ALTER TABLE borrowers ADD sms_provider_id INT( 11 ) NULL DEFAULT NULL AFTER smsalertnumber, ADD INDEX ( sms_provider_id )"); + $dbh->do("ALTER TABLE borrowers ADD FOREIGN KEY ( sms_provider_id ) REFERENCES sms_providers ( id )"); + + print "Upgrade to $DBversion done (Add SMS via Email feature)\n"; + SetVersion ($DBversion); +} + =head1 FUNCTIONS =head2 TableExists($table) 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 02bc60a..378e93b 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 @@ -104,6 +104,10 @@
Choose which plugins to use to suggest searches to patrons and staff.
Configure columns
Hide or show columns for tables.
+ [% 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 0000000..6bae201 --- /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 f9da10b..cde0f2c 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/members/memberentrygen.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/members/memberentrygen.tt @@ -1382,6 +1382,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 c13adc4..365c218 100644 --- a/koha-tmpl/opac-tmpl/bootstrap/en/modules/opac-messaging.tt +++ b/koha-tmpl/opac-tmpl/bootstrap/en/modules/opac-messaging.tt @@ -110,9 +110,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 7a0329f..74f2bcf 100755 --- a/members/memberentry.pl +++ b/members/memberentry.pl @@ -47,6 +47,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); @@ -69,6 +70,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 096dbff..de377fe 100755 --- a/opac/opac-messaging.pl +++ b/opac/opac-messaging.pl @@ -54,7 +54,9 @@ if ( defined $query->param('modify') && $query->param('modify') eq 'yes' ) { # If they've modified the SMS number, record it. if ( ( defined $query->param('SMSnumber') ) && ( $query->param('SMSnumber') ne $borrower->{'mobile'} ) ) { ModMember( borrowernumber => $borrowernumber, - smsalertnumber => $query->param('SMSnumber') ); + smsalertnumber => $query->param('SMSnumber'), + sms_provider_id => $query->param('sms_provider_id') + ); $borrower = GetMemberDetails( $borrowernumber ); } @@ -70,4 +72,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; -- 1.7.2.5