From 9508e215c78bc172721e14d41d21858abb23672e Mon Sep 17 00:00:00 2001 From: Werner Hahn Date: Thu, 12 Nov 2015 09:25:34 +0100 Subject: [PATCH] Kundenstatistik: erster commit ohne Webtemplates --- SL/Controller/CustomerVendor.pm | 15 ++- SL/Controller/CustomerVendorTurnover.pm | 143 ++++++++++++++++++++++++ SL/DB/Invoice.pm | 6 + js/kivi.CustomerVendor.js | 1 + locale/de/all | 8 ++ 5 files changed, 171 insertions(+), 2 deletions(-) create mode 100644 SL/Controller/CustomerVendorTurnover.pm diff --git a/SL/Controller/CustomerVendor.pm b/SL/Controller/CustomerVendor.pm index 3b8f7fc77..373f71360 100644 --- a/SL/Controller/CustomerVendor.pm +++ b/SL/Controller/CustomerVendor.pm @@ -10,6 +10,8 @@ use SL::DBUtils; use SL::Helper::Flash; use SL::Locale::String; use SL::Controller::Helper::GetModels; +use SL::Controller::Helper::ReportGenerator; +use SL::Controller::Helper::ParseFilter; use SL::DB::Customer; use SL::DB::Vendor; @@ -25,6 +27,9 @@ use SL::DB::FollowUp; use SL::DB::FollowUpLink; use SL::DB::History; use SL::DB::Currency; +use SL::DB::Invoice; + +use Data::Dumper; use Rose::Object::MakeMethods::Generic ( 'scalar --get_set_init' => [ qw(customer_models vendor_models) ], @@ -466,7 +471,6 @@ sub action_search_contact { print $::form->redirect_header($url); } - sub action_get_delivery { my ($self) = @_; @@ -941,7 +945,14 @@ sub _pre_render { ], with_objects => ['follow_up'], ); - + + $self->{open_items} = SL::DB::Manager::Invoice->get_all_count( + query => [ + customer_id => $self->{cv}->id, + paid => {lt_sql => 'amount'}, + ], + ); + $self->{template_args} ||= {}; $::request->{layout}->add_javascripts('kivi.CustomerVendor.js'); diff --git a/SL/Controller/CustomerVendorTurnover.pm b/SL/Controller/CustomerVendorTurnover.pm new file mode 100644 index 000000000..3101bbf27 --- /dev/null +++ b/SL/Controller/CustomerVendorTurnover.pm @@ -0,0 +1,143 @@ +package SL::Controller::CustomerVendorTurnover; +use strict; +use parent qw(SL::Controller::Base); +use SL::DBUtils; +use SL::DB::AccTransaction; +use SL::DB::Invoice; + +__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; + $open_invoices = SL::DB::Manager::Invoice->get_all( + query => [customer_id => $cv, + paid => {lt_sql => 'amount'}, + ], + with_objects => ['dunnings'], + ); + my $open_items; + if (@{$open_invoices}) { + return $self->render(\'', { type => 'json' }) unless scalar @{$open_invoices}; + $open_items = $self->_list_open_items($open_invoices); + } + return $self->render('customer_vendor_turnover/turnover', { header => 0 }, 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 = $::form->get_standard_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 }); +} +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 = $::form->get_standard_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); + $self->render('customer_vendor_turnover/count_open_items_by_year', { layout => 0 }); +} +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}; + + my $dbh = $::form->get_standard_dbh(); + my $cv = $::form->{id} || {}; + my $query = "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 ar WHERE customer_id = $cv + GROUP BY EXTRACT (YEAR FROM transdate), EXTRACT (MONTH FROM transdate) + ORDER BY EXTRACT (YEAR FROM transdate) DESC, EXTRACT (MONTH FROM transdate) DESC"; + + $self->{turnover_statistic} = selectall_hashref_query($::form, $dbh, $query); + $self->render('customer_vendor_turnover/count_turnover', { layout => 0 }); +} +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}; + + my $dbh = $::form->get_standard_dbh(); + my $cv = $::form->{id} || {}; + my $query = "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 ar WHERE customer_id = $cv + GROUP BY date_part + ORDER BY date_part DESC"; + + $self->{turnover_statistic} = selectall_hashref_query($::form, $dbh, $query); + $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 = SL::DB::Manager::Invoice->get_all( + query => [ customer_id => $cv, ], + sort_by => 'invnumber DESC', + ); + $self->render('customer_vendor_turnover/invoices_statistic', { layout => 0 }, invoices => $invoices); +} +sub _list_articles_by_invoice { +} +sub _list_count_articles_by_year { +} +sub check_auth { + $::auth->assert('general_ledger'); +} +1; diff --git a/SL/DB/Invoice.pm b/SL/DB/Invoice.pm index ad590c79c..c82462451 100644 --- a/SL/DB/Invoice.pm +++ b/SL/DB/Invoice.pm @@ -60,6 +60,12 @@ __PACKAGE__->meta->add_relationship( sort_by => 'acc_trans_id ASC', }, }, + dunnings => { + type => 'one to many', + class => 'SL::DB::Dunning', + column_map => { id => 'trans_id' }, + manager_args => { with_objects => [ 'dunnings' ] } + }, ); __PACKAGE__->meta->initialize; diff --git a/js/kivi.CustomerVendor.js b/js/kivi.CustomerVendor.js index e9b22bedf..d489d07d3 100644 --- a/js/kivi.CustomerVendor.js +++ b/js/kivi.CustomerVendor.js @@ -208,6 +208,7 @@ namespace('kivi.CustomerVendor', function(ns) { }; this.inline_report = function(target, source, data){ +// alert("HALLO S " + source + " --T " + target + " tt D " + data); $.ajax({ url: source, success: function (rsp) { diff --git a/locale/de/all b/locale/de/all index e7c978cff..b1d856ecd 100755 --- a/locale/de/all +++ b/locale/de/all @@ -1093,6 +1093,7 @@ $self->{texts} = { 'Dunning overview' => 'Mahnungsübersicht', 'Dunning status' => 'Mahnstatus', 'Dunnings' => 'Mahnungen', + 'Dunningstatistic' => 'Mahnstatistic', 'Duplicate in CSV file' => 'Duplikat in CSV-Datei', 'Duplicate in database' => 'Duplikat in Datenbank', 'During the next update a taxkey 0 with tax rate of 0 will automatically created.' => 'Beim nächsten Ausführen des Updates wird ein Steuerschlüssel 0 mit einem Steuersatz von 0% automatisch erzeugt.', @@ -1519,6 +1520,7 @@ $self->{texts} = { 'Hide mappings (csv_import)' => 'Spaltenzuordnungen verbergen', 'Hide settings' => 'Einstellungen verbergen', 'Hint: Not all VC Numbers are personal accounts compliant' => 'Hinweis: Nicht alle Kunden-/Lieferantennummern sind DATEV-Personenkonten kompatibel.', + 'Highest Dunninglevel' => 'Höchste Mahnstufe', 'Hints' => 'Hinweise', 'History' => 'Historie', 'History Search' => 'Historien Suche', @@ -1668,6 +1670,7 @@ $self->{texts} = { 'Invoice Field 2' => 'Belegfeld 2', 'Invoice Number' => 'Rechnungsnummer', 'Invoice Number missing!' => 'Rechnungsnummer fehlt!', + 'Invoice Total' => '', 'Invoice deleted!' => 'Rechnung gelöscht!', 'Invoice filter' => 'Rechnungsfilter', 'Invoice for fees' => 'Rechnung über Gebühren', @@ -1891,6 +1894,7 @@ $self->{texts} = { 'Module name' => 'Modulname', 'Monat' => 'Monat', 'Month' => 'Monat', + 'Month/Year' => 'Monat/Jahr', 'Monthly' => 'monatlich', 'More than one control file with the tag \'%s\' exist.' => 'Es gibt mehr als eine Kontrolldatei mit dem Tag \'%s\'.', 'More than one file selected, please set only one checkbox!' => 'Mehr als ein Element selektiert, bitte nur eine Box anklicken', @@ -2101,6 +2105,7 @@ $self->{texts} = { 'Oops. No valid action found to dispatch. Please report this case to the kivitendo team.' => 'Ups. Es wurde keine gültige Funktion zum Aufrufen gefunden. Bitte berichten Sie diesen Fall den kivitendo-Entwicklern.', 'Open' => 'Offen', 'Open Amount' => 'Offener Betrag', + 'Open Items' => 'Offene Posten', 'Open a further kivitendo window or tab' => 'Weiteres kivitendo-Fenster/-Tab öffnen', 'Open amount' => 'offener Betrag', 'Open in new window' => 'In neuem Fenster öffnen.', @@ -3594,6 +3599,9 @@ $self->{texts} = { 'Trial balance between %s and %s' => 'Summen- und Saldenlisten vom %s bis zum %s', 'Trying to call a sub without a name' => 'Es wurde versucht, eine Unterfunktion ohne Namen aufzurufen.', 'TypAbbreviation' => 'Abkürzung des Artikel-Typs', + 'Turnover' => 'Umsätze', + 'Turnovers' => 'Umsätze', + 'Turnoverstatistic' => 'Umsatzstatistik', 'Type' => 'Typ', 'Type abbreviation' => 'Typen-Abkürzung', 'Type can be either \'part\', \'service\' or \'assembly\'.' => 'Der Typ kann entweder \'part\' (für Waren), \'service\' (für Dienstleistungen) oder \'assembly\' (für Erzeugnisse) enthalten.', -- 2.20.1