projekt kivitendo

  '. Automatically generated.'  => '. Automatisch erzeugt.',

  '0% tax with taxkey'          => '0% Steuer mit Steuerschlüssel ',

  'Inconsistency in database'   => 'Unstimmigkeiten in der Datenbank',

  'Please define a taxkey for the following taxes and run the update again:' => 'Bitte definieren Sie einen Steuerschlüssel für die folgenden Steuern und starten Sie dann das Update erneut:',

  'Please set another taxnumber for the following taxes and run the update again:' => 'Bitte wählen Sie ein anderes Steuerautomatik-Konto für die folgenden Steuern aus uns starten Sie dann das Update erneut.',

  'There are entries in tax where taxkey is NULL.' => 'In der Datenbank sind Steuern ohne Steuerschlüssel vorhanden (in der Tabelle tax Spalte taxkey).',

  'There are invalid taxnumbers in use.' => 'Es werden ungültige Steuerautomatik-Konten benutzt.',

  'There is an inconsistancy in your database.' => 'In Ihrer Datenbank sind Unstimmigkeiten vorhanden.',

  '. Automatically generated.'  => '',

  '0% tax with taxkey'          => '',

  'There are entries in tax where taxkey is NULL.' => '',

  'There are invalid taxnumbers in use.' => '',

  'There are no entries in the background job history.' => '',

  'There is an inconsistancy in your database.' => '',

# @tag: tax_constraints

# @description: Setzt Fremdschlüssel und andere constraints auf die Tabellen tax und taxkeys

# @depends: release_3_0_0

# @charset: utf-8

use utf8;

use strict;

use SL::Locale;

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 CONSISTANCY OF tax

  #update tax.rate and tax.taxdescription in order to set later NOT NULL constraints

my $query= <<SQL;

UPDATE tax SET rate=0 WHERE rate IS NULL;

UPDATE tax SET taxdescription='-' WHERE taxdescription IS NULL;

SQL

  do_query($query);

  #check automatic tax accounts

  $query= <<SQL;

SELECT count(*) FROM tax WHERE chart_id NOT IN (SELECT id FROM chart);

SQL

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

  if ($invalid_tax_account > 0){

    #list all invalid tax accounts

    $query = <<SQL;

SELECT id,

  taxkey,

  taxdescription, 

  round(rate * 100, 2) AS rate 

FROM tax WHERE chart_id NOT IN (SELECT id FROM chart);

SQL

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

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

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

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

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

    }

    $sth->finish;

    $main::form->{invalid_tax_account} = 1;

    print_error_message();

    return 0;

  } 

  #check entry tax.taxkey of NOT NULL

  $query= <<SQL;

SELECT count(*) FROM tax WHERE taxkey IS NULL;

SQL

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

  if ($taxkey_is_null > 0){

    #list all invalid tax accounts

    $query = <<SQL;

SELECT id, 

  taxdescription, 

  round(rate * 100, 2) AS rate, 

  (SELECT accno FROM chart WHERE id = chart_id) AS taxnumber, 

  (SELECT description FROM chart WHERE id = chart_id) AS account_description

FROM tax 

WHERE taxkey IS NULL;

SQL

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

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

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

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

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

    }

    $sth->finish;

    $main::form->{taxkey_is_null} = 1;

    print_error_message();

    return 0;

  } 

#END CHECK OF tax

#CHECK CONSISTANCY OF taxkeys

  #delete invalide entries in taxkeys

  $query= <<SQL;

DELETE FROM taxkeys 

WHERE chart_id IS NULL 

OR chart_id NOT IN (SELECT id FROM chart) 

OR startdate IS NULL;

SQL

  do_query($query);

#There are 3 cases for taxkeys.tax_id and taxkeys.taxkey_id

#taxkeys.taxkey_id is NULL and taxkeys.tax_id is not NULL:

  #Update taxkeys.taxkey_id with tax.taxkey

  $query= <<SQL;

UPDATE taxkeys 

SET taxkey_id = (SELECT t.taxkey 

                        FROM tax t 

                        WHERE t.id=tax_id)

WHERE taxkey_id IS NULL 

AND tax_id IS NOT NULL;

SQL

  do_query($query);

#taxkeys.taxkey_id and taxkeys.tax_id are NULL:

  #Set taxkey 0 in this case:

  $query= <<SQL;

UPDATE taxkeys

SET taxkey_id = 0, tax_id = (SELECT id FROM tax WHERE taxkey=0)

WHERE taxkey_id IS NULL

AND tax_id IS NULL;

SQL

  do_query($query);

#Last case where taxkeys.taxkey_id is not null and taxkeys.tax_id is null

  #If such entries exist we update with an entry in tax where tax.rate=0

  #and tax.taxkey corresponds to taxkeys.taxkey_id.

  #If no entry in tax with rate 0 and taxkey taxkeys.taxkey_id exists 

  #we create one.

  $query= <<SQL;

SELECT DISTINCT taxkey_id

FROM taxkeys 

WHERE taxkey_id IS NOT NULL 

AND tax_id IS NULL;

SQL

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

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

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

  my $rowcount = 0;

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

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

    $rowcount++;

  }

  $sth->finish;

  my $insertquery;

  my $updatequery;

  my $tax_id;

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

    $query= qq|

SELECT id FROM tax WHERE rate = 0 and taxkey=| . $main::form->{TAXID}[$i]->{taxkey_id} . qq| LIMIT 1

|;  

    ($tax_id) = $dbh->selectrow_array($query); 

    if ( not $tax_id ){

      $insertquery=qq|

INSERT INTO tax (rate, taxdescription, taxkey) VALUES (0, '| . $::locale->text('0% tax with taxkey') . $main::form->{TAXID}[$i]->{taxkey_id} .  $::locale->text('. Automatically generated.') . 

qq|', | . $main::form->{TAXID}[$i]->{taxkey_id} . qq|);

|;

      do_query($insertquery);

      ($tax_id) = $dbh->selectrow_array($query);

      $tax_id || $main::form->dberror($query); 

    }

    $updatequery = qq|

UPDATE taxkeys SET tax_id= | . $tax_id . qq| WHERE taxkey_id = | . $main::form->{TAXID}[$i]->{taxkey_id} . qq| AND tax_id IS NULL

|;

    do_query($updatequery);

  }

#The triple taxkey_id, chart_id, startdate in taxkeys has to be unique

  #Select these entries:

  $query= <<SQL;

SELECT DISTINCT tk1.chart_id AS chart_id, tk1.startdate AS startdate

FROM taxkeys tk1 

WHERE (SELECT count(*) 

       FROM taxkeys tk2 

       WHERE tk2.chart_id  = tk1.chart_id 

       AND   tk2.startdate = tk1.startdate) > 1;

SQL

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

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

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

  $rowcount = 0;

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

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

    $rowcount++;

  }

  $sth->finish;

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

    $query= qq| 

DELETE FROM taxkeys tk1 

WHERE (SELECT count(*) 

       FROM taxkeys tk2 

       WHERE tk2.chart_id  = tk1.chart_id 

       AND   tk2.startdate = tk1.startdate) > 1 

AND NOT tk1.id = (SELECT id 

                  FROM taxkeys 

                  WHERE chart_id  = | . $main::form->{TAXKEYS}[$i]->{chart_id} . qq|

                  AND   startdate = '| . $main::form->{TAXKEYS}[$i]->{startdate} . qq|'

                  LIMIT 1)

|;

    do_query($query);

  }

#END CHECK OF taxkeys

#ADD CONSTRAINTS:

#Now the database is consistent, so we can add constraints:

  #Crate NOT NULL constraint for tax.rate with default value 0

  $query= <<SQL;

ALTER TABLE tax ALTER COLUMN rate SET NOT NULL;

ALTER TABLE tax ALTER COLUMN rate SET DEFAULT 0;

SQL

  do_query($query);

  #Create NOT NULL constraint for tax.description

  $query= <<SQL;

ALTER TABLE tax ALTER COLUMN taxdescription SET NOT NULL;

SQL

  do_query($query);

  #Create foreign key for tax.chart_id to chart.id

  $query= <<SQL;

ALTER TABLE tax ADD FOREIGN KEY (chart_id) REFERENCES chart(id);

SQL

  do_query($query);

  #Create NOT NULL constraint for tax.taxkey

  $query= <<SQL;

ALTER TABLE tax ALTER COLUMN taxkey SET NOT NULL;

SQL

  do_query($query);

  #Create NOT NULL constraint for taxkey.chart_id and foreign key for taxkey.chart_id

  $query= <<SQL;

ALTER TABLE taxkeys ALTER COLUMN chart_id SET NOT NULL;

ALTER TABLE taxkeys ADD FOREIGN KEY (chart_id) REFERENCES chart(id);

SQL

  do_query($query);

  #Create NOT NULL constraint for taxkey.startdate

  $query= <<SQL;

ALTER TABLE taxkeys ALTER COLUMN startdate SET NOT NULL;

SQL

  do_query($query);

  #Create NOT NULL constraint for taxkey.taxkey_id

  $query= <<SQL;

ALTER TABLE taxkeys ALTER COLUMN taxkey_id SET NOT NULL;

SQL

  do_query($query);

  #Create NOT NULL constraint for taxkey.tax_id

  $query= <<SQL;

ALTER TABLE taxkeys ALTER COLUMN tax_id SET NOT NULL;

SQL

  do_query($query);

  #The triple chart_id, taxkey_id, startdate should be unique:

  $query= <<SQL;

CREATE UNIQUE INDEX taxkeys_chartid_startdate ON taxkeys(chart_id, startdate);

SQL

  do_query($query);

#ALL CONSTRAINTS WERE ADDED

  return 1;

}; # end do_update

sub print_error_message {

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

}

return do_update();

[%- USE T8 %]

[% USE HTML %]<div class="listtop">[% 'Inconsistency in database' | $T8 %]</div>

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

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

<p>[% 'There is an inconsistancy in your database.' | $T8 %]</p>

<p>[% 'Please contact your administrator.' | $T8 %]</p>

[% IF invalid_tax_account %]

  <p>[% 'There are invalid taxnumbers in use.' | $T8 %]</p>

  <p>[% 'Please set another taxnumber for the following taxes and run the update again:' | $T8 %]</p>

  <table>

    <tr>

      <th class="listheading">[% 'tax_taxkey' | $T8 %]</th>

      <th class="listheading">[% 'tax_taxdescription' | $T8 %]</th>

      <th class="listheading">[% 'tax_rate' | $T8 %]</th>

    </tr>

    [% SET row_odd = '1' %][% FOREACH row = TAX %]

    <tr class="listrow[% IF row_odd %]1[% SET row_odd = '0' %][% ELSE %]0[% SET row_odd = '1' %][% END %]">

      <td align="right">[% HTML.escape(row.taxkey) %]</td>

      <td align="left"> [% HTML.escape(row.taxdescription) %]</a></td>

      <td align="right">[% HTML.escape(row.rate) %] %</td>

    </tr>

    [% END %]

  </table>

[% END %]

[% IF taxkey_is_null %]

  <p>[% 'There are entries in tax where taxkey is NULL.' | $T8 %]</p>

  <p>[% 'Please define a taxkey for the following taxes and run the update again:' | $T8 %]</p>

  <table>

    <tr>

      <th class="listheading">[% 'tax_taxdescription' | $T8 %]</th>

      <th class="listheading">[% 'tax_rate' | $T8 %]</th>

      <th class="listheading">[% 'taxnumber' | $T8 %]</th>

      <th class="listheading">[% 'account_description' | $T8 %]</th>

    </tr>

    [% SET row_odd = '1' %][% FOREACH row = TAX %]

    <tr class="listrow[% IF row_odd %]1[% SET row_odd = '0' %][% ELSE %]0[% SET row_odd = '1' %][% END %]">

      <td align="left"> [% HTML.escape(row.taxdescription) %]</a></td>

      <td align="right">[% HTML.escape(row.rate) %] %</td>

      <td align="right">[% HTML.escape(row.taxnumber) %]</td>

      <td>[% HTML.escape(row.account_description) %]</td>

    </tr>

    [% END %]

  </table>

[% END %]

</form>