From 7f41e027adaed6bbd1623453a5f3959da76e43d6 Mon Sep 17 00:00:00 2001 From: Paul Poulain Date: Tue, 28 Apr 2009 22:33:15 +0200 Subject: [PATCH] Acquisitions.pm lot of changes everywhere --- C4/Acquisition.pm | 1023 ++++++++++++++++++++++++++++++++------------- 1 file changed, 732 insertions(+), 291 deletions(-) diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm index d8a6c95e6e..ca98ec4f34 100644 --- a/C4/Acquisition.pm +++ b/C4/Acquisition.pm @@ -21,10 +21,13 @@ package C4::Acquisition; use strict; use C4::Context; use C4::Debug; -use C4::Dates qw(format_date); +use C4::Dates qw(format_date format_date_in_iso); use MARC::Record; use C4::Suggestions; +use C4::Debug; + use Time::localtime; +use HTML::Entities; use vars qw($VERSION @ISA @EXPORT); @@ -34,18 +37,54 @@ BEGIN { require Exporter; @ISA = qw(Exporter); @EXPORT = qw( - &GetBasket &NewBasket &CloseBasket + &GetBasket &NewBasket &CloseBasket &DelBasket &ModBasket + &ModBasketHeader &GetBasketsByBookseller &GetBasketsByBasketgroup + &ModBasketgroup &NewBasketgroup &DelBasketgroup &GetBasketgroup + &GetBasketgroups + &GetPendingOrders &GetOrder &GetOrders &GetOrderNumber &GetLateOrders &NewOrder &DelOrder &SearchOrder &GetHistory &GetRecentAcqui - &ModOrder &ModReceiveOrder &ModOrderBiblioNumber + &ModOrder &ModReceiveOrder &ModOrderBiblioitemNumber + + &NewOrderItem + &GetParcels &GetParcel + &GetContracts &GetContract + + &GetOrderFromItemnumber ); } -# used in receiveorder subroutine -# to provide library specific handling -my $library_name = C4::Context->preference("LibraryName"); + + + + +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 ); + +} + + + + + + =head1 NAME @@ -102,10 +141,16 @@ sub GetBasket { =over 4 -$basket = &NewBasket(); +$basket = &NewBasket( $booksellerid, $authorizedby, $basketname, $basketnote, $basketbooksellernote, $basketcontractnumber ); Create a new basket in aqbasket table +=item C<$booksellerid> is a foreign key in the aqbasket table + +=item C<$authorizedby> is the username of who created the basket + +The other parameters are optional, see ModBasketHeader for more info on them. + =back =cut @@ -113,7 +158,7 @@ Create a new basket in aqbasket table # FIXME : this function seems to be unused. sub NewBasket { - my ( $booksellerid, $authorisedby ) = @_; + my ( $booksellerid, $authorisedby, $basketname, $basketnote, $basketbooksellernote, $basketcontractnumber ) = @_; my $dbh = C4::Context->dbh; my $query = " INSERT INTO aqbasket @@ -122,9 +167,9 @@ sub NewBasket { "; 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; } @@ -156,8 +201,414 @@ sub CloseBasket { #------------------------------------------------------------# +=head3 DelBasket + +=over 4 + +&DelBasket($basketno); + +Deletes the basket that has basketno field $basketno in the aqbasket table. + +=over 2 + +=item C<$basketno> is the primary key of the basket in the aqbasket table. + +=back + +=back + +=cut +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; +} + +#------------------------------------------------------------# + +=head3 ModBasket + +=over 4 + +&ModBasket($basketinfo); + +Modifies a basket, using a hashref $basketinfo for the relevant information, only $basketinfo->{'basketno'} is required. + +=over 2 + +=item C<$basketno> is the primary key of the basket in the aqbasket table. + +=back + +=back + +=cut +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; +} + +#------------------------------------------------------------# + +=head3 ModBasketHeader + +=over 4 + +&ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber); + +Modifies a basket's header. + +=over 2 + +=item C<$basketno> is the "basketno" field in the "aqbasket" table; + +=item C<$basketname> is the "basketname" field in the "aqbasket" table; + +=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 + +=back + +=cut +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; +} + +#------------------------------------------------------------# + +=head3 GetBasketsByBookseller + +=over 4 + +@results = &GetBasketsByBookseller($booksellerid, $extra); + +Returns a list of hashes of all the baskets that belong to bookseller 'booksellerid'. + +=over 2 + +=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 + +=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 + +=over 4 + +$baskets = &GetBasketsByBasketgroup($basketgroupid); + +=over 2 + +Returns a reference to all baskets that belong to basketgroup $basketgroupid. + +=back + +=back + +=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 + +=over 4 + +$basketgroupid = NewBasketgroup(\%hashref); + +=over 2 + +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->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise. + +=back + +=back + +=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', '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 + +=over 4 + +ModBasketgroup(\%hashref); + +=over 2 + +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->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise. + +=back + +=back + +=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 closed)) { + if ( $basketgroupinfo->{$field} ne undef) { + $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); + if($basketgroupinfo->{'basketlist'} && @{$basketgroupinfo->{'basketlist'}}){ + foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) { + my $query2 = "UPDATE aqbasket SET basketgroupid=? WHERE basketno=?"; + my $sth2 = $dbh->prepare($query2); + $sth2->execute($basketgroupinfo->{'id'}, $basketno); + $sth2->finish; + } + } + $sth->finish; +} + +#------------------------------------------------------------# + +=head3 DelBasketgroup + +=over 4 + +DelBasketgroup($basketgroupid); + +=over 2 + +Deletes a basketgroup in the aqbasketgroups table, and removes the reference to it from the baskets, + +=item C<$basketgroupid> is the 'id' field of the basket in the aqbasketgroup table + +=back + +=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; +} + +#------------------------------------------------------------# + +=back + =head2 FUNCTIONS ABOUT ORDERS +=over 2 + +=cut + +=head3 GetBasketgroup + +=over 4 + +$basketgroup = &GetBasketgroup($basketgroupid); + +=over 2 + +Returns a reference to the hash containing all infermation about the basketgroup. + +=back + +=back + +=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 + +=over 4 + +$basketgroups = &GetBasketgroups($booksellerid); + +=over 2 + +Returns a reference to the array of all the basketgroups of bookseller $booksellerid. + +=back + +=back + +=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 +} + +#------------------------------------------------------------# + +=back + +=head2 FUNCTIONS ABOUT ORDERS + +=over 2 + =cut #------------------------------------------------------------# @@ -166,15 +617,19 @@ sub CloseBasket { =over 4 -$orders = &GetPendingOrders($booksellerid, $grouped); +$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 +in a single result line =over 2 @@ -196,15 +651,16 @@ Results are ordered from most to least recent. =cut sub GetPendingOrders { - my ($supplierid,$grouped) = @_; + my ($supplierid,$grouped,$owner) = @_; my $dbh = C4::Context->dbh; my $strsth = " SELECT ".($grouped?"count(*),":"")."aqbasket.basketno, - surname,firstname,aqorders.*, - aqbasket.closedate, aqbasket.creationdate + surname,firstname,aqorders.*,biblio.*, + 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 WHERE booksellerid=? AND (quantity > quantityreceived OR quantityreceived is NULL) AND datecancellationprinted IS NULL @@ -212,14 +668,18 @@ sub GetPendingOrders { "; ## 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 = ? or borrowers.branchcode = '')"; push @query_params, $userenv->{branch}; } } + if ($owner) { + $strsth .= " AND aqbasket.authorisedby=? "; + push @query_params, $userenv->{'number'}; + } $strsth .= " group by aqbasket.basketno" if $grouped; $strsth .= " order by aqbasket.basketno"; @@ -255,16 +715,14 @@ sub GetOrders { my ( $basketno, $orderby ) = @_; my $dbh = C4::Context->dbh; my $query =" - SELECT aqorderbreakdown.*, - biblio.*,biblioitems.*, + SELECT biblio.*,biblioitems.*, aqorders.*, - aqbookfund.bookfundname, + aqbudgets.*, biblio.title FROM aqorders - LEFT JOIN aqorderbreakdown ON aqorders.ordernumber=aqorderbreakdown.ordernumber - LEFT JOIN aqbookfund ON aqbookfund.bookfundid=aqorderbreakdown.bookfundid - LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber - LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber + 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') "; @@ -273,13 +731,9 @@ sub GetOrders { $query .= " ORDER BY $orderby"; my $sth = $dbh->prepare($query); $sth->execute($basketno); - my @results; - - while ( my $data = $sth->fetchrow_hashref ) { - push @results, $data; - } + my $results = $sth->fetchall_arrayref({}); $sth->finish; - return @results; + return @$results; } #------------------------------------------------------------# @@ -329,8 +783,7 @@ $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. =back @@ -340,9 +793,8 @@ sub GetOrder { my ($ordnum) = @_; my $dbh = C4::Context->dbh; my $query = " - SELECT biblioitems.*, biblio.*, aqorderbreakdown.*, aqorders.* + SELECT biblioitems.*, biblio.*, aqorders.* FROM aqorders - LEFT JOIN aqorderbreakdown ON aqorders.ordernumber=aqorderbreakdown.ordernumber LEFT JOIN biblio on biblio.biblionumber=aqorders.biblionumber LEFT JOIN biblioitems on biblioitems.biblionumber=aqorders.biblionumber WHERE aqorders.ordernumber=? @@ -361,101 +813,104 @@ sub GetOrder { =over 4 - &NewOrder($basket, $biblionumber, $title, $quantity, $listprice, - $booksellerid, $who, $notes, $bookfund, $biblioitemnumber, $rrp, - $ecost, $gst, $budget, $unitprice, $subscription, - $booksellerinvoicenumber, $purchaseorder, $branchcode); +&NewOrder(\%hashref); 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. +=over 4 + +=item $hashref->{'basketno'} is the basketno foreign key in aqorders, it is mandatory + -C<$budget> is effectively ignored. +=item $hashref->{'ordnum'} 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. -C<$subscription> may be either "yes", or anything else for "no". +=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" + +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 =back =cut sub NewOrder { - my ( - $basketno, $bibnum, $title, $quantity, - $listprice, $booksellerid, $authorisedby, $notes, - $bookfund, $bibitemnum, $rrp, $ecost, - $gst, $budget, $cost, $sub, - $invoice, $sort1, $sort2, $purchaseorder, - $branchcode - ) - = @_; - - my $year = localtime->year() + 1900; - my $month = localtime->mon() + 1; # months starts at 0, add 1 + my $orderinfo = shift; +#### ------------------------------ + my $dbh = C4::Context->dbh; + my @params; - if ( !$budget || $budget eq 'now' ) { - $budget = undef; - } - # 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"; + # 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}; } - else { - # START OF NEW BUDGET, 1ST OF JULY, THIS YEAR - $budget = "$year-07-01"; + if ( $orderinfo->{'subscription'} eq 'yes' ) { + $orderinfo->{'subscription'} = 1; + } else { + $orderinfo->{'subscription'} = 0; } - if ( $sub eq 'yes' ) { - $sub = 1; - } - else { - $sub = 0; + my $query = "INSERT INTO aqorders ("; + foreach my $orderinfokey (keys %{$orderinfo}) { + next if $orderinfokey =~ m/branchcode|entrydate/; # skip branchcode and entrydate, branchcode isnt a vaild col, entrydate we add manually with NOW() + $query .= "$orderinfokey,"; + push(@params, $orderinfo->{$orderinfokey}); } - # if $basket empty, it's also a new basket, create it - unless ($basketno) { - $basketno = NewBasket( $booksellerid, $authorisedby ); + $query .= "entrydate) VALUES ("; + foreach (@params) { + $query .= "?,"; } + $query .= " NOW() )"; #ADDING CURRENT DATE TO 'budgetdate, entrydate, purchaseordernumber'... - my $dbh = C4::Context->dbh; - my $query = " - INSERT INTO aqorders - ( biblionumber, title, basketno, quantity, listprice, - notes, biblioitemnumber, rrp, ecost, gst, - unitprice, subscription, sort1, sort2, budgetdate, - entrydate, purchaseordernumber) - VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,COALESCE(?,NOW()),NOW(),? ) - "; my $sth = $dbh->prepare($query); - $sth->execute( - $bibnum, $title, $basketno, $quantity, $listprice, - $notes, $bibitemnum, $rrp, $ecost, $gst, - $cost, $sub, $sort1, $sort2, $budget, - $purchaseorder - ); + $sth->execute(@params); $sth->finish; #get ordnum MYSQL dependant, but $dbh->last_insert_id returns null my $ordnum = $dbh->{'mysql_insertid'}; - $query = " - INSERT INTO aqorderbreakdown (ordernumber,bookfundid, branchcode) - VALUES (?,?,?) - "; - $sth = $dbh->prepare($query); - $sth->execute( $ordnum, $bookfund, $branchcode ); + $sth->finish; - return ( $basketno, $ordnum ); + return ( $orderinfo->{'basketno'}, $ordnum ); +} + + + +#------------------------------------------------------------# + +=head3 NewOrderItem + +=over 4 + +&NewOrderItem(); + + +=back + +=cut + +sub NewOrderItem { + #my ($biblioitemnumber,$ordnum, $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); } #------------------------------------------------------------# @@ -464,76 +919,58 @@ sub NewOrder { =over 4 -&ModOrder($title, $ordernumber, $quantity, $listprice, - $biblionumber, $basketno, $supplier, $who, $notes, - $bookfundid, $bibitemnum, $rrp, $ecost, $gst, $budget, - $unitprice, $booksellerinvoicenumber, $branchcode); +&ModOrder(\%hashref); + +=over 2 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. +$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. -Entries with order number C<$ordernumber> in the aqorderbreakdown -table are also updated to the new book fund ID or branchcode. +=back =back =cut sub ModOrder { - my ( - $title, $ordnum, $quantity, $listprice, $bibnum, - $basketno, $supplier, $who, $notes, $bookfund, - $bibitemnum, $rrp, $ecost, $gst, $budget, - $cost, $invoice, $sort1, $sort2, $purchaseorder, $branchcode - ) - = @_; - # FIXME : Refactor to pass a hashref instead of fifty params. - my $dbh = C4::Context->dbh; - my $query = " - UPDATE aqorders - SET title=?, - quantity=?,listprice=?,basketno=?, - rrp=?,ecost=?,unitprice=?,booksellerinvoicenumber=?, - notes=?,sort1=?, sort2=?, purchaseordernumber=? - WHERE ordernumber=? AND biblionumber=? - "; - my $sth = $dbh->prepare($query); - $sth->execute( - $title, $quantity, $listprice, $basketno, $rrp, - $ecost, $cost, $invoice, $notes, $sort1, - $sort2, $purchaseorder, - $ordnum, $bibnum - ); - $sth->finish; - $query = " - UPDATE aqorderbreakdown - SET bookfundid=?,branchcode=? - WHERE ordernumber=? - "; - $sth = $dbh->prepare($query); + my $orderinfo = shift; - my $rv = $sth->execute( $bookfund,$branchcode, $ordnum ); - unless($rv && ( $rv ne '0E0' )) { # zero rows affected [Bug 734] - my $query =" - INSERT INTO aqorderbreakdown - (ordernumber,branchcode,bookfundid) - VALUES (?,?,?) - "; - $sth = $dbh->prepare($query); - $sth->execute( $ordnum,$branchcode, $bookfund ); + 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 ModOrderBiblioNumber +=head3 ModOrderBibliotemNumber =over 4 -&ModOrderBiblioNumber($biblioitemnumber,$ordnum, $biblionumber); +&ModOrderBiblioitemNumber($biblioitemnumber,$ordnum, $biblionumber); Modifies the biblioitemnumber for an existing order. Updates the order with order number C<$ordernum> and biblionumber C<$biblionumber>. @@ -542,7 +979,8 @@ Updates the order with order number C<$ordernum> and biblionumber C<$biblionumbe =cut -sub ModOrderBiblioNumber { +#FIXME: is this used at all? +sub ModOrderBiblioitemNumber { my ($biblioitemnumber,$ordnum, $biblionumber) = @_; my $dbh = C4::Context->dbh; my $query = " @@ -569,13 +1007,11 @@ 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. +portion must have a booksellerinvoicenumber. Updates the order with bibilionumber C<$biblionumber> and ordernumber C<$ordernumber>. -Also updates the book fund ID in the aqorderbreakdown table. - =back =cut @@ -584,7 +1020,7 @@ Also updates the book fund ID in the aqorderbreakdown table. sub ModReceiveOrder { my ( $biblionumber, $ordnum, $quantrec, $user, $cost, - $invoiceno, $freight, $rrp, $bookfund, $datereceived + $invoiceno, $freight, $rrp, $budget_id, $datereceived ) = @_; my $dbh = C4::Context->dbh; @@ -596,40 +1032,38 @@ sub ModReceiveOrder { if ($suggestionid) { ModStatus( $suggestionid, 'AVAILABLE', '', $biblionumber ); } - # Allows libraries to change their bookfund during receiving orders - # allows them to adjust budgets - if ( C4::Context->preference("LooseBudgets") && $bookfund ) { - my $query = " - UPDATE aqorderbreakdown - SET bookfundid=? - WHERE ordernumber=? - "; - my $sth = $dbh->prepare($query); - $sth->execute( $bookfund, $ordnum ); - $sth->finish; - } - - my $sth=$dbh->prepare("SELECT * FROM aqorders LEFT JOIN aqorderbreakdown ON aqorders.ordernumber=aqorderbreakdown.ordernumber - WHERE biblionumber=? AND aqorders.ordernumber=?"); + + my $sth=$dbh->prepare(" + SELECT * FROM aqorders + WHERE biblionumber=? AND aqorders.ordernumber=?"); + $sth->execute($biblionumber,$ordnum); 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=$dbh->prepare(" + UPDATE aqorders + SET quantityreceived=? + , datereceived=? + , booksellerinvoicenumber=? + , unitprice=? + , freight=? + , rrp=? + , quantityreceived=? + WHERE biblionumber=? AND ordernumber=?"); + $sth->execute($quantrec,$datereceived,$invoiceno,$cost,$freight,$rrp,$quantrec,$biblionumber,$ordnum); $sth->finish; + # create a new order for the remaining items, and set its bookfund. - my $newOrder = NewOrder($order->{'basketno'},$order->{'biblionumber'},$order->{'title'}, $order->{'quantity'} - $quantrec, - $order->{'listprice'},$order->{'booksellerid'},$order->{'authorisedby'},$order->{'notes'}, - $order->{'bookfundid'},$order->{'biblioitemnumber'},$order->{'rrp'},$order->{'ecost'},$order->{'gst'}, - $order->{'budget'},$order->{'unitcost'},$order->{'sub'},'',$order->{'sort1'},$order->{'sort2'},$order->{'purchaseordernumber'}); + foreach my $orderkey ( "linenumber", "allocation" ) { + delete($order->{'$orderkey'}); + } + my $newOrder = NewOrder($order); } else { - $sth=$dbh->prepare("update aqorders - set quantityreceived=?,datereceived=?,booksellerinvoicenumber=?, + $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,$ordnum); @@ -674,102 +1108,40 @@ C<@results> is an array of references-to-hash with the following keys: =cut sub SearchOrder { - my ( $search, $id, $biblionumber, $catview ) = @_; - my $dbh = C4::Context->dbh; - my @data = split( ' ', $search ); - my @searchterms; - if ($id) { - @searchterms = ($id); - } - map { push( @searchterms, "$_%", "%$_%" ) } @data; - push( @searchterms, $search, $search, $biblionumber ); - my $query; - ### FIXME THIS CAN raise a problem if more THAN ONE biblioitem is linked to one biblio - if($id and $search){ - @searchterms = ($id, $search); - $query = - "SELECT *,biblio.title - 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 aqbasket.booksellerid = ? AND aqorders.ordernumber = ? - " - }elsif ($id) { - $query = - "SELECT *,biblio.title - 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 aqbasket.booksellerid = ? - 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=?)) "; - - } - else { - $query = - " SELECT *,biblio.title - FROM aqorders - LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber - LEFT JOIN aqbasket on aqorders.basketno=aqbasket.basketno - LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber - WHERE ((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=?)) "; - } - - if ( $biblionumber ) { - $query .= "AND biblio.biblionumber = ? "; - push (@searchterms, $biblionumber); - } - - $query .= " GROUP BY aqorders.ordernumber"; - ### $query - my $sth = $dbh->prepare($query); - $sth->execute(@searchterms); - my @results = (); - my $query2 = " - SELECT * - FROM biblio - WHERE biblionumber=? - "; - my $sth2 = $dbh->prepare($query2); - my $query3 = " - SELECT * - FROM aqorderbreakdown - WHERE ordernumber=? - "; - my $sth3 = $dbh->prepare($query3); - - 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 ); +#### -------- SearchOrder------------------------------- + my ($ordernumber, $search) = @_; + + if ($ordernumber) { + my $dbh = C4::Context->dbh; + 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) + AND (aqorders.ordernumber=?))"; + my $sth = $dbh->prepare($query); + $sth->execute($ordernumber); + my $results = $sth->fetchall_arrayref({}); + $sth->finish; + return $results; + } else { + my $dbh = C4::Context->dbh; + 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) + AND (biblio.title like ? OR biblioitems.isbn like ?))"; + my $sth = $dbh->prepare($query); + $sth->execute("%$search%","%$search%"); + my $results = $sth->fetchall_arrayref({}); + $sth->finish; + return $results; } - ### @results - $sth->finish; - $sth2->finish; - $sth3->finish; - return @results; } #------------------------------------------------------------# @@ -839,18 +1211,19 @@ sub GetParcel { closedate,surname, firstname, aqorders.biblionumber, - aqorders.title, aqorders.ordernumber, aqorders.quantity, aqorders.quantityreceived, aqorders.unitprice, aqorders.listprice, aqorders.rrp, - aqorders.ecost - FROM aqorders + aqorders.ecost, + biblio.title + FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber - WHERE + LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber + WHERE aqbasket.booksellerid = ? AND aqorders.booksellerinvoicenumber LIKE ? AND aqorders.datereceived = ? "; @@ -865,13 +1238,13 @@ sub GetParcel { } } $strsth .= " ORDER BY aqbasket.basketno"; - ### parcelinformation : $strsth + # ## parcelinformation : $strsth my $sth = $dbh->prepare($strsth); $sth->execute( @query_params ); while ( my $data = $sth->fetchrow_hashref ) { push( @results, $data ); } - ### countparcelbiblio: scalar(@results) + # ## countparcelbiblio: scalar(@results) $sth->finish; return @results; @@ -938,7 +1311,7 @@ sub GetParcels { # 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; @@ -997,10 +1370,9 @@ sub GetLateOrders { DATE(aqbasket.closedate) AS orderdate, aqorders.rrp AS unitpricesupplier, aqorders.ecost AS unitpricelib, - aqbookfund.bookfundname AS budget, + aqbudgets.budget_name AS budget, borrowers.branchcode AS branch, aqbooksellers.name AS supplier, - aqorders.title, biblio.author, biblioitems.publishercode AS publisher, biblioitems.publicationyear, @@ -1009,8 +1381,7 @@ sub GetLateOrders { 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), + 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 @@ -1126,8 +1497,8 @@ sub GetHistory { aqorders.booksellerinvoicenumber as invoicenumber, aqbooksellers.id as id, aqorders.biblionumber - FROM aqorders - LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno + FROM aqorders + LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber"; @@ -1135,9 +1506,9 @@ sub GetHistory { 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%"; @@ -1151,7 +1522,7 @@ sub GetHistory { if ( defined $name ) { $query .= " AND name LIKE ? "; push @query_params, "%$name%"; - } + } if ( defined $from_placed_on ) { $query .= " AND creationdate >= ? "; @@ -1207,11 +1578,81 @@ sub GetRecentAcqui { my $sth = $dbh->prepare($query); $sth->execute; + my $results = $sth->fetchall_arrayref({}); + return $results; +} + +=head3 GetContracts + +=over 4 + +$contractlist = &GetContracts($booksellerid, $activeonly); + +Looks up the contracts that belong to a bookseller + +Returns a list of contracts + +=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 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; + while (my $data = $sth->fetchrow_hashref ) { + push(@results, $data); } - return \@results; + $sth->finish; + return @results; +} + +#------------------------------------------------------------# + +=head3 GetContract + +=over 4 + +$contract = &GetContract($contractID); + +Looks up the contract that has PRIMKEY (contractnumber) value $contractID + +Returns a contract + +=back + +=cut +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; -- 2.20.1