| File: | C4/Overdues.pm |
| Coverage: | 10.0% |
| line | stmt | bran | cond | sub | time | code |
|---|---|---|---|---|---|---|
| 1 | package C4::Overdues; | |||||
| 2 | ||||||
| 3 | ||||||
| 4 | # Copyright 2000-2002 Katipo Communications | |||||
| 5 | # copyright 2010 BibLibre | |||||
| 6 | # | |||||
| 7 | # This file is part of Koha. | |||||
| 8 | # | |||||
| 9 | # Koha is free software; you can redistribute it and/or modify it under the | |||||
| 10 | # terms of the GNU General Public License as published by the Free Software | |||||
| 11 | # Foundation; either version 2 of the License, or (at your option) any later | |||||
| 12 | # version. | |||||
| 13 | # | |||||
| 14 | # Koha is distributed in the hope that it will be useful, but WITHOUT ANY | |||||
| 15 | # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR | |||||
| 16 | # A PARTICULAR PURPOSE. See the GNU General Public License for more details. | |||||
| 17 | # | |||||
| 18 | # You should have received a copy of the GNU General Public License along | |||||
| 19 | # with Koha; if not, write to the Free Software Foundation, Inc., | |||||
| 20 | # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. | |||||
| 21 | ||||||
| 22 | 14 14 14 | 512 39 540 | use strict; | |||
| 23 | #use warnings; FIXME - Bug 2505 | |||||
| 24 | 14 14 14 | 343 3566 893 | use Date::Calc qw/Today Date_to_Days/; | |||
| 25 | 14 14 14 | 26475 2238617 2207 | use Date::Manip qw/UnixDate/; | |||
| 26 | 14 14 14 | 2201 182 4802 | use C4::Circulation; | |||
| 27 | 14 14 14 | 224 124 327 | use C4::Context; | |||
| 28 | 14 14 14 | 443 110 2519 | use C4::Accounts; | |||
| 29 | 14 14 14 | 137 82 1787 | use C4::Log; # logaction | |||
| 30 | 14 14 14 | 136 88 1318 | use C4::Debug; | |||
| 31 | ||||||
| 32 | 14 14 14 | 167 135 2733 | use vars qw($VERSION @ISA @EXPORT); | |||
| 33 | ||||||
| 34 | BEGIN { | |||||
| 35 | # set the version for version checking | |||||
| 36 | 14 | 63 | $VERSION = 3.01; | |||
| 37 | 14 | 100 | require Exporter; | |||
| 38 | 14 | 254 | @ISA = qw(Exporter); | |||
| 39 | # subs to rename (and maybe merge some...) | |||||
| 40 | 14 | 202 | push @EXPORT, qw( | |||
| 41 | &CalcFine | |||||
| 42 | &Getoverdues | |||||
| 43 | &checkoverdues | |||||
| 44 | &CheckAccountLineLevelInfo | |||||
| 45 | &CheckAccountLineItemInfo | |||||
| 46 | &CheckExistantNotifyid | |||||
| 47 | &GetNextIdNotify | |||||
| 48 | &GetNotifyId | |||||
| 49 | &NumberNotifyId | |||||
| 50 | &AmountNotify | |||||
| 51 | &UpdateAccountLines | |||||
| 52 | &UpdateFine | |||||
| 53 | &GetOverdueDelays | |||||
| 54 | &GetOverduerules | |||||
| 55 | &GetFine | |||||
| 56 | &CreateItemAccountLine | |||||
| 57 | &ReplacementCost2 | |||||
| 58 | ||||||
| 59 | &CheckItemNotify | |||||
| 60 | &GetOverduesForBranch | |||||
| 61 | &RemoveNotifyLine | |||||
| 62 | &AddNotifyLine | |||||
| 63 | ); | |||||
| 64 | # subs to remove | |||||
| 65 | 14 | 82 | push @EXPORT, qw( | |||
| 66 | &BorType | |||||
| 67 | ); | |||||
| 68 | ||||||
| 69 | # check that an equivalent don't exist already before moving | |||||
| 70 | ||||||
| 71 | # subs to move to Circulation.pm | |||||
| 72 | 14 | 93 | push @EXPORT, qw( | |||
| 73 | &GetIssuesIteminfo | |||||
| 74 | ); | |||||
| 75 | # | |||||
| 76 | # &GetIssuingRules - delete. | |||||
| 77 | # use C4::Circulation::GetIssuingRule instead. | |||||
| 78 | ||||||
| 79 | # subs to move to Members.pm | |||||
| 80 | 14 | 81 | push @EXPORT, qw( | |||
| 81 | &CheckBorrowerDebarred | |||||
| 82 | ); | |||||
| 83 | # subs to move to Biblio.pm | |||||
| 84 | 14 | 62608 | push @EXPORT, qw( | |||
| 85 | &GetItems | |||||
| 86 | &ReplacementCost | |||||
| 87 | ); | |||||
| 88 | } | |||||
| 89 | ||||||
| 90 - 115 | =head1 NAME
C4::Circulation::Fines - Koha module dealing with fines
=head1 SYNOPSIS
use C4::Overdues;
=head1 DESCRIPTION
This module contains several functions for dealing with fines for
overdue items. It is primarily used by the 'misc/fines2.pl' script.
=head1 FUNCTIONS
=head2 Getoverdues
$overdues = Getoverdues( { minimumdays => 1, maximumdays => 30 } );
Returns the list of all overdue books, with their itemtype.
C<$overdues> is a reference-to-array. Each element is a
reference-to-hash whose keys are the fields of the issues table in the
Koha database.
=cut | |||||
| 116 | ||||||
| 117 | #' | |||||
| 118 | sub Getoverdues { | |||||
| 119 | 0 | my $params = shift; | ||||
| 120 | 0 | my $dbh = C4::Context->dbh; | ||||
| 121 | 0 | my $statement; | ||||
| 122 | 0 | if ( C4::Context->preference('item-level_itypes') ) { | ||||
| 123 | 0 | $statement = " | ||||
| 124 | SELECT issues.*, items.itype as itemtype, items.homebranch, items.barcode | |||||
| 125 | FROM issues | |||||
| 126 | LEFT JOIN items USING (itemnumber) | |||||
| 127 | WHERE date_due < NOW() | |||||
| 128 | "; | |||||
| 129 | } else { | |||||
| 130 | 0 | $statement = " | ||||
| 131 | SELECT issues.*, biblioitems.itemtype, items.itype, items.homebranch, items.barcode | |||||
| 132 | FROM issues | |||||
| 133 | LEFT JOIN items USING (itemnumber) | |||||
| 134 | LEFT JOIN biblioitems USING (biblioitemnumber) | |||||
| 135 | WHERE date_due < NOW() | |||||
| 136 | "; | |||||
| 137 | } | |||||
| 138 | ||||||
| 139 | 0 | my @bind_parameters; | ||||
| 140 | 0 | if ( exists $params->{'minimumdays'} and exists $params->{'maximumdays'} ) { | ||||
| 141 | 0 | $statement .= ' AND TO_DAYS( NOW() )-TO_DAYS( date_due ) BETWEEN ? and ? '; | ||||
| 142 | 0 | push @bind_parameters, $params->{'minimumdays'}, $params->{'maximumdays'}; | ||||
| 143 | } elsif ( exists $params->{'minimumdays'} ) { | |||||
| 144 | 0 | $statement .= ' AND ( TO_DAYS( NOW() )-TO_DAYS( date_due ) ) > ? '; | ||||
| 145 | 0 | push @bind_parameters, $params->{'minimumdays'}; | ||||
| 146 | } elsif ( exists $params->{'maximumdays'} ) { | |||||
| 147 | 0 | $statement .= ' AND ( TO_DAYS( NOW() )-TO_DAYS( date_due ) ) < ? '; | ||||
| 148 | 0 | push @bind_parameters, $params->{'maximumdays'}; | ||||
| 149 | } | |||||
| 150 | 0 | $statement .= 'ORDER BY borrowernumber'; | ||||
| 151 | 0 | my $sth = $dbh->prepare( $statement ); | ||||
| 152 | 0 | $sth->execute( @bind_parameters ); | ||||
| 153 | 0 | return $sth->fetchall_arrayref({}); | ||||
| 154 | } | |||||
| 155 | ||||||
| 156 | ||||||
| 157 - 163 | =head2 checkoverdues
($count, $overdueitems) = checkoverdues($borrowernumber);
Returns a count and a list of overdueitems for a given borrowernumber
=cut | |||||
| 164 | ||||||
| 165 | sub checkoverdues { | |||||
| 166 | 0 | my $borrowernumber = shift or return; | ||||
| 167 | # don't select biblioitems.marc or biblioitems.marcxml... too slow on large systems | |||||
| 168 | 0 | my $sth = C4::Context->dbh->prepare( | ||||
| 169 | "SELECT biblio.*, items.*, issues.*, | |||||
| 170 | biblioitems.volume, | |||||
| 171 | biblioitems.number, | |||||
| 172 | biblioitems.itemtype, | |||||
| 173 | biblioitems.isbn, | |||||
| 174 | biblioitems.issn, | |||||
| 175 | biblioitems.publicationyear, | |||||
| 176 | biblioitems.publishercode, | |||||
| 177 | biblioitems.volumedate, | |||||
| 178 | biblioitems.volumedesc, | |||||
| 179 | biblioitems.collectiontitle, | |||||
| 180 | biblioitems.collectionissn, | |||||
| 181 | biblioitems.collectionvolume, | |||||
| 182 | biblioitems.editionstatement, | |||||
| 183 | biblioitems.editionresponsibility, | |||||
| 184 | biblioitems.illus, | |||||
| 185 | biblioitems.pages, | |||||
| 186 | biblioitems.notes, | |||||
| 187 | biblioitems.size, | |||||
| 188 | biblioitems.place, | |||||
| 189 | biblioitems.lccn, | |||||
| 190 | biblioitems.url, | |||||
| 191 | biblioitems.cn_source, | |||||
| 192 | biblioitems.cn_class, | |||||
| 193 | biblioitems.cn_item, | |||||
| 194 | biblioitems.cn_suffix, | |||||
| 195 | biblioitems.cn_sort, | |||||
| 196 | biblioitems.totalissues | |||||
| 197 | FROM issues | |||||
| 198 | LEFT JOIN items ON issues.itemnumber = items.itemnumber | |||||
| 199 | LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber | |||||
| 200 | LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber | |||||
| 201 | WHERE issues.borrowernumber = ? | |||||
| 202 | AND issues.date_due < NOW()" | |||||
| 203 | ); | |||||
| 204 | # FIXME: SELECT * across 4 tables? do we really need the marc AND marcxml blobs?? | |||||
| 205 | 0 | $sth->execute($borrowernumber); | ||||
| 206 | 0 | my $results = $sth->fetchall_arrayref({}); | ||||
| 207 | 0 | return ( scalar(@$results), $results); # returning the count and the results is silly | ||||
| 208 | } | |||||
| 209 | ||||||
| 210 - 252 | =head2 CalcFine
($amount, $chargename, $daycounttotal) = &CalcFine($item,
$categorycode, $branch,
$start_dt, $end_dt );
Calculates the fine for a book.
The issuingrules table in the Koha database is a fine matrix, listing
the penalties for each type of patron for each type of item and each branch (e.g., the
standard fine for books might be $0.50, but $1.50 for DVDs, or staff
members might get a longer grace period between the first and second
reminders that a book is overdue).
C<$item> is an item object (hashref).
C<$categorycode> is the category code (string) of the patron who currently has
the book.
C<$branchcode> is the library (string) whose issuingrules govern this transaction.
C<$start_date> & C<$end_date> are DateTime objects
defining the date range over which to determine the fine.
Fines scripts should just supply the date range over which to calculate the fine.
C<&CalcFine> returns four values:
C<$amount> is the fine owed by the patron (see above).
C<$chargename> is the chargename field from the applicable record in
the categoryitem table, whatever that is.
C<$daycount> is the number of days between start and end dates, Calendar adjusted (where needed),
minus any applicable grace period.
FIXME - What is chargename supposed to be ?
FIXME: previously attempted to return C<$message> as a text message, either "First Notice", "Second Notice",
or "Final Notice". But CalcFine never defined any value.
=cut | |||||
| 253 | ||||||
| 254 | sub CalcFine { | |||||
| 255 | 0 | my ( $item, $bortype, $branchcode, $due_dt, $end_date ) = @_; | ||||
| 256 | 0 | my $start_date = $due_dt->clone(); | ||||
| 257 | 0 | my $dbh = C4::Context->dbh; | ||||
| 258 | 0 | my $amount = 0; | ||||
| 259 | 0 | my $charge_duration; | ||||
| 260 | # get issuingrules (fines part will be used) | |||||
| 261 | 0 | my $data = C4::Circulation::GetIssuingRule($bortype, $item->{itemtype}, $branchcode); | ||||
| 262 | 0 | if(C4::Context->preference('finesCalendar') eq 'noFinesWhenClosed') { | ||||
| 263 | 0 | my $calendar = Koha::Calendar->new( branchcode => $branchcode ); | ||||
| 264 | 0 | $charge_duration = $calendar->days_between( $start_date, $end_date ); | ||||
| 265 | } else { | |||||
| 266 | 0 | $charge_duration = $end_date - $start_date; | ||||
| 267 | } | |||||
| 268 | # correct for grace period. | |||||
| 269 | 0 | my $fine_unit = $data->{lengthunit}; | ||||
| 270 | 0 | $fine_unit ||= 'days'; | ||||
| 271 | 0 | my $chargeable_units; | ||||
| 272 | 0 | if ($fine_unit eq 'hours') { | ||||
| 273 | 0 | $chargeable_units = $charge_duration->hours(); # TODO closed times??? | ||||
| 274 | } | |||||
| 275 | else { | |||||
| 276 | 0 | $chargeable_units = $charge_duration->days; | ||||
| 277 | } | |||||
| 278 | 0 | my $days_minus_grace = $chargeable_units - $data->{firstremind}; | ||||
| 279 | 0 | if ($data->{'chargeperiod'} && $days_minus_grace ) { | ||||
| 280 | 0 | $amount = int($chargeable_units / $data->{'chargeperiod'}) * $data->{'fine'};# TODO fine calc should be in cents | ||||
| 281 | } else { | |||||
| 282 | # a zero (or null) chargeperiod means no charge. | |||||
| 283 | } | |||||
| 284 | 0 | if(C4::Context->preference('maxFine') && ( $amount > C4::Context->preference('maxFine'))) { | ||||
| 285 | 0 | $amount = C4::Context->preference('maxFine'); | ||||
| 286 | } | |||||
| 287 | 0 | return ($amount, $data->{chargename}, $days_minus_grace); | ||||
| 288 | # FIXME: chargename is NEVER populated anywhere. | |||||
| 289 | } | |||||
| 290 | ||||||
| 291 | ||||||
| 292 - 302 | =head2 GetSpecialHolidays
&GetSpecialHolidays($date_dues,$itemnumber);
return number of special days between date of the day and date due
C<$date_dues> is the envisaged date of book return.
C<$itemnumber> is the book's item number.
=cut | |||||
| 303 | ||||||
| 304 | sub GetSpecialHolidays { | |||||
| 305 | 0 | my ( $date_dues, $itemnumber ) = @_; | ||||
| 306 | ||||||
| 307 | # calcul the today date | |||||
| 308 | 0 | my $today = join "-", &Today(); | ||||
| 309 | ||||||
| 310 | # return the holdingbranch | |||||
| 311 | 0 | my $iteminfo = GetIssuesIteminfo($itemnumber); | ||||
| 312 | ||||||
| 313 | # use sql request to find all date between date_due and today | |||||
| 314 | 0 | my $dbh = C4::Context->dbh; | ||||
| 315 | 0 | my $query = | ||||
| 316 | qq|SELECT DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d') as date | |||||
| 317 | FROM `special_holidays` | |||||
| 318 | WHERE DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d') >= ? | |||||
| 319 | AND DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d') <= ? | |||||
| 320 | AND branchcode=? | |||||
| 321 | |; | |||||
| 322 | 0 | my @result = GetWdayFromItemnumber($itemnumber); | ||||
| 323 | 0 | my @result_date; | ||||
| 324 | 0 | my $wday; | ||||
| 325 | 0 | my $dateinsec; | ||||
| 326 | 0 | my $sth = $dbh->prepare($query); | ||||
| 327 | 0 | $sth->execute( $date_dues, $today, $iteminfo->{'branchcode'} ) | ||||
| 328 | ; # FIXME: just use NOW() in SQL instead of passing in $today | |||||
| 329 | ||||||
| 330 | 0 | while ( my $special_date = $sth->fetchrow_hashref ) { | ||||
| 331 | 0 | push( @result_date, $special_date ); | ||||
| 332 | } | |||||
| 333 | ||||||
| 334 | 0 | my $specialdaycount = scalar(@result_date); | ||||
| 335 | ||||||
| 336 | for ( my $i = 0 ; $i < scalar(@result_date) ; $i++ ) { | |||||
| 337 | 0 | $dateinsec = UnixDate( $result_date[$i]->{'date'}, "%o" ); | ||||
| 338 | 0 | ( undef, undef, undef, undef, undef, undef, $wday, undef, undef ) = | ||||
| 339 | localtime($dateinsec); | |||||
| 340 | for ( my $j = 0 ; $j < scalar(@result) ; $j++ ) { | |||||
| 341 | 0 | if ( $wday == ( $result[$j]->{'weekday'} ) ) { | ||||
| 342 | 0 | $specialdaycount--; | ||||
| 343 | } | |||||
| 344 | 0 | } | ||||
| 345 | 0 | } | ||||
| 346 | ||||||
| 347 | 0 | return $specialdaycount; | ||||
| 348 | } | |||||
| 349 | ||||||
| 350 - 362 | =head2 GetRepeatableHolidays
&GetRepeatableHolidays($date_dues, $itemnumber, $difference,);
return number of day closed between date of the day and date due
C<$date_dues> is the envisaged date of book return.
C<$itemnumber> is item number.
C<$difference> numbers of between day date of the day and date due
=cut | |||||
| 363 | ||||||
| 364 | sub GetRepeatableHolidays { | |||||
| 365 | 0 | my ( $date_dues, $itemnumber, $difference ) = @_; | ||||
| 366 | 0 | my $dateinsec = UnixDate( $date_dues, "%o" ); | ||||
| 367 | 0 | my ( $sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $isdst ) = | ||||
| 368 | localtime($dateinsec); | |||||
| 369 | 0 | my @result = GetWdayFromItemnumber($itemnumber); | ||||
| 370 | 0 | my @dayclosedcount; | ||||
| 371 | 0 | my $j; | ||||
| 372 | ||||||
| 373 | for ( my $i = 0 ; $i < scalar(@result) ; $i++ ) { | |||||
| 374 | 0 | my $k = $wday; | ||||
| 375 | ||||||
| 376 | for ( $j = 0 ; $j < $difference ; $j++ ) { | |||||
| 377 | 0 | if ( $result[$i]->{'weekday'} == $k ) { | ||||
| 378 | 0 | push( @dayclosedcount, $k ); | ||||
| 379 | } | |||||
| 380 | 0 | $k++; | ||||
| 381 | 0 | ( $k = 0 ) if ( $k eq 7 ); | ||||
| 382 | 0 | } | ||||
| 383 | 0 | } | ||||
| 384 | 0 | return scalar(@dayclosedcount); | ||||
| 385 | } | |||||
| 386 | ||||||
| 387 | ||||||
| 388 - 396 | =head2 GetWayFromItemnumber
&Getwdayfromitemnumber($itemnumber);
return the different week day from repeatable_holidays table
C<$itemnumber> is item number.
=cut | |||||
| 397 | ||||||
| 398 | sub GetWdayFromItemnumber { | |||||
| 399 | 0 | my ($itemnumber) = @_; | ||||
| 400 | 0 | my $iteminfo = GetIssuesIteminfo($itemnumber); | ||||
| 401 | 0 | my @result; | ||||
| 402 | 0 | my $query = qq|SELECT weekday | ||||
| 403 | FROM repeatable_holidays | |||||
| 404 | WHERE branchcode=? | |||||
| 405 | |; | |||||
| 406 | 0 | my $sth = C4::Context->dbh->prepare($query); | ||||
| 407 | ||||||
| 408 | 0 | $sth->execute( $iteminfo->{'branchcode'} ); | ||||
| 409 | 0 | while ( my $weekday = $sth->fetchrow_hashref ) { | ||||
| 410 | 0 | push( @result, $weekday ); | ||||
| 411 | } | |||||
| 412 | 0 | return @result; | ||||
| 413 | } | |||||
| 414 | ||||||
| 415 | ||||||
| 416 - 424 | =head2 GetIssuesIteminfo
&GetIssuesIteminfo($itemnumber);
return all data from issues about item
C<$itemnumber> is item number.
=cut | |||||
| 425 | ||||||
| 426 | sub GetIssuesIteminfo { | |||||
| 427 | 0 | my ($itemnumber) = @_; | ||||
| 428 | 0 | my $dbh = C4::Context->dbh; | ||||
| 429 | 0 | my $query = qq|SELECT * | ||||
| 430 | FROM issues | |||||
| 431 | WHERE itemnumber=? | |||||
| 432 | |; | |||||
| 433 | 0 | my $sth = $dbh->prepare($query); | ||||
| 434 | 0 | $sth->execute($itemnumber); | ||||
| 435 | 0 | my ($issuesinfo) = $sth->fetchrow_hashref; | ||||
| 436 | 0 | return $issuesinfo; | ||||
| 437 | } | |||||
| 438 | ||||||
| 439 | ||||||
| 440 - 464 | =head2 UpdateFine
&UpdateFine($itemnumber, $borrowernumber, $amount, $type, $description);
(Note: the following is mostly conjecture and guesswork.)
Updates the fine owed on an overdue book.
C<$itemnumber> is the book's item number.
C<$borrowernumber> is the borrower number of the patron who currently
has the book on loan.
C<$amount> is the current amount owed by the patron.
C<$type> will be used in the description of the fine.
C<$description> is a string that must be present in the description of
the fine. I think this is expected to be a date in DD/MM/YYYY format.
C<&UpdateFine> looks up the amount currently owed on the given item
and sets it to C<$amount>, creating, if necessary, a new entry in the
accountlines table of the Koha database.
=cut | |||||
| 465 | ||||||
| 466 | # | |||||
| 467 | # Question: Why should the caller have to | |||||
| 468 | # specify both the item number and the borrower number? A book can't | |||||
| 469 | # be on loan to two different people, so the item number should be | |||||
| 470 | # sufficient. | |||||
| 471 | # | |||||
| 472 | # Possible Answer: You might update a fine for a damaged item, *after* it is returned. | |||||
| 473 | # | |||||
| 474 | sub UpdateFine { | |||||
| 475 | 0 | my ( $itemnum, $borrowernumber, $amount, $type, $due ) = @_; | ||||
| 476 | 0 | $debug and warn "UpdateFine($itemnum, $borrowernumber, $amount, " . ($type||'""') . ", $due) called"; | ||||
| 477 | 0 | my $dbh = C4::Context->dbh; | ||||
| 478 | # FIXME - What exactly is this query supposed to do? It looks up an | |||||
| 479 | # entry in accountlines that matches the given item and borrower | |||||
| 480 | # numbers, where the description contains $due, and where the | |||||
| 481 | # account type has one of several values, but what does this _mean_? | |||||
| 482 | # Does it look up existing fines for this item? | |||||
| 483 | # FIXME - What are these various account types? ("FU", "O", "F", "M") | |||||
| 484 | # "L" is LOST item | |||||
| 485 | # "A" is Account Management Fee | |||||
| 486 | # "N" is New Card | |||||
| 487 | # "M" is Sundry | |||||
| 488 | # "O" is Overdue ?? | |||||
| 489 | # "F" is Fine ?? | |||||
| 490 | # "FU" is Fine UPDATE?? | |||||
| 491 | # "Pay" is Payment | |||||
| 492 | # "REF" is Cash Refund | |||||
| 493 | 0 | my $sth = $dbh->prepare( | ||||
| 494 | "SELECT * FROM accountlines | |||||
| 495 | WHERE itemnumber=? | |||||
| 496 | AND borrowernumber=? | |||||
| 497 | AND accounttype IN ('FU','O','F','M') | |||||
| 498 | AND description like ? " | |||||
| 499 | ); | |||||
| 500 | 0 | $sth->execute( $itemnum, $borrowernumber, "%$due%" ); | ||||
| 501 | ||||||
| 502 | 0 | if ( my $data = $sth->fetchrow_hashref ) { | ||||
| 503 | ||||||
| 504 | # we're updating an existing fine. Only modify if amount changed | |||||
| 505 | # Note that in the current implementation, you cannot pay against an accruing fine | |||||
| 506 | # (i.e. , of accounttype 'FU'). Doing so will break accrual. | |||||
| 507 | 0 | if ( $data->{'amount'} != $amount ) { | ||||
| 508 | 0 | my $diff = $amount - $data->{'amount'}; | ||||
| 509 | #3341: diff could be positive or negative! | |||||
| 510 | 0 | my $out = $data->{'amountoutstanding'} + $diff; | ||||
| 511 | 0 | my $query = " | ||||
| 512 | UPDATE accountlines | |||||
| 513 | SET date=now(), amount=?, amountoutstanding=?, | |||||
| 514 | lastincrement=?, accounttype='FU' | |||||
| 515 | WHERE borrowernumber=? | |||||
| 516 | AND itemnumber=? | |||||
| 517 | AND accounttype IN ('FU','O') | |||||
| 518 | AND description LIKE ? | |||||
| 519 | LIMIT 1 "; | |||||
| 520 | 0 | my $sth2 = $dbh->prepare($query); | ||||
| 521 | # FIXME: BOGUS query cannot ensure uniqueness w/ LIKE %x% !!! | |||||
| 522 | # LIMIT 1 added to prevent multiple affected lines | |||||
| 523 | # FIXME: accountlines table needs unique key!! Possibly a combo of borrowernumber and accountline. | |||||
| 524 | # But actually, we should just have a regular autoincrementing PK and forget accountline, | |||||
| 525 | # including the bogus getnextaccountno function (doesn't prevent conflict on simultaneous ops). | |||||
| 526 | # FIXME: Why only 2 account types here? | |||||
| 527 | 0 | $debug and print STDERR "UpdateFine query: $query\n" . | ||||
| 528 | "w/ args: $amount, $out, $diff, $data->{'borrowernumber'}, $data->{'itemnumber'}, \"\%$due\%\"\n"; | |||||
| 529 | 0 | $sth2->execute($amount, $out, $diff, $data->{'borrowernumber'}, $data->{'itemnumber'}, "%$due%"); | ||||
| 530 | } else { | |||||
| 531 | # print "no update needed $data->{'amount'}" | |||||
| 532 | } | |||||
| 533 | } else { | |||||
| 534 | 0 | my $sth4 = $dbh->prepare( | ||||
| 535 | "SELECT title FROM biblio LEFT JOIN items ON biblio.biblionumber=items.biblionumber WHERE items.itemnumber=?" | |||||
| 536 | ); | |||||
| 537 | 0 | $sth4->execute($itemnum); | ||||
| 538 | 0 | my $title = $sth4->fetchrow; | ||||
| 539 | ||||||
| 540 | # # print "not in account"; | |||||
| 541 | # my $sth3 = $dbh->prepare("Select max(accountno) from accountlines"); | |||||
| 542 | # $sth3->execute; | |||||
| 543 | # | |||||
| 544 | # # FIXME - Make $accountno a scalar. | |||||
| 545 | # my @accountno = $sth3->fetchrow_array; | |||||
| 546 | # $sth3->finish; | |||||
| 547 | # $accountno[0]++; | |||||
| 548 | # begin transaction | |||||
| 549 | 0 | my $nextaccntno = C4::Accounts::getnextacctno($borrowernumber); | ||||
| 550 | 0 | my $desc = ($type ? "$type " : '') . "$title $due"; # FIXEDME, avoid whitespace prefix on empty $type | ||||
| 551 | 0 | my $query = "INSERT INTO accountlines | ||||
| 552 | (borrowernumber,itemnumber,date,amount,description,accounttype,amountoutstanding,lastincrement,accountno) | |||||
| 553 | VALUES (?,?,now(),?,?,'FU',?,?,?)"; | |||||
| 554 | 0 | my $sth2 = $dbh->prepare($query); | ||||
| 555 | 0 | $debug and print STDERR "UpdateFine query: $query\nw/ args: $borrowernumber, $itemnum, $amount, $desc, $amount, $amount, $nextaccntno\n"; | ||||
| 556 | 0 | $sth2->execute($borrowernumber, $itemnum, $amount, $desc, $amount, $amount, $nextaccntno); | ||||
| 557 | } | |||||
| 558 | # logging action | |||||
| 559 | 0 | &logaction( | ||||
| 560 | "FINES", | |||||
| 561 | $type, | |||||
| 562 | $borrowernumber, | |||||
| 563 | "due=".$due." amount=".$amount." itemnumber=".$itemnum | |||||
| 564 | ) if C4::Context->preference("FinesLog"); | |||||
| 565 | } | |||||
| 566 | ||||||
| 567 - 578 | =head2 BorType
$borrower = &BorType($borrowernumber);
Looks up a patron by borrower number.
C<$borrower> is a reference-to-hash whose keys are all of the fields
from the borrowers and categories tables of the Koha database. Thus,
C<$borrower> contains all information about both the borrower and
category he or she belongs to.
=cut | |||||
| 579 | ||||||
| 580 | #' | |||||
| 581 | sub BorType { | |||||
| 582 | 0 | my ($borrowernumber) = @_; | ||||
| 583 | 0 | my $dbh = C4::Context->dbh; | ||||
| 584 | 0 | my $sth = $dbh->prepare( | ||||
| 585 | "SELECT * from borrowers | |||||
| 586 | LEFT JOIN categories ON borrowers.categorycode=categories.categorycode | |||||
| 587 | WHERE borrowernumber=?" | |||||
| 588 | ); | |||||
| 589 | 0 | $sth->execute($borrowernumber); | ||||
| 590 | 0 | return $sth->fetchrow_hashref; | ||||
| 591 | } | |||||
| 592 | ||||||
| 593 - 599 | =head2 ReplacementCost
$cost = &ReplacementCost($itemnumber);
Returns the replacement cost of the item with the given item number.
=cut | |||||
| 600 | ||||||
| 601 | #' | |||||
| 602 | sub ReplacementCost { | |||||
| 603 | 0 | my ($itemnum) = @_; | ||||
| 604 | 0 | my $dbh = C4::Context->dbh; | ||||
| 605 | 0 | my $sth = | ||||
| 606 | $dbh->prepare("Select replacementprice from items where itemnumber=?"); | |||||
| 607 | 0 | $sth->execute($itemnum); | ||||
| 608 | ||||||
| 609 | # FIXME - Use fetchrow_array or a slice. | |||||
| 610 | 0 | my $data = $sth->fetchrow_hashref; | ||||
| 611 | 0 | return ( $data->{'replacementprice'} ); | ||||
| 612 | } | |||||
| 613 | ||||||
| 614 - 624 | =head2 GetFine
$data->{'sum(amountoutstanding)'} = &GetFine($itemnum,$borrowernumber);
return the total of fine
C<$itemnum> is item number
C<$borrowernumber> is the borrowernumber
=cut | |||||
| 625 | ||||||
| 626 | ||||||
| 627 | sub GetFine { | |||||
| 628 | 0 | my ( $itemnum, $borrowernumber ) = @_; | ||||
| 629 | 0 | my $dbh = C4::Context->dbh(); | ||||
| 630 | 0 | my $query = q|SELECT sum(amountoutstanding) as fineamount FROM accountlines | ||||
| 631 | where accounttype like 'F%' | |||||
| 632 | AND amountoutstanding > 0 AND itemnumber = ? AND borrowernumber=?|; | |||||
| 633 | 0 | my $sth = $dbh->prepare($query); | ||||
| 634 | 0 | $sth->execute( $itemnum, $borrowernumber ); | ||||
| 635 | 0 | my $fine = $sth->fetchrow_hashref(); | ||||
| 636 | 0 | if ($fine->{fineamount}) { | ||||
| 637 | 0 | return $fine->{fineamount}; | ||||
| 638 | } | |||||
| 639 | 0 | return 0; | ||||
| 640 | } | |||||
| 641 | ||||||
| 642 | ||||||
| 643 - 659 | =head2 GetIssuingRules
FIXME - This sub should be deprecated and removed.
It ignores branch and defaults.
$data = &GetIssuingRules($itemtype,$categorycode);
Looks up for all issuingrules an item info
C<$itemnumber> is a reference-to-hash whose keys are all of the fields
from the borrowers and categories tables of the Koha database. Thus,
C<$categorycode> contains information about borrowers category
C<$data> contains all information about both the borrower and
category he or she belongs to.
=cut | |||||
| 660 | ||||||
| 661 | sub GetIssuingRules { | |||||
| 662 | 0 | warn "GetIssuingRules is deprecated: use GetIssuingRule from C4::Circulation instead."; | ||||
| 663 | 0 | my ($itemtype,$categorycode)=@_; | ||||
| 664 | 0 | my $dbh = C4::Context->dbh(); | ||||
| 665 | 0 | my $query=qq|SELECT * | ||||
| 666 | FROM issuingrules | |||||
| 667 | WHERE issuingrules.itemtype=? | |||||
| 668 | AND issuingrules.categorycode=? | |||||
| 669 | |; | |||||
| 670 | 0 | my $sth = $dbh->prepare($query); | ||||
| 671 | # print $query; | |||||
| 672 | 0 | $sth->execute($itemtype,$categorycode); | ||||
| 673 | 0 | return $sth->fetchrow_hashref; | ||||
| 674 | } | |||||
| 675 | ||||||
| 676 | ||||||
| 677 | sub ReplacementCost2 { | |||||
| 678 | 0 | my ( $itemnum, $borrowernumber ) = @_; | ||||
| 679 | 0 | my $dbh = C4::Context->dbh(); | ||||
| 680 | 0 | my $query = "SELECT amountoutstanding | ||||
| 681 | FROM accountlines | |||||
| 682 | WHERE accounttype like 'L' | |||||
| 683 | AND amountoutstanding > 0 | |||||
| 684 | AND itemnumber = ? | |||||
| 685 | AND borrowernumber= ?"; | |||||
| 686 | 0 | my $sth = $dbh->prepare($query); | ||||
| 687 | 0 | $sth->execute( $itemnum, $borrowernumber ); | ||||
| 688 | 0 | my $data = $sth->fetchrow_hashref(); | ||||
| 689 | 0 | return ( $data->{'amountoutstanding'} ); | ||||
| 690 | } | |||||
| 691 | ||||||
| 692 | ||||||
| 693 - 703 | =head2 GetNextIdNotify
($result) = &GetNextIdNotify($reference);
Returns the new file number
C<$result> contains the next file number
C<$reference> contains the beggining of file number
=cut | |||||
| 704 | ||||||
| 705 | sub GetNextIdNotify { | |||||
| 706 | 0 | my ($reference) = @_; | ||||
| 707 | 0 | my $query = qq|SELECT max(notify_id) | ||||
| 708 | FROM accountlines | |||||
| 709 | WHERE notify_id like \"$reference%\" | |||||
| 710 | |; | |||||
| 711 | ||||||
| 712 | # AND borrowernumber=?|; | |||||
| 713 | 0 | my $dbh = C4::Context->dbh; | ||||
| 714 | 0 | my $sth = $dbh->prepare($query); | ||||
| 715 | 0 | $sth->execute(); | ||||
| 716 | 0 | my $result = $sth->fetchrow; | ||||
| 717 | 0 | my $count; | ||||
| 718 | 0 | if ( $result eq '' ) { | ||||
| 719 | 0 | ( $result = $reference . "01" ); | ||||
| 720 | } | |||||
| 721 | else { | |||||
| 722 | 0 | $count = substr( $result, 6 ) + 1; | ||||
| 723 | ||||||
| 724 | 0 | if ( $count < 10 ) { | ||||
| 725 | 0 | ( $count = "0" . $count ); | ||||
| 726 | } | |||||
| 727 | 0 | $result = $reference . $count; | ||||
| 728 | } | |||||
| 729 | 0 | return $result; | ||||
| 730 | } | |||||
| 731 | ||||||
| 732 - 741 | =head2 NumberNotifyId
(@notify) = &NumberNotifyId($borrowernumber);
Returns amount for all file per borrowers
C<@notify> array contains all file per borrowers
C<$notify_id> contains the file number for the borrower number nad item number
=cut | |||||
| 742 | ||||||
| 743 | sub NumberNotifyId{ | |||||
| 744 | 0 | my ($borrowernumber)=@_; | ||||
| 745 | 0 | my $dbh = C4::Context->dbh; | ||||
| 746 | 0 | my $query=qq| SELECT distinct(notify_id) | ||||
| 747 | FROM accountlines | |||||
| 748 | WHERE borrowernumber=?|; | |||||
| 749 | 0 | my @notify; | ||||
| 750 | 0 | my $sth = $dbh->prepare($query); | ||||
| 751 | 0 | $sth->execute($borrowernumber); | ||||
| 752 | 0 | while ( my ($numberofnotify) = $sth->fetchrow ) { | ||||
| 753 | 0 | push( @notify, $numberofnotify ); | ||||
| 754 | } | |||||
| 755 | 0 | return (@notify); | ||||
| 756 | } | |||||
| 757 | ||||||
| 758 - 769 | =head2 AmountNotify
($totalnotify) = &AmountNotify($notifyid);
Returns amount for all file per borrowers
C<$notifyid> is the file number
C<$totalnotify> contains amount of a file
C<$notify_id> contains the file number for the borrower number and item number
=cut | |||||
| 770 | ||||||
| 771 | sub AmountNotify{ | |||||
| 772 | 0 | my ($notifyid,$borrowernumber)=@_; | ||||
| 773 | 0 | my $dbh = C4::Context->dbh; | ||||
| 774 | 0 | my $query=qq| SELECT sum(amountoutstanding) | ||||
| 775 | FROM accountlines | |||||
| 776 | WHERE notify_id=? AND borrowernumber = ?|; | |||||
| 777 | 0 | my $sth=$dbh->prepare($query); | ||||
| 778 | 0 | $sth->execute($notifyid,$borrowernumber); | ||||
| 779 | 0 | my $totalnotify=$sth->fetchrow; | ||||
| 780 | 0 | $sth->finish; | ||||
| 781 | 0 | return ($totalnotify); | ||||
| 782 | } | |||||
| 783 | ||||||
| 784 | ||||||
| 785 - 798 | =head2 GetNotifyId
($notify_id) = &GetNotifyId($borrowernumber,$itemnumber);
Returns the file number per borrower and itemnumber
C<$borrowernumber> is a reference-to-hash whose keys are all of the fields
from the items tables of the Koha database. Thus,
C<$itemnumber> contains the borrower categorycode
C<$notify_id> contains the file number for the borrower number nad item number
=cut | |||||
| 799 | ||||||
| 800 | sub GetNotifyId { | |||||
| 801 | 0 | my ( $borrowernumber, $itemnumber ) = @_; | ||||
| 802 | 0 | my $query = qq|SELECT notify_id | ||||
| 803 | FROM accountlines | |||||
| 804 | WHERE borrowernumber=? | |||||
| 805 | AND itemnumber=? | |||||
| 806 | AND (accounttype='FU' or accounttype='O')|; | |||||
| 807 | 0 | my $dbh = C4::Context->dbh; | ||||
| 808 | 0 | my $sth = $dbh->prepare($query); | ||||
| 809 | 0 | $sth->execute( $borrowernumber, $itemnumber ); | ||||
| 810 | 0 | my ($notify_id) = $sth->fetchrow; | ||||
| 811 | 0 | $sth->finish; | ||||
| 812 | 0 | return ($notify_id); | ||||
| 813 | } | |||||
| 814 | ||||||
| 815 - 846 | =head2 CreateItemAccountLine
() = &CreateItemAccountLine($borrowernumber, $itemnumber, $date, $amount,
$description, $accounttype, $amountoutstanding,
$timestamp, $notify_id, $level);
update the account lines with file number or with file level
C<$items> is a reference-to-hash whose keys are all of the fields
from the items tables of the Koha database. Thus,
C<$itemnumber> contains the item number
C<$borrowernumber> contains the borrower number
C<$date> contains the date of the day
C<$amount> contains item price
C<$description> contains the descritpion of accounttype
C<$accounttype> contains the account type
C<$amountoutstanding> contains the $amountoutstanding
C<$timestamp> contains the timestamp with time and the date of the day
C<$notify_id> contains the file number
C<$level> contains the file level
=cut | |||||
| 847 | ||||||
| 848 | sub CreateItemAccountLine { | |||||
| 849 | my ( | |||||
| 850 | 0 | $borrowernumber, $itemnumber, $date, $amount, | ||||
| 851 | $description, $accounttype, $amountoutstanding, $timestamp, | |||||
| 852 | $notify_id, $level | |||||
| 853 | ) = @_; | |||||
| 854 | 0 | my $dbh = C4::Context->dbh; | ||||
| 855 | 0 | my $nextaccntno = C4::Accounts::getnextacctno($borrowernumber); | ||||
| 856 | 0 | my $query = "INSERT into accountlines | ||||
| 857 | (borrowernumber,accountno,itemnumber,date,amount,description,accounttype,amountoutstanding,timestamp,notify_id,notify_level) | |||||
| 858 | VALUES | |||||
| 859 | (?,?,?,?,?,?,?,?,?,?,?)"; | |||||
| 860 | ||||||
| 861 | 0 | my $sth = $dbh->prepare($query); | ||||
| 862 | 0 | $sth->execute( | ||||
| 863 | $borrowernumber, $nextaccntno, $itemnumber, | |||||
| 864 | $date, $amount, $description, | |||||
| 865 | $accounttype, $amountoutstanding, $timestamp, | |||||
| 866 | $notify_id, $level | |||||
| 867 | ); | |||||
| 868 | } | |||||
| 869 | ||||||
| 870 - 887 | =head2 UpdateAccountLines
() = &UpdateAccountLines($notify_id,$notify_level,$borrowernumber,$itemnumber);
update the account lines with file number or with file level
C<$items> is a reference-to-hash whose keys are all of the fields
from the items tables of the Koha database. Thus,
C<$itemnumber> contains the item number
C<$notify_id> contains the file number
C<$notify_level> contains the file level
C<$borrowernumber> contains the borrowernumber
=cut | |||||
| 888 | ||||||
| 889 | sub UpdateAccountLines { | |||||
| 890 | 0 | my ( $notify_id, $notify_level, $borrowernumber, $itemnumber ) = @_; | ||||
| 891 | 0 | my $query; | ||||
| 892 | 0 | if ( $notify_id eq '' ) { | ||||
| 893 | 0 | $query = qq|UPDATE accountlines | ||||
| 894 | SET notify_level=? | |||||
| 895 | WHERE borrowernumber=? AND itemnumber=? | |||||
| 896 | AND (accounttype='FU' or accounttype='O')|; | |||||
| 897 | } else { | |||||
| 898 | 0 | $query = qq|UPDATE accountlines | ||||
| 899 | SET notify_id=?, notify_level=? | |||||
| 900 | WHERE borrowernumber=? | |||||
| 901 | AND itemnumber=? | |||||
| 902 | AND (accounttype='FU' or accounttype='O')|; | |||||
| 903 | } | |||||
| 904 | ||||||
| 905 | 0 | my $sth = C4::Context->dbh->prepare($query); | ||||
| 906 | 0 | if ( $notify_id eq '' ) { | ||||
| 907 | 0 | $sth->execute( $notify_level, $borrowernumber, $itemnumber ); | ||||
| 908 | } else { | |||||
| 909 | 0 | $sth->execute( $notify_id, $notify_level, $borrowernumber, $itemnumber ); | ||||
| 910 | } | |||||
| 911 | } | |||||
| 912 | ||||||
| 913 - 924 | =head2 GetItems
($items) = &GetItems($itemnumber);
Returns the list of all delays from overduerules.
C<$items> is a reference-to-hash whose keys are all of the fields
from the items tables of the Koha database. Thus,
C<$itemnumber> contains the borrower categorycode
=cut | |||||
| 925 | ||||||
| 926 | # FIXME: This is a bad function to have here. | |||||
| 927 | # Shouldn't it be in C4::Items? | |||||
| 928 | # Shouldn't it be called GetItem since you only get 1 row? | |||||
| 929 | # Shouldn't it be called GetItem since you give it only 1 itemnumber? | |||||
| 930 | ||||||
| 931 | sub GetItems { | |||||
| 932 | 0 | my $itemnumber = shift or return; | ||||
| 933 | 0 | my $query = qq|SELECT * | ||||
| 934 | FROM items | |||||
| 935 | WHERE itemnumber=?|; | |||||
| 936 | 0 | my $sth = C4::Context->dbh->prepare($query); | ||||
| 937 | 0 | $sth->execute($itemnumber); | ||||
| 938 | 0 | my ($items) = $sth->fetchrow_hashref; | ||||
| 939 | 0 | return ($items); | ||||
| 940 | } | |||||
| 941 | ||||||
| 942 - 952 | =head2 GetOverdueDelays
(@delays) = &GetOverdueDelays($categorycode);
Returns the list of all delays from overduerules.
C<@delays> it's an array contains the three delays from overduerules table
C<$categorycode> contains the borrower categorycode
=cut | |||||
| 953 | ||||||
| 954 | sub GetOverdueDelays { | |||||
| 955 | 0 | my ($category) = @_; | ||||
| 956 | 0 | my $query = qq|SELECT delay1,delay2,delay3 | ||||
| 957 | FROM overduerules | |||||
| 958 | WHERE categorycode=?|; | |||||
| 959 | 0 | my $sth = C4::Context->dbh->prepare($query); | ||||
| 960 | 0 | $sth->execute($category); | ||||
| 961 | 0 | my (@delays) = $sth->fetchrow_array; | ||||
| 962 | 0 | return (@delays); | ||||
| 963 | } | |||||
| 964 | ||||||
| 965 - 971 | =head2 GetBranchcodesWithOverdueRules
my @branchcodes = C4::Overdues::GetBranchcodesWithOverdueRules()
returns a list of branch codes for branches with overdue rules defined.
=cut | |||||
| 972 | ||||||
| 973 | sub GetBranchcodesWithOverdueRules { | |||||
| 974 | 0 | my $dbh = C4::Context->dbh; | ||||
| 975 | 0 | my $rqoverduebranches = $dbh->prepare("SELECT DISTINCT branchcode FROM overduerules WHERE delay1 IS NOT NULL AND branchcode <> '' ORDER BY branchcode"); | ||||
| 976 | 0 | $rqoverduebranches->execute; | ||||
| 977 | 0 0 0 | my @branches = map { shift @$_ } @{ $rqoverduebranches->fetchall_arrayref }; | ||||
| 978 | 0 | if (!$branches[0]) { | ||||
| 979 | 0 | my $availbranches = C4::Branch::GetBranches(); | ||||
| 980 | 0 | @branches = keys %$availbranches; | ||||
| 981 | } | |||||
| 982 | 0 | return @branches; | ||||
| 983 | } | |||||
| 984 | ||||||
| 985 - 1003 | =head2 CheckAccountLineLevelInfo
($exist) = &CheckAccountLineLevelInfo($borrowernumber,$itemnumber,$accounttype,notify_level);
Check and Returns the list of all overdue books.
C<$exist> contains number of line in accounlines
with the same .biblionumber,itemnumber,accounttype,and notify_level
C<$borrowernumber> contains the borrower number
C<$itemnumber> contains item number
C<$accounttype> contains account type
C<$notify_level> contains the accountline level
=cut | |||||
| 1004 | ||||||
| 1005 | sub CheckAccountLineLevelInfo { | |||||
| 1006 | 0 | my ( $borrowernumber, $itemnumber, $level ) = @_; | ||||
| 1007 | 0 | my $dbh = C4::Context->dbh; | ||||
| 1008 | 0 | my $query = qq|SELECT count(*) | ||||
| 1009 | FROM accountlines | |||||
| 1010 | WHERE borrowernumber =? | |||||
| 1011 | AND itemnumber = ? | |||||
| 1012 | AND notify_level=?|; | |||||
| 1013 | 0 | my $sth = $dbh->prepare($query); | ||||
| 1014 | 0 | $sth->execute( $borrowernumber, $itemnumber, $level ); | ||||
| 1015 | 0 | my ($exist) = $sth->fetchrow; | ||||
| 1016 | 0 | return ($exist); | ||||
| 1017 | } | |||||
| 1018 | ||||||
| 1019 - 1031 | =head2 GetOverduerules
($overduerules) = &GetOverduerules($categorycode);
Returns the value of borrowers (debarred or not) with notify level
C<$overduerules> return value of debbraed field in overduerules table
C<$category> contains the borrower categorycode
C<$notify_level> contains the notify level
=cut | |||||
| 1032 | ||||||
| 1033 | sub GetOverduerules { | |||||
| 1034 | 0 | my ( $category, $notify_level ) = @_; | ||||
| 1035 | 0 | my $dbh = C4::Context->dbh; | ||||
| 1036 | 0 | my $query = qq|SELECT debarred$notify_level | ||||
| 1037 | FROM overduerules | |||||
| 1038 | WHERE categorycode=?|; | |||||
| 1039 | 0 | my $sth = $dbh->prepare($query); | ||||
| 1040 | 0 | $sth->execute($category); | ||||
| 1041 | 0 | my ($overduerules) = $sth->fetchrow; | ||||
| 1042 | 0 | return ($overduerules); | ||||
| 1043 | } | |||||
| 1044 | ||||||
| 1045 | ||||||
| 1046 - 1056 | =head2 CheckBorrowerDebarred
($debarredstatus) = &CheckBorrowerDebarred($borrowernumber);
Check if the borrowers is already debarred
C<$debarredstatus> return 0 for not debarred and return 1 for debarred
C<$borrowernumber> contains the borrower number
=cut | |||||
| 1057 | ||||||
| 1058 | # FIXME: Shouldn't this be in C4::Members? | |||||
| 1059 | sub CheckBorrowerDebarred { | |||||
| 1060 | 0 | my ($borrowernumber) = @_; | ||||
| 1061 | 0 | my $dbh = C4::Context->dbh; | ||||
| 1062 | 0 | my $query = qq| | ||||
| 1063 | SELECT debarred | |||||
| 1064 | FROM borrowers | |||||
| 1065 | WHERE borrowernumber=? | |||||
| 1066 | AND debarred > NOW() | |||||
| 1067 | |; | |||||
| 1068 | 0 | my $sth = $dbh->prepare($query); | ||||
| 1069 | 0 | $sth->execute($borrowernumber); | ||||
| 1070 | 0 | my $debarredstatus = $sth->fetchrow; | ||||
| 1071 | 0 | return $debarredstatus; | ||||
| 1072 | } | |||||
| 1073 | ||||||
| 1074 | ||||||
| 1075 - 1088 | =head2 CheckExistantNotifyid
($exist) = &CheckExistantNotifyid($borrowernumber,$itemnumber,$accounttype,$notify_id);
Check and Returns the notify id if exist else return 0.
C<$exist> contains a notify_id
C<$borrowernumber> contains the borrower number
C<$date_due> contains the date of item return
=cut | |||||
| 1089 | ||||||
| 1090 | sub CheckExistantNotifyid { | |||||
| 1091 | 0 | my ( $borrowernumber, $date_due ) = @_; | ||||
| 1092 | 0 | my $dbh = C4::Context->dbh; | ||||
| 1093 | 0 | my $query = qq|SELECT notify_id FROM accountlines | ||||
| 1094 | LEFT JOIN issues ON issues.itemnumber= accountlines.itemnumber | |||||
| 1095 | WHERE accountlines.borrowernumber =? | |||||
| 1096 | AND date_due = ?|; | |||||
| 1097 | 0 | my $sth = $dbh->prepare($query); | ||||
| 1098 | 0 | $sth->execute( $borrowernumber, $date_due ); | ||||
| 1099 | 0 | return $sth->fetchrow || 0; | ||||
| 1100 | } | |||||
| 1101 | ||||||
| 1102 - 1119 | =head2 CheckAccountLineItemInfo
($exist) = &CheckAccountLineItemInfo($borrowernumber,$itemnumber,$accounttype,$notify_id);
Check and Returns the list of all overdue items from the same file number(notify_id).
C<$exist> contains number of line in accounlines
with the same .biblionumber,itemnumber,accounttype,notify_id
C<$borrowernumber> contains the borrower number
C<$itemnumber> contains item number
C<$accounttype> contains account type
C<$notify_id> contains the file number
=cut | |||||
| 1120 | ||||||
| 1121 | sub CheckAccountLineItemInfo { | |||||
| 1122 | 0 | my ( $borrowernumber, $itemnumber, $accounttype, $notify_id ) = @_; | ||||
| 1123 | 0 | my $dbh = C4::Context->dbh; | ||||
| 1124 | 0 | my $query = qq|SELECT count(*) FROM accountlines | ||||
| 1125 | WHERE borrowernumber =? | |||||
| 1126 | AND itemnumber = ? | |||||
| 1127 | AND accounttype= ? | |||||
| 1128 | AND notify_id = ?|; | |||||
| 1129 | 0 | my $sth = $dbh->prepare($query); | ||||
| 1130 | 0 | $sth->execute( $borrowernumber, $itemnumber, $accounttype, $notify_id ); | ||||
| 1131 | 0 | my ($exist) = $sth->fetchrow; | ||||
| 1132 | 0 | return ($exist); | ||||
| 1133 | } | |||||
| 1134 | ||||||
| 1135 - 1140 | =head2 CheckItemNotify Sql request to check if the document has alreday been notified this function is not exported, only used with GetOverduesForBranch =cut | |||||
| 1141 | ||||||
| 1142 | sub CheckItemNotify { | |||||
| 1143 | 0 | my ($notify_id,$notify_level,$itemnumber) = @_; | ||||
| 1144 | 0 | my $dbh = C4::Context->dbh; | ||||
| 1145 | 0 | my $sth = $dbh->prepare(" | ||||
| 1146 | SELECT COUNT(*) | |||||
| 1147 | FROM notifys | |||||
| 1148 | WHERE notify_id = ? | |||||
| 1149 | AND notify_level = ? | |||||
| 1150 | AND itemnumber = ? "); | |||||
| 1151 | 0 | $sth->execute($notify_id,$notify_level,$itemnumber); | ||||
| 1152 | 0 | my $notified = $sth->fetchrow; | ||||
| 1153 | 0 | return ($notified); | ||||
| 1154 | } | |||||
| 1155 | ||||||
| 1156 - 1164 | =head2 GetOverduesForBranch Sql request for display all information for branchoverdues.pl 2 possibilities : with or without location . display is filtered by branch FIXME: This function should be renamed. =cut | |||||
| 1165 | ||||||
| 1166 | sub GetOverduesForBranch { | |||||
| 1167 | 0 | my ( $branch, $location) = @_; | ||||
| 1168 | 0 | my $itype_link = (C4::Context->preference('item-level_itypes')) ? " items.itype " : " biblioitems.itemtype "; | ||||
| 1169 | 0 | my $dbh = C4::Context->dbh; | ||||
| 1170 | 0 | my $select = " | ||||
| 1171 | SELECT | |||||
| 1172 | borrowers.borrowernumber, | |||||
| 1173 | borrowers.surname, | |||||
| 1174 | borrowers.firstname, | |||||
| 1175 | borrowers.phone, | |||||
| 1176 | borrowers.email, | |||||
| 1177 | biblio.title, | |||||
| 1178 | biblio.author, | |||||
| 1179 | biblio.biblionumber, | |||||
| 1180 | issues.date_due, | |||||
| 1181 | issues.returndate, | |||||
| 1182 | issues.branchcode, | |||||
| 1183 | branches.branchname, | |||||
| 1184 | items.barcode, | |||||
| 1185 | items.homebranch, | |||||
| 1186 | items.itemcallnumber, | |||||
| 1187 | items.location, | |||||
| 1188 | items.itemnumber, | |||||
| 1189 | itemtypes.description, | |||||
| 1190 | accountlines.notify_id, | |||||
| 1191 | accountlines.notify_level, | |||||
| 1192 | accountlines.amountoutstanding | |||||
| 1193 | FROM accountlines | |||||
| 1194 | LEFT JOIN issues ON issues.itemnumber = accountlines.itemnumber | |||||
| 1195 | AND issues.borrowernumber = accountlines.borrowernumber | |||||
| 1196 | LEFT JOIN borrowers ON borrowers.borrowernumber = accountlines.borrowernumber | |||||
| 1197 | LEFT JOIN items ON items.itemnumber = issues.itemnumber | |||||
| 1198 | LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber | |||||
| 1199 | LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber | |||||
| 1200 | LEFT JOIN itemtypes ON itemtypes.itemtype = $itype_link | |||||
| 1201 | LEFT JOIN branches ON branches.branchcode = issues.branchcode | |||||
| 1202 | WHERE (accountlines.amountoutstanding != '0.000000') | |||||
| 1203 | AND (accountlines.accounttype = 'FU' ) | |||||
| 1204 | AND (issues.branchcode = ? ) | |||||
| 1205 | AND (issues.date_due < NOW()) | |||||
| 1206 | "; | |||||
| 1207 | 0 | my @getoverdues; | ||||
| 1208 | 0 | my $i = 0; | ||||
| 1209 | 0 | my $sth; | ||||
| 1210 | 0 | if ($location) { | ||||
| 1211 | 0 | $sth = $dbh->prepare("$select AND items.location = ? ORDER BY borrowers.surname, borrowers.firstname"); | ||||
| 1212 | 0 | $sth->execute($branch, $location); | ||||
| 1213 | } else { | |||||
| 1214 | 0 | $sth = $dbh->prepare("$select ORDER BY borrowers.surname, borrowers.firstname"); | ||||
| 1215 | 0 | $sth->execute($branch); | ||||
| 1216 | } | |||||
| 1217 | 0 | while ( my $data = $sth->fetchrow_hashref ) { | ||||
| 1218 | #check if the document has already been notified | |||||
| 1219 | 0 | my $countnotify = CheckItemNotify($data->{'notify_id'}, $data->{'notify_level'}, $data->{'itemnumber'}); | ||||
| 1220 | 0 | if ($countnotify eq '0') { | ||||
| 1221 | 0 | $getoverdues[$i] = $data; | ||||
| 1222 | 0 | $i++; | ||||
| 1223 | } | |||||
| 1224 | } | |||||
| 1225 | 0 | return (@getoverdues); | ||||
| 1226 | } | |||||
| 1227 | ||||||
| 1228 | ||||||
| 1229 - 1235 | =head2 AddNotifyLine
&AddNotifyLine($borrowernumber, $itemnumber, $overduelevel, $method, $notifyId)
Create a line into notify, if the method is phone, the notification_send_date is implemented to
=cut | |||||
| 1236 | ||||||
| 1237 | sub AddNotifyLine { | |||||
| 1238 | 0 | my ( $borrowernumber, $itemnumber, $overduelevel, $method, $notifyId ) = @_; | ||||
| 1239 | 0 | my $dbh = C4::Context->dbh; | ||||
| 1240 | 0 | if ( $method eq "phone" ) { | ||||
| 1241 | 0 | my $sth = $dbh->prepare( | ||||
| 1242 | "INSERT INTO notifys (borrowernumber,itemnumber,notify_date,notify_send_date,notify_level,method,notify_id) | |||||
| 1243 | VALUES (?,?,now(),now(),?,?,?)" | |||||
| 1244 | ); | |||||
| 1245 | 0 | $sth->execute( $borrowernumber, $itemnumber, $overduelevel, $method, | ||||
| 1246 | $notifyId ); | |||||
| 1247 | } | |||||
| 1248 | else { | |||||
| 1249 | 0 | my $sth = $dbh->prepare( | ||||
| 1250 | "INSERT INTO notifys (borrowernumber,itemnumber,notify_date,notify_level,method,notify_id) | |||||
| 1251 | VALUES (?,?,now(),?,?,?)" | |||||
| 1252 | ); | |||||
| 1253 | 0 | $sth->execute( $borrowernumber, $itemnumber, $overduelevel, $method, | ||||
| 1254 | $notifyId ); | |||||
| 1255 | } | |||||
| 1256 | 0 | return 1; | ||||
| 1257 | } | |||||
| 1258 | ||||||
| 1259 - 1265 | =head2 RemoveNotifyLine
&RemoveNotifyLine( $borrowernumber, $itemnumber, $notify_date );
Cancel a notification
=cut | |||||
| 1266 | ||||||
| 1267 | sub RemoveNotifyLine { | |||||
| 1268 | 0 | my ( $borrowernumber, $itemnumber, $notify_date ) = @_; | ||||
| 1269 | 0 | my $dbh = C4::Context->dbh; | ||||
| 1270 | 0 | my $sth = $dbh->prepare( | ||||
| 1271 | "DELETE FROM notifys | |||||
| 1272 | WHERE | |||||
| 1273 | borrowernumber=? | |||||
| 1274 | AND itemnumber=? | |||||
| 1275 | AND notify_date=?" | |||||
| 1276 | ); | |||||
| 1277 | 0 | $sth->execute( $borrowernumber, $itemnumber, $notify_date ); | ||||
| 1278 | 0 | return 1; | ||||
| 1279 | } | |||||
| 1280 | ||||||
| 1281 | 1; | |||||