contrib/util/load_fda_drugs.plx

   1 #!/usr/bin/perl
   2 use strict;
   3
   4 use DBI;
   5
   6 #######################################################################
   7 # Copyright (C) 2005, 2008 Rod Roark <rod@sunsetsystems.com>
   8 #
   9 # This program is free software; you can redistribute it and/or
  10 # modify it under the terms of the GNU General Public License
  11 # as published by the Free Software Foundation; either version 2
  12 # of the License, or (at your option) any later version.
  13 #
  14 # This loads the FDA Orange Book data for drugs into the "drugs"
  15 # table of OpenEMR. If a row already exists with the same drug name
  16 # it is ignored, otherwise a new row is created with the drug
  17 # trade name provided from the Orange Book data.
  18 #
  19 # Run it like this:
  20 #
  21 #   ./load_fda_drugs.plx < Products.txt
  22 #
  23 # The Orange Book Data files as of this writing are at:
  24 #   http://www.fda.gov/cder/orange/obreadme.htm
  25 # Get the EOBZIP.ZIP file and extract the Products.txt file from there
  26 #######################################################################
  27
  28 #######################################################################
  29 #               Parameters that you should customize                  #
  30 #######################################################################
  31
  32 my $DBNAME         = "openemr";  # database name
  33
  34 # You can hard-code the database user name and password (see below),
  35 # or else put them into the environment with bash commands like these
  36 # before running this script:
  37 #
  38 #   export DBI_USER=username
  39 #   export DBI_PASS=password
  40 #
  41 my $dbh = DBI->connect("dbi:mysql:dbname=$DBNAME") or die $DBI::errstr;
  42
  43 # my $dbh = DBI->connect("dbi:mysql:dbname=$DBNAME", "username", "password")
  44 #   or die $DBI::errstr;
  45
  46 #######################################################################
  47 #                             Startup                                 #
  48 #######################################################################
  49
  50 $| = 1; # Turn on autoflushing of stdout.
  51
  52 my $countnew = 0;
  53 my $countup  = 0;
  54
  55 #######################################################################
  56 #                            Main Loop                                #
  57 #######################################################################
  58
  59 while (my $line = <STDIN>) {
  60
  61     my ($ingredients, $dosage_route, $tradename, $applicant,
  62         $strength, $ndaNum, $prodNum, $teCode, $approvalDate,
  63         $refDrug, $type, $fullName) = split /~/, $line;
  64
  65     # check for existing record
  66     my $usth = $dbh->prepare("SELECT drug_id FROM drugs " .
  67                 "WHERE name=".$dbh->quote($tradename) )
  68     or die $dbh->errstr;
  69     $usth->execute() or die $usth->errstr;
  70     my @urow = $usth->fetchrow_array();
  71
  72     my $query;
  73     my $drug_id;
  74
  75     if (! @urow) {
  76         # add a new drug
  77         $query = "INSERT INTO drugs" .
  78             "( name ) VALUES " .
  79             "( ".$dbh->quote($tradename)." )";
  80         $dbh->do($query) or die $query;
  81         $drug_id = $dbh->{'mysql_insertid'};
  82         print $query . "\n";
  83         ++$countnew;
  84     }
  85     else {
  86         $drug_id = $urow[0];
  87         print "Skipped $tradename, already exists\n";
  88         ++$countup;
  89     }
  90 }
  91
  92 #######################################################################
  93 #                             Shutdown                                #
  94 #######################################################################
  95
  96 print "\nInserted $countnew rows, skipped $countup rows.\n";
  97
  98 $dbh->disconnect;