use strict;
+use warnings;
use C4::Context;
use C4::Debug;
use C4::Dates qw(format_date format_date_in_iso);
use MARC::Record;
use C4::Suggestions;
use C4::Debug;
+use C4::SQLHelper qw(InsertInTable);
use Time::localtime;
use HTML::Entities;
use vars qw($VERSION @ISA @EXPORT);
BEGIN {
- # set the version for version checking
- $VERSION = 3.01;
- require Exporter;
- @ISA = qw(Exporter);
- @EXPORT = qw(
- &GetBasket &NewBasket &CloseBasket &CloseBasketgroup &ReOpenBasketgroup &DelBasket &ModBasket
- &ModBasketHeader &GetBasketsByBookseller &GetBasketsByBasketgroup
- &ModBasketgroup &NewBasketgroup &DelBasketgroup &GetBasketgroup
- &GetBasketgroups
-
- &GetPendingOrders &GetOrder &GetOrders
- &GetOrderNumber &GetLateOrders &NewOrder &DelOrder
- &SearchOrder &GetHistory &GetRecentAcqui
- &ModOrder &ModOrderItem &ModReceiveOrder &ModOrderBiblioitemNumber
-
- &NewOrderItem
-
- &GetParcels &GetParcel
- &GetContracts &GetContract
-
- &GetOrderFromItemnumber
+ # set the version for version checking
+ $VERSION = 3.01;
+ require Exporter;
+ @ISA = qw(Exporter);
+ @EXPORT = qw(
+ &GetBasket &NewBasket &CloseBasket &DelBasket &ModBasket
+ &GetBasketsByBookseller &GetBasketsByBasketgroup
+
+ &ModBasketHeader
+
+ &ModBasketgroup &NewBasketgroup &DelBasketgroup &GetBasketgroup &CloseBasketgroup
+ &GetBasketgroups &ReOpenBasketgroup
+
+ &NewOrder &DelOrder &ModOrder &GetPendingOrders &GetOrder &GetOrders
+ &GetOrderNumber &GetLateOrders &GetOrderFromItemnumber
+ &SearchOrder &GetHistory &GetRecentAcqui
+ &ModReceiveOrder &ModOrderBiblioitemNumber
+
+ &NewOrderItem &ModOrderItem
+
+ &GetParcels &GetParcel
+ &GetContracts &GetContract
+
&GetItemnumbersFromOrder
- );
+ );
}
$sth->execute($itemnumber);
my $order = $sth->fetchrow_hashref;
- return ( $order );
+ return ( $order );
}
my @tab;
while (my $order = $sth->fetchrow_hashref) {
- push @tab, $order->{'itemnumber'};
+ push @tab, $order->{'itemnumber'};
}
return @tab;
my $sth=$dbh->prepare($query);
$sth->execute($basketno);
my $basket = $sth->fetchrow_hashref;
- return ( $basket );
+ return ( $basket );
}
#------------------------------------------------------------#
VALUES (now(),'$booksellerid','$authorisedby')
";
my $sth =
- $dbh->do($query);
+ $dbh->do($query);
#find & return basketno MYSQL dependant, but $dbh->last_insert_id always returns null :-(
my $basket = $dbh->{'mysql_insertid'};
ModBasketHeader($basket, $basketname || '', $basketnote || '', $basketbooksellernote || '', $basketcontractnumber || undef);
=item C<$extra> is the extra sql parameters, can be
- - $extra->{groupby}: group baskets by column
- ex. $extra->{groupby} = aqbasket.basketgroupid
- - $extra->{orderby}: order baskets by column
- - $extra->{limit}: limit number of results (can be helpful for pagination)
+- $extra->{groupby}: group baskets by column
+ ex. $extra->{groupby} = aqbasket.basketgroupid
+- $extra->{orderby}: order baskets by column
+- $extra->{limit}: limit number of results (can be helpful for pagination)
=back
$hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
+$hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
+
+$hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
+
$hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
=back
die "booksellerid is required to create a basketgroup" unless $basketgroupinfo->{'booksellerid'};
my $query = "INSERT INTO aqbasketgroups (";
my @params;
- foreach my $field ('name', 'closed') {
+ foreach my $field ('name', 'deliveryplace', 'deliverycomment', 'closed') {
if ( $basketgroupinfo->{$field} ) {
$query .= "$field, ";
push(@params, $basketgroupinfo->{$field});
$hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
+$hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
+
+$hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
+
+$hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
+
$hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
=back
my $dbh = C4::Context->dbh;
my $query = "UPDATE aqbasketgroups SET ";
my @params;
- foreach my $field (qw(name closed)) {
- if ( $basketgroupinfo->{$field} ne undef) {
+ foreach my $field (qw(name billingplace deliveryplace deliverycomment closed)) {
+ if ( defined $basketgroupinfo->{$field} ) {
$query .= "$field=?, ";
push(@params, $basketgroupinfo->{$field});
}
if ( C4::Context->preference("IndependantBranches") ) {
if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
$strsth .= " and (borrowers.branchcode = ?
- or borrowers.branchcode = '')";
+ or borrowers.branchcode = '')";
push @query_params, $userenv->{branch};
}
}
my ( $basketno, $orderby ) = @_;
my $dbh = C4::Context->dbh;
my $query ="
- SELECT biblio.*,biblioitems.*,
+ SELECT biblio.*,biblioitems.*,
aqorders.*,
aqbudgets.*,
biblio.title
=cut
sub GetOrder {
- my ($ordnum) = @_;
+ my ($ordernumber) = @_;
my $dbh = C4::Context->dbh;
my $query = "
SELECT biblioitems.*, biblio.*, aqorders.*
";
my $sth= $dbh->prepare($query);
- $sth->execute($ordnum);
+ $sth->execute($ordernumber);
my $data = $sth->fetchrow_hashref;
$sth->finish;
return $data;
=item $hashref->{'basketno'} is the basketno foreign key in aqorders, it is mandatory
-=item $hashref->{'ordnum'} is a "minimum order number."
+=item $hashref->{'ordernumber'} is a "minimum order number."
=item $hashref->{'budgetdate'} is effectively ignored.
- If it's undef (anything false) or the string 'now', the current day is used.
- Else, the upcoming July 1st is used.
+If it's undef (anything false) or the string 'now', the current day is used.
+Else, the upcoming July 1st is used.
=item $hashref->{'subscription'} may be either "yes", or anything else for "no".
=item $hashref->{'uncertainprice'} may be 0 for "the price is known" or 1 for "the price is uncertain"
+=item defaults entrydate to Now
+
The following keys are used: "biblionumber", "title", "basketno", "quantity", "notes", "biblioitemnumber", "rrp", "ecost", "gst", "unitprice", "subscription", "sort1", "sort2", "booksellerinvoicenumber", "listprice", "budgetdate", "purchaseordernumber", "branchcode", "booksellerinvoicenumber", "bookfundid".
=back
} else {
$orderinfo->{'subscription'} = 0;
}
+ $orderinfo->{'entrydate'} ||= C4::Dates->new()->output("iso");
- my $query = "INSERT INTO aqorders (";
- foreach my $orderinfokey (keys %{$orderinfo}) {
- next if $orderinfokey =~ m/branchcode|entrydate/; # skip branchcode and entrydate, branchcode isnt a vaild col, entrydate we add manually with NOW()
- $query .= "$orderinfokey,";
- push(@params, $orderinfo->{$orderinfokey});
- }
-
- $query .= "entrydate) VALUES (";
- foreach (@params) {
- $query .= "?,";
- }
- $query .= " NOW() )"; #ADDING CURRENT DATE TO 'budgetdate, entrydate, purchaseordernumber'...
-
- my $sth = $dbh->prepare($query);
-
- $sth->execute(@params);
- $sth->finish;
-
- #get ordnum MYSQL dependant, but $dbh->last_insert_id returns null
- my $ordnum = $dbh->{'mysql_insertid'};
-
- $sth->finish;
- return ( $orderinfo->{'basketno'}, $ordnum );
+ my $ordernumber=InsertInTable("aqorders",$orderinfo);
+ return ( $orderinfo->{'basketno'}, $ordernumber );
}
=cut
sub NewOrderItem {
- #my ($biblioitemnumber,$ordnum, $biblionumber) = @_;
+ #my ($biblioitemnumber,$ordernumber, $biblionumber) = @_;
my ($itemnumber, $ordernumber) = @_;
my $dbh = C4::Context->dbh;
my $query = qq|
=over 2
Modifies the itemnumber in the aqorders_items table. The input hash needs three entities:
- - itemnumber: the old itemnumber
- - ordernumber: the order this item is attached to
- - newitemnumber: the new itemnumber we want to attach the line to
+- itemnumber: the old itemnumber
+- ordernumber: the order this item is attached to
+- newitemnumber: the new itemnumber we want to attach the line to
=back
=over 4
-&ModOrderBiblioitemNumber($biblioitemnumber,$ordnum, $biblionumber);
+&ModOrderBiblioitemNumber($biblioitemnumber,$ordernumber, $biblionumber);
Modifies the biblioitemnumber for an existing order.
Updates the order with order number C<$ordernum> and biblionumber C<$biblionumber>.
#FIXME: is this used at all?
sub ModOrderBiblioitemNumber {
- my ($biblioitemnumber,$ordnum, $biblionumber) = @_;
+ my ($biblioitemnumber,$ordernumber, $biblionumber) = @_;
my $dbh = C4::Context->dbh;
my $query = "
- UPDATE aqorders
- SET biblioitemnumber = ?
- WHERE ordernumber = ?
- AND biblionumber = ?";
+ UPDATE aqorders
+ SET biblioitemnumber = ?
+ WHERE ordernumber = ?
+ AND biblionumber = ?";
my $sth = $dbh->prepare($query);
- $sth->execute( $biblioitemnumber, $ordnum, $biblionumber );
+ $sth->execute( $biblioitemnumber, $ordernumber, $biblionumber );
}
#------------------------------------------------------------#
sub ModReceiveOrder {
my (
- $biblionumber, $ordnum, $quantrec, $user, $cost,
+ $biblionumber, $ordernumber, $quantrec, $user, $cost,
$invoiceno, $freight, $rrp, $budget_id, $datereceived
- )
- = @_;
+ )
+ = @_;
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;
+ $datereceived = C4::Dates->output('iso') unless $datereceived;
my $suggestionid = GetSuggestionFromBiblionumber( $dbh, $biblionumber );
if ($suggestionid) {
ModStatus( $suggestionid, 'AVAILABLE', '', $biblionumber );
}
- my $sth=$dbh->prepare("
+ my $sth=$dbh->prepare("
SELECT * FROM aqorders
- WHERE biblionumber=? AND aqorders.ordernumber=?");
+ WHERE biblionumber=? AND aqorders.ordernumber=?");
- $sth->execute($biblionumber,$ordnum);
+ $sth->execute($biblionumber,$ordernumber);
my $order = $sth->fetchrow_hashref();
$sth->finish();
- if ( $order->{quantity} > $quantrec ) {
+ if ( $order->{quantity} > $quantrec ) {
$sth=$dbh->prepare("
UPDATE aqorders
SET quantityreceived=?
, quantityreceived=?
WHERE biblionumber=? AND ordernumber=?");
- $sth->execute($quantrec,$datereceived,$invoiceno,$cost,$freight,$rrp,$quantrec,$biblionumber,$ordnum);
+ $sth->execute($quantrec,$datereceived,$invoiceno,$cost,$freight,$rrp,$quantrec,$biblionumber,$ordernumber);
$sth->finish;
# create a new order for the remaining items, and set its bookfund.
delete($order->{'$orderkey'});
}
my $newOrder = NewOrder($order);
- } else {
+} else {
$sth=$dbh->prepare("update aqorders
- set quantityreceived=?,datereceived=?,booksellerinvoicenumber=?,
- unitprice=?,freight=?,rrp=?
+ set quantityreceived=?,datereceived=?,booksellerinvoicenumber=?,
+ unitprice=?,freight=?,rrp=?
where biblionumber=? and ordernumber=?");
- $sth->execute($quantrec,$datereceived,$invoiceno,$cost,$freight,$rrp,$biblionumber,$ordnum);
+ $sth->execute($quantrec,$datereceived,$invoiceno,$cost,$freight,$rrp,$biblionumber,$ordernumber);
$sth->finish;
}
return $datereceived;
=cut
sub DelOrder {
- my ( $bibnum, $ordnum ) = @_;
+ my ( $bibnum, $ordernumber ) = @_;
my $dbh = C4::Context->dbh;
my $query = "
UPDATE aqorders
WHERE biblionumber=? AND ordernumber=?
";
my $sth = $dbh->prepare($query);
- $sth->execute( $bibnum, $ordnum );
+ $sth->execute( $bibnum, $ordernumber );
$sth->finish;
}
my $dbh = C4::Context->dbh;
my @results = ();
$code .= '%'
- if $code; # add % if we search on a given code (otherwise, let him empty)
+ if $code; # add % if we search on a given code (otherwise, let him empty)
my $strsth ="
SELECT authorisedby,
creationdate,
my $userenv = C4::Context->userenv;
if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
$strsth .= " and (borrowers.branchcode = ?
- or borrowers.branchcode = '')";
+ or borrowers.branchcode = '')";
push @query_params, $userenv->{branch};
}
}
my $dbdriver = C4::Context->config("db_scheme") || "mysql";
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,
- aqbudgets.budget_name AS budget,
- borrowers.branchcode AS branch,
- aqbooksellers.name AS supplier,
- 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 aqbudgets ON aqorders.budget_id = aqbudgets.budget_id),
- (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 $select = "
+ SELECT aqbasket.basketno,
+ aqorders.ordernumber,
+ DATE(aqbasket.closedate) AS orderdate,
+ aqorders.rrp AS unitpricesupplier,
+ aqorders.ecost AS unitpricelib,
+ aqbudgets.budget_name AS budget,
+ borrowers.branchcode AS branch,
+ aqbooksellers.name AS supplier,
+ 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 aqbudgets ON aqorders.budget_id = aqbudgets.budget_id),
+ (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 = "";
+ 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
- ";
+ $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
+ $having = "
+ HAVING quantity <> 0
AND unitpricesupplier <> 0
AND unitpricelib <> 0
- ";
+ ";
} else {
- # FIXME: account for IFNULL as above
+ # 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 = ? ';
+ $from .= ' AND aqbasket.booksellerid = ? ';
push @query_params, $supplierid;
}
if (defined $branch) {
push @query_params, $branch;
}
if (C4::Context->preference("IndependantBranches")
- && C4::Context->userenv
- && C4::Context->userenv->{flags} != 1 ) {
+ && C4::Context->userenv
+ && C4::Context->userenv->{flags} != 1 ) {
$from .= ' AND borrowers.branchcode LIKE ? ';
push @query_params, C4::Context->userenv->{branch};
}
- 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 $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;
(\@order_loop, $total_qty, $total_price, $total_qtyreceived) = GetHistory( $title, $author, $name, $from_placed_on, $to_placed_on );
- Retreives some acquisition history information
+Retreives some acquisition history information
- returns:
+returns:
$order_loop is a list of hashrefs that each look like this:
- {
+ {
'author' => 'Twain, Mark',
'basketno' => '1',
'biblionumber' => '215',
'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
LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber";
$query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber"
- if ( C4::Context->preference("IndependantBranches") );
+ if ( C4::Context->preference("IndependantBranches") );
$query .= " WHERE (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') ";
=head2 GetRecentAcqui
- $results = GetRecentAcqui($days);
+$results = GetRecentAcqui($days);
- C<$results> is a ref to a table which containts hashref
+C<$results> is a ref to a table which containts hashref
=cut