Update statistics to avoid MySQL-specific functions

9 files changed, 54 insertions, 58 deletions

diff --git a/app/Statistics/Google/ChartAge.php b/app/Statistics/Google/ChartAge.php
index 0ae0939b17..2bc0530b8c 100644
--- a/app/Statistics/Google/ChartAge.php
+++ b/app/Statistics/Google/ChartAge.php
@@ -58,7 +58,7 @@ class ChartAge extends AbstractGoogle
         return $this->runSql(
             'SELECT'
             . ' ROUND(AVG(death.d_julianday2-birth.d_julianday1)/365.25,1) AS age,'
-            . ' FLOOR(death.d_year/100+1) AS century,'
+            . ' ROUND((death.d_year - 50) / 100) AS century,'
             . ' i_sex AS sex'
             . ' FROM'
             . ' `##dates` AS death,'
diff --git a/app/Statistics/Google/ChartBirth.php b/app/Statistics/Google/ChartBirth.php
index ebf3b7bdd9..688b7a729f 100644
--- a/app/Statistics/Google/ChartBirth.php
+++ b/app/Statistics/Google/ChartBirth.php
@@ -58,7 +58,7 @@ class ChartBirth extends AbstractGoogle
     private function queryRecords(): array
     {
         $query = DB::table('dates')
-            ->selectRaw('FLOOR(d_year / 100 + 1) AS century')
+            ->selectRaw('ROUND((d_year - 50) / 100) AS century')
             ->selectRaw('COUNT(*) AS total')
             ->where('d_file', '=', $this->tree->id())
             ->where('d_year', '<>', 0)
@@ -107,7 +107,7 @@ class ChartBirth extends AbstractGoogle
         $counts    = [];
         foreach ($rows as $values) {
             $counts[] = intdiv(100 * $values->total, $tot);
-            $centuries .= $this->centuryHelper->centuryName($values->century) . ' - ' . I18N::number($values->total) . '|';
+            $centuries .= $this->centuryHelper->centuryName((int) $values->century) . ' - ' . I18N::number((int) $values->total) . '|';
         }
 
         $chd    = $this->arrayToExtendedEncoding($counts);
diff --git a/app/Statistics/Google/ChartChildren.php b/app/Statistics/Google/ChartChildren.php
index cc0f60a8f1..7c8ebcf41b 100644
--- a/app/Statistics/Google/ChartChildren.php
+++ b/app/Statistics/Google/ChartChildren.php
@@ -59,7 +59,7 @@ class ChartChildren extends AbstractGoogle
     {
         $query = DB::table('families')
             ->selectRaw('ROUND(AVG(f_numchil),2) AS num')
-            ->selectRaw('FLOOR(d_year / 100 + 1) AS century')
+            ->selectRaw('ROUND((d_year - 50) / 100) AS century')
             ->join('dates', function (JoinClause $join) {
                 $join->on('d_file', '=', 'f_file')
                     ->on('d_gid', '=', 'f_id');
diff --git a/app/Statistics/Google/ChartDeath.php b/app/Statistics/Google/ChartDeath.php
index a516230cbd..e51203a392 100644
--- a/app/Statistics/Google/ChartDeath.php
+++ b/app/Statistics/Google/ChartDeath.php
@@ -58,7 +58,7 @@ class ChartDeath extends AbstractGoogle
     private function queryRecords(): array
     {
         $query = DB::table('dates')
-            ->selectRaw('FLOOR(d_year / 100 + 1) AS century')
+            ->selectRaw('ROUND((d_year - 50) / 100) AS century')
             ->selectRaw('COUNT(*) AS total')
             ->where('d_file', '=', $this->tree->id())
             ->where('d_year', '<>', 0)
diff --git a/app/Statistics/Google/ChartDivorce.php b/app/Statistics/Google/ChartDivorce.php
index 317d77d37d..79b6126e4f 100644
--- a/app/Statistics/Google/ChartDivorce.php
+++ b/app/Statistics/Google/ChartDivorce.php
@@ -58,7 +58,7 @@ class ChartDivorce extends AbstractGoogle
     private function queryRecords(): array
     {
         $query = DB::table('dates')
-            ->selectRaw('FLOOR(d_year / 100 + 1) AS century')
+            ->selectRaw('ROUND((d_year - 50) / 100) AS century')
             ->selectRaw('COUNT(*) AS total')
             ->where('d_file', '=', $this->tree->id())
             ->where('d_year', '<>', 0)
diff --git a/app/Statistics/Google/ChartMarriage.php b/app/Statistics/Google/ChartMarriage.php
index b69c90d0d2..dbc6661171 100644
--- a/app/Statistics/Google/ChartMarriage.php
+++ b/app/Statistics/Google/ChartMarriage.php
@@ -58,7 +58,7 @@ class ChartMarriage extends AbstractGoogle
     private function queryRecords(): array
     {
         $query = DB::table('dates')
-            ->selectRaw('FLOOR(d_year / 100 + 1) AS century')
+            ->selectRaw('ROUND((d_year - 50) / 100) AS century')
             ->selectRaw('COUNT(*) AS total')
             ->where('d_file', '=', $this->tree->id())
             ->where('d_year', '<>', 0)
diff --git a/app/Statistics/Google/ChartMarriageAge.php b/app/Statistics/Google/ChartMarriageAge.php
index f6eead497c..d71987b5da 100644
--- a/app/Statistics/Google/ChartMarriageAge.php
+++ b/app/Statistics/Google/ChartMarriageAge.php
@@ -61,7 +61,7 @@ class ChartMarriageAge extends AbstractGoogle
         return $this->runSql(
             'SELECT '
             . ' ROUND(AVG(married.d_julianday2-birth.d_julianday1-182.5)/365.25,1) AS age, '
-            . ' FLOOR(married.d_year/100+1) AS century, '
+            .  ' ROUND((married.d_year - 50) / 100) AS century,'
             . " 'M' AS sex "
             . 'FROM `##dates` AS married '
             . 'JOIN `##families` AS fam ON (married.d_gid=fam.f_id AND married.d_file=fam.f_file) '
@@ -75,7 +75,7 @@ class ChartMarriageAge extends AbstractGoogle
             . 'UNION ALL '
             . 'SELECT '
             . ' ROUND(AVG(married.d_julianday2-birth.d_julianday1-182.5)/365.25,1) AS age, '
-            . ' FLOOR(married.d_year/100+1) AS century, '
+            . ' ROUND((married.d_year - 50) / 100) AS century,'
             . " 'F' AS sex "
             . 'FROM `##dates` AS married '
             . 'JOIN `##families` AS fam ON (married.d_gid=fam.f_id AND married.d_file=fam.f_file) '
diff --git a/app/Statistics/Google/ChartNoChildrenFamilies.php b/app/Statistics/Google/ChartNoChildrenFamilies.php
index 725f30849a..8c211bfb0e 100644
--- a/app/Statistics/Google/ChartNoChildrenFamilies.php
+++ b/app/Statistics/Google/ChartNoChildrenFamilies.php
@@ -61,7 +61,7 @@ class ChartNoChildrenFamilies extends AbstractGoogle
     private function queryRecords(int $year1, int $year2): array
     {
         $query = DB::table('families')
-            ->selectRaw('FLOOR(d_year / 100 + 1) AS century')
+            ->selectRaw('ROUND((d_year - 50) / 100) AS century')
             ->selectRaw('COUNT(*) AS count')
             ->join('dates', function (JoinClause $join) {
                 $join->on('d_file', '=', 'f_file')
diff --git a/app/Statistics/Repository/IndividualRepository.php b/app/Statistics/Repository/IndividualRepository.php
index 00f61fc6b6..741f0cf765 100644
--- a/app/Statistics/Repository/IndividualRepository.php
+++ b/app/Statistics/Repository/IndividualRepository.php
@@ -29,7 +29,6 @@ use Fisharebest\Webtrees\Statistics\Google\ChartBirth;
 use Fisharebest\Webtrees\Statistics\Google\ChartCommonGiven;
 use Fisharebest\Webtrees\Statistics\Google\ChartCommonSurname;
 use Fisharebest\Webtrees\Statistics\Google\ChartDeath;
-use Fisharebest\Webtrees\Statistics\Google\ChartFamily;
 use Fisharebest\Webtrees\Statistics\Google\ChartFamilyWithSources;
 use Fisharebest\Webtrees\Statistics\Google\ChartIndividual;
 use Fisharebest\Webtrees\Statistics\Google\ChartMortality;
@@ -38,6 +37,7 @@ use Fisharebest\Webtrees\Statistics\Helper\Sql;
 use Fisharebest\Webtrees\Statistics\Repository\Interfaces\IndividualRepositoryInterface;
 use Fisharebest\Webtrees\Tree;
 use Illuminate\Database\Capsule\Manager as DB;
+use Illuminate\Database\Query\Builder;
 use Illuminate\Database\Query\JoinClause;
 
 /**
@@ -421,61 +421,57 @@ class IndividualRepository implements IndividualRepositoryInterface
     }
 
     /**
-     * Count the number of distinct given names, or count the number of
-     * occurrences of a specific name or names.
+     * Count the number of distinct given names (or the number of occurences of specific given names).
      *
-     * @param array ...$params
+     * @param string[] ...$params
      *
      * @return string
      */
     public function totalGivennames(...$params): string
     {
-        if ($params) {
-            $qs       = implode(',', array_fill(0, \count($params), '?'));
-            $params[] = $this->tree->id();
-            $total    = (int) Database::prepare(
-                "SELECT COUNT( n_givn) FROM `##name` WHERE n_givn IN ({$qs}) AND n_file=?"
-            )->execute(
-                $params
-            )->fetchOne();
+        $query = DB::table('name')
+            ->where('n_file', '=', $this->tree->id());
+
+        if (empty($params)) {
+            // Count number of distinct surnames
+            $query
+                ->whereNotIn('g_givn', ['', '@N.N.'])
+                ->groupBy('g_givn');
         } else {
-            $total = (int) Database::prepare(
-                "SELECT COUNT(DISTINCT n_givn) FROM `##name` WHERE n_givn IS NOT NULL AND n_file=?"
-            )->execute([
-                $this->tree->id(),
-            ])->fetchOne();
+            // Count number of occurences of specific surnames.
+            $query->whereIn('g_givn', $params);
         }
 
-        return I18N::number($total);
+        $count = $query->count();
+
+        return I18N::number($count);
     }
 
     /**
-     * Count the surnames.
+     * Count the number of distinct surnames (or the number of occurences of specific surnmaes).
      *
-     * @param array ...$params
+     * @param string[] ...$params
      *
      * @return string
      */
     public function totalSurnames(...$params): string
     {
-        if ($params) {
-            $opt      = 'IN (' . implode(',', array_fill(0, \count($params), '?')) . ')';
-            $distinct = '';
+        $query = DB::table('name')
+            ->where('n_file', '=', $this->tree->id());
+
+        if (empty($params)) {
+            // Count number of distinct surnames
+            $query
+                ->whereNotIn('n_surn', ['', '@N.N.'])
+                ->groupBy('n_surn');
         } else {
-            $opt      = "IS NOT NULL";
-            $distinct = 'DISTINCT';
+            // Count number of occurences of specific surnames.
+            $query->whereIn('n_surn', $params);
         }
-        $params[] = $this->tree->id();
 
-        $total = (int) Database::prepare(
-            "SELECT COUNT({$distinct} n_surn COLLATE '" . I18N::collation() . "')" .
-            " FROM `##name`" .
-            " WHERE n_surn COLLATE '" . I18N::collation() . "' {$opt} AND n_file=?"
-        )->execute(
-            $params
-        )->fetchOne();
-
-        return I18N::number($total);
+        $count = $query->count();
+        
+        return I18N::number($count);
     }
 
     /**
@@ -1505,14 +1501,14 @@ class IndividualRepository implements IndividualRepositoryInterface
      */
     private function totalLivingQuery(): int
     {
-        return DB::table('individuals')
-            ->where('i_file', '=', $this->tree->id())
-            ->where(
-                'i_gedcom',
-                'not regexp',
-                "\n1 (" . implode('|', Gedcom::DEATH_EVENTS) . ')'
-            )
-            ->count();
+        $query = DB::table('individuals')
+            ->where('i_file', '=', $this->tree->id());
+
+        foreach (Gedcom::DEATH_EVENTS as $death_event) {
+            $query->where('i_gedcom', 'NOT LIKE', '%\n1 ' . $death_event);
+        }
+
+        return $query->count();
     }
 
     /**
@@ -1524,11 +1520,11 @@ class IndividualRepository implements IndividualRepositoryInterface
     {
         return DB::table('individuals')
             ->where('i_file', '=', $this->tree->id())
-            ->where(
-                'i_gedcom',
-                'regexp',
-                "\n1 (" . implode('|', Gedcom::DEATH_EVENTS) . ')'
-            )
+            ->where(function (Builder $query): void {
+                foreach (Gedcom::DEATH_EVENTS as $death_event) {
+                    $query->orWhere('i_gedcom', 'NOT LIKE', '%\n1 ' . $death_event);
+                }
+            })
             ->count();
     }