/** * Generate a recursive list of descendants of an individual. * If parents are specified, we can also show the pedigree (adopted, etc.). * * @param Individual $individual * @param Family|null $parents * * @return string */ private function getDescendantsHtml(Individual $individual, Family $parents = null) { // A person has many names. Select the one that matches the searched surname $person_name = ''; foreach ($individual->getAllNames() as $name) { list($surn1) = explode(",", $name['sort']); if (stripos($surn1, $this->surname) !== false || stripos($this->surname, $surn1) !== false || $this->soundex_std && Soundex::compare(Soundex::russell($surn1), Soundex::russell($this->surname)) || $this->soundex_dm && Soundex::compare(Soundex::daitchMokotoff($surn1), Soundex::daitchMokotoff($this->surname))) { $person_name = $name['full']; break; } } // No matching name? Typically children with a different surname. The branch stops here. if (!$person_name) { return '<li title="' . strip_tags($individual->getFullName()) . '">' . $individual->getSexImage() . '…</li>'; } // Is this individual one of our ancestors? $sosa = array_search($individual, $this->ancestors, true); if ($sosa) { $sosa_class = 'search_hit'; $sosa_html = ' <a class="details1 ' . $individual->getBoxStyle() . '" title="' . I18N::translate('Sosa') . '" href="relationship.php?pid2=' . $this->ancestors[1]->getXref() . '&pid1=' . $individual->getXref() . '">' . $sosa . '</a>' . self::sosaGeneration($sosa); } else { $sosa_class = ''; $sosa_html = ''; } // Generate HTML for this individual, and all their descendants $indi_html = $individual->getSexImage() . '<a class="' . $sosa_class . '" href="' . $individual->getHtmlUrl() . '">' . $person_name . '</a> ' . $individual->getLifeSpan() . $sosa_html; // If this is not a birth pedigree (e.g. an adoption), highlight it if ($parents) { $pedi = ''; foreach ($individual->getFacts('FAMC') as $fact) { if ($fact->getTarget() === $parents) { $pedi = $fact->getAttribute('PEDI'); break; } } if ($pedi && $pedi != 'birth') { $indi_html = '<span class="red">' . GedcomCodePedi::getValue($pedi, $individual) . '</span> ' . $indi_html; } } // spouses and children $spouse_families = $individual->getSpouseFamilies(); if ($spouse_families) { usort($spouse_families, '\\Fisharebest\\Webtrees\\Family::compareMarrDate'); $fam_html = ''; foreach ($spouse_families as $family) { $fam_html .= $indi_html; // Repeat the individual details for each spouse. $spouse = $family->getSpouse($individual); if ($spouse) { $sosa = array_search($spouse, $this->ancestors, true); if ($sosa) { $sosa_class = 'search_hit'; $sosa_html = ' <a class="details1 ' . $spouse->getBoxStyle() . '" title="' . I18N::translate('Sosa') . '" href="relationship.php?pid2=' . $this->ancestors[1]->getXref() . '&pid1=' . $spouse->getXref() . '"> ' . $sosa . ' </a>' . self::sosaGeneration($sosa); } else { $sosa_class = ''; $sosa_html = ''; } $marriage_year = $family->getMarriageYear(); if ($marriage_year) { $fam_html .= ' <a href="' . $family->getHtmlUrl() . '" title="' . strip_tags($family->getMarriageDate()->display()) . '"><i class="icon-rings"></i>' . $marriage_year . '</a>'; } elseif ($family->getFirstFact('MARR')) { $fam_html .= ' <a href="' . $family->getHtmlUrl() . '" title="' . GedcomTag::getLabel('MARR') . '"><i class="icon-rings"></i></a>'; } elseif ($family->getFirstFact('_NMR')) { $fam_html .= ' <a href="' . $family->getHtmlUrl() . '" title="' . GedcomTag::getLabel('_NMR') . '"><i class="icon-rings"></i></a>'; } $fam_html .= ' ' . $spouse->getSexImage() . '<a class="' . $sosa_class . '" href="' . $spouse->getHtmlUrl() . '">' . $spouse->getFullName() . '</a> ' . $spouse->getLifeSpan() . ' ' . $sosa_html; } $fam_html .= '<ol>'; foreach ($family->getChildren() as $child) { $fam_html .= $this->getDescendantsHtml($child, $family); } $fam_html .= '</ol>'; } return '<li>' . $fam_html . '</li>'; } else { // No spouses - just show the individual return '<li>' . $indi_html . '</li>'; } }
/** * Get Indis from surname input - see: WT\Controller\Branches.php - loadIndividuals * * @param type $surname * @param type $russell * @param type $daitchMokotoff * @return type */ protected function indisArray($surname, $russell, $daitchMokotoff) { $sql = "SELECT DISTINCT i_id AS xref, i_file AS tree_id, i_gedcom AS gedcom" . " FROM `##individuals`" . " JOIN `##name` ON (i_id = n_id AND i_file = n_file)" . " WHERE n_file = :tree_id" . " AND n_type != '_MARNM'" . " AND (n_surn = :surname1 OR n_surname = :surname2"; $args = array('tree_id' => $this->tree_id, 'surname1' => $surname, 'surname2' => $surname); if ($russell) { // works only with latin letters. For other letters it outputs the code '0000'. foreach (explode(':', Soundex::russell($surname)) as $value) { if ($value != '0000') { $sql .= " OR n_soundex_surn_std LIKE CONCAT('%', '" . $value . "', '%')"; } } } if ($daitchMokotoff) { // works only with predefined letters and lettercombinations. Fot other letters it outputs the code '000000'. foreach (explode(':', Soundex::daitchMokotoff($surname)) as $value) { if ($value != '000000') { $sql .= " OR n_soundex_surn_dm LIKE CONCAT('%', '" . $value . "', '%')"; } } } $sql .= ')'; $rows = Database::prepare($sql)->execute($args)->fetchAll(); $data = array(); foreach ($rows as $row) { $tree = Tree::findById($row->tree_id); $data[] = Individual::getInstance($row->xref, $tree, $row->gedcom); } return $data; }
/** * Convert custom markup into HTML * * @param Note $note * * @return string */ public static function formatCensusNote(Note $note) { global $WT_TREE; if (preg_match('/(.*)((?:\\n.*)*)\\n\\.start_formatted_area\\.\\n(.+)\\n(.+(?:\\n.+)*)\\n.end_formatted_area\\.((?:\\n.*)*)/', $note->getNote(), $match)) { // This looks like a census-assistant shared note $title = Filter::escapeHtml($match[1]); $preamble = Filter::escapeHtml($match[2]); $header = Filter::escapeHtml($match[3]); $data = Filter::escapeHtml($match[4]); $postamble = Filter::escapeHtml($match[5]); // Get the column headers for the census to which this note refers // requires the fact place & date to match the specific census // censusPlace() (Soundex match) and censusDate() functions $fmt_headers = array(); $linkedRecords = array_merge($note->linkedIndividuals('NOTE'), $note->linkedFamilies('NOTE')); $firstRecord = array_shift($linkedRecords); if ($firstRecord) { $countryCode = ''; $date = ''; foreach ($firstRecord->getFacts('CENS') as $fact) { if (trim($fact->getAttribute('NOTE'), '@') === $note->getXref()) { $date = $fact->getAttribute('DATE'); $place = explode(',', strip_tags($fact->getPlace()->getFullName())); $countryCode = Soundex::daitchMokotoff(array_pop($place)); break; } } foreach (Census::allCensusPlaces() as $censusPlace) { if (Soundex::compare($countryCode, Soundex::daitchMokotoff($censusPlace->censusPlace()))) { foreach ($censusPlace->allCensusDates() as $census) { if ($census->censusDate() == $date) { foreach ($census->columns() as $column) { $abbrev = $column->abbreviation(); if ($abbrev) { $description = $column->title() ? $column->title() : I18N::translate('Description unavailable'); $fmt_headers[$abbrev] = '<span title="' . $description . '">' . $abbrev . '</span>'; } } break 2; } } } } } // Substitute header labels and format as HTML $thead = '<tr><th>' . strtr(str_replace('|', '</th><th>', $header), $fmt_headers) . '</th></tr>'; $thead = str_replace('.b.', '', $thead); // Format data as HTML $tbody = ''; foreach (explode("\n", $data) as $row) { $tbody .= '<tr>'; foreach (explode('|', $row) as $column) { $tbody .= '<td>' . $column . '</td>'; } $tbody .= '</tr>'; } return $title . "\n" . '<div class="markdown">' . '<p>' . $preamble . '</p>' . '<table>' . '<thead>' . $thead . '</thead>' . '<tbody>' . $tbody . '</tbody>' . '</table>' . '<p>' . $postamble . '</p>' . '</div>'; } else { // Not a census-assistant shared note - apply default formatting return Filter::formatText($note->getNote(), $WT_TREE); } }
/** * Perform the search */ private function advancedSearch() { global $WT_TREE; $this->myindilist = array(); $fct = count($this->fields); if (!array_filter($this->values)) { return; } // Dynamic SQL query, plus bind variables $sql = 'SELECT DISTINCT ind.i_id AS xref, ind.i_gedcom AS gedcom FROM `##individuals` ind'; $bind = array(); // Join the following tables $father_name = false; $mother_name = false; $spouse_family = false; $indi_name = false; $indi_date = false; $fam_date = false; $indi_plac = false; $fam_plac = false; foreach ($this->fields as $n => $field) { if ($this->values[$n]) { if (substr($field, 0, 14) == 'FAMC:HUSB:NAME') { $father_name = true; } elseif (substr($field, 0, 14) == 'FAMC:WIFE:NAME') { $mother_name = true; } elseif (substr($field, 0, 4) == 'NAME') { $indi_name = true; } elseif (strpos($field, ':DATE') !== false) { if (substr($field, 0, 4) == 'FAMS') { $fam_date = true; $spouse_family = true; } else { $indi_date = true; } } elseif (strpos($field, ':PLAC') !== false) { if (substr($field, 0, 4) == 'FAMS') { $fam_plac = true; $spouse_family = true; } else { $indi_plac = true; } } elseif ($field == 'FAMS:NOTE') { $spouse_family = true; } } } if ($father_name || $mother_name) { $sql .= " JOIN `##link` l_1 ON (l_1.l_file=ind.i_file AND l_1.l_from=ind.i_id AND l_1.l_type='FAMC')"; } if ($father_name) { $sql .= " JOIN `##link` l_2 ON (l_2.l_file=ind.i_file AND l_2.l_from=l_1.l_to AND l_2.l_type='HUSB')"; $sql .= " JOIN `##name` f_n ON (f_n.n_file=ind.i_file AND f_n.n_id =l_2.l_to)"; } if ($mother_name) { $sql .= " JOIN `##link` l_3 ON (l_3.l_file=ind.i_file AND l_3.l_from=l_1.l_to AND l_3.l_type='WIFE')"; $sql .= " JOIN `##name` m_n ON (m_n.n_file=ind.i_file AND m_n.n_id =l_3.l_to)"; } if ($spouse_family) { $sql .= " JOIN `##link` l_4 ON (l_4.l_file=ind.i_file AND l_4.l_from=ind.i_id AND l_4.l_type='FAMS')"; $sql .= " JOIN `##families` fam ON (fam.f_file=ind.i_file AND fam.f_id =l_4.l_to)"; } if ($indi_name) { $sql .= " JOIN `##name` i_n ON (i_n.n_file=ind.i_file AND i_n.n_id=ind.i_id)"; } if ($indi_date) { $sql .= " JOIN `##dates` i_d ON (i_d.d_file=ind.i_file AND i_d.d_gid=ind.i_id)"; } if ($fam_date) { $sql .= " JOIN `##dates` f_d ON (f_d.d_file=ind.i_file AND f_d.d_gid=fam.f_id)"; } if ($indi_plac) { $sql .= " JOIN `##placelinks` i_pl ON (i_pl.pl_file=ind.i_file AND i_pl.pl_gid =ind.i_id)"; $sql .= " JOIN (" . "SELECT CONCAT_WS(', ', p1.p_place, p2.p_place, p3.p_place, p4.p_place, p5.p_place, p6.p_place, p7.p_place, p8.p_place, p9.p_place) AS place, p1.p_id AS id, p1.p_file AS file" . " FROM `##places` AS p1" . " LEFT JOIN `##places` AS p2 ON (p1.p_parent_id=p2.p_id)" . " LEFT JOIN `##places` AS p3 ON (p2.p_parent_id=p3.p_id)" . " LEFT JOIN `##places` AS p4 ON (p3.p_parent_id=p4.p_id)" . " LEFT JOIN `##places` AS p5 ON (p4.p_parent_id=p5.p_id)" . " LEFT JOIN `##places` AS p6 ON (p5.p_parent_id=p6.p_id)" . " LEFT JOIN `##places` AS p7 ON (p6.p_parent_id=p7.p_id)" . " LEFT JOIN `##places` AS p8 ON (p7.p_parent_id=p8.p_id)" . " LEFT JOIN `##places` AS p9 ON (p8.p_parent_id=p9.p_id)" . ") AS i_p ON (i_p.file =ind.i_file AND i_pl.pl_p_id= i_p.id)"; } if ($fam_plac) { $sql .= " JOIN `##placelinks` f_pl ON (f_pl.pl_file=ind.i_file AND f_pl.pl_gid =fam.f_id)"; $sql .= " JOIN (" . "SELECT CONCAT_WS(', ', p1.p_place, p2.p_place, p3.p_place, p4.p_place, p5.p_place, p6.p_place, p7.p_place, p8.p_place, p9.p_place) AS place, p1.p_id AS id, p1.p_file AS file" . " FROM `##places` AS p1" . " LEFT JOIN `##places` AS p2 ON (p1.p_parent_id=p2.p_id)" . " LEFT JOIN `##places` AS p3 ON (p2.p_parent_id=p3.p_id)" . " LEFT JOIN `##places` AS p4 ON (p3.p_parent_id=p4.p_id)" . " LEFT JOIN `##places` AS p5 ON (p4.p_parent_id=p5.p_id)" . " LEFT JOIN `##places` AS p6 ON (p5.p_parent_id=p6.p_id)" . " LEFT JOIN `##places` AS p7 ON (p6.p_parent_id=p7.p_id)" . " LEFT JOIN `##places` AS p8 ON (p7.p_parent_id=p8.p_id)" . " LEFT JOIN `##places` AS p9 ON (p8.p_parent_id=p9.p_id)" . ") AS f_p ON (f_p.file =ind.i_file AND f_pl.pl_p_id= f_p.id)"; } // Add the where clause $sql .= " WHERE ind.i_file=?"; $bind[] = $WT_TREE->getTreeId(); for ($i = 0; $i < $fct; $i++) { $field = $this->fields[$i]; $value = $this->values[$i]; if ($value === '') { continue; } $parts = preg_split("/:/", $field . '::::'); if ($parts[0] == 'NAME') { // NAME:* switch ($parts[1]) { case 'GIVN': switch ($parts[2]) { case 'EXACT': $sql .= " AND i_n.n_givn=?"; $bind[] = $value; break; case 'BEGINS': $sql .= " AND i_n.n_givn LIKE CONCAT(?, '%')"; $bind[] = $value; break; case 'CONTAINS': $sql .= " AND i_n.n_givn LIKE CONCAT('%', ?, '%')"; $bind[] = $value; break; case 'SDX_STD': $sdx = Soundex::russell($value); if ($sdx !== null) { $sdx = explode(':', $sdx); foreach ($sdx as $k => $v) { $sdx[$k] = "i_n.n_soundex_givn_std LIKE CONCAT('%', ?, '%')"; $bind[] = $v; } $sql .= ' AND (' . implode(' OR ', $sdx) . ')'; } else { // No phonetic content? Use a substring match $sql .= " AND i_n.n_givn LIKE CONCAT('%', ?, '%')"; $bind[] = $value; } break; case 'SDX': // SDX uses DM by default. // SDX uses DM by default. case 'SDX_DM': $sdx = Soundex::daitchMokotoff($value); if ($sdx !== null) { $sdx = explode(':', $sdx); foreach ($sdx as $k => $v) { $sdx[$k] = "i_n.n_soundex_givn_dm LIKE CONCAT('%', ?, '%')"; $bind[] = $v; } $sql .= ' AND (' . implode(' OR ', $sdx) . ')'; } else { // No phonetic content? Use a substring match $sql .= " AND i_n.n_givn LIKE CONCAT('%', ?, '%')"; $bind[] = $value; } break; } break; case 'SURN': switch ($parts[2]) { case 'EXACT': $sql .= " AND i_n.n_surname=?"; $bind[] = $value; break; case 'BEGINS': $sql .= " AND i_n.n_surname LIKE CONCAT(?, '%')"; $bind[] = $value; break; case 'CONTAINS': $sql .= " AND i_n.n_surname LIKE CONCAT('%', ?, '%')"; $bind[] = $value; break; case 'SDX_STD': $sdx = Soundex::russell($value); if ($sdx !== null) { $sdx = explode(':', $sdx); foreach ($sdx as $k => $v) { $sdx[$k] = "i_n.n_soundex_surn_std LIKE CONCAT('%', ?, '%')"; $bind[] = $v; } $sql .= " AND (" . implode(' OR ', $sdx) . ")"; } else { // No phonetic content? Use a substring match $sql .= " AND i_n.n_surn LIKE CONCAT('%', ?, '%')"; $bind[] = $value; } break; case 'SDX': // SDX uses DM by default. // SDX uses DM by default. case 'SDX_DM': $sdx = Soundex::daitchMokotoff($value); if ($sdx !== null) { $sdx = explode(':', $sdx); foreach ($sdx as $k => $v) { $sdx[$k] = "i_n.n_soundex_surn_dm LIKE CONCAT('%', ?, '%')"; $bind[] = $v; } $sql .= " AND (" . implode(' OR ', $sdx) . ")"; break; } else { // No phonetic content? Use a substring match $sql .= " AND i_n.n_surn LIKE CONCAT('%', ?, '%')"; $bind[] = $value; } } break; case 'NICK': case '_MARNM': case '_HEB': case '_AKA': $sql .= " AND i_n.n_type=? AND i_n.n_full LIKE CONCAT('%', ?, '%')"; $bind[] = $parts[1]; $bind[] = $value; break; } } elseif ($parts[1] == 'DATE') { // *:DATE $date = new Date($value); if ($date->isOK()) { $jd1 = $date->minimumJulianDay(); $jd2 = $date->maximumJulianDay(); if (!empty($this->plusminus[$i])) { $adjd = $this->plusminus[$i] * 365; $jd1 -= $adjd; $jd2 += $adjd; } $sql .= " AND i_d.d_fact=? AND i_d.d_julianday1>=? AND i_d.d_julianday2<=?"; $bind[] = $parts[0]; $bind[] = $jd1; $bind[] = $jd2; } } elseif ($parts[0] == 'FAMS' && $parts[2] == 'DATE') { // FAMS:*:DATE $date = new Date($value); if ($date->isOK()) { $jd1 = $date->minimumJulianDay(); $jd2 = $date->maximumJulianDay(); if (!empty($this->plusminus[$i])) { $adjd = $this->plusminus[$i] * 365; $jd1 -= $adjd; $jd2 += $adjd; } $sql .= " AND f_d.d_fact=? AND f_d.d_julianday1>=? AND f_d.d_julianday2<=?"; $bind[] = $parts[1]; $bind[] = $jd1; $bind[] = $jd2; } } elseif ($parts[1] == 'PLAC') { // *:PLAC // SQL can only link a place to a person/family, not to an event. $sql .= " AND i_p.place LIKE CONCAT('%', ?, '%')"; $bind[] = $value; } elseif ($parts[0] == 'FAMS' && $parts[2] == 'PLAC') { // FAMS:*:PLAC // SQL can only link a place to a person/family, not to an event. $sql .= " AND f_p.place LIKE CONCAT('%', ?, '%')"; $bind[] = $value; } elseif ($parts[0] == 'FAMC' && $parts[2] == 'NAME') { $table = $parts[1] == 'HUSB' ? 'f_n' : 'm_n'; // NAME:* switch ($parts[3]) { case 'GIVN': switch ($parts[4]) { case 'EXACT': $sql .= " AND {$table}.n_givn=?"; $bind[] = $value; break; case 'BEGINS': $sql .= " AND {$table}.n_givn LIKE CONCAT(?, '%')"; $bind[] = $value; break; case 'CONTAINS': $sql .= " AND {$table}.n_givn LIKE CONCAT('%', ?, '%')"; $bind[] = $value; break; case 'SDX_STD': $sdx = Soundex::russell($value); if ($sdx !== null) { $sdx = explode(':', $sdx); foreach ($sdx as $k => $v) { $sdx[$k] = "{$table}.n_soundex_givn_std LIKE CONCAT('%', ?, '%')"; $bind[] = $v; } $sql .= ' AND (' . implode(' OR ', $sdx) . ')'; } else { // No phonetic content? Use a substring match $sql .= " AND {$table}.n_givn = LIKE CONCAT('%', ?, '%')"; $bind[] = $value; } break; case 'SDX': // SDX uses DM by default. // SDX uses DM by default. case 'SDX_DM': $sdx = Soundex::daitchMokotoff($value); if ($sdx !== null) { $sdx = explode(':', $sdx); foreach ($sdx as $k => $v) { $sdx[$k] = "{$table}.n_soundex_givn_dm LIKE CONCAT('%', ?, '%')"; $bind[] = $v; } $sql .= ' AND (' . implode(' OR ', $sdx) . ')'; break; } else { // No phonetic content? Use a substring match $sql .= " AND {$table}.n_givn = LIKE CONCAT('%', ?, '%')"; $bind[] = $value; } } break; case 'SURN': switch ($parts[4]) { case 'EXACT': $sql .= " AND {$table}.n_surname=?"; $bind[] = $value; break; case 'BEGINS': $sql .= " AND {$table}.n_surname LIKE CONCAT(?, '%')"; $bind[] = $value; break; case 'CONTAINS': $sql .= " AND {$table}.n_surname LIKE CONCAT('%', ?, '%')"; $bind[] = $value; break; case 'SDX_STD': $sdx = Soundex::russell($value); if ($sdx !== null) { $sdx = explode(':', $sdx); foreach ($sdx as $k => $v) { $sdx[$k] = "{$table}.n_soundex_surn_std LIKE CONCAT('%', ?, '%')"; $bind[] = $v; } $sql .= ' AND (' . implode(' OR ', $sdx) . ')'; } else { // No phonetic content? Use a substring match $sql .= " AND {$table}.n_surn = LIKE CONCAT('%', ?, '%')"; $bind[] = $value; } break; case 'SDX': // SDX uses DM by default. // SDX uses DM by default. case 'SDX_DM': $sdx = Soundex::daitchMokotoff($value); if ($sdx !== null) { $sdx = explode(':', $sdx); foreach ($sdx as $k => $v) { $sdx[$k] = "{$table}.n_soundex_surn_dm LIKE CONCAT('%', ?, '%')"; $bind[] = $v; } $sql .= ' AND (' . implode(' OR ', $sdx) . ')'; } else { // No phonetic content? Use a substring match $sql .= " AND {$table}.n_surn = LIKE CONCAT('%', ?, '%')"; $bind[] = $value; } break; } break; } } elseif ($parts[0] == 'FAMS') { // e.g. searches for occupation, religion, note, etc. $sql .= " AND fam.f_gedcom REGEXP CONCAT('\n[0-9] ', ?, '(.*\n[0-9] CONT)* [^\n]*', ?)"; $bind[] = $parts[1]; $bind[] = $value; } else { // e.g. searches for occupation, religion, note, etc. $sql .= " AND ind.i_gedcom REGEXP CONCAT('\n[0-9] ', ?, '(.*\n[0-9] CONT)* [^\n]*', ?)"; $bind[] = $parts[0]; $bind[] = $value; } } $rows = Database::prepare($sql)->execute($bind)->fetchAll(); foreach ($rows as $row) { $person = Individual::getInstance($row->xref, $WT_TREE, $row->gedcom); // Check for XXXX:PLAC fields, which were only partially matched by SQL foreach ($this->fields as $n => $field) { if ($this->values[$n] && preg_match('/^(' . WT_REGEX_TAG . '):PLAC$/', $field, $match)) { if (!preg_match('/\\n1 ' . $match[1] . '(\\n[2-9].*)*\\n2 PLAC .*' . preg_quote($this->values[$n], '/') . '/i', $person->getGedcom())) { continue 2; } } } $this->myindilist[] = $person; } }
/** * Extract all the names from the given record and insert them into the database. * * @param string $xref * @param int $ged_id * @param GedcomRecord $record */ public static function updateNames($xref, $ged_id, GedcomRecord $record) { foreach ($record->getAllNames() as $n => $name) { if ($record instanceof Individual) { if ($name['givn'] === '@P.N.') { $soundex_givn_std = null; $soundex_givn_dm = null; } else { $soundex_givn_std = Soundex::russell($name['givn']); $soundex_givn_dm = Soundex::daitchMokotoff($name['givn']); } if ($name['surn'] === '@N.N.') { $soundex_surn_std = null; $soundex_surn_dm = null; } else { $soundex_surn_std = Soundex::russell($name['surname']); $soundex_surn_dm = Soundex::daitchMokotoff($name['surname']); } Database::prepare("INSERT INTO `##name` (n_file,n_id,n_num,n_type,n_sort,n_full,n_surname,n_surn,n_givn,n_soundex_givn_std,n_soundex_surn_std,n_soundex_givn_dm,n_soundex_surn_dm) VALUES (?, ?, ?, ?, LEFT(?, 255), LEFT(?, 255), LEFT(?, 255), LEFT(?, 255), ?, ?, ?, ?, ?)")->execute(array($ged_id, $xref, $n, $name['type'], $name['sort'], $name['fullNN'], $name['surname'], $name['surn'], $name['givn'], $soundex_givn_std, $soundex_surn_std, $soundex_givn_dm, $soundex_surn_dm)); } else { Database::prepare("INSERT INTO `##name` (n_file,n_id,n_num,n_type,n_sort,n_full) VALUES (?, ?, ?, ?, LEFT(?, 255), LEFT(?, 255))")->execute(array($ged_id, $xref, $n, $name['type'], $name['sort'], $name['fullNN'])); } } }
/** * Search for individuals names/places using phonetic matching * * @param string $soundex * @param string $lastname * @param string $firstname * @param string $place * @param Tree[] $trees * * @return Individual[] */ public static function searchIndividualsPhonetic($soundex, $lastname, $firstname, $place, array $trees) { switch ($soundex) { case 'Russell': $givn_sdx = Soundex::russell($firstname); $surn_sdx = Soundex::russell($lastname); $plac_sdx = Soundex::russell($place); break; case 'DaitchM': $givn_sdx = Soundex::daitchMokotoff($firstname); $surn_sdx = Soundex::daitchMokotoff($lastname); $plac_sdx = Soundex::daitchMokotoff($place); break; default: throw new \DomainException('soundex: ' . $soundex); } // Nothing to search for? Return nothing. if (!$givn_sdx && !$surn_sdx && !$plac_sdx) { return array(); } $sql = "SELECT DISTINCT i_id AS xref, i_file AS gedcom_id, i_gedcom AS gedcom FROM `##individuals`"; $args = array(); if ($place) { $sql .= " JOIN `##placelinks` ON pl_file = i_file AND pl_gid = i_id"; $sql .= " JOIN `##places` ON p_file = pl_file AND pl_p_id = p_id"; } if ($firstname || $lastname) { $sql .= " JOIN `##name` ON i_file=n_file AND i_id=n_id"; } $sql .= " AND i_file IN ("; foreach ($trees as $n => $tree) { $sql .= $n ? ", " : ""; $sql .= ":tree_id_" . $n; $args['tree_id_' . $n] = $tree->getTreeId(); } $sql .= ")"; if ($firstname && $givn_sdx) { $sql .= " AND ("; $givn_sdx = explode(':', $givn_sdx); foreach ($givn_sdx as $n => $sdx) { $sql .= $n ? " OR " : ""; switch ($soundex) { case 'Russell': $sql .= "n_soundex_givn_std LIKE CONCAT('%', :given_name_" . $n . ", '%')"; break; case 'DaitchM': $sql .= "n_soundex_givn_dm LIKE CONCAT('%', :given_name_" . $n . ", '%')"; break; } $args['given_name_' . $n] = $sdx; } $sql .= ")"; } if ($lastname && $surn_sdx) { $sql .= " AND ("; $surn_sdx = explode(':', $surn_sdx); foreach ($surn_sdx as $n => $sdx) { $sql .= $n ? " OR " : ""; switch ($soundex) { case 'Russell': $sql .= "n_soundex_surn_std LIKE CONCAT('%', :surname_" . $n . ", '%')"; break; case 'DaitchM': $sql .= "n_soundex_surn_dm LIKE CONCAT('%', :surname_" . $n . ", '%')"; break; } $args['surname_' . $n] = $sdx; } $sql .= ")"; } if ($place && $plac_sdx) { $sql .= " AND ("; $plac_sdx = explode(':', $plac_sdx); foreach ($plac_sdx as $n => $sdx) { $sql .= $n ? " OR " : ""; switch ($soundex) { case 'Russell': $sql .= "p_std_soundex LIKE CONCAT('%', :place_" . $n . ", '%')"; break; case 'DaitchM': $sql .= "p_dm_soundex LIKE CONCAT('%', :place_" . $n . ", '%')"; break; } $args['place_' . $n] = $sdx; } $sql .= ")"; } $list = array(); $rows = Database::prepare($sql)->execute($args)->fetchAll(); foreach ($rows as $row) { $list[] = Individual::getInstance($row->xref, Tree::findById($row->gedcom_id), $row->gedcom); } $list = array_filter($list, function (Individual $x) { return $x->canShowName(); }); return $list; }