|
1
|
|
|
<?php |
|
2
|
|
|
/** |
|
3
|
|
|
* webtrees-lib: MyArtJaub library for webtrees |
|
4
|
|
|
* |
|
5
|
|
|
* @package MyArtJaub\Webtrees |
|
6
|
|
|
* @subpackage Sosa |
|
7
|
|
|
* @author Jonathan Jaubart <[email protected]> |
|
8
|
|
|
* @copyright Copyright (c) 2009-2016, Jonathan Jaubart |
|
9
|
|
|
* @license http://www.gnu.org/licenses/gpl.html GNU General Public License, version 3 |
|
10
|
|
|
*/ |
|
11
|
|
|
namespace MyArtJaub\Webtrees\Module\Sosa\Model; |
|
12
|
|
|
|
|
13
|
|
|
use Fisharebest\Webtrees\Tree; |
|
14
|
|
|
use Fisharebest\Webtrees\User; |
|
15
|
|
|
use Fisharebest\Webtrees\Database; |
|
16
|
|
|
use MyArtJaub\Webtrees\Functions\Functions; |
|
17
|
|
|
use Fisharebest\Webtrees\Auth; |
|
18
|
|
|
use Fisharebest\Webtrees\Individual; |
|
19
|
|
|
|
|
20
|
|
|
/** |
|
21
|
|
|
* Provide Sosa data access |
|
22
|
|
|
*/ |
|
23
|
|
|
class SosaProvider { |
|
24
|
|
|
|
|
25
|
|
|
/** |
|
26
|
|
|
* Maximum number of generation the database is able to hold. |
|
27
|
|
|
* @var int MAX_DB_GENERATIONS |
|
28
|
|
|
*/ |
|
29
|
|
|
const MAX_DB_GENERATIONS = 64; |
|
30
|
|
|
|
|
31
|
|
|
/** |
|
32
|
|
|
* System's default user (ID -1 in the database |
|
33
|
|
|
* @var User $default_user |
|
34
|
|
|
*/ |
|
35
|
|
|
protected static $default_user; |
|
36
|
|
|
|
|
37
|
|
|
/** |
|
38
|
|
|
* Reference user |
|
39
|
|
|
* @var User $user |
|
40
|
|
|
*/ |
|
41
|
|
|
protected $user; |
|
42
|
|
|
|
|
43
|
|
|
/** |
|
44
|
|
|
* Reference tree |
|
45
|
|
|
* @var Tree $tree |
|
46
|
|
|
*/ |
|
47
|
|
|
protected $tree; |
|
48
|
|
|
|
|
49
|
|
|
/** |
|
50
|
|
|
* Cached list of Sosa Individuals by generation |
|
51
|
|
|
* Format: key = generation, value = array ( sosa => Individual ID) |
|
52
|
|
|
* @var array $sosa_list_by_gen |
|
53
|
|
|
*/ |
|
54
|
|
|
protected $sosa_list_by_gen; |
|
55
|
|
|
|
|
56
|
|
|
/** |
|
57
|
|
|
* Cached list of Sosa Families by generation |
|
58
|
|
|
* Format: key = generation, value = array ( sosa => Family ID) |
|
59
|
|
|
* @var unknown $sosa_fam_list_by_gen |
|
60
|
|
|
*/ |
|
61
|
|
|
protected $sosa_fam_list_by_gen; |
|
62
|
|
|
|
|
63
|
|
|
/** |
|
64
|
|
|
* Cached array of statistics by generation |
|
65
|
|
|
* Format: key = generation, |
|
66
|
|
|
* value = array( |
|
67
|
|
|
* sosaCount, sosaTotalCount, diffSosaTotalCount, firstBirth, lastBirth, avgBirth |
|
68
|
|
|
* ) |
|
69
|
|
|
* @var array $statistics_tab |
|
70
|
|
|
*/ |
|
71
|
|
|
protected $statistics_tab; |
|
72
|
|
|
|
|
73
|
|
|
/** |
|
74
|
|
|
* Has the provider's initialisation completed |
|
75
|
|
|
* @var bool $is_setup |
|
76
|
|
|
*/ |
|
77
|
|
|
protected $is_setup; |
|
78
|
|
|
|
|
79
|
|
|
/** |
|
80
|
|
|
* Constructor for Sosa Provider. |
|
81
|
|
|
* A provider is defined in relation to a specific tree and reference user. |
|
82
|
|
|
* |
|
83
|
|
|
* @param Tree $tree |
|
84
|
|
|
* @param User $user |
|
85
|
|
|
*/ |
|
86
|
|
|
public function __construct(Tree $tree, User $user = null) { |
|
87
|
|
|
if(self::$default_user === null) |
|
88
|
|
|
self::$default_user = User::find(-1); |
|
89
|
|
|
|
|
90
|
|
|
$this->tree = $tree; |
|
91
|
|
|
$this->user = $user; |
|
92
|
|
|
$this->is_setup = true; |
|
93
|
|
|
if($this->user === null) $this->user = Auth::user(); |
|
94
|
|
|
if(strlen($this->user->getUserId()) == 0) $this->user = self::$default_user; |
|
95
|
|
|
|
|
96
|
|
|
// Check if the user, or the default user, has a root already setup; |
|
97
|
|
|
if(empty($this->getRootIndiId())) { |
|
98
|
|
|
if($this->user == self::$default_user) { // If the default user is not setup |
|
99
|
|
|
$this->is_setup = false; |
|
100
|
|
|
} |
|
101
|
|
|
else { |
|
102
|
|
|
$this->user = self::$default_user; |
|
103
|
|
|
$this->is_setup = $this->getRootIndiId() === null; |
|
104
|
|
|
} |
|
105
|
|
|
} |
|
106
|
|
|
} |
|
107
|
|
|
|
|
108
|
|
|
/** |
|
109
|
|
|
* Returns is the Provider has been successfully set up |
|
110
|
|
|
* @return bool |
|
111
|
|
|
*/ |
|
112
|
|
|
public function isSetup() { |
|
113
|
|
|
return $this->is_setup; |
|
114
|
|
|
} |
|
115
|
|
|
|
|
116
|
|
|
/** |
|
117
|
|
|
* Return the root individual ID for the reference tree and user. |
|
118
|
|
|
* @return string Individual ID |
|
119
|
|
|
*/ |
|
120
|
|
|
public function getRootIndiId() { |
|
121
|
|
|
return $this->tree->getUserPreference($this->user, 'MAJ_SOSA_ROOT_ID'); |
|
122
|
|
|
} |
|
123
|
|
|
|
|
124
|
|
|
/** |
|
125
|
|
|
* Return the root individual for the reference tree and user. |
|
126
|
|
|
* @return Individual Individual |
|
127
|
|
|
*/ |
|
128
|
|
|
public function getRootIndi() { |
|
129
|
|
|
$root_indi_id = $this->getRootIndiId(); |
|
130
|
|
|
if(!empty($root_indi_id)) { |
|
131
|
|
|
return Individual::getInstance($root_indi_id, $this->tree); |
|
132
|
|
|
} |
|
133
|
|
|
return null; |
|
134
|
|
|
} |
|
135
|
|
|
|
|
136
|
|
|
/***************** |
|
137
|
|
|
* DATA CRUD LAYER |
|
138
|
|
|
*****************/ |
|
139
|
|
|
|
|
140
|
|
|
/** |
|
141
|
|
|
* Remove all Sosa entries related to the gedcom file and user |
|
142
|
|
|
*/ |
|
143
|
|
|
public function deleteAll() { |
|
144
|
|
|
if(!$this->is_setup) return; |
|
145
|
|
|
Database::prepare( |
|
146
|
|
|
'DELETE FROM `##maj_sosa`'. |
|
147
|
|
|
' WHERE majs_gedcom_id= :tree_id and majs_user_id = :user_id ') |
|
148
|
|
|
->execute(array( |
|
149
|
|
|
'tree_id' => $this->tree->getTreeId(), |
|
150
|
|
|
'user_id' => $this->user->getUserId() |
|
151
|
|
|
)); |
|
152
|
|
|
} |
|
153
|
|
|
|
|
154
|
|
|
/** |
|
155
|
|
|
* Remove all ancestors of a sosa number |
|
156
|
|
|
* |
|
157
|
|
|
* @param int $sosa |
|
158
|
|
|
*/ |
|
159
|
|
|
public function deleteAncestors($sosa) { |
|
160
|
|
|
if(!$this->is_setup) return; |
|
161
|
|
|
$gen = Functions::getGeneration($sosa); |
|
162
|
|
|
Database::prepare( |
|
163
|
|
|
'DELETE FROM `##maj_sosa`'. |
|
164
|
|
|
' WHERE majs_gedcom_id=:tree_id and majs_user_id = :user_id' . |
|
165
|
|
|
' AND majs_gen >= :gen' . |
|
166
|
|
|
' AND FLOOR(majs_sosa / (POW(2, (majs_gen - :gen)))) = :sosa' |
|
167
|
|
|
)->execute(array( |
|
168
|
|
|
'tree_id' => $this->tree->getTreeId(), |
|
169
|
|
|
'user_id' => $this->user->getUserId(), |
|
170
|
|
|
'gen' => $gen, |
|
171
|
|
|
'sosa' => $sosa |
|
172
|
|
|
)); |
|
173
|
|
|
} |
|
174
|
|
|
|
|
175
|
|
|
/** |
|
176
|
|
|
* Insert (or update if already existing) a list of Sosa individuals |
|
177
|
|
|
* @param array $sosa_records |
|
178
|
|
|
*/ |
|
179
|
|
|
public function insertOrUpdate($sosa_records) { |
|
180
|
|
|
if(!$this->is_setup) return; |
|
181
|
|
|
|
|
182
|
|
|
$treeid = $this->tree->getTreeId(); |
|
183
|
|
|
$userid = $this->user->getUserId(); |
|
184
|
|
|
$questionmarks_table = array(); |
|
185
|
|
|
$values_table = array(); |
|
186
|
|
|
|
|
187
|
|
|
$i = 0; |
|
188
|
|
|
foreach ($sosa_records as $row) { |
|
189
|
|
|
$gen = Functions::getGeneration($row['sosa']); |
|
190
|
|
|
if($gen <= self::MAX_DB_GENERATIONS) { |
|
191
|
|
|
$questionmarks_table[] = |
|
192
|
|
|
'(:tree_id'.$i.', :user_id'.$i.', :sosa'.$i.', :indi_id'.$i.', :gen'.$i.', :byear'.$i.', :dyear'.$i.')'; |
|
193
|
|
|
$values_table = array_merge( |
|
194
|
|
|
$values_table, |
|
195
|
|
|
array( |
|
196
|
|
|
'tree_id'.$i => $treeid, |
|
197
|
|
|
'user_id'.$i => $userid, |
|
198
|
|
|
'sosa'.$i => $row['sosa'], |
|
199
|
|
|
'indi_id'.$i => $row['indi'], |
|
200
|
|
|
'gen'.$i => Functions::getGeneration($row['sosa']), |
|
201
|
|
|
'byear'.$i => $row['birth_year'], |
|
202
|
|
|
'dyear'.$i => $row['death_year'] |
|
203
|
|
|
) |
|
204
|
|
|
); |
|
205
|
|
|
} |
|
206
|
|
|
$i++; |
|
207
|
|
|
} |
|
208
|
|
|
|
|
209
|
|
|
$sql = 'REPLACE INTO `##maj_sosa`' . |
|
210
|
|
|
' (majs_gedcom_id, majs_user_id, majs_sosa, majs_i_id, majs_gen, majs_birth_year, majs_death_year)' . |
|
211
|
|
|
' VALUES '. implode(',', $questionmarks_table); |
|
212
|
|
|
Database::prepare($sql)->execute($values_table); |
|
213
|
|
|
} |
|
214
|
|
|
|
|
215
|
|
|
/**************** |
|
216
|
|
|
* SIMPLE QUERIES |
|
217
|
|
|
****************/ |
|
218
|
|
|
|
|
219
|
|
|
/** |
|
220
|
|
|
* Returns the list of Sosa numbers to which an individual is related. |
|
221
|
|
|
* Format: key = sosa number, value = generation for the Sosa number |
|
222
|
|
|
* |
|
223
|
|
|
* @param Individual $indi |
|
224
|
|
|
* @return array Array of sosa numbers |
|
225
|
|
|
*/ |
|
226
|
|
View Code Duplication |
public function getSosaNumbers(Individual $indi) { |
|
|
|
|
|
|
227
|
|
|
if(!$this->is_setup) return array(); |
|
228
|
|
|
return Database::prepare( |
|
229
|
|
|
'SELECT majs_sosa, majs_gen FROM `##maj_sosa`'. |
|
230
|
|
|
' WHERE majs_i_id=:indi_id AND majs_gedcom_id=:tree_id AND majs_user_id=:user_id' |
|
231
|
|
|
)->execute(array( |
|
232
|
|
|
'indi_id' => $indi->getXref(), |
|
233
|
|
|
'tree_id' => $this->tree->getTreeId(), |
|
234
|
|
|
'user_id' => $this->user->getUserId() |
|
235
|
|
|
))->fetchAssoc(); |
|
236
|
|
|
} |
|
237
|
|
|
|
|
238
|
|
|
/** |
|
239
|
|
|
* Get the last generation of Sosa ancestors |
|
240
|
|
|
* |
|
241
|
|
|
* @return number Last generation if found, 1 otherwise |
|
242
|
|
|
*/ |
|
243
|
|
View Code Duplication |
public function getLastGeneration() { |
|
|
|
|
|
|
244
|
|
|
if(!$this->is_setup) return; |
|
245
|
|
|
return Database::prepare( |
|
246
|
|
|
'SELECT MAX(majs_gen) FROM `##maj_sosa`'. |
|
247
|
|
|
' WHERE majs_gedcom_id=:tree_id AND majs_user_id=:user_id' |
|
248
|
|
|
)->execute(array( |
|
249
|
|
|
'tree_id' => $this->tree->getTreeId(), |
|
250
|
|
|
'user_id' => $this->user->getUserId() |
|
251
|
|
|
))->fetchOne() ?: 1; |
|
252
|
|
|
} |
|
253
|
|
|
|
|
254
|
|
|
/************* |
|
255
|
|
|
* SOSA LISTS |
|
256
|
|
|
*************/ |
|
257
|
|
|
|
|
258
|
|
|
/** |
|
259
|
|
|
* Return the list of all sosas, with the generations it belongs to |
|
260
|
|
|
* |
|
261
|
|
|
* @return array Associative array of Sosa ancestors, with their generation, comma separated |
|
262
|
|
|
*/ |
|
263
|
|
View Code Duplication |
public function getAllSosaWithGenerations(){ |
|
|
|
|
|
|
264
|
|
|
if(!$this->is_setup) return array(); |
|
265
|
|
|
return Database::prepare( |
|
266
|
|
|
'SELECT majs_i_id AS indi,' . |
|
267
|
|
|
' GROUP_CONCAT(DISTINCT majs_gen ORDER BY majs_gen ASC SEPARATOR ",") AS generations' . |
|
268
|
|
|
' FROM `##maj_sosa`' . |
|
269
|
|
|
' WHERE majs_gedcom_id=:tree_id AND majs_user_id=:user_id' . |
|
270
|
|
|
' GROUP BY majs_i_id' |
|
271
|
|
|
)->execute(array( |
|
272
|
|
|
'tree_id' => $this->tree->getTreeId(), |
|
273
|
|
|
'user_id' => $this->user->getUserId() |
|
274
|
|
|
))->fetchAssoc(); |
|
275
|
|
|
} |
|
276
|
|
|
|
|
277
|
|
|
/** |
|
278
|
|
|
* Get an associative array of Sosa individuals in generation G. Keys are Sosa numbers, values individuals. |
|
279
|
|
|
* |
|
280
|
|
|
* @param number $gen Generation |
|
281
|
|
|
* @return array Array of Sosa individuals |
|
282
|
|
|
*/ |
|
283
|
|
|
public function getSosaListAtGeneration($gen){ |
|
284
|
|
|
if(!$this->is_setup) return array(); |
|
285
|
|
|
if(!$this->sosa_list_by_gen) |
|
|
|
|
|
|
286
|
|
|
$this->sosa_list_by_gen = array(); |
|
287
|
|
|
|
|
288
|
|
|
if($gen){ |
|
289
|
|
|
if(!isset($this->sosa_list_by_gen[$gen])){ |
|
290
|
|
|
$this->sosa_list_by_gen[$gen] = Database::prepare( |
|
291
|
|
|
'SELECT majs_sosa AS sosa, majs_i_id AS indi'. |
|
292
|
|
|
' FROM `##maj_sosa`'. |
|
293
|
|
|
' WHERE majs_gedcom_id=:tree_id AND majs_user_id=:user_id'. |
|
294
|
|
|
' AND majs_gen = :gen'. |
|
295
|
|
|
' ORDER BY majs_sosa ASC') |
|
296
|
|
|
->execute(array( |
|
297
|
|
|
'tree_id' => $this->tree->getTreeId(), |
|
298
|
|
|
'user_id' => $this->user->getUserId(), |
|
299
|
|
|
'gen' => $gen |
|
300
|
|
|
)) |
|
301
|
|
|
->fetchAssoc(); |
|
302
|
|
|
} |
|
303
|
|
|
return $this->sosa_list_by_gen[$gen]; |
|
304
|
|
|
} |
|
305
|
|
|
return array(); |
|
306
|
|
|
} |
|
307
|
|
|
|
|
308
|
|
|
/** |
|
309
|
|
|
* Get an associative array of Sosa families in generation G. Keys are Sosa numbers for the husband, values families. |
|
310
|
|
|
* |
|
311
|
|
|
* @param number $gen Generation |
|
312
|
|
|
* @return array Array of Sosa families |
|
313
|
|
|
*/ |
|
314
|
|
|
public function getFamilySosaListAtGeneration($gen){ |
|
315
|
|
|
if(!$this->is_setup) return array(); |
|
316
|
|
|
if(!$this->sosa_fam_list_by_gen) |
|
317
|
|
|
$this->sosa_fam_list_by_gen = array(); |
|
|
|
|
|
|
318
|
|
|
|
|
319
|
|
|
if($gen){ |
|
320
|
|
|
if(!isset($this->sosa_fam_list_by_gen[$gen])){ |
|
321
|
|
|
$this->sosa_fam_list_by_gen[$gen] = Database::prepare( |
|
322
|
|
|
'SELECT s1.majs_sosa AS sosa, f_id AS fam'. |
|
323
|
|
|
' FROM `##families`'. |
|
324
|
|
|
' INNER JOIN `##maj_sosa` AS s1 ON (`##families`.f_husb = s1.majs_i_id AND `##families`.f_file = s1.majs_gedcom_id)'. |
|
325
|
|
|
' INNER JOIN `##maj_sosa` AS s2 ON (`##families`.f_wife = s2.majs_i_id AND `##families`.f_file = s2.majs_gedcom_id)'. |
|
326
|
|
|
' WHERE s1.majs_sosa + 1 = s2.majs_sosa'. |
|
327
|
|
|
' AND s1.majs_gedcom_id= :tree_id AND s1.majs_user_id=:user_id'. |
|
328
|
|
|
' AND s2.majs_gedcom_id= :tree_id AND s2.majs_user_id=:user_id'. |
|
329
|
|
|
' AND s1.majs_gen = :gen'. |
|
330
|
|
|
' ORDER BY s1.majs_sosa ASC' |
|
331
|
|
|
) |
|
332
|
|
|
->execute(array( |
|
333
|
|
|
'tree_id' => $this->tree->getTreeId(), |
|
334
|
|
|
'user_id' => $this->user->getUserId(), |
|
335
|
|
|
'gen' => $gen |
|
336
|
|
|
)) |
|
337
|
|
|
->fetchAssoc(); |
|
338
|
|
|
} |
|
339
|
|
|
return $this->sosa_fam_list_by_gen[$gen]; |
|
340
|
|
|
} |
|
341
|
|
|
return array(); |
|
342
|
|
|
} |
|
343
|
|
|
|
|
344
|
|
|
/** |
|
345
|
|
|
* Get an associative array of Sosa individuals in generation G who are missing parents. Keys are Sosa numbers, values individuals. |
|
346
|
|
|
* |
|
347
|
|
|
* @param number $gen Generation |
|
348
|
|
|
* @return array Array of Sosa individuals |
|
349
|
|
|
*/ |
|
350
|
|
|
public function getMissingSosaListAtGeneration($gen){ |
|
351
|
|
|
if(!$this->is_setup) return array(); |
|
352
|
|
|
if($gen){ |
|
353
|
|
|
return $this->sosa_list_by_gen[$gen] = Database::prepare( |
|
354
|
|
|
'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'. |
|
355
|
|
|
' FROM `##maj_sosa` schild'. |
|
356
|
|
|
' 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)'. |
|
357
|
|
|
' 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)'. |
|
358
|
|
|
' WHERE schild.majs_gedcom_id = :tree_id AND schild.majs_user_id = :user_id'. |
|
359
|
|
|
' AND schild.majs_gen = :gen'. |
|
360
|
|
|
' AND (sfat.majs_sosa IS NULL OR smot.majs_sosa IS NULL)'. |
|
361
|
|
|
' ORDER BY schild.majs_sosa ASC') |
|
362
|
|
|
->execute(array( |
|
363
|
|
|
'tree_id' => $this->tree->getTreeId(), |
|
364
|
|
|
'user_id' => $this->user->getUserId(), |
|
365
|
|
|
'gen' => $gen - 1 |
|
366
|
|
|
))->fetchAll(\PDO::FETCH_ASSOC); |
|
367
|
|
|
} |
|
368
|
|
|
return array(); |
|
369
|
|
|
} |
|
370
|
|
|
|
|
371
|
|
|
|
|
372
|
|
|
|
|
373
|
|
|
/************* |
|
374
|
|
|
* STATISTICS |
|
375
|
|
|
*************/ |
|
376
|
|
|
/** |
|
377
|
|
|
* Get the statistic array detailed by generation. |
|
378
|
|
|
* Statistics for each generation are: |
|
379
|
|
|
* - The number of Sosa in generation |
|
380
|
|
|
* - The number of Sosa up to generation |
|
381
|
|
|
* - The number of distinct Sosa up to generation |
|
382
|
|
|
* - The year of the first birth in generation |
|
383
|
|
|
* - The year of the last birth in generation |
|
384
|
|
|
* - The average year of birth in generation |
|
385
|
|
|
* |
|
386
|
|
|
* @return array Statistics array |
|
387
|
|
|
*/ |
|
388
|
|
|
public function getStatisticsByGeneration() { |
|
389
|
|
|
if(!$this->is_setup) return array(); |
|
390
|
|
|
if(!$this->statistics_tab) { |
|
|
|
|
|
|
391
|
|
|
$this->statistics_tab = array(); |
|
392
|
|
|
if($maxGeneration = $this->getLastGeneration()) { |
|
393
|
|
|
for ($gen = 1; $gen <= $maxGeneration; $gen++) { |
|
394
|
|
|
$birthStats = $this->getStatsBirthYearInGeneration($gen); |
|
395
|
|
|
$this->statistics_tab[$gen] = array( |
|
396
|
|
|
'sosaCount' => $this->getSosaCountAtGeneration($gen), |
|
397
|
|
|
'sosaTotalCount' => $this->getSosaCountUpToGeneration($gen), |
|
398
|
|
|
'diffSosaTotalCount' => $this->getDifferentSosaCountUpToGeneration($gen), |
|
399
|
|
|
'firstBirth' => $birthStats['first'], |
|
400
|
|
|
'lastBirth' => $birthStats['last'], |
|
401
|
|
|
'avgBirth' => $birthStats['avg'] |
|
402
|
|
|
); |
|
403
|
|
|
} |
|
404
|
|
|
} |
|
405
|
|
|
} |
|
406
|
|
|
return $this->statistics_tab; |
|
407
|
|
|
} |
|
408
|
|
|
|
|
409
|
|
|
/** |
|
410
|
|
|
* How many individuals exist in the tree. |
|
411
|
|
|
* |
|
412
|
|
|
* @return int |
|
413
|
|
|
*/ |
|
414
|
|
|
public function getTotalIndividuals() { |
|
415
|
|
|
if(!$this->is_setup) return 0; |
|
416
|
|
|
return Database::prepare( |
|
417
|
|
|
'SELECT SQL_CACHE COUNT(*) FROM `##individuals`' . |
|
418
|
|
|
' WHERE i_file = :tree_id') |
|
419
|
|
|
->execute(array('tree_id' => $this->tree->getTreeId())) |
|
420
|
|
|
->fetchOne() ?: 0; |
|
421
|
|
|
} |
|
422
|
|
|
|
|
423
|
|
|
/** |
|
424
|
|
|
* Get the total Sosa count for all generations |
|
425
|
|
|
* |
|
426
|
|
|
* @return number Number of Sosas |
|
427
|
|
|
*/ |
|
428
|
|
View Code Duplication |
public function getSosaCount(){ |
|
|
|
|
|
|
429
|
|
|
if(!$this->is_setup) return 0; |
|
430
|
|
|
return Database::prepare( |
|
431
|
|
|
'SELECT SQL_CACHE COUNT(majs_sosa) FROM `##maj_sosa`' . |
|
432
|
|
|
' WHERE majs_gedcom_id=:tree_id AND majs_user_id=:user_id') |
|
433
|
|
|
->execute(array( |
|
434
|
|
|
'tree_id' => $this->tree->getTreeId(), |
|
435
|
|
|
'user_id' => $this->user->getUserId() |
|
436
|
|
|
))->fetchOne() ?: 0; |
|
437
|
|
|
} |
|
438
|
|
|
|
|
439
|
|
|
/** |
|
440
|
|
|
* Get the number of Sosa in a specific generation. |
|
441
|
|
|
* |
|
442
|
|
|
* @param number $gen Generation |
|
443
|
|
|
* @return number Number of Sosas in generation |
|
444
|
|
|
*/ |
|
445
|
|
View Code Duplication |
public function getSosaCountAtGeneration($gen){ |
|
|
|
|
|
|
446
|
|
|
if(!$this->is_setup) return 0; |
|
447
|
|
|
return Database::prepare( |
|
448
|
|
|
'SELECT SQL_CACHE COUNT(majs_sosa) FROM `##maj_sosa`' . |
|
449
|
|
|
' WHERE majs_gedcom_id=:tree_id AND majs_user_id=:user_id'. |
|
450
|
|
|
' AND majs_gen= :gen') |
|
451
|
|
|
->execute(array( |
|
452
|
|
|
'tree_id' => $this->tree->getTreeId(), |
|
453
|
|
|
'user_id' => $this->user->getUserId(), |
|
454
|
|
|
'gen' => $gen |
|
455
|
|
|
))->fetchOne() ?: 0; |
|
456
|
|
|
} |
|
457
|
|
|
|
|
458
|
|
|
/** |
|
459
|
|
|
* Get the total number of Sosa up to a specific generation. |
|
460
|
|
|
* |
|
461
|
|
|
* @param number $gen Generation |
|
462
|
|
|
* @return number Total number of Sosas up to generation |
|
463
|
|
|
*/ |
|
464
|
|
View Code Duplication |
public function getSosaCountUpToGeneration($gen){ |
|
|
|
|
|
|
465
|
|
|
if(!$this->is_setup) return 0; |
|
466
|
|
|
return Database::prepare( |
|
467
|
|
|
'SELECT SQL_CACHE COUNT(majs_sosa) FROM `##maj_sosa`' . |
|
468
|
|
|
' WHERE majs_gedcom_id=:tree_id AND majs_user_id=:user_id'. |
|
469
|
|
|
' AND majs_gen <= :gen') |
|
470
|
|
|
->execute(array( |
|
471
|
|
|
'tree_id' => $this->tree->getTreeId(), |
|
472
|
|
|
'user_id' => $this->user->getUserId(), |
|
473
|
|
|
'gen' => $gen |
|
474
|
|
|
))->fetchOne() ?: 0; |
|
475
|
|
|
} |
|
476
|
|
|
|
|
477
|
|
|
/** |
|
478
|
|
|
* Get the total number of distinct Sosa individual for all generations. |
|
479
|
|
|
* |
|
480
|
|
|
* @return number Total number of distinct individual |
|
481
|
|
|
*/ |
|
482
|
|
View Code Duplication |
public function getDifferentSosaCount(){ |
|
|
|
|
|
|
483
|
|
|
if(!$this->is_setup) return 0; |
|
484
|
|
|
return Database::prepare( |
|
485
|
|
|
'SELECT SQL_CACHE COUNT(DISTINCT majs_i_id) FROM `##maj_sosa`' . |
|
486
|
|
|
' WHERE majs_gedcom_id=:tree_id AND majs_user_id=:user_id') |
|
487
|
|
|
->execute(array( |
|
488
|
|
|
'tree_id' => $this->tree->getTreeId(), |
|
489
|
|
|
'user_id' => $this->user->getUserId() |
|
490
|
|
|
))->fetchOne() ?: 0; |
|
491
|
|
|
} |
|
492
|
|
|
|
|
493
|
|
|
/** |
|
494
|
|
|
* Get the number of distinct Sosa individual up to a specific generation. |
|
495
|
|
|
* |
|
496
|
|
|
* @param number $gen Generation |
|
497
|
|
|
* @return number Number of distinct Sosa individuals up to generation |
|
498
|
|
|
*/ |
|
499
|
|
View Code Duplication |
public function getDifferentSosaCountUpToGeneration($gen){ |
|
|
|
|
|
|
500
|
|
|
if(!$this->is_setup) return 0; |
|
501
|
|
|
return Database::prepare( |
|
502
|
|
|
'SELECT SQL_CACHE COUNT(DISTINCT majs_i_id) FROM `##maj_sosa`' . |
|
503
|
|
|
' WHERE majs_gedcom_id=:tree_id AND majs_user_id=:user_id'. |
|
504
|
|
|
' AND majs_gen <= :gen') |
|
505
|
|
|
->execute(array( |
|
506
|
|
|
'tree_id' => $this->tree->getTreeId(), |
|
507
|
|
|
'user_id' => $this->user->getUserId(), |
|
508
|
|
|
'gen' => $gen |
|
509
|
|
|
))->fetchOne() ?: 0; |
|
510
|
|
|
} |
|
511
|
|
|
|
|
512
|
|
|
/** |
|
513
|
|
|
* Get an array of birth statistics for a specific generation |
|
514
|
|
|
* Statistics are : |
|
515
|
|
|
* - first : First birth year in generation |
|
516
|
|
|
* - last : Last birth year in generation |
|
517
|
|
|
* - avg : Average birth year |
|
518
|
|
|
* |
|
519
|
|
|
* @param number $gen Generation |
|
520
|
|
|
* @return array Birth statistics array |
|
521
|
|
|
*/ |
|
522
|
|
|
public function getStatsBirthYearInGeneration($gen){ |
|
523
|
|
|
if(!$this->is_setup) return array('first' => 0, 'avg' => 0, 'last' => 0); |
|
524
|
|
|
return Database::prepare( |
|
525
|
|
|
'SELECT MIN(majs_birth_year) AS first, AVG(majs_birth_year) AS avg, MAX(majs_birth_year) AS last'. |
|
526
|
|
|
' FROM `##maj_sosa`' . |
|
527
|
|
|
' WHERE majs_gedcom_id=:tree_id AND majs_user_id=:user_id'. |
|
528
|
|
|
' AND majs_gen=:gen AND NOT majs_birth_year = :birth_year') |
|
529
|
|
|
->execute(array( |
|
530
|
|
|
'tree_id' => $this->tree->getTreeId(), |
|
531
|
|
|
'user_id' => $this->user->getUserId(), |
|
532
|
|
|
'gen' => $gen, |
|
533
|
|
|
'birth_year' => 0)) |
|
534
|
|
|
->fetchOneRow(\PDO::FETCH_ASSOC) ?: array('first' => 0, 'avg' => 0, 'last' => 0); |
|
535
|
|
|
} |
|
536
|
|
|
|
|
537
|
|
|
/** |
|
538
|
|
|
* Get the mean generation time, based on a linear regression of birth years and generations |
|
539
|
|
|
* |
|
540
|
|
|
* @return number|NULL Mean generation time |
|
541
|
|
|
*/ |
|
542
|
|
|
public function getMeanGenerationTime(){ |
|
543
|
|
|
if(!$this->is_setup) return; |
|
544
|
|
|
if(!$this->statistics_tab){ |
|
|
|
|
|
|
545
|
|
|
$this->getStatisticsByGeneration(); |
|
546
|
|
|
} |
|
547
|
|
|
//Linear regression on x=generation and y=birthdate |
|
548
|
|
|
$sum_xy = 0; |
|
549
|
|
|
$sum_x=0; |
|
550
|
|
|
$sum_y=0; |
|
551
|
|
|
$sum_x2=0; |
|
552
|
|
|
$n=count($this->statistics_tab); |
|
553
|
|
|
foreach($this->statistics_tab as $gen=>$stats){ |
|
554
|
|
|
$sum_xy+=$gen*$stats['avgBirth']; |
|
555
|
|
|
$sum_x+=$gen; |
|
556
|
|
|
$sum_y+=$stats['avgBirth']; |
|
557
|
|
|
$sum_x2+=$gen*$gen; |
|
558
|
|
|
} |
|
559
|
|
|
$denom=($n*$sum_x2)-($sum_x*$sum_x); |
|
560
|
|
|
if($denom!=0){ |
|
561
|
|
|
return -(($n*$sum_xy)-($sum_x*$sum_y))/($denom); |
|
562
|
|
|
} |
|
563
|
|
|
return null; |
|
564
|
|
|
} |
|
565
|
|
|
|
|
566
|
|
|
/** |
|
567
|
|
|
* Return a computed array of statistics about the dispersion of ancestors across the ancestors |
|
568
|
|
|
* at a specified generation. |
|
569
|
|
|
* This statistics cannot be used for generations above 11, as it would cause a out of range in MySQL |
|
570
|
|
|
* |
|
571
|
|
|
* Format: |
|
572
|
|
|
* - key : a base-2 representation of the ancestor at generation G for which exclusive ancestors have been found, |
|
573
|
|
|
* -1 is used for shared ancestors |
|
574
|
|
|
* For instance base2(0100) = base10(4) represent the maternal grand father |
|
575
|
|
|
* - values: number of ancestors exclusively in the ancestors of the ancestor in key |
|
576
|
|
|
* |
|
577
|
|
|
* For instance a result at generation 3 could be : |
|
578
|
|
|
* array ( -1 => 12 -> 12 ancestors are shared by the grand-parents |
|
579
|
|
|
* base10(1) => 32 -> 32 ancestors are exclusive to the paternal grand-father |
|
580
|
|
|
* base10(2) => 25 -> 25 ancestors are exclusive to the paternal grand-mother |
|
581
|
|
|
* base10(4) => 12 -> 12 ancestors are exclusive to the maternal grand-father |
|
582
|
|
|
* base10(8) => 30 -> 30 ancestors are exclusive to the maternal grand-mother |
|
583
|
|
|
* ) |
|
584
|
|
|
* |
|
585
|
|
|
* @param int $gen Reference generation |
|
586
|
|
|
* @return array |
|
587
|
|
|
*/ |
|
588
|
|
|
public function getAncestorDispersionForGen($gen) { |
|
589
|
|
|
if(!$this->is_setup || $gen > 11) return array(); // Going further than 11 gen will be out of range in the query |
|
590
|
|
|
return Database::prepare( |
|
591
|
|
|
'SELECT branches, count(i_id)'. |
|
592
|
|
|
' FROM ('. |
|
593
|
|
|
' SELECT i_id,'. |
|
594
|
|
|
' CASE'. |
|
595
|
|
|
' WHEN CEIL(LOG2(SUM(branch))) = LOG2(SUM(branch)) THEN SUM(branch)'. |
|
596
|
|
|
' ELSE -1'. // We put all ancestors shared between some branches in the same bucket |
|
597
|
|
|
' END branches'. |
|
598
|
|
|
' FROM ('. |
|
599
|
|
|
' SELECT DISTINCT majs_i_id i_id,'. |
|
600
|
|
|
' POW(2, FLOOR(majs_sosa / POW(2, (majs_gen - :gen))) - POW(2, :gen -1)) branch'. |
|
601
|
|
|
' FROM `##maj_sosa`'. |
|
602
|
|
|
' WHERE majs_gedcom_id = :tree_id AND majs_user_id = :user_id'. |
|
603
|
|
|
' AND majs_gen >= :gen'. |
|
604
|
|
|
' ) indistat'. |
|
605
|
|
|
' GROUP BY i_id'. |
|
606
|
|
|
') grouped'. |
|
607
|
|
|
' GROUP BY branches') |
|
608
|
|
|
->execute(array( |
|
609
|
|
|
'tree_id' => $this->tree->getTreeId(), |
|
610
|
|
|
'user_id' => $this->user->getUserId(), |
|
611
|
|
|
'gen' => $gen |
|
612
|
|
|
))->fetchAssoc() ?: array(); |
|
613
|
|
|
} |
|
614
|
|
|
|
|
615
|
|
|
/** |
|
616
|
|
|
* Return an array of the most duplicated root Sosa ancestors. |
|
617
|
|
|
* The number of ancestors to return is limited by the parameter $limit. |
|
618
|
|
|
* If several individuals are tied when reaching the limit, none of them are returned, |
|
619
|
|
|
* which means that there can be less individuals returned than requested. |
|
620
|
|
|
* |
|
621
|
|
|
* Format: |
|
622
|
|
|
* - key : root Sosa individual |
|
623
|
|
|
* - value: number of duplications of the ancestor (e.g. 3 if it appears 3 times) |
|
624
|
|
|
* |
|
625
|
|
|
* @param number $limit Maximum number of individuals to return |
|
626
|
|
|
* @return array |
|
627
|
|
|
*/ |
|
628
|
|
|
public function getTopMultiSosaAncestorsNoTies($limit) { |
|
629
|
|
|
if(!$this->is_setup) return array(); |
|
630
|
|
|
return Database::prepare( |
|
631
|
|
|
'SELECT sosa_i_id, sosa_count FROM ('. |
|
632
|
|
|
' SELECT'. |
|
633
|
|
|
' top_sosa.sosa_i_id, top_sosa.sosa_count, top_sosa.sosa_min,'. |
|
634
|
|
|
' @keep := IF(@prev_count = 0 OR sosa_count = @prev_count, @keep, 1) AS keep,'. |
|
635
|
|
|
' @prev_count := top_sosa.sosa_count AS prev_count'. |
|
636
|
|
|
' FROM ('. |
|
637
|
|
|
' SELECT'. |
|
638
|
|
|
' sosa.majs_i_id sosa_i_id,'. |
|
639
|
|
|
' COUNT(sosa.majs_sosa) sosa_count,'. |
|
640
|
|
|
' MIN(sosa.majs_sosa) sosa_min'. |
|
641
|
|
|
' FROM ##maj_sosa AS sosa'. |
|
642
|
|
|
' LEFT JOIN ##maj_sosa AS sosa_fat ON sosa_fat.majs_sosa = 2 * sosa.majs_sosa'. // Link to sosa's father |
|
643
|
|
|
' AND sosa.majs_gedcom_id = sosa_fat.majs_gedcom_id'. |
|
644
|
|
|
' AND sosa.majs_user_id = sosa_fat.majs_user_id'. |
|
645
|
|
|
' LEFT JOIN ##maj_sosa AS sosa_mot on sosa_mot.majs_sosa = (2 * sosa.majs_sosa + 1)'. // Link to sosa's mother |
|
646
|
|
|
' AND sosa.majs_gedcom_id = sosa_fat.majs_gedcom_id'. |
|
647
|
|
|
' AND sosa.majs_user_id = sosa_fat.majs_user_id'. |
|
648
|
|
|
' WHERE sosa.majs_gedcom_id = :tree_id'. |
|
649
|
|
|
' AND sosa.majs_user_id = :user_id'. |
|
650
|
|
|
' AND sosa_fat.majs_sosa IS NULL'. // We keep only root individuals, i.e. those with no father or mother |
|
651
|
|
|
' AND sosa_mot.majs_sosa IS NULL'. |
|
652
|
|
|
' GROUP BY sosa.majs_i_id'. |
|
653
|
|
|
' HAVING COUNT(sosa.majs_sosa) > 1'. // Limit to the duplicate sosas. |
|
654
|
|
|
' ORDER BY COUNT(sosa.majs_sosa) DESC'. |
|
655
|
|
|
' LIMIT ' . ($limit + 1) . // We want to select one more than required |
|
656
|
|
|
' ) AS top_sosa,'. |
|
657
|
|
|
' (SELECT @prev_count := 0, @keep := 0) x'. |
|
658
|
|
|
' ORDER BY top_sosa.sosa_count ASC'. |
|
659
|
|
|
' ) top_sosa_list'. |
|
660
|
|
|
' WHERE keep = 1'. |
|
661
|
|
|
' ORDER BY sosa_count DESC, sosa_min ASC' |
|
662
|
|
|
)->execute(array( |
|
663
|
|
|
'tree_id' => $this->tree->getTreeId(), |
|
664
|
|
|
'user_id' => $this->user->getUserId() |
|
665
|
|
|
))->fetchAssoc() ?: array(); |
|
666
|
|
|
} |
|
667
|
|
|
|
|
668
|
|
|
|
|
669
|
|
|
} |
|
670
|
|
|
|
jon48 /
webtrees-lib
Loading history...
Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation.
You can also find more detailed suggestions in the “Code” section of your repository.