2 # @description: Erstellt neue Tabelle currencies. Währungen können dann einfacher eingegeben und unkritisch geändert werden.
3 # @depends: release_3_0_0 rm_whitespaces
9 die("This script cannot be run from the command line.") unless ($main::form);
13 die($dbup_locale->text("Database update error:") . "<br>$msg<br>" . $DBI::errstr);
17 my ($query, $may_fail) = @_;
19 if (!$dbh->do($query)) {
20 mydberror($query) unless ($may_fail);
28 #Check wheather default currency exists
29 my $query = qq|SELECT curr FROM defaults|;
30 my ($currencies) = $dbh->selectrow_array($query);
32 if (length($currencies) == 0 and length($main::form->{defaultcurrency}) == 0){
33 print_no_default_currency();
36 if (length($main::form->{defaultcurrency}) == 0){
37 $main::form->{defaultcurrency} = (split m/:/, $currencies)[0];
40 my @currency_array = grep {$_ ne '' } split m/:/, $currencies;
42 $query = qq|SELECT DISTINCT curr FROM ar
44 SELECT DISTINCT curr FROM ap
46 SELECT DISTINCT curr FROM oe
48 SELECT DISTINCT curr FROM customer
50 SELECT DISTINCT curr FROM delivery_orders
52 SELECT DISTINCT curr FROM exchangerate
54 SELECT DISTINCT curr FROM vendor|;
56 my $sth = $dbh->prepare($query);
57 $sth->execute || $self->dberror($query);
59 $main::form->{ORPHANED_CURRENCIES} = [];
62 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
63 next unless length($ref->{curr}) > 0;
65 foreach my $key (split(/:/, $currencies)) {
66 if ($ref->{curr} eq $key) {
72 push @{ $main::form->{ORPHANED_CURRENCIES} }, $ref;
73 $main::form->{ORPHANED_CURRENCIES}[$rowcount]->{name} = "curr_$rowcount";
80 if (scalar @{ $main::form->{ORPHANED_CURRENCIES} } > 0 and not ($main::form->{continue_options})) {
81 print_orphaned_currencies();
85 if ($main::form->{continue_options} eq 'break_up') {
89 if ($main::form->{continue_options} eq 'insert') {
90 for my $i (0..($rowcount-1)){
91 push @currency_array, $form->{"curr_$i"};
93 create_and_fill_table(@currency_array);
98 if ($main::form->{continue_options} eq 'replace') {
99 for my $i (0..($rowcount - 1)){
101 for my $item (@currency_array){
102 if ($main::form->{"curr_$i"} eq $item){
104 $query = qq|DELETE FROM exchangerate WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
106 $query = qq|UPDATE ap SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
108 $query = qq|UPDATE ar SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
110 $query = qq|UPDATE oe SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
112 $query = qq|UPDATE customer SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
114 $query = qq|UPDATE delivery_orders SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
116 $query = qq|UPDATE vendor SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
121 if ($still_orphaned){
122 $main::form->{continue_options} = '';
126 create_and_fill_table(@currency_array);
130 #No orphaned currencies, so create table:
131 create_and_fill_table(@currency_array);
135 sub create_and_fill_table {
136 #Create an fill table currencies:
137 my $query = qq|CREATE TABLE currencies (id INTEGER DEFAULT nextval(('id'::text)::regclass) UNIQUE NOT NULL, curr TEXT PRIMARY KEY)|;
139 foreach my $item ( @_ ) {
140 $query = qq|INSERT INTO currencies (curr) VALUES ('| . $item . qq|')|;
144 #Set default currency if no currency was chosen:
145 $query = qq|UPDATE ap SET curr = '| . $main::form->{"defaultcurrency"} . qq|' WHERE curr IS NULL or curr='';|;
146 $query .= qq|UPDATE ar SET curr = '| . $main::form->{"defaultcurrency"} . qq|' WHERE curr IS NULL or curr='';|;
147 $query .= qq|UPDATE oe SET curr = '| . $main::form->{"defaultcurrency"} . qq|' WHERE curr IS NULL or curr='';|;
148 $query .= qq|UPDATE customer SET curr = '| . $main::form->{"defaultcurrency"} . qq|' WHERE curr IS NULL or curr='';|;
149 $query .= qq|UPDATE delivery_orders SET curr = '| . $main::form->{"defaultcurrency"} . qq|' WHERE curr IS NULL or curr='';|;
150 $query .= qq|UPDATE vendor SET curr = '| . $main::form->{"defaultcurrency"} . qq|' WHERE curr IS NULL or curr='';|;
151 $query .= qq|DELETE FROM exchangerate WHERE curr IS NULL or curr='';|;
154 #Check wheather defaultcurrency is already in table currencies:
155 $query = qq|SELECT curr FROM currencies WHERE curr = '| . $main::form->{defaultcurrency} . qq|'|;
156 my ($insert_default) = $dbh->selectrow_array($query);
158 if (!$insert_default) {
159 $query = qq|INSERT INTO currencies (curr) VALUES ('| . $main::form->{defaultcurrency} . qq|')|;
163 #Create a new columns currency and update with curr.id:
164 $query = qq|ALTER TABLE ap ADD currency INTEGER;
165 ALTER TABLE ar ADD currency INTEGER;
166 ALTER TABLE oe ADD currency INTEGER;
167 ALTER TABLE customer ADD currency INTEGER;
168 ALTER TABLE delivery_orders ADD currency INTEGER;
169 ALTER TABLE exchangerate ADD currency INTEGER;
170 ALTER TABLE vendor ADD currency INTEGER;
171 ALTER TABLE defaults ADD currency INTEGER;|;
173 #Set defaultcurrency:
174 $query = qq|UPDATE defaults SET currency= (SELECT id FROM currencies WHERE curr = '| . $main::form->{defaultcurrency} . qq|')|;
176 $query = qq|UPDATE ap SET currency = (SELECT id FROM currencies c WHERE c.curr = ap.curr);
177 UPDATE ar SET currency = (SELECT id FROM currencies c WHERE c.curr = ar.curr);
178 UPDATE oe SET currency = (SELECT id FROM currencies c WHERE c.curr = oe.curr);
179 UPDATE customer SET currency = (SELECT id FROM currencies c WHERE c.curr = customer.curr);
180 UPDATE delivery_orders SET currency = (SELECT id FROM currencies c WHERE c.curr = delivery_orders.curr);
181 UPDATE exchangerate SET currency = (SELECT id FROM currencies c WHERE c.curr = exchangerate.curr);
182 UPDATE vendor SET currency = (SELECT id FROM currencies c WHERE c.curr = vendor.curr);|;
186 $query = qq|ALTER TABLE ap DROP COLUMN curr;
187 ALTER TABLE ar DROP COLUMN curr;
188 ALTER TABLE oe DROP COLUMN curr;
189 ALTER TABLE customer DROP COLUMN curr;
190 ALTER TABLE delivery_orders DROP COLUMN curr;
191 ALTER TABLE exchangerate DROP COLUMN curr;
192 ALTER TABLE vendor DROP COLUMN curr;
193 ALTER TABLE defaults DROP COLUMN curr;|;
196 #Rename currency to curr:
197 $query = qq|ALTER TABLE defaults RENAME COLUMN currency TO curr;
198 ALTER TABLE ap RENAME COLUMN currency TO curr;
199 ALTER TABLE ar RENAME COLUMN currency TO curr;
200 ALTER TABLE oe RENAME COLUMN currency TO curr;
201 ALTER TABLE customer RENAME COLUMN currency TO curr;
202 ALTER TABLE delivery_orders RENAME COLUMN currency TO curr;
203 ALTER TABLE exchangerate RENAME COLUMN currency TO curr;
204 ALTER TABLE vendor RENAME COLUMN currency TO curr;|;
207 #Set NOT NULL constraints:
208 $query = qq|ALTER TABLE ap ALTER COLUMN curr SET NOT NULL;
209 ALTER TABLE ar ALTER COLUMN curr SET NOT NULL;
210 ALTER TABLE oe ALTER COLUMN curr SET NOT NULL;
211 ALTER TABLE customer ALTER COLUMN curr SET NOT NULL;
212 ALTER TABLE delivery_orders ALTER COLUMN curr SET NOT NULL;
213 ALTER TABLE exchangerate ALTER COLUMN curr SET NOT NULL;
214 ALTER TABLE vendor ALTER COLUMN curr SET NOT NULL;
215 ALTER TABLE defaults ALTER COLUMN curr SET NOT NULL;|;
219 $query = qq|ALTER TABLE ap ADD FOREIGN KEY (curr) REFERENCES currencies(id);
220 ALTER TABLE ar ADD FOREIGN KEY (curr) REFERENCES currencies(id);
221 ALTER TABLE oe ADD FOREIGN KEY (curr) REFERENCES currencies(id);
222 ALTER TABLE customer ADD FOREIGN KEY (curr) REFERENCES currencies(id);
223 ALTER TABLE delivery_orders ADD FOREIGN KEY (curr) REFERENCES currencies(id);
224 ALTER TABLE exchangerate ADD FOREIGN KEY (curr) REFERENCES currencies(id);
225 ALTER TABLE vendor ADD FOREIGN KEY (curr) REFERENCES currencies(id);
226 ALTER TABLE defaults ADD FOREIGN KEY (curr) REFERENCES currencies(id);|;
231 sub print_no_default_currency {
232 print $main::form->parse_html_template("dbupgrade/no_default_currency");
235 sub print_orphaned_currencies {
236 print $main::form->parse_html_template("dbupgrade/orphaned_currencies");