From 47db6ae13df64092d401896ec476b9335e9ec807 Mon Sep 17 00:00:00 2001 From: Sparky6 Date: Tue, 13 Dec 2022 19:11:40 +0100 Subject: [PATCH] epic-s6g --- SL/Controller/Mebil.pm | 152 +----------------- SL/mebil/Mapping.pm | 149 +++++++++++++++++ Test/FitNesseRoot/RecentChanges/content.txt | 3 +- .../MappingSuite/CalcMapping/.dummy | 0 .../MappingSuite/ShowMapping/.dummy | 0 Test/FitnessePages/MappingSuite/content.txt | 1 + .../FitnessePages/MappingSuite/properties.xml | 12 ++ Test/perl/test_action.pl | 7 +- 8 files changed, 173 insertions(+), 151 deletions(-) create mode 100644 Test/FitnessePages/MappingSuite/CalcMapping/.dummy create mode 100644 Test/FitnessePages/MappingSuite/ShowMapping/.dummy create mode 100644 Test/FitnessePages/MappingSuite/content.txt create mode 100644 Test/FitnessePages/MappingSuite/properties.xml diff --git a/SL/Controller/Mebil.pm b/SL/Controller/Mebil.pm index dbb8c2dea..b8d02a46c 100644 --- a/SL/Controller/Mebil.pm +++ b/SL/Controller/Mebil.pm @@ -67,89 +67,6 @@ sub action_showmap { $::lxdebug->leave_sub; } -sub extract_rule { - my $rule = shift; - my $part = $rule; - $part =~ s/:.*//; - if ($rule =~ /:/) { - $rule =~ s/[^:]*://; - } - else { - $rule = ""; - } - $::lxdebug->message(5, "part=$part"); - return ($part,$rule); -} - -sub rule2sql { - $::lxdebug->enter_sub; - - my $rule = shift; - my $year = shift; - $::lxdebug->message(5, "rule=$rule"); - - # a rule consists of key=value pairs seperated by colon. Possible keys are: - # - ACC: account number - # - VALUES=positive|negative: only positive or negative values are selected - # negative implies INVERT=true - # - INVERT=true: Result is multiplied by -1 - # - START=YEAR: start year. Absolute ('2020') or relative ('YEAR/PY') - # - END=YEAR: end year. Default: $year - - # supply defaults: - my $acc = "0000"; - my $values = ""; - my $invert = ""; - my $start = ""; - my $end = "AND (ac.transdate <= '31.12.$year')"; - my $py = $year - 1; - - # parse rule - (my $part,$rule) = extract_rule($rule); - $::lxdebug->message(5, "part=$part"); - while ($part) { - $part =~ /(.*)=(.*)/; - if ($1 eq "ACC") { - $acc = $2; - } - elsif ($1 eq "VALUES") { - if ($2 eq "positive") { - $values = "AND (ac.amount > 0)"; - } - elsif ($2 eq "negative") { - $values = "AND (ac.amount < 0)"; - $invert = "* (-1)"; - } - else { - die "invalid rule part: $part"; - } - } - elsif ($1 eq "INVERT") { - $invert = "* (-1)"; - } - elsif ($1 eq "START") { - $start = "AND (ac.transdate >= '01.01.$2')"; - $start =~ s/YEAR/$year/; - $start =~ s/PY/$py/; - } - elsif ($1 eq "END") { - $end = "AND (ac.transdate <= '31.12.$2')"; - $end =~ s/YEAR/$year/; - $end =~ s/PY/$py/; - } - else { - die "invalid rule part: $part"; - } - ($part,$rule) = extract_rule($rule); - } - - $::lxdebug->leave_sub; - return "SELECT SUM(ac.amount) $invert AS saldo - FROM acc_trans ac - JOIN chart c ON (c.id = ac.chart_id) - WHERE (c.accno = '$acc') $values $start $end"; -} - sub action_calcmap { $::lxdebug->enter_sub; my ($self) = @_; @@ -158,68 +75,9 @@ sub action_calcmap { $::form->{title} = $::locale->text('Mebil Map'); - my $sql = "SELECT fromacc,typ,toacc from mebil_mapping order by ordering"; - $self->{data} = SL::DBUtils::selectall_hashref_query($::form, $::form->get_standard_dbh, $sql); - - my %fromacc = (); - my %toacc = (); - my $year = $self->year; - foreach my $mapping (@{ $self->{data} }) { - if ($mapping->{typ} eq 'H') { - # process Haben - $sql = "SELECT SUM(ac.amount) AS saldo - FROM acc_trans ac - JOIN chart c ON (c.id = ac.chart_id) - WHERE (ac.transdate <= '31.12.$year') - AND (c.accno = '$mapping->{fromacc}') "; - my $result = SL::DBUtils::selectall_hashref_query($::form, $::form->get_standard_dbh, $sql); - $fromacc{$mapping->{fromacc}} = $result->[0]->{saldo}; - $toacc{$mapping->{toacc}} += $result->[0]->{saldo}; - } - elsif ($mapping->{typ} eq 'S') { - # process Soll - $sql = "SELECT SUM(ac.amount)* -1 AS saldo - FROM acc_trans ac - JOIN chart c ON (c.id = ac.chart_id) - WHERE (ac.transdate <= '31.12.$year') - AND (c.accno = '$mapping->{fromacc}') "; - my @result = SL::DBUtils::selectfirst_array_query($::form, $::form->get_standard_dbh, $sql); - $fromacc{$mapping->{fromacc}} = $result[0]; - $toacc{$mapping->{toacc}} += $result[0]; - } -# elsif ($mapping->{typ} eq 'V') { -# # process Vorjahr -# $fromacc{$mapping->{fromacc}} = 300.; -# $toacc{$mapping->{toacc}} = 300.; -# } -# elsif ($mapping->{typ} eq 'A') { -# # process Aktjahr -# $fromacc{$mapping->{fromacc}} = 400.; -# $toacc{$mapping->{toacc}} = 400.; -# } - elsif ($mapping->{typ} eq 'X') { - # add to other account - $toacc{$mapping->{toacc}} += $toacc{$mapping->{fromacc}}; - } - elsif ($mapping->{typ} eq 'Y') { - # substract from other account - $toacc{$mapping->{toacc}} -= $toacc{$mapping->{fromacc}}; - } - elsif ($mapping->{typ} eq 'R') { - # rule based - my $sql = rule2sql($mapping->{fromacc}, $year); - $::lxdebug->message(5, "sql=$sql"); - my @result = SL::DBUtils::selectfirst_array_query($::form, $::form->get_standard_dbh, $sql); - $toacc{$mapping->{toacc}} += $result[0]; - } - elsif ($mapping->{typ} eq 'C') { - # constant value - ; # do nothing here - } - else { - die "Error: Invalid mapping type: $mapping->{typ}\n"; - } - } + my $mapping = new SL::mebil::Mapping($::form, $::form->get_standard_dbh); + + (my $fromacc, my $toacc) = $mapping->calc_mapping($self->{year}); $self->report(SL::ReportGenerator->new(\%::myconfig, $::form)); @@ -243,8 +101,8 @@ sub action_calcmap { $self->report->set_column_order(@columns); # $self->report->set_export_options(qw(list year subtotals_per_quarter salesman_id)); $self->report->set_options_from_form; - $self->add_data_sorted(\%fromacc); - $self->add_data_sorted(\%toacc); + $self->add_data_sorted($fromacc); + $self->add_data_sorted($toacc); $::lxdebug->leave_sub; return $self->report->generate_with_headers; diff --git a/SL/mebil/Mapping.pm b/SL/mebil/Mapping.pm index ca2d5ea63..5ff100147 100644 --- a/SL/mebil/Mapping.pm +++ b/SL/mebil/Mapping.pm @@ -18,5 +18,154 @@ sub get_mapping { my $sql = "SELECT fromacc,typ,toacc from mebil_mapping order by ordering"; return SL::DBUtils::selectall_hashref_query($self->{error_channel}, $self->{dbh}, $sql); } +sub calc_mapping { + my $self = shift; + my $year = shift; + + my $sql = "SELECT fromacc,typ,toacc from mebil_mapping order by ordering"; + my $data = SL::DBUtils::selectall_hashref_query($self->{error_channel}, $self->{dbh}, $sql); + + my %fromacc = (); + my %toacc = (); + + foreach my $mapping (@{ $data }) { + if ($mapping->{typ} eq 'H') { + # process Haben + $sql = "SELECT SUM(ac.amount) AS saldo + FROM acc_trans ac + JOIN chart c ON (c.id = ac.chart_id) + WHERE (ac.transdate <= '31.12.$year') + AND (c.accno = '$mapping->{fromacc}') "; + my $result = SL::DBUtils::selectall_hashref_query($self->{error_channel}, $self->{dbh}, $sql); + $fromacc{$mapping->{fromacc}} = $result->[0]->{saldo}; + $toacc{$mapping->{toacc}} += $result->[0]->{saldo}; + } + elsif ($mapping->{typ} eq 'S') { + # process Soll + $sql = "SELECT SUM(ac.amount)* -1 AS saldo + FROM acc_trans ac + JOIN chart c ON (c.id = ac.chart_id) + WHERE (ac.transdate <= '31.12.$year') + AND (c.accno = '$mapping->{fromacc}') "; + my @result = SL::DBUtils::selectfirst_array_query($self->{error_channel}, $self->{dbh}, $sql); + $fromacc{$mapping->{fromacc}} = $result[0]; + $toacc{$mapping->{toacc}} += $result[0]; + } +# elsif ($mapping->{typ} eq 'V') { +# # process Vorjahr +# $fromacc{$mapping->{fromacc}} = 300.; +# $toacc{$mapping->{toacc}} = 300.; +# } +# elsif ($mapping->{typ} eq 'A') { +# # process Aktjahr +# $fromacc{$mapping->{fromacc}} = 400.; +# $toacc{$mapping->{toacc}} = 400.; +# } + elsif ($mapping->{typ} eq 'X') { + # add to other account + $toacc{$mapping->{toacc}} += $toacc{$mapping->{fromacc}}; + } + elsif ($mapping->{typ} eq 'Y') { + # substract from other account + $toacc{$mapping->{toacc}} -= $toacc{$mapping->{fromacc}}; + } + elsif ($mapping->{typ} eq 'R') { + # rule based + my $sql = rule2sql($mapping->{fromacc}, $year); + $::lxdebug->message(5, "sql=$sql"); + my @result = SL::DBUtils::selectfirst_array_query($self->{error_channel}, $self->{dbh}, $sql); + $toacc{$mapping->{toacc}} += $result[0]; + } + elsif ($mapping->{typ} eq 'C') { + # constant value + ; # do nothing here + } + else { + die "Error: Invalid mapping type: $mapping->{typ}\n"; + } + } + return \%fromacc, \%toacc; +} +sub rule2sql { + $::lxdebug->enter_sub; + + my $rule = shift; + my $year = shift; + $::lxdebug->message(5, "rule=$rule"); + + # a rule consists of key=value pairs seperated by colon. Possible keys are: + # - ACC: account number + # - VALUES=positive|negative: only positive or negative values are selected + # negative implies INVERT=true + # - INVERT=true: Result is multiplied by -1 + # - START=YEAR: start year. Absolute ('2020') or relative ('YEAR/PY') + # - END=YEAR: end year. Default: $year + + # supply defaults: + my $acc = "0000"; + my $values = ""; + my $invert = ""; + my $start = ""; + my $end = "AND (ac.transdate <= '31.12.$year')"; + my $py = $year - 1; + + # parse rule + (my $part,$rule) = extract_rule($rule); + $::lxdebug->message(5, "part=$part"); + while ($part) { + $part =~ /(.*)=(.*)/; + if ($1 eq "ACC") { + $acc = $2; + } + elsif ($1 eq "VALUES") { + if ($2 eq "positive") { + $values = "AND (ac.amount > 0)"; + } + elsif ($2 eq "negative") { + $values = "AND (ac.amount < 0)"; + $invert = "* (-1)"; + } + else { + die "invalid rule part: $part"; + } + } + elsif ($1 eq "INVERT") { + $invert = "* (-1)"; + } + elsif ($1 eq "START") { + $start = "AND (ac.transdate >= '01.01.$2')"; + $start =~ s/YEAR/$year/; + $start =~ s/PY/$py/; + } + elsif ($1 eq "END") { + $end = "AND (ac.transdate <= '31.12.$2')"; + $end =~ s/YEAR/$year/; + $end =~ s/PY/$py/; + } + else { + die "invalid rule part: $part"; + } + ($part,$rule) = extract_rule($rule); + } + + $::lxdebug->leave_sub; + return "SELECT SUM(ac.amount) $invert AS saldo + FROM acc_trans ac + JOIN chart c ON (c.id = ac.chart_id) + WHERE (c.accno = '$acc') $values $start $end"; +} +sub extract_rule { + my $rule = shift; + my $part = $rule; + $part =~ s/:.*//; + if ($rule =~ /:/) { + $rule =~ s/[^:]*://; + } + else { + $rule = ""; + } + $::lxdebug->message(5, "part=$part"); + return ($part,$rule); +} 1; diff --git a/Test/FitNesseRoot/RecentChanges/content.txt b/Test/FitNesseRoot/RecentChanges/content.txt index bc1d4b74c..da43d2d86 100644 --- a/Test/FitNesseRoot/RecentChanges/content.txt +++ b/Test/FitNesseRoot/RecentChanges/content.txt @@ -1,4 +1,5 @@ -|FrontPage.MappingSuite.ShowMapping||18:30:23 Di., Dez. 13, 2022| +|FrontPage.MappingSuite.CalcMapping||19:03:27 Di., Dez. 13, 2022| +|FrontPage.MappingSuite.ShowMapping||18:44:25 Di., Dez. 13, 2022| |FrontPage.MappingSuite||12:36:18 Di., Dez. 13, 2022| |FrontPage.AlAktualisierung||19:45:06 Do, Feb 18, 2021| |FrontPage.AdNeuInstallation||19:39:22 Do, Feb 18, 2021| diff --git a/Test/FitnessePages/MappingSuite/CalcMapping/.dummy b/Test/FitnessePages/MappingSuite/CalcMapping/.dummy new file mode 100644 index 000000000..e69de29bb diff --git a/Test/FitnessePages/MappingSuite/ShowMapping/.dummy b/Test/FitnessePages/MappingSuite/ShowMapping/.dummy new file mode 100644 index 000000000..e69de29bb diff --git a/Test/FitnessePages/MappingSuite/content.txt b/Test/FitnessePages/MappingSuite/content.txt new file mode 100644 index 000000000..990fb6f9e --- /dev/null +++ b/Test/FitnessePages/MappingSuite/content.txt @@ -0,0 +1 @@ +!contents -R2 -g -p -f -h \ No newline at end of file diff --git a/Test/FitnessePages/MappingSuite/properties.xml b/Test/FitnessePages/MappingSuite/properties.xml new file mode 100644 index 000000000..6e9e1e6e0 --- /dev/null +++ b/Test/FitnessePages/MappingSuite/properties.xml @@ -0,0 +1,12 @@ + + + true + true + true + true + true + true + true + true + true + diff --git a/Test/perl/test_action.pl b/Test/perl/test_action.pl index 069cc1491..abf737988 100755 --- a/Test/perl/test_action.pl +++ b/Test/perl/test_action.pl @@ -2,9 +2,10 @@ use strict; use lib "/opt/kivitendo-erp"; +use lib "perl"; use SL::LXDebug; use SL::mebil::Mapping; -use Test::perl::ErrorChannel; +use ErrorChannel; $::lxdebug = new LXDebug; @@ -26,14 +27,14 @@ my $errch = new ErrorChannel; my $mapper = new SL::mebil::Mapping($errch, $dbh); my $command = $ARGV[0]; -if ($command eq "GetMapping") { +if ($command eq "ShowMapping") { my $mapping = $mapper->get_mapping(); foreach (@$mapping) { print $_->{fromacc}."\n"; } die ("Kein Mapping vorhanden.") unless (scalar(@$mapping) > 0); } elsif ($command eq "CalcMapping") { - die ("NIY."); + (my $fromacc, my $toacc) = $mapper->calc_mapping(2022); } else { die "Invalid command: $command"; } -- 2.20.1