# 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
@ISA = qw(Exporter);
@EXPORT = qw(
- &GetBasket &GetBasketContent &NewBasket &CloseBasket
- &GetPendingOrders &GetAllOrders
- &GetOrder &GetLateOrders &NewOrder &DelOrder
- &SearchOrder &GetHistory
- &ModOrder &GetSingleOrder &ModReceiveOrder
+ &GetBasket &NewBasket &CloseBasket
+ &GetPendingOrders &GetOrder &GetOrders
+ &GetOrderNumber &GetLateOrders &NewOrder &DelOrder
+ &SearchOrder &GetHistory &GetRecentAcqui
+ &ModOrder &ModReceiveOrder &ModOrderBiblioNumber
&GetParcels &GetParcel
);
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 );
-}
-
-#------------------------------------------------------------#
-
-=head3 GetBasketContent
-
-=over 4
-
-@orders = &GetBasketContent($basketnumber, $orderby);
-
-Looks up the pending (non-cancelled) orders with the given basket
-number. If C<$booksellerID> is non-empty, only orders from that seller
-are returned.
-
-return :
-C<&basket> returns a two-element array. C<@orders> is an array of
-references-to-hash, whose keys are the fields from the aqorders,
-biblio, and biblioitems tables in the Koha database.
-
-=back
-
-=cut
-
-sub GetBasketContent {
- my ( $basketno, $orderby ) = @_;
- my $dbh = C4::Context->dbh;
- my $query ="
- SELECT aqorderbreakdown.*,
- biblio.*,biblioitems.*,
- aqorders.*,
- biblio.title
- FROM aqorders,biblio,biblioitems
- LEFT JOIN aqorderbreakdown ON
- aqorders.ordernumber=aqorderbreakdown.ordernumber
- WHERE basketno=?
- AND biblio.biblionumber=aqorders.biblionumber
- AND biblioitems.biblioitemnumber=aqorders.biblioitemnumber
- AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
- ";
-
- $orderby = "biblioitems.publishercode" unless $orderby;
- $query .= " ORDER BY $orderby";
- my $sth = $dbh->prepare($query);
- $sth->execute($basketno);
- my @results;
-
- my $i=0;
- # print $query;
- while ( my $data = $sth->fetchrow_hashref ) {
- $results[$i++] = $data;
- }
- $sth->finish;
- return @results;
+ 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 = ();
+ my $results = $sth->fetchall_arrayref({});
+ $sth->finish;
+ return $results;
+}
+
+#------------------------------------------------------------#
+
+=head3 GetOrders
+
+=over 4
+
+@orders = &GetOrders($basketnumber, $orderby);
+
+Looks up the pending (non-cancelled) orders with the given basket
+number. If C<$booksellerID> is non-empty, only orders from that seller
+are returned.
+
+return :
+C<&basket> returns a two-element array. C<@orders> is an array of
+references-to-hash, whose keys are the fields from the aqorders,
+biblio, and biblioitems tables in the Koha database.
+
+=back
+
+=cut
+
+sub GetOrders {
+ my ( $basketno, $orderby ) = @_;
+ my $dbh = C4::Context->dbh;
+ my $query ="
+ SELECT aqorderbreakdown.*,
+ biblio.*,biblioitems.*,
+ aqorders.*,
+ aqbookfund.bookfundname,
+ biblio.title
+ 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 (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
+ ";
+
+ $orderby = "biblioitems.publishercode" unless $orderby;
+ $query .= " ORDER BY $orderby";
+ my $sth = $dbh->prepare($query);
+ $sth->execute($basketno);
+ my @results;
+
while ( my $data = $sth->fetchrow_hashref ) {
- push( @results, $data );
+ push @results, $data;
}
$sth->finish;
- return \@results;
+ return @results;
}
#------------------------------------------------------------#
-=head3 GetOrder
+=head3 GetOrderNumber
=over 4
-($order, $ordernumber) = &GetOrder($biblioitemnumber, $biblionumber);
+$ordernumber = &GetOrderNumber($biblioitemnumber, $biblionumber);
-Looks up the order with the given biblionumber and biblioitemnumber.
+Looks up the ordernumber with the given biblionumber and biblioitemnumber.
-Returns a two-element array.
+Returns the number of this order.
=item C<$ordernumber> is the order number.
-=item C<$order> is a reference-to-hash describing the order;
-its keys are fields from the biblio, biblioitems, aqorders, and aqorderbreakdown
-tables of the Koha database.
-
=back
=cut
-sub GetOrder {
+sub GetOrderNumber {
my ( $biblionumber,$biblioitemnumber ) = @_;
my $dbh = C4::Context->dbh;
my $query = "
my $sth = $dbh->prepare($query);
$sth->execute( $biblionumber, $biblioitemnumber );
- # FIXME - Use fetchrow_array(), since we're only interested in the one
- # value.
- my $ordnum = $sth->fetchrow_hashref;
- $sth->finish;
- my $order = GetSingleOrder( $ordnum->{'ordernumber'} );
- return ( $order, $ordnum->{'ordernumber'} );
+ return $sth->fetchrow;
}
#------------------------------------------------------------#
-=head3 GetSingleOrder
+=head3 GetOrder
=over 4
-$order = &GetSingleOrder($ordernumber);
+$order = &GetOrder($ordernumber);
Looks up an order by order number.
=cut
-sub GetSingleOrder {
+sub GetOrder {
my ($ordnum) = @_;
my $dbh = C4::Context->dbh;
my $query = "
SELECT *
- FROM biblio,biblioitems,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
- AND biblioitems.biblioitemnumber=aqorders.biblioitemnumber
+
";
my $sth= $dbh->prepare($query);
$sth->execute($ordnum);
#------------------------------------------------------------#
-=head3 GetAllOrders
-
-=over 4
-
-@results = &GetAllOrders($booksellerid);
-
-Looks up all of the pending orders from the supplier with the given
-bookseller ID. 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, and biblioitems tables of the Koha database.
-
-C<@results> is sorted alphabetically by book title.
-
-=back
-
-=cut
-
-sub GetAllOrders {
-
- #gets all orders from a certain supplier, orders them alphabetically
- my ($supplierid) = @_;
- my $dbh = C4::Context->dbh;
- my @results = ();
- my $strsth = "
- SELECT count(*),authorisedby,creationdate,aqbasket.basketno,
- closedate,surname,firstname,aqorders.biblionumber,aqorders.title, aqorders.ordernumber
- 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
- ";
-
- if ( C4::Context->preference("IndependantBranches") ) {
- my $userenv = C4::Context->userenv;
- if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
- $strsth .=
- " and (borrowers.branchcode = '"
- . $userenv->{branch}
- . "' or borrowers.branchcode ='')";
- }
- }
- $strsth .= " group by basketno order by aqbasket.basketno";
- my $sth = $dbh->prepare($strsth);
- $sth->execute($supplierid);
- while ( my $data = $sth->fetchrow_hashref ) {
- push( @results, $data );
- }
- $sth->finish;
- return @results;
-}
-
-#------------------------------------------------------------#
-
=head3 NewOrder
=over 4
#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 (?,?)
";
$sort2, $ordnum, $bibnum
);
$sth->finish;
- my $query = "
+ $query = "
UPDATE aqorderbreakdown
SET bookfundid=?
WHERE ordernumber=?
#------------------------------------------------------------#
+=head3 ModOrderBiblioNumber
+
+=over 4
+
+&ModOrderBiblioNumber($biblioitemnumber,$ordnum, $biblionumber);
+
+Modifies the biblioitemnumber for an existing order.
+Updates the order with order number C<$ordernum> and biblionumber C<$biblionumber>.
+
+=back
+
+=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 );
+}
+
+#------------------------------------------------------------#
+
=head3 ModReceiveOrder
=over 4
sub ModReceiveOrder {
my (
- $biblio, $ordnum, $quantrec, $user, $cost,
- $invoiceno, $freight, $rrp, $bookfund
+ $biblionumber, $ordnum, $quantrec, $user, $cost,
+ $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=?
";
my $sth = $dbh->prepare($query);
- my $suggestionid = GetSuggestionFromBiblionumber( $dbh, $biblio );
+ my $suggestionid = GetSuggestionFromBiblionumber( $dbh, $biblionumber );
if ($suggestionid) {
- ModStatus( $suggestionid, 'AVAILABLE', '', $biblio );
+ ModStatus( $suggestionid, 'AVAILABLE', '', $biblionumber );
}
- $sth->execute( $quantrec, $invoiceno, $cost, $freight, $rrp, $biblio,
- $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 {
- 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,biblioitems,biblio,aqbasket
- WHERE aqorders.biblioitemnumber = biblioitems.biblioitemnumber AND
- aqorders.basketno = aqbasket.basketno
- AND aqbasket.booksellerid = ?
- AND biblio.biblionumber=aqorders.biblionumber
+ "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 (("
else {
$query =
" SELECT *,biblio.title
- FROM aqorders,biblioitems,biblio,aqbasket
- WHERE aqorders.biblioitemnumber = biblioitems.biblioitemnumber
- AND aqorders.basketno = aqbasket.basketno
- AND biblio.biblionumber=aqorders.biblionumber
- 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 (("
. ") 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 = ();
$data->{'bookfundid'} = $data3->{'bookfundid'};
push( @results, $data );
}
+ ### @results
$sth->finish;
$sth2->finish;
$sth3->finish;
=back
-=back
-
=head2 FUNCTIONS ABOUT PARCELS
=over 2
=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;
}
#------------------------------------------------------------#
# 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,
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