projekt kivitendo

  my $customers;

  my $name             = $self->billing_lastname ne '' ? $self->billing_firstname . " " . $self->billing_lastname : '';

  my $lastname         = $self->billing_lastname ne '' ? "%" . $self->billing_lastname . "%"                      : '';

  my $company          = $self->billing_company  ne '' ? "%" . $self->billing_company  . "%"                      : '';

  my $street           = $self->billing_street   ne '' ?  $self->billing_street                                   : '';

  my $street_not_fuzzy = $self->billing_street   ne '' ?  "%" . $self->billing_street . "%"                       : '';

  my $zipcode          = $self->billing_street   ne '' ?  $self->billing_zipcode                                  : '';

  my $email            = $self->billing_street   ne '' ?  $self->billing_email                                    : '';

  if($self->check_trgm) {

    # Fuzzysearch for street to find e.g. "Dorfstrasse - Dorfstr. - Dorfstraße"

    my $fs_query = <<SQL;

    my @values = ($lastname, $company, $self->billing_zipcode, $street, $self->billing_zipcode, $self->billing_email);

    $customers = SL::DB::Manager::Customer->get_objects_from_sql(

      sql  => $fs_query,

      args => \@values,

    );

  }else{

    # If trgm extension is not installed

    $customers = SL::DB::Manager::Customer->get_all(

      where => [

          or => [

            and => [

                     or => [ 'name' => { ilike => $lastname },

                             'name' => { ilike => $company  },

                           ],

                     'zipcode' => { ilike => $zipcode },

                   ],

            and => [

                     and => [ 'street'  => { ilike => $street_not_fuzzy },

                              'zipcode' => { ilike => $zipcode },

                            ],

                   ],

            or  => [ 'email' => { ilike => $email } ],

          ],

      ],

    );

  }

sub check_trgm {

  my ( $self ) = @_;

  my $dbh     = $::form->get_standard_dbh();

  my $sql     = "SELECT installed_version FROM pg_available_extensions WHERE name = 'pg_trgm'";

  my @version = selectall_hashref_query($::form, $dbh, $sql);

  return 1 if($version[0]->{installed_version});

  return 0;

}

=item C<check_trgm>

Checks if the postgresextension pg_trgm is installed and return 0 or 1.