From 902b70fc8afe197df219583aef4913f152f1964f Mon Sep 17 00:00:00 2001 From: Rolf Fluehmann Date: Mon, 7 Oct 2013 14:51:09 +0200 Subject: [PATCH] new report Erfolgsrechnung Conflicts: bin/mozilla/rp.pl --- SL/Layout/Base.pm | 10 +- SL/RP.pm | 84 ++- bin/mozilla/am.pl | 92 ++- bin/mozilla/rp.pl | 188 +----- menus/erp_ch.ini | 723 +++++++++++++++++++++ templates/webpages/rp/erfolgsrechnung.html | 40 ++ templates/webpages/rp/report.html | 187 +++++- 7 files changed, 1080 insertions(+), 244 deletions(-) create mode 100644 menus/erp_ch.ini create mode 100644 templates/webpages/rp/erfolgsrechnung.html diff --git a/SL/Layout/Base.pm b/SL/Layout/Base.pm index e39502f1f..f3c40bf67 100644 --- a/SL/Layout/Base.pm +++ b/SL/Layout/Base.pm @@ -19,6 +19,7 @@ use Rose::Object::MakeMethods::Generic ( use SL::Menu; use SL::Presenter; +use SL::DBUtils; my %menu_cache; @@ -29,7 +30,14 @@ sub new { } sub init_menu { - SL::Menu->new('user'); + my @menu_files; + if(scalar(grep(/^Switzerland/, (selectrow_query($::form, $::form->get_standard_dbh, 'SELECT coa FROM defaults'))[0]))) { + @menu_files = qw(menus/erp_ch.ini); + } else { + @menu_files = qw(menus/erp.ini); + } + unshift @menu_files, 'menus/crm.ini' if $::instance_conf->crm_installed; + Menu->new(@menu_files); } sub init_auto_reload_resources_param { diff --git a/SL/RP.pm b/SL/RP.pm index e8e07e0ce..b5f35e5db 100644 --- a/SL/RP.pm +++ b/SL/RP.pm @@ -1872,33 +1872,89 @@ sub income_statement { $main::lxdebug->leave_sub(); } -sub income_statement_ch { - +sub erfolgsrechnung { $main::lxdebug->enter_sub(); my ($self, $myconfig, $form) = @_; + ($form->{company}, $form->{adress}) = get_defaults_ch(); + + # wrong user inputs should be handled during users input + # e.g. spaces, tabs, wrong format or wrong dates + $form->{fromdate} = "01.01.2000" if ! $form->{fromdate}; + $form->{todate} = $form->current_date(%{$myconfig}) if ! $form->{todate}; + + my %categories = (I => "ERTRAG", E => "AUFWAND"); + my $fromdate = conv_dateq($form->{fromdate}); + my $todate = conv_dateq($form->{todate}); + + $form->{total} = 0; + foreach my $category (keys %categories) { + my %category = ( + name => $categories{$category}, + total => 0, + accounts => get_accounts_ch($category), + ); + foreach my $account (@{$category{accounts}}) { + $account->{total} = 0; + $account->{total} += ($account->{category} eq $category ? 1 : -1) * $_ foreach (@{get_amounts_ch($account->{id}, $fromdate, $todate)}); + $category{total} = $account->{total}; + } + $form->{total} += $category{total}; + push(@{$form->{categories}}, \%category); + } - my $last_period = 0; - + $main::lxdebug->leave_sub(); + return {}; +} - # connect to database - my $dbh = $form->dbconnect($myconfig); +sub get_defaults_ch { + $main::lxdebug->enter_sub(); - get_accounts_ch($dbh, $last_period, $form->{fromdate}, $form->{todate}, $form); + my $query = q|SELECT company, address FROM defaults|; + my $row = @{_query($query)}[0]; + my ($company, $adress) = ($row->{company}, $row->{address}); - $main::lxdebug->leave_sub(); + $main::lxdebug->leave_sub(); + return ($company, $adress); } +sub get_accounts_ch { + $main::lxdebug->enter_sub(); + + my ($category) = @_; + my ($query, $inclusion); + if ($category eq 'I') {$inclusion = "AND pos_eur = NULL OR pos_eur > '0' AND pos_eur <= '5'";} + elsif ($category eq 'E') {$inclusion = "AND pos_eur = NUll OR pos_eur >= '6' AND pos_eur < '100'";} + else {$inclusion = "";} + $query = qq| + SELECT id, accno, description, category + FROM chart + WHERE category = '$category' $inclusion + ORDER BY accno + |; + my $accounts = _query($query); + + $main::lxdebug->leave_sub(); + return $accounts; +} - sub get_accounts_ch { +sub get_amounts_ch { $main::lxdebug->enter_sub(); - my $query = - qq|SELECT c.accno, c.description - FROM chart c - ORDER BY c.accno|; + my ($chart_id, $fromdate, $todate) = @_; + my $query = qq| + SELECT amount + FROM acc_trans + WHERE chart_id = '$chart_id' + AND transdate >= $fromdate + AND transdate <= $todate + |; + my $amounts = _query($query); - $main::lxdebug->leave_sub(); + $main::lxdebug->leave_sub(); + return $amounts; } +sub _query {return selectall_hashref_query($::form, $::form->get_standard_dbh, $_[0]);} + 1; diff --git a/bin/mozilla/am.pl b/bin/mozilla/am.pl index ad17f3123..5cde7aef8 100644 --- a/bin/mozilla/am.pl +++ b/bin/mozilla/am.pl @@ -214,38 +214,66 @@ sub account_header { } my $select_eur = q|\n|; - my %eur = ( - 1 => "Umsatzerlöse", - 2 => "sonstige Erlöse", - 3 => "Privatanteile", - 4 => "Zinserträge", - 5 => "Ausserordentliche Erträge", - 6 => "Vereinnahmte Umsatzst.", - 7 => "Umsatzsteuererstattungen", - 8 => "Wareneingänge", - 9 => "Löhne und Gehälter", - 10 => "Gesetzl. sozialer Aufw.", - 11 => "Mieten", - 12 => "Gas, Strom, Wasser", - 13 => "Instandhaltung", - 14 => "Steuern, Versich., Beiträge", - 15 => "Kfz-Steuern", - 16 => "Kfz-Versicherungen", - 17 => "Sonst. Fahrzeugkosten", - 18 => "Werbe- und Reisekosten", - 19 => "Instandhaltung u. Werkzeuge", - 20 => "Fachzeitschriften, Bücher", - 21 => "Miete für Einrichtungen", - 22 => "Rechts- und Beratungskosten", - 23 => "Bürobedarf, Porto, Telefon", - 24 => "Sonstige Aufwendungen", - 25 => "Abschreibungen auf Anlagever.", - 26 => "Abschreibungen auf GWG", - 27 => "Vorsteuer", - 28 => "Umsatzsteuerzahlungen", - 29 => "Zinsaufwand", - 30 => "Ausserordentlicher Aufwand", - 31 => "Betriebliche Steuern"); + my %eur; + if(scalar(grep(/^Switzerland/, (selectrow_query($form, $form->get_standard_dbh, 'SELECT coa FROM defaults'))[0]))) { + %eur = ( + 1 => "Umsatzerlöse", + 2 => "Spenden", + 3 => "Mitgliederbeiträge", + 4 => "Finanzerträge", + 5 => "Ertragsminderungen", + 6 => "Einkauf", + 7 => "Löhne und Gehälter", + 8 => "Sozialversicherungen", + 9 => "Mieten", + 10 => "Betriebsversicherungen", + 11 => "Material- und Warenverluste", + 12 => "Werbeaufwand", + 13 => "Werkzeuge, Unterhalt, Betriebsmaterial", + 14 => "Internet- und Serverhousingkosten", + 15 => "Arbeitsleistungen externer Firmen", + 16 => "Bürobedarf, Versandspesen, Telefon", + 17 => "Sonstige Aufwendungen", + 18 => "Abschreibungen", + 19 => "Finanzaufwand", + 20 => "Aufwandsminderungen", + 21 => "Betriebliche Steuern", + ); + } else { + %eur = ( + 1 => "Umsatzerlöse", + 2 => "sonstige Erlöse", + 3 => "Privatanteile", + 4 => "Zinserträge", + 5 => "Ausserordentliche Erträge", + 6 => "Vereinnahmte Umsatzst.", + 7 => "Umsatzsteuererstattungen", + 8 => "Wareneingänge", + 9 => "Löhne und Gehälter", + 10 => "Gesetzl. sozialer Aufw.", + 11 => "Mieten", + 12 => "Gas, Strom, Wasser", + 13 => "Instandhaltung", + 14 => "Steuern, Versich., Beiträge", + 15 => "Kfz-Steuern", + 16 => "Kfz-Versicherungen", + 17 => "Sonst. Fahrzeugkosten", + 18 => "Werbe- und Reisekosten", + 19 => "Instandhaltung u. Werkzeuge", + 20 => "Fachzeitschriften, Bücher", + 21 => "Miete für Einrichtungen", + 22 => "Rechts- und Beratungskosten", + 23 => "Bürobedarf, Porto, Telefon", + 24 => "Sonstige Aufwendungen", + 25 => "Abschreibungen auf Anlagever.", + 26 => "Abschreibungen auf GWG", + 27 => "Vorsteuer", + 28 => "Umsatzsteuerzahlungen", + 29 => "Zinsaufwand", + 30 => "Ausserordentlicher Aufwand", + 31 => "Betriebliche Steuern", + ); + } foreach my $item (sort({ $a <=> $b } keys(%eur))) { my $text = H($::locale->{iconv_utf8}->convert($eur{$item})); if ($item == $form->{pos_eur}) { diff --git a/bin/mozilla/rp.pl b/bin/mozilla/rp.pl index 6672be75e..b4092e08e 100644 --- a/bin/mozilla/rp.pl +++ b/bin/mozilla/rp.pl @@ -108,7 +108,7 @@ my $rp_access_map = { 'payments' => 'cash', 'trial_balance' => 'report', 'income_statement' => 'report', - 'income_statement_ch'=> 'report', + 'erfolgsrechnung' => 'report', 'bwa' => 'report', 'balance_sheet' => 'report', }; @@ -130,7 +130,7 @@ sub report { my %title = ( balance_sheet => $::locale->text('Balance Sheet'), income_statement => $::locale->text('Income Statement'), - income_statement_ch => ('Erfolgsrechnung'), + erfolgsrechnung => ('Erfolgsrechnung'), trial_balance => $::locale->text('Trial Balance'), ar_aging => $::locale->text('Search AR Aging'), ap_aging => $::locale->text('Search AP Aging'), @@ -144,8 +144,13 @@ sub report { $::form->{title} = $title{$::form->{report}}; $::request->{layout}->add_javascripts('autocomplete_customer.js'); + $::form->{dateformat} = $::myconfig{dateformat}; + my $year = DateTime->today->year; + $::form->{fromdate} = $::locale->reformat_date(\%::myconfig, '01.01.'.$year, $::myconfig{dateformat}); + $::form->{todate} = $::locale->reformat_date(\%::myconfig, '31.01.'.$year, $::myconfig{dateformat}); # get departments + $::form->all_departments(\%::myconfig); if (@{ $::form->{all_departments} || [] }) { $::form->{selectdepartment} = "