Unroll LOINC & CPT lists to create a search_keys table for finding lab test indicators

gistfile1.rb

require "win32ole"

=begin

Parsing strategy for unrolling the lists.
  lists are either CPTs or LOINCs
  CPTs are \d{5}.
  Loincs are \d{4,5}-\d

  Particular, problematic list is:
  14646-4, 2085-9, 35197-3, 12772-0 (electrophoresis, prop is Acnc) 49130-8 (electrophoresis, prop is MCnc) 18263-4 (system is ser/plas. ultracentrifugate) (updated 11/2013)

  we want (code)(optional parenthetical)--any number of same.

=end

# db = 'g:\CTRHS\CHS\pardre1\repos\lab\supporting_files\lab_codes.mdb'
db = 'C:\Users\pardre1\Documents\vdw\lab\supporting_files\lab_codes.mdb'

@conn = WIN32OLE.new('ADODB.Connection')
@conn.Open("Driver={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=#{db}")

def insert_search_key(test_type, val, target, cmt)
  if val.length > 0 then
    sql ="INSERT INTO search_keys_new (test_type, key, target, comment) values ('#{test_type}', '#{val}', '#{target}', '#{cmt}')"
    puts(sql)
    @conn.Execute(sql)
  end
end

def unroll_list(test_type, target, list)
  return if list.nil?
  case target
  when 'LOINC'
    code_regex = Regexp.new('(\d{3,6}-\d)')
  when 'Panel CPT', 'Single CPT'
    code_regex = Regexp.new('(\d{5})')
  end

  components = list.split(code_regex)

  components.each_with_index do |s, i|
    key = s.strip
    nxt = ''
    if key.match(code_regex) then
      if i <= components.length then
        nxt = components[i+1]
        nxt.strip! if nxt
        nxt = '' if %w(, ;).include?(nxt)
      end
      insert_search_key(test_type, key, target, nxt) unless %w(TBD NA N/A ?).include?(key)
    end
  end
end

def loop_recs
  sql = "select TEST_TYPE, LOINC, CPT_CODES, CPT_PANELS from VDWLABreformatted"
  rset = WIN32OLE.new('ADODB.Recordset')
  rset.Open(sql, @conn, 3)
  @conn.Execute("delete from search_keys_new")
  rset.MoveFirst
  until rset.EOF
    test_type   = rset.Fields('TEST_TYPE').Value
    loinc       = rset.Fields('LOINC').Value
    cpt_codes   = rset.Fields('CPT_CODES').Value
    cpt_panels  = rset.Fields('CPT_PANELS').Value

    unroll_list(test_type, "LOINC"      , loinc)
    unroll_list(test_type, "Panel CPT"  , cpt_panels)
    unroll_list(test_type, "Single CPT" , cpt_codes)
    rset.MoveNext
  end
end

loop_recs

puts "Finished!"