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;
1269 croak "The ordernumber parameter should not be provided on calling NewOrder"
1270 if $orderinfo->{ordernumber};
1272 # if these parameters are missing, we can't continue
1273 for my $key (qw/basketno quantity biblionumber budget_id/) {
1274 croak "Mandatory parameter $key missing" unless $orderinfo->{$key};
1277 $orderinfo->{'entrydate'} ||= C4::Dates->new()->output("iso");
1278 if (!$orderinfo->{quantityreceived}) {
1279 $orderinfo->{quantityreceived} = 0;
1282 # get only the columns of Aqorder
1283 my $schema = Koha::Database->new()->schema;
1284 my $columns = ' '.join(' ', $schema->source('Aqorder')->columns).' ';
1285 my $new_order = { map { $columns =~ / $_ / ? ($_ => $orderinfo->{$_}) : () } keys(%$orderinfo) };
1286 $new_order->{ordernumber} ||= undef;
1288 my $rs = $schema->resultset('Aqorder');
1289 my $ordernumber = $rs->create($new_order)->id;
1290 if (not $new_order->{parent_ordernumber}) {
1291 my $sth = $dbh->prepare("
1293 SET parent_ordernumber = ordernumber
1294 WHERE ordernumber = ?
1296 $sth->execute($ordernumber);
1298 return ( $new_order->{'basketno'}, $ordernumber );
1303 #------------------------------------------------------------#
1312 my ($itemnumber, $ordernumber) = @_;
1313 my $dbh = C4::Context->dbh;
1315 INSERT INTO aqorders_items
1316 (itemnumber, ordernumber)
1319 my $sth = $dbh->prepare($query);
1320 $sth->execute( $itemnumber, $ordernumber);
1323 #------------------------------------------------------------#
1327 &ModOrder(\%hashref);
1329 Modifies an existing order. Updates the order with order number
1330 $hashref->{'ordernumber'} and biblionumber $hashref->{'biblionumber'}. All
1331 other keys of the hash update the fields with the same name in the aqorders
1332 table of the Koha database.
1337 my $orderinfo = shift;
1339 die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq '' ;
1340 die "Biblionumber is required" if $orderinfo->{'biblionumber'} eq '';
1342 my $dbh = C4::Context->dbh;
1345 # update uncertainprice to an integer, just in case (under FF, checked boxes have the value "ON" by default)
1346 $orderinfo->{uncertainprice}=1 if $orderinfo->{uncertainprice};
1348 # delete($orderinfo->{'branchcode'});
1349 # the hash contains a lot of entries not in aqorders, so get the columns ...
1350 my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;");
1352 my $colnames = $sth->{NAME};
1353 #FIXME Be careful. If aqorders would have columns with diacritics,
1354 #you should need to decode what you get back from NAME.
1355 #See report 10110 and guided_reports.pl
1356 my $query = "UPDATE aqorders SET ";
1358 foreach my $orderinfokey (grep(!/ordernumber/, keys %$orderinfo)){
1359 # ... and skip hash entries that are not in the aqorders table
1360 # FIXME : probably not the best way to do it (would be better to have a correct hash)
1361 next unless grep(/^$orderinfokey$/, @$colnames);
1362 $query .= "$orderinfokey=?, ";
1363 push(@params, $orderinfo->{$orderinfokey});
1366 $query .= "timestamp=NOW() WHERE ordernumber=?";
1367 push(@params, $orderinfo->{'ordernumber'} );
1368 $sth = $dbh->prepare($query);
1369 $sth->execute(@params);
1373 #------------------------------------------------------------#
1377 ModItemOrder($itemnumber, $ordernumber);
1379 Modifies the ordernumber of an item in aqorders_items.
1384 my ($itemnumber, $ordernumber) = @_;
1386 return unless ($itemnumber and $ordernumber);
1388 my $dbh = C4::Context->dbh;
1390 UPDATE aqorders_items
1392 WHERE itemnumber = ?
1394 my $sth = $dbh->prepare($query);
1395 return $sth->execute($ordernumber, $itemnumber);
1398 #------------------------------------------------------------#
1400 =head3 GetCancelledOrders
1402 my @orders = GetCancelledOrders($basketno, $orderby);
1404 Returns cancelled orders for a basket
1408 sub GetCancelledOrders {
1409 my ( $basketno, $orderby ) = @_;
1411 return () unless $basketno;
1413 my $dbh = C4::Context->dbh;
1420 aqorders_transfers.ordernumber_to AS transferred_to,
1421 aqorders_transfers.timestamp AS transferred_to_timestamp
1423 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1424 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1425 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1426 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber
1428 AND (datecancellationprinted IS NOT NULL
1429 AND datecancellationprinted <> '0000-00-00')
1432 $orderby = "aqorders.datecancellationprinted desc, aqorders.timestamp desc"
1434 $query .= " ORDER BY $orderby";
1435 my $sth = $dbh->prepare($query);
1436 $sth->execute($basketno);
1437 my $results = $sth->fetchall_arrayref( {} );
1443 #------------------------------------------------------------#
1445 =head3 ModReceiveOrder
1448 biblionumber => $biblionumber,
1449 ordernumber => $ordernumber,
1450 quantityreceived => $quantityreceived,
1454 invoiceid => $invoiceid,
1456 budget_id => $budget_id,
1457 datereceived => $datereceived,
1458 received_itemnumbers => \@received_itemnumbers,
1459 order_internalnote => $order_internalnote,
1460 order_vendornote => $order_vendornote,
1463 Updates an order, to reflect the fact that it was received, at least
1464 in part. All arguments not mentioned below update the fields with the
1465 same name in the aqorders table of the Koha database.
1467 If a partial order is received, splits the order into two.
1469 Updates the order with bibilionumber C<$biblionumber> and ordernumber
1475 sub ModReceiveOrder {
1476 my ( $params ) = @_;
1477 my $biblionumber = $params->{biblionumber};
1478 my $ordernumber = $params->{ordernumber};
1479 my $quantrec = $params->{quantityreceived};
1480 my $user = $params->{user};
1481 my $cost = $params->{cost};
1482 my $ecost = $params->{ecost};
1483 my $invoiceid = $params->{invoiceid};
1484 my $rrp = $params->{rrp};
1485 my $budget_id = $params->{budget_id};
1486 my $datereceived = $params->{datereceived};
1487 my $received_items = $params->{received_items};
1488 my $order_internalnote = $params->{order_internalnote};
1489 my $order_vendornote = $params->{order_vendornote};
1491 my $dbh = C4::Context->dbh;
1492 $datereceived = C4::Dates->output('iso') unless $datereceived;
1493 my $suggestionid = GetSuggestionFromBiblionumber( $biblionumber );
1494 if ($suggestionid) {
1495 ModSuggestion( {suggestionid=>$suggestionid,
1496 STATUS=>'AVAILABLE',
1497 biblionumber=> $biblionumber}
1501 my $result_set = $dbh->selectall_arrayref(
1502 q{SELECT * FROM aqorders WHERE biblionumber=? AND aqorders.ordernumber=?},
1503 { Slice => {} }, $biblionumber, $ordernumber
1506 # we assume we have a unique order
1507 my $order = $result_set->[0];
1509 my $new_ordernumber = $ordernumber;
1510 if ( $order->{quantity} > $quantrec ) {
1511 # Split order line in two parts: the first is the original order line
1512 # without received items (the quantity is decreased),
1513 # the second part is a new order line with quantity=quantityrec
1514 # (entirely received)
1518 orderstatus = 'partial'|;
1519 $query .= q|, order_internalnote = ?| if defined $order_internalnote;
1520 $query .= q|, order_vendornote = ?| if defined $order_vendornote;
1521 $query .= q| WHERE ordernumber = ?|;
1522 my $sth = $dbh->prepare($query);
1525 $order->{quantity} - $quantrec,
1526 ( defined $order_internalnote ? $order_internalnote : () ),
1527 ( defined $order_vendornote ? $order_vendornote : () ),
1531 delete $order->{'ordernumber'};
1532 $order->{'budget_id'} = ( $budget_id || $order->{'budget_id'} );
1533 $order->{'quantity'} = $quantrec;
1534 $order->{'quantityreceived'} = $quantrec;
1535 $order->{'datereceived'} = $datereceived;
1536 $order->{'invoiceid'} = $invoiceid;
1537 $order->{'unitprice'} = $cost;
1538 $order->{'rrp'} = $rrp;
1539 $order->{ecost} = $ecost;
1540 $order->{'orderstatus'} = 'complete';
1542 ( $basketno, $new_ordernumber ) = NewOrder($order);
1544 if ($received_items) {
1545 foreach my $itemnumber (@$received_items) {
1546 ModItemOrder($itemnumber, $new_ordernumber);
1552 set quantityreceived=?,datereceived=?,invoiceid=?,
1553 unitprice=?,rrp=?,ecost=?,budget_id=?,orderstatus='complete'|;
1554 $query .= q|, order_internalnote = ?| if defined $order_internalnote;
1555 $query .= q|, order_vendornote = ?| if defined $order_vendornote;
1556 $query .= q| where biblionumber=? and ordernumber=?|;
1557 my $sth = $dbh->prepare( $query );
1566 ( defined $order_internalnote ? $order_internalnote : () ),
1567 ( defined $order_vendornote ? $order_vendornote : () ),
1572 return ($datereceived, $new_ordernumber);
1575 =head3 CancelReceipt
1577 my $parent_ordernumber = CancelReceipt($ordernumber);
1579 Cancel an order line receipt and update the parent order line, as if no
1581 If items are created at receipt (AcqCreateItem = receiving) then delete
1587 my $ordernumber = shift;
1589 return unless $ordernumber;
1591 my $dbh = C4::Context->dbh;
1593 SELECT datereceived, parent_ordernumber, quantity
1595 WHERE ordernumber = ?
1597 my $sth = $dbh->prepare($query);
1598 $sth->execute($ordernumber);
1599 my $order = $sth->fetchrow_hashref;
1601 warn "CancelReceipt: order $ordernumber does not exist";
1604 unless($order->{'datereceived'}) {
1605 warn "CancelReceipt: order $ordernumber is not received";
1609 my $parent_ordernumber = $order->{'parent_ordernumber'};
1611 my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1613 if($parent_ordernumber == $ordernumber || not $parent_ordernumber) {
1614 # The order line has no parent, just mark it as not received
1617 SET quantityreceived = ?,
1620 orderstatus = 'ordered'
1621 WHERE ordernumber = ?
1623 $sth = $dbh->prepare($query);
1624 $sth->execute(0, undef, undef, $ordernumber);
1625 _cancel_items_receipt( $ordernumber );
1627 # The order line has a parent, increase parent quantity and delete
1630 SELECT quantity, datereceived
1632 WHERE ordernumber = ?
1634 $sth = $dbh->prepare($query);
1635 $sth->execute($parent_ordernumber);
1636 my $parent_order = $sth->fetchrow_hashref;
1637 unless($parent_order) {
1638 warn "Parent order $parent_ordernumber does not exist.";
1641 if($parent_order->{'datereceived'}) {
1642 warn "CancelReceipt: parent order is received.".
1643 " Can't cancel receipt.";
1649 orderstatus = 'ordered'
1650 WHERE ordernumber = ?
1652 $sth = $dbh->prepare($query);
1653 my $rv = $sth->execute(
1654 $order->{'quantity'} + $parent_order->{'quantity'},
1658 warn "Cannot update parent order line, so do not cancel".
1662 _cancel_items_receipt( $ordernumber, $parent_ordernumber );
1665 DELETE FROM aqorders
1666 WHERE ordernumber = ?
1668 $sth = $dbh->prepare($query);
1669 $sth->execute($ordernumber);
1673 if(C4::Context->preference('AcqCreateItem') eq 'ordering') {
1674 my @affects = split q{\|}, C4::Context->preference("AcqItemSetSubfieldsWhenReceiptIsCancelled");
1676 for my $in ( @itemnumbers ) {
1677 my $biblionumber = C4::Biblio::GetBiblionumberFromItemnumber( $in );
1678 my $frameworkcode = GetFrameworkCode($biblionumber);
1679 my ( $itemfield ) = GetMarcFromKohaField( 'items.itemnumber', $frameworkcode );
1680 my $item = C4::Items::GetMarcItem( $biblionumber, $in );
1681 for my $affect ( @affects ) {
1682 my ( $sf, $v ) = split q{=}, $affect, 2;
1683 foreach ( $item->field($itemfield) ) {
1684 $_->update( $sf => $v );
1687 C4::Items::ModItemFromMarc( $item, $biblionumber, $in );
1692 return $parent_ordernumber;
1695 sub _cancel_items_receipt {
1696 my ( $ordernumber, $parent_ordernumber ) = @_;
1697 $parent_ordernumber ||= $ordernumber;
1699 my @itemnumbers = GetItemnumbersFromOrder($ordernumber);
1700 if(C4::Context->preference('AcqCreateItem') eq 'receiving') {
1701 # Remove items that were created at receipt
1703 DELETE FROM items, aqorders_items
1704 USING items, aqorders_items
1705 WHERE items.itemnumber = ? AND aqorders_items.itemnumber = ?
1707 my $dbh = C4::Context->dbh;
1708 my $sth = $dbh->prepare($query);
1709 foreach my $itemnumber (@itemnumbers) {
1710 $sth->execute($itemnumber, $itemnumber);
1714 foreach my $itemnumber (@itemnumbers) {
1715 ModItemOrder($itemnumber, $parent_ordernumber);
1720 #------------------------------------------------------------#
1724 @results = &SearchOrders({
1725 ordernumber => $ordernumber,
1727 biblionumber => $biblionumber,
1729 booksellerid => $booksellerid,
1730 basketno => $basketno,
1736 Searches for orders.
1738 C<$owner> Finds order for the logged in user.
1739 C<$pending> Finds pending orders. Ignores completed and cancelled orders.
1740 C<$ordered> Finds orders to receive only (status 'ordered' or 'partial').
1743 C<@results> is an array of references-to-hash with the keys are fields
1744 from aqorders, biblio, biblioitems and aqbasket tables.
1749 my ( $params ) = @_;
1750 my $ordernumber = $params->{ordernumber};
1751 my $search = $params->{search};
1752 my $ean = $params->{ean};
1753 my $booksellerid = $params->{booksellerid};
1754 my $basketno = $params->{basketno};
1755 my $basketname = $params->{basketname};
1756 my $basketgroupname = $params->{basketgroupname};
1757 my $owner = $params->{owner};
1758 my $pending = $params->{pending};
1759 my $ordered = $params->{ordered};
1760 my $biblionumber = $params->{biblionumber};
1761 my $budget_id = $params->{budget_id};
1763 my $dbh = C4::Context->dbh;
1766 SELECT aqbasket.basketno,
1768 borrowers.firstname,
1771 biblioitems.biblioitemnumber,
1772 aqbasket.authorisedby,
1773 aqbasket.booksellerid,
1775 aqbasket.creationdate,
1776 aqbasket.basketname,
1777 aqbasketgroups.id as basketgroupid,
1778 aqbasketgroups.name as basketgroupname,
1781 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
1782 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
1783 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1784 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1785 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
1788 # If we search on ordernumber, we retrieve the transfered order if a transfer has been done.
1790 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1794 WHERE (datecancellationprinted is NULL)
1797 if ( $pending or $ordered ) {
1798 $query .= q{ AND (quantity > quantityreceived OR quantityreceived is NULL)};
1801 $query .= q{ AND aqorders.orderstatus IN ( "ordered", "partial" )};
1804 my $userenv = C4::Context->userenv;
1805 if ( C4::Context->preference("IndependentBranches") ) {
1806 unless ( C4::Context->IsSuperLibrarian() ) {
1809 borrowers.branchcode = ?
1810 OR borrowers.branchcode = ''
1813 push @args, $userenv->{branch};
1817 if ( $ordernumber ) {
1818 $query .= ' AND ( aqorders.ordernumber = ? OR aqorders_transfers.ordernumber_from = ? ) ';
1819 push @args, ( $ordernumber, $ordernumber );
1821 if ( $biblionumber ) {
1822 $query .= 'AND aqorders.biblionumber = ?';
1823 push @args, $biblionumber;
1826 $query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)';
1827 push @args, ("%$search%","%$search%","%$search%");
1830 $query .= ' AND biblioitems.ean = ?';
1833 if ( $booksellerid ) {
1834 $query .= 'AND aqbasket.booksellerid = ?';
1835 push @args, $booksellerid;
1838 $query .= 'AND aqbasket.basketno = ?';
1839 push @args, $basketno;
1842 $query .= 'AND aqbasket.basketname LIKE ?';
1843 push @args, "%$basketname%";
1845 if( $basketgroupname ) {
1846 $query .= ' AND aqbasketgroups.name LIKE ?';
1847 push @args, "%$basketgroupname%";
1851 $query .= ' AND aqbasket.authorisedby=? ';
1852 push @args, $userenv->{'number'};
1856 $query .= ' AND aqorders.budget_id = ?';
1857 push @args, $budget_id;
1860 $query .= ' ORDER BY aqbasket.basketno';
1862 my $sth = $dbh->prepare($query);
1863 $sth->execute(@args);
1864 return $sth->fetchall_arrayref({});
1867 #------------------------------------------------------------#
1871 &DelOrder($biblionumber, $ordernumber);
1873 Cancel the order with the given order and biblio numbers. It does not
1874 delete any entries in the aqorders table, it merely marks them as
1880 my ( $bibnum, $ordernumber ) = @_;
1881 my $dbh = C4::Context->dbh;
1884 SET datecancellationprinted=now(), orderstatus='cancelled'
1885 WHERE biblionumber=? AND ordernumber=?
1887 my $sth = $dbh->prepare($query);
1888 $sth->execute( $bibnum, $ordernumber );
1889 my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1890 foreach my $itemnumber (@itemnumbers){
1893 biblionumber => $bibnum,
1894 itemnumber => $itemnumber
1901 =head3 TransferOrder
1903 my $newordernumber = TransferOrder($ordernumber, $basketno);
1905 Transfer an order line to a basket.
1906 Mark $ordernumber as cancelled with an internal note 'Cancelled and transfered
1907 to BOOKSELLER on DATE' and create new order with internal note
1908 'Transfered from BOOKSELLER on DATE'.
1909 Move all attached items to the new order.
1910 Received orders cannot be transfered.
1911 Return the ordernumber of created order.
1916 my ($ordernumber, $basketno) = @_;
1918 return unless ($ordernumber and $basketno);
1920 my $order = GetOrder( $ordernumber );
1921 return if $order->{datereceived};
1922 my $basket = GetBasket($basketno);
1923 return unless $basket;
1925 my $dbh = C4::Context->dbh;
1926 my ($query, $sth, $rv);
1930 SET datecancellationprinted = CAST(NOW() AS date)
1931 WHERE ordernumber = ?
1933 $sth = $dbh->prepare($query);
1934 $rv = $sth->execute($ordernumber);
1936 delete $order->{'ordernumber'};
1937 delete $order->{parent_ordernumber};
1938 $order->{'basketno'} = $basketno;
1940 (undef, $newordernumber) = NewOrder($order);
1943 UPDATE aqorders_items
1945 WHERE ordernumber = ?
1947 $sth = $dbh->prepare($query);
1948 $sth->execute($newordernumber, $ordernumber);
1951 INSERT INTO aqorders_transfers (ordernumber_from, ordernumber_to)
1954 $sth = $dbh->prepare($query);
1955 $sth->execute($ordernumber, $newordernumber);
1957 return $newordernumber;
1960 =head2 FUNCTIONS ABOUT PARCELS
1964 #------------------------------------------------------------#
1968 @results = &GetParcel($booksellerid, $code, $date);
1970 Looks up all of the received items from the supplier with the given
1971 bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders.
1973 C<@results> is an array of references-to-hash. The keys of each element are fields from
1974 the aqorders, biblio, and biblioitems tables of the Koha database.
1976 C<@results> is sorted alphabetically by book title.
1981 #gets all orders from a certain supplier, orders them alphabetically
1982 my ( $supplierid, $code, $datereceived ) = @_;
1983 my $dbh = C4::Context->dbh;
1986 if $code; # add % if we search on a given code (otherwise, let him empty)
1988 SELECT authorisedby,
1993 aqorders.biblionumber,
1994 aqorders.ordernumber,
1995 aqorders.parent_ordernumber,
1997 aqorders.quantityreceived,
2005 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
2006 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2007 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
2008 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2010 aqbasket.booksellerid = ?
2011 AND aqinvoices.invoicenumber LIKE ?
2012 AND aqorders.datereceived = ? ";
2014 my @query_params = ( $supplierid, $code, $datereceived );
2015 if ( C4::Context->preference("IndependentBranches") ) {
2016 unless ( C4::Context->IsSuperLibrarian() ) {
2017 $strsth .= " and (borrowers.branchcode = ?
2018 or borrowers.branchcode = '')";
2019 push @query_params, C4::Context->userenv->{branch};
2022 $strsth .= " ORDER BY aqbasket.basketno";
2023 my $result_set = $dbh->selectall_arrayref(
2028 return @{$result_set};
2031 #------------------------------------------------------------#
2035 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
2037 get a lists of parcels.
2044 is the bookseller this function has to get parcels.
2047 To know on what criteria the results list has to be ordered.
2050 is the booksellerinvoicenumber.
2052 =item $datefrom & $dateto
2053 to know on what date this function has to filter its search.
2058 a pointer on a hash list containing parcel informations as such :
2064 =item Last operation
2066 =item Number of biblio
2068 =item Number of items
2075 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
2076 my $dbh = C4::Context->dbh;
2077 my @query_params = ();
2079 SELECT aqinvoices.invoicenumber,
2080 datereceived,purchaseordernumber,
2081 count(DISTINCT biblionumber) AS biblio,
2082 sum(quantity) AS itemsexpected,
2083 sum(quantityreceived) AS itemsreceived
2084 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
2085 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2086 WHERE aqbasket.booksellerid = ? and datereceived IS NOT NULL
2088 push @query_params, $bookseller;
2090 if ( defined $code ) {
2091 $strsth .= ' and aqinvoices.invoicenumber like ? ';
2092 # add a % to the end of the code to allow stemming.
2093 push @query_params, "$code%";
2096 if ( defined $datefrom ) {
2097 $strsth .= ' and datereceived >= ? ';
2098 push @query_params, $datefrom;
2101 if ( defined $dateto ) {
2102 $strsth .= 'and datereceived <= ? ';
2103 push @query_params, $dateto;
2106 $strsth .= "group by aqinvoices.invoicenumber,datereceived ";
2108 # can't use a placeholder to place this column name.
2109 # but, we could probably be checking to make sure it is a column that will be fetched.
2110 $strsth .= "order by $order " if ($order);
2112 my $sth = $dbh->prepare($strsth);
2114 $sth->execute( @query_params );
2115 my $results = $sth->fetchall_arrayref({});
2119 #------------------------------------------------------------#
2121 =head3 GetLateOrders
2123 @results = &GetLateOrders;
2125 Searches for bookseller with late orders.
2128 the table of supplier with late issues. This table is full of hashref.
2134 my $supplierid = shift;
2136 my $estimateddeliverydatefrom = shift;
2137 my $estimateddeliverydateto = shift;
2139 my $dbh = C4::Context->dbh;
2141 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
2142 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
2144 my @query_params = ();
2146 SELECT aqbasket.basketno,
2147 aqorders.ordernumber,
2148 DATE(aqbasket.closedate) AS orderdate,
2149 aqbasket.basketname AS basketname,
2150 aqbasket.basketgroupid AS basketgroupid,
2151 aqbasketgroups.name AS basketgroupname,
2152 aqorders.rrp AS unitpricesupplier,
2153 aqorders.ecost AS unitpricelib,
2154 aqorders.claims_count AS claims_count,
2155 aqorders.claimed_date AS claimed_date,
2156 aqbudgets.budget_name AS budget,
2157 borrowers.branchcode AS branch,
2158 aqbooksellers.name AS supplier,
2159 aqbooksellers.id AS supplierid,
2160 biblio.author, biblio.title,
2161 biblioitems.publishercode AS publisher,
2162 biblioitems.publicationyear,
2163 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
2167 aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
2168 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
2169 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
2170 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
2171 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
2172 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
2173 WHERE aqorders.basketno = aqbasket.basketno
2174 AND ( datereceived = ''
2175 OR datereceived IS NULL
2176 OR aqorders.quantityreceived < aqorders.quantity
2178 AND aqbasket.closedate IS NOT NULL
2179 AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
2182 if ($dbdriver eq "mysql") {
2184 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity,
2185 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
2186 DATEDIFF(CAST(now() AS date),closedate) AS latesince
2188 if ( defined $delay ) {
2189 $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ;
2190 push @query_params, $delay;
2193 HAVING quantity <> 0
2194 AND unitpricesupplier <> 0
2195 AND unitpricelib <> 0
2198 # FIXME: account for IFNULL as above
2200 aqorders.quantity AS quantity,
2201 aqorders.quantity * aqorders.rrp AS subtotal,
2202 (CAST(now() AS date) - closedate) AS latesince
2204 if ( defined $delay ) {
2205 $from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) ";
2206 push @query_params, $delay;
2209 if (defined $supplierid) {
2210 $from .= ' AND aqbasket.booksellerid = ? ';
2211 push @query_params, $supplierid;
2213 if (defined $branch) {
2214 $from .= ' AND borrowers.branchcode LIKE ? ';
2215 push @query_params, $branch;
2218 if ( defined $estimateddeliverydatefrom or defined $estimateddeliverydateto ) {
2219 $from .= ' AND aqbooksellers.deliverytime IS NOT NULL ';
2221 if ( defined $estimateddeliverydatefrom ) {
2222 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
2223 push @query_params, $estimateddeliverydatefrom;
2225 if ( defined $estimateddeliverydateto ) {
2226 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
2227 push @query_params, $estimateddeliverydateto;
2229 if ( defined $estimateddeliverydatefrom and not defined $estimateddeliverydateto ) {
2230 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
2232 if (C4::Context->preference("IndependentBranches")
2233 && !C4::Context->IsSuperLibrarian() ) {
2234 $from .= ' AND borrowers.branchcode LIKE ? ';
2235 push @query_params, C4::Context->userenv->{branch};
2237 $from .= " AND orderstatus <> 'cancelled' ";
2238 my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier";
2239 $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params);
2240 my $sth = $dbh->prepare($query);
2241 $sth->execute(@query_params);
2243 while (my $data = $sth->fetchrow_hashref) {
2244 push @results, $data;
2249 #------------------------------------------------------------#
2253 (\@order_loop, $total_qty, $total_price, $total_qtyreceived) = GetHistory( %params );
2255 Retreives some acquisition history information
2265 basket - search both basket name and number
2266 booksellerinvoicenumber
2269 orderstatus (note that orderstatus '' will retrieve orders
2270 of any status except cancelled)
2272 get_canceled_order (if set to a true value, cancelled orders will
2276 $order_loop is a list of hashrefs that each look like this:
2278 'author' => 'Twain, Mark',
2280 'biblionumber' => '215',
2282 'creationdate' => 'MM/DD/YYYY',
2283 'datereceived' => undef,
2286 'invoicenumber' => undef,
2288 'ordernumber' => '1',
2290 'quantityreceived' => undef,
2291 'title' => 'The Adventures of Huckleberry Finn'
2293 $total_qty is the sum of all of the quantities in $order_loop
2294 $total_price is the cost of each in $order_loop times the quantity
2295 $total_qtyreceived is the sum of all of the quantityreceived entries in $order_loop
2300 # don't run the query if there are no parameters (list would be too long for sure !)
2301 croak "No search params" unless @_;
2303 my $title = $params{title};
2304 my $author = $params{author};
2305 my $isbn = $params{isbn};
2306 my $ean = $params{ean};
2307 my $name = $params{name};
2308 my $from_placed_on = $params{from_placed_on};
2309 my $to_placed_on = $params{to_placed_on};
2310 my $basket = $params{basket};
2311 my $booksellerinvoicenumber = $params{booksellerinvoicenumber};
2312 my $basketgroupname = $params{basketgroupname};
2313 my $budget = $params{budget};
2314 my $orderstatus = $params{orderstatus};
2315 my $biblionumber = $params{biblionumber};
2316 my $get_canceled_order = $params{get_canceled_order} || 0;
2317 my $ordernumber = $params{ordernumber};
2318 my $search_children_too = $params{search_children_too} || 0;
2322 my $total_qtyreceived = 0;
2323 my $total_price = 0;
2325 my $dbh = C4::Context->dbh;
2328 COALESCE(biblio.title, deletedbiblio.title) AS title,
2329 COALESCE(biblio.author, deletedbiblio.author) AS author,
2330 COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn,
2331 COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean,
2333 aqbasket.basketname,
2334 aqbasket.basketgroupid,
2335 aqbasketgroups.name as groupname,
2337 aqbasket.creationdate,
2338 aqorders.datereceived,
2340 aqorders.quantityreceived,
2342 aqorders.ordernumber,
2344 aqinvoices.invoicenumber,
2345 aqbooksellers.id as id,
2346 aqorders.biblionumber,
2347 aqorders.orderstatus,
2348 aqorders.parent_ordernumber,
2349 aqbudgets.budget_name
2351 $query .= ", aqbudgets.budget_id AS budget" if defined $budget;
2354 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
2355 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
2356 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
2357 LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
2358 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
2359 LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id
2360 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2361 LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.biblionumber
2362 LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=aqorders.biblionumber
2365 if ( C4::Context->preference("IndependentBranches") ) {
2366 $query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber";
2369 $query .= " WHERE 1 ";
2371 unless ($get_canceled_order or (defined $orderstatus and $orderstatus eq 'cancelled')) {
2372 $query .= " AND (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') ";
2375 my @query_params = ();
2377 if ( $biblionumber ) {
2378 $query .= " AND biblio.biblionumber = ?";
2379 push @query_params, $biblionumber;
2383 $query .= " AND biblio.title LIKE ? ";
2384 $title =~ s/\s+/%/g;
2385 push @query_params, "%$title%";
2389 $query .= " AND biblio.author LIKE ? ";
2390 push @query_params, "%$author%";
2394 $query .= " AND biblioitems.isbn LIKE ? ";
2395 push @query_params, "%$isbn%";
2398 $query .= " AND biblioitems.ean = ? ";
2399 push @query_params, "$ean";
2402 $query .= " AND aqbooksellers.name LIKE ? ";
2403 push @query_params, "%$name%";
2407 $query .= " AND aqbudgets.budget_id = ? ";
2408 push @query_params, "$budget";
2411 if ( $from_placed_on ) {
2412 $query .= " AND creationdate >= ? ";
2413 push @query_params, $from_placed_on;
2416 if ( $to_placed_on ) {
2417 $query .= " AND creationdate <= ? ";
2418 push @query_params, $to_placed_on;
2421 if ( defined $orderstatus and $orderstatus ne '') {
2422 $query .= " AND aqorders.orderstatus = ? ";
2423 push @query_params, "$orderstatus";
2427 if ($basket =~ m/^\d+$/) {
2428 $query .= " AND aqorders.basketno = ? ";
2429 push @query_params, $basket;
2431 $query .= " AND aqbasket.basketname LIKE ? ";
2432 push @query_params, "%$basket%";
2436 if ($booksellerinvoicenumber) {
2437 $query .= " AND aqinvoices.invoicenumber LIKE ? ";
2438 push @query_params, "%$booksellerinvoicenumber%";
2441 if ($basketgroupname) {
2442 $query .= " AND aqbasketgroups.name LIKE ? ";
2443 push @query_params, "%$basketgroupname%";
2447 $query .= " AND (aqorders.ordernumber = ? ";
2448 push @query_params, $ordernumber;
2449 if ($search_children_too) {
2450 $query .= " OR aqorders.parent_ordernumber = ? ";
2451 push @query_params, $ordernumber;
2457 if ( C4::Context->preference("IndependentBranches") ) {
2458 unless ( C4::Context->IsSuperLibrarian() ) {
2459 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
2460 push @query_params, C4::Context->userenv->{branch};
2463 $query .= " ORDER BY id";
2464 my $sth = $dbh->prepare($query);
2465 $sth->execute( @query_params );
2467 while ( my $line = $sth->fetchrow_hashref ) {
2468 $line->{count} = $cnt++;
2469 $line->{toggle} = 1 if $cnt % 2;
2470 push @order_loop, $line;
2471 $total_qty += ( $line->{quantity} ) ? $line->{quantity} : 0;
2472 $total_qtyreceived += ( $line->{quantityreceived} ) ? $line->{quantityreceived} : 0;
2473 $total_price += ( $line->{quantity} and $line->{ecost} ) ? $line->{quantity} * $line->{ecost} : 0;
2475 return \@order_loop, $total_qty, $total_price, $total_qtyreceived;
2478 =head2 GetRecentAcqui
2480 $results = GetRecentAcqui($days);
2482 C<$results> is a ref to a table which containts hashref
2486 sub GetRecentAcqui {
2488 my $dbh = C4::Context->dbh;
2492 ORDER BY timestamp DESC
2495 my $sth = $dbh->prepare($query);
2497 my $results = $sth->fetchall_arrayref({});
2501 #------------------------------------------------------------#
2507 &AddClaim($ordernumber);
2509 Add a claim for an order
2516 my ($ordernumber) = @_;
2517 my $dbh = C4::Context->dbh;
2520 claims_count = claims_count + 1,
2521 claimed_date = CURDATE()
2522 WHERE ordernumber = ?
2524 my $sth = $dbh->prepare($query);
2525 $sth->execute($ordernumber);
2530 my @invoices = GetInvoices(
2531 invoicenumber => $invoicenumber,
2532 supplierid => $supplierid,
2533 suppliername => $suppliername,
2534 shipmentdatefrom => $shipmentdatefrom, # ISO format
2535 shipmentdateto => $shipmentdateto, # ISO format
2536 billingdatefrom => $billingdatefrom, # ISO format
2537 billingdateto => $billingdateto, # ISO format
2538 isbneanissn => $isbn_or_ean_or_issn,
2541 publisher => $publisher,
2542 publicationyear => $publicationyear,
2543 branchcode => $branchcode,
2544 order_by => $order_by
2547 Return a list of invoices that match all given criteria.
2549 $order_by is "column_name (asc|desc)", where column_name is any of
2550 'invoicenumber', 'booksellerid', 'shipmentdate', 'billingdate', 'closedate',
2551 'shipmentcost', 'shipmentcost_budgetid'.
2553 asc is the default if omitted
2560 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2561 closedate shipmentcost shipmentcost_budgetid);
2563 my $dbh = C4::Context->dbh;
2565 SELECT aqinvoices.*, aqbooksellers.name AS suppliername,
2568 aqorders.datereceived IS NOT NULL,
2569 aqorders.biblionumber,
2572 ) AS receivedbiblios,
2575 aqorders.subscriptionid IS NOT NULL,
2576 aqorders.subscriptionid,
2579 ) AS is_linked_to_subscriptions,
2580 SUM(aqorders.quantityreceived) AS receiveditems
2582 LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid
2583 LEFT JOIN aqorders ON aqorders.invoiceid = aqinvoices.invoiceid
2584 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
2585 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2586 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2587 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
2588 LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber
2593 if($args{supplierid}) {
2594 push @bind_strs, " aqinvoices.booksellerid = ? ";
2595 push @bind_args, $args{supplierid};
2597 if($args{invoicenumber}) {
2598 push @bind_strs, " aqinvoices.invoicenumber LIKE ? ";
2599 push @bind_args, "%$args{invoicenumber}%";
2601 if($args{suppliername}) {
2602 push @bind_strs, " aqbooksellers.name LIKE ? ";
2603 push @bind_args, "%$args{suppliername}%";
2605 if($args{shipmentdatefrom}) {
2606 push @bind_strs, " aqinvoices.shipmentdate >= ? ";
2607 push @bind_args, $args{shipmentdatefrom};
2609 if($args{shipmentdateto}) {
2610 push @bind_strs, " aqinvoices.shipmentdate <= ? ";
2611 push @bind_args, $args{shipmentdateto};
2613 if($args{billingdatefrom}) {
2614 push @bind_strs, " aqinvoices.billingdate >= ? ";
2615 push @bind_args, $args{billingdatefrom};
2617 if($args{billingdateto}) {
2618 push @bind_strs, " aqinvoices.billingdate <= ? ";
2619 push @bind_args, $args{billingdateto};
2621 if($args{isbneanissn}) {
2622 push @bind_strs, " (biblioitems.isbn LIKE CONCAT('%', ?, '%') OR biblioitems.ean LIKE CONCAT('%', ?, '%') OR biblioitems.issn LIKE CONCAT('%', ?, '%') ) ";
2623 push @bind_args, $args{isbneanissn}, $args{isbneanissn}, $args{isbneanissn};
2626 push @bind_strs, " biblio.title LIKE CONCAT('%', ?, '%') ";
2627 push @bind_args, $args{title};
2630 push @bind_strs, " biblio.author LIKE CONCAT('%', ?, '%') ";
2631 push @bind_args, $args{author};
2633 if($args{publisher}) {
2634 push @bind_strs, " biblioitems.publishercode LIKE CONCAT('%', ?, '%') ";
2635 push @bind_args, $args{publisher};
2637 if($args{publicationyear}) {
2638 push @bind_strs, " ((biblioitems.publicationyear LIKE CONCAT('%', ?, '%')) OR (biblio.copyrightdate LIKE CONCAT('%', ?, '%'))) ";
2639 push @bind_args, $args{publicationyear}, $args{publicationyear};
2641 if($args{branchcode}) {
2642 push @bind_strs, " borrowers.branchcode = ? ";
2643 push @bind_args, $args{branchcode};
2646 $query .= " WHERE " . join(" AND ", @bind_strs) if @bind_strs;
2647 $query .= " GROUP BY aqinvoices.invoiceid ";
2649 if($args{order_by}) {
2650 my ($column, $direction) = split / /, $args{order_by};
2651 if(grep /^$column$/, @columns) {
2652 $direction ||= 'ASC';
2653 $query .= " ORDER BY $column $direction";
2657 my $sth = $dbh->prepare($query);
2658 $sth->execute(@bind_args);
2660 my $results = $sth->fetchall_arrayref({});
2666 my $invoice = GetInvoice($invoiceid);
2668 Get informations about invoice with given $invoiceid
2670 Return a hash filled with aqinvoices.* fields
2675 my ($invoiceid) = @_;
2678 return unless $invoiceid;
2680 my $dbh = C4::Context->dbh;
2686 my $sth = $dbh->prepare($query);
2687 $sth->execute($invoiceid);
2689 $invoice = $sth->fetchrow_hashref;
2693 =head3 GetInvoiceDetails
2695 my $invoice = GetInvoiceDetails($invoiceid)
2697 Return informations about an invoice + the list of related order lines
2699 Orders informations are in $invoice->{orders} (array ref)
2703 sub GetInvoiceDetails {
2704 my ($invoiceid) = @_;
2706 if ( !defined $invoiceid ) {
2707 carp 'GetInvoiceDetails called without an invoiceid';
2711 my $dbh = C4::Context->dbh;
2713 SELECT aqinvoices.*, aqbooksellers.name AS suppliername
2715 LEFT JOIN aqbooksellers ON aqinvoices.booksellerid = aqbooksellers.id
2718 my $sth = $dbh->prepare($query);
2719 $sth->execute($invoiceid);
2721 my $invoice = $sth->fetchrow_hashref;
2724 SELECT aqorders.*, biblio.*, aqbasket.basketname
2726 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
2727 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2730 $sth = $dbh->prepare($query);
2731 $sth->execute($invoiceid);
2732 $invoice->{orders} = $sth->fetchall_arrayref({});
2733 $invoice->{orders} ||= []; # force an empty arrayref if fetchall_arrayref fails
2740 my $invoiceid = AddInvoice(
2741 invoicenumber => $invoicenumber,
2742 booksellerid => $booksellerid,
2743 shipmentdate => $shipmentdate,
2744 billingdate => $billingdate,
2745 closedate => $closedate,
2746 shipmentcost => $shipmentcost,
2747 shipmentcost_budgetid => $shipmentcost_budgetid
2750 Create a new invoice and return its id or undef if it fails.
2757 return unless(%invoice and $invoice{invoicenumber});
2759 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2760 closedate shipmentcost shipmentcost_budgetid);
2764 foreach my $key (keys %invoice) {
2765 if(0 < grep(/^$key$/, @columns)) {
2766 push @set_strs, "$key = ?";
2767 push @set_args, ($invoice{$key} || undef);
2773 my $dbh = C4::Context->dbh;
2774 my $query = "INSERT INTO aqinvoices SET ";
2775 $query .= join (",", @set_strs);
2776 my $sth = $dbh->prepare($query);
2777 $rv = $sth->execute(@set_args);
2779 $rv = $dbh->last_insert_id(undef, undef, 'aqinvoices', undef);
2788 invoiceid => $invoiceid, # Mandatory
2789 invoicenumber => $invoicenumber,
2790 booksellerid => $booksellerid,
2791 shipmentdate => $shipmentdate,
2792 billingdate => $billingdate,
2793 closedate => $closedate,
2794 shipmentcost => $shipmentcost,
2795 shipmentcost_budgetid => $shipmentcost_budgetid
2798 Modify an invoice, invoiceid is mandatory.
2800 Return undef if it fails.
2807 return unless(%invoice and $invoice{invoiceid});
2809 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2810 closedate shipmentcost shipmentcost_budgetid);
2814 foreach my $key (keys %invoice) {
2815 if(0 < grep(/^$key$/, @columns)) {
2816 push @set_strs, "$key = ?";
2817 push @set_args, ($invoice{$key} || undef);
2821 my $dbh = C4::Context->dbh;
2822 my $query = "UPDATE aqinvoices SET ";
2823 $query .= join(",", @set_strs);
2824 $query .= " WHERE invoiceid = ?";
2826 my $sth = $dbh->prepare($query);
2827 $sth->execute(@set_args, $invoice{invoiceid});
2832 CloseInvoice($invoiceid);
2836 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => undef);
2841 my ($invoiceid) = @_;
2843 return unless $invoiceid;
2845 my $dbh = C4::Context->dbh;
2848 SET closedate = CAST(NOW() AS DATE)
2851 my $sth = $dbh->prepare($query);
2852 $sth->execute($invoiceid);
2855 =head3 ReopenInvoice
2857 ReopenInvoice($invoiceid);
2861 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => C4::Dates->new()->output('iso'))
2866 my ($invoiceid) = @_;
2868 return unless $invoiceid;
2870 my $dbh = C4::Context->dbh;
2873 SET closedate = NULL
2876 my $sth = $dbh->prepare($query);
2877 $sth->execute($invoiceid);
2882 DelInvoice($invoiceid);
2884 Delete an invoice if there are no items attached to it.
2889 my ($invoiceid) = @_;
2891 return unless $invoiceid;
2893 my $dbh = C4::Context->dbh;
2899 my $sth = $dbh->prepare($query);
2900 $sth->execute($invoiceid);
2901 my $res = $sth->fetchrow_arrayref;
2902 if ( $res && $res->[0] == 0 ) {
2904 DELETE FROM aqinvoices
2907 my $sth = $dbh->prepare($query);
2908 return ( $sth->execute($invoiceid) > 0 );
2913 =head3 MergeInvoices
2915 MergeInvoices($invoiceid, \@sourceids);
2917 Merge the invoices identified by the IDs in \@sourceids into
2918 the invoice identified by $invoiceid.
2923 my ($invoiceid, $sourceids) = @_;
2925 return unless $invoiceid;
2926 foreach my $sourceid (@$sourceids) {
2927 next if $sourceid == $invoiceid;
2928 my $source = GetInvoiceDetails($sourceid);
2929 foreach my $order (@{$source->{'orders'}}) {
2930 $order->{'invoiceid'} = $invoiceid;
2933 DelInvoice($source->{'invoiceid'});
2938 =head3 GetBiblioCountByBasketno
2940 $biblio_count = &GetBiblioCountByBasketno($basketno);
2942 Looks up the biblio's count that has basketno value $basketno
2948 sub GetBiblioCountByBasketno {
2949 my ($basketno) = @_;
2950 my $dbh = C4::Context->dbh;
2952 SELECT COUNT( DISTINCT( biblionumber ) )
2955 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
2958 my $sth = $dbh->prepare($query);
2959 $sth->execute($basketno);
2960 return $sth->fetchrow;
2968 Koha Development Team <http://koha-community.org/>