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

}