3 die("This script cannot be run from the command line.") unless ($main::form);
7 die($dbup_locale->text("Database update error:") .
8 "<br>$msg<br>" . $DBI::errstr);
12 my ($query, @values) = @_;
14 $dbh->do($query, undef, @values) ||
15 mydberror($query . " (" . join(", ", @values) . ")");
19 foreach my $table (qw(customer vendor ar ap oe)) {
20 my $query = "UPDATE ${table} SET taxzone_id = 0";
21 $dbh->do($query) || mydberror($query);
30 " (SELECT DISTINCT inventory_accno_id " .
32 " WHERE (NOT inventory_accno_id ISNULL) AND (inventory_accno_id > 0))";
34 my $sth = $dbh->prepare($query);
35 $sth->execute() || mydberror($query);
37 my $query_update = "UPDATE chart SET link = ? WHERE id = ?";
38 my $sth_update = $dbh->prepare($query_update);
40 while (my $ref = $sth->fetchrow_hashref()) {
42 map({ $links{$_} = 1 } split(/:/, $ref->{"link"}));
44 my $new_link = join(":", keys(%links));
45 $sth_update->execute($new_link, $ref->{"id"}) ||
46 mydberror($query_update . " ($new_link, $ref->{id})");
50 $sth_update->finish();
53 sub force_inventory_accno_id_for_parts {
55 "UPDATE parts SET inventory_accno_id = " .
56 "(SELECT bg.inventory_accno_id " .
57 " FROM buchungsgruppen bg " .
58 " WHERE bg.description = 'Standard 16%') " .
59 "WHERE (NOT inventory_accno_id ISNULL) AND (inventory_accno_id > 0)";
61 $dbh->do($query) || mydberror($query);
64 sub retrieve_accounts {
66 "SELECT c.accno, c.description, c.link, c.id, " .
67 "d.inventory_accno_id, d.income_accno_id, d.expense_accno_id " .
68 "FROM chart c, defaults d " .
69 "WHERE c.link LIKE '%IC%' " .
72 my $sth = $dbh->prepare($query);
73 $sth->execute() || mydberror($query);
75 my ($acc_inventory, $acc_income, $acc_expense) = ({}, {}, {});
77 "IC" => $acc_inventory,
78 "IC_income" => $acc_income,
79 "IC_sale" => $acc_income,
80 "IC_expense" => $acc_expense,
81 "IC_cogs" => $acc_expense,
84 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
85 foreach my $key (split(/:/, $ref->{link})) {
86 next unless ($key_map{$key});
87 $key_map{$key}->{$ref->{"id"}} = {
88 "accno" => $ref->{"accno"},
89 "description" => $ref->{"description"},
91 "selected" => ($ref->{id} eq $ref->{inventory_accno_id})
92 || ($ref->{id} eq $ref->{income_accno_id})
93 || ($ref->{id} eq $ref->{expense_accno_id}) ?
101 [sort({ $a->{"accno"} cmp $b->{"accno"} } values(%{$acc_inventory}))];
103 [sort({ $a->{"accno"} cmp $b->{"accno"} } values(%{$acc_income}))];
105 [sort({ $a->{"accno"} cmp $b->{"accno"} } values(%{$acc_expense}))];
107 return ($acc_inventory, $acc_income, $acc_expense);
110 sub retrieve_buchungsgruppen {
115 " ci.accno AS inventory_accno, " .
116 " ci0.accno AS income_accno_0, " .
117 " ce0.accno AS expense_accno_0, " .
118 " ci1.accno AS income_accno_1, " .
119 " ce1.accno AS expense_accno_1, " .
120 " ci2.accno AS income_accno_2, " .
121 " ce2.accno AS expense_accno_2, " .
122 " ci3.accno AS income_accno_3, " .
123 " ce3.accno AS expense_accno_3 " .
124 "FROM buchungsgruppen bg " .
125 "LEFT JOIN chart ci ON bg.inventory_accno_id = ci.id " .
126 "LEFT JOIN chart ci0 ON bg.income_accno_id_0 = ci0.id " .
127 "LEFT JOIN chart ce0 ON bg.expense_accno_id_0 = ce0.id " .
128 "LEFT JOIN chart ci1 ON bg.income_accno_id_1 = ci1.id " .
129 "LEFT JOIN chart ce1 ON bg.expense_accno_id_1 = ce1.id " .
130 "LEFT JOIN chart ci2 ON bg.income_accno_id_2 = ci2.id " .
131 "LEFT JOIN chart ce2 ON bg.expense_accno_id_2 = ce2.id " .
132 "LEFT JOIN chart ci3 ON bg.income_accno_id_3 = ci3.id " .
133 "LEFT JOIN chart ce3 ON bg.expense_accno_id_3 = ce3.id";
134 my $sth = $dbh->prepare($query);
135 $sth->execute() || mydberror($query);
137 while (my $ref = $sth->fetchrow_hashref()) {
138 push(@buchungsgruppen, $ref);
142 return \@buchungsgruppen;
145 sub update_known_buchungsgruppen {
146 my ($buchungsgruppen) = @_;
151 "SELECT id, inventory_accno_id, income_accno_id, expense_accno_id " .
153 "WHERE NOT inventory_accno_id ISNULL AND (inventory_accno_id > 0)";
154 my $sth = $dbh->prepare($query);
155 $sth->execute() || mydberror($query);
157 my $query_update = "UPDATE parts SET buchungsgruppen_id = ? WHERE id = ?";
158 my $sth_update = $dbh->prepare($query_update);
160 while (my $ref = $sth->fetchrow_hashref()) {
161 foreach my $bg (@{$buchungsgruppen}) {
162 if (($ref->{"inventory_accno_id"} == $bg->{"inventory_accno_id"}) &&
163 ($ref->{"income_accno_id"} == $bg->{"income_accno_id_0"}) &&
164 ($ref->{"expense_accno_id"} == $bg->{"expense_accno_id_0"})) {
165 $sth_update->execute($bg->{"id"}, $ref->{"id"}) ||
166 mydberror($query_update . " ($bg->{id}, $ref->{id})");
174 "SELECT id, inventory_accno_id, income_accno_id, expense_accno_id " .
176 "WHERE inventory_accno_id ISNULL OR (inventory_accno_id = 0)";
177 my $sth = $dbh->prepare($query);
178 $sth->execute() || mydberror($query);
180 while (my $ref = $sth->fetchrow_hashref()) {
181 foreach my $bg (@{$buchungsgruppen}) {
182 if (($ref->{"income_accno_id"} == $bg->{"income_accno_id_0"}) &&
183 ($ref->{"expense_accno_id"} == $bg->{"expense_accno_id_0"})) {
184 $sth_update->execute($bg->{"id"}, $ref->{"id"}) ||
185 mydberror($query_update . " ($bg->{id}, $ref->{id})");
191 $sth_update->finish();
194 sub retrieve_unknown_accno_combinations {
195 my ($buchungsgruppen) = @_;
197 my (@parts, @services, $sth, $query, $ref);
201 "p.inventory_accno_id, p.income_accno_id, p.expense_accno_id, " .
202 "c1.accno AS inventory_accno, c1.description AS inventory_description, " .
203 "c2.accno AS income_accno, c2.description AS income_description, " .
204 "c3.accno AS expense_accno, c3.description AS expense_description " .
206 "LEFT JOIN chart c1 ON p.inventory_accno_id = c1.id " .
207 "LEFT JOIN chart c2 ON p.income_accno_id = c2.id " .
208 "LEFT JOIN chart c3 ON p.expense_accno_id = c3.id " .
209 "WHERE NOT inventory_accno_id ISNULL AND (inventory_accno_id > 0)";
211 $sth = $dbh->prepare($query);
212 $sth->execute() || mydberror($query);
214 while ($ref = $sth->fetchrow_hashref()) {
217 foreach my $bg (@{$buchungsgruppen}) {
218 if (($ref->{"inventory_accno_id"} == $bg->{"inventory_accno_id"}) &&
219 ($ref->{"income_accno_id"} == $bg->{"income_accno_id_0"}) &&
220 ($ref->{"expense_accno_id"} == $bg->{"expense_accno_id_0"})) {
226 push(@parts, $ref) unless ($found);
232 "p.income_accno_id, p.expense_accno_id, " .
233 "c2.accno AS income_accno, c2.description AS income_description, " .
234 "c3.accno AS expense_accno, c3.description AS expense_description " .
236 "LEFT JOIN chart c1 ON p.inventory_accno_id = c1.id " .
237 "LEFT JOIN chart c2 ON p.income_accno_id = c2.id " .
238 "LEFT JOIN chart c3 ON p.expense_accno_id = c3.id " .
239 "WHERE inventory_accno_id ISNULL OR (inventory_accno_id = 0)";
241 $sth = $dbh->prepare($query);
242 $sth->execute() || mydberror($query);
244 while ($ref = $sth->fetchrow_hashref()) {
247 foreach my $bg (@{$buchungsgruppen}) {
248 if (($ref->{"income_accno_id"} == $bg->{"income_accno_id_0"}) &&
249 ($ref->{"expense_accno_id"} == $bg->{"expense_accno_id_0"})) {
255 push(@services, $ref) unless ($found);
259 return (\@parts, \@services);
262 sub display_create_bgs_dialog {
264 $acc_inventory, $acc_income, $acc_expense,
265 $buchungsgruppen) = @_;
267 foreach my $entry (@{$list}) {
268 $entry->{"ACC_INVENTORY"} = $acc_inventory;
269 $entry->{"ACC_INCOME"} = $acc_income;
270 $entry->{"ACC_EXPENSE"} = $acc_expense;
271 $entry->{"eur"} = $main::eur;
274 print($form->parse_html_template("dbupgrade/buchungsgruppen_${type}",
276 "BUCHUNGSGRUPPEN" => $buchungsgruppen,
280 sub create_buchungsgruppen {
281 my $form = $main::form;
283 $main::lxdebug->dump(0, "gaby", $form);
284 for (my $i = 1; $i <= $form->{"rowcount"}; $i++) {
285 next unless ($form->{"description_$i"} &&
286 $form->{"inventory_accno_id_$i"} &&
287 $form->{"income_accno_id_0_$i"} &&
288 $form->{"expense_accno_id_0_$i"} &&
289 $form->{"income_accno_id_1_$i"} &&
290 $form->{"expense_accno_id_1_$i"} &&
291 $form->{"income_accno_id_2_$i"} &&
292 $form->{"expense_accno_id_2_$i"} &&
293 $form->{"income_accno_id_3_$i"} &&
294 $form->{"expense_accno_id_3_$i"});
297 "INSERT INTO buchungsgruppen (" .
298 "description, inventory_accno_id, " .
299 "income_accno_id_0, expense_accno_id_0, " .
300 "income_accno_id_1, expense_accno_id_1, " .
301 "income_accno_id_2, expense_accno_id_2, " .
302 "income_accno_id_3, expense_accno_id_3) " .
303 "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
304 my @values = ($form->{"description_$i"});
306 foreach my $acc (qw(inventory_accno_id
307 income_accno_id_0 expense_accno_id_0
308 income_accno_id_1 expense_accno_id_1
309 income_accno_id_2 expense_accno_id_2
310 income_accno_id_3 expense_accno_id_3)) {
311 push(@values, (split(/--/, $form->{"${acc}_${i}"}))[0]);
314 $main::lxdebug->dump(0, "klaus$i", \@values);
316 mydoquery($query, @values);
317 $main::lxdebug->message(0, "nachklausi$i");
320 $main::lxdebug->message(0, "commit: " . $dbh->commit());
321 $main::lxdebug->message(0, "errstr: " . $dbh->errstr);
325 sub retrieve_std_inventory_accno_id {
328 $query = "SELECT coa FROM defaults";
329 my ($coa) = $dbh->selectrow_array($query);
333 if ($coa eq "Germany-DATEV-SKR03EU") {
334 $inventory_accno = "3980";
336 } elsif ($coa eq "Germany-DATEV-SKR04EU") {
337 $inventory_accno = "1140";
340 my $inventory_accno_id;
341 if ($inventory_accno) {
342 $query = "SELECT id FROM chart WHERE accno = $inventory_accno";
343 ($inventory_accno_id) = $dbh->selectrow_array($query);
346 if (!$inventory_accno_id) {
347 $query = "SELECT id, link FROM chart WHERE link LIKE '%IC%'";
348 my $sth = $dbh->prepare($query);
349 $sth->execute() || mydberror($query);
351 while (my $ref = $sth->fetchrow_hashref()) {
352 if (grep({ $_ eq "IC" } split(/:/, $ref->{"link"}))) {
353 $inventory_accno_id = $ref->{"id"};
360 $form->{"std_inventory_accno_id"} = $inventory_accno_id;
364 if ($main::form->{"action2"} eq "create_buchungsgruppen") {
365 create_buchungsgruppen();
368 retrieve_std_inventory_accno_id();
370 # Set all taxzone_id columns = 0.
373 # If balancing is off then force parts.inventory_accno_id to
374 # a single value for parts.
375 force_inventory_accno_id_for_parts() if ($main::eur);
377 # Force "IC" to be present in chart.link for all accounts
378 # which have been used as inventory accounts in parts.
381 # Assign buchungsgruppen_ids in parts for known combinations
382 # of inventory_accno_id, income_accno_id, expense_accno_id.
383 my $buchungsgruppen = retrieve_buchungsgruppen();
385 update_known_buchungsgruppen($buchungsgruppen);
387 # Retrieve all distinct combinations of inventory_accno_id,
388 # income_accno_id and expense_accno_id for which there's no
389 # Buchungsgruppe. Then let the user create new ones.
390 ($parts, $services) = retrieve_unknown_accno_combinations($buchungsgruppen);
392 my ($acc_inventory, $acc_income, $acc_expense) = retrieve_accounts();
394 print($form->parse_html_template("dbupgrade/buchungsgruppen_header"));
396 if (scalar(@{$parts})) {
397 display_create_bgs_dialog("parts", $parts,
398 $acc_inventory, $acc_income, $acc_expense,
403 if (scalar(@{$services})) {
404 display_create_bgs_dialog("services", $services,
405 $acc_inventory, $acc_income, $acc_expense,
410 print($form->parse_html_template("dbupgrade/buchungsgruppen_footer"));