From 2f3d8327f0fc98e345b8d691b9c3c91cc51d771c Mon Sep 17 00:00:00 2001 From: tipaul Date: Mon, 12 Jan 2004 16:58:20 +0000 Subject: [PATCH] sync'ing with rel_2_0 (mostly prepare/execute pb, bug #662) --- bookcount.pl | 45 ++++++++++++++++----------------------------- deletemem.pl | 33 ++++++++++----------------------- overdue.pl | 22 ++++++++-------------- pay.pl | 22 +++++++++------------- stats2.pl | 7 +++---- thesaurus_popup.pl | 4 ++-- updateitem.pl | 21 +++++++++------------ 7 files changed, 57 insertions(+), 97 deletions(-) diff --git a/bookcount.pl b/bookcount.pl index bbb2709b04..0fab4f78a9 100755 --- a/bookcount.pl +++ b/bookcount.pl @@ -107,10 +107,8 @@ use DBI; sub itemdatanum { my ($itemnumber)=@_; my $dbh = C4::Context->dbh; - my $itm = $dbh->quote("$itemnumber"); - my $query = "select * from items where itemnumber=$itm"; - my $sth=$dbh->prepare($query); - $sth->execute; + my $sth=$dbh->prepare("select * from items where itemnumber=?"); + $sth->execute($itemnumber); my $data=$sth->fetchrow_hashref; $sth->finish; return($data); @@ -119,14 +117,12 @@ sub itemdatanum { sub lastmove { my ($itemnumber)=@_; my $dbh = C4::Context->dbh; - my $var1 = $dbh->quote($itemnumber); - my $sth =$dbh->prepare("select max(branchtransfers.datearrived) from branchtransfers where branchtransfers.itemnumber=$var1"); - $sth->execute; + my $sth =$dbh->prepare("select max(branchtransfers.datearrived) from branchtransfers where branchtransfers.itemnumber=?"); + $sth->execute($itemnumber); my ($date) = $sth->fetchrow_array; return(0, "Item has no branch transfers record") if not $date; - my $var2 = $dbh->quote($date); - $sth=$dbh->prepare("Select * from branchtransfers where branchtransfers.itemnumber=$var1 and branchtransfers.datearrived=$var2"); - $sth->execute; + $sth=$dbh->prepare("Select * from branchtransfers where branchtransfers.itemnumber=? and branchtransfers.datearrived=?"); + $sth->execute($itemnumber,$date); my ($data) = $sth->fetchrow_hashref; return(0, "Item has no branch transfers record") if not $data; $sth->finish; @@ -136,10 +132,8 @@ sub lastmove { sub issuessince { my ($itemnumber, $date)=@_; my $dbh = C4::Context->dbh; - my $itm = $dbh->quote($itemnumber); - my $dat = $dbh->quote($date); - my $sth=$dbh->prepare("Select count(*) from issues where issues.itemnumber=$itm and issues.timestamp > $dat"); - $sth->execute; + my $sth=$dbh->prepare("Select count(*) from issues where issues.itemnumber=? and issues.timestamp > ?"); + $sth->execute($itemnumber,$date); my $count=$sth->fetchrow_hashref; $sth->finish; return($count->{'count(*)'}); @@ -148,32 +142,25 @@ sub issuessince { sub issuesat { my ($itemnumber, $brcd)=@_; my $dbh = C4::Context->dbh; - my $itm = $dbh->quote($itemnumber); - my $brc = $dbh->quote($brcd); - my $query = "Select count(*) from issues where itemnumber=$itm and branchcode = $brc"; - my $sth=$dbh->prepare($query); - $sth->execute; + my $sth=$dbh->prepare("Select count(*) from issues where itemnumber=? and branchcode = ?"); + $sth->execute($itemnumber,$brcd); my ($count)=$sth->fetchrow_array; $sth->finish; return($count); } sub lastseenat { - my ($itemnumber, $brcd)=@_; + my ($itm, $brc)=@_; my $dbh = C4::Context->dbh; - my $itm = $dbh->quote($itemnumber); - my $brc = $dbh->quote($brcd); - my $query = "Select max(timestamp) from issues where itemnumber=$itm and branchcode = $brc"; - my $sth=$dbh->prepare($query); - $sth->execute; + my $sth=$dbh->prepare("Select max(timestamp) from issues where itemnumber=? and branchcode = ?"); + $sth->execute($itm,$brc); my ($date1)=$sth->fetchrow_array; $sth->finish; - $query = "Select max(datearrived) from branchtransfers where itemnumber=$itm and tobranch = $brc"; - # FIXME - There's already a $sth in this scope. - my $sth=$dbh->prepare($query); - $sth->execute; + $sth=$dbh->prepare("Select max(datearrived) from branchtransfers where itemnumber=? and tobranch = ?"); + $sth->execute($itm,$brc); my ($date2)=$sth->fetchrow_array; $sth->finish; + #FIXME: MJR thinks unsafe $date2 =~ s/-//g; $date2 =~ s/://g; $date2 =~ s/ //g; diff --git a/deletemem.pl b/deletemem.pl index 74113739aa..5d7746afc9 100755 --- a/deletemem.pl +++ b/deletemem.pl @@ -56,9 +56,8 @@ $env{'nottodayissues'}=1; } my ($bor,$flags)=getpatroninformation(\%env, $member,''); my $dbh = C4::Context->dbh; -my $query="Select * from borrowers where guarantor='$member'"; -my $sth=$dbh->prepare($query); -$sth->execute; +my $sth=$dbh->prepare("Select * from borrowers where guarantor=?"); +$sth->execute($member); my $data=$sth->fetchrow_hashref; $sth->finish; @@ -85,29 +84,17 @@ if ($i > 0 || $flags->{'CHARGES'} ne '' || $data ne ''){ sub delmember{ my ($member)=@_; my $dbh = C4::Context->dbh; - my $query="Select * from borrowers where borrowernumber='$member'"; - my $sth=$dbh->prepare($query); - $sth->execute; + my $sth=$dbh->prepare("Select * from borrowers where borrowernumber=?"); + $sth->execute($member); my @data=$sth->fetchrow_array; $sth->finish; - $query="Insert into deletedborrowers values ("; - foreach my $temp (@data){ - $query .= "'$temp',"; - } - $query=~ s/\,$/\)/; - # print $query; - # FIXME - Use $dbh->do() - $sth=$dbh->prepare($query); - $sth->execute; + $sth=$dbh->prepare("Insert into deletedborrowers values (".("?,"x(scalar(@data)-1))."?)"); + $sth->execute(@data); $sth->finish; - # FIXME - Use $dbh->do() - $query = "Delete from borrowers where borrowernumber='$member'"; - $sth=$dbh->prepare($query); - $sth->execute; + $sth=$dbh->prepare("Delete from borrowers where borrowernumber=?"); + $sth->execute($member); $sth->finish; - # FIXME - Use $dbh->do() - $query="Delete from reserves where borrowernumber='$member'"; - $sth=$dbh->prepare($query); - $sth->execute; + $sth=$dbh->prepare("Delete from reserves where borrowernumber=?"); + $sth->execute($member); $sth->finish; } diff --git a/overdue.pl b/overdue.pl index 4a08882a21..05a4ad6fe7 100755 --- a/overdue.pl +++ b/overdue.pl @@ -56,9 +56,8 @@ my $todaysdate = (1900+$datearr[5]).'-'.sprintf ("%0.2d", ($datearr[4]+1)).'-'.s my $dbh = C4::Context->dbh; -my $query="select date_due,borrowernumber,itemnumber from issues where isnull(returndate) && date_due<'$todaysdate' order by date_due,borrowernumber"; -my $sth=$dbh->prepare($query); -$sth->execute; +my $sth=$dbh->prepare("select date_due,borrowernumber,itemnumber from issues where isnull(returndate) && date_dueexecute($todaysdate); my @overduedata; while (my $data=$sth->fetchrow_hashref) { @@ -66,27 +65,22 @@ while (my $data=$sth->fetchrow_hashref) { $bornum=$data->{'borrowernumber'}; $itemnum=$data->{'itemnumber'}; - my $query="select concat(firstname,' ',surname),phone,emailaddress from borrowers where borrowernumber='$bornum'"; - my $sth1=$dbh->prepare($query); - $sth1->execute; + my $sth1=$dbh->prepare("select concat(firstname,' ',surname),phone,emailaddress from borrowers where borrowernumber=?"); + $sth1->execute($bornum); $data1=$sth1->fetchrow_hashref; $name=$data1->{'concat(firstname,\' \',surname)'}; $phone=$data1->{'phone'}; $email=$data1->{'emailaddress'}; $sth1->finish; - # FIXME - There's already a $query in this scope. - my $query="select biblionumber from items where itemnumber='$itemnum'"; - my $sth2=$dbh->prepare($query); - $sth2->execute; + my $sth2=$dbh->prepare("select biblionumber from items where itemnumber=?"); + $sth2->execute($itemnum); $data2=$sth2->fetchrow_hashref; $biblionumber=$data2->{'biblionumber'}; $sth2->finish; - # FIXME - There's already a $query in this scope. - my $query="select title,author from biblio where biblionumber='$biblionumber'"; - my $sth3=$dbh->prepare($query); - $sth3->execute; + my $sth3=$dbh->prepare("select title,author from biblio where biblionumber=?"); + $sth3->execute($biblionumber); $data3=$sth3->fetchrow_hashref; $title=$data3->{'title'}; $author=$data3->{'author'}; diff --git a/pay.pl b/pay.pl index 54c2a8358a..f3de382b0e 100755 --- a/pay.pl +++ b/pay.pl @@ -157,28 +157,24 @@ sub writeoff{ $user=~ s/Shannon/S/; my $dbh = C4::Context->dbh; my $env; - my $query="Update accountlines set amountoutstanding=0 where "; + my $sth; if ($accounttype eq 'Res'){ - $query.="accounttype='Res' and accountno='$accountnum' and borrowernumber='$bornum'"; + $sth=$dbh->prepare("Update accountlines set amountoutstanding=0 where accounttype='Res' and accountno=? and borrowernumber=?"); + $sth->execute($accountnum,$bornum); } else { - $query.="accounttype='$accounttype' and itemnumber='$itemnum' and borrowernumber='$bornum'"; + $sth=$dbh->prepare("Update accountlines set amountoutstanding=0 where accounttype=? and itemnumber=? and borrowernumber=?"); + $sth->execute($accounttype,$itemnum,$bornum); } - my $sth=$dbh->prepare($query); - # print $query; - $sth->execute; $sth->finish; - $query="select max(accountno) from accountlines"; - $sth=$dbh->prepare($query); + $sth=$dbh->prepare("select max(accountno) from accountlines"); $sth->execute; my $account=$sth->fetchrow_hashref; $sth->finish; $account->{'max(accountno)'}++; - $query="insert into accountlines (borrowernumber,accountno,itemnumber,date,amount,description,accounttype) - values ('$bornum','$account->{'max(accountno)'}','$itemnum',now(),'$amount','Writeoff','W')"; - $sth=$dbh->prepare($query); - $sth->execute; + $sth=$dbh->prepare("insert into accountlines (borrowernumber,accountno,itemnumber,date,amount,description,accounttype) + values (?,?,?,now(),?,'Writeoff','W')"); + $sth->execute($bornum,$account->{'max(accountno)'},$itemnum,$amount); $sth->finish; - # print $query; UpdateStats($env,$user,'writeoff',$amount,'','','',$bornum); } diff --git a/stats2.pl b/stats2.pl index 8944b027fc..2b11ca1689 100755 --- a/stats2.pl +++ b/stats2.pl @@ -62,15 +62,14 @@ $date=UnixDate($date,'%Y-%m-%d'); $date2=UnixDate($date2,'%Y-%m-%d'); my $dbh = C4::Context->dbh; -my $query="select * +my $sth=$dbh->prepare("select * from accountlines,accountoffsets,borrowers where accountlines.borrowernumber=accountoffsets.borrowernumber and (accountlines.accountno=accountoffsets.accountno or accountlines.accountno =accountoffsets.offsetaccount) and accountlines.timestamp >=20000621000000 and borrowers.borrowernumber=accountlines.borrowernumber -group by accountlines.borrowernumber,accountlines.accountno"; -my $sth=$dbh->prepare($query); -$sth->execute; +group by accountlines.borrowernumber,accountlines.accountno"); +$sth->execute(); diff --git a/thesaurus_popup.pl b/thesaurus_popup.pl index 491d3291ee..c4ef269562 100755 --- a/thesaurus_popup.pl +++ b/thesaurus_popup.pl @@ -74,8 +74,8 @@ my %stdlib; my $select_list; if ($search_string) { # my $sti=$dbh->prepare("select id,freelib from bibliothesaurus where freelib like '".$search_string."%' and category ='$category'"); - my $sti=$dbh->prepare("select id,freelib,father from bibliothesaurus where match (category,freelib) AGAINST (?) and category ='$category'"); - $sti->execute($search_string); + my $sti=$dbh->prepare("select id,freelib,father from bibliothesaurus where match (category,freelib) AGAINST (?) and category =?"); + $sti->execute($search_string,$category); while (my $line=$sti->fetchrow_hashref) { $stdlib{$line->{'id'}} = "$line->{'father'} $line->{'freelib'}"; push(@freelib,$line->{'id'}); diff --git a/updateitem.pl b/updateitem.pl index 48945fdb6b..d300a5d940 100755 --- a/updateitem.pl +++ b/updateitem.pl @@ -88,23 +88,20 @@ if ($wthdrawn == 0 && $override ne 'yes'){ }); if ($lost ==1){ my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("Select * from issues where (itemnumber='$itemnum') and (returndate is null)"); - $sth->execute; + my $sth=$dbh->prepare("Select * from issues where (itemnumber=?) and (returndate is null)"); + $sth->execute($itemnum); my $data=$sth->fetchrow_hashref; if ($data->{'borrowernumber'} ne '') { #item on issue add replacement cost to borrowers record my $accountno=getnextacctno($env,$data->{'borrowernumber'},$dbh); my $item=getiteminformation($env, $itemnum); - my $account="Insert into accountlines + my $sth2=$dbh->prepare("Insert into accountlines (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding,itemnumber) values - ('$data->{'borrowernumber'}','$accountno',now(),'$item->{'replacementprice'}', - 'Lost Item $item->{'title'} $item->{'barcode'}','L', - '$item->{'replacementprice'}','$itemnum')"; - my $sth2=$dbh->prepare($account); -# print $input->header; -# print $account; - $sth2->execute; + (?,?,now(),?,?,'L',?,?)"); + $sth2->execute($data->{'borrowernumber'},$accountno,$item->{'replacementprice'}, + "Lost Item $item->{'title'} $item->{'barcode'}", + $item->{'replacementprice'},$itemnum); $sth2->finish; } $sth->finish; @@ -123,8 +120,8 @@ if ($wthdrawn == 0 && $override ne 'yes'){ print "The biblio or biblioitem this item belongs to has a reserve on it"; $flag=1; } - my $sth=$dbh->prepare("Select * from issues where (itemnumber='$itemnum') and (returndate is null)"); - $sth->execute; + my $sth=$dbh->prepare("Select * from issues where (itemnumber=?) and (returndate is null)"); + $sth->execute($itemnum); my $data=$sth->fetchrow_hashref; if ($data->{'borrowernumber'} ne '') { print $input->header; -- 2.39.5