Revision d5145a56
Von Bernd Bleßmann vor mehr als 1 Jahr hinzugefügt
| SL/Controller/CustomerVendorTurnover.pm | ||
|---|---|---|
| 1 | 1 |
package SL::Controller::CustomerVendorTurnover; |
| 2 | 2 |
use strict; |
| 3 | 3 |
use parent qw(SL::Controller::Base); |
| 4 |
|
|
| 5 |
use List::Util qw(first); |
|
| 6 |
|
|
| 4 | 7 |
use SL::DBUtils; |
| 5 | 8 |
use SL::DB::AccTransaction; |
| 6 | 9 |
use SL::DB::Invoice; |
| ... | ... | |
| 9 | 12 |
use SL::DB::Letter; |
| 10 | 13 |
use SL::DB; |
| 11 | 14 |
use SL::JSON qw(to_json); |
| 12 |
|
|
| 13 | 15 |
__PACKAGE__->run_before('check_auth');
|
| 14 | 16 |
|
| 15 | 17 |
sub action_list_turnover {
|
| ... | ... | |
| 138 | 140 |
$cv_type = "vendor_id"; |
| 139 | 141 |
} |
| 140 | 142 |
|
| 143 |
my $year_where = ('month' eq $::form->{mode} && $::form->{year})
|
|
| 144 |
? 'AND EXTRACT (YEAR FROM transdate) = ?' |
|
| 145 |
: ''; |
|
| 146 |
|
|
| 147 |
|
|
| 141 | 148 |
my ($date_part_select, $group_by, $order_by); |
| 142 | 149 |
if ('month' eq $::form->{mode}) {
|
| 143 | 150 |
$date_part_select = "CONCAT(EXTRACT (MONTH FROM transdate),'/',EXTRACT (YEAR FROM transdate))"; |
| ... | ... | |
| 155 | 162 |
sum(amount) as amount, |
| 156 | 163 |
sum(netamount) as netamount, |
| 157 | 164 |
sum(paid) as paid |
| 158 |
FROM $db WHERE $cv_type = ? |
|
| 165 |
FROM $db WHERE $cv_type = ? $year_where
|
|
| 159 | 166 |
GROUP BY $group_by |
| 160 | 167 |
ORDER BY $order_by |
| 161 | 168 |
SQL |
| 162 |
$self->{turnover_statistic} = selectall_hashref_query($::form, $dbh, $query, $cv);
|
|
| 169 |
$self->{turnover_statistic} = selectall_hashref_query($::form, $dbh, $query, $cv, ($::form->{year} || '') x !!('month' eq $::form->{mode} && $::form->{year}));
|
|
| 163 | 170 |
|
| 164 | 171 |
if ('month' eq $::form->{mode} && $fill_holes && @{$self->{turnover_statistic}} > 1) {
|
| 165 | 172 |
my $date_part_to_months = sub { my ($m, $y) = $_[0] =~ m{^(\d{1,2})/(\d{1,4})$}; return $m + 12*$y; };
|
| 166 |
my $months_to_date_part = sub { my $y = int($_[0]/12); my $m = $_[0] - 12*$y; $m ||= 12; return "$m/$y"; };
|
|
| 167 |
my $start_month = $date_part_to_months->($self->{turnover_statistic}[ 0]->{date_part});
|
|
| 168 |
my $end_month = $date_part_to_months->($self->{turnover_statistic}[-1]->{date_part});
|
|
| 169 |
my $step = ($start_month > $end_month) ? -1 : 1; |
|
| 170 |
my $next_month = $start_month; |
|
| 171 |
my @new_stats = (); |
|
| 172 |
foreach my $stat (@{$self->{turnover_statistic}}) {
|
|
| 173 |
while ($date_part_to_months->($stat->{date_part}) != $next_month) {
|
|
| 174 |
push @new_stats, {date_part => $months_to_date_part->($next_month)};
|
|
| 175 |
$next_month += $step; |
|
| 173 |
my $months_to_date_part = sub { my $y = int(($_[0] - 1)/12); my $m = $_[0] - 12*$y; $m ||= 12; return "$m/$y"; };
|
|
| 174 |
|
|
| 175 |
my $start_month; |
|
| 176 |
my $end_month; |
|
| 177 |
if (!$::form->{year}) {
|
|
| 178 |
$start_month = $date_part_to_months->($self->{turnover_statistic}[ 0]->{date_part});
|
|
| 179 |
$end_month = $date_part_to_months->($self->{turnover_statistic}[-1]->{date_part});
|
|
| 180 |
|
|
| 181 |
} else {
|
|
| 182 |
if ($sort_dir eq 'ASC') {
|
|
| 183 |
$start_month = $date_part_to_months->('1/' . $::form->{year});
|
|
| 184 |
$end_month = $date_part_to_months->('12/' . $::form->{year});
|
|
| 185 |
} else {
|
|
| 186 |
$start_month = $date_part_to_months->('12/' . $::form->{year});
|
|
| 187 |
$end_month = $date_part_to_months->('1/' . $::form->{year});
|
|
| 176 | 188 |
} |
| 177 |
push @new_stats, $stat; |
|
| 178 |
$next_month += $step; |
|
| 179 | 189 |
} |
| 190 |
|
|
| 191 |
my $step = ($start_month > $end_month) ? -1 : 1; |
|
| 192 |
my @range = ($step == 1) ? ($start_month .. $end_month) : reverse ($end_month .. $start_month); |
|
| 193 |
my @new_stats = (); |
|
| 194 |
|
|
| 195 |
my %stats_by_month = map { $date_part_to_months->($_->{date_part}) => $_ } grep { $_ } @{$self->{turnover_statistic} || []};
|
|
| 196 |
foreach my $month (@range) {
|
|
| 197 |
if ($stats_by_month{$month}) {
|
|
| 198 |
push @new_stats, $stats_by_month{$month};
|
|
| 199 |
} else {
|
|
| 200 |
push @new_stats, {date_part => $months_to_date_part->($month)};
|
|
| 201 |
} |
|
| 202 |
} |
|
| 203 |
|
|
| 180 | 204 |
$self->{turnover_statistic} = \@new_stats;
|
| 181 | 205 |
} |
| 182 | 206 |
|
| ... | ... | |
| 185 | 209 |
my $end = $self->{turnover_statistic}[-1]->{date_part};
|
| 186 | 210 |
my $step = ($start > $end) ? -1 : 1; |
| 187 | 211 |
my $next_date_part = $start; |
| 188 |
my @new_stats = (); |
|
| 212 |
my @new_stats = (); |
|
| 213 |
|
|
| 189 | 214 |
foreach my $stat (@{$self->{turnover_statistic}}) {
|
| 190 | 215 |
while ($stat->{date_part} != $next_date_part) {
|
| 191 | 216 |
push @new_stats, {date_part => $next_date_part};
|
| ... | ... | |
| 194 | 219 |
push @new_stats, $stat; |
| 195 | 220 |
$next_date_part += $step; |
| 196 | 221 |
} |
| 222 |
|
|
| 197 | 223 |
$self->{turnover_statistic} = \@new_stats;
|
| 198 | 224 |
} |
| 199 | 225 |
|
| 226 |
|
|
| 200 | 227 |
if ($::request->type eq 'json') {
|
| 201 | 228 |
$self->render(\ SL::JSON::to_json($self->{turnover_statistic}), { layout => 0, type => 'json', process => 0 });
|
| 202 | 229 |
} else {
|
Auch abrufbar als: Unified diff
Umsatzstatistik-Chart: per Klick auf Monate des entsprechenden Jahres