X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FCT.pm;h=b52c8794acafa55317b3c4bb674b4eab08d223c9;hb=96e32f352f3937bdd2deb279a55d94871396d9c0;hp=b430272e018216cdca776c04c6dd1e250b8e68b1;hpb=fa7fc7eeb3ca718914affee06c0629a08d571288;p=kivitendo-erp.git diff --git a/SL/CT.pm b/SL/CT.pm index b430272e0..b52c8794a 100644 --- a/SL/CT.pm +++ b/SL/CT.pm @@ -25,7 +25,8 @@ # GNU General Public License for more details. # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software -# Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA. +# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, +# MA 02110-1335, USA. #====================================================================== # # backend code for customers and vendors @@ -40,6 +41,9 @@ package CT; use SL::Common; use SL::CVar; use SL::DBUtils; +use SL::DB; +use SL::Util qw(trim); +use Text::ParseWords; use strict; @@ -75,9 +79,15 @@ 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", + "ustid" => "ct.ustid", + "creditlimit" => "ct.creditlimit", + "commercial_court" => "ct.commercial_court", ); $form->{sort} ||= "name"; @@ -92,7 +102,7 @@ sub search { } my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC'; - if ($sortorder !~ /(business|id|discount)/ && !$join_records) { + if ($sortorder !~ /(business|creditlimit|id|discount|itime)/ && !$join_records) { $sortorder = "lower($sortorder) ${sortdir}"; } else { $sortorder .= " ${sortdir}"; @@ -100,19 +110,41 @@ sub search { if ($form->{"${cv}number"}) { $where .= " AND ct.${cv}number ILIKE ?"; - push(@values, '%' . $form->{"${cv}number"} . '%'); + push(@values, like($form->{"${cv}number"})); } foreach my $key (qw(name contact email)) { if ($form->{$key}) { $where .= " AND ct.$key ILIKE ?"; - push(@values, '%' . $form->{$key} . '%'); + push(@values, like($form->{$key})); } } if ($form->{cp_name}) { $where .= " AND ct.id IN (SELECT cp_cv_id FROM contacts WHERE lower(cp_name) LIKE lower(?))"; - push @values, '%' . $form->{cp_name} . '%'; + push @values, like($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, (like($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, (like($form->{addr_zipcode})) x 2; } if ($form->{addr_city}) { @@ -125,7 +157,7 @@ sub search { AND (lower(sc.shiptocity) LIKE lower(?)) )) )"; - push @values, ('%' . $form->{addr_city} . '%') x 2; + push @values, (like($form->{addr_city})) x 2; } if ($form->{addr_country}) { @@ -138,7 +170,20 @@ sub search { AND (lower(so.shiptocountry) LIKE lower(?)) )) )"; - push @values, ('%' . $form->{addr_country} . '%') x 2; + push @values, (like($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, (like($form->{addr_gln})) x 2; } if ( $form->{status} eq 'orphaned' ) { @@ -171,11 +216,55 @@ sub search { push(@values, conv_i($form->{business_id})); } - # 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)) { - $where .= qq| AND ct.salesman_id = (select em.id from employee em where em.login = ?)|; - push(@values, $form->{login}); + if ($form->{salesman_id}) { + $where .= qq| AND (ct.salesman_id = ?)|; + push(@values, conv_i($form->{salesman_id})); + } + + if($form->{insertdatefrom}) { + $where .= qq| AND (ct.itime::DATE >= ?)|; + push@values, conv_date($form->{insertdatefrom}); + } + + if($form->{insertdateto}) { + $where .= qq| AND (ct.itime::DATE <= ?)|; + 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; + } + + if (($form->{create_zugferd_invoices} // '') ne '') { + $where .= qq| AND (ct.create_zugferd_invoices = ?)|; + push @values, $form->{create_zugferd_invoices}; + } + + if ($form->{all_phonenumbers}) { + my $search_term = trim($form->{all_phonenumbers}); + $search_term =~ s{\p{WSpace}+}{}g; + $search_term = join ' *', split(//, $search_term); + + $where .= qq| AND (ct.phone ~* ? OR + ct.fax ~* ? OR + ct.id IN + (SELECT cp_cv_id FROM contacts + WHERE cp_phone1 ~* ? OR + cp_phone2 ~* ? OR + cp_fax ~* ? OR + cp_mobile1 ~* ? OR + cp_mobile2 ~* ? OR + cp_satphone ~* ? OR + cp_satfax ~* ? OR + cp_privatphone ~* ? + ) + )|; + push @values, ($search_term)x10; } my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT', @@ -187,24 +276,26 @@ sub search { push @values, @cvar_values; } - if ($form->{addr_street}) { - $where .= qq| AND (ct.street ILIKE ?)|; - push @values, '%' . $form->{addr_street} . '%'; - } + my $pg_select = $form->{l_pricegroup} ? qq|, pg.pricegroup as pricegroup | : ''; + my $pg_join = $form->{l_pricegroup} ? qq|LEFT JOIN pricegroup pg ON (ct.pricegroup_id = pg.id) | : ''; - if ($form->{addr_zipcode}) { - $where .= qq| AND (ct.zipcode ILIKE ?)|; - push @values, $form->{addr_zipcode} . '%'; + my $main_cp_select = ''; + if ($form->{l_main_contact_person}) { + $main_cp_select = qq/, (SELECT concat(cp.cp_givenname, ' ', cp.cp_name, ' | ', cp.cp_email, ' | ', cp.cp_phone1) + FROM contacts cp WHERE ct.id=cp.cp_cv_id AND cp.cp_main LIMIT 1) + AS main_contact_person /; } - my $query = - qq|SELECT ct.*, b.description AS business, e.name as salesman, |. + qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | . qq| pt.description as payment | . + $pg_select . + $main_cp_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; @@ -218,9 +309,11 @@ sub search { push(@values, @saved_values); $query .= qq| UNION | . - qq|SELECT ct.*, 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|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | . + qq| pt.description as payment | . + $pg_select . + $main_cp_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 | . @@ -228,6 +321,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')|; } @@ -235,15 +329,18 @@ sub search { push(@values, @saved_values); $query .= qq| UNION | . - qq|SELECT ct.*, 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|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | . + qq| pt.description as payment | . + $pg_select . + $main_cp_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')|; } @@ -251,15 +348,18 @@ sub search { push(@values, @saved_values); $query .= qq| UNION | . - qq|SELECT ct.*, 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|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | . + qq| pt.description as payment | . + $pg_select . + $main_cp_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')|; } } @@ -278,7 +378,7 @@ sub get_contact { die 'Missing argument: cp_id' unless $::form->{cp_id}; - my $dbh = $form->dbconnect($myconfig); + my $dbh = SL::DB->client->dbh; my $query = qq|SELECT * FROM contacts c | . qq|WHERE cp_id = ? ORDER BY cp_id limit 1|; @@ -296,7 +396,6 @@ sub get_contact { ($form->{cp_used}) = selectfirst_array_query($form, $dbh, $query, ($form->{cp_id})x2); $sth->finish; - $dbh->disconnect; $main::lxdebug->leave_sub(); } @@ -368,7 +467,7 @@ sub search_contacts { 'cp.cp_name ILIKE ?', 'cp.cp_givenname ILIKE ?', 'cp.cp_email ILIKE ?'; - push @values, ('%' . $params{search_term} . '%') x 3; + push @values, (like($params{search_term})) x 3; if (($params{search_term} =~ m/\d/) && ($params{search_term} !~ m/[^\d \(\)+\-]/)) { my $number = $params{search_term};