use SL::ReportGenerator;
use SL::DBUtils;
+use SL::Locale::String; # t8
+
#use Data::Dumper;
#use SL::ClientJS;
+use SL::mebil::Mapping;
+
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_showmap {
$::lxdebug->enter_sub;
my ($self) = @_;
+
+ # call model -> diese Zeile ist fraglich, war ein Konflikt
+ #$self->{data} = DB::MebilMapping::getMappings($::form->get_standard_dbh);
+
+ $::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 $mapping = new SL::mebil::Mapping($::form, $::form->get_standard_dbh);
+ $self->{data} = $mapping->get_mapping();
$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.2020')
+ 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};
}
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.;
+ $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') {
- # 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}};
+ }
+ 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\n";
+ die "Error: Invalid mapping type: $mapping->{typ}\n";
}
}
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',
+ 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;
-
- 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);
+
+ $::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 => $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) = @_;
projects / kivitendo-erp.git / blobdiff