Mebil ready
"; $::lxdebug->leave_sub; } +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 $mapping = new SL::mebil::Mapping($::form, $::form->get_standard_dbh); + $self->{data} = $mapping->get_mapping(); + + $self->prepare_report; + $self->list_data; + + $::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(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;