3 # Datenbankupgrade: Einfuehrung von Buchungsgruppen
5 die("This script cannot be run from the command line.") unless ($main::form);
9 die($dbup_locale->text("Database update error:") .
10 "<br>$msg<br>" . $DBI::errstr);
14 my ($query, @values) = @_;
16 $dbh->do($query, undef, @values) ||
17 mydberror($query . " (" . join(", ", @values) . ")");
21 foreach my $table (qw(customer vendor ar ap oe)) {
22 my $query = "UPDATE ${table} SET taxzone_id = 0";
23 $dbh->do($query) || mydberror($query);
32 " (SELECT DISTINCT inventory_accno_id " .
34 " WHERE (NOT inventory_accno_id ISNULL) AND (inventory_accno_id > 0))";
36 my $sth = $dbh->prepare($query);
37 $sth->execute() || mydberror($query);
39 my $query_update = "UPDATE chart SET link = ? WHERE id = ?";
40 my $sth_update = $dbh->prepare($query_update);
42 while (my $ref = $sth->fetchrow_hashref()) {
44 map({ $links{$_} = 1 } split(/:/, $ref->{"link"}));
46 my $new_link = join(":", keys(%links));
47 $sth_update->execute($new_link, $ref->{"id"}) ||
48 mydberror($query_update . " ($new_link, $ref->{id})");
52 $sth_update->finish();
55 sub force_inventory_accno_id_for_parts {
57 "SELECT inventory_accno_id " .
58 "FROM buchungsgruppen " .
59 "WHERE description = 'Standard 16%'";
61 my ($bg_id) = $dbh->selectrow_array($query);
65 "UPDATE parts SET inventory_accno_id = $bg_id " .
66 "WHERE (NOT inventory_accno_id ISNULL) AND (inventory_accno_id > 0)";
67 $dbh->do($query) || mydberror($query);
71 sub retrieve_accounts {
73 "SELECT c.accno, c.description, c.link, c.id, " .
74 "d.inventory_accno_id, d.income_accno_id, d.expense_accno_id " .
75 "FROM chart c, defaults d " .
76 "WHERE c.link LIKE '%IC%' " .
79 my $sth = $dbh->prepare($query);
80 $sth->execute() || mydberror($query);
82 my ($acc_inventory, $acc_income, $acc_expense) = ({}, {}, {});
84 "IC" => $acc_inventory,
85 "IC_income" => $acc_income,
86 "IC_sale" => $acc_income,
87 "IC_expense" => $acc_expense,
88 "IC_cogs" => $acc_expense,
91 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
92 foreach my $key (split(/:/, $ref->{link})) {
93 next unless ($key_map{$key});
94 $key_map{$key}->{$ref->{"id"}} = {
95 "accno" => $ref->{"accno"},
96 "description" => $ref->{"description"},
98 "selected" => ($ref->{id} eq $ref->{inventory_accno_id})
99 || ($ref->{id} eq $ref->{income_accno_id})
100 || ($ref->{id} eq $ref->{expense_accno_id}) ?
108 [sort({ $a->{"accno"} cmp $b->{"accno"} } values(%{$acc_inventory}))];
110 [sort({ $a->{"accno"} cmp $b->{"accno"} } values(%{$acc_income}))];
112 [sort({ $a->{"accno"} cmp $b->{"accno"} } values(%{$acc_expense}))];
114 return ($acc_inventory, $acc_income, $acc_expense);
117 sub retrieve_buchungsgruppen {
122 " ci.accno AS inventory_accno, " .
123 " ci0.accno AS income_accno_0, " .
124 " ce0.accno AS expense_accno_0, " .
125 " ci1.accno AS income_accno_1, " .
126 " ce1.accno AS expense_accno_1, " .
127 " ci2.accno AS income_accno_2, " .
128 " ce2.accno AS expense_accno_2, " .
129 " ci3.accno AS income_accno_3, " .
130 " ce3.accno AS expense_accno_3 " .
131 "FROM buchungsgruppen bg " .
132 "LEFT JOIN chart ci ON bg.inventory_accno_id = ci.id " .
133 "LEFT JOIN chart ci0 ON bg.income_accno_id_0 = ci0.id " .
134 "LEFT JOIN chart ce0 ON bg.expense_accno_id_0 = ce0.id " .
135 "LEFT JOIN chart ci1 ON bg.income_accno_id_1 = ci1.id " .
136 "LEFT JOIN chart ce1 ON bg.expense_accno_id_1 = ce1.id " .
137 "LEFT JOIN chart ci2 ON bg.income_accno_id_2 = ci2.id " .
138 "LEFT JOIN chart ce2 ON bg.expense_accno_id_2 = ce2.id " .
139 "LEFT JOIN chart ci3 ON bg.income_accno_id_3 = ci3.id " .
140 "LEFT JOIN chart ce3 ON bg.expense_accno_id_3 = ce3.id";
141 my $sth = $dbh->prepare($query);
142 $sth->execute() || mydberror($query);
144 while (my $ref = $sth->fetchrow_hashref()) {
145 push(@buchungsgruppen, $ref);
149 return \@buchungsgruppen;
152 sub update_known_buchungsgruppen {
153 my ($buchungsgruppen) = @_;
158 "SELECT id, inventory_accno_id, income_accno_id, expense_accno_id " .
160 "WHERE NOT inventory_accno_id ISNULL AND (inventory_accno_id > 0) ";
161 my $sth = $dbh->prepare($query);
162 $sth->execute() || mydberror($query);
164 my $query_update = "UPDATE parts SET buchungsgruppen_id = ?";
165 $query_update .= ", inventory_accno_id = ?" if $::lx_office_conf{system}->{eur};
166 $query_update .= " WHERE id = ?";
167 my $sth_update = $dbh->prepare($query_update);
169 while (my $ref = $sth->fetchrow_hashref()) {
170 foreach my $bg (@{$buchungsgruppen}) {
171 if (($::lx_office_conf{system}->{eur} ||
172 ($ref->{"inventory_accno_id"} == $bg->{"inventory_accno_id"})) &&
173 ($ref->{"income_accno_id"} == $bg->{"income_accno_id_0"}) &&
174 ($ref->{"expense_accno_id"} == $bg->{"expense_accno_id_0"})) {
175 my @values = ($bg->{"id"}, $ref->{"id"});
176 splice(@values, 1, 0, $bg->{"inventory_accno_id"}) if $::lx_office_conf{system}->{eur};
177 $sth_update->execute(@values) ||
178 mydberror($query_update . " (" . join(", ", @values) . ")");
186 "SELECT id, inventory_accno_id, income_accno_id, expense_accno_id " .
188 "WHERE (inventory_accno_id ISNULL OR (inventory_accno_id = 0)) AND " .
190 my $sth = $dbh->prepare($query);
191 $sth->execute() || mydberror($query);
193 while (my $ref = $sth->fetchrow_hashref()) {
194 foreach my $bg (@{$buchungsgruppen}) {
195 if (($ref->{"income_accno_id"} == $bg->{"income_accno_id_0"}) &&
196 ($ref->{"expense_accno_id"} == $bg->{"expense_accno_id_0"})) {
197 my @values = ($bg->{"id"}, $ref->{"id"});
198 splice(@values, 1, 0, undef) if $::lx_office_conf{system}->{eur};
199 $sth_update->execute(@values) ||
200 mydberror($query_update . " (" . join(", ", @values) . ")");
206 $sth_update->finish();
209 sub retrieve_unknown_accno_combinations {
210 my ($buchungsgruppen) = @_;
212 my (@parts, @services, @assemblies, $sth, $query, $ref);
216 "p.inventory_accno_id, p.income_accno_id, p.expense_accno_id, " .
217 "c1.accno AS inventory_accno, c1.description AS inventory_description, " .
218 "c2.accno AS income_accno, c2.description AS income_description, " .
219 "c3.accno AS expense_accno, c3.description AS expense_description " .
221 "LEFT JOIN chart c1 ON p.inventory_accno_id = c1.id " .
222 "LEFT JOIN chart c2 ON p.income_accno_id = c2.id " .
223 "LEFT JOIN chart c3 ON p.expense_accno_id = c3.id " .
224 "WHERE NOT inventory_accno_id ISNULL AND (inventory_accno_id > 0)";
226 $sth = $dbh->prepare($query);
227 $sth->execute() || mydberror($query);
229 while ($ref = $sth->fetchrow_hashref()) {
232 foreach my $bg (@{$buchungsgruppen}) {
233 if (($ref->{"inventory_accno_id"} == $bg->{"inventory_accno_id"}) &&
234 ($ref->{"income_accno_id"} == $bg->{"income_accno_id_0"}) &&
235 ($ref->{"expense_accno_id"} == $bg->{"expense_accno_id_0"})) {
241 push(@parts, $ref) unless ($found);
247 "p.income_accno_id, p.expense_accno_id, " .
248 "c2.accno AS income_accno, c2.description AS income_description, " .
249 "c3.accno AS expense_accno, c3.description AS expense_description " .
251 "LEFT JOIN chart c2 ON p.income_accno_id = c2.id " .
252 "LEFT JOIN chart c3 ON p.expense_accno_id = c3.id " .
253 "WHERE (inventory_accno_id ISNULL OR (inventory_accno_id = 0)) AND " .
256 $sth = $dbh->prepare($query);
257 $sth->execute() || mydberror($query);
259 while ($ref = $sth->fetchrow_hashref()) {
262 foreach my $bg (@{$buchungsgruppen}) {
263 if (($ref->{"income_accno_id"} == $bg->{"income_accno_id_0"}) &&
264 ($ref->{"expense_accno_id"} == $bg->{"expense_accno_id_0"})) {
270 push(@services, $ref) unless ($found);
276 "p.income_accno_id, " .
277 "c.accno AS income_accno, c.description AS income_description " .
279 "LEFT JOIN chart c ON p.income_accno_id = c.id " .
280 "WHERE p.assembly AND " .
281 " (p.buchungsgruppen_id ISNULL OR (p.buchungsgruppen_id = 0))";
283 $sth = $dbh->prepare($query);
284 $sth->execute() || mydberror($query);
286 while ($ref = $sth->fetchrow_hashref()) {
287 push(@assemblies, $ref);
290 return (\@parts, \@services, \@assemblies);
293 sub display_create_bgs_dialog {
295 $acc_inventory, $acc_income, $acc_expense,
296 $buchungsgruppen) = @_;
298 foreach my $entry (@{$list}) {
299 $entry->{"ACC_INVENTORY"} = $acc_inventory;
300 $entry->{"ACC_INCOME"} = $acc_income;
301 $entry->{"ACC_EXPENSE"} = $acc_expense;
302 $entry->{"eur"} = $::lx_office_conf{system}->{eur};
305 # $form->parse_html_template("dbupgrade/buchungsgruppen_parts")
306 # $form->parse_html_template("dbupgrade/buchungsgruppen_services")
307 # $form->parse_html_template("dbupgrade/buchungsgruppen_assemblies")
309 print($form->parse_html_template("dbupgrade/buchungsgruppen_${type}",
311 "BUCHUNGSGRUPPEN" => $buchungsgruppen,
315 sub create_buchungsgruppen {
316 my $form = $main::form;
318 for (my $i = 1; $i <= $form->{"rowcount"}; $i++) {
319 next unless ($form->{"description_$i"} &&
320 $form->{"inventory_accno_id_$i"} &&
321 $form->{"income_accno_id_0_$i"} &&
322 $form->{"expense_accno_id_0_$i"} &&
323 $form->{"income_accno_id_1_$i"} &&
324 $form->{"expense_accno_id_1_$i"} &&
325 $form->{"income_accno_id_2_$i"} &&
326 $form->{"expense_accno_id_2_$i"} &&
327 $form->{"income_accno_id_3_$i"} &&
328 $form->{"expense_accno_id_3_$i"});
330 my $query = "SELECT nextval('id')";
331 my ($id) = $dbh->selectrow_array($query);
333 "INSERT INTO buchungsgruppen (" .
334 "id, description, inventory_accno_id, " .
335 "income_accno_id_0, expense_accno_id_0, " .
336 "income_accno_id_1, expense_accno_id_1, " .
337 "income_accno_id_2, expense_accno_id_2, " .
338 "income_accno_id_3, expense_accno_id_3) " .
339 "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
340 my @values = ($id, $form->{"description_$i"});
342 foreach my $acc (qw(inventory_accno_id
343 income_accno_id_0 expense_accno_id_0
344 income_accno_id_1 expense_accno_id_1
345 income_accno_id_2 expense_accno_id_2
346 income_accno_id_3 expense_accno_id_3)) {
347 push(@values, (split(/--/, $form->{"${acc}_${i}"}))[0]);
350 mydoquery($query, @values);
352 $form->{"new_buchungsgruppen_id_$i"} = $id;
359 sub assign_buchungsgruppen_for_assemblies {
360 my ($query, $sth, $i);
364 "SET buchungsgruppen_id = ? " .
365 "WHERE assembly AND " .
366 "(buchungsgruppen_id ISNULL OR (buchungsgruppen_id = 0)) AND " .
367 "(income_accno_id = ?)";
368 $sth = $dbh->prepare($query);
370 for ($i = 1; $i <= $form->{"rowcount"}; $i++) {
371 next unless ($form->{"new_buchungsgruppen_id_$i"});
373 my @values = ($form->{"new_buchungsgruppen_id_$i"},
374 $form->{"income_accno_id_0_$i"});
375 $sth->execute(@values) ||
376 mydberror($query . " (" . join(", ", @values) . ")");
385 sub retrieve_std_inventory_accno_id {
388 $query = "SELECT coa FROM defaults";
389 my ($coa) = $dbh->selectrow_array($query);
393 if ($coa eq "Germany-DATEV-SKR03EU") {
394 $inventory_accno = "3980";
396 } elsif ($coa eq "Germany-DATEV-SKR04EU") {
397 $inventory_accno = "1140";
400 my $inventory_accno_id;
401 if ($inventory_accno) {
402 $query = "SELECT id FROM chart WHERE accno = '$inventory_accno'";
403 ($inventory_accno_id) = $dbh->selectrow_array($query);
406 if (!$inventory_accno_id) {
407 $query = "SELECT id, link FROM chart WHERE link LIKE '%IC%'";
408 my $sth = $dbh->prepare($query);
409 $sth->execute() || mydberror($query);
411 while (my $ref = $sth->fetchrow_hashref()) {
412 if (grep({ $_ eq "IC" } split(/:/, $ref->{"link"}))) {
413 $inventory_accno_id = $ref->{"id"};
420 $inventory_accno_id = 1 unless ($inventory_accno_id);
422 $form->{"std_inventory_accno_id"} = $inventory_accno_id;
426 if ($main::form->{"action2"} eq "create_buchungsgruppen") {
427 create_buchungsgruppen();
430 if ($main::form->{"action2"} eq "create_buchungsgruppen_assemblies") {
431 create_buchungsgruppen();
432 assign_buchungsgruppen_for_assemblies();
435 retrieve_std_inventory_accno_id();
437 # Set all taxzone_id columns = 0.
440 # If balancing is off then force parts.inventory_accno_id to
441 # a single value for parts.
442 force_inventory_accno_id_for_parts() if $::lx_office_conf{system}->{eur};
444 # Force "IC" to be present in chart.link for all accounts
445 # which have been used as inventory accounts in parts.
448 # Assign buchungsgruppen_ids in parts for known combinations
449 # of inventory_accno_id, income_accno_id, expense_accno_id.
450 my $buchungsgruppen = retrieve_buchungsgruppen();
452 update_known_buchungsgruppen($buchungsgruppen);
454 # Retrieve all distinct combinations of inventory_accno_id,
455 # income_accno_id and expense_accno_id for which there's no
456 # Buchungsgruppe. Then let the user create new ones.
457 ($parts, $services, $assemblies) =
458 retrieve_unknown_accno_combinations($buchungsgruppen);
460 my ($acc_inventory, $acc_income, $acc_expense) = retrieve_accounts();
462 print($form->parse_html_template("dbupgrade/buchungsgruppen_header"));
464 if (scalar(@{$parts})) {
465 display_create_bgs_dialog("parts", $parts,
466 $acc_inventory, $acc_income, $acc_expense,
470 print($form->parse_html_template("dbupgrade/buchungsgruppen_parts_done"));
473 if (scalar(@{$services})) {
474 display_create_bgs_dialog("services", $services,
475 $acc_inventory, $acc_income, $acc_expense,
479 print($form->parse_html_template("dbupgrade/buchungsgruppen_services_done"));
482 if (scalar(@{$assemblies})) {
483 display_create_bgs_dialog("assemblies", $assemblies,
484 $acc_inventory, $acc_income, $acc_expense,
488 print($form->parse_html_template("dbupgrade/buchungsgruppen_assemblies_done"));
491 print($form->parse_html_template("dbupgrade/buchungsgruppen_footer"));