From 91353321a860c4aa5508f7c8cfe69d4c22f33507 Mon Sep 17 00:00:00 2001 From: Michael Wagner Date: Sat, 13 Nov 2021 00:13:35 +0100 Subject: [PATCH] epic-ts --- SL/Controller/Mebil.pm | 115 +++++++++++++++++++++++++++++++++++++---- menus/user/00-erp.yaml | 9 +++- sql/mebil.sql | 16 ++++-- 3 files changed, 125 insertions(+), 15 deletions(-) diff --git a/SL/Controller/Mebil.pm b/SL/Controller/Mebil.pm index 903312ffd..fa80bcac8 100644 --- a/SL/Controller/Mebil.pm +++ b/SL/Controller/Mebil.pm @@ -49,7 +49,7 @@ sub action_showmap { $::lxdebug->enter_sub; my ($self) = @_; - my $sql = "SELECT chart_id,xbrl_tag from mebil_mapping"; + my $sql = "SELECT fromacc,typ,toacc from mebil_mapping order by ordering"; $self->{data} = SL::DBUtils::selectall_hashref_query($::form, $::form->get_standard_dbh, $sql); $self->prepare_report; @@ -58,30 +58,124 @@ sub action_showmap { $::lxdebug->leave_sub; } +sub action_calcmap { + $::lxdebug->enter_sub; + my ($self) = @_; + + my $sql = "SELECT fromacc,typ,toacc from mebil_mapping order by ordering"; + $self->{data} = SL::DBUtils::selectall_hashref_query($::form, $::form->get_standard_dbh, $sql); + + my %fromacc = (); + my %toacc = (); + foreach my $mapping (@{ $self->{data} }) { + if ($mapping->{typ} eq 'H') { + # process Haben + $sql = "SELECT SUM(ac.amount) AS saldo + FROM acc_trans ac + JOIN chart c ON (c.id = ac.chart_id) + WHERE (ac.transdate <= '31.12.2020') + AND (c.accno = '$mapping->{fromacc}') "; + my $result = SL::DBUtils::selectall_hashref_query($::form, $::form->get_standard_dbh, $sql); + $fromacc{$mapping->{fromacc}} = $result->[0]->{saldo}; + $toacc{$mapping->{toacc}} += $result->[0]->{saldo}; + } + elsif ($mapping->{typ} eq 'S') { + # process Soll + $fromacc{$mapping->{fromacc}} = 200.; + $toacc{$mapping->{toacc}} = 200.; + } + elsif ($mapping->{typ} eq 'V') { + # process Vorjahr + $fromacc{$mapping->{fromacc}} = 300.; + $toacc{$mapping->{toacc}} = 300.; + } + elsif ($mapping->{typ} eq 'A') { + # process Aktjahr + $fromacc{$mapping->{fromacc}} = 400.; + $toacc{$mapping->{toacc}} = 400.; + } + elsif ($mapping->{typ} eq 'X') { + # process Soll + $fromacc{$mapping->{fromacc}} += $toacc{$mapping->{fromacc}}; + $toacc{$mapping->{toacc}} += $toacc{$mapping->{fromacc}}; + } + else { + die "Error: Invalid mapping type\n"; + } + } + + $self->report(SL::ReportGenerator->new(\%::myconfig, $::form)); + + my @columns = (qw(name amount)); + + my %column_defs = ( + name => { text => 'Konto', align => 'left' }, + amount => { text => 'Betrag' , align => 'right' }, + ); + + #$column_defs{$_}->{align} = 'right' for @columns; + + $self->report->set_options( + std_column_visibility => 1, + controller_class => 'Mebil', + output_format => 'HTML', + # raw_top_info_text => $self->render('financial_overview/report_top', { output => 0 }, YEARS_TO_LIST => [ reverse(($self->current_year - 10)..($self->current_year + 5)) ]), + title => 'mebil - Mapping', +# allow_pdf_export => 1, +# allow_csv_export => 1, + ); + $self->report->set_columns(%column_defs); + $self->report->set_column_order(@columns); +# $self->report->set_export_options(qw(list year subtotals_per_quarter salesman_id)); + $self->report->set_options_from_form; + + while (my($a,$v) = each(%fromacc)) { + if ($a =~ /\d+/) { + # list only kivitendo accounts + my %data = ( + name => { data => $a }, + amount => { data => $v }, + ); + $self->report->add_data(\%data); + } + } + while (my($a,$v) = each(%toacc)) { + my %data = ( + name => { data => $a }, + amount => { data => $v }, + ); + $self->report->add_data(\%data); + } + + return $self->report->generate_with_headers; + $::lxdebug->leave_sub; +} + sub prepare_report { my ($self) = @_; $self->report(SL::ReportGenerator->new(\%::myconfig, $::form)); - my @columns = (qw(chart_id xbrl_tag)); + my @columns = (qw(fromacc typ toacc)); #$self->number_columns([ grep { !m/^(?:month|year|quarter)$/ } @columns ]); my %column_defs = ( - chart_id => { text => 'Kontonummer' }, - xbrl_tag => { text => 'XBRL' }, + fromacc => { text => 'Quelle', align => 'left' }, + typ => { text => 'Typ' , align => 'right' }, + toacc => { text => 'Ziel' , align => 'left' }, ); - $column_defs{$_}->{align} = 'right' for @columns; + #$column_defs{$_}->{align} = 'right' for @columns; $self->report->set_options( std_column_visibility => 1, controller_class => 'Mebil', output_format => 'HTML', # raw_top_info_text => $self->render('financial_overview/report_top', { output => 0 }, YEARS_TO_LIST => [ reverse(($self->current_year - 10)..($self->current_year + 5)) ]), - # title => t8('Financial overview for #1', $self->year), - # allow_pdf_export => 1, - # allow_csv_export => 1, + title => 'mebil - Mapping', +# allow_pdf_export => 1, +# allow_csv_export => 1, ); $self->report->set_columns(%column_defs); $self->report->set_column_order(@columns); @@ -97,8 +191,9 @@ sub list_data { foreach my $mapping (@{ $self->{data} }) { my %data = ( - chart_id => { data => $mapping->{chart_id}}, - xbrl_tag => { data => $mapping->{xbrl_tag}}, + fromacc => { data => $mapping->{fromacc} }, + typ => { data => $mapping->{typ} }, + toacc => { data => $mapping->{toacc} }, ); $self->report->add_data(\%data); } diff --git a/menus/user/00-erp.yaml b/menus/user/00-erp.yaml index 57fc96a8e..ec7672e05 100644 --- a/menus/user/00-erp.yaml +++ b/menus/user/00-erp.yaml @@ -839,11 +839,18 @@ order: 750 - parent: mebil id: mebil_showmap - name: Mebil Map + name: Map anzeigen order: 100 access: report params: action: Mebil/showmap +- parent: mebil + id: mebil_calcmap + name: Map berechnen + order: 200 + access: report + params: + action: Mebil/calcmap - id: batch_printing name: Batch Printing icon: printing diff --git a/sql/mebil.sql b/sql/mebil.sql index 8badc9b94..0d8a101aa 100644 --- a/sql/mebil.sql +++ b/sql/mebil.sql @@ -3,9 +3,17 @@ -- Lx office DROP TABLE IF EXISTS mebil_mapping; CREATE TABLE mebil_mapping ( - id integer NOT NULL, - chart_id integer NOT NULL, - xbrl_tag VARCHAR(200) NOT NULL + id SERIAL PRIMARY KEY, + ordering INTEGER NOT NULL, + typ CHAR NOT NULL, + fromacc VARCHAR(200) NOT NULL, + toacc VARCHAR(200) NOT NULL ); -INSERT INTO mebil_mapping VALUES (1,1,'acc.trans.global'); +INSERT INTO mebil_mapping (ordering,typ,fromacc,toacc) +VALUES (10,'H','8400','is.netIncome.regular.operatingTC.grossTradingProfit.totalOutput.statutoryDuties'), + (10,'S','1200','is.netIncome.regular.operatingTC.grossTradingProfit.totalOutput.netSales'), + (10,'V','1300','is.netIncome.regular.operatingTC.grossTradingProfit.totalOutput.inventoryIncrease'), + (10,'A','1400','bs.eqLiab.liab.other.thereoffSocSec'), + (20,'X','is.netIncome.regular.operatingTC.grossTradingProfit.totalOutput.statutoryDuties','is.netIncome.regular.operatingTC.grossTradingProfit.totalOutput'), + (20,'X','is.netIncome.regular.operatingTC.grossTradingProfit.totalOutput.netSales','is.netIncome.regular.operatingTC.grossTradingProfit.totalOutput'); -- 2.20.1