1 package C4::Reserves2; #assumes C4/Reserves2
4 # Copyright 2000-2002 Katipo Communications
6 # This file is part of Koha.
8 # Koha is free software; you can redistribute it and/or modify it under the
9 # terms of the GNU General Public License as published by the Free Software
10 # Foundation; either version 2 of the License, or (at your option) any later
13 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
14 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
15 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
17 # You should have received a copy of the GNU General Public License along with
18 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
19 # Suite 330, Boston, MA 02111-1307 USA
28 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
30 # set the version for version checking
34 @EXPORT = qw(&FindReserves &CheckReserves &CheckWaiting &CancelReserve &FillReserve &ReserveWaiting &CreateReserve &updatereserves &getreservetitle &Findgroupreserve);
36 # make all your functions, whether exported or not;
41 my $query="SELECT *,reserves.branchcode,biblio.title AS btitle
42 FROM reserves,borrowers,biblio ";
44 $bib = $dbh->quote($bib);
46 $bor = $dbh->quote($bor);
47 $query .= " where reserves.biblionumber = $bib
48 and borrowers.borrowernumber = $bor
49 and reserves.borrowernumber = borrowers.borrowernumber
50 and biblio.biblionumber = $bib
51 and cancellationdate is NULL
52 and (found <> 'F' or found is NULL)";
54 $query .= " where reserves.borrowernumber = borrowers.borrowernumber
55 and biblio.biblionumber = $bib
56 and reserves.biblionumber = $bib
57 and cancellationdate is NULL
58 and (found <> 'F' or found is NULL)";
61 $query .= " where borrowers.borrowernumber = $bor
62 and reserves.borrowernumber = borrowers.borrowernumber
63 and reserves.biblionumber = biblio.biblionumber
64 and cancellationdate is NULL and
65 (found <> 'F' or found is NULL)";
67 $query.=" order by priority";
69 my $sth=$dbh->prepare($query);
73 while (my $data=$sth->fetchrow_hashref){
74 if ($data->{'constrainttype'} eq 'o') {
75 my $conquery = "SELECT biblioitemnumber FROM reserveconstraints
76 WHERE biblionumber = ?
77 AND borrowernumber = ?
79 my $csth=$dbh->prepare($conquery);
80 my $bibn = $data->{'biblionumber'};
81 my $born = $data->{'borrowernumber'};
82 my $resd = $data->{'reservedate'};
83 $csth->execute($bibn, $born, $resd);
84 my ($bibitemno) = $csth->fetchrow_array;
86 my $bdata = C4::Search::bibitemdata($bibitemno);
87 foreach my $key (keys %$bdata) {
88 $data->{$key} = $bdata->{$key};
102 warn "In CheckReserves";
104 my $qitem=$dbh->quote($item);
105 # get the biblionumber...
106 my $sth=$dbh->prepare("select biblionumber, biblioitemnumber from items where itemnumber=$qitem");
108 my ($biblio, $bibitem) = $sth->fetchrow_array;
111 # get the reserves...
112 my ($count, @reserves) = Findgroupreserve($bibitem, $biblio);
113 my $priority = 10000000;
116 foreach my $res (@reserves) {
117 if ($res->{'itemnumber'} == $item) {
118 return ("Waiting", $res);
120 if ($res->{'priority'} != 0 && $res->{'priority'} < $priority) {
121 $priority = $res->{'priority'};
126 warn "highest = $highest";
127 $highest->{'itemnumber'} = $item;
128 foreach my $key (keys %$highest) {
129 warn "$key : $highest->{$key}\n";
132 return ("Reserved", $highest);
139 my ($biblio, $item, $borr) = @_;
141 #warn "In CancelReserve";
142 if (($item and $borr) and (not $biblio)) {
143 # removing a waiting reserve record....
144 $item = $dbh->quote($item);
145 $borr = $dbh->quote($borr);
146 # update the database...
147 my $query = "update reserves set cancellationdate = now(),
150 where itemnumber = $item
151 and borrowernumber = $borr";
152 my $sth = $dbh->prepare($query);
156 if (($biblio and $borr) and (not $item)) {
157 # removing a reserve record....
158 my $q_biblio = $dbh->quote($biblio);
159 $borr = $dbh->quote($borr);
160 # fix up the priorities on the other records....
161 my $query = "SELECT priority FROM reserves
162 WHERE biblionumber = $q_biblio
163 AND borrowernumber = $borr
164 AND cancellationdate is NULL
165 AND (found <> 'F' or found is NULL)";
166 my $sth=$dbh->prepare($query);
168 my ($priority) = $sth->fetchrow_array;
170 # update the database, removing the record...
171 my $query = "update reserves set cancellationdate = now(),
174 where biblionumber = $q_biblio
175 and borrowernumber = $borr
176 and cancellationdate is NULL
177 and (found <> 'F' or found is NULL)";
178 my $sth = $dbh->prepare($query);
181 # now fix the priority on the others....
182 fixpriority($priority, $biblio);
191 # removing a waiting reserve record....
192 my $biblio = $res->{'biblionumber'}; my $qbiblio = $dbh->quote($biblio);
193 my $borr = $res->{'borrowernumber'}; $borr = $dbh->quote($borr);
194 my $resdate = $res->{'reservedate'}; $resdate = $dbh->quote($resdate);
195 # update the database...
196 my $query = "UPDATE reserves SET found = 'F',
198 WHERE biblionumber = $qbiblio
199 AND reservedate = $resdate
200 AND borrowernumber = $borr";
201 my $sth = $dbh->prepare($query);
205 # now fix the priority on the others....
206 fixpriority($res->{'priority'}, $biblio);
210 my ($priority, $biblio) = @_;
212 my ($count, $reserves) = FindReserves($biblio);
213 foreach my $rec (@$reserves) {
214 if ($rec->{'priority'} > $priority) {
215 my $newpr = $rec->{'priority'}; $newpr = $dbh->quote($newpr - 1);
216 my $nbib = $rec->{'biblionumber'}; $nbib = $dbh->quote($nbib);
217 my $nbor = $rec->{'borrowernumber'}; $nbor = $dbh->quote($nbor);
218 my $nresd = $rec->{'reservedate'}; $nresd = $dbh->quote($nresd);
219 my $query = "UPDATE reserves SET priority = $newpr
220 WHERE biblionumber = $nbib
221 AND borrowernumber = $nbor
222 AND reservedate = $nresd";
224 my $sth = $dbh->prepare($query);
235 my ($item, $borr) = @_;
237 $item = $dbh->quote($item);
238 $borr = $dbh->quote($borr);
239 # get priority and biblionumber....
240 my $query = "SELECT reserves.priority as priority,
241 reserves.biblionumber as biblionumber,
242 reserves.branchcode as branchcode
244 WHERE reserves.biblionumber = items.biblionumber
245 AND items.itemnumber = $item
246 AND reserves.borrowernumber = $borr
247 AND reserves.cancellationdate is NULL
248 AND (reserves.found <> 'F' or reserves.found is NULL)";
249 my $sth = $dbh->prepare($query);
251 my $data = $sth->fetchrow_hashref;
253 my $biblio = $data->{'biblionumber'};
254 my $q_biblio = $dbh->quote($biblio);
255 # update reserves record....
256 $query = "UPDATE reserves SET priority = 0, found = 'W', itemnumber = $item
257 WHERE borrowernumber = $borr AND biblionumber = $q_biblio";
258 $sth = $dbh->prepare($query);
262 # now fix up the remaining priorities....
263 fixpriority($data->{'priority'}, $biblio);
264 my $branchcode = $data->{'branchcode'};
271 $borr = $dbh->quote($borr);
273 my $query = "SELECT * FROM reserves
274 WHERE borrowernumber = $borr
275 AND reserves.found = 'W'
276 AND cancellationdate is NULL";
277 my $sth = $dbh->prepare($query);
280 if (my $data=$sth->fetchrow_hashref) {
281 @itemswaiting[$cnt] =$data;
285 return ($cnt,\@itemswaiting);
288 sub Findgroupreserve {
289 my ($bibitem,$biblio)=@_;
291 $bibitem=$dbh->quote($bibitem);
292 my $query = "SELECT reserves.biblionumber AS biblionumber,
293 reserves.borrowernumber AS borrowernumber,
294 reserves.reservedate AS reservedate,
295 reserves.branchcode AS branchcode,
296 reserves.cancellationdate AS cancellationdate,
297 reserves.found AS found,
298 reserves.reservenotes AS reservenotes,
299 reserves.priority AS priority,
300 reserves.timestamp AS timestamp,
301 reserveconstraints.biblioitemnumber AS biblioitemnumber,
302 reserves.itemnumber AS itemnumber
303 FROM reserves LEFT JOIN reserveconstraints
304 ON reserves.biblionumber = reserveconstraints.biblionumber
305 WHERE reserves.biblionumber = $biblio
306 AND ( ( reserveconstraints.biblioitemnumber = $bibitem
307 AND reserves.borrowernumber = reserveconstraints.borrowernumber
308 AND reserves.reservedate =reserveconstraints.reservedate )
309 OR reserves.constrainttype='a' )
310 AND reserves.cancellationdate is NULL
311 AND (reserves.found <> 'F' or reserves.found is NULL)";
312 my $sth=$dbh->prepare($query);
316 while (my $data=$sth->fetchrow_hashref){
327 ($env,$branch,$borrnum,$biblionumber,$constraint,$bibitems,$priority,$notes,$title)= @_;
328 my $fee=CalcReserveFee($env,$borrnum,$biblionumber,$constraint,$bibitems);
329 my $dbh = &C4Connect;
330 my $const = lc substr($constraint,0,1);
331 my @datearr = localtime(time);
332 my $resdate =(1900+$datearr[5])."-".($datearr[4]+1)."-".$datearr[3];
334 # updates take place here
337 my $nextacctno = &getnextacctno($env,$borrnum,$dbh);
338 my $updquery = "insert into accountlines
339 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
341 ($borrnum,$nextacctno,now(),$fee,'Reserve Charge - $title','Res',$fee)";
342 my $usth = $dbh->prepare($updquery);
347 my $query="insert into reserves
348 (borrowernumber,biblionumber,reservedate,branchcode,constrainttype,priority,reservenotes)
350 ('$borrnum','$biblionumber','$resdate','$branch','$const','$priority','$notes')";
351 my $sth = $dbh->prepare($query);
355 if (($const eq "o") || ($const eq "e")) {
356 my $numitems = @$bibitems;
358 while ($i < $numitems) {
359 my $biblioitem = @$bibitems[$i];
360 my $query = "insert into
362 (borrowernumber,biblionumber,reservedate,biblioitemnumber)
364 ('$borrnum','$biblionumber','$resdate','$biblioitem')";
365 my $sth = $dbh->prepare($query);
377 my ($env,$borrnum,$biblionumber,$constraint,$bibitems) = @_;
379 my $dbh = &C4Connect;
380 my $const = lc substr($constraint,0,1);
381 my $query = "SELECT * FROM borrowers,categories
382 WHERE (borrowernumber = ?)
383 AND (borrowers.categorycode = categories.categorycode)";
384 my $sth = $dbh->prepare($query);
385 $sth->execute($borrnum);
386 my $data = $sth->fetchrow_hashref;
388 my $fee = $data->{'reservefee'};
389 my $cntitems = @->$bibitems;
391 # check for items on issue
392 # first find biblioitem records
394 my $query1 = "SELECT * FROM biblio,biblioitems
395 WHERE (biblio.biblionumber = ?)
396 AND (biblio.biblionumber = biblioitems.biblionumber)";
397 my $sth1 = $dbh->prepare($query1);
398 $sth1->execute($biblionumber);
399 while (my $data1=$sth1->fetchrow_hashref) {
401 push @biblioitems,$data1;
405 while ($x < $cntitems) {
406 if (@$bibitems->{'biblioitemnumber'} == $data->{'biblioitemnumber'}) {
413 push @biblioitems,$data1;
417 push @biblioitems,$data1;
423 my $cntitemsfound = @biblioitems;
427 while ($x < $cntitemsfound) {
428 my $bitdata = $biblioitems[$x];
429 my $query2 = "SELECT * FROM items
430 WHERE biblioitemnumber = ?";
431 my $sth2 = $dbh->prepare($query2);
432 $sth2->execute($bitdata->{'biblioitemnumber'});
433 while (my $itdata=$sth2->fetchrow_hashref) {
434 my $query3 = "SELECT * FROM issues
436 AND returndate IS NULL";
438 my $sth3 = $dbh->prepare($query3);
439 $sth3->execute($itdata->{'itemnumber'});
440 if (my $isdata=$sth3->fetchrow_hashref) {
447 if ($allissued == 0) {
448 my $rquery = "SELECT * FROM reserves WHERE biblionumber = ?";
449 my $rsth = $dbh->prepare($rquery);
450 $rsth->execute($biblionumber);
451 if (my $rdata = $rsth->fetchrow_hashref) {
463 my ($env,$bornumber,$dbh)=@_;
465 my $query = "select * from accountlines
466 where (borrowernumber = '$bornumber')
467 order by accountno desc";
468 my $sth = $dbh->prepare($query);
470 if (my $accdata=$sth->fetchrow_hashref){
471 $nextaccntno = $accdata->{'accountno'} + 1;
474 return($nextaccntno);
478 #subroutine to update a reserve
479 my ($rank,$biblio,$borrower,$del,$branch)=@_;
481 my $query="Update reserves ";
483 $query.="set priority='$rank',branchcode='$branch' where
484 biblionumber=$biblio and borrowernumber=$borrower";
486 $query="Select * from reserves where biblionumber=$biblio and
487 borrowernumber=$borrower";
488 my $sth=$dbh->prepare($query);
490 my $data=$sth->fetchrow_hashref;
492 $query="Select * from reserves where biblionumber=$biblio and
493 priority > '$data->{'priority'}' and cancellationdate is NULL
495 my $sth2=$dbh->prepare($query) || die $dbh->errstr;
496 $sth2->execute || die $sth2->errstr;
497 while (my $data=$sth2->fetchrow_hashref){
498 $data->{'priority'}--;
499 $query="Update reserves set priority=$data->{'priority'} where
500 biblionumber=$data->{'biblionumber'} and
501 borrowernumber=$data->{'borrowernumber'}";
502 my $sth3=$dbh->prepare($query);
503 $sth3->execute || die $sth3->errstr;
507 $query="update reserves set cancellationdate=now() where biblionumber=$biblio
508 and borrowernumber=$borrower";
510 my $sth=$dbh->prepare($query);
516 sub getreservetitle {
517 my ($biblio,$bor,$date,$timestamp)=@_;
519 my $query="Select * from reserveconstraints,biblioitems where
520 reserveconstraints.biblioitemnumber=biblioitems.biblioitemnumber
521 and reserveconstraints.biblionumber=$biblio and reserveconstraints.borrowernumber
522 = $bor and reserveconstraints.reservedate='$date' and
523 reserveconstraints.timestamp=$timestamp";
524 my $sth=$dbh->prepare($query);
526 my $data=$sth->fetchrow_hashref;
538 END { } # module clean-up code here (global destructor)