/**
* Returns an array of the place hierarchy, based on a random example of place within the GEDCOM.
* It will look for the longest hierarchy in the tree.
* The places are reversed compared to normal GEDCOM structure.
*
* @return array
*/
protected function getPlacesHierarchyFromData()
{
$nb_levels = 0;
//Select all '2 PLAC ' tags in the file and create array
$places_list = array();
$ged_data = Database::prepare('SELECT i_gedcom AS gedcom' . ' FROM `##individuals`' . ' WHERE i_gedcom LIKE :gedcom AND i_file = :gedcom_id' . ' UNION ALL' . 'SELECT f_gedcom AS gedcom' . ' FROM `##families`' . ' WHERE f_gedcom LIKE :gedcom AND f_file = :gedcom_id')->execute(array('gedcom' => '%\\n2 PLAC %', 'gedcom_id' => $this->tree->getTreeId()))->fetchOneColumn();
foreach ($ged_data as $ged_datum) {
$matches = null;
preg_match_all('/\\n2 PLAC (.+)/', $ged_datum, $matches);
foreach ($matches[1] as $match) {
$places_list[$match] = true;
}
}
// Unique list of places
$places_list = array_keys($places_list);
//sort the array, limit to unique values, and count them
usort($places_list, array('I18N', 'strcasecmp'));
//calculate maximum no. of levels to display
$has_found_good_example = false;
foreach ($places_list as $place) {
$levels = explode(",", $place);
$parts = count($levels);
if ($parts >= $nb_levels) {
$nb_levels = $parts;
if (!$has_found_good_example) {
$random_place = $place;
if (min(array_map('strlen', $levels)) > 0) {
$has_found_good_example = true;
}
}
}
}
return array_reverse(array_map('trim', explode(',', $random_place)));
}
/**
* Sometimes, we'll know in advance that we need to load a set of records.
* Typically when we load families and their members.
*
* @param Tree $tree
* @param string[] $xrefs
*/
public static function load(Tree $tree, array $xrefs)
{
$args = array('tree_id' => $tree->getTreeId());
$placeholders = array();
foreach (array_unique($xrefs) as $n => $xref) {
if (!isset(self::$gedcom_record_cache[$tree->getTreeId()][$xref])) {
$placeholders[] = ':x' . $n;
$args['x' . $n] = $xref;
}
}
if (!empty($placeholders)) {
$rows = Database::prepare("SELECT i_id AS xref, i_gedcom AS gedcom" . " FROM `##individuals`" . " WHERE i_file = :tree_id AND i_id IN (" . implode(',', $placeholders) . ")")->execute($args)->fetchAll();
foreach ($rows as $row) {
self::getInstance($row->xref, $tree, $row->gedcom);
}
}
}
/**
* Return a computed array of statistics about the dispersion of ancestors across the ancestors
* at a specified generation.
* This statistics cannot be used for generations above 11, as it would cause a out of range in MySQL
*
* Format:
* - key : a base-2 representation of the ancestor at generation G for which exclusive ancestors have been found,
* -1 is used for shared ancestors
* For instance base2(0100) = base10(4) represent the maternal grand father
* - values: number of ancestors exclusively in the ancestors of the ancestor in key
*
* For instance a result at generation 3 could be :
* array ( -1 => 12 -> 12 ancestors are shared by the grand-parents
* base10(1) => 32 -> 32 ancestors are exclusive to the paternal grand-father
* base10(2) => 25 -> 25 ancestors are exclusive to the paternal grand-mother
* base10(4) => 12 -> 12 ancestors are exclusive to the maternal grand-father
* base10(8) => 30 -> 30 ancestors are exclusive to the maternal grand-mother
* )
*
* @param int $gen Reference generation
* @return array
*/
public function getAncestorDispersionForGen($gen)
{
if (!$this->is_setup || $gen > 11) {
return array();
}
// Going further than 11 gen will be out of range in the query
return Database::prepare('SELECT branches, count(i_id)' . ' FROM (' . ' SELECT i_id,' . ' CASE' . ' WHEN CEIL(LOG2(SUM(branch))) = LOG2(SUM(branch)) THEN SUM(branch)' . ' ELSE -1' . ' END branches' . ' FROM (' . ' SELECT DISTINCT majs_i_id i_id,' . ' POW(2, FLOOR(majs_sosa / POW(2, (majs_gen - :gen))) - POW(2, :gen -1)) branch' . ' FROM `##maj_sosa`' . ' WHERE majs_gedcom_id = :tree_id AND majs_user_id = :user_id' . ' AND majs_gen >= :gen' . ' ) indistat' . ' GROUP BY i_id' . ') grouped' . ' GROUP BY branches')->execute(array('tree_id' => $this->tree->getTreeId(), 'user_id' => $this->user->getUserId(), 'gen' => $gen))->fetchAssoc() ?: array();
}
/**
* Sometimes, we'll know in advance that we need to load a set of records.
* Typically when we load families and their members.
*
* @param Tree $tree
* @param string[] $xrefs
*/
public static function load(Tree $tree, array $xrefs)
{
$sql = '';
$args = array('tree_id' => $tree->getTreeId());
foreach (array_unique($xrefs) as $n => $xref) {
if (!isset(self::$gedcom_record_cache[$tree->getTreeId()][$xref])) {
$sql .= ($n ? ',:x' : ':x') . $n;
$args['x' . $n] = $xref;
}
}
if (count($args) > 1) {
$rows = Database::prepare("SELECT i_id AS xref, i_gedcom AS gedcom" . " FROM `##individuals`" . " WHERE i_file = :tree_id AND i_id IN (" . $sql . ")")->execute($args)->fetchAll();
foreach ($rows as $row) {
self::getInstance($row->xref, $tree, $row->gedcom);
}
}
}
/**
* Get a list of modules which (a) provide a specific function and (b) we have permission to see.
*
* We cannot currently use auto-loading for modules, as there may be user-defined
* modules about which the auto-loader knows nothing.
*
* @param Tree $tree
* @param string $component The type of module, such as "tab", "report" or "menu"
*
* @return AbstractModule[]
*/
private static function getActiveModulesByComponent(Tree $tree, $component)
{
$module_names = Database::prepare("SELECT SQL_CACHE module_name" . " FROM `##module`" . " JOIN `##module_privacy` USING (module_name)" . " WHERE gedcom_id = :tree_id AND component = :component AND status = 'enabled' AND access_level >= :access_level" . " ORDER BY CASE component WHEN 'menu' THEN menu_order WHEN 'sidebar' THEN sidebar_order WHEN 'tab' THEN tab_order ELSE 0 END, module_name")->execute(array('tree_id' => $tree->getTreeId(), 'component' => $component, 'access_level' => Auth::accessLevel($tree)))->fetchOneColumn();
$array = array();
foreach ($module_names as $module_name) {
$interface = '\\Fisharebest\\Webtrees\\Module\\Module' . ucfirst($component) . 'Interface';
$module = self::getModuleByName($module_name);
if ($module instanceof $interface) {
$array[$module_name] = $module;
}
}
// The order of menus/sidebars/tabs is defined in the database. Others are sorted by name.
if ($component !== 'menu' && $component !== 'sidebar' && $component !== 'tab') {
uasort($array, function (AbstractModule $x, AbstractModule $y) {
return I18N::strcasecmp($x->getTitle(), $y->getTitle());
});
}
return $array;
}
/**
* Fetch a list of individuals with specified names
*
* To search for unknown names, use $surn="@N.N.", $salpha="@" or $galpha="@"
* To search for names with no surnames, use $salpha=","
*
* @param Tree $tree only fetch individuals from this tree
* @param string $surn if set, only fetch people with this surname
* @param string $salpha if set, only fetch surnames starting with this letter
* @param string $galpha if set, only fetch given names starting with this letter
* @param bool $marnm if set, include married names
* @param bool $fams if set, only fetch individuals with FAMS records
*
* @return Individual[]
*/
public static function individuals(Tree $tree, $surn, $salpha, $galpha, $marnm, $fams)
{
$sql = "SELECT i_id AS xref, i_gedcom AS gedcom, n_full " . "FROM `##individuals` " . "JOIN `##name` ON n_id = i_id AND n_file = i_file " . ($fams ? "JOIN `##link` ON n_id = l_from AND n_file = l_file AND l_type = 'FAMS' " : "") . "WHERE n_file = :tree_id " . ($marnm ? "" : "AND n_type != '_MARNM'");
$args = array('tree_id' => $tree->getTreeId());
if ($surn) {
$sql .= " AND n_surn COLLATE :collate_1 = :surn";
$args['collate_1'] = I18N::collation();
$args['surn'] = $surn;
} elseif ($salpha === ',') {
$sql .= " AND n_surn = ''";
} elseif ($salpha === '@') {
$sql .= " AND n_surn = '@N.N.'";
} elseif ($salpha) {
$sql .= " AND " . self::getInitialSql('n_surn', $salpha);
} else {
// All surnames
$sql .= " AND n_surn NOT IN ('', '@N.N.')";
}
if ($galpha) {
$sql .= " AND " . self::getInitialSql('n_givn', $galpha);
}
$sql .= " ORDER BY CASE n_surn WHEN '@N.N.' THEN 1 ELSE 0 END, n_surn COLLATE :collate_2, CASE n_givn WHEN '@P.N.' THEN 1 ELSE 0 END, n_givn COLLATE :collate_3";
$args['collate_2'] = I18N::collation();
$args['collate_3'] = I18N::collation();
$list = array();
$rows = Database::prepare($sql)->execute($args)->fetchAll();
foreach ($rows as $row) {
$person = Individual::getInstance($row->xref, $tree, $row->gedcom);
// The name from the database may be private - check the filtered list...
foreach ($person->getAllNames() as $n => $name) {
if ($name['fullNN'] == $row->n_full) {
$person->setPrimaryName($n);
// We need to clone $person, as we may have multiple references to the
// same person in this list, and the "primary name" would otherwise
// be shared amongst all of them.
$list[] = clone $person;
break;
}
}
}
return $list;
}
/**
* Search for individuals in a tree.
*
* @param Tree $tree Search this tree
* @param string $query Search for this text
*
* @return string
*/
private function search(Tree $tree, $query)
{
if (strlen($query) < 2) {
return '';
}
$rows = Database::prepare("SELECT i_id AS xref, i_gedcom AS gedcom" . " FROM `##individuals`, `##name`" . " WHERE (i_id LIKE CONCAT('%', :query_1, '%') OR n_sort LIKE CONCAT('%', :query_2, '%'))" . " AND i_id = n_id AND i_file = n_file AND i_file = :tree_id" . " ORDER BY n_sort COLLATE :collation" . " LIMIT 50")->execute(array('query_1' => $query, 'query_2' => $query, 'tree_id' => $tree->getTreeId(), 'collation' => I18N::collation()))->fetchAll();
$out = '<ul>';
foreach ($rows as $row) {
$person = Individual::getInstance($row->xref, $tree, $row->gedcom);
if ($person->canShowName()) {
$out .= '<li><a href="' . $person->getHtmlUrl() . '">' . $person->getSexImage() . ' ' . $person->getFullName() . ' ';
if ($person->canShow()) {
$bd = $person->getLifeSpan();
if (!empty($bd)) {
$out .= ' (' . $bd . ')';
}
}
$out .= '</a></li>';
}
}
$out .= '</ul>';
return $out;
}
/**
* Find records that have changed since a given julian day
*
* @param Tree $tree Changes for which tree
* @param int $jd Julian day
*
* @return GedcomRecord[] List of records with changes
*/
private function getRecentChanges(Tree $tree, $jd)
{
$sql = "SELECT d_gid FROM `##dates`" . " WHERE d_fact='CHAN' AND d_julianday1 >= :jd AND d_file = :tree_id";
$vars = array('jd' => $jd, 'tree_id' => $tree->getTreeId());
$xrefs = Database::prepare($sql)->execute($vars)->fetchOneColumn();
$records = array();
foreach ($xrefs as $xref) {
$record = GedcomRecord::getInstance($xref, $tree);
if ($record->canShow()) {
$records[] = $record;
}
}
return $records;
}
/**
* Autocomplete search for families.
*
* @param Tree $tree Search this tree
* @param string $query Search for this text
*
* @return string
*/
private function search(Tree $tree, $query)
{
if (strlen($query) < 2) {
return '';
}
$rows = Database::prepare("SELECT i_id AS xref" . " FROM `##individuals`, `##name`" . " WHERE (i_id LIKE CONCAT('%', :query_1, '%') OR n_sort LIKE CONCAT('%', :query_2, '%'))" . " AND i_id = n_id AND i_file = n_file AND i_file = :tree_id" . " ORDER BY n_sort COLLATE :collation" . " LIMIT 50")->execute(array('query_1' => $query, 'query_2' => $query, 'tree_id' => $tree->getTreeId(), 'collation' => I18N::collation()))->fetchAll();
$ids = array();
foreach ($rows as $row) {
$ids[] = $row->xref;
}
$vars = array();
if (empty($ids)) {
//-- no match : search for FAM id
$where = "f_id LIKE CONCAT('%', ?, '%')";
$vars[] = $query;
} else {
//-- search for spouses
$qs = implode(',', array_fill(0, count($ids), '?'));
$where = "(f_husb IN ({$qs}) OR f_wife IN ({$qs}))";
$vars = array_merge($vars, $ids, $ids);
}
$vars[] = $tree->getTreeId();
$rows = Database::prepare("SELECT f_id AS xref, f_file AS gedcom_id, f_gedcom AS gedcom FROM `##families` WHERE {$where} AND f_file=?")->execute($vars)->fetchAll();
$out = '<ul>';
foreach ($rows as $row) {
$family = Family::getInstance($row->xref, $tree, $row->gedcom);
if ($family->canShowName()) {
$out .= '<li><a href="' . $family->getHtmlUrl() . '">' . $family->getFullName() . ' ';
if ($family->canShow()) {
$marriage_year = $family->getMarriageYear();
if ($marriage_year) {
$out .= ' (' . $marriage_year . ')';
}
}
$out .= '</a></li>';
}
}
$out .= '</ul>';
return $out;
}
/**
* Get a the current access level for a module
*
* @param Tree $tree
* @param string $component tab, block, menu, etc
*
* @return int
*/
public function getAccessLevel(Tree $tree, $component)
{
$access_level = Database::prepare("SELECT access_level FROM `##module_privacy` WHERE gedcom_id = :gedcom_id AND module_name = :module_name AND component = :component")->execute(array('gedcom_id' => $tree->getTreeId(), 'module_name' => $this->getName(), 'component' => $component))->fetchOne();
if ($access_level === null) {
return $this->defaultAccessLevel();
} else {
return (int) $access_level;
}
}
/**
* Get an instance of a GedcomRecord object. For single records,
* we just receive the XREF. For bulk records (such as lists
* and search results) we can receive the GEDCOM data as well.
*
* @param string $xref
* @param Tree $tree
* @param string|null $gedcom
*
* @throws \Exception
*
* @return GedcomRecord|Individual|Family|Source|Repository|Media|Note|null
*/
public static function getInstance($xref, Tree $tree, $gedcom = null)
{
$tree_id = $tree->getTreeId();
// Is this record already in the cache, and of the correct type?
if (isset(self::$gedcom_record_cache[$xref][$tree_id])) {
$record = self::$gedcom_record_cache[$xref][$tree_id];
if ($record instanceof static) {
return $record;
} else {
null;
}
}
// Do we need to fetch the record from the database?
if ($gedcom === null) {
$gedcom = static::fetchGedcomRecord($xref, $tree_id);
}
// If we can edit, then we also need to be able to see pending records.
if (Auth::isEditor($tree)) {
if (!isset(self::$pending_record_cache[$tree_id])) {
// Fetch all pending records in one database query
self::$pending_record_cache[$tree_id] = array();
$rows = Database::prepare("SELECT xref, new_gedcom FROM `##change` WHERE status = 'pending' AND gedcom_id = :tree_id ORDER BY change_id")->execute(array('tree_id' => $tree_id))->fetchAll();
foreach ($rows as $row) {
self::$pending_record_cache[$tree_id][$row->xref] = $row->new_gedcom;
}
}
if (isset(self::$pending_record_cache[$tree_id][$xref])) {
// A pending edit exists for this record
$pending = self::$pending_record_cache[$tree_id][$xref];
} else {
$pending = null;
}
} else {
// There are no pending changes for this record
$pending = null;
}
// No such record exists
if ($gedcom === null && $pending === null) {
return null;
}
// Create the object
if (preg_match('/^0 @(' . WT_REGEX_XREF . ')@ (' . WT_REGEX_TAG . ')/', $gedcom . $pending, $match)) {
$xref = $match[1];
// Collation - we may have requested I123 and found i123
$type = $match[2];
} elseif (preg_match('/^0 (HEAD|TRLR)/', $gedcom . $pending, $match)) {
$xref = $match[1];
$type = $match[1];
} elseif ($gedcom . $pending) {
throw new \Exception('Unrecognized GEDCOM record: ' . $gedcom);
} else {
// A record with both pending creation and pending deletion
$type = static::RECORD_TYPE;
}
switch ($type) {
case 'INDI':
$record = new Individual($xref, $gedcom, $pending, $tree);
break;
case 'FAM':
$record = new Family($xref, $gedcom, $pending, $tree);
break;
case 'SOUR':
$record = new Source($xref, $gedcom, $pending, $tree);
break;
case 'OBJE':
$record = new Media($xref, $gedcom, $pending, $tree);
break;
case 'REPO':
$record = new Repository($xref, $gedcom, $pending, $tree);
break;
case 'NOTE':
$record = new Note($xref, $gedcom, $pending, $tree);
break;
default:
$record = new self($xref, $gedcom, $pending, $tree);
break;
}
// Store it in the cache
self::$gedcom_record_cache[$xref][$tree_id] = $record;
return $record;
}
/**
* Create a new media object, from inline media data.
*
* @param int $level
* @param string $gedrec
* @param Tree $tree
*
* @return string
*/
public static function createMediaObject($level, $gedrec, Tree $tree)
{
if (preg_match('/\\n\\d FILE (.+)/', $gedrec, $file_match)) {
$file = $file_match[1];
} else {
$file = '';
}
if (preg_match('/\\n\\d TITL (.+)/', $gedrec, $file_match)) {
$titl = $file_match[1];
} else {
$titl = '';
}
// Have we already created a media object with the same title/filename?
$xref = Database::prepare("SELECT m_id FROM `##media` WHERE m_filename = ? AND m_titl = ? AND m_file = ?")->execute(array($file, $titl, $tree->getTreeId()))->fetchOne();
if (!$xref) {
$xref = $tree->getNewXref('OBJE');
// renumber the lines
$gedrec = preg_replace_callback('/\\n(\\d+)/', function ($m) use($level) {
return "\n" . ($m[1] - $level);
}, $gedrec);
// convert to an object
$gedrec = str_replace("\n0 OBJE\n", '0 @' . $xref . "@ OBJE\n", $gedrec);
// Fix Legacy GEDCOMS
$gedrec = preg_replace('/\\n1 FORM (.+)\\n1 FILE (.+)\\n1 TITL (.+)/', "\n1 FILE \$2\n2 FORM \$1\n2 TITL \$3", $gedrec);
// Fix FTB GEDCOMS
$gedrec = preg_replace('/\\n1 FORM (.+)\\n1 TITL (.+)\\n1 FILE (.+)/', "\n1 FILE \$3\n2 FORM \$1\n2 TITL \$2", $gedrec);
// Create new record
$record = new Media($xref, $gedrec, null, $tree);
Database::prepare("INSERT INTO `##media` (m_id, m_ext, m_type, m_titl, m_filename, m_file, m_gedcom) VALUES (?, ?, ?, ?, ?, ?, ?)")->execute(array($xref, $record->extension(), $record->getMediaType(), $record->getTitle(), $record->getFilename(), $tree->getTreeId(), $gedrec));
}
return "\n" . $level . ' OBJE @' . $xref . '@';
}
/**
* Respond to an autocomplete search request.
*
* @param string $query Search for this term
* @param Tree $tree Search in this tree
*
* @return string
*/
public function search($query, Tree $tree)
{
if (strlen($query) < 2) {
return '';
}
$rows = Database::prepare("SELECT i_id AS xref" . " FROM `##individuals`" . " JOIN `##name` ON i_id = n_id AND i_file = n_file" . " WHERE n_sort LIKE CONCAT('%', :query, '%') AND i_file = :tree_id" . " ORDER BY n_sort")->execute(array('query' => $query, 'tree_id' => $tree->getTreeId()))->fetchAll();
$out = '';
foreach ($rows as $row) {
$person = Individual::getInstance($row->xref, $tree);
if ($person && $person->canShowName()) {
$out .= $this->getPersonLi($person);
}
}
if ($out) {
return '<ul>' . $out . '</ul>';
} else {
return '';
}
}
/**
* Update favorites after merging records.
*
* @param string $xref_from
* @param string $xref_to
* @param Tree $tree
*
* @return int
*/
public static function updateFavorites($xref_from, $xref_to, Tree $tree)
{
return Database::prepare("UPDATE `##favorite` SET xref=? WHERE xref=? AND gedcom_id=?")->execute(array($xref_to, $xref_from, $tree->getTreeId()))->rowCount();
}