From 8a2b3bc0c89e265cd48c5482523ddeb36543d3d5 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Wed, 18 Jan 2012 16:05:40 +0100 Subject: [PATCH] Bug 5336: Order search (search and UI enhancements) You can now search orders by - order status - fund The patch series also adds a new field, aqorders.orderstatus, which can contain following values: new ordered partial (for partially received orders) complete cancelled To test: Search and check if results are consistent in histsearch.pl Signed-off-by: Cedric Vita Signed-off-by: Pierre Angot Signed-off-by: Katrin Fischer Comments on last patch. Note: status are no longer numeric, but strings now. Signed-off-by: Galen Charlton --- C4/Acquisition.pm | 145 +++++++++++++++--- acqui/basket.pl | 18 +-- acqui/histsearch.pl | 22 ++- installer/data/mysql/kohastructure.sql | 1 + installer/data/mysql/updatedatabase.pl | 12 ++ .../prog/en/modules/acqui/histsearch.tt | 101 ++++++++---- 6 files changed, 237 insertions(+), 62 deletions(-) diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm index c93b0a71b0..0244675676 100644 --- a/C4/Acquisition.pm +++ b/C4/Acquisition.pm @@ -28,7 +28,7 @@ use MARC::Record; use C4::Suggestions; use C4::Biblio; use C4::Debug; -use C4::SQLHelper qw(InsertInTable); +use C4::SQLHelper qw(InsertInTable UpdateInTable); use C4::Bookseller qw(GetBookSellerFromId); use C4::Templates qw(gettemplate); @@ -43,7 +43,7 @@ BEGIN { require Exporter; @ISA = qw(Exporter); @EXPORT = qw( - &GetBasket &NewBasket &CloseBasket &DelBasket &ModBasket + &GetBasket &NewBasket &CloseBasket &ReopenBasket &DelBasket &ModBasket &GetBasketAsCSV &GetBasketGroupAsCSV &GetBasketsByBookseller &GetBasketsByBasketgroup &GetBasketsInfosByBookseller @@ -77,6 +77,7 @@ BEGIN { &GetItemnumbersFromOrder &AddClaim + &GetBiblioCountByBasketno ); } @@ -216,7 +217,7 @@ sub NewBasket { &CloseBasket($basketno); -close a basket (becomes unmodifiable,except for recieves) +close a basket (becomes unmodifiable, except for receives) =cut @@ -230,6 +231,48 @@ sub CloseBasket { "; my $sth = $dbh->prepare($query); $sth->execute($basketno); + + my @orders = GetOrders($basketno); + foreach my $order (@orders) { + $query = qq{ + UPDATE aqorders + SET orderstatus = 1 + WHERE ordernumber = ?; + }; + $sth = $dbh->prepare($query); + $sth->execute($order->{'ordernumber'}); + } +} + +=head3 ReopenBasket + + &ReopenBasket($basketno); + +reopen a basket + +=cut + +sub ReopenBasket { + my ($basketno) = @_; + my $dbh = C4::Context->dbh; + my $query = " + UPDATE aqbasket + SET closedate=NULL + WHERE basketno=? + "; + my $sth = $dbh->prepare($query); + $sth->execute($basketno); + + my @orders = GetOrders($basketno); + foreach my $order (@orders) { + $query = qq{ + UPDATE aqorders + SET orderstatus = 0 + WHERE ordernumber = ?; + }; + $sth = $dbh->prepare($query); + $sth->execute($order->{'ordernumber'}); + } } #------------------------------------------------------------# @@ -469,6 +512,7 @@ sub ModBasket { my $dbh = C4::Context->dbh; my $sth = $dbh->prepare($query); $sth->execute(@params); + $sth->finish; } @@ -1032,7 +1076,8 @@ The following keys are used: "biblionumber", "title", "basketno", "quantity", "n sub NewOrder { my $orderinfo = shift; -#### ------------------------------ + my $parent_ordernumber = shift; + my $dbh = C4::Context->dbh; my @params; @@ -1259,7 +1304,8 @@ sub ModReceiveOrder { # (entirely received) $sth=$dbh->prepare(" UPDATE aqorders - SET quantity = ? + SET quantity = ?, + orderstatus = 2 WHERE ordernumber = ? "); @@ -1288,7 +1334,7 @@ sub ModReceiveOrder { } else { $sth=$dbh->prepare("update aqorders set quantityreceived=?,datereceived=?,invoiceid=?, - unitprice=?,rrp=?,ecost=?,budget_id=? + unitprice=?,rrp=?,ecost=?,budget_id=?,orderstatus=3 where biblionumber=? and ordernumber=?"); $sth->execute($quantrec,$datereceived,$invoiceid,$cost,$rrp,$ecost,$budget_id,$biblionumber,$ordernumber); $sth->finish; @@ -1338,7 +1384,8 @@ sub CancelReceipt { UPDATE aqorders SET quantityreceived = ?, datereceived = ?, - invoiceid = ? + invoiceid = ?, + orderstatus = 1 WHERE ordernumber = ? }; $sth = $dbh->prepare($query); @@ -1365,7 +1412,8 @@ sub CancelReceipt { } $query = qq{ UPDATE aqorders - SET quantity = ? + SET quantity = ?, + orderstatus = 1 WHERE ordernumber = ? }; $sth = $dbh->prepare($query); @@ -1545,7 +1593,7 @@ sub DelOrder { my $dbh = C4::Context->dbh; my $query = " UPDATE aqorders - SET datecancellationprinted=now() + SET datecancellationprinted=now(), orderstatus=4 WHERE biblionumber=? AND ordernumber=? "; my $sth = $dbh->prepare($query); @@ -1964,6 +2012,9 @@ sub GetHistory { my $basket = $params{basket}; my $booksellerinvoicenumber = $params{booksellerinvoicenumber}; my $basketgroupname = $params{basketgroupname}; + my $budget = $params{budget}; + my $orderstatus = $params{orderstatus}; + my @order_loop; my $total_qty = 0; my $total_qtyreceived = 0; @@ -1972,10 +2023,10 @@ sub GetHistory { my $dbh = C4::Context->dbh; my $query =" SELECT - biblio.title, - biblio.author, - biblioitems.isbn, - biblioitems.ean, + COALESCE(biblio.title, deletedbiblio.title) AS title, + COALESCE(biblio.author, deletedbiblio.author) AS author, + COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn, + COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean, aqorders.basketno, aqbasket.basketname, aqbasket.basketgroupid, @@ -1990,19 +2041,32 @@ sub GetHistory { aqorders.invoiceid, aqinvoices.invoicenumber, aqbooksellers.id as id, - aqorders.biblionumber + aqorders.biblionumber, + aqorders.orderstatus, + aqorders.parent_ordernumber, + aqbudgets.budget_name + "; + $query .= ", aqbudgets.budget_id AS budget" if defined $budget; + $query .= " 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 biblioitems ON biblioitems.biblionumber=aqorders.biblionumber + LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber - LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid"; + LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id + LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid + LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.biblionumber + LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=aqorders.biblionumber + "; + + if ( C4::Context->preference("IndependentBranches") ) { + $query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber"; + } - $query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber" - if ( C4::Context->preference("IndependentBranches") ); + $query .= " WHERE 1 "; - $query .= " WHERE (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') "; + $query .= " AND (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') " if $orderstatus ne '4'; my @query_params = (); @@ -2021,7 +2085,7 @@ sub GetHistory { $query .= " AND biblioitems.isbn LIKE ? "; push @query_params, "%$isbn%"; } - if ( defined $ean and $ean ) { + if ( $ean ) { $query .= " AND biblioitems.ean = ? "; push @query_params, "$ean"; } @@ -2030,6 +2094,11 @@ sub GetHistory { push @query_params, "%$name%"; } + if ( $budget ) { + $query .= " AND aqbudgets.budget_id = ? "; + push @query_params, "$budget"; + } + if ( $from_placed_on ) { $query .= " AND creationdate >= ? "; push @query_params, $from_placed_on; @@ -2040,6 +2109,11 @@ sub GetHistory { push @query_params, $to_placed_on; } + if ( defined $orderstatus and $orderstatus ne '') { + $query .= " AND aqorders.orderstatus = ? "; + push @query_params, "$orderstatus"; + } + if ($basket) { if ($basket =~ m/^\d+$/) { $query .= " AND aqorders.basketno = ? "; @@ -2075,9 +2149,9 @@ sub GetHistory { $line->{count} = $cnt++; $line->{toggle} = 1 if $cnt % 2; push @order_loop, $line; - $total_qty += $line->{'quantity'}; - $total_qtyreceived += $line->{'quantityreceived'}; - $total_price += $line->{'quantity'} * $line->{'ecost'}; + $total_qty += ( $line->{quantity} ) ? $line->{quantity} : 0; + $total_qtyreceived += ( $line->{quantityreceived} ) ? $line->{quantityreceived} : 0; + $total_price += ( $line->{quantity} and $line->{ecost} ) ? $line->{quantity} * $line->{ecost} : 0; } return \@order_loop, $total_qty, $total_price, $total_qtyreceived; } @@ -2601,6 +2675,31 @@ sub MergeInvoices { return; } +=head3 GetBiblioCountByBasketno + +$biblio_count = &GetBiblioCountByBasketno($basketno); + +Looks up the biblio's count that has basketno value $basketno + +Returns a quantity + +=cut + +sub GetBiblioCountByBasketno { + my ($basketno) = @_; + my $dbh = C4::Context->dbh; + my $query = " + SELECT COUNT( DISTINCT( biblionumber ) ) + FROM aqorders + WHERE basketno = ? + AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00') + "; + + my $sth = $dbh->prepare($query); + $sth->execute($basketno); + return $sth->fetchrow; +} + 1; __END__ diff --git a/acqui/basket.pl b/acqui/basket.pl index 681cd60dd3..5c0e95de3b 100755 --- a/acqui/basket.pl +++ b/acqui/basket.pl @@ -177,18 +177,16 @@ if ( $op eq 'delete_confirm' ) { } exit; } else { - $template->param(confirm_close => "1", - booksellerid => $booksellerid, - basketno => $basket->{'basketno'}, - basketname => $basket->{'basketname'}, - basketgroupname => $basket->{'basketname'}); - + $template->param( + confirm_close => "1", + booksellerid => $booksellerid, + basketno => $basket->{'basketno'}, + basketname => $basket->{'basketname'}, + basketgroupname => $basket->{'basketname'}, + ); } } elsif ($op eq 'reopen') { - my $basket; - $basket->{basketno} = $query->param('basketno'); - $basket->{closedate} = undef; - ModBasket($basket); + ReopenBasket($query->param('basketno')); print $query->redirect('/cgi-bin/koha/acqui/basket.pl?basketno='.$basket->{'basketno'}) } else { # get librarian branch... diff --git a/acqui/histsearch.pl b/acqui/histsearch.pl index 971fc18fa8..e9e40098e3 100755 --- a/acqui/histsearch.pl +++ b/acqui/histsearch.pl @@ -57,6 +57,8 @@ use C4::Output; use C4::Acquisition; use C4::Dates; use C4::Debug; +use C4::Branch; +use C4::Koha; my $input = new CGI; my $title = $input->param( 'title'); @@ -70,6 +72,9 @@ my $booksellerinvoicenumber = $input->param( 'booksellerinvoicenumber' ); my $do_search = $input->param('do_search') || 0; my $from_placed_on = C4::Dates->new($input->param('from')); my $to_placed_on = C4::Dates->new($input->param('to')); +my $budget = $input->param( 'budget' ); +my $orderstatus = $input->param( 'orderstatus' ); + if ( not $input->param('from') ) { # FIXME Dirty but we can't sent a Date::Calc to C4::Dates ? # We would use a function like Add_Delta_YM(-1, 0, 0); @@ -110,21 +115,33 @@ if ($do_search) { basket => $basket, booksellerinvoicenumber => $booksellerinvoicenumber, basketgroupname => $basketgroupname, + budget => $budget, + orderstatus => $orderstatus, ); } my $from_date = $from_placed_on ? $from_placed_on->output('syspref') : undef; my $to_date = $to_placed_on ? $to_placed_on->output('syspref') : undef; +my $budgetperiods = C4::Budgets::GetBudgetPeriods; +my $bp_loop = $budgetperiods; +for my $bp ( @{$budgetperiods} ) { + my $hierarchy = C4::Budgets::GetBudgetHierarchy( $$bp{budget_period_id} ); + for my $budget ( @{$hierarchy} ) { + $$budget{budget_display_name} = sprintf("%s", ">" x $$budget{depth} . $$budget{budget_name}); + } + $$bp{hierarchy} = $hierarchy; +} + $template->param( - suggestions_loop => $order_loop, + order_loop => $order_loop, total_qty => $total_qty, total_qtyreceived => $total_qtyreceived, total_price => sprintf( "%.2f", $total_price ), numresults => $order_loop ? scalar(@$order_loop) : undef, title => $title, author => $author, - isbn => $isbn, + isbn => $isbn, ean => $ean, name => $name, basket => $basket, @@ -132,6 +149,7 @@ $template->param( basketgroupname => $basketgroupname, from_placed_on => $from_date, to_placed_on => $to_date, + bp_loop => $bp_loop, debug => $debug || $input->param('debug') || 0, uc(C4::Context->preference("marcflavour")) => 1 ); diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index e1e3e96611..6cc907b4c3 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2937,6 +2937,7 @@ CREATE TABLE `aqorders` ( -- information related to the basket line items `claimed_date` date default NULL, -- last date a claim was generated `subscriptionid` int(11) default NULL, -- links this order line to a subscription (subscription.subscriptionid) parent_ordernumber int(11) default NULL, -- ordernumber of parent order line, or same as ordernumber if no parent + `orderstatus` tinyint(2) default 0, -- the current status for this line item PRIMARY KEY (`ordernumber`), KEY `basketno` (`basketno`), KEY `biblionumber` (`biblionumber`), diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 9f57eff0bc..c6288c559f 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -7306,6 +7306,18 @@ if ( CheckVersion($DBversion) ) { SetVersion($DBversion); } +$DBversion = "3.13.00.XXX"; +if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { + my $return_count; + $dbh->do("ALTER TABLE aqorders ADD COLUMN orderstatus tinyint(2) DEFAULT 0 AFTER parent_ordernumber"); + $dbh->do("UPDATE aqorders SET orderstatus=1 WHERE basketno IN (SELECT basketno FROM aqbasket WHERE closedate IS NOT NULL)"); + $dbh->do("UPDATE aqorders SET orderstatus=2 WHERE quantity > quantityreceived AND quantityreceived > 0"); + $dbh->do("UPDATE aqorders SET orderstatus=3 WHERE quantity=quantityreceived"); + $dbh->do("UPDATE aqorders SET orderstatus=4 WHERE datecancellationprinted IS NOT NULL"); + print "Upgrade to $DBversion done (Add category ORDRSTATUS values in authorised_values table)\n"; + SetVersion($DBversion); +} + =head1 FUNCTIONS =head2 TableExists($table) diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/histsearch.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/histsearch.tt index c539fe0401..04fb8a3777 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/histsearch.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/histsearch.tt @@ -1,6 +1,6 @@ [% USE KohaDates %] [% INCLUDE 'doc-head-open.inc' %] -Koha › Acquisitions › [% IF ( suggestions_loop ) %]Orders search › Search results[% ELSE %]Order search[% END %] +Koha › Acquisitions › [% IF ( order_loop ) %]Orders search › Search results[% ELSE %]Order search[% END %] [% INCLUDE 'doc-head-close.inc' %] [% INCLUDE 'calendar.inc' %] @@ -22,7 +22,7 @@ [% INCLUDE 'header.inc' %] [% INCLUDE 'acquisitions-search.inc' %] - +
@@ -30,7 +30,7 @@
- [% UNLESS ( suggestions_loop ) %]
+[% UNLESS ( order_loop ) %]
Search Orders
    @@ -47,6 +47,35 @@ + +
  1. + + +
    2. + +
  2. + + +
    3. +
  3. [% INCLUDE 'date-format.inc' %]
    @@ -60,11 +89,12 @@
    4. [% END %] - [% IF ( suggestions_loop ) %]

    Search results

    + [% IF ( order_loop ) %]

    Search results

    + @@ -73,41 +103,58 @@ - + + + + - [% FOREACH suggestions_loo IN suggestions_loop %] - - + [% FOREACH order IN order_loop %] + + + - - - - - + + + + + + - - - - [% END %] + + + + + + [% END %]
    Order line Basket Basket group Invoice numberVendor Placed on Received onQuantity orderedStatusQuantity receivedPending order Unit costFund
    [% suggestions_loo.basketname %] ([% suggestions_loo.basketno %])
    [% order.parent_ordernumber %][% order.basketname %] ([% order.basketno %]) - [% IF ( suggestions_loo.basketgroupid ) %] - [% suggestions_loo.groupname %] ([% suggestions_loo.basketgroupid %]) + [% IF ( order.basketgroupid ) %] + [% order.groupname %] ([% order.basketgroupid %]) [% ELSE %]   [% END %] [% IF suggestions_loo.invoicenumber %] - [% suggestions_loo.invoicenumber %] - [% ELSE %] -   - [% END %] - [% suggestions_loo.ordernumber %][% suggestions_loo.title |html %] -
    [% suggestions_loo.author %]
    [% suggestions_loo.isbn %]    		[% suggestions_loo.name %][% suggestions_loo.creationdate | $KohaDates %][% IF ( order.invoicenumber ) %] + [% order.invoicenumber %] + [% ELSE %] +   + [% END %] + [% order.ordernumber %] + [% order.title |html %] +
    [% order.author %]
    [% order.isbn %] +     		[% order.name %][% order.creationdate | $KohaDates %] + [% IF order.datereceived %] + [% order.datereceived | $KohaDates %] + [% END %] + - [% IF suggestions_loo.datereceived %] - [% suggestions_loo.datereceived | $KohaDates %] + [% SWITCH order.orderstatus %] + [% CASE '0' %]New + [% CASE '1' %]Ordered + [% CASE '2' %]Partial + [% CASE '3' %]Complete + [% CASE '4' %]Deleted [% END %] [% suggestions_loo.quantity %][% suggestions_loo.ecost %]
    [% order.quantityreceived %][% order.quantity %][% order.ecost %][% order.budget_name %]
    [% ELSE %][% END %] -- 2.39.5