Bug 12443 - Initial re-factoring of buildQuery

[koha_fer] / C4 / Acquisition.pm

diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm
index b848c73..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'},
             publicationyear => $bd->{'publicationyear'},
             publishercode => $bd->{'publishercode'},
             collectiontitle => $bd->{'collectiontitle'},

-            notes => $order->{'notes'},
+            notes => $order->{'order_vendornote'},

             quantity => $order->{'quantity'},
             rrp => $order->{'rrp'},
             deliveryplace => C4::Branch::GetBranchName( $basket->{'deliveryplace'} ),
             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},
                 rrp => $order->{rrp},
                 discount => $bookseller->{discount},
                 ecost => $order->{ecost},

-                notes => $order->{notes},
+                notes => $order->{order_vendornote},

                 entrydate => $order->{entrydate},
                 booksellername => $bookseller->{name},
                 bookselleraddress => $bookseller->{address1},
                 entrydate => $order->{entrydate},
                 booksellername => $bookseller->{name},
                 bookselleraddress => $bookseller->{address1},


@@ -632,9 +632,12 @@ sub GetBasketsInfosByBookseller {
     return unless $supplierid;
 
     my $dbh = C4::Context->dbh;
     return unless $supplierid;
 
     my $dbh = C4::Context->dbh;

-    my $query = qq{
+    my $query = q{

         SELECT aqbasket.*,
           SUM(aqorders.quantity) AS total_items,
         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
           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 = ?};
         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);
         $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
 }
 
 =head3 GetBasketUsers


@@ -1027,6 +1046,7 @@ biblio, and biblioitems tables in the Koha database.
 
 sub GetOrders {
     my ( $basketno, $orderby ) = @_;
 
 sub GetOrders {
     my ( $basketno, $orderby ) = @_;

+    return () unless $basketno;

     my $dbh   = C4::Context->dbh;
     my $query  ="
         SELECT biblio.*,biblioitems.*,
     my $dbh   = C4::Context->dbh;
     my $query  ="
         SELECT biblio.*,biblioitems.*,


@@ -1101,6 +1121,8 @@ C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha d
 
 sub GetOrder {
     my ($ordernumber) = @_;
 
 sub GetOrder {
     my ($ordernumber) = @_;

+    return unless $ordernumber;
+

     my $dbh      = C4::Context->dbh;
     my $query = qq{SELECT
                 aqorders.*,
     my $dbh      = C4::Context->dbh;
     my $query = qq{SELECT
                 aqorders.*,


@@ -1231,7 +1253,7 @@ Else, the upcoming July 1st is used.
 
 =item defaults entrydate to Now
 
 
 =item defaults entrydate to Now
 

-The following keys are used: "biblionumber", "title", "basketno", "quantity", "notes", "rrp", "ecost", "gstrate", "unitprice", "subscription", "sort1", "sort2", "booksellerinvoicenumber", "listprice", "budgetdate", "purchaseordernumber", "branchcode", "booksellerinvoicenumber", "budget_id".
+The following keys are used: "biblionumber", "title", "basketno", "quantity", "order_vendornote", "order_internalnote", "rrp", "ecost", "gstrate", "unitprice", "subscription", "sort1", "sort2", "booksellerinvoicenumber", "listprice", "budgetdate", "purchaseordernumber", "branchcode", "booksellerinvoicenumber", "budget_id".

 
 =back
 
 
 =back
 


@@ -1417,8 +1439,21 @@ sub GetCancelledOrders {
 
 =head3 ModReceiveOrder
 
 
 =head3 ModReceiveOrder
 

-  &ModReceiveOrder($biblionumber, $ordernumber, $quantityreceived, $user,
-    $cost, $ecost, $invoiceid, rrp, budget_id, datereceived, \@received_itemnumbers);
+  &ModReceiveOrder({
+    biblionumber => $biblionumber,
+    ordernumber => $ordernumber,
+    quantityreceived => $quantityreceived,
+    user => $user,
+    cost => $cost,
+    ecost => $ecost,
+    invoiceid => $invoiceid,
+    rrp => $rrp,
+    budget_id => $budget_id,
+    datereceived => $datereceived,
+    received_itemnumbers => \@received_itemnumbers,
+    order_internalnote => $order_internalnote,
+    order_vendornote => $order_vendornote,
+   });

 
 Updates an order, to reflect the fact that it was received, at least
 in part. All arguments not mentioned below update the fields with the
 
 Updates an order, to reflect the fact that it was received, at least
 in part. All arguments not mentioned below update the fields with the


@@ -1433,11 +1468,20 @@ C<$ordernumber>.
 
 
 sub ModReceiveOrder {
 
 
 sub ModReceiveOrder {

-    my (
-        $biblionumber,    $ordernumber,  $quantrec, $user, $cost, $ecost,
-        $invoiceid, $rrp, $budget_id, $datereceived, $received_items
-    )
-    = @_;
+    my ( $params ) = @_;
+    my $biblionumber = $params->{biblionumber};
+    my $ordernumber = $params->{ordernumber};
+    my $quantrec = $params->{quantityreceived};
+    my $user = $params->{user};
+    my $cost = $params->{cost};
+    my $ecost = $params->{ecost};
+    my $invoiceid = $params->{invoiceid};
+    my $rrp = $params->{rrp};
+    my $budget_id = $params->{budget_id};
+    my $datereceived = $params->{datereceived};
+    my $received_items = $params->{received_items};
+    my $order_internalnote = $params->{order_internalnote};
+    my $order_vendornote = $params->{order_vendornote};

 
     my $dbh = C4::Context->dbh;
     $datereceived = C4::Dates->output('iso') unless $datereceived;
 
     my $dbh = C4::Context->dbh;
     $datereceived = C4::Dates->output('iso') unless $datereceived;


@@ -1463,14 +1507,21 @@ q{SELECT * FROM aqorders WHERE biblionumber=? AND aqorders.ordernumber=?},
         # without received items (the quantity is decreased),
         # the second part is a new order line with quantity=quantityrec
         # (entirely received)
         # without received items (the quantity is decreased),
         # the second part is a new order line with quantity=quantityrec
         # (entirely received)

-        my $sth=$dbh->prepare("
+        my $query = q|

             UPDATE aqorders
             SET quantity = ?,
             UPDATE aqorders
             SET quantity = ?,

-                orderstatus = 'partial'
-            WHERE ordernumber = ?
-        ");
+                orderstatus = 'partial'|;
+        $query .= q|, order_internalnote = ?| if defined $order_internalnote;
+        $query .= q|, order_vendornote = ?| if defined $order_vendornote;
+        $query .= q| WHERE ordernumber = ?|;
+        my $sth = $dbh->prepare($query);

 
 

-        $sth->execute($order->{quantity} - $quantrec, $ordernumber);
+        $sth->execute(
+            $order->{quantity} - $quantrec,
+            ( defined $order_internalnote ? $order_internalnote : () ),
+            ( defined $order_vendornote ? $order_vendornote : () ),
+            $ordernumber
+        );

 
         delete $order->{'ordernumber'};
         $order->{'budget_id'} = ( $budget_id || $order->{'budget_id'} );
 
         delete $order->{'ordernumber'};
         $order->{'budget_id'} = ( $budget_id || $order->{'budget_id'} );


@@ -1491,11 +1542,27 @@ q{SELECT * FROM aqorders WHERE biblionumber=? AND aqorders.ordernumber=?},
             }
         }
     } else {
             }
         }
     } else {

-        my $sth=$dbh->prepare("update aqorders
-                            set quantityreceived=?,datereceived=?,invoiceid=?,
-                                unitprice=?,rrp=?,ecost=?,budget_id=?,orderstatus='complete'
-                            where biblionumber=? and ordernumber=?");
-        $sth->execute($quantrec,$datereceived,$invoiceid,$cost,$rrp,$ecost,$budget_id,$biblionumber,$ordernumber);
+        my $query = q|
+            update aqorders
+            set quantityreceived=?,datereceived=?,invoiceid=?,
+                unitprice=?,rrp=?,ecost=?,budget_id=?,orderstatus='complete'|;
+        $query .= q|, order_internalnote = ?| if defined $order_internalnote;
+        $query .= q|, order_vendornote = ?| if defined $order_vendornote;
+        $query .= q| where biblionumber=? and ordernumber=?|;
+        my $sth = $dbh->prepare( $query );
+        $sth->execute(
+            $quantrec,
+            $datereceived,
+            $invoiceid,
+            $cost,
+            $rrp,
+            $ecost,
+            $budget_id,
+            ( defined $order_internalnote ? $order_internalnote : () ),
+            ( defined $order_vendornote ? $order_vendornote : () ),
+            $biblionumber,
+            $ordernumber
+        );

     }
     return ($datereceived, $new_ordernumber);
 }
     }
     return ($datereceived, $new_ordernumber);
 }


@@ -1629,12 +1696,14 @@ sub CancelReceipt {
     basketno => $basketno,
     owner => $owner,
     pending => $pending
     basketno => $basketno,
     owner => $owner,
     pending => $pending

+    ordered => $ordered

 });
 
 Searches for orders.
 
 C<$owner> Finds order for the logged in user.
 C<$pending> Finds pending orders. Ignores completed and cancelled orders.
 });
 
 Searches for orders.
 
 C<$owner> Finds order for the logged in user.
 C<$pending> Finds pending orders. Ignores completed and cancelled orders.

+C<$ordered> Finds orders to receive only (status 'ordered' or 'partial').

 
 
 C<@results> is an array of references-to-hash with the keys are fields
 
 
 C<@results> is an array of references-to-hash with the keys are fields


@@ -1653,6 +1722,8 @@ sub SearchOrders {
     my $basketgroupname = $params->{basketgroupname};
     my $owner = $params->{owner};
     my $pending = $params->{pending};
     my $basketgroupname = $params->{basketgroupname};
     my $owner = $params->{owner};
     my $pending = $params->{pending};

+    my $ordered = $params->{ordered};
+    my $biblionumber = $params->{biblionumber};

 
     my $dbh = C4::Context->dbh;
     my @args = ();
 
     my $dbh = C4::Context->dbh;
     my @args = ();


@@ -1664,6 +1735,7 @@ sub SearchOrders {
                biblioitems.isbn,
                biblioitems.biblioitemnumber,
                aqbasket.authorisedby,
                biblioitems.isbn,
                biblioitems.biblioitemnumber,
                aqbasket.authorisedby,

+               aqbasket.booksellerid,

                aqbasket.closedate,
                aqbasket.creationdate,
                aqbasket.basketname,
                aqbasket.closedate,
                aqbasket.creationdate,
                aqbasket.basketname,


@@ -1676,12 +1748,23 @@ 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
             LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
             LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
             LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber

-        WHERE (datecancellationprinted is NULL)

     };
 
     };
 

+    # If we search on ordernumber, we retrieve the transfered order if a transfer has been done.

     $query .= q{
     $query .= q{

-        AND (quantity > quantityreceived OR quantityreceived is NULL)
-    } if $pending;
+            LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
+    } if $ordernumber;
+
+    $query .= q{
+        WHERE (datecancellationprinted is NULL)
+    };
+
+    if ( $pending or $ordered ) {
+        $query .= q{ AND (quantity > quantityreceived OR quantityreceived is NULL)};
+    }
+    if ( $ordered ) {
+        $query .= q{ AND aqorders.orderstatus IN ( "ordered", "partial" )};
+    }

 
     my $userenv = C4::Context->userenv;
     if ( C4::Context->preference("IndependentBranches") ) {
 
     my $userenv = C4::Context->userenv;
     if ( C4::Context->preference("IndependentBranches") ) {


@@ -1697,8 +1780,12 @@ sub SearchOrders {
     }
 
     if ( $ordernumber ) {
     }
 
     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 ?)';
     }
     if( $search ) {
         $query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)';


@@ -2014,6 +2101,9 @@ sub GetLateOrders {
     SELECT aqbasket.basketno,
         aqorders.ordernumber,
         DATE(aqbasket.closedate)  AS orderdate,
     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,
         aqorders.rrp              AS unitpricesupplier,
         aqorders.ecost            AS unitpricelib,
         aqorders.claims_count     AS claims_count,


@@ -2034,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 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
         WHERE aqorders.basketno = aqbasket.basketno
         AND ( datereceived = ''
             OR datereceived IS NULL


@@ -2105,8 +2196,6 @@ sub GetLateOrders {
     $sth->execute(@query_params);
     my @results;
     while (my $data = $sth->fetchrow_hashref) {
     $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;
         push @results, $data;
     }
     return @results;


@@ -2180,6 +2269,8 @@ sub GetHistory {
     my $orderstatus = $params{orderstatus};
     my $biblionumber = $params{biblionumber};
     my $get_canceled_order = $params{get_canceled_order} || 0;
     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;
 
     my @order_loop;
     my $total_qty         = 0;


@@ -2307,6 +2398,17 @@ sub GetHistory {
         push @query_params, "%$basketgroupname%";
     }
 
         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 ='' ) ";
     if ( C4::Context->preference("IndependentBranches") ) {
         unless ( C4::Context->IsSuperLibrarian() ) {
             $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";


@@ -2487,6 +2589,13 @@ sub GetInvoices {
               NULL
             )
           ) AS receivedbiblios,
               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
           SUM(aqorders.quantityreceived) AS receiveditems
         FROM aqinvoices
           LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid