1 # @tag: record_links_bt_acc_trans
2 # @description: RecordLinks von bt nach acc_trans
3 # @depends: release_3_5_3
4 package SL::DBUpgrade2::record_links_bt_acc_trans;
9 use parent qw(SL::DBUpgrade2::Base);
21 CREATE SEQUENCE bank_transaction_acc_trans_id_seq;
22 CREATE TABLE bank_transaction_acc_trans (
23 id integer NOT NULL DEFAULT nextval('bank_transaction_acc_trans_id_seq'),
24 bank_transaction_id integer NOT NULL,
25 acc_trans_id bigint NOT NULL,
29 itime TIMESTAMP DEFAULT now(),
31 PRIMARY KEY (bank_transaction_id, acc_trans_id),
32 FOREIGN KEY (bank_transaction_id) REFERENCES bank_transactions (id),
33 FOREIGN KEY (acc_trans_id) REFERENCES acc_trans (acc_trans_id),
34 FOREIGN KEY (ar_id) REFERENCES ar (id),
35 FOREIGN KEY (ap_id) REFERENCES ap (id),
36 FOREIGN KEY (gl_id) REFERENCES gl (id));|;
38 $self->db_query($query_table);
41 my $query = qq|SELECT to_id, itime, from_id, to_table
43 WHERE from_table='bank_transactions'|;
45 my $sth = $self->dbh->prepare($query);
53 AND (chart_link='AR' OR chart_link='AP' OR chart_link ilike '%paid%');
56 my $sth_acc_trans_ids = $self->dbh->prepare($sql) or die $self->dbh->errstr;
58 my $sql_insert = <<SQL;
59 INSERT INTO bank_transaction_acc_trans (bank_transaction_id, acc_trans_id, ar_id, ap_id, gl_id)
60 VALUES ( ?, ?, ?, ?, ?);
63 my $sth_insert = $self->dbh->prepare($sql_insert) or die $self->dbh->errstr;
66 # get all current record links from bank to arap
67 $sth->execute() or die $self->dbh->errstr;
69 while (my $rl_ref = $sth->fetchrow_hashref("NAME_lc")) {
71 # get all concurrent acc_trans entries (payment) for this transaction
72 $sth_acc_trans_ids->execute($rl_ref->{to_id}, $rl_ref->{itime}) or die $self->dbh->errstr;
73 while (my $ac_ref = $sth_acc_trans_ids->fetchrow_hashref("NAME_lc")) {
74 my $ar_id = $rl_ref->{to_table} eq 'ar' ? $rl_ref->{to_id} : undef;
75 my $ap_id = $rl_ref->{to_table} eq 'ap' ? $rl_ref->{to_id} : undef;
76 my $gl_id = $rl_ref->{to_table} eq 'gl' ? $rl_ref->{to_id} : undef;
77 $sth_insert->execute($rl_ref->{from_id},$ac_ref->{acc_trans_id},
78 $ar_id, $ap_id, $gl_id) or die $self->dbh->errstr;