projekt kivitendo » kivitendo Kundenerweiterungen

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>