use strict;
use C4::Context;
+use C4::Debug;
use C4::Dates qw(format_date);
use MARC::Record;
use C4::Suggestions;
=head1 FUNCTIONS
-=over 2
-
=head2 FUNCTIONS ABOUT BASKETS
-=over 2
-
=head3 GetBasket
=over 4
=back
-=back
-
=cut
sub GetBasket {
#------------------------------------------------------------#
-=back
-
=head2 FUNCTIONS ABOUT ORDERS
-=over 2
-
=cut
#------------------------------------------------------------#
=cut
sub GetPendingOrders {
- my ($supplierid,$grouped) = @_;
+ my ($supplierid,$grouped, $closed) = @_;
my $dbh = C4::Context->dbh;
my $strsth = "
SELECT ".($grouped?"count(*),":"")."aqbasket.basketno,
LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
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)
";
+ if($closed){
+ $strsth .= "
+ AND (quantity > quantityreceived OR quantityreceived is NULL)
+ AND closedate IS NOT NULL ";
+ }
## FIXME Why 180 days ???
my @query_params = ( $supplierid );
if ( C4::Context->preference("IndependantBranches") ) {
my $dbh = C4::Context->dbh;
my $query ="
SELECT aqorderbreakdown.*,
- biblio.*,biblioitems.publishercode,
+ biblio.*,biblioitems.*,
aqorders.*,
aqbookfund.bookfundname,
biblio.title
$ordernumber = &GetOrderNumber($biblioitemnumber, $biblionumber);
+=back
+
Looks up the ordernumber with the given biblionumber and biblioitemnumber.
Returns the number of this order.
+=over 4
+
=item C<$ordernumber> is the order number.
=back
&NewOrder($basket, $biblionumber, $title, $quantity, $listprice,
$booksellerid, $who, $notes, $bookfund, $biblioitemnumber, $rrp,
$ecost, $gst, $budget, $unitprice, $subscription,
- $booksellerinvoicenumber, $purchaseorder);
+ $booksellerinvoicenumber, $purchaseorder, $branchcode);
Adds a new order to the database. Any argument that isn't described
below is the new value of the field with the same name in the aqorders
$listprice, $booksellerid, $authorisedby, $notes,
$bookfund, $bibitemnum, $rrp, $ecost,
$gst, $budget, $cost, $sub,
- $invoice, $sort1, $sort2, $purchaseorder
+ $invoice, $sort1, $sort2, $purchaseorder,
+ $branchcode
)
= @_;
#get ordnum MYSQL dependant, but $dbh->last_insert_id returns null
my $ordnum = $dbh->{'mysql_insertid'};
$query = "
- INSERT INTO aqorderbreakdown (ordernumber,bookfundid)
- VALUES (?,?)
+ INSERT INTO aqorderbreakdown (ordernumber,bookfundid, branchcode)
+ VALUES (?,?,?)
";
$sth = $dbh->prepare($query);
- $sth->execute( $ordnum, $bookfund );
+ $sth->execute( $ordnum, $bookfund, $branchcode );
$sth->finish;
return ( $basketno, $ordnum );
}
&ModOrder($title, $ordernumber, $quantity, $listprice,
$biblionumber, $basketno, $supplier, $who, $notes,
$bookfundid, $bibitemnum, $rrp, $ecost, $gst, $budget,
- $unitprice, $booksellerinvoicenumber);
+ $unitprice, $booksellerinvoicenumber, $branchcode);
Modifies an existing order. Updates the order with order number
C<$ordernumber> and biblionumber C<$biblionumber>. All other arguments
database.
Entries with order number C<$ordernumber> in the aqorderbreakdown
-table are also updated to the new book fund ID.
+table are also updated to the new book fund ID or branchcode.
=back
$title, $ordnum, $quantity, $listprice, $bibnum,
$basketno, $supplier, $who, $notes, $bookfund,
$bibitemnum, $rrp, $ecost, $gst, $budget,
- $cost, $invoice, $sort1, $sort2, $purchaseorder
+ $cost, $invoice, $sort1, $sort2, $purchaseorder, $branchcode
)
= @_;
+ # FIXME : Refactor to pass a hashref instead of fifty params.
my $dbh = C4::Context->dbh;
my $query = "
UPDATE aqorders
$ordnum, $bibnum
);
$sth->finish;
- my $branchcode;
$query = "
UPDATE aqorderbreakdown
SET bookfundid=?,branchcode=?
";
$sth = $dbh->prepare($query);
- unless ( $sth->execute( $bookfund,$branchcode, $ordnum ) )
- { # zero rows affected [Bug 734]
+ my $rv = $sth->execute( $bookfund,$branchcode, $ordnum );
+ unless($rv && ( $rv ne '0E0' )) { # zero rows affected [Bug 734]
my $query ="
INSERT INTO aqorderbreakdown
(ordernumber,branchcode,bookfundid)
push( @searchterms, $search, $search, $biblionumber );
my $query;
### FIXME THIS CAN raise a problem if more THAN ONE biblioitem is linked to one biblio
- if ($id) {
+ if($id and $search){
+ @searchterms = ($id, $search);
+ $query =
+ "SELECT *,biblio.title
+ FROM aqorders
+ LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
+ LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
+ LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
+ WHERE aqbasket.booksellerid = ? AND aqorders.ordernumber = ?
+ "
+ }elsif ($id) {
$query =
"SELECT *,biblio.title
FROM aqorders
)
. ") or biblioitems.isbn=? OR (aqorders.ordernumber=? AND aqorders.biblionumber=?)) ";
}
+
+ if ( $biblionumber ) {
+ $query .= "AND biblio.biblionumber = ? ";
+ push (@searchterms, $biblionumber);
+ }
+
$query .= " GROUP BY aqorders.ordernumber";
### $query
my $sth = $dbh->prepare($query);
$sth->finish;
}
-
-=back
-
=head2 FUNCTIONS ABOUT PARCELS
-=over 2
-
=cut
#------------------------------------------------------------#
$results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
get a lists of parcels.
+=back
+
* Input arg :
+=over 4
+
=item $bookseller
is the bookseller this function has to get parcels.
my $dbh = C4::Context->dbh;
#BEWARE, order of parenthesis and LEFT JOIN is important for speed
- my $strsth;
my $dbdriver = C4::Context->config("db_scheme") || "mysql";
- my @query_params = ();
-
- # warn " $dbdriver";
- if ( $dbdriver eq "mysql" ) {
- $strsth = "
- SELECT aqbasket.basketno,aqorders.ordernumber,
- DATE(aqbasket.closedate) AS orderdate,
- aqorders.quantity - IFNULL(aqorders.quantityreceived,0) AS quantity,
- aqorders.rrp AS unitpricesupplier,
- aqorders.ecost AS unitpricelib,
- (aqorders.quantity - IFNULL(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
- aqbookfund.bookfundname AS budget,
- borrowers.branchcode AS branch,
- aqbooksellers.name AS supplier,
- aqorders.title,
- biblio.author,
- biblioitems.publishercode AS publisher,
- biblioitems.publicationyear,
- DATEDIFF(CURDATE( ),closedate) AS latesince
- FROM (((
- (aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber)
- LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber)
- LEFT JOIN aqorderbreakdown ON aqorders.ordernumber = aqorderbreakdown.ordernumber)
- LEFT JOIN aqbookfund ON aqorderbreakdown.bookfundid = aqbookfund.bookfundid),
- (aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber)
- LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
- WHERE aqorders.basketno = aqbasket.basketno
- AND (closedate <= DATE_SUB(CURDATE( ),INTERVAL ? DAY))
- AND ((datereceived = '' OR datereceived is null)
- OR (aqorders.quantityreceived < aqorders.quantity) )
- ";
-
- push @query_params, $delay;
-
- if ( defined $supplierid ) {
- $strsth .= ' AND aqbasket.booksellerid = ? ';
- push @query_params, $supplierid;
- }
-
- if ( defined $branch ) {
- $strsth .= ' AND borrowers.branchcode like ? ';
- push @query_params, $branch;
- }
-
- if ( C4::Context->preference("IndependantBranches")
+ my @query_params = ($delay); # delay is the first argument regardless
+ my $select = "
+ SELECT aqbasket.basketno,
+ aqorders.ordernumber,
+ DATE(aqbasket.closedate) AS orderdate,
+ aqorders.rrp AS unitpricesupplier,
+ aqorders.ecost AS unitpricelib,
+ aqbookfund.bookfundname AS budget,
+ borrowers.branchcode AS branch,
+ aqbooksellers.name AS supplier,
+ aqorders.title,
+ biblio.author,
+ biblioitems.publishercode AS publisher,
+ biblioitems.publicationyear,
+ ";
+ my $from = "
+ FROM (((
+ (aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber)
+ LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber)
+ LEFT JOIN aqorderbreakdown ON aqorders.ordernumber = aqorderbreakdown.ordernumber)
+ LEFT JOIN aqbookfund ON aqorderbreakdown.bookfundid = aqbookfund.bookfundid),
+ (aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber)
+ LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
+ WHERE aqorders.basketno = aqbasket.basketno
+ AND ( (datereceived = '' OR datereceived IS NULL)
+ OR (aqorders.quantityreceived < aqorders.quantity)
+ )
+ ";
+ my $having = "";
+ if ($dbdriver eq "mysql") {
+ $select .= "
+ aqorders.quantity - IFNULL(aqorders.quantityreceived,0) AS quantity,
+ (aqorders.quantity - IFNULL(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
+ DATEDIFF(CURDATE( ),closedate) AS latesince
+ ";
+ $from .= " AND (closedate <= DATE_SUB(CURDATE( ),INTERVAL ? DAY)) ";
+ $having = "
+ HAVING quantity <> 0
+ AND unitpricesupplier <> 0
+ AND unitpricelib <> 0
+ ";
+ } else {
+ # FIXME: account for IFNULL as above
+ $select .= "
+ aqorders.quantity AS quantity,
+ aqorders.quantity * aqorders.rrp AS subtotal,
+ (CURDATE - closedate) AS latesince
+ ";
+ $from .= " AND (closedate <= (CURDATE -(INTERVAL ? DAY)) ";
+ }
+ if (defined $supplierid) {
+ $from .= ' AND aqbasket.booksellerid = ? ';
+ push @query_params, $supplierid;
+ }
+ if (defined $branch) {
+ $from .= ' AND borrowers.branchcode LIKE ? ';
+ push @query_params, $branch;
+ }
+ if (C4::Context->preference("IndependantBranches")
&& C4::Context->userenv
&& C4::Context->userenv->{flags} != 1 ) {
- $strsth .= ' AND borrowers.branchcode like ? ';
- push @query_params, C4::Context->userenv->{branch};
- }
-
- $strsth .= " HAVING quantity <> 0
- AND unitpricesupplier <> 0
- AND unitpricelib <> 0
- ORDER BY latesince, basketno, borrowers.branchcode, supplier ";
- } else {
- $strsth = "
- SELECT aqbasket.basketno,
- DATE(aqbasket.closedate) AS orderdate,
- aqorders.quantity, aqorders.rrp AS unitpricesupplier,
- aqorders.ecost as unitpricelib,
- aqorders.quantity * aqorders.rrp AS subtotal
- aqbookfund.bookfundname AS budget,
- borrowers.branchcode AS branch,
- aqbooksellers.name AS supplier,
- biblio.title,
- biblio.author,
- biblioitems.publishercode AS publisher,
- biblioitems.publicationyear,
- (CURDATE - closedate) AS latesince
- FROM(( (
- (aqorders LEFT JOIN biblio on biblio.biblionumber = aqorders.biblionumber)
- LEFT JOIN biblioitems on biblioitems.biblionumber=biblio.biblionumber)
- LEFT JOIN aqorderbreakdown on aqorders.ordernumber = aqorderbreakdown.ordernumber)
- LEFT JOIN aqbookfund ON aqorderbreakdown.bookfundid = aqbookfund.bookfundid),
- (aqbasket LEFT JOIN borrowers on aqbasket.authorisedby = borrowers.borrowernumber) LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
- WHERE aqorders.basketno = aqbasket.basketno
- AND (closedate <= (CURDATE -(INTERVAL $delay DAY))
- AND ((datereceived = '' OR datereceived is null)
- OR (aqorders.quantityreceived < aqorders.quantity) ) ";
- $strsth .= " AND aqbasket.booksellerid = $supplierid " if ($supplierid);
-
- $strsth .= " AND borrowers.branchcode like \'" . $branch . "\'" if ($branch);
- $strsth .=" AND borrowers.branchcode like \'". C4::Context->userenv->{branch} . "\'"
- if (C4::Context->preference("IndependantBranches") && C4::Context->userenv->{flags} != 1 );
- $strsth .=" ORDER BY latesince,basketno,borrowers.branchcode, supplier";
+ $from .= ' AND borrowers.branchcode LIKE ? ';
+ push @query_params, C4::Context->userenv->{branch};
}
- my $sth = $dbh->prepare($strsth);
- $sth->execute( @query_params );
+ my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier";
+ $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params);
+ my $sth = $dbh->prepare($query);
+ $sth->execute(@query_params);
my @results;
- my $hilighted = 1;
- while ( my $data = $sth->fetchrow_hashref ) {
- $data->{hilighted} = $hilighted if ( $hilighted > 0 );
- $data->{orderdate} = format_date( $data->{orderdate} );
+ while (my $data = $sth->fetchrow_hashref) {
+ $data->{orderdate} = format_date($data->{orderdate});
push @results, $data;
- $hilighted = -$hilighted;
}
- $sth->finish;
return @results;
}
=over 4
-(\@order_loop, $total_qty, $total_price, $total_qtyreceived)=&GetHistory( $title, $author, $name, $from_placed_on, $to_placed_on )
-
-this function get the search history.
+(\@order_loop, $total_qty, $total_price, $total_qtyreceived) = GetHistory( $title, $author, $name, $from_placed_on, $to_placed_on );
+
+ Retreives some acquisition history information
+
+ returns:
+ $order_loop is a list of hashrefs that each look like this:
+ {
+ 'author' => 'Twain, Mark',
+ 'basketno' => '1',
+ 'biblionumber' => '215',
+ 'count' => 1,
+ 'creationdate' => 'MM/DD/YYYY',
+ 'datereceived' => undef,
+ 'ecost' => '1.00',
+ 'id' => '1',
+ 'invoicenumber' => undef,
+ 'name' => '',
+ 'ordernumber' => '1',
+ 'quantity' => 1,
+ 'quantityreceived' => undef,
+ 'title' => 'The Adventures of Huckleberry Finn'
+ }
+ $total_qty is the sum of all of the quantities in $order_loop
+ $total_price is the cost of each in $order_loop times the quantity
+ $total_qtyreceived is the sum of all of the quantityreceived entries in $order_loop
=back
$query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber"
if ( C4::Context->preference("IndependantBranches") );
- $query .= " WHERE 1 ";
- $query .= " AND biblio.title LIKE " . $dbh->quote( "%" . $title . "%" )
- if $title;
+ $query .= " WHERE (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') ";
+
+ my @query_params = ();
+
+ if ( defined $title ) {
+ $query .= " AND biblio.title LIKE ? ";
+ push @query_params, "%$title%";
+ }
- $query .=
- " AND biblio.author LIKE " . $dbh->quote( "%" . $author . "%" )
- if $author;
+ if ( defined $author ) {
+ $query .= " AND biblio.author LIKE ? ";
+ push @query_params, "%$author%";
+ }
- $query .= " AND name LIKE " . $dbh->quote( "%" . $name . "%" ) if $name;
+ if ( defined $name ) {
+ $query .= " AND name LIKE ? ";
+ push @query_params, "%$name%";
+ }
- $query .= " AND creationdate >" . $dbh->quote($from_placed_on)
- if $from_placed_on;
+ if ( defined $from_placed_on ) {
+ $query .= " AND creationdate >= ? ";
+ push @query_params, $from_placed_on;
+ }
- $query .= " AND creationdate<" . $dbh->quote($to_placed_on)
- if $to_placed_on;
- $query .= " AND (datecancellationprinted is NULL or datecancellationprinted='0000-00-00')";
+ if ( defined $to_placed_on ) {
+ $query .= " AND creationdate <= ? ";
+ push @query_params, $to_placed_on;
+ }
if ( C4::Context->preference("IndependantBranches") ) {
my $userenv = C4::Context->userenv;
if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
- $query .=
- " AND (borrowers.branchcode = '"
- . $userenv->{branch}
- . "' OR borrowers.branchcode ='')";
+ $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
+ push @query_params, $userenv->{branch};
}
}
$query .= " ORDER BY booksellerid";
my $sth = $dbh->prepare($query);
- $sth->execute;
+ $sth->execute( @query_params );
my $cnt = 1;
while ( my $line = $sth->fetchrow_hashref ) {
$line->{count} = $cnt++;
1;
__END__
-=back
-
=head1 AUTHOR
Koha Developement team <info@koha.org>
projects / koha.git / blobdiff