1 package SL::mebil::Mapping;
5 # Manager methods for mebil
11 error_channel => 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);
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);
31 foreach my $mapping (@{ $data }) {
32 if ($mapping->{typ} eq 'H') {
34 $sql = "SELECT SUM(ac.amount) AS saldo
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};
43 elsif ($mapping->{typ} eq 'S') {
45 $sql = "SELECT SUM(ac.amount)* -1 AS saldo
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];
54 # elsif ($mapping->{typ} eq 'V') {
56 # $fromacc{$mapping->{fromacc}} = 300.;
57 # $toacc{$mapping->{toacc}} = 300.;
59 # elsif ($mapping->{typ} eq 'A') {
61 # $fromacc{$mapping->{fromacc}} = 400.;
62 # $toacc{$mapping->{toacc}} = 400.;
64 elsif ($mapping->{typ} eq 'X') {
65 # add to other account
66 $toacc{$mapping->{toacc}} += $toacc{$mapping->{fromacc}};
68 elsif ($mapping->{typ} eq 'Y') {
69 # substract from other account
70 $toacc{$mapping->{toacc}} -= $toacc{$mapping->{fromacc}};
72 elsif ($mapping->{typ} eq 'R') {
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];
79 elsif ($mapping->{typ} eq 'C') {
84 die "Error: Invalid mapping type: $mapping->{typ}\n";
87 return \%fromacc, \%toacc;
90 $::lxdebug->enter_sub;
94 $::lxdebug->message(5, "rule=$rule");
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
109 my $end = "AND (ac.transdate <= '31.12.$year')";
113 (my $part,$rule) = extract_rule($rule);
114 $::lxdebug->message(5, "part=$part");
116 $part =~ /(.*)=(.*)/;
120 elsif ($1 eq "VALUES") {
121 if ($2 eq "positive") {
122 $values = "AND (ac.amount > 0)";
124 elsif ($2 eq "negative") {
125 $values = "AND (ac.amount < 0)";
129 die "invalid rule part: $part";
132 elsif ($1 eq "INVERT") {
135 elsif ($1 eq "START") {
136 $start = "AND (ac.transdate >= '01.01.$2')";
137 $start =~ s/YEAR/$year/;
140 elsif ($1 eq "END") {
141 $end = "AND (ac.transdate <= '31.12.$2')";
142 $end =~ s/YEAR/$year/;
146 die "invalid rule part: $part";
148 ($part,$rule) = extract_rule($rule);
151 $::lxdebug->leave_sub;
152 return "SELECT SUM(ac.amount) $invert AS saldo
154 JOIN chart c ON (c.id = ac.chart_id)
155 WHERE (c.accno = '$acc') $values $start $end";
167 $::lxdebug->message(5, "part=$part");
168 return ($part,$rule);