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->{'notes'},
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->{notes},
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 my $dbh = C4::Context->dbh;
1051 SELECT biblio.*,biblioitems.*,
1055 aqorders_transfers.ordernumber_from AS transferred_from,
1056 aqorders_transfers.timestamp AS transferred_from_timestamp
1058 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1059 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1060 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1061 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1063 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
1066 $orderby = "biblioitems.publishercode,biblio.title" unless $orderby;
1067 $query .= " ORDER BY $orderby";
1069 $dbh->selectall_arrayref( $query, { Slice => {} }, $basketno );
1070 return @{$result_set};
1074 #------------------------------------------------------------#
1075 =head3 GetOrdersByBiblionumber
1077 @orders = &GetOrdersByBiblionumber($biblionumber);
1079 Looks up the orders with linked to a specific $biblionumber, including
1080 cancelled orders and received orders.
1083 C<@orders> is an array of references-to-hash, whose keys are the
1084 fields from the aqorders, biblio, and biblioitems tables in the Koha database.
1088 sub GetOrdersByBiblionumber {
1089 my $biblionumber = shift;
1090 return unless $biblionumber;
1091 my $dbh = C4::Context->dbh;
1093 SELECT biblio.*,biblioitems.*,
1097 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1098 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1099 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1100 WHERE aqorders.biblionumber=?
1103 $dbh->selectall_arrayref( $query, { Slice => {} }, $biblionumber );
1104 return @{$result_set};
1108 #------------------------------------------------------------#
1112 $order = &GetOrder($ordernumber);
1114 Looks up an order by order number.
1116 Returns a reference-to-hash describing the order. The keys of
1117 C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database.
1122 my ($ordernumber) = @_;
1123 return unless $ordernumber;
1125 my $dbh = C4::Context->dbh;
1126 my $query = qq{SELECT
1130 aqbasket.basketname,
1131 borrowers.branchcode,
1132 biblioitems.publicationyear,
1133 biblio.copyrightdate,
1134 biblioitems.editionstatement,
1138 biblioitems.publishercode,
1139 aqorders.rrp AS unitpricesupplier,
1140 aqorders.ecost AS unitpricelib,
1141 aqorders.claims_count AS claims_count,
1142 aqorders.claimed_date AS claimed_date,
1143 aqbudgets.budget_name AS budget,
1144 aqbooksellers.name AS supplier,
1145 aqbooksellers.id AS supplierid,
1146 biblioitems.publishercode AS publisher,
1147 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
1148 DATE(aqbasket.closedate) AS orderdate,
1149 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity_to_receive,
1150 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1151 DATEDIFF(CURDATE( ),closedate) AS latesince
1152 FROM aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1153 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1154 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
1155 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
1156 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1157 WHERE aqorders.basketno = aqbasket.basketno
1160 $dbh->selectall_arrayref( $query, { Slice => {} }, $ordernumber );
1162 # result_set assumed to contain 1 match
1163 return $result_set->[0];
1166 =head3 GetLastOrderNotReceivedFromSubscriptionid
1168 $order = &GetLastOrderNotReceivedFromSubscriptionid($subscriptionid);
1170 Returns a reference-to-hash describing the last order not received for a subscription.
1174 sub GetLastOrderNotReceivedFromSubscriptionid {
1175 my ( $subscriptionid ) = @_;
1176 my $dbh = C4::Context->dbh;
1178 SELECT * FROM aqorders
1179 LEFT JOIN subscription
1180 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1181 WHERE aqorders.subscriptionid = ?
1182 AND aqorders.datereceived IS NULL
1186 $dbh->selectall_arrayref( $query, { Slice => {} }, $subscriptionid );
1188 # result_set assumed to contain 1 match
1189 return $result_set->[0];
1192 =head3 GetLastOrderReceivedFromSubscriptionid
1194 $order = &GetLastOrderReceivedFromSubscriptionid($subscriptionid);
1196 Returns a reference-to-hash describing the last order received for a subscription.
1200 sub GetLastOrderReceivedFromSubscriptionid {
1201 my ( $subscriptionid ) = @_;
1202 my $dbh = C4::Context->dbh;
1204 SELECT * FROM aqorders
1205 LEFT JOIN subscription
1206 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1207 WHERE aqorders.subscriptionid = ?
1208 AND aqorders.datereceived =
1210 SELECT MAX( aqorders.datereceived )
1212 LEFT JOIN subscription
1213 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1214 WHERE aqorders.subscriptionid = ?
1215 AND aqorders.datereceived IS NOT NULL
1217 ORDER BY ordernumber DESC
1221 $dbh->selectall_arrayref( $query, { Slice => {} }, $subscriptionid, $subscriptionid );
1223 # result_set assumed to contain 1 match
1224 return $result_set->[0];
1229 #------------------------------------------------------------#
1233 &NewOrder(\%hashref);
1235 Adds a new order to the database. Any argument that isn't described
1236 below is the new value of the field with the same name in the aqorders
1237 table of the Koha database.
1241 =item $hashref->{'basketno'} is the basketno foreign key in aqorders, it is mandatory
1243 =item $hashref->{'ordernumber'} is a "minimum order number."
1245 =item $hashref->{'budgetdate'} is effectively ignored.
1246 If it's undef (anything false) or the string 'now', the current day is used.
1247 Else, the upcoming July 1st is used.
1249 =item $hashref->{'subscription'} may be either "yes", or anything else for "no".
1251 =item $hashref->{'uncertainprice'} may be 0 for "the price is known" or 1 for "the price is uncertain"
1253 =item defaults entrydate to Now
1255 The following keys are used: "biblionumber", "title", "basketno", "quantity", "notes", "rrp", "ecost", "gstrate", "unitprice", "subscription", "sort1", "sort2", "booksellerinvoicenumber", "listprice", "budgetdate", "purchaseordernumber", "branchcode", "booksellerinvoicenumber", "budget_id".
1262 my $orderinfo = shift;
1264 my $dbh = C4::Context->dbh;
1268 # if these parameters are missing, we can't continue
1269 for my $key (qw/basketno quantity biblionumber budget_id/) {
1270 croak "Mandatory parameter $key missing" unless $orderinfo->{$key};
1273 if ( defined $orderinfo->{subscription} && $orderinfo->{'subscription'} eq 'yes' ) {
1274 $orderinfo->{'subscription'} = 1;
1276 $orderinfo->{'subscription'} = 0;
1278 $orderinfo->{'entrydate'} ||= C4::Dates->new()->output("iso");
1279 if (!$orderinfo->{quantityreceived}) {
1280 $orderinfo->{quantityreceived} = 0;
1283 my $ordernumber=InsertInTable("aqorders",$orderinfo);
1284 if (not $orderinfo->{parent_ordernumber}) {
1285 my $sth = $dbh->prepare("
1287 SET parent_ordernumber = ordernumber
1288 WHERE ordernumber = ?
1290 $sth->execute($ordernumber);
1292 return ( $orderinfo->{'basketno'}, $ordernumber );
1297 #------------------------------------------------------------#
1306 my ($itemnumber, $ordernumber) = @_;
1307 my $dbh = C4::Context->dbh;
1309 INSERT INTO aqorders_items
1310 (itemnumber, ordernumber)
1313 my $sth = $dbh->prepare($query);
1314 $sth->execute( $itemnumber, $ordernumber);
1317 #------------------------------------------------------------#
1321 &ModOrder(\%hashref);
1323 Modifies an existing order. Updates the order with order number
1324 $hashref->{'ordernumber'} and biblionumber $hashref->{'biblionumber'}. All
1325 other keys of the hash update the fields with the same name in the aqorders
1326 table of the Koha database.
1331 my $orderinfo = shift;
1333 die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq '' ;
1334 die "Biblionumber is required" if $orderinfo->{'biblionumber'} eq '';
1336 my $dbh = C4::Context->dbh;
1339 # update uncertainprice to an integer, just in case (under FF, checked boxes have the value "ON" by default)
1340 $orderinfo->{uncertainprice}=1 if $orderinfo->{uncertainprice};
1342 # delete($orderinfo->{'branchcode'});
1343 # the hash contains a lot of entries not in aqorders, so get the columns ...
1344 my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;");
1346 my $colnames = $sth->{NAME};
1347 #FIXME Be careful. If aqorders would have columns with diacritics,
1348 #you should need to decode what you get back from NAME.
1349 #See report 10110 and guided_reports.pl
1350 my $query = "UPDATE aqorders SET ";
1352 foreach my $orderinfokey (grep(!/ordernumber/, keys %$orderinfo)){
1353 # ... and skip hash entries that are not in the aqorders table
1354 # FIXME : probably not the best way to do it (would be better to have a correct hash)
1355 next unless grep(/^$orderinfokey$/, @$colnames);
1356 $query .= "$orderinfokey=?, ";
1357 push(@params, $orderinfo->{$orderinfokey});
1360 $query .= "timestamp=NOW() WHERE ordernumber=?";
1361 push(@params, $orderinfo->{'ordernumber'} );
1362 $sth = $dbh->prepare($query);
1363 $sth->execute(@params);
1367 #------------------------------------------------------------#
1371 ModItemOrder($itemnumber, $ordernumber);
1373 Modifies the ordernumber of an item in aqorders_items.
1378 my ($itemnumber, $ordernumber) = @_;
1380 return unless ($itemnumber and $ordernumber);
1382 my $dbh = C4::Context->dbh;
1384 UPDATE aqorders_items
1386 WHERE itemnumber = ?
1388 my $sth = $dbh->prepare($query);
1389 return $sth->execute($ordernumber, $itemnumber);
1392 #------------------------------------------------------------#
1394 =head3 GetCancelledOrders
1396 my @orders = GetCancelledOrders($basketno, $orderby);
1398 Returns cancelled orders for a basket
1402 sub GetCancelledOrders {
1403 my ( $basketno, $orderby ) = @_;
1405 return () unless $basketno;
1407 my $dbh = C4::Context->dbh;
1414 aqorders_transfers.ordernumber_to AS transferred_to,
1415 aqorders_transfers.timestamp AS transferred_to_timestamp
1417 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1418 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1419 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1420 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber
1422 AND (datecancellationprinted IS NOT NULL
1423 AND datecancellationprinted <> '0000-00-00')
1426 $orderby = "aqorders.datecancellationprinted desc, aqorders.timestamp desc"
1428 $query .= " ORDER BY $orderby";
1429 my $sth = $dbh->prepare($query);
1430 $sth->execute($basketno);
1431 my $results = $sth->fetchall_arrayref( {} );
1437 #------------------------------------------------------------#
1439 =head3 ModReceiveOrder
1442 biblionumber => $biblionumber,
1443 ordernumber => $ordernumber,
1444 quantityreceived => $quantityreceived,
1448 invoiceid => $invoiceid,
1450 budget_id => $budget_id,
1451 datereceived => $datereceived,
1452 received_itemnumbers => \@received_itemnumbers,
1456 Updates an order, to reflect the fact that it was received, at least
1457 in part. All arguments not mentioned below update the fields with the
1458 same name in the aqorders table of the Koha database.
1460 If a partial order is received, splits the order into two.
1462 Updates the order with bibilionumber C<$biblionumber> and ordernumber
1468 sub ModReceiveOrder {
1469 my ( $params ) = @_;
1470 my $biblionumber = $params->{biblionumber};
1471 my $ordernumber = $params->{ordernumber};
1472 my $quantrec = $params->{quantityreceived};
1473 my $user = $params->{user};
1474 my $cost = $params->{cost};
1475 my $ecost = $params->{ecost};
1476 my $invoiceid = $params->{invoiceid};
1477 my $rrp = $params->{rrp};
1478 my $budget_id = $params->{budget_id};
1479 my $datereceived = $params->{datereceived};
1480 my $received_items = $params->{received_items};
1481 my $notes = $params->{notes};
1483 my $dbh = C4::Context->dbh;
1484 $datereceived = C4::Dates->output('iso') unless $datereceived;
1485 my $suggestionid = GetSuggestionFromBiblionumber( $biblionumber );
1486 if ($suggestionid) {
1487 ModSuggestion( {suggestionid=>$suggestionid,
1488 STATUS=>'AVAILABLE',
1489 biblionumber=> $biblionumber}
1493 my $result_set = $dbh->selectall_arrayref(
1494 q{SELECT * FROM aqorders WHERE biblionumber=? AND aqorders.ordernumber=?},
1495 { Slice => {} }, $biblionumber, $ordernumber
1498 # we assume we have a unique order
1499 my $order = $result_set->[0];
1501 my $new_ordernumber = $ordernumber;
1502 if ( $order->{quantity} > $quantrec ) {
1503 # Split order line in two parts: the first is the original order line
1504 # without received items (the quantity is decreased),
1505 # the second part is a new order line with quantity=quantityrec
1506 # (entirely received)
1510 orderstatus = 'partial'|;
1511 $query .= q|, notes = ?| if defined $notes;
1512 $query .= q| WHERE ordernumber = ?|;
1513 my $sth = $dbh->prepare($query);
1515 $sth->execute($order->{quantity} - $quantrec, ( defined $notes ? $notes : () ), $ordernumber);
1517 delete $order->{'ordernumber'};
1518 $order->{'budget_id'} = ( $budget_id || $order->{'budget_id'} );
1519 $order->{'quantity'} = $quantrec;
1520 $order->{'quantityreceived'} = $quantrec;
1521 $order->{'datereceived'} = $datereceived;
1522 $order->{'invoiceid'} = $invoiceid;
1523 $order->{'unitprice'} = $cost;
1524 $order->{'rrp'} = $rrp;
1525 $order->{ecost} = $ecost;
1526 $order->{'orderstatus'} = 'complete';
1528 ( $basketno, $new_ordernumber ) = NewOrder($order);
1530 if ($received_items) {
1531 foreach my $itemnumber (@$received_items) {
1532 ModItemOrder($itemnumber, $new_ordernumber);
1538 set quantityreceived=?,datereceived=?,invoiceid=?,
1539 unitprice=?,rrp=?,ecost=?,budget_id=?,orderstatus='complete'|;
1540 $query .= q|, notes = ?| if defined $notes;
1541 $query .= q| where biblionumber=? and ordernumber=?|;
1542 my $sth = $dbh->prepare( $query );
1543 $sth->execute($quantrec,$datereceived,$invoiceid,$cost,$rrp,$ecost,$budget_id,( defined $notes ? $notes : () ),$biblionumber,$ordernumber);
1545 return ($datereceived, $new_ordernumber);
1548 =head3 CancelReceipt
1550 my $parent_ordernumber = CancelReceipt($ordernumber);
1552 Cancel an order line receipt and update the parent order line, as if no
1554 If items are created at receipt (AcqCreateItem = receiving) then delete
1560 my $ordernumber = shift;
1562 return unless $ordernumber;
1564 my $dbh = C4::Context->dbh;
1566 SELECT datereceived, parent_ordernumber, quantity
1568 WHERE ordernumber = ?
1570 my $sth = $dbh->prepare($query);
1571 $sth->execute($ordernumber);
1572 my $order = $sth->fetchrow_hashref;
1574 warn "CancelReceipt: order $ordernumber does not exist";
1577 unless($order->{'datereceived'}) {
1578 warn "CancelReceipt: order $ordernumber is not received";
1582 my $parent_ordernumber = $order->{'parent_ordernumber'};
1584 if($parent_ordernumber == $ordernumber || not $parent_ordernumber) {
1585 # The order line has no parent, just mark it as not received
1588 SET quantityreceived = ?,
1591 orderstatus = 'ordered'
1592 WHERE ordernumber = ?
1594 $sth = $dbh->prepare($query);
1595 $sth->execute(0, undef, undef, $ordernumber);
1597 # The order line has a parent, increase parent quantity and delete
1600 SELECT quantity, datereceived
1602 WHERE ordernumber = ?
1604 $sth = $dbh->prepare($query);
1605 $sth->execute($parent_ordernumber);
1606 my $parent_order = $sth->fetchrow_hashref;
1607 unless($parent_order) {
1608 warn "Parent order $parent_ordernumber does not exist.";
1611 if($parent_order->{'datereceived'}) {
1612 warn "CancelReceipt: parent order is received.".
1613 " Can't cancel receipt.";
1619 orderstatus = 'ordered'
1620 WHERE ordernumber = ?
1622 $sth = $dbh->prepare($query);
1623 my $rv = $sth->execute(
1624 $order->{'quantity'} + $parent_order->{'quantity'},
1628 warn "Cannot update parent order line, so do not cancel".
1632 if(C4::Context->preference('AcqCreateItem') eq 'receiving') {
1633 # Remove items that were created at receipt
1635 DELETE FROM items, aqorders_items
1636 USING items, aqorders_items
1637 WHERE items.itemnumber = ? AND aqorders_items.itemnumber = ?
1639 $sth = $dbh->prepare($query);
1640 my @itemnumbers = GetItemnumbersFromOrder($ordernumber);
1641 foreach my $itemnumber (@itemnumbers) {
1642 $sth->execute($itemnumber, $itemnumber);
1646 my @itemnumbers = GetItemnumbersFromOrder($ordernumber);
1647 foreach my $itemnumber (@itemnumbers) {
1648 ModItemOrder($itemnumber, $parent_ordernumber);
1653 DELETE FROM aqorders
1654 WHERE ordernumber = ?
1656 $sth = $dbh->prepare($query);
1657 $sth->execute($ordernumber);
1661 return $parent_ordernumber;
1664 #------------------------------------------------------------#
1668 @results = &SearchOrders({
1669 ordernumber => $ordernumber,
1671 biblionumber => $biblionumber,
1673 booksellerid => $booksellerid,
1674 basketno => $basketno,
1679 Searches for orders.
1681 C<$owner> Finds order for the logged in user.
1682 C<$pending> Finds pending orders. Ignores completed and cancelled orders.
1685 C<@results> is an array of references-to-hash with the keys are fields
1686 from aqorders, biblio, biblioitems and aqbasket tables.
1691 my ( $params ) = @_;
1692 my $ordernumber = $params->{ordernumber};
1693 my $search = $params->{search};
1694 my $ean = $params->{ean};
1695 my $booksellerid = $params->{booksellerid};
1696 my $basketno = $params->{basketno};
1697 my $basketname = $params->{basketname};
1698 my $basketgroupname = $params->{basketgroupname};
1699 my $owner = $params->{owner};
1700 my $pending = $params->{pending};
1702 my $dbh = C4::Context->dbh;
1705 SELECT aqbasket.basketno,
1707 borrowers.firstname,
1710 biblioitems.biblioitemnumber,
1711 aqbasket.authorisedby,
1712 aqbasket.booksellerid,
1714 aqbasket.creationdate,
1715 aqbasket.basketname,
1716 aqbasketgroups.id as basketgroupid,
1717 aqbasketgroups.name as basketgroupname,
1720 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
1721 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
1722 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1723 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1724 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
1727 # If we search on ordernumber, we retrieve the transfered order is a transfer has been done.
1729 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1733 WHERE (datecancellationprinted is NULL)
1737 AND (quantity > quantityreceived OR quantityreceived is NULL)
1740 my $userenv = C4::Context->userenv;
1741 if ( C4::Context->preference("IndependentBranches") ) {
1742 unless ( C4::Context->IsSuperLibrarian() ) {
1745 borrowers.branchcode = ?
1746 OR borrowers.branchcode = ''
1749 push @args, $userenv->{branch};
1753 if ( $ordernumber ) {
1754 $query .= ' AND ( aqorders.ordernumber = ? OR aqorders_transfers.ordernumber_from = ? ) ';
1755 push @args, ( $ordernumber, $ordernumber );
1758 $query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)';
1759 push @args, ("%$search%","%$search%","%$search%");
1762 $query .= ' AND biblioitems.ean = ?';
1765 if ( $booksellerid ) {
1766 $query .= 'AND aqbasket.booksellerid = ?';
1767 push @args, $booksellerid;
1770 $query .= 'AND aqbasket.basketno = ?';
1771 push @args, $basketno;
1774 $query .= 'AND aqbasket.basketname LIKE ?';
1775 push @args, "%$basketname%";
1777 if( $basketgroupname ) {
1778 $query .= ' AND aqbasketgroups.name LIKE ?';
1779 push @args, "%$basketgroupname%";
1783 $query .= ' AND aqbasket.authorisedby=? ';
1784 push @args, $userenv->{'number'};
1787 $query .= ' ORDER BY aqbasket.basketno';
1789 my $sth = $dbh->prepare($query);
1790 $sth->execute(@args);
1791 return $sth->fetchall_arrayref({});
1794 #------------------------------------------------------------#
1798 &DelOrder($biblionumber, $ordernumber);
1800 Cancel the order with the given order and biblio numbers. It does not
1801 delete any entries in the aqorders table, it merely marks them as
1807 my ( $bibnum, $ordernumber ) = @_;
1808 my $dbh = C4::Context->dbh;
1811 SET datecancellationprinted=now(), orderstatus='cancelled'
1812 WHERE biblionumber=? AND ordernumber=?
1814 my $sth = $dbh->prepare($query);
1815 $sth->execute( $bibnum, $ordernumber );
1816 my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1817 foreach my $itemnumber (@itemnumbers){
1818 C4::Items::DelItem( $dbh, $bibnum, $itemnumber );
1823 =head3 TransferOrder
1825 my $newordernumber = TransferOrder($ordernumber, $basketno);
1827 Transfer an order line to a basket.
1828 Mark $ordernumber as cancelled with an internal note 'Cancelled and transfered
1829 to BOOKSELLER on DATE' and create new order with internal note
1830 'Transfered from BOOKSELLER on DATE'.
1831 Move all attached items to the new order.
1832 Received orders cannot be transfered.
1833 Return the ordernumber of created order.
1838 my ($ordernumber, $basketno) = @_;
1840 return unless ($ordernumber and $basketno);
1842 my $order = GetOrder( $ordernumber );
1843 return if $order->{datereceived};
1844 my $basket = GetBasket($basketno);
1845 return unless $basket;
1847 my $dbh = C4::Context->dbh;
1848 my ($query, $sth, $rv);
1852 SET datecancellationprinted = CAST(NOW() AS date)
1853 WHERE ordernumber = ?
1855 $sth = $dbh->prepare($query);
1856 $rv = $sth->execute($ordernumber);
1858 delete $order->{'ordernumber'};
1859 delete $order->{parent_ordernumber};
1860 $order->{'basketno'} = $basketno;
1862 (undef, $newordernumber) = NewOrder($order);
1865 UPDATE aqorders_items
1867 WHERE ordernumber = ?
1869 $sth = $dbh->prepare($query);
1870 $sth->execute($newordernumber, $ordernumber);
1873 INSERT INTO aqorders_transfers (ordernumber_from, ordernumber_to)
1876 $sth = $dbh->prepare($query);
1877 $sth->execute($ordernumber, $newordernumber);
1879 return $newordernumber;
1882 =head2 FUNCTIONS ABOUT PARCELS
1886 #------------------------------------------------------------#
1890 @results = &GetParcel($booksellerid, $code, $date);
1892 Looks up all of the received items from the supplier with the given
1893 bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders.
1895 C<@results> is an array of references-to-hash. The keys of each element are fields from
1896 the aqorders, biblio, and biblioitems tables of the Koha database.
1898 C<@results> is sorted alphabetically by book title.
1903 #gets all orders from a certain supplier, orders them alphabetically
1904 my ( $supplierid, $code, $datereceived ) = @_;
1905 my $dbh = C4::Context->dbh;
1908 if $code; # add % if we search on a given code (otherwise, let him empty)
1910 SELECT authorisedby,
1915 aqorders.biblionumber,
1916 aqorders.ordernumber,
1917 aqorders.parent_ordernumber,
1919 aqorders.quantityreceived,
1927 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
1928 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1929 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1930 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
1932 aqbasket.booksellerid = ?
1933 AND aqinvoices.invoicenumber LIKE ?
1934 AND aqorders.datereceived = ? ";
1936 my @query_params = ( $supplierid, $code, $datereceived );
1937 if ( C4::Context->preference("IndependentBranches") ) {
1938 unless ( C4::Context->IsSuperLibrarian() ) {
1939 $strsth .= " and (borrowers.branchcode = ?
1940 or borrowers.branchcode = '')";
1941 push @query_params, C4::Context->userenv->{branch};
1944 $strsth .= " ORDER BY aqbasket.basketno";
1945 my $result_set = $dbh->selectall_arrayref(
1950 return @{$result_set};
1953 #------------------------------------------------------------#
1957 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
1959 get a lists of parcels.
1966 is the bookseller this function has to get parcels.
1969 To know on what criteria the results list has to be ordered.
1972 is the booksellerinvoicenumber.
1974 =item $datefrom & $dateto
1975 to know on what date this function has to filter its search.
1980 a pointer on a hash list containing parcel informations as such :
1986 =item Last operation
1988 =item Number of biblio
1990 =item Number of items
1997 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
1998 my $dbh = C4::Context->dbh;
1999 my @query_params = ();
2001 SELECT aqinvoices.invoicenumber,
2002 datereceived,purchaseordernumber,
2003 count(DISTINCT biblionumber) AS biblio,
2004 sum(quantity) AS itemsexpected,
2005 sum(quantityreceived) AS itemsreceived
2006 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
2007 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2008 WHERE aqbasket.booksellerid = ? and datereceived IS NOT NULL
2010 push @query_params, $bookseller;
2012 if ( defined $code ) {
2013 $strsth .= ' and aqinvoices.invoicenumber like ? ';
2014 # add a % to the end of the code to allow stemming.
2015 push @query_params, "$code%";
2018 if ( defined $datefrom ) {
2019 $strsth .= ' and datereceived >= ? ';
2020 push @query_params, $datefrom;
2023 if ( defined $dateto ) {
2024 $strsth .= 'and datereceived <= ? ';
2025 push @query_params, $dateto;
2028 $strsth .= "group by aqinvoices.invoicenumber,datereceived ";
2030 # can't use a placeholder to place this column name.
2031 # but, we could probably be checking to make sure it is a column that will be fetched.
2032 $strsth .= "order by $order " if ($order);
2034 my $sth = $dbh->prepare($strsth);
2036 $sth->execute( @query_params );
2037 my $results = $sth->fetchall_arrayref({});
2041 #------------------------------------------------------------#
2043 =head3 GetLateOrders
2045 @results = &GetLateOrders;
2047 Searches for bookseller with late orders.
2050 the table of supplier with late issues. This table is full of hashref.
2056 my $supplierid = shift;
2058 my $estimateddeliverydatefrom = shift;
2059 my $estimateddeliverydateto = shift;
2061 my $dbh = C4::Context->dbh;
2063 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
2064 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
2066 my @query_params = ();
2068 SELECT aqbasket.basketno,
2069 aqorders.ordernumber,
2070 DATE(aqbasket.closedate) AS orderdate,
2071 aqorders.rrp AS unitpricesupplier,
2072 aqorders.ecost AS unitpricelib,
2073 aqorders.claims_count AS claims_count,
2074 aqorders.claimed_date AS claimed_date,
2075 aqbudgets.budget_name AS budget,
2076 borrowers.branchcode AS branch,
2077 aqbooksellers.name AS supplier,
2078 aqbooksellers.id AS supplierid,
2079 biblio.author, biblio.title,
2080 biblioitems.publishercode AS publisher,
2081 biblioitems.publicationyear,
2082 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
2086 aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
2087 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
2088 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
2089 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
2090 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
2091 WHERE aqorders.basketno = aqbasket.basketno
2092 AND ( datereceived = ''
2093 OR datereceived IS NULL
2094 OR aqorders.quantityreceived < aqorders.quantity
2096 AND aqbasket.closedate IS NOT NULL
2097 AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
2100 if ($dbdriver eq "mysql") {
2102 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity,
2103 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
2104 DATEDIFF(CAST(now() AS date),closedate) AS latesince
2106 if ( defined $delay ) {
2107 $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ;
2108 push @query_params, $delay;
2111 HAVING quantity <> 0
2112 AND unitpricesupplier <> 0
2113 AND unitpricelib <> 0
2116 # FIXME: account for IFNULL as above
2118 aqorders.quantity AS quantity,
2119 aqorders.quantity * aqorders.rrp AS subtotal,
2120 (CAST(now() AS date) - closedate) AS latesince
2122 if ( defined $delay ) {
2123 $from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) ";
2124 push @query_params, $delay;
2127 if (defined $supplierid) {
2128 $from .= ' AND aqbasket.booksellerid = ? ';
2129 push @query_params, $supplierid;
2131 if (defined $branch) {
2132 $from .= ' AND borrowers.branchcode LIKE ? ';
2133 push @query_params, $branch;
2136 if ( defined $estimateddeliverydatefrom or defined $estimateddeliverydateto ) {
2137 $from .= ' AND aqbooksellers.deliverytime IS NOT NULL ';
2139 if ( defined $estimateddeliverydatefrom ) {
2140 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
2141 push @query_params, $estimateddeliverydatefrom;
2143 if ( defined $estimateddeliverydateto ) {
2144 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
2145 push @query_params, $estimateddeliverydateto;
2147 if ( defined $estimateddeliverydatefrom and not defined $estimateddeliverydateto ) {
2148 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
2150 if (C4::Context->preference("IndependentBranches")
2151 && !C4::Context->IsSuperLibrarian() ) {
2152 $from .= ' AND borrowers.branchcode LIKE ? ';
2153 push @query_params, C4::Context->userenv->{branch};
2155 $from .= " AND orderstatus <> 'cancelled' ";
2156 my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier";
2157 $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params);
2158 my $sth = $dbh->prepare($query);
2159 $sth->execute(@query_params);
2161 while (my $data = $sth->fetchrow_hashref) {
2162 $data->{orderdate} = format_date($data->{orderdate});
2163 $data->{claimed_date} = format_date($data->{claimed_date});
2164 push @results, $data;
2169 #------------------------------------------------------------#
2173 (\@order_loop, $total_qty, $total_price, $total_qtyreceived) = GetHistory( %params );
2175 Retreives some acquisition history information
2183 basket - search both basket name and number
2184 booksellerinvoicenumber
2187 $order_loop is a list of hashrefs that each look like this:
2189 'author' => 'Twain, Mark',
2191 'biblionumber' => '215',
2193 'creationdate' => 'MM/DD/YYYY',
2194 'datereceived' => undef,
2197 'invoicenumber' => undef,
2199 'ordernumber' => '1',
2201 'quantityreceived' => undef,
2202 'title' => 'The Adventures of Huckleberry Finn'
2204 $total_qty is the sum of all of the quantities in $order_loop
2205 $total_price is the cost of each in $order_loop times the quantity
2206 $total_qtyreceived is the sum of all of the quantityreceived entries in $order_loop
2211 # don't run the query if there are no parameters (list would be too long for sure !)
2212 croak "No search params" unless @_;
2214 my $title = $params{title};
2215 my $author = $params{author};
2216 my $isbn = $params{isbn};
2217 my $ean = $params{ean};
2218 my $name = $params{name};
2219 my $from_placed_on = $params{from_placed_on};
2220 my $to_placed_on = $params{to_placed_on};
2221 my $basket = $params{basket};
2222 my $booksellerinvoicenumber = $params{booksellerinvoicenumber};
2223 my $basketgroupname = $params{basketgroupname};
2224 my $budget = $params{budget};
2225 my $orderstatus = $params{orderstatus};
2229 my $total_qtyreceived = 0;
2230 my $total_price = 0;
2232 my $dbh = C4::Context->dbh;
2235 COALESCE(biblio.title, deletedbiblio.title) AS title,
2236 COALESCE(biblio.author, deletedbiblio.author) AS author,
2237 COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn,
2238 COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean,
2240 aqbasket.basketname,
2241 aqbasket.basketgroupid,
2242 aqbasketgroups.name as groupname,
2244 aqbasket.creationdate,
2245 aqorders.datereceived,
2247 aqorders.quantityreceived,
2249 aqorders.ordernumber,
2251 aqinvoices.invoicenumber,
2252 aqbooksellers.id as id,
2253 aqorders.biblionumber,
2254 aqorders.orderstatus,
2255 aqorders.parent_ordernumber,
2256 aqbudgets.budget_name
2258 $query .= ", aqbudgets.budget_id AS budget" if defined $budget;
2261 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
2262 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
2263 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
2264 LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
2265 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
2266 LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id
2267 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2268 LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.biblionumber
2269 LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=aqorders.biblionumber
2272 if ( C4::Context->preference("IndependentBranches") ) {
2273 $query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber";
2276 $query .= " WHERE 1 ";
2278 $query .= " AND (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') " if $orderstatus ne 'cancelled';
2280 my @query_params = ();
2283 $query .= " AND biblio.title LIKE ? ";
2284 $title =~ s/\s+/%/g;
2285 push @query_params, "%$title%";
2289 $query .= " AND biblio.author LIKE ? ";
2290 push @query_params, "%$author%";
2294 $query .= " AND biblioitems.isbn LIKE ? ";
2295 push @query_params, "%$isbn%";
2298 $query .= " AND biblioitems.ean = ? ";
2299 push @query_params, "$ean";
2302 $query .= " AND aqbooksellers.name LIKE ? ";
2303 push @query_params, "%$name%";
2307 $query .= " AND aqbudgets.budget_id = ? ";
2308 push @query_params, "$budget";
2311 if ( $from_placed_on ) {
2312 $query .= " AND creationdate >= ? ";
2313 push @query_params, $from_placed_on;
2316 if ( $to_placed_on ) {
2317 $query .= " AND creationdate <= ? ";
2318 push @query_params, $to_placed_on;
2321 if ( defined $orderstatus and $orderstatus ne '') {
2322 $query .= " AND aqorders.orderstatus = ? ";
2323 push @query_params, "$orderstatus";
2327 if ($basket =~ m/^\d+$/) {
2328 $query .= " AND aqorders.basketno = ? ";
2329 push @query_params, $basket;
2331 $query .= " AND aqbasket.basketname LIKE ? ";
2332 push @query_params, "%$basket%";
2336 if ($booksellerinvoicenumber) {
2337 $query .= " AND aqinvoices.invoicenumber LIKE ? ";
2338 push @query_params, "%$booksellerinvoicenumber%";
2341 if ($basketgroupname) {
2342 $query .= " AND aqbasketgroups.name LIKE ? ";
2343 push @query_params, "%$basketgroupname%";
2346 if ( C4::Context->preference("IndependentBranches") ) {
2347 unless ( C4::Context->IsSuperLibrarian() ) {
2348 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
2349 push @query_params, C4::Context->userenv->{branch};
2352 $query .= " ORDER BY id";
2353 my $sth = $dbh->prepare($query);
2354 $sth->execute( @query_params );
2356 while ( my $line = $sth->fetchrow_hashref ) {
2357 $line->{count} = $cnt++;
2358 $line->{toggle} = 1 if $cnt % 2;
2359 push @order_loop, $line;
2360 $total_qty += ( $line->{quantity} ) ? $line->{quantity} : 0;
2361 $total_qtyreceived += ( $line->{quantityreceived} ) ? $line->{quantityreceived} : 0;
2362 $total_price += ( $line->{quantity} and $line->{ecost} ) ? $line->{quantity} * $line->{ecost} : 0;
2364 return \@order_loop, $total_qty, $total_price, $total_qtyreceived;
2367 =head2 GetRecentAcqui
2369 $results = GetRecentAcqui($days);
2371 C<$results> is a ref to a table which containts hashref
2375 sub GetRecentAcqui {
2377 my $dbh = C4::Context->dbh;
2381 ORDER BY timestamp DESC
2384 my $sth = $dbh->prepare($query);
2386 my $results = $sth->fetchall_arrayref({});
2392 $contractlist = &GetContracts($booksellerid, $activeonly);
2394 Looks up the contracts that belong to a bookseller
2396 Returns a list of contracts
2400 =item C<$booksellerid> is the "id" field in the "aqbooksellers" table.
2402 =item C<$activeonly> if exists get only contracts that are still active.
2409 my ( $booksellerid, $activeonly ) = @_;
2410 my $dbh = C4::Context->dbh;
2412 if (! $activeonly) {
2416 WHERE booksellerid=?
2421 WHERE booksellerid=?
2422 AND contractenddate >= CURDATE( )";
2425 $dbh->selectall_arrayref( $query, { Slice => {} }, $booksellerid );
2426 return @{$result_set};
2429 #------------------------------------------------------------#
2433 $contract = &GetContract($contractID);
2435 Looks up the contract that has PRIMKEY (contractnumber) value $contractID
2442 my ( $contractno ) = @_;
2443 my $dbh = C4::Context->dbh;
2447 WHERE contractnumber=?
2450 my $sth = $dbh->prepare($query);
2451 $sth->execute( $contractno );
2452 my $result = $sth->fetchrow_hashref;
2460 &AddClaim($ordernumber);
2462 Add a claim for an order
2469 my ($ordernumber) = @_;
2470 my $dbh = C4::Context->dbh;
2473 claims_count = claims_count + 1,
2474 claimed_date = CURDATE()
2475 WHERE ordernumber = ?
2477 my $sth = $dbh->prepare($query);
2478 $sth->execute($ordernumber);
2483 my @invoices = GetInvoices(
2484 invoicenumber => $invoicenumber,
2485 supplierid => $supplierid,
2486 suppliername => $suppliername,
2487 shipmentdatefrom => $shipmentdatefrom, # ISO format
2488 shipmentdateto => $shipmentdateto, # ISO format
2489 billingdatefrom => $billingdatefrom, # ISO format
2490 billingdateto => $billingdateto, # ISO format
2491 isbneanissn => $isbn_or_ean_or_issn,
2494 publisher => $publisher,
2495 publicationyear => $publicationyear,
2496 branchcode => $branchcode,
2497 order_by => $order_by
2500 Return a list of invoices that match all given criteria.
2502 $order_by is "column_name (asc|desc)", where column_name is any of
2503 'invoicenumber', 'booksellerid', 'shipmentdate', 'billingdate', 'closedate',
2504 'shipmentcost', 'shipmentcost_budgetid'.
2506 asc is the default if omitted
2513 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2514 closedate shipmentcost shipmentcost_budgetid);
2516 my $dbh = C4::Context->dbh;
2518 SELECT aqinvoices.*, aqbooksellers.name AS suppliername,
2521 aqorders.datereceived IS NOT NULL,
2522 aqorders.biblionumber,
2525 ) AS receivedbiblios,
2526 SUM(aqorders.quantityreceived) AS receiveditems
2528 LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid
2529 LEFT JOIN aqorders ON aqorders.invoiceid = aqinvoices.invoiceid
2530 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
2531 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2532 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2533 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
2534 LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber
2539 if($args{supplierid}) {
2540 push @bind_strs, " aqinvoices.booksellerid = ? ";
2541 push @bind_args, $args{supplierid};
2543 if($args{invoicenumber}) {
2544 push @bind_strs, " aqinvoices.invoicenumber LIKE ? ";
2545 push @bind_args, "%$args{invoicenumber}%";
2547 if($args{suppliername}) {
2548 push @bind_strs, " aqbooksellers.name LIKE ? ";
2549 push @bind_args, "%$args{suppliername}%";
2551 if($args{shipmentdatefrom}) {
2552 push @bind_strs, " aqinvoices.shipmentdate >= ? ";
2553 push @bind_args, $args{shipmentdatefrom};
2555 if($args{shipmentdateto}) {
2556 push @bind_strs, " aqinvoices.shipmentdate <= ? ";
2557 push @bind_args, $args{shipmentdateto};
2559 if($args{billingdatefrom}) {
2560 push @bind_strs, " aqinvoices.billingdate >= ? ";
2561 push @bind_args, $args{billingdatefrom};
2563 if($args{billingdateto}) {
2564 push @bind_strs, " aqinvoices.billingdate <= ? ";
2565 push @bind_args, $args{billingdateto};
2567 if($args{isbneanissn}) {
2568 push @bind_strs, " (biblioitems.isbn LIKE CONCAT('%', ?, '%') OR biblioitems.ean LIKE CONCAT('%', ?, '%') OR biblioitems.issn LIKE CONCAT('%', ?, '%') ) ";
2569 push @bind_args, $args{isbneanissn}, $args{isbneanissn}, $args{isbneanissn};
2572 push @bind_strs, " biblio.title LIKE CONCAT('%', ?, '%') ";
2573 push @bind_args, $args{title};
2576 push @bind_strs, " biblio.author LIKE CONCAT('%', ?, '%') ";
2577 push @bind_args, $args{author};
2579 if($args{publisher}) {
2580 push @bind_strs, " biblioitems.publishercode LIKE CONCAT('%', ?, '%') ";
2581 push @bind_args, $args{publisher};
2583 if($args{publicationyear}) {
2584 push @bind_strs, " ((biblioitems.publicationyear LIKE CONCAT('%', ?, '%')) OR (biblio.copyrightdate LIKE CONCAT('%', ?, '%'))) ";
2585 push @bind_args, $args{publicationyear}, $args{publicationyear};
2587 if($args{branchcode}) {
2588 push @bind_strs, " borrowers.branchcode = ? ";
2589 push @bind_args, $args{branchcode};
2592 $query .= " WHERE " . join(" AND ", @bind_strs) if @bind_strs;
2593 $query .= " GROUP BY aqinvoices.invoiceid ";
2595 if($args{order_by}) {
2596 my ($column, $direction) = split / /, $args{order_by};
2597 if(grep /^$column$/, @columns) {
2598 $direction ||= 'ASC';
2599 $query .= " ORDER BY $column $direction";
2603 my $sth = $dbh->prepare($query);
2604 $sth->execute(@bind_args);
2606 my $results = $sth->fetchall_arrayref({});
2612 my $invoice = GetInvoice($invoiceid);
2614 Get informations about invoice with given $invoiceid
2616 Return a hash filled with aqinvoices.* fields
2621 my ($invoiceid) = @_;
2624 return unless $invoiceid;
2626 my $dbh = C4::Context->dbh;
2632 my $sth = $dbh->prepare($query);
2633 $sth->execute($invoiceid);
2635 $invoice = $sth->fetchrow_hashref;
2639 =head3 GetInvoiceDetails
2641 my $invoice = GetInvoiceDetails($invoiceid)
2643 Return informations about an invoice + the list of related order lines
2645 Orders informations are in $invoice->{orders} (array ref)
2649 sub GetInvoiceDetails {
2650 my ($invoiceid) = @_;
2652 if ( !defined $invoiceid ) {
2653 carp 'GetInvoiceDetails called without an invoiceid';
2657 my $dbh = C4::Context->dbh;
2659 SELECT aqinvoices.*, aqbooksellers.name AS suppliername
2661 LEFT JOIN aqbooksellers ON aqinvoices.booksellerid = aqbooksellers.id
2664 my $sth = $dbh->prepare($query);
2665 $sth->execute($invoiceid);
2667 my $invoice = $sth->fetchrow_hashref;
2670 SELECT aqorders.*, biblio.*, aqbasket.basketname
2672 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
2673 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2676 $sth = $dbh->prepare($query);
2677 $sth->execute($invoiceid);
2678 $invoice->{orders} = $sth->fetchall_arrayref({});
2679 $invoice->{orders} ||= []; # force an empty arrayref if fetchall_arrayref fails
2686 my $invoiceid = AddInvoice(
2687 invoicenumber => $invoicenumber,
2688 booksellerid => $booksellerid,
2689 shipmentdate => $shipmentdate,
2690 billingdate => $billingdate,
2691 closedate => $closedate,
2692 shipmentcost => $shipmentcost,
2693 shipmentcost_budgetid => $shipmentcost_budgetid
2696 Create a new invoice and return its id or undef if it fails.
2703 return unless(%invoice and $invoice{invoicenumber});
2705 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2706 closedate shipmentcost shipmentcost_budgetid);
2710 foreach my $key (keys %invoice) {
2711 if(0 < grep(/^$key$/, @columns)) {
2712 push @set_strs, "$key = ?";
2713 push @set_args, ($invoice{$key} || undef);
2719 my $dbh = C4::Context->dbh;
2720 my $query = "INSERT INTO aqinvoices SET ";
2721 $query .= join (",", @set_strs);
2722 my $sth = $dbh->prepare($query);
2723 $rv = $sth->execute(@set_args);
2725 $rv = $dbh->last_insert_id(undef, undef, 'aqinvoices', undef);
2734 invoiceid => $invoiceid, # Mandatory
2735 invoicenumber => $invoicenumber,
2736 booksellerid => $booksellerid,
2737 shipmentdate => $shipmentdate,
2738 billingdate => $billingdate,
2739 closedate => $closedate,
2740 shipmentcost => $shipmentcost,
2741 shipmentcost_budgetid => $shipmentcost_budgetid
2744 Modify an invoice, invoiceid is mandatory.
2746 Return undef if it fails.
2753 return unless(%invoice and $invoice{invoiceid});
2755 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2756 closedate shipmentcost shipmentcost_budgetid);
2760 foreach my $key (keys %invoice) {
2761 if(0 < grep(/^$key$/, @columns)) {
2762 push @set_strs, "$key = ?";
2763 push @set_args, ($invoice{$key} || undef);
2767 my $dbh = C4::Context->dbh;
2768 my $query = "UPDATE aqinvoices SET ";
2769 $query .= join(",", @set_strs);
2770 $query .= " WHERE invoiceid = ?";
2772 my $sth = $dbh->prepare($query);
2773 $sth->execute(@set_args, $invoice{invoiceid});
2778 CloseInvoice($invoiceid);
2782 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => undef);
2787 my ($invoiceid) = @_;
2789 return unless $invoiceid;
2791 my $dbh = C4::Context->dbh;
2794 SET closedate = CAST(NOW() AS DATE)
2797 my $sth = $dbh->prepare($query);
2798 $sth->execute($invoiceid);
2801 =head3 ReopenInvoice
2803 ReopenInvoice($invoiceid);
2807 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => C4::Dates->new()->output('iso'))
2812 my ($invoiceid) = @_;
2814 return unless $invoiceid;
2816 my $dbh = C4::Context->dbh;
2819 SET closedate = NULL
2822 my $sth = $dbh->prepare($query);
2823 $sth->execute($invoiceid);
2828 DelInvoice($invoiceid);
2830 Delete an invoice if there are no items attached to it.
2835 my ($invoiceid) = @_;
2837 return unless $invoiceid;
2839 my $dbh = C4::Context->dbh;
2845 my $sth = $dbh->prepare($query);
2846 $sth->execute($invoiceid);
2847 my $res = $sth->fetchrow_arrayref;
2848 if ( $res && $res->[0] == 0 ) {
2850 DELETE FROM aqinvoices
2853 my $sth = $dbh->prepare($query);
2854 return ( $sth->execute($invoiceid) > 0 );
2859 =head3 MergeInvoices
2861 MergeInvoices($invoiceid, \@sourceids);
2863 Merge the invoices identified by the IDs in \@sourceids into
2864 the invoice identified by $invoiceid.
2869 my ($invoiceid, $sourceids) = @_;
2871 return unless $invoiceid;
2872 foreach my $sourceid (@$sourceids) {
2873 next if $sourceid == $invoiceid;
2874 my $source = GetInvoiceDetails($sourceid);
2875 foreach my $order (@{$source->{'orders'}}) {
2876 $order->{'invoiceid'} = $invoiceid;
2879 DelInvoice($source->{'invoiceid'});
2884 =head3 GetBiblioCountByBasketno
2886 $biblio_count = &GetBiblioCountByBasketno($basketno);
2888 Looks up the biblio's count that has basketno value $basketno
2894 sub GetBiblioCountByBasketno {
2895 my ($basketno) = @_;
2896 my $dbh = C4::Context->dbh;
2898 SELECT COUNT( DISTINCT( biblionumber ) )
2901 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
2904 my $sth = $dbh->prepare($query);
2905 $sth->execute($basketno);
2906 return $sth->fetchrow;
2914 Koha Development Team <http://koha-community.org/>