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 &GetOrder &GetOrders &GetOrdersByBiblionumber
57 &GetLateOrders &GetOrderFromItemnumber
58 &SearchOrders &GetHistory &GetRecentAcqui
59 &ModReceiveOrder &CancelReceipt
60 &GetCancelledOrders &TransferOrder
61 &GetLastOrderNotReceivedFromSubscriptionid &GetLastOrderReceivedFromSubscriptionid
62 &NewOrderItem &ModItemOrder
64 &GetParcels &GetParcel
65 &GetContracts &GetContract
77 &GetItemnumbersFromOrder
87 sub GetOrderFromItemnumber {
88 my ($itemnumber) = @_;
89 my $dbh = C4::Context->dbh;
92 SELECT * from aqorders LEFT JOIN aqorders_items
93 ON ( aqorders.ordernumber = aqorders_items.ordernumber )
94 WHERE itemnumber = ? |;
96 my $sth = $dbh->prepare($query);
100 $sth->execute($itemnumber);
102 my $order = $sth->fetchrow_hashref;
107 # Returns the itemnumber(s) associated with the ordernumber given in parameter
108 sub GetItemnumbersFromOrder {
109 my ($ordernumber) = @_;
110 my $dbh = C4::Context->dbh;
111 my $query = "SELECT itemnumber FROM aqorders_items WHERE ordernumber=?";
112 my $sth = $dbh->prepare($query);
113 $sth->execute($ordernumber);
116 while (my $order = $sth->fetchrow_hashref) {
117 push @tab, $order->{'itemnumber'};
131 C4::Acquisition - Koha functions for dealing with orders and acquisitions
139 The functions in this module deal with acquisitions, managing book
140 orders, basket and parcels.
144 =head2 FUNCTIONS ABOUT BASKETS
148 $aqbasket = &GetBasket($basketnumber);
150 get all basket informations in aqbasket for a given basket
152 B<returns:> informations for a given basket returned as a hashref.
158 my $dbh = C4::Context->dbh;
161 concat( b.firstname,' ',b.surname) AS authorisedbyname,
162 b.branchcode AS branch
164 LEFT JOIN borrowers b ON aqbasket.authorisedby=b.borrowernumber
167 my $sth=$dbh->prepare($query);
168 $sth->execute($basketno);
169 my $basket = $sth->fetchrow_hashref;
173 #------------------------------------------------------------#
177 $basket = &NewBasket( $booksellerid, $authorizedby, $basketname,
178 $basketnote, $basketbooksellernote, $basketcontractnumber, $deliveryplace, $billingplace );
180 Create a new basket in aqbasket table
184 =item C<$booksellerid> is a foreign key in the aqbasket table
186 =item C<$authorizedby> is the username of who created the basket
190 The other parameters are optional, see ModBasketHeader for more info on them.
195 my ( $booksellerid, $authorisedby, $basketname, $basketnote,
196 $basketbooksellernote, $basketcontractnumber, $deliveryplace,
197 $billingplace ) = @_;
198 my $dbh = C4::Context->dbh;
200 'INSERT INTO aqbasket (creationdate,booksellerid,authorisedby) '
201 . 'VALUES (now(),?,?)';
202 $dbh->do( $query, {}, $booksellerid, $authorisedby );
204 my $basket = $dbh->{mysql_insertid};
205 $basketname ||= q{}; # default to empty strings
207 $basketbooksellernote ||= q{};
208 ModBasketHeader( $basket, $basketname, $basketnote, $basketbooksellernote,
209 $basketcontractnumber, $booksellerid, $deliveryplace, $billingplace );
213 #------------------------------------------------------------#
217 &CloseBasket($basketno);
219 close a basket (becomes unmodifiable,except for recieves)
225 my $dbh = C4::Context->dbh;
231 my $sth = $dbh->prepare($query);
232 $sth->execute($basketno);
235 #------------------------------------------------------------#
237 =head3 GetBasketAsCSV
239 &GetBasketAsCSV($basketno);
241 Export a basket as CSV
243 $cgi parameter is needed for column name translation
248 my ($basketno, $cgi) = @_;
249 my $basket = GetBasket($basketno);
250 my @orders = GetOrders($basketno);
251 my $contract = GetContract($basket->{'contractnumber'});
253 my $template = C4::Templates::gettemplate("acqui/csv/basket.tmpl", "intranet", $cgi);
256 foreach my $order (@orders) {
257 my $bd = GetBiblioData( $order->{'biblionumber'} );
259 contractname => $contract->{'contractname'},
260 ordernumber => $order->{'ordernumber'},
261 entrydate => $order->{'entrydate'},
262 isbn => $order->{'isbn'},
263 author => $bd->{'author'},
264 title => $bd->{'title'},
265 publicationyear => $bd->{'publicationyear'},
266 publishercode => $bd->{'publishercode'},
267 collectiontitle => $bd->{'collectiontitle'},
268 notes => $order->{'notes'},
269 quantity => $order->{'quantity'},
270 rrp => $order->{'rrp'},
271 deliveryplace => C4::Branch::GetBranchName( $basket->{'deliveryplace'} ),
272 billingplace => C4::Branch::GetBranchName( $basket->{'billingplace'} ),
275 contractname author title publishercode collectiontitle notes
276 deliveryplace billingplace
278 # Double the quotes to not be interpreted as a field end
279 $row->{$_} =~ s/"/""/g if $row->{$_};
285 if(defined $a->{publishercode} and defined $b->{publishercode}) {
286 $a->{publishercode} cmp $b->{publishercode};
290 $template->param(rows => \@rows);
292 return $template->output;
296 =head3 GetBasketGroupAsCSV
300 &GetBasketGroupAsCSV($basketgroupid);
302 Export a basket group as CSV
304 $cgi parameter is needed for column name translation
310 sub GetBasketGroupAsCSV {
311 my ($basketgroupid, $cgi) = @_;
312 my $baskets = GetBasketsByBasketgroup($basketgroupid);
314 my $template = C4::Templates::gettemplate('acqui/csv/basketgroup.tmpl', 'intranet', $cgi);
317 for my $basket (@$baskets) {
318 my @orders = GetOrders( $$basket{basketno} );
319 my $contract = GetContract( $$basket{contractnumber} );
320 my $bookseller = GetBookSellerFromId( $$basket{booksellerid} );
321 my $basketgroup = GetBasketgroup( $$basket{basketgroupid} );
323 foreach my $order (@orders) {
324 my $bd = GetBiblioData( $order->{'biblionumber'} );
326 clientnumber => $bookseller->{accountnumber},
327 basketname => $basket->{basketname},
328 ordernumber => $order->{ordernumber},
329 author => $bd->{author},
330 title => $bd->{title},
331 publishercode => $bd->{publishercode},
332 publicationyear => $bd->{publicationyear},
333 collectiontitle => $bd->{collectiontitle},
334 isbn => $order->{isbn},
335 quantity => $order->{quantity},
336 rrp => $order->{rrp},
337 discount => $bookseller->{discount},
338 ecost => $order->{ecost},
339 notes => $order->{notes},
340 entrydate => $order->{entrydate},
341 booksellername => $bookseller->{name},
342 bookselleraddress => $bookseller->{address1},
343 booksellerpostal => $bookseller->{postal},
344 contractnumber => $contract->{contractnumber},
345 contractname => $contract->{contractname},
346 basketgroupdeliveryplace => C4::Branch::GetBranchName( $basketgroup->{deliveryplace} ),
347 basketgroupbillingplace => C4::Branch::GetBranchName( $basketgroup->{billingplace} ),
348 basketdeliveryplace => C4::Branch::GetBranchName( $basket->{deliveryplace} ),
349 basketbillingplace => C4::Branch::GetBranchName( $basket->{billingplace} ),
352 basketname author title publishercode collectiontitle notes
353 booksellername bookselleraddress booksellerpostal contractname
354 basketgroupdeliveryplace basketgroupbillingplace
355 basketdeliveryplace basketbillingplace
357 # Double the quotes to not be interpreted as a field end
358 $row->{$_} =~ s/"/""/g if $row->{$_};
363 $template->param(rows => \@rows);
365 return $template->output;
369 =head3 CloseBasketgroup
371 &CloseBasketgroup($basketgroupno);
377 sub CloseBasketgroup {
378 my ($basketgroupno) = @_;
379 my $dbh = C4::Context->dbh;
380 my $sth = $dbh->prepare("
381 UPDATE aqbasketgroups
385 $sth->execute($basketgroupno);
388 #------------------------------------------------------------#
390 =head3 ReOpenBaskergroup($basketgroupno)
392 &ReOpenBaskergroup($basketgroupno);
398 sub ReOpenBasketgroup {
399 my ($basketgroupno) = @_;
400 my $dbh = C4::Context->dbh;
401 my $sth = $dbh->prepare("
402 UPDATE aqbasketgroups
406 $sth->execute($basketgroupno);
409 #------------------------------------------------------------#
414 &DelBasket($basketno);
416 Deletes the basket that has basketno field $basketno in the aqbasket table.
420 =item C<$basketno> is the primary key of the basket in the aqbasket table.
427 my ( $basketno ) = @_;
428 my $query = "DELETE FROM aqbasket WHERE basketno=?";
429 my $dbh = C4::Context->dbh;
430 my $sth = $dbh->prepare($query);
431 $sth->execute($basketno);
435 #------------------------------------------------------------#
439 &ModBasket($basketinfo);
441 Modifies a basket, using a hashref $basketinfo for the relevant information, only $basketinfo->{'basketno'} is required.
445 =item C<$basketno> is the primary key of the basket in the aqbasket table.
452 my $basketinfo = shift;
453 my $query = "UPDATE aqbasket SET ";
455 foreach my $key (keys %$basketinfo){
456 if ($key ne 'basketno'){
457 $query .= "$key=?, ";
458 push(@params, $basketinfo->{$key} || undef );
461 # get rid of the "," at the end of $query
462 if (substr($query, length($query)-2) eq ', '){
467 $query .= "WHERE basketno=?";
468 push(@params, $basketinfo->{'basketno'});
469 my $dbh = C4::Context->dbh;
470 my $sth = $dbh->prepare($query);
471 $sth->execute(@params);
475 #------------------------------------------------------------#
477 =head3 ModBasketHeader
479 &ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid);
481 Modifies a basket's header.
485 =item C<$basketno> is the "basketno" field in the "aqbasket" table;
487 =item C<$basketname> is the "basketname" field in the "aqbasket" table;
489 =item C<$note> is the "note" field in the "aqbasket" table;
491 =item C<$booksellernote> is the "booksellernote" field in the "aqbasket" table;
493 =item C<$contractnumber> is the "contractnumber" (foreign) key in the "aqbasket" table.
495 =item C<$booksellerid> is the id (foreign) key in the "aqbooksellers" table for the vendor.
497 =item C<$deliveryplace> is the "deliveryplace" field in the aqbasket table.
499 =item C<$billingplace> is the "billingplace" field in the aqbasket table.
505 sub ModBasketHeader {
506 my ($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid, $deliveryplace, $billingplace) = @_;
509 SET basketname=?, note=?, booksellernote=?, booksellerid=?, deliveryplace=?, billingplace=?
513 my $dbh = C4::Context->dbh;
514 my $sth = $dbh->prepare($query);
515 $sth->execute($basketname, $note, $booksellernote, $booksellerid, $deliveryplace, $billingplace, $basketno);
517 if ( $contractnumber ) {
518 my $query2 ="UPDATE aqbasket SET contractnumber=? WHERE basketno=?";
519 my $sth2 = $dbh->prepare($query2);
520 $sth2->execute($contractnumber,$basketno);
526 #------------------------------------------------------------#
528 =head3 GetBasketsByBookseller
530 @results = &GetBasketsByBookseller($booksellerid, $extra);
532 Returns a list of hashes of all the baskets that belong to bookseller 'booksellerid'.
536 =item C<$booksellerid> is the 'id' field of the bookseller in the aqbooksellers table
538 =item C<$extra> is the extra sql parameters, can be
540 $extra->{groupby}: group baskets by column
541 ex. $extra->{groupby} = aqbasket.basketgroupid
542 $extra->{orderby}: order baskets by column
543 $extra->{limit}: limit number of results (can be helpful for pagination)
549 sub GetBasketsByBookseller {
550 my ($booksellerid, $extra) = @_;
551 my $query = "SELECT * FROM aqbasket WHERE booksellerid=?";
553 if ($extra->{groupby}) {
554 $query .= " GROUP by $extra->{groupby}";
556 if ($extra->{orderby}){
557 $query .= " ORDER by $extra->{orderby}";
559 if ($extra->{limit}){
560 $query .= " LIMIT $extra->{limit}";
563 my $dbh = C4::Context->dbh;
564 my $sth = $dbh->prepare($query);
565 $sth->execute($booksellerid);
566 my $results = $sth->fetchall_arrayref({});
571 =head3 GetBasketsInfosByBookseller
573 my $baskets = GetBasketsInfosByBookseller($supplierid, $allbaskets);
575 The optional second parameter allbaskets is a boolean allowing you to
576 select all baskets from the supplier; by default only active baskets (open or
577 closed but still something to receive) are returned.
579 Returns in a arrayref of hashref all about booksellers baskets, plus:
580 total_biblios: Number of distinct biblios in basket
581 total_items: Number of items in basket
582 expected_items: Number of non-received items in basket
586 sub GetBasketsInfosByBookseller {
587 my ($supplierid, $allbaskets) = @_;
589 return unless $supplierid;
591 my $dbh = C4::Context->dbh;
594 SUM(aqorders.quantity) AS total_items,
595 COUNT(DISTINCT aqorders.biblionumber) AS total_biblios,
597 IF(aqorders.datereceived IS NULL
598 AND aqorders.datecancellationprinted IS NULL
603 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
604 WHERE booksellerid = ?};
606 $query.=" AND (closedate IS NULL OR (aqorders.quantity > aqorders.quantityreceived AND datecancellationprinted IS NULL))";
608 $query.=" GROUP BY aqbasket.basketno";
610 my $sth = $dbh->prepare($query);
611 $sth->execute($supplierid);
612 return $sth->fetchall_arrayref({});
616 #------------------------------------------------------------#
618 =head3 GetBasketsByBasketgroup
620 $baskets = &GetBasketsByBasketgroup($basketgroupid);
622 Returns a reference to all baskets that belong to basketgroup $basketgroupid.
626 sub GetBasketsByBasketgroup {
627 my $basketgroupid = shift;
629 SELECT *, aqbasket.booksellerid as booksellerid
631 LEFT JOIN aqcontract USING(contractnumber) WHERE basketgroupid=?
633 my $dbh = C4::Context->dbh;
634 my $sth = $dbh->prepare($query);
635 $sth->execute($basketgroupid);
636 my $results = $sth->fetchall_arrayref({});
641 #------------------------------------------------------------#
643 =head3 NewBasketgroup
645 $basketgroupid = NewBasketgroup(\%hashref);
647 Adds a basketgroup to the aqbasketgroups table, and add the initial baskets to it.
649 $hashref->{'booksellerid'} is the 'id' field of the bookseller in the aqbooksellers table,
651 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
653 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
655 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
657 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
659 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
661 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
663 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
668 my $basketgroupinfo = shift;
669 die "booksellerid is required to create a basketgroup" unless $basketgroupinfo->{'booksellerid'};
670 my $query = "INSERT INTO aqbasketgroups (";
672 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
673 if ( defined $basketgroupinfo->{$field} ) {
674 $query .= "$field, ";
675 push(@params, $basketgroupinfo->{$field});
678 $query .= "booksellerid) VALUES (";
683 push(@params, $basketgroupinfo->{'booksellerid'});
684 my $dbh = C4::Context->dbh;
685 my $sth = $dbh->prepare($query);
686 $sth->execute(@params);
687 my $basketgroupid = $dbh->{'mysql_insertid'};
688 if( $basketgroupinfo->{'basketlist'} ) {
689 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
690 my $query2 = "UPDATE aqbasket SET basketgroupid=? WHERE basketno=?";
691 my $sth2 = $dbh->prepare($query2);
692 $sth2->execute($basketgroupid, $basketno);
695 return $basketgroupid;
698 #------------------------------------------------------------#
700 =head3 ModBasketgroup
702 ModBasketgroup(\%hashref);
704 Modifies a basketgroup in the aqbasketgroups table, and add the baskets to it.
706 $hashref->{'id'} is the 'id' field of the basketgroup in the aqbasketgroup table, this parameter is mandatory,
708 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
710 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
712 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
714 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
716 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
718 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
720 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
725 my $basketgroupinfo = shift;
726 die "basketgroup id is required to edit a basketgroup" unless $basketgroupinfo->{'id'};
727 my $dbh = C4::Context->dbh;
728 my $query = "UPDATE aqbasketgroups SET ";
730 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
731 if ( defined $basketgroupinfo->{$field} ) {
732 $query .= "$field=?, ";
733 push(@params, $basketgroupinfo->{$field});
738 $query .= " WHERE id=?";
739 push(@params, $basketgroupinfo->{'id'});
740 my $sth = $dbh->prepare($query);
741 $sth->execute(@params);
743 $sth = $dbh->prepare('UPDATE aqbasket SET basketgroupid = NULL WHERE basketgroupid = ?');
744 $sth->execute($basketgroupinfo->{'id'});
746 if($basketgroupinfo->{'basketlist'} && @{$basketgroupinfo->{'basketlist'}}){
747 $sth = $dbh->prepare("UPDATE aqbasket SET basketgroupid=? WHERE basketno=?");
748 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
749 $sth->execute($basketgroupinfo->{'id'}, $basketno);
756 #------------------------------------------------------------#
758 =head3 DelBasketgroup
760 DelBasketgroup($basketgroupid);
762 Deletes a basketgroup in the aqbasketgroups table, and removes the reference to it from the baskets,
766 =item C<$basketgroupid> is the 'id' field of the basket in the aqbasketgroup table
773 my $basketgroupid = shift;
774 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
775 my $query = "DELETE FROM aqbasketgroups WHERE id=?";
776 my $dbh = C4::Context->dbh;
777 my $sth = $dbh->prepare($query);
778 $sth->execute($basketgroupid);
782 #------------------------------------------------------------#
785 =head2 FUNCTIONS ABOUT ORDERS
787 =head3 GetBasketgroup
789 $basketgroup = &GetBasketgroup($basketgroupid);
791 Returns a reference to the hash containing all infermation about the basketgroup.
796 my $basketgroupid = shift;
797 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
798 my $query = "SELECT * FROM aqbasketgroups WHERE id=?";
799 my $dbh = C4::Context->dbh;
800 my $sth = $dbh->prepare($query);
801 $sth->execute($basketgroupid);
802 my $result = $sth->fetchrow_hashref;
807 #------------------------------------------------------------#
809 =head3 GetBasketgroups
811 $basketgroups = &GetBasketgroups($booksellerid);
813 Returns a reference to the array of all the basketgroups of bookseller $booksellerid.
817 sub GetBasketgroups {
818 my $booksellerid = shift;
819 die 'bookseller id is required to edit a basketgroup' unless $booksellerid;
820 my $query = 'SELECT * FROM aqbasketgroups WHERE booksellerid=? ORDER BY id DESC';
821 my $dbh = C4::Context->dbh;
822 my $sth = $dbh->prepare($query);
823 $sth->execute($booksellerid);
824 return $sth->fetchall_arrayref({});
827 #------------------------------------------------------------#
829 =head2 FUNCTIONS ABOUT ORDERS
833 @orders = &GetOrders($basketnumber, $orderby);
835 Looks up the pending (non-cancelled) orders with the given basket
836 number. If C<$booksellerID> is non-empty, only orders from that seller
840 C<&basket> returns a two-element array. C<@orders> is an array of
841 references-to-hash, whose keys are the fields from the aqorders,
842 biblio, and biblioitems tables in the Koha database.
847 my ( $basketno, $orderby ) = @_;
848 my $dbh = C4::Context->dbh;
850 SELECT biblio.*,biblioitems.*,
854 aqorders_transfers.ordernumber_from AS transferred_from,
855 aqorders_transfers.timestamp AS transferred_from_timestamp
857 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
858 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
859 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
860 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
862 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
865 $orderby = "biblioitems.publishercode,biblio.title" unless $orderby;
866 $query .= " ORDER BY $orderby";
867 my $sth = $dbh->prepare($query);
868 $sth->execute($basketno);
869 my $results = $sth->fetchall_arrayref({});
874 #------------------------------------------------------------#
875 =head3 GetOrdersByBiblionumber
877 @orders = &GetOrdersByBiblionumber($biblionumber);
879 Looks up the orders with linked to a specific $biblionumber, including
880 cancelled orders and received orders.
883 C<@orders> is an array of references-to-hash, whose keys are the
884 fields from the aqorders, biblio, and biblioitems tables in the Koha database.
888 sub GetOrdersByBiblionumber {
889 my $biblionumber = shift;
890 return unless $biblionumber;
891 my $dbh = C4::Context->dbh;
893 SELECT biblio.*,biblioitems.*,
897 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
898 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
899 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
900 WHERE aqorders.biblionumber=?
902 my $sth = $dbh->prepare($query);
903 $sth->execute($biblionumber);
904 my $results = $sth->fetchall_arrayref({});
909 #------------------------------------------------------------#
913 $order = &GetOrder($ordernumber);
915 Looks up an order by order number.
917 Returns a reference-to-hash describing the order. The keys of
918 C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database.
923 my ($ordernumber) = @_;
924 my $dbh = C4::Context->dbh;
926 SELECT biblioitems.*, biblio.*, aqorders.*
928 LEFT JOIN biblio on biblio.biblionumber=aqorders.biblionumber
929 LEFT JOIN biblioitems on biblioitems.biblionumber=aqorders.biblionumber
930 WHERE aqorders.ordernumber=?
933 my $sth= $dbh->prepare($query);
934 $sth->execute($ordernumber);
935 my $data = $sth->fetchrow_hashref;
940 =head3 GetLastOrderNotReceivedFromSubscriptionid
942 $order = &GetLastOrderNotReceivedFromSubscriptionid($subscriptionid);
944 Returns a reference-to-hash describing the last order not received for a subscription.
948 sub GetLastOrderNotReceivedFromSubscriptionid {
949 my ( $subscriptionid ) = @_;
950 my $dbh = C4::Context->dbh;
952 SELECT * FROM aqorders
953 LEFT JOIN subscription
954 ON ( aqorders.subscriptionid = subscription.subscriptionid )
955 WHERE aqorders.subscriptionid = ?
956 AND aqorders.datereceived IS NULL
959 my $sth = $dbh->prepare( $query );
960 $sth->execute( $subscriptionid );
961 my $order = $sth->fetchrow_hashref;
965 =head3 GetLastOrderReceivedFromSubscriptionid
967 $order = &GetLastOrderReceivedFromSubscriptionid($subscriptionid);
969 Returns a reference-to-hash describing the last order received for a subscription.
973 sub GetLastOrderReceivedFromSubscriptionid {
974 my ( $subscriptionid ) = @_;
975 my $dbh = C4::Context->dbh;
977 SELECT * FROM aqorders
978 LEFT JOIN subscription
979 ON ( aqorders.subscriptionid = subscription.subscriptionid )
980 WHERE aqorders.subscriptionid = ?
981 AND aqorders.datereceived =
983 SELECT MAX( aqorders.datereceived )
985 LEFT JOIN subscription
986 ON ( aqorders.subscriptionid = subscription.subscriptionid )
987 WHERE aqorders.subscriptionid = ?
988 AND aqorders.datereceived IS NOT NULL
990 ORDER BY ordernumber DESC
993 my $sth = $dbh->prepare( $query );
994 $sth->execute( $subscriptionid, $subscriptionid );
995 my $order = $sth->fetchrow_hashref;
1001 #------------------------------------------------------------#
1005 &NewOrder(\%hashref);
1007 Adds a new order to the database. Any argument that isn't described
1008 below is the new value of the field with the same name in the aqorders
1009 table of the Koha database.
1013 =item $hashref->{'basketno'} is the basketno foreign key in aqorders, it is mandatory
1015 =item $hashref->{'ordernumber'} is a "minimum order number."
1017 =item $hashref->{'budgetdate'} is effectively ignored.
1018 If it's undef (anything false) or the string 'now', the current day is used.
1019 Else, the upcoming July 1st is used.
1021 =item $hashref->{'subscription'} may be either "yes", or anything else for "no".
1023 =item $hashref->{'uncertainprice'} may be 0 for "the price is known" or 1 for "the price is uncertain"
1025 =item defaults entrydate to Now
1027 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".
1034 my $orderinfo = shift;
1035 #### ------------------------------
1036 my $dbh = C4::Context->dbh;
1040 # if these parameters are missing, we can't continue
1041 for my $key (qw/basketno quantity biblionumber budget_id/) {
1042 croak "Mandatory parameter $key missing" unless $orderinfo->{$key};
1045 if ( defined $orderinfo->{subscription} && $orderinfo->{'subscription'} eq 'yes' ) {
1046 $orderinfo->{'subscription'} = 1;
1048 $orderinfo->{'subscription'} = 0;
1050 $orderinfo->{'entrydate'} ||= C4::Dates->new()->output("iso");
1051 if (!$orderinfo->{quantityreceived}) {
1052 $orderinfo->{quantityreceived} = 0;
1055 my $ordernumber=InsertInTable("aqorders",$orderinfo);
1056 if (not $orderinfo->{parent_ordernumber}) {
1057 my $sth = $dbh->prepare("
1059 SET parent_ordernumber = ordernumber
1060 WHERE ordernumber = ?
1062 $sth->execute($ordernumber);
1064 return ( $orderinfo->{'basketno'}, $ordernumber );
1069 #------------------------------------------------------------#
1078 my ($itemnumber, $ordernumber) = @_;
1079 my $dbh = C4::Context->dbh;
1081 INSERT INTO aqorders_items
1082 (itemnumber, ordernumber)
1085 my $sth = $dbh->prepare($query);
1086 $sth->execute( $itemnumber, $ordernumber);
1089 #------------------------------------------------------------#
1093 &ModOrder(\%hashref);
1095 Modifies an existing order. Updates the order with order number
1096 $hashref->{'ordernumber'} and biblionumber $hashref->{'biblionumber'}. All
1097 other keys of the hash update the fields with the same name in the aqorders
1098 table of the Koha database.
1103 my $orderinfo = shift;
1105 die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq '' ;
1106 die "Biblionumber is required" if $orderinfo->{'biblionumber'} eq '';
1108 my $dbh = C4::Context->dbh;
1111 # update uncertainprice to an integer, just in case (under FF, checked boxes have the value "ON" by default)
1112 $orderinfo->{uncertainprice}=1 if $orderinfo->{uncertainprice};
1114 # delete($orderinfo->{'branchcode'});
1115 # the hash contains a lot of entries not in aqorders, so get the columns ...
1116 my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;");
1118 my $colnames = $sth->{NAME};
1119 #FIXME Be careful. If aqorders would have columns with diacritics,
1120 #you should need to decode what you get back from NAME.
1121 #See report 10110 and guided_reports.pl
1122 my $query = "UPDATE aqorders SET ";
1124 foreach my $orderinfokey (grep(!/ordernumber/, keys %$orderinfo)){
1125 # ... and skip hash entries that are not in the aqorders table
1126 # FIXME : probably not the best way to do it (would be better to have a correct hash)
1127 next unless grep(/^$orderinfokey$/, @$colnames);
1128 $query .= "$orderinfokey=?, ";
1129 push(@params, $orderinfo->{$orderinfokey});
1132 $query .= "timestamp=NOW() WHERE ordernumber=?";
1133 # push(@params, $specorderinfo{'ordernumber'});
1134 push(@params, $orderinfo->{'ordernumber'} );
1135 $sth = $dbh->prepare($query);
1136 $sth->execute(@params);
1140 #------------------------------------------------------------#
1144 ModItemOrder($itemnumber, $ordernumber);
1146 Modifies the ordernumber of an item in aqorders_items.
1151 my ($itemnumber, $ordernumber) = @_;
1153 return unless ($itemnumber and $ordernumber);
1155 my $dbh = C4::Context->dbh;
1157 UPDATE aqorders_items
1159 WHERE itemnumber = ?
1161 my $sth = $dbh->prepare($query);
1162 return $sth->execute($ordernumber, $itemnumber);
1165 #------------------------------------------------------------#
1167 =head3 GetCancelledOrders
1169 my @orders = GetCancelledOrders($basketno, $orderby);
1171 Returns cancelled orders for a basket
1175 sub GetCancelledOrders {
1176 my ( $basketno, $orderby ) = @_;
1178 return () unless $basketno;
1180 my $dbh = C4::Context->dbh;
1187 aqorders_transfers.ordernumber_to AS transferred_to,
1188 aqorders_transfers.timestamp AS transferred_to_timestamp
1190 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1191 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1192 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1193 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber
1195 AND (datecancellationprinted IS NOT NULL
1196 AND datecancellationprinted <> '0000-00-00')
1199 $orderby = "aqorders.datecancellationprinted desc, aqorders.timestamp desc"
1201 $query .= " ORDER BY $orderby";
1202 my $sth = $dbh->prepare($query);
1203 $sth->execute($basketno);
1204 my $results = $sth->fetchall_arrayref( {} );
1210 #------------------------------------------------------------#
1212 =head3 ModReceiveOrder
1214 &ModReceiveOrder($biblionumber, $ordernumber, $quantityreceived, $user,
1215 $cost, $ecost, $invoiceid, rrp, budget_id, datereceived, \@received_itemnumbers);
1217 Updates an order, to reflect the fact that it was received, at least
1218 in part. All arguments not mentioned below update the fields with the
1219 same name in the aqorders table of the Koha database.
1221 If a partial order is received, splits the order into two.
1223 Updates the order with bibilionumber C<$biblionumber> and ordernumber
1229 sub ModReceiveOrder {
1231 $biblionumber, $ordernumber, $quantrec, $user, $cost, $ecost,
1232 $invoiceid, $rrp, $budget_id, $datereceived, $received_items
1236 my $dbh = C4::Context->dbh;
1237 $datereceived = C4::Dates->output('iso') unless $datereceived;
1238 my $suggestionid = GetSuggestionFromBiblionumber( $biblionumber );
1239 if ($suggestionid) {
1240 ModSuggestion( {suggestionid=>$suggestionid,
1241 STATUS=>'AVAILABLE',
1242 biblionumber=> $biblionumber}
1246 my $sth=$dbh->prepare("
1247 SELECT * FROM aqorders
1248 WHERE biblionumber=? AND aqorders.ordernumber=?");
1250 $sth->execute($biblionumber,$ordernumber);
1251 my $order = $sth->fetchrow_hashref();
1254 my $new_ordernumber = $ordernumber;
1255 if ( $order->{quantity} > $quantrec ) {
1256 # Split order line in two parts: the first is the original order line
1257 # without received items (the quantity is decreased),
1258 # the second part is a new order line with quantity=quantityrec
1259 # (entirely received)
1260 $sth=$dbh->prepare("
1263 WHERE ordernumber = ?
1266 $sth->execute($order->{quantity} - $quantrec, $ordernumber);
1270 delete $order->{'ordernumber'};
1271 $order->{'budget_id'} = ( $budget_id || $order->{'budget_id'} );
1272 $order->{'quantity'} = $quantrec;
1273 $order->{'quantityreceived'} = $quantrec;
1274 $order->{'datereceived'} = $datereceived;
1275 $order->{'invoiceid'} = $invoiceid;
1276 $order->{'unitprice'} = $cost;
1277 $order->{'rrp'} = $rrp;
1278 $order->{ecost} = $ecost;
1279 $order->{'orderstatus'} = 3; # totally received
1281 ( $basketno, $new_ordernumber ) = NewOrder($order);
1283 if ($received_items) {
1284 foreach my $itemnumber (@$received_items) {
1285 ModItemOrder($itemnumber, $new_ordernumber);
1289 $sth=$dbh->prepare("update aqorders
1290 set quantityreceived=?,datereceived=?,invoiceid=?,
1291 unitprice=?,rrp=?,ecost=?,budget_id=?
1292 where biblionumber=? and ordernumber=?");
1293 $sth->execute($quantrec,$datereceived,$invoiceid,$cost,$rrp,$ecost,$budget_id,$biblionumber,$ordernumber);
1296 return ($datereceived, $new_ordernumber);
1299 =head3 CancelReceipt
1301 my $parent_ordernumber = CancelReceipt($ordernumber);
1303 Cancel an order line receipt and update the parent order line, as if no
1305 If items are created at receipt (AcqCreateItem = receiving) then delete
1311 my $ordernumber = shift;
1313 return unless $ordernumber;
1315 my $dbh = C4::Context->dbh;
1317 SELECT datereceived, parent_ordernumber, quantity
1319 WHERE ordernumber = ?
1321 my $sth = $dbh->prepare($query);
1322 $sth->execute($ordernumber);
1323 my $order = $sth->fetchrow_hashref;
1325 warn "CancelReceipt: order $ordernumber does not exist";
1328 unless($order->{'datereceived'}) {
1329 warn "CancelReceipt: order $ordernumber is not received";
1333 my $parent_ordernumber = $order->{'parent_ordernumber'};
1335 if($parent_ordernumber == $ordernumber || not $parent_ordernumber) {
1336 # The order line has no parent, just mark it as not received
1339 SET quantityreceived = ?,
1342 WHERE ordernumber = ?
1344 $sth = $dbh->prepare($query);
1345 $sth->execute(0, undef, undef, $ordernumber);
1347 # The order line has a parent, increase parent quantity and delete
1350 SELECT quantity, datereceived
1352 WHERE ordernumber = ?
1354 $sth = $dbh->prepare($query);
1355 $sth->execute($parent_ordernumber);
1356 my $parent_order = $sth->fetchrow_hashref;
1357 unless($parent_order) {
1358 warn "Parent order $parent_ordernumber does not exist.";
1361 if($parent_order->{'datereceived'}) {
1362 warn "CancelReceipt: parent order is received.".
1363 " Can't cancel receipt.";
1369 WHERE ordernumber = ?
1371 $sth = $dbh->prepare($query);
1372 my $rv = $sth->execute(
1373 $order->{'quantity'} + $parent_order->{'quantity'},
1377 warn "Cannot update parent order line, so do not cancel".
1381 if(C4::Context->preference('AcqCreateItem') eq 'receiving') {
1382 # Remove items that were created at receipt
1384 DELETE FROM items, aqorders_items
1385 USING items, aqorders_items
1386 WHERE items.itemnumber = ? AND aqorders_items.itemnumber = ?
1388 $sth = $dbh->prepare($query);
1389 my @itemnumbers = GetItemnumbersFromOrder($ordernumber);
1390 foreach my $itemnumber (@itemnumbers) {
1391 $sth->execute($itemnumber, $itemnumber);
1395 my @itemnumbers = GetItemnumbersFromOrder($ordernumber);
1396 foreach my $itemnumber (@itemnumbers) {
1397 ModItemOrder($itemnumber, $parent_ordernumber);
1402 DELETE FROM aqorders
1403 WHERE ordernumber = ?
1405 $sth = $dbh->prepare($query);
1406 $sth->execute($ordernumber);
1410 return $parent_ordernumber;
1413 #------------------------------------------------------------#
1417 @results = &SearchOrders({
1418 ordernumber => $ordernumber,
1420 biblionumber => $biblionumber,
1422 booksellerid => $booksellerid,
1423 basketno => $basketno,
1428 Searches for orders.
1430 C<$owner> Finds order for the logged in user.
1431 C<$pending> Finds pending orders. Ignores completed and cancelled orders.
1434 C<@results> is an array of references-to-hash with the keys are fields
1435 from aqorders, biblio, biblioitems and aqbasket tables.
1440 my ( $params ) = @_;
1441 my $ordernumber = $params->{ordernumber};
1442 my $search = $params->{search};
1443 my $ean = $params->{ean};
1444 my $booksellerid = $params->{booksellerid};
1445 my $basketno = $params->{basketno};
1446 my $basketname = $params->{basketname};
1447 my $basketgroupname = $params->{basketgroupname};
1448 my $owner = $params->{owner};
1449 my $pending = $params->{pending};
1451 my $dbh = C4::Context->dbh;
1454 SELECT aqbasket.basketno,
1456 borrowers.firstname,
1459 biblioitems.biblioitemnumber,
1461 aqbasket.creationdate,
1462 aqbasket.basketname,
1465 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
1466 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
1467 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1468 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1469 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
1470 WHERE (datecancellationprinted is NULL)
1474 AND (quantity > quantityreceived OR quantityreceived is NULL)
1477 my $userenv = C4::Context->userenv;
1478 if ( C4::Context->preference("IndependentBranches") ) {
1479 if ( ( $userenv ) and ( $userenv->{flags} != 1 ) ) {
1482 borrowers.branchcode = ?
1483 OR borrowers.branchcode = ''
1486 push @args, $userenv->{branch};
1490 if ( $ordernumber ) {
1491 $query .= ' AND (aqorders.ordernumber=?)';
1492 push @args, $ordernumber;
1495 $query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)';
1496 push @args, ("%$search%","%$search%","%$search%");
1499 $query .= ' AND biblioitems.ean = ?';
1502 if ( $booksellerid ) {
1503 $query .= 'AND aqbasket.booksellerid = ?';
1504 push @args, $booksellerid;
1507 $query .= 'AND aqbasket.basketno = ?';
1508 push @args, $basketno;
1511 $query .= 'AND aqbasket.basketname LIKE ?';
1512 push @args, "%$basketname%";
1514 if( $basketgroupname ) {
1515 $query .= ' AND aqbasketgroups.name LIKE ?';
1516 push @args, "%$basketgroupname%";
1520 $query .= ' AND aqbasket.authorisedby=? ';
1521 push @args, $userenv->{'number'};
1524 $query .= ' ORDER BY aqbasket.basketno';
1526 my $sth = $dbh->prepare($query);
1527 $sth->execute(@args);
1528 return $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 =head3 TransferOrder
1563 my $newordernumber = TransferOrder($ordernumber, $basketno);
1565 Transfer an order line to a basket.
1566 Mark $ordernumber as cancelled with an internal note 'Cancelled and transfered
1567 to BOOKSELLER on DATE' and create new order with internal note
1568 'Transfered from BOOKSELLER on DATE'.
1569 Move all attached items to the new order.
1570 Received orders cannot be transfered.
1571 Return the ordernumber of created order.
1576 my ($ordernumber, $basketno) = @_;
1578 return unless ($ordernumber and $basketno);
1580 my $order = GetOrder( $ordernumber );
1581 return if $order->{datereceived};
1582 my $basket = GetBasket($basketno);
1583 return unless $basket;
1585 my $dbh = C4::Context->dbh;
1586 my ($query, $sth, $rv);
1590 SET datecancellationprinted = CAST(NOW() AS date)
1591 WHERE ordernumber = ?
1593 $sth = $dbh->prepare($query);
1594 $rv = $sth->execute($ordernumber);
1596 delete $order->{'ordernumber'};
1597 $order->{'basketno'} = $basketno;
1599 (undef, $newordernumber) = NewOrder($order);
1602 UPDATE aqorders_items
1604 WHERE ordernumber = ?
1606 $sth = $dbh->prepare($query);
1607 $sth->execute($newordernumber, $ordernumber);
1610 INSERT INTO aqorders_transfers (ordernumber_from, ordernumber_to)
1613 $sth = $dbh->prepare($query);
1614 $sth->execute($ordernumber, $newordernumber);
1616 return $newordernumber;
1619 =head2 FUNCTIONS ABOUT PARCELS
1623 #------------------------------------------------------------#
1627 @results = &GetParcel($booksellerid, $code, $date);
1629 Looks up all of the received items from the supplier with the given
1630 bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders.
1632 C<@results> is an array of references-to-hash. The keys of each element are fields from
1633 the aqorders, biblio, and biblioitems tables of the Koha database.
1635 C<@results> is sorted alphabetically by book title.
1640 #gets all orders from a certain supplier, orders them alphabetically
1641 my ( $supplierid, $code, $datereceived ) = @_;
1642 my $dbh = C4::Context->dbh;
1645 if $code; # add % if we search on a given code (otherwise, let him empty)
1647 SELECT authorisedby,
1652 aqorders.biblionumber,
1653 aqorders.ordernumber,
1654 aqorders.parent_ordernumber,
1656 aqorders.quantityreceived,
1664 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
1665 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1666 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1667 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
1669 aqbasket.booksellerid = ?
1670 AND aqinvoices.invoicenumber LIKE ?
1671 AND aqorders.datereceived = ? ";
1673 my @query_params = ( $supplierid, $code, $datereceived );
1674 if ( C4::Context->preference("IndependentBranches") ) {
1675 my $userenv = C4::Context->userenv;
1676 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
1677 $strsth .= " and (borrowers.branchcode = ?
1678 or borrowers.branchcode = '')";
1679 push @query_params, $userenv->{branch};
1682 $strsth .= " ORDER BY aqbasket.basketno";
1683 # ## parcelinformation : $strsth
1684 my $sth = $dbh->prepare($strsth);
1685 $sth->execute( @query_params );
1686 while ( my $data = $sth->fetchrow_hashref ) {
1687 push( @results, $data );
1689 # ## countparcelbiblio: scalar(@results)
1695 #------------------------------------------------------------#
1699 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
1701 get a lists of parcels.
1708 is the bookseller this function has to get parcels.
1711 To know on what criteria the results list has to be ordered.
1714 is the booksellerinvoicenumber.
1716 =item $datefrom & $dateto
1717 to know on what date this function has to filter its search.
1722 a pointer on a hash list containing parcel informations as such :
1728 =item Last operation
1730 =item Number of biblio
1732 =item Number of items
1739 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
1740 my $dbh = C4::Context->dbh;
1741 my @query_params = ();
1743 SELECT aqinvoices.invoicenumber,
1744 datereceived,purchaseordernumber,
1745 count(DISTINCT biblionumber) AS biblio,
1746 sum(quantity) AS itemsexpected,
1747 sum(quantityreceived) AS itemsreceived
1748 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
1749 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
1750 WHERE aqbasket.booksellerid = ? and datereceived IS NOT NULL
1752 push @query_params, $bookseller;
1754 if ( defined $code ) {
1755 $strsth .= ' and aqinvoices.invoicenumber like ? ';
1756 # add a % to the end of the code to allow stemming.
1757 push @query_params, "$code%";
1760 if ( defined $datefrom ) {
1761 $strsth .= ' and datereceived >= ? ';
1762 push @query_params, $datefrom;
1765 if ( defined $dateto ) {
1766 $strsth .= 'and datereceived <= ? ';
1767 push @query_params, $dateto;
1770 $strsth .= "group by aqinvoices.invoicenumber,datereceived ";
1772 # can't use a placeholder to place this column name.
1773 # but, we could probably be checking to make sure it is a column that will be fetched.
1774 $strsth .= "order by $order " if ($order);
1776 my $sth = $dbh->prepare($strsth);
1778 $sth->execute( @query_params );
1779 my $results = $sth->fetchall_arrayref({});
1784 #------------------------------------------------------------#
1786 =head3 GetLateOrders
1788 @results = &GetLateOrders;
1790 Searches for bookseller with late orders.
1793 the table of supplier with late issues. This table is full of hashref.
1799 my $supplierid = shift;
1801 my $estimateddeliverydatefrom = shift;
1802 my $estimateddeliverydateto = shift;
1804 my $dbh = C4::Context->dbh;
1806 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
1807 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
1809 my @query_params = ();
1811 SELECT aqbasket.basketno,
1812 aqorders.ordernumber,
1813 DATE(aqbasket.closedate) AS orderdate,
1814 aqorders.rrp AS unitpricesupplier,
1815 aqorders.ecost AS unitpricelib,
1816 aqorders.claims_count AS claims_count,
1817 aqorders.claimed_date AS claimed_date,
1818 aqbudgets.budget_name AS budget,
1819 borrowers.branchcode AS branch,
1820 aqbooksellers.name AS supplier,
1821 aqbooksellers.id AS supplierid,
1822 biblio.author, biblio.title,
1823 biblioitems.publishercode AS publisher,
1824 biblioitems.publicationyear,
1825 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
1829 aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1830 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1831 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
1832 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
1833 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1834 WHERE aqorders.basketno = aqbasket.basketno
1835 AND ( datereceived = ''
1836 OR datereceived IS NULL
1837 OR aqorders.quantityreceived < aqorders.quantity
1839 AND aqbasket.closedate IS NOT NULL
1840 AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
1843 if ($dbdriver eq "mysql") {
1845 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity,
1846 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1847 DATEDIFF(CAST(now() AS date),closedate) AS latesince
1849 if ( defined $delay ) {
1850 $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ;
1851 push @query_params, $delay;
1854 HAVING quantity <> 0
1855 AND unitpricesupplier <> 0
1856 AND unitpricelib <> 0
1859 # FIXME: account for IFNULL as above
1861 aqorders.quantity AS quantity,
1862 aqorders.quantity * aqorders.rrp AS subtotal,
1863 (CAST(now() AS date) - closedate) AS latesince
1865 if ( defined $delay ) {
1866 $from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) ";
1867 push @query_params, $delay;
1870 if (defined $supplierid) {
1871 $from .= ' AND aqbasket.booksellerid = ? ';
1872 push @query_params, $supplierid;
1874 if (defined $branch) {
1875 $from .= ' AND borrowers.branchcode LIKE ? ';
1876 push @query_params, $branch;
1879 if ( defined $estimateddeliverydatefrom or defined $estimateddeliverydateto ) {
1880 $from .= ' AND aqbooksellers.deliverytime IS NOT NULL ';
1882 if ( defined $estimateddeliverydatefrom ) {
1883 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
1884 push @query_params, $estimateddeliverydatefrom;
1886 if ( defined $estimateddeliverydateto ) {
1887 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
1888 push @query_params, $estimateddeliverydateto;
1890 if ( defined $estimateddeliverydatefrom and not defined $estimateddeliverydateto ) {
1891 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
1893 if (C4::Context->preference("IndependentBranches")
1894 && C4::Context->userenv
1895 && C4::Context->userenv->{flags} != 1 ) {
1896 $from .= ' AND borrowers.branchcode LIKE ? ';
1897 push @query_params, C4::Context->userenv->{branch};
1899 my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier";
1900 $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params);
1901 my $sth = $dbh->prepare($query);
1902 $sth->execute(@query_params);
1904 while (my $data = $sth->fetchrow_hashref) {
1905 $data->{orderdate} = format_date($data->{orderdate});
1906 $data->{claimed_date} = format_date($data->{claimed_date});
1907 push @results, $data;
1912 #------------------------------------------------------------#
1916 (\@order_loop, $total_qty, $total_price, $total_qtyreceived) = GetHistory( %params );
1918 Retreives some acquisition history information
1926 basket - search both basket name and number
1927 booksellerinvoicenumber
1930 $order_loop is a list of hashrefs that each look like this:
1932 'author' => 'Twain, Mark',
1934 'biblionumber' => '215',
1936 'creationdate' => 'MM/DD/YYYY',
1937 'datereceived' => undef,
1940 'invoicenumber' => undef,
1942 'ordernumber' => '1',
1944 'quantityreceived' => undef,
1945 'title' => 'The Adventures of Huckleberry Finn'
1947 $total_qty is the sum of all of the quantities in $order_loop
1948 $total_price is the cost of each in $order_loop times the quantity
1949 $total_qtyreceived is the sum of all of the quantityreceived entries in $order_loop
1954 # don't run the query if there are no parameters (list would be too long for sure !)
1955 croak "No search params" unless @_;
1957 my $title = $params{title};
1958 my $author = $params{author};
1959 my $isbn = $params{isbn};
1960 my $ean = $params{ean};
1961 my $name = $params{name};
1962 my $from_placed_on = $params{from_placed_on};
1963 my $to_placed_on = $params{to_placed_on};
1964 my $basket = $params{basket};
1965 my $booksellerinvoicenumber = $params{booksellerinvoicenumber};
1966 my $basketgroupname = $params{basketgroupname};
1969 my $total_qtyreceived = 0;
1970 my $total_price = 0;
1972 my $dbh = C4::Context->dbh;
1980 aqbasket.basketname,
1981 aqbasket.basketgroupid,
1982 aqbasketgroups.name as groupname,
1984 aqbasket.creationdate,
1985 aqorders.datereceived,
1987 aqorders.quantityreceived,
1989 aqorders.ordernumber,
1991 aqinvoices.invoicenumber,
1992 aqbooksellers.id as id,
1993 aqorders.biblionumber
1995 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
1996 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
1997 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
1998 LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
1999 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
2000 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid";
2002 $query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber"
2003 if ( C4::Context->preference("IndependentBranches") );
2005 $query .= " WHERE (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') ";
2007 my @query_params = ();
2010 $query .= " AND biblio.title LIKE ? ";
2011 $title =~ s/\s+/%/g;
2012 push @query_params, "%$title%";
2016 $query .= " AND biblio.author LIKE ? ";
2017 push @query_params, "%$author%";
2021 $query .= " AND biblioitems.isbn LIKE ? ";
2022 push @query_params, "%$isbn%";
2024 if ( defined $ean and $ean ) {
2025 $query .= " AND biblioitems.ean = ? ";
2026 push @query_params, "$ean";
2029 $query .= " AND aqbooksellers.name LIKE ? ";
2030 push @query_params, "%$name%";
2033 if ( $from_placed_on ) {
2034 $query .= " AND creationdate >= ? ";
2035 push @query_params, $from_placed_on;
2038 if ( $to_placed_on ) {
2039 $query .= " AND creationdate <= ? ";
2040 push @query_params, $to_placed_on;
2044 if ($basket =~ m/^\d+$/) {
2045 $query .= " AND aqorders.basketno = ? ";
2046 push @query_params, $basket;
2048 $query .= " AND aqbasket.basketname LIKE ? ";
2049 push @query_params, "%$basket%";
2053 if ($booksellerinvoicenumber) {
2054 $query .= " AND aqinvoices.invoicenumber LIKE ? ";
2055 push @query_params, "%$booksellerinvoicenumber%";
2058 if ($basketgroupname) {
2059 $query .= " AND aqbasketgroups.name LIKE ? ";
2060 push @query_params, "%$basketgroupname%";
2063 if ( C4::Context->preference("IndependentBranches") ) {
2064 my $userenv = C4::Context->userenv;
2065 if ( $userenv && ($userenv->{flags} || 0) != 1 ) {
2066 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
2067 push @query_params, $userenv->{branch};
2070 $query .= " ORDER BY id";
2071 my $sth = $dbh->prepare($query);
2072 $sth->execute( @query_params );
2074 while ( my $line = $sth->fetchrow_hashref ) {
2075 $line->{count} = $cnt++;
2076 $line->{toggle} = 1 if $cnt % 2;
2077 push @order_loop, $line;
2078 $total_qty += $line->{'quantity'};
2079 $total_qtyreceived += $line->{'quantityreceived'};
2080 $total_price += $line->{'quantity'} * $line->{'ecost'};
2082 return \@order_loop, $total_qty, $total_price, $total_qtyreceived;
2085 =head2 GetRecentAcqui
2087 $results = GetRecentAcqui($days);
2089 C<$results> is a ref to a table which containts hashref
2093 sub GetRecentAcqui {
2095 my $dbh = C4::Context->dbh;
2099 ORDER BY timestamp DESC
2102 my $sth = $dbh->prepare($query);
2104 my $results = $sth->fetchall_arrayref({});
2110 $contractlist = &GetContracts($booksellerid, $activeonly);
2112 Looks up the contracts that belong to a bookseller
2114 Returns a list of contracts
2118 =item C<$booksellerid> is the "id" field in the "aqbooksellers" table.
2120 =item C<$activeonly> if exists get only contracts that are still active.
2127 my ( $booksellerid, $activeonly ) = @_;
2128 my $dbh = C4::Context->dbh;
2130 if (! $activeonly) {
2134 WHERE booksellerid=?
2139 WHERE booksellerid=?
2140 AND contractenddate >= CURDATE( )";
2142 my $sth = $dbh->prepare($query);
2143 $sth->execute( $booksellerid );
2145 while (my $data = $sth->fetchrow_hashref ) {
2146 push(@results, $data);
2152 #------------------------------------------------------------#
2156 $contract = &GetContract($contractID);
2158 Looks up the contract that has PRIMKEY (contractnumber) value $contractID
2165 my ( $contractno ) = @_;
2166 my $dbh = C4::Context->dbh;
2170 WHERE contractnumber=?
2173 my $sth = $dbh->prepare($query);
2174 $sth->execute( $contractno );
2175 my $result = $sth->fetchrow_hashref;
2183 &AddClaim($ordernumber);
2185 Add a claim for an order
2191 my ($ordernumber) = @_;
2192 my $dbh = C4::Context->dbh;
2195 claims_count = claims_count + 1,
2196 claimed_date = CURDATE()
2197 WHERE ordernumber = ?
2199 my $sth = $dbh->prepare($query);
2200 $sth->execute($ordernumber);
2205 my @invoices = GetInvoices(
2206 invoicenumber => $invoicenumber,
2207 suppliername => $suppliername,
2208 shipmentdatefrom => $shipmentdatefrom, # ISO format
2209 shipmentdateto => $shipmentdateto, # ISO format
2210 billingdatefrom => $billingdatefrom, # ISO format
2211 billingdateto => $billingdateto, # ISO format
2212 isbneanissn => $isbn_or_ean_or_issn,
2215 publisher => $publisher,
2216 publicationyear => $publicationyear,
2217 branchcode => $branchcode,
2218 order_by => $order_by
2221 Return a list of invoices that match all given criteria.
2223 $order_by is "column_name (asc|desc)", where column_name is any of
2224 'invoicenumber', 'booksellerid', 'shipmentdate', 'billingdate', 'closedate',
2225 'shipmentcost', 'shipmentcost_budgetid'.
2227 asc is the default if omitted
2234 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2235 closedate shipmentcost shipmentcost_budgetid);
2237 my $dbh = C4::Context->dbh;
2239 SELECT aqinvoices.*, aqbooksellers.name AS suppliername,
2242 aqorders.datereceived IS NOT NULL,
2243 aqorders.biblionumber,
2246 ) AS receivedbiblios,
2247 SUM(aqorders.quantityreceived) AS receiveditems
2249 LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid
2250 LEFT JOIN aqorders ON aqorders.invoiceid = aqinvoices.invoiceid
2251 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2252 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
2253 LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber
2258 if($args{supplierid}) {
2259 push @bind_strs, " aqinvoices.booksellerid = ? ";
2260 push @bind_args, $args{supplierid};
2262 if($args{invoicenumber}) {
2263 push @bind_strs, " aqinvoices.invoicenumber LIKE ? ";
2264 push @bind_args, "%$args{invoicenumber}%";
2266 if($args{suppliername}) {
2267 push @bind_strs, " aqbooksellers.name LIKE ? ";
2268 push @bind_args, "%$args{suppliername}%";
2270 if($args{shipmentdatefrom}) {
2271 push @bind_strs, " aqinvoices.shipementdate >= ? ";
2272 push @bind_args, $args{shipmentdatefrom};
2274 if($args{shipmentdateto}) {
2275 push @bind_strs, " aqinvoices.shipementdate <= ? ";
2276 push @bind_args, $args{shipmentdateto};
2278 if($args{billingdatefrom}) {
2279 push @bind_strs, " aqinvoices.billingdate >= ? ";
2280 push @bind_args, $args{billingdatefrom};
2282 if($args{billingdateto}) {
2283 push @bind_strs, " aqinvoices.billingdate <= ? ";
2284 push @bind_args, $args{billingdateto};
2286 if($args{isbneanissn}) {
2287 push @bind_strs, " (biblioitems.isbn LIKE ? OR biblioitems.ean LIKE ? OR biblioitems.issn LIKE ? ) ";
2288 push @bind_args, $args{isbneanissn}, $args{isbneanissn}, $args{isbneanissn};
2291 push @bind_strs, " biblio.title LIKE ? ";
2292 push @bind_args, $args{title};
2295 push @bind_strs, " biblio.author LIKE ? ";
2296 push @bind_args, $args{author};
2298 if($args{publisher}) {
2299 push @bind_strs, " biblioitems.publishercode LIKE ? ";
2300 push @bind_args, $args{publisher};
2302 if($args{publicationyear}) {
2303 push @bind_strs, " biblioitems.publicationyear = ? ";
2304 push @bind_args, $args{publicationyear};
2306 if($args{branchcode}) {
2307 push @bind_strs, " aqorders.branchcode = ? ";
2308 push @bind_args, $args{branchcode};
2311 $query .= " WHERE " . join(" AND ", @bind_strs) if @bind_strs;
2312 $query .= " GROUP BY aqinvoices.invoiceid ";
2314 if($args{order_by}) {
2315 my ($column, $direction) = split / /, $args{order_by};
2316 if(grep /^$column$/, @columns) {
2317 $direction ||= 'ASC';
2318 $query .= " ORDER BY $column $direction";
2322 my $sth = $dbh->prepare($query);
2323 $sth->execute(@bind_args);
2325 my $results = $sth->fetchall_arrayref({});
2331 my $invoice = GetInvoice($invoiceid);
2333 Get informations about invoice with given $invoiceid
2335 Return a hash filled with aqinvoices.* fields
2340 my ($invoiceid) = @_;
2343 return unless $invoiceid;
2345 my $dbh = C4::Context->dbh;
2351 my $sth = $dbh->prepare($query);
2352 $sth->execute($invoiceid);
2354 $invoice = $sth->fetchrow_hashref;
2358 =head3 GetInvoiceDetails
2360 my $invoice = GetInvoiceDetails($invoiceid)
2362 Return informations about an invoice + the list of related order lines
2364 Orders informations are in $invoice->{orders} (array ref)
2368 sub GetInvoiceDetails {
2369 my ($invoiceid) = @_;
2371 if ( !defined $invoiceid ) {
2372 carp 'GetInvoiceDetails called without an invoiceid';
2376 my $dbh = C4::Context->dbh;
2378 SELECT aqinvoices.*, aqbooksellers.name AS suppliername
2380 LEFT JOIN aqbooksellers ON aqinvoices.booksellerid = aqbooksellers.id
2383 my $sth = $dbh->prepare($query);
2384 $sth->execute($invoiceid);
2386 my $invoice = $sth->fetchrow_hashref;
2389 SELECT aqorders.*, biblio.*,
2392 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
2393 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2396 $sth = $dbh->prepare($query);
2397 $sth->execute($invoiceid);
2398 $invoice->{orders} = $sth->fetchall_arrayref({});
2399 $invoice->{orders} ||= []; # force an empty arrayref if fetchall_arrayref fails
2406 my $invoiceid = AddInvoice(
2407 invoicenumber => $invoicenumber,
2408 booksellerid => $booksellerid,
2409 shipmentdate => $shipmentdate,
2410 billingdate => $billingdate,
2411 closedate => $closedate,
2412 shipmentcost => $shipmentcost,
2413 shipmentcost_budgetid => $shipmentcost_budgetid
2416 Create a new invoice and return its id or undef if it fails.
2423 return unless(%invoice and $invoice{invoicenumber});
2425 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2426 closedate shipmentcost shipmentcost_budgetid);
2430 foreach my $key (keys %invoice) {
2431 if(0 < grep(/^$key$/, @columns)) {
2432 push @set_strs, "$key = ?";
2433 push @set_args, ($invoice{$key} || undef);
2439 my $dbh = C4::Context->dbh;
2440 my $query = "INSERT INTO aqinvoices SET ";
2441 $query .= join (",", @set_strs);
2442 my $sth = $dbh->prepare($query);
2443 $rv = $sth->execute(@set_args);
2445 $rv = $dbh->last_insert_id(undef, undef, 'aqinvoices', undef);
2454 invoiceid => $invoiceid, # Mandatory
2455 invoicenumber => $invoicenumber,
2456 booksellerid => $booksellerid,
2457 shipmentdate => $shipmentdate,
2458 billingdate => $billingdate,
2459 closedate => $closedate,
2460 shipmentcost => $shipmentcost,
2461 shipmentcost_budgetid => $shipmentcost_budgetid
2464 Modify an invoice, invoiceid is mandatory.
2466 Return undef if it fails.
2473 return unless(%invoice and $invoice{invoiceid});
2475 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2476 closedate shipmentcost shipmentcost_budgetid);
2480 foreach my $key (keys %invoice) {
2481 if(0 < grep(/^$key$/, @columns)) {
2482 push @set_strs, "$key = ?";
2483 push @set_args, ($invoice{$key} || undef);
2487 my $dbh = C4::Context->dbh;
2488 my $query = "UPDATE aqinvoices SET ";
2489 $query .= join(",", @set_strs);
2490 $query .= " WHERE invoiceid = ?";
2492 my $sth = $dbh->prepare($query);
2493 $sth->execute(@set_args, $invoice{invoiceid});
2498 CloseInvoice($invoiceid);
2502 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => undef);
2507 my ($invoiceid) = @_;
2509 return unless $invoiceid;
2511 my $dbh = C4::Context->dbh;
2514 SET closedate = CAST(NOW() AS DATE)
2517 my $sth = $dbh->prepare($query);
2518 $sth->execute($invoiceid);
2521 =head3 ReopenInvoice
2523 ReopenInvoice($invoiceid);
2527 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => C4::Dates->new()->output('iso'))
2532 my ($invoiceid) = @_;
2534 return unless $invoiceid;
2536 my $dbh = C4::Context->dbh;
2539 SET closedate = NULL
2542 my $sth = $dbh->prepare($query);
2543 $sth->execute($invoiceid);
2548 DelInvoice($invoiceid);
2550 Delete an invoice if there are no items attached to it.
2555 my ($invoiceid) = @_;
2557 return unless $invoiceid;
2559 my $dbh = C4::Context->dbh;
2565 my $sth = $dbh->prepare($query);
2566 $sth->execute($invoiceid);
2567 my $res = $sth->fetchrow_arrayref;
2568 if ( $res && $res->[0] == 0 ) {
2570 DELETE FROM aqinvoices
2573 my $sth = $dbh->prepare($query);
2574 return ( $sth->execute($invoiceid) > 0 );
2579 =head3 MergeInvoices
2581 MergeInvoices($invoiceid, \@sourceids);
2583 Merge the invoices identified by the IDs in \@sourceids into
2584 the invoice identified by $invoiceid.
2589 my ($invoiceid, $sourceids) = @_;
2591 return unless $invoiceid;
2592 foreach my $sourceid (@$sourceids) {
2593 next if $sourceid == $invoiceid;
2594 my $source = GetInvoiceDetails($sourceid);
2595 foreach my $order (@{$source->{'orders'}}) {
2596 $order->{'invoiceid'} = $invoiceid;
2599 DelInvoice($source->{'invoiceid'});
2609 Koha Development Team <http://koha-community.org/>