SMSSendDriver => C4::Context->preference("SMSSendDriver"),

				TalkingTechItivaPhone => C4::Context->preference("TalkingTechItivaPhoneNotification"),

#! /usr/bin/perl

use strict;

use warnings;

use C4::Context;

my $dbh = C4::Context->dbh;

# add phone message transport type

$dbh->do("INSERT INTO message_transport_types (message_transport_type) VALUES ('phone')");

# adds HOLD_PHONE and PREDUE_PHONE letters (as placeholders)

$dbh->do("INSERT INTO letter (module, code, name, title, content) VALUES

          ('reserves', 'HOLD_PHONE', 'Item Available for Pick-up (phone notice)', 'Item Available for Pick-up (phone notice)', 'Your item is available for pickup'),

          ('circulation', 'PREDUE_PHONE', 'Advance Notice of Item Due (phone notice)', 'Advance Notice of Item Due (phone notice)', 'Your item is due soon'),

          ('circulation', 'OVERDUE_PHONE', 'Overdue Notice (phone notice)', 'Overdue Notice (phone notice)', 'Your item is overdue')

          ");

# add phone notifications to patron message preferences options

$dbh->do("INSERT INTO message_transports

         (message_attribute_id, message_transport_type, is_digest, letter_module, letter_code) VALUES

         (4, 'phone', 0, 'reserves', 'HOLD_PHONE'),

         (2, 'phone', 0, 'circulation', 'PREDUE_PHONE')

         ");

# add TalkingTechItivaPhoneNotification syspref

$dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('TalkingTechItivaPhoneNotification',0,'If ON, enables Talking Tech I-tiva phone notifications',NULL,'YesNo');");

print "Upgrade done (Support for Talking Tech i-tiva phone notification system)\n";

        [% IF ( TalkingTechItivaPhone ) %]<th>Phone</th>[% END %]

      [% IF ( TalkingTechItivaPhone ) %]<td>

          [% IF ( messaging_form_inactive ) %]

              [% IF ( messaging_preference.transports_phone ) %]

                 <input type="checkbox"

                 id="phone[% messaging_preference.message_attribute_id %]"

                 name="[% messaging_preference.message_attribute_id %]"

                 value="phone" checked="checked" disabled="disabled" />

              [% ELSE %]

                 <input type="checkbox"

                 id="phone[% messaging_preference.message_attribute_id %]"

                 name="[% messaging_preference.message_attribute_id %]"

                 value="phone" disabled="disabled" />

              [% END %]

          [% ELSE %]

              [% IF ( messaging_preference.transports_phone ) %]

                 <input type="checkbox"

                 id="phone[% messaging_preference.message_attribute_id %]"

                 name="[% messaging_preference.message_attribute_id %]"

                 value="phone" checked="checked" onclick = "$('#none'+'[% messaging_preference.message_attribute_id %]').attr('checked','');" />

              [% ELSE %]

                 <input type="checkbox"

                 id="phone[% messaging_preference.message_attribute_id %]"

                 name="[% messaging_preference.message_attribute_id %]"

                 value="phone" onclick = "$('#none'+'[% messaging_preference.message_attribute_id %]').attr('checked','');" />

              [% END %]

          [% END %]

      </td>[% END %]

     -

         - pref: TalkingTechItivaPhoneNotification

           choices: 

               yes: Enable

               no: Disable

         - patron phone notifications using Talking Tech i-tiva (overdues, predues and holds notices currently supported).

    $template->param(TalkingTechItivaPhone => C4::Context->preference("TalkingTechItivaPhoneNotification"));

    $template->param(TalkingTechItivaPhone => C4::Context->preference("TalkingTechItivaPhoneNotification"));

Installation and Setup instructions for Talking Tech I-tiva phone notification

=============================

Be sure you've run installer/data/mysql/atomicupdate/Bug-4246-Talking-Tech-itiva-phone-notifications.pl

to install the required data pack (new syspref, notice placeholders and messaging transport preferences)

To use, TalkingTechItivaPhoneNotification syspref must be turned on.

If you wish to process PREOVERDUE or RESERVES messages, you'll need the EnhancedMessagingPreferences 

system preference turned on, and patrons to have filled in a perference for receiving these notices by phone.

For OVERDUE messages, overdue notice triggers must be configured under Koha -> Tools -> Overdue Notice Triggers.

Either branch-specific triggers or the default level triggers may be used (script will select whichever is appropriate).

Sending Notices file to I-tiva:

=============================

1. Add the TalkingTech_itiva_outbound.pl script to your crontab 

2. Add an FTP/SFTP or other transport method to send the output file to the I-tiva server

3. If you wish, archive the sent notices file in another directory after sending

Run TalkingTech_itiva_outbound.pl --help for more information

Receiving Results file from I-tiva:

=============================

1. Add an FTP/SFTP or other transport method to send the Results file to a known directory on your Koha server

2. Add the TalkingTech_itiva_inbound.pl script to your crontab, aimed at that directory

3. If you wish, archive the results file in another directory after processing

Run TalkingTech_itiva_inbound.pl --help for more information

#!/usr/bin/perl

#

# Copyright (C) 2011 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;

BEGIN {

    # find Koha's Perl modules

    # test carefully before changing this

    use FindBin;

    eval { require "$FindBin::Bin/../kohalib.pl" };

}

use Getopt::Long;

use Pod::Usage;

use C4::Context;

sub usage {

    pod2usage( -verbose => 2 );

    exit;

}

die "TalkingTechItivaPhoneNotification system preference not activated... dying\n" unless (C4::Context->preference("TalkingTechItivaPhoneNotification"));

# Database handle

my $dbh = C4::Context->dbh;

# Benchmarking

my $updated= 0;

my $total = 0;

# Options

my $verbose;

my $help;

my $infile;

GetOptions(

  'i|input:s' => \$infile,

  'v' => \$verbose,

  'help|h'   => \$help,

);

die pod2usage() if $help;

# initialize the input data, either file or query

if (defined $infile) {

   open(IN, $infile) || die ("Cannot open input file");

   print "Opening $infile\n" if (defined $verbose);

   while (<IN>) {

     # data should take to form "<Transaction ID>","<SUCCESS or FAIL>"

     s/["\n]//g; # strip quotes and newlines: they're unnecessary

     my @data = split(/,/);

     my $result = update_notice(@data);

     $updated += $result;

     $total++;

   }

} else {

    die pod2usage( -verbose => 1 );

}

print "$updated of $total results lines processed\n" if (defined $verbose);

=head1 NAME

TalkingTech_itiva_inbound.pl

=head1 SYNOPSIS

  TalkingTech_itiva_inbound.pl

  TalkingTech_itiva_inbound.pl -v --input=/tmp/talkingtech/results.csv

Script to process received Results files for Talking Tech i-tiva

phone notification system.

=over 8

=item B<--help> B<-h>

Prints this help

=item B<-v>

Provide verbose log information.

=item B<--input> B<-i>

REQUIRED. Path to incoming results file.

=back

=cut

sub update_notice {

   my $message_id = shift;

   my $status = shift;

   if ($status =~ m/SUCCESS/i) {

      $status = 'sent';

   } elsif ($status =~ m/FAIL/i) {

      $status = 'failed';

   } else {

      warn "unexpected status $status for message ID $message_id\n";

      return 0;

   }

   my $query = "UPDATE message_queue SET status = ? WHERE message_id = ? and status = 'pending'";

   my $sth = $dbh->prepare($query);

   my $result = $sth->execute($status,$message_id);

   return $result;

}

#!/usr/bin/perl

#

# Copyright (C) 2011 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;

BEGIN {

    # find Koha's Perl modules

    # test carefully before changing this

    use FindBin;

    eval { require "$FindBin::Bin/../kohalib.pl" };

}

use Getopt::Long;

use Pod::Usage;

use Date::Calc qw(Add_Delta_Days);

use C4::Context;

use C4::Items;

use C4::Letters;

use C4::Overdues;

use C4::Dates;

use C4::Calendar;

sub usage {

    pod2usage( -verbose => 2 );

    exit;

}

die "TalkingTechItivaPhoneNotification system preference not activated... dying\n" unless (C4::Context->preference("TalkingTechItivaPhoneNotification"));

# Database handle

my $dbh = C4::Context->dbh;

# Options

my $verbose;

my $language = "EN";

my @types;

my @holds_waiting_days_to_call;

my $help;

my $outfile;

# maps to convert I-tiva terms to Koha terms

my $type_module_map = { 'PREOVERDUE'  => 'circulation',

                        'OVERDUE' => 'circulation',

                        'RESERVE'    => 'reserves',

                      };

my $type_notice_map = { 'PREOVERDUE'  => 'PREDUE_PHONE',

                        'OVERDUE' => 'OVERDUE_PHONE',

                        'RESERVE'    => 'HOLD_PHONE',

                      };

GetOptions(

  'o|output:s' => \$outfile,

  'v' => \$verbose,

  'lang:s' => \$language,

  'type:s' => \@types,

  'w|waiting-hold-day:s' => \@holds_waiting_days_to_call,

  'help|h'   => \$help,

);

$language = uc($language);

pod2usage( -verbose => 1 ) if $help;

# output log or STDOUT

if (defined $outfile) {

   open (OUT, ">$outfile") || die ("Cannot open output file");

} else {

   print "No output file defined; printing to STDOUT\n" if (defined $verbose);

   open(OUT, ">&STDOUT") || die ("Couldn't duplicate STDOUT: $!");

}

my $format = 'V'; # format for phone notifications

foreach my $type (@types) {

   $type = uc($type); #just in case lower or mixed-case was supplied

   my $module = $type_module_map->{$type}; #since the module is required to get the letter

   my $code = $type_notice_map->{$type}; #to get the Koha name of the notice

   my @loop;

   if ($type eq 'OVERDUE') {

     @loop = GetOverdueIssues();

   } elsif ($type eq 'PREOVERDUE') {

     @loop = GetPredueIssues();

   } elsif ($type eq 'RESERVE') {

     @loop = GetWaitingHolds();

   } else {

      print "Unknown or unsupported message type $type; skipping...\n" if (defined $verbose);

      next;

   }

   foreach my $issues (@loop) {

      my $date = C4::Dates->new($issues->{'date_due'}, 'iso');

      my $due_date = $date->output('metric');

      # gets the placeholder message, and enqueues the letter

      my $letter = getletter($module, $code);

      die "No letter found for type $type!... dying\n" unless $letter;

      # covers basic variable parsing in letter

      $letter = C4::Letters::parseletter($letter, 'borrowers', $issues->{'borrowernumber'});

      $letter = C4::Letters::parseletter($letter, 'biblio', $issues->{'biblionumber'});

      $letter = C4::Letters::parseletter($letter, 'biblioitems', $issues->{'biblionumber'});

      my $message_id = 0;

      if ($outfile) {

         $message_id = C4::Letters::EnqueueLetter( {  letter => $letter,

                                                      borrowernumber => $issues->{'borrowernumber'},

                                                      message_transport_type => 'phone',

                                                   });

      }

      print OUT "\"$format\",\"$language\",\"$type\",\"$issues->{level}\",\"$issues->{cardnumber}\",\"$issues->{patron_title}\",\"$issues->{firstname}\",";

      print OUT "\"$issues->{surname}\",\"$issues->{phone}\",\"$issues->{email}\",\"$issues->{branchcode}\",";

      print OUT "\"$issues->{site}\",\"$issues->{site_name}\",\"$issues->{barcode}\",\"$due_date\",\"$issues->{title}\",\"$message_id\"\n";

   }

}

=head1 NAME

TalkingTech_itiva_outbound.pl

=head1 SYNOPSIS

  TalkingTech_itiva_outbound.pl

  TalkingTech_itiva_outbound.pl --type=OVERDUE -w 0 -w 2 -w 6 --output=/tmp/talkingtech/outbound.csv

  TalkingTech_itiva_outbound.pl --type=RESERVE --type=PREOVERDUE --lang=FR

Script to generate Spec C outbound notifications file for Talking Tech i-tiva

phone notification system.

=item B<--help> B<-h>

Prints this help

=item B<-v>

Provide verbose log information.

=item B<--output> B<-o>

Destination for outbound notifications file (CSV format).  If no value is specified,

output is dumped to screen.

=item B<--lang>

Sets the language for all outbound messages.  Currently supported values are EN, FR and ES.

If no value is specified, EN will be used by default.

=item B<--type>

REQUIRED. Sets which messaging types are to be used.  Can be given multiple times, to 

specify multiple types in a single output file.  Currently supported values are RESERVE, PREOVERDUE

and OVERDUE.  If no value is given, this script will not produce any outbound notifications.

=item B<--waiting-hold-day> B<-w>

OPTIONAL for --type=RESERVE. Sets the days after a hold has been set to waiting on which to call. Use

switch as many times as desired. For example, passing "-w 0 -w 2 -w 6" will cause calls to be placed

on the day the hold was set to waiting, 2 days after the waiting date, and 6 days after. See example above.

If this switch is not used with --type=RESERVE, calls will be placed every day until the waiting reserve

is picked up or canceled.

=cut

sub GetOverdueIssues {

   my $query = "SELECT borrowers.borrowernumber, borrowers.cardnumber, borrowers.title as patron_title, borrowers.firstname, borrowers.surname,

                borrowers.phone, borrowers.email, borrowers.branchcode, biblio.biblionumber, biblio.title, items.barcode, issues.date_due,

                max(overduerules.branchcode) as rulebranch, TO_DAYS(NOW())-TO_DAYS(date_due) as daysoverdue, delay1, delay2, delay3,

                issues.branchcode as site, branches.branchname as site_name

                FROM borrowers JOIN issues USING (borrowernumber)

                JOIN items USING (itemnumber)

                JOIN biblio USING (biblionumber)

                JOIN branches ON (issues.branchcode = branches.branchcode)

                JOIN overduerules USING (categorycode)

                WHERE ( overduerules.branchcode = borrowers.branchcode or overduerules.branchcode = '')

                AND ( (TO_DAYS(NOW())-TO_DAYS(date_due) ) = delay1

                  OR  (TO_DAYS(NOW())-TO_DAYS(date_due) ) = delay2

                  OR  (TO_DAYS(NOW())-TO_DAYS(date_due) ) = delay3 )

                GROUP BY items.itemnumber

                ";

   my $sth = $dbh->prepare($query);

   $sth->execute();

   my @results;

   while( my $issue = $sth->fetchrow_hashref() ) {

      if ( $issue->{'daysoverdue'} == $issue->{'delay1'} ) {

         $issue->{'level'} = 1;

      } elsif ( $issue->{'daysoverdue'} == $issue->{'delay2'} ) {

         $issue->{'level'} = 2;

      } elsif ( $issue->{'daysoverdue'} == $issue->{'delay3'} ) {

         $issue->{'level'} = 3;

      } else { 

      # this shouldn't ever happen, based our SQL criteria

      }

      push @results, $issue;

   }

   return @results;

}

sub GetPredueIssues {

   my $query = "SELECT borrowers.borrowernumber, borrowers.cardnumber, borrowers.title as patron_title, borrowers.firstname, borrowers.surname,

                borrowers.phone, borrowers.email, borrowers.branchcode, biblio.biblionumber, biblio.title, items.barcode, issues.date_due,

                issues.branchcode as site, branches.branchname as site_name

                FROM borrowers JOIN issues USING (borrowernumber)

                JOIN items USING (itemnumber)

                JOIN biblio USING (biblionumber)

                JOIN branches ON (issues.branchcode = branches.branchcode)

                JOIN borrower_message_preferences USING (borrowernumber)

                JOIN borrower_message_transport_preferences USING (borrower_message_preference_id)

                JOIN message_attributes USING (message_attribute_id)

                WHERE ( TO_DAYS( date_due ) - TO_DAYS( NOW() ) ) = days_in_advance

                AND message_transport_type = 'phone'

                AND message_name = 'Advance_Notice'

                ";

   my $sth = $dbh->prepare($query);

   $sth->execute();

   my @results;

   while( my $issue = $sth->fetchrow_hashref() ) {

      $issue->{'level'} = 1; # only one level for Predue notifications

      push @results, $issue;

   }

   return @results;

}

sub GetWaitingHolds {

   my $query = "SELECT borrowers.borrowernumber, borrowers.cardnumber, borrowers.title as patron_title, borrowers.firstname, borrowers.surname,

                borrowers.phone, borrowers.email, borrowers.branchcode, biblio.biblionumber, biblio.title, items.barcode, reserves.waitingdate,

                reserves.branchcode AS site, branches.branchname AS site_name,

                TO_DAYS(NOW())-TO_DAYS(reserves.waitingdate) AS days_since_waiting

                FROM borrowers JOIN reserves USING (borrowernumber)

                JOIN items USING (itemnumber)

                JOIN biblio ON (biblio.biblionumber = items.biblionumber)

                JOIN branches ON (reserves.branchcode = branches.branchcode)

                JOIN borrower_message_preferences USING (borrowernumber)

                JOIN borrower_message_transport_preferences USING (borrower_message_preference_id)

                JOIN message_attributes USING (message_attribute_id)

                WHERE ( reserves.found = 'W' )

                AND message_transport_type = 'phone'

                AND message_name = 'Hold_Filled'

                ";

   my $pickupdelay = C4::Context->preference("ReservesMaxPickUpDelay");

   my $sth = $dbh->prepare($query);

   $sth->execute();

   my @results;

   while( my $issue = $sth->fetchrow_hashref() ) {

      my @waitingdate = split( /-/, $issue->{'waitingdate'} );

      my @date_due = Add_Delta_Days( $waitingdate[0], $waitingdate[1], $waitingdate[2], $pickupdelay );

      $issue->{'date_due'} = sprintf( "%04d-%02d-%02d", $date_due[0], $date_due[1], $date_due[2] );

      $issue->{'level'} = 1; # only one level for Hold Waiting notifications

      my $days_to_subtract = 0;

      my $calendar = C4::Calendar->new( branchcode => $issue->{'site'} );

      while( $calendar->isHoliday( reverse( Add_Delta_Days( $waitingdate[0], $waitingdate[1], $waitingdate[2], $days_to_subtract ) ) ) ) {

        $days_to_subtract++;

      }

      $issue->{'days_since_waiting'} = $issue->{'days_since_waiting'} - $days_to_subtract;

      if ( ( grep $_ eq $issue->{'days_since_waiting'}, @holds_waiting_days_to_call ) || !scalar( @holds_waiting_days_to_call ) ) {

          push @results, $issue;

      }

   }

   return @results;

}