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 Data::Dumper;
  12 #use SL::ClientJS;
  13
  14 use Rose::Object::MakeMethods::Generic (
  15   scalar                  => [ qw(report number_columns year current_year objects subtotals_per_quarter salesman_id) ],
  16   'scalar --get_set_init' => [ qw(employees types data) ],
  17 );
  18
  19 sub action_map {
  20         $::lxdebug->enter_sub;
  21         $::lxdebug->message(5, 'controller=mebil/action=map');
  22         my ($self) = @_;
  23
  24         $::form->header(no_layout => 1);
  25
  26         print "<h1>Mebil running</h1>";
  27
  28         my $sql = "SELECT  chart_id,xbrl_tag from mebil_mapping";
  29         my $result = SL::DBUtils::do_query($::form, $::form->get_standard_dbh, $sql);
  30         $::lxdebug->message(5, "result= $result");
  31         print "$result<br>\n";
  32
  33         my @r = SL::DBUtils::selectall_hashref_query($::form, $::form->get_standard_dbh, $sql);
  34         print ref($r[1])||"SCALAR";
  35         print "<br>";
  36         my $fst = $r[1];
  37         while (my($k,$v) = each(%$fst)) {
  38                 print $k, " : ", $v, "</br>\n";
  39         }
  40         print "<br>";
  41         print scalar @r;
  42         print "<br>";
  43         print @r;
  44         print "<p>Mebil ready</p>";
  45         $::lxdebug->leave_sub;
  46 }
  47
  48 sub action_showmap {
  49         $::lxdebug->enter_sub;
  50         my ($self) = @_;
  51
  52         my $sql = "SELECT  fromacc,typ,toacc from mebil_mapping order by ordering";
  53         $self->{data} = SL::DBUtils::selectall_hashref_query($::form, $::form->get_standard_dbh, $sql);
  54
  55         $self->prepare_report;
  56         $self->list_data;
  57
  58         $::lxdebug->leave_sub;
  59 }
  60
  61 sub action_calcmap {
  62         $::lxdebug->enter_sub;
  63         my ($self) = @_;
  64
  65         my $sql = "SELECT  fromacc,typ,toacc from mebil_mapping order by ordering";
  66         $self->{data} = SL::DBUtils::selectall_hashref_query($::form, $::form->get_standard_dbh, $sql);
  67
  68         my %fromacc = ();
  69         my %toacc   = ();
  70         foreach my $mapping (@{ $self->{data} }) {
  71                 if ($mapping->{typ} eq 'H') {
  72                         # process Haben
  73                         $sql = "SELECT SUM(ac.amount) AS saldo
  74                                                 FROM acc_trans ac
  75                                                 JOIN chart c ON (c.id = ac.chart_id)
  76                                                 WHERE (ac.transdate <= '30.12.2020')
  77                                                 AND (c.accno = '$mapping->{fromacc}') ";
  78                         my $result = SL::DBUtils::selectall_hashref_query($::form, $::form->get_standard_dbh, $sql);
  79                         $fromacc{$mapping->{fromacc}} = $result->[0]->{saldo};
  80                         $toacc{$mapping->{toacc}} += $result->[0]->{saldo};
  81                 }
  82                 elsif ($mapping->{typ} eq 'S') {
  83                         # process Soll
  84                         $sql = "SELECT SUM(ac.amount)* -1 AS saldo
  85                                                 FROM acc_trans ac
  86                                                 JOIN chart c ON (c.id = ac.chart_id)
  87                                                 WHERE (ac.transdate <= '30.12.2020')
  88                                                 AND (c.accno = '$mapping->{fromacc}') ";
  89                         my @result = SL::DBUtils::selectfirst_array_query($::form, $::form->get_standard_dbh, $sql);
  90                         $fromacc{$mapping->{fromacc}} = $result[0];
  91                         $toacc{$mapping->{toacc}} += $result[0];
  92                 }
  93                 elsif ($mapping->{typ} eq 'V') {
  94                         # process Vorjahr
  95                         $fromacc{$mapping->{fromacc}} = 300.;
  96                         $toacc{$mapping->{toacc}} = 300.;
  97                 }
  98                 elsif ($mapping->{typ} eq 'A') {
  99                         # process Aktjahr
 100                         $fromacc{$mapping->{fromacc}} = 400.;
 101                         $toacc{$mapping->{toacc}} = 400.;
 102                 }
 103                 elsif ($mapping->{typ} eq 'X') {
 104                         # add to other account
 105                         $toacc{$mapping->{toacc}} += $toacc{$mapping->{fromacc}};
 106                 }
 107                 elsif ($mapping->{typ} eq 'Y') {
 108                         # substract from other account
 109                         $toacc{$mapping->{toacc}} -= $toacc{$mapping->{fromacc}};
 110                 }
 111                 else {
 112                         die "Error: Invalid mapping type: $mapping->{typ}\n";
 113                 }
 114         }
 115
 116         $self->report(SL::ReportGenerator->new(\%::myconfig, $::form));
 117
 118         my @columns = (qw(name amount));
 119
 120         my %column_defs          = (
 121         name                        => { text => 'Konto', align => 'left'   },
 122         amount                      => { text => 'Betrag'   , align => 'right'   },
 123         );
 124
 125   #$column_defs{$_}->{align} = 'right' for @columns;
 126
 127   $self->report->set_options(
 128     std_column_visibility => 1,
 129     controller_class      => 'Mebil',
 130     output_format         => 'HTML',
 131  #   raw_top_info_text     => $self->render('financial_overview/report_top', { output => 0 }, YEARS_TO_LIST => [ reverse(($self->current_year - 10)..($self->current_year + 5)) ]),
 132     title                 => 'mebil - Mapping: Werte für 2020',
 133 #    allow_pdf_export      => 1,
 134 #    allow_csv_export      => 1,
 135   );
 136   $self->report->set_columns(%column_defs);
 137   $self->report->set_column_order(@columns);
 138 #  $self->report->set_export_options(qw(list year subtotals_per_quarter salesman_id));
 139         $self->report->set_options_from_form;
 140         $self->add_data_sorted(\%fromacc);
 141         $self->add_data_sorted(\%toacc);
 142
 143         return $self->report->generate_with_headers;
 144         $::lxdebug->leave_sub;
 145 }
 146 sub add_data_sorted {
 147         my $self = shift;
 148         my $data = shift; # hash reference
 149
 150         foreach my $key (sort keys %$data) {
 151                 my %data = (
 152                     name                     => { data => $key },
 153                 amount                   => { data => $::form->format_amount(\%::myconfig, $data->{$key}, 2) },
 154                 );
 155                 $self->report->add_data(\%data);
 156
 157         }
 158 }
 159 sub prepare_report {
 160   my ($self)      = @_;
 161
 162   $self->report(SL::ReportGenerator->new(\%::myconfig, $::form));
 163
 164   my @columns = (qw(fromacc typ toacc));
 165
 166   #$self->number_columns([ grep { !m/^(?:month|year|quarter)$/ } @columns ]);
 167
 168   my %column_defs          = (
 169     fromacc                  => { text => 'Quelle', align => 'left'   },
 170     typ                      => { text => 'Typ'   , align => 'right'   },
 171     toacc                    => { text => 'Ziel'  , align => 'left'   },
 172   );
 173
 174   #$column_defs{$_}->{align} = 'right' for @columns;
 175
 176   $self->report->set_options(
 177     std_column_visibility => 1,
 178     controller_class      => 'Mebil',
 179     output_format         => 'HTML',
 180  #   raw_top_info_text     => $self->render('financial_overview/report_top', { output => 0 }, YEARS_TO_LIST => [ reverse(($self->current_year - 10)..($self->current_year + 5)) ]),
 181     title                 => 'mebil - Mapping',
 182 #    allow_pdf_export      => 1,
 183 #    allow_csv_export      => 1,
 184   );
 185   $self->report->set_columns(%column_defs);
 186   $self->report->set_column_order(@columns);
 187 #  $self->report->set_export_options(qw(list year subtotals_per_quarter salesman_id));
 188   $self->report->set_options_from_form;
 189 }
 190 sub list_data {
 191         my ($self)           = @_;
 192
 193 #  my @visible_columns  = $self->report->get_visible_columns;
 194 #  my @type_columns     = @{ $self->types };
 195 #  my @non_type_columns = grep { my $c = $_; none { $c eq $_ } @type_columns } @visible_columns;
 196
 197         foreach my $mapping (@{ $self->{data} }) {
 198                 my %data = (
 199                     fromacc                  => { data => $mapping->{fromacc} },
 200                 typ                      => { data => $mapping->{typ}     },
 201                 toacc                    => { data => $mapping->{toacc}   },
 202                 );
 203                 $self->report->add_data(\%data);
 204         }
 205
 206         return $self->report->generate_with_headers;
 207 }
 208
 209 1;