1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
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");
?>
|
