use strict;
use warnings;
+use Carp;
use C4::Context;
use C4::Debug;
use C4::Dates qw(format_date format_date_in_iso);
&GetOrderNumber &GetLateOrders &GetOrderFromItemnumber
&SearchOrder &GetHistory &GetRecentAcqui
&ModReceiveOrder &ModOrderBiblioitemNumber
+ &GetCancelledOrders
&NewOrderItem &ModOrderItem
my $output;
# TODO: Translate headers
- my @headers = qw(contractname ordernumber line entrydate isbn author title publishercode collectiontitle notes quantity rrp);
+ my @headers = qw(contractname ordernumber entrydate isbn author title publishercode collectiontitle notes quantity rrp);
$csv->combine(@headers);
$output = $csv->string() . "\n";
my @rows;
foreach my $order (@orders) {
my @cols;
- my $bd = GetBiblioData($order->{'biblionumber'});
+ # 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'},
- $bd->{'author'},
- $bd->{'title'},
- $bd->{'publishercode'},
- $bd->{'collectiontitle'},
+ $order->{'author'},
+ $order->{'title'},
+ $order->{'publishercode'},
+ $order->{'collectiontitle'},
$order->{'notes'},
$order->{'quantity'},
$order->{'rrp'},
push (@rows, \@cols);
}
- # Sort by publishercode
- # TODO: Sort by publishercode then by title
- @rows = sort { @$a[7] cmp @$b[7] } @rows;
-
foreach my $row (@rows) {
$csv->combine(@$row);
$output .= $csv->string() . "\n";
LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
WHERE booksellerid=?
AND (quantity > quantityreceived OR quantityreceived is NULL)
- AND datecancellationprinted IS NULL
- AND (to_days(now())-to_days(closedate) < 180 OR closedate IS NULL)
- ";
- ## FIXME Why 180 days ???
+ AND datecancellationprinted IS NULL";
my @query_params = ( $supplierid );
my $userenv = C4::Context->userenv;
if ( C4::Context->preference("IndependantBranches") ) {
# 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' ) {
$sth->execute( $biblioitemnumber, $ordernumber, $biblionumber );
}
+=head3 GetCancelledOrders
+
+ my @orders = GetCancelledOrders($basketno, $orderby);
+
+Returns cancelled orders for a basket
+
+=cut
+
+sub GetCancelledOrders {
+ my ( $basketno, $orderby ) = @_;
+
+ return () unless $basketno;
+
+ my $dbh = C4::Context->dbh;
+ my $query = "
+ SELECT biblio.*, biblioitems.*, aqorders.*, aqbudgets.*
+ FROM aqorders
+ 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 NOT NULL
+ AND datecancellationprinted <> '0000-00-00')
+ ";
+
+ $orderby = "aqorders.datecancellationprinted desc, aqorders.timestamp desc"
+ unless $orderby;
+ $query .= " ORDER BY $orderby";
+ my $sth = $dbh->prepare($query);
+ $sth->execute($basketno);
+ my $results = $sth->fetchall_arrayref( {} );
+
+ return @$results;
+}
+
+
#------------------------------------------------------------#
=head3 ModReceiveOrder
my $sth = $dbh->prepare($query);
$sth->execute( $bibnum, $ordernumber );
$sth->finish;
+ my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
+ foreach my $itemnumber (@itemnumbers){
+ C4::Items::DelItem( $dbh, $bibnum, $itemnumber );
+ }
+
}
=head2 FUNCTIONS ABOUT PARCELS
sum(quantity) AS itemsexpected,
sum(quantityreceived) AS itemsreceived
FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
- WHERE aqbasket.booksellerid = $bookseller and datereceived IS NOT NULL
+ WHERE aqbasket.booksellerid = ? and datereceived IS NOT NULL
";
+ push @query_params, $bookseller;
if ( defined $code ) {
$strsth .= ' and aqorders.booksellerinvoicenumber like ? ';
OR datereceived IS NULL
OR aqorders.quantityreceived < aqorders.quantity
)
+ AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
";
my $having = "";
if ($dbdriver eq "mysql") {
=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:
{
=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 $isbn = $params{isbn};
+ 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,
+ biblioitems.isbn,
+ 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 biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
+ 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 ( $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 ( $author ) {
+ $query .= " AND biblio.author LIKE ? ";
+ push @query_params, "%$author%";
+ }
+
+ if ( $isbn ) {
+ $query .= " AND biblioitems.isbn LIKE ? ";
+ push @query_params, "%$isbn%";
+ }
+
+ if ( $name ) {
+ $query .= " AND aqbooksellers.name LIKE ? ";
+ push @query_params, "%$name%";
+ }
+
+ if ( $from_placed_on ) {
+ $query .= " AND creationdate >= ? ";
+ push @query_params, $from_placed_on;
+ }
+
+ if ( $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.basketname 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;
}