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)"; } /** * Calculates entry repartition for all feeds and for main stream. * * @return array{'main_stream':array{'total':int,'count_unreads':int,'count_reads':int,'count_favorites':int}|false,'all_feeds':array{'total':int,'count_unreads':int,'count_reads':int,'count_favorites':int}|false} */ public function calculateEntryRepartition(): array { return [ 'main_stream' => $this->calculateEntryRepartitionPerFeed(null, true), 'all_feeds' => $this->calculateEntryRepartitionPerFeed(null, false), ]; } /** * Calculates entry repartition for the selection. * The repartition includes: * - total entries * - read entries * - unread entries * - favorite entries * * @return array{total:int,count_unreads:int,count_reads:int,count_favorites:int}|false */ public function calculateEntryRepartitionPerFeed(?int $feed = null, bool $only_main = false): array|false { $filter = ''; if ($only_main) { $filter .= 'AND f.priority = 10'; } if ($feed !== null) { $filter .= "AND e.id_feed = {$feed}"; } $sql = <<fetchAssoc($sql); if (is_array($res) && !empty($res[0]) && is_array($res[0])) { $dao = array_map('intval', $res[0]); /** @var array{total:int,count_unreads:int,count_reads:int,count_favorites:int} $dao */ return $dao; } return false; } /** * Calculates entry count per day on a 30 days period. * @return array */ public function calculateEntryCount(): array { $count = $this->initEntryCountArray(); $midnight = mktime(0, 0, 0) ?: 0; $oldest = $midnight - (self::ENTRY_COUNT_PERIOD * 86400); // Get stats per day for the last 30 days $sqlDay = $this->sqlFloor("(date - $midnight) / 86400"); $sql = <<= {$oldest} AND date < {$midnight} GROUP BY day ORDER BY day ASC SQL; $res = $this->fetchAssoc($sql); if (!is_array($res)) { return []; } /** @var list $res */ foreach ($res as $value) { $count[(int)($value['day'])] = (int)($value['count']); } return $count; } /** * Initialize an array for the entry count. * @return array */ protected function initEntryCountArray(): array { return $this->initStatsArray(-self::ENTRY_COUNT_PERIOD, -1); } /** * Calculates the number of article per hour of the day per feed * @return array */ public function calculateEntryRepartitionPerFeedPerHour(?int $feed = null): array { return $this->calculateEntryRepartitionPerFeedPerPeriod('%H', $feed); } /** * Calculates the number of article per day of week per feed * @return array */ public function calculateEntryRepartitionPerFeedPerDayOfWeek(?int $feed = null): array { return $this->calculateEntryRepartitionPerFeedPerPeriod('%w', $feed); } /** * Calculates the number of article per month per feed * @return array */ public function calculateEntryRepartitionPerFeedPerMonth(?int $feed = null): array { $monthRepartition = $this->calculateEntryRepartitionPerFeedPerPeriod('%m', $feed); // cut out the 0th month (Jan=1, Dec=12) \array_splice($monthRepartition, 0, 1); return $monthRepartition; } /** * Calculates the number of article per period per feed * @param string $period format string to use for grouping * @return array */ protected function calculateEntryRepartitionPerFeedPerPeriod(string $period, ?int $feed = null): array { $restrict = ''; if ($feed) { $restrict = "WHERE e.id_feed = {$feed}"; } $offset = $this->getTimezoneOffset(); $sql = <<fetchAssoc($sql); if ($res == false) { return []; } $periodMax = match ($period) { '%H' => 24, '%w' => 7, '%m' => 12, default => 30, }; $repartition = array_fill(0, $periodMax, 0); foreach ($res as $value) { $repartition[(int)$value['period']] = (int)$value['count']; } return $repartition; } /** * Calculates the average number of article per hour per feed */ public function calculateEntryAveragePerFeedPerHour(?int $feed = null): float { return $this->calculateEntryAveragePerFeedPerPeriod(1 / 24, $feed); } /** * Calculates the average number of article per day of week per feed */ public function calculateEntryAveragePerFeedPerDayOfWeek(?int $feed = null): float { return $this->calculateEntryAveragePerFeedPerPeriod(7, $feed); } /** * Calculates the average number of article per month per feed */ public function calculateEntryAveragePerFeedPerMonth(?int $feed = null): float { return $this->calculateEntryAveragePerFeedPerPeriod(30, $feed); } /** * Calculates the average number of article per feed * @param float $period number used to divide the number of day in the period */ protected function calculateEntryAveragePerFeedPerPeriod(float $period, ?int $feed = null): float { $restrict = ''; if ($feed) { $restrict = "WHERE e.id_feed = {$feed}"; } $sql = <<fetchAssoc($sql); if ($res == null || empty($res[0])) { return -1.0; } $date_min = new \DateTime(); $date_min->setTimestamp((int)($res[0]['date_min'])); $date_max = new \DateTime(); $date_max->setTimestamp((int)($res[0]['date_max'])); $interval = $date_max->diff($date_min, true); $interval_in_days = (float)($interval->format('%a')); if ($interval_in_days <= 0) { // Surely only one article. // We will return count / (period/period) == count. $interval_in_days = $period; } return (int)$res[0]['count'] / ($interval_in_days / $period); } /** * Initialize an array for statistics depending on a range * @return array */ protected function initStatsArray(int $min, int $max): array { return array_map(fn() => 0, array_flip(range($min, $max))); } /** * Calculates feed count per category. * @return list */ public function calculateFeedByCategory(): array { $sql = <<|null @res */ $res = $this->fetchAssoc($sql); return $res == null ? [] : $res; } /** * Calculates entry count per category. * @return list */ public function calculateEntryByCategory(): array { $sql = <<fetchAssoc($sql); /** @var list|null $res */ return $res == null ? [] : $res; } /** * Calculates the 10 top feeds based on their number of entries * @return list */ public function calculateTopFeed(): array { $sql = <<fetchAssoc($sql); /** @var list|null $res */ if (is_array($res)) { return $res; } return []; } /** * Calculates the last publication date for each feed * @return list */ public function calculateFeedLastDate(): array { $sql = <<fetchAssoc($sql); /** @var list|null $res */ if (is_array($res)) { return $res; } return []; } /** * Gets days ready for graphs * @return list */ public function getDays(): array { return $this->convertToTranslatedJson([ 'sun', 'mon', 'tue', 'wed', 'thu', 'fri', 'sat', ]); } /** * Gets months ready for graphs * @return list */ public function getMonths(): array { return $this->convertToTranslatedJson([ 'jan', 'feb', 'mar', 'apr', 'may_', 'jun', 'jul', 'aug', 'sep', 'oct', 'nov', 'dec', ]); } /** * Translates array content * @param list $data * @return list */ private function convertToTranslatedJson(array $data = []): array { $translated = array_map(static fn(string $a) => _t('gen.date.' . $a), $data); 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 */ public function getMaxUnreadDates(string $field, string $granularity, int $max = 100): array { $sql = <<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|null $res */ return is_array($res) ? $res : []; } }