From 7ae9294307d08debc48c7db0bfd25712639aadb3 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 | 5 +- admin/sms_providers.pl | 59 ++++++++ installer/data/mysql/kohastructure.sql | 18 ++- installer/data/mysql/updatedatabase.pl | 19 +++ .../prog/en/modules/admin/admin-home.tt | 15 ++- .../prog/en/modules/admin/sms_providers.tt | 102 +++++++++++++ .../prog/en/modules/members/memberentrygen.tt | 13 ++ .../opac-tmpl/prog/en/modules/opac-messaging.tt | 25 +++- members/memberentry.pl | 7 + opac/opac-messaging.pl | 9 +- 13 files changed, 439 insertions(+), 9 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 93bc34d..fdc94d9 100644 --- a/C4/Letters.pm +++ b/C4/Letters.pm @@ -30,6 +30,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; @@ -752,7 +754,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 ); @@ -959,6 +968,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 7ac69df..4d110b2 100644 --- a/C4/Members.pm +++ b/C4/Members.pm @@ -753,8 +753,13 @@ sub ModMember { $data{password} = md5_base64($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..c1f80ee --- /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 2 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 bf3d9af..c5f0a6a 100755 --- a/admin/admin-home.pl +++ b/admin/admin-home.pl @@ -34,6 +34,9 @@ my ($template, $loggedinuser, $cookie) debug => 1, }); -$template->param( SearchEngine => C4::Context->preference('SearchEngine') ); +$template->param( + SearchEngine => C4::Context->preference('SearchEngine'), + 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..a6ee013 --- /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 2 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.tmpl", + 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 fdab879..94caf4e 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 UNIQUE KEY `cardnumber` (`cardnumber`), PRIMARY KEY `borrowernumber` (`borrowernumber`), @@ -270,8 +271,10 @@ CREATE TABLE `borrowers` ( -- this table includes information about your patrons KEY `branchcode` (`branchcode`), KEY `userid` (`userid`), KEY `guarantorid` (`guarantorid`), + 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; -- @@ -1919,6 +1922,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 a37ea02..8b667ef 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -7155,6 +7155,25 @@ if ( CheckVersion($DBversion) ) { SetVersion($DBversion); } +$DBversion = "3.13.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 a5dbe44..3a5c75e 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 @@ -101,12 +101,21 @@

Additional parameters

- + +
Z39.50 client targets
-
Define which servers to query for MARC data in the integrated Z39.50 client.
+
Define which servers to query for MARC data in the integrated Z39.50 client.
+
Did you mean?
Choose which plugins to use to suggest searches to patrons and staff.
+ + [% 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 cfd81c8..28e73bd 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/members/memberentrygen.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/members/memberentrygen.tt @@ -1296,6 +1296,19 @@

+

+ + +

[% END %] [% END %] [% END %] diff --git a/koha-tmpl/opac-tmpl/prog/en/modules/opac-messaging.tt b/koha-tmpl/opac-tmpl/prog/en/modules/opac-messaging.tt index dfd6eaa..5c934ca 100644 --- a/koha-tmpl/opac-tmpl/prog/en/modules/opac-messaging.tt +++ b/koha-tmpl/opac-tmpl/prog/en/modules/opac-messaging.tt @@ -144,8 +144,31 @@ [% END %] -[% 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 0bf3122..d0cf900 100755 --- a/members/memberentry.pl +++ b/members/memberentry.pl @@ -41,6 +41,7 @@ use C4::Log; use C4::Letters; use C4::Branch; # GetBranches use C4::Form::MessagingPreferences; +use Koha::SMS::Provider; use vars qw($debug); @@ -62,6 +63,12 @@ my ($template, $loggedinuser, $cookie) flagsrequired => {borrowers => 1}, 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 dc244db..e42d8e8 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