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.

my $trgm = SL::DB::ShopOrder->check_trgm;

  billing_street    => 'Evil Street 666',

  billing_email     => 'email',

note('adding 2 similar customers and 1 dissimilar but same email');

  street  => 'Evil Street 666', # difference not large enough from "Evil Street", street matches

my $customer_similar_2 = new_customer(

  name    => "Different Name",

  street  => 'Evil Straet', # difference not large enough from "Evil Street", street matches

  zipcode => $customer->zipcode,

  email   => "foofoo",

)->save;

my $customer_same_email = new_customer(

  name    => "Different Name",

  street  => 'Angel Way', # difference large enough from "Evil Street", street not matches , same email

  zipcode => $customer->zipcode,

  email   => 'email',

)->save;

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

if($trgm){

  is(scalar @{ $fuzzy_customers }, 4, 'found 4 matching customers (zipcode equal + street similar + same email) trgm_pg is installed');

}else{

  is(scalar @{ $fuzzy_customers }, 3, 'found 3 matching customers (zipcode equal + %street% + same email) trgm_pg is not installed, could be 4 with trgm_pg');

}