From 8e99e751c3617f03dbbca3210b1bf127aceabb40 Mon Sep 17 00:00:00 2001 From: Werner Hahn Date: Fri, 7 Sep 2018 20:17:26 +0200 Subject: [PATCH] =?utf8?q?Kundenstatistik:=20offene=20Rechnungen,=20offene?= =?utf8?q?=20Auftr=C3=A4ge,=20Angebote,=20Auftr=C3=A4ge,?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Kunden und Lieferanten Kunden Angebote, Aufträge, Rechnungen Lieferanten Preisanfragen, Lieferantenaufträge, Rechnungen --- SL/Controller/CustomerVendor.pm | 42 +++++- SL/Controller/CustomerVendorTurnover.pm | 137 +++++++++++++++--- templates/webpages/customer_vendor/form.html | 4 +- .../_list_open_items.html | 4 +- .../_list_open_orders.html | 44 ++++++ .../_statistic_tabs.html | 28 +++- .../order_statistic.html | 35 +++++ .../quotation_statistic.html | 35 +++++ .../customer_vendor_turnover/turnover.html | 4 + .../turnover_statistic.html | 6 +- 10 files changed, 303 insertions(+), 36 deletions(-) create mode 100644 templates/webpages/customer_vendor_turnover/_list_open_orders.html create mode 100644 templates/webpages/customer_vendor_turnover/order_statistic.html create mode 100644 templates/webpages/customer_vendor_turnover/quotation_statistic.html diff --git a/SL/Controller/CustomerVendor.pm b/SL/Controller/CustomerVendor.pm index 373f71360..a380f4373 100644 --- a/SL/Controller/CustomerVendor.pm +++ b/SL/Controller/CustomerVendor.pm @@ -28,6 +28,8 @@ use SL::DB::FollowUpLink; use SL::DB::History; use SL::DB::Currency; use SL::DB::Invoice; +use SL::DB::PurchaseInvoice; +use SL::DB::Order; use Data::Dumper; @@ -945,14 +947,38 @@ 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'}, - ], - ); - + + if ( $self->is_vendor()) { + $self->{open_items} = SL::DB::Manager::PurchaseInvoice->get_all_count( + query => [ + vendor_id => $self->{cv}->id, + paid => {lt_sql => 'amount'}, + ], + ); + } else { + $self->{open_items} = SL::DB::Manager::Invoice->get_all_count( + query => [ + customer_id => $self->{cv}->id, + paid => {lt_sql => 'amount'}, + ], + ); + } + + if ( $self->is_vendor() ) { + $self->{open_orders} = SL::DB::Manager::Order->get_all_count( + query => [ + vendor_id => $self->{cv}->id, + closed => 'F', + ], + ); + } else { + $self->{open_orders} = SL::DB::Manager::Order->get_all_count( + query => [ + customer_id => $self->{cv}->id, + closed => 'F', + ], + ); + } $self->{template_args} ||= {}; $::request->{layout}->add_javascripts('kivi.CustomerVendor.js'); diff --git a/SL/Controller/CustomerVendorTurnover.pm b/SL/Controller/CustomerVendorTurnover.pm index bd327daa4..e705a0868 100644 --- a/SL/Controller/CustomerVendorTurnover.pm +++ b/SL/Controller/CustomerVendorTurnover.pm @@ -4,6 +4,7 @@ use parent qw(SL::Controller::Base); use SL::DBUtils; use SL::DB::AccTransaction; use SL::DB::Invoice; +use SL::DB::Order; use SL::DB; __PACKAGE__->run_before('check_auth'); @@ -15,18 +16,34 @@ sub action_list_turnover { my $cv = $::form->{id} || {}; my $open_invoices; + if ( $::form->{db} eq 'customer' ) { $open_invoices = SL::DB::Manager::Invoice->get_all( query => [customer_id => $cv, - paid => {lt_sql => 'amount'}, + 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'}, + ], + ], + sort_by => 'invnumber DESC', + ); + } 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); + 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 { @@ -39,7 +56,7 @@ 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 = SL::DB->client->dbh() + my $dbh = SL::DB->client->dbh; my $cv = $::form->{id} || {}; @@ -63,7 +80,7 @@ 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 = SL::DB->client->dbh() + my $dbh = SL::DB->client->dbh; my $cv = $::form->{id} || {}; @@ -88,52 +105,134 @@ 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 $dbh = SL::DB->client->dbh; my $cv = $::form->{id} || {}; - my $query = "SELECT CONCAT(EXTRACT (MONTH FROM transdate),'/',EXTRACT (YEAR FROM transdate)) AS date_part, + my ($db, $cv_type); + if ($::form->{db} eq 'customer') { + $db = "ar"; + $cv_type = "customer_id"; + } else { + $db = "ap"; + $cv_type = "vendor_id"; + } + my $query = <{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 = SL::DB->client->dbh() + my $dbh = SL::DB->client->dbh; my $cv = $::form->{id} || {}; - my $query = "SELECT EXTRACT (YEAR FROM transdate) as date_part, + my ($db, $cv_type); + if ($::form->{db} eq 'customer') { + $db = "ar"; + $cv_type = "customer_id"; + } else { + $db = "ap"; + $cv_type = "vendor_id"; + } + my $query = <{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', - ); + 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', + ); + } $self->render('customer_vendor_turnover/invoices_statistic', { layout => 0 }, invoices => $invoices); } + +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') ); +} + sub _list_articles_by_invoice { } sub _list_count_articles_by_year { diff --git a/templates/webpages/customer_vendor/form.html b/templates/webpages/customer_vendor/form.html index ae05d85c2..fc709430e 100644 --- a/templates/webpages/customer_vendor/form.html +++ b/templates/webpages/customer_vendor/form.html @@ -2,8 +2,8 @@ [%- USE HTML %] [%- USE LxERP %] [%- USE L %] +[%- USE Dumper %]

[% FORM.title %] [% IF SELF.cv.id %] - [% HTML.escape(SELF.cv.displayable_name) %][% END %]

- [% L.hidden_tag('_cti_enabled', !!LXCONFIG.cti.dial_command) %] [% cv_cvars = SELF.cv.cvars_by_config %] @@ -36,7 +36,7 @@ [% IF SELF.cv.id %]
  • [% 'Price Rules' | $T8 %]
    • [% END %] -
  • [% LxERP.t8('Turnovers') %] [%- IF SELF.open_items > 0 %]   $  [% END %]
    • +
  • [% LxERP.t8('Records') %] [%- IF SELF.open_items > 0 %]  $[% END %] [%- IF SELF.open_orders > 0 %]  ![% END %]
    • [% 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 index 51eb72166..0f62e6eaa 100644 --- a/templates/webpages/customer_vendor_turnover/_list_open_items.html +++ b/templates/webpages/customer_vendor_turnover/_list_open_items.html @@ -2,10 +2,10 @@ [%- USE LxERP %] [%- USE L %] [%- USE HTML %] -
    [%- HTML.escape(title) %]
    + @@ -36,4 +36,4 @@ [% END %]
    [%- HTML.escape(title) %]
    [% 'Invoice Number' | $T8 %]
    -
    + diff --git a/templates/webpages/customer_vendor_turnover/_list_open_orders.html b/templates/webpages/customer_vendor_turnover/_list_open_orders.html new file mode 100644 index 000000000..cbde1746e --- /dev/null +++ b/templates/webpages/customer_vendor_turnover/_list_open_orders.html @@ -0,0 +1,44 @@ +[%- USE T8 %] +[%- USE LxERP %] +[%- USE L %] +[%- USE HTML %] + +
    + + + + + + + + + + + + + [%- FOREACH row = orders %] + + + [%- IF INSTANCE_CONF.get_feature_experimental -%] + + [%- ELSE -%] + + [%- END -%] + [% ELSE %] + [% IF FORM.db == 'customer' %][% 'Sales Order' | $T8 %][% ELSE %][% 'Purchase Order' | $T8 %][% END %] + [%- IF INSTANCE_CONF.get_feature_experimental -%] + + [%- ELSE -%] + + [%- END -%] + [% END %] + + + + + + [% END %] + +
    [%- HTML.escape(title) %]
    [% 'Type' | $T8 %][% 'Order/RFQ Number' | $T8 %][% 'Date' | $T8 %][% 'Amount' | $T8 %][% 'Saleman' | $T8 %][% 'Transaction description' | $T8 %]
    [% IF row.quotation %] + [% IF FORM.db == 'customer' %][% 'Sales quotation' | $T8 %][% ELSE %][% 'RFQ' | $T8 %][% END %][% IF row.id %][% END %][% HTML.escape(row.quonumber) || ' ' %][% IF row.id %][% END %][% IF row.id %][% END %][% HTML.escape(row.quonumber) || ' ' %][% IF row.id %][% END %][% IF row.id %][% END %][% HTML.escape(row.ordnumber) || ' ' %][% IF row.id %][% END %][% IF row.id %][% END %][% HTML.escape(row.ordnumber) || ' ' %][% IF row.id %][% END %][% row.transdate.to_kivitendo | html %][%- LxERP.format_amount(row.amount, 2) %][% row.employee.name | html %][% row.transaction_description | html %]
    +
    diff --git a/templates/webpages/customer_vendor_turnover/_statistic_tabs.html b/templates/webpages/customer_vendor_turnover/_statistic_tabs.html index 06bcb8c78..32bb9f559 100644 --- a/templates/webpages/customer_vendor_turnover/_statistic_tabs.html +++ b/templates/webpages/customer_vendor_turnover/_statistic_tabs.html @@ -7,17 +7,41 @@ $ ( "#statistic_tabs" ).tabs(); }); function get_invoices() { - var url = 'controller.pl?action=CustomerVendorTurnover/get_invoices&id=' + $('#cv_id').val(); + var url = 'controller.pl?action=CustomerVendorTurnover/get_invoices&id=' + $('#cv_id').val() + '&db=' + $('#db').val(); $('#invoices').load(url); } + function get_sales_quotations() { + var url = 'controller.pl?action=CustomerVendorTurnover/get_orders&id=' + $('#cv_id').val() + '&db=' + $('#db').val() + '&type=quotation'; + $('#quotations').load(url); + } + function get_orders() { + var url = 'controller.pl?action=CustomerVendorTurnover/get_orders&id=' + $('#cv_id').val() + '&db=' + $('#db').val() + '&type=order'; + $('#orders').load(url); + } + function get_letters() { + var url = 'controller.pl?action=CustomerVendorTurnover/get_letters&id=' + $('#cv_id').val() + '&db=' + $('#db').val();; + $('#letters').load(url); + } + function get_mails() { + var url = 'controller.pl?action=CustomerVendorTurnover/get_mails&id=' + $('#cv_id').val() + '&db=' + $('#db').val();; + $('#mails').load(url); + }
    [% PROCESS "customer_vendor_turnover/turnover_statistic.html" %]
    [% PROCESS "customer_vendor_turnover/dun_statistic.html" %]
    +
    +
    +
    +
    diff --git a/templates/webpages/customer_vendor_turnover/order_statistic.html b/templates/webpages/customer_vendor_turnover/order_statistic.html new file mode 100644 index 000000000..2d52392a6 --- /dev/null +++ b/templates/webpages/customer_vendor_turnover/order_statistic.html @@ -0,0 +1,35 @@ +[%- USE T8 %] +[%- USE LxERP %] +[%- USE L %] +[%- USE HTML %] +
    + + + + + + + + + + + [%- FOREACH row = orders %] + + [%- IF INSTANCE_CONF.get_feature_experimental -%] + + [%- ELSE -%] + + [%- END -%] + + + + + + [% END %] + +
    [% 'Order Number' | $T8 %][% 'Order Date' | $T8 %][% 'Amount' | $T8 %][% 'Delivery Date' | $T8 %][% 'Transaction description' | $T8 %]
    [% IF row.id %][% END %][% HTML.escape(row.ordnumber) || ' ' %][% IF row.id %][% END %][% IF row.id %][% END %][% HTML.escape(row.ordnumber) || ' ' %][% IF row.id %][% END %][% row.transdate.to_kivitendo | html %][%- LxERP.format_amount(row.amount, 2) %][% row.reqdate.to_kivitendo | html %][% row.transaction_description %]
    + + + + +
    diff --git a/templates/webpages/customer_vendor_turnover/quotation_statistic.html b/templates/webpages/customer_vendor_turnover/quotation_statistic.html new file mode 100644 index 000000000..03c595a3a --- /dev/null +++ b/templates/webpages/customer_vendor_turnover/quotation_statistic.html @@ -0,0 +1,35 @@ +[%- USE T8 %] +[%- USE LxERP %] +[%- USE L %] +[%- USE HTML %] +
    + + + + + + + + + + + [%- FOREACH row = orders %] + + [%- IF INSTANCE_CONF.get_feature_experimental -%] + + [%- ELSE -%] + + [%- END -%] + + + + + + [% END %] + +
    [% IF FORM.db == 'customer' %][% 'Quotation Number' | $T8 %][% ELSE %][% 'RFQ Number' | $T8 %][% END %][% IF FORM.db == 'customer' %][% 'Quotation Date' | $T8 %][% ELSE %][% 'RFQ Date' | $T8 %][% END %][% 'Amount' | $T8 %][% 'Delivery Date' | $T8 %][% 'Transaction description' | $T8 %]
    [% IF row.id %][% END %][% HTML.escape(row.quonumber) || ' ' %][% IF row.id %][% END %][% IF row.id %][% END %][% HTML.escape(row.quonumber) || ' ' %][% IF row.id %][% END %][% row.transdate.to_kivitendo | html %][%- LxERP.format_amount(row.amount, 2) %][% row.reqdate.to_kivitendo | html %][% row.transaction_description %]
    + + + + +
    diff --git a/templates/webpages/customer_vendor_turnover/turnover.html b/templates/webpages/customer_vendor_turnover/turnover.html index 310355d48..28c8264e4 100644 --- a/templates/webpages/customer_vendor_turnover/turnover.html +++ b/templates/webpages/customer_vendor_turnover/turnover.html @@ -8,4 +8,8 @@ [% open_items %] [% END %] +[%- IF open_orders %] +[% open_orders %] +[% 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 index c2ea10ec5..0ee39bf57 100644 --- a/templates/webpages/customer_vendor_turnover/turnover_statistic.html +++ b/templates/webpages/customer_vendor_turnover/turnover_statistic.html @@ -2,7 +2,7 @@ [%- USE LxERP %] [%- USE L %] [%- USE HTML %] -

    +

    [% 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");') %] @@ -10,10 +10,10 @@