3 # Copyright (C) 2011 ByWater Solutions
5 # This file is part of Koha.
7 # Koha is free software; you can redistribute it and/or modify it under the
8 # terms of the GNU General Public License as published by the Free Software
9 # Foundation; either version 2 of the License, or (at your option) any later
12 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
13 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
14 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
16 # You should have received a copy of the GNU General Public License along
17 # with Koha; if not, write to the Free Software Foundation, Inc.,
18 # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
23 # find Koha's Perl modules
24 # test carefully before changing this
26 eval { require "$FindBin::Bin/../kohalib.pl" };
31 use Date::Calc qw(Add_Delta_Days);
41 pod2usage( -verbose => 2 );
45 die "TalkingTechItivaPhoneNotification system preference not activated... dying\n" unless (C4::Context->preference("TalkingTechItivaPhoneNotification"));
48 my $dbh = C4::Context->dbh;
54 my @holds_waiting_days_to_call;
59 # maps to convert I-tiva terms to Koha terms
60 my $type_module_map = { 'PREOVERDUE' => 'circulation',
61 'OVERDUE' => 'circulation',
62 'RESERVE' => 'reserves',
65 my $type_notice_map = { 'PREOVERDUE' => 'PREDUE_PHONE',
66 'OVERDUE' => 'OVERDUE_PHONE',
67 'RESERVE' => 'HOLD_PHONE',
71 'o|output:s' => \$outfile,
73 'lang:s' => \$language,
75 'w|waiting-hold-day:s' => \@holds_waiting_days_to_call,
76 'c|code|library-code:s' => \$library_code,
80 $language = uc($language);
83 pod2usage( -verbose => 1 ) if $help;
85 # output log or STDOUT
86 if (defined $outfile) {
87 open (OUT, ">$outfile") || die ("Cannot open output file");
89 print "No output file defined; printing to STDOUT\n" if (defined $verbose);
90 open(OUT, ">&STDOUT") || die ("Couldn't duplicate STDOUT: $!");
93 my $format = 'V'; # format for phone notifications
95 foreach my $type (@types) {
96 $type = uc($type); #just in case lower or mixed-case was supplied
97 my $module = $type_module_map->{$type}; #since the module is required to get the letter
98 my $code = $type_notice_map->{$type}; #to get the Koha name of the notice
101 if ($type eq 'OVERDUE') {
102 @loop = GetOverdueIssues();
103 } elsif ($type eq 'PREOVERDUE') {
104 @loop = GetPredueIssues();
105 } elsif ($type eq 'RESERVE') {
106 @loop = GetWaitingHolds();
108 print "Unknown or unsupported message type $type; skipping...\n" if (defined $verbose);
112 foreach my $issues (@loop) {
113 my $date = C4::Dates->new($issues->{'date_due'}, 'iso');
114 my $due_date = $date->output('metric');
116 # gets the placeholder message, and enqueues the letter
117 my $letter = getletter($module, $code);
118 die "No letter found for type $type!... dying\n" unless $letter;
119 # covers basic variable parsing in letter
120 $letter = C4::Letters::parseletter($letter, 'borrowers', $issues->{'borrowernumber'});
121 $letter = C4::Letters::parseletter($letter, 'biblio', $issues->{'biblionumber'});
122 $letter = C4::Letters::parseletter($letter, 'biblioitems', $issues->{'biblionumber'});
126 $message_id = C4::Letters::EnqueueLetter( { letter => $letter,
127 borrowernumber => $issues->{'borrowernumber'},
128 message_transport_type => 'phone',
132 print OUT "\"$format\",\"$language\",\"$type\",\"$issues->{level}\",\"$issues->{cardnumber}\",\"$issues->{patron_title}\",\"$issues->{firstname}\",";
133 print OUT "\"$issues->{surname}\",\"$issues->{phone}\",\"$issues->{email}\",\"$library_code\",";
134 print OUT "\"$issues->{site}\",\"$issues->{site_name}\",\"$issues->{barcode}\",\"$due_date\",\"$issues->{title}\",\"$message_id\"\n";
141 TalkingTech_itiva_outbound.pl
145 TalkingTech_itiva_outbound.pl
146 TalkingTech_itiva_outbound.pl --type=OVERDUE -w 0 -w 2 -w 6 --output=/tmp/talkingtech/outbound.csv
147 TalkingTech_itiva_outbound.pl --type=RESERVE --type=PREOVERDUE --lang=FR
150 Script to generate Spec C outbound notifications file for Talking Tech i-tiva
151 phone notification system.
153 =item B<--help> B<-h>
159 Provide verbose log information.
161 =item B<--output> B<-o>
163 Destination for outbound notifications file (CSV format). If no value is specified,
164 output is dumped to screen.
168 Sets the language for all outbound messages. Currently supported values are EN, FR and ES.
169 If no value is specified, EN will be used by default.
173 REQUIRED. Sets which messaging types are to be used. Can be given multiple times, to
174 specify multiple types in a single output file. Currently supported values are RESERVE, PREOVERDUE
175 and OVERDUE. If no value is given, this script will not produce any outbound notifications.
177 =item B<--waiting-hold-day> B<-w>
179 OPTIONAL for --type=RESERVE. Sets the days after a hold has been set to waiting on which to call. Use
180 switch as many times as desired. For example, passing "-w 0 -w 2 -w 6" will cause calls to be placed
181 on the day the hold was set to waiting, 2 days after the waiting date, and 6 days after. See example above.
182 If this switch is not used with --type=RESERVE, calls will be placed every day until the waiting reserve
183 is picked up or canceled.
185 =item B<--library-code> B<--code> B<-c>
188 The code of the source library of the message.
189 The library code is used to group notices together for
190 consortium purposes and apply library specific settings, such as
191 prompts, to those notices.
192 This field can be blank if all messages are from a single library.
196 sub GetOverdueIssues {
197 my $query = "SELECT borrowers.borrowernumber, borrowers.cardnumber, borrowers.title as patron_title, borrowers.firstname, borrowers.surname,
198 borrowers.phone, borrowers.email, borrowers.branchcode, biblio.biblionumber, biblio.title, items.barcode, issues.date_due,
199 max(overduerules.branchcode) as rulebranch, TO_DAYS(NOW())-TO_DAYS(date_due) as daysoverdue, delay1, delay2, delay3,
200 issues.branchcode as site, branches.branchname as site_name
201 FROM borrowers JOIN issues USING (borrowernumber)
202 JOIN items USING (itemnumber)
203 JOIN biblio USING (biblionumber)
204 JOIN branches ON (issues.branchcode = branches.branchcode)
205 JOIN overduerules USING (categorycode)
206 WHERE ( overduerules.branchcode = borrowers.branchcode or overduerules.branchcode = '')
207 AND ( (TO_DAYS(NOW())-TO_DAYS(date_due) ) = delay1
208 OR (TO_DAYS(NOW())-TO_DAYS(date_due) ) = delay2
209 OR (TO_DAYS(NOW())-TO_DAYS(date_due) ) = delay3 )
210 GROUP BY items.itemnumber
212 my $sth = $dbh->prepare($query);
215 while( my $issue = $sth->fetchrow_hashref() ) {
216 if ( $issue->{'daysoverdue'} == $issue->{'delay1'} ) {
217 $issue->{'level'} = 1;
218 } elsif ( $issue->{'daysoverdue'} == $issue->{'delay2'} ) {
219 $issue->{'level'} = 2;
220 } elsif ( $issue->{'daysoverdue'} == $issue->{'delay3'} ) {
221 $issue->{'level'} = 3;
223 # this shouldn't ever happen, based our SQL criteria
225 push @results, $issue;
230 sub GetPredueIssues {
231 my $query = "SELECT borrowers.borrowernumber, borrowers.cardnumber, borrowers.title as patron_title, borrowers.firstname, borrowers.surname,
232 borrowers.phone, borrowers.email, borrowers.branchcode, biblio.biblionumber, biblio.title, items.barcode, issues.date_due,
233 issues.branchcode as site, branches.branchname as site_name
234 FROM borrowers JOIN issues USING (borrowernumber)
235 JOIN items USING (itemnumber)
236 JOIN biblio USING (biblionumber)
237 JOIN branches ON (issues.branchcode = branches.branchcode)
238 JOIN borrower_message_preferences USING (borrowernumber)
239 JOIN borrower_message_transport_preferences USING (borrower_message_preference_id)
240 JOIN message_attributes USING (message_attribute_id)
241 WHERE ( TO_DAYS( date_due ) - TO_DAYS( NOW() ) ) = days_in_advance
242 AND message_transport_type = 'phone'
243 AND message_name = 'Advance_Notice'
245 my $sth = $dbh->prepare($query);
248 while( my $issue = $sth->fetchrow_hashref() ) {
249 $issue->{'level'} = 1; # only one level for Predue notifications
250 push @results, $issue;
255 sub GetWaitingHolds {
256 my $query = "SELECT borrowers.borrowernumber, borrowers.cardnumber, borrowers.title as patron_title, borrowers.firstname, borrowers.surname,
257 borrowers.phone, borrowers.email, borrowers.branchcode, biblio.biblionumber, biblio.title, items.barcode, reserves.waitingdate,
258 reserves.branchcode AS site, branches.branchname AS site_name,
259 TO_DAYS(NOW())-TO_DAYS(reserves.waitingdate) AS days_since_waiting
260 FROM borrowers JOIN reserves USING (borrowernumber)
261 JOIN items USING (itemnumber)
262 JOIN biblio ON (biblio.biblionumber = items.biblionumber)
263 JOIN branches ON (reserves.branchcode = branches.branchcode)
264 JOIN borrower_message_preferences USING (borrowernumber)
265 JOIN borrower_message_transport_preferences USING (borrower_message_preference_id)
266 JOIN message_attributes USING (message_attribute_id)
267 WHERE ( reserves.found = 'W' )
268 AND message_transport_type = 'phone'
269 AND message_name = 'Hold_Filled'
271 my $pickupdelay = C4::Context->preference("ReservesMaxPickUpDelay");
272 my $sth = $dbh->prepare($query);
275 while( my $issue = $sth->fetchrow_hashref() ) {
276 my @waitingdate = split( /-/, $issue->{'waitingdate'} );
277 my @date_due = Add_Delta_Days( $waitingdate[0], $waitingdate[1], $waitingdate[2], $pickupdelay );
278 $issue->{'date_due'} = sprintf( "%04d-%02d-%02d", $date_due[0], $date_due[1], $date_due[2] );
279 $issue->{'level'} = 1; # only one level for Hold Waiting notifications
281 my $days_to_subtract = 0;
282 my $calendar = C4::Calendar->new( branchcode => $issue->{'site'} );
283 while( $calendar->isHoliday( reverse( Add_Delta_Days( $waitingdate[0], $waitingdate[1], $waitingdate[2], $days_to_subtract ) ) ) ) {
286 $issue->{'days_since_waiting'} = $issue->{'days_since_waiting'} - $days_to_subtract;
288 if ( ( grep $_ eq $issue->{'days_since_waiting'}, @holds_waiting_days_to_call ) || !scalar( @holds_waiting_days_to_call ) ) {
289 push @results, $issue;