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.
26 use C4::Dates qw(format_date format_date_in_iso);
31 use C4::SQLHelper qw(InsertInTable UpdateInTable);
32 use C4::Bookseller qw(GetBookSellerFromId);
33 use C4::Templates qw(gettemplate);
38 use vars qw($VERSION @ISA @EXPORT);
41 # set the version for version checking
42 $VERSION = 3.07.00.049;
46 &GetBasket &NewBasket &CloseBasket &ReopenBasket &DelBasket &ModBasket
47 &GetBasketAsCSV &GetBasketGroupAsCSV
48 &GetBasketsByBookseller &GetBasketsByBasketgroup
49 &GetBasketsInfosByBookseller
51 &GetBasketUsers &ModBasketUsers
56 &ModBasketgroup &NewBasketgroup &DelBasketgroup &GetBasketgroup &CloseBasketgroup
57 &GetBasketgroups &ReOpenBasketgroup
59 &NewOrder &DelOrder &ModOrder &GetOrder &GetOrders &GetOrdersByBiblionumber
60 &GetLateOrders &GetOrderFromItemnumber
61 &SearchOrders &GetHistory &GetRecentAcqui
62 &ModReceiveOrder &CancelReceipt
63 &GetCancelledOrders &TransferOrder
64 &GetLastOrderNotReceivedFromSubscriptionid &GetLastOrderReceivedFromSubscriptionid
65 &NewOrderItem &ModItemOrder
67 &GetParcels &GetParcel
68 &GetContracts &GetContract
80 &GetItemnumbersFromOrder
83 &GetBiblioCountByBasketno
91 sub GetOrderFromItemnumber {
92 my ($itemnumber) = @_;
93 my $dbh = C4::Context->dbh;
96 SELECT * from aqorders LEFT JOIN aqorders_items
97 ON ( aqorders.ordernumber = aqorders_items.ordernumber )
98 WHERE itemnumber = ? |;
100 my $sth = $dbh->prepare($query);
104 $sth->execute($itemnumber);
106 my $order = $sth->fetchrow_hashref;
111 # Returns the itemnumber(s) associated with the ordernumber given in parameter
112 sub GetItemnumbersFromOrder {
113 my ($ordernumber) = @_;
114 my $dbh = C4::Context->dbh;
115 my $query = "SELECT itemnumber FROM aqorders_items WHERE ordernumber=?";
116 my $sth = $dbh->prepare($query);
117 $sth->execute($ordernumber);
120 while (my $order = $sth->fetchrow_hashref) {
121 push @tab, $order->{'itemnumber'};
135 C4::Acquisition - Koha functions for dealing with orders and acquisitions
143 The functions in this module deal with acquisitions, managing book
144 orders, basket and parcels.
148 =head2 FUNCTIONS ABOUT BASKETS
152 $aqbasket = &GetBasket($basketnumber);
154 get all basket informations in aqbasket for a given basket
156 B<returns:> informations for a given basket returned as a hashref.
162 my $dbh = C4::Context->dbh;
165 concat( b.firstname,' ',b.surname) AS authorisedbyname
167 LEFT JOIN borrowers b ON aqbasket.authorisedby=b.borrowernumber
170 my $sth=$dbh->prepare($query);
171 $sth->execute($basketno);
172 my $basket = $sth->fetchrow_hashref;
176 #------------------------------------------------------------#
180 $basket = &NewBasket( $booksellerid, $authorizedby, $basketname,
181 $basketnote, $basketbooksellernote, $basketcontractnumber, $deliveryplace, $billingplace );
183 Create a new basket in aqbasket table
187 =item C<$booksellerid> is a foreign key in the aqbasket table
189 =item C<$authorizedby> is the username of who created the basket
193 The other parameters are optional, see ModBasketHeader for more info on them.
198 my ( $booksellerid, $authorisedby, $basketname, $basketnote,
199 $basketbooksellernote, $basketcontractnumber, $deliveryplace,
200 $billingplace ) = @_;
201 my $dbh = C4::Context->dbh;
203 'INSERT INTO aqbasket (creationdate,booksellerid,authorisedby) '
204 . 'VALUES (now(),?,?)';
205 $dbh->do( $query, {}, $booksellerid, $authorisedby );
207 my $basket = $dbh->{mysql_insertid};
208 $basketname ||= q{}; # default to empty strings
210 $basketbooksellernote ||= q{};
211 ModBasketHeader( $basket, $basketname, $basketnote, $basketbooksellernote,
212 $basketcontractnumber, $booksellerid, $deliveryplace, $billingplace );
216 #------------------------------------------------------------#
220 &CloseBasket($basketno);
222 close a basket (becomes unmodifiable, except for receives)
228 my $dbh = C4::Context->dbh;
234 my $sth = $dbh->prepare($query);
235 $sth->execute($basketno);
237 my @orders = GetOrders($basketno);
238 foreach my $order (@orders) {
241 SET orderstatus = 'ordered'
242 WHERE ordernumber = ?;
244 $sth = $dbh->prepare($query);
245 $sth->execute($order->{'ordernumber'});
251 &ReopenBasket($basketno);
259 my $dbh = C4::Context->dbh;
265 my $sth = $dbh->prepare($query);
266 $sth->execute($basketno);
268 my @orders = GetOrders($basketno);
269 foreach my $order (@orders) {
272 SET orderstatus = 'new'
273 WHERE ordernumber = ?;
275 $sth = $dbh->prepare($query);
276 $sth->execute($order->{'ordernumber'});
280 #------------------------------------------------------------#
282 =head3 GetBasketAsCSV
284 &GetBasketAsCSV($basketno);
286 Export a basket as CSV
288 $cgi parameter is needed for column name translation
293 my ($basketno, $cgi) = @_;
294 my $basket = GetBasket($basketno);
295 my @orders = GetOrders($basketno);
296 my $contract = GetContract($basket->{'contractnumber'});
298 my $template = C4::Templates::gettemplate("acqui/csv/basket.tmpl", "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_internalnote'},
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.tmpl', 'intranet', $cgi);
362 for my $basket (@$baskets) {
363 my @orders = GetOrders( $$basket{basketno} );
364 my $contract = GetContract( $$basket{contractnumber} );
365 my $bookseller = GetBookSellerFromId( $$basket{booksellerid} );
366 my $basketgroup = GetBasketgroup( $$basket{basketgroupid} );
368 foreach my $order (@orders) {
369 my $bd = GetBiblioData( $order->{'biblionumber'} );
371 clientnumber => $bookseller->{accountnumber},
372 basketname => $basket->{basketname},
373 ordernumber => $order->{ordernumber},
374 author => $bd->{author},
375 title => $bd->{title},
376 publishercode => $bd->{publishercode},
377 publicationyear => $bd->{publicationyear},
378 collectiontitle => $bd->{collectiontitle},
379 isbn => $order->{isbn},
380 quantity => $order->{quantity},
381 rrp => $order->{rrp},
382 discount => $bookseller->{discount},
383 ecost => $order->{ecost},
384 notes => $order->{order_internalnote},
385 entrydate => $order->{entrydate},
386 booksellername => $bookseller->{name},
387 bookselleraddress => $bookseller->{address1},
388 booksellerpostal => $bookseller->{postal},
389 contractnumber => $contract->{contractnumber},
390 contractname => $contract->{contractname},
391 basketgroupdeliveryplace => C4::Branch::GetBranchName( $basketgroup->{deliveryplace} ),
392 basketgroupbillingplace => C4::Branch::GetBranchName( $basketgroup->{billingplace} ),
393 basketdeliveryplace => C4::Branch::GetBranchName( $basket->{deliveryplace} ),
394 basketbillingplace => C4::Branch::GetBranchName( $basket->{billingplace} ),
397 basketname author title publishercode collectiontitle notes
398 booksellername bookselleraddress booksellerpostal contractname
399 basketgroupdeliveryplace basketgroupbillingplace
400 basketdeliveryplace basketbillingplace
402 # Double the quotes to not be interpreted as a field end
403 $row->{$_} =~ s/"/""/g if $row->{$_};
408 $template->param(rows => \@rows);
410 return $template->output;
414 =head3 CloseBasketgroup
416 &CloseBasketgroup($basketgroupno);
422 sub CloseBasketgroup {
423 my ($basketgroupno) = @_;
424 my $dbh = C4::Context->dbh;
425 my $sth = $dbh->prepare("
426 UPDATE aqbasketgroups
430 $sth->execute($basketgroupno);
433 #------------------------------------------------------------#
435 =head3 ReOpenBaskergroup($basketgroupno)
437 &ReOpenBaskergroup($basketgroupno);
443 sub ReOpenBasketgroup {
444 my ($basketgroupno) = @_;
445 my $dbh = C4::Context->dbh;
446 my $sth = $dbh->prepare("
447 UPDATE aqbasketgroups
451 $sth->execute($basketgroupno);
454 #------------------------------------------------------------#
459 &DelBasket($basketno);
461 Deletes the basket that has basketno field $basketno in the aqbasket table.
465 =item C<$basketno> is the primary key of the basket in the aqbasket table.
472 my ( $basketno ) = @_;
473 my $query = "DELETE FROM aqbasket WHERE basketno=?";
474 my $dbh = C4::Context->dbh;
475 my $sth = $dbh->prepare($query);
476 $sth->execute($basketno);
480 #------------------------------------------------------------#
484 &ModBasket($basketinfo);
486 Modifies a basket, using a hashref $basketinfo for the relevant information, only $basketinfo->{'basketno'} is required.
490 =item C<$basketno> is the primary key of the basket in the aqbasket table.
497 my $basketinfo = shift;
498 my $query = "UPDATE aqbasket SET ";
500 foreach my $key (keys %$basketinfo){
501 if ($key ne 'basketno'){
502 $query .= "$key=?, ";
503 push(@params, $basketinfo->{$key} || undef );
506 # get rid of the "," at the end of $query
507 if (substr($query, length($query)-2) eq ', '){
512 $query .= "WHERE basketno=?";
513 push(@params, $basketinfo->{'basketno'});
514 my $dbh = C4::Context->dbh;
515 my $sth = $dbh->prepare($query);
516 $sth->execute(@params);
521 #------------------------------------------------------------#
523 =head3 ModBasketHeader
525 &ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid);
527 Modifies a basket's header.
531 =item C<$basketno> is the "basketno" field in the "aqbasket" table;
533 =item C<$basketname> is the "basketname" field in the "aqbasket" table;
535 =item C<$note> is the "note" field in the "aqbasket" table;
537 =item C<$booksellernote> is the "booksellernote" field in the "aqbasket" table;
539 =item C<$contractnumber> is the "contractnumber" (foreign) key in the "aqbasket" table.
541 =item C<$booksellerid> is the id (foreign) key in the "aqbooksellers" table for the vendor.
543 =item C<$deliveryplace> is the "deliveryplace" field in the aqbasket table.
545 =item C<$billingplace> is the "billingplace" field in the aqbasket table.
551 sub ModBasketHeader {
552 my ($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid, $deliveryplace, $billingplace) = @_;
555 SET basketname=?, note=?, booksellernote=?, booksellerid=?, deliveryplace=?, billingplace=?
559 my $dbh = C4::Context->dbh;
560 my $sth = $dbh->prepare($query);
561 $sth->execute($basketname, $note, $booksellernote, $booksellerid, $deliveryplace, $billingplace, $basketno);
563 if ( $contractnumber ) {
564 my $query2 ="UPDATE aqbasket SET contractnumber=? WHERE basketno=?";
565 my $sth2 = $dbh->prepare($query2);
566 $sth2->execute($contractnumber,$basketno);
571 #------------------------------------------------------------#
573 =head3 GetBasketsByBookseller
575 @results = &GetBasketsByBookseller($booksellerid, $extra);
577 Returns a list of hashes of all the baskets that belong to bookseller 'booksellerid'.
581 =item C<$booksellerid> is the 'id' field of the bookseller in the aqbooksellers table
583 =item C<$extra> is the extra sql parameters, can be
585 $extra->{groupby}: group baskets by column
586 ex. $extra->{groupby} = aqbasket.basketgroupid
587 $extra->{orderby}: order baskets by column
588 $extra->{limit}: limit number of results (can be helpful for pagination)
594 sub GetBasketsByBookseller {
595 my ($booksellerid, $extra) = @_;
596 my $query = "SELECT * FROM aqbasket WHERE booksellerid=?";
598 if ($extra->{groupby}) {
599 $query .= " GROUP by $extra->{groupby}";
601 if ($extra->{orderby}){
602 $query .= " ORDER by $extra->{orderby}";
604 if ($extra->{limit}){
605 $query .= " LIMIT $extra->{limit}";
608 my $dbh = C4::Context->dbh;
609 my $sth = $dbh->prepare($query);
610 $sth->execute($booksellerid);
611 return $sth->fetchall_arrayref({});
614 =head3 GetBasketsInfosByBookseller
616 my $baskets = GetBasketsInfosByBookseller($supplierid, $allbaskets);
618 The optional second parameter allbaskets is a boolean allowing you to
619 select all baskets from the supplier; by default only active baskets (open or
620 closed but still something to receive) are returned.
622 Returns in a arrayref of hashref all about booksellers baskets, plus:
623 total_biblios: Number of distinct biblios in basket
624 total_items: Number of items in basket
625 expected_items: Number of non-received items in basket
629 sub GetBasketsInfosByBookseller {
630 my ($supplierid, $allbaskets) = @_;
632 return unless $supplierid;
634 my $dbh = C4::Context->dbh;
637 SUM(aqorders.quantity) AS total_items,
639 IF ( aqorders.orderstatus = 'cancelled', aqorders.quantity, 0 )
640 ) AS total_items_cancelled,
641 COUNT(DISTINCT aqorders.biblionumber) AS total_biblios,
643 IF(aqorders.datereceived IS NULL
644 AND aqorders.datecancellationprinted IS NULL
649 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
650 WHERE booksellerid = ?};
652 unless ( $allbaskets ) {
653 $query.=" AND (closedate IS NULL OR (aqorders.quantity > aqorders.quantityreceived AND datecancellationprinted IS NULL))";
655 $query.=" GROUP BY aqbasket.basketno";
657 my $sth = $dbh->prepare($query);
658 $sth->execute($supplierid);
659 my $baskets = $sth->fetchall_arrayref({});
661 # Retrieve the number of biblios cancelled
662 my $cancelled_biblios = $dbh->selectall_hashref( q|
663 SELECT COUNT(DISTINCT(biblionumber)) AS total_biblios_cancelled, aqbasket.basketno
665 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
666 WHERE booksellerid = ?
667 AND aqorders.orderstatus = 'cancelled'
668 GROUP BY aqbasket.basketno
669 |, 'basketno', {}, $supplierid );
671 $_->{total_biblios_cancelled} = $cancelled_biblios->{$_->{basketno}}{total_biblios_cancelled} || 0
677 =head3 GetBasketUsers
679 $basketusers_ids = &GetBasketUsers($basketno);
681 Returns a list of all borrowernumbers that are in basket users list
686 my $basketno = shift;
688 return unless $basketno;
691 SELECT borrowernumber
695 my $dbh = C4::Context->dbh;
696 my $sth = $dbh->prepare($query);
697 $sth->execute($basketno);
698 my $results = $sth->fetchall_arrayref( {} );
701 foreach (@$results) {
702 push @borrowernumbers, $_->{'borrowernumber'};
705 return @borrowernumbers;
708 =head3 ModBasketUsers
710 my @basketusers_ids = (1, 2, 3);
711 &ModBasketUsers($basketno, @basketusers_ids);
713 Delete all users from basket users list, and add users in C<@basketusers_ids>
719 my ($basketno, @basketusers_ids) = @_;
721 return unless $basketno;
723 my $dbh = C4::Context->dbh;
725 DELETE FROM aqbasketusers
728 my $sth = $dbh->prepare($query);
729 $sth->execute($basketno);
732 INSERT INTO aqbasketusers (basketno, borrowernumber)
735 $sth = $dbh->prepare($query);
736 foreach my $basketuser_id (@basketusers_ids) {
737 $sth->execute($basketno, $basketuser_id);
742 =head3 CanUserManageBasket
744 my $bool = CanUserManageBasket($borrower, $basket[, $userflags]);
745 my $bool = CanUserManageBasket($borrowernumber, $basketno[, $userflags]);
747 Check if a borrower can manage a basket, according to system preference
748 AcqViewBaskets, user permissions and basket properties (creator, users list,
751 First parameter can be either a borrowernumber or a hashref as returned by
752 C4::Members::GetMember.
754 Second parameter can be either a basketno or a hashref as returned by
755 C4::Acquisition::GetBasket.
757 The third parameter is optional. If given, it should be a hashref as returned
758 by C4::Auth::getuserflags. If not, getuserflags is called.
760 If user is authorised to manage basket, returns 1.
765 sub CanUserManageBasket {
766 my ($borrower, $basket, $userflags) = @_;
768 if (!ref $borrower) {
769 $borrower = C4::Members::GetMember(borrowernumber => $borrower);
772 $basket = GetBasket($basket);
775 return 0 unless ($basket and $borrower);
777 my $borrowernumber = $borrower->{borrowernumber};
778 my $basketno = $basket->{basketno};
780 my $AcqViewBaskets = C4::Context->preference('AcqViewBaskets');
782 if (!defined $userflags) {
783 my $dbh = C4::Context->dbh;
784 my $sth = $dbh->prepare("SELECT flags FROM borrowers WHERE borrowernumber = ?");
785 $sth->execute($borrowernumber);
786 my ($flags) = $sth->fetchrow_array;
789 $userflags = C4::Auth::getuserflags($flags, $borrower->{userid}, $dbh);
792 unless ($userflags->{superlibrarian}
793 || (ref $userflags->{acquisition} && $userflags->{acquisition}->{order_manage_all})
794 || (!ref $userflags->{acquisition} && $userflags->{acquisition}))
796 if (not exists $userflags->{acquisition}) {
800 if ( (ref $userflags->{acquisition} && !$userflags->{acquisition}->{order_manage})
801 || (!ref $userflags->{acquisition} && !$userflags->{acquisition}) ) {
805 if ($AcqViewBaskets eq 'user'
806 && $basket->{authorisedby} != $borrowernumber
807 && grep($borrowernumber, GetBasketUsers($basketno)) == 0) {
811 if ($AcqViewBaskets eq 'branch' && defined $basket->{branch}
812 && $basket->{branch} ne $borrower->{branchcode}) {
820 #------------------------------------------------------------#
822 =head3 GetBasketsByBasketgroup
824 $baskets = &GetBasketsByBasketgroup($basketgroupid);
826 Returns a reference to all baskets that belong to basketgroup $basketgroupid.
830 sub GetBasketsByBasketgroup {
831 my $basketgroupid = shift;
833 SELECT *, aqbasket.booksellerid as booksellerid
835 LEFT JOIN aqcontract USING(contractnumber) WHERE basketgroupid=?
837 my $dbh = C4::Context->dbh;
838 my $sth = $dbh->prepare($query);
839 $sth->execute($basketgroupid);
840 return $sth->fetchall_arrayref({});
843 #------------------------------------------------------------#
845 =head3 NewBasketgroup
847 $basketgroupid = NewBasketgroup(\%hashref);
849 Adds a basketgroup to the aqbasketgroups table, and add the initial baskets to it.
851 $hashref->{'booksellerid'} is the 'id' field of the bookseller in the aqbooksellers table,
853 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
855 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
857 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
859 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
861 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
863 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
865 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
870 my $basketgroupinfo = shift;
871 die "booksellerid is required to create a basketgroup" unless $basketgroupinfo->{'booksellerid'};
872 my $query = "INSERT INTO aqbasketgroups (";
874 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
875 if ( defined $basketgroupinfo->{$field} ) {
876 $query .= "$field, ";
877 push(@params, $basketgroupinfo->{$field});
880 $query .= "booksellerid) VALUES (";
885 push(@params, $basketgroupinfo->{'booksellerid'});
886 my $dbh = C4::Context->dbh;
887 my $sth = $dbh->prepare($query);
888 $sth->execute(@params);
889 my $basketgroupid = $dbh->{'mysql_insertid'};
890 if( $basketgroupinfo->{'basketlist'} ) {
891 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
892 my $query2 = "UPDATE aqbasket SET basketgroupid=? WHERE basketno=?";
893 my $sth2 = $dbh->prepare($query2);
894 $sth2->execute($basketgroupid, $basketno);
897 return $basketgroupid;
900 #------------------------------------------------------------#
902 =head3 ModBasketgroup
904 ModBasketgroup(\%hashref);
906 Modifies a basketgroup in the aqbasketgroups table, and add the baskets to it.
908 $hashref->{'id'} is the 'id' field of the basketgroup in the aqbasketgroup table, this parameter is mandatory,
910 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
912 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
914 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
916 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
918 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
920 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
922 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
927 my $basketgroupinfo = shift;
928 die "basketgroup id is required to edit a basketgroup" unless $basketgroupinfo->{'id'};
929 my $dbh = C4::Context->dbh;
930 my $query = "UPDATE aqbasketgroups SET ";
932 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
933 if ( defined $basketgroupinfo->{$field} ) {
934 $query .= "$field=?, ";
935 push(@params, $basketgroupinfo->{$field});
940 $query .= " WHERE id=?";
941 push(@params, $basketgroupinfo->{'id'});
942 my $sth = $dbh->prepare($query);
943 $sth->execute(@params);
945 $sth = $dbh->prepare('UPDATE aqbasket SET basketgroupid = NULL WHERE basketgroupid = ?');
946 $sth->execute($basketgroupinfo->{'id'});
948 if($basketgroupinfo->{'basketlist'} && @{$basketgroupinfo->{'basketlist'}}){
949 $sth = $dbh->prepare("UPDATE aqbasket SET basketgroupid=? WHERE basketno=?");
950 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
951 $sth->execute($basketgroupinfo->{'id'}, $basketno);
957 #------------------------------------------------------------#
959 =head3 DelBasketgroup
961 DelBasketgroup($basketgroupid);
963 Deletes a basketgroup in the aqbasketgroups table, and removes the reference to it from the baskets,
967 =item C<$basketgroupid> is the 'id' field of the basket in the aqbasketgroup table
974 my $basketgroupid = shift;
975 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
976 my $query = "DELETE FROM aqbasketgroups WHERE id=?";
977 my $dbh = C4::Context->dbh;
978 my $sth = $dbh->prepare($query);
979 $sth->execute($basketgroupid);
983 #------------------------------------------------------------#
986 =head2 FUNCTIONS ABOUT ORDERS
988 =head3 GetBasketgroup
990 $basketgroup = &GetBasketgroup($basketgroupid);
992 Returns a reference to the hash containing all information about the basketgroup.
997 my $basketgroupid = shift;
998 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
999 my $dbh = C4::Context->dbh;
1000 my $result_set = $dbh->selectall_arrayref(
1001 'SELECT * FROM aqbasketgroups WHERE id=?',
1005 return $result_set->[0]; # id is unique
1008 #------------------------------------------------------------#
1010 =head3 GetBasketgroups
1012 $basketgroups = &GetBasketgroups($booksellerid);
1014 Returns a reference to the array of all the basketgroups of bookseller $booksellerid.
1018 sub GetBasketgroups {
1019 my $booksellerid = shift;
1020 die 'bookseller id is required to edit a basketgroup' unless $booksellerid;
1021 my $query = 'SELECT * FROM aqbasketgroups WHERE booksellerid=? ORDER BY id DESC';
1022 my $dbh = C4::Context->dbh;
1023 my $sth = $dbh->prepare($query);
1024 $sth->execute($booksellerid);
1025 return $sth->fetchall_arrayref({});
1028 #------------------------------------------------------------#
1030 =head2 FUNCTIONS ABOUT ORDERS
1034 @orders = &GetOrders($basketnumber, $orderby);
1036 Looks up the pending (non-cancelled) orders with the given basket
1037 number. If C<$booksellerID> is non-empty, only orders from that seller
1041 C<&basket> returns a two-element array. C<@orders> is an array of
1042 references-to-hash, whose keys are the fields from the aqorders,
1043 biblio, and biblioitems tables in the Koha database.
1048 my ( $basketno, $orderby ) = @_;
1049 return () unless $basketno;
1050 my $dbh = C4::Context->dbh;
1052 SELECT biblio.*,biblioitems.*,
1056 aqorders_transfers.ordernumber_from AS transferred_from,
1057 aqorders_transfers.timestamp AS transferred_from_timestamp
1059 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1060 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1061 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1062 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1064 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
1067 $orderby = "biblioitems.publishercode,biblio.title" unless $orderby;
1068 $query .= " ORDER BY $orderby";
1070 $dbh->selectall_arrayref( $query, { Slice => {} }, $basketno );
1071 return @{$result_set};
1075 #------------------------------------------------------------#
1076 =head3 GetOrdersByBiblionumber
1078 @orders = &GetOrdersByBiblionumber($biblionumber);
1080 Looks up the orders with linked to a specific $biblionumber, including
1081 cancelled orders and received orders.
1084 C<@orders> is an array of references-to-hash, whose keys are the
1085 fields from the aqorders, biblio, and biblioitems tables in the Koha database.
1089 sub GetOrdersByBiblionumber {
1090 my $biblionumber = shift;
1091 return unless $biblionumber;
1092 my $dbh = C4::Context->dbh;
1094 SELECT biblio.*,biblioitems.*,
1098 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1099 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1100 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1101 WHERE aqorders.biblionumber=?
1104 $dbh->selectall_arrayref( $query, { Slice => {} }, $biblionumber );
1105 return @{$result_set};
1109 #------------------------------------------------------------#
1113 $order = &GetOrder($ordernumber);
1115 Looks up an order by order number.
1117 Returns a reference-to-hash describing the order. The keys of
1118 C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database.
1123 my ($ordernumber) = @_;
1124 return unless $ordernumber;
1126 my $dbh = C4::Context->dbh;
1127 my $query = qq{SELECT
1131 aqbasket.basketname,
1132 borrowers.branchcode,
1133 biblioitems.publicationyear,
1134 biblio.copyrightdate,
1135 biblioitems.editionstatement,
1139 biblioitems.publishercode,
1140 aqorders.rrp AS unitpricesupplier,
1141 aqorders.ecost AS unitpricelib,
1142 aqorders.claims_count AS claims_count,
1143 aqorders.claimed_date AS claimed_date,
1144 aqbudgets.budget_name AS budget,
1145 aqbooksellers.name AS supplier,
1146 aqbooksellers.id AS supplierid,
1147 biblioitems.publishercode AS publisher,
1148 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
1149 DATE(aqbasket.closedate) AS orderdate,
1150 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity_to_receive,
1151 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1152 DATEDIFF(CURDATE( ),closedate) AS latesince
1153 FROM aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1154 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1155 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
1156 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
1157 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1158 WHERE aqorders.basketno = aqbasket.basketno
1161 $dbh->selectall_arrayref( $query, { Slice => {} }, $ordernumber );
1163 # result_set assumed to contain 1 match
1164 return $result_set->[0];
1167 =head3 GetLastOrderNotReceivedFromSubscriptionid
1169 $order = &GetLastOrderNotReceivedFromSubscriptionid($subscriptionid);
1171 Returns a reference-to-hash describing the last order not received for a subscription.
1175 sub GetLastOrderNotReceivedFromSubscriptionid {
1176 my ( $subscriptionid ) = @_;
1177 my $dbh = C4::Context->dbh;
1179 SELECT * FROM aqorders
1180 LEFT JOIN subscription
1181 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1182 WHERE aqorders.subscriptionid = ?
1183 AND aqorders.datereceived IS NULL
1187 $dbh->selectall_arrayref( $query, { Slice => {} }, $subscriptionid );
1189 # result_set assumed to contain 1 match
1190 return $result_set->[0];
1193 =head3 GetLastOrderReceivedFromSubscriptionid
1195 $order = &GetLastOrderReceivedFromSubscriptionid($subscriptionid);
1197 Returns a reference-to-hash describing the last order received for a subscription.
1201 sub GetLastOrderReceivedFromSubscriptionid {
1202 my ( $subscriptionid ) = @_;
1203 my $dbh = C4::Context->dbh;
1205 SELECT * FROM aqorders
1206 LEFT JOIN subscription
1207 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1208 WHERE aqorders.subscriptionid = ?
1209 AND aqorders.datereceived =
1211 SELECT MAX( aqorders.datereceived )
1213 LEFT JOIN subscription
1214 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1215 WHERE aqorders.subscriptionid = ?
1216 AND aqorders.datereceived IS NOT NULL
1218 ORDER BY ordernumber DESC
1222 $dbh->selectall_arrayref( $query, { Slice => {} }, $subscriptionid, $subscriptionid );
1224 # result_set assumed to contain 1 match
1225 return $result_set->[0];
1230 #------------------------------------------------------------#
1234 &NewOrder(\%hashref);
1236 Adds a new order to the database. Any argument that isn't described
1237 below is the new value of the field with the same name in the aqorders
1238 table of the Koha database.
1242 =item $hashref->{'basketno'} is the basketno foreign key in aqorders, it is mandatory
1244 =item $hashref->{'ordernumber'} is a "minimum order number."
1246 =item $hashref->{'budgetdate'} is effectively ignored.
1247 If it's undef (anything false) or the string 'now', the current day is used.
1248 Else, the upcoming July 1st is used.
1250 =item $hashref->{'subscription'} may be either "yes", or anything else for "no".
1252 =item $hashref->{'uncertainprice'} may be 0 for "the price is known" or 1 for "the price is uncertain"
1254 =item defaults entrydate to Now
1256 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".
1263 my $orderinfo = shift;
1265 my $dbh = C4::Context->dbh;
1269 # if these parameters are missing, we can't continue
1270 for my $key (qw/basketno quantity biblionumber budget_id/) {
1271 croak "Mandatory parameter $key missing" unless $orderinfo->{$key};
1274 if ( defined $orderinfo->{subscription} && $orderinfo->{'subscription'} eq 'yes' ) {
1275 $orderinfo->{'subscription'} = 1;
1277 $orderinfo->{'subscription'} = 0;
1279 $orderinfo->{'entrydate'} ||= C4::Dates->new()->output("iso");
1280 if (!$orderinfo->{quantityreceived}) {
1281 $orderinfo->{quantityreceived} = 0;
1284 my $ordernumber=InsertInTable("aqorders",$orderinfo);
1285 if (not $orderinfo->{parent_ordernumber}) {
1286 my $sth = $dbh->prepare("
1288 SET parent_ordernumber = ordernumber
1289 WHERE ordernumber = ?
1291 $sth->execute($ordernumber);
1293 return ( $orderinfo->{'basketno'}, $ordernumber );
1298 #------------------------------------------------------------#
1307 my ($itemnumber, $ordernumber) = @_;
1308 my $dbh = C4::Context->dbh;
1310 INSERT INTO aqorders_items
1311 (itemnumber, ordernumber)
1314 my $sth = $dbh->prepare($query);
1315 $sth->execute( $itemnumber, $ordernumber);
1318 #------------------------------------------------------------#
1322 &ModOrder(\%hashref);
1324 Modifies an existing order. Updates the order with order number
1325 $hashref->{'ordernumber'} and biblionumber $hashref->{'biblionumber'}. All
1326 other keys of the hash update the fields with the same name in the aqorders
1327 table of the Koha database.
1332 my $orderinfo = shift;
1334 die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq '' ;
1335 die "Biblionumber is required" if $orderinfo->{'biblionumber'} eq '';
1337 my $dbh = C4::Context->dbh;
1340 # update uncertainprice to an integer, just in case (under FF, checked boxes have the value "ON" by default)
1341 $orderinfo->{uncertainprice}=1 if $orderinfo->{uncertainprice};
1343 # delete($orderinfo->{'branchcode'});
1344 # the hash contains a lot of entries not in aqorders, so get the columns ...
1345 my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;");
1347 my $colnames = $sth->{NAME};
1348 #FIXME Be careful. If aqorders would have columns with diacritics,
1349 #you should need to decode what you get back from NAME.
1350 #See report 10110 and guided_reports.pl
1351 my $query = "UPDATE aqorders SET ";
1353 foreach my $orderinfokey (grep(!/ordernumber/, keys %$orderinfo)){
1354 # ... and skip hash entries that are not in the aqorders table
1355 # FIXME : probably not the best way to do it (would be better to have a correct hash)
1356 next unless grep(/^$orderinfokey$/, @$colnames);
1357 $query .= "$orderinfokey=?, ";
1358 push(@params, $orderinfo->{$orderinfokey});
1361 $query .= "timestamp=NOW() WHERE ordernumber=?";
1362 push(@params, $orderinfo->{'ordernumber'} );
1363 $sth = $dbh->prepare($query);
1364 $sth->execute(@params);
1368 #------------------------------------------------------------#
1372 ModItemOrder($itemnumber, $ordernumber);
1374 Modifies the ordernumber of an item in aqorders_items.
1379 my ($itemnumber, $ordernumber) = @_;
1381 return unless ($itemnumber and $ordernumber);
1383 my $dbh = C4::Context->dbh;
1385 UPDATE aqorders_items
1387 WHERE itemnumber = ?
1389 my $sth = $dbh->prepare($query);
1390 return $sth->execute($ordernumber, $itemnumber);
1393 #------------------------------------------------------------#
1395 =head3 GetCancelledOrders
1397 my @orders = GetCancelledOrders($basketno, $orderby);
1399 Returns cancelled orders for a basket
1403 sub GetCancelledOrders {
1404 my ( $basketno, $orderby ) = @_;
1406 return () unless $basketno;
1408 my $dbh = C4::Context->dbh;
1415 aqorders_transfers.ordernumber_to AS transferred_to,
1416 aqorders_transfers.timestamp AS transferred_to_timestamp
1418 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1419 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1420 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1421 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber
1423 AND (datecancellationprinted IS NOT NULL
1424 AND datecancellationprinted <> '0000-00-00')
1427 $orderby = "aqorders.datecancellationprinted desc, aqorders.timestamp desc"
1429 $query .= " ORDER BY $orderby";
1430 my $sth = $dbh->prepare($query);
1431 $sth->execute($basketno);
1432 my $results = $sth->fetchall_arrayref( {} );
1438 #------------------------------------------------------------#
1440 =head3 ModReceiveOrder
1443 biblionumber => $biblionumber,
1444 ordernumber => $ordernumber,
1445 quantityreceived => $quantityreceived,
1449 invoiceid => $invoiceid,
1451 budget_id => $budget_id,
1452 datereceived => $datereceived,
1453 received_itemnumbers => \@received_itemnumbers,
1454 order_internalnote => $order_internalnote,
1455 order_vendornote => $order_vendornote,
1458 Updates an order, to reflect the fact that it was received, at least
1459 in part. All arguments not mentioned below update the fields with the
1460 same name in the aqorders table of the Koha database.
1462 If a partial order is received, splits the order into two.
1464 Updates the order with bibilionumber C<$biblionumber> and ordernumber
1470 sub ModReceiveOrder {
1471 my ( $params ) = @_;
1472 my $biblionumber = $params->{biblionumber};
1473 my $ordernumber = $params->{ordernumber};
1474 my $quantrec = $params->{quantityreceived};
1475 my $user = $params->{user};
1476 my $cost = $params->{cost};
1477 my $ecost = $params->{ecost};
1478 my $invoiceid = $params->{invoiceid};
1479 my $rrp = $params->{rrp};
1480 my $budget_id = $params->{budget_id};
1481 my $datereceived = $params->{datereceived};
1482 my $received_items = $params->{received_items};
1483 my $order_internalnote = $params->{order_internalnote};
1484 my $order_vendornote = $params->{order_vendornote};
1486 my $dbh = C4::Context->dbh;
1487 $datereceived = C4::Dates->output('iso') unless $datereceived;
1488 my $suggestionid = GetSuggestionFromBiblionumber( $biblionumber );
1489 if ($suggestionid) {
1490 ModSuggestion( {suggestionid=>$suggestionid,
1491 STATUS=>'AVAILABLE',
1492 biblionumber=> $biblionumber}
1496 my $result_set = $dbh->selectall_arrayref(
1497 q{SELECT * FROM aqorders WHERE biblionumber=? AND aqorders.ordernumber=?},
1498 { Slice => {} }, $biblionumber, $ordernumber
1501 # we assume we have a unique order
1502 my $order = $result_set->[0];
1504 my $new_ordernumber = $ordernumber;
1505 if ( $order->{quantity} > $quantrec ) {
1506 # Split order line in two parts: the first is the original order line
1507 # without received items (the quantity is decreased),
1508 # the second part is a new order line with quantity=quantityrec
1509 # (entirely received)
1513 orderstatus = 'partial'|;
1514 $query .= q|, order_internalnote = ?| if defined $order_internalnote;
1515 $query .= q|, order_vendornote = ?| if defined $order_vendornote;
1516 $query .= q| WHERE ordernumber = ?|;
1517 my $sth = $dbh->prepare($query);
1520 $order->{quantity} - $quantrec,
1521 ( defined $order_internalnote ? $order_internalnote : () ),
1522 ( defined $order_vendornote ? $order_vendornote : () ),
1526 delete $order->{'ordernumber'};
1527 $order->{'budget_id'} = ( $budget_id || $order->{'budget_id'} );
1528 $order->{'quantity'} = $quantrec;
1529 $order->{'quantityreceived'} = $quantrec;
1530 $order->{'datereceived'} = $datereceived;
1531 $order->{'invoiceid'} = $invoiceid;
1532 $order->{'unitprice'} = $cost;
1533 $order->{'rrp'} = $rrp;
1534 $order->{ecost} = $ecost;
1535 $order->{'orderstatus'} = 'complete';
1537 ( $basketno, $new_ordernumber ) = NewOrder($order);
1539 if ($received_items) {
1540 foreach my $itemnumber (@$received_items) {
1541 ModItemOrder($itemnumber, $new_ordernumber);
1547 set quantityreceived=?,datereceived=?,invoiceid=?,
1548 unitprice=?,rrp=?,ecost=?,budget_id=?,orderstatus='complete'|;
1549 $query .= q|, order_internalnote = ?| if defined $order_internalnote;
1550 $query .= q|, order_vendornote = ?| if defined $order_vendornote;
1551 $query .= q| where biblionumber=? and ordernumber=?|;
1552 my $sth = $dbh->prepare( $query );
1561 ( defined $order_internalnote ? $order_internalnote : () ),
1562 ( defined $order_vendornote ? $order_vendornote : () ),
1567 return ($datereceived, $new_ordernumber);
1570 =head3 CancelReceipt
1572 my $parent_ordernumber = CancelReceipt($ordernumber);
1574 Cancel an order line receipt and update the parent order line, as if no
1576 If items are created at receipt (AcqCreateItem = receiving) then delete
1582 my $ordernumber = shift;
1584 return unless $ordernumber;
1586 my $dbh = C4::Context->dbh;
1588 SELECT datereceived, parent_ordernumber, quantity
1590 WHERE ordernumber = ?
1592 my $sth = $dbh->prepare($query);
1593 $sth->execute($ordernumber);
1594 my $order = $sth->fetchrow_hashref;
1596 warn "CancelReceipt: order $ordernumber does not exist";
1599 unless($order->{'datereceived'}) {
1600 warn "CancelReceipt: order $ordernumber is not received";
1604 my $parent_ordernumber = $order->{'parent_ordernumber'};
1606 if($parent_ordernumber == $ordernumber || not $parent_ordernumber) {
1607 # The order line has no parent, just mark it as not received
1610 SET quantityreceived = ?,
1613 orderstatus = 'ordered'
1614 WHERE ordernumber = ?
1616 $sth = $dbh->prepare($query);
1617 $sth->execute(0, undef, undef, $ordernumber);
1619 # The order line has a parent, increase parent quantity and delete
1622 SELECT quantity, datereceived
1624 WHERE ordernumber = ?
1626 $sth = $dbh->prepare($query);
1627 $sth->execute($parent_ordernumber);
1628 my $parent_order = $sth->fetchrow_hashref;
1629 unless($parent_order) {
1630 warn "Parent order $parent_ordernumber does not exist.";
1633 if($parent_order->{'datereceived'}) {
1634 warn "CancelReceipt: parent order is received.".
1635 " Can't cancel receipt.";
1641 orderstatus = 'ordered'
1642 WHERE ordernumber = ?
1644 $sth = $dbh->prepare($query);
1645 my $rv = $sth->execute(
1646 $order->{'quantity'} + $parent_order->{'quantity'},
1650 warn "Cannot update parent order line, so do not cancel".
1654 if(C4::Context->preference('AcqCreateItem') eq 'receiving') {
1655 # Remove items that were created at receipt
1657 DELETE FROM items, aqorders_items
1658 USING items, aqorders_items
1659 WHERE items.itemnumber = ? AND aqorders_items.itemnumber = ?
1661 $sth = $dbh->prepare($query);
1662 my @itemnumbers = GetItemnumbersFromOrder($ordernumber);
1663 foreach my $itemnumber (@itemnumbers) {
1664 $sth->execute($itemnumber, $itemnumber);
1668 my @itemnumbers = GetItemnumbersFromOrder($ordernumber);
1669 foreach my $itemnumber (@itemnumbers) {
1670 ModItemOrder($itemnumber, $parent_ordernumber);
1675 DELETE FROM aqorders
1676 WHERE ordernumber = ?
1678 $sth = $dbh->prepare($query);
1679 $sth->execute($ordernumber);
1683 return $parent_ordernumber;
1686 #------------------------------------------------------------#
1690 @results = &SearchOrders({
1691 ordernumber => $ordernumber,
1693 biblionumber => $biblionumber,
1695 booksellerid => $booksellerid,
1696 basketno => $basketno,
1702 Searches for orders.
1704 C<$owner> Finds order for the logged in user.
1705 C<$pending> Finds pending orders. Ignores completed and cancelled orders.
1706 C<$ordered> Finds orders to receive only (status 'ordered' or 'partial').
1709 C<@results> is an array of references-to-hash with the keys are fields
1710 from aqorders, biblio, biblioitems and aqbasket tables.
1715 my ( $params ) = @_;
1716 my $ordernumber = $params->{ordernumber};
1717 my $search = $params->{search};
1718 my $ean = $params->{ean};
1719 my $booksellerid = $params->{booksellerid};
1720 my $basketno = $params->{basketno};
1721 my $basketname = $params->{basketname};
1722 my $basketgroupname = $params->{basketgroupname};
1723 my $owner = $params->{owner};
1724 my $pending = $params->{pending};
1725 my $ordered = $params->{ordered};
1727 my $dbh = C4::Context->dbh;
1730 SELECT aqbasket.basketno,
1732 borrowers.firstname,
1735 biblioitems.biblioitemnumber,
1736 aqbasket.authorisedby,
1737 aqbasket.booksellerid,
1739 aqbasket.creationdate,
1740 aqbasket.basketname,
1741 aqbasketgroups.id as basketgroupid,
1742 aqbasketgroups.name as basketgroupname,
1745 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
1746 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
1747 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1748 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1749 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
1752 # If we search on ordernumber, we retrieve the transfered order if a transfer has been done.
1754 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1758 WHERE (datecancellationprinted is NULL)
1761 if ( $pending or $ordered ) {
1762 $query .= q{ AND (quantity > quantityreceived OR quantityreceived is NULL)};
1765 $query .= q{ AND aqorders.orderstatus IN ( "ordered", "partial" )};
1768 my $userenv = C4::Context->userenv;
1769 if ( C4::Context->preference("IndependentBranches") ) {
1770 unless ( C4::Context->IsSuperLibrarian() ) {
1773 borrowers.branchcode = ?
1774 OR borrowers.branchcode = ''
1777 push @args, $userenv->{branch};
1781 if ( $ordernumber ) {
1782 $query .= ' AND ( aqorders.ordernumber = ? OR aqorders_transfers.ordernumber_from = ? ) ';
1783 push @args, ( $ordernumber, $ordernumber );
1786 $query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)';
1787 push @args, ("%$search%","%$search%","%$search%");
1790 $query .= ' AND biblioitems.ean = ?';
1793 if ( $booksellerid ) {
1794 $query .= 'AND aqbasket.booksellerid = ?';
1795 push @args, $booksellerid;
1798 $query .= 'AND aqbasket.basketno = ?';
1799 push @args, $basketno;
1802 $query .= 'AND aqbasket.basketname LIKE ?';
1803 push @args, "%$basketname%";
1805 if( $basketgroupname ) {
1806 $query .= ' AND aqbasketgroups.name LIKE ?';
1807 push @args, "%$basketgroupname%";
1811 $query .= ' AND aqbasket.authorisedby=? ';
1812 push @args, $userenv->{'number'};
1815 $query .= ' ORDER BY aqbasket.basketno';
1817 my $sth = $dbh->prepare($query);
1818 $sth->execute(@args);
1819 return $sth->fetchall_arrayref({});
1822 #------------------------------------------------------------#
1826 &DelOrder($biblionumber, $ordernumber);
1828 Cancel the order with the given order and biblio numbers. It does not
1829 delete any entries in the aqorders table, it merely marks them as
1835 my ( $bibnum, $ordernumber ) = @_;
1836 my $dbh = C4::Context->dbh;
1839 SET datecancellationprinted=now(), orderstatus='cancelled'
1840 WHERE biblionumber=? AND ordernumber=?
1842 my $sth = $dbh->prepare($query);
1843 $sth->execute( $bibnum, $ordernumber );
1844 my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1845 foreach my $itemnumber (@itemnumbers){
1846 C4::Items::DelItem( $dbh, $bibnum, $itemnumber );
1851 =head3 TransferOrder
1853 my $newordernumber = TransferOrder($ordernumber, $basketno);
1855 Transfer an order line to a basket.
1856 Mark $ordernumber as cancelled with an internal note 'Cancelled and transfered
1857 to BOOKSELLER on DATE' and create new order with internal note
1858 'Transfered from BOOKSELLER on DATE'.
1859 Move all attached items to the new order.
1860 Received orders cannot be transfered.
1861 Return the ordernumber of created order.
1866 my ($ordernumber, $basketno) = @_;
1868 return unless ($ordernumber and $basketno);
1870 my $order = GetOrder( $ordernumber );
1871 return if $order->{datereceived};
1872 my $basket = GetBasket($basketno);
1873 return unless $basket;
1875 my $dbh = C4::Context->dbh;
1876 my ($query, $sth, $rv);
1880 SET datecancellationprinted = CAST(NOW() AS date)
1881 WHERE ordernumber = ?
1883 $sth = $dbh->prepare($query);
1884 $rv = $sth->execute($ordernumber);
1886 delete $order->{'ordernumber'};
1887 delete $order->{parent_ordernumber};
1888 $order->{'basketno'} = $basketno;
1890 (undef, $newordernumber) = NewOrder($order);
1893 UPDATE aqorders_items
1895 WHERE ordernumber = ?
1897 $sth = $dbh->prepare($query);
1898 $sth->execute($newordernumber, $ordernumber);
1901 INSERT INTO aqorders_transfers (ordernumber_from, ordernumber_to)
1904 $sth = $dbh->prepare($query);
1905 $sth->execute($ordernumber, $newordernumber);
1907 return $newordernumber;
1910 =head2 FUNCTIONS ABOUT PARCELS
1914 #------------------------------------------------------------#
1918 @results = &GetParcel($booksellerid, $code, $date);
1920 Looks up all of the received items from the supplier with the given
1921 bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders.
1923 C<@results> is an array of references-to-hash. The keys of each element are fields from
1924 the aqorders, biblio, and biblioitems tables of the Koha database.
1926 C<@results> is sorted alphabetically by book title.
1931 #gets all orders from a certain supplier, orders them alphabetically
1932 my ( $supplierid, $code, $datereceived ) = @_;
1933 my $dbh = C4::Context->dbh;
1936 if $code; # add % if we search on a given code (otherwise, let him empty)
1938 SELECT authorisedby,
1943 aqorders.biblionumber,
1944 aqorders.ordernumber,
1945 aqorders.parent_ordernumber,
1947 aqorders.quantityreceived,
1955 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
1956 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1957 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1958 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
1960 aqbasket.booksellerid = ?
1961 AND aqinvoices.invoicenumber LIKE ?
1962 AND aqorders.datereceived = ? ";
1964 my @query_params = ( $supplierid, $code, $datereceived );
1965 if ( C4::Context->preference("IndependentBranches") ) {
1966 unless ( C4::Context->IsSuperLibrarian() ) {
1967 $strsth .= " and (borrowers.branchcode = ?
1968 or borrowers.branchcode = '')";
1969 push @query_params, C4::Context->userenv->{branch};
1972 $strsth .= " ORDER BY aqbasket.basketno";
1973 my $result_set = $dbh->selectall_arrayref(
1978 return @{$result_set};
1981 #------------------------------------------------------------#
1985 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
1987 get a lists of parcels.
1994 is the bookseller this function has to get parcels.
1997 To know on what criteria the results list has to be ordered.
2000 is the booksellerinvoicenumber.
2002 =item $datefrom & $dateto
2003 to know on what date this function has to filter its search.
2008 a pointer on a hash list containing parcel informations as such :
2014 =item Last operation
2016 =item Number of biblio
2018 =item Number of items
2025 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
2026 my $dbh = C4::Context->dbh;
2027 my @query_params = ();
2029 SELECT aqinvoices.invoicenumber,
2030 datereceived,purchaseordernumber,
2031 count(DISTINCT biblionumber) AS biblio,
2032 sum(quantity) AS itemsexpected,
2033 sum(quantityreceived) AS itemsreceived
2034 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
2035 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2036 WHERE aqbasket.booksellerid = ? and datereceived IS NOT NULL
2038 push @query_params, $bookseller;
2040 if ( defined $code ) {
2041 $strsth .= ' and aqinvoices.invoicenumber like ? ';
2042 # add a % to the end of the code to allow stemming.
2043 push @query_params, "$code%";
2046 if ( defined $datefrom ) {
2047 $strsth .= ' and datereceived >= ? ';
2048 push @query_params, $datefrom;
2051 if ( defined $dateto ) {
2052 $strsth .= 'and datereceived <= ? ';
2053 push @query_params, $dateto;
2056 $strsth .= "group by aqinvoices.invoicenumber,datereceived ";
2058 # can't use a placeholder to place this column name.
2059 # but, we could probably be checking to make sure it is a column that will be fetched.
2060 $strsth .= "order by $order " if ($order);
2062 my $sth = $dbh->prepare($strsth);
2064 $sth->execute( @query_params );
2065 my $results = $sth->fetchall_arrayref({});
2069 #------------------------------------------------------------#
2071 =head3 GetLateOrders
2073 @results = &GetLateOrders;
2075 Searches for bookseller with late orders.
2078 the table of supplier with late issues. This table is full of hashref.
2084 my $supplierid = shift;
2086 my $estimateddeliverydatefrom = shift;
2087 my $estimateddeliverydateto = shift;
2089 my $dbh = C4::Context->dbh;
2091 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
2092 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
2094 my @query_params = ();
2096 SELECT aqbasket.basketno,
2097 aqorders.ordernumber,
2098 DATE(aqbasket.closedate) AS orderdate,
2099 aqorders.rrp AS unitpricesupplier,
2100 aqorders.ecost AS unitpricelib,
2101 aqorders.claims_count AS claims_count,
2102 aqorders.claimed_date AS claimed_date,
2103 aqbudgets.budget_name AS budget,
2104 borrowers.branchcode AS branch,
2105 aqbooksellers.name AS supplier,
2106 aqbooksellers.id AS supplierid,
2107 biblio.author, biblio.title,
2108 biblioitems.publishercode AS publisher,
2109 biblioitems.publicationyear,
2110 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
2114 aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
2115 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
2116 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
2117 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
2118 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
2119 WHERE aqorders.basketno = aqbasket.basketno
2120 AND ( datereceived = ''
2121 OR datereceived IS NULL
2122 OR aqorders.quantityreceived < aqorders.quantity
2124 AND aqbasket.closedate IS NOT NULL
2125 AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
2128 if ($dbdriver eq "mysql") {
2130 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity,
2131 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
2132 DATEDIFF(CAST(now() AS date),closedate) AS latesince
2134 if ( defined $delay ) {
2135 $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ;
2136 push @query_params, $delay;
2139 HAVING quantity <> 0
2140 AND unitpricesupplier <> 0
2141 AND unitpricelib <> 0
2144 # FIXME: account for IFNULL as above
2146 aqorders.quantity AS quantity,
2147 aqorders.quantity * aqorders.rrp AS subtotal,
2148 (CAST(now() AS date) - closedate) AS latesince
2150 if ( defined $delay ) {
2151 $from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) ";
2152 push @query_params, $delay;
2155 if (defined $supplierid) {
2156 $from .= ' AND aqbasket.booksellerid = ? ';
2157 push @query_params, $supplierid;
2159 if (defined $branch) {
2160 $from .= ' AND borrowers.branchcode LIKE ? ';
2161 push @query_params, $branch;
2164 if ( defined $estimateddeliverydatefrom or defined $estimateddeliverydateto ) {
2165 $from .= ' AND aqbooksellers.deliverytime IS NOT NULL ';
2167 if ( defined $estimateddeliverydatefrom ) {
2168 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
2169 push @query_params, $estimateddeliverydatefrom;
2171 if ( defined $estimateddeliverydateto ) {
2172 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
2173 push @query_params, $estimateddeliverydateto;
2175 if ( defined $estimateddeliverydatefrom and not defined $estimateddeliverydateto ) {
2176 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
2178 if (C4::Context->preference("IndependentBranches")
2179 && !C4::Context->IsSuperLibrarian() ) {
2180 $from .= ' AND borrowers.branchcode LIKE ? ';
2181 push @query_params, C4::Context->userenv->{branch};
2183 $from .= " AND orderstatus <> 'cancelled' ";
2184 my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier";
2185 $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params);
2186 my $sth = $dbh->prepare($query);
2187 $sth->execute(@query_params);
2189 while (my $data = $sth->fetchrow_hashref) {
2190 $data->{orderdate} = format_date($data->{orderdate});
2191 $data->{claimed_date} = format_date($data->{claimed_date});
2192 push @results, $data;
2197 #------------------------------------------------------------#
2201 (\@order_loop, $total_qty, $total_price, $total_qtyreceived) = GetHistory( %params );
2203 Retreives some acquisition history information
2213 basket - search both basket name and number
2214 booksellerinvoicenumber
2217 orderstatus (note that orderstatus '' will retrieve orders
2218 of any status except cancelled)
2220 get_canceled_order (if set to a true value, cancelled orders will
2224 $order_loop is a list of hashrefs that each look like this:
2226 'author' => 'Twain, Mark',
2228 'biblionumber' => '215',
2230 'creationdate' => 'MM/DD/YYYY',
2231 'datereceived' => undef,
2234 'invoicenumber' => undef,
2236 'ordernumber' => '1',
2238 'quantityreceived' => undef,
2239 'title' => 'The Adventures of Huckleberry Finn'
2241 $total_qty is the sum of all of the quantities in $order_loop
2242 $total_price is the cost of each in $order_loop times the quantity
2243 $total_qtyreceived is the sum of all of the quantityreceived entries in $order_loop
2248 # don't run the query if there are no parameters (list would be too long for sure !)
2249 croak "No search params" unless @_;
2251 my $title = $params{title};
2252 my $author = $params{author};
2253 my $isbn = $params{isbn};
2254 my $ean = $params{ean};
2255 my $name = $params{name};
2256 my $from_placed_on = $params{from_placed_on};
2257 my $to_placed_on = $params{to_placed_on};
2258 my $basket = $params{basket};
2259 my $booksellerinvoicenumber = $params{booksellerinvoicenumber};
2260 my $basketgroupname = $params{basketgroupname};
2261 my $budget = $params{budget};
2262 my $orderstatus = $params{orderstatus};
2263 my $biblionumber = $params{biblionumber};
2264 my $get_canceled_order = $params{get_canceled_order} || 0;
2265 my $ordernumber = $params{ordernumber};
2266 my $search_children_too = $params{search_children_too} || 0;
2270 my $total_qtyreceived = 0;
2271 my $total_price = 0;
2273 my $dbh = C4::Context->dbh;
2276 COALESCE(biblio.title, deletedbiblio.title) AS title,
2277 COALESCE(biblio.author, deletedbiblio.author) AS author,
2278 COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn,
2279 COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean,
2281 aqbasket.basketname,
2282 aqbasket.basketgroupid,
2283 aqbasketgroups.name as groupname,
2285 aqbasket.creationdate,
2286 aqorders.datereceived,
2288 aqorders.quantityreceived,
2290 aqorders.ordernumber,
2292 aqinvoices.invoicenumber,
2293 aqbooksellers.id as id,
2294 aqorders.biblionumber,
2295 aqorders.orderstatus,
2296 aqorders.parent_ordernumber,
2297 aqbudgets.budget_name
2299 $query .= ", aqbudgets.budget_id AS budget" if defined $budget;
2302 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
2303 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
2304 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
2305 LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
2306 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
2307 LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id
2308 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2309 LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.biblionumber
2310 LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=aqorders.biblionumber
2313 if ( C4::Context->preference("IndependentBranches") ) {
2314 $query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber";
2317 $query .= " WHERE 1 ";
2319 unless ($get_canceled_order or (defined $orderstatus and $orderstatus eq 'cancelled')) {
2320 $query .= " AND (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') ";
2323 my @query_params = ();
2325 if ( $biblionumber ) {
2326 $query .= " AND biblio.biblionumber = ?";
2327 push @query_params, $biblionumber;
2331 $query .= " AND biblio.title LIKE ? ";
2332 $title =~ s/\s+/%/g;
2333 push @query_params, "%$title%";
2337 $query .= " AND biblio.author LIKE ? ";
2338 push @query_params, "%$author%";
2342 $query .= " AND biblioitems.isbn LIKE ? ";
2343 push @query_params, "%$isbn%";
2346 $query .= " AND biblioitems.ean = ? ";
2347 push @query_params, "$ean";
2350 $query .= " AND aqbooksellers.name LIKE ? ";
2351 push @query_params, "%$name%";
2355 $query .= " AND aqbudgets.budget_id = ? ";
2356 push @query_params, "$budget";
2359 if ( $from_placed_on ) {
2360 $query .= " AND creationdate >= ? ";
2361 push @query_params, $from_placed_on;
2364 if ( $to_placed_on ) {
2365 $query .= " AND creationdate <= ? ";
2366 push @query_params, $to_placed_on;
2369 if ( defined $orderstatus and $orderstatus ne '') {
2370 $query .= " AND aqorders.orderstatus = ? ";
2371 push @query_params, "$orderstatus";
2375 if ($basket =~ m/^\d+$/) {
2376 $query .= " AND aqorders.basketno = ? ";
2377 push @query_params, $basket;
2379 $query .= " AND aqbasket.basketname LIKE ? ";
2380 push @query_params, "%$basket%";
2384 if ($booksellerinvoicenumber) {
2385 $query .= " AND aqinvoices.invoicenumber LIKE ? ";
2386 push @query_params, "%$booksellerinvoicenumber%";
2389 if ($basketgroupname) {
2390 $query .= " AND aqbasketgroups.name LIKE ? ";
2391 push @query_params, "%$basketgroupname%";
2395 $query .= " AND (aqorders.ordernumber = ? ";
2396 push @query_params, $ordernumber;
2397 if ($search_children_too) {
2398 $query .= " OR aqorders.parent_ordernumber = ? ";
2399 push @query_params, $ordernumber;
2405 if ( C4::Context->preference("IndependentBranches") ) {
2406 unless ( C4::Context->IsSuperLibrarian() ) {
2407 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
2408 push @query_params, C4::Context->userenv->{branch};
2411 $query .= " ORDER BY id";
2412 my $sth = $dbh->prepare($query);
2413 $sth->execute( @query_params );
2415 while ( my $line = $sth->fetchrow_hashref ) {
2416 $line->{count} = $cnt++;
2417 $line->{toggle} = 1 if $cnt % 2;
2418 push @order_loop, $line;
2419 $total_qty += ( $line->{quantity} ) ? $line->{quantity} : 0;
2420 $total_qtyreceived += ( $line->{quantityreceived} ) ? $line->{quantityreceived} : 0;
2421 $total_price += ( $line->{quantity} and $line->{ecost} ) ? $line->{quantity} * $line->{ecost} : 0;
2423 return \@order_loop, $total_qty, $total_price, $total_qtyreceived;
2426 =head2 GetRecentAcqui
2428 $results = GetRecentAcqui($days);
2430 C<$results> is a ref to a table which containts hashref
2434 sub GetRecentAcqui {
2436 my $dbh = C4::Context->dbh;
2440 ORDER BY timestamp DESC
2443 my $sth = $dbh->prepare($query);
2445 my $results = $sth->fetchall_arrayref({});
2451 $contractlist = &GetContracts($booksellerid, $activeonly);
2453 Looks up the contracts that belong to a bookseller
2455 Returns a list of contracts
2459 =item C<$booksellerid> is the "id" field in the "aqbooksellers" table.
2461 =item C<$activeonly> if exists get only contracts that are still active.
2468 my ( $booksellerid, $activeonly ) = @_;
2469 my $dbh = C4::Context->dbh;
2471 if (! $activeonly) {
2475 WHERE booksellerid=?
2480 WHERE booksellerid=?
2481 AND contractenddate >= CURDATE( )";
2484 $dbh->selectall_arrayref( $query, { Slice => {} }, $booksellerid );
2485 return @{$result_set};
2488 #------------------------------------------------------------#
2492 $contract = &GetContract($contractID);
2494 Looks up the contract that has PRIMKEY (contractnumber) value $contractID
2501 my ( $contractno ) = @_;
2502 my $dbh = C4::Context->dbh;
2506 WHERE contractnumber=?
2509 my $sth = $dbh->prepare($query);
2510 $sth->execute( $contractno );
2511 my $result = $sth->fetchrow_hashref;
2519 &AddClaim($ordernumber);
2521 Add a claim for an order
2528 my ($ordernumber) = @_;
2529 my $dbh = C4::Context->dbh;
2532 claims_count = claims_count + 1,
2533 claimed_date = CURDATE()
2534 WHERE ordernumber = ?
2536 my $sth = $dbh->prepare($query);
2537 $sth->execute($ordernumber);
2542 my @invoices = GetInvoices(
2543 invoicenumber => $invoicenumber,
2544 supplierid => $supplierid,
2545 suppliername => $suppliername,
2546 shipmentdatefrom => $shipmentdatefrom, # ISO format
2547 shipmentdateto => $shipmentdateto, # ISO format
2548 billingdatefrom => $billingdatefrom, # ISO format
2549 billingdateto => $billingdateto, # ISO format
2550 isbneanissn => $isbn_or_ean_or_issn,
2553 publisher => $publisher,
2554 publicationyear => $publicationyear,
2555 branchcode => $branchcode,
2556 order_by => $order_by
2559 Return a list of invoices that match all given criteria.
2561 $order_by is "column_name (asc|desc)", where column_name is any of
2562 'invoicenumber', 'booksellerid', 'shipmentdate', 'billingdate', 'closedate',
2563 'shipmentcost', 'shipmentcost_budgetid'.
2565 asc is the default if omitted
2572 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2573 closedate shipmentcost shipmentcost_budgetid);
2575 my $dbh = C4::Context->dbh;
2577 SELECT aqinvoices.*, aqbooksellers.name AS suppliername,
2580 aqorders.datereceived IS NOT NULL,
2581 aqorders.biblionumber,
2584 ) AS receivedbiblios,
2585 SUM(aqorders.quantityreceived) AS receiveditems
2587 LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid
2588 LEFT JOIN aqorders ON aqorders.invoiceid = aqinvoices.invoiceid
2589 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
2590 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2591 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2592 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
2593 LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber
2598 if($args{supplierid}) {
2599 push @bind_strs, " aqinvoices.booksellerid = ? ";
2600 push @bind_args, $args{supplierid};
2602 if($args{invoicenumber}) {
2603 push @bind_strs, " aqinvoices.invoicenumber LIKE ? ";
2604 push @bind_args, "%$args{invoicenumber}%";
2606 if($args{suppliername}) {
2607 push @bind_strs, " aqbooksellers.name LIKE ? ";
2608 push @bind_args, "%$args{suppliername}%";
2610 if($args{shipmentdatefrom}) {
2611 push @bind_strs, " aqinvoices.shipmentdate >= ? ";
2612 push @bind_args, $args{shipmentdatefrom};
2614 if($args{shipmentdateto}) {
2615 push @bind_strs, " aqinvoices.shipmentdate <= ? ";
2616 push @bind_args, $args{shipmentdateto};
2618 if($args{billingdatefrom}) {
2619 push @bind_strs, " aqinvoices.billingdate >= ? ";
2620 push @bind_args, $args{billingdatefrom};
2622 if($args{billingdateto}) {
2623 push @bind_strs, " aqinvoices.billingdate <= ? ";
2624 push @bind_args, $args{billingdateto};
2626 if($args{isbneanissn}) {
2627 push @bind_strs, " (biblioitems.isbn LIKE CONCAT('%', ?, '%') OR biblioitems.ean LIKE CONCAT('%', ?, '%') OR biblioitems.issn LIKE CONCAT('%', ?, '%') ) ";
2628 push @bind_args, $args{isbneanissn}, $args{isbneanissn}, $args{isbneanissn};
2631 push @bind_strs, " biblio.title LIKE CONCAT('%', ?, '%') ";
2632 push @bind_args, $args{title};
2635 push @bind_strs, " biblio.author LIKE CONCAT('%', ?, '%') ";
2636 push @bind_args, $args{author};
2638 if($args{publisher}) {
2639 push @bind_strs, " biblioitems.publishercode LIKE CONCAT('%', ?, '%') ";
2640 push @bind_args, $args{publisher};
2642 if($args{publicationyear}) {
2643 push @bind_strs, " ((biblioitems.publicationyear LIKE CONCAT('%', ?, '%')) OR (biblio.copyrightdate LIKE CONCAT('%', ?, '%'))) ";
2644 push @bind_args, $args{publicationyear}, $args{publicationyear};
2646 if($args{branchcode}) {
2647 push @bind_strs, " borrowers.branchcode = ? ";
2648 push @bind_args, $args{branchcode};
2651 $query .= " WHERE " . join(" AND ", @bind_strs) if @bind_strs;
2652 $query .= " GROUP BY aqinvoices.invoiceid ";
2654 if($args{order_by}) {
2655 my ($column, $direction) = split / /, $args{order_by};
2656 if(grep /^$column$/, @columns) {
2657 $direction ||= 'ASC';
2658 $query .= " ORDER BY $column $direction";
2662 my $sth = $dbh->prepare($query);
2663 $sth->execute(@bind_args);
2665 my $results = $sth->fetchall_arrayref({});
2671 my $invoice = GetInvoice($invoiceid);
2673 Get informations about invoice with given $invoiceid
2675 Return a hash filled with aqinvoices.* fields
2680 my ($invoiceid) = @_;
2683 return unless $invoiceid;
2685 my $dbh = C4::Context->dbh;
2691 my $sth = $dbh->prepare($query);
2692 $sth->execute($invoiceid);
2694 $invoice = $sth->fetchrow_hashref;
2698 =head3 GetInvoiceDetails
2700 my $invoice = GetInvoiceDetails($invoiceid)
2702 Return informations about an invoice + the list of related order lines
2704 Orders informations are in $invoice->{orders} (array ref)
2708 sub GetInvoiceDetails {
2709 my ($invoiceid) = @_;
2711 if ( !defined $invoiceid ) {
2712 carp 'GetInvoiceDetails called without an invoiceid';
2716 my $dbh = C4::Context->dbh;
2718 SELECT aqinvoices.*, aqbooksellers.name AS suppliername
2720 LEFT JOIN aqbooksellers ON aqinvoices.booksellerid = aqbooksellers.id
2723 my $sth = $dbh->prepare($query);
2724 $sth->execute($invoiceid);
2726 my $invoice = $sth->fetchrow_hashref;
2729 SELECT aqorders.*, biblio.*, aqbasket.basketname
2731 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
2732 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2735 $sth = $dbh->prepare($query);
2736 $sth->execute($invoiceid);
2737 $invoice->{orders} = $sth->fetchall_arrayref({});
2738 $invoice->{orders} ||= []; # force an empty arrayref if fetchall_arrayref fails
2745 my $invoiceid = AddInvoice(
2746 invoicenumber => $invoicenumber,
2747 booksellerid => $booksellerid,
2748 shipmentdate => $shipmentdate,
2749 billingdate => $billingdate,
2750 closedate => $closedate,
2751 shipmentcost => $shipmentcost,
2752 shipmentcost_budgetid => $shipmentcost_budgetid
2755 Create a new invoice and return its id or undef if it fails.
2762 return unless(%invoice and $invoice{invoicenumber});
2764 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2765 closedate shipmentcost shipmentcost_budgetid);
2769 foreach my $key (keys %invoice) {
2770 if(0 < grep(/^$key$/, @columns)) {
2771 push @set_strs, "$key = ?";
2772 push @set_args, ($invoice{$key} || undef);
2778 my $dbh = C4::Context->dbh;
2779 my $query = "INSERT INTO aqinvoices SET ";
2780 $query .= join (",", @set_strs);
2781 my $sth = $dbh->prepare($query);
2782 $rv = $sth->execute(@set_args);
2784 $rv = $dbh->last_insert_id(undef, undef, 'aqinvoices', undef);
2793 invoiceid => $invoiceid, # Mandatory
2794 invoicenumber => $invoicenumber,
2795 booksellerid => $booksellerid,
2796 shipmentdate => $shipmentdate,
2797 billingdate => $billingdate,
2798 closedate => $closedate,
2799 shipmentcost => $shipmentcost,
2800 shipmentcost_budgetid => $shipmentcost_budgetid
2803 Modify an invoice, invoiceid is mandatory.
2805 Return undef if it fails.
2812 return unless(%invoice and $invoice{invoiceid});
2814 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2815 closedate shipmentcost shipmentcost_budgetid);
2819 foreach my $key (keys %invoice) {
2820 if(0 < grep(/^$key$/, @columns)) {
2821 push @set_strs, "$key = ?";
2822 push @set_args, ($invoice{$key} || undef);
2826 my $dbh = C4::Context->dbh;
2827 my $query = "UPDATE aqinvoices SET ";
2828 $query .= join(",", @set_strs);
2829 $query .= " WHERE invoiceid = ?";
2831 my $sth = $dbh->prepare($query);
2832 $sth->execute(@set_args, $invoice{invoiceid});
2837 CloseInvoice($invoiceid);
2841 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => undef);
2846 my ($invoiceid) = @_;
2848 return unless $invoiceid;
2850 my $dbh = C4::Context->dbh;
2853 SET closedate = CAST(NOW() AS DATE)
2856 my $sth = $dbh->prepare($query);
2857 $sth->execute($invoiceid);
2860 =head3 ReopenInvoice
2862 ReopenInvoice($invoiceid);
2866 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => C4::Dates->new()->output('iso'))
2871 my ($invoiceid) = @_;
2873 return unless $invoiceid;
2875 my $dbh = C4::Context->dbh;
2878 SET closedate = NULL
2881 my $sth = $dbh->prepare($query);
2882 $sth->execute($invoiceid);
2887 DelInvoice($invoiceid);
2889 Delete an invoice if there are no items attached to it.
2894 my ($invoiceid) = @_;
2896 return unless $invoiceid;
2898 my $dbh = C4::Context->dbh;
2904 my $sth = $dbh->prepare($query);
2905 $sth->execute($invoiceid);
2906 my $res = $sth->fetchrow_arrayref;
2907 if ( $res && $res->[0] == 0 ) {
2909 DELETE FROM aqinvoices
2912 my $sth = $dbh->prepare($query);
2913 return ( $sth->execute($invoiceid) > 0 );
2918 =head3 MergeInvoices
2920 MergeInvoices($invoiceid, \@sourceids);
2922 Merge the invoices identified by the IDs in \@sourceids into
2923 the invoice identified by $invoiceid.
2928 my ($invoiceid, $sourceids) = @_;
2930 return unless $invoiceid;
2931 foreach my $sourceid (@$sourceids) {
2932 next if $sourceid == $invoiceid;
2933 my $source = GetInvoiceDetails($sourceid);
2934 foreach my $order (@{$source->{'orders'}}) {
2935 $order->{'invoiceid'} = $invoiceid;
2938 DelInvoice($source->{'invoiceid'});
2943 =head3 GetBiblioCountByBasketno
2945 $biblio_count = &GetBiblioCountByBasketno($basketno);
2947 Looks up the biblio's count that has basketno value $basketno
2953 sub GetBiblioCountByBasketno {
2954 my ($basketno) = @_;
2955 my $dbh = C4::Context->dbh;
2957 SELECT COUNT( DISTINCT( biblionumber ) )
2960 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
2963 my $sth = $dbh->prepare($query);
2964 $sth->execute($basketno);
2965 return $sth->fetchrow;
2973 Koha Development Team <http://koha-community.org/>