summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--app/Report/ReportBase.php162
-rw-r--r--includes/functions/functions_db.php48
-rw-r--r--tests/includes/functions/FunctionsDbTest.php18
3 files changed, 104 insertions, 124 deletions
diff --git a/app/Report/ReportBase.php b/app/Report/ReportBase.php
index acbaaecf35..8945c5acb3 100644
--- a/app/Report/ReportBase.php
+++ b/app/Report/ReportBase.php
@@ -2289,14 +2289,13 @@ function listStartHandler($attrs) {
$list[] = 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();
+ case 'individual':
+ $sql_select = "SELECT DISTINCT i_id AS xref, i_file AS gedcom_id, i_gedcom AS gedcom FROM `##individuals` ";
+ $sql_join = "";
+ $sql_where = " WHERE i_file = " . WT_GED_ID;
+ $sql_order_by = "";
foreach ($attrs as $attr => $value) {
- if ((strpos($attr, "filter") === 0) && $value) {
+ if (strpos($attr, 'filter') === 0 && $value) {
// Substitute global vars
$value = preg_replace_callback(
'/\$(\w+)/',
@@ -2307,106 +2306,153 @@ function listStartHandler($attrs) {
);
// 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]}'";
+ $sql_join .= " JOIN `##dates` AS {$attr} ON ({$attr}.d_file=i_file AND {$attr}.d_gid=i_id)";
+ $sql_where .= " AND {$attr}.d_fact='{$match[1]}'";
$date = new Date($match[3]);
if ($match[2] == "LTE") {
- $sql_where[] = "{$attr}.d_julianday2<=" . $date->minJD();
+ $sql_where .= " AND {$attr}.d_julianday2<=" . $date->minJD();
} else {
- $sql_where[] = "{$attr}.d_julianday1>=" . $date->minJD();
+ $sql_where[] = " AND {$attr}.d_julianday1>=" . $date->minJD();
}
if ($sortby == $match[1]) {
$sortby = "";
- $sql_order_by[] = "{$attr}.d_julianday1";
+ $sql_order_by .= ($sql_order_by ? ", " : " ORDER BY ") . "{$attr}.d_julianday1";
}
unset($attrs[$attr]); // This filter has been fully processed
- } elseif (($listname == "individual") && (preg_match('/^NAME CONTAINS (.*)$/', $value, $match))) {
+ } elseif (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)";
+ $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 $name) {
- $sql_where[] = "{$attr}.n_full LIKE " . Database::quote("%{$name}%");
+ $sql_where .= " AND {$attr}.n_full LIKE " . Database::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";
+ $sql_order_by .= ($sql_order_by ? ", " : " 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 " . Database::quote("%{$match[1]}%");
- if ($sortby == "NAME") {
- $sortby = "";
- $sql_order_by[] = "{$attr}.n_sort";
- }
+ } elseif (preg_match('/^REGEXP \/(.+)\//', $value, $match)) {
+ $sql_where .= " AND i_gedcom REGEXP '" . $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={$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 " . Database::quote("%{$match[1]}%");
- // Don't unset this filter. This is just the first primary PLAC filter to reduce the returned list from the DB
- } /**
- * General Purpose DB Filter for Individual and Family Lists
- * Place any other filter before these filters because they will pick up any filters that has not been processed
- * Also, do not unset() these two filters. These are just the first primary filters to reduce the returned list from the DB
- */ elseif ($listname == "individual" && preg_match('/^(\w*):*(\w*) 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 " . Database::quote("%{$match[1]}%");
+ // Don't unset this filter. This is just initial filtering
+ } elseif (preg_match('/^(\w*):*(\w*) CONTAINS (.+)$/', $value, $match)) {
$query = "";
// Level 1 tag
- if ($match[1] != "") {
+ if ($match[1] !== '') {
$query .= "%1 {$match[1]}%";
}
// Level 2 tag
- if ($match[2] != "") {
+ if ($match[2] !== '') {
$query .= "%2 {$match[2]}%";
}
// Contains what?
- if ($match[3] != "") {
+ if ($match[3] !== '') {
$query .= "%{$match[3]}%";
}
- $sql_where[] = "i_gedcom LIKE " . Database::quote($query);
- } elseif ($listname == "family" && preg_match('/^(\w*):*(\w*) CONTAINS (.+)$/', $value, $match)) {
+ $sql_where .= " AND i_gedcom LIKE " . Database::quote($query);
+ // Don't unset this filter. This is just initial filtering
+ }
+ }
+ }
+
+ $list = array();
+ $rows = Database::prepare(
+ $sql_select . $sql_join . $sql_where . $sql_order_by
+ )->fetchAll();
+
+ foreach ($rows as $row) {
+ $list[] = Individual::getInstance($row->xref, $row->gedcom_id, $row->gedcom);
+ }
+ break;
+
+ case 'family':
+ $sql_select = "SELECT DISTINCT f_id AS xref, f_file AS gedcom_id, f_gedcom AS gedcom FROM `##families`";
+ $sql_join = "";
+ $sql_where = " WHERE f_file=" . WT_GED_ID;
+ $sql_order_by = "";
+ 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=f_file AND {$attr}.d_gid=f_id)";
+ $sql_where .= " AND {$attr}.d_fact='{$match[1]}'";
+ $date = new Date($match[3]);
+ if ($match[2] == "LTE") {
+ $sql_where .= " AND {$attr}.d_julianday2<=" . $date->minJD();
+ } else {
+ $sql_where .= " AND {$attr}.d_julianday1>=" . $date->minJD();
+ }
+ 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 '" . $match[1] . "'";
+ unset($attrs[$attr]); // This filter has been fully processed
+ } elseif (preg_match('/^NAME CONTAINS (.+)$/', $value, $match)) {
+ $sql_join .= " JOIN `##link` AS {$attr}a ON ({$attr}a.l_file=f_file AND {$attr}a.l_from=f_id)";
+ $sql_join .= " JOIN `##name` AS {$attr}b ON ({$attr}b.n_file=f_file AND n_id=f_id)";
+ $sql_where .= " AND {$attr}a.l_type=IN ('HUSB, 'WIFE')";
+ $sql_where .= " AND {$attr}.n_full LIKE " . Database::quote("%{$match[1]}%");
+ 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 " . Database::quote("%{$match[1]}%");
+ // Don't unset this filter. This is just initial filtering
+ } elseif (preg_match('/^(\w*):*(\w*) CONTAINS (.+)$/', $value, $match)) {
$query = "";
// Level 1 tag
- if ($match[1] != "") {
+ if ($match[1] !== '') {
$query .= "%1 {$match[1]}%";
}
// Level 2 tag
- if ($match[2] != "") {
+ if ($match[2] !== '') {
$query .= "%2 {$match[2]}%";
}
// Contains what?
- if ($match[3] != "") {
+ if ($match[3] !== '') {
$query .= "%{$match[3]}%";
}
- $sql_where[] = "f_gedcom LIKE " . Database::quote($query);
- } else {
- // What other filters can we apply in SQL?
+ $sql_where .= " AND f_gedcom LIKE " . Database::quote($query);
+ // Don't unset this filter. This is just initial filtering
}
}
}
- 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);
+
+ $list = array();
+ $rows = Database::prepare(
+ $sql_select . $sql_join . $sql_where . $sql_order_by
+ )->fetchAll();
+
+ foreach ($rows as $row) {
+ $list[] = Family::getInstance($row->xref, $row->gedcom_id, $row->gedcom);
}
- // Clean up the SQL queries - they will not be used again
- unset($sql_join, $sql_where, $sql_order_by);
break;
+
default:
throw new \DomainException('Invalid list name: ' . $listname);
}
diff --git a/includes/functions/functions_db.php b/includes/functions/functions_db.php
index 254ecb7955..bdeb44a48a 100644
--- a/includes/functions/functions_db.php
+++ b/includes/functions/functions_db.php
@@ -119,54 +119,6 @@ function get_note_list($ged_id) {
}
/**
- * Search for INDIs using custom SQL generated by the report engine
- *
- * @param string[] $join
- * @param string[] $where
- * @param string[] $order
- *
- * @return Individual[]
- */
-function search_indis_custom($join, $where, $order) {
- $sql = "SELECT DISTINCT i_id AS xref, i_file AS gedcom_id, i_gedcom AS gedcom FROM `##individuals` " . implode(' ', $join) . ' WHERE ' . implode(' AND ', $where);
- if ($order) {
- $sql .= ' ORDER BY ' . implode(' ', $order);
- }
-
- $list = array();
- $rows = Database::prepare($sql)->fetchAll();
- foreach ($rows as $row) {
- $list[] = Individual::getInstance($row->xref, $row->gedcom_id, $row->gedcom);
- }
-
- return $list;
-}
-
-/**
- * Search for FAMs using custom SQL generated by the report engine
- *
- * @param string[] $join
- * @param string[] $where
- * @param string[] $order
- *
- * @return Family[]
- */
-function search_fams_custom($join, $where, $order) {
- $sql = "SELECT DISTINCT f_id AS xref, f_file AS gedcom_id, f_gedcom AS gedcom FROM `##families` " . implode(' ', $join) . ' WHERE ' . implode(' AND ', $where);
- if ($order) {
- $sql .= ' ORDER BY ' . implode(' ', $order);
- }
-
- $list = array();
- $rows = Database::prepare($sql)->fetchAll();
- foreach ($rows as $row) {
- $list[] = Family::getInstance($row->xref, $row->gedcom_id, $row->gedcom);
- }
-
- return $list;
-}
-
-/**
* Search all individuals
*
* @param string[] $query array of search terms
diff --git a/tests/includes/functions/FunctionsDbTest.php b/tests/includes/functions/FunctionsDbTest.php
index fd36ce1e49..450a2b4f65 100644
--- a/tests/includes/functions/FunctionsDbTest.php
+++ b/tests/includes/functions/FunctionsDbTest.php
@@ -76,24 +76,6 @@ class FunctionsDbTest extends PHPUnit_Framework_TestCase {
}
/**
- * Test that function search_indis_custom() exists in the correct namespace.
- *
- * @return void
- */
- public function testFunctionSearchIndisCustomExists() {
- $this->assertEquals(function_exists(__NAMESPACE__ . '\\search_indis_custom'), true);
- }
-
- /**
- * Test that function search_fams_custom() exists in the correct namespace.
- *
- * @return void
- */
- public function testFunctionSearchFamsCustomExists() {
- $this->assertEquals(function_exists(__NAMESPACE__ . '\\search_fams_custom'), true);
- }
-
- /**
* Test that function search_indis() exists in the correct namespace.
*
* @return void
generated by cgit v1.3 (git 2.53.0) at 2026-07-05 15:49:17 +0000