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,
638 COUNT(DISTINCT aqorders.biblionumber) AS total_biblios,
640 IF(aqorders.datereceived IS NULL
641 AND aqorders.datecancellationprinted IS NULL
646 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
647 WHERE booksellerid = ?};
649 $query.=" AND (closedate IS NULL OR (aqorders.quantity > aqorders.quantityreceived AND datecancellationprinted IS NULL))";
651 $query.=" GROUP BY aqbasket.basketno";
653 my $sth = $dbh->prepare($query);
654 $sth->execute($supplierid);
655 return $sth->fetchall_arrayref({});
658 =head3 GetBasketUsers
660 $basketusers_ids = &GetBasketUsers($basketno);
662 Returns a list of all borrowernumbers that are in basket users list
667 my $basketno = shift;
669 return unless $basketno;
672 SELECT borrowernumber
676 my $dbh = C4::Context->dbh;
677 my $sth = $dbh->prepare($query);
678 $sth->execute($basketno);
679 my $results = $sth->fetchall_arrayref( {} );
682 foreach (@$results) {
683 push @borrowernumbers, $_->{'borrowernumber'};
686 return @borrowernumbers;
689 =head3 ModBasketUsers
691 my @basketusers_ids = (1, 2, 3);
692 &ModBasketUsers($basketno, @basketusers_ids);
694 Delete all users from basket users list, and add users in C<@basketusers_ids>
700 my ($basketno, @basketusers_ids) = @_;
702 return unless $basketno;
704 my $dbh = C4::Context->dbh;
706 DELETE FROM aqbasketusers
709 my $sth = $dbh->prepare($query);
710 $sth->execute($basketno);
713 INSERT INTO aqbasketusers (basketno, borrowernumber)
716 $sth = $dbh->prepare($query);
717 foreach my $basketuser_id (@basketusers_ids) {
718 $sth->execute($basketno, $basketuser_id);
723 =head3 CanUserManageBasket
725 my $bool = CanUserManageBasket($borrower, $basket[, $userflags]);
726 my $bool = CanUserManageBasket($borrowernumber, $basketno[, $userflags]);
728 Check if a borrower can manage a basket, according to system preference
729 AcqViewBaskets, user permissions and basket properties (creator, users list,
732 First parameter can be either a borrowernumber or a hashref as returned by
733 C4::Members::GetMember.
735 Second parameter can be either a basketno or a hashref as returned by
736 C4::Acquisition::GetBasket.
738 The third parameter is optional. If given, it should be a hashref as returned
739 by C4::Auth::getuserflags. If not, getuserflags is called.
741 If user is authorised to manage basket, returns 1.
746 sub CanUserManageBasket {
747 my ($borrower, $basket, $userflags) = @_;
749 if (!ref $borrower) {
750 $borrower = C4::Members::GetMember(borrowernumber => $borrower);
753 $basket = GetBasket($basket);
756 return 0 unless ($basket and $borrower);
758 my $borrowernumber = $borrower->{borrowernumber};
759 my $basketno = $basket->{basketno};
761 my $AcqViewBaskets = C4::Context->preference('AcqViewBaskets');
763 if (!defined $userflags) {
764 my $dbh = C4::Context->dbh;
765 my $sth = $dbh->prepare("SELECT flags FROM borrowers WHERE borrowernumber = ?");
766 $sth->execute($borrowernumber);
767 my ($flags) = $sth->fetchrow_array;
770 $userflags = C4::Auth::getuserflags($flags, $borrower->{userid}, $dbh);
773 unless ($userflags->{superlibrarian}
774 || (ref $userflags->{acquisition} && $userflags->{acquisition}->{order_manage_all})
775 || (!ref $userflags->{acquisition} && $userflags->{acquisition}))
777 if (not exists $userflags->{acquisition}) {
781 if ( (ref $userflags->{acquisition} && !$userflags->{acquisition}->{order_manage})
782 || (!ref $userflags->{acquisition} && !$userflags->{acquisition}) ) {
786 if ($AcqViewBaskets eq 'user'
787 && $basket->{authorisedby} != $borrowernumber
788 && grep($borrowernumber, GetBasketUsers($basketno)) == 0) {
792 if ($AcqViewBaskets eq 'branch' && defined $basket->{branch}
793 && $basket->{branch} ne $borrower->{branchcode}) {
801 #------------------------------------------------------------#
803 =head3 GetBasketsByBasketgroup
805 $baskets = &GetBasketsByBasketgroup($basketgroupid);
807 Returns a reference to all baskets that belong to basketgroup $basketgroupid.
811 sub GetBasketsByBasketgroup {
812 my $basketgroupid = shift;
814 SELECT *, aqbasket.booksellerid as booksellerid
816 LEFT JOIN aqcontract USING(contractnumber) WHERE basketgroupid=?
818 my $dbh = C4::Context->dbh;
819 my $sth = $dbh->prepare($query);
820 $sth->execute($basketgroupid);
821 return $sth->fetchall_arrayref({});
824 #------------------------------------------------------------#
826 =head3 NewBasketgroup
828 $basketgroupid = NewBasketgroup(\%hashref);
830 Adds a basketgroup to the aqbasketgroups table, and add the initial baskets to it.
832 $hashref->{'booksellerid'} is the 'id' field of the bookseller in the aqbooksellers table,
834 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
836 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
838 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
840 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
842 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
844 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
846 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
851 my $basketgroupinfo = shift;
852 die "booksellerid is required to create a basketgroup" unless $basketgroupinfo->{'booksellerid'};
853 my $query = "INSERT INTO aqbasketgroups (";
855 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
856 if ( defined $basketgroupinfo->{$field} ) {
857 $query .= "$field, ";
858 push(@params, $basketgroupinfo->{$field});
861 $query .= "booksellerid) VALUES (";
866 push(@params, $basketgroupinfo->{'booksellerid'});
867 my $dbh = C4::Context->dbh;
868 my $sth = $dbh->prepare($query);
869 $sth->execute(@params);
870 my $basketgroupid = $dbh->{'mysql_insertid'};
871 if( $basketgroupinfo->{'basketlist'} ) {
872 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
873 my $query2 = "UPDATE aqbasket SET basketgroupid=? WHERE basketno=?";
874 my $sth2 = $dbh->prepare($query2);
875 $sth2->execute($basketgroupid, $basketno);
878 return $basketgroupid;
881 #------------------------------------------------------------#
883 =head3 ModBasketgroup
885 ModBasketgroup(\%hashref);
887 Modifies a basketgroup in the aqbasketgroups table, and add the baskets to it.
889 $hashref->{'id'} is the 'id' field of the basketgroup in the aqbasketgroup table, this parameter is mandatory,
891 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
893 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
895 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
897 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
899 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
901 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
903 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
908 my $basketgroupinfo = shift;
909 die "basketgroup id is required to edit a basketgroup" unless $basketgroupinfo->{'id'};
910 my $dbh = C4::Context->dbh;
911 my $query = "UPDATE aqbasketgroups SET ";
913 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
914 if ( defined $basketgroupinfo->{$field} ) {
915 $query .= "$field=?, ";
916 push(@params, $basketgroupinfo->{$field});
921 $query .= " WHERE id=?";
922 push(@params, $basketgroupinfo->{'id'});
923 my $sth = $dbh->prepare($query);
924 $sth->execute(@params);
926 $sth = $dbh->prepare('UPDATE aqbasket SET basketgroupid = NULL WHERE basketgroupid = ?');
927 $sth->execute($basketgroupinfo->{'id'});
929 if($basketgroupinfo->{'basketlist'} && @{$basketgroupinfo->{'basketlist'}}){
930 $sth = $dbh->prepare("UPDATE aqbasket SET basketgroupid=? WHERE basketno=?");
931 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
932 $sth->execute($basketgroupinfo->{'id'}, $basketno);
938 #------------------------------------------------------------#
940 =head3 DelBasketgroup
942 DelBasketgroup($basketgroupid);
944 Deletes a basketgroup in the aqbasketgroups table, and removes the reference to it from the baskets,
948 =item C<$basketgroupid> is the 'id' field of the basket in the aqbasketgroup table
955 my $basketgroupid = shift;
956 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
957 my $query = "DELETE FROM aqbasketgroups WHERE id=?";
958 my $dbh = C4::Context->dbh;
959 my $sth = $dbh->prepare($query);
960 $sth->execute($basketgroupid);
964 #------------------------------------------------------------#
967 =head2 FUNCTIONS ABOUT ORDERS
969 =head3 GetBasketgroup
971 $basketgroup = &GetBasketgroup($basketgroupid);
973 Returns a reference to the hash containing all information about the basketgroup.
978 my $basketgroupid = shift;
979 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
980 my $dbh = C4::Context->dbh;
981 my $result_set = $dbh->selectall_arrayref(
982 'SELECT * FROM aqbasketgroups WHERE id=?',
986 return $result_set->[0]; # id is unique
989 #------------------------------------------------------------#
991 =head3 GetBasketgroups
993 $basketgroups = &GetBasketgroups($booksellerid);
995 Returns a reference to the array of all the basketgroups of bookseller $booksellerid.
999 sub GetBasketgroups {
1000 my $booksellerid = shift;
1001 die 'bookseller id is required to edit a basketgroup' unless $booksellerid;
1002 my $query = 'SELECT * FROM aqbasketgroups WHERE booksellerid=? ORDER BY id DESC';
1003 my $dbh = C4::Context->dbh;
1004 my $sth = $dbh->prepare($query);
1005 $sth->execute($booksellerid);
1006 return $sth->fetchall_arrayref({});
1009 #------------------------------------------------------------#
1011 =head2 FUNCTIONS ABOUT ORDERS
1015 @orders = &GetOrders($basketnumber, $orderby);
1017 Looks up the pending (non-cancelled) orders with the given basket
1018 number. If C<$booksellerID> is non-empty, only orders from that seller
1022 C<&basket> returns a two-element array. C<@orders> is an array of
1023 references-to-hash, whose keys are the fields from the aqorders,
1024 biblio, and biblioitems tables in the Koha database.
1029 my ( $basketno, $orderby ) = @_;
1030 return () unless $basketno;
1031 my $dbh = C4::Context->dbh;
1033 SELECT biblio.*,biblioitems.*,
1037 aqorders_transfers.ordernumber_from AS transferred_from,
1038 aqorders_transfers.timestamp AS transferred_from_timestamp
1040 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1041 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1042 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1043 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1045 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
1048 $orderby = "biblioitems.publishercode,biblio.title" unless $orderby;
1049 $query .= " ORDER BY $orderby";
1051 $dbh->selectall_arrayref( $query, { Slice => {} }, $basketno );
1052 return @{$result_set};
1056 #------------------------------------------------------------#
1057 =head3 GetOrdersByBiblionumber
1059 @orders = &GetOrdersByBiblionumber($biblionumber);
1061 Looks up the orders with linked to a specific $biblionumber, including
1062 cancelled orders and received orders.
1065 C<@orders> is an array of references-to-hash, whose keys are the
1066 fields from the aqorders, biblio, and biblioitems tables in the Koha database.
1070 sub GetOrdersByBiblionumber {
1071 my $biblionumber = shift;
1072 return unless $biblionumber;
1073 my $dbh = C4::Context->dbh;
1075 SELECT biblio.*,biblioitems.*,
1079 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1080 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1081 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1082 WHERE aqorders.biblionumber=?
1085 $dbh->selectall_arrayref( $query, { Slice => {} }, $biblionumber );
1086 return @{$result_set};
1090 #------------------------------------------------------------#
1094 $order = &GetOrder($ordernumber);
1096 Looks up an order by order number.
1098 Returns a reference-to-hash describing the order. The keys of
1099 C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database.
1104 my ($ordernumber) = @_;
1105 my $dbh = C4::Context->dbh;
1106 my $query = qq{SELECT
1110 aqbasket.basketname,
1111 borrowers.branchcode,
1112 biblioitems.publicationyear,
1113 biblio.copyrightdate,
1114 biblioitems.editionstatement,
1118 biblioitems.publishercode,
1119 aqorders.rrp AS unitpricesupplier,
1120 aqorders.ecost AS unitpricelib,
1121 aqorders.claims_count AS claims_count,
1122 aqorders.claimed_date AS claimed_date,
1123 aqbudgets.budget_name AS budget,
1124 aqbooksellers.name AS supplier,
1125 aqbooksellers.id AS supplierid,
1126 biblioitems.publishercode AS publisher,
1127 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
1128 DATE(aqbasket.closedate) AS orderdate,
1129 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity_to_receive,
1130 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1131 DATEDIFF(CURDATE( ),closedate) AS latesince
1132 FROM aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1133 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1134 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
1135 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
1136 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1137 WHERE aqorders.basketno = aqbasket.basketno
1140 $dbh->selectall_arrayref( $query, { Slice => {} }, $ordernumber );
1142 # result_set assumed to contain 1 match
1143 return $result_set->[0];
1146 =head3 GetLastOrderNotReceivedFromSubscriptionid
1148 $order = &GetLastOrderNotReceivedFromSubscriptionid($subscriptionid);
1150 Returns a reference-to-hash describing the last order not received for a subscription.
1154 sub GetLastOrderNotReceivedFromSubscriptionid {
1155 my ( $subscriptionid ) = @_;
1156 my $dbh = C4::Context->dbh;
1158 SELECT * FROM aqorders
1159 LEFT JOIN subscription
1160 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1161 WHERE aqorders.subscriptionid = ?
1162 AND aqorders.datereceived IS NULL
1166 $dbh->selectall_arrayref( $query, { Slice => {} }, $subscriptionid );
1168 # result_set assumed to contain 1 match
1169 return $result_set->[0];
1172 =head3 GetLastOrderReceivedFromSubscriptionid
1174 $order = &GetLastOrderReceivedFromSubscriptionid($subscriptionid);
1176 Returns a reference-to-hash describing the last order received for a subscription.
1180 sub GetLastOrderReceivedFromSubscriptionid {
1181 my ( $subscriptionid ) = @_;
1182 my $dbh = C4::Context->dbh;
1184 SELECT * FROM aqorders
1185 LEFT JOIN subscription
1186 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1187 WHERE aqorders.subscriptionid = ?
1188 AND aqorders.datereceived =
1190 SELECT MAX( aqorders.datereceived )
1192 LEFT JOIN subscription
1193 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1194 WHERE aqorders.subscriptionid = ?
1195 AND aqorders.datereceived IS NOT NULL
1197 ORDER BY ordernumber DESC
1201 $dbh->selectall_arrayref( $query, { Slice => {} }, $subscriptionid, $subscriptionid );
1203 # result_set assumed to contain 1 match
1204 return $result_set->[0];
1209 #------------------------------------------------------------#
1213 &NewOrder(\%hashref);
1215 Adds a new order to the database. Any argument that isn't described
1216 below is the new value of the field with the same name in the aqorders
1217 table of the Koha database.
1221 =item $hashref->{'basketno'} is the basketno foreign key in aqorders, it is mandatory
1223 =item $hashref->{'ordernumber'} is a "minimum order number."
1225 =item $hashref->{'budgetdate'} is effectively ignored.
1226 If it's undef (anything false) or the string 'now', the current day is used.
1227 Else, the upcoming July 1st is used.
1229 =item $hashref->{'subscription'} may be either "yes", or anything else for "no".
1231 =item $hashref->{'uncertainprice'} may be 0 for "the price is known" or 1 for "the price is uncertain"
1233 =item defaults entrydate to Now
1235 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".
1242 my $orderinfo = shift;
1244 my $dbh = C4::Context->dbh;
1248 # if these parameters are missing, we can't continue
1249 for my $key (qw/basketno quantity biblionumber budget_id/) {
1250 croak "Mandatory parameter $key missing" unless $orderinfo->{$key};
1253 if ( defined $orderinfo->{subscription} && $orderinfo->{'subscription'} eq 'yes' ) {
1254 $orderinfo->{'subscription'} = 1;
1256 $orderinfo->{'subscription'} = 0;
1258 $orderinfo->{'entrydate'} ||= C4::Dates->new()->output("iso");
1259 if (!$orderinfo->{quantityreceived}) {
1260 $orderinfo->{quantityreceived} = 0;
1263 my $ordernumber=InsertInTable("aqorders",$orderinfo);
1264 if (not $orderinfo->{parent_ordernumber}) {
1265 my $sth = $dbh->prepare("
1267 SET parent_ordernumber = ordernumber
1268 WHERE ordernumber = ?
1270 $sth->execute($ordernumber);
1272 return ( $orderinfo->{'basketno'}, $ordernumber );
1277 #------------------------------------------------------------#
1286 my ($itemnumber, $ordernumber) = @_;
1287 my $dbh = C4::Context->dbh;
1289 INSERT INTO aqorders_items
1290 (itemnumber, ordernumber)
1293 my $sth = $dbh->prepare($query);
1294 $sth->execute( $itemnumber, $ordernumber);
1297 #------------------------------------------------------------#
1301 &ModOrder(\%hashref);
1303 Modifies an existing order. Updates the order with order number
1304 $hashref->{'ordernumber'} and biblionumber $hashref->{'biblionumber'}. All
1305 other keys of the hash update the fields with the same name in the aqorders
1306 table of the Koha database.
1311 my $orderinfo = shift;
1313 die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq '' ;
1314 die "Biblionumber is required" if $orderinfo->{'biblionumber'} eq '';
1316 my $dbh = C4::Context->dbh;
1319 # update uncertainprice to an integer, just in case (under FF, checked boxes have the value "ON" by default)
1320 $orderinfo->{uncertainprice}=1 if $orderinfo->{uncertainprice};
1322 # delete($orderinfo->{'branchcode'});
1323 # the hash contains a lot of entries not in aqorders, so get the columns ...
1324 my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;");
1326 my $colnames = $sth->{NAME};
1327 #FIXME Be careful. If aqorders would have columns with diacritics,
1328 #you should need to decode what you get back from NAME.
1329 #See report 10110 and guided_reports.pl
1330 my $query = "UPDATE aqorders SET ";
1332 foreach my $orderinfokey (grep(!/ordernumber/, keys %$orderinfo)){
1333 # ... and skip hash entries that are not in the aqorders table
1334 # FIXME : probably not the best way to do it (would be better to have a correct hash)
1335 next unless grep(/^$orderinfokey$/, @$colnames);
1336 $query .= "$orderinfokey=?, ";
1337 push(@params, $orderinfo->{$orderinfokey});
1340 $query .= "timestamp=NOW() WHERE ordernumber=?";
1341 push(@params, $orderinfo->{'ordernumber'} );
1342 $sth = $dbh->prepare($query);
1343 $sth->execute(@params);
1347 #------------------------------------------------------------#
1351 ModItemOrder($itemnumber, $ordernumber);
1353 Modifies the ordernumber of an item in aqorders_items.
1358 my ($itemnumber, $ordernumber) = @_;
1360 return unless ($itemnumber and $ordernumber);
1362 my $dbh = C4::Context->dbh;
1364 UPDATE aqorders_items
1366 WHERE itemnumber = ?
1368 my $sth = $dbh->prepare($query);
1369 return $sth->execute($ordernumber, $itemnumber);
1372 #------------------------------------------------------------#
1374 =head3 GetCancelledOrders
1376 my @orders = GetCancelledOrders($basketno, $orderby);
1378 Returns cancelled orders for a basket
1382 sub GetCancelledOrders {
1383 my ( $basketno, $orderby ) = @_;
1385 return () unless $basketno;
1387 my $dbh = C4::Context->dbh;
1394 aqorders_transfers.ordernumber_to AS transferred_to,
1395 aqorders_transfers.timestamp AS transferred_to_timestamp
1397 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1398 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1399 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1400 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber
1402 AND (datecancellationprinted IS NOT NULL
1403 AND datecancellationprinted <> '0000-00-00')
1406 $orderby = "aqorders.datecancellationprinted desc, aqorders.timestamp desc"
1408 $query .= " ORDER BY $orderby";
1409 my $sth = $dbh->prepare($query);
1410 $sth->execute($basketno);
1411 my $results = $sth->fetchall_arrayref( {} );
1417 #------------------------------------------------------------#
1419 =head3 ModReceiveOrder
1421 &ModReceiveOrder($biblionumber, $ordernumber, $quantityreceived, $user,
1422 $cost, $ecost, $invoiceid, rrp, budget_id, datereceived, \@received_itemnumbers);
1424 Updates an order, to reflect the fact that it was received, at least
1425 in part. All arguments not mentioned below update the fields with the
1426 same name in the aqorders table of the Koha database.
1428 If a partial order is received, splits the order into two.
1430 Updates the order with bibilionumber C<$biblionumber> and ordernumber
1436 sub ModReceiveOrder {
1438 $biblionumber, $ordernumber, $quantrec, $user, $cost, $ecost,
1439 $invoiceid, $rrp, $budget_id, $datereceived, $received_items
1443 my $dbh = C4::Context->dbh;
1444 $datereceived = C4::Dates->output('iso') unless $datereceived;
1445 my $suggestionid = GetSuggestionFromBiblionumber( $biblionumber );
1446 if ($suggestionid) {
1447 ModSuggestion( {suggestionid=>$suggestionid,
1448 STATUS=>'AVAILABLE',
1449 biblionumber=> $biblionumber}
1453 my $result_set = $dbh->selectall_arrayref(
1454 q{SELECT * FROM aqorders WHERE biblionumber=? AND aqorders.ordernumber=?},
1455 { Slice => {} }, $biblionumber, $ordernumber
1458 # we assume we have a unique order
1459 my $order = $result_set->[0];
1461 my $new_ordernumber = $ordernumber;
1462 if ( $order->{quantity} > $quantrec ) {
1463 # Split order line in two parts: the first is the original order line
1464 # without received items (the quantity is decreased),
1465 # the second part is a new order line with quantity=quantityrec
1466 # (entirely received)
1467 my $sth=$dbh->prepare("
1470 orderstatus = 'partial'
1471 WHERE ordernumber = ?
1474 $sth->execute($order->{quantity} - $quantrec, $ordernumber);
1476 delete $order->{'ordernumber'};
1477 $order->{'budget_id'} = ( $budget_id || $order->{'budget_id'} );
1478 $order->{'quantity'} = $quantrec;
1479 $order->{'quantityreceived'} = $quantrec;
1480 $order->{'datereceived'} = $datereceived;
1481 $order->{'invoiceid'} = $invoiceid;
1482 $order->{'unitprice'} = $cost;
1483 $order->{'rrp'} = $rrp;
1484 $order->{ecost} = $ecost;
1485 $order->{'orderstatus'} = 'complete';
1487 ( $basketno, $new_ordernumber ) = NewOrder($order);
1489 if ($received_items) {
1490 foreach my $itemnumber (@$received_items) {
1491 ModItemOrder($itemnumber, $new_ordernumber);
1495 my $sth=$dbh->prepare("update aqorders
1496 set quantityreceived=?,datereceived=?,invoiceid=?,
1497 unitprice=?,rrp=?,ecost=?,budget_id=?,orderstatus='complete'
1498 where biblionumber=? and ordernumber=?");
1499 $sth->execute($quantrec,$datereceived,$invoiceid,$cost,$rrp,$ecost,$budget_id,$biblionumber,$ordernumber);
1501 return ($datereceived, $new_ordernumber);
1504 =head3 CancelReceipt
1506 my $parent_ordernumber = CancelReceipt($ordernumber);
1508 Cancel an order line receipt and update the parent order line, as if no
1510 If items are created at receipt (AcqCreateItem = receiving) then delete
1516 my $ordernumber = shift;
1518 return unless $ordernumber;
1520 my $dbh = C4::Context->dbh;
1522 SELECT datereceived, parent_ordernumber, quantity
1524 WHERE ordernumber = ?
1526 my $sth = $dbh->prepare($query);
1527 $sth->execute($ordernumber);
1528 my $order = $sth->fetchrow_hashref;
1530 warn "CancelReceipt: order $ordernumber does not exist";
1533 unless($order->{'datereceived'}) {
1534 warn "CancelReceipt: order $ordernumber is not received";
1538 my $parent_ordernumber = $order->{'parent_ordernumber'};
1540 if($parent_ordernumber == $ordernumber || not $parent_ordernumber) {
1541 # The order line has no parent, just mark it as not received
1544 SET quantityreceived = ?,
1547 orderstatus = 'ordered'
1548 WHERE ordernumber = ?
1550 $sth = $dbh->prepare($query);
1551 $sth->execute(0, undef, undef, $ordernumber);
1553 # The order line has a parent, increase parent quantity and delete
1556 SELECT quantity, datereceived
1558 WHERE ordernumber = ?
1560 $sth = $dbh->prepare($query);
1561 $sth->execute($parent_ordernumber);
1562 my $parent_order = $sth->fetchrow_hashref;
1563 unless($parent_order) {
1564 warn "Parent order $parent_ordernumber does not exist.";
1567 if($parent_order->{'datereceived'}) {
1568 warn "CancelReceipt: parent order is received.".
1569 " Can't cancel receipt.";
1575 orderstatus = 'ordered'
1576 WHERE ordernumber = ?
1578 $sth = $dbh->prepare($query);
1579 my $rv = $sth->execute(
1580 $order->{'quantity'} + $parent_order->{'quantity'},
1584 warn "Cannot update parent order line, so do not cancel".
1588 if(C4::Context->preference('AcqCreateItem') eq 'receiving') {
1589 # Remove items that were created at receipt
1591 DELETE FROM items, aqorders_items
1592 USING items, aqorders_items
1593 WHERE items.itemnumber = ? AND aqorders_items.itemnumber = ?
1595 $sth = $dbh->prepare($query);
1596 my @itemnumbers = GetItemnumbersFromOrder($ordernumber);
1597 foreach my $itemnumber (@itemnumbers) {
1598 $sth->execute($itemnumber, $itemnumber);
1602 my @itemnumbers = GetItemnumbersFromOrder($ordernumber);
1603 foreach my $itemnumber (@itemnumbers) {
1604 ModItemOrder($itemnumber, $parent_ordernumber);
1609 DELETE FROM aqorders
1610 WHERE ordernumber = ?
1612 $sth = $dbh->prepare($query);
1613 $sth->execute($ordernumber);
1617 return $parent_ordernumber;
1620 #------------------------------------------------------------#
1624 @results = &SearchOrders({
1625 ordernumber => $ordernumber,
1627 biblionumber => $biblionumber,
1629 booksellerid => $booksellerid,
1630 basketno => $basketno,
1635 Searches for orders.
1637 C<$owner> Finds order for the logged in user.
1638 C<$pending> Finds pending orders. Ignores completed and cancelled orders.
1641 C<@results> is an array of references-to-hash with the keys are fields
1642 from aqorders, biblio, biblioitems and aqbasket tables.
1647 my ( $params ) = @_;
1648 my $ordernumber = $params->{ordernumber};
1649 my $search = $params->{search};
1650 my $ean = $params->{ean};
1651 my $booksellerid = $params->{booksellerid};
1652 my $basketno = $params->{basketno};
1653 my $basketname = $params->{basketname};
1654 my $basketgroupname = $params->{basketgroupname};
1655 my $owner = $params->{owner};
1656 my $pending = $params->{pending};
1657 my $ordered = $params->{ordered};
1659 my $dbh = C4::Context->dbh;
1662 SELECT aqbasket.basketno,
1664 borrowers.firstname,
1667 biblioitems.biblioitemnumber,
1668 aqbasket.authorisedby,
1670 aqbasket.creationdate,
1671 aqbasket.basketname,
1672 aqbasketgroups.id as basketgroupid,
1673 aqbasketgroups.name as basketgroupname,
1676 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
1677 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
1678 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1679 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1680 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
1681 WHERE (datecancellationprinted is NULL)
1685 $query .= q{ AND (quantity > quantityreceived OR quantityreceived is NULL)};
1688 $query .= q{ AND (quantity > quantityreceived OR quantityreceived is NULL)}
1690 $query .= q{ AND aqorders.orderstatus IN ( "ordered", "partial" )};
1693 my $userenv = C4::Context->userenv;
1694 if ( C4::Context->preference("IndependentBranches") ) {
1695 unless ( C4::Context->IsSuperLibrarian() ) {
1698 borrowers.branchcode = ?
1699 OR borrowers.branchcode = ''
1702 push @args, $userenv->{branch};
1706 if ( $ordernumber ) {
1707 $query .= ' AND (aqorders.ordernumber=?)';
1708 push @args, $ordernumber;
1711 $query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)';
1712 push @args, ("%$search%","%$search%","%$search%");
1715 $query .= ' AND biblioitems.ean = ?';
1718 if ( $booksellerid ) {
1719 $query .= 'AND aqbasket.booksellerid = ?';
1720 push @args, $booksellerid;
1723 $query .= 'AND aqbasket.basketno = ?';
1724 push @args, $basketno;
1727 $query .= 'AND aqbasket.basketname LIKE ?';
1728 push @args, "%$basketname%";
1730 if( $basketgroupname ) {
1731 $query .= ' AND aqbasketgroups.name LIKE ?';
1732 push @args, "%$basketgroupname%";
1736 $query .= ' AND aqbasket.authorisedby=? ';
1737 push @args, $userenv->{'number'};
1740 $query .= ' ORDER BY aqbasket.basketno';
1742 my $sth = $dbh->prepare($query);
1743 $sth->execute(@args);
1744 return $sth->fetchall_arrayref({});
1747 #------------------------------------------------------------#
1751 &DelOrder($biblionumber, $ordernumber);
1753 Cancel the order with the given order and biblio numbers. It does not
1754 delete any entries in the aqorders table, it merely marks them as
1760 my ( $bibnum, $ordernumber ) = @_;
1761 my $dbh = C4::Context->dbh;
1764 SET datecancellationprinted=now(), orderstatus='cancelled'
1765 WHERE biblionumber=? AND ordernumber=?
1767 my $sth = $dbh->prepare($query);
1768 $sth->execute( $bibnum, $ordernumber );
1769 my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1770 foreach my $itemnumber (@itemnumbers){
1771 C4::Items::DelItem( $dbh, $bibnum, $itemnumber );
1776 =head3 TransferOrder
1778 my $newordernumber = TransferOrder($ordernumber, $basketno);
1780 Transfer an order line to a basket.
1781 Mark $ordernumber as cancelled with an internal note 'Cancelled and transfered
1782 to BOOKSELLER on DATE' and create new order with internal note
1783 'Transfered from BOOKSELLER on DATE'.
1784 Move all attached items to the new order.
1785 Received orders cannot be transfered.
1786 Return the ordernumber of created order.
1791 my ($ordernumber, $basketno) = @_;
1793 return unless ($ordernumber and $basketno);
1795 my $order = GetOrder( $ordernumber );
1796 return if $order->{datereceived};
1797 my $basket = GetBasket($basketno);
1798 return unless $basket;
1800 my $dbh = C4::Context->dbh;
1801 my ($query, $sth, $rv);
1805 SET datecancellationprinted = CAST(NOW() AS date)
1806 WHERE ordernumber = ?
1808 $sth = $dbh->prepare($query);
1809 $rv = $sth->execute($ordernumber);
1811 delete $order->{'ordernumber'};
1812 delete $order->{parent_ordernumber};
1813 $order->{'basketno'} = $basketno;
1815 (undef, $newordernumber) = NewOrder($order);
1818 UPDATE aqorders_items
1820 WHERE ordernumber = ?
1822 $sth = $dbh->prepare($query);
1823 $sth->execute($newordernumber, $ordernumber);
1826 INSERT INTO aqorders_transfers (ordernumber_from, ordernumber_to)
1829 $sth = $dbh->prepare($query);
1830 $sth->execute($ordernumber, $newordernumber);
1832 return $newordernumber;
1835 =head2 FUNCTIONS ABOUT PARCELS
1839 #------------------------------------------------------------#
1843 @results = &GetParcel($booksellerid, $code, $date);
1845 Looks up all of the received items from the supplier with the given
1846 bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders.
1848 C<@results> is an array of references-to-hash. The keys of each element are fields from
1849 the aqorders, biblio, and biblioitems tables of the Koha database.
1851 C<@results> is sorted alphabetically by book title.
1856 #gets all orders from a certain supplier, orders them alphabetically
1857 my ( $supplierid, $code, $datereceived ) = @_;
1858 my $dbh = C4::Context->dbh;
1861 if $code; # add % if we search on a given code (otherwise, let him empty)
1863 SELECT authorisedby,
1868 aqorders.biblionumber,
1869 aqorders.ordernumber,
1870 aqorders.parent_ordernumber,
1872 aqorders.quantityreceived,
1880 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
1881 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1882 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1883 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
1885 aqbasket.booksellerid = ?
1886 AND aqinvoices.invoicenumber LIKE ?
1887 AND aqorders.datereceived = ? ";
1889 my @query_params = ( $supplierid, $code, $datereceived );
1890 if ( C4::Context->preference("IndependentBranches") ) {
1891 unless ( C4::Context->IsSuperLibrarian() ) {
1892 $strsth .= " and (borrowers.branchcode = ?
1893 or borrowers.branchcode = '')";
1894 push @query_params, C4::Context->userenv->{branch};
1897 $strsth .= " ORDER BY aqbasket.basketno";
1898 my $result_set = $dbh->selectall_arrayref(
1903 return @{$result_set};
1906 #------------------------------------------------------------#
1910 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
1912 get a lists of parcels.
1919 is the bookseller this function has to get parcels.
1922 To know on what criteria the results list has to be ordered.
1925 is the booksellerinvoicenumber.
1927 =item $datefrom & $dateto
1928 to know on what date this function has to filter its search.
1933 a pointer on a hash list containing parcel informations as such :
1939 =item Last operation
1941 =item Number of biblio
1943 =item Number of items
1950 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
1951 my $dbh = C4::Context->dbh;
1952 my @query_params = ();
1954 SELECT aqinvoices.invoicenumber,
1955 datereceived,purchaseordernumber,
1956 count(DISTINCT biblionumber) AS biblio,
1957 sum(quantity) AS itemsexpected,
1958 sum(quantityreceived) AS itemsreceived
1959 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
1960 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
1961 WHERE aqbasket.booksellerid = ? and datereceived IS NOT NULL
1963 push @query_params, $bookseller;
1965 if ( defined $code ) {
1966 $strsth .= ' and aqinvoices.invoicenumber like ? ';
1967 # add a % to the end of the code to allow stemming.
1968 push @query_params, "$code%";
1971 if ( defined $datefrom ) {
1972 $strsth .= ' and datereceived >= ? ';
1973 push @query_params, $datefrom;
1976 if ( defined $dateto ) {
1977 $strsth .= 'and datereceived <= ? ';
1978 push @query_params, $dateto;
1981 $strsth .= "group by aqinvoices.invoicenumber,datereceived ";
1983 # can't use a placeholder to place this column name.
1984 # but, we could probably be checking to make sure it is a column that will be fetched.
1985 $strsth .= "order by $order " if ($order);
1987 my $sth = $dbh->prepare($strsth);
1989 $sth->execute( @query_params );
1990 my $results = $sth->fetchall_arrayref({});
1994 #------------------------------------------------------------#
1996 =head3 GetLateOrders
1998 @results = &GetLateOrders;
2000 Searches for bookseller with late orders.
2003 the table of supplier with late issues. This table is full of hashref.
2009 my $supplierid = shift;
2011 my $estimateddeliverydatefrom = shift;
2012 my $estimateddeliverydateto = shift;
2014 my $dbh = C4::Context->dbh;
2016 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
2017 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
2019 my @query_params = ();
2021 SELECT aqbasket.basketno,
2022 aqorders.ordernumber,
2023 DATE(aqbasket.closedate) AS orderdate,
2024 aqorders.rrp AS unitpricesupplier,
2025 aqorders.ecost AS unitpricelib,
2026 aqorders.claims_count AS claims_count,
2027 aqorders.claimed_date AS claimed_date,
2028 aqbudgets.budget_name AS budget,
2029 borrowers.branchcode AS branch,
2030 aqbooksellers.name AS supplier,
2031 aqbooksellers.id AS supplierid,
2032 biblio.author, biblio.title,
2033 biblioitems.publishercode AS publisher,
2034 biblioitems.publicationyear,
2035 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
2039 aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
2040 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
2041 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
2042 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
2043 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
2044 WHERE aqorders.basketno = aqbasket.basketno
2045 AND ( datereceived = ''
2046 OR datereceived IS NULL
2047 OR aqorders.quantityreceived < aqorders.quantity
2049 AND aqbasket.closedate IS NOT NULL
2050 AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
2053 if ($dbdriver eq "mysql") {
2055 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity,
2056 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
2057 DATEDIFF(CAST(now() AS date),closedate) AS latesince
2059 if ( defined $delay ) {
2060 $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ;
2061 push @query_params, $delay;
2064 HAVING quantity <> 0
2065 AND unitpricesupplier <> 0
2066 AND unitpricelib <> 0
2069 # FIXME: account for IFNULL as above
2071 aqorders.quantity AS quantity,
2072 aqorders.quantity * aqorders.rrp AS subtotal,
2073 (CAST(now() AS date) - closedate) AS latesince
2075 if ( defined $delay ) {
2076 $from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) ";
2077 push @query_params, $delay;
2080 if (defined $supplierid) {
2081 $from .= ' AND aqbasket.booksellerid = ? ';
2082 push @query_params, $supplierid;
2084 if (defined $branch) {
2085 $from .= ' AND borrowers.branchcode LIKE ? ';
2086 push @query_params, $branch;
2089 if ( defined $estimateddeliverydatefrom or defined $estimateddeliverydateto ) {
2090 $from .= ' AND aqbooksellers.deliverytime IS NOT NULL ';
2092 if ( defined $estimateddeliverydatefrom ) {
2093 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
2094 push @query_params, $estimateddeliverydatefrom;
2096 if ( defined $estimateddeliverydateto ) {
2097 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
2098 push @query_params, $estimateddeliverydateto;
2100 if ( defined $estimateddeliverydatefrom and not defined $estimateddeliverydateto ) {
2101 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
2103 if (C4::Context->preference("IndependentBranches")
2104 && !C4::Context->IsSuperLibrarian() ) {
2105 $from .= ' AND borrowers.branchcode LIKE ? ';
2106 push @query_params, C4::Context->userenv->{branch};
2108 $from .= " AND orderstatus <> 'cancelled' ";
2109 my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier";
2110 $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params);
2111 my $sth = $dbh->prepare($query);
2112 $sth->execute(@query_params);
2114 while (my $data = $sth->fetchrow_hashref) {
2115 $data->{orderdate} = format_date($data->{orderdate});
2116 $data->{claimed_date} = format_date($data->{claimed_date});
2117 push @results, $data;
2122 #------------------------------------------------------------#
2126 (\@order_loop, $total_qty, $total_price, $total_qtyreceived) = GetHistory( %params );
2128 Retreives some acquisition history information
2138 basket - search both basket name and number
2139 booksellerinvoicenumber
2142 orderstatus (note that orderstatus '' will retrieve orders
2143 of any status except cancelled)
2145 get_canceled_order (if set to a true value, cancelled orders will
2149 $order_loop is a list of hashrefs that each look like this:
2151 'author' => 'Twain, Mark',
2153 'biblionumber' => '215',
2155 'creationdate' => 'MM/DD/YYYY',
2156 'datereceived' => undef,
2159 'invoicenumber' => undef,
2161 'ordernumber' => '1',
2163 'quantityreceived' => undef,
2164 'title' => 'The Adventures of Huckleberry Finn'
2166 $total_qty is the sum of all of the quantities in $order_loop
2167 $total_price is the cost of each in $order_loop times the quantity
2168 $total_qtyreceived is the sum of all of the quantityreceived entries in $order_loop
2173 # don't run the query if there are no parameters (list would be too long for sure !)
2174 croak "No search params" unless @_;
2176 my $title = $params{title};
2177 my $author = $params{author};
2178 my $isbn = $params{isbn};
2179 my $ean = $params{ean};
2180 my $name = $params{name};
2181 my $from_placed_on = $params{from_placed_on};
2182 my $to_placed_on = $params{to_placed_on};
2183 my $basket = $params{basket};
2184 my $booksellerinvoicenumber = $params{booksellerinvoicenumber};
2185 my $basketgroupname = $params{basketgroupname};
2186 my $budget = $params{budget};
2187 my $orderstatus = $params{orderstatus};
2188 my $biblionumber = $params{biblionumber};
2189 my $get_canceled_order = $params{get_canceled_order} || 0;
2193 my $total_qtyreceived = 0;
2194 my $total_price = 0;
2196 my $dbh = C4::Context->dbh;
2199 COALESCE(biblio.title, deletedbiblio.title) AS title,
2200 COALESCE(biblio.author, deletedbiblio.author) AS author,
2201 COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn,
2202 COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean,
2204 aqbasket.basketname,
2205 aqbasket.basketgroupid,
2206 aqbasketgroups.name as groupname,
2208 aqbasket.creationdate,
2209 aqorders.datereceived,
2211 aqorders.quantityreceived,
2213 aqorders.ordernumber,
2215 aqinvoices.invoicenumber,
2216 aqbooksellers.id as id,
2217 aqorders.biblionumber,
2218 aqorders.orderstatus,
2219 aqorders.parent_ordernumber,
2220 aqbudgets.budget_name
2222 $query .= ", aqbudgets.budget_id AS budget" if defined $budget;
2225 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
2226 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
2227 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
2228 LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
2229 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
2230 LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id
2231 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2232 LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.biblionumber
2233 LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=aqorders.biblionumber
2236 if ( C4::Context->preference("IndependentBranches") ) {
2237 $query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber";
2240 $query .= " WHERE 1 ";
2242 unless ($get_canceled_order or (defined $orderstatus and $orderstatus eq 'cancelled')) {
2243 $query .= " AND (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') ";
2246 my @query_params = ();
2248 if ( $biblionumber ) {
2249 $query .= " AND biblio.biblionumber = ?";
2250 push @query_params, $biblionumber;
2254 $query .= " AND biblio.title LIKE ? ";
2255 $title =~ s/\s+/%/g;
2256 push @query_params, "%$title%";
2260 $query .= " AND biblio.author LIKE ? ";
2261 push @query_params, "%$author%";
2265 $query .= " AND biblioitems.isbn LIKE ? ";
2266 push @query_params, "%$isbn%";
2269 $query .= " AND biblioitems.ean = ? ";
2270 push @query_params, "$ean";
2273 $query .= " AND aqbooksellers.name LIKE ? ";
2274 push @query_params, "%$name%";
2278 $query .= " AND aqbudgets.budget_id = ? ";
2279 push @query_params, "$budget";
2282 if ( $from_placed_on ) {
2283 $query .= " AND creationdate >= ? ";
2284 push @query_params, $from_placed_on;
2287 if ( $to_placed_on ) {
2288 $query .= " AND creationdate <= ? ";
2289 push @query_params, $to_placed_on;
2292 if ( defined $orderstatus and $orderstatus ne '') {
2293 $query .= " AND aqorders.orderstatus = ? ";
2294 push @query_params, "$orderstatus";
2298 if ($basket =~ m/^\d+$/) {
2299 $query .= " AND aqorders.basketno = ? ";
2300 push @query_params, $basket;
2302 $query .= " AND aqbasket.basketname LIKE ? ";
2303 push @query_params, "%$basket%";
2307 if ($booksellerinvoicenumber) {
2308 $query .= " AND aqinvoices.invoicenumber LIKE ? ";
2309 push @query_params, "%$booksellerinvoicenumber%";
2312 if ($basketgroupname) {
2313 $query .= " AND aqbasketgroups.name LIKE ? ";
2314 push @query_params, "%$basketgroupname%";
2317 if ( C4::Context->preference("IndependentBranches") ) {
2318 unless ( C4::Context->IsSuperLibrarian() ) {
2319 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
2320 push @query_params, C4::Context->userenv->{branch};
2323 $query .= " ORDER BY id";
2324 my $sth = $dbh->prepare($query);
2325 $sth->execute( @query_params );
2327 while ( my $line = $sth->fetchrow_hashref ) {
2328 $line->{count} = $cnt++;
2329 $line->{toggle} = 1 if $cnt % 2;
2330 push @order_loop, $line;
2331 $total_qty += ( $line->{quantity} ) ? $line->{quantity} : 0;
2332 $total_qtyreceived += ( $line->{quantityreceived} ) ? $line->{quantityreceived} : 0;
2333 $total_price += ( $line->{quantity} and $line->{ecost} ) ? $line->{quantity} * $line->{ecost} : 0;
2335 return \@order_loop, $total_qty, $total_price, $total_qtyreceived;
2338 =head2 GetRecentAcqui
2340 $results = GetRecentAcqui($days);
2342 C<$results> is a ref to a table which containts hashref
2346 sub GetRecentAcqui {
2348 my $dbh = C4::Context->dbh;
2352 ORDER BY timestamp DESC
2355 my $sth = $dbh->prepare($query);
2357 my $results = $sth->fetchall_arrayref({});
2363 $contractlist = &GetContracts($booksellerid, $activeonly);
2365 Looks up the contracts that belong to a bookseller
2367 Returns a list of contracts
2371 =item C<$booksellerid> is the "id" field in the "aqbooksellers" table.
2373 =item C<$activeonly> if exists get only contracts that are still active.
2380 my ( $booksellerid, $activeonly ) = @_;
2381 my $dbh = C4::Context->dbh;
2383 if (! $activeonly) {
2387 WHERE booksellerid=?
2392 WHERE booksellerid=?
2393 AND contractenddate >= CURDATE( )";
2396 $dbh->selectall_arrayref( $query, { Slice => {} }, $booksellerid );
2397 return @{$result_set};
2400 #------------------------------------------------------------#
2404 $contract = &GetContract($contractID);
2406 Looks up the contract that has PRIMKEY (contractnumber) value $contractID
2413 my ( $contractno ) = @_;
2414 my $dbh = C4::Context->dbh;
2418 WHERE contractnumber=?
2421 my $sth = $dbh->prepare($query);
2422 $sth->execute( $contractno );
2423 my $result = $sth->fetchrow_hashref;
2431 &AddClaim($ordernumber);
2433 Add a claim for an order
2440 my ($ordernumber) = @_;
2441 my $dbh = C4::Context->dbh;
2444 claims_count = claims_count + 1,
2445 claimed_date = CURDATE()
2446 WHERE ordernumber = ?
2448 my $sth = $dbh->prepare($query);
2449 $sth->execute($ordernumber);
2454 my @invoices = GetInvoices(
2455 invoicenumber => $invoicenumber,
2456 supplierid => $supplierid,
2457 suppliername => $suppliername,
2458 shipmentdatefrom => $shipmentdatefrom, # ISO format
2459 shipmentdateto => $shipmentdateto, # ISO format
2460 billingdatefrom => $billingdatefrom, # ISO format
2461 billingdateto => $billingdateto, # ISO format
2462 isbneanissn => $isbn_or_ean_or_issn,
2465 publisher => $publisher,
2466 publicationyear => $publicationyear,
2467 branchcode => $branchcode,
2468 order_by => $order_by
2471 Return a list of invoices that match all given criteria.
2473 $order_by is "column_name (asc|desc)", where column_name is any of
2474 'invoicenumber', 'booksellerid', 'shipmentdate', 'billingdate', 'closedate',
2475 'shipmentcost', 'shipmentcost_budgetid'.
2477 asc is the default if omitted
2484 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2485 closedate shipmentcost shipmentcost_budgetid);
2487 my $dbh = C4::Context->dbh;
2489 SELECT aqinvoices.*, aqbooksellers.name AS suppliername,
2492 aqorders.datereceived IS NOT NULL,
2493 aqorders.biblionumber,
2496 ) AS receivedbiblios,
2497 SUM(aqorders.quantityreceived) AS receiveditems
2499 LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid
2500 LEFT JOIN aqorders ON aqorders.invoiceid = aqinvoices.invoiceid
2501 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
2502 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2503 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2504 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
2505 LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber
2510 if($args{supplierid}) {
2511 push @bind_strs, " aqinvoices.booksellerid = ? ";
2512 push @bind_args, $args{supplierid};
2514 if($args{invoicenumber}) {
2515 push @bind_strs, " aqinvoices.invoicenumber LIKE ? ";
2516 push @bind_args, "%$args{invoicenumber}%";
2518 if($args{suppliername}) {
2519 push @bind_strs, " aqbooksellers.name LIKE ? ";
2520 push @bind_args, "%$args{suppliername}%";
2522 if($args{shipmentdatefrom}) {
2523 push @bind_strs, " aqinvoices.shipmentdate >= ? ";
2524 push @bind_args, $args{shipmentdatefrom};
2526 if($args{shipmentdateto}) {
2527 push @bind_strs, " aqinvoices.shipmentdate <= ? ";
2528 push @bind_args, $args{shipmentdateto};
2530 if($args{billingdatefrom}) {
2531 push @bind_strs, " aqinvoices.billingdate >= ? ";
2532 push @bind_args, $args{billingdatefrom};
2534 if($args{billingdateto}) {
2535 push @bind_strs, " aqinvoices.billingdate <= ? ";
2536 push @bind_args, $args{billingdateto};
2538 if($args{isbneanissn}) {
2539 push @bind_strs, " (biblioitems.isbn LIKE CONCAT('%', ?, '%') OR biblioitems.ean LIKE CONCAT('%', ?, '%') OR biblioitems.issn LIKE CONCAT('%', ?, '%') ) ";
2540 push @bind_args, $args{isbneanissn}, $args{isbneanissn}, $args{isbneanissn};
2543 push @bind_strs, " biblio.title LIKE CONCAT('%', ?, '%') ";
2544 push @bind_args, $args{title};
2547 push @bind_strs, " biblio.author LIKE CONCAT('%', ?, '%') ";
2548 push @bind_args, $args{author};
2550 if($args{publisher}) {
2551 push @bind_strs, " biblioitems.publishercode LIKE CONCAT('%', ?, '%') ";
2552 push @bind_args, $args{publisher};
2554 if($args{publicationyear}) {
2555 push @bind_strs, " ((biblioitems.publicationyear LIKE CONCAT('%', ?, '%')) OR (biblio.copyrightdate LIKE CONCAT('%', ?, '%'))) ";
2556 push @bind_args, $args{publicationyear}, $args{publicationyear};
2558 if($args{branchcode}) {
2559 push @bind_strs, " borrowers.branchcode = ? ";
2560 push @bind_args, $args{branchcode};
2563 $query .= " WHERE " . join(" AND ", @bind_strs) if @bind_strs;
2564 $query .= " GROUP BY aqinvoices.invoiceid ";
2566 if($args{order_by}) {
2567 my ($column, $direction) = split / /, $args{order_by};
2568 if(grep /^$column$/, @columns) {
2569 $direction ||= 'ASC';
2570 $query .= " ORDER BY $column $direction";
2574 my $sth = $dbh->prepare($query);
2575 $sth->execute(@bind_args);
2577 my $results = $sth->fetchall_arrayref({});
2583 my $invoice = GetInvoice($invoiceid);
2585 Get informations about invoice with given $invoiceid
2587 Return a hash filled with aqinvoices.* fields
2592 my ($invoiceid) = @_;
2595 return unless $invoiceid;
2597 my $dbh = C4::Context->dbh;
2603 my $sth = $dbh->prepare($query);
2604 $sth->execute($invoiceid);
2606 $invoice = $sth->fetchrow_hashref;
2610 =head3 GetInvoiceDetails
2612 my $invoice = GetInvoiceDetails($invoiceid)
2614 Return informations about an invoice + the list of related order lines
2616 Orders informations are in $invoice->{orders} (array ref)
2620 sub GetInvoiceDetails {
2621 my ($invoiceid) = @_;
2623 if ( !defined $invoiceid ) {
2624 carp 'GetInvoiceDetails called without an invoiceid';
2628 my $dbh = C4::Context->dbh;
2630 SELECT aqinvoices.*, aqbooksellers.name AS suppliername
2632 LEFT JOIN aqbooksellers ON aqinvoices.booksellerid = aqbooksellers.id
2635 my $sth = $dbh->prepare($query);
2636 $sth->execute($invoiceid);
2638 my $invoice = $sth->fetchrow_hashref;
2641 SELECT aqorders.*, biblio.*, aqbasket.basketname
2643 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
2644 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2647 $sth = $dbh->prepare($query);
2648 $sth->execute($invoiceid);
2649 $invoice->{orders} = $sth->fetchall_arrayref({});
2650 $invoice->{orders} ||= []; # force an empty arrayref if fetchall_arrayref fails
2657 my $invoiceid = AddInvoice(
2658 invoicenumber => $invoicenumber,
2659 booksellerid => $booksellerid,
2660 shipmentdate => $shipmentdate,
2661 billingdate => $billingdate,
2662 closedate => $closedate,
2663 shipmentcost => $shipmentcost,
2664 shipmentcost_budgetid => $shipmentcost_budgetid
2667 Create a new invoice and return its id or undef if it fails.
2674 return unless(%invoice and $invoice{invoicenumber});
2676 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2677 closedate shipmentcost shipmentcost_budgetid);
2681 foreach my $key (keys %invoice) {
2682 if(0 < grep(/^$key$/, @columns)) {
2683 push @set_strs, "$key = ?";
2684 push @set_args, ($invoice{$key} || undef);
2690 my $dbh = C4::Context->dbh;
2691 my $query = "INSERT INTO aqinvoices SET ";
2692 $query .= join (",", @set_strs);
2693 my $sth = $dbh->prepare($query);
2694 $rv = $sth->execute(@set_args);
2696 $rv = $dbh->last_insert_id(undef, undef, 'aqinvoices', undef);
2705 invoiceid => $invoiceid, # Mandatory
2706 invoicenumber => $invoicenumber,
2707 booksellerid => $booksellerid,
2708 shipmentdate => $shipmentdate,
2709 billingdate => $billingdate,
2710 closedate => $closedate,
2711 shipmentcost => $shipmentcost,
2712 shipmentcost_budgetid => $shipmentcost_budgetid
2715 Modify an invoice, invoiceid is mandatory.
2717 Return undef if it fails.
2724 return unless(%invoice and $invoice{invoiceid});
2726 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2727 closedate shipmentcost shipmentcost_budgetid);
2731 foreach my $key (keys %invoice) {
2732 if(0 < grep(/^$key$/, @columns)) {
2733 push @set_strs, "$key = ?";
2734 push @set_args, ($invoice{$key} || undef);
2738 my $dbh = C4::Context->dbh;
2739 my $query = "UPDATE aqinvoices SET ";
2740 $query .= join(",", @set_strs);
2741 $query .= " WHERE invoiceid = ?";
2743 my $sth = $dbh->prepare($query);
2744 $sth->execute(@set_args, $invoice{invoiceid});
2749 CloseInvoice($invoiceid);
2753 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => undef);
2758 my ($invoiceid) = @_;
2760 return unless $invoiceid;
2762 my $dbh = C4::Context->dbh;
2765 SET closedate = CAST(NOW() AS DATE)
2768 my $sth = $dbh->prepare($query);
2769 $sth->execute($invoiceid);
2772 =head3 ReopenInvoice
2774 ReopenInvoice($invoiceid);
2778 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => C4::Dates->new()->output('iso'))
2783 my ($invoiceid) = @_;
2785 return unless $invoiceid;
2787 my $dbh = C4::Context->dbh;
2790 SET closedate = NULL
2793 my $sth = $dbh->prepare($query);
2794 $sth->execute($invoiceid);
2799 DelInvoice($invoiceid);
2801 Delete an invoice if there are no items attached to it.
2806 my ($invoiceid) = @_;
2808 return unless $invoiceid;
2810 my $dbh = C4::Context->dbh;
2816 my $sth = $dbh->prepare($query);
2817 $sth->execute($invoiceid);
2818 my $res = $sth->fetchrow_arrayref;
2819 if ( $res && $res->[0] == 0 ) {
2821 DELETE FROM aqinvoices
2824 my $sth = $dbh->prepare($query);
2825 return ( $sth->execute($invoiceid) > 0 );
2830 =head3 MergeInvoices
2832 MergeInvoices($invoiceid, \@sourceids);
2834 Merge the invoices identified by the IDs in \@sourceids into
2835 the invoice identified by $invoiceid.
2840 my ($invoiceid, $sourceids) = @_;
2842 return unless $invoiceid;
2843 foreach my $sourceid (@$sourceids) {
2844 next if $sourceid == $invoiceid;
2845 my $source = GetInvoiceDetails($sourceid);
2846 foreach my $order (@{$source->{'orders'}}) {
2847 $order->{'invoiceid'} = $invoiceid;
2850 DelInvoice($source->{'invoiceid'});
2855 =head3 GetBiblioCountByBasketno
2857 $biblio_count = &GetBiblioCountByBasketno($basketno);
2859 Looks up the biblio's count that has basketno value $basketno
2865 sub GetBiblioCountByBasketno {
2866 my ($basketno) = @_;
2867 my $dbh = C4::Context->dbh;
2869 SELECT COUNT( DISTINCT( biblionumber ) )
2872 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
2875 my $sth = $dbh->prepare($query);
2876 $sth->execute($basketno);
2877 return $sth->fetchrow;
2885 Koha Development Team <http://koha-community.org/>