From deb4d2dbb676d7d6f69dfe7815d6e0cb09bd4a44 Mon Sep 17 00:00:00 2001
From: Michael Wagner <michael@wagnertech.de>
Date: Mon, 20 Dec 2021 19:24:17 +0100
Subject: [PATCH] epic-ts

---
 SL/Controller/Mebil.pm | 92 +++++++++++++++++++++++++++++++++++++++++-
 sql/mebil.sql          | 19 +++++++++
 2 files changed, 110 insertions(+), 1 deletion(-)

diff --git a/SL/Controller/Mebil.pm b/SL/Controller/Mebil.pm
index fd77ce5b0..db4a84b11 100644
--- a/SL/Controller/Mebil.pm
+++ b/SL/Controller/Mebil.pm
@@ -62,6 +62,89 @@ 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) = @_;
@@ -117,6 +200,13 @@ sub action_calcmap {
 			# 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
@@ -151,8 +241,8 @@ sub action_calcmap {
 	$self->add_data_sorted(\%fromacc);
 	$self->add_data_sorted(\%toacc);
  
-	return $self->report->generate_with_headers;
 	$::lxdebug->leave_sub;
+	return $self->report->generate_with_headers;
 }
 sub add_data_sorted {
 	my $self = shift;
diff --git a/sql/mebil.sql b/sql/mebil.sql
index 218a4ffca..db92f51dc 100644
--- a/sql/mebil.sql
+++ b/sql/mebil.sql
@@ -74,6 +74,25 @@ VALUES
 	(22,'X',       'is.netIncome.regular.operatingTC.grossTradingProfit.materialServices.material','ismi.netIncome.materialServices'),
 	(50,'Y',       'ismi.netIncome.materialServices','ismi.netIncome'),
 	
+	(50,'X','bs.ass.fixAss.tan.machinery.gwgsammelposten','all'),
+	(10,'R','ACC=0482:START=2016:VALUES=negative','grossCost.beginning'),
+	(10,'R','ACC=0484:START=2017:VALUES=negative','grossCost.beginning'),
+	(50,'X','grossCost.beginning','gross'),
+	(50,'X','gross.addition','gross'),
+	(10,'R','ACC=0483:START=YEAR:VALUES=negative','gross.addition'),
+	(52,'X','grossCost.beginning','accDepr'),
+	(52,'X','gross.addition','accDepr'),
+	(52,'Y','all','accDepr'),
+	(50,'X','grossCost.beginning','accDepr.beginning'),
+	(50,'Y','all_Prev_period','accDepr.beginning'),
+	(50,'X','all_Prev_period','accDepr.DeprPeriod'),
+	(50,'X','gross.addition','accDepr.DeprPeriod'),
+	(50,'Y','all','accDepr.DeprPeriod'),
+	(60,'X','accDepr.DeprPeriod','accDepr.DeprPeriod.regular'),
+	(10,'R','ACC=0482:END=PY:INVERT=true','all_Prev_period'),
+	(10,'R','ACC=0483:END=PY:INVERT=true','all_Prev_period'),
+	(10,'R','ACC=0484:END=PY:INVERT=true','all_Prev_period'),
+	
 	(0,'C','de-gaap-ci.bsAss','hbst.transfer.bsAss.name')
 	;
 
-- 
2.20.1