$::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) = @_;
$::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));
$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;
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;
-|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|
--- /dev/null
+!contents -R2 -g -p -f -h
\ No newline at end of file
--- /dev/null
+<?xml version="1.0"?>
+<properties>
+ <Edit>true</Edit>
+ <Files>true</Files>
+ <Properties>true</Properties>
+ <RecentChanges>true</RecentChanges>
+ <Refactor>true</Refactor>
+ <Search>true</Search>
+ <Suite>true</Suite>
+ <Versions>true</Versions>
+ <WhereUsed>true</WhereUsed>
+</properties>
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;
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";
}
projects / kivitendo-erp.git / commitdiff