X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FCT.pm;h=fabc3cac875a14cc6a76dccbb8e308be8eb67d15;hb=ee932f3f74983a16977a6d89314a3ab09df20faa;hp=eece19e44e6b7ed4c66eb3d4e308e058e1eba76a;hpb=dc39f671740dc3ab2292c604e8605dd69fb33eac;p=kivitendo-erp.git diff --git a/SL/CT.pm b/SL/CT.pm index eece19e44..fabc3cac8 100644 --- a/SL/CT.pm +++ b/SL/CT.pm @@ -367,73 +367,18 @@ sub save_customer { do_query( $form, $dbh, $query, @values ); $query = undef; + my @columns = qw(cp_title cp_givenname cp_name cp_email cp_phone1 cp_phone2 cp_abteilung cp_fax + cp_mobile1 cp_mobile2 cp_satphone cp_satfax cp_project cp_privatphone cp_privatemail cp_birthday cp_gender + cp_street cp_zipcode cp_city); + @values = map { $_ eq 'cp_gender' ? ($form->{$_} eq 'f' ? 'f' : 'm') : $form->{$_} } @columns; + if ( $form->{cp_id} ) { - $query = qq|UPDATE contacts SET | . - qq|cp_title = ?, | . - qq|cp_givenname = ?, | . - qq|cp_name = ?, | . - qq|cp_email = ?, | . - qq|cp_phone1 = ?, | . - qq|cp_phone2 = ?, | . - qq|cp_abteilung = ?, | . - qq|cp_fax = ?, | . - qq|cp_mobile1 = ?, | . - qq|cp_mobile2 = ?, | . - qq|cp_satphone = ?, | . - qq|cp_satfax = ?, | . - qq|cp_project = ?, | . - qq|cp_privatphone = ?, | . - qq|cp_privatemail = ?, | . - qq|cp_birthday = ?, | . - qq|cp_gender = ? | . - qq|WHERE cp_id = ?|; - @values = ( - $form->{cp_title}, - $form->{cp_givenname}, - $form->{cp_name}, - $form->{cp_email}, - $form->{cp_phone1}, - $form->{cp_phone2}, - $form->{cp_abteilung}, - $form->{cp_fax}, - $form->{cp_mobile1}, - $form->{cp_mobile2}, - $form->{cp_satphone}, - $form->{cp_satfax}, - $form->{cp_project}, - $form->{cp_privatphone}, - $form->{cp_privatemail}, - $form->{cp_birthday}, - $form->{cp_gender} eq 'f' ? 'f' : 'm', - $form->{cp_id} - ); + $query = qq|UPDATE contacts SET | . join(', ', map { "${_} = ?" } @columns) . qq| WHERE cp_id = ?|; + push @values, $form->{cp_id}; + } elsif ( $form->{cp_name} || $form->{cp_givenname} ) { - $query = - qq|INSERT INTO contacts ( cp_cv_id, cp_title, cp_givenname, | . - qq| cp_name, cp_email, cp_phone1, cp_phone2, cp_abteilung, cp_fax, cp_mobile1, | . - qq| cp_mobile2, cp_satphone, cp_satfax, cp_project, cp_privatphone, cp_privatemail, | . - qq| cp_birthday, cp_gender) | . - qq|VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|; - @values = ( - $form->{id}, - $form->{cp_title}, - $form->{cp_givenname}, - $form->{cp_name}, - $form->{cp_email}, - $form->{cp_phone1}, - $form->{cp_phone2}, - $form->{cp_abteilung}, - $form->{cp_fax}, - $form->{cp_mobile1}, - $form->{cp_mobile2}, - $form->{cp_satphone}, - $form->{cp_satfax}, - $form->{cp_project}, - $form->{cp_privatphone}, - $form->{cp_privatemail}, - $form->{cp_birthday}, - $form->{cp_gender} eq 'f' ? 'f' : 'm', - ); + $query = qq|INSERT INTO contacts (| . join(', ', 'cp_cv_id', @columns) . qq|) VALUES (?, | . join(', ', ('?') x scalar(@columns)) . qq|)|; + unshift @values, $form->{id}; } do_query( $form, $dbh, $query, @values ) if ($query); @@ -448,6 +393,14 @@ sub save_customer { 'trans_id' => $form->{id}, 'variables' => $form, 'always_valid' => 1); + if ($form->{cp_id}) { + CVar->save_custom_variables('dbh' => $dbh, + 'module' => 'Contacts', + 'trans_id' => $form->{cp_id}, + 'variables' => $form, + 'name_prefix' => 'cp', + 'always_valid' => 1); + } my $rc = $dbh->commit(); @@ -657,6 +610,14 @@ sub save_vendor { 'trans_id' => $form->{id}, 'variables' => $form, 'always_valid' => 1); + if ($form->{cp_id}) { + CVar->save_custom_variables('dbh' => $dbh, + 'module' => 'Contacts', + 'trans_id' => $form->{cp_id}, + 'variables' => $form, + 'name_prefix' => 'cp', + 'always_valid' => 1); + } my $rc = $dbh->commit(); @@ -1195,4 +1156,91 @@ sub parse_excel_file { $main::lxdebug->leave_sub(); } +sub search_contacts { + $::lxdebug->enter_sub; + + my $self = shift; + my %params = @_; + + my $dbh = $params{dbh} || $::form->get_standard_dbh; + my $vc = $params{db} eq 'customer' ? 'customer' : 'vendor'; + + my %sortspecs = ( + 'cp_name' => 'cp_name, cp_givenname', + 'vcname' => 'vcname, cp_name, cp_givenname', + 'vcnumber' => 'vcnumber, cp_name, cp_givenname', + ); + + my %sortcols = map { $_ => 1 } qw(cp_name cp_givenname cp_phone1 cp_phone2 cp_mobile1 cp_email cp_street cp_zipcode cp_city vcname vcnumber); + + my $order_by = $sortcols{$::form->{sort}} ? $::form->{sort} : 'cp_name'; + $::form->{sort} = $order_by; + $order_by = $sortspecs{$order_by} if ($sortspecs{$order_by}); + + my $sortdir = $::form->{sortdir} ? 'ASC' : 'DESC'; + $order_by =~ s/,/ ${sortdir},/g; + $order_by .= " $sortdir"; + + my @where_tokens = (); + my @values; + + if ($params{search_term}) { + my @tokens; + push @tokens, + 'cp.cp_name ILIKE ?', + 'cp.cp_givenname ILIKE ?', + 'cp.cp_email ILIKE ?'; + push @values, ('%' . $params{search_term} . '%') x 3; + + if (($params{search_term} =~ m/\d/) && ($params{search_term} !~ m/[^\d \(\)+\-]/)) { + my $number = $params{search_term}; + $number =~ s/[^\d]//g; + $number = join '[ /\(\)+\-]*', split(m//, $number); + + push @tokens, map { "($_ ~ '$number')" } qw(cp_phone1 cp_phone2 cp_mobile1 cp_mobile2); + } + + push @where_tokens, map { "($_)" } join ' OR ', @tokens; + } + + my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'Contacts', + 'trans_id_field' => 'cp.cp_id', + 'filter' => $params{filter}); + + if ($cvar_where) { + push @where_tokens, $cvar_where; + push @values, @cvar_values; + } + + if (my $filter = $params{filter}) { + for (qw(name title givenname email project abteilung)) { + next unless $filter->{"cp_$_"}; + add_token(\@where_tokens, \@values, col => "cp.cp_$_", val => $filter->{"cp_$_"}, method => 'ILIKE', esc => 'substr'); + } + + push @where_tokens, 'cp.cp_cv_id IS NOT NULL' if $filter->{status} eq 'active'; + push @where_tokens, 'cp.cp_cv_id IS NULL' if $filter->{status} eq 'orphaned'; + } + + my $where = @where_tokens ? 'WHERE ' . join ' AND ', @where_tokens : ''; + + my $query = qq|SELECT cp.*, + COALESCE(c.id, v.id) AS vcid, + COALESCE(c.name, v.name) AS vcname, + COALESCE(c.customernumber, v.vendornumber) AS vcnumber, + CASE WHEN c.name IS NULL THEN 'vendor' ELSE 'customer' END AS db + FROM contacts cp + LEFT JOIN customer c ON (cp.cp_cv_id = c.id) + LEFT JOIN vendor v ON (cp.cp_cv_id = v.id) + $where + ORDER BY $order_by|; + + my $contacts = selectall_hashref_query($::form, $dbh, $query, @values); + + $::lxdebug->leave_sub; + + return @{ $contacts }; +} + + 1;