projekt kivitendo

# 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::BankTransactionAccTrans;

use strict;

use SL::DB::MetaSetup::BankTransactionAccTrans;

use SL::DB::Manager::BankTransactionAccTrans;

__PACKAGE__->meta->initialize;

1;

  bank_transaction_acc_trans     => 'bank_transaction_acc_trans',

# 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::Manager::BankTransactionAccTrans;

use strict;

use parent qw(SL::DB::Helper::Manager);

sub object_class { 'SL::DB::BankTransactionAccTrans' }

__PACKAGE__->make_manager_methods;

1;

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

# by rose_auto_create_model.pl automatically.

package SL::DB::BankTransactionAccTrans;

use strict;

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

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

__PACKAGE__->meta->columns(

  acc_trans_id        => { type => 'bigint', not_null => 1 },

  ap_id               => { type => 'integer' },

  ar_id               => { type => 'integer' },

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

  gl_id               => { type => 'integer' },

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

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

  mtime               => { type => 'timestamp' },

);

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

__PACKAGE__->meta->allow_inline_column_values(1);

__PACKAGE__->meta->foreign_keys(

  acc_transaction => {

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

    key_columns => { acc_trans_id => 'acc_trans_id' },

  },

  ap => {

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

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

  },

  ar => {

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

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

  },

  bank_transaction => {

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

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

  },

  gl => {

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

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

  },

);

1;

;

# @tag: record_links_bt_acc_trans

# @description: RecordLinks von bt nach acc_trans

# @depends: release_3_5_3

package SL::DBUpgrade2::record_links_bt_acc_trans;

use strict;

use utf8;

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

use SL::DBUtils;

use SL::RecordLinks;

sub run {

  my ($self) = @_;

  my $query_table =

    qq|

      CREATE SEQUENCE bank_transaction_acc_trans_id_seq;

      CREATE TABLE bank_transaction_acc_trans (

        id                      integer NOT NULL DEFAULT nextval('bank_transaction_acc_trans_id_seq'),

        bank_transaction_id     integer NOT NULL,

        acc_trans_id            bigint  NOT NULL,

        ar_id                   integer,

        ap_id                   integer,

        gl_id                   integer,

        itime                   TIMESTAMP      DEFAULT now(),

        mtime                   TIMESTAMP,

        PRIMARY KEY (bank_transaction_id, acc_trans_id),

        FOREIGN KEY (bank_transaction_id)      REFERENCES bank_transactions (id),

        FOREIGN KEY (acc_trans_id)             REFERENCES acc_trans (acc_trans_id),

        FOREIGN KEY (ar_id)                    REFERENCES ar (id),

        FOREIGN KEY (ap_id)                    REFERENCES ap (id),

        FOREIGN KEY (gl_id)                    REFERENCES gl (id));|;

  $self->db_query($query_table);

  my $query = qq|SELECT to_id, itime, from_id, to_table

                 FROM record_links

                 WHERE from_table='bank_transactions'|;

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

  my $sql       = <<SQL;

    SELECT

     acc_trans_id

    FROM acc_trans

    WHERE trans_id = ?

    AND itime = ?

    AND (chart_link='AR' OR chart_link='AP' OR chart_link ilike '%paid%');

SQL

  my $sth_acc_trans_ids = $self->dbh->prepare($sql) or die $self->dbh->errstr;

  my $sql_insert       = <<SQL;

  INSERT INTO bank_transaction_acc_trans (bank_transaction_id, acc_trans_id, ar_id, ap_id, gl_id)

  VALUES ( ?, ?, ?, ?, ?);

SQL

  my $sth_insert = $self->dbh->prepare($sql_insert) or die $self->dbh->errstr;

  # get all current record links from bank to arap

  $sth->execute() or die $self->dbh->errstr;

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

    # get all concurrent acc_trans entries (payment) for this transaction

    $sth_acc_trans_ids->execute($rl_ref->{to_id}, $rl_ref->{itime}) or die $self->dbh->errstr;

    while (my $ac_ref = $sth_acc_trans_ids->fetchrow_hashref("NAME_lc")) {

      my $ar_id = $rl_ref->{to_table} eq 'ar' ? $rl_ref->{to_id} : undef;

      my $ap_id = $rl_ref->{to_table} eq 'ap' ? $rl_ref->{to_id} : undef;

      my $gl_id = $rl_ref->{to_table} eq 'gl' ? $rl_ref->{to_id} : undef;

      $sth_insert->execute($rl_ref->{from_id},$ac_ref->{acc_trans_id},

                           $ar_id, $ap_id, $gl_id) or die $self->dbh->errstr;

    }

  }

  return 1;

}

1;