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
27 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
29 # set the version for version checking
33 @EXPORT = qw(&FindReserves &CheckReserves &CheckWaiting &CancelReserve &FillReserve &ReserveWaiting &CreateReserve &updatereserves &getreservetitle &Findgroupreserve);
35 # make all your functions, whether exported or not;
40 my $query="SELECT *,reserves.branchcode,biblio.title AS btitle
41 FROM reserves,borrowers,biblio ";
43 $bib = $dbh->quote($bib);
45 $bor = $dbh->quote($bor);
46 $query .= " where reserves.biblionumber = $bib
47 and borrowers.borrowernumber = $bor
48 and reserves.borrowernumber = borrowers.borrowernumber
49 and biblio.biblionumber = $bib
50 and cancellationdate is NULL
51 and (found <> 'F' or found is NULL)";
53 $query .= " where reserves.borrowernumber = borrowers.borrowernumber
54 and biblio.biblionumber = $bib
55 and reserves.biblionumber = $bib
56 and cancellationdate is NULL
57 and (found <> 'F' or found is NULL)";
60 $query .= " where borrowers.borrowernumber = $bor
61 and reserves.borrowernumber = borrowers.borrowernumber
62 and reserves.biblionumber = biblio.biblionumber
63 and cancellationdate is NULL and
64 (found <> 'F' or found is NULL)";
66 $query.=" order by priority";
67 my $sth=$dbh->prepare($query);
71 while (my $data=$sth->fetchrow_hashref){
84 my $qitem=$dbh->quote($item);
85 # get the biblionumber...
86 my $sth=$dbh->prepare("select biblionumber, biblioitemnumber from items where itemnumber=$qitem");
88 my ($biblio, $bibitem) = $sth->fetchrow_array;
92 my ($count, @reserves) = Findgroupreserve($bibitem, $biblio);
93 my $priority = 10000000;
96 foreach my $res (@reserves) {
97 if ($res->{'itemnumber'} == $item) {
98 return ("Waiting", $res);
100 if ($res->{'priority'} < $priority) {
101 $priority = $res->{'priority'};
106 $highest->{'itemnumber'} = $item;
107 return ("Reserved", $highest);
114 my ($biblio, $item, $borr) = @_;
116 warn "In CancelReserve";
117 if (($item and $borr) and (not $biblio)) {
118 # removing a waiting reserve record....
119 $item = $dbh->quote($item);
120 $borr = $dbh->quote($borr);
121 # update the database...
122 my $query = "update reserves set cancellationdate = now(),
125 where itemnumber = $item
126 and borrowernumber = $borr";
127 my $sth = $dbh->prepare($query);
131 if (($biblio and $borr) and (not $item)) {
132 # removing a reserve record....
133 my $q_biblio = $dbh->quote($biblio);
134 $borr = $dbh->quote($borr);
135 # fix up the priorities on the other records....
136 my $query = "SELECT priority FROM reserves
137 WHERE biblionumber = $q_biblio
138 AND borrowernumber = $borr
139 AND cancellationdate is NULL
140 AND (found <> 'F' or found is NULL)";
141 my $sth=$dbh->prepare($query);
143 my ($priority) = $sth->fetchrow_array;
145 # update the database, removing the record...
146 my $query = "update reserves set cancellationdate = now(),
149 where biblionumber = $q_biblio
150 and borrowernumber = $borr
151 and cancellationdate is NULL
152 and (found <> 'F' or found is NULL)";
153 my $sth = $dbh->prepare($query);
156 # now fix the priority on the others....
157 fixpriority($priority, $biblio);
166 # removing a waiting reserve record....
167 my $biblio = $res->{'biblionumber'}; my $qbiblio = $dbh->quote($biblio);
168 my $borr = $res->{'borrowernumber'}; $borr = $dbh->quote($borr);
169 my $resdate = $res->{'reservedate'}; $resdate = $dbh->quote($resdate);
170 # update the database...
171 my $query = "UPDATE reserves SET found = 'F',
173 WHERE biblionumber = $qbiblio
174 AND reservedate = $resdate
175 AND borrowernumber = $borr";
176 my $sth = $dbh->prepare($query);
180 # now fix the priority on the others....
181 fixpriority($res->{'priority'}, $biblio);
185 my ($priority, $biblio) = @_;
187 my ($count, $reserves) = FindReserves($biblio);
188 foreach my $rec (@$reserves) {
189 if ($rec->{'priority'} > $priority) {
190 my $newpr = $rec->{'priority'}; $newpr = $dbh->quote($newpr - 1);
191 my $nbib = $rec->{'biblionumber'}; $nbib = $dbh->quote($nbib);
192 my $nbor = $rec->{'borrowernumber'}; $nbor = $dbh->quote($nbor);
193 my $nresd = $rec->{'reservedate'}; $nresd = $dbh->quote($nresd);
194 my $query = "UPDATE reserves SET priority = $newpr
195 WHERE biblionumber = $nbib
196 AND borrowernumber = $nbor
197 AND reservedate = $nresd";
199 my $sth = $dbh->prepare($query);
210 my ($item, $borr) = @_;
212 $item = $dbh->quote($item);
213 $borr = $dbh->quote($borr);
214 # get priority and biblionumber....
215 my $query = "SELECT reserves.priority as priority,
216 reserves.biblionumber as biblionumber,
217 reserves.branchcode as branchcode
219 WHERE reserves.biblionumber = items.biblionumber
220 AND items.itemnumber = $item
221 AND reserves.borrowernumber = $borr
222 AND reserves.cancellationdate is NULL
223 AND (reserves.found <> 'F' or reserves.found is NULL)";
224 my $sth = $dbh->prepare($query);
226 my $data = $sth->fetchrow_hashref;
228 my $biblio = $data->{'biblionumber'};
229 my $q_biblio = $dbh->quote($biblio);
230 # update reserves record....
231 $query = "UPDATE reserves SET priority = 0, found = 'W', itemnumber = $item
232 WHERE borrowernumber = $borr AND biblionumber = $q_biblio";
233 $sth = $dbh->prepare($query);
237 # now fix up the remaining priorities....
238 fixpriority($data->{'priority'}, $biblio);
239 my $branchcode = $data->{'branchcode'};
246 $borr = $dbh->quote($borr);
248 my $query = "SELECT * FROM reserves
249 WHERE borrowernumber = $borr
250 AND reserves.found = 'W'
251 AND cancellationdate is NULL";
252 my $sth = $dbh->prepare($query);
255 if (my $data=$sth->fetchrow_hashref) {
256 @itemswaiting[$cnt] =$data;
260 return ($cnt,\@itemswaiting);
263 sub Findgroupreserve {
264 my ($bibitem,$biblio)=@_;
266 $bibitem=$dbh->quote($bibitem);
267 my $query = "SELECT reserves.biblionumber AS biblionumber,
268 reserves.borrowernumber AS borrowernumber,
269 reserves.reservedate AS reservedate,
270 reserves.branchcode AS branchcode,
271 reserves.cancellationdate AS cancellationdate,
272 reserves.found AS found,
273 reserves.reservenotes AS reservenotes,
274 reserves.priority AS priority,
275 reserves.timestamp AS timestamp,
276 reserveconstraints.biblioitemnumber AS biblioitemnumber,
277 reserves.itemnumber AS itemnumber
278 FROM reserves LEFT JOIN reserveconstraints
279 ON reserves.biblionumber = reserveconstraints.biblionumber
280 WHERE reserves.biblionumber = $biblio
281 AND ( ( reserveconstraints.biblioitemnumber = $bibitem
282 AND reserves.borrowernumber = reserveconstraints.borrowernumber
283 AND reserves.reservedate =reserveconstraints.reservedate )
284 OR reserves.constrainttype='a' )
285 AND reserves.cancellationdate is NULL
286 AND (reserves.found <> 'F' or reserves.found is NULL)";
287 my $sth=$dbh->prepare($query);
291 while (my $data=$sth->fetchrow_hashref){
302 ($env,$branch,$borrnum,$biblionumber,$constraint,$bibitems,$priority,$notes,$title)= @_;
303 my $fee=CalcReserveFee($env,$borrnum,$biblionumber,$constraint,$bibitems);
304 my $dbh = &C4Connect;
305 my $const = lc substr($constraint,0,1);
306 my @datearr = localtime(time);
307 my $resdate =(1900+$datearr[5])."-".($datearr[4]+1)."-".$datearr[3];
309 # updates take place here
312 my $nextacctno = &getnextacctno($env,$borrnum,$dbh);
313 my $updquery = "insert into accountlines
314 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
316 ($borrnum,$nextacctno,now(),$fee,'Reserve Charge - $title','Res',$fee)";
317 my $usth = $dbh->prepare($updquery);
322 my $query="insert into reserves
323 (borrowernumber,biblionumber,reservedate,branchcode,constrainttype,priority,reservenotes)
325 ('$borrnum','$biblionumber','$resdate','$branch','$const','$priority','$notes')";
326 my $sth = $dbh->prepare($query);
330 if (($const eq "o") || ($const eq "e")) {
331 my $numitems = @$bibitems;
333 while ($i < $numitems) {
334 my $biblioitem = @$bibitems[$i];
335 my $query = "insert into
337 (borrowernumber,biblionumber,reservedate,biblioitemnumber)
339 ('$borrnum','$biblionumber','$resdate','$biblioitem')";
340 my $sth = $dbh->prepare($query);
352 my ($env,$borrnum,$biblionumber,$constraint,$bibitems) = @_;
354 my $dbh = &C4Connect;
355 my $const = lc substr($constraint,0,1);
356 my $query = "select * from borrowers,categories
357 where (borrowernumber = '$borrnum')
358 and (borrowers.categorycode = categories.categorycode)";
359 my $sth = $dbh->prepare($query);
361 my $data = $sth->fetchrow_hashref;
363 my $fee = $data->{'reservefee'};
364 my $cntitems = @->$bibitems;
366 # check for items on issue
367 # first find biblioitem records
369 my $query1 = "select * from biblio,biblioitems
370 where (biblio.biblionumber = '$biblionumber')
371 and (biblio.biblionumber = biblioitems.biblionumber)";
372 my $sth1 = $dbh->prepare($query1);
374 while (my $data1=$sth1->fetchrow_hashref) {
376 push @biblioitems,$data1;
380 while ($x < $cntitems) {
381 if (@$bibitems->{'biblioitemnumber'} == $data->{'biblioitemnumber'}) {
388 push @biblioitems,$data1;
392 push @biblioitems,$data1;
398 my $cntitemsfound = @biblioitems;
402 while ($x < $cntitemsfound) {
403 my $bitdata = $biblioitems[$x];
404 my $query2 = "select * from items
405 where biblioitemnumber = '$bitdata->{'biblioitemnumber'}'";
406 my $sth2 = $dbh->prepare($query2);
408 while (my $itdata=$sth2->fetchrow_hashref) {
409 my $query3 = "select * from issues
410 where itemnumber = '$itdata->{'itemnumber'}' and
413 my $sth3 = $dbh->prepare($query3);
415 if (my $isdata=$sth3->fetchrow_hashref) {
422 if ($allissued == 0) {
423 my $rquery = "select * from reserves
424 where biblionumber = '$biblionumber'";
425 my $rsth = $dbh->prepare($rquery);
427 if (my $rdata = $rsth->fetchrow_hashref) {
439 my ($env,$bornumber,$dbh)=@_;
441 my $query = "select * from accountlines
442 where (borrowernumber = '$bornumber')
443 order by accountno desc";
444 my $sth = $dbh->prepare($query);
446 if (my $accdata=$sth->fetchrow_hashref){
447 $nextaccntno = $accdata->{'accountno'} + 1;
450 return($nextaccntno);
454 #subroutine to update a reserve
455 my ($rank,$biblio,$borrower,$del,$branch)=@_;
457 my $query="Update reserves ";
459 $query.="set priority='$rank',branchcode='$branch' where
460 biblionumber=$biblio and borrowernumber=$borrower";
462 $query="Select * from reserves where biblionumber=$biblio and
463 borrowernumber=$borrower";
464 my $sth=$dbh->prepare($query);
466 my $data=$sth->fetchrow_hashref;
468 $query="Select * from reserves where biblionumber=$biblio and
469 priority > '$data->{'priority'}' and cancellationdate is NULL
471 my $sth2=$dbh->prepare($query) || die $dbh->errstr;
472 $sth2->execute || die $sth2->errstr;
473 while (my $data=$sth2->fetchrow_hashref){
474 $data->{'priority'}--;
475 $query="Update reserves set priority=$data->{'priority'} where
476 biblionumber=$data->{'biblionumber'} and
477 borrowernumber=$data->{'borrowernumber'}";
478 my $sth3=$dbh->prepare($query);
479 $sth3->execute || die $sth3->errstr;
483 $query="update reserves set cancellationdate=now() where biblionumber=$biblio
484 and borrowernumber=$borrower";
486 my $sth=$dbh->prepare($query);
492 sub getreservetitle {
493 my ($biblio,$bor,$date,$timestamp)=@_;
495 my $query="Select * from reserveconstraints,biblioitems where
496 reserveconstraints.biblioitemnumber=biblioitems.biblioitemnumber
497 and reserveconstraints.biblionumber=$biblio and reserveconstraints.borrowernumber
498 = $bor and reserveconstraints.reservedate='$date' and
499 reserveconstraints.timestamp=$timestamp";
500 my $sth=$dbh->prepare($query);
502 my $data=$sth->fetchrow_hashref;
514 END { } # module clean-up code here (global destructor)