use C4::Suggestions;
use C4::Biblio;
use C4::Debug;
-use C4::SQLHelper qw(InsertInTable);
+use C4::SQLHelper qw(InsertInTable UpdateInTable);
use C4::Bookseller qw(GetBookSellerFromId);
use C4::Templates qw(gettemplate);
require Exporter;
@ISA = qw(Exporter);
@EXPORT = qw(
- &GetBasket &NewBasket &CloseBasket &DelBasket &ModBasket
+ &GetBasket &NewBasket &CloseBasket &ReopenBasket &DelBasket &ModBasket
&GetBasketAsCSV &GetBasketGroupAsCSV
&GetBasketsByBookseller &GetBasketsByBasketgroup
&GetBasketsInfosByBookseller
+ &GetBasketUsers &ModBasketUsers
+ &CanUserManageBasket
+
&ModBasketHeader
&ModBasketgroup &NewBasketgroup &DelBasketgroup &GetBasketgroup &CloseBasketgroup
&GetBasketgroups &ReOpenBasketgroup
- &NewOrder &DelOrder &ModOrder &GetPendingOrders &GetOrder &GetOrders
- &GetOrderNumber &GetLateOrders &GetOrderFromItemnumber
- &SearchOrder &GetHistory &GetRecentAcqui
- &ModReceiveOrder &CancelReceipt &ModOrderBiblioitemNumber
- &GetCancelledOrders
-
- &NewOrderItem &ModOrderItem &ModItemOrder
+ &NewOrder &DelOrder &ModOrder &GetOrder &GetOrders &GetOrdersByBiblionumber
+ &GetLateOrders &GetOrderFromItemnumber
+ &SearchOrders &GetHistory &GetRecentAcqui
+ &ModReceiveOrder &CancelReceipt
+ &GetCancelledOrders &TransferOrder
+ &GetLastOrderNotReceivedFromSubscriptionid &GetLastOrderReceivedFromSubscriptionid
+ &NewOrderItem &ModItemOrder
&GetParcels &GetParcel
&GetContracts &GetContract
&ModInvoice
&CloseInvoice
&ReopenInvoice
+ &DelInvoice
+ &MergeInvoices
&GetItemnumbersFromOrder
&AddClaim
+ &GetBiblioCountByBasketno
);
}
my $dbh = C4::Context->dbh;
my $query = "
SELECT aqbasket.*,
- concat( b.firstname,' ',b.surname) AS authorisedbyname,
- b.branchcode AS branch
+ concat( b.firstname,' ',b.surname) AS authorisedbyname
FROM aqbasket
LEFT JOIN borrowers b ON aqbasket.authorisedby=b.borrowernumber
WHERE basketno=?
=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, $booksellerid);
+ 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;
}
&CloseBasket($basketno);
-close a basket (becomes unmodifiable,except for recieves)
+close a basket (becomes unmodifiable, except for receives)
=cut
";
my $sth = $dbh->prepare($query);
$sth->execute($basketno);
+
+ my @orders = GetOrders($basketno);
+ foreach my $order (@orders) {
+ $query = qq{
+ UPDATE aqorders
+ SET orderstatus = 'ordered'
+ WHERE ordernumber = ?;
+ };
+ $sth = $dbh->prepare($query);
+ $sth->execute($order->{'ordernumber'});
+ }
+}
+
+=head3 ReopenBasket
+
+ &ReopenBasket($basketno);
+
+reopen a basket
+
+=cut
+
+sub ReopenBasket {
+ my ($basketno) = @_;
+ my $dbh = C4::Context->dbh;
+ my $query = "
+ UPDATE aqbasket
+ SET closedate=NULL
+ WHERE basketno=?
+ ";
+ my $sth = $dbh->prepare($query);
+ $sth->execute($basketno);
+
+ my @orders = GetOrders($basketno);
+ foreach my $order (@orders) {
+ $query = qq{
+ UPDATE aqorders
+ SET orderstatus = 'new'
+ WHERE ordernumber = ?;
+ };
+ $sth = $dbh->prepare($query);
+ $sth->execute($order->{'ordernumber'});
+ }
}
#------------------------------------------------------------#
notes => $order->{'notes'},
quantity => $order->{'quantity'},
rrp => $order->{'rrp'},
- deliveryplace => $basket->{'deliveryplace'},
- billingplace => $basket->{'billingplace'}
+ deliveryplace => C4::Branch::GetBranchName( $basket->{'deliveryplace'} ),
+ billingplace => C4::Branch::GetBranchName( $basket->{'billingplace'} ),
};
foreach(qw(
contractname author title publishercode collectiontitle notes
=head3 GetBasketGroupAsCSV
-=over 4
+=over
&GetBasketGroupAsCSV($basketgroupid);
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'} );
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
my $dbh = C4::Context->dbh;
my $sth = $dbh->prepare($query);
$sth->execute(@params);
+
$sth->finish;
}
=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, $booksellerid) = @_;
+ my ($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid, $deliveryplace, $billingplace) = @_;
+ my $query = qq{
+ UPDATE aqbasket
+ SET basketname=?, note=?, booksellernote=?, booksellerid=?, deliveryplace=?, billingplace=?
+ WHERE basketno=?
+ };
- my $query = "UPDATE aqbasket SET basketname=?, note=?, booksellernote=?, booksellerid=? WHERE basketno=?";
my $dbh = C4::Context->dbh;
my $sth = $dbh->prepare($query);
- $sth->execute($basketname,$note,$booksellernote,$booksellerid,$basketno);
+ $sth->execute($basketname, $note, $booksellernote, $booksellerid, $deliveryplace, $billingplace, $basketno);
if ( $contractnumber ) {
my $query2 ="UPDATE aqbasket SET contractnumber=? WHERE basketno=?";
=head3 GetBasketsInfosByBookseller
- my $baskets = GetBasketsInfosByBookseller($supplierid);
+ 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
=cut
sub GetBasketsInfosByBookseller {
- my ($supplierid) = @_;
+ my ($supplierid, $allbaskets) = @_;
return unless $supplierid;
) AS expected_items
FROM aqbasket
LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
- WHERE booksellerid = ?
- GROUP BY 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 GetBasketUsers
+
+ $basketusers_ids = &GetBasketUsers($basketno);
+
+Returns a list of all borrowernumbers that are in basket users list
+
+=cut
+
+sub GetBasketUsers {
+ my $basketno = shift;
+
+ return unless $basketno;
+
+ my $query = qq{
+ SELECT borrowernumber
+ FROM aqbasketusers
+ WHERE basketno = ?
+ };
+ my $dbh = C4::Context->dbh;
+ my $sth = $dbh->prepare($query);
+ $sth->execute($basketno);
+ my $results = $sth->fetchall_arrayref( {} );
+ $sth->finish();
+
+ my @borrowernumbers;
+ foreach (@$results) {
+ push @borrowernumbers, $_->{'borrowernumber'};
+ }
+
+ return @borrowernumbers;
+}
+
+=head3 ModBasketUsers
+
+ my @basketusers_ids = (1, 2, 3);
+ &ModBasketUsers($basketno, @basketusers_ids);
+
+Delete all users from basket users list, and add users in C<@basketusers_ids>
+to this users list.
+
+=cut
+
+sub ModBasketUsers {
+ my ($basketno, @basketusers_ids) = @_;
+
+ return unless $basketno;
+
+ my $dbh = C4::Context->dbh;
+ my $query = qq{
+ DELETE FROM aqbasketusers
+ WHERE basketno = ?
+ };
+ my $sth = $dbh->prepare($query);
+ $sth->execute($basketno);
+ $sth->finish();
+
+ $query = qq{
+ INSERT INTO aqbasketusers (basketno, borrowernumber)
+ VALUES (?, ?)
+ };
+ $sth = $dbh->prepare($query);
+ foreach my $basketuser_id (@basketusers_ids) {
+ $sth->execute($basketno, $basketuser_id);
+ }
+}
+
+=head3 CanUserManageBasket
+
+ my $bool = CanUserManageBasket($borrower, $basket[, $userflags]);
+ my $bool = CanUserManageBasket($borrowernumber, $basketno[, $userflags]);
+
+Check if a borrower can manage a basket, according to system preference
+AcqViewBaskets, user permissions and basket properties (creator, users list,
+branch).
+
+First parameter can be either a borrowernumber or a hashref as returned by
+C4::Members::GetMember.
+
+Second parameter can be either a basketno or a hashref as returned by
+C4::Acquisition::GetBasket.
+
+The third parameter is optional. If given, it should be a hashref as returned
+by C4::Auth::getuserflags. If not, getuserflags is called.
+
+If user is authorised to manage basket, returns 1.
+Otherwise returns 0.
+
+=cut
+
+sub CanUserManageBasket {
+ my ($borrower, $basket, $userflags) = @_;
+
+ if (!ref $borrower) {
+ $borrower = C4::Members::GetMember(borrowernumber => $borrower);
+ }
+ if (!ref $basket) {
+ $basket = GetBasket($basket);
+ }
+
+ return 0 unless ($basket and $borrower);
+
+ my $borrowernumber = $borrower->{borrowernumber};
+ my $basketno = $basket->{basketno};
+
+ my $AcqViewBaskets = C4::Context->preference('AcqViewBaskets');
+
+ if (!defined $userflags) {
+ my $dbh = C4::Context->dbh;
+ my $sth = $dbh->prepare("SELECT flags FROM borrowers WHERE borrowernumber = ?");
+ $sth->execute($borrowernumber);
+ my ($flags) = $sth->fetchrow_array;
+ $sth->finish;
+
+ $userflags = C4::Auth::getuserflags($flags, $borrower->{userid}, $dbh);
+ }
+
+ unless ($userflags->{superlibrarian}
+ || (ref $userflags->{acquisition} && $userflags->{acquisition}->{order_manage_all})
+ || (!ref $userflags->{acquisition} && $userflags->{acquisition}))
+ {
+ if (not exists $userflags->{acquisition}) {
+ return 0;
+ }
+
+ if ( (ref $userflags->{acquisition} && !$userflags->{acquisition}->{order_manage})
+ || (!ref $userflags->{acquisition} && !$userflags->{acquisition}) ) {
+ return 0;
+ }
+
+ if ($AcqViewBaskets eq 'user'
+ && $basket->{authorisedby} != $borrowernumber
+ && grep($borrowernumber, GetBasketUsers($basketno)) == 0) {
+ return 0;
+ }
+
+ if ($AcqViewBaskets eq 'branch' && defined $basket->{branch}
+ && $basket->{branch} ne $borrower->{branchcode}) {
+ return 0;
+ }
+ }
+
+ return 1;
+}
#------------------------------------------------------------#
$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({});
}
#------------------------------------------------------------#
=head2 FUNCTIONS ABOUT ORDERS
-=cut
-
-#------------------------------------------------------------#
-
-=head3 GetPendingOrders
-
-$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<$owner> contains 0 or 1. 0 means any owner. 1 means only the list of orders entered by the user itself.
-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.
-
-Used also by the filter in parcel.pl
-I have added:
-
-C<$ordernumber>
-C<$search>
-C<$ean>
-
-These give the value of the corresponding field in the aqorders table
-of the Koha database.
-
-Results are ordered from most to least recent.
-
-=cut
-
-sub GetPendingOrders {
- my ($supplierid,$grouped,$owner,$basketno,$ordernumber,$search,$ean) = @_;
- my $dbh = C4::Context->dbh;
- my $strsth = "
- 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 (quantity > quantityreceived OR quantityreceived is NULL)
- AND datecancellationprinted IS NULL";
- my @query_params;
- my $userenv = C4::Context->userenv;
- if ( C4::Context->preference("IndependantBranches") ) {
- if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
- $strsth .= " AND (borrowers.branchcode = ?
- or borrowers.branchcode = '')";
- push @query_params, $userenv->{branch};
- }
- }
- 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({});
- $sth->finish;
- return $results;
-}
-
-#------------------------------------------------------------#
-
=head3 GetOrders
@orders = &GetOrders($basketnumber, $orderby);
SELECT biblio.*,biblioitems.*,
aqorders.*,
aqbudgets.*,
- biblio.title
+ biblio.title,
+ aqorders_transfers.ordernumber_from AS transferred_from,
+ aqorders_transfers.timestamp AS transferred_from_timestamp
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
+ LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
WHERE basketno=?
AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
";
}
#------------------------------------------------------------#
+=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;
}
#------------------------------------------------------------#
sub GetOrder {
my ($ordernumber) = @_;
my $dbh = C4::Context->dbh;
- my $query = "
- SELECT biblioitems.*, biblio.*, aqorders.*
- FROM aqorders
- LEFT JOIN biblio on biblio.biblionumber=aqorders.biblionumber
- LEFT JOIN biblioitems on biblioitems.biblionumber=aqorders.biblionumber
- WHERE aqorders.ordernumber=?
-
- ";
+ my $query = qq{SELECT
+ aqorders.*,
+ biblio.title,
+ biblio.author,
+ aqbasket.basketname,
+ borrowers.branchcode,
+ biblioitems.publicationyear,
+ biblio.copyrightdate,
+ biblioitems.editionstatement,
+ biblioitems.isbn,
+ biblioitems.ean,
+ biblio.seriestitle,
+ biblioitems.publishercode,
+ 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,
+ aqbooksellers.name AS supplier,
+ aqbooksellers.id AS supplierid,
+ biblioitems.publishercode AS publisher,
+ ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
+ DATE(aqbasket.closedate) AS orderdate,
+ aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity_to_receive,
+ (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
+ DATEDIFF(CURDATE( ),closedate) AS latesince
+ 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 ordernumber=?};
my $sth= $dbh->prepare($query);
$sth->execute($ordernumber);
my $data = $sth->fetchrow_hashref;
+ $data->{orderdate} = format_date( $data->{orderdate} );
$sth->finish;
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
sub NewOrder {
my $orderinfo = shift;
-#### ------------------------------
+
my $dbh = C4::Context->dbh;
my @params;
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)){
#------------------------------------------------------------#
-=head3 ModOrderItem
-
- &ModOrderItem(\%hashref);
-
-Modifies the itemnumber in the aqorders_items table. The input hash needs three entities:
-
-=over
-
-=item - itemnumber: the old itemnumber
-=item - ordernumber: the order this item is attached to
-=item - newitemnumber: the new itemnumber we want to attach the line to
-
-=back
-
-=cut
-
-sub ModOrderItem {
- my $orderiteminfo = shift;
- if (! $orderiteminfo->{'ordernumber'} || ! $orderiteminfo->{'itemnumber'} || ! $orderiteminfo->{'newitemnumber'}){
- die "Ordernumber, itemnumber and newitemnumber is required";
- }
-
- my $dbh = C4::Context->dbh;
-
- my $query = "UPDATE aqorders_items set itemnumber=? where itemnumber=? and ordernumber=?";
- my @params = ($orderiteminfo->{'newitemnumber'}, $orderiteminfo->{'itemnumber'}, $orderiteminfo->{'ordernumber'});
- my $sth = $dbh->prepare($query);
- $sth->execute(@params);
- return 0;
-}
-
=head3 ModItemOrder
ModItemOrder($itemnumber, $ordernumber);
#------------------------------------------------------------#
-
-=head3 ModOrderBibliotemNumber
-
- &ModOrderBiblioitemNumber($biblioitemnumber,$ordernumber, $biblionumber);
-
-Modifies the biblioitemnumber for an existing order.
-Updates the order with order number C<$ordernum> and biblionumber C<$biblionumber>.
-
-=cut
-
-#FIXME: is this used at all?
-sub ModOrderBiblioitemNumber {
- my ($biblioitemnumber,$ordernumber, $biblionumber) = @_;
- my $dbh = C4::Context->dbh;
- my $query = "
- UPDATE aqorders
- SET biblioitemnumber = ?
- WHERE ordernumber = ?
- AND biblionumber = ?";
- my $sth = $dbh->prepare($query);
- $sth->execute( $biblioitemnumber, $ordernumber, $biblionumber );
-}
-
=head3 GetCancelledOrders
my @orders = GetCancelledOrders($basketno, $orderby);
my $dbh = C4::Context->dbh;
my $query = "
- SELECT biblio.*, biblioitems.*, aqorders.*, aqbudgets.*
+ SELECT
+ biblio.*,
+ biblioitems.*,
+ aqorders.*,
+ aqbudgets.*,
+ aqorders_transfers.ordernumber_to AS transferred_to,
+ aqorders_transfers.timestamp AS transferred_to_timestamp
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
+ LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber
WHERE basketno = ?
AND (datecancellationprinted IS NOT NULL
AND datecancellationprinted <> '0000-00-00')
=head3 ModReceiveOrder
&ModReceiveOrder($biblionumber, $ordernumber, $quantityreceived, $user,
- $unitprice, $invoiceid, $biblioitemnumber,
- $bookfund, $rrp, \@received_itemnumbers);
+ $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
sub ModReceiveOrder {
my (
- $biblionumber, $ordernumber, $quantrec, $user, $cost,
+ $biblionumber, $ordernumber, $quantrec, $user, $cost, $ecost,
$invoiceid, $rrp, $budget_id, $datereceived, $received_items
)
= @_;
# (entirely received)
$sth=$dbh->prepare("
UPDATE aqorders
- SET quantity = ?
+ SET quantity = ?,
+ orderstatus = 'partial'
WHERE ordernumber = ?
");
$sth->execute($order->{quantity} - $quantrec, $ordernumber);
+
$sth->finish;
delete $order->{'ordernumber'};
+ $order->{'budget_id'} = ( $budget_id || $order->{'budget_id'} );
$order->{'quantity'} = $quantrec;
$order->{'quantityreceived'} = $quantrec;
$order->{'datereceived'} = $datereceived;
$order->{'invoiceid'} = $invoiceid;
$order->{'unitprice'} = $cost;
$order->{'rrp'} = $rrp;
- $order->{'orderstatus'} = 3; # totally received
- $new_ordernumber = NewOrder($order);
+ $order->{ecost} = $ecost;
+ $order->{'orderstatus'} = 'complete';
+ my $basketno;
+ ( $basketno, $new_ordernumber ) = NewOrder($order);
if ($received_items) {
foreach my $itemnumber (@$received_items) {
} else {
$sth=$dbh->prepare("update aqorders
set quantityreceived=?,datereceived=?,invoiceid=?,
- unitprice=?,rrp=?
+ unitprice=?,rrp=?,ecost=?,budget_id=?,orderstatus='complete'
where biblionumber=? and ordernumber=?");
- $sth->execute($quantrec,$datereceived,$invoiceid,$cost,$rrp,$biblionumber,$ordernumber);
+ $sth->execute($quantrec,$datereceived,$invoiceid,$cost,$rrp,$ecost,$budget_id,$biblionumber,$ordernumber);
$sth->finish;
}
return ($datereceived, $new_ordernumber);
UPDATE aqorders
SET quantityreceived = ?,
datereceived = ?,
- invoiceid = ?
+ invoiceid = ?,
+ orderstatus = 'ordered'
WHERE ordernumber = ?
};
$sth = $dbh->prepare($query);
}
$query = qq{
UPDATE aqorders
- SET quantity = ?
+ SET quantity = ?,
+ orderstatus = 'ordered'
WHERE ordernumber = ?
};
$sth = $dbh->prepare($query);
#------------------------------------------------------------#
-=head3 SearchOrder
+=head3 SearchOrders
-@results = &SearchOrder($search, $biblionumber, $complete);
+@results = &SearchOrders({
+ ordernumber => $ordernumber,
+ search => $search,
+ biblionumber => $biblionumber,
+ ean => $ean,
+ booksellerid => $booksellerid,
+ basketno => $basketno,
+ owner => $owner,
+ pending => $pending
+});
Searches for orders.
-C<$search> may take one of several forms: if it is an ISBN,
-C<&ordersearch> returns orders with that ISBN. If C<$search> is an
-order number, C<&ordersearch> returns orders with that order number
-and biblionumber C<$biblionumber>. Otherwise, C<$search> is considered
-to be a space-separated list of search terms; in this case, all of the
-terms must appear in the title (matching the beginning of title
-words).
+C<$owner> Finds order for the logged in user.
+C<$pending> Finds pending orders. Ignores completed and cancelled orders.
-If C<$complete> is C<yes>, the results will include only completed
-orders. In any case, C<&ordersearch> ignores cancelled orders.
-C<&ordersearch> returns an array.
-C<@results> is an array of references-to-hash with the following keys:
-
-=over 4
-
-=item C<author>
-
-=item C<seriestitle>
-
-=item C<branchcode>
-
-=item C<bookfundid>
-
-=back
+C<@results> is an array of references-to-hash with the keys are fields
+from aqorders, biblio, biblioitems and aqbasket tables.
=cut
-sub SearchOrder {
-#### -------- SearchOrder-------------------------------
- my ( $ordernumber, $search, $ean, $supplierid, $basket ) = @_;
+sub SearchOrders {
+ my ( $params ) = @_;
+ my $ordernumber = $params->{ordernumber};
+ my $search = $params->{search};
+ my $ean = $params->{ean};
+ my $booksellerid = $params->{booksellerid};
+ my $basketno = $params->{basketno};
+ my $basketname = $params->{basketname};
+ my $basketgroupname = $params->{basketgroupname};
+ my $owner = $params->{owner};
+ my $pending = $params->{pending};
my $dbh = C4::Context->dbh;
my @args = ();
- my $query =
- "SELECT *
- FROM aqorders
+ my $query = q{
+ SELECT aqbasket.basketno,
+ borrowers.surname,
+ borrowers.firstname,
+ biblio.*,
+ biblioitems.isbn,
+ biblioitems.biblioitemnumber,
+ aqbasket.closedate,
+ aqbasket.creationdate,
+ aqbasket.basketname,
+ aqorders.*
+ FROM aqorders
+ LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
+ LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
+ LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
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 (datecancellationprinted is NULL)";
+ WHERE (datecancellationprinted is NULL)
+ };
+
+ $query .= q{
+ AND (quantity > quantityreceived OR quantityreceived is NULL)
+ } if $pending;
+
+ my $userenv = C4::Context->userenv;
+ if ( C4::Context->preference("IndependentBranches") ) {
+ if ( ( $userenv ) and ( $userenv->{flags} != 1 ) ) {
+ $query .= q{
+ AND (
+ borrowers.branchcode = ?
+ OR borrowers.branchcode = ''
+ )
+ };
+ push @args, $userenv->{branch};
+ }
+ }
- if($ordernumber){
- $query .= " AND (aqorders.ordernumber=?)";
+ if ( $ordernumber ) {
+ $query .= ' AND (aqorders.ordernumber=?)';
push @args, $ordernumber;
}
- if($search){
- $query .= " AND (biblio.title like ? OR biblio.author LIKE ? OR biblioitems.isbn like ?)";
+ if( $search ) {
+ $query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)';
push @args, ("%$search%","%$search%","%$search%");
}
- if ($ean) {
- $query .= " AND biblioitems.ean = ?";
+ if ( $ean ) {
+ $query .= ' AND biblioitems.ean = ?';
push @args, $ean;
}
- if ($supplierid) {
- $query .= "AND aqbasket.booksellerid = ?";
- push @args, $supplierid;
+ if ( $booksellerid ) {
+ $query .= 'AND aqbasket.booksellerid = ?';
+ push @args, $booksellerid;
+ }
+ if( $basketno ) {
+ $query .= 'AND aqbasket.basketno = ?';
+ push @args, $basketno;
}
- if($basket){
- $query .= "AND aqorders.basketno = ?";
- push @args, $basket;
+ if( $basketname ) {
+ $query .= 'AND aqbasket.basketname LIKE ?';
+ push @args, "%$basketname%";
+ }
+ if( $basketgroupname ) {
+ $query .= ' AND aqbasketgroups.name LIKE ?';
+ push @args, "%$basketgroupname%";
+ }
+
+ if ( $owner ) {
+ $query .= ' AND aqbasket.authorisedby=? ';
+ push @args, $userenv->{'number'};
}
+ $query .= ' ORDER BY aqbasket.basketno';
+
my $sth = $dbh->prepare($query);
$sth->execute(@args);
- my $results = $sth->fetchall_arrayref({});
- $sth->finish;
- return $results;
+ return $sth->fetchall_arrayref({});
}
#------------------------------------------------------------#
my $dbh = C4::Context->dbh;
my $query = "
UPDATE aqorders
- SET datecancellationprinted=now()
+ SET datecancellationprinted=now(), orderstatus='cancelled'
WHERE biblionumber=? AND ordernumber=?
";
my $sth = $dbh->prepare($query);
}
+=head3 TransferOrder
+
+ my $newordernumber = TransferOrder($ordernumber, $basketno);
+
+Transfer an order line to a basket.
+Mark $ordernumber as cancelled with an internal note 'Cancelled and transfered
+to BOOKSELLER on DATE' and create new order with internal note
+'Transfered from BOOKSELLER on DATE'.
+Move all attached items to the new order.
+Received orders cannot be transfered.
+Return the ordernumber of created order.
+
+=cut
+
+sub TransferOrder {
+ my ($ordernumber, $basketno) = @_;
+
+ return unless ($ordernumber and $basketno);
+
+ my $order = GetOrder( $ordernumber );
+ return if $order->{datereceived};
+ my $basket = GetBasket($basketno);
+ return unless $basket;
+
+ my $dbh = C4::Context->dbh;
+ my ($query, $sth, $rv);
+
+ $query = qq{
+ UPDATE aqorders
+ SET datecancellationprinted = CAST(NOW() AS date)
+ WHERE ordernumber = ?
+ };
+ $sth = $dbh->prepare($query);
+ $rv = $sth->execute($ordernumber);
+
+ delete $order->{'ordernumber'};
+ $order->{'basketno'} = $basketno;
+ my $newordernumber;
+ (undef, $newordernumber) = NewOrder($order);
+
+ $query = qq{
+ UPDATE aqorders_items
+ SET ordernumber = ?
+ WHERE ordernumber = ?
+ };
+ $sth = $dbh->prepare($query);
+ $sth->execute($newordernumber, $ordernumber);
+
+ $query = q{
+ INSERT INTO aqorders_transfers (ordernumber_from, ordernumber_to)
+ VALUES (?, ?)
+ };
+ $sth = $dbh->prepare($query);
+ $sth->execute($ordernumber, $newordernumber);
+
+ return $newordernumber;
+}
+
=head2 FUNCTIONS ABOUT PARCELS
=cut
aqorders.listprice,
aqorders.rrp,
aqorders.ecost,
+ aqorders.gstrate,
biblio.title
FROM aqorders
LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
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 $having = "";
if ($dbdriver eq "mysql") {
$select .= "
- aqorders.quantity - IFNULL(aqorders.quantityreceived,0) AS quantity,
- (aqorders.quantity - IFNULL(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
+ 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
";
if ( defined $delay ) {
$from .= ' AND borrowers.branchcode LIKE ? ';
push @query_params, $branch;
}
+
+ if ( defined $estimateddeliverydatefrom or defined $estimateddeliverydateto ) {
+ $from .= ' AND aqbooksellers.deliverytime IS NOT NULL ';
+ }
if ( defined $estimateddeliverydatefrom ) {
- $from .= '
- AND aqbooksellers.deliverytime IS NOT NULL
- AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
+ $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
push @query_params, $estimateddeliverydatefrom;
}
- if ( defined $estimateddeliverydatefrom and defined $estimateddeliverydateto ) {
+ if ( defined $estimateddeliverydateto ) {
$from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
push @query_params, $estimateddeliverydateto;
- } elsif ( defined $estimateddeliverydatefrom ) {
+ }
+ if ( defined $estimateddeliverydatefrom and not defined $estimateddeliverydateto ) {
$from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
}
- if (C4::Context->preference("IndependantBranches")
+ if (C4::Context->preference("IndependentBranches")
&& C4::Context->userenv
&& C4::Context->userenv->{flags} != 1 ) {
$from .= ' AND borrowers.branchcode LIKE ? ';
push @query_params, C4::Context->userenv->{branch};
}
+ $from .= " AND orderstatus <> 'cancelled' ";
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);
my $basket = $params{basket};
my $booksellerinvoicenumber = $params{booksellerinvoicenumber};
my $basketgroupname = $params{basketgroupname};
+ my $budget = $params{budget};
+ my $orderstatus = $params{orderstatus};
+
my @order_loop;
my $total_qty = 0;
my $total_qtyreceived = 0;
my $dbh = C4::Context->dbh;
my $query ="
SELECT
- biblio.title,
- biblio.author,
- biblioitems.isbn,
- biblioitems.ean,
+ COALESCE(biblio.title, deletedbiblio.title) AS title,
+ COALESCE(biblio.author, deletedbiblio.author) AS author,
+ COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn,
+ COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean,
aqorders.basketno,
aqbasket.basketname,
aqbasket.basketgroupid,
aqorders.quantityreceived,
aqorders.ecost,
aqorders.ordernumber,
+ aqorders.invoiceid,
aqinvoices.invoicenumber,
aqbooksellers.id as id,
- aqorders.biblionumber
+ aqorders.biblionumber,
+ aqorders.orderstatus,
+ aqorders.parent_ordernumber,
+ aqbudgets.budget_name
+ ";
+ $query .= ", aqbudgets.budget_id AS budget" if defined $budget;
+ $query .= "
FROM aqorders
LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
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 biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
- LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid";
+ LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id
+ LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
+ LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.biblionumber
+ LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=aqorders.biblionumber
+ ";
+
+ if ( C4::Context->preference("IndependentBranches") ) {
+ $query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber";
+ }
- $query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber"
- if ( C4::Context->preference("IndependantBranches") );
+ $query .= " WHERE 1 ";
- $query .= " WHERE (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') ";
+ $query .= " AND (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') " if $orderstatus ne 'cancelled';
my @query_params = ();
$query .= " AND biblioitems.isbn LIKE ? ";
push @query_params, "%$isbn%";
}
- if ( defined $ean and $ean ) {
+ if ( $ean ) {
$query .= " AND biblioitems.ean = ? ";
push @query_params, "$ean";
}
push @query_params, "%$name%";
}
+ if ( $budget ) {
+ $query .= " AND aqbudgets.budget_id = ? ";
+ push @query_params, "$budget";
+ }
+
if ( $from_placed_on ) {
$query .= " AND creationdate >= ? ";
push @query_params, $from_placed_on;
push @query_params, $to_placed_on;
}
+ if ( defined $orderstatus and $orderstatus ne '') {
+ $query .= " AND aqorders.orderstatus = ? ";
+ push @query_params, "$orderstatus";
+ }
+
if ($basket) {
if ($basket =~ m/^\d+$/) {
$query .= " AND aqorders.basketno = ? ";
push @query_params, "%$basketgroupname%";
}
- if ( C4::Context->preference("IndependantBranches") ) {
+ 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;
- $total_qty += $line->{'quantity'};
- $total_qtyreceived += $line->{'quantityreceived'};
- $total_price += $line->{'quantity'} * $line->{'ecost'};
+ $total_qty += ( $line->{quantity} ) ? $line->{quantity} : 0;
+ $total_qtyreceived += ( $line->{quantityreceived} ) ? $line->{quantityreceived} : 0;
+ $total_price += ( $line->{quantity} and $line->{ecost} ) ? $line->{quantity} * $line->{ecost} : 0;
}
return \@order_loop, $total_qty, $total_price, $total_qtyreceived;
}
=head3 AddClaim
-=over 4
+=over
&AddClaim($ordernumber);
=back
=cut
+
sub AddClaim {
my ($ordernumber) = @_;
my $dbh = C4::Context->dbh;
}
if($args{shipmentdatefrom}) {
push @bind_strs, " aqinvoices.shipementdate >= ? ";
- push @bind_args, $args{shipementdatefrom};
+ push @bind_args, $args{shipmentdatefrom};
}
if($args{shipmentdateto}) {
push @bind_strs, " aqinvoices.shipementdate <= ? ";
- push @bind_args, $args{shipementdateto};
+ push @bind_args, $args{shipmentdateto};
}
if($args{billingdatefrom}) {
push @bind_strs, " aqinvoices.billingdate >= ? ";
sub GetInvoiceDetails {
my ($invoiceid) = @_;
- my $invoice;
- return unless $invoiceid;
+ if ( !defined $invoiceid ) {
+ carp 'GetInvoiceDetails called without an invoiceid';
+ return;
+ }
my $dbh = C4::Context->dbh;
- my $query = qq{
+ my $query = q{
SELECT aqinvoices.*, aqbooksellers.name AS suppliername
FROM aqinvoices
LEFT JOIN aqbooksellers ON aqinvoices.booksellerid = aqbooksellers.id
my $sth = $dbh->prepare($query);
$sth->execute($invoiceid);
- $invoice = $sth->fetchrow_hashref;
+ my $invoice = $sth->fetchrow_hashref;
- $query = qq{
- SELECT aqorders.*, biblio.*
+ $query = q{
+ SELECT aqorders.*, biblio.*, aqbasket.basketname
FROM aqorders
+ LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
WHERE invoiceid = ?
};
$sth->execute($invoiceid);
}
+=head3 DelInvoice
+
+ DelInvoice($invoiceid);
+
+Delete an invoice if there are no items attached to it.
+
+=cut
+
+sub DelInvoice {
+ my ($invoiceid) = @_;
+
+ return unless $invoiceid;
+
+ my $dbh = C4::Context->dbh;
+ my $query = qq{
+ SELECT COUNT(*)
+ FROM aqorders
+ WHERE invoiceid = ?
+ };
+ my $sth = $dbh->prepare($query);
+ $sth->execute($invoiceid);
+ my $res = $sth->fetchrow_arrayref;
+ if ( $res && $res->[0] == 0 ) {
+ $query = qq{
+ DELETE FROM aqinvoices
+ WHERE invoiceid = ?
+ };
+ my $sth = $dbh->prepare($query);
+ return ( $sth->execute($invoiceid) > 0 );
+ }
+ return;
+}
+
+=head3 MergeInvoices
+
+ MergeInvoices($invoiceid, \@sourceids);
+
+Merge the invoices identified by the IDs in \@sourceids into
+the invoice identified by $invoiceid.
+
+=cut
+
+sub MergeInvoices {
+ my ($invoiceid, $sourceids) = @_;
+
+ return unless $invoiceid;
+ foreach my $sourceid (@$sourceids) {
+ next if $sourceid == $invoiceid;
+ my $source = GetInvoiceDetails($sourceid);
+ foreach my $order (@{$source->{'orders'}}) {
+ $order->{'invoiceid'} = $invoiceid;
+ ModOrder($order);
+ }
+ DelInvoice($source->{'invoiceid'});
+ }
+ return;
+}
+
+=head3 GetBiblioCountByBasketno
+
+$biblio_count = &GetBiblioCountByBasketno($basketno);
+
+Looks up the biblio's count that has basketno value $basketno
+
+Returns a quantity
+
+=cut
+
+sub GetBiblioCountByBasketno {
+ my ($basketno) = @_;
+ my $dbh = C4::Context->dbh;
+ my $query = "
+ SELECT COUNT( DISTINCT( biblionumber ) )
+ FROM aqorders
+ WHERE basketno = ?
+ AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
+ ";
+
+ my $sth = $dbh->prepare($query);
+ $sth->execute($basketno);
+ return $sth->fetchrow;
+}
+
1;
__END__
projects / koha.git / blobdiff