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
8 # under the terms of the GNU General Public License as published by
9 # the Free Software Foundation; either version 3 of the License, or
10 # (at your option) any later version.
12 # Koha is distributed in the hope that it will be useful, but
13 # WITHOUT ANY WARRANTY; without even the implied warranty of
14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 # GNU General Public License for more details.
17 # You should have received a copy of the GNU General Public License
18 # along with Koha; if not, see <http://www.gnu.org/licenses>.
29 use C4
::Templates
qw(gettemplate);
30 use Koha
::DateUtils
qw( dt_from_string output_pref );
31 use Koha
::Acquisition
::Order
;
32 use Koha
::Acquisition
::Bookseller
;
33 use Koha
::Number
::Price
;
43 use vars
qw(@ISA @EXPORT);
49 &GetBasket &NewBasket &CloseBasket &ReopenBasket &DelBasket &ModBasket
50 &GetBasketAsCSV &GetBasketGroupAsCSV
51 &GetBasketsByBookseller &GetBasketsByBasketgroup
52 &GetBasketsInfosByBookseller
54 &GetBasketUsers &ModBasketUsers
59 &ModBasketgroup &NewBasketgroup &DelBasketgroup &GetBasketgroup &CloseBasketgroup
60 &GetBasketgroups &ReOpenBasketgroup
62 &DelOrder &ModOrder &GetOrder &GetOrders &GetOrdersByBiblionumber
63 &GetLateOrders &GetOrderFromItemnumber
64 &SearchOrders &GetHistory &GetRecentAcqui
65 &ModReceiveOrder &CancelReceipt
67 &GetLastOrderNotReceivedFromSubscriptionid &GetLastOrderReceivedFromSubscriptionid
82 &GetItemnumbersFromOrder
85 &GetBiblioCountByBasketno
91 &FillWithDefaultValues
99 sub GetOrderFromItemnumber
{
100 my ($itemnumber) = @_;
101 my $dbh = C4
::Context
->dbh;
104 SELECT
* from aqorders LEFT JOIN aqorders_items
105 ON
( aqorders
.ordernumber
= aqorders_items
.ordernumber
)
106 WHERE itemnumber
= ?
|;
108 my $sth = $dbh->prepare($query);
112 $sth->execute($itemnumber);
114 my $order = $sth->fetchrow_hashref;
119 # Returns the itemnumber(s) associated with the ordernumber given in parameter
120 sub GetItemnumbersFromOrder
{
121 my ($ordernumber) = @_;
122 my $dbh = C4
::Context
->dbh;
123 my $query = "SELECT itemnumber FROM aqorders_items WHERE ordernumber=?";
124 my $sth = $dbh->prepare($query);
125 $sth->execute($ordernumber);
128 while (my $order = $sth->fetchrow_hashref) {
129 push @tab, $order->{'itemnumber'};
143 C4::Acquisition - Koha functions for dealing with orders and acquisitions
151 The functions in this module deal with acquisitions, managing book
152 orders, basket and parcels.
156 =head2 FUNCTIONS ABOUT BASKETS
160 $aqbasket = &GetBasket($basketnumber);
162 get all basket informations in aqbasket for a given basket
164 B<returns:> informations for a given basket returned as a hashref.
170 my $dbh = C4
::Context
->dbh;
173 concat( b.firstname,' ',b.surname) AS authorisedbyname
175 LEFT JOIN borrowers b ON aqbasket.authorisedby=b.borrowernumber
178 my $sth=$dbh->prepare($query);
179 $sth->execute($basketno);
180 my $basket = $sth->fetchrow_hashref;
184 #------------------------------------------------------------#
188 $basket = &NewBasket( $booksellerid, $authorizedby, $basketname,
189 $basketnote, $basketbooksellernote, $basketcontractnumber, $deliveryplace, $billingplace, $is_standing );
191 Create a new basket in aqbasket table
195 =item C<$booksellerid> is a foreign key in the aqbasket table
197 =item C<$authorizedby> is the username of who created the basket
201 The other parameters are optional, see ModBasketHeader for more info on them.
206 my ( $booksellerid, $authorisedby, $basketname, $basketnote,
207 $basketbooksellernote, $basketcontractnumber, $deliveryplace,
208 $billingplace, $is_standing ) = @_;
209 my $dbh = C4
::Context
->dbh;
211 'INSERT INTO aqbasket (creationdate,booksellerid,authorisedby) '
212 . 'VALUES (now(),?,?)';
213 $dbh->do( $query, {}, $booksellerid, $authorisedby );
215 my $basket = $dbh->{mysql_insertid
};
216 $basketname ||= q{}; # default to empty strings
218 $basketbooksellernote ||= q{};
219 ModBasketHeader
( $basket, $basketname, $basketnote, $basketbooksellernote,
220 $basketcontractnumber, $booksellerid, $deliveryplace, $billingplace, $is_standing );
224 #------------------------------------------------------------#
228 &CloseBasket($basketno);
230 close a basket (becomes unmodifiable, except for receives)
236 my $dbh = C4
::Context
->dbh;
237 $dbh->do('UPDATE aqbasket SET closedate=now() WHERE basketno=?', {}, $basketno );
239 $dbh->do( q{UPDATE aqorders SET orderstatus = 'ordered' WHERE basketno = ? AND orderstatus != 'complete'},
246 &ReopenBasket($basketno);
254 my $dbh = C4
::Context
->dbh;
255 $dbh->do( q{UPDATE aqbasket SET closedate=NULL WHERE basketno=?}, {}, $basketno );
259 SET orderstatus = 'new'
261 AND orderstatus != 'complete'
266 #------------------------------------------------------------#
268 =head3 GetBasketAsCSV
270 &GetBasketAsCSV($basketno);
272 Export a basket as CSV
274 $cgi parameter is needed for column name translation
279 my ($basketno, $cgi) = @_;
280 my $basket = GetBasket
($basketno);
281 my @orders = GetOrders
($basketno);
282 my $contract = GetContract
({
283 contractnumber
=> $basket->{'contractnumber'}
286 my $template = C4
::Templates
::gettemplate
("acqui/csv/basket.tt", "intranet", $cgi);
289 foreach my $order (@orders) {
290 my $bd = GetBiblioData
( $order->{'biblionumber'} );
292 contractname
=> $contract->{'contractname'},
293 ordernumber
=> $order->{'ordernumber'},
294 entrydate
=> $order->{'entrydate'},
295 isbn
=> $order->{'isbn'},
296 author
=> $bd->{'author'},
297 title
=> $bd->{'title'},
298 publicationyear
=> $bd->{'publicationyear'},
299 publishercode
=> $bd->{'publishercode'},
300 collectiontitle
=> $bd->{'collectiontitle'},
301 notes
=> $order->{'order_vendornote'},
302 quantity
=> $order->{'quantity'},
303 rrp
=> $order->{'rrp'},
304 deliveryplace
=> C4
::Branch
::GetBranchName
( $basket->{'deliveryplace'} ),
305 billingplace
=> C4
::Branch
::GetBranchName
( $basket->{'billingplace'} ),
308 contractname author title publishercode collectiontitle notes
309 deliveryplace billingplace
311 # Double the quotes to not be interpreted as a field end
312 $row->{$_} =~ s/"/""/g if $row->{$_};
318 if(defined $a->{publishercode
} and defined $b->{publishercode
}) {
319 $a->{publishercode
} cmp $b->{publishercode
};
323 $template->param(rows
=> \
@rows);
325 return $template->output;
329 =head3 GetBasketGroupAsCSV
331 &GetBasketGroupAsCSV($basketgroupid);
333 Export a basket group as CSV
335 $cgi parameter is needed for column name translation
339 sub GetBasketGroupAsCSV
{
340 my ($basketgroupid, $cgi) = @_;
341 my $baskets = GetBasketsByBasketgroup
($basketgroupid);
343 my $template = C4
::Templates
::gettemplate
('acqui/csv/basketgroup.tt', 'intranet', $cgi);
346 for my $basket (@
$baskets) {
347 my @orders = GetOrders
( $basket->{basketno
} );
348 my $contract = GetContract
({
349 contractnumber
=> $basket->{contractnumber
}
351 my $bookseller = Koha
::Acquisition
::Bookseller
->fetch({ id
=> $basket->{booksellerid
} });
352 my $basketgroup = GetBasketgroup
( $$basket{basketgroupid
} );
354 foreach my $order (@orders) {
355 my $bd = GetBiblioData
( $order->{'biblionumber'} );
357 clientnumber
=> $bookseller->{accountnumber
},
358 basketname
=> $basket->{basketname
},
359 ordernumber
=> $order->{ordernumber
},
360 author
=> $bd->{author
},
361 title
=> $bd->{title
},
362 publishercode
=> $bd->{publishercode
},
363 publicationyear
=> $bd->{publicationyear
},
364 collectiontitle
=> $bd->{collectiontitle
},
365 isbn
=> $order->{isbn
},
366 quantity
=> $order->{quantity
},
367 rrp
=> $order->{rrp
},
368 discount
=> $bookseller->{discount
},
369 ecost
=> $order->{ecost
},
370 notes
=> $order->{order_vendornote
},
371 entrydate
=> $order->{entrydate
},
372 booksellername
=> $bookseller->{name
},
373 bookselleraddress
=> $bookseller->{address1
},
374 booksellerpostal
=> $bookseller->{postal
},
375 contractnumber
=> $contract->{contractnumber
},
376 contractname
=> $contract->{contractname
},
377 basketgroupdeliveryplace
=> C4
::Branch
::GetBranchName
( $basketgroup->{deliveryplace
} ),
378 basketgroupbillingplace
=> C4
::Branch
::GetBranchName
( $basketgroup->{billingplace
} ),
379 basketdeliveryplace
=> C4
::Branch
::GetBranchName
( $basket->{deliveryplace
} ),
380 basketbillingplace
=> C4
::Branch
::GetBranchName
( $basket->{billingplace
} ),
383 basketname author title publishercode collectiontitle notes
384 booksellername bookselleraddress booksellerpostal contractname
385 basketgroupdeliveryplace basketgroupbillingplace
386 basketdeliveryplace basketbillingplace
388 # Double the quotes to not be interpreted as a field end
389 $row->{$_} =~ s/"/""/g if $row->{$_};
394 $template->param(rows
=> \
@rows);
396 return $template->output;
400 =head3 CloseBasketgroup
402 &CloseBasketgroup($basketgroupno);
408 sub CloseBasketgroup
{
409 my ($basketgroupno) = @_;
410 my $dbh = C4
::Context
->dbh;
411 my $sth = $dbh->prepare("
412 UPDATE aqbasketgroups
416 $sth->execute($basketgroupno);
419 #------------------------------------------------------------#
421 =head3 ReOpenBaskergroup($basketgroupno)
423 &ReOpenBaskergroup($basketgroupno);
429 sub ReOpenBasketgroup
{
430 my ($basketgroupno) = @_;
431 my $dbh = C4
::Context
->dbh;
432 my $sth = $dbh->prepare("
433 UPDATE aqbasketgroups
437 $sth->execute($basketgroupno);
440 #------------------------------------------------------------#
445 &DelBasket($basketno);
447 Deletes the basket that has basketno field $basketno in the aqbasket table.
451 =item C<$basketno> is the primary key of the basket in the aqbasket table.
458 my ( $basketno ) = @_;
459 my $query = "DELETE FROM aqbasket WHERE basketno=?";
460 my $dbh = C4
::Context
->dbh;
461 my $sth = $dbh->prepare($query);
462 $sth->execute($basketno);
466 #------------------------------------------------------------#
470 &ModBasket($basketinfo);
472 Modifies a basket, using a hashref $basketinfo for the relevant information, only $basketinfo->{'basketno'} is required.
476 =item C<$basketno> is the primary key of the basket in the aqbasket table.
483 my $basketinfo = shift;
484 my $query = "UPDATE aqbasket SET ";
486 foreach my $key (keys %$basketinfo){
487 if ($key ne 'basketno'){
488 $query .= "$key=?, ";
489 push(@params, $basketinfo->{$key} || undef );
492 # get rid of the "," at the end of $query
493 if (substr($query, length($query)-2) eq ', '){
498 $query .= "WHERE basketno=?";
499 push(@params, $basketinfo->{'basketno'});
500 my $dbh = C4
::Context
->dbh;
501 my $sth = $dbh->prepare($query);
502 $sth->execute(@params);
507 #------------------------------------------------------------#
509 =head3 ModBasketHeader
511 &ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid);
513 Modifies a basket's header.
517 =item C<$basketno> is the "basketno" field in the "aqbasket" table;
519 =item C<$basketname> is the "basketname" field in the "aqbasket" table;
521 =item C<$note> is the "note" field in the "aqbasket" table;
523 =item C<$booksellernote> is the "booksellernote" field in the "aqbasket" table;
525 =item C<$contractnumber> is the "contractnumber" (foreign) key in the "aqbasket" table.
527 =item C<$booksellerid> is the id (foreign) key in the "aqbooksellers" table for the vendor.
529 =item C<$deliveryplace> is the "deliveryplace" field in the aqbasket table.
531 =item C<$billingplace> is the "billingplace" field in the aqbasket table.
533 =item C<$is_standing> is the "is_standing" field in the aqbasket table.
539 sub ModBasketHeader
{
540 my ($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid, $deliveryplace, $billingplace, $is_standing) = @_;
543 SET basketname
=?
, note
=?
, booksellernote
=?
, booksellerid
=?
, deliveryplace
=?
, billingplace
=?
, is_standing
=?
547 my $dbh = C4
::Context
->dbh;
548 my $sth = $dbh->prepare($query);
549 $sth->execute($basketname, $note, $booksellernote, $booksellerid, $deliveryplace, $billingplace, $is_standing, $basketno);
551 if ( $contractnumber ) {
552 my $query2 ="UPDATE aqbasket SET contractnumber=? WHERE basketno=?";
553 my $sth2 = $dbh->prepare($query2);
554 $sth2->execute($contractnumber,$basketno);
559 #------------------------------------------------------------#
561 =head3 GetBasketsByBookseller
563 @results = &GetBasketsByBookseller($booksellerid, $extra);
565 Returns a list of hashes of all the baskets that belong to bookseller 'booksellerid'.
569 =item C<$booksellerid> is the 'id' field of the bookseller in the aqbooksellers table
571 =item C<$extra> is the extra sql parameters, can be
573 $extra->{groupby}: group baskets by column
574 ex. $extra->{groupby} = aqbasket.basketgroupid
575 $extra->{orderby}: order baskets by column
576 $extra->{limit}: limit number of results (can be helpful for pagination)
582 sub GetBasketsByBookseller
{
583 my ($booksellerid, $extra) = @_;
584 my $query = "SELECT * FROM aqbasket WHERE booksellerid=?";
586 if ($extra->{groupby
}) {
587 $query .= " GROUP by $extra->{groupby}";
589 if ($extra->{orderby
}){
590 $query .= " ORDER by $extra->{orderby}";
592 if ($extra->{limit
}){
593 $query .= " LIMIT $extra->{limit}";
596 my $dbh = C4
::Context
->dbh;
597 my $sth = $dbh->prepare($query);
598 $sth->execute($booksellerid);
599 return $sth->fetchall_arrayref({});
602 =head3 GetBasketsInfosByBookseller
604 my $baskets = GetBasketsInfosByBookseller($supplierid, $allbaskets);
606 The optional second parameter allbaskets is a boolean allowing you to
607 select all baskets from the supplier; by default only active baskets (open or
608 closed but still something to receive) are returned.
610 Returns in a arrayref of hashref all about booksellers baskets, plus:
611 total_biblios: Number of distinct biblios in basket
612 total_items: Number of items in basket
613 expected_items: Number of non-received items in basket
617 sub GetBasketsInfosByBookseller
{
618 my ($supplierid, $allbaskets) = @_;
620 return unless $supplierid;
622 my $dbh = C4
::Context
->dbh;
625 SUM(aqorders.quantity) AS total_items,
627 IF ( aqorders.orderstatus = 'cancelled', aqorders.quantity, 0 )
628 ) AS total_items_cancelled,
629 COUNT(DISTINCT aqorders.biblionumber) AS total_biblios,
631 IF(aqorders.datereceived IS NULL
632 AND aqorders.datecancellationprinted IS NULL
637 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
638 WHERE booksellerid = ?};
640 unless ( $allbaskets ) {
641 $query.=" AND (closedate IS NULL OR (aqorders.quantity > aqorders.quantityreceived AND datecancellationprinted IS NULL))";
643 $query.=" GROUP BY aqbasket.basketno";
645 my $sth = $dbh->prepare($query);
646 $sth->execute($supplierid);
647 my $baskets = $sth->fetchall_arrayref({});
649 # Retrieve the number of biblios cancelled
650 my $cancelled_biblios = $dbh->selectall_hashref( q
|
651 SELECT COUNT
(DISTINCT
(biblionumber
)) AS total_biblios_cancelled
, aqbasket
.basketno
653 LEFT JOIN aqorders ON aqorders
.basketno
= aqbasket
.basketno
654 WHERE booksellerid
= ?
655 AND aqorders
.orderstatus
= 'cancelled'
656 GROUP BY aqbasket
.basketno
657 |, 'basketno', {}, $supplierid );
659 $_->{total_biblios_cancelled
} = $cancelled_biblios->{$_->{basketno
}}{total_biblios_cancelled
} || 0
665 =head3 GetBasketUsers
667 $basketusers_ids = &GetBasketUsers($basketno);
669 Returns a list of all borrowernumbers that are in basket users list
674 my $basketno = shift;
676 return unless $basketno;
679 SELECT borrowernumber
683 my $dbh = C4
::Context
->dbh;
684 my $sth = $dbh->prepare($query);
685 $sth->execute($basketno);
686 my $results = $sth->fetchall_arrayref( {} );
689 foreach (@
$results) {
690 push @borrowernumbers, $_->{'borrowernumber'};
693 return @borrowernumbers;
696 =head3 ModBasketUsers
698 my @basketusers_ids = (1, 2, 3);
699 &ModBasketUsers($basketno, @basketusers_ids);
701 Delete all users from basket users list, and add users in C<@basketusers_ids>
707 my ($basketno, @basketusers_ids) = @_;
709 return unless $basketno;
711 my $dbh = C4
::Context
->dbh;
713 DELETE FROM aqbasketusers
716 my $sth = $dbh->prepare($query);
717 $sth->execute($basketno);
720 INSERT INTO aqbasketusers
(basketno
, borrowernumber
)
723 $sth = $dbh->prepare($query);
724 foreach my $basketuser_id (@basketusers_ids) {
725 $sth->execute($basketno, $basketuser_id);
730 =head3 CanUserManageBasket
732 my $bool = CanUserManageBasket($borrower, $basket[, $userflags]);
733 my $bool = CanUserManageBasket($borrowernumber, $basketno[, $userflags]);
735 Check if a borrower can manage a basket, according to system preference
736 AcqViewBaskets, user permissions and basket properties (creator, users list,
739 First parameter can be either a borrowernumber or a hashref as returned by
740 C4::Members::GetMember.
742 Second parameter can be either a basketno or a hashref as returned by
743 C4::Acquisition::GetBasket.
745 The third parameter is optional. If given, it should be a hashref as returned
746 by C4::Auth::getuserflags. If not, getuserflags is called.
748 If user is authorised to manage basket, returns 1.
753 sub CanUserManageBasket
{
754 my ($borrower, $basket, $userflags) = @_;
756 if (!ref $borrower) {
757 $borrower = C4
::Members
::GetMember
(borrowernumber
=> $borrower);
760 $basket = GetBasket
($basket);
763 return 0 unless ($basket and $borrower);
765 my $borrowernumber = $borrower->{borrowernumber
};
766 my $basketno = $basket->{basketno
};
768 my $AcqViewBaskets = C4
::Context
->preference('AcqViewBaskets');
770 if (!defined $userflags) {
771 my $dbh = C4
::Context
->dbh;
772 my $sth = $dbh->prepare("SELECT flags FROM borrowers WHERE borrowernumber = ?");
773 $sth->execute($borrowernumber);
774 my ($flags) = $sth->fetchrow_array;
777 $userflags = C4
::Auth
::getuserflags
($flags, $borrower->{userid
}, $dbh);
780 unless ($userflags->{superlibrarian
}
781 || (ref $userflags->{acquisition
} && $userflags->{acquisition
}->{order_manage_all
})
782 || (!ref $userflags->{acquisition
} && $userflags->{acquisition
}))
784 if (not exists $userflags->{acquisition
}) {
788 if ( (ref $userflags->{acquisition
} && !$userflags->{acquisition
}->{order_manage
})
789 || (!ref $userflags->{acquisition
} && !$userflags->{acquisition
}) ) {
793 if ($AcqViewBaskets eq 'user'
794 && $basket->{authorisedby
} != $borrowernumber
795 && grep($borrowernumber, GetBasketUsers
($basketno)) == 0) {
799 if ($AcqViewBaskets eq 'branch' && defined $basket->{branch
}
800 && $basket->{branch
} ne $borrower->{branchcode
}) {
808 #------------------------------------------------------------#
810 =head3 GetBasketsByBasketgroup
812 $baskets = &GetBasketsByBasketgroup($basketgroupid);
814 Returns a reference to all baskets that belong to basketgroup $basketgroupid.
818 sub GetBasketsByBasketgroup
{
819 my $basketgroupid = shift;
821 SELECT
*, aqbasket
.booksellerid as booksellerid
823 LEFT JOIN aqcontract USING
(contractnumber
) WHERE basketgroupid
=?
825 my $dbh = C4
::Context
->dbh;
826 my $sth = $dbh->prepare($query);
827 $sth->execute($basketgroupid);
828 return $sth->fetchall_arrayref({});
831 #------------------------------------------------------------#
833 =head3 NewBasketgroup
835 $basketgroupid = NewBasketgroup(\%hashref);
837 Adds a basketgroup to the aqbasketgroups table, and add the initial baskets to it.
839 $hashref->{'booksellerid'} is the 'id' field of the bookseller in the aqbooksellers table,
841 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
843 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
845 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
847 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
849 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
851 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
853 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
858 my $basketgroupinfo = shift;
859 die "booksellerid is required to create a basketgroup" unless $basketgroupinfo->{'booksellerid'};
860 my $query = "INSERT INTO aqbasketgroups (";
862 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
863 if ( defined $basketgroupinfo->{$field} ) {
864 $query .= "$field, ";
865 push(@params, $basketgroupinfo->{$field});
868 $query .= "booksellerid) VALUES (";
873 push(@params, $basketgroupinfo->{'booksellerid'});
874 my $dbh = C4
::Context
->dbh;
875 my $sth = $dbh->prepare($query);
876 $sth->execute(@params);
877 my $basketgroupid = $dbh->{'mysql_insertid'};
878 if( $basketgroupinfo->{'basketlist'} ) {
879 foreach my $basketno (@
{$basketgroupinfo->{'basketlist'}}) {
880 my $query2 = "UPDATE aqbasket SET basketgroupid=? WHERE basketno=?";
881 my $sth2 = $dbh->prepare($query2);
882 $sth2->execute($basketgroupid, $basketno);
885 return $basketgroupid;
888 #------------------------------------------------------------#
890 =head3 ModBasketgroup
892 ModBasketgroup(\%hashref);
894 Modifies a basketgroup in the aqbasketgroups table, and add the baskets to it.
896 $hashref->{'id'} is the 'id' field of the basketgroup in the aqbasketgroup table, this parameter is mandatory,
898 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
900 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
902 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
904 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
906 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
908 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
910 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
915 my $basketgroupinfo = shift;
916 die "basketgroup id is required to edit a basketgroup" unless $basketgroupinfo->{'id'};
917 my $dbh = C4
::Context
->dbh;
918 my $query = "UPDATE aqbasketgroups SET ";
920 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
921 if ( defined $basketgroupinfo->{$field} ) {
922 $query .= "$field=?, ";
923 push(@params, $basketgroupinfo->{$field});
928 $query .= " WHERE id=?";
929 push(@params, $basketgroupinfo->{'id'});
930 my $sth = $dbh->prepare($query);
931 $sth->execute(@params);
933 $sth = $dbh->prepare('UPDATE aqbasket SET basketgroupid = NULL WHERE basketgroupid = ?');
934 $sth->execute($basketgroupinfo->{'id'});
936 if($basketgroupinfo->{'basketlist'} && @
{$basketgroupinfo->{'basketlist'}}){
937 $sth = $dbh->prepare("UPDATE aqbasket SET basketgroupid=? WHERE basketno=?");
938 foreach my $basketno (@
{$basketgroupinfo->{'basketlist'}}) {
939 $sth->execute($basketgroupinfo->{'id'}, $basketno);
945 #------------------------------------------------------------#
947 =head3 DelBasketgroup
949 DelBasketgroup($basketgroupid);
951 Deletes a basketgroup in the aqbasketgroups table, and removes the reference to it from the baskets,
955 =item C<$basketgroupid> is the 'id' field of the basket in the aqbasketgroup table
962 my $basketgroupid = shift;
963 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
964 my $query = "DELETE FROM aqbasketgroups WHERE id=?";
965 my $dbh = C4
::Context
->dbh;
966 my $sth = $dbh->prepare($query);
967 $sth->execute($basketgroupid);
971 #------------------------------------------------------------#
974 =head2 FUNCTIONS ABOUT ORDERS
976 =head3 GetBasketgroup
978 $basketgroup = &GetBasketgroup($basketgroupid);
980 Returns a reference to the hash containing all information about the basketgroup.
985 my $basketgroupid = shift;
986 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
987 my $dbh = C4
::Context
->dbh;
988 my $result_set = $dbh->selectall_arrayref(
989 'SELECT * FROM aqbasketgroups WHERE id=?',
993 return $result_set->[0]; # id is unique
996 #------------------------------------------------------------#
998 =head3 GetBasketgroups
1000 $basketgroups = &GetBasketgroups($booksellerid);
1002 Returns a reference to the array of all the basketgroups of bookseller $booksellerid.
1006 sub GetBasketgroups
{
1007 my $booksellerid = shift;
1008 die 'bookseller id is required to edit a basketgroup' unless $booksellerid;
1009 my $query = 'SELECT * FROM aqbasketgroups WHERE booksellerid=? ORDER BY id DESC';
1010 my $dbh = C4
::Context
->dbh;
1011 my $sth = $dbh->prepare($query);
1012 $sth->execute($booksellerid);
1013 return $sth->fetchall_arrayref({});
1016 #------------------------------------------------------------#
1018 =head2 FUNCTIONS ABOUT ORDERS
1022 @orders = &GetOrders( $basketno, { orderby => 'biblio.title', cancelled => 0|1 } );
1024 Looks up the pending (non-cancelled) orders with the given basket
1027 If cancelled is set, only cancelled orders will be returned.
1032 my ( $basketno, $params ) = @_;
1034 return () unless $basketno;
1036 my $orderby = $params->{orderby
};
1037 my $cancelled = $params->{cancelled
} || 0;
1039 my $dbh = C4
::Context
->dbh;
1041 SELECT biblio
.*,biblioitems
.*,
1045 $query .= $cancelled
1047 aqorders_transfers
.ordernumber_to AS transferred_to
,
1048 aqorders_transfers
.timestamp AS transferred_to_timestamp
1051 aqorders_transfers
.ordernumber_from AS transferred_from
,
1052 aqorders_transfers
.timestamp AS transferred_from_timestamp
1056 LEFT JOIN aqbudgets ON aqbudgets
.budget_id
= aqorders
.budget_id
1057 LEFT JOIN biblio ON biblio
.biblionumber
= aqorders
.biblionumber
1058 LEFT JOIN biblioitems ON biblioitems
.biblionumber
=biblio
.biblionumber
1060 $query .= $cancelled
1062 LEFT JOIN aqorders_transfers ON aqorders_transfers
.ordernumber_from
= aqorders
.ordernumber
1065 LEFT JOIN aqorders_transfers ON aqorders_transfers
.ordernumber_to
= aqorders
.ordernumber
1073 $orderby ||= q
|biblioitems
.publishercode
, biblio
.title
|;
1075 AND
(datecancellationprinted IS NOT NULL
1076 AND datecancellationprinted
<> '0000-00-00')
1081 q
|aqorders
.datecancellationprinted desc
, aqorders
.timestamp desc
|;
1083 AND
(datecancellationprinted IS NULL OR datecancellationprinted
='0000-00-00')
1087 $query .= " ORDER BY $orderby";
1089 $dbh->selectall_arrayref( $query, { Slice
=> {} }, $basketno );
1094 #------------------------------------------------------------#
1096 =head3 GetOrdersByBiblionumber
1098 @orders = &GetOrdersByBiblionumber($biblionumber);
1100 Looks up the orders with linked to a specific $biblionumber, including
1101 cancelled orders and received orders.
1104 C<@orders> is an array of references-to-hash, whose keys are the
1105 fields from the aqorders, biblio, and biblioitems tables in the Koha database.
1109 sub GetOrdersByBiblionumber
{
1110 my $biblionumber = shift;
1111 return unless $biblionumber;
1112 my $dbh = C4
::Context
->dbh;
1114 SELECT biblio.*,biblioitems.*,
1118 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1119 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1120 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1121 WHERE aqorders.biblionumber=?
1124 $dbh->selectall_arrayref( $query, { Slice
=> {} }, $biblionumber );
1125 return @
{$result_set};
1129 #------------------------------------------------------------#
1133 $order = &GetOrder($ordernumber);
1135 Looks up an order by order number.
1137 Returns a reference-to-hash describing the order. The keys of
1138 C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database.
1143 my ($ordernumber) = @_;
1144 return unless $ordernumber;
1146 my $dbh = C4
::Context
->dbh;
1147 my $query = qq{SELECT
1151 aqbasket
.basketname
,
1152 borrowers
.branchcode
,
1153 biblioitems
.publicationyear
,
1154 biblio
.copyrightdate
,
1155 biblioitems
.editionstatement
,
1159 biblioitems
.publishercode
,
1160 aqorders
.rrp AS unitpricesupplier
,
1161 aqorders
.ecost AS unitpricelib
,
1162 aqorders
.claims_count AS claims_count
,
1163 aqorders
.claimed_date AS claimed_date
,
1164 aqbudgets
.budget_name AS budget
,
1165 aqbooksellers
.name AS supplier
,
1166 aqbooksellers
.id AS supplierid
,
1167 biblioitems
.publishercode AS publisher
,
1168 ADDDATE
(aqbasket
.closedate
, INTERVAL aqbooksellers
.deliverytime DAY
) AS estimateddeliverydate
,
1169 DATE
(aqbasket
.closedate
) AS orderdate
,
1170 aqorders
.quantity
- COALESCE
(aqorders
.quantityreceived
,0) AS quantity_to_receive
,
1171 (aqorders
.quantity
- COALESCE
(aqorders
.quantityreceived
,0)) * aqorders
.rrp AS subtotal
,
1172 DATEDIFF
(CURDATE
( ),closedate
) AS latesince
1173 FROM aqorders LEFT JOIN biblio ON biblio
.biblionumber
= aqorders
.biblionumber
1174 LEFT JOIN biblioitems ON biblioitems
.biblionumber
= biblio
.biblionumber
1175 LEFT JOIN aqbudgets ON aqorders
.budget_id
= aqbudgets
.budget_id
,
1176 aqbasket LEFT JOIN borrowers ON aqbasket
.authorisedby
= borrowers
.borrowernumber
1177 LEFT JOIN aqbooksellers ON aqbasket
.booksellerid
= aqbooksellers
.id
1178 WHERE aqorders
.basketno
= aqbasket
.basketno
1181 $dbh->selectall_arrayref( $query, { Slice
=> {} }, $ordernumber );
1183 # result_set assumed to contain 1 match
1184 return $result_set->[0];
1187 =head3 GetLastOrderNotReceivedFromSubscriptionid
1189 $order = &GetLastOrderNotReceivedFromSubscriptionid($subscriptionid);
1191 Returns a reference-to-hash describing the last order not received for a subscription.
1195 sub GetLastOrderNotReceivedFromSubscriptionid
{
1196 my ( $subscriptionid ) = @_;
1197 my $dbh = C4
::Context
->dbh;
1199 SELECT
* FROM aqorders
1200 LEFT JOIN subscription
1201 ON
( aqorders
.subscriptionid
= subscription
.subscriptionid
)
1202 WHERE aqorders
.subscriptionid
= ?
1203 AND aqorders
.datereceived IS NULL
1207 $dbh->selectall_arrayref( $query, { Slice
=> {} }, $subscriptionid );
1209 # result_set assumed to contain 1 match
1210 return $result_set->[0];
1213 =head3 GetLastOrderReceivedFromSubscriptionid
1215 $order = &GetLastOrderReceivedFromSubscriptionid($subscriptionid);
1217 Returns a reference-to-hash describing the last order received for a subscription.
1221 sub GetLastOrderReceivedFromSubscriptionid
{
1222 my ( $subscriptionid ) = @_;
1223 my $dbh = C4
::Context
->dbh;
1225 SELECT
* FROM aqorders
1226 LEFT JOIN subscription
1227 ON
( aqorders
.subscriptionid
= subscription
.subscriptionid
)
1228 WHERE aqorders
.subscriptionid
= ?
1229 AND aqorders
.datereceived
=
1231 SELECT MAX
( aqorders
.datereceived
)
1233 LEFT JOIN subscription
1234 ON
( aqorders
.subscriptionid
= subscription
.subscriptionid
)
1235 WHERE aqorders
.subscriptionid
= ?
1236 AND aqorders
.datereceived IS NOT NULL
1238 ORDER BY ordernumber DESC
1242 $dbh->selectall_arrayref( $query, { Slice
=> {} }, $subscriptionid, $subscriptionid );
1244 # result_set assumed to contain 1 match
1245 return $result_set->[0];
1249 #------------------------------------------------------------#
1253 &ModOrder(\%hashref);
1255 Modifies an existing order. Updates the order with order number
1256 $hashref->{'ordernumber'} and biblionumber $hashref->{'biblionumber'}. All
1257 other keys of the hash update the fields with the same name in the aqorders
1258 table of the Koha database.
1263 my $orderinfo = shift;
1265 die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq '';
1267 my $dbh = C4
::Context
->dbh;
1270 # update uncertainprice to an integer, just in case (under FF, checked boxes have the value "ON" by default)
1271 $orderinfo->{uncertainprice
}=1 if $orderinfo->{uncertainprice
};
1273 # delete($orderinfo->{'branchcode'});
1274 # the hash contains a lot of entries not in aqorders, so get the columns ...
1275 my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;");
1277 my $colnames = $sth->{NAME
};
1278 #FIXME Be careful. If aqorders would have columns with diacritics,
1279 #you should need to decode what you get back from NAME.
1280 #See report 10110 and guided_reports.pl
1281 my $query = "UPDATE aqorders SET ";
1283 foreach my $orderinfokey (grep(!/ordernumber/, keys %$orderinfo)){
1284 # ... and skip hash entries that are not in the aqorders table
1285 # FIXME : probably not the best way to do it (would be better to have a correct hash)
1286 next unless grep(/^$orderinfokey$/, @
$colnames);
1287 $query .= "$orderinfokey=?, ";
1288 push(@params, $orderinfo->{$orderinfokey});
1291 $query .= "timestamp=NOW() WHERE ordernumber=?";
1292 push(@params, $orderinfo->{'ordernumber'} );
1293 $sth = $dbh->prepare($query);
1294 $sth->execute(@params);
1298 #------------------------------------------------------------#
1302 ModItemOrder($itemnumber, $ordernumber);
1304 Modifies the ordernumber of an item in aqorders_items.
1309 my ($itemnumber, $ordernumber) = @_;
1311 return unless ($itemnumber and $ordernumber);
1313 my $dbh = C4
::Context
->dbh;
1315 UPDATE aqorders_items
1317 WHERE itemnumber
= ?
1319 my $sth = $dbh->prepare($query);
1320 return $sth->execute($ordernumber, $itemnumber);
1323 #------------------------------------------------------------#
1325 =head3 ModReceiveOrder
1328 biblionumber => $biblionumber,
1329 ordernumber => $ordernumber,
1330 quantityreceived => $quantityreceived,
1334 invoiceid => $invoiceid,
1336 budget_id => $budget_id,
1337 datereceived => $datereceived,
1338 received_itemnumbers => \@received_itemnumbers,
1339 order_internalnote => $order_internalnote,
1340 order_vendornote => $order_vendornote,
1343 Updates an order, to reflect the fact that it was received, at least
1344 in part. All arguments not mentioned below update the fields with the
1345 same name in the aqorders table of the Koha database.
1347 If a partial order is received, splits the order into two.
1349 Updates the order with bibilionumber C<$biblionumber> and ordernumber
1355 sub ModReceiveOrder
{
1356 my ( $params ) = @_;
1357 my $biblionumber = $params->{biblionumber
};
1358 my $ordernumber = $params->{ordernumber
};
1359 my $quantrec = $params->{quantityreceived
};
1360 my $user = $params->{user
};
1361 my $cost = $params->{cost
};
1362 my $ecost = $params->{ecost
};
1363 my $invoiceid = $params->{invoiceid
};
1364 my $rrp = $params->{rrp
};
1365 my $budget_id = $params->{budget_id
};
1366 my $datereceived = $params->{datereceived
};
1367 my $received_items = $params->{received_items
};
1368 my $order_internalnote = $params->{order_internalnote
};
1369 my $order_vendornote = $params->{order_vendornote
};
1371 my $dbh = C4
::Context
->dbh;
1372 $datereceived = output_pref
(
1374 dt
=> ( $datereceived ? dt_from_string
( $datereceived ) : dt_from_string
),
1375 dateformat
=> 'iso',
1379 my $suggestionid = GetSuggestionFromBiblionumber
( $biblionumber );
1380 if ($suggestionid) {
1381 ModSuggestion
( {suggestionid
=>$suggestionid,
1382 STATUS
=>'AVAILABLE',
1383 biblionumber
=> $biblionumber}
1387 my $result_set = $dbh->selectall_arrayref(
1388 q{SELECT *, aqbasket.is_standing FROM aqorders LEFT JOIN aqbasket USING (basketno) WHERE biblionumber=? AND aqorders.ordernumber=?},
1389 { Slice
=> {} }, $biblionumber, $ordernumber
1392 # we assume we have a unique order
1393 my $order = $result_set->[0];
1395 my $new_ordernumber = $ordernumber;
1396 if ( $order->{is_standing
} || $order->{quantity
} > $quantrec ) {
1397 # Split order line in two parts: the first is the original order line
1398 # without received items (the quantity is decreased),
1399 # the second part is a new order line with quantity=quantityrec
1400 # (entirely received)
1404 orderstatus
= 'partial'|;
1405 $query .= q
|, order_internalnote
= ?
| if defined $order_internalnote;
1406 $query .= q
|, order_vendornote
= ?
| if defined $order_vendornote;
1407 $query .= q
| WHERE ordernumber
= ?
|;
1408 my $sth = $dbh->prepare($query);
1411 ( $order->{is_standing
} ?
1 : ( $order->{quantity
} - $quantrec ) ),
1412 ( defined $order_internalnote ?
$order_internalnote : () ),
1413 ( defined $order_vendornote ?
$order_vendornote : () ),
1417 delete $order->{'ordernumber'};
1418 $order->{'budget_id'} = ( $budget_id || $order->{'budget_id'} );
1419 $order->{'quantity'} = $quantrec;
1420 $order->{'quantityreceived'} = $quantrec;
1421 $order->{'datereceived'} = $datereceived;
1422 $order->{'invoiceid'} = $invoiceid;
1423 $order->{'unitprice'} = $cost;
1424 $order->{'rrp'} = $rrp;
1425 $order->{ecost
} = $ecost;
1426 $order->{'orderstatus'} = 'complete';
1427 $new_ordernumber = Koha
::Acquisition
::Order
->new($order)->insert->{ordernumber
};
1429 if ($received_items) {
1430 foreach my $itemnumber (@
$received_items) {
1431 ModItemOrder
($itemnumber, $new_ordernumber);
1437 set quantityreceived
=?
,datereceived
=?
,invoiceid
=?
,
1438 unitprice
=?
,rrp
=?
,ecost
=?
,budget_id
=?
,orderstatus
='complete'|;
1439 $query .= q
|, order_internalnote
= ?
| if defined $order_internalnote;
1440 $query .= q
|, order_vendornote
= ?
| if defined $order_vendornote;
1441 $query .= q
| where biblionumber
=?
and ordernumber
=?
|;
1442 my $sth = $dbh->prepare( $query );
1450 ( $budget_id ?
$budget_id : $order->{budget_id
} ),
1451 ( defined $order_internalnote ?
$order_internalnote : () ),
1452 ( defined $order_vendornote ?
$order_vendornote : () ),
1457 # All items have been received, sent a notification to users
1458 NotifyOrderUsers
( $ordernumber );
1461 return ($datereceived, $new_ordernumber);
1464 =head3 CancelReceipt
1466 my $parent_ordernumber = CancelReceipt($ordernumber);
1468 Cancel an order line receipt and update the parent order line, as if no
1470 If items are created at receipt (AcqCreateItem = receiving) then delete
1476 my $ordernumber = shift;
1478 return unless $ordernumber;
1480 my $dbh = C4
::Context
->dbh;
1482 SELECT datereceived
, parent_ordernumber
, quantity
1484 WHERE ordernumber
= ?
1486 my $sth = $dbh->prepare($query);
1487 $sth->execute($ordernumber);
1488 my $order = $sth->fetchrow_hashref;
1490 warn "CancelReceipt: order $ordernumber does not exist";
1493 unless($order->{'datereceived'}) {
1494 warn "CancelReceipt: order $ordernumber is not received";
1498 my $parent_ordernumber = $order->{'parent_ordernumber'};
1500 my @itemnumbers = GetItemnumbersFromOrder
( $ordernumber );
1502 if($parent_ordernumber == $ordernumber || not $parent_ordernumber) {
1503 # The order line has no parent, just mark it as not received
1506 SET quantityreceived
= ?
,
1509 orderstatus
= 'ordered'
1510 WHERE ordernumber
= ?
1512 $sth = $dbh->prepare($query);
1513 $sth->execute(0, undef, undef, $ordernumber);
1514 _cancel_items_receipt
( $ordernumber );
1516 # The order line has a parent, increase parent quantity and delete
1519 SELECT quantity
, datereceived
1521 WHERE ordernumber
= ?
1523 $sth = $dbh->prepare($query);
1524 $sth->execute($parent_ordernumber);
1525 my $parent_order = $sth->fetchrow_hashref;
1526 unless($parent_order) {
1527 warn "Parent order $parent_ordernumber does not exist.";
1530 if($parent_order->{'datereceived'}) {
1531 warn "CancelReceipt: parent order is received.".
1532 " Can't cancel receipt.";
1538 orderstatus
= 'ordered'
1539 WHERE ordernumber
= ?
1541 $sth = $dbh->prepare($query);
1542 my $rv = $sth->execute(
1543 $order->{'quantity'} + $parent_order->{'quantity'},
1547 warn "Cannot update parent order line, so do not cancel".
1551 _cancel_items_receipt
( $ordernumber, $parent_ordernumber );
1554 DELETE FROM aqorders
1555 WHERE ordernumber
= ?
1557 $sth = $dbh->prepare($query);
1558 $sth->execute($ordernumber);
1562 if(C4
::Context
->preference('AcqCreateItem') eq 'ordering') {
1563 my @affects = split q{\|}, C4
::Context
->preference("AcqItemSetSubfieldsWhenReceiptIsCancelled");
1565 for my $in ( @itemnumbers ) {
1566 my $biblionumber = C4
::Biblio
::GetBiblionumberFromItemnumber
( $in );
1567 my $frameworkcode = GetFrameworkCode
($biblionumber);
1568 my ( $itemfield ) = GetMarcFromKohaField
( 'items.itemnumber', $frameworkcode );
1569 my $item = C4
::Items
::GetMarcItem
( $biblionumber, $in );
1570 for my $affect ( @affects ) {
1571 my ( $sf, $v ) = split q{=}, $affect, 2;
1572 foreach ( $item->field($itemfield) ) {
1573 $_->update( $sf => $v );
1576 C4
::Items
::ModItemFromMarc
( $item, $biblionumber, $in );
1581 return $parent_ordernumber;
1584 sub _cancel_items_receipt
{
1585 my ( $ordernumber, $parent_ordernumber ) = @_;
1586 $parent_ordernumber ||= $ordernumber;
1588 my @itemnumbers = GetItemnumbersFromOrder
($ordernumber);
1589 if(C4
::Context
->preference('AcqCreateItem') eq 'receiving') {
1590 # Remove items that were created at receipt
1592 DELETE FROM items
, aqorders_items
1593 USING items
, aqorders_items
1594 WHERE items
.itemnumber
= ? AND aqorders_items
.itemnumber
= ?
1596 my $dbh = C4
::Context
->dbh;
1597 my $sth = $dbh->prepare($query);
1598 foreach my $itemnumber (@itemnumbers) {
1599 $sth->execute($itemnumber, $itemnumber);
1603 foreach my $itemnumber (@itemnumbers) {
1604 ModItemOrder
($itemnumber, $parent_ordernumber);
1609 #------------------------------------------------------------#
1613 @results = &SearchOrders({
1614 ordernumber => $ordernumber,
1616 biblionumber => $biblionumber,
1618 booksellerid => $booksellerid,
1619 basketno => $basketno,
1625 Searches for orders.
1627 C<$owner> Finds order for the logged in user.
1628 C<$pending> Finds pending orders. Ignores completed and cancelled orders.
1629 C<$ordered> Finds orders to receive only (status 'ordered' or 'partial').
1632 C<@results> is an array of references-to-hash with the keys are fields
1633 from aqorders, biblio, biblioitems and aqbasket tables.
1638 my ( $params ) = @_;
1639 my $ordernumber = $params->{ordernumber
};
1640 my $search = $params->{search
};
1641 my $ean = $params->{ean
};
1642 my $booksellerid = $params->{booksellerid
};
1643 my $basketno = $params->{basketno
};
1644 my $basketname = $params->{basketname
};
1645 my $basketgroupname = $params->{basketgroupname
};
1646 my $owner = $params->{owner
};
1647 my $pending = $params->{pending
};
1648 my $ordered = $params->{ordered
};
1649 my $biblionumber = $params->{biblionumber
};
1650 my $budget_id = $params->{budget_id
};
1652 my $dbh = C4
::Context
->dbh;
1655 SELECT aqbasket.basketno,
1657 borrowers.firstname,
1660 biblioitems.biblioitemnumber,
1661 aqbasket.authorisedby,
1662 aqbasket.booksellerid,
1664 aqbasket.creationdate,
1665 aqbasket.basketname,
1666 aqbasketgroups.id as basketgroupid,
1667 aqbasketgroups.name as basketgroupname,
1670 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
1671 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
1672 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1673 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1674 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
1677 # If we search on ordernumber, we retrieve the transferred order if a transfer has been done.
1679 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1683 WHERE (datecancellationprinted is NULL)
1686 if ( $pending or $ordered ) {
1689 ( aqbasket.is_standing AND aqorders.orderstatus IN ( "new", "ordered", "partial" ) )
1691 ( quantity > quantityreceived OR quantityreceived is NULL )
1695 $query .= q{ AND aqorders.orderstatus IN ( "ordered", "partial" )};
1703 my $userenv = C4
::Context
->userenv;
1704 if ( C4
::Context
->preference("IndependentBranches") ) {
1705 unless ( C4
::Context
->IsSuperLibrarian() ) {
1708 borrowers.branchcode = ?
1709 OR borrowers.branchcode = ''
1712 push @args, $userenv->{branch
};
1716 if ( $ordernumber ) {
1717 $query .= ' AND ( aqorders.ordernumber = ? OR aqorders_transfers.ordernumber_from = ? ) ';
1718 push @args, ( $ordernumber, $ordernumber );
1720 if ( $biblionumber ) {
1721 $query .= 'AND aqorders.biblionumber = ?';
1722 push @args, $biblionumber;
1725 $query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)';
1726 push @args, ("%$search%","%$search%","%$search%");
1729 $query .= ' AND biblioitems.ean = ?';
1732 if ( $booksellerid ) {
1733 $query .= 'AND aqbasket.booksellerid = ?';
1734 push @args, $booksellerid;
1737 $query .= 'AND aqbasket.basketno = ?';
1738 push @args, $basketno;
1741 $query .= 'AND aqbasket.basketname LIKE ?';
1742 push @args, "%$basketname%";
1744 if( $basketgroupname ) {
1745 $query .= ' AND aqbasketgroups.name LIKE ?';
1746 push @args, "%$basketgroupname%";
1750 $query .= ' AND aqbasket.authorisedby=? ';
1751 push @args, $userenv->{'number'};
1755 $query .= ' AND aqorders.budget_id = ?';
1756 push @args, $budget_id;
1759 $query .= ' ORDER BY aqbasket.basketno';
1761 my $sth = $dbh->prepare($query);
1762 $sth->execute(@args);
1763 return $sth->fetchall_arrayref({});
1766 #------------------------------------------------------------#
1770 &DelOrder($biblionumber, $ordernumber);
1772 Cancel the order with the given order and biblio numbers. It does not
1773 delete any entries in the aqorders table, it merely marks them as
1779 my ( $bibnum, $ordernumber, $delete_biblio, $reason ) = @_;
1782 my $dbh = C4
::Context
->dbh;
1785 SET datecancellationprinted=now(), orderstatus='cancelled'
1788 $query .= ", cancellationreason = ? ";
1791 WHERE biblionumber=? AND ordernumber=?
1793 my $sth = $dbh->prepare($query);
1795 $sth->execute($reason, $bibnum, $ordernumber);
1797 $sth->execute( $bibnum, $ordernumber );
1801 my @itemnumbers = GetItemnumbersFromOrder
( $ordernumber );
1802 foreach my $itemnumber (@itemnumbers){
1803 my $delcheck = C4
::Items
::DelItemCheck
( $bibnum, $itemnumber );
1805 if($delcheck != 1) {
1806 $error->{'delitem'} = 1;
1810 if($delete_biblio) {
1811 # We get the number of remaining items
1812 my $itemcount = C4
::Items
::GetItemsCount
($bibnum);
1814 # If there are no items left,
1815 if ( $itemcount == 0 ) {
1816 # We delete the record
1817 my $delcheck = DelBiblio
($bibnum);
1820 $error->{'delbiblio'} = 1;
1828 =head3 TransferOrder
1830 my $newordernumber = TransferOrder($ordernumber, $basketno);
1832 Transfer an order line to a basket.
1833 Mark $ordernumber as cancelled with an internal note 'Cancelled and transferred
1834 to BOOKSELLER on DATE' and create new order with internal note
1835 'Transferred from BOOKSELLER on DATE'.
1836 Move all attached items to the new order.
1837 Received orders cannot be transferred.
1838 Return the ordernumber of created order.
1843 my ($ordernumber, $basketno) = @_;
1845 return unless ($ordernumber and $basketno);
1847 my $order = GetOrder
( $ordernumber );
1848 return if $order->{datereceived
};
1849 my $basket = GetBasket
($basketno);
1850 return unless $basket;
1852 my $dbh = C4
::Context
->dbh;
1853 my ($query, $sth, $rv);
1857 SET datecancellationprinted = CAST(NOW() AS date), orderstatus = ?
1858 WHERE ordernumber = ?
1860 $sth = $dbh->prepare($query);
1861 $rv = $sth->execute('cancelled', $ordernumber);
1863 delete $order->{'ordernumber'};
1864 delete $order->{parent_ordernumber
};
1865 $order->{'basketno'} = $basketno;
1867 my $newordernumber = Koha
::Acquisition
::Order
->new($order)->insert->{ordernumber
};
1870 UPDATE aqorders_items
1872 WHERE ordernumber = ?
1874 $sth = $dbh->prepare($query);
1875 $sth->execute($newordernumber, $ordernumber);
1878 INSERT INTO aqorders_transfers (ordernumber_from, ordernumber_to)
1881 $sth = $dbh->prepare($query);
1882 $sth->execute($ordernumber, $newordernumber);
1884 return $newordernumber;
1887 =head2 FUNCTIONS ABOUT PARCELS
1891 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
1893 get a lists of parcels.
1900 is the bookseller this function has to get parcels.
1903 To know on what criteria the results list has to be ordered.
1906 is the booksellerinvoicenumber.
1908 =item $datefrom & $dateto
1909 to know on what date this function has to filter its search.
1914 a pointer on a hash list containing parcel informations as such :
1920 =item Last operation
1922 =item Number of biblio
1924 =item Number of items
1931 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
1932 my $dbh = C4
::Context
->dbh;
1933 my @query_params = ();
1935 SELECT aqinvoices.invoicenumber,
1936 datereceived,purchaseordernumber,
1937 count(DISTINCT biblionumber) AS biblio,
1938 sum(quantity) AS itemsexpected,
1939 sum(quantityreceived) AS itemsreceived
1940 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
1941 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
1942 WHERE aqbasket.booksellerid = ? and datereceived IS NOT NULL
1944 push @query_params, $bookseller;
1946 if ( defined $code ) {
1947 $strsth .= ' and aqinvoices.invoicenumber like ? ';
1948 # add a % to the end of the code to allow stemming.
1949 push @query_params, "$code%";
1952 if ( defined $datefrom ) {
1953 $strsth .= ' and datereceived >= ? ';
1954 push @query_params, $datefrom;
1957 if ( defined $dateto ) {
1958 $strsth .= 'and datereceived <= ? ';
1959 push @query_params, $dateto;
1962 $strsth .= "group by aqinvoices.invoicenumber,datereceived ";
1964 # can't use a placeholder to place this column name.
1965 # but, we could probably be checking to make sure it is a column that will be fetched.
1966 $strsth .= "order by $order " if ($order);
1968 my $sth = $dbh->prepare($strsth);
1970 $sth->execute( @query_params );
1971 my $results = $sth->fetchall_arrayref({});
1975 #------------------------------------------------------------#
1977 =head3 GetLateOrders
1979 @results = &GetLateOrders;
1981 Searches for bookseller with late orders.
1984 the table of supplier with late issues. This table is full of hashref.
1990 my $supplierid = shift;
1992 my $estimateddeliverydatefrom = shift;
1993 my $estimateddeliverydateto = shift;
1995 my $dbh = C4
::Context
->dbh;
1997 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
1998 my $dbdriver = C4
::Context
->config("db_scheme") || "mysql";
2000 my @query_params = ();
2002 SELECT aqbasket.basketno,
2003 aqorders.ordernumber,
2004 DATE(aqbasket.closedate) AS orderdate,
2005 aqbasket.basketname AS basketname,
2006 aqbasket.basketgroupid AS basketgroupid,
2007 aqbasketgroups.name AS basketgroupname,
2008 aqorders.rrp AS unitpricesupplier,
2009 aqorders.ecost AS unitpricelib,
2010 aqorders.claims_count AS claims_count,
2011 aqorders.claimed_date AS claimed_date,
2012 aqbudgets.budget_name AS budget,
2013 borrowers.branchcode AS branch,
2014 aqbooksellers.name AS supplier,
2015 aqbooksellers.id AS supplierid,
2016 biblio.author, biblio.title,
2017 biblioitems.publishercode AS publisher,
2018 biblioitems.publicationyear,
2019 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
2023 aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
2024 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
2025 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
2026 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
2027 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
2028 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
2029 WHERE aqorders.basketno = aqbasket.basketno
2030 AND ( datereceived = ''
2031 OR datereceived IS NULL
2032 OR aqorders.quantityreceived < aqorders.quantity
2034 AND aqbasket.closedate IS NOT NULL
2035 AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
2038 if ($dbdriver eq "mysql") {
2040 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity,
2041 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
2042 DATEDIFF(CAST(now() AS date),closedate) AS latesince
2044 if ( defined $delay ) {
2045 $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ;
2046 push @query_params, $delay;
2049 HAVING quantity <> 0
2050 AND unitpricesupplier <> 0
2051 AND unitpricelib <> 0
2054 # FIXME: account for IFNULL as above
2056 aqorders.quantity AS quantity,
2057 aqorders.quantity * aqorders.rrp AS subtotal,
2058 (CAST(now() AS date) - closedate) AS latesince
2060 if ( defined $delay ) {
2061 $from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) ";
2062 push @query_params, $delay;
2065 if (defined $supplierid) {
2066 $from .= ' AND aqbasket.booksellerid = ? ';
2067 push @query_params, $supplierid;
2069 if (defined $branch) {
2070 $from .= ' AND borrowers.branchcode LIKE ? ';
2071 push @query_params, $branch;
2074 if ( defined $estimateddeliverydatefrom or defined $estimateddeliverydateto ) {
2075 $from .= ' AND aqbooksellers.deliverytime IS NOT NULL ';
2077 if ( defined $estimateddeliverydatefrom ) {
2078 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
2079 push @query_params, $estimateddeliverydatefrom;
2081 if ( defined $estimateddeliverydateto ) {
2082 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
2083 push @query_params, $estimateddeliverydateto;
2085 if ( defined $estimateddeliverydatefrom and not defined $estimateddeliverydateto ) {
2086 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
2088 if (C4
::Context
->preference("IndependentBranches")
2089 && !C4
::Context
->IsSuperLibrarian() ) {
2090 $from .= ' AND borrowers.branchcode LIKE ? ';
2091 push @query_params, C4
::Context
->userenv->{branch
};
2093 $from .= " AND orderstatus <> 'cancelled' ";
2094 my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier";
2095 $debug and print STDERR
"GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params);
2096 my $sth = $dbh->prepare($query);
2097 $sth->execute(@query_params);
2099 while (my $data = $sth->fetchrow_hashref) {
2100 push @results, $data;
2105 #------------------------------------------------------------#
2109 \@order_loop = GetHistory( %params );
2111 Retreives some acquisition history information
2121 basket - search both basket name and number
2122 booksellerinvoicenumber
2125 orderstatus (note that orderstatus '' will retrieve orders
2126 of any status except cancelled)
2128 get_canceled_order (if set to a true value, cancelled orders will
2132 $order_loop is a list of hashrefs that each look like this:
2134 'author' => 'Twain, Mark',
2136 'biblionumber' => '215',
2138 'creationdate' => 'MM/DD/YYYY',
2139 'datereceived' => undef,
2142 'invoicenumber' => undef,
2144 'ordernumber' => '1',
2146 'quantityreceived' => undef,
2147 'title' => 'The Adventures of Huckleberry Finn'
2153 # don't run the query if there are no parameters (list would be too long for sure !)
2154 croak
"No search params" unless @_;
2156 my $title = $params{title
};
2157 my $author = $params{author
};
2158 my $isbn = $params{isbn
};
2159 my $ean = $params{ean
};
2160 my $name = $params{name
};
2161 my $from_placed_on = $params{from_placed_on
};
2162 my $to_placed_on = $params{to_placed_on
};
2163 my $basket = $params{basket
};
2164 my $booksellerinvoicenumber = $params{booksellerinvoicenumber
};
2165 my $basketgroupname = $params{basketgroupname
};
2166 my $budget = $params{budget
};
2167 my $orderstatus = $params{orderstatus
};
2168 my $biblionumber = $params{biblionumber
};
2169 my $get_canceled_order = $params{get_canceled_order
} || 0;
2170 my $ordernumber = $params{ordernumber
};
2171 my $search_children_too = $params{search_children_too
} || 0;
2172 my $created_by = $params{created_by
} || [];
2176 my $total_qtyreceived = 0;
2177 my $total_price = 0;
2179 my $dbh = C4
::Context
->dbh;
2182 COALESCE(biblio.title, deletedbiblio.title) AS title,
2183 COALESCE(biblio.author, deletedbiblio.author) AS author,
2184 COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn,
2185 COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean,
2187 aqbasket.basketname,
2188 aqbasket.basketgroupid,
2189 aqbasket.authorisedby,
2190 concat( borrowers.firstname,' ',borrowers.surname) AS authorisedbyname,
2191 aqbasketgroups.name as groupname,
2193 aqbasket.creationdate,
2194 aqorders.datereceived,
2196 aqorders.quantityreceived,
2198 aqorders.ordernumber,
2200 aqinvoices.invoicenumber,
2201 aqbooksellers.id as id,
2202 aqorders.biblionumber,
2203 aqorders.orderstatus,
2204 aqorders.parent_ordernumber,
2205 aqbudgets.budget_name
2207 $query .= ", aqbudgets.budget_id AS budget" if defined $budget;
2210 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
2211 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
2212 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
2213 LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
2214 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
2215 LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id
2216 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2217 LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.biblionumber
2218 LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=aqorders.biblionumber
2219 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2222 $query .= " WHERE 1 ";
2224 unless ($get_canceled_order or (defined $orderstatus and $orderstatus eq 'cancelled')) {
2225 $query .= " AND (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') ";
2228 my @query_params = ();
2230 if ( $biblionumber ) {
2231 $query .= " AND biblio.biblionumber = ?";
2232 push @query_params, $biblionumber;
2236 $query .= " AND biblio.title LIKE ? ";
2237 $title =~ s/\s+/%/g;
2238 push @query_params, "%$title%";
2242 $query .= " AND biblio.author LIKE ? ";
2243 push @query_params, "%$author%";
2247 $query .= " AND biblioitems.isbn LIKE ? ";
2248 push @query_params, "%$isbn%";
2251 $query .= " AND biblioitems.ean = ? ";
2252 push @query_params, "$ean";
2255 $query .= " AND aqbooksellers.name LIKE ? ";
2256 push @query_params, "%$name%";
2260 $query .= " AND aqbudgets.budget_id = ? ";
2261 push @query_params, "$budget";
2264 if ( $from_placed_on ) {
2265 $query .= " AND creationdate >= ? ";
2266 push @query_params, $from_placed_on;
2269 if ( $to_placed_on ) {
2270 $query .= " AND creationdate <= ? ";
2271 push @query_params, $to_placed_on;
2274 if ( defined $orderstatus and $orderstatus ne '') {
2275 $query .= " AND aqorders.orderstatus = ? ";
2276 push @query_params, "$orderstatus";
2280 if ($basket =~ m/^\d+$/) {
2281 $query .= " AND aqorders.basketno = ? ";
2282 push @query_params, $basket;
2284 $query .= " AND aqbasket.basketname LIKE ? ";
2285 push @query_params, "%$basket%";
2289 if ($booksellerinvoicenumber) {
2290 $query .= " AND aqinvoices.invoicenumber LIKE ? ";
2291 push @query_params, "%$booksellerinvoicenumber%";
2294 if ($basketgroupname) {
2295 $query .= " AND aqbasketgroups.name LIKE ? ";
2296 push @query_params, "%$basketgroupname%";
2300 $query .= " AND (aqorders.ordernumber = ? ";
2301 push @query_params, $ordernumber;
2302 if ($search_children_too) {
2303 $query .= " OR aqorders.parent_ordernumber = ? ";
2304 push @query_params, $ordernumber;
2309 if ( @
$created_by ) {
2310 $query .= ' AND aqbasket.authorisedby IN ( ' . join( ',', ('?') x @
$created_by ) . ')';
2311 push @query_params, @
$created_by;
2315 if ( C4
::Context
->preference("IndependentBranches") ) {
2316 unless ( C4
::Context
->IsSuperLibrarian() ) {
2317 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
2318 push @query_params, C4
::Context
->userenv->{branch
};
2321 $query .= " ORDER BY id";
2323 return $dbh->selectall_arrayref( $query, { Slice
=> {} }, @query_params );
2326 =head2 GetRecentAcqui
2328 $results = GetRecentAcqui($days);
2330 C<$results> is a ref to a table which containts hashref
2334 sub GetRecentAcqui
{
2336 my $dbh = C4
::Context
->dbh;
2340 ORDER BY timestamp DESC
2343 my $sth = $dbh->prepare($query);
2345 my $results = $sth->fetchall_arrayref({});
2349 #------------------------------------------------------------#
2353 &AddClaim($ordernumber);
2355 Add a claim for an order
2360 my ($ordernumber) = @_;
2361 my $dbh = C4
::Context
->dbh;
2364 claims_count = claims_count + 1,
2365 claimed_date = CURDATE()
2366 WHERE ordernumber = ?
2368 my $sth = $dbh->prepare($query);
2369 $sth->execute($ordernumber);
2374 my @invoices = GetInvoices(
2375 invoicenumber => $invoicenumber,
2376 supplierid => $supplierid,
2377 suppliername => $suppliername,
2378 shipmentdatefrom => $shipmentdatefrom, # ISO format
2379 shipmentdateto => $shipmentdateto, # ISO format
2380 billingdatefrom => $billingdatefrom, # ISO format
2381 billingdateto => $billingdateto, # ISO format
2382 isbneanissn => $isbn_or_ean_or_issn,
2385 publisher => $publisher,
2386 publicationyear => $publicationyear,
2387 branchcode => $branchcode,
2388 order_by => $order_by
2391 Return a list of invoices that match all given criteria.
2393 $order_by is "column_name (asc|desc)", where column_name is any of
2394 'invoicenumber', 'booksellerid', 'shipmentdate', 'billingdate', 'closedate',
2395 'shipmentcost', 'shipmentcost_budgetid'.
2397 asc is the default if omitted
2404 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2405 closedate shipmentcost shipmentcost_budgetid);
2407 my $dbh = C4
::Context
->dbh;
2409 SELECT aqinvoices
.*, aqbooksellers
.name AS suppliername
,
2412 aqorders
.datereceived IS NOT NULL
,
2413 aqorders
.biblionumber
,
2416 ) AS receivedbiblios
,
2419 aqorders
.subscriptionid IS NOT NULL
,
2420 aqorders
.subscriptionid
,
2423 ) AS is_linked_to_subscriptions
,
2424 SUM
(aqorders
.quantityreceived
) AS receiveditems
2426 LEFT JOIN aqbooksellers ON aqbooksellers
.id
= aqinvoices
.booksellerid
2427 LEFT JOIN aqorders ON aqorders
.invoiceid
= aqinvoices
.invoiceid
2428 LEFT JOIN aqbasket ON aqbasket
.basketno
=aqorders
.basketno
2429 LEFT JOIN borrowers ON aqbasket
.authorisedby
=borrowers
.borrowernumber
2430 LEFT JOIN biblio ON aqorders
.biblionumber
= biblio
.biblionumber
2431 LEFT JOIN biblioitems ON biblio
.biblionumber
= biblioitems
.biblionumber
2432 LEFT JOIN subscription ON biblio
.biblionumber
= subscription
.biblionumber
2437 if($args{supplierid
}) {
2438 push @bind_strs, " aqinvoices.booksellerid = ? ";
2439 push @bind_args, $args{supplierid
};
2441 if($args{invoicenumber
}) {
2442 push @bind_strs, " aqinvoices.invoicenumber LIKE ? ";
2443 push @bind_args, "%$args{invoicenumber}%";
2445 if($args{suppliername
}) {
2446 push @bind_strs, " aqbooksellers.name LIKE ? ";
2447 push @bind_args, "%$args{suppliername}%";
2449 if($args{shipmentdatefrom
}) {
2450 push @bind_strs, " aqinvoices.shipmentdate >= ? ";
2451 push @bind_args, $args{shipmentdatefrom
};
2453 if($args{shipmentdateto
}) {
2454 push @bind_strs, " aqinvoices.shipmentdate <= ? ";
2455 push @bind_args, $args{shipmentdateto
};
2457 if($args{billingdatefrom
}) {
2458 push @bind_strs, " aqinvoices.billingdate >= ? ";
2459 push @bind_args, $args{billingdatefrom
};
2461 if($args{billingdateto
}) {
2462 push @bind_strs, " aqinvoices.billingdate <= ? ";
2463 push @bind_args, $args{billingdateto
};
2465 if($args{isbneanissn
}) {
2466 push @bind_strs, " (biblioitems.isbn LIKE CONCAT('%', ?, '%') OR biblioitems.ean LIKE CONCAT('%', ?, '%') OR biblioitems.issn LIKE CONCAT('%', ?, '%') ) ";
2467 push @bind_args, $args{isbneanissn
}, $args{isbneanissn
}, $args{isbneanissn
};
2470 push @bind_strs, " biblio.title LIKE CONCAT('%', ?, '%') ";
2471 push @bind_args, $args{title
};
2474 push @bind_strs, " biblio.author LIKE CONCAT('%', ?, '%') ";
2475 push @bind_args, $args{author
};
2477 if($args{publisher
}) {
2478 push @bind_strs, " biblioitems.publishercode LIKE CONCAT('%', ?, '%') ";
2479 push @bind_args, $args{publisher
};
2481 if($args{publicationyear
}) {
2482 push @bind_strs, " ((biblioitems.publicationyear LIKE CONCAT('%', ?, '%')) OR (biblio.copyrightdate LIKE CONCAT('%', ?, '%'))) ";
2483 push @bind_args, $args{publicationyear
}, $args{publicationyear
};
2485 if($args{branchcode
}) {
2486 push @bind_strs, " borrowers.branchcode = ? ";
2487 push @bind_args, $args{branchcode
};
2489 if($args{message_id
}) {
2490 push @bind_strs, " aqinvoices.message_id = ? ";
2491 push @bind_args, $args{message_id
};
2494 $query .= " WHERE " . join(" AND ", @bind_strs) if @bind_strs;
2495 $query .= " GROUP BY aqinvoices.invoiceid ";
2497 if($args{order_by
}) {
2498 my ($column, $direction) = split / /, $args{order_by
};
2499 if(grep /^$column$/, @columns) {
2500 $direction ||= 'ASC';
2501 $query .= " ORDER BY $column $direction";
2505 my $sth = $dbh->prepare($query);
2506 $sth->execute(@bind_args);
2508 my $results = $sth->fetchall_arrayref({});
2514 my $invoice = GetInvoice($invoiceid);
2516 Get informations about invoice with given $invoiceid
2518 Return a hash filled with aqinvoices.* fields
2523 my ($invoiceid) = @_;
2526 return unless $invoiceid;
2528 my $dbh = C4
::Context
->dbh;
2534 my $sth = $dbh->prepare($query);
2535 $sth->execute($invoiceid);
2537 $invoice = $sth->fetchrow_hashref;
2541 =head3 GetInvoiceDetails
2543 my $invoice = GetInvoiceDetails($invoiceid)
2545 Return informations about an invoice + the list of related order lines
2547 Orders informations are in $invoice->{orders} (array ref)
2551 sub GetInvoiceDetails
{
2552 my ($invoiceid) = @_;
2554 if ( !defined $invoiceid ) {
2555 carp
'GetInvoiceDetails called without an invoiceid';
2559 my $dbh = C4
::Context
->dbh;
2561 SELECT aqinvoices.*, aqbooksellers.name AS suppliername
2563 LEFT JOIN aqbooksellers ON aqinvoices.booksellerid = aqbooksellers.id
2566 my $sth = $dbh->prepare($query);
2567 $sth->execute($invoiceid);
2569 my $invoice = $sth->fetchrow_hashref;
2574 biblio.copyrightdate,
2575 biblioitems.publishercode,
2576 biblioitems.publicationyear,
2577 aqbasket.basketname,
2578 aqbasketgroups.id AS basketgroupid,
2579 aqbasketgroups.name AS basketgroupname
2581 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
2582 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
2583 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2584 LEFT JOIN biblioitems ON aqorders.biblionumber = biblioitems.biblionumber
2587 $sth = $dbh->prepare($query);
2588 $sth->execute($invoiceid);
2589 $invoice->{orders
} = $sth->fetchall_arrayref({});
2590 $invoice->{orders
} ||= []; # force an empty arrayref if fetchall_arrayref fails
2597 my $invoiceid = AddInvoice(
2598 invoicenumber => $invoicenumber,
2599 booksellerid => $booksellerid,
2600 shipmentdate => $shipmentdate,
2601 billingdate => $billingdate,
2602 closedate => $closedate,
2603 shipmentcost => $shipmentcost,
2604 shipmentcost_budgetid => $shipmentcost_budgetid
2607 Create a new invoice and return its id or undef if it fails.
2614 return unless(%invoice and $invoice{invoicenumber
});
2616 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2617 closedate shipmentcost shipmentcost_budgetid message_id);
2621 foreach my $key (keys %invoice) {
2622 if(0 < grep(/^$key$/, @columns)) {
2623 push @set_strs, "$key = ?";
2624 push @set_args, ($invoice{$key} || undef);
2630 my $dbh = C4
::Context
->dbh;
2631 my $query = "INSERT INTO aqinvoices SET ";
2632 $query .= join (",", @set_strs);
2633 my $sth = $dbh->prepare($query);
2634 $rv = $sth->execute(@set_args);
2636 $rv = $dbh->last_insert_id(undef, undef, 'aqinvoices', undef);
2645 invoiceid => $invoiceid, # Mandatory
2646 invoicenumber => $invoicenumber,
2647 booksellerid => $booksellerid,
2648 shipmentdate => $shipmentdate,
2649 billingdate => $billingdate,
2650 closedate => $closedate,
2651 shipmentcost => $shipmentcost,
2652 shipmentcost_budgetid => $shipmentcost_budgetid
2655 Modify an invoice, invoiceid is mandatory.
2657 Return undef if it fails.
2664 return unless(%invoice and $invoice{invoiceid
});
2666 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2667 closedate shipmentcost shipmentcost_budgetid);
2671 foreach my $key (keys %invoice) {
2672 if(0 < grep(/^$key$/, @columns)) {
2673 push @set_strs, "$key = ?";
2674 push @set_args, ($invoice{$key} || undef);
2678 my $dbh = C4
::Context
->dbh;
2679 my $query = "UPDATE aqinvoices SET ";
2680 $query .= join(",", @set_strs);
2681 $query .= " WHERE invoiceid = ?";
2683 my $sth = $dbh->prepare($query);
2684 $sth->execute(@set_args, $invoice{invoiceid
});
2689 CloseInvoice($invoiceid);
2693 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => undef);
2698 my ($invoiceid) = @_;
2700 return unless $invoiceid;
2702 my $dbh = C4
::Context
->dbh;
2705 SET closedate
= CAST
(NOW
() AS DATE
)
2708 my $sth = $dbh->prepare($query);
2709 $sth->execute($invoiceid);
2712 =head3 ReopenInvoice
2714 ReopenInvoice($invoiceid);
2718 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => output_pref({ dt=>dt_from_string, dateonly=>1, otputpref=>'iso' }))
2723 my ($invoiceid) = @_;
2725 return unless $invoiceid;
2727 my $dbh = C4
::Context
->dbh;
2730 SET closedate
= NULL
2733 my $sth = $dbh->prepare($query);
2734 $sth->execute($invoiceid);
2739 DelInvoice($invoiceid);
2741 Delete an invoice if there are no items attached to it.
2746 my ($invoiceid) = @_;
2748 return unless $invoiceid;
2750 my $dbh = C4
::Context
->dbh;
2756 my $sth = $dbh->prepare($query);
2757 $sth->execute($invoiceid);
2758 my $res = $sth->fetchrow_arrayref;
2759 if ( $res && $res->[0] == 0 ) {
2761 DELETE FROM aqinvoices
2764 my $sth = $dbh->prepare($query);
2765 return ( $sth->execute($invoiceid) > 0 );
2770 =head3 MergeInvoices
2772 MergeInvoices($invoiceid, \@sourceids);
2774 Merge the invoices identified by the IDs in \@sourceids into
2775 the invoice identified by $invoiceid.
2780 my ($invoiceid, $sourceids) = @_;
2782 return unless $invoiceid;
2783 foreach my $sourceid (@
$sourceids) {
2784 next if $sourceid == $invoiceid;
2785 my $source = GetInvoiceDetails
($sourceid);
2786 foreach my $order (@
{$source->{'orders'}}) {
2787 $order->{'invoiceid'} = $invoiceid;
2790 DelInvoice
($source->{'invoiceid'});
2795 =head3 GetBiblioCountByBasketno
2797 $biblio_count = &GetBiblioCountByBasketno($basketno);
2799 Looks up the biblio's count that has basketno value $basketno
2805 sub GetBiblioCountByBasketno
{
2806 my ($basketno) = @_;
2807 my $dbh = C4
::Context
->dbh;
2809 SELECT COUNT( DISTINCT( biblionumber ) )
2812 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
2815 my $sth = $dbh->prepare($query);
2816 $sth->execute($basketno);
2817 return $sth->fetchrow;
2820 # This is *not* the good way to calcul prices
2821 # But it's how it works at the moment into Koha
2822 # This will be fixed later.
2823 # Note this subroutine should be moved to Koha::Acquisition::Order
2824 # Will do when a DBIC decision will be taken.
2825 sub populate_order_with_prices
{
2828 my $order = $params->{order
};
2829 my $booksellerid = $params->{booksellerid
};
2830 return unless $booksellerid;
2832 my $bookseller = Koha
::Acquisition
::Bookseller
->fetch({ id
=> $booksellerid });
2834 my $receiving = $params->{receiving
};
2835 my $ordering = $params->{ordering
};
2836 my $discount = $order->{discount
};
2837 $discount /= 100 if $discount > 1;
2839 $order->{rrp
} = Koha
::Number
::Price
->new( $order->{rrp
} )->round;
2840 $order->{ecost
} = Koha
::Number
::Price
->new( $order->{ecost
} )->round;
2842 if ( $bookseller->{listincgst
} ) {
2843 $order->{rrpgsti
} = $order->{rrp
};
2844 $order->{rrpgste
} = Koha
::Number
::Price
->new(
2845 $order->{rrpgsti
} / ( 1 + $order->{gstrate
} ) )->round;
2846 $order->{ecostgsti
} = $order->{ecost
};
2847 $order->{ecostgste
} = Koha
::Number
::Price
->new(
2848 $order->{ecost
} / ( 1 + $order->{gstrate
} ) )->round;
2849 $order->{gstvalue
} = Koha
::Number
::Price
->new(
2850 ( $order->{ecostgsti
} - $order->{ecostgste
} ) *
2851 $order->{quantity
} )->round;
2852 $order->{totalgste
} = $order->{ecostgste
} * $order->{quantity
};
2853 $order->{totalgsti
} = $order->{ecostgsti
} * $order->{quantity
};
2856 $order->{rrpgste
} = $order->{rrp
};
2857 $order->{rrpgsti
} = Koha
::Number
::Price
->new(
2858 $order->{rrp
} * ( 1 + $order->{gstrate
} ) )->round;
2859 $order->{ecostgste
} = $order->{ecost
};
2860 $order->{ecostgsti
} = Koha
::Number
::Price
->new(
2861 $order->{ecost
} * ( 1 + $order->{gstrate
} ) )->round;
2862 $order->{gstvalue
} = Koha
::Number
::Price
->new(
2863 ( $order->{ecostgsti
} - $order->{ecostgste
} ) *
2864 $order->{quantity
} )->round;
2865 $order->{totalgste
} = $order->{ecostgste
} * $order->{quantity
};
2866 $order->{totalgsti
} = $order->{ecostgsti
} * $order->{quantity
};
2871 if ( $bookseller->{listincgst
} ) {
2872 $order->{unitpricegsti
} = Koha
::Number
::Price
->new( $order->{unitprice
} )->round;
2873 $order->{unitpricegste
} = Koha
::Number
::Price
->new(
2874 $order->{unitpricegsti
} / ( 1 + $order->{gstrate
} ) )->round;
2877 $order->{unitpricegste
} = Koha
::Number
::Price
->new( $order->{unitprice
} )->round;
2878 $order->{unitpricegsti
} = Koha
::Number
::Price
->new(
2879 $order->{unitpricegste
} * ( 1 + $order->{gstrate
} ) )->round;
2881 $order->{gstvalue
} = Koha
::Number
::Price
->new(
2882 ( $order->{unitpricegsti
} - $order->{unitpricegste
} )
2883 * $order->{quantityreceived
} )->round;
2885 $order->{totalgste
} = $order->{unitpricegste
} * $order->{quantity
};
2886 $order->{totalgsti
} = $order->{unitpricegsti
} * $order->{quantity
};
2892 =head3 GetOrderUsers
2894 $order_users_ids = &GetOrderUsers($ordernumber);
2896 Returns a list of all borrowernumbers that are in order users list
2901 my ($ordernumber) = @_;
2903 return unless $ordernumber;
2906 SELECT borrowernumber
2908 WHERE ordernumber
= ?
2910 my $dbh = C4
::Context
->dbh;
2911 my $sth = $dbh->prepare($query);
2912 $sth->execute($ordernumber);
2913 my $results = $sth->fetchall_arrayref( {} );
2915 my @borrowernumbers;
2916 foreach (@
$results) {
2917 push @borrowernumbers, $_->{'borrowernumber'};
2920 return @borrowernumbers;
2923 =head3 ModOrderUsers
2925 my @order_users_ids = (1, 2, 3);
2926 &ModOrderUsers($ordernumber, @basketusers_ids);
2928 Delete all users from order users list, and add users in C<@order_users_ids>
2934 my ( $ordernumber, @order_users_ids ) = @_;
2936 return unless $ordernumber;
2938 my $dbh = C4
::Context
->dbh;
2940 DELETE FROM aqorder_users
2941 WHERE ordernumber
= ?
2943 my $sth = $dbh->prepare($query);
2944 $sth->execute($ordernumber);
2947 INSERT INTO aqorder_users
(ordernumber
, borrowernumber
)
2950 $sth = $dbh->prepare($query);
2951 foreach my $order_user_id (@order_users_ids) {
2952 $sth->execute( $ordernumber, $order_user_id );
2956 sub NotifyOrderUsers
{
2957 my ($ordernumber) = @_;
2959 my @borrowernumbers = GetOrderUsers
($ordernumber);
2960 return unless @borrowernumbers;
2962 my $order = GetOrder
( $ordernumber );
2963 for my $borrowernumber (@borrowernumbers) {
2964 my $borrower = C4
::Members
::GetMember
( borrowernumber
=> $borrowernumber );
2965 my $library = Koha
::Libraries
->find( $borrower->{branchcode
} )->unblessed;
2966 my $biblio = C4
::Biblio
::GetBiblio
( $order->{biblionumber
} );
2967 my $letter = C4
::Letters
::GetPreparedLetter
(
2968 module
=> 'acquisition',
2969 letter_code
=> 'ACQ_NOTIF_ON_RECEIV',
2970 branchcode
=> $library->{branchcode
},
2972 'branches' => $library,
2973 'borrowers' => $borrower,
2974 'biblio' => $biblio,
2975 'aqorders' => $order,
2979 C4
::Letters
::EnqueueLetter
(
2982 borrowernumber
=> $borrowernumber,
2983 LibraryName
=> C4
::Context
->preference("LibraryName"),
2984 message_transport_type
=> 'email',
2986 ) or warn "can't enqueue letter $letter";
2991 =head3 FillWithDefaultValues
2993 FillWithDefaultValues( $marc_record );
2995 This will update the record with default value defined in the ACQ framework.
2996 For all existing fields, if a default value exists and there are no subfield, it will be created.
2997 If the field does not exist, it will be created too.
3001 sub FillWithDefaultValues
{
3003 my $tagslib = C4
::Biblio
::GetMarcStructure
( 1, 'ACQ' );
3006 C4
::Biblio
::GetMarcFromKohaField
( 'items.itemnumber', '' );
3007 for my $tag ( sort keys %$tagslib ) {
3009 next if $tag == $itemfield;
3010 for my $subfield ( sort keys %{ $tagslib->{$tag} } ) {
3011 next if IsMarcStructureInternal
($tagslib->{$tag}{$subfield});
3012 my $defaultvalue = $tagslib->{$tag}{$subfield}{defaultvalue
};
3013 if ( defined $defaultvalue and $defaultvalue ne '' ) {
3014 my @fields = $record->field($tag);
3016 for my $field (@fields) {
3017 unless ( defined $field->subfield($subfield) ) {
3018 $field->add_subfields(
3019 $subfield => $defaultvalue );
3024 $record->insert_fields_ordered(
3026 $tag, '', '', $subfield => $defaultvalue
3041 Koha Development Team <http://koha-community.org/>