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);
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 &DelBasket &ModBasket
47 &GetBasketAsCSV &GetBasketGroupAsCSV
48 &GetBasketsByBookseller &GetBasketsByBasketgroup
49 &GetBasketsInfosByBookseller
53 &ModBasketgroup &NewBasketgroup &DelBasketgroup &GetBasketgroup &CloseBasketgroup
54 &GetBasketgroups &ReOpenBasketgroup
56 &NewOrder &DelOrder &ModOrder &GetPendingOrders &GetOrder &GetOrders
57 &GetOrderNumber &GetLateOrders &GetOrderFromItemnumber
58 &SearchOrder &GetHistory &GetRecentAcqui
59 &ModReceiveOrder &CancelReceipt &ModOrderBiblioitemNumber
62 &NewOrderItem &ModOrderItem &ModItemOrder
64 &GetParcels &GetParcel
65 &GetContracts &GetContract
75 &GetItemnumbersFromOrder
85 sub GetOrderFromItemnumber {
86 my ($itemnumber) = @_;
87 my $dbh = C4::Context->dbh;
90 SELECT * from aqorders LEFT JOIN aqorders_items
91 ON ( aqorders.ordernumber = aqorders_items.ordernumber )
92 WHERE itemnumber = ? |;
94 my $sth = $dbh->prepare($query);
98 $sth->execute($itemnumber);
100 my $order = $sth->fetchrow_hashref;
105 # Returns the itemnumber(s) associated with the ordernumber given in parameter
106 sub GetItemnumbersFromOrder {
107 my ($ordernumber) = @_;
108 my $dbh = C4::Context->dbh;
109 my $query = "SELECT itemnumber FROM aqorders_items WHERE ordernumber=?";
110 my $sth = $dbh->prepare($query);
111 $sth->execute($ordernumber);
114 while (my $order = $sth->fetchrow_hashref) {
115 push @tab, $order->{'itemnumber'};
129 C4::Acquisition - Koha functions for dealing with orders and acquisitions
137 The functions in this module deal with acquisitions, managing book
138 orders, basket and parcels.
142 =head2 FUNCTIONS ABOUT BASKETS
146 $aqbasket = &GetBasket($basketnumber);
148 get all basket informations in aqbasket for a given basket
150 B<returns:> informations for a given basket returned as a hashref.
156 my $dbh = C4::Context->dbh;
159 concat( b.firstname,' ',b.surname) AS authorisedbyname,
160 b.branchcode AS branch
162 LEFT JOIN borrowers b ON aqbasket.authorisedby=b.borrowernumber
165 my $sth=$dbh->prepare($query);
166 $sth->execute($basketno);
167 my $basket = $sth->fetchrow_hashref;
171 #------------------------------------------------------------#
175 $basket = &NewBasket( $booksellerid, $authorizedby, $basketname,
176 $basketnote, $basketbooksellernote, $basketcontractnumber );
178 Create a new basket in aqbasket table
182 =item C<$booksellerid> is a foreign key in the aqbasket table
184 =item C<$authorizedby> is the username of who created the basket
188 The other parameters are optional, see ModBasketHeader for more info on them.
192 # FIXME : this function seems to be unused.
195 my ( $booksellerid, $authorisedby, $basketname, $basketnote, $basketbooksellernote, $basketcontractnumber ) = @_;
196 my $dbh = C4::Context->dbh;
199 (creationdate,booksellerid,authorisedby)
200 VALUES (now(),'$booksellerid','$authorisedby')
204 #find & return basketno MYSQL dependant, but $dbh->last_insert_id always returns null :-(
205 my $basket = $dbh->{'mysql_insertid'};
206 ModBasketHeader($basket, $basketname || '', $basketnote || '', $basketbooksellernote || '', $basketcontractnumber || undef, $booksellerid);
210 #------------------------------------------------------------#
214 &CloseBasket($basketno);
216 close a basket (becomes unmodifiable,except for recieves)
222 my $dbh = C4::Context->dbh;
228 my $sth = $dbh->prepare($query);
229 $sth->execute($basketno);
232 #------------------------------------------------------------#
234 =head3 GetBasketAsCSV
236 &GetBasketAsCSV($basketno);
238 Export a basket as CSV
240 $cgi parameter is needed for column name translation
245 my ($basketno, $cgi) = @_;
246 my $basket = GetBasket($basketno);
247 my @orders = GetOrders($basketno);
248 my $contract = GetContract($basket->{'contractnumber'});
250 my $template = C4::Templates::gettemplate("acqui/csv/basket.tmpl", "intranet", $cgi);
253 foreach my $order (@orders) {
254 my $bd = GetBiblioData( $order->{'biblionumber'} );
256 contractname => $contract->{'contractname'},
257 ordernumber => $order->{'ordernumber'},
258 entrydate => $order->{'entrydate'},
259 isbn => $order->{'isbn'},
260 author => $bd->{'author'},
261 title => $bd->{'title'},
262 publicationyear => $bd->{'publicationyear'},
263 publishercode => $bd->{'publishercode'},
264 collectiontitle => $bd->{'collectiontitle'},
265 notes => $order->{'notes'},
266 quantity => $order->{'quantity'},
267 rrp => $order->{'rrp'},
268 deliveryplace => $basket->{'deliveryplace'},
269 billingplace => $basket->{'billingplace'}
272 contractname author title publishercode collectiontitle notes
273 deliveryplace billingplace
275 # Double the quotes to not be interpreted as a field end
276 $row->{$_} =~ s/"/""/g if $row->{$_};
282 if(defined $a->{publishercode} and defined $b->{publishercode}) {
283 $a->{publishercode} cmp $b->{publishercode};
287 $template->param(rows => \@rows);
289 return $template->output;
293 =head3 GetBasketGroupAsCSV
297 &GetBasketGroupAsCSV($basketgroupid);
299 Export a basket group as CSV
301 $cgi parameter is needed for column name translation
307 sub GetBasketGroupAsCSV {
308 my ($basketgroupid, $cgi) = @_;
309 my $baskets = GetBasketsByBasketgroup($basketgroupid);
311 my $template = C4::Templates::gettemplate('acqui/csv/basketgroup.tmpl', 'intranet', $cgi);
314 for my $basket (@$baskets) {
315 my @orders = GetOrders( $$basket{basketno} );
316 my $contract = GetContract( $$basket{contractnumber} );
317 my $bookseller = GetBookSellerFromId( $$basket{booksellerid} );
319 foreach my $order (@orders) {
320 my $bd = GetBiblioData( $order->{'biblionumber'} );
322 clientnumber => $bookseller->{accountnumber},
323 basketname => $basket->{basketname},
324 ordernumber => $order->{ordernumber},
325 author => $bd->{author},
326 title => $bd->{title},
327 publishercode => $bd->{publishercode},
328 publicationyear => $bd->{publicationyear},
329 collectiontitle => $bd->{collectiontitle},
330 isbn => $order->{isbn},
331 quantity => $order->{quantity},
332 rrp => $order->{rrp},
333 discount => $bookseller->{discount},
334 ecost => $order->{ecost},
335 notes => $order->{notes},
336 entrydate => $order->{entrydate},
337 booksellername => $bookseller->{name},
338 bookselleraddress => $bookseller->{address1},
339 booksellerpostal => $bookseller->{postal},
340 contractnumber => $contract->{contractnumber},
341 contractname => $contract->{contractname},
344 basketname author title publishercode collectiontitle notes
345 booksellername bookselleraddress booksellerpostal contractname
346 basketgroupdeliveryplace basketgroupbillingplace
347 basketdeliveryplace basketbillingplace
349 # Double the quotes to not be interpreted as a field end
350 $row->{$_} =~ s/"/""/g if $row->{$_};
355 $template->param(rows => \@rows);
357 return $template->output;
361 =head3 CloseBasketgroup
363 &CloseBasketgroup($basketgroupno);
369 sub CloseBasketgroup {
370 my ($basketgroupno) = @_;
371 my $dbh = C4::Context->dbh;
372 my $sth = $dbh->prepare("
373 UPDATE aqbasketgroups
377 $sth->execute($basketgroupno);
380 #------------------------------------------------------------#
382 =head3 ReOpenBaskergroup($basketgroupno)
384 &ReOpenBaskergroup($basketgroupno);
390 sub ReOpenBasketgroup {
391 my ($basketgroupno) = @_;
392 my $dbh = C4::Context->dbh;
393 my $sth = $dbh->prepare("
394 UPDATE aqbasketgroups
398 $sth->execute($basketgroupno);
401 #------------------------------------------------------------#
406 &DelBasket($basketno);
408 Deletes the basket that has basketno field $basketno in the aqbasket table.
412 =item C<$basketno> is the primary key of the basket in the aqbasket table.
419 my ( $basketno ) = @_;
420 my $query = "DELETE FROM aqbasket WHERE basketno=?";
421 my $dbh = C4::Context->dbh;
422 my $sth = $dbh->prepare($query);
423 $sth->execute($basketno);
427 #------------------------------------------------------------#
431 &ModBasket($basketinfo);
433 Modifies a basket, using a hashref $basketinfo for the relevant information, only $basketinfo->{'basketno'} is required.
437 =item C<$basketno> is the primary key of the basket in the aqbasket table.
444 my $basketinfo = shift;
445 my $query = "UPDATE aqbasket SET ";
447 foreach my $key (keys %$basketinfo){
448 if ($key ne 'basketno'){
449 $query .= "$key=?, ";
450 push(@params, $basketinfo->{$key} || undef );
453 # get rid of the "," at the end of $query
454 if (substr($query, length($query)-2) eq ', '){
459 $query .= "WHERE basketno=?";
460 push(@params, $basketinfo->{'basketno'});
461 my $dbh = C4::Context->dbh;
462 my $sth = $dbh->prepare($query);
463 $sth->execute(@params);
467 #------------------------------------------------------------#
469 =head3 ModBasketHeader
471 &ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid);
473 Modifies a basket's header.
477 =item C<$basketno> is the "basketno" field in the "aqbasket" table;
479 =item C<$basketname> is the "basketname" field in the "aqbasket" table;
481 =item C<$note> is the "note" field in the "aqbasket" table;
483 =item C<$booksellernote> is the "booksellernote" field in the "aqbasket" table;
485 =item C<$contractnumber> is the "contractnumber" (foreign) key in the "aqbasket" table.
487 =item C<$booksellerid> is the id (foreign) key in the "aqbooksellers" table for the vendor.
493 sub ModBasketHeader {
494 my ($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid) = @_;
496 my $query = "UPDATE aqbasket SET basketname=?, note=?, booksellernote=?, booksellerid=? WHERE basketno=?";
497 my $dbh = C4::Context->dbh;
498 my $sth = $dbh->prepare($query);
499 $sth->execute($basketname,$note,$booksellernote,$booksellerid,$basketno);
501 if ( $contractnumber ) {
502 my $query2 ="UPDATE aqbasket SET contractnumber=? WHERE basketno=?";
503 my $sth2 = $dbh->prepare($query2);
504 $sth2->execute($contractnumber,$basketno);
510 #------------------------------------------------------------#
512 =head3 GetBasketsByBookseller
514 @results = &GetBasketsByBookseller($booksellerid, $extra);
516 Returns a list of hashes of all the baskets that belong to bookseller 'booksellerid'.
520 =item C<$booksellerid> is the 'id' field of the bookseller in the aqbooksellers table
522 =item C<$extra> is the extra sql parameters, can be
524 $extra->{groupby}: group baskets by column
525 ex. $extra->{groupby} = aqbasket.basketgroupid
526 $extra->{orderby}: order baskets by column
527 $extra->{limit}: limit number of results (can be helpful for pagination)
533 sub GetBasketsByBookseller {
534 my ($booksellerid, $extra) = @_;
535 my $query = "SELECT * FROM aqbasket WHERE booksellerid=?";
537 if ($extra->{groupby}) {
538 $query .= " GROUP by $extra->{groupby}";
540 if ($extra->{orderby}){
541 $query .= " ORDER by $extra->{orderby}";
543 if ($extra->{limit}){
544 $query .= " LIMIT $extra->{limit}";
547 my $dbh = C4::Context->dbh;
548 my $sth = $dbh->prepare($query);
549 $sth->execute($booksellerid);
550 my $results = $sth->fetchall_arrayref({});
555 =head3 GetBasketsInfosByBookseller
557 my $baskets = GetBasketsInfosByBookseller($supplierid);
559 Returns in a arrayref of hashref all about booksellers baskets, plus:
560 total_biblios: Number of distinct biblios in basket
561 total_items: Number of items in basket
562 expected_items: Number of non-received items in basket
566 sub GetBasketsInfosByBookseller {
567 my ($supplierid) = @_;
569 return unless $supplierid;
571 my $dbh = C4::Context->dbh;
574 SUM(aqorders.quantity) AS total_items,
575 COUNT(DISTINCT aqorders.biblionumber) AS total_biblios,
577 IF(aqorders.datereceived IS NULL
578 AND aqorders.datecancellationprinted IS NULL
583 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
584 WHERE booksellerid = ?
585 GROUP BY aqbasket.basketno
587 my $sth = $dbh->prepare($query);
588 $sth->execute($supplierid);
589 return $sth->fetchall_arrayref({});
593 #------------------------------------------------------------#
595 =head3 GetBasketsByBasketgroup
597 $baskets = &GetBasketsByBasketgroup($basketgroupid);
599 Returns a reference to all baskets that belong to basketgroup $basketgroupid.
603 sub GetBasketsByBasketgroup {
604 my $basketgroupid = shift;
606 SELECT *, aqbasket.booksellerid as booksellerid
608 LEFT JOIN aqcontract USING(contractnumber) WHERE basketgroupid=?
610 my $dbh = C4::Context->dbh;
611 my $sth = $dbh->prepare($query);
612 $sth->execute($basketgroupid);
613 my $results = $sth->fetchall_arrayref({});
618 #------------------------------------------------------------#
620 =head3 NewBasketgroup
622 $basketgroupid = NewBasketgroup(\%hashref);
624 Adds a basketgroup to the aqbasketgroups table, and add the initial baskets to it.
626 $hashref->{'booksellerid'} is the 'id' field of the bookseller in the aqbooksellers table,
628 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
630 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
632 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
634 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
636 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
641 my $basketgroupinfo = shift;
642 die "booksellerid is required to create a basketgroup" unless $basketgroupinfo->{'booksellerid'};
643 my $query = "INSERT INTO aqbasketgroups (";
645 foreach my $field ('name', 'deliveryplace', 'deliverycomment', 'closed') {
646 if ( $basketgroupinfo->{$field} ) {
647 $query .= "$field, ";
648 push(@params, $basketgroupinfo->{$field});
651 $query .= "booksellerid) VALUES (";
656 push(@params, $basketgroupinfo->{'booksellerid'});
657 my $dbh = C4::Context->dbh;
658 my $sth = $dbh->prepare($query);
659 $sth->execute(@params);
660 my $basketgroupid = $dbh->{'mysql_insertid'};
661 if( $basketgroupinfo->{'basketlist'} ) {
662 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
663 my $query2 = "UPDATE aqbasket SET basketgroupid=? WHERE basketno=?";
664 my $sth2 = $dbh->prepare($query2);
665 $sth2->execute($basketgroupid, $basketno);
668 return $basketgroupid;
671 #------------------------------------------------------------#
673 =head3 ModBasketgroup
675 ModBasketgroup(\%hashref);
677 Modifies a basketgroup in the aqbasketgroups table, and add the baskets to it.
679 $hashref->{'id'} is the 'id' field of the basketgroup in the aqbasketgroup table, this parameter is mandatory,
681 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
683 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
685 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
687 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
689 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
691 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
696 my $basketgroupinfo = shift;
697 die "basketgroup id is required to edit a basketgroup" unless $basketgroupinfo->{'id'};
698 my $dbh = C4::Context->dbh;
699 my $query = "UPDATE aqbasketgroups SET ";
701 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
702 if ( defined $basketgroupinfo->{$field} ) {
703 $query .= "$field=?, ";
704 push(@params, $basketgroupinfo->{$field});
709 $query .= " WHERE id=?";
710 push(@params, $basketgroupinfo->{'id'});
711 my $sth = $dbh->prepare($query);
712 $sth->execute(@params);
714 $sth = $dbh->prepare('UPDATE aqbasket SET basketgroupid = NULL WHERE basketgroupid = ?');
715 $sth->execute($basketgroupinfo->{'id'});
717 if($basketgroupinfo->{'basketlist'} && @{$basketgroupinfo->{'basketlist'}}){
718 $sth = $dbh->prepare("UPDATE aqbasket SET basketgroupid=? WHERE basketno=?");
719 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
720 $sth->execute($basketgroupinfo->{'id'}, $basketno);
727 #------------------------------------------------------------#
729 =head3 DelBasketgroup
731 DelBasketgroup($basketgroupid);
733 Deletes a basketgroup in the aqbasketgroups table, and removes the reference to it from the baskets,
737 =item C<$basketgroupid> is the 'id' field of the basket in the aqbasketgroup table
744 my $basketgroupid = shift;
745 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
746 my $query = "DELETE FROM aqbasketgroups WHERE id=?";
747 my $dbh = C4::Context->dbh;
748 my $sth = $dbh->prepare($query);
749 $sth->execute($basketgroupid);
753 #------------------------------------------------------------#
756 =head2 FUNCTIONS ABOUT ORDERS
758 =head3 GetBasketgroup
760 $basketgroup = &GetBasketgroup($basketgroupid);
762 Returns a reference to the hash containing all infermation about the basketgroup.
767 my $basketgroupid = shift;
768 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
769 my $query = "SELECT * FROM aqbasketgroups WHERE id=?";
770 my $dbh = C4::Context->dbh;
771 my $sth = $dbh->prepare($query);
772 $sth->execute($basketgroupid);
773 my $result = $sth->fetchrow_hashref;
778 #------------------------------------------------------------#
780 =head3 GetBasketgroups
782 $basketgroups = &GetBasketgroups($booksellerid);
784 Returns a reference to the array of all the basketgroups of bookseller $booksellerid.
788 sub GetBasketgroups {
789 my $booksellerid = shift;
790 die "bookseller id is required to edit a basketgroup" unless $booksellerid;
791 my $query = "SELECT * FROM aqbasketgroups WHERE booksellerid=? ORDER BY `id` DESC";
792 my $dbh = C4::Context->dbh;
793 my $sth = $dbh->prepare($query);
794 $sth->execute($booksellerid);
795 my $results = $sth->fetchall_arrayref({});
800 #------------------------------------------------------------#
802 =head2 FUNCTIONS ABOUT ORDERS
806 #------------------------------------------------------------#
808 =head3 GetPendingOrders
810 $orders = &GetPendingOrders($supplierid,$grouped,$owner,$basketno,$ordernumber,$search,$ean);
812 Finds pending orders from the bookseller with the given ID. Ignores
813 completed and cancelled orders.
815 C<$booksellerid> contains the bookseller identifier
816 C<$owner> contains 0 or 1. 0 means any owner. 1 means only the list of orders entered by the user itself.
817 C<$grouped> is a boolean that, if set to 1 will group all order lines of the same basket
818 in a single result line
819 C<$orders> is a reference-to-array; each element is a reference-to-hash.
821 Used also by the filter in parcel.pl
828 These give the value of the corresponding field in the aqorders table
829 of the Koha database.
831 Results are ordered from most to least recent.
835 sub GetPendingOrders {
836 my ($supplierid,$grouped,$owner,$basketno,$ordernumber,$search,$ean) = @_;
837 my $dbh = C4::Context->dbh;
839 SELECT ".($grouped?"count(*),":"")."aqbasket.basketno,
840 surname,firstname,biblio.*,biblioitems.isbn,
841 aqbasket.closedate, aqbasket.creationdate, aqbasket.basketname,
844 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
845 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
846 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
847 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
848 WHERE (quantity > quantityreceived OR quantityreceived is NULL)
849 AND datecancellationprinted IS NULL";
851 my $userenv = C4::Context->userenv;
852 if ( C4::Context->preference("IndependantBranches") ) {
853 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
854 $strsth .= " AND (borrowers.branchcode = ?
855 or borrowers.branchcode = '')";
856 push @query_params, $userenv->{branch};
860 $strsth .= " AND aqbasket.booksellerid = ?";
861 push @query_params, $supplierid;
864 $strsth .= " AND (aqorders.ordernumber=?)";
865 push @query_params, $ordernumber;
868 $strsth .= " AND (biblio.title like ? OR biblio.author LIKE ? OR biblioitems.isbn like ?)";
869 push @query_params, ("%$search%","%$search%","%$search%");
872 $strsth .= " AND biblioitems.ean = ?";
873 push @query_params, $ean;
876 $strsth .= " AND aqbasket.basketno=? ";
877 push @query_params, $basketno;
880 $strsth .= " AND aqbasket.authorisedby=? ";
881 push @query_params, $userenv->{'number'};
883 $strsth .= " group by aqbasket.basketno" if $grouped;
884 $strsth .= " order by aqbasket.basketno";
885 my $sth = $dbh->prepare($strsth);
886 $sth->execute( @query_params );
887 my $results = $sth->fetchall_arrayref({});
892 #------------------------------------------------------------#
896 @orders = &GetOrders($basketnumber, $orderby);
898 Looks up the pending (non-cancelled) orders with the given basket
899 number. If C<$booksellerID> is non-empty, only orders from that seller
903 C<&basket> returns a two-element array. C<@orders> is an array of
904 references-to-hash, whose keys are the fields from the aqorders,
905 biblio, and biblioitems tables in the Koha database.
910 my ( $basketno, $orderby ) = @_;
911 my $dbh = C4::Context->dbh;
913 SELECT biblio.*,biblioitems.*,
918 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
919 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
920 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
922 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
925 $orderby = "biblioitems.publishercode,biblio.title" unless $orderby;
926 $query .= " ORDER BY $orderby";
927 my $sth = $dbh->prepare($query);
928 $sth->execute($basketno);
929 my $results = $sth->fetchall_arrayref({});
934 #------------------------------------------------------------#
936 =head3 GetOrderNumber
938 $ordernumber = &GetOrderNumber($biblioitemnumber, $biblionumber);
940 Looks up the ordernumber with the given biblionumber and biblioitemnumber.
942 Returns the number of this order.
946 =item C<$ordernumber> is the order number.
953 my ( $biblionumber,$biblioitemnumber ) = @_;
954 my $dbh = C4::Context->dbh;
959 AND biblioitemnumber=?
961 my $sth = $dbh->prepare($query);
962 $sth->execute( $biblionumber, $biblioitemnumber );
964 return $sth->fetchrow;
967 #------------------------------------------------------------#
971 $order = &GetOrder($ordernumber);
973 Looks up an order by order number.
975 Returns a reference-to-hash describing the order. The keys of
976 C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database.
981 my ($ordernumber) = @_;
982 my $dbh = C4::Context->dbh;
984 SELECT biblioitems.*, biblio.*, aqorders.*
986 LEFT JOIN biblio on biblio.biblionumber=aqorders.biblionumber
987 LEFT JOIN biblioitems on biblioitems.biblionumber=aqorders.biblionumber
988 WHERE aqorders.ordernumber=?
991 my $sth= $dbh->prepare($query);
992 $sth->execute($ordernumber);
993 my $data = $sth->fetchrow_hashref;
998 #------------------------------------------------------------#
1002 &NewOrder(\%hashref);
1004 Adds a new order to the database. Any argument that isn't described
1005 below is the new value of the field with the same name in the aqorders
1006 table of the Koha database.
1010 =item $hashref->{'basketno'} is the basketno foreign key in aqorders, it is mandatory
1012 =item $hashref->{'ordernumber'} is a "minimum order number."
1014 =item $hashref->{'budgetdate'} is effectively ignored.
1015 If it's undef (anything false) or the string 'now', the current day is used.
1016 Else, the upcoming July 1st is used.
1018 =item $hashref->{'subscription'} may be either "yes", or anything else for "no".
1020 =item $hashref->{'uncertainprice'} may be 0 for "the price is known" or 1 for "the price is uncertain"
1022 =item defaults entrydate to Now
1024 The following keys are used: "biblionumber", "title", "basketno", "quantity", "notes", "biblioitemnumber", "rrp", "ecost", "gstrate", "unitprice", "subscription", "sort1", "sort2", "booksellerinvoicenumber", "listprice", "budgetdate", "purchaseordernumber", "branchcode", "booksellerinvoicenumber", "bookfundid".
1031 my $orderinfo = shift;
1032 #### ------------------------------
1033 my $dbh = C4::Context->dbh;
1037 # if these parameters are missing, we can't continue
1038 for my $key (qw/basketno quantity biblionumber budget_id/) {
1039 croak "Mandatory parameter $key missing" unless $orderinfo->{$key};
1042 if ( defined $orderinfo->{subscription} && $orderinfo->{'subscription'} eq 'yes' ) {
1043 $orderinfo->{'subscription'} = 1;
1045 $orderinfo->{'subscription'} = 0;
1047 $orderinfo->{'entrydate'} ||= C4::Dates->new()->output("iso");
1048 if (!$orderinfo->{quantityreceived}) {
1049 $orderinfo->{quantityreceived} = 0;
1052 my $ordernumber=InsertInTable("aqorders",$orderinfo);
1053 if (not $orderinfo->{parent_ordernumber}) {
1054 my $sth = $dbh->prepare("
1056 SET parent_ordernumber = ordernumber
1057 WHERE ordernumber = ?
1059 $sth->execute($ordernumber);
1061 return ( $orderinfo->{'basketno'}, $ordernumber );
1066 #------------------------------------------------------------#
1075 my ($itemnumber, $ordernumber) = @_;
1076 my $dbh = C4::Context->dbh;
1078 INSERT INTO aqorders_items
1079 (itemnumber, ordernumber)
1082 my $sth = $dbh->prepare($query);
1083 $sth->execute( $itemnumber, $ordernumber);
1086 #------------------------------------------------------------#
1090 &ModOrder(\%hashref);
1092 Modifies an existing order. Updates the order with order number
1093 $hashref->{'ordernumber'} and biblionumber $hashref->{'biblionumber'}. All
1094 other keys of the hash update the fields with the same name in the aqorders
1095 table of the Koha database.
1100 my $orderinfo = shift;
1102 die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq '' ;
1103 die "Biblionumber is required" if $orderinfo->{'biblionumber'} eq '';
1105 my $dbh = C4::Context->dbh;
1108 # update uncertainprice to an integer, just in case (under FF, checked boxes have the value "ON" by default)
1109 $orderinfo->{uncertainprice}=1 if $orderinfo->{uncertainprice};
1111 # delete($orderinfo->{'branchcode'});
1112 # the hash contains a lot of entries not in aqorders, so get the columns ...
1113 my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;");
1115 my $colnames = $sth->{NAME};
1116 my $query = "UPDATE aqorders SET ";
1118 foreach my $orderinfokey (grep(!/ordernumber/, keys %$orderinfo)){
1119 # ... and skip hash entries that are not in the aqorders table
1120 # FIXME : probably not the best way to do it (would be better to have a correct hash)
1121 next unless grep(/^$orderinfokey$/, @$colnames);
1122 $query .= "$orderinfokey=?, ";
1123 push(@params, $orderinfo->{$orderinfokey});
1126 $query .= "timestamp=NOW() WHERE ordernumber=?";
1127 # push(@params, $specorderinfo{'ordernumber'});
1128 push(@params, $orderinfo->{'ordernumber'} );
1129 $sth = $dbh->prepare($query);
1130 $sth->execute(@params);
1134 #------------------------------------------------------------#
1138 &ModOrderItem(\%hashref);
1140 Modifies the itemnumber in the aqorders_items table. The input hash needs three entities:
1144 =item - itemnumber: the old itemnumber
1145 =item - ordernumber: the order this item is attached to
1146 =item - newitemnumber: the new itemnumber we want to attach the line to
1153 my $orderiteminfo = shift;
1154 if (! $orderiteminfo->{'ordernumber'} || ! $orderiteminfo->{'itemnumber'} || ! $orderiteminfo->{'newitemnumber'}){
1155 die "Ordernumber, itemnumber and newitemnumber is required";
1158 my $dbh = C4::Context->dbh;
1160 my $query = "UPDATE aqorders_items set itemnumber=? where itemnumber=? and ordernumber=?";
1161 my @params = ($orderiteminfo->{'newitemnumber'}, $orderiteminfo->{'itemnumber'}, $orderiteminfo->{'ordernumber'});
1162 my $sth = $dbh->prepare($query);
1163 $sth->execute(@params);
1169 ModItemOrder($itemnumber, $ordernumber);
1171 Modifies the ordernumber of an item in aqorders_items.
1176 my ($itemnumber, $ordernumber) = @_;
1178 return unless ($itemnumber and $ordernumber);
1180 my $dbh = C4::Context->dbh;
1182 UPDATE aqorders_items
1184 WHERE itemnumber = ?
1186 my $sth = $dbh->prepare($query);
1187 return $sth->execute($ordernumber, $itemnumber);
1190 #------------------------------------------------------------#
1193 =head3 ModOrderBibliotemNumber
1195 &ModOrderBiblioitemNumber($biblioitemnumber,$ordernumber, $biblionumber);
1197 Modifies the biblioitemnumber for an existing order.
1198 Updates the order with order number C<$ordernum> and biblionumber C<$biblionumber>.
1202 #FIXME: is this used at all?
1203 sub ModOrderBiblioitemNumber {
1204 my ($biblioitemnumber,$ordernumber, $biblionumber) = @_;
1205 my $dbh = C4::Context->dbh;
1208 SET biblioitemnumber = ?
1209 WHERE ordernumber = ?
1210 AND biblionumber = ?";
1211 my $sth = $dbh->prepare($query);
1212 $sth->execute( $biblioitemnumber, $ordernumber, $biblionumber );
1215 =head3 GetCancelledOrders
1217 my @orders = GetCancelledOrders($basketno, $orderby);
1219 Returns cancelled orders for a basket
1223 sub GetCancelledOrders {
1224 my ( $basketno, $orderby ) = @_;
1226 return () unless $basketno;
1228 my $dbh = C4::Context->dbh;
1230 SELECT biblio.*, biblioitems.*, aqorders.*, aqbudgets.*
1232 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1233 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1234 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1236 AND (datecancellationprinted IS NOT NULL
1237 AND datecancellationprinted <> '0000-00-00')
1240 $orderby = "aqorders.datecancellationprinted desc, aqorders.timestamp desc"
1242 $query .= " ORDER BY $orderby";
1243 my $sth = $dbh->prepare($query);
1244 $sth->execute($basketno);
1245 my $results = $sth->fetchall_arrayref( {} );
1251 #------------------------------------------------------------#
1253 =head3 ModReceiveOrder
1255 &ModReceiveOrder($biblionumber, $ordernumber, $quantityreceived, $user,
1256 $unitprice, $invoiceid, $biblioitemnumber,
1257 $bookfund, $rrp, \@received_itemnumbers);
1259 Updates an order, to reflect the fact that it was received, at least
1260 in part. All arguments not mentioned below update the fields with the
1261 same name in the aqorders table of the Koha database.
1263 If a partial order is received, splits the order into two.
1265 Updates the order with bibilionumber C<$biblionumber> and ordernumber
1271 sub ModReceiveOrder {
1273 $biblionumber, $ordernumber, $quantrec, $user, $cost, $ecost,
1274 $invoiceid, $rrp, $budget_id, $datereceived, $received_items
1278 my $dbh = C4::Context->dbh;
1279 $datereceived = C4::Dates->output('iso') unless $datereceived;
1280 my $suggestionid = GetSuggestionFromBiblionumber( $biblionumber );
1281 if ($suggestionid) {
1282 ModSuggestion( {suggestionid=>$suggestionid,
1283 STATUS=>'AVAILABLE',
1284 biblionumber=> $biblionumber}
1288 my $sth=$dbh->prepare("
1289 SELECT * FROM aqorders
1290 WHERE biblionumber=? AND aqorders.ordernumber=?");
1292 $sth->execute($biblionumber,$ordernumber);
1293 my $order = $sth->fetchrow_hashref();
1296 my $new_ordernumber = $ordernumber;
1297 if ( $order->{quantity} > $quantrec ) {
1298 # Split order line in two parts: the first is the original order line
1299 # without received items (the quantity is decreased),
1300 # the second part is a new order line with quantity=quantityrec
1301 # (entirely received)
1302 $sth=$dbh->prepare("
1305 WHERE ordernumber = ?
1308 $sth->execute($order->{quantity} - $quantrec, $ordernumber);
1312 delete $order->{'ordernumber'};
1313 $order->{'quantity'} = $quantrec;
1314 $order->{'quantityreceived'} = $quantrec;
1315 $order->{'datereceived'} = $datereceived;
1316 $order->{'invoiceid'} = $invoiceid;
1317 $order->{'unitprice'} = $cost;
1318 $order->{'rrp'} = $rrp;
1319 $order->{ecost} = $ecost;
1320 $order->{'orderstatus'} = 3; # totally received
1321 $new_ordernumber = NewOrder($order);
1323 if ($received_items) {
1324 foreach my $itemnumber (@$received_items) {
1325 ModItemOrder($itemnumber, $new_ordernumber);
1329 $sth=$dbh->prepare("update aqorders
1330 set quantityreceived=?,datereceived=?,invoiceid=?,
1331 unitprice=?,rrp=?,ecost=?
1332 where biblionumber=? and ordernumber=?");
1333 $sth->execute($quantrec,$datereceived,$invoiceid,$cost,$rrp,$ecost,$biblionumber,$ordernumber);
1336 return ($datereceived, $new_ordernumber);
1339 =head3 CancelReceipt
1341 my $parent_ordernumber = CancelReceipt($ordernumber);
1343 Cancel an order line receipt and update the parent order line, as if no
1345 If items are created at receipt (AcqCreateItem = receiving) then delete
1351 my $ordernumber = shift;
1353 return unless $ordernumber;
1355 my $dbh = C4::Context->dbh;
1357 SELECT datereceived, parent_ordernumber, quantity
1359 WHERE ordernumber = ?
1361 my $sth = $dbh->prepare($query);
1362 $sth->execute($ordernumber);
1363 my $order = $sth->fetchrow_hashref;
1365 warn "CancelReceipt: order $ordernumber does not exist";
1368 unless($order->{'datereceived'}) {
1369 warn "CancelReceipt: order $ordernumber is not received";
1373 my $parent_ordernumber = $order->{'parent_ordernumber'};
1375 if($parent_ordernumber == $ordernumber || not $parent_ordernumber) {
1376 # The order line has no parent, just mark it as not received
1379 SET quantityreceived = ?,
1382 WHERE ordernumber = ?
1384 $sth = $dbh->prepare($query);
1385 $sth->execute(0, undef, undef, $ordernumber);
1387 # The order line has a parent, increase parent quantity and delete
1390 SELECT quantity, datereceived
1392 WHERE ordernumber = ?
1394 $sth = $dbh->prepare($query);
1395 $sth->execute($parent_ordernumber);
1396 my $parent_order = $sth->fetchrow_hashref;
1397 unless($parent_order) {
1398 warn "Parent order $parent_ordernumber does not exist.";
1401 if($parent_order->{'datereceived'}) {
1402 warn "CancelReceipt: parent order is received.".
1403 " Can't cancel receipt.";
1409 WHERE ordernumber = ?
1411 $sth = $dbh->prepare($query);
1412 my $rv = $sth->execute(
1413 $order->{'quantity'} + $parent_order->{'quantity'},
1417 warn "Cannot update parent order line, so do not cancel".
1421 if(C4::Context->preference('AcqCreateItem') eq 'receiving') {
1422 # Remove items that were created at receipt
1424 DELETE FROM items, aqorders_items
1425 USING items, aqorders_items
1426 WHERE items.itemnumber = ? AND aqorders_items.itemnumber = ?
1428 $sth = $dbh->prepare($query);
1429 my @itemnumbers = GetItemnumbersFromOrder($ordernumber);
1430 foreach my $itemnumber (@itemnumbers) {
1431 $sth->execute($itemnumber, $itemnumber);
1435 my @itemnumbers = GetItemnumbersFromOrder($ordernumber);
1436 foreach my $itemnumber (@itemnumbers) {
1437 ModItemOrder($itemnumber, $parent_ordernumber);
1442 DELETE FROM aqorders
1443 WHERE ordernumber = ?
1445 $sth = $dbh->prepare($query);
1446 $sth->execute($ordernumber);
1450 return $parent_ordernumber;
1453 #------------------------------------------------------------#
1457 @results = &SearchOrder($search, $biblionumber, $complete);
1459 Searches for orders.
1461 C<$search> may take one of several forms: if it is an ISBN,
1462 C<&ordersearch> returns orders with that ISBN. If C<$search> is an
1463 order number, C<&ordersearch> returns orders with that order number
1464 and biblionumber C<$biblionumber>. Otherwise, C<$search> is considered
1465 to be a space-separated list of search terms; in this case, all of the
1466 terms must appear in the title (matching the beginning of title
1469 If C<$complete> is C<yes>, the results will include only completed
1470 orders. In any case, C<&ordersearch> ignores cancelled orders.
1472 C<&ordersearch> returns an array.
1473 C<@results> is an array of references-to-hash with the following keys:
1479 =item C<seriestitle>
1490 #### -------- SearchOrder-------------------------------
1491 my ( $ordernumber, $search, $ean, $supplierid, $basket ) = @_;
1493 my $dbh = C4::Context->dbh;
1498 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1499 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
1500 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
1501 WHERE (datecancellationprinted is NULL)";
1504 $query .= " AND (aqorders.ordernumber=?)";
1505 push @args, $ordernumber;
1508 $query .= " AND (biblio.title like ? OR biblio.author LIKE ? OR biblioitems.isbn like ?)";
1509 push @args, ("%$search%","%$search%","%$search%");
1512 $query .= " AND biblioitems.ean = ?";
1516 $query .= "AND aqbasket.booksellerid = ?";
1517 push @args, $supplierid;
1520 $query .= "AND aqorders.basketno = ?";
1521 push @args, $basket;
1524 my $sth = $dbh->prepare($query);
1525 $sth->execute(@args);
1526 my $results = $sth->fetchall_arrayref({});
1531 #------------------------------------------------------------#
1535 &DelOrder($biblionumber, $ordernumber);
1537 Cancel the order with the given order and biblio numbers. It does not
1538 delete any entries in the aqorders table, it merely marks them as
1544 my ( $bibnum, $ordernumber ) = @_;
1545 my $dbh = C4::Context->dbh;
1548 SET datecancellationprinted=now()
1549 WHERE biblionumber=? AND ordernumber=?
1551 my $sth = $dbh->prepare($query);
1552 $sth->execute( $bibnum, $ordernumber );
1554 my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1555 foreach my $itemnumber (@itemnumbers){
1556 C4::Items::DelItem( $dbh, $bibnum, $itemnumber );
1561 =head2 FUNCTIONS ABOUT PARCELS
1565 #------------------------------------------------------------#
1569 @results = &GetParcel($booksellerid, $code, $date);
1571 Looks up all of the received items from the supplier with the given
1572 bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders.
1574 C<@results> is an array of references-to-hash. The keys of each element are fields from
1575 the aqorders, biblio, and biblioitems tables of the Koha database.
1577 C<@results> is sorted alphabetically by book title.
1582 #gets all orders from a certain supplier, orders them alphabetically
1583 my ( $supplierid, $code, $datereceived ) = @_;
1584 my $dbh = C4::Context->dbh;
1587 if $code; # add % if we search on a given code (otherwise, let him empty)
1589 SELECT authorisedby,
1594 aqorders.biblionumber,
1595 aqorders.ordernumber,
1596 aqorders.parent_ordernumber,
1598 aqorders.quantityreceived,
1606 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
1607 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1608 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1609 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
1611 aqbasket.booksellerid = ?
1612 AND aqinvoices.invoicenumber LIKE ?
1613 AND aqorders.datereceived = ? ";
1615 my @query_params = ( $supplierid, $code, $datereceived );
1616 if ( C4::Context->preference("IndependantBranches") ) {
1617 my $userenv = C4::Context->userenv;
1618 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
1619 $strsth .= " and (borrowers.branchcode = ?
1620 or borrowers.branchcode = '')";
1621 push @query_params, $userenv->{branch};
1624 $strsth .= " ORDER BY aqbasket.basketno";
1625 # ## parcelinformation : $strsth
1626 my $sth = $dbh->prepare($strsth);
1627 $sth->execute( @query_params );
1628 while ( my $data = $sth->fetchrow_hashref ) {
1629 push( @results, $data );
1631 # ## countparcelbiblio: scalar(@results)
1637 #------------------------------------------------------------#
1641 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
1643 get a lists of parcels.
1650 is the bookseller this function has to get parcels.
1653 To know on what criteria the results list has to be ordered.
1656 is the booksellerinvoicenumber.
1658 =item $datefrom & $dateto
1659 to know on what date this function has to filter its search.
1664 a pointer on a hash list containing parcel informations as such :
1670 =item Last operation
1672 =item Number of biblio
1674 =item Number of items
1681 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
1682 my $dbh = C4::Context->dbh;
1683 my @query_params = ();
1685 SELECT aqinvoices.invoicenumber,
1686 datereceived,purchaseordernumber,
1687 count(DISTINCT biblionumber) AS biblio,
1688 sum(quantity) AS itemsexpected,
1689 sum(quantityreceived) AS itemsreceived
1690 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
1691 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
1692 WHERE aqbasket.booksellerid = ? and datereceived IS NOT NULL
1694 push @query_params, $bookseller;
1696 if ( defined $code ) {
1697 $strsth .= ' and aqinvoices.invoicenumber like ? ';
1698 # add a % to the end of the code to allow stemming.
1699 push @query_params, "$code%";
1702 if ( defined $datefrom ) {
1703 $strsth .= ' and datereceived >= ? ';
1704 push @query_params, $datefrom;
1707 if ( defined $dateto ) {
1708 $strsth .= 'and datereceived <= ? ';
1709 push @query_params, $dateto;
1712 $strsth .= "group by aqinvoices.invoicenumber,datereceived ";
1714 # can't use a placeholder to place this column name.
1715 # but, we could probably be checking to make sure it is a column that will be fetched.
1716 $strsth .= "order by $order " if ($order);
1718 my $sth = $dbh->prepare($strsth);
1720 $sth->execute( @query_params );
1721 my $results = $sth->fetchall_arrayref({});
1726 #------------------------------------------------------------#
1728 =head3 GetLateOrders
1730 @results = &GetLateOrders;
1732 Searches for bookseller with late orders.
1735 the table of supplier with late issues. This table is full of hashref.
1741 my $supplierid = shift;
1743 my $estimateddeliverydatefrom = shift;
1744 my $estimateddeliverydateto = shift;
1746 my $dbh = C4::Context->dbh;
1748 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
1749 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
1751 my @query_params = ();
1753 SELECT aqbasket.basketno,
1754 aqorders.ordernumber,
1755 DATE(aqbasket.closedate) AS orderdate,
1756 aqorders.rrp AS unitpricesupplier,
1757 aqorders.ecost AS unitpricelib,
1758 aqorders.claims_count AS claims_count,
1759 aqorders.claimed_date AS claimed_date,
1760 aqbudgets.budget_name AS budget,
1761 borrowers.branchcode AS branch,
1762 aqbooksellers.name AS supplier,
1763 aqbooksellers.id AS supplierid,
1764 biblio.author, biblio.title,
1765 biblioitems.publishercode AS publisher,
1766 biblioitems.publicationyear,
1767 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
1771 aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1772 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1773 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
1774 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
1775 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1776 WHERE aqorders.basketno = aqbasket.basketno
1777 AND ( datereceived = ''
1778 OR datereceived IS NULL
1779 OR aqorders.quantityreceived < aqorders.quantity
1781 AND aqbasket.closedate IS NOT NULL
1782 AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
1785 if ($dbdriver eq "mysql") {
1787 aqorders.quantity - IFNULL(aqorders.quantityreceived,0) AS quantity,
1788 (aqorders.quantity - IFNULL(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1789 DATEDIFF(CAST(now() AS date),closedate) AS latesince
1791 if ( defined $delay ) {
1792 $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ;
1793 push @query_params, $delay;
1796 HAVING quantity <> 0
1797 AND unitpricesupplier <> 0
1798 AND unitpricelib <> 0
1801 # FIXME: account for IFNULL as above
1803 aqorders.quantity AS quantity,
1804 aqorders.quantity * aqorders.rrp AS subtotal,
1805 (CAST(now() AS date) - closedate) AS latesince
1807 if ( defined $delay ) {
1808 $from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) ";
1809 push @query_params, $delay;
1812 if (defined $supplierid) {
1813 $from .= ' AND aqbasket.booksellerid = ? ';
1814 push @query_params, $supplierid;
1816 if (defined $branch) {
1817 $from .= ' AND borrowers.branchcode LIKE ? ';
1818 push @query_params, $branch;
1820 if ( defined $estimateddeliverydatefrom ) {
1822 AND aqbooksellers.deliverytime IS NOT NULL
1823 AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
1824 push @query_params, $estimateddeliverydatefrom;
1826 if ( defined $estimateddeliverydatefrom and defined $estimateddeliverydateto ) {
1827 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
1828 push @query_params, $estimateddeliverydateto;
1829 } elsif ( defined $estimateddeliverydatefrom ) {
1830 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
1832 if (C4::Context->preference("IndependantBranches")
1833 && C4::Context->userenv
1834 && C4::Context->userenv->{flags} != 1 ) {
1835 $from .= ' AND borrowers.branchcode LIKE ? ';
1836 push @query_params, C4::Context->userenv->{branch};
1838 my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier";
1839 $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params);
1840 my $sth = $dbh->prepare($query);
1841 $sth->execute(@query_params);
1843 while (my $data = $sth->fetchrow_hashref) {
1844 $data->{orderdate} = format_date($data->{orderdate});
1845 $data->{claimed_date} = format_date($data->{claimed_date});
1846 push @results, $data;
1851 #------------------------------------------------------------#
1855 (\@order_loop, $total_qty, $total_price, $total_qtyreceived) = GetHistory( %params );
1857 Retreives some acquisition history information
1865 basket - search both basket name and number
1866 booksellerinvoicenumber
1869 $order_loop is a list of hashrefs that each look like this:
1871 'author' => 'Twain, Mark',
1873 'biblionumber' => '215',
1875 'creationdate' => 'MM/DD/YYYY',
1876 'datereceived' => undef,
1879 'invoicenumber' => undef,
1881 'ordernumber' => '1',
1883 'quantityreceived' => undef,
1884 'title' => 'The Adventures of Huckleberry Finn'
1886 $total_qty is the sum of all of the quantities in $order_loop
1887 $total_price is the cost of each in $order_loop times the quantity
1888 $total_qtyreceived is the sum of all of the quantityreceived entries in $order_loop
1893 # don't run the query if there are no parameters (list would be too long for sure !)
1894 croak "No search params" unless @_;
1896 my $title = $params{title};
1897 my $author = $params{author};
1898 my $isbn = $params{isbn};
1899 my $ean = $params{ean};
1900 my $name = $params{name};
1901 my $from_placed_on = $params{from_placed_on};
1902 my $to_placed_on = $params{to_placed_on};
1903 my $basket = $params{basket};
1904 my $booksellerinvoicenumber = $params{booksellerinvoicenumber};
1905 my $basketgroupname = $params{basketgroupname};
1908 my $total_qtyreceived = 0;
1909 my $total_price = 0;
1911 my $dbh = C4::Context->dbh;
1919 aqbasket.basketname,
1920 aqbasket.basketgroupid,
1921 aqbasketgroups.name as groupname,
1923 aqbasket.creationdate,
1924 aqorders.datereceived,
1926 aqorders.quantityreceived,
1928 aqorders.ordernumber,
1929 aqinvoices.invoicenumber,
1930 aqbooksellers.id as id,
1931 aqorders.biblionumber
1933 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
1934 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
1935 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
1936 LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
1937 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
1938 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid";
1940 $query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber"
1941 if ( C4::Context->preference("IndependantBranches") );
1943 $query .= " WHERE (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') ";
1945 my @query_params = ();
1948 $query .= " AND biblio.title LIKE ? ";
1949 $title =~ s/\s+/%/g;
1950 push @query_params, "%$title%";
1954 $query .= " AND biblio.author LIKE ? ";
1955 push @query_params, "%$author%";
1959 $query .= " AND biblioitems.isbn LIKE ? ";
1960 push @query_params, "%$isbn%";
1962 if ( defined $ean and $ean ) {
1963 $query .= " AND biblioitems.ean = ? ";
1964 push @query_params, "$ean";
1967 $query .= " AND aqbooksellers.name LIKE ? ";
1968 push @query_params, "%$name%";
1971 if ( $from_placed_on ) {
1972 $query .= " AND creationdate >= ? ";
1973 push @query_params, $from_placed_on;
1976 if ( $to_placed_on ) {
1977 $query .= " AND creationdate <= ? ";
1978 push @query_params, $to_placed_on;
1982 if ($basket =~ m/^\d+$/) {
1983 $query .= " AND aqorders.basketno = ? ";
1984 push @query_params, $basket;
1986 $query .= " AND aqbasket.basketname LIKE ? ";
1987 push @query_params, "%$basket%";
1991 if ($booksellerinvoicenumber) {
1992 $query .= " AND aqinvoices.invoicenumber LIKE ? ";
1993 push @query_params, "%$booksellerinvoicenumber%";
1996 if ($basketgroupname) {
1997 $query .= " AND aqbasketgroups.name LIKE ? ";
1998 push @query_params, "%$basketgroupname%";
2001 if ( C4::Context->preference("IndependantBranches") ) {
2002 my $userenv = C4::Context->userenv;
2003 if ( $userenv && ($userenv->{flags} || 0) != 1 ) {
2004 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
2005 push @query_params, $userenv->{branch};
2008 $query .= " ORDER BY id";
2009 my $sth = $dbh->prepare($query);
2010 $sth->execute( @query_params );
2012 while ( my $line = $sth->fetchrow_hashref ) {
2013 $line->{count} = $cnt++;
2014 $line->{toggle} = 1 if $cnt % 2;
2015 push @order_loop, $line;
2016 $total_qty += $line->{'quantity'};
2017 $total_qtyreceived += $line->{'quantityreceived'};
2018 $total_price += $line->{'quantity'} * $line->{'ecost'};
2020 return \@order_loop, $total_qty, $total_price, $total_qtyreceived;
2023 =head2 GetRecentAcqui
2025 $results = GetRecentAcqui($days);
2027 C<$results> is a ref to a table which containts hashref
2031 sub GetRecentAcqui {
2033 my $dbh = C4::Context->dbh;
2037 ORDER BY timestamp DESC
2040 my $sth = $dbh->prepare($query);
2042 my $results = $sth->fetchall_arrayref({});
2048 $contractlist = &GetContracts($booksellerid, $activeonly);
2050 Looks up the contracts that belong to a bookseller
2052 Returns a list of contracts
2056 =item C<$booksellerid> is the "id" field in the "aqbooksellers" table.
2058 =item C<$activeonly> if exists get only contracts that are still active.
2065 my ( $booksellerid, $activeonly ) = @_;
2066 my $dbh = C4::Context->dbh;
2068 if (! $activeonly) {
2072 WHERE booksellerid=?
2077 WHERE booksellerid=?
2078 AND contractenddate >= CURDATE( )";
2080 my $sth = $dbh->prepare($query);
2081 $sth->execute( $booksellerid );
2083 while (my $data = $sth->fetchrow_hashref ) {
2084 push(@results, $data);
2090 #------------------------------------------------------------#
2094 $contract = &GetContract($contractID);
2096 Looks up the contract that has PRIMKEY (contractnumber) value $contractID
2103 my ( $contractno ) = @_;
2104 my $dbh = C4::Context->dbh;
2108 WHERE contractnumber=?
2111 my $sth = $dbh->prepare($query);
2112 $sth->execute( $contractno );
2113 my $result = $sth->fetchrow_hashref;
2121 &AddClaim($ordernumber);
2123 Add a claim for an order
2129 my ($ordernumber) = @_;
2130 my $dbh = C4::Context->dbh;
2133 claims_count = claims_count + 1,
2134 claimed_date = CURDATE()
2135 WHERE ordernumber = ?
2137 my $sth = $dbh->prepare($query);
2138 $sth->execute($ordernumber);
2143 my @invoices = GetInvoices(
2144 invoicenumber => $invoicenumber,
2145 suppliername => $suppliername,
2146 shipmentdatefrom => $shipmentdatefrom, # ISO format
2147 shipmentdateto => $shipmentdateto, # ISO format
2148 billingdatefrom => $billingdatefrom, # ISO format
2149 billingdateto => $billingdateto, # ISO format
2150 isbneanissn => $isbn_or_ean_or_issn,
2153 publisher => $publisher,
2154 publicationyear => $publicationyear,
2155 branchcode => $branchcode,
2156 order_by => $order_by
2159 Return a list of invoices that match all given criteria.
2161 $order_by is "column_name (asc|desc)", where column_name is any of
2162 'invoicenumber', 'booksellerid', 'shipmentdate', 'billingdate', 'closedate',
2163 'shipmentcost', 'shipmentcost_budgetid'.
2165 asc is the default if omitted
2172 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2173 closedate shipmentcost shipmentcost_budgetid);
2175 my $dbh = C4::Context->dbh;
2177 SELECT aqinvoices.*, aqbooksellers.name AS suppliername,
2180 aqorders.datereceived IS NOT NULL,
2181 aqorders.biblionumber,
2184 ) AS receivedbiblios,
2185 SUM(aqorders.quantityreceived) AS receiveditems
2187 LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid
2188 LEFT JOIN aqorders ON aqorders.invoiceid = aqinvoices.invoiceid
2189 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2190 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
2191 LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber
2196 if($args{supplierid}) {
2197 push @bind_strs, " aqinvoices.booksellerid = ? ";
2198 push @bind_args, $args{supplierid};
2200 if($args{invoicenumber}) {
2201 push @bind_strs, " aqinvoices.invoicenumber LIKE ? ";
2202 push @bind_args, "%$args{invoicenumber}%";
2204 if($args{suppliername}) {
2205 push @bind_strs, " aqbooksellers.name LIKE ? ";
2206 push @bind_args, "%$args{suppliername}%";
2208 if($args{shipmentdatefrom}) {
2209 push @bind_strs, " aqinvoices.shipementdate >= ? ";
2210 push @bind_args, $args{shipmentdatefrom};
2212 if($args{shipmentdateto}) {
2213 push @bind_strs, " aqinvoices.shipementdate <= ? ";
2214 push @bind_args, $args{shipmentdateto};
2216 if($args{billingdatefrom}) {
2217 push @bind_strs, " aqinvoices.billingdate >= ? ";
2218 push @bind_args, $args{billingdatefrom};
2220 if($args{billingdateto}) {
2221 push @bind_strs, " aqinvoices.billingdate <= ? ";
2222 push @bind_args, $args{billingdateto};
2224 if($args{isbneanissn}) {
2225 push @bind_strs, " (biblioitems.isbn LIKE ? OR biblioitems.ean LIKE ? OR biblioitems.issn LIKE ? ) ";
2226 push @bind_args, $args{isbneanissn}, $args{isbneanissn}, $args{isbneanissn};
2229 push @bind_strs, " biblio.title LIKE ? ";
2230 push @bind_args, $args{title};
2233 push @bind_strs, " biblio.author LIKE ? ";
2234 push @bind_args, $args{author};
2236 if($args{publisher}) {
2237 push @bind_strs, " biblioitems.publishercode LIKE ? ";
2238 push @bind_args, $args{publisher};
2240 if($args{publicationyear}) {
2241 push @bind_strs, " biblioitems.publicationyear = ? ";
2242 push @bind_args, $args{publicationyear};
2244 if($args{branchcode}) {
2245 push @bind_strs, " aqorders.branchcode = ? ";
2246 push @bind_args, $args{branchcode};
2249 $query .= " WHERE " . join(" AND ", @bind_strs) if @bind_strs;
2250 $query .= " GROUP BY aqinvoices.invoiceid ";
2252 if($args{order_by}) {
2253 my ($column, $direction) = split / /, $args{order_by};
2254 if(grep /^$column$/, @columns) {
2255 $direction ||= 'ASC';
2256 $query .= " ORDER BY $column $direction";
2260 my $sth = $dbh->prepare($query);
2261 $sth->execute(@bind_args);
2263 my $results = $sth->fetchall_arrayref({});
2269 my $invoice = GetInvoice($invoiceid);
2271 Get informations about invoice with given $invoiceid
2273 Return a hash filled with aqinvoices.* fields
2278 my ($invoiceid) = @_;
2281 return unless $invoiceid;
2283 my $dbh = C4::Context->dbh;
2289 my $sth = $dbh->prepare($query);
2290 $sth->execute($invoiceid);
2292 $invoice = $sth->fetchrow_hashref;
2296 =head3 GetInvoiceDetails
2298 my $invoice = GetInvoiceDetails($invoiceid)
2300 Return informations about an invoice + the list of related order lines
2302 Orders informations are in $invoice->{orders} (array ref)
2306 sub GetInvoiceDetails {
2307 my ($invoiceid) = @_;
2310 return unless $invoiceid;
2312 my $dbh = C4::Context->dbh;
2314 SELECT aqinvoices.*, aqbooksellers.name AS suppliername
2316 LEFT JOIN aqbooksellers ON aqinvoices.booksellerid = aqbooksellers.id
2319 my $sth = $dbh->prepare($query);
2320 $sth->execute($invoiceid);
2322 $invoice = $sth->fetchrow_hashref;
2325 SELECT aqorders.*, biblio.*
2327 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2330 $sth = $dbh->prepare($query);
2331 $sth->execute($invoiceid);
2332 $invoice->{orders} = $sth->fetchall_arrayref({});
2333 $invoice->{orders} ||= []; # force an empty arrayref if fetchall_arrayref fails
2340 my $invoiceid = AddInvoice(
2341 invoicenumber => $invoicenumber,
2342 booksellerid => $booksellerid,
2343 shipmentdate => $shipmentdate,
2344 billingdate => $billingdate,
2345 closedate => $closedate,
2346 shipmentcost => $shipmentcost,
2347 shipmentcost_budgetid => $shipmentcost_budgetid
2350 Create a new invoice and return its id or undef if it fails.
2357 return unless(%invoice and $invoice{invoicenumber});
2359 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2360 closedate shipmentcost shipmentcost_budgetid);
2364 foreach my $key (keys %invoice) {
2365 if(0 < grep(/^$key$/, @columns)) {
2366 push @set_strs, "$key = ?";
2367 push @set_args, ($invoice{$key} || undef);
2373 my $dbh = C4::Context->dbh;
2374 my $query = "INSERT INTO aqinvoices SET ";
2375 $query .= join (",", @set_strs);
2376 my $sth = $dbh->prepare($query);
2377 $rv = $sth->execute(@set_args);
2379 $rv = $dbh->last_insert_id(undef, undef, 'aqinvoices', undef);
2388 invoiceid => $invoiceid, # Mandatory
2389 invoicenumber => $invoicenumber,
2390 booksellerid => $booksellerid,
2391 shipmentdate => $shipmentdate,
2392 billingdate => $billingdate,
2393 closedate => $closedate,
2394 shipmentcost => $shipmentcost,
2395 shipmentcost_budgetid => $shipmentcost_budgetid
2398 Modify an invoice, invoiceid is mandatory.
2400 Return undef if it fails.
2407 return unless(%invoice and $invoice{invoiceid});
2409 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2410 closedate shipmentcost shipmentcost_budgetid);
2414 foreach my $key (keys %invoice) {
2415 if(0 < grep(/^$key$/, @columns)) {
2416 push @set_strs, "$key = ?";
2417 push @set_args, ($invoice{$key} || undef);
2421 my $dbh = C4::Context->dbh;
2422 my $query = "UPDATE aqinvoices SET ";
2423 $query .= join(",", @set_strs);
2424 $query .= " WHERE invoiceid = ?";
2426 my $sth = $dbh->prepare($query);
2427 $sth->execute(@set_args, $invoice{invoiceid});
2432 CloseInvoice($invoiceid);
2436 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => undef);
2441 my ($invoiceid) = @_;
2443 return unless $invoiceid;
2445 my $dbh = C4::Context->dbh;
2448 SET closedate = CAST(NOW() AS DATE)
2451 my $sth = $dbh->prepare($query);
2452 $sth->execute($invoiceid);
2455 =head3 ReopenInvoice
2457 ReopenInvoice($invoiceid);
2461 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => C4::Dates->new()->output('iso'))
2466 my ($invoiceid) = @_;
2468 return unless $invoiceid;
2470 my $dbh = C4::Context->dbh;
2473 SET closedate = NULL
2476 my $sth = $dbh->prepare($query);
2477 $sth->execute($invoiceid);
2485 Koha Development Team <http://koha-community.org/>