use strict;
use warnings;
+use Carp;
use C4::Context;
use C4::Debug;
use C4::Dates qw(format_date format_date_in_iso);
&GetBasket &NewBasket &CloseBasket &DelBasket &ModBasket
&GetBasketAsCSV
&GetBasketsByBookseller &GetBasketsByBasketgroup
+ &GetBasketsInfosByBookseller
&ModBasketHeader
&GetOrderNumber &GetLateOrders &GetOrderFromItemnumber
&SearchOrder &GetHistory &GetRecentAcqui
&ModReceiveOrder &ModOrderBiblioitemNumber
+ &GetCancelledOrders
&NewOrderItem &ModOrderItem
&GetContracts &GetContract
&GetItemnumbersFromOrder
+
+ &AddClaim
);
}
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";
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, 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
my $dbh = C4::Context->dbh;
my $query = "UPDATE aqbasketgroups SET ";
my @params;
- foreach my $field (qw(name billingplace deliveryplace deliverycomment closed)) {
+ foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
if ( defined $basketgroupinfo->{$field} ) {
$query .= "$field=?, ";
push(@params, $basketgroupinfo->{$field});
sub GetBasketgroups {
my $booksellerid = shift;
die "bookseller id is required to edit a basketgroup" unless $booksellerid;
- my $query = "SELECT * FROM aqbasketgroups WHERE booksellerid=?";
+ my $query = "SELECT * FROM aqbasketgroups WHERE booksellerid=? ORDER BY `id` DESC";
my $dbh = C4::Context->dbh;
my $sth = $dbh->prepare($query);
$sth->execute($booksellerid);
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
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' ) {
=cut
sub NewOrderItem {
- #my ($biblioitemnumber,$ordernumber, $biblionumber) = @_;
my ($itemnumber, $ordernumber) = @_;
my $dbh = C4::Context->dbh;
my $query = qq|
my $dbh = C4::Context->dbh;
my @params;
+
+ # update uncertainprice to an integer, just in case (under FF, checked boxes have the value "ON" by default)
+ $orderinfo->{uncertainprice}=1 if $orderinfo->{uncertainprice};
+
# delete($orderinfo->{'branchcode'});
# the hash contains a lot of entries not in aqorders, so get the columns ...
my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;");
$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 $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 );
if ($suggestionid) {
ModSuggestion( {suggestionid=>$suggestionid,
- STATUS=>'AVAILABLE',
- biblionumber=> $biblionumber}
- );
+ STATUS=>'AVAILABLE',
+ biblionumber=> $biblionumber}
+ );
}
my $sth=$dbh->prepare("
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 ? ';
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,
- biblio.author,
+ aqbooksellers.id AS supplierid,
+ biblio.author, biblio.title,
biblioitems.publishercode AS publisher,
biblioitems.publicationyear,
";
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") {
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;
=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;
+ $total_qty += $line->{'quantity'};
+ $total_qtyreceived += $line->{'quantityreceived'};
+ $total_price += $line->{'quantity'} * $line->{'ecost'};
+ }
return \@order_loop, $total_qty, $total_price, $total_qtyreceived;
}
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__
projects / koha.git / blobdiff