diff options
Diffstat (limited to 'app/Functions/FunctionsDb.php')
| -rw-r--r-- | app/Functions/FunctionsDb.php | 1081 |
1 files changed, 1081 insertions, 0 deletions
diff --git a/app/Functions/FunctionsDb.php b/app/Functions/FunctionsDb.php new file mode 100644 index 0000000000..f0bdabe01a --- /dev/null +++ b/app/Functions/FunctionsDb.php @@ -0,0 +1,1081 @@ +<?php +namespace Fisharebest\Webtrees\Functions; + +/** + * webtrees: online genealogy + * Copyright (C) 2015 webtrees development team + * This program is free software: you can redistribute it and/or modify + * it under the terms of the GNU General Public License as published by + * the Free Software Foundation, either version 3 of the License, or + * (at your option) any later version. + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + * You should have received a copy of the GNU General Public License + * along with this program. If not, see <http://www.gnu.org/licenses/>. + */ +use Fisharebest\Webtrees\Auth; +use Fisharebest\Webtrees\Database; +use Fisharebest\Webtrees\Date; +use Fisharebest\Webtrees\Date\FrenchDate; +use Fisharebest\Webtrees\Date\GregorianDate; +use Fisharebest\Webtrees\Date\HijriDate; +use Fisharebest\Webtrees\Date\JalaliDate; +use Fisharebest\Webtrees\Date\JewishDate; +use Fisharebest\Webtrees\Date\JulianDate; +use Fisharebest\Webtrees\Fact; +use Fisharebest\Webtrees\Family; +use Fisharebest\Webtrees\Filter; +use Fisharebest\Webtrees\I18N; +use Fisharebest\Webtrees\Individual; +use Fisharebest\Webtrees\Note; +use Fisharebest\Webtrees\Repository; +use Fisharebest\Webtrees\Soundex; +use Fisharebest\Webtrees\Source; +use Fisharebest\Webtrees\Tree; + +/** + * Class FunctionsDb - common functions + */ +class FunctionsDb { + /** + * Fetch all records linked to a record - when deleting an object, we must + * also delete all links to it. + * + * @param string $xref + * @param int $gedcom_id + * + * @return string[] + */ + public static function fetchAllLinks($xref, $gedcom_id) { + return + Database::prepare( + "SELECT l_from FROM `##link` WHERE l_file = ? AND l_to = ?" . + " UNION " . + "SELECT xref FROM `##change` WHERE status = 'pending' AND gedcom_id = ? AND new_gedcom LIKE" . + " CONCAT('%@', ?, '@%')" + )->execute(array( + $gedcom_id, + $xref, + $gedcom_id, + $xref, + ))->fetchOneColumn(); + } + + /** + * Get a list of all the sources. + * + * @param Tree $tree + * + * @return Source[] array + */ + public static function getSourceList(Tree $tree) { + $rows = Database::prepare( + "SELECT s_id AS xref, s_gedcom AS gedcom FROM `##sources` WHERE s_file = :tree_id" + )->execute(array( + 'tree_id' => $tree->getTreeId(), + ))->fetchAll(); + + $list = array(); + foreach ($rows as $row) { + $list[] = Source::getInstance($row->xref, $tree, $row->gedcom); + } + usort($list, '\Fisharebest\Webtrees\GedcomRecord::compare'); + + return $list; + } + + /** + * Get a list of all the repositories. + * + * @param Tree $tree + * + * @return Repository[] array + */ + public static function getRepositoryList(Tree $tree) { + $rows = Database::prepare( + "SELECT o_id AS xref, o_gedcom AS gedcom FROM `##other` WHERE o_type = 'REPO' AND o_file = ?" + )->execute(array( + $tree->getTreeId(), + ))->fetchAll(); + + $list = array(); + foreach ($rows as $row) { + $list[] = Repository::getInstance($row->xref, $tree, $row->gedcom); + } + usort($list, '\Fisharebest\Webtrees\GedcomRecord::compare'); + + return $list; + } + + /** + * Get a list of all the shared notes. + * + * @param Tree $tree + * + * @return Note[] array + */ + public static function getNoteList(Tree $tree) { + $rows = Database::prepare( + "SELECT o_id AS xref, o_gedcom AS gedcom FROM `##other` WHERE o_type = 'NOTE' AND o_file = :tree_id" + )->execute(array( + 'tree_id' => $tree->getTreeId(), + ))->fetchAll(); + + $list = array(); + foreach ($rows as $row) { + $list[] = Note::getInstance($row->xref, $tree, $row->gedcom); + } + usort($list, '\Fisharebest\Webtrees\GedcomRecord::compare'); + + return $list; + } + + /** + * Search all individuals + * + * @param string[] $query Search terms + * @param Tree[] $trees The trees to search + * + * @return Individual[] + */ + public static function searchIndividuals(array $query, array $trees) { + // Convert the query into a regular expression + $queryregex = array(); + + $sql = "SELECT i_id AS xref, i_file AS gedcom_id, i_gedcom AS gedcom FROM `##individuals` WHERE 1"; + $args = array(); + + foreach ($query as $n => $q) { + $queryregex[] = preg_quote(I18N::strtoupper($q), '/'); + $sql .= " AND i_gedcom COLLATE :collate_" . $n . " LIKE CONCAT('%', :query_" . $n . ", '%')"; + $args['collate_' . $n] = I18N::collation(); + $args['query_' . $n] = Filter::escapeLike($q); + } + + $sql .= " AND i_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) { + // SQL may have matched on private data or gedcom tags, so check again against privatized data. + $record = Individual::getInstance($row->xref, Tree::findById($row->gedcom_id), $row->gedcom); + // Ignore non-genealogy data + $gedrec = preg_replace('/\n\d (_UID|_WT_USER|FILE|FORM|TYPE|CHAN|REFN|RESN) .*/', '', $record->getGedcom()); + // Ignore links and tags + $gedrec = preg_replace('/\n\d ' . WT_REGEX_TAG . '( @' . WT_REGEX_XREF . '@)?/', '', $gedrec); + // Re-apply the filtering + $gedrec = I18N::strtoupper($gedrec); + foreach ($queryregex as $regex) { + if (!preg_match('/' . $regex . '/', $gedrec)) { + continue 2; + } + } + $list[] = $record; + } + + return $list; + } + + /** + * Search the names of individuals + * + * @param string[] $query Search terms + * @param Tree[] $trees The trees to search + * + * @return Individual[] + */ + public static function searchIndividualNames(array $query, array $trees) { + $sql = "SELECT DISTINCT i_id AS xref, i_file AS gedcom_id, i_gedcom AS gedcom, n_full FROM `##individuals` JOIN `##name` ON i_id=n_id AND i_file=n_file WHERE 1"; + $args = array(); + + // Convert the query into a SQL expression + foreach ($query as $n => $q) { + $sql .= " AND n_full COLLATE :collate_" . $n . " LIKE CONCAT('%', :query_" . $n . ", '%')"; + $args['collate_' . $n] = I18N::collation(); + $args['query_' . $n] = Filter::escapeLike($q); + } + + $sql .= " AND i_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) { + $indi = Individual::getInstance($row->xref, Tree::findById($row->gedcom_id), $row->gedcom); + // The individual may have private names - and the DB search may have found it. + if ($indi->canShowName()) { + foreach ($indi->getAllNames() as $num => $name) { + if ($name['fullNN'] === $row->n_full) { + $indi->setPrimaryName($num); + // We need to clone $indi, 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 $indi; + // Only need to match an individual on one name + break; + } + } + } + } + + return $list; + } + + /** + * 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) { + $indi = Individual::getInstance($row->xref, Tree::findById($row->gedcom_id), $row->gedcom); + if ($indi->canShowName()) { + $list[] = $indi; + } + } + + return $list; + } + + /** + * get recent changes since the given julian day inclusive + * + * @param int $jd leave empty to include all + * @param bool $allgeds + * + * @return string[] List of XREFs of records with changes + */ + public static function getRecentChanges($jd = 0, $allgeds = false) { + global $WT_TREE; + + $sql = "SELECT d_gid FROM `##dates` WHERE d_fact='CHAN' AND d_julianday1>=?"; + $vars = array($jd); + if (!$allgeds) { + $sql .= " AND d_file=?"; + $vars[] = $WT_TREE->getTreeId(); + } + $sql .= " ORDER BY d_julianday1 DESC"; + + return Database::prepare($sql)->execute($vars)->fetchOneColumn(); + } + + /** + * Search family records + * + * @param string[] $query Search terms + * @param Tree[] $trees The trees to search + * + * @return Family[] + */ + public static function searchFamilies(array $query, array $trees) { + // Convert the query into a regular expression + $queryregex = array(); + + $sql = "SELECT f_id AS xref, f_file AS gedcom_id, f_gedcom AS gedcom FROM `##families` WHERE 1"; + $args = array(); + + foreach ($query as $n => $q) { + $queryregex[] = preg_quote(I18N::strtoupper($q), '/'); + $sql .= " AND f_gedcom COLLATE :collate_" . $n . " LIKE CONCAT('%', :query_" . $n . ", '%')"; + $args['collate_' . $n] = I18N::collation(); + $args['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) { + // SQL may have matched on private data or gedcom tags, so check again against privatized data. + $record = Family::getInstance($row->xref, Tree::findById($row->gedcom_id), $row->gedcom); + // Ignore non-genealogy data + $gedrec = preg_replace('/\n\d (_UID|_WT_USER|FILE|FORM|TYPE|CHAN|REFN|RESN) .*/', '', $record->getGedcom()); + // Ignore links and tags + $gedrec = preg_replace('/\n\d ' . WT_REGEX_TAG . '( @' . WT_REGEX_XREF . '@)?/', '', $gedrec); + // Ignore tags + $gedrec = preg_replace('/\n\d ' . WT_REGEX_TAG . ' ?/', '', $gedrec); + // Re-apply the filtering + $gedrec = I18N::strtoupper($gedrec); + foreach ($queryregex as $regex) { + if (!preg_match('/' . $regex . '/', $gedrec)) { + continue 2; + } + } + $list[] = $record; + } + + return $list; + } + + /** + * 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) { + $indi = Family::getInstance($row->xref, Tree::findById($row->gedcom_id), $row->gedcom); + if ($indi->canShowName()) { + $list[] = $indi; + } + } + + return $list; + } + + /** + * Search the sources + * + * @param string[] $query Search terms + * @param Tree[] $trees The tree to search + * + * @return Source[] + */ + public static function searchSources($query, $trees) { + // Convert the query into a regular expression + $queryregex = array(); + + $sql = "SELECT s_id AS xref, s_file AS gedcom_id, s_gedcom AS gedcom FROM `##sources` WHERE 1"; + $args = array(); + + foreach ($query as $n => $q) { + $queryregex[] = preg_quote(I18N::strtoupper($q), '/'); + $sql .= " AND s_gedcom COLLATE :collate_" . $n . " LIKE CONCAT('%', :query_" . $n . ", '%')"; + $args['collate_' . $n] = I18N::collation(); + $args['query_' . $n] = Filter::escapeLike($q); + } + + $sql .= " AND s_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) { + // SQL may have matched on private data or gedcom tags, so check again against privatized data. + $record = Source::getInstance($row->xref, Tree::findById($row->gedcom_id), $row->gedcom); + // Ignore non-genealogy data + $gedrec = preg_replace('/\n\d (_UID|_WT_USER|FILE|FORM|TYPE|CHAN|REFN|RESN) .*/', '', $record->getGedcom()); + // Ignore links and tags + $gedrec = preg_replace('/\n\d ' . WT_REGEX_TAG . '( @' . WT_REGEX_XREF . '@)?/', '', $gedrec); + // Ignore tags + $gedrec = preg_replace('/\n\d ' . WT_REGEX_TAG . ' ?/', '', $gedrec); + // Re-apply the filtering + $gedrec = I18N::strtoupper($gedrec); + foreach ($queryregex as $regex) { + if (!preg_match('/' . $regex . '/', $gedrec)) { + continue 2; + } + } + $list[] = $record; + } + + return $list; + } + + /** + * Search the shared notes + * + * @param string[] $query Search terms + * @param Tree[] $trees The tree to search + * + * @return Note[] + */ + public static function searchNotes(array $query, array $trees) { + // Convert the query into a regular expression + $queryregex = array(); + + $sql = "SELECT o_id AS xref, o_file AS gedcom_id, o_gedcom AS gedcom FROM `##other` WHERE o_type = 'NOTE'"; + $args = array(); + + foreach ($query as $n => $q) { + $queryregex[] = preg_quote(I18N::strtoupper($q), '/'); + $sql .= " AND o_gedcom COLLATE :collate_" . $n . " LIKE CONCAT('%', :query_" . $n . ", '%')"; + $args['collate_' . $n] = I18N::collation(); + $args['query_' . $n] = Filter::escapeLike($q); + } + + $sql .= " AND o_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) { + // SQL may have matched on private data or gedcom tags, so check again against privatized data. + $record = Note::getInstance($row->xref, Tree::findById($row->gedcom_id), $row->gedcom); + // Ignore non-genealogy data + $gedrec = preg_replace('/\n\d (_UID|_WT_USER|FILE|FORM|TYPE|CHAN|REFN|RESN) .*/', '', $record->getGedcom()); + // Ignore links and tags + $gedrec = preg_replace('/\n\d ' . WT_REGEX_TAG . '( @' . WT_REGEX_XREF . '@)?/', '', $gedrec); + // Ignore tags + $gedrec = preg_replace('/\n\d ' . WT_REGEX_TAG . ' ?/', '', $gedrec); + // Re-apply the filtering + $gedrec = I18N::strtoupper($gedrec); + foreach ($queryregex as $regex) { + if (!preg_match('/' . $regex . '/', $gedrec)) { + continue 2; + } + } + $list[] = $record; + } + + return $list; + } + + /** + * Search the repositories + * + * @param string[] $query Search terms + * @param Tree[] $trees The trees to search + * + * @return Repository[] + */ + public static function searchRepositories(array $query, array $trees) { + // Convert the query into a regular expression + $queryregex = array(); + + $sql = "SELECT o_id AS xref, o_file AS gedcom_id, o_gedcom AS gedcom FROM `##other` WHERE o_type = 'REPO'"; + $args = array(); + + foreach ($query as $n => $q) { + $queryregex[] = preg_quote(I18N::strtoupper($q), '/'); + $sql .= " AND o_gedcom COLLATE :collate_" . $n . " LIKE CONCAT('%', :query_" . $n . ", '%')"; + $args['collate_' . $n] = I18N::collation(); + $args['query_' . $n] = Filter::escapeLike($q); + } + + $sql .= " AND o_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) { + // SQL may have matched on private data or gedcom tags, so check again against privatized data. + $record = Repository::getInstance($row->xref, Tree::findById($row->gedcom_id), $row->gedcom); + // Ignore non-genealogy data + $gedrec = preg_replace('/\n\d (_UID|_WT_USER|FILE|FORM|TYPE|CHAN|REFN|RESN) .*/', '', $record->getGedcom()); + // Ignore links and tags + $gedrec = preg_replace('/\n\d ' . WT_REGEX_TAG . '( @' . WT_REGEX_XREF . '@)?/', '', $gedrec); + // Ignore tags + $gedrec = preg_replace('/\n\d ' . WT_REGEX_TAG . ' ?/', '', $gedrec); + // Re-apply the filtering + $gedrec = I18N::strtoupper($gedrec); + foreach ($queryregex as $regex) { + if (!preg_match('/' . $regex . '/', $gedrec)) { + continue 2; + } + } + $list[] = $record; + } + + return $list; + } + + /** + * Find the record for the given rin. + * + * @param string $rin + * + * @return string + */ + public static function findRin($rin) { + global $WT_TREE; + + $xref = + Database::prepare("SELECT i_id FROM `##individuals` WHERE i_rin=? AND i_file=?") + ->execute(array($rin, $WT_TREE->getTreeId())) + ->fetchOne(); + + return $xref ? $xref : $rin; + } + + /** + * Get array of common surnames + * + * This function returns a simple array of the most common surnames + * found in the individuals list. + * + * @param int $min The number of times a surname must occur before it is added to the array + * @param Tree $tree + * + * @return mixed[][] + */ + public static function getCommonSurnames($min, Tree $tree) { + $COMMON_NAMES_ADD = $tree->getPreference('COMMON_NAMES_ADD'); + $COMMON_NAMES_REMOVE = $tree->getPreference('COMMON_NAMES_REMOVE'); + + $topsurns = self::getTopSurnames($tree->getTreeId(), $min, 0); + foreach (explode(',', $COMMON_NAMES_ADD) as $surname) { + if ($surname && !array_key_exists($surname, $topsurns)) { + $topsurns[$surname] = $min; + } + } + foreach (explode(',', $COMMON_NAMES_REMOVE) as $surname) { + unset($topsurns[I18N::strtoupper($surname)]); + } + + //-- check if we found some, else recurse + if (empty($topsurns) && $min > 2) { + return self::getCommonSurnames($min / 2, $tree); + } else { + uksort($topsurns, '\Fisharebest\Webtrees\I18N::strcasecmp'); + foreach ($topsurns as $key => $value) { + $topsurns[$key] = array('name' => $key, 'match' => $value); + } + + return $topsurns; + } + } + + /** + * get the top surnames + * + * @param int $ged_id fetch surnames from this gedcom + * @param int $min only fetch surnames occuring this many times + * @param int $max only fetch this number of surnames (0=all) + * + * @return string[] + */ + public static function getTopSurnames($ged_id, $min, $max) { + // Use n_surn, rather than n_surname, as it is used to generate URLs for + // the indi-list, etc. + $max = (int) $max; + if ($max == 0) { + return + Database::prepare( + "SELECT SQL_CACHE n_surn, COUNT(n_surn) FROM `##name`" . + " WHERE n_file = :tree_id AND n_type != '_MARNM' AND n_surn NOT IN ('@N.N.', '', '?', 'UNKNOWN')" . + " GROUP BY n_surn HAVING COUNT(n_surn) >= :min" . + " ORDER BY 2 DESC" + )->execute(array( + 'tree_id' => $ged_id, + 'min' => $min, + ))->fetchAssoc(); + } else { + return + Database::prepare( + "SELECT SQL_CACHE n_surn, COUNT(n_surn) FROM `##name`" . + " WHERE n_file = :tree_id AND n_type != '_MARNM' AND n_surn NOT IN ('@N.N.', '', '?', 'UNKNOWN')" . + " GROUP BY n_surn HAVING COUNT(n_surn) >= :min" . + " ORDER BY 2 DESC" . + " LIMIT :limit" + )->execute(array( + 'tree_id' => $ged_id, + 'min' => $min, + 'limit' => $max, + ))->fetchAssoc(); + } + } + + /** + * Get a list of events whose anniversary occured on a given julian day. + * Used on the on-this-day/upcoming blocks and the day/month calendar views. + * + * @param int $jd the julian day + * @param string $facts restrict the search to just these facts or leave blank for all + * @param Tree $tree the tree to search + * + * @return Fact[] + */ + public static function getAnniversaryEvents($jd, $facts, Tree $tree) { + $found_facts = array(); + foreach (array( + new GregorianDate($jd), + new JulianDate($jd), + new FrenchDate($jd), + new JewishDate($jd), + new HijriDate($jd), + new JalaliDate($jd), + ) as $anniv) { + // Build a SQL where clause to match anniversaries in the appropriate calendar. + $ind_sql = + "SELECT DISTINCT i_id AS xref, i_gedcom AS gedcom, d_type, d_day, d_month, d_year, d_fact" . + " FROM `##dates` JOIN `##individuals` ON d_gid = i_id AND d_file = i_file" . + " WHERE d_type = :type AND d_file = :tree_id"; + $fam_sql = + "SELECT DISTINCT f_id AS xref, f_gedcom AS gedcom, d_type, d_day, d_month, d_year, d_fact" . + " FROM `##dates` JOIN `##families` ON d_gid = f_id AND d_file = f_file" . + " WHERE d_type = :type AND d_file = :tree_id"; + $args = array( + 'type' => $anniv->format('%@'), + 'tree_id' => $tree->getTreeId(), + ); + + $where = ""; + // SIMPLE CASES: + // a) Non-hebrew anniversaries + // b) Hebrew months TVT, SHV, IYR, SVN, TMZ, AAV, ELL + if (!$anniv instanceof JewishDate || in_array($anniv->m, array(1, 5, 6, 9, 10, 11, 12, 13))) { + // Dates without days go on the first day of the month + // Dates with invalid days go on the last day of the month + if ($anniv->d === 1) { + $where .= " AND d_day <= 1"; + } elseif ($anniv->d === $anniv->daysInMonth()) { + $where .= " AND d_day >= :day"; + $args['day'] = $anniv->d; + } else { + $where .= " AND d_day = :day"; + $args['day'] = $anniv->d; + } + $where .= " AND d_mon = :month"; + $args['month'] = $anniv->m; + } else { + // SPECIAL CASES: + switch ($anniv->m) { + case 2: + // 29 CSH does not include 30 CSH (but would include an invalid 31 CSH if there were no 30 CSH) + if ($anniv->d === 1) { + $where .= " AND d_day <= 1 AND d_mon = 2"; + } elseif ($anniv->d === 30) { + $where .= " AND d_day >= 30 AND d_mon = 2"; + } elseif ($anniv->d === 29 && $anniv->daysInMonth() === 29) { + $where .= " AND (d_day = 29 OR d_day > 30) AND d_mon = 2"; + } else { + $where .= " AND d_day = :day AND d_mon = 2"; + $args['day'] = $anniv->d; + } + break; + case 3: + // 1 KSL includes 30 CSH (if this year didn’t have 30 CSH) + // 29 KSL does not include 30 KSL (but would include an invalid 31 KSL if there were no 30 KSL) + if ($anniv->d === 1) { + $tmp = new JewishDate(array($anniv->y, 'CSH', 1)); + if ($tmp->daysInMonth() === 29) { + $where .= " AND (d_day <= 1 AND d_mon = 3 OR d_day = 30 AND d_mon = 2)"; + } else { + $where .= " AND d_day <= 1 AND d_mon = 3"; + } + } elseif ($anniv->d === 30) { + $where .= " AND d_day >= 30 AND d_mon = 3"; + } elseif ($anniv->d == 29 && $anniv->daysInMonth() === 29) { + $where .= " AND (d_day = 29 OR d_day > 30) AND d_mon = 3"; + } else { + $where .= " AND d_day = :day AND d_mon = 3"; + $args['day'] = $anniv->d; + } + break; + case 4: + // 1 TVT includes 30 KSL (if this year didn’t have 30 KSL) + if ($anniv->d === 1) { + $tmp = new JewishDate(array($anniv->y, 'KSL', 1)); + if ($tmp->daysInMonth() === 29) { + $where .= " AND (d_day <=1 AND d_mon = 4 OR d_day = 30 AND d_mon = 3)"; + } else { + $where .= " AND d_day <= 1 AND d_mon = 4"; + } + } elseif ($anniv->d === $anniv->daysInMonth()) { + $where .= " AND d_day >= :day AND d_mon=4"; + $args['day'] = $anniv->d; + } else { + $where .= " AND d_day = :day AND d_mon=4"; + $args['day'] = $anniv->d; + } + break; + case 7: // ADS includes ADR (non-leap) + if ($anniv->d === 1) { + $where .= " AND d_day <= 1"; + } elseif ($anniv->d === $anniv->daysInMonth()) { + $where .= " AND d_day >= :day"; + $args['day'] = $anniv->d; + } else { + $where .= " AND d_day = :day"; + $args['day'] = $anniv->d; + } + $where .= " AND (d_mon = 6 AND MOD(7 * d_year + 1, 19) >= 7 OR d_mon = 7)"; + break; + case 8: // 1 NSN includes 30 ADR, if this year is non-leap + if ($anniv->d === 1) { + if ($anniv->isLeapYear()) { + $where .= " AND d_day <= 1 AND d_mon = 8"; + } else { + $where .= " AND (d_day <= 1 AND d_mon = 8 OR d_day = 30 AND d_mon = 6)"; + } + } elseif ($anniv->d === $anniv->daysInMonth()) { + $where .= " AND d_day >= :day AND d_mon = 8"; + $args['day'] = $anniv->d; + } else { + $where .= " AND d_day = :day AND d_mon = 8"; + $args['day'] = $anniv->d; + } + break; + } + } + // Only events in the past (includes dates without a year) + $where .= " AND d_year <= :year"; + $args['year'] = $anniv->y; + + if ($facts) { + // Restrict to certain types of fact + $where .= " AND d_fact IN ("; + preg_match_all('/([_A-Z]+)/', $facts, $matches); + foreach ($matches[1] as $n => $fact) { + $where .= $n ? ", " : ""; + $where .= ":fact_" . $n; + $args['fact_' . $n] = $fact; + } + $where .= ")"; + } else { + // If no facts specified, get all except these + $where .= " AND d_fact NOT IN ('CHAN', 'BAPL', 'SLGC', 'SLGS', 'ENDL', 'CENS', 'RESI', '_TODO')"; + } + + $order_by = " ORDER BY d_day, d_year DESC"; + + // Now fetch these anniversaries + foreach (array('INDI' => $ind_sql . $where . $order_by, 'FAM' => $fam_sql . $where . $order_by) as $type => $sql) { + $rows = Database::prepare($sql)->execute($args)->fetchAll(); + foreach ($rows as $row) { + if ($type === 'INDI') { + $record = Individual::getInstance($row->xref, $tree, $row->gedcom); + } else { + $record = Family::getInstance($row->xref, $tree, $row->gedcom); + } + $anniv_date = new Date($row->d_type . ' ' . $row->d_day . ' ' . $row->d_month . ' ' . $row->d_year); + foreach ($record->getFacts() as $fact) { + if (($fact->getDate()->minimumDate() == $anniv_date->minimumDate() || $fact->getDate()->maximumDate() == $anniv_date->minimumDate()) && $fact->getTag() === $row->d_fact) { + $fact->anniv = $row->d_year === 0 ? 0 : $anniv->y - $row->d_year; + $found_facts[] = $fact; + } + } + } + } + } + + return $found_facts; + } + + /** + * Get a list of events which occured during a given date range. + * + * @param int $jd1 the start range of julian day + * @param int $jd2 the end range of julian day + * @param string $facts restrict the search to just these facts or leave blank for all + * @param Tree $tree the tree to search + * + * @return Fact[] + */ + public static function getCalendarEvents($jd1, $jd2, $facts, Tree $tree) { + // If no facts specified, get all except these + $skipfacts = "CHAN,BAPL,SLGC,SLGS,ENDL,CENS,RESI,NOTE,ADDR,OBJE,SOUR,PAGE,DATA,TEXT"; + if ($facts != '_TODO') { + $skipfacts .= ',_TODO'; + } + + $found_facts = array(); + + // Events that start or end during the period + $where = "WHERE (d_julianday1>={$jd1} AND d_julianday1<={$jd2} OR d_julianday2>={$jd1} AND d_julianday2<={$jd2})"; + + // Restrict to certain types of fact + if (empty($facts)) { + $excl_facts = "'" . preg_replace('/\W+/', "','", $skipfacts) . "'"; + $where .= " AND d_fact NOT IN ({$excl_facts})"; + } else { + $incl_facts = "'" . preg_replace('/\W+/', "','", $facts) . "'"; + $where .= " AND d_fact IN ({$incl_facts})"; + } + // Only get events from the current gedcom + $where .= " AND d_file=" . $tree->getTreeId(); + + // Now fetch these events + $ind_sql = "SELECT d_gid AS xref, i_gedcom AS gedcom, d_type, d_day, d_month, d_year, d_fact, d_type FROM `##dates`, `##individuals` {$where} AND d_gid=i_id AND d_file=i_file GROUP BY d_julianday1, d_gid ORDER BY d_julianday1"; + $fam_sql = "SELECT d_gid AS xref, f_gedcom AS gedcom, d_type, d_day, d_month, d_year, d_fact, d_type FROM `##dates`, `##families` {$where} AND d_gid=f_id AND d_file=f_file GROUP BY d_julianday1, d_gid ORDER BY d_julianday1"; + foreach (array('INDI' => $ind_sql, 'FAM' => $fam_sql) as $type => $sql) { + $rows = Database::prepare($sql)->fetchAll(); + foreach ($rows as $row) { + if ($type === 'INDI') { + $record = Individual::getInstance($row->xref, $tree, $row->gedcom); + } else { + $record = Family::getInstance($row->xref, $tree, $row->gedcom); + } + $anniv_date = new Date($row->d_type . ' ' . $row->d_day . ' ' . $row->d_month . ' ' . $row->d_year); + foreach ($record->getFacts() as $fact) { + if (($fact->getDate()->minimumDate() == $anniv_date->minimumDate() || $fact->getDate()->maximumDate() == $anniv_date->minimumDate()) && $fact->getTag() === $row->d_fact) { + $fact->anniv = 0; + $found_facts[] = $fact; + } + } + } + } + + return $found_facts; + } + + /** + * Get the list of current and upcoming events, sorted by anniversary date + * + * @param int $jd1 + * @param int $jd2 + * @param string $events + * @param Tree $tree + * + * @return Fact[] + */ + public static function getEventsList($jd1, $jd2, $events, Tree $tree) { + $found_facts = array(); + for ($jd = $jd1; $jd <= $jd2; ++$jd) { + $found_facts = array_merge($found_facts, self::getAnniversaryEvents($jd, $events, $tree)); + } + + return $found_facts; + } + + /** + * Check if a media file is shared (i.e. used by another gedcom) + * + * @param string $file_name + * @param int $ged_id + * + * @return bool + */ + public static function isMediaUsedInOtherTree($file_name, $ged_id) { + return + (bool) Database::prepare("SELECT COUNT(*) FROM `##media` WHERE m_filename LIKE ? AND m_file<>?") + ->execute(array("%{$file_name}", $ged_id)) + ->fetchOne(); + } + + /** + * @param int $user_id + * + * @return string[][] + */ + public static function getUserBlocks($user_id) { + global $WT_TREE; + + $blocks = array('main' => array(), 'side' => array()); + $rows = Database::prepare( + "SELECT SQL_CACHE location, block_id, module_name" . + " FROM `##block`" . + " JOIN `##module` USING (module_name)" . + " JOIN `##module_privacy` USING (module_name)" . + " WHERE user_id=?" . + " AND status='enabled'" . + " AND `##module_privacy`.gedcom_id=?" . + " AND access_level>=?" . + " ORDER BY location, block_order" + )->execute(array($user_id, $WT_TREE->getTreeId(), Auth::accessLevel($WT_TREE)))->fetchAll(); + foreach ($rows as $row) { + $blocks[$row->location][$row->block_id] = $row->module_name; + } + + return $blocks; + } + + /** + * Get the blocks for the specified tree + * + * @param int $gedcom_id + * + * @return string[][] + */ + public static function getTreeBlocks($gedcom_id) { + if ($gedcom_id < 0) { + $access_level = Auth::PRIV_NONE; + } else { + $access_level = Auth::accessLevel(Tree::findById($gedcom_id)); + } + + $blocks = array('main' => array(), 'side' => array()); + $rows = Database::prepare( + "SELECT SQL_CACHE location, block_id, module_name" . + " FROM `##block`" . + " JOIN `##module` USING (module_name)" . + " JOIN `##module_privacy` USING (module_name, gedcom_id)" . + " WHERE gedcom_id = :tree_id" . + " AND status='enabled'" . + " AND access_level >= :access_level" . + " ORDER BY location, block_order" + )->execute(array( + 'tree_id' => $gedcom_id, + 'access_level' => $access_level, + ))->fetchAll(); + foreach ($rows as $row) { + $blocks[$row->location][$row->block_id] = $row->module_name; + } + + return $blocks; + } + + /** + * 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(); + } +} |