From 703f76b624a9c447bf77e40db2cd3d5f3cc19e51 Mon Sep 17 00:00:00 2001 From: Philip Reetz Date: Fri, 29 May 2009 15:30:45 +0000 Subject: [PATCH] Kontenuebersicht und Kontenblatt angepasst, so dass auch bei EUR korrekte Werte angezeigt werden --- SL/CA.pm | 54 +++++++++++++++++++++++++++++------------------ bin/mozilla/ca.pl | 4 ++++ 2 files changed, 37 insertions(+), 21 deletions(-) diff --git a/SL/CA.pm b/SL/CA.pm index e1079c83a..e8676f992 100644 --- a/SL/CA.pm +++ b/SL/CA.pm @@ -48,10 +48,17 @@ sub all_accounts { # connect to database my $dbh = $form->dbconnect($myconfig); + if ($form->{method} eq "cash") { + $acc_cash_where = qq| AND (a.trans_id IN (SELECT id FROM ar WHERE datepaid>=(select date_trunc('year', current_date)) UNION SELECT id FROM ap WHERE datepaid>=(select date_trunc('year', current_date)) UNION SELECT id FROM gl WHERE transdate>=(select date_trunc('year', current_date)))) |; + } else { + $acc_cash_where = " AND ((select date_trunc('year', a.transdate::date)) >= (select date_trunc('year', current_date)))"; + } + my $query = qq|SELECT c.accno, SUM(a.amount) AS amount | . qq|FROM chart c, acc_trans a | . qq|WHERE c.id = a.chart_id | . + qq|$acc_cash_where| . qq|GROUP BY c.accno|; my $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); @@ -186,9 +193,15 @@ sub all_transactions { if ($form->{method} eq "cash") { - $acc_cash_where = qq| AND (ac.trans_id IN (SELECT id FROM ar WHERE datepaid>='$form->{fromdate}' AND datepaid<='$form->{todate}' UNION SELECT id FROM ap WHERE datepaid>='$form->{fromdate}' AND datepaid<='$form->{todate}' UNION SELECT id FROM gl WHERE transdate>='$form->{fromdate}' AND transdate<='$form->{todate}')) |; - $ar_ap_cash_where = qq| AND (a.datepaid>='$form->{fromdate}' AND a.datepaid<='$form->{todate}') |; - } + $where = qq| (ac.trans_id IN (SELECT id FROM ar WHERE datepaid>= ? AND datepaid<= ? UNION SELECT id FROM ap WHERE datepaid>= ? AND datepaid<= ? UNION SELECT id FROM gl WHERE transdate>= ? AND transdate<= ?)) |; + @where_values = (); + push(@where_values, conv_date($form->{fromdate})); + push(@where_values, conv_date($form->{todate})); + push(@where_values, conv_date($form->{fromdate})); + push(@where_values, conv_date($form->{todate})); + push(@where_values, conv_date($form->{fromdate})); + push(@where_values, conv_date($form->{todate})); + } if ($form->{accno}) { @@ -204,39 +217,39 @@ sub all_transactions { JOIN chart c ON (ac.chart_id = c.id) WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.ob_transaction $project - AND c.accno = ? $acc_cash_where|; + AND c.accno = ?|; ($form->{beginning_balance}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno}); # get last transaction date my $todate = ($form->{todate}) ? " AND ac.transdate <= '$form->{todate}' " : ""; - $query = qq|SELECT max(ac.transdate) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id) WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) $todate AND c.accno = ? $acc_cash_where|; - ($form->{last_transaction}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno}); + $query = qq|SELECT max(ac.transdate) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id) WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) $todate AND c.accno = ? AND $where|; + ($form->{last_transaction}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno}, @where_values); # get old saldo - $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) AND ac.transdate < ? AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $acc_cash_where|; - ($form->{saldo_old}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{fromdate}, $form->{accno}); + $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) AND ac.transdate < ? AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND $where|; + ($form->{saldo_old}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{fromdate}, $form->{accno}, @where_values); #get old balance - $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) AND ac.transdate < ? AND c.accno = ? AND ac.amount < 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $acc_cash_where|; - ($form->{old_balance_debit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{fromdate}, $form->{accno}); + $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) AND ac.transdate < ? AND c.accno = ? AND ac.amount < 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND $where|; + ($form->{old_balance_debit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{fromdate}, $form->{accno}, @where_values); - $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) AND ac.transdate < ? AND c.accno = ? AND ac.amount > 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $acc_cash_where|; - ($form->{old_balance_credit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{fromdate}, $form->{accno}); + $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) AND ac.transdate < ? AND c.accno = ? AND ac.amount > 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND $where|; + ($form->{old_balance_credit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{fromdate}, $form->{accno}, @where_values); # get current saldo my $todate = ($form->{todate} ne "") ? " AND ac.transdate <= '$form->{todate}' " : ""; - $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) $todate AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $acc_cash_where|; - ($form->{saldo_new}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno}); + $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) $todate AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND $where|; + ($form->{saldo_new}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno}, @where_values); #get current balance my $todate = ($form->{todate} ne "") ? " AND ac.transdate <= '$form->{todate}' " : ""; - $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) $todate AND c.accno = ? AND ac.amount < 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $acc_cash_where|; - ($form->{current_balance_debit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno}); + $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) $todate AND c.accno = ? AND ac.amount < 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND $where|; + ($form->{current_balance_debit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno}, @where_values); my $todate = ($form->{todate} ne "") ? " AND ac.transdate <= '$form->{todate}' " : ""; - $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) $todate AND c.accno = ? AND ac.amount > 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $acc_cash_where|; - ($form->{current_balance_credit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno}); + $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) $todate AND c.accno = ? AND ac.amount > 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND $where|; + ($form->{current_balance_credit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno}, @where_values); } } @@ -278,7 +291,7 @@ sub all_transactions { qq| AND ac.chart_id = ? | . qq| AND ac.trans_id = a.id | . qq| AND a.customer_id = c.id | . - qq| AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $ar_ap_cash_where| . + qq| AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)| . qq|UNION ALL | . @@ -291,8 +304,7 @@ sub all_transactions { qq| AND ac.chart_id = ? | . qq| AND ac.trans_id = a.id | . qq| AND a.vendor_id = v.id |; - qq| AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $ar_ap_cash_where| . - + qq| AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)| . push(@values, @where_values, @department_values, @project_values, $id, @where_values, @department_values, @project_values, $id, diff --git a/bin/mozilla/ca.pl b/bin/mozilla/ca.pl index b45c36d64..0558ae6ba 100644 --- a/bin/mozilla/ca.pl +++ b/bin/mozilla/ca.pl @@ -78,6 +78,10 @@ sub chart_of_accounts { $form->{title} = $locale->text('Chart of Accounts'); + if ($eur) { + $form->{method} = "cash"; + } + CA->all_accounts(\%myconfig, \%$form); my @columns = qw(accno description debit credit); -- 2.20.1