From 2ef19c818a05b2baa2552ae5b4edbcc16bcfc986 Mon Sep 17 00:00:00 2001 From: Werner Hahn <wh@futureworldsearch.net> Date: Tue, 11 Sep 2018 12:09:27 +0200 Subject: [PATCH] Kundenstatistik: Typo und Formatierung --- SL/Controller/CustomerVendorTurnover.pm | 345 ++++++++++++++---------- 1 file changed, 199 insertions(+), 146 deletions(-) diff --git a/SL/Controller/CustomerVendorTurnover.pm b/SL/Controller/CustomerVendorTurnover.pm index 8cfa9652e..5356a73a0 100644 --- a/SL/Controller/CustomerVendorTurnover.pm +++ b/SL/Controller/CustomerVendorTurnover.pm @@ -15,26 +15,28 @@ sub action_list_turnover { return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id}; - my $cv = $::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'}, - ], - ], - with_objects => ['dunnings'], - ); + $open_invoices = SL::DB::Manager::Invoice->get_all( + query => [ + customer_id => $cv, + or => [ + amount => { gt => \'paid'}, + amount => { lt => \'paid'}, + ], + ], + with_objects => [ 'dunnings' ], + ); } else { $open_invoices = SL::DB::Manager::PurchaseInvoice->get_all( - query => [ vendor_id => $cv, - or => [ - amount => { gt => \'paid'}, - amount => { lt => \'paid'}, - ], - ], + query => [ + vendor_id => $cv, + or => [ + amount => { gt => \'paid'}, + amount => { lt => \'paid'}, + ], + ], sort_by => 'invnumber DESC', ); } @@ -43,14 +45,21 @@ sub action_list_turnover { return $self->render(\'', { type => 'json' }) unless scalar @{$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); + 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') ); + 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 { @@ -59,22 +68,22 @@ sub action_count_open_items_by_year { 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 = "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); - $self->render('customer_vendor_turnover/count_open_items_by_year', { layout => 0 }); + 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 { @@ -84,21 +93,21 @@ sub action_count_open_items_by_month { 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 = "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); + 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 }); } @@ -109,7 +118,7 @@ sub action_turnover_by_month { 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 $cv = $::form->{id}; my ($db, $cv_type); if ($::form->{db} eq 'customer') { $db = "ar"; @@ -119,16 +128,16 @@ sub action_turnover_by_month { $cv_type = "vendor_id"; } my $query = <<SQL; -SELECT CONCAT(EXTRACT (MONTH FROM transdate),'/',EXTRACT (YEAR FROM transdate)) AS date_part, - count(id) as count, - sum(amount) as amount, - sum(netamount) as netamount, - sum(paid) as paid - FROM $db WHERE $cv_type = $cv - GROUP BY EXTRACT (YEAR FROM transdate), EXTRACT (MONTH FROM transdate) - ORDER BY EXTRACT (YEAR FROM transdate) DESC, EXTRACT (MONTH FROM transdate) DESC + SELECT CONCAT(EXTRACT (MONTH FROM transdate),'/',EXTRACT (YEAR FROM transdate)) as date_part, + count(id) as count, + sum(amount) as amount, + sum(netamount) as netamount, + sum(paid) as paid + FROM $db WHERE $cv_type = ? +GROUP BY EXTRACT (YEAR FROM transdate), EXTRACT (MONTH FROM transdate) +ORDER BY EXTRACT (YEAR FROM transdate) DESC, EXTRACT (MONTH FROM transdate) DESC SQL - $self->{turnover_statistic} = selectall_hashref_query($::form, $dbh, $query); + $self->{turnover_statistic} = selectall_hashref_query($::form, $dbh, $query, $cv); $self->render('customer_vendor_turnover/count_turnover', { layout => 0 }); } @@ -138,7 +147,7 @@ sub action_turnover_by_year { 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 $cv = $::form->{id}; my ($db, $cv_type); if ($::form->{db} eq 'customer') { $db = "ar"; @@ -148,16 +157,16 @@ sub action_turnover_by_year { $cv_type = "vendor_id"; } my $query = <<SQL; -SELECT EXTRACT (YEAR FROM transdate) as date_part, - count(id) as count, - sum(amount) as amount, - sum(netamount) as netamount, - sum(paid) as paid - FROM $db WHERE $cv_type = $cv - GROUP BY date_part - ORDER BY date_part DESC + SELECT EXTRACT (YEAR FROM transdate) as date_part, + count(id) as count, + sum(amount) as amount, + sum(netamount) as netamount, + sum(paid) as paid + FROM $db WHERE $cv_type = ? +GROUP BY date_part +ORDER BY date_part DESC SQL - $self->{turnover_statistic} = selectall_hashref_query($::form, $dbh, $query); + $self->{turnover_statistic} = selectall_hashref_query($::form, $dbh, $query, $cv); $self->render('customer_vendor_turnover/count_turnover', { layout => 0 }); } @@ -166,16 +175,16 @@ sub action_get_invoices { return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id}; - my $cv = $::form->{id} || {}; + my $cv = $::form->{id}; my $invoices; if ( $::form->{db} eq 'customer' ) { $invoices = SL::DB::Manager::Invoice->get_all( - query => [ customer_id => $cv, ], + query => [ customer_id => $cv, ], sort_by => 'invnumber DESC', ); } else { $invoices = SL::DB::Manager::PurchaseInvoice->get_all( - query => [ vendor_id => $cv, ], + query => [ vendor_id => $cv, ], sort_by => 'invnumber DESC', ); } @@ -187,19 +196,23 @@ sub action_get_orders { return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id}; - my $cv = $::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') ], + 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') ], + query => [ + vendor_id => $cv, + quotation => ($type eq 'quotation' ? 'T' : 'F') + ], sort_by => ( $type eq 'order' ? 'ordnumber DESC' : 'quonumber DESC'), ); } @@ -215,154 +228,190 @@ sub _get_open_orders { 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} || {}; + 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', - ); + 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', - ); + 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') ); + 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 +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'), + 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 +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'), + 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 +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'), + 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 +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') + WHERE rc.to_table = 'email_journal' + AND rc.from_table = 'letter') -SELECT ej.*, CASE +SELECT ej.*, + CASE oec.quotation WHEN 'F' THEN 'Sales Order' ELSE 'Quotation' - END AS type, - CASE + 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 + 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 = ? + WHERE oec.id = ? UNION -SELECT ej.*, 'Delivery Order' AS type, dec.donumber AS recordnumber,dec.id AS record_id FROM email_journal ej +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 = ? + WHERE dec.id = ? UNION -SELECT ej.*, CASE +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 + 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 = ? + WHERE iec.id = ? UNION -SELECT ej.*, 'Letter' AS type, lec.letternumber AS recordnumber,lec.id AS record_id FROM email_journal ej +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 + 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 +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'), + 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 +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'), + 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 +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'), + 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 +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') + WHERE rc.to_table = 'email_journal' + AND rc.from_table = 'letter') -SELECT ej.*, CASE +SELECT ej.*, + CASE oec.quotation WHEN 'F' THEN 'Purchase Order' ELSE 'Request quotation' - END AS type, - CASE + 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 + 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 = ? + WHERE oec.id = ? UNION -SELECT ej.*, 'Purchase Delivery Order' AS type, dec.donumber AS recordnumber, dec.id AS record_id FROM email_journal ej +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 = ? + WHERE dec.id = ? UNION -SELECT ej.*, iec.type AS type, iec.invnumber AS recordnumber, iec.id AS record_id FROM email_journal ej +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 = ? + WHERE iec.id = ? UNION -SELECT ej.*, 'Letter' AS type, lec.letternumber AS recordnumber, lec.id AS record_id FROM email_journal ej +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 + WHERE lec.id = ? + ORDER BY sent_on DESC SQL } my $emails = selectall_hashref_query($::form, $dbh, $query, $cv, $cv, $cv, $cv); @@ -374,17 +423,17 @@ sub action_get_letters { return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id}; - my $cv = $::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, ], + query => [ customer_id => $cv, ], sort_by => 'date DESC', ); } else { $letters = SL::DB::Manager::Letter->get_all( - query => [ vendor_id => $cv, ], + query => [ vendor_id => $cv, ], sort_by => 'date DESC', ); } @@ -407,6 +456,8 @@ Gets all kinds of records like orders, request orders, quotations, invoices, ema wich belong to customer/vendor and displays them in an extra tab "Records". +=back + =head1 URL ACTIONS =over 4 @@ -470,3 +521,5 @@ None yet. :) =head1 AUTHOR W. Hahn E<lt>wh@futureworldsearch.netE<gt> + +=back -- 2.20.1