From 3e75062814f3a40c86e987e8412f70719e15208e Mon Sep 17 00:00:00 2001 From: Srdjan Jankovic Date: Tue, 16 Aug 2011 17:33:44 +1200 Subject: [PATCH] bug_6721: Acquisitions: basket and invoice search --- C4/Acquisition.pm | 186 ++++++++++++-------- acqui/histsearch.pl | 27 ++- .../prog/en/includes/acquisitions-search.inc | 8 +- .../prog/en/modules/acqui/histsearch.tt | 2 + t/db_dependent/lib/KohaTest.pm | 6 +- t/db_dependent/lib/KohaTest/Acquisition.pm | 38 ++--- .../lib/KohaTest/Acquisition/GetHistory.pm | 50 ++++-- 7 files changed, 192 insertions(+), 125 deletions(-) diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm index 738fe16..4d49d70 100644 --- a/C4/Acquisition.pm +++ b/C4/Acquisition.pm @@ -20,6 +20,7 @@ package C4::Acquisition; use strict; use warnings; +use Carp; use C4::Context; use C4::Debug; use C4::Dates qw(format_date format_date_in_iso); @@ -897,7 +898,7 @@ sub NewOrder { # 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}; + croak "Mandatory parameter $key missing" unless $orderinfo->{$key}; } if ( defined $orderinfo->{subscription} && $orderinfo->{'subscription'} eq 'yes' ) { @@ -1486,10 +1487,19 @@ sub GetLateOrders { =head3 GetHistory - (\@order_loop, $total_qty, $total_price, $total_qtyreceived) = GetHistory( $title, $author, $name, $from_placed_on, $to_placed_on ); + (\@order_loop, $total_qty, $total_price, $total_qtyreceived) = GetHistory( %params ); Retreives some acquisition history information +params: + title + author + name + from_placed_on + to_placed_on + basket - search both basket name and number + booksellerinvoicenumber + returns: $order_loop is a list of hashrefs that each look like this: { @@ -1515,94 +1525,116 @@ returns: =cut sub GetHistory { - my ( $title, $author, $name, $from_placed_on, $to_placed_on ) = @_; +# don't run the query if there are no parameters (list would be too long for sure !) + croak "No search params" unless @_; + my %params = @_; + my $title = $params{title}; + my $author = $params{author}; + 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; my $total_price = 0; -# don't run the query if there are no parameters (list would be too long for sure !) - if ( $title || $author || $name || $from_placed_on || $to_placed_on ) { - my $dbh = C4::Context->dbh; - my $query =" - SELECT - biblio.title, - biblio.author, - aqorders.basketno, - aqbasket.basketname, - aqbasket.basketgroupid, - aqbasketgroups.name as groupname, - aqbooksellers.name, - aqbasket.creationdate, - aqorders.datereceived, - aqorders.quantity, - aqorders.quantityreceived, - aqorders.ecost, - aqorders.ordernumber, - aqorders.booksellerinvoicenumber as invoicenumber, - aqbooksellers.id as id, - aqorders.biblionumber - FROM aqorders - LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno - LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id - LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id - LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber"; - - $query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber" - if ( C4::Context->preference("IndependantBranches") ); - - $query .= " WHERE (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') "; - - my @query_params = (); - - if ( defined $title ) { - $query .= " AND biblio.title LIKE ? "; - $title =~ s/\s+/%/g; - push @query_params, "%$title%"; - } + my $dbh = C4::Context->dbh; + my $query =" + SELECT + biblio.title, + biblio.author, + aqorders.basketno, + aqbasket.basketname, + aqbasket.basketgroupid, + aqbasketgroups.name as groupname, + aqbooksellers.name, + aqbasket.creationdate, + aqorders.datereceived, + aqorders.quantity, + aqorders.quantityreceived, + aqorders.ecost, + aqorders.ordernumber, + aqorders.booksellerinvoicenumber as invoicenumber, + aqbooksellers.id as id, + aqorders.biblionumber + FROM aqorders + LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno + LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id + LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id + LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber"; - if ( defined $author ) { - $query .= " AND biblio.author LIKE ? "; - push @query_params, "%$author%"; - } + $query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber" + if ( C4::Context->preference("IndependantBranches") ); - if ( defined $name ) { - $query .= " AND aqbooksellers.name LIKE ? "; - push @query_params, "%$name%"; - } + $query .= " WHERE (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') "; - if ( defined $from_placed_on ) { - $query .= " AND creationdate >= ? "; - push @query_params, $from_placed_on; - } + my @query_params = (); - if ( defined $to_placed_on ) { - $query .= " AND creationdate <= ? "; - push @query_params, $to_placed_on; - } + if ( defined $title ) { + $query .= " AND biblio.title LIKE ? "; + $title =~ s/\s+/%/g; + push @query_params, "%$title%"; + } - if ( C4::Context->preference("IndependantBranches") ) { - my $userenv = C4::Context->userenv; - if ( ($userenv) && ( $userenv->{flags} != 1 ) ) { - $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) "; - push @query_params, $userenv->{branch}; - } + if ( defined $author ) { + $query .= " AND biblio.author LIKE ? "; + push @query_params, "%$author%"; + } + + if ( defined $name ) { + $query .= " AND aqbooksellers.name LIKE ? "; + push @query_params, "%$name%"; + } + + if ( defined $from_placed_on ) { + $query .= " AND creationdate >= ? "; + push @query_params, $from_placed_on; + } + + if ( defined $to_placed_on ) { + $query .= " AND creationdate <= ? "; + push @query_params, $to_placed_on; + } + + if ($basket) { + if ($basket =~ m/^\d+$/) { + $query .= " AND aqorders.basketno = ? "; + push @query_params, $basket; + } else { + $query .= " AND aqbasket.name LIKE ? "; + push @query_params, "%$basket%"; } - $query .= " ORDER BY id"; - my $sth = $dbh->prepare($query); - $sth->execute( @query_params ); - my $cnt = 1; - while ( my $line = $sth->fetchrow_hashref ) { - $line->{count} = $cnt++; - $line->{toggle} = 1 if $cnt % 2; - push @order_loop, $line; - $line->{creationdate} = format_date( $line->{creationdate} ); - $line->{datereceived} = format_date( $line->{datereceived} ); - $total_qty += $line->{'quantity'}; - $total_qtyreceived += $line->{'quantityreceived'}; - $total_price += $line->{'quantity'} * $line->{'ecost'}; + } + + if ($booksellerinvoicenumber) { + $query .= " AND (aqorders.booksellerinvoicenumber LIKE ? OR aqbasket.booksellerinvoicenumber LIKE ?)"; + push @query_params, "%$booksellerinvoicenumber%", "%$booksellerinvoicenumber%"; + } + + if ( C4::Context->preference("IndependantBranches") ) { + my $userenv = C4::Context->userenv; + if ( $userenv && ($userenv->{flags} || 0) != 1 ) { + $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) "; + push @query_params, $userenv->{branch}; } } + $query .= " ORDER BY id"; + my $sth = $dbh->prepare($query); + $sth->execute( @query_params ); + my $cnt = 1; + while ( my $line = $sth->fetchrow_hashref ) { + $line->{count} = $cnt++; + $line->{toggle} = 1 if $cnt % 2; + push @order_loop, $line; + $line->{creationdate} = format_date( $line->{creationdate} ); + $line->{datereceived} = format_date( $line->{datereceived} ); + $total_qty += $line->{'quantity'}; + $total_qtyreceived += $line->{'quantityreceived'}; + $total_price += $line->{'quantity'} * $line->{'ecost'}; + } return \@order_loop, $total_qty, $total_price, $total_qtyreceived; } diff --git a/acqui/histsearch.pl b/acqui/histsearch.pl index 2c41742..a674155 100755 --- a/acqui/histsearch.pl +++ b/acqui/histsearch.pl @@ -56,12 +56,14 @@ use C4::Acquisition; use C4::Dates; use C4::Debug; -my $input = new CGI; -my $title = $input->param( 'title'); -my $author = $input->param('author'); -my $name = $input->param( 'name' ); -my $from_placed_on = C4::Dates->new($input->param('from')); -my $to_placed_on = C4::Dates->new($input->param( 'to')); +my $input = new CGI; +my $title = $input->param( 'title'); +my $author = $input->param('author'); +my $name = $input->param( 'name' ); +my $basket = $input->param( 'basket' ); +my $booksellerinvoicenumber = $input->param( 'booksellerinvoicenumber' ); +my $from_placed_on = C4::Dates->new($input->param('from')); +my $to_placed_on = C4::Dates->new($input->param( 'to')); my $dbh = C4::Context->dbh; my ( $template, $loggedinuser, $cookie ) = get_template_and_user( @@ -83,8 +85,15 @@ if ( $d = $input->param('iso') ) { $to_iso = C4::Dates->new($d)->output('iso'); } -my ( $order_loop, $total_qty, $total_price, $total_qtyreceived ) = - GetHistory( $title, $author, $name, $from_iso, $to_iso ); +my ( $order_loop, $total_qty, $total_price, $total_qtyreceived ) = GetHistory( + title => $title, + author => $author, + name => $name, + from_placed_on => $from_iso, + to_placed_on => $to_iso, + basket => $basket, + booksellerinvoicenumber => $booksellerinvoicenumber, +); $template->param( suggestions_loop => $order_loop, @@ -95,6 +104,8 @@ $template->param( title => $title, author => $author, name => $name, + basket => $basket, + booksellerinvoicenumber => $booksellerinvoicenumber, from_placed_on => $from_placed_on->output('syspref'), to_placed_on => $to_placed_on->output('syspref'), DHTMLcalendar_dateformat=> C4::Dates->DHTMLcalendar(), diff --git a/koha-tmpl/intranet-tmpl/prog/en/includes/acquisitions-search.inc b/koha-tmpl/intranet-tmpl/prog/en/includes/acquisitions-search.inc index dd3d325..ddf6f8d 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/includes/acquisitions-search.inc +++ b/koha-tmpl/intranet-tmpl/prog/en/includes/acquisitions-search.inc @@ -12,10 +12,16 @@
+ + [+] Advanced Search +
-