X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=sql%2FPg-upgrade2%2Fcurrencies.pl;h=30f779537a47911efec1d68d3428d9f2dfe0343a;hb=958b82d600cebec78ff208257b1fd348ee0439bf;hp=a2c498ec07224fb3133c4f4430f12808883f8856;hpb=41276623722df9317163274551400fc42a2722e0;p=kivitendo-erp.git

diff --git a/sql/Pg-upgrade2/currencies.pl b/sql/Pg-upgrade2/currencies.pl
index a2c498ec0..30f779537 100644
--- a/sql/Pg-upgrade2/currencies.pl
+++ b/sql/Pg-upgrade2/currencies.pl
@@ -20,7 +20,7 @@ sub run {
     print_no_default_currency();
     return 2;
   } else {
-    if (length($main::form->{defaultcurrency}) == 0){
+    if (!defined $::form->{defaultcurrency} || length($main::form->{defaultcurrency}) == 0){
       $main::form->{defaultcurrency} = (split m/:/, $currencies)[0];
     }
   }
@@ -69,49 +69,51 @@ sub run {
     return 2;
   }
 
-  if ($main::form->{continue_options} eq 'break_up') {
-    return 0;
-  }
+  if (defined $::form->{continue_options}) {
+    if ($::form->{continue_options} eq 'break_up') {
+      return 0;
+    }
 
-  if ($main::form->{continue_options} eq 'insert') {
-    for my $i (0..($rowcount-1)){
-      push @currency_array, $main::form->{"curr_$i"};
+    if ($::form->{continue_options} eq 'insert') {
+      for my $i (0..($rowcount-1)){
+        push @currency_array, $main::form->{"curr_$i"};
+      }
+      create_and_fill_table($self, @currency_array);
+      return 1;
     }
-    create_and_fill_table($self, @currency_array);
-    return 1;
-  }
 
-  my $still_orphaned;
-  if ($main::form->{continue_options} eq 'replace') {
-    for my $i (0..($rowcount - 1)){
-      $still_orphaned = 1;
-      for my $item (@currency_array){
-        if ($main::form->{"curr_$i"} eq $item){
-          $still_orphaned = 0;
-          $query = qq|DELETE FROM exchangerate WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
-          $self->db_query($query);
-          $query = qq|UPDATE ap SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
-          $self->db_query($query);
-          $query = qq|UPDATE ar SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
-          $self->db_query($query);
-          $query = qq|UPDATE oe SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
-          $self->db_query($query);
-          $query = qq|UPDATE customer SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
-          $self->db_query($query);
-          $query = qq|UPDATE delivery_orders SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
-          $self->db_query($query);
-          $query = qq|UPDATE vendor SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
-          $self->db_query($query);
-          last;
+    my $still_orphaned;
+    if ($::form->{continue_options} eq 'replace') {
+      for my $i (0..($rowcount - 1)){
+        $still_orphaned = 1;
+        for my $item (@currency_array){
+          if ($main::form->{"curr_$i"} eq $item){
+            $still_orphaned = 0;
+            $query = qq|DELETE FROM exchangerate WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
+            $self->db_query($query);
+            $query = qq|UPDATE ap SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
+            $self->db_query($query);
+            $query = qq|UPDATE ar SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
+            $self->db_query($query);
+            $query = qq|UPDATE oe SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
+            $self->db_query($query);
+            $query = qq|UPDATE customer SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
+            $self->db_query($query);
+            $query = qq|UPDATE delivery_orders SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
+            $self->db_query($query);
+            $query = qq|UPDATE vendor SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
+            $self->db_query($query);
+            last;
+          }
+        }
+        if ($still_orphaned){
+          $main::form->{continue_options} = '';
+          return do_update();
         }
       }
-      if ($still_orphaned){
-        $main::form->{continue_options} = '';
-        return do_update();
-      }
+      create_and_fill_table($self, @currency_array);
+      return 1;
     }
-    create_and_fill_table($self, @currency_array);
-    return 1;
   }
 
   #No orphaned currencies, so create table:
@@ -122,10 +124,11 @@ sub run {
 sub create_and_fill_table {
   my $self = shift;
   #Create an fill table currencies:
-  my $query = qq|CREATE TABLE currencies (id INTEGER DEFAULT nextval(('id'::text)::regclass) UNIQUE NOT NULL, curr TEXT PRIMARY KEY)|;
+  my $query = qq|CREATE TABLE currencies (id   SERIAL        PRIMARY KEY,
+                                          name TEXT NOT NULL UNIQUE)|;
   $self->db_query($query);
   foreach my $item ( @_ ) {
-    $query = qq|INSERT INTO currencies (curr) VALUES ('| . $item . qq|')|;
+    $query = qq|INSERT INTO currencies (name) VALUES ('| . $item . qq|')|;
     $self->db_query($query);
   }
 
@@ -140,34 +143,34 @@ sub create_and_fill_table {
   $self->db_query($query);
 
   #Check wheather defaultcurrency is already in table currencies:
-  $query = qq|SELECT curr FROM currencies WHERE curr = '| . $main::form->{defaultcurrency} . qq|'|;
+  $query = qq|SELECT name FROM currencies WHERE name = '| . $main::form->{defaultcurrency} . qq|'|;
   my ($insert_default) = $self->dbh->selectrow_array($query);
 
   if (!$insert_default) {
-    $query = qq|INSERT INTO currencies (curr) VALUES ('| . $main::form->{defaultcurrency} . qq|')|;
+    $query = qq|INSERT INTO currencies (name) VALUES ('| . $main::form->{defaultcurrency} . qq|')|;
     $self->db_query($query);
   }
 
-  #Create a new columns currency and update with curr.id:
-  $query = qq|ALTER TABLE ap ADD currency INTEGER;
-              ALTER TABLE ar ADD currency INTEGER;
-              ALTER TABLE oe ADD currency INTEGER;
-              ALTER TABLE customer ADD currency INTEGER;
-              ALTER TABLE delivery_orders ADD currency INTEGER;
-              ALTER TABLE exchangerate ADD currency INTEGER;
-              ALTER TABLE vendor ADD currency INTEGER;
-              ALTER TABLE defaults ADD currency INTEGER;|;
+  #Create a new columns currency_id and update with curr.id:
+  $query = qq|ALTER TABLE ap ADD currency_id INTEGER;
+              ALTER TABLE ar ADD currency_id INTEGER;
+              ALTER TABLE oe ADD currency_id INTEGER;
+              ALTER TABLE customer ADD currency_id INTEGER;
+              ALTER TABLE delivery_orders ADD currency_id INTEGER;
+              ALTER TABLE exchangerate ADD currency_id INTEGER;
+              ALTER TABLE vendor ADD currency_id INTEGER;
+              ALTER TABLE defaults ADD currency_id INTEGER;|;
   $self->db_query($query);
   #Set defaultcurrency:
-  $query = qq|UPDATE defaults SET currency= (SELECT id FROM currencies WHERE curr = '| . $main::form->{defaultcurrency} . qq|')|;
+  $query = qq|UPDATE defaults SET currency_id= (SELECT id FROM currencies WHERE name = '| . $main::form->{defaultcurrency} . qq|')|;
   $self->db_query($query);
-  $query = qq|UPDATE ap SET currency = (SELECT id FROM currencies c WHERE c.curr = ap.curr);
-              UPDATE ar SET currency = (SELECT id FROM currencies c WHERE c.curr = ar.curr);
-              UPDATE oe SET currency = (SELECT id FROM currencies c WHERE c.curr = oe.curr);
-              UPDATE customer SET currency = (SELECT id FROM currencies c WHERE c.curr = customer.curr);
-              UPDATE delivery_orders SET currency = (SELECT id FROM currencies c WHERE c.curr = delivery_orders.curr);
-              UPDATE exchangerate SET currency = (SELECT id FROM currencies c WHERE c.curr = exchangerate.curr);
-              UPDATE vendor SET currency = (SELECT id FROM currencies c WHERE c.curr = vendor.curr);|;
+  $query = qq|UPDATE ap SET currency_id = (SELECT id FROM currencies c WHERE c.name = ap.curr);
+              UPDATE ar SET currency_id = (SELECT id FROM currencies c WHERE c.name = ar.curr);
+              UPDATE oe SET currency_id = (SELECT id FROM currencies c WHERE c.name = oe.curr);
+              UPDATE customer SET currency_id = (SELECT id FROM currencies c WHERE c.name = customer.curr);
+              UPDATE delivery_orders SET currency_id = (SELECT id FROM currencies c WHERE c.name = delivery_orders.curr);
+              UPDATE exchangerate SET currency_id = (SELECT id FROM currencies c WHERE c.name = exchangerate.curr);
+              UPDATE vendor SET currency_id = (SELECT id FROM currencies c WHERE c.name = vendor.curr);|;
   $self->db_query($query);
 
   #Drop column 'curr':
@@ -181,37 +184,26 @@ sub create_and_fill_table {
               ALTER TABLE defaults DROP COLUMN curr;|;
   $self->db_query($query);
 
-  #Rename currency to curr:
-  $query = qq|ALTER TABLE defaults RENAME COLUMN currency TO curr;
-              ALTER TABLE ap RENAME COLUMN currency TO curr;
-              ALTER TABLE ar RENAME COLUMN currency TO curr;
-              ALTER TABLE oe RENAME COLUMN currency TO curr;
-              ALTER TABLE customer RENAME COLUMN currency TO curr;
-              ALTER TABLE delivery_orders RENAME COLUMN currency TO curr;
-              ALTER TABLE exchangerate RENAME COLUMN currency TO curr;
-              ALTER TABLE vendor RENAME COLUMN currency TO curr;|;
-  $self->db_query($query);
-
   #Set NOT NULL constraints:
-  $query = qq|ALTER TABLE ap ALTER COLUMN curr SET NOT NULL;
-              ALTER TABLE ar ALTER COLUMN curr SET NOT NULL;
-              ALTER TABLE oe ALTER COLUMN curr SET NOT NULL;
-              ALTER TABLE customer ALTER COLUMN curr SET NOT NULL;
-              ALTER TABLE delivery_orders ALTER COLUMN curr SET NOT NULL;
-              ALTER TABLE exchangerate ALTER COLUMN curr SET NOT NULL;
-              ALTER TABLE vendor ALTER COLUMN curr SET NOT NULL;
-              ALTER TABLE defaults ALTER COLUMN curr SET NOT NULL;|;
+  $query = qq|ALTER TABLE ap ALTER COLUMN currency_id SET NOT NULL;
+              ALTER TABLE ar ALTER COLUMN currency_id SET NOT NULL;
+              ALTER TABLE oe ALTER COLUMN currency_id SET NOT NULL;
+              ALTER TABLE customer ALTER COLUMN currency_id SET NOT NULL;
+              ALTER TABLE delivery_orders ALTER COLUMN currency_id SET NOT NULL;
+              ALTER TABLE exchangerate ALTER COLUMN currency_id SET NOT NULL;
+              ALTER TABLE vendor ALTER COLUMN currency_id SET NOT NULL;
+              ALTER TABLE defaults ALTER COLUMN currency_id SET NOT NULL;|;
   $self->db_query($query);
 
   #Set foreign keys:
-  $query = qq|ALTER TABLE ap ADD FOREIGN KEY (curr) REFERENCES currencies(id);
-              ALTER TABLE ar ADD FOREIGN KEY (curr) REFERENCES currencies(id);
-              ALTER TABLE oe ADD FOREIGN KEY (curr) REFERENCES currencies(id);
-              ALTER TABLE customer ADD FOREIGN KEY (curr) REFERENCES currencies(id);
-              ALTER TABLE delivery_orders ADD FOREIGN KEY (curr) REFERENCES currencies(id);
-              ALTER TABLE exchangerate ADD FOREIGN KEY (curr) REFERENCES currencies(id);
-              ALTER TABLE vendor ADD FOREIGN KEY (curr) REFERENCES currencies(id);
-              ALTER TABLE defaults ADD FOREIGN KEY (curr) REFERENCES currencies(id);|;
+  $query = qq|ALTER TABLE ap ADD FOREIGN KEY (currency_id) REFERENCES currencies(id);
+              ALTER TABLE ar ADD FOREIGN KEY (currency_id) REFERENCES currencies(id);
+              ALTER TABLE oe ADD FOREIGN KEY (currency_id) REFERENCES currencies(id);
+              ALTER TABLE customer ADD FOREIGN KEY (currency_id) REFERENCES currencies(id);
+              ALTER TABLE delivery_orders ADD FOREIGN KEY (currency_id) REFERENCES currencies(id);
+              ALTER TABLE exchangerate ADD FOREIGN KEY (currency_id) REFERENCES currencies(id);
+              ALTER TABLE vendor ADD FOREIGN KEY (currency_id) REFERENCES currencies(id);
+              ALTER TABLE defaults ADD FOREIGN KEY (currency_id) REFERENCES currencies(id);|;
   $self->db_query($query);
 
 };