use C4::Biblio;
use C4::Debug;
use C4::SQLHelper qw(InsertInTable);
+use C4::Bookseller qw(GetBookSellerFromId);
+use C4::Templates qw(gettemplate);
use Time::localtime;
use HTML::Entities;
BEGIN {
# set the version for version checking
- $VERSION = 3.01;
+ $VERSION = 3.07.00.049;
require Exporter;
@ISA = qw(Exporter);
@EXPORT = qw(
&GetBasket &NewBasket &CloseBasket &DelBasket &ModBasket
- &GetBasketAsCSV
+ &GetBasketAsCSV &GetBasketGroupAsCSV
&GetBasketsByBookseller &GetBasketsByBasketgroup
+ &GetBasketsInfosByBookseller
&ModBasketHeader
&ModBasketgroup &NewBasketgroup &DelBasketgroup &GetBasketgroup &CloseBasketgroup
&GetBasketgroups &ReOpenBasketgroup
- &NewOrder &DelOrder &ModOrder &GetPendingOrders &GetOrder &GetOrders
- &GetOrderNumber &GetLateOrders &GetOrderFromItemnumber
+ &NewOrder &DelOrder &ModOrder &GetPendingOrders &GetOrder &GetOrders &GetOrdersByBiblionumber
+ &GetLateOrders &GetOrderFromItemnumber
&SearchOrder &GetHistory &GetRecentAcqui
- &ModReceiveOrder &ModOrderBiblioitemNumber
-
- &NewOrderItem &ModOrderItem
+ &ModReceiveOrder &CancelReceipt
+ &GetCancelledOrders
+ &GetLastOrderNotReceivedFromSubscriptionid &GetLastOrderReceivedFromSubscriptionid
+ &NewOrderItem &ModOrderItem &ModItemOrder
&GetParcels &GetParcel
&GetContracts &GetContract
+ &GetInvoices
+ &GetInvoice
+ &GetInvoiceDetails
+ &AddInvoice
+ &ModInvoice
+ &CloseInvoice
+ &ReopenInvoice
+
&GetItemnumbersFromOrder
+
+ &AddClaim
);
}
=head3 NewBasket
$basket = &NewBasket( $booksellerid, $authorizedby, $basketname,
- $basketnote, $basketbooksellernote, $basketcontractnumber );
+ $basketnote, $basketbooksellernote, $basketcontractnumber, $deliveryplace, $billingplace );
Create a new basket in aqbasket table
=cut
-# FIXME : this function seems to be unused.
-
sub NewBasket {
- my ( $booksellerid, $authorisedby, $basketname, $basketnote, $basketbooksellernote, $basketcontractnumber ) = @_;
+ my ( $booksellerid, $authorisedby, $basketname, $basketnote,
+ $basketbooksellernote, $basketcontractnumber, $deliveryplace,
+ $billingplace ) = @_;
my $dbh = C4::Context->dbh;
- my $query = "
- INSERT INTO aqbasket
- (creationdate,booksellerid,authorisedby)
- VALUES (now(),'$booksellerid','$authorisedby')
- ";
- my $sth =
- $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);
+ my $query =
+ 'INSERT INTO aqbasket (creationdate,booksellerid,authorisedby) '
+ . 'VALUES (now(),?,?)';
+ $dbh->do( $query, {}, $booksellerid, $authorisedby );
+
+ my $basket = $dbh->{mysql_insertid};
+ $basketname ||= q{}; # default to empty strings
+ $basketnote ||= q{};
+ $basketbooksellernote ||= q{};
+ ModBasketHeader( $basket, $basketname, $basketnote, $basketbooksellernote,
+ $basketcontractnumber, $booksellerid, $deliveryplace, $billingplace );
return $basket;
}
Export a basket as CSV
+$cgi parameter is needed for column name translation
+
=cut
sub GetBasketAsCSV {
- my ($basketno) = @_;
+ my ($basketno, $cgi) = @_;
my $basket = GetBasket($basketno);
my @orders = GetOrders($basketno);
my $contract = GetContract($basket->{'contractnumber'});
- my $csv = Text::CSV->new();
- my $output;
- # TODO: Translate headers
- my @headers = qw(contractname ordernumber entrydate isbn author title publishercode collectiontitle notes quantity rrp);
-
- $csv->combine(@headers);
- $output = $csv->string() . "\n";
+ my $template = C4::Templates::gettemplate("acqui/csv/basket.tmpl", "intranet", $cgi);
my @rows;
foreach my $order (@orders) {
- my @cols;
- # newlines are not valid characters for Text::CSV combine()
- $order->{'notes'} =~ s/[\r\n]+//g;
- push(@cols,
- $contract->{'contractname'},
- $order->{'ordernumber'},
- $order->{'entrydate'},
- $order->{'isbn'},
- $order->{'author'},
- $order->{'title'},
- $order->{'publishercode'},
- $order->{'collectiontitle'},
- $order->{'notes'},
- $order->{'quantity'},
- $order->{'rrp'},
- );
- push (@rows, \@cols);
- }
-
- foreach my $row (@rows) {
- $csv->combine(@$row);
- $output .= $csv->string() . "\n";
-
- }
-
- return $output;
+ my $bd = GetBiblioData( $order->{'biblionumber'} );
+ my $row = {
+ contractname => $contract->{'contractname'},
+ ordernumber => $order->{'ordernumber'},
+ entrydate => $order->{'entrydate'},
+ isbn => $order->{'isbn'},
+ author => $bd->{'author'},
+ title => $bd->{'title'},
+ publicationyear => $bd->{'publicationyear'},
+ publishercode => $bd->{'publishercode'},
+ collectiontitle => $bd->{'collectiontitle'},
+ notes => $order->{'notes'},
+ quantity => $order->{'quantity'},
+ rrp => $order->{'rrp'},
+ deliveryplace => C4::Branch::GetBranchName( $basket->{'deliveryplace'} ),
+ billingplace => C4::Branch::GetBranchName( $basket->{'billingplace'} ),
+ };
+ foreach(qw(
+ contractname author title publishercode collectiontitle notes
+ deliveryplace billingplace
+ ) ) {
+ # Double the quotes to not be interpreted as a field end
+ $row->{$_} =~ s/"/""/g if $row->{$_};
+ }
+ push @rows, $row;
+ }
+
+ @rows = sort {
+ if(defined $a->{publishercode} and defined $b->{publishercode}) {
+ $a->{publishercode} cmp $b->{publishercode};
+ }
+ } @rows;
+ $template->param(rows => \@rows);
+
+ return $template->output;
}
+=head3 GetBasketGroupAsCSV
+
+=over 4
+
+&GetBasketGroupAsCSV($basketgroupid);
+
+Export a basket group as CSV
+
+$cgi parameter is needed for column name translation
+
+=back
+
+=cut
+
+sub GetBasketGroupAsCSV {
+ my ($basketgroupid, $cgi) = @_;
+ my $baskets = GetBasketsByBasketgroup($basketgroupid);
+
+ my $template = C4::Templates::gettemplate('acqui/csv/basketgroup.tmpl', 'intranet', $cgi);
+
+ my @rows;
+ for my $basket (@$baskets) {
+ my @orders = GetOrders( $$basket{basketno} );
+ my $contract = GetContract( $$basket{contractnumber} );
+ my $bookseller = GetBookSellerFromId( $$basket{booksellerid} );
+ my $basketgroup = GetBasketgroup( $$basket{basketgroupid} );
+
+ foreach my $order (@orders) {
+ my $bd = GetBiblioData( $order->{'biblionumber'} );
+ my $row = {
+ clientnumber => $bookseller->{accountnumber},
+ basketname => $basket->{basketname},
+ ordernumber => $order->{ordernumber},
+ author => $bd->{author},
+ title => $bd->{title},
+ publishercode => $bd->{publishercode},
+ publicationyear => $bd->{publicationyear},
+ collectiontitle => $bd->{collectiontitle},
+ isbn => $order->{isbn},
+ quantity => $order->{quantity},
+ rrp => $order->{rrp},
+ discount => $bookseller->{discount},
+ ecost => $order->{ecost},
+ notes => $order->{notes},
+ entrydate => $order->{entrydate},
+ booksellername => $bookseller->{name},
+ bookselleraddress => $bookseller->{address1},
+ booksellerpostal => $bookseller->{postal},
+ contractnumber => $contract->{contractnumber},
+ contractname => $contract->{contractname},
+ basketgroupdeliveryplace => C4::Branch::GetBranchName( $basketgroup->{deliveryplace} ),
+ basketgroupbillingplace => C4::Branch::GetBranchName( $basketgroup->{billingplace} ),
+ basketdeliveryplace => C4::Branch::GetBranchName( $basket->{deliveryplace} ),
+ basketbillingplace => C4::Branch::GetBranchName( $basket->{billingplace} ),
+ };
+ foreach(qw(
+ basketname author title publishercode collectiontitle notes
+ booksellername bookselleraddress booksellerpostal contractname
+ basketgroupdeliveryplace basketgroupbillingplace
+ basketdeliveryplace basketbillingplace
+ ) ) {
+ # Double the quotes to not be interpreted as a field end
+ $row->{$_} =~ s/"/""/g if $row->{$_};
+ }
+ push @rows, $row;
+ }
+ }
+ $template->param(rows => \@rows);
+
+ return $template->output;
+
+}
+
=head3 CloseBasketgroup
&CloseBasketgroup($basketgroupno);
=head3 ModBasketHeader
- &ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber);
+ &ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid);
Modifies a basket's header.
=item C<$contractnumber> is the "contractnumber" (foreign) key in the "aqbasket" table.
+=item C<$booksellerid> is the id (foreign) key in the "aqbooksellers" table for the vendor.
+
+=item C<$deliveryplace> is the "deliveryplace" field in the aqbasket table.
+
+=item C<$billingplace> is the "billingplace" field in the aqbasket table.
+
=back
=cut
sub ModBasketHeader {
- my ($basketno, $basketname, $note, $booksellernote, $contractnumber) = @_;
- my $query = "UPDATE aqbasket SET basketname=?, note=?, booksellernote=? WHERE basketno=?";
+ my ($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid, $deliveryplace, $billingplace) = @_;
+ my $query = qq{
+ UPDATE aqbasket
+ SET basketname=?, note=?, booksellernote=?, booksellerid=?, deliveryplace=?, billingplace=?
+ WHERE basketno=?
+ };
+
my $dbh = C4::Context->dbh;
my $sth = $dbh->prepare($query);
- $sth->execute($basketname,$note,$booksellernote,$basketno);
+ $sth->execute($basketname, $note, $booksellernote, $booksellerid, $deliveryplace, $billingplace, $basketno);
+
if ( $contractnumber ) {
my $query2 ="UPDATE aqbasket SET contractnumber=? WHERE basketno=?";
my $sth2 = $dbh->prepare($query2);
return $results
}
+=head3 GetBasketsInfosByBookseller
+
+ my $baskets = GetBasketsInfosByBookseller($supplierid, $allbaskets);
+
+The optional second parameter allbaskets is a boolean allowing you to
+select all baskets from the supplier; by default only active baskets (open or
+closed but still something to receive) are returned.
+
+Returns in a arrayref of hashref all about booksellers baskets, plus:
+ total_biblios: Number of distinct biblios in basket
+ total_items: Number of items in basket
+ expected_items: Number of non-received items in basket
+
+=cut
+
+sub GetBasketsInfosByBookseller {
+ my ($supplierid, $allbaskets) = @_;
+
+ return unless $supplierid;
+
+ my $dbh = C4::Context->dbh;
+ my $query = qq{
+ SELECT aqbasket.*,
+ SUM(aqorders.quantity) AS total_items,
+ COUNT(DISTINCT aqorders.biblionumber) AS total_biblios,
+ SUM(
+ IF(aqorders.datereceived IS NULL
+ AND aqorders.datecancellationprinted IS NULL
+ , aqorders.quantity
+ , 0)
+ ) AS expected_items
+ FROM aqbasket
+ LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
+ WHERE booksellerid = ?};
+ if(!$allbaskets) {
+ $query.=" AND (closedate IS NULL OR (aqorders.quantity > aqorders.quantityreceived AND datecancellationprinted IS NULL))";
+ }
+ $query.=" GROUP BY aqbasket.basketno";
+
+ my $sth = $dbh->prepare($query);
+ $sth->execute($supplierid);
+ return $sth->fetchall_arrayref({});
+}
+
+
#------------------------------------------------------------#
=head3 GetBasketsByBasketgroup
sub GetBasketsByBasketgroup {
my $basketgroupid = shift;
- my $query = "SELECT * FROM aqbasket
- LEFT JOIN aqcontract USING(contractnumber) WHERE basketgroupid=?";
+ my $query = qq{
+ SELECT *, aqbasket.booksellerid as booksellerid
+ FROM aqbasket
+ LEFT JOIN aqcontract USING(contractnumber) WHERE basketgroupid=?
+ };
my $dbh = C4::Context->dbh;
my $sth = $dbh->prepare($query);
$sth->execute($basketgroupid);
$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->{'freedeliveryplace'} is the 'freedeliveryplace' 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.
die "booksellerid is required to create a basketgroup" unless $basketgroupinfo->{'booksellerid'};
my $query = "INSERT INTO aqbasketgroups (";
my @params;
- foreach my $field ('name', 'deliveryplace', 'deliverycomment', 'closed') {
- if ( $basketgroupinfo->{$field} ) {
+ foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
+ if ( defined $basketgroupinfo->{$field} ) {
$query .= "$field, ";
push(@params, $basketgroupinfo->{$field});
}
$hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
+$hashref->{'freedeliveryplace'} is the 'freedeliveryplace' 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.
sub GetBasketgroups {
my $booksellerid = shift;
- die "bookseller id is required to edit a basketgroup" unless $booksellerid;
- my $query = "SELECT * FROM aqbasketgroups WHERE booksellerid=? ORDER BY `id` DESC";
+ die 'bookseller id is required to edit a basketgroup' unless $booksellerid;
+ my $query = 'SELECT * FROM aqbasketgroups WHERE booksellerid=? ORDER BY id DESC';
my $dbh = C4::Context->dbh;
my $sth = $dbh->prepare($query);
$sth->execute($booksellerid);
- my $results = $sth->fetchall_arrayref({});
- $sth->finish;
- return $results
+ return $sth->fetchall_arrayref({});
}
#------------------------------------------------------------#
=head3 GetPendingOrders
- $orders = &GetPendingOrders($booksellerid, $grouped, $owner);
+$orders = &GetPendingOrders($supplierid,$grouped,$owner,$basketno,$ordernumber,$search,$ean);
Finds pending orders from the bookseller with the given ID. Ignores
completed and cancelled orders.
C<$booksellerid> contains the bookseller identifier
-C<$grouped> contains 0 or 1. 0 means returns the list, 1 means return the total
C<$owner> contains 0 or 1. 0 means any owner. 1 means only the list of orders entered by the user itself.
-
-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
+C<$orders> is a reference-to-array; each element is a reference-to-hash.
-=over
+Used also by the filter in parcel.pl
+I have added:
-=item C<authorizedby>
-
-=item C<entrydate>
-
-=item C<basketno>
-
-=back
+C<$ordernumber>
+C<$search>
+C<$ean>
These give the value of the corresponding field in the aqorders table
of the Koha database.
=cut
sub GetPendingOrders {
- my ($supplierid,$grouped,$owner,$basketno) = @_;
+ my ($supplierid,$grouped,$owner,$basketno,$ordernumber,$search,$ean) = @_;
my $dbh = C4::Context->dbh;
my $strsth = "
- SELECT ".($grouped?"count(*),":"")."aqbasket.basketno,
- surname,firstname,aqorders.*,biblio.*,biblioitems.isbn,
- aqbasket.closedate, aqbasket.creationdate, aqbasket.basketname
- FROM aqorders
+ SELECT ".($grouped?"count(*),":"")."aqbasket.basketno,
+ surname,firstname,biblio.*,biblioitems.isbn,
+ aqbasket.closedate, aqbasket.creationdate, aqbasket.basketname,
+ aqorders.*
+ FROM aqorders
LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
- 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)
- ";
- ## FIXME Why 180 days ???
- my @query_params = ( $supplierid );
+ WHERE (quantity > quantityreceived OR quantityreceived is NULL)
+ AND datecancellationprinted IS NULL";
+ my @query_params;
my $userenv = C4::Context->userenv;
- if ( C4::Context->preference("IndependantBranches") ) {
+ if ( C4::Context->preference("IndependentBranches") ) {
if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
- $strsth .= " and (borrowers.branchcode = ?
+ $strsth .= " AND (borrowers.branchcode = ?
or borrowers.branchcode = '')";
push @query_params, $userenv->{branch};
}
}
- if ($owner) {
- $strsth .= " AND aqbasket.authorisedby=? ";
- push @query_params, $userenv->{'number'};
+ if ($supplierid) {
+ $strsth .= " AND aqbasket.booksellerid = ?";
+ push @query_params, $supplierid;
+ }
+ if($ordernumber){
+ $strsth .= " AND (aqorders.ordernumber=?)";
+ push @query_params, $ordernumber;
+ }
+ if($search){
+ $strsth .= " AND (biblio.title like ? OR biblio.author LIKE ? OR biblioitems.isbn like ?)";
+ push @query_params, ("%$search%","%$search%","%$search%");
+ }
+ if ($ean) {
+ $strsth .= " AND biblioitems.ean = ?";
+ push @query_params, $ean;
}
if ($basketno) {
$strsth .= " AND aqbasket.basketno=? ";
push @query_params, $basketno;
}
+ if ($owner) {
+ $strsth .= " AND aqbasket.authorisedby=? ";
+ push @query_params, $userenv->{'number'};
+ }
$strsth .= " group by aqbasket.basketno" if $grouped;
$strsth .= " order by aqbasket.basketno";
-
my $sth = $dbh->prepare($strsth);
$sth->execute( @query_params );
my $results = $sth->fetchall_arrayref({});
}
#------------------------------------------------------------#
+=head3 GetOrdersByBiblionumber
-=head3 GetOrderNumber
+ @orders = &GetOrdersByBiblionumber($biblionumber);
- $ordernumber = &GetOrderNumber($biblioitemnumber, $biblionumber);
+Looks up the orders with linked to a specific $biblionumber, including
+cancelled orders and received orders.
-Looks up the ordernumber with the given biblionumber and biblioitemnumber.
-
-Returns the number of this order.
-
-=over
-
-=item C<$ordernumber> is the order number.
-
-=back
+return :
+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.
=cut
-sub GetOrderNumber {
- my ( $biblionumber,$biblioitemnumber ) = @_;
- my $dbh = C4::Context->dbh;
- my $query = "
- SELECT ordernumber
- FROM aqorders
- WHERE biblionumber=?
- AND biblioitemnumber=?
+sub GetOrdersByBiblionumber {
+ my $biblionumber = shift;
+ return unless $biblionumber;
+ my $dbh = C4::Context->dbh;
+ my $query ="
+ SELECT biblio.*,biblioitems.*,
+ aqorders.*,
+ aqbudgets.*
+ FROM aqorders
+ LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
+ LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
+ LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
+ WHERE aqorders.biblionumber=?
";
my $sth = $dbh->prepare($query);
- $sth->execute( $biblionumber, $biblioitemnumber );
-
- return $sth->fetchrow;
+ $sth->execute($biblionumber);
+ my $results = $sth->fetchall_arrayref({});
+ $sth->finish;
+ return @$results;
}
#------------------------------------------------------------#
return $data;
}
+=head3 GetLastOrderNotReceivedFromSubscriptionid
+
+ $order = &GetLastOrderNotReceivedFromSubscriptionid($subscriptionid);
+
+Returns a reference-to-hash describing the last order not received for a subscription.
+
+=cut
+
+sub GetLastOrderNotReceivedFromSubscriptionid {
+ my ( $subscriptionid ) = @_;
+ my $dbh = C4::Context->dbh;
+ my $query = qq|
+ SELECT * FROM aqorders
+ LEFT JOIN subscription
+ ON ( aqorders.subscriptionid = subscription.subscriptionid )
+ WHERE aqorders.subscriptionid = ?
+ AND aqorders.datereceived IS NULL
+ LIMIT 1
+ |;
+ my $sth = $dbh->prepare( $query );
+ $sth->execute( $subscriptionid );
+ my $order = $sth->fetchrow_hashref;
+ return $order;
+}
+
+=head3 GetLastOrderReceivedFromSubscriptionid
+
+ $order = &GetLastOrderReceivedFromSubscriptionid($subscriptionid);
+
+Returns a reference-to-hash describing the last order received for a subscription.
+
+=cut
+
+sub GetLastOrderReceivedFromSubscriptionid {
+ my ( $subscriptionid ) = @_;
+ my $dbh = C4::Context->dbh;
+ my $query = qq|
+ SELECT * FROM aqorders
+ LEFT JOIN subscription
+ ON ( aqorders.subscriptionid = subscription.subscriptionid )
+ WHERE aqorders.subscriptionid = ?
+ AND aqorders.datereceived =
+ (
+ SELECT MAX( aqorders.datereceived )
+ FROM aqorders
+ LEFT JOIN subscription
+ ON ( aqorders.subscriptionid = subscription.subscriptionid )
+ WHERE aqorders.subscriptionid = ?
+ AND aqorders.datereceived IS NOT NULL
+ )
+ ORDER BY ordernumber DESC
+ LIMIT 1
+ |;
+ my $sth = $dbh->prepare( $query );
+ $sth->execute( $subscriptionid, $subscriptionid );
+ my $order = $sth->fetchrow_hashref;
+ return $order;
+
+}
+
+
#------------------------------------------------------------#
=head3 NewOrder
=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".
+The following keys are used: "biblionumber", "title", "basketno", "quantity", "notes", "rrp", "ecost", "gstrate", "unitprice", "subscription", "sort1", "sort2", "booksellerinvoicenumber", "listprice", "budgetdate", "purchaseordernumber", "branchcode", "booksellerinvoicenumber", "budget_id".
=back
}
my $ordernumber=InsertInTable("aqorders",$orderinfo);
+ if (not $orderinfo->{parent_ordernumber}) {
+ my $sth = $dbh->prepare("
+ UPDATE aqorders
+ SET parent_ordernumber = ordernumber
+ WHERE ordernumber = ?
+ ");
+ $sth->execute($ordernumber);
+ }
return ( $orderinfo->{'basketno'}, $ordernumber );
}
=cut
sub NewOrderItem {
- #my ($biblioitemnumber,$ordernumber, $biblionumber) = @_;
my ($itemnumber, $ordernumber) = @_;
my $dbh = C4::Context->dbh;
my $query = qq|
my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;");
$sth->execute;
my $colnames = $sth->{NAME};
+ #FIXME Be careful. If aqorders would have columns with diacritics,
+ #you should need to decode what you get back from NAME.
+ #See report 10110 and guided_reports.pl
my $query = "UPDATE aqorders SET ";
foreach my $orderinfokey (grep(!/ordernumber/, keys %$orderinfo)){
return 0;
}
-#------------------------------------------------------------#
+=head3 ModItemOrder
+ ModItemOrder($itemnumber, $ordernumber);
-=head3 ModOrderBibliotemNumber
+Modifies the ordernumber of an item in aqorders_items.
- &ModOrderBiblioitemNumber($biblioitemnumber,$ordernumber, $biblionumber);
+=cut
-Modifies the biblioitemnumber for an existing order.
-Updates the order with order number C<$ordernum> and biblionumber C<$biblionumber>.
+sub ModItemOrder {
+ my ($itemnumber, $ordernumber) = @_;
-=cut
+ return unless ($itemnumber and $ordernumber);
-#FIXME: is this used at all?
-sub ModOrderBiblioitemNumber {
- my ($biblioitemnumber,$ordernumber, $biblionumber) = @_;
my $dbh = C4::Context->dbh;
+ my $query = qq{
+ UPDATE aqorders_items
+ SET ordernumber = ?
+ WHERE itemnumber = ?
+ };
+ my $sth = $dbh->prepare($query);
+ return $sth->execute($ordernumber, $itemnumber);
+}
+
+#------------------------------------------------------------#
+
+=head3 GetCancelledOrders
+
+ my @orders = GetCancelledOrders($basketno, $orderby);
+
+Returns cancelled orders for a basket
+
+=cut
+
+sub GetCancelledOrders {
+ my ( $basketno, $orderby ) = @_;
+
+ return () unless $basketno;
+
+ my $dbh = C4::Context->dbh;
my $query = "
- UPDATE aqorders
- SET biblioitemnumber = ?
- WHERE ordernumber = ?
- AND biblionumber = ?";
+ SELECT biblio.*, biblioitems.*, aqorders.*, aqbudgets.*
+ FROM aqorders
+ LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
+ LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
+ LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
+ WHERE basketno = ?
+ AND (datecancellationprinted IS NOT NULL
+ AND datecancellationprinted <> '0000-00-00')
+ ";
+
+ $orderby = "aqorders.datecancellationprinted desc, aqorders.timestamp desc"
+ unless $orderby;
+ $query .= " ORDER BY $orderby";
my $sth = $dbh->prepare($query);
- $sth->execute( $biblioitemnumber, $ordernumber, $biblionumber );
+ $sth->execute($basketno);
+ my $results = $sth->fetchall_arrayref( {} );
+
+ return @$results;
}
+
#------------------------------------------------------------#
=head3 ModReceiveOrder
&ModReceiveOrder($biblionumber, $ordernumber, $quantityreceived, $user,
- $unitprice, $booksellerinvoicenumber, $biblioitemnumber,
- $freight, $bookfund, $rrp);
+ $cost, $ecost, $invoiceid, rrp, budget_id, datereceived, \@received_itemnumbers);
Updates an order, to reflect the fact that it was received, at least
in part. All arguments not mentioned below update the fields with the
same name in the aqorders table of the Koha database.
-If a partial order is received, splits the order into two. The received
-portion must have a booksellerinvoicenumber.
+If a partial order is received, splits the order into two.
Updates the order with bibilionumber C<$biblionumber> and ordernumber
C<$ordernumber>.
sub ModReceiveOrder {
my (
- $biblionumber, $ordernumber, $quantrec, $user, $cost,
- $invoiceno, $freight, $rrp, $budget_id, $datereceived
+ $biblionumber, $ordernumber, $quantrec, $user, $cost, $ecost,
+ $invoiceid, $rrp, $budget_id, $datereceived, $received_items
)
= @_;
+
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;
- my $suggestionid = GetSuggestionFromBiblionumber( $dbh, $biblionumber );
+ my $suggestionid = GetSuggestionFromBiblionumber( $biblionumber );
if ($suggestionid) {
ModSuggestion( {suggestionid=>$suggestionid,
- STATUS=>'AVAILABLE',
- biblionumber=> $biblionumber}
- );
+ STATUS=>'AVAILABLE',
+ biblionumber=> $biblionumber}
+ );
}
my $sth=$dbh->prepare("
my $order = $sth->fetchrow_hashref();
$sth->finish();
+ my $new_ordernumber = $ordernumber;
if ( $order->{quantity} > $quantrec ) {
+ # Split order line in two parts: the first is the original order line
+ # without received items (the quantity is decreased),
+ # the second part is a new order line with quantity=quantityrec
+ # (entirely received)
$sth=$dbh->prepare("
UPDATE aqorders
- SET quantityreceived=?
- , datereceived=?
- , booksellerinvoicenumber=?
- , unitprice=?
- , freight=?
- , rrp=?
- , quantity=?
- WHERE biblionumber=? AND ordernumber=?");
-
- $sth->execute($quantrec,$datereceived,$invoiceno,$cost,$freight,$rrp,$quantrec,$biblionumber,$ordernumber);
+ SET quantity = ?
+ WHERE ordernumber = ?
+ ");
+
+ $sth->execute($order->{quantity} - $quantrec, $ordernumber);
+
$sth->finish;
- # create a new order for the remaining items, and set its bookfund.
- foreach my $orderkey ( "linenumber", "allocation" ) {
- delete($order->{'$orderkey'});
+ delete $order->{'ordernumber'};
+ $order->{'quantity'} = $quantrec;
+ $order->{'quantityreceived'} = $quantrec;
+ $order->{'datereceived'} = $datereceived;
+ $order->{'invoiceid'} = $invoiceid;
+ $order->{'unitprice'} = $cost;
+ $order->{'rrp'} = $rrp;
+ $order->{ecost} = $ecost;
+ $order->{'orderstatus'} = 3; # totally received
+ $new_ordernumber = NewOrder($order);
+
+ if ($received_items) {
+ foreach my $itemnumber (@$received_items) {
+ ModItemOrder($itemnumber, $new_ordernumber);
+ }
}
- $order->{'quantity'} -= $quantrec;
- $order->{'quantityreceived'} = 0;
- my $newOrder = NewOrder($order);
-} else {
+ } else {
$sth=$dbh->prepare("update aqorders
- set quantityreceived=?,datereceived=?,booksellerinvoicenumber=?,
- unitprice=?,freight=?,rrp=?
+ set quantityreceived=?,datereceived=?,invoiceid=?,
+ unitprice=?,rrp=?,ecost=?
where biblionumber=? and ordernumber=?");
- $sth->execute($quantrec,$datereceived,$invoiceno,$cost,$freight,$rrp,$biblionumber,$ordernumber);
+ $sth->execute($quantrec,$datereceived,$invoiceid,$cost,$rrp,$ecost,$biblionumber,$ordernumber);
$sth->finish;
}
- return $datereceived;
+ return ($datereceived, $new_ordernumber);
+}
+
+=head3 CancelReceipt
+
+ my $parent_ordernumber = CancelReceipt($ordernumber);
+
+ Cancel an order line receipt and update the parent order line, as if no
+ receipt was made.
+ If items are created at receipt (AcqCreateItem = receiving) then delete
+ these items.
+
+=cut
+
+sub CancelReceipt {
+ my $ordernumber = shift;
+
+ return unless $ordernumber;
+
+ my $dbh = C4::Context->dbh;
+ my $query = qq{
+ SELECT datereceived, parent_ordernumber, quantity
+ FROM aqorders
+ WHERE ordernumber = ?
+ };
+ my $sth = $dbh->prepare($query);
+ $sth->execute($ordernumber);
+ my $order = $sth->fetchrow_hashref;
+ unless($order) {
+ warn "CancelReceipt: order $ordernumber does not exist";
+ return;
+ }
+ unless($order->{'datereceived'}) {
+ warn "CancelReceipt: order $ordernumber is not received";
+ return;
+ }
+
+ my $parent_ordernumber = $order->{'parent_ordernumber'};
+
+ if($parent_ordernumber == $ordernumber || not $parent_ordernumber) {
+ # The order line has no parent, just mark it as not received
+ $query = qq{
+ UPDATE aqorders
+ SET quantityreceived = ?,
+ datereceived = ?,
+ invoiceid = ?
+ WHERE ordernumber = ?
+ };
+ $sth = $dbh->prepare($query);
+ $sth->execute(0, undef, undef, $ordernumber);
+ } else {
+ # The order line has a parent, increase parent quantity and delete
+ # the order line.
+ $query = qq{
+ SELECT quantity, datereceived
+ FROM aqorders
+ WHERE ordernumber = ?
+ };
+ $sth = $dbh->prepare($query);
+ $sth->execute($parent_ordernumber);
+ my $parent_order = $sth->fetchrow_hashref;
+ unless($parent_order) {
+ warn "Parent order $parent_ordernumber does not exist.";
+ return;
+ }
+ if($parent_order->{'datereceived'}) {
+ warn "CancelReceipt: parent order is received.".
+ " Can't cancel receipt.";
+ return;
+ }
+ $query = qq{
+ UPDATE aqorders
+ SET quantity = ?
+ WHERE ordernumber = ?
+ };
+ $sth = $dbh->prepare($query);
+ my $rv = $sth->execute(
+ $order->{'quantity'} + $parent_order->{'quantity'},
+ $parent_ordernumber
+ );
+ unless($rv) {
+ warn "Cannot update parent order line, so do not cancel".
+ " receipt";
+ return;
+ }
+ if(C4::Context->preference('AcqCreateItem') eq 'receiving') {
+ # Remove items that were created at receipt
+ $query = qq{
+ DELETE FROM items, aqorders_items
+ USING items, aqorders_items
+ WHERE items.itemnumber = ? AND aqorders_items.itemnumber = ?
+ };
+ $sth = $dbh->prepare($query);
+ my @itemnumbers = GetItemnumbersFromOrder($ordernumber);
+ foreach my $itemnumber (@itemnumbers) {
+ $sth->execute($itemnumber, $itemnumber);
+ }
+ } else {
+ # Update items
+ my @itemnumbers = GetItemnumbersFromOrder($ordernumber);
+ foreach my $itemnumber (@itemnumbers) {
+ ModItemOrder($itemnumber, $parent_ordernumber);
+ }
+ }
+ # Delete order line
+ $query = qq{
+ DELETE FROM aqorders
+ WHERE ordernumber = ?
+ };
+ $sth = $dbh->prepare($query);
+ $sth->execute($ordernumber);
+
+ }
+
+ return $parent_ordernumber;
}
+
#------------------------------------------------------------#
=head3 SearchOrder
=item C<branchcode>
-=item C<bookfundid>
+=item C<budget_id>
=back
sub SearchOrder {
#### -------- SearchOrder-------------------------------
- my ($ordernumber, $search, $supplierid, $basket) = @_;
+ my ( $ordernumber, $search, $ean, $supplierid, $basket ) = @_;
my $dbh = C4::Context->dbh;
my @args = ();
$query .= " AND (biblio.title like ? OR biblio.author LIKE ? OR biblioitems.isbn like ?)";
push @args, ("%$search%","%$search%","%$search%");
}
- if($supplierid){
+ if ($ean) {
+ $query .= " AND biblioitems.ean = ?";
+ push @args, $ean;
+ }
+ if ($supplierid) {
$query .= "AND aqbasket.booksellerid = ?";
push @args, $supplierid;
}
my $sth = $dbh->prepare($query);
$sth->execute( $bibnum, $ordernumber );
$sth->finish;
+ my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
+ foreach my $itemnumber (@itemnumbers){
+ C4::Items::DelItem( $dbh, $bibnum, $itemnumber );
+ }
+
}
=head2 FUNCTIONS ABOUT PARCELS
firstname,
aqorders.biblionumber,
aqorders.ordernumber,
+ aqorders.parent_ordernumber,
aqorders.quantity,
aqorders.quantityreceived,
aqorders.unitprice,
aqorders.listprice,
aqorders.rrp,
aqorders.ecost,
+ aqorders.gstrate,
biblio.title
FROM aqorders
LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
+ LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
WHERE
aqbasket.booksellerid = ?
- AND aqorders.booksellerinvoicenumber LIKE ?
+ AND aqinvoices.invoicenumber LIKE ?
AND aqorders.datereceived = ? ";
my @query_params = ( $supplierid, $code, $datereceived );
- if ( C4::Context->preference("IndependantBranches") ) {
+ if ( C4::Context->preference("IndependentBranches") ) {
my $userenv = C4::Context->userenv;
if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
$strsth .= " and (borrowers.branchcode = ?
my $dbh = C4::Context->dbh;
my @query_params = ();
my $strsth ="
- SELECT aqorders.booksellerinvoicenumber,
+ SELECT aqinvoices.invoicenumber,
datereceived,purchaseordernumber,
count(DISTINCT biblionumber) AS biblio,
sum(quantity) AS itemsexpected,
sum(quantityreceived) AS itemsreceived
FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
+ LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
WHERE aqbasket.booksellerid = ? and datereceived IS NOT NULL
";
push @query_params, $bookseller;
if ( defined $code ) {
- $strsth .= ' and aqorders.booksellerinvoicenumber like ? ';
+ $strsth .= ' and aqinvoices.invoicenumber like ? ';
# add a % to the end of the code to allow stemming.
push @query_params, "$code%";
}
push @query_params, $dateto;
}
- $strsth .= "group by aqorders.booksellerinvoicenumber,datereceived ";
+ $strsth .= "group by aqinvoices.invoicenumber,datereceived ";
# can't use a placeholder to place this column name.
# but, we could probably be checking to make sure it is a column that will be fetched.
my $delay = shift;
my $supplierid = shift;
my $branch = shift;
+ my $estimateddeliverydatefrom = shift;
+ my $estimateddeliverydateto = shift;
my $dbh = C4::Context->dbh;
#BEWARE, order of parenthesis and LEFT JOIN is important for speed
my $dbdriver = C4::Context->config("db_scheme") || "mysql";
- my @query_params = ($delay); # delay is the first argument regardless
+ my @query_params = ();
my $select = "
SELECT aqbasket.basketno,
aqorders.ordernumber,
DATE(aqbasket.closedate) AS orderdate,
aqorders.rrp AS unitpricesupplier,
aqorders.ecost AS unitpricelib,
+ aqorders.claims_count AS claims_count,
+ aqorders.claimed_date AS claimed_date,
aqbudgets.budget_name AS budget,
borrowers.branchcode AS branch,
aqbooksellers.name AS supplier,
+ aqbooksellers.id AS supplierid,
biblio.author, biblio.title,
biblioitems.publishercode AS publisher,
biblioitems.publicationyear,
+ ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
";
my $from = "
FROM
OR datereceived IS NULL
OR aqorders.quantityreceived < aqorders.quantity
)
+ AND aqbasket.closedate IS NOT NULL
AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
";
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
+ aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity,
+ (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
+ DATEDIFF(CAST(now() AS date),closedate) AS latesince
";
- $from .= " AND (closedate <= DATE_SUB(CURDATE( ),INTERVAL ? DAY)) ";
+ if ( defined $delay ) {
+ $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ;
+ push @query_params, $delay;
+ }
$having = "
HAVING quantity <> 0
AND unitpricesupplier <> 0
$select .= "
aqorders.quantity AS quantity,
aqorders.quantity * aqorders.rrp AS subtotal,
- (CURDATE - closedate) AS latesince
+ (CAST(now() AS date) - closedate) AS latesince
";
- $from .= " AND (closedate <= (CURDATE -(INTERVAL ? DAY)) ";
+ if ( defined $delay ) {
+ $from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) ";
+ push @query_params, $delay;
+ }
}
if (defined $supplierid) {
$from .= ' AND aqbasket.booksellerid = ? ';
$from .= ' AND borrowers.branchcode LIKE ? ';
push @query_params, $branch;
}
- if (C4::Context->preference("IndependantBranches")
+
+ if ( defined $estimateddeliverydatefrom or defined $estimateddeliverydateto ) {
+ $from .= ' AND aqbooksellers.deliverytime IS NOT NULL ';
+ }
+ if ( defined $estimateddeliverydatefrom ) {
+ $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
+ push @query_params, $estimateddeliverydatefrom;
+ }
+ if ( defined $estimateddeliverydateto ) {
+ $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
+ push @query_params, $estimateddeliverydateto;
+ }
+ if ( defined $estimateddeliverydatefrom and not defined $estimateddeliverydateto ) {
+ $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
+ }
+ if (C4::Context->preference("IndependentBranches")
&& C4::Context->userenv
&& C4::Context->userenv->{flags} != 1 ) {
$from .= ' AND borrowers.branchcode LIKE ? ';
my @results;
while (my $data = $sth->fetchrow_hashref) {
$data->{orderdate} = format_date($data->{orderdate});
+ $data->{claimed_date} = format_date($data->{claimed_date});
push @results, $data;
}
return @results;
my $title = $params{title};
my $author = $params{author};
my $isbn = $params{isbn};
+ my $ean = $params{ean};
my $name = $params{name};
my $from_placed_on = $params{from_placed_on};
my $to_placed_on = $params{to_placed_on};
my $basket = $params{basket};
my $booksellerinvoicenumber = $params{booksellerinvoicenumber};
-
+ my $basketgroupname = $params{basketgroupname};
my @order_loop;
my $total_qty = 0;
my $total_qtyreceived = 0;
biblio.title,
biblio.author,
biblioitems.isbn,
+ biblioitems.ean,
aqorders.basketno,
- aqbasket.basketname,
- aqbasket.basketgroupid,
- aqbasketgroups.name as groupname,
+ aqbasket.basketname,
+ aqbasket.basketgroupid,
+ aqbasketgroups.name as groupname,
aqbooksellers.name,
- aqbasket.creationdate,
+ aqbasket.creationdate,
aqorders.datereceived,
aqorders.quantity,
aqorders.quantityreceived,
aqorders.ecost,
aqorders.ordernumber,
- aqorders.booksellerinvoicenumber as invoicenumber,
+ aqorders.invoiceid,
+ aqinvoices.invoicenumber,
aqbooksellers.id as id,
aqorders.biblionumber
FROM aqorders
LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
- LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
+ LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
- LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber";
+ LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
+ LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid";
$query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber"
- if ( C4::Context->preference("IndependantBranches") );
+ if ( C4::Context->preference("IndependentBranches") );
$query .= " WHERE (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') ";
my @query_params = ();
- if ( defined $title ) {
+ if ( $title ) {
$query .= " AND biblio.title LIKE ? ";
$title =~ s/\s+/%/g;
push @query_params, "%$title%";
}
- if ( defined $author ) {
+ if ( $author ) {
$query .= " AND biblio.author LIKE ? ";
push @query_params, "%$author%";
}
- if ( defined $isbn ) {
+ if ( $isbn ) {
$query .= " AND biblioitems.isbn LIKE ? ";
push @query_params, "%$isbn%";
}
-
- if ( defined $name ) {
+ if ( defined $ean and $ean ) {
+ $query .= " AND biblioitems.ean = ? ";
+ push @query_params, "$ean";
+ }
+ if ( $name ) {
$query .= " AND aqbooksellers.name LIKE ? ";
push @query_params, "%$name%";
}
- if ( defined $from_placed_on ) {
+ if ( $from_placed_on ) {
$query .= " AND creationdate >= ? ";
push @query_params, $from_placed_on;
}
- if ( defined $to_placed_on ) {
+ if ( $to_placed_on ) {
$query .= " AND creationdate <= ? ";
push @query_params, $to_placed_on;
}
}
if ($booksellerinvoicenumber) {
- $query .= " AND (aqorders.booksellerinvoicenumber LIKE ? OR aqbasket.booksellerinvoicenumber LIKE ?)";
- push @query_params, "%$booksellerinvoicenumber%", "%$booksellerinvoicenumber%";
+ $query .= " AND aqinvoices.invoicenumber LIKE ? ";
+ push @query_params, "%$booksellerinvoicenumber%";
}
- if ( C4::Context->preference("IndependantBranches") ) {
+ if ($basketgroupname) {
+ $query .= " AND aqbasketgroups.name LIKE ? ";
+ push @query_params, "%$basketgroupname%";
+ }
+
+ if ( C4::Context->preference("IndependentBranches") ) {
my $userenv = C4::Context->userenv;
if ( $userenv && ($userenv->{flags} || 0) != 1 ) {
$query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
$line->{count} = $cnt++;
$line->{toggle} = 1 if $cnt % 2;
push @order_loop, $line;
- $line->{creationdate} = format_date( $line->{creationdate} );
- $line->{datereceived} = format_date( $line->{datereceived} );
$total_qty += $line->{'quantity'};
$total_qtyreceived += $line->{'quantityreceived'};
$total_price += $line->{'quantity'} * $line->{'ecost'};
return $result;
}
+=head3 AddClaim
+
+=over 4
+
+&AddClaim($ordernumber);
+
+Add a claim for an order
+
+=back
+
+=cut
+sub AddClaim {
+ my ($ordernumber) = @_;
+ my $dbh = C4::Context->dbh;
+ my $query = "
+ UPDATE aqorders SET
+ claims_count = claims_count + 1,
+ claimed_date = CURDATE()
+ WHERE ordernumber = ?
+ ";
+ my $sth = $dbh->prepare($query);
+ $sth->execute($ordernumber);
+}
+
+=head3 GetInvoices
+
+ my @invoices = GetInvoices(
+ invoicenumber => $invoicenumber,
+ suppliername => $suppliername,
+ shipmentdatefrom => $shipmentdatefrom, # ISO format
+ shipmentdateto => $shipmentdateto, # ISO format
+ billingdatefrom => $billingdatefrom, # ISO format
+ billingdateto => $billingdateto, # ISO format
+ isbneanissn => $isbn_or_ean_or_issn,
+ title => $title,
+ author => $author,
+ publisher => $publisher,
+ publicationyear => $publicationyear,
+ branchcode => $branchcode,
+ order_by => $order_by
+ );
+
+Return a list of invoices that match all given criteria.
+
+$order_by is "column_name (asc|desc)", where column_name is any of
+'invoicenumber', 'booksellerid', 'shipmentdate', 'billingdate', 'closedate',
+'shipmentcost', 'shipmentcost_budgetid'.
+
+asc is the default if omitted
+
+=cut
+
+sub GetInvoices {
+ my %args = @_;
+
+ my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
+ closedate shipmentcost shipmentcost_budgetid);
+
+ my $dbh = C4::Context->dbh;
+ my $query = qq{
+ SELECT aqinvoices.*, aqbooksellers.name AS suppliername,
+ COUNT(
+ DISTINCT IF(
+ aqorders.datereceived IS NOT NULL,
+ aqorders.biblionumber,
+ NULL
+ )
+ ) AS receivedbiblios,
+ SUM(aqorders.quantityreceived) AS receiveditems
+ FROM aqinvoices
+ LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid
+ LEFT JOIN aqorders ON aqorders.invoiceid = aqinvoices.invoiceid
+ LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
+ LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
+ LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber
+ };
+
+ my @bind_args;
+ my @bind_strs;
+ if($args{supplierid}) {
+ push @bind_strs, " aqinvoices.booksellerid = ? ";
+ push @bind_args, $args{supplierid};
+ }
+ if($args{invoicenumber}) {
+ push @bind_strs, " aqinvoices.invoicenumber LIKE ? ";
+ push @bind_args, "%$args{invoicenumber}%";
+ }
+ if($args{suppliername}) {
+ push @bind_strs, " aqbooksellers.name LIKE ? ";
+ push @bind_args, "%$args{suppliername}%";
+ }
+ if($args{shipmentdatefrom}) {
+ push @bind_strs, " aqinvoices.shipementdate >= ? ";
+ push @bind_args, $args{shipmentdatefrom};
+ }
+ if($args{shipmentdateto}) {
+ push @bind_strs, " aqinvoices.shipementdate <= ? ";
+ push @bind_args, $args{shipmentdateto};
+ }
+ if($args{billingdatefrom}) {
+ push @bind_strs, " aqinvoices.billingdate >= ? ";
+ push @bind_args, $args{billingdatefrom};
+ }
+ if($args{billingdateto}) {
+ push @bind_strs, " aqinvoices.billingdate <= ? ";
+ push @bind_args, $args{billingdateto};
+ }
+ if($args{isbneanissn}) {
+ push @bind_strs, " (biblioitems.isbn LIKE ? OR biblioitems.ean LIKE ? OR biblioitems.issn LIKE ? ) ";
+ push @bind_args, $args{isbneanissn}, $args{isbneanissn}, $args{isbneanissn};
+ }
+ if($args{title}) {
+ push @bind_strs, " biblio.title LIKE ? ";
+ push @bind_args, $args{title};
+ }
+ if($args{author}) {
+ push @bind_strs, " biblio.author LIKE ? ";
+ push @bind_args, $args{author};
+ }
+ if($args{publisher}) {
+ push @bind_strs, " biblioitems.publishercode LIKE ? ";
+ push @bind_args, $args{publisher};
+ }
+ if($args{publicationyear}) {
+ push @bind_strs, " biblioitems.publicationyear = ? ";
+ push @bind_args, $args{publicationyear};
+ }
+ if($args{branchcode}) {
+ push @bind_strs, " aqorders.branchcode = ? ";
+ push @bind_args, $args{branchcode};
+ }
+
+ $query .= " WHERE " . join(" AND ", @bind_strs) if @bind_strs;
+ $query .= " GROUP BY aqinvoices.invoiceid ";
+
+ if($args{order_by}) {
+ my ($column, $direction) = split / /, $args{order_by};
+ if(grep /^$column$/, @columns) {
+ $direction ||= 'ASC';
+ $query .= " ORDER BY $column $direction";
+ }
+ }
+
+ my $sth = $dbh->prepare($query);
+ $sth->execute(@bind_args);
+
+ my $results = $sth->fetchall_arrayref({});
+ return @$results;
+}
+
+=head3 GetInvoice
+
+ my $invoice = GetInvoice($invoiceid);
+
+Get informations about invoice with given $invoiceid
+
+Return a hash filled with aqinvoices.* fields
+
+=cut
+
+sub GetInvoice {
+ my ($invoiceid) = @_;
+ my $invoice;
+
+ return unless $invoiceid;
+
+ my $dbh = C4::Context->dbh;
+ my $query = qq{
+ SELECT *
+ FROM aqinvoices
+ WHERE invoiceid = ?
+ };
+ my $sth = $dbh->prepare($query);
+ $sth->execute($invoiceid);
+
+ $invoice = $sth->fetchrow_hashref;
+ return $invoice;
+}
+
+=head3 GetInvoiceDetails
+
+ my $invoice = GetInvoiceDetails($invoiceid)
+
+Return informations about an invoice + the list of related order lines
+
+Orders informations are in $invoice->{orders} (array ref)
+
+=cut
+
+sub GetInvoiceDetails {
+ my ($invoiceid) = @_;
+
+ if ( !defined $invoiceid ) {
+ carp 'GetInvoiceDetails called without an invoiceid';
+ return;
+ }
+
+ my $dbh = C4::Context->dbh;
+ my $query = qq{
+ SELECT aqinvoices.*, aqbooksellers.name AS suppliername
+ FROM aqinvoices
+ LEFT JOIN aqbooksellers ON aqinvoices.booksellerid = aqbooksellers.id
+ WHERE invoiceid = ?
+ };
+ my $sth = $dbh->prepare($query);
+ $sth->execute($invoiceid);
+
+ my $invoice = $sth->fetchrow_hashref;
+
+ $query = qq{
+ SELECT aqorders.*, biblio.*
+ FROM aqorders
+ LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
+ WHERE invoiceid = ?
+ };
+ $sth = $dbh->prepare($query);
+ $sth->execute($invoiceid);
+ $invoice->{orders} = $sth->fetchall_arrayref({});
+ $invoice->{orders} ||= []; # force an empty arrayref if fetchall_arrayref fails
+
+ return $invoice;
+}
+
+=head3 AddInvoice
+
+ my $invoiceid = AddInvoice(
+ invoicenumber => $invoicenumber,
+ booksellerid => $booksellerid,
+ shipmentdate => $shipmentdate,
+ billingdate => $billingdate,
+ closedate => $closedate,
+ shipmentcost => $shipmentcost,
+ shipmentcost_budgetid => $shipmentcost_budgetid
+ );
+
+Create a new invoice and return its id or undef if it fails.
+
+=cut
+
+sub AddInvoice {
+ my %invoice = @_;
+
+ return unless(%invoice and $invoice{invoicenumber});
+
+ my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
+ closedate shipmentcost shipmentcost_budgetid);
+
+ my @set_strs;
+ my @set_args;
+ foreach my $key (keys %invoice) {
+ if(0 < grep(/^$key$/, @columns)) {
+ push @set_strs, "$key = ?";
+ push @set_args, ($invoice{$key} || undef);
+ }
+ }
+
+ my $rv;
+ if(@set_args > 0) {
+ my $dbh = C4::Context->dbh;
+ my $query = "INSERT INTO aqinvoices SET ";
+ $query .= join (",", @set_strs);
+ my $sth = $dbh->prepare($query);
+ $rv = $sth->execute(@set_args);
+ if($rv) {
+ $rv = $dbh->last_insert_id(undef, undef, 'aqinvoices', undef);
+ }
+ }
+ return $rv;
+}
+
+=head3 ModInvoice
+
+ ModInvoice(
+ invoiceid => $invoiceid, # Mandatory
+ invoicenumber => $invoicenumber,
+ booksellerid => $booksellerid,
+ shipmentdate => $shipmentdate,
+ billingdate => $billingdate,
+ closedate => $closedate,
+ shipmentcost => $shipmentcost,
+ shipmentcost_budgetid => $shipmentcost_budgetid
+ );
+
+Modify an invoice, invoiceid is mandatory.
+
+Return undef if it fails.
+
+=cut
+
+sub ModInvoice {
+ my %invoice = @_;
+
+ return unless(%invoice and $invoice{invoiceid});
+
+ my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
+ closedate shipmentcost shipmentcost_budgetid);
+
+ my @set_strs;
+ my @set_args;
+ foreach my $key (keys %invoice) {
+ if(0 < grep(/^$key$/, @columns)) {
+ push @set_strs, "$key = ?";
+ push @set_args, ($invoice{$key} || undef);
+ }
+ }
+
+ my $dbh = C4::Context->dbh;
+ my $query = "UPDATE aqinvoices SET ";
+ $query .= join(",", @set_strs);
+ $query .= " WHERE invoiceid = ?";
+
+ my $sth = $dbh->prepare($query);
+ $sth->execute(@set_args, $invoice{invoiceid});
+}
+
+=head3 CloseInvoice
+
+ CloseInvoice($invoiceid);
+
+Close an invoice.
+
+Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => undef);
+
+=cut
+
+sub CloseInvoice {
+ my ($invoiceid) = @_;
+
+ return unless $invoiceid;
+
+ my $dbh = C4::Context->dbh;
+ my $query = qq{
+ UPDATE aqinvoices
+ SET closedate = CAST(NOW() AS DATE)
+ WHERE invoiceid = ?
+ };
+ my $sth = $dbh->prepare($query);
+ $sth->execute($invoiceid);
+}
+
+=head3 ReopenInvoice
+
+ ReopenInvoice($invoiceid);
+
+Reopen an invoice
+
+Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => C4::Dates->new()->output('iso'))
+
+=cut
+
+sub ReopenInvoice {
+ my ($invoiceid) = @_;
+
+ return unless $invoiceid;
+
+ my $dbh = C4::Context->dbh;
+ my $query = qq{
+ UPDATE aqinvoices
+ SET closedate = NULL
+ WHERE invoiceid = ?
+ };
+ my $sth = $dbh->prepare($query);
+ $sth->execute($invoiceid);
+}
+
1;
__END__
projects / koha.git / blobdiff