Class: OfacSdnIndividual

Inherits:
ActiveRecord::Base
  • Object
show all
Defined in:
app/models/ofac_sdn_individual.rb

Class Method Summary collapse

Instance Method Summary collapse

Class Method Details

.possible_sdns(name_array, use_ors = false) ⇒ Object

This code is really ugly, but given OfacIndividual.new(=> {:first_name => ‘Incredibly Long Name’, :last_name => ‘No Match’}).score it builds sql like this:

if use_ors is true:

SELECT last_name,

first_name_1,
first_name_2,
first_name_3,
first_name_4,
first_name_5,
first_name_6,
first_name_7,
first_name_8,
alternate_last_name,
alternate_first_name_1,
alternate_first_name_2,
alternate_first_name_3,
alternate_first_name_4,
alternate_first_name_5,
alternate_first_name_6,
alternate_first_name_7,
alternate_first_name_8,
address,
city

FROM “ofac_sdn_individuals” WHERE ( ( ( ( ( ( ( ( ( ( ( ( ( “ofac_sdn_individuals”.“last_name” = ‘NO’

                        OR "ofac_sdn_individuals"."first_name_1" =
                           'NO' )
                      OR "ofac_sdn_individuals"."first_name_2" = 'NO'
                   )
                    OR "ofac_sdn_individuals"."first_name_3" = 'NO' )
                  OR "ofac_sdn_individuals"."first_name_4" = 'NO' )
                OR "ofac_sdn_individuals"."first_name_5" = 'NO' )
              OR "ofac_sdn_individuals"."first_name_6" = 'NO' )
            OR "ofac_sdn_individuals"."first_name_7" = 'NO' )
          OR "ofac_sdn_individuals"."first_name_8" = 'NO' )
        OR ( ( ( ( ( ( ( ( "ofac_sdn_individuals"."last_name" =
                           'MATCH'
                            OR "ofac_sdn_individuals"."first_name_1" =
                               'MATCH'
                         )
                          OR "ofac_sdn_individuals"."first_name_2" =
                             'MATCH'
                       )
                        OR "ofac_sdn_individuals"."first_name_3" =
                           'MATCH' )
                      OR "ofac_sdn_individuals"."first_name_4" =
                         'MATCH' )
                    OR "ofac_sdn_individuals"."first_name_5" = 'MATCH'
                 )
                  OR "ofac_sdn_individuals"."first_name_6" = 'MATCH' )
                OR "ofac_sdn_individuals"."first_name_7" = 'MATCH' )
              OR "ofac_sdn_individuals"."first_name_8" = 'MATCH' ) )
      OR ( ( ( ( ( ( ( ( "ofac_sdn_individuals"."last_name" =
                         'INCREDIBLY'
                          OR "ofac_sdn_individuals"."first_name_1" =
                             'INCREDIBLY'
                       )
                        OR "ofac_sdn_individuals"."first_name_2" =
                           'INCREDIBLY'
                     )
                      OR "ofac_sdn_individuals"."first_name_3" =
                         'INCREDIBLY'
                   )
                    OR "ofac_sdn_individuals"."first_name_4" =
                       'INCREDIBLY' )
                  OR "ofac_sdn_individuals"."first_name_5" =
                     'INCREDIBLY' )
                OR "ofac_sdn_individuals"."first_name_6" =
                   'INCREDIBLY' )
              OR "ofac_sdn_individuals"."first_name_7" = 'INCREDIBLY'
           )
            OR "ofac_sdn_individuals"."first_name_8" = 'INCREDIBLY' )
   )
    OR ( ( ( ( ( ( ( ( "ofac_sdn_individuals"."last_name" = 'LONG'
                        OR "ofac_sdn_individuals"."first_name_1" =
                           'LONG' )
                      OR "ofac_sdn_individuals"."first_name_2" =
                         'LONG' )
                    OR "ofac_sdn_individuals"."first_name_3" = 'LONG'
                 )
                  OR "ofac_sdn_individuals"."first_name_4" = 'LONG' )
                OR "ofac_sdn_individuals"."first_name_5" = 'LONG' )
              OR "ofac_sdn_individuals"."first_name_6" = 'LONG' )
            OR "ofac_sdn_individuals"."first_name_7" = 'LONG' )
          OR "ofac_sdn_individuals"."first_name_8" = 'LONG' ) )
  OR ( ( ( ( ( ( ( ( "ofac_sdn_individuals"."last_name" = 'NAME'
                      OR "ofac_sdn_individuals"."first_name_1" =
                         'NAME' )
                    OR "ofac_sdn_individuals"."first_name_2" = 'NAME'
                 )
                  OR "ofac_sdn_individuals"."first_name_3" = 'NAME' )
                OR "ofac_sdn_individuals"."first_name_4" = 'NAME' )
              OR "ofac_sdn_individuals"."first_name_5" = 'NAME' )
            OR "ofac_sdn_individuals"."first_name_6" = 'NAME' )
          OR "ofac_sdn_individuals"."first_name_7" = 'NAME' )
        OR "ofac_sdn_individuals"."first_name_8" = 'NAME' ) )
OR (( ( ( ( ( ( ( ( ( ( ( (
                  "ofac_sdn_individuals"."alternate_last_name" =
                  'NO'
                   OR
                      "ofac_sdn_individuals"."alternate_first_name_1"
                      =
                      'NO'
                       )
                           OR
                    "ofac_sdn_individuals"."alternate_first_name_2"
                    =
                    'NO'
                             )
                         OR
                  "ofac_sdn_individuals"."alternate_first_name_3" =
                  'NO'
                          )
                       OR
                "ofac_sdn_individuals"."alternate_first_name_4" =
                'NO' )
                     OR
              "ofac_sdn_individuals"."alternate_first_name_5" =
              'NO'
                  )
                   OR "ofac_sdn_individuals"."alternate_first_name_6"
                      = 'NO'
                )
                 OR "ofac_sdn_individuals"."alternate_first_name_7" =
                    'NO' )
               OR "ofac_sdn_individuals"."alternate_first_name_8" =
                  'NO' )
             OR ( ( ( ( ( ( ( (
                      "ofac_sdn_individuals"."alternate_last_name"
                      =
                      'MATCH'
                       OR

“ofac_sdn_individuals”.“alternate_first_name_1”

‘MATCH’ )

OR

“ofac_sdn_individuals”.“alternate_first_name_2” = ‘MATCH’

)
 OR

“ofac_sdn_individuals”.“alternate_first_name_3”

‘MATCH’ )

OR "ofac_sdn_individuals"."alternate_first_name_4"
   =
   'MATCH'

) OR “ofac_sdn_individuals”.“alternate_first_name_5” =

'MATCH'

) OR “ofac_sdn_individuals”.“alternate_first_name_6” = ‘MATCH’ ) OR “ofac_sdn_individuals”.“alternate_first_name_7” = ‘MATCH’ ) OR “ofac_sdn_individuals”.“alternate_first_name_8” = ‘MATCH’ ) ) OR ( ( ( ( ( ( ( ( “ofac_sdn_individuals”.“alternate_last_name” =

'INCREDIBLY'
 OR

“ofac_sdn_individuals”.“alternate_first_name_1” = ‘INCREDIBLY’ )

OR

“ofac_sdn_individuals”.“alternate_first_name_2”

‘INCREDIBLY’

   )
OR "ofac_sdn_individuals"."alternate_first_name_3"
   =
   'INCREDIBLY' )

OR “ofac_sdn_individuals”.“alternate_first_name_4” =

'INCREDIBLY'

) OR “ofac_sdn_individuals”.“alternate_first_name_5” = ‘INCREDIBLY’ ) OR “ofac_sdn_individuals”.“alternate_first_name_6” = ‘INCREDIBLY’ ) OR “ofac_sdn_individuals”.“alternate_first_name_7” = ‘INCREDIBLY’ ) OR “ofac_sdn_individuals”.“alternate_first_name_8” = ‘INCREDIBLY’ ) ) OR ( ( ( ( ( ( ( ( “ofac_sdn_individuals”.“alternate_last_name” =

'LONG'
 OR

“ofac_sdn_individuals”.“alternate_first_name_1”

‘LONG’ )

OR "ofac_sdn_individuals"."alternate_first_name_2"
   =
   'LONG'

) OR “ofac_sdn_individuals”.“alternate_first_name_3” =

'LONG'

) OR “ofac_sdn_individuals”.“alternate_first_name_4” = ‘LONG’ ) OR “ofac_sdn_individuals”.“alternate_first_name_5” = ‘LONG’ ) OR “ofac_sdn_individuals”.“alternate_first_name_6” = ‘LONG’ ) OR “ofac_sdn_individuals”.“alternate_first_name_7” = ‘LONG’ ) OR “ofac_sdn_individuals”.“alternate_first_name_8” = ‘LONG’ ) ) OR ( ( ( ( ( ( ( ( “ofac_sdn_individuals”.“alternate_last_name” = ‘NAME’

OR "ofac_sdn_individuals"."alternate_first_name_1"
   =
   'NAME'

) OR “ofac_sdn_individuals”.“alternate_first_name_2” =

'NAME'

) OR “ofac_sdn_individuals”.“alternate_first_name_3” = ‘NAME’ ) OR “ofac_sdn_individuals”.“alternate_first_name_4” = ‘NAME’ ) OR “ofac_sdn_individuals”.“alternate_first_name_5” = ‘NAME’ ) OR “ofac_sdn_individuals”.“alternate_first_name_6” = ‘NAME’ ) OR “ofac_sdn_individuals”.“alternate_first_name_7” = ‘NAME’ ) OR “ofac_sdn_individuals”.“alternate_first_name_8” = ‘NAME’ ) )) )

if use_ors is false, when you call db_hit? OfacIndividual.new(=> {:first_name => ‘Incredibly Long Name’, :last_name => ‘No Match’}).db_hit?

SELECT 1 AS one FROM “ofac_sdn_individuals” WHERE ( ( ( ( ( ( ( ( ( “ofac_sdn_individuals”.“last_name” = ‘NO’

                     OR "ofac_sdn_individuals"."first_name_1" = 'NO' )
                   OR "ofac_sdn_individuals"."first_name_2" = 'NO' )
                 OR "ofac_sdn_individuals"."first_name_3" = 'NO' )
               OR "ofac_sdn_individuals"."first_name_4" = 'NO' )
             OR "ofac_sdn_individuals"."first_name_5" = 'NO' )
           OR "ofac_sdn_individuals"."first_name_6" = 'NO' )
         OR "ofac_sdn_individuals"."first_name_7" = 'NO' )
       OR "ofac_sdn_individuals"."first_name_8" = 'NO' )
AND ( ( ( ( ( ( ( ( "ofac_sdn_individuals"."last_name" = 'MATCH'
                     OR "ofac_sdn_individuals"."first_name_1" = 'MATCH'
                  )
                   OR "ofac_sdn_individuals"."first_name_2" = 'MATCH' )
                 OR "ofac_sdn_individuals"."first_name_3" = 'MATCH' )
               OR "ofac_sdn_individuals"."first_name_4" = 'MATCH' )
             OR "ofac_sdn_individuals"."first_name_5" = 'MATCH' )
           OR "ofac_sdn_individuals"."first_name_6" = 'MATCH' )
         OR "ofac_sdn_individuals"."first_name_7" = 'MATCH' )
       OR "ofac_sdn_individuals"."first_name_8" = 'MATCH' )
AND ( ( ( ( ( ( ( ( "ofac_sdn_individuals"."last_name" = 'INCREDIBLY'
                     OR "ofac_sdn_individuals"."first_name_1" =
                        'INCREDIBLY' )
                   OR "ofac_sdn_individuals"."first_name_2" =
                      'INCREDIBLY' )
                 OR "ofac_sdn_individuals"."first_name_3" =
                    'INCREDIBLY' )
               OR "ofac_sdn_individuals"."first_name_4" = 'INCREDIBLY'
            )
             OR "ofac_sdn_individuals"."first_name_5" = 'INCREDIBLY' )
           OR "ofac_sdn_individuals"."first_name_6" = 'INCREDIBLY' )
         OR "ofac_sdn_individuals"."first_name_7" = 'INCREDIBLY' )
       OR "ofac_sdn_individuals"."first_name_8" = 'INCREDIBLY' )
AND ( ( ( ( ( ( ( ( "ofac_sdn_individuals"."last_name" = 'LONG'
                     OR "ofac_sdn_individuals"."first_name_1" = 'LONG'
                  )
                   OR "ofac_sdn_individuals"."first_name_2" = 'LONG' )
                 OR "ofac_sdn_individuals"."first_name_3" = 'LONG' )
               OR "ofac_sdn_individuals"."first_name_4" = 'LONG' )
             OR "ofac_sdn_individuals"."first_name_5" = 'LONG' )
           OR "ofac_sdn_individuals"."first_name_6" = 'LONG' )
         OR "ofac_sdn_individuals"."first_name_7" = 'LONG' )
       OR "ofac_sdn_individuals"."first_name_8" = 'LONG' )
AND ( ( ( ( ( ( ( ( "ofac_sdn_individuals"."last_name" = 'NAME'
                     OR "ofac_sdn_individuals"."first_name_1" = 'NAME'
                  )
                   OR "ofac_sdn_individuals"."first_name_2" = 'NAME' )
                 OR "ofac_sdn_individuals"."first_name_3" = 'NAME' )
               OR "ofac_sdn_individuals"."first_name_4" = 'NAME' )
             OR "ofac_sdn_individuals"."first_name_5" = 'NAME' )
           OR "ofac_sdn_individuals"."first_name_6" = 'NAME' )
         OR "ofac_sdn_individuals"."first_name_7" = 'NAME' )
       OR "ofac_sdn_individuals"."first_name_8" = 'NAME' )
 OR ( ( ( ( ( ( ( ( ( "ofac_sdn_individuals"."alternate_last_name" =
                      'NO'
                       OR
                "ofac_sdn_individuals"."alternate_first_name_1" =
                'NO'
                    )
                     OR "ofac_sdn_individuals"."alternate_first_name_2"
                        = 'NO'
                  )
                   OR "ofac_sdn_individuals"."alternate_first_name_3" =
                      'NO' )
                 OR "ofac_sdn_individuals"."alternate_first_name_4" =
                    'NO' )
               OR "ofac_sdn_individuals"."alternate_first_name_5" =
                  'NO' )
             OR "ofac_sdn_individuals"."alternate_first_name_6" = 'NO'
          )
           OR "ofac_sdn_individuals"."alternate_first_name_7" = 'NO' )
         OR "ofac_sdn_individuals"."alternate_first_name_8" = 'NO' )
      AND ( ( ( ( ( ( ( ( "ofac_sdn_individuals"."alternate_last_name"
                          =
                          'MATCH'
                           OR
                    "ofac_sdn_individuals"."alternate_first_name_1"
                    =
                    'MATCH' )
                         OR
                  "ofac_sdn_individuals"."alternate_first_name_2" =
                  'MATCH'
                      )
                       OR
                "ofac_sdn_individuals"."alternate_first_name_3" =
                'MATCH'
                    )
                     OR "ofac_sdn_individuals"."alternate_first_name_4"
                        =
                        'MATCH' )
                   OR "ofac_sdn_individuals"."alternate_first_name_5" =
                      'MATCH'
                )
                 OR "ofac_sdn_individuals"."alternate_first_name_6" =
                    'MATCH'
              )
               OR "ofac_sdn_individuals"."alternate_first_name_7" =
                  'MATCH' )
             OR "ofac_sdn_individuals"."alternate_first_name_8" =
                'MATCH' )
      AND ( ( ( ( ( ( ( ( "ofac_sdn_individuals"."alternate_last_name"
                          =
                          'INCREDIBLY'
                           OR
                    "ofac_sdn_individuals"."alternate_first_name_1"
                    =
                    'INCREDIBLY'
                    )
                         OR
                  "ofac_sdn_individuals"."alternate_first_name_2" =
                  'INCREDIBLY'
                          )
                       OR
                "ofac_sdn_individuals"."alternate_first_name_3" =
                'INCREDIBLY' )
                     OR "ofac_sdn_individuals"."alternate_first_name_4"
                        =
                        'INCREDIBLY'
                  )
                   OR "ofac_sdn_individuals"."alternate_first_name_5" =
                      'INCREDIBLY'
                )
                 OR "ofac_sdn_individuals"."alternate_first_name_6" =
                    'INCREDIBLY' )
               OR "ofac_sdn_individuals"."alternate_first_name_7" =
                  'INCREDIBLY'
            )
             OR "ofac_sdn_individuals"."alternate_first_name_8" =
                'INCREDIBLY'
          )
      AND ( ( ( ( ( ( ( ( "ofac_sdn_individuals"."alternate_last_name"
                          =
                          'LONG'
                           OR
                    "ofac_sdn_individuals"."alternate_first_name_1"
                    =
                    'LONG'
                        )
                         OR
                  "ofac_sdn_individuals"."alternate_first_name_2" =
                  'LONG'
                      )
                       OR
                "ofac_sdn_individuals"."alternate_first_name_3" =
                'LONG' )
                     OR "ofac_sdn_individuals"."alternate_first_name_4"
                        =
                        'LONG'
                  )
                   OR "ofac_sdn_individuals"."alternate_first_name_5" =
                      'LONG'
                )
                 OR "ofac_sdn_individuals"."alternate_first_name_6" =
                    'LONG' )
               OR "ofac_sdn_individuals"."alternate_first_name_7" =
                  'LONG' )
             OR "ofac_sdn_individuals"."alternate_first_name_8" =
                'LONG' )
      AND ( ( ( ( ( ( ( ( "ofac_sdn_individuals"."alternate_last_name"
                          =
                          'NAME'
                           OR
                    "ofac_sdn_individuals"."alternate_first_name_1"
                    =
                    'NAME'
                        )
                         OR
                  "ofac_sdn_individuals"."alternate_first_name_2" =
                  'NAME'
                      )
                       OR
                "ofac_sdn_individuals"."alternate_first_name_3" =
                'NAME' )
                     OR "ofac_sdn_individuals"."alternate_first_name_4"
                        =
                        'NAME'
                  )
                   OR "ofac_sdn_individuals"."alternate_first_name_5" =
                      'NAME'
                )
                 OR "ofac_sdn_individuals"."alternate_first_name_6" =
                    'NAME' )
               OR "ofac_sdn_individuals"."alternate_first_name_7" =
                  'NAME' )
             OR "ofac_sdn_individuals"."alternate_first_name_8" =
                'NAME' ) ) )



439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
 
# File 'app/models/ofac_sdn_individual.rb', line 439

def self.possible_sdns(name_array, use_ors = false)
  ofac_sdn_individual = OfacSdnIndividual.arel_table

  main_conditions = nil
  alt_conditions = nil
  name_array.each do |name|
    last_name = ofac_sdn_individual[:last_name].eq(name)
    alternate_last_name = ofac_sdn_individual[:alternate_last_name].eq(name)
    first_name_1 = ofac_sdn_individual[:first_name_1].eq(name)
    first_name_2 = ofac_sdn_individual[:first_name_2].eq(name)
    first_name_3 = ofac_sdn_individual[:first_name_3].eq(name)
    first_name_4 = ofac_sdn_individual[:first_name_4].eq(name)
    first_name_5 = ofac_sdn_individual[:first_name_5].eq(name)
    first_name_6 = ofac_sdn_individual[:first_name_6].eq(name)
    first_name_7 = ofac_sdn_individual[:first_name_7].eq(name)
    first_name_8 = ofac_sdn_individual[:first_name_8].eq(name)
    alternate_first_name_1 = ofac_sdn_individual[:alternate_first_name_1].eq(name)
    alternate_first_name_2 = ofac_sdn_individual[:alternate_first_name_2].eq(name)
    alternate_first_name_3 = ofac_sdn_individual[:alternate_first_name_3].eq(name)
    alternate_first_name_4 = ofac_sdn_individual[:alternate_first_name_4].eq(name)
    alternate_first_name_5 = ofac_sdn_individual[:alternate_first_name_5].eq(name)
    alternate_first_name_6 = ofac_sdn_individual[:alternate_first_name_6].eq(name)
    alternate_first_name_7 = ofac_sdn_individual[:alternate_first_name_7].eq(name)
    alternate_first_name_8 = ofac_sdn_individual[:alternate_first_name_8].eq(name)
    if use_ors
      if main_conditions
        main_conditions = main_conditions.or(last_name.or(first_name_1).or(first_name_2).or(first_name_3).or(first_name_4).or(first_name_5).or(first_name_6).or(first_name_7).or(first_name_8))
        alt_conditions = alt_conditions.or(alternate_last_name.or(alternate_first_name_1).or(alternate_first_name_2).or(alternate_first_name_3).or(alternate_first_name_4).or(alternate_first_name_5).or(alternate_first_name_6).or(alternate_first_name_7).or(alternate_first_name_8))
      else
        main_conditions = last_name.or(first_name_1).or(first_name_2).or(first_name_3).or(first_name_4).or(first_name_5).or(first_name_6).or(first_name_7).or(first_name_8)
        alt_conditions = alternate_last_name.or(alternate_first_name_1).or(alternate_first_name_2).or(alternate_first_name_3).or(alternate_first_name_4).or(alternate_first_name_5).or(alternate_first_name_6).or(alternate_first_name_7).or(alternate_first_name_8)
      end
    else
      if main_conditions
        main_conditions = main_conditions.and(last_name.or(first_name_1).or(first_name_2).or(first_name_3).or(first_name_4).or(first_name_5).or(first_name_6).or(first_name_7).or(first_name_8))
        alt_conditions = alt_conditions.and(alternate_last_name.or(alternate_first_name_1).or(alternate_first_name_2).or(alternate_first_name_3).or(alternate_first_name_4).or(alternate_first_name_5).or(alternate_first_name_6).or(alternate_first_name_7).or(alternate_first_name_8))
      else
        main_conditions = last_name.or(first_name_1).or(first_name_2).or(first_name_3).or(first_name_4).or(first_name_5).or(first_name_6).or(first_name_7).or(first_name_8)
        alt_conditions = alternate_last_name.or(alternate_first_name_1).or(alternate_first_name_2).or(alternate_first_name_3).or(alternate_first_name_4).or(alternate_first_name_5).or(alternate_first_name_6).or(alternate_first_name_7).or(alternate_first_name_8)
      end
    end
  end

  select('last_name, first_name_1, first_name_2, first_name_3, first_name_4, first_name_5, first_name_6, first_name_7, first_name_8, alternate_last_name, alternate_first_name_1, alternate_first_name_2, alternate_first_name_3, alternate_first_name_4, alternate_first_name_5, alternate_first_name_6, alternate_first_name_7, alternate_first_name_8, address, city').
      where("#{main_conditions.to_sql} or (#{alt_conditions.to_sql})")
end

Instance Method Details

#alternate_identity_name ⇒ Object 



490
491
492
 
# File 'app/models/ofac_sdn_individual.rb', line 490

def alternate_identity_name
  "#{alternate_last_name}, #{alternate_first_name_1} #{alternate_first_name_2} #{alternate_first_name_3} #{alternate_first_name_4} #{alternate_first_name_5} #{alternate_first_name_6} #{alternate_first_name_7} #{alternate_first_name_8}".strip
end

#name ⇒ Object 



486
487
488
 
# File 'app/models/ofac_sdn_individual.rb', line 486

def name
  "#{last_name}, #{first_name_1} #{first_name_2} #{first_name_3} #{first_name_4} #{first_name_5} #{first_name_6} #{first_name_7} #{first_name_8}".strip
end