Projekt

Allgemein

Profil

Herunterladen (19,9 KB)

Umsatzstatistik als Reiter in der Kundendatei » diffStatistiken2.txt

patch - Werner Hahn, 18.06.2015 09:57

 
diff --git a/SL/Controller/CustomerVendor.pm b/SL/Controller/CustomerVendor.pm
index 403413f..17b0c4b 100644
--- a/SL/Controller/CustomerVendor.pm
+++ b/SL/Controller/CustomerVendor.pm
@@ -8,6 +8,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;
@@ -23,6 +25,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) ],
@@ -427,7 +432,6 @@ sub action_search_contact {
print $::form->redirect_header($url);
}
-
sub action_get_delivery {
my ($self) = @_;
@@ -913,7 +917,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('autocomplete_customer.js');
diff --git a/SL/Controller/CustomerVendorTurnover.pm b/SL/Controller/CustomerVendorTurnover.pm
new file mode 100644
index 0000000..3101bbf
--- /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 065d2b3..337d550 100644
--- a/SL/DB/Invoice.pm
+++ b/SL/DB/Invoice.pm
@@ -54,6 +54,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/locale/de/all b/locale/de/all
index 7ef64ac..47749c7 100755
--- a/locale/de/all
+++ b/locale/de/all
@@ -958,6 +958,7 @@ $self->{texts} = {
'Dunning number' => 'Mahnungsnummer',
'Dunning overview' => 'Mahnungsübersicht',
'Dunnings' => 'Mahnungen',
+ 'Dunningstatistic' => 'Mahnstatistik',
'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.',
@@ -1293,6 +1294,7 @@ $self->{texts} = {
'Hide chart details' => 'Konteninformation verstecken',
'Hide help text' => 'Hilfetext verbergen',
'Hide settings' => 'Einstellungen verbergen',
+ 'Highest Dunninglevel' => 'Höchste Mahnstufe',
'Hints' => 'Hinweise',
'History' => 'Historie',
'History Search' => 'Historien Suche',
@@ -1617,6 +1619,7 @@ $self->{texts} = {
'Module name' => 'Modulname',
'Monat' => 'Monat',
'Month' => 'Monat',
+ 'Month/Year' => 'Monat/Jahr',
'Monthly' => 'monatlich',
'More than one #1 found matching, please be more specific.' => 'Mehr als ein #1 wurde gefunden, bitte geben Sie den Namen genauer an.',
'More than one control file with the tag \'%s\' exist.' => 'Es gibt mehr als eine Kontrolldatei mit dem Tag \'%s\'.',
@@ -1632,6 +1635,7 @@ $self->{texts} = {
'Net Income Statement' => 'Einnahmenüberschußrechnung',
'Net amount' => 'Nettobetrag',
'Net amount (for verification)' => 'Nettobetrag (zur Überprüfung)',
+ 'Net.Turnover' => 'Netto Umsatz',
'Netto Terms' => 'Zahlungsziel netto',
'New Password' => 'Neues Passwort',
'New Purchase Price Rule' => 'Neue Einkaufspreisregel',
@@ -1783,6 +1787,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.',
@@ -2983,6 +2988,9 @@ $self->{texts} = {
'Trial Balance' => 'Summen- und Saldenliste',
'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.',
+ 'Turnover' => 'Umsatz',
+ 'Turnovers' => 'Umsätze',
+ 'Turnoverstatistic' => 'Umsatzstatistik',
'Type' => 'Typ',
'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.',
'Type of Business' => 'Kunden-/Lieferantentyp',
diff --git a/templates/webpages/customer_vendor/form.html b/templates/webpages/customer_vendor/form.html
index 356bb1f..6572464 100644
--- a/templates/webpages/customer_vendor/form.html
+++ b/templates/webpages/customer_vendor/form.html
@@ -1,7 +1,7 @@
[%- USE T8 %]
[%- USE LxERP %]
[%- USE L %]
-<h1>[% FORM.title %]</h1>
+<h1>[% FORM.title %] -- [% SELF.cv.name %], [% SELF.cv.street %], [% SELF.cv.zipcode %] [% SELF.cv.city %]</h1>
[% L.hidden_tag('_cti_enabled', !!LXCONFIG.cti.dial_command) %]
@@ -32,6 +32,7 @@
[% IF SELF.cv.id %]
<li><a href="#price_rules">[% 'Price Rules' | $T8 %]</a></li>
[% END %]
+ <li><a href="[% 'controller.pl?action=CustomerVendorTurnover/list_turnover&id=' _ SELF.cv.id | html %]">[% LxERP.t8('Turnovers') %] [%- IF SELF.open_items > 0 %] <span style="background-color:red;color:black;">&nbsp; $&nbsp; </span>[% END %]</a></li>
</ul>
[% PROCESS "customer_vendor/tabs/billing.html" %]
diff --git a/templates/webpages/customer_vendor_turnover/_list_open_items.html b/templates/webpages/customer_vendor_turnover/_list_open_items.html
new file mode 100644
index 0000000..6953adc
--- /dev/null
+++ b/templates/webpages/customer_vendor_turnover/_list_open_items.html
@@ -0,0 +1,39 @@
+[%- USE T8 %]
+[%- USE LxERP %]
+[%- USE L %]
+[%- USE HTML %]
+<div class="listtop">[%- HTML.escape(title) %]</div>
+
+<div id="sales_report">
+ <table width="100%">
+ <tbody>
+ <tr>
+ <td class="listheading">[% 'Invoice Number' | $T8 %]</td>
+ <td class="listheading">[% 'Invoice Date' | $T8 %]</td>
+ <td class="listheading">[% 'Amount' | $T8 %]</td>
+ <td class="listheading">[% 'Inv. Duedate' | $T8 %]</td>
+ <td class="listheading">[% 'Paid' | $T8 %]</td>
+ <td class="listheading">[% 'Open Amount' | $T8 %]</td>
+ <td class="listheading">[% 'Dunnings' | $T8 %]</td>
+ </tr>
+
+ [%- FOREACH row = OPEN_ITEMS %]
+ <tr class="listrow[% loop.count % 2 %]">
+ <td>[% row.invnumber | html %]</td>
+ <td>[% row.transdate.to_kivitendo | html %]</td>
+ <td>[%- LxERP.format_amount(row.amount, 2) %]</td>
+ <td>[% row.duedate.to_kivitendo | html %]</td>
+ <td>[%- LxERP.format_amount(row.paid, 2) %]</td>
+ <td>[%- LxERP.format_amount(row.amount - row.paid, 2) %]
+ <td>
+ [%- IF row.dunning_config_id != '' %]
+ [%- FOREACH dun = row.dunnings %]
+ [% dun.dunning_id | html %] -- [% dun.duedate.to_kivitendo | html %] -- [% dun.dunning_level | html %] -- [% LxERP.format_amount(dun.fee, 2) %]<br>
+ [% END %]
+ [% END %]
+ </td>
+ </tr>
+ [% END %]
+ </tbody>
+ </table>
+</div>
diff --git a/templates/webpages/customer_vendor_turnover/_statistic_tabs.html b/templates/webpages/customer_vendor_turnover/_statistic_tabs.html
new file mode 100644
index 0000000..ce34e2a
--- /dev/null
+++ b/templates/webpages/customer_vendor_turnover/_statistic_tabs.html
@@ -0,0 +1,23 @@
+[%- USE T8 %]
+[%- USE LxERP %]
+[%- USE L %]
+[%- USE HTML %]
+<script type="text/javascript">
+ $(function() {
+ $ ( "#statistic_tabs" ).tabs();
+ });
+ function get_invoices() {
+ var url = 'controller.pl?action=CustomerVendorTurnover/get_invoices&id=' + $('#cv_id').val();
+ $('#invoices').load(url);
+ }
+</script>
+ <div class="tabwidget" id="statistic_tabs">
+ <ul>
+ <li><a href="#turnover_stat">[% 'Turnoverstatistic' | $T8 %]</a></li>
+ <li><a href="#dun_stat">[% 'Dunningstatistic' | $T8 %]</a></li>
+ <li><a href="#invoices" onclick="get_invoices();">[% 'Invoices' | $T8 %]</a></li>
+ </ul>
+ <div id="turnover_stat">[% PROCESS "customer_vendor_turnover/turnover_statistic.html" %]</div>
+ <div id="dun_stat">[% PROCESS "customer_vendor_turnover/dun_statistic.html" %]</div>
+ <div id="invoices"></div>
+ </div>
diff --git a/templates/webpages/customer_vendor_turnover/count_open_items_by_year.html b/templates/webpages/customer_vendor_turnover/count_open_items_by_year.html
new file mode 100644
index 0000000..8309b05
--- /dev/null
+++ b/templates/webpages/customer_vendor_turnover/count_open_items_by_year.html
@@ -0,0 +1,23 @@
+[%- USE T8 %]
+[%- USE LxERP %]
+[%- USE L %]
+[%- USE HTML %]
+<div id="dun_statistic">
+ <table width="100%">
+ <tbody>
+ <tr>
+ <td class="listheading">[% 'Month/Year' | $T8 %]</td>
+ <td class="listheading">[% 'Dunnings' | $T8 %]</td>
+ <td class="listheading">[% 'Highest Dunninglevel' | $T8 %]</td>
+ </tr>
+
+ [%- FOREACH row = SELF.dun_statistic %]
+ <tr class="listrow[% loop.count % 2 %]">
+ <td>[% row.date_part | html %]</td>
+ <td>[% row.count | html %]</td>
+ <td>[% row.max | html %]</td>
+ </tr>
+ [% END %]
+ </tbody>
+ </table>
+</div>
diff --git a/templates/webpages/customer_vendor_turnover/count_turnover.html b/templates/webpages/customer_vendor_turnover/count_turnover.html
new file mode 100644
index 0000000..cf793e2
--- /dev/null
+++ b/templates/webpages/customer_vendor_turnover/count_turnover.html
@@ -0,0 +1,27 @@
+[%- USE T8 %]
+[%- USE LxERP %]
+[%- USE L %]
+[%- USE HTML %]
+<div id="turnover_statistic">
+ <table width="100%">
+ <tbody>
+ <tr>
+ <td class="listheading">[% 'Month/Year' | $T8 %]</td>
+ <td class="listheading">[% 'Invoices' | $T8 %]</td>
+ <td class="listheading">[% 'Turnover' | $T8 %]</td>
+ <td class="listheading">[% 'Net.Turnover' | $T8 %]</td>
+ <td class="listheading">[% 'Paid' | $T8 %]</td>
+ </tr>
+ [%- FOREACH row = SELF.turnover_statistic %]
+ <tr class="listrow[% loop.count % 2 %]">
+ <td>[% row.date_part | html %]</td>
+ <td>[% row.count | html %]</td>
+ <td>[%- LxERP.format_amount(row.amount,2) %]</td>
+ <td>[%- LxERP.format_amount(row.netamount,2) %]</td>
+ <td>[%- LxERP.format_amount(row.paid,2) %]</td>
+ </tr>
+ [% END %]
+
+ </tbody>
+ </table>
+</div>
diff --git a/templates/webpages/customer_vendor_turnover/dun_statistic.html b/templates/webpages/customer_vendor_turnover/dun_statistic.html
new file mode 100644
index 0000000..7d46bf9
--- /dev/null
+++ b/templates/webpages/customer_vendor_turnover/dun_statistic.html
@@ -0,0 +1,22 @@
+[%- USE T8 %]
+[%- USE LxERP %]
+[%- USE L %]
+[%- USE HTML %]
+
+<p>
+[% L.radio_button_tag('period', value='year', label= LxERP.t8('Year'), onclick='show_dun_stat("y");') %]
+
+[% L.radio_button_tag('period', value='month', label= LxERP.t8('Month'), onclick='show_dun_stat("m");') %]
+</p>
+<script type="text/javascript">
+ function show_dun_stat(period) {
+ if (period === 'y') {
+ var url = 'controller.pl?action=CustomerVendorTurnover/count_open_items_by_year&id=' + $('#cv_id').val();
+ $('#duns').load(url);
+ } else {
+ var url = 'controller.pl?action=CustomerVendorTurnover/count_open_items_by_month&id=' + $('#cv_id').val();
+ $('#duns').load(url);
+ }
+ }
+</script>
+<div id="duns"></div>
diff --git a/templates/webpages/customer_vendor_turnover/invoices_statistic.html b/templates/webpages/customer_vendor_turnover/invoices_statistic.html
new file mode 100644
index 0000000..7b8f3d9
--- /dev/null
+++ b/templates/webpages/customer_vendor_turnover/invoices_statistic.html
@@ -0,0 +1,33 @@
+[%- USE T8 %]
+[%- USE LxERP %]
+[%- USE L %]
+[%- USE HTML %]
+<div id="invoice_statistic">
+ <table width="100%">
+ <tbody>
+ <tr>
+ <td class="listheading">[% 'Invoice Number' | $T8 %]</td>
+ <td class="listheading">[% 'Invoice Date' | $T8 %]</td>
+ <td class="listheading">[% 'Amount' | $T8 %]</td>
+ <td class="listheading">[% 'Inv. Duedate' | $T8 %]</td>
+ <td class="listheading">[% 'Paid' | $T8 %]</td>
+ <td class="listheading">[% 'Open Amount' | $T8 %]</td>
+ </tr>
+
+ [%- FOREACH row = invoices %]
+ <tr class="listrow[% loop.count % 2 %]">
+ <td>[% row.invnumber | html %]</td>
+ <td>[% row.transdate.to_kivitendo | html %]</td>
+ <td>[%- LxERP.format_amount(row.amount, 2) %]</td>
+ <td>[% row.duedate.to_kivitendo | html %]</td>
+ <td>[%- LxERP.format_amount(row.paid, 2) %]</td>
+ <td>[%- LxERP.format_amount(row.amount - row.paid, 2) %]
+ </tr>
+ [% END %]
+ </tbody>
+ </table>
+
+
+ </tbody>
+ </table>
+</div>
diff --git a/templates/webpages/customer_vendor_turnover/turnover.html b/templates/webpages/customer_vendor_turnover/turnover.html
new file mode 100644
index 0000000..310355d
--- /dev/null
+++ b/templates/webpages/customer_vendor_turnover/turnover.html
@@ -0,0 +1,11 @@
+[%- USE T8 %]
+[%- USE LxERP %]
+[%- USE L %]
+[%- USE HTML %]
+[%- USE JavaScript -%]
+
+[%- IF open_items %]
+[% open_items %]
+[% END %]
+
+[% PROCESS "customer_vendor_turnover/_statistic_tabs.html" %]
diff --git a/templates/webpages/customer_vendor_turnover/turnover_statistic.html b/templates/webpages/customer_vendor_turnover/turnover_statistic.html
new file mode 100644
index 0000000..c2ea10e
--- /dev/null
+++ b/templates/webpages/customer_vendor_turnover/turnover_statistic.html
@@ -0,0 +1,21 @@
+[%- USE T8 %]
+[%- USE LxERP %]
+[%- USE L %]
+[%- USE HTML %]
+<p>
+[% L.radio_button_tag('period', value='year', label= LxERP.t8('Year'), onclick='show_turnover_stat("y");') %]
+
+[% L.radio_button_tag('period', value='month', label= LxERP.t8('Month'), onclick='show_turnover_stat("m");') %]
+</p>
+<script type="text/javascript">
+ function show_turnover_stat(period) {
+ if (period === 'y') {
+ var url = 'controller.pl?action=CustomerVendorTurnover/turnover_by_year&id=' + $('#cv_id').val();
+ $('#turnovers').load(url);
+ } else {
+ var url = 'controller.pl?action=CustomerVendorTurnover/turnover_by_month&id=' + $('#cv_id').val();
+ $('#turnovers').load(url);
+ }
+ }
+</script>
+<div id="turnovers"></div>
(1-1/2)