epic-s6g

[kivitendo-erp.git] / SL / mebil / Mapping.pm

diff --git a/SL/mebil/Mapping.pm b/SL/mebil/Mapping.pm
index ca2d5ea..5ff1001 100644 (file)
--- 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;