SL/mebil/Mapping.pm

   1 package SL::mebil::Mapping;
   2
   3 use strict;
   4
   5 # Manager methods for mebil
   6
   7 sub new {
   8         # parameter: 1) ...
   9         my $name = shift;
  10         my $my_data = {
  11                 error_channel    => shift,
  12                 dbh              => shift};
  13         bless $my_data;
  14         return $my_data;
  15 }
  16 sub get_mapping {
  17         my $self = shift;
  18         my $sql = "SELECT fromacc,typ,toacc from mebil_mapping order by ordering";
  19         return SL::DBUtils::selectall_hashref_query($self->{error_channel}, $self->{dbh}, $sql);
  20 }
  21 sub calc_mapping {
  22         my $self = shift;
  23         my $year = shift;
  24
  25         my $sql = "SELECT  fromacc,typ,toacc from mebil_mapping order by ordering";
  26         my $data = SL::DBUtils::selectall_hashref_query($self->{error_channel}, $self->{dbh}, $sql);
  27
  28         my %fromacc = ();
  29         my %toacc   = ();
  30
  31         foreach my $mapping (@{ $data }) {
  32                 if ($mapping->{typ} eq 'H') {
  33                         # process Haben
  34                         $sql = "SELECT SUM(ac.amount) AS saldo
  35                                                 FROM acc_trans ac
  36                                                 JOIN chart c ON (c.id = ac.chart_id)
  37                                                 WHERE (ac.transdate <= '31.12.$year')
  38                                                 AND (c.accno = '$mapping->{fromacc}') ";
  39                         my $result = SL::DBUtils::selectall_hashref_query($self->{error_channel}, $self->{dbh}, $sql);
  40                         $fromacc{$mapping->{fromacc}} = $result->[0]->{saldo};
  41                         $toacc{$mapping->{toacc}} += $result->[0]->{saldo};
  42                 }
  43                 elsif ($mapping->{typ} eq 'S') {
  44                         # process Soll
  45                         $sql = "SELECT SUM(ac.amount)* -1 AS saldo
  46                                                 FROM acc_trans ac
  47                                                 JOIN chart c ON (c.id = ac.chart_id)
  48                                                 WHERE (ac.transdate <= '31.12.$year')
  49                                                 AND (c.accno = '$mapping->{fromacc}') ";
  50                         my @result = SL::DBUtils::selectfirst_array_query($self->{error_channel}, $self->{dbh}, $sql);
  51                         $fromacc{$mapping->{fromacc}} = $result[0];
  52                         $toacc{$mapping->{toacc}} += $result[0];
  53                 }
  54 #               elsif ($mapping->{typ} eq 'V') {
  55 #                       # process Vorjahr
  56 #                       $fromacc{$mapping->{fromacc}} = 300.;
  57 #                       $toacc{$mapping->{toacc}} = 300.;
  58 #               }
  59 #               elsif ($mapping->{typ} eq 'A') {
  60 #                       # process Aktjahr
  61 #                       $fromacc{$mapping->{fromacc}} = 400.;
  62 #                       $toacc{$mapping->{toacc}} = 400.;
  63 #               }
  64                 elsif ($mapping->{typ} eq 'X') {
  65                         # add to other account
  66                         $toacc{$mapping->{toacc}} += $toacc{$mapping->{fromacc}};
  67                 }
  68                 elsif ($mapping->{typ} eq 'Y') {
  69                         # substract from other account
  70                         $toacc{$mapping->{toacc}} -= $toacc{$mapping->{fromacc}};
  71                 }
  72                 elsif ($mapping->{typ} eq 'R') {
  73                         # rule based
  74                         my $sql = rule2sql($mapping->{fromacc}, $year);
  75                         $::lxdebug->message(5, "sql=$sql");
  76                         my @result = SL::DBUtils::selectfirst_array_query($self->{error_channel}, $self->{dbh}, $sql);
  77                         $toacc{$mapping->{toacc}} += $result[0];
  78                 }
  79                 elsif ($mapping->{typ} eq 'C') {
  80                         # constant value
  81                         ; # do nothing here
  82                 }
  83                 else {
  84                         die "Error: Invalid mapping type: $mapping->{typ}\n";
  85                 }
  86         }
  87         return \%fromacc, \%toacc;
  88 }
  89 sub rule2sql {
  90         $::lxdebug->enter_sub;
  91
  92         my $rule = shift;
  93         my $year = shift;
  94         $::lxdebug->message(5, "rule=$rule");
  95
  96         # a rule consists of key=value pairs seperated by colon. Possible keys are:
  97         # - ACC: account number
  98         # - VALUES=positive|negative: only positive or negative values are selected
  99         #     negative implies INVERT=true
 100         # - INVERT=true: Result is multiplied by -1
 101         # - START=YEAR: start year. Absolute ('2020') or relative ('YEAR/PY')
 102         # - END=YEAR: end year. Default: $year
 103
 104         # supply defaults:
 105         my $acc = "0000";
 106         my $values = "";
 107         my $invert = "";
 108         my $start = "";
 109         my $end = "AND (ac.transdate <= '31.12.$year')";
 110         my $py = $year - 1;
 111
 112         # parse rule
 113         (my $part,$rule) = extract_rule($rule);
 114         $::lxdebug->message(5, "part=$part");
 115         while ($part) {
 116                 $part =~ /(.*)=(.*)/;
 117                 if ($1 eq "ACC") {
 118                         $acc = $2;
 119                 }
 120                 elsif ($1 eq "VALUES") {
 121                         if ($2 eq "positive") {
 122                                 $values = "AND (ac.amount > 0)";
 123                         }
 124                         elsif ($2 eq "negative") {
 125                                 $values = "AND (ac.amount < 0)";
 126                                 $invert = "* (-1)";
 127                         }
 128                         else {
 129                                 die "invalid rule part: $part";
 130                         }
 131                 }
 132                 elsif ($1 eq "INVERT") {
 133                         $invert = "* (-1)";
 134                 }
 135                 elsif ($1 eq "START") {
 136                         $start = "AND (ac.transdate >= '01.01.$2')";
 137                         $start =~ s/YEAR/$year/;
 138                         $start =~ s/PY/$py/;
 139                 }
 140                 elsif ($1 eq "END") {
 141                         $end = "AND (ac.transdate <= '31.12.$2')";
 142                         $end =~ s/YEAR/$year/;
 143                         $end =~ s/PY/$py/;
 144                 }
 145                 else {
 146                         die "invalid rule part: $part";
 147                 }
 148                 ($part,$rule) = extract_rule($rule);
 149         }
 150
 151         $::lxdebug->leave_sub;
 152         return "SELECT SUM(ac.amount) $invert AS saldo
 153                                                 FROM acc_trans ac
 154                                                 JOIN chart c ON (c.id = ac.chart_id)
 155                                                 WHERE (c.accno = '$acc') $values $start $end";
 156 }
 157 sub extract_rule {
 158         my $rule = shift;
 159         my $part = $rule;
 160         $part =~ s/:.*//;
 161         if ($rule =~ /:/) {
 162                 $rule =~ s/[^:]*://;
 163         }
 164         else {
 165                 $rule = "";
 166         }
 167         $::lxdebug->message(5, "part=$part");
 168         return ($part,$rule);
 169 }
 170
 171 1;