# 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
);
=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.*,
+ my $query ="
+ SELECT aqorderbreakdown.*,
biblio.*,biblioitems.*,
aqorders.*,
+ aqbookfund.bookfundname,
biblio.title
- FROM aqorders,biblio,biblioitems
- 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 biblioitems.biblioitemnumber=aqorders.biblioitemnumber
AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
";
$sth->execute($basketno);
my @results;
- # print $query;
while ( my $data = $sth->fetchrow_hashref ) {
push @results, $data;
}
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);
#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=?
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;