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