diff options
Diffstat (limited to 'db_update_scripts/update_nutrientData.php')
| -rwxr-xr-x | db_update_scripts/update_nutrientData.php | 154 |
1 files changed, 154 insertions, 0 deletions
diff --git a/db_update_scripts/update_nutrientData.php b/db_update_scripts/update_nutrientData.php new file mode 100755 index 0000000..e4c81a7 --- /dev/null +++ b/db_update_scripts/update_nutrientData.php @@ -0,0 +1,154 @@ +#!/usr/bin/php + +<?php + +/** + * Script to update nutridb table nutrientData which corresponds + * to USDA file NUT_DATA, which corresponds to update files + * DEL_NUTR.txt and ADD_NUTR.txt and CHG_NUTR.txt. + * + * NOTE: This script relies on the fact that updates files + * are named as they were from the SR19 -> SR20 update. + */ + +require("config.php"); + +$fh_del = fopen("{$updatesDir}/DEL_NUTR.txt", "r"); +$fh_add = fopen("{$updatesDir}/ADD_NUTR.txt", "r"); +$fh_chg = fopen("{$updatesDir}/CHG_NUTR.txt", "r"); + +# First handle deletions +fwrite($fh_log, "### PROCESSING DEL_NUTR.txt ###\n"); +$idx = 0; +while ( ($row = fgetcsv($fh_del, 0, $delimiter, $enclosure)) !== FALSE ) { + $sql = sprintf(" + UPDATE nutrientData + SET usda_status = 'deleted' + WHERE ndb_no = '%s' + AND nutr_no = '%s' + ", + $row[0], + $row[1] + ); + $db->Modify($sql); + if ( $db->_error ) { + fwrite($fh_log, "\tERROR: deactivating ndb_no {$row[0]}: $db->_error\n"); + } elseif ( $db->_affectedRows == 0 ) { + fwrite($fh_log, "\tWARNING: ndb_no {$row[0]} was not found in the database.\n"); + } else { + $idx++; + } +} +fwrite($fh_log, "\tDeactivated $idx records from table nutrientData.\n"); + +# Now handle additions +fwrite($fh_log, "### PROCESSING ADD_NUTR.txt ###\n"); +$idx = 0; +while ( ($row = fgetcsv($fh_add, 0, $delimiter, $enclosure)) !== FALSE ) { + # Don't add the record if this ndb_no already exists + $sql = sprintf(" + SELECT ndb_no FROM nutrientData + WHERE ndb_no = '%s' + AND nutr_no = '%s' + ", + $row[0], + $row[1] + ); + $db->Select($sql); + if ( $db->_rowCount != 0 ) { + fwrite($fh_log, "\tWARNING not adding ndb_no {$row[0]} because it already exists.\n"); + continue; + } + + $sql = sprintf(" + INSERT INTO nutrientData( + ndb_no, nutr_no, nutr_val, num_data_pts, std_error, src_cd, deriv_cd, ref_ndb_no, + add_nutr_mark, num_studies, min, max, df, low_eb, up_eb, stat_cmt, cc + ) + VALUES('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s') + ", + $row[0], + $row[1], + $row[2], + $row[3], + $row[4], + $row[5], + $row[6], + $row[7], + $row[8], + $row[9], + $row[10], + $row[11], + $row[12], + $row[13], + $row[14], + addslashes($row[15]), + $row[16] + ); + $db->Modify($sql); + if ( $db->_error ) { + fwrite($fh_log, "\tERROR adding ndb_no {$row[0]}: $db->_error\n"); + } else { + $idx++; + } +} +fwrite($fh_log, "\tAdded $idx records to table nutrientData.\n"); + +# Now make updates +fwrite($fh_log, "### PROCESSING CHG_NUTR.txt ###\n"); +$idx = 0; +while ( ($row = fgetcsv($fh_chg, 0, $delimiter, $enclosure)) !== FALSE ) { + $sql = sprintf(" + UPDATE nutrientData set + ndb_no = '%s', + nutr_no = '%s', + nutr_val = '%s', + num_data_pts = '%s', + std_error = '%s', + src_cd = '%s', + deriv_cd = '%s', + ref_ndb_no = '%s', + add_nutr_mark = '%s', + num_studies = '%s', + min = '%s', + max = '%s', + df = '%s', + low_eb = '%s', + up_eb = '%s', + stat_cmt = '%s', + cc = '%s' + WHERE ndb_no = '%s' + and nutr_no = '%s' + ", + $row[0], + $row[1], + $row[2], + $row[3], + $row[4], + $row[5], + $row[6], + $row[7], + $row[8], + $row[9], + $row[10], + $row[11], + $row[12], + $row[13], + $row[14], + addslashes($row[15]), + $row[16], + $row[0], + $row[1] + ); + $db->Modify($sql); + if ( $db->_error ) { + fwrite($fh_log, "\tERROR modifying ndb_no {$row[0]}: $db->_error\n"); + } elseif ( $db->_affectedRows != 1 ) { + fwrite($fh_log, "\tWARNING: nothing modified for ndb_no {$row[0]}.\n"); + } else { + $idx++; + } +} +fwrite($fh_log, "\tUpdated $idx records in table nutrientData.\n"); + +?> |