From e6cd6e705548bffe64c08ff4bef4f9c8a929333e Mon Sep 17 00:00:00 2001
From: Jonathan Druart <jonathan.druart@biblibre.com>
Date: Thu, 4 Apr 2013 12:04:46 +0200
Subject: [PATCH][SIGNED OFF] Bug 9987: Remove DB field aqorders.biblioitemnunmber

The DB field aqorders.biblioitemnumber seems to be unused except to get
the itype on the spent.pl page.
This information can be retrieved uising another sql join.

Test plan:
Try a complete workflow in the acquisition module: create an order,
receive it, play with the syspref AcqCreateItem.
Check that no regression is found and that the data for existing
orders don't change.

Signed-off-by: Mathieu Saby <mathieu.saby@univ-rennes2.fr>
---
 C4/Acquisition.pm                                  |   65 ++------------------
 C4/Letters.pm                                      |    2 +-
 acqui/addorder.pl                                  |    4 +-
 acqui/finishreceive.pl                             |    1 -
 acqui/neworderempty.pl                             |    1 -
 acqui/orderreceive.pl                              |    1 -
 acqui/spent.pl                                     |    7 ++-
 installer/data/mysql/kohastructure.sql             |    1 -
 installer/data/mysql/updatedatabase.pl             |    8 +++
 .../prog/en/modules/acqui/neworderempty.tt         |    1 -
 .../prog/en/modules/acqui/orderreceive.tt          |    1 -
 t/db_dependent/lib/KohaTest/Acquisition.pm         |    1 -
 12 files changed, 18 insertions(+), 75 deletions(-)

diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm
index b78a51e..4f9ea9f 100644
--- a/C4/Acquisition.pm
+++ b/C4/Acquisition.pm
@@ -54,9 +54,9 @@ BEGIN {
         &GetBasketgroups &ReOpenBasketgroup
 
         &NewOrder &DelOrder &ModOrder &GetPendingOrders &GetOrder &GetOrders
-        &GetOrderNumber &GetLateOrders &GetOrderFromItemnumber
+        &GetLateOrders &GetOrderFromItemnumber
         &SearchOrder &GetHistory &GetRecentAcqui
-        &ModReceiveOrder &CancelReceipt &ModOrderBiblioitemNumber
+        &ModReceiveOrder &CancelReceipt
         &GetCancelledOrders
         &GetLastOrderNotReceivedFromSubscriptionid &GetLastOrderReceivedFromSubscriptionid
         &NewOrderItem &ModOrderItem &ModItemOrder
@@ -951,39 +951,6 @@ sub GetOrders {
 
 #------------------------------------------------------------#
 
-=head3 GetOrderNumber
-
-  $ordernumber = &GetOrderNumber($biblioitemnumber, $biblionumber);
-
-Looks up the ordernumber with the given biblionumber and biblioitemnumber.
-
-Returns the number of this order.
-
-=over
-
-=item C<$ordernumber> is the order number.
-
-=back
-
-=cut
-
-sub GetOrderNumber {
-    my ( $biblionumber,$biblioitemnumber ) = @_;
-    my $dbh = C4::Context->dbh;
-    my $query = "
-        SELECT ordernumber
-        FROM   aqorders
-        WHERE  biblionumber=?
-        AND    biblioitemnumber=?
-    ";
-    my $sth = $dbh->prepare($query);
-    $sth->execute( $biblionumber, $biblioitemnumber );
-
-    return $sth->fetchrow;
-}
-
-#------------------------------------------------------------#
-
 =head3 GetOrder
 
   $order = &GetOrder($ordernumber);
@@ -1100,7 +1067,7 @@ Else, the upcoming July 1st is used.
 
 =item defaults entrydate to Now
 
-The following keys are used: "biblionumber", "title", "basketno", "quantity", "notes", "biblioitemnumber", "rrp", "ecost", "gstrate", "unitprice", "subscription", "sort1", "sort2", "booksellerinvoicenumber", "listprice", "budgetdate", "purchaseordernumber", "branchcode", "booksellerinvoicenumber", "budget_id".
+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".
 
 =back
 
@@ -1268,29 +1235,6 @@ sub ModItemOrder {
 
 #------------------------------------------------------------#
 
-
-=head3 ModOrderBibliotemNumber
-
-  &ModOrderBiblioitemNumber($biblioitemnumber,$ordernumber, $biblionumber);
-
-Modifies the biblioitemnumber for an existing order.
-Updates the order with order number C<$ordernum> and biblionumber C<$biblionumber>.
-
-=cut
-
-#FIXME: is this used at all?
-sub ModOrderBiblioitemNumber {
-    my ($biblioitemnumber,$ordernumber, $biblionumber) = @_;
-    my $dbh = C4::Context->dbh;
-    my $query = "
-    UPDATE aqorders
-    SET    biblioitemnumber = ?
-    WHERE  ordernumber = ?
-    AND biblionumber =  ?";
-    my $sth = $dbh->prepare($query);
-    $sth->execute( $biblioitemnumber, $ordernumber, $biblionumber );
-}
-
 =head3 GetCancelledOrders
 
   my @orders = GetCancelledOrders($basketno, $orderby);
@@ -1332,8 +1276,7 @@ sub GetCancelledOrders {
 =head3 ModReceiveOrder
 
   &ModReceiveOrder($biblionumber, $ordernumber, $quantityreceived, $user,
-    $unitprice, $invoiceid, $biblioitemnumber,
-    $bookfund, $rrp, \@received_itemnumbers);
+    $cost, $ecost, $invoiceid, rrp, budget_id, datereceived, \@received_itemnumbers);
 
 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
diff --git a/C4/Letters.pm b/C4/Letters.pm
index fdbb473..21fbad0 100644
--- a/C4/Letters.pm
+++ b/C4/Letters.pm
@@ -318,7 +318,7 @@ sub SendAlerts {
             FROM aqorders
             LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
             LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
-            LEFT JOIN biblioitems ON aqorders.biblioitemnumber=biblioitems.biblioitemnumber
+            LEFT JOIN biblioitems ON aqorders.biblionumber=biblioitems.biblionumber
             LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
             WHERE aqorders.ordernumber IN (
             }
diff --git a/acqui/addorder.pl b/acqui/addorder.pl
index 077adaf..42324aa 100755
--- a/acqui/addorder.pl
+++ b/acqui/addorder.pl
@@ -162,7 +162,6 @@ my $user = $input->remote_user;
 # modify if $quantity>=0 and $existing='yes'
 # delete if $quantity has been set to 0 by the librarian
 # delete biblio if delbiblio has been set to 1 by the librarian
-my $bibitemnum;
 if ( $orderinfo->{quantity} ne '0' ) {
     #TODO:check to see if biblio exists
     unless ( $$orderinfo{biblionumber} ) {
@@ -194,8 +193,7 @@ if ( $orderinfo->{quantity} ne '0' ) {
         if ($$orderinfo{suggestionid}) {
             ModSuggestion( {suggestionid=>$$orderinfo{suggestionid}, STATUS=>'ORDERED', biblionumber=>$biblionumber} );
         }
-		$orderinfo->{biblioitemnumber}=$bibitemnum;
-		$orderinfo->{biblionumber}=$biblionumber;
+        $orderinfo->{biblionumber}=$biblionumber;
     }
 
     $orderinfo->{unitprice} = $orderinfo->{ecost} if not defined $orderinfo->{unitprice} or $orderinfo->{unitprice} eq '';
diff --git a/acqui/finishreceive.pl b/acqui/finishreceive.pl
index 5ca2c98..f9c8532 100755
--- a/acqui/finishreceive.pl
+++ b/acqui/finishreceive.pl
@@ -40,7 +40,6 @@ checkauth($input, 0, $flagsrequired, 'intranet');
 
 my $user             = $input->remote_user;
 my $biblionumber     = $input->param('biblionumber');
-my $biblioitemnumber = $input->param('biblioitemnumber');
 my $ordernumber      = $input->param('ordernumber');
 my $origquantityrec  = $input->param('origquantityrec');
 my $quantityrec      = $input->param('quantityrec');
diff --git a/acqui/neworderempty.pl b/acqui/neworderempty.pl
index b1c9afa..c51e5fd 100755
--- a/acqui/neworderempty.pl
+++ b/acqui/neworderempty.pl
@@ -379,7 +379,6 @@ $template->param(
     biblionumber         => $biblionumber,
     uncertainprice       => $data->{'uncertainprice'},
     authorisedbyname     => $borrower->{'firstname'} . " " . $borrower->{'surname'},
-    biblioitemnumber     => $data->{'biblioitemnumber'},
     discount_2dp         => sprintf( "%.2f",  $bookseller->{'discount'} ) ,   # for display
     discount             => $bookseller->{'discount'},
     orderdiscount_2dp    => sprintf( "%.2f", $data->{'discount'} || 0 ),
diff --git a/acqui/orderreceive.pl b/acqui/orderreceive.pl
index fc79ccb..8fc760b 100755
--- a/acqui/orderreceive.pl
+++ b/acqui/orderreceive.pl
@@ -198,7 +198,6 @@ $template->param(
     count                 => 1,
     biblionumber          => $order->{'biblionumber'},
     ordernumber           => $order->{'ordernumber'},
-    biblioitemnumber      => $order->{'biblioitemnumber'},
     subscriptionid        => $order->{subscriptionid},
     booksellerid          => $order->{'booksellerid'},
     freight               => $freight,
diff --git a/acqui/spent.pl b/acqui/spent.pl
index 2d555f9..c3c4693 100755
--- a/acqui/spent.pl
+++ b/acqui/spent.pl
@@ -67,15 +67,16 @@ SELECT
     datereceived,
     aqorders.biblionumber
 FROM (aqorders, aqbasket)
-LEFT JOIN items ON
-    items.biblioitemnumber=aqorders.biblioitemnumber
 LEFT JOIN biblio ON
     biblio.biblionumber=aqorders.biblionumber
+LEFT JOIN items ON
+    biblio.biblionumber = items.biblionumber
 LEFT JOIN aqorders_items ON
-    aqorders.ordernumber=aqorders_items.ordernumber
+     items.itemnumber = aqorders_items.itemnumber
 LEFT JOIN aqinvoices ON
     aqorders.invoiceid = aqinvoices.invoiceid
 WHERE
+    aqorders.ordernumber=aqorders_items.ordernumber AND
     aqorders.basketno=aqbasket.basketno AND
     budget_id=? AND
     (datecancellationprinted IS NULL OR
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index a6760d4..431da72 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -2777,7 +2777,6 @@ CREATE TABLE `aqorders` ( -- information related to the basket line items
   `supplierreference` mediumtext, -- not used? always NULL
   `purchaseordernumber` mediumtext, -- not used? always NULL
   `basketno` int(11) default NULL, -- links this order line to a specific basket (aqbasket.basketno)
-  `biblioitemnumber` int(11) default NULL, -- links this order line the biblioitems table (biblioitems.biblioitemnumber)
   `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this order line was last modified
   `rrp` decimal(13,2) default NULL, -- the replacement cost for this line item
   `ecost` decimal(13,2) default NULL, -- the estimated cost for this line item
diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
index 7c97a51..40ee6f6 100755
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -6772,6 +6772,14 @@ if ( CheckVersion($DBversion) ) {
 }
 
 
+
+$DBversion = "3.13.00.XXX";
+if ( CheckVersion($DBversion) ) {
+    $dbh->do("ALTER TABLE aqorders DROP COLUMN biblioitemnumber");
+    print "Upgrade to $DBversion done (Bug 9987 - Drop column aqorders.biblioitemnumber)\n";
+    SetVersion($DBversion);
+}
+
 =head1 FUNCTIONS
 
 =head2 TableExists($table)
diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/neworderempty.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/neworderempty.tt
index 549bf62..95b5093 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/neworderempty.tt
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/neworderempty.tt
@@ -243,7 +243,6 @@ $(document).ready(function()
         <input type="hidden" name="basketno" value="[% basketno %]" />
         <input type="hidden" name="booksellerid" value="[% booksellerid %]" />
         <input type="hidden" name="biblionumber" value="[% biblionumber %]" />
-        <input type="hidden" name="biblioitemnumber" value="[% biblioitemnumber %]" />
         <input type="hidden" name="listinc" id="listinc" value="[% listincgst %]" />
         <input type="hidden" name="applygst" id="applygst" value="[% gstreg %]" />
         <input type="hidden" name="invoiceincgst" id="invoiceincgst" value="[% invoiceincgst %]" />
diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/orderreceive.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/orderreceive.tt
index 9e16755..8e14f00 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/orderreceive.tt
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/orderreceive.tt
@@ -283,7 +283,6 @@ function IEEventHandler_KeyDown() {
     <input type="hidden" name="biblionumber" value="[% biblionumber %]" />
     <input type="hidden" name="invoiceid" value="[% invoiceid %]" />
     <input type="hidden" name="ordernumber" value="[% ordernumber %]" />
-    <input type="hidden" name="biblioitemnumber" value="[% biblioitemnumber %]" />
     <input type="hidden" name="booksellerid" value="[% booksellerid %]" />
     <input type="hidden" name="datereceived" value="[% datereceived_iso %]" />
     <input type="hidden" name="gstrate" value="[% gstrate %]" />
diff --git a/t/db_dependent/lib/KohaTest/Acquisition.pm b/t/db_dependent/lib/KohaTest/Acquisition.pm
index e7618c9..bb96ddf 100644
--- a/t/db_dependent/lib/KohaTest/Acquisition.pm
+++ b/t/db_dependent/lib/KohaTest/Acquisition.pm
@@ -22,7 +22,6 @@ sub methods : Test( 1 ) {
                        CloseBasket 
                        GetPendingOrders 
                        GetOrders 
-                       GetOrderNumber 
                        GetOrder 
                        NewOrder 
                        ModOrder 
-- 
1.7.10.4