sql/Pg-upgrade/Pg-upgrade-2.2.0.33-2.2.0.34.pl

   1 #!/usr/bin/perl
   2
   3 # Datenbankupgrade: Einfuehrung von Buchungsgruppen
   4
   5 die("This script cannot be run from the command line.") unless ($main::form);
   6
   7 sub mydberror {
   8   my ($msg) = @_;
   9   die($dbup_locale->text("Database update error:") .
  10       "<br>$msg<br>" . $DBI::errstr);
  11 }
  12
  13 sub mydoquery {
  14   my ($query, @values) = @_;
  15
  16   $dbh->do($query, undef, @values) ||
  17     mydberror($query . " (" . join(", ", @values) . ")");
  18 }
  19
  20 sub set_taxzone_ids {
  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);
  24   }
  25 }
  26
  27 sub set_ic_links {
  28   my $query =
  29     "SELECT id, link " .
  30     "FROM chart " .
  31     "WHERE id IN " .
  32     "  (SELECT DISTINCT inventory_accno_id " .
  33     "   FROM parts " .
  34     "   WHERE (NOT inventory_accno_id ISNULL) AND (inventory_accno_id > 0))";
  35
  36   my $sth = $dbh->prepare($query);
  37   $sth->execute() || mydberror($query);
  38
  39   my $query_update = "UPDATE chart SET link = ? WHERE id = ?";
  40   my $sth_update = $dbh->prepare($query_update);
  41
  42   while (my $ref = $sth->fetchrow_hashref()) {
  43     my %links;
  44     map({ $links{$_} = 1 } split(/:/, $ref->{"link"}));
  45     $links{"IC"} = 1;
  46     my $new_link = join(":", keys(%links));
  47     $sth_update->execute($new_link, $ref->{"id"}) ||
  48       mydberror($query_update . " ($new_link, $ref->{id})");
  49   }
  50
  51   $sth->finish();
  52   $sth_update->finish();
  53 }
  54
  55 sub force_inventory_accno_id_for_parts {
  56   my $query =
  57     "SELECT inventory_accno_id " .
  58     "FROM buchungsgruppen " .
  59     "WHERE description = 'Standard 16%'";
  60
  61   my ($bg_id) = $dbh->selectrow_array($query);
  62
  63   if ($bg_id) {
  64     $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);
  68   }
  69 }
  70
  71 sub retrieve_accounts {
  72   my $query =
  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%' " .
  77     "ORDER BY c.accno";
  78
  79   my $sth = $dbh->prepare($query);
  80   $sth->execute() || mydberror($query);
  81
  82   my ($acc_inventory, $acc_income, $acc_expense) = ({}, {}, {});
  83   my %key_map = (
  84     "IC" => $acc_inventory,
  85     "IC_income" => $acc_income,
  86     "IC_sale" => $acc_income,
  87     "IC_expense" => $acc_expense,
  88     "IC_cogs" => $acc_expense,
  89     );
  90
  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"},
  97         "id" => $ref->{"id"},
  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}) ?
 101           "selected" : "",
 102       };
 103     }
 104   }
 105   $sth->finish();
 106
 107   $acc_inventory =
 108     [sort({ $a->{"accno"} cmp $b->{"accno"} } values(%{$acc_inventory}))];
 109   $acc_income =
 110     [sort({ $a->{"accno"} cmp $b->{"accno"} } values(%{$acc_income}))];
 111   $acc_expense =
 112     [sort({ $a->{"accno"} cmp $b->{"accno"} } values(%{$acc_expense}))];
 113
 114   return ($acc_inventory, $acc_income, $acc_expense);
 115 }
 116
 117 sub retrieve_buchungsgruppen {
 118   my @buchungsgruppen;
 119
 120   my $query =
 121     "SELECT bg.*, " .
 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);
 143
 144   while (my $ref = $sth->fetchrow_hashref()) {
 145     push(@buchungsgruppen, $ref);
 146   }
 147   $sth->finish();
 148
 149   return \@buchungsgruppen;
 150 }
 151
 152 sub update_known_buchungsgruppen {
 153   my ($buchungsgruppen) = @_;
 154
 155   my @updates;
 156
 157   my $query =
 158     "SELECT id, inventory_accno_id, income_accno_id, expense_accno_id " .
 159     "FROM parts " .
 160     "WHERE NOT inventory_accno_id ISNULL AND (inventory_accno_id > 0) ";
 161   my $sth = $dbh->prepare($query);
 162   $sth->execute() || mydberror($query);
 163
 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);
 168
 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) . ")");
 179         last;
 180       }
 181     }
 182   }
 183   $sth->finish();
 184
 185   my $query =
 186     "SELECT id, inventory_accno_id, income_accno_id, expense_accno_id " .
 187     "FROM parts " .
 188     "WHERE (inventory_accno_id ISNULL OR (inventory_accno_id = 0)) AND " .
 189     " NOT assembly";
 190   my $sth = $dbh->prepare($query);
 191   $sth->execute() || mydberror($query);
 192
 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) . ")");
 201         last;
 202       }
 203     }
 204   }
 205   $sth->finish();
 206   $sth_update->finish();
 207 }
 208
 209 sub retrieve_unknown_accno_combinations {
 210   my ($buchungsgruppen) = @_;
 211
 212   my (@parts, @services, @assemblies, $sth, $query, $ref);
 213
 214   $query =
 215     "SELECT DISTINCT " .
 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 " .
 220     "FROM parts p " .
 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)";
 225
 226   $sth = $dbh->prepare($query);
 227   $sth->execute() || mydberror($query);
 228
 229   while ($ref = $sth->fetchrow_hashref()) {
 230     my $found = 0;
 231
 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"})) {
 236         $found = 1;
 237         last;
 238       }
 239     }
 240
 241     push(@parts, $ref) unless ($found);
 242   }
 243   $sth->finish();
 244
 245   $query =
 246     "SELECT DISTINCT " .
 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 " .
 250     "FROM parts p " .
 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 " .
 254     " NOT assembly";
 255
 256   $sth = $dbh->prepare($query);
 257   $sth->execute() || mydberror($query);
 258
 259   while ($ref = $sth->fetchrow_hashref()) {
 260     my $found = 0;
 261
 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"})) {
 265         $found = 1;
 266         last;
 267       }
 268     }
 269
 270     push(@services, $ref) unless ($found);
 271   }
 272   $sth->finish();
 273
 274   $query =
 275     "SELECT DISTINCT " .
 276     "p.income_accno_id, " .
 277     "c.accno AS income_accno, c.description AS income_description " .
 278     "FROM parts p " .
 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))";
 282
 283   $sth = $dbh->prepare($query);
 284   $sth->execute() || mydberror($query);
 285
 286   while ($ref = $sth->fetchrow_hashref()) {
 287     push(@assemblies, $ref);
 288   }
 289
 290   return (\@parts, \@services, \@assemblies);
 291 }
 292
 293 sub display_create_bgs_dialog {
 294   my ($type, $list,
 295       $acc_inventory, $acc_income, $acc_expense,
 296       $buchungsgruppen) = @_;
 297
 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};
 303   }
 304
 305   # $form->parse_html_template("dbupgrade/buchungsgruppen_parts")
 306   # $form->parse_html_template("dbupgrade/buchungsgruppen_services")
 307   # $form->parse_html_template("dbupgrade/buchungsgruppen_assemblies")
 308
 309   print($form->parse_html_template("dbupgrade/buchungsgruppen_${type}",
 310                                    { "LIST"            => $list,
 311                                      "BUCHUNGSGRUPPEN" => $buchungsgruppen,
 312                                    }));
 313 }
 314
 315 sub create_buchungsgruppen {
 316   my $form = $main::form;
 317
 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"});
 329
 330     my $query = "SELECT nextval('id')";
 331     my ($id) = $dbh->selectrow_array($query);
 332     $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"});
 341
 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]);
 348     }
 349
 350     mydoquery($query, @values);
 351
 352     $form->{"new_buchungsgruppen_id_$i"} = $id;
 353   }
 354
 355   $dbh->commit();
 356   $dbh->begin_work();
 357 }
 358
 359 sub assign_buchungsgruppen_for_assemblies {
 360   my ($query, $sth, $i);
 361
 362   $query =
 363     "UPDATE parts " .
 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);
 369
 370   for ($i = 1; $i <= $form->{"rowcount"}; $i++) {
 371     next unless ($form->{"new_buchungsgruppen_id_$i"});
 372
 373     my @values = ($form->{"new_buchungsgruppen_id_$i"},
 374                   $form->{"income_accno_id_0_$i"});
 375     $sth->execute(@values) ||
 376       mydberror($query . " (" . join(", ", @values) . ")");
 377   }
 378
 379   $sth->finish();
 380
 381   $dbh->commit();
 382   $dbh->begin_work();
 383 }
 384
 385 sub retrieve_std_inventory_accno_id {
 386   my $query;
 387
 388   $query = "SELECT coa FROM defaults";
 389   my ($coa) = $dbh->selectrow_array($query);
 390
 391   my $inventory_accno;
 392
 393   if ($coa eq "Germany-DATEV-SKR03EU") {
 394     $inventory_accno = "3980";
 395
 396   } elsif ($coa eq "Germany-DATEV-SKR04EU") {
 397     $inventory_accno = "1140";
 398   }
 399
 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);
 404   }
 405
 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);
 410
 411     while (my $ref = $sth->fetchrow_hashref()) {
 412       if (grep({ $_ eq "IC" } split(/:/, $ref->{"link"}))) {
 413         $inventory_accno_id = $ref->{"id"};
 414         last;
 415       }
 416     }
 417     $sth->finish();
 418   }
 419
 420   $inventory_accno_id = 1 unless ($inventory_accno_id);
 421
 422   $form->{"std_inventory_accno_id"} = $inventory_accno_id;
 423 }
 424
 425 sub do_update {
 426   if ($main::form->{"action2"} eq "create_buchungsgruppen") {
 427     create_buchungsgruppen();
 428   }
 429
 430   if ($main::form->{"action2"} eq "create_buchungsgruppen_assemblies") {
 431     create_buchungsgruppen();
 432     assign_buchungsgruppen_for_assemblies();
 433   }
 434
 435   retrieve_std_inventory_accno_id();
 436
 437   # Set all taxzone_id columns = 0.
 438   set_taxzone_ids();
 439
 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};
 443
 444   # Force "IC" to be present in chart.link for all accounts
 445   # which have been used as inventory accounts in parts.
 446   set_ic_links();
 447
 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();
 451
 452   update_known_buchungsgruppen($buchungsgruppen);
 453
 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);
 459
 460   my ($acc_inventory, $acc_income, $acc_expense) = retrieve_accounts();
 461
 462   print($form->parse_html_template("dbupgrade/buchungsgruppen_header"));
 463
 464   if (scalar(@{$parts})) {
 465     display_create_bgs_dialog("parts", $parts,
 466                               $acc_inventory, $acc_income, $acc_expense,
 467                               $buchungsgruppen);
 468     return 2;
 469   } else {
 470     print($form->parse_html_template("dbupgrade/buchungsgruppen_parts_done"));
 471   }
 472
 473   if (scalar(@{$services})) {
 474     display_create_bgs_dialog("services", $services,
 475                               $acc_inventory, $acc_income, $acc_expense,
 476                               $buchungsgruppen);
 477     return 2;
 478   } else {
 479     print($form->parse_html_template("dbupgrade/buchungsgruppen_services_done"));
 480   }
 481
 482   if (scalar(@{$assemblies})) {
 483     display_create_bgs_dialog("assemblies", $assemblies,
 484                               $acc_inventory, $acc_income, $acc_expense,
 485                               $buchungsgruppen);
 486     return 2;
 487   } else {
 488     print($form->parse_html_template("dbupgrade/buchungsgruppen_assemblies_done"));
 489   }
 490
 491   print($form->parse_html_template("dbupgrade/buchungsgruppen_footer"));
 492
 493   return 1;
 494 }
 495
 496 return do_update();