C4/Acquisition.pm

   1 package C4::Acquisition;
   2
   3 # Copyright 2000-2002 Katipo Communications
   4 #
   5 # This file is part of Koha.
   6 #
   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.
  11 #
  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.
  16 #
  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>.
  19
  20
  21 use Modern::Perl;
  22 use Carp;
  23 use C4::Context;
  24 use C4::Debug;
  25 use C4::Suggestions;
  26 use C4::Biblio;
  27 use C4::Contract;
  28 use C4::Debug;
  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;
  34 use Koha::Libraries;
  35
  36 use C4::Koha;
  37
  38 use MARC::Field;
  39 use MARC::Record;
  40
  41 use Time::localtime;
  42 use HTML::Entities;
  43
  44 use vars qw(@ISA @EXPORT);
  45
  46 BEGIN {
  47     require Exporter;
  48     @ISA    = qw(Exporter);
  49     @EXPORT = qw(
  50         &GetBasket &NewBasket &CloseBasket &ReopenBasket &DelBasket &ModBasket
  51         &GetBasketAsCSV &GetBasketGroupAsCSV
  52         &GetBasketsByBookseller &GetBasketsByBasketgroup
  53         &GetBasketsInfosByBookseller
  54
  55         &GetBasketUsers &ModBasketUsers
  56         &CanUserManageBasket
  57
  58         &ModBasketHeader
  59
  60         &ModBasketgroup &NewBasketgroup &DelBasketgroup &GetBasketgroup &CloseBasketgroup
  61         &GetBasketgroups &ReOpenBasketgroup
  62
  63         &DelOrder &ModOrder &GetOrder &GetOrders &GetOrdersByBiblionumber
  64         &GetLateOrders &GetOrderFromItemnumber
  65         &SearchOrders &GetHistory &GetRecentAcqui
  66         &ModReceiveOrder &CancelReceipt
  67         &TransferOrder
  68         &GetLastOrderNotReceivedFromSubscriptionid &GetLastOrderReceivedFromSubscriptionid
  69         &ModItemOrder
  70
  71         &GetParcels
  72
  73         &GetInvoices
  74         &GetInvoice
  75         &GetInvoiceDetails
  76         &AddInvoice
  77         &ModInvoice
  78         &CloseInvoice
  79         &ReopenInvoice
  80         &DelInvoice
  81         &MergeInvoices
  82
  83         &GetItemnumbersFromOrder
  84
  85         &AddClaim
  86         &GetBiblioCountByBasketno
  87
  88         &GetOrderUsers
  89         &ModOrderUsers
  90         &NotifyOrderUsers
  91
  92         &FillWithDefaultValues
  93     );
  94 }
  95
  96
  97
  98
  99
 100 sub GetOrderFromItemnumber {
 101     my ($itemnumber) = @_;
 102     my $dbh          = C4::Context->dbh;
 103     my $query        = qq|
 104
 105     SELECT  * from aqorders    LEFT JOIN aqorders_items
 106     ON (     aqorders.ordernumber = aqorders_items.ordernumber   )
 107     WHERE itemnumber = ?  |;
 108
 109     my $sth = $dbh->prepare($query);
 110
 111 #    $sth->trace(3);
 112
 113     $sth->execute($itemnumber);
 114
 115     my $order = $sth->fetchrow_hashref;
 116     return ( $order  );
 117
 118 }
 119
 120 # Returns the itemnumber(s) associated with the ordernumber given in parameter
 121 sub GetItemnumbersFromOrder {
 122     my ($ordernumber) = @_;
 123     my $dbh          = C4::Context->dbh;
 124     my $query        = "SELECT itemnumber FROM aqorders_items WHERE ordernumber=?";
 125     my $sth = $dbh->prepare($query);
 126     $sth->execute($ordernumber);
 127     my @tab;
 128
 129     while (my $order = $sth->fetchrow_hashref) {
 130     push @tab, $order->{'itemnumber'};
 131     }
 132
 133     return @tab;
 134
 135 }
 136
 137
 138
 139
 140
 141
 142 =head1 NAME
 143
 144 C4::Acquisition - Koha functions for dealing with orders and acquisitions
 145
 146 =head1 SYNOPSIS
 147
 148 use C4::Acquisition;
 149
 150 =head1 DESCRIPTION
 151
 152 The functions in this module deal with acquisitions, managing book
 153 orders, basket and parcels.
 154
 155 =head1 FUNCTIONS
 156
 157 =head2 FUNCTIONS ABOUT BASKETS
 158
 159 =head3 GetBasket
 160
 161   $aqbasket = &GetBasket($basketnumber);
 162
 163 get all basket informations in aqbasket for a given basket
 164
 165 B<returns:> informations for a given basket returned as a hashref.
 166
 167 =cut
 168
 169 sub GetBasket {
 170     my ($basketno) = @_;
 171     my $dbh        = C4::Context->dbh;
 172     my $query = "
 173         SELECT  aqbasket.*,
 174                 concat( b.firstname,' ',b.surname) AS authorisedbyname
 175         FROM    aqbasket
 176         LEFT JOIN borrowers b ON aqbasket.authorisedby=b.borrowernumber
 177         WHERE basketno=?
 178     ";
 179     my $sth=$dbh->prepare($query);
 180     $sth->execute($basketno);
 181     my $basket = $sth->fetchrow_hashref;
 182     return ( $basket );
 183 }
 184
 185 #------------------------------------------------------------#
 186
 187 =head3 NewBasket
 188
 189   $basket = &NewBasket( $booksellerid, $authorizedby, $basketname,
 190       $basketnote, $basketbooksellernote, $basketcontractnumber, $deliveryplace, $billingplace );
 191
 192 Create a new basket in aqbasket table
 193
 194 =over
 195
 196 =item C<$booksellerid> is a foreign key in the aqbasket table
 197
 198 =item C<$authorizedby> is the username of who created the basket
 199
 200 =back
 201
 202 The other parameters are optional, see ModBasketHeader for more info on them.
 203
 204 =cut
 205
 206 sub NewBasket {
 207     my ( $booksellerid, $authorisedby, $basketname, $basketnote,
 208         $basketbooksellernote, $basketcontractnumber, $deliveryplace,
 209         $billingplace ) = @_;
 210     my $dbh = C4::Context->dbh;
 211     my $query =
 212         'INSERT INTO aqbasket (creationdate,booksellerid,authorisedby) '
 213       . 'VALUES  (now(),?,?)';
 214     $dbh->do( $query, {}, $booksellerid, $authorisedby );
 215
 216     my $basket = $dbh->{mysql_insertid};
 217     $basketname           ||= q{}; # default to empty strings
 218     $basketnote           ||= q{};
 219     $basketbooksellernote ||= q{};
 220     ModBasketHeader( $basket, $basketname, $basketnote, $basketbooksellernote,
 221         $basketcontractnumber, $booksellerid, $deliveryplace, $billingplace );
 222     return $basket;
 223 }
 224
 225 #------------------------------------------------------------#
 226
 227 =head3 CloseBasket
 228
 229   &CloseBasket($basketno);
 230
 231 close a basket (becomes unmodifiable, except for receives)
 232
 233 =cut
 234
 235 sub CloseBasket {
 236     my ($basketno) = @_;
 237     my $dbh        = C4::Context->dbh;
 238     $dbh->do('UPDATE aqbasket SET closedate=now() WHERE basketno=?', {}, $basketno );
 239
 240     $dbh->do( q{UPDATE aqorders SET orderstatus = 'ordered' WHERE basketno = ? AND orderstatus != 'complete'},
 241         {}, $basketno);
 242     return;
 243 }
 244
 245 =head3 ReopenBasket
 246
 247   &ReopenBasket($basketno);
 248
 249 reopen a basket
 250
 251 =cut
 252
 253 sub ReopenBasket {
 254     my ($basketno) = @_;
 255     my $dbh        = C4::Context->dbh;
 256     $dbh->do( q{UPDATE aqbasket SET closedate=NULL WHERE  basketno=?}, {}, $basketno );
 257
 258     $dbh->do( q{
 259         UPDATE aqorders
 260         SET orderstatus = 'new'
 261         WHERE basketno = ?
 262         AND orderstatus != 'complete'
 263         }, {}, $basketno);
 264     return;
 265 }
 266
 267 #------------------------------------------------------------#
 268
 269 =head3 GetBasketAsCSV
 270
 271   &GetBasketAsCSV($basketno);
 272
 273 Export a basket as CSV
 274
 275 $cgi parameter is needed for column name translation
 276
 277 =cut
 278
 279 sub GetBasketAsCSV {
 280     my ($basketno, $cgi) = @_;
 281     my $basket = GetBasket($basketno);
 282     my @orders = GetOrders($basketno);
 283     my $contract = GetContract({
 284         contractnumber => $basket->{'contractnumber'}
 285     });
 286
 287     my $template = C4::Templates::gettemplate("acqui/csv/basket.tt", "intranet", $cgi);
 288
 289     my @rows;
 290     foreach my $order (@orders) {
 291         my $bd = GetBiblioData( $order->{'biblionumber'} );
 292         my $row = {
 293             contractname => $contract->{'contractname'},
 294             ordernumber => $order->{'ordernumber'},
 295             entrydate => $order->{'entrydate'},
 296             isbn => $order->{'isbn'},
 297             author => $bd->{'author'},
 298             title => $bd->{'title'},
 299             publicationyear => $bd->{'publicationyear'},
 300             publishercode => $bd->{'publishercode'},
 301             collectiontitle => $bd->{'collectiontitle'},
 302             notes => $order->{'order_vendornote'},
 303             quantity => $order->{'quantity'},
 304             rrp => $order->{'rrp'},
 305             deliveryplace => C4::Branch::GetBranchName( $basket->{'deliveryplace'} ),
 306             billingplace => C4::Branch::GetBranchName( $basket->{'billingplace'} ),
 307         };
 308         foreach(qw(
 309             contractname author title publishercode collectiontitle notes
 310             deliveryplace billingplace
 311         ) ) {
 312             # Double the quotes to not be interpreted as a field end
 313             $row->{$_} =~ s/"/""/g if $row->{$_};
 314         }
 315         push @rows, $row;
 316     }
 317
 318     @rows = sort {
 319         if(defined $a->{publishercode} and defined $b->{publishercode}) {
 320             $a->{publishercode} cmp $b->{publishercode};
 321         }
 322     } @rows;
 323
 324     $template->param(rows => \@rows);
 325
 326     return $template->output;
 327 }
 328
 329
 330 =head3 GetBasketGroupAsCSV
 331
 332   &GetBasketGroupAsCSV($basketgroupid);
 333
 334 Export a basket group as CSV
 335
 336 $cgi parameter is needed for column name translation
 337
 338 =cut
 339
 340 sub GetBasketGroupAsCSV {
 341     my ($basketgroupid, $cgi) = @_;
 342     my $baskets = GetBasketsByBasketgroup($basketgroupid);
 343
 344     my $template = C4::Templates::gettemplate('acqui/csv/basketgroup.tt', 'intranet', $cgi);
 345
 346     my @rows;
 347     for my $basket (@$baskets) {
 348         my @orders     = GetOrders( $basket->{basketno} );
 349         my $contract   = GetContract({
 350             contractnumber => $basket->{contractnumber}
 351         });
 352         my $bookseller = Koha::Acquisition::Bookseller->fetch({ id => $basket->{booksellerid} });
 353         my $basketgroup = GetBasketgroup( $$basket{basketgroupid} );
 354
 355         foreach my $order (@orders) {
 356             my $bd = GetBiblioData( $order->{'biblionumber'} );
 357             my $row = {
 358                 clientnumber => $bookseller->{accountnumber},
 359                 basketname => $basket->{basketname},
 360                 ordernumber => $order->{ordernumber},
 361                 author => $bd->{author},
 362                 title => $bd->{title},
 363                 publishercode => $bd->{publishercode},
 364                 publicationyear => $bd->{publicationyear},
 365                 collectiontitle => $bd->{collectiontitle},
 366                 isbn => $order->{isbn},
 367                 quantity => $order->{quantity},
 368                 rrp => $order->{rrp},
 369                 discount => $bookseller->{discount},
 370                 ecost => $order->{ecost},
 371                 notes => $order->{order_vendornote},
 372                 entrydate => $order->{entrydate},
 373                 booksellername => $bookseller->{name},
 374                 bookselleraddress => $bookseller->{address1},
 375                 booksellerpostal => $bookseller->{postal},
 376                 contractnumber => $contract->{contractnumber},
 377                 contractname => $contract->{contractname},
 378                 basketgroupdeliveryplace => C4::Branch::GetBranchName( $basketgroup->{deliveryplace} ),
 379                 basketgroupbillingplace => C4::Branch::GetBranchName( $basketgroup->{billingplace} ),
 380                 basketdeliveryplace => C4::Branch::GetBranchName( $basket->{deliveryplace} ),
 381                 basketbillingplace => C4::Branch::GetBranchName( $basket->{billingplace} ),
 382             };
 383             foreach(qw(
 384                 basketname author title publishercode collectiontitle notes
 385                 booksellername bookselleraddress booksellerpostal contractname
 386                 basketgroupdeliveryplace basketgroupbillingplace
 387                 basketdeliveryplace basketbillingplace
 388             ) ) {
 389                 # Double the quotes to not be interpreted as a field end
 390                 $row->{$_} =~ s/"/""/g if $row->{$_};
 391             }
 392             push @rows, $row;
 393          }
 394      }
 395     $template->param(rows => \@rows);
 396
 397     return $template->output;
 398
 399 }
 400
 401 =head3 CloseBasketgroup
 402
 403   &CloseBasketgroup($basketgroupno);
 404
 405 close a basketgroup
 406
 407 =cut
 408
 409 sub CloseBasketgroup {
 410     my ($basketgroupno) = @_;
 411     my $dbh        = C4::Context->dbh;
 412     my $sth = $dbh->prepare("
 413         UPDATE aqbasketgroups
 414         SET    closed=1
 415         WHERE  id=?
 416     ");
 417     $sth->execute($basketgroupno);
 418 }
 419
 420 #------------------------------------------------------------#
 421
 422 =head3 ReOpenBaskergroup($basketgroupno)
 423
 424   &ReOpenBaskergroup($basketgroupno);
 425
 426 reopen a basketgroup
 427
 428 =cut
 429
 430 sub ReOpenBasketgroup {
 431     my ($basketgroupno) = @_;
 432     my $dbh        = C4::Context->dbh;
 433     my $sth = $dbh->prepare("
 434         UPDATE aqbasketgroups
 435         SET    closed=0
 436         WHERE  id=?
 437     ");
 438     $sth->execute($basketgroupno);
 439 }
 440
 441 #------------------------------------------------------------#
 442
 443
 444 =head3 DelBasket
 445
 446   &DelBasket($basketno);
 447
 448 Deletes the basket that has basketno field $basketno in the aqbasket table.
 449
 450 =over
 451
 452 =item C<$basketno> is the primary key of the basket in the aqbasket table.
 453
 454 =back
 455
 456 =cut
 457
 458 sub DelBasket {
 459     my ( $basketno ) = @_;
 460     my $query = "DELETE FROM aqbasket WHERE basketno=?";
 461     my $dbh = C4::Context->dbh;
 462     my $sth = $dbh->prepare($query);
 463     $sth->execute($basketno);
 464     return;
 465 }
 466
 467 #------------------------------------------------------------#
 468
 469 =head3 ModBasket
 470
 471   &ModBasket($basketinfo);
 472
 473 Modifies a basket, using a hashref $basketinfo for the relevant information, only $basketinfo->{'basketno'} is required.
 474
 475 =over
 476
 477 =item C<$basketno> is the primary key of the basket in the aqbasket table.
 478
 479 =back
 480
 481 =cut
 482
 483 sub ModBasket {
 484     my $basketinfo = shift;
 485     my $query = "UPDATE aqbasket SET ";
 486     my @params;
 487     foreach my $key (keys %$basketinfo){
 488         if ($key ne 'basketno'){
 489             $query .= "$key=?, ";
 490             push(@params, $basketinfo->{$key} || undef );
 491         }
 492     }
 493 # get rid of the "," at the end of $query
 494     if (substr($query, length($query)-2) eq ', '){
 495         chop($query);
 496         chop($query);
 497         $query .= ' ';
 498     }
 499     $query .= "WHERE basketno=?";
 500     push(@params, $basketinfo->{'basketno'});
 501     my $dbh = C4::Context->dbh;
 502     my $sth = $dbh->prepare($query);
 503     $sth->execute(@params);
 504
 505     return;
 506 }
 507
 508 #------------------------------------------------------------#
 509
 510 =head3 ModBasketHeader
 511
 512   &ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid);
 513
 514 Modifies a basket's header.
 515
 516 =over
 517
 518 =item C<$basketno> is the "basketno" field in the "aqbasket" table;
 519
 520 =item C<$basketname> is the "basketname" field in the "aqbasket" table;
 521
 522 =item C<$note> is the "note" field in the "aqbasket" table;
 523
 524 =item C<$booksellernote> is the "booksellernote" field in the "aqbasket" table;
 525
 526 =item C<$contractnumber> is the "contractnumber" (foreign) key in the "aqbasket" table.
 527
 528 =item C<$booksellerid> is the id (foreign) key in the "aqbooksellers" table for the vendor.
 529
 530 =item C<$deliveryplace> is the "deliveryplace" field in the aqbasket table.
 531
 532 =item C<$billingplace> is the "billingplace" field in the aqbasket table.
 533
 534 =back
 535
 536 =cut
 537
 538 sub ModBasketHeader {
 539     my ($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid, $deliveryplace, $billingplace) = @_;
 540     my $query = qq{
 541         UPDATE aqbasket
 542         SET basketname=?, note=?, booksellernote=?, booksellerid=?, deliveryplace=?, billingplace=?
 543         WHERE basketno=?
 544     };
 545
 546     my $dbh = C4::Context->dbh;
 547     my $sth = $dbh->prepare($query);
 548     $sth->execute($basketname, $note, $booksellernote, $booksellerid, $deliveryplace, $billingplace, $basketno);
 549
 550     if ( $contractnumber ) {
 551         my $query2 ="UPDATE aqbasket SET contractnumber=? WHERE basketno=?";
 552         my $sth2 = $dbh->prepare($query2);
 553         $sth2->execute($contractnumber,$basketno);
 554     }
 555     return;
 556 }
 557
 558 #------------------------------------------------------------#
 559
 560 =head3 GetBasketsByBookseller
 561
 562   @results = &GetBasketsByBookseller($booksellerid, $extra);
 563
 564 Returns a list of hashes of all the baskets that belong to bookseller 'booksellerid'.
 565
 566 =over
 567
 568 =item C<$booksellerid> is the 'id' field of the bookseller in the aqbooksellers table
 569
 570 =item C<$extra> is the extra sql parameters, can be
 571
 572  $extra->{groupby}: group baskets by column
 573     ex. $extra->{groupby} = aqbasket.basketgroupid
 574  $extra->{orderby}: order baskets by column
 575  $extra->{limit}: limit number of results (can be helpful for pagination)
 576
 577 =back
 578
 579 =cut
 580
 581 sub GetBasketsByBookseller {
 582     my ($booksellerid, $extra) = @_;
 583     my $query = "SELECT * FROM aqbasket WHERE booksellerid=?";
 584     if ($extra){
 585         if ($extra->{groupby}) {
 586             $query .= " GROUP by $extra->{groupby}";
 587         }
 588         if ($extra->{orderby}){
 589             $query .= " ORDER by $extra->{orderby}";
 590         }
 591         if ($extra->{limit}){
 592             $query .= " LIMIT $extra->{limit}";
 593         }
 594     }
 595     my $dbh = C4::Context->dbh;
 596     my $sth = $dbh->prepare($query);
 597     $sth->execute($booksellerid);
 598     return $sth->fetchall_arrayref({});
 599 }
 600
 601 =head3 GetBasketsInfosByBookseller
 602
 603     my $baskets = GetBasketsInfosByBookseller($supplierid, $allbaskets);
 604
 605 The optional second parameter allbaskets is a boolean allowing you to
 606 select all baskets from the supplier; by default only active baskets (open or
 607 closed but still something to receive) are returned.
 608
 609 Returns in a arrayref of hashref all about booksellers baskets, plus:
 610     total_biblios: Number of distinct biblios in basket
 611     total_items: Number of items in basket
 612     expected_items: Number of non-received items in basket
 613
 614 =cut
 615
 616 sub GetBasketsInfosByBookseller {
 617     my ($supplierid, $allbaskets) = @_;
 618
 619     return unless $supplierid;
 620
 621     my $dbh = C4::Context->dbh;
 622     my $query = q{
 623         SELECT aqbasket.*,
 624           SUM(aqorders.quantity) AS total_items,
 625           SUM(
 626             IF ( aqorders.orderstatus = 'cancelled', aqorders.quantity, 0 )
 627           ) AS total_items_cancelled,
 628           COUNT(DISTINCT aqorders.biblionumber) AS total_biblios,
 629           SUM(
 630             IF(aqorders.datereceived IS NULL
 631               AND aqorders.datecancellationprinted IS NULL
 632             , aqorders.quantity
 633             , 0)
 634           ) AS expected_items
 635         FROM aqbasket
 636           LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
 637         WHERE booksellerid = ?};
 638
 639     unless ( $allbaskets ) {
 640         $query.=" AND (closedate IS NULL OR (aqorders.quantity > aqorders.quantityreceived AND datecancellationprinted IS NULL))";
 641     }
 642     $query.=" GROUP BY aqbasket.basketno";
 643
 644     my $sth = $dbh->prepare($query);
 645     $sth->execute($supplierid);
 646     my $baskets = $sth->fetchall_arrayref({});
 647
 648     # Retrieve the number of biblios cancelled
 649     my $cancelled_biblios = $dbh->selectall_hashref( q|
 650         SELECT COUNT(DISTINCT(biblionumber)) AS total_biblios_cancelled, aqbasket.basketno
 651         FROM aqbasket
 652         LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
 653         WHERE booksellerid = ?
 654         AND aqorders.orderstatus = 'cancelled'
 655         GROUP BY aqbasket.basketno
 656     |, 'basketno', {}, $supplierid );
 657     map {
 658         $_->{total_biblios_cancelled} = $cancelled_biblios->{$_->{basketno}}{total_biblios_cancelled} || 0
 659     } @$baskets;
 660
 661     return $baskets;
 662 }
 663
 664 =head3 GetBasketUsers
 665
 666     $basketusers_ids = &GetBasketUsers($basketno);
 667
 668 Returns a list of all borrowernumbers that are in basket users list
 669
 670 =cut
 671
 672 sub GetBasketUsers {
 673     my $basketno = shift;
 674
 675     return unless $basketno;
 676
 677     my $query = qq{
 678         SELECT borrowernumber
 679         FROM aqbasketusers
 680         WHERE basketno = ?
 681     };
 682     my $dbh = C4::Context->dbh;
 683     my $sth = $dbh->prepare($query);
 684     $sth->execute($basketno);
 685     my $results = $sth->fetchall_arrayref( {} );
 686
 687     my @borrowernumbers;
 688     foreach (@$results) {
 689         push @borrowernumbers, $_->{'borrowernumber'};
 690     }
 691
 692     return @borrowernumbers;
 693 }
 694
 695 =head3 ModBasketUsers
 696
 697     my @basketusers_ids = (1, 2, 3);
 698     &ModBasketUsers($basketno, @basketusers_ids);
 699
 700 Delete all users from basket users list, and add users in C<@basketusers_ids>
 701 to this users list.
 702
 703 =cut
 704
 705 sub ModBasketUsers {
 706     my ($basketno, @basketusers_ids) = @_;
 707
 708     return unless $basketno;
 709
 710     my $dbh = C4::Context->dbh;
 711     my $query = qq{
 712         DELETE FROM aqbasketusers
 713         WHERE basketno = ?
 714     };
 715     my $sth = $dbh->prepare($query);
 716     $sth->execute($basketno);
 717
 718     $query = qq{
 719         INSERT INTO aqbasketusers (basketno, borrowernumber)
 720         VALUES (?, ?)
 721     };
 722     $sth = $dbh->prepare($query);
 723     foreach my $basketuser_id (@basketusers_ids) {
 724         $sth->execute($basketno, $basketuser_id);
 725     }
 726     return;
 727 }
 728
 729 =head3 CanUserManageBasket
 730
 731     my $bool = CanUserManageBasket($borrower, $basket[, $userflags]);
 732     my $bool = CanUserManageBasket($borrowernumber, $basketno[, $userflags]);
 733
 734 Check if a borrower can manage a basket, according to system preference
 735 AcqViewBaskets, user permissions and basket properties (creator, users list,
 736 branch).
 737
 738 First parameter can be either a borrowernumber or a hashref as returned by
 739 C4::Members::GetMember.
 740
 741 Second parameter can be either a basketno or a hashref as returned by
 742 C4::Acquisition::GetBasket.
 743
 744 The third parameter is optional. If given, it should be a hashref as returned
 745 by C4::Auth::getuserflags. If not, getuserflags is called.
 746
 747 If user is authorised to manage basket, returns 1.
 748 Otherwise returns 0.
 749
 750 =cut
 751
 752 sub CanUserManageBasket {
 753     my ($borrower, $basket, $userflags) = @_;
 754
 755     if (!ref $borrower) {
 756         $borrower = C4::Members::GetMember(borrowernumber => $borrower);
 757     }
 758     if (!ref $basket) {
 759         $basket = GetBasket($basket);
 760     }
 761
 762     return 0 unless ($basket and $borrower);
 763
 764     my $borrowernumber = $borrower->{borrowernumber};
 765     my $basketno = $basket->{basketno};
 766
 767     my $AcqViewBaskets = C4::Context->preference('AcqViewBaskets');
 768
 769     if (!defined $userflags) {
 770         my $dbh = C4::Context->dbh;
 771         my $sth = $dbh->prepare("SELECT flags FROM borrowers WHERE borrowernumber = ?");
 772         $sth->execute($borrowernumber);
 773         my ($flags) = $sth->fetchrow_array;
 774         $sth->finish;
 775
 776         $userflags = C4::Auth::getuserflags($flags, $borrower->{userid}, $dbh);
 777     }
 778
 779     unless ($userflags->{superlibrarian}
 780     || (ref $userflags->{acquisition} && $userflags->{acquisition}->{order_manage_all})
 781     || (!ref $userflags->{acquisition} && $userflags->{acquisition}))
 782     {
 783         if (not exists $userflags->{acquisition}) {
 784             return 0;
 785         }
 786
 787         if ( (ref $userflags->{acquisition} && !$userflags->{acquisition}->{order_manage})
 788         || (!ref $userflags->{acquisition} && !$userflags->{acquisition}) ) {
 789             return 0;
 790         }
 791
 792         if ($AcqViewBaskets eq 'user'
 793         && $basket->{authorisedby} != $borrowernumber
 794         && grep($borrowernumber, GetBasketUsers($basketno)) == 0) {
 795             return 0;
 796         }
 797
 798         if ($AcqViewBaskets eq 'branch' && defined $basket->{branch}
 799         && $basket->{branch} ne $borrower->{branchcode}) {
 800             return 0;
 801         }
 802     }
 803
 804     return 1;
 805 }
 806
 807 #------------------------------------------------------------#
 808
 809 =head3 GetBasketsByBasketgroup
 810
 811   $baskets = &GetBasketsByBasketgroup($basketgroupid);
 812
 813 Returns a reference to all baskets that belong to basketgroup $basketgroupid.
 814
 815 =cut
 816
 817 sub GetBasketsByBasketgroup {
 818     my $basketgroupid = shift;
 819     my $query = qq{
 820         SELECT *, aqbasket.booksellerid as booksellerid
 821         FROM aqbasket
 822         LEFT JOIN aqcontract USING(contractnumber) WHERE basketgroupid=?
 823     };
 824     my $dbh = C4::Context->dbh;
 825     my $sth = $dbh->prepare($query);
 826     $sth->execute($basketgroupid);
 827     return $sth->fetchall_arrayref({});
 828 }
 829
 830 #------------------------------------------------------------#
 831
 832 =head3 NewBasketgroup
 833
 834   $basketgroupid = NewBasketgroup(\%hashref);
 835
 836 Adds a basketgroup to the aqbasketgroups table, and add the initial baskets to it.
 837
 838 $hashref->{'booksellerid'} is the 'id' field of the bookseller in the aqbooksellers table,
 839
 840 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
 841
 842 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
 843
 844 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
 845
 846 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
 847
 848 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
 849
 850 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
 851
 852 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
 853
 854 =cut
 855
 856 sub NewBasketgroup {
 857     my $basketgroupinfo = shift;
 858     die "booksellerid is required to create a basketgroup" unless $basketgroupinfo->{'booksellerid'};
 859     my $query = "INSERT INTO aqbasketgroups (";
 860     my @params;
 861     foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
 862         if ( defined $basketgroupinfo->{$field} ) {
 863             $query .= "$field, ";
 864             push(@params, $basketgroupinfo->{$field});
 865         }
 866     }
 867     $query .= "booksellerid) VALUES (";
 868     foreach (@params) {
 869         $query .= "?, ";
 870     }
 871     $query .= "?)";
 872     push(@params, $basketgroupinfo->{'booksellerid'});
 873     my $dbh = C4::Context->dbh;
 874     my $sth = $dbh->prepare($query);
 875     $sth->execute(@params);
 876     my $basketgroupid = $dbh->{'mysql_insertid'};
 877     if( $basketgroupinfo->{'basketlist'} ) {
 878         foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
 879             my $query2 = "UPDATE aqbasket SET basketgroupid=? WHERE basketno=?";
 880             my $sth2 = $dbh->prepare($query2);
 881             $sth2->execute($basketgroupid, $basketno);
 882         }
 883     }
 884     return $basketgroupid;
 885 }
 886
 887 #------------------------------------------------------------#
 888
 889 =head3 ModBasketgroup
 890
 891   ModBasketgroup(\%hashref);
 892
 893 Modifies a basketgroup in the aqbasketgroups table, and add the baskets to it.
 894
 895 $hashref->{'id'} is the 'id' field of the basketgroup in the aqbasketgroup table, this parameter is mandatory,
 896
 897 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
 898
 899 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
 900
 901 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
 902
 903 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
 904
 905 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
 906
 907 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
 908
 909 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
 910
 911 =cut
 912
 913 sub ModBasketgroup {
 914     my $basketgroupinfo = shift;
 915     die "basketgroup id is required to edit a basketgroup" unless $basketgroupinfo->{'id'};
 916     my $dbh = C4::Context->dbh;
 917     my $query = "UPDATE aqbasketgroups SET ";
 918     my @params;
 919     foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
 920         if ( defined $basketgroupinfo->{$field} ) {
 921             $query .= "$field=?, ";
 922             push(@params, $basketgroupinfo->{$field});
 923         }
 924     }
 925     chop($query);
 926     chop($query);
 927     $query .= " WHERE id=?";
 928     push(@params, $basketgroupinfo->{'id'});
 929     my $sth = $dbh->prepare($query);
 930     $sth->execute(@params);
 931
 932     $sth = $dbh->prepare('UPDATE aqbasket SET basketgroupid = NULL WHERE basketgroupid = ?');
 933     $sth->execute($basketgroupinfo->{'id'});
 934
 935     if($basketgroupinfo->{'basketlist'} && @{$basketgroupinfo->{'basketlist'}}){
 936         $sth = $dbh->prepare("UPDATE aqbasket SET basketgroupid=? WHERE basketno=?");
 937         foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
 938             $sth->execute($basketgroupinfo->{'id'}, $basketno);
 939         }
 940     }
 941     return;
 942 }
 943
 944 #------------------------------------------------------------#
 945
 946 =head3 DelBasketgroup
 947
 948   DelBasketgroup($basketgroupid);
 949
 950 Deletes a basketgroup in the aqbasketgroups table, and removes the reference to it from the baskets,
 951
 952 =over
 953
 954 =item C<$basketgroupid> is the 'id' field of the basket in the aqbasketgroup table
 955
 956 =back
 957
 958 =cut
 959
 960 sub DelBasketgroup {
 961     my $basketgroupid = shift;
 962     die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
 963     my $query = "DELETE FROM aqbasketgroups WHERE id=?";
 964     my $dbh = C4::Context->dbh;
 965     my $sth = $dbh->prepare($query);
 966     $sth->execute($basketgroupid);
 967     return;
 968 }
 969
 970 #------------------------------------------------------------#
 971
 972
 973 =head2 FUNCTIONS ABOUT ORDERS
 974
 975 =head3 GetBasketgroup
 976
 977   $basketgroup = &GetBasketgroup($basketgroupid);
 978
 979 Returns a reference to the hash containing all information about the basketgroup.
 980
 981 =cut
 982
 983 sub GetBasketgroup {
 984     my $basketgroupid = shift;
 985     die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
 986     my $dbh = C4::Context->dbh;
 987     my $result_set = $dbh->selectall_arrayref(
 988         'SELECT * FROM aqbasketgroups WHERE id=?',
 989         { Slice => {} },
 990         $basketgroupid
 991     );
 992     return $result_set->[0];    # id is unique
 993 }
 994
 995 #------------------------------------------------------------#
 996
 997 =head3 GetBasketgroups
 998
 999   $basketgroups = &GetBasketgroups($booksellerid);
1000
1001 Returns a reference to the array of all the basketgroups of bookseller $booksellerid.
1002
1003 =cut
1004
1005 sub GetBasketgroups {
1006     my $booksellerid = shift;
1007     die 'bookseller id is required to edit a basketgroup' unless $booksellerid;
1008     my $query = 'SELECT * FROM aqbasketgroups WHERE booksellerid=? ORDER BY id DESC';
1009     my $dbh = C4::Context->dbh;
1010     my $sth = $dbh->prepare($query);
1011     $sth->execute($booksellerid);
1012     return $sth->fetchall_arrayref({});
1013 }
1014
1015 #------------------------------------------------------------#
1016
1017 =head2 FUNCTIONS ABOUT ORDERS
1018
1019 =head3 GetOrders
1020
1021   @orders = &GetOrders( $basketno, { orderby => 'biblio.title', cancelled => 0|1 } );
1022
1023 Looks up the pending (non-cancelled) orders with the given basket
1024 number.
1025
1026 If cancelled is set, only cancelled orders will be returned.
1027
1028 =cut
1029
1030 sub GetOrders {
1031     my ( $basketno, $params ) = @_;
1032
1033     return () unless $basketno;
1034
1035     my $orderby = $params->{orderby};
1036     my $cancelled = $params->{cancelled} || 0;
1037
1038     my $dbh   = C4::Context->dbh;
1039     my $query = q|
1040         SELECT biblio.*,biblioitems.*,
1041                 aqorders.*,
1042                 aqbudgets.*,
1043         |;
1044     $query .= $cancelled
1045       ? q|
1046                 aqorders_transfers.ordernumber_to AS transferred_to,
1047                 aqorders_transfers.timestamp AS transferred_to_timestamp
1048     |
1049       : q|
1050                 aqorders_transfers.ordernumber_from AS transferred_from,
1051                 aqorders_transfers.timestamp AS transferred_from_timestamp
1052     |;
1053     $query .= q|
1054         FROM    aqorders
1055             LEFT JOIN aqbudgets        ON aqbudgets.budget_id = aqorders.budget_id
1056             LEFT JOIN biblio           ON biblio.biblionumber = aqorders.biblionumber
1057             LEFT JOIN biblioitems      ON biblioitems.biblionumber =biblio.biblionumber
1058     |;
1059     $query .= $cancelled
1060       ? q|
1061             LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber
1062     |
1063       : q|
1064             LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1065
1066     |;
1067     $query .= q|
1068         WHERE   basketno=?
1069     |;
1070
1071     if ($cancelled) {
1072         $orderby ||= q|biblioitems.publishercode, biblio.title|;
1073         $query .= q|
1074             AND (datecancellationprinted IS NOT NULL
1075                AND datecancellationprinted <> '0000-00-00')
1076         |;
1077     }
1078     else {
1079         $orderby ||=
1080           q|aqorders.datecancellationprinted desc, aqorders.timestamp desc|;
1081         $query .= q|
1082             AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
1083         |;
1084     }
1085
1086     $query .= " ORDER BY $orderby";
1087     my $orders =
1088       $dbh->selectall_arrayref( $query, { Slice => {} }, $basketno );
1089     return @{$orders};
1090
1091 }
1092
1093 #------------------------------------------------------------#
1094
1095 =head3 GetOrdersByBiblionumber
1096
1097   @orders = &GetOrdersByBiblionumber($biblionumber);
1098
1099 Looks up the orders with linked to a specific $biblionumber, including
1100 cancelled orders and received orders.
1101
1102 return :
1103 C<@orders> is an array of references-to-hash, whose keys are the
1104 fields from the aqorders, biblio, and biblioitems tables in the Koha database.
1105
1106 =cut
1107
1108 sub GetOrdersByBiblionumber {
1109     my $biblionumber = shift;
1110     return unless $biblionumber;
1111     my $dbh   = C4::Context->dbh;
1112     my $query  ="
1113         SELECT biblio.*,biblioitems.*,
1114                 aqorders.*,
1115                 aqbudgets.*
1116         FROM    aqorders
1117             LEFT JOIN aqbudgets        ON aqbudgets.budget_id = aqorders.budget_id
1118             LEFT JOIN biblio           ON biblio.biblionumber = aqorders.biblionumber
1119             LEFT JOIN biblioitems      ON biblioitems.biblionumber =biblio.biblionumber
1120         WHERE   aqorders.biblionumber=?
1121     ";
1122     my $result_set =
1123       $dbh->selectall_arrayref( $query, { Slice => {} }, $biblionumber );
1124     return @{$result_set};
1125
1126 }
1127
1128 #------------------------------------------------------------#
1129
1130 =head3 GetOrder
1131
1132   $order = &GetOrder($ordernumber);
1133
1134 Looks up an order by order number.
1135
1136 Returns a reference-to-hash describing the order. The keys of
1137 C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database.
1138
1139 =cut
1140
1141 sub GetOrder {
1142     my ($ordernumber) = @_;
1143     return unless $ordernumber;
1144
1145     my $dbh      = C4::Context->dbh;
1146     my $query = qq{SELECT
1147                 aqorders.*,
1148                 biblio.title,
1149                 biblio.author,
1150                 aqbasket.basketname,
1151                 borrowers.branchcode,
1152                 biblioitems.publicationyear,
1153                 biblio.copyrightdate,
1154                 biblioitems.editionstatement,
1155                 biblioitems.isbn,
1156                 biblioitems.ean,
1157                 biblio.seriestitle,
1158                 biblioitems.publishercode,
1159                 aqorders.rrp              AS unitpricesupplier,
1160                 aqorders.ecost            AS unitpricelib,
1161                 aqorders.claims_count     AS claims_count,
1162                 aqorders.claimed_date     AS claimed_date,
1163                 aqbudgets.budget_name     AS budget,
1164                 aqbooksellers.name        AS supplier,
1165                 aqbooksellers.id          AS supplierid,
1166                 biblioitems.publishercode AS publisher,
1167                 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
1168                 DATE(aqbasket.closedate)  AS orderdate,
1169                 aqorders.quantity - COALESCE(aqorders.quantityreceived,0)                 AS quantity_to_receive,
1170                 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1171                 DATEDIFF(CURDATE( ),closedate) AS latesince
1172                 FROM aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1173                 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1174                 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
1175                 aqbasket LEFT JOIN borrowers  ON aqbasket.authorisedby = borrowers.borrowernumber
1176                 LEFT JOIN aqbooksellers       ON aqbasket.booksellerid = aqbooksellers.id
1177                 WHERE aqorders.basketno = aqbasket.basketno
1178                     AND ordernumber=?};
1179     my $result_set =
1180       $dbh->selectall_arrayref( $query, { Slice => {} }, $ordernumber );
1181
1182     # result_set assumed to contain 1 match
1183     return $result_set->[0];
1184 }
1185
1186 =head3 GetLastOrderNotReceivedFromSubscriptionid
1187
1188   $order = &GetLastOrderNotReceivedFromSubscriptionid($subscriptionid);
1189
1190 Returns a reference-to-hash describing the last order not received for a subscription.
1191
1192 =cut
1193
1194 sub GetLastOrderNotReceivedFromSubscriptionid {
1195     my ( $subscriptionid ) = @_;
1196     my $dbh                = C4::Context->dbh;
1197     my $query              = qq|
1198         SELECT * FROM aqorders
1199         LEFT JOIN subscription
1200             ON ( aqorders.subscriptionid = subscription.subscriptionid )
1201         WHERE aqorders.subscriptionid = ?
1202             AND aqorders.datereceived IS NULL
1203         LIMIT 1
1204     |;
1205     my $result_set =
1206       $dbh->selectall_arrayref( $query, { Slice => {} }, $subscriptionid );
1207
1208     # result_set assumed to contain 1 match
1209     return $result_set->[0];
1210 }
1211
1212 =head3 GetLastOrderReceivedFromSubscriptionid
1213
1214   $order = &GetLastOrderReceivedFromSubscriptionid($subscriptionid);
1215
1216 Returns a reference-to-hash describing the last order received for a subscription.
1217
1218 =cut
1219
1220 sub GetLastOrderReceivedFromSubscriptionid {
1221     my ( $subscriptionid ) = @_;
1222     my $dbh                = C4::Context->dbh;
1223     my $query              = qq|
1224         SELECT * FROM aqorders
1225         LEFT JOIN subscription
1226             ON ( aqorders.subscriptionid = subscription.subscriptionid )
1227         WHERE aqorders.subscriptionid = ?
1228             AND aqorders.datereceived =
1229                 (
1230                     SELECT MAX( aqorders.datereceived )
1231                     FROM aqorders
1232                     LEFT JOIN subscription
1233                         ON ( aqorders.subscriptionid = subscription.subscriptionid )
1234                         WHERE aqorders.subscriptionid = ?
1235                             AND aqorders.datereceived IS NOT NULL
1236                 )
1237         ORDER BY ordernumber DESC
1238         LIMIT 1
1239     |;
1240     my $result_set =
1241       $dbh->selectall_arrayref( $query, { Slice => {} }, $subscriptionid, $subscriptionid );
1242
1243     # result_set assumed to contain 1 match
1244     return $result_set->[0];
1245
1246 }
1247
1248 #------------------------------------------------------------#
1249
1250 =head3 ModOrder
1251
1252   &ModOrder(\%hashref);
1253
1254 Modifies an existing order. Updates the order with order number
1255 $hashref->{'ordernumber'} and biblionumber $hashref->{'biblionumber'}. All
1256 other keys of the hash update the fields with the same name in the aqorders
1257 table of the Koha database.
1258
1259 =cut
1260
1261 sub ModOrder {
1262     my $orderinfo = shift;
1263
1264     die "Ordernumber is required"     if $orderinfo->{'ordernumber'} eq  '' ;
1265     die "Biblionumber is required"  if  $orderinfo->{'biblionumber'} eq '';
1266
1267     my $dbh = C4::Context->dbh;
1268     my @params;
1269
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};
1272
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;");
1276     $sth->execute;
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 ";
1282
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});
1289     }
1290
1291     $query .= "timestamp=NOW()  WHERE  ordernumber=?";
1292     push(@params, $orderinfo->{'ordernumber'} );
1293     $sth = $dbh->prepare($query);
1294     $sth->execute(@params);
1295     return;
1296 }
1297
1298 #------------------------------------------------------------#
1299
1300 =head3 ModItemOrder
1301
1302     ModItemOrder($itemnumber, $ordernumber);
1303
1304 Modifies the ordernumber of an item in aqorders_items.
1305
1306 =cut
1307
1308 sub ModItemOrder {
1309     my ($itemnumber, $ordernumber) = @_;
1310
1311     return unless ($itemnumber and $ordernumber);
1312
1313     my $dbh = C4::Context->dbh;
1314     my $query = qq{
1315         UPDATE aqorders_items
1316         SET ordernumber = ?
1317         WHERE itemnumber = ?
1318     };
1319     my $sth = $dbh->prepare($query);
1320     return $sth->execute($ordernumber, $itemnumber);
1321 }
1322
1323 #------------------------------------------------------------#
1324
1325 =head3 ModReceiveOrder
1326
1327   &ModReceiveOrder({
1328     biblionumber => $biblionumber,
1329     ordernumber => $ordernumber,
1330     quantityreceived => $quantityreceived,
1331     user => $user,
1332     cost => $cost,
1333     ecost => $ecost,
1334     invoiceid => $invoiceid,
1335     rrp => $rrp,
1336     budget_id => $budget_id,
1337     datereceived => $datereceived,
1338     received_itemnumbers => \@received_itemnumbers,
1339     order_internalnote => $order_internalnote,
1340     order_vendornote => $order_vendornote,
1341    });
1342
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.
1346
1347 If a partial order is received, splits the order into two.
1348
1349 Updates the order with bibilionumber C<$biblionumber> and ordernumber
1350 C<$ordernumber>.
1351
1352 =cut
1353
1354
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};
1370
1371     my $dbh = C4::Context->dbh;
1372     $datereceived = output_pref(
1373         {
1374             dt => ( $datereceived ? dt_from_string( $datereceived ) : dt_from_string ),
1375             dateformat => 'iso',
1376             dateonly => 1,
1377         }
1378     );
1379     my $suggestionid = GetSuggestionFromBiblionumber( $biblionumber );
1380     if ($suggestionid) {
1381         ModSuggestion( {suggestionid=>$suggestionid,
1382                         STATUS=>'AVAILABLE',
1383                         biblionumber=> $biblionumber}
1384                         );
1385     }
1386
1387     my $result_set = $dbh->selectall_arrayref(
1388 q{SELECT * FROM aqorders WHERE biblionumber=? AND aqorders.ordernumber=?},
1389         { Slice => {} }, $biblionumber, $ordernumber
1390     );
1391
1392     # we assume we have a unique order
1393     my $order = $result_set->[0];
1394
1395     my $new_ordernumber = $ordernumber;
1396     if ( $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)
1401         my $query = q|
1402             UPDATE aqorders
1403             SET quantity = ?,
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);
1409
1410         $sth->execute(
1411             $order->{quantity} - $quantrec,
1412             ( defined $order_internalnote ? $order_internalnote : () ),
1413             ( defined $order_vendornote ? $order_vendornote : () ),
1414             $ordernumber
1415         );
1416
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};
1428
1429         if ($received_items) {
1430             foreach my $itemnumber (@$received_items) {
1431                 ModItemOrder($itemnumber, $new_ordernumber);
1432             }
1433         }
1434     } else {
1435         my $query = q|
1436             update aqorders
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 );
1443         $sth->execute(
1444             $quantrec,
1445             $datereceived,
1446             $invoiceid,
1447             $cost,
1448             $rrp,
1449             $ecost,
1450             ( $budget_id ? $budget_id : $order->{budget_id} ),
1451             ( defined $order_internalnote ? $order_internalnote : () ),
1452             ( defined $order_vendornote ? $order_vendornote : () ),
1453             $biblionumber,
1454             $ordernumber
1455         );
1456
1457         # All items have been received, sent a notification to users
1458         NotifyOrderUsers( $ordernumber );
1459
1460     }
1461     return ($datereceived, $new_ordernumber);
1462 }
1463
1464 =head3 CancelReceipt
1465
1466     my $parent_ordernumber = CancelReceipt($ordernumber);
1467
1468     Cancel an order line receipt and update the parent order line, as if no
1469     receipt was made.
1470     If items are created at receipt (AcqCreateItem = receiving) then delete
1471     these items.
1472
1473 =cut
1474
1475 sub CancelReceipt {
1476     my $ordernumber = shift;
1477
1478     return unless $ordernumber;
1479
1480     my $dbh = C4::Context->dbh;
1481     my $query = qq{
1482         SELECT datereceived, parent_ordernumber, quantity
1483         FROM aqorders
1484         WHERE ordernumber = ?
1485     };
1486     my $sth = $dbh->prepare($query);
1487     $sth->execute($ordernumber);
1488     my $order = $sth->fetchrow_hashref;
1489     unless($order) {
1490         warn "CancelReceipt: order $ordernumber does not exist";
1491         return;
1492     }
1493     unless($order->{'datereceived'}) {
1494         warn "CancelReceipt: order $ordernumber is not received";
1495         return;
1496     }
1497
1498     my $parent_ordernumber = $order->{'parent_ordernumber'};
1499
1500     my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1501
1502     if($parent_ordernumber == $ordernumber || not $parent_ordernumber) {
1503         # The order line has no parent, just mark it as not received
1504         $query = qq{
1505             UPDATE aqorders
1506             SET quantityreceived = ?,
1507                 datereceived = ?,
1508                 invoiceid = ?,
1509                 orderstatus = 'ordered'
1510             WHERE ordernumber = ?
1511         };
1512         $sth = $dbh->prepare($query);
1513         $sth->execute(0, undef, undef, $ordernumber);
1514         _cancel_items_receipt( $ordernumber );
1515     } else {
1516         # The order line has a parent, increase parent quantity and delete
1517         # the order line.
1518         $query = qq{
1519             SELECT quantity, datereceived
1520             FROM aqorders
1521             WHERE ordernumber = ?
1522         };
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.";
1528             return;
1529         }
1530         if($parent_order->{'datereceived'}) {
1531             warn "CancelReceipt: parent order is received.".
1532                 " Can't cancel receipt.";
1533             return;
1534         }
1535         $query = qq{
1536             UPDATE aqorders
1537             SET quantity = ?,
1538                 orderstatus = 'ordered'
1539             WHERE ordernumber = ?
1540         };
1541         $sth = $dbh->prepare($query);
1542         my $rv = $sth->execute(
1543             $order->{'quantity'} + $parent_order->{'quantity'},
1544             $parent_ordernumber
1545         );
1546         unless($rv) {
1547             warn "Cannot update parent order line, so do not cancel".
1548                 " receipt";
1549             return;
1550         }
1551         _cancel_items_receipt( $ordernumber, $parent_ordernumber );
1552         # Delete order line
1553         $query = qq{
1554             DELETE FROM aqorders
1555             WHERE ordernumber = ?
1556         };
1557         $sth = $dbh->prepare($query);
1558         $sth->execute($ordernumber);
1559
1560     }
1561
1562     if(C4::Context->preference('AcqCreateItem') eq 'ordering') {
1563         my @affects = split q{\|}, C4::Context->preference("AcqItemSetSubfieldsWhenReceiptIsCancelled");
1564         if ( @affects ) {
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 );
1574                     }
1575                 }
1576                 C4::Items::ModItemFromMarc( $item, $biblionumber, $in );
1577             }
1578         }
1579     }
1580
1581     return $parent_ordernumber;
1582 }
1583
1584 sub _cancel_items_receipt {
1585     my ( $ordernumber, $parent_ordernumber ) = @_;
1586     $parent_ordernumber ||= $ordernumber;
1587
1588     my @itemnumbers = GetItemnumbersFromOrder($ordernumber);
1589     if(C4::Context->preference('AcqCreateItem') eq 'receiving') {
1590         # Remove items that were created at receipt
1591         my $query = qq{
1592             DELETE FROM items, aqorders_items
1593             USING items, aqorders_items
1594             WHERE items.itemnumber = ? AND aqorders_items.itemnumber = ?
1595         };
1596         my $dbh = C4::Context->dbh;
1597         my $sth = $dbh->prepare($query);
1598         foreach my $itemnumber (@itemnumbers) {
1599             $sth->execute($itemnumber, $itemnumber);
1600         }
1601     } else {
1602         # Update items
1603         foreach my $itemnumber (@itemnumbers) {
1604             ModItemOrder($itemnumber, $parent_ordernumber);
1605         }
1606     }
1607 }
1608
1609 #------------------------------------------------------------#
1610
1611 =head3 SearchOrders
1612
1613 @results = &SearchOrders({
1614     ordernumber => $ordernumber,
1615     search => $search,
1616     biblionumber => $biblionumber,
1617     ean => $ean,
1618     booksellerid => $booksellerid,
1619     basketno => $basketno,
1620     owner => $owner,
1621     pending => $pending
1622     ordered => $ordered
1623 });
1624
1625 Searches for orders.
1626
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').
1630
1631
1632 C<@results> is an array of references-to-hash with the keys are fields
1633 from aqorders, biblio, biblioitems and aqbasket tables.
1634
1635 =cut
1636
1637 sub SearchOrders {
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};
1651
1652     my $dbh = C4::Context->dbh;
1653     my @args = ();
1654     my $query = q{
1655         SELECT aqbasket.basketno,
1656                borrowers.surname,
1657                borrowers.firstname,
1658                biblio.*,
1659                biblioitems.isbn,
1660                biblioitems.biblioitemnumber,
1661                aqbasket.authorisedby,
1662                aqbasket.booksellerid,
1663                aqbasket.closedate,
1664                aqbasket.creationdate,
1665                aqbasket.basketname,
1666                aqbasketgroups.id as basketgroupid,
1667                aqbasketgroups.name as basketgroupname,
1668                aqorders.*
1669         FROM aqorders
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
1675     };
1676
1677     # If we search on ordernumber, we retrieve the transferred order if a transfer has been done.
1678     $query .= q{
1679             LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1680     } if $ordernumber;
1681
1682     $query .= q{
1683         WHERE (datecancellationprinted is NULL)
1684     };
1685
1686     if ( $pending or $ordered ) {
1687         $query .= q{ AND (quantity > quantityreceived OR quantityreceived is NULL)};
1688     }
1689     if ( $ordered ) {
1690         $query .= q{ AND aqorders.orderstatus IN ( "ordered", "partial" )};
1691     }
1692
1693     my $userenv = C4::Context->userenv;
1694     if ( C4::Context->preference("IndependentBranches") ) {
1695         unless ( C4::Context->IsSuperLibrarian() ) {
1696             $query .= q{
1697                 AND (
1698                     borrowers.branchcode = ?
1699                     OR borrowers.branchcode  = ''
1700                 )
1701             };
1702             push @args, $userenv->{branch};
1703         }
1704     }
1705
1706     if ( $ordernumber ) {
1707         $query .= ' AND ( aqorders.ordernumber = ? OR aqorders_transfers.ordernumber_from = ? ) ';
1708         push @args, ( $ordernumber, $ordernumber );
1709     }
1710     if ( $biblionumber ) {
1711         $query .= 'AND aqorders.biblionumber = ?';
1712         push @args, $biblionumber;
1713     }
1714     if( $search ) {
1715         $query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)';
1716         push @args, ("%$search%","%$search%","%$search%");
1717     }
1718     if ( $ean ) {
1719         $query .= ' AND biblioitems.ean = ?';
1720         push @args, $ean;
1721     }
1722     if ( $booksellerid ) {
1723         $query .= 'AND aqbasket.booksellerid = ?';
1724         push @args, $booksellerid;
1725     }
1726     if( $basketno ) {
1727         $query .= 'AND aqbasket.basketno = ?';
1728         push @args, $basketno;
1729     }
1730     if( $basketname ) {
1731         $query .= 'AND aqbasket.basketname LIKE ?';
1732         push @args, "%$basketname%";
1733     }
1734     if( $basketgroupname ) {
1735         $query .= ' AND aqbasketgroups.name LIKE ?';
1736         push @args, "%$basketgroupname%";
1737     }
1738
1739     if ( $owner ) {
1740         $query .= ' AND aqbasket.authorisedby=? ';
1741         push @args, $userenv->{'number'};
1742     }
1743
1744     if ( $budget_id ) {
1745         $query .= ' AND aqorders.budget_id = ?';
1746         push @args, $budget_id;
1747     }
1748
1749     $query .= ' ORDER BY aqbasket.basketno';
1750
1751     my $sth = $dbh->prepare($query);
1752     $sth->execute(@args);
1753     return $sth->fetchall_arrayref({});
1754 }
1755
1756 #------------------------------------------------------------#
1757
1758 =head3 DelOrder
1759
1760   &DelOrder($biblionumber, $ordernumber);
1761
1762 Cancel the order with the given order and biblio numbers. It does not
1763 delete any entries in the aqorders table, it merely marks them as
1764 cancelled.
1765
1766 =cut
1767
1768 sub DelOrder {
1769     my ( $bibnum, $ordernumber, $delete_biblio, $reason ) = @_;
1770
1771     my $error;
1772     my $dbh = C4::Context->dbh;
1773     my $query = "
1774         UPDATE aqorders
1775         SET    datecancellationprinted=now(), orderstatus='cancelled'
1776     ";
1777     if($reason) {
1778         $query .= ", cancellationreason = ? ";
1779     }
1780     $query .= "
1781         WHERE biblionumber=? AND ordernumber=?
1782     ";
1783     my $sth = $dbh->prepare($query);
1784     if($reason) {
1785         $sth->execute($reason, $bibnum, $ordernumber);
1786     } else {
1787         $sth->execute( $bibnum, $ordernumber );
1788     }
1789     $sth->finish;
1790
1791     my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1792     foreach my $itemnumber (@itemnumbers){
1793         my $delcheck = C4::Items::DelItemCheck( $dbh, $bibnum, $itemnumber );
1794
1795         if($delcheck != 1) {
1796             $error->{'delitem'} = 1;
1797         }
1798     }
1799
1800     if($delete_biblio) {
1801         # We get the number of remaining items
1802         my $itemcount = C4::Items::GetItemsCount($bibnum);
1803
1804         # If there are no items left,
1805         if ( $itemcount == 0 ) {
1806             # We delete the record
1807             my $delcheck = DelBiblio($bibnum);
1808
1809             if($delcheck) {
1810                 $error->{'delbiblio'} = 1;
1811             }
1812         }
1813     }
1814
1815     return $error;
1816 }
1817
1818 =head3 TransferOrder
1819
1820     my $newordernumber = TransferOrder($ordernumber, $basketno);
1821
1822 Transfer an order line to a basket.
1823 Mark $ordernumber as cancelled with an internal note 'Cancelled and transferred
1824 to BOOKSELLER on DATE' and create new order with internal note
1825 'Transferred from BOOKSELLER on DATE'.
1826 Move all attached items to the new order.
1827 Received orders cannot be transferred.
1828 Return the ordernumber of created order.
1829
1830 =cut
1831
1832 sub TransferOrder {
1833     my ($ordernumber, $basketno) = @_;
1834
1835     return unless ($ordernumber and $basketno);
1836
1837     my $order = GetOrder( $ordernumber );
1838     return if $order->{datereceived};
1839     my $basket = GetBasket($basketno);
1840     return unless $basket;
1841
1842     my $dbh = C4::Context->dbh;
1843     my ($query, $sth, $rv);
1844
1845     $query = q{
1846         UPDATE aqorders
1847         SET datecancellationprinted = CAST(NOW() AS date), orderstatus = ?
1848         WHERE ordernumber = ?
1849     };
1850     $sth = $dbh->prepare($query);
1851     $rv = $sth->execute('cancelled', $ordernumber);
1852
1853     delete $order->{'ordernumber'};
1854     delete $order->{parent_ordernumber};
1855     $order->{'basketno'} = $basketno;
1856
1857     my $newordernumber = Koha::Acquisition::Order->new($order)->insert->{ordernumber};
1858
1859     $query = q{
1860         UPDATE aqorders_items
1861         SET ordernumber = ?
1862         WHERE ordernumber = ?
1863     };
1864     $sth = $dbh->prepare($query);
1865     $sth->execute($newordernumber, $ordernumber);
1866
1867     $query = q{
1868         INSERT INTO aqorders_transfers (ordernumber_from, ordernumber_to)
1869         VALUES (?, ?)
1870     };
1871     $sth = $dbh->prepare($query);
1872     $sth->execute($ordernumber, $newordernumber);
1873
1874     return $newordernumber;
1875 }
1876
1877 =head2 FUNCTIONS ABOUT PARCELS
1878
1879 =head3 GetParcels
1880
1881   $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
1882
1883 get a lists of parcels.
1884
1885 * Input arg :
1886
1887 =over
1888
1889 =item $bookseller
1890 is the bookseller this function has to get parcels.
1891
1892 =item $order
1893 To know on what criteria the results list has to be ordered.
1894
1895 =item $code
1896 is the booksellerinvoicenumber.
1897
1898 =item $datefrom & $dateto
1899 to know on what date this function has to filter its search.
1900
1901 =back
1902
1903 * return:
1904 a pointer on a hash list containing parcel informations as such :
1905
1906 =over
1907
1908 =item Creation date
1909
1910 =item Last operation
1911
1912 =item Number of biblio
1913
1914 =item Number of items
1915
1916 =back
1917
1918 =cut
1919
1920 sub GetParcels {
1921     my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
1922     my $dbh    = C4::Context->dbh;
1923     my @query_params = ();
1924     my $strsth ="
1925         SELECT  aqinvoices.invoicenumber,
1926                 datereceived,purchaseordernumber,
1927                 count(DISTINCT biblionumber) AS biblio,
1928                 sum(quantity) AS itemsexpected,
1929                 sum(quantityreceived) AS itemsreceived
1930         FROM   aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
1931         LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
1932         WHERE aqbasket.booksellerid = ? and datereceived IS NOT NULL
1933     ";
1934     push @query_params, $bookseller;
1935
1936     if ( defined $code ) {
1937         $strsth .= ' and aqinvoices.invoicenumber like ? ';
1938         # add a % to the end of the code to allow stemming.
1939         push @query_params, "$code%";
1940     }
1941
1942     if ( defined $datefrom ) {
1943         $strsth .= ' and datereceived >= ? ';
1944         push @query_params, $datefrom;
1945     }
1946
1947     if ( defined $dateto ) {
1948         $strsth .=  'and datereceived <= ? ';
1949         push @query_params, $dateto;
1950     }
1951
1952     $strsth .= "group by aqinvoices.invoicenumber,datereceived ";
1953
1954     # can't use a placeholder to place this column name.
1955     # but, we could probably be checking to make sure it is a column that will be fetched.
1956     $strsth .= "order by $order " if ($order);
1957
1958     my $sth = $dbh->prepare($strsth);
1959
1960     $sth->execute( @query_params );
1961     my $results = $sth->fetchall_arrayref({});
1962     return @{$results};
1963 }
1964
1965 #------------------------------------------------------------#
1966
1967 =head3 GetLateOrders
1968
1969   @results = &GetLateOrders;
1970
1971 Searches for bookseller with late orders.
1972
1973 return:
1974 the table of supplier with late issues. This table is full of hashref.
1975
1976 =cut
1977
1978 sub GetLateOrders {
1979     my $delay      = shift;
1980     my $supplierid = shift;
1981     my $branch     = shift;
1982     my $estimateddeliverydatefrom = shift;
1983     my $estimateddeliverydateto = shift;
1984
1985     my $dbh = C4::Context->dbh;
1986
1987     #BEWARE, order of parenthesis and LEFT JOIN is important for speed
1988     my $dbdriver = C4::Context->config("db_scheme") || "mysql";
1989
1990     my @query_params = ();
1991     my $select = "
1992     SELECT aqbasket.basketno,
1993         aqorders.ordernumber,
1994         DATE(aqbasket.closedate)  AS orderdate,
1995         aqbasket.basketname       AS basketname,
1996         aqbasket.basketgroupid    AS basketgroupid,
1997         aqbasketgroups.name       AS basketgroupname,
1998         aqorders.rrp              AS unitpricesupplier,
1999         aqorders.ecost            AS unitpricelib,
2000         aqorders.claims_count     AS claims_count,
2001         aqorders.claimed_date     AS claimed_date,
2002         aqbudgets.budget_name     AS budget,
2003         borrowers.branchcode      AS branch,
2004         aqbooksellers.name        AS supplier,
2005         aqbooksellers.id          AS supplierid,
2006         biblio.author, biblio.title,
2007         biblioitems.publishercode AS publisher,
2008         biblioitems.publicationyear,
2009         ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
2010     ";
2011     my $from = "
2012     FROM
2013         aqorders LEFT JOIN biblio     ON biblio.biblionumber         = aqorders.biblionumber
2014         LEFT JOIN biblioitems         ON biblioitems.biblionumber    = biblio.biblionumber
2015         LEFT JOIN aqbudgets           ON aqorders.budget_id          = aqbudgets.budget_id,
2016         aqbasket LEFT JOIN borrowers  ON aqbasket.authorisedby       = borrowers.borrowernumber
2017         LEFT JOIN aqbooksellers       ON aqbasket.booksellerid       = aqbooksellers.id
2018         LEFT JOIN aqbasketgroups      ON aqbasket.basketgroupid      = aqbasketgroups.id
2019         WHERE aqorders.basketno = aqbasket.basketno
2020         AND ( datereceived = ''
2021             OR datereceived IS NULL
2022             OR aqorders.quantityreceived < aqorders.quantity
2023         )
2024         AND aqbasket.closedate IS NOT NULL
2025         AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
2026     ";
2027     my $having = "";
2028     if ($dbdriver eq "mysql") {
2029         $select .= "
2030         aqorders.quantity - COALESCE(aqorders.quantityreceived,0)                 AS quantity,
2031         (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
2032         DATEDIFF(CAST(now() AS date),closedate) AS latesince
2033         ";
2034         if ( defined $delay ) {
2035             $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ;
2036             push @query_params, $delay;
2037         }
2038         $having = "
2039         HAVING quantity          <> 0
2040             AND unitpricesupplier <> 0
2041             AND unitpricelib      <> 0
2042         ";
2043     } else {
2044         # FIXME: account for IFNULL as above
2045         $select .= "
2046                 aqorders.quantity                AS quantity,
2047                 aqorders.quantity * aqorders.rrp AS subtotal,
2048                 (CAST(now() AS date) - closedate)            AS latesince
2049         ";
2050         if ( defined $delay ) {
2051             $from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) ";
2052             push @query_params, $delay;
2053         }
2054     }
2055     if (defined $supplierid) {
2056         $from .= ' AND aqbasket.booksellerid = ? ';
2057         push @query_params, $supplierid;
2058     }
2059     if (defined $branch) {
2060         $from .= ' AND borrowers.branchcode LIKE ? ';
2061         push @query_params, $branch;
2062     }
2063
2064     if ( defined $estimateddeliverydatefrom or defined $estimateddeliverydateto ) {
2065         $from .= ' AND aqbooksellers.deliverytime IS NOT NULL ';
2066     }
2067     if ( defined $estimateddeliverydatefrom ) {
2068         $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
2069         push @query_params, $estimateddeliverydatefrom;
2070     }
2071     if ( defined $estimateddeliverydateto ) {
2072         $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
2073         push @query_params, $estimateddeliverydateto;
2074     }
2075     if ( defined $estimateddeliverydatefrom and not defined $estimateddeliverydateto ) {
2076         $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
2077     }
2078     if (C4::Context->preference("IndependentBranches")
2079             && !C4::Context->IsSuperLibrarian() ) {
2080         $from .= ' AND borrowers.branchcode LIKE ? ';
2081         push @query_params, C4::Context->userenv->{branch};
2082     }
2083     $from .= " AND orderstatus <> 'cancelled' ";
2084     my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier";
2085     $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params);
2086     my $sth = $dbh->prepare($query);
2087     $sth->execute(@query_params);
2088     my @results;
2089     while (my $data = $sth->fetchrow_hashref) {
2090         push @results, $data;
2091     }
2092     return @results;
2093 }
2094
2095 #------------------------------------------------------------#
2096
2097 =head3 GetHistory
2098
2099   \@order_loop = GetHistory( %params );
2100
2101 Retreives some acquisition history information
2102
2103 params:
2104   title
2105   author
2106   name
2107   isbn
2108   ean
2109   from_placed_on
2110   to_placed_on
2111   basket                  - search both basket name and number
2112   booksellerinvoicenumber
2113   basketgroupname
2114   budget
2115   orderstatus (note that orderstatus '' will retrieve orders
2116                of any status except cancelled)
2117   biblionumber
2118   get_canceled_order (if set to a true value, cancelled orders will
2119                       be included)
2120
2121 returns:
2122     $order_loop is a list of hashrefs that each look like this:
2123             {
2124                 'author'           => 'Twain, Mark',
2125                 'basketno'         => '1',
2126                 'biblionumber'     => '215',
2127                 'count'            => 1,
2128                 'creationdate'     => 'MM/DD/YYYY',
2129                 'datereceived'     => undef,
2130                 'ecost'            => '1.00',
2131                 'id'               => '1',
2132                 'invoicenumber'    => undef,
2133                 'name'             => '',
2134                 'ordernumber'      => '1',
2135                 'quantity'         => 1,
2136                 'quantityreceived' => undef,
2137                 'title'            => 'The Adventures of Huckleberry Finn'
2138             }
2139
2140 =cut
2141
2142 sub GetHistory {
2143 # don't run the query if there are no parameters (list would be too long for sure !)
2144     croak "No search params" unless @_;
2145     my %params = @_;
2146     my $title = $params{title};
2147     my $author = $params{author};
2148     my $isbn   = $params{isbn};
2149     my $ean    = $params{ean};
2150     my $name = $params{name};
2151     my $from_placed_on = $params{from_placed_on};
2152     my $to_placed_on = $params{to_placed_on};
2153     my $basket = $params{basket};
2154     my $booksellerinvoicenumber = $params{booksellerinvoicenumber};
2155     my $basketgroupname = $params{basketgroupname};
2156     my $budget = $params{budget};
2157     my $orderstatus = $params{orderstatus};
2158     my $biblionumber = $params{biblionumber};
2159     my $get_canceled_order = $params{get_canceled_order} || 0;
2160     my $ordernumber = $params{ordernumber};
2161     my $search_children_too = $params{search_children_too} || 0;
2162     my $created_by = $params{created_by} || [];
2163
2164     my @order_loop;
2165     my $total_qty         = 0;
2166     my $total_qtyreceived = 0;
2167     my $total_price       = 0;
2168
2169     my $dbh   = C4::Context->dbh;
2170     my $query ="
2171         SELECT
2172             COALESCE(biblio.title,     deletedbiblio.title)     AS title,
2173             COALESCE(biblio.author,    deletedbiblio.author)    AS author,
2174             COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn,
2175             COALESCE(biblioitems.ean,  deletedbiblioitems.ean)  AS ean,
2176             aqorders.basketno,
2177             aqbasket.basketname,
2178             aqbasket.basketgroupid,
2179             aqbasket.authorisedby,
2180             concat( borrowers.firstname,' ',borrowers.surname) AS authorisedbyname,
2181             aqbasketgroups.name as groupname,
2182             aqbooksellers.name,
2183             aqbasket.creationdate,
2184             aqorders.datereceived,
2185             aqorders.quantity,
2186             aqorders.quantityreceived,
2187             aqorders.ecost,
2188             aqorders.ordernumber,
2189             aqorders.invoiceid,
2190             aqinvoices.invoicenumber,
2191             aqbooksellers.id as id,
2192             aqorders.biblionumber,
2193             aqorders.orderstatus,
2194             aqorders.parent_ordernumber,
2195             aqbudgets.budget_name
2196             ";
2197     $query .= ", aqbudgets.budget_id AS budget" if defined $budget;
2198     $query .= "
2199         FROM aqorders
2200         LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
2201         LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
2202         LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
2203         LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
2204         LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
2205         LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id
2206         LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2207         LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.biblionumber
2208         LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=aqorders.biblionumber
2209         LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2210         ";
2211
2212     $query .= " WHERE 1 ";
2213
2214     unless ($get_canceled_order or (defined $orderstatus and $orderstatus eq 'cancelled')) {
2215         $query .= " AND (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') ";
2216     }
2217
2218     my @query_params  = ();
2219
2220     if ( $biblionumber ) {
2221         $query .= " AND biblio.biblionumber = ?";
2222         push @query_params, $biblionumber;
2223     }
2224
2225     if ( $title ) {
2226         $query .= " AND biblio.title LIKE ? ";
2227         $title =~ s/\s+/%/g;
2228         push @query_params, "%$title%";
2229     }
2230
2231     if ( $author ) {
2232         $query .= " AND biblio.author LIKE ? ";
2233         push @query_params, "%$author%";
2234     }
2235
2236     if ( $isbn ) {
2237         $query .= " AND biblioitems.isbn LIKE ? ";
2238         push @query_params, "%$isbn%";
2239     }
2240     if ( $ean ) {
2241         $query .= " AND biblioitems.ean = ? ";
2242         push @query_params, "$ean";
2243     }
2244     if ( $name ) {
2245         $query .= " AND aqbooksellers.name LIKE ? ";
2246         push @query_params, "%$name%";
2247     }
2248
2249     if ( $budget ) {
2250         $query .= " AND aqbudgets.budget_id = ? ";
2251         push @query_params, "$budget";
2252     }
2253
2254     if ( $from_placed_on ) {
2255         $query .= " AND creationdate >= ? ";
2256         push @query_params, $from_placed_on;
2257     }
2258
2259     if ( $to_placed_on ) {
2260         $query .= " AND creationdate <= ? ";
2261         push @query_params, $to_placed_on;
2262     }
2263
2264     if ( defined $orderstatus and $orderstatus ne '') {
2265         $query .= " AND aqorders.orderstatus = ? ";
2266         push @query_params, "$orderstatus";
2267     }
2268
2269     if ($basket) {
2270         if ($basket =~ m/^\d+$/) {
2271             $query .= " AND aqorders.basketno = ? ";
2272             push @query_params, $basket;
2273         } else {
2274             $query .= " AND aqbasket.basketname LIKE ? ";
2275             push @query_params, "%$basket%";
2276         }
2277     }
2278
2279     if ($booksellerinvoicenumber) {
2280         $query .= " AND aqinvoices.invoicenumber LIKE ? ";
2281         push @query_params, "%$booksellerinvoicenumber%";
2282     }
2283
2284     if ($basketgroupname) {
2285         $query .= " AND aqbasketgroups.name LIKE ? ";
2286         push @query_params, "%$basketgroupname%";
2287     }
2288
2289     if ($ordernumber) {
2290         $query .= " AND (aqorders.ordernumber = ? ";
2291         push @query_params, $ordernumber;
2292         if ($search_children_too) {
2293             $query .= " OR aqorders.parent_ordernumber = ? ";
2294             push @query_params, $ordernumber;
2295         }
2296         $query .= ") ";
2297     }
2298
2299     if ( @$created_by ) {
2300         $query .= ' AND aqbasket.authorisedby IN ( ' . join( ',', ('?') x @$created_by ) . ')';
2301         push @query_params, @$created_by;
2302     }
2303
2304
2305     if ( C4::Context->preference("IndependentBranches") ) {
2306         unless ( C4::Context->IsSuperLibrarian() ) {
2307             $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
2308             push @query_params, C4::Context->userenv->{branch};
2309         }
2310     }
2311     $query .= " ORDER BY id";
2312
2313     return $dbh->selectall_arrayref( $query, { Slice => {} }, @query_params );
2314 }
2315
2316 =head2 GetRecentAcqui
2317
2318   $results = GetRecentAcqui($days);
2319
2320 C<$results> is a ref to a table which containts hashref
2321
2322 =cut
2323
2324 sub GetRecentAcqui {
2325     my $limit  = shift;
2326     my $dbh    = C4::Context->dbh;
2327     my $query = "
2328         SELECT *
2329         FROM   biblio
2330         ORDER BY timestamp DESC
2331         LIMIT  0,".$limit;
2332
2333     my $sth = $dbh->prepare($query);
2334     $sth->execute;
2335     my $results = $sth->fetchall_arrayref({});
2336     return $results;
2337 }
2338
2339 #------------------------------------------------------------#
2340
2341 =head3 AddClaim
2342
2343   &AddClaim($ordernumber);
2344
2345 Add a claim for an order
2346
2347 =cut
2348
2349 sub AddClaim {
2350     my ($ordernumber) = @_;
2351     my $dbh          = C4::Context->dbh;
2352     my $query        = "
2353         UPDATE aqorders SET
2354             claims_count = claims_count + 1,
2355             claimed_date = CURDATE()
2356         WHERE ordernumber = ?
2357         ";
2358     my $sth = $dbh->prepare($query);
2359     $sth->execute($ordernumber);
2360 }
2361
2362 =head3 GetInvoices
2363
2364     my @invoices = GetInvoices(
2365         invoicenumber => $invoicenumber,
2366         supplierid => $supplierid,
2367         suppliername => $suppliername,
2368         shipmentdatefrom => $shipmentdatefrom, # ISO format
2369         shipmentdateto => $shipmentdateto, # ISO format
2370         billingdatefrom => $billingdatefrom, # ISO format
2371         billingdateto => $billingdateto, # ISO format
2372         isbneanissn => $isbn_or_ean_or_issn,
2373         title => $title,
2374         author => $author,
2375         publisher => $publisher,
2376         publicationyear => $publicationyear,
2377         branchcode => $branchcode,
2378         order_by => $order_by
2379     );
2380
2381 Return a list of invoices that match all given criteria.
2382
2383 $order_by is "column_name (asc|desc)", where column_name is any of
2384 'invoicenumber', 'booksellerid', 'shipmentdate', 'billingdate', 'closedate',
2385 'shipmentcost', 'shipmentcost_budgetid'.
2386
2387 asc is the default if omitted
2388
2389 =cut
2390
2391 sub GetInvoices {
2392     my %args = @_;
2393
2394     my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2395         closedate shipmentcost shipmentcost_budgetid);
2396
2397     my $dbh = C4::Context->dbh;
2398     my $query = qq{
2399         SELECT aqinvoices.*, aqbooksellers.name AS suppliername,
2400           COUNT(
2401             DISTINCT IF(
2402               aqorders.datereceived IS NOT NULL,
2403               aqorders.biblionumber,
2404               NULL
2405             )
2406           ) AS receivedbiblios,
2407           COUNT(
2408              DISTINCT IF(
2409               aqorders.subscriptionid IS NOT NULL,
2410               aqorders.subscriptionid,
2411               NULL
2412             )
2413           ) AS is_linked_to_subscriptions,
2414           SUM(aqorders.quantityreceived) AS receiveditems
2415         FROM aqinvoices
2416           LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid
2417           LEFT JOIN aqorders ON aqorders.invoiceid = aqinvoices.invoiceid
2418           LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
2419           LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2420           LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2421           LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
2422           LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber
2423     };
2424
2425     my @bind_args;
2426     my @bind_strs;
2427     if($args{supplierid}) {
2428         push @bind_strs, " aqinvoices.booksellerid = ? ";
2429         push @bind_args, $args{supplierid};
2430     }
2431     if($args{invoicenumber}) {
2432         push @bind_strs, " aqinvoices.invoicenumber LIKE ? ";
2433         push @bind_args, "%$args{invoicenumber}%";
2434     }
2435     if($args{suppliername}) {
2436         push @bind_strs, " aqbooksellers.name LIKE ? ";
2437         push @bind_args, "%$args{suppliername}%";
2438     }
2439     if($args{shipmentdatefrom}) {
2440         push @bind_strs, " aqinvoices.shipmentdate >= ? ";
2441         push @bind_args, $args{shipmentdatefrom};
2442     }
2443     if($args{shipmentdateto}) {
2444         push @bind_strs, " aqinvoices.shipmentdate <= ? ";
2445         push @bind_args, $args{shipmentdateto};
2446     }
2447     if($args{billingdatefrom}) {
2448         push @bind_strs, " aqinvoices.billingdate >= ? ";
2449         push @bind_args, $args{billingdatefrom};
2450     }
2451     if($args{billingdateto}) {
2452         push @bind_strs, " aqinvoices.billingdate <= ? ";
2453         push @bind_args, $args{billingdateto};
2454     }
2455     if($args{isbneanissn}) {
2456         push @bind_strs, " (biblioitems.isbn LIKE CONCAT('%', ?, '%') OR biblioitems.ean LIKE CONCAT('%', ?, '%') OR biblioitems.issn LIKE CONCAT('%', ?, '%') ) ";
2457         push @bind_args, $args{isbneanissn}, $args{isbneanissn}, $args{isbneanissn};
2458     }
2459     if($args{title}) {
2460         push @bind_strs, " biblio.title LIKE CONCAT('%', ?, '%') ";
2461         push @bind_args, $args{title};
2462     }
2463     if($args{author}) {
2464         push @bind_strs, " biblio.author LIKE CONCAT('%', ?, '%') ";
2465         push @bind_args, $args{author};
2466     }
2467     if($args{publisher}) {
2468         push @bind_strs, " biblioitems.publishercode LIKE CONCAT('%', ?, '%') ";
2469         push @bind_args, $args{publisher};
2470     }
2471     if($args{publicationyear}) {
2472         push @bind_strs, " ((biblioitems.publicationyear LIKE CONCAT('%', ?, '%')) OR (biblio.copyrightdate LIKE CONCAT('%', ?, '%'))) ";
2473         push @bind_args, $args{publicationyear}, $args{publicationyear};
2474     }
2475     if($args{branchcode}) {
2476         push @bind_strs, " borrowers.branchcode = ? ";
2477         push @bind_args, $args{branchcode};
2478     }
2479     if($args{message_id}) {
2480         push @bind_strs, " aqinvoices.message_id = ? ";
2481         push @bind_args, $args{message_id};
2482     }
2483
2484     $query .= " WHERE " . join(" AND ", @bind_strs) if @bind_strs;
2485     $query .= " GROUP BY aqinvoices.invoiceid ";
2486
2487     if($args{order_by}) {
2488         my ($column, $direction) = split / /, $args{order_by};
2489         if(grep /^$column$/, @columns) {
2490             $direction ||= 'ASC';
2491             $query .= " ORDER BY $column $direction";
2492         }
2493     }
2494
2495     my $sth = $dbh->prepare($query);
2496     $sth->execute(@bind_args);
2497
2498     my $results = $sth->fetchall_arrayref({});
2499     return @$results;
2500 }
2501
2502 =head3 GetInvoice
2503
2504     my $invoice = GetInvoice($invoiceid);
2505
2506 Get informations about invoice with given $invoiceid
2507
2508 Return a hash filled with aqinvoices.* fields
2509
2510 =cut
2511
2512 sub GetInvoice {
2513     my ($invoiceid) = @_;
2514     my $invoice;
2515
2516     return unless $invoiceid;
2517
2518     my $dbh = C4::Context->dbh;
2519     my $query = qq{
2520         SELECT *
2521         FROM aqinvoices
2522         WHERE invoiceid = ?
2523     };
2524     my $sth = $dbh->prepare($query);
2525     $sth->execute($invoiceid);
2526
2527     $invoice = $sth->fetchrow_hashref;
2528     return $invoice;
2529 }
2530
2531 =head3 GetInvoiceDetails
2532
2533     my $invoice = GetInvoiceDetails($invoiceid)
2534
2535 Return informations about an invoice + the list of related order lines
2536
2537 Orders informations are in $invoice->{orders} (array ref)
2538
2539 =cut
2540
2541 sub GetInvoiceDetails {
2542     my ($invoiceid) = @_;
2543
2544     if ( !defined $invoiceid ) {
2545         carp 'GetInvoiceDetails called without an invoiceid';
2546         return;
2547     }
2548
2549     my $dbh = C4::Context->dbh;
2550     my $query = q{
2551         SELECT aqinvoices.*, aqbooksellers.name AS suppliername
2552         FROM aqinvoices
2553           LEFT JOIN aqbooksellers ON aqinvoices.booksellerid = aqbooksellers.id
2554         WHERE invoiceid = ?
2555     };
2556     my $sth = $dbh->prepare($query);
2557     $sth->execute($invoiceid);
2558
2559     my $invoice = $sth->fetchrow_hashref;
2560
2561     $query = q{
2562         SELECT aqorders.*,
2563                 biblio.*,
2564                 biblio.copyrightdate,
2565                 biblioitems.publishercode,
2566                 biblioitems.publicationyear,
2567                 aqbasket.basketname,
2568                 aqbasketgroups.id AS basketgroupid,
2569                 aqbasketgroups.name AS basketgroupname
2570         FROM aqorders
2571           LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
2572           LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
2573           LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2574           LEFT JOIN biblioitems ON aqorders.biblionumber = biblioitems.biblionumber
2575         WHERE invoiceid = ?
2576     };
2577     $sth = $dbh->prepare($query);
2578     $sth->execute($invoiceid);
2579     $invoice->{orders} = $sth->fetchall_arrayref({});
2580     $invoice->{orders} ||= []; # force an empty arrayref if fetchall_arrayref fails
2581
2582     return $invoice;
2583 }
2584
2585 =head3 AddInvoice
2586
2587     my $invoiceid = AddInvoice(
2588         invoicenumber => $invoicenumber,
2589         booksellerid => $booksellerid,
2590         shipmentdate => $shipmentdate,
2591         billingdate => $billingdate,
2592         closedate => $closedate,
2593         shipmentcost => $shipmentcost,
2594         shipmentcost_budgetid => $shipmentcost_budgetid
2595     );
2596
2597 Create a new invoice and return its id or undef if it fails.
2598
2599 =cut
2600
2601 sub AddInvoice {
2602     my %invoice = @_;
2603
2604     return unless(%invoice and $invoice{invoicenumber});
2605
2606     my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2607         closedate shipmentcost shipmentcost_budgetid message_id);
2608
2609     my @set_strs;
2610     my @set_args;
2611     foreach my $key (keys %invoice) {
2612         if(0 < grep(/^$key$/, @columns)) {
2613             push @set_strs, "$key = ?";
2614             push @set_args, ($invoice{$key} || undef);
2615         }
2616     }
2617
2618     my $rv;
2619     if(@set_args > 0) {
2620         my $dbh = C4::Context->dbh;
2621         my $query = "INSERT INTO aqinvoices SET ";
2622         $query .= join (",", @set_strs);
2623         my $sth = $dbh->prepare($query);
2624         $rv = $sth->execute(@set_args);
2625         if($rv) {
2626             $rv = $dbh->last_insert_id(undef, undef, 'aqinvoices', undef);
2627         }
2628     }
2629     return $rv;
2630 }
2631
2632 =head3 ModInvoice
2633
2634     ModInvoice(
2635         invoiceid => $invoiceid,    # Mandatory
2636         invoicenumber => $invoicenumber,
2637         booksellerid => $booksellerid,
2638         shipmentdate => $shipmentdate,
2639         billingdate => $billingdate,
2640         closedate => $closedate,
2641         shipmentcost => $shipmentcost,
2642         shipmentcost_budgetid => $shipmentcost_budgetid
2643     );
2644
2645 Modify an invoice, invoiceid is mandatory.
2646
2647 Return undef if it fails.
2648
2649 =cut
2650
2651 sub ModInvoice {
2652     my %invoice = @_;
2653
2654     return unless(%invoice and $invoice{invoiceid});
2655
2656     my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2657         closedate shipmentcost shipmentcost_budgetid);
2658
2659     my @set_strs;
2660     my @set_args;
2661     foreach my $key (keys %invoice) {
2662         if(0 < grep(/^$key$/, @columns)) {
2663             push @set_strs, "$key = ?";
2664             push @set_args, ($invoice{$key} || undef);
2665         }
2666     }
2667
2668     my $dbh = C4::Context->dbh;
2669     my $query = "UPDATE aqinvoices SET ";
2670     $query .= join(",", @set_strs);
2671     $query .= " WHERE invoiceid = ?";
2672
2673     my $sth = $dbh->prepare($query);
2674     $sth->execute(@set_args, $invoice{invoiceid});
2675 }
2676
2677 =head3 CloseInvoice
2678
2679     CloseInvoice($invoiceid);
2680
2681 Close an invoice.
2682
2683 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => undef);
2684
2685 =cut
2686
2687 sub CloseInvoice {
2688     my ($invoiceid) = @_;
2689
2690     return unless $invoiceid;
2691
2692     my $dbh = C4::Context->dbh;
2693     my $query = qq{
2694         UPDATE aqinvoices
2695         SET closedate = CAST(NOW() AS DATE)
2696         WHERE invoiceid = ?
2697     };
2698     my $sth = $dbh->prepare($query);
2699     $sth->execute($invoiceid);
2700 }
2701
2702 =head3 ReopenInvoice
2703
2704     ReopenInvoice($invoiceid);
2705
2706 Reopen an invoice
2707
2708 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => output_pref({ dt=>dt_from_string, dateonly=>1, otputpref=>'iso' }))
2709
2710 =cut
2711
2712 sub ReopenInvoice {
2713     my ($invoiceid) = @_;
2714
2715     return unless $invoiceid;
2716
2717     my $dbh = C4::Context->dbh;
2718     my $query = qq{
2719         UPDATE aqinvoices
2720         SET closedate = NULL
2721         WHERE invoiceid = ?
2722     };
2723     my $sth = $dbh->prepare($query);
2724     $sth->execute($invoiceid);
2725 }
2726
2727 =head3 DelInvoice
2728
2729     DelInvoice($invoiceid);
2730
2731 Delete an invoice if there are no items attached to it.
2732
2733 =cut
2734
2735 sub DelInvoice {
2736     my ($invoiceid) = @_;
2737
2738     return unless $invoiceid;
2739
2740     my $dbh   = C4::Context->dbh;
2741     my $query = qq{
2742         SELECT COUNT(*)
2743         FROM aqorders
2744         WHERE invoiceid = ?
2745     };
2746     my $sth = $dbh->prepare($query);
2747     $sth->execute($invoiceid);
2748     my $res = $sth->fetchrow_arrayref;
2749     if ( $res && $res->[0] == 0 ) {
2750         $query = qq{
2751             DELETE FROM aqinvoices
2752             WHERE invoiceid = ?
2753         };
2754         my $sth = $dbh->prepare($query);
2755         return ( $sth->execute($invoiceid) > 0 );
2756     }
2757     return;
2758 }
2759
2760 =head3 MergeInvoices
2761
2762     MergeInvoices($invoiceid, \@sourceids);
2763
2764 Merge the invoices identified by the IDs in \@sourceids into
2765 the invoice identified by $invoiceid.
2766
2767 =cut
2768
2769 sub MergeInvoices {
2770     my ($invoiceid, $sourceids) = @_;
2771
2772     return unless $invoiceid;
2773     foreach my $sourceid (@$sourceids) {
2774         next if $sourceid == $invoiceid;
2775         my $source = GetInvoiceDetails($sourceid);
2776         foreach my $order (@{$source->{'orders'}}) {
2777             $order->{'invoiceid'} = $invoiceid;
2778             ModOrder($order);
2779         }
2780         DelInvoice($source->{'invoiceid'});
2781     }
2782     return;
2783 }
2784
2785 =head3 GetBiblioCountByBasketno
2786
2787 $biblio_count = &GetBiblioCountByBasketno($basketno);
2788
2789 Looks up the biblio's count that has basketno value $basketno
2790
2791 Returns a quantity
2792
2793 =cut
2794
2795 sub GetBiblioCountByBasketno {
2796     my ($basketno) = @_;
2797     my $dbh          = C4::Context->dbh;
2798     my $query        = "
2799         SELECT COUNT( DISTINCT( biblionumber ) )
2800         FROM   aqorders
2801         WHERE  basketno = ?
2802             AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
2803         ";
2804
2805     my $sth = $dbh->prepare($query);
2806     $sth->execute($basketno);
2807     return $sth->fetchrow;
2808 }
2809
2810 # This is *not* the good way to calcul prices
2811 # But it's how it works at the moment into Koha
2812 # This will be fixed later.
2813 # Note this subroutine should be moved to Koha::Acquisition::Order
2814 # Will do when a DBIC decision will be taken.
2815 sub populate_order_with_prices {
2816     my ($params) = @_;
2817
2818     my $order        = $params->{order};
2819     my $booksellerid = $params->{booksellerid};
2820     return unless $booksellerid;
2821
2822     my $bookseller = Koha::Acquisition::Bookseller->fetch({ id => $booksellerid });
2823
2824     my $receiving = $params->{receiving};
2825     my $ordering  = $params->{ordering};
2826     my $discount  = $order->{discount};
2827     $discount /= 100 if $discount > 1;
2828
2829     $order->{rrp}   = Koha::Number::Price->new( $order->{rrp} )->round;
2830     $order->{ecost} = Koha::Number::Price->new( $order->{ecost} )->round;
2831     if ($ordering) {
2832         if ( $bookseller->{listincgst} ) {
2833             $order->{rrpgsti} = $order->{rrp};
2834             $order->{rrpgste} = Koha::Number::Price->new(
2835                 $order->{rrpgsti} / ( 1 + $order->{gstrate} ) )->round;
2836             $order->{ecostgsti} = $order->{ecost};
2837             $order->{ecostgste} = Koha::Number::Price->new(
2838                 $order->{ecost} / ( 1 + $order->{gstrate} ) )->round;
2839             $order->{gstvalue} = Koha::Number::Price->new(
2840                 ( $order->{ecostgsti} - $order->{ecostgste} ) *
2841                   $order->{quantity} )->round;
2842             $order->{totalgste} = $order->{ecostgste} * $order->{quantity};
2843             $order->{totalgsti} = $order->{ecostgsti} * $order->{quantity};
2844         }
2845         else {
2846             $order->{rrpgste} = $order->{rrp};
2847             $order->{rrpgsti} = Koha::Number::Price->new(
2848                 $order->{rrp} * ( 1 + $order->{gstrate} ) )->round;
2849             $order->{ecostgste} = $order->{ecost};
2850             $order->{ecostgsti} = Koha::Number::Price->new(
2851                 $order->{ecost} * ( 1 + $order->{gstrate} ) )->round;
2852             $order->{gstvalue} = Koha::Number::Price->new(
2853                 ( $order->{ecostgsti} - $order->{ecostgste} ) *
2854                   $order->{quantity} )->round;
2855             $order->{totalgste} = $order->{ecostgste} * $order->{quantity};
2856             $order->{totalgsti} = $order->{ecostgsti} * $order->{quantity};
2857         }
2858     }
2859
2860     if ($receiving) {
2861         if ( $bookseller->{listincgst} ) {
2862             $order->{unitpricegsti} = Koha::Number::Price->new( $order->{unitprice} )->round;
2863             $order->{unitpricegste} = Koha::Number::Price->new(
2864               $order->{unitpricegsti} / ( 1 + $order->{gstrate} ) )->round;
2865         }
2866         else {
2867             $order->{unitpricegste} = Koha::Number::Price->new( $order->{unitprice} )->round;
2868             $order->{unitpricegsti} = Koha::Number::Price->new(
2869               $order->{unitpricegste} * ( 1 + $order->{gstrate} ) )->round;
2870         }
2871         $order->{gstvalue} = Koha::Number::Price->new(
2872           ( $order->{unitpricegsti} - $order->{unitpricegste} )
2873           * $order->{quantityreceived} )->round;
2874
2875         $order->{totalgste} = $order->{unitpricegste} * $order->{quantity};
2876         $order->{totalgsti} = $order->{unitpricegsti} * $order->{quantity};
2877     }
2878
2879     return $order;
2880 }
2881
2882 =head3 GetOrderUsers
2883
2884     $order_users_ids = &GetOrderUsers($ordernumber);
2885
2886 Returns a list of all borrowernumbers that are in order users list
2887
2888 =cut
2889
2890 sub GetOrderUsers {
2891     my ($ordernumber) = @_;
2892
2893     return unless $ordernumber;
2894
2895     my $query = q|
2896         SELECT borrowernumber
2897         FROM aqorder_users
2898         WHERE ordernumber = ?
2899     |;
2900     my $dbh = C4::Context->dbh;
2901     my $sth = $dbh->prepare($query);
2902     $sth->execute($ordernumber);
2903     my $results = $sth->fetchall_arrayref( {} );
2904
2905     my @borrowernumbers;
2906     foreach (@$results) {
2907         push @borrowernumbers, $_->{'borrowernumber'};
2908     }
2909
2910     return @borrowernumbers;
2911 }
2912
2913 =head3 ModOrderUsers
2914
2915     my @order_users_ids = (1, 2, 3);
2916     &ModOrderUsers($ordernumber, @basketusers_ids);
2917
2918 Delete all users from order users list, and add users in C<@order_users_ids>
2919 to this users list.
2920
2921 =cut
2922
2923 sub ModOrderUsers {
2924     my ( $ordernumber, @order_users_ids ) = @_;
2925
2926     return unless $ordernumber;
2927
2928     my $dbh   = C4::Context->dbh;
2929     my $query = q|
2930         DELETE FROM aqorder_users
2931         WHERE ordernumber = ?
2932     |;
2933     my $sth = $dbh->prepare($query);
2934     $sth->execute($ordernumber);
2935
2936     $query = q|
2937         INSERT INTO aqorder_users (ordernumber, borrowernumber)
2938         VALUES (?, ?)
2939     |;
2940     $sth = $dbh->prepare($query);
2941     foreach my $order_user_id (@order_users_ids) {
2942         $sth->execute( $ordernumber, $order_user_id );
2943     }
2944 }
2945
2946 sub NotifyOrderUsers {
2947     my ($ordernumber) = @_;
2948
2949     my @borrowernumbers = GetOrderUsers($ordernumber);
2950     return unless @borrowernumbers;
2951
2952     my $order = GetOrder( $ordernumber );
2953     for my $borrowernumber (@borrowernumbers) {
2954         my $borrower = C4::Members::GetMember( borrowernumber => $borrowernumber );
2955         my $library = Koha::Libraries->find( $borrower->{branchcode} )->unblessed;
2956         my $biblio = C4::Biblio::GetBiblio( $order->{biblionumber} );
2957         my $letter = C4::Letters::GetPreparedLetter(
2958             module      => 'acquisition',
2959             letter_code => 'ACQ_NOTIF_ON_RECEIV',
2960             branchcode  => $library->{branchcode},
2961             tables      => {
2962                 'branches'    => $library,
2963                 'borrowers'   => $borrower,
2964                 'biblio'      => $biblio,
2965                 'aqorders'    => $order,
2966             },
2967         );
2968         if ( $letter ) {
2969             C4::Letters::EnqueueLetter(
2970                 {
2971                     letter         => $letter,
2972                     borrowernumber => $borrowernumber,
2973                     LibraryName    => C4::Context->preference("LibraryName"),
2974                     message_transport_type => 'email',
2975                 }
2976             ) or warn "can't enqueue letter $letter";
2977         }
2978     }
2979 }
2980
2981 =head3 FillWithDefaultValues
2982
2983 FillWithDefaultValues( $marc_record );
2984
2985 This will update the record with default value defined in the ACQ framework.
2986 For all existing fields, if a default value exists and there are no subfield, it will be created.
2987 If the field does not exist, it will be created too.
2988
2989 =cut
2990
2991 sub FillWithDefaultValues {
2992     my ($record) = @_;
2993     my $tagslib = C4::Biblio::GetMarcStructure( 1, 'ACQ' );
2994     if ($tagslib) {
2995         my ($itemfield) =
2996           C4::Biblio::GetMarcFromKohaField( 'items.itemnumber', '' );
2997         for my $tag ( sort keys %$tagslib ) {
2998             next unless $tag;
2999             next if $tag == $itemfield;
3000             for my $subfield ( sort keys %{ $tagslib->{$tag} } ) {
3001                 next if IsMarcStructureInternal($tagslib->{$tag}{$subfield});
3002                 my $defaultvalue = $tagslib->{$tag}{$subfield}{defaultvalue};
3003                 if ( defined $defaultvalue and $defaultvalue ne '' ) {
3004                     my @fields = $record->field($tag);
3005                     if (@fields) {
3006                         for my $field (@fields) {
3007                             unless ( defined $field->subfield($subfield) ) {
3008                                 $field->add_subfields(
3009                                     $subfield => $defaultvalue );
3010                             }
3011                         }
3012                     }
3013                     else {
3014                         $record->insert_fields_ordered(
3015                             MARC::Field->new(
3016                                 $tag, '', '', $subfield => $defaultvalue
3017                             )
3018                         );
3019                     }
3020                 }
3021             }
3022         }
3023     }
3024 }
3025
3026 1;
3027 __END__
3028
3029 =head1 AUTHOR
3030
3031 Koha Development Team <http://koha-community.org/>
3032
3033 =cut