function indis_array($surn, $soundex_std, $soundex_dm)
{
global $TBLPREFIX;
$sql = "SELECT DISTINCT n_id" . " FROM {$TBLPREFIX}name" . " WHERE n_file=?" . " AND n_type!=?" . " AND (n_surn=? OR n_surname=?";
$args = array(PGV_GED_ID, '_MARNM', $surn, $surn);
if ($soundex_std) {
$sql .= " OR n_soundex_surn_std=?";
$args[] = soundex_std($surn);
}
if ($soundex_dm) {
$sql .= " OR n_soundex_surn_dm=?";
$args[] = soundex_dm($surn);
}
$sql .= ") ORDER BY n_sort";
$rows = PGV_DB::prepare($sql)->execute($args)->fetchAll();
// var_dump($sql); var_dump($rows);
$data = array();
foreach ($rows as $row) {
$data[$row->n_id] = Person::getInstance($row->n_id);
}
return $data;
}
function advancedSearch($justSql = false, $table = "individuals", $prefix = "i")
{
global $TBLPREFIX, $gedcom_record_cache;
DMsoundex("", "opencache");
$this->myindilist = array();
$fct = count($this->fields);
if ($fct == 0) {
return;
}
$namesTable = false;
$datesTable = false;
$placesTable = false;
$famsTable = false;
$famcTable = false;
$sql = '';
if ($justSql) {
$sqlfields = "SELECT DISTINCT {$prefix}_id, {$prefix}_file";
} else {
$sqlfields = "SELECT i_id, i_gedcom, i_isdead, i_file, i_sex";
}
$sqltables = " FROM " . $TBLPREFIX . $table;
$sqlwhere = " WHERE " . $prefix . "_file=" . PGV_GED_ID;
$keepfields = $this->fields;
for ($i = 0; $i < $fct; $i++) {
$field = $this->fields[$i];
if (empty($field)) {
continue;
}
$value = '';
if (isset($this->values[$i])) {
$value = $this->values[$i];
}
if (empty($value)) {
continue;
}
$parts = preg_split("/:/", $field);
//-- handle names seperately
if ($parts[0] == "NAME") {
// The pgv_name table contains both names and soundex values
if (!$namesTable) {
$sqltables .= " JOIN {$TBLPREFIX}name ON (i_file=n_file AND i_id=n_id) ";
$namesTable = true;
}
switch (end($parts)) {
case 'SDX_STD':
$sdx = explode(':', soundex_std($value));
foreach ($sdx as $k => $v) {
if ($parts[1] == 'GIVN') {
$sdx[$k] = 'n_soundex_givn_std ' . PGV_DB::$LIKE . " '%{$v}%'";
} else {
$sdx[$k] = 'n_soundex_surn_std ' . PGV_DB::$LIKE . " '%{$v}%'";
}
}
$sqlwhere .= ' AND (' . implode(' OR ', $sdx) . ')';
break;
case 'SDX':
// SDX uses DM by default.
// SDX uses DM by default.
case 'SDX_DM':
$sdx = explode(':', soundex_dm($value));
foreach ($sdx as $k => $v) {
if ($parts[1] == 'GIVN') {
$sdx[$k] = 'n_soundex_givn_dm ' . PGV_DB::$LIKE . " '%{$v}%'";
} else {
$sdx[$k] = 'n_soundex_surn_dm ' . PGV_DB::$LIKE . " '%{$v}%'";
}
}
$sqlwhere .= ' AND (' . implode(' OR ', $sdx) . ')';
break;
case 'EXACT':
// Exact match.
switch ($parts[1]) {
case 'GIVN':
// Allow for exact match on multiple given names.
$sqlwhere .= ' AND (n_givn ' . PGV_DB::$LIKE . " " . PGV_DB::quote($value) . " OR n_givn " . PGV_DB::$LIKE . " " . PGV_DB::quote("{$value} %") . " OR n_givn " . PGV_DB::$LIKE . " " . PGV_DB::quote("% {$value}") . " OR n_givn " . PGV_DB::$LIKE . " " . PGV_DB::quote("% {$value} %") . ")";
break;
case 'SURN':
$sqlwhere .= ' AND n_surname ' . PGV_DB::$LIKE . " " . PGV_DB::quote($value);
break;
default:
$sqlwhere .= ' AND n_full ' . PGV_DB::$LIKE . " " . PGV_DB::quote($value);
break;
}
break;
case 'BEGINS':
// "Begins with" match.
switch ($parts[1]) {
case 'GIVN':
// Allow for match on start of multiple given names
$sqlwhere .= ' AND (n_givn ' . PGV_DB::$LIKE . " " . PGV_DB::quote("{$value}%") . " OR n_givn " . PGV_DB::$LIKE . " " . PGV_DB::quote("% {$value}%") . ")";
break;
case 'SURN':
$sqlwhere .= ' AND n_surname ' . PGV_DB::$LIKE . " " . PGV_DB::quote("{$value}%");
break;
default:
$sqlwhere .= ' AND n_full ' . PGV_DB::$LIKE . " " . PGV_DB::quote("{$value}%");
break;
}
break;
case 'CONTAINS':
default:
// Partial match.
switch ($parts[1]) {
case 'GIVN':
$sqlwhere .= ' AND n_givn ' . PGV_DB::$LIKE . " " . PGV_DB::quote("%{$value}%");
break;
case 'SURN':
$sqlwhere .= ' AND n_surname ' . PGV_DB::$LIKE . " " . PGV_DB::quote("%{$value}%");
break;
default:
$sqlwhere .= ' AND n_full ' . PGV_DB::$LIKE . " " . PGV_DB::quote("%{$value}%");
break;
}
break;
}
} else {
if (isset($parts[1]) && $parts[1] == "DATE") {
if (!$datesTable) {
$sqltables .= ", " . $TBLPREFIX . "dates";
$sqlwhere .= " AND " . $prefix . "_file=d_file AND " . $prefix . "_id=d_gid";
$datesTable = true;
}
$sqlwhere .= " AND (d_fact='" . $parts[0] . "'";
$date = new GedcomDate($value);
if ($date->isOK()) {
$jd1 = $date->date1->minJD;
if ($date->date2) {
$jd2 = $date->date2->maxJD;
} else {
$jd2 = $date->date1->maxJD;
}
if (!empty($this->plusminus[$i])) {
$adjd = $this->plusminus[$i] * 365;
//print $jd1.":".$jd2.":".$adjd;
$jd1 = $jd1 - $adjd;
$jd2 = $jd2 + $adjd;
}
$sqlwhere .= " AND d_julianday1>=" . $jd1 . " AND d_julianday2<=" . $jd2;
}
$sqlwhere .= ") ";
} else {
if (isset($parts[1]) && $parts[1] == "PLAC") {
if (!$placesTable) {
$sqltables .= ", " . $TBLPREFIX . "places, " . $TBLPREFIX . "placelinks";
$sqlwhere .= " AND " . $prefix . "_file=p_file AND p_file=pl_file AND " . $prefix . "_id=pl_gid AND pl_p_id=p_id";
$placesTable = true;
}
//-- soundex search
//if (end($parts)=="SDX") {
$places = preg_split("/[, ]+/", $value);
$parr = array();
for ($j = 0; $j < count($places); $j++) {
$parr[$j] = DMsoundex($places[$j]);
}
$sqlwhere .= " AND (";
$fnc = 0;
$field = "p_dm_soundex";
foreach ($parr as $name) {
foreach ($name as $name1) {
if ($fnc > 0) {
$sqlwhere .= " OR ";
}
$fnc++;
$sqlwhere .= $field . " " . PGV_DB::$LIKE . " " . PGV_DB::quote("%{$name1}%");
}
}
$sqlwhere .= ") ";
//}
} else {
if ($parts[0] == 'FAMS') {
if (!$famsTable) {
$sqltables .= ", " . $TBLPREFIX . "families as FAMS";
$sqlwhere .= " AND i_file=FAMS.f_file";
$famsTable = true;
}
//-- alter the fields and recurse to generate a subquery for spouse/parent fields
$oldfields = $this->fields;
for ($j = 0; $j < $fct; $j++) {
//-- if it doesn't start with FAMS or FAMC then remove that field
if (preg_match("/^" . $parts[0] . ":/", $this->fields[$j]) == 0) {
$this->fields[$j] = '';
} else {
$this->fields[$j] = preg_replace("/^" . $parts[0] . ":/", "", $this->fields[$j]);
}
}
$sqlwhere .= " AND (FAMS.f_husb=i_id OR FAMS.f_wife=i_id)";
$subsql = $this->advancedSearch(true, "families", "f");
$sqlwhere .= " AND ROW(FAMS.f_id, FAMS.f_file) IN (" . $subsql . ")";
$this->fields = $oldfields;
//-- remove all of the fam fields so they don't show up again
for ($j = 0; $j < $fct; $j++) {
//-- if it does start with FAMS or FAMC then remove that field
if (preg_match("/^" . $parts[0] . ":/", $this->fields[$j]) > 0) {
$this->fields[$j] = '';
}
}
} else {
if ($parts[0] == 'FAMC') {
if (!$famcTable) {
$sqltables .= ", " . $TBLPREFIX . "families as FAMC";
$sqlwhere .= " AND i_file=FAMC.f_file";
$famcTable = true;
}
//-- alter the fields and recurse to generate a subquery for spouse/parent fields
$oldfields = $this->fields;
for ($j = 0; $j < $fct; $j++) {
//-- if it doesn't start with FAMS or FAMC then remove that field
if (preg_match("/^" . $parts[0] . ":/", $this->fields[$j]) == 0) {
$this->fields[$j] = '';
} else {
$this->fields[$j] = preg_replace("/^" . $parts[0] . ":/", "", $this->fields[$j]);
}
}
$sqlwhere .= " AND (FAMC.f_chil " . PGV_DB::$LIKE . " CONCAT('%',i_id,';%'))";
$subsql = $this->advancedSearch(true, "families", "f");
$sqlwhere .= " AND ROW(FAMC.f_id, FAMC.f_file) IN (" . $subsql . ")";
$this->fields = $oldfields;
//-- remove all of the fam fields so they don't show up again
for ($j = 0; $j < $fct; $j++) {
//-- if it does start with FAMS or FAMC then remove that field
if (preg_match("/^" . $parts[0] . ":/", $this->fields[$j]) > 0) {
$this->fields[$j] = '';
}
}
} else {
if ($parts[0] == 'HUSB' || $parts[0] == 'WIFE') {
if (!$famsTable) {
$sqltables .= ", " . $TBLPREFIX . "individuals";
$sqlwhere .= " AND i_file=f_file";
$famsTable = true;
}
//-- alter the fields and recurse to generate a subquery for spouse/parent fields
$oldfields = $this->fields;
for ($j = 0; $j < $fct; $j++) {
//-- if it doesn't start with FAMS or FAMC then remove that field
if (preg_match("/^" . $parts[0] . ":/", $this->fields[$j]) == 0) {
$this->fields[$j] = '';
} else {
$this->fields[$j] = preg_replace("/^" . $parts[0] . ":/", "", $this->fields[$j]);
}
}
$subsql = $this->advancedSearch(true, "individuals", "i");
if ($parts[0] == 'HUSB') {
$sqlwhere .= " AND ROW(f_husb, f_file) IN (" . $subsql . ")";
}
if ($parts[0] == 'WIFE') {
$sqlwhere .= " AND ROW(f_wife, f_file) IN (" . $subsql . ")";
}
$this->fields = $oldfields;
//-- remove all of the fam fields so they don't show up again
for ($j = 0; $j < $fct; $j++) {
//-- if it does start with HUSB or WIFE then remove that field
if (preg_match("/^" . $parts[0] . ":/", $this->fields[$j]) > 0) {
$this->fields[$j] = '';
}
}
} else {
$sqlwhere .= " AND i_gedcom " . PGV_DB::$LIKE . " ";
$ct = count($parts);
$liketmp = '';
for ($j = 0; $j < $ct; $j++) {
$liketmp .= "%" . ($j + 1) . " " . $parts[$j] . " %";
// if ($j<$ct-1) {
// $sqlwhere .= "%";
// } else {
$liketmp .= "%{$value}%";
// }
}
$sqlwhere .= PGV_DB::quote($liketmp);
}
}
}
}
}
}
}
$sql = $sqlfields . $sqltables . $sqlwhere;
// print $sql;
if ($justSql) {
return $sql;
}
$rows = PGV_DB::prepare($sql)->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
$row['xref'] = $row['i_id'];
$row['ged_id'] = $row['i_file'];
$row['type'] = 'INDI';
$row['gedrec'] = $row['i_gedcom'];
$object = Person::getInstance($row);
$this->myindilist[$row['i_id']] = $object;
}
$this->fields = $keepfields;
}
function search_indis_soundex($soundex, $lastname, $firstname, $place, $geds)
{
global $TBLPREFIX, $gBitDb;
$sql = "SELECT DISTINCT 'INDI' AS type, i_id AS xref, i_file AS ged_id, i_gedcom AS gedrec, i_isdead, i_sex FROM {$TBLPREFIX}individuals";
if ($place) {
$sql .= " JOIN {$TBLPREFIX}placelinks ON (pl_file=i_file AND pl_gid=i_id)";
$sql .= " JOIN {$TBLPREFIX}places ON (p_file=pl_file AND pl_p_id=p_id)";
}
if ($firstname || $lastname) {
$sql .= " JOIN {$TBLPREFIX}name ON (i_file=n_file AND i_id=n_id)";
}
$sql .= ' WHERE i_file IN (' . implode(',', $geds) . ')';
switch ($soundex) {
case 'Russell':
$givn_sdx = explode(':', soundex_std($firstname));
$surn_sdx = explode(':', soundex_std($lastname));
$plac_sdx = explode(':', soundex_std($place));
$field = 'std';
break;
default:
case 'DaitchM':
$givn_sdx = explode(':', soundex_dm($firstname));
$surn_sdx = explode(':', soundex_dm($lastname));
$plac_sdx = explode(':', soundex_dm($place));
$field = 'dm';
break;
}
if ($firstname && $givn_sdx) {
foreach ($givn_sdx as $k => $v) {
$givn_sdx[$k] = "n_soundex_givn_{$field} LIKE '%{$v}%'";
}
$sql .= ' AND (' . implode(' OR ', $givn_sdx) . ')';
}
if ($lastname && $surn_sdx) {
foreach ($surn_sdx as $k => $v) {
$surn_sdx[$k] = "n_soundex_surn_{$field} LIKE '%{$v}%'";
}
$sql .= ' AND (' . implode(' OR ', $surn_sdx) . ')';
}
if ($place && $plac_sdx) {
foreach ($plac_sdx as $k => $v) {
$plac_sdx[$k] = "p_{$field}_soundex LIKE '%{$v}%'";
}
$sql .= ' AND (' . implode(' OR ', $plac_sdx) . ')';
}
// Group results by gedcom, to minimise switching between privacy files
$sql .= ' ORDER BY ged_id';
$list = array();
$rows = $gBitDb->getAssoc($sql);
$GED_ID = PGV_GED_ID;
foreach ($rows as $row) {
// Switch privacy file if necessary
if ($row['ged_id'] != $GED_ID) {
$GEDCOM = get_gedcom_from_id($row['ged_id']);
load_privacy_file($row['ged_id']);
$GED_ID = $row['ged_id'];
}
$indi = Person::getInstance($row);
if ($indi->canDisplayName()) {
$list[] = $indi;
}
}
// Switch privacy file if necessary
if ($GED_ID != PGV_GED_ID) {
$GEDCOM = get_gedcom_from_id(PGV_GED_ID);
load_privacy_file(PGV_GED_ID);
}
return $list;
}
function update_names($xref, $ged_id, $record)
{
global $TBLPREFIX, $gBitDb;
if ($record->getType() != 'FAM' && $record->getXref()) {
foreach ($record->getAllNames() as $n => $name) {
if ($record->getType() == 'INDI') {
if ($name['givn'] == '@P.N.') {
$soundex_givn_std = null;
$soundex_givn_dm = null;
} else {
$soundex_givn_std = "'" . soundex_std($name['givn']) . "'";
$soundex_givn_dm = "'" . soundex_dm($name['givn']) . "'";
}
if ($name['surn'] == '@N.N.') {
$soundex_surn_std = null;
$soundex_surn_dm = null;
} else {
$soundex_surn_std = "'" . soundex_std($name['surname']) . "'";
$soundex_surn_dm = "'" . soundex_dm($name['surname']) . "'";
}
$gBitDb->query("INSERT INTO {$TBLPREFIX}name (n_file,n_id,n_num,n_type,n_sort,n_full,n_list,n_surname,n_surn,n_givn,n_soundex_givn_std,n_soundex_surn_std,n_soundex_givn_dm,n_soundex_surn_dm) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", array($ged_id, $xref, $n, $name['type'], $name['sort'], $name['fullNN'], $name['listNN'], $name['surname'], $name['surn'], $name['givn'], $soundex_givn_std, $soundex_surn_std, $soundex_givn_dm, $soundex_surn_dm));
} else {
$gBitDb->query("INSERT INTO {$TBLPREFIX}name (n_file,n_id,n_num,n_type,n_sort,n_full,n_list) VALUES (?,?,?,?,?,?,?)", array($ged_id, $xref, $n, $name['type'], $name['sort'], $name['full'], $name['list']));
}
}
}
}
function indis_array($surn, $soundex_std, $soundex_dm)
{
global $TBLPREFIX, $gBitDb;
$sql = "SELECT DISTINCT n_id" . " FROM {$TBLPREFIX}name" . " WHERE n_file=?" . " AND n_type!=?" . " AND (n_surn=? OR n_surname=?";
$args = array(PGV_GED_ID, '_MARNM', $surn, $surn);
if ($soundex_std) {
$sql .= " OR n_soundex_surn_std=?";
$args[] = soundex_std($surn);
}
if ($soundex_dm) {
$sql .= " OR n_soundex_surn_dm=?";
$args[] = soundex_dm($surn);
}
$sql .= ") ORDER BY n_sort";
$rows = $gBitDb->query($sql, $args);
// var_dump($sql); var_dump($rows);
$data = array();
while ($row = $roes->fetchRow()) {
$data[$row[n_id]] = Person::getInstance($row[n_id]);
}
return $data;
}
