/**
* Display the age difference between marriages and the births of children.
*
* @param Date $prev
* @param Date $next
* @param int $child_number
*
* @return string
*/
private static function ageDifference(Date $prev, Date $next, $child_number = 0)
{
if ($prev->isOK() && $next->isOK()) {
$days = $next->maximumJulianDay() - $prev->minimumJulianDay();
if ($days < 0) {
// Show warning triangle if dates in reverse order
$diff = '<i class="icon-warning"></i> ';
} elseif ($child_number > 1 && $days > 1 && $days < 240) {
// Show warning triangle if children born too close together
$diff = '<i class="icon-warning"></i> ';
} else {
$diff = '';
}
$months = round($days * 12 / 365.25);
// Approximate - we do not know the calendar
if (abs($months) == 12 || abs($months) >= 24) {
$diff .= I18N::plural('%s year', '%s years', round($months / 12), I18N::number(round($months / 12)));
} elseif ($months != 0) {
$diff .= I18N::plural('%s month', '%s months', $months, I18N::number($months));
}
return '<div class="elderdate age">' . $diff . '</div>';
} else {
return '';
}
}
$tmp = $fact->getDate()->minimumDate();
if ($tmp->d >= 1 && $tmp->d <= $tmp->daysInMonth()) {
// If the day is valid (for its own calendar), display it in the
// anniversary day (for the display calendar).
$found_facts[$jd - $cal_date->minJD + 1][] = $fact;
} else {
// Otherwise, display it in the "Day not set" box.
$found_facts[0][] = $fact;
}
}
}
break;
case 'year':
$cal_date->m = 0;
$cal_date->setJdFromYmd();
$found_facts = apply_filter(FunctionsDb::getCalendarEvents($ged_date->minimumJulianDay(), $ged_date->maximumJulianDay(), $filterev, $WT_TREE), $filterof, $filtersx);
// Eliminate duplicates (e.g. BET JUL 1900 AND SEP 1900 will appear twice in 1900)
$found_facts = array_unique($found_facts);
break;
}
// Group the facts by family/individual
$indis = array();
$fams = array();
$cal_facts = array();
switch ($view) {
case 'year':
case 'day':
foreach ($found_facts as $fact) {
$record = $fact->getParent();
$xref = $record->getXref();
if ($record instanceof Individual) {
/**
* Perform the search
*/
private function advancedSearch()
{
global $WT_TREE;
$this->myindilist = array();
$fct = count($this->fields);
if (!array_filter($this->values)) {
return;
}
// Dynamic SQL query, plus bind variables
$sql = 'SELECT DISTINCT ind.i_id AS xref, ind.i_gedcom AS gedcom FROM `##individuals` ind';
$bind = array();
// Join the following tables
$father_name = false;
$mother_name = false;
$spouse_family = false;
$indi_name = false;
$indi_date = false;
$fam_date = false;
$indi_plac = false;
$fam_plac = false;
foreach ($this->fields as $n => $field) {
if ($this->values[$n]) {
if (substr($field, 0, 14) == 'FAMC:HUSB:NAME') {
$father_name = true;
} elseif (substr($field, 0, 14) == 'FAMC:WIFE:NAME') {
$mother_name = true;
} elseif (substr($field, 0, 4) == 'NAME') {
$indi_name = true;
} elseif (strpos($field, ':DATE') !== false) {
if (substr($field, 0, 4) == 'FAMS') {
$fam_date = true;
$spouse_family = true;
} else {
$indi_date = true;
}
} elseif (strpos($field, ':PLAC') !== false) {
if (substr($field, 0, 4) == 'FAMS') {
$fam_plac = true;
$spouse_family = true;
} else {
$indi_plac = true;
}
} elseif ($field == 'FAMS:NOTE') {
$spouse_family = true;
}
}
}
if ($father_name || $mother_name) {
$sql .= " JOIN `##link` l_1 ON (l_1.l_file=ind.i_file AND l_1.l_from=ind.i_id AND l_1.l_type='FAMC')";
}
if ($father_name) {
$sql .= " JOIN `##link` l_2 ON (l_2.l_file=ind.i_file AND l_2.l_from=l_1.l_to AND l_2.l_type='HUSB')";
$sql .= " JOIN `##name` f_n ON (f_n.n_file=ind.i_file AND f_n.n_id =l_2.l_to)";
}
if ($mother_name) {
$sql .= " JOIN `##link` l_3 ON (l_3.l_file=ind.i_file AND l_3.l_from=l_1.l_to AND l_3.l_type='WIFE')";
$sql .= " JOIN `##name` m_n ON (m_n.n_file=ind.i_file AND m_n.n_id =l_3.l_to)";
}
if ($spouse_family) {
$sql .= " JOIN `##link` l_4 ON (l_4.l_file=ind.i_file AND l_4.l_from=ind.i_id AND l_4.l_type='FAMS')";
$sql .= " JOIN `##families` fam ON (fam.f_file=ind.i_file AND fam.f_id =l_4.l_to)";
}
if ($indi_name) {
$sql .= " JOIN `##name` i_n ON (i_n.n_file=ind.i_file AND i_n.n_id=ind.i_id)";
}
if ($indi_date) {
$sql .= " JOIN `##dates` i_d ON (i_d.d_file=ind.i_file AND i_d.d_gid=ind.i_id)";
}
if ($fam_date) {
$sql .= " JOIN `##dates` f_d ON (f_d.d_file=ind.i_file AND f_d.d_gid=fam.f_id)";
}
if ($indi_plac) {
$sql .= " JOIN `##placelinks` i_pl ON (i_pl.pl_file=ind.i_file AND i_pl.pl_gid =ind.i_id)";
$sql .= " JOIN (" . "SELECT CONCAT_WS(', ', p1.p_place, p2.p_place, p3.p_place, p4.p_place, p5.p_place, p6.p_place, p7.p_place, p8.p_place, p9.p_place) AS place, p1.p_id AS id, p1.p_file AS file" . " FROM `##places` AS p1" . " LEFT JOIN `##places` AS p2 ON (p1.p_parent_id=p2.p_id)" . " LEFT JOIN `##places` AS p3 ON (p2.p_parent_id=p3.p_id)" . " LEFT JOIN `##places` AS p4 ON (p3.p_parent_id=p4.p_id)" . " LEFT JOIN `##places` AS p5 ON (p4.p_parent_id=p5.p_id)" . " LEFT JOIN `##places` AS p6 ON (p5.p_parent_id=p6.p_id)" . " LEFT JOIN `##places` AS p7 ON (p6.p_parent_id=p7.p_id)" . " LEFT JOIN `##places` AS p8 ON (p7.p_parent_id=p8.p_id)" . " LEFT JOIN `##places` AS p9 ON (p8.p_parent_id=p9.p_id)" . ") AS i_p ON (i_p.file =ind.i_file AND i_pl.pl_p_id= i_p.id)";
}
if ($fam_plac) {
$sql .= " JOIN `##placelinks` f_pl ON (f_pl.pl_file=ind.i_file AND f_pl.pl_gid =fam.f_id)";
$sql .= " JOIN (" . "SELECT CONCAT_WS(', ', p1.p_place, p2.p_place, p3.p_place, p4.p_place, p5.p_place, p6.p_place, p7.p_place, p8.p_place, p9.p_place) AS place, p1.p_id AS id, p1.p_file AS file" . " FROM `##places` AS p1" . " LEFT JOIN `##places` AS p2 ON (p1.p_parent_id=p2.p_id)" . " LEFT JOIN `##places` AS p3 ON (p2.p_parent_id=p3.p_id)" . " LEFT JOIN `##places` AS p4 ON (p3.p_parent_id=p4.p_id)" . " LEFT JOIN `##places` AS p5 ON (p4.p_parent_id=p5.p_id)" . " LEFT JOIN `##places` AS p6 ON (p5.p_parent_id=p6.p_id)" . " LEFT JOIN `##places` AS p7 ON (p6.p_parent_id=p7.p_id)" . " LEFT JOIN `##places` AS p8 ON (p7.p_parent_id=p8.p_id)" . " LEFT JOIN `##places` AS p9 ON (p8.p_parent_id=p9.p_id)" . ") AS f_p ON (f_p.file =ind.i_file AND f_pl.pl_p_id= f_p.id)";
}
// Add the where clause
$sql .= " WHERE ind.i_file=?";
$bind[] = $WT_TREE->getTreeId();
for ($i = 0; $i < $fct; $i++) {
$field = $this->fields[$i];
$value = $this->values[$i];
if ($value === '') {
continue;
}
$parts = preg_split("/:/", $field . '::::');
if ($parts[0] == 'NAME') {
// NAME:*
switch ($parts[1]) {
case 'GIVN':
switch ($parts[2]) {
case 'EXACT':
$sql .= " AND i_n.n_givn=?";
$bind[] = $value;
break;
case 'BEGINS':
$sql .= " AND i_n.n_givn LIKE CONCAT(?, '%')";
$bind[] = $value;
break;
case 'CONTAINS':
$sql .= " AND i_n.n_givn LIKE CONCAT('%', ?, '%')";
$bind[] = $value;
break;
case 'SDX_STD':
$sdx = Soundex::russell($value);
if ($sdx !== null) {
$sdx = explode(':', $sdx);
foreach ($sdx as $k => $v) {
$sdx[$k] = "i_n.n_soundex_givn_std LIKE CONCAT('%', ?, '%')";
$bind[] = $v;
}
$sql .= ' AND (' . implode(' OR ', $sdx) . ')';
} else {
// No phonetic content? Use a substring match
$sql .= " AND i_n.n_givn LIKE CONCAT('%', ?, '%')";
$bind[] = $value;
}
break;
case 'SDX':
// SDX uses DM by default.
// SDX uses DM by default.
case 'SDX_DM':
$sdx = Soundex::daitchMokotoff($value);
if ($sdx !== null) {
$sdx = explode(':', $sdx);
foreach ($sdx as $k => $v) {
$sdx[$k] = "i_n.n_soundex_givn_dm LIKE CONCAT('%', ?, '%')";
$bind[] = $v;
}
$sql .= ' AND (' . implode(' OR ', $sdx) . ')';
} else {
// No phonetic content? Use a substring match
$sql .= " AND i_n.n_givn LIKE CONCAT('%', ?, '%')";
$bind[] = $value;
}
break;
}
break;
case 'SURN':
switch ($parts[2]) {
case 'EXACT':
$sql .= " AND i_n.n_surname=?";
$bind[] = $value;
break;
case 'BEGINS':
$sql .= " AND i_n.n_surname LIKE CONCAT(?, '%')";
$bind[] = $value;
break;
case 'CONTAINS':
$sql .= " AND i_n.n_surname LIKE CONCAT('%', ?, '%')";
$bind[] = $value;
break;
case 'SDX_STD':
$sdx = Soundex::russell($value);
if ($sdx !== null) {
$sdx = explode(':', $sdx);
foreach ($sdx as $k => $v) {
$sdx[$k] = "i_n.n_soundex_surn_std LIKE CONCAT('%', ?, '%')";
$bind[] = $v;
}
$sql .= " AND (" . implode(' OR ', $sdx) . ")";
} else {
// No phonetic content? Use a substring match
$sql .= " AND i_n.n_surn LIKE CONCAT('%', ?, '%')";
$bind[] = $value;
}
break;
case 'SDX':
// SDX uses DM by default.
// SDX uses DM by default.
case 'SDX_DM':
$sdx = Soundex::daitchMokotoff($value);
if ($sdx !== null) {
$sdx = explode(':', $sdx);
foreach ($sdx as $k => $v) {
$sdx[$k] = "i_n.n_soundex_surn_dm LIKE CONCAT('%', ?, '%')";
$bind[] = $v;
}
$sql .= " AND (" . implode(' OR ', $sdx) . ")";
break;
} else {
// No phonetic content? Use a substring match
$sql .= " AND i_n.n_surn LIKE CONCAT('%', ?, '%')";
$bind[] = $value;
}
}
break;
case 'NICK':
case '_MARNM':
case '_HEB':
case '_AKA':
$sql .= " AND i_n.n_type=? AND i_n.n_full LIKE CONCAT('%', ?, '%')";
$bind[] = $parts[1];
$bind[] = $value;
break;
}
} elseif ($parts[1] == 'DATE') {
// *:DATE
$date = new Date($value);
if ($date->isOK()) {
$jd1 = $date->minimumJulianDay();
$jd2 = $date->maximumJulianDay();
if (!empty($this->plusminus[$i])) {
$adjd = $this->plusminus[$i] * 365;
$jd1 -= $adjd;
$jd2 += $adjd;
}
$sql .= " AND i_d.d_fact=? AND i_d.d_julianday1>=? AND i_d.d_julianday2<=?";
$bind[] = $parts[0];
$bind[] = $jd1;
$bind[] = $jd2;
}
} elseif ($parts[0] == 'FAMS' && $parts[2] == 'DATE') {
// FAMS:*:DATE
$date = new Date($value);
if ($date->isOK()) {
$jd1 = $date->minimumJulianDay();
$jd2 = $date->maximumJulianDay();
if (!empty($this->plusminus[$i])) {
$adjd = $this->plusminus[$i] * 365;
$jd1 -= $adjd;
$jd2 += $adjd;
}
$sql .= " AND f_d.d_fact=? AND f_d.d_julianday1>=? AND f_d.d_julianday2<=?";
$bind[] = $parts[1];
$bind[] = $jd1;
$bind[] = $jd2;
}
} elseif ($parts[1] == 'PLAC') {
// *:PLAC
// SQL can only link a place to a person/family, not to an event.
$sql .= " AND i_p.place LIKE CONCAT('%', ?, '%')";
$bind[] = $value;
} elseif ($parts[0] == 'FAMS' && $parts[2] == 'PLAC') {
// FAMS:*:PLAC
// SQL can only link a place to a person/family, not to an event.
$sql .= " AND f_p.place LIKE CONCAT('%', ?, '%')";
$bind[] = $value;
} elseif ($parts[0] == 'FAMC' && $parts[2] == 'NAME') {
$table = $parts[1] == 'HUSB' ? 'f_n' : 'm_n';
// NAME:*
switch ($parts[3]) {
case 'GIVN':
switch ($parts[4]) {
case 'EXACT':
$sql .= " AND {$table}.n_givn=?";
$bind[] = $value;
break;
case 'BEGINS':
$sql .= " AND {$table}.n_givn LIKE CONCAT(?, '%')";
$bind[] = $value;
break;
case 'CONTAINS':
$sql .= " AND {$table}.n_givn LIKE CONCAT('%', ?, '%')";
$bind[] = $value;
break;
case 'SDX_STD':
$sdx = Soundex::russell($value);
if ($sdx !== null) {
$sdx = explode(':', $sdx);
foreach ($sdx as $k => $v) {
$sdx[$k] = "{$table}.n_soundex_givn_std LIKE CONCAT('%', ?, '%')";
$bind[] = $v;
}
$sql .= ' AND (' . implode(' OR ', $sdx) . ')';
} else {
// No phonetic content? Use a substring match
$sql .= " AND {$table}.n_givn = LIKE CONCAT('%', ?, '%')";
$bind[] = $value;
}
break;
case 'SDX':
// SDX uses DM by default.
// SDX uses DM by default.
case 'SDX_DM':
$sdx = Soundex::daitchMokotoff($value);
if ($sdx !== null) {
$sdx = explode(':', $sdx);
foreach ($sdx as $k => $v) {
$sdx[$k] = "{$table}.n_soundex_givn_dm LIKE CONCAT('%', ?, '%')";
$bind[] = $v;
}
$sql .= ' AND (' . implode(' OR ', $sdx) . ')';
break;
} else {
// No phonetic content? Use a substring match
$sql .= " AND {$table}.n_givn = LIKE CONCAT('%', ?, '%')";
$bind[] = $value;
}
}
break;
case 'SURN':
switch ($parts[4]) {
case 'EXACT':
$sql .= " AND {$table}.n_surname=?";
$bind[] = $value;
break;
case 'BEGINS':
$sql .= " AND {$table}.n_surname LIKE CONCAT(?, '%')";
$bind[] = $value;
break;
case 'CONTAINS':
$sql .= " AND {$table}.n_surname LIKE CONCAT('%', ?, '%')";
$bind[] = $value;
break;
case 'SDX_STD':
$sdx = Soundex::russell($value);
if ($sdx !== null) {
$sdx = explode(':', $sdx);
foreach ($sdx as $k => $v) {
$sdx[$k] = "{$table}.n_soundex_surn_std LIKE CONCAT('%', ?, '%')";
$bind[] = $v;
}
$sql .= ' AND (' . implode(' OR ', $sdx) . ')';
} else {
// No phonetic content? Use a substring match
$sql .= " AND {$table}.n_surn = LIKE CONCAT('%', ?, '%')";
$bind[] = $value;
}
break;
case 'SDX':
// SDX uses DM by default.
// SDX uses DM by default.
case 'SDX_DM':
$sdx = Soundex::daitchMokotoff($value);
if ($sdx !== null) {
$sdx = explode(':', $sdx);
foreach ($sdx as $k => $v) {
$sdx[$k] = "{$table}.n_soundex_surn_dm LIKE CONCAT('%', ?, '%')";
$bind[] = $v;
}
$sql .= ' AND (' . implode(' OR ', $sdx) . ')';
} else {
// No phonetic content? Use a substring match
$sql .= " AND {$table}.n_surn = LIKE CONCAT('%', ?, '%')";
$bind[] = $value;
}
break;
}
break;
}
} elseif ($parts[0] == 'FAMS') {
// e.g. searches for occupation, religion, note, etc.
$sql .= " AND fam.f_gedcom REGEXP CONCAT('\n[0-9] ', ?, '(.*\n[0-9] CONT)* [^\n]*', ?)";
$bind[] = $parts[1];
$bind[] = $value;
} else {
// e.g. searches for occupation, religion, note, etc.
$sql .= " AND ind.i_gedcom REGEXP CONCAT('\n[0-9] ', ?, '(.*\n[0-9] CONT)* [^\n]*', ?)";
$bind[] = $parts[0];
$bind[] = $value;
}
}
$rows = Database::prepare($sql)->execute($bind)->fetchAll();
foreach ($rows as $row) {
$person = Individual::getInstance($row->xref, $WT_TREE, $row->gedcom);
// Check for XXXX:PLAC fields, which were only partially matched by SQL
foreach ($this->fields as $n => $field) {
if ($this->values[$n] && preg_match('/^(' . WT_REGEX_TAG . '):PLAC$/', $field, $match)) {
if (!preg_match('/\\n1 ' . $match[1] . '(\\n[2-9].*)*\\n2 PLAC .*' . preg_quote($this->values[$n], '/') . '/i', $person->getGedcom())) {
continue 2;
}
}
}
$this->myindilist[] = $person;
}
}
/**
* Compare two dates, so they can be sorted.
*
* return <0 if $a<$b
* return >0 if $b>$a
* return 0 if dates same/overlap
* BEF/AFT sort as the day before/after
*
* @param Date $a
* @param Date $b
*
* @return int
*/
public static function compare(Date $a, Date $b)
{
// Get min/max JD for each date.
switch ($a->qual1) {
case 'BEF':
$amin = $a->minimumJulianDay() - 1;
$amax = $amin;
break;
case 'AFT':
$amax = $a->maximumJulianDay() + 1;
$amin = $amax;
break;
default:
$amin = $a->minimumJulianDay();
$amax = $a->maximumJulianDay();
break;
}
switch ($b->qual1) {
case 'BEF':
$bmin = $b->minimumJulianDay() - 1;
$bmax = $bmin;
break;
case 'AFT':
$bmax = $b->maximumJulianDay() + 1;
$bmin = $bmax;
break;
default:
$bmin = $b->minimumJulianDay();
$bmax = $b->maximumJulianDay();
break;
}
if ($amax < $bmin) {
return -1;
} elseif ($amin > $bmax && $bmax > 0) {
return 1;
} elseif ($amin < $bmin && $amax <= $bmax) {
return -1;
} elseif ($amin > $bmin && $amax >= $bmax && $bmax > 0) {
return 1;
} else {
return 0;
}
}
/**
* XML <List>
*
* @param array $attrs an array of key value pairs for the attributes
*/
private function listStartHandler($attrs)
{
global $WT_TREE;
$this->process_repeats++;
if ($this->process_repeats > 1) {
return;
}
$match = array();
if (isset($attrs['sortby'])) {
$sortby = $attrs['sortby'];
if (preg_match("/\\\$(\\w+)/", $sortby, $match)) {
$sortby = $this->vars[$match[1]]['id'];
$sortby = trim($sortby);
}
} else {
$sortby = "NAME";
}
if (isset($attrs['list'])) {
$listname = $attrs['list'];
} else {
$listname = "individual";
}
// Some filters/sorts can be applied using SQL, while others require PHP
switch ($listname) {
case "pending":
$rows = Database::prepare("SELECT xref, CASE new_gedcom WHEN '' THEN old_gedcom ELSE new_gedcom END AS gedcom" . " FROM `##change`" . " WHERE (xref, change_id) IN (" . " SELECT xref, MAX(change_id)" . " FROM `##change`" . " WHERE status = 'pending' AND gedcom_id = :tree_id" . " GROUP BY xref" . " )")->execute(array('tree_id' => $WT_TREE->getTreeId()))->fetchAll();
$this->list = array();
foreach ($rows as $row) {
$this->list[] = GedcomRecord::getInstance($row->xref, $WT_TREE, $row->gedcom);
}
break;
case 'individual':
$sql_select = "SELECT i_id AS xref, i_gedcom AS gedcom FROM `##individuals` ";
$sql_join = "";
$sql_where = " WHERE i_file = :tree_id";
$sql_order_by = "";
$sql_params = array('tree_id' => $WT_TREE->getTreeId());
foreach ($attrs as $attr => $value) {
if (strpos($attr, 'filter') === 0 && $value) {
$value = $this->substituteVars($value, false);
// Convert the various filters into SQL
if (preg_match('/^(\\w+):DATE (LTE|GTE) (.+)$/', $value, $match)) {
$sql_join .= " JOIN `##dates` AS {$attr} ON ({$attr}.d_file=i_file AND {$attr}.d_gid=i_id)";
$sql_where .= " AND {$attr}.d_fact = :{$attr}fact";
$sql_params[$attr . 'fact'] = $match[1];
$date = new Date($match[3]);
if ($match[2] == "LTE") {
$sql_where .= " AND {$attr}.d_julianday2 <= :{$attr}date";
$sql_params[$attr . 'date'] = $date->maximumJulianDay();
} else {
$sql_where .= " AND {$attr}.d_julianday1 >= :{$attr}date";
$sql_params[$attr . 'date'] = $date->minimumJulianDay();
}
if ($sortby == $match[1]) {
$sortby = "";
$sql_order_by .= ($sql_order_by ? ", " : " ORDER BY ") . "{$attr}.d_julianday1";
}
unset($attrs[$attr]);
// This filter has been fully processed
} elseif (preg_match('/^NAME CONTAINS (.*)$/', $value, $match)) {
// Do nothing, unless you have to
if ($match[1] != '' || $sortby == 'NAME') {
$sql_join .= " JOIN `##name` AS {$attr} ON (n_file=i_file AND n_id=i_id)";
// Search the DB only if there is any name supplied
if ($match[1] != "") {
$names = explode(" ", $match[1]);
foreach ($names as $n => $name) {
$sql_where .= " AND {$attr}.n_full LIKE CONCAT('%', :{$attr}name{$n}, '%')";
$sql_params[$attr . 'name' . $n] = $name;
}
}
// Let the DB do the name sorting even when no name was entered
if ($sortby == "NAME") {
$sortby = "";
$sql_order_by .= ($sql_order_by ? ", " : " ORDER BY ") . "{$attr}.n_sort";
}
}
unset($attrs[$attr]);
// This filter has been fully processed
} elseif (preg_match('/^REGEXP \\/(.+)\\//', $value, $match)) {
$sql_where .= " AND i_gedcom REGEXP :{$attr}gedcom";
// PDO helpfully escapes backslashes for us, preventing us from matching "\n1 FACT"
$sql_params[$attr . 'gedcom'] = str_replace('\\n', "\n", $match[1]);
unset($attrs[$attr]);
// This filter has been fully processed
} elseif (preg_match('/^(?:\\w+):PLAC CONTAINS (.+)$/', $value, $match)) {
$sql_join .= " JOIN `##places` AS {$attr}a ON ({$attr}a.p_file = i_file)";
$sql_join .= " JOIN `##placelinks` AS {$attr}b ON ({$attr}a.p_file = {$attr}b.pl_file AND {$attr}b.pl_p_id = {$attr}a.p_id AND {$attr}b.pl_gid = i_id)";
$sql_where .= " AND {$attr}a.p_place LIKE CONCAT('%', :{$attr}place, '%')";
$sql_params[$attr . 'place'] = $match[1];
// Don't unset this filter. This is just initial filtering
} elseif (preg_match('/^(\\w*):*(\\w*) CONTAINS (.+)$/', $value, $match)) {
$sql_where .= " AND i_gedcom LIKE CONCAT('%', :{$attr}contains1, '%', :{$attr}contains2, '%', :{$attr}contains3, '%')";
$sql_params[$attr . 'contains1'] = $match[1];
$sql_params[$attr . 'contains2'] = $match[2];
$sql_params[$attr . 'contains3'] = $match[3];
// Don't unset this filter. This is just initial filtering
}
}
}
$this->list = array();
$rows = Database::prepare($sql_select . $sql_join . $sql_where . $sql_order_by)->execute($sql_params)->fetchAll();
foreach ($rows as $row) {
$this->list[$row->xref] = Individual::getInstance($row->xref, $WT_TREE, $row->gedcom);
}
break;
case 'family':
$sql_select = "SELECT f_id AS xref, f_gedcom AS gedcom FROM `##families`";
$sql_join = "";
$sql_where = " WHERE f_file = :tree_id";
$sql_order_by = "";
$sql_params = array('tree_id' => $WT_TREE->getTreeId());
foreach ($attrs as $attr => $value) {
if (strpos($attr, 'filter') === 0 && $value) {
$value = $this->substituteVars($value, false);
// Convert the various filters into SQL
if (preg_match('/^(\\w+):DATE (LTE|GTE) (.+)$/', $value, $match)) {
$sql_join .= " JOIN `##dates` AS {$attr} ON ({$attr}.d_file=f_file AND {$attr}.d_gid=f_id)";
$sql_where .= " AND {$attr}.d_fact = :{$attr}fact";
$sql_params[$attr . 'fact'] = $match[1];
$date = new Date($match[3]);
if ($match[2] == "LTE") {
$sql_where .= " AND {$attr}.d_julianday2 <= :{$attr}date";
$sql_params[$attr . 'date'] = $date->maximumJulianDay();
} else {
$sql_where .= " AND {$attr}.d_julianday1 >= :{$attr}date";
$sql_params[$attr . 'date'] = $date->minimumJulianDay();
}
if ($sortby == $match[1]) {
$sortby = "";
$sql_order_by .= ($sql_order_by ? ", " : " ORDER BY ") . "{$attr}.d_julianday1";
}
unset($attrs[$attr]);
// This filter has been fully processed
} elseif (preg_match('/^REGEXP \\/(.+)\\//', $value, $match)) {
$sql_where .= " AND f_gedcom REGEXP :{$attr}gedcom";
// PDO helpfully escapes backslashes for us, preventing us from matching "\n1 FACT"
$sql_params[$attr . 'gedcom'] = str_replace('\\n', "\n", $match[1]);
unset($attrs[$attr]);
// This filter has been fully processed
} elseif (preg_match('/^NAME CONTAINS (.+)$/', $value, $match)) {
// Do nothing, unless you have to
if ($match[1] != '' || $sortby == 'NAME') {
$sql_join .= " JOIN `##name` AS {$attr} ON n_file = f_file AND n_id IN (f_husb, f_wife)";
// Search the DB only if there is any name supplied
if ($match[1] != "") {
$names = explode(" ", $match[1]);
foreach ($names as $n => $name) {
$sql_where .= " AND {$attr}.n_full LIKE CONCAT('%', :{$attr}name{$n}, '%')";
$sql_params[$attr . 'name' . $n] = $name;
}
}
// Let the DB do the name sorting even when no name was entered
if ($sortby == "NAME") {
$sortby = "";
$sql_order_by .= ($sql_order_by ? ", " : " ORDER BY ") . "{$attr}.n_sort";
}
}
unset($attrs[$attr]);
// This filter has been fully processed
} elseif (preg_match('/^(?:\\w+):PLAC CONTAINS (.+)$/', $value, $match)) {
$sql_join .= " JOIN `##places` AS {$attr}a ON ({$attr}a.p_file=f_file)";
$sql_join .= " JOIN `##placelinks` AS {$attr}b ON ({$attr}a.p_file={$attr}b.pl_file AND {$attr}b.pl_p_id={$attr}a.p_id AND {$attr}b.pl_gid=f_id)";
$sql_where .= " AND {$attr}a.p_place LIKE CONCAT('%', :{$attr}place, '%')";
$sql_params[$attr . 'place'] = $match[1];
// Don't unset this filter. This is just initial filtering
} elseif (preg_match('/^(\\w*):*(\\w*) CONTAINS (.+)$/', $value, $match)) {
$sql_where .= " AND f_gedcom LIKE CONCAT('%', :{$attr}contains1, '%', :{$attr}contains2, '%', :{$attr}contains3, '%')";
$sql_params[$attr . 'contains1'] = $match[1];
$sql_params[$attr . 'contains2'] = $match[2];
$sql_params[$attr . 'contains3'] = $match[3];
// Don't unset this filter. This is just initial filtering
}
}
}
$this->list = array();
$rows = Database::prepare($sql_select . $sql_join . $sql_where . $sql_order_by)->execute($sql_params)->fetchAll();
foreach ($rows as $row) {
$this->list[$row->xref] = Family::getInstance($row->xref, $WT_TREE, $row->gedcom);
}
break;
default:
throw new \DomainException('Invalid list name: ' . $listname);
}
$filters = array();
$filters2 = array();
if (isset($attrs['filter1']) && count($this->list) > 0) {
foreach ($attrs as $key => $value) {
if (preg_match("/filter(\\d)/", $key)) {
$condition = $value;
if (preg_match("/@(\\w+)/", $condition, $match)) {
$id = $match[1];
$value = "''";
if ($id == "ID") {
if (preg_match("/0 @(.+)@/", $this->gedrec, $match)) {
$value = "'" . $match[1] . "'";
}
} elseif ($id == "fact") {
$value = "'" . $this->fact . "'";
} elseif ($id == "desc") {
$value = "'" . $this->desc . "'";
} else {
if (preg_match("/\\d {$id} (.+)/", $this->gedrec, $match)) {
$value = "'" . str_replace("@", "", trim($match[1])) . "'";
}
}
$condition = preg_replace("/@{$id}/", $value, $condition);
}
//-- handle regular expressions
if (preg_match("/([A-Z:]+)\\s*([^\\s]+)\\s*(.+)/", $condition, $match)) {
$tag = trim($match[1]);
$expr = trim($match[2]);
$val = trim($match[3]);
if (preg_match("/\\\$(\\w+)/", $val, $match)) {
$val = $this->vars[$match[1]]['id'];
$val = trim($val);
}
if ($val) {
$searchstr = "";
$tags = explode(":", $tag);
//-- only limit to a level number if we are specifically looking at a level
if (count($tags) > 1) {
$level = 1;
foreach ($tags as $t) {
if (!empty($searchstr)) {
$searchstr .= "[^\n]*(\n[2-9][^\n]*)*\n";
}
//-- search for both EMAIL and _EMAIL... silly double gedcom standard
if ($t == "EMAIL" || $t == "_EMAIL") {
$t = "_?EMAIL";
}
$searchstr .= $level . " " . $t;
$level++;
}
} else {
if ($tag == "EMAIL" || $tag == "_EMAIL") {
$tag = "_?EMAIL";
}
$t = $tag;
$searchstr = "1 " . $tag;
}
switch ($expr) {
case "CONTAINS":
if ($t == "PLAC") {
$searchstr .= "[^\n]*[, ]*" . $val;
} else {
$searchstr .= "[^\n]*" . $val;
}
$filters[] = $searchstr;
break;
default:
$filters2[] = array("tag" => $tag, "expr" => $expr, "val" => $val);
break;
}
}
}
}
}
}
//-- apply other filters to the list that could not be added to the search string
if ($filters) {
foreach ($this->list as $key => $record) {
foreach ($filters as $filter) {
if (!preg_match("/" . $filter . "/i", $record->privatizeGedcom(Auth::accessLevel($WT_TREE)))) {
unset($this->list[$key]);
break;
}
}
}
}
if ($filters2) {
$mylist = array();
foreach ($this->list as $indi) {
$key = $indi->getXref();
$grec = $indi->privatizeGedcom(Auth::accessLevel($WT_TREE));
$keep = true;
foreach ($filters2 as $filter) {
if ($keep) {
$tag = $filter['tag'];
$expr = $filter['expr'];
$val = $filter['val'];
if ($val == "''") {
$val = "";
}
$tags = explode(":", $tag);
$t = end($tags);
$v = $this->getGedcomValue($tag, 1, $grec);
//-- check for EMAIL and _EMAIL (silly double gedcom standard :P)
if ($t == "EMAIL" && empty($v)) {
$tag = str_replace("EMAIL", "_EMAIL", $tag);
$tags = explode(":", $tag);
$t = end($tags);
$v = Functions::getSubRecord(1, $tag, $grec);
}
switch ($expr) {
case "GTE":
if ($t == "DATE") {
$date1 = new Date($v);
$date2 = new Date($val);
$keep = Date::compare($date1, $date2) >= 0;
} elseif ($val >= $v) {
$keep = true;
}
break;
case "LTE":
if ($t == "DATE") {
$date1 = new Date($v);
$date2 = new Date($val);
$keep = Date::compare($date1, $date2) <= 0;
} elseif ($val >= $v) {
$keep = true;
}
break;
default:
if ($v == $val) {
$keep = true;
} else {
$keep = false;
}
break;
}
}
}
if ($keep) {
$mylist[$key] = $indi;
}
}
$this->list = $mylist;
}
switch ($sortby) {
case 'NAME':
uasort($this->list, '\\Fisharebest\\Webtrees\\GedcomRecord::compare');
break;
case 'CHAN':
uasort($this->list, function (GedcomRecord $x, GedcomRecord $y) {
return $y->lastChangeTimestamp(true) - $x->lastChangeTimestamp(true);
});
break;
case 'BIRT:DATE':
uasort($this->list, '\\Fisharebest\\Webtrees\\Individual::compareBirthDate');
break;
case 'DEAT:DATE':
uasort($this->list, '\\Fisharebest\\Webtrees\\Individual::compareDeathDate');
break;
case 'MARR:DATE':
uasort($this->list, '\\Fisharebest\\Webtrees\\Family::compareMarrDate');
break;
default:
// unsorted or already sorted by SQL
break;
}
array_push($this->repeats_stack, array($this->repeats, $this->repeat_bytes));
$this->repeat_bytes = xml_get_current_line_number($this->parser) + 1;
}
