projekt kivitendo

  #Get currencies:

  $query = qq|SELECT curr FROM currencies ORDER BY id|;

  $form->{CURRENCIES} = [];

  $sth = prepare_execute_query($form, $dbh, $query);

  $sth->execute || $form->dberror($query);

  while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {

    push @{ $form->{ CURRENCIES } } , $ref;

  }

  $sth->finish;

  #Which of them is the default currency?

  $query = qq|SELECT curr AS defaultcurrency FROM currencies WHERE id = (SELECT curr FROM defaults LIMIT 1);|;

  $sth   = $dbh->prepare($query);

  $sth->execute || $form->dberror($query);

  $form->{defaultcurrency}               = ($sth->fetchrow_hashref("NAME_lc"))->{defaultcurrency};

  $sth->finish;

  '(recommended) Insert the used currencies in the system. You can simply change the name of the currencies by editing the textfields above. Do not use a name of a currency that is already in use.' => '(empfohlen) Fügen Sie die verwaisten Währungen in Ihr System ein. Sie können den Namen der Währung einfach ändern, indem Sie die Felder oben bearbeiten. Benutzen Sie keine Namen von Währungen, die Sie bereits benutzen.',

  'Break up the update and contact a service provider.' => 'Diese Option bricht das Update ab. Bitte kontaktieren Sie Ihren Administrator oder beauftragen einen Dienstleister.',

  'From this version on it is necessary to name a default value.' => 'Ab dieser Version benötigt kivitendo eine Standardwährung.',

  'No default currency'         => 'Keine Standardwährung',

  'Orphaned currencies'         => 'Verwaiste Währungen',

  'Please enter the currency you are working with.' => 'Bitte geben Sie die Währung an, mit der Sie arbeiten.',

  'Replace the orphaned currencies by other not orphaned currencies. To do so, please delete the currency in the textfields above and replace it by another currency. You could loose or change unintentionally exchangerates. Go on very carefully since you could destroy transactions.' => 'Ersetze die Währungen durch andere gültige Währungen. Wenn Sie sich hierfür entscheiden, ersetzen Sie bitte alle Währungen, die oben angegeben sind, durch Währungen, die in Ihrem System ordnungsgemäß eingetragen sind. Alle eingetragenen Wechselkurse für die verwaiste Währung werden dabei gelöscht. Bitte gehen Sie sehr vorsichtig vor, denn die betroffenen Buchungen können unter Umständen kaputt gehen.',

  'That is why kivitendo could not find a default currency.' => 'Daher konnte kivitendo keine Standardwährung finden.',

  'The following currencies have been used, but they are not defined:' => 'Die folgenden Währungen wurden benutzt, sind aber nicht ordnungsgemäß in der Datenbank eingetragen:',

  'There are several options you can handle this problem, please select one:' => 'Bitte wählen Sie eine der folgenden Optionen, um mit dem Problem umzugehen:',

  'There are undefined currencies in your system.' => 'In Ihrer Datenbank wurden Währungen benutzt, die nicht ordnungsgemäß in den Währungen eingetragen wurden.',

  'You have never worked with currencies.' => 'Sie haben noch nie  mit Währungen gearbeitet.',

# @tag: currencies

# @description: Erstellt neue Tabelle currencies. Währungen können dann einfacher eingegeben und unkritisch geändert werden.

# @depends: release_3_0_0 rm_whitespaces

# @charset: utf-8

use utf8;

use strict;

die("This script cannot be run from the command line.") unless ($main::form);

sub mydberror {

  my ($msg) = @_;

  die($dbup_locale->text("Database update error:") . "<br>$msg<br>" . $DBI::errstr);

}

sub do_query {

  my ($query, $may_fail) = @_;

  if (!$dbh->do($query)) {

    mydberror($query) unless ($may_fail);

    $dbh->rollback();

    $dbh->begin_work();

  }

}

sub do_update {

  #Check wheather default currency exists

  my $query = qq|SELECT curr FROM defaults|;

  my ($currencies) = $dbh->selectrow_array($query);

  if (length($currencies) == 0 and length($main::form->{defaultcurrency}) == 0){

    print_no_default_currency();

    return 2;

  } else {

    if (length($main::form->{defaultcurrency}) == 0){

      $main::form->{defaultcurrency} = (split m/:/, $currencies)[0];

    }

  }

  my @currency_array = grep {$_ ne '' } split m/:/, $currencies;

  $query = qq|SELECT DISTINCT curr FROM ar

              UNION

              SELECT DISTINCT curr FROM ap

              UNION

              SELECT DISTINCT curr FROM oe

              UNION

              SELECT DISTINCT curr FROM customer

              UNION

              SELECT DISTINCT curr FROM delivery_orders

              UNION

              SELECT DISTINCT curr FROM exchangerate

              UNION

              SELECT DISTINCT curr FROM vendor|;

  my $sth = $dbh->prepare($query);

  $sth->execute || $self->dberror($query);

  $main::form->{ORPHANED_CURRENCIES} = [];

  my $is_orphaned;

  my $rowcount = 0;

  while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {

    next unless length($ref->{curr}) > 0;

    $is_orphaned = 1;

    foreach my $key (split(/:/, $currencies)) {

      if ($ref->{curr} eq $key) {

        $is_orphaned = 0;

        last;

      }

    }

    if ($is_orphaned) {

     push @{ $main::form->{ORPHANED_CURRENCIES} }, $ref;

     $main::form->{ORPHANED_CURRENCIES}[$rowcount]->{name} = "curr_$rowcount";

     $rowcount++;

    }

  }

  $sth->finish;

  if (scalar @{ $main::form->{ORPHANED_CURRENCIES} } > 0 and not ($main::form->{continue_options})) {

    print_orphaned_currencies();

    return 2;

  }

  if ($main::form->{continue_options} eq 'break_up') {

    return 0;

  }

  if ($main::form->{continue_options} eq 'insert') {

    for my $i (0..($rowcount-1)){

      push @currency_array, $form->{"curr_$i"};

    }

    create_and_fill_table(@currency_array);

    return 1;

  }

  my $still_orphaned;

  if ($main::form->{continue_options} eq 'replace') {

    for my $i (0..($rowcount - 1)){

      $still_orphaned = 1;

      for my $item (@currency_array){

        if ($main::form->{"curr_$i"} eq $item){

          $still_orphaned = 0;

          $query = qq|DELETE FROM exchangerate WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;

          do_query($query);

          $query = qq|UPDATE ap SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;

          do_query($query);

          $query = qq|UPDATE ar SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;

          do_query($query);

          $query = qq|UPDATE oe SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;

          do_query($query);

          $query = qq|UPDATE customer SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;

          do_query($query);

          $query = qq|UPDATE delivery_orders SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;

          do_query($query);

          $query = qq|UPDATE vendor SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;

          do_query($query);

          last;

        }

      }

      if ($still_orphaned){

        $main::form->{continue_options} = '';

        return do_update();

      }

    }

    create_and_fill_table(@currency_array);

    return 1;

  }

  #No orphaned currencies, so create table:

  create_and_fill_table(@currency_array);

  return 1;

}; # end do_update

sub create_and_fill_table {

  #Create an fill table currencies:

  my $query = qq|CREATE TABLE currencies (id INTEGER DEFAULT nextval(('id'::text)::regclass) UNIQUE NOT NULL, curr TEXT PRIMARY KEY)|;

  do_query($query);

  foreach my $item ( @_ ) {

    $query = qq|INSERT INTO currencies (curr) VALUES ('| . $item . qq|')|;

    do_query($query);

  }

  #Set default currency if no currency was chosen:

  $query = qq|UPDATE ap SET curr = '| . $main::form->{"defaultcurrency"} . qq|' WHERE curr IS NULL or curr='';|;

  $query .= qq|UPDATE ar SET curr = '| . $main::form->{"defaultcurrency"} . qq|' WHERE curr IS NULL or curr='';|;

  $query .= qq|UPDATE oe SET curr = '| . $main::form->{"defaultcurrency"} . qq|' WHERE curr IS NULL or curr='';|;

  $query .= qq|UPDATE customer SET curr = '| . $main::form->{"defaultcurrency"} . qq|' WHERE curr IS NULL or curr='';|;

  $query .= qq|UPDATE delivery_orders SET curr = '| . $main::form->{"defaultcurrency"} . qq|' WHERE curr IS NULL or curr='';|;

  $query .= qq|UPDATE vendor SET curr = '| . $main::form->{"defaultcurrency"} . qq|' WHERE curr IS NULL or curr='';|;

  $query .= qq|DELETE FROM exchangerate WHERE curr IS NULL or curr='';|;

  do_query($query);

  #Check wheather defaultcurrency is already in table currencies:

  $query = qq|SELECT curr FROM currencies WHERE curr = '| . $main::form->{defaultcurrency} . qq|'|;

  my ($insert_default) = $dbh->selectrow_array($query);

  if (!$insert_default) {

    $query = qq|INSERT INTO currencies (curr) VALUES ('| . $main::form->{defaultcurrency} . qq|')|;

    do_query($query);

  }

  #Create a new columns currency and update with curr.id:

  $query = qq|ALTER TABLE ap ADD currency INTEGER;

              ALTER TABLE ar ADD currency INTEGER;

              ALTER TABLE oe ADD currency INTEGER;

              ALTER TABLE customer ADD currency INTEGER;

              ALTER TABLE delivery_orders ADD currency INTEGER;

              ALTER TABLE exchangerate ADD currency INTEGER;

              ALTER TABLE vendor ADD currency INTEGER;

              ALTER TABLE defaults ADD currency INTEGER;|;

  do_query($query);

  #Set defaultcurrency:

  $query = qq|UPDATE defaults SET currency= (SELECT id FROM currencies WHERE curr = '| . $main::form->{defaultcurrency} . qq|')|;

  do_query($query);

  $query = qq|UPDATE ap SET currency = (SELECT id FROM currencies c WHERE c.curr = ap.curr);

              UPDATE ar SET currency = (SELECT id FROM currencies c WHERE c.curr = ar.curr);

              UPDATE oe SET currency = (SELECT id FROM currencies c WHERE c.curr = oe.curr);

              UPDATE customer SET currency = (SELECT id FROM currencies c WHERE c.curr = customer.curr);

              UPDATE delivery_orders SET currency = (SELECT id FROM currencies c WHERE c.curr = delivery_orders.curr);

              UPDATE exchangerate SET currency = (SELECT id FROM currencies c WHERE c.curr = exchangerate.curr);

              UPDATE vendor SET currency = (SELECT id FROM currencies c WHERE c.curr = vendor.curr);|;

  do_query($query);

  #Drop column 'curr':

  $query = qq|ALTER TABLE ap DROP COLUMN curr;

              ALTER TABLE ar DROP COLUMN curr;

              ALTER TABLE oe DROP COLUMN curr;

              ALTER TABLE customer DROP COLUMN curr;

              ALTER TABLE delivery_orders DROP COLUMN curr;

              ALTER TABLE exchangerate DROP COLUMN curr;

              ALTER TABLE vendor DROP COLUMN curr;

              ALTER TABLE defaults DROP COLUMN curr;|;

  do_query($query);

  #Rename currency to curr:

  $query = qq|ALTER TABLE defaults RENAME COLUMN currency TO curr;

              ALTER TABLE ap RENAME COLUMN currency TO curr;

              ALTER TABLE ar RENAME COLUMN currency TO curr;

              ALTER TABLE oe RENAME COLUMN currency TO curr;

              ALTER TABLE customer RENAME COLUMN currency TO curr;

              ALTER TABLE delivery_orders RENAME COLUMN currency TO curr;

              ALTER TABLE exchangerate RENAME COLUMN currency TO curr;

              ALTER TABLE vendor RENAME COLUMN currency TO curr;|;

  do_query($query);

  #Set NOT NULL constraints:

  $query = qq|ALTER TABLE ap ALTER COLUMN curr SET NOT NULL;

              ALTER TABLE ar ALTER COLUMN curr SET NOT NULL;

              ALTER TABLE oe ALTER COLUMN curr SET NOT NULL;

              ALTER TABLE customer ALTER COLUMN curr SET NOT NULL;

              ALTER TABLE delivery_orders ALTER COLUMN curr SET NOT NULL;

              ALTER TABLE exchangerate ALTER COLUMN curr SET NOT NULL;

              ALTER TABLE vendor ALTER COLUMN curr SET NOT NULL;

              ALTER TABLE defaults ALTER COLUMN curr SET NOT NULL;|;

  do_query($query);

  #Set foreign keys:

  $query = qq|ALTER TABLE ap ADD FOREIGN KEY (curr) REFERENCES currencies(id);

              ALTER TABLE ar ADD FOREIGN KEY (curr) REFERENCES currencies(id);

              ALTER TABLE oe ADD FOREIGN KEY (curr) REFERENCES currencies(id);

              ALTER TABLE customer ADD FOREIGN KEY (curr) REFERENCES currencies(id);

              ALTER TABLE delivery_orders ADD FOREIGN KEY (curr) REFERENCES currencies(id);

              ALTER TABLE exchangerate ADD FOREIGN KEY (curr) REFERENCES currencies(id);

              ALTER TABLE vendor ADD FOREIGN KEY (curr) REFERENCES currencies(id);

              ALTER TABLE defaults ADD FOREIGN KEY (curr) REFERENCES currencies(id);|;

  do_query($query);

};

sub print_no_default_currency {

  print $main::form->parse_html_template("dbupgrade/no_default_currency");

};

sub print_orphaned_currencies {

  print $main::form->parse_html_template("dbupgrade/orphaned_currencies");

};

return do_update();

# @tag: rm_whitespaces

# @description: Entfernt mögliche Leerzeichen am Anfang und Ende jeder Währung

# @depends: release_3_0_0

# @charset: utf-8

use utf8;

use strict;

die("This script cannot be run from the command line.") unless ($main::form);

sub mydberror {

  my ($msg) = @_;

  die($dbup_locale->text("Database update error:") . "<br>$msg<br>" . $DBI::errstr);

}

sub do_query {

  my ($query, $may_fail) = @_;

  if (!$dbh->do($query)) {

    mydberror($query) unless ($may_fail);

    $dbh->rollback();

    $dbh->begin_work();

  }

}

sub do_update {

  my $query = qq|UPDATE ar SET curr = RTRIM(LTRIM(curr))|;

  do_query($query);

  $query = qq|UPDATE ap SET curr = RTRIM(LTRIM(curr))|;

  do_query($query);

  $query = qq|UPDATE oe SET curr = RTRIM(LTRIM(curr))|;

  do_query($query);

  $query = qq|UPDATE customer SET curr = RTRIM(LTRIM(curr))|;

  do_query($query);

  $query = qq|UPDATE delivery_orders SET curr = RTRIM(LTRIM(curr))|;

  do_query($query);

  $query = qq|UPDATE exchangerate SET curr = RTRIM(LTRIM(curr))|;

  do_query($query);

  $query = qq|UPDATE vendor SET curr = RTRIM(LTRIM(curr))|;

  do_query($query);

  $query = qq|SELECT curr FROM defaults|;

  my ($curr)     = selectrow_query($self, $dbh, $query);

  $curr  =~ s/ //g;

  $query = qq|UPDATE defaults SET curr = '$curr'|;

  do_query($query);

  return 1;

};

return do_update();

     <th align="right">[% 'Default currency' | $T8 %]</th>

     <td colspan="3">[% HTML.escape(defaultcurrency) %]</td>

    </tr>

    <tr>

     <input type="hidden" name="rowcount" value="[% CURRENCIES.size %]">

     <th align="right">[% 'Currencies' | $T8 %]</th>

     <td colspan="3">[% FOREACH row = CURRENCIES %]<input name="curr_[% loop.count %]" size="3" value="[% HTML.escape(row.curr) %]">

                                                   <input type=hidden name="old_curr_[% loop.count %]" value="[% HTML.escape(defaults_curr) %]">

                     [% END %]

                     <input name="new_curr" size="3" value="">

     </td>

[%- USE T8 %]

[% USE HTML %]<div class="listtop">[% 'No default currency' | $T8 %]</div>

<form name="Form" method="post" action="login.pl">

<input type="hidden" name="action" value="login">

<p>[% 'You have never worked with currencies.' | $T8 %]</p>

<p>[% 'That is why kivitendo could not find a default currency.' | $T8 %]</p>

<p>[% 'From this version on it is necessary to name a default value.' | $T8 %]</p>

<p>[% 'Please enter the currency you are working with.' | $T8 %]</p>

<input name="defaultcurrency" value="EUR">

<input type="submit" value="[% 'Continue' | $T8 %]">

</form>

[%- USE T8 %]

[%- USE L %]

[% USE HTML %]<div class="listtop">[% 'Orphaned currencies' | $T8 %]</div>

<form name="Form" method="post" action="login.pl">

<input type="hidden" name="action" value="login">

<input type="hidden" name="defaultcurrency" value="[% HTML.escape(defaultcurrency) %]">

<p>[% 'There are undefined currencies in your system.' | $T8 %]</p>

<p>[% 'The following currencies have been used, but they are not defined:' | $T8 %]</p>

[% FOREACH row = ORPHANED_CURRENCIES %]

<input name="[% row.name %]" value="[% HTML.escape(row.curr) %]"><br>

<input type="hidden" name="old_[% row.name %]" value="[% HTML.escape(row.curr) %]">

[% END %]

<p>[% 'There are several options you can handle this problem, please select one:' | $T8 %]</p>

<table width="100%">

<tr>

<td>

[% L.radio_button_tag('continue_options',

    value => 'insert',

    disabled => 0,

    checked  => 0) %]

&nbsp;[% '(recommended) Insert the used currencies in the system. You can simply change the name of the currencies by editing the textfields above. Do not use a name of a currency that is already in use.' | $T8 %] <br>

[% L.radio_button_tag('continue_options',

    value => 'replace',

    disabled => 0,

    checked  => 0) %]

&nbsp;[% 'Replace the orphaned currencies by other not orphaned currencies. To do so, please delete the currency in the textfields above and replace it by another currency. You could loose or change unintentionally exchangerates. Go on very carefully since you could destroy transactions.' | $T8 %] <br>

[% L.radio_button_tag('continue_options',

    value => 'break_up',

    disabled => 0,

    checked  => 0) %]

 [% 'Break up the update and contact a service provider.' | $T8 %]

</tr>

</td>

</table>

<input type="submit" value="[% 'Continue' | $T8 %]">

</form>