1 package SL::Controller::Mebil;
5 use parent qw(SL::Controller::Base);
7 #use SL::Controller::Helper::ReportGenerator;
8 use SL::ReportGenerator;
11 use SL::Locale::String; # t8
16 use SL::mebil::Mapping;
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) ],
24 $::lxdebug->enter_sub;
25 $::lxdebug->message(5, 'controller=mebil/action=map');
28 $::form->header(no_layout => 1);
30 print "<h1>Mebil running</h1>";
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";
37 my @r = SL::DBUtils::selectall_hashref_query($::form, $::form->get_standard_dbh, $sql);
38 print ref($r[1])||"SCALAR";
41 while (my($k,$v) = each(%$fst)) {
42 print $k, " : ", $v, "</br>\n";
48 print "<p>Mebil ready</p>";
49 $::lxdebug->leave_sub;
53 $::lxdebug->enter_sub;
56 # call model -> diese Zeile ist fraglich, war ein Konflikt
57 #$self->{data} = DB::MebilMapping::getMappings($::form->get_standard_dbh);
59 $::form->{title} = $::locale->text('Mebil Map');
61 my $mapping = new SL::mebil::Mapping($::form, $::form->get_standard_dbh);
62 $self->{data} = $mapping->get_mapping();
64 $self->prepare_report;
67 $::lxdebug->leave_sub;
80 $::lxdebug->message(5, "part=$part");
85 $::lxdebug->enter_sub;
89 $::lxdebug->message(5, "rule=$rule");
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
104 my $end = "AND (ac.transdate <= '31.12.$year')";
108 (my $part,$rule) = extract_rule($rule);
109 $::lxdebug->message(5, "part=$part");
111 $part =~ /(.*)=(.*)/;
115 elsif ($1 eq "VALUES") {
116 if ($2 eq "positive") {
117 $values = "AND (ac.amount > 0)";
119 elsif ($2 eq "negative") {
120 $values = "AND (ac.amount < 0)";
124 die "invalid rule part: $part";
127 elsif ($1 eq "INVERT") {
130 elsif ($1 eq "START") {
131 $start = "AND (ac.transdate >= '01.01.$2')";
132 $start =~ s/YEAR/$year/;
135 elsif ($1 eq "END") {
136 $end = "AND (ac.transdate <= '31.12.$2')";
137 $end =~ s/YEAR/$year/;
141 die "invalid rule part: $part";
143 ($part,$rule) = extract_rule($rule);
146 $::lxdebug->leave_sub;
147 return "SELECT SUM(ac.amount) $invert AS saldo
149 JOIN chart c ON (c.id = ac.chart_id)
150 WHERE (c.accno = '$acc') $values $start $end";
154 $::lxdebug->enter_sub;
157 $self->year($::form->{year} || DateTime->today->year - 1);
159 $::form->{title} = $::locale->text('Mebil Map');
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);
166 my $year = $self->year;
167 foreach my $mapping (@{ $self->{data} }) {
168 if ($mapping->{typ} eq 'H') {
170 $sql = "SELECT SUM(ac.amount) AS saldo
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};
179 elsif ($mapping->{typ} eq 'S') {
181 $sql = "SELECT SUM(ac.amount)* -1 AS saldo
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];
190 # elsif ($mapping->{typ} eq 'V') {
192 # $fromacc{$mapping->{fromacc}} = 300.;
193 # $toacc{$mapping->{toacc}} = 300.;
195 # elsif ($mapping->{typ} eq 'A') {
197 # $fromacc{$mapping->{fromacc}} = 400.;
198 # $toacc{$mapping->{toacc}} = 400.;
200 elsif ($mapping->{typ} eq 'X') {
201 # add to other account
202 $toacc{$mapping->{toacc}} += $toacc{$mapping->{fromacc}};
204 elsif ($mapping->{typ} eq 'Y') {
205 # substract from other account
206 $toacc{$mapping->{toacc}} -= $toacc{$mapping->{fromacc}};
208 elsif ($mapping->{typ} eq 'R') {
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];
215 elsif ($mapping->{typ} eq 'C') {
220 die "Error: Invalid mapping type: $mapping->{typ}\n";
224 $self->report(SL::ReportGenerator->new(\%::myconfig, $::form));
226 my @columns = (qw(name amount));
229 name => { text => 'Konto', align => 'left' },
230 amount => { text => 'Betrag' , align => 'right' },
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,
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);
249 $::lxdebug->leave_sub;
250 return $self->report->generate_with_headers;
252 sub add_data_sorted {
254 my $data = shift; # hash reference
256 foreach my $key (sort keys %$data) {
258 name => { data => $key },
259 amount => { data => $::form->format_amount(\%::myconfig, $data->{$key}, 2) },
261 $self->report->add_data(\%data);
268 $self->report(SL::ReportGenerator->new(\%::myconfig, $::form));
270 my @columns = (qw(fromacc typ toacc));
272 #$self->number_columns([ grep { !m/^(?:month|year|quarter)$/ } @columns ]);
275 fromacc => { text => 'Quelle', align => 'left' },
276 typ => { text => 'Typ' , align => 'right' },
277 toacc => { text => 'Ziel' , align => 'left' },
280 #$column_defs{$_}->{align} = 'right' for @columns;
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,
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;
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;
303 foreach my $mapping (@{ $self->{data} }) {
305 fromacc => { data => $mapping->{fromacc} },
306 typ => { data => $mapping->{typ} },
307 toacc => { data => $mapping->{toacc} },
309 $self->report->add_data(\%data);
312 return $self->report->generate_with_headers;