X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FController%2FMebil.pm;h=570e07011884ecfd7ab063d9031b5d647f9a5699;hb=2a0cbd885790174fa0f212e6661b30362650a42c;hp=903312ffd73792f4e08bfb745c4531c56fdf12bc;hpb=1630b93572dd4c56575665554cb11fe34b2f8860;p=kivitendo-erp.git

diff --git a/SL/Controller/Mebil.pm b/SL/Controller/Mebil.pm
index 903312ffd..570e07011 100644
--- a/SL/Controller/Mebil.pm
+++ b/SL/Controller/Mebil.pm
@@ -8,6 +8,8 @@ use parent qw(SL::Controller::Base);
 use SL::ReportGenerator;
 use SL::DBUtils;
 
+use SL::Locale::String;  # t8
+
 #use Data::Dumper;
 #use SL::ClientJS;
 
@@ -48,8 +50,13 @@ sub action_map {
 sub action_showmap {
 	$::lxdebug->enter_sub;
 	my ($self) = @_;
+	
+	# call model -> diese Zeile ist fraglich, war ein Konflikt
+	#$self->{data} = DB::MebilMapping::getMappings($::form->get_standard_dbh);
+	
+	$::form->{title} = $::locale->text('Mebil Map');
 
-	my $sql = "SELECT  chart_id,xbrl_tag from mebil_mapping";
+	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);
 
 	$self->prepare_report;
@@ -58,30 +65,226 @@ 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) = @_;
+	
+	$self->year($::form->{year} || DateTime->today->year - 1);
+	
+	$::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";
+		}
+	}
+	
+  	$self->report(SL::ReportGenerator->new(\%::myconfig, $::form));
+
+  	my @columns = (qw(name amount));
+
+  	my %column_defs          = (
+    	name                        => { text => 'Konto', align => 'left'   },
+    	amount                      => { text => 'Betrag'   , align => 'right'   },
+  	);
+
+  $self->report->set_options(
+    std_column_visibility => 1,
+    controller_class      => 'Mebil',
+    output_format         => 'HTML',
+	raw_top_info_text     => $self->render('mebil/report_top', { output => 0 }, YEARS_TO_LIST => [ reverse(($self->year - 10)..($self->year + 5)) ]),
+    title                 => t8('mebil - Mapping: values for #1', $self->year),
+#    allow_pdf_export      => 1,
+#    allow_csv_export      => 1,
+  );
+  $self->report->set_columns(%column_defs);
+  $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);
+ 
+	$::lxdebug->leave_sub;
+	return $self->report->generate_with_headers;
+}
+sub add_data_sorted {
+	my $self = shift;
+	my $data = shift; # hash reference
+	
+	foreach my $key (sort keys %$data) {
+		my %data = (
+		    name                     => { data => $key },
+    		amount                   => { data => $::form->format_amount(\%::myconfig, $data->{$key}, 2) },
+		);
+		$self->report->add_data(\%data);
+		
+	}
+}
 sub prepare_report {
   my ($self)      = @_;
 
   $self->report(SL::ReportGenerator->new(\%::myconfig, $::form));
 
-  my @columns = (qw(chart_id xbrl_tag));
+  my @columns = (qw(fromacc typ toacc));
 
   #$self->number_columns([ grep { !m/^(?:month|year|quarter)$/ } @columns ]);
 
   my %column_defs          = (
-    chart_id                  => { text => 'Kontonummer'                  },
-    xbrl_tag                   => { text => 'XBRL'                   },
+    fromacc                  => { text => 'Quelle', align => 'left'   },
+    typ                      => { text => 'Typ'   , align => 'right'   },
+    toacc                    => { text => 'Ziel'  , align => 'left'   },
   );
 
-  $column_defs{$_}->{align} = 'right' for @columns;
+  #$column_defs{$_}->{align} = 'right' for @columns;
 
   $self->report->set_options(
     std_column_visibility => 1,
     controller_class      => 'Mebil',
     output_format         => 'HTML',
  #   raw_top_info_text     => $self->render('financial_overview/report_top', { output => 0 }, YEARS_TO_LIST => [ reverse(($self->current_year - 10)..($self->current_year + 5)) ]),
- #   title                 => t8('Financial overview for #1', $self->year),
- #   allow_pdf_export      => 1,
- #   allow_csv_export      => 1,
+    title                 => 'mebil - Mapping',
+#    allow_pdf_export      => 1,
+#    allow_csv_export      => 1,
   );
   $self->report->set_columns(%column_defs);
   $self->report->set_column_order(@columns);
@@ -97,8 +300,9 @@ sub list_data {
 
 	foreach my $mapping (@{ $self->{data} }) {
 		my %data = (
-			chart_id => { data => $mapping->{chart_id}},
-			xbrl_tag => { data => $mapping->{xbrl_tag}},
+		    fromacc                  => { data => $mapping->{fromacc} },
+    		typ                      => { data => $mapping->{typ}     },
+    		toacc                    => { data => $mapping->{toacc}   },
 		);
 		$self->report->add_data(\%data);
 	}