SosaStatisticsService::rootIndividual() - Code Metrics - Inspection of "Upgrade to webtrees 2.0.10 - Adapt MyArtJaub Sosa..." - jon48/webtrees-lib - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Branch — feature/2.0 (be78a0)

by Jonathan

created 2020-12-14 10:58 UTC

SosaStatisticsService::rootIndividual() A

↳ Parent: SosaStatisticsService

Complexity

Conditions	1
Paths	1

Size

Total Lines	4
Code Lines	2

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	1
eloc	2
c	1
b	0
f	0
nc	1
nop	0
dl	0
loc	4
rs	10

<?php

/**
 * webtrees-lib: MyArtJaub library for webtrees
 *
 * @package MyArtJaub\Webtrees
 * @subpackage Sosa
 * @author Jonathan Jaubart <[email protected]>
 * @copyright Copyright (c) 2009-2020, Jonathan Jaubart
 * @license http://www.gnu.org/licenses/gpl.html GNU General Public License, version 3
 */

declare(strict_types=1);

namespace MyArtJaub\Webtrees\Module\Sosa\Services;

use Fisharebest\Webtrees\Individual;
use Fisharebest\Webtrees\Registry;
use Fisharebest\Webtrees\Tree;
use Fisharebest\Webtrees\User;
use Illuminate\Database\Capsule\Manager as DB;
use Illuminate\Database\Query\Builder;
use Illuminate\Database\Query\JoinClause;
use Illuminate\Support\Collection;

/**
 * Service for retrieving Sosa statistics
 */
class SosaStatisticsService
{

    /**
     * Reference user
     * @var User $user
     */
    private $user;

    /**
     * Reference tree
     * @var Tree $tree
     */
    private $tree;

    /**
     * Constructor for Sosa Statistics Service
     *
     * @param Tree $tree
     * @param User $user
     */
    public function __construct(Tree $tree, User $user)
    {
        $this->tree = $tree;
        $this->user = $user;
    }

    /**
     * Return the root individual for the reference tree and user.
     *
     * @return Individual|NULL
     */
    public function rootIndividual(): ?Individual
    {
        $root_indi_id = $this->tree->getUserPreference($this->user, 'MAJ_SOSA_ROOT_ID');
        return Registry::individualFactory()->make($root_indi_id, $this->tree);
    }

    /**
     * Get the highest generation for the reference tree and user.
     *
     * @return int
     */
    public function maxGeneration(): int
    {
        return (int) DB::table('maj_sosa')
            ->where('majs_gedcom_id', '=', $this->tree->id())
            ->where('majs_user_id', '=', $this->user->id())
            ->max('majs_gen');
    }

    /**
     * Get the total count of individuals in the tree.
     *
     * @return int
     */
    public function totalIndividuals(): int
    {
        return DB::table('individuals')
            ->where('i_file', '=', $this->tree->id())
            ->count();
    }

    /**
     * Get the total count of Sosa ancestors for all generations
     *
     * @return int
     */
    public function totalAncestors(): int
    {
        return DB::table('maj_sosa')
            ->where('majs_gedcom_id', '=', $this->tree->id())
            ->where('majs_user_id', '=', $this->user->id())
            ->count();
    }

    /**
     * Get the total count of Sosa ancestors for a generation
     *
     * @return int
     */
    public function totalAncestorsAtGeneration(int $gen): int
    {
        return DB::table('maj_sosa')
            ->where('majs_gedcom_id', '=', $this->tree->id())
            ->where('majs_user_id', '=', $this->user->id())
            ->where('majs_gen', '=', $gen)
            ->count();
    }

    /**
     * Get the total count of distinct Sosa ancestors for all generations
     *
     * @return int
     */
    public function totalDistinctAncestors(): int
    {
        return DB::table('maj_sosa')
            ->where('majs_gedcom_id', '=', $this->tree->id())
            ->where('majs_user_id', '=', $this->user->id())
            ->distinct()
            ->count('majs_i_id');
    }

    /**
     * Get the mean generation time, as the slope of the linear regression of birth years vs generations
     *
     * @return float
     */
    public function meanGenerationTime(): float
    {
        $row = DB::table('maj_sosa')
            ->where('majs_gedcom_id', '=', $this->tree->id())
            ->where('majs_user_id', '=', $this->user->id())
            ->whereNotNull('majs_birth_year')
            ->selectRaw('COUNT(majs_sosa) AS n')
            ->selectRaw('SUM(majs_gen * majs_birth_year) AS sum_xy')
            ->selectRaw('SUM(majs_gen) AS sum_x')
            ->selectRaw('SUM(majs_birth_year) AS sum_y')
            ->selectRaw('SUM(majs_gen * majs_gen) AS sum_x2')
            ->get()->first();

        return $row->n == 0 ? 0 :
            -($row->n * $row->sum_xy - $row->sum_x * $row->sum_y) / ($row->n * $row->sum_x2 - pow($row->sum_x, 2));
    }

    /**
     * Get the statistic array detailed by generation.
     * Statistics for each generation are:
     *  - The number of Sosa in generation
     *  - The number of Sosa up to generation
     *  - The number of distinct Sosa up to generation
     *  - The year of the first birth in generation
     *  - The year of the first estimated birth in generation
     *  - The year of the last birth in generation
     *  - The year of the last estimated birth in generation
     *  - The average year of birth in generation
     *
     * @return array<int, array<string, int|null>> Statistics array
     */
    public function statisticsByGenerations(): array
    {
        $stats_by_gen = $this->statisticsByGenerationBasicData();
        $cumul_stats_by_gen = $this->statisticsByGenerationCumulativeData();

        $statistics_by_gen = [];
        foreach ($stats_by_gen as $gen => $stats_gen) {
            $statistics_by_gen[(int) $stats_gen->gen] = array(
                'sosaCount'             =>  (int) $stats_gen->total_sosa,
                'sosaTotalCount'        =>  (int) $cumul_stats_by_gen[$gen]->total_cumul,
                'diffSosaTotalCount'    =>  (int) $cumul_stats_by_gen[$gen]->total_distinct_cumul,
                'firstBirth'            =>  $stats_gen->first_year,
                'firstEstimatedBirth'   =>  $stats_gen->first_est_year,
                'lastBirth'             =>  $stats_gen->last_year,
                'lastEstimatedBirth'    =>  $stats_gen->last_est_year
            );
        }

        return $statistics_by_gen;
    }

    /**
     * Returns the basic statistics data by generation.
     *
     * @return Collection
     */
    private function statisticsByGenerationBasicData(): Collection
    {
        return DB::table('maj_sosa')
            ->where('majs_gedcom_id', '=', $this->tree->id())
            ->where('majs_user_id', '=', $this->user->id())
            ->groupBy('majs_gen')
            ->orderBy('majs_gen', 'asc')
            ->select('majs_gen AS gen')
            ->selectRaw('COUNT(majs_sosa) AS total_sosa')
            ->selectRaw('MIN(majs_birth_year) AS first_year')
            ->selectRaw('MIN(majs_birth_year_est) AS first_est_year')
            ->selectRaw('MAX(majs_birth_year) AS last_year')
            ->selectRaw('MAX(majs_birth_year_est) AS last_est_year')
            ->get()->keyBy('gen');
    }

    /**
     * Returns the cumulative statistics data by generation
     *
     * @return Collection
     */
    private function statisticsByGenerationCumulativeData(): Collection
    {
        $list_gen = DB::table('maj_sosa')->select('majs_gen')->distinct()
            ->where('majs_gedcom_id', '=', $this->tree->id())
            ->where('majs_user_id', '=', $this->user->id());

        return DB::table('maj_sosa')
            ->joinSub($list_gen, 'list_gen', function (JoinClause $join): void {
                $join->on('maj_sosa.majs_gen', '<=', 'list_gen.majs_gen')
                ->where('majs_gedcom_id', '=', $this->tree->id())
                ->where('majs_user_id', '=', $this->user->id());
            })
            ->groupBy('list_gen.majs_gen')
            ->select('list_gen.majs_gen AS gen')
            ->selectRaw('COUNT(majs_i_id) AS total_cumul')
            ->selectRaw('COUNT(DISTINCT majs_i_id) AS total_distinct_cumul')
            ->get()->keyBy('gen');
    }

    /**
     * Return a Collection of the mean generation depth and deviation for all Sosa ancestors at a given generation.
     * Sosa 1 is of generation 1.
     *
     * Mean generation depth and deviation are calculated based on the works of Marie-Héléne Cazes and Pierre Cazes,
     * published in Population (French Edition), Vol. 51, No. 1 (Jan. - Feb., 1996), pp. 117-140
     * http://kintip.net/index.php?option=com_jdownloads&task=download.send&id=9&catid=4&m=0
     *
     * Format:
     *  - key : sosa number of the ancestor
     *  - values:
     *      - root_ancestor_id : ID of the ancestor
     *      - mean_gen_depth : Mean generation depth
     *      - stddev_gen_depth : Standard deviation of generation depth
     *
     * @param int $gen Sosa generation
     * @return Collection
     */
    public function generationDepthStatsAtGeneration(int $gen): Collection
    {
        $table_prefix = DB::connection()->getTablePrefix();
        $missing_ancestors_by_gen = DB::table('maj_sosa AS sosa')
            ->selectRaw($table_prefix . 'sosa.majs_gen - ? AS majs_gen_norm', [$gen])
            ->selectRaw('FLOOR(((' . $table_prefix . 'sosa.majs_sosa / POW(2, ' . $table_prefix . 'sosa.majs_gen -1 )) - 1) * POWER(2, ? - 1)) + POWER(2, ? - 1) AS root_ancestor', [$gen, $gen])   //@phpcs:ignore Generic.Files.LineLength.TooLong
            ->selectRaw('SUM(CASE WHEN ' . $table_prefix . 'sosa_fat.majs_i_id IS NULL AND ' . $table_prefix . 'sosa_mot.majs_i_id IS NULL THEN 1 ELSE 0 END) AS full_root_count')  //@phpcs:ignore Generic.Files.LineLength.TooLong
            ->selectRaw('SUM(CASE WHEN ' . $table_prefix . 'sosa_fat.majs_i_id IS NULL AND ' . $table_prefix . 'sosa_mot.majs_i_id IS NULL THEN 0 ELSE 1 END) As semi_root_count')  //@phpcs:ignore Generic.Files.LineLength.TooLong
            ->leftJoin('maj_sosa AS sosa_fat', function (JoinClause $join) use ($table_prefix): void {
                // Link to sosa's father
                $join->whereRaw($table_prefix . 'sosa_fat.majs_sosa = 2 * ' . $table_prefix . 'sosa.majs_sosa')
                ->where('sosa_fat.majs_gedcom_id', '=', $this->tree->id())
                ->where('sosa_fat.majs_user_id', '=', $this->user->id());
            })
            ->leftJoin('maj_sosa AS sosa_mot', function (JoinClause $join) use ($table_prefix): void {
                // Link to sosa's mother
                $join->whereRaw($table_prefix . 'sosa_mot.majs_sosa = 2 * ' . $table_prefix . 'sosa.majs_sosa + 1')
                ->where('sosa_mot.majs_gedcom_id', '=', $this->tree->id())
                ->where('sosa_mot.majs_user_id', '=', $this->user->id());
            })
            ->where('sosa.majs_gedcom_id', '=', $this->tree->id())
            ->where('sosa.majs_user_id', '=', $this->user->id())
            ->where('sosa.majs_gen', '>=', $gen)
            ->where(function (Builder $query): void {
                $query->whereNull('sosa_fat.majs_i_id')
                    ->orWhereNull('sosa_mot.majs_i_id');
            })
            ->groupBy(['sosa.majs_gen', 'root_ancestor']);

        return DB::table('maj_sosa AS sosa_list')
            ->select(['stats_by_gen.root_ancestor AS root_ancestor_sosa', 'sosa_list.majs_i_id as root_ancestor_id'])
            ->selectRaw('1 + SUM( (majs_gen_norm) * ( 2 * full_root_count + semi_root_count) /  (2 * POWER(2, majs_gen_norm))) AS mean_gen_depth')  //@phpcs:ignore Generic.Files.LineLength.TooLong
            ->selectRaw(' SQRT(' .
                '   SUM(POWER(majs_gen_norm, 2) * ( 2 * full_root_count + semi_root_count) /  (2 * POWER(2, majs_gen_norm)))' .     //@phpcs:ignore Generic.Files.LineLength.TooLong
                '   - POWER( SUM( (majs_gen_norm) * ( 2 * full_root_count + semi_root_count) /  (2 * POWER(2, majs_gen_norm))), 2)' .       //@phpcs:ignore Generic.Files.LineLength.TooLong
                ' ) AS stddev_gen_depth')
            ->joinSub($missing_ancestors_by_gen, 'stats_by_gen', function (JoinClause $join): void {
                $join->on('sosa_list.majs_sosa', '=', 'stats_by_gen.root_ancestor')
                    ->where('sosa_list.majs_gedcom_id', '=', $this->tree->id())
                    ->where('sosa_list.majs_user_id', '=', $this->user->id());
            })
            ->groupBy(['stats_by_gen.root_ancestor', 'sosa_list.majs_i_id'])
            ->orderBy('stats_by_gen.root_ancestor')
            ->get()->keyBy('root_ancestor_sosa');
    }

    /**
     * Return a collection of the most duplicated root Sosa ancestors.
     * The number of ancestors to return is limited by the parameter $limit.
     * If several individuals are tied when reaching the limit, none of them are returned,
     * which means that there can be less individuals returned than requested.
     *
     * Format:
     *  - value:
     *      - sosa_i_id : sosa individual
     *      - sosa_count: number of duplications of the ancestor (e.g. 3 if it appears 3 times)
     *
     * @param int $limit
     * @return Collection
     */
    public function topMultipleAncestorsWithNoTies(int $limit): Collection
    {
        $table_prefix = DB::connection()->getTablePrefix();
        $multiple_ancestors = DB::table('maj_sosa AS sosa')
            ->select('sosa.majs_i_id AS sosa_i_id')
            ->selectRaw('COUNT(' . $table_prefix . 'sosa.majs_sosa) AS sosa_count')
            ->leftJoin('maj_sosa AS sosa_fat', function (JoinClause $join) use ($table_prefix): void {
                // Link to sosa's father
                $join->whereRaw($table_prefix . 'sosa_fat.majs_sosa = 2 * ' . $table_prefix . 'sosa.majs_sosa')
                    ->where('sosa_fat.majs_gedcom_id', '=', $this->tree->id())
                    ->where('sosa_fat.majs_user_id', '=', $this->user->id());
            })
            ->leftJoin('maj_sosa AS sosa_mot', function (JoinClause $join) use ($table_prefix): void {
                // Link to sosa's mother
                $join->whereRaw($table_prefix . 'sosa_mot.majs_sosa = 2 * ' . $table_prefix . 'sosa.majs_sosa + 1')
                ->where('sosa_mot.majs_gedcom_id', '=', $this->tree->id())
                ->where('sosa_mot.majs_user_id', '=', $this->user->id());
            })
            ->where('sosa.majs_gedcom_id', '=', $this->tree->id())
            ->where('sosa.majs_user_id', '=', $this->user->id())
            ->whereNull('sosa_fat.majs_sosa')   // We keep only root individuals, i.e. those with no father or mother
            ->whereNull('sosa_mot.majs_sosa')
            ->groupBy('sosa.majs_i_id')
            ->havingRaw('COUNT(' . $table_prefix . 'sosa.majs_sosa) > 1')    // Limit to the duplicate sosas.
            ->orderByRaw('COUNT(' . $table_prefix . 'sosa.majs_sosa) DESC, MIN(' . $table_prefix . 'sosa.majs_sosa) ASC')   //@phpcs:ignore Generic.Files.LineLength.TooLong
            ->limit($limit + 1)     // We want to select one more than required, for ties
            ->get();

        if ($multiple_ancestors->count() > $limit) {
            $last_count = $multiple_ancestors->last()->sosa_count;
            $multiple_ancestors = $multiple_ancestors->reject(function ($element) use ($last_count): bool {
                return $element->sosa_count ==  $last_count;
            });
        }
        return $multiple_ancestors;
    }

    /**
     * Return a computed array of statistics about the dispersion of ancestors across the ancestors
     * at a specified generation.
     *
     * Format:
     *  - key : rank of the ancestor in generation G for which exclusive ancestors have been found
     *          For instance 3 represent the maternal grand father
     *          0 is used for shared ancestors
     *  - values: number of ancestors exclusively in the ancestors of the ancestor in key
     *
     *  For instance a result at generation 3 could be :
     *      array (   0     =>  12      -> 12 ancestors are shared by the grand-parents
     *                1     =>  32      -> 32 ancestors are exclusive to the paternal grand-father
     *                2     =>  25      -> 25 ancestors are exclusive to the paternal grand-mother
     *                3     =>  12      -> 12 ancestors are exclusive to the maternal grand-father
     *                4     =>  30      -> 30 ancestors are exclusive to the maternal grand-mother
     *            )
     *
     * @param int $gen
     * @return Collection
     */
    public function ancestorsDispersionForGeneration(int $gen): Collection
    {
        $ancestors_branches = DB::table('maj_sosa')
            ->select('majs_i_id AS i_id')
            ->selectRaw('FLOOR(majs_sosa / POW(2, (majs_gen - ?))) - POW(2, ? -1) + 1 AS branch', [$gen, $gen])
            ->where('majs_gedcom_id', '=', $this->tree->id())
            ->where('majs_user_id', '=', $this->user->id())
            ->where('majs_gen', '>=', $gen)
            ->groupBy('majs_i_id', 'branch');


        $consolidated_ancestors_branches = DB::table('maj_sosa')
            ->fromSub($ancestors_branches, 'indi_branch')
            ->select('i_id')
            ->selectRaw('CASE WHEN COUNT(branch) > 1 THEN 0 ELSE MIN(branch) END AS branches')
            ->groupBy('i_id');

        return DB::table('maj_sosa')
            ->fromSub($consolidated_ancestors_branches, 'indi_branch_consolidated')
            ->select('branches')
            ->selectRaw('COUNT(i_id) AS count_indi')
            ->groupBy('branches')
            ->get()->pluck('count_indi', 'branches');
    }
}


1			<?php
2
3			/**
4			* webtrees-lib: MyArtJaub library for webtrees
5			*
6			* @package MyArtJaub\Webtrees
7			* @subpackage Sosa
8			* @author Jonathan Jaubart <[email protected]>
9			* @copyright Copyright (c) 2009-2020, Jonathan Jaubart
10			* @license http://www.gnu.org/licenses/gpl.html GNU General Public License, version 3
11			*/
12
13			declare(strict_types=1);
14
15			namespace MyArtJaub\Webtrees\Module\Sosa\Services;
16
17			use Fisharebest\Webtrees\Individual;
18			use Fisharebest\Webtrees\Registry;
19			use Fisharebest\Webtrees\Tree;
20			use Fisharebest\Webtrees\User;
21			use Illuminate\Database\Capsule\Manager as DB;
22			use Illuminate\Database\Query\Builder;
23			use Illuminate\Database\Query\JoinClause;
24			use Illuminate\Support\Collection;
25
26			/**
27			* Service for retrieving Sosa statistics
28			*/
29			class SosaStatisticsService
30			{
31
32			/**
33			* Reference user
34			* @var User $user
35			*/
36			private $user;
37
38			/**
39			* Reference tree
40			* @var Tree $tree
41			*/
42			private $tree;
43
44			/**
45			* Constructor for Sosa Statistics Service
46			*
47			* @param Tree $tree
48			* @param User $user
49			*/
50			public function __construct(Tree $tree, User $user)
51			{
52			$this->tree = $tree;
53			$this->user = $user;
54			}
55
56			/**
57			* Return the root individual for the reference tree and user.
58			*
59			* @return Individual\|NULL
60			*/
61			public function rootIndividual(): ?Individual
62			{
63			$root_indi_id = $this->tree->getUserPreference($this->user, 'MAJ_SOSA_ROOT_ID');
64			return Registry::individualFactory()->make($root_indi_id, $this->tree);
65			}
66
67			/**
68			* Get the highest generation for the reference tree and user.
69			*
70			* @return int
71			*/
72			public function maxGeneration(): int
73			{
74			return (int) DB::table('maj_sosa')
75			->where('majs_gedcom_id', '=', $this->tree->id())
76			->where('majs_user_id', '=', $this->user->id())
77			->max('majs_gen');
78			}
79
80			/**
81			* Get the total count of individuals in the tree.
82			*
83			* @return int
84			*/
85			public function totalIndividuals(): int
86			{
87			return DB::table('individuals')
88			->where('i_file', '=', $this->tree->id())
89			->count();
90			}
91
92			/**
93			* Get the total count of Sosa ancestors for all generations
94			*
95			* @return int
96			*/
97			public function totalAncestors(): int
98			{
99			return DB::table('maj_sosa')
100			->where('majs_gedcom_id', '=', $this->tree->id())
101			->where('majs_user_id', '=', $this->user->id())
102			->count();
103			}
104
105			/**
106			* Get the total count of Sosa ancestors for a generation
107			*
108			* @return int
109			*/
110			public function totalAncestorsAtGeneration(int $gen): int
111			{
112			return DB::table('maj_sosa')
113			->where('majs_gedcom_id', '=', $this->tree->id())
114			->where('majs_user_id', '=', $this->user->id())
115			->where('majs_gen', '=', $gen)
116			->count();
117			}
118
119			/**
120			* Get the total count of distinct Sosa ancestors for all generations
121			*
122			* @return int
123			*/
124			public function totalDistinctAncestors(): int
125			{
126			return DB::table('maj_sosa')
127			->where('majs_gedcom_id', '=', $this->tree->id())
128			->where('majs_user_id', '=', $this->user->id())
129			->distinct()
130			->count('majs_i_id');
131			}
132
133			/**
134			* Get the mean generation time, as the slope of the linear regression of birth years vs generations
135			*
136			* @return float
137			*/
138			public function meanGenerationTime(): float
139			{
140			$row = DB::table('maj_sosa')
141			->where('majs_gedcom_id', '=', $this->tree->id())
142			->where('majs_user_id', '=', $this->user->id())
143			->whereNotNull('majs_birth_year')
144			->selectRaw('COUNT(majs_sosa) AS n')
145			->selectRaw('SUM(majs_gen * majs_birth_year) AS sum_xy')
146			->selectRaw('SUM(majs_gen) AS sum_x')
147			->selectRaw('SUM(majs_birth_year) AS sum_y')
148			->selectRaw('SUM(majs_gen * majs_gen) AS sum_x2')
149			->get()->first();
150
151			return $row->n == 0 ? 0 :
152			-($row->n * $row->sum_xy - $row->sum_x * $row->sum_y) / ($row->n * $row->sum_x2 - pow($row->sum_x, 2));
153			}
154
155			/**
156			* Get the statistic array detailed by generation.
157			* Statistics for each generation are:
158			* - The number of Sosa in generation
159			* - The number of Sosa up to generation
160			* - The number of distinct Sosa up to generation
161			* - The year of the first birth in generation
162			* - The year of the first estimated birth in generation
163			* - The year of the last birth in generation
164			* - The year of the last estimated birth in generation
165			* - The average year of birth in generation
166			*
167			* @return array<int, array<string, int\|null>> Statistics array
168			*/
169			public function statisticsByGenerations(): array
170			{
171			$stats_by_gen = $this->statisticsByGenerationBasicData();
172			$cumul_stats_by_gen = $this->statisticsByGenerationCumulativeData();
173
174			$statistics_by_gen = [];
175			foreach ($stats_by_gen as $gen => $stats_gen) {
176			$statistics_by_gen[(int) $stats_gen->gen] = array(
177			'sosaCount' => (int) $stats_gen->total_sosa,
178			'sosaTotalCount' => (int) $cumul_stats_by_gen[$gen]->total_cumul,
179			'diffSosaTotalCount' => (int) $cumul_stats_by_gen[$gen]->total_distinct_cumul,
180			'firstBirth' => $stats_gen->first_year,
181			'firstEstimatedBirth' => $stats_gen->first_est_year,
182			'lastBirth' => $stats_gen->last_year,
183			'lastEstimatedBirth' => $stats_gen->last_est_year
184			);
185			}
186
187			return $statistics_by_gen;
188			}
189
190			/**
191			* Returns the basic statistics data by generation.
192			*
193			* @return Collection
194			*/
195			private function statisticsByGenerationBasicData(): Collection
196			{
197			return DB::table('maj_sosa')
198			->where('majs_gedcom_id', '=', $this->tree->id())
199			->where('majs_user_id', '=', $this->user->id())
200			->groupBy('majs_gen')
201			->orderBy('majs_gen', 'asc')
202			->select('majs_gen AS gen')
203			->selectRaw('COUNT(majs_sosa) AS total_sosa')
204			->selectRaw('MIN(majs_birth_year) AS first_year')
205			->selectRaw('MIN(majs_birth_year_est) AS first_est_year')
206			->selectRaw('MAX(majs_birth_year) AS last_year')
207			->selectRaw('MAX(majs_birth_year_est) AS last_est_year')
208			->get()->keyBy('gen');
209			}
210
211			/**
212			* Returns the cumulative statistics data by generation
213			*
214			* @return Collection
215			*/
216			private function statisticsByGenerationCumulativeData(): Collection
217			{
218			$list_gen = DB::table('maj_sosa')->select('majs_gen')->distinct()
219			->where('majs_gedcom_id', '=', $this->tree->id())
220			->where('majs_user_id', '=', $this->user->id());
221
222			return DB::table('maj_sosa')
223			->joinSub($list_gen, 'list_gen', function (JoinClause $join): void {
224			$join->on('maj_sosa.majs_gen', '<=', 'list_gen.majs_gen')
225			->where('majs_gedcom_id', '=', $this->tree->id())
226			->where('majs_user_id', '=', $this->user->id());
227			})
228			->groupBy('list_gen.majs_gen')
229			->select('list_gen.majs_gen AS gen')
230			->selectRaw('COUNT(majs_i_id) AS total_cumul')
231			->selectRaw('COUNT(DISTINCT majs_i_id) AS total_distinct_cumul')
232			->get()->keyBy('gen');
233			}
234
235			/**
236			* Return a Collection of the mean generation depth and deviation for all Sosa ancestors at a given generation.
237			* Sosa 1 is of generation 1.
238			*
239			* Mean generation depth and deviation are calculated based on the works of Marie-Héléne Cazes and Pierre Cazes,
240			* published in Population (French Edition), Vol. 51, No. 1 (Jan. - Feb., 1996), pp. 117-140
241			* http://kintip.net/index.php?option=com_jdownloads&task=download.send&id=9&catid=4&m=0
242			*
243			* Format:
244			* - key : sosa number of the ancestor
245			* - values:
246			* - root_ancestor_id : ID of the ancestor
247			* - mean_gen_depth : Mean generation depth
248			* - stddev_gen_depth : Standard deviation of generation depth
249			*
250			* @param int $gen Sosa generation
251			* @return Collection
252			*/
253			public function generationDepthStatsAtGeneration(int $gen): Collection
254			{
255			$table_prefix = DB::connection()->getTablePrefix();
256			$missing_ancestors_by_gen = DB::table('maj_sosa AS sosa')
257			->selectRaw($table_prefix . 'sosa.majs_gen - ? AS majs_gen_norm', [$gen])
258			->selectRaw('FLOOR(((' . $table_prefix . 'sosa.majs_sosa / POW(2, ' . $table_prefix . 'sosa.majs_gen -1 )) - 1) * POWER(2, ? - 1)) + POWER(2, ? - 1) AS root_ancestor', [$gen, $gen]) //@phpcs:ignore Generic.Files.LineLength.TooLong
259			->selectRaw('SUM(CASE WHEN ' . $table_prefix . 'sosa_fat.majs_i_id IS NULL AND ' . $table_prefix . 'sosa_mot.majs_i_id IS NULL THEN 1 ELSE 0 END) AS full_root_count') //@phpcs:ignore Generic.Files.LineLength.TooLong
260			->selectRaw('SUM(CASE WHEN ' . $table_prefix . 'sosa_fat.majs_i_id IS NULL AND ' . $table_prefix . 'sosa_mot.majs_i_id IS NULL THEN 0 ELSE 1 END) As semi_root_count') //@phpcs:ignore Generic.Files.LineLength.TooLong
261			->leftJoin('maj_sosa AS sosa_fat', function (JoinClause $join) use ($table_prefix): void {
262			// Link to sosa's father
263			$join->whereRaw($table_prefix . 'sosa_fat.majs_sosa = 2 * ' . $table_prefix . 'sosa.majs_sosa')
264			->where('sosa_fat.majs_gedcom_id', '=', $this->tree->id())
265			->where('sosa_fat.majs_user_id', '=', $this->user->id());
266			})
267			->leftJoin('maj_sosa AS sosa_mot', function (JoinClause $join) use ($table_prefix): void {
268			// Link to sosa's mother
269			$join->whereRaw($table_prefix . 'sosa_mot.majs_sosa = 2 * ' . $table_prefix . 'sosa.majs_sosa + 1')
270			->where('sosa_mot.majs_gedcom_id', '=', $this->tree->id())
271			->where('sosa_mot.majs_user_id', '=', $this->user->id());
272			})
273			->where('sosa.majs_gedcom_id', '=', $this->tree->id())
274			->where('sosa.majs_user_id', '=', $this->user->id())
275			->where('sosa.majs_gen', '>=', $gen)
276			->where(function (Builder $query): void {
277			$query->whereNull('sosa_fat.majs_i_id')
278			->orWhereNull('sosa_mot.majs_i_id');
279			})
280			->groupBy(['sosa.majs_gen', 'root_ancestor']);
281
282			return DB::table('maj_sosa AS sosa_list')
283			->select(['stats_by_gen.root_ancestor AS root_ancestor_sosa', 'sosa_list.majs_i_id as root_ancestor_id'])
284			->selectRaw('1 + SUM( (majs_gen_norm) * ( 2 * full_root_count + semi_root_count) / (2 * POWER(2, majs_gen_norm))) AS mean_gen_depth') //@phpcs:ignore Generic.Files.LineLength.TooLong
285			->selectRaw(' SQRT(' .
286			' SUM(POWER(majs_gen_norm, 2) * ( 2 * full_root_count + semi_root_count) / (2 * POWER(2, majs_gen_norm)))' . //@phpcs:ignore Generic.Files.LineLength.TooLong
287			' - POWER( SUM( (majs_gen_norm) * ( 2 * full_root_count + semi_root_count) / (2 * POWER(2, majs_gen_norm))), 2)' . //@phpcs:ignore Generic.Files.LineLength.TooLong
288			' ) AS stddev_gen_depth')
289			->joinSub($missing_ancestors_by_gen, 'stats_by_gen', function (JoinClause $join): void {
290			$join->on('sosa_list.majs_sosa', '=', 'stats_by_gen.root_ancestor')
291			->where('sosa_list.majs_gedcom_id', '=', $this->tree->id())
292			->where('sosa_list.majs_user_id', '=', $this->user->id());
293			})
294			->groupBy(['stats_by_gen.root_ancestor', 'sosa_list.majs_i_id'])
295			->orderBy('stats_by_gen.root_ancestor')
296			->get()->keyBy('root_ancestor_sosa');
297			}
298
299			/**
300			* Return a collection of the most duplicated root Sosa ancestors.
301			* The number of ancestors to return is limited by the parameter $limit.
302			* If several individuals are tied when reaching the limit, none of them are returned,
303			* which means that there can be less individuals returned than requested.
304			*
305			* Format:
306			* - value:
307			* - sosa_i_id : sosa individual
308			* - sosa_count: number of duplications of the ancestor (e.g. 3 if it appears 3 times)
309			*
310			* @param int $limit
311			* @return Collection
312			*/
313			public function topMultipleAncestorsWithNoTies(int $limit): Collection
314			{
315			$table_prefix = DB::connection()->getTablePrefix();
316			$multiple_ancestors = DB::table('maj_sosa AS sosa')
317			->select('sosa.majs_i_id AS sosa_i_id')
318			->selectRaw('COUNT(' . $table_prefix . 'sosa.majs_sosa) AS sosa_count')
319			->leftJoin('maj_sosa AS sosa_fat', function (JoinClause $join) use ($table_prefix): void {
320			// Link to sosa's father
321			$join->whereRaw($table_prefix . 'sosa_fat.majs_sosa = 2 * ' . $table_prefix . 'sosa.majs_sosa')
322			->where('sosa_fat.majs_gedcom_id', '=', $this->tree->id())
323			->where('sosa_fat.majs_user_id', '=', $this->user->id());
324			})
325			->leftJoin('maj_sosa AS sosa_mot', function (JoinClause $join) use ($table_prefix): void {
326			// Link to sosa's mother
327			$join->whereRaw($table_prefix . 'sosa_mot.majs_sosa = 2 * ' . $table_prefix . 'sosa.majs_sosa + 1')
328			->where('sosa_mot.majs_gedcom_id', '=', $this->tree->id())
329			->where('sosa_mot.majs_user_id', '=', $this->user->id());
330			})
331			->where('sosa.majs_gedcom_id', '=', $this->tree->id())
332			->where('sosa.majs_user_id', '=', $this->user->id())
333			->whereNull('sosa_fat.majs_sosa') // We keep only root individuals, i.e. those with no father or mother
334			->whereNull('sosa_mot.majs_sosa')
335			->groupBy('sosa.majs_i_id')
336			->havingRaw('COUNT(' . $table_prefix . 'sosa.majs_sosa) > 1') // Limit to the duplicate sosas.
337			->orderByRaw('COUNT(' . $table_prefix . 'sosa.majs_sosa) DESC, MIN(' . $table_prefix . 'sosa.majs_sosa) ASC') //@phpcs:ignore Generic.Files.LineLength.TooLong
338			->limit($limit + 1) // We want to select one more than required, for ties
339			->get();
340
341			if ($multiple_ancestors->count() > $limit) {
342			$last_count = $multiple_ancestors->last()->sosa_count;
343			$multiple_ancestors = $multiple_ancestors->reject(function ($element) use ($last_count): bool {
344			return $element->sosa_count == $last_count;
345			});
346			}
347			return $multiple_ancestors;
348			}
349
350			/**
351			* Return a computed array of statistics about the dispersion of ancestors across the ancestors
352			* at a specified generation.
353			*
354			* Format:
355			* - key : rank of the ancestor in generation G for which exclusive ancestors have been found
356			* For instance 3 represent the maternal grand father
357			* 0 is used for shared ancestors
358			* - values: number of ancestors exclusively in the ancestors of the ancestor in key
359			*
360			* For instance a result at generation 3 could be :
361			* array ( 0 => 12 -> 12 ancestors are shared by the grand-parents
362			* 1 => 32 -> 32 ancestors are exclusive to the paternal grand-father
363			* 2 => 25 -> 25 ancestors are exclusive to the paternal grand-mother
364			* 3 => 12 -> 12 ancestors are exclusive to the maternal grand-father
365			* 4 => 30 -> 30 ancestors are exclusive to the maternal grand-mother
366			* )
367			*
368			* @param int $gen
369			* @return Collection
370			*/
371			public function ancestorsDispersionForGeneration(int $gen): Collection
372			{
373			$ancestors_branches = DB::table('maj_sosa')
374			->select('majs_i_id AS i_id')
375			->selectRaw('FLOOR(majs_sosa / POW(2, (majs_gen - ?))) - POW(2, ? -1) + 1 AS branch', [$gen, $gen])
376			->where('majs_gedcom_id', '=', $this->tree->id())
377			->where('majs_user_id', '=', $this->user->id())
378			->where('majs_gen', '>=', $gen)
379			->groupBy('majs_i_id', 'branch');
380
381
382			$consolidated_ancestors_branches = DB::table('maj_sosa')
383			->fromSub($ancestors_branches, 'indi_branch')
384			->select('i_id')
385			->selectRaw('CASE WHEN COUNT(branch) > 1 THEN 0 ELSE MIN(branch) END AS branches')
386			->groupBy('i_id');
387
388			return DB::table('maj_sosa')
389			->fromSub($consolidated_ancestors_branches, 'indi_branch_consolidated')
390			->select('branches')
391			->selectRaw('COUNT(i_id) AS count_indi')
392			->groupBy('branches')
393			->get()->pluck('count_indi', 'branches');
394			}
395			}
396

jon48 / webtrees-lib

Branch — feature/2.0 (be78a0)

SosaStatisticsService::rootIndividual() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like