From b98b8e3f73a399d2d4df7be4962b56bd4ad8c82b Mon Sep 17 00:00:00 2001 From: =?utf8?q?Sven=20Sch=C3=B6ling?= Date: Fri, 14 Nov 2014 16:02:59 +0100 Subject: [PATCH] Belege: Filtern nach Ansprechpartner --- SL/AP.pm | 5 +++++ SL/AR.pm | 5 +++++ SL/DO.pm | 6 ++++++ SL/OE.pm | 6 ++++++ bin/mozilla/ap.pl | 1 + bin/mozilla/ar.pl | 3 +++ bin/mozilla/do.pl | 3 +++ bin/mozilla/oe.pl | 1 + templates/webpages/ap/search.html | 4 ++++ templates/webpages/ar/search.html | 4 ++++ templates/webpages/do/search.html | 5 +++++ templates/webpages/oe/search.html | 4 ++++ 12 files changed, 47 insertions(+) diff --git a/SL/AP.pm b/SL/AP.pm index dddb8b3ea..35a8e19b0 100644 --- a/SL/AP.pm +++ b/SL/AP.pm @@ -433,6 +433,7 @@ sub ap_transactions { ) AS charts } . qq|FROM ap a | . qq|JOIN vendor v ON (a.vendor_id = v.id) | . + qq|LEFT JOIN contacts cp ON (a.cp_id = cp.cp_id) | . qq|LEFT JOIN employee e ON (a.employee_id = e.id) | . qq|LEFT JOIN project pr ON (a.globalproject_id = pr.id) | . qq|LEFT JOIN tax_zones tz ON (tz.id = v.taxzone_id)| . @@ -453,6 +454,10 @@ sub ap_transactions { $where .= " AND v.name ILIKE ?"; push(@values, $form->like($form->{vendor})); } + if ($form->{"cp_name"}) { + $where .= " AND (cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)"; + push(@values, ('%' . $form->{"cp_name"} . '%')x2); + } if ($form->{department}) { # ähnlich wie commit 0bbfb33b6aa8e38bb6c81d1684ab7d08e5b5c5af abteilung # wird so nicht mehr als zeichenkette zusammengebaut diff --git a/SL/AR.pm b/SL/AR.pm index af78117bb..692967fae 100644 --- a/SL/AR.pm +++ b/SL/AR.pm @@ -480,6 +480,7 @@ sub ar_transactions { ) AS charts } . qq|FROM ar a | . qq|JOIN customer c ON (a.customer_id = c.id) | . + qq|LEFT JOIN contacts cp ON (a.cp_id = cp.cp_id) | . qq|LEFT JOIN employee e ON (a.employee_id = e.id) | . qq|LEFT JOIN employee e2 ON (a.salesman_id = e2.id) | . qq|LEFT JOIN project pr ON (a.globalproject_id = pr.id)| . @@ -505,6 +506,10 @@ sub ar_transactions { $where .= " AND c.name ILIKE ?"; push(@values, $form->like($form->{customer})); } + if ($form->{"cp_name"}) { + $where .= " AND (cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)"; + push(@values, ('%' . $form->{"cp_name"} . '%')x2); + } if ($form->{business_id}) { my $business_id = $form->{business_id}; $where .= " AND c.business_id = ?"; diff --git a/SL/DO.pm b/SL/DO.pm index a66b64d5f..89f2d92b6 100644 --- a/SL/DO.pm +++ b/SL/DO.pm @@ -76,6 +76,7 @@ sub transactions { sm.name AS salesman FROM delivery_orders dord LEFT JOIN $vc ct ON (dord.${vc}_id = ct.id) + LEFT JOIN contacts cp ON (dord.cp_id = cp.cp_id) LEFT JOIN employee e ON (dord.employee_id = e.id) LEFT JOIN employee sm ON (dord.salesman_id = sm.id) LEFT JOIN project pr ON (dord.globalproject_id = pr.id) @@ -106,6 +107,11 @@ sub transactions { push @values, '%' . $form->{$vc} . '%'; } + if ($form->{"cp_name"}) { + push @where, "(cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)"; + push @values, ('%' . $form->{"cp_name"} . '%')x2; + } + foreach my $item (qw(employee_id salesman_id)) { next unless ($form->{$item}); push @where, "dord.$item = ?"; diff --git a/SL/OE.pm b/SL/OE.pm index 8e746f43c..2c821cb25 100644 --- a/SL/OE.pm +++ b/SL/OE.pm @@ -118,6 +118,7 @@ sub transactions { 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 contacts cp ON (o.cp_id = cp.cp_id) | . qq|LEFT JOIN employee e ON (o.employee_id = e.id) | . qq|LEFT JOIN employee s ON (o.salesman_id = s.id) | . qq|LEFT JOIN exchangerate ex ON (ex.currency_id = o.currency_id | . @@ -168,6 +169,11 @@ SQL push(@values, '%' . $form->{$vc} . '%'); } + if ($form->{"cp_name"}) { + $query .= " AND (cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)"; + push(@values, ('%' . $form->{"cp_name"} . '%')x2); + } + if (!$main::auth->assert('sales_all_edit', 1)) { $query .= " AND o.employee_id = (select id from employee where login= ?)"; push @values, $form->{login}; diff --git a/bin/mozilla/ap.pl b/bin/mozilla/ap.pl index f290c340e..dc48e3d70 100644 --- a/bin/mozilla/ap.pl +++ b/bin/mozilla/ap.pl @@ -911,6 +911,7 @@ sub ap_transactions { my @options; push @options, $locale->text('Vendor') . " : $form->{vendor}" if ($form->{vendor}); + push @options, $locale->text('Contact Person') . " : $form->{cp_name}" if ($form->{cp_name}); push @options, $locale->text('Department') . " : " . (split /--/, $form->{department})[0] if ($form->{department}); push @options, $locale->text('Invoice Number') . " : $form->{invnumber}" if ($form->{invnumber}); push @options, $locale->text('Order Number') . " : $form->{ordnumber}" if ($form->{ordnumber}); diff --git a/bin/mozilla/ar.pl b/bin/mozilla/ar.pl index 64efbc7e6..a7a93de55 100644 --- a/bin/mozilla/ar.pl +++ b/bin/mozilla/ar.pl @@ -957,6 +957,9 @@ sub ar_transactions { if ($form->{customer}) { push @options, $locale->text('Customer') . " : $form->{customer}"; } + if ($form->{cp_name}) { + push @options, $locale->text('Contact Person') . " : $form->{cp_name}"; + } if ($form->{department}) { my ($department) = split /--/, $form->{department}; push @options, $locale->text('Department') . " : $department"; diff --git a/bin/mozilla/do.pl b/bin/mozilla/do.pl index c6083510e..b25cdadb4 100644 --- a/bin/mozilla/do.pl +++ b/bin/mozilla/do.pl @@ -580,6 +580,9 @@ sub orders { if ($form->{vendor}) { push @options, $locale->text('Vendor') . " : $form->{vendor}"; } + if ($form->{cp_name}) { + push @options, $locale->text('Contact Person') . " : $form->{cp_name}"; + } if ($form->{department}) { my ($department) = split /--/, $form->{department}; push @options, $locale->text('Department') . " : $department"; diff --git a/bin/mozilla/oe.pl b/bin/mozilla/oe.pl index 7e38f8e56..59b5c6206 100644 --- a/bin/mozilla/oe.pl +++ b/bin/mozilla/oe.pl @@ -949,6 +949,7 @@ sub orders { push @options, $locale->text('Customer') . " : $form->{customer}" if $form->{customer}; push @options, $locale->text('Vendor') . " : $form->{vendor}" if $form->{vendor}; + push @options, $locale->text('Contact Person') . " : $form->{cp_name}" if $form->{cp_name}; push @options, $locale->text('Department') . " : $department" if $form->{department}; push @options, $locale->text('Order Number') . " : $form->{ordnumber}" if $form->{ordnumber}; push @options, $locale->text('Customer Order Number') . " : $form->{cusordnumber}" if $form->{cusordnumber}; diff --git a/templates/webpages/ap/search.html b/templates/webpages/ap/search.html index 9932cdd1f..ff763c046 100644 --- a/templates/webpages/ap/search.html +++ b/templates/webpages/ap/search.html @@ -28,6 +28,10 @@ -%] + + [% 'Contact Person' | $T8 %] + [% L.input_tag("cp_name", '', size=20) %] + [% 'Department' | $T8 %] diff --git a/templates/webpages/ar/search.html b/templates/webpages/ar/search.html index b095f08c3..71d2b693f 100644 --- a/templates/webpages/ar/search.html +++ b/templates/webpages/ar/search.html @@ -27,6 +27,10 @@ -%] + + [% 'Contact Person' | $T8 %] + [% L.input_tag("cp_name", '', size=20) %] + [% 'Department' | $T8 %] diff --git a/templates/webpages/do/search.html b/templates/webpages/do/search.html index 905215c56..040b0cdb7 100644 --- a/templates/webpages/do/search.html +++ b/templates/webpages/do/search.html @@ -40,6 +40,11 @@ + + [% 'Contact Person' | $T8 %] + [% L.input_tag("cp_name", '', class="fixed_width") %] + + [% 'Delivery Order Number' | $T8 %] diff --git a/templates/webpages/oe/search.html b/templates/webpages/oe/search.html index 4fdcbda00..f50a77ba2 100644 --- a/templates/webpages/oe/search.html +++ b/templates/webpages/oe/search.html @@ -32,6 +32,10 @@ -%] + + [% 'Contact Person' | $T8 %] + [% L.input_tag("cp_name", '', style="width: 250px") %] + [%- IF ALL_DEPARTMENTS.size %] [% 'Department' | $T8 %] -- 2.20.1