From bfb17b7159a61d45bf41897860e8813331f528ff Mon Sep 17 00:00:00 2001 From: Michael Wagner Date: Sun, 14 Nov 2021 00:30:13 +0100 Subject: [PATCH] epic-ts --- SL/Controller/Mebil.pm | 57 ++++++++++++++++++---------------- sql/mebil.sql | 70 ++++++++++++++++++++++++++++++++++++++---- 2 files changed, 95 insertions(+), 32 deletions(-) diff --git a/SL/Controller/Mebil.pm b/SL/Controller/Mebil.pm index fa80bcac8..ba50427b3 100644 --- a/SL/Controller/Mebil.pm +++ b/SL/Controller/Mebil.pm @@ -73,7 +73,7 @@ sub action_calcmap { $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') + WHERE (ac.transdate <= '30.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}; @@ -81,8 +81,14 @@ sub action_calcmap { } elsif ($mapping->{typ} eq 'S') { # process Soll - $fromacc{$mapping->{fromacc}} = 200.; - $toacc{$mapping->{toacc}} = 200.; + $sql = "SELECT SUM(ac.amount)* -1 AS saldo + FROM acc_trans ac + JOIN chart c ON (c.id = ac.chart_id) + WHERE (ac.transdate <= '30.12.2020') + AND (c.accno = '$mapping->{fromacc}') "; + my @result = SL::DBUtils::selectfirst_array_query($::form, $::form->get_standard_dbh, $sql); + $fromacc{$mapping->{fromacc}} = $result[0]; + $toacc{$mapping->{toacc}} += $result[0]; } elsif ($mapping->{typ} eq 'V') { # process Vorjahr @@ -95,12 +101,15 @@ sub action_calcmap { $toacc{$mapping->{toacc}} = 400.; } elsif ($mapping->{typ} eq 'X') { - # process Soll - $fromacc{$mapping->{fromacc}} += $toacc{$mapping->{fromacc}}; + # add to other account $toacc{$mapping->{toacc}} += $toacc{$mapping->{fromacc}}; } + elsif ($mapping->{typ} eq 'Y') { + # substract from other account + $toacc{$mapping->{toacc}} -= $toacc{$mapping->{fromacc}}; + } else { - die "Error: Invalid mapping type\n"; + die "Error: Invalid mapping type: $mapping->{typ}\n"; } } @@ -120,37 +129,33 @@ sub action_calcmap { 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', + title => 'mebil - Mapping: Werte für 2020', # 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)) { + $self->report->set_options_from_form; + $self->add_data_sorted(\%fromacc); + $self->add_data_sorted(\%toacc); + + return $self->report->generate_with_headers; + $::lxdebug->leave_sub; +} +sub add_data_sorted { + my $self = shift; + my $data = shift; # hash reference + + foreach my $key (sort keys %$data) { my %data = ( - name => { data => $a }, - amount => { data => $v }, + name => { data => $key }, + amount => { data => $::form->format_amount(\%::myconfig, $data->{$key}, 2) }, ); $self->report->add_data(\%data); + } - - return $self->report->generate_with_headers; - $::lxdebug->leave_sub; } - sub prepare_report { my ($self) = @_; diff --git a/sql/mebil.sql b/sql/mebil.sql index 0d8a101aa..cb9229dee 100644 --- a/sql/mebil.sql +++ b/sql/mebil.sql @@ -10,10 +10,68 @@ CREATE TABLE mebil_mapping ( toacc VARCHAR(200) NOT NULL ); 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'); +VALUES + (10,'S','0483','bs.ass.fixAss.tan.machinery.gwgsammelposten'), + (10,'S','0484','bs.ass.fixAss.tan.machinery.gwgsammelposten'), + (20,'X','bs.ass.fixAss.tan.machinery.gwgsammelposten','bs.ass.fixAss.tan.machinery'), + (21,'X','bs.ass.fixAss.tan.machinery','bs.ass.fixAss.tan'), + (22,'X','bs.ass.fixAss.tan','bs.ass.fixAss'), + (50,'X','bs.ass.fixAss','bs.ass'), + (10,'S','1200','bs.ass.currAss.cashEquiv.bank'), + (20,'X','bs.ass.currAss.cashEquiv.bank','bs.ass.currAss.cashEquiv'), + (21,'X','bs.ass.currAss.cashEquiv.bank','bs.ass.currAss'), + + (10,'S','3980','bs.ass.currAss.inventory.finishedAndMerch.merchandise.new'), + (20,'X','bs.ass.currAss.inventory.finishedAndMerch.merchandise.new','bs.ass.currAss.inventory.finishedAndMerch.merchandise'), + (21,'X','bs.ass.currAss.inventory.finishedAndMerch.merchandise','bs.ass.currAss.inventory.finishedAndMerch'), + (22,'X','bs.ass.currAss.inventory.finishedAndMerch','bs.ass.currAss.inventory'), + (23,'X','bs.ass.currAss.inventory','bs.ass.currAss'), + + (10,'S','1400','bs.ass.currAss.receiv.trade'), + (20,'X','bs.ass.currAss.receiv.trade','bs.ass.currAss.receiv'), + + (10,'S','1575','bs.ass.currAss.receiv.other.vat'), + (10,'S','1576','bs.ass.currAss.receiv.other.vat'), + (10,'S','1579','bs.ass.currAss.receiv.other.vat'), + (10,'S','1780','bs.ass.currAss.receiv.other.vat'), + (20,'X','bs.ass.currAss.receiv.other.vat','bs.ass.currAss.receiv.other'), + (21,'X','bs.ass.currAss.receiv.other','bs.ass.currAss.receiv'), + (30,'X','bs.ass.currAss.receiv','bs.ass.currAss'), + + (50,'X','bs.ass.currAss','bs.ass'), + + (10,'H','0800','bs.eqLiab.equity.subscribed.corp'), + (20,'X', 'bs.eqLiab.equity.subscribed.corp','bs.eqLiab.equity.subscribed'), + (40,'X', 'bs.eqLiab.equity.subscribed', 'bs.eqLiab.equity'), + (10,'H','0810','bs.eqLiab.equity.capRes'), + (20,'X', 'bs.eqLiab.equity.capRes', 'bs.eqLiab.equity'), + (50,'X', 'bs.eqLiab.equity', 'bs.eqLiab'), + (10,'H','0970','bs.eqLiab.accruals.other.upTo1year'), + (20,'X', 'bs.eqLiab.accruals.other.upTo1year','bs.eqLiab.accruals.other'), + (21,'X', 'bs.eqLiab.accruals.other','bs.eqLiab.accruals'), + (50,'X', 'bs.eqLiab.accruals','bs.eqLiab'), + (10,'H','1775','bs.eqLiab.liab.other.theroffTax.vat'), + (10,'H','1776','bs.eqLiab.liab.other.theroffTax.vat'), + (20,'X', 'bs.eqLiab.liab.other.theroffTax.vat','bs.eqLiab.liab.other.theroffTax'), + (10,'H','1651','bs.eqLiab.liab.other.theroffTax.operatingTaxes'), + (10,'H','1652','bs.eqLiab.liab.other.theroffTax.operatingTaxes'), + (10,'H','1653','bs.eqLiab.liab.other.theroffTax.operatingTaxes'), + (20,'X', 'bs.eqLiab.liab.other.theroffTax.operatingTaxes','bs.eqLiab.liab.other.theroffTax'), + (21,'X', 'bs.eqLiab.liab.other.theroffTax','bs.eqLiab.liab.other'), + (10,'H','1606','bs.eqLiab.liab.other.profitPartLoans'), + (20,'X', 'bs.eqLiab.liab.other.profitPartLoans','bs.eqLiab.liab.other'), + (22,'X', 'bs.eqLiab.liab.other','bs.eqLiab.liab'), + (50,'X', 'bs.eqLiab.liab','bs.eqLiab'), + + (10,'S','4121','is.netIncome.regular.operatingTC.staff.salaries.managerPartner'), + (20,'X' ,'is.netIncome.regular.operatingTC.staff.salaries.managerPartner','is.netIncome.regular.operatingTC.staff.salaries'), + (21,'X' ,'is.netIncome.regular.operatingTC.staff.salaries','ismi.netIncome.staff'), + (50,'Y' ,'ismi.netIncome.staff','ismi.netIncome'), + (10,'S','3960','is.netIncome.regular.operatingTC.grossTradingProfit.materialServices.material.purchased.generalRateVAT'), + (20,'X', 'is.netIncome.regular.operatingTC.grossTradingProfit.materialServices.material.purchased.generalRateVAT','is.netIncome.regular.operatingTC.grossTradingProfit.materialServices.material.purchased'), + (21,'X', 'is.netIncome.regular.operatingTC.grossTradingProfit.materialServices.material.purchased','is.netIncome.regular.operatingTC.grossTradingProfit.materialServices.material'), + (22,'X', 'is.netIncome.regular.operatingTC.grossTradingProfit.materialServices.material','ismi.netIncome.materialServices'), + (50,'Y', 'ismi.netIncome.materialServices','ismi.netIncome') + + ; -- 2.20.1