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
5 package SL::DBUpgrade2::currencies;
10 use parent qw(SL::DBUpgrade2::Base);
15 #Check wheather default currency exists
16 my $query = qq|SELECT curr FROM defaults|;
17 my ($currencies) = $self->dbh->selectrow_array($query);
19 if (length($currencies) == 0 and length($main::form->{defaultcurrency}) == 0){
20 print_no_default_currency();
23 if (length($main::form->{defaultcurrency}) == 0){
24 $main::form->{defaultcurrency} = (split m/:/, $currencies)[0];
27 my @currency_array = grep {$_ ne '' } split m/:/, $currencies;
29 $query = qq|SELECT DISTINCT curr FROM ar
31 SELECT DISTINCT curr FROM ap
33 SELECT DISTINCT curr FROM oe
35 SELECT DISTINCT curr FROM customer
37 SELECT DISTINCT curr FROM delivery_orders
39 SELECT DISTINCT curr FROM exchangerate
41 SELECT DISTINCT curr FROM vendor|;
43 my $sth = $self->dbh->prepare($query);
44 $sth->execute || $self->dberror($query);
46 $main::form->{ORPHANED_CURRENCIES} = [];
49 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
50 next unless length($ref->{curr}) > 0;
52 foreach my $key (split(/:/, $currencies)) {
53 if ($ref->{curr} eq $key) {
59 push @{ $main::form->{ORPHANED_CURRENCIES} }, $ref;
60 $main::form->{ORPHANED_CURRENCIES}[$rowcount]->{name} = "curr_$rowcount";
67 if (scalar @{ $main::form->{ORPHANED_CURRENCIES} } > 0 and not ($main::form->{continue_options})) {
68 print_orphaned_currencies();
72 if ($main::form->{continue_options} eq 'break_up') {
76 if ($main::form->{continue_options} eq 'insert') {
77 for my $i (0..($rowcount-1)){
78 push @currency_array, $main::form->{"curr_$i"};
80 create_and_fill_table($self, @currency_array);
85 if ($main::form->{continue_options} eq 'replace') {
86 for my $i (0..($rowcount - 1)){
88 for my $item (@currency_array){
89 if ($main::form->{"curr_$i"} eq $item){
91 $query = qq|DELETE FROM exchangerate WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
92 $self->db_query($query);
93 $query = qq|UPDATE ap SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
94 $self->db_query($query);
95 $query = qq|UPDATE ar SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
96 $self->db_query($query);
97 $query = qq|UPDATE oe SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
98 $self->db_query($query);
99 $query = qq|UPDATE customer SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
100 $self->db_query($query);
101 $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);
103 $query = qq|UPDATE vendor SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;
104 $self->db_query($query);
108 if ($still_orphaned){
109 $main::form->{continue_options} = '';
113 create_and_fill_table($self, @currency_array);
117 #No orphaned currencies, so create table:
118 create_and_fill_table($self, @currency_array);
122 sub create_and_fill_table {
124 #Create an fill table currencies:
125 my $query = qq|CREATE TABLE currencies (id SERIAL PRIMARY KEY,
126 name TEXT NOT NULL UNIQUE)|;
127 $self->db_query($query);
128 foreach my $item ( @_ ) {
129 $query = qq|INSERT INTO currencies (name) VALUES ('| . $item . qq|')|;
130 $self->db_query($query);
133 #Set default currency if no currency was chosen:
134 $query = qq|UPDATE ap SET curr = '| . $main::form->{"defaultcurrency"} . qq|' WHERE curr IS NULL or curr='';|;
135 $query .= qq|UPDATE ar SET curr = '| . $main::form->{"defaultcurrency"} . qq|' WHERE curr IS NULL or curr='';|;
136 $query .= qq|UPDATE oe SET curr = '| . $main::form->{"defaultcurrency"} . qq|' WHERE curr IS NULL or curr='';|;
137 $query .= qq|UPDATE customer SET curr = '| . $main::form->{"defaultcurrency"} . qq|' WHERE curr IS NULL or curr='';|;
138 $query .= qq|UPDATE delivery_orders SET curr = '| . $main::form->{"defaultcurrency"} . qq|' WHERE curr IS NULL or curr='';|;
139 $query .= qq|UPDATE vendor SET curr = '| . $main::form->{"defaultcurrency"} . qq|' WHERE curr IS NULL or curr='';|;
140 $query .= qq|DELETE FROM exchangerate WHERE curr IS NULL or curr='';|;
141 $self->db_query($query);
143 #Check wheather defaultcurrency is already in table currencies:
144 $query = qq|SELECT name FROM currencies WHERE name = '| . $main::form->{defaultcurrency} . qq|'|;
145 my ($insert_default) = $self->dbh->selectrow_array($query);
147 if (!$insert_default) {
148 $query = qq|INSERT INTO currencies (name) VALUES ('| . $main::form->{defaultcurrency} . qq|')|;
149 $self->db_query($query);
152 #Create a new columns currency_id and update with curr.id:
153 $query = qq|ALTER TABLE ap ADD currency_id INTEGER;
154 ALTER TABLE ar ADD currency_id INTEGER;
155 ALTER TABLE oe ADD currency_id INTEGER;
156 ALTER TABLE customer ADD currency_id INTEGER;
157 ALTER TABLE delivery_orders ADD currency_id INTEGER;
158 ALTER TABLE exchangerate ADD currency_id INTEGER;
159 ALTER TABLE vendor ADD currency_id INTEGER;
160 ALTER TABLE defaults ADD currency_id INTEGER;|;
161 $self->db_query($query);
162 #Set defaultcurrency:
163 $query = qq|UPDATE defaults SET currency_id= (SELECT id FROM currencies WHERE name = '| . $main::form->{defaultcurrency} . qq|')|;
164 $self->db_query($query);
165 $query = qq|UPDATE ap SET currency_id = (SELECT id FROM currencies c WHERE c.name = ap.curr);
166 UPDATE ar SET currency_id = (SELECT id FROM currencies c WHERE c.name = ar.curr);
167 UPDATE oe SET currency_id = (SELECT id FROM currencies c WHERE c.name = oe.curr);
168 UPDATE customer SET currency_id = (SELECT id FROM currencies c WHERE c.name = customer.curr);
169 UPDATE delivery_orders SET currency_id = (SELECT id FROM currencies c WHERE c.name = delivery_orders.curr);
170 UPDATE exchangerate SET currency_id = (SELECT id FROM currencies c WHERE c.name = exchangerate.curr);
171 UPDATE vendor SET currency_id = (SELECT id FROM currencies c WHERE c.name = vendor.curr);|;
172 $self->db_query($query);
175 $query = qq|ALTER TABLE ap DROP COLUMN curr;
176 ALTER TABLE ar DROP COLUMN curr;
177 ALTER TABLE oe DROP COLUMN curr;
178 ALTER TABLE customer DROP COLUMN curr;
179 ALTER TABLE delivery_orders DROP COLUMN curr;
180 ALTER TABLE exchangerate DROP COLUMN curr;
181 ALTER TABLE vendor DROP COLUMN curr;
182 ALTER TABLE defaults DROP COLUMN curr;|;
183 $self->db_query($query);
185 #Set NOT NULL constraints:
186 $query = qq|ALTER TABLE ap ALTER COLUMN currency_id SET NOT NULL;
187 ALTER TABLE ar ALTER COLUMN currency_id SET NOT NULL;
188 ALTER TABLE oe ALTER COLUMN currency_id SET NOT NULL;
189 ALTER TABLE customer ALTER COLUMN currency_id SET NOT NULL;
190 ALTER TABLE delivery_orders ALTER COLUMN currency_id SET NOT NULL;
191 ALTER TABLE exchangerate ALTER COLUMN currency_id SET NOT NULL;
192 ALTER TABLE vendor ALTER COLUMN currency_id SET NOT NULL;
193 ALTER TABLE defaults ALTER COLUMN currency_id SET NOT NULL;|;
194 $self->db_query($query);
197 $query = qq|ALTER TABLE ap ADD FOREIGN KEY (currency_id) REFERENCES currencies(id);
198 ALTER TABLE ar ADD FOREIGN KEY (currency_id) REFERENCES currencies(id);
199 ALTER TABLE oe ADD FOREIGN KEY (currency_id) REFERENCES currencies(id);
200 ALTER TABLE customer ADD FOREIGN KEY (currency_id) REFERENCES currencies(id);
201 ALTER TABLE delivery_orders ADD FOREIGN KEY (currency_id) REFERENCES currencies(id);
202 ALTER TABLE exchangerate ADD FOREIGN KEY (currency_id) REFERENCES currencies(id);
203 ALTER TABLE vendor ADD FOREIGN KEY (currency_id) REFERENCES currencies(id);
204 ALTER TABLE defaults ADD FOREIGN KEY (currency_id) REFERENCES currencies(id);|;
205 $self->db_query($query);
209 sub print_no_default_currency {
210 print $main::form->parse_html_template("dbupgrade/no_default_currency");
213 sub print_orphaned_currencies {
214 print $main::form->parse_html_template("dbupgrade/orphaned_currencies");