Projekt

Allgemein

Profil

« Zurück | Weiter » 

Revision 1ee1e429

Von Moritz Bunkus vor mehr als 11 Jahren hinzugefügt

  • ID 1ee1e4294fd04bdb3593ac7c494cb80f693c3e16
  • Vorgänger fd8acf8e
  • Nachfolger c1a8c632

Revert "Anpassung Upgrade-Script an neue Synthax"

This reverts commit 41276623722df9317163274551400fc42a2722e0.

Unterschiede anzeigen:

sql/Pg-upgrade2/currencies.pl
1 1 
# @tag: currencies
2 2 
# @description: Erstellt neue Tabelle currencies. Währungen können dann einfacher eingegeben und unkritisch geändert werden.
3 3 
# @depends: release_3_0_0 rm_whitespaces
4 

  		




  5
  
  
    
package SL::DBUpgrade2::currencies;


  		




  
  4
  
    
# @charset: utf-8


  		




  6
  5
  
    

  		




  7
  6
  
    
use utf8;


  		




  8
  7
  
    
use strict;


  		




  9
  8
  
    

  		




  10
  
  
    
use parent qw(SL::DBUpgrade2::Base);


  		




  
  9
  
    
die("This script cannot be run from the command line.") unless ($main::form);


  		




  
  10
  
    

  		




  
  11
  
    
sub mydberror {


  		




  
  12
  
    
  my ($msg) = @_;


  		




  
  13
  
    
  die($dbup_locale->text("Database update error:") . "<br>$msg<br>" . $DBI::errstr);


  		




  
  14
  
    
}


  		




  
  15
  
    

  		




  
  16
  
    
sub do_query {


  		




  
  17
  
    
  my ($query, $may_fail) = @_;


  		




  
  18
  
    

  		




  
  19
  
    
  if (!$dbh->do($query)) {


  		




  
  20
  
    
    mydberror($query) unless ($may_fail);


  		




  
  21
  
    
    $dbh->rollback();


  		




  
  22
  
    
    $dbh->begin_work();


  		




  
  23
  
    
  }


  		




  
  24
  
    
}


  		




  11
  25
  
    

  		




  12
  26
  
    

  		




  13
  
  
    
sub run {


  		




  14
  
  
    
  my ($self) = @_;


  		




  
  27
  
    
sub do_update {


  		




  15
  28
  
    
  #Check wheather default currency exists


  		




  16
  29
  
    
  my $query = qq|SELECT curr FROM defaults|;


  		




  17
  
  
    
  my ($currencies) = $self->dbh->selectrow_array($query);


  		




  
  30
  
    
  my ($currencies) = $dbh->selectrow_array($query);


  		




  18
  31
  
    

  		




  19
  32
  
    
  if (length($currencies) == 0 and length($main::form->{defaultcurrency}) == 0){


  		




  20
  33
  
    
    print_no_default_currency();


  		




  ......




  40
  53
  
    
              UNION


  		




  41
  54
  
    
              SELECT DISTINCT curr FROM vendor|;


  		




  42
  55
  
    

  		




  43
  
  
    
  my $sth = $self->dbh->prepare($query);


  		




  
  56
  
    
  my $sth = $dbh->prepare($query);


  		




  44
  57
  
    
  $sth->execute || $self->dberror($query);


  		




  45
  58
  
    

  		




  46
  59
  
    
  $main::form->{ORPHANED_CURRENCIES} = [];


  		




  ......




  75
  88
  
    

  		




  76
  89
  
    
  if ($main::form->{continue_options} eq 'insert') {


  		




  77
  90
  
    
    for my $i (0..($rowcount-1)){


  		




  78
  
  
    
      push @currency_array, $main::form->{"curr_$i"};


  		




  
  91
  
    
      push @currency_array, $form->{"curr_$i"};


  		




  79
  92
  
    
    }


  		




  80
  
  
    
    create_and_fill_table($self, @currency_array);


  		




  
  93
  
    
    create_and_fill_table(@currency_array);


  		




  81
  94
  
    
    return 1;


  		




  82
  95
  
    
  }


  		




  83
  96
  
    

  		




  ......




  89
  102
  
    
        if ($main::form->{"curr_$i"} eq $item){


  		




  90
  103
  
    
          $still_orphaned = 0;


  		




  91
  104
  
    
          $query = qq|DELETE FROM exchangerate WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;


  		




  92
  
  
    
          $self->db_query($query);


  		




  
  105
  
    
          do_query($query);


  		




  93
  106
  
    
          $query = qq|UPDATE ap SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;


  		




  94
  
  
    
          $self->db_query($query);


  		




  
  107
  
    
          do_query($query);


  		




  95
  108
  
    
          $query = qq|UPDATE ar SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;


  		




  96
  
  
    
          $self->db_query($query);


  		




  
  109
  
    
          do_query($query);


  		




  97
  110
  
    
          $query = qq|UPDATE oe SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;


  		




  98
  
  
    
          $self->db_query($query);


  		




  
  111
  
    
          do_query($query);


  		




  99
  112
  
    
          $query = qq|UPDATE customer SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;


  		




  100
  
  
    
          $self->db_query($query);


  		




  
  113
  
    
          do_query($query);


  		




  101
  114
  
    
          $query = qq|UPDATE delivery_orders SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;


  		




  102
  
  
    
          $self->db_query($query);


  		




  
  115
  
    
          do_query($query);


  		




  103
  116
  
    
          $query = qq|UPDATE vendor SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;


  		




  104
  
  
    
          $self->db_query($query);


  		




  
  117
  
    
          do_query($query);


  		




  105
  118
  
    
          last;


  		




  106
  119
  
    
        }


  		




  107
  120
  
    
      }


  		




  ......




  110
  123
  
    
        return do_update();


  		




  111
  124
  
    
      }


  		




  112
  125
  
    
    }


  		




  113
  
  
    
    create_and_fill_table($self, @currency_array);


  		




  
  126
  
    
    create_and_fill_table(@currency_array);


  		




  114
  127
  
    
    return 1;


  		




  115
  128
  
    
  }


  		




  116
  129
  
    

  		




  117
  130
  
    
  #No orphaned currencies, so create table:


  		




  118
  
  
    
  create_and_fill_table($self, @currency_array);


  		




  
  131
  
    
  create_and_fill_table(@currency_array);


  		




  119
  132
  
    
  return 1;


  		




  120
  133
  
    
}; # end do_update


  		




  121
  134
  
    

  		




  122
  135
  
    
sub create_and_fill_table {


  		




  123
  
  
    
  my $self = shift;


  		




  124
  136
  
    
  #Create an fill table currencies:


  		




  125
  137
  
    
  my $query = qq|CREATE TABLE currencies (id INTEGER DEFAULT nextval(('id'::text)::regclass) UNIQUE NOT NULL, curr TEXT PRIMARY KEY)|;


  		




  126
  
  
    
  $self->db_query($query);


  		




  
  138
  
    
  do_query($query);


  		




  127
  139
  
    
  foreach my $item ( @_ ) {


  		




  128
  140
  
    
    $query = qq|INSERT INTO currencies (curr) VALUES ('| . $item . qq|')|;


  		




  129
  
  
    
    $self->db_query($query);


  		




  
  141
  
    
    do_query($query);


  		




  130
  142
  
    
  }


  		




  131
  143
  
    

  		




  132
  144
  
    
  #Set default currency if no currency was chosen:


  		




  ......




  137
  149
  
    
  $query .= qq|UPDATE delivery_orders SET curr = '| . $main::form->{"defaultcurrency"} . qq|' WHERE curr IS NULL or curr='';|;


  		




  138
  150
  
    
  $query .= qq|UPDATE vendor SET curr = '| . $main::form->{"defaultcurrency"} . qq|' WHERE curr IS NULL or curr='';|;


  		




  139
  151
  
    
  $query .= qq|DELETE FROM exchangerate WHERE curr IS NULL or curr='';|;


  		




  140
  
  
    
  $self->db_query($query);


  		




  
  152
  
    
  do_query($query);


  		




  141
  153
  
    

  		




  142
  154
  
    
  #Check wheather defaultcurrency is already in table currencies:


  		




  143
  155
  
    
  $query = qq|SELECT curr FROM currencies WHERE curr = '| . $main::form->{defaultcurrency} . qq|'|;


  		




  144
  
  
    
  my ($insert_default) = $self->dbh->selectrow_array($query);


  		




  
  156
  
    
  my ($insert_default) = $dbh->selectrow_array($query);


  		




  145
  157
  
    

  		




  146
  158
  
    
  if (!$insert_default) {


  		




  147
  159
  
    
    $query = qq|INSERT INTO currencies (curr) VALUES ('| . $main::form->{defaultcurrency} . qq|')|;


  		




  148
  
  
    
    $self->db_query($query);


  		




  
  160
  
    
    do_query($query);


  		




  149
  161
  
    
  }


  		




  150
  162
  
    

  		




  151
  163
  
    
  #Create a new columns currency and update with curr.id:


  		




  ......




  157
  169
  
    
              ALTER TABLE exchangerate ADD currency INTEGER;


  		




  158
  170
  
    
              ALTER TABLE vendor ADD currency INTEGER;


  		




  159
  171
  
    
              ALTER TABLE defaults ADD currency INTEGER;|;


  		




  160
  
  
    
  $self->db_query($query);


  		




  
  172
  
    
  do_query($query);


  		




  161
  173
  
    
  #Set defaultcurrency:


  		




  162
  174
  
    
  $query = qq|UPDATE defaults SET currency= (SELECT id FROM currencies WHERE curr = '| . $main::form->{defaultcurrency} . qq|')|;


  		




  163
  
  
    
  $self->db_query($query);


  		




  
  175
  
    
  do_query($query);


  		




  164
  176
  
    
  $query = qq|UPDATE ap SET currency = (SELECT id FROM currencies c WHERE c.curr = ap.curr);


  		




  165
  177
  
    
              UPDATE ar SET currency = (SELECT id FROM currencies c WHERE c.curr = ar.curr);


  		




  166
  178
  
    
              UPDATE oe SET currency = (SELECT id FROM currencies c WHERE c.curr = oe.curr);


  		




  ......




  168
  180
  
    
              UPDATE delivery_orders SET currency = (SELECT id FROM currencies c WHERE c.curr = delivery_orders.curr);


  		




  169
  181
  
    
              UPDATE exchangerate SET currency = (SELECT id FROM currencies c WHERE c.curr = exchangerate.curr);


  		




  170
  182
  
    
              UPDATE vendor SET currency = (SELECT id FROM currencies c WHERE c.curr = vendor.curr);|;


  		




  171
  
  
    
  $self->db_query($query);


  		




  
  183
  
    
  do_query($query);


  		




  172
  184
  
    

  		




  173
  185
  
    
  #Drop column 'curr':


  		




  174
  186
  
    
  $query = qq|ALTER TABLE ap DROP COLUMN curr;


  		




  ......




  179
  191
  
    
              ALTER TABLE exchangerate DROP COLUMN curr;


  		




  180
  192
  
    
              ALTER TABLE vendor DROP COLUMN curr;


  		




  181
  193
  
    
              ALTER TABLE defaults DROP COLUMN curr;|;


  		




  182
  
  
    
  $self->db_query($query);


  		




  
  194
  
    
  do_query($query);


  		




  183
  195
  
    

  		




  184
  196
  
    
  #Rename currency to curr:


  		




  185
  197
  
    
  $query = qq|ALTER TABLE defaults RENAME COLUMN currency TO curr;


  		




  ......




  190
  202
  
    
              ALTER TABLE delivery_orders RENAME COLUMN currency TO curr;


  		




  191
  203
  
    
              ALTER TABLE exchangerate RENAME COLUMN currency TO curr;


  		




  192
  204
  
    
              ALTER TABLE vendor RENAME COLUMN currency TO curr;|;


  		




  193
  
  
    
  $self->db_query($query);


  		




  
  205
  
    
  do_query($query);


  		




  194
  206
  
    

  		




  195
  207
  
    
  #Set NOT NULL constraints:


  		




  196
  208
  
    
  $query = qq|ALTER TABLE ap ALTER COLUMN curr SET NOT NULL;


  		




  ......




  201
  213
  
    
              ALTER TABLE exchangerate ALTER COLUMN curr SET NOT NULL;


  		




  202
  214
  
    
              ALTER TABLE vendor ALTER COLUMN curr SET NOT NULL;


  		




  203
  215
  
    
              ALTER TABLE defaults ALTER COLUMN curr SET NOT NULL;|;


  		




  204
  
  
    
  $self->db_query($query);


  		




  
  216
  
    
  do_query($query);


  		




  205
  217
  
    

  		




  206
  218
  
    
  #Set foreign keys:


  		




  207
  219
  
    
  $query = qq|ALTER TABLE ap ADD FOREIGN KEY (curr) REFERENCES currencies(id);


  		




  ......




  212
  224
  
    
              ALTER TABLE exchangerate ADD FOREIGN KEY (curr) REFERENCES currencies(id);


  		




  213
  225
  
    
              ALTER TABLE vendor ADD FOREIGN KEY (curr) REFERENCES currencies(id);


  		




  214
  226
  
    
              ALTER TABLE defaults ADD FOREIGN KEY (curr) REFERENCES currencies(id);|;


  		




  215
  
  
    
  $self->db_query($query);


  		




  
  227
  
    
  do_query($query);


  		




  216
  228
  
    

  		




  217
  229
  
    
};


  		




  218
  230
  
    

  		




  ......




  224
  236
  
    
  print $main::form->parse_html_template("dbupgrade/orphaned_currencies");


  		




  225
  237
  
    
};


  		




  226
  238
  
    

  		




  227
  
  
    
1;


  		




  
  239
  
    
return do_update();


  		












Auch abrufbar als:  Unified diff