X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=C4%2FAcquisition.pm;h=c1b837bc433dbd6bd1bb9477aa7c5584b23820b1;hb=7de29cff5faa4f8aeec987ae06c6d3270a1c2932;hp=de67a3ca30aee7b4d6ef0f94187b8ffe8df34275;hpb=787c184dc7ab50156666b5ee7fa7cbaeb0c18a45;p=koha.git diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm index de67a3ca30..c1b837bc43 100644 --- a/C4/Acquisition.pm +++ b/C4/Acquisition.pm @@ -13,20 +13,101 @@ package C4::Acquisition; # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR # A PARTICULAR PURPOSE. See the GNU General Public License for more details. # -# You should have received a copy of the GNU General Public License along with -# Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place, -# Suite 330, Boston, MA 02111-1307 USA +# You should have received a copy of the GNU General Public License along +# with Koha; if not, write to the Free Software Foundation, Inc., +# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. + use strict; -require Exporter; +use warnings; use C4::Context; +use C4::Debug; +use C4::Dates qw(format_date format_date_in_iso); use MARC::Record; -# use C4::Biblio; +use C4::Suggestions; +use C4::Biblio; +use C4::Debug; +use C4::SQLHelper qw(InsertInTable); + +use Time::localtime; +use HTML::Entities; use vars qw($VERSION @ISA @EXPORT); -# set the version for version checking -$VERSION = 0.01; +BEGIN { + # set the version for version checking + $VERSION = 3.01; + require Exporter; + @ISA = qw(Exporter); + @EXPORT = qw( + &GetBasket &NewBasket &CloseBasket &DelBasket &ModBasket + &GetBasketAsCSV + &GetBasketsByBookseller &GetBasketsByBasketgroup + + &ModBasketHeader + + &ModBasketgroup &NewBasketgroup &DelBasketgroup &GetBasketgroup &CloseBasketgroup + &GetBasketgroups &ReOpenBasketgroup + + &NewOrder &DelOrder &ModOrder &GetPendingOrders &GetOrder &GetOrders + &GetOrderNumber &GetLateOrders &GetOrderFromItemnumber + &SearchOrder &GetHistory &GetRecentAcqui + &ModReceiveOrder &ModOrderBiblioitemNumber + + &NewOrderItem &ModOrderItem + + &GetParcels &GetParcel + &GetContracts &GetContract + + &GetItemnumbersFromOrder + ); +} + + + + + +sub GetOrderFromItemnumber { + my ($itemnumber) = @_; + my $dbh = C4::Context->dbh; + my $query = qq| + + SELECT * from aqorders LEFT JOIN aqorders_items + ON ( aqorders.ordernumber = aqorders_items.ordernumber ) + WHERE itemnumber = ? |; + + my $sth = $dbh->prepare($query); + +# $sth->trace(3); + + $sth->execute($itemnumber); + + my $order = $sth->fetchrow_hashref; + return ( $order ); + +} + +# Returns the itemnumber(s) associated with the ordernumber given in parameter +sub GetItemnumbersFromOrder { + my ($ordernumber) = @_; + my $dbh = C4::Context->dbh; + my $query = "SELECT itemnumber FROM aqorders_items WHERE ordernumber=?"; + my $sth = $dbh->prepare($query); + $sth->execute($ordernumber); + my @tab; + + while (my $order = $sth->fetchrow_hashref) { + push @tab, $order->{'itemnumber'}; + } + + return @tab; + +} + + + + + =head1 NAME @@ -34,344 +115,583 @@ C4::Acquisition - Koha functions for dealing with orders and acquisitions =head1 SYNOPSIS - use C4::Acquisition; +use C4::Acquisition; =head1 DESCRIPTION The functions in this module deal with acquisitions, managing book -orders, converting money to different currencies, and so forth. +orders, basket and parcels. =head1 FUNCTIONS -=over 2 +=head2 FUNCTIONS ABOUT BASKETS + +=head3 GetBasket + + $aqbasket = &GetBasket($basketnumber); + +get all basket informations in aqbasket for a given basket + +B informations for a given basket returned as a hashref. =cut -@ISA = qw(Exporter); -@EXPORT = qw( - &getbasket &getbasketcontent &newbasket &closebasket +sub GetBasket { + my ($basketno) = @_; + my $dbh = C4::Context->dbh; + my $query = " + SELECT aqbasket.*, + concat( b.firstname,' ',b.surname) AS authorisedbyname, + b.branchcode AS branch + FROM aqbasket + LEFT JOIN borrowers b ON aqbasket.authorisedby=b.borrowernumber + WHERE basketno=? + "; + my $sth=$dbh->prepare($query); + $sth->execute($basketno); + my $basket = $sth->fetchrow_hashref; + return ( $basket ); +} - &getorders &getallorders &getrecorders - &getorder &neworder &delorder - &ordersearch &histsearch - &modorder &getsingleorder &invoice &receiveorder - &updaterecorder &newordernum +#------------------------------------------------------------# - &bookfunds &curconvert &getcurrencies &bookfundbreakdown - &updatecurrencies &getcurrency +=head3 NewBasket - &branches &updatesup &insertsup - &bookseller &breakdown -); + $basket = &NewBasket( $booksellerid, $authorizedby, $basketname, + $basketnote, $basketbooksellernote, $basketcontractnumber ); -# -# -# -# BASKETS -# -# -# -=item getbasket +Create a new basket in aqbasket table - $aqbasket = &getbasket($basketnumber); +=over + +=item C<$booksellerid> is a foreign key in the aqbasket table + +=item C<$authorizedby> is the username of who created the basket + +=back + +The other parameters are optional, see ModBasketHeader for more info on them. -get all basket informations in aqbasket for a given basket =cut -sub getbasket { - my ($basketno)=@_; - my $dbh=C4::Context->dbh; - my $sth=$dbh->prepare("select aqbasket.*,borrowers.firstname+' '+borrowers.surname as authorisedbyname, borrowers.branchcode as branch from aqbasket left join borrowers on aqbasket.authorisedby=borrowers.borrowernumber where basketno=?"); - $sth->execute($basketno); - return($sth->fetchrow_hashref); +# FIXME : this function seems to be unused. + +sub NewBasket { + my ( $booksellerid, $authorisedby, $basketname, $basketnote, $basketbooksellernote, $basketcontractnumber ) = @_; + my $dbh = C4::Context->dbh; + my $query = " + INSERT INTO aqbasket + (creationdate,booksellerid,authorisedby) + VALUES (now(),'$booksellerid','$authorisedby') + "; + my $sth = + $dbh->do($query); +#find & return basketno MYSQL dependant, but $dbh->last_insert_id always returns null :-( + my $basket = $dbh->{'mysql_insertid'}; + ModBasketHeader($basket, $basketname || '', $basketnote || '', $basketbooksellernote || '', $basketcontractnumber || undef); + return $basket; } -=item getbasketcontent +#------------------------------------------------------------# - ($count, @orders) = &getbasketcontent($basketnumber, $booksellerID); +=head3 CloseBasket -Looks up the pending (non-cancelled) orders with the given basket -number. If C<$booksellerID> is non-empty, only orders from that seller -are returned. + &CloseBasket($basketno); -C<&basket> returns a two-element array. C<@orders> is an array of -references-to-hash, whose keys are the fields from the aqorders, -biblio, and biblioitems tables in the Koha database. C<$count> is the -number of elements in C<@orders>. - -=cut -#' -sub getbasketcontent { - my ($basketno,$supplier,$orderby)=@_; - my $dbh = C4::Context->dbh; - my $query="Select biblio.*,biblioitems.*,aqorders.*,aqorderbreakdown.*,biblio.title from aqorders,biblio,biblioitems - left join aqorderbreakdown on aqorderbreakdown.ordernumber=aqorders.ordernumber - 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'"; - } - - $orderby="biblioitems.publishercode" unless $orderby; - $query.=" order by $orderby"; - 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; - return($i,@results); +close a basket (becomes unmodifiable,except for recieves) + +=cut + +sub CloseBasket { + my ($basketno) = @_; + my $dbh = C4::Context->dbh; + my $query = " + UPDATE aqbasket + SET closedate=now() + WHERE basketno=? + "; + my $sth = $dbh->prepare($query); + $sth->execute($basketno); } -=item newbasket +#------------------------------------------------------------# - $basket = &newbasket(); +=head3 GetBasketAsCSV + + &GetBasketAsCSV($basketno); + +Export a basket as CSV -Create a new basket in aqbasket table =cut -sub newbasket { - my ($booksellerid,$authorisedby) = @_; - my $dbh = C4::Context->dbh; - my $sth=$dbh->do("insert into aqbasket (creationdate,booksellerid,authorisedby) values(now(),'$booksellerid','$authorisedby')"); - #find & return basketno MYSQL dependant, but $dbh->last_insert_id always returns null :-( - my $basket = $dbh->{'mysql_insertid'}; - return($basket); +sub GetBasketAsCSV { + my ($basketno) = @_; + my $basket = GetBasket($basketno); + my @orders = GetOrders($basketno); + my $contract = GetContract($basket->{'contractnumber'}); + my $csv = Text::CSV->new(); + my $output; + + # TODO: Translate headers + my @headers = qw(contractname ordernumber line entrydate isbn author title publishercode collectiontitle notes quantity rrp); + + $csv->combine(@headers); + $output = $csv->string() . "\n"; + + my @rows; + foreach my $order (@orders) { + my @cols; + my $bd = GetBiblioData($order->{'biblionumber'}); + push(@cols, + $contract->{'contractname'}, + $order->{'ordernumber'}, + $order->{'entrydate'}, + $order->{'isbn'}, + $bd->{'author'}, + $bd->{'title'}, + $bd->{'publishercode'}, + $bd->{'collectiontitle'}, + $order->{'notes'}, + $order->{'quantity'}, + $order->{'rrp'}, + ); + push (@rows, \@cols); + } + + # Sort by publishercode + # TODO: Sort by publishercode then by title + @rows = sort { @$a[7] cmp @$b[7] } @rows; + + foreach my $row (@rows) { + $csv->combine(@$row); + $output .= $csv->string() . "\n"; + + } + + return $output; + } -=item closebasket - &newbasket($basketno); +=head3 CloseBasketgroup + + &CloseBasketgroup($basketgroupno); + +close a basketgroup -close a basket (becomes unmodifiable,except for recieves =cut -sub closebasket { - my ($basketno) = @_; - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("update aqbasket set closedate=now() where basketno=?"); - $sth->execute($basketno); +sub CloseBasketgroup { + my ($basketgroupno) = @_; + my $dbh = C4::Context->dbh; + my $sth = $dbh->prepare(" + UPDATE aqbasketgroups + SET closed=1 + WHERE id=? + "); + $sth->execute($basketgroupno); } -=item neworder +#------------------------------------------------------------# - &neworder($basket, $biblionumber, $title, $quantity, $listprice, - $booksellerid, $who, $notes, $bookfund, $biblioitemnumber, $rrp, - $ecost, $gst, $budget, $unitprice, $subscription, - $booksellerinvoicenumber); +=head3 ReOpenBaskergroup($basketgroupno) -Adds a new order to the database. Any argument that isn't described -below is the new value of the field with the same name in the aqorders -table of the Koha database. + &ReOpenBaskergroup($basketgroupno); -C<$ordnum> is a "minimum order number." After adding the new entry to -the aqorders table, C<&neworder> finds the first entry in aqorders -with order number greater than or equal to C<$ordnum>, and adds an -entry to the aqorderbreakdown table, with the order number just found, -and the book fund ID of the newly-added order. - -C<$budget> is effectively ignored. - -C<$subscription> may be either "yes", or anything else for "no". - -=cut -#' -sub neworder { - my ($basketno,$bibnum,$title,$quantity,$listprice,$booksellerid,$authorisedby,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$sub,$invoice,$sort1,$sort2)=@_; - if ($budget eq 'now'){ - $budget="now()"; - } else { - $budget="'2001-07-01'"; - } - if ($sub eq 'yes'){ - $sub=1; - } else { - $sub=0; - } - # if $basket empty, it's also a new basket, create it - unless ($basketno) { - $basketno=newbasket($booksellerid,$authorisedby); - } - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("insert into aqorders - (biblionumber,title,basketno,quantity,listprice,notes, - biblioitemnumber,rrp,ecost,gst,unitprice,subscription,sort1,sort2) - values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); - $sth->execute($bibnum,$title,$basketno,$quantity,$listprice,$notes, - $bibitemnum,$rrp,$ecost,$gst,$cost,$sub,$sort1,$sort2); - $sth->finish; - #get ordnum MYSQL dependant, but $dbh->last_insert_id returns null - my $ordnum = $dbh->{'mysql_insertid'}; - $sth=$dbh->prepare("insert into aqorderbreakdown (ordernumber,bookfundid) values - (?,?)"); - $sth->execute($ordnum,$bookfund); - $sth->finish; - return $basketno; -} - -=item delorder - - &delorder($biblionumber, $ordernumber); +reopen a basketgroup -Cancel the order with the given order and biblio numbers. It does not -delete any entries in the aqorders table, it merely marks them as -cancelled. +=cut + +sub ReOpenBasketgroup { + my ($basketgroupno) = @_; + my $dbh = C4::Context->dbh; + my $sth = $dbh->prepare(" + UPDATE aqbasketgroups + SET closed=0 + WHERE id=? + "); + $sth->execute($basketgroupno); +} + +#------------------------------------------------------------# + + +=head3 DelBasket + + &DelBasket($basketno); + +Deletes the basket that has basketno field $basketno in the aqbasket table. + +=over + +=item C<$basketno> is the primary key of the basket in the aqbasket table. + +=back =cut -#' -sub delorder { - my ($bibnum,$ordnum)=@_; - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("update aqorders set datecancellationprinted=now() - where biblionumber=? and ordernumber=?"); - $sth->execute($bibnum,$ordnum); - $sth->finish; + +sub DelBasket { + my ( $basketno ) = @_; + my $query = "DELETE FROM aqbasket WHERE basketno=?"; + my $dbh = C4::Context->dbh; + my $sth = $dbh->prepare($query); + $sth->execute($basketno); + $sth->finish; } -=item modorder +#------------------------------------------------------------# - &modorder($title, $ordernumber, $quantity, $listprice, - $biblionumber, $basketno, $supplier, $who, $notes, - $bookfundid, $bibitemnum, $rrp, $ecost, $gst, $budget, - $unitprice, $booksellerinvoicenumber); +=head3 ModBasket -Modifies an existing order. Updates the order with order number -C<$ordernumber> and biblionumber C<$biblionumber>. All other arguments -update the fields with the same name in the aqorders table of the Koha -database. - -Entries with order number C<$ordernumber> in the aqorderbreakdown -table are also updated to the new book fund ID. - -=cut -#' -sub modorder { - my ($title,$ordnum,$quantity,$listprice,$bibnum,$basketno,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$invoice,$sort1,$sort2)=@_; - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("update aqorders set title=?, - quantity=?,listprice=?,basketno=?, - rrp=?,ecost=?,unitprice=?,booksellerinvoicenumber=?, - notes=?,sort1=?, sort2=? - where - ordernumber=? and biblionumber=?"); - $sth->execute($title,$quantity,$listprice,$basketno,$rrp,$ecost,$cost,$invoice,$notes,$sort1,$sort2,$ordnum,$bibnum); - $sth->finish; - $sth=$dbh->prepare("update aqorderbreakdown set bookfundid=? where - ordernumber=?"); - unless ($sth->execute($bookfund,$ordnum)) { # zero rows affected [Bug 734] - my $query="insert into aqorderbreakdown (ordernumber,bookfundid) values (?,?)"; - $sth=$dbh->prepare($query); - $sth->execute($ordnum,$bookfund); - } - $sth->finish; -} - -=item newordernum - - $order = &newordernum(); - -Finds the next unused order number in the aqorders table of the Koha -database, and returns it. - -=cut -#' -# FIXME - Race condition -sub newordernum { - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("Select max(ordernumber) from aqorders"); - $sth->execute; - my $data=$sth->fetchrow_arrayref; - my $ordnum=$$data[0]; - $ordnum++; - $sth->finish; - return($ordnum); -} - -=item receiveorder - - &receiveorder($biblionumber, $ordernumber, $quantityreceived, $user, - $unitprice, $booksellerinvoicenumber, $biblioitemnumber, - $freight, $bookfund, $rrp); + &ModBasket($basketinfo); -Updates an order, to reflect the fact that it was received, at least -in part. All arguments not mentioned below update the fields with the -same name in the aqorders table of the Koha database. +Modifies a basket, using a hashref $basketinfo for the relevant information, only $basketinfo->{'basketno'} is required. -Updates the order with bibilionumber C<$biblionumber> and ordernumber -C<$ordernumber>. +=over -Also updates the book fund ID in the aqorderbreakdown table. +=item C<$basketno> is the primary key of the basket in the aqbasket table. + +=back =cut -#' -sub receiveorder { - my ($biblio,$ordnum,$quantrec,$user,$cost,$invoiceno,$freight,$rrp)=@_; - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("update aqorders set quantityreceived=?,datereceived=now(),booksellerinvoicenumber=?, - unitprice=?,freight=?,rrp=? - where biblionumber=? and ordernumber=?"); - $sth->execute($quantrec,$invoiceno,$cost,$freight,$rrp,$biblio,$ordnum); - $sth->finish; + +sub ModBasket { + my $basketinfo = shift; + my $query = "UPDATE aqbasket SET "; + my @params; + foreach my $key (keys %$basketinfo){ + if ($key ne 'basketno'){ + $query .= "$key=?, "; + push(@params, $basketinfo->{$key} || undef ); + } + } +# get rid of the "," at the end of $query + if (substr($query, length($query)-2) eq ', '){ + chop($query); + chop($query); + $query .= ' '; + } + $query .= "WHERE basketno=?"; + push(@params, $basketinfo->{'basketno'}); + my $dbh = C4::Context->dbh; + my $sth = $dbh->prepare($query); + $sth->execute(@params); + $sth->finish; } -=item updaterecorder +#------------------------------------------------------------# + +=head3 ModBasketHeader + + &ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber); + +Modifies a basket's header. + +=over - &updaterecorder($biblionumber, $ordernumber, $user, $unitprice, - $bookfundid, $rrp); +=item C<$basketno> is the "basketno" field in the "aqbasket" table; -Updates the order with biblionumber C<$biblionumber> and order number -C<$ordernumber>. C<$bookfundid> is the new value for the book fund ID -in the aqorderbreakdown table of the Koha database. All other -arguments update the fields with the same name in the aqorders table. +=item C<$basketname> is the "basketname" field in the "aqbasket" table; -C<$user> is ignored. +=item C<$note> is the "note" field in the "aqbasket" table; + +=item C<$booksellernote> is the "booksellernote" field in the "aqbasket" table; + +=item C<$contractnumber> is the "contractnumber" (foreign) key in the "aqbasket" table. + +=back =cut -#' -sub updaterecorder{ - my($biblio,$ordnum,$user,$cost,$bookfund,$rrp)=@_; - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("update aqorders set - unitprice=?, rrp=? - where biblionumber=? and ordernumber=? - "); - $sth->execute($cost,$rrp,$biblio,$ordnum); - $sth->finish; - $sth=$dbh->prepare("update aqorderbreakdown set bookfundid=? where ordernumber=?"); - $sth->execute($bookfund,$ordnum); - $sth->finish; + +sub ModBasketHeader { + my ($basketno, $basketname, $note, $booksellernote, $contractnumber) = @_; + my $query = "UPDATE aqbasket SET basketname=?, note=?, booksellernote=? WHERE basketno=?"; + my $dbh = C4::Context->dbh; + my $sth = $dbh->prepare($query); + $sth->execute($basketname,$note,$booksellernote,$basketno); + if ( $contractnumber ) { + my $query2 ="UPDATE aqbasket SET contractnumber=? WHERE basketno=?"; + my $sth2 = $dbh->prepare($query2); + $sth2->execute($contractnumber,$basketno); + $sth2->finish; + } + $sth->finish; } -# -# -# ORDERS -# -# +#------------------------------------------------------------# + +=head3 GetBasketsByBookseller + + @results = &GetBasketsByBookseller($booksellerid, $extra); + +Returns a list of hashes of all the baskets that belong to bookseller 'booksellerid'. + +=over + +=item C<$booksellerid> is the 'id' field of the bookseller in the aqbooksellers table + +=item C<$extra> is the extra sql parameters, can be + + $extra->{groupby}: group baskets by column + ex. $extra->{groupby} = aqbasket.basketgroupid + $extra->{orderby}: order baskets by column + $extra->{limit}: limit number of results (can be helpful for pagination) + +=back + +=cut + +sub GetBasketsByBookseller { + my ($booksellerid, $extra) = @_; + my $query = "SELECT * FROM aqbasket WHERE booksellerid=?"; + if ($extra){ + if ($extra->{groupby}) { + $query .= " GROUP by $extra->{groupby}"; + } + if ($extra->{orderby}){ + $query .= " ORDER by $extra->{orderby}"; + } + if ($extra->{limit}){ + $query .= " LIMIT $extra->{limit}"; + } + } + my $dbh = C4::Context->dbh; + my $sth = $dbh->prepare($query); + $sth->execute($booksellerid); + my $results = $sth->fetchall_arrayref({}); + $sth->finish; + return $results +} + +#------------------------------------------------------------# + +=head3 GetBasketsByBasketgroup + + $baskets = &GetBasketsByBasketgroup($basketgroupid); + +Returns a reference to all baskets that belong to basketgroup $basketgroupid. + +=cut + +sub GetBasketsByBasketgroup { + my $basketgroupid = shift; + my $query = "SELECT * FROM aqbasket + LEFT JOIN aqcontract USING(contractnumber) WHERE basketgroupid=?"; + my $dbh = C4::Context->dbh; + my $sth = $dbh->prepare($query); + $sth->execute($basketgroupid); + my $results = $sth->fetchall_arrayref({}); + $sth->finish; + return $results +} + +#------------------------------------------------------------# + +=head3 NewBasketgroup + + $basketgroupid = NewBasketgroup(\%hashref); + +Adds a basketgroup to the aqbasketgroups table, and add the initial baskets to it. + +$hashref->{'booksellerid'} is the 'id' field of the bookseller in the aqbooksellers table, + +$hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table, + +$hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group, + +$hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table, + +$hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table, + +$hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise. + +=cut + +sub NewBasketgroup { + my $basketgroupinfo = shift; + die "booksellerid is required to create a basketgroup" unless $basketgroupinfo->{'booksellerid'}; + my $query = "INSERT INTO aqbasketgroups ("; + my @params; + foreach my $field ('name', 'deliveryplace', 'deliverycomment', 'closed') { + if ( $basketgroupinfo->{$field} ) { + $query .= "$field, "; + push(@params, $basketgroupinfo->{$field}); + } + } + $query .= "booksellerid) VALUES ("; + foreach (@params) { + $query .= "?, "; + } + $query .= "?)"; + push(@params, $basketgroupinfo->{'booksellerid'}); + my $dbh = C4::Context->dbh; + my $sth = $dbh->prepare($query); + $sth->execute(@params); + my $basketgroupid = $dbh->{'mysql_insertid'}; + if( $basketgroupinfo->{'basketlist'} ) { + foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) { + my $query2 = "UPDATE aqbasket SET basketgroupid=? WHERE basketno=?"; + my $sth2 = $dbh->prepare($query2); + $sth2->execute($basketgroupid, $basketno); + } + } + return $basketgroupid; +} + +#------------------------------------------------------------# + +=head3 ModBasketgroup + + ModBasketgroup(\%hashref); + +Modifies a basketgroup in the aqbasketgroups table, and add the baskets to it. + +$hashref->{'id'} is the 'id' field of the basketgroup in the aqbasketgroup table, this parameter is mandatory, + +$hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table, + +$hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group, + +$hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table, + +$hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table, + +$hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table, + +$hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise. -=item getorders +=cut + +sub ModBasketgroup { + my $basketgroupinfo = shift; + die "basketgroup id is required to edit a basketgroup" unless $basketgroupinfo->{'id'}; + my $dbh = C4::Context->dbh; + my $query = "UPDATE aqbasketgroups SET "; + my @params; + foreach my $field (qw(name billingplace deliveryplace deliverycomment closed)) { + if ( defined $basketgroupinfo->{$field} ) { + $query .= "$field=?, "; + push(@params, $basketgroupinfo->{$field}); + } + } + chop($query); + chop($query); + $query .= " WHERE id=?"; + push(@params, $basketgroupinfo->{'id'}); + my $sth = $dbh->prepare($query); + $sth->execute(@params); + + $sth = $dbh->prepare('UPDATE aqbasket SET basketgroupid = NULL WHERE basketgroupid = ?'); + $sth->execute($basketgroupinfo->{'id'}); + + if($basketgroupinfo->{'basketlist'} && @{$basketgroupinfo->{'basketlist'}}){ + $sth = $dbh->prepare("UPDATE aqbasket SET basketgroupid=? WHERE basketno=?"); + foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) { + $sth->execute($basketgroupinfo->{'id'}, $basketno); + $sth->finish; + } + } + $sth->finish; +} + +#------------------------------------------------------------# + +=head3 DelBasketgroup + + DelBasketgroup($basketgroupid); + +Deletes a basketgroup in the aqbasketgroups table, and removes the reference to it from the baskets, + +=over + +=item C<$basketgroupid> is the 'id' field of the basket in the aqbasketgroup table + +=back + +=cut + +sub DelBasketgroup { + my $basketgroupid = shift; + die "basketgroup id is required to edit a basketgroup" unless $basketgroupid; + my $query = "DELETE FROM aqbasketgroups WHERE id=?"; + my $dbh = C4::Context->dbh; + my $sth = $dbh->prepare($query); + $sth->execute($basketgroupid); + $sth->finish; +} + +#------------------------------------------------------------# + + +=head2 FUNCTIONS ABOUT ORDERS + +=head3 GetBasketgroup + + $basketgroup = &GetBasketgroup($basketgroupid); + +Returns a reference to the hash containing all infermation about the basketgroup. + +=cut + +sub GetBasketgroup { + my $basketgroupid = shift; + die "basketgroup id is required to edit a basketgroup" unless $basketgroupid; + my $query = "SELECT * FROM aqbasketgroups WHERE id=?"; + my $dbh = C4::Context->dbh; + my $sth = $dbh->prepare($query); + $sth->execute($basketgroupid); + my $result = $sth->fetchrow_hashref; + $sth->finish; + return $result +} + +#------------------------------------------------------------# + +=head3 GetBasketgroups + + $basketgroups = &GetBasketgroups($booksellerid); + +Returns a reference to the array of all the basketgroups of bookseller $booksellerid. + +=cut + +sub GetBasketgroups { + my $booksellerid = shift; + die "bookseller id is required to edit a basketgroup" unless $booksellerid; + my $query = "SELECT * FROM aqbasketgroups WHERE booksellerid=?"; + my $dbh = C4::Context->dbh; + my $sth = $dbh->prepare($query); + $sth->execute($booksellerid); + my $results = $sth->fetchall_arrayref({}); + $sth->finish; + return $results +} + +#------------------------------------------------------------# + +=head2 FUNCTIONS ABOUT ORDERS + +=cut + +#------------------------------------------------------------# - ($count, $orders) = &getorders($booksellerid); +=head3 GetPendingOrders + + $orders = &GetPendingOrders($booksellerid, $grouped, $owner); Finds pending orders from the bookseller with the given ID. Ignores completed and cancelled orders. -C<$count> is the number of elements in C<@{$orders}>. +C<$booksellerid> contains the bookseller identifier +C<$grouped> contains 0 or 1. 0 means returns the list, 1 means return the total +C<$owner> contains 0 or 1. 0 means any owner. 1 means only the list of orders entered by the user itself. C<$orders> is a reference-to-array; each element is a reference-to-hash with the following fields: +C<$grouped> is a boolean that, if set to 1 will group all order lines of the same basket +in a single result line -=over 4 - -=item C - -Gives the number of orders in with this basket number. +=over =item C @@ -379,165 +699,434 @@ Gives the number of orders in with this basket number. =item C +=back + These give the value of the corresponding field in the aqorders table of the Koha database. -=back - Results are ordered from most to least recent. =cut -#' -sub getorders { - my ($supplierid)=@_; - my $dbh = C4::Context->dbh; - my $strsth ="Select count(*),authorisedby,creationdate,aqbasket.basketno, -closedate,surname,firstname,aqorders.title -from aqorders -left join aqbasket on aqbasket.basketno=aqorders.basketno -left join borrowers on aqbasket.authorisedby=borrowers.borrowernumber -where booksellerid=? and (quantity > quantityreceived or -quantityreceived is NULL) and datecancellationprinted is NULL "; - - if (C4::Context->preference("IndependantBranches")) { - my $userenv = C4::Context->userenv; - unless ($userenv->{flags} == 1){ - $strsth .= " and (borrowers.branchcode = '".$userenv->{branch}."' or borrowers.branchcode ='')"; - } - } - $strsth.=" group by basketno order by aqbasket.basketno"; - my $sth=$dbh->prepare($strsth); - $sth->execute($supplierid); - my @results = (); - while (my $data=$sth->fetchrow_hashref){ - push(@results,$data); - } - $sth->finish; - return (scalar(@results),\@results); -} - -=item getorder - - ($order, $ordernumber) = &getorder($biblioitemnumber, $biblionumber); - -Looks up the order with the given biblionumber and biblioitemnumber. - -Returns a two-element array. C<$ordernumber> is the order number. -C<$order> is a reference-to-hash describing the order; its keys are -fields from the biblio, biblioitems, aqorders, and aqorderbreakdown -tables of the Koha database. - -=cut - -sub getorder{ - my ($bi,$bib)=@_; - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("Select ordernumber from aqorders where biblionumber=? and biblioitemnumber=?"); - $sth->execute($bib,$bi); - # FIXME - Use fetchrow_array(), since we're only interested in the one - # value. - my $ordnum=$sth->fetchrow_hashref; - $sth->finish; - my $order=getsingleorder($ordnum->{'ordernumber'}); - return ($order,$ordnum->{'ordernumber'}); -} - -=item getsingleorder - - $order = &getsingleorder($ordernumber); + +sub GetPendingOrders { + my ($supplierid,$grouped,$owner,$basketno) = @_; + my $dbh = C4::Context->dbh; + my $strsth = " + SELECT ".($grouped?"count(*),":"")."aqbasket.basketno, + surname,firstname,aqorders.*,biblio.*,biblioitems.isbn, + aqbasket.closedate, aqbasket.creationdate, aqbasket.basketname + FROM aqorders + LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno + LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber + LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber + LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber + WHERE booksellerid=? + AND (quantity > quantityreceived OR quantityreceived is NULL) + AND datecancellationprinted IS NULL + AND (to_days(now())-to_days(closedate) < 180 OR closedate IS NULL) + "; + ## FIXME Why 180 days ??? + my @query_params = ( $supplierid ); + my $userenv = C4::Context->userenv; + if ( C4::Context->preference("IndependantBranches") ) { + if ( ($userenv) && ( $userenv->{flags} != 1 ) ) { + $strsth .= " and (borrowers.branchcode = ? + or borrowers.branchcode = '')"; + push @query_params, $userenv->{branch}; + } + } + if ($owner) { + $strsth .= " AND aqbasket.authorisedby=? "; + push @query_params, $userenv->{'number'}; + } + if ($basketno) { + $strsth .= " AND aqbasket.basketno=? "; + push @query_params, $basketno; + } + $strsth .= " group by aqbasket.basketno" if $grouped; + $strsth .= " order by aqbasket.basketno"; + + my $sth = $dbh->prepare($strsth); + $sth->execute( @query_params ); + my $results = $sth->fetchall_arrayref({}); + $sth->finish; + return $results; +} + +#------------------------------------------------------------# + +=head3 GetOrders + + @orders = &GetOrders($basketnumber, $orderby); + +Looks up the pending (non-cancelled) orders with the given basket +number. If C<$booksellerID> is non-empty, only orders from that seller +are returned. + +return : +C<&basket> returns a two-element array. C<@orders> is an array of +references-to-hash, whose keys are the fields from the aqorders, +biblio, and biblioitems tables in the Koha database. + +=cut + +sub GetOrders { + my ( $basketno, $orderby ) = @_; + my $dbh = C4::Context->dbh; + my $query =" + SELECT biblio.*,biblioitems.*, + aqorders.*, + aqbudgets.*, + biblio.title + FROM aqorders + LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id + LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber + LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber + WHERE basketno=? + AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00') + "; + + $orderby = "biblioitems.publishercode,biblio.title" unless $orderby; + $query .= " ORDER BY $orderby"; + my $sth = $dbh->prepare($query); + $sth->execute($basketno); + my $results = $sth->fetchall_arrayref({}); + $sth->finish; + return @$results; +} + +#------------------------------------------------------------# + +=head3 GetOrderNumber + + $ordernumber = &GetOrderNumber($biblioitemnumber, $biblionumber); + +Looks up the ordernumber with the given biblionumber and biblioitemnumber. + +Returns the number of this order. + +=over + +=item C<$ordernumber> is the order number. + +=back + +=cut + +sub GetOrderNumber { + my ( $biblionumber,$biblioitemnumber ) = @_; + my $dbh = C4::Context->dbh; + my $query = " + SELECT ordernumber + FROM aqorders + WHERE biblionumber=? + AND biblioitemnumber=? + "; + my $sth = $dbh->prepare($query); + $sth->execute( $biblionumber, $biblioitemnumber ); + + return $sth->fetchrow; +} + +#------------------------------------------------------------# + +=head3 GetOrder + + $order = &GetOrder($ordernumber); Looks up an order by order number. Returns a reference-to-hash describing the order. The keys of -C<$order> are fields from the biblio, biblioitems, aqorders, and -aqorderbreakdown tables of the Koha database. +C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database. =cut -sub getsingleorder { - my ($ordnum)=@_; - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("Select * from biblio,biblioitems,aqorders left join aqorderbreakdown - on aqorders.ordernumber=aqorderbreakdown.ordernumber - where aqorders.ordernumber=? - and biblio.biblionumber=aqorders.biblionumber and - biblioitems.biblioitemnumber=aqorders.biblioitemnumber"); - $sth->execute($ordnum); - my $data=$sth->fetchrow_hashref; - $sth->finish; - return($data); +sub GetOrder { + my ($ordernumber) = @_; + my $dbh = C4::Context->dbh; + my $query = " + SELECT biblioitems.*, biblio.*, aqorders.* + FROM aqorders + LEFT JOIN biblio on biblio.biblionumber=aqorders.biblionumber + LEFT JOIN biblioitems on biblioitems.biblionumber=aqorders.biblionumber + WHERE aqorders.ordernumber=? + + "; + my $sth= $dbh->prepare($query); + $sth->execute($ordernumber); + my $data = $sth->fetchrow_hashref; + $sth->finish; + return $data; } -=item getallorders +#------------------------------------------------------------# - ($count, @results) = &getallorders($booksellerid); +=head3 NewOrder -Looks up all of the pending orders from the supplier with the given -bookseller ID. Ignores cancelled and completed orders. + &NewOrder(\%hashref); -C<$count> is the number of elements in C<@results>. C<@results> is an -array of references-to-hash. The keys of each element are fields from -the aqorders, biblio, and biblioitems tables of the Koha database. +Adds a new order to the database. Any argument that isn't described +below is the new value of the field with the same name in the aqorders +table of the Koha database. -C<@results> is sorted alphabetically by book title. +=over + +=item $hashref->{'basketno'} is the basketno foreign key in aqorders, it is mandatory + +=item $hashref->{'ordernumber'} is a "minimum order number." + +=item $hashref->{'budgetdate'} is effectively ignored. +If it's undef (anything false) or the string 'now', the current day is used. +Else, the upcoming July 1st is used. + +=item $hashref->{'subscription'} may be either "yes", or anything else for "no". + +=item $hashref->{'uncertainprice'} may be 0 for "the price is known" or 1 for "the price is uncertain" + +=item defaults entrydate to Now + +The following keys are used: "biblionumber", "title", "basketno", "quantity", "notes", "biblioitemnumber", "rrp", "ecost", "gst", "unitprice", "subscription", "sort1", "sort2", "booksellerinvoicenumber", "listprice", "budgetdate", "purchaseordernumber", "branchcode", "booksellerinvoicenumber", "bookfundid". + +=back =cut -#' -sub getallorders { - #gets all orders from a certain supplier, orders them alphabetically - my ($supid)=@_; - my $dbh = C4::Context->dbh; - my @results = (); - my $strsth="Select *,aqorders.title as suggestedtitle,biblio.title as truetitle from aqorders,biblio,biblioitems,aqbasket,aqbooksellers "; - $strsth .= ",borrowers " if (C4::Context->preference("IndependantBranches")); - $strsth .=" where aqorders.basketno=aqbasket.basketno and aqbasket.booksellerid=aqbooksellers.id and biblio.biblionumber=aqorders.biblionumber "; - $strsth .= " and aqbasket.authorisedby=borrowers.borrowernumber" if (C4::Context->preference("IndependantBranches")); - $strsth.=" and booksellerid=? and (cancelledby is NULL or cancelledby = '') - and (quantityreceived < quantity or quantityreceived is NULL) - and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber= - aqorders.biblioitemnumber "; - if (C4::Context->preference("IndependantBranches")) { - my $userenv = C4::Context->userenv; - unless ($userenv->{flags} == 1){ - $strsth .= " and (borrowers.branchcode = '".$userenv->{branch}."' or borrowers.branchcode ='')"; - } - } - $strsth .= " group by aqorders.biblioitemnumber order by biblio.title"; - my $sth=$dbh->prepare($strsth); - $sth->execute($supid); - while (my $data=$sth->fetchrow_hashref){ - push(@results,$data); - } - $sth->finish; - return(scalar(@results),@results); -} - -# FIXME - Never used -sub getrecorders { - #gets all orders from a certain supplier, orders them alphabetically - my ($supid)=@_; - my $dbh = C4::Context->dbh; - my @results= (); - my $sth=$dbh->prepare("Select * from aqorders,biblio,biblioitems where booksellerid=? - 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"); - $sth->execute($supid); - while (my $data=$sth->fetchrow_hashref){ - push(@results,$data); - } - $sth->finish; - return(scalar(@results),@results); -} - -=item ordersearch - - ($count, @results) = &ordersearch($search, $biblionumber, $complete); + +sub NewOrder { + my $orderinfo = shift; +#### ------------------------------ + my $dbh = C4::Context->dbh; + my @params; + + + # if these parameters are missing, we can't continue + for my $key (qw/basketno quantity biblionumber budget_id/) { + die "Mandatory parameter $key missing" unless $orderinfo->{$key}; + } + + if ( defined $orderinfo->{subscription} && $orderinfo->{'subscription'} eq 'yes' ) { + $orderinfo->{'subscription'} = 1; + } else { + $orderinfo->{'subscription'} = 0; + } + $orderinfo->{'entrydate'} ||= C4::Dates->new()->output("iso"); + if (!$orderinfo->{quantityreceived}) { + $orderinfo->{quantityreceived} = 0; + } + + my $ordernumber=InsertInTable("aqorders",$orderinfo); + return ( $orderinfo->{'basketno'}, $ordernumber ); +} + + + +#------------------------------------------------------------# + +=head3 NewOrderItem + + &NewOrderItem(); + +=cut + +sub NewOrderItem { + #my ($biblioitemnumber,$ordernumber, $biblionumber) = @_; + my ($itemnumber, $ordernumber) = @_; + my $dbh = C4::Context->dbh; + my $query = qq| + INSERT INTO aqorders_items + (itemnumber, ordernumber) + VALUES (?,?) |; + + my $sth = $dbh->prepare($query); + $sth->execute( $itemnumber, $ordernumber); +} + +#------------------------------------------------------------# + +=head3 ModOrder + + &ModOrder(\%hashref); + +Modifies an existing order. Updates the order with order number +$hashref->{'ordernumber'} and biblionumber $hashref->{'biblionumber'}. All +other keys of the hash update the fields with the same name in the aqorders +table of the Koha database. + +=cut + +sub ModOrder { + my $orderinfo = shift; + + die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq '' ; + die "Biblionumber is required" if $orderinfo->{'biblionumber'} eq ''; + + my $dbh = C4::Context->dbh; + my @params; +# delete($orderinfo->{'branchcode'}); + # the hash contains a lot of entries not in aqorders, so get the columns ... + my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;"); + $sth->execute; + my $colnames = $sth->{NAME}; + my $query = "UPDATE aqorders SET "; + + foreach my $orderinfokey (grep(!/ordernumber/, keys %$orderinfo)){ + # ... and skip hash entries that are not in the aqorders table + # FIXME : probably not the best way to do it (would be better to have a correct hash) + next unless grep(/^$orderinfokey$/, @$colnames); + $query .= "$orderinfokey=?, "; + push(@params, $orderinfo->{$orderinfokey}); + } + + $query .= "timestamp=NOW() WHERE ordernumber=?"; +# push(@params, $specorderinfo{'ordernumber'}); + push(@params, $orderinfo->{'ordernumber'} ); + $sth = $dbh->prepare($query); + $sth->execute(@params); + $sth->finish; +} + +#------------------------------------------------------------# + +=head3 ModOrderItem + + &ModOrderItem(\%hashref); + +Modifies the itemnumber in the aqorders_items table. The input hash needs three entities: + +=over + +=item - itemnumber: the old itemnumber +=item - ordernumber: the order this item is attached to +=item - newitemnumber: the new itemnumber we want to attach the line to + +=back + +=cut + +sub ModOrderItem { + my $orderiteminfo = shift; + if (! $orderiteminfo->{'ordernumber'} || ! $orderiteminfo->{'itemnumber'} || ! $orderiteminfo->{'newitemnumber'}){ + die "Ordernumber, itemnumber and newitemnumber is required"; + } + + my $dbh = C4::Context->dbh; + + my $query = "UPDATE aqorders_items set itemnumber=? where itemnumber=? and ordernumber=?"; + my @params = ($orderiteminfo->{'newitemnumber'}, $orderiteminfo->{'itemnumber'}, $orderiteminfo->{'ordernumber'}); + my $sth = $dbh->prepare($query); + $sth->execute(@params); + return 0; +} + +#------------------------------------------------------------# + + +=head3 ModOrderBibliotemNumber + + &ModOrderBiblioitemNumber($biblioitemnumber,$ordernumber, $biblionumber); + +Modifies the biblioitemnumber for an existing order. +Updates the order with order number C<$ordernum> and biblionumber C<$biblionumber>. + +=cut + +#FIXME: is this used at all? +sub ModOrderBiblioitemNumber { + my ($biblioitemnumber,$ordernumber, $biblionumber) = @_; + my $dbh = C4::Context->dbh; + my $query = " + UPDATE aqorders + SET biblioitemnumber = ? + WHERE ordernumber = ? + AND biblionumber = ?"; + my $sth = $dbh->prepare($query); + $sth->execute( $biblioitemnumber, $ordernumber, $biblionumber ); +} + +#------------------------------------------------------------# + +=head3 ModReceiveOrder + + &ModReceiveOrder($biblionumber, $ordernumber, $quantityreceived, $user, + $unitprice, $booksellerinvoicenumber, $biblioitemnumber, + $freight, $bookfund, $rrp); + +Updates an order, to reflect the fact that it was received, at least +in part. All arguments not mentioned below update the fields with the +same name in the aqorders table of the Koha database. + +If a partial order is received, splits the order into two. The received +portion must have a booksellerinvoicenumber. + +Updates the order with bibilionumber C<$biblionumber> and ordernumber +C<$ordernumber>. + +=cut + + +sub ModReceiveOrder { + my ( + $biblionumber, $ordernumber, $quantrec, $user, $cost, + $invoiceno, $freight, $rrp, $budget_id, $datereceived + ) + = @_; + my $dbh = C4::Context->dbh; +# warn "DATE BEFORE : $daterecieved"; +# $daterecieved=POSIX::strftime("%Y-%m-%d",CORE::localtime) unless $daterecieved; +# warn "DATE REC : $daterecieved"; + $datereceived = C4::Dates->output('iso') unless $datereceived; + my $suggestionid = GetSuggestionFromBiblionumber( $dbh, $biblionumber ); + if ($suggestionid) { + ModSuggestion( {suggestionid=>$suggestionid, + STATUS=>'AVAILABLE', + biblionumber=> $biblionumber} + ); + } + + my $sth=$dbh->prepare(" + SELECT * FROM aqorders + WHERE biblionumber=? AND aqorders.ordernumber=?"); + + $sth->execute($biblionumber,$ordernumber); + my $order = $sth->fetchrow_hashref(); + $sth->finish(); + + if ( $order->{quantity} > $quantrec ) { + $sth=$dbh->prepare(" + UPDATE aqorders + SET quantityreceived=? + , datereceived=? + , booksellerinvoicenumber=? + , unitprice=? + , freight=? + , rrp=? + , quantity=? + WHERE biblionumber=? AND ordernumber=?"); + + $sth->execute($quantrec,$datereceived,$invoiceno,$cost,$freight,$rrp,$quantrec,$biblionumber,$ordernumber); + $sth->finish; + + # create a new order for the remaining items, and set its bookfund. + foreach my $orderkey ( "linenumber", "allocation" ) { + delete($order->{'$orderkey'}); + } + $order->{'quantity'} -= $quantrec; + $order->{'quantityreceived'} = 0; + my $newOrder = NewOrder($order); +} else { + $sth=$dbh->prepare("update aqorders + set quantityreceived=?,datereceived=?,booksellerinvoicenumber=?, + unitprice=?,freight=?,rrp=? + where biblionumber=? and ordernumber=?"); + $sth->execute($quantrec,$datereceived,$invoiceno,$cost,$freight,$rrp,$biblionumber,$ordernumber); + $sth->finish; + } + return $datereceived; +} +#------------------------------------------------------------# + +=head3 SearchOrder + +@results = &SearchOrder($search, $biblionumber, $complete); Searches for orders. @@ -552,9 +1141,8 @@ words). If C<$complete> is C, the results will include only completed orders. In any case, C<&ordersearch> ignores cancelled orders. -C<&ordersearch> returns an array. C<$count> is the number of elements -in C<@results>. C<@results> is an array of references-to-hash with the -following keys: +C<&ordersearch> returns an array. +C<@results> is an array of references-to-hash with the following keys: =over 4 @@ -569,410 +1157,551 @@ following keys: =back =cut -#' -sub ordersearch { - my ($search,$id,$biblio,$catview) = @_; - my $dbh = C4::Context->dbh; - my @data = split(' ',$search); - my @searchterms = ($id); - map { push(@searchterms,"$_%","% $_%") } @data; - push(@searchterms,$search,$search,$biblio); - my $sth=$dbh->prepare("Select biblio.*,biblioitems.*,aqorders.*,aqbasket.*,biblio.title from aqorders,biblioitems,biblio,aqbasket - where aqorders.biblioitemnumber = biblioitems.biblioitemnumber and - aqorders.basketno = aqbasket.basketno - and aqbasket.booksellerid = ? - and biblio.biblionumber=aqorders.biblionumber - and ((datecancellationprinted is NULL) - or (datecancellationprinted = '0000-00-00')) - and ((" - .(join(" and ",map { "(biblio.title like ? or biblio.title like ?)" } @data)) - .") or biblioitems.isbn=? or (aqorders.ordernumber=? and aqorders.biblionumber=?)) " - .(($catview ne 'yes')?" and (quantityreceived < quantity or quantityreceived is NULL)":"") - ." group by aqorders.ordernumber"); - $sth->execute(@searchterms); - my @results = (); - my $sth2=$dbh->prepare("Select * from biblio where biblionumber=?"); - my $sth3=$dbh->prepare("Select * from aqorderbreakdown where ordernumber=?"); - while (my $data=$sth->fetchrow_hashref){ - $sth2->execute($data->{'biblionumber'}); - my $data2=$sth2->fetchrow_hashref; - $data->{'author'}=$data2->{'author'}; - $data->{'seriestitle'}=$data2->{'seriestitle'}; - $sth3->execute($data->{'ordernumber'}); - my $data3=$sth3->fetchrow_hashref; - $data->{'branchcode'}=$data3->{'branchcode'}; - $data->{'bookfundid'}=$data3->{'bookfundid'}; - push(@results,$data); - } - $sth->finish; - $sth2->finish; - $sth3->finish; - return(scalar(@results),@results); -} - - -sub histsearch { - my ($title,$author,$name,$from_placed_on,$to_placed_on)=@_; - my $dbh= C4::Context->dbh; - my $query = "select biblio.title,aqorders.basketno,name,aqbasket.creationdate,aqorders.datereceived, aqorders.quantity, aqorders.ecost from aqorders,aqbasket,aqbooksellers,biblio"; - - $query .= ",borrowers " if (C4::Context->preference("IndependantBranches")); - $query .=" where aqorders.basketno=aqbasket.basketno and aqbasket.booksellerid=aqbooksellers.id and biblio.biblionumber=aqorders.biblionumber "; - $query .= " and aqbasket.authorisedby=borrowers.borrowernumber" if (C4::Context->preference("IndependantBranches")); - $query .= " and biblio.title like ".$dbh->quote("%".$title."%") if $title; - $query .= " and biblio.author like ".$dbh->quote("%".$author."%") if $author; - $query .= " and name like ".$dbh->quote("%".$name."%") if $name; - $query .= " and creationdate >" .$dbh->quote($from_placed_on) if $from_placed_on; - $query .= " and creationdate<".$dbh->quote($to_placed_on) if $to_placed_on; - if (C4::Context->preference("IndependantBranches")) { - my $userenv = C4::Context->userenv; - unless ($userenv->{flags} == 1){ - $query .= " and (borrowers.branchcode = '".$userenv->{branch}."' or borrowers.branchcode ='')"; - } - } - warn "C4:Acquisition : ".$query; - my $sth = $dbh->prepare($query); - $sth->execute; - my @order_loop; - my $cnt=1; - while (my $line = $sth->fetchrow_hashref) { - $line->{count}=$cnt++; - push @order_loop, $line; - } - return \@order_loop; + +sub SearchOrder { +#### -------- SearchOrder------------------------------- + my ($ordernumber, $search, $supplierid, $basket) = @_; + + my $dbh = C4::Context->dbh; + my @args = (); + my $query = + "SELECT * + FROM aqorders + LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber + LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber + LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno + WHERE (datecancellationprinted is NULL)"; + + if($ordernumber){ + $query .= " AND (aqorders.ordernumber=?)"; + push @args, $ordernumber; + } + if($search){ + $query .= " AND (biblio.title like ? OR biblio.author LIKE ? OR biblioitems.isbn like ?)"; + push @args, ("%$search%","%$search%","%$search%"); + } + if($supplierid){ + $query .= "AND aqbasket.booksellerid = ?"; + push @args, $supplierid; + } + if($basket){ + $query .= "AND aqorders.basketno = ?"; + push @args, $basket; + } + + my $sth = $dbh->prepare($query); + $sth->execute(@args); + my $results = $sth->fetchall_arrayref({}); + $sth->finish; + return $results; } -# -# -# MONEY -# -# -=item invoice +#------------------------------------------------------------# - ($count, @results) = &invoice($booksellerinvoicenumber); +=head3 DelOrder -Looks up orders by invoice number. + &DelOrder($biblionumber, $ordernumber); -Returns an array. C<$count> is the number of elements in C<@results>. -C<@results> is an array of references-to-hash; the keys of each -elements are fields from the aqorders, biblio, and biblioitems tables -of the Koha database. +Cancel the order with the given order and biblio numbers. It does not +delete any entries in the aqorders table, it merely marks them as +cancelled. =cut -#' -sub invoice { - my ($invoice)=@_; - my $dbh = C4::Context->dbh; - my @results = (); - my $sth=$dbh->prepare("Select * from aqorders,biblio,biblioitems where - booksellerinvoicenumber=? - and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber= - aqorders.biblioitemnumber group by aqorders.ordernumber,aqorders.biblioitemnumber"); - $sth->execute($invoice); - while (my $data=$sth->fetchrow_hashref){ - push(@results,$data); - } - $sth->finish; - return(scalar(@results),@results); -} - -=item bookfunds - - ($count, @results) = &bookfunds(); - -Returns a list of all book funds. - -C<$count> is the number of elements in C<@results>. C<@results> is an -array of references-to-hash, whose keys are fields from the aqbookfund -and aqbudget tables of the Koha database. Results are ordered -alphabetically by book fund name. - -=cut -#' -sub bookfunds { - my ($branch)=@_; - my $dbh = C4::Context->dbh; - my $strsth; - - if ($branch eq '') { - $strsth="Select * from aqbookfund,aqbudget where aqbookfund.bookfundid - =aqbudget.bookfundid - group by aqbookfund.bookfundid order by bookfundname"; - } else { - $strsth="Select * from aqbookfund,aqbudget where aqbookfund.bookfundid - =aqbudget.bookfundid and (aqbookfund.branchcode is null or aqbookfund.branchcode='' or aqbookfund.branchcode= ? ) - group by aqbookfund.bookfundid order by bookfundname"; - } - my $sth=$dbh->prepare($strsth); - if ($branch){ - $sth->execute($branch); - } else { - $sth->execute; - } - my @results = (); - while (my $data=$sth->fetchrow_hashref){ - push(@results,$data); - } - $sth->finish; - return(scalar(@results),@results); -} - -=item bookfundbreakdown - - returns the total comtd & spent for a given bookfund - used in acqui-home.pl -=cut -#' - -sub bookfundbreakdown { - my ($id)=@_; - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("Select quantity,datereceived,freight,unitprice,listprice,ecost,quantityreceived,subscription - from aqorders,aqorderbreakdown where bookfundid=? and - aqorders.ordernumber=aqorderbreakdown.ordernumber - and (datecancellationprinted is NULL or - datecancellationprinted='0000-00-00')"); - $sth->execute($id); - 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; - return($spent,$comtd); + +sub DelOrder { + my ( $bibnum, $ordernumber ) = @_; + my $dbh = C4::Context->dbh; + my $query = " + UPDATE aqorders + SET datecancellationprinted=now() + WHERE biblionumber=? AND ordernumber=? + "; + my $sth = $dbh->prepare($query); + $sth->execute( $bibnum, $ordernumber ); + $sth->finish; } +=head2 FUNCTIONS ABOUT PARCELS + +=cut + +#------------------------------------------------------------# +=head3 GetParcel -=item curconvert + @results = &GetParcel($booksellerid, $code, $date); - $foreignprice = &curconvert($currency, $localprice); +Looks up all of the received items from the supplier with the given +bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders. -Converts the price C<$localprice> to foreign currency C<$currency> by -dividing by the exchange rate, and returns the result. +C<@results> is an array of references-to-hash. The keys of each element are fields from +the aqorders, biblio, and biblioitems tables of the Koha database. -If no exchange rate is found, C<&curconvert> assumes the rate is one -to one. +C<@results> is sorted alphabetically by book title. =cut -#' -sub curconvert { - my ($currency,$price)=@_; - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("Select rate from currency where currency=?"); - $sth->execute($currency); - my $cur=($sth->fetchrow_array())[0]; - $sth->finish; - if ($cur==0){ - $cur=1; - } - return($price / $cur); + +sub GetParcel { + #gets all orders from a certain supplier, orders them alphabetically + my ( $supplierid, $code, $datereceived ) = @_; + my $dbh = C4::Context->dbh; + my @results = (); + $code .= '%' + if $code; # add % if we search on a given code (otherwise, let him empty) + my $strsth =" + SELECT authorisedby, + creationdate, + aqbasket.basketno, + closedate,surname, + firstname, + aqorders.biblionumber, + aqorders.ordernumber, + aqorders.quantity, + aqorders.quantityreceived, + aqorders.unitprice, + aqorders.listprice, + aqorders.rrp, + aqorders.ecost, + biblio.title + FROM aqorders + LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno + LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber + LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber + WHERE + aqbasket.booksellerid = ? + AND aqorders.booksellerinvoicenumber LIKE ? + AND aqorders.datereceived = ? "; + + my @query_params = ( $supplierid, $code, $datereceived ); + if ( C4::Context->preference("IndependantBranches") ) { + my $userenv = C4::Context->userenv; + if ( ($userenv) && ( $userenv->{flags} != 1 ) ) { + $strsth .= " and (borrowers.branchcode = ? + or borrowers.branchcode = '')"; + push @query_params, $userenv->{branch}; + } + } + $strsth .= " ORDER BY aqbasket.basketno"; + # ## parcelinformation : $strsth + my $sth = $dbh->prepare($strsth); + $sth->execute( @query_params ); + while ( my $data = $sth->fetchrow_hashref ) { + push( @results, $data ); + } + # ## countparcelbiblio: scalar(@results) + $sth->finish; + + return @results; } -=item getcurrencies +#------------------------------------------------------------# + +=head3 GetParcels + + $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto); + +get a lists of parcels. + +* Input arg : + +=over + +=item $bookseller +is the bookseller this function has to get parcels. + +=item $order +To know on what criteria the results list has to be ordered. + +=item $code +is the booksellerinvoicenumber. + +=item $datefrom & $dateto +to know on what date this function has to filter its search. + +=back + +* return: +a pointer on a hash list containing parcel informations as such : + +=over - ($count, $currencies) = &getcurrencies(); +=item Creation date -Returns the list of all known currencies. +=item Last operation -C<$count> is the number of elements in C<$currencies>. C<$currencies> -is a reference-to-array; its elements are references-to-hash, whose -keys are the fields from the currency table in the Koha database. +=item Number of biblio + +=item Number of items + +=back =cut -#' -sub getcurrencies { - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("Select * from currency"); - $sth->execute; - my @results = (); - while (my $data=$sth->fetchrow_hashref){ - push(@results,$data); - } - $sth->finish; - return(scalar(@results),\@results); + +sub GetParcels { + my ($bookseller,$order, $code, $datefrom, $dateto) = @_; + my $dbh = C4::Context->dbh; + my @query_params = (); + my $strsth =" + SELECT aqorders.booksellerinvoicenumber, + datereceived,purchaseordernumber, + count(DISTINCT biblionumber) AS biblio, + sum(quantity) AS itemsexpected, + sum(quantityreceived) AS itemsreceived + FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno + WHERE aqbasket.booksellerid = $bookseller and datereceived IS NOT NULL + "; + + if ( defined $code ) { + $strsth .= ' and aqorders.booksellerinvoicenumber like ? '; + # add a % to the end of the code to allow stemming. + push @query_params, "$code%"; + } + + if ( defined $datefrom ) { + $strsth .= ' and datereceived >= ? '; + push @query_params, $datefrom; + } + + if ( defined $dateto ) { + $strsth .= 'and datereceived <= ? '; + push @query_params, $dateto; + } + + $strsth .= "group by aqorders.booksellerinvoicenumber,datereceived "; + + # can't use a placeholder to place this column name. + # but, we could probably be checking to make sure it is a column that will be fetched. + $strsth .= "order by $order " if ($order); + + my $sth = $dbh->prepare($strsth); + + $sth->execute( @query_params ); + my $results = $sth->fetchall_arrayref({}); + $sth->finish; + return @$results; } -=item updatecurrencies +#------------------------------------------------------------# + +=head3 GetLateOrders + + @results = &GetLateOrders; - &updatecurrencies($currency, $newrate); +Searches for bookseller with late orders. -Sets the exchange rate for C<$currency> to be C<$newrate>. +return: +the table of supplier with late issues. This table is full of hashref. =cut -#' -sub updatecurrencies { - my ($currency,$rate)=@_; - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("update currency set rate=? where currency=?"); - $sth->execute($rate,$currency); - $sth->finish; + +sub GetLateOrders { + my $delay = shift; + my $supplierid = shift; + my $branch = shift; + + my $dbh = C4::Context->dbh; + + #BEWARE, order of parenthesis and LEFT JOIN is important for speed + my $dbdriver = C4::Context->config("db_scheme") || "mysql"; + + my @query_params = ($delay); # delay is the first argument regardless + my $select = " + SELECT aqbasket.basketno, + aqorders.ordernumber, + DATE(aqbasket.closedate) AS orderdate, + aqorders.rrp AS unitpricesupplier, + aqorders.ecost AS unitpricelib, + aqbudgets.budget_name AS budget, + borrowers.branchcode AS branch, + aqbooksellers.name AS supplier, + biblio.author, + biblioitems.publishercode AS publisher, + biblioitems.publicationyear, + "; + my $from = " + FROM + aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber + LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber + LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id, + aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber + LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id + WHERE aqorders.basketno = aqbasket.basketno + AND ( datereceived = '' + OR datereceived IS NULL + OR aqorders.quantityreceived < aqorders.quantity + ) + "; + my $having = ""; + if ($dbdriver eq "mysql") { + $select .= " + aqorders.quantity - IFNULL(aqorders.quantityreceived,0) AS quantity, + (aqorders.quantity - IFNULL(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal, + DATEDIFF(CURDATE( ),closedate) AS latesince + "; + $from .= " AND (closedate <= DATE_SUB(CURDATE( ),INTERVAL ? DAY)) "; + $having = " + HAVING quantity <> 0 + AND unitpricesupplier <> 0 + AND unitpricelib <> 0 + "; + } else { + # FIXME: account for IFNULL as above + $select .= " + aqorders.quantity AS quantity, + aqorders.quantity * aqorders.rrp AS subtotal, + (CURDATE - closedate) AS latesince + "; + $from .= " AND (closedate <= (CURDATE -(INTERVAL ? DAY)) "; + } + if (defined $supplierid) { + $from .= ' AND aqbasket.booksellerid = ? '; + push @query_params, $supplierid; + } + if (defined $branch) { + $from .= ' AND borrowers.branchcode LIKE ? '; + push @query_params, $branch; + } + if (C4::Context->preference("IndependantBranches") + && C4::Context->userenv + && C4::Context->userenv->{flags} != 1 ) { + $from .= ' AND borrowers.branchcode LIKE ? '; + push @query_params, C4::Context->userenv->{branch}; + } + my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier"; + $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params); + my $sth = $dbh->prepare($query); + $sth->execute(@query_params); + my @results; + while (my $data = $sth->fetchrow_hashref) { + $data->{orderdate} = format_date($data->{orderdate}); + push @results, $data; + } + return @results; } -# -# -# OTHERS -# -# +#------------------------------------------------------------# + +=head3 GetHistory + + (\@order_loop, $total_qty, $total_price, $total_qtyreceived) = GetHistory( $title, $author, $name, $from_placed_on, $to_placed_on ); + +Retreives some acquisition history information + +returns: + $order_loop is a list of hashrefs that each look like this: + { + 'author' => 'Twain, Mark', + 'basketno' => '1', + 'biblionumber' => '215', + 'count' => 1, + 'creationdate' => 'MM/DD/YYYY', + 'datereceived' => undef, + 'ecost' => '1.00', + 'id' => '1', + 'invoicenumber' => undef, + 'name' => '', + 'ordernumber' => '1', + 'quantity' => 1, + 'quantityreceived' => undef, + 'title' => 'The Adventures of Huckleberry Finn' + } + $total_qty is the sum of all of the quantities in $order_loop + $total_price is the cost of each in $order_loop times the quantity + $total_qtyreceived is the sum of all of the quantityreceived entries in $order_loop + +=cut -=item bookseller +sub GetHistory { + my ( $title, $author, $name, $from_placed_on, $to_placed_on ) = @_; + my @order_loop; + my $total_qty = 0; + my $total_qtyreceived = 0; + my $total_price = 0; + +# don't run the query if there are no parameters (list would be too long for sure !) + if ( $title || $author || $name || $from_placed_on || $to_placed_on ) { + my $dbh = C4::Context->dbh; + my $query =" + SELECT + biblio.title, + biblio.author, + aqorders.basketno, + aqbasket.basketname, + aqbasket.basketgroupid, + aqbasketgroups.name as groupname, + aqbooksellers.name, + aqbasket.creationdate, + aqorders.datereceived, + aqorders.quantity, + aqorders.quantityreceived, + aqorders.ecost, + aqorders.ordernumber, + aqorders.booksellerinvoicenumber as invoicenumber, + aqbooksellers.id as id, + aqorders.biblionumber + FROM aqorders + LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno + LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id + LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id + LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber"; + + $query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber" + if ( C4::Context->preference("IndependantBranches") ); + + $query .= " WHERE (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') "; + + my @query_params = (); + + if ( defined $title ) { + $query .= " AND biblio.title LIKE ? "; + $title =~ s/\s+/%/g; + push @query_params, "%$title%"; + } + + if ( defined $author ) { + $query .= " AND biblio.author LIKE ? "; + push @query_params, "%$author%"; + } + + if ( defined $name ) { + $query .= " AND aqbooksellers.name LIKE ? "; + push @query_params, "%$name%"; + } + + if ( defined $from_placed_on ) { + $query .= " AND creationdate >= ? "; + push @query_params, $from_placed_on; + } + + if ( defined $to_placed_on ) { + $query .= " AND creationdate <= ? "; + push @query_params, $to_placed_on; + } + + if ( C4::Context->preference("IndependantBranches") ) { + my $userenv = C4::Context->userenv; + if ( ($userenv) && ( $userenv->{flags} != 1 ) ) { + $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) "; + push @query_params, $userenv->{branch}; + } + } + $query .= " ORDER BY id"; + my $sth = $dbh->prepare($query); + $sth->execute( @query_params ); + my $cnt = 1; + while ( my $line = $sth->fetchrow_hashref ) { + $line->{count} = $cnt++; + $line->{toggle} = 1 if $cnt % 2; + push @order_loop, $line; + $line->{creationdate} = format_date( $line->{creationdate} ); + $line->{datereceived} = format_date( $line->{datereceived} ); + $total_qty += $line->{'quantity'}; + $total_qtyreceived += $line->{'quantityreceived'}; + $total_price += $line->{'quantity'} * $line->{'ecost'}; + } + } + return \@order_loop, $total_qty, $total_price, $total_qtyreceived; +} - ($count, @results) = &bookseller($searchstring); +=head2 GetRecentAcqui -Looks up a book seller. C<$searchstring> may be either a book seller -ID, or a string to look for in the book seller's name. + $results = GetRecentAcqui($days); -C<$count> is the number of elements in C<@results>. C<@results> is an -array of references-to-hash, whose keys are the fields of of the -aqbooksellers table in the Koha database. +C<$results> is a ref to a table which containts hashref =cut -#' -sub bookseller { - my ($searchstring)=@_; - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("Select * from aqbooksellers where name like ? or id = ?"); - $sth->execute("$searchstring%",$searchstring); - my @results; - while (my $data=$sth->fetchrow_hashref){ - push(@results,$data); - } - $sth->finish; - return(scalar(@results),@results); + +sub GetRecentAcqui { + my $limit = shift; + my $dbh = C4::Context->dbh; + my $query = " + SELECT * + FROM biblio + ORDER BY timestamp DESC + LIMIT 0,".$limit; + + my $sth = $dbh->prepare($query); + $sth->execute; + my $results = $sth->fetchall_arrayref({}); + return $results; } -=item breakdown +=head3 GetContracts + + $contractlist = &GetContracts($booksellerid, $activeonly); + +Looks up the contracts that belong to a bookseller + +Returns a list of contracts - ($count, $results) = &breakdown($ordernumber); +=over -Looks up an order by order ID, and returns its breakdown. +=item C<$booksellerid> is the "id" field in the "aqbooksellers" table. -C<$count> is the number of elements in C<$results>. C<$results> is a -reference-to-array; its elements are references-to-hash, whose keys -are the fields of the aqorderbreakdown table in the Koha database. +=item C<$activeonly> if exists get only contracts that are still active. + +=back =cut -#' -sub breakdown { - my ($id)=@_; - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("Select * from aqorderbreakdown where ordernumber=?"); - $sth->execute($id); - my @results = (); - while (my $data=$sth->fetchrow_hashref){ - push(@results,$data); - } - $sth->finish; - return(scalar(@results),\@results); + +sub GetContracts { + my ( $booksellerid, $activeonly ) = @_; + my $dbh = C4::Context->dbh; + my $query; + if (! $activeonly) { + $query = " + SELECT * + FROM aqcontract + WHERE booksellerid=? + "; + } else { + $query = "SELECT * + FROM aqcontract + WHERE booksellerid=? + AND contractenddate >= CURDATE( )"; + } + my $sth = $dbh->prepare($query); + $sth->execute( $booksellerid ); + my @results; + while (my $data = $sth->fetchrow_hashref ) { + push(@results, $data); + } + $sth->finish; + return @results; } -=item branches +#------------------------------------------------------------# - ($count, @results) = &branches(); +=head3 GetContract -Returns a list of all library branches. + $contract = &GetContract($contractID); -C<$count> is the number of elements in C<@results>. C<@results> is an -array of references-to-hash, whose keys are the fields of the branches -table of the Koha database. +Looks up the contract that has PRIMKEY (contractnumber) value $contractID -=cut -#' -sub branches { - my $dbh = C4::Context->dbh; - my $sth = $dbh->prepare("Select * from branches order by branchname"); - my @results = (); +Returns a contract - $sth->execute(); - while (my $data = $sth->fetchrow_hashref) { - push(@results,$data); - } # while +=cut - $sth->finish; - return(scalar(@results), @results); -} # sub branches - -=item updatesup - - &updatesup($bookseller); - -Updates the information for a given bookseller. C<$bookseller> is a -reference-to-hash whose keys are the fields of the aqbooksellers table -in the Koha database. It must contain entries for all of the fields. -The entry to modify is determined by C<$bookseller-E{id}>. - -The easiest way to get all of the necessary fields is to look up a -book seller with C<&booksellers>, modify what's necessary, then call -C<&updatesup> with the result. - -=cut -#' -sub updatesup { - my ($data)=@_; - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("Update aqbooksellers set - name=?,address1=?,address2=?,address3=?,address4=?,postal=?, - phone=?,fax=?,url=?,contact=?,contpos=?,contphone=?,contfax=?,contaltphone=?, - contemail=?,contnotes=?,active=?, - listprice=?, invoiceprice=?,gstreg=?, listincgst=?, - invoiceincgst=?, specialty=?,discount=?,invoicedisc=?, - nocalc=? - where id=?"); - $sth->execute($data->{'name'},$data->{'address1'},$data->{'address2'}, - $data->{'address3'},$data->{'address4'},$data->{'postal'},$data->{'phone'}, - $data->{'fax'},$data->{'url'},$data->{'contact'},$data->{'contpos'}, - $data->{'contphone'},$data->{'contfax'},$data->{'contaltphone'}, - $data->{'contemail'}, - $data->{'contnote'},$data->{'active'},$data->{'listprice'}, - $data->{'invoiceprice'},$data->{'gstreg'},$data->{'listincgst'}, - $data->{'invoiceincgst'},$data->{'specialty'},$data->{'discount'}, - $data->{'invoicedisc'},$data->{'nocalc'},$data->{'id'}); - $sth->finish; -} - -=item insertsup - - $id = &insertsup($bookseller); - -Creates a new bookseller. C<$bookseller> is a reference-to-hash whose -keys are the fields of the aqbooksellers table in the Koha database. -All fields must be present. - -Returns the ID of the newly-created bookseller. - -=cut -#' -sub insertsup { - my ($data)=@_; - my $dbh = C4::Context->dbh; - 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 (?)"); - $sth->execute($data2->{'max(id)'}); - $sth->finish; - $data->{'id'}=$data2->{'max(id)'}; - updatesup($data); - return($data->{'id'}); -} - -END { } # module clean-up code here (global destructor) +sub GetContract { + my ( $contractno ) = @_; + my $dbh = C4::Context->dbh; + my $query = " + SELECT * + FROM aqcontract + WHERE contractnumber=? + "; + + my $sth = $dbh->prepare($query); + $sth->execute( $contractno ); + my $result = $sth->fetchrow_hashref; + return $result; +} 1; __END__ -=back - =head1 AUTHOR -Koha Developement team +Koha Development Team =cut