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
  34 use C4::Context;
  35 use C4::Items;
  36 use C4::Letters;
  37 use C4::Overdues;
  38 use Koha::Calendar;
  39 use Koha::DateUtils;
  40
  41 sub usage {
  42     pod2usage( -verbose => 2 );
  43     exit;
  44 }
  45
  46 die "TalkingTechItivaPhoneNotification system preference not activated... dying\n"
  47   unless ( C4::Context->preference("TalkingTechItivaPhoneNotification") );
  48
  49 # Database handle
  50 my $dbh = C4::Context->dbh;
  51
  52 # Options
  53 my $verbose;
  54 my $language = "EN";
  55 my @types;
  56 my @holds_waiting_days_to_call;
  57 my $library_code;
  58 my $help;
  59 my $outfile;
  60
  61 # maps to convert I-tiva terms to Koha terms
  62 my $type_module_map = {
  63     'PREOVERDUE' => 'circulation',
  64     'OVERDUE'    => 'circulation',
  65     'RESERVE'    => 'reserves',
  66 };
  67
  68 my $type_notice_map = {
  69     'PREOVERDUE' => 'PREDUE',
  70     'OVERDUE'    => 'OVERDUE',
  71     'RESERVE'    => 'HOLD',
  72 };
  73
  74 GetOptions(
  75     'o|output:s'            => \$outfile,
  76     'v'                     => \$verbose,
  77     'lang:s'                => \$language,
  78     'type:s'                => \@types,
  79     'w|waiting-hold-day:s'  => \@holds_waiting_days_to_call,
  80     'c|code|library-code:s' => \$library_code,
  81     'help|h'                => \$help,
  82 );
  83
  84 $language = uc($language);
  85 $library_code ||= '';
  86
  87 pod2usage( -verbose => 1 ) if $help;
  88
  89 # output log or STDOUT
  90 my $OUT;
  91 if ( defined $outfile ) {
  92     open( $OUT, '>', "$outfile" ) || die("Cannot open output file");
  93 } else {
  94     print "No output file defined; printing to STDOUT\n"
  95       if ( defined $verbose );
  96     open( $OUT, '>', "&STDOUT" ) || die("Couldn't duplicate STDOUT: $!");
  97 }
  98
  99 my $format = 'V';    # format for phone notifications
 100
 101 foreach my $type (@types) {
 102     $type = uc($type);    #just in case lower or mixed-case was supplied
 103     my $module = $type_module_map->{$type};    #since the module is required to get the letter
 104     my $code   = $type_notice_map->{$type};    #to get the Koha name of the notice
 105
 106     my @loop;
 107     if ( $type eq 'OVERDUE' ) {
 108         @loop = GetOverdueIssues();
 109     } elsif ( $type eq 'PREOVERDUE' ) {
 110         @loop = GetPredueIssues();
 111     } elsif ( $type eq 'RESERVE' ) {
 112         @loop = GetWaitingHolds();
 113     } else {
 114         print "Unknown or unsupported message type $type; skipping...\n"
 115           if ( defined $verbose );
 116         next;
 117     }
 118
 119     foreach my $issues (@loop) {
 120         my $date_dt = dt_from_string ( $issues->{'date_due'} );
 121         my $due_date = output_pref( { dt => $date_dt, dateonly => 1, dateformat =>'metric' } );
 122
 123         my $letter = C4::Letters::GetPreparedLetter(
 124             module      => $module,
 125             letter_code => $code,
 126             lang        => 'default', # It does not sound useful to send a lang here
 127             tables      => {
 128                 borrowers   => $issues->{'borrowernumber'},
 129                 biblio      => $issues->{'biblionumber'},
 130                 biblioitems => $issues->{'biblionumber'},
 131             },
 132             message_transport_type => 'phone',
 133         );
 134
 135         die "No letter found for type $type!... dying\n" unless $letter;
 136
 137         my $message_id = 0;
 138         if ($outfile) {
 139             $message_id = C4::Letters::EnqueueLetter(
 140                 {   letter                 => $letter,
 141                     borrowernumber         => $issues->{'borrowernumber'},
 142                     message_transport_type => 'phone',
 143                 }
 144             );
 145         }
 146
 147         print $OUT "\"$format\",\"$language\",\"$type\",\"$issues->{level}\",\"$issues->{cardnumber}\",\"$issues->{patron_title}\",\"$issues->{firstname}\",";
 148         print $OUT "\"$issues->{surname}\",\"$issues->{phone}\",\"$issues->{email}\",\"$library_code\",";
 149         print $OUT "\"$issues->{site}\",\"$issues->{site_name}\",\"$issues->{barcode}\",\"$due_date\",\"$issues->{title}\",\"$message_id\"\n";
 150     }
 151 }
 152
 153 =head1 NAME
 154
 155 TalkingTech_itiva_outbound.pl
 156
 157 =head1 SYNOPSIS
 158
 159   TalkingTech_itiva_outbound.pl
 160   TalkingTech_itiva_outbound.pl --type=OVERDUE -w 0 -w 2 -w 6 --output=/tmp/talkingtech/outbound.csv
 161   TalkingTech_itiva_outbound.pl --type=RESERVE --type=PREOVERDUE --lang=FR
 162
 163
 164 Script to generate Spec C outbound notifications file for Talking Tech i-tiva
 165 phone notification system.
 166
 167 =over
 168
 169 =item B<--help> B<-h>
 170
 171 Prints this help
 172
 173 =item B<-v>
 174
 175 Provide verbose log information.
 176
 177 =item B<--output> B<-o>
 178
 179 Destination for outbound notifications file (CSV format).  If no value is specified,
 180 output is dumped to screen.
 181
 182 =item B<--lang>
 183
 184 Sets the language for all outbound messages.  Currently supported values are EN, FR and ES.
 185 If no value is specified, EN will be used by default.
 186
 187 =item B<--type>
 188
 189 REQUIRED. Sets which messaging types are to be used.  Can be given multiple times, to
 190 specify multiple types in a single output file.  Currently supported values are RESERVE, PREOVERDUE
 191 and OVERDUE.  If no value is given, this script will not produce any outbound notifications.
 192
 193 =item B<--waiting-hold-day> B<-w>
 194
 195 OPTIONAL for --type=RESERVE. Sets the days after a hold has been set to waiting on which to call. Use
 196 switch as many times as desired. For example, passing "-w 0 -w 2 -w 6" will cause calls to be placed
 197 on the day the hold was set to waiting, 2 days after the waiting date, and 6 days after. See example above.
 198 If this switch is not used with --type=RESERVE, calls will be placed every day until the waiting reserve
 199 is picked up or canceled.
 200
 201 =item B<--library-code> B<--code> B<-c>
 202
 203 OPTIONAL
 204 The code of the source library of the message.
 205 The library code is used to group notices together for
 206 consortium purposes and apply library specific settings, such as
 207 prompts, to those notices.
 208 This field can be blank if all messages are from a single library.
 209
 210 =back
 211
 212 =cut
 213
 214 sub GetOverdueIssues {
 215     my $query = "SELECT borrowers.borrowernumber, borrowers.cardnumber, borrowers.title as patron_title, borrowers.firstname, borrowers.surname,
 216                 borrowers.phone, borrowers.email, borrowers.branchcode, biblio.biblionumber, biblio.title, items.barcode, issues.date_due,
 217                 max(overduerules.branchcode) as rulebranch, TO_DAYS(NOW())-TO_DAYS(date_due) as daysoverdue, delay1, delay2, delay3,
 218                 issues.branchcode as site, branches.branchname as site_name
 219                 FROM borrowers JOIN issues USING (borrowernumber)
 220                 JOIN items USING (itemnumber)
 221                 JOIN biblio USING (biblionumber)
 222                 JOIN branches ON (issues.branchcode = branches.branchcode)
 223                 JOIN overduerules USING (categorycode)
 224                 JOIN overduerules_transport_types USING ( overduerules_id )
 225                 WHERE ( overduerules.branchcode = borrowers.branchcode or overduerules.branchcode = '')
 226                 AND overduerules_transport_types.message_transport_type = 'phone'
 227                 AND ( (TO_DAYS(NOW())-TO_DAYS(date_due) ) = delay1
 228                   OR  (TO_DAYS(NOW())-TO_DAYS(date_due) ) = delay2
 229                   OR  (TO_DAYS(NOW())-TO_DAYS(date_due) ) = delay3 )
 230                 GROUP BY items.itemnumber
 231                 ";
 232     my $sth = $dbh->prepare($query);
 233     $sth->execute();
 234     my @results;
 235     while ( my $issue = $sth->fetchrow_hashref() ) {
 236         if ( $issue->{'daysoverdue'} == $issue->{'delay1'} ) {
 237             $issue->{'level'} = 1;
 238         } elsif ( $issue->{'daysoverdue'} == $issue->{'delay2'} ) {
 239             $issue->{'level'} = 2;
 240         } elsif ( $issue->{'daysoverdue'} == $issue->{'delay3'} ) {
 241             $issue->{'level'} = 3;
 242         } else {
 243
 244             # this shouldn't ever happen, based our SQL criteria
 245         }
 246         push @results, $issue;
 247     }
 248     return @results;
 249 }
 250
 251 sub GetPredueIssues {
 252     my $query = "SELECT borrowers.borrowernumber, borrowers.cardnumber, borrowers.title as patron_title, borrowers.firstname, borrowers.surname,
 253                 borrowers.phone, borrowers.email, borrowers.branchcode, biblio.biblionumber, biblio.title, items.barcode, issues.date_due,
 254                 issues.branchcode as site, branches.branchname as site_name
 255                 FROM borrowers JOIN issues USING (borrowernumber)
 256                 JOIN items USING (itemnumber)
 257                 JOIN biblio USING (biblionumber)
 258                 JOIN branches ON (issues.branchcode = branches.branchcode)
 259                 JOIN borrower_message_preferences USING (borrowernumber)
 260                 JOIN borrower_message_transport_preferences USING (borrower_message_preference_id)
 261                 JOIN message_attributes USING (message_attribute_id)
 262                 WHERE ( TO_DAYS( date_due ) - TO_DAYS( NOW() ) ) = days_in_advance
 263                 AND message_transport_type = 'phone'
 264                 AND message_name = 'Advance_Notice'
 265                 ";
 266     my $sth = $dbh->prepare($query);
 267     $sth->execute();
 268     my @results;
 269     while ( my $issue = $sth->fetchrow_hashref() ) {
 270         $issue->{'level'} = 1;    # only one level for Predue notifications
 271         push @results, $issue;
 272     }
 273     return @results;
 274 }
 275
 276 sub GetWaitingHolds {
 277     my $query = "SELECT borrowers.borrowernumber, borrowers.cardnumber, borrowers.title as patron_title, borrowers.firstname, borrowers.surname,
 278                 borrowers.phone, borrowers.email, borrowers.branchcode, biblio.biblionumber, biblio.title, items.barcode, reserves.waitingdate,
 279                 reserves.branchcode AS site, branches.branchname AS site_name,
 280                 TO_DAYS(NOW())-TO_DAYS(reserves.waitingdate) AS days_since_waiting
 281                 FROM borrowers JOIN reserves USING (borrowernumber)
 282                 JOIN items USING (itemnumber)
 283                 JOIN biblio ON (biblio.biblionumber = items.biblionumber)
 284                 JOIN branches ON (reserves.branchcode = branches.branchcode)
 285                 JOIN borrower_message_preferences USING (borrowernumber)
 286                 JOIN borrower_message_transport_preferences USING (borrower_message_preference_id)
 287                 JOIN message_attributes USING (message_attribute_id)
 288                 WHERE ( reserves.found = 'W' )
 289                 AND message_transport_type = 'phone'
 290                 AND message_name = 'Hold_Filled'
 291                 ";
 292     my $pickupdelay = C4::Context->preference("ReservesMaxPickUpDelay");
 293     my $sth         = $dbh->prepare($query);
 294     $sth->execute();
 295     my @results;
 296     while ( my $issue = $sth->fetchrow_hashref() ) {
 297         my $calendar = Koha::Calendar->new( branchcode => $issue->{'site'} );
 298
 299         my $waiting_date = dt_from_string( $issue->{waitingdate}, 'sql' );
 300         my $pickup_date = $waiting_date->clone->add( days => $pickupdelay );
 301         if ( $calendar->is_holiday($pickup_date) ) {
 302             $pickup_date = $calendar->next_open_day( $pickup_date );
 303         }
 304
 305         $issue->{'date_due'} = output_pref({dt => $pickup_date, dateformat => 'iso' });
 306         $issue->{'level'} = 1;    # only one level for Hold Waiting notifications
 307
 308         my $days_to_subtract = 0;
 309         if ( $calendar->is_holiday($waiting_date) ) {
 310             my $next_open_day = $calendar->next_open_day( $waiting_date );
 311             $days_to_subtract = $calendar->days_between($waiting_date, $next_open_day)->days;
 312         }
 313
 314         $issue->{'days_since_waiting'} = $issue->{'days_since_waiting'} - $days_to_subtract;
 315
 316         if ( ( grep $_ eq $issue->{'days_since_waiting'}, @holds_waiting_days_to_call )
 317             || !scalar(@holds_waiting_days_to_call) ) {
 318             push @results, $issue;
 319         }
 320     }
 321     return @results;
 322
 323 }