From 7f1072e28ffbfe1a6d00cbaa29f8a1ba5ffc2ca0 Mon Sep 17 00:00:00 2001 From: slef Date: Mon, 8 Dec 2003 14:24:55 +0000 Subject: [PATCH] DBI call fix for bug 662 --- C4/Circulation/Main.pm | 66 +++++++---------- C4/Circulation/Renewals2.pm | 57 +++++++------- C4/Circulation/Returns.pm | 143 ++++++++++++++++-------------------- 3 files changed, 114 insertions(+), 152 deletions(-) diff --git a/C4/Circulation/Main.pm b/C4/Circulation/Main.pm index e1b40f08c6..25a5844b27 100755 --- a/C4/Circulation/Main.pm +++ b/C4/Circulation/Main.pm @@ -78,8 +78,7 @@ primarily for checking reserves and overdue items. sub getbranch { my ($env) = @_; my $dbh = C4::Context->dbh; - my $query = "select * from branches order by branchcode"; - my $sth = $dbh->prepare($query); + my $sth = $dbh->prepare("select * from branches order by branchcode"); $sth->execute; if ($sth->rows>1) { my @branches; @@ -91,10 +90,9 @@ sub getbranch { my $data = $sth->fetchrow_hashref; $env->{'branchcode'}=$data->{'branchcode'}; } - my $query = "select * from branches - where branchcode = '$env->{'branchcode'}'"; - $sth = $dbh->prepare($query); - $sth->execute; + $sth = $dbh->prepare("select * from branches + where branchcode = ?"); + $sth->execute($env->{'branchcode'}); my $data = $sth->fetchrow_hashref; $env->{'brdata'} = $data; $env->{'branchname'} = $data->{'branchname'}; @@ -108,8 +106,7 @@ sub getbranch { sub getprinter { my ($env) = @_; my $dbh = C4::Context->dbh; - my $query = "select * from printers order by printername"; - my $sth = $dbh->prepare($query); + my $sth = $dbh->prepare("select * from printers order by printername"); $sth->execute; if ($sth->rows>1) { my @printers; @@ -134,10 +131,9 @@ sub getprinter { sub pastitems{ #Get list of all items borrower has currently on issue my ($env,$bornum,$dbh)=@_; - my $query1 = "select * from issues where (borrowernumber=$bornum) - and (returndate is null) order by date_due"; - my $sth=$dbh->prepare($query1); - $sth->execute; + my $sth=$dbh->prepare("select * from issues where (borrowernumber=?) + and (returndate is null) order by date_due"); + $sth->execute($bornum); my $i=0; my @items; my @items2; @@ -171,16 +167,9 @@ sub checkoverdues{ #checks whether a borrower has overdue items # FIXME - Use C4::Context->dbh instead of getting $dbh as an argument my ($env,$bornum,$dbh)=@_; - # FIXME - This is what POSIX::strftime is for. - my @datearr = localtime; - my $today = ($datearr[5] + 1900)."-".($datearr[4]+1)."-".$datearr[3]; - # FIXME - MySQL can figure out what today is, so there's no need - # to calculate that separately. Just use - # ... date_due < curdate() - my $query = "Select count(*) from issues where borrowernumber=$bornum and - returndate is NULL and date_due < '$today'"; - my $sth=$dbh->prepare($query); - $sth->execute; + my $sth=$dbh->prepare("Select count(*) from issues where borrowernumber=? and + returndate is NULL and date_due < curdate()"); + $sth->execute($bornum); my $data = $sth->fetchrow_hashref; $sth->finish; return $data->{'count(*)'}; @@ -272,15 +261,14 @@ sub checkreserve{ # Find this item in the reserves. # Apparently reserves.found=='W' means "Waiting". # FIXME - Is it necessary to get every field from both tables? - my $query = "select * from reserves,items - where (items.itemnumber = '$itemnum') + my $sth = $dbh->prepare("select * from reserves,items + where (items.itemnumber = ?) and (reserves.cancellationdate is NULL) and (items.biblionumber = reserves.biblionumber) and ((reserves.found = 'W') or (reserves.found is null)) - order by priority"; - my $sth = $dbh->prepare($query); - $sth->execute(); + order by priority"); + $sth->execute($itemnum); my $resrec; if (my $data=$sth->fetchrow_hashref) { $resrec=$data; @@ -289,14 +277,13 @@ sub checkreserve{ $resbor = $data->{'borrowernumber'}; } else { my $found = 0; - my $cquery = "select * from reserveconstraints,items - where (borrowernumber='$data->{'borrowernumber'}') - and reservedate='$data->{'reservedate'}' - and reserveconstraints.biblionumber='$data->{'biblionumber'}' - and (items.itemnumber=$itemnum and - items.biblioitemnumber = reserveconstraints.biblioitemnumber)"; - my $csth = $dbh->prepare($cquery); - $csth->execute; + my $csth = $dbh->prepare("select * from reserveconstraints,items + where (borrowernumber=?) + and reservedate=? + and reserveconstraints.biblionumber=? + and (items.itemnumber=? and + items.biblioitemnumber = reserveconstraints.biblioitemnumber)"); + $csth->execute($data->{'borrowernumber'},$data->{'reservedate'},$data->{'biblionumber'},$itemnum); if (my $cdata=$csth->fetchrow_hashref) {$found = 1;} if ($const eq 'o') { # FIXME - What does 'o' mean? if ($found eq 1) {$resbor = $data->{'borrowernumber'};} @@ -317,11 +304,10 @@ sub checkwaiting{ # check for reserves waiting my ($env,$dbh,$bornum)=@_; my @itemswaiting; - my $query = "select * from reserves - where (borrowernumber = '$bornum') - and (reserves.found='W') and cancellationdate is NULL"; - my $sth = $dbh->prepare($query); - $sth->execute(); + my $sth = $dbh->prepare("select * from reserves + where (borrowernumber = ?) + and (reserves.found='W') and cancellationdate is NULL"); + $sth->execute($bornum); my $cnt=0; if (my $data=$sth->fetchrow_hashref) { @itemswaiting[$cnt] =$data; diff --git a/C4/Circulation/Renewals2.pm b/C4/Circulation/Renewals2.pm index f9c1e05fcd..0076fa8110 100755 --- a/C4/Circulation/Renewals2.pm +++ b/C4/Circulation/Renewals2.pm @@ -98,12 +98,11 @@ sub renewstatus { # FIXME - I think this function could be redone to use only one SQL # call. - my $q1 = "select * from issues - where (borrowernumber = '$bornum') - and (itemnumber = '$itemno') - and returndate is null"; - my $sth1 = $dbh->prepare($q1); - $sth1->execute; + my $sth1 = $dbh->prepare("select * from issues + where (borrowernumber = ?) + and (itemnumber = ?') + and returndate is null"); + $sth1->execute($bornum,$itemno); if (my $data1 = $sth1->fetchrow_hashref) { # Found a matching item @@ -111,12 +110,11 @@ sub renewstatus { # because it's a bit messy: given the item number, we need to find # the biblioitem, which gives us the itemtype, which tells us # whether it may be renewed. - my $q2 = "select renewalsallowed from items,biblioitems,itemtypes - where (items.itemnumber = '$itemno') + my $sth2 = $dbh->prepare("select renewalsallowed from items,biblioitems,itemtypes + where (items.itemnumber = ?) and (items.biblioitemnumber = biblioitems.biblioitemnumber) - and (biblioitems.itemtype = itemtypes.itemtype)"; - my $sth2 = $dbh->prepare($q2); - $sth2->execute; + and (biblioitems.itemtype = itemtypes.itemtype)"); + $sth2->execute($itemno); if (my $data2=$sth2->fetchrow_hashref) { $renews = $data2->{'renewalsallowed'}; } @@ -174,12 +172,11 @@ sub renewbook { # type or whatever, then that should # be an error # Find this item's item type, via its biblioitem. - my $query= "Select * from biblioitems,items,itemtypes - where (items.itemnumber = '$itemno') + my $sth=$dbh->prepare("Select * from biblioitems,items,itemtypes + where (items.itemnumber = ?) and (biblioitems.biblioitemnumber = items.biblioitemnumber) - and (biblioitems.itemtype = itemtypes.itemtype)"; - my $sth=$dbh->prepare($query); - $sth->execute; + and (biblioitems.itemtype = itemtypes.itemtype)"); + $sth->execute($itemno); if (my $data=$sth->fetchrow_hashref) { $loanlength = $data->{'loanlength'} } @@ -193,10 +190,9 @@ sub renewbook { } # Find the issues record for this book - my $issquery = "select * from issues where borrowernumber='$bornum' and - itemnumber='$itemno' and returndate is null"; - my $sth=$dbh->prepare($issquery); - $sth->execute; + my $sth=$dbh->prepare("select * from issues where borrowernumber=? and + itemnumber=? and returndate is null"); + $sth->execute($bornum,$itemno); my $issuedata=$sth->fetchrow_hashref; # FIXME - Error-checking $sth->finish; @@ -204,13 +200,11 @@ sub renewbook { # Update the issues record to have the new due date, and a new count # of how many times it has been renewed. my $renews = $issuedata->{'renewals'} +1; - my $updquery = "update issues - set date_due = '$datedue', renewals = '$renews' - where borrowernumber='$bornum' and - itemnumber='$itemno' and returndate is null"; - # FIXME - Use $dbh->do() - $sth=$dbh->prepare($updquery); - $sth->execute; + $sth=$dbh->prepare("update issues + set date_due = ?, renewals = ? + where borrowernumber=? and + itemnumber=? and returndate is null"); + $sth->execute($datedue,$renews,$bornum,$itemno); $sth->finish; # Log the renewal @@ -273,13 +267,12 @@ sub calc_charges { $charge = $data1->{'rentalcharge'}; # Figure out the applicable rental discount - my $q2 = "select rentaldiscount from + my $sth2=$dbh->prepare("select rentaldiscount from borrowers,categoryitem - where (borrowers.borrowernumber = '$bornum') + where (borrowers.borrowernumber = ?) and (borrowers.categorycode = categoryitem.categorycode) - and (categoryitem.itemtype = '$item_type')"; - my $sth2=$dbh->prepare($q2); - $sth2->execute; + and (categoryitem.itemtype = ?)"); + $sth2->execute($bornum,$item_type); if (my$data2=$sth2->fetchrow_hashref) { my $discount = $data2->{'rentaldiscount'}; $charge *= (100 - $discount) / 100; diff --git a/C4/Circulation/Returns.pm b/C4/Circulation/Returns.pm index b5fe014c10..1379abea12 100755 --- a/C4/Circulation/Returns.pm +++ b/C4/Circulation/Returns.pm @@ -125,25 +125,22 @@ sub checkissue { my $itemrec; my $amt_owing; $item = uc $item; - my $query = "select * from items,biblio - where barcode = '$item' - and (biblio.biblionumber=items.biblionumber)"; - my $sth=$dbh->prepare($query); - $sth->execute; + my $sth=$dbh->prepare("select * from items,biblio + where barcode = ? + and (biblio.biblionumber=items.biblionumber)"); + $sth->execute($item); if ($itemrec=$sth->fetchrow_hashref) { $sth->finish; $itemno = $itemrec->{'itemnumber'}; - $query = "select * from issues - where (itemnumber='$itemrec->{'itemnumber'}') - and (returndate is null)"; - my $sth=$dbh->prepare($query); - $sth->execute; + my $sth=$dbh->prepare("select * from issues + where (itemnumber=?) + and (returndate is null)"); + $sth->execute($itemrec->{'itemnumber'}); if (my $issuerec=$sth->fetchrow_hashref) { $sth->finish; - $query = "select * from borrowers where - (borrowernumber = '$issuerec->{'borrowernumber'}')"; - my $sth= $dbh->prepare($query); - $sth->execute; + my $sth= $dbh->prepare("select * from borrowers where + (borrowernumber = ?)"); + $sth->execute($issuerec->{'borrowernumber'}); $env->{'bornum'}=$issuerec->{'borrowernumber'}; $borrower = $sth->fetchrow_hashref; $bornum = $issuerec->{'borrowernumber'}; @@ -157,10 +154,9 @@ sub checkissue { } my ($resfound,$resrec) = find_reserves($env,$dbh,$itemrec->{'itemnumber'}); if ($resfound eq "y") { - my $bquery = "select * from borrowers - where borrowernumber = '$resrec->{'borrowernumber'}'"; - my $btsh = $dbh->prepare($bquery); - $btsh->execute; + my $btsh = $dbh->prepare("select * from borrowers + where borrowernumber = ?"); + $btsh->execute($resrec->{'borrowernumber'}); my $resborrower = $btsh->fetchrow_hashref; #printreserve($env,$resrec,$resborrower,$itemrec); my $mess = "Reserved for collection at branch $resrec->{'branchcode'}"; @@ -185,41 +181,37 @@ sub returnrecord { #my $amt_owing = calc_odues($env,$dbh,$bornum,$itemno); my @datearr = localtime(time); my $dateret = (1900+$datearr[5])."-".$datearr[4]."-".$datearr[3]; - my $query = "update issues set returndate = now(), branchcode ='$env->{'branchcode'}' where - (borrowernumber = '$bornum') and (itemnumber = '$itemno') - and (returndate is null)"; - my $sth = $dbh->prepare($query); - $sth->execute; + my $sth = $dbh->prepare("update issues set returndate = now(), branchcode = ? where + (borrowernumber = ?) and (itemnumber = ?) + and (returndate is null)"); + $sth->execute($env->{'branchcode'},$bornum,$itemno); $sth->finish; updatelastseen($env,$dbh,$itemno); # check for overdue fine my $oduecharge; - my $query = "select * from accountlines - where (borrowernumber = '$bornum') - and (itemnumber = '$itemno') - and (accounttype = 'FU' or accounttype='O')"; - my $sth = $dbh->prepare($query); - $sth->execute; + my $sth = $dbh->prepare("select * from accountlines + where (borrowernumber = ?) + and (itemnumber = ?) + and (accounttype = 'FU' or accounttype='O')"); + $sth->execute($bornum,$itemno); if (my $data = $sth->fetchrow_hashref) { # alter fine to show that the book has been returned. - my $uquery = "update accountlines + my $usth = $dbh->prepare("update accountlines set accounttype = 'F' - where (borrowernumber = '$bornum') - and (itemnumber = '$itemno') - and (accountno = '$data->{'accountno'}') "; - my $usth = $dbh->prepare($uquery); - $usth->execute(); + where (borrowernumber = ?) + and (itemnumber = ?) + and (accountno = ?) "); + $usth->execute($bornum,$itemno,$data->{'accountno'}); $usth->finish(); $oduecharge = $data->{'amountoutstanding'}; } $sth->finish; # check for charge made for lost book - my $query = "select * from accountlines - where (borrowernumber = '$bornum') - and (itemnumber = '$itemno') - and (accounttype = 'L')"; - my $sth = $dbh->prepare($query); - $sth->execute; + my $sth = $dbh->prepare("select * from accountlines + where (borrowernumber = ?) + and (itemnumber = ?) + and (accounttype = 'L')"); + $sth->execute($bornum,$itemno); if (my $data = $sth->fetchrow_hashref) { # writeoff this amount my $offset; @@ -233,27 +225,24 @@ sub returnrecord { $offset = $amount - $data->{'amountoutstanding'}; $amountleft = $data->{'amountoutstanding'} - $amount; } - my $uquery = "update accountlines + my $usth = $dbh->prepare("update accountlines set accounttype = 'LR',amountoutstanding='0' - where (borrowernumber = '$bornum') - and (itemnumber = '$itemno') - and (accountno = '$acctno') "; - my $usth = $dbh->prepare($uquery); - $usth->execute(); + where (borrowernumber = ?) + and (itemnumber = ?) + and (accountno = ?) "); + $usth->execute($bornum,$itemno,$acctno); $usth->finish; my $nextaccntno = C4::Accounts::getnextacctno($env,$bornum,$dbh); - $uquery = "insert into accountlines + $usth = $dbh->prepare("insert into accountlines (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding) - values ($bornum,$nextaccntno,now(),0-$amount,'Book Returned', - 'CR',$amountleft)"; - $usth = $dbh->prepare($uquery); - $usth->execute; + values (?,?,now(),?,'Book Returned','CR',?)"); + $usth->execute($bornum,$nextaccntno,0-$amount,$amountleft); $usth->finish; $uquery = "insert into accountoffsets (borrowernumber, accountno, offsetaccount, offsetamount) - values ($bornum,$data->{'accountno'},$nextaccntno,$offset)"; - $usth = $dbh->prepare($uquery); - $usth->execute; + values (?,?,?,?)"; + $usth = $dbh->prepare(""); + $usth->execute($bornum,$data->{'accountno'},$nextaccntno,$offset); $usth->finish; } $sth->finish; @@ -278,11 +267,10 @@ sub calc_odues { sub updatelastseen { my ($env,$dbh,$itemnumber)= @_; my $br = $env->{'branchcode'}; - my $query = "update items - set datelastseen = now(), holdingbranch = '$br' - where (itemnumber = '$itemnumber')"; - my $sth = $dbh->prepare($query); - $sth->execute; + my $sth = $dbh->prepare("update items + set datelastseen = now(), holdingbranch = ? + where (itemnumber = ?)"); + $sth->execute($br,$itemnumber); $sth->finish; } @@ -295,11 +283,10 @@ sub updatelastseen { sub find_reserves { my ($env,$dbh,$itemno) = @_; my $itemdata = itemnodata($env,$dbh,$itemno); - my $query = "select * from reserves where found is null - and biblionumber = $itemdata->{'biblionumber'} and cancellationdate is NULL - order by priority,reservedate "; - my $sth = $dbh->prepare($query); - $sth->execute; + my $sth = $dbh->prepare("select * from reserves where found is null + and biblionumber = ? and cancellationdate is NULL + order by priority,reservedate "); + $sth->execute($itemdata->{'biblionumber'}; my $resfound = "n"; my $resrec; while (($resrec=$sth->fetchrow_hashref) && ($resfound eq "n")) { @@ -310,10 +297,8 @@ sub find_reserves { } elsif ($resrec->{'constrainttype'} eq "a") { $resfound = "y"; } else { - my $conquery = "select * from reserveconstraints where borrowernumber -= $resrec->{'borrowernumber'} and reservedate = '$resrec->{'reservedate'}' and biblionumber = $resrec->{'biblionumber'} and biblioitemnumber = $itemdata->{'biblioitemnumber'}"; - my $consth = $dbh->prepare($conquery); - $consth->execute; + my $consth = $dbh->prepare("select * from reserveconstraints where borrowernumber = ? and reservedate = ? and biblionumber = ? and biblioitemnumber = ?"); + $consth->execute($resrec->{'borrowernumber'},$resrec->{'reservedate'},$resrec->{'biblionumber'},$itemdata->{'biblioitemnumber'}); if (my $conrec=$consth->fetchrow_hashref) { if ($resrec->{'constrainttype'} eq "o") { $resfound = "y"; @@ -326,21 +311,19 @@ sub find_reserves { $consth->finish; } if ($resfound eq "y") { - my $updquery = "update reserves - set found = 'W',itemnumber='$itemno' - where borrowernumber = $resrec->{'borrowernumber'} - and reservedate = '$resrec->{'reservedate'}' - and biblionumber = $resrec->{'biblionumber'}"; - my $updsth = $dbh->prepare($updquery); - $updsth->execute; + my $updsth = $dbh->prepare("update reserves + set found = 'W',itemnumber = ? + where borrowernumber = ? + and reservedate = ? + and biblionumber = ?"); + $updsth->execute($itemno,$resrec->{'borrowernumber'},$resrec->{'reservedate'},$resrec->{'biblionumber'}); $updsth->finish; my $itbr = $resrec->{'branchcode'}; if ($resrec->{'branchcode'} ne $env->{'branchcode'}) { - my $updquery = "update items + my $updsth = $dbh->prepare("update items set holdingbranch = 'TR' - where itemnumber = $itemno"; - my $updsth = $dbh->prepare($updquery); - $updsth->execute; + where itemnumber = ?"); + $updsth->execute($itemno); $updsth->finish; } } -- 2.39.5