From 3da73190b8f201bfb33e3b6d96bbc6330142e922 Mon Sep 17 00:00:00 2001 From: Moritz Bunkus Date: Mon, 23 Jun 2014 16:53:14 +0200 Subject: [PATCH] Angebote: neue Felder Auftragswahrscheinlichkeit, Voraus. Abrechnungsdatum --- SL/DB/MetaSetup/Order.pm | 2 ++ SL/OE.pm | 20 +++++++++++++ bin/mozilla/oe.pl | 30 +++++++++++++++++-- locale/de/all | 10 +++++-- ...rder_probability_expected_billing_date.sql | 12 ++++++++ templates/webpages/oe/form_header.html | 14 +++++++++ templates/webpages/oe/search.html | 27 +++++++++++++++++ 7 files changed, 110 insertions(+), 5 deletions(-) create mode 100644 sql/Pg-upgrade2/sales_quotation_order_probability_expected_billing_date.sql diff --git a/SL/DB/MetaSetup/Order.pm b/SL/DB/MetaSetup/Order.pm index 7f9357313..92bb92ffd 100644 --- a/SL/DB/MetaSetup/Order.pm +++ b/SL/DB/MetaSetup/Order.pm @@ -21,6 +21,7 @@ __PACKAGE__->meta->columns( delivery_vendor_id => { type => 'integer' }, department_id => { type => 'integer' }, employee_id => { type => 'integer' }, + expected_billing_date => { type => 'date' }, globalproject_id => { type => 'integer' }, id => { type => 'integer', not_null => 1, sequence => 'id' }, intnotes => { type => 'text' }, @@ -31,6 +32,7 @@ __PACKAGE__->meta->columns( mtime => { type => 'timestamp' }, netamount => { type => 'numeric', precision => 15, scale => 5 }, notes => { type => 'text' }, + order_probability => { type => 'integer', default => '0', not_null => 1 }, ordnumber => { type => 'text', not_null => 1 }, payment_id => { type => 'integer' }, proforma => { type => 'boolean', default => 'false' }, diff --git a/SL/OE.pm b/SL/OE.pm index e36a635ee..01e930cbf 100644 --- a/SL/OE.pm +++ b/SL/OE.pm @@ -115,6 +115,7 @@ sub transactions { qq| ct.${vc}number AS vcnumber, ct.country, ct.ustid, ct.business_id, | . qq| tz.description AS taxzone | . $periodic_invoices_columns . + qq| , o.order_probability, o.expected_billing_date, (o.netamount * o.order_probability / 100) AS expected_netamount | . qq|FROM oe o | . qq|JOIN $vc ct ON (o.${vc}_id = ct.id) | . qq|LEFT JOIN employee e ON (o.employee_id = e.id) | . @@ -243,6 +244,22 @@ SQL $query .= qq| AND ${not} COALESCE(pcfg.active, 'f')|; } + if (($form->{order_probability_value} || '') ne '') { + my $op = $form->{order_probability_value} eq 'le' ? '<=' : '>='; + $query .= qq| AND (o.order_probability ${op} ?)|; + push @values, $form->{order_probability_value}; + } + + if ($form->{expected_billing_date_from}) { + $query .= qq| AND (o.expected_billing_date >= ?)|; + push @values, conv_date($form->{expected_billing_date_from}); + } + + if ($form->{expected_billing_date_to}) { + $query .= qq| AND (o.expected_billing_date <= ?)|; + push @values, conv_date($form->{expected_billing_date_to}); + } + my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC'; my $sortorder = join(', ', map { "${_} ${sortdir} " } ("o.id", $form->sort_columns("transdate", $ordnumber, "name"))); my %allowed_sort_columns = ( @@ -561,6 +578,7 @@ sub save { delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?, taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,delivery_term_id = ?, globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ? + , order_probability = ?, expected_billing_date = ? WHERE id = ?|; @values = ($form->{ordnumber} || '', $form->{quonumber}, @@ -581,6 +599,7 @@ sub save { conv_i($form->{salesman_id}), conv_i($form->{cp_id}), $form->{transaction_description}, $form->{marge_total} * 1, $form->{marge_percent} * 1, + $form->{order_probability} * 1, conv_date($form->{expected_billing_date}), conv_i($form->{id})); do_query($form, $dbh, $query, @values); @@ -811,6 +830,7 @@ sub retrieve { d.description AS department, o.payment_id, o.language_id, o.taxzone_id, o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id, o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id + , o.order_probability, o.expected_billing_date FROM oe o JOIN ${vc} cv ON (o.${vc}_id = cv.id) LEFT JOIN employee e ON (o.employee_id = e.id) diff --git a/bin/mozilla/oe.pl b/bin/mozilla/oe.pl index 12a74464a..2d82b9d17 100644 --- a/bin/mozilla/oe.pl +++ b/bin/mozilla/oe.pl @@ -479,6 +479,8 @@ sub form_header { is_pur_ord => scalar ($form->{type} =~ /purchase_order$/), ); + $TMPL_VAR{ORDER_PROBABILITIES} = [ map { { title => ($_ * 10) . '%', id => $_ * 10 } } (0..10) ]; + print $form->parse_html_template("oe/form_header", { %TMPL_VAR }); $main::lxdebug->leave_sub(); @@ -756,6 +758,8 @@ sub search { # constants and subs for template $form->{vc_keys} = sub { "$_[0]->{name}--$_[0]->{id}" }; + $form->{ORDER_PROBABILITIES} = [ map { { title => ($_ * 10) . '%', id => $_ * 10 } } (0..10) ]; + $form->header(); print $form->parse_html_template('oe/search', { @@ -823,6 +827,7 @@ sub orders { "vcnumber", "ustid", "country", "shippingpoint", "taxzone", + "order_probability", "expected_billing_date", "expected_netamount", ); # only show checkboxes if gotten here via sales_order form. @@ -835,6 +840,8 @@ sub orders { $form->{l_delivered} = "Y" if ($form->{delivered} && $form->{notdelivered}); $form->{l_periodic_invoices} = "Y" if ($form->{periodic_invoices_active} && $form->{periodic_invoices_inactive}); + map { $form->{"l_${_}"} = 'Y' } qw(order_probability expected_billing_date expected_netamount) if $form->{l_order_probability_expected_billing_date}; + my $attachment_basename; if ($form->{vc} eq 'vendor') { if ($form->{type} eq 'purchase_order') { @@ -861,7 +868,8 @@ sub orders { push @hidden_variables, "l_subtotal", $form->{vc}, qw(l_closed l_notdelivered open closed delivered notdelivered ordnumber quonumber cusordnumber transaction_description transdatefrom transdateto type vc employee_id salesman_id reqdatefrom reqdateto projectnumber project_id periodic_invoices_active periodic_invoices_inactive - business_id shippingpoint taxzone_id); + business_id shippingpoint taxzone_id + order_probability_op order_probability_value expected_billing_date_from expected_billing_date_to); my @keys_for_url = grep { $form->{$_} } @hidden_variables; push @keys_for_url, 'taxzone_id' if $form->{taxzone_id} ne ''; # taxzone_id could be 0 @@ -899,6 +907,9 @@ sub orders { 'periodic_invoices' => { 'text' => $locale->text('Per. Inv.'), }, 'shippingpoint' => { 'text' => $locale->text('Shipping Point'), }, 'taxzone' => { 'text' => $locale->text('Steuersatz'), }, + 'order_probability' => { 'text' => $locale->text('Order probability'), }, + 'expected_billing_date' => { 'text' => $locale->text('Exp. bill. date'), }, + 'expected_netamount' => { 'text' => $locale->text('Exp. netamount'), }, ); foreach my $name (qw(id transdate reqdate quonumber ordnumber cusordnumber name employee salesman shipvia transaction_description shippingpoint taxzone)) { @@ -906,7 +917,7 @@ sub orders { $column_defs{$name}->{link} = $href . "&sort=$name&sortdir=$sortdir"; } - my %column_alignment = map { $_ => 'right' } qw(netamount tax amount curr remaining_amount remaining_netamount); + my %column_alignment = map { $_ => 'right' } qw(netamount tax amount curr remaining_amount remaining_netamount order_probability expected_billing_date expected_netamount); $form->{"l_type"} = "Y"; map { $column_defs{$_}->{visible} = $form->{"l_${_}"} ? 1 : 0 } @columns; @@ -952,6 +963,16 @@ sub orders { push @options, $locale->text('Steuersatz') . " : " . SL::DB::TaxZone->new(id => $form->{taxzone_id})->load->description; } + if (($form->{order_probability_value} || '') ne '') { + push @options, $::locale->text('Order probability') . ' ' . ($form->{order_probability_op} eq 'le' ? '<=' : '>=') . ' ' . $form->{order_probability_value} . '%'; + } + + if ($form->{expected_billing_date_from} or $form->{expected_billing_date_to}) { + push @options, $locale->text('Expected billing date'); + push @options, $locale->text('From') . " " . $locale->date(\%myconfig, $form->{expected_billing_date_from}, 1) if $form->{expected_billing_date_from}; + push @options, $locale->text('Bis') . " " . $locale->date(\%myconfig, $form->{expected_billing_date_to}, 1) if $form->{expected_billing_date_to}; + } + $report->set_options('top_info_text' => join("\n", @options), 'raw_top_info_text' => $form->parse_html_template('oe/orders_top'), 'raw_bottom_info_text' => $form->parse_html_template('oe/orders_bottom', { 'SHOW_CONTINUE_BUTTON' => $allow_multiple_orders }), @@ -969,6 +990,7 @@ sub orders { my $callback = $form->escape($href); my @subtotal_columns = qw(netamount amount marge_total marge_percent remaining_amount remaining_netamount); + push @subtotal_columns, 'expected_netamount' if $form->{l_order_probability_expected_billing_date}; my %totals = map { $_ => 0 } @subtotal_columns; my %subtotals = map { $_ => 0 } @subtotal_columns; @@ -991,7 +1013,9 @@ sub orders { $subtotals{marge_percent} = $subtotals{netamount} ? ($subtotals{marge_total} * 100 / $subtotals{netamount}) : 0; $totals{marge_percent} = $totals{netamount} ? ($totals{marge_total} * 100 / $totals{netamount} ) : 0; - map { $oe->{$_} = $form->format_amount(\%myconfig, $oe->{$_}, 2) } qw(netamount tax amount marge_total marge_percent remaining_amount remaining_netamount); + map { $oe->{$_} = $form->format_amount(\%myconfig, $oe->{$_}, 2) } qw(netamount tax amount marge_total marge_percent remaining_amount remaining_netamount expected_netamount); + + $oe->{order_probability} = ($oe->{order_probability} || 0) . '%'; my $row = { }; diff --git a/locale/de/all b/locale/de/all index c5c1e83c3..09d879988 100755 --- a/locale/de/all +++ b/locale/de/all @@ -1047,7 +1047,10 @@ $self->{texts} = { 'Existing file on server' => 'Auf dem Server existierende Datei', 'Existing pending follow-ups for this item' => 'Noch nicht erledigte Wiedervorlagen für dieses Dokument', 'Existing profiles' => 'Existierende Profile', + 'Exp. bill. date' => 'Vorauss. Abr.datum', + 'Exp. netamount' => 'Vorauss. Summe', 'Expected Tax' => 'Erwartete Steuern', + 'Expected billing date' => 'Voraussichtliches Abrechnungsdatum', 'Expense' => 'Aufwandskonto', 'Expense Account' => 'Aufwandskonto', 'Expense/Asset' => 'Aufwand/Anlagen', @@ -1624,14 +1627,17 @@ $self->{texts} = { 'Options' => 'Optionen', 'Or download the whole Installation Documentation as PDF (350kB) for off-line study (currently in German Language): ' => 'Oder laden Sie die komplette Installationsbeschreibung als PDF (350kB) herunter: ', 'Order' => 'Auftrag', - 'Order Date' => 'Auftragsdatum', 'Order Date missing!' => 'Auftragsdatum fehlt!', - 'Order Number' => 'Auftragsnummer', + 'Order Date' => 'Auftragsdatum', 'Order Number missing!' => 'Auftragsnummer fehlt!', + 'Order Number' => 'Auftragsnummer', 'Order amount' => 'Auftragswert', 'Order deleted!' => 'Auftrag gelöscht!', + 'Order probability & expected billing date' => 'Auftragswahrscheinlichkeit & vorrauss. Abrechnungsdatum', + 'Order probability' => 'Auftragswahrscheinlichkeit', 'Order/Item row name' => 'Name der Auftrag-/Positions-Zeilen', 'OrderItem' => 'Position', + 'Ordered' => 'Vom Kunde bestellt', 'Ordered' => 'Von Kunden bestellt', 'Orders' => 'Aufträge', 'Orders / Delivery Orders deleteable' => 'Aufträge / Lieferscheine löschbar', diff --git a/sql/Pg-upgrade2/sales_quotation_order_probability_expected_billing_date.sql b/sql/Pg-upgrade2/sales_quotation_order_probability_expected_billing_date.sql new file mode 100644 index 000000000..5c3716860 --- /dev/null +++ b/sql/Pg-upgrade2/sales_quotation_order_probability_expected_billing_date.sql @@ -0,0 +1,12 @@ +-- @tag: sales_quotation_order_probability_expected_billing_date +-- @charset: utf-8 +-- @description: Weitere Felder im Angebot: Angebotswahrscheinlichkeit, voraussichtliches Abrechnungsdatum +ALTER TABLE oe + ADD COLUMN order_probability INTEGER, + ADD COLUMN expected_billing_date DATE; + +UPDATE oe SET order_probability = 0; + +ALTER TABLE oe + ALTER COLUMN order_probability SET DEFAULT 0, + ALTER COLUMN order_probability SET NOT NULL; diff --git a/templates/webpages/oe/form_header.html b/templates/webpages/oe/form_header.html index 6a88c012f..3419fa6be 100644 --- a/templates/webpages/oe/form_header.html +++ b/templates/webpages/oe/form_header.html @@ -237,6 +237,20 @@ [%- L.select_tag('globalproject_id', ALL_PROJECTS, title_key='projectnumber', default=globalproject_id, with_empty='1', onChange="document.getElementById('update_button').click();") %] +[%- IF type == 'sales_quotation' %] + + [% 'Order probability' | $T8 %] + + [%- L.select_tag('order_probability', ORDER_PROBABILITIES, title='title', default=order_probability) %]% + + + + [% 'Expected billing date' | $T8 %] + + [%- L.date_tag('expected_billing_date', expected_billing_date 'BL') %] + + +[%- END %] diff --git a/templates/webpages/oe/search.html b/templates/webpages/oe/search.html index 71b22a962..b651bf035 100644 --- a/templates/webpages/oe/search.html +++ b/templates/webpages/oe/search.html @@ -122,6 +122,25 @@ [% L.date_tag('reqdateto') %] +[%- IF type == 'sales_quotation' %] + + [% 'Expected billing date' | $T8 %] [% 'From' | $T8 %] + + [% L.date_tag('expected_billing_date_from', '' 'BL') %] + + [% 'Expected billing date' | $T8 %] [% 'Bis' | $T8 %] + + [% L.date_tag('expected_billing_date_to', '' 'BL') %] + + + + [% 'Order probability' | $T8 %] + + [% L.select_tag('order_probability_op', [[ 'ge', '>=' ], [ 'le', '<=' ]]) %] + [% L.select_tag('order_probability_value', ORDER_PROBABILITIES, title='title', with_empty=1) %] + + +[%- END %] [% 'Include in Report' | $T8 %] @@ -250,6 +269,14 @@ +[% IF type == 'sales_quotation' %] + + + + + + +[%- END %] -- 2.20.1