X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FCT.pm;h=bec4a6d73bbc2d19a1ba44385c86b3189db3d4a1;hb=0b33cc602d717933791d06cb64f65dea2a7c2354;hp=8f8aa4132b84015dafcc2067366493a24da95f43;hpb=8dc4cef21cbcdcd2fd3bc6539aefd6fa104bb8c5;p=kivitendo-erp.git diff --git a/SL/CT.pm b/SL/CT.pm index 8f8aa4132..bec4a6d73 100644 --- a/SL/CT.pm +++ b/SL/CT.pm @@ -40,6 +40,7 @@ package CT; use SL::Common; use SL::CVar; use SL::DBUtils; +use Text::ParseWords; use strict; @@ -75,10 +76,12 @@ sub search { "zipcode" => "ct.zipcode", "city" => "ct.city", "country" => "ct.country", + "gln" => "ct.gln", "discount" => "ct.discount", "insertdate" => "ct.itime", "salesman" => "e.name", - "payment" => "pt.description" + "payment" => "pt.description", + "pricegroup" => "pg.pricegroup", ); $form->{sort} ||= "name"; @@ -116,6 +119,28 @@ sub search { push @values, '%' . $form->{cp_name} . '%'; } + if ($form->{addr_street}) { + $where .= qq| AND ((ct.street ILIKE ?) | . + qq| OR | . + qq| (ct.id IN ( | . + qq| SELECT sc.trans_id FROM shipto sc | . + qq| WHERE (sc.module = 'CT') | . + qq| AND (sc.shiptostreet ILIKE ?) | . + qq| ))) |; + push @values, ('%' . $form->{addr_street} . '%') x 2; + } + + if ($form->{addr_zipcode}) { + $where .= qq| AND ((ct.zipcode ILIKE ?) | . + qq| OR | . + qq| (ct.id IN ( | . + qq| SELECT sc.trans_id FROM shipto sc | . + qq| WHERE (sc.module = 'CT') | . + qq| AND (sc.shiptozipcode ILIKE ?) | . + qq| ))) |; + push @values, ('%' . $form->{addr_zipcode} . '%') x 2; + } + if ($form->{addr_city}) { $where .= " AND ((lower(ct.city) LIKE lower(?)) OR @@ -142,6 +167,19 @@ sub search { push @values, ('%' . $form->{addr_country} . '%') x 2; } + if ($form->{addr_gln}) { + $where .= " AND ((lower(ct.gln) LIKE lower(?)) + OR + (ct.id IN ( + SELECT so.trans_id + FROM shipto so + WHERE (so.module = 'CT') + AND (lower(so.shiptogln) LIKE lower(?)) + )) + )"; + push @values, ('%' . $form->{addr_gln} . '%') x 2; + } + if ( $form->{status} eq 'orphaned' ) { $where .= qq| AND ct.id NOT IN | . @@ -187,6 +225,15 @@ sub search { push @values, conv_date($form->{insertdateto}); } + if ($form->{all}) { + my @tokens = parse_line('\s+', 0, $form->{all}); + $where .= qq| AND ( + ct.${cv}number ILIKE ? OR + ct.name ILIKE ? + )| for @tokens; + push @values, ("%$_%")x2 for @tokens; + } + # Nur Kunden finden, bei denen ich selber der Verkäufer bin # Gilt nicht für Lieferanten if ($cv eq 'customer' && !$main::auth->assert('customer_vendor_all_edit', 1)) { @@ -203,24 +250,18 @@ sub search { push @values, @cvar_values; } - if ($form->{addr_street}) { - $where .= qq| AND (ct.street ILIKE ?)|; - push @values, '%' . $form->{addr_street} . '%'; - } - - if ($form->{addr_zipcode}) { - $where .= qq| AND (ct.zipcode ILIKE ?)|; - push @values, $form->{addr_zipcode} . '%'; - } - + my $pg_select = $form->{l_pricegroup} ? qq|, pg.pricegroup as pricegroup | : ''; + my $pg_join = $form->{l_pricegroup} ? qq|LEFT JOIN pricegroup pg ON (ct.klass = pg.id) | : ''; my $query = qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | . qq| pt.description as payment | . + $pg_select . (qq|, NULL AS invnumber, NULL AS ordnumber, NULL AS quonumber, NULL AS invid, NULL AS module, NULL AS formtype, NULL AS closed | x!! $join_records) . qq|FROM $cv ct | . qq|LEFT JOIN business b ON (ct.business_id = b.id) | . qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | . qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | . + $pg_join . qq|WHERE $where|; my @saved_values = @values; @@ -235,8 +276,9 @@ sub search { $query .= qq| UNION | . qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | . - qq| pt.description as payment, | . - qq| a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, | . + qq| pt.description as payment | . + $pg_select . + qq|, a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, | . qq| '$module' AS module, 'invoice' AS formtype, | . qq| (a.amount = a.paid) AS closed | . qq|FROM $cv ct | . @@ -244,6 +286,7 @@ sub search { qq|LEFT JOIN business b ON (ct.business_id = b.id) | . qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | . qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | . + $pg_join . qq|WHERE $where AND (a.invoice = '1')|; } @@ -252,14 +295,16 @@ sub search { $query .= qq| UNION | . qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | . - qq| pt.description as payment, | . - qq| ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | . + qq| pt.description as payment | . + $pg_select . + qq|, ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | . qq| 'oe' AS module, 'order' AS formtype, o.closed | . qq|FROM $cv ct | . qq|JOIN oe o ON (o.${cv}_id = ct.id) | . qq|LEFT JOIN business b ON (ct.business_id = b.id) | . qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | . qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | . + $pg_join . qq|WHERE $where AND (o.quotation = '0')|; } @@ -268,14 +313,16 @@ sub search { $query .= qq| UNION | . qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | . - qq| pt.description as payment, | . - qq| ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | . + qq| pt.description as payment | . + $pg_select . + qq|, ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | . qq| 'oe' AS module, 'quotation' AS formtype, o.closed | . qq|FROM $cv ct | . qq|JOIN oe o ON (o.${cv}_id = ct.id) | . qq|LEFT JOIN business b ON (ct.business_id = b.id) | . qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | . qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | . + $pg_join . qq|WHERE $where AND (o.quotation = '1')|; } }