3 * This library contains functions that implement the database load processing
4 * of external database files into openEMR
6 * Copyright (C) 2011 Phyaura, LLC <info@phyaura.com>
8 * LICENSE: This program is free software; you can redistribute it and/or
9 * modify it under the terms of the GNU General Public License
10 * as published by the Free Software Foundation; either version 2
11 * of the License, or (at your option) any later version.
12 * This program is distributed in the hope that it will be useful,
13 * but WITHOUT ANY WARRANTY; without even the implied warranty of
14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 * GNU General Public License for more details.
16 * You should have received a copy of the GNU General Public License
17 * along with this program. If not, see <http://opensource.org/licenses/gpl-license.php>;.
20 * @author Rohit Kumar <pandit.rohit@netsity.com>
21 * @author (Mac) Kevin McAloon <mcaloon@patienthealthcareanalytics.com>
22 * @author Brady Miller <brady@sparmy.com>
23 * @link http://www.open-emr.org
27 // Function to copy a package to temp
28 // $type (RXNORM, SNOMED etc.)
29 function temp_copy($filename,$type) {
31 if(!file_exists($filename)) {
35 if(!file_exists($GLOBALS['temporary_files_dir']."/".$type)) {
36 if(!mkdir($GLOBALS['temporary_files_dir']."/".$type, 0777, true)) {
41 if(copy($filename,$GLOBALS['temporary_files_dir']."/".$type."/".basename($filename))) {
49 // Function to unarchive a package
50 // $type (RXNORM, SNOMED etc.)
51 function temp_unarchive($filename,$type) {
52 $filename = $GLOBALS['temporary_files_dir']."/".$type."/".basename($filename);
53 if(!file_exists($filename)) {
58 // let's uzip the file
59 // use checksums to determine the "version"
61 $zip = new ZipArchive;
62 if ($zip->open($filename) === TRUE) {
63 if (!($zip->extractTo($GLOBALS['temporary_files_dir']."/".$type))) {
75 // Function to import the RXNORM tables
76 // $is_windows_flag - pass the IS_WINDOWS constant
77 function rxnorm_import($is_windows_flag) {
80 $dirScripts = $GLOBALS['temporary_files_dir']."/RXNORM/scripts/mysql";
81 $dir = $GLOBALS['temporary_files_dir']."/RXNORM/rrf";
82 $dir=str_replace('\\','/',$dir);
85 $rx_info['rxnatomarchive'] = array('title' => "Archive Data", 'dir' => "$dir", 'origin' => "RXNATOMARCHIVE.RRF", 'filename' => "RXNATOMARCHIVE.RRF", 'table' => "rxnatomarchive", 'required' => 0);
86 $rx_info['rxnconso'] = array('title' => "Concept Names and Sources", 'dir' => "$dir", 'origin' => "RXNCONSO.RRF", 'filename' => "RXNCONSO.RRF", 'table' => "rxnconso", 'required' => 1);
87 $rx_info['rxncui'] = array('title' => "Retired RXCUI Data", 'dir' => "$dir", 'origin' => "RXNCUI.RRF", 'filename' => "RXNCUI.RRF", 'table' => "rxncui", 'required' => 1);
88 $rx_info['rxncuichanges'] = array('title' => "Concept Changes", 'dir' => "$dir", 'origin' => "RXNCUICHANGES.RRF", 'filename' => "RXNCUICHANGES.RRF", 'table' => "rxncuichanges", 'required' => 1);
89 $rx_info['rxndoc'] = array('title' => "Documentation for Abbreviated Values", 'dir' => "$dir", 'origin' => "RXNDOC.RRF", 'filename' => "RXNDOC.RRF", 'table' => "rxndoc", 'required' => 1);
90 $rx_info['rxnrel'] = array('title' => "Relationships", 'dir' => "$dir", 'origin' => "RXNREL.RRF", 'filename' => "RXNREL.RRF", 'table' => "rxnrel", 'required' => 1);
91 $rx_info['rxnsab'] = array('title' => "Source Information", 'dir' => "$dir", 'origin' => "RXNSAB.RRF", 'filename' => "RXNSAB.RRF", 'table' => "rxnsab", 'required' => 0);
92 $rx_info['rxnsat'] = array('title' => "Simple Concept and Atom Attributes", 'dir' => "$dir", 'origin' => "RXNSAT.RRF", 'filename' => "RXNSAT.RRF", 'table' => "rxnsat", 'required' => 0);
93 $rx_info['rxnsty'] = array('title' => "Semantic Types ", 'dir' => "$dir", 'origin' => "RXNSTY.RRF", 'filename' => "RXNSTY.RRF", 'table' => "rxnsty", 'required' => 1);
96 $file_load = file_get_contents($dirScripts.'/Table_scripts_mysql_rxn.sql', true);
97 if ($is_windows_flag) {
98 $data_load = file_get_contents($dirScripts.'/Load_scripts_mysql_rxn_win.sql', true);
101 $data_load = file_get_contents($dirScripts.'/Load_scripts_mysql_rxn_unix.sql', true);
103 $indexes_load = file_get_contents($dirScripts.'/Indexes_mysql_rxn.sql', true);
106 // Creating the structure for table and applying indexes
109 $file_array=explode(";",$file_load);
110 foreach($file_array as $val){
117 $indexes_array=explode(";",$indexes_load);
119 foreach($indexes_array as $val1){
126 $data=explode(";",$data_load);
127 foreach($data as $val)
129 foreach($rx_info as $key => $value)
131 $file_name= $value['origin'];
132 $replacement=$dir."/".$file_name;
134 $pattern='/'.$file_name.'/';
135 if(strpos($val,$file_name) !== false) {
136 $val1 = str_replace($file_name,$replacement,$val);
137 if(trim($val1)!='') {
146 // Function to import SNOMED tables
147 function snomed_import($us_extension=FALSE) {
150 $table_array_for_snomed=array(
151 "sct_concepts_drop"=>"DROP TABLE IF EXISTS `sct_concepts`",
152 "sct_concepts_structure"=>"CREATE TABLE IF NOT EXISTS `sct_concepts` (
153 `ConceptId` bigint(20) NOT NULL,
154 `ConceptStatus` int(11) NOT NULL,
155 `FullySpecifiedName` varchar(255) NOT NULL,
156 `CTV3ID` varchar(5) NOT NULL,
157 `SNOMEDID` varchar(8) NOT NULL,
158 `IsPrimitive` tinyint(1) NOT NULL,
159 PRIMARY KEY (`ConceptId`)
161 "sct_descriptions_drop"=>"DROP TABLE IF EXISTS `sct_descriptions`",
162 "sct_descriptions_structure"=>"CREATE TABLE IF NOT EXISTS `sct_descriptions` (
163 `DescriptionId` bigint(20) NOT NULL,
164 `DescriptionStatus` int(11) NOT NULL,
165 `ConceptId` bigint(20) NOT NULL,
166 `Term` varchar(255) NOT NULL,
167 `InitialCapitalStatus` tinyint(1) NOT NULL,
168 `DescriptionType` int(11) NOT NULL,
169 `LanguageCode` varchar(8) NOT NULL,
170 PRIMARY KEY (`DescriptionId`)
172 "sct_relationships_drop"=>"DROP TABLE IF EXISTS `sct_relationships`",
173 "sct_relationships_structure"=>"CREATE TABLE IF NOT EXISTS `sct_relationships` (
174 `RelationshipId` bigint(20) NOT NULL,
175 `ConceptId1` bigint(20) NOT NULL,
176 `RelationshipType` bigint(20) NOT NULL,
177 `ConceptId2` bigint(20) NOT NULL,
178 `CharacteristicType` int(11) NOT NULL,
179 `Refinability` int(11) NOT NULL,
180 `RelationshipGroup` int(11) NOT NULL,
181 PRIMARY KEY (`RelationshipId`)
186 $dir_snomed = $GLOBALS['temporary_files_dir']."/SNOMED/";
187 $sub_path="Terminology/Content/";
189 $dir=str_replace('\\','/',$dir);
191 // executing the create statement for tables, these are defined in snomed_capture.inc file
192 // this is skipped if the US extension is being added
193 if (!$us_extension) {
194 foreach($table_array_for_snomed as $val){
201 // reading the SNOMED directory and identifying the files to import and replacing the variables by originals values.
202 if( is_dir($dir) && $handle = opendir($dir)) {
203 while (false !== ($filename = readdir($handle))) {
204 if ($filename != "." && $filename != ".." && !strpos($filename,"zip")) {
205 $path=$dir."".$filename."/".$sub_path;
206 if (!(is_dir($path))) {
207 $path=$dir."".$filename."/RF1Release/".$sub_path;
209 if( is_dir($path) && $handle1 = opendir($path)) {
210 while (false !== ($filename1 = readdir($handle1))) {
211 $load_script="Load data local infile '#FILENAME#' into table #TABLE# fields terminated by '\\t' ESCAPED BY '' lines terminated by '\\n' ignore 1 lines ";
212 $array_replace=array("#FILENAME#","#TABLE#");
213 if ($filename1 != "." && $filename1 != "..") {
214 $file_replace=$path.$filename1;
215 if(strpos($filename1,"Concepts") !== false){
216 $new_str=str_replace($array_replace,array($file_replace,"sct_concepts"),$load_script);
218 if(strpos($filename1,"Descriptions") !== false){
219 $new_str=str_replace($array_replace,array($file_replace,"sct_descriptions"),$load_script);
221 if(strpos($filename1,"Relationships") !== false){
222 $new_str=str_replace($array_replace,array($file_replace,"sct_relationships"),$load_script);
225 sqlStatement($new_str);
238 // Function to import ICD tables $type differentiates ICD 9, 10 and eventually 11 (circa 2018 :-) etc.
240 function icd_import($type) {
243 $dir_icd = $GLOBALS['temporary_files_dir']."/".$type."/";
244 $dir=str_replace('\\','/',$dir_icd);
248 // the incoming array is a metadata array containing keys that substr match to the incoming filename
249 // followed by the field name, position and length of each fixed length text record in the incoming
250 // flat files. There are separate definitions for ICD 9 and 10 based on the type passed in
252 if ($type == 'ICD9') {
253 $incoming['SHORT_DX'] = array('#TABLENAME#' => "icd9_dx_code",
254 '#FLD1#' => "dx_code", '#POS1#' => 1, '#LEN1#' => 5,
255 '#FLD2#' => "short_desc", '#POS2#' => 7, '#LEN2#' => 60);
256 $incoming['SHORT_SG'] = array('#TABLENAME#' => "icd9_sg_code",
257 '#FLD1#' => "sg_code", '#POS1#' => 1, '#LEN1#' => 4,
258 '#FLD2#' => "short_desc", '#POS2#' => 6, '#LEN2#' => 60);
259 $incoming['LONG_SG'] = array('#TABLENAME#' => "icd9_sg_long_code",
260 '#FLD1#' => "sg_code", '#POS1#' => 1, '#LEN1#' => 4,
261 '#FLD2#' => "long_desc", '#POS2#' => 6, '#LEN2#' => 300);
262 $incoming['LONG_DX'] = array('#TABLENAME#' => "icd9_dx_long_code",
263 '#FLD1#' => "dx_code", '#POS1#' => 1, '#LEN1#' => 5,
264 '#FLD2#' => "long_desc", '#POS2#' => 7, '#LEN2#' => 300);
267 $incoming['icd10pcs_order_'] = array('#TABLENAME#' => "icd10_pcs_order_code",
268 '#FLD1#' => "pcs_code", '#POS1#' => 7, '#LEN1#' => 7,
269 '#FLD2#' => "valid_for_coding", '#POS2#' => 15, '#LEN2#' => 1,
270 '#FLD3#' => "short_desc", '#POS3#' => 17, '#LEN3#' => 60,
271 '#FLD4#' => "long_desc", '#POS4#' => 78, '#LEN4#' => 300);
272 $incoming['icd10cm_order_'] = array('#TABLENAME#' => "icd10_dx_order_code",
273 '#FLD1#' => "dx_code", '#POS1#' =>7, '#LEN1#' => 7,
274 '#FLD2#' => "valid_for_coding", '#POS2#' => 15, '#LEN2#' => 1,
275 '#FLD3#' => "short_desc", '#POS3#' => 17, '#LEN3#' => 60,
276 '#FLD4#' => "long_desc", '#POS4#' => 78, '#LEN4#' => 300);
277 $incoming['reimb_map_pr_'] = array('#TABLENAME#' => "icd10_reimbr_pcs_9_10",
278 '#FLD1#' => "code", '#POS1#' => 1, '#LEN1#' => 7,
279 '#FLD2#' => "code_cnt", '#POS2#' => 9, '#LEN2#' => 1,
280 '#FLD3#' => "ICD9_01", '#POS3#' => 11, '#LEN3#' => 5,
281 '#FLD4#' => "ICD9_02", '#POS4#' => 17, '#LEN4#' => 5,
282 '#FLD5#' => "ICD9_03", '#POS5#' => 23, '#LEN5#' => 5,
283 '#FLD6#' => "ICD9_04", '#POS6#' => 29, '#LEN6#' => 5,
284 '#FLD7#' => "ICD9_05", '#POS7#' => 35, '#LEN7#' => 5,
285 '#FLD8#' => "ICD9_06", '#POS8#' => 41, '#LEN8#' => 5);
286 $incoming['reimb_map_dx_'] = array('#TABLENAME#' => "icd10_reimbr_dx_9_10",
287 '#FLD1#' => "code", '#POS1#' => 1, '#LEN1#' => 7,
288 '#FLD2#' => "code_cnt", '#POS2#' => 9, '#LEN2#' => 1,
289 '#FLD3#' => "ICD9_01", '#POS3#' => 11, '#LEN3#' => 5,
290 '#FLD4#' => "ICD9_02", '#POS4#' => 17, '#LEN4#' => 5,
291 '#FLD5#' => "ICD9_03", '#POS5#' => 23, '#LEN5#' => 5,
292 '#FLD6#' => "ICD9_04", '#POS6#' => 29, '#LEN6#' => 5,
293 '#FLD7#' => "ICD9_05", '#POS7#' => 35, '#LEN7#' => 5,
294 '#FLD8#' => "ICD9_06", '#POS8#' => 41, '#LEN8#' => 5);
295 $incoming['2012_I10gem'] = array('#TABLENAME#' => "icd10_gem_dx_10_9",
296 '#FLD1#' => "dx_icd10_source", '#POS1#' => 1, '#LEN1#' => 7,
297 '#FLD2#' => "dx_icd9_target", '#POS2#' => 9, '#LEN2#' => 5,
298 '#FLD3#' => "flags", '#POS3#' => 15, '#LEN3#' => 5);
299 $incoming['2012_I9gem'] = array('#TABLENAME#' => "icd10_gem_dx_9_10",
300 '#FLD1#' => "dx_icd9_source", '#POS1#' => 1, '#LEN1#' => 5,
301 '#FLD2#' => "dx_icd10_target", '#POS2#' => 7, '#LEN2#' => 7,
302 '#FLD3#' => "flags", '#POS3#' => 15, '#LEN3#' => 5);
303 $incoming['gem_pcsi9'] = array('#TABLENAME#' => "icd10_gem_pcs_10_9",
304 '#FLD1#' => "pcs_icd10_source", '#POS1#' => 1, '#LEN1#' => 7,
305 '#FLD2#' => "pcs_icd9_target", '#POS2#' => 9, '#LEN2#' => 5,
306 '#FLD3#' => "flags", '#POS3#' => 15, '#LEN3#' => 5);
307 $incoming['gem_i9pcs'] = array('#TABLENAME#' => "icd10_gem_pcs_9_10",
308 '#FLD1#' => "pcs_icd9_source", '#POS1#' => 1, '#LEN1#' => 5,
309 '#FLD2#' => "pcs_icd10_target", '#POS2#' => 7, '#LEN2#' => 7,
310 '#FLD3#' => "flags", '#POS3#' => 15, '#LEN3#' => 5);
313 // set up the start of the load script to be appended from the incoming array defined above where incoming
315 $db_load = "LOAD DATA LOCAL INFILE '#INFILE#' INTO TABLE #TABLENAME# FIELDS TERMINATED BY '\0' (@var) SET revision = 0, ";
316 $col_template = "#FLD# = trim(Substring(@var, #POS#, #LEN#))";
318 // load all data and set active revision
319 if( is_dir($dir) && $handle = opendir($dir)) {
320 while (false !== ($filename = readdir($handle))) {
321 // bypass unwanted entries
322 if (!stripos($filename, ".txt") || stripos($filename,"diff")) {
326 // reset the sql load command and susbtitute the filename
328 $run_sql = str_replace("#INFILE#", $dir . $filename, $run_sql);
329 $keys = array_keys($incoming);
330 while ($this_key = array_pop($keys)) {
331 if (stripos($filename, $this_key) !== false) {
333 // now substitute the tablename
334 $run_sql = str_replace("#TABLENAME#", $incoming[$this_key]['#TABLENAME#'], $run_sql);
336 // the range defines the maximum number of fields contained
337 // in any of the incoming files
338 foreach(range(1, 8) as $field) {
339 $fld = "#FLD" . $field . "#";
340 $nxtfld = "#FLD" . ($field+1) . "#";
341 $pos = "#POS" . $field . "#";
342 $len = "#LEN" . $field . "#";
344 // concat this fields template in the sql string
345 $run_sql .= $col_template;
346 $run_sql = str_replace("#FLD#", $incoming[$this_key][$fld], $run_sql);
347 $run_sql = str_replace("#POS#", $incoming[$this_key][$pos], $run_sql);
348 $run_sql = str_replace("#LEN#", $incoming[$this_key][$len], $run_sql);
349 // at the end of this table's field list
350 if (!array_key_exists($nxtfld, $incoming[$this_key])) {
355 sqlStatement($run_sql);
357 // now update the revision for this load
358 $res = sqlStatement("SELECT max(revision) rev FROM " . $incoming[$this_key]['#TABLENAME#']);
359 $row = sqlFetchArray($res);
360 $next_rev = $row['rev'] + 1;
361 $run_sql = "UPDATE " . $incoming[$this_key]['#TABLENAME#'] . " SET active = 0";
363 $run_sql = "UPDATE " . $incoming[$this_key]['#TABLENAME#'] . " SET active = 1, revision = ? WHERE revision = 0";
364 sqlQuery($run_sql, array($next_rev) );
372 echo htmlspecialchars( xl('ERROR: No ICD import directory.'), ENT_NOQUOTES)."<br>";
376 // now update the tables where necessary
377 if ($type == 'ICD9') {
378 sqlStatement("update `icd9_dx_code` SET formatted_dx_code = dx_code");
379 sqlStatement("update `icd9_dx_code` SET formatted_dx_code = concat(concat(left(dx_code, 3), '.'), substr(dx_code, 4)) WHERE dx_code RLIKE '^[V0-9]{1}.*' AND LENGTH(dx_code) > 3");
380 sqlStatement("update `icd9_dx_code` SET formatted_dx_code = concat(concat(left(dx_code, 4), '.'), substr(dx_code, 5)) WHERE dx_code RLIKE '^[E]{1}.*' AND LENGTH(dx_code) > 4");
381 sqlStatement("update `icd9_sg_code` SET formatted_sg_code = concat(concat(left(sg_code, 2), '.'), substr(sg_code, 3))");
382 sqlStatement("update `icd9_dx_code` A, `icd9_dx_long_code` B set A.long_desc = B.long_desc where A.dx_code = B.dx_code and A.active = 1 and A.long_desc is NULL");
383 sqlStatement("update `icd9_sg_code` A, `icd9_sg_long_code` B set A.long_desc = B.long_desc where A.sg_code = B.sg_code and A.active = 1 and A.long_desc is NULL");
386 sqlStatement("update `icd10_dx_order_code` SET formatted_dx_code = dx_code");
387 sqlStatement("update `icd10_dx_order_code` SET formatted_dx_code = concat(concat(left(dx_code, 3), '.'), substr(dx_code, 4)) WHERE LENGTH(dx_code) > 3");
393 // Function to clean up temp files
394 // $type (RXNORM etc.)
395 function temp_dir_cleanup($type) {
396 if(is_dir($GLOBALS['temporary_files_dir']."/".$type)) {
397 rmdir_recursive($GLOBALS['temporary_files_dir']."/".$type);
401 // Function to update version tracker table if successful
402 // $type (RXNORM etc.)
403 function update_tracker_table($type,$revision,$version,$file_checksum) {
404 if ($type == 'RXNORM') {
405 sqlStatement("INSERT INTO `standardized_tables_track` (`imported_date`,`name`,`revision_date`, `revision_version`, `file_checksum`) VALUES (NOW(),'RXNORM',?,?,?)", array($revision, $version, $file_checksum) );
408 else if ($type == 'SNOMED') {
409 sqlStatement("INSERT INTO `standardized_tables_track` (`imported_date`,`name`,`revision_date`, `revision_version`, `file_checksum`) VALUES (NOW(),'SNOMED',?,?,?)", array($revision, $version, $file_checksum) );
412 else if ($type == 'ICD9') {
413 sqlStatement("INSERT INTO `standardized_tables_track` (`imported_date`,`name`,`revision_date`, `revision_version`, `file_checksum`) VALUES (NOW(),'ICD9',?,?,?)", array($revision, $version, $file_checksum) );
416 else { // $type == 'ICD10')
417 sqlStatement("INSERT INTO `standardized_tables_track` (`imported_date`,`name`,`revision_date`, `revision_version`, `file_checksum`) VALUES (NOW(),'ICD10',?,?,?)", array($revision, $version, $file_checksum) );
423 // Function to delete an entire directory
424 function rmdir_recursive($dir) {
425 $files = scandir($dir);
426 array_shift($files); // remove '.' from array
427 array_shift($files); // remove '..' from array
429 foreach ($files as $file) {
430 $file = $dir . '/' . $file;
432 rmdir_recursive($file);
440 // function to cleanup temp, copy and unarchive the zip file
441 function handle_zip_file($mode, $file) {
442 // 1. copy the file to temp directory
443 if (!temp_copy($file,$mode)) {
444 echo htmlspecialchars( xl('ERROR: Unable to copy the file.'), ENT_NOQUOTES)."<br>";
445 temp_dir_cleanup($mode);
449 // 2. unarchive the file
450 if (!temp_unarchive($file,$mode)) {
451 echo htmlspecialchars( xl('ERROR: Unable to extract the file.'), ENT_NOQUOTES)."<br>";
452 temp_dir_cleanup($mode);