X-Git-Url: http://koha-dev.rot13.org:8081/gitweb/?a=blobdiff_plain;f=C4%2FAcquisition.pm;h=f6f58058aac0a3472b8ec9e182c93beea4ef66ae;hb=6fd9b3febeebf95bd0590269bd33739f5cbed7de;hp=2814977575320fbb52525dce12777ab1b2ea73ff;hpb=79b0b7648127180d704a7311c058939c8cb76bf0;p=koha_fer diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm index 2814977575..f6f58058aa 100644 --- a/C4/Acquisition.pm +++ b/C4/Acquisition.pm @@ -29,6 +29,8 @@ use C4::Suggestions; use C4::Biblio; use C4::Debug; use C4::SQLHelper qw(InsertInTable); +use C4::Bookseller qw(GetBookSellerFromId); +use C4::Templates qw(gettemplate); use Time::localtime; use HTML::Entities; @@ -37,13 +39,14 @@ use vars qw($VERSION @ISA @EXPORT); BEGIN { # set the version for version checking - $VERSION = 3.01; + $VERSION = 3.07.00.049; require Exporter; @ISA = qw(Exporter); @EXPORT = qw( &GetBasket &NewBasket &CloseBasket &DelBasket &ModBasket - &GetBasketAsCSV + &GetBasketAsCSV &GetBasketGroupAsCSV &GetBasketsByBookseller &GetBasketsByBasketgroup + &GetBasketsInfosByBookseller &ModBasketHeader @@ -56,12 +59,14 @@ BEGIN { &ModReceiveOrder &ModOrderBiblioitemNumber &GetCancelledOrders - &NewOrderItem &ModOrderItem + &NewOrderItem &ModOrderItem &ModItemOrder &GetParcels &GetParcel &GetContracts &GetContract &GetItemnumbersFromOrder + + &AddClaim ); } @@ -190,7 +195,7 @@ sub NewBasket { $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); + ModBasketHeader($basket, $basketname || '', $basketnote || '', $basketbooksellernote || '', $basketcontractnumber || undef, $booksellerid); return $basket; } @@ -224,54 +229,127 @@ sub CloseBasket { Export a basket as CSV +$cgi parameter is needed for column name translation + =cut sub GetBasketAsCSV { - my ($basketno) = @_; + my ($basketno, $cgi) = @_; 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 entrydate isbn author title publishercode collectiontitle notes quantity rrp); - - $csv->combine(@headers); - $output = $csv->string() . "\n"; + my $template = C4::Templates::gettemplate("acqui/csv/basket.tmpl", "intranet", $cgi); my @rows; foreach my $order (@orders) { - my @cols; - # newlines are not valid characters for Text::CSV combine() - $order->{'notes'} =~ s/[\r\n]+//g; - push(@cols, - $contract->{'contractname'}, - $order->{'ordernumber'}, - $order->{'entrydate'}, - $order->{'isbn'}, - $order->{'author'}, - $order->{'title'}, - $order->{'publishercode'}, - $order->{'collectiontitle'}, - $order->{'notes'}, - $order->{'quantity'}, - $order->{'rrp'}, - ); - push (@rows, \@cols); + my $bd = GetBiblioData( $order->{'biblionumber'} ); + my $row = { + contractname => $contract->{'contractname'}, + ordernumber => $order->{'ordernumber'}, + entrydate => $order->{'entrydate'}, + isbn => $order->{'isbn'}, + author => $bd->{'author'}, + title => $bd->{'title'}, + publicationyear => $bd->{'publicationyear'}, + publishercode => $bd->{'publishercode'}, + collectiontitle => $bd->{'collectiontitle'}, + notes => $order->{'notes'}, + quantity => $order->{'quantity'}, + rrp => $order->{'rrp'}, + deliveryplace => $basket->{'deliveryplace'}, + billingplace => $basket->{'billingplace'} + }; + foreach(qw( + contractname author title publishercode collectiontitle notes + deliveryplace billingplace + ) ) { + # Double the quotes to not be interpreted as a field end + $row->{$_} =~ s/"/""/g if $row->{$_}; + } + push @rows, $row; } - foreach my $row (@rows) { - $csv->combine(@$row); - $output .= $csv->string() . "\n"; + @rows = sort { + if(defined $a->{publishercode} and defined $b->{publishercode}) { + $a->{publishercode} cmp $b->{publishercode}; + } + } @rows; - } - - return $output; + $template->param(rows => \@rows); + return $template->output; } +=head3 GetBasketGroupAsCSV + +=over 4 + +&GetBasketGroupAsCSV($basketgroupid); + +Export a basket group as CSV + +$cgi parameter is needed for column name translation + +=back + +=cut + +sub GetBasketGroupAsCSV { + my ($basketgroupid, $cgi) = @_; + my $baskets = GetBasketsByBasketgroup($basketgroupid); + + my $template = C4::Templates::gettemplate('acqui/csv/basketgroup.tmpl', 'intranet', $cgi); + + my @rows; + for my $basket (@$baskets) { + my @orders = GetOrders( $$basket{basketno} ); + my $contract = GetContract( $$basket{contractnumber} ); + my $bookseller = GetBookSellerFromId( $$basket{booksellerid} ); + + foreach my $order (@orders) { + my $bd = GetBiblioData( $order->{'biblionumber'} ); + my $row = { + clientnumber => $bookseller->{accountnumber}, + basketname => $basket->{basketname}, + ordernumber => $order->{ordernumber}, + author => $bd->{author}, + title => $bd->{title}, + publishercode => $bd->{publishercode}, + publicationyear => $bd->{publicationyear}, + collectiontitle => $bd->{collectiontitle}, + isbn => $order->{isbn}, + quantity => $order->{quantity}, + rrp => $order->{rrp}, + discount => $bookseller->{discount}, + ecost => $order->{ecost}, + notes => $order->{notes}, + entrydate => $order->{entrydate}, + booksellername => $bookseller->{name}, + bookselleraddress => $bookseller->{address1}, + booksellerpostal => $bookseller->{postal}, + contractnumber => $contract->{contractnumber}, + contractname => $contract->{contractname}, + }; + foreach(qw( + basketname author title publishercode collectiontitle notes + booksellername bookselleraddress booksellerpostal contractname + basketgroupdeliveryplace basketgroupbillingplace + basketdeliveryplace basketbillingplace + ) ) { + # Double the quotes to not be interpreted as a field end + $row->{$_} =~ s/"/""/g if $row->{$_}; + } + push @rows, $row; + } + } + $template->param(rows => \@rows); + + return $template->output; + +} + =head3 CloseBasketgroup &CloseBasketgroup($basketgroupno); @@ -382,7 +460,7 @@ sub ModBasket { =head3 ModBasketHeader - &ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber); + &ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid); Modifies a basket's header. @@ -398,16 +476,20 @@ Modifies a basket's header. =item C<$contractnumber> is the "contractnumber" (foreign) key in the "aqbasket" table. +=item C<$booksellerid> is the id (foreign) key in the "aqbooksellers" table for the vendor. + =back =cut sub ModBasketHeader { - my ($basketno, $basketname, $note, $booksellernote, $contractnumber) = @_; - my $query = "UPDATE aqbasket SET basketname=?, note=?, booksellernote=? WHERE basketno=?"; + my ($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid) = @_; + + my $query = "UPDATE aqbasket SET basketname=?, note=?, booksellernote=?, booksellerid=? WHERE basketno=?"; my $dbh = C4::Context->dbh; my $sth = $dbh->prepare($query); - $sth->execute($basketname,$note,$booksellernote,$basketno); + $sth->execute($basketname,$note,$booksellernote,$booksellerid,$basketno); + if ( $contractnumber ) { my $query2 ="UPDATE aqbasket SET contractnumber=? WHERE basketno=?"; my $sth2 = $dbh->prepare($query2); @@ -462,6 +544,44 @@ sub GetBasketsByBookseller { return $results } +=head3 GetBasketsInfosByBookseller + + my $baskets = GetBasketsInfosByBookseller($supplierid); + +Returns in a arrayref of hashref all about booksellers baskets, plus: + total_biblios: Number of distinct biblios in basket + total_items: Number of items in basket + expected_items: Number of non-received items in basket + +=cut + +sub GetBasketsInfosByBookseller { + my ($supplierid) = @_; + + return unless $supplierid; + + my $dbh = C4::Context->dbh; + my $query = qq{ + SELECT aqbasket.*, + SUM(aqorders.quantity) AS total_items, + COUNT(DISTINCT aqorders.biblionumber) AS total_biblios, + SUM( + IF(aqorders.datereceived IS NULL + AND aqorders.datecancellationprinted IS NULL + , aqorders.quantity + , 0) + ) AS expected_items + FROM aqbasket + LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno + WHERE booksellerid = ? + GROUP BY aqbasket.basketno + }; + my $sth = $dbh->prepare($query); + $sth->execute($supplierid); + return $sth->fetchall_arrayref({}); +} + + #------------------------------------------------------------# =head3 GetBasketsByBasketgroup @@ -474,8 +594,11 @@ Returns a reference to all baskets that belong to basketgroup $basketgroupid. sub GetBasketsByBasketgroup { my $basketgroupid = shift; - my $query = "SELECT * FROM aqbasket - LEFT JOIN aqcontract USING(contractnumber) WHERE basketgroupid=?"; + my $query = qq{ + SELECT *, aqbasket.booksellerid as booksellerid + FROM aqbasket + LEFT JOIN aqcontract USING(contractnumber) WHERE basketgroupid=? + }; my $dbh = C4::Context->dbh; my $sth = $dbh->prepare($query); $sth->execute($basketgroupid); @@ -712,8 +835,9 @@ sub GetPendingOrders { my $dbh = C4::Context->dbh; my $strsth = " SELECT ".($grouped?"count(*),":"")."aqbasket.basketno, - surname,firstname,aqorders.*,biblio.*,biblioitems.isbn, - aqbasket.closedate, aqbasket.creationdate, aqbasket.basketname + surname,firstname,biblio.*,biblioitems.isbn, + aqbasket.closedate, aqbasket.creationdate, aqbasket.basketname, + aqorders.* FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber @@ -924,7 +1048,6 @@ sub NewOrder { =cut sub NewOrderItem { - #my ($biblioitemnumber,$ordernumber, $biblionumber) = @_; my ($itemnumber, $ordernumber) = @_; my $dbh = C4::Context->dbh; my $query = qq| @@ -1017,6 +1140,29 @@ sub ModOrderItem { return 0; } +=head3 ModItemOrder + + ModItemOrder($itemnumber, $ordernumber); + +Modifies the ordernumber of an item in aqorders_items. + +=cut + +sub ModItemOrder { + my ($itemnumber, $ordernumber) = @_; + + return unless ($itemnumber and $ordernumber); + + my $dbh = C4::Context->dbh; + my $query = qq{ + UPDATE aqorders_items + SET ordernumber = ? + WHERE itemnumber = ? + }; + my $sth = $dbh->prepare($query); + return $sth->execute($ordernumber, $itemnumber); +} + #------------------------------------------------------------# @@ -1102,20 +1248,17 @@ C<$ordernumber>. sub ModReceiveOrder { my ( $biblionumber, $ordernumber, $quantrec, $user, $cost, - $invoiceno, $freight, $rrp, $budget_id, $datereceived + $invoiceno, $freight, $rrp, $budget_id, $datereceived, $received_items ) = @_; my $dbh = C4::Context->dbh; -# warn "DATE BEFORE : $daterecieved"; -# $daterecieved=POSIX::strftime("%Y-%m-%d",CORE::localtime) unless $daterecieved; -# warn "DATE REC : $daterecieved"; $datereceived = C4::Dates->output('iso') unless $datereceived; - my $suggestionid = GetSuggestionFromBiblionumber( $dbh, $biblionumber ); + my $suggestionid = GetSuggestionFromBiblionumber( $biblionumber ); if ($suggestionid) { ModSuggestion( {suggestionid=>$suggestionid, - STATUS=>'AVAILABLE', - biblionumber=> $biblionumber} - ); + STATUS=>'AVAILABLE', + biblionumber=> $biblionumber} + ); } my $sth=$dbh->prepare(" @@ -1148,7 +1291,19 @@ sub ModReceiveOrder { $order->{'quantity'} -= $quantrec; $order->{'quantityreceived'} = 0; my $newOrder = NewOrder($order); -} else { + # Change ordernumber in aqorders_items for items not received + my @orderitems = GetItemnumbersFromOrder( $order->{'ordernumber'} ); + my $count = scalar @orderitems; + + for (my $i=0; $i<$count; $i++){ + foreach (@$received_items){ + splice (@orderitems, $i, 1) if ($orderitems[$i] == $_); + } + } + foreach (@orderitems) { + ModItemOrder($_, $newOrder); + } + } else { $sth=$dbh->prepare("update aqorders set quantityreceived=?,datereceived=?,booksellerinvoicenumber=?, unitprice=?,freight=?,rrp=? @@ -1196,7 +1351,7 @@ C<@results> is an array of references-to-hash with the following keys: sub SearchOrder { #### -------- SearchOrder------------------------------- - my ($ordernumber, $search, $supplierid, $basket) = @_; + my ( $ordernumber, $search, $ean, $supplierid, $basket ) = @_; my $dbh = C4::Context->dbh; my @args = (); @@ -1216,7 +1371,11 @@ sub SearchOrder { $query .= " AND (biblio.title like ? OR biblio.author LIKE ? OR biblioitems.isbn like ?)"; push @args, ("%$search%","%$search%","%$search%"); } - if($supplierid){ + if ($ean) { + $query .= " AND biblioitems.ean = ?"; + push @args, $ean; + } + if ($supplierid) { $query .= "AND aqbasket.booksellerid = ?"; push @args, $supplierid; } @@ -1440,25 +1599,31 @@ sub GetLateOrders { my $delay = shift; my $supplierid = shift; my $branch = shift; + my $estimateddeliverydatefrom = shift; + my $estimateddeliverydateto = shift; my $dbh = C4::Context->dbh; #BEWARE, order of parenthesis and LEFT JOIN is important for speed my $dbdriver = C4::Context->config("db_scheme") || "mysql"; - my @query_params = ($delay); # delay is the first argument regardless + my @query_params = (); my $select = " SELECT aqbasket.basketno, aqorders.ordernumber, DATE(aqbasket.closedate) AS orderdate, aqorders.rrp AS unitpricesupplier, aqorders.ecost AS unitpricelib, + aqorders.claims_count AS claims_count, + aqorders.claimed_date AS claimed_date, aqbudgets.budget_name AS budget, borrowers.branchcode AS branch, aqbooksellers.name AS supplier, + aqbooksellers.id AS supplierid, biblio.author, biblio.title, biblioitems.publishercode AS publisher, biblioitems.publicationyear, + ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate, "; my $from = " FROM @@ -1472,6 +1637,7 @@ sub GetLateOrders { OR datereceived IS NULL OR aqorders.quantityreceived < aqorders.quantity ) + AND aqbasket.closedate IS NOT NULL AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00') "; my $having = ""; @@ -1479,9 +1645,12 @@ sub GetLateOrders { $select .= " aqorders.quantity - IFNULL(aqorders.quantityreceived,0) AS quantity, (aqorders.quantity - IFNULL(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal, - DATEDIFF(CURDATE( ),closedate) AS latesince + DATEDIFF(CAST(now() AS date),closedate) AS latesince "; - $from .= " AND (closedate <= DATE_SUB(CURDATE( ),INTERVAL ? DAY)) "; + if ( defined $delay ) { + $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ; + push @query_params, $delay; + } $having = " HAVING quantity <> 0 AND unitpricesupplier <> 0 @@ -1492,9 +1661,12 @@ sub GetLateOrders { $select .= " aqorders.quantity AS quantity, aqorders.quantity * aqorders.rrp AS subtotal, - (CURDATE - closedate) AS latesince + (CAST(now() AS date) - closedate) AS latesince "; - $from .= " AND (closedate <= (CURDATE -(INTERVAL ? DAY)) "; + if ( defined $delay ) { + $from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) "; + push @query_params, $delay; + } } if (defined $supplierid) { $from .= ' AND aqbasket.booksellerid = ? '; @@ -1504,6 +1676,18 @@ sub GetLateOrders { $from .= ' AND borrowers.branchcode LIKE ? '; push @query_params, $branch; } + if ( defined $estimateddeliverydatefrom ) { + $from .= ' + AND aqbooksellers.deliverytime IS NOT NULL + AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?'; + push @query_params, $estimateddeliverydatefrom; + } + if ( defined $estimateddeliverydatefrom and defined $estimateddeliverydateto ) { + $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?'; + push @query_params, $estimateddeliverydateto; + } elsif ( defined $estimateddeliverydatefrom ) { + $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)'; + } if (C4::Context->preference("IndependantBranches") && C4::Context->userenv && C4::Context->userenv->{flags} != 1 ) { @@ -1517,6 +1701,7 @@ sub GetLateOrders { my @results; while (my $data = $sth->fetchrow_hashref) { $data->{orderdate} = format_date($data->{orderdate}); + $data->{claimed_date} = format_date($data->{claimed_date}); push @results, $data; } return @results; @@ -1570,12 +1755,12 @@ sub GetHistory { my $title = $params{title}; my $author = $params{author}; my $isbn = $params{isbn}; + my $ean = $params{ean}; my $name = $params{name}; my $from_placed_on = $params{from_placed_on}; my $to_placed_on = $params{to_placed_on}; my $basket = $params{basket}; my $booksellerinvoicenumber = $params{booksellerinvoicenumber}; - my @order_loop; my $total_qty = 0; my $total_qtyreceived = 0; @@ -1587,12 +1772,13 @@ sub GetHistory { biblio.title, biblio.author, biblioitems.isbn, + biblioitems.ean, aqorders.basketno, - aqbasket.basketname, - aqbasket.basketgroupid, - aqbasketgroups.name as groupname, + aqbasket.basketname, + aqbasket.basketgroupid, + aqbasketgroups.name as groupname, aqbooksellers.name, - aqbasket.creationdate, + aqbasket.creationdate, aqorders.datereceived, aqorders.quantity, aqorders.quantityreceived, @@ -1603,7 +1789,7 @@ sub GetHistory { aqorders.biblionumber FROM aqorders LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno - LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id + LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber"; @@ -1630,7 +1816,10 @@ sub GetHistory { $query .= " AND biblioitems.isbn LIKE ? "; push @query_params, "%$isbn%"; } - + if ( defined $ean and $ean ) { + $query .= " AND biblioitems.ean = ? "; + push @query_params, "$ean"; + } if ( $name ) { $query .= " AND aqbooksellers.name LIKE ? "; push @query_params, "%$name%"; @@ -1676,8 +1865,6 @@ sub GetHistory { $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'}; @@ -1779,6 +1966,31 @@ sub GetContract { return $result; } +=head3 AddClaim + +=over 4 + +&AddClaim($ordernumber); + +Add a claim for an order + +=back + +=cut +sub AddClaim { + my ($ordernumber) = @_; + my $dbh = C4::Context->dbh; + my $query = " + UPDATE aqorders SET + claims_count = claims_count + 1, + claimed_date = CURDATE() + WHERE ordernumber = ? + "; + my $sth = $dbh->prepare($query); + $sth->execute($ordernumber); + +} + 1; __END__