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
76 &GetItemnumbersFromOrder
86 sub GetOrderFromItemnumber {
87 my ($itemnumber) = @_;
88 my $dbh = C4::Context->dbh;
91 SELECT * from aqorders LEFT JOIN aqorders_items
92 ON ( aqorders.ordernumber = aqorders_items.ordernumber )
93 WHERE itemnumber = ? |;
95 my $sth = $dbh->prepare($query);
99 $sth->execute($itemnumber);
101 my $order = $sth->fetchrow_hashref;
106 # Returns the itemnumber(s) associated with the ordernumber given in parameter
107 sub GetItemnumbersFromOrder {
108 my ($ordernumber) = @_;
109 my $dbh = C4::Context->dbh;
110 my $query = "SELECT itemnumber FROM aqorders_items WHERE ordernumber=?";
111 my $sth = $dbh->prepare($query);
112 $sth->execute($ordernumber);
115 while (my $order = $sth->fetchrow_hashref) {
116 push @tab, $order->{'itemnumber'};
130 C4::Acquisition - Koha functions for dealing with orders and acquisitions
138 The functions in this module deal with acquisitions, managing book
139 orders, basket and parcels.
143 =head2 FUNCTIONS ABOUT BASKETS
147 $aqbasket = &GetBasket($basketnumber);
149 get all basket informations in aqbasket for a given basket
151 B<returns:> informations for a given basket returned as a hashref.
157 my $dbh = C4::Context->dbh;
160 concat( b.firstname,' ',b.surname) AS authorisedbyname,
161 b.branchcode AS branch
163 LEFT JOIN borrowers b ON aqbasket.authorisedby=b.borrowernumber
166 my $sth=$dbh->prepare($query);
167 $sth->execute($basketno);
168 my $basket = $sth->fetchrow_hashref;
172 #------------------------------------------------------------#
176 $basket = &NewBasket( $booksellerid, $authorizedby, $basketname,
177 $basketnote, $basketbooksellernote, $basketcontractnumber, $deliveryplace, $billingplace );
179 Create a new basket in aqbasket table
183 =item C<$booksellerid> is a foreign key in the aqbasket table
185 =item C<$authorizedby> is the username of who created the basket
189 The other parameters are optional, see ModBasketHeader for more info on them.
194 my ( $booksellerid, $authorisedby, $basketname, $basketnote,
195 $basketbooksellernote, $basketcontractnumber, $deliveryplace,
196 $billingplace ) = @_;
197 my $dbh = C4::Context->dbh;
199 'INSERT INTO aqbasket (creationdate,booksellerid,authorisedby) '
200 . 'VALUES (now(),?,?)';
201 $dbh->do( $query, {}, $booksellerid, $authorisedby );
203 my $basket = $dbh->{mysql_insertid};
204 $basketname ||= q{}; # default to empty strings
206 $basketbooksellernote ||= q{};
207 ModBasketHeader( $basket, $basketname, $basketnote, $basketbooksellernote,
208 $basketcontractnumber, $booksellerid, $deliveryplace, $billingplace );
212 #------------------------------------------------------------#
216 &CloseBasket($basketno);
218 close a basket (becomes unmodifiable,except for recieves)
224 my $dbh = C4::Context->dbh;
230 my $sth = $dbh->prepare($query);
231 $sth->execute($basketno);
234 #------------------------------------------------------------#
236 =head3 GetBasketAsCSV
238 &GetBasketAsCSV($basketno);
240 Export a basket as CSV
242 $cgi parameter is needed for column name translation
247 my ($basketno, $cgi) = @_;
248 my $basket = GetBasket($basketno);
249 my @orders = GetOrders($basketno);
250 my $contract = GetContract($basket->{'contractnumber'});
252 my $template = C4::Templates::gettemplate("acqui/csv/basket.tmpl", "intranet", $cgi);
255 foreach my $order (@orders) {
256 my $bd = GetBiblioData( $order->{'biblionumber'} );
258 contractname => $contract->{'contractname'},
259 ordernumber => $order->{'ordernumber'},
260 entrydate => $order->{'entrydate'},
261 isbn => $order->{'isbn'},
262 author => $bd->{'author'},
263 title => $bd->{'title'},
264 publicationyear => $bd->{'publicationyear'},
265 publishercode => $bd->{'publishercode'},
266 collectiontitle => $bd->{'collectiontitle'},
267 notes => $order->{'notes'},
268 quantity => $order->{'quantity'},
269 rrp => $order->{'rrp'},
270 deliveryplace => C4::Branch::GetBranchName( $basket->{'deliveryplace'} ),
271 billingplace => C4::Branch::GetBranchName( $basket->{'billingplace'} ),
274 contractname author title publishercode collectiontitle notes
275 deliveryplace billingplace
277 # Double the quotes to not be interpreted as a field end
278 $row->{$_} =~ s/"/""/g if $row->{$_};
284 if(defined $a->{publishercode} and defined $b->{publishercode}) {
285 $a->{publishercode} cmp $b->{publishercode};
289 $template->param(rows => \@rows);
291 return $template->output;
295 =head3 GetBasketGroupAsCSV
299 &GetBasketGroupAsCSV($basketgroupid);
301 Export a basket group as CSV
303 $cgi parameter is needed for column name translation
309 sub GetBasketGroupAsCSV {
310 my ($basketgroupid, $cgi) = @_;
311 my $baskets = GetBasketsByBasketgroup($basketgroupid);
313 my $template = C4::Templates::gettemplate('acqui/csv/basketgroup.tmpl', 'intranet', $cgi);
316 for my $basket (@$baskets) {
317 my @orders = GetOrders( $$basket{basketno} );
318 my $contract = GetContract( $$basket{contractnumber} );
319 my $bookseller = GetBookSellerFromId( $$basket{booksellerid} );
320 my $basketgroup = GetBasketgroup( $$basket{basketgroupid} );
322 foreach my $order (@orders) {
323 my $bd = GetBiblioData( $order->{'biblionumber'} );
325 clientnumber => $bookseller->{accountnumber},
326 basketname => $basket->{basketname},
327 ordernumber => $order->{ordernumber},
328 author => $bd->{author},
329 title => $bd->{title},
330 publishercode => $bd->{publishercode},
331 publicationyear => $bd->{publicationyear},
332 collectiontitle => $bd->{collectiontitle},
333 isbn => $order->{isbn},
334 quantity => $order->{quantity},
335 rrp => $order->{rrp},
336 discount => $bookseller->{discount},
337 ecost => $order->{ecost},
338 notes => $order->{notes},
339 entrydate => $order->{entrydate},
340 booksellername => $bookseller->{name},
341 bookselleraddress => $bookseller->{address1},
342 booksellerpostal => $bookseller->{postal},
343 contractnumber => $contract->{contractnumber},
344 contractname => $contract->{contractname},
345 basketgroupdeliveryplace => C4::Branch::GetBranchName( $basketgroup->{deliveryplace} ),
346 basketgroupbillingplace => C4::Branch::GetBranchName( $basketgroup->{billingplace} ),
347 basketdeliveryplace => C4::Branch::GetBranchName( $basket->{deliveryplace} ),
348 basketbillingplace => C4::Branch::GetBranchName( $basket->{billingplace} ),
351 basketname author title publishercode collectiontitle notes
352 booksellername bookselleraddress booksellerpostal contractname
353 basketgroupdeliveryplace basketgroupbillingplace
354 basketdeliveryplace basketbillingplace
356 # Double the quotes to not be interpreted as a field end
357 $row->{$_} =~ s/"/""/g if $row->{$_};
362 $template->param(rows => \@rows);
364 return $template->output;
368 =head3 CloseBasketgroup
370 &CloseBasketgroup($basketgroupno);
376 sub CloseBasketgroup {
377 my ($basketgroupno) = @_;
378 my $dbh = C4::Context->dbh;
379 my $sth = $dbh->prepare("
380 UPDATE aqbasketgroups
384 $sth->execute($basketgroupno);
387 #------------------------------------------------------------#
389 =head3 ReOpenBaskergroup($basketgroupno)
391 &ReOpenBaskergroup($basketgroupno);
397 sub ReOpenBasketgroup {
398 my ($basketgroupno) = @_;
399 my $dbh = C4::Context->dbh;
400 my $sth = $dbh->prepare("
401 UPDATE aqbasketgroups
405 $sth->execute($basketgroupno);
408 #------------------------------------------------------------#
413 &DelBasket($basketno);
415 Deletes the basket that has basketno field $basketno in the aqbasket table.
419 =item C<$basketno> is the primary key of the basket in the aqbasket table.
426 my ( $basketno ) = @_;
427 my $query = "DELETE FROM aqbasket WHERE basketno=?";
428 my $dbh = C4::Context->dbh;
429 my $sth = $dbh->prepare($query);
430 $sth->execute($basketno);
434 #------------------------------------------------------------#
438 &ModBasket($basketinfo);
440 Modifies a basket, using a hashref $basketinfo for the relevant information, only $basketinfo->{'basketno'} is required.
444 =item C<$basketno> is the primary key of the basket in the aqbasket table.
451 my $basketinfo = shift;
452 my $query = "UPDATE aqbasket SET ";
454 foreach my $key (keys %$basketinfo){
455 if ($key ne 'basketno'){
456 $query .= "$key=?, ";
457 push(@params, $basketinfo->{$key} || undef );
460 # get rid of the "," at the end of $query
461 if (substr($query, length($query)-2) eq ', '){
466 $query .= "WHERE basketno=?";
467 push(@params, $basketinfo->{'basketno'});
468 my $dbh = C4::Context->dbh;
469 my $sth = $dbh->prepare($query);
470 $sth->execute(@params);
474 #------------------------------------------------------------#
476 =head3 ModBasketHeader
478 &ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid);
480 Modifies a basket's header.
484 =item C<$basketno> is the "basketno" field in the "aqbasket" table;
486 =item C<$basketname> is the "basketname" field in the "aqbasket" table;
488 =item C<$note> is the "note" field in the "aqbasket" table;
490 =item C<$booksellernote> is the "booksellernote" field in the "aqbasket" table;
492 =item C<$contractnumber> is the "contractnumber" (foreign) key in the "aqbasket" table.
494 =item C<$booksellerid> is the id (foreign) key in the "aqbooksellers" table for the vendor.
496 =item C<$deliveryplace> is the "deliveryplace" field in the aqbasket table.
498 =item C<$billingplace> is the "billingplace" field in the aqbasket table.
504 sub ModBasketHeader {
505 my ($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid, $deliveryplace, $billingplace) = @_;
508 SET basketname=?, note=?, booksellernote=?, booksellerid=?, deliveryplace=?, billingplace=?
512 my $dbh = C4::Context->dbh;
513 my $sth = $dbh->prepare($query);
514 $sth->execute($basketname, $note, $booksellernote, $booksellerid, $deliveryplace, $billingplace, $basketno);
516 if ( $contractnumber ) {
517 my $query2 ="UPDATE aqbasket SET contractnumber=? WHERE basketno=?";
518 my $sth2 = $dbh->prepare($query2);
519 $sth2->execute($contractnumber,$basketno);
525 #------------------------------------------------------------#
527 =head3 GetBasketsByBookseller
529 @results = &GetBasketsByBookseller($booksellerid, $extra);
531 Returns a list of hashes of all the baskets that belong to bookseller 'booksellerid'.
535 =item C<$booksellerid> is the 'id' field of the bookseller in the aqbooksellers table
537 =item C<$extra> is the extra sql parameters, can be
539 $extra->{groupby}: group baskets by column
540 ex. $extra->{groupby} = aqbasket.basketgroupid
541 $extra->{orderby}: order baskets by column
542 $extra->{limit}: limit number of results (can be helpful for pagination)
548 sub GetBasketsByBookseller {
549 my ($booksellerid, $extra) = @_;
550 my $query = "SELECT * FROM aqbasket WHERE booksellerid=?";
552 if ($extra->{groupby}) {
553 $query .= " GROUP by $extra->{groupby}";
555 if ($extra->{orderby}){
556 $query .= " ORDER by $extra->{orderby}";
558 if ($extra->{limit}){
559 $query .= " LIMIT $extra->{limit}";
562 my $dbh = C4::Context->dbh;
563 my $sth = $dbh->prepare($query);
564 $sth->execute($booksellerid);
565 my $results = $sth->fetchall_arrayref({});
570 =head3 GetBasketsInfosByBookseller
572 my $baskets = GetBasketsInfosByBookseller($supplierid, $allbaskets);
574 The optional second parameter allbaskets is a boolean allowing you to
575 select all baskets from the supplier; by default only active baskets (open or
576 closed but still something to receive) are returned.
578 Returns in a arrayref of hashref all about booksellers baskets, plus:
579 total_biblios: Number of distinct biblios in basket
580 total_items: Number of items in basket
581 expected_items: Number of non-received items in basket
585 sub GetBasketsInfosByBookseller {
586 my ($supplierid, $allbaskets) = @_;
588 return unless $supplierid;
590 my $dbh = C4::Context->dbh;
593 SUM(aqorders.quantity) AS total_items,
594 COUNT(DISTINCT aqorders.biblionumber) AS total_biblios,
596 IF(aqorders.datereceived IS NULL
597 AND aqorders.datecancellationprinted IS NULL
602 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
603 WHERE booksellerid = ?};
605 $query.=" AND (closedate IS NULL OR (aqorders.quantity > aqorders.quantityreceived AND datecancellationprinted IS NULL))";
607 $query.=" GROUP BY aqbasket.basketno";
609 my $sth = $dbh->prepare($query);
610 $sth->execute($supplierid);
611 return $sth->fetchall_arrayref({});
615 #------------------------------------------------------------#
617 =head3 GetBasketsByBasketgroup
619 $baskets = &GetBasketsByBasketgroup($basketgroupid);
621 Returns a reference to all baskets that belong to basketgroup $basketgroupid.
625 sub GetBasketsByBasketgroup {
626 my $basketgroupid = shift;
628 SELECT *, aqbasket.booksellerid as booksellerid
630 LEFT JOIN aqcontract USING(contractnumber) WHERE basketgroupid=?
632 my $dbh = C4::Context->dbh;
633 my $sth = $dbh->prepare($query);
634 $sth->execute($basketgroupid);
635 my $results = $sth->fetchall_arrayref({});
640 #------------------------------------------------------------#
642 =head3 NewBasketgroup
644 $basketgroupid = NewBasketgroup(\%hashref);
646 Adds a basketgroup to the aqbasketgroups table, and add the initial baskets to it.
648 $hashref->{'booksellerid'} is the 'id' field of the bookseller in the aqbooksellers table,
650 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
652 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
654 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
656 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
658 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
660 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
662 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
667 my $basketgroupinfo = shift;
668 die "booksellerid is required to create a basketgroup" unless $basketgroupinfo->{'booksellerid'};
669 my $query = "INSERT INTO aqbasketgroups (";
671 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
672 if ( defined $basketgroupinfo->{$field} ) {
673 $query .= "$field, ";
674 push(@params, $basketgroupinfo->{$field});
677 $query .= "booksellerid) VALUES (";
682 push(@params, $basketgroupinfo->{'booksellerid'});
683 my $dbh = C4::Context->dbh;
684 my $sth = $dbh->prepare($query);
685 $sth->execute(@params);
686 my $basketgroupid = $dbh->{'mysql_insertid'};
687 if( $basketgroupinfo->{'basketlist'} ) {
688 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
689 my $query2 = "UPDATE aqbasket SET basketgroupid=? WHERE basketno=?";
690 my $sth2 = $dbh->prepare($query2);
691 $sth2->execute($basketgroupid, $basketno);
694 return $basketgroupid;
697 #------------------------------------------------------------#
699 =head3 ModBasketgroup
701 ModBasketgroup(\%hashref);
703 Modifies a basketgroup in the aqbasketgroups table, and add the baskets to it.
705 $hashref->{'id'} is the 'id' field of the basketgroup in the aqbasketgroup table, this parameter is mandatory,
707 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
709 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
711 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
713 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
715 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
717 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
719 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
724 my $basketgroupinfo = shift;
725 die "basketgroup id is required to edit a basketgroup" unless $basketgroupinfo->{'id'};
726 my $dbh = C4::Context->dbh;
727 my $query = "UPDATE aqbasketgroups SET ";
729 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
730 if ( defined $basketgroupinfo->{$field} ) {
731 $query .= "$field=?, ";
732 push(@params, $basketgroupinfo->{$field});
737 $query .= " WHERE id=?";
738 push(@params, $basketgroupinfo->{'id'});
739 my $sth = $dbh->prepare($query);
740 $sth->execute(@params);
742 $sth = $dbh->prepare('UPDATE aqbasket SET basketgroupid = NULL WHERE basketgroupid = ?');
743 $sth->execute($basketgroupinfo->{'id'});
745 if($basketgroupinfo->{'basketlist'} && @{$basketgroupinfo->{'basketlist'}}){
746 $sth = $dbh->prepare("UPDATE aqbasket SET basketgroupid=? WHERE basketno=?");
747 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
748 $sth->execute($basketgroupinfo->{'id'}, $basketno);
755 #------------------------------------------------------------#
757 =head3 DelBasketgroup
759 DelBasketgroup($basketgroupid);
761 Deletes a basketgroup in the aqbasketgroups table, and removes the reference to it from the baskets,
765 =item C<$basketgroupid> is the 'id' field of the basket in the aqbasketgroup table
772 my $basketgroupid = shift;
773 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
774 my $query = "DELETE FROM aqbasketgroups WHERE id=?";
775 my $dbh = C4::Context->dbh;
776 my $sth = $dbh->prepare($query);
777 $sth->execute($basketgroupid);
781 #------------------------------------------------------------#
784 =head2 FUNCTIONS ABOUT ORDERS
786 =head3 GetBasketgroup
788 $basketgroup = &GetBasketgroup($basketgroupid);
790 Returns a reference to the hash containing all infermation about the basketgroup.
795 my $basketgroupid = shift;
796 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
797 my $query = "SELECT * FROM aqbasketgroups WHERE id=?";
798 my $dbh = C4::Context->dbh;
799 my $sth = $dbh->prepare($query);
800 $sth->execute($basketgroupid);
801 my $result = $sth->fetchrow_hashref;
806 #------------------------------------------------------------#
808 =head3 GetBasketgroups
810 $basketgroups = &GetBasketgroups($booksellerid);
812 Returns a reference to the array of all the basketgroups of bookseller $booksellerid.
816 sub GetBasketgroups {
817 my $booksellerid = shift;
818 die 'bookseller id is required to edit a basketgroup' unless $booksellerid;
819 my $query = 'SELECT * FROM aqbasketgroups WHERE booksellerid=? ORDER BY id DESC';
820 my $dbh = C4::Context->dbh;
821 my $sth = $dbh->prepare($query);
822 $sth->execute($booksellerid);
823 return $sth->fetchall_arrayref({});
826 #------------------------------------------------------------#
828 =head2 FUNCTIONS ABOUT ORDERS
832 @orders = &GetOrders($basketnumber, $orderby);
834 Looks up the pending (non-cancelled) orders with the given basket
835 number. If C<$booksellerID> is non-empty, only orders from that seller
839 C<&basket> returns a two-element array. C<@orders> is an array of
840 references-to-hash, whose keys are the fields from the aqorders,
841 biblio, and biblioitems tables in the Koha database.
846 my ( $basketno, $orderby ) = @_;
847 my $dbh = C4::Context->dbh;
849 SELECT biblio.*,biblioitems.*,
853 aqorders_transfers.ordernumber_from AS transferred_from,
854 aqorders_transfers.timestamp AS transferred_from_timestamp
856 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
857 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
858 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
859 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
861 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
864 $orderby = "biblioitems.publishercode,biblio.title" unless $orderby;
865 $query .= " ORDER BY $orderby";
866 my $sth = $dbh->prepare($query);
867 $sth->execute($basketno);
868 my $results = $sth->fetchall_arrayref({});
873 #------------------------------------------------------------#
874 =head3 GetOrdersByBiblionumber
876 @orders = &GetOrdersByBiblionumber($biblionumber);
878 Looks up the orders with linked to a specific $biblionumber, including
879 cancelled orders and received orders.
882 C<@orders> is an array of references-to-hash, whose keys are the
883 fields from the aqorders, biblio, and biblioitems tables in the Koha database.
887 sub GetOrdersByBiblionumber {
888 my $biblionumber = shift;
889 return unless $biblionumber;
890 my $dbh = C4::Context->dbh;
892 SELECT biblio.*,biblioitems.*,
896 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
897 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
898 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
899 WHERE aqorders.biblionumber=?
901 my $sth = $dbh->prepare($query);
902 $sth->execute($biblionumber);
903 my $results = $sth->fetchall_arrayref({});
908 #------------------------------------------------------------#
912 $order = &GetOrder($ordernumber);
914 Looks up an order by order number.
916 Returns a reference-to-hash describing the order. The keys of
917 C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database.
922 my ($ordernumber) = @_;
923 my $dbh = C4::Context->dbh;
925 SELECT biblioitems.*, biblio.*, aqorders.*
927 LEFT JOIN biblio on biblio.biblionumber=aqorders.biblionumber
928 LEFT JOIN biblioitems on biblioitems.biblionumber=aqorders.biblionumber
929 WHERE aqorders.ordernumber=?
932 my $sth= $dbh->prepare($query);
933 $sth->execute($ordernumber);
934 my $data = $sth->fetchrow_hashref;
939 =head3 GetLastOrderNotReceivedFromSubscriptionid
941 $order = &GetLastOrderNotReceivedFromSubscriptionid($subscriptionid);
943 Returns a reference-to-hash describing the last order not received for a subscription.
947 sub GetLastOrderNotReceivedFromSubscriptionid {
948 my ( $subscriptionid ) = @_;
949 my $dbh = C4::Context->dbh;
951 SELECT * FROM aqorders
952 LEFT JOIN subscription
953 ON ( aqorders.subscriptionid = subscription.subscriptionid )
954 WHERE aqorders.subscriptionid = ?
955 AND aqorders.datereceived IS NULL
958 my $sth = $dbh->prepare( $query );
959 $sth->execute( $subscriptionid );
960 my $order = $sth->fetchrow_hashref;
964 =head3 GetLastOrderReceivedFromSubscriptionid
966 $order = &GetLastOrderReceivedFromSubscriptionid($subscriptionid);
968 Returns a reference-to-hash describing the last order received for a subscription.
972 sub GetLastOrderReceivedFromSubscriptionid {
973 my ( $subscriptionid ) = @_;
974 my $dbh = C4::Context->dbh;
976 SELECT * FROM aqorders
977 LEFT JOIN subscription
978 ON ( aqorders.subscriptionid = subscription.subscriptionid )
979 WHERE aqorders.subscriptionid = ?
980 AND aqorders.datereceived =
982 SELECT MAX( aqorders.datereceived )
984 LEFT JOIN subscription
985 ON ( aqorders.subscriptionid = subscription.subscriptionid )
986 WHERE aqorders.subscriptionid = ?
987 AND aqorders.datereceived IS NOT NULL
989 ORDER BY ordernumber DESC
992 my $sth = $dbh->prepare( $query );
993 $sth->execute( $subscriptionid, $subscriptionid );
994 my $order = $sth->fetchrow_hashref;
1000 #------------------------------------------------------------#
1004 &NewOrder(\%hashref);
1006 Adds a new order to the database. Any argument that isn't described
1007 below is the new value of the field with the same name in the aqorders
1008 table of the Koha database.
1012 =item $hashref->{'basketno'} is the basketno foreign key in aqorders, it is mandatory
1014 =item $hashref->{'ordernumber'} is a "minimum order number."
1016 =item $hashref->{'budgetdate'} is effectively ignored.
1017 If it's undef (anything false) or the string 'now', the current day is used.
1018 Else, the upcoming July 1st is used.
1020 =item $hashref->{'subscription'} may be either "yes", or anything else for "no".
1022 =item $hashref->{'uncertainprice'} may be 0 for "the price is known" or 1 for "the price is uncertain"
1024 =item defaults entrydate to Now
1026 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".
1033 my $orderinfo = shift;
1034 #### ------------------------------
1035 my $dbh = C4::Context->dbh;
1039 # if these parameters are missing, we can't continue
1040 for my $key (qw/basketno quantity biblionumber budget_id/) {
1041 croak "Mandatory parameter $key missing" unless $orderinfo->{$key};
1044 if ( defined $orderinfo->{subscription} && $orderinfo->{'subscription'} eq 'yes' ) {
1045 $orderinfo->{'subscription'} = 1;
1047 $orderinfo->{'subscription'} = 0;
1049 $orderinfo->{'entrydate'} ||= C4::Dates->new()->output("iso");
1050 if (!$orderinfo->{quantityreceived}) {
1051 $orderinfo->{quantityreceived} = 0;
1054 my $ordernumber=InsertInTable("aqorders",$orderinfo);
1055 if (not $orderinfo->{parent_ordernumber}) {
1056 my $sth = $dbh->prepare("
1058 SET parent_ordernumber = ordernumber
1059 WHERE ordernumber = ?
1061 $sth->execute($ordernumber);
1063 return ( $orderinfo->{'basketno'}, $ordernumber );
1068 #------------------------------------------------------------#
1077 my ($itemnumber, $ordernumber) = @_;
1078 my $dbh = C4::Context->dbh;
1080 INSERT INTO aqorders_items
1081 (itemnumber, ordernumber)
1084 my $sth = $dbh->prepare($query);
1085 $sth->execute( $itemnumber, $ordernumber);
1088 #------------------------------------------------------------#
1092 &ModOrder(\%hashref);
1094 Modifies an existing order. Updates the order with order number
1095 $hashref->{'ordernumber'} and biblionumber $hashref->{'biblionumber'}. All
1096 other keys of the hash update the fields with the same name in the aqorders
1097 table of the Koha database.
1102 my $orderinfo = shift;
1104 die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq '' ;
1105 die "Biblionumber is required" if $orderinfo->{'biblionumber'} eq '';
1107 my $dbh = C4::Context->dbh;
1110 # update uncertainprice to an integer, just in case (under FF, checked boxes have the value "ON" by default)
1111 $orderinfo->{uncertainprice}=1 if $orderinfo->{uncertainprice};
1113 # delete($orderinfo->{'branchcode'});
1114 # the hash contains a lot of entries not in aqorders, so get the columns ...
1115 my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;");
1117 my $colnames = $sth->{NAME};
1118 #FIXME Be careful. If aqorders would have columns with diacritics,
1119 #you should need to decode what you get back from NAME.
1120 #See report 10110 and guided_reports.pl
1121 my $query = "UPDATE aqorders SET ";
1123 foreach my $orderinfokey (grep(!/ordernumber/, keys %$orderinfo)){
1124 # ... and skip hash entries that are not in the aqorders table
1125 # FIXME : probably not the best way to do it (would be better to have a correct hash)
1126 next unless grep(/^$orderinfokey$/, @$colnames);
1127 $query .= "$orderinfokey=?, ";
1128 push(@params, $orderinfo->{$orderinfokey});
1131 $query .= "timestamp=NOW() WHERE ordernumber=?";
1132 # push(@params, $specorderinfo{'ordernumber'});
1133 push(@params, $orderinfo->{'ordernumber'} );
1134 $sth = $dbh->prepare($query);
1135 $sth->execute(@params);
1139 #------------------------------------------------------------#
1143 ModItemOrder($itemnumber, $ordernumber);
1145 Modifies the ordernumber of an item in aqorders_items.
1150 my ($itemnumber, $ordernumber) = @_;
1152 return unless ($itemnumber and $ordernumber);
1154 my $dbh = C4::Context->dbh;
1156 UPDATE aqorders_items
1158 WHERE itemnumber = ?
1160 my $sth = $dbh->prepare($query);
1161 return $sth->execute($ordernumber, $itemnumber);
1164 #------------------------------------------------------------#
1166 =head3 GetCancelledOrders
1168 my @orders = GetCancelledOrders($basketno, $orderby);
1170 Returns cancelled orders for a basket
1174 sub GetCancelledOrders {
1175 my ( $basketno, $orderby ) = @_;
1177 return () unless $basketno;
1179 my $dbh = C4::Context->dbh;
1186 aqorders_transfers.ordernumber_to AS transferred_to,
1187 aqorders_transfers.timestamp AS transferred_to_timestamp
1189 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1190 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1191 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1192 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber
1194 AND (datecancellationprinted IS NOT NULL
1195 AND datecancellationprinted <> '0000-00-00')
1198 $orderby = "aqorders.datecancellationprinted desc, aqorders.timestamp desc"
1200 $query .= " ORDER BY $orderby";
1201 my $sth = $dbh->prepare($query);
1202 $sth->execute($basketno);
1203 my $results = $sth->fetchall_arrayref( {} );
1209 #------------------------------------------------------------#
1211 =head3 ModReceiveOrder
1213 &ModReceiveOrder($biblionumber, $ordernumber, $quantityreceived, $user,
1214 $cost, $ecost, $invoiceid, rrp, budget_id, datereceived, \@received_itemnumbers);
1216 Updates an order, to reflect the fact that it was received, at least
1217 in part. All arguments not mentioned below update the fields with the
1218 same name in the aqorders table of the Koha database.
1220 If a partial order is received, splits the order into two.
1222 Updates the order with bibilionumber C<$biblionumber> and ordernumber
1228 sub ModReceiveOrder {
1230 $biblionumber, $ordernumber, $quantrec, $user, $cost, $ecost,
1231 $invoiceid, $rrp, $budget_id, $datereceived, $received_items
1235 my $dbh = C4::Context->dbh;
1236 $datereceived = C4::Dates->output('iso') unless $datereceived;
1237 my $suggestionid = GetSuggestionFromBiblionumber( $biblionumber );
1238 if ($suggestionid) {
1239 ModSuggestion( {suggestionid=>$suggestionid,
1240 STATUS=>'AVAILABLE',
1241 biblionumber=> $biblionumber}
1245 my $sth=$dbh->prepare("
1246 SELECT * FROM aqorders
1247 WHERE biblionumber=? AND aqorders.ordernumber=?");
1249 $sth->execute($biblionumber,$ordernumber);
1250 my $order = $sth->fetchrow_hashref();
1253 my $new_ordernumber = $ordernumber;
1254 if ( $order->{quantity} > $quantrec ) {
1255 # Split order line in two parts: the first is the original order line
1256 # without received items (the quantity is decreased),
1257 # the second part is a new order line with quantity=quantityrec
1258 # (entirely received)
1259 $sth=$dbh->prepare("
1262 WHERE ordernumber = ?
1265 $sth->execute($order->{quantity} - $quantrec, $ordernumber);
1269 delete $order->{'ordernumber'};
1270 $order->{'quantity'} = $quantrec;
1271 $order->{'quantityreceived'} = $quantrec;
1272 $order->{'datereceived'} = $datereceived;
1273 $order->{'invoiceid'} = $invoiceid;
1274 $order->{'unitprice'} = $cost;
1275 $order->{'rrp'} = $rrp;
1276 $order->{ecost} = $ecost;
1277 $order->{'orderstatus'} = 3; # totally received
1279 ( $basketno, $new_ordernumber ) = NewOrder($order);
1281 if ($received_items) {
1282 foreach my $itemnumber (@$received_items) {
1283 ModItemOrder($itemnumber, $new_ordernumber);
1287 $sth=$dbh->prepare("update aqorders
1288 set quantityreceived=?,datereceived=?,invoiceid=?,
1289 unitprice=?,rrp=?,ecost=?
1290 where biblionumber=? and ordernumber=?");
1291 $sth->execute($quantrec,$datereceived,$invoiceid,$cost,$rrp,$ecost,$biblionumber,$ordernumber);
1294 return ($datereceived, $new_ordernumber);
1297 =head3 CancelReceipt
1299 my $parent_ordernumber = CancelReceipt($ordernumber);
1301 Cancel an order line receipt and update the parent order line, as if no
1303 If items are created at receipt (AcqCreateItem = receiving) then delete
1309 my $ordernumber = shift;
1311 return unless $ordernumber;
1313 my $dbh = C4::Context->dbh;
1315 SELECT datereceived, parent_ordernumber, quantity
1317 WHERE ordernumber = ?
1319 my $sth = $dbh->prepare($query);
1320 $sth->execute($ordernumber);
1321 my $order = $sth->fetchrow_hashref;
1323 warn "CancelReceipt: order $ordernumber does not exist";
1326 unless($order->{'datereceived'}) {
1327 warn "CancelReceipt: order $ordernumber is not received";
1331 my $parent_ordernumber = $order->{'parent_ordernumber'};
1333 if($parent_ordernumber == $ordernumber || not $parent_ordernumber) {
1334 # The order line has no parent, just mark it as not received
1337 SET quantityreceived = ?,
1340 WHERE ordernumber = ?
1342 $sth = $dbh->prepare($query);
1343 $sth->execute(0, undef, undef, $ordernumber);
1345 # The order line has a parent, increase parent quantity and delete
1348 SELECT quantity, datereceived
1350 WHERE ordernumber = ?
1352 $sth = $dbh->prepare($query);
1353 $sth->execute($parent_ordernumber);
1354 my $parent_order = $sth->fetchrow_hashref;
1355 unless($parent_order) {
1356 warn "Parent order $parent_ordernumber does not exist.";
1359 if($parent_order->{'datereceived'}) {
1360 warn "CancelReceipt: parent order is received.".
1361 " Can't cancel receipt.";
1367 WHERE ordernumber = ?
1369 $sth = $dbh->prepare($query);
1370 my $rv = $sth->execute(
1371 $order->{'quantity'} + $parent_order->{'quantity'},
1375 warn "Cannot update parent order line, so do not cancel".
1379 if(C4::Context->preference('AcqCreateItem') eq 'receiving') {
1380 # Remove items that were created at receipt
1382 DELETE FROM items, aqorders_items
1383 USING items, aqorders_items
1384 WHERE items.itemnumber = ? AND aqorders_items.itemnumber = ?
1386 $sth = $dbh->prepare($query);
1387 my @itemnumbers = GetItemnumbersFromOrder($ordernumber);
1388 foreach my $itemnumber (@itemnumbers) {
1389 $sth->execute($itemnumber, $itemnumber);
1393 my @itemnumbers = GetItemnumbersFromOrder($ordernumber);
1394 foreach my $itemnumber (@itemnumbers) {
1395 ModItemOrder($itemnumber, $parent_ordernumber);
1400 DELETE FROM aqorders
1401 WHERE ordernumber = ?
1403 $sth = $dbh->prepare($query);
1404 $sth->execute($ordernumber);
1408 return $parent_ordernumber;
1411 #------------------------------------------------------------#
1415 @results = &SearchOrders({
1416 ordernumber => $ordernumber,
1418 biblionumber => $biblionumber,
1420 booksellerid => $booksellerid,
1421 basketno => $basketno,
1426 Searches for orders.
1428 C<$owner> Finds order for the logged in user.
1429 C<$pending> Finds pending orders. Ignores completed and cancelled orders.
1432 C<@results> is an array of references-to-hash with the keys are fields
1433 from aqorders, biblio, biblioitems and aqbasket tables.
1438 my ( $params ) = @_;
1439 my $ordernumber = $params->{ordernumber};
1440 my $search = $params->{search};
1441 my $ean = $params->{ean};
1442 my $booksellerid = $params->{booksellerid};
1443 my $basketno = $params->{basketno};
1444 my $basketname = $params->{basketname};
1445 my $basketgroupname = $params->{basketgroupname};
1446 my $owner = $params->{owner};
1447 my $pending = $params->{pending};
1449 my $dbh = C4::Context->dbh;
1452 SELECT aqbasket.basketno,
1454 borrowers.firstname,
1457 biblioitems.biblioitemnumber,
1459 aqbasket.creationdate,
1460 aqbasket.basketname,
1463 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
1464 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
1465 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1466 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1467 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
1468 WHERE (datecancellationprinted is NULL)
1472 AND (quantity > quantityreceived OR quantityreceived is NULL)
1475 my $userenv = C4::Context->userenv;
1476 if ( C4::Context->preference("IndependentBranches") ) {
1477 if ( ( $userenv ) and ( $userenv->{flags} != 1 ) ) {
1480 borrowers.branchcode = ?
1481 OR borrowers.branchcode = ''
1484 push @args, $userenv->{branch};
1488 if ( $ordernumber ) {
1489 $query .= ' AND (aqorders.ordernumber=?)';
1490 push @args, $ordernumber;
1493 $query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)';
1494 push @args, ("%$search%","%$search%","%$search%");
1497 $query .= ' AND biblioitems.ean = ?';
1500 if ( $booksellerid ) {
1501 $query .= 'AND aqbasket.booksellerid = ?';
1502 push @args, $booksellerid;
1505 $query .= 'AND aqbasket.basketno = ?';
1506 push @args, $basketno;
1509 $query .= 'AND aqbasket.basketname LIKE ?';
1510 push @args, "%$basketname%";
1512 if( $basketgroupname ) {
1513 $query .= ' AND aqbasketgroups.name LIKE ?';
1514 push @args, "%$basketgroupname%";
1518 $query .= ' AND aqbasket.authorisedby=? ';
1519 push @args, $userenv->{'number'};
1522 $query .= ' ORDER BY aqbasket.basketno';
1524 my $sth = $dbh->prepare($query);
1525 $sth->execute(@args);
1526 return $sth->fetchall_arrayref({});
1529 #------------------------------------------------------------#
1533 &DelOrder($biblionumber, $ordernumber);
1535 Cancel the order with the given order and biblio numbers. It does not
1536 delete any entries in the aqorders table, it merely marks them as
1542 my ( $bibnum, $ordernumber ) = @_;
1543 my $dbh = C4::Context->dbh;
1546 SET datecancellationprinted=now()
1547 WHERE biblionumber=? AND ordernumber=?
1549 my $sth = $dbh->prepare($query);
1550 $sth->execute( $bibnum, $ordernumber );
1552 my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1553 foreach my $itemnumber (@itemnumbers){
1554 C4::Items::DelItem( $dbh, $bibnum, $itemnumber );
1559 =head3 TransferOrder
1561 my $newordernumber = TransferOrder($ordernumber, $basketno);
1563 Transfer an order line to a basket.
1564 Mark $ordernumber as cancelled with an internal note 'Cancelled and transfered
1565 to BOOKSELLER on DATE' and create new order with internal note
1566 'Transfered from BOOKSELLER on DATE'.
1567 Move all attached items to the new order.
1568 Received orders cannot be transfered.
1569 Return the ordernumber of created order.
1574 my ($ordernumber, $basketno) = @_;
1576 return unless ($ordernumber and $basketno);
1578 my $order = GetOrder( $ordernumber );
1579 return if $order->{datereceived};
1580 my $basket = GetBasket($basketno);
1581 return unless $basket;
1583 my $dbh = C4::Context->dbh;
1584 my ($query, $sth, $rv);
1588 SET datecancellationprinted = CAST(NOW() AS date)
1589 WHERE ordernumber = ?
1591 $sth = $dbh->prepare($query);
1592 $rv = $sth->execute($ordernumber);
1594 delete $order->{'ordernumber'};
1595 $order->{'basketno'} = $basketno;
1597 (undef, $newordernumber) = NewOrder($order);
1600 UPDATE aqorders_items
1602 WHERE ordernumber = ?
1604 $sth = $dbh->prepare($query);
1605 $sth->execute($newordernumber, $ordernumber);
1608 INSERT INTO aqorders_transfers (ordernumber_from, ordernumber_to)
1611 $sth = $dbh->prepare($query);
1612 $sth->execute($ordernumber, $newordernumber);
1614 return $newordernumber;
1617 =head2 FUNCTIONS ABOUT PARCELS
1621 #------------------------------------------------------------#
1625 @results = &GetParcel($booksellerid, $code, $date);
1627 Looks up all of the received items from the supplier with the given
1628 bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders.
1630 C<@results> is an array of references-to-hash. The keys of each element are fields from
1631 the aqorders, biblio, and biblioitems tables of the Koha database.
1633 C<@results> is sorted alphabetically by book title.
1638 #gets all orders from a certain supplier, orders them alphabetically
1639 my ( $supplierid, $code, $datereceived ) = @_;
1640 my $dbh = C4::Context->dbh;
1643 if $code; # add % if we search on a given code (otherwise, let him empty)
1645 SELECT authorisedby,
1650 aqorders.biblionumber,
1651 aqorders.ordernumber,
1652 aqorders.parent_ordernumber,
1654 aqorders.quantityreceived,
1662 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
1663 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1664 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1665 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
1667 aqbasket.booksellerid = ?
1668 AND aqinvoices.invoicenumber LIKE ?
1669 AND aqorders.datereceived = ? ";
1671 my @query_params = ( $supplierid, $code, $datereceived );
1672 if ( C4::Context->preference("IndependentBranches") ) {
1673 my $userenv = C4::Context->userenv;
1674 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
1675 $strsth .= " and (borrowers.branchcode = ?
1676 or borrowers.branchcode = '')";
1677 push @query_params, $userenv->{branch};
1680 $strsth .= " ORDER BY aqbasket.basketno";
1681 # ## parcelinformation : $strsth
1682 my $sth = $dbh->prepare($strsth);
1683 $sth->execute( @query_params );
1684 while ( my $data = $sth->fetchrow_hashref ) {
1685 push( @results, $data );
1687 # ## countparcelbiblio: scalar(@results)
1693 #------------------------------------------------------------#
1697 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
1699 get a lists of parcels.
1706 is the bookseller this function has to get parcels.
1709 To know on what criteria the results list has to be ordered.
1712 is the booksellerinvoicenumber.
1714 =item $datefrom & $dateto
1715 to know on what date this function has to filter its search.
1720 a pointer on a hash list containing parcel informations as such :
1726 =item Last operation
1728 =item Number of biblio
1730 =item Number of items
1737 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
1738 my $dbh = C4::Context->dbh;
1739 my @query_params = ();
1741 SELECT aqinvoices.invoicenumber,
1742 datereceived,purchaseordernumber,
1743 count(DISTINCT biblionumber) AS biblio,
1744 sum(quantity) AS itemsexpected,
1745 sum(quantityreceived) AS itemsreceived
1746 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
1747 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
1748 WHERE aqbasket.booksellerid = ? and datereceived IS NOT NULL
1750 push @query_params, $bookseller;
1752 if ( defined $code ) {
1753 $strsth .= ' and aqinvoices.invoicenumber like ? ';
1754 # add a % to the end of the code to allow stemming.
1755 push @query_params, "$code%";
1758 if ( defined $datefrom ) {
1759 $strsth .= ' and datereceived >= ? ';
1760 push @query_params, $datefrom;
1763 if ( defined $dateto ) {
1764 $strsth .= 'and datereceived <= ? ';
1765 push @query_params, $dateto;
1768 $strsth .= "group by aqinvoices.invoicenumber,datereceived ";
1770 # can't use a placeholder to place this column name.
1771 # but, we could probably be checking to make sure it is a column that will be fetched.
1772 $strsth .= "order by $order " if ($order);
1774 my $sth = $dbh->prepare($strsth);
1776 $sth->execute( @query_params );
1777 my $results = $sth->fetchall_arrayref({});
1782 #------------------------------------------------------------#
1784 =head3 GetLateOrders
1786 @results = &GetLateOrders;
1788 Searches for bookseller with late orders.
1791 the table of supplier with late issues. This table is full of hashref.
1797 my $supplierid = shift;
1799 my $estimateddeliverydatefrom = shift;
1800 my $estimateddeliverydateto = shift;
1802 my $dbh = C4::Context->dbh;
1804 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
1805 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
1807 my @query_params = ();
1809 SELECT aqbasket.basketno,
1810 aqorders.ordernumber,
1811 DATE(aqbasket.closedate) AS orderdate,
1812 aqorders.rrp AS unitpricesupplier,
1813 aqorders.ecost AS unitpricelib,
1814 aqorders.claims_count AS claims_count,
1815 aqorders.claimed_date AS claimed_date,
1816 aqbudgets.budget_name AS budget,
1817 borrowers.branchcode AS branch,
1818 aqbooksellers.name AS supplier,
1819 aqbooksellers.id AS supplierid,
1820 biblio.author, biblio.title,
1821 biblioitems.publishercode AS publisher,
1822 biblioitems.publicationyear,
1823 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
1827 aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1828 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1829 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
1830 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
1831 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1832 WHERE aqorders.basketno = aqbasket.basketno
1833 AND ( datereceived = ''
1834 OR datereceived IS NULL
1835 OR aqorders.quantityreceived < aqorders.quantity
1837 AND aqbasket.closedate IS NOT NULL
1838 AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
1841 if ($dbdriver eq "mysql") {
1843 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity,
1844 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1845 DATEDIFF(CAST(now() AS date),closedate) AS latesince
1847 if ( defined $delay ) {
1848 $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ;
1849 push @query_params, $delay;
1852 HAVING quantity <> 0
1853 AND unitpricesupplier <> 0
1854 AND unitpricelib <> 0
1857 # FIXME: account for IFNULL as above
1859 aqorders.quantity AS quantity,
1860 aqorders.quantity * aqorders.rrp AS subtotal,
1861 (CAST(now() AS date) - closedate) AS latesince
1863 if ( defined $delay ) {
1864 $from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) ";
1865 push @query_params, $delay;
1868 if (defined $supplierid) {
1869 $from .= ' AND aqbasket.booksellerid = ? ';
1870 push @query_params, $supplierid;
1872 if (defined $branch) {
1873 $from .= ' AND borrowers.branchcode LIKE ? ';
1874 push @query_params, $branch;
1877 if ( defined $estimateddeliverydatefrom or defined $estimateddeliverydateto ) {
1878 $from .= ' AND aqbooksellers.deliverytime IS NOT NULL ';
1880 if ( defined $estimateddeliverydatefrom ) {
1881 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
1882 push @query_params, $estimateddeliverydatefrom;
1884 if ( defined $estimateddeliverydateto ) {
1885 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
1886 push @query_params, $estimateddeliverydateto;
1888 if ( defined $estimateddeliverydatefrom and not defined $estimateddeliverydateto ) {
1889 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
1891 if (C4::Context->preference("IndependentBranches")
1892 && C4::Context->userenv
1893 && C4::Context->userenv->{flags} != 1 ) {
1894 $from .= ' AND borrowers.branchcode LIKE ? ';
1895 push @query_params, C4::Context->userenv->{branch};
1897 my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier";
1898 $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params);
1899 my $sth = $dbh->prepare($query);
1900 $sth->execute(@query_params);
1902 while (my $data = $sth->fetchrow_hashref) {
1903 $data->{orderdate} = format_date($data->{orderdate});
1904 $data->{claimed_date} = format_date($data->{claimed_date});
1905 push @results, $data;
1910 #------------------------------------------------------------#
1914 (\@order_loop, $total_qty, $total_price, $total_qtyreceived) = GetHistory( %params );
1916 Retreives some acquisition history information
1924 basket - search both basket name and number
1925 booksellerinvoicenumber
1928 $order_loop is a list of hashrefs that each look like this:
1930 'author' => 'Twain, Mark',
1932 'biblionumber' => '215',
1934 'creationdate' => 'MM/DD/YYYY',
1935 'datereceived' => undef,
1938 'invoicenumber' => undef,
1940 'ordernumber' => '1',
1942 'quantityreceived' => undef,
1943 'title' => 'The Adventures of Huckleberry Finn'
1945 $total_qty is the sum of all of the quantities in $order_loop
1946 $total_price is the cost of each in $order_loop times the quantity
1947 $total_qtyreceived is the sum of all of the quantityreceived entries in $order_loop
1952 # don't run the query if there are no parameters (list would be too long for sure !)
1953 croak "No search params" unless @_;
1955 my $title = $params{title};
1956 my $author = $params{author};
1957 my $isbn = $params{isbn};
1958 my $ean = $params{ean};
1959 my $name = $params{name};
1960 my $from_placed_on = $params{from_placed_on};
1961 my $to_placed_on = $params{to_placed_on};
1962 my $basket = $params{basket};
1963 my $booksellerinvoicenumber = $params{booksellerinvoicenumber};
1964 my $basketgroupname = $params{basketgroupname};
1967 my $total_qtyreceived = 0;
1968 my $total_price = 0;
1970 my $dbh = C4::Context->dbh;
1978 aqbasket.basketname,
1979 aqbasket.basketgroupid,
1980 aqbasketgroups.name as groupname,
1982 aqbasket.creationdate,
1983 aqorders.datereceived,
1985 aqorders.quantityreceived,
1987 aqorders.ordernumber,
1989 aqinvoices.invoicenumber,
1990 aqbooksellers.id as id,
1991 aqorders.biblionumber
1993 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
1994 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
1995 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
1996 LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
1997 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
1998 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid";
2000 $query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber"
2001 if ( C4::Context->preference("IndependentBranches") );
2003 $query .= " WHERE (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') ";
2005 my @query_params = ();
2008 $query .= " AND biblio.title LIKE ? ";
2009 $title =~ s/\s+/%/g;
2010 push @query_params, "%$title%";
2014 $query .= " AND biblio.author LIKE ? ";
2015 push @query_params, "%$author%";
2019 $query .= " AND biblioitems.isbn LIKE ? ";
2020 push @query_params, "%$isbn%";
2022 if ( defined $ean and $ean ) {
2023 $query .= " AND biblioitems.ean = ? ";
2024 push @query_params, "$ean";
2027 $query .= " AND aqbooksellers.name LIKE ? ";
2028 push @query_params, "%$name%";
2031 if ( $from_placed_on ) {
2032 $query .= " AND creationdate >= ? ";
2033 push @query_params, $from_placed_on;
2036 if ( $to_placed_on ) {
2037 $query .= " AND creationdate <= ? ";
2038 push @query_params, $to_placed_on;
2042 if ($basket =~ m/^\d+$/) {
2043 $query .= " AND aqorders.basketno = ? ";
2044 push @query_params, $basket;
2046 $query .= " AND aqbasket.basketname LIKE ? ";
2047 push @query_params, "%$basket%";
2051 if ($booksellerinvoicenumber) {
2052 $query .= " AND aqinvoices.invoicenumber LIKE ? ";
2053 push @query_params, "%$booksellerinvoicenumber%";
2056 if ($basketgroupname) {
2057 $query .= " AND aqbasketgroups.name LIKE ? ";
2058 push @query_params, "%$basketgroupname%";
2061 if ( C4::Context->preference("IndependentBranches") ) {
2062 my $userenv = C4::Context->userenv;
2063 if ( $userenv && ($userenv->{flags} || 0) != 1 ) {
2064 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
2065 push @query_params, $userenv->{branch};
2068 $query .= " ORDER BY id";
2069 my $sth = $dbh->prepare($query);
2070 $sth->execute( @query_params );
2072 while ( my $line = $sth->fetchrow_hashref ) {
2073 $line->{count} = $cnt++;
2074 $line->{toggle} = 1 if $cnt % 2;
2075 push @order_loop, $line;
2076 $total_qty += $line->{'quantity'};
2077 $total_qtyreceived += $line->{'quantityreceived'};
2078 $total_price += $line->{'quantity'} * $line->{'ecost'};
2080 return \@order_loop, $total_qty, $total_price, $total_qtyreceived;
2083 =head2 GetRecentAcqui
2085 $results = GetRecentAcqui($days);
2087 C<$results> is a ref to a table which containts hashref
2091 sub GetRecentAcqui {
2093 my $dbh = C4::Context->dbh;
2097 ORDER BY timestamp DESC
2100 my $sth = $dbh->prepare($query);
2102 my $results = $sth->fetchall_arrayref({});
2108 $contractlist = &GetContracts($booksellerid, $activeonly);
2110 Looks up the contracts that belong to a bookseller
2112 Returns a list of contracts
2116 =item C<$booksellerid> is the "id" field in the "aqbooksellers" table.
2118 =item C<$activeonly> if exists get only contracts that are still active.
2125 my ( $booksellerid, $activeonly ) = @_;
2126 my $dbh = C4::Context->dbh;
2128 if (! $activeonly) {
2132 WHERE booksellerid=?
2137 WHERE booksellerid=?
2138 AND contractenddate >= CURDATE( )";
2140 my $sth = $dbh->prepare($query);
2141 $sth->execute( $booksellerid );
2143 while (my $data = $sth->fetchrow_hashref ) {
2144 push(@results, $data);
2150 #------------------------------------------------------------#
2154 $contract = &GetContract($contractID);
2156 Looks up the contract that has PRIMKEY (contractnumber) value $contractID
2163 my ( $contractno ) = @_;
2164 my $dbh = C4::Context->dbh;
2168 WHERE contractnumber=?
2171 my $sth = $dbh->prepare($query);
2172 $sth->execute( $contractno );
2173 my $result = $sth->fetchrow_hashref;
2181 &AddClaim($ordernumber);
2183 Add a claim for an order
2189 my ($ordernumber) = @_;
2190 my $dbh = C4::Context->dbh;
2193 claims_count = claims_count + 1,
2194 claimed_date = CURDATE()
2195 WHERE ordernumber = ?
2197 my $sth = $dbh->prepare($query);
2198 $sth->execute($ordernumber);
2203 my @invoices = GetInvoices(
2204 invoicenumber => $invoicenumber,
2205 suppliername => $suppliername,
2206 shipmentdatefrom => $shipmentdatefrom, # ISO format
2207 shipmentdateto => $shipmentdateto, # ISO format
2208 billingdatefrom => $billingdatefrom, # ISO format
2209 billingdateto => $billingdateto, # ISO format
2210 isbneanissn => $isbn_or_ean_or_issn,
2213 publisher => $publisher,
2214 publicationyear => $publicationyear,
2215 branchcode => $branchcode,
2216 order_by => $order_by
2219 Return a list of invoices that match all given criteria.
2221 $order_by is "column_name (asc|desc)", where column_name is any of
2222 'invoicenumber', 'booksellerid', 'shipmentdate', 'billingdate', 'closedate',
2223 'shipmentcost', 'shipmentcost_budgetid'.
2225 asc is the default if omitted
2232 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2233 closedate shipmentcost shipmentcost_budgetid);
2235 my $dbh = C4::Context->dbh;
2237 SELECT aqinvoices.*, aqbooksellers.name AS suppliername,
2240 aqorders.datereceived IS NOT NULL,
2241 aqorders.biblionumber,
2244 ) AS receivedbiblios,
2245 SUM(aqorders.quantityreceived) AS receiveditems
2247 LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid
2248 LEFT JOIN aqorders ON aqorders.invoiceid = aqinvoices.invoiceid
2249 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2250 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
2251 LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber
2256 if($args{supplierid}) {
2257 push @bind_strs, " aqinvoices.booksellerid = ? ";
2258 push @bind_args, $args{supplierid};
2260 if($args{invoicenumber}) {
2261 push @bind_strs, " aqinvoices.invoicenumber LIKE ? ";
2262 push @bind_args, "%$args{invoicenumber}%";
2264 if($args{suppliername}) {
2265 push @bind_strs, " aqbooksellers.name LIKE ? ";
2266 push @bind_args, "%$args{suppliername}%";
2268 if($args{shipmentdatefrom}) {
2269 push @bind_strs, " aqinvoices.shipementdate >= ? ";
2270 push @bind_args, $args{shipmentdatefrom};
2272 if($args{shipmentdateto}) {
2273 push @bind_strs, " aqinvoices.shipementdate <= ? ";
2274 push @bind_args, $args{shipmentdateto};
2276 if($args{billingdatefrom}) {
2277 push @bind_strs, " aqinvoices.billingdate >= ? ";
2278 push @bind_args, $args{billingdatefrom};
2280 if($args{billingdateto}) {
2281 push @bind_strs, " aqinvoices.billingdate <= ? ";
2282 push @bind_args, $args{billingdateto};
2284 if($args{isbneanissn}) {
2285 push @bind_strs, " (biblioitems.isbn LIKE ? OR biblioitems.ean LIKE ? OR biblioitems.issn LIKE ? ) ";
2286 push @bind_args, $args{isbneanissn}, $args{isbneanissn}, $args{isbneanissn};
2289 push @bind_strs, " biblio.title LIKE ? ";
2290 push @bind_args, $args{title};
2293 push @bind_strs, " biblio.author LIKE ? ";
2294 push @bind_args, $args{author};
2296 if($args{publisher}) {
2297 push @bind_strs, " biblioitems.publishercode LIKE ? ";
2298 push @bind_args, $args{publisher};
2300 if($args{publicationyear}) {
2301 push @bind_strs, " biblioitems.publicationyear = ? ";
2302 push @bind_args, $args{publicationyear};
2304 if($args{branchcode}) {
2305 push @bind_strs, " aqorders.branchcode = ? ";
2306 push @bind_args, $args{branchcode};
2309 $query .= " WHERE " . join(" AND ", @bind_strs) if @bind_strs;
2310 $query .= " GROUP BY aqinvoices.invoiceid ";
2312 if($args{order_by}) {
2313 my ($column, $direction) = split / /, $args{order_by};
2314 if(grep /^$column$/, @columns) {
2315 $direction ||= 'ASC';
2316 $query .= " ORDER BY $column $direction";
2320 my $sth = $dbh->prepare($query);
2321 $sth->execute(@bind_args);
2323 my $results = $sth->fetchall_arrayref({});
2329 my $invoice = GetInvoice($invoiceid);
2331 Get informations about invoice with given $invoiceid
2333 Return a hash filled with aqinvoices.* fields
2338 my ($invoiceid) = @_;
2341 return unless $invoiceid;
2343 my $dbh = C4::Context->dbh;
2349 my $sth = $dbh->prepare($query);
2350 $sth->execute($invoiceid);
2352 $invoice = $sth->fetchrow_hashref;
2356 =head3 GetInvoiceDetails
2358 my $invoice = GetInvoiceDetails($invoiceid)
2360 Return informations about an invoice + the list of related order lines
2362 Orders informations are in $invoice->{orders} (array ref)
2366 sub GetInvoiceDetails {
2367 my ($invoiceid) = @_;
2369 if ( !defined $invoiceid ) {
2370 carp 'GetInvoiceDetails called without an invoiceid';
2374 my $dbh = C4::Context->dbh;
2376 SELECT aqinvoices.*, aqbooksellers.name AS suppliername
2378 LEFT JOIN aqbooksellers ON aqinvoices.booksellerid = aqbooksellers.id
2381 my $sth = $dbh->prepare($query);
2382 $sth->execute($invoiceid);
2384 my $invoice = $sth->fetchrow_hashref;
2387 SELECT aqorders.*, biblio.*,
2390 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
2391 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2394 $sth = $dbh->prepare($query);
2395 $sth->execute($invoiceid);
2396 $invoice->{orders} = $sth->fetchall_arrayref({});
2397 $invoice->{orders} ||= []; # force an empty arrayref if fetchall_arrayref fails
2404 my $invoiceid = AddInvoice(
2405 invoicenumber => $invoicenumber,
2406 booksellerid => $booksellerid,
2407 shipmentdate => $shipmentdate,
2408 billingdate => $billingdate,
2409 closedate => $closedate,
2410 shipmentcost => $shipmentcost,
2411 shipmentcost_budgetid => $shipmentcost_budgetid
2414 Create a new invoice and return its id or undef if it fails.
2421 return unless(%invoice and $invoice{invoicenumber});
2423 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2424 closedate shipmentcost shipmentcost_budgetid);
2428 foreach my $key (keys %invoice) {
2429 if(0 < grep(/^$key$/, @columns)) {
2430 push @set_strs, "$key = ?";
2431 push @set_args, ($invoice{$key} || undef);
2437 my $dbh = C4::Context->dbh;
2438 my $query = "INSERT INTO aqinvoices SET ";
2439 $query .= join (",", @set_strs);
2440 my $sth = $dbh->prepare($query);
2441 $rv = $sth->execute(@set_args);
2443 $rv = $dbh->last_insert_id(undef, undef, 'aqinvoices', undef);
2452 invoiceid => $invoiceid, # Mandatory
2453 invoicenumber => $invoicenumber,
2454 booksellerid => $booksellerid,
2455 shipmentdate => $shipmentdate,
2456 billingdate => $billingdate,
2457 closedate => $closedate,
2458 shipmentcost => $shipmentcost,
2459 shipmentcost_budgetid => $shipmentcost_budgetid
2462 Modify an invoice, invoiceid is mandatory.
2464 Return undef if it fails.
2471 return unless(%invoice and $invoice{invoiceid});
2473 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2474 closedate shipmentcost shipmentcost_budgetid);
2478 foreach my $key (keys %invoice) {
2479 if(0 < grep(/^$key$/, @columns)) {
2480 push @set_strs, "$key = ?";
2481 push @set_args, ($invoice{$key} || undef);
2485 my $dbh = C4::Context->dbh;
2486 my $query = "UPDATE aqinvoices SET ";
2487 $query .= join(",", @set_strs);
2488 $query .= " WHERE invoiceid = ?";
2490 my $sth = $dbh->prepare($query);
2491 $sth->execute(@set_args, $invoice{invoiceid});
2496 CloseInvoice($invoiceid);
2500 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => undef);
2505 my ($invoiceid) = @_;
2507 return unless $invoiceid;
2509 my $dbh = C4::Context->dbh;
2512 SET closedate = CAST(NOW() AS DATE)
2515 my $sth = $dbh->prepare($query);
2516 $sth->execute($invoiceid);
2519 =head3 ReopenInvoice
2521 ReopenInvoice($invoiceid);
2525 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => C4::Dates->new()->output('iso'))
2530 my ($invoiceid) = @_;
2532 return unless $invoiceid;
2534 my $dbh = C4::Context->dbh;
2537 SET closedate = NULL
2540 my $sth = $dbh->prepare($query);
2541 $sth->execute($invoiceid);
2546 DelInvoice($invoiceid);
2548 Delete an invoice if there are no items attached to it.
2553 my ($invoiceid) = @_;
2555 return unless $invoiceid;
2557 my $dbh = C4::Context->dbh;
2563 my $sth = $dbh->prepare($query);
2564 $sth->execute($invoiceid);
2565 my $res = $sth->fetchrow_arrayref;
2566 if ( $res && $res->[0] == 0 ) {
2568 DELETE FROM aqinvoices
2571 my $sth = $dbh->prepare($query);
2572 return ( $sth->execute($invoiceid) > 0 );
2582 Koha Development Team <http://koha-community.org/>