Fisharebest\Webtrees\Query\QueryName::givenAlpha()

Complexity

Conditions	16
Paths	3200

Size

Total Lines	69
Code Lines	46

Duplication

Lines	33
Ratio	47.83 %

Importance

Changes

0

<?php
	public static function givenAlpha(Tree $tree, $surn, $salpha, $marnm, $fams) {
		$alphas = [];

		$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() . "'";

The function Fisharebest\Webtrees\Database::quote() has been deprecated: We should use bind-variables instead.

		} 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 = [
			'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;
	}