Projekt

Allgemein

Profil

Herunterladen (18 KB) Statistiken
| Zweig: | Markierung: | Revision:

kivitendo/SL/Controller/CustomerVendorTurnover.pm @ 2e6fe034

package SL::Controller::CustomerVendorTurnover;
use strict;
use parent qw(SL::Controller::Base);

use List::Util qw(first);

use SL::DBUtils;
use SL::DB::AccTransaction;
use SL::DB::Invoice;
use SL::DB::Order;
use SL::DB::EmailJournal;
use SL::DB::Letter;
use SL::DB;
use SL::JSON qw(to_json);
__PACKAGE__->run_before('check_auth');

sub action_list_turnover {
my ($self) = @_;

return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};

my $cv = $::form->{id};
my $open_invoices;
if ( $::form->{db} eq 'customer' ) {
$open_invoices = SL::DB::Manager::Invoice->get_all(
query => [
customer_id => $cv,
or => [
amount => { gt => \'paid'},
amount => { lt => \'paid'},
],
],
sort_by => 'transdate DESC',
with_objects => [ 'dunnings' ],
);
} else {
$open_invoices = SL::DB::Manager::PurchaseInvoice->get_all(
query => [
vendor_id => $cv,
or => [
amount => { gt => \'paid'},
amount => { lt => \'paid'},
],
],
sort_by => 'transdate DESC',
);
}
my $open_items;
if (@{$open_invoices}) {
$open_items = $self->_list_open_items($open_invoices);
}
my $open_orders = $self->_get_open_orders;
return $self->render('customer_vendor_turnover/turnover', { header => 0 },
open_orders => $open_orders,
open_items => $open_items,
id => $cv,
);
}

sub _list_open_items {
my ($self, $open_items) = @_;

return $self->render('customer_vendor_turnover/_list_open_items', { output => 0 },
OPEN_ITEMS => $open_items,
title => $::locale->text('Open Items'),
);
}

sub action_count_open_items_by_year {
my ($self) = @_;

return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
my $dbh = SL::DB->client->dbh;

my $cv = $::form->{id};

my $query = <<SQL;
SELECT EXTRACT (YEAR FROM d.transdate),
count(d.id),
max(d.dunning_level)
FROM dunning d
LEFT JOIN ar a ON a.id = d.trans_id
LEFT JOIN customer c ON a.customer_id = c.id
WHERE c.id = ?
GROUP BY EXTRACT (YEAR FROM d.transdate), c.id
ORDER BY date_part DESC
SQL

$self->{dun_statistic} = selectall_hashref_query($::form, $dbh, $query, $cv);
$self->render('customer_vendor_turnover/count_open_items_by_year', { layout => 0 });
}

sub action_count_open_items_by_month {

my ($self) = @_;

return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
my $dbh = SL::DB->client->dbh;

my $cv = $::form->{id};

my $query = <<SQL;
SELECT CONCAT(EXTRACT (MONTH FROM d.transdate),'/',EXTRACT (YEAR FROM d.transdate)) AS date_part,
count(d.id),
max(d.dunning_level)
FROM dunning d
LEFT JOIN ar a ON a.id = d.trans_id
LEFT JOIN customer c ON a.customer_id = c.id
WHERE c.id = ?
GROUP BY EXTRACT (YEAR FROM d.transdate), EXTRACT (MONTH FROM d.transdate), c.id
ORDER BY EXTRACT (YEAR FROM d.transdate) DESC
SQL

$self->{dun_statistic} = selectall_hashref_query($::form, $dbh, $query, $cv);
$self->render('customer_vendor_turnover/count_open_items_by_year', { layout => 0 });
}

sub action_turnover {

my ($self) = @_;

return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};

my $sort_dir = 'DESC';
my $fill_holes = 0;

if ($::request->type eq 'json') {
$sort_dir = 'ASC';
$fill_holes = 1;
}

my $dbh = SL::DB->client->dbh;
my $cv = $::form->{id};
my ($db, $cv_type);
if ($::form->{db} eq 'customer') {
$db = "ar";
$cv_type = "customer_id";
} else {
$db = "ap";
$cv_type = "vendor_id";
}

my $year_where = ('month' eq $::form->{mode} && $::form->{year})
? 'AND EXTRACT (YEAR FROM transdate) = ?'
: '';


my ($date_part_select, $group_by, $order_by);
if ('month' eq $::form->{mode}) {
$date_part_select = "CONCAT(EXTRACT (MONTH FROM transdate),'/',EXTRACT (YEAR FROM transdate))";
$group_by = "EXTRACT (YEAR FROM transdate), EXTRACT (MONTH FROM transdate)";
$order_by = "EXTRACT (YEAR FROM transdate) $sort_dir, EXTRACT (MONTH FROM transdate) $sort_dir";
} else {
$date_part_select = "EXTRACT (YEAR FROM transdate)";
$group_by = "EXTRACT (YEAR FROM transdate)";
$order_by = "EXTRACT (YEAR FROM transdate) $sort_dir";
}

my $query = <<SQL;
SELECT $date_part_select as date_part,
count(id) as count,
sum(amount) as amount,
sum(netamount) as netamount,
sum(paid) as paid
FROM $db WHERE $cv_type = ? $year_where
GROUP BY $group_by
ORDER BY $order_by
SQL
$self->{turnover_statistic} = selectall_hashref_query($::form, $dbh, $query, $cv, ($::form->{year} || '') x !!('month' eq $::form->{mode} && $::form->{year}));

if ('month' eq $::form->{mode} && $fill_holes && @{$self->{turnover_statistic}} > 1) {
my $date_part_to_months = sub { my ($m, $y) = $_[0] =~ m{^(\d{1,2})/(\d{1,4})$}; return $m + 12*$y; };
my $months_to_date_part = sub { my $y = int(($_[0] - 1)/12); my $m = $_[0] - 12*$y; $m ||= 12; return "$m/$y"; };

my $start_month;
my $end_month;
if (!$::form->{year}) {
$start_month = $date_part_to_months->($self->{turnover_statistic}[ 0]->{date_part});
$end_month = $date_part_to_months->($self->{turnover_statistic}[-1]->{date_part});

} else {
if ($sort_dir eq 'ASC') {
$start_month = $date_part_to_months->('1/' . $::form->{year});
$end_month = $date_part_to_months->('12/' . $::form->{year});
} else {
$start_month = $date_part_to_months->('12/' . $::form->{year});
$end_month = $date_part_to_months->('1/' . $::form->{year});
}
}

my $step = ($start_month > $end_month) ? -1 : 1;
my @range = ($step == 1) ? ($start_month .. $end_month) : reverse ($end_month .. $start_month);
my @new_stats = ();

my %stats_by_month = map { $date_part_to_months->($_->{date_part}) => $_ } grep { $_ } @{$self->{turnover_statistic} || []};
foreach my $month (@range) {
if ($stats_by_month{$month}) {
push @new_stats, $stats_by_month{$month};
} else {
push @new_stats, {date_part => $months_to_date_part->($month)};
}
}

$self->{turnover_statistic} = \@new_stats;
}

if ('month' ne $::form->{mode} && $fill_holes && @{$self->{turnover_statistic}} > 1) {
my $start = $self->{turnover_statistic}[ 0]->{date_part};
my $end = $self->{turnover_statistic}[-1]->{date_part};
my $step = ($start > $end) ? -1 : 1;
my $next_date_part = $start;
my @new_stats = ();

foreach my $stat (@{$self->{turnover_statistic}}) {
while ($stat->{date_part} != $next_date_part) {
push @new_stats, {date_part => $next_date_part};
$next_date_part += $step;
}
push @new_stats, $stat;
$next_date_part += $step;
}

$self->{turnover_statistic} = \@new_stats;
}

if (@{$self->{turnover_statistic}} > 1) {
my $query = <<SQL;
SELECT $date_part_select as date_part,
count(id) as count,
sum(amount) as amount,
sum(netamount) as netamount,
sum(paid) as paid
FROM $db WHERE $cv_type IS NOT NULL
GROUP BY $group_by
ORDER BY $order_by
SQL
my $overall_turnover = selectall_hashref_query($::form, $dbh, $query);
foreach my $stat (@{$self->{turnover_statistic}}) {
my $overall_stat = first { $_->{date_part} eq $stat->{date_part} } @$overall_turnover;
$stat->{overall_netamount} = 0;
$stat->{'overall_' . $_} = $overall_stat->{$_} for keys %$overall_stat;
}
}

if ($::request->type eq 'json') {
$self->render(\ SL::JSON::to_json($self->{turnover_statistic}), { layout => 0, type => 'json', process => 0 });
} else {
$self->render('customer_vendor_turnover/count_turnover', { layout => 0 });
}
}

sub action_get_invoices {
my ($self) = @_;

return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};

my $cv = $::form->{id};
my $invoices;
if ( $::form->{db} eq 'customer' ) {
$invoices = SL::DB::Manager::Invoice->get_all(
query => [ customer_id => $cv, ],
sort_by => 'transdate DESC',
);
} else {
$invoices = SL::DB::Manager::PurchaseInvoice->get_all(
query => [ vendor_id => $cv, ],
sort_by => 'transdate DESC',
);
}
$self->render('customer_vendor_turnover/invoices_statistic', { layout => 0 }, invoices => $invoices);
}

sub action_get_orders {
my ($self) = @_;

return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};

my $cv = $::form->{id};
my $orders;
my $type = $::form->{type};
if ( $::form->{db} eq 'customer' ) {
$orders = SL::DB::Manager::Order->get_all(
query => [
customer_id => $cv,
quotation => ($type eq 'quotation' ? 'T' : 'F')
],
sort_by => 'transdate DESC',
);
} else {
$orders = SL::DB::Manager::Order->get_all(
query => [
vendor_id => $cv,
quotation => ($type eq 'quotation' ? 'T' : 'F')
],
sort_by => 'transdate DESC',
);
}
if ( $type eq 'order') {
$self->render('customer_vendor_turnover/order_statistic', { layout => 0 }, orders => $orders);
} else {
$self->render('customer_vendor_turnover/quotation_statistic', { layout => 0 }, orders => $orders);
}
}

sub _get_open_orders {
my ( $self ) = @_;

return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
my $open_orders;
my $cv = $::form->{id};

if ( $::form->{db} eq 'customer' ) {
$open_orders = SL::DB::Manager::Order->get_all(
query => [
customer_id => $cv,
closed => 'F',
],
sort_by => 'transdate DESC',
);
} else {
$open_orders = SL::DB::Manager::Order->get_all(
query => [
vendor_id => $cv,
closed => 'F',
],
sort_by => 'transdate DESC',
);
}

return 0 unless scalar @{$open_orders};
return $self->render('customer_vendor_turnover/_list_open_orders', { output => 0 },
orders => $open_orders,
title => $::locale->text('Open Orders'),
);
}

sub action_get_mails {
my ( $self ) = @_;

return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
my $dbh = SL::DB->client->dbh;
my $query;
my $cv = $::form->{id};

if ( $::form->{db} eq 'customer') {
$query = <<SQL;
WITH
oe_emails_customer
AS (SELECT rc.to_id, rc.from_id, oe.quotation, oe.quonumber, oe.ordnumber, c.id
FROM record_links rc
LEFT JOIN oe oe ON rc.from_id = oe.id
LEFT JOIN customer c ON oe.customer_id = c.id
WHERE rc.to_table = 'email_journal'
AND rc.from_table ='oe'),

do_emails_customer
AS (SELECT rc.to_id, rc.from_id, o.donumber, c.id
FROM record_links rc
LEFT JOIN delivery_orders o ON rc.from_id = o.id
LEFT JOIN customer c ON o.customer_id = c.id
WHERE rc.to_table = 'email_journal'
AND rc.from_table = 'delivery_orders'),

inv_emails_customer
AS (SELECT rc.to_id, rc.from_id, inv.type, inv.invnumber, c.id
FROM record_links rc
LEFT JOIN ar inv ON rc.from_id = inv.id
LEFT JOIN customer c ON inv.customer_id = c.id
WHERE rc.to_table = 'email_journal'
AND rc.from_table = 'ar'),

letter_emails_customer
AS (SELECT rc.to_id, rc.from_id, l.letternumber, c.id
FROM record_links rc
LEFT JOIN letter l ON rc.from_id = l.id
LEFT JOIN customer c ON l.customer_id = c.id
WHERE rc.to_table = 'email_journal'
AND rc.from_table = 'letter')

SELECT ej.*,
CASE
oec.quotation WHEN 'F' THEN 'Sales Order'
ELSE 'Quotation'
END AS type,
CASE
oec.quotation WHEN 'F' THEN oec.ordnumber
ELSE oec.quonumber
END AS recordnumber,
oec.id AS record_id
FROM email_journal ej
LEFT JOIN oe_emails_customer oec ON ej.id = oec.to_id
WHERE oec.id = ?

UNION

SELECT ej.*, 'Delivery Order' AS type, dec.donumber AS recordnumber,dec.id AS record_id
FROM email_journal ej
LEFT JOIN do_emails_customer dec ON ej.id = dec.to_id
WHERE dec.id = ?

UNION

SELECT ej.*,
CASE
iec.type WHEN 'credit_note' THEN 'Credit Note'
WHEN 'invoice' THEN 'Invoice'
ELSE 'N/A'
END AS type,
iec.invnumber AS recordnumber,
iec.id AS record_id
FROM email_journal ej
LEFT JOIN inv_emails_customer iec ON ej.id = iec.to_id
WHERE iec.id = ?

UNION

SELECT ej.*, 'Letter' AS type, lec.letternumber AS recordnumber,lec.id AS record_id
FROM email_journal ej
LEFT JOIN letter_emails_customer lec ON ej.id = lec.to_id
WHERE lec.id = ?
ORDER BY sent_on DESC
SQL
}