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 infermation 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 my $dbh = C4::Context->dbh;
1032 SELECT biblio.*,biblioitems.*,
1036 aqorders_transfers.ordernumber_from AS transferred_from,
1037 aqorders_transfers.timestamp AS transferred_from_timestamp
1039 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1040 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1041 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1042 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1044 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
1047 $orderby = "biblioitems.publishercode,biblio.title" unless $orderby;
1048 $query .= " ORDER BY $orderby";
1050 $dbh->selectall_arrayref( $query, { Slice => {} }, $basketno );
1051 return @{$result_set};
1055 #------------------------------------------------------------#
1056 =head3 GetOrdersByBiblionumber
1058 @orders = &GetOrdersByBiblionumber($biblionumber);
1060 Looks up the orders with linked to a specific $biblionumber, including
1061 cancelled orders and received orders.
1064 C<@orders> is an array of references-to-hash, whose keys are the
1065 fields from the aqorders, biblio, and biblioitems tables in the Koha database.
1069 sub GetOrdersByBiblionumber {
1070 my $biblionumber = shift;
1071 return unless $biblionumber;
1072 my $dbh = C4::Context->dbh;
1074 SELECT biblio.*,biblioitems.*,
1078 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1079 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1080 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1081 WHERE aqorders.biblionumber=?
1084 $dbh->selectall_arrayref( $query, { Slice => {} }, $biblionumber );
1085 return @{$result_set};
1089 #------------------------------------------------------------#
1093 $order = &GetOrder($ordernumber);
1095 Looks up an order by order number.
1097 Returns a reference-to-hash describing the order. The keys of
1098 C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database.
1103 my ($ordernumber) = @_;
1104 my $dbh = C4::Context->dbh;
1105 my $query = qq{SELECT
1109 aqbasket.basketname,
1110 borrowers.branchcode,
1111 biblioitems.publicationyear,
1112 biblio.copyrightdate,
1113 biblioitems.editionstatement,
1117 biblioitems.publishercode,
1118 aqorders.rrp AS unitpricesupplier,
1119 aqorders.ecost AS unitpricelib,
1120 aqorders.claims_count AS claims_count,
1121 aqorders.claimed_date AS claimed_date,
1122 aqbudgets.budget_name AS budget,
1123 aqbooksellers.name AS supplier,
1124 aqbooksellers.id AS supplierid,
1125 biblioitems.publishercode AS publisher,
1126 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
1127 DATE(aqbasket.closedate) AS orderdate,
1128 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity_to_receive,
1129 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1130 DATEDIFF(CURDATE( ),closedate) AS latesince
1131 FROM aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1132 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1133 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
1134 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
1135 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1136 WHERE aqorders.basketno = aqbasket.basketno
1139 $dbh->selectall_arrayref( $query, { Slice => {} }, $ordernumber );
1141 # result_set assumed to contain 1 match
1142 return $result_set->[0];
1145 =head3 GetLastOrderNotReceivedFromSubscriptionid
1147 $order = &GetLastOrderNotReceivedFromSubscriptionid($subscriptionid);
1149 Returns a reference-to-hash describing the last order not received for a subscription.
1153 sub GetLastOrderNotReceivedFromSubscriptionid {
1154 my ( $subscriptionid ) = @_;
1155 my $dbh = C4::Context->dbh;
1157 SELECT * FROM aqorders
1158 LEFT JOIN subscription
1159 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1160 WHERE aqorders.subscriptionid = ?
1161 AND aqorders.datereceived IS NULL
1165 $dbh->selectall_arrayref( $query, { Slice => {} }, $subscriptionid );
1167 # result_set assumed to contain 1 match
1168 return $result_set->[0];
1171 =head3 GetLastOrderReceivedFromSubscriptionid
1173 $order = &GetLastOrderReceivedFromSubscriptionid($subscriptionid);
1175 Returns a reference-to-hash describing the last order received for a subscription.
1179 sub GetLastOrderReceivedFromSubscriptionid {
1180 my ( $subscriptionid ) = @_;
1181 my $dbh = C4::Context->dbh;
1183 SELECT * FROM aqorders
1184 LEFT JOIN subscription
1185 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1186 WHERE aqorders.subscriptionid = ?
1187 AND aqorders.datereceived =
1189 SELECT MAX( aqorders.datereceived )
1191 LEFT JOIN subscription
1192 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1193 WHERE aqorders.subscriptionid = ?
1194 AND aqorders.datereceived IS NOT NULL
1196 ORDER BY ordernumber DESC
1200 $dbh->selectall_arrayref( $query, { Slice => {} }, $subscriptionid );
1202 # result_set assumed to contain 1 match
1203 return $result_set->[0];
1208 #------------------------------------------------------------#
1212 &NewOrder(\%hashref);
1214 Adds a new order to the database. Any argument that isn't described
1215 below is the new value of the field with the same name in the aqorders
1216 table of the Koha database.
1220 =item $hashref->{'basketno'} is the basketno foreign key in aqorders, it is mandatory
1222 =item $hashref->{'ordernumber'} is a "minimum order number."
1224 =item $hashref->{'budgetdate'} is effectively ignored.
1225 If it's undef (anything false) or the string 'now', the current day is used.
1226 Else, the upcoming July 1st is used.
1228 =item $hashref->{'subscription'} may be either "yes", or anything else for "no".
1230 =item $hashref->{'uncertainprice'} may be 0 for "the price is known" or 1 for "the price is uncertain"
1232 =item defaults entrydate to Now
1234 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".
1241 my $orderinfo = shift;
1243 my $dbh = C4::Context->dbh;
1247 # if these parameters are missing, we can't continue
1248 for my $key (qw/basketno quantity biblionumber budget_id/) {
1249 croak "Mandatory parameter $key missing" unless $orderinfo->{$key};
1252 if ( defined $orderinfo->{subscription} && $orderinfo->{'subscription'} eq 'yes' ) {
1253 $orderinfo->{'subscription'} = 1;
1255 $orderinfo->{'subscription'} = 0;
1257 $orderinfo->{'entrydate'} ||= C4::Dates->new()->output("iso");
1258 if (!$orderinfo->{quantityreceived}) {
1259 $orderinfo->{quantityreceived} = 0;
1262 my $ordernumber=InsertInTable("aqorders",$orderinfo);
1263 if (not $orderinfo->{parent_ordernumber}) {
1264 my $sth = $dbh->prepare("
1266 SET parent_ordernumber = ordernumber
1267 WHERE ordernumber = ?
1269 $sth->execute($ordernumber);
1271 return ( $orderinfo->{'basketno'}, $ordernumber );
1276 #------------------------------------------------------------#
1285 my ($itemnumber, $ordernumber) = @_;
1286 my $dbh = C4::Context->dbh;
1288 INSERT INTO aqorders_items
1289 (itemnumber, ordernumber)
1292 my $sth = $dbh->prepare($query);
1293 $sth->execute( $itemnumber, $ordernumber);
1296 #------------------------------------------------------------#
1300 &ModOrder(\%hashref);
1302 Modifies an existing order. Updates the order with order number
1303 $hashref->{'ordernumber'} and biblionumber $hashref->{'biblionumber'}. All
1304 other keys of the hash update the fields with the same name in the aqorders
1305 table of the Koha database.
1310 my $orderinfo = shift;
1312 die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq '' ;
1313 die "Biblionumber is required" if $orderinfo->{'biblionumber'} eq '';
1315 my $dbh = C4::Context->dbh;
1318 # update uncertainprice to an integer, just in case (under FF, checked boxes have the value "ON" by default)
1319 $orderinfo->{uncertainprice}=1 if $orderinfo->{uncertainprice};
1321 # delete($orderinfo->{'branchcode'});
1322 # the hash contains a lot of entries not in aqorders, so get the columns ...
1323 my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;");
1325 my $colnames = $sth->{NAME};
1326 #FIXME Be careful. If aqorders would have columns with diacritics,
1327 #you should need to decode what you get back from NAME.
1328 #See report 10110 and guided_reports.pl
1329 my $query = "UPDATE aqorders SET ";
1331 foreach my $orderinfokey (grep(!/ordernumber/, keys %$orderinfo)){
1332 # ... and skip hash entries that are not in the aqorders table
1333 # FIXME : probably not the best way to do it (would be better to have a correct hash)
1334 next unless grep(/^$orderinfokey$/, @$colnames);
1335 $query .= "$orderinfokey=?, ";
1336 push(@params, $orderinfo->{$orderinfokey});
1339 $query .= "timestamp=NOW() WHERE ordernumber=?";
1340 push(@params, $orderinfo->{'ordernumber'} );
1341 $sth = $dbh->prepare($query);
1342 $sth->execute(@params);
1346 #------------------------------------------------------------#
1350 ModItemOrder($itemnumber, $ordernumber);
1352 Modifies the ordernumber of an item in aqorders_items.
1357 my ($itemnumber, $ordernumber) = @_;
1359 return unless ($itemnumber and $ordernumber);
1361 my $dbh = C4::Context->dbh;
1363 UPDATE aqorders_items
1365 WHERE itemnumber = ?
1367 my $sth = $dbh->prepare($query);
1368 return $sth->execute($ordernumber, $itemnumber);
1371 #------------------------------------------------------------#
1373 =head3 GetCancelledOrders
1375 my @orders = GetCancelledOrders($basketno, $orderby);
1377 Returns cancelled orders for a basket
1381 sub GetCancelledOrders {
1382 my ( $basketno, $orderby ) = @_;
1384 return () unless $basketno;
1386 my $dbh = C4::Context->dbh;
1393 aqorders_transfers.ordernumber_to AS transferred_to,
1394 aqorders_transfers.timestamp AS transferred_to_timestamp
1396 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1397 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1398 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1399 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber
1401 AND (datecancellationprinted IS NOT NULL
1402 AND datecancellationprinted <> '0000-00-00')
1405 $orderby = "aqorders.datecancellationprinted desc, aqorders.timestamp desc"
1407 $query .= " ORDER BY $orderby";
1408 my $sth = $dbh->prepare($query);
1409 $sth->execute($basketno);
1410 my $results = $sth->fetchall_arrayref( {} );
1416 #------------------------------------------------------------#
1418 =head3 ModReceiveOrder
1420 &ModReceiveOrder($biblionumber, $ordernumber, $quantityreceived, $user,
1421 $cost, $ecost, $invoiceid, rrp, budget_id, datereceived, \@received_itemnumbers);
1423 Updates an order, to reflect the fact that it was received, at least
1424 in part. All arguments not mentioned below update the fields with the
1425 same name in the aqorders table of the Koha database.
1427 If a partial order is received, splits the order into two.
1429 Updates the order with bibilionumber C<$biblionumber> and ordernumber
1435 sub ModReceiveOrder {
1437 $biblionumber, $ordernumber, $quantrec, $user, $cost, $ecost,
1438 $invoiceid, $rrp, $budget_id, $datereceived, $received_items
1442 my $dbh = C4::Context->dbh;
1443 $datereceived = C4::Dates->output('iso') unless $datereceived;
1444 my $suggestionid = GetSuggestionFromBiblionumber( $biblionumber );
1445 if ($suggestionid) {
1446 ModSuggestion( {suggestionid=>$suggestionid,
1447 STATUS=>'AVAILABLE',
1448 biblionumber=> $biblionumber}
1452 my $result_set = $dbh->selectall_arrayref(
1453 q{SELECT * FROM aqorders WHERE biblionumber=? AND aqorders.ordernumber=?},
1454 { Slice => {} }, $biblionumber, $ordernumber
1457 # we assume we have a unique order
1458 my $order = $result_set->[0];
1460 my $new_ordernumber = $ordernumber;
1461 if ( $order->{quantity} > $quantrec ) {
1462 # Split order line in two parts: the first is the original order line
1463 # without received items (the quantity is decreased),
1464 # the second part is a new order line with quantity=quantityrec
1465 # (entirely received)
1466 my $sth=$dbh->prepare("
1469 orderstatus = 'partial'
1470 WHERE ordernumber = ?
1473 $sth->execute($order->{quantity} - $quantrec, $ordernumber);
1475 delete $order->{'ordernumber'};
1476 $order->{'budget_id'} = ( $budget_id || $order->{'budget_id'} );
1477 $order->{'quantity'} = $quantrec;
1478 $order->{'quantityreceived'} = $quantrec;
1479 $order->{'datereceived'} = $datereceived;
1480 $order->{'invoiceid'} = $invoiceid;
1481 $order->{'unitprice'} = $cost;
1482 $order->{'rrp'} = $rrp;
1483 $order->{ecost} = $ecost;
1484 $order->{'orderstatus'} = 'complete';
1486 ( $basketno, $new_ordernumber ) = NewOrder($order);
1488 if ($received_items) {
1489 foreach my $itemnumber (@$received_items) {
1490 ModItemOrder($itemnumber, $new_ordernumber);
1494 my $sth=$dbh->prepare("update aqorders
1495 set quantityreceived=?,datereceived=?,invoiceid=?,
1496 unitprice=?,rrp=?,ecost=?,budget_id=?,orderstatus='complete'
1497 where biblionumber=? and ordernumber=?");
1498 $sth->execute($quantrec,$datereceived,$invoiceid,$cost,$rrp,$ecost,$budget_id,$biblionumber,$ordernumber);
1500 return ($datereceived, $new_ordernumber);
1503 =head3 CancelReceipt
1505 my $parent_ordernumber = CancelReceipt($ordernumber);
1507 Cancel an order line receipt and update the parent order line, as if no
1509 If items are created at receipt (AcqCreateItem = receiving) then delete
1515 my $ordernumber = shift;
1517 return unless $ordernumber;
1519 my $dbh = C4::Context->dbh;
1521 SELECT datereceived, parent_ordernumber, quantity
1523 WHERE ordernumber = ?
1525 my $sth = $dbh->prepare($query);
1526 $sth->execute($ordernumber);
1527 my $order = $sth->fetchrow_hashref;
1529 warn "CancelReceipt: order $ordernumber does not exist";
1532 unless($order->{'datereceived'}) {
1533 warn "CancelReceipt: order $ordernumber is not received";
1537 my $parent_ordernumber = $order->{'parent_ordernumber'};
1539 if($parent_ordernumber == $ordernumber || not $parent_ordernumber) {
1540 # The order line has no parent, just mark it as not received
1543 SET quantityreceived = ?,
1546 orderstatus = 'ordered'
1547 WHERE ordernumber = ?
1549 $sth = $dbh->prepare($query);
1550 $sth->execute(0, undef, undef, $ordernumber);
1552 # The order line has a parent, increase parent quantity and delete
1555 SELECT quantity, datereceived
1557 WHERE ordernumber = ?
1559 $sth = $dbh->prepare($query);
1560 $sth->execute($parent_ordernumber);
1561 my $parent_order = $sth->fetchrow_hashref;
1562 unless($parent_order) {
1563 warn "Parent order $parent_ordernumber does not exist.";
1566 if($parent_order->{'datereceived'}) {
1567 warn "CancelReceipt: parent order is received.".
1568 " Can't cancel receipt.";
1574 orderstatus = 'ordered'
1575 WHERE ordernumber = ?
1577 $sth = $dbh->prepare($query);
1578 my $rv = $sth->execute(
1579 $order->{'quantity'} + $parent_order->{'quantity'},
1583 warn "Cannot update parent order line, so do not cancel".
1587 if(C4::Context->preference('AcqCreateItem') eq 'receiving') {
1588 # Remove items that were created at receipt
1590 DELETE FROM items, aqorders_items
1591 USING items, aqorders_items
1592 WHERE items.itemnumber = ? AND aqorders_items.itemnumber = ?
1594 $sth = $dbh->prepare($query);
1595 my @itemnumbers = GetItemnumbersFromOrder($ordernumber);
1596 foreach my $itemnumber (@itemnumbers) {
1597 $sth->execute($itemnumber, $itemnumber);
1601 my @itemnumbers = GetItemnumbersFromOrder($ordernumber);
1602 foreach my $itemnumber (@itemnumbers) {
1603 ModItemOrder($itemnumber, $parent_ordernumber);
1608 DELETE FROM aqorders
1609 WHERE ordernumber = ?
1611 $sth = $dbh->prepare($query);
1612 $sth->execute($ordernumber);
1616 return $parent_ordernumber;
1619 #------------------------------------------------------------#
1623 @results = &SearchOrders({
1624 ordernumber => $ordernumber,
1626 biblionumber => $biblionumber,
1628 booksellerid => $booksellerid,
1629 basketno => $basketno,
1634 Searches for orders.
1636 C<$owner> Finds order for the logged in user.
1637 C<$pending> Finds pending orders. Ignores completed and cancelled orders.
1640 C<@results> is an array of references-to-hash with the keys are fields
1641 from aqorders, biblio, biblioitems and aqbasket tables.
1646 my ( $params ) = @_;
1647 my $ordernumber = $params->{ordernumber};
1648 my $search = $params->{search};
1649 my $ean = $params->{ean};
1650 my $booksellerid = $params->{booksellerid};
1651 my $basketno = $params->{basketno};
1652 my $basketname = $params->{basketname};
1653 my $basketgroupname = $params->{basketgroupname};
1654 my $owner = $params->{owner};
1655 my $pending = $params->{pending};
1657 my $dbh = C4::Context->dbh;
1660 SELECT aqbasket.basketno,
1662 borrowers.firstname,
1665 biblioitems.biblioitemnumber,
1667 aqbasket.creationdate,
1668 aqbasket.basketname,
1669 aqbasketgroups.id as basketgroupid,
1670 aqbasketgroups.name as basketgroupname,
1673 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
1674 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
1675 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1676 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1677 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
1678 WHERE (datecancellationprinted is NULL)
1682 AND (quantity > quantityreceived OR quantityreceived is NULL)
1685 my $userenv = C4::Context->userenv;
1686 if ( C4::Context->preference("IndependentBranches") ) {
1687 unless ( C4::Context->IsSuperLibrarian() ) {
1690 borrowers.branchcode = ?
1691 OR borrowers.branchcode = ''
1694 push @args, $userenv->{branch};
1698 if ( $ordernumber ) {
1699 $query .= ' AND (aqorders.ordernumber=?)';
1700 push @args, $ordernumber;
1703 $query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)';
1704 push @args, ("%$search%","%$search%","%$search%");
1707 $query .= ' AND biblioitems.ean = ?';
1710 if ( $booksellerid ) {
1711 $query .= 'AND aqbasket.booksellerid = ?';
1712 push @args, $booksellerid;
1715 $query .= 'AND aqbasket.basketno = ?';
1716 push @args, $basketno;
1719 $query .= 'AND aqbasket.basketname LIKE ?';
1720 push @args, "%$basketname%";
1722 if( $basketgroupname ) {
1723 $query .= ' AND aqbasketgroups.name LIKE ?';
1724 push @args, "%$basketgroupname%";
1728 $query .= ' AND aqbasket.authorisedby=? ';
1729 push @args, $userenv->{'number'};
1732 $query .= ' ORDER BY aqbasket.basketno';
1734 my $sth = $dbh->prepare($query);
1735 $sth->execute(@args);
1736 return $sth->fetchall_arrayref({});
1739 #------------------------------------------------------------#
1743 &DelOrder($biblionumber, $ordernumber);
1745 Cancel the order with the given order and biblio numbers. It does not
1746 delete any entries in the aqorders table, it merely marks them as
1752 my ( $bibnum, $ordernumber ) = @_;
1753 my $dbh = C4::Context->dbh;
1756 SET datecancellationprinted=now(), orderstatus='cancelled'
1757 WHERE biblionumber=? AND ordernumber=?
1759 my $sth = $dbh->prepare($query);
1760 $sth->execute( $bibnum, $ordernumber );
1761 my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1762 foreach my $itemnumber (@itemnumbers){
1763 C4::Items::DelItem( $dbh, $bibnum, $itemnumber );
1768 =head3 TransferOrder
1770 my $newordernumber = TransferOrder($ordernumber, $basketno);
1772 Transfer an order line to a basket.
1773 Mark $ordernumber as cancelled with an internal note 'Cancelled and transfered
1774 to BOOKSELLER on DATE' and create new order with internal note
1775 'Transfered from BOOKSELLER on DATE'.
1776 Move all attached items to the new order.
1777 Received orders cannot be transfered.
1778 Return the ordernumber of created order.
1783 my ($ordernumber, $basketno) = @_;
1785 return unless ($ordernumber and $basketno);
1787 my $order = GetOrder( $ordernumber );
1788 return if $order->{datereceived};
1789 my $basket = GetBasket($basketno);
1790 return unless $basket;
1792 my $dbh = C4::Context->dbh;
1793 my ($query, $sth, $rv);
1797 SET datecancellationprinted = CAST(NOW() AS date)
1798 WHERE ordernumber = ?
1800 $sth = $dbh->prepare($query);
1801 $rv = $sth->execute($ordernumber);
1803 delete $order->{'ordernumber'};
1804 delete $order->{parent_ordernumber};
1805 $order->{'basketno'} = $basketno;
1807 (undef, $newordernumber) = NewOrder($order);
1810 UPDATE aqorders_items
1812 WHERE ordernumber = ?
1814 $sth = $dbh->prepare($query);
1815 $sth->execute($newordernumber, $ordernumber);
1818 INSERT INTO aqorders_transfers (ordernumber_from, ordernumber_to)
1821 $sth = $dbh->prepare($query);
1822 $sth->execute($ordernumber, $newordernumber);
1824 return $newordernumber;
1827 =head2 FUNCTIONS ABOUT PARCELS
1831 #------------------------------------------------------------#
1835 @results = &GetParcel($booksellerid, $code, $date);
1837 Looks up all of the received items from the supplier with the given
1838 bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders.
1840 C<@results> is an array of references-to-hash. The keys of each element are fields from
1841 the aqorders, biblio, and biblioitems tables of the Koha database.
1843 C<@results> is sorted alphabetically by book title.
1848 #gets all orders from a certain supplier, orders them alphabetically
1849 my ( $supplierid, $code, $datereceived ) = @_;
1850 my $dbh = C4::Context->dbh;
1853 if $code; # add % if we search on a given code (otherwise, let him empty)
1855 SELECT authorisedby,
1860 aqorders.biblionumber,
1861 aqorders.ordernumber,
1862 aqorders.parent_ordernumber,
1864 aqorders.quantityreceived,
1872 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
1873 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1874 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1875 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
1877 aqbasket.booksellerid = ?
1878 AND aqinvoices.invoicenumber LIKE ?
1879 AND aqorders.datereceived = ? ";
1881 my @query_params = ( $supplierid, $code, $datereceived );
1882 if ( C4::Context->preference("IndependentBranches") ) {
1883 unless ( C4::Context->IsSuperLibrarian() ) {
1884 $strsth .= " and (borrowers.branchcode = ?
1885 or borrowers.branchcode = '')";
1886 push @query_params, C4::Context->userenv->{branch};
1889 $strsth .= " ORDER BY aqbasket.basketno";
1890 my $result_set = $dbh->selectall_arrayref(
1895 return @{$result_set};
1898 #------------------------------------------------------------#
1902 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
1904 get a lists of parcels.
1911 is the bookseller this function has to get parcels.
1914 To know on what criteria the results list has to be ordered.
1917 is the booksellerinvoicenumber.
1919 =item $datefrom & $dateto
1920 to know on what date this function has to filter its search.
1925 a pointer on a hash list containing parcel informations as such :
1931 =item Last operation
1933 =item Number of biblio
1935 =item Number of items
1942 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
1943 my $dbh = C4::Context->dbh;
1944 my @query_params = ();
1946 SELECT aqinvoices.invoicenumber,
1947 datereceived,purchaseordernumber,
1948 count(DISTINCT biblionumber) AS biblio,
1949 sum(quantity) AS itemsexpected,
1950 sum(quantityreceived) AS itemsreceived
1951 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
1952 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
1953 WHERE aqbasket.booksellerid = ? and datereceived IS NOT NULL
1955 push @query_params, $bookseller;
1957 if ( defined $code ) {
1958 $strsth .= ' and aqinvoices.invoicenumber like ? ';
1959 # add a % to the end of the code to allow stemming.
1960 push @query_params, "$code%";
1963 if ( defined $datefrom ) {
1964 $strsth .= ' and datereceived >= ? ';
1965 push @query_params, $datefrom;
1968 if ( defined $dateto ) {
1969 $strsth .= 'and datereceived <= ? ';
1970 push @query_params, $dateto;
1973 $strsth .= "group by aqinvoices.invoicenumber,datereceived ";
1975 # can't use a placeholder to place this column name.
1976 # but, we could probably be checking to make sure it is a column that will be fetched.
1977 $strsth .= "order by $order " if ($order);
1979 my $sth = $dbh->prepare($strsth);
1981 $sth->execute( @query_params );
1982 my $results = $sth->fetchall_arrayref({});
1986 #------------------------------------------------------------#
1988 =head3 GetLateOrders
1990 @results = &GetLateOrders;
1992 Searches for bookseller with late orders.
1995 the table of supplier with late issues. This table is full of hashref.
2001 my $supplierid = shift;
2003 my $estimateddeliverydatefrom = shift;
2004 my $estimateddeliverydateto = shift;
2006 my $dbh = C4::Context->dbh;
2008 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
2009 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
2011 my @query_params = ();
2013 SELECT aqbasket.basketno,
2014 aqorders.ordernumber,
2015 DATE(aqbasket.closedate) AS orderdate,
2016 aqorders.rrp AS unitpricesupplier,
2017 aqorders.ecost AS unitpricelib,
2018 aqorders.claims_count AS claims_count,
2019 aqorders.claimed_date AS claimed_date,
2020 aqbudgets.budget_name AS budget,
2021 borrowers.branchcode AS branch,
2022 aqbooksellers.name AS supplier,
2023 aqbooksellers.id AS supplierid,
2024 biblio.author, biblio.title,
2025 biblioitems.publishercode AS publisher,
2026 biblioitems.publicationyear,
2027 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
2031 aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
2032 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
2033 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
2034 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
2035 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
2036 WHERE aqorders.basketno = aqbasket.basketno
2037 AND ( datereceived = ''
2038 OR datereceived IS NULL
2039 OR aqorders.quantityreceived < aqorders.quantity
2041 AND aqbasket.closedate IS NOT NULL
2042 AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
2045 if ($dbdriver eq "mysql") {
2047 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity,
2048 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
2049 DATEDIFF(CAST(now() AS date),closedate) AS latesince
2051 if ( defined $delay ) {
2052 $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ;
2053 push @query_params, $delay;
2056 HAVING quantity <> 0
2057 AND unitpricesupplier <> 0
2058 AND unitpricelib <> 0
2061 # FIXME: account for IFNULL as above
2063 aqorders.quantity AS quantity,
2064 aqorders.quantity * aqorders.rrp AS subtotal,
2065 (CAST(now() AS date) - closedate) AS latesince
2067 if ( defined $delay ) {
2068 $from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) ";
2069 push @query_params, $delay;
2072 if (defined $supplierid) {
2073 $from .= ' AND aqbasket.booksellerid = ? ';
2074 push @query_params, $supplierid;
2076 if (defined $branch) {
2077 $from .= ' AND borrowers.branchcode LIKE ? ';
2078 push @query_params, $branch;
2081 if ( defined $estimateddeliverydatefrom or defined $estimateddeliverydateto ) {
2082 $from .= ' AND aqbooksellers.deliverytime IS NOT NULL ';
2084 if ( defined $estimateddeliverydatefrom ) {
2085 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
2086 push @query_params, $estimateddeliverydatefrom;
2088 if ( defined $estimateddeliverydateto ) {
2089 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
2090 push @query_params, $estimateddeliverydateto;
2092 if ( defined $estimateddeliverydatefrom and not defined $estimateddeliverydateto ) {
2093 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
2095 if (C4::Context->preference("IndependentBranches")
2096 && !C4::Context->IsSuperLibrarian() ) {
2097 $from .= ' AND borrowers.branchcode LIKE ? ';
2098 push @query_params, C4::Context->userenv->{branch};
2100 $from .= " AND orderstatus <> 'cancelled' ";
2101 my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier";
2102 $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params);
2103 my $sth = $dbh->prepare($query);
2104 $sth->execute(@query_params);
2106 while (my $data = $sth->fetchrow_hashref) {
2107 $data->{orderdate} = format_date($data->{orderdate});
2108 $data->{claimed_date} = format_date($data->{claimed_date});
2109 push @results, $data;
2114 #------------------------------------------------------------#
2118 (\@order_loop, $total_qty, $total_price, $total_qtyreceived) = GetHistory( %params );
2120 Retreives some acquisition history information
2130 basket - search both basket name and number
2131 booksellerinvoicenumber
2134 orderstatus (note that orderstatus '' will retrieve orders
2135 of any status except cancelled)
2137 get_canceled_order (if set to a true value, cancelled orders will
2141 $order_loop is a list of hashrefs that each look like this:
2143 'author' => 'Twain, Mark',
2145 'biblionumber' => '215',
2147 'creationdate' => 'MM/DD/YYYY',
2148 'datereceived' => undef,
2151 'invoicenumber' => undef,
2153 'ordernumber' => '1',
2155 'quantityreceived' => undef,
2156 'title' => 'The Adventures of Huckleberry Finn'
2158 $total_qty is the sum of all of the quantities in $order_loop
2159 $total_price is the cost of each in $order_loop times the quantity
2160 $total_qtyreceived is the sum of all of the quantityreceived entries in $order_loop
2165 # don't run the query if there are no parameters (list would be too long for sure !)
2166 croak "No search params" unless @_;
2168 my $title = $params{title};
2169 my $author = $params{author};
2170 my $isbn = $params{isbn};
2171 my $ean = $params{ean};
2172 my $name = $params{name};
2173 my $from_placed_on = $params{from_placed_on};
2174 my $to_placed_on = $params{to_placed_on};
2175 my $basket = $params{basket};
2176 my $booksellerinvoicenumber = $params{booksellerinvoicenumber};
2177 my $basketgroupname = $params{basketgroupname};
2178 my $budget = $params{budget};
2179 my $orderstatus = $params{orderstatus};
2180 my $biblionumber = $params{biblionumber};
2181 my $get_canceled_order = $params{get_canceled_order} || 0;
2185 my $total_qtyreceived = 0;
2186 my $total_price = 0;
2188 my $dbh = C4::Context->dbh;
2191 COALESCE(biblio.title, deletedbiblio.title) AS title,
2192 COALESCE(biblio.author, deletedbiblio.author) AS author,
2193 COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn,
2194 COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean,
2196 aqbasket.basketname,
2197 aqbasket.basketgroupid,
2198 aqbasketgroups.name as groupname,
2200 aqbasket.creationdate,
2201 aqorders.datereceived,
2203 aqorders.quantityreceived,
2205 aqorders.ordernumber,
2207 aqinvoices.invoicenumber,
2208 aqbooksellers.id as id,
2209 aqorders.biblionumber,
2210 aqorders.orderstatus,
2211 aqorders.parent_ordernumber,
2212 aqbudgets.budget_name
2214 $query .= ", aqbudgets.budget_id AS budget" if defined $budget;
2217 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
2218 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
2219 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
2220 LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
2221 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
2222 LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id
2223 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2224 LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.biblionumber
2225 LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=aqorders.biblionumber
2228 if ( C4::Context->preference("IndependentBranches") ) {
2229 $query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber";
2232 $query .= " WHERE 1 ";
2234 unless ($get_canceled_order or (defined $orderstatus and $orderstatus eq 'cancelled')) {
2235 $query .= " AND (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') ";
2238 my @query_params = ();
2240 if ( $biblionumber ) {
2241 $query .= " AND biblio.biblionumber = ?";
2242 push @query_params, $biblionumber;
2246 $query .= " AND biblio.title LIKE ? ";
2247 $title =~ s/\s+/%/g;
2248 push @query_params, "%$title%";
2252 $query .= " AND biblio.author LIKE ? ";
2253 push @query_params, "%$author%";
2257 $query .= " AND biblioitems.isbn LIKE ? ";
2258 push @query_params, "%$isbn%";
2261 $query .= " AND biblioitems.ean = ? ";
2262 push @query_params, "$ean";
2265 $query .= " AND aqbooksellers.name LIKE ? ";
2266 push @query_params, "%$name%";
2270 $query .= " AND aqbudgets.budget_id = ? ";
2271 push @query_params, "$budget";
2274 if ( $from_placed_on ) {
2275 $query .= " AND creationdate >= ? ";
2276 push @query_params, $from_placed_on;
2279 if ( $to_placed_on ) {
2280 $query .= " AND creationdate <= ? ";
2281 push @query_params, $to_placed_on;
2284 if ( defined $orderstatus and $orderstatus ne '') {
2285 $query .= " AND aqorders.orderstatus = ? ";
2286 push @query_params, "$orderstatus";
2290 if ($basket =~ m/^\d+$/) {
2291 $query .= " AND aqorders.basketno = ? ";
2292 push @query_params, $basket;
2294 $query .= " AND aqbasket.basketname LIKE ? ";
2295 push @query_params, "%$basket%";
2299 if ($booksellerinvoicenumber) {
2300 $query .= " AND aqinvoices.invoicenumber LIKE ? ";
2301 push @query_params, "%$booksellerinvoicenumber%";
2304 if ($basketgroupname) {
2305 $query .= " AND aqbasketgroups.name LIKE ? ";
2306 push @query_params, "%$basketgroupname%";
2309 if ( C4::Context->preference("IndependentBranches") ) {
2310 unless ( C4::Context->IsSuperLibrarian() ) {
2311 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
2312 push @query_params, C4::Context->userenv->{branch};
2315 $query .= " ORDER BY id";
2316 my $sth = $dbh->prepare($query);
2317 $sth->execute( @query_params );
2319 while ( my $line = $sth->fetchrow_hashref ) {
2320 $line->{count} = $cnt++;
2321 $line->{toggle} = 1 if $cnt % 2;
2322 push @order_loop, $line;
2323 $total_qty += ( $line->{quantity} ) ? $line->{quantity} : 0;
2324 $total_qtyreceived += ( $line->{quantityreceived} ) ? $line->{quantityreceived} : 0;
2325 $total_price += ( $line->{quantity} and $line->{ecost} ) ? $line->{quantity} * $line->{ecost} : 0;
2327 return \@order_loop, $total_qty, $total_price, $total_qtyreceived;
2330 =head2 GetRecentAcqui
2332 $results = GetRecentAcqui($days);
2334 C<$results> is a ref to a table which containts hashref
2338 sub GetRecentAcqui {
2340 my $dbh = C4::Context->dbh;
2344 ORDER BY timestamp DESC
2347 my $sth = $dbh->prepare($query);
2349 my $results = $sth->fetchall_arrayref({});
2355 $contractlist = &GetContracts($booksellerid, $activeonly);
2357 Looks up the contracts that belong to a bookseller
2359 Returns a list of contracts
2363 =item C<$booksellerid> is the "id" field in the "aqbooksellers" table.
2365 =item C<$activeonly> if exists get only contracts that are still active.
2372 my ( $booksellerid, $activeonly ) = @_;
2373 my $dbh = C4::Context->dbh;
2375 if (! $activeonly) {
2379 WHERE booksellerid=?
2384 WHERE booksellerid=?
2385 AND contractenddate >= CURDATE( )";
2388 $dbh->selectall_arrayref( $query, { Slice => {} }, $booksellerid );
2389 return @{$result_set};
2392 #------------------------------------------------------------#
2396 $contract = &GetContract($contractID);
2398 Looks up the contract that has PRIMKEY (contractnumber) value $contractID
2405 my ( $contractno ) = @_;
2406 my $dbh = C4::Context->dbh;
2410 WHERE contractnumber=?
2413 my $sth = $dbh->prepare($query);
2414 $sth->execute( $contractno );
2415 my $result = $sth->fetchrow_hashref;
2423 &AddClaim($ordernumber);
2425 Add a claim for an order
2432 my ($ordernumber) = @_;
2433 my $dbh = C4::Context->dbh;
2436 claims_count = claims_count + 1,
2437 claimed_date = CURDATE()
2438 WHERE ordernumber = ?
2440 my $sth = $dbh->prepare($query);
2441 $sth->execute($ordernumber);
2446 my @invoices = GetInvoices(
2447 invoicenumber => $invoicenumber,
2448 supplierid => $supplierid,
2449 suppliername => $suppliername,
2450 shipmentdatefrom => $shipmentdatefrom, # ISO format
2451 shipmentdateto => $shipmentdateto, # ISO format
2452 billingdatefrom => $billingdatefrom, # ISO format
2453 billingdateto => $billingdateto, # ISO format
2454 isbneanissn => $isbn_or_ean_or_issn,
2457 publisher => $publisher,
2458 publicationyear => $publicationyear,
2459 branchcode => $branchcode,
2460 order_by => $order_by
2463 Return a list of invoices that match all given criteria.
2465 $order_by is "column_name (asc|desc)", where column_name is any of
2466 'invoicenumber', 'booksellerid', 'shipmentdate', 'billingdate', 'closedate',
2467 'shipmentcost', 'shipmentcost_budgetid'.
2469 asc is the default if omitted
2476 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2477 closedate shipmentcost shipmentcost_budgetid);
2479 my $dbh = C4::Context->dbh;
2481 SELECT aqinvoices.*, aqbooksellers.name AS suppliername,
2484 aqorders.datereceived IS NOT NULL,
2485 aqorders.biblionumber,
2488 ) AS receivedbiblios,
2489 SUM(aqorders.quantityreceived) AS receiveditems
2491 LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid
2492 LEFT JOIN aqorders ON aqorders.invoiceid = aqinvoices.invoiceid
2493 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
2494 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2495 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2496 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
2497 LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber
2502 if($args{supplierid}) {
2503 push @bind_strs, " aqinvoices.booksellerid = ? ";
2504 push @bind_args, $args{supplierid};
2506 if($args{invoicenumber}) {
2507 push @bind_strs, " aqinvoices.invoicenumber LIKE ? ";
2508 push @bind_args, "%$args{invoicenumber}%";
2510 if($args{suppliername}) {
2511 push @bind_strs, " aqbooksellers.name LIKE ? ";
2512 push @bind_args, "%$args{suppliername}%";
2514 if($args{shipmentdatefrom}) {
2515 push @bind_strs, " aqinvoices.shipmentdate >= ? ";
2516 push @bind_args, $args{shipmentdatefrom};
2518 if($args{shipmentdateto}) {
2519 push @bind_strs, " aqinvoices.shipmentdate <= ? ";
2520 push @bind_args, $args{shipmentdateto};
2522 if($args{billingdatefrom}) {
2523 push @bind_strs, " aqinvoices.billingdate >= ? ";
2524 push @bind_args, $args{billingdatefrom};
2526 if($args{billingdateto}) {
2527 push @bind_strs, " aqinvoices.billingdate <= ? ";
2528 push @bind_args, $args{billingdateto};
2530 if($args{isbneanissn}) {
2531 push @bind_strs, " (biblioitems.isbn LIKE CONCAT('%', ?, '%') OR biblioitems.ean LIKE CONCAT('%', ?, '%') OR biblioitems.issn LIKE CONCAT('%', ?, '%') ) ";
2532 push @bind_args, $args{isbneanissn}, $args{isbneanissn}, $args{isbneanissn};
2535 push @bind_strs, " biblio.title LIKE CONCAT('%', ?, '%') ";
2536 push @bind_args, $args{title};
2539 push @bind_strs, " biblio.author LIKE CONCAT('%', ?, '%') ";
2540 push @bind_args, $args{author};
2542 if($args{publisher}) {
2543 push @bind_strs, " biblioitems.publishercode LIKE CONCAT('%', ?, '%') ";
2544 push @bind_args, $args{publisher};
2546 if($args{publicationyear}) {
2547 push @bind_strs, " ((biblioitems.publicationyear LIKE CONCAT('%', ?, '%')) OR (biblio.copyrightdate LIKE CONCAT('%', ?, '%'))) ";
2548 push @bind_args, $args{publicationyear}, $args{publicationyear};
2550 if($args{branchcode}) {
2551 push @bind_strs, " borrowers.branchcode = ? ";
2552 push @bind_args, $args{branchcode};
2555 $query .= " WHERE " . join(" AND ", @bind_strs) if @bind_strs;
2556 $query .= " GROUP BY aqinvoices.invoiceid ";
2558 if($args{order_by}) {
2559 my ($column, $direction) = split / /, $args{order_by};
2560 if(grep /^$column$/, @columns) {
2561 $direction ||= 'ASC';
2562 $query .= " ORDER BY $column $direction";
2566 my $sth = $dbh->prepare($query);
2567 $sth->execute(@bind_args);
2569 my $results = $sth->fetchall_arrayref({});
2575 my $invoice = GetInvoice($invoiceid);
2577 Get informations about invoice with given $invoiceid
2579 Return a hash filled with aqinvoices.* fields
2584 my ($invoiceid) = @_;
2587 return unless $invoiceid;
2589 my $dbh = C4::Context->dbh;
2595 my $sth = $dbh->prepare($query);
2596 $sth->execute($invoiceid);
2598 $invoice = $sth->fetchrow_hashref;
2602 =head3 GetInvoiceDetails
2604 my $invoice = GetInvoiceDetails($invoiceid)
2606 Return informations about an invoice + the list of related order lines
2608 Orders informations are in $invoice->{orders} (array ref)
2612 sub GetInvoiceDetails {
2613 my ($invoiceid) = @_;
2615 if ( !defined $invoiceid ) {
2616 carp 'GetInvoiceDetails called without an invoiceid';
2620 my $dbh = C4::Context->dbh;
2622 SELECT aqinvoices.*, aqbooksellers.name AS suppliername
2624 LEFT JOIN aqbooksellers ON aqinvoices.booksellerid = aqbooksellers.id
2627 my $sth = $dbh->prepare($query);
2628 $sth->execute($invoiceid);
2630 my $invoice = $sth->fetchrow_hashref;
2633 SELECT aqorders.*, biblio.*, aqbasket.basketname
2635 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
2636 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2639 $sth = $dbh->prepare($query);
2640 $sth->execute($invoiceid);
2641 $invoice->{orders} = $sth->fetchall_arrayref({});
2642 $invoice->{orders} ||= []; # force an empty arrayref if fetchall_arrayref fails
2649 my $invoiceid = AddInvoice(
2650 invoicenumber => $invoicenumber,
2651 booksellerid => $booksellerid,
2652 shipmentdate => $shipmentdate,
2653 billingdate => $billingdate,
2654 closedate => $closedate,
2655 shipmentcost => $shipmentcost,
2656 shipmentcost_budgetid => $shipmentcost_budgetid
2659 Create a new invoice and return its id or undef if it fails.
2666 return unless(%invoice and $invoice{invoicenumber});
2668 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2669 closedate shipmentcost shipmentcost_budgetid);
2673 foreach my $key (keys %invoice) {
2674 if(0 < grep(/^$key$/, @columns)) {
2675 push @set_strs, "$key = ?";
2676 push @set_args, ($invoice{$key} || undef);
2682 my $dbh = C4::Context->dbh;
2683 my $query = "INSERT INTO aqinvoices SET ";
2684 $query .= join (",", @set_strs);
2685 my $sth = $dbh->prepare($query);
2686 $rv = $sth->execute(@set_args);
2688 $rv = $dbh->last_insert_id(undef, undef, 'aqinvoices', undef);
2697 invoiceid => $invoiceid, # Mandatory
2698 invoicenumber => $invoicenumber,
2699 booksellerid => $booksellerid,
2700 shipmentdate => $shipmentdate,
2701 billingdate => $billingdate,
2702 closedate => $closedate,
2703 shipmentcost => $shipmentcost,
2704 shipmentcost_budgetid => $shipmentcost_budgetid
2707 Modify an invoice, invoiceid is mandatory.
2709 Return undef if it fails.
2716 return unless(%invoice and $invoice{invoiceid});
2718 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2719 closedate shipmentcost shipmentcost_budgetid);
2723 foreach my $key (keys %invoice) {
2724 if(0 < grep(/^$key$/, @columns)) {
2725 push @set_strs, "$key = ?";
2726 push @set_args, ($invoice{$key} || undef);
2730 my $dbh = C4::Context->dbh;
2731 my $query = "UPDATE aqinvoices SET ";
2732 $query .= join(",", @set_strs);
2733 $query .= " WHERE invoiceid = ?";
2735 my $sth = $dbh->prepare($query);
2736 $sth->execute(@set_args, $invoice{invoiceid});
2741 CloseInvoice($invoiceid);
2745 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => undef);
2750 my ($invoiceid) = @_;
2752 return unless $invoiceid;
2754 my $dbh = C4::Context->dbh;
2757 SET closedate = CAST(NOW() AS DATE)
2760 my $sth = $dbh->prepare($query);
2761 $sth->execute($invoiceid);
2764 =head3 ReopenInvoice
2766 ReopenInvoice($invoiceid);
2770 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => C4::Dates->new()->output('iso'))
2775 my ($invoiceid) = @_;
2777 return unless $invoiceid;
2779 my $dbh = C4::Context->dbh;
2782 SET closedate = NULL
2785 my $sth = $dbh->prepare($query);
2786 $sth->execute($invoiceid);
2791 DelInvoice($invoiceid);
2793 Delete an invoice if there are no items attached to it.
2798 my ($invoiceid) = @_;
2800 return unless $invoiceid;
2802 my $dbh = C4::Context->dbh;
2808 my $sth = $dbh->prepare($query);
2809 $sth->execute($invoiceid);
2810 my $res = $sth->fetchrow_arrayref;
2811 if ( $res && $res->[0] == 0 ) {
2813 DELETE FROM aqinvoices
2816 my $sth = $dbh->prepare($query);
2817 return ( $sth->execute($invoiceid) > 0 );
2822 =head3 MergeInvoices
2824 MergeInvoices($invoiceid, \@sourceids);
2826 Merge the invoices identified by the IDs in \@sourceids into
2827 the invoice identified by $invoiceid.
2832 my ($invoiceid, $sourceids) = @_;
2834 return unless $invoiceid;
2835 foreach my $sourceid (@$sourceids) {
2836 next if $sourceid == $invoiceid;
2837 my $source = GetInvoiceDetails($sourceid);
2838 foreach my $order (@{$source->{'orders'}}) {
2839 $order->{'invoiceid'} = $invoiceid;
2842 DelInvoice($source->{'invoiceid'});
2847 =head3 GetBiblioCountByBasketno
2849 $biblio_count = &GetBiblioCountByBasketno($basketno);
2851 Looks up the biblio's count that has basketno value $basketno
2857 sub GetBiblioCountByBasketno {
2858 my ($basketno) = @_;
2859 my $dbh = C4::Context->dbh;
2861 SELECT COUNT( DISTINCT( biblionumber ) )
2864 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
2867 my $sth = $dbh->prepare($query);
2868 $sth->execute($basketno);
2869 return $sth->fetchrow;
2877 Koha Development Team <http://koha-community.org/>