SosaProvider::getStatisticsByGeneration() - Code Metrics - Inspection of "RESOLVED - #JJ215: Add Root Sosa name, and link to..." - jon48/webtrees-lib - Measure and Improve Code Quality continuously with Scrutinizer

Completed
Push — master ( bdc52b...f93e28 )

by Jonathan
created 2016-07-29 07:58 UTC
SosaProvider::getStatisticsByGeneration() B

↳ Parent: SosaProvider
Complexity

Conditions	5
Paths	4
Size

Total Lines	20
Code Lines	15
Duplication

Lines	0
Ratio	0 %
Importance

Changes	1
Bugs	0	Features	0
Metric	Value
c	1
b	0
f	0
dl	0
loc	20
rs	8.8571
cc	5
eloc	15
nc	4
nop	0
<?php
/**
 * webtrees-lib: MyArtJaub library for webtrees
 *
 * @package MyArtJaub\Webtrees
 * @subpackage Sosa
 * @author Jonathan Jaubart <[email protected]>
 * @copyright Copyright (c) 2009-2016, Jonathan Jaubart
 * @license http://www.gnu.org/licenses/gpl.html GNU General Public License, version 3
 */
namespace MyArtJaub\Webtrees\Module\Sosa\Model;

use Fisharebest\Webtrees\Tree;
use Fisharebest\Webtrees\User;
use Fisharebest\Webtrees\Database;
use MyArtJaub\Webtrees\Functions\Functions;
use Fisharebest\Webtrees\Auth;
use Fisharebest\Webtrees\Individual;

/**
 * Provide Sosa data access
 */
class SosaProvider {
    
    /**
     * Maximum number of generation the database is able to hold.
     * @var int MAX_DB_GENERATIONS
     */
    const MAX_DB_GENERATIONS = 64;
    
    /**
     * System's default user (ID -1 in the database
     * @var User $default_user
     */
    protected static $default_user;
    
    /**
     * Reference user
     * @var User $user
     */
    protected $user;
    
    /**
     * Reference tree
     * @var Tree $tree
     */
    protected $tree;
    
    /**
     * Cached list of Sosa Individuals by generation
     * Format: key = generation, value = array ( sosa => Individual ID)
     * @var array $sosa_list_by_gen
     */
    protected $sosa_list_by_gen;
    
    /**
     * Cached list of Sosa Families by generation
     * Format: key = generation, value = array ( sosa => Family ID)
     * @var unknown $sosa_fam_list_by_gen
     */
    protected $sosa_fam_list_by_gen;
    
    /**
     * Cached array of statistics by generation
     * Format:  key = generation, 
     *          value = array(
     *              sosaCount, sosaTotalCount, diffSosaTotalCount, firstBirth, lastBirth, avgBirth
     *           )
     * @var array $statistics_tab
     */
    protected $statistics_tab;
    
    /**
     * Has the provider's initialisation completed
     * @var bool $is_setup
     */
    protected $is_setup;
    
    /**
     * Constructor for Sosa Provider.
     * A provider is defined in relation to a specific tree and reference user.
     * 
     * @param Tree $tree
     * @param User $user
     */
    public function __construct(Tree $tree, User $user = null) {
        if(self::$default_user === null) 
            self::$default_user = User::find(-1);
        
        $this->tree = $tree;
        $this->user = $user;
        $this->is_setup = true;
        if($this->user === null) $this->user = Auth::user();
        if(strlen($this->user->getUserId()) == 0) $this->user = self::$default_user;
        
        // Check if the user, or the default user, has a root already setup;
        if(empty($this->getRootIndiId())) {
            if($this->user == self::$default_user) {  // If the default user is not setup
                $this->is_setup = false;
            }
            else {
                $this->user = self::$default_user;
                $this->is_setup = $this->getRootIndiId() === null;
            }            
        }
    }
    
    /**
     * Returns is the Provider has been successfully set up
     * @return bool
     */
    public function isSetup() {
        return $this->is_setup;
    }
    
    /**
     * Return the root individual ID for the reference tree and user.
     * @return string Individual ID
     */
    public function getRootIndiId() {
        return $this->tree->getUserPreference($this->user, 'MAJ_SOSA_ROOT_ID');
    }
    
    /**
     * Return the root individual for the reference tree and user.
     * @return Individual Individual
     */
    public function getRootIndi() {
        $root_indi_id = $this->getRootIndiId();
        if(!empty($root_indi_id)) {
            return Individual::getInstance($root_indi_id, $this->tree);
        }
        return null;
    }
       
    /*****************
     * DATA CRUD LAYER
     *****************/
    
    /**
     * Remove all Sosa entries related to the gedcom file and user
     */
    public function deleteAll() {
        if(!$this->is_setup) return;
        Database::prepare(
            'DELETE FROM `##maj_sosa`'.
            ' WHERE majs_gedcom_id= :tree_id and majs_user_id = :user_id ')
            ->execute(array(
                'tree_id' => $this->tree->getTreeId(), 
                'user_id' => $this->user->getUserId()                
            ));
    }
    
    /**
     * Remove all ancestors of a sosa number
     * 
     * @param int $sosa
     */
    public function deleteAncestors($sosa) {
        if(!$this->is_setup) return;
        $gen = Functions::getGeneration($sosa);
        Database::prepare(
            'DELETE FROM `##maj_sosa`'.
            ' WHERE majs_gedcom_id=:tree_id and majs_user_id = :user_id' .
            ' AND majs_gen >= :gen' .
            ' AND FLOOR(majs_sosa / (POW(2, (majs_gen - :gen)))) = :sosa'
        )->execute(array(
            'tree_id' => $this->tree->getTreeId(), 
            'user_id' => $this->user->getUserId(),
            'gen' => $gen,
            'sosa' => $sosa
        ));
    }    
    
    /**
     * Insert (or update if already existing) a list of Sosa individuals
     * @param array $sosa_records
     */
    public function insertOrUpdate($sosa_records) {
        if(!$this->is_setup) return;
        
        $treeid = $this->tree->getTreeId();
        $userid = $this->user->getUserId();
        $questionmarks_table = array();
        $values_table = array();
        
        $i = 0;
        foreach  ($sosa_records as $row) {
            $gen = Functions::getGeneration($row['sosa']);
            if($gen <= self::MAX_DB_GENERATIONS) {
                $questionmarks_table[] = 
                    '(:tree_id'.$i.', :user_id'.$i.', :sosa'.$i.', :indi_id'.$i.', :gen'.$i.', :byear'.$i.', :dyear'.$i.')';
                $values_table = array_merge(
                    $values_table, 
                    array(
                        'tree_id'.$i => $treeid, 
                        'user_id'.$i => $userid, 
                        'sosa'.$i => $row['sosa'], 
                        'indi_id'.$i => $row['indi'], 
                        'gen'.$i => Functions::getGeneration($row['sosa']),
                        'byear'.$i => $row['birth_year'],
                        'dyear'.$i => $row['death_year']
                    )
                );
            }
            $i++;
        }
        
        $sql = 'REPLACE INTO `##maj_sosa`' .
            ' (majs_gedcom_id, majs_user_id, majs_sosa, majs_i_id, majs_gen, majs_birth_year, majs_death_year)' .
            ' VALUES '. implode(',', $questionmarks_table);
        Database::prepare($sql)->execute($values_table);
    }
    
    /****************
     * SIMPLE QUERIES
     ****************/
    
    /**
     * Returns the list of Sosa numbers to which an individual is related.
     * Format: key = sosa number, value = generation for the Sosa number
     * 
     * @param Individual $indi
     * @return array Array of sosa numbers
     */
    public function getSosaNumbers(Individual $indi) {

        if(!$this->is_setup) return array();
        return Database::prepare(
                'SELECT majs_sosa, majs_gen FROM `##maj_sosa`'.
                ' WHERE majs_i_id=:indi_id AND majs_gedcom_id=:tree_id AND majs_user_id=:user_id'
            )->execute(array(
                'indi_id' => $indi->getXref(), 
                'tree_id' => $this->tree->getTreeId(), 
                'user_id' => $this->user->getUserId()
            ))->fetchAssoc();
    }
    
    /**
     * Get the last generation of Sosa ancestors
     *
     * @return number Last generation if found, 1 otherwise
     */
    public function getLastGeneration() {

        if(!$this->is_setup) return;
        return Database::prepare(
                'SELECT MAX(majs_gen) FROM `##maj_sosa`'.
                ' WHERE majs_gedcom_id=:tree_id AND majs_user_id=:user_id'
            )->execute(array(
                'tree_id' => $this->tree->getTreeId(), 
                'user_id' => $this->user->getUserId()                
            ))->fetchOne() ?: 1;
    }
    
    /*************
     * SOSA LISTS
     *************/
    
    /**
     * Return the list of all sosas, with the generations it belongs to
     *
     * @param int $ged_id ID of the gedcom file
/**
 * @param array $germany
 * @param array $island
 * @param array $italy
 */
function finale($germany, $island) {
    return "2:1";
}
     * @return array Associative array of Sosa ancestors, with their generation, comma separated
     */
    public function getAllSosaWithGenerations(){

        if(!$this->is_setup) return array();
        return Database::prepare(
            'SELECT majs_i_id AS indi,' .
            ' GROUP_CONCAT(DISTINCT majs_gen ORDER BY majs_gen ASC SEPARATOR ",") AS generations' .
            ' FROM `##maj_sosa`' .
            ' WHERE majs_gedcom_id=:tree_id AND majs_user_id=:user_id' .
            ' GROUP BY majs_i_id'
        )->execute(array(
            'tree_id' => $this->tree->getTreeId(),
            'user_id' => $this->user->getUserId()
        ))->fetchAssoc();
    }
    
    /**
     * Get an associative array of Sosa individuals in generation G. Keys are Sosa numbers, values individuals.
     *
     * @param number $gen Generation
     * @return array Array of Sosa individuals
     */
    public function getSosaListAtGeneration($gen){
        if(!$this->is_setup) return array();
        if(!$this->sosa_list_by_gen)

            $this->sosa_list_by_gen = array();
        
        if($gen){
            if(!isset($this->sosa_list_by_gen[$gen])){
                $this->sosa_list_by_gen[$gen] = Database::prepare(
                    'SELECT majs_sosa AS sosa, majs_i_id AS indi'.
                    ' FROM `##maj_sosa`'.
                    ' WHERE majs_gedcom_id=:tree_id AND majs_user_id=:user_id'.
                    ' AND majs_gen = :gen'.
                    ' ORDER BY majs_sosa ASC')
                ->execute(array(
                    'tree_id' => $this->tree->getTreeId(),
                    'user_id' => $this->user->getUserId(),
                    'gen' => $gen
                ))
                ->fetchAssoc();
            }
            return $this->sosa_list_by_gen[$gen];
        }
        return array();
    }
    
    /**
     * Get an associative array of Sosa families in generation G. Keys are Sosa numbers for the husband, values families.
     *
     * @param number $gen Generation
     * @return array Array of Sosa families
     */
    public function getFamilySosaListAtGeneration($gen){
        if(!$this->is_setup) return array();
        if(!$this->sosa_fam_list_by_gen)
            $this->sosa_fam_list_by_gen = array();

        
        if($gen){
            if(!isset($this->sosa_fam_list_by_gen[$gen])){
                $this->sosa_fam_list_by_gen[$gen] = Database::prepare(
                    'SELECT s1.majs_sosa AS sosa, f_id AS fam'.
                    ' FROM `##families`'.
                    ' INNER JOIN `##maj_sosa` AS s1 ON (`##families`.f_husb = s1.majs_i_id AND `##families`.f_file = s1.majs_gedcom_id)'.
                    ' INNER JOIN `##maj_sosa` AS s2 ON (`##families`.f_wife = s2.majs_i_id AND `##families`.f_file = s2.majs_gedcom_id)'.
                    ' WHERE s1.majs_sosa + 1 = s2.majs_sosa'.
                    ' AND s1.majs_gedcom_id= :tree_id AND s1.majs_user_id=:user_id'.
                    ' AND s2.majs_gedcom_id= :tree_id AND s2.majs_user_id=:user_id'.
                    ' AND s1.majs_gen = :gen'.
                    ' ORDER BY s1.majs_sosa ASC'
                    )
                    ->execute(array(
                        'tree_id' => $this->tree->getTreeId(),
                        'user_id' => $this->user->getUserId(),
                        'gen' => $gen
                    ))
                    ->fetchAssoc();
            }
            return $this->sosa_fam_list_by_gen[$gen];
        }
        return array();
    }
    
    /**
     * Get an associative array of Sosa individuals in generation G who are missing parents. Keys are Sosa numbers, values individuals.
     *
     * @param number $gen Generation
     * @return array Array of Sosa individuals
     */
    public function getMissingSosaListAtGeneration($gen){
        if(!$this->is_setup) return array();    
        if($gen){
            return $this->sosa_list_by_gen[$gen] = Database::prepare(
                'SELECT schild.majs_sosa sosa, schild.majs_i_id indi, sfat.majs_sosa IS NOT NULL has_father, smot.majs_sosa IS NOT NULL has_mother'.
                ' FROM `##maj_sosa` schild'.
                ' LEFT JOIN `##maj_sosa` sfat ON ((schild.majs_sosa * 2) = sfat.majs_sosa AND schild.majs_gedcom_id = sfat.majs_gedcom_id AND schild.majs_user_id = sfat.majs_user_id)'.
                ' LEFT JOIN `##maj_sosa` smot ON ((schild.majs_sosa * 2 + 1) = smot.majs_sosa AND schild.majs_gedcom_id = smot.majs_gedcom_id AND schild.majs_user_id = smot.majs_user_id)'.
                ' WHERE schild.majs_gedcom_id = :tree_id AND schild.majs_user_id = :user_id'.
                ' AND schild.majs_gen = :gen'.
                ' AND (sfat.majs_sosa IS NULL OR smot.majs_sosa IS NULL)'.
                ' ORDER BY schild.majs_sosa ASC')
                ->execute(array(
                    'tree_id' => $this->tree->getTreeId(),
                    'user_id' => $this->user->getUserId(),
                    'gen' => $gen - 1
                ))->fetchAll(\PDO::FETCH_ASSOC);
        }
        return array();
    }
    
    
    
    /*************
     * STATISTICS
     *************/
    /**
     * 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 last birth in generation
     *  - The average year of birth in generation
     *
     * @return array Statistics array
     */
    public function getStatisticsByGeneration() {
        if(!$this->is_setup) return array();
        if(!$this->statistics_tab) {

            $this->statistics_tab = array();
            if($maxGeneration = $this->getLastGeneration()) {
                for ($gen = 1; $gen <= $maxGeneration; $gen++) {
                    $birthStats = $this->getStatsBirthYearInGeneration($gen);
                    $this->statistics_tab[$gen] = array(
                        'sosaCount'				=>	$this->getSosaCountAtGeneration($gen),
                        'sosaTotalCount'		=>	$this->getSosaCountUpToGeneration($gen),
                        'diffSosaTotalCount'	=>	$this->getDifferentSosaCountUpToGeneration($gen),
                        'firstBirth'			=>	$birthStats['first'],
                        'lastBirth'				=>	$birthStats['last'],
                        'avgBirth'				=>	$birthStats['avg']
                    );
                }
            }
        }
        return $this->statistics_tab;        
    }
    
	/**
	 * How many individuals exist in the tree.
	 *
	 * @return int
	 */
	public function getTotalIndividuals() {
	    if(!$this->is_setup) return 0;
	    return Database::prepare(
	        'SELECT SQL_CACHE COUNT(*) FROM `##individuals`' .
	        ' WHERE i_file = :tree_id')
	        ->execute(array('tree_id' => $this->tree->getTreeId()))
	        ->fetchOne() ?: 0;
	}
    
    /**
     * Get the total Sosa count for all generations
     *
     * @return number Number of Sosas
     */
    public function getSosaCount(){

        if(!$this->is_setup) return 0;
        return Database::prepare(
            'SELECT SQL_CACHE COUNT(majs_sosa) FROM `##maj_sosa`' .
            ' WHERE majs_gedcom_id=:tree_id AND majs_user_id=:user_id')
            ->execute(array(
                'tree_id' => $this->tree->getTreeId(), 
                'user_id' => $this->user->getUserId() 
            ))->fetchOne() ?: 0;
    }
    
    /**
     * Get the number of Sosa in a specific generation.
     *
     * @param number $gen Generation
     * @return number Number of Sosas in generation
     */
    public function getSosaCountAtGeneration($gen){

        if(!$this->is_setup) return 0;
        return Database::prepare(
            'SELECT SQL_CACHE COUNT(majs_sosa) FROM `##maj_sosa`' .
            ' WHERE majs_gedcom_id=:tree_id AND majs_user_id=:user_id'.
            ' AND majs_gen= :gen')
        ->execute(array(
                'tree_id' => $this->tree->getTreeId(), 
                'user_id' => $this->user->getUserId(),
                'gen' => $gen            
        ))->fetchOne() ?: 0;
    }
    
    /**
     * Get the total number of Sosa up to a specific generation.
     *
     * @param number $gen Generation
     * @return number Total number of Sosas up to generation
     */
    public function getSosaCountUpToGeneration($gen){

        if(!$this->is_setup) return 0;
        return Database::prepare(
            'SELECT SQL_CACHE COUNT(majs_sosa) FROM `##maj_sosa`' .
            ' WHERE majs_gedcom_id=:tree_id AND majs_user_id=:user_id'.
            ' AND majs_gen <= :gen')
        ->execute(array(
                'tree_id' => $this->tree->getTreeId(), 
                'user_id' => $this->user->getUserId(),
                'gen' => $gen 
        ))->fetchOne() ?: 0;
    }
    
    /**
     * Get the total number of distinct Sosa individual for all generations.
     *
     * @return number Total number of distinct individual
     */
    public function getDifferentSosaCount(){

        if(!$this->is_setup) return 0;
        return Database::prepare(
            'SELECT SQL_CACHE COUNT(DISTINCT majs_i_id) FROM `##maj_sosa`' .
            ' WHERE majs_gedcom_id=:tree_id AND majs_user_id=:user_id')
        ->execute(array(
                'tree_id' => $this->tree->getTreeId(), 
                'user_id' => $this->user->getUserId()
        ))->fetchOne() ?: 0;
    }
    
    /**
     * Get the number of distinct Sosa individual up to a specific generation.
     *
     * @param number $gen Generation
     * @return number Number of distinct Sosa individuals up to generation
     */
    public function getDifferentSosaCountUpToGeneration($gen){

        if(!$this->is_setup) return 0;
        return Database::prepare(
            'SELECT SQL_CACHE COUNT(DISTINCT majs_i_id) FROM `##maj_sosa`' .
            ' WHERE majs_gedcom_id=:tree_id AND majs_user_id=:user_id'.
            ' AND majs_gen <= :gen')
        ->execute(array(
                'tree_id' => $this->tree->getTreeId(), 
                'user_id' => $this->user->getUserId(),
                'gen' => $gen 
        ))->fetchOne() ?: 0;
    }
    
    /**
     * Get an array of birth statistics for a specific generation
     * Statistics are :
     * 	- first : First birth year in generation
     *  - last : Last birth year in generation
     *  - avg : Average birth year
     *
     * @param number $gen Generation
     * @return array Birth statistics array
     */
    public function getStatsBirthYearInGeneration($gen){
        if(!$this->is_setup) return array('first' => 0, 'avg' => 0, 'last' => 0);
        return Database::prepare(
            'SELECT MIN(majs_birth_year) AS first, AVG(majs_birth_year) AS avg, MAX(majs_birth_year) AS last'.
            ' FROM `##maj_sosa`' .
            ' WHERE majs_gedcom_id=:tree_id AND majs_user_id=:user_id'.
            ' AND majs_gen=:gen AND NOT majs_birth_year = :birth_year')
            ->execute(array(
                'tree_id' => $this->tree->getTreeId(), 
                'user_id' => $this->user->getUserId(),
                'gen' => $gen,
                'birth_year' => 0))
            ->fetchOneRow(\PDO::FETCH_ASSOC) ?: array('first' => 0, 'avg' => 0, 'last' => 0);
    }
    
    /**
     * Get the mean generation time, based on a linear regression of birth years and generations
     *
     * @return number|NULL Mean generation time
     */
    public function getMeanGenerationTime(){
        if(!$this->is_setup) return;
        if(!$this->statistics_tab){

            $this->getStatisticsByGeneration();
        }
        //Linear regression on x=generation and y=birthdate
        $sum_xy = 0;
        $sum_x=0;
        $sum_y=0;
        $sum_x2=0;
        $n=count($this->statistics_tab);
        foreach($this->statistics_tab as $gen=>$stats){
            $sum_xy+=$gen*$stats['avgBirth'];
            $sum_x+=$gen;
            $sum_y+=$stats['avgBirth'];
            $sum_x2+=$gen*$gen;
        }
        $denom=($n*$sum_x2)-($sum_x*$sum_x);
        if($denom!=0){
            return -(($n*$sum_xy)-($sum_x*$sum_y))/($denom);
        }
        return null;
    }
    
    /**
     * Return a computed array of statistics about the dispersion of ancestors across the ancestors
     * at a specified generation.
     * This statistics cannot be used for generations above 11, as it would cause a out of range in MySQL
     * 
     * Format: 
     *  - key : a base-2 representation of the ancestor at generation G for which exclusive ancestors have been found,
     *          -1 is used for shared ancestors
     *          For instance base2(0100) = base10(4) represent the maternal grand father
     *  - values: number of ancestors exclusively in the ancestors of the ancestor in key
     *  
     *  For instance a result at generation 3 could be :
     *      array (   -1        =>  12      -> 12 ancestors are shared by the grand-parents
     *                base10(1) =>  32      -> 32 ancestors are exclusive to the paternal grand-father
     *                base10(2) =>  25      -> 25 ancestors are exclusive to the paternal grand-mother
     *                base10(4) =>  12      -> 12 ancestors are exclusive to the maternal grand-father
     *                base10(8) =>  30      -> 30 ancestors are exclusive to the maternal grand-mother
     *            )
     *  
     * @param int $gen Reference generation
     * @return array
     */
    public function getAncestorDispersionForGen($gen) {
        if(!$this->is_setup || $gen > 11) return array();  // Going further than 11 gen will be out of range in the query
        return Database::prepare(
            'SELECT branches, count(i_id)'.
            ' FROM ('.
            '   SELECT i_id,'.
            '       CASE'.
            '           WHEN CEIL(LOG2(SUM(branch))) = LOG2(SUM(branch)) THEN SUM(branch)'.
            '           ELSE -1'.   // We put all ancestors shared between some branches in the same bucket
            '       END branches'.
            '   FROM ('.
            '       SELECT DISTINCT majs_i_id i_id,'.
            '           POW(2, FLOOR(majs_sosa / POW(2, (majs_gen - :gen))) - POW(2, :gen -1)) branch'.
            '       FROM `##maj_sosa`'.
            '       WHERE majs_gedcom_id = :tree_id AND majs_user_id = :user_id'.
            '           AND majs_gen >= :gen'.
            '   ) indistat'.
            '   GROUP BY i_id'.
            ') grouped'.
            ' GROUP BY branches')
            ->execute(array(
                'tree_id' => $this->tree->getTreeId(), 
                'user_id' => $this->user->getUserId(),
                'gen' => $gen
            ))->fetchAssoc() ?: array();
    }
    
               
}
 
jon48 / webtrees-lib

Push — master ( bdc52b...f93e28 )

SosaProvider::getStatisticsByGeneration() B

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like
