Refactor classes to use namespaces, as per PSR-4. Replace GPL2 with GPL3.

2 files changed, 641 insertions, 0 deletions

diff --git a/app/Query/Media.php b/app/Query/Media.php
new file mode 100644
index 0000000000..cd1e41fb95
--- /dev/null
+++ b/app/Query/Media.php
@@ -0,0 +1,143 @@
+<?php
+namespace Webtrees;
+
+/**
+ * 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/>.
+ */
+
+/**
+ * Class WT_Query_Media - generate lists of files for admin_media.php
+ *
+ * @package   webtrees
+ * @copyright (c) 2014 webtrees development team
+ * @license   http://www.gnu.org/licenses/gpl-2.0.html GNU/GPLv2
+ */
+class WT_Query_Media {
+	/**
+	 * Generate a list of all the folders in the current tree - for the media list.
+	 *
+	 * @return string[]
+	 */
+	public static function folderList() {
+		$folders = Database::prepare(
+			"SELECT SQL_CACHE LEFT(m_filename, CHAR_LENGTH(m_filename) - CHAR_LENGTH(SUBSTRING_INDEX(m_filename, '/', -1))) AS media_path" .
+			" FROM  `##media`" .
+			" WHERE m_file = ?" .
+			" AND   m_filename NOT LIKE 'http://%'" .
+			" AND   m_filename NOT LIKE 'https://%'" .
+			" GROUP BY 1" .
+			" ORDER BY 1"
+		)->execute(array(WT_GED_ID))->fetchOneColumn();
+
+		if (!$folders || reset($folders) != '') {
+			array_unshift($folders, '');
+		}
+
+		return array_combine($folders, $folders);
+	}
+
+	/**
+	 * Generate a list of all folders from all the trees - for the media admin.
+	 *
+	 * @return array
+	 */
+	public static function folderListAll() {
+		$folders = Database::prepare(
+			"SELECT SQL_CACHE LEFT(m_filename, CHAR_LENGTH(m_filename) - CHAR_LENGTH(SUBSTRING_INDEX(m_filename, '/', -1))) AS media_path" .
+			" FROM  `##media`" .
+			" WHERE m_filename NOT LIKE 'http://%'" .
+			" AND   m_filename NOT LIKE 'https://%'" .
+			" GROUP BY 1" .
+			" ORDER BY 1"
+		)->execute()->fetchOneColumn();
+
+		if ($folders) {
+			return array_combine($folders, $folders);
+		} else {
+			return array();
+		}
+	}
+
+	/**
+	 * Generate a filtered, sourced, privacy-checked list of media objects - for the media list.
+	 *
+	 * @param string $folder     folder to search
+	 * @param string $subfolders either "include" or "exclude"
+	 * @param string $sort       either "file" or "title"
+	 * @param string $filter     optional search string
+	 *
+	 * @return Media[]
+	 * @throws Exception
+	 */
+	public static function mediaList($folder, $subfolders, $sort, $filter) {
+		// All files in the folder, plus external files
+		$sql =
+			"SELECT m_id AS xref, m_file AS gedcom_id, m_gedcom AS gedcom" .
+			" FROM `##media`" .
+			" WHERE m_file=?";
+		$args = array(
+			WT_GED_ID,
+		);
+
+		// Only show external files when we are looking at the root folder
+		if ($folder == '') {
+			$sql_external = " OR m_filename LIKE 'http://%' OR m_filename LIKE 'https://%'";
+		} else {
+			$sql_external = "";
+		}
+
+		// Include / exclude subfolders (but always include external)
+		switch ($subfolders) {
+		case 'include':
+			$sql .= " AND (m_filename LIKE CONCAT(?, '%') $sql_external)";
+			$args[] = Filter::escapeLike($folder);
+			break;
+		case 'exclude':
+			$sql .= " AND (m_filename LIKE CONCAT(?, '%')  AND m_filename NOT LIKE CONCAT(?, '%/%') $sql_external)";
+			$args[] = Filter::escapeLike($folder);
+			$args[] = Filter::escapeLike($folder);
+			break;
+		default:
+			throw new \Exception('Bad argument (subfolders=' . $subfolders . ') in WT_Query_Media::mediaList()');
+		}
+
+		// Apply search terms
+		if ($filter) {
+			$sql .= " AND (SUBSTRING_INDEX(m_filename, '/', -1) LIKE CONCAT('%', ?, '%') OR m_titl LIKE CONCAT('%', ?, '%'))";
+			$args[] = Filter::escapeLike($filter);
+			$args[] = Filter::escapeLike($filter);
+		}
+
+		switch ($sort) {
+		case 'file':
+			$sql .= " ORDER BY m_filename";
+			break;
+		case 'title':
+			$sql .= " ORDER BY m_titl";
+			break;
+		default:
+			throw new \Exception('Bad argument (sort=' . $sort . ') in WT_Query_Media::mediaList()');
+		}
+
+		$rows = Database::prepare($sql)->execute($args)->fetchAll();
+		$list = array();
+		foreach ($rows as $row) {
+			$media = Media::getInstance($row->xref, $row->gedcom_id, $row->gedcom);
+			if ($media->canShow()) {
+				$list[] = $media;
+			}
+		}
+		return $list;
+	}
+}
diff --git a/app/Query/Name.php b/app/Query/Name.php
new file mode 100644
index 0000000000..e07adde918
--- /dev/null
+++ b/app/Query/Name.php
@@ -0,0 +1,498 @@
+<?php
+namespace Webtrees;
+
+/**
+ * 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/>.
+ */
+
+/**
+ * Class WT_Query_Name - generate lists for indilist.php and famlist.php
+ *
+ * @package   webtrees
+ * @copyright (c) 2014 webtrees development team
+ * @license   http://www.gnu.org/licenses/gpl-2.0.html GNU/GPLv2
+ */
+class WT_Query_Name {
+	/**
+	 * Get a list of initial letters, for lists of names.
+	 *
+	 * @param string $locale Return the alphabet for this locale
+	 *
+	 * @return string[]
+	 */
+	private static function getAlphabetForLocale($locale) {
+		switch ($locale) {
+		case 'ar':
+			return array(
+				'ا', 'ب', 'ت', 'ث', 'ج', 'ح', 'خ', 'د', 'ذ', 'ر', 'ز', 'س', 'ش', 'ص', 'ض', 'ط', 'ظ', 'ع', 'غ', 'ف', 'ق', 'ك', 'ل', 'م', 'ن', 'ه', 'و', 'ي', 'آ', 'ة', 'ى', 'ی'
+			);
+		case 'cs':
+			return array(
+				'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'CH', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'
+			);
+		case 'da':
+		case 'nb':
+		case 'nn':
+			return array(
+				'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'Æ', 'Ø', 'Å'
+			);
+		case 'el':
+			return array(
+				'Α', 'Β', 'Γ', 'Δ', 'Ε', 'Ζ', 'Η', 'Θ', 'Ι', 'Κ', 'Λ', 'Μ', 'Ν', 'Ξ', 'Ο', 'Π', 'Ρ', 'Σ', 'Τ', 'Υ', 'Φ', 'Χ', 'Ψ', 'Ω'
+			);
+		case 'es':
+			return array(
+				'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'Ñ', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'
+			);
+		case 'et':
+			return array(
+				'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'Š', 'Z', 'Ž', 'T', 'U', 'V', 'W', 'Õ', 'Ä', 'Ö', 'Ü', 'X', 'Y'
+			);
+		case 'fi':
+		case 'sv':
+			return array(
+				'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'Å', 'Ä', 'Ö'
+			);
+		case 'he':
+			return array(
+				'א', 'ב', 'ג', 'ד', 'ה', 'ו', 'ז', 'ח', 'ט', 'י', 'כ', 'ל', 'מ', 'נ', 'ס', 'ע', 'פ', 'צ', 'ק', 'ר', 'ש', 'ת'
+			);
+		case 'hu':
+			return array(
+				'A', 'B', 'C', 'CS', 'D', 'DZ', 'DZS', 'E', 'F', 'G', 'GY', 'H', 'I', 'J', 'K', 'L', 'LY', 'M', 'N', 'NY', 'O', 'Ö', 'P', 'Q', 'R', 'S', 'SZ', 'T', 'TY', 'U', 'Ü', 'V', 'W', 'X', 'Y', 'Z', 'ZS'
+			);
+		case 'lt':
+			return array(
+				'A', 'Ą', 'B', 'C', 'Č', 'D', 'E', 'Ę', 'Ė', 'F', 'G', 'H', 'I', 'Y', 'Į', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'R', 'S', 'Š', 'T', 'U', 'Ų', 'Ū', 'V', 'Z', 'Ž'
+			);
+		case 'nl':
+			return array(
+				'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'IJ'
+			);
+		case 'pl':
+			return array(
+				'A', 'B', 'C', 'Ć', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'Ł', 'M', 'N', 'O', 'Ó', 'P', 'Q', 'R', 'S', 'Ś', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'Ź', 'Ż'
+			);
+		case 'ro':
+			return array(
+				'A', 'Ă', 'Â', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'Î', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'Ş', 'T', 'Ţ', 'U', 'V', 'W', 'X', 'Y', 'Z'
+			);
+		case 'ru':
+			return array(
+				'А', 'Б', 'В', 'Г', 'Д', 'Е', 'Ё', 'Ж', 'З', 'И', 'Й', 'К', 'Л', 'М', 'Н', 'О', 'П', 'Р', 'С', 'Т', 'У', 'Ф', 'Х', 'Ц', 'Ч', 'Ш', 'Щ', 'Ъ', 'Ы', 'Ь', 'Э', 'Ю', 'Я'
+			);
+		case 'sk':
+			return array(
+				'A', 'Á', 'Ä', 'B', 'C', 'Č', 'D', 'Ď', 'E', 'É', 'F', 'G', 'H', 'I', 'Í', 'J', 'K', 'L', 'Ľ', 'Ĺ', 'M', 'N', 'Ň', 'O', 'Ó', 'Ô', 'P', 'Q', 'R', 'Ŕ', 'S', 'Š', 'T', 'Ť', 'U', 'Ú', 'V', 'W', 'X', 'Y', 'Ý', 'Z', 'Ž'
+			);
+		case 'sl':
+			return array(
+				'A', 'B', 'C', 'Č', 'Ć', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'Š', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'Ž'
+			);
+		case 'sr':
+			return array(
+				'A', 'B', 'C', 'Č', 'Ć', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'Š', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'Ž'
+			);
+		case 'tr':
+			return array(
+				'A', 'B', 'C', 'Ç', 'D', 'E', 'F', 'G', 'Ğ', 'H', 'I', 'İ', 'J', 'K', 'L', 'M', 'N', 'O', 'Ö', 'P', 'R', 'S', 'Ş', 'T', 'U', 'Ü', 'V', 'Y', 'Z'
+			);
+		default:
+			return array(
+				'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'
+			);
+		}
+	}
+
+	/**
+	 * Get the initial letter of a name, taking care of multi-letter sequences and equivalences.
+	 *
+	 * @param string $name
+	 *
+	 * @return string
+	 */
+	static public function initialLetter($name) {
+		$name = I18N::strtoupper($name);
+		switch (WT_LOCALE) {
+		case 'cs':
+			if (substr($name, 0, 2) == 'CH') {
+				return 'CH';
+			}
+			break;
+		case 'da':
+		case 'nb':
+		case 'nn':
+			if (substr($name, 0, 2) == 'AA') {
+				return 'Å';
+			}
+			break;
+		case 'hu':
+			if (substr($name, 0, 2) == 'CS') {
+				return 'CS';
+			} elseif (substr($name, 0, 3) == 'DZS') {
+				return 'DZS';
+			} elseif (substr($name, 0, 2) == 'DZ') {
+				return 'DZ';
+			} elseif (substr($name, 0, 2) == 'GY') {
+				return 'GY';
+			} elseif (substr($name, 0, 2) == 'LY') {
+				return 'LY';
+			} elseif (substr($name, 0, 2) == 'NY') {
+				return 'NY';
+			} elseif (substr($name, 0, 2) == 'SZ') {
+				return 'SZ';
+			} elseif (substr($name, 0, 2) == 'TY') {
+				return 'TY';
+			} elseif (substr($name, 0, 2) == 'ZS') {
+				return 'ZS';
+			}
+			break;
+		case 'nl':
+			if (substr($name, 0, 2) == 'IJ') {
+				return 'IJ';
+			}
+			break;
+		}
+		// No special rules - just take the first character
+		return mb_substr($name, 0, 1);
+	}
+
+	/**
+	 * Generate SQL to match a given letter, taking care of cases that
+	 * are not covered by the collation setting.
+	 *
+	 * We must consider:
+	 * potential substrings, such as Czech "CH" and "C"
+	 * equivalent letters, such as Danish "AA" and "Å"
+	 *
+	 * We COULD write something that handles all languages generically,
+	 * but its performance would most likely be poor.
+	 *
+	 * For languages that don't appear in this list, we could write
+	 * simpler versions of the surnameAlpha() and givenAlpha() functions,
+	 * but it gives no noticable improvement in performance.
+	 *
+	 * @param string $field
+	 * @param string $letter
+	 *
+	 * @return string
+	 */
+	static private function getInitialSql($field, $letter) {
+		switch (WT_LOCALE) {
+		case 'cs':
+			switch ($letter) {
+			case 'C': return $field . " LIKE 'C%' COLLATE " . I18N::$collation . " AND " . $field . " NOT LIKE 'CH%' COLLATE " . I18N::$collation;
+			}
+			break;
+		case 'da':
+		case 'nb':
+		case 'nn':
+			switch ($letter) {
+			// AA gets listed under Å
+			case 'A': return $field . " LIKE 'A%' COLLATE " . I18N::$collation . " AND " . $field . " NOT LIKE 'AA%' COLLATE " . I18N::$collation;
+			case 'Å': return "(" . $field . " LIKE 'Å%' COLLATE " . I18N::$collation . " OR " . $field . " LIKE 'AA%' COLLATE " . I18N::$collation . ")";
+			}
+			break;
+		case 'hu':
+			switch ($letter) {
+			case 'C':  return $field . " LIKE 'C%' COLLATE " . I18N::$collation . " AND " . $field . " NOT LIKE 'CS%' COLLATE " . I18N::$collation;
+			case 'D':  return $field . " LIKE 'D%' COLLATE " . I18N::$collation . " AND " . $field . " NOT LIKE 'DZ%' COLLATE " . I18N::$collation;
+			case 'DZ': return $field . " LIKE 'DZ%' COLLATE " . I18N::$collation . " AND " . $field . " NOT LIKE 'DZS%' COLLATE " . I18N::$collation;
+			case 'G':  return $field . " LIKE 'G%' COLLATE " . I18N::$collation . " AND " . $field . " NOT LIKE 'GY%' COLLATE " . I18N::$collation;
+			case 'L':  return $field . " LIKE 'L%' COLLATE " . I18N::$collation . " AND " . $field . " NOT LIKE 'LY%' COLLATE " . I18N::$collation;
+			case 'N':  return $field . " LIKE 'N%' COLLATE " . I18N::$collation . " AND " . $field . " NOT LIKE 'NY%' COLLATE " . I18N::$collation;
+			case 'S':  return $field . " LIKE 'S%' COLLATE " . I18N::$collation . " AND " . $field . " NOT LIKE 'SZ%' COLLATE " . I18N::$collation;
+			case 'T':  return $field . " LIKE 'T%' COLLATE " . I18N::$collation . " AND " . $field . " NOT LIKE 'TY%' COLLATE " . I18N::$collation;
+			case 'Z':  return $field . " LIKE 'Z%' COLLATE " . I18N::$collation . " AND " . $field . " NOT LIKE 'ZS%' COLLATE " . I18N::$collation;
+			}
+			break;
+		case 'nl':
+			switch ($letter) {
+			case 'I': return $field . " LIKE 'I%' COLLATE " . I18N::$collation . " AND " . $field . " NOT LIKE 'IJ%' COLLATE " . I18N::$collation;
+			}
+			break;
+		}
+		// Easy cases: the MySQL collation rules take care of it
+		return "$field LIKE CONCAT('@'," . Database::quote($letter) . ",'%') COLLATE " . I18N::$collation . " ESCAPE '@'";
+	}
+
+	/**
+	 * Get a list of initial surname letters for indilist.php and famlist.php
+	 *
+	 * @param boolean $marnm   if set, include married names
+	 * @param boolean $fams    if set, only consider individuals with FAMS records
+	 * @param integer $ged_id  only consider individuals from this tree
+	 * @param boolean $totals  if set, count the number of names beginning with each letter
+	 *
+	 * @return integer[]
+	 */
+	public static function surnameAlpha($marnm, $fams, $ged_id, $totals = true) {
+		$alphas = array();
+
+		$sql =
+			"SELECT SQL_CACHE COUNT(n_id)" .
+			" FROM `##name` " .
+			($fams ? " JOIN `##link` ON (n_id=l_from AND n_file=l_file AND l_type='FAMS') " : "") .
+			" WHERE n_file={$ged_id}" .
+			($marnm ? "" : " AND n_type!='_MARNM'");
+
+		// Fetch all the letters in our alphabet, whether or not there
+		// are any names beginning with that letter.  It looks better to
+		// show the full alphabet, rather than omitting rare letters such as X
+		foreach (self::getAlphabetForLocale(WT_LOCALE) as $letter) {
+			$count = 1;
+			if ($totals) {
+				$count = Database::prepare($sql . " AND " . self::getInitialSql('n_surn', $letter))->fetchOne();
+			}
+			$alphas[$letter] = $count;
+		}
+
+		// Now fetch initial letters that are not in our alphabet,
+		// including "@" (for "@N.N.") and "" for no surname.
+		$sql =
+			"SELECT SQL_CACHE UPPER(LEFT(n_surn, 1)), COUNT(n_id)" .
+			" FROM `##name` " .
+			($fams ? " JOIN `##link` ON (n_id=l_from AND n_file=l_file AND l_type='FAMS') " : "") .
+			" WHERE n_file={$ged_id} AND n_surn<>''" .
+			($marnm ? "" : " AND n_type!='_MARNM'");
+
+		foreach (self::getAlphabetForLocale(WT_LOCALE) as $letter) {
+			$sql .= " AND n_surn NOT LIKE '" . $letter . "%' COLLATE " . I18N::$collation;
+		}
+		$sql .= " GROUP BY LEFT(n_surn, 1) ORDER BY LEFT(n_surn, 1)='', LEFT(n_surn, 1)='@', LEFT(n_surn, 1)";
+		foreach (Database::prepare($sql)->fetchAssoc() as $alpha=>$count) {
+			$alphas[$alpha] = $count;
+		}
+
+		// Names with no surname
+		$sql =
+			"SELECT SQL_CACHE COUNT(n_id)" .
+			" FROM `##name` " .
+			($fams ? " JOIN `##link` ON (n_id=l_from AND n_file=l_file AND l_type='FAMS') " : "") .
+			" WHERE n_file={$ged_id} AND n_surn=''" .
+			($marnm ? "" : " AND n_type!='_MARNM'");
+		$num_none = Database::prepare($sql)->fetchOne();
+		if ($num_none) {
+			// Special code to indicate "no surname"
+			$alphas[','] = $num_none;
+		}
+
+		return $alphas;
+	}
+
+	/**
+	 * Get a list of initial given name letters for indilist.php and famlist.php
+	 *
+	 * @param string  $surn   if set, only consider people with this surname
+	 * @param string  $salpha if set, only consider surnames starting with this letter
+	 * @param boolean $marnm  if set, include married names
+	 * @param boolean $fams   if set, only consider individuals with FAMS records
+	 * @param integer $ged_id only consider individuals from this tree
+	 *
+	 * @return integer[]
+	 */
+	public static function givenAlpha($surn, $salpha, $marnm, $fams, $ged_id) {
+		$alphas = array();
+
+		$sql =
+			"SELECT SQL_CACHE COUNT(DISTINCT n_id)" .
+			" FROM `##name`" .
+			($fams ? " JOIN `##link` ON (n_id=l_from AND n_file=l_file AND l_type='FAMS') " : "") .
+			" WHERE n_file={$ged_id} " .
+			($marnm ? "" : " AND n_type!='_MARNM'");
+
+		if ($surn) {
+			$sql .= " AND n_surn=" . Database::quote($surn) . " COLLATE '" . I18N::$collation . "'";
+		} 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.')";
+		}
+
+		// Fetch all the letters in our alphabet, whether or not there
+		// are any names beginning with that letter.  It looks better to
+		// show the full alphabet, rather than omitting rare letters such as X
+		foreach (self::getAlphabetForLocale(WT_LOCALE) as $letter) {
+			$count = Database::prepare($sql . " AND " . self::getInitialSql('n_givn', $letter))->fetchOne();
+			$alphas[$letter] = $count;
+		}
+
+		// Now fetch initial letters that are not in our alphabet,
+		// including "@" (for "@N.N.") and "" for no surname
+		$sql =
+			"SELECT SQL_CACHE UPPER(LEFT(n_givn, 1)), COUNT(DISTINCT n_id)" .
+			" FROM `##name` " .
+			($fams ? " JOIN `##link` ON (n_id=l_from AND n_file=l_file AND l_type='FAMS') " : "") .
+			" WHERE n_file={$ged_id} " .
+			($marnm ? "" : " AND n_type!='_MARNM'");
+
+		if ($surn) {
+			$sql .= " AND n_surn=" . Database::quote($surn) . " COLLATE '" . I18N::$collation . "'";
+		} 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.')";
+		}
+
+		foreach (self::getAlphabetForLocale(WT_LOCALE) as $letter) {
+			$sql .= " AND n_givn NOT LIKE '" . $letter . "%' COLLATE " . I18N::$collation;
+		}
+		$sql .= " GROUP BY LEFT(n_givn, 1) ORDER BY LEFT(n_givn, 1)='@', LEFT(n_givn, 1)='', LEFT(n_givn, 1)";
+		foreach (Database::prepare($sql)->fetchAssoc() as $alpha=>$count) {
+			$alphas[$alpha] = $count;
+		}
+
+		return $alphas;
+	}
+
+	/**
+	 * Get a list of actual surnames and variants, based on a "root" surname.
+	 *
+	 * @param string  $surn   if set, only fetch people with this surname
+	 * @param string  $salpha if set, only consider surnames starting with this letter
+	 * @param boolean $marnm  if set, include married names
+	 * @param boolean $fams   if set, only consider individuals with FAMS records
+	 * @param integer $ged_id only consider individuals from this gedcom
+	 *
+	 * @return array
+	 */
+	public static function surnames($surn, $salpha, $marnm, $fams, $ged_id) {
+		$sql =
+			"SELECT SQL_CACHE n2.n_surn, n1.n_surname, n1.n_id" .
+			" FROM `##name` n1 " .
+			($fams ? " JOIN `##link` ON (n_id=l_from AND n_file=l_file AND l_type='FAMS') " : "") .
+			" JOIN (SELECT n_surn, n_file FROM `##name`" .
+			" WHERE n_file={$ged_id}" .
+			($marnm ? "" : " AND n_type!='_MARNM'");
+
+		if ($surn) {
+			$sql .= " AND n_surn COLLATE '" . I18N::$collation . "' =" . Database::quote($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.')";
+		}
+		$sql .= " GROUP BY n_surn COLLATE '" . I18N::$collation . "', n_file) n2 ON (n1.n_surn=n2.n_surn COLLATE '" . I18N::$collation . "' AND n1.n_file=n2.n_file)";
+		if (!$marnm) {
+			$sql .= " AND n_type!='_MARNM'";
+		}
+
+		$list = array();
+		foreach (Database::prepare($sql)->fetchAll() as $row) {
+			$list[I18N::strtoupper($row->n_surn)][$row->n_surname][$row->n_id] = true;
+		}
+		return $list;
+	}
+
+	/**
+	 * 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 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 boolean $marnm  if set, include married names
+	 * @param boolean $fams   if set, only fetch individuals with FAMS records
+	 * @param integer $ged_id if set, only fetch individuals from this gedcom
+	 *
+	 * @return Individual[]
+	 */
+	public static function individuals($surn, $salpha, $galpha, $marnm, $fams, $ged_id) {
+		$sql =
+			"SELECT i_id AS xref, i_file AS gedcom_id, 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={$ged_id} " .
+			($marnm ? "" : "AND n_type!='_MARNM'");
+
+		if ($surn) {
+			$sql .= " AND n_surn COLLATE '" . I18N::$collation . "'=" . Database::quote($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 '" . I18N::$collation . "', CASE n_givn WHEN '@P.N.' THEN 1 ELSE 0 END, n_givn COLLATE '" . I18N::$collation . "'";
+
+		$list = array();
+		$rows = Database::prepare($sql)->fetchAll();
+		foreach ($rows as $row) {
+			$person = Individual::getInstance($row->xref, $row->gedcom_id, $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;
+	}
+
+	/**
+	 * Fetch a list of families 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 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 boolean $marnm  if set, include married names
+	 * @param integer $ged_id if set, only fetch individuals from this gedcom
+	 *
+	 * @return Family[]
+	 */
+	public static function families($surn, $salpha, $galpha, $marnm, $ged_id) {
+		$list = array();
+		foreach (self::individuals($surn, $salpha, $galpha, $marnm, true, $ged_id) as $indi) {
+			foreach ($indi->getSpouseFamilies() as $family) {
+				$list[$family->getXref()] = $family;
+			}
+		}
+		usort($list, 'Webtrees\GedcomRecord::compare');
+		return $list;
+	}
+}