Merge remote-tracking branch 'FreshRSS/master' into dev

1 files changed, 71 insertions, 104 deletions

diff --git a/app/Models/StatsDAO.php b/app/Models/StatsDAO.php
index 283d5dcb1..2ce4f2944 100644
--- a/app/Models/StatsDAO.php
+++ b/app/Models/StatsDAO.php
@@ -4,67 +4,77 @@ class FreshRSS_StatsDAO extends Minz_ModelPdo {
 
 	const ENTRY_COUNT_PERIOD = 30;
 
+	protected function sqlFloor($s) {
+		return "FLOOR($s)";
+	}
+
 	/**
 	 * Calculates entry repartition for all feeds and for main stream.
+	 *
+	 * @return array
+	 */
+	public function calculateEntryRepartition() {
+		return array(
+		    '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 type
+	 * @param null|integer $feed feed id
+	 * @param boolean $only_main
+	 * @return array
 	 */
-	public function calculateEntryRepartition() {
-		$repartition = array();
-
-		// Generates the repartition for the main stream of entry
+	public function calculateEntryRepartitionPerFeed($feed = null, $only_main = false) {
+		$filter = '';
+		if ($only_main) {
+			$filter .= 'AND f.priority = 10';
+		}
+		if (!is_null($feed)) {
+			$filter .= "AND e.id_feed = {$feed}";
+		}
 		$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
+SELECT COUNT(1) AS total,
+COUNT(1) - SUM(e.is_read) AS count_unreads,
+SUM(e.is_read) AS count_reads,
+SUM(e.is_favorite) AS count_favorites
+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
+{$filter}
 SQL;
 		$stm = $this->bd->prepare($sql);
 		$stm->execute();
 		$res = $stm->fetchAll(PDO::FETCH_ASSOC);
-		$repartition['all_feeds'] = $res[0];
 
-		return $repartition;
+		return $res[0];
 	}
 
 	/**
 	 * Calculates entry count per day on a 30 days period.
-	 * Returns the result as a JSON string.
+	 * Returns the result as a JSON object.
 	 *
-	 * @return string
+	 * @return JSON object
 	 */
 	public function calculateEntryCount() {
 		$count = $this->initEntryCountArray();
-		$period = self::ENTRY_COUNT_PERIOD;
+		$midnight = mktime(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 = <<<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 -{$period} DAY), '%Y%m%d') AND DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -1 DAY), '%Y%m%d')
+SELECT {$sqlDay} AS day,
+COUNT(*) as count
+FROM `{$this->prefix}entry`
+WHERE date >= {$oldest} AND date < {$midnight}
 GROUP BY day
 ORDER BY day ASC
 SQL;
@@ -76,28 +86,7 @@ SQL;
 			$count[$value['day']] = (int) $value['count'];
 		}
 
-		return $this->convertToSerie($count);
-	}
-
-	/**
-	 * Calculates entry average per day on a 30 days period.
-	 *
-	 * @return integer
-	 */
-	public function calculateEntryAverage() {
-		$period = self::ENTRY_COUNT_PERIOD;
-
-		// Get stats per day for the last 30 days
-		$sql = <<<SQL
-SELECT COUNT(1) / {$period} AS average
-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')
-SQL;
-		$stm = $this->bd->prepare($sql);
-		$stm->execute();
-		$res = $stm->fetch(PDO::FETCH_NAMED);
-
-		return round($res['average'], 2);
+		return $count;
 	}
 
 	/**
@@ -147,15 +136,14 @@ SQL;
 	 * @return string
 	 */
 	protected function calculateEntryRepartitionPerFeedPerPeriod($period, $feed = null) {
+		$restrict = '';
 		if ($feed) {
 			$restrict = "WHERE e.id_feed = {$feed}";
-		} else {
-			$restrict = '';
 		}
 		$sql = <<<SQL
 SELECT DATE_FORMAT(FROM_UNIXTIME(e.date), '{$period}') AS period
 , COUNT(1) AS count
-FROM {$this->prefix}entry AS e
+FROM `{$this->prefix}entry` AS e
 {$restrict}
 GROUP BY period
 ORDER BY period ASC
@@ -165,11 +153,12 @@ SQL;
 		$stm->execute();
 		$res = $stm->fetchAll(PDO::FETCH_NAMED);
 
+		$repartition = array();
 		foreach ($res as $value) {
 			$repartition[(int) $value['period']] = (int) $value['count'];
 		}
 
-		return $this->convertToSerie($repartition);
+		return $repartition;
 	}
 
 	/**
@@ -179,7 +168,7 @@ SQL;
 	 * @return integer
 	 */
 	public function calculateEntryAveragePerFeedPerHour($feed = null) {
-		return $this->calculateEntryAveragePerFeedPerPeriod(1/24, $feed);
+		return $this->calculateEntryAveragePerFeedPerPeriod(1 / 24, $feed);
 	}
 
 	/**
@@ -210,16 +199,15 @@ SQL;
 	 * @return integer
 	 */
 	protected function calculateEntryAveragePerFeedPerPeriod($period, $feed = null) {
+		$restrict = '';
 		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
+FROM `{$this->prefix}entry` AS e
 {$restrict}
 SQL;
 		$stm = $this->bd->prepare($sql);
@@ -255,16 +243,16 @@ SQL;
 
 	/**
 	 * Calculates feed count per category.
-	 * Returns the result as a JSON string.
+	 * Returns the result as a JSON object.
 	 *
-	 * @return string
+	 * @return JSON object
 	 */
 	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
+FROM `{$this->prefix}category` AS c,
+`{$this->prefix}feed` AS f
 WHERE c.id = f.category
 GROUP BY label
 ORDER BY data DESC
@@ -273,22 +261,22 @@ SQL;
 		$stm->execute();
 		$res = $stm->fetchAll(PDO::FETCH_ASSOC);
 
-		return $this->convertToPieSerie($res);
+		return $res;
 	}
 
 	/**
 	 * Calculates entry count per category.
 	 * Returns the result as a JSON string.
 	 *
-	 * @return string
+	 * @return JSON object
 	 */
 	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
+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
@@ -298,7 +286,7 @@ SQL;
 		$stm->execute();
 		$res = $stm->fetchAll(PDO::FETCH_ASSOC);
 
-		return $this->convertToPieSerie($res);
+		return $res;
 	}
 
 	/**
@@ -312,9 +300,9 @@ 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
+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 f.id
@@ -337,8 +325,8 @@ 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
+FROM `{$this->prefix}feed` AS f,
+`{$this->prefix}entry` AS e
 WHERE f.id = e.id_feed
 GROUP BY f.id
 ORDER BY name
@@ -348,27 +336,6 @@ SQL;
 		return $stm->fetchAll(PDO::FETCH_ASSOC);
 	}
 
-	protected function convertToSerie($data) {
-		$serie = array();
-
-		foreach ($data as $key => $value) {
-			$serie[] = array($key, $value);
-		}
-
-		return json_encode($serie);
-	}
-
-	protected function convertToPieSerie($data) {
-		$serie = array();
-
-		foreach ($data as $value) {
-			$value['data'] = array(array(0, (int) $value['data']));
-			$serie[] = $value;
-		}
-
-		return json_encode($serie);
-	}
-
 	/**
 	 * Gets days ready for graphs
 	 *
@@ -409,17 +376,17 @@ SQL;
 	}
 
 	/**
-	 * Translates array content and encode it as JSON
+	 * Translates array content
 	 *
 	 * @param array $data
-	 * @return string
+	 * @return JSON object
 	 */
 	private function convertToTranslatedJson($data = array()) {
 		$translated = array_map(function($a) {
-			return _t($a);
+			return _t('gen.date.' . $a);
 		}, $data);
 
-		return json_encode($translated);
+		return $translated;
 	}
 
 }