From ebee2a0b712617d526add6393a312a9a76f89c68 Mon Sep 17 00:00:00 2001
From: Jonathan Druart <jonathan.druart@biblibre.com>
Date: Fri, 18 Jul 2014 12:34:07 +0200
Subject: [PATCH] Bug 12601: Add a foreign key for aqorders.budget_id

When creating an order, the fund value is mandatory but the DB structure
does not show this constraint.

The aqorders.budget_id field should be linked to aqbudgets.budget_id.

The updatedatabase.pl (entry 3.01.00.077) commented this constraint,
certainly for a retro compatibility reason.

Actually I found some cases (in production) where aqorders.budget_id is
set to "0". To add this constraint, we should add a temporary fund to
linked orphan orders.

Test plan:
0/ Verify it is not possible to create an order not linked to a fund via
the Koha interface.
1/ Using your SQL CLI (or equivalent), create or update 1+ orders and set
"0" in the budget_id field.
2/ Execute the updatedabase script.
3/ Verify that your order is linked to a new fund (which is linked to a
new budget).
4/ Verify the constraint has correctly been added (show create table
aqorders).

Signed-off-by: Paola Rossi <paola.rossi@cineca.it>
---
 installer/data/mysql/kohastructure.sql             |    1 +
 installer/data/mysql/updatedatabase.pl             |   41 ++++++++++++++++++++
 .../prog/en/modules/admin/aqbudgets.tt             |    1 +
 3 files changed, 43 insertions(+)

diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index e33c168..4ae9296 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -3051,6 +3051,7 @@ CREATE TABLE `aqorders` ( -- information related to the basket line items
   KEY `basketno` (`basketno`),
   KEY `biblionumber` (`biblionumber`),
   KEY `budget_id` (`budget_id`),
+  CONSTRAINT `aqorders_budget_id_fk` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE,
   CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
   CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE,
   CONSTRAINT aqorders_ibfk_3 FOREIGN KEY (invoiceid) REFERENCES aqinvoices (invoiceid) ON DELETE SET NULL ON UPDATE CASCADE,
diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
index 95832e5..72fe6e3 100755
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -8820,6 +8820,47 @@ if ( CheckVersion($DBversion) ) {
     SetVersion ($DBversion);
 }
 
+$DBversion = "3.17.00.030";
+if ( CheckVersion($DBversion) ) {
+    my $number_of_orders_not_linked = $dbh->selectcol_arrayref(q|
+        SELECT COUNT(*)
+        FROM aqorders o
+        WHERE NOT EXISTS (
+            SELECT NULL
+            FROM aqbudgets b
+            WHERE b.budget_id = o.budget_id
+        );
+    |);
+
+    if ( $number_of_orders_not_linked->[0] > 0 ) {
+        $dbh->do(q|
+            INSERT INTO aqbudgetperiods(budget_period_startdate, budget_period_enddate, budget_period_active, budget_period_description, budget_period_total) VALUES ( CAST(NOW() AS date), CAST(NOW() AS date), 0, "WARNING: This budget has been automatically created by the updatedatabase script, please see bug 12601 for more information", 0)
+        |);
+        my $budget_period_id = $dbh->last_insert_id( undef, undef, 'aqbudgetperiods', undef );
+        $dbh->do(qq|
+            INSERT INTO aqbudgets(budget_code, budget_name, budget_amount, budget_period_id) VALUES ( "BACKUP_TMP", "WARNING: fund created by the updatedatabase script, please see bug 12601", 0, $budget_period_id );
+        |);
+        my $budget_id = $dbh->last_insert_id( undef, undef, 'aqbudgets', undef );
+        $dbh->do(qq|
+            UPDATE aqorders o
+            SET budget_id = $budget_id
+            WHERE NOT EXISTS (
+                SELECT NULL
+                FROM aqbudgets b
+                WHERE b.budget_id = o.budget_id
+            )
+        |);
+    }
+
+    $dbh->do(q|
+        ALTER TABLE aqorders
+        ADD CONSTRAINT aqorders_budget_id_fk FOREIGN KEY (budget_id) REFERENCES aqbudgets(budget_id) ON DELETE CASCADE ON UPDATE CASCADE
+    |);
+
+    print "Upgrade to $DBversion done (Bug 12601 - Add new foreign key aqorders.budget_id" . ( ( $number_of_orders_not_linked->[0] > 0 )  ? ' WARNING: temporary budget and fund have been created (search for "BACKUP_TMP"). At least one of your order was not linked to a budget' : '' ) . ")\n";
+    SetVersion($DBversion);
+}
+
 =head1 FUNCTIONS
 
 =head2 TableExists($table)
diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/aqbudgets.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/aqbudgets.tt
index b0c4344..78299e9 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/aqbudgets.tt
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/aqbudgets.tt
@@ -593,6 +593,7 @@ var MSG_PARENT_BENEATH_BUDGET = "- " + _("New budget-parent is beneath budget")
 
 [% IF op == 'delete_confirm' %]
 <div class="dialog alert"> <h3>Delete fund [% budget_name %]?</h3>
+If you delete this fund, all orders linked to this fund will be deleted!
 <table>
     <tr>
     <th scope="row">Fund amount:</th>
-- 
1.7.10.4