1 package C4::Acquisition;
3 # Copyright 2000-2002 Katipo Communications
5 # This file is part of Koha.
7 # Koha is free software; you can redistribute it and/or modify it under the
8 # terms of the GNU General Public License as published by the Free Software
9 # Foundation; either version 2 of the License, or (at your option) any later
12 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
13 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
14 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
16 # You should have received a copy of the GNU General Public License along
17 # with Koha; if not, write to the Free Software Foundation, Inc.,
18 # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
25 use C4::Dates qw(format_date format_date_in_iso);
31 use C4::Bookseller qw(GetBookSellerFromId);
32 use C4::Templates qw(gettemplate);
37 use vars qw($VERSION @ISA @EXPORT);
40 # set the version for version checking
41 $VERSION = 3.07.00.049;
45 &GetBasket &NewBasket &CloseBasket &ReopenBasket &DelBasket &ModBasket
46 &GetBasketAsCSV &GetBasketGroupAsCSV
47 &GetBasketsByBookseller &GetBasketsByBasketgroup
48 &GetBasketsInfosByBookseller
50 &GetBasketUsers &ModBasketUsers
55 &ModBasketgroup &NewBasketgroup &DelBasketgroup &GetBasketgroup &CloseBasketgroup
56 &GetBasketgroups &ReOpenBasketgroup
58 &NewOrder &DelOrder &ModOrder &GetOrder &GetOrders &GetOrdersByBiblionumber
59 &GetLateOrders &GetOrderFromItemnumber
60 &SearchOrders &GetHistory &GetRecentAcqui
61 &ModReceiveOrder &CancelReceipt
62 &GetCancelledOrders &TransferOrder
63 &GetLastOrderNotReceivedFromSubscriptionid &GetLastOrderReceivedFromSubscriptionid
64 &NewOrderItem &ModItemOrder
66 &GetParcels &GetParcel
78 &GetItemnumbersFromOrder
81 &GetBiblioCountByBasketno
89 sub GetOrderFromItemnumber {
90 my ($itemnumber) = @_;
91 my $dbh = C4::Context->dbh;
94 SELECT * from aqorders LEFT JOIN aqorders_items
95 ON ( aqorders.ordernumber = aqorders_items.ordernumber )
96 WHERE itemnumber = ? |;
98 my $sth = $dbh->prepare($query);
102 $sth->execute($itemnumber);
104 my $order = $sth->fetchrow_hashref;
109 # Returns the itemnumber(s) associated with the ordernumber given in parameter
110 sub GetItemnumbersFromOrder {
111 my ($ordernumber) = @_;
112 my $dbh = C4::Context->dbh;
113 my $query = "SELECT itemnumber FROM aqorders_items WHERE ordernumber=?";
114 my $sth = $dbh->prepare($query);
115 $sth->execute($ordernumber);
118 while (my $order = $sth->fetchrow_hashref) {
119 push @tab, $order->{'itemnumber'};
133 C4::Acquisition - Koha functions for dealing with orders and acquisitions
141 The functions in this module deal with acquisitions, managing book
142 orders, basket and parcels.
146 =head2 FUNCTIONS ABOUT BASKETS
150 $aqbasket = &GetBasket($basketnumber);
152 get all basket informations in aqbasket for a given basket
154 B<returns:> informations for a given basket returned as a hashref.
160 my $dbh = C4::Context->dbh;
163 concat( b.firstname,' ',b.surname) AS authorisedbyname
165 LEFT JOIN borrowers b ON aqbasket.authorisedby=b.borrowernumber
168 my $sth=$dbh->prepare($query);
169 $sth->execute($basketno);
170 my $basket = $sth->fetchrow_hashref;
174 #------------------------------------------------------------#
178 $basket = &NewBasket( $booksellerid, $authorizedby, $basketname,
179 $basketnote, $basketbooksellernote, $basketcontractnumber, $deliveryplace, $billingplace );
181 Create a new basket in aqbasket table
185 =item C<$booksellerid> is a foreign key in the aqbasket table
187 =item C<$authorizedby> is the username of who created the basket
191 The other parameters are optional, see ModBasketHeader for more info on them.
196 my ( $booksellerid, $authorisedby, $basketname, $basketnote,
197 $basketbooksellernote, $basketcontractnumber, $deliveryplace,
198 $billingplace ) = @_;
199 my $dbh = C4::Context->dbh;
201 'INSERT INTO aqbasket (creationdate,booksellerid,authorisedby) '
202 . 'VALUES (now(),?,?)';
203 $dbh->do( $query, {}, $booksellerid, $authorisedby );
205 my $basket = $dbh->{mysql_insertid};
206 $basketname ||= q{}; # default to empty strings
208 $basketbooksellernote ||= q{};
209 ModBasketHeader( $basket, $basketname, $basketnote, $basketbooksellernote,
210 $basketcontractnumber, $booksellerid, $deliveryplace, $billingplace );
214 #------------------------------------------------------------#
218 &CloseBasket($basketno);
220 close a basket (becomes unmodifiable, except for receives)
226 my $dbh = C4::Context->dbh;
232 my $sth = $dbh->prepare($query);
233 $sth->execute($basketno);
235 my @orders = GetOrders($basketno);
236 foreach my $order (@orders) {
239 SET orderstatus = 'ordered'
240 WHERE ordernumber = ?;
242 $sth = $dbh->prepare($query);
243 $sth->execute($order->{'ordernumber'});
249 &ReopenBasket($basketno);
257 my $dbh = C4::Context->dbh;
263 my $sth = $dbh->prepare($query);
264 $sth->execute($basketno);
266 my @orders = GetOrders($basketno);
267 foreach my $order (@orders) {
270 SET orderstatus = 'new'
271 WHERE ordernumber = ?;
273 $sth = $dbh->prepare($query);
274 $sth->execute($order->{'ordernumber'});
278 #------------------------------------------------------------#
280 =head3 GetBasketAsCSV
282 &GetBasketAsCSV($basketno);
284 Export a basket as CSV
286 $cgi parameter is needed for column name translation
291 my ($basketno, $cgi) = @_;
292 my $basket = GetBasket($basketno);
293 my @orders = GetOrders($basketno);
294 my $contract = GetContract({
295 contractnumber => $basket->{'contractnumber'}
298 my $template = C4::Templates::gettemplate("acqui/csv/basket.tt", "intranet", $cgi);
301 foreach my $order (@orders) {
302 my $bd = GetBiblioData( $order->{'biblionumber'} );
304 contractname => $contract->{'contractname'},
305 ordernumber => $order->{'ordernumber'},
306 entrydate => $order->{'entrydate'},
307 isbn => $order->{'isbn'},
308 author => $bd->{'author'},
309 title => $bd->{'title'},
310 publicationyear => $bd->{'publicationyear'},
311 publishercode => $bd->{'publishercode'},
312 collectiontitle => $bd->{'collectiontitle'},
313 notes => $order->{'order_vendornote'},
314 quantity => $order->{'quantity'},
315 rrp => $order->{'rrp'},
316 deliveryplace => C4::Branch::GetBranchName( $basket->{'deliveryplace'} ),
317 billingplace => C4::Branch::GetBranchName( $basket->{'billingplace'} ),
320 contractname author title publishercode collectiontitle notes
321 deliveryplace billingplace
323 # Double the quotes to not be interpreted as a field end
324 $row->{$_} =~ s/"/""/g if $row->{$_};
330 if(defined $a->{publishercode} and defined $b->{publishercode}) {
331 $a->{publishercode} cmp $b->{publishercode};
335 $template->param(rows => \@rows);
337 return $template->output;
341 =head3 GetBasketGroupAsCSV
345 &GetBasketGroupAsCSV($basketgroupid);
347 Export a basket group as CSV
349 $cgi parameter is needed for column name translation
355 sub GetBasketGroupAsCSV {
356 my ($basketgroupid, $cgi) = @_;
357 my $baskets = GetBasketsByBasketgroup($basketgroupid);
359 my $template = C4::Templates::gettemplate('acqui/csv/basketgroup.tt', 'intranet', $cgi);
362 for my $basket (@$baskets) {
363 my @orders = GetOrders( $basket->{basketno} );
364 my $contract = GetContract({
365 contractnumber => $basket->{contractnumber}
367 my $bookseller = GetBookSellerFromId( $$basket{booksellerid} );
368 my $basketgroup = GetBasketgroup( $$basket{basketgroupid} );
370 foreach my $order (@orders) {
371 my $bd = GetBiblioData( $order->{'biblionumber'} );
373 clientnumber => $bookseller->{accountnumber},
374 basketname => $basket->{basketname},
375 ordernumber => $order->{ordernumber},
376 author => $bd->{author},
377 title => $bd->{title},
378 publishercode => $bd->{publishercode},
379 publicationyear => $bd->{publicationyear},
380 collectiontitle => $bd->{collectiontitle},
381 isbn => $order->{isbn},
382 quantity => $order->{quantity},
383 rrp => $order->{rrp},
384 discount => $bookseller->{discount},
385 ecost => $order->{ecost},
386 notes => $order->{order_vendornote},
387 entrydate => $order->{entrydate},
388 booksellername => $bookseller->{name},
389 bookselleraddress => $bookseller->{address1},
390 booksellerpostal => $bookseller->{postal},
391 contractnumber => $contract->{contractnumber},
392 contractname => $contract->{contractname},
393 basketgroupdeliveryplace => C4::Branch::GetBranchName( $basketgroup->{deliveryplace} ),
394 basketgroupbillingplace => C4::Branch::GetBranchName( $basketgroup->{billingplace} ),
395 basketdeliveryplace => C4::Branch::GetBranchName( $basket->{deliveryplace} ),
396 basketbillingplace => C4::Branch::GetBranchName( $basket->{billingplace} ),
399 basketname author title publishercode collectiontitle notes
400 booksellername bookselleraddress booksellerpostal contractname
401 basketgroupdeliveryplace basketgroupbillingplace
402 basketdeliveryplace basketbillingplace
404 # Double the quotes to not be interpreted as a field end
405 $row->{$_} =~ s/"/""/g if $row->{$_};
410 $template->param(rows => \@rows);
412 return $template->output;
416 =head3 CloseBasketgroup
418 &CloseBasketgroup($basketgroupno);
424 sub CloseBasketgroup {
425 my ($basketgroupno) = @_;
426 my $dbh = C4::Context->dbh;
427 my $sth = $dbh->prepare("
428 UPDATE aqbasketgroups
432 $sth->execute($basketgroupno);
435 #------------------------------------------------------------#
437 =head3 ReOpenBaskergroup($basketgroupno)
439 &ReOpenBaskergroup($basketgroupno);
445 sub ReOpenBasketgroup {
446 my ($basketgroupno) = @_;
447 my $dbh = C4::Context->dbh;
448 my $sth = $dbh->prepare("
449 UPDATE aqbasketgroups
453 $sth->execute($basketgroupno);
456 #------------------------------------------------------------#
461 &DelBasket($basketno);
463 Deletes the basket that has basketno field $basketno in the aqbasket table.
467 =item C<$basketno> is the primary key of the basket in the aqbasket table.
474 my ( $basketno ) = @_;
475 my $query = "DELETE FROM aqbasket WHERE basketno=?";
476 my $dbh = C4::Context->dbh;
477 my $sth = $dbh->prepare($query);
478 $sth->execute($basketno);
482 #------------------------------------------------------------#
486 &ModBasket($basketinfo);
488 Modifies a basket, using a hashref $basketinfo for the relevant information, only $basketinfo->{'basketno'} is required.
492 =item C<$basketno> is the primary key of the basket in the aqbasket table.
499 my $basketinfo = shift;
500 my $query = "UPDATE aqbasket SET ";
502 foreach my $key (keys %$basketinfo){
503 if ($key ne 'basketno'){
504 $query .= "$key=?, ";
505 push(@params, $basketinfo->{$key} || undef );
508 # get rid of the "," at the end of $query
509 if (substr($query, length($query)-2) eq ', '){
514 $query .= "WHERE basketno=?";
515 push(@params, $basketinfo->{'basketno'});
516 my $dbh = C4::Context->dbh;
517 my $sth = $dbh->prepare($query);
518 $sth->execute(@params);
523 #------------------------------------------------------------#
525 =head3 ModBasketHeader
527 &ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid);
529 Modifies a basket's header.
533 =item C<$basketno> is the "basketno" field in the "aqbasket" table;
535 =item C<$basketname> is the "basketname" field in the "aqbasket" table;
537 =item C<$note> is the "note" field in the "aqbasket" table;
539 =item C<$booksellernote> is the "booksellernote" field in the "aqbasket" table;
541 =item C<$contractnumber> is the "contractnumber" (foreign) key in the "aqbasket" table.
543 =item C<$booksellerid> is the id (foreign) key in the "aqbooksellers" table for the vendor.
545 =item C<$deliveryplace> is the "deliveryplace" field in the aqbasket table.
547 =item C<$billingplace> is the "billingplace" field in the aqbasket table.
553 sub ModBasketHeader {
554 my ($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid, $deliveryplace, $billingplace) = @_;
557 SET basketname=?, note=?, booksellernote=?, booksellerid=?, deliveryplace=?, billingplace=?
561 my $dbh = C4::Context->dbh;
562 my $sth = $dbh->prepare($query);
563 $sth->execute($basketname, $note, $booksellernote, $booksellerid, $deliveryplace, $billingplace, $basketno);
565 if ( $contractnumber ) {
566 my $query2 ="UPDATE aqbasket SET contractnumber=? WHERE basketno=?";
567 my $sth2 = $dbh->prepare($query2);
568 $sth2->execute($contractnumber,$basketno);
573 #------------------------------------------------------------#
575 =head3 GetBasketsByBookseller
577 @results = &GetBasketsByBookseller($booksellerid, $extra);
579 Returns a list of hashes of all the baskets that belong to bookseller 'booksellerid'.
583 =item C<$booksellerid> is the 'id' field of the bookseller in the aqbooksellers table
585 =item C<$extra> is the extra sql parameters, can be
587 $extra->{groupby}: group baskets by column
588 ex. $extra->{groupby} = aqbasket.basketgroupid
589 $extra->{orderby}: order baskets by column
590 $extra->{limit}: limit number of results (can be helpful for pagination)
596 sub GetBasketsByBookseller {
597 my ($booksellerid, $extra) = @_;
598 my $query = "SELECT * FROM aqbasket WHERE booksellerid=?";
600 if ($extra->{groupby}) {
601 $query .= " GROUP by $extra->{groupby}";
603 if ($extra->{orderby}){
604 $query .= " ORDER by $extra->{orderby}";
606 if ($extra->{limit}){
607 $query .= " LIMIT $extra->{limit}";
610 my $dbh = C4::Context->dbh;
611 my $sth = $dbh->prepare($query);
612 $sth->execute($booksellerid);
613 return $sth->fetchall_arrayref({});
616 =head3 GetBasketsInfosByBookseller
618 my $baskets = GetBasketsInfosByBookseller($supplierid, $allbaskets);
620 The optional second parameter allbaskets is a boolean allowing you to
621 select all baskets from the supplier; by default only active baskets (open or
622 closed but still something to receive) are returned.
624 Returns in a arrayref of hashref all about booksellers baskets, plus:
625 total_biblios: Number of distinct biblios in basket
626 total_items: Number of items in basket
627 expected_items: Number of non-received items in basket
631 sub GetBasketsInfosByBookseller {
632 my ($supplierid, $allbaskets) = @_;
634 return unless $supplierid;
636 my $dbh = C4::Context->dbh;
639 SUM(aqorders.quantity) AS total_items,
641 IF ( aqorders.orderstatus = 'cancelled', aqorders.quantity, 0 )
642 ) AS total_items_cancelled,
643 COUNT(DISTINCT aqorders.biblionumber) AS total_biblios,
645 IF(aqorders.datereceived IS NULL
646 AND aqorders.datecancellationprinted IS NULL
651 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
652 WHERE booksellerid = ?};
654 unless ( $allbaskets ) {
655 $query.=" AND (closedate IS NULL OR (aqorders.quantity > aqorders.quantityreceived AND datecancellationprinted IS NULL))";
657 $query.=" GROUP BY aqbasket.basketno";
659 my $sth = $dbh->prepare($query);
660 $sth->execute($supplierid);
661 my $baskets = $sth->fetchall_arrayref({});
663 # Retrieve the number of biblios cancelled
664 my $cancelled_biblios = $dbh->selectall_hashref( q|
665 SELECT COUNT(DISTINCT(biblionumber)) AS total_biblios_cancelled, aqbasket.basketno
667 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
668 WHERE booksellerid = ?
669 AND aqorders.orderstatus = 'cancelled'
670 GROUP BY aqbasket.basketno
671 |, 'basketno', {}, $supplierid );
673 $_->{total_biblios_cancelled} = $cancelled_biblios->{$_->{basketno}}{total_biblios_cancelled} || 0
679 =head3 GetBasketUsers
681 $basketusers_ids = &GetBasketUsers($basketno);
683 Returns a list of all borrowernumbers that are in basket users list
688 my $basketno = shift;
690 return unless $basketno;
693 SELECT borrowernumber
697 my $dbh = C4::Context->dbh;
698 my $sth = $dbh->prepare($query);
699 $sth->execute($basketno);
700 my $results = $sth->fetchall_arrayref( {} );
703 foreach (@$results) {
704 push @borrowernumbers, $_->{'borrowernumber'};
707 return @borrowernumbers;
710 =head3 ModBasketUsers
712 my @basketusers_ids = (1, 2, 3);
713 &ModBasketUsers($basketno, @basketusers_ids);
715 Delete all users from basket users list, and add users in C<@basketusers_ids>
721 my ($basketno, @basketusers_ids) = @_;
723 return unless $basketno;
725 my $dbh = C4::Context->dbh;
727 DELETE FROM aqbasketusers
730 my $sth = $dbh->prepare($query);
731 $sth->execute($basketno);
734 INSERT INTO aqbasketusers (basketno, borrowernumber)
737 $sth = $dbh->prepare($query);
738 foreach my $basketuser_id (@basketusers_ids) {
739 $sth->execute($basketno, $basketuser_id);
744 =head3 CanUserManageBasket
746 my $bool = CanUserManageBasket($borrower, $basket[, $userflags]);
747 my $bool = CanUserManageBasket($borrowernumber, $basketno[, $userflags]);
749 Check if a borrower can manage a basket, according to system preference
750 AcqViewBaskets, user permissions and basket properties (creator, users list,
753 First parameter can be either a borrowernumber or a hashref as returned by
754 C4::Members::GetMember.
756 Second parameter can be either a basketno or a hashref as returned by
757 C4::Acquisition::GetBasket.
759 The third parameter is optional. If given, it should be a hashref as returned
760 by C4::Auth::getuserflags. If not, getuserflags is called.
762 If user is authorised to manage basket, returns 1.
767 sub CanUserManageBasket {
768 my ($borrower, $basket, $userflags) = @_;
770 if (!ref $borrower) {
771 $borrower = C4::Members::GetMember(borrowernumber => $borrower);
774 $basket = GetBasket($basket);
777 return 0 unless ($basket and $borrower);
779 my $borrowernumber = $borrower->{borrowernumber};
780 my $basketno = $basket->{basketno};
782 my $AcqViewBaskets = C4::Context->preference('AcqViewBaskets');
784 if (!defined $userflags) {
785 my $dbh = C4::Context->dbh;
786 my $sth = $dbh->prepare("SELECT flags FROM borrowers WHERE borrowernumber = ?");
787 $sth->execute($borrowernumber);
788 my ($flags) = $sth->fetchrow_array;
791 $userflags = C4::Auth::getuserflags($flags, $borrower->{userid}, $dbh);
794 unless ($userflags->{superlibrarian}
795 || (ref $userflags->{acquisition} && $userflags->{acquisition}->{order_manage_all})
796 || (!ref $userflags->{acquisition} && $userflags->{acquisition}))
798 if (not exists $userflags->{acquisition}) {
802 if ( (ref $userflags->{acquisition} && !$userflags->{acquisition}->{order_manage})
803 || (!ref $userflags->{acquisition} && !$userflags->{acquisition}) ) {
807 if ($AcqViewBaskets eq 'user'
808 && $basket->{authorisedby} != $borrowernumber
809 && grep($borrowernumber, GetBasketUsers($basketno)) == 0) {
813 if ($AcqViewBaskets eq 'branch' && defined $basket->{branch}
814 && $basket->{branch} ne $borrower->{branchcode}) {
822 #------------------------------------------------------------#
824 =head3 GetBasketsByBasketgroup
826 $baskets = &GetBasketsByBasketgroup($basketgroupid);
828 Returns a reference to all baskets that belong to basketgroup $basketgroupid.
832 sub GetBasketsByBasketgroup {
833 my $basketgroupid = shift;
835 SELECT *, aqbasket.booksellerid as booksellerid
837 LEFT JOIN aqcontract USING(contractnumber) WHERE basketgroupid=?
839 my $dbh = C4::Context->dbh;
840 my $sth = $dbh->prepare($query);
841 $sth->execute($basketgroupid);
842 return $sth->fetchall_arrayref({});
845 #------------------------------------------------------------#
847 =head3 NewBasketgroup
849 $basketgroupid = NewBasketgroup(\%hashref);
851 Adds a basketgroup to the aqbasketgroups table, and add the initial baskets to it.
853 $hashref->{'booksellerid'} is the 'id' field of the bookseller in the aqbooksellers table,
855 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
857 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
859 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
861 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
863 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
865 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
867 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
872 my $basketgroupinfo = shift;
873 die "booksellerid is required to create a basketgroup" unless $basketgroupinfo->{'booksellerid'};
874 my $query = "INSERT INTO aqbasketgroups (";
876 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
877 if ( defined $basketgroupinfo->{$field} ) {
878 $query .= "$field, ";
879 push(@params, $basketgroupinfo->{$field});
882 $query .= "booksellerid) VALUES (";
887 push(@params, $basketgroupinfo->{'booksellerid'});
888 my $dbh = C4::Context->dbh;
889 my $sth = $dbh->prepare($query);
890 $sth->execute(@params);
891 my $basketgroupid = $dbh->{'mysql_insertid'};
892 if( $basketgroupinfo->{'basketlist'} ) {
893 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
894 my $query2 = "UPDATE aqbasket SET basketgroupid=? WHERE basketno=?";
895 my $sth2 = $dbh->prepare($query2);
896 $sth2->execute($basketgroupid, $basketno);
899 return $basketgroupid;
902 #------------------------------------------------------------#
904 =head3 ModBasketgroup
906 ModBasketgroup(\%hashref);
908 Modifies a basketgroup in the aqbasketgroups table, and add the baskets to it.
910 $hashref->{'id'} is the 'id' field of the basketgroup in the aqbasketgroup table, this parameter is mandatory,
912 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
914 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
916 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
918 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
920 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
922 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
924 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
929 my $basketgroupinfo = shift;
930 die "basketgroup id is required to edit a basketgroup" unless $basketgroupinfo->{'id'};
931 my $dbh = C4::Context->dbh;
932 my $query = "UPDATE aqbasketgroups SET ";
934 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
935 if ( defined $basketgroupinfo->{$field} ) {
936 $query .= "$field=?, ";
937 push(@params, $basketgroupinfo->{$field});
942 $query .= " WHERE id=?";
943 push(@params, $basketgroupinfo->{'id'});
944 my $sth = $dbh->prepare($query);
945 $sth->execute(@params);
947 $sth = $dbh->prepare('UPDATE aqbasket SET basketgroupid = NULL WHERE basketgroupid = ?');
948 $sth->execute($basketgroupinfo->{'id'});
950 if($basketgroupinfo->{'basketlist'} && @{$basketgroupinfo->{'basketlist'}}){
951 $sth = $dbh->prepare("UPDATE aqbasket SET basketgroupid=? WHERE basketno=?");
952 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
953 $sth->execute($basketgroupinfo->{'id'}, $basketno);
959 #------------------------------------------------------------#
961 =head3 DelBasketgroup
963 DelBasketgroup($basketgroupid);
965 Deletes a basketgroup in the aqbasketgroups table, and removes the reference to it from the baskets,
969 =item C<$basketgroupid> is the 'id' field of the basket in the aqbasketgroup table
976 my $basketgroupid = shift;
977 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
978 my $query = "DELETE FROM aqbasketgroups WHERE id=?";
979 my $dbh = C4::Context->dbh;
980 my $sth = $dbh->prepare($query);
981 $sth->execute($basketgroupid);
985 #------------------------------------------------------------#
988 =head2 FUNCTIONS ABOUT ORDERS
990 =head3 GetBasketgroup
992 $basketgroup = &GetBasketgroup($basketgroupid);
994 Returns a reference to the hash containing all information about the basketgroup.
999 my $basketgroupid = shift;
1000 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
1001 my $dbh = C4::Context->dbh;
1002 my $result_set = $dbh->selectall_arrayref(
1003 'SELECT * FROM aqbasketgroups WHERE id=?',
1007 return $result_set->[0]; # id is unique
1010 #------------------------------------------------------------#
1012 =head3 GetBasketgroups
1014 $basketgroups = &GetBasketgroups($booksellerid);
1016 Returns a reference to the array of all the basketgroups of bookseller $booksellerid.
1020 sub GetBasketgroups {
1021 my $booksellerid = shift;
1022 die 'bookseller id is required to edit a basketgroup' unless $booksellerid;
1023 my $query = 'SELECT * FROM aqbasketgroups WHERE booksellerid=? ORDER BY id DESC';
1024 my $dbh = C4::Context->dbh;
1025 my $sth = $dbh->prepare($query);
1026 $sth->execute($booksellerid);
1027 return $sth->fetchall_arrayref({});
1030 #------------------------------------------------------------#
1032 =head2 FUNCTIONS ABOUT ORDERS
1036 @orders = &GetOrders($basketnumber, $orderby);
1038 Looks up the pending (non-cancelled) orders with the given basket
1039 number. If C<$booksellerID> is non-empty, only orders from that seller
1043 C<&basket> returns a two-element array. C<@orders> is an array of
1044 references-to-hash, whose keys are the fields from the aqorders,
1045 biblio, and biblioitems tables in the Koha database.
1050 my ( $basketno, $orderby ) = @_;
1051 return () unless $basketno;
1052 my $dbh = C4::Context->dbh;
1054 SELECT biblio.*,biblioitems.*,
1057 aqorders_transfers.ordernumber_from AS transferred_from,
1058 aqorders_transfers.timestamp AS transferred_from_timestamp
1060 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1061 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1062 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1063 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1065 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
1068 $orderby = "biblioitems.publishercode,biblio.title" unless $orderby;
1069 $query .= " ORDER BY $orderby";
1071 $dbh->selectall_arrayref( $query, { Slice => {} }, $basketno );
1072 return @{$result_set};
1076 #------------------------------------------------------------#
1077 =head3 GetOrdersByBiblionumber
1079 @orders = &GetOrdersByBiblionumber($biblionumber);
1081 Looks up the orders with linked to a specific $biblionumber, including
1082 cancelled orders and received orders.
1085 C<@orders> is an array of references-to-hash, whose keys are the
1086 fields from the aqorders, biblio, and biblioitems tables in the Koha database.
1090 sub GetOrdersByBiblionumber {
1091 my $biblionumber = shift;
1092 return unless $biblionumber;
1093 my $dbh = C4::Context->dbh;
1095 SELECT biblio.*,biblioitems.*,
1099 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1100 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1101 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1102 WHERE aqorders.biblionumber=?
1105 $dbh->selectall_arrayref( $query, { Slice => {} }, $biblionumber );
1106 return @{$result_set};
1110 #------------------------------------------------------------#
1114 $order = &GetOrder($ordernumber);
1116 Looks up an order by order number.
1118 Returns a reference-to-hash describing the order. The keys of
1119 C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database.
1124 my ($ordernumber) = @_;
1125 return unless $ordernumber;
1127 my $dbh = C4::Context->dbh;
1128 my $query = qq{SELECT
1132 aqbasket.basketname,
1133 borrowers.branchcode,
1134 biblioitems.publicationyear,
1135 biblio.copyrightdate,
1136 biblioitems.editionstatement,
1140 biblioitems.publishercode,
1141 aqorders.rrp AS unitpricesupplier,
1142 aqorders.ecost AS unitpricelib,
1143 aqorders.claims_count AS claims_count,
1144 aqorders.claimed_date AS claimed_date,
1145 aqbudgets.budget_name AS budget,
1146 aqbooksellers.name AS supplier,
1147 aqbooksellers.id AS supplierid,
1148 biblioitems.publishercode AS publisher,
1149 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
1150 DATE(aqbasket.closedate) AS orderdate,
1151 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity_to_receive,
1152 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1153 DATEDIFF(CURDATE( ),closedate) AS latesince
1154 FROM aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1155 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1156 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
1157 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
1158 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1159 WHERE aqorders.basketno = aqbasket.basketno
1162 $dbh->selectall_arrayref( $query, { Slice => {} }, $ordernumber );
1164 # result_set assumed to contain 1 match
1165 return $result_set->[0];
1168 =head3 GetLastOrderNotReceivedFromSubscriptionid
1170 $order = &GetLastOrderNotReceivedFromSubscriptionid($subscriptionid);
1172 Returns a reference-to-hash describing the last order not received for a subscription.
1176 sub GetLastOrderNotReceivedFromSubscriptionid {
1177 my ( $subscriptionid ) = @_;
1178 my $dbh = C4::Context->dbh;
1180 SELECT * FROM aqorders
1181 LEFT JOIN subscription
1182 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1183 WHERE aqorders.subscriptionid = ?
1184 AND aqorders.datereceived IS NULL
1188 $dbh->selectall_arrayref( $query, { Slice => {} }, $subscriptionid );
1190 # result_set assumed to contain 1 match
1191 return $result_set->[0];
1194 =head3 GetLastOrderReceivedFromSubscriptionid
1196 $order = &GetLastOrderReceivedFromSubscriptionid($subscriptionid);
1198 Returns a reference-to-hash describing the last order received for a subscription.
1202 sub GetLastOrderReceivedFromSubscriptionid {
1203 my ( $subscriptionid ) = @_;
1204 my $dbh = C4::Context->dbh;
1206 SELECT * FROM aqorders
1207 LEFT JOIN subscription
1208 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1209 WHERE aqorders.subscriptionid = ?
1210 AND aqorders.datereceived =
1212 SELECT MAX( aqorders.datereceived )
1214 LEFT JOIN subscription
1215 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1216 WHERE aqorders.subscriptionid = ?
1217 AND aqorders.datereceived IS NOT NULL
1219 ORDER BY ordernumber DESC
1223 $dbh->selectall_arrayref( $query, { Slice => {} }, $subscriptionid, $subscriptionid );
1225 # result_set assumed to contain 1 match
1226 return $result_set->[0];
1231 #------------------------------------------------------------#
1235 &NewOrder(\%hashref);
1237 Adds a new order to the database. Any argument that isn't described
1238 below is the new value of the field with the same name in the aqorders
1239 table of the Koha database.
1243 =item $hashref->{'basketno'} is the basketno foreign key in aqorders, it is mandatory
1245 =item $hashref->{'ordernumber'} is a "minimum order number."
1247 =item $hashref->{'budgetdate'} is effectively ignored.
1248 If it's undef (anything false) or the string 'now', the current day is used.
1249 Else, the upcoming July 1st is used.
1251 =item $hashref->{'subscription'} may be either "yes", or anything else for "no".
1253 =item $hashref->{'uncertainprice'} may be 0 for "the price is known" or 1 for "the price is uncertain"
1255 =item defaults entrydate to Now
1257 The following keys are used: "biblionumber", "title", "basketno", "quantity", "order_vendornote", "order_internalnote", "rrp", "ecost", "gstrate", "unitprice", "subscription", "sort1", "sort2", "booksellerinvoicenumber", "listprice", "budgetdate", "purchaseordernumber", "branchcode", "booksellerinvoicenumber", "budget_id".
1264 my $orderinfo = shift;
1266 my $dbh = C4::Context->dbh;
1270 # if these parameters are missing, we can't continue
1271 for my $key (qw/basketno quantity biblionumber budget_id/) {
1272 croak "Mandatory parameter $key missing" unless $orderinfo->{$key};
1275 $orderinfo->{'entrydate'} ||= C4::Dates->new()->output("iso");
1276 if (!$orderinfo->{quantityreceived}) {
1277 $orderinfo->{quantityreceived} = 0;
1280 # get only the columns of Aqorder
1281 my $schema = Koha::Database->new()->schema;
1282 my $columns = ' '.join(' ', $schema->source('Aqorder')->columns).' ';
1283 my $new_order = { map { $columns =~ / $_ / ? ($_ => $orderinfo->{$_}) : () } keys(%$orderinfo) };
1285 my $rs = $schema->resultset('Aqorder');
1286 my $ordernumber = $rs->create($new_order)->id;
1287 if (not $new_order->{parent_ordernumber}) {
1288 my $sth = $dbh->prepare("
1290 SET parent_ordernumber = ordernumber
1291 WHERE ordernumber = ?
1293 $sth->execute($ordernumber);
1295 return ( $new_order->{'basketno'}, $ordernumber );
1300 #------------------------------------------------------------#
1309 my ($itemnumber, $ordernumber) = @_;
1310 my $dbh = C4::Context->dbh;
1312 INSERT INTO aqorders_items
1313 (itemnumber, ordernumber)
1316 my $sth = $dbh->prepare($query);
1317 $sth->execute( $itemnumber, $ordernumber);
1320 #------------------------------------------------------------#
1324 &ModOrder(\%hashref);
1326 Modifies an existing order. Updates the order with order number
1327 $hashref->{'ordernumber'} and biblionumber $hashref->{'biblionumber'}. All
1328 other keys of the hash update the fields with the same name in the aqorders
1329 table of the Koha database.
1334 my $orderinfo = shift;
1336 die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq '' ;
1337 die "Biblionumber is required" if $orderinfo->{'biblionumber'} eq '';
1339 my $dbh = C4::Context->dbh;
1342 # update uncertainprice to an integer, just in case (under FF, checked boxes have the value "ON" by default)
1343 $orderinfo->{uncertainprice}=1 if $orderinfo->{uncertainprice};
1345 # delete($orderinfo->{'branchcode'});
1346 # the hash contains a lot of entries not in aqorders, so get the columns ...
1347 my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;");
1349 my $colnames = $sth->{NAME};
1350 #FIXME Be careful. If aqorders would have columns with diacritics,
1351 #you should need to decode what you get back from NAME.
1352 #See report 10110 and guided_reports.pl
1353 my $query = "UPDATE aqorders SET ";
1355 foreach my $orderinfokey (grep(!/ordernumber/, keys %$orderinfo)){
1356 # ... and skip hash entries that are not in the aqorders table
1357 # FIXME : probably not the best way to do it (would be better to have a correct hash)
1358 next unless grep(/^$orderinfokey$/, @$colnames);
1359 $query .= "$orderinfokey=?, ";
1360 push(@params, $orderinfo->{$orderinfokey});
1363 $query .= "timestamp=NOW() WHERE ordernumber=?";
1364 push(@params, $orderinfo->{'ordernumber'} );
1365 $sth = $dbh->prepare($query);
1366 $sth->execute(@params);
1370 #------------------------------------------------------------#
1374 ModItemOrder($itemnumber, $ordernumber);
1376 Modifies the ordernumber of an item in aqorders_items.
1381 my ($itemnumber, $ordernumber) = @_;
1383 return unless ($itemnumber and $ordernumber);
1385 my $dbh = C4::Context->dbh;
1387 UPDATE aqorders_items
1389 WHERE itemnumber = ?
1391 my $sth = $dbh->prepare($query);
1392 return $sth->execute($ordernumber, $itemnumber);
1395 #------------------------------------------------------------#
1397 =head3 GetCancelledOrders
1399 my @orders = GetCancelledOrders($basketno, $orderby);
1401 Returns cancelled orders for a basket
1405 sub GetCancelledOrders {
1406 my ( $basketno, $orderby ) = @_;
1408 return () unless $basketno;
1410 my $dbh = C4::Context->dbh;
1417 aqorders_transfers.ordernumber_to AS transferred_to,
1418 aqorders_transfers.timestamp AS transferred_to_timestamp
1420 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1421 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1422 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1423 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber
1425 AND (datecancellationprinted IS NOT NULL
1426 AND datecancellationprinted <> '0000-00-00')
1429 $orderby = "aqorders.datecancellationprinted desc, aqorders.timestamp desc"
1431 $query .= " ORDER BY $orderby";
1432 my $sth = $dbh->prepare($query);
1433 $sth->execute($basketno);
1434 my $results = $sth->fetchall_arrayref( {} );
1440 #------------------------------------------------------------#
1442 =head3 ModReceiveOrder
1445 biblionumber => $biblionumber,
1446 ordernumber => $ordernumber,
1447 quantityreceived => $quantityreceived,
1451 invoiceid => $invoiceid,
1453 budget_id => $budget_id,
1454 datereceived => $datereceived,
1455 received_itemnumbers => \@received_itemnumbers,
1456 order_internalnote => $order_internalnote,
1457 order_vendornote => $order_vendornote,
1460 Updates an order, to reflect the fact that it was received, at least
1461 in part. All arguments not mentioned below update the fields with the
1462 same name in the aqorders table of the Koha database.
1464 If a partial order is received, splits the order into two.
1466 Updates the order with bibilionumber C<$biblionumber> and ordernumber
1472 sub ModReceiveOrder {
1473 my ( $params ) = @_;
1474 my $biblionumber = $params->{biblionumber};
1475 my $ordernumber = $params->{ordernumber};
1476 my $quantrec = $params->{quantityreceived};
1477 my $user = $params->{user};
1478 my $cost = $params->{cost};
1479 my $ecost = $params->{ecost};
1480 my $invoiceid = $params->{invoiceid};
1481 my $rrp = $params->{rrp};
1482 my $budget_id = $params->{budget_id};
1483 my $datereceived = $params->{datereceived};
1484 my $received_items = $params->{received_items};
1485 my $order_internalnote = $params->{order_internalnote};
1486 my $order_vendornote = $params->{order_vendornote};
1488 my $dbh = C4::Context->dbh;
1489 $datereceived = C4::Dates->output('iso') unless $datereceived;
1490 my $suggestionid = GetSuggestionFromBiblionumber( $biblionumber );
1491 if ($suggestionid) {
1492 ModSuggestion( {suggestionid=>$suggestionid,
1493 STATUS=>'AVAILABLE',
1494 biblionumber=> $biblionumber}
1498 my $result_set = $dbh->selectall_arrayref(
1499 q{SELECT * FROM aqorders WHERE biblionumber=? AND aqorders.ordernumber=?},
1500 { Slice => {} }, $biblionumber, $ordernumber
1503 # we assume we have a unique order
1504 my $order = $result_set->[0];
1506 my $new_ordernumber = $ordernumber;
1507 if ( $order->{quantity} > $quantrec ) {
1508 # Split order line in two parts: the first is the original order line
1509 # without received items (the quantity is decreased),
1510 # the second part is a new order line with quantity=quantityrec
1511 # (entirely received)
1515 orderstatus = 'partial'|;
1516 $query .= q|, order_internalnote = ?| if defined $order_internalnote;
1517 $query .= q|, order_vendornote = ?| if defined $order_vendornote;
1518 $query .= q| WHERE ordernumber = ?|;
1519 my $sth = $dbh->prepare($query);
1522 $order->{quantity} - $quantrec,
1523 ( defined $order_internalnote ? $order_internalnote : () ),
1524 ( defined $order_vendornote ? $order_vendornote : () ),
1528 delete $order->{'ordernumber'};
1529 $order->{'budget_id'} = ( $budget_id || $order->{'budget_id'} );
1530 $order->{'quantity'} = $quantrec;
1531 $order->{'quantityreceived'} = $quantrec;
1532 $order->{'datereceived'} = $datereceived;
1533 $order->{'invoiceid'} = $invoiceid;
1534 $order->{'unitprice'} = $cost;
1535 $order->{'rrp'} = $rrp;
1536 $order->{ecost} = $ecost;
1537 $order->{'orderstatus'} = 'complete';
1539 ( $basketno, $new_ordernumber ) = NewOrder($order);
1541 if ($received_items) {
1542 foreach my $itemnumber (@$received_items) {
1543 ModItemOrder($itemnumber, $new_ordernumber);
1549 set quantityreceived=?,datereceived=?,invoiceid=?,
1550 unitprice=?,rrp=?,ecost=?,budget_id=?,orderstatus='complete'|;
1551 $query .= q|, order_internalnote = ?| if defined $order_internalnote;
1552 $query .= q|, order_vendornote = ?| if defined $order_vendornote;
1553 $query .= q| where biblionumber=? and ordernumber=?|;
1554 my $sth = $dbh->prepare( $query );
1563 ( defined $order_internalnote ? $order_internalnote : () ),
1564 ( defined $order_vendornote ? $order_vendornote : () ),
1569 return ($datereceived, $new_ordernumber);
1572 =head3 CancelReceipt
1574 my $parent_ordernumber = CancelReceipt($ordernumber);
1576 Cancel an order line receipt and update the parent order line, as if no
1578 If items are created at receipt (AcqCreateItem = receiving) then delete
1584 my $ordernumber = shift;
1586 return unless $ordernumber;
1588 my $dbh = C4::Context->dbh;
1590 SELECT datereceived, parent_ordernumber, quantity
1592 WHERE ordernumber = ?
1594 my $sth = $dbh->prepare($query);
1595 $sth->execute($ordernumber);
1596 my $order = $sth->fetchrow_hashref;
1598 warn "CancelReceipt: order $ordernumber does not exist";
1601 unless($order->{'datereceived'}) {
1602 warn "CancelReceipt: order $ordernumber is not received";
1606 my $parent_ordernumber = $order->{'parent_ordernumber'};
1608 my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1610 if($parent_ordernumber == $ordernumber || not $parent_ordernumber) {
1611 # The order line has no parent, just mark it as not received
1614 SET quantityreceived = ?,
1617 orderstatus = 'ordered'
1618 WHERE ordernumber = ?
1620 $sth = $dbh->prepare($query);
1621 $sth->execute(0, undef, undef, $ordernumber);
1622 _cancel_items_receipt( $ordernumber );
1624 # The order line has a parent, increase parent quantity and delete
1627 SELECT quantity, datereceived
1629 WHERE ordernumber = ?
1631 $sth = $dbh->prepare($query);
1632 $sth->execute($parent_ordernumber);
1633 my $parent_order = $sth->fetchrow_hashref;
1634 unless($parent_order) {
1635 warn "Parent order $parent_ordernumber does not exist.";
1638 if($parent_order->{'datereceived'}) {
1639 warn "CancelReceipt: parent order is received.".
1640 " Can't cancel receipt.";
1646 orderstatus = 'ordered'
1647 WHERE ordernumber = ?
1649 $sth = $dbh->prepare($query);
1650 my $rv = $sth->execute(
1651 $order->{'quantity'} + $parent_order->{'quantity'},
1655 warn "Cannot update parent order line, so do not cancel".
1659 _cancel_items_receipt( $ordernumber, $parent_ordernumber );
1662 DELETE FROM aqorders
1663 WHERE ordernumber = ?
1665 $sth = $dbh->prepare($query);
1666 $sth->execute($ordernumber);
1670 if(C4::Context->preference('AcqCreateItem') eq 'ordering') {
1671 my @affects = split q{\|}, C4::Context->preference("AcqItemSetSubfieldsWhenReceiptIsCancelled");
1673 for my $in ( @itemnumbers ) {
1674 my $biblionumber = C4::Biblio::GetBiblionumberFromItemnumber( $in );
1675 my $frameworkcode = GetFrameworkCode($biblionumber);
1676 my ( $itemfield ) = GetMarcFromKohaField( 'items.itemnumber', $frameworkcode );
1677 my $item = C4::Items::GetMarcItem( $biblionumber, $in );
1678 for my $affect ( @affects ) {
1679 my ( $sf, $v ) = split q{=}, $affect, 2;
1680 foreach ( $item->field($itemfield) ) {
1681 $_->update( $sf => $v );
1684 C4::Items::ModItemFromMarc( $item, $biblionumber, $in );
1689 return $parent_ordernumber;
1692 sub _cancel_items_receipt {
1693 my ( $ordernumber, $parent_ordernumber ) = @_;
1694 $parent_ordernumber ||= $ordernumber;
1696 my @itemnumbers = GetItemnumbersFromOrder($ordernumber);
1697 if(C4::Context->preference('AcqCreateItem') eq 'receiving') {
1698 # Remove items that were created at receipt
1700 DELETE FROM items, aqorders_items
1701 USING items, aqorders_items
1702 WHERE items.itemnumber = ? AND aqorders_items.itemnumber = ?
1704 my $dbh = C4::Context->dbh;
1705 my $sth = $dbh->prepare($query);
1706 foreach my $itemnumber (@itemnumbers) {
1707 $sth->execute($itemnumber, $itemnumber);
1711 foreach my $itemnumber (@itemnumbers) {
1712 ModItemOrder($itemnumber, $parent_ordernumber);
1717 #------------------------------------------------------------#
1721 @results = &SearchOrders({
1722 ordernumber => $ordernumber,
1724 biblionumber => $biblionumber,
1726 booksellerid => $booksellerid,
1727 basketno => $basketno,
1733 Searches for orders.
1735 C<$owner> Finds order for the logged in user.
1736 C<$pending> Finds pending orders. Ignores completed and cancelled orders.
1737 C<$ordered> Finds orders to receive only (status 'ordered' or 'partial').
1740 C<@results> is an array of references-to-hash with the keys are fields
1741 from aqorders, biblio, biblioitems and aqbasket tables.
1746 my ( $params ) = @_;
1747 my $ordernumber = $params->{ordernumber};
1748 my $search = $params->{search};
1749 my $ean = $params->{ean};
1750 my $booksellerid = $params->{booksellerid};
1751 my $basketno = $params->{basketno};
1752 my $basketname = $params->{basketname};
1753 my $basketgroupname = $params->{basketgroupname};
1754 my $owner = $params->{owner};
1755 my $pending = $params->{pending};
1756 my $ordered = $params->{ordered};
1757 my $biblionumber = $params->{biblionumber};
1758 my $budget_id = $params->{budget_id};
1760 my $dbh = C4::Context->dbh;
1763 SELECT aqbasket.basketno,
1765 borrowers.firstname,
1768 biblioitems.biblioitemnumber,
1769 aqbasket.authorisedby,
1770 aqbasket.booksellerid,
1772 aqbasket.creationdate,
1773 aqbasket.basketname,
1774 aqbasketgroups.id as basketgroupid,
1775 aqbasketgroups.name as basketgroupname,
1778 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
1779 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
1780 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1781 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1782 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
1785 # If we search on ordernumber, we retrieve the transfered order if a transfer has been done.
1787 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1791 WHERE (datecancellationprinted is NULL)
1794 if ( $pending or $ordered ) {
1795 $query .= q{ AND (quantity > quantityreceived OR quantityreceived is NULL)};
1798 $query .= q{ AND aqorders.orderstatus IN ( "ordered", "partial" )};
1801 my $userenv = C4::Context->userenv;
1802 if ( C4::Context->preference("IndependentBranches") ) {
1803 unless ( C4::Context->IsSuperLibrarian() ) {
1806 borrowers.branchcode = ?
1807 OR borrowers.branchcode = ''
1810 push @args, $userenv->{branch};
1814 if ( $ordernumber ) {
1815 $query .= ' AND ( aqorders.ordernumber = ? OR aqorders_transfers.ordernumber_from = ? ) ';
1816 push @args, ( $ordernumber, $ordernumber );
1818 if ( $biblionumber ) {
1819 $query .= 'AND aqorders.biblionumber = ?';
1820 push @args, $biblionumber;
1823 $query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)';
1824 push @args, ("%$search%","%$search%","%$search%");
1827 $query .= ' AND biblioitems.ean = ?';
1830 if ( $booksellerid ) {
1831 $query .= 'AND aqbasket.booksellerid = ?';
1832 push @args, $booksellerid;
1835 $query .= 'AND aqbasket.basketno = ?';
1836 push @args, $basketno;
1839 $query .= 'AND aqbasket.basketname LIKE ?';
1840 push @args, "%$basketname%";
1842 if( $basketgroupname ) {
1843 $query .= ' AND aqbasketgroups.name LIKE ?';
1844 push @args, "%$basketgroupname%";
1848 $query .= ' AND aqbasket.authorisedby=? ';
1849 push @args, $userenv->{'number'};
1853 $query .= ' AND aqorders.budget_id = ?';
1854 push @args, $budget_id;
1857 $query .= ' ORDER BY aqbasket.basketno';
1859 my $sth = $dbh->prepare($query);
1860 $sth->execute(@args);
1861 return $sth->fetchall_arrayref({});
1864 #------------------------------------------------------------#
1868 &DelOrder($biblionumber, $ordernumber);
1870 Cancel the order with the given order and biblio numbers. It does not
1871 delete any entries in the aqorders table, it merely marks them as
1877 my ( $bibnum, $ordernumber ) = @_;
1878 my $dbh = C4::Context->dbh;
1881 SET datecancellationprinted=now(), orderstatus='cancelled'
1882 WHERE biblionumber=? AND ordernumber=?
1884 my $sth = $dbh->prepare($query);
1885 $sth->execute( $bibnum, $ordernumber );
1886 my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1887 foreach my $itemnumber (@itemnumbers){
1890 biblionumber => $bibnum,
1891 itemnumber => $itemnumber
1898 =head3 TransferOrder
1900 my $newordernumber = TransferOrder($ordernumber, $basketno);
1902 Transfer an order line to a basket.
1903 Mark $ordernumber as cancelled with an internal note 'Cancelled and transfered
1904 to BOOKSELLER on DATE' and create new order with internal note
1905 'Transfered from BOOKSELLER on DATE'.
1906 Move all attached items to the new order.
1907 Received orders cannot be transfered.
1908 Return the ordernumber of created order.
1913 my ($ordernumber, $basketno) = @_;
1915 return unless ($ordernumber and $basketno);
1917 my $order = GetOrder( $ordernumber );
1918 return if $order->{datereceived};
1919 my $basket = GetBasket($basketno);
1920 return unless $basket;
1922 my $dbh = C4::Context->dbh;
1923 my ($query, $sth, $rv);
1927 SET datecancellationprinted = CAST(NOW() AS date)
1928 WHERE ordernumber = ?
1930 $sth = $dbh->prepare($query);
1931 $rv = $sth->execute($ordernumber);
1933 delete $order->{'ordernumber'};
1934 delete $order->{parent_ordernumber};
1935 $order->{'basketno'} = $basketno;
1937 (undef, $newordernumber) = NewOrder($order);
1940 UPDATE aqorders_items
1942 WHERE ordernumber = ?
1944 $sth = $dbh->prepare($query);
1945 $sth->execute($newordernumber, $ordernumber);
1948 INSERT INTO aqorders_transfers (ordernumber_from, ordernumber_to)
1951 $sth = $dbh->prepare($query);
1952 $sth->execute($ordernumber, $newordernumber);
1954 return $newordernumber;
1957 =head2 FUNCTIONS ABOUT PARCELS
1961 #------------------------------------------------------------#
1965 @results = &GetParcel($booksellerid, $code, $date);
1967 Looks up all of the received items from the supplier with the given
1968 bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders.
1970 C<@results> is an array of references-to-hash. The keys of each element are fields from
1971 the aqorders, biblio, and biblioitems tables of the Koha database.
1973 C<@results> is sorted alphabetically by book title.
1978 #gets all orders from a certain supplier, orders them alphabetically
1979 my ( $supplierid, $code, $datereceived ) = @_;
1980 my $dbh = C4::Context->dbh;
1983 if $code; # add % if we search on a given code (otherwise, let him empty)
1985 SELECT authorisedby,
1990 aqorders.biblionumber,
1991 aqorders.ordernumber,
1992 aqorders.parent_ordernumber,
1994 aqorders.quantityreceived,
2002 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
2003 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2004 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
2005 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2007 aqbasket.booksellerid = ?
2008 AND aqinvoices.invoicenumber LIKE ?
2009 AND aqorders.datereceived = ? ";
2011 my @query_params = ( $supplierid, $code, $datereceived );
2012 if ( C4::Context->preference("IndependentBranches") ) {
2013 unless ( C4::Context->IsSuperLibrarian() ) {
2014 $strsth .= " and (borrowers.branchcode = ?
2015 or borrowers.branchcode = '')";
2016 push @query_params, C4::Context->userenv->{branch};
2019 $strsth .= " ORDER BY aqbasket.basketno";
2020 my $result_set = $dbh->selectall_arrayref(
2025 return @{$result_set};
2028 #------------------------------------------------------------#
2032 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
2034 get a lists of parcels.
2041 is the bookseller this function has to get parcels.
2044 To know on what criteria the results list has to be ordered.
2047 is the booksellerinvoicenumber.
2049 =item $datefrom & $dateto
2050 to know on what date this function has to filter its search.
2055 a pointer on a hash list containing parcel informations as such :
2061 =item Last operation
2063 =item Number of biblio
2065 =item Number of items
2072 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
2073 my $dbh = C4::Context->dbh;
2074 my @query_params = ();
2076 SELECT aqinvoices.invoicenumber,
2077 datereceived,purchaseordernumber,
2078 count(DISTINCT biblionumber) AS biblio,
2079 sum(quantity) AS itemsexpected,
2080 sum(quantityreceived) AS itemsreceived
2081 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
2082 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2083 WHERE aqbasket.booksellerid = ? and datereceived IS NOT NULL
2085 push @query_params, $bookseller;
2087 if ( defined $code ) {
2088 $strsth .= ' and aqinvoices.invoicenumber like ? ';
2089 # add a % to the end of the code to allow stemming.
2090 push @query_params, "$code%";
2093 if ( defined $datefrom ) {
2094 $strsth .= ' and datereceived >= ? ';
2095 push @query_params, $datefrom;
2098 if ( defined $dateto ) {
2099 $strsth .= 'and datereceived <= ? ';
2100 push @query_params, $dateto;
2103 $strsth .= "group by aqinvoices.invoicenumber,datereceived ";
2105 # can't use a placeholder to place this column name.
2106 # but, we could probably be checking to make sure it is a column that will be fetched.
2107 $strsth .= "order by $order " if ($order);
2109 my $sth = $dbh->prepare($strsth);
2111 $sth->execute( @query_params );
2112 my $results = $sth->fetchall_arrayref({});
2116 #------------------------------------------------------------#
2118 =head3 GetLateOrders
2120 @results = &GetLateOrders;
2122 Searches for bookseller with late orders.
2125 the table of supplier with late issues. This table is full of hashref.
2131 my $supplierid = shift;
2133 my $estimateddeliverydatefrom = shift;
2134 my $estimateddeliverydateto = shift;
2136 my $dbh = C4::Context->dbh;
2138 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
2139 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
2141 my @query_params = ();
2143 SELECT aqbasket.basketno,
2144 aqorders.ordernumber,
2145 DATE(aqbasket.closedate) AS orderdate,
2146 aqbasket.basketname AS basketname,
2147 aqbasket.basketgroupid AS basketgroupid,
2148 aqbasketgroups.name AS basketgroupname,
2149 aqorders.rrp AS unitpricesupplier,
2150 aqorders.ecost AS unitpricelib,
2151 aqorders.claims_count AS claims_count,
2152 aqorders.claimed_date AS claimed_date,
2153 aqbudgets.budget_name AS budget,
2154 borrowers.branchcode AS branch,
2155 aqbooksellers.name AS supplier,
2156 aqbooksellers.id AS supplierid,
2157 biblio.author, biblio.title,
2158 biblioitems.publishercode AS publisher,
2159 biblioitems.publicationyear,
2160 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
2164 aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
2165 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
2166 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
2167 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
2168 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
2169 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
2170 WHERE aqorders.basketno = aqbasket.basketno
2171 AND ( datereceived = ''
2172 OR datereceived IS NULL
2173 OR aqorders.quantityreceived < aqorders.quantity
2175 AND aqbasket.closedate IS NOT NULL
2176 AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
2179 if ($dbdriver eq "mysql") {
2181 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity,
2182 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
2183 DATEDIFF(CAST(now() AS date),closedate) AS latesince
2185 if ( defined $delay ) {
2186 $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ;
2187 push @query_params, $delay;
2190 HAVING quantity <> 0
2191 AND unitpricesupplier <> 0
2192 AND unitpricelib <> 0
2195 # FIXME: account for IFNULL as above
2197 aqorders.quantity AS quantity,
2198 aqorders.quantity * aqorders.rrp AS subtotal,
2199 (CAST(now() AS date) - closedate) AS latesince
2201 if ( defined $delay ) {
2202 $from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) ";
2203 push @query_params, $delay;
2206 if (defined $supplierid) {
2207 $from .= ' AND aqbasket.booksellerid = ? ';
2208 push @query_params, $supplierid;
2210 if (defined $branch) {
2211 $from .= ' AND borrowers.branchcode LIKE ? ';
2212 push @query_params, $branch;
2215 if ( defined $estimateddeliverydatefrom or defined $estimateddeliverydateto ) {
2216 $from .= ' AND aqbooksellers.deliverytime IS NOT NULL ';
2218 if ( defined $estimateddeliverydatefrom ) {
2219 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
2220 push @query_params, $estimateddeliverydatefrom;
2222 if ( defined $estimateddeliverydateto ) {
2223 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
2224 push @query_params, $estimateddeliverydateto;
2226 if ( defined $estimateddeliverydatefrom and not defined $estimateddeliverydateto ) {
2227 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
2229 if (C4::Context->preference("IndependentBranches")
2230 && !C4::Context->IsSuperLibrarian() ) {
2231 $from .= ' AND borrowers.branchcode LIKE ? ';
2232 push @query_params, C4::Context->userenv->{branch};
2234 $from .= " AND orderstatus <> 'cancelled' ";
2235 my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier";
2236 $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params);
2237 my $sth = $dbh->prepare($query);
2238 $sth->execute(@query_params);
2240 while (my $data = $sth->fetchrow_hashref) {
2241 push @results, $data;
2246 #------------------------------------------------------------#
2250 (\@order_loop, $total_qty, $total_price, $total_qtyreceived) = GetHistory( %params );
2252 Retreives some acquisition history information
2262 basket - search both basket name and number
2263 booksellerinvoicenumber
2266 orderstatus (note that orderstatus '' will retrieve orders
2267 of any status except cancelled)
2269 get_canceled_order (if set to a true value, cancelled orders will
2273 $order_loop is a list of hashrefs that each look like this:
2275 'author' => 'Twain, Mark',
2277 'biblionumber' => '215',
2279 'creationdate' => 'MM/DD/YYYY',
2280 'datereceived' => undef,
2283 'invoicenumber' => undef,
2285 'ordernumber' => '1',
2287 'quantityreceived' => undef,
2288 'title' => 'The Adventures of Huckleberry Finn'
2290 $total_qty is the sum of all of the quantities in $order_loop
2291 $total_price is the cost of each in $order_loop times the quantity
2292 $total_qtyreceived is the sum of all of the quantityreceived entries in $order_loop
2297 # don't run the query if there are no parameters (list would be too long for sure !)
2298 croak "No search params" unless @_;
2300 my $title = $params{title};
2301 my $author = $params{author};
2302 my $isbn = $params{isbn};
2303 my $ean = $params{ean};
2304 my $name = $params{name};
2305 my $from_placed_on = $params{from_placed_on};
2306 my $to_placed_on = $params{to_placed_on};
2307 my $basket = $params{basket};
2308 my $booksellerinvoicenumber = $params{booksellerinvoicenumber};
2309 my $basketgroupname = $params{basketgroupname};
2310 my $budget = $params{budget};
2311 my $orderstatus = $params{orderstatus};
2312 my $biblionumber = $params{biblionumber};
2313 my $get_canceled_order = $params{get_canceled_order} || 0;
2314 my $ordernumber = $params{ordernumber};
2315 my $search_children_too = $params{search_children_too} || 0;
2319 my $total_qtyreceived = 0;
2320 my $total_price = 0;
2322 my $dbh = C4::Context->dbh;
2325 COALESCE(biblio.title, deletedbiblio.title) AS title,
2326 COALESCE(biblio.author, deletedbiblio.author) AS author,
2327 COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn,
2328 COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean,
2330 aqbasket.basketname,
2331 aqbasket.basketgroupid,
2332 aqbasketgroups.name as groupname,
2334 aqbasket.creationdate,
2335 aqorders.datereceived,
2337 aqorders.quantityreceived,
2339 aqorders.ordernumber,
2341 aqinvoices.invoicenumber,
2342 aqbooksellers.id as id,
2343 aqorders.biblionumber,
2344 aqorders.orderstatus,
2345 aqorders.parent_ordernumber,
2346 aqbudgets.budget_name
2348 $query .= ", aqbudgets.budget_id AS budget" if defined $budget;
2351 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
2352 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
2353 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
2354 LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
2355 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
2356 LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id
2357 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2358 LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.biblionumber
2359 LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=aqorders.biblionumber
2362 if ( C4::Context->preference("IndependentBranches") ) {
2363 $query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber";
2366 $query .= " WHERE 1 ";
2368 unless ($get_canceled_order or (defined $orderstatus and $orderstatus eq 'cancelled')) {
2369 $query .= " AND (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') ";
2372 my @query_params = ();
2374 if ( $biblionumber ) {
2375 $query .= " AND biblio.biblionumber = ?";
2376 push @query_params, $biblionumber;
2380 $query .= " AND biblio.title LIKE ? ";
2381 $title =~ s/\s+/%/g;
2382 push @query_params, "%$title%";
2386 $query .= " AND biblio.author LIKE ? ";
2387 push @query_params, "%$author%";
2391 $query .= " AND biblioitems.isbn LIKE ? ";
2392 push @query_params, "%$isbn%";
2395 $query .= " AND biblioitems.ean = ? ";
2396 push @query_params, "$ean";
2399 $query .= " AND aqbooksellers.name LIKE ? ";
2400 push @query_params, "%$name%";
2404 $query .= " AND aqbudgets.budget_id = ? ";
2405 push @query_params, "$budget";
2408 if ( $from_placed_on ) {
2409 $query .= " AND creationdate >= ? ";
2410 push @query_params, $from_placed_on;
2413 if ( $to_placed_on ) {
2414 $query .= " AND creationdate <= ? ";
2415 push @query_params, $to_placed_on;
2418 if ( defined $orderstatus and $orderstatus ne '') {
2419 $query .= " AND aqorders.orderstatus = ? ";
2420 push @query_params, "$orderstatus";
2424 if ($basket =~ m/^\d+$/) {
2425 $query .= " AND aqorders.basketno = ? ";
2426 push @query_params, $basket;
2428 $query .= " AND aqbasket.basketname LIKE ? ";
2429 push @query_params, "%$basket%";
2433 if ($booksellerinvoicenumber) {
2434 $query .= " AND aqinvoices.invoicenumber LIKE ? ";
2435 push @query_params, "%$booksellerinvoicenumber%";
2438 if ($basketgroupname) {
2439 $query .= " AND aqbasketgroups.name LIKE ? ";
2440 push @query_params, "%$basketgroupname%";
2444 $query .= " AND (aqorders.ordernumber = ? ";
2445 push @query_params, $ordernumber;
2446 if ($search_children_too) {
2447 $query .= " OR aqorders.parent_ordernumber = ? ";
2448 push @query_params, $ordernumber;
2454 if ( C4::Context->preference("IndependentBranches") ) {
2455 unless ( C4::Context->IsSuperLibrarian() ) {
2456 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
2457 push @query_params, C4::Context->userenv->{branch};
2460 $query .= " ORDER BY id";
2461 my $sth = $dbh->prepare($query);
2462 $sth->execute( @query_params );
2464 while ( my $line = $sth->fetchrow_hashref ) {
2465 $line->{count} = $cnt++;
2466 $line->{toggle} = 1 if $cnt % 2;
2467 push @order_loop, $line;
2468 $total_qty += ( $line->{quantity} ) ? $line->{quantity} : 0;
2469 $total_qtyreceived += ( $line->{quantityreceived} ) ? $line->{quantityreceived} : 0;
2470 $total_price += ( $line->{quantity} and $line->{ecost} ) ? $line->{quantity} * $line->{ecost} : 0;
2472 return \@order_loop, $total_qty, $total_price, $total_qtyreceived;
2475 =head2 GetRecentAcqui
2477 $results = GetRecentAcqui($days);
2479 C<$results> is a ref to a table which containts hashref
2483 sub GetRecentAcqui {
2485 my $dbh = C4::Context->dbh;
2489 ORDER BY timestamp DESC
2492 my $sth = $dbh->prepare($query);
2494 my $results = $sth->fetchall_arrayref({});
2498 #------------------------------------------------------------#
2504 &AddClaim($ordernumber);
2506 Add a claim for an order
2513 my ($ordernumber) = @_;
2514 my $dbh = C4::Context->dbh;
2517 claims_count = claims_count + 1,
2518 claimed_date = CURDATE()
2519 WHERE ordernumber = ?
2521 my $sth = $dbh->prepare($query);
2522 $sth->execute($ordernumber);
2527 my @invoices = GetInvoices(
2528 invoicenumber => $invoicenumber,
2529 supplierid => $supplierid,
2530 suppliername => $suppliername,
2531 shipmentdatefrom => $shipmentdatefrom, # ISO format
2532 shipmentdateto => $shipmentdateto, # ISO format
2533 billingdatefrom => $billingdatefrom, # ISO format
2534 billingdateto => $billingdateto, # ISO format
2535 isbneanissn => $isbn_or_ean_or_issn,
2538 publisher => $publisher,
2539 publicationyear => $publicationyear,
2540 branchcode => $branchcode,
2541 order_by => $order_by
2544 Return a list of invoices that match all given criteria.
2546 $order_by is "column_name (asc|desc)", where column_name is any of
2547 'invoicenumber', 'booksellerid', 'shipmentdate', 'billingdate', 'closedate',
2548 'shipmentcost', 'shipmentcost_budgetid'.
2550 asc is the default if omitted
2557 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2558 closedate shipmentcost shipmentcost_budgetid);
2560 my $dbh = C4::Context->dbh;
2562 SELECT aqinvoices.*, aqbooksellers.name AS suppliername,
2565 aqorders.datereceived IS NOT NULL,
2566 aqorders.biblionumber,
2569 ) AS receivedbiblios,
2572 aqorders.subscriptionid IS NOT NULL,
2573 aqorders.subscriptionid,
2576 ) AS is_linked_to_subscriptions,
2577 SUM(aqorders.quantityreceived) AS receiveditems
2579 LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid
2580 LEFT JOIN aqorders ON aqorders.invoiceid = aqinvoices.invoiceid
2581 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
2582 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2583 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2584 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
2585 LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber
2590 if($args{supplierid}) {
2591 push @bind_strs, " aqinvoices.booksellerid = ? ";
2592 push @bind_args, $args{supplierid};
2594 if($args{invoicenumber}) {
2595 push @bind_strs, " aqinvoices.invoicenumber LIKE ? ";
2596 push @bind_args, "%$args{invoicenumber}%";
2598 if($args{suppliername}) {
2599 push @bind_strs, " aqbooksellers.name LIKE ? ";
2600 push @bind_args, "%$args{suppliername}%";
2602 if($args{shipmentdatefrom}) {
2603 push @bind_strs, " aqinvoices.shipmentdate >= ? ";
2604 push @bind_args, $args{shipmentdatefrom};
2606 if($args{shipmentdateto}) {
2607 push @bind_strs, " aqinvoices.shipmentdate <= ? ";
2608 push @bind_args, $args{shipmentdateto};
2610 if($args{billingdatefrom}) {
2611 push @bind_strs, " aqinvoices.billingdate >= ? ";
2612 push @bind_args, $args{billingdatefrom};
2614 if($args{billingdateto}) {
2615 push @bind_strs, " aqinvoices.billingdate <= ? ";
2616 push @bind_args, $args{billingdateto};
2618 if($args{isbneanissn}) {
2619 push @bind_strs, " (biblioitems.isbn LIKE CONCAT('%', ?, '%') OR biblioitems.ean LIKE CONCAT('%', ?, '%') OR biblioitems.issn LIKE CONCAT('%', ?, '%') ) ";
2620 push @bind_args, $args{isbneanissn}, $args{isbneanissn}, $args{isbneanissn};
2623 push @bind_strs, " biblio.title LIKE CONCAT('%', ?, '%') ";
2624 push @bind_args, $args{title};
2627 push @bind_strs, " biblio.author LIKE CONCAT('%', ?, '%') ";
2628 push @bind_args, $args{author};
2630 if($args{publisher}) {
2631 push @bind_strs, " biblioitems.publishercode LIKE CONCAT('%', ?, '%') ";
2632 push @bind_args, $args{publisher};
2634 if($args{publicationyear}) {
2635 push @bind_strs, " ((biblioitems.publicationyear LIKE CONCAT('%', ?, '%')) OR (biblio.copyrightdate LIKE CONCAT('%', ?, '%'))) ";
2636 push @bind_args, $args{publicationyear}, $args{publicationyear};
2638 if($args{branchcode}) {
2639 push @bind_strs, " borrowers.branchcode = ? ";
2640 push @bind_args, $args{branchcode};
2643 $query .= " WHERE " . join(" AND ", @bind_strs) if @bind_strs;
2644 $query .= " GROUP BY aqinvoices.invoiceid ";
2646 if($args{order_by}) {
2647 my ($column, $direction) = split / /, $args{order_by};
2648 if(grep /^$column$/, @columns) {
2649 $direction ||= 'ASC';
2650 $query .= " ORDER BY $column $direction";
2654 my $sth = $dbh->prepare($query);
2655 $sth->execute(@bind_args);
2657 my $results = $sth->fetchall_arrayref({});
2663 my $invoice = GetInvoice($invoiceid);
2665 Get informations about invoice with given $invoiceid
2667 Return a hash filled with aqinvoices.* fields
2672 my ($invoiceid) = @_;
2675 return unless $invoiceid;
2677 my $dbh = C4::Context->dbh;
2683 my $sth = $dbh->prepare($query);
2684 $sth->execute($invoiceid);
2686 $invoice = $sth->fetchrow_hashref;
2690 =head3 GetInvoiceDetails
2692 my $invoice = GetInvoiceDetails($invoiceid)
2694 Return informations about an invoice + the list of related order lines
2696 Orders informations are in $invoice->{orders} (array ref)
2700 sub GetInvoiceDetails {
2701 my ($invoiceid) = @_;
2703 if ( !defined $invoiceid ) {
2704 carp 'GetInvoiceDetails called without an invoiceid';
2708 my $dbh = C4::Context->dbh;
2710 SELECT aqinvoices.*, aqbooksellers.name AS suppliername
2712 LEFT JOIN aqbooksellers ON aqinvoices.booksellerid = aqbooksellers.id
2715 my $sth = $dbh->prepare($query);
2716 $sth->execute($invoiceid);
2718 my $invoice = $sth->fetchrow_hashref;
2721 SELECT aqorders.*, biblio.*, aqbasket.basketname
2723 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
2724 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2727 $sth = $dbh->prepare($query);
2728 $sth->execute($invoiceid);
2729 $invoice->{orders} = $sth->fetchall_arrayref({});
2730 $invoice->{orders} ||= []; # force an empty arrayref if fetchall_arrayref fails
2737 my $invoiceid = AddInvoice(
2738 invoicenumber => $invoicenumber,
2739 booksellerid => $booksellerid,
2740 shipmentdate => $shipmentdate,
2741 billingdate => $billingdate,
2742 closedate => $closedate,
2743 shipmentcost => $shipmentcost,
2744 shipmentcost_budgetid => $shipmentcost_budgetid
2747 Create a new invoice and return its id or undef if it fails.
2754 return unless(%invoice and $invoice{invoicenumber});
2756 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2757 closedate shipmentcost shipmentcost_budgetid);
2761 foreach my $key (keys %invoice) {
2762 if(0 < grep(/^$key$/, @columns)) {
2763 push @set_strs, "$key = ?";
2764 push @set_args, ($invoice{$key} || undef);
2770 my $dbh = C4::Context->dbh;
2771 my $query = "INSERT INTO aqinvoices SET ";
2772 $query .= join (",", @set_strs);
2773 my $sth = $dbh->prepare($query);
2774 $rv = $sth->execute(@set_args);
2776 $rv = $dbh->last_insert_id(undef, undef, 'aqinvoices', undef);
2785 invoiceid => $invoiceid, # Mandatory
2786 invoicenumber => $invoicenumber,
2787 booksellerid => $booksellerid,
2788 shipmentdate => $shipmentdate,
2789 billingdate => $billingdate,
2790 closedate => $closedate,
2791 shipmentcost => $shipmentcost,
2792 shipmentcost_budgetid => $shipmentcost_budgetid
2795 Modify an invoice, invoiceid is mandatory.
2797 Return undef if it fails.
2804 return unless(%invoice and $invoice{invoiceid});
2806 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2807 closedate shipmentcost shipmentcost_budgetid);
2811 foreach my $key (keys %invoice) {
2812 if(0 < grep(/^$key$/, @columns)) {
2813 push @set_strs, "$key = ?";
2814 push @set_args, ($invoice{$key} || undef);
2818 my $dbh = C4::Context->dbh;
2819 my $query = "UPDATE aqinvoices SET ";
2820 $query .= join(",", @set_strs);
2821 $query .= " WHERE invoiceid = ?";
2823 my $sth = $dbh->prepare($query);
2824 $sth->execute(@set_args, $invoice{invoiceid});
2829 CloseInvoice($invoiceid);
2833 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => undef);
2838 my ($invoiceid) = @_;
2840 return unless $invoiceid;
2842 my $dbh = C4::Context->dbh;
2845 SET closedate = CAST(NOW() AS DATE)
2848 my $sth = $dbh->prepare($query);
2849 $sth->execute($invoiceid);
2852 =head3 ReopenInvoice
2854 ReopenInvoice($invoiceid);
2858 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => C4::Dates->new()->output('iso'))
2863 my ($invoiceid) = @_;
2865 return unless $invoiceid;
2867 my $dbh = C4::Context->dbh;
2870 SET closedate = NULL
2873 my $sth = $dbh->prepare($query);
2874 $sth->execute($invoiceid);
2879 DelInvoice($invoiceid);
2881 Delete an invoice if there are no items attached to it.
2886 my ($invoiceid) = @_;
2888 return unless $invoiceid;
2890 my $dbh = C4::Context->dbh;
2896 my $sth = $dbh->prepare($query);
2897 $sth->execute($invoiceid);
2898 my $res = $sth->fetchrow_arrayref;
2899 if ( $res && $res->[0] == 0 ) {
2901 DELETE FROM aqinvoices
2904 my $sth = $dbh->prepare($query);
2905 return ( $sth->execute($invoiceid) > 0 );
2910 =head3 MergeInvoices
2912 MergeInvoices($invoiceid, \@sourceids);
2914 Merge the invoices identified by the IDs in \@sourceids into
2915 the invoice identified by $invoiceid.
2920 my ($invoiceid, $sourceids) = @_;
2922 return unless $invoiceid;
2923 foreach my $sourceid (@$sourceids) {
2924 next if $sourceid == $invoiceid;
2925 my $source = GetInvoiceDetails($sourceid);
2926 foreach my $order (@{$source->{'orders'}}) {
2927 $order->{'invoiceid'} = $invoiceid;
2930 DelInvoice($source->{'invoiceid'});
2935 =head3 GetBiblioCountByBasketno
2937 $biblio_count = &GetBiblioCountByBasketno($basketno);
2939 Looks up the biblio's count that has basketno value $basketno
2945 sub GetBiblioCountByBasketno {
2946 my ($basketno) = @_;
2947 my $dbh = C4::Context->dbh;
2949 SELECT COUNT( DISTINCT( biblionumber ) )
2952 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
2955 my $sth = $dbh->prepare($query);
2956 $sth->execute($basketno);
2957 return $sth->fetchrow;
2965 Koha Development Team <http://koha-community.org/>