Custom reports: show server hours in site's timezone (#16565)

4 files changed, 67 insertions, 4 deletions

diff --git a/plugins/CoreHome/Columns/ServerTime.php b/plugins/CoreHome/Columns/ServerTime.php
index a7ddd4fb20..d2005f11fa 100644
--- a/plugins/CoreHome/Columns/ServerTime.php
+++ b/plugins/CoreHome/Columns/ServerTime.php
@@ -35,7 +35,8 @@ class ServerTime extends ActionDimension
 
     public function formatValue($value, $idSite, Formatter $formatter)
     {
-        return \Piwik\Plugins\VisitTime\getTimeLabel($value);
+        $hourInTz = VisitLastActionTime::convertHourToHourInSiteTimezone($value, $idSite);
+        return \Piwik\Plugins\VisitTime\getTimeLabel($hourInTz);
     }
 
     public function install()
diff --git a/plugins/CoreHome/Columns/VisitFirstActionTime.php b/plugins/CoreHome/Columns/VisitFirstActionTime.php
index 878d452399..5f76c50763 100644
--- a/plugins/CoreHome/Columns/VisitFirstActionTime.php
+++ b/plugins/CoreHome/Columns/VisitFirstActionTime.php
@@ -37,7 +37,8 @@ class VisitFirstActionTime extends VisitDimension
 
     public function formatValue($value, $idSite, Formatter $formatter)
     {
-        return \Piwik\Plugins\VisitTime\getTimeLabel($value);
+        $hourInTz = VisitLastActionTime::convertHourToHourInSiteTimezone($value, $idSite);
+        return \Piwik\Plugins\VisitTime\getTimeLabel($hourInTz);
     }
 
     /**
diff --git a/plugins/CoreHome/Columns/VisitLastActionTime.php b/plugins/CoreHome/Columns/VisitLastActionTime.php
index 9b2bbe56d6..2ec021f68f 100644
--- a/plugins/CoreHome/Columns/VisitLastActionTime.php
+++ b/plugins/CoreHome/Columns/VisitLastActionTime.php
@@ -8,13 +8,15 @@
  */
 namespace Piwik\Plugins\CoreHome\Columns;
 
+use Piwik\Common;
 use Piwik\Date;
+use Piwik\Period;
 use Piwik\Plugin\Dimension\VisitDimension;
+use Piwik\Site;
 use Piwik\Tracker\Action;
 use Piwik\Tracker\Request;
 use Piwik\Tracker\Visitor;
 use Piwik\Metrics\Formatter;
-use Piwik\Tracker\VisitorRecognizer;
 
 require_once PIWIK_INCLUDE_PATH . '/plugins/VisitTime/functions.php';
 
@@ -35,9 +37,40 @@ class VisitLastActionTime extends VisitDimension
     protected $segmentName = 'visitServerHour';
     protected $acceptValues = '0, 1, 2, 3, ..., 20, 21, 22, 23';
 
+    /**
+     * Converts the hour to the hour depending on the configured site's timezone.
+     * Only works correct if a date/period is present in the request. Otherwise the result may vary depending on the
+     * day of the year as it can't know about daylight savings for example. Also if the currently selected date range
+     * includes multiple days with daylight savings and some without, then it might not be 100% correct as we are
+     * only looking at the start of the time.
+     * @param int $hour
+     * @param int $idSite
+     * @return int
+     * @throws \Exception
+     */
+    public static function convertHourToHourInSiteTimezone($hour, $idSite)
+    {
+        $date = Date::now();
+        $dateString = Common::getRequestVar('date', '', 'string');
+        $periodString = Common::getRequestVar('period', '', 'string');
+        if (!empty($dateString) && !empty($periodString)) {
+            try {
+                $date = Period\Factory::build($periodString, $dateString)->getDateStart();
+            } catch (\Exception $e) {
+                // ignore any error eg if values are wrong...
+            }
+        }
+        $timezone = Site::getTimezoneFor($idSite);
+        $datetime = $date->toString() . ' ' . $hour . ':00:00';
+        $hourInTz = (int)Date::factory($datetime, $timezone)->toString('H');
+        return $hourInTz;
+    }
+
     public function formatValue($value, $idSite, Formatter $formatter)
     {
-        return \Piwik\Plugins\VisitTime\getTimeLabel($value);
+        $hourInTz = self::convertHourToHourInSiteTimezone($value, $idSite);
+
+        return \Piwik\Plugins\VisitTime\getTimeLabel($hourInTz);
     }
 
     // we do not install or define column definition here as we need to create this column when installing as there is
diff --git a/plugins/CoreHome/tests/Integration/Column/VisitLastActionTimeTest.php b/plugins/CoreHome/tests/Integration/Column/VisitLastActionTimeTest.php
index 6940c9074a..acbf850dde 100644
--- a/plugins/CoreHome/tests/Integration/Column/VisitLastActionTimeTest.php
+++ b/plugins/CoreHome/tests/Integration/Column/VisitLastActionTimeTest.php
@@ -58,6 +58,34 @@ class VisitLastActionTimeTest extends IntegrationTestCase
         return new Request($request);
     }
 
+    public function test_convertHourToHourInSiteTimezone_UTC()
+    {
+        $idSite = Fixture::createWebsite('2020-01-02 03:04:05');
+        $hourConverted = VisitLastActionTime::convertHourToHourInSiteTimezone(5, $idSite);
+        $this->assertEquals(5, $hourConverted);
+    }
+
+    public function test_convertHourToHourInSiteTimezone_WithTimezone()
+    {
+        $idSite = Fixture::createWebsite('2020-01-02 03:04:05', $ecommerce = 1, 'Site', $siteUrl = false,
+            $siteSearch = 1, $searchKeywordParameters = null,
+            $searchCategoryParameters = null, $timezone = 'Asia/Jakarta');
+        $hourConverted = VisitLastActionTime::convertHourToHourInSiteTimezone(5, $idSite);
+        $this->assertEquals(12, $hourConverted);
+    }
+
+    public function test_convertHourToHourInSiteTimezone_WithTimezoneAndCustomDate()
+    {
+        $_GET['period'] = 'day';
+        $_GET['date'] = '2020-01-02 03:04:05';
+        $idSite = Fixture::createWebsite('2020-01-02 03:04:05', $ecommerce = 1, 'Site', $siteUrl = false,
+            $siteSearch = 1, $searchKeywordParameters = null,
+            $searchCategoryParameters = null, $timezone = 'Asia/Jakarta');
+        $hourConverted = VisitLastActionTime::convertHourToHourInSiteTimezone(5, $idSite);
+        unset($_GET['period'], $_GET['date']);
+        $this->assertEquals(12, $hourConverted);
+    }
+
     private function getVisitor(VisitProperties $previousProperties = null)
     {
         $visit = new VisitProperties();