app/Services/IndividualListService.php
public function givenAlpha(string $surn, string $salpha, bool $marnm, bool $fams, string $locale, string $collation): array
    {
        $alphas = [];

        $query = DB::table('name')
            ->where('n_file', '=', $this->tree->id());

        $this->whereFamily($fams, $query);
        $this->whereMarriedName($marnm, $query);

        if ($surn !== '') {
            $n_surn = $this->fieldWithCollation('n_surn', $collation);
            $query->where($n_surn, '=', $surn);
        } elseif ($salpha === ',') {
            $query->where('n_surn', '=', '');
        } elseif ($salpha === '@') {
            $query->where('n_surn', '=', '@N.N.');
        } elseif ($salpha !== '') {
            $this->whereInitial($query, 'n_surn', $salpha, $locale, $collation);
        } else {
            // All surnames
            $query->whereNotIn('n_surn', ['', '@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 ($this->localization_service->alphabet() as $letter) {
            $query2 = clone $query;

            $this->whereInitial($query2, 'n_givn', $letter, $locale, $collation);

            $alphas[$letter] = (int) $query2->distinct()->count('n_id');
        }

        $rows = $query
            ->select([DB::raw('UPPER(SUBSTR(n_givn, 1, 1)) AS initial'), DB::raw('COUNT(*) AS count')])
            ->groupBy('initial')
            ->orderBy(DB::raw("CASE initial WHEN '' THEN 1 ELSE 0 END"))
            ->orderBy(DB::raw("CASE initial WHEN '@' THEN 1 ELSE 0 END"))
            ->orderBy('initial')
            ->pluck('count', 'initial');

        foreach ($rows as $alpha => $count) {
            $alphas[$alpha] = (int) $count;
        }

        return $alphas;
    }
public function givenAlpha(string $surn, string $salpha, bool $marnm, bool $fams, string $locale, string $collation): array
    {
        $alphas = [];

        $query = DB::table('name')
            ->where('n_file', '=', $this->tree->id());

        $this->whereFamily($fams, $query);
        $this->whereMarriedName($marnm, $query);

        if ($surn !== '') {
            $n_surn = $this->fieldWithCollation('n_surn', $collation);
            $query->where($n_surn, '=', $surn);
        } elseif ($salpha === ',') {
            $query->where('n_surn', '=', '');
        } elseif ($salpha === '@') {
            $query->where('n_surn', '=', '@N.N.');
        } elseif ($salpha !== '') {
            $this->whereInitial($query, 'n_surn', $salpha, $locale, $collation);
        } else {
            // All surnames
            $query->whereNotIn('n_surn', ['', '@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 ($this->localization_service->alphabet() as $letter) {
            $query2 = clone $query;

            $this->whereInitial($query2, 'n_givn', $letter, $locale, $collation);

            $alphas[$letter] = (int) $query2->distinct()->count('n_id');
        }

        $rows = $query
            ->select([DB::raw('UPPER(SUBSTR(n_givn, 1, 1)) AS initial'), DB::raw('COUNT(*) AS count')])
            ->groupBy('initial')
            ->orderBy(DB::raw("CASE initial WHEN '' THEN 1 ELSE 0 END"))
            ->orderBy(DB::raw("CASE initial WHEN '@' THEN 1 ELSE 0 END"))
            ->orderBy('initial')
            ->pluck('count', 'initial');

        foreach ($rows as $alpha => $count) {
            $alphas[$alpha] = (int) $count;
        }

        return $alphas;
    }
public function givenAlpha ( string $ surn , string $ salpha , bool $ marnm , bool $ fams , string $ locale , string $ collation ) : array { $ alphas = [ ] ; $ query = DB :: table ( 'name' ) -> where ( 'n_file' , '=' , $ this -> tree -> id ( ) ) ; $ this -> whereFamily ( $ fams , $ query ) ; $ this -> whereMarriedName ( $ marnm , $ query ) ; if ( $ surn !== '' ) { $ n_surn = $ this -> fieldWithCollation ( 'n_surn' , $ collation ) ; $ query -> where ( $ n_surn , '=' , $ surn ) ; } elseif ( $ salpha === ',' ) { $ query -> where ( 'n_surn' , '=' , '' ) ; } elseif ( $ salpha === '@' ) { $ query -> where ( 'n_surn' , '=' , '@N.N.' ) ; } elseif ( $ salpha !== '' ) { $ this -> whereInitial ( $ query , 'n_surn' , $ salpha , $ locale , $ collation ) ; } else { // All surnames $ query -> whereNotIn ( 'n_surn' , [ '' , '@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 ( $ this -> localization_service -> alphabet ( ) as $ letter ) { $ query2 = clone $ query ; $ this -> whereInitial ( $ query2 , 'n_givn' , $ letter , $ locale , $ collation ) ; $ alphas [ $ letter ] = ( int ) $ query2 -> distinct ( ) -> count ( 'n_id' ) ; } $ rows = $ query -> select ( [ DB :: raw ( 'UPPER(SUBSTR(n_givn, 1, 1)) AS initial' ) , DB :: raw ( 'COUNT(*) AS count' ) ] ) -> groupBy ( 'initial' ) -> orderBy ( DB :: raw ( "CASE initial WHEN '' THEN 1 ELSE 0 END" ) ) -> orderBy ( DB :: raw ( "CASE initial WHEN '@' THEN 1 ELSE 0 END" ) ) -> orderBy ( 'initial' ) -> pluck ( 'count' , 'initial' ) ; foreach ( $ rows as $ alpha => $ count ) { $ alphas [ $ alpha ] = ( int ) $ count ; } 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 bool   $marnm  if set, include married names
@param bool   $fams   if set, only consider individuals with FAMS records
@param string $locale
@param string $collation

@return int[]
Get a list of initial given name letters for indilist . php and famlist . php