app/Services/IndividualListService.php
public function surnames(string $surn, string $salpha, bool $marnm, bool $fams, string $locale, string $collation): array
    {
        $query = DB::table('name')
            ->where('n_file', '=', $this->tree->id())
            ->select([
                DB::raw('UPPER(n_surn /*! COLLATE ' . I18N::collation() . ' */) AS n_surn'),
                DB::raw('n_surname /*! COLLATE utf8_bin */ AS n_surname'),
                DB::raw('COUNT(*) AS total'),
            ])
            ->groupBy(['n_surn'])
            ->groupBy(['n_surname'])
            ->orderBy('n_surname');

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

        if ($surn !== '') {
            $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.']);
        }
        $query->groupBy(['n_surn'])->groupBy(['n_surname']);

        $list = [];

        foreach ($query->get() as $row) {
            $list[$row->n_surn][$row->n_surname] = (int) $row->total;
        }

        return $list;
    }
public function surnames(string $surn, string $salpha, bool $marnm, bool $fams, string $locale, string $collation): array
    {
        $query = DB::table('name')
            ->where('n_file', '=', $this->tree->id())
            ->select([
                DB::raw('UPPER(n_surn /*! COLLATE ' . I18N::collation() . ' */) AS n_surn'),
                DB::raw('n_surname /*! COLLATE utf8_bin */ AS n_surname'),
                DB::raw('COUNT(*) AS total'),
            ])
            ->groupBy(['n_surn'])
            ->groupBy(['n_surname'])
            ->orderBy('n_surname');

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

        if ($surn !== '') {
            $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.']);
        }
        $query->groupBy(['n_surn'])->groupBy(['n_surname']);

        $list = [];

        foreach ($query->get() as $row) {
            $list[$row->n_surn][$row->n_surname] = (int) $row->total;
        }

        return $list;
    }
public function surnames ( string $ surn , string $ salpha , bool $ marnm , bool $ fams , string $ locale , string $ collation ) : array { $ query = DB :: table ( 'name' ) -> where ( 'n_file' , '=' , $ this -> tree -> id ( ) ) -> select ( [ DB :: raw ( 'UPPER(n_surn /*! COLLATE ' . I18N :: collation ( ) . ' */) AS n_surn' ) , DB :: raw ( 'n_surname /*! COLLATE utf8_bin */ AS n_surname' ) , DB :: raw ( 'COUNT(*) AS total' ) , ] ) -> groupBy ( [ 'n_surn' ] ) -> groupBy ( [ 'n_surname' ] ) -> orderBy ( 'n_surname' ) ; $ this -> whereFamily ( $ fams , $ query ) ; $ this -> whereMarriedName ( $ marnm , $ query ) ; if ( $ surn !== '' ) { $ 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.' ] ) ; } $ query -> groupBy ( [ 'n_surn' ] ) -> groupBy ( [ 'n_surname' ] ) ; $ list = [ ] ; foreach ( $ query -> get ( ) as $ row ) { $ list [ $ row -> n_surn ] [ $ row -> n_surname ] = ( int ) $ row -> total ; } return $ list ; }
Get a count of actual surnames and variants, based on a "root" surname.

@param string $surn   if set, only count 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 count of actual surnames and variants based on a root surname .