aboutsummaryrefslogtreecommitdiff
path: root/app/Models/StatsDAO.php
diff options
context:
space:
mode:
Diffstat (limited to 'app/Models/StatsDAO.php')
-rw-r--r--app/Models/StatsDAO.php281
1 files changed, 240 insertions, 41 deletions
diff --git a/app/Models/StatsDAO.php b/app/Models/StatsDAO.php
index 60cec7847..40505ab3e 100644
--- a/app/Models/StatsDAO.php
+++ b/app/Models/StatsDAO.php
@@ -2,6 +2,8 @@
class FreshRSS_StatsDAO extends Minz_ModelPdo {
+ const ENTRY_COUNT_PERIOD = 30;
+
/**
* Calculates entry repartition for all feeds and for main stream.
* The repartition includes:
@@ -9,7 +11,7 @@ class FreshRSS_StatsDAO extends Minz_ModelPdo {
* - read entries
* - unread entries
* - favorite entries
- *
+ *
* @return type
*/
public function calculateEntryRepartition() {
@@ -50,68 +52,190 @@ SQL;
/**
* Calculates entry count per day on a 30 days period.
* Returns the result as a JSON string.
- *
+ *
* @return string
*/
public function calculateEntryCount() {
- $count = array();
+ $count = $this->initEntryCountArray();
+ $period = self::ENTRY_COUNT_PERIOD;
- // Generates a list of 30 last day to be sure we always have 30 days.
- // If we do not do that kind of thing, we'll end up with holes in the
- // days if the user do not have a lot of feeds.
+ // Get stats per day for the last 30 days
$sql = <<<SQL
-SELECT - (tens.val + units.val + 1) AS day
-FROM (
- SELECT 0 AS val
- UNION ALL SELECT 1
- UNION ALL SELECT 2
- UNION ALL SELECT 3
- UNION ALL SELECT 4
- UNION ALL SELECT 5
- UNION ALL SELECT 6
- UNION ALL SELECT 7
- UNION ALL SELECT 8
- UNION ALL SELECT 9
-) AS units
-CROSS JOIN (
- SELECT 0 AS val
- UNION ALL SELECT 10
- UNION ALL SELECT 20
-) AS tens
+SELECT DATEDIFF(FROM_UNIXTIME(e.date), NOW()) AS day,
+COUNT(1) AS count
+FROM {$this->prefix}entry AS e
+WHERE FROM_UNIXTIME(e.date, '%Y%m%d') BETWEEN DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -{$period} DAY), '%Y%m%d') AND DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -1 DAY), '%Y%m%d')
+GROUP BY day
ORDER BY day ASC
SQL;
$stm = $this->bd->prepare($sql);
$stm->execute();
$res = $stm->fetchAll(PDO::FETCH_ASSOC);
+
foreach ($res as $value) {
- $count[$value['day']] = 0;
+ $count[$value['day']] = (int) $value['count'];
}
- // Get stats per day for the last 30 days and applies the result on
- // the array created with the last query.
+ return $this->convertToSerie($count);
+ }
+
+ /**
+ * Initialize an array for the entry count.
+ *
+ * @return array
+ */
+ protected function initEntryCountArray() {
+ return $this->initStatsArray(-self::ENTRY_COUNT_PERIOD, -1);
+ }
+
+ /**
+ * Calculates the number of article per hour of the day per feed
+ *
+ * @param integer $feed id
+ * @return string
+ */
+ public function calculateEntryRepartitionPerFeedPerHour($feed = null) {
+ return $this->calculateEntryRepartitionPerFeedPerPeriod('%H', $feed);
+ }
+
+ /**
+ * Calculates the number of article per day of week per feed
+ *
+ * @param integer $feed id
+ * @return string
+ */
+ public function calculateEntryRepartitionPerFeedPerDayOfWeek($feed = null) {
+ return $this->calculateEntryRepartitionPerFeedPerPeriod('%w', $feed);
+ }
+
+ /**
+ * Calculates the number of article per month per feed
+ *
+ * @param integer $feed
+ * @return string
+ */
+ public function calculateEntryRepartitionPerFeedPerMonth($feed = null) {
+ return $this->calculateEntryRepartitionPerFeedPerPeriod('%m', $feed);
+ }
+
+ /**
+ * Calculates the number of article per period per feed
+ *
+ * @param string $period format string to use for grouping
+ * @param integer $feed id
+ * @return string
+ */
+ protected function calculateEntryRepartitionPerFeedPerPeriod($period, $feed = null) {
+ if ($feed) {
+ $restrict = "WHERE e.id_feed = {$feed}";
+ } else {
+ $restrict = '';
+ }
$sql = <<<SQL
-SELECT DATEDIFF(FROM_UNIXTIME(e.date), NOW()) AS day,
-COUNT(1) AS count
+SELECT DATE_FORMAT(FROM_UNIXTIME(e.date), '{$period}') AS period
+, COUNT(1) AS count
FROM {$this->prefix}entry AS e
-WHERE FROM_UNIXTIME(e.date, '%Y%m%d') BETWEEN DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -30 DAY), '%Y%m%d') AND DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -1 DAY), '%Y%m%d')
-GROUP BY day
-ORDER BY day ASC
+{$restrict}
+GROUP BY period
+ORDER BY period ASC
SQL;
+
$stm = $this->bd->prepare($sql);
$stm->execute();
- $res = $stm->fetchAll(PDO::FETCH_ASSOC);
+ $res = $stm->fetchAll(PDO::FETCH_NAMED);
foreach ($res as $value) {
- $count[$value['day']] = (int) $value['count'];
+ $repartition[(int) $value['period']] = (int) $value['count'];
}
- return $this->convertToSerie($count);
+ return $this->convertToSerie($repartition);
+ }
+
+ /**
+ * Calculates the average number of article per hour per feed
+ *
+ * @param integer $feed id
+ * @return integer
+ */
+ public function calculateEntryAveragePerFeedPerHour($feed = null) {
+ return $this->calculateEntryAveragePerFeedPerPeriod(1/24, $feed);
+ }
+
+ /**
+ * Calculates the average number of article per day of week per feed
+ *
+ * @param integer $feed id
+ * @return integer
+ */
+ public function calculateEntryAveragePerFeedPerDayOfWeek($feed = null) {
+ return $this->calculateEntryAveragePerFeedPerPeriod(7, $feed);
+ }
+
+ /**
+ * Calculates the average number of article per month per feed
+ *
+ * @param integer $feed id
+ * @return integer
+ */
+ public function calculateEntryAveragePerFeedPerMonth($feed = null) {
+ 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
+ * @param integer $feed id
+ * @return integer
+ */
+ protected function calculateEntryAveragePerFeedPerPeriod($period, $feed = null) {
+ if ($feed) {
+ $restrict = "WHERE e.id_feed = {$feed}";
+ } else {
+ $restrict = '';
+ }
+ $sql = <<<SQL
+SELECT COUNT(1) AS count
+, MIN(date) AS date_min
+, MAX(date) AS date_max
+FROM {$this->prefix}entry AS e
+{$restrict}
+SQL;
+ $stm = $this->bd->prepare($sql);
+ $stm->execute();
+ $res = $stm->fetch(PDO::FETCH_NAMED);
+ $date_min = new \DateTime();
+ $date_min->setTimestamp($res['date_min']);
+ $date_max = new \DateTime();
+ $date_max->setTimestamp($res['date_max']);
+ $interval = $date_max->diff($date_min, true);
+ $interval_in_days = $interval->format('%a');
+ if ($interval_in_days <= 0) {
+ // Surely only one article.
+ // We will return count / (period/period) == count.
+ $interval_in_days = $period;
+ }
+
+ return round($res['count'] / ($interval_in_days / $period), 2);
+ }
+
+ /**
+ * Initialize an array for statistics depending on a range
+ *
+ * @param integer $min
+ * @param integer $max
+ * @return array
+ */
+ protected function initStatsArray($min, $max) {
+ return array_map(function () {
+ return 0;
+ }, array_flip(range($min, $max)));
}
/**
* Calculates feed count per category.
* Returns the result as a JSON string.
- *
+ *
* @return string
*/
public function calculateFeedByCategory() {
@@ -134,7 +258,7 @@ SQL;
/**
* Calculates entry count per category.
* Returns the result as a JSON string.
- *
+ *
* @return string
*/
public function calculateEntryByCategory() {
@@ -158,7 +282,7 @@ SQL;
/**
* Calculates the 10 top feeds based on their number of entries
- *
+ *
* @return array
*/
public function calculateTopFeed() {
@@ -172,7 +296,7 @@ FROM {$this->prefix}category AS c,
{$this->prefix}entry AS e
WHERE c.id = f.category
AND f.id = e.id_feed
-GROUP BY id
+GROUP BY f.id
ORDER BY count DESC
LIMIT 10
SQL;
@@ -181,7 +305,29 @@ SQL;
return $stm->fetchAll(PDO::FETCH_ASSOC);
}
- private function convertToSerie($data) {
+ /**
+ * Calculates the last publication date for each feed
+ *
+ * @return array
+ */
+ public function calculateFeedLastDate() {
+ $sql = <<<SQL
+SELECT MAX(f.id) as id
+, MAX(f.name) AS name
+, MAX(date) AS last_date
+, COUNT(*) AS nb_articles
+FROM {$this->prefix}feed AS f,
+{$this->prefix}entry AS e
+WHERE f.id = e.id_feed
+GROUP BY f.id
+ORDER BY name
+SQL;
+ $stm = $this->bd->prepare($sql);
+ $stm->execute();
+ return $stm->fetchAll(PDO::FETCH_ASSOC);
+ }
+
+ protected function convertToSerie($data) {
$serie = array();
foreach ($data as $key => $value) {
@@ -191,7 +337,7 @@ SQL;
return json_encode($serie);
}
- private function convertToPieSerie($data) {
+ protected function convertToPieSerie($data) {
$serie = array();
foreach ($data as $value) {
@@ -202,4 +348,57 @@ SQL;
return json_encode($serie);
}
+ /**
+ * Gets days ready for graphs
+ *
+ * @return string
+ */
+ public function getDays() {
+ return $this->convertToTranslatedJson(array(
+ 'sun',
+ 'mon',
+ 'tue',
+ 'wed',
+ 'thu',
+ 'fri',
+ 'sat',
+ ));
+ }
+
+ /**
+ * Gets months ready for graphs
+ *
+ * @return string
+ */
+ public function getMonths() {
+ return $this->convertToTranslatedJson(array(
+ 'jan',
+ 'feb',
+ 'mar',
+ 'apr',
+ 'may',
+ 'jun',
+ 'jul',
+ 'aug',
+ 'sep',
+ 'oct',
+ 'nov',
+ 'dec',
+ ));
+ }
+
+ /**
+ * Translates array content and encode it as JSON
+ *
+ * @param array $data
+ * @return string
+ */
+ private function convertToTranslatedJson($data = array()) {
+ $translated = array_map(function ($a) {
+ return Minz_Translate::t($a);
+ }, $data);
+
+ return json_encode($translated);
+ }
+
}
generated by cgit v1.2.3 (git 2.39.1) at 2026-02-10 09:26:08 +0000