diff options
Diffstat (limited to 'app/Models/StatsDAO.php')
| -rw-r--r-- | app/Models/StatsDAO.php | 205 |
1 files changed, 205 insertions, 0 deletions
diff --git a/app/Models/StatsDAO.php b/app/Models/StatsDAO.php new file mode 100644 index 000000000..60cec7847 --- /dev/null +++ b/app/Models/StatsDAO.php @@ -0,0 +1,205 @@ +<?php + +class FreshRSS_StatsDAO extends Minz_ModelPdo { + + /** + * Calculates entry repartition for all feeds and for main stream. + * The repartition includes: + * - total entries + * - read entries + * - unread entries + * - favorite entries + * + * @return type + */ + public function calculateEntryRepartition() { + $repartition = array(); + + // Generates the repartition for the main stream of entry + $sql = <<<SQL +SELECT COUNT(1) AS `total`, +COUNT(1) - SUM(e.is_read) AS `unread`, +SUM(e.is_read) AS `read`, +SUM(e.is_favorite) AS `favorite` +FROM {$this->prefix}entry AS e +, {$this->prefix}feed AS f +WHERE e.id_feed = f.id +AND f.priority = 10 +SQL; + $stm = $this->bd->prepare($sql); + $stm->execute(); + $res = $stm->fetchAll(PDO::FETCH_ASSOC); + $repartition['main_stream'] = $res[0]; + + // Generates the repartition for all entries + $sql = <<<SQL +SELECT COUNT(1) AS `total`, +COUNT(1) - SUM(e.is_read) AS `unread`, +SUM(e.is_read) AS `read`, +SUM(e.is_favorite) AS `favorite` +FROM {$this->prefix}entry AS e +SQL; + $stm = $this->bd->prepare($sql); + $stm->execute(); + $res = $stm->fetchAll(PDO::FETCH_ASSOC); + $repartition['all_feeds'] = $res[0]; + + return $repartition; + } + + /** + * Calculates entry count per day on a 30 days period. + * Returns the result as a JSON string. + * + * @return string + */ + public function calculateEntryCount() { + $count = array(); + + // 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. + $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 +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; + } + + // Get stats per day for the last 30 days and applies the result on + // the array created with the last query. + $sql = <<<SQL +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 -30 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']] = (int) $value['count']; + } + + return $this->convertToSerie($count); + } + + /** + * Calculates feed count per category. + * Returns the result as a JSON string. + * + * @return string + */ + public function calculateFeedByCategory() { + $sql = <<<SQL +SELECT c.name AS label +, COUNT(f.id) AS data +FROM {$this->prefix}category AS c, +{$this->prefix}feed AS f +WHERE c.id = f.category +GROUP BY label +ORDER BY data DESC +SQL; + $stm = $this->bd->prepare($sql); + $stm->execute(); + $res = $stm->fetchAll(PDO::FETCH_ASSOC); + + return $this->convertToPieSerie($res); + } + + /** + * Calculates entry count per category. + * Returns the result as a JSON string. + * + * @return string + */ + public function calculateEntryByCategory() { + $sql = <<<SQL +SELECT c.name AS label +, COUNT(e.id) AS data +FROM {$this->prefix}category AS c, +{$this->prefix}feed AS f, +{$this->prefix}entry AS e +WHERE c.id = f.category +AND f.id = e.id_feed +GROUP BY label +ORDER BY data DESC +SQL; + $stm = $this->bd->prepare($sql); + $stm->execute(); + $res = $stm->fetchAll(PDO::FETCH_ASSOC); + + return $this->convertToPieSerie($res); + } + + /** + * Calculates the 10 top feeds based on their number of entries + * + * @return array + */ + public function calculateTopFeed() { + $sql = <<<SQL +SELECT f.id AS id +, MAX(f.name) AS name +, MAX(c.name) AS category +, COUNT(e.id) AS count +FROM {$this->prefix}category AS c, +{$this->prefix}feed AS f, +{$this->prefix}entry AS e +WHERE c.id = f.category +AND f.id = e.id_feed +GROUP BY id +ORDER BY count DESC +LIMIT 10 +SQL; + $stm = $this->bd->prepare($sql); + $stm->execute(); + return $stm->fetchAll(PDO::FETCH_ASSOC); + } + + private function convertToSerie($data) { + $serie = array(); + + foreach ($data as $key => $value) { + $serie[] = array($key, $value); + } + + return json_encode($serie); + } + + private function convertToPieSerie($data) { + $serie = array(); + + foreach ($data as $value) { + $value['data'] = array(array(0, (int) $value['data'])); + $serie[] = $value; + } + + return json_encode($serie); + } + +} |