|
1
|
|
|
<?php |
|
2
|
|
|
/** |
|
3
|
|
|
* webtrees: online genealogy |
|
4
|
|
|
* Copyright (C) 2017 webtrees development team |
|
5
|
|
|
* This program is free software: you can redistribute it and/or modify |
|
6
|
|
|
* it under the terms of the GNU General Public License as published by |
|
7
|
|
|
* the Free Software Foundation, either version 3 of the License, or |
|
8
|
|
|
* (at your option) any later version. |
|
9
|
|
|
* This program is distributed in the hope that it will be useful, |
|
10
|
|
|
* but WITHOUT ANY WARRANTY; without even the implied warranty of |
|
11
|
|
|
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
|
12
|
|
|
* GNU General Public License for more details. |
|
13
|
|
|
* You should have received a copy of the GNU General Public License |
|
14
|
|
|
* along with this program. If not, see <http://www.gnu.org/licenses/>. |
|
15
|
|
|
*/ |
|
16
|
|
|
namespace Fisharebest\Webtrees\Query; |
|
17
|
|
|
|
|
18
|
|
|
use Fisharebest\Webtrees\Database; |
|
19
|
|
|
use Fisharebest\Webtrees\Family; |
|
20
|
|
|
use Fisharebest\Webtrees\I18N; |
|
21
|
|
|
use Fisharebest\Webtrees\Individual; |
|
22
|
|
|
use Fisharebest\Webtrees\Tree; |
|
23
|
|
|
|
|
24
|
|
|
/** |
|
25
|
|
|
* Generate lists for indilist.php and famlist.php |
|
26
|
|
|
*/ |
|
27
|
|
|
class QueryName { |
|
28
|
|
|
/** |
|
29
|
|
|
* Get a list of initial letters, for lists of names. |
|
30
|
|
|
* |
|
31
|
|
|
* @param string $locale Return the alphabet for this locale |
|
32
|
|
|
* |
|
33
|
|
|
* @return string[] |
|
34
|
|
|
*/ |
|
35
|
|
View Code Duplication |
private static function getAlphabetForLocale($locale) { |
|
|
|
|
|
|
36
|
|
|
switch ($locale) { |
|
37
|
|
|
case 'ar': |
|
38
|
|
|
return [ |
|
39
|
|
|
'ا', 'ب', 'ت', 'ث', 'ج', 'ح', 'خ', 'د', 'ذ', 'ر', 'ز', 'س', 'ش', 'ص', 'ض', 'ط', 'ظ', 'ع', 'غ', 'ف', 'ق', 'ك', 'ل', 'م', 'ن', 'ه', 'و', 'ي', 'آ', 'ة', 'ى', 'ی', |
|
40
|
|
|
]; |
|
41
|
|
|
case 'cs': |
|
42
|
|
|
return [ |
|
43
|
|
|
'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'CH', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', |
|
44
|
|
|
]; |
|
45
|
|
|
case 'da': |
|
46
|
|
|
case 'nb': |
|
47
|
|
|
case 'nn': |
|
48
|
|
|
return [ |
|
49
|
|
|
'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'Æ', 'Ø', 'Å', |
|
50
|
|
|
]; |
|
51
|
|
|
case 'el': |
|
52
|
|
|
return [ |
|
53
|
|
|
'Α', 'Β', 'Γ', 'Δ', 'Ε', 'Ζ', 'Η', 'Θ', 'Ι', 'Κ', 'Λ', 'Μ', 'Ν', 'Ξ', 'Ο', 'Π', 'Ρ', 'Σ', 'Τ', 'Υ', 'Φ', 'Χ', 'Ψ', 'Ω', |
|
54
|
|
|
]; |
|
55
|
|
|
case 'es': |
|
56
|
|
|
return [ |
|
57
|
|
|
'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'Ñ', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', |
|
58
|
|
|
]; |
|
59
|
|
|
case 'et': |
|
60
|
|
|
return [ |
|
61
|
|
|
'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'Š', 'Z', 'Ž', 'T', 'U', 'V', 'W', 'Õ', 'Ä', 'Ö', 'Ü', 'X', 'Y', |
|
62
|
|
|
]; |
|
63
|
|
|
case 'fi': |
|
64
|
|
|
case 'sv': |
|
65
|
|
|
return [ |
|
66
|
|
|
'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'Å', 'Ä', 'Ö', |
|
67
|
|
|
]; |
|
68
|
|
|
case 'he': |
|
69
|
|
|
return [ |
|
70
|
|
|
'א', 'ב', 'ג', 'ד', 'ה', 'ו', 'ז', 'ח', 'ט', 'י', 'כ', 'ל', 'מ', 'נ', 'ס', 'ע', 'פ', 'צ', 'ק', 'ר', 'ש', 'ת', |
|
71
|
|
|
]; |
|
72
|
|
|
case 'hu': |
|
73
|
|
|
return [ |
|
74
|
|
|
'A', 'B', 'C', 'CS', 'D', 'DZ', 'DZS', 'E', 'F', 'G', 'GY', 'H', 'I', 'J', 'K', 'L', 'LY', 'M', 'N', 'NY', 'O', 'Ö', 'P', 'Q', 'R', 'S', 'SZ', 'T', 'TY', 'U', 'Ü', 'V', 'W', 'X', 'Y', 'Z', 'ZS', |
|
75
|
|
|
]; |
|
76
|
|
|
case 'lt': |
|
77
|
|
|
return [ |
|
78
|
|
|
'A', 'Ą', 'B', 'C', 'Č', 'D', 'E', 'Ę', 'Ė', 'F', 'G', 'H', 'I', 'Y', 'Į', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'R', 'S', 'Š', 'T', 'U', 'Ų', 'Ū', 'V', 'Z', 'Ž', |
|
79
|
|
|
]; |
|
80
|
|
|
case 'nl': |
|
81
|
|
|
return [ |
|
82
|
|
|
'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'IJ', |
|
83
|
|
|
]; |
|
84
|
|
|
case 'pl': |
|
85
|
|
|
return [ |
|
86
|
|
|
'A', 'B', 'C', 'Ć', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'Ł', 'M', 'N', 'O', 'Ó', 'P', 'Q', 'R', 'S', 'Ś', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'Ź', 'Ż', |
|
87
|
|
|
]; |
|
88
|
|
|
case 'ro': |
|
89
|
|
|
return [ |
|
90
|
|
|
'A', 'Ă', 'Â', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'Î', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'Ş', 'T', 'Ţ', 'U', 'V', 'W', 'X', 'Y', 'Z', |
|
91
|
|
|
]; |
|
92
|
|
|
case 'ru': |
|
93
|
|
|
return [ |
|
94
|
|
|
'А', 'Б', 'В', 'Г', 'Д', 'Е', 'Ё', 'Ж', 'З', 'И', 'Й', 'К', 'Л', 'М', 'Н', 'О', 'П', 'Р', 'С', 'Т', 'У', 'Ф', 'Х', 'Ц', 'Ч', 'Ш', 'Щ', 'Ъ', 'Ы', 'Ь', 'Э', 'Ю', 'Я', |
|
95
|
|
|
]; |
|
96
|
|
|
case 'sk': |
|
97
|
|
|
return [ |
|
98
|
|
|
'A', 'Á', 'Ä', 'B', 'C', 'Č', 'D', 'Ď', 'E', 'É', 'F', 'G', 'H', 'I', 'Í', 'J', 'K', 'L', 'Ľ', 'Ĺ', 'M', 'N', 'Ň', 'O', 'Ó', 'Ô', 'P', 'Q', 'R', 'Ŕ', 'S', 'Š', 'T', 'Ť', 'U', 'Ú', 'V', 'W', 'X', 'Y', 'Ý', 'Z', 'Ž', |
|
99
|
|
|
]; |
|
100
|
|
|
case 'sl': |
|
101
|
|
|
return [ |
|
102
|
|
|
'A', 'B', 'C', 'Č', 'Ć', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'Š', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'Ž', |
|
103
|
|
|
]; |
|
104
|
|
|
case 'sr': |
|
105
|
|
|
return [ |
|
106
|
|
|
'A', 'B', 'C', 'Č', 'Ć', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'Š', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'Ž', |
|
107
|
|
|
]; |
|
108
|
|
|
case 'tr': |
|
109
|
|
|
return [ |
|
110
|
|
|
'A', 'B', 'C', 'Ç', 'D', 'E', 'F', 'G', 'Ğ', 'H', 'I', 'İ', 'J', 'K', 'L', 'M', 'N', 'O', 'Ö', 'P', 'R', 'S', 'Ş', 'T', 'U', 'Ü', 'V', 'Y', 'Z', |
|
111
|
|
|
]; |
|
112
|
|
|
default: |
|
113
|
|
|
return [ |
|
114
|
|
|
'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', |
|
115
|
|
|
]; |
|
116
|
|
|
} |
|
117
|
|
|
} |
|
118
|
|
|
|
|
119
|
|
|
/** |
|
120
|
|
|
* Get the initial letter of a name, taking care of multi-letter sequences and equivalences. |
|
121
|
|
|
* |
|
122
|
|
|
* @param string $name |
|
123
|
|
|
* |
|
124
|
|
|
* @return string |
|
125
|
|
|
*/ |
|
126
|
|
View Code Duplication |
public static function initialLetter($name) { |
|
|
|
|
|
|
127
|
|
|
$name = I18N::strtoupper($name); |
|
128
|
|
|
switch (WT_LOCALE) { |
|
129
|
|
|
case 'cs': |
|
130
|
|
|
if (substr($name, 0, 2) == 'CH') { |
|
131
|
|
|
return 'CH'; |
|
132
|
|
|
} |
|
133
|
|
|
break; |
|
134
|
|
|
case 'da': |
|
135
|
|
|
case 'nb': |
|
136
|
|
|
case 'nn': |
|
137
|
|
|
if (substr($name, 0, 2) == 'AA') { |
|
138
|
|
|
return 'Å'; |
|
139
|
|
|
} |
|
140
|
|
|
break; |
|
141
|
|
|
case 'hu': |
|
142
|
|
|
if (substr($name, 0, 2) == 'CS') { |
|
143
|
|
|
return 'CS'; |
|
144
|
|
|
} elseif (substr($name, 0, 3) == 'DZS') { |
|
145
|
|
|
return 'DZS'; |
|
146
|
|
|
} elseif (substr($name, 0, 2) == 'DZ') { |
|
147
|
|
|
return 'DZ'; |
|
148
|
|
|
} elseif (substr($name, 0, 2) == 'GY') { |
|
149
|
|
|
return 'GY'; |
|
150
|
|
|
} elseif (substr($name, 0, 2) == 'LY') { |
|
151
|
|
|
return 'LY'; |
|
152
|
|
|
} elseif (substr($name, 0, 2) == 'NY') { |
|
153
|
|
|
return 'NY'; |
|
154
|
|
|
} elseif (substr($name, 0, 2) == 'SZ') { |
|
155
|
|
|
return 'SZ'; |
|
156
|
|
|
} elseif (substr($name, 0, 2) == 'TY') { |
|
157
|
|
|
return 'TY'; |
|
158
|
|
|
} elseif (substr($name, 0, 2) == 'ZS') { |
|
159
|
|
|
return 'ZS'; |
|
160
|
|
|
} |
|
161
|
|
|
break; |
|
162
|
|
|
case 'nl': |
|
163
|
|
|
if (substr($name, 0, 2) == 'IJ') { |
|
164
|
|
|
return 'IJ'; |
|
165
|
|
|
} |
|
166
|
|
|
break; |
|
167
|
|
|
} |
|
168
|
|
|
// No special rules - just take the first character |
|
169
|
|
|
return mb_substr($name, 0, 1); |
|
170
|
|
|
} |
|
171
|
|
|
|
|
172
|
|
|
/** |
|
173
|
|
|
* Generate SQL to match a given letter, taking care of cases that |
|
174
|
|
|
* are not covered by the collation setting. |
|
175
|
|
|
* |
|
176
|
|
|
* We must consider: |
|
177
|
|
|
* potential substrings, such as Czech "CH" and "C" |
|
178
|
|
|
* equivalent letters, such as Danish "AA" and "Å" |
|
179
|
|
|
* |
|
180
|
|
|
* We COULD write something that handles all languages generically, |
|
181
|
|
|
* but its performance would most likely be poor. |
|
182
|
|
|
* |
|
183
|
|
|
* For languages that don't appear in this list, we could write |
|
184
|
|
|
* simpler versions of the surnameAlpha() and givenAlpha() functions, |
|
185
|
|
|
* but it gives no noticable improvement in performance. |
|
186
|
|
|
* |
|
187
|
|
|
* @param string $field |
|
188
|
|
|
* @param string $letter |
|
189
|
|
|
* |
|
190
|
|
|
* @return string |
|
191
|
|
|
*/ |
|
192
|
|
View Code Duplication |
private static function getInitialSql($field, $letter) { |
|
|
|
|
|
|
193
|
|
|
switch (WT_LOCALE) { |
|
194
|
|
|
case 'cs': |
|
195
|
|
|
switch ($letter) { |
|
196
|
|
|
case 'C': return $field . " LIKE 'C%' COLLATE " . I18N::collation() . " AND " . $field . " NOT LIKE 'CH%' COLLATE " . I18N::collation(); |
|
197
|
|
|
} |
|
198
|
|
|
break; |
|
199
|
|
|
case 'da': |
|
200
|
|
|
case 'nb': |
|
201
|
|
|
case 'nn': |
|
202
|
|
|
switch ($letter) { |
|
203
|
|
|
// AA gets listed under Å |
|
204
|
|
|
case 'A': return $field . " LIKE 'A%' COLLATE " . I18N::collation() . " AND " . $field . " NOT LIKE 'AA%' COLLATE " . I18N::collation(); |
|
205
|
|
|
case 'Å': return "(" . $field . " LIKE 'Å%' COLLATE " . I18N::collation() . " OR " . $field . " LIKE 'AA%' COLLATE " . I18N::collation() . ")"; |
|
206
|
|
|
} |
|
207
|
|
|
break; |
|
208
|
|
|
case 'hu': |
|
209
|
|
|
switch ($letter) { |
|
210
|
|
|
case 'C': return $field . " LIKE 'C%' COLLATE " . I18N::collation() . " AND " . $field . " NOT LIKE 'CS%' COLLATE " . I18N::collation(); |
|
211
|
|
|
case 'D': return $field . " LIKE 'D%' COLLATE " . I18N::collation() . " AND " . $field . " NOT LIKE 'DZ%' COLLATE " . I18N::collation(); |
|
212
|
|
|
case 'DZ': return $field . " LIKE 'DZ%' COLLATE " . I18N::collation() . " AND " . $field . " NOT LIKE 'DZS%' COLLATE " . I18N::collation(); |
|
213
|
|
|
case 'G': return $field . " LIKE 'G%' COLLATE " . I18N::collation() . " AND " . $field . " NOT LIKE 'GY%' COLLATE " . I18N::collation(); |
|
214
|
|
|
case 'L': return $field . " LIKE 'L%' COLLATE " . I18N::collation() . " AND " . $field . " NOT LIKE 'LY%' COLLATE " . I18N::collation(); |
|
215
|
|
|
case 'N': return $field . " LIKE 'N%' COLLATE " . I18N::collation() . " AND " . $field . " NOT LIKE 'NY%' COLLATE " . I18N::collation(); |
|
216
|
|
|
case 'S': return $field . " LIKE 'S%' COLLATE " . I18N::collation() . " AND " . $field . " NOT LIKE 'SZ%' COLLATE " . I18N::collation(); |
|
217
|
|
|
case 'T': return $field . " LIKE 'T%' COLLATE " . I18N::collation() . " AND " . $field . " NOT LIKE 'TY%' COLLATE " . I18N::collation(); |
|
218
|
|
|
case 'Z': return $field . " LIKE 'Z%' COLLATE " . I18N::collation() . " AND " . $field . " NOT LIKE 'ZS%' COLLATE " . I18N::collation(); |
|
219
|
|
|
} |
|
220
|
|
|
break; |
|
221
|
|
|
case 'nl': |
|
222
|
|
|
switch ($letter) { |
|
223
|
|
|
case 'I': return $field . " LIKE 'I%' COLLATE " . I18N::collation() . " AND " . $field . " NOT LIKE 'IJ%' COLLATE " . I18N::collation(); |
|
224
|
|
|
} |
|
225
|
|
|
break; |
|
226
|
|
|
} |
|
227
|
|
|
|
|
228
|
|
|
// Easy cases: the MySQL collation rules take care of it |
|
229
|
|
|
return "$field LIKE CONCAT('@'," . Database::quote($letter) . ",'%') COLLATE " . I18N::collation() . " ESCAPE '@'"; |
|
|
|
|
|
|
230
|
|
|
} |
|
231
|
|
|
|
|
232
|
|
|
/** |
|
233
|
|
|
* Get a list of initial surname letters for indilist.php and famlist.php |
|
234
|
|
|
* |
|
235
|
|
|
* @param Tree $tree Find surnames from this tree |
|
236
|
|
|
* @param bool $marnm if set, include married names |
|
237
|
|
|
* @param bool $fams if set, only consider individuals with FAMS records |
|
238
|
|
|
* @param bool $totals if set, count the number of names beginning with each letter |
|
239
|
|
|
* |
|
240
|
|
|
* @return int[] |
|
241
|
|
|
*/ |
|
242
|
|
|
public static function surnameAlpha(Tree $tree, $marnm, $fams, $totals = true) { |
|
243
|
|
|
$alphas = []; |
|
244
|
|
|
|
|
245
|
|
|
$sql = |
|
246
|
|
|
"SELECT SQL_CACHE COUNT(n_id)" . |
|
247
|
|
|
" FROM `##name` " . |
|
248
|
|
|
($fams ? " JOIN `##link` ON (n_id=l_from AND n_file=l_file AND l_type='FAMS') " : "") . |
|
249
|
|
|
" WHERE n_file=" . $tree->getTreeId() . |
|
250
|
|
|
($marnm ? "" : " AND n_type!='_MARNM'"); |
|
251
|
|
|
|
|
252
|
|
|
// Fetch all the letters in our alphabet, whether or not there |
|
253
|
|
|
// are any names beginning with that letter. It looks better to |
|
254
|
|
|
// show the full alphabet, rather than omitting rare letters such as X |
|
255
|
|
View Code Duplication |
foreach (self::getAlphabetForLocale(WT_LOCALE) as $letter) { |
|
256
|
|
|
$count = 1; |
|
257
|
|
|
if ($totals) { |
|
258
|
|
|
$count = Database::prepare($sql . " AND " . self::getInitialSql('n_surn', $letter))->fetchOne(); |
|
259
|
|
|
} |
|
260
|
|
|
$alphas[$letter] = $count; |
|
261
|
|
|
} |
|
262
|
|
|
|
|
263
|
|
|
// Now fetch initial letters that are not in our alphabet, |
|
264
|
|
|
// including "@" (for "@N.N.") and "" for no surname. |
|
265
|
|
|
$sql = |
|
266
|
|
|
"SELECT SQL_CACHE initial, count FROM (SELECT UPPER(LEFT(n_surn, 1)) AS initial, COUNT(n_id) AS count" . |
|
267
|
|
|
" FROM `##name` " . |
|
268
|
|
|
($fams ? " JOIN `##link` ON n_id = l_from AND n_file = l_file AND l_type = 'FAMS' " : "") . |
|
269
|
|
|
" WHERE n_file = :tree_id AND n_surn <> ''" . |
|
270
|
|
|
($marnm ? "" : " AND n_type != '_MARNM'"); |
|
271
|
|
|
|
|
272
|
|
|
$args = [ |
|
273
|
|
|
'tree_id' => $tree->getTreeId(), |
|
274
|
|
|
]; |
|
275
|
|
|
|
|
276
|
|
|
foreach (self::getAlphabetForLocale(WT_LOCALE) as $n => $letter) { |
|
277
|
|
|
$sql .= " AND n_surn COLLATE :collate_" . $n . " NOT LIKE :letter_" . $n; |
|
278
|
|
|
$args['collate_' . $n] = I18N::collation(); |
|
279
|
|
|
$args['letter_' . $n] = $letter . '%'; |
|
280
|
|
|
} |
|
281
|
|
|
$sql .= " GROUP BY UPPER(LEFT(n_surn, 1))) AS subquery ORDER BY initial = '', initial = '@', initial"; |
|
282
|
|
|
foreach (Database::prepare($sql)->execute($args)->fetchAssoc() as $alpha => $count) { |
|
283
|
|
|
$alphas[$alpha] = $count; |
|
284
|
|
|
} |
|
285
|
|
|
|
|
286
|
|
|
// Names with no surname |
|
287
|
|
|
$sql = |
|
288
|
|
|
"SELECT SQL_CACHE COUNT(n_id)" . |
|
289
|
|
|
" FROM `##name` " . |
|
290
|
|
|
($fams ? " JOIN `##link` ON n_id = l_from AND n_file = l_file AND l_type = 'FAMS' " : "") . |
|
291
|
|
|
" WHERE n_file = :tree_id AND n_surn = ''" . |
|
292
|
|
|
($marnm ? "" : " AND n_type != '_MARNM'"); |
|
293
|
|
|
|
|
294
|
|
|
$args = [ |
|
295
|
|
|
'tree_id' => $tree->getTreeId(), |
|
296
|
|
|
]; |
|
297
|
|
|
|
|
298
|
|
|
$count_no_surname = (int) Database::prepare($sql)->execute($args)->fetchOne(); |
|
299
|
|
|
if ($count_no_surname !== 0) { |
|
300
|
|
|
// Special code to indicate "no surname" |
|
301
|
|
|
$alphas[','] = $count_no_surname; |
|
302
|
|
|
} |
|
303
|
|
|
|
|
304
|
|
|
return $alphas; |
|
305
|
|
|
} |
|
306
|
|
|
|
|
307
|
|
|
/** |
|
308
|
|
|
* Get a list of initial given name letters for indilist.php and famlist.php |
|
309
|
|
|
* |
|
310
|
|
|
* @param Tree $tree Find names in this tree |
|
311
|
|
|
* @param string $surn if set, only consider people with this surname |
|
312
|
|
|
* @param string $salpha if set, only consider surnames starting with this letter |
|
313
|
|
|
* @param bool $marnm if set, include married names |
|
314
|
|
|
* @param bool $fams if set, only consider individuals with FAMS records |
|
315
|
|
|
* |
|
316
|
|
|
* @return int[] |
|
317
|
|
|
*/ |
|
318
|
|
|
public static function givenAlpha(Tree $tree, $surn, $salpha, $marnm, $fams) { |
|
319
|
|
|
$alphas = []; |
|
320
|
|
|
|
|
321
|
|
|
$sql = |
|
322
|
|
|
"SELECT SQL_CACHE COUNT(DISTINCT n_id)" . |
|
323
|
|
|
" FROM `##name`" . |
|
324
|
|
|
($fams ? " JOIN `##link` ON (n_id=l_from AND n_file=l_file AND l_type='FAMS') " : "") . |
|
325
|
|
|
" WHERE n_file=" . $tree->getTreeId() . " " . |
|
326
|
|
|
($marnm ? "" : " AND n_type!='_MARNM'"); |
|
327
|
|
|
|
|
328
|
|
View Code Duplication |
if ($surn) { |
|
329
|
|
|
$sql .= " AND n_surn=" . Database::quote($surn) . " COLLATE '" . I18N::collation() . "'"; |
|
|
|
|
|
|
330
|
|
|
} elseif ($salpha == ',') { |
|
331
|
|
|
$sql .= " AND n_surn=''"; |
|
332
|
|
|
} elseif ($salpha == '@') { |
|
333
|
|
|
$sql .= " AND n_surn='@N.N.'"; |
|
334
|
|
|
} elseif ($salpha) { |
|
335
|
|
|
$sql .= " AND " . self::getInitialSql('n_surn', $salpha); |
|
336
|
|
|
} else { |
|
337
|
|
|
// All surnames |
|
338
|
|
|
$sql .= " AND n_surn NOT IN ('', '@N.N.')"; |
|
339
|
|
|
} |
|
340
|
|
|
|
|
341
|
|
|
// Fetch all the letters in our alphabet, whether or not there |
|
342
|
|
|
// are any names beginning with that letter. It looks better to |
|
343
|
|
|
// show the full alphabet, rather than omitting rare letters such as X |
|
344
|
|
View Code Duplication |
foreach (self::getAlphabetForLocale(WT_LOCALE) as $letter) { |
|
345
|
|
|
$count = Database::prepare($sql . " AND " . self::getInitialSql('n_givn', $letter))->fetchOne(); |
|
346
|
|
|
$alphas[$letter] = $count; |
|
347
|
|
|
} |
|
348
|
|
|
|
|
349
|
|
|
// Now fetch initial letters that are not in our alphabet, |
|
350
|
|
|
// including "@" (for "@N.N.") and "" for no surname |
|
351
|
|
|
$sql = |
|
352
|
|
|
"SELECT SQL_CACHE initial, total FROM (SELECT UPPER(LEFT(n_givn, 1)) AS initial, COUNT(DISTINCT n_id) AS total" . |
|
353
|
|
|
" FROM `##name` " . |
|
354
|
|
|
($fams ? " JOIN `##link` ON (n_id = l_from AND n_file = l_file AND l_type = 'FAMS') " : "") . |
|
355
|
|
|
" WHERE n_file = :tree_id" . |
|
356
|
|
|
($marnm ? "" : " AND n_type != '_MARNM'"); |
|
357
|
|
|
|
|
358
|
|
|
$args = [ |
|
359
|
|
|
'tree_id' => $tree->getTreeId(), |
|
360
|
|
|
]; |
|
361
|
|
|
|
|
362
|
|
View Code Duplication |
if ($surn) { |
|
363
|
|
|
$sql .= " AND n_surn COLLATE :collate_1 = :surn"; |
|
364
|
|
|
$args['collate_1'] = I18N::collation(); |
|
365
|
|
|
$args['surn'] = $surn; |
|
366
|
|
|
} elseif ($salpha === ',') { |
|
367
|
|
|
$sql .= " AND n_surn = ''"; |
|
368
|
|
|
} elseif ($salpha === '@') { |
|
369
|
|
|
$sql .= " AND n_surn = '@N.N.'"; |
|
370
|
|
|
} elseif ($salpha) { |
|
371
|
|
|
$sql .= " AND " . self::getInitialSql('n_surn', $salpha); |
|
372
|
|
|
} else { |
|
373
|
|
|
// All surnames |
|
374
|
|
|
$sql .= " AND n_surn NOT IN ('', '@N.N.')"; |
|
375
|
|
|
} |
|
376
|
|
|
|
|
377
|
|
View Code Duplication |
foreach (self::getAlphabetForLocale(WT_LOCALE) as $letter) { |
|
378
|
|
|
$sql .= " AND n_givn NOT LIKE '" . $letter . "%' COLLATE " . I18N::collation(); |
|
379
|
|
|
} |
|
380
|
|
|
$sql .= " GROUP BY UPPER(LEFT(n_givn, 1))) AS subquery ORDER BY initial = '@', initial = '', initial"; |
|
381
|
|
|
|
|
382
|
|
|
foreach (Database::prepare($sql)->execute($args)->fetchAssoc() as $alpha => $count) { |
|
383
|
|
|
$alphas[$alpha] = $count; |
|
384
|
|
|
} |
|
385
|
|
|
|
|
386
|
|
|
return $alphas; |
|
387
|
|
|
} |
|
388
|
|
|
|
|
389
|
|
|
/** |
|
390
|
|
|
* Get a list of actual surnames and variants, based on a "root" surname. |
|
391
|
|
|
* |
|
392
|
|
|
* @param Tree $tree only fetch individuals from this tree |
|
393
|
|
|
* @param string $surn if set, only fetch people with this surname |
|
394
|
|
|
* @param string $salpha if set, only consider surnames starting with this letter |
|
395
|
|
|
* @param bool $marnm if set, include married names |
|
396
|
|
|
* @param bool $fams if set, only consider individuals with FAMS records |
|
397
|
|
|
* |
|
398
|
|
|
* @return array |
|
399
|
|
|
*/ |
|
400
|
|
|
public static function surnames(Tree $tree, $surn, $salpha, $marnm, $fams) { |
|
401
|
|
|
$sql = |
|
402
|
|
|
"SELECT SQL_CACHE n2.n_surn, n1.n_surname, n1.n_id" . |
|
403
|
|
|
" FROM `##name` n1 " . |
|
404
|
|
|
($fams ? " JOIN `##link` ON n_id = l_from AND n_file = l_file AND l_type = 'FAMS' " : "") . |
|
405
|
|
|
" JOIN (SELECT n_surn COLLATE :collate_0 AS n_surn, n_file FROM `##name`" . |
|
406
|
|
|
" WHERE n_file = :tree_id" . |
|
407
|
|
|
($marnm ? "" : " AND n_type != '_MARNM'"); |
|
408
|
|
|
|
|
409
|
|
|
$args = [ |
|
410
|
|
|
'tree_id' => $tree->getTreeId(), |
|
411
|
|
|
'collate_0' => I18N::collation(), |
|
412
|
|
|
]; |
|
413
|
|
|
|
|
414
|
|
View Code Duplication |
if ($surn) { |
|
415
|
|
|
$sql .= " AND n_surn COLLATE :collate_1 = :surn"; |
|
416
|
|
|
$args['collate_1'] = I18N::collation(); |
|
417
|
|
|
$args['surn'] = $surn; |
|
418
|
|
|
} elseif ($salpha === ',') { |
|
419
|
|
|
$sql .= " AND n_surn = ''"; |
|
420
|
|
|
} elseif ($salpha === '@') { |
|
421
|
|
|
$sql .= " AND n_surn = '@N.N.'"; |
|
422
|
|
|
} elseif ($salpha) { |
|
423
|
|
|
$sql .= " AND " . self::getInitialSql('n_surn', $salpha); |
|
424
|
|
|
} else { |
|
425
|
|
|
// All surnames |
|
426
|
|
|
$sql .= " AND n_surn NOT IN ('', '@N.N.')"; |
|
427
|
|
|
} |
|
428
|
|
|
$sql .= " GROUP BY n_surn COLLATE :collate_2, n_file) AS n2 ON (n1.n_surn = n2.n_surn COLLATE :collate_3 AND n1.n_file = n2.n_file)"; |
|
429
|
|
|
$args['collate_2'] = I18N::collation(); |
|
430
|
|
|
$args['collate_3'] = I18N::collation(); |
|
431
|
|
|
|
|
432
|
|
|
$list = []; |
|
433
|
|
View Code Duplication |
foreach (Database::prepare($sql)->execute($args)->fetchAll() as $row) { |
|
434
|
|
|
$list[I18N::strtoupper($row->n_surn)][$row->n_surname][$row->n_id] = true; |
|
435
|
|
|
} |
|
436
|
|
|
|
|
437
|
|
|
return $list; |
|
438
|
|
|
} |
|
439
|
|
|
|
|
440
|
|
|
/** |
|
441
|
|
|
* Fetch a list of individuals with specified names |
|
442
|
|
|
* |
|
443
|
|
|
* To search for unknown names, use $surn="@N.N.", $salpha="@" or $galpha="@" |
|
444
|
|
|
* To search for names with no surnames, use $salpha="," |
|
445
|
|
|
* |
|
446
|
|
|
* @param Tree $tree only fetch individuals from this tree |
|
447
|
|
|
* @param string $surn if set, only fetch people with this surname |
|
448
|
|
|
* @param string $salpha if set, only fetch surnames starting with this letter |
|
449
|
|
|
* @param string $galpha if set, only fetch given names starting with this letter |
|
450
|
|
|
* @param bool $marnm if set, include married names |
|
451
|
|
|
* @param bool $fams if set, only fetch individuals with FAMS records |
|
452
|
|
|
* |
|
453
|
|
|
* @return Individual[] |
|
454
|
|
|
*/ |
|
455
|
|
|
public static function individuals(Tree $tree, $surn, $salpha, $galpha, $marnm, $fams) { |
|
456
|
|
|
$sql = |
|
457
|
|
|
"SELECT i_id AS xref, i_gedcom AS gedcom, n_full " . |
|
458
|
|
|
"FROM `##individuals` " . |
|
459
|
|
|
"JOIN `##name` ON n_id = i_id AND n_file = i_file " . |
|
460
|
|
|
($fams ? "JOIN `##link` ON n_id = l_from AND n_file = l_file AND l_type = 'FAMS' " : "") . |
|
461
|
|
|
"WHERE n_file = :tree_id " . |
|
462
|
|
|
($marnm ? "" : "AND n_type != '_MARNM'"); |
|
463
|
|
|
|
|
464
|
|
|
$args = [ |
|
465
|
|
|
'tree_id' => $tree->getTreeId(), |
|
466
|
|
|
]; |
|
467
|
|
|
|
|
468
|
|
View Code Duplication |
if ($surn) { |
|
469
|
|
|
$sql .= " AND n_surn COLLATE :collate_1 = :surn"; |
|
470
|
|
|
$args['collate_1'] = I18N::collation(); |
|
471
|
|
|
$args['surn'] = $surn; |
|
472
|
|
|
} elseif ($salpha === ',') { |
|
473
|
|
|
$sql .= " AND n_surn = ''"; |
|
474
|
|
|
} elseif ($salpha === '@') { |
|
475
|
|
|
$sql .= " AND n_surn = '@N.N.'"; |
|
476
|
|
|
} elseif ($salpha) { |
|
477
|
|
|
$sql .= " AND " . self::getInitialSql('n_surn', $salpha); |
|
478
|
|
|
} else { |
|
479
|
|
|
// All surnames |
|
480
|
|
|
$sql .= " AND n_surn NOT IN ('', '@N.N.')"; |
|
481
|
|
|
} |
|
482
|
|
|
if ($galpha) { |
|
483
|
|
|
$sql .= " AND " . self::getInitialSql('n_givn', $galpha); |
|
484
|
|
|
} |
|
485
|
|
|
|
|
486
|
|
|
$sql .= " ORDER BY CASE n_surn WHEN '@N.N.' THEN 1 ELSE 0 END, n_surn COLLATE :collate_2, CASE n_givn WHEN '@P.N.' THEN 1 ELSE 0 END, n_givn COLLATE :collate_3"; |
|
487
|
|
|
$args['collate_2'] = I18N::collation(); |
|
488
|
|
|
$args['collate_3'] = I18N::collation(); |
|
489
|
|
|
|
|
490
|
|
|
$list = []; |
|
491
|
|
|
$rows = Database::prepare($sql)->execute($args)->fetchAll(); |
|
492
|
|
View Code Duplication |
foreach ($rows as $row) { |
|
493
|
|
|
$person = Individual::getInstance($row->xref, $tree, $row->gedcom); |
|
494
|
|
|
// The name from the database may be private - check the filtered list... |
|
495
|
|
|
foreach ($person->getAllNames() as $n => $name) { |
|
496
|
|
|
if ($name['fullNN'] == $row->n_full) { |
|
497
|
|
|
$person->setPrimaryName($n); |
|
498
|
|
|
// We need to clone $person, as we may have multiple references to the |
|
499
|
|
|
// same person in this list, and the "primary name" would otherwise |
|
500
|
|
|
// be shared amongst all of them. |
|
501
|
|
|
$list[] = clone $person; |
|
502
|
|
|
break; |
|
503
|
|
|
} |
|
504
|
|
|
} |
|
505
|
|
|
} |
|
506
|
|
|
|
|
507
|
|
|
return $list; |
|
508
|
|
|
} |
|
509
|
|
|
|
|
510
|
|
|
/** |
|
511
|
|
|
* Fetch a list of families with specified names |
|
512
|
|
|
* |
|
513
|
|
|
* To search for unknown names, use $surn="@N.N.", $salpha="@" or $galpha="@" |
|
514
|
|
|
* To search for names with no surnames, use $salpha="," |
|
515
|
|
|
* |
|
516
|
|
|
* @param Tree $tree only fetch individuals from this tree |
|
517
|
|
|
* @param string $surn if set, only fetch people with this surname |
|
518
|
|
|
* @param string $salpha if set, only fetch surnames starting with this letter |
|
519
|
|
|
* @param string $galpha if set, only fetch given names starting with this letter |
|
520
|
|
|
* @param bool $marnm if set, include married names |
|
521
|
|
|
* |
|
522
|
|
|
* @return Family[] |
|
523
|
|
|
*/ |
|
524
|
|
View Code Duplication |
public static function families(Tree $tree, $surn, $salpha, $galpha, $marnm) { |
|
|
|
|
|
|
525
|
|
|
$list = []; |
|
526
|
|
|
foreach (self::individuals($tree, $surn, $salpha, $galpha, $marnm, true) as $indi) { |
|
527
|
|
|
foreach ($indi->getSpouseFamilies() as $family) { |
|
528
|
|
|
$list[$family->getXref()] = $family; |
|
529
|
|
|
} |
|
530
|
|
|
} |
|
531
|
|
|
usort($list, '\Fisharebest\Webtrees\GedcomRecord::compare'); |
|
532
|
|
|
|
|
533
|
|
|
return $list; |
|
534
|
|
|
} |
|
535
|
|
|
} |
|
536
|
|
|
|
fisharebest /
webtrees
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.