From 0595587f93e6c4466f5c8f8111f9cddb3b15d587 Mon Sep 17 00:00:00 2001
From: tipaul <tipaul>
Date: Tue, 4 Jun 2002 16:08:47 +0000
Subject: [PATCH] test.pl, contains a script to test the Catalogue.pm stuff.
 Catalogue.pm : marc stuff : * the 3 xxxSubfield functions works * the
 addMarcBiblio function has been added : if you pass it a perlmarcstructure
 (sample in test.pl and explanation in marc/perlmarcstructure file), it adds a
 biblio to the marc_schema. The newbiblioitem (old koha-DB) still has to be
 updated to build the perlmarcstructure internally and call addMarcBiblio
 function.

---
 C4/Catalogue.pm | 1513 ++++++++++++++++++++++++++++++++++++++++++++++-
 C4/test.pl      |   59 ++
 2 files changed, 1550 insertions(+), 22 deletions(-)
 create mode 100755 C4/test.pl

diff --git a/C4/Catalogue.pm b/C4/Catalogue.pm
index 1533a0525d..fce576179d 100644
--- a/C4/Catalogue.pm
+++ b/C4/Catalogue.pm
@@ -20,7 +20,20 @@ $VERSION = 0.01;
 
 @ISA = qw(Exporter);
 @EXPORT = qw(&newBiblio &newBiblioItem &newItem &updateBiblio &updateBiblioItem
-	     &updateItem &changeSubfield);
+	     &updateItem &changeSubfield &addSubfield &findSubfield 
+	     &addMarcBiblio
+
+	     &getorders &bookseller &breakdown &basket &newbasket &bookfunds
+	     &ordersearch &newbiblio &newbiblioitem &newsubject &newsubtitle &neworder
+	     &newordernum &modbiblio &modorder &getsingleorder &invoice &receiveorder
+	     &bookfundbreakdown &curconvert &updatesup &insertsup &newitems &modbibitem
+	     &getcurrencies &modsubtitle &modsubject &modaddauthor &moditem &countitems 
+	     &findall &needsmod &delitem &deletebiblioitem &delbiblio &delorder &branches
+	     &getallorders &getrecorders &updatecurrencies &getorder &getcurrency &updaterecorder
+	     &updatecost &checkitems &modnote &getitemtypes &getbiblio
+	     &getbiblioitembybiblionumber
+	     &getbiblioitem &getitemsbybiblioitem &isbnsearch
+	     &websitesearch &addwebsite &updatewebsite &deletewebsite);
 %EXPORT_TAGS = ( );     # eg: TAG => [ qw!name1 name2! ],
 
 # your exported package globals go here,
@@ -109,35 +122,66 @@ sub changeSubfield {
 # Subroutine changes a subfield value given a subfieldid.
     my ( $subfieldid, $subfieldvalue )=@_;
 
-    my $dbh=&c4connect;  
-    my $sth=$dbh->prepare("update marc_subfield_table set subfieldvalue=? where subfieldid=?");
-    $sth->execute($subfieldvalue, $subfieldid);
-
+    my $dbh=&C4Connect;
+    $dbh->do("lock tables marc_blob_subfield WRITE,marc_subfield_table WRITE");
+    my $sth1=$dbh->prepare("select valuebloblink from marc_subfield_table where subfieldid=?");
+    $sth1->execute($subfieldid);
+    my ($oldvaluebloblink)=$sth1->fetchrow;
+    $sth1->finish;
+    my $sth;
+    # if too long, use a bloblink
+    if (length($subfieldvalue)>255 ) {
+	# if already a bloblink, update it, otherwise, insert a new one.
+	if ($oldvaluebloblink) {
+	    $sth=$dbh->prepare("update marc_blob_subfield set subfieldvalue=? where blobidlink=?");
+	    $sth->execute($subfieldvalue,$oldvaluebloblink);
+	} else {
+	    $sth=$dbh->prepare("insert into marc_blob_subfield (subfieldvalue) values (?)");
+	    $sth->execute($subfieldvalue);
+	    $sth=$dbh->prepare("select max(blobidlink) from marc_blob_subfield");
+	    $sth->execute;
+	    my ($res)=$sth->fetchrow;
+	    $sth=$dbh->prepare("update marc_subfield_table set subfieldvalue=null, valuebloblink=$res where subfieldid=?");
+	    $sth->execute($subfieldid);
+	}
+    } else {
+	# note this can leave orphan bloblink. Not a big problem, but we should build somewhere a orphan deleting script...
+	$sth=$dbh->prepare("update marc_subfield_table set subfieldvalue=?,valuebloblink=null where subfieldid=?");
+	$sth->execute($subfieldvalue, $subfieldid);
+    }
+    $dbh->do("unlock tables");
+    $sth->finish;
     $dbh->disconnect;
-    return($Subfield_ID, $Subfield_Key);
+    return($subfieldid, $subfieldvalue);
 }
 
 sub findSubfield {
-# find a subfieldid given bibid, tag, subfieldcode, subfieldvalue this is
-# inherently dangerous.  There could be multiple subfields that match the given
-# parameters.  What then?  What if no subfields match?  That one's easy, just
-# return 0
-    my ($bibid,$tag,$subfieldcode,$subfieldvalue) = @_;
+    my ($bibid,$tag,$subfieldcode,$subfieldvalue,$subfieldorder) = @_;
     my $resultcounter=0;
-    my $subfieldid=0;
-    my $dbh=&c4connect;
-    my $sth=$dbh->prepare("select tagid from marc_$firstdigit\XX_tag_table where bibid=$bibid");
-    $sth->execute;
-    my $sti=$dbh->prepare("select subfieldid from marc_subfield_table where tag=? and subfieldcode=? and subfieldvalue=?");
-    $sti->execute($tag, $subfieldcode,$subfieldvalue);
+    my $subfieldid;
+    my $lastsubfieldid;
+    my $dbh=&C4Connect;
+    my $query="select subfieldid from marc_subfield_table where bibid=? and tag=? and subfieldcode=?";
+    if ($subfieldvalue) {
+	$query .= " and subfieldvalue=".$dbh->quote($subfieldvalue);
+    } else {
+	if ($subfieldorder<1) {
+	    $subfieldorder=1;
+	}
+	$query .= " and subfieldorder=$subfieldorder";
+    }
+    my $sti=$dbh->prepare($query);
+    $sti->execute($bibid,$tag, $subfieldcode);
     while (($subfieldid) = $sti->fetchrow) {
 	$resultcounter++;
+	$lastsubfieldid=$subfieldid;
     }
     if ($resultcounter>1) {
 	# Error condition.  Values given did not resolve into a unique record.  Don't know what to edit
+	# should rarely occur (only if we use subfieldvalue with a value that exists twice, which is strange)
 	return -1;
     } else {
-	return $subfieldid;
+	return $lastsubfieldid;
     }
 }
 
@@ -145,11 +189,12 @@ sub addSubfield {
 # Add a new subfield to a tag.
     my $bibid=shift;
     my $tagid=shift;
+    my $tagorder=shift;
     my $subfieldcode=shift;
-    my $subfieldvalue=shift;
     my $subfieldorder=shift;
+    my $subfieldvalue=shift;
 
-    my $dbh=&c4connect;  
+    my $dbh=&C4Connect;
     unless ($subfieldorder) {
 	my $sth=$dbh->prepare("select max(subfieldorder) from marc_subfield_table where tagid=$tagid");
 	$sth->execute;
@@ -160,10 +205,55 @@ sub addSubfield {
 	    $subfieldorder=1;
 	}
     }
-    my $sth=$dbh->prepare("insert into marc_subfield_table (tagid,bibid,subfieldorder,subfieldcode,subfieldvalue) values (?,?,?,?,?)");
-    $sth->execute($tagid,$bibid,$subfieldorder,$subfieldcode,$subfieldvalue);
+    if (length($subfieldvalue)>255) {
+	$dbh->do("lock tables marc_blob_subfield WRITE, marc_subfield_table WRITE");
+	my $sth=$dbh->prepare("insert into marc_blob_subfield (subfieldvalue) values (?)");
+	$sth->execute($subfieldvalue);
+	$sth=$dbh->prepare("select max(blobidlink)from marc_blob_subfield");
+	$sth->execute;
+	my ($res)=$sth->fetchrow;
+	my $sth=$dbh->prepare("insert into marc_subfield_table (bibid,tagid,tagorder,subfieldcode,subfieldorder,valuebloblink) values (?,?,?,?,?,?)");
+	$sth->execute($bibid,$tagid,$tagorder,$subfieldcode,$subfieldorder,$res);
+	$dbh->do("unlock tables");
+    } else {
+	my $sth=$dbh->prepare("insert into marc_subfield_table (bibid,tag,tagorder,subfieldcode,subfieldorder,subfieldvalue) values (?,?,?,?,?,?)");
+	$sth->execute($bibid,$tagid,$tagorder,$subfieldcode,$subfieldorder,$subfieldvalue);
+    }
 }
 
+sub addMarcBiblio {
+# pass the marcperlstructure to this function, and it will create the records in the marc tables
+    my ($marcstructure) = @_;
+    my $dbh=C4Connect;
+    my $tags;
+    my $i;
+    my $j;
+    # adding main table, and retrieving bibid
+    $dbh->do("lock tables marc_biblio WRITE");
+    my $sth=$dbh->prepare("insert into marc_biblio (datecreated,origincode) values (now(),?)");
+    $sth->execute($marcstructure->{origincode});
+    $sth=$dbh->prepare("select max(bibid) from marc_biblio");
+    $sth->execute;
+    ($marcstructure->{bibid})=$sth->fetchrow;
+    print "BIBID :::".$marcstructure->{bibid}."\n";
+    $sth->finish;
+    $dbh->do("unlock tables");
+    # now, add subfields...
+    foreach $tags ($marcstructure->{tags}) {
+	foreach $i (keys %{$tags}) {
+	    foreach $j (keys %{$tags->{$i}->{subfields}}) {
+		&addSubfield($marcstructure->{bibid},
+			     $tags->{$i}->{tag},
+			     $tags->{$i}->{tagorder},
+			     $tags->{$i}->{subfields}->{$j}->{mark},
+			     $tags->{$i}->{subfields}->{$j}->{subfieldorder},
+			     $tags->{$i}->{subfields}->{$j}->{value}
+			     );
+		print $tags->{$i}->{tag}."//".$tags->{$i}->{subfields}->{$j}->{value}."\n";
+	    }
+	}
+    }
+}
 
 sub updateBiblio {
 # Update the biblio with biblionumber $biblio->{'biblionumber'}
@@ -1071,4 +1161,1383 @@ sub updateItem {
     $dbh->disconnect;
 }
 
+
+sub getorders {
+  my ($supplierid)=@_;
+  my $dbh=C4Connect;
+  my $query = "Select count(*),authorisedby,entrydate,basketno from aqorders where 
+  booksellerid='$supplierid' and (quantity > quantityreceived or
+  quantityreceived is NULL)
+  and (datecancellationprinted is NULL or datecancellationprinted = '0000-00-00')";
+  $query.=" group by basketno order by entrydate desc";
+  #print $query;
+  my $sth=$dbh->prepare($query);
+  $sth->execute;
+  my @results;
+  my $i=0;
+  while (my $data=$sth->fetchrow_hashref){
+    $results[$i]=$data;
+    $i++;
+  }
+  $sth->finish;
+  $dbh->disconnect;
+  return ($i,\@results);
+}
+
+sub itemcount{
+  my ($biblio)=@_;
+  my $dbh=C4Connect;
+  my $query="Select count(*) from items where biblionumber=$biblio";
+#  print $query;
+  my $sth=$dbh->prepare($query);
+  $sth->execute;
+  my $data=$sth->fetchrow_hashref;
+  $sth->finish;
+  $dbh->disconnect;
+  return($data->{'count(*)'});
+}
+
+sub getorder{
+  my ($bi,$bib)=@_;
+  my $dbh=C4Connect;
+  my $query="Select ordernumber from aqorders where biblionumber=$bib and
+  biblioitemnumber='$bi'";
+  my $sth=$dbh->prepare($query);
+  $sth->execute;
+  my $ordnum=$sth->fetchrow_hashref;
+  $sth->finish;
+  my $order=getsingleorder($ordnum->{'ordernumber'});
+  $dbh->disconnect;
+#  print $query;
+  return ($order,$ordnum->{'ordernumber'});
+}
+
+sub getsingleorder {
+  my ($ordnum)=@_;
+  my $dbh=C4Connect;
+  my $query="Select * from biblio,biblioitems,aqorders,aqorderbreakdown 
+  where aqorders.ordernumber='$ordnum' 
+  and biblio.biblionumber=aqorders.biblionumber and
+  biblioitems.biblioitemnumber=aqorders.biblioitemnumber and
+  aqorders.ordernumber=aqorderbreakdown.ordernumber";
+  my $sth=$dbh->prepare($query);
+  $sth->execute;
+  my $data=$sth->fetchrow_hashref;
+  $sth->finish;
+  $dbh->disconnect;
+  return($data);
+}
+
+sub invoice {
+  my ($invoice)=@_;
+  my $dbh=C4Connect;
+  my $query="Select * from aqorders,biblio,biblioitems where
+  booksellerinvoicenumber='$invoice'
+  and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
+  aqorders.biblioitemnumber group by aqorders.ordernumber,aqorders.biblioitemnumber";
+  my $i=0;
+  my @results;
+  my $sth=$dbh->prepare($query);
+  $sth->execute;
+  while (my $data=$sth->fetchrow_hashref){
+    $results[$i]=$data;
+    $i++;
+  }
+  $sth->finish;
+  $dbh->disconnect;
+  return($i,@results);
+}
+
+sub getallorders {
+  #gets all orders from a certain supplier, orders them alphabetically
+  my ($supid)=@_;
+  my $dbh=C4Connect;
+  my $query="Select * from aqorders,biblio,biblioitems where booksellerid='$supid'
+  and (cancelledby is NULL or cancelledby = '')
+  and (quantityreceived < quantity or quantityreceived is NULL)
+  and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
+  aqorders.biblioitemnumber
+  group by aqorders.biblioitemnumber
+  order by
+  biblio.title";
+  my $i=0;
+  my @results;
+  my $sth=$dbh->prepare($query);
+  $sth->execute;
+  while (my $data=$sth->fetchrow_hashref){
+    $results[$i]=$data;
+    $i++;
+  }
+  $sth->finish;
+  $dbh->disconnect;
+  return($i,@results);
+}
+
+sub getrecorders {
+  #gets all orders from a certain supplier, orders them alphabetically
+  my ($supid)=@_;
+  my $dbh=C4Connect;
+  my $query="Select * from aqorders,biblio,biblioitems where booksellerid='$supid'
+  and (cancelledby is NULL or cancelledby = '')
+  and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=                    
+  aqorders.biblioitemnumber and
+  aqorders.quantityreceived>0
+  and aqorders.datereceived >=now()
+  group by aqorders.biblioitemnumber 
+  order by
+  biblio.title";
+  my $i=0;
+  my @results;
+  my $sth=$dbh->prepare($query);
+  $sth->execute;
+  while (my $data=$sth->fetchrow_hashref){
+    $results[$i]=$data;
+    $i++;
+  }
+  $sth->finish;
+  $dbh->disconnect;
+  return($i,@results);
+}
+
+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 = '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'
+  or (aqorders.ordernumber='$search' and aqorders.biblionumber='$biblio')) ";
+  if ($catview ne 'yes'){
+    $query.=" and (quantityreceived < quantity or quantityreceived is NULL)";
+  }
+  $query.=" group by aqorders.ordernumber";
+  my $sth=$dbh->prepare($query);
+  $sth->execute;
+  my $i=0;
+  my @results;
+  while (my $data=$sth->fetchrow_hashref){
+     my $sth2=$dbh->prepare("Select * from biblio where
+     biblionumber='$data->{'biblionumber'}'");
+     $sth2->execute;
+     my $data2=$sth2->fetchrow_hashref;
+     $sth2->finish;
+     $data->{'author'}=$data2->{'author'};
+     $data->{'seriestitle'}=$data2->{'seriestitle'};
+     $sth2=$dbh->prepare("Select * from aqorderbreakdown where
+    ordernumber=$data->{'ordernumber'}");
+    $sth2->execute;
+    $data2=$sth2->fetchrow_hashref;
+    $sth2->finish;
+    $data->{'branchcode'}=$data2->{'branchcode'};
+    $data->{'bookfundid'}=$data2->{'bookfundid'};
+    $results[$i]=$data;
+    $i++;
+  }
+  $sth->finish;
+  $dbh->disconnect;
+  return($i,@results);
+}
+
+
+sub bookseller {
+  my ($searchstring)=@_;
+  my $dbh=C4Connect;
+  my $query="Select * from aqbooksellers where name like '%$searchstring%' or
+  id = '$searchstring'";
+  my $sth=$dbh->prepare($query);
+  $sth->execute;
+  my @results;
+  my $i=0;
+  while (my $data=$sth->fetchrow_hashref){
+    $results[$i]=$data;
+    $i++;
+  }
+  $sth->finish;
+  $dbh->disconnect;
+  return($i,@results);
+}
+
+sub breakdown {
+  my ($id)=@_;
+  my $dbh=C4Connect;
+  my $query="Select * from aqorderbreakdown where ordernumber='$id'";
+  my $sth=$dbh->prepare($query);
+  $sth->execute;
+  my @results;
+  my $i=0;
+  while (my $data=$sth->fetchrow_hashref){
+    $results[$i]=$data;
+    $i++;
+  }
+  $sth->finish;
+  $dbh->disconnect;
+  return($i,\@results);
+}
+
+sub basket {
+  my ($basketno,$supplier)=@_;
+  my $dbh=C4Connect;
+  my $query="Select *,biblio.title from aqorders,biblio,biblioitems 
+  where basketno='$basketno'
+  and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber
+  =aqorders.biblioitemnumber 
+  and (datecancellationprinted is NULL or datecancellationprinted =
+  '0000-00-00')";
+  if ($supplier ne ''){
+    $query.=" and aqorders.booksellerid='$supplier'";
+  }
+  $query.=" group by aqorders.ordernumber";
+  my $sth=$dbh->prepare($query);
+  $sth->execute;
+  my @results;
+#  print $query;
+  my $i=0;
+  while (my $data=$sth->fetchrow_hashref){
+    $results[$i]=$data;
+    $i++;
+  }
+  $sth->finish;
+  $dbh->disconnect;
+  return($i,@results);
+}
+
+sub newbasket {
+  my $dbh=C4Connect;
+  my $query="Select max(basketno) from aqorders";
+  my $sth=$dbh->prepare($query);
+  $sth->execute;
+  my $data=$sth->fetchrow_arrayref;
+  my $basket=$$data[0];
+  $basket++;
+  $sth->finish;
+  $dbh->disconnect;
+  return($basket);
+}
+
+sub bookfunds {
+  my $dbh=C4Connect;
+  my $query="Select * from aqbookfund,aqbudget where aqbookfund.bookfundid
+  =aqbudget.bookfundid
+  group by aqbookfund.bookfundid order by bookfundname";
+  my $sth=$dbh->prepare($query);
+  $sth->execute;
+  my @results;
+  my $i=0;
+  while (my $data=$sth->fetchrow_hashref){
+    $results[$i]=$data;
+    $i++;
+  }
+  $sth->finish;
+  $dbh->disconnect;
+  return($i,@results);
+}
+
+sub branches {
+    my $dbh   = C4Connect;
+    my $query = "Select * from branches";
+    my $sth   = $dbh->prepare($query);
+    my $i     = 0;
+    my @results;
+
+    $sth->execute;
+    while (my $data = $sth->fetchrow_hashref) {
+        $results[$i] = $data;
+    	$i++;
+    } # while
+
+    $sth->finish;
+    $dbh->disconnect;
+    return($i, @results);
+} # sub branches
+
+sub bookfundbreakdown {
+  my ($id)=@_;
+  my $dbh=C4Connect;
+  my $query="Select quantity,datereceived,freight,unitprice,listprice,ecost,quantityreceived,subscription
+  from aqorders,aqorderbreakdown where bookfundid='$id' and 
+  aqorders.ordernumber=aqorderbreakdown.ordernumber
+  and (datecancellationprinted is NULL or
+  datecancellationprinted='0000-00-00')";
+  my $sth=$dbh->prepare($query);
+  $sth->execute;
+  my $comtd=0;
+  my $spent=0;
+  while (my $data=$sth->fetchrow_hashref){
+    if ($data->{'subscription'} == 1){
+      $spent+=$data->{'quantity'}*$data->{'unitprice'};
+    } else {
+      my $leftover=$data->{'quantity'}-$data->{'quantityreceived'};
+      $comtd+=($data->{'ecost'})*$leftover;
+      $spent+=($data->{'unitprice'})*$data->{'quantityreceived'};
+    }
+  }
+  $sth->finish;
+  $dbh->disconnect;
+  return($spent,$comtd);
+}
+      
+
+sub newbiblio {
+  my ($biblio) = @_;
+  my $dbh    = &C4Connect;
+  my $query  = "Select max(biblionumber) from biblio";
+  my $sth    = $dbh->prepare($query);
+  $sth->execute;
+  my $data   = $sth->fetchrow_arrayref;
+  my $bibnum = $$data[0] + 1;
+  my $series = 0;
+
+  $biblio->{'title'}       = $dbh->quote($biblio->{'title'});
+  $biblio->{'author'}      = $dbh->quote($biblio->{'author'});
+  $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;
+  $query = "insert into biblio set
+biblionumber  = $bibnum,
+title         = $biblio->{'title'},
+author        = $biblio->{'author'},
+copyrightdate = $biblio->{'copyright'},
+serial        = $series,
+seriestitle   = $biblio->{'seriestitle'},
+notes         = $biblio->{'notes'},
+abstract      = $biblio->{'abstract'}";
+
+  $sth = $dbh->prepare($query);
+  $sth->execute;
+
+  $sth->finish;
+  $dbh->disconnect;
+  return($bibnum);
+}
+
+
+sub modbiblio {
+  my ($biblio) = @_;
+  my $dbh   = C4Connect;
+  my $query;
+  my $sth;
+  
+  $biblio->{'title'}         = $dbh->quote($biblio->{'title'});
+  $biblio->{'author'}        = $dbh->quote($biblio->{'author'});
+  $biblio->{'abstract'}      = $dbh->quote($biblio->{'abstract'});
+  $biblio->{'copyrightdate'} = $dbh->quote($biblio->{'copyrightdate'});
+  $biblio->{'seriestitle'}   = $dbh->quote($biblio->{'serirestitle'});
+  $biblio->{'serial'}        = $dbh->quote($biblio->{'serial'});
+  $biblio->{'unititle'}      = $dbh->quote($biblio->{'unititle'});
+  $biblio->{'notes'}         = $dbh->quote($biblio->{'notes'});
+
+  $query = "Update biblio set
+title         = $biblio->{'title'},
+author        = $biblio->{'author'},
+abstract      = $biblio->{'abstract'},
+copyrightdate = $biblio->{'copyrightdate'},
+seriestitle   = $biblio->{'seriestitle'},
+serial        = $biblio->{'serial'},
+unititle      = $biblio->{'unititle'},
+notes         = $biblio->{'notes'}
+where biblionumber = $biblio->{'biblionumber'}";
+  $sth   = $dbh->prepare($query);
+
+  $sth->execute;
+
+  $sth->finish;
+  $dbh->disconnect;
+  return($biblio->{'biblionumber'});
+} # sub modbiblio
+
+
+sub modsubtitle {
+  my ($bibnum, $subtitle) = @_;
+  my $dbh   = C4Connect;
+  my $query = "update bibliosubtitle set
+subtitle = '$subtitle'
+where biblionumber = $bibnum";
+  my $sth   = $dbh->prepare($query);
+
+  $sth->execute;
+  $sth->finish;
+  $dbh->disconnect;
+} # 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 set
+author       = '$author',
+biblionumber = '$bibnum'";
+        $sth   = $dbh->prepare($query);
+
+        $sth->execute;
+
+        $sth->finish;
+    } # if
+
+  $dbh->disconnect;
+} # sub modaddauthor
+
+
+sub modsubject {
+  my ($bibnum, $force, @subject) = @_;
+  my $dbh   = C4Connect;
+  my $count = @subject;
+  my $error;
+  for (my $i = 0; $i < $count; $i++) {
+    $subject[$i] =~ s/^ //g;
+    $subject[$i] =~ s/ $//g;
+    my $query = "select * from catalogueentry
+where entrytype = 's'
+and catalogueentry = '$subject[$i]'";
+    my $sth   = $dbh->prepare($query);
+    $sth->execute;
+
+    if (my $data = $sth->fetchrow_hashref) {
+    } else {
+      if ($force eq $subject[$i]) {
+
+         # subject not in aut, chosen to force anway
+         # so insert into cataloguentry so its in auth file
+	 $query = "Insert into catalogueentry
+(entrytype,catalogueentry)
+values ('s','$subject[$i]')";
+	 my $sth2 = $dbh->prepare($query);
+
+	 $sth2->execute;
+	 $sth2->finish;
+
+      } else {
+
+        $error = "$subject[$i]\n does not exist in the subject authority file";
+        $query = "Select * from catalogueentry
+where entrytype = 's'
+and (catalogueentry like '$subject[$i] %'
+or catalogueentry like '% $subject[$i] %'
+or catalogueentry like '% $subject[$i]')";
+        my $sth2 = $dbh->prepare($query);
+
+        $sth2->execute;
+        while (my $data = $sth2->fetchrow_hashref) {
+          $error = $error."<br>$data->{'catalogueentry'}";
+        } # while
+        $sth2->finish;
+      } # else
+    } # else
+    $sth->finish;
+  } # else
+
+  if ($error eq '') {
+    my $query = "Delete from bibliosubject where biblionumber = $bibnum";
+    my $sth   = $dbh->prepare($query);
+
+    $sth->execute;
+    $sth->finish;
+
+    for (my $i = 0; $i < $count; $i++) {
+      $sth = $dbh->prepare("Insert into bibliosubject
+values ('$subject[$i]', $bibnum)");
+
+      $sth->execute;
+      $sth->finish;
+    } # for
+  } # if
+
+  $dbh->disconnect;
+  return($error);
+} # sub modsubject
+
+
+sub modbibitem {
+    my ($biblioitem) = @_;
+    my $dbh   = C4Connect;
+    my $query;
+
+    $biblioitem->{'itemtype'}        = $dbh->quote($biblioitem->{'itemtype'});
+    $biblioitem->{'url'}             = $dbh->quote($biblioitem->{'url'});
+    $biblioitem->{'isbn'}            = $dbh->quote($biblioitem->{'isbn'});
+    $biblioitem->{'publishercode'}   = $dbh->quote($biblioitem->{'publishercode'});
+    $biblioitem->{'publicationyear'} = $dbh->quote($biblioitem->{'publicationyear'});
+    $biblioitem->{'classification'}  = $dbh->quote($biblioitem->{'classification'});
+    $biblioitem->{'dewey'}	     = $dbh->quote($biblioitem->{'dewey'});
+    $biblioitem->{'subclass'}	     = $dbh->quote($biblioitem->{'subclass'});
+    $biblioitem->{'illus'}           = $dbh->quote($biblioitem->{'illus'});
+    $biblioitem->{'pages'}           = $dbh->quote($biblioitem->{'pages'});
+    $biblioitem->{'volumeddesc'}     = $dbh->quote($biblioitem->{'volumeddesc'});
+    $biblioitem->{'notes'}           = $dbh->quote($biblioitem->{'notes'});
+    $biblioitem->{'size'}            = $dbh->quote($biblioitem->{'size'});
+    $biblioitem->{'place'}           = $dbh->quote($biblioitem->{'place'});
+
+    $query = "Update biblioitems set
+itemtype        = $biblioitem->{'itemtype'},
+url             = $biblioitem->{'url'},
+isbn            = $biblioitem->{'isbn'},
+publishercode   = $biblioitem->{'publishercode'},
+publicationyear = $biblioitem->{'publicationyear'},
+classification  = $biblioitem->{'classification'},
+dewey           = $biblioitem->{'dewey'},
+subclass        = $biblioitem->{'subclass'},
+illus           = $biblioitem->{'illus'},
+pages           = $biblioitem->{'pages'},
+volumeddesc     = $biblioitem->{'volumeddesc'},
+notes 		= $biblioitem->{'notes'},
+size		= $biblioitem->{'size'},
+place		= $biblioitem->{'place'}
+where biblioitemnumber = $biblioitem->{'biblioitemnumber'}";
+
+    $dbh->do($query);
+
+    $dbh->disconnect;
+} # sub modbibitem
+
+
+sub modnote {
+  my ($bibitemnum,$note)=@_;
+  my $dbh=C4Connect;
+  my $query="update biblioitems set notes='$note' where
+  biblioitemnumber='$bibitemnum'";
+  my $sth=$dbh->prepare($query);
+  $sth->execute;
+  $sth->finish;
+  $dbh->disconnect;
+}
+
+sub newbiblioitem {
+  my ($biblioitem) = @_;
+  my $dbh   = C4Connect;
+  my $query = "Select max(biblioitemnumber) from biblioitems";
+  my $sth   = $dbh->prepare($query);
+  my $data;
+  my $bibitemnum;
+
+  $biblioitem->{'volume'}          = $dbh->quote($biblioitem->{'volume'});
+  $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'});
+  $biblioitem->{'subclass'}        = $dbh->quote($biblioitem->{'subclass'});
+  $biblioitem->{'publicationyear'} = $dbh->quote($biblioitem->{'publicationyear'});
+  $biblioitem->{'publishercode'}   = $dbh->quote($biblioitem->{'publishercode'});
+  $biblioitem->{'volumedate'}      = $dbh->quote($biblioitem->{'volumedate'});
+  $biblioitem->{'volumeddesc'}     = $dbh->quote($biblioitem->{'volumeddesc'});  $biblioitem->{'illus'}            = $dbh->quote($biblioitem->{'illus'});
+  $biblioitem->{'illus'}	   = $dbh->quote($biblioitem->{'illus'});
+  $biblioitem->{'pages'}           = $dbh->quote($biblioitem->{'pages'});
+  $biblioitem->{'notes'}           = $dbh->quote($biblioitem->{'notes'});
+  $biblioitem->{'size'}            = $dbh->quote($biblioitem->{'size'});
+  $biblioitem->{'place'}           = $dbh->quote($biblioitem->{'place'});
+  $biblioitem->{'lccn'}            = $dbh->quote($biblioitem->{'lccn'});
+  $biblioitem->{'marc'}            = $dbh->quote($biblioitem->{'marc'});
+  
+  $sth->execute;
+  $data       = $sth->fetchrow_arrayref;
+  $bibitemnum = $$data[0] + 1;
+
+  $sth->finish;
+
+  $query = "insert into biblioitems set
+biblioitemnumber = $bibitemnum,
+biblionumber 	 = $biblioitem->{'biblionumber'},
+volume		 = $biblioitem->{'volume'},
+number		 = $biblioitem->{'number'},
+classification   = $biblioitem->{'classification'},
+itemtype         = $biblioitem->{'itemtype'},
+url              = $biblioitem->{'url'},
+isbn		 = $biblioitem->{'isbn'},
+issn		 = $biblioitem->{'issn'},
+dewey		 = $biblioitem->{'dewey'},
+subclass	 = $biblioitem->{'subclass'},
+publicationyear	 = $biblioitem->{'publicationyear'},
+publishercode	 = $biblioitem->{'publishercode'},
+volumedate	 = $biblioitem->{'volumedate'},
+volumeddesc	 = $biblioitem->{'volumeddesc'},
+illus		 = $biblioitem->{'illus'},
+pages		 = $biblioitem->{'pages'},
+notes		 = $biblioitem->{'notes'},
+size		 = $biblioitem->{'size'},
+lccn		 = $biblioitem->{'lccn'},
+marc		 = $biblioitem->{'marc'},
+place		 = $biblioitem->{'place'}";
+
+  $sth = $dbh->prepare($query);
+  $sth->execute;
+
+  $sth->finish;
+  $dbh->disconnect;
+  return($bibitemnum);
+}
+
+sub newsubject {
+  my ($bibnum)=@_;
+  my $dbh=C4Connect;
+  my $query="insert into bibliosubject (biblionumber) values
+  ($bibnum)";
+  my $sth=$dbh->prepare($query);
+#  print $query;
+  $sth->execute;
+  $sth->finish;
+  $dbh->disconnect;
+}
+
+sub newsubtitle {
+  my ($bibnum, $subtitle) = @_;
+  my $dbh   = C4Connect;
+  $subtitle = $dbh->quote($subtitle);
+  my $query = "insert into bibliosubtitle set
+biblionumber = $bibnum,
+subtitle = $subtitle";
+  my $sth   = $dbh->prepare($query);
+
+  $sth->execute;
+
+  $sth->finish;
+  $dbh->disconnect;
+}
+
+sub neworder {
+  my ($bibnum,$title,$ordnum,$basket,$quantity,$listprice,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$sub,$invoice)=@_;
+  if ($budget eq 'now'){
+    $budget="now()";
+  } else {
+    $budget="'2001-07-01'";
+  }
+  if ($sub eq 'yes'){
+    $sub=1;
+  } else {
+    $sub=0;
+  }
+  my $dbh=C4Connect;
+  my $query="insert into aqorders (biblionumber,title,basketno,
+  quantity,listprice,booksellerid,entrydate,requisitionedby,authorisedby,notes,
+  biblioitemnumber,rrp,ecost,gst,unitprice,subscription,booksellerinvoicenumber)
+
+  values
+  ($bibnum,'$title',$basket,$quantity,$listprice,'$supplier',now(),
+  '$who','$who','$notes',$bibitemnum,'$rrp','$ecost','$gst','$cost',
+  '$sub','$invoice')";
+  my $sth=$dbh->prepare($query);
+#  print $query;
+  $sth->execute;
+  $sth->finish;
+  $query="select * from aqorders where
+  biblionumber=$bibnum and basketno=$basket and ordernumber >=$ordnum";
+  $sth=$dbh->prepare($query);
+  $sth->execute;
+  my $data=$sth->fetchrow_hashref;
+  $sth->finish;
+  $ordnum=$data->{'ordernumber'};
+  $query="insert into aqorderbreakdown (ordernumber,bookfundid) values
+  ($ordnum,'$bookfund')";
+  $sth=$dbh->prepare($query);
+#  print $query;
+  $sth->execute;
+  $sth->finish;
+  $dbh->disconnect;
+}
+
+sub delorder {
+  my ($bibnum,$ordnum)=@_;
+  my $dbh=C4Connect;
+  my $query="update aqorders set datecancellationprinted=now()
+  where biblionumber='$bibnum' and
+  ordernumber='$ordnum'";
+  my $sth=$dbh->prepare($query);
+  #print $query;
+  $sth->execute;
+  $sth->finish;
+  my $count=itemcount($bibnum);
+  if ($count == 0){
+    delbiblio($bibnum);
+  }
+  $dbh->disconnect;
+}
+
+sub modorder {
+  my ($title,$ordnum,$quantity,$listprice,$bibnum,$basketno,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$invoice)=@_;
+  my $dbh=C4Connect;
+  my $query="update aqorders set title='$title',
+  quantity='$quantity',listprice='$listprice',basketno='$basketno', 
+  rrp='$rrp',ecost='$ecost',unitprice='$cost',
+  booksellerinvoicenumber='$invoice'
+  where
+  ordernumber=$ordnum and biblionumber=$bibnum";
+  my $sth=$dbh->prepare($query);
+#  print $query;
+  $sth->execute;
+  $sth->finish;
+  $query="update aqorderbreakdown set bookfundid=$bookfund where
+  ordernumber=$ordnum";
+  $sth=$dbh->prepare($query);
+#  print $query;
+  $sth->execute;
+  $sth->finish;
+  $dbh->disconnect;
+}
+
+sub newordernum {
+  my $dbh=C4Connect;
+  my $query="Select max(ordernumber) from aqorders";
+  my $sth=$dbh->prepare($query);
+  $sth->execute;
+  my $data=$sth->fetchrow_arrayref;
+  my $ordnum=$$data[0];
+  $ordnum++;
+  $sth->finish;
+  $dbh->disconnect;
+  return($ordnum);
+}
+
+sub receiveorder {
+  my ($biblio,$ordnum,$quantrec,$user,$cost,$invoiceno,$bibitemno,$freight,$bookfund,$rrp)=@_;
+  my $dbh=C4Connect;
+  my $query="update aqorders set quantityreceived='$quantrec',
+  datereceived=now(),booksellerinvoicenumber='$invoiceno',
+  biblioitemnumber=$bibitemno,unitprice='$cost',freight='$freight',
+  rrp='$rrp'
+  where biblionumber=$biblio and ordernumber=$ordnum
+  ";
+#  print $query;
+  my $sth=$dbh->prepare($query);
+  $sth->execute;
+  $sth->finish;
+  $query="update aqorderbreakdown set bookfundid=$bookfund where
+  ordernumber=$ordnum";
+  $sth=$dbh->prepare($query);
+#  print $query;
+  $sth->execute;
+  $sth->finish;  
+  $dbh->disconnect;
+}
+sub updaterecorder{
+  my($biblio,$ordnum,$user,$cost,$bookfund,$rrp)=@_;
+  my $dbh=C4Connect;
+  my $query="update aqorders set
+  unitprice='$cost', rrp='$rrp'
+  where biblionumber=$biblio and ordernumber=$ordnum
+  ";
+#  print $query;
+  my $sth=$dbh->prepare($query);
+  $sth->execute;
+  $sth->finish;
+  $query="update aqorderbreakdown set bookfundid=$bookfund where
+  ordernumber=$ordnum";
+  $sth=$dbh->prepare($query);
+#  print $query;
+  $sth->execute;
+  $sth->finish;  
+  $dbh->disconnect;
+}
+
+sub curconvert {
+  my ($currency,$price)=@_;
+  my $dbh=C4Connect;
+  my $query="Select rate from currency where currency='$currency'";
+  my $sth=$dbh->prepare($query);
+  $sth->execute;
+  my $data=$sth->fetchrow_hashref;
+  $sth->finish;
+  $dbh->disconnect;
+  my $cur=$data->{'rate'};
+  if ($cur==0){
+    $cur=1;
+  }
+  my $price=$price / $cur;
+  return($price);
+}
+
+sub getcurrencies {
+  my $dbh=C4Connect;
+  my $query="Select * from currency";
+  my $sth=$dbh->prepare($query);
+  $sth->execute;
+  my @results;
+  my $i=0;
+  while (my $data=$sth->fetchrow_hashref){
+    $results[$i]=$data;
+    $i++;
+  }
+  $sth->finish;
+  $dbh->disconnect;
+  return($i,\@results);
+} 
+
+sub getcurrency {
+  my ($cur)=@_;
+  my $dbh=C4Connect;
+  my $query="Select * from currency where currency='$cur'";
+  my $sth=$dbh->prepare($query);
+  $sth->execute;
+
+  my $data=$sth->fetchrow_hashref;
+  $sth->finish;
+  $dbh->disconnect;
+  return($data);
+} 
+
+sub updatecurrencies {
+  my ($currency,$rate)=@_;
+  my $dbh=C4Connect;
+  my $query="update currency set rate=$rate where currency='$currency'";
+  my $sth=$dbh->prepare($query);
+  $sth->execute;
+  $sth->finish;
+  $dbh->disconnect;
+} 
+
+sub updatesup {
+   my ($data)=@_;
+   my $dbh=C4Connect;
+   my $query="Update aqbooksellers set
+   name='$data->{'name'}',address1='$data->{'address1'}',address2='$data->{'address2'}',
+   address3='$data->{'address3'}',address4='$data->{'address4'}',postal='$data->{'postal'}',
+   phone='$data->{'phone'}',fax='$data->{'fax'}',url='$data->{'url'}',
+   contact='$data->{'contact'}',contpos='$data->{'contpos'}',
+   contphone='$data->{'contphone'}', contfax='$data->{'contfax'}', contaltphone=
+   '$data->{'contaltphone'}', contemail='$data->{'contemail'}', contnotes=
+   '$data->{'contnotes'}', active=$data->{'active'},
+   listprice='$data->{'listprice'}', invoiceprice='$data->{'invoiceprice'}',
+   gstreg=$data->{'gstreg'}, listincgst=$data->{'listincgst'},
+   invoiceincgst=$data->{'invoiceincgst'}, specialty='$data->{'specialty'}',
+   discount='$data->{'discount'}',invoicedisc='$data->{'invoicedisc'}',
+   nocalc='$data->{'nocalc'}'
+   where id='$data->{'id'}'";
+   my $sth=$dbh->prepare($query);
+   $sth->execute;
+   $sth->finish;
+   $dbh->disconnect;
+#   print $query;
+}
+
+sub insertsup {
+  my ($data)=@_;
+  my $dbh=C4Connect;
+  my $sth=$dbh->prepare("Select max(id) from aqbooksellers");
+  $sth->execute;
+  my $data2=$sth->fetchrow_hashref;
+  $sth->finish;
+  $data2->{'max(id)'}++;
+  $sth=$dbh->prepare("Insert into aqbooksellers (id) values ($data2->{'max(id)'})");
+  $sth->execute;
+  $sth->finish;
+  $data->{'id'}=$data2->{'max(id)'};
+  $dbh->disconnect;
+  updatesup($data);
+  return($data->{'id'});
+}
+
+
+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;
+  $data       = $sth->fetchrow_hashref;
+  $itemnumber = $data->{'max(itemnumber)'} + 1;
+  $sth->finish;
+  
+  $item->{'booksellerid'}     = $dbh->quote($item->{'booksellerid'});
+  $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);
+    $barcode = $dbh->quote($barcode);
+    $query   = "Insert into items set
+itemnumber           = $itemnumber,
+biblionumber         = $item->{'biblionumber'},
+biblioitemnumber     = $item->{'biblioitemnumber'},
+barcode              = $barcode,
+booksellerid         = $item->{'booksellerid'},
+dateaccessioned      = NOW(),
+homebranch           = $item->{'homebranch'},
+holdingbranch        = $item->{'homebranch'},
+price                = $item->{'price'},
+replacementprice     = $item->{'replacementprice'},
+replacementpricedate = NOW(),
+itemnotes            = $item->{'itemnotes'}";
+
+    if ($item->{'loan'}) {
+      $query .= ",
+notforloan           = $item->{'loan'}";
+    } # if
+
+    $sth = $dbh->prepare($query);
+    $sth->execute;
+
+    $error .= $sth->errstr;
+
+    $sth->finish;
+    $itemnumber++;
+  } # for
+
+  $dbh->disconnect;
+  return($error);
+}
+
+sub checkitems{
+  my ($count,@barcodes)=@_;
+  my $dbh=C4Connect;
+  my $error;
+  for (my $i=0;$i<$count;$i++){
+    $barcodes[$i]=uc $barcodes[$i];
+    my $query="Select * from items where barcode='$barcodes[$i]'";
+    my $sth=$dbh->prepare($query);
+    $sth->execute;
+    if (my $data=$sth->fetchrow_hashref){
+      $error.=" Duplicate Barcode: $barcodes[$i]";
+    }
+    $sth->finish;
+  }
+  $dbh->disconnect;
+  return($error);
+}
+
+sub moditem {
+  my ($loan,$itemnum,$bibitemnum,$barcode,$notes,$homebranch,$lost,$wthdrawn,$replacement)=@_;
+  my $dbh=C4Connect;
+  my $query="update items set biblioitemnumber=$bibitemnum,
+  barcode='$barcode',itemnotes='$notes'
+  where itemnumber=$itemnum";
+  if ($barcode eq ''){
+    $query="update items set biblioitemnumber=$bibitemnum,notforloan=$loan where itemnumber=$itemnum";
+  }
+  if ($lost ne ''){
+    $query="update items set biblioitemnumber=$bibitemnum,
+      barcode='$barcode',itemnotes='$notes',homebranch='$homebranch',
+      itemlost='$lost',wthdrawn='$wthdrawn' where itemnumber=$itemnum";
+  }
+  if ($replacement ne ''){
+    $query=~ s/ where/,replacementprice='$replacement' where/;
+  }
+
+  my $sth=$dbh->prepare($query);
+  $sth->execute;
+  $sth->finish;
+  $dbh->disconnect;
+}
+
+sub updatecost{
+  my($price,$rrp,$itemnum)=@_;
+  my $dbh=C4Connect;
+  my $query="update items set price='$price',replacementprice='$rrp'
+  where itemnumber=$itemnum";
+  my $sth=$dbh->prepare($query);
+  $sth->execute;
+  $sth->finish;
+  $dbh->disconnect;
+}
+sub countitems{
+  my ($bibitemnum)=@_;
+  my $dbh=C4Connect;
+  my $query="Select count(*) from items where biblioitemnumber='$bibitemnum'";
+  my $sth=$dbh->prepare($query);
+  $sth->execute;
+  my $data=$sth->fetchrow_hashref;
+  $sth->finish;
+  $dbh->disconnect;
+  return($data->{'count(*)'});
+}
+
+sub findall {
+  my ($biblionumber)=@_;
+  my $dbh=C4Connect;
+  my $query="Select * from biblioitems,items,itemtypes where 
+  biblioitems.biblionumber=$biblionumber 
+  and biblioitems.biblioitemnumber=items.biblioitemnumber and
+  itemtypes.itemtype=biblioitems.itemtype
+  order by items.biblioitemnumber";
+  my $sth=$dbh->prepare($query);
+  $sth->execute;
+  my @results;
+  my $i;
+  while (my $data=$sth->fetchrow_hashref){
+    $results[$i]=$data;
+    $i++;
+  }
+  $sth->finish;
+  $dbh->disconnect;
+  return(@results);
+}
+
+sub needsmod{
+  my ($bibitemnum,$itemtype)=@_;
+  my $dbh=C4Connect;
+  my $query="Select * from biblioitems where biblioitemnumber=$bibitemnum
+  and itemtype='$itemtype'";
+  my $sth=$dbh->prepare($query);
+  $sth->execute;
+  my $result=0;
+  if (my $data=$sth->fetchrow_hashref){
+    $result=1;
+  }
+  $sth->finish;
+  $dbh->disconnect;
+  return($result);
+}
+
+sub delitem{
+  my ($itemnum)=@_;
+  my $dbh=C4Connect;
+  my $query="select * from items where itemnumber=$itemnum";
+  my $sth=$dbh->prepare($query);
+  $sth->execute;
+  my @data=$sth->fetchrow_array;
+  $sth->finish;
+  $query="Insert into deleteditems values (";
+  foreach my $temp (@data){
+    $query=$query."'$temp',";
+  }
+  $query=~ s/\,$/\)/;
+#  print $query;
+  $sth=$dbh->prepare($query);
+  $sth->execute;
+  $sth->finish;
+  $query = "Delete from items where itemnumber=$itemnum";
+  $sth=$dbh->prepare($query);
+  $sth->execute;
+  $sth->finish;
+  $dbh->disconnect;
+}
+sub deletebiblioitem {
+    my ($biblioitemnumber) = @_;
+    my $dbh   = C4Connect;
+    my $query = "Select * from biblioitems
+where biblioitemnumber = $biblioitemnumber";
+    my $sth   = $dbh->prepare($query);
+    my @results;
+
+    $sth->execute;
+  
+    if (@results = $sth->fetchrow_array) {
+
+        $query = "Insert into deletedbiblioitems values (";
+        foreach my $value (@results) {
+            $value  = $dbh->quote($value);
+            $query .= "$value,";
+        } # foreach
+
+        $query =~ s/\,$/\)/;
+        $dbh->do($query);
+
+        $query = "Delete from biblioitems
+where biblioitemnumber = $biblioitemnumber";
+        $dbh->do($query);
+    } # if
+
+    $sth->finish;
+
+# Now delete all the items attached to the biblioitem
+
+    $query = "Select * from items where biblioitemnumber = $biblioitemnumber";
+    $sth   = $dbh->prepare($query);
+
+    $sth->execute;
+
+    while (@results = $sth->fetchrow_array) {
+
+	$query = "Insert into deleteditems values (";
+	foreach my $value (@results) {
+	    $value  = $dbh->quote($value);
+	    $query .= "$value,";
+	} # foreach
+
+	$query =~ s/\,$/\)/;
+	$dbh->do($query);
+    } # while
+
+    $sth->finish;
+
+    $query = "Delete from items where biblioitemnumber = $biblioitemnumber";
+    $dbh->do($query);
+    
+    $dbh->disconnect;
+} # sub deletebiblioitem
+
+
+sub delbiblio{
+  my ($biblio)=@_;
+  my $dbh=C4Connect;
+  my $query="select * from biblio where biblionumber=$biblio";
+  my $sth=$dbh->prepare($query);
+  $sth->execute;
+  if (my @data=$sth->fetchrow_array){
+    $sth->finish;
+    $query="Insert into deletedbiblio values (";
+    foreach my $temp (@data){
+      $temp=~ s/\'/\\\'/g;
+      $query=$query."'$temp',";
+    }
+    $query=~ s/\,$/\)/;
+#   print $query;
+    $sth=$dbh->prepare($query);
+    $sth->execute;
+    $sth->finish;
+    $query = "Delete from biblio where biblionumber=$biblio";
+    $sth=$dbh->prepare($query);
+    $sth->execute;
+    $sth->finish;
+  }
+
+  $sth->finish;
+  $dbh->disconnect;
+}
+
+
+sub getitemtypes {
+  my $dbh   = C4Connect;
+  my $query = "select * from itemtypes";
+  my $sth   = $dbh->prepare($query);
+    # || die "Cannot prepare $query" . $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 getitemtypes
+
+
+sub getbiblio {
+    my ($biblionumber) = @_;
+    my $dbh   = C4Connect;
+    my $query = "Select * from biblio where biblionumber = $biblionumber";
+    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 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 getbiblioitembybiblionumber {
+    my ($biblionumber) = @_;
+    my $dbh   = C4Connect;
+    my $query = "Select * from biblioitems where biblionumber =
+$biblionumber";
+    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
+
+
+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 biblio.* from biblio, biblioitems where
+biblio.biblionumber = biblioitems.biblionumber
+and 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 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
+
+
+sub addwebsite {
+    my ($website) = @_;
+    my $dbh = C4Connect;
+    my $query;
+    
+    $website->{'biblionumber'} = $dbh->quote($website->{'biblionumber'});
+    $website->{'title'}        = $dbh->quote($website->{'title'});
+    $website->{'description'}  = $dbh->quote($website->{'description'});
+    $website->{'url'}          = $dbh->quote($website->{'url'});
+    
+    $query = "Insert into websites set
+biblionumber = $website->{'biblionumber'},
+title        = $website->{'title'},
+description  = $website->{'description'},
+url          = $website->{'url'}";
+    
+    $dbh->do($query);
+    
+    $dbh->disconnect;
+} # sub website
+
+
+sub updatewebsite {
+    my ($website) = @_;
+    my $dbh = C4Connect;
+    my $query;
+    
+    $website->{'title'}      = $dbh->quote($website->{'title'});
+    $website->{'description'} = $dbh->quote($website->{'description'});
+    $website->{'url'}        = $dbh->quote($website->{'url'});
+    
+    $query = "Update websites set
+title       = $website->{'title'},
+description = $website->{'description'},
+url         = $website->{'url'}
+where websitenumber = $website->{'websitenumber'}";
+
+    $dbh->do($query);
+    
+    $dbh->disconnect;
+} # sub updatewebsite
+
+
+sub deletewebsite {
+    my ($websitenumber) = @_;
+    my $dbh = C4Connect;
+    my $query = "Delete from websites where websitenumber = $websitenumber";
+    
+    $dbh->do($query);
+    
+    $dbh->disconnect;
+} # sub deletewebsite
+
 END { }       # module clean-up code here (global destructor)
diff --git a/C4/test.pl b/C4/test.pl
new file mode 100755
index 0000000000..5d0a0a8d66
--- /dev/null
+++ b/C4/test.pl
@@ -0,0 +1,59 @@
+#!/usr/bin/perl
+use strict;
+require Exporter;
+use C4::Database;
+use C4::Catalogue;
+
+my $dbh=&C4Connect;
+$dbh->do("delete from marc_subfield_table");
+$dbh->do("delete from marc_blob_subfield");
+&addSubfield(1,'001','a','1 - This is a value',1);
+&addSubfield(1,'001','b','2 - This is another value',1);
+&addSubfield(1,'001','c',"3 - This is a value very very long. I try to make it longer than 255 char. I need to add something else. will it be long enough now... I'm not sure. That's why i continue to add a few word to this very important sentence. Now I hope it will be enough... Oh, not it need some more characters. So i add stupid strings : xxxxxxxxxxxxxxx dddddddddddddddddddd eeeeeeeeeeeeeeeeeeeeeee rrrrrrrrrrrrrrrrrrr ffffffffffffffffff",1);
+&addSubfield(1,'001','d',"4 - This is another value very very long. I try to make it longer than 255 char. I need to add something else. will it be long enough now... I'm not sure. That's why i continue to add a few word to this very important sentence. Now I hope it will be enough... Oh, not it need some more characters. So i add stupid strings : xxxxxxxxxxxxxxx dddddddddddddddddddd eeeeeeeeeeeeeeeeeeeeeee rrrrrrrrrrrrrrrrrrr ffffffffffffffffff",1);
+print "change 1\n";
+&changeSubfield(1,"1new - this is a changed value");
+print "change 2\n";
+&changeSubfield(2,"2new - go from short to long subfield... uuuuuuuuuuuuuuuuuuuuuuuu yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy tttttttttttttttttttttttttttttttt rrrrrrrrrrrrrrrrrrrrrr eeeeeeeeeeeeeeeeeeeeeeeee zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq ssssssssssssssssssssssssss ddddddddddddddddddddddddddddddd fffffffffffffffffffffffff ggggggggggggggggggggggggg hhhhhhhhhhhhhhhhhhhhhhhhhh jjjjjjjjjjjjjjjjjjjjjkkkkkkkkkkkkkkkkkkkkkkkk");
+print "change 3\n";
+&changeSubfield(3,"3new - go from long to short subfield...");
+print "change 4\n";
+&changeSubfield(4,"4new - stay with blob subfield...uuuuuuuuuuuuuuuuuuuuuuuu yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy tttttttttttttttttttttttttttttttt rrrrrrrrrrrrrrrrrrrrrr eeeeeeeeeeeeeeeeeeeeeeeee zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq ssssssssssssssssssssssssss ddddddddddddddddddddddddddddddd fffffffffffffffffffffffff ggggggggggggggggggggggggg hhhhhhhhhhhhhhhhhhhhhhhhhh jjjjjjjjjjjjjjjjjjjjjkkkkkkkkkkkkkkkkkkkkkkkk");
+my $x= &findSubfield(1,'001','a','',1);
+print "subfieldid : $x\n";
+my $marcstru={};
+$marcstru->{bibid}=58973;
+$marcstru->{tags}->{1}->{tag}='110';
+$marcstru->{tags}->{1}->{tagorder}=1;
+$marcstru->{tags}->{1}->{subfields}->{1}->{mark}='a';
+$marcstru->{tags}->{1}->{subfields}->{1}->{subfieldorder}=1;
+$marcstru->{tags}->{1}->{subfields}->{1}->{value}='this is a test';
+$marcstru->{tags}->{1}->{subfields}->{2}->{mark}='b';
+$marcstru->{tags}->{1}->{subfields}->{2}->{subfieldorder}=1;
+$marcstru->{tags}->{1}->{subfields}->{2}->{value}='this is another test';
+
+$marcstru->{tags}->{2}->{tag}='220';
+$marcstru->{tags}->{2}->{tagorder}=1;
+$marcstru->{tags}->{2}->{subfields}->{1}->{mark}='a';
+$marcstru->{tags}->{2}->{subfields}->{1}->{subfieldorder}=1;
+$marcstru->{tags}->{2}->{subfields}->{1}->{value}='this is a test for 220';
+$marcstru->{tags}->{2}->{subfields}->{2}->{mark}='b';
+$marcstru->{tags}->{2}->{subfields}->{2}->{subfieldorder}=1;
+$marcstru->{tags}->{2}->{subfields}->{2}->{value}='this is another test for 220';
+$marcstru->{tags}->{2}->{subfields}->{3}->{mark}='b';
+$marcstru->{tags}->{2}->{subfields}->{3}->{subfieldorder}=2;
+$marcstru->{tags}->{2}->{subfields}->{3}->{value}='this is a third test for 220';
+
+$marcstru->{tags}->{3}->{tag}='330';
+$marcstru->{tags}->{3}->{tagorder}=1;
+$marcstru->{tags}->{3}->{subfields}->{1}->{mark}='a';
+$marcstru->{tags}->{3}->{subfields}->{1}->{subfieldorder}=1;
+$marcstru->{tags}->{3}->{subfields}->{1}->{value}='this is a test for 330';
+$marcstru->{tags}->{3}->{subfields}->{2}->{mark}='b';
+$marcstru->{tags}->{3}->{subfields}->{2}->{subfieldorder}=1;
+$marcstru->{tags}->{3}->{subfields}->{2}->{value}='this is another test for 330';
+$marcstru->{tags}->{3}->{subfields}->{3}->{mark}='b';
+$marcstru->{tags}->{3}->{subfields}->{3}->{subfieldorder}=2;
+$marcstru->{tags}->{3}->{subfields}->{3}->{value}='this is a third test for 330';
+
+&addMarcBiblio($marcstru);
-- 
2.39.5