X-Git-Url: http://wagnertech.de/gitweb/gitweb.cgi/mfinanz.git/blobdiff_plain/b90ff6b18df92c98b1b875d14835d1fdf9ff339a..47db6ae13df64092d401896ec476b9335e9ec807:/SL/Controller/Mebil.pm

diff --git a/SL/Controller/Mebil.pm b/SL/Controller/Mebil.pm
index 99e20ff6d..b8d02a46c 100644
--- a/SL/Controller/Mebil.pm
+++ b/SL/Controller/Mebil.pm
@@ -13,6 +13,8 @@ use SL::Locale::String;  # t8
 #use Data::Dumper;
 #use SL::ClientJS;
 
+use SL::mebil::Mapping;
+
 use Rose::Object::MakeMethods::Generic (
   scalar                  => [ qw(report number_columns year current_year objects subtotals_per_quarter salesman_id) ],
   'scalar --get_set_init' => [ qw(employees types data) ],
@@ -52,12 +54,12 @@ sub action_showmap {
 	my ($self) = @_;
 	
 	# call model -> diese Zeile ist fraglich, war ein Konflikt
-	$self->{data} = DB::MebilMapping::getMappings($::form->get_standard_dbh);
+	#$self->{data} = DB::MebilMapping::getMappings($::form->get_standard_dbh);
 	
 	$::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 $mapping = new SL::mebil::Mapping($::form, $::form->get_standard_dbh);
+	$self->{data} = $mapping->get_mapping();
 
 	$self->prepare_report;
 	$self->list_data;
@@ -65,89 +67,6 @@ 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) = @_;
@@ -156,68 +75,9 @@ sub action_calcmap {
 	
 	$::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));
 
@@ -241,8 +101,8 @@ sub action_calcmap {
   $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;