package SL::Controller::Mebil;

use strict;

use parent qw(SL::Controller::Base);

#use SL::Controller::Helper::ReportGenerator;
use SL::ReportGenerator;
use SL::DBUtils;

#use Data::Dumper;
#use SL::ClientJS;

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) ],
);

sub action_map {
	$::lxdebug->enter_sub;
	$::lxdebug->message(5, 'controller=mebil/action=map');
	my ($self) = @_;
	
	$::form->header(no_layout => 1);
	
	print "<h1>Mebil running</h1>";

	my $sql = "SELECT  chart_id,xbrl_tag from mebil_mapping";
	my $result = SL::DBUtils::do_query($::form, $::form->get_standard_dbh, $sql);
	$::lxdebug->message(5, "result= $result");
	print "$result<br>\n";
	
	my @r = SL::DBUtils::selectall_hashref_query($::form, $::form->get_standard_dbh, $sql);
	print ref($r[1])||"SCALAR";
	print "<br>";
	my $fst = $r[1];
	while (my($k,$v) = each(%$fst)) {
		print $k, " : ", $v, "</br>\n";
	}
	print "<br>";
	print scalar @r;
	print "<br>";
	print @r;
	print "<p>Mebil ready</p>";
	$::lxdebug->leave_sub;
}

sub action_showmap {
	$::lxdebug->enter_sub;
	my ($self) = @_;

	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;
	$self->list_data;

	$::lxdebug->leave_sub;
}

sub action_calcmap {
	$::lxdebug->enter_sub;
	my ($self) = @_;

	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   = ();
	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 <= '30.12.2020') 
						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 <= '30.12.2020') 
						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}};
		}
		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'   },
  	);

  #$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                 => 'mebil - Mapping: Werte für 2020',
#    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);
 
	return $self->report->generate_with_headers;
	$::lxdebug->leave_sub;
}
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(fromacc typ toacc));

  #$self->number_columns([ grep { !m/^(?:month|year|quarter)$/ } @columns ]);

  my %column_defs          = (
    fromacc                  => { text => 'Quelle', align => 'left'   },
    typ                      => { text => 'Typ'   , align => 'right'   },
    toacc                    => { text => 'Ziel'  , align => 'left'   },
  );

  #$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                 => 'mebil - Mapping',
#    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;
}
sub list_data {
	my ($self)           = @_;

#  my @visible_columns  = $self->report->get_visible_columns;
#  my @type_columns     = @{ $self->types };
#  my @non_type_columns = grep { my $c = $_; none { $c eq $_ } @type_columns } @visible_columns;

	foreach my $mapping (@{ $self->{data} }) {
		my %data = (
		    fromacc                  => { data => $mapping->{fromacc} },
    		typ                      => { data => $mapping->{typ}     },
    		toacc                    => { data => $mapping->{toacc}   },
		);
		$self->report->add_data(\%data);
	}
 
	return $self->report->generate_with_headers;
}

1;