X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FGL.pm;h=db9d9aec9e98b31a67ec2bf975b2056f758b4b0c;hb=0421095640741a61180e2df732d3a387b9c05ec9;hp=4d75fa1bba9ff44455a016ef95859d1c0b36fe67;hpb=eb693e82824f13052de0e05d8405929a37c286b8;p=kivitendo-erp.git diff --git a/SL/GL.pm b/SL/GL.pm index 4d75fa1bb..db9d9aec9 100644 --- a/SL/GL.pm +++ b/SL/GL.pm @@ -221,9 +221,9 @@ sub all_transactions { } if ($form->{source}) { - $glwhere .= " AND ac.source ILIKE ?"; - $arwhere .= " AND ac.source ILIKE ?"; - $apwhere .= " AND ac.source ILIKE ?"; + $glwhere .= " AND ac.trans_id IN (SELECT trans_id from acc_trans WHERE source ILIKE ?)"; + $arwhere .= " AND ac.trans_id IN (SELECT trans_id from acc_trans WHERE source ILIKE ?)"; + $apwhere .= " AND ac.trans_id IN (SELECT trans_id from acc_trans WHERE source ILIKE ?)"; push(@glvalues, '%' . $form->{source} . '%'); push(@arvalues, '%' . $form->{source} . '%'); push(@apvalues, '%' . $form->{source} . '%'); @@ -321,7 +321,6 @@ sub all_transactions { 'id' => [ qw(id) ], 'transdate' => [ qw(transdate id) ], 'reference' => [ qw(lower_reference id) ], - 'source' => [ qw(lower_source id) ], 'description' => [ qw(lower_description id) ], 'accno' => [ qw(accno transdate id) ], ); @@ -345,9 +344,9 @@ sub all_transactions { my $query = qq|SELECT - ac.oid AS acoid, g.id, 'gl' AS type, $false AS invoice, g.reference, ac.taxkey, c.link, + ac.acc_trans_id, g.id, 'gl' AS type, $false AS invoice, g.reference, ac.taxkey, c.link, g.description, ac.transdate, ac.source, ac.trans_id, - ac.amount, c.accno, g.notes, t.chart_id, ac.oid + ac.amount, c.accno, g.notes, t.chart_id $project_columns $columns_for_sorting{gl} FROM gl g, acc_trans ac $project_join, chart c @@ -358,9 +357,9 @@ sub all_transactions { UNION - SELECT ac.oid AS acoid, a.id, 'ar' AS type, a.invoice, a.invnumber, ac.taxkey, c.link, + SELECT ac.acc_trans_id, a.id, 'ar' AS type, a.invoice, a.invnumber, ac.taxkey, c.link, ct.name, ac.transdate, ac.source, ac.trans_id, - ac.amount, c.accno, a.notes, t.chart_id, ac.oid + ac.amount, c.accno, a.notes, t.chart_id $project_columns $columns_for_sorting{arap} FROM ar a, acc_trans ac $project_join, customer ct, chart c @@ -372,9 +371,9 @@ sub all_transactions { UNION - SELECT ac.oid AS acoid, a.id, 'ap' AS type, a.invoice, a.invnumber, ac.taxkey, c.link, + SELECT ac.acc_trans_id, a.id, 'ap' AS type, a.invoice, a.invnumber, ac.taxkey, c.link, ct.name, ac.transdate, ac.source, ac.trans_id, - ac.amount, c.accno, a.notes, t.chart_id, ac.oid + ac.amount, c.accno, a.notes, t.chart_id $project_columns $columns_for_sorting{arap} FROM ap a, acc_trans ac $project_join, vendor ct, chart c @@ -384,7 +383,7 @@ sub all_transactions { AND (a.vendor_id = ct.id) AND (a.id = ac.trans_id) - ORDER BY $sortorder, acoid $sortdir|; + ORDER BY $sortorder, acc_trans_id $sortdir|; my @values = (@glvalues, @arvalues, @apvalues); @@ -403,6 +402,9 @@ sub all_transactions { $trans_id = $ref0->{id}; + my $source = $ref0->{source}; + undef($ref0->{source}); + if ($trans_id != $trans_id2) { # first line of a booking if ($trans_id2) { @@ -447,13 +449,12 @@ sub all_transactions { $i = 0; # Debit Tax # AP_tax # VSt $j = 0; # Credit Tax # AR_tax # USt - if ($ref->{chart_id} > 0) { # all tax accounts first line, no line increasing if ($ref->{amount} < 0) { if ($ref->{link} =~ /AR_tax/) { $ref->{credit_tax}{$j} = $ref->{amount}; $ref->{credit_tax_accno}{$j} = $ref->{accno}; - } + } if ($ref->{link} =~ /AP_tax/) { $ref->{debit_tax}{$i} = $ref->{amount} * -1; $ref->{debit_tax_accno}{$i} = $ref->{accno}; @@ -469,19 +470,19 @@ sub all_transactions { } } } else { #all other accounts first line + if ($ref->{amount} < 0) { $ref->{debit}{$k} = $ref->{amount} * -1; $ref->{debit_accno}{$k} = $ref->{accno}; $ref->{debit_taxkey}{$k} = $ref->{taxkey}; $ref->{ac_transdate}{$k} = $ref->{transdate}; - + $ref->{source}{$k} = $source; } else { $ref->{credit}{$l} = $ref->{amount} * 1; $ref->{credit_accno}{$l} = $ref->{accno}; $ref->{credit_taxkey}{$l} = $ref->{taxkey}; $ref->{ac_transdate}{$l} = $ref->{transdate}; - - + $ref->{source}{$l} = $source; } } @@ -533,22 +534,35 @@ sub all_transactions { if ($ref->{debit_accno}{$k} ne "") { $k++; } + if ($ref->{source}{$k} ne "") { + $space = " | "; + } else { + $space = ""; + } $ref->{debit}{$k} = $ref2->{amount} * - 1; $ref->{debit_accno}{$k} = $ref2->{accno}; $ref->{debit_taxkey}{$k} = $ref2->{taxkey}; $ref->{ac_transdate}{$k} = $ref2->{transdate}; + $ref->{source}{$k} = $source . $space . $ref->{source}{$k}; } else { if ($ref->{credit_accno}{$l} ne "") { $l++; } + if ($ref->{source}{$l} ne "") { + $space = " | "; + } else { + $space = ""; + } $ref->{credit}{$l} = $ref2->{amount}; $ref->{credit_accno}{$l} = $ref2->{accno}; $ref->{credit_taxkey}{$l} = $ref2->{taxkey}; $ref->{ac_transdate}{$l} = $ref2->{transdate}; + $ref->{source}{$l} = $ref->{source}{$l} . $space . $source; } } } } + push @{ $form->{GL} }, $ref; $sth->finish; @@ -621,7 +635,7 @@ sub transaction { ORDER BY startdate DESC LIMIT 1)) WHERE (a.trans_id = ?) AND (a.fx_transaction = '0') - ORDER BY a.oid, a.transdate|; + ORDER BY a.acc_trans_id, a.transdate|; $form->{GL} = selectall_hashref_query($form, $dbh, $query, conv_i($form->{id})); } else { @@ -702,4 +716,39 @@ sub storno { $main::lxdebug->leave_sub(); } +sub get_chart_balances { + $main::lxdebug->enter_sub(); + + my $self = shift; + my %params = @_; + + Common::check_params(\%params, qw(charts)); + + my $myconfig = \%main::myconfig; + my $form = $main::form; + + my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); + + my @ids = map { $_->{id} } @{ $params{charts} }; + + if (!@ids) { + $main::lxdebug->leave_sub(); + return; + } + + my $query = qq|SELECT chart_id, SUM(amount) AS sum + FROM acc_trans + WHERE chart_id IN (| . join(', ', ('?') x scalar(@ids)) . qq|) + GROUP BY chart_id|; + + my %balances = selectall_as_map($form, $dbh, $query, 'chart_id', 'sum', @ids); + + foreach my $chart (@{ $params{charts} }) { + $chart->{balance} = $balances{ $chart->{id} } || 0; + } + + $main::lxdebug->leave_sub(); +} + + 1;