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 if ( ( $userenv ) and ( $userenv->{flags} != 1 ) ) {
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 my $userenv = C4::Context->userenv;
1884 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
1885 $strsth .= " and (borrowers.branchcode = ?
1886 or borrowers.branchcode = '')";
1887 push @query_params, $userenv->{branch};
1890 $strsth .= " ORDER BY aqbasket.basketno";
1891 my $result_set = $dbh->selectall_arrayref(
1896 return @{$result_set};
1899 #------------------------------------------------------------#
1903 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
1905 get a lists of parcels.
1912 is the bookseller this function has to get parcels.
1915 To know on what criteria the results list has to be ordered.
1918 is the booksellerinvoicenumber.
1920 =item $datefrom & $dateto
1921 to know on what date this function has to filter its search.
1926 a pointer on a hash list containing parcel informations as such :
1932 =item Last operation
1934 =item Number of biblio
1936 =item Number of items
1943 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
1944 my $dbh = C4::Context->dbh;
1945 my @query_params = ();
1947 SELECT aqinvoices.invoicenumber,
1948 datereceived,purchaseordernumber,
1949 count(DISTINCT biblionumber) AS biblio,
1950 sum(quantity) AS itemsexpected,
1951 sum(quantityreceived) AS itemsreceived
1952 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
1953 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
1954 WHERE aqbasket.booksellerid = ? and datereceived IS NOT NULL
1956 push @query_params, $bookseller;
1958 if ( defined $code ) {
1959 $strsth .= ' and aqinvoices.invoicenumber like ? ';
1960 # add a % to the end of the code to allow stemming.
1961 push @query_params, "$code%";
1964 if ( defined $datefrom ) {
1965 $strsth .= ' and datereceived >= ? ';
1966 push @query_params, $datefrom;
1969 if ( defined $dateto ) {
1970 $strsth .= 'and datereceived <= ? ';
1971 push @query_params, $dateto;
1974 $strsth .= "group by aqinvoices.invoicenumber,datereceived ";
1976 # can't use a placeholder to place this column name.
1977 # but, we could probably be checking to make sure it is a column that will be fetched.
1978 $strsth .= "order by $order " if ($order);
1980 my $sth = $dbh->prepare($strsth);
1982 $sth->execute( @query_params );
1983 my $results = $sth->fetchall_arrayref({});
1987 #------------------------------------------------------------#
1989 =head3 GetLateOrders
1991 @results = &GetLateOrders;
1993 Searches for bookseller with late orders.
1996 the table of supplier with late issues. This table is full of hashref.
2002 my $supplierid = shift;
2004 my $estimateddeliverydatefrom = shift;
2005 my $estimateddeliverydateto = shift;
2007 my $dbh = C4::Context->dbh;
2009 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
2010 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
2012 my @query_params = ();
2014 SELECT aqbasket.basketno,
2015 aqorders.ordernumber,
2016 DATE(aqbasket.closedate) AS orderdate,
2017 aqorders.rrp AS unitpricesupplier,
2018 aqorders.ecost AS unitpricelib,
2019 aqorders.claims_count AS claims_count,
2020 aqorders.claimed_date AS claimed_date,
2021 aqbudgets.budget_name AS budget,
2022 borrowers.branchcode AS branch,
2023 aqbooksellers.name AS supplier,
2024 aqbooksellers.id AS supplierid,
2025 biblio.author, biblio.title,
2026 biblioitems.publishercode AS publisher,
2027 biblioitems.publicationyear,
2028 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
2032 aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
2033 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
2034 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
2035 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
2036 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
2037 WHERE aqorders.basketno = aqbasket.basketno
2038 AND ( datereceived = ''
2039 OR datereceived IS NULL
2040 OR aqorders.quantityreceived < aqorders.quantity
2042 AND aqbasket.closedate IS NOT NULL
2043 AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
2046 if ($dbdriver eq "mysql") {
2048 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity,
2049 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
2050 DATEDIFF(CAST(now() AS date),closedate) AS latesince
2052 if ( defined $delay ) {
2053 $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ;
2054 push @query_params, $delay;
2057 HAVING quantity <> 0
2058 AND unitpricesupplier <> 0
2059 AND unitpricelib <> 0
2062 # FIXME: account for IFNULL as above
2064 aqorders.quantity AS quantity,
2065 aqorders.quantity * aqorders.rrp AS subtotal,
2066 (CAST(now() AS date) - closedate) AS latesince
2068 if ( defined $delay ) {
2069 $from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) ";
2070 push @query_params, $delay;
2073 if (defined $supplierid) {
2074 $from .= ' AND aqbasket.booksellerid = ? ';
2075 push @query_params, $supplierid;
2077 if (defined $branch) {
2078 $from .= ' AND borrowers.branchcode LIKE ? ';
2079 push @query_params, $branch;
2082 if ( defined $estimateddeliverydatefrom or defined $estimateddeliverydateto ) {
2083 $from .= ' AND aqbooksellers.deliverytime IS NOT NULL ';
2085 if ( defined $estimateddeliverydatefrom ) {
2086 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
2087 push @query_params, $estimateddeliverydatefrom;
2089 if ( defined $estimateddeliverydateto ) {
2090 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
2091 push @query_params, $estimateddeliverydateto;
2093 if ( defined $estimateddeliverydatefrom and not defined $estimateddeliverydateto ) {
2094 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
2096 if (C4::Context->preference("IndependentBranches")
2097 && C4::Context->userenv
2098 && C4::Context->userenv->{flags} != 1 ) {
2099 $from .= ' AND borrowers.branchcode LIKE ? ';
2100 push @query_params, C4::Context->userenv->{branch};
2102 $from .= " AND orderstatus <> 'cancelled' ";
2103 my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier";
2104 $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params);
2105 my $sth = $dbh->prepare($query);
2106 $sth->execute(@query_params);
2108 while (my $data = $sth->fetchrow_hashref) {
2109 $data->{orderdate} = format_date($data->{orderdate});
2110 $data->{claimed_date} = format_date($data->{claimed_date});
2111 push @results, $data;
2116 #------------------------------------------------------------#
2120 (\@order_loop, $total_qty, $total_price, $total_qtyreceived) = GetHistory( %params );
2122 Retreives some acquisition history information
2130 basket - search both basket name and number
2131 booksellerinvoicenumber
2134 $order_loop is a list of hashrefs that each look like this:
2136 'author' => 'Twain, Mark',
2138 'biblionumber' => '215',
2140 'creationdate' => 'MM/DD/YYYY',
2141 'datereceived' => undef,
2144 'invoicenumber' => undef,
2146 'ordernumber' => '1',
2148 'quantityreceived' => undef,
2149 'title' => 'The Adventures of Huckleberry Finn'
2151 $total_qty is the sum of all of the quantities in $order_loop
2152 $total_price is the cost of each in $order_loop times the quantity
2153 $total_qtyreceived is the sum of all of the quantityreceived entries in $order_loop
2158 # don't run the query if there are no parameters (list would be too long for sure !)
2159 croak "No search params" unless @_;
2161 my $title = $params{title};
2162 my $author = $params{author};
2163 my $isbn = $params{isbn};
2164 my $ean = $params{ean};
2165 my $name = $params{name};
2166 my $from_placed_on = $params{from_placed_on};
2167 my $to_placed_on = $params{to_placed_on};
2168 my $basket = $params{basket};
2169 my $booksellerinvoicenumber = $params{booksellerinvoicenumber};
2170 my $basketgroupname = $params{basketgroupname};
2171 my $budget = $params{budget};
2172 my $orderstatus = $params{orderstatus};
2176 my $total_qtyreceived = 0;
2177 my $total_price = 0;
2179 my $dbh = C4::Context->dbh;
2182 COALESCE(biblio.title, deletedbiblio.title) AS title,
2183 COALESCE(biblio.author, deletedbiblio.author) AS author,
2184 COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn,
2185 COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean,
2187 aqbasket.basketname,
2188 aqbasket.basketgroupid,
2189 aqbasketgroups.name as groupname,
2191 aqbasket.creationdate,
2192 aqorders.datereceived,
2194 aqorders.quantityreceived,
2196 aqorders.ordernumber,
2198 aqinvoices.invoicenumber,
2199 aqbooksellers.id as id,
2200 aqorders.biblionumber,
2201 aqorders.orderstatus,
2202 aqorders.parent_ordernumber,
2203 aqbudgets.budget_name
2205 $query .= ", aqbudgets.budget_id AS budget" if defined $budget;
2208 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
2209 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
2210 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
2211 LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
2212 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
2213 LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id
2214 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2215 LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.biblionumber
2216 LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=aqorders.biblionumber
2219 if ( C4::Context->preference("IndependentBranches") ) {
2220 $query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber";
2223 $query .= " WHERE 1 ";
2225 $query .= " AND (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') " if $orderstatus ne 'cancelled';
2227 my @query_params = ();
2230 $query .= " AND biblio.title LIKE ? ";
2231 $title =~ s/\s+/%/g;
2232 push @query_params, "%$title%";
2236 $query .= " AND biblio.author LIKE ? ";
2237 push @query_params, "%$author%";
2241 $query .= " AND biblioitems.isbn LIKE ? ";
2242 push @query_params, "%$isbn%";
2245 $query .= " AND biblioitems.ean = ? ";
2246 push @query_params, "$ean";
2249 $query .= " AND aqbooksellers.name LIKE ? ";
2250 push @query_params, "%$name%";
2254 $query .= " AND aqbudgets.budget_id = ? ";
2255 push @query_params, "$budget";
2258 if ( $from_placed_on ) {
2259 $query .= " AND creationdate >= ? ";
2260 push @query_params, $from_placed_on;
2263 if ( $to_placed_on ) {
2264 $query .= " AND creationdate <= ? ";
2265 push @query_params, $to_placed_on;
2268 if ( defined $orderstatus and $orderstatus ne '') {
2269 $query .= " AND aqorders.orderstatus = ? ";
2270 push @query_params, "$orderstatus";
2274 if ($basket =~ m/^\d+$/) {
2275 $query .= " AND aqorders.basketno = ? ";
2276 push @query_params, $basket;
2278 $query .= " AND aqbasket.basketname LIKE ? ";
2279 push @query_params, "%$basket%";
2283 if ($booksellerinvoicenumber) {
2284 $query .= " AND aqinvoices.invoicenumber LIKE ? ";
2285 push @query_params, "%$booksellerinvoicenumber%";
2288 if ($basketgroupname) {
2289 $query .= " AND aqbasketgroups.name LIKE ? ";
2290 push @query_params, "%$basketgroupname%";
2293 if ( C4::Context->preference("IndependentBranches") ) {
2294 my $userenv = C4::Context->userenv;
2295 if ( $userenv && ($userenv->{flags} || 0) != 1 ) {
2296 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
2297 push @query_params, $userenv->{branch};
2300 $query .= " ORDER BY id";
2301 my $sth = $dbh->prepare($query);
2302 $sth->execute( @query_params );
2304 while ( my $line = $sth->fetchrow_hashref ) {
2305 $line->{count} = $cnt++;
2306 $line->{toggle} = 1 if $cnt % 2;
2307 push @order_loop, $line;
2308 $total_qty += ( $line->{quantity} ) ? $line->{quantity} : 0;
2309 $total_qtyreceived += ( $line->{quantityreceived} ) ? $line->{quantityreceived} : 0;
2310 $total_price += ( $line->{quantity} and $line->{ecost} ) ? $line->{quantity} * $line->{ecost} : 0;
2312 return \@order_loop, $total_qty, $total_price, $total_qtyreceived;
2315 =head2 GetRecentAcqui
2317 $results = GetRecentAcqui($days);
2319 C<$results> is a ref to a table which containts hashref
2323 sub GetRecentAcqui {
2325 my $dbh = C4::Context->dbh;
2329 ORDER BY timestamp DESC
2332 my $sth = $dbh->prepare($query);
2334 my $results = $sth->fetchall_arrayref({});
2340 $contractlist = &GetContracts($booksellerid, $activeonly);
2342 Looks up the contracts that belong to a bookseller
2344 Returns a list of contracts
2348 =item C<$booksellerid> is the "id" field in the "aqbooksellers" table.
2350 =item C<$activeonly> if exists get only contracts that are still active.
2357 my ( $booksellerid, $activeonly ) = @_;
2358 my $dbh = C4::Context->dbh;
2360 if (! $activeonly) {
2364 WHERE booksellerid=?
2369 WHERE booksellerid=?
2370 AND contractenddate >= CURDATE( )";
2373 $dbh->selectall_arrayref( $query, { Slice => {} }, $booksellerid );
2374 return @{$result_set};
2377 #------------------------------------------------------------#
2381 $contract = &GetContract($contractID);
2383 Looks up the contract that has PRIMKEY (contractnumber) value $contractID
2390 my ( $contractno ) = @_;
2391 my $dbh = C4::Context->dbh;
2395 WHERE contractnumber=?
2398 my $sth = $dbh->prepare($query);
2399 $sth->execute( $contractno );
2400 my $result = $sth->fetchrow_hashref;
2408 &AddClaim($ordernumber);
2410 Add a claim for an order
2417 my ($ordernumber) = @_;
2418 my $dbh = C4::Context->dbh;
2421 claims_count = claims_count + 1,
2422 claimed_date = CURDATE()
2423 WHERE ordernumber = ?
2425 my $sth = $dbh->prepare($query);
2426 $sth->execute($ordernumber);
2431 my @invoices = GetInvoices(
2432 invoicenumber => $invoicenumber,
2433 supplierid => $supplierid,
2434 suppliername => $suppliername,
2435 shipmentdatefrom => $shipmentdatefrom, # ISO format
2436 shipmentdateto => $shipmentdateto, # ISO format
2437 billingdatefrom => $billingdatefrom, # ISO format
2438 billingdateto => $billingdateto, # ISO format
2439 isbneanissn => $isbn_or_ean_or_issn,
2442 publisher => $publisher,
2443 publicationyear => $publicationyear,
2444 branchcode => $branchcode,
2445 order_by => $order_by
2448 Return a list of invoices that match all given criteria.
2450 $order_by is "column_name (asc|desc)", where column_name is any of
2451 'invoicenumber', 'booksellerid', 'shipmentdate', 'billingdate', 'closedate',
2452 'shipmentcost', 'shipmentcost_budgetid'.
2454 asc is the default if omitted
2461 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2462 closedate shipmentcost shipmentcost_budgetid);
2464 my $dbh = C4::Context->dbh;
2466 SELECT aqinvoices.*, aqbooksellers.name AS suppliername,
2469 aqorders.datereceived IS NOT NULL,
2470 aqorders.biblionumber,
2473 ) AS receivedbiblios,
2474 SUM(aqorders.quantityreceived) AS receiveditems
2476 LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid
2477 LEFT JOIN aqorders ON aqorders.invoiceid = aqinvoices.invoiceid
2478 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
2479 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2480 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2481 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
2482 LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber
2487 if($args{supplierid}) {
2488 push @bind_strs, " aqinvoices.booksellerid = ? ";
2489 push @bind_args, $args{supplierid};
2491 if($args{invoicenumber}) {
2492 push @bind_strs, " aqinvoices.invoicenumber LIKE ? ";
2493 push @bind_args, "%$args{invoicenumber}%";
2495 if($args{suppliername}) {
2496 push @bind_strs, " aqbooksellers.name LIKE ? ";
2497 push @bind_args, "%$args{suppliername}%";
2499 if($args{shipmentdatefrom}) {
2500 push @bind_strs, " aqinvoices.shipmentdate >= ? ";
2501 push @bind_args, $args{shipmentdatefrom};
2503 if($args{shipmentdateto}) {
2504 push @bind_strs, " aqinvoices.shipmentdate <= ? ";
2505 push @bind_args, $args{shipmentdateto};
2507 if($args{billingdatefrom}) {
2508 push @bind_strs, " aqinvoices.billingdate >= ? ";
2509 push @bind_args, $args{billingdatefrom};
2511 if($args{billingdateto}) {
2512 push @bind_strs, " aqinvoices.billingdate <= ? ";
2513 push @bind_args, $args{billingdateto};
2515 if($args{isbneanissn}) {
2516 push @bind_strs, " (biblioitems.isbn LIKE CONCAT('%', ?, '%') OR biblioitems.ean LIKE CONCAT('%', ?, '%') OR biblioitems.issn LIKE CONCAT('%', ?, '%') ) ";
2517 push @bind_args, $args{isbneanissn}, $args{isbneanissn}, $args{isbneanissn};
2520 push @bind_strs, " biblio.title LIKE CONCAT('%', ?, '%') ";
2521 push @bind_args, $args{title};
2524 push @bind_strs, " biblio.author LIKE CONCAT('%', ?, '%') ";
2525 push @bind_args, $args{author};
2527 if($args{publisher}) {
2528 push @bind_strs, " biblioitems.publishercode LIKE CONCAT('%', ?, '%') ";
2529 push @bind_args, $args{publisher};
2531 if($args{publicationyear}) {
2532 push @bind_strs, " ((biblioitems.publicationyear LIKE CONCAT('%', ?, '%')) OR (biblio.copyrightdate LIKE CONCAT('%', ?, '%'))) ";
2533 push @bind_args, $args{publicationyear}, $args{publicationyear};
2535 if($args{branchcode}) {
2536 push @bind_strs, " borrowers.branchcode = ? ";
2537 push @bind_args, $args{branchcode};
2540 $query .= " WHERE " . join(" AND ", @bind_strs) if @bind_strs;
2541 $query .= " GROUP BY aqinvoices.invoiceid ";
2543 if($args{order_by}) {
2544 my ($column, $direction) = split / /, $args{order_by};
2545 if(grep /^$column$/, @columns) {
2546 $direction ||= 'ASC';
2547 $query .= " ORDER BY $column $direction";
2551 my $sth = $dbh->prepare($query);
2552 $sth->execute(@bind_args);
2554 my $results = $sth->fetchall_arrayref({});
2560 my $invoice = GetInvoice($invoiceid);
2562 Get informations about invoice with given $invoiceid
2564 Return a hash filled with aqinvoices.* fields
2569 my ($invoiceid) = @_;
2572 return unless $invoiceid;
2574 my $dbh = C4::Context->dbh;
2580 my $sth = $dbh->prepare($query);
2581 $sth->execute($invoiceid);
2583 $invoice = $sth->fetchrow_hashref;
2587 =head3 GetInvoiceDetails
2589 my $invoice = GetInvoiceDetails($invoiceid)
2591 Return informations about an invoice + the list of related order lines
2593 Orders informations are in $invoice->{orders} (array ref)
2597 sub GetInvoiceDetails {
2598 my ($invoiceid) = @_;
2600 if ( !defined $invoiceid ) {
2601 carp 'GetInvoiceDetails called without an invoiceid';
2605 my $dbh = C4::Context->dbh;
2607 SELECT aqinvoices.*, aqbooksellers.name AS suppliername
2609 LEFT JOIN aqbooksellers ON aqinvoices.booksellerid = aqbooksellers.id
2612 my $sth = $dbh->prepare($query);
2613 $sth->execute($invoiceid);
2615 my $invoice = $sth->fetchrow_hashref;
2618 SELECT aqorders.*, biblio.*, aqbasket.basketname
2620 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
2621 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2624 $sth = $dbh->prepare($query);
2625 $sth->execute($invoiceid);
2626 $invoice->{orders} = $sth->fetchall_arrayref({});
2627 $invoice->{orders} ||= []; # force an empty arrayref if fetchall_arrayref fails
2634 my $invoiceid = AddInvoice(
2635 invoicenumber => $invoicenumber,
2636 booksellerid => $booksellerid,
2637 shipmentdate => $shipmentdate,
2638 billingdate => $billingdate,
2639 closedate => $closedate,
2640 shipmentcost => $shipmentcost,
2641 shipmentcost_budgetid => $shipmentcost_budgetid
2644 Create a new invoice and return its id or undef if it fails.
2651 return unless(%invoice and $invoice{invoicenumber});
2653 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2654 closedate shipmentcost shipmentcost_budgetid);
2658 foreach my $key (keys %invoice) {
2659 if(0 < grep(/^$key$/, @columns)) {
2660 push @set_strs, "$key = ?";
2661 push @set_args, ($invoice{$key} || undef);
2667 my $dbh = C4::Context->dbh;
2668 my $query = "INSERT INTO aqinvoices SET ";
2669 $query .= join (",", @set_strs);
2670 my $sth = $dbh->prepare($query);
2671 $rv = $sth->execute(@set_args);
2673 $rv = $dbh->last_insert_id(undef, undef, 'aqinvoices', undef);
2682 invoiceid => $invoiceid, # Mandatory
2683 invoicenumber => $invoicenumber,
2684 booksellerid => $booksellerid,
2685 shipmentdate => $shipmentdate,
2686 billingdate => $billingdate,
2687 closedate => $closedate,
2688 shipmentcost => $shipmentcost,
2689 shipmentcost_budgetid => $shipmentcost_budgetid
2692 Modify an invoice, invoiceid is mandatory.
2694 Return undef if it fails.
2701 return unless(%invoice and $invoice{invoiceid});
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);
2715 my $dbh = C4::Context->dbh;
2716 my $query = "UPDATE aqinvoices SET ";
2717 $query .= join(",", @set_strs);
2718 $query .= " WHERE invoiceid = ?";
2720 my $sth = $dbh->prepare($query);
2721 $sth->execute(@set_args, $invoice{invoiceid});
2726 CloseInvoice($invoiceid);
2730 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => undef);
2735 my ($invoiceid) = @_;
2737 return unless $invoiceid;
2739 my $dbh = C4::Context->dbh;
2742 SET closedate = CAST(NOW() AS DATE)
2745 my $sth = $dbh->prepare($query);
2746 $sth->execute($invoiceid);
2749 =head3 ReopenInvoice
2751 ReopenInvoice($invoiceid);
2755 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => C4::Dates->new()->output('iso'))
2760 my ($invoiceid) = @_;
2762 return unless $invoiceid;
2764 my $dbh = C4::Context->dbh;
2767 SET closedate = NULL
2770 my $sth = $dbh->prepare($query);
2771 $sth->execute($invoiceid);
2776 DelInvoice($invoiceid);
2778 Delete an invoice if there are no items attached to it.
2783 my ($invoiceid) = @_;
2785 return unless $invoiceid;
2787 my $dbh = C4::Context->dbh;
2793 my $sth = $dbh->prepare($query);
2794 $sth->execute($invoiceid);
2795 my $res = $sth->fetchrow_arrayref;
2796 if ( $res && $res->[0] == 0 ) {
2798 DELETE FROM aqinvoices
2801 my $sth = $dbh->prepare($query);
2802 return ( $sth->execute($invoiceid) > 0 );
2807 =head3 MergeInvoices
2809 MergeInvoices($invoiceid, \@sourceids);
2811 Merge the invoices identified by the IDs in \@sourceids into
2812 the invoice identified by $invoiceid.
2817 my ($invoiceid, $sourceids) = @_;
2819 return unless $invoiceid;
2820 foreach my $sourceid (@$sourceids) {
2821 next if $sourceid == $invoiceid;
2822 my $source = GetInvoiceDetails($sourceid);
2823 foreach my $order (@{$source->{'orders'}}) {
2824 $order->{'invoiceid'} = $invoiceid;
2827 DelInvoice($source->{'invoiceid'});
2832 =head3 GetBiblioCountByBasketno
2834 $biblio_count = &GetBiblioCountByBasketno($basketno);
2836 Looks up the biblio's count that has basketno value $basketno
2842 sub GetBiblioCountByBasketno {
2843 my ($basketno) = @_;
2844 my $dbh = C4::Context->dbh;
2846 SELECT COUNT( DISTINCT( biblionumber ) )
2849 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
2852 my $sth = $dbh->prepare($query);
2853 $sth->execute($basketno);
2854 return $sth->fetchrow;
2862 Koha Development Team <http://koha-community.org/>