misc/cronjobs/thirdparty/TalkingTech_itiva_outbound.pl

   1 #!/usr/bin/perl
   2 #
   3 # Copyright (C) 2011 ByWater Solutions
   4 #
   5 # This file is part of Koha.
   6 #
   7 # Koha is free software; you can redistribute it and/or modify it
   8 # under the terms of the GNU General Public License as published by
   9 # the Free Software Foundation; either version 3 of the License, or
  10 # (at your option) any later version.
  11 #
  12 # Koha is distributed in the hope that it will be useful, but
  13 # WITHOUT ANY WARRANTY; without even the implied warranty of
  14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  15 # GNU General Public License for more details.
  16 #
  17 # You should have received a copy of the GNU General Public License
  18 # along with Koha; if not, see <http://www.gnu.org/licenses>.
  19
  20 use strict;
  21 use warnings;
  22
  23 BEGIN {
  24
  25     # find Koha's Perl modules
  26     # test carefully before changing this
  27     use FindBin;
  28     eval { require "$FindBin::Bin/../kohalib.pl" };
  29 }
  30
  31 use Getopt::Long;
  32 use Pod::Usage;
  33 use Date::Calc qw(Add_Delta_Days);
  34
  35 use C4::Context;
  36 use C4::Items;
  37 use C4::Letters;
  38 use C4::Overdues;
  39 use C4::Calendar;
  40 use Koha::DateUtils;
  41
  42 sub usage {
  43     pod2usage( -verbose => 2 );
  44     exit;
  45 }
  46
  47 die
  48   "TalkingTechItivaPhoneNotification system preference not activated... dying\n"
  49   unless ( C4::Context->preference("TalkingTechItivaPhoneNotification") );
  50
  51 # Database handle
  52 my $dbh = C4::Context->dbh;
  53
  54 # Options
  55 my $verbose;
  56 my $language = "EN";
  57 my @types;
  58 my @holds_waiting_days_to_call;
  59 my $library_code;
  60 my $help;
  61 my $outfile;
  62
  63 # maps to convert I-tiva terms to Koha terms
  64 my $type_module_map = {
  65     'PREOVERDUE' => 'circulation',
  66     'OVERDUE'    => 'circulation',
  67     'RESERVE'    => 'reserves',
  68 };
  69
  70 my $type_notice_map = {
  71     'PREOVERDUE' => 'PREDUE',
  72     'OVERDUE'    => 'OVERDUE',
  73     'RESERVE'    => 'HOLD',
  74 };
  75
  76 GetOptions(
  77     'o|output:s'            => \$outfile,
  78     'v'                     => \$verbose,
  79     'lang:s'                => \$language,
  80     'type:s'                => \@types,
  81     'w|waiting-hold-day:s'  => \@holds_waiting_days_to_call,
  82     'c|code|library-code:s' => \$library_code,
  83     'help|h'                => \$help,
  84 );
  85
  86 $language = uc($language);
  87 $library_code ||= '';
  88
  89 pod2usage( -verbose => 1 ) if $help;
  90
  91 # output log or STDOUT
  92 my $OUT;
  93 if ( defined $outfile ) {
  94     open( $OUT, '>', "$outfile" ) || die("Cannot open output file");
  95 }
  96 else {
  97     print "No output file defined; printing to STDOUT\n"
  98       if ( defined $verbose );
  99     open( $OUT, '>', "&STDOUT" ) || die("Couldn't duplicate STDOUT: $!");
 100 }
 101
 102 my $format = 'V';    # format for phone notifications
 103
 104 foreach my $type (@types) {
 105     $type = uc($type);    #just in case lower or mixed-case was supplied
 106     my $module =
 107       $type_module_map->{$type}; #since the module is required to get the letter
 108     my $code = $type_notice_map->{$type};    #to get the Koha name of the notice
 109
 110     my @loop;
 111     if ( $type eq 'OVERDUE' ) {
 112         @loop = GetOverdueIssues();
 113     }
 114     elsif ( $type eq 'PREOVERDUE' ) {
 115         @loop = GetPredueIssues();
 116     }
 117     elsif ( $type eq 'RESERVE' ) {
 118         @loop = GetWaitingHolds();
 119     }
 120     else {
 121         print "Unknown or unsupported message type $type; skipping...\n"
 122           if ( defined $verbose );
 123         next;
 124     }
 125
 126     foreach my $issues (@loop) {
 127         my $date_dt = dt_from_string ( $issues->{'date_due'} );
 128         my $due_date = output_pref( { dt => $date_dt, dateonly => 1, dateformat =>'metric' } );
 129
 130         my $letter = C4::Letters::GetPreparedLetter(
 131             module      => $module,
 132             letter_code => $code,
 133             tables      => {
 134                 borrowers   => $issues->{'borrowernumber'},
 135                 biblio      => $issues->{'biblionumber'},
 136                 biblioitems => $issues->{'biblionumber'},
 137             },
 138             message_transport_type => 'phone',
 139         );
 140
 141         die "No letter found for type $type!... dying\n" unless $letter;
 142
 143         my $message_id = 0;
 144         if ($outfile) {
 145             $message_id = C4::Letters::EnqueueLetter(
 146                 {
 147                     letter                 => $letter,
 148                     borrowernumber         => $issues->{'borrowernumber'},
 149                     message_transport_type => 'phone',
 150                 }
 151             );
 152         }
 153
 154         print $OUT
 155 "\"$format\",\"$language\",\"$type\",\"$issues->{level}\",\"$issues->{cardnumber}\",\"$issues->{patron_title}\",\"$issues->{firstname}\",";
 156         print $OUT
 157 "\"$issues->{surname}\",\"$issues->{phone}\",\"$issues->{email}\",\"$library_code\",";
 158         print $OUT
 159 "\"$issues->{site}\",\"$issues->{site_name}\",\"$issues->{barcode}\",\"$due_date\",\"$issues->{title}\",\"$message_id\"\n";
 160     }
 161 }
 162
 163 =head1 NAME
 164
 165 TalkingTech_itiva_outbound.pl
 166
 167 =head1 SYNOPSIS
 168
 169   TalkingTech_itiva_outbound.pl
 170   TalkingTech_itiva_outbound.pl --type=OVERDUE -w 0 -w 2 -w 6 --output=/tmp/talkingtech/outbound.csv
 171   TalkingTech_itiva_outbound.pl --type=RESERVE --type=PREOVERDUE --lang=FR
 172
 173
 174 Script to generate Spec C outbound notifications file for Talking Tech i-tiva
 175 phone notification system.
 176
 177 =item B<--help> B<-h>
 178
 179 Prints this help
 180
 181 =item B<-v>
 182
 183 Provide verbose log information.
 184
 185 =item B<--output> B<-o>
 186
 187 Destination for outbound notifications file (CSV format).  If no value is specified,
 188 output is dumped to screen.
 189
 190 =item B<--lang>
 191
 192 Sets the language for all outbound messages.  Currently supported values are EN, FR and ES.
 193 If no value is specified, EN will be used by default.
 194
 195 =item B<--type>
 196
 197 REQUIRED. Sets which messaging types are to be used.  Can be given multiple times, to
 198 specify multiple types in a single output file.  Currently supported values are RESERVE, PREOVERDUE
 199 and OVERDUE.  If no value is given, this script will not produce any outbound notifications.
 200
 201 =item B<--waiting-hold-day> B<-w>
 202
 203 OPTIONAL for --type=RESERVE. Sets the days after a hold has been set to waiting on which to call. Use
 204 switch as many times as desired. For example, passing "-w 0 -w 2 -w 6" will cause calls to be placed
 205 on the day the hold was set to waiting, 2 days after the waiting date, and 6 days after. See example above.
 206 If this switch is not used with --type=RESERVE, calls will be placed every day until the waiting reserve
 207 is picked up or canceled.
 208
 209 =item B<--library-code> B<--code> B<-c>
 210
 211 OPTIONAL
 212 The code of the source library of the message.
 213 The library code is used to group notices together for
 214 consortium purposes and apply library specific settings, such as
 215 prompts, to those notices.
 216 This field can be blank if all messages are from a single library.
 217
 218 =cut
 219
 220 sub GetOverdueIssues {
 221     my $query =
 222 "SELECT borrowers.borrowernumber, borrowers.cardnumber, borrowers.title as patron_title, borrowers.firstname, borrowers.surname,
 223                 borrowers.phone, borrowers.email, borrowers.branchcode, biblio.biblionumber, biblio.title, items.barcode, issues.date_due,
 224                 max(overduerules.branchcode) as rulebranch, TO_DAYS(NOW())-TO_DAYS(date_due) as daysoverdue, delay1, delay2, delay3,
 225                 issues.branchcode as site, branches.branchname as site_name
 226                 FROM borrowers JOIN issues USING (borrowernumber)
 227                 JOIN items USING (itemnumber)
 228                 JOIN biblio USING (biblionumber)
 229                 JOIN branches ON (issues.branchcode = branches.branchcode)
 230                 JOIN overduerules USING (categorycode)
 231                 WHERE ( overduerules.branchcode = borrowers.branchcode or overduerules.branchcode = '')
 232                 AND ( (TO_DAYS(NOW())-TO_DAYS(date_due) ) = delay1
 233                   OR  (TO_DAYS(NOW())-TO_DAYS(date_due) ) = delay2
 234                   OR  (TO_DAYS(NOW())-TO_DAYS(date_due) ) = delay3 )
 235                 GROUP BY items.itemnumber
 236                 ";
 237     my $sth = $dbh->prepare($query);
 238     $sth->execute();
 239     my @results;
 240     while ( my $issue = $sth->fetchrow_hashref() ) {
 241         if ( $issue->{'daysoverdue'} == $issue->{'delay1'} ) {
 242             $issue->{'level'} = 1;
 243         }
 244         elsif ( $issue->{'daysoverdue'} == $issue->{'delay2'} ) {
 245             $issue->{'level'} = 2;
 246         }
 247         elsif ( $issue->{'daysoverdue'} == $issue->{'delay3'} ) {
 248             $issue->{'level'} = 3;
 249         }
 250         else {
 251
 252             # this shouldn't ever happen, based our SQL criteria
 253         }
 254         push @results, $issue;
 255     }
 256     return @results;
 257 }
 258
 259 sub GetPredueIssues {
 260     my $query =
 261 "SELECT borrowers.borrowernumber, borrowers.cardnumber, borrowers.title as patron_title, borrowers.firstname, borrowers.surname,
 262                 borrowers.phone, borrowers.email, borrowers.branchcode, biblio.biblionumber, biblio.title, items.barcode, issues.date_due,
 263                 issues.branchcode as site, branches.branchname as site_name
 264                 FROM borrowers JOIN issues USING (borrowernumber)
 265                 JOIN items USING (itemnumber)
 266                 JOIN biblio USING (biblionumber)
 267                 JOIN branches ON (issues.branchcode = branches.branchcode)
 268                 JOIN borrower_message_preferences USING (borrowernumber)
 269                 JOIN borrower_message_transport_preferences USING (borrower_message_preference_id)
 270                 JOIN message_attributes USING (message_attribute_id)
 271                 WHERE ( TO_DAYS( date_due ) - TO_DAYS( NOW() ) ) = days_in_advance
 272                 AND message_transport_type = 'phone'
 273                 AND message_name = 'Advance_Notice'
 274                 ";
 275     my $sth = $dbh->prepare($query);
 276     $sth->execute();
 277     my @results;
 278     while ( my $issue = $sth->fetchrow_hashref() ) {
 279         $issue->{'level'} = 1;    # only one level for Predue notifications
 280         push @results, $issue;
 281     }
 282     return @results;
 283 }
 284
 285 sub GetWaitingHolds {
 286     my $query =
 287 "SELECT borrowers.borrowernumber, borrowers.cardnumber, borrowers.title as patron_title, borrowers.firstname, borrowers.surname,
 288                 borrowers.phone, borrowers.email, borrowers.branchcode, biblio.biblionumber, biblio.title, items.barcode, reserves.waitingdate,
 289                 reserves.branchcode AS site, branches.branchname AS site_name,
 290                 TO_DAYS(NOW())-TO_DAYS(reserves.waitingdate) AS days_since_waiting
 291                 FROM borrowers JOIN reserves USING (borrowernumber)
 292                 JOIN items USING (itemnumber)
 293                 JOIN biblio ON (biblio.biblionumber = items.biblionumber)
 294                 JOIN branches ON (reserves.branchcode = branches.branchcode)
 295                 JOIN borrower_message_preferences USING (borrowernumber)
 296                 JOIN borrower_message_transport_preferences USING (borrower_message_preference_id)
 297                 JOIN message_attributes USING (message_attribute_id)
 298                 WHERE ( reserves.found = 'W' )
 299                 AND message_transport_type = 'phone'
 300                 AND message_name = 'Hold_Filled'
 301                 ";
 302     my $pickupdelay = C4::Context->preference("ReservesMaxPickUpDelay");
 303     my $sth         = $dbh->prepare($query);
 304     $sth->execute();
 305     my @results;
 306     while ( my $issue = $sth->fetchrow_hashref() ) {
 307         my @waitingdate = split( /-/, $issue->{'waitingdate'} );
 308         my @date_due =
 309           Add_Delta_Days( $waitingdate[0], $waitingdate[1], $waitingdate[2],
 310             $pickupdelay );
 311         $issue->{'date_due'} =
 312           sprintf( "%04d-%02d-%02d", $date_due[0], $date_due[1], $date_due[2] );
 313         $issue->{'level'} = 1;   # only one level for Hold Waiting notifications
 314
 315         my $days_to_subtract = 0;
 316         my $calendar = C4::Calendar->new( branchcode => $issue->{'site'} );
 317         while (
 318             $calendar->isHoliday(
 319                 reverse(
 320                     Add_Delta_Days(
 321                         $waitingdate[0], $waitingdate[1],
 322                         $waitingdate[2], $days_to_subtract
 323                     )
 324                 )
 325             )
 326           )
 327         {
 328             $days_to_subtract++;
 329         }
 330         $issue->{'days_since_waiting'} =
 331           $issue->{'days_since_waiting'} - $days_to_subtract;
 332
 333         if (
 334             (
 335                 grep $_ eq $issue->{'days_since_waiting'},
 336                 @holds_waiting_days_to_call
 337             )
 338             || !scalar(@holds_waiting_days_to_call)
 339           )
 340         {
 341             push @results, $issue;
 342         }
 343     }
 344     return @results;
 345
 346 }