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 |
|
# @charset: utf-8
|
|
4 |
|
|
5 |
package SL::DBUpgrade2::currencies;
|
5 |
6 |
|
6 |
7 |
use utf8;
|
7 |
8 |
use strict;
|
8 |
9 |
|
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 |
|
}
|
|
10 |
use parent qw(SL::DBUpgrade2::Base);
|
25 |
11 |
|
26 |
12 |
|
27 |
|
sub do_update {
|
|
13 |
sub run {
|
|
14 |
my ($self) = @_;
|
28 |
15 |
#Check wheather default currency exists
|
29 |
16 |
my $query = qq|SELECT curr FROM defaults|;
|
30 |
|
my ($currencies) = $dbh->selectrow_array($query);
|
|
17 |
my ($currencies) = $self->dbh->selectrow_array($query);
|
31 |
18 |
|
32 |
19 |
if (length($currencies) == 0 and length($main::form->{defaultcurrency}) == 0){
|
33 |
20 |
print_no_default_currency();
|
... | ... | |
53 |
40 |
UNION
|
54 |
41 |
SELECT DISTINCT curr FROM vendor|;
|
55 |
42 |
|
56 |
|
my $sth = $dbh->prepare($query);
|
|
43 |
my $sth = $self->dbh->prepare($query);
|
57 |
44 |
$sth->execute || $self->dberror($query);
|
58 |
45 |
|
59 |
46 |
$main::form->{ORPHANED_CURRENCIES} = [];
|
... | ... | |
88 |
75 |
|
89 |
76 |
if ($main::form->{continue_options} eq 'insert') {
|
90 |
77 |
for my $i (0..($rowcount-1)){
|
91 |
|
push @currency_array, $form->{"curr_$i"};
|
|
78 |
push @currency_array, $main::form->{"curr_$i"};
|
92 |
79 |
}
|
93 |
|
create_and_fill_table(@currency_array);
|
|
80 |
create_and_fill_table($self, @currency_array);
|
94 |
81 |
return 1;
|
95 |
82 |
}
|
96 |
83 |
|
... | ... | |
102 |
89 |
if ($main::form->{"curr_$i"} eq $item){
|
103 |
90 |
$still_orphaned = 0;
|
104 |
91 |
$query = qq|DELETE FROM exchangerate WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
|
105 |
|
do_query($query);
|
|
92 |
$self->db_query($query);
|
106 |
93 |
$query = qq|UPDATE ap SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
|
107 |
|
do_query($query);
|
|
94 |
$self->db_query($query);
|
108 |
95 |
$query = qq|UPDATE ar SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
|
109 |
|
do_query($query);
|
|
96 |
$self->db_query($query);
|
110 |
97 |
$query = qq|UPDATE oe SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
|
111 |
|
do_query($query);
|
|
98 |
$self->db_query($query);
|
112 |
99 |
$query = qq|UPDATE customer SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
|
113 |
|
do_query($query);
|
|
100 |
$self->db_query($query);
|
114 |
101 |
$query = qq|UPDATE delivery_orders SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
|
115 |
|
do_query($query);
|
|
102 |
$self->db_query($query);
|
116 |
103 |
$query = qq|UPDATE vendor SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
|
117 |
|
do_query($query);
|
|
104 |
$self->db_query($query);
|
118 |
105 |
last;
|
119 |
106 |
}
|
120 |
107 |
}
|
... | ... | |
123 |
110 |
return do_update();
|
124 |
111 |
}
|
125 |
112 |
}
|
126 |
|
create_and_fill_table(@currency_array);
|
|
113 |
create_and_fill_table($self, @currency_array);
|
127 |
114 |
return 1;
|
128 |
115 |
}
|
129 |
116 |
|
130 |
117 |
#No orphaned currencies, so create table:
|
131 |
|
create_and_fill_table(@currency_array);
|
|
118 |
create_and_fill_table($self, @currency_array);
|
132 |
119 |
return 1;
|
133 |
120 |
}; # end do_update
|
134 |
121 |
|
135 |
122 |
sub create_and_fill_table {
|
|
123 |
my $self = shift;
|
136 |
124 |
#Create an fill table currencies:
|
137 |
125 |
my $query = qq|CREATE TABLE currencies (id INTEGER DEFAULT nextval(('id'::text)::regclass) UNIQUE NOT NULL, curr TEXT PRIMARY KEY)|;
|
138 |
|
do_query($query);
|
|
126 |
$self->db_query($query);
|
139 |
127 |
foreach my $item ( @_ ) {
|
140 |
128 |
$query = qq|INSERT INTO currencies (curr) VALUES ('| . $item . qq|')|;
|
141 |
|
do_query($query);
|
|
129 |
$self->db_query($query);
|
142 |
130 |
}
|
143 |
131 |
|
144 |
132 |
#Set default currency if no currency was chosen:
|
... | ... | |
149 |
137 |
$query .= qq|UPDATE delivery_orders SET curr = '| . $main::form->{"defaultcurrency"} . qq|' WHERE curr IS NULL or curr='';|;
|
150 |
138 |
$query .= qq|UPDATE vendor SET curr = '| . $main::form->{"defaultcurrency"} . qq|' WHERE curr IS NULL or curr='';|;
|
151 |
139 |
$query .= qq|DELETE FROM exchangerate WHERE curr IS NULL or curr='';|;
|
152 |
|
do_query($query);
|
|
140 |
$self->db_query($query);
|
153 |
141 |
|
154 |
142 |
#Check wheather defaultcurrency is already in table currencies:
|
155 |
143 |
$query = qq|SELECT curr FROM currencies WHERE curr = '| . $main::form->{defaultcurrency} . qq|'|;
|
156 |
|
my ($insert_default) = $dbh->selectrow_array($query);
|
|
144 |
my ($insert_default) = $self->dbh->selectrow_array($query);
|
157 |
145 |
|
158 |
146 |
if (!$insert_default) {
|
159 |
147 |
$query = qq|INSERT INTO currencies (curr) VALUES ('| . $main::form->{defaultcurrency} . qq|')|;
|
160 |
|
do_query($query);
|
|
148 |
$self->db_query($query);
|
161 |
149 |
}
|
162 |
150 |
|
163 |
151 |
#Create a new columns currency and update with curr.id:
|
... | ... | |
169 |
157 |
ALTER TABLE exchangerate ADD currency INTEGER;
|
170 |
158 |
ALTER TABLE vendor ADD currency INTEGER;
|
171 |
159 |
ALTER TABLE defaults ADD currency INTEGER;|;
|
172 |
|
do_query($query);
|
|
160 |
$self->db_query($query);
|
173 |
161 |
#Set defaultcurrency:
|
174 |
162 |
$query = qq|UPDATE defaults SET currency= (SELECT id FROM currencies WHERE curr = '| . $main::form->{defaultcurrency} . qq|')|;
|
175 |
|
do_query($query);
|
|
163 |
$self->db_query($query);
|
176 |
164 |
$query = qq|UPDATE ap SET currency = (SELECT id FROM currencies c WHERE c.curr = ap.curr);
|
177 |
165 |
UPDATE ar SET currency = (SELECT id FROM currencies c WHERE c.curr = ar.curr);
|
178 |
166 |
UPDATE oe SET currency = (SELECT id FROM currencies c WHERE c.curr = oe.curr);
|
... | ... | |
180 |
168 |
UPDATE delivery_orders SET currency = (SELECT id FROM currencies c WHERE c.curr = delivery_orders.curr);
|
181 |
169 |
UPDATE exchangerate SET currency = (SELECT id FROM currencies c WHERE c.curr = exchangerate.curr);
|
182 |
170 |
UPDATE vendor SET currency = (SELECT id FROM currencies c WHERE c.curr = vendor.curr);|;
|
183 |
|
do_query($query);
|
|
171 |
$self->db_query($query);
|
184 |
172 |
|
185 |
173 |
#Drop column 'curr':
|
186 |
174 |
$query = qq|ALTER TABLE ap DROP COLUMN curr;
|
... | ... | |
191 |
179 |
ALTER TABLE exchangerate DROP COLUMN curr;
|
192 |
180 |
ALTER TABLE vendor DROP COLUMN curr;
|
193 |
181 |
ALTER TABLE defaults DROP COLUMN curr;|;
|
194 |
|
do_query($query);
|
|
182 |
$self->db_query($query);
|
195 |
183 |
|
196 |
184 |
#Rename currency to curr:
|
197 |
185 |
$query = qq|ALTER TABLE defaults RENAME COLUMN currency TO curr;
|
... | ... | |
202 |
190 |
ALTER TABLE delivery_orders RENAME COLUMN currency TO curr;
|
203 |
191 |
ALTER TABLE exchangerate RENAME COLUMN currency TO curr;
|
204 |
192 |
ALTER TABLE vendor RENAME COLUMN currency TO curr;|;
|
205 |
|
do_query($query);
|
|
193 |
$self->db_query($query);
|
206 |
194 |
|
207 |
195 |
#Set NOT NULL constraints:
|
208 |
196 |
$query = qq|ALTER TABLE ap ALTER COLUMN curr SET NOT NULL;
|
... | ... | |
213 |
201 |
ALTER TABLE exchangerate ALTER COLUMN curr SET NOT NULL;
|
214 |
202 |
ALTER TABLE vendor ALTER COLUMN curr SET NOT NULL;
|
215 |
203 |
ALTER TABLE defaults ALTER COLUMN curr SET NOT NULL;|;
|
216 |
|
do_query($query);
|
|
204 |
$self->db_query($query);
|
217 |
205 |
|
218 |
206 |
#Set foreign keys:
|
219 |
207 |
$query = qq|ALTER TABLE ap ADD FOREIGN KEY (curr) REFERENCES currencies(id);
|
... | ... | |
224 |
212 |
ALTER TABLE exchangerate ADD FOREIGN KEY (curr) REFERENCES currencies(id);
|
225 |
213 |
ALTER TABLE vendor ADD FOREIGN KEY (curr) REFERENCES currencies(id);
|
226 |
214 |
ALTER TABLE defaults ADD FOREIGN KEY (curr) REFERENCES currencies(id);|;
|
227 |
|
do_query($query);
|
|
215 |
$self->db_query($query);
|
228 |
216 |
|
229 |
217 |
};
|
230 |
218 |
|
... | ... | |
236 |
224 |
print $main::form->parse_html_template("dbupgrade/orphaned_currencies");
|
237 |
225 |
};
|
238 |
226 |
|
239 |
|
return do_update();
|
|
227 |
1;
|
Anpassung Upgrade-Script an neue Synthax
Passt zwei alte Upgrade-Scripte an die neue Synthax an.