Projekt

Allgemein

Profil

Herunterladen (12,8 KB) Statistiken
| Zweig: | Markierung: | Revision:

kivitendo/SL/Controller/CustomerVendorTurnover.pm @ 1c4bd891

9508e215 Werner Hahn
package SL::Controller::CustomerVendorTurnover;
use strict;
use parent qw(SL::Controller::Base);
use SL::DBUtils;
use SL::DB::AccTransaction;
use SL::DB::Invoice;
8e99e751 Werner Hahn
use SL::DB::Order;
29318a62 Werner Hahn
use SL::DB::EmailJournal;
use SL::DB::Letter;
c092c963 Werner Hahn
use SL::DB;
9508e215 Werner Hahn
__PACKAGE__->run_before('check_auth');

sub action_list_turnover {
my ($self) = @_;
c092c963 Werner Hahn
9508e215 Werner Hahn
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;
8e99e751 Werner Hahn
if ( $::form->{db} eq 'customer' ) {
9508e215 Werner Hahn
$open_invoices = SL::DB::Manager::Invoice->get_all(
query => [customer_id => $cv,
8e99e751 Werner Hahn
or => [
amount => { gt => \'paid'},
amount => { lt => \'paid'},
],
9508e215 Werner Hahn
],
with_objects => ['dunnings'],
);
8e99e751 Werner Hahn
} else {
$open_invoices = SL::DB::Manager::PurchaseInvoice->get_all(
query => [ vendor_id => $cv,
or => [
amount => { gt => \'paid'},
amount => { lt => \'paid'},
],
],
sort_by => 'invnumber DESC',
);
}
9508e215 Werner Hahn
my $open_items;
if (@{$open_invoices}) {
return $self->render(\'', { type => 'json' }) unless scalar @{$open_invoices};
$open_items = $self->_list_open_items($open_invoices);
}
8e99e751 Werner Hahn
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);
9508e215 Werner Hahn
}

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};
8e99e751 Werner Hahn
my $dbh = SL::DB->client->dbh;
9508e215 Werner Hahn
my $cv = $::form->{id} || {};

my $query = "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 = $cv
GROUP BY EXTRACT (YEAR FROM d.transdate), c.id
ORDER BY date_part DESC";

$self->{dun_statistic} = selectall_hashref_query($::form, $dbh, $query);
c092c963 Werner Hahn
$self->render('customer_vendor_turnover/count_open_items_by_year', { layout => 0 });
9508e215 Werner Hahn
}
29318a62 Werner Hahn
9508e215 Werner Hahn
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};
8e99e751 Werner Hahn
my $dbh = SL::DB->client->dbh;
9508e215 Werner Hahn
my $cv = $::form->{id} || {};

my $query = "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 = $cv
GROUP BY EXTRACT (YEAR FROM d.transdate), EXTRACT (MONTH FROM d.transdate), c.id
ORDER BY EXTRACT (YEAR FROM d.transdate) DESC";

$self->{dun_statistic} = selectall_hashref_query($::form, $dbh, $query);
c092c963 Werner Hahn
$self->render('customer_vendor_turnover/count_open_items_by_year', { layout => 0 });
9508e215 Werner Hahn
}
29318a62 Werner Hahn
9508e215 Werner Hahn
sub action_turnover_by_month {

my ($self) = @_;

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

8e99e751 Werner Hahn
my $dbh = SL::DB->client->dbh;
9508e215 Werner Hahn
my $cv = $::form->{id} || {};
8e99e751 Werner Hahn
my ($db, $cv_type);
if ($::form->{db} eq 'customer') {
$db = "ar";
$cv_type = "customer_id";
} else {
$db = "ap";
$cv_type = "vendor_id";
}
my $query = <<SQL;
SELECT CONCAT(EXTRACT (MONTH FROM transdate),'/',EXTRACT (YEAR FROM transdate)) AS date_part,
9508e215 Werner Hahn
count(id) as count,
sum(amount) as amount,
sum(netamount) as netamount,
sum(paid) as paid
8e99e751 Werner Hahn
FROM $db WHERE $cv_type = $cv
9508e215 Werner Hahn
GROUP BY EXTRACT (YEAR FROM transdate), EXTRACT (MONTH FROM transdate)
8e99e751 Werner Hahn
ORDER BY EXTRACT (YEAR FROM transdate) DESC, EXTRACT (MONTH FROM transdate) DESC
SQL
9508e215 Werner Hahn
$self->{turnover_statistic} = selectall_hashref_query($::form, $dbh, $query);
c092c963 Werner Hahn
$self->render('customer_vendor_turnover/count_turnover', { layout => 0 });
9508e215 Werner Hahn
}
29318a62 Werner Hahn
9508e215 Werner Hahn
sub action_turnover_by_year {
my ($self) = @_;

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

8e99e751 Werner Hahn
my $dbh = SL::DB->client->dbh;
9508e215 Werner Hahn
my $cv = $::form->{id} || {};
8e99e751 Werner Hahn
my ($db, $cv_type);
if ($::form->{db} eq 'customer') {
$db = "ar";
$cv_type = "customer_id";
} else {
$db = "ap";
$cv_type = "vendor_id";
}
my $query = <<SQL;
SELECT EXTRACT (YEAR FROM transdate) as date_part,
9508e215 Werner Hahn
count(id) as count,
sum(amount) as amount,
sum(netamount) as netamount,
sum(paid) as paid
8e99e751 Werner Hahn
FROM $db WHERE $cv_type = $cv
9508e215 Werner Hahn
GROUP BY date_part
8e99e751 Werner Hahn
ORDER BY date_part DESC
SQL
9508e215 Werner Hahn
$self->{turnover_statistic} = selectall_hashref_query($::form, $dbh, $query);
c092c963 Werner Hahn
$self->render('customer_vendor_turnover/count_turnover', { layout => 0 });
9508e215 Werner Hahn
}
8e99e751 Werner Hahn
9508e215 Werner Hahn
sub action_get_invoices {
my ($self) = @_;
c092c963 Werner Hahn
9508e215 Werner Hahn
return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};

my $cv = $::form->{id} || {};
8e99e751 Werner Hahn
my $invoices;
if ( $::form->{db} eq 'customer' ) {
$invoices = SL::DB::Manager::Invoice->get_all(
query => [ customer_id => $cv, ],
sort_by => 'invnumber DESC',
);
} else {
$invoices = SL::DB::Manager::PurchaseInvoice->get_all(
query => [ vendor_id => $cv, ],
sort_by => 'invnumber DESC',
);
}
9508e215 Werner Hahn
$self->render('customer_vendor_turnover/invoices_statistic', { layout => 0 }, invoices => $invoices);
}
8e99e751 Werner Hahn
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 => ( $type eq 'order' ? 'ordnumber DESC' : 'quonumber DESC'),
);
} else {
$orders = SL::DB::Manager::Order->get_all(
query => [ vendor_id => $cv,
quotation => ($type eq 'quotation' ? 'T' : 'F') ],
sort_by => ( $type eq 'order' ? 'ordnumber DESC' : 'quonumber 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 => 'ordnumber DESC',
);
} else {
$open_orders = SL::DB::Manager::Order->get_all(
query => [ vendor_id => $cv,
closed => 'F',
],
sort_by => 'ordnumber 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') );
}

29318a62 Werner Hahn
sub action_get_mails {
my ( $self ) = @_;

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
}
else {
$query = <<SQL;
WITH oe_emails_vendor 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 vendor c ON oe.vendor_id = c.id
WHERE rc.to_table = 'email_journal' AND rc.from_table ='oe'),

do_emails_vendor 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 vendor c ON o.vendor_id = c.id
WHERE rc.to_table = 'email_journal' AND rc.from_table = 'delivery_orders'),

inv_emails_vendor AS (SELECT rc.to_id, rc.from_id, inv.type, inv.invnumber, c.id FROM
record_links rc
LEFT JOIN ap inv ON rc.from_id = inv.id
LEFT JOIN vendor c ON inv.vendor_id = c.id
WHERE rc.to_table = 'email_journal' AND rc.from_table = 'ar'),

letter_emails_vendor 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 vendor c ON l.vendor_id = c.id
WHERE rc.to_table = 'email_journal' AND rc.from_table = 'letter')

SELECT ej.*, CASE
oec.quotation WHEN 'F' THEN 'Purchase Order'
ELSE 'Request 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_vendor oec ON ej.id = oec.to_id
WHERE oec.id = ?

UNION

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

UNION

SELECT ej.*, iec.type AS type, iec.invnumber AS recordnumber, iec.id AS record_id FROM email_journal ej
LEFT JOIN inv_emails_vendor 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_vendor lec ON ej.id = lec.to_id
WHERE lec.id = ?
ORDER BY sent_on DESC
SQL
}
my $emails = selectall_hashref_query($::form, $dbh, $query, $cv, $cv, $cv, $cv);
$self->render('customer_vendor_turnover/email_statistic', { layout => 0 }, emails => $emails);
}

8233ba0b Werner Hahn
sub action_get_letters {
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 $letters;
my $type = $::form->{type};
if ( $::form->{db} eq 'customer' ) {
$letters = SL::DB::Manager::Letter->get_all(
query => [ customer_id => $cv, ],
sort_by => 'date DESC',
);
} else {
$letters = SL::DB::Manager::Letter->get_all(
query => [ vendor_id => $cv, ],
sort_by => 'date DESC',
);
}
$self->render('customer_vendor_turnover/letter_statistic', { layout => 0 }, letters => $letters);
}

9508e215 Werner Hahn
sub _list_articles_by_invoice {
}
sub _list_count_articles_by_year {
}
sub check_auth {
$::auth->assert('general_ledger');
}
1;