Bug 12443 - Initial re-factoring of buildQuery

[koha_fer] / C4 / Acquisition.pm

diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm
index c565db6..2e013ac 100644 (file)
--- a/C4/Acquisition.pm
+++ b/C4/Acquisition.pm
@@ -310,7 +310,7 @@ sub GetBasketAsCSV {
             publicationyear => $bd->{'publicationyear'},
             publishercode => $bd->{'publishercode'},
             collectiontitle => $bd->{'collectiontitle'},
-            notes => $order->{'order_internalnote'},
+            notes => $order->{'order_vendornote'},
             quantity => $order->{'quantity'},
             rrp => $order->{'rrp'},
             deliveryplace => C4::Branch::GetBranchName( $basket->{'deliveryplace'} ),
@@ -381,7 +381,7 @@ sub GetBasketGroupAsCSV {
                 rrp => $order->{rrp},
                 discount => $bookseller->{discount},
                 ecost => $order->{ecost},
-                notes => $order->{order_internalnote},
+                notes => $order->{order_vendornote},
                 entrydate => $order->{entrydate},
                 booksellername => $bookseller->{name},
                 bookselleraddress => $bookseller->{address1},
@@ -632,9 +632,12 @@ sub GetBasketsInfosByBookseller {
     return unless $supplierid;
 
     my $dbh = C4::Context->dbh;
-    my $query = qq{
+    my $query = q{
         SELECT aqbasket.*,
           SUM(aqorders.quantity) AS total_items,
+          SUM(
+            IF ( aqorders.orderstatus = 'cancelled', aqorders.quantity, 0 )
+          ) AS total_items_cancelled,
           COUNT(DISTINCT aqorders.biblionumber) AS total_biblios,
           SUM(
             IF(aqorders.datereceived IS NULL
@@ -645,14 +648,30 @@ sub GetBasketsInfosByBookseller {
         FROM aqbasket
           LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
         WHERE booksellerid = ?};
-    if(!$allbaskets) {
+
+    unless ( $allbaskets ) {
         $query.=" AND (closedate IS NULL OR (aqorders.quantity > aqorders.quantityreceived AND datecancellationprinted IS NULL))";
     }
     $query.=" GROUP BY aqbasket.basketno";
 
     my $sth = $dbh->prepare($query);
     $sth->execute($supplierid);
-    return $sth->fetchall_arrayref({});
+    my $baskets = $sth->fetchall_arrayref({});
+
+    # Retrieve the number of biblios cancelled
+    my $cancelled_biblios = $dbh->selectall_hashref( q|
+        SELECT COUNT(DISTINCT(biblionumber)) AS total_biblios_cancelled, aqbasket.basketno
+        FROM aqbasket
+        LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
+        WHERE booksellerid = ?
+        AND aqorders.orderstatus = 'cancelled'
+        GROUP BY aqbasket.basketno
+    |, 'basketno', {}, $supplierid );
+    map {
+        $_->{total_biblios_cancelled} = $cancelled_biblios->{$_->{basketno}}{total_biblios_cancelled} || 0
+    } @$baskets;
+
+    return $baskets;
 }
 
 =head3 GetBasketUsers
@@ -1704,6 +1723,7 @@ sub SearchOrders {
     my $owner = $params->{owner};
     my $pending = $params->{pending};
     my $ordered = $params->{ordered};
+    my $biblionumber = $params->{biblionumber};
 
     my $dbh = C4::Context->dbh;
     my @args = ();
@@ -1728,6 +1748,14 @@ sub SearchOrders {
             LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
             LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
             LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
+    };
+
+    # If we search on ordernumber, we retrieve the transfered order if a transfer has been done.
+    $query .= q{
+            LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
+    } if $ordernumber;
+
+    $query .= q{
         WHERE (datecancellationprinted is NULL)
     };
 
@@ -1752,8 +1780,12 @@ sub SearchOrders {
     }
 
     if ( $ordernumber ) {
-        $query .= ' AND (aqorders.ordernumber=?)';
-        push @args, $ordernumber;
+        $query .= ' AND ( aqorders.ordernumber = ? OR aqorders_transfers.ordernumber_from = ? ) ';
+        push @args, ( $ordernumber, $ordernumber );
+    }
+    if ( $biblionumber ) {
+        $query .= 'AND aqorders.biblionumber = ?';
+        push @args, $biblionumber;
     }
     if( $search ) {
         $query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)';
@@ -2069,6 +2101,9 @@ sub GetLateOrders {
     SELECT aqbasket.basketno,
         aqorders.ordernumber,
         DATE(aqbasket.closedate)  AS orderdate,
+        aqbasket.basketname       AS basketname,
+        aqbasket.basketgroupid    AS basketgroupid,
+        aqbasketgroups.name       AS basketgroupname,
         aqorders.rrp              AS unitpricesupplier,
         aqorders.ecost            AS unitpricelib,
         aqorders.claims_count     AS claims_count,
@@ -2089,6 +2124,7 @@ sub GetLateOrders {
         LEFT JOIN aqbudgets           ON aqorders.budget_id          = aqbudgets.budget_id,
         aqbasket LEFT JOIN borrowers  ON aqbasket.authorisedby       = borrowers.borrowernumber
         LEFT JOIN aqbooksellers       ON aqbasket.booksellerid       = aqbooksellers.id
+        LEFT JOIN aqbasketgroups      ON aqbasket.basketgroupid      = aqbasketgroups.id
         WHERE aqorders.basketno = aqbasket.basketno
         AND ( datereceived = ''
             OR datereceived IS NULL
@@ -2160,8 +2196,6 @@ sub GetLateOrders {
     $sth->execute(@query_params);
     my @results;
     while (my $data = $sth->fetchrow_hashref) {
-        $data->{orderdate} = format_date($data->{orderdate});
-        $data->{claimed_date} = format_date($data->{claimed_date});
         push @results, $data;
     }
     return @results;
@@ -2235,6 +2269,8 @@ sub GetHistory {
     my $orderstatus = $params{orderstatus};
     my $biblionumber = $params{biblionumber};
     my $get_canceled_order = $params{get_canceled_order} || 0;
+    my $ordernumber = $params{ordernumber};
+    my $search_children_too = $params{search_children_too} || 0;
 
     my @order_loop;
     my $total_qty         = 0;
@@ -2362,6 +2398,17 @@ sub GetHistory {
         push @query_params, "%$basketgroupname%";
     }
 
+    if ($ordernumber) {
+        $query .= " AND (aqorders.ordernumber = ? ";
+        push @query_params, $ordernumber;
+        if ($search_children_too) {
+            $query .= " OR aqorders.parent_ordernumber = ? ";
+            push @query_params, $ordernumber;
+        }
+        $query .= ") ";
+    }
+
+
     if ( C4::Context->preference("IndependentBranches") ) {
         unless ( C4::Context->IsSuperLibrarian() ) {
             $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
@@ -2542,6 +2589,13 @@ sub GetInvoices {
               NULL
             )
           ) AS receivedbiblios,
+          COUNT(
+             DISTINCT IF(
+              aqorders.subscriptionid IS NOT NULL,
+              aqorders.subscriptionid,
+              NULL
+            )
+          ) AS is_linked_to_subscriptions,
           SUM(aqorders.quantityreceived) AS receiveditems
         FROM aqinvoices
           LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid