X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=C4%2FAcquisition.pm;h=984c15a5f7ecee362b202648af99e1c1a3856ca6;hb=a70822480ec1b479603092cea627f7c785d38a77;hp=3edf80d0e869a0f414b0b0d5258995bf1a50533c;hpb=3aad0103857860200fec4301689796ad612a6355;p=koha.git diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm index 3edf80d0e8..984c15a5f7 100644 --- a/C4/Acquisition.pm +++ b/C4/Acquisition.pm @@ -13,28 +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::Date; +use C4::Debug; +use C4::Dates qw(format_date format_date_in_iso); use MARC::Record; use C4::Suggestions; -use Time::localtime; +use C4::Biblio; +use C4::Debug; +use C4::SQLHelper qw(InsertInTable); -# use C4::Biblio; +use Time::localtime; +use HTML::Entities; use vars qw($VERSION @ISA @EXPORT); -# set the version for version checking -$VERSION = do { my @v = '$Revision$' =~ /\d+/g; shift(@v) . "." . join( "_", map { sprintf "%03d", $_ } @v ); }; +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; + +} + + + + -# used in reciveorder subroutine -# to provide library specific handling -my $library_name = C4::Context->preference("LibraryName"); =head1 NAME @@ -42,1533 +115,1592 @@ 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 - -=cut +=head2 FUNCTIONS ABOUT BASKETS -@ISA = qw(Exporter); -@EXPORT = qw( - &getbasket &getbasketcontent &newbasket &closebasket - - &getorders &getallorders &getrecorders - &getorder &neworder &delorder - &ordersearch &histsearch - &modorder &getsingleorder &invoice &receiveorder - &updaterecorder &newordernum - &getsupplierlistwithlateorders - &getlateorders - &getparcels &getparcelinformation - &bookfunds &curconvert &getcurrencies &bookfundbreakdown - &updatecurrencies &getcurrency - &updatesup &insertsup - &bookseller &breakdown -); +=head3 GetBasket -# -# -# -# BASKETS -# -# -# + $aqbasket = &GetBasket($basketnumber); -=item getbasket +get all basket informations in aqbasket for a given basket - $aqbasket = &getbasket($basketnumber); +B informations for a given basket returned as a hashref. -get all basket informations in aqbasket for a given basket =cut -sub getbasket { +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=?" - ); + 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); - return ( $sth->fetchrow_hashref ); - $sth->finish(); + my $basket = $sth->fetchrow_hashref; + return ( $basket ); } -=item getbasketcontent +#------------------------------------------------------------# - ($count, @orders) = &getbasketcontent($basketnumber, $booksellerID); +=head3 NewBasket -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. - -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>. + $basket = &NewBasket( $booksellerid, $authorizedby, $basketname, + $basketnote, $basketbooksellernote, $basketcontractnumber ); -=cut +Create a new basket in aqbasket table -#' -sub getbasketcontent { - my ( $basketno, $supplier, $orderby ) = @_; - my $dbh = C4::Context->dbh; - my $query = -"SELECT aqorderbreakdown.*,biblio.*,biblioitems.*,aqorders.*,biblio.title FROM aqorders,biblio,biblioitems - LEFT JOIN aqorderbreakdown ON aqorderbreakdown.ordernumber=aqorders.ordernumber - where 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=?"; - } +=over - $orderby = "biblioitems.publishercode" unless $orderby; - $query .= " ORDER BY $orderby"; - my $sth = $dbh->prepare($query); - if ( $supplier ne '' ) { - $sth->execute( $basketno, $supplier ); - } - else { - $sth->execute($basketno); - } - my @results; +=item C<$booksellerid> is a foreign key in the aqbasket table - # print $query; - my $i = 0; - while ( my $data = $sth->fetchrow_hashref ) { - $results[$i] = $data; - $i++; - } - $sth->finish; - return ( $i, @results ); -} +=item C<$authorizedby> is the username of who created the basket -=item newbasket +=back - $basket = &newbasket(); +The other parameters are optional, see ModBasketHeader for more info on them. -Create a new basket in aqbasket table =cut -sub newbasket { - my ( $booksellerid, $authorisedby ) = @_; +# 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( -"insert into aqbasket (creationdate,booksellerid,authorisedby) values(now(),'$booksellerid','$authorisedby')" - ); - + $dbh->do($query); #find & return basketno MYSQL dependant, but $dbh->last_insert_id always returns null :-( my $basket = $dbh->{'mysql_insertid'}; - return ($basket); + ModBasketHeader($basket, $basketname || '', $basketnote || '', $basketbooksellernote || '', $basketcontractnumber || undef); + return $basket; } -=item closebasket +#------------------------------------------------------------# - &newbasket($basketno); +=head3 CloseBasket + + &CloseBasket($basketno); + +close a basket (becomes unmodifiable,except for recieves) -close a basket (becomes unmodifiable,except for recieves =cut -sub closebasket { +sub CloseBasket { my ($basketno) = @_; my $dbh = C4::Context->dbh; - my $sth = - $dbh->prepare("update aqbasket set closedate=now() where basketno=?"); + my $query = " + UPDATE aqbasket + SET closedate=now() + WHERE basketno=? + "; + my $sth = $dbh->prepare($query); $sth->execute($basketno); } -=item neworder - - &neworder($basket, $biblionumber, $title, $quantity, $listprice, - $booksellerid, $who, $notes, $bookfund, $biblioitemnumber, $rrp, - $ecost, $gst, $budget, $unitprice, $subscription, - $booksellerinvoicenumber); - -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<$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. +=head3 GetBasketAsCSV -C<$budget> is effectively ignored. + &GetBasketAsCSV($basketno); -C<$subscription> may be either "yes", or anything else for "no". +Export a basket as CSV =cut -#' -sub neworder { - my ( - $basketno, $bibnum, $title, $quantity, - $listprice, $booksellerid, $authorisedby, $notes, - $bookfund, $bibitemnum, $rrp, $ecost, - $gst, $budget, $cost, $sub, - $invoice, $sort1, $sort2 - ) - = @_; - - my $year = localtime->year() + 1900; - my $month = localtime->mon() + 1; # months starts at 0, add 1 - - if ( !$budget || $budget eq 'now' ) { - $budget = "now()"; +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); } - # if month is july or more, budget start is 1 jul, next year. - elsif ( $month >= '7' ) { - ++$year; # add 1 to year , coz its next year - $budget = "'$year-07-01'"; - } - else { + # Sort by publishercode + # TODO: Sort by publishercode then by title + @rows = sort { @$a[7] cmp @$b[7] } @rows; - # START OF NEW BUDGET, 1ST OF JULY, THIS YEAR - $budget = "'$year-07-01'"; - } + foreach my $row (@rows) { + $csv->combine(@$row); + $output .= $csv->string() . "\n"; - if ( $sub eq 'yes' ) { - $sub = 1; - } - else { - $sub = 0; } + + return $output; - # 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,budgetdate,entrydate) - values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,$budget,now() )" - ); - $sth->execute( - $bibnum, $title, $basketno, $quantity, $listprice, - $notes, $bibitemnum, $rrp, $ecost, $gst, - $cost, $sub, $sort1, $sort2 - ); - $sth->finish; +=head3 CloseBasketgroup - #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, $ordnum ); + &CloseBasketgroup($basketgroupno); + +close a basketgroup + +=cut + +sub CloseBasketgroup { + my ($basketgroupno) = @_; + my $dbh = C4::Context->dbh; + my $sth = $dbh->prepare(" + UPDATE aqbasketgroups + SET closed=1 + WHERE id=? + "); + $sth->execute($basketgroupno); } -=item delorder +#------------------------------------------------------------# - &delorder($biblionumber, $ordernumber); +=head3 ReOpenBaskergroup($basketgroupno) -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. + &ReOpenBaskergroup($basketgroupno); + +reopen a basketgroup =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 ReOpenBasketgroup { + my ($basketgroupno) = @_; + my $dbh = C4::Context->dbh; + my $sth = $dbh->prepare(" + UPDATE aqbasketgroups + SET closed=0 + WHERE id=? + "); + $sth->execute($basketgroupno); } -=item modorder +#------------------------------------------------------------# - &modorder($title, $ordernumber, $quantity, $listprice, - $biblionumber, $basketno, $supplier, $who, $notes, - $bookfundid, $bibitemnum, $rrp, $ecost, $gst, $budget, - $unitprice, $booksellerinvoicenumber); -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. +=head3 DelBasket + + &DelBasket($basketno); + +Deletes the basket that has basketno field $basketno in the aqbasket table. -Entries with order number C<$ordernumber> in the aqorderbreakdown -table are also updated to the new book fund ID. +=over + +=item C<$basketno> is the primary key of the basket in the aqbasket table. + +=back =cut -#' -sub modorder { - my ( - $title, $ordnum, $quantity, $listprice, $bibnum, - $basketno, $supplier, $who, $notes, $bookfund, - $bibitemnum, $rrp, $ecost, $gst, $budget, - $cost, $invoice, $sort1, $sort2 - ) - = @_; +sub DelBasket { + my ( $basketno ) = @_; + my $query = "DELETE FROM aqbasket WHERE basketno=?"; 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 ); - } + my $sth = $dbh->prepare($query); + $sth->execute($basketno); $sth->finish; } -=item newordernum +#------------------------------------------------------------# - $order = &newordernum(); +=head3 ModBasket -Finds the next unused order number in the aqorders table of the Koha -database, and returns it. + &ModBasket($basketinfo); + +Modifies a basket, using a hashref $basketinfo for the relevant information, only $basketinfo->{'basketno'} is required. + +=over + +=item C<$basketno> is the primary key of the basket in the aqbasket table. + +=back =cut -#' -# FIXME - Race condition -sub newordernum { +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("Select max(ordernumber) from aqorders"); - $sth->execute; - my $data = $sth->fetchrow_arrayref; - my $ordnum = $$data[0]; - $ordnum++; + my $sth = $dbh->prepare($query); + $sth->execute(@params); $sth->finish; - return ($ordnum); } -=item receiveorder +#------------------------------------------------------------# - &receiveorder($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. +=head3 ModBasketHeader -Updates the order with bibilionumber C<$biblionumber> and ordernumber -C<$ordernumber>. + &ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber); -Also updates the book fund ID in the aqorderbreakdown table. +Modifies a basket's header. -=cut +=over -#' -sub receiveorder { - my ( - $biblio, $ordnum, $quantrec, $user, $cost, - $invoiceno, $freight, $rrp, $bookfund - ) - = @_; - my $dbh = C4::Context->dbh; - my $sth = $dbh->prepare( -"update aqorders set quantityreceived=?,datereceived=now(),booksellerinvoicenumber=?, - unitprice=?,freight=?,rrp=? - where biblionumber=? and ordernumber=?" - ); - my $suggestionid = GetSuggestionFromBiblionumber( $dbh, $biblio ); - if ($suggestionid) { - ModStatus( $suggestionid, 'AVAILABLE', '', $biblio ); - } - $sth->execute( $quantrec, $invoiceno, $cost, $freight, $rrp, $biblio, - $ordnum ); - $sth->finish; +=item C<$basketno> is the "basketno" field in the "aqbasket" table; - # Allows libraries to change their bookfund during receiving orders - # allows them to adjust budgets - if ( C4::Context->preferene("LooseBudgets") ) { - my $sth = $dbh->prepare( - "UPDATE aqorderbreakdown SET bookfundid=? - WHERE ordernumber=?" - ); - $sth->execute( $bookfund, $ordnum ); - $sth->finish; - } -} +=item C<$basketname> is the "basketname" field in the "aqbasket" table; -=item updaterecorder +=item C<$note> is the "note" field in the "aqbasket" table; - &updaterecorder($biblionumber, $ordernumber, $user, $unitprice, - $bookfundid, $rrp); +=item C<$booksellernote> is the "booksellernote" 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<$contractnumber> is the "contractnumber" (foreign) key in the "aqbasket" table. -C<$user> is ignored. +=back =cut -#' -sub updaterecorder { - my ( $biblio, $ordnum, $user, $cost, $bookfund, $rrp ) = @_; +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( - "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 ); + 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 -# -# +#------------------------------------------------------------# -=item getorders +=head3 GetBasketsByBookseller - ($count, $orders) = &getorders($booksellerid); + @results = &GetBasketsByBookseller($booksellerid, $extra); -Finds pending orders from the bookseller with the given ID. Ignores -completed and cancelled orders. +Returns a list of hashes of all the baskets that belong to bookseller 'booksellerid'. -C<$count> is the number of elements in C<@{$orders}>. +=over -C<$orders> is a reference-to-array; each element is a -reference-to-hash with the following fields: +=item C<$booksellerid> is the 'id' field of the bookseller in the aqbooksellers table -=over 4 +=item C<$extra> is the extra sql parameters, can be -=item C + $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) -Gives the number of orders in with this basket number. +=back -=item C +=cut -=item C +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 +} -=item C +#------------------------------------------------------------# -These give the value of the corresponding field in the aqorders table -of the Koha database. +=head3 GetBasketsByBasketgroup -=back + $baskets = &GetBasketsByBasketgroup($basketgroupid); -Results are ordered from most to least recent. +Returns a reference to all baskets that belong to basketgroup $basketgroupid. =cut -#' -sub getorders { - my ($supplierid) = @_; +sub GetBasketsByBasketgroup { + my $basketgroupid = shift; + my $query = "SELECT * FROM aqbasket + LEFT JOIN aqcontract USING(contractnumber) WHERE basketgroupid=?"; 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 and (to_days(now())-to_days(closedate) < 180 or closedate is null)"; - if ( C4::Context->preference("IndependantBranches") ) { - my $userenv = C4::Context->userenv; - if ( ($userenv) && ( $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 ); - } + my $sth = $dbh->prepare($query); + $sth->execute($basketgroupid); + my $results = $sth->fetchall_arrayref({}); $sth->finish; - return ( scalar(@results), \@results ); + return $results } -=item getorder +#------------------------------------------------------------# + +=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, - ($order, $ordernumber) = &getorder($biblioitemnumber, $biblionumber); +$hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table, -Looks up the order with the given biblionumber and biblioitemnumber. +$hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group, -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. +$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 getorder { - my ( $bi, $bib ) = @_; +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( -"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'} ); + 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; } -=item getsingleorder +#------------------------------------------------------------# - $order = &getsingleorder($ordernumber); +=head3 ModBasketgroup -Looks up an order by order number. + ModBasketgroup(\%hashref); -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. +Modifies a basketgroup in the aqbasketgroups table, and add the baskets to it. -=cut +$hashref->{'id'} is the 'id' field of the basketgroup in the aqbasketgroup table, this parameter is mandatory, -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); -} +$hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table, -=item getallorders +$hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group, - ($count, @results) = &getallorders($booksellerid); +$hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table, -Looks up all of the pending orders from the supplier with the given -bookseller ID. Ignores cancelled and completed orders. +$hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table, -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. +$hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table, -C<@results> is sorted alphabetically by book title. +$hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise. =cut -#' -sub getallorders { +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); - #gets all orders from a certain supplier, orders them alphabetically - my ($supplierid) = @_; - my $dbh = C4::Context->dbh; - my @results = (); - my $strsth = "Select count(*),authorisedby,creationdate,aqbasket.basketno, -closedate,surname,firstname,aqorders.biblionumber,aqorders.title, aqorders.ordernumber -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 "; + $sth = $dbh->prepare('UPDATE aqbasket SET basketgroupid = NULL WHERE basketgroupid = ?'); + $sth->execute($basketgroupinfo->{'id'}); - if ( C4::Context->preference("IndependantBranches") ) { - my $userenv = C4::Context->userenv; - if ( ($userenv) && ( $userenv->{flags} != 1 ) ) { - $strsth .= - " and (borrowers.branchcode = '" - . $userenv->{branch} - . "' or borrowers.branchcode ='')"; + 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; } } - $strsth .= " group by basketno order by aqbasket.basketno"; - my $sth = $dbh->prepare($strsth); - $sth->execute($supplierid); - while ( my $data = $sth->fetchrow_hashref ) { - push( @results, $data ); - } $sth->finish; - return ( scalar(@results), @results ); } -=item getparcelinformation +#------------------------------------------------------------# - ($count, @results) = &getparcelinformation($booksellerid, $code, $date); +=head3 DelBasketgroup -Looks up all of the received items from the supplier with the given -bookseller ID at the given date, for the given code. Ignores cancelled and completed orders. + DelBasketgroup($basketgroupid); -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. +Deletes a basketgroup in the aqbasketgroups table, and removes the reference to it from the baskets, -C<@results> is sorted alphabetically by book title. +=over -=cut +=item C<$basketgroupid> is the 'id' field of the basket in the aqbasketgroup table -#' -sub getparcelinformation { +=back - #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.title,aqorders.ordernumber, aqorders.quantity, aqorders.quantityreceived, aqorders.unitprice, aqorders.listprice, aqorders.rrp, aqorders.ecost from aqorders,aqbasket left join borrowers on aqbasket.authorisedby=borrowers.borrowernumber where aqbasket.basketno=aqorders.basketno and aqbasket.booksellerid=? and aqorders.booksellerinvoicenumber like \"$code\" and aqorders.datereceived= \'$datereceived\'"; +=cut - if ( C4::Context->preference("IndependantBranches") ) { - my $userenv = C4::Context->userenv; - if ( ($userenv) && ( $userenv->{flags} != 1 ) ) { - $strsth .= - " and (borrowers.branchcode = '" - . $userenv->{branch} - . "' or borrowers.branchcode ='')"; - } - } - $strsth .= " order by aqbasket.basketno"; - ### parcelinformation : $strsth - my $sth = $dbh->prepare($strsth); - $sth->execute($supplierid); - while ( my $data = $sth->fetchrow_hashref ) { - push( @results, $data ); - } - my $count = scalar(@results); - ### countparcelbiblio: $count +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; - - return ( scalar(@results), @results ); } -=item getparcelinformation +#------------------------------------------------------------# - ($count, @results) = &getparcelinformation($booksellerid, $code, $date); -Looks up all of the received items from the supplier with the given -bookseller ID at the given date, for the given code. Ignores cancelled and completed orders. +=head2 FUNCTIONS ABOUT ORDERS -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. +=head3 GetBasketgroup -C<@results> is sorted alphabetically by book title. + $basketgroup = &GetBasketgroup($basketgroupid); -=cut +Returns a reference to the hash containing all infermation about the basketgroup. -#' -sub getparcelinformation { +=cut - #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.title,aqorders.ordernumber, aqorders.quantity, aqorders.quantityreceived, aqorders.unitprice, aqorders.listprice, aqorders.rrp, aqorders.ecost from aqorders,aqbasket left join borrowers on aqbasket.authorisedby=borrowers.borrowernumber where aqbasket.basketno=aqorders.basketno and aqbasket.booksellerid=? and aqorders.booksellerinvoicenumber like \"$code\" and aqorders.datereceived= \'$datereceived\'"; +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 + +#------------------------------------------------------------# + +=head3 GetPendingOrders + + $orders = &GetPendingOrders($booksellerid, $grouped, $owner); + +Finds pending orders from the bookseller with the given ID. Ignores +completed and cancelled 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 + +=item C + +=item C + +=item C + +=back + +These give the value of the corresponding field in the aqorders table +of the Koha database. + +Results are ordered from most to least recent. + +=cut + +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") ) { - my $userenv = C4::Context->userenv; if ( ($userenv) && ( $userenv->{flags} != 1 ) ) { - $strsth .= - " and (borrowers.branchcode = '" - . $userenv->{branch} - . "' or borrowers.branchcode ='')"; + $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"; - ### parcelinformation : $strsth + my $sth = $dbh->prepare($strsth); - $sth->execute($supplierid); - while ( my $data = $sth->fetchrow_hashref ) { - push( @results, $data ); - } - my $count = scalar(@results); - ### countparcelbiblio: $count + $sth->execute( @query_params ); + my $results = $sth->fetchall_arrayref({}); $sth->finish; - - return ( scalar(@results), @results ); + return $results; } -=item getsupplierlistwithlateorders +#------------------------------------------------------------# + +=head3 GetOrders + + @orders = &GetOrders($basketnumber, $orderby); - %results = &getsupplierlistwithlateorders; +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. -Searches for suppliers with late orders. +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 getsupplierlistwithlateorders { - my $delay = shift; +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; +} -#FIXME NOT quite sure that this operation is valid for DBMs different from Mysql, HOPING so -#should be tested with other DBMs +#------------------------------------------------------------# - my $strsth; - my $dbdriver = C4::Context->config("db_scheme") || "mysql"; - if ( $dbdriver eq "mysql" ) { - $strsth = "SELECT DISTINCT aqbasket.booksellerid, aqbooksellers.name - FROM aqorders, aqbasket - LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id - WHERE aqorders.basketno = aqbasket.basketno AND - (closedate < DATE_SUB(CURDATE( ),INTERVAL $delay DAY) AND (datereceived = '' or datereceived is null)) - "; - } - else { - $strsth = "SELECT DISTINCT aqbasket.booksellerid, aqbooksellers.name - FROM aqorders, aqbasket - LEFT JOIN aqbooksellers ON aqbasket.aqbooksellerid = aqbooksellers.id - WHERE aqorders.basketno = aqbasket.basketno AND - (closedate < (CURDATE( )-(INTERVAL $delay DAY))) AND (datereceived = '' or datereceived is null)) - "; - } +=head3 GetOrderNumber - # warn "C4::Acquisition getsupplierlistwithlateorders : ".$strsth; - my $sth = $dbh->prepare($strsth); - $sth->execute; - my %supplierlist; - while ( my ( $id, $name ) = $sth->fetchrow ) { - $supplierlist{$id} = $name; - } - return %supplierlist; -} + $ordernumber = &GetOrderNumber($biblioitemnumber, $biblionumber); -=item getlateorders +Looks up the ordernumber with the given biblionumber and biblioitemnumber. - %results = &getlateorders; +Returns the number of this order. -Searches for suppliers with late orders. +=over -=cut +=item C<$ordernumber> is the order number. -#' -sub getlateorders { - my $delay = shift; - my $supplierid = shift; - my $branch = shift; +=back - my $dbh = C4::Context->dbh; +=cut - #BEWARE, order of parenthesis and LEFT JOIN is important for speed - my $strsth; - my $dbdriver = C4::Context->config("db_scheme") || "mysql"; +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 ); - # warn " $dbdriver"; - if ( $dbdriver eq "mysql" ) { - $strsth = "SELECT aqbasket.basketno, - DATE(aqbasket.closedate) as orderdate, aqorders.quantity - IFNULL(aqorders.quantityreceived,0) as quantity, aqorders.rrp as unitpricesupplier,aqorders.ecost as unitpricelib, - (aqorders.quantity - IFNULL(aqorders.quantityreceived,0)) * aqorders.rrp as subtotal, aqbookfund.bookfundname as budget, borrowers.branchcode as branch, - aqbooksellers.name as supplier, - aqorders.title, biblio.author, biblioitems.publishercode as publisher, biblioitems.publicationyear, - DATEDIFF(CURDATE( ),closedate) AS latesince - FROM - (( ( - (aqorders LEFT JOIN biblio on biblio.biblionumber = aqorders.biblionumber) LEFT JOIN biblioitems on biblioitems.biblionumber=biblio.biblionumber - ) LEFT JOIN aqorderbreakdown on aqorders.ordernumber = aqorderbreakdown.ordernumber - ) LEFT JOIN aqbookfund on aqorderbreakdown.bookfundid = aqbookfund.bookfundid - ),(aqbasket LEFT JOIN borrowers on aqbasket.authorisedby = borrowers.borrowernumber) LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id - WHERE aqorders.basketno = aqbasket.basketno AND (closedate < DATE_SUB(CURDATE( ),INTERVAL $delay DAY)) - AND ((datereceived = '' OR datereceived is null) OR (aqorders.quantityreceived < aqorders.quantity) ) "; - $strsth .= " AND aqbasket.booksellerid = $supplierid " if ($supplierid); - $strsth .= " AND borrowers.branchcode like \'" . $branch . "\'" - if ($branch); - $strsth .= - " AND borrowers.branchcode like \'" - . C4::Context->userenv->{branch} . "\'" - if ( C4::Context->preference("IndependantBranches") - && C4::Context->userenv - && C4::Context->userenv->{flags} != 1 ); - $strsth .= -" HAVING quantity<>0 AND unitpricesupplier<>0 AND unitpricelib<>0 ORDER BY latesince,basketno,borrowers.branchcode, supplier "; - } - else { - $strsth = "SELECT aqbasket.basketno, - DATE(aqbasket.closedate) as orderdate, - aqorders.quantity, aqorders.rrp as unitpricesupplier,aqorders.ecost as unitpricelib, aqorders.quantity * aqorders.rrp as subtotal - aqbookfund.bookfundname as budget, borrowers.branchcode as branch, - aqbooksellers.name as supplier, - biblio.title, biblio.author, biblioitems.publishercode as publisher, biblioitems.publicationyear, - (CURDATE - closedate) AS latesince - FROM - (( ( - (aqorders LEFT JOIN biblio on biblio.biblionumber = aqorders.biblionumber) LEFT JOIN biblioitems on biblioitems.biblionumber=biblio.biblionumber - ) LEFT JOIN aqorderbreakdown on aqorders.ordernumber = aqorderbreakdown.ordernumber - ) LEFT JOIN aqbookfund on aqorderbreakdown.bookfundid = aqbookfund.bookfundid - ),(aqbasket LEFT JOIN borrowers on aqbasket.authorisedby = borrowers.borrowernumber) LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id - WHERE aqorders.basketno = aqbasket.basketno AND (closedate < (CURDATE -(INTERVAL $delay DAY)) - AND ((datereceived = '' OR datereceived is null) OR (aqorders.quantityreceived < aqorders.quantity) ) "; - $strsth .= " AND aqbasket.booksellerid = $supplierid " if ($supplierid); - $strsth .= " AND borrowers.branchcode like \'" . $branch . "\'" - if ($branch); - $strsth .= - " AND borrowers.branchcode like \'" - . C4::Context->userenv->{branch} . "\'" - if ( C4::Context->preference("IndependantBranches") - && C4::Context->userenv->{flags} != 1 ); - $strsth .= - " ORDER BY latesince,basketno,borrowers.branchcode, supplier"; - } - warn "C4::Acquisition : getlateorders SQL:" . $strsth; - my $sth = $dbh->prepare($strsth); - $sth->execute; - my @results; - my $hilighted = 1; - while ( my $data = $sth->fetchrow_hashref ) { - $data->{hilighted} = $hilighted if ( $hilighted > 0 ); - $data->{orderdate} = format_date( $data->{orderdate} ); - push @results, $data; - $hilighted = -$hilighted; - } - $sth->finish; - return ( scalar(@results), @results ); + return $sth->fetchrow; } -# 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 ); - } +=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 tables of the Koha database. + +=cut + +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 ( scalar(@results), @results ); + return $data; } -=item ordersearch +#------------------------------------------------------------# - ($count, @results) = &ordersearch($search, $biblionumber, $complete); +=head3 NewOrder -Searches for orders. + &NewOrder(\%hashref); -C<$search> may take one of several forms: if it is an ISBN, -C<&ordersearch> returns orders with that ISBN. If C<$search> is an -order number, C<&ordersearch> returns orders with that order number -and biblionumber C<$biblionumber>. Otherwise, C<$search> is considered -to be a space-separated list of search terms; in this case, all of the -terms must appear in the title (matching the beginning of title -words). +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. -If C<$complete> is C, the results will include only completed -orders. In any case, C<&ordersearch> ignores cancelled orders. +=over -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: +=item $hashref->{'basketno'} is the basketno foreign key in aqorders, it is mandatory -=over 4 +=item $hashref->{'ordernumber'} is a "minimum order number." -=item C +=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 C +=item $hashref->{'subscription'} may be either "yes", or anything else for "no". -=item C +=item $hashref->{'uncertainprice'} may be 0 for "the price is known" or 1 for "the price is uncertain" -=item C +=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 ordersearch { - my ( $search, $id, $biblio, $catview ) = @_; +sub NewOrder { + my $orderinfo = shift; +#### ------------------------------ my $dbh = C4::Context->dbh; - my @data = split( ' ', $search ); - my @searchterms; - if ($id) { - @searchterms = ($id); + 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}; } - map { push( @searchterms, "$_%", "% $_%" ) } @data; - push( @searchterms, $search, $search, $biblio ); - my $query; - if ($id) { - $query = - "SELECT *,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=?)) "; + if ( defined $orderinfo->{subscription} && $orderinfo->{'subscription'} eq 'yes' ) { + $orderinfo->{'subscription'} = 1; + } else { + $orderinfo->{'subscription'} = 0; } - else { - $query = - "SELECT *,biblio.title FROM aqorders,biblioitems,biblio,aqbasket - WHERE aqorders.biblioitemnumber = biblioitems.biblioitemnumber AND - aqorders.basketno = aqbasket.basketno - AND biblio.biblionumber=aqorders.biblionumber - AND ((datecancellationprinted is NULL) - OR (datecancellationprinted = '0000-00-00')) - AND (aqorders.quantityreceived < aqorders.quantity OR aqorders.quantityreceived is NULL) - AND ((" - . ( - join( " AND ", - map { "(biblio.title like ? OR biblio.title like ?)" } @data ) - ) - . ") or biblioitems.isbn=? OR (aqorders.ordernumber=? AND aqorders.biblionumber=?)) "; + $orderinfo->{'entrydate'} ||= C4::Dates->new()->output("iso"); + if (!$orderinfo->{quantityreceived}) { + $orderinfo->{quantityreceived} = 0; } - $query .= " GROUP BY aqorders.ordernumber"; - my $sth = $dbh->prepare($query); - $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 @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,name,aqbasket.creationdate,aqorders.datereceived, aqorders.quantity, aqorders.quantityreceived, aqorders.ecost, aqorders.ordernumber 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; - if ( ($userenv) && ( $userenv->{flags} != 1 ) ) { - $query .= - " and (borrowers.branchcode = '" - . $userenv->{branch} - . "' or borrowers.branchcode ='')"; - } - } - $query .= " order by booksellerid"; - warn "query histearch: " . $query; - my $sth = $dbh->prepare($query); - $sth->execute; - 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; + my $ordernumber=InsertInTable("aqorders",$orderinfo); + return ( $orderinfo->{'basketno'}, $ordernumber ); } -# -# -# MONEY -# -# -=item invoice - ($count, @results) = &invoice($booksellerinvoicenumber); +#------------------------------------------------------------# -Looks up orders by invoice number. +=head3 NewOrderItem -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. + &NewOrderItem(); =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 ); +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); } -=item bookfunds +#------------------------------------------------------------# - ($count, @results) = &bookfunds(); +=head3 ModOrder -Returns a list of all book funds. + &ModOrder(\%hashref); -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. +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 bookfunds { - my ($branch) = @_; - my $dbh = C4::Context->dbh; - my $userenv = C4::Context->userenv; - my $branch = $userenv->{branch}; - my $strsth; - - if ( $branch ne '' ) { - $strsth = "SELECT * FROM aqbookfund,aqbudget WHERE aqbookfund.bookfundid - =aqbudget.bookfundid AND startdatenow() AND (aqbookfund.branchcode is null or aqbookfund.branchcode='' or aqbookfund.branchcode= ? ) - GROUP BY aqbookfund.bookfundid ORDER BY bookfundname"; - } - else { - $strsth = "SELECT * FROM aqbookfund,aqbudget WHERE aqbookfund.bookfundid - =aqbudget.bookfundid AND startdatenow() - GROUP BY aqbookfund.bookfundid ORDER BY bookfundname"; - } - my $sth = $dbh->prepare($strsth); - if ( $branch ne '' ) { - $sth->execute($branch); - } - else { - $sth->execute; - } - my @results = (); - while ( my $data = $sth->fetchrow_hashref ) { - push( @results, $data ); +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; - return ( scalar(@results), @results ); } -=item bookfundbreakdown +#------------------------------------------------------------# - returns the total comtd & spent for a given bookfund, and a given year - used in acqui-home.pl -=cut +=head3 ModOrderItem -#' + &ModOrderItem(\%hashref); -sub bookfundbreakdown { - my ( $id, $year, $start, $end ) = @_; - my $dbh = C4::Context->dbh; +Modifies the itemnumber in the aqorders_items table. The input hash needs three entities: - # if no start/end dates given defaut to everything - if ( !$start ) { - $start = '0000-00-00'; - $end = 'now()'; - } +=over - # do a query for spent totals. - my $sth = $dbh->prepare( - "Select quantity,datereceived,freight,unitprice,listprice,ecost, - quantityreceived,subscription - from aqorders left join aqorderbreakdown on - aqorders.ordernumber=aqorderbreakdown.ordernumber - where bookfundid=? and (datecancellationprinted is NULL or - datecancellationprinted='0000-00-00') and - ((datereceived >= ? and datereceived < ?) or - (budgetdate >= ? and budgetdate < ?))" - ); - $sth->execute( $id, $start, $end, $start, $end ); +=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 - my $spent = 0; - while ( my $data = $sth->fetchrow_hashref ) { - if ( $data->{'subscription'} == 1 ) { - $spent += $data->{'quantity'} * $data->{'unitprice'}; - } - else { +=back - my $leftover = $data->{'quantity'} - $data->{'quantityreceived'}; - $spent += ( $data->{'unitprice'} ) * $data->{'quantityreceived'}; +=cut - } +sub ModOrderItem { + my $orderiteminfo = shift; + if (! $orderiteminfo->{'ordernumber'} || ! $orderiteminfo->{'itemnumber'} || ! $orderiteminfo->{'newitemnumber'}){ + die "Ordernumber, itemnumber and newitemnumber is required"; } - # then do a seperate query for commited totals, (pervious single query was - # returning incorrect comitted results. - - my $query = "Select quantity,datereceived,freight,unitprice, - listprice,ecost,quantityreceived as qrev, - subscription,title,itemtype,aqorders.biblionumber, - aqorders.booksellerinvoicenumber, - quantity-quantityreceived as tleft, - aqorders.ordernumber as ordnum,entrydate,budgetdate, - booksellerid,aqbasket.basketno - from aqorderbreakdown,aqbasket,aqorders - left join biblioitems on - biblioitems.biblioitemnumber=aqorders.biblioitemnumber - where bookfundid=? and aqorders.ordernumber=aqorderbreakdown.ordernumber and - aqorders.basketno=aqbasket.basketno and - (budgetdate >= ? and budgetdate < ?) and - (datecancellationprinted is NULL or datecancellationprinted='0000-00-00')"; - #warn $query; + 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( $id, $start, $end ); + $sth->execute(@params); + return 0; +} - my $comtd; +#------------------------------------------------------------# - my $total = 0; - while ( my $data = $sth->fetchrow_hashref ) { - my $left = $data->{'tleft'}; - if ( !$left || $left eq '' ) { - $left = $data->{'quantity'}; - } - if ( $left && $left > 0 ) { - my $subtotal = $left * $data->{'ecost'}; - $data->{subtotal} = $subtotal; - $data->{'left'} = $left; - $comtd += $subtotal; - } - } - #warn " spent=$spent, comtd=$comtd\n"; - $sth->finish; - return ( $spent, $comtd ); +=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 -=item curconvert + &ModReceiveOrder($biblionumber, $ordernumber, $quantityreceived, $user, + $unitprice, $booksellerinvoicenumber, $biblioitemnumber, + $freight, $bookfund, $rrp); - $foreignprice = &curconvert($currency, $localprice); +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. -Converts the price C<$localprice> to foreign currency C<$currency> by -dividing by the exchange rate, and returns the result. +If a partial order is received, splits the order into two. The received +portion must have a booksellerinvoicenumber. -If no exchange rate is found, C<&curconvert> assumes the rate is one -to one. +Updates the order with bibilionumber C<$biblionumber> and ordernumber +C<$ordernumber>. =cut -#' -sub curconvert { - my ( $currency, $price ) = @_; + +sub ModReceiveOrder { + my ( + $biblionumber, $ordernumber, $quantrec, $user, $cost, + $invoiceno, $freight, $rrp, $budget_id, $datereceived + ) + = @_; 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; +# 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 ( $price / $cur ); + return $datereceived; } +#------------------------------------------------------------# -=item getcurrencies +=head3 SearchOrder - ($count, $currencies) = &getcurrencies(); +@results = &SearchOrder($search, $biblionumber, $complete); -Returns the list of all known currencies. +Searches for orders. -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. +C<$search> may take one of several forms: if it is an ISBN, +C<&ordersearch> returns orders with that ISBN. If C<$search> is an +order number, C<&ordersearch> returns orders with that order number +and biblionumber C<$biblionumber>. Otherwise, C<$search> is considered +to be a space-separated list of search terms; in this case, all of the +terms must appear in the title (matching the beginning of title +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<@results> is an array of references-to-hash with the following keys: + +=over 4 + +=item C + +=item C + +=item C + +=item C + +=back =cut -#' -sub getcurrencies { +sub SearchOrder { +#### -------- SearchOrder------------------------------- + my ($ordernumber, $search, $supplierid, $basket) = @_; + 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 ); + 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 ( scalar(@results), \@results ); + return $results; } -=item updatecurrencies +#------------------------------------------------------------# - &updatecurrencies($currency, $newrate); +=head3 DelOrder -Sets the exchange rate for C<$currency> to be C<$newrate>. + &DelOrder($biblionumber, $ordernumber); + +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 updatecurrencies { - my ( $currency, $rate ) = @_; +sub DelOrder { + my ( $bibnum, $ordernumber ) = @_; my $dbh = C4::Context->dbh; - my $sth = $dbh->prepare("update currency set rate=? where currency=?"); - $sth->execute( $rate, $currency ); + my $query = " + UPDATE aqorders + SET datecancellationprinted=now() + WHERE biblionumber=? AND ordernumber=? + "; + my $sth = $dbh->prepare($query); + $sth->execute( $bibnum, $ordernumber ); $sth->finish; } -# -# -# OTHERS -# -# +=head2 FUNCTIONS ABOUT PARCELS -=item bookseller +=cut - ($count, @results) = &bookseller($searchstring); +#------------------------------------------------------------# -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. +=head3 GetParcel -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. + @results = &GetParcel($booksellerid, $code, $date); + +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. + +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. + +C<@results> is sorted alphabetically by book title. =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; +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 ( scalar(@results), @results ); + + return @results; } -=item breakdown +#------------------------------------------------------------# + +=head3 GetParcels - ($count, $results) = &breakdown($ordernumber); + $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto); -Looks up an order by order ID, and returns its breakdown. +get a lists of parcels. -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. +* 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 + +=item Creation date + +=item Last operation + +=item Number of biblio + +=item Number of items + +=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 ); +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 ( scalar(@results), \@results ); + return @$results; } -=item branches +#------------------------------------------------------------# - ($count, @results) = &branches(); +=head3 GetLateOrders -Returns a list of all library branches. + @results = &GetLateOrders; -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. +Searches for bookseller with late orders. + +return: +the table of supplier with late issues. This table is full of hashref. =cut -#' -sub branches { +sub GetLateOrders { + my $delay = shift; + my $supplierid = shift; + my $branch = shift; + my $dbh = C4::Context->dbh; - my $sth; - if ( C4::Context->preference("IndependantBranches") - && ( C4::Context->userenv ) - && ( C4::Context->userenv->{flags} != 1 ) ) - { - my $strsth = "Select * from branches "; - $strsth .= - " WHERE branchcode = " - . $dbh->quote( C4::Context->userenv->{branch} ); - $strsth .= " order by branchname"; - warn "C4::Acquisition->branches : " . $strsth; - $sth = $dbh->prepare($strsth); + + #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)) "; } - else { - $sth = $dbh->prepare("Select * from branches order by branchname"); + if (defined $supplierid) { + $from .= ' AND aqbasket.booksellerid = ? '; + push @query_params, $supplierid; } - my @results = (); + 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; +} - $sth->execute(); - while ( my $data = $sth->fetchrow_hashref ) { - push( @results, $data ); - } # while +#------------------------------------------------------------# + +=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 - $sth->finish; - return ( scalar(@results), @results ); -} # sub branches +=cut -=item updatesup +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; - &updatesup($bookseller); +# 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 ? "; + push @query_params, "%$title%"; + } -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}>. + if ( defined $author ) { + $query .= " AND biblio.author LIKE ? "; + push @query_params, "%$author%"; + } -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. + if ( defined $name ) { + $query .= " AND aqbooksellers.name LIKE ? "; + push @query_params, "%$name%"; + } -=cut + if ( defined $from_placed_on ) { + $query .= " AND creationdate >= ? "; + push @query_params, $from_placed_on; + } -#' -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=?, notes=? - 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->{'contnotes'}, - $data->{'active'}, $data->{'listprice'}, - $data->{'invoiceprice'}, $data->{'gstreg'}, - $data->{'listincgst'}, $data->{'invoiceincgst'}, - $data->{'specialty'}, $data->{'discount'}, - $data->{'invoicedisc'}, $data->{'nocalc'}, - $data->{'notes'}, $data->{'id'} - ); - $sth->finish; -} + if ( defined $to_placed_on ) { + $query .= " AND creationdate <= ? "; + push @query_params, $to_placed_on; + } -=item insertsup + 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; +} - $id = &insertsup($bookseller); +=head2 GetRecentAcqui -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. + $results = GetRecentAcqui($days); -Returns the ID of the newly-created bookseller. +C<$results> is a ref to a table which containts hashref =cut -#' -sub insertsup { - my ($data) = @_; +sub GetRecentAcqui { + my $limit = shift; my $dbh = C4::Context->dbh; - my $sth = $dbh->prepare("Select max(id) from aqbooksellers"); + my $query = " + SELECT * + FROM biblio + ORDER BY timestamp DESC + LIMIT 0,".$limit; + + my $sth = $dbh->prepare($query); $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'} ); + my $results = $sth->fetchall_arrayref({}); + return $results; } -=item getparcels +=head3 GetContracts - ($count, $results) = &getparcels($dbh, $bookseller, $order, $limit); + $contractlist = &GetContracts($booksellerid, $activeonly); -get a lists of parcels -Returns the count of parcels returned and a pointer on a hash list containing parcel informations as such : - Creation date - Last operation - Number of biblio - Number of items - +Looks up the contracts that belong to a bookseller + +Returns a list of contracts + +=over + +=item C<$booksellerid> is the "id" field in the "aqbooksellers" table. + +=item C<$activeonly> if exists get only contracts that are still active. + +=back =cut -#' -sub getparcels { - my ( $bookseller, $order, $code, $datefrom, $dateto, $limit ) = @_; - my $dbh = C4::Context->dbh; - my $strsth = -"SELECT aqorders.booksellerinvoicenumber, datereceived, count(DISTINCT biblionumber) as biblio, sum(quantity) as itemsexpected, sum(quantityreceived) as itemsreceived from aqorders, aqbasket where aqbasket.basketno = aqorders.basketno and aqbasket.booksellerid = $bookseller and datereceived is not null "; - $strsth .= "and aqorders.booksellerinvoicenumber like \"$code%\" " - if ($code); - $strsth .= "and datereceived >=" . $dbh->quote($datefrom) . " " - if ($datefrom); - $strsth .= "and datereceived <=" . $dbh->quote($dateto) . " " if ($dateto); - $strsth .= "group by aqorders.booksellerinvoicenumber,datereceived "; - $strsth .= "order by $order " if ($order); - $strsth .= " LIMIT 0,$limit" if ($limit); - my $sth = $dbh->prepare($strsth); -### getparcels: $strsth - $sth->execute; +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 $data2 = $sth->fetchrow_hashref ) { - push @results, $data2; + while (my $data = $sth->fetchrow_hashref ) { + push(@results, $data); } - $sth->finish; - return ( scalar(@results), @results ); + return @results; } -=item getparcels +#------------------------------------------------------------# - ($count, $results) = &getparcels($dbh, $bookseller, $order, $limit); +=head3 GetContract -get a lists of parcels -Returns the count of parcels returned and a pointer on a hash list containing parcel informations as such : - Creation date - Last operation - Number of biblio - Number of items - + $contract = &GetContract($contractID); -=cut +Looks up the contract that has PRIMKEY (contractnumber) value $contractID -#' -sub getparcels { - my ( $bookseller, $order, $code, $datefrom, $dateto, $limit ) = @_; - my $dbh = C4::Context->dbh; - my $strsth = -"SELECT aqorders.booksellerinvoicenumber, datereceived, count(DISTINCT biblionumber) as biblio, sum(quantity) as itemsexpected, sum(quantityreceived) as itemsreceived from aqorders, aqbasket where aqbasket.basketno = aqorders.basketno and aqbasket.booksellerid = $bookseller and datereceived is not null "; - $strsth .= "and aqorders.booksellerinvoicenumber like \"$code%\" " - if ($code); - $strsth .= "and datereceived >=" . $dbh->quote($datefrom) . " " - if ($datefrom); - $strsth .= "and datereceived <=" . $dbh->quote($dateto) . " " if ($dateto); - $strsth .= "group by aqorders.booksellerinvoicenumber,datereceived "; - $strsth .= "order by $order " if ($order); - $strsth .= " LIMIT 0,$limit" if ($limit); - my $sth = $dbh->prepare($strsth); -### getparcels: $strsth - $sth->execute; - my @results; +Returns a contract - while ( my $data2 = $sth->fetchrow_hashref ) { - push @results, $data2; - } +=cut - $sth->finish; - return ( scalar(@results), @results ); -} +sub GetContract { + my ( $contractno ) = @_; + my $dbh = C4::Context->dbh; + my $query = " + SELECT * + FROM aqcontract + WHERE contractnumber=? + "; -END { } # module clean-up code here (global destructor) + 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