1 package SL::Controller::Mebil;
5 use parent qw(SL::Controller::Base);
7 #use SL::Controller::Helper::ReportGenerator;
8 use SL::ReportGenerator;
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) ],
20 $::lxdebug->enter_sub;
21 $::lxdebug->message(5, 'controller=mebil/action=map');
24 $::form->header(no_layout => 1);
26 print "<h1>Mebil running</h1>";
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";
33 my @r = SL::DBUtils::selectall_hashref_query($::form, $::form->get_standard_dbh, $sql);
34 print ref($r[1])||"SCALAR";
37 while (my($k,$v) = each(%$fst)) {
38 print $k, " : ", $v, "</br>\n";
44 print "<p>Mebil ready</p>";
45 $::lxdebug->leave_sub;
49 $::lxdebug->enter_sub;
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);
55 $self->prepare_report;
58 $::lxdebug->leave_sub;
62 $::lxdebug->enter_sub;
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);
70 foreach my $mapping (@{ $self->{data} }) {
71 if ($mapping->{typ} eq 'H') {
73 $sql = "SELECT SUM(ac.amount) AS saldo
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};
82 elsif ($mapping->{typ} eq 'S') {
84 $sql = "SELECT SUM(ac.amount)* -1 AS saldo
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];
93 elsif ($mapping->{typ} eq 'V') {
95 $fromacc{$mapping->{fromacc}} = 300.;
96 $toacc{$mapping->{toacc}} = 300.;
98 elsif ($mapping->{typ} eq 'A') {
100 $fromacc{$mapping->{fromacc}} = 400.;
101 $toacc{$mapping->{toacc}} = 400.;
103 elsif ($mapping->{typ} eq 'X') {
104 # add to other account
105 $toacc{$mapping->{toacc}} += $toacc{$mapping->{fromacc}};
107 elsif ($mapping->{typ} eq 'Y') {
108 # substract from other account
109 $toacc{$mapping->{toacc}} -= $toacc{$mapping->{fromacc}};
112 die "Error: Invalid mapping type: $mapping->{typ}\n";
116 $self->report(SL::ReportGenerator->new(\%::myconfig, $::form));
118 my @columns = (qw(name amount));
121 name => { text => 'Konto', align => 'left' },
122 amount => { text => 'Betrag' , align => 'right' },
125 #$column_defs{$_}->{align} = 'right' for @columns;
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,
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);
143 return $self->report->generate_with_headers;
144 $::lxdebug->leave_sub;
146 sub add_data_sorted {
148 my $data = shift; # hash reference
150 foreach my $key (sort keys %$data) {
152 name => { data => $key },
153 amount => { data => $::form->format_amount(\%::myconfig, $data->{$key}, 2) },
155 $self->report->add_data(\%data);
162 $self->report(SL::ReportGenerator->new(\%::myconfig, $::form));
164 my @columns = (qw(fromacc typ toacc));
166 #$self->number_columns([ grep { !m/^(?:month|year|quarter)$/ } @columns ]);
169 fromacc => { text => 'Quelle', align => 'left' },
170 typ => { text => 'Typ' , align => 'right' },
171 toacc => { text => 'Ziel' , align => 'left' },
174 #$column_defs{$_}->{align} = 'right' for @columns;
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,
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;
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;
197 foreach my $mapping (@{ $self->{data} }) {
199 fromacc => { data => $mapping->{fromacc} },
200 typ => { data => $mapping->{typ} },
201 toacc => { data => $mapping->{toacc} },
203 $self->report->add_data(\%data);
206 return $self->report->generate_with_headers;