/** * 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; }