From a0f1eb26de9ab2772000117043cb7df3bec81254 Mon Sep 17 00:00:00 2001 From: Michael Wagner Date: Thu, 27 Jan 2022 22:13:54 +0100 Subject: [PATCH] epic-ts --- SL/Controller/Mebil.pm | 310 ++++++++++++++++++++++++++++++++++++++ debian/.dummy | 0 image/icons/16x16/wtg.png | Bin 0 -> 5299 bytes menus/user/00-erp.yaml | 18 +++ 4 files changed, 328 insertions(+) create mode 100644 SL/Controller/Mebil.pm create mode 100644 debian/.dummy create mode 100644 image/icons/16x16/wtg.png diff --git a/SL/Controller/Mebil.pm b/SL/Controller/Mebil.pm new file mode 100644 index 000000000..db4a84b11 --- /dev/null +++ b/SL/Controller/Mebil.pm @@ -0,0 +1,310 @@ +package SL::Controller::Mebil; + +use strict; + +use parent qw(SL::Controller::Base); + +#use SL::Controller::Helper::ReportGenerator; +use SL::ReportGenerator; +use SL::DBUtils; + +use SL::Locale::String; # t8 + +#use Data::Dumper; +#use SL::ClientJS; + +use Rose::Object::MakeMethods::Generic ( + scalar => [ qw(report number_columns year current_year objects subtotals_per_quarter salesman_id) ], + 'scalar --get_set_init' => [ qw(employees types data) ], +); + +sub action_map { + $::lxdebug->enter_sub; + $::lxdebug->message(5, 'controller=mebil/action=map'); + my ($self) = @_; + + $::form->header(no_layout => 1); + + print "

Mebil running

"; + + my $sql = "SELECT chart_id,xbrl_tag from mebil_mapping"; + my $result = SL::DBUtils::do_query($::form, $::form->get_standard_dbh, $sql); + $::lxdebug->message(5, "result= $result"); + print "$result
\n"; + + my @r = SL::DBUtils::selectall_hashref_query($::form, $::form->get_standard_dbh, $sql); + print ref($r[1])||"SCALAR"; + print "
"; + my $fst = $r[1]; + while (my($k,$v) = each(%$fst)) { + print $k, " : ", $v, "
\n"; + } + print "
"; + print scalar @r; + print "
"; + print @r; + print "

Mebil ready

"; + $::lxdebug->leave_sub; +} + +sub action_showmap { + $::lxdebug->enter_sub; + my ($self) = @_; + + $::form->{title} = $::locale->text('Mebil Map'); + + 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; + $self->list_data; + + $::lxdebug->leave_sub; +} + +sub extract_rule { + my $rule = shift; + my $part = $rule; + $part =~ s/:.*//; + if ($rule =~ /:/) { + $rule =~ s/[^:]*://; + } + else { + $rule = ""; + } + $::lxdebug->message(5, "part=$part"); + return ($part,$rule); +} + +sub rule2sql { + $::lxdebug->enter_sub; + + my $rule = shift; + my $year = shift; + $::lxdebug->message(5, "rule=$rule"); + + # a rule consists of key=value pairs seperated by colon. Possible keys are: + # - ACC: account number + # - VALUES=positive|negative: only positive or negative values are selected + # negative implies INVERT=true + # - INVERT=true: Result is multiplied by -1 + # - START=YEAR: start year. Absolute ('2020') or relative ('YEAR/PY') + # - END=YEAR: end year. Default: $year + + # supply defaults: + my $acc = "0000"; + my $values = ""; + my $invert = ""; + my $start = ""; + my $end = "AND (ac.transdate <= '31.12.$year')"; + my $py = $year - 1; + + # parse rule + (my $part,$rule) = extract_rule($rule); + $::lxdebug->message(5, "part=$part"); + while ($part) { + $part =~ /(.*)=(.*)/; + if ($1 eq "ACC") { + $acc = $2; + } + elsif ($1 eq "VALUES") { + if ($2 eq "positive") { + $values = "AND (ac.amount > 0)"; + } + elsif ($2 eq "negative") { + $values = "AND (ac.amount < 0)"; + $invert = "* (-1)"; + } + else { + die "invalid rule part: $part"; + } + } + elsif ($1 eq "INVERT") { + $invert = "* (-1)"; + } + elsif ($1 eq "START") { + $start = "AND (ac.transdate >= '01.01.$2')"; + $start =~ s/YEAR/$year/; + $start =~ s/PY/$py/; + } + elsif ($1 eq "END") { + $end = "AND (ac.transdate <= '31.12.$2')"; + $end =~ s/YEAR/$year/; + $end =~ s/PY/$py/; + } + else { + die "invalid rule part: $part"; + } + ($part,$rule) = extract_rule($rule); + } + + $::lxdebug->leave_sub; + return "SELECT SUM(ac.amount) $invert AS saldo + FROM acc_trans ac + JOIN chart c ON (c.id = ac.chart_id) + WHERE (c.accno = '$acc') $values $start $end"; +} + +sub action_calcmap { + $::lxdebug->enter_sub; + my ($self) = @_; + + $self->year($::form->{year} || DateTime->today->year - 1); + + $::form->{title} = $::locale->text('Mebil Map'); + + 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 = (); + my $year = $self->year; + 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.$year') + 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 + $sql = "SELECT SUM(ac.amount)* -1 AS saldo + FROM acc_trans ac + JOIN chart c ON (c.id = ac.chart_id) + WHERE (ac.transdate <= '31.12.$year') + 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 +# $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') { + # 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}}; + } + elsif ($mapping->{typ} eq 'R') { + # rule based + my $sql = rule2sql($mapping->{fromacc}, $year); + $::lxdebug->message(5, "sql=$sql"); + my @result = SL::DBUtils::selectfirst_array_query($::form, $::form->get_standard_dbh, $sql); + $toacc{$mapping->{toacc}} += $result[0]; + } + elsif ($mapping->{typ} eq 'C') { + # constant value + ; # do nothing here + } + else { + die "Error: Invalid mapping type: $mapping->{typ}\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' }, + ); + + $self->report->set_options( + std_column_visibility => 1, + controller_class => 'Mebil', + output_format => 'HTML', + raw_top_info_text => $self->render('mebil/report_top', { output => 0 }, YEARS_TO_LIST => [ reverse(($self->year - 10)..($self->year + 5)) ]), + title => t8('mebil - Mapping: values for #1', $self->year), +# 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; + $self->add_data_sorted(\%fromacc); + $self->add_data_sorted(\%toacc); + + $::lxdebug->leave_sub; + return $self->report->generate_with_headers; +} +sub add_data_sorted { + my $self = shift; + my $data = shift; # hash reference + + foreach my $key (sort keys %$data) { + my %data = ( + name => { data => $key }, + amount => { data => $::form->format_amount(\%::myconfig, $data->{$key}, 2) }, + ); + $self->report->add_data(\%data); + + } +} +sub prepare_report { + my ($self) = @_; + + $self->report(SL::ReportGenerator->new(\%::myconfig, $::form)); + + my @columns = (qw(fromacc typ toacc)); + + #$self->number_columns([ grep { !m/^(?:month|year|quarter)$/ } @columns ]); + + my %column_defs = ( + fromacc => { text => 'Quelle', align => 'left' }, + typ => { text => 'Typ' , align => 'right' }, + toacc => { text => 'Ziel' , align => 'left' }, + ); + + #$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; +} +sub list_data { + my ($self) = @_; + +# my @visible_columns = $self->report->get_visible_columns; +# my @type_columns = @{ $self->types }; +# my @non_type_columns = grep { my $c = $_; none { $c eq $_ } @type_columns } @visible_columns; + + foreach my $mapping (@{ $self->{data} }) { + my %data = ( + fromacc => { data => $mapping->{fromacc} }, + typ => { data => $mapping->{typ} }, + toacc => { data => $mapping->{toacc} }, + ); + $self->report->add_data(\%data); + } + + return $self->report->generate_with_headers; +} + +1; diff --git a/debian/.dummy b/debian/.dummy new file mode 100644 index 000000000..e69de29bb diff --git a/image/icons/16x16/wtg.png b/image/icons/16x16/wtg.png new file mode 100644 index 0000000000000000000000000000000000000000..0d4d690da6e19c711e37c9f99cae1fc69c747f8f GIT binary patch literal 5299 zcmV;k6in-hP) zaB^>EX>4U6ba`-PAZ2)IW&i+q+O1bxk}RtY{AU$j0+M)$= zAp?vN;ZNA{6arT3ukMv({jyYC34dAgnrP!?7x@Ez&i};l3&1=2!q1<`4cV+$=(TqO(pPwK2JEZ08 z$Pfh^9n1;Ld}E<^-LlrhVv3aqVI~ut(aYeEulqLq)$cDy&$H5;$m_)laq&u7gt3@g ze=!S$&|{d!7hiJw;=ezOSOJ6ag;}w|>f3hFB7Jg8UY^BAhpEBudZ9eb0U*NK5y6mv z0iQ@BCGwd$69GpBKNXR)1a4q}qKiBbB?plNa+KHFcqh|4mm_a8WLkqWXx zh#{fg6e(0)lo(=+DdwoL#F`|96jMq$EuE@aa>y~KoU`VVtBMAVnl!6w(OS3&WGuRp zN-kPTsTGUXP~od$eubfNlNMTRspY1vwAztAJ@nX9&s}@zb?}e@32-BgJam*%CrzZ% z3^UF&^VC^peUNL9JmHB?dh(-BdFo}&SpCh~H*^1+H8R%tC2GyfAJ$;b`M97ZCvkBG z#yAid&x-*hG%wD)QV6`5Tb%jA;zS}N%DBiGv={?}VG_rOE_Wa1{wr?|=>EUF@t>G; zi@LvqIk%|$!Q6lH_6OERedShOf>sp*Qz)dM{4lAq;s@!n(x!$LoE9-9zL&^h(5AMp?f|~nk@=))NNb*ibu+^aKDkA82x;o zwW?hopWmC}d>?G~KC@WNjeVDG=B1iD)*c{|dxe%Dr^EIzkrGZ?+D}SvvS?fG5p`LM zqqpZ84lp{*#wW;$%vh$R!Ii!djDwLrd5>EuOM%v~Bj0G&?Kc6vv>~b-O4=h&7`PR2 zuhPe~J%w%%j40as*f+%eNfvv@u6xfX_$Mt8+=ez>kZnXX(~csiqNu1nt|>E(vV)ye zrXtm3J#%rBlrS5cT zavULl*_dt$ru(ulj7CR31Y1|vyKTRVJ=JILNlJ?2pt8zmr>@d|9&z+*ZSRIr`TMyK ziHu2flX6JUTFp8aP;uWt_gWqcBY?(b%)@*@^NzN&37HUVRqu3+vvx(pQ50D$& zhP3;UKY1y9QsH>_$)ZslfN!`dh0QGL)Y*37$xkcCxlVWe*~Rk2{@^A&mP1hnwD>Uk zh^;@IVix+zcK0B?LYT%*R}|VwFfx3D;;lEL>Q(pNpvU5N2K6A@mX4SdFZoldc@|w|U_idSwY$Fi0kjt?2Ev0j=w4<0fiuI(sXf^Hhg;KWQ3@hG|>@0=-!Ro}JTY(XO z*qVc=lNGc-rAhH>rn9Us+jnB47DQZ^wxggODf&?oWUH0cNTSYTEA|lDx&B`;M)fS~ zZ&Gg8v7vt#tB`^IkQp(%#aKjjLMN}vjK1k$G9Kmb=3a+kOB-3g#NVoqjCqd%rs;Ew9Y0!)s zFNQhf2Jj>4omlfQk!8M}k<8LC1~JysZ?ROh>~D&74idgNgWd}ay8Bt$kFTcb zzC&CrzXnM`ww>2`Q5Zj!RktS`Yn~LXPIS^Oo_X@I-*r#b>TmRIk1wL>% z#L@M%jYhLX(-%+)sI1bhmT)rm;T%UCTUYHpB`Z&l`KN0I^!8?<;+R{`xnu=% z#4Y7I-=f`+-h<*D&2)QX^W&>%e_z$R8cla^`^Ue|eWu%)2wAroo{BbQnq2+G>>)n~ zyL-%;mZZoB9Nf!0YiLpeNDaM6kuEAR2@p!akN_e!L{xA@QIVzy zGAQ7HqeGFgB8r6pQL*a;8AQ}^a1n6@-c&M->OB3XhmR+Dq`EL(i` znPm?-^D=}y8Ow9d;$`sU+$ZCWITF5%kzg4Y=Lq<@GQK8bgLFxTK*n$6u^D_$HUKD+ z+%D#GQ)Fx{W0EK`f-U2D0N_Z;U+~f|Sj^88%MZoQ%vvrIB&UcOCR|g7jgu3L;m^-a z=ZnS6Fb+43BjPdGnHgCe;c@_G&-_^wd2Jc8B0JbPIXEzFEp5Ii)PG(4o09i-mR^K^ z?ioZM_`~*Bewhsbu%>0T+4_fVX%zrn>j6-^{fEt9F93?NzI6_LaUQySUQ)#3EN3gL z+}vDC0iSCrFX-?3pALURUwqF}zTNNTVR-YCIFfWRLtZy-W_qSX#K_L#aQO`8pNIG# z2mW;)77_d;zKAcBMMTS{Odw2_wOhy&hy|HKhCukAn)naH{-oKtmWkT<5zv-c0;M4u zKz<$oC@K*k343HK(C>W<#zzDB&5O~Qn4SC2g8qG1xJ>@Y79@X;V@E_XxDrv$?3(;q z0yH21ML+}UKpW@-6Tk$vz!A6r58wj=K`4j>abPjvf)tPeL?9OwfVH3)l!C2bC#VK> zpb<2KHgFhpfn(q_I0r6)%U}fD0a7pyo`5Ov3d}$dgoVfu6;g+EAVbI;vV~ZX8{`88 zLlICMln5n5LP!D?K>Y--nTj(fs8oB@tL${z&XcGDrdIuvg38ukXun}wp zvtUm+2#$mo!O8G4I3F&8x4@Nf1AGwfgiphl;1O5~KY^zafDjQnqKhyQ7Q#kCk$5Bt z5h1IP5~KoYK-!QVq#wD8NRg+=TNDOGMKMrJlncrq6@}uWmZ4UmHlwOh2T+};KGapz zC~6Az5lu#GqRr9H=m2yqIvJgdE=E_No6sHTv*;1@IQkU^gP~)LF^(92OdKW^vjVdj zvm4WnIfWUY%#V9dk}jPdj&g=eS;(7ba1vfUtBy+h%3ZZ z;977ea93~>xEZ_>-VpDM55@EF%kgFSMtl!`2tSUWAt)1!39f`lLMmY`p_0%>I7_%o zctIo*^@vWyaH4>?hFD2FL_AL%CB7w5NMLy&#jxMr03iJXuWM zLT)CXA>SvzQJ^YVDg-F-6jm$LD0C`ZQFx|^S2R@gR9vi>uUMgYL~%&*sS;kvNQte) zQCg)`qjXg1hSIb$RoO;4R5?R=vvP~_1?5K+EX9c8L*Y@1DEla9C}UKFs!wH8xzu&k zM(SDWI1NKHrUlSaX{EGpXoIvV6^e?TO0-IzN{z~K6)7E|8_@&k>GU%CVfuCYJ5?=J zPgTC^Ce=38E2^*6=BRn7@zqMy+SNwX-l;Rxebm#`x2boj-_t;8m}!J-V#`E~|8t09<>bY`U>s_U847WtLy>3!>U3ZRq zgZl#yeGi^TlgEUosb{L^LC^t4d)BzcZGt`fY9xs_ri?BmW6#EjtLJ7uL*w`VG~gh(Z7JYV9A1(1+OB#BFiH0 zM43cMqI#nhqZ6W=qhH5($CSrNW36IW#$Jlkh!ezh$7AE8xdr`1lgVC7dNk648kso~Qb!)}_8q3r*Xf_9;Cky*&e$k(hB* zND-z9`!cmN^D>9C%(IHKq|2O_?OZk`3KBJCL)nY6yTvrw&(wg#M6zBon&XyJlk+Aw zI`>GPa-J}6V7b}yP0J_pee=Iwfm*>`(OaNfu(n`yrRU1}RnV%Xt9n=KuP$0WzQ%9O zfwhFSX={Hdv@R?!e7P=WT~Co#(VC*M^?vJHixrAR#Wyy%Y^d9a+9=pKxM}XDs!bnD zcqJE0txGFP-*4t_zW9~RSCwCV+LE+ou*{)s&sNM<;nwTh+_xPlS1!*lAKMka+ zN6C(tJD2RdP+?b5w~Me#vP)VST-jY^P_?z{eRWFpNR3xbd#z^errOuLdAqOd@z~Q= zr&U*4_inFX@6CF@`pyQUhKhZdeL4FcHbyu0f6e;3xk;m`wCTfs;eP3Xhy#7ij?K+2 znk{9maI2(s@?gTj%inl^)7{2wt8b^bmmUI#B!?bmEKDc(k|2rKjV2%kTFe(>+#mT;+J#3Brk@6Q54zpPW9Gb?WKq zB=X}qd>G$kEdEWK>u?x-@j$UM4?7HM|sdK=7pyLPo zA36pd20Mowhq^C2UG5p4H+rvNl-blD1y~(@z=vMlz z=eKii&)iva7k#(np3=RF`@L<7%J7e6jCqHHX^nSePA^Bm& zgw90sBil#ECVeL_KVJBF{7Kf6nWx1+EB{>k%;ed3zj*vIFcmvB{#^9@)32LfsK40% z((YyNblCL$R~fHnUYER4e{uvwLn0FI1x$m*>D?gZi==m7*@$RRLPoI7R_k{EN zIiTCQ00009a7bBm001r{001r{0eGc9b^rhX2XskIMF-{q8x$5IWM)*300091NklCDseMiFTs;1_zjcgk`r0O-0N3WX36Ip?aX`u%=YRc#Xa{QPHP zxId_L6|>9LQd(~BIeFsHYp?XDo-DVtw8Z0a)HDqdFI@ayO;@!oH_S>WC&vX}3ljnY zTDR`bJ*jq7H`YJ?VDwobKQ%Rli9{l?v^0DBfw<$)iT(EEJI6&)OsyA@2)(=80QheU z_pjcoq#iAN^U3@B2V!JkV4!Tb>kT&AZ|@s?-L#qj@H_?phG~U5_MAE16Ocn!Z>LSu zBv13jKUUoFV@J=NdW#V^TePaRIwAsqVVEnK3ZpnY7&~|RnBJg&Co*JtRR<(*ytO6l-{Uf}M-q|VZ5t&qt+O4M_?0swlRtLc{Kx=5(w zI6K19^1mVg0Oz@t$8#l->7~s=PW$3gN|O8>DImIT({mg_lz{W5<4^*E=(8=831U81 zWqtkKdkg-L-i>zcQUJiRoHgygA7|(OTq^x~?L!0rBmf)$UC$$kBo2QmM?$%y15&aQNnpUx-hV-i!Ct9s2Fhk8P{g7mYB+xMw?#5sh|FOiUz` z$qV0I?Ck6;6^qSglK{YhmtvDca%PkNa3`rIv$ky`5=zDOOm=>LzO}6l00mLJa^-Tl zTqXbjfNzfto*ip1H~5#=SI>TW@ArRn0z4Y+K73?!Yjevq8ueOjdU{$>l;;2dU}WgU zug?ulz1i_{?{>qsEz2-X^QY^x!@~#1$K#X|##kg0`5%hzWxON_)*Ao-002ovPDHLk FV1n#|M;ibD literal 0 HcmV?d00001 diff --git a/menus/user/00-erp.yaml b/menus/user/00-erp.yaml index b8d9630fe..a22a5a655 100644 --- a/menus/user/00-erp.yaml +++ b/menus/user/00-erp.yaml @@ -916,6 +916,24 @@ access: report params: action: LiquidityProjection/show +- id: mebil + name: Mebil + icon: wtg + order: 750 +- parent: mebil + id: mebil_showmap + 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 -- 2.20.1