From 68c0a827d221eeb398774d558f3d23b6e1c9e76c Mon Sep 17 00:00:00 2001
From: Alexis Degrugillier <github@ainw.org>
Date: Fri, 18 Jul 2014 20:35:03 -0400
Subject: Add statistics support for Sqlite

Add statistics support for Sqlite by tweeking one query and rewrite an other. The rewrite implied a complete refactor of the MySql query as well.
Now the code is more flexible and make less queries to the database.

See #527
---
 app/Models/StatsDAO.php | 68 ++++++++++++++++++-------------------------------
 1 file changed, 25 insertions(+), 43 deletions(-)

(limited to 'app/Models/StatsDAO.php')

diff --git a/app/Models/StatsDAO.php b/app/Models/StatsDAO.php
index 62f238bd2..66f5104b3 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,50 +52,19 @@ 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.
-		$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.
+		// Get stats per day for the last 30 days
 		$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')
+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;
@@ -108,10 +79,21 @@ SQL;
 		return $this->convertToSerie($count);
 	}
 
+	/**
+	 * Initialize an array for the entry count.
+	 *
+	 * @return array
+	 */
+	protected function initEntryCountArray() {
+		return array_map(function () {
+			return 0;
+		}, array_flip(range(-self::ENTRY_COUNT_PERIOD, -1)));
+	}
+
 	/**
 	 * Calculates feed count per category.
 	 * Returns the result as a JSON string.
-	 * 
+	 *
 	 * @return string
 	 */
 	public function calculateFeedByCategory() {
@@ -134,7 +116,7 @@ SQL;
 	/**
 	 * Calculates entry count per category.
 	 * Returns the result as a JSON string.
-	 * 
+	 *
 	 * @return string
 	 */
 	public function calculateEntryByCategory() {
@@ -158,7 +140,7 @@ SQL;
 
 	/**
 	 * Calculates the 10 top feeds based on their number of entries
-	 * 
+	 *
 	 * @return array
 	 */
 	public function calculateTopFeed() {
@@ -172,7 +154,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;
@@ -180,10 +162,10 @@ SQL;
 		$stm->execute();
 		return $stm->fetchAll(PDO::FETCH_ASSOC);
 	}
-	
+
 	/**
 	 * Calculates the last publication date for each feed
-	 * 
+	 *
 	 * @return array
 	 */
 	public function calculateFeedLastDate() {
-- 
cgit v1.2.3