From 5eba322cbd24191e05304df08c80af846977d99b Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre@alapetite.fr>
Date: Tue, 14 Oct 2025 11:05:17 +0200
Subject: New stats overview of dates with most unread articles (#8089)

New view with direct links to dates with most unread articles:

<img width="734" height="581" alt="image" src="https://github.com/user-attachments/assets/159a39b3-3a06-4ae9-9cc0-62ae36d9db9c" />
---
 app/Models/StatsDAO.php | 49 ++++++++++++++++++++++++++++++++++++++++++++++++-
 1 file changed, 48 insertions(+), 1 deletion(-)

(limited to 'app/Models/StatsDAO.php')

diff --git a/app/Models/StatsDAO.php b/app/Models/StatsDAO.php
index 771a2c7ee..c10567951 100644
--- a/app/Models/StatsDAO.php
+++ b/app/Models/StatsDAO.php
@@ -5,6 +5,30 @@ class FreshRSS_StatsDAO extends Minz_ModelPdo {
 
 	public const ENTRY_COUNT_PERIOD = 30;
 
+	/** Get the number of seconds to add to UTC to get the user's local time */
+	protected function getTimezoneOffset(): int {
+		$timezone = new DateTimeZone(date_default_timezone_get());
+		return $timezone->getOffset(new DateTime('now', new DateTimeZone('UTC')));
+	}
+
+	/**
+	 * @param string $field to use for the date
+	 * @param int $precision to apply to the timestamp (1 for seconds, 1000 for milliseconds, 1000000 for microseconds)
+	 * @param 'day'|'month'|'year' $granularity of the date intervals
+	 */
+	protected function sqlDateToIsoGranularity(string $field, int $precision, string $granularity): string {
+		if (!preg_match('/^[a-zA-Z0-9_]+$/', $field)) {
+			throw new InvalidArgumentException('Invalid date field!');
+		}
+		$offset = $this->getTimezoneOffset();
+		return match ($granularity) {
+			'day' => "FROM_UNIXTIME(($field / $precision) + $offset, '%Y-%m-%d')",
+			'month' => "FROM_UNIXTIME(($field / $precision) + $offset, '%Y-%m')",
+			'year' => "FROM_UNIXTIME(($field / $precision) + $offset, '%Y')",
+			default => throw new InvalidArgumentException('Invalid date granularity!'),
+		};
+	}
+
 	protected function sqlFloor(string $s): string {
 		return "FLOOR($s)";
 	}
@@ -132,8 +156,9 @@ SQL;
 		if ($feed) {
 			$restrict = "WHERE e.id_feed = {$feed}";
 		}
+		$offset = $this->getTimezoneOffset();
 		$sql = <<<SQL
-SELECT DATE_FORMAT(FROM_UNIXTIME(e.date), '{$period}') AS period
+SELECT DATE_FORMAT(FROM_UNIXTIME(e.date + {$offset}), '{$period}') AS period
 , COUNT(1) AS count
 FROM `_entry` AS e
 {$restrict}
@@ -356,4 +381,26 @@ SQL;
 
 		return $translated;
 	}
+
+	/**
+	 * Gets the date intervals with the largest number of unread articles.
+	 * @param 'id'|'date' $field to use for the date
+	 * @param 'day'|'month'|'year' $granularity of the date intervals
+	 * @return list<array{'granularity':string,'unread_count':int}>
+	 */
+	public function getMaxUnreadDates(string $field, string $granularity, int $max = 100): array {
+		$sql = <<<SQL
+SELECT
+	{$this->sqlDateToIsoGranularity($field, precision: $field === 'id' ? 1000000 : 1, granularity: $granularity)} AS granularity,
+	COUNT(*) AS unread_count
+FROM `_entry`
+WHERE is_read = 0
+GROUP BY granularity
+ORDER BY unread_count DESC, granularity DESC
+LIMIT $max;
+SQL;
+		$res = $this->fetchAssoc($sql);
+		/** @var list<array{granularity:string,unread_count:int}>|null $res */
+		return is_array($res) ? $res : [];
+	}
 }
-- 
cgit v1.2.3