public function deleteSpecies($tid)
{
if (is_numeric($tid)) {
$sql = 'UPDATE taxa t SET t.SecurityStatus = 0 WHERE (t.tid = ' . $tid . ')';
//echo $sql;
$this->con->query($sql);
//Update specimen records
$sql2 = 'UPDATE omoccurrences o INNER JOIN taxstatus ts1 ON o.tidinterpreted = ts1.tid ' . 'INNER JOIN taxstatus ts2 ON ts1.tidaccepted = ts2.tidaccepted ' . 'INNER JOIN taxa t ON ts2.tid = t.tid ' . 'SET o.LocalitySecurity = 0 ' . 'WHERE (t.tid = ' . $tid . ')';
//echo $sql2; exit;
$this->con->query($sql2);
$occurMain = new OccurrenceMaintenance($this->conn);
$occurMain->protectGloballyRareSpecies();
}
}
public function uploadCsvList($hasHeader, $thesId)
{
set_time_limit(300);
ini_set("max_input_time", 300);
ini_set('auto_detect_line_endings', true);
$successCnt = 0;
$fh = fopen($_FILES['uploadfile']['tmp_name'], 'r') or die("Can't open file. File may be too large. Try uploading file in sections.");
$headerArr = array();
if ($hasHeader) {
$headerData = fgetcsv($fh);
foreach ($headerData as $k => $v) {
$vStr = strtolower($v);
$vStr = str_replace(array(" ", ".", "_"), "", $vStr);
$vStr = str_replace(array("scientificnamewithauthor", "scientificname", "taxa", "species", "taxon"), "sciname", $vStr);
$headerArr[$vStr] = $k;
}
} else {
$headerArr["sciname"] = 0;
}
if (array_key_exists("sciname", $headerArr)) {
$cnt = 0;
ob_flush();
flush();
$taxUtil = new TaxonomyUtilities();
while ($valueArr = fgetcsv($fh)) {
$tid = 0;
$rankId = 0;
$sciName = "";
$family = "";
$sciNameStr = $this->cleanInStr($valueArr[$headerArr["sciname"]]);
$noteStr = '';
if ($sciNameStr) {
$sciNameArr = $taxUtil->parseSciName($sciNameStr);
//Check name is in taxa table and grab tid if it is
$sql = "";
if ($thesId && is_numeric($thesId)) {
$sql = 'SELECT t2.tid, ts.family, t2.rankid ' . 'FROM (taxa t INNER JOIN taxstatus ts ON t.tid = ts.tid) ' . 'INNER JOIN taxa t2 ON ts.tidaccepted = t2.tid ' . 'WHERE (ts.taxauthid = ' . $thesId . ') ';
} else {
$sql = 'SELECT t.tid, ts.family, t.rankid ' . 'FROM taxa t INNER JOIN taxstatus ts ON t.tid = ts.tid ' . 'WHERE ts.taxauthid = 1 ';
}
$cleanSciName = $this->encodeString($sciNameArr['sciname']);
$sql .= 'AND (t.sciname IN("' . $sciNameStr . '"' . ($cleanSciName ? ',"' . $cleanSciName . '"' : '') . '))';
$rs = $this->conn->query($sql);
if ($rs) {
if ($row = $rs->fetch_object()) {
$tid = $row->tid;
$family = $row->family;
$rankId = $row->rankid;
}
$rs->free();
}
//Load taxon into checklist
if ($tid) {
if ($rankId >= 180) {
$sqlInsert = '';
$sqlValues = '';
if (array_key_exists('family', $headerArr) && ($valueArr[$headerArr['family']] && strtolower($family) != strtolower($valueArr[$headerArr['family']]))) {
$sqlInsert .= ',familyoverride';
$sqlValues .= ',"' . $this->cleanInStr($valueArr[$headerArr['family']]) . '"';
}
if (array_key_exists('habitat', $headerArr) && $valueArr[$headerArr['habitat']]) {
$sqlInsert .= ',habitat';
$sqlValues .= ',"' . $this->cleanInStr($valueArr[$headerArr['habitat']]) . '"';
}
if (array_key_exists('abundance', $headerArr) && $valueArr[$headerArr['abundance']]) {
$sqlInsert .= ',abundance';
$sqlValues .= ',"' . $this->cleanInStr($valueArr[$headerArr['abundance']]) . '"';
}
if ($noteStr || array_key_exists('notes', $headerArr) && $valueArr[$headerArr['notes']]) {
if (array_key_exists('notes', $headerArr) && $valueArr[$headerArr['notes']]) {
if ($noteStr) {
$noteStr .= '; ';
}
$noteStr .= $valueArr[$headerArr['notes']];
}
$sqlInsert .= ',notes';
$sqlValues .= ',"' . $this->cleanInStr($noteStr) . '"';
}
$sql = 'INSERT INTO fmchklsttaxalink (tid,clid' . $sqlInsert . ') VALUES (' . $tid . ', ' . $this->clid . $sqlValues . ')';
//echo $sql;
if ($this->conn->query($sql)) {
$successCnt++;
} else {
$this->errorArr[] = $sciNameStr . " (TID = {$tid}) failed to load<br />Error msg: " . $this->conn->error;
//echo $sql."<br />";
}
} else {
$this->errorArr[] = $sciNameStr . " failed to load (taxon must be of genus, species, or infraspecific ranking)";
}
} else {
$this->problemTaxa[] = $cleanSciName;
//$statusStr = $sciNameStr." failed to load (misspelled or not yet in taxonomic thesaurus)";
//$failCnt++;
}
$cnt++;
if ($cnt % 500 == 0) {
echo '<li style="margin-left:10px;">' . $cnt . ' taxa loaded</li>';
ob_flush();
flush();
}
}
}
fclose($fh);
if ($cnt && $this->clMeta['type'] == 'rarespp') {
$occurMain = new OccurrenceMaintenance($this->conn);
$occurMain->protectStateRareSpecies();
}
} else {
$this->errorStr = 'ERROR: unable to locate scientific name column';
}
return $successCnt;
}
private function cleanHouse($collList)
{
$this->logOrEcho('Updating collection statistics...', 1);
$occurMain = new OccurrenceMaintenance($this->conn);
$this->logOrEcho('General cleaning...', 2);
$collString = implode(',', $collList);
if (!$occurMain->generalOccurrenceCleaning($collString)) {
$errorArr = $occurMain->getErrorArr();
foreach ($errorArr as $errorStr) {
$this->logOrEcho($errorStr, 1);
}
}
$this->logOrEcho('Protecting sensitive species...', 2);
if (!$occurMain->protectRareSpecies()) {
$errorArr = $occurMain->getErrorArr();
foreach ($errorArr as $errorStr) {
$this->logOrEcho($errorStr, 1);
}
}
if ($collList) {
$this->logOrEcho('Updating collection statistics...', 2);
foreach ($collList as $collid) {
if (!$occurMain->updateCollectionStats($collid)) {
$errorArr = $occurMain->getErrorArr();
foreach ($errorArr as $errorStr) {
$this->logOrEcho($errorStr, 1);
}
}
}
}
$this->logOrEcho('Populating global unique identifiers (GUIDs) for all records...', 2);
$uuidManager = new UuidFactory($this->conn);
$uuidManager->setSilent(1);
$uuidManager->populateGuids();
}
private function updateCollectionStats()
{
if ($this->dbMetadata) {
//Do some more cleaning of the data after it haas been indexed in the omoccurrences table
$occurMain = new OccurrenceMaintenance($this->conn);
$this->logOrEcho('Cleaning house...');
$collString = implode(',', $this->collProcessedArr);
if (!$occurMain->generalOccurrenceCleaning($collString)) {
$errorArr = $occurMain->getErrorArr();
foreach ($errorArr as $errorStr) {
$this->logOrEcho($errorStr, 1);
}
}
$this->logOrEcho('Protecting sensitive species...');
if (!$occurMain->protectRareSpecies()) {
$errorArr = $occurMain->getErrorArr();
foreach ($errorArr as $errorStr) {
$this->logOrEcho($errorStr, 1);
}
}
$this->logOrEcho('Updating statistics...');
foreach ($this->collProcessedArr as $collid) {
if (!$occurMain->updateCollectionStats($collid)) {
$errorArr = $occurMain->getErrorArr();
foreach ($errorArr as $errorStr) {
$this->logOrEcho($errorStr, 1);
}
}
}
$this->logOrEcho('Populating global unique identifiers (GUIDs) for all records...');
$uuidManager = new UuidFactory();
$uuidManager->setSilent(1);
$uuidManager->populateGuids();
}
$this->logOrEcho("Stats update completed");
}
public function batchUpdateStatistics($collId)
{
echo 'Updating collection statistics...';
echo '<ul>';
//echo '<li>General cleaning in preparation for collecting stats... </li>';
flush();
ob_flush();
$occurMaintenance = new OccurrenceMaintenance();
//$occurMaintenance->generalOccurrenceCleaning();
$sql = 'SELECT collid, collectionname FROM omcollections WHERE collid IN(' . $collId . ') ';
//echo $sql;
$rs = $this->conn->query($sql);
while ($r = $rs->fetch_object()) {
echo '<li style="margin-left:15px;">Cleaning statistics for: ' . $r->collectionname . '</li>';
flush();
ob_flush();
$occurMaintenance->updateCollectionStats($r->collid, true);
}
$rs->free();
echo '<li>Statistics update complete!</li>';
echo '</ul>';
flush();
ob_flush();
}
protected function finalCleanup()
{
$this->outputMsg('<li>Transfer process complete</li>');
//Update uploaddate
$sql = 'UPDATE omcollectionstats SET uploaddate = CURDATE() WHERE collid = ' . $this->collId;
$this->conn->query($sql);
//Remove records from occurrence temp table (uploadspectemp)
$sql = 'DELETE FROM uploadspectemp WHERE (collid = ' . $this->collId . ') OR (initialtimestamp < DATE_SUB(CURDATE(),INTERVAL 3 DAY))';
$this->conn->query($sql);
//Optimize table to reset indexes
$this->conn->query('OPTIMIZE TABLE uploadspectemp');
//Remove records from determination temp table (uploaddetermtemp)
$sql = 'DELETE FROM uploaddetermtemp WHERE (collid = ' . $this->collId . ') OR (initialtimestamp < DATE_SUB(CURDATE(),INTERVAL 3 DAY))';
$this->conn->query($sql);
//Optimize table to reset indexes
$this->conn->query('OPTIMIZE TABLE uploaddetermtemp');
//Remove records from image temp table (uploadimagetemp)
$sql = 'DELETE FROM uploadimagetemp WHERE (collid = ' . $this->collId . ') OR (initialtimestamp < DATE_SUB(CURDATE(),INTERVAL 3 DAY))';
$this->conn->query($sql);
//Optimize table to reset indexes
$this->conn->query('OPTIMIZE TABLE uploadimagetemp');
//Do some more cleaning of the data after it haas been indexed in the omoccurrences table
$occurMain = new OccurrenceMaintenance($this->conn);
$this->outputMsg('<li>Cleaning house</li>');
ob_flush();
flush();
if (!$occurMain->generalOccurrenceCleaning($this->collId)) {
$errorArr = $occurMain->getErrorArr();
foreach ($errorArr as $errorStr) {
echo '<li style="margin-left:20px;">' . $errorStr . '</li>';
}
}
$this->outputMsg('<li style="margin-left:10px;">Protecting sensitive species...</li>');
ob_flush();
flush();
if (!$occurMain->protectRareSpecies($this->collId)) {
$errorArr = $occurMain->getErrorArr();
foreach ($errorArr as $errorStr) {
echo '<li style="margin-left:20px;">' . $errorStr . '</li>';
}
}
$this->outputMsg('<li style="margin-left:10px;">Updating statistics...</li>');
ob_flush();
flush();
if (!$occurMain->updateCollectionStats($this->collId)) {
$errorArr = $occurMain->getErrorArr();
foreach ($errorArr as $errorStr) {
echo '<li style="margin-left:20px;">' . $errorStr . '</li>';
}
}
/*
$this->outputMsg('<li style="margin-left:10px;">Searching for duplicate Catalog Numbers... ');
ob_flush();
flush();
$sql = 'SELECT catalognumber FROM omoccurrences GROUP BY catalognumber, collid '.
'HAVING Count(*)>1 AND collid = '.$this->collId.' AND catalognumber IS NOT NULL';
$rs = $this->conn->query($sql);
if($rs->num_rows){
$this->outputMsg('<span style="color:red;">Duplicate Catalog Numbers exist</span></li>');
$this->outputMsg('<li style="margin-left:10px;">');
$this->outputMsg('Open <a href="../cleaning/occurrencecleaner.php?collid='.$this->collId.'&action=listdupscatalog" target="_blank">Occurrence Cleaner</a> to resolve this issue');
$this->outputMsg('</li>');
}
else{
$this->outputMsg('All good!</li>');
}
$rs->free();
*/
$this->outputMsg('<li style="margin-left:10px;">Populating global unique identifiers (GUIDs) for all records... </li>');
ob_flush();
flush();
$uuidManager = new UuidFactory();
$uuidManager->setSilent(1);
$uuidManager->populateGuids();
if ($this->imageTransferCount) {
$this->outputMsg('<li style="margin-left:10px;color:orange">WARNING: Image thumbnails likely need to be created! Do this using the <a href="../../imagelib/admin/thumbnailbuilder.php?collid=' . $this->collId . '">Images Thumbnail Builder</a></li>');
ob_flush();
flush();
}
}
