public function createTeamsFromCollectors()
{
$count = 0;
$countpotential = 0;
$teamcount = 0;
$leaveout = " and recordedby not like '\"' and associatedcollectors not like '\"' ";
// obtain list of possible pipe or semicolon delimited teams from recordedby and associated collectors.
$sql = "select min(year(eventdate)), max(year(eventdate)), concat(recordedby,ifnull(concat('|',associatedcollectors),'')) from omoccurrences where recordedby is not null and (associatedcollectors is not null or recordedby rlike '[;|]') and recordedby like ? {$leaveout} group by concat(recordedby,ifnull(concat('|',associatedcollectors),''))";
$letters = array('A%', 'B%', 'C%', 'D%', 'E%', 'F%', 'G%', 'H%', 'I%', 'J%', 'K%', 'L%', 'M%', 'N%', 'O%', 'P%', 'Q%', 'R%', 'S%', 'T%', 'U%', 'V%', 'W%', 'X%', 'Y%', 'Z%');
if ($statement = $this->conn->prepare($sql)) {
foreach ($letters as $letter) {
$lcount = 0;
$statement->bind_param('s', $letter);
$statement->execute();
$statement->bind_result($startyear, $endyear, $name);
while ($statement->fetch()) {
$countpotential++;
// convert any semicolon delimiters to the DarwinCore standard pipe.
// This does not handle any cases where a comma is the delimiter for a list of names.
$team = trim(html_entity_decode($name));
$team = str_replace(' & ', '|', $team);
// amperstand is a frequent separator in name lists
$team = str_replace(' and ', '|', $team);
$team = str_replace(';', '|', $team);
// remove some pathologies found in real data
$team = str_replace(" et. al.", '', $team);
$team = str_replace(" et al.", '', $team);
$team = str_replace(" et al", '', $team);
$team = preg_replace("/\\| *et al\\./", '|', $team);
$team = preg_replace("/\\| *[0-9]+(\\|\$)/", '|', $team);
// collector number
$team = str_replace("collected by", '', $team);
$team = str_replace('\\\'', "'", $team);
$team = str_replace('\\\\"', '"', $team);
// cleanup separators
$team = preg_replace("/\\| *\\|/", '|', $team);
// repeated separators
$team = preg_replace("/\\| *\$/", '', $team);
// terminal separator
$team = trim(preg_replace("/ /", ' ', $team));
// remove any double spaces and trim.
// explode into array on pipe character
$members = explode('|', $team);
$memberagentids = array();
$membercount = 0;
foreach ($members as $member) {
$member = trim($member);
if (strlen(trim($member)) > 1) {
$membercount++;
}
$pattern = '/^(' . '[A-Z][a-z]+ [A-Z][a-z]+ [A-Z][a-z]+|' . '|' . '[A-Z][a-z]+ [A-Z]\\. [A-Z][a-z]+' . '|' . '[A-Z]\\. [A-Z]\\. [A-Z][a-z]+' . ')$/';
// Initials Last
if (preg_match($pattern, $member)) {
$notes = "Generated from '{$member}' split out of team collector name '{$name}'.";
$add = $this->addFMLAgentIfNotExist($member, null, null, $notes);
$count += $add['added'];
$memberagentids[] = $add['agentid'];
}
}
// skip some pathological cases
$skip = FALSE;
if (strpos($name, 'participants') !== FALSE) {
$skip = TRUE;
}
if (strpos($name, ' Students') !== FALSE) {
$skip = TRUE;
}
if (strpos($name, ' students') !== FALSE) {
$skip = TRUE;
}
if (strpos($name, ' Class ') !== FALSE) {
$skip = TRUE;
}
if (strpos($name, ' class') !== FALSE) {
$skip = TRUE;
}
if (strpos($name, ' Bioblitz ') !== FALSE) {
$skip = TRUE;
}
if (strpos($name, ' workshop') !== FALSE) {
$skip = TRUE;
}
if (strpos($name, ' Workshop') !== FALSE) {
$skip = TRUE;
}
if (strpos($name, ' typo ') !== FALSE) {
$skip = TRUE;
}
if (strpos($name, ' should ') !== FALSE) {
$skip = TRUE;
}
if ($membercount > 1 && !$skip) {
// plausibly is a team.
$ag = new Agent();
$teamid = $ag->findIdByName($team);
if (!$teamid) {
$team = AgentManager::standardizeNameString($team);
$an = new agentnames();
$matches = $an->findAgentIdByName($team);
if (count($matches) == 0) {
$notes = "Generated from collector name '{$name}'.";
$toSave = $this->constructNewAgent('Team', '', '', '', $team, $notes);
$result .= $this->saveNewAgent($toSave);
$teamid = $toSave->getagentid();
$teamcount++;
$lcount++;
} else {
$teamid = $matches[0];
}
}
$ordinal = 1;
foreach ($memberagentids as $memberagentid) {
$at = new agentteams();
$at->setteamagentid($teamid);
$at->setmemberagentid($memberagentid);
$at->setordinal($ordinal);
$at->save();
$ordinal++;
}
}
}
echo "{$letter}(+{$lcount}) ";
flush();
}
} else {
$error .= "Error preparing query '{$sql}'. ";
}
return "<h3>Created {$count} individual records and {$teamcount} team agent records from {$countpotential} collector teams.</h3>\n<h3>{$error}</h3>\n{$result}\n";
}
