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";
68 my $sth=$dbh->prepare($query);
72 while (my $data=$sth->fetchrow_hashref){
73 if ($data->{'constrainttype'} eq 'o') {
74 my $conquery = "SELECT biblioitemnumber FROM reserveconstraints
75 WHERE biblionumber = ?
76 AND borrowernumber = ?
78 my $csth=$dbh->prepare($conquery);
79 my $bibn = $data->{'biblionumber'};
80 my $born = $data->{'borrowernumber'};
81 my $resd = $data->{'reservedate'};
82 $csth->execute($bibn, $born, $resd);
83 my ($bibitemno) = $csth->fetchrow_array;
85 my $bdata = C4::Search::bibitemdata($bibitemno);
86 foreach my $key (keys %$bdata) {
87 $data->{$key} = $bdata->{$key};
100 my ($item, $barcode) = @_;
101 # warn "In CheckReserves: itemnumber = $item";
105 my $qitem=$dbh->quote($item);
106 # get the biblionumber...
107 $sth=$dbh->prepare("SELECT items.biblionumber, items.biblioitemnumber, itemtypes.notforloan
108 FROM items, biblioitems, itemtypes
109 WHERE items.biblioitemnumber = biblioitems.biblioitemnumber
110 AND biblioitems.itemtype = itemtypes.itemtype
111 AND itemnumber=$qitem");
113 my $qbc=$dbh->quote($barcode);
114 # get the biblionumber...
115 $sth=$dbh->prepare("SELECT items.biblionumber, items.biblioitemnumber, itemtypes.notforloan
116 FROM items, biblioitems, itemtypes
117 WHERE items.biblioitemnumber = biblioitems.biblioitemnumber
118 AND biblioitems.itemtype = itemtypes.itemtype
122 my ($biblio, $bibitem, $notforloan) = $sth->fetchrow_array;
125 # if item is not for loan it cannot be reserved either.....
126 return (0, 0) if ($notforloan);
127 # get the reserves...
128 my ($count, @reserves) = Findgroupreserve($bibitem, $biblio);
129 my $priority = 10000000;
132 foreach my $res (@reserves) {
133 if ($res->{'itemnumber'} == $item) {
134 return ("Waiting", $res);
136 if ($res->{'priority'} != 0 && $res->{'priority'} < $priority) {
137 $priority = $res->{'priority'};
144 $highest->{'itemnumber'} = $item;
145 return ("Reserved", $highest);
152 my ($biblio, $item, $borr) = @_;
154 #warn "In CancelReserve";
155 if (($item and $borr) and (not $biblio)) {
156 # removing a waiting reserve record....
157 $item = $dbh->quote($item);
158 $borr = $dbh->quote($borr);
159 # update the database...
160 my $query = "update reserves set cancellationdate = now(),
163 where itemnumber = $item
164 and borrowernumber = $borr";
165 my $sth = $dbh->prepare($query);
169 if (($biblio and $borr) and (not $item)) {
170 # removing a reserve record....
171 my $q_biblio = $dbh->quote($biblio);
172 $borr = $dbh->quote($borr);
173 # get the prioritiy on this record....
174 my $query = "SELECT priority FROM reserves
175 WHERE biblionumber = $q_biblio
176 AND borrowernumber = $borr
177 AND cancellationdate is NULL
178 AND (found <> 'F' or found is NULL)";
179 my $sth=$dbh->prepare($query);
181 my ($priority) = $sth->fetchrow_array;
183 # update the database, removing the record...
184 my $query = "update reserves set cancellationdate = now(),
187 where biblionumber = $q_biblio
188 and borrowernumber = $borr
189 and cancellationdate is NULL
190 and (found <> 'F' or found is NULL)";
191 my $sth = $dbh->prepare($query);
194 # now fix the priority on the others....
195 fixpriority($priority, $biblio);
204 # fillinf a reserve record....
205 my $biblio = $res->{'biblionumber'}; my $qbiblio = $dbh->quote($biblio);
206 my $borr = $res->{'borrowernumber'}; $borr = $dbh->quote($borr);
207 my $resdate = $res->{'reservedate'}; $resdate = $dbh->quote($resdate);
208 # get the prioritiy on this record....
209 my $query = "SELECT priority FROM reserves
210 WHERE biblionumber = $qbiblio
211 AND borrowernumber = $borr
212 AND reservedate = $resdate)";
213 my $sth=$dbh->prepare($query);
215 my ($priority) = $sth->fetchrow_array;
217 # update the database...
218 my $query = "UPDATE reserves SET found = 'F',
220 WHERE biblionumber = $qbiblio
221 AND reservedate = $resdate
222 AND borrowernumber = $borr";
223 my $sth = $dbh->prepare($query);
226 # now fix the priority on the others (if the priority wasnt already sorted!)....
227 unless ($priority == 0) {
228 fixpriority($priority, $biblio);
234 my ($priority, $biblio) = @_;
236 my ($count, $reserves) = FindReserves($biblio);
237 foreach my $rec (@$reserves) {
238 if ($rec->{'priority'} > $priority) {
239 my $newpr = $rec->{'priority'}; $newpr = $dbh->quote($newpr - 1);
240 my $nbib = $rec->{'biblionumber'}; $nbib = $dbh->quote($nbib);
241 my $nbor = $rec->{'borrowernumber'}; $nbor = $dbh->quote($nbor);
242 my $nresd = $rec->{'reservedate'}; $nresd = $dbh->quote($nresd);
243 my $query = "UPDATE reserves SET priority = $newpr
244 WHERE biblionumber = $nbib
245 AND borrowernumber = $nbor
246 AND reservedate = $nresd";
248 my $sth = $dbh->prepare($query);
259 my ($item, $borr) = @_;
261 $item = $dbh->quote($item);
262 $borr = $dbh->quote($borr);
263 # get priority and biblionumber....
264 my $query = "SELECT reserves.priority as priority,
265 reserves.biblionumber as biblionumber,
266 reserves.branchcode as branchcode
268 WHERE reserves.biblionumber = items.biblionumber
269 AND items.itemnumber = $item
270 AND reserves.borrowernumber = $borr
271 AND reserves.cancellationdate is NULL
272 AND (reserves.found <> 'F' or reserves.found is NULL)";
273 my $sth = $dbh->prepare($query);
275 my $data = $sth->fetchrow_hashref;
277 my $biblio = $data->{'biblionumber'};
278 my $timestamp = $data->{'timestamp'};
279 my $q_biblio = $dbh->quote($biblio);
280 my $q_timestamp = $dbh->quote($biblio);
281 # update reserves record....
282 $query = "UPDATE reserves SET priority = 0, found = 'W', itemnumber = $item
283 WHERE borrowernumber = $borr
284 AND biblionumber = $q_biblio
285 AND timestamp = $q_timestamp";
286 $sth = $dbh->prepare($query);
290 # now fix up the remaining priorities....
291 fixpriority($data->{'priority'}, $biblio);
292 my $branchcode = $data->{'branchcode'};
299 $borr = $dbh->quote($borr);
301 my $query = "SELECT * FROM reserves
302 WHERE borrowernumber = $borr
303 AND reserves.found = 'W'
304 AND cancellationdate is NULL";
305 my $sth = $dbh->prepare($query);
308 if (my $data=$sth->fetchrow_hashref) {
309 @itemswaiting[$cnt] =$data;
313 return ($cnt,\@itemswaiting);
316 sub Findgroupreserve {
317 my ($bibitem,$biblio)=@_;
319 $bibitem=$dbh->quote($bibitem);
320 my $query = "SELECT reserves.biblionumber AS biblionumber,
321 reserves.borrowernumber AS borrowernumber,
322 reserves.reservedate AS reservedate,
323 reserves.branchcode AS branchcode,
324 reserves.cancellationdate AS cancellationdate,
325 reserves.found AS found,
326 reserves.reservenotes AS reservenotes,
327 reserves.priority AS priority,
328 reserves.timestamp AS timestamp,
329 reserveconstraints.biblioitemnumber AS biblioitemnumber,
330 reserves.itemnumber AS itemnumber
331 FROM reserves LEFT JOIN reserveconstraints
332 ON reserves.biblionumber = reserveconstraints.biblionumber
333 WHERE reserves.biblionumber = $biblio
334 AND ( ( reserveconstraints.biblioitemnumber = $bibitem
335 AND reserves.borrowernumber = reserveconstraints.borrowernumber
336 AND reserves.reservedate =reserveconstraints.reservedate )
337 OR reserves.constrainttype='a' )
338 AND reserves.cancellationdate is NULL
339 AND (reserves.found <> 'F' or reserves.found is NULL)";
340 my $sth=$dbh->prepare($query);
344 while (my $data=$sth->fetchrow_hashref){
355 ($env,$branch,$borrnum,$biblionumber,$constraint,$bibitems,$priority,$notes,$title)= @_;
356 my $fee=CalcReserveFee($env,$borrnum,$biblionumber,$constraint,$bibitems);
357 my $dbh = &C4Connect;
358 my $const = lc substr($constraint,0,1);
359 my @datearr = localtime(time);
360 my $resdate =(1900+$datearr[5])."-".($datearr[4]+1)."-".$datearr[3];
362 # updates take place here
365 my $nextacctno = &getnextacctno($env,$borrnum,$dbh);
366 my $updquery = "insert into accountlines
367 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
369 ($borrnum,$nextacctno,now(),$fee,'Reserve Charge - $title','Res',$fee)";
370 my $usth = $dbh->prepare($updquery);
375 my $query="insert into reserves
376 (borrowernumber,biblionumber,reservedate,branchcode,constrainttype,priority,reservenotes)
378 ('$borrnum','$biblionumber','$resdate','$branch','$const','$priority','$notes')";
379 my $sth = $dbh->prepare($query);
383 if (($const eq "o") || ($const eq "e")) {
384 my $numitems = @$bibitems;
386 while ($i < $numitems) {
387 my $biblioitem = @$bibitems[$i];
388 my $query = "insert into
390 (borrowernumber,biblionumber,reservedate,biblioitemnumber)
392 ('$borrnum','$biblionumber','$resdate','$biblioitem')";
393 my $sth = $dbh->prepare($query);
405 my ($env,$borrnum,$biblionumber,$constraint,$bibitems) = @_;
407 my $dbh = &C4Connect;
408 my $const = lc substr($constraint,0,1);
409 my $query = "SELECT * FROM borrowers,categories
410 WHERE (borrowernumber = ?)
411 AND (borrowers.categorycode = categories.categorycode)";
412 my $sth = $dbh->prepare($query);
413 $sth->execute($borrnum);
414 my $data = $sth->fetchrow_hashref;
416 my $fee = $data->{'reservefee'};
417 my $cntitems = @->$bibitems;
419 # check for items on issue
420 # first find biblioitem records
422 my $query1 = "SELECT * FROM biblio,biblioitems
423 WHERE (biblio.biblionumber = ?)
424 AND (biblio.biblionumber = biblioitems.biblionumber)";
425 my $sth1 = $dbh->prepare($query1);
426 $sth1->execute($biblionumber);
427 while (my $data1=$sth1->fetchrow_hashref) {
429 push @biblioitems,$data1;
433 while ($x < $cntitems) {
434 if (@$bibitems->{'biblioitemnumber'} == $data->{'biblioitemnumber'}) {
441 push @biblioitems,$data1;
445 push @biblioitems,$data1;
451 my $cntitemsfound = @biblioitems;
455 while ($x < $cntitemsfound) {
456 my $bitdata = $biblioitems[$x];
457 my $query2 = "SELECT * FROM items
458 WHERE biblioitemnumber = ?";
459 my $sth2 = $dbh->prepare($query2);
460 $sth2->execute($bitdata->{'biblioitemnumber'});
461 while (my $itdata=$sth2->fetchrow_hashref) {
462 my $query3 = "SELECT * FROM issues
464 AND returndate IS NULL";
466 my $sth3 = $dbh->prepare($query3);
467 $sth3->execute($itdata->{'itemnumber'});
468 if (my $isdata=$sth3->fetchrow_hashref) {
475 if ($allissued == 0) {
476 my $rquery = "SELECT * FROM reserves WHERE biblionumber = ?";
477 my $rsth = $dbh->prepare($rquery);
478 $rsth->execute($biblionumber);
479 if (my $rdata = $rsth->fetchrow_hashref) {
491 my ($env,$bornumber,$dbh)=@_;
493 my $query = "select * from accountlines
494 where (borrowernumber = '$bornumber')
495 order by accountno desc";
496 my $sth = $dbh->prepare($query);
498 if (my $accdata=$sth->fetchrow_hashref){
499 $nextaccntno = $accdata->{'accountno'} + 1;
502 return($nextaccntno);
506 #subroutine to update a reserve
507 my ($rank,$biblio,$borrower,$del,$branch)=@_;
509 my $query="Update reserves ";
511 $query.="set priority='$rank',branchcode='$branch' where
512 biblionumber=$biblio and borrowernumber=$borrower";
514 $query="Select * from reserves where biblionumber=$biblio and
515 borrowernumber=$borrower";
516 my $sth=$dbh->prepare($query);
518 my $data=$sth->fetchrow_hashref;
520 $query="Select * from reserves where biblionumber=$biblio and
521 priority > '$data->{'priority'}' and cancellationdate is NULL
523 my $sth2=$dbh->prepare($query) || die $dbh->errstr;
524 $sth2->execute || die $sth2->errstr;
525 while (my $data=$sth2->fetchrow_hashref){
526 $data->{'priority'}--;
527 $query="Update reserves set priority=$data->{'priority'} where
528 biblionumber=$data->{'biblionumber'} and
529 borrowernumber=$data->{'borrowernumber'}";
530 my $sth3=$dbh->prepare($query);
531 $sth3->execute || die $sth3->errstr;
535 $query="update reserves set cancellationdate=now() where biblionumber=$biblio
536 and borrowernumber=$borrower";
538 my $sth=$dbh->prepare($query);
544 sub getreservetitle {
545 my ($biblio,$bor,$date,$timestamp)=@_;
547 my $query="Select * from reserveconstraints,biblioitems where
548 reserveconstraints.biblioitemnumber=biblioitems.biblioitemnumber
549 and reserveconstraints.biblionumber=$biblio and reserveconstraints.borrowernumber
550 = $bor and reserveconstraints.reservedate='$date' and
551 reserveconstraints.timestamp=$timestamp";
552 my $sth=$dbh->prepare($query);
554 my $data=$sth->fetchrow_hashref;
566 END { } # module clean-up code here (global destructor)