SL/Controller/Mebil.pm

   1 package SL::Controller::Mebil;
   2
   3 use strict;
   4
   5 use parent qw(SL::Controller::Base);
   6
   7 #use SL::Controller::Helper::ReportGenerator;
   8 use SL::ReportGenerator;
   9 use SL::DBUtils;
  10
  11 use SL::Locale::String;  # t8
  12
  13 #use Data::Dumper;
  14 #use SL::ClientJS;
  15
  16 use SL::mebil::Mapping;
  17
  18 use Rose::Object::MakeMethods::Generic (
  19   scalar                  => [ qw(report number_columns year current_year objects subtotals_per_quarter salesman_id) ],
  20   'scalar --get_set_init' => [ qw(employees types data) ],
  21 );
  22
  23 sub action_map {
  24         $::lxdebug->enter_sub;
  25         $::lxdebug->message(5, 'controller=mebil/action=map');
  26         my ($self) = @_;
  27
  28         $::form->header(no_layout => 1);
  29
  30         print "<h1>Mebil running</h1>";
  31
  32         my $sql = "SELECT  chart_id,xbrl_tag from mebil_mapping";
  33         my $result = SL::DBUtils::do_query($::form, $::form->get_standard_dbh, $sql);
  34         $::lxdebug->message(5, "result= $result");
  35         print "$result<br>\n";
  36
  37         my @r = SL::DBUtils::selectall_hashref_query($::form, $::form->get_standard_dbh, $sql);
  38         print ref($r[1])||"SCALAR";
  39         print "<br>";
  40         my $fst = $r[1];
  41         while (my($k,$v) = each(%$fst)) {
  42                 print $k, " : ", $v, "</br>\n";
  43         }
  44         print "<br>";
  45         print scalar @r;
  46         print "<br>";
  47         print @r;
  48         print "<p>Mebil ready</p>";
  49         $::lxdebug->leave_sub;
  50 }
  51
  52 sub action_showmap {
  53         $::lxdebug->enter_sub;
  54         my ($self) = @_;
  55
  56         # call model -> diese Zeile ist fraglich, war ein Konflikt
  57         #$self->{data} = DB::MebilMapping::getMappings($::form->get_standard_dbh);
  58
  59         $::form->{title} = $::locale->text('Mebil Map');
  60
  61         my $mapping = new SL::mebil::Mapping($::form, $::form->get_standard_dbh);
  62         $self->{data} = $mapping->get_mapping();
  63
  64         $self->prepare_report;
  65         $self->list_data;
  66
  67         $::lxdebug->leave_sub;
  68 }
  69
  70 sub extract_rule {
  71         my $rule = shift;
  72         my $part = $rule;
  73         $part =~ s/:.*//;
  74         if ($rule =~ /:/) {
  75                 $rule =~ s/[^:]*://;
  76         }
  77         else {
  78                 $rule = "";
  79         }
  80         $::lxdebug->message(5, "part=$part");
  81         return ($part,$rule);
  82 }
  83
  84 sub rule2sql {
  85         $::lxdebug->enter_sub;
  86
  87         my $rule = shift;
  88         my $year = shift;
  89         $::lxdebug->message(5, "rule=$rule");
  90
  91         # a rule consists of key=value pairs seperated by colon. Possible keys are:
  92         # - ACC: account number
  93         # - VALUES=positive|negative: only positive or negative values are selected
  94         #     negative implies INVERT=true
  95         # - INVERT=true: Result is multiplied by -1
  96         # - START=YEAR: start year. Absolute ('2020') or relative ('YEAR/PY')
  97         # - END=YEAR: end year. Default: $year
  98
  99         # supply defaults:
 100         my $acc = "0000";
 101         my $values = "";
 102         my $invert = "";
 103         my $start = "";
 104         my $end = "AND (ac.transdate <= '31.12.$year')";
 105         my $py = $year - 1;
 106
 107         # parse rule
 108         (my $part,$rule) = extract_rule($rule);
 109         $::lxdebug->message(5, "part=$part");
 110         while ($part) {
 111                 $part =~ /(.*)=(.*)/;
 112                 if ($1 eq "ACC") {
 113                         $acc = $2;
 114                 }
 115                 elsif ($1 eq "VALUES") {
 116                         if ($2 eq "positive") {
 117                                 $values = "AND (ac.amount > 0)";
 118                         }
 119                         elsif ($2 eq "negative") {
 120                                 $values = "AND (ac.amount < 0)";
 121                                 $invert = "* (-1)";
 122                         }
 123                         else {
 124                                 die "invalid rule part: $part";
 125                         }
 126                 }
 127                 elsif ($1 eq "INVERT") {
 128                         $invert = "* (-1)";
 129                 }
 130                 elsif ($1 eq "START") {
 131                         $start = "AND (ac.transdate >= '01.01.$2')";
 132                         $start =~ s/YEAR/$year/;
 133                         $start =~ s/PY/$py/;
 134                 }
 135                 elsif ($1 eq "END") {
 136                         $end = "AND (ac.transdate <= '31.12.$2')";
 137                         $end =~ s/YEAR/$year/;
 138                         $end =~ s/PY/$py/;
 139                 }
 140                 else {
 141                         die "invalid rule part: $part";
 142                 }
 143                 ($part,$rule) = extract_rule($rule);
 144         }
 145
 146         $::lxdebug->leave_sub;
 147         return "SELECT SUM(ac.amount) $invert AS saldo
 148                                                 FROM acc_trans ac
 149                                                 JOIN chart c ON (c.id = ac.chart_id)
 150                                                 WHERE (c.accno = '$acc') $values $start $end";
 151 }
 152
 153 sub action_calcmap {
 154         $::lxdebug->enter_sub;
 155         my ($self) = @_;
 156
 157         $self->year($::form->{year} || DateTime->today->year - 1);
 158
 159         $::form->{title} = $::locale->text('Mebil Map');
 160
 161         my $sql = "SELECT  fromacc,typ,toacc from mebil_mapping order by ordering";
 162         $self->{data} = SL::DBUtils::selectall_hashref_query($::form, $::form->get_standard_dbh, $sql);
 163
 164         my %fromacc = ();
 165         my %toacc   = ();
 166         my $year = $self->year;
 167         foreach my $mapping (@{ $self->{data} }) {
 168                 if ($mapping->{typ} eq 'H') {
 169                         # process Haben
 170                         $sql = "SELECT SUM(ac.amount) AS saldo
 171                                                 FROM acc_trans ac
 172                                                 JOIN chart c ON (c.id = ac.chart_id)
 173                                                 WHERE (ac.transdate <= '31.12.$year')
 174                                                 AND (c.accno = '$mapping->{fromacc}') ";
 175                         my $result = SL::DBUtils::selectall_hashref_query($::form, $::form->get_standard_dbh, $sql);
 176                         $fromacc{$mapping->{fromacc}} = $result->[0]->{saldo};
 177                         $toacc{$mapping->{toacc}} += $result->[0]->{saldo};
 178                 }
 179                 elsif ($mapping->{typ} eq 'S') {
 180                         # process Soll
 181                         $sql = "SELECT SUM(ac.amount)* -1 AS saldo
 182                                                 FROM acc_trans ac
 183                                                 JOIN chart c ON (c.id = ac.chart_id)
 184                                                 WHERE (ac.transdate <= '31.12.$year')
 185                                                 AND (c.accno = '$mapping->{fromacc}') ";
 186                         my @result = SL::DBUtils::selectfirst_array_query($::form, $::form->get_standard_dbh, $sql);
 187                         $fromacc{$mapping->{fromacc}} = $result[0];
 188                         $toacc{$mapping->{toacc}} += $result[0];
 189                 }
 190 #               elsif ($mapping->{typ} eq 'V') {
 191 #                       # process Vorjahr
 192 #                       $fromacc{$mapping->{fromacc}} = 300.;
 193 #                       $toacc{$mapping->{toacc}} = 300.;
 194 #               }
 195 #               elsif ($mapping->{typ} eq 'A') {
 196 #                       # process Aktjahr
 197 #                       $fromacc{$mapping->{fromacc}} = 400.;
 198 #                       $toacc{$mapping->{toacc}} = 400.;
 199 #               }
 200                 elsif ($mapping->{typ} eq 'X') {
 201                         # add to other account
 202                         $toacc{$mapping->{toacc}} += $toacc{$mapping->{fromacc}};
 203                 }
 204                 elsif ($mapping->{typ} eq 'Y') {
 205                         # substract from other account
 206                         $toacc{$mapping->{toacc}} -= $toacc{$mapping->{fromacc}};
 207                 }
 208                 elsif ($mapping->{typ} eq 'R') {
 209                         # rule based
 210                         my $sql = rule2sql($mapping->{fromacc}, $year);
 211                         $::lxdebug->message(5, "sql=$sql");
 212                         my @result = SL::DBUtils::selectfirst_array_query($::form, $::form->get_standard_dbh, $sql);
 213                         $toacc{$mapping->{toacc}} += $result[0];
 214                 }
 215                 elsif ($mapping->{typ} eq 'C') {
 216                         # constant value
 217                         ; # do nothing here
 218                 }
 219                 else {
 220                         die "Error: Invalid mapping type: $mapping->{typ}\n";
 221                 }
 222         }
 223
 224         $self->report(SL::ReportGenerator->new(\%::myconfig, $::form));
 225
 226         my @columns = (qw(name amount));
 227
 228         my %column_defs          = (
 229         name                        => { text => 'Konto', align => 'left'   },
 230         amount                      => { text => 'Betrag'   , align => 'right'   },
 231         );
 232
 233   $self->report->set_options(
 234     std_column_visibility => 1,
 235     controller_class      => 'Mebil',
 236     output_format         => 'HTML',
 237         raw_top_info_text     => $self->render('mebil/report_top', { output => 0 }, YEARS_TO_LIST => [ reverse(($self->year - 10)..($self->year + 5)) ]),
 238     title                 => t8('mebil - Mapping: values for #1', $self->year),
 239 #    allow_pdf_export      => 1,
 240 #    allow_csv_export      => 1,
 241   );
 242   $self->report->set_columns(%column_defs);
 243   $self->report->set_column_order(@columns);
 244 #  $self->report->set_export_options(qw(list year subtotals_per_quarter salesman_id));
 245         $self->report->set_options_from_form;
 246         $self->add_data_sorted(\%fromacc);
 247         $self->add_data_sorted(\%toacc);
 248
 249         $::lxdebug->leave_sub;
 250         return $self->report->generate_with_headers;
 251 }
 252 sub add_data_sorted {
 253         my $self = shift;
 254         my $data = shift; # hash reference
 255
 256         foreach my $key (sort keys %$data) {
 257                 my %data = (
 258                     name                     => { data => $key },
 259                 amount                   => { data => $::form->format_amount(\%::myconfig, $data->{$key}, 2) },
 260                 );
 261                 $self->report->add_data(\%data);
 262
 263         }
 264 }
 265 sub prepare_report {
 266   my ($self)      = @_;
 267
 268   $self->report(SL::ReportGenerator->new(\%::myconfig, $::form));
 269
 270   my @columns = (qw(fromacc typ toacc));
 271
 272   #$self->number_columns([ grep { !m/^(?:month|year|quarter)$/ } @columns ]);
 273
 274   my %column_defs          = (
 275     fromacc                  => { text => 'Quelle', align => 'left'   },
 276     typ                      => { text => 'Typ'   , align => 'right'   },
 277     toacc                    => { text => 'Ziel'  , align => 'left'   },
 278   );
 279
 280   #$column_defs{$_}->{align} = 'right' for @columns;
 281
 282   $self->report->set_options(
 283     std_column_visibility => 1,
 284     controller_class      => 'Mebil',
 285     output_format         => 'HTML',
 286  #   raw_top_info_text     => $self->render('financial_overview/report_top', { output => 0 }, YEARS_TO_LIST => [ reverse(($self->current_year - 10)..($self->current_year + 5)) ]),
 287     title                 => 'mebil - Mapping',
 288 #    allow_pdf_export      => 1,
 289 #    allow_csv_export      => 1,
 290   );
 291   $self->report->set_columns(%column_defs);
 292   $self->report->set_column_order(@columns);
 293 #  $self->report->set_export_options(qw(list year subtotals_per_quarter salesman_id));
 294   $self->report->set_options_from_form;
 295 }
 296 sub list_data {
 297         my ($self)           = @_;
 298
 299 #  my @visible_columns  = $self->report->get_visible_columns;
 300 #  my @type_columns     = @{ $self->types };
 301 #  my @non_type_columns = grep { my $c = $_; none { $c eq $_ } @type_columns } @visible_columns;
 302
 303         foreach my $mapping (@{ $self->{data} }) {
 304                 my %data = (
 305                     fromacc                  => { data => $mapping->{fromacc} },
 306                 typ                      => { data => $mapping->{typ}     },
 307                 toacc                    => { data => $mapping->{toacc}   },
 308                 );
 309                 $self->report->add_data(\%data);
 310         }
 311
 312         return $self->report->generate_with_headers;
 313 }
 314
 315 1;