From c9a7e79e10591ae95b1523f2c7f249eae8439580 Mon Sep 17 00:00:00 2001 From: Philip Reetz Date: Sat, 2 Feb 2008 19:26:31 +0000 Subject: [PATCH] Stichtagsbezogene Auswertungen von offenen Forderungen und Verbindlichkeiten --- SL/RP.pm | 78 +++-------------------------- bin/mozilla/rp.pl | 124 ++++++++++++++++++++++++++++++++++++++-------- locale/de/all | 1 - locale/de/rp | 2 +- 4 files changed, 111 insertions(+), 94 deletions(-) diff --git a/SL/RP.pm b/SL/RP.pm index 33912bae5..1355cc83e 100644 --- a/SL/RP.pm +++ b/SL/RP.pm @@ -1274,18 +1274,20 @@ sub aging { # connect to database my $dbh = $form->dbconnect($myconfig); - my ($invoice, $arap, $buysell, $ct, $ct_id); + my ($invoice, $arap, $buysell, $ct, $ct_id, $ml); if ($form->{ct} eq "customer") { $invoice = "is"; $arap = "ar"; $buysell = "buy"; $ct = "customer"; + $ml = -1; } else { $invoice = "ir"; $arap = "ap"; $buysell = "sell"; $ct = "vendor"; + $ml = 1; } $ct_id = "${ct}_id"; @@ -1315,84 +1317,18 @@ sub aging { street, zipcode, city, country, contact, email, phone as customerphone, fax as customerfax, ${ct}number, "invnumber", "transdate", - (amount - paid) as "c0", 0.00 as "c30", 0.00 as "c60", 0.00 as "c90", - "duedate", invoice, ${arap}.id, - (SELECT $buysell - FROM exchangerate - WHERE (${arap}.curr = exchangerate.curr) - AND (exchangerate.transdate = ${arap}.transdate)) AS exchangerate - FROM ${arap}, ${ct} - WHERE (paid != amount) - AND (${arap}.storno IS FALSE) - AND (${arap}.${ct}_id = ${ct}.id) - AND (${ct}.id = ?) - AND (transdate <= (date $todate - interval '0 days')) - AND (transdate >= (date $todate - interval '30 days')) - - UNION - - -- between 31-60 days - - SELECT ${ct}.id AS ctid, ${ct}.name, - street, zipcode, city, country, contact, email, - phone as customerphone, fax as customerfax, ${ct}number, - "invnumber", "transdate", - 0.00 as "c0", (amount - paid) as "c30", 0.00 as "c60", 0.00 as "c90", - "duedate", invoice, ${arap}.id, - (SELECT $buysell - FROM exchangerate - WHERE (${arap}.curr = exchangerate.curr) - AND (exchangerate.transdate = ${arap}.transdate)) AS exchangerate - FROM ${arap}, ${ct} - WHERE (paid != amount) - AND (${arap}.storno IS FALSE) - AND (${arap}.${ct}_id = ${ct}.id) - AND (${ct}.id = ?) - AND (transdate < (date $todate - interval '30 days')) - AND (transdate >= (date $todate - interval '60 days')) - - UNION - - -- between 61-90 days - - SELECT ${ct}.id AS ctid, ${ct}.name, - street, zipcode, city, country, contact, email, - phone as customerphone, fax as customerfax, ${ct}number, - "invnumber", "transdate", - 0.00 as "c0", 0.00 as "c30", (amount - paid) as "c60", 0.00 as "c90", + (amount - COALESCE((SELECT sum(amount)*$ml FROM acc_trans LEFT JOIN chart ON (acc_trans.chart_id=chart.id) WHERE link ilike '%paid%' AND acc_trans.trans_id=${arap}.id AND acc_trans.transdate <= (date $todate)),0)) as "open", "amount", "duedate", invoice, ${arap}.id, (SELECT $buysell FROM exchangerate WHERE (${arap}.curr = exchangerate.curr) AND (exchangerate.transdate = ${arap}.transdate)) AS exchangerate FROM ${arap}, ${ct} - WHERE (paid != amount) - AND (${arap}.storno IS FALSE) - AND (${arap}.${ct}_id = ${ct}.id) - AND (${ct}.id = ?) - AND (transdate < (date $todate - interval '60 days')) - AND (transdate >= (date $todate - interval '90 days')) - - UNION - - -- over 90 days - - SELECT ${ct}.id AS ctid, ${ct}.name, - street, zipcode, city, country, contact, email, - phone as customerphone, fax as customerfax, ${ct}number, - "invnumber", "transdate", - 0.00 as "c0", 0.00 as "c30", 0.00 as "c60", (amount - paid) as "c90", - "duedate", invoice, ${arap}.id, - (SELECT $buysell - FROM exchangerate - WHERE (${arap}.curr = exchangerate.curr) - AND (exchangerate.transdate = ${arap}.transdate)) AS exchangerate - FROM ${arap}, ${ct} - WHERE (paid != amount) + WHERE ((paid != amount) OR (datepaid > (date $todate) AND datepaid is not null)) AND (${arap}.storno IS FALSE) AND (${arap}.${ct}_id = ${ct}.id) AND (${ct}.id = ?) - AND (transdate < (date $todate - interval '90 days')) + AND (transdate <= (date $todate)) ORDER BY ctid, transdate, invnumber |; @@ -1414,7 +1350,7 @@ sub aging { $form->{AG} = []; # for each company that has some stuff outstanding while (my ($id) = $sth->fetchrow_array) { - do_statement($form, $sth_details, $q_details, $id, $id, $id, $id); + do_statement($form, $sth_details, $q_details, $id); while (my $ref = $sth_details->fetchrow_hashref(NAME_lc)) { $ref->{module} = ($ref->{invoice}) ? $invoice : $arap; diff --git a/bin/mozilla/rp.pl b/bin/mozilla/rp.pl index 8004e920a..731a060fd 100644 --- a/bin/mozilla/rp.pl +++ b/bin/mozilla/rp.pl @@ -1194,11 +1194,6 @@ sub generate_balance_sheet { $auth->assert('report'); - $form->{padding} = "  "; - $form->{bold} = ""; - $form->{endbold} = ""; - $form->{br} = "
"; - RP->balance_sheet(\%myconfig, \%$form); $form->{asofdate} = $form->current_date(\%myconfig) unless $form->{asofdate}; @@ -1216,14 +1211,103 @@ sub generate_balance_sheet { $form->{last_period} = $locale->date(\%myconfig, $form->{compareasofdate}, 0); - $form->{IN} = "balance_sheet.html"; + my $attachment_basename; - # setup company variables for the form - map { $form->{$_} = $myconfig{$_}; } (qw(company address businessnumber nativecurr)); + my $report = SL::ReportGenerator->new(\%myconfig, $form); - $form->{templates} = $myconfig{templates}; + my @hidden_variables = (); + push @hidden_variables, qw(fromdate todate year cash ); + + my $href = build_std_url('action=orders', grep { $form->{$_} } @hidden_variables); + + my %column_defs = ( + 'accno' => { 'text' => $locale->text('Account Number'), }, + 'description' => { 'text' => $locale->text('Description'), }, + 'last_transaction' => { 'text' => $locale->text('Last Transaction'), }, + 'soll_eb' => { 'text' => $locale->text('Debit Starting Balance'), }, + 'haben_eb' => { 'text' => $locale->text('Credit Starting Balance'), }, + 'soll' => { 'text' => $locale->text('Debit'), }, + 'haben' => { 'text' => $locale->text('Credit'), }, + 'soll_kumuliert' => { 'text' => $locale->text('Sum Debit'), }, + 'haben_kumuliert' => { 'text' => $locale->text('Sum Credit'), }, + 'soll_saldo' => { 'text' => $locale->text('Saldo Debit'), }, + 'haben_saldo' => { 'text' => $locale->text('Saldo Credit'), } + ); + + + + my %column_alignment = map { $_ => 'right' } qw(soll_eb haben_eb soll haben soll_kumuliert haben_kumuliert soll_saldo haben_saldo); + + map { $column_defs{$_}->{visible} = 1 } @columns; + + $report->set_columns(%column_defs); + $report->set_column_order(@columns); + + $report->set_export_options('trial_balance', @hidden_variables); + + $report->set_sort_indicator($form->{sort}, 1); + + my @options; + + + $form->{template_fromto} = $locale->date(\%myconfig, $form->{fromdate}, 0) . "  -  " . $locale->date(\%myconfig, $form->{todate}, 0); + $form->{template_to} = $locale->date(\%myconfig, $form->{todate}, 0); + + $report->set_options('output_format' => 'HTML', + 'title' => $form->{title}, + 'attachment_basename' => $attachment_basename . strftime('_%Y%m%d', localtime time), + ); + $report->set_options_from_form(); + # $form->parse_html_template('report_generator/html_report_bilanz', $variables)); + $form->{report_template} = 'report_generator/html_report_bilanz'; + # add sort and escape callback, this one we use for the add sub + $form->{callback} = $href .= "&sort=$form->{sort}"; + + # escape callback for href + $callback = $form->escape($href); + + my @subtotal_columns = qw(soll_eb haben_eb soll haben soll_kumuliert haben_kumuliert soll_saldo haben_saldo); + + my %totals = map { $_ => 0 } @subtotal_columns; + + my $edit_url = build_std_url('action=edit', 'type', 'vc'); + +# foreach $accno (@{ $form->{TB} }) { +# +# $accno->{soll} = $accno->{debit}; +# $accno->{haben} = $accno->{credit}; +# map { $totals{$_} += $accno->{$_} } @subtotal_columns; +# +# map { $accno->{$_} = $form->format_amount(\%myconfig, $accno->{$_}, 2) } qw(soll_eb haben_eb soll haben soll_kumuliert haben_kumuliert soll_saldo haben_saldo); +# +# map { $accno->{$_} = ($accno->{$_} == 0) ? '' : $accno->{$_} } qw(soll_eb haben_eb soll haben soll_kumuliert haben_kumuliert soll_saldo haben_saldo); +# +# my $row = { }; +# +# foreach my $column (@columns) { +# $row->{$column} = { +# 'data' => $accno->{$column}, +# 'align' => $column_alignment{$column}, +# }; +# } +# +# +# $row->{$ordnumber}->{link} = $edit_url . "&id=" . E($oe->{id}) . "&callback=${callback}"; +# +# my $row_set = [ $row ]; +# +# +# $report->add_data($row_set); +# +# $idx++; +# } +# +# $report->add_separator(); +# +# $report->add_data(create_subtotal_row(\%totals, \@columns, \%column_alignment, \@subtotal_columns, 'listtotal')); + + $report->generate_with_headers(); - $form->parse_template; $lxdebug->leave_sub(); } @@ -1676,7 +1760,7 @@ sub create_aging_subtotal_row { my $row = { map { $_ => { 'data' => '', 'class' => $class, 'align' => 'right' } } @{ $columns } }; foreach (@{ $periods }) { - $row->{"c$_"}->{data} = $subtotals->{$_} != 0 ? $form->format_amount(\%myconfig, $subtotals->{$_}, 2) : ''; + $row->{"$_"}->{data} = $subtotals->{$_} != 0 ? $form->format_amount(\%myconfig, $subtotals->{$_}, 2) : ''; $subtotals->{$_} = 0; } @@ -1692,7 +1776,7 @@ sub aging { my $report = SL::ReportGenerator->new(\%myconfig, $form); - my @columns = qw(statement ct invnumber transdate duedate c0 c30 c60 c90); + my @columns = qw(statement ct invnumber transdate duedate amount open); my %column_defs = ( 'statement' => { 'text' => '', 'visible' => $form->{ct} eq 'customer' ? 'HTML' : 0, }, @@ -1700,14 +1784,12 @@ sub aging { 'invnumber' => { 'text' => $locale->text('Invoice'), }, 'transdate' => { 'text' => $locale->text('Date'), }, 'duedate' => { 'text' => $locale->text('Due'), }, - 'c0' => { 'text' => $locale->text('Current'), }, - 'c30' => { 'text' => '30', }, - 'c60' => { 'text' => '60', }, - 'c90' => { 'text' => '90', }, + 'amount' => { 'text' => $locale->text('Amount'), }, + 'open' => { 'text' => $locale->text('Open'), }, ); my %column_alignment = ('statement' => 'center', - map { $_ => 'right' } qw(c0 c30 c60 c90)); + map { $_ => 'right' } qw(open amount)); $report->set_options('std_column_visibility' => 1); $report->set_columns(%column_defs); @@ -1744,7 +1826,7 @@ sub aging { my $previous_ctid = 0; my $row_idx = 0; - my @periods = qw(0 30 60 90); + my @periods = qw(open amount); my %subtotals = map { $_ => 0 } @periods; my %totals = map { $_ => 0 } @periods; @@ -1754,9 +1836,9 @@ sub aging { } foreach my $key (@periods) { - $subtotals{$key} += $ref->{"c${key}"}; - $totals{$key} += $ref->{"c${key}"}; - $ref->{"c${key}"} = $ref->{"c${key}"} != 0 ? $form->format_amount(\%myconfig, $ref->{"c${key}"}, 2) : ''; + $subtotals{$key} += $ref->{"$key"}; + $totals{$key} += $ref->{"$key"}; + $ref->{"$key"} = $ref->{"$key"} != 0 ? $form->format_amount(\%myconfig, $ref->{"$key"}, 2) : ''; } my $row = { }; diff --git a/locale/de/all b/locale/de/all index fb1c2bff7..4c618b2fc 100644 --- a/locale/de/all +++ b/locale/de/all @@ -373,7 +373,6 @@ aktualisieren wollen?', 'Curr' => 'Währung', 'Currencies' => 'Währungen', 'Currency' => 'Währung', - 'Current' => 'Betrag', 'Current / Next Level' => 'Aktuelles / Nächstes Mahnlevel', 'Current Earnings' => 'Gewinn', 'Current unit' => 'Aktuelle Einheit', diff --git a/locale/de/rp b/locale/de/rp index ff4c36b81..1fc8bd1f9 100644 --- a/locale/de/rp +++ b/locale/de/rp @@ -54,7 +54,6 @@ $self->{texts} = { 'Credit' => 'Haben', 'Credit Note' => 'Gutschrift', 'Credit Starting Balance' => 'Eröffnungsbilanzwert Aktiva', - 'Current' => 'Betrag', 'Current Earnings' => 'Gewinn', 'Customer' => 'Kunde', 'Customer Number' => 'Kundennummer', @@ -141,6 +140,7 @@ $self->{texts} = { 'Number' => 'Nummer', 'Oct' => 'Okt', 'October' => 'Oktober', + 'Open' => 'Offen', 'Others' => 'Andere', 'PAYMENT POSTED' => 'Rechung gebucht', 'PDF' => 'PDF', -- 2.20.1