# Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
# Suite 330, Boston, MA 02111-1307 USA
-# $Id$
use strict;
require Exporter;
use vars qw($VERSION @ISA @EXPORT);
# set the version for version checking
-$VERSION = do { my @v = '$Revision$' =~ /\d+/g; shift(@v) . "." . join( "_", map { sprintf "%03d", $_ } @v ); };
+$VERSION = 3.00;
# used in receiveorder subroutine
# to provide library specific handling
&GetBasket &NewBasket &CloseBasket
&GetPendingOrders &GetOrder &GetOrders
&GetOrderNumber &GetLateOrders &NewOrder &DelOrder
- &SearchOrder &GetHistory
+ &SearchOrder &GetHistory &GetRecentAcqui
&ModOrder &ModReceiveOrder &ModOrderBiblioNumber
- &GetParcels &GetParcel
+ &GetParcels &GetParcel
);
-
=head2 FUNCTIONS ABOUT BASKETS
=over 2
my $dbh = C4::Context->dbh;
my $query = "
SELECT aqbasket.*,
- borrowers.firstname+' '+borrowers.surname AS authorisedbyname,
- borrowers.branchcode AS branch
+ concat( b.firstname,' ',b.surname) AS authorisedbyname,
+ b.branchcode AS branch
FROM aqbasket
- LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
+ LEFT JOIN borrowers b ON aqbasket.authorisedby=b.borrowernumber
WHERE basketno=?
";
my $sth=$dbh->prepare($query);
$sth->execute($basketno);
- return ( $sth->fetchrow_hashref );
+ my $basket = $sth->fetchrow_hashref;
+ return ( $basket );
}
#------------------------------------------------------------#
=over 4
-$orders = &GetPendingOrders($booksellerid);
+$orders = &GetPendingOrders($booksellerid, $grouped);
Finds pending orders from the bookseller with the given ID. Ignores
completed and cancelled orders.
C<$orders> is a reference-to-array; each element is a
reference-to-hash with the following fields:
+C<$grouped> is a boolean that, if set to 1 will group all order lines of the same basket
+in a single result line
=over 2
=cut
sub GetPendingOrders {
- my $supplierid = @_;
+ my ($supplierid,$grouped) = @_;
my $dbh = C4::Context->dbh;
my $strsth = "
- SELECT count(*),authorisedby,creationdate,aqbasket.basketno,
- closedate,surname,firstname,aqorders.title
+ SELECT ".($grouped?"count(*),":"")."aqbasket.basketno,
+ surname,firstname,aqorders.*,
+ aqbasket.closedate, aqbasket.creationdate
FROM aqorders
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)
+ 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 ???
if ( C4::Context->preference("IndependantBranches") ) {
my $userenv = C4::Context->userenv;
if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
. "' or borrowers.branchcode ='')";
}
}
- $strsth .= " group by basketno order by aqbasket.basketno";
+ $strsth .= " group by aqbasket.basketno" if $grouped;
+ $strsth .= " order by aqbasket.basketno";
+
my $sth = $dbh->prepare($strsth);
$sth->execute($supplierid);
- my @results = ();
- while ( my $data = $sth->fetchrow_hashref ) {
- push( @results, $data );
- }
+ my $results = $sth->fetchall_arrayref({});
$sth->finish;
- return \@results;
+ return $results;
}
#------------------------------------------------------------#
sub GetOrders {
my ( $basketno, $orderby ) = @_;
my $dbh = C4::Context->dbh;
- my $query ="
- SELECT aqorderbreakdown.*,
- biblio.*,
+ my $query ="
+ SELECT aqorderbreakdown.*,
+ biblio.*,biblioitems.*,
aqorders.*,
+ aqbookfund.bookfundname,
biblio.title
- FROM aqorders,biblio
- LEFT JOIN aqorderbreakdown ON
- aqorders.ordernumber=aqorderbreakdown.ordernumber
+ FROM aqorders
+ LEFT JOIN aqorderbreakdown ON aqorders.ordernumber=aqorderbreakdown.ordernumber
+ LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
+ LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=aqorders.biblioitemnumber
+ LEFT JOIN aqbookfund ON aqbookfund.bookfundid=aqorderbreakdown.bookfundid
WHERE basketno=?
- AND biblio.biblionumber=aqorders.biblionumber
AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
";
- $orderby = "biblio.title" unless $orderby;
+ $orderby = "biblioitems.publishercode" unless $orderby;
$query .= " ORDER BY $orderby";
my $sth = $dbh->prepare($query);
$sth->execute($basketno);
my @results;
- # print $query;
while ( my $data = $sth->fetchrow_hashref ) {
push @results, $data;
}
$ordernumber = &GetOrderNumber($biblioitemnumber, $biblionumber);
-Looks up the ordernumber with the given biblionumber
+Looks up the ordernumber with the given biblionumber and biblioitemnumber.
Returns the number of this order.
=cut
sub GetOrderNumber {
- my ( $biblionumber ) = @_;
+ my ( $biblionumber,$biblioitemnumber ) = @_;
my $dbh = C4::Context->dbh;
my $query = "
SELECT ordernumber
FROM aqorders
WHERE biblionumber=?
-
+ AND biblioitemnumber=?
";
my $sth = $dbh->prepare($query);
- $sth->execute( $biblionumber );
+ $sth->execute( $biblionumber, $biblioitemnumber );
return $sth->fetchrow;
}
Looks up an order by order number.
Returns a reference-to-hash describing the order. The keys of
-C<$order> are fields from the biblio, , aqorders, and
+C<$order> are fields from the biblio, biblioitems, aqorders, and
aqorderbreakdown tables of the Koha database.
=back
my $dbh = C4::Context->dbh;
my $query = "
SELECT *
- FROM biblio,aqorders
+ FROM aqorders
LEFT JOIN aqorderbreakdown ON aqorders.ordernumber=aqorderbreakdown.ordernumber
+ LEFT JOIN biblio on biblio.biblionumber=aqorders.biblionumber
+ LEFT JOIN biblioitems on biblioitems.biblionumber=aqorders.biblionumber
WHERE aqorders.ordernumber=?
- AND biblio.biblionumber=aqorders.biblionumber
-
+
";
my $sth= $dbh->prepare($query);
$sth->execute($ordnum);
sub NewOrder {
my (
- $basketno, $biblionumber, $title, $quantity,
+ $basketno, $bibnum, $title, $quantity,
$listprice, $booksellerid, $authorisedby, $notes,
- $bookfund, $rrp, $ecost,
+ $bookfund, $bibitemnum, $rrp, $ecost,
$gst, $budget, $cost, $sub,
$invoice, $sort1, $sort2
)
my $query = "
INSERT INTO aqorders
( biblionumber,title,basketno,quantity,listprice,notes,
- rrp,ecost,gst,unitprice,subscription,sort1,sort2,budgetdate,entrydate)
- VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,$budget,now() )
+ biblioitemnumber,rrp,ecost,gst,unitprice,subscription,sort1,sort2,budgetdate,entrydate)
+ VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,$budget,now() )
";
my $sth = $dbh->prepare($query);
$sth->execute(
- $biblionumber, $title, $basketno, $quantity, $listprice,
- $notes, $rrp, $ecost, $gst,
+ $bibnum, $title, $basketno, $quantity, $listprice,
+ $notes, $bibitemnum, $rrp, $ecost, $gst,
$cost, $sub, $sort1, $sort2
);
$sth->finish;
#get ordnum MYSQL dependant, but $dbh->last_insert_id returns null
my $ordnum = $dbh->{'mysql_insertid'};
- my $query = "
+ $query = "
INSERT INTO aqorderbreakdown (ordernumber,bookfundid)
VALUES (?,?)
";
sub ModOrder {
my (
- $title, $ordnum, $quantity, $listprice, $biblionumber,
+ $title, $ordnum, $quantity, $listprice, $bibnum,
$basketno, $supplier, $who, $notes, $bookfund,
- $rrp, $ecost, $gst, $budget,
+ $bibitemnum, $rrp, $ecost, $gst, $budget,
$cost, $invoice, $sort1, $sort2
)
= @_;
$sth->execute(
$title, $quantity, $listprice, $basketno, $rrp,
$ecost, $cost, $invoice, $notes, $sort1,
- $sort2, $ordnum, $biblionumber
+ $sort2, $ordnum, $bibnum
);
$sth->finish;
- my $query = "
+ $query = "
UPDATE aqorderbreakdown
SET bookfundid=?
WHERE ordernumber=?
=cut
+sub ModOrderBiblioNumber {
+ my ($biblioitemnumber,$ordnum, $biblionumber) = @_;
+ my $dbh = C4::Context->dbh;
+ my $query = "
+ UPDATE aqorders
+ SET biblioitemnumber = ?
+ WHERE ordernumber = ?
+ AND biblionumber = ?";
+ my $sth = $dbh->prepare($query);
+ $sth->execute( $biblioitemnumber, $ordnum, $biblionumber );
+}
#------------------------------------------------------------#
sub ModReceiveOrder {
my (
$biblionumber, $ordnum, $quantrec, $user, $cost,
- $invoiceno, $freight, $rrp, $bookfund
+ $invoiceno, $freight, $rrp, $bookfund, $daterecieved
)
= @_;
my $dbh = C4::Context->dbh;
+# warn "DATE BEFORE : $daterecieved";
+ $daterecieved=POSIX::strftime("%Y-%m-%d",CORE::localtime) unless $daterecieved;
+# warn "DATE REC : $daterecieved";
my $query = "
UPDATE aqorders
- SET quantityreceived=?,datereceived=now(),booksellerinvoicenumber=?,
+ SET quantityreceived=?,datereceived=?,booksellerinvoicenumber=?,
unitprice=?,freight=?,rrp=?
WHERE biblionumber=? AND ordernumber=?
";
if ($suggestionid) {
ModStatus( $suggestionid, 'AVAILABLE', '', $biblionumber );
}
- $sth->execute( $quantrec, $invoiceno, $cost, $freight, $rrp, $biblionumber,
- $ordnum );
+ $sth->execute( $quantrec,$daterecieved, $invoiceno, $cost, $freight, $rrp, $biblionumber,
+ $ordnum);
$sth->finish;
# Allows libraries to change their bookfund during receiving orders
$sth->execute( $bookfund, $ordnum );
$sth->finish;
}
+ return $daterecieved;
}
#------------------------------------------------------------#
=cut
sub SearchOrder {
-### Requires fixing for KOHA 3 API for performance. Currently just fiixed so it works
-## Very CPU expensive searches seems to be repeated!!
-## This search can be directed to ZEBRA for title,isbn etc. ordernumber ,booksellerid to acquiorders
- my ( $search, $id, $biblio, $catview ) = @_;
+ my ( $search, $id, $biblionumber, $catview ) = @_;
my $dbh = C4::Context->dbh;
my @data = split( ' ', $search );
my @searchterms;
if ($id) {
@searchterms = ($id);
}
- map { push( @searchterms, "$_%", "% $_%" ) } @data;
- push( @searchterms, $search, $search, $biblio );
+ map { push( @searchterms, "$_%", "%$_%" ) } @data;
+ push( @searchterms, $search, $search, $biblionumber );
my $query;
- if ($id) {
+ ### FIXME THIS CAN raise a problem if more THAN ONE biblioitem is linked to one biblio
+ if ($id) {
$query =
- "SELECT *,biblio.title FROM aqorders,biblio,aqbasket
- WHERE biblio.biblionumber=aqorders.biblionumber AND
- aqorders.basketno = aqbasket.basketno
- AND aqbasket.booksellerid = ?
-
+ "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 ((datecancellationprinted is NULL)
OR (datecancellationprinted = '0000-00-00'))
AND (("
join( " AND ",
map { "(biblio.title like ? or biblio.title like ?)" } @data )
)
- . ") OR biblio.isbn=? OR (aqorders.ordernumber=? AND aqorders.biblionumber=?)) ";
+ . ") OR biblioitems.isbn=? OR (aqorders.ordernumber=? AND aqorders.biblionumber=?)) ";
}
else {
$query =
" SELECT *,biblio.title
- FROM aqorders,biblio,aqbasket
- WHERE aqorders.biblionumber = biblio.biblionumber
- AND aqorders.basketno = aqbasket.basketno
-
- AND ((datecancellationprinted is NULL)
+ FROM aqorders
+ LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
+ LEFT JOIN aqbasket on aqorders.basketno=aqbasket.basketno
+ LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
+ WHERE ((datecancellationprinted is NULL)
OR (datecancellationprinted = '0000-00-00'))
AND (aqorders.quantityreceived < aqorders.quantity OR aqorders.quantityreceived is NULL)
AND (("
join( " AND ",
map { "(biblio.title like ? OR biblio.title like ?)" } @data )
)
- . ") or biblio.isbn=? OR (aqorders.ordernumber=? AND aqorders.biblionumber=?)) ";
+ . ") or biblioitems.isbn=? OR (aqorders.ordernumber=? AND aqorders.biblionumber=?)) ";
}
$query .= " GROUP BY aqorders.ordernumber";
+ ### $query
my $sth = $dbh->prepare($query);
$sth->execute(@searchterms);
my @results = ();
-
-
-
+ my $query2 = "
+ SELECT *
+ FROM biblio
+ WHERE biblionumber=?
+ ";
+ my $sth2 = $dbh->prepare($query2);
my $query3 = "
SELECT *
FROM aqorderbreakdown
my $sth3 = $dbh->prepare($query3);
while ( my $data = $sth->fetchrow_hashref ) {
-## Retrieving a whole marc record just to extract seriestitle is very poor performance
-## Rewrite these searches
-my $record=MARCgetbiblio($dbh,$data->{'biblionumber'});
-my $data2=MARCmarc2koha($dbh,$record,"biblios");
-
+ $sth2->execute( $data->{'biblionumber'} );
+ my $data2 = $sth2->fetchrow_hashref;
$data->{'author'} = $data2->{'author'};
$data->{'seriestitle'} = $data2->{'seriestitle'};
$sth3->execute( $data->{'ordernumber'} );
$data->{'bookfundid'} = $data3->{'bookfundid'};
push( @results, $data );
}
+ ### @results
$sth->finish;
-
+ $sth2->finish;
$sth3->finish;
return @results;
}
=cut
sub DelOrder {
- my ( $biblionumber, $ordnum ) = @_;
+ my ( $bibnum, $ordnum ) = @_;
my $dbh = C4::Context->dbh;
my $query = "
UPDATE aqorders
WHERE biblionumber=? AND ordernumber=?
";
my $sth = $dbh->prepare($query);
- $sth->execute( $biblionumber, $ordnum );
+ $sth->execute( $bibnum, $ordnum );
$sth->finish;
}
=back
-=back
-
=head2 FUNCTIONS ABOUT PARCELS
=over 2
bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders.
C<@results> is an array of references-to-hash. The keys of each element are fields from
-the aqorders, biblio tables of the Koha database.
+the aqorders, biblio, and biblioitems tables of the Koha database.
C<@results> is sorted alphabetically by book title.
=cut
sub GetParcel {
-
#gets all orders from a certain supplier, orders them alphabetically
my ( $supplierid, $code, $datereceived ) = @_;
my $dbh = C4::Context->dbh;
aqorders.listprice,
aqorders.rrp,
aqorders.ecost
- FROM aqorders,aqbasket
+ FROM aqorders
+ LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
- WHERE aqbasket.basketno=aqorders.basketno
- AND aqbasket.booksellerid=?
+ WHERE
+ aqbasket.booksellerid=?
AND aqorders.booksellerinvoicenumber LIKE \"$code\"
AND aqorders.datereceived= \'$datereceived\'";
my $userenv = C4::Context->userenv;
if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
$strsth .=
- " and (borrowers.branchcode = '"
+ " AND (borrowers.branchcode = '"
. $userenv->{branch}
- . "' or borrowers.branchcode ='')";
+ . "' OR borrowers.branchcode ='')";
}
}
- $strsth .= " order by aqbasket.basketno";
+ $strsth .= " ORDER BY aqbasket.basketno";
### parcelinformation : $strsth
+ warn "STH : $strsth";
my $sth = $dbh->prepare($strsth);
$sth->execute($supplierid);
while ( my $data = $sth->fetchrow_hashref ) {
push( @results, $data );
}
- ### countparcelbiblio: $count
+ ### countparcelbiblio: scalar(@results)
$sth->finish;
return @results;
count(DISTINCT biblionumber) AS biblio,
sum(quantity) AS itemsexpected,
sum(quantityreceived) AS itemsreceived
- FROM aqorders, aqbasket
- WHERE aqbasket.basketno = aqorders.basketno
- AND aqbasket.booksellerid = $bookseller and datereceived IS NOT NULL
+ FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
+ WHERE aqbasket.booksellerid = $bookseller and datereceived IS NOT NULL
";
$strsth .= "and aqorders.booksellerinvoicenumber like \"$code%\" " if ($code);
$strsth .= "group by aqorders.booksellerinvoicenumber,datereceived ";
$strsth .= "order by $order " if ($order);
+### $strsth
my $sth = $dbh->prepare($strsth);
$sth->execute;
- my @results;
-
- while ( my $data2 = $sth->fetchrow_hashref ) {
- push @results, $data2;
- }
-
+ my $results = $sth->fetchall_arrayref({});
$sth->finish;
- return @results;
+ return @$results;
}
#------------------------------------------------------------#
=cut
sub GetLateOrders {
-## requirse fixing for KOHA 3 API. Currently does not return publisher
my $delay = shift;
my $supplierid = shift;
my $branch = shift;
# warn " $dbdriver";
if ( $dbdriver eq "mysql" ) {
$strsth = "
- SELECT aqbasket.basketno,
+ SELECT aqbasket.basketno,aqorders.ordernumber,
DATE(aqbasket.closedate) AS orderdate,
aqorders.quantity - IFNULL(aqorders.quantityreceived,0) AS quantity,
aqorders.rrp AS unitpricesupplier,
aqbooksellers.name AS supplier,
aqorders.title,
biblio.author,
-
+ biblioitems.publishercode AS publisher,
+ biblioitems.publicationyear,
DATEDIFF(CURDATE( ),closedate) AS latesince
- 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)
aqbooksellers.name AS supplier,
biblio.title,
biblio.author,
-
+ biblioitems.publishercode AS publisher,
+ biblioitems.publicationyear,
(CURDATE - closedate) AS latesince
- 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
aqorders.quantity,
aqorders.quantityreceived,
aqorders.ecost,
- aqorders.ordernumber
- FROM aqorders,aqbasket,aqbooksellers,biblio";
-
- $query .= ",borrowers "
- if ( C4::Context->preference("IndependantBranches") );
-
- $query .="
- WHERE aqorders.basketno=aqbasket.basketno
- AND aqbasket.booksellerid=aqbooksellers.id
- AND biblio.biblionumber=aqorders.biblionumber ";
-
- $query .= " AND aqbasket.authorisedby=borrowers.borrowernumber"
+ aqorders.ordernumber,
+ aqorders.booksellerinvoicenumber as invoicenumber,
+ aqbooksellers.id as id,
+ aqorders.biblionumber
+ FROM aqorders
+ LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
+ 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 1 ";
$query .= " AND biblio.title LIKE " . $dbh->quote( "%" . $title . "%" )
if $title;
$query .= " AND creationdate<" . $dbh->quote($to_placed_on)
if $to_placed_on;
+ $query .= " AND (datecancellationprinted is NULL or datecancellationprinted='0000-00-00')";
if ( C4::Context->preference("IndependantBranches") ) {
my $userenv = C4::Context->userenv;
return \@order_loop, $total_qty, $total_price, $total_qtyreceived;
}
+=head2 GetRecentAcqui
+
+ $results = GetRecentAcqui($days);
+
+ C<$results> is a ref to a table which containts hashref
+
+=cut
+
+sub GetRecentAcqui {
+ my $limit = shift;
+ my $dbh = C4::Context->dbh;
+ my $query = "
+ SELECT *
+ FROM biblio
+ ORDER BY timestamp DESC
+ LIMIT 0,".$limit;
+
+ my $sth = $dbh->prepare($query);
+ $sth->execute;
+ my @results;
+ while(my $data = $sth->fetchrow_hashref){
+ push @results,$data;
+ }
+ return \@results;
+}
+
END { } # module clean-up code here (global destructor)
1;
projects / koha_fer / blobdiff