public static function update_taxon_concept_names($taxon_concept_ids) { if (!$taxon_concept_ids) { return false; } if (is_numeric($taxon_concept_ids)) { $taxon_concept_ids = array($taxon_concept_ids); } $mysqli =& $GLOBALS['db_connection']; $started_new_transaction = false; if (!$mysqli->in_transaction()) { $mysqli->begin_transaction(); $started_new_transaction = true; } $batches = array_chunk($taxon_concept_ids, 500); foreach ($batches as $batch_ids) { usleep(500000); $name_ids = array(); $matching_ids = array(); $query = "\n (SELECT he.taxon_concept_id, he.id, he.name_id, 'preferred' as type FROM hierarchy_entries he WHERE taxon_concept_id IN (" . implode(",", $batch_ids) . ") AND ((he.published=1 AND he.visibility_id=" . Visibility::visible()->id . ") OR (he.published=0 AND he.visibility_id=" . Visibility::preview()->id . ")))\n UNION\n (SELECT he.taxon_concept_id, s.hierarchy_entry_id, s.name_id, 'synonym' as type\n FROM hierarchy_entries he\n JOIN synonyms s ON (he.id=s.hierarchy_entry_id)\n WHERE he.taxon_concept_id IN (" . implode(",", $batch_ids) . ")\n AND s.language_id=0\n AND s.synonym_relation_id!=" . SynonymRelation::find_or_create_by_translated_label('genbank common name')->id . "\n AND s.synonym_relation_id!=" . SynonymRelation::find_or_create_by_translated_label('common name')->id . "\n AND s.synonym_relation_id!=" . SynonymRelation::find_or_create_by_translated_label('blast name')->id . "\n AND s.synonym_relation_id!=" . SynonymRelation::find_or_create_by_translated_label('genbank acronym')->id . "\n AND s.synonym_relation_id!=" . SynonymRelation::find_or_create_by_translated_label('acronym')->id . "\n AND ((he.published=1 AND he.visibility_id=" . Visibility::visible()->id . ") OR (he.published=0 AND he.visibility_id=" . Visibility::preview()->id . ")))"; foreach ($mysqli->iterate_file($query) as $row_num => $row) { $taxon_concept_id = $row[0]; $hierarchy_entry_id = $row[1]; $name_id = $row[2]; $name_type = $row[3]; $name_ids[$name_id][$taxon_concept_id] = 1; $matching_ids[$taxon_concept_id][$name_id][$hierarchy_entry_id] = $name_type; } if ($name_ids) { //This makes sure we have a scientific name, gets the canonicalFormID $query = "SELECT n.id, n_match.id FROM names n JOIN canonical_forms cf ON (n.canonical_form_id=cf.id) JOIN names n_match ON (cf.id=n_match.canonical_form_id) WHERE n.id IN (" . implode(",", array_keys($name_ids)) . ") AND n_match.string=cf.string"; foreach ($mysqli->iterate_file($query) as $row_num => $row) { $original_name_id = $row[0]; $canonical_name_id = $row[1]; if ($original_name_id != $canonical_name_id) { foreach ($name_ids[$original_name_id] as $taxon_concept_id => $junk) { $matching_ids[$taxon_concept_id][$canonical_name_id][0] = 1; } } } } $common_names = array(); $preferred_in_language = array(); $query = "SELECT he.taxon_concept_id, he.published, he.visibility_id, s.id, s.hierarchy_id, s.hierarchy_entry_id, s.name_id, s.language_id, s.preferred, s.vetted_id FROM hierarchy_entries he JOIN synonyms s ON (he.id=s.hierarchy_entry_id) JOIN vetted v ON (s.vetted_id=v.id) WHERE he.taxon_concept_id IN (" . implode(",", $batch_ids) . ") AND s.language_id!=0 AND (s.synonym_relation_id=" . SynonymRelation::genbank_common_name()->id . " OR s.synonym_relation_id=" . SynonymRelation::common_name()->id . ") ORDER BY s.language_id, (s.hierarchy_id=" . Hierarchy::contributors()->id . ") DESC, v.view_order ASC, s.preferred DESC, s.id DESC"; foreach ($mysqli->iterate_file($query) as $row_num => $row) { $taxon_concept_id = $row[0]; $published = $row[1]; $visibility_id = $row[2]; $synonym_id = $row[3]; $hierarchy_id = $row[4]; $hierarchy_entry_id = $row[5]; $name_id = $row[6]; $language_id = $row[7]; $preferred = $row[8]; $vetted_id = $row[9]; // skipping Wikipedia common names entirely if ($hierarchy_id == @Hierarchy::wikipedia()->id) { continue; } $curator_name = $hierarchy_id == @Hierarchy::contributors()->id; $ubio_name = $hierarchy_id == @Hierarchy::ubio()->id; if ($curator_name || $ubio_name || $curator_name || $published == 1 && $visibility_id == Visibility::visible()->id) { if (isset($preferred_in_language[$taxon_concept_id][$language_id])) { $preferred = 0; } if ($preferred && $curator_name && ($vetted_id == Vetted::trusted()->id || $vetted_id == Vetted::unknown()->id)) { $preferred_in_language[$taxon_concept_id][$language_id] = 1; } else { $preferred = 0; } if (!isset($common_names[$taxon_concept_id])) { $common_names[$taxon_concept_id] = array(); } $common_names[$taxon_concept_id][] = array('synonym_id' => $synonym_id, 'language_id' => $language_id, 'name_id' => $name_id, 'hierarchy_entry_id' => $hierarchy_entry_id, 'preferred' => $preferred, 'vetted_id' => $vetted_id, 'is_curator_name' => $curator_name); } } // if there was no preferred name foreach ($common_names as $taxon_concept_id => $arr) { foreach ($arr as $key => $arr2) { if (@(!$preferred_in_language[$taxon_concept_id][$arr2['language_id']]) && ($arr2['vetted_id'] == Vetted::trusted()->id || $arr2['vetted_id'] == Vetted::unknown()->id)) { $common_names[$taxon_concept_id][$key]['preferred'] = 1; $preferred_in_language[$taxon_concept_id][$arr2['language_id']] = 1; } } } $mysqli->delete("DELETE FROM taxon_concept_names WHERE taxon_concept_id IN (" . implode(",", $batch_ids) . ")"); $tmp_file_path = temp_filepath(); if (!($LOAD_DATA_TEMP = fopen($tmp_file_path, "w+"))) { debug(__CLASS__ . ":" . __LINE__ . ": Couldn't open file: " . $tmp_file_path); return; } /* Insert the scientific names */ foreach ($matching_ids as $taxon_concept_id => $arr) { foreach ($arr as $name_id => $arr2) { foreach ($arr2 as $hierarchy_entry_id => $type) { $preferred = 0; if ($hierarchy_entry_id && $type == "preferred") { $preferred = 1; } fwrite($LOAD_DATA_TEMP, "{$taxon_concept_id}\t{$name_id}\t{$hierarchy_entry_id}\t0\t0\t{$preferred}\n"); } } } $mysqli->load_data_infile($tmp_file_path, 'taxon_concept_names'); unlink($tmp_file_path); $tmp_file_path = temp_filepath(); if (!($LOAD_DATA_TEMP = fopen($tmp_file_path, "w+"))) { debug(__CLASS__ . ":" . __LINE__ . ": Couldn't open file: " . $tmp_file_path); return; } /* Insert the common names */ foreach ($common_names as $taxon_concept_id => $arr) { foreach ($arr as $key => $arr2) { $synonym_id = $arr2['synonym_id']; $language_id = $arr2['language_id']; $name_id = $arr2['name_id']; $hierarchy_entry_id = $arr2['hierarchy_entry_id']; $preferred = $arr2['preferred']; $vetted_id = $arr2['vetted_id']; fwrite($LOAD_DATA_TEMP, "{$taxon_concept_id}\t{$name_id}\t{$hierarchy_entry_id}\t{$language_id}\t1\t{$preferred}\t{$synonym_id}\t{$vetted_id}\n"); } } $mysqli->load_data_infile($tmp_file_path, 'taxon_concept_names'); unlink($tmp_file_path); unset($matching_ids); unset($common_names); unset($name_ids); unset($preferred_in_language); $mysqli->commit(); } if ($started_new_transaction) { $mysqli->end_transaction(); } }