From 21c3e782d4f965ef0deef365bbac91b2036694e4 Mon Sep 17 00:00:00 2001
From: Peter Zhang <peter@innocraft.com>
Date: Sat, 8 Jan 2022 04:35:17 +1300
Subject: update Performance Report Average Load Time query (#18526)

* Update Archiver.php

update mysql query that return right load time

* Update Archiver.php

remove specialist code

* Update Archiver.php

update archiver sum query

* Update test_ImportLogs__PagePerformance.get_month.xml

update tests xml

* try having condition

try having condition

* update some avg_page

update some avg_page

* Update test_TwoVisitors_twoWebsites_differentDays_schedrep_html_tables_only__ScheduledReports.generateReport_month.original.html

update html test

* update html and pdf tests

update html and pdf tests

* revert having to where

revert having to where

* Update plugins/PagePerformance/Archiver.php

Co-authored-by: Stefan Giehl <stefan@matomo.org>

* updates expected test files

Co-authored-by: Stefan Giehl <stefan@matomo.org>
---
 plugins/PagePerformance/Archiver.php                         | 12 ++++++++----
 .../expected/test_ImportLogs__PagePerformance.get_month.xml  |  2 +-
 2 files changed, 9 insertions(+), 5 deletions(-)

diff --git a/plugins/PagePerformance/Archiver.php b/plugins/PagePerformance/Archiver.php
index 5b12cac165..3138c47a08 100644
--- a/plugins/PagePerformance/Archiver.php
+++ b/plugins/PagePerformance/Archiver.php
@@ -38,9 +38,10 @@ class Archiver extends \Piwik\Plugin\Archiver
 
     public function aggregateDayReport()
     {
-        $selects = $totalColumns = $hitsColumns = [];
+        $selects = $totalColumns = $hitsColumns = $allColumns = [];
         $table  = 'log_link_visit_action';
 
+
         $performanceDimensions = [
             new TimeNetwork(),
             new TimeServer(),
@@ -54,16 +55,19 @@ class Archiver extends \Piwik\Plugin\Archiver
             $column = $dimension->getColumnName();
             $selects[] = "sum($table.$column) as {$column}_total";
             $selects[] = "sum(if($table.$column is null, 0, 1)) as {$column}_hits";
-            $totalColumns[] = "$table.$column";
+            $totalColumns[] = "IFNULL($table.$column,0)";
             $hitsColumns[] = "if($table.$column is null, 0, 1)";
+            $allColumns[]  = "$table.$column";
         }
 
         $selects[] = sprintf('SUM(%s) as page_load_total', implode(' + ', $totalColumns));
-        $selects[] = sprintf('(SUM(%s)/%s) as page_load_hits', implode(' + ', $hitsColumns), count($hitsColumns));
+        $selects[] = "count(idlink_va) as page_load_hits";
 
         $joinLogActionOnColumn = array('idaction_url');
+        $where = sprintf("COALESCE(%s) IS NOT NULL", implode(',', $allColumns));
 
-        $query = $this->getLogAggregator()->queryActionsByDimension([], '', $selects, false, null, $joinLogActionOnColumn);
+        $query = $this->getLogAggregator()->queryActionsByDimension([], $where, $selects, false, null,
+          $joinLogActionOnColumn, null, -1);
 
         $result = $query->fetchAll();
 
diff --git a/tests/PHPUnit/System/expected/test_ImportLogs__PagePerformance.get_month.xml b/tests/PHPUnit/System/expected/test_ImportLogs__PagePerformance.get_month.xml
index eb284626c9..91a1eb3ecd 100644
--- a/tests/PHPUnit/System/expected/test_ImportLogs__PagePerformance.get_month.xml
+++ b/tests/PHPUnit/System/expected/test_ImportLogs__PagePerformance.get_month.xml
@@ -6,5 +6,5 @@
 	<avg_time_dom_processing>0</avg_time_dom_processing>
 	<avg_time_dom_completion>0</avg_time_dom_completion>
 	<avg_time_on_load>0</avg_time_on_load>
-	<avg_page_load_time>0</avg_page_load_time>
+	<avg_page_load_time>0.19</avg_page_load_time>
 </result>
\ No newline at end of file
-- 
cgit v1.2.3