-
Notifications
You must be signed in to change notification settings - Fork 4
/
fetch_taxon_properties.php
173 lines (153 loc) · 7.16 KB
/
fetch_taxon_properties.php
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
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
<?php
/*
* Using a matched name via autocomplete (could be a scientific name, common name, synonym, etc), find and return either
* A) the target fossiltaxa record already assigned for this calibration
* B) another existing fossiltaxa record in the database?
* C) taxon names (partial result) for a matching NCBI or FCD node
* NOTE that there's more here than I expected. NCBI_names of class 'authority' have this information, albeit cluttered with the taxon name.
SELECT * FROM NCBI_names WHERE taxonid = (SELECT taxonid FROM NCBI_names WHERE uniquename LIKE 'Felis domesticus' OR name LIKE 'Felis domesticus');
*/
// open and load site variables
require('../config.php');
// Quick test for non-empty string
function isNullOrEmptyString($str){
return (!isset($str) || ($str == null) || trim($str)==='');
}
// prepare an associative array for fossiltaxa values
$taxon_properties = array(
'fossiltaxaID' => '',
'properName' => '',
'commonName' => '',
'author' => '',
'pbdbTaxonNumber' => '',
'TOTAL_MATCHING_TAXA' => '',
'SOURCE_TABLE' => '',
'AUTHOR_SOURCE_TABLE' => '',
'ERROR' => '?'
);
// check for a valid (non-empty) query
if (!isset($_GET["autocomplete_match"])) {
echo "{'ERROR': 'autocomplete_match not submitted!'}";
return;
}
$q = strtolower($_GET["autocomplete_match"]);
if (!$q || trim($q) == "") {
// if string is empty, don't bother checking; just return no matches
$taxonProperties['ERROR'] = "empty match term!";
echo json_encode($taxon_properties);
return;
}
// connect to mySQL server and select the Fossil Calibration database
$connection=mysql_connect($SITEINFO['servername'],$SITEINFO['UserName'], $SITEINFO['password']) or die ('Unable to connect!');
mysql_select_db('FossilCalibration') or die ('Unable to select database!');
// check existing fossiltaxa and matching NCBI/FCD nodes, and return the best match
// TODO: filter results for admin/reviewer vs. visitor?
$bestMatchFound = false;
$authorshipFound = false;
$CalibrationID = $_GET['calibration_ID'];
$taxonProperties['ERROR'] = "empty match term!";
/* look for a fossiltaxa record already assigned to this calibration?
if (is_numeric($CalibrationID) && $CalibrationID > 0) {
$query="SELECT fossiltaxa.* FROM fossiltaxa
INNER JOIN fossils ON fossils.Species = fossiltaxa.TaxonName
LEFT OUTER JOIN Link_CalibrationFossil AS link ON link.FossilID = fossils.FossilID
INNER JOIN calibrations ON calibrations.CalibrationID = link.CalibrationID AND calibrations.CalibrationID = '". mysql_real_escape_string($CalibrationID) ."'";
}
*/
// look for an existing fossiltaxa record matching this name
$query="SELECT * FROM fossiltaxa
WHERE TaxonName LIKE '". mysql_real_escape_string($q) ."' OR CommonName LIKE '". mysql_real_escape_string($q) ."'";
$result=mysql_query($query) or die ('Error in query: '.$query.'|'. mysql_error());
if (mysql_num_rows($result) > 0) {
$row = mysql_fetch_assoc($result);
$bestMatchFound = true;
$taxon_properties['fossiltaxaID'] = $row['TaxonID'];
$taxon_properties['properName'] = $row['TaxonName'];
$taxon_properties['commonName'] = $row['CommonName'];
$taxon_properties['author'] = $row['TaxonAuthor'];
$taxon_properties['pbdbTaxonNumber'] = $row['PBDBTaxonNum'];
$taxon_properties['TOTAL_MATCHING_TAXA'] = mysql_num_rows($result);
$taxon_properties['SOURCE_TABLE'] = "fossiltaxa";
$taxon_properties['AUTHOR_SOURCE_TABLE'] = "fossiltaxa";
}
if (!isNullOrEmptyString($taxon_properties['author'])) {
$authorshipFound = true;
}
if (!$bestMatchFound || !$authorshipFound) {
/* fall back to any 'taxa' record that matches fossil's species name (worth a try, if only for fossil species and authorship info)
$query="SELECT taxa.* FROM taxa
INNER JOIN fossils ON fossils.Species = taxa.TaxonName
LEFT OUTER JOIN Link_CalibrationFossil AS link ON link.FossilID = fossils.FossilID
INNER JOIN calibrations ON calibrations.CalibrationID = link.CalibrationID AND calibrations.CalibrationID = '". mysql_real_escape_string($CalibrationID) ."'";
*/
// look for an existing taxa record matching this name
$query="SELECT * FROM taxa
WHERE TaxonName LIKE '". mysql_real_escape_string($q) ."' OR CommonName LIKE '". mysql_real_escape_string($q) ."'";
$result=mysql_query($query) or die ('Error in query: '.$query.'|'. mysql_error());
if (mysql_num_rows($result) > 0) {
$row = mysql_fetch_assoc($result);
// update authorship, in any case
$taxon_properties['author'] = $row['TaxonAuthor'];
$taxon_properties['AUTHOR_SOURCE_TABLE'] = "taxa";
if (!$bestMatchFound) {
$taxon_properties['fossiltaxaID'] = 'ADD TO FOSSILTAXA';
$taxon_properties['properName'] = $row['TaxonName'];
$taxon_properties['commonName'] = $row['CommonName'];
$taxon_properties['pbdbTaxonNumber'] = ''; // this is not included in table 'taxa'
$taxon_properties['TOTAL_MATCHING_TAXA'] = mysql_num_rows($result);
$taxon_properties['SOURCE_TABLE'] = "taxa";
$bestMatchFound = true;
}
if (!isNullOrEmptyString($taxon_properties['author'])) {
$authorshipFound = true;
}
}
}
if (!$bestMatchFound || !$authorshipFound) {
// pull all NCBI names for the node whose name is an exact match
// (NOTE that while the NCBI_names table does include authorship info, it's only for about 15% of taxa)
$query="SELECT * FROM NCBI_names
WHERE taxonid = (SELECT taxonid FROM NCBI_names WHERE uniquename LIKE '". mysql_real_escape_string($q) ."' OR name LIKE '". mysql_real_escape_string($q) ."')";
$result=mysql_query($query) or die ('Error in query: '.$query.'|'. mysql_error());
if (mysql_num_rows($result) > 0) {
// walk the matching names, sorting them by type (class), eg, 'synonym', 'authorship', 'common name'
$scientificName = "";
$commonName = "";
$genbankCommonName = ""; // use 'genbank common name' if found
$author = "";
while ($matchingName = mysql_fetch_assoc($result)) {
$nameType = $matchingName['class'];
switch( $nameType ) {
case 'scientific name':
$scientificName = $matchingName['name']; // OR uniquename??
break;
case 'common name':
// NOTE that there might be several of these; just send the last one found
$commonName = $matchingName['name']; // OR uniquename??
break;
case 'genbank common name':
// this will trump other common names
$genbankCommonName = $matchingName['name']; // OR uniquename??
break;
case 'authority':
// this includes the taxon name, but send it as-is for now
$author = $matchingName['name']; // OR uniquename??
break;
}
}
// update authorship, in any case
$taxon_properties['author'] = $author;
$taxon_properties['AUTHOR_SOURCE_TABLE'] = "NCBI_names";
if (!$bestMatchFound) {
$taxon_properties['fossiltaxaID'] = 'ADD TO FOSSILTAXA';
$taxon_properties['properName'] = $scientificName;
$taxon_properties['commonName'] = !isNullOrEmptyString($genbankCommonName) ? $genbankCommonName : $commonName;
$taxon_properties['pbdbTaxonNumber'] = ""; // sorry, not available here
$taxon_properties['TOTAL_MATCHING_TAXA'] = "NOT DETERMINED (". mysql_num_rows($result) ." matching NCBI names found)";
$taxon_properties['SOURCE_TABLE'] = "NCBI_names";
}
}
}
// return our best guess for all fields, or empty values (to clear the UI) if no match was found
echo json_encode($taxon_properties);
?>