X-Git-Url: http://wagnertech.de/git?p=kivitendo-erp.git;a=blobdiff_plain;f=SL%2FCT.pm;fp=SL%2FCT.pm;h=b52c8794acafa55317b3c4bb674b4eab08d223c9;hp=cdf274b712dce558b15523c9e37ff2eeb8d9a0e8;hb=53593baa211863fbf66540cf1bcc36c8fb37257f;hpb=deb4d2dbb676d7d6f69dfe7815d6e0cb09bd4a44 diff --git a/SL/CT.pm b/SL/CT.pm index cdf274b71..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,11 +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", "pricegroup" => "pg.pricegroup", + "ustid" => "ct.ustid", + "creditlimit" => "ct.creditlimit", + "commercial_court" => "ct.commercial_court", ); $form->{sort} ||= "name"; @@ -94,7 +102,7 @@ sub search { } my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC'; - if ($sortorder !~ /(business|id|discount|itime)/ && !$join_records) { + if ($sortorder !~ /(business|creditlimit|id|discount|itime)/ && !$join_records) { $sortorder = "lower($sortorder) ${sortdir}"; } else { $sortorder .= " ${sortdir}"; @@ -102,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}) { @@ -127,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}) { @@ -140,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' ) { @@ -188,11 +231,40 @@ sub search { push @values, conv_date($form->{insertdateto}); } - # 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, $::myconfig{login}); + 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', @@ -204,22 +276,20 @@ 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 $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 . + $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) | . @@ -242,6 +312,7 @@ sub search { 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 | . @@ -261,6 +332,7 @@ sub search { 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 | . @@ -279,6 +351,7 @@ sub search { 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 | . @@ -305,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|; @@ -323,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(); } @@ -395,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};