kivitendo/sql/Pg-upgrade2/currencies.pl @ 714a28b1
| 9c8450d9 | Niclas Zimmermann | # @tag: currencies
|
||
# @description: Erstellt neue Tabelle currencies. Währungen können dann einfacher eingegeben und unkritisch geändert werden.
|
||||
# @depends: release_3_0_0 rm_whitespaces
|
||||
| 714a28b1 | Niclas Zimmermann | |||
package SL::DBUpgrade2::currencies;
|
||||
| 9c8450d9 | Niclas Zimmermann | |||
use utf8;
|
||||
use strict;
|
||||
| 714a28b1 | Niclas Zimmermann | use parent qw(SL::DBUpgrade2::Base);
|
||
| 9c8450d9 | Niclas Zimmermann | |||
| 714a28b1 | Niclas Zimmermann | sub run {
|
||
my ($self) = @_;
|
||||
| 9c8450d9 | Niclas Zimmermann | #Check wheather default currency exists
|
||
my $query = qq|SELECT curr FROM defaults|;
|
||||
| 714a28b1 | Niclas Zimmermann | my ($currencies) = $self->dbh->selectrow_array($query);
|
||
| 9c8450d9 | Niclas Zimmermann | |||
if (length($currencies) == 0 and length($main::form->{defaultcurrency}) == 0){
|
||||
print_no_default_currency();
|
||||
return 2;
|
||||
} else {
|
||||
if (length($main::form->{defaultcurrency}) == 0){
|
||||
$main::form->{defaultcurrency} = (split m/:/, $currencies)[0];
|
||||
}
|
||||
}
|
||||
my @currency_array = grep {$_ ne '' } split m/:/, $currencies;
|
||||
$query = qq|SELECT DISTINCT curr FROM ar
|
||||
UNION
|
||||
SELECT DISTINCT curr FROM ap
|
||||
UNION
|
||||
SELECT DISTINCT curr FROM oe
|
||||
UNION
|
||||
SELECT DISTINCT curr FROM customer
|
||||
UNION
|
||||
SELECT DISTINCT curr FROM delivery_orders
|
||||
UNION
|
||||
SELECT DISTINCT curr FROM exchangerate
|
||||
UNION
|
||||
SELECT DISTINCT curr FROM vendor|;
|
||||
| 714a28b1 | Niclas Zimmermann | my $sth = $self->dbh->prepare($query);
|
||
| 9c8450d9 | Niclas Zimmermann | $sth->execute || $self->dberror($query);
|
||
$main::form->{ORPHANED_CURRENCIES} = [];
|
||||
my $is_orphaned;
|
||||
my $rowcount = 0;
|
||||
while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
|
||||
next unless length($ref->{curr}) > 0;
|
||||
$is_orphaned = 1;
|
||||
foreach my $key (split(/:/, $currencies)) {
|
||||
if ($ref->{curr} eq $key) {
|
||||
$is_orphaned = 0;
|
||||
last;
|
||||
}
|
||||
}
|
||||
if ($is_orphaned) {
|
||||
push @{ $main::form->{ORPHANED_CURRENCIES} }, $ref;
|
||||
$main::form->{ORPHANED_CURRENCIES}[$rowcount]->{name} = "curr_$rowcount";
|
||||
$rowcount++;
|
||||
}
|
||||
}
|
||||
$sth->finish;
|
||||
if (scalar @{ $main::form->{ORPHANED_CURRENCIES} } > 0 and not ($main::form->{continue_options})) {
|
||||
print_orphaned_currencies();
|
||||
return 2;
|
||||
}
|
||||
if ($main::form->{continue_options} eq 'break_up') {
|
||||
return 0;
|
||||
}
|
||||
if ($main::form->{continue_options} eq 'insert') {
|
||||
for my $i (0..($rowcount-1)){
|
||||
| 714a28b1 | Niclas Zimmermann | push @currency_array, $main::form->{"curr_$i"};
|
||
| 9c8450d9 | Niclas Zimmermann | }
|
||
| 714a28b1 | Niclas Zimmermann | create_and_fill_table($self, @currency_array);
|
||
| 9c8450d9 | Niclas Zimmermann | 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|'|;
|
||||
| 714a28b1 | Niclas Zimmermann | $self->db_query($query);
|
||
| 9c8450d9 | Niclas Zimmermann | $query = qq|UPDATE ap SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
|
||
| 714a28b1 | Niclas Zimmermann | $self->db_query($query);
|
||
| 9c8450d9 | Niclas Zimmermann | $query = qq|UPDATE ar SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
|
||
| 714a28b1 | Niclas Zimmermann | $self->db_query($query);
|
||
| 9c8450d9 | Niclas Zimmermann | $query = qq|UPDATE oe SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
|
||
| 714a28b1 | Niclas Zimmermann | $self->db_query($query);
|
||
| 9c8450d9 | Niclas Zimmermann | $query = qq|UPDATE customer SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
|
||
| 714a28b1 | Niclas Zimmermann | $self->db_query($query);
|
||
| 9c8450d9 | Niclas Zimmermann | $query = qq|UPDATE delivery_orders SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
|
||
| 714a28b1 | Niclas Zimmermann | $self->db_query($query);
|
||
| 9c8450d9 | Niclas Zimmermann | $query = qq|UPDATE vendor SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
|
||
| 714a28b1 | Niclas Zimmermann | $self->db_query($query);
|
||
| 9c8450d9 | Niclas Zimmermann | last;
|
||
}
|
||||
}
|
||||
if ($still_orphaned){
|
||||
$main::form->{continue_options} = '';
|
||||
return do_update();
|
||||
}
|
||||
}
|
||||
| 714a28b1 | Niclas Zimmermann | create_and_fill_table($self, @currency_array);
|
||
| 9c8450d9 | Niclas Zimmermann | return 1;
|
||
}
|
||||
#No orphaned currencies, so create table:
|
||||
| 714a28b1 | Niclas Zimmermann | create_and_fill_table($self, @currency_array);
|
||
| 9c8450d9 | Niclas Zimmermann | return 1;
|
||
}; # end do_update
|
||||
sub create_and_fill_table {
|
||||
| 714a28b1 | Niclas Zimmermann | my $self = shift;
|
||
| 9c8450d9 | Niclas Zimmermann | #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)|;
|
||||
| 714a28b1 | Niclas Zimmermann | $self->db_query($query);
|
||
| 9c8450d9 | Niclas Zimmermann | foreach my $item ( @_ ) {
|
||
$query = qq|INSERT INTO currencies (curr) VALUES ('| . $item . qq|')|;
|
||||
| 714a28b1 | Niclas Zimmermann | $self->db_query($query);
|
||
| 9c8450d9 | Niclas Zimmermann | }
|
||
#Set default currency if no currency was chosen:
|
||||
$query = qq|UPDATE ap SET curr = '| . $main::form->{"defaultcurrency"} . qq|' WHERE curr IS NULL or curr='';|;
|
||||
$query .= qq|UPDATE ar SET curr = '| . $main::form->{"defaultcurrency"} . qq|' WHERE curr IS NULL or curr='';|;
|
||||
$query .= qq|UPDATE oe SET curr = '| . $main::form->{"defaultcurrency"} . qq|' WHERE curr IS NULL or curr='';|;
|
||||
$query .= qq|UPDATE customer SET curr = '| . $main::form->{"defaultcurrency"} . qq|' WHERE curr IS NULL or curr='';|;
|
||||
$query .= qq|UPDATE delivery_orders SET curr = '| . $main::form->{"defaultcurrency"} . qq|' WHERE curr IS NULL or curr='';|;
|
||||
$query .= qq|UPDATE vendor SET curr = '| . $main::form->{"defaultcurrency"} . qq|' WHERE curr IS NULL or curr='';|;
|
||||
$query .= qq|DELETE FROM exchangerate WHERE curr IS NULL or curr='';|;
|
||||
| 714a28b1 | Niclas Zimmermann | $self->db_query($query);
|
||
| 9c8450d9 | Niclas Zimmermann | |||
#Check wheather defaultcurrency is already in table currencies:
|
||||
$query = qq|SELECT curr FROM currencies WHERE curr = '| . $main::form->{defaultcurrency} . qq|'|;
|
||||
| 714a28b1 | Niclas Zimmermann | my ($insert_default) = $self->dbh->selectrow_array($query);
|
||
| 9c8450d9 | Niclas Zimmermann | |||
if (!$insert_default) {
|
||||
$query = qq|INSERT INTO currencies (curr) VALUES ('| . $main::form->{defaultcurrency} . qq|')|;
|
||||
| 714a28b1 | Niclas Zimmermann | $self->db_query($query);
|
||
| 9c8450d9 | Niclas Zimmermann | }
|
||
#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;|;
|
||||
| 714a28b1 | Niclas Zimmermann | $self->db_query($query);
|
||
| 9c8450d9 | Niclas Zimmermann | #Set defaultcurrency:
|
||
$query = qq|UPDATE defaults SET currency= (SELECT id FROM currencies WHERE curr = '| . $main::form->{defaultcurrency} . qq|')|;
|
||||
| 714a28b1 | Niclas Zimmermann | $self->db_query($query);
|
||
| 9c8450d9 | Niclas Zimmermann | $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);|;
|
||||
| 714a28b1 | Niclas Zimmermann | $self->db_query($query);
|
||
| 9c8450d9 | Niclas Zimmermann | |||
#Drop column 'curr':
|
||||
$query = qq|ALTER TABLE ap DROP COLUMN curr;
|
||||
ALTER TABLE ar DROP COLUMN curr;
|
||||
ALTER TABLE oe DROP COLUMN curr;
|
||||
ALTER TABLE customer DROP COLUMN curr;
|
||||
ALTER TABLE delivery_orders DROP COLUMN curr;
|
||||
ALTER TABLE exchangerate DROP COLUMN curr;
|
||||
ALTER TABLE vendor DROP COLUMN curr;
|
||||
ALTER TABLE defaults DROP COLUMN curr;|;
|
||||
| 714a28b1 | Niclas Zimmermann | $self->db_query($query);
|
||
| 9c8450d9 | Niclas Zimmermann | |||
#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;|;
|
||||
| 714a28b1 | Niclas Zimmermann | $self->db_query($query);
|
||
| 9c8450d9 | Niclas Zimmermann | |||
#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;|;
|
||||
| 714a28b1 | Niclas Zimmermann | $self->db_query($query);
|
||
| 9c8450d9 | Niclas Zimmermann | |||
#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);|;
|
||||
| 714a28b1 | Niclas Zimmermann | $self->db_query($query);
|
||
| 9c8450d9 | Niclas Zimmermann | |||
};
|
||||
sub print_no_default_currency {
|
||||
print $main::form->parse_html_template("dbupgrade/no_default_currency");
|
||||
};
|
||||
sub print_orphaned_currencies {
|
||||
print $main::form->parse_html_template("dbupgrade/orphaned_currencies");
|
||||
};
|
||||
| 714a28b1 | Niclas Zimmermann | 1;
|