/**
* extract all places from the given record and insert them into the places table
*
* @param string $gid
* @param int $ged_id
* @param string $gedrec
*/
public static function updatePlaces($gid, $ged_id, $gedrec)
{
global $placecache;
if (!isset($placecache)) {
$placecache = array();
}
$personplace = array();
// import all place locations, but not control info such as
// 0 HEAD/1 PLAC or 0 _EVDEF/1 PLAC
$pt = preg_match_all("/^[2-9] PLAC (.+)/m", $gedrec, $match, PREG_SET_ORDER);
for ($i = 0; $i < $pt; $i++) {
$place = trim($match[$i][1]);
$lowplace = I18N::strtolower($place);
//-- if we have already visited this place for this person then we don't need to again
if (isset($personplace[$lowplace])) {
continue;
}
$personplace[$lowplace] = 1;
$places = explode(',', $place);
//-- reverse the array to start at the highest level
$secalp = array_reverse($places);
$parent_id = 0;
$search = true;
foreach ($secalp as $place) {
$place = trim($place);
$key = strtolower(mb_substr($place, 0, 150) . "_" . $parent_id);
//-- if this place has already been added then we don't need to add it again
if (isset($placecache[$key])) {
$parent_id = $placecache[$key];
if (!isset($personplace[$key])) {
$personplace[$key] = 1;
// Use INSERT IGNORE as a (temporary) fix for https://bugs.launchpad.net/webtrees/+bug/582226
// It ignores places that utf8_unicode_ci consider to be the same (i.e. accents).
// For example Québec and Quebec
// We need a better solution that attaches multiple names to single places
Database::prepare("INSERT IGNORE INTO `##placelinks` (pl_p_id, pl_gid, pl_file) VALUES (?, ?, ?)")->execute(array($parent_id, $gid, $ged_id));
}
continue;
}
//-- only search the database while we are finding places in it
if ($search) {
//-- check if this place and level has already been added
$tmp = Database::prepare("SELECT p_id FROM `##places` WHERE p_file = ? AND p_parent_id = ? AND p_place = LEFT(?, 150)")->execute(array($ged_id, $parent_id, $place))->fetchOne();
if ($tmp) {
$p_id = $tmp;
} else {
$search = false;
}
}
//-- if we are not searching then we have to insert the place into the db
if (!$search) {
$std_soundex = Soundex::russell($place);
$dm_soundex = Soundex::daitchMokotoff($place);
Database::prepare("INSERT INTO `##places` (p_place, p_parent_id, p_file, p_std_soundex, p_dm_soundex) VALUES (LEFT(?, 150), ?, ?, ?, ?)")->execute(array($place, $parent_id, $ged_id, $std_soundex, $dm_soundex));
$p_id = Database::getInstance()->lastInsertId();
}
Database::prepare("INSERT IGNORE INTO `##placelinks` (pl_p_id, pl_gid, pl_file) VALUES (?, ?, ?)")->execute(array($p_id, $gid, $ged_id));
//-- increment the level and assign the parent id for the next place level
$parent_id = $p_id;
$placecache[$key] = $p_id;
$personplace[$key] = 1;
}
}
}
/**
* Search the names of the husb/wife in a family
*
* @param string[] $query Search terms
* @param Tree[] $trees The trees to search
*
* @return Family[]
*/
public static function searchFamilyNames(array $query, array $trees)
{
// No query => no results
if (!$query) {
return array();
}
$sql = "SELECT DISTINCT f_id AS xref, f_file AS gedcom_id, f_gedcom AS gedcom" . " FROM `##families`" . " LEFT JOIN `##name` husb ON f_husb = husb.n_id AND f_file = husb.n_file" . " LEFT JOIN `##name` wife ON f_wife = wife.n_id AND f_file = wife.n_file" . " WHERE 1";
$args = array();
foreach ($query as $n => $q) {
$sql .= " AND (husb.n_full COLLATE :husb_collate_" . $n . " LIKE CONCAT('%', :husb_query_" . $n . ", '%') OR wife.n_full COLLATE :wife_collate_" . $n . " LIKE CONCAT('%', :wife_query_" . $n . ", '%'))";
$args['husb_collate_' . $n] = I18N::collation();
$args['husb_query_' . $n] = Filter::escapeLike($q);
$args['wife_collate_' . $n] = I18N::collation();
$args['wife_query_' . $n] = Filter::escapeLike($q);
}
$sql .= " AND f_file IN (";
foreach ($trees as $n => $tree) {
$sql .= $n ? ", " : "";
$sql .= ":tree_id_" . $n;
$args['tree_id_' . $n] = $tree->getTreeId();
}
$sql .= ")";
$list = array();
$rows = Database::prepare($sql)->execute($args)->fetchAll();
foreach ($rows as $row) {
$list[] = Family::getInstance($row->xref, Tree::findById($row->gedcom_id), $row->gedcom);
}
$list = array_filter($list, function (Family $x) use($query) {
$name = I18N::strtolower(strip_tags($x->getFullName()));
foreach ($query as $q) {
if (stripos($name, I18N::strtolower($q)) === false) {
return false;
}
}
return true;
});
return $list;
}
