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

   1 #!/usr/bin/perl
   2
   3 die("This script cannot be run from the command line.") unless ($main::form);
   4
   5 sub mydberror {
   6   my ($msg) = @_;
   7   die($dbup_locale->text("Database update error:") .
   8       "<br>$msg<br>" . $DBI::errstr);
   9 }
  10
  11 sub mydoquery {
  12   my ($query, @values) = @_;
  13
  14   $dbh->do($query, undef, @values) ||
  15     mydberror($query . " (" . join(", ", @values) . ")");
  16 }
  17
  18 sub set_taxzone_ids {
  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);
  22   }
  23 }
  24
  25 sub set_ic_links {
  26   my $query =
  27     "SELECT id, link " .
  28     "FROM chart " .
  29     "WHERE id IN " .
  30     "  (SELECT DISTINCT inventory_accno_id " .
  31     "   FROM parts " .
  32     "   WHERE (NOT inventory_accno_id ISNULL) AND (inventory_accno_id > 0))";
  33
  34   my $sth = $dbh->prepare($query);
  35   $sth->execute() || mydberror($query);
  36
  37   my $query_update = "UPDATE chart SET link = ? WHERE id = ?";
  38   my $sth_update = $dbh->prepare($query_update);
  39
  40   while (my $ref = $sth->fetchrow_hashref()) {
  41     my %links;
  42     map({ $links{$_} = 1 } split(/:/, $ref->{"link"}));
  43     $links{"IC"} = 1;
  44     my $new_link = join(":", keys(%links));
  45     $sth_update->execute($new_link, $ref->{"id"}) ||
  46       mydberror($query_update . " ($new_link, $ref->{id})");
  47   }
  48
  49   $sth->finish();
  50   $sth_update->finish();
  51 }
  52
  53 sub force_inventory_accno_id_for_parts {
  54   my $query =
  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)";
  60
  61   $dbh->do($query) || mydberror($query);
  62 }
  63
  64 sub retrieve_accounts {
  65   my $query =
  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%' " .
  70     "ORDER BY c.accno";
  71
  72   my $sth = $dbh->prepare($query);
  73   $sth->execute() || mydberror($query);
  74
  75   my ($acc_inventory, $acc_income, $acc_expense) = ({}, {}, {});
  76   my %key_map = (
  77     "IC" => $acc_inventory,
  78     "IC_income" => $acc_income,
  79     "IC_sale" => $acc_income,
  80     "IC_expense" => $acc_expense,
  81     "IC_cogs" => $acc_expense,
  82     );
  83
  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"},
  90         "id" => $ref->{"id"},
  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}) ?
  94           "selected" : "",
  95       };
  96     }
  97   }
  98   $sth->finish();
  99
 100   $acc_inventory =
 101     [sort({ $a->{"accno"} cmp $b->{"accno"} } values(%{$acc_inventory}))];
 102   $acc_income =
 103     [sort({ $a->{"accno"} cmp $b->{"accno"} } values(%{$acc_income}))];
 104   $acc_expense =
 105     [sort({ $a->{"accno"} cmp $b->{"accno"} } values(%{$acc_expense}))];
 106
 107   return ($acc_inventory, $acc_income, $acc_expense);
 108 }
 109
 110 sub retrieve_buchungsgruppen {
 111   my @buchungsgruppen;
 112
 113   my $query =
 114     "SELECT bg.*, " .
 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);
 136
 137   while (my $ref = $sth->fetchrow_hashref()) {
 138     push(@buchungsgruppen, $ref);
 139   }
 140   $sth->finish();
 141
 142   return \@buchungsgruppen;
 143 }
 144
 145 sub update_known_buchungsgruppen {
 146   my ($buchungsgruppen) = @_;
 147
 148   my @updates;
 149
 150   my $query =
 151     "SELECT id, inventory_accno_id, income_accno_id, expense_accno_id " .
 152     "FROM parts " .
 153     "WHERE NOT inventory_accno_id ISNULL AND (inventory_accno_id > 0)";
 154   my $sth = $dbh->prepare($query);
 155   $sth->execute() || mydberror($query);
 156
 157   my $query_update = "UPDATE parts SET buchungsgruppen_id = ?";
 158   $query_update .= ", inventory_accno_id = ?" if ($main::eur);
 159   $query_update .= " WHERE id = ?";
 160   my $sth_update = $dbh->prepare($query_update);
 161
 162   while (my $ref = $sth->fetchrow_hashref()) {
 163     foreach my $bg (@{$buchungsgruppen}) {
 164       if (($main::eur ||
 165            ($ref->{"inventory_accno_id"} == $bg->{"inventory_accno_id"})) &&
 166           ($ref->{"income_accno_id"} == $bg->{"income_accno_id_0"}) &&
 167           ($ref->{"expense_accno_id"} == $bg->{"expense_accno_id_0"})) {
 168         my @values = ($bg->{"id"}, $ref->{"id"});
 169         splice(@values, 1, 0, $bg->{"inventory_accno_id"}) if ($main::eur);
 170         $sth_update->execute(@values) ||
 171           mydberror($query_update . " (" . join(", ", @values) . ")");
 172         last;
 173       }
 174     }
 175   }
 176   $sth->finish();
 177
 178   my $query =
 179     "SELECT id, inventory_accno_id, income_accno_id, expense_accno_id " .
 180     "FROM parts " .
 181     "WHERE inventory_accno_id ISNULL OR (inventory_accno_id = 0)";
 182   my $sth = $dbh->prepare($query);
 183   $sth->execute() || mydberror($query);
 184
 185   while (my $ref = $sth->fetchrow_hashref()) {
 186     foreach my $bg (@{$buchungsgruppen}) {
 187       if (($ref->{"income_accno_id"} == $bg->{"income_accno_id_0"}) &&
 188           ($ref->{"expense_accno_id"} == $bg->{"expense_accno_id_0"})) {
 189         my @values = ($bg->{"id"}, $ref->{"id"});
 190         splice(@values, 1, 0, undef) if ($main::eur);
 191         $sth_update->execute(@values) ||
 192           mydberror($query_update . " (" . join(", ", @values) . ")");
 193         last;
 194       }
 195     }
 196   }
 197   $sth->finish();
 198   $sth_update->finish();
 199 }
 200
 201 sub retrieve_unknown_accno_combinations {
 202   my ($buchungsgruppen) = @_;
 203
 204   my (@parts, @services, $sth, $query, $ref);
 205
 206   $query =
 207     "SELECT DISTINCT " .
 208     "p.inventory_accno_id, p.income_accno_id, p.expense_accno_id, " .
 209     "c1.accno AS inventory_accno, c1.description AS inventory_description, " .
 210     "c2.accno AS income_accno, c2.description AS income_description, " .
 211     "c3.accno AS expense_accno, c3.description AS expense_description " .
 212     "FROM parts p " .
 213     "LEFT JOIN chart c1 ON p.inventory_accno_id = c1.id " .
 214     "LEFT JOIN chart c2 ON p.income_accno_id = c2.id " .
 215     "LEFT JOIN chart c3 ON p.expense_accno_id = c3.id " .
 216     "WHERE NOT inventory_accno_id ISNULL AND (inventory_accno_id > 0)";
 217
 218   $sth = $dbh->prepare($query);
 219   $sth->execute() || mydberror($query);
 220
 221   while ($ref = $sth->fetchrow_hashref()) {
 222     my $found = 0;
 223
 224     foreach my $bg (@{$buchungsgruppen}) {
 225       if (($ref->{"inventory_accno_id"} == $bg->{"inventory_accno_id"}) &&
 226           ($ref->{"income_accno_id"} == $bg->{"income_accno_id_0"}) &&
 227           ($ref->{"expense_accno_id"} == $bg->{"expense_accno_id_0"})) {
 228         $found = 1;
 229         last;
 230       }
 231     }
 232
 233     push(@parts, $ref) unless ($found);
 234   }
 235   $sth->finish();
 236
 237   $query =
 238     "SELECT DISTINCT " .
 239     "p.income_accno_id, p.expense_accno_id, " .
 240     "c2.accno AS income_accno, c2.description AS income_description, " .
 241     "c3.accno AS expense_accno, c3.description AS expense_description " .
 242     "FROM parts p " .
 243     "LEFT JOIN chart c1 ON p.inventory_accno_id = c1.id " .
 244     "LEFT JOIN chart c2 ON p.income_accno_id = c2.id " .
 245     "LEFT JOIN chart c3 ON p.expense_accno_id = c3.id " .
 246     "WHERE inventory_accno_id ISNULL OR (inventory_accno_id = 0)";
 247
 248   $sth = $dbh->prepare($query);
 249   $sth->execute() || mydberror($query);
 250
 251   while ($ref = $sth->fetchrow_hashref()) {
 252     my $found = 0;
 253
 254     foreach my $bg (@{$buchungsgruppen}) {
 255       if (($ref->{"income_accno_id"} == $bg->{"income_accno_id_0"}) &&
 256           ($ref->{"expense_accno_id"} == $bg->{"expense_accno_id_0"})) {
 257         $found = 1;
 258         last;
 259       }
 260     }
 261
 262     push(@services, $ref) unless ($found);
 263   }
 264   $sth->finish();
 265
 266   return (\@parts, \@services);
 267 }
 268
 269 sub display_create_bgs_dialog {
 270   my ($type, $list,
 271       $acc_inventory, $acc_income, $acc_expense,
 272       $buchungsgruppen) = @_;
 273
 274   foreach my $entry (@{$list}) {
 275     $entry->{"ACC_INVENTORY"} = $acc_inventory;
 276     $entry->{"ACC_INCOME"} = $acc_income;
 277     $entry->{"ACC_EXPENSE"} = $acc_expense;
 278     $entry->{"eur"} = $main::eur;
 279   }
 280
 281   print($form->parse_html_template("dbupgrade/buchungsgruppen_${type}",
 282                                    { "LIST" => $list,
 283                                      "BUCHUNGSGRUPPEN" => $buchungsgruppen,
 284                                    }));
 285 }
 286
 287 sub create_buchungsgruppen {
 288   my $form = $main::form;
 289
 290   for (my $i = 1; $i <= $form->{"rowcount"}; $i++) {
 291     next unless ($form->{"description_$i"} &&
 292                  $form->{"inventory_accno_id_$i"} &&
 293                  $form->{"income_accno_id_0_$i"} &&
 294                  $form->{"expense_accno_id_0_$i"} &&
 295                  $form->{"income_accno_id_1_$i"} &&
 296                  $form->{"expense_accno_id_1_$i"} &&
 297                  $form->{"income_accno_id_2_$i"} &&
 298                  $form->{"expense_accno_id_2_$i"} &&
 299                  $form->{"income_accno_id_3_$i"} &&
 300                  $form->{"expense_accno_id_3_$i"});
 301
 302     my $query =
 303       "INSERT INTO buchungsgruppen (" .
 304       "description, inventory_accno_id, " .
 305       "income_accno_id_0, expense_accno_id_0, " .
 306       "income_accno_id_1, expense_accno_id_1, " .
 307       "income_accno_id_2, expense_accno_id_2, " .
 308       "income_accno_id_3, expense_accno_id_3) " .
 309       "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
 310     my @values = ($form->{"description_$i"});
 311
 312     foreach my $acc (qw(inventory_accno_id
 313                         income_accno_id_0 expense_accno_id_0
 314                         income_accno_id_1 expense_accno_id_1
 315                         income_accno_id_2 expense_accno_id_2
 316                         income_accno_id_3 expense_accno_id_3)) {
 317       push(@values, (split(/--/, $form->{"${acc}_${i}"}))[0]);
 318     }
 319
 320     mydoquery($query, @values);
 321   }
 322
 323   $dbh->commit();
 324   $dbh->begin_work();
 325 }
 326
 327 sub retrieve_std_inventory_accno_id {
 328   my $query;
 329
 330   $query = "SELECT coa FROM defaults";
 331   my ($coa) = $dbh->selectrow_array($query);
 332
 333   my $inventory_accno;
 334
 335   if ($coa eq "Germany-DATEV-SKR03EU") {
 336     $inventory_accno = "3980";
 337
 338   } elsif ($coa eq "Germany-DATEV-SKR04EU") {
 339     $inventory_accno = "1140";
 340   }
 341
 342   my $inventory_accno_id;
 343   if ($inventory_accno) {
 344     $query = "SELECT id FROM chart WHERE accno = $inventory_accno";
 345     ($inventory_accno_id) = $dbh->selectrow_array($query);
 346   }
 347
 348   if (!$inventory_accno_id) {
 349     $query = "SELECT id, link FROM chart WHERE link LIKE '%IC%'";
 350     my $sth = $dbh->prepare($query);
 351     $sth->execute() || mydberror($query);
 352
 353     while (my $ref = $sth->fetchrow_hashref()) {
 354       if (grep({ $_ eq "IC" } split(/:/, $ref->{"link"}))) {
 355         $inventory_accno_id = $ref->{"id"};
 356         last;
 357       }
 358     }
 359     $sth->finish();
 360   }
 361
 362   $form->{"std_inventory_accno_id"} = $inventory_accno_id;
 363 }
 364
 365 sub do_update {
 366   if ($main::form->{"action2"} eq "create_buchungsgruppen") {
 367     create_buchungsgruppen();
 368   }
 369
 370   retrieve_std_inventory_accno_id();
 371
 372   # Set all taxzone_id columns = 0.
 373   set_taxzone_ids();
 374
 375   # If balancing is off then force parts.inventory_accno_id to
 376   # a single value for parts.
 377   force_inventory_accno_id_for_parts() if ($main::eur);
 378
 379   # Force "IC" to be present in chart.link for all accounts
 380   # which have been used as inventory accounts in parts.
 381   set_ic_links();
 382
 383   # Assign buchungsgruppen_ids in parts for known combinations
 384   # of inventory_accno_id, income_accno_id, expense_accno_id.
 385   my $buchungsgruppen = retrieve_buchungsgruppen();
 386
 387   update_known_buchungsgruppen($buchungsgruppen);
 388
 389   # Retrieve all distinct combinations of inventory_accno_id,
 390   # income_accno_id and expense_accno_id for which there's no
 391   # Buchungsgruppe. Then let the user create new ones.
 392   ($parts, $services) = retrieve_unknown_accno_combinations($buchungsgruppen);
 393
 394   my ($acc_inventory, $acc_income, $acc_expense) = retrieve_accounts();
 395
 396   print($form->parse_html_template("dbupgrade/buchungsgruppen_header"));
 397
 398   if (scalar(@{$parts})) {
 399     display_create_bgs_dialog("parts", $parts,
 400                               $acc_inventory, $acc_income, $acc_expense,
 401                               $buchungsgruppen);
 402     return 2;
 403   }
 404
 405   if (scalar(@{$services})) {
 406     display_create_bgs_dialog("services", $services,
 407                               $acc_inventory, $acc_income, $acc_expense,
 408                               $buchungsgruppen);
 409     return 2;
 410   }
 411
 412   print($form->parse_html_template("dbupgrade/buchungsgruppen_footer"));
 413
 414   return 1;
 415 }
 416
 417 return do_update();