1 package C4::Acquisition;
3 # Copyright 2000-2002 Katipo Communications
5 # This file is part of Koha.
7 # Koha is free software; you can redistribute it and/or modify it under the
8 # terms of the GNU General Public License as published by the Free Software
9 # Foundation; either version 2 of the License, or (at your option) any later
12 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
13 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
14 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
16 # You should have received a copy of the GNU General Public License along
17 # with Koha; if not, write to the Free Software Foundation, Inc.,
18 # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
26 use C4::Dates qw(format_date format_date_in_iso);
31 use C4::SQLHelper qw(InsertInTable UpdateInTable);
32 use C4::Bookseller qw(GetBookSellerFromId);
33 use C4::Templates qw(gettemplate);
38 use vars qw($VERSION @ISA @EXPORT);
41 # set the version for version checking
42 $VERSION = 3.07.00.049;
46 &GetBasket &NewBasket &CloseBasket &ReopenBasket &DelBasket &ModBasket
47 &GetBasketAsCSV &GetBasketGroupAsCSV
48 &GetBasketsByBookseller &GetBasketsByBasketgroup
49 &GetBasketsInfosByBookseller
51 &GetBasketUsers &ModBasketUsers
56 &ModBasketgroup &NewBasketgroup &DelBasketgroup &GetBasketgroup &CloseBasketgroup
57 &GetBasketgroups &ReOpenBasketgroup
59 &NewOrder &DelOrder &ModOrder &GetOrder &GetOrders &GetOrdersByBiblionumber
60 &GetLateOrders &GetOrderFromItemnumber
61 &SearchOrders &GetHistory &GetRecentAcqui
62 &ModReceiveOrder &CancelReceipt
63 &GetCancelledOrders &TransferOrder
64 &GetLastOrderNotReceivedFromSubscriptionid &GetLastOrderReceivedFromSubscriptionid
65 &NewOrderItem &ModItemOrder
67 &GetParcels &GetParcel
68 &GetContracts &GetContract
80 &GetItemnumbersFromOrder
83 &GetBiblioCountByBasketno
91 sub GetOrderFromItemnumber {
92 my ($itemnumber) = @_;
93 my $dbh = C4::Context->dbh;
96 SELECT * from aqorders LEFT JOIN aqorders_items
97 ON ( aqorders.ordernumber = aqorders_items.ordernumber )
98 WHERE itemnumber = ? |;
100 my $sth = $dbh->prepare($query);
104 $sth->execute($itemnumber);
106 my $order = $sth->fetchrow_hashref;
111 # Returns the itemnumber(s) associated with the ordernumber given in parameter
112 sub GetItemnumbersFromOrder {
113 my ($ordernumber) = @_;
114 my $dbh = C4::Context->dbh;
115 my $query = "SELECT itemnumber FROM aqorders_items WHERE ordernumber=?";
116 my $sth = $dbh->prepare($query);
117 $sth->execute($ordernumber);
120 while (my $order = $sth->fetchrow_hashref) {
121 push @tab, $order->{'itemnumber'};
135 C4::Acquisition - Koha functions for dealing with orders and acquisitions
143 The functions in this module deal with acquisitions, managing book
144 orders, basket and parcels.
148 =head2 FUNCTIONS ABOUT BASKETS
152 $aqbasket = &GetBasket($basketnumber);
154 get all basket informations in aqbasket for a given basket
156 B<returns:> informations for a given basket returned as a hashref.
162 my $dbh = C4::Context->dbh;
165 concat( b.firstname,' ',b.surname) AS authorisedbyname
167 LEFT JOIN borrowers b ON aqbasket.authorisedby=b.borrowernumber
170 my $sth=$dbh->prepare($query);
171 $sth->execute($basketno);
172 my $basket = $sth->fetchrow_hashref;
176 #------------------------------------------------------------#
180 $basket = &NewBasket( $booksellerid, $authorizedby, $basketname,
181 $basketnote, $basketbooksellernote, $basketcontractnumber, $deliveryplace, $billingplace );
183 Create a new basket in aqbasket table
187 =item C<$booksellerid> is a foreign key in the aqbasket table
189 =item C<$authorizedby> is the username of who created the basket
193 The other parameters are optional, see ModBasketHeader for more info on them.
198 my ( $booksellerid, $authorisedby, $basketname, $basketnote,
199 $basketbooksellernote, $basketcontractnumber, $deliveryplace,
200 $billingplace ) = @_;
201 my $dbh = C4::Context->dbh;
203 'INSERT INTO aqbasket (creationdate,booksellerid,authorisedby) '
204 . 'VALUES (now(),?,?)';
205 $dbh->do( $query, {}, $booksellerid, $authorisedby );
207 my $basket = $dbh->{mysql_insertid};
208 $basketname ||= q{}; # default to empty strings
210 $basketbooksellernote ||= q{};
211 ModBasketHeader( $basket, $basketname, $basketnote, $basketbooksellernote,
212 $basketcontractnumber, $booksellerid, $deliveryplace, $billingplace );
216 #------------------------------------------------------------#
220 &CloseBasket($basketno);
222 close a basket (becomes unmodifiable, except for receives)
228 my $dbh = C4::Context->dbh;
234 my $sth = $dbh->prepare($query);
235 $sth->execute($basketno);
237 my @orders = GetOrders($basketno);
238 foreach my $order (@orders) {
241 SET orderstatus = 'ordered'
242 WHERE ordernumber = ?;
244 $sth = $dbh->prepare($query);
245 $sth->execute($order->{'ordernumber'});
251 &ReopenBasket($basketno);
259 my $dbh = C4::Context->dbh;
265 my $sth = $dbh->prepare($query);
266 $sth->execute($basketno);
268 my @orders = GetOrders($basketno);
269 foreach my $order (@orders) {
272 SET orderstatus = 'new'
273 WHERE ordernumber = ?;
275 $sth = $dbh->prepare($query);
276 $sth->execute($order->{'ordernumber'});
280 #------------------------------------------------------------#
282 =head3 GetBasketAsCSV
284 &GetBasketAsCSV($basketno);
286 Export a basket as CSV
288 $cgi parameter is needed for column name translation
293 my ($basketno, $cgi) = @_;
294 my $basket = GetBasket($basketno);
295 my @orders = GetOrders($basketno);
296 my $contract = GetContract($basket->{'contractnumber'});
298 my $template = C4::Templates::gettemplate("acqui/csv/basket.tmpl", "intranet", $cgi);
301 foreach my $order (@orders) {
302 my $bd = GetBiblioData( $order->{'biblionumber'} );
304 contractname => $contract->{'contractname'},
305 ordernumber => $order->{'ordernumber'},
306 entrydate => $order->{'entrydate'},
307 isbn => $order->{'isbn'},
308 author => $bd->{'author'},
309 title => $bd->{'title'},
310 publicationyear => $bd->{'publicationyear'},
311 publishercode => $bd->{'publishercode'},
312 collectiontitle => $bd->{'collectiontitle'},
313 notes => $order->{'order_internalnote'},
314 quantity => $order->{'quantity'},
315 rrp => $order->{'rrp'},
316 deliveryplace => C4::Branch::GetBranchName( $basket->{'deliveryplace'} ),
317 billingplace => C4::Branch::GetBranchName( $basket->{'billingplace'} ),
320 contractname author title publishercode collectiontitle notes
321 deliveryplace billingplace
323 # Double the quotes to not be interpreted as a field end
324 $row->{$_} =~ s/"/""/g if $row->{$_};
330 if(defined $a->{publishercode} and defined $b->{publishercode}) {
331 $a->{publishercode} cmp $b->{publishercode};
335 $template->param(rows => \@rows);
337 return $template->output;
341 =head3 GetBasketGroupAsCSV
345 &GetBasketGroupAsCSV($basketgroupid);
347 Export a basket group as CSV
349 $cgi parameter is needed for column name translation
355 sub GetBasketGroupAsCSV {
356 my ($basketgroupid, $cgi) = @_;
357 my $baskets = GetBasketsByBasketgroup($basketgroupid);
359 my $template = C4::Templates::gettemplate('acqui/csv/basketgroup.tmpl', 'intranet', $cgi);
362 for my $basket (@$baskets) {
363 my @orders = GetOrders( $$basket{basketno} );
364 my $contract = GetContract( $$basket{contractnumber} );
365 my $bookseller = GetBookSellerFromId( $$basket{booksellerid} );
366 my $basketgroup = GetBasketgroup( $$basket{basketgroupid} );
368 foreach my $order (@orders) {
369 my $bd = GetBiblioData( $order->{'biblionumber'} );
371 clientnumber => $bookseller->{accountnumber},
372 basketname => $basket->{basketname},
373 ordernumber => $order->{ordernumber},
374 author => $bd->{author},
375 title => $bd->{title},
376 publishercode => $bd->{publishercode},
377 publicationyear => $bd->{publicationyear},
378 collectiontitle => $bd->{collectiontitle},
379 isbn => $order->{isbn},
380 quantity => $order->{quantity},
381 rrp => $order->{rrp},
382 discount => $bookseller->{discount},
383 ecost => $order->{ecost},
384 notes => $order->{order_internalnote},
385 entrydate => $order->{entrydate},
386 booksellername => $bookseller->{name},
387 bookselleraddress => $bookseller->{address1},
388 booksellerpostal => $bookseller->{postal},
389 contractnumber => $contract->{contractnumber},
390 contractname => $contract->{contractname},
391 basketgroupdeliveryplace => C4::Branch::GetBranchName( $basketgroup->{deliveryplace} ),
392 basketgroupbillingplace => C4::Branch::GetBranchName( $basketgroup->{billingplace} ),
393 basketdeliveryplace => C4::Branch::GetBranchName( $basket->{deliveryplace} ),
394 basketbillingplace => C4::Branch::GetBranchName( $basket->{billingplace} ),
397 basketname author title publishercode collectiontitle notes
398 booksellername bookselleraddress booksellerpostal contractname
399 basketgroupdeliveryplace basketgroupbillingplace
400 basketdeliveryplace basketbillingplace
402 # Double the quotes to not be interpreted as a field end
403 $row->{$_} =~ s/"/""/g if $row->{$_};
408 $template->param(rows => \@rows);
410 return $template->output;
414 =head3 CloseBasketgroup
416 &CloseBasketgroup($basketgroupno);
422 sub CloseBasketgroup {
423 my ($basketgroupno) = @_;
424 my $dbh = C4::Context->dbh;
425 my $sth = $dbh->prepare("
426 UPDATE aqbasketgroups
430 $sth->execute($basketgroupno);
433 #------------------------------------------------------------#
435 =head3 ReOpenBaskergroup($basketgroupno)
437 &ReOpenBaskergroup($basketgroupno);
443 sub ReOpenBasketgroup {
444 my ($basketgroupno) = @_;
445 my $dbh = C4::Context->dbh;
446 my $sth = $dbh->prepare("
447 UPDATE aqbasketgroups
451 $sth->execute($basketgroupno);
454 #------------------------------------------------------------#
459 &DelBasket($basketno);
461 Deletes the basket that has basketno field $basketno in the aqbasket table.
465 =item C<$basketno> is the primary key of the basket in the aqbasket table.
472 my ( $basketno ) = @_;
473 my $query = "DELETE FROM aqbasket WHERE basketno=?";
474 my $dbh = C4::Context->dbh;
475 my $sth = $dbh->prepare($query);
476 $sth->execute($basketno);
480 #------------------------------------------------------------#
484 &ModBasket($basketinfo);
486 Modifies a basket, using a hashref $basketinfo for the relevant information, only $basketinfo->{'basketno'} is required.
490 =item C<$basketno> is the primary key of the basket in the aqbasket table.
497 my $basketinfo = shift;
498 my $query = "UPDATE aqbasket SET ";
500 foreach my $key (keys %$basketinfo){
501 if ($key ne 'basketno'){
502 $query .= "$key=?, ";
503 push(@params, $basketinfo->{$key} || undef );
506 # get rid of the "," at the end of $query
507 if (substr($query, length($query)-2) eq ', '){
512 $query .= "WHERE basketno=?";
513 push(@params, $basketinfo->{'basketno'});
514 my $dbh = C4::Context->dbh;
515 my $sth = $dbh->prepare($query);
516 $sth->execute(@params);
521 #------------------------------------------------------------#
523 =head3 ModBasketHeader
525 &ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid);
527 Modifies a basket's header.
531 =item C<$basketno> is the "basketno" field in the "aqbasket" table;
533 =item C<$basketname> is the "basketname" field in the "aqbasket" table;
535 =item C<$note> is the "note" field in the "aqbasket" table;
537 =item C<$booksellernote> is the "booksellernote" field in the "aqbasket" table;
539 =item C<$contractnumber> is the "contractnumber" (foreign) key in the "aqbasket" table.
541 =item C<$booksellerid> is the id (foreign) key in the "aqbooksellers" table for the vendor.
543 =item C<$deliveryplace> is the "deliveryplace" field in the aqbasket table.
545 =item C<$billingplace> is the "billingplace" field in the aqbasket table.
551 sub ModBasketHeader {
552 my ($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid, $deliveryplace, $billingplace) = @_;
555 SET basketname=?, note=?, booksellernote=?, booksellerid=?, deliveryplace=?, billingplace=?
559 my $dbh = C4::Context->dbh;
560 my $sth = $dbh->prepare($query);
561 $sth->execute($basketname, $note, $booksellernote, $booksellerid, $deliveryplace, $billingplace, $basketno);
563 if ( $contractnumber ) {
564 my $query2 ="UPDATE aqbasket SET contractnumber=? WHERE basketno=?";
565 my $sth2 = $dbh->prepare($query2);
566 $sth2->execute($contractnumber,$basketno);
571 #------------------------------------------------------------#
573 =head3 GetBasketsByBookseller
575 @results = &GetBasketsByBookseller($booksellerid, $extra);
577 Returns a list of hashes of all the baskets that belong to bookseller 'booksellerid'.
581 =item C<$booksellerid> is the 'id' field of the bookseller in the aqbooksellers table
583 =item C<$extra> is the extra sql parameters, can be
585 $extra->{groupby}: group baskets by column
586 ex. $extra->{groupby} = aqbasket.basketgroupid
587 $extra->{orderby}: order baskets by column
588 $extra->{limit}: limit number of results (can be helpful for pagination)
594 sub GetBasketsByBookseller {
595 my ($booksellerid, $extra) = @_;
596 my $query = "SELECT * FROM aqbasket WHERE booksellerid=?";
598 if ($extra->{groupby}) {
599 $query .= " GROUP by $extra->{groupby}";
601 if ($extra->{orderby}){
602 $query .= " ORDER by $extra->{orderby}";
604 if ($extra->{limit}){
605 $query .= " LIMIT $extra->{limit}";
608 my $dbh = C4::Context->dbh;
609 my $sth = $dbh->prepare($query);
610 $sth->execute($booksellerid);
611 return $sth->fetchall_arrayref({});
614 =head3 GetBasketsInfosByBookseller
616 my $baskets = GetBasketsInfosByBookseller($supplierid, $allbaskets);
618 The optional second parameter allbaskets is a boolean allowing you to
619 select all baskets from the supplier; by default only active baskets (open or
620 closed but still something to receive) are returned.
622 Returns in a arrayref of hashref all about booksellers baskets, plus:
623 total_biblios: Number of distinct biblios in basket
624 total_items: Number of items in basket
625 expected_items: Number of non-received items in basket
629 sub GetBasketsInfosByBookseller {
630 my ($supplierid, $allbaskets) = @_;
632 return unless $supplierid;
634 my $dbh = C4::Context->dbh;
637 SUM(aqorders.quantity) AS total_items,
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 return unless $ordernumber;
1107 my $dbh = C4::Context->dbh;
1108 my $query = qq{SELECT
1112 aqbasket.basketname,
1113 borrowers.branchcode,
1114 biblioitems.publicationyear,
1115 biblio.copyrightdate,
1116 biblioitems.editionstatement,
1120 biblioitems.publishercode,
1121 aqorders.rrp AS unitpricesupplier,
1122 aqorders.ecost AS unitpricelib,
1123 aqorders.claims_count AS claims_count,
1124 aqorders.claimed_date AS claimed_date,
1125 aqbudgets.budget_name AS budget,
1126 aqbooksellers.name AS supplier,
1127 aqbooksellers.id AS supplierid,
1128 biblioitems.publishercode AS publisher,
1129 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
1130 DATE(aqbasket.closedate) AS orderdate,
1131 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity_to_receive,
1132 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1133 DATEDIFF(CURDATE( ),closedate) AS latesince
1134 FROM aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1135 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1136 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
1137 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
1138 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1139 WHERE aqorders.basketno = aqbasket.basketno
1142 $dbh->selectall_arrayref( $query, { Slice => {} }, $ordernumber );
1144 # result_set assumed to contain 1 match
1145 return $result_set->[0];
1148 =head3 GetLastOrderNotReceivedFromSubscriptionid
1150 $order = &GetLastOrderNotReceivedFromSubscriptionid($subscriptionid);
1152 Returns a reference-to-hash describing the last order not received for a subscription.
1156 sub GetLastOrderNotReceivedFromSubscriptionid {
1157 my ( $subscriptionid ) = @_;
1158 my $dbh = C4::Context->dbh;
1160 SELECT * FROM aqorders
1161 LEFT JOIN subscription
1162 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1163 WHERE aqorders.subscriptionid = ?
1164 AND aqorders.datereceived IS NULL
1168 $dbh->selectall_arrayref( $query, { Slice => {} }, $subscriptionid );
1170 # result_set assumed to contain 1 match
1171 return $result_set->[0];
1174 =head3 GetLastOrderReceivedFromSubscriptionid
1176 $order = &GetLastOrderReceivedFromSubscriptionid($subscriptionid);
1178 Returns a reference-to-hash describing the last order received for a subscription.
1182 sub GetLastOrderReceivedFromSubscriptionid {
1183 my ( $subscriptionid ) = @_;
1184 my $dbh = C4::Context->dbh;
1186 SELECT * FROM aqorders
1187 LEFT JOIN subscription
1188 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1189 WHERE aqorders.subscriptionid = ?
1190 AND aqorders.datereceived =
1192 SELECT MAX( aqorders.datereceived )
1194 LEFT JOIN subscription
1195 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1196 WHERE aqorders.subscriptionid = ?
1197 AND aqorders.datereceived IS NOT NULL
1199 ORDER BY ordernumber DESC
1203 $dbh->selectall_arrayref( $query, { Slice => {} }, $subscriptionid, $subscriptionid );
1205 # result_set assumed to contain 1 match
1206 return $result_set->[0];
1211 #------------------------------------------------------------#
1215 &NewOrder(\%hashref);
1217 Adds a new order to the database. Any argument that isn't described
1218 below is the new value of the field with the same name in the aqorders
1219 table of the Koha database.
1223 =item $hashref->{'basketno'} is the basketno foreign key in aqorders, it is mandatory
1225 =item $hashref->{'ordernumber'} is a "minimum order number."
1227 =item $hashref->{'budgetdate'} is effectively ignored.
1228 If it's undef (anything false) or the string 'now', the current day is used.
1229 Else, the upcoming July 1st is used.
1231 =item $hashref->{'subscription'} may be either "yes", or anything else for "no".
1233 =item $hashref->{'uncertainprice'} may be 0 for "the price is known" or 1 for "the price is uncertain"
1235 =item defaults entrydate to Now
1237 The following keys are used: "biblionumber", "title", "basketno", "quantity", "order_vendornote", "order_internalnote", "rrp", "ecost", "gstrate", "unitprice", "subscription", "sort1", "sort2", "booksellerinvoicenumber", "listprice", "budgetdate", "purchaseordernumber", "branchcode", "booksellerinvoicenumber", "budget_id".
1244 my $orderinfo = shift;
1246 my $dbh = C4::Context->dbh;
1250 # if these parameters are missing, we can't continue
1251 for my $key (qw/basketno quantity biblionumber budget_id/) {
1252 croak "Mandatory parameter $key missing" unless $orderinfo->{$key};
1255 if ( defined $orderinfo->{subscription} && $orderinfo->{'subscription'} eq 'yes' ) {
1256 $orderinfo->{'subscription'} = 1;
1258 $orderinfo->{'subscription'} = 0;
1260 $orderinfo->{'entrydate'} ||= C4::Dates->new()->output("iso");
1261 if (!$orderinfo->{quantityreceived}) {
1262 $orderinfo->{quantityreceived} = 0;
1265 my $ordernumber=InsertInTable("aqorders",$orderinfo);
1266 if (not $orderinfo->{parent_ordernumber}) {
1267 my $sth = $dbh->prepare("
1269 SET parent_ordernumber = ordernumber
1270 WHERE ordernumber = ?
1272 $sth->execute($ordernumber);
1274 return ( $orderinfo->{'basketno'}, $ordernumber );
1279 #------------------------------------------------------------#
1288 my ($itemnumber, $ordernumber) = @_;
1289 my $dbh = C4::Context->dbh;
1291 INSERT INTO aqorders_items
1292 (itemnumber, ordernumber)
1295 my $sth = $dbh->prepare($query);
1296 $sth->execute( $itemnumber, $ordernumber);
1299 #------------------------------------------------------------#
1303 &ModOrder(\%hashref);
1305 Modifies an existing order. Updates the order with order number
1306 $hashref->{'ordernumber'} and biblionumber $hashref->{'biblionumber'}. All
1307 other keys of the hash update the fields with the same name in the aqorders
1308 table of the Koha database.
1313 my $orderinfo = shift;
1315 die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq '' ;
1316 die "Biblionumber is required" if $orderinfo->{'biblionumber'} eq '';
1318 my $dbh = C4::Context->dbh;
1321 # update uncertainprice to an integer, just in case (under FF, checked boxes have the value "ON" by default)
1322 $orderinfo->{uncertainprice}=1 if $orderinfo->{uncertainprice};
1324 # delete($orderinfo->{'branchcode'});
1325 # the hash contains a lot of entries not in aqorders, so get the columns ...
1326 my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;");
1328 my $colnames = $sth->{NAME};
1329 #FIXME Be careful. If aqorders would have columns with diacritics,
1330 #you should need to decode what you get back from NAME.
1331 #See report 10110 and guided_reports.pl
1332 my $query = "UPDATE aqorders SET ";
1334 foreach my $orderinfokey (grep(!/ordernumber/, keys %$orderinfo)){
1335 # ... and skip hash entries that are not in the aqorders table
1336 # FIXME : probably not the best way to do it (would be better to have a correct hash)
1337 next unless grep(/^$orderinfokey$/, @$colnames);
1338 $query .= "$orderinfokey=?, ";
1339 push(@params, $orderinfo->{$orderinfokey});
1342 $query .= "timestamp=NOW() WHERE ordernumber=?";
1343 push(@params, $orderinfo->{'ordernumber'} );
1344 $sth = $dbh->prepare($query);
1345 $sth->execute(@params);
1349 #------------------------------------------------------------#
1353 ModItemOrder($itemnumber, $ordernumber);
1355 Modifies the ordernumber of an item in aqorders_items.
1360 my ($itemnumber, $ordernumber) = @_;
1362 return unless ($itemnumber and $ordernumber);
1364 my $dbh = C4::Context->dbh;
1366 UPDATE aqorders_items
1368 WHERE itemnumber = ?
1370 my $sth = $dbh->prepare($query);
1371 return $sth->execute($ordernumber, $itemnumber);
1374 #------------------------------------------------------------#
1376 =head3 GetCancelledOrders
1378 my @orders = GetCancelledOrders($basketno, $orderby);
1380 Returns cancelled orders for a basket
1384 sub GetCancelledOrders {
1385 my ( $basketno, $orderby ) = @_;
1387 return () unless $basketno;
1389 my $dbh = C4::Context->dbh;
1396 aqorders_transfers.ordernumber_to AS transferred_to,
1397 aqorders_transfers.timestamp AS transferred_to_timestamp
1399 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1400 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1401 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1402 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber
1404 AND (datecancellationprinted IS NOT NULL
1405 AND datecancellationprinted <> '0000-00-00')
1408 $orderby = "aqorders.datecancellationprinted desc, aqorders.timestamp desc"
1410 $query .= " ORDER BY $orderby";
1411 my $sth = $dbh->prepare($query);
1412 $sth->execute($basketno);
1413 my $results = $sth->fetchall_arrayref( {} );
1419 #------------------------------------------------------------#
1421 =head3 ModReceiveOrder
1424 biblionumber => $biblionumber,
1425 ordernumber => $ordernumber,
1426 quantityreceived => $quantityreceived,
1430 invoiceid => $invoiceid,
1432 budget_id => $budget_id,
1433 datereceived => $datereceived,
1434 received_itemnumbers => \@received_itemnumbers,
1438 Updates an order, to reflect the fact that it was received, at least
1439 in part. All arguments not mentioned below update the fields with the
1440 same name in the aqorders table of the Koha database.
1442 If a partial order is received, splits the order into two.
1444 Updates the order with bibilionumber C<$biblionumber> and ordernumber
1450 sub ModReceiveOrder {
1451 my ( $params ) = @_;
1452 my $biblionumber = $params->{biblionumber};
1453 my $ordernumber = $params->{ordernumber};
1454 my $quantrec = $params->{quantityreceived};
1455 my $user = $params->{user};
1456 my $cost = $params->{cost};
1457 my $ecost = $params->{ecost};
1458 my $invoiceid = $params->{invoiceid};
1459 my $rrp = $params->{rrp};
1460 my $budget_id = $params->{budget_id};
1461 my $datereceived = $params->{datereceived};
1462 my $received_items = $params->{received_items};
1463 my $notes = $params->{notes};
1465 my $dbh = C4::Context->dbh;
1466 $datereceived = C4::Dates->output('iso') unless $datereceived;
1467 my $suggestionid = GetSuggestionFromBiblionumber( $biblionumber );
1468 if ($suggestionid) {
1469 ModSuggestion( {suggestionid=>$suggestionid,
1470 STATUS=>'AVAILABLE',
1471 biblionumber=> $biblionumber}
1475 my $result_set = $dbh->selectall_arrayref(
1476 q{SELECT * FROM aqorders WHERE biblionumber=? AND aqorders.ordernumber=?},
1477 { Slice => {} }, $biblionumber, $ordernumber
1480 # we assume we have a unique order
1481 my $order = $result_set->[0];
1483 my $new_ordernumber = $ordernumber;
1484 if ( $order->{quantity} > $quantrec ) {
1485 # Split order line in two parts: the first is the original order line
1486 # without received items (the quantity is decreased),
1487 # the second part is a new order line with quantity=quantityrec
1488 # (entirely received)
1492 orderstatus = 'partial'|;
1493 $query .= q|, notes = ?| if defined $notes;
1494 $query .= q| WHERE ordernumber = ?|;
1495 my $sth = $dbh->prepare($query);
1497 $sth->execute($order->{quantity} - $quantrec, ( defined $notes ? $notes : () ), $ordernumber);
1499 delete $order->{'ordernumber'};
1500 $order->{'budget_id'} = ( $budget_id || $order->{'budget_id'} );
1501 $order->{'quantity'} = $quantrec;
1502 $order->{'quantityreceived'} = $quantrec;
1503 $order->{'datereceived'} = $datereceived;
1504 $order->{'invoiceid'} = $invoiceid;
1505 $order->{'unitprice'} = $cost;
1506 $order->{'rrp'} = $rrp;
1507 $order->{ecost} = $ecost;
1508 $order->{'orderstatus'} = 'complete';
1510 ( $basketno, $new_ordernumber ) = NewOrder($order);
1512 if ($received_items) {
1513 foreach my $itemnumber (@$received_items) {
1514 ModItemOrder($itemnumber, $new_ordernumber);
1520 set quantityreceived=?,datereceived=?,invoiceid=?,
1521 unitprice=?,rrp=?,ecost=?,budget_id=?,orderstatus='complete'|;
1522 $query .= q|, notes = ?| if defined $notes;
1523 $query .= q| where biblionumber=? and ordernumber=?|;
1524 my $sth = $dbh->prepare( $query );
1525 $sth->execute($quantrec,$datereceived,$invoiceid,$cost,$rrp,$ecost,$budget_id,( defined $notes ? $notes : () ),$biblionumber,$ordernumber);
1527 return ($datereceived, $new_ordernumber);
1530 =head3 CancelReceipt
1532 my $parent_ordernumber = CancelReceipt($ordernumber);
1534 Cancel an order line receipt and update the parent order line, as if no
1536 If items are created at receipt (AcqCreateItem = receiving) then delete
1542 my $ordernumber = shift;
1544 return unless $ordernumber;
1546 my $dbh = C4::Context->dbh;
1548 SELECT datereceived, parent_ordernumber, quantity
1550 WHERE ordernumber = ?
1552 my $sth = $dbh->prepare($query);
1553 $sth->execute($ordernumber);
1554 my $order = $sth->fetchrow_hashref;
1556 warn "CancelReceipt: order $ordernumber does not exist";
1559 unless($order->{'datereceived'}) {
1560 warn "CancelReceipt: order $ordernumber is not received";
1564 my $parent_ordernumber = $order->{'parent_ordernumber'};
1566 if($parent_ordernumber == $ordernumber || not $parent_ordernumber) {
1567 # The order line has no parent, just mark it as not received
1570 SET quantityreceived = ?,
1573 orderstatus = 'ordered'
1574 WHERE ordernumber = ?
1576 $sth = $dbh->prepare($query);
1577 $sth->execute(0, undef, undef, $ordernumber);
1579 # The order line has a parent, increase parent quantity and delete
1582 SELECT quantity, datereceived
1584 WHERE ordernumber = ?
1586 $sth = $dbh->prepare($query);
1587 $sth->execute($parent_ordernumber);
1588 my $parent_order = $sth->fetchrow_hashref;
1589 unless($parent_order) {
1590 warn "Parent order $parent_ordernumber does not exist.";
1593 if($parent_order->{'datereceived'}) {
1594 warn "CancelReceipt: parent order is received.".
1595 " Can't cancel receipt.";
1601 orderstatus = 'ordered'
1602 WHERE ordernumber = ?
1604 $sth = $dbh->prepare($query);
1605 my $rv = $sth->execute(
1606 $order->{'quantity'} + $parent_order->{'quantity'},
1610 warn "Cannot update parent order line, so do not cancel".
1614 if(C4::Context->preference('AcqCreateItem') eq 'receiving') {
1615 # Remove items that were created at receipt
1617 DELETE FROM items, aqorders_items
1618 USING items, aqorders_items
1619 WHERE items.itemnumber = ? AND aqorders_items.itemnumber = ?
1621 $sth = $dbh->prepare($query);
1622 my @itemnumbers = GetItemnumbersFromOrder($ordernumber);
1623 foreach my $itemnumber (@itemnumbers) {
1624 $sth->execute($itemnumber, $itemnumber);
1628 my @itemnumbers = GetItemnumbersFromOrder($ordernumber);
1629 foreach my $itemnumber (@itemnumbers) {
1630 ModItemOrder($itemnumber, $parent_ordernumber);
1635 DELETE FROM aqorders
1636 WHERE ordernumber = ?
1638 $sth = $dbh->prepare($query);
1639 $sth->execute($ordernumber);
1643 return $parent_ordernumber;
1646 #------------------------------------------------------------#
1650 @results = &SearchOrders({
1651 ordernumber => $ordernumber,
1653 biblionumber => $biblionumber,
1655 booksellerid => $booksellerid,
1656 basketno => $basketno,
1662 Searches for orders.
1664 C<$owner> Finds order for the logged in user.
1665 C<$pending> Finds pending orders. Ignores completed and cancelled orders.
1666 C<$ordered> Finds orders to receive only (status 'ordered' or 'partial').
1669 C<@results> is an array of references-to-hash with the keys are fields
1670 from aqorders, biblio, biblioitems and aqbasket tables.
1675 my ( $params ) = @_;
1676 my $ordernumber = $params->{ordernumber};
1677 my $search = $params->{search};
1678 my $ean = $params->{ean};
1679 my $booksellerid = $params->{booksellerid};
1680 my $basketno = $params->{basketno};
1681 my $basketname = $params->{basketname};
1682 my $basketgroupname = $params->{basketgroupname};
1683 my $owner = $params->{owner};
1684 my $pending = $params->{pending};
1685 my $ordered = $params->{ordered};
1687 my $dbh = C4::Context->dbh;
1690 SELECT aqbasket.basketno,
1692 borrowers.firstname,
1695 biblioitems.biblioitemnumber,
1696 aqbasket.authorisedby,
1697 aqbasket.booksellerid,
1699 aqbasket.creationdate,
1700 aqbasket.basketname,
1701 aqbasketgroups.id as basketgroupid,
1702 aqbasketgroups.name as basketgroupname,
1705 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
1706 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
1707 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1708 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1709 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
1710 WHERE (datecancellationprinted is NULL)
1713 if ( $pending or $ordered ) {
1714 $query .= q{ AND (quantity > quantityreceived OR quantityreceived is NULL)};
1717 $query .= q{ AND aqorders.orderstatus IN ( "ordered", "partial" )};
1720 my $userenv = C4::Context->userenv;
1721 if ( C4::Context->preference("IndependentBranches") ) {
1722 unless ( C4::Context->IsSuperLibrarian() ) {
1725 borrowers.branchcode = ?
1726 OR borrowers.branchcode = ''
1729 push @args, $userenv->{branch};
1733 if ( $ordernumber ) {
1734 $query .= ' AND (aqorders.ordernumber=?)';
1735 push @args, $ordernumber;
1738 $query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)';
1739 push @args, ("%$search%","%$search%","%$search%");
1742 $query .= ' AND biblioitems.ean = ?';
1745 if ( $booksellerid ) {
1746 $query .= 'AND aqbasket.booksellerid = ?';
1747 push @args, $booksellerid;
1750 $query .= 'AND aqbasket.basketno = ?';
1751 push @args, $basketno;
1754 $query .= 'AND aqbasket.basketname LIKE ?';
1755 push @args, "%$basketname%";
1757 if( $basketgroupname ) {
1758 $query .= ' AND aqbasketgroups.name LIKE ?';
1759 push @args, "%$basketgroupname%";
1763 $query .= ' AND aqbasket.authorisedby=? ';
1764 push @args, $userenv->{'number'};
1767 $query .= ' ORDER BY aqbasket.basketno';
1769 my $sth = $dbh->prepare($query);
1770 $sth->execute(@args);
1771 return $sth->fetchall_arrayref({});
1774 #------------------------------------------------------------#
1778 &DelOrder($biblionumber, $ordernumber);
1780 Cancel the order with the given order and biblio numbers. It does not
1781 delete any entries in the aqorders table, it merely marks them as
1787 my ( $bibnum, $ordernumber ) = @_;
1788 my $dbh = C4::Context->dbh;
1791 SET datecancellationprinted=now(), orderstatus='cancelled'
1792 WHERE biblionumber=? AND ordernumber=?
1794 my $sth = $dbh->prepare($query);
1795 $sth->execute( $bibnum, $ordernumber );
1796 my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1797 foreach my $itemnumber (@itemnumbers){
1798 C4::Items::DelItem( $dbh, $bibnum, $itemnumber );
1803 =head3 TransferOrder
1805 my $newordernumber = TransferOrder($ordernumber, $basketno);
1807 Transfer an order line to a basket.
1808 Mark $ordernumber as cancelled with an internal note 'Cancelled and transfered
1809 to BOOKSELLER on DATE' and create new order with internal note
1810 'Transfered from BOOKSELLER on DATE'.
1811 Move all attached items to the new order.
1812 Received orders cannot be transfered.
1813 Return the ordernumber of created order.
1818 my ($ordernumber, $basketno) = @_;
1820 return unless ($ordernumber and $basketno);
1822 my $order = GetOrder( $ordernumber );
1823 return if $order->{datereceived};
1824 my $basket = GetBasket($basketno);
1825 return unless $basket;
1827 my $dbh = C4::Context->dbh;
1828 my ($query, $sth, $rv);
1832 SET datecancellationprinted = CAST(NOW() AS date)
1833 WHERE ordernumber = ?
1835 $sth = $dbh->prepare($query);
1836 $rv = $sth->execute($ordernumber);
1838 delete $order->{'ordernumber'};
1839 delete $order->{parent_ordernumber};
1840 $order->{'basketno'} = $basketno;
1842 (undef, $newordernumber) = NewOrder($order);
1845 UPDATE aqorders_items
1847 WHERE ordernumber = ?
1849 $sth = $dbh->prepare($query);
1850 $sth->execute($newordernumber, $ordernumber);
1853 INSERT INTO aqorders_transfers (ordernumber_from, ordernumber_to)
1856 $sth = $dbh->prepare($query);
1857 $sth->execute($ordernumber, $newordernumber);
1859 return $newordernumber;
1862 =head2 FUNCTIONS ABOUT PARCELS
1866 #------------------------------------------------------------#
1870 @results = &GetParcel($booksellerid, $code, $date);
1872 Looks up all of the received items from the supplier with the given
1873 bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders.
1875 C<@results> is an array of references-to-hash. The keys of each element are fields from
1876 the aqorders, biblio, and biblioitems tables of the Koha database.
1878 C<@results> is sorted alphabetically by book title.
1883 #gets all orders from a certain supplier, orders them alphabetically
1884 my ( $supplierid, $code, $datereceived ) = @_;
1885 my $dbh = C4::Context->dbh;
1888 if $code; # add % if we search on a given code (otherwise, let him empty)
1890 SELECT authorisedby,
1895 aqorders.biblionumber,
1896 aqorders.ordernumber,
1897 aqorders.parent_ordernumber,
1899 aqorders.quantityreceived,
1907 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
1908 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1909 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1910 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
1912 aqbasket.booksellerid = ?
1913 AND aqinvoices.invoicenumber LIKE ?
1914 AND aqorders.datereceived = ? ";
1916 my @query_params = ( $supplierid, $code, $datereceived );
1917 if ( C4::Context->preference("IndependentBranches") ) {
1918 unless ( C4::Context->IsSuperLibrarian() ) {
1919 $strsth .= " and (borrowers.branchcode = ?
1920 or borrowers.branchcode = '')";
1921 push @query_params, C4::Context->userenv->{branch};
1924 $strsth .= " ORDER BY aqbasket.basketno";
1925 my $result_set = $dbh->selectall_arrayref(
1930 return @{$result_set};
1933 #------------------------------------------------------------#
1937 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
1939 get a lists of parcels.
1946 is the bookseller this function has to get parcels.
1949 To know on what criteria the results list has to be ordered.
1952 is the booksellerinvoicenumber.
1954 =item $datefrom & $dateto
1955 to know on what date this function has to filter its search.
1960 a pointer on a hash list containing parcel informations as such :
1966 =item Last operation
1968 =item Number of biblio
1970 =item Number of items
1977 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
1978 my $dbh = C4::Context->dbh;
1979 my @query_params = ();
1981 SELECT aqinvoices.invoicenumber,
1982 datereceived,purchaseordernumber,
1983 count(DISTINCT biblionumber) AS biblio,
1984 sum(quantity) AS itemsexpected,
1985 sum(quantityreceived) AS itemsreceived
1986 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
1987 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
1988 WHERE aqbasket.booksellerid = ? and datereceived IS NOT NULL
1990 push @query_params, $bookseller;
1992 if ( defined $code ) {
1993 $strsth .= ' and aqinvoices.invoicenumber like ? ';
1994 # add a % to the end of the code to allow stemming.
1995 push @query_params, "$code%";
1998 if ( defined $datefrom ) {
1999 $strsth .= ' and datereceived >= ? ';
2000 push @query_params, $datefrom;
2003 if ( defined $dateto ) {
2004 $strsth .= 'and datereceived <= ? ';
2005 push @query_params, $dateto;
2008 $strsth .= "group by aqinvoices.invoicenumber,datereceived ";
2010 # can't use a placeholder to place this column name.
2011 # but, we could probably be checking to make sure it is a column that will be fetched.
2012 $strsth .= "order by $order " if ($order);
2014 my $sth = $dbh->prepare($strsth);
2016 $sth->execute( @query_params );
2017 my $results = $sth->fetchall_arrayref({});
2021 #------------------------------------------------------------#
2023 =head3 GetLateOrders
2025 @results = &GetLateOrders;
2027 Searches for bookseller with late orders.
2030 the table of supplier with late issues. This table is full of hashref.
2036 my $supplierid = shift;
2038 my $estimateddeliverydatefrom = shift;
2039 my $estimateddeliverydateto = shift;
2041 my $dbh = C4::Context->dbh;
2043 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
2044 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
2046 my @query_params = ();
2048 SELECT aqbasket.basketno,
2049 aqorders.ordernumber,
2050 DATE(aqbasket.closedate) AS orderdate,
2051 aqorders.rrp AS unitpricesupplier,
2052 aqorders.ecost AS unitpricelib,
2053 aqorders.claims_count AS claims_count,
2054 aqorders.claimed_date AS claimed_date,
2055 aqbudgets.budget_name AS budget,
2056 borrowers.branchcode AS branch,
2057 aqbooksellers.name AS supplier,
2058 aqbooksellers.id AS supplierid,
2059 biblio.author, biblio.title,
2060 biblioitems.publishercode AS publisher,
2061 biblioitems.publicationyear,
2062 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
2066 aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
2067 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
2068 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
2069 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
2070 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
2071 WHERE aqorders.basketno = aqbasket.basketno
2072 AND ( datereceived = ''
2073 OR datereceived IS NULL
2074 OR aqorders.quantityreceived < aqorders.quantity
2076 AND aqbasket.closedate IS NOT NULL
2077 AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
2080 if ($dbdriver eq "mysql") {
2082 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity,
2083 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
2084 DATEDIFF(CAST(now() AS date),closedate) AS latesince
2086 if ( defined $delay ) {
2087 $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ;
2088 push @query_params, $delay;
2091 HAVING quantity <> 0
2092 AND unitpricesupplier <> 0
2093 AND unitpricelib <> 0
2096 # FIXME: account for IFNULL as above
2098 aqorders.quantity AS quantity,
2099 aqorders.quantity * aqorders.rrp AS subtotal,
2100 (CAST(now() AS date) - closedate) AS latesince
2102 if ( defined $delay ) {
2103 $from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) ";
2104 push @query_params, $delay;
2107 if (defined $supplierid) {
2108 $from .= ' AND aqbasket.booksellerid = ? ';
2109 push @query_params, $supplierid;
2111 if (defined $branch) {
2112 $from .= ' AND borrowers.branchcode LIKE ? ';
2113 push @query_params, $branch;
2116 if ( defined $estimateddeliverydatefrom or defined $estimateddeliverydateto ) {
2117 $from .= ' AND aqbooksellers.deliverytime IS NOT NULL ';
2119 if ( defined $estimateddeliverydatefrom ) {
2120 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
2121 push @query_params, $estimateddeliverydatefrom;
2123 if ( defined $estimateddeliverydateto ) {
2124 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
2125 push @query_params, $estimateddeliverydateto;
2127 if ( defined $estimateddeliverydatefrom and not defined $estimateddeliverydateto ) {
2128 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
2130 if (C4::Context->preference("IndependentBranches")
2131 && !C4::Context->IsSuperLibrarian() ) {
2132 $from .= ' AND borrowers.branchcode LIKE ? ';
2133 push @query_params, C4::Context->userenv->{branch};
2135 $from .= " AND orderstatus <> 'cancelled' ";
2136 my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier";
2137 $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params);
2138 my $sth = $dbh->prepare($query);
2139 $sth->execute(@query_params);
2141 while (my $data = $sth->fetchrow_hashref) {
2142 $data->{orderdate} = format_date($data->{orderdate});
2143 $data->{claimed_date} = format_date($data->{claimed_date});
2144 push @results, $data;
2149 #------------------------------------------------------------#
2153 (\@order_loop, $total_qty, $total_price, $total_qtyreceived) = GetHistory( %params );
2155 Retreives some acquisition history information
2165 basket - search both basket name and number
2166 booksellerinvoicenumber
2169 orderstatus (note that orderstatus '' will retrieve orders
2170 of any status except cancelled)
2172 get_canceled_order (if set to a true value, cancelled orders will
2176 $order_loop is a list of hashrefs that each look like this:
2178 'author' => 'Twain, Mark',
2180 'biblionumber' => '215',
2182 'creationdate' => 'MM/DD/YYYY',
2183 'datereceived' => undef,
2186 'invoicenumber' => undef,
2188 'ordernumber' => '1',
2190 'quantityreceived' => undef,
2191 'title' => 'The Adventures of Huckleberry Finn'
2193 $total_qty is the sum of all of the quantities in $order_loop
2194 $total_price is the cost of each in $order_loop times the quantity
2195 $total_qtyreceived is the sum of all of the quantityreceived entries in $order_loop
2200 # don't run the query if there are no parameters (list would be too long for sure !)
2201 croak "No search params" unless @_;
2203 my $title = $params{title};
2204 my $author = $params{author};
2205 my $isbn = $params{isbn};
2206 my $ean = $params{ean};
2207 my $name = $params{name};
2208 my $from_placed_on = $params{from_placed_on};
2209 my $to_placed_on = $params{to_placed_on};
2210 my $basket = $params{basket};
2211 my $booksellerinvoicenumber = $params{booksellerinvoicenumber};
2212 my $basketgroupname = $params{basketgroupname};
2213 my $budget = $params{budget};
2214 my $orderstatus = $params{orderstatus};
2215 my $biblionumber = $params{biblionumber};
2216 my $get_canceled_order = $params{get_canceled_order} || 0;
2220 my $total_qtyreceived = 0;
2221 my $total_price = 0;
2223 my $dbh = C4::Context->dbh;
2226 COALESCE(biblio.title, deletedbiblio.title) AS title,
2227 COALESCE(biblio.author, deletedbiblio.author) AS author,
2228 COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn,
2229 COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean,
2231 aqbasket.basketname,
2232 aqbasket.basketgroupid,
2233 aqbasketgroups.name as groupname,
2235 aqbasket.creationdate,
2236 aqorders.datereceived,
2238 aqorders.quantityreceived,
2240 aqorders.ordernumber,
2242 aqinvoices.invoicenumber,
2243 aqbooksellers.id as id,
2244 aqorders.biblionumber,
2245 aqorders.orderstatus,
2246 aqorders.parent_ordernumber,
2247 aqbudgets.budget_name
2249 $query .= ", aqbudgets.budget_id AS budget" if defined $budget;
2252 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
2253 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
2254 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
2255 LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
2256 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
2257 LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id
2258 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2259 LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.biblionumber
2260 LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=aqorders.biblionumber
2263 if ( C4::Context->preference("IndependentBranches") ) {
2264 $query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber";
2267 $query .= " WHERE 1 ";
2269 unless ($get_canceled_order or (defined $orderstatus and $orderstatus eq 'cancelled')) {
2270 $query .= " AND (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') ";
2273 my @query_params = ();
2275 if ( $biblionumber ) {
2276 $query .= " AND biblio.biblionumber = ?";
2277 push @query_params, $biblionumber;
2281 $query .= " AND biblio.title LIKE ? ";
2282 $title =~ s/\s+/%/g;
2283 push @query_params, "%$title%";
2287 $query .= " AND biblio.author LIKE ? ";
2288 push @query_params, "%$author%";
2292 $query .= " AND biblioitems.isbn LIKE ? ";
2293 push @query_params, "%$isbn%";
2296 $query .= " AND biblioitems.ean = ? ";
2297 push @query_params, "$ean";
2300 $query .= " AND aqbooksellers.name LIKE ? ";
2301 push @query_params, "%$name%";
2305 $query .= " AND aqbudgets.budget_id = ? ";
2306 push @query_params, "$budget";
2309 if ( $from_placed_on ) {
2310 $query .= " AND creationdate >= ? ";
2311 push @query_params, $from_placed_on;
2314 if ( $to_placed_on ) {
2315 $query .= " AND creationdate <= ? ";
2316 push @query_params, $to_placed_on;
2319 if ( defined $orderstatus and $orderstatus ne '') {
2320 $query .= " AND aqorders.orderstatus = ? ";
2321 push @query_params, "$orderstatus";
2325 if ($basket =~ m/^\d+$/) {
2326 $query .= " AND aqorders.basketno = ? ";
2327 push @query_params, $basket;
2329 $query .= " AND aqbasket.basketname LIKE ? ";
2330 push @query_params, "%$basket%";
2334 if ($booksellerinvoicenumber) {
2335 $query .= " AND aqinvoices.invoicenumber LIKE ? ";
2336 push @query_params, "%$booksellerinvoicenumber%";
2339 if ($basketgroupname) {
2340 $query .= " AND aqbasketgroups.name LIKE ? ";
2341 push @query_params, "%$basketgroupname%";
2344 if ( C4::Context->preference("IndependentBranches") ) {
2345 unless ( C4::Context->IsSuperLibrarian() ) {
2346 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
2347 push @query_params, C4::Context->userenv->{branch};
2350 $query .= " ORDER BY id";
2351 my $sth = $dbh->prepare($query);
2352 $sth->execute( @query_params );
2354 while ( my $line = $sth->fetchrow_hashref ) {
2355 $line->{count} = $cnt++;
2356 $line->{toggle} = 1 if $cnt % 2;
2357 push @order_loop, $line;
2358 $total_qty += ( $line->{quantity} ) ? $line->{quantity} : 0;
2359 $total_qtyreceived += ( $line->{quantityreceived} ) ? $line->{quantityreceived} : 0;
2360 $total_price += ( $line->{quantity} and $line->{ecost} ) ? $line->{quantity} * $line->{ecost} : 0;
2362 return \@order_loop, $total_qty, $total_price, $total_qtyreceived;
2365 =head2 GetRecentAcqui
2367 $results = GetRecentAcqui($days);
2369 C<$results> is a ref to a table which containts hashref
2373 sub GetRecentAcqui {
2375 my $dbh = C4::Context->dbh;
2379 ORDER BY timestamp DESC
2382 my $sth = $dbh->prepare($query);
2384 my $results = $sth->fetchall_arrayref({});
2390 $contractlist = &GetContracts($booksellerid, $activeonly);
2392 Looks up the contracts that belong to a bookseller
2394 Returns a list of contracts
2398 =item C<$booksellerid> is the "id" field in the "aqbooksellers" table.
2400 =item C<$activeonly> if exists get only contracts that are still active.
2407 my ( $booksellerid, $activeonly ) = @_;
2408 my $dbh = C4::Context->dbh;
2410 if (! $activeonly) {
2414 WHERE booksellerid=?
2419 WHERE booksellerid=?
2420 AND contractenddate >= CURDATE( )";
2423 $dbh->selectall_arrayref( $query, { Slice => {} }, $booksellerid );
2424 return @{$result_set};
2427 #------------------------------------------------------------#
2431 $contract = &GetContract($contractID);
2433 Looks up the contract that has PRIMKEY (contractnumber) value $contractID
2440 my ( $contractno ) = @_;
2441 my $dbh = C4::Context->dbh;
2445 WHERE contractnumber=?
2448 my $sth = $dbh->prepare($query);
2449 $sth->execute( $contractno );
2450 my $result = $sth->fetchrow_hashref;
2458 &AddClaim($ordernumber);
2460 Add a claim for an order
2467 my ($ordernumber) = @_;
2468 my $dbh = C4::Context->dbh;
2471 claims_count = claims_count + 1,
2472 claimed_date = CURDATE()
2473 WHERE ordernumber = ?
2475 my $sth = $dbh->prepare($query);
2476 $sth->execute($ordernumber);
2481 my @invoices = GetInvoices(
2482 invoicenumber => $invoicenumber,
2483 supplierid => $supplierid,
2484 suppliername => $suppliername,
2485 shipmentdatefrom => $shipmentdatefrom, # ISO format
2486 shipmentdateto => $shipmentdateto, # ISO format
2487 billingdatefrom => $billingdatefrom, # ISO format
2488 billingdateto => $billingdateto, # ISO format
2489 isbneanissn => $isbn_or_ean_or_issn,
2492 publisher => $publisher,
2493 publicationyear => $publicationyear,
2494 branchcode => $branchcode,
2495 order_by => $order_by
2498 Return a list of invoices that match all given criteria.
2500 $order_by is "column_name (asc|desc)", where column_name is any of
2501 'invoicenumber', 'booksellerid', 'shipmentdate', 'billingdate', 'closedate',
2502 'shipmentcost', 'shipmentcost_budgetid'.
2504 asc is the default if omitted
2511 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2512 closedate shipmentcost shipmentcost_budgetid);
2514 my $dbh = C4::Context->dbh;
2516 SELECT aqinvoices.*, aqbooksellers.name AS suppliername,
2519 aqorders.datereceived IS NOT NULL,
2520 aqorders.biblionumber,
2523 ) AS receivedbiblios,
2524 SUM(aqorders.quantityreceived) AS receiveditems
2526 LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid
2527 LEFT JOIN aqorders ON aqorders.invoiceid = aqinvoices.invoiceid
2528 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
2529 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2530 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2531 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
2532 LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber
2537 if($args{supplierid}) {
2538 push @bind_strs, " aqinvoices.booksellerid = ? ";
2539 push @bind_args, $args{supplierid};
2541 if($args{invoicenumber}) {
2542 push @bind_strs, " aqinvoices.invoicenumber LIKE ? ";
2543 push @bind_args, "%$args{invoicenumber}%";
2545 if($args{suppliername}) {
2546 push @bind_strs, " aqbooksellers.name LIKE ? ";
2547 push @bind_args, "%$args{suppliername}%";
2549 if($args{shipmentdatefrom}) {
2550 push @bind_strs, " aqinvoices.shipmentdate >= ? ";
2551 push @bind_args, $args{shipmentdatefrom};
2553 if($args{shipmentdateto}) {
2554 push @bind_strs, " aqinvoices.shipmentdate <= ? ";
2555 push @bind_args, $args{shipmentdateto};
2557 if($args{billingdatefrom}) {
2558 push @bind_strs, " aqinvoices.billingdate >= ? ";
2559 push @bind_args, $args{billingdatefrom};
2561 if($args{billingdateto}) {
2562 push @bind_strs, " aqinvoices.billingdate <= ? ";
2563 push @bind_args, $args{billingdateto};
2565 if($args{isbneanissn}) {
2566 push @bind_strs, " (biblioitems.isbn LIKE CONCAT('%', ?, '%') OR biblioitems.ean LIKE CONCAT('%', ?, '%') OR biblioitems.issn LIKE CONCAT('%', ?, '%') ) ";
2567 push @bind_args, $args{isbneanissn}, $args{isbneanissn}, $args{isbneanissn};
2570 push @bind_strs, " biblio.title LIKE CONCAT('%', ?, '%') ";
2571 push @bind_args, $args{title};
2574 push @bind_strs, " biblio.author LIKE CONCAT('%', ?, '%') ";
2575 push @bind_args, $args{author};
2577 if($args{publisher}) {
2578 push @bind_strs, " biblioitems.publishercode LIKE CONCAT('%', ?, '%') ";
2579 push @bind_args, $args{publisher};
2581 if($args{publicationyear}) {
2582 push @bind_strs, " ((biblioitems.publicationyear LIKE CONCAT('%', ?, '%')) OR (biblio.copyrightdate LIKE CONCAT('%', ?, '%'))) ";
2583 push @bind_args, $args{publicationyear}, $args{publicationyear};
2585 if($args{branchcode}) {
2586 push @bind_strs, " borrowers.branchcode = ? ";
2587 push @bind_args, $args{branchcode};
2590 $query .= " WHERE " . join(" AND ", @bind_strs) if @bind_strs;
2591 $query .= " GROUP BY aqinvoices.invoiceid ";
2593 if($args{order_by}) {
2594 my ($column, $direction) = split / /, $args{order_by};
2595 if(grep /^$column$/, @columns) {
2596 $direction ||= 'ASC';
2597 $query .= " ORDER BY $column $direction";
2601 my $sth = $dbh->prepare($query);
2602 $sth->execute(@bind_args);
2604 my $results = $sth->fetchall_arrayref({});
2610 my $invoice = GetInvoice($invoiceid);
2612 Get informations about invoice with given $invoiceid
2614 Return a hash filled with aqinvoices.* fields
2619 my ($invoiceid) = @_;
2622 return unless $invoiceid;
2624 my $dbh = C4::Context->dbh;
2630 my $sth = $dbh->prepare($query);
2631 $sth->execute($invoiceid);
2633 $invoice = $sth->fetchrow_hashref;
2637 =head3 GetInvoiceDetails
2639 my $invoice = GetInvoiceDetails($invoiceid)
2641 Return informations about an invoice + the list of related order lines
2643 Orders informations are in $invoice->{orders} (array ref)
2647 sub GetInvoiceDetails {
2648 my ($invoiceid) = @_;
2650 if ( !defined $invoiceid ) {
2651 carp 'GetInvoiceDetails called without an invoiceid';
2655 my $dbh = C4::Context->dbh;
2657 SELECT aqinvoices.*, aqbooksellers.name AS suppliername
2659 LEFT JOIN aqbooksellers ON aqinvoices.booksellerid = aqbooksellers.id
2662 my $sth = $dbh->prepare($query);
2663 $sth->execute($invoiceid);
2665 my $invoice = $sth->fetchrow_hashref;
2668 SELECT aqorders.*, biblio.*, aqbasket.basketname
2670 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
2671 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2674 $sth = $dbh->prepare($query);
2675 $sth->execute($invoiceid);
2676 $invoice->{orders} = $sth->fetchall_arrayref({});
2677 $invoice->{orders} ||= []; # force an empty arrayref if fetchall_arrayref fails
2684 my $invoiceid = AddInvoice(
2685 invoicenumber => $invoicenumber,
2686 booksellerid => $booksellerid,
2687 shipmentdate => $shipmentdate,
2688 billingdate => $billingdate,
2689 closedate => $closedate,
2690 shipmentcost => $shipmentcost,
2691 shipmentcost_budgetid => $shipmentcost_budgetid
2694 Create a new invoice and return its id or undef if it fails.
2701 return unless(%invoice and $invoice{invoicenumber});
2703 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2704 closedate shipmentcost shipmentcost_budgetid);
2708 foreach my $key (keys %invoice) {
2709 if(0 < grep(/^$key$/, @columns)) {
2710 push @set_strs, "$key = ?";
2711 push @set_args, ($invoice{$key} || undef);
2717 my $dbh = C4::Context->dbh;
2718 my $query = "INSERT INTO aqinvoices SET ";
2719 $query .= join (",", @set_strs);
2720 my $sth = $dbh->prepare($query);
2721 $rv = $sth->execute(@set_args);
2723 $rv = $dbh->last_insert_id(undef, undef, 'aqinvoices', undef);
2732 invoiceid => $invoiceid, # Mandatory
2733 invoicenumber => $invoicenumber,
2734 booksellerid => $booksellerid,
2735 shipmentdate => $shipmentdate,
2736 billingdate => $billingdate,
2737 closedate => $closedate,
2738 shipmentcost => $shipmentcost,
2739 shipmentcost_budgetid => $shipmentcost_budgetid
2742 Modify an invoice, invoiceid is mandatory.
2744 Return undef if it fails.
2751 return unless(%invoice and $invoice{invoiceid});
2753 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2754 closedate shipmentcost shipmentcost_budgetid);
2758 foreach my $key (keys %invoice) {
2759 if(0 < grep(/^$key$/, @columns)) {
2760 push @set_strs, "$key = ?";
2761 push @set_args, ($invoice{$key} || undef);
2765 my $dbh = C4::Context->dbh;
2766 my $query = "UPDATE aqinvoices SET ";
2767 $query .= join(",", @set_strs);
2768 $query .= " WHERE invoiceid = ?";
2770 my $sth = $dbh->prepare($query);
2771 $sth->execute(@set_args, $invoice{invoiceid});
2776 CloseInvoice($invoiceid);
2780 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => undef);
2785 my ($invoiceid) = @_;
2787 return unless $invoiceid;
2789 my $dbh = C4::Context->dbh;
2792 SET closedate = CAST(NOW() AS DATE)
2795 my $sth = $dbh->prepare($query);
2796 $sth->execute($invoiceid);
2799 =head3 ReopenInvoice
2801 ReopenInvoice($invoiceid);
2805 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => C4::Dates->new()->output('iso'))
2810 my ($invoiceid) = @_;
2812 return unless $invoiceid;
2814 my $dbh = C4::Context->dbh;
2817 SET closedate = NULL
2820 my $sth = $dbh->prepare($query);
2821 $sth->execute($invoiceid);
2826 DelInvoice($invoiceid);
2828 Delete an invoice if there are no items attached to it.
2833 my ($invoiceid) = @_;
2835 return unless $invoiceid;
2837 my $dbh = C4::Context->dbh;
2843 my $sth = $dbh->prepare($query);
2844 $sth->execute($invoiceid);
2845 my $res = $sth->fetchrow_arrayref;
2846 if ( $res && $res->[0] == 0 ) {
2848 DELETE FROM aqinvoices
2851 my $sth = $dbh->prepare($query);
2852 return ( $sth->execute($invoiceid) > 0 );
2857 =head3 MergeInvoices
2859 MergeInvoices($invoiceid, \@sourceids);
2861 Merge the invoices identified by the IDs in \@sourceids into
2862 the invoice identified by $invoiceid.
2867 my ($invoiceid, $sourceids) = @_;
2869 return unless $invoiceid;
2870 foreach my $sourceid (@$sourceids) {
2871 next if $sourceid == $invoiceid;
2872 my $source = GetInvoiceDetails($sourceid);
2873 foreach my $order (@{$source->{'orders'}}) {
2874 $order->{'invoiceid'} = $invoiceid;
2877 DelInvoice($source->{'invoiceid'});
2882 =head3 GetBiblioCountByBasketno
2884 $biblio_count = &GetBiblioCountByBasketno($basketno);
2886 Looks up the biblio's count that has basketno value $basketno
2892 sub GetBiblioCountByBasketno {
2893 my ($basketno) = @_;
2894 my $dbh = C4::Context->dbh;
2896 SELECT COUNT( DISTINCT( biblionumber ) )
2899 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
2902 my $sth = $dbh->prepare($query);
2903 $sth->execute($basketno);
2904 return $sth->fetchrow;
2912 Koha Development Team <http://koha-community.org/>