Bug 18001 - Unit Test
[koha.git] / C4 / Overdues.pm
blobc26b5f067838001714d4457e07283508543c6bfe
1 package C4::Overdues;
4 # Copyright 2000-2002 Katipo Communications
5 # copyright 2010 BibLibre
7 # This file is part of Koha.
9 # Koha is free software; you can redistribute it and/or modify it
10 # under the terms of the GNU General Public License as published by
11 # the Free Software Foundation; either version 3 of the License, or
12 # (at your option) any later version.
14 # Koha is distributed in the hope that it will be useful, but
15 # WITHOUT ANY WARRANTY; without even the implied warranty of
16 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
17 # GNU General Public License for more details.
19 # You should have received a copy of the GNU General Public License
20 # along with Koha; if not, see <http://www.gnu.org/licenses>.
22 use strict;
23 #use warnings; FIXME - Bug 2505
24 use Date::Calc qw/Today Date_to_Days/;
25 use Date::Manip qw/UnixDate/;
26 use List::MoreUtils qw( uniq );
27 use POSIX qw( floor ceil );
28 use Locale::Currency::Format 1.28;
29 use Carp;
31 use C4::Circulation;
32 use C4::Context;
33 use C4::Accounts;
34 use C4::Log; # logaction
35 use C4::Debug;
36 use Koha::DateUtils;
37 use Koha::Account::Line;
38 use Koha::Account::Lines;
39 use Koha::IssuingRules;
40 use Koha::Libraries;
42 use vars qw(@ISA @EXPORT);
44 BEGIN {
45 require Exporter;
46 @ISA = qw(Exporter);
48 # subs to rename (and maybe merge some...)
49 push @EXPORT, qw(
50 &CalcFine
51 &Getoverdues
52 &checkoverdues
53 &NumberNotifyId
54 &AmountNotify
55 &UpdateFine
56 &GetFine
57 &get_chargeable_units
58 &CheckItemNotify
59 &GetOverduesForBranch
60 &RemoveNotifyLine
61 &AddNotifyLine
62 &GetOverdueMessageTransportTypes
63 &parse_overdues_letter
66 # subs to remove
67 push @EXPORT, qw(
68 &BorType
71 # check that an equivalent don't exist already before moving
73 # subs to move to Circulation.pm
74 push @EXPORT, qw(
75 &GetIssuesIteminfo
78 # subs to move to Biblio.pm
79 push @EXPORT, qw(
80 &GetItems
84 =head1 NAME
86 C4::Circulation::Fines - Koha module dealing with fines
88 =head1 SYNOPSIS
90 use C4::Overdues;
92 =head1 DESCRIPTION
94 This module contains several functions for dealing with fines for
95 overdue items. It is primarily used by the 'misc/fines2.pl' script.
97 =head1 FUNCTIONS
99 =head2 Getoverdues
101 $overdues = Getoverdues( { minimumdays => 1, maximumdays => 30 } );
103 Returns the list of all overdue books, with their itemtype.
105 C<$overdues> is a reference-to-array. Each element is a
106 reference-to-hash whose keys are the fields of the issues table in the
107 Koha database.
109 =cut
112 sub Getoverdues {
113 my $params = shift;
114 my $dbh = C4::Context->dbh;
115 my $statement;
116 if ( C4::Context->preference('item-level_itypes') ) {
117 $statement = "
118 SELECT issues.*, items.itype as itemtype, items.homebranch, items.barcode, items.itemlost, items.replacementprice
119 FROM issues
120 LEFT JOIN items USING (itemnumber)
121 WHERE date_due < NOW()
123 } else {
124 $statement = "
125 SELECT issues.*, biblioitems.itemtype, items.itype, items.homebranch, items.barcode, items.itemlost, replacementprice
126 FROM issues
127 LEFT JOIN items USING (itemnumber)
128 LEFT JOIN biblioitems USING (biblioitemnumber)
129 WHERE date_due < NOW()
133 my @bind_parameters;
134 if ( exists $params->{'minimumdays'} and exists $params->{'maximumdays'} ) {
135 $statement .= ' AND TO_DAYS( NOW() )-TO_DAYS( date_due ) BETWEEN ? and ? ';
136 push @bind_parameters, $params->{'minimumdays'}, $params->{'maximumdays'};
137 } elsif ( exists $params->{'minimumdays'} ) {
138 $statement .= ' AND ( TO_DAYS( NOW() )-TO_DAYS( date_due ) ) > ? ';
139 push @bind_parameters, $params->{'minimumdays'};
140 } elsif ( exists $params->{'maximumdays'} ) {
141 $statement .= ' AND ( TO_DAYS( NOW() )-TO_DAYS( date_due ) ) < ? ';
142 push @bind_parameters, $params->{'maximumdays'};
144 $statement .= 'ORDER BY borrowernumber';
145 my $sth = $dbh->prepare( $statement );
146 $sth->execute( @bind_parameters );
147 return $sth->fetchall_arrayref({});
151 =head2 checkoverdues
153 ($count, $overdueitems) = checkoverdues($borrowernumber);
155 Returns a count and a list of overdueitems for a given borrowernumber
157 =cut
159 sub checkoverdues {
160 my $borrowernumber = shift or return;
161 my $sth = C4::Context->dbh->prepare(
162 "SELECT biblio.*, items.*, issues.*,
163 biblioitems.volume,
164 biblioitems.number,
165 biblioitems.itemtype,
166 biblioitems.isbn,
167 biblioitems.issn,
168 biblioitems.publicationyear,
169 biblioitems.publishercode,
170 biblioitems.volumedate,
171 biblioitems.volumedesc,
172 biblioitems.collectiontitle,
173 biblioitems.collectionissn,
174 biblioitems.collectionvolume,
175 biblioitems.editionstatement,
176 biblioitems.editionresponsibility,
177 biblioitems.illus,
178 biblioitems.pages,
179 biblioitems.notes,
180 biblioitems.size,
181 biblioitems.place,
182 biblioitems.lccn,
183 biblioitems.url,
184 biblioitems.cn_source,
185 biblioitems.cn_class,
186 biblioitems.cn_item,
187 biblioitems.cn_suffix,
188 biblioitems.cn_sort,
189 biblioitems.totalissues
190 FROM issues
191 LEFT JOIN items ON issues.itemnumber = items.itemnumber
192 LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
193 LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
194 WHERE issues.borrowernumber = ?
195 AND issues.date_due < NOW()"
197 $sth->execute($borrowernumber);
198 my $results = $sth->fetchall_arrayref({});
199 return ( scalar(@$results), $results); # returning the count and the results is silly
202 =head2 CalcFine
204 ($amount, $chargename, $units_minus_grace, $chargeable_units) = &CalcFine($item,
205 $categorycode, $branch,
206 $start_dt, $end_dt );
208 Calculates the fine for a book.
210 The issuingrules table in the Koha database is a fine matrix, listing
211 the penalties for each type of patron for each type of item and each branch (e.g., the
212 standard fine for books might be $0.50, but $1.50 for DVDs, or staff
213 members might get a longer grace period between the first and second
214 reminders that a book is overdue).
217 C<$item> is an item object (hashref).
219 C<$categorycode> is the category code (string) of the patron who currently has
220 the book.
222 C<$branchcode> is the library (string) whose issuingrules govern this transaction.
224 C<$start_date> & C<$end_date> are DateTime objects
225 defining the date range over which to determine the fine.
227 Fines scripts should just supply the date range over which to calculate the fine.
229 C<&CalcFine> returns four values:
231 C<$amount> is the fine owed by the patron (see above).
233 C<$chargename> is the chargename field from the applicable record in
234 the categoryitem table, whatever that is.
236 C<$units_minus_grace> is the number of chargeable units minus the grace period
238 C<$chargeable_units> is the number of chargeable units (days between start and end dates, Calendar adjusted where needed,
239 minus any applicable grace period, or hours)
241 FIXME: previously attempted to return C<$message> as a text message, either "First Notice", "Second Notice",
242 or "Final Notice". But CalcFine never defined any value.
244 =cut
246 sub CalcFine {
247 my ( $item, $bortype, $branchcode, $due_dt, $end_date ) = @_;
248 my $start_date = $due_dt->clone();
249 # get issuingrules (fines part will be used)
250 my $itemtype = $item->{itemtype} || $item->{itype};
251 my $issuing_rule = Koha::IssuingRules->get_effective_issuing_rule({ categorycode => $bortype, itemtype => $itemtype, branchcode => $branchcode });
253 return unless $issuing_rule; # If not rule exist, there is no fine
255 my $fine_unit = $issuing_rule->lengthunit || 'days';
257 my $chargeable_units = get_chargeable_units($fine_unit, $start_date, $end_date, $branchcode);
258 my $units_minus_grace = $chargeable_units - $issuing_rule->firstremind;
259 my $amount = 0;
260 if ( $issuing_rule->chargeperiod && ( $units_minus_grace > 0 ) ) {
261 my $units = C4::Context->preference('FinesIncludeGracePeriod') ? $chargeable_units : $units_minus_grace;
262 my $charge_periods = $units / $issuing_rule->chargeperiod;
263 # If chargeperiod_charge_at = 1, we charge a fine at the start of each charge period
264 # if chargeperiod_charge_at = 0, we charge at the end of each charge period
265 $charge_periods = $issuing_rule->chargeperiod_charge_at == 1 ? ceil($charge_periods) : floor($charge_periods);
266 $amount = $charge_periods * $issuing_rule->fine;
267 } # else { # a zero (or null) chargeperiod or negative units_minus_grace value means no charge. }
269 $amount = $issuing_rule->overduefinescap if $issuing_rule->overduefinescap && $amount > $issuing_rule->overduefinescap;
270 $amount = $item->{replacementprice} if ( $issuing_rule->cap_fine_to_replacement_price && $item->{replacementprice} && $amount > $item->{replacementprice} );
271 $debug and warn sprintf("CalcFine returning (%s, %s, %s, %s)", $amount, $issuing_rule->chargename, $units_minus_grace, $chargeable_units);
272 return ($amount, $issuing_rule->chargename, $units_minus_grace, $chargeable_units);
273 # FIXME: chargename is NEVER populated anywhere.
277 =head2 get_chargeable_units
279 get_chargeable_units($unit, $start_date_ $end_date, $branchcode);
281 return integer value of units between C<$start_date> and C<$end_date>, factoring in holidays for C<$branchcode>.
283 C<$unit> is 'days' or 'hours' (default is 'days').
285 C<$start_date> and C<$end_date> are the two DateTimes to get the number of units between.
287 C<$branchcode> is the branch whose calendar to use for finding holidays.
289 =cut
291 sub get_chargeable_units {
292 my ($unit, $date_due, $date_returned, $branchcode) = @_;
294 # If the due date is later than the return date
295 return 0 unless ( $date_returned > $date_due );
297 my $charge_units = 0;
298 my $charge_duration;
299 if ($unit eq 'hours') {
300 if(C4::Context->preference('finesCalendar') eq 'noFinesWhenClosed') {
301 my $calendar = Koha::Calendar->new( branchcode => $branchcode );
302 $charge_duration = $calendar->hours_between( $date_due, $date_returned );
303 } else {
304 $charge_duration = $date_returned->delta_ms( $date_due );
306 if($charge_duration->in_units('hours') == 0 && $charge_duration->in_units('seconds') > 0){
307 return 1;
309 return $charge_duration->in_units('hours');
311 else { # days
312 if(C4::Context->preference('finesCalendar') eq 'noFinesWhenClosed') {
313 my $calendar = Koha::Calendar->new( branchcode => $branchcode );
314 $charge_duration = $calendar->days_between( $date_due, $date_returned );
315 } else {
316 $charge_duration = $date_returned->delta_days( $date_due );
318 return $charge_duration->in_units('days');
323 =head2 GetSpecialHolidays
325 &GetSpecialHolidays($date_dues,$itemnumber);
327 return number of special days between date of the day and date due
329 C<$date_dues> is the envisaged date of book return.
331 C<$itemnumber> is the book's item number.
333 =cut
335 sub GetSpecialHolidays {
336 my ( $date_dues, $itemnumber ) = @_;
338 # calcul the today date
339 my $today = join "-", &Today();
341 # return the holdingbranch
342 my $iteminfo = GetIssuesIteminfo($itemnumber);
344 # use sql request to find all date between date_due and today
345 my $dbh = C4::Context->dbh;
346 my $query =
347 qq|SELECT DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d') as date
348 FROM `special_holidays`
349 WHERE DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d') >= ?
350 AND DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d') <= ?
351 AND branchcode=?
353 my @result = GetWdayFromItemnumber($itemnumber);
354 my @result_date;
355 my $wday;
356 my $dateinsec;
357 my $sth = $dbh->prepare($query);
358 $sth->execute( $date_dues, $today, $iteminfo->{'branchcode'} )
359 ; # FIXME: just use NOW() in SQL instead of passing in $today
361 while ( my $special_date = $sth->fetchrow_hashref ) {
362 push( @result_date, $special_date );
365 my $specialdaycount = scalar(@result_date);
367 for ( my $i = 0 ; $i < scalar(@result_date) ; $i++ ) {
368 $dateinsec = UnixDate( $result_date[$i]->{'date'}, "%o" );
369 ( undef, undef, undef, undef, undef, undef, $wday, undef, undef ) =
370 localtime($dateinsec);
371 for ( my $j = 0 ; $j < scalar(@result) ; $j++ ) {
372 if ( $wday == ( $result[$j]->{'weekday'} ) ) {
373 $specialdaycount--;
378 return $specialdaycount;
381 =head2 GetRepeatableHolidays
383 &GetRepeatableHolidays($date_dues, $itemnumber, $difference,);
385 return number of day closed between date of the day and date due
387 C<$date_dues> is the envisaged date of book return.
389 C<$itemnumber> is item number.
391 C<$difference> numbers of between day date of the day and date due
393 =cut
395 sub GetRepeatableHolidays {
396 my ( $date_dues, $itemnumber, $difference ) = @_;
397 my $dateinsec = UnixDate( $date_dues, "%o" );
398 my ( $sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $isdst ) =
399 localtime($dateinsec);
400 my @result = GetWdayFromItemnumber($itemnumber);
401 my @dayclosedcount;
402 my $j;
404 for ( my $i = 0 ; $i < scalar(@result) ; $i++ ) {
405 my $k = $wday;
407 for ( $j = 0 ; $j < $difference ; $j++ ) {
408 if ( $result[$i]->{'weekday'} == $k ) {
409 push( @dayclosedcount, $k );
411 $k++;
412 ( $k = 0 ) if ( $k eq 7 );
415 return scalar(@dayclosedcount);
419 =head2 GetWayFromItemnumber
421 &Getwdayfromitemnumber($itemnumber);
423 return the different week day from repeatable_holidays table
425 C<$itemnumber> is item number.
427 =cut
429 sub GetWdayFromItemnumber {
430 my ($itemnumber) = @_;
431 my $iteminfo = GetIssuesIteminfo($itemnumber);
432 my @result;
433 my $query = qq|SELECT weekday
434 FROM repeatable_holidays
435 WHERE branchcode=?
437 my $sth = C4::Context->dbh->prepare($query);
439 $sth->execute( $iteminfo->{'branchcode'} );
440 while ( my $weekday = $sth->fetchrow_hashref ) {
441 push( @result, $weekday );
443 return @result;
447 =head2 GetIssuesIteminfo
449 &GetIssuesIteminfo($itemnumber);
451 return all data from issues about item
453 C<$itemnumber> is item number.
455 =cut
457 sub GetIssuesIteminfo {
458 my ($itemnumber) = @_;
459 my $dbh = C4::Context->dbh;
460 my $query = qq|SELECT *
461 FROM issues
462 WHERE itemnumber=?
464 my $sth = $dbh->prepare($query);
465 $sth->execute($itemnumber);
466 my ($issuesinfo) = $sth->fetchrow_hashref;
467 return $issuesinfo;
471 =head2 UpdateFine
473 &UpdateFine({ issue_id => $issue_id, itemnumber => $itemnumber, borrwernumber => $borrowernumber, amount => $amount, type => $type, $due => $date_due });
475 (Note: the following is mostly conjecture and guesswork.)
477 Updates the fine owed on an overdue book.
479 C<$itemnumber> is the book's item number.
481 C<$borrowernumber> is the borrower number of the patron who currently
482 has the book on loan.
484 C<$amount> is the current amount owed by the patron.
486 C<$type> will be used in the description of the fine.
488 C<$due> is the due date formatted to the currently specified date format
490 C<&UpdateFine> looks up the amount currently owed on the given item
491 and sets it to C<$amount>, creating, if necessary, a new entry in the
492 accountlines table of the Koha database.
494 =cut
497 # Question: Why should the caller have to
498 # specify both the item number and the borrower number? A book can't
499 # be on loan to two different people, so the item number should be
500 # sufficient.
502 # Possible Answer: You might update a fine for a damaged item, *after* it is returned.
504 sub UpdateFine {
505 my ($params) = @_;
507 my $issue_id = $params->{issue_id};
508 my $itemnum = $params->{itemnumber};
509 my $borrowernumber = $params->{borrowernumber};
510 my $amount = $params->{amount};
511 my $type = $params->{type};
512 my $due = $params->{due};
514 $debug and warn "UpdateFine({ itemnumber => $itemnum, borrowernumber => $borrowernumber, type => $type, due => $due, issue_id => $issue_id})";
516 unless ( $issue_id ) {
517 carp("No issue_id passed in!");
518 return;
521 my $dbh = C4::Context->dbh;
522 # FIXME - What exactly is this query supposed to do? It looks up an
523 # entry in accountlines that matches the given item and borrower
524 # numbers, where the description contains $due, and where the
525 # account type has one of several values, but what does this _mean_?
526 # Does it look up existing fines for this item?
527 # FIXME - What are these various account types? ("FU", "O", "F", "M")
528 # "L" is LOST item
529 # "A" is Account Management Fee
530 # "N" is New Card
531 # "M" is Sundry
532 # "O" is Overdue ??
533 # "F" is Fine ??
534 # "FU" is Fine UPDATE??
535 # "Pay" is Payment
536 # "REF" is Cash Refund
537 my $sth = $dbh->prepare(
538 "SELECT * FROM accountlines
539 WHERE borrowernumber=? AND
540 (( accounttype IN ('O','F','M') AND amountoutstanding<>0 ) OR
541 accounttype = 'FU' )"
543 $sth->execute( $borrowernumber );
544 my $data;
545 my $total_amount_other = 0.00;
546 my $due_qr = qr/$due/;
547 # Cycle through the fines and
548 # - find line that relates to the requested $itemnum
549 # - accumulate fines for other items
550 # so we can update $itemnum fine taking in account fine caps
551 while (my $rec = $sth->fetchrow_hashref) {
552 if ( $rec->{issue_id} == $issue_id && $rec->{accounttype} eq 'FU' ) {
553 if ($data) {
554 warn "Not a unique accountlines record for issue_id $issue_id";
555 #FIXME Should we still count this one in total_amount ??
557 else {
558 $data = $rec;
559 next;
562 $total_amount_other += $rec->{'amountoutstanding'};
565 if (my $maxfine = C4::Context->preference('MaxFine')) {
566 if ($total_amount_other + $amount > $maxfine) {
567 my $new_amount = $maxfine - $total_amount_other;
568 return if $new_amount <= 0.00;
569 warn "Reducing fine for item $itemnum borrower $borrowernumber from $amount to $new_amount - MaxFine reached";
570 $amount = $new_amount;
574 if ( $data ) {
575 # we're updating an existing fine. Only modify if amount changed
576 # Note that in the current implementation, you cannot pay against an accruing fine
577 # (i.e. , of accounttype 'FU'). Doing so will break accrual.
578 if ( $data->{'amount'} != $amount ) {
579 my $accountline = Koha::Account::Lines->find( $data->{accountlines_id} );
580 my $diff = $amount - $data->{'amount'};
582 #3341: diff could be positive or negative!
583 my $out = $data->{'amountoutstanding'} + $diff;
585 $accountline->set(
587 date => dt_from_string(),
588 amount => $amount,
589 amountoutstanding => $out,
590 lastincrement => $diff,
591 accounttype => 'FU',
593 )->store();
595 } else {
596 if ( $amount ) { # Don't add new fines with an amount of 0
597 my $sth4 = $dbh->prepare(
598 "SELECT title FROM biblio LEFT JOIN items ON biblio.biblionumber=items.biblionumber WHERE items.itemnumber=?"
600 $sth4->execute($itemnum);
601 my $title = $sth4->fetchrow;
603 my $nextaccntno = C4::Accounts::getnextacctno($borrowernumber);
605 my $desc = ( $type ? "$type " : '' ) . "$title $due"; # FIXEDME, avoid whitespace prefix on empty $type
607 my $accountline = Koha::Account::Line->new(
609 borrowernumber => $borrowernumber,
610 itemnumber => $itemnum,
611 date => dt_from_string(),
612 amount => $amount,
613 description => $desc,
614 accounttype => 'FU',
615 amountoutstanding => $amount,
616 lastincrement => $amount,
617 accountno => $nextaccntno,
618 issue_id => $issue_id,
620 )->store();
623 # logging action
624 &logaction(
625 "FINES",
626 $type,
627 $borrowernumber,
628 "due=".$due." amount=".$amount." itemnumber=".$itemnum
629 ) if C4::Context->preference("FinesLog");
632 =head2 BorType
634 $borrower = &BorType($borrowernumber);
636 Looks up a patron by borrower number.
638 C<$borrower> is a reference-to-hash whose keys are all of the fields
639 from the borrowers and categories tables of the Koha database. Thus,
640 C<$borrower> contains all information about both the borrower and
641 category he or she belongs to.
643 =cut
645 sub BorType {
646 my ($borrowernumber) = @_;
647 my $dbh = C4::Context->dbh;
648 my $sth = $dbh->prepare(
649 "SELECT * from borrowers
650 LEFT JOIN categories ON borrowers.categorycode=categories.categorycode
651 WHERE borrowernumber=?"
653 $sth->execute($borrowernumber);
654 return $sth->fetchrow_hashref;
657 =head2 GetFine
659 $data->{'sum(amountoutstanding)'} = &GetFine($itemnum,$borrowernumber);
661 return the total of fine
663 C<$itemnum> is item number
665 C<$borrowernumber> is the borrowernumber
667 =cut
669 sub GetFine {
670 my ( $itemnum, $borrowernumber ) = @_;
671 my $dbh = C4::Context->dbh();
672 my $query = q|SELECT sum(amountoutstanding) as fineamount FROM accountlines
673 where accounttype like 'F%'
674 AND amountoutstanding > 0 AND borrowernumber=?|;
675 my @query_param;
676 push @query_param, $borrowernumber;
677 if (defined $itemnum )
679 $query .= " AND itemnumber=?";
680 push @query_param, $itemnum;
682 my $sth = $dbh->prepare($query);
683 $sth->execute( @query_param );
684 my $fine = $sth->fetchrow_hashref();
685 if ($fine->{fineamount}) {
686 return $fine->{fineamount};
688 return 0;
691 =head2 NumberNotifyId
693 (@notify) = &NumberNotifyId($borrowernumber);
695 Returns amount for all file per borrowers
696 C<@notify> array contains all file per borrowers
698 C<$notify_id> contains the file number for the borrower number nad item number
700 =cut
702 sub NumberNotifyId{
703 my ($borrowernumber)=@_;
704 my $dbh = C4::Context->dbh;
705 my $query=qq| SELECT distinct(notify_id)
706 FROM accountlines
707 WHERE borrowernumber=?|;
708 my @notify;
709 my $sth = $dbh->prepare($query);
710 $sth->execute($borrowernumber);
711 while ( my ($numberofnotify) = $sth->fetchrow ) {
712 push( @notify, $numberofnotify );
714 return (@notify);
717 =head2 AmountNotify
719 ($totalnotify) = &AmountNotify($notifyid);
721 Returns amount for all file per borrowers
722 C<$notifyid> is the file number
724 C<$totalnotify> contains amount of a file
726 C<$notify_id> contains the file number for the borrower number and item number
728 =cut
730 sub AmountNotify{
731 my ($notifyid,$borrowernumber)=@_;
732 my $dbh = C4::Context->dbh;
733 my $query=qq| SELECT sum(amountoutstanding)
734 FROM accountlines
735 WHERE notify_id=? AND borrowernumber = ?|;
736 my $sth=$dbh->prepare($query);
737 $sth->execute($notifyid,$borrowernumber);
738 my $totalnotify=$sth->fetchrow;
739 $sth->finish;
740 return ($totalnotify);
743 =head2 GetItems
745 ($items) = &GetItems($itemnumber);
747 Returns the list of all delays from overduerules.
749 C<$items> is a reference-to-hash whose keys are all of the fields
750 from the items tables of the Koha database. Thus,
752 C<$itemnumber> contains the borrower categorycode
754 =cut
756 # FIXME: This is a bad function to have here.
757 # Shouldn't it be in C4::Items?
758 # Shouldn't it be called GetItem since you only get 1 row?
759 # Shouldn't it be called GetItem since you give it only 1 itemnumber?
761 sub GetItems {
762 my $itemnumber = shift or return;
763 my $query = qq|SELECT *
764 FROM items
765 WHERE itemnumber=?|;
766 my $sth = C4::Context->dbh->prepare($query);
767 $sth->execute($itemnumber);
768 my ($items) = $sth->fetchrow_hashref;
769 return ($items);
772 =head2 GetBranchcodesWithOverdueRules
774 my @branchcodes = C4::Overdues::GetBranchcodesWithOverdueRules()
776 returns a list of branch codes for branches with overdue rules defined.
778 =cut
780 sub GetBranchcodesWithOverdueRules {
781 my $dbh = C4::Context->dbh;
782 my $branchcodes = $dbh->selectcol_arrayref(q|
783 SELECT DISTINCT(branchcode)
784 FROM overduerules
785 WHERE delay1 IS NOT NULL
786 ORDER BY branchcode
788 if ( $branchcodes->[0] eq '' ) {
789 # If a default rule exists, all branches should be returned
790 return map { $_->branchcode } Koha::Libraries->search({}, { order_by => 'branchname' });
792 return @$branchcodes;
795 =head2 CheckItemNotify
797 Sql request to check if the document has alreday been notified
798 this function is not exported, only used with GetOverduesForBranch
800 =cut
802 sub CheckItemNotify {
803 my ($notify_id,$notify_level,$itemnumber) = @_;
804 my $dbh = C4::Context->dbh;
805 my $sth = $dbh->prepare("
806 SELECT COUNT(*)
807 FROM notifys
808 WHERE notify_id = ?
809 AND notify_level = ?
810 AND itemnumber = ? ");
811 $sth->execute($notify_id,$notify_level,$itemnumber);
812 my $notified = $sth->fetchrow;
813 return ($notified);
816 =head2 GetOverduesForBranch
818 Sql request for display all information for branchoverdues.pl
819 2 possibilities : with or without location .
820 display is filtered by branch
822 FIXME: This function should be renamed.
824 =cut
826 sub GetOverduesForBranch {
827 my ( $branch, $location) = @_;
828 my $itype_link = (C4::Context->preference('item-level_itypes')) ? " items.itype " : " biblioitems.itemtype ";
829 my $dbh = C4::Context->dbh;
830 my $select = "
831 SELECT
832 borrowers.cardnumber,
833 borrowers.borrowernumber,
834 borrowers.surname,
835 borrowers.firstname,
836 borrowers.phone,
837 borrowers.email,
838 biblio.title,
839 biblio.author,
840 biblio.biblionumber,
841 issues.date_due,
842 issues.returndate,
843 issues.branchcode,
844 branches.branchname,
845 items.barcode,
846 items.homebranch,
847 items.itemcallnumber,
848 items.location,
849 items.itemnumber,
850 itemtypes.description,
851 accountlines.notify_id,
852 accountlines.notify_level,
853 accountlines.amountoutstanding
854 FROM accountlines
855 LEFT JOIN issues ON issues.itemnumber = accountlines.itemnumber
856 AND issues.borrowernumber = accountlines.borrowernumber
857 LEFT JOIN borrowers ON borrowers.borrowernumber = accountlines.borrowernumber
858 LEFT JOIN items ON items.itemnumber = issues.itemnumber
859 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
860 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber
861 LEFT JOIN itemtypes ON itemtypes.itemtype = $itype_link
862 LEFT JOIN branches ON branches.branchcode = issues.branchcode
863 WHERE (accountlines.amountoutstanding != '0.000000')
864 AND (accountlines.accounttype = 'FU' )
865 AND (issues.branchcode = ? )
866 AND (issues.date_due < NOW())
868 my @getoverdues;
869 my $i = 0;
870 my $sth;
871 if ($location) {
872 $sth = $dbh->prepare("$select AND items.location = ? ORDER BY borrowers.surname, borrowers.firstname");
873 $sth->execute($branch, $location);
874 } else {
875 $sth = $dbh->prepare("$select ORDER BY borrowers.surname, borrowers.firstname");
876 $sth->execute($branch);
878 while ( my $data = $sth->fetchrow_hashref ) {
879 #check if the document has already been notified
880 my $countnotify = CheckItemNotify($data->{'notify_id'}, $data->{'notify_level'}, $data->{'itemnumber'});
881 if ($countnotify eq '0') {
882 $getoverdues[$i] = $data;
883 $i++;
886 return (@getoverdues);
890 =head2 AddNotifyLine
892 &AddNotifyLine($borrowernumber, $itemnumber, $overduelevel, $method, $notifyId)
894 Create a line into notify, if the method is phone, the notification_send_date is implemented to
896 =cut
898 sub AddNotifyLine {
899 my ( $borrowernumber, $itemnumber, $overduelevel, $method, $notifyId ) = @_;
900 my $dbh = C4::Context->dbh;
901 if ( $method eq "phone" ) {
902 my $sth = $dbh->prepare(
903 "INSERT INTO notifys (borrowernumber,itemnumber,notify_date,notify_send_date,notify_level,method,notify_id)
904 VALUES (?,?,now(),now(),?,?,?)"
906 $sth->execute( $borrowernumber, $itemnumber, $overduelevel, $method,
907 $notifyId );
909 else {
910 my $sth = $dbh->prepare(
911 "INSERT INTO notifys (borrowernumber,itemnumber,notify_date,notify_level,method,notify_id)
912 VALUES (?,?,now(),?,?,?)"
914 $sth->execute( $borrowernumber, $itemnumber, $overduelevel, $method,
915 $notifyId );
917 return 1;
920 =head2 RemoveNotifyLine
922 &RemoveNotifyLine( $borrowernumber, $itemnumber, $notify_date );
924 Cancel a notification
926 =cut
928 sub RemoveNotifyLine {
929 my ( $borrowernumber, $itemnumber, $notify_date ) = @_;
930 my $dbh = C4::Context->dbh;
931 my $sth = $dbh->prepare(
932 "DELETE FROM notifys
933 WHERE
934 borrowernumber=?
935 AND itemnumber=?
936 AND notify_date=?"
938 $sth->execute( $borrowernumber, $itemnumber, $notify_date );
939 return 1;
942 =head2 GetOverdueMessageTransportTypes
944 my $message_transport_types = GetOverdueMessageTransportTypes( $branchcode, $categorycode, $letternumber);
946 return a arrayref with all message_transport_type for given branchcode, categorycode and letternumber(1,2 or 3)
948 =cut
950 sub GetOverdueMessageTransportTypes {
951 my ( $branchcode, $categorycode, $letternumber ) = @_;
952 return unless $categorycode and $letternumber;
953 my $dbh = C4::Context->dbh;
954 my $sth = $dbh->prepare("
955 SELECT message_transport_type
956 FROM overduerules odr LEFT JOIN overduerules_transport_types ott USING (overduerules_id)
957 WHERE branchcode = ?
958 AND categorycode = ?
959 AND letternumber = ?
961 $sth->execute( $branchcode, $categorycode, $letternumber );
962 my @mtts;
963 while ( my $mtt = $sth->fetchrow ) {
964 push @mtts, $mtt;
967 # Put 'print' in first if exists
968 # It avoid to sent a print notice with an email or sms template is no email or sms is defined
969 @mtts = uniq( 'print', @mtts )
970 if grep {/^print$/} @mtts;
972 return \@mtts;
975 =head2 parse_overdues_letter
977 parses the letter template, replacing the placeholders with data
978 specific to this patron, biblio, or item for overdues
980 named parameters:
981 letter - required hashref
982 borrowernumber - required integer
983 substitute - optional hashref of other key/value pairs that should
984 be substituted in the letter content
986 returns the C<letter> hashref, with the content updated to reflect the
987 substituted keys and values.
989 =cut
991 sub parse_overdues_letter {
992 my $params = shift;
993 foreach my $required (qw( letter_code borrowernumber )) {
994 return unless ( exists $params->{$required} && $params->{$required} );
997 my $substitute = $params->{'substitute'} || {};
998 $substitute->{today} ||= output_pref( { dt => dt_from_string, dateonly => 1} );
1000 my %tables = ( 'borrowers' => $params->{'borrowernumber'} );
1001 if ( my $p = $params->{'branchcode'} ) {
1002 $tables{'branches'} = $p;
1005 my $active_currency = Koha::Acquisition::Currencies->get_active;
1007 my $currency_format;
1008 $currency_format = $active_currency->currency if defined($active_currency);
1010 my @item_tables;
1011 if ( my $i = $params->{'items'} ) {
1012 foreach my $item (@$i) {
1013 my $fine = GetFine($item->{'itemnumber'}, $params->{'borrowernumber'});
1014 $item->{'fine'} = currency_format($currency_format, "$fine", FMT_SYMBOL);
1015 # if active currency isn't correct ISO code fallback to sprintf
1016 $item->{'fine'} = sprintf('%.2f', $fine) unless $item->{'fine'};
1018 push @item_tables, {
1019 'biblio' => $item->{'biblionumber'},
1020 'biblioitems' => $item->{'biblionumber'},
1021 'items' => $item,
1022 'issues' => $item->{'itemnumber'},
1027 return C4::Letters::GetPreparedLetter (
1028 module => 'circulation',
1029 letter_code => $params->{'letter_code'},
1030 branchcode => $params->{'branchcode'},
1031 tables => \%tables,
1032 substitute => $substitute,
1033 repeat => { item => \@item_tables },
1034 message_transport_type => $params->{message_transport_type},
1039 __END__
1041 =head1 AUTHOR
1043 Koha Development Team <http://koha-community.org/>
1045 =cut