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; |