From 47c18cfcdcc828cd7150daf193b53166264cdd44 Mon Sep 17 00:00:00 2001 From: truth_nz Date: Mon, 29 Apr 2002 05:25:41 +0000 Subject: [PATCH] Modifications for new acqui.simple Database includes new biblio abstract field and biblioitem url field acqui/finishreceive.pl modified to use changed C4/Acquisitions.pm --- C4/Acquisitions.pm | 315 +++++++++++++++++++++----- acqui.simple/addbooks.pl | 472 +++++++-------------------------------- acqui/finishreceive.pl | 9 +- database.mysql | 2 + 4 files changed, 343 insertions(+), 455 deletions(-) diff --git a/C4/Acquisitions.pm b/C4/Acquisitions.pm index 7ccadbddbf..9f29c1fa7d 100644 --- a/C4/Acquisitions.pm +++ b/C4/Acquisitions.pm @@ -12,13 +12,14 @@ $VERSION = 0.01; @ISA = qw(Exporter); @EXPORT = qw(&getorders &bookseller &breakdown &basket &newbasket &bookfunds &ordersearch &newbiblio &newbiblioitem &newsubject &newsubtitle &neworder - &newordernum &modbiblio &modorder &getsingleorder &invoice &receiveorder - &bookfundbreakdown &curconvert &updatesup &insertsup &makeitems &modbibitem +&newordernum &modbiblio &modorder &getsingleorder &invoice &receiveorder +&bookfundbreakdown &curconvert &updatesup &insertsup &newitems &modbibitem &getcurrencies &modsubtitle &modsubject &modaddauthor &moditem &countitems &findall &needsmod &delitem &delbibitem &delbiblio &delorder &branches &getallorders &getrecorders &updatecurrencies &getorder &getcurrency &updaterecorder -&updatecost &checkitems &modnote &getitemtypes &getbiblio); - +&updatecost &checkitems &modnote &getitemtypes &getbiblio +&getbiblioitem &getitemsbybiblioitem &isbnsearch &keywordsearch +&websitesearch); %EXPORT_TAGS = ( ); # eg: TAG => [ qw!name1 name2! ], # your exported package globals go here, @@ -193,18 +194,18 @@ sub getrecorders { } sub ordersearch { - my ($search,$biblio,$catview)=@_; - my $dbh=C4Connect; - my $query="Select *,biblio.title from aqorders,biblioitems,biblio - where aqorders.biblioitemnumber= - biblioitems.biblioitemnumber and biblio.biblionumber=aqorders.biblionumber - and (datecancellationprinted is NULL or datecancellationprinted = -'000-00-00') - and (("; - my @data=split(' ',$search); - my $count=@data; - for (my $i=0;$i<$count;$i++){ - $query.= "(biblio.title like '$data[$i]%' or biblio.title like '% $data[$i]%') and "; + my ($search,$biblio,$catview) = @_; + my $dbh = C4Connect; + my $query = "Select *,biblio.title from aqorders,biblioitems,biblio +where aqorders.biblioitemnumber = biblioitems.biblioitemnumber +and biblio.biblionumber=aqorders.biblionumber +and ((datecancellationprinted is NULL) +or (datecancellationprinted = '0000-00-00') +and (("; + my @data = split(' ',$search); + my $count = @data; + for (my $i = 0; $i < $count; $i++) { + $query .= "(biblio.title like '$data[$i]%' or biblio.title like '% $data[$i]%') and "; } $query=~ s/ and $//; $query.=" ) or biblioitems.isbn='$search' @@ -396,6 +397,7 @@ sub newbiblio { $biblio->{'copyright'} = $dbh->quote($biblio->{'copyright'}); $biblio->{'seriestitle'} = $dbh->quote($biblio->{'seriestitle'}); $biblio->{'notes'} = $dbh->quote($biblio->{'notes'}); + $biblio->{'abstract'} = $dbh->quote($biblio->{'abstract'}); if ($biblio->{'seriestitle'}) { $series = 1 }; $sth->finish; @@ -406,7 +408,8 @@ author = $biblio->{'author'}, copyrightdate = $biblio->{'copyright'}, series = $series; seriestitle = $biblio->{'seriestitle'}, -notes = $biblio->{'notes'}"; +notes = $biblio->{'notes'}, +abstract = $biblio->{'abstract'}"; $sth = $dbh->prepare($query); # print $query; @@ -418,18 +421,24 @@ notes = $biblio->{'notes'}"; } sub modbiblio { - my ($bibnum,$title,$author,$copyright,$seriestitle,$serial,$unititle,$notes)=@_; - my $dbh=C4Connect; - my $query="update biblio set title='$title', - author='$author',copyrightdate='$copyright', - seriestitle='$seriestitle',serial='$serial',unititle='$unititle',notes='$notes' - where - biblionumber=$bibnum"; - my $sth=$dbh->prepare($query); + my ($bibnum, $title, $author, $copyright, $seriestitle, $serial, $unititle, $notes)=@_; + my $dbh = C4Connect; + my $query = "Update biblio set +title = '$title', +author = '$author', +copyrightdate = '$copyright', +seriestitle = '$seriestitle', +serial = '$serial', +unititle = '$unititle', +notes = '$notes' +where biblionumber = $bibnum"; + my $sth = $dbh->prepare($query); + $sth->execute; + $sth->finish; $dbh->disconnect; - return($bibnum); + return($bibnum); } sub modsubtitle { @@ -443,20 +452,28 @@ sub modsubtitle { } sub modaddauthor { - my ($bibnum,$author)=@_; - my $dbh=C4Connect; - my $query="Delete from additionalauthors where biblionumber=$bibnum"; - my $sth=$dbh->prepare($query); - $sth->execute; - $sth->finish; - if ($author ne ''){ - $query="insert into additionalauthors (author,biblionumber) values ('$author','$bibnum')"; - $sth=$dbh->prepare($query); + my ($bibnum, $author) = @_; + my $dbh = C4Connect; + my $query = "Delete from additionalauthors where biblionumber = $bibnum"; + my $sth = $dbh->prepare($query); + $sth->execute; $sth->finish; - } + + if ($author ne '') { + $query = "Insert into additionalauthors set +author = '$author', +biblionumber = '$bibnum'"; + $sth = $dbh->prepare($query); + + $sth->execute; + + $sth->finish; + } # if + $dbh->disconnect; -} +} # sub modaddauthor + sub modsubject { my ($bibnum,$force,@subject)=@_; @@ -557,6 +574,7 @@ sub newbiblioitem { $biblioitem->{'number'} = $dbh->quote($biblioitem->{'number'}); $biblioitem->{'classification'} = $dbh->quote($biblioitem->{'classification'}); $biblioitem->{'itemtype'} = $dbh->quote($biblioitem->{'itemtype'}); + $biblioitem->{'url'} = $dbh->quote($biblioitem->{'url'}); $biblioitem->{'isbn'} = $dbh->quote($biblioitem->{'isbn'}); $biblioitem->{'issn'} = $dbh->quote($biblioitem->{'issn'}); $biblioitem->{'dewey'} = $dbh->quote($biblioitem->{'dewey'}); @@ -583,6 +601,7 @@ volume = $biblioitem->{'volume'}, number = $biblioitem->{'number'}, classification = $biblioitem->{'classification'}, itemtype = $biblioitem->{'itemtype'}, +url = $biblioitem->{'url'}, isbn = $biblioitem->{'isbn'}, issn = $biblioitem->{'issn'}, dewey = $biblioitem->{'dewey'}, @@ -863,31 +882,53 @@ sub insertsup { return($data->{'id'}); } -sub makeitems { - my -($count,$bibitemno,$biblio,$replacement,$price,$booksellerid,$branch,$loan,@barcodes)=@_; - my $dbh=C4Connect; - my $sth=$dbh->prepare("Select max(itemnumber) from items"); + +sub newitems { + my ($item, @barcodes) = @_; + my $dbh = C4Connect; + my $query = "Select max(itemnumber) from items"; + my $sth = $dbh->prepare($query); + my $data; + my $itemnumber; + my $error; + $sth->execute; - my $data=$sth->fetchrow_hashref; - my $item=$data->{'max(itemnumber)'}; + $data = $sth->fetchrow_hashref; + $itemnumber = $data->{'max(itemnumber)'} + 1; $sth->finish; - $item++; - my $error; - for (my $i=0;$i<$count;$i++){ - $barcodes[$i]=uc $barcodes[$i]; - my $query="Insert into items (biblionumber,biblioitemnumber,itemnumber,barcode, - booksellerid,dateaccessioned,homebranch,holdingbranch,price,replacementprice, - replacementpricedate,notforloan) values - ($biblio,$bibitemno,$item,'$barcodes[$i]','$booksellerid',now(),'$branch', - '$branch','$price','$replacement',now(),$loan)"; - my $sth=$dbh->prepare($query); + + $item->{'booksellerid'} = $dbh->quote($item->{'bookselletid'}); + $item->{'homebranch'} = $dbh->quote($item->{'homebranch'}); + $item->{'price'} = $dbh->quote($item->{'price'}); + $item->{'replacementprice'} = $dbh->quote($item->{'replacementprice'}); + $item->{'itemnotes'} = $dbh->quote($item->{'itemnotes'}); + + foreach my $barcode (@barcodes) { + $barcode = uc($barcode); + $query = "Insert into items set +itemnumber = $itemnumber, +biblionumber = $item->{'biblionumber'}, +biblioitemnumber = $item->{'biblioitemnumber'}, +barcode = $barcode, +booksellerid = $item->{'booksellerid'}, +dateaccessioned = NOW(), +homebranch = $item->{'branch'}, +holdingbranch = $item->{'branch'}, +price = $item->{'price'}, +replacementprice = $item->{'replacementprice'}, +replacementpricedate = NOW(), +notforloan = $item->{'loan'}, +itemnotes = $item->{'itemnotes'}"; + + $sth = $dbh->prepare($query); $sth->execute; - $error.=$sth->errstr; + + $error .= $sth->errstr; + $sth->finish; - $item++; -# print $query; - } + $itemnumber++; + } # for + $dbh->disconnect; return($error); } @@ -1098,12 +1139,12 @@ sub getbiblio { my $dbh = C4Connect; my $query = "Select * from biblio where biblionumber = $biblionumber"; my $sth = $dbh->prepare($query); - # || die "Cannot prepare $query" . $dbh->errstr; + # || die "Cannot prepare $query\n" . $dbh->errstr; my $count = 0; my @results; $sth->execute; - # || die "Cannot execute $query" . $sth->errstr; + # || die "Cannot execute $query\n" . $sth->errstr; while (my $data = $sth->fetchrow_hashref) { $results[$count] = $data; $count++; @@ -1115,4 +1156,158 @@ sub getbiblio { } # sub getbiblio +sub getbiblioitem { + my ($biblioitemnum) = @_; + my $dbh = C4Connect; + my $query = "Select * from biblioitems where +biblioitemnumber = $biblioitemnum"; + my $sth = $dbh->prepare($query); + my $count = 0; + my @results; + + $sth->execute; + + while (my $data = $sth->fetchrow_hashref) { + $results[$count] = $data; + $count++; + } # while + + $sth->finish; + $dbh->disconnect; + return($count, @results); +} # sub getbiblioitem + + +sub getitemsbybiblioitem { + my ($biblioitemnum) = @_; + my $dbh = C4Connect; + my $query = "Select * from items, biblio where +biblio.biblionumber = items.biblionumber and biblioitemnumber += $biblioitemnum"; + my $sth = $dbh->prepare($query); + # || die "Cannot prepare $query\n" . $dbh->errstr; + my $count = 0; + my @results; + + $sth->execute; + # || die "Cannot execute $query\n" . $sth->errstr; + while (my $data = $sth->fetchrow_hashref) { + $results[$count] = $data; + $count++; + } # while + + $sth->finish; + $dbh->disconnect; + return($count, @results); +} # sub getitemsbybiblioitem + + +sub isbnsearch { + my ($isbn) = @_; + my $dbh = C4Connect; + my $count = 0; + my $query; + my $sth; + my @results; + + $isbn = $dbh->quote($isbn); + $query = "Select * from biblioitems where isbn = $isbn"; + $sth = $dbh->prepare($query); + + $sth->execute; + while (my $data = $sth->fetchrow_hashref) { + $results[$count] = $data; + $count++; + } # while + + $sth->finish; + $dbh->disconnect; + return($count, @results); +} # sub isbnsearch + + +sub keywordsearch { + my ($keywordlist) = @_; + my $dbh = C4Connect; + my $query = "Select * from biblio where"; + my $count = 0; + my $sth; + my @results; + my @keywords = split(/ +/, $keywordlist); + my $keyword = shift(@keywords); + + $keyword =~ s/%/\\%/g; + $keyword =~ s/_/\\_/; + $keyword = "%" . $keyword . "%"; + $keyword = $dbh->quote($keyword); + $query .= " (author like $keyword) or +(title like $keyword) or (unititle like $keyword) or +(notes like $keyword) or (seriestitle like $keyword) or +(abstract like $keyword)"; + + foreach $keyword (@keywords) { + $keyword =~ s/%/\\%/; + $keyword =~ s/_/\\_/; + $keyword = "%" . $keyword . "%"; + $keyword = $dbh->quote($keyword); + $query .= " or (author like $keyword) or +(title like $keyword) or (unititle like $keyword) or +(notes like $keyword) or (seriestitle like $keyword) or +(abstract like $keyword)"; + } # foreach + + $sth = $dbh->prepare($query); + $sth->execute; + + while (my $data = $sth->fetchrow_hashref) { + $results[$count] = $data; + $count++; + } # while + + $sth->finish; + $dbh->disconnect; + return($count, @results); +} # sub keywordsearch + + +sub websitesearch { + my ($keywordlist) = @_; + my $dbh = C4Connect; + my $query = "Select distinct biblio.* from biblio, biblioitems where +biblio.biblionumber = biblioitems.biblionumber and ("; + my $count = 0; + my $sth; + my @results; + my @keywords = split(/ +/, $keywordlist); + my $keyword = shift(@keywords); + + $keyword =~ s/%/\\%/g; + $keyword =~ s/_/\\_/; + $keyword = "%" . $keyword . "%"; + $keyword = $dbh->quote($keyword); + $query .= " (url like $keyword)"; + + foreach $keyword (@keywords) { + $keyword =~ s/%/\\%/; + $keyword =~ s/_/\\_/; + $keyword = "%" . $keyword . "%"; + $keyword = $dbh->quote($keyword); + $query .= " or (url like $keyword)"; + } # foreach + + $query .= ")"; + $sth = $dbh->prepare($query); + $sth->execute; + + while (my $data = $sth->fetchrow_hashref) { + $results[$count] = $data; + $count++; + } # while + + $sth->finish; + $dbh->disconnect; + return($count, @results); +} # sub websitesearch + + END { } # module clean-up code here (global destructor) diff --git a/acqui.simple/addbooks.pl b/acqui.simple/addbooks.pl index 84569afda0..4a335e23b7 100755 --- a/acqui.simple/addbooks.pl +++ b/acqui.simple/addbooks.pl @@ -1,424 +1,108 @@ #!/usr/bin/perl -# -# Modified saas@users.sf.net 12:00 01 April 2001 -# The biblioitemnumber was not correctly initialised -# The max(barcode) value was broken - koha 'barcode' is a string value! -# - If left blank, barcode value now defaults to max(biblionumber) - # # TODO # -# Error checking for pre-existing barcodes, biblionumbers and maybe others -# # Add info on biblioitems and items already entered as you enter new ones +# -use C4::Database; use CGI; use strict; -use C4::Acquisitions; use C4::Output; -use C4::Circulation::Circ2; my $input = new CGI; -my $dbh=C4Connect; - -my $isbn=$input->param('isbn'); -my $q_isbn=$dbh->quote($isbn); -my $biblioitemnumber; +my $error = $input->param('error'); print $input->header; print startpage(); print startmenu('acquisitions'); -($input->param('checkforbiblio')) && (checkforbiblio()); -($input->param('newbiblioitem')) && (newbiblioitem()); -($input->param('newitem')) && (newitem()); +&searchscreen(); +print endmenu(); +print endpage(); -sub checkforbiblio { - my $title=$input->param('title'); - my $q_title=$dbh->quote($title); - my $author=$input->param('author'); - my $q_author=$dbh->quote($author); - my $seriestitle=$input->param('seriestitle'); - my $serial=0; - ($seriestitle) && ($serial=1); - my $q_seriestitle=$dbh->quote($seriestitle); - my $copyrightdate=$input->param('copyrightdate'); - my $q_copyrightdate=$dbh->quote($copyrightdate); - my $notes=$input->param('notes'); - my $q_notes=$dbh->quote($notes); - my $subtitle=$input->param('subtitle'); - my $q_subtitle=$dbh->quote($subtitle); - my $sth=$dbh->prepare("select biblionumber from biblio where title=$q_title - and author=$q_author and copyrightdate=$q_copyrightdate"); - $sth->execute; - my $biblionumber=0; - if ($sth->rows) { - ($biblionumber) = $sth->fetchrow; - } else { - print "Adding new biblio for $title by $author
\n"; - my $sth=$dbh->prepare("select max(biblionumber) from biblio"); - $sth->execute; - ($biblionumber) = $sth->fetchrow; - $biblionumber++; - $sth=$dbh->prepare("insert into biblio (biblionumber, title, author, - serial, seriestitle, copyrightdate, notes) values ($biblionumber, - $q_title, $q_author, $serial, $q_seriestitle, $q_copyrightdate, - $q_notes)"); - $sth->execute; - $sth=$dbh->prepare("insert into bibliosubtitle (subtitle, biblionumber) - values ($q_subtitle, $biblionumber)"); - $sth->execute; - } - my $itemtypeselect=''; - $sth=$dbh->prepare("select itemtype,description from itemtypes"); - $sth->execute; - while (my ($itemtype, $description) = $sth->fetchrow) { - $itemtypeselect.="