4 * This library contains functions that implement the database load processing
5 * of external database files into openEMR
8 * @link https://www.open-emr.org
9 * @author Rohit Kumar <pandit.rohit@netsity.com>
10 * @author (Mac) Kevin McAloon <mcaloon@patienthealthcareanalytics.com>
11 * @author Brady Miller <brady.g.miller@gmail.com>
12 * @author Roberto Vasquez <robertogagliotta@gmail.com>
13 * @author Stephen Waite <stephen.waite@cmsvt.com>
14 * @copyright Copyright (c) 2011 Phyaura, LLC <info@phyaura.com>
15 * @copyright Copyright (c) 2019 Brady Miller <brady.g.miller@gmail.com>
16 * @copyright Copyright (c) 2019 Stephen Waite <stephen.waite@cmsvt.com>
17 * @license https://github.com/openemr/openemr/blob/master/LICENSE GNU General Public License 3
20 // Function to copy a package to temp
21 // $type (RXNORM, SNOMED etc.)
22 function temp_copy($filename, $type)
25 if (!file_exists($filename)) {
29 if (!file_exists($GLOBALS['temporary_files_dir'] . "/" . $type)) {
30 if (!mkdir($GLOBALS['temporary_files_dir'] . "/" . $type, 0777, true)) {
35 if (copy($filename, $GLOBALS['temporary_files_dir'] . "/" . $type . "/" . basename($filename))) {
42 // Function to unarchive a package
43 // $type (RXNORM, SNOMED etc.)
44 function temp_unarchive($filename, $type)
46 $filename = $GLOBALS['temporary_files_dir'] . "/" . $type . "/" . basename($filename);
47 if (!file_exists($filename)) {
49 } elseif ($type == "ICD10") {
50 // copy zip file contents to /tmp/ICD10 due to CMS zip file
51 $zip = new ZipArchive();
52 $path = $GLOBALS['temporary_files_dir'] . "/" . $type;
53 if ($zip->open($filename) === true) {
54 for ($i = 0; $i < $zip->numFiles; $i++) {
55 $sub_dir_filename = $zip->getNameIndex($i);
56 $fileinfo = pathinfo($sub_dir_filename);
57 if (!(copy("zip://" . $filename . "#" . $sub_dir_filename, "$path/" . $fileinfo['basename']))) {
68 $zip = new ZipArchive();
69 if ($zip->open($filename) === true) {
70 if (!($zip->extractTo($GLOBALS['temporary_files_dir'] . "/" . $type))) {
79 // Function to import the RXNORM tables
80 // $is_windows_flag - pass the IS_WINDOWS constant
81 function rxnorm_import($is_windows_flag)
85 $dirScripts = $GLOBALS['temporary_files_dir'] . "/RXNORM/scripts/mysql";
86 $dir = $GLOBALS['temporary_files_dir'] . "/RXNORM/rrf";
87 $dir = str_replace('\\', '/', $dir);
90 $rx_info['rxnatomarchive'] = array('title' => "Archive Data", 'dir' => "$dir", 'origin' => "RXNATOMARCHIVE.RRF", 'filename' => "RXNATOMARCHIVE.RRF", 'table' => "rxnatomarchive", 'required' => 0);
91 $rx_info['rxnconso'] = array('title' => "Concept Names and Sources", 'dir' => "$dir", 'origin' => "RXNCONSO.RRF", 'filename' => "RXNCONSO.RRF", 'table' => "rxnconso", 'required' => 1);
92 $rx_info['rxncui'] = array('title' => "Retired RXCUI Data", 'dir' => "$dir", 'origin' => "RXNCUI.RRF", 'filename' => "RXNCUI.RRF", 'table' => "rxncui", 'required' => 1);
93 $rx_info['rxncuichanges'] = array('title' => "Concept Changes", 'dir' => "$dir", 'origin' => "RXNCUICHANGES.RRF", 'filename' => "RXNCUICHANGES.RRF", 'table' => "rxncuichanges", 'required' => 1);
94 $rx_info['rxndoc'] = array('title' => "Documentation for Abbreviated Values", 'dir' => "$dir", 'origin' => "RXNDOC.RRF", 'filename' => "RXNDOC.RRF", 'table' => "rxndoc", 'required' => 1);
95 $rx_info['rxnrel'] = array('title' => "Relationships", 'dir' => "$dir", 'origin' => "RXNREL.RRF", 'filename' => "RXNREL.RRF", 'table' => "rxnrel", 'required' => 1);
96 $rx_info['rxnsab'] = array('title' => "Source Information", 'dir' => "$dir", 'origin' => "RXNSAB.RRF", 'filename' => "RXNSAB.RRF", 'table' => "rxnsab", 'required' => 0);
97 $rx_info['rxnsat'] = array('title' => "Simple Concept and Atom Attributes", 'dir' => "$dir", 'origin' => "RXNSAT.RRF", 'filename' => "RXNSAT.RRF", 'table' => "rxnsat", 'required' => 0);
98 $rx_info['rxnsty'] = array('title' => "Semantic Types ", 'dir' => "$dir", 'origin' => "RXNSTY.RRF", 'filename' => "RXNSTY.RRF", 'table' => "rxnsty", 'required' => 1);
101 $file_load = file_get_contents($dirScripts . '/Table_scripts_mysql_rxn.sql', true);
102 if ($is_windows_flag) {
103 $data_load = file_get_contents($dirScripts . '/Load_scripts_mysql_rxn_win.sql', true);
105 $data_load = file_get_contents($dirScripts . '/Load_scripts_mysql_rxn_unix.sql', true);
108 $indexes_load = file_get_contents($dirScripts . '/Indexes_mysql_rxn.sql', true);
111 // Creating the structure for table and applying indexes
114 $file_array = explode(";", $file_load);
115 foreach ($file_array as $val) {
116 if (trim($val) != '') {
117 sqlStatementNoLog($val);
121 $indexes_array = explode(";", $indexes_load);
123 foreach ($indexes_array as $val1) {
124 if (trim($val1) != '') {
125 sqlStatementNoLog($val1);
130 // Settings to drastically speed up import with InnoDB
131 sqlStatementNoLog("SET autocommit=0");
132 sqlStatementNoLog("START TRANSACTION");
133 $data = explode(";", $data_load);
134 foreach ($data as $val) {
135 foreach ($rx_info as $key => $value) {
136 $file_name = $value['origin'];
137 $replacement = $dir . "/" . $file_name;
139 $pattern = '/' . $file_name . '/';
140 if (strpos($val, $file_name) !== false) {
141 $val1 = str_replace($file_name, $replacement, $val);
142 if (trim($val1) != '') {
143 sqlStatementNoLog($val1);
149 // Settings to drastically speed up import with InnoDB
150 sqlStatementNoLog("COMMIT");
151 sqlStatementNoLog("SET autocommit=1");
156 // Function to import SNOMED tables
157 function snomed_import($us_extension = false)
161 $table_array_for_snomed = array(
162 "sct_concepts_drop" => "DROP TABLE IF EXISTS `sct_concepts`",
163 "sct_concepts_structure" => "CREATE TABLE IF NOT EXISTS `sct_concepts` (
164 `ConceptId` bigint(20) NOT NULL,
165 `ConceptStatus` int(11) NOT NULL,
166 `FullySpecifiedName` varchar(255) NOT NULL,
167 `CTV3ID` varchar(5) NOT NULL,
168 `SNOMEDID` varchar(8) NOT NULL,
169 `IsPrimitive` tinyint(1) NOT NULL,
170 PRIMARY KEY (`ConceptId`)
172 "sct_descriptions_drop" => "DROP TABLE IF EXISTS `sct_descriptions`",
173 "sct_descriptions_structure" => "CREATE TABLE IF NOT EXISTS `sct_descriptions` (
174 `DescriptionId` bigint(20) NOT NULL,
175 `DescriptionStatus` int(11) NOT NULL,
176 `ConceptId` bigint(20) NOT NULL,
177 `Term` varchar(255) NOT NULL,
178 `InitialCapitalStatus` tinyint(1) NOT NULL,
179 `DescriptionType` int(11) NOT NULL,
180 `LanguageCode` varchar(8) NOT NULL,
181 PRIMARY KEY (`DescriptionId`)
183 "sct_relationships_drop" => "DROP TABLE IF EXISTS `sct_relationships`",
184 "sct_relationships_structure" => "CREATE TABLE IF NOT EXISTS `sct_relationships` (
185 `RelationshipId` bigint(20) NOT NULL,
186 `ConceptId1` bigint(20) NOT NULL,
187 `RelationshipType` bigint(20) NOT NULL,
188 `ConceptId2` bigint(20) NOT NULL,
189 `CharacteristicType` int(11) NOT NULL,
190 `Refinability` int(11) NOT NULL,
191 `RelationshipGroup` int(11) NOT NULL,
192 PRIMARY KEY (`RelationshipId`)
197 $dir_snomed = $GLOBALS['temporary_files_dir'] . "/SNOMED/";
198 $sub_path = "Terminology/Content/";
200 $dir = str_replace('\\', '/', $dir);
202 // executing the create statement for tables, these are defined in snomed_capture.inc file
203 // this is skipped if the US extension is being added
204 if (!$us_extension) {
205 foreach ($table_array_for_snomed as $val) {
206 if (trim($val) != '') {
212 // reading the SNOMED directory and identifying the files to import and replacing the variables by originals values.
213 if (is_dir($dir) && $handle = opendir($dir)) {
214 while (false !== ($filename = readdir($handle))) {
215 if ($filename != "." && $filename != ".." && !strpos($filename, "zip")) {
216 $path = $dir . "" . $filename . "/" . $sub_path;
217 if (!(is_dir($path))) {
218 $path = $dir . "" . $filename . "/RF1Release/" . $sub_path;
221 if (is_dir($path) && $handle1 = opendir($path)) {
222 while (false !== ($filename1 = readdir($handle1))) {
223 $load_script = "Load data local infile '#FILENAME#' into table #TABLE# fields terminated by '\\t' ESCAPED BY '' lines terminated by '\\n' ignore 1 lines ";
224 $array_replace = array("#FILENAME#","#TABLE#");
225 if ($filename1 != "." && $filename1 != "..") {
226 $file_replace = $path . $filename1;
227 if (strpos($filename1, "Concepts") !== false) {
228 $new_str = str_replace($array_replace, array($file_replace,"sct_concepts"), $load_script);
231 if (strpos($filename1, "Descriptions") !== false) {
232 $new_str = str_replace($array_replace, array($file_replace,"sct_descriptions"), $load_script);
235 if (strpos($filename1, "Relationships") !== false) {
236 $new_str = str_replace($array_replace, array($file_replace,"sct_relationships"), $load_script);
239 if ($new_str != '') {
240 sqlStatement($new_str);
256 function drop_old_sct()
258 $array_to_truncate = array(
259 "sct_concepts_drop" => "DROP TABLE IF EXISTS `sct_concepts`",
260 "sct_descriptions_drop" => "DROP TABLE IF EXISTS `sct_descriptions`",
261 "sct_relationships_drop" => "DROP TABLE IF EXISTS `sct_relationships`"
263 foreach ($array_to_truncate as $val) {
264 if (trim($val) != '') {
270 function drop_old_sct2()
272 $array_to_truncate = array(
273 "sct2_concept_drop" => "DROP TABLE IF EXISTS `sct2_concept`",
274 "sct2_description_drop" => "DROP TABLE IF EXISTS `sct2_description`",
275 "sct2_identifier_drop" => "DROP TABLE IF EXISTS `sct2_identifier`",
276 "sct2_relationship_drop" => "DROP TABLE IF EXISTS `sct2_relationship`",
277 "sct2_statedrelationship_drop" => "DROP TABLE IF EXISTS `sct2_statedrelationship`",
278 "sct2_textdefinition_drop" => "DROP TABLE IF EXISTS `sct2_textdefinition`"
280 foreach ($array_to_truncate as $val) {
281 if (trim($val) != '') {
287 function chg_ct_external_torf1()
289 sqlStatement("UPDATE code_types SET ct_external = 2 WHERE ct_key = 'SNOMED'");
290 sqlStatement("UPDATE code_types SET ct_external = 7 WHERE ct_key = 'SNOMED-CT'");
291 sqlStatement("UPDATE code_types SET ct_external = 9 WHERE ct_key = 'SNOMED-PR'");
294 function chg_ct_external_torf2()
296 sqlStatement("UPDATE code_types SET ct_external = 10 WHERE ct_key = 'SNOMED'");
297 sqlStatement("UPDATE code_types SET ct_external = 11 WHERE ct_key = 'SNOMED-CT'");
298 sqlStatement("UPDATE code_types SET ct_external = 12 WHERE ct_key = 'SNOMED-PR'");
301 function snomedRF2_import()
305 $table_array_for_snomed = array(
306 "sct2_concept_drop" => "DROP TABLE IF EXISTS `sct2_concept`",
307 "sct2_concept_structure" => "CREATE TABLE IF NOT EXISTS `sct2_concept` (
308 `id` bigint(20) NOT NULL,
309 `effectiveTime` date NOT NULL,
310 `active` int(11) NOT NULL,
311 `moduleId` bigint(20) NOT NULL,
312 `definitionStatusId` bigint(25) NOT NULL,
315 "sct2_description_drop" => "DROP TABLE IF EXISTS `sct2_description`",
316 "sct2_description_structure" => "CREATE TABLE IF NOT EXISTS `sct2_description` (
317 `id` bigint(20) NOT NULL,
318 `effectiveTime` date NOT NULL,
319 `active` bigint(11) NOT NULL,
320 `moduleId` bigint(25) NOT NULL,
321 `conceptId` bigint(20) NOT NULL,
322 `languageCode` varchar(8) NOT NULL,
323 `typeId` bigint(25) NOT NULL,
324 `term` varchar(255) NOT NULL,
325 `caseSignificanceId` bigint(25) NOT NULL,
326 PRIMARY KEY (`id`, `active`, `conceptId`)
328 "sct2_identifier_drop" => "DROP TABLE IF EXISTS `sct2_identifier`",
329 "sct2_identifier_structure" => "CREATE TABLE IF NOT EXISTS `sct2_identifier` (
330 `identifierSchemeId` bigint(25) NOT NULL,
331 `alternateIdentifier` bigint(25) NOT NULL,
332 `effectiveTime` date NOT NULL,
333 `active` int(11) NOT NULL,
334 `moduleId` bigint(25) NOT NULL,
335 `referencedComponentId` bigint(25) NOT NULL,
336 PRIMARY KEY (`identifierSchemeId`)
338 "sct2_relationship_drop" => "DROP TABLE IF EXISTS `sct2_relationship`",
339 "sct2_relationship_structure" => "CREATE TABLE IF NOT EXISTS `sct2_relationship` (
340 `id` bigint(20) NOT NULL,
341 `effectiveTime` date NOT NULL,
342 `active` int(11) NOT NULL,
343 `moduleId` bigint(25) NOT NULL,
344 `sourceId` bigint(20) NOT NULL,
345 `destinationId` bigint(20) NOT NULL,
346 `typeId` bigint(25) NOT NULL,
347 `characteristicTypeId` bigint(25) NOT NULL,
348 `modifierId` bigint(25) NOT NULL,
351 "sct2_statedrelationship_drop" => "DROP TABLE IF EXISTS `sct2_statedrelationship`",
352 "sct2_statedrelationship_structure" => "CREATE TABLE IF NOT EXISTS `sct2_statedrelationship` (
353 `id` bigint(20) NOT NULL,
354 `effectiveTime` date NOT NULL,
355 `active` int(11) NOT NULL,
356 `moduleId` bigint(25) NOT NULL,
357 `sourceId` bigint(20) NOT NULL,
358 `destinationId` bigint(20) NOT NULL,
359 `relationshipGroup` int(11) NOT NULL,
360 `typeId` bigint(25) NOT NULL,
363 "sct2_textdefinition_drop" => "DROP TABLE IF EXISTS `sct2_textdefinition`",
364 "sct2_textdefinition_structure" => "CREATE TABLE IF NOT EXISTS `sct2_textdefinition` (
365 `id` bigint(20) NOT NULL,
366 `effectiveTime` date NOT NULL,
367 `active` int(11) NOT NULL,
368 `moduleId` bigint(25) NOT NULL,
369 `conceptId` bigint(20) NOT NULL,
370 `languageCode` varchar(8) NOT NULL,
371 `typeId` bigint(25) NOT NULL,
372 `term` varchar(655) NOT NULL,
378 $dir_snomed = $GLOBALS['temporary_files_dir'] . "/SNOMED/";
379 // $sub_path="Terminology/Content/";
380 $sub_path = "Full/Terminology/";
382 $dir = str_replace('\\', '/', $dir);
384 // executing the create statement for tables, these are defined in snomed_capture.inc file
385 // this is skipped if the US extension is being added
386 //if (!$us_extension) {
387 //var_dump($us_extension);
388 foreach ($table_array_for_snomed as $val) {
389 if (trim($val) != '') {
395 // reading the SNOMED directory and identifying the files to import and replacing the variables by originals values.
396 if (is_dir($dir) && $handle = opendir($dir)) {
397 while (false !== ($filename = readdir($handle))) {
398 if ($filename != "." && $filename != ".." && !strpos($filename, "zip")) {
399 $path = $dir . "" . $filename . "/" . $sub_path;
400 if (!(is_dir($path))) {
401 $path = $dir . "" . $filename . "/RF2Release/" . $sub_path;
403 if (is_dir($path) && $handle1 = opendir($path)) {
404 while (false !== ($filename1 = readdir($handle1))) {
405 $load_script = "Load data local infile '#FILENAME#' into table #TABLE# fields terminated by '\\t' ESCAPED BY '' lines terminated by '\\n' ignore 1 lines ";
406 $array_replace = array("#FILENAME#","#TABLE#");
407 if ($filename1 != "." && $filename1 != "..") {
408 $file_replace = $path . $filename1;
409 if (strpos($filename1, "Concept") !== false) {
410 $new_str = str_replace($array_replace, array($file_replace,"sct2_concept"), $load_script);
412 if (strpos($filename1, "Description") !== false) {
413 $new_str = str_replace($array_replace, array($file_replace,"sct2_description"), $load_script);
415 if (strpos($filename1, "Identifier") !== false) {
416 $new_str = str_replace($array_replace, array($file_replace,"sct2_identifier"), $load_script);
418 if (strpos($filename1, "Relationship") !== false) {
419 $new_str = str_replace($array_replace, array($file_replace,"sct2_relationship"), $load_script);
421 if (strpos($filename1, "StatedRelationship") !== false) {
422 $new_str = str_replace($array_replace, array($file_replace,"sct2_statedrelationship"), $load_script);
424 if (strpos($filename1, "TextDefinition") !== false) {
425 $new_str = str_replace($array_replace, array($file_replace,"sct2_textdefinition"), $load_script);
427 if ($new_str != '') {
428 sqlStatement($new_str);
441 // Function to import ICD tables $type differentiates ICD 9, 10 and eventually 11 (circa 2018 :-) etc.
443 function icd_import($type)
446 $dir_icd = $GLOBALS['temporary_files_dir'] . "/" . $type . "/";
447 $dir = str_replace('\\', '/', $dir_icd);
451 // the incoming array is a metadata array containing keys that substr match to the incoming filename
452 // followed by the field name, position and length of each fixed length text record in the incoming
453 // flat files. There are separate definitions for ICD 9 and 10 based on the type passed in
455 if ($type == 'ICD9') {
456 $incoming['SHORT_DX'] = array('#TABLENAME#' => "icd9_dx_code",
457 '#FLD1#' => "dx_code", '#POS1#' => 1, '#LEN1#' => 5,
458 '#FLD2#' => "short_desc", '#POS2#' => 7, '#LEN2#' => 60);
459 $incoming['SHORT_SG'] = array('#TABLENAME#' => "icd9_sg_code",
460 '#FLD1#' => "sg_code", '#POS1#' => 1, '#LEN1#' => 4,
461 '#FLD2#' => "short_desc", '#POS2#' => 6, '#LEN2#' => 60);
462 $incoming['LONG_SG'] = array('#TABLENAME#' => "icd9_sg_long_code",
463 '#FLD1#' => "sg_code", '#POS1#' => 1, '#LEN1#' => 4,
464 '#FLD2#' => "long_desc", '#POS2#' => 6, '#LEN2#' => 300);
465 $incoming['LONG_DX'] = array('#TABLENAME#' => "icd9_dx_long_code",
466 '#FLD1#' => "dx_code", '#POS1#' => 1, '#LEN1#' => 5,
467 '#FLD2#' => "long_desc", '#POS2#' => 7, '#LEN2#' => 300);
469 $incoming['icd10pcs_order_'] = array('#TABLENAME#' => "icd10_pcs_order_code",
470 '#FLD1#' => "pcs_code", '#POS1#' => 7, '#LEN1#' => 7,
471 '#FLD2#' => "valid_for_coding", '#POS2#' => 15, '#LEN2#' => 1,
472 '#FLD3#' => "short_desc", '#POS3#' => 17, '#LEN3#' => 60,
473 '#FLD4#' => "long_desc", '#POS4#' => 78, '#LEN4#' => 300);
474 $incoming['icd10cm_order_'] = array('#TABLENAME#' => "icd10_dx_order_code",
475 '#FLD1#' => "dx_code", '#POS1#' => 7, '#LEN1#' => 7,
476 '#FLD2#' => "valid_for_coding", '#POS2#' => 15, '#LEN2#' => 1,
477 '#FLD3#' => "short_desc", '#POS3#' => 17, '#LEN3#' => 60,
478 '#FLD4#' => "long_desc", '#POS4#' => 78, '#LEN4#' => 300);
479 $incoming['reimb_map_pr_'] = array('#TABLENAME#' => "icd10_reimbr_pcs_9_10",
480 '#FLD1#' => "code", '#POS1#' => 1, '#LEN1#' => 7,
481 '#FLD2#' => "code_cnt", '#POS2#' => 9, '#LEN2#' => 1,
482 '#FLD3#' => "ICD9_01", '#POS3#' => 11, '#LEN3#' => 5,
483 '#FLD4#' => "ICD9_02", '#POS4#' => 17, '#LEN4#' => 5,
484 '#FLD5#' => "ICD9_03", '#POS5#' => 23, '#LEN5#' => 5,
485 '#FLD6#' => "ICD9_04", '#POS6#' => 29, '#LEN6#' => 5,
486 '#FLD7#' => "ICD9_05", '#POS7#' => 35, '#LEN7#' => 5,
487 '#FLD8#' => "ICD9_06", '#POS8#' => 41, '#LEN8#' => 5);
488 $incoming['reimb_map_dx_'] = array('#TABLENAME#' => "icd10_reimbr_dx_9_10",
489 '#FLD1#' => "code", '#POS1#' => 1, '#LEN1#' => 7,
490 '#FLD2#' => "code_cnt", '#POS2#' => 9, '#LEN2#' => 1,
491 '#FLD3#' => "ICD9_01", '#POS3#' => 11, '#LEN3#' => 5,
492 '#FLD4#' => "ICD9_02", '#POS4#' => 17, '#LEN4#' => 5,
493 '#FLD5#' => "ICD9_03", '#POS5#' => 23, '#LEN5#' => 5,
494 '#FLD6#' => "ICD9_04", '#POS6#' => 29, '#LEN6#' => 5,
495 '#FLD7#' => "ICD9_05", '#POS7#' => 35, '#LEN7#' => 5,
496 '#FLD8#' => "ICD9_06", '#POS8#' => 41, '#LEN8#' => 5);
497 $incoming['I10gem'] = array('#TABLENAME#' => "icd10_gem_dx_10_9",
498 '#FLD1#' => "dx_icd10_source", '#POS1#' => 1, '#LEN1#' => 7,
499 '#FLD2#' => "dx_icd9_target", '#POS2#' => 9, '#LEN2#' => 5,
500 '#FLD3#' => "flags", '#POS3#' => 15, '#LEN3#' => 5);
501 $incoming['I9gem'] = array('#TABLENAME#' => "icd10_gem_dx_9_10",
502 '#FLD1#' => "dx_icd9_source", '#POS1#' => 1, '#LEN1#' => 5,
503 '#FLD2#' => "dx_icd10_target", '#POS2#' => 7, '#LEN2#' => 7,
504 '#FLD3#' => "flags", '#POS3#' => 15, '#LEN3#' => 5);
505 $incoming['gem_pcsi9'] = array('#TABLENAME#' => "icd10_gem_pcs_10_9",
506 '#FLD1#' => "pcs_icd10_source", '#POS1#' => 1, '#LEN1#' => 7,
507 '#FLD2#' => "pcs_icd9_target", '#POS2#' => 9, '#LEN2#' => 5,
508 '#FLD3#' => "flags", '#POS3#' => 15, '#LEN3#' => 5);
509 $incoming['gem_i9pcs'] = array('#TABLENAME#' => "icd10_gem_pcs_9_10",
510 '#FLD1#' => "pcs_icd9_source", '#POS1#' => 1, '#LEN1#' => 5,
511 '#FLD2#' => "pcs_icd10_target", '#POS2#' => 7, '#LEN2#' => 7,
512 '#FLD3#' => "flags", '#POS3#' => 15, '#LEN3#' => 5);
515 // set up the start of the load script to be appended from the incoming array defined above where incoming
517 $db_load = "LOAD DATA LOCAL INFILE '#INFILE#' INTO TABLE #TABLENAME# FIELDS TERMINATED BY '\0' (@var) SET revision = 0, ";
518 $col_template = "#FLD# = trim(Substring(@var, #POS#, #LEN#))";
520 // load all data and set active revision
521 if (is_dir($dir) && $handle = opendir($dir)) {
522 while (false !== ($filename = readdir($handle))) {
523 // bypass unwanted entries
524 if (!stripos($filename, ".txt") || stripos($filename, "diff") || stripos($filename, "addenda")) {
528 // reset the sql load command and susbtitute the filename
530 $run_sql = str_replace("#INFILE#", $dir . $filename, $run_sql);
531 $keys = array_keys($incoming);
532 while ($this_key = array_pop($keys)) {
533 if (stripos($filename, $this_key) !== false) {
534 // now substitute the tablename
535 $run_sql = str_replace("#TABLENAME#", $incoming[$this_key]['#TABLENAME#'], $run_sql);
537 // the range defines the maximum number of fields contained
538 // in any of the incoming files
539 foreach (range(1, 8) as $field) {
540 $fld = "#FLD" . $field . "#";
541 $nxtfld = "#FLD" . ($field + 1) . "#";
542 $pos = "#POS" . $field . "#";
543 $len = "#LEN" . $field . "#";
545 // concat this fields template in the sql string
546 $run_sql .= $col_template;
547 $run_sql = str_replace("#FLD#", $incoming[$this_key][$fld], $run_sql);
548 $run_sql = str_replace("#POS#", $incoming[$this_key][$pos], $run_sql);
549 $run_sql = str_replace("#LEN#", $incoming[$this_key][$len], $run_sql);
550 // at the end of this table's field list
551 if (!array_key_exists($nxtfld, $incoming[$this_key])) {
558 sqlStatement($run_sql);
560 // now update the revision for this load
561 $res = sqlStatement("SELECT max(revision) rev FROM " . escape_table_name($incoming[$this_key]['#TABLENAME#']));
562 $row = sqlFetchArray($res);
563 $next_rev = $row['rev'] + 1;
564 $run_sql = "UPDATE " . $incoming[$this_key]['#TABLENAME#'] . " SET active = 0";
566 $run_sql = "UPDATE " . $incoming[$this_key]['#TABLENAME#'] . " SET active = 1, revision = ? WHERE revision = 0";
567 sqlQuery($run_sql, array($next_rev));
575 echo htmlspecialchars(xl('ERROR: No ICD import directory.'), ENT_NOQUOTES) . "<br />";
579 // now update the tables where necessary
580 if ($type == 'ICD9') {
581 sqlStatement("update `icd9_dx_code` SET formatted_dx_code = dx_code");
582 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");
583 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");
584 sqlStatement("update `icd9_sg_code` SET formatted_sg_code = concat(concat(left(sg_code, 2), '.'), substr(sg_code, 3))");
585 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");
586 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");
588 sqlStatement("update `icd10_dx_order_code` SET formatted_dx_code = dx_code");
589 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");
595 function valueset_import($type)
597 $dir_valueset = $GLOBALS['temporary_files_dir'] . "/" . $type . "/";
598 $dir = str_replace('\\', '/', $dir_valueset);
600 // Settings to drastically speed up import with InnoDB
601 sqlStatementNoLog("SET autocommit=0");
602 sqlStatementNoLog("START TRANSACTION");
603 if (is_dir($dir) && $handle = opendir($dir)) {
604 while (false !== ($filename = readdir($handle))) {
605 if (stripos($filename, ".xml")) {
606 $abs_path = $dir . $filename;
607 $xml = simplexml_load_file($abs_path, null, null, 'ns0', true);
608 foreach ($xml->DescribedValueSet as $vset) {
609 $vset_attr = $vset->attributes();
610 $nqf = $vset->xpath('ns0:Group[@displayName="NQF Number"]/ns0:Keyword');
611 foreach ($vset->ConceptList as $cp) {
612 foreach ($nqf as $nqf_code) {
613 foreach ($cp->Concept as $con) {
614 $con_attr = $con->attributes();
615 sqlStatementNoLog("INSERT INTO valueset values(?,?,?,?,?,?,?) on DUPLICATE KEY UPDATE code_system = values(code_system),description = values(description),valueset_name = values(valueset_name)", array($nqf_code,$con_attr->code,$con_attr->codeSystem,$con_attr->codeSystemName,$vset_attr->ID,$con_attr->displayName,$vset_attr->displayName));
621 sqlStatementNoLog("UPDATE valueset set code_type='SNOMED CT' where code_type='SNOMEDCT'");
622 sqlStatementNoLog("UPDATE valueset set code_type='ICD9' where code_type='ICD9CM'");
623 sqlStatementNoLog("UPDATE valueset set code_type='ICD10' where code_type='ICD10CM'");
628 // Settings to drastically speed up import with InnoDB
629 sqlStatementNoLog("COMMIT");
630 sqlStatementNoLog("SET autocommit=1");
634 // Function to clean up temp files
635 // $type (RXNORM etc.)
636 function temp_dir_cleanup($type)
638 if (is_dir($GLOBALS['temporary_files_dir'] . "/" . $type)) {
639 rmdir_recursive($GLOBALS['temporary_files_dir'] . "/" . $type);
643 // Function to update version tracker table if successful
644 // $type (RXNORM etc.)
645 function update_tracker_table($type, $revision, $version, $file_checksum)
647 if ($type == 'RXNORM') {
648 sqlStatement("INSERT INTO `standardized_tables_track` (`imported_date`,`name`,`revision_date`, `revision_version`, `file_checksum`) VALUES (NOW(),'RXNORM',?,?,?)", array($revision, $version, $file_checksum));
650 } elseif ($type == 'SNOMED') {
651 sqlStatement("INSERT INTO `standardized_tables_track` (`imported_date`,`name`,`revision_date`, `revision_version`, `file_checksum`) VALUES (NOW(),'SNOMED',?,?,?)", array($revision, $version, $file_checksum));
653 } elseif ($type == 'ICD9') {
654 sqlStatement("INSERT INTO `standardized_tables_track` (`imported_date`,`name`,`revision_date`, `revision_version`, `file_checksum`) VALUES (NOW(),'ICD9',?,?,?)", array($revision, $version, $file_checksum));
656 } elseif ($type == 'CQM_VALUESET') {
657 sqlStatement("INSERT INTO `standardized_tables_track` (`imported_date`,`name`,`revision_date`, `revision_version`, `file_checksum`) VALUES (NOW(),'CQM_VALUESET',?,?,?)", array($revision, $version, $file_checksum));
659 } else { // $type == 'ICD10')
660 sqlStatement("INSERT INTO `standardized_tables_track` (`imported_date`,`name`,`revision_date`, `revision_version`, `file_checksum`) VALUES (NOW(),'ICD10',?,?,?)", array($revision, $version, $file_checksum));
667 // Function to delete an entire directory
668 function rmdir_recursive($dir)
670 $files = scandir($dir);
671 array_shift($files); // remove '.' from array
672 array_shift($files); // remove '..' from array
674 foreach ($files as $file) {
675 $file = $dir . '/' . $file;
677 rmdir_recursive($file);
687 // function to cleanup temp, copy and unarchive the zip file
688 function handle_zip_file($mode, $file)
690 // 1. copy the file to temp directory
691 if (!temp_copy($file, $mode)) {
692 echo htmlspecialchars(xl('ERROR: Unable to copy the file.'), ENT_NOQUOTES) . "<br />";
693 temp_dir_cleanup($mode);
696 // 2. unarchive the file
697 if (!temp_unarchive($file, $mode)) {
698 echo htmlspecialchars(xl('ERROR: Unable to extract the file.'), ENT_NOQUOTES) . "<br />";
699 temp_dir_cleanup($mode);