projekt kivitendo

  # TODO: extract information about income/expense accounts from new table taxzone_chart

                 (SELECT accno FROM chart WHERE id = inventory_accno_id) AS inventory_accno

    # TODO: extract information about income/expense accounts from new table taxzone_chart

         (SELECT accno FROM chart WHERE id = inventory_accno_id) AS inventory_accno

  my $taxzone_chart = SL::DB::Manager::TaxzoneChart->find_by(taxzone_id => $taxzone_id, buchungsgruppen_id => $self->id);

  return $taxzone_chart->income_accno_id if $taxzone_chart;

  my $taxzone_chart = SL::DB::Manager::TaxzoneChart->find_by(taxzone_id => $taxzone_id, buchungsgruppen_id => $self->id);

  return $taxzone_chart->expense_accno_id if $taxzone_chart;

  my $taxzone_chart = SL::DB::Manager::TaxzoneChart->find_by(taxzone_id => $taxzone_id, buchungsgruppen_id => $self->id);

  return $taxzone_chart->income_accno if $taxzone_chart;

  my $taxzone_chart = SL::DB::Manager::TaxzoneChart->find_by(taxzone_id => $taxzone_id, buchungsgruppen_id => $self->id);

  return $taxzone_chart->expense_accno if $taxzone_chart;

use SL::DB::TaxzoneChart;

  taxzone_charts                 => 'taxzone_chart',

# This file has been auto-generated. Do not modify it; it will be overwritten

# by rose_auto_create_model.pl automatically.

package SL::DB::TaxzoneChart;

use strict;

use base qw(SL::DB::Object);

__PACKAGE__->meta->table('taxzone_charts');

__PACKAGE__->meta->columns(

  id                 => { type => 'serial', not_null => 1 },

  taxzone_id         => { type => 'integer', not_null => 1 },

  buchungsgruppen_id => { type => 'integer', not_null => 1 },

  income_accno_id    => { type => 'integer', not_null => 1 },

  expense_accno_id   => { type => 'integer', not_null => 1 },

  itime              => { type => 'timestamp', default => 'now()' },

);

__PACKAGE__->meta->primary_key_columns([ 'id' ]);

__PACKAGE__->meta->allow_inline_column_values(1);

__PACKAGE__->meta->foreign_keys(

  buchungsgruppen => {

    class       => 'SL::DB::Buchungsgruppe',

    key_columns => { buchungsgruppen_id => 'id' },

  },

  expense_accno => {

    class       => 'SL::DB::Chart',

    key_columns => { expense_accno_id => 'id' },

  },

  income_accno => {

    class       => 'SL::DB::Chart',

    key_columns => { income_accno_id => 'id' },

  },

);

# __PACKAGE__->meta->initialize;

1;

;

                 :                          $bugru->call_sub("${type}_accno_id", $taxzone);

# This file has been auto-generated only because it didn't exist.

# Feel free to modify it at will; it will not be overwritten automatically.

package SL::DB::TaxzoneChart;

use strict;

use SL::DB::MetaSetup::TaxzoneChart;

__PACKAGE__->meta->initialize;

# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all.

__PACKAGE__->meta->make_manager_class;

1;

      qq|(SELECT tc.expense_accno_id

          FROM taxzone_charts tc

          WHERE tc.buchungsgruppen_id = | . conv_i($form->{"buchungsgruppen_id"}, 'NULL') . qq| and tc.taxzone_id = 0)|;

         income_accno_id = (SELECT tc.income_accno_id FROM taxzone_charts tc WHERE tc.taxzone_id = 0 and tc.buchungsgruppen_id = ?),

      tc.income_accno_id AS income_accno_id,

      tc.expense_accno_id AS expense_accno_id,

    LEFT JOIN taxzone_charts tc on bg.id = tc.buchungsgruppen_id

    LEFT JOIN chart c2 ON tc.income_accno_id = c2.id

    LEFT JOIN chart c3 ON tc.expense_accno_id = c3.id

    WHERE 

    tc.taxzone_id = '$form->{taxzone_id}' 

    and 

    p.id IN ($in)

             bg.inventory_accno_id, tc.expense_accno_id AS expense_accno_id, a.transdate

           FROM invoice i, ar a, parts p, buchungsgruppen bg, taxzone_charts tc

             AND (tc.buchungsgruppen_id = p.buchungsgruppen_id)

             AND (tc.taxzone_id = ${taxzone})

        LEFT JOIN chart c2 ON ((SELECT tc.income_accno_id FROM taxzone_charts tc where tc.taxzone_id = '$taxzone_id' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c2.id)

        LEFT JOIN chart c3 ON ((SELECT tc.expense_accno_id FROM taxzone_charts tc where tc.taxzone_id = '$taxzone_id' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c3.id)

         ((SELECT tc.income_accno_id

           FROM taxzone_charts tc 

           WHERE tc.taxzone_id = '$taxzone_id' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c2.id)

         ((SELECT tc.expense_accno_id

           FROM taxzone_charts tc

           WHERE tc.taxzone_id = '$taxzone_id' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c3.id)

       LEFT JOIN chart c2 ON ((SELECT tc.income_accno_id FROM taxzone_charts tc WHERE tc.taxzone_id = '$taxzone_id' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c2.id)

       LEFT JOIN chart c3 ON ((SELECT tc.expense_accno_id FROM taxzone_charts tc WHERE tc.taxzone_id = '$taxzone_id' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c3.id)

         LEFT JOIN chart c2 ON ((SELECT tc.income_accno_id FROM taxzone_charts tc WHERE tc.taxzone_id = '$taxzone_id' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c2.id)

         LEFT JOIN chart c3 ON ((SELECT tc.expense_accno_id FROM taxzone_charts tc WHERE tc.taxzone_id = '$taxzone_id' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c3.id)

         ((SELECT tc.income_accno_id

           FROM taxzone_charts tc

           WHERE tc.buchungsgruppen_id = p.buchungsgruppen_id and tc.taxzone_id = ${taxzone_id}) = c2.id)

         ((SELECT tc.expense_accno_id

           FROM taxzone_charts tc

           WHERE tc.buchungsgruppen_id = p.buchungsgruppen_id and tc.taxzone_id = ${taxzone_id}) = c3.id)

         LEFT JOIN chart c2 ON ((SELECT tc.income_accno_id FROM taxzone_charts tc WHERE tc.taxzone_id = '$form->{taxzone_id}' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c2.id)

         LEFT JOIN chart c3 ON ((SELECT tc.expense_accno_id FROM taxzone_charts tc WHERE tc.taxzone_id = '$form->{taxzone_id}' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c3.id)

# @tag: convert_taxzone

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

# @depends: taxzone_charts

package SL::DBUpgrade2::convert_taxzone;

use strict;

use utf8;

use parent qw(SL::DBUpgrade2::Base);

sub run {

  my ($self) = @_;

    # extract all buchungsgruppen data

    my $buchungsgruppen_query = <<SQL;

      SELECT * from buchungsgruppen;

SQL

    my $sth = $self->dbh->prepare($buchungsgruppen_query);

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

    $::form->{buchungsgruppen} = [];

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

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

    }

    $sth->finish;

    # extract all tax_zone data

    my $taxzone_query = <<SQL;

      SELECT * from tax_zones;

SQL

    $sth = $self->dbh->prepare($taxzone_query);

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

    $::form->{taxzones} = [];

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

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

    }

    $sth->finish;

    my $taxzone_charts_update_query;

    foreach my $taxzone (  @{$::form->{taxzones}} ) {

        foreach my $buchungsgruppe (  @{$::form->{buchungsgruppen}} ) {

            my $id = $taxzone->{id};

            my $income_accno_id = $buchungsgruppe->{"income_accno_id_$id"};

            my $expense_accno_id = $buchungsgruppe->{"expense_accno_id_$id"};

            # TODO: check if the variables have a value

            $taxzone_charts_update_query .= "INSERT INTO taxzone_charts (taxzone_id, buchungsgruppen_id, income_accno_id, expense_accno_id) VALUES ('$taxzone->{id}', '$buchungsgruppe->{id}', $income_accno_id, $expense_accno_id);\n";

        };

    };

    $self->db_query($taxzone_charts_update_query);

    my $clean_buchungsgruppen_query = <<SQL;

alter table buchungsgruppen drop column income_accno_id_0;

alter table buchungsgruppen drop column income_accno_id_1;

alter table buchungsgruppen drop column income_accno_id_2;

alter table buchungsgruppen drop column income_accno_id_3;

alter table buchungsgruppen drop column expense_accno_id_0;

alter table buchungsgruppen drop column expense_accno_id_1;

alter table buchungsgruppen drop column expense_accno_id_2;

alter table buchungsgruppen drop column expense_accno_id_3;

SQL

  $sth = $self->dbh->prepare($clean_buchungsgruppen_query);

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

  return 1;

} # end run

1;

-- @tag: taxzone_charts

-- @description: Neue Tabelle für Buchungskonten der Steuerzonen 

-- @depends: release_3_0_0

-- @ignore: 0

CREATE TABLE taxzone_charts (

  id SERIAL PRIMARY KEY,

  taxzone_id integer     NOT NULL, 

  buchungsgruppen_id integer     NOT NULL, 

  income_accno_id integer     NOT NULL, 

  expense_accno_id integer     NOT NULL, 

  itime timestamp DEFAULT now(),

  FOREIGN KEY (taxzone_id)         REFERENCES tax_zones       (id),

  FOREIGN KEY (income_accno_id)    REFERENCES chart           (id),

  FOREIGN KEY (expense_accno_id)   REFERENCES chart           (id),

  FOREIGN KEY (buchungsgruppen_id) REFERENCES buchungsgruppen (id)

);