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; }