SosaRecordsService::insertOrUpdate() - 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

SosaRecordsService::insertOrUpdate() B

↳ Parent: SosaRecordsService

Complexity

Conditions	5
Paths	8

Size

Total Lines	52
Code Lines	39

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	5
eloc	39
c	1
b	0
f	0
nc	8
nop	3
dl	0
loc	52
rs	8.9848

How to fix Long Method

<?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\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 CRUD operations on Sosa records
 */
class SosaRecordsService
{
    /**
     * Maximum number of generation the database is able to hold.
     * @var int MAX_DB_GENERATIONS
     */
    public const MAX_DB_GENERATIONS = 64;

    /**
     * Calculate the generation of a sosa
     * Sosa 1 is of generation 1.
     *
     * @param int $sosa
     * @return int
     */
    public function generation(int $sosa): int
    {
        return (int) log($sosa, 2) + 1;
    }

    /**
     * Returns all Sosa numbers associated to an Individual
     *
     * @param Tree $tree
     * @param User $user
     * @param Individual $indi
     * @return Collection
     */
    public function getSosaNumbers(Tree $tree, User $user, Individual $indi): Collection
    {
        return DB::table('maj_sosa')
            ->select(['majs_sosa', 'majs_gen'])
            ->where('majs_gedcom_id', '=', $tree->id())
            ->where('majs_user_id', '=', $user->id())
            ->where('majs_i_id', '=', $indi->xref())
            ->orderBy('majs_sosa')
            ->get()->pluck('majs_gen', 'majs_sosa');
    }

    /**
     * Return a list of the Sosa ancestors at a given generation
     *
     * @param Tree $tree
     * @param User $user
     * @param int $gen
     * @return Collection
     */
    public function listAncestorsAtGeneration(Tree $tree, User $user, int $gen): Collection
    {
        return DB::table('maj_sosa')
            ->select(['majs_sosa', 'majs_i_id'])
            ->where('majs_gedcom_id', '=', $tree->id())
            ->where('majs_user_id', '=', $user->id())
            ->where('majs_gen', '=', $gen)
            ->orderBy('majs_sosa')
            ->get();
    }

    /**
     * Return a list of the Sosa families at a given generation
     *
     * @param Tree $tree
     * @param User $user
     * @param int $gen
     * @return Collection
     */
    public function listAncestorFamiliesAtGeneration(Tree $tree, User $user, int $gen): Collection
    {
        $table_prefix = DB::connection()->getTablePrefix();
        return DB::table('families')
            ->join('maj_sosa AS sosa_husb', function (JoinClause $join) use ($tree, $user): void {
                // Link to family husband
                $join->on('families.f_file', '=', 'sosa_husb.majs_gedcom_id')
                    ->on('families.f_husb', '=', 'sosa_husb.majs_i_id')
                    ->where('sosa_husb.majs_gedcom_id', '=', $tree->id())
                    ->where('sosa_husb.majs_user_id', '=', $user->id());
            })
            ->join('maj_sosa AS sosa_wife', function (JoinClause $join) use ($tree, $user): void {
                // Link to family husband
                $join->on('families.f_file', '=', 'sosa_wife.majs_gedcom_id')
                ->on('families.f_wife', '=', 'sosa_wife.majs_i_id')
                ->where('sosa_wife.majs_gedcom_id', '=', $tree->id())
                ->where('sosa_wife.majs_user_id', '=', $user->id());
            })
            ->select(['sosa_husb.majs_sosa', 'families.f_id'])
            ->where('sosa_husb.majs_gen', '=', $gen)
            ->whereRaw($table_prefix . 'sosa_husb.majs_sosa + 1 = ' . $table_prefix . 'sosa_wife.majs_sosa')
            ->orderBy('sosa_husb.majs_sosa')
            ->get();
    }

    /**
     * Return a list of Sosa ancestors missing at a given generation.
     * It includes the reference of either parent if it is known.
     *
     * @param Tree $tree
     * @param User $user
     * @param int $gen
     * @return Collection
     */
    public function listMissingAncestorsAtGeneration(Tree $tree, User $user, int $gen): Collection
    {
        if ($gen == 1) {
            return collect();
        }

        $table_prefix = DB::connection()->getTablePrefix();
        return DB::table('maj_sosa AS sosa')
            ->select(['sosa.majs_i_id', 'sosa_fat.majs_i_id AS majs_fat_id', 'sosa_mot.majs_i_id AS majs_mot_id'])
            ->selectRaw('MIN(' . $table_prefix . 'sosa.majs_sosa) AS majs_sosa')
            ->leftJoin('maj_sosa AS sosa_fat', function (JoinClause $join) use ($tree, $user, $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', '=', $tree->id())
                    ->where('sosa_fat.majs_user_id', '=', $user->id());
            })
            ->leftJoin('maj_sosa AS sosa_mot', function (JoinClause $join) use ($tree, $user, $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', '=', $tree->id())
                    ->where('sosa_mot.majs_user_id', '=', $user->id());
            })
            ->where('sosa.majs_gedcom_id', '=', $tree->id())
            ->where('sosa.majs_user_id', '=', $user->id())
            ->where('sosa.majs_gen', '=', $gen - 1)
            ->where(function (Builder $query): void {
                $query->whereNull('sosa_fat.majs_i_id')
                    ->orWhereNull('sosa_mot.majs_i_id');
            })
            ->groupBy('sosa.majs_i_id', 'sosa_fat.majs_i_id', 'sosa_mot.majs_i_id')
            ->orderByRaw('MIN(' . $table_prefix . 'sosa.majs_sosa)')
            ->get();
    }

    /**
     * Remove all Sosa entries related to the gedcom file and user
     *
     * @param Tree $tree
     * @param User $user
     */
    public function deleteAll(Tree $tree, User $user): void
    {
        DB::table('maj_sosa')
            ->where('majs_gedcom_id', '=', $tree->id())
            ->where('majs_user_id', '=', $user->id())
            ->delete();
    }

    /**
     *
     * @param Tree $tree
     * @param User $user
     * @param int $sosa
     */
    public function deleteAncestorsFrom(Tree $tree, User $user, int $sosa): void
    {
        DB::table('maj_sosa')
            ->where('majs_gedcom_id', '=', $tree->id())
            ->where('majs_user_id', '=', $user->id())
            ->where('majs_sosa', '>=', $sosa)
            ->whereRaw(
                'FLOOR(majs_sosa / (POW(2, (majs_gen - ?)))) = ?',
                [$this->generation($sosa), $sosa]
            )
            ->delete();
    }

    /**
     * Insert (or update if already existing) a list of Sosa individuals
     *
     * @param Tree $tree
     * @param User $user
     * @param array $sosa_records
     */
    public function insertOrUpdate(Tree $tree, User $user, array $sosa_records): void
    {
        $mass_update = DB::connection()->getDriverName() === 'mysql';

        $bindings_placeholders = $bindings_values = [];
        foreach ($sosa_records as $i => $row) {
            $gen = $this->generation($row['sosa']);
            if ($gen <=  self::MAX_DB_GENERATIONS) {
                if ($mass_update) {
                    $bindings_placeholders[] = '(:tree_id' . $i . ', :user_id' . $i . ', :sosa' . $i . ',' .
                        ' :indi_id' . $i . ', :gen' . $i . ',' .
                        ' :byear' . $i . ', :byearest' . $i . ', :dyear' . $i . ', :dyearest' . $i . ')';
                    $bindings_values = array_merge(
                        $bindings_values,
                        [
                            'tree_id' . $i => $tree->id(),
                            'user_id' . $i => $user->id(),
                            'sosa' . $i => $row['sosa'],
                            'indi_id' . $i => $row['indi'],
                            'gen' . $i => $gen,
                            'byear' . $i => $row['birth_year'],
                            'byearest' . $i => $row['birth_year_est'],
                            'dyear' . $i => $row['death_year'],
                            'dyearest' . $i => $row['death_year_est']
                        ]
                    );
                } else {
                    DB::table('maj_sosa')->updateOrInsert(
                        [ 'majs_gedcom_id' => $tree->id(), 'majs_user_id' => $user->id(), 'majs_sosa' => $row['sosa']],
                        [
                            'majs_i_id' => $row['indi'],
                            'majs_gen' => $gen,
                            'majs_birth_year' => $row['birth_year'],
                            'majs_birth_year_est' => $row['birth_year_est'],
                            'majs_death_year' => $row['death_year'],
                            'majs_death_year_est' => $row['death_year_est']
                        ]
                    );
                }
            }
        }

        if ($mass_update) {
            DB::connection()->statement(
                'INSERT INTO `' . DB::connection()->getTablePrefix() . 'maj_sosa`' .
                ' (majs_gedcom_id, majs_user_id, majs_sosa,' .
                '   majs_i_id, majs_gen, majs_birth_year, majs_birth_year_est, majs_death_year, majs_death_year_est)' .
                ' VALUES ' . implode(',', $bindings_placeholders) .
                ' ON DUPLICATE KEY UPDATE majs_i_id = VALUES(majs_i_id), majs_gen = VALUES(majs_gen),' .
                '   majs_birth_year = VALUES(majs_birth_year), majs_birth_year_est = VALUES(majs_birth_year_est),' .
                '   majs_death_year = VALUES(majs_death_year), majs_death_year_est = VALUES(majs_death_year_est)',
                $bindings_values
            );
        }
    }
}


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\Tree;
19			use Fisharebest\Webtrees\User;
20			use Illuminate\Database\Capsule\Manager as DB;
21			use Illuminate\Database\Query\Builder;
22			use Illuminate\Database\Query\JoinClause;
23			use Illuminate\Support\Collection;
24
25			/**
26			* Service for CRUD operations on Sosa records
27			*/
28			class SosaRecordsService
29			{
30			/**
31			* Maximum number of generation the database is able to hold.
32			* @var int MAX_DB_GENERATIONS
33			*/
34			public const MAX_DB_GENERATIONS = 64;
35
36			/**
37			* Calculate the generation of a sosa
38			* Sosa 1 is of generation 1.
39			*
40			* @param int $sosa
41			* @return int
42			*/
43			public function generation(int $sosa): int
44			{
45			return (int) log($sosa, 2) + 1;
46			}
47
48			/**
49			* Returns all Sosa numbers associated to an Individual
50			*
51			* @param Tree $tree
52			* @param User $user
53			* @param Individual $indi
54			* @return Collection
55			*/
56			public function getSosaNumbers(Tree $tree, User $user, Individual $indi): Collection
57			{
58			return DB::table('maj_sosa')
59			->select(['majs_sosa', 'majs_gen'])
60			->where('majs_gedcom_id', '=', $tree->id())
61			->where('majs_user_id', '=', $user->id())
62			->where('majs_i_id', '=', $indi->xref())
63			->orderBy('majs_sosa')
64			->get()->pluck('majs_gen', 'majs_sosa');
65			}
66
67			/**
68			* Return a list of the Sosa ancestors at a given generation
69			*
70			* @param Tree $tree
71			* @param User $user
72			* @param int $gen
73			* @return Collection
74			*/
75			public function listAncestorsAtGeneration(Tree $tree, User $user, int $gen): Collection
76			{
77			return DB::table('maj_sosa')
78			->select(['majs_sosa', 'majs_i_id'])
79			->where('majs_gedcom_id', '=', $tree->id())
80			->where('majs_user_id', '=', $user->id())
81			->where('majs_gen', '=', $gen)
82			->orderBy('majs_sosa')
83			->get();
84			}
85
86			/**
87			* Return a list of the Sosa families at a given generation
88			*
89			* @param Tree $tree
90			* @param User $user
91			* @param int $gen
92			* @return Collection
93			*/
94			public function listAncestorFamiliesAtGeneration(Tree $tree, User $user, int $gen): Collection
95			{
96			$table_prefix = DB::connection()->getTablePrefix();
97			return DB::table('families')
98			->join('maj_sosa AS sosa_husb', function (JoinClause $join) use ($tree, $user): void {
99			// Link to family husband
100			$join->on('families.f_file', '=', 'sosa_husb.majs_gedcom_id')
101			->on('families.f_husb', '=', 'sosa_husb.majs_i_id')
102			->where('sosa_husb.majs_gedcom_id', '=', $tree->id())
103			->where('sosa_husb.majs_user_id', '=', $user->id());
104			})
105			->join('maj_sosa AS sosa_wife', function (JoinClause $join) use ($tree, $user): void {
106			// Link to family husband
107			$join->on('families.f_file', '=', 'sosa_wife.majs_gedcom_id')
108			->on('families.f_wife', '=', 'sosa_wife.majs_i_id')
109			->where('sosa_wife.majs_gedcom_id', '=', $tree->id())
110			->where('sosa_wife.majs_user_id', '=', $user->id());
111			})
112			->select(['sosa_husb.majs_sosa', 'families.f_id'])
113			->where('sosa_husb.majs_gen', '=', $gen)
114			->whereRaw($table_prefix . 'sosa_husb.majs_sosa + 1 = ' . $table_prefix . 'sosa_wife.majs_sosa')
115			->orderBy('sosa_husb.majs_sosa')
116			->get();
117			}
118
119			/**
120			* Return a list of Sosa ancestors missing at a given generation.
121			* It includes the reference of either parent if it is known.
122			*
123			* @param Tree $tree
124			* @param User $user
125			* @param int $gen
126			* @return Collection
127			*/
128			public function listMissingAncestorsAtGeneration(Tree $tree, User $user, int $gen): Collection
129			{
130			if ($gen == 1) {
131			return collect();
132			}
133
134			$table_prefix = DB::connection()->getTablePrefix();
135			return DB::table('maj_sosa AS sosa')
136			->select(['sosa.majs_i_id', 'sosa_fat.majs_i_id AS majs_fat_id', 'sosa_mot.majs_i_id AS majs_mot_id'])
137			->selectRaw('MIN(' . $table_prefix . 'sosa.majs_sosa) AS majs_sosa')
138			->leftJoin('maj_sosa AS sosa_fat', function (JoinClause $join) use ($tree, $user, $table_prefix): void {
139			// Link to sosa's father
140			$join->whereRaw($table_prefix . 'sosa_fat.majs_sosa = 2 * ' . $table_prefix . 'sosa.majs_sosa')
141			->where('sosa_fat.majs_gedcom_id', '=', $tree->id())
142			->where('sosa_fat.majs_user_id', '=', $user->id());
143			})
144			->leftJoin('maj_sosa AS sosa_mot', function (JoinClause $join) use ($tree, $user, $table_prefix): void {
145			// Link to sosa's mother
146			$join->whereRaw($table_prefix . 'sosa_mot.majs_sosa = 2 * ' . $table_prefix . 'sosa.majs_sosa + 1')
147			->where('sosa_mot.majs_gedcom_id', '=', $tree->id())
148			->where('sosa_mot.majs_user_id', '=', $user->id());
149			})
150			->where('sosa.majs_gedcom_id', '=', $tree->id())
151			->where('sosa.majs_user_id', '=', $user->id())
152			->where('sosa.majs_gen', '=', $gen - 1)
153			->where(function (Builder $query): void {
154			$query->whereNull('sosa_fat.majs_i_id')
155			->orWhereNull('sosa_mot.majs_i_id');
156			})
157			->groupBy('sosa.majs_i_id', 'sosa_fat.majs_i_id', 'sosa_mot.majs_i_id')
158			->orderByRaw('MIN(' . $table_prefix . 'sosa.majs_sosa)')
159			->get();
160			}
161
162			/**
163			* Remove all Sosa entries related to the gedcom file and user
164			*
165			* @param Tree $tree
166			* @param User $user
167			*/
168			public function deleteAll(Tree $tree, User $user): void
169			{
170			DB::table('maj_sosa')
171			->where('majs_gedcom_id', '=', $tree->id())
172			->where('majs_user_id', '=', $user->id())
173			->delete();
174			}
175
176			/**
177			*
178			* @param Tree $tree
179			* @param User $user
180			* @param int $sosa
181			*/
182			public function deleteAncestorsFrom(Tree $tree, User $user, int $sosa): void
183			{
184			DB::table('maj_sosa')
185			->where('majs_gedcom_id', '=', $tree->id())
186			->where('majs_user_id', '=', $user->id())
187			->where('majs_sosa', '>=', $sosa)
188			->whereRaw(
189			'FLOOR(majs_sosa / (POW(2, (majs_gen - ?)))) = ?',
190			[$this->generation($sosa), $sosa]
191			)
192			->delete();
193			}
194
195			/**
196			* Insert (or update if already existing) a list of Sosa individuals
197			*
198			* @param Tree $tree
199			* @param User $user
200			* @param array $sosa_records
201			*/
202			public function insertOrUpdate(Tree $tree, User $user, array $sosa_records): void
203			{
204			$mass_update = DB::connection()->getDriverName() === 'mysql';
205
206			$bindings_placeholders = $bindings_values = [];
207			foreach ($sosa_records as $i => $row) {
208			$gen = $this->generation($row['sosa']);
209			if ($gen <= self::MAX_DB_GENERATIONS) {
210			if ($mass_update) {
211			$bindings_placeholders[] = '(:tree_id' . $i . ', :user_id' . $i . ', :sosa' . $i . ',' .
212			' :indi_id' . $i . ', :gen' . $i . ',' .
213			' :byear' . $i . ', :byearest' . $i . ', :dyear' . $i . ', :dyearest' . $i . ')';
214			$bindings_values = array_merge(
215			$bindings_values,
216			[
217			'tree_id' . $i => $tree->id(),
218			'user_id' . $i => $user->id(),
219			'sosa' . $i => $row['sosa'],
220			'indi_id' . $i => $row['indi'],
221			'gen' . $i => $gen,
222			'byear' . $i => $row['birth_year'],
223			'byearest' . $i => $row['birth_year_est'],
224			'dyear' . $i => $row['death_year'],
225			'dyearest' . $i => $row['death_year_est']
226			]
227			);
228			} else {
229			DB::table('maj_sosa')->updateOrInsert(
230			[ 'majs_gedcom_id' => $tree->id(), 'majs_user_id' => $user->id(), 'majs_sosa' => $row['sosa']],
231			[
232			'majs_i_id' => $row['indi'],
233			'majs_gen' => $gen,
234			'majs_birth_year' => $row['birth_year'],
235			'majs_birth_year_est' => $row['birth_year_est'],
236			'majs_death_year' => $row['death_year'],
237			'majs_death_year_est' => $row['death_year_est']
238			]
239			);
240			}
241			}
242			}
243
244			if ($mass_update) {
245			DB::connection()->statement(
246			'INSERT INTO `' . DB::connection()->getTablePrefix() . 'maj_sosa`' .
247			' (majs_gedcom_id, majs_user_id, majs_sosa,' .
248			' majs_i_id, majs_gen, majs_birth_year, majs_birth_year_est, majs_death_year, majs_death_year_est)' .
249			' VALUES ' . implode(',', $bindings_placeholders) .
250			' ON DUPLICATE KEY UPDATE majs_i_id = VALUES(majs_i_id), majs_gen = VALUES(majs_gen),' .
251			' majs_birth_year = VALUES(majs_birth_year), majs_birth_year_est = VALUES(majs_birth_year_est),' .
252			' majs_death_year = VALUES(majs_death_year), majs_death_year_est = VALUES(majs_death_year_est)',
253			$bindings_values
254			);
255			}
256			}
257			}
258

jon48 / webtrees-lib

Branch — feature/2.0 (be78a0)

SosaRecordsService::insertOrUpdate() B

Complexity

Size

Duplication

Importance

How to fix Long Method

Long Method

Duplication Side-by-Side

Filter issues like