From de19a598f9f9d313248681ea2ecbbd70fd7045c5 Mon Sep 17 00:00:00 2001 From: Jonathan Druart <jonathan.druart@biblibre.com> 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 New field aqorders.orderstatus, it can contains following values: 0 => New 1 => Ordered 2 => Partial 3 => Complete 4 => Deleted To test: Search and check if results are consistent in histsearch.pl --- C4/Acquisition.pm | 145 ++++++++++++++++---- acqui/basket.pl | 18 ++- acqui/histsearch.pl | 22 ++- installer/data/mysql/kohastructure.sql | 1 + installer/data/mysql/updatedatabase.pl | 16 +++ .../prog/en/modules/acqui/histsearch.tt | 100 ++++++++++---- 6 files changed, 240 insertions(+), 62 deletions(-) diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm index c766753..c334afd 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 @@ -75,6 +75,7 @@ BEGIN { &GetItemnumbersFromOrder &AddClaim + &GetBiblioCountByBasketno ); } @@ -214,7 +215,7 @@ sub NewBasket { &CloseBasket($basketno); -close a basket (becomes unmodifiable,except for recieves) +close a basket (becomes unmodifiable, except for receives) =cut @@ -228,6 +229,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'}); + } } #------------------------------------------------------------# @@ -467,6 +510,7 @@ sub ModBasket { my $dbh = C4::Context->dbh; my $sth = $dbh->prepare($query); $sth->execute(@params); + $sth->finish; } @@ -1146,7 +1190,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; @@ -1421,7 +1466,8 @@ sub ModReceiveOrder { # (entirely received) $sth=$dbh->prepare(" UPDATE aqorders - SET quantity = ? + SET quantity = ?, + orderstatus = 2 WHERE ordernumber = ? "); @@ -1448,7 +1494,7 @@ sub ModReceiveOrder { } else { $sth=$dbh->prepare("update aqorders set quantityreceived=?,datereceived=?,invoiceid=?, - unitprice=?,rrp=?,ecost=? + unitprice=?,rrp=?,ecost=?,orderstatus=3 where biblionumber=? and ordernumber=?"); $sth->execute($quantrec,$datereceived,$invoiceid,$cost,$rrp,$ecost,$biblionumber,$ordernumber); $sth->finish; @@ -1498,7 +1544,8 @@ sub CancelReceipt { UPDATE aqorders SET quantityreceived = ?, datereceived = ?, - invoiceid = ? + invoiceid = ?, + orderstatus = 1 WHERE ordernumber = ? }; $sth = $dbh->prepare($query); @@ -1525,7 +1572,8 @@ sub CancelReceipt { } $query = qq{ UPDATE aqorders - SET quantity = ? + SET quantity = ?, + orderstatus = 1 WHERE ordernumber = ? }; $sth = $dbh->prepare($query); @@ -1665,7 +1713,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); @@ -2026,6 +2074,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; @@ -2034,10 +2085,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, @@ -2052,19 +2103,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 = (); @@ -2083,7 +2147,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"; } @@ -2092,6 +2156,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; @@ -2102,6 +2171,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 = ? "; @@ -2137,9 +2211,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; } @@ -2603,6 +2677,31 @@ sub ReopenInvoice { $sth->execute($invoiceid); } +=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 7eff6e4..f7dd4cb 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 971fc18..e9e4009 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 ca2f2b2..7e510f9 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2914,6 +2914,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 f3a90d8..c4b970f 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -6984,6 +6984,22 @@ INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ( } + + + + +$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 4132edb..2b5f30a 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' %] -<title>Koha › Acquisitions › [% IF ( suggestions_loop ) %]Orders search › Search results[% ELSE %]Order search[% END %]</title> +<title>Koha › Acquisitions › [% IF ( order_loop ) %]Orders search › Search results[% ELSE %]Order search[% END %]</title> <link rel="stylesheet" type="text/css" href="[% themelang %]/css/datatables.css" /> [% INCLUDE 'doc-head-close.inc' %] [% INCLUDE 'calendar.inc' %] @@ -24,7 +24,7 @@ [% INCLUDE 'header.inc' %] [% INCLUDE 'acquisitions-search.inc' %] -<div id="breadcrumbs"><a href="/cgi-bin/koha/mainpage.pl">Home</a> › <a href="/cgi-bin/koha/acqui/acqui-home.pl">Acquisitions</a> › [% IF ( suggestions_loop ) %]<a href="/cgi-bin/koha/acqui/histsearch.pl">Orders search</a> › Search results[% ELSE %]Order search[% END %]</div> +<div id="breadcrumbs"><a href="/cgi-bin/koha/mainpage.pl">Home</a> › <a href="/cgi-bin/koha/acqui/acqui-home.pl">Acquisitions</a> › [% IF ( order_loop ) %]<a href="/cgi-bin/koha/acqui/histsearch.pl">Orders search</a> › Search results[% ELSE %]Order search[% END %]</div> <div id="doc3" class="yui-t2"> @@ -32,7 +32,7 @@ <div id="yui-main"> <div class="yui-b"> - [% UNLESS ( suggestions_loop ) %]<form action="/cgi-bin/koha/acqui/histsearch.pl" method="post"> +[% UNLESS ( order_loop ) %]<form action="/cgi-bin/koha/acqui/histsearch.pl" method="post"> <fieldset class="rows"> <legend>Search Orders</legend> <ol> @@ -49,6 +49,35 @@ <label for="basketgroupname">Basket group:</label> <input type="text" name="basketgroupname" id="basketgroupname" value="[% basketgroupname %]" /> </li> + + <li> + <label for="orderstatus">Order Status: </label> + <select name="orderstatus" id="orderstatus"> + <option value=""></option> + <option value="0">New</option> + <option value="1">Ordered</option> + <option value="2">Partial</option> + <option value="3">Complete</option> + <option value="4">Deleted</option> + </select> + </li> + + <li> + <label for="fund">Fund: </label> + <select name="budget" id="fund"> + <option value="">All funds</option> + [% FOREACH bp_loo IN bp_loop %] + <optgroup label="[% bp_loo.budget_period_description %]"> + [% FOREACH h_loo IN bp_loo.hierarchy %] + <option type="text" value="[% h_loo.budget_id %]" branchcode="[% h_loo.budget_branchcode %]"> + [% h_loo.budget_display_name %] + </option> + [% END %] + </optgroup> + [% END %] + </select> + </li> + <li><label for="from">From: </label> <input type="text" size="10" id="from" name="from" value="[% from_placed_on %]" class="datepickerfrom" /> <div class="hint">[% INCLUDE 'date-format.inc' %]</div> @@ -62,11 +91,12 @@ <input type="hidden" name="do_search" value="1" /> <fieldset class="action"><input type="submit" value="Search" /></fieldset> </form>[% END %] - [% IF ( suggestions_loop ) %]<h1>Search results</h1> + [% IF ( order_loop ) %]<h1>Search results</h1> <div id="acqui_histsearch"> <table id="histsearcht"> <thead> <tr> + <th>Order line</th> <th>Basket</th> <th>Basket group</th> <th>Invoice number</th> @@ -75,41 +105,57 @@ <th>Vendor</th> <th>Placed on</th> <th>Received on</th> - <th>Quantity ordered</th> + <th>Status</th> + <th>Quantity received</th> + <th>Pending order</th> <th>Unit cost</th> + <th>Fund</th> </tr> </thead> <tbody> - [% FOREACH suggestions_loo IN suggestions_loop %] - <tr> - <td>[% suggestions_loo.basketname %] (<a href="basket.pl?basketno=[% suggestions_loo.basketno %]">[% suggestions_loo.basketno %]</a>)</td> + [% FOREACH order IN order_loop %] + <tr> + <td>[% order.parent_ordernumber %]</td> + <td>[% order.basketname %] (<a href="basket.pl?basketno=[% order.basketno %]">[% order.basketno %]</a>)</td> <td> - [% IF ( suggestions_loo.basketgroupid ) %] - [% suggestions_loo.groupname %] (<a href="basketgroup.pl?booksellerid=[% suggestions_loo.id %]">[% suggestions_loo.basketgroupid %]</a>) + [% IF ( order.basketgroupid ) %] + [% order.groupname %] (<a href="basketgroup.pl?booksellerid=[% order.id %]">[% order.basketgroupid %]</a>) [% ELSE %] [% END %] </td> - <td>[% IF suggestions_loo.invoicenumber %] - <a href="/cgi-bin/koha/acqui/parcel.pl?invoiceid=[% suggestions_loo.invoiceid %]">[% suggestions_loo.invoicenumber %]</a> - [% ELSE %] - - [% END %] - </td> - <td>[% suggestions_loo.ordernumber %]</td> - <td><a href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=[% suggestions_loo.biblionumber %]">[% suggestions_loo.title |html %]</a> - <br />[% suggestions_loo.author %] <br /> [% suggestions_loo.isbn %]</td> - <td><a href="/cgi-bin/koha/acqui/supplier.pl?booksellerid=[% suggestions_loo.id %]">[% suggestions_loo.name %]</a></td> - <td>[% suggestions_loo.creationdate | $KohaDates %]</td> + <td>[% IF ( order.invoicenumber ) %] + <a href="/cgi-bin/koha/acqui/parcel.pl?invoiceid=[% order.invoiceid %]">[% order.invoicenumber %]</a> + [% ELSE %] + + [% END %] + </td> + <td>[% order.ordernumber %]</td> + <td> + <a href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=[% order.biblionumber %]">[% order.title |html %]</a> + <br />[% order.author %] <br /> [% IF ( order.internalnotes ) %][% order.internalnotes %]<br />[% END %][% order.isbn %]</td> + <td><a href="/cgi-bin/koha/acqui/supplier.pl?booksellerid=[% order.id %]">[% order.name %]</a></td> + <td>[% order.creationdate | $KohaDates %]</td> + <td> + [% IF order.datereceived %] + [% order.datereceived | $KohaDates %] + [% END %] + </td> <td> - [% 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 %] </td> - <td>[% suggestions_loo.quantity %]</td> - <td>[% suggestions_loo.ecost %]</td> - </tr> - [% END %] + <td>[% order.quantityreceived %]</td> + <td>[% order.quantity %]</td> + <td>[% order.ecost %]</td> + <td>[% order.budget_name %]</td> + </tr> + [% END %] </tbody> </table> </div>[% ELSE %][% END %] -- 1.7.10.4