projekt kivitendo

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 };

}

use SL::Request qw(flatten);

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_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;

}

  'Customer/Vendor Name'        => 'Kunde/Lieferant',

  'Customer/Vendor Number'      => 'Kundennummer/Lieferantennummer',

  'Mobile'                      => 'Mobiltelefon',

  'Mobile 1'                    => '',

  'Mobile 2'                    => '',

  'Search contacts'             => 'Ansprechpartnersuche',

  'Search term'                 => 'Suchbegriff',

  'contact_list'                => 'ansprechpartner_liste',

[Master Data--Reports--Contacts]

ACCESS=customer_vendor_edit

module=ct.pl

action=search_contact

db=customer

[%- USE HTML %]

[%- USE T8 %]

<body onload="fokus()">

 <form method="post" action="ct.pl" name="Form">

  <input type="hidden" name="db" value="[% db | html %]">

  <div class="listtop">[% 'Contacts' | $T8 %]</div>

  <table>

   <tr>

    <th align="right" nowrap>[% 'Name' | $T8 %]</th>

    <td><input name="filter.cp_name" size="35"></td>

   </tr>

   <tr>

    <th align="right" nowrap>[% 'Greeting' | $T8 %]</th>

    <td><input name="filter.cp_greeting" size="35"></td>

   </tr>

   <tr>

    <th align="right" nowrap>[% 'Title' | $T8 %]</th>

    <td><input name="filter.cp_title" size="35"></td>

   </tr>

   <tr>

    <th align="right" nowrap>[% 'E-mail' | $T8 %]</th>

    <td><input name="filter.cp_email" size="35"></td>

   </tr>

   <tr>

    <th align="right" nowrap>[% 'Department' | $T8 %]</th>

    <td><input name="filter.cp_abteilung" size="35"></td>

   </tr>

   <tr>

    <th align="right" nowrap>[% 'Project' | $T8 %]</th>

    <td><input name="filter.cp_project" size="35"></td>

   </tr>

   <tr>

    <td></td>

    <td>

     <input name="filter.status" class="radio" type="radio" value="active" checked> [% 'Active' | $T8 %]

     <input name="filter.status" class="radio" type="radio" value="all"> [% 'All' | $T8 %]

     <input name="filter.status" class="radio" type="radio" value="orphaned"> [% 'Orphaned' | $T8 %]

    </td>

   </tr>

   <tr>

    <th align="right" nowrap>[% 'Include in Report' | $T8 %]</th>

    <td>

     <table border="0">

      <tr>

       <td>

        <input name="l.cp_id" id="l_cp_id" type="checkbox" class="checkbox" value="Y">

        <label for="l_cp_id">[% 'ID' | $T8 %]</label>

       </td>

       <td>

        <input name="l.vcnumber" id="l_vcnumber" type="checkbox" class="checkbox" value="Y" checked>

        <label for="l_vcnumber">[% 'Customer/Vendor Number' | $T8 %]</label>

       </td>

       <td>

        <input name="l.vcname" id="l_vcname" type="checkbox" class="checkbox" value="Y" checked>

        <label for="l_vcname">[% 'Customer/Vendor Name' | $T8 %]</label>

       </td>

      </tr>

      <tr>

       <td>

        <input name="l.cp_name" id="l_cp_name" type="checkbox" class="checkbox" value="Y" checked>

        <label for="l_cp_name">[% 'Name' | $T8 %]</label>

       </td>

       <td>

        <input name="l.cp_givenname" id="l_cp_givenname" type="checkbox" class="checkbox" value="Y" checked>

        <label for="l_cp_givenname">[% 'Given Name' | $T8 %]</label>

       </td>

       <td>

        <input name="l.cp_street" id="l_cp_street" type="checkbox" class="checkbox" value="Y">

        <label for="l_cp_street">[% 'Street' | $T8 %]</label>

       </td>

      </tr>

      <tr>

       <td>

        <input name="l.cp_phone" id="l_cp_phone" type="checkbox" class="checkbox" value="Y" checked>

        <label for="l_cp_phone">[% 'Phone' | $T8 %]</label>

       </td>

       <td>

        <input name="l.cp_mobile" id="l_cp_mobile" type="checkbox" class="checkbox" value="Y" checked>

        <label for="l_cp_mobile">[% 'Mobile' | $T8 %]</label>

       </td>

       <td>

        <input name="l.cp_email" id="l_cp_email" type="checkbox" class="checkbox" value="Y" checked>

        <label for="l_cp_email">[% 'E-mail' | $T8 %]</label>

       </td>

      </tr>

      <tr>

       <td>

        <input name="l.cp_birthday" id="l_cp_birthday" type="checkbox" class="checkbox" value="Y">

        <label for="l_cp_birthday">[% 'Birthday' | $T8 %]</label>

       </td>

       <td>

        <input name="l.cp_abteilung" id="l_cp_abteilung" type="checkbox" class="checkbox" value="Y">

        <label for="l_cp_abteilung">[% 'Department' | $T8 %]</label>

       </td>

       <td>

        <input name="l.cp_gender" id="l_cp_gender" type="checkbox" class="checkbox" value="Y">

        <label for="l_cp_gender">[% 'Gender' | $T8 %]</label>

       </td>

      </tr>

      [% CUSTOM_VARIABLES_INCLUSION_CODE %]

     </table>

    </td>

   </tr>

  </table>

  <input type="hidden" name="nextsub" value="list_contacts">

  <input type="submit" class="submit" name="action" value="[% 'Continue' | $T8 %]">

 </form>

</body>

</html>

 <script type="text/javascript" src="js/jquery.js"></script>

 <!--

function on_keydown_quicksearch(e) {

  var key;

  if (window.event)

    key = window.event.keyCode;   // IE

  else

    key = e.which;                // Firefox

  if (key != 13)

    return true;

  var search_term = $("#search_term");

  var value       = search_term.val();

  if (!value)

    return true;

  var url = "ct.pl?action=list_contacts&INPUT_ENCODING=utf-8&filter.status=active&search_term=" + encodeURIComponent(value);

  search_term.val('');

  $("#win1").attr('src', url);

  return false;

}

   <td style="color:white; font-family:verdana,arial,sans-serif; font-size: 12px;" nowrap>

     

    [[% 'Search contacts' | $T8 %] <input size="15" name="search_term" id="search_term" onkeydown="return on_keydown_quicksearch(event)">]