From 4070dbf38e04b168fe43bd3a6e5a3f28f77e986d Mon Sep 17 00:00:00 2001 From: =?utf8?q?Sven=20Sch=C3=B6ling?= Date: Fri, 20 Apr 2012 16:03:07 +0200 Subject: [PATCH] Suche nach Ansprechpartnern MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Merge aus zwei verschiedenen Implementierungen der gleichen Funktionalität Features: - behandelt Ansprechparter als direkte Suchziele wie Kunden und Lieferanten (1) - Suche ähnlich den bekannten Suchmasken (1) - Suche nach direktem Suchwort (2) - Verlinkung einer Schnellsuche in der menunew header Leiste (2) --- SL/CT.pm | 78 ++++++++++++++ bin/mozilla/ct.pl | 100 ++++++++++++++++++ locale/de/all | 9 +- menu.ini | 6 ++ templates/webpages/ct/search_contact.html | 122 ++++++++++++++++++++++ templates/webpages/menu/menuv3.html | 31 +++++- 6 files changed, 343 insertions(+), 3 deletions(-) create mode 100644 templates/webpages/ct/search_contact.html diff --git a/SL/CT.pm b/SL/CT.pm index 6dbbec568..2e8a017b9 100644 --- a/SL/CT.pm +++ b/SL/CT.pm @@ -1207,4 +1207,82 @@ 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 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; + } + + 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; diff --git a/bin/mozilla/ct.pl b/bin/mozilla/ct.pl index dbf8cdd82..32fe32462 100644 --- a/bin/mozilla/ct.pl +++ b/bin/mozilla/ct.pl @@ -49,6 +49,7 @@ use POSIX qw(strftime); use SL::CT; use SL::CVar; +use SL::Request qw(flatten); use SL::DB::Business; use SL::DB::Default; use SL::Helper::Flash; @@ -110,6 +111,18 @@ sub search { $main::lxdebug->leave_sub(); } +sub search_contact { + $::lxdebug->enter_sub; + $::auth->assert('customer_vendor_edit'); + + $::form->{fokus} = 'Form.name'; + + $::form->header; + print $::form->parse_html_template('ct/search_contact'); + + $::lxdebug->leave_sub; +} + sub list_names { $main::lxdebug->enter_sub(); @@ -268,6 +281,93 @@ sub list_names { $main::lxdebug->leave_sub(); } +sub list_contacts { + $::lxdebug->enter_sub; + $::auth->assert('customer_vendor_edit'); + + $::form->{sortdir} = 1 unless defined $::form->{sortdir}; + + my @contacts = CT->search_contacts( + search_term => $::form->{search_term}, + filter => $::form->{filter}, + ); + + my @columns = qw( + cp_id vcname vcnumber cp_name cp_givenname cp_street cp_phone1 cp_phone2 + cp_mobile1 cp_mobile2 cp_email cp_abteilung cp_birthday cp_gender + ); + + my @visible_columns; + if ($::form->{l}) { + @visible_columns = grep { $::form->{l}{$_} } @columns; + push @visible_columns, qw(cp_phone1 cp_phone2) if $::form->{l}{cp_phone}; + push @visible_columns, qw(cp_mobile1 cp_mobile2) if $::form->{l}{cp_mobile}; + } else { + @visible_columns = qw(vcname vcnumber cp_name cp_givenname cp_phone1 cp_phone2 cp_mobile1 cp_email); + } + + my %column_defs = ( + 'cp_id' => { 'text' => $::locale->text('ID'), }, + 'vcname' => { 'text' => $::locale->text('Customer/Vendor'), }, + 'vcnumber' => { 'text' => $::locale->text('Customer/Vendor Number'), }, + 'cp_name' => { 'text' => $::locale->text('Name'), }, + 'cp_givenname' => { 'text' => $::locale->text('Given Name'), }, + 'cp_street' => { 'text' => $::locale->text('Street'), }, + 'cp_phone1' => { 'text' => $::locale->text('Phone1'), }, + 'cp_phone2' => { 'text' => $::locale->text('Phone2'), }, + 'cp_mobile1' => { 'text' => $::locale->text('Mobile 1'), }, + 'cp_mobile2' => { 'text' => $::locale->text('Mobile 2'), }, + 'cp_email' => { 'text' => $::locale->text('E-mail'), }, + 'cp_abteilung' => { 'text' => $::locale->text('Department'), }, + 'cp_birthday' => { 'text' => $::locale->text('Birthday'), }, + 'cp_gender' => { 'text' => $::locale->text('Gender'), }, + ); + + map { $column_defs{$_}->{visible} = 1 } @visible_columns; + + my @hidden_variables = (qw(search_term filter l)); + my $hide_vars = { map { $_ => $::form->{$_} } @hidden_variables }; + my @hidden_nondefault = grep({ $::form->{$_} } @hidden_variables); + my $callback = build_std_url('action=list_contacts', join '&', map { E($_->[0]) . '=' . E($_->[1]) } @{ flatten($hide_vars) }); + $::form->{callback} = "$callback&sort=" . E($::form->{sort}); + + map { $column_defs{$_}->{link} = "${callback}&sort=${_}&sortdir=" . ($::form->{sort} eq $_ ? 1 - $::form->{sortdir} : $::form->{sortdir}) } @columns; + + $::form->{title} = $::locale->text('Contacts'); + + my $report = SL::ReportGenerator->new(\%::myconfig, $::form); + + my @options = $::locale->text('Search term') . ': ' . $::form->{search_term}; + + $report->set_options('top_info_text' => join("\n", @options), + 'output_format' => 'HTML', + 'title' => $::form->{title}, + 'attachment_basename' => $::locale->text('contact_list') . strftime('_%Y%m%d', localtime time), + ); + $report->set_options_from_form; + + $report->set_columns(%column_defs); + $report->set_column_order(@columns); + + $report->set_export_options('list_contacts', @hidden_variables); + + $report->set_sort_indicator($::form->{sort}, $::form->{sortdir}); + + foreach my $ref (@contacts) { + my $row = { map { $_ => { 'data' => $ref->{$_} } } @columns }; + + $row->{vcname}->{link} = build_std_url('action=edit', 'id=' . E($ref->{vcid}), 'db=' . E($ref->{db}), 'callback', @hidden_nondefault); + $row->{vcnumber}->{link} = $row->{vcname}->{link}; + $row->{cp_email}->{link} = 'mailto:' . E($ref->{cp_email}); + + $report->add_data($row); + } + + $report->generate_with_headers; + + $::lxdebug->leave_sub; +} + sub edit { $main::lxdebug->enter_sub(); diff --git a/locale/de/all b/locale/de/all index 1e9b423fb..9e595516c 100644 --- a/locale/de/all +++ b/locale/de/all @@ -512,6 +512,8 @@ $self->{texts} = { 'Customer type' => 'Kundentyp', 'Customer/Vendor' => 'Kunde/Lieferant', 'Customer/Vendor (database ID)' => 'Kunde/Lieferant (Datenbank-ID)', + 'Customer/Vendor Name' => 'Kunde/Lieferant', + 'Customer/Vendor Number' => 'Kundennummer/Lieferantennummer', 'Customername' => 'Kundenname', 'Customernumberinit' => 'Kunden-/Lieferantennummernkreis', 'Customers' => 'Kunden', @@ -1091,7 +1093,6 @@ $self->{texts} = { 'Logout now' => 'Lx-Office jetzt verlassen', 'Long Dates' => 'Lange Monatsnamen', 'Long Description' => 'Langtext', - 'Lx-Office' => 'Lx-Office', 'MAILED' => 'Gesendet', 'MSG_BROWSER_DOES_NOT_SUPPORT_IFRAMES' => 'Ihr Browser kann leider keine eingebetteten Frames anzeigen. Bitte wählen Sie ein anderes Menü in der Benutzerkonfiguration im Administrationsmenü aus.', 'Main Preferences' => 'Grundeinstellungen', @@ -1138,6 +1139,9 @@ $self->{texts} = { 'Missing user id!' => 'Benutzer ID fehlt!', 'Mitarbeiter' => 'Mitarbeiter', 'Mixed (requires column "type")' => 'Gemischt (erfordert Spalte "type")', + 'Mobile' => 'Mobiltelefon', + 'Mobile 1' => '', + 'Mobile 2' => '', 'Mobile1' => 'Mobile 1', 'Mobile2' => 'Mobile 2', 'Model' => 'Lieferanten-Art-Nr.', @@ -1576,6 +1580,8 @@ $self->{texts} = { 'Screen' => 'Bildschirm', 'Search AP Aging' => 'Offene Verbindlichkeiten', 'Search AR Aging' => 'Offene Forderungen', + 'Search contacts' => 'Ansprechpartnersuche', + 'Search term' => 'Suchbegriff', 'Searchable' => 'Durchsuchbar', 'Select' => 'auswählen', 'Select a Customer' => 'Endkunde auswählen', @@ -2185,6 +2191,7 @@ $self->{texts} = { 'config/lx_office.conf: Key "authentication/ldap" is missing.' => 'config/lx_office.conf: Der Schlüssel "authentication/ldap" fehlt.', 'config/lx_office.conf: Missing parameters in "authentication/database". Required parameters are "host", "db" and "user".' => 'config/lx_office.conf: Fehlende Parameter in "authentication/database". Benötigte Parameter sind "host", "db" und "user".', 'config/lx_office.conf: Missing parameters in "authentication/ldap". Required parameters are "host", "attribute" and "base_dn".' => 'config/lx_office.conf: Fehlende Parameter in "authentication/ldap". Benötigt werden "host", "attribute" und "base_dn".', + 'contact_list' => 'ansprechpartner_liste', 'continue' => 'weiter', 'correction' => 'Korrektur', 'cp_greeting to cp_gender migration' => 'Datenumwandlung von Titel nach Geschlecht (cp_greeting to cp_gender)', diff --git a/menu.ini b/menu.ini index 5d39fd691..da5450664 100644 --- a/menu.ini +++ b/menu.ini @@ -59,6 +59,12 @@ module=ct.pl action=search db=vendor +[Master Data--Reports--Contacts] +ACCESS=customer_vendor_edit +module=ct.pl +action=search_contact +db=customer + [Master Data--Reports--Parts] ACCESS=part_service_assembly_edit module=ic.pl diff --git a/templates/webpages/ct/search_contact.html b/templates/webpages/ct/search_contact.html new file mode 100644 index 000000000..2fe8bdc9e --- /dev/null +++ b/templates/webpages/ct/search_contact.html @@ -0,0 +1,122 @@ +[%- USE HTML %] +[%- USE T8 %] + + +
+ + + +
[% 'Contacts' | $T8 %]
+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
[% 'Name' | $T8 %]
[% 'Greeting' | $T8 %]
[% 'Title' | $T8 %]
[% 'E-mail' | $T8 %]
[% 'Department' | $T8 %]
[% 'Project' | $T8 %]
+ [% 'Active' | $T8 %] + [% 'All' | $T8 %] + [% 'Orphaned' | $T8 %] +
[% 'Include in Report' | $T8 %] + + + + + + + + + + + + + + + + + + + + + + + + + [% CUSTOM_VARIABLES_INCLUSION_CODE %] + +
+ + + + + + + + +
+ + + + + + + + +
+ + + + + + + + +
+ + + + + + + + +
+
+ + + + +
+ + + diff --git a/templates/webpages/menu/menuv3.html b/templates/webpages/menu/menuv3.html index e02d27375..6faa762e0 100644 --- a/templates/webpages/menu/menuv3.html +++ b/templates/webpages/menu/menuv3.html @@ -1,8 +1,33 @@ [%- USE T8 %] [% USE HTML %] +