Sortie de la version 0.70.7.0

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);
+	}
+
+}