function search_repos($query, $geds, $match)
{
global $GEDCOM;
// No query => no results
if (!$query) {
return array();
}
// Convert the query into a SQL expression
$querysql = array();
// Convert the query into a regular expression
$queryregex = array();
foreach ($query as $q) {
$queryregex[] = preg_quote(WT_I18N::strtoupper($q), '/');
$querysql[] = "o_gedcom LIKE " . WT_DB::quote("%{$q}%") . " COLLATE '" . WT_I18N::$collation . "'";
}
$sql = "SELECT o_id AS xref, o_file AS gedcom_id, o_gedcom AS gedcom FROM `##other` WHERE (" . implode(" {$match} ", $querysql) . ") AND o_type='REPO' AND o_file IN (" . implode(',', $geds) . ')';
// Group results by gedcom, to minimise switching between privacy files
$sql .= ' ORDER BY gedcom_id';
$list = array();
$rows = WT_DB::prepare($sql)->fetchAll();
$GED_ID = WT_GED_ID;
foreach ($rows as $row) {
// Switch privacy file if necessary
if ($row->gedcom_id != $GED_ID) {
$GEDCOM = get_gedcom_from_id($row->gedcom_id);
load_gedcom_settings($row->gedcom_id);
$GED_ID = $row->gedcom_id;
}
// SQL may have matched on private data or gedcom tags, so check again against privatized data.
$record = WT_Individual::getInstance($row->xref, $row->gedcom_id, $row->gedcom);
// Ignore non-genealogical data
$gedrec = preg_replace('/\\n\\d (_UID|_WT_USER|FILE|FORM|TYPE|CHAN|REFN|RESN) .*/', '', $record->getGedcom());
// Ignore links and tags
$gedrec = preg_replace('/\\n\\d ' . WT_REGEX_TAG . '( @' . WT_REGEX_XREF . '@)?/', '', $gedrec);
// Ignore tags
$gedrec = preg_replace('/\\n\\d ' . WT_REGEX_TAG . ' ?/', '', $gedrec);
// Re-apply the filtering
$gedrec = WT_I18N::strtoupper($gedrec);
foreach ($queryregex as $regex) {
if (!preg_match('/' . $regex . '/', $gedrec)) {
continue 2;
}
}
$list[] = $record;
}
// Switch privacy file if necessary
if ($GED_ID != WT_GED_ID) {
$GEDCOM = WT_GEDCOM;
load_gedcom_settings(WT_GED_ID);
}
return $list;
}
/**
* Fetch a list of individuals with specified names
*
* To search for unknown names, use $surn="@N.N.", $salpha="@" or $galpha="@"
* To search for names with no surnames, use $salpha=","
*
* @param string $surn if set, only fetch people with this surname
* @param string $salpha if set, only fetch surnames starting with this letter
* @param string $galpha if set, only fetch given names starting with this letter
* @param bool $marnm if set, include married names
* @param bool $fams if set, only fetch individuals with FAMS records
* @param int $ged_id if set, only fetch individuals from this gedcom
*
* @return WT_Individual[]
*/
public static function individuals($surn, $salpha, $galpha, $marnm, $fams, $ged_id)
{
$sql = "SELECT i_id AS xref, i_file AS gedcom_id, i_gedcom AS gedcom, n_full " . "FROM `##individuals` " . "JOIN `##name` ON (n_id=i_id AND n_file=i_file) " . ($fams ? "JOIN `##link` ON (n_id=l_from AND n_file=l_file AND l_type='FAMS') " : "") . "WHERE n_file={$ged_id} " . ($marnm ? "" : "AND n_type!='_MARNM'");
if ($surn) {
$sql .= " AND n_surn COLLATE '" . WT_I18N::$collation . "'=" . WT_DB::quote($surn);
} elseif ($salpha == ',') {
$sql .= " AND n_surn=''";
} elseif ($salpha == '@') {
$sql .= " AND n_surn='@N.N.'";
} elseif ($salpha) {
$sql .= " AND " . self::_getInitialSql('n_surn', $salpha);
} else {
// All surnames
$sql .= " AND n_surn NOT IN ('', '@N.N.')";
}
if ($galpha) {
$sql .= " AND " . self::_getInitialSql('n_givn', $galpha);
}
$sql .= " ORDER BY CASE n_surn WHEN '@N.N.' THEN 1 ELSE 0 END, n_surn COLLATE '" . WT_I18N::$collation . "', CASE n_givn WHEN '@P.N.' THEN 1 ELSE 0 END, n_givn COLLATE '" . WT_I18N::$collation . "'";
$list = array();
$rows = WT_DB::prepare($sql)->fetchAll();
foreach ($rows as $row) {
$person = WT_Individual::getInstance($row->xref, $row->gedcom_id, $row->gedcom);
// The name from the database may be private - check the filtered list...
foreach ($person->getAllNames() as $n => $name) {
if ($name['fullNN'] == $row->n_full) {
$person->setPrimaryName($n);
// We need to clone $person, as we may have multiple references to the
// same person in this list, and the "primary name" would otherwise
// be shared amongst all of them.
$list[] = clone $person;
break;
}
}
}
return $list;
}
/**
* XML <List> start element handler
*
* @see ListEHandler()
*
* @param array $attrs an array of key value pairs for the attributes
*/
function ListSHandler($attrs)
{
global $gedrec, $repeats, $repeatBytes, $list, $repeatsStack, $processRepeats, $parser, $vars, $sortby;
global $GEDCOM;
$processRepeats++;
if ($processRepeats > 1) {
return;
}
$match = array();
if (isset($attrs['sortby'])) {
$sortby = $attrs['sortby'];
if (preg_match("/\\\$(\\w+)/", $sortby, $match)) {
$sortby = $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 = WT_DB::prepare("SELECT xref, gedcom_id, 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=?" . " GROUP BY xref" . " )")->execute(array(WT_GED_ID))->fetchAll();
$list = array();
foreach ($rows as $row) {
$list[] = WT_GedcomRecord::getInstance($row->xref, $row->gedcom_id, $row->gedcom);
}
break;
case "individual":
case "family":
$sql_col_prefix = substr($listname, 0, 1) . "_";
// i_ for individual, f_ for family, etc.
$sql_join = array();
$sql_where = array($sql_col_prefix . "file=" . WT_GED_ID);
$sql_order_by = array();
foreach ($attrs as $attr => $value) {
if (strpos($attr, "filter") === 0 && $value) {
// Substitute global vars
$value = preg_replace_callback('/\\$(\\w+)/', function ($matches) use($vars) {
return $vars[$matches[1]]['id'];
}, $value);
// 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={$sql_col_prefix}file AND {$attr}.d_gid={$sql_col_prefix}id)";
$sql_where[] = "{$attr}.d_fact='{$match[1]}'";
$date = new WT_Date($match[3]);
if ($match[2] == "LTE") {
$sql_where[] = "{$attr}.d_julianday2<=" . $date->minJD();
} else {
$sql_where[] = "{$attr}.d_julianday1>=" . $date->minJD();
}
if ($sortby == $match[1]) {
$sortby = "";
$sql_order_by[] = "{$attr}.d_julianday1";
}
unset($attrs[$attr]);
// This filter has been fully processed
} elseif ($listname == "individual" && preg_match('/^NAME CONTAINS (.*)$/', $value, $match)) {
// Do nothing, unless you have to
if ($match[1] != "" or $sortby == "NAME") {
$sql_join[] = "JOIN `##name` AS {$attr} ON (n_file={$sql_col_prefix}file AND n_id={$sql_col_prefix}id)";
// Search the DB only if there is any name supplied
if ($match[1] != "") {
$names = explode(" ", $match[1]);
foreach ($names as $name) {
$sql_where[] = "{$attr}.n_full LIKE " . WT_DB::quote("%{$name}%");
}
}
// Let the DB do the name sorting even when no name was entered
if ($sortby == "NAME") {
$sortby = "";
$sql_order_by[] = "{$attr}.n_sort";
}
}
unset($attrs[$attr]);
// This filter has been fully processed
} elseif ($listname == "individual" && preg_match('/^REGEXP \\/(.+)\\//', $value, $match)) {
$sql_where[] = "i_gedcom REGEXP '" . $match[1] . "'";
unset($attrs[$attr]);
// This filter has been fully processed
} elseif ($listname == "family" && preg_match('/^REGEXP \\/(.+)\\//', $value, $match)) {
$sql_where[] = "f_gedcom REGEXP '" . $match[1] . "'";
unset($attrs[$attr]);
// This filter has been fully processed
} elseif ($listname == "family" && preg_match('/^NAME CONTAINS (.+)$/', $value, $match)) {
// Eventually, family "names" will be stored in wt_name. Until then, an extra is needed....
$sql_join[] = "JOIN `##link` AS {$attr}a ON ({$attr}a.l_file={$sql_col_prefix}file AND {$attr}a.l_from={$sql_col_prefix}id)";
$sql_join[] = "JOIN `##name` AS {$attr}b ON ({$attr}b.n_file={$sql_col_prefix}file AND n_id={$sql_col_prefix}id)";
$sql_where[] = "{$attr}a.l_type=IN ('HUSB, 'WIFE')";
$sql_where[] = "{$attr}.n_full LIKE " . WT_DB::quote("%{$match[1]}%");
if ($sortby == "NAME") {
$sortby = "";
$sql_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={$sql_col_prefix}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={$sql_col_prefix}id)";
$sql_where[] = "{$attr}a.p_place LIKE " . WT_DB::quote("%{$match[1]}%");
// Don't unset this filter. This is just the first primary PLAC filter to reduce the returned list from the DB
} elseif ($listname == "individual" && preg_match('/^(\\w*):*(\\w*) CONTAINS (.+)$/', $value, $match)) {
$query = "";
// Level 1 tag
if ($match[1] != "") {
$query .= "%1 {$match[1]}%";
}
// Level 2 tag
if ($match[2] != "") {
$query .= "%2 {$match[2]}%";
}
// Contains what?
if ($match[3] != "") {
$query .= "%{$match[3]}%";
}
$sql_where[] = "i_gedcom LIKE " . WT_DB::quote($query);
} elseif ($listname == "family" && preg_match('/^(\\w*):*(\\w*) CONTAINS (.+)$/', $value, $match)) {
$query = "";
// Level 1 tag
if ($match[1] != "") {
$query .= "%1 {$match[1]}%";
}
// Level 2 tag
if ($match[2] != "") {
$query .= "%2 {$match[2]}%";
}
// Contains what?
if ($match[3] != "") {
$query .= "%{$match[3]}%";
}
$sql_where[] = "f_gedcom LIKE " . WT_DB::quote($query);
} else {
// TODO: what other filters can we apply in SQL?
}
}
}
if ($listname == "family") {
$list = search_fams_custom($sql_join, $sql_where, $sql_order_by);
} else {
$list = search_indis_custom($sql_join, $sql_where, $sql_order_by);
}
// Clean up the SQL queries - they will not be used again
unset($sql_join, $sql_where, $sql_order_by);
break;
default:
die("Invalid list name: {$listname}");
}
$filters = array();
$filters2 = array();
if (isset($attrs['filter1']) and count($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 @(.+)@/", $gedrec, $match)) {
$value = "'" . $match[1] . "'";
}
} elseif ($id == "fact") {
$value = "'{$fact}'";
} elseif ($id == "desc") {
$value = "'{$desc}'";
} else {
if (preg_match("/\\d {$id} (.+)/", $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 = $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 ($list as $key => $record) {
foreach ($filters as $filter) {
if (!preg_match("/" . $filter . "/i", $record->privatizeGedcom(WT_USER_ACCESS_LEVEL))) {
unset($list[$key]);
break;
}
}
}
}
if ($filters2) {
$mylist = array();
foreach ($list as $indi) {
$key = $indi->getXref();
$grec = $indi->privatizeGedcom(WT_USER_ACCESS_LEVEL);
$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 = get_gedcom_value($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 = get_sub_record(1, $tag, $grec);
}
$level = count($tags);
switch ($expr) {
case "GTE":
if ($t == "DATE") {
$date1 = new WT_Date($v);
$date2 = new WT_Date($val);
$keep = WT_Date::Compare($date1, $date2) >= 0;
} elseif ($val >= $v) {
$keep = true;
}
break;
case "LTE":
if ($t == "DATE") {
$date1 = new WT_Date($v);
$date2 = new WT_Date($val);
$keep = WT_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;
}
}
$list = $mylist;
}
switch ($sortby) {
case "NAME":
uasort($list, array("WT_GedcomRecord", "compare"));
break;
case "CHAN":
uasort($list, function (WT_GedcomRecord $x, WT_GedcomRecord $y) {
$f1 = $x->getFirstFact('CHAN');
$f2 = $y->getFirstFact('CHAN');
if ($f1 && $f2) {
$d1 = $f1->getDate();
$d2 = $f2->getDate();
$cmp = WT_Date::compare($d1, $d2);
if ($cmp) {
return $cmp;
} else {
// Same date. Compare times
preg_match('/\\n3 TIME (.+)/', $f1->getGedcom(), $m1);
preg_match('/\\n3 TIME (.+)/', $f2->getGedcom(), $m2);
return strcmp($m1[1], $m2[1]);
}
} else {
return 0;
}
});
break;
case "BIRT:DATE":
uasort($list, array("WT_Individual", "CompareBirtDate"));
break;
case "DEAT:DATE":
uasort($list, array("WT_Individual", "CompareDeatDate"));
break;
case "MARR:DATE":
uasort($list, array("WT_Family", "compareMarrDate"));
break;
default:
// unsorted or already sorted by SQL
break;
}
array_push($repeatsStack, array($repeats, $repeatBytes));
$repeatBytes = xml_get_current_line_number($parser) + 1;
}
