/**
* Get a list of initial given name letters for indilist.php and famlist.php
*
* @param Tree $tree Find names in this tree
* @param string $surn if set, only consider people with this surname
* @param string $salpha if set, only consider surnames starting with this letter
* @param bool $marnm if set, include married names
* @param bool $fams if set, only consider individuals with FAMS records
*
* @return int[]
*/
public static function givenAlpha(Tree $tree, $surn, $salpha, $marnm, $fams)
{
$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=" . $tree->getTreeId() . " " . ($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 initial, total FROM (SELECT UPPER(LEFT(n_givn, 1)) AS initial, COUNT(DISTINCT n_id) AS total" . " FROM `##name` " . ($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.')";
}
foreach (self::getAlphabetForLocale(WT_LOCALE) as $letter) {
$sql .= " AND n_givn NOT LIKE '" . $letter . "%' COLLATE " . I18N::collation();
}
$sql .= " GROUP BY UPPER(LEFT(n_givn, 1))) AS subquery ORDER BY initial = '@', initial = '', initial";
foreach (Database::prepare($sql)->execute($args)->fetchAssoc() as $alpha => $count) {
$alphas[$alpha] = $count;
}
return $alphas;
}